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.
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 (qtyAvailable(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
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);
39 $$ LANGUAGE 'plpgsql' STABLE;