git.roojs.org Git - xtuple/blob - foundation-database/public/functions/getsoitemstatus.sql

   1 CREATE OR REPLACE FUNCTION getSoitemStatus(pCoitemid INTEGER) RETURNS TEXT AS $$
   2 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
   3 -- See www.xtuple.com/CPAL for the full text of the software license.
   4 DECLARE
   5   _result TEXT;
   6
   7 BEGIN
   8
   9   SELECT
  10       (CASE WHEN (coitem_status='O' AND (SELECT cust_creditstatus FROM custinfo WHERE cust_id=cohead_cust_id)='H') THEN 'H'
  11             WHEN (coitem_status='O' AND ((SELECT SUM(invcitem_billed)
  12                                             FROM invchead, invcitem
  13                                            WHERE ((CAST(invchead_ordernumber AS text)=cohead_number)
  14                                              AND  (invcitem_invchead_id=invchead_id)
  15                                              AND  (invcitem_item_id=itemsite_item_id)
  16                                              AND  (invcitem_warehous_id=itemsite_warehous_id)
  17                                              AND  (invcitem_linenumber=coitem_linenumber))) >= coitem_qtyord)) THEN 'I'
  18             WHEN (coitem_status='O' AND ((SELECT SUM(invcitem_billed)
  19                                             FROM invchead, invcitem
  20                                            WHERE ((CAST(invchead_ordernumber AS text)=cohead_number)
  21                                              AND  (invcitem_invchead_id=invchead_id)
  22                                              AND  (invcitem_item_id=itemsite_item_id)
  23                                              AND  (invcitem_warehous_id=itemsite_warehous_id)
  24                                              AND  (invcitem_linenumber=coitem_linenumber))) > 0)) THEN 'P'
  25             WHEN (coitem_status='O' AND (qtyNetable(itemsite_id) - qtyAllocated(itemsite_id, CURRENT_DATE)
  26                                          + qtyOrdered(itemsite_id, CURRENT_DATE))
  27                                           >= ((coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) * coitem_qty_invuomratio)) THEN 'R'
  28             ELSE coitem_status END
  29        || CASE WHEN (coitem_firm) THEN 'F' ELSE '' END
  30        ) INTO _result
  31   FROM coitem JOIN cohead ON (cohead_id=coitem_cohead_id)
  32               JOIN custinfo ON (cust_id=cohead_cust_id)
  33               JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
  34   WHERE (coitem_id=pCoitemid);
  35
  36   RETURN _result;
  37
  38 END;
  39 $$ LANGUAGE 'plpgsql' STABLE;