3 <title>Inventory Availability by Sales Order</title>
4 <name>InventoryAvailabilityBySalesOrder</name>
5 <description></description>
8 <topmargin>50</topmargin>
9 <bottommargin>50</bottommargin>
10 <rightmargin>50</rightmargin>
11 <leftmargin>50</leftmargin>
14 <sql>SELECT cohead_number,
15 formatDate(cohead_orderdate) AS orderdate,
18 cntct_phone AS cust_phone,
19 <? if exists("onlyShowShortages") ?>
20 text('Only Showing Shortages')
24 AS f_onlyShowShortages,
25 <? if exists("showWoSupply") ?>
26 text('Show W/O Supply')
32 LEFT OUTER JOIN cntct ON (cust_cntct_id=cntct_id)
33 LEFT OUTER JOIN addr ON (cntct_addr_id=addr_id)
34 WHERE ((cohead_cust_id=cust_id)
35 AND (cohead_id=<? value("sohead_id") ?>) );</sql>
39 <sql>SELECT itemsite_id, coitem_id,
40 item_number, item_description, uom_name, item_picklist,
41 qoh, formatQty(qoh) AS f_qoh,sobalance,
42 formatQty(sobalance) AS f_sobalance,
43 formatQty(allocated) AS f_allocated,
44 ordered, formatQty(ordered) AS f_ordered,
45 (qoh + ordered - sobalance) AS woavail,
46 formatQty(qoh + ordered - sobalance) AS f_soavail,
47 (qoh + ordered - allocated) AS totalavail,
48 formatQty(qoh + ordered - allocated) AS f_totalavail,
49 atshipping,formatQty(atshipping) AS f_atshipping,
51 <? if exists(showWoSupply) ?>,
56 CASE WHEN (wo_id = -1) THEN NULL ELSE formatQty(wo_ordered) END AS f_wo_ordered,
57 formatdate(wo_startdate) AS f_wo_startdate,
58 formatdate(wo_duedate) AS f_wo_duedate,
59 COALESCE(wo_latestart,false) AS wo_latestart,
60 COALESCE(wo_latedue,false) AS wo_latedue
62 FROM ( SELECT itemsite_id, coitem_id,
63 item_number, (item_descrip1 || ' ' || item_descrip2) AS item_description,
64 uom_name, item_picklist,
65 noNeg(itemsite_qtyonhand) AS qoh,
66 noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) AS sobalance,
67 qtyAllocated(itemsite_id, coitem_scheddate) AS allocated,
68 qtyOrdered(itemsite_id, coitem_scheddate) AS ordered,
69 qtyatshipping(coitem_id) AS atshipping,
70 CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel
71 <? if exists(showWoSupply) ?>,
72 COALESCE(wo_id,-1) AS wo_id,
73 formatwonumber(wo_id) AS wo_number,
74 noNeg((wo_qtyord-wo_qtyrcv)) AS wo_ordered,
75 wo_status, wo_startdate, wo_duedate,
76 ((wo_startdate <= CURRENT_DATE) AND (wo_status IN ('O','E','S','R'))) AS wo_latestart,
77 (wo_duedate<=CURRENT_DATE) AS wo_latedue
79 FROM cohead, itemsite, item, uom, coitem
80 <? if exists(showWoSupply) ?>
82 ON ((coitem_itemsite_id=wo_itemsite_id)
83 AND (wo_status IN ('E','R','I'))
84 AND (wo_qtyord-wo_qtyrcv > 0)
85 AND (noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned-qtyatshipping(coitem_id)) >
86 (SELECT itemsite_qtyonhand FROM itemsite WHERE (itemsite_id=coitem_itemsite_id))))
88 WHERE ( (coitem_cohead_id=cohead_id)
89 AND (coitem_itemsite_id=itemsite_id)
90 AND (itemsite_item_id=item_id)
91 AND (item_inv_uom_id=uom_id)
92 AND (coitem_status NOT IN ('C', 'X'))
93 AND (cohead_id=<? value(sohead_id) ?>))
95 <? if exists(onlyShowShortages) ?>
96 WHERE ( ((qoh + ordered - allocated) < 0)
97 OR ((qoh + ordered - sobalance) < 0) )
100 <? if exists(showWoSupply) ?> ,
117 <weight>normal</weight>
121 <string>Qty Due</string>
133 <weight>normal</weight>
137 <string>Total Allocated</string>
149 <weight>normal</weight>
153 <string>Status</string>
165 <weight>normal</weight>
169 <string>Customer:</string>
181 <weight>normal</weight>
185 <string>Start Date</string>
197 <weight>normal</weight>
201 <string>Cust. Phone:</string>
213 <weight>normal</weight>
217 <string>This Available</string>
229 <weight>normal</weight>
233 <string>Sales Order #:</string>
245 <weight>bold</weight>
251 <column>cohead_custponumber</column>
264 <weight>bold</weight>
268 <string>Inventory Availability by Sales Order</string>
280 <weight>normal</weight>
284 <string>P/O #:</string>
296 <weight>normal</weight>
300 <string>Orders</string>
312 <weight>normal</weight>
316 <string>W/O Number</string>
328 <weight>bold</weight>
334 <column>f_showWoSupply</column>
347 <weight>normal</weight>
351 <string>Description</string>
363 <weight>normal</weight>
367 <string>This Allocated</string>
386 <weight>bold</weight>
392 <column>cohead_number</column>
405 <weight>normal</weight>
409 <string>Order Date:</string>
421 <weight>bold</weight>
427 <column>cust_name</column>
440 <weight>normal</weight>
444 <string>Due Date</string>
456 <weight>normal</weight>
460 <string>Item</string>
472 <weight>bold</weight>
478 <column>cust_phone</column>
491 <weight>bold</weight>
497 <column>f_onlyShowShortages</column>
510 <weight>bold</weight>
516 <column>orderdate</column>
529 <weight>normal</weight>
545 <weight>normal</weight>
561 <weight>normal</weight>
565 <string>Total Available</string>
577 <weight>normal</weight>
581 <string>At Shipping</string>
600 <weight>normal</weight>
604 <string>This Allocated</string>
616 <weight>normal</weight>
632 <weight>normal</weight>
636 <string>Description</string>
648 <weight>normal</weight>
652 <string>At Shipping</string>
664 <weight>normal</weight>
668 <string>Total Available</string>
680 <weight>normal</weight>
684 <string>Item</string>
696 <weight>normal</weight>
700 <string>This Available</string>
719 <weight>normal</weight>
723 <string>Total Allocated</string>
735 <weight>normal</weight>
751 <weight>normal</weight>
755 <string>Orders</string>
762 <column>coitem_id</column>
775 <weight>bold</weight>
780 <query>detail</query>
781 <column>f_allocated</column>
794 <weight>bold</weight>
799 <query>detail</query>
800 <column>f_qoh</column>
813 <weight>bold</weight>
818 <query>detail</query>
819 <column>uom_name</column>
832 <weight>bold</weight>
837 <query>detail</query>
838 <column>f_soavail</column>
851 <weight>bold</weight>
856 <query>detail</query>
857 <column>f_atshipping</column>
870 <weight>bold</weight>
875 <query>detail</query>
876 <column>f_totalavail</column>
889 <weight>bold</weight>
894 <query>detail</query>
895 <column>f_ordered</column>
908 <weight>bold</weight>
913 <query>detail</query>
914 <column>Item_number</column>
927 <weight>bold</weight>
932 <query>detail</query>
933 <column>Item_description</column>
946 <weight>bold</weight>
951 <query>detail</query>
952 <column>f_sobalance</column>
969 <query>detail</query>
982 <weight>bold</weight>
987 <query>detail</query>
988 <column>f_wo_startdate</column>
1001 <weight>bold</weight>
1006 <query>detail</query>
1007 <column>wo_number</column>
1020 <weight>bold</weight>
1025 <query>detail</query>
1026 <column>f_wo_ordered</column>
1039 <weight>bold</weight>
1044 <query>detail</query>
1045 <column>f_wo_duedate</column>
1058 <weight>bold</weight>
1063 <query>detail</query>
1064 <column>wo_status</column>
1081 <weight>bold</weight>
1086 <query>Context Query</query>
1087 <column>report_date</column>
1100 <weight>normal</weight>
1104 <string>Report Date:</string>
1116 <weight>normal</weight>
1120 <string>Page:</string>
1132 <weight>bold</weight>
1137 <query>Context Query</query>
1138 <column>page_number</column>