1 -- Group: salesOrderItems
3 -- Notes: used by salesOrder
4 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
5 -- See www.xtuple.com/CPAL for the full text of the software license.
7 SELECT coitem_id, coitem_altid, groupby,
8 coitem_linenumber, coitem_subnumber, f_linenumber,
9 warehous_code, item_number, item_type, description,
10 item_number_cust, item_descrip1_cust, item_descrip2_cust, item_barcode,
11 prodweight, packweight,
12 enhanced_status, coitem_firm,
13 qty_uom, price_uom, ship_uom,
15 -- always create formatted columns for scheduled reports
16 formatDate(coitem_scheddate) AS f_scheddate,
17 formatSalesPrice(coitem_price) AS f_price,
18 formatSalesPrice(coitem_custprice) AS f_custprice,
19 formatPrcnt(discountfromcust / 100) AS f_discountfromcust,
20 formatQty(coitem_qtyord) AS f_qtyord,
21 CASE WHEN (qtyshipped > 0.0) THEN formatQty(qtyshipped) ELSE NULL END AS f_qtyshipped,
22 CASE WHEN (qtyshipped > 0.0) THEN formatQty(balance) ELSE NULL END AS f_balance,
23 CASE WHEN (qtyatshipping > 0.0) THEN formatQty(qtyatshipping) ELSE NULL END AS f_qtyatshipping,
24 formatMoney(extprice) AS f_extprice,
25 CASE WHEN (extprice_shipped > 0.0) THEN formatMoney(extprice_shipped) ELSE NULL END AS f_extprice_shipped,
26 -- end formatted columns
27 coitem_scheddate, coitem_price, coitem_custprice, discountfromcust,
28 coitem_unitcost, margin, marginpercent,
29 coitem_qtyord, qtyshipped, balance, qtyatshipping,
30 extprice, extprice_shipped,
31 CASE WHEN (discountfromcust=100.0) THEN 'N/A' END AS discountfromcust_qtdisplayrole,
32 CASE WHEN (margin=0.0) THEN 'N/A' END AS margin_qtdisplayrole,
33 CASE WHEN (marginpercent=0.0) THEN 'N/A' END AS marginpercent_qtdisplayrole,
34 'qty' AS coitem_qtyord_xtnumericrole,
35 'qty' AS qtyshipped_xtnumericrole,
36 'qty' AS balance_xtnumericrole,
37 'qty' AS qtyatshipping_xtnumericrole,
38 'qty' AS prodweight_xtnumericrole,
39 'qty' AS packweight_xtnumericrole,
40 'salesprice' AS coitem_price_xtnumericrole,
41 'salesprice' AS coitem_custprice_xtnumericrole,
42 'cost' AS coitem_unitcost_xtnumericrole,
43 'curr' AS extprice_xtnumericrole,
44 'curr' AS extprice_shipped_xtnumericrole,
45 'curr' AS margin_xtnumericrole,
46 'percent' AS marginpercent_xtnumericrole,
47 CASE WHEN fetchMetricBool('EnableSOShipping') AND
48 coitem_scheddate > CURRENT_DATE AND
49 (noNeg(coitem_qtyord) <> qtyAtShipping('SO', coitem_id)) THEN 'future'
50 WHEN fetchMetricBool('EnableSOShipping') AND
51 (noNeg(coitem_qtyord) <> qtyAtShipping('SO', coitem_id)) THEN 'expired'
52 WHEN (coitem_status NOT IN ('C', 'X') AND
53 EXISTS(SELECT coitem_id
55 WHERE ((coitem_status='C')
56 AND (coitem_cohead_id=<? value("cohead_id") ?>)))) THEN 'error'
57 END AS coitem_scheddate_qtforegroundrole,
58 CASE WHEN coitem_subnumber = 0 THEN 0
63 'qty' AS availableqoh_xtnumericrole
64 <? if exists("includeReservations") ?>
68 'qty' AS reserved_xtnumericrole,
69 'qty' AS reservable_xtnumericrole
73 CASE WHEN (coitem_status='C') THEN 1
74 WHEN (coitem_status='X') THEN 4
75 WHEN ( (coitem_status='O') AND ( (qtyAtShipping('SO', coitem_id) > 0) OR (coitem_qtyshipped > 0) ) ) THEN 2
77 END AS coitem_altid, 1 AS groupby,
78 coitem_linenumber, coitem_subnumber, formatSoLineNumber(coitem_id) AS f_linenumber,
79 warehous_code, item_number, item_type,
80 (item_descrip1 || ' ' || item_descrip2) AS description,
81 CASE WHEN (coitem_custpn != '') THEN coitem_custpn
83 END AS item_number_cust,
84 CASE WHEN (coitem_custpn != '' AND itemalias_usedescrip=TRUE) THEN itemalias_descrip1
86 END AS item_descrip1_cust,
87 CASE WHEN (coitem_custpn != '' AND itemalias_usedescrip=TRUE) THEN itemalias_descrip2
89 END AS item_descrip2_cust,
90 formatitemsitebarcode(itemsite_id) AS item_barcode,
91 (coitem_qtyord * coitem_qty_invuomratio * item_prodweight) AS prodweight,
92 (coitem_qtyord * coitem_qty_invuomratio * item_packweight) AS packweight,
93 coitem_status, coitem_firm,
94 getSoitemStatus(coitem_id) AS enhanced_status,
95 quom.uom_name AS qty_uom, puom.uom_name AS price_uom,
96 itemSellingUOM(item_id) AS ship_uom,
98 coitem_scheddate, coitem_price, coitem_custprice, coitem_qtyord,
99 CASE WHEN (coitem_custprice = 0.0) THEN 100.0
100 ELSE ((1.0 - (coitem_price / coitem_custprice)) * 100.0)
101 END AS discountfromcust,
103 CASE WHEN (coitem_price = 0.0) THEN 0.0
104 ELSE ROUND(coitem_qtyord * coitem_qty_invuomratio * (coitem_price - coitem_unitcost) / coitem_price_invuomratio,2)
106 CASE WHEN (coitem_price = 0.0) THEN 0.0
107 ELSE ((coitem_price - coitem_unitcost) / coitem_price)
108 END AS marginpercent,
109 noNeg(coitem_qtyshipped - coitem_qtyreturned) AS qtyshipped,
110 noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) AS balance,
111 qtyAtShipping('SO', coitem_id) AS qtyatshipping,
112 ROUND((coitem_qtyord * coitem_qty_invuomratio) *
113 (coitem_price / coitem_price_invuomratio),2) AS extprice,
114 ROUND(((coitem_qtyshipped - coitem_qtyreturned) * coitem_qty_invuomratio) *
115 (coitem_price / coitem_price_invuomratio),2) AS extprice_shipped,
116 CASE WHEN coitem_order_type='W' THEN TEXT( 'WO')
117 WHEN coitem_order_type='P' THEN TEXT('PO' )
118 WHEN coitem_order_type='R' THEN TEXT('PR')
121 CASE WHEN coitem_order_type='W' THEN (wo_number || '-' || wo_subnumber)
122 WHEN coitem_order_type='P' THEN (pohead_number || '-' || poitem_linenumber)
123 WHEN coitem_order_type='R' THEN (pr_number || '-' || pr_subnumber)
126 qtyAvailable(itemsite_id) AS availableqoh
127 <? if exists("includeReservations") ?>
129 coitem_qtyreserved AS reserved,
130 qtyAvailable(itemsite_id) - qtyReserved(itemsite_id) AS reservable
133 JOIN coitem ON (coitem_cohead_id=cohead_id)
134 JOIN itemsite ON (itemsite_id=coitem_itemsite_id)
135 JOIN item ON (item_id=itemsite_item_id)
136 JOIN whsinfo ON (warehous_id=itemsite_warehous_id)
137 JOIN uom AS quom ON (quom.uom_id=coitem_qty_uom_id)
138 JOIN uom AS puom ON (puom.uom_id=coitem_price_uom_id)
139 LEFT OUTER JOIN itemalias ON ((itemalias_item_id=item_id) AND (itemalias_number=coitem_custpn))
140 LEFT OUTER JOIN wo ON (coitem_order_id = wo_id)
141 LEFT OUTER JOIN pr ON (coitem_order_id = pr_id)
142 LEFT OUTER JOIN (pohead JOIN poitem ON (pohead_id = poitem_pohead_id))
143 ON (coitem_order_id = poitem_id)
144 WHERE (cohead_id=<? value("sohead_id") ?>)
146 <? if exists("excludeCancelled") ?>
147 AND (coitem_status != 'X')
150 <? if exists("excludeClosed") ?>
151 AND (coitem_status != 'C')
154 <? if exists("excludeKits") ?>
155 AND (item_type != 'K')
159 ORDER BY coitem_linenumber, coitem_subnumber;