1 -- Group: pendingAvailability
3 -- Notes: used by dspPendingAvailability
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 <? if exists("showIndented") ?>
10 SELECT itemsite_id, reorderlevel,
11 bomdata_bomwork_level,
13 bomdata_bomwork_parent_id,
14 bomdata_bomwork_seqnumber AS bomitem_seqnumber,
15 bomdata_item_number AS item_number,
16 bomdata_itemdescription AS item_descrip,
17 bomdata_uom_name AS uom_name,
21 (totalalloc + pendalloc) AS totalalloc,
22 (qoh + ordered - (totalalloc + pendalloc)) AS totalavail,
23 'qty' AS pendalloc_xtnumericrole,
24 'qty' AS ordered_xtnumericrole,
25 'qty' AS qoh_xtnumericrole,
26 'qty' AS totalalloc_xtnumericrole,
27 'qty' AS totalavail_xtnumericrole,
28 CASE WHEN (qoh < pendalloc) THEN 'error' END AS qoh_qtforegroundrole,
29 CASE WHEN ((qoh + ordered - (totalalloc + pendalloc)) < reorderlevel) THEN 'error'
30 WHEN ((qoh + ordered - (totalalloc + pendalloc)) = reorderlevel) THEN 'warning'
31 END AS totalavail_qtforegroundrole,
32 bomdata_bomwork_level - 1 AS xtindentrole
33 FROM ( SELECT itemsite_id,
34 CASE WHEN(itemsite_useparams)
35 THEN itemsite_reorderlevel
39 ((bomdata_qtyreq::NUMERIC * <? value("buildQty") ?>) * (1 + bomdata_scrap::NUMERIC)) AS pendalloc,
40 qtyAllocated(itemsite_id, DATE(<? value("buildDate") ?>)) AS totalalloc,
41 noNeg(qtyAvailable(itemsite_id)) AS qoh,
42 qtyOrdered(itemsite_id, DATE(<? value("buildDate") ?>)) AS ordered
43 FROM indentedBOM(<? value("item_id") ?>,
44 getActiveRevId('BOM', <? value("item_id") ?>),
45 0,0) ib LEFT OUTER JOIN
46 itemsite ON ((itemsite_item_id=bomdata_item_id)
47 AND (itemsite_warehous_id=<? value("warehous_id") ?>))
48 WHERE (bomdata_item_id > 0)
49 <? if not exists("effective") ?>
50 AND (CURRENT_DATE BETWEEN bomdata_effective AND (bomdata_expires-1))
52 AND (<? value("effective") ?> BETWEEN bomdata_effective AND (bomdata_expires-1))
55 <? if exists("showShortages") ?>
56 WHERE ((qoh + ordered - (totalalloc + pendalloc)) < 0)
58 ORDER BY bomworkSequence(bomdata_bomwork_id);
63 SELECT itemsite_id, bomitem_seqnumber, item_number, item_descrip, uom_name,
64 pendalloc, (totalalloc + pendalloc) AS totalalloc,
65 qoh, (qoh + ordered - (totalalloc + pendalloc)) AS totalavail,
67 'qty' AS pendalloc_xtnumericrole,
68 'qty' AS totalalloc_xtnumericrole,
69 'qty' AS qoh_xtnumericrole,
70 'qty' AS totalavail_xtnumericrole,
71 CASE WHEN (qoh < pendalloc) THEN 'error' END AS qoh_qtforegroundrole,
72 CASE WHEN ((qoh + ordered - (totalalloc + pendalloc)) < reorderlevel) THEN 'error'
73 WHEN ((qoh + ordered - (totalalloc + pendalloc)) = reorderlevel) THEN 'warning'
74 END AS totalavail_qtforegroundrole
75 FROM ( SELECT itemsite_id, bomitem_seqnumber, item_number,
76 (item_descrip1 || ' ' || item_descrip2) AS item_descrip, uom_name,
77 ((itemuomtouom(bomitem_item_id, bomitem_uom_id, NULL,
78 (bomitem_qtyfxd + bomitem_qtyper * <? value("buildQty") ?>) * (1 + bomitem_scrap)))) AS pendalloc,
79 qtyAllocated(itemsite_id, DATE(<? value("buildDate") ?>)) AS totalalloc,
80 noNeg(qtyAvailable(itemsite_id)) AS qoh,
81 qtyOrdered(itemsite_id, DATE(<? value("buildDate") ?>)) AS ordered,
82 CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel
83 FROM itemsite, item, bomitem(<? value("item_id") ?>), uom
84 WHERE ( (bomitem_item_id=itemsite_item_id)
85 AND (itemsite_item_id=item_id)
86 AND (item_inv_uom_id=uom_id)
87 AND (itemsite_warehous_id=<? value("warehous_id") ?>)
88 <? if not exists("effective") ?>
89 AND (CURRENT_DATE BETWEEN bomitem_effective AND (bomitem_expires-1))
91 AND (<? value("effective") ?> BETWEEN bomitem_effective AND (bomitem_expires-1))
95 <? if exists("showShortages") ?>
96 WHERE ((qoh + ordered - (totalalloc + pendalloc)) < 0)
98 ORDER BY bomitem_seqnumber;