3 <title>Inventory Availability by Customer Type</title>
4 <name>InventoryAvailabilityByCustomerType</name>
5 <description></description>
14 <topmargin>50</topmargin>
15 <bottommargin>50</bottommargin>
16 <rightmargin>50</rightmargin>
17 <leftmargin>50</leftmargin>
21 <? if exists("onlyShowShortages") ?>
22 text('Only Showing Shortages')
26 AS f_onlyShowShortages,
27 <? if exists("showWoSupply") ?>
28 text('Show W/O Supply')
33 <? if exists("custtype_id") ?>
34 (SELECT custtype_code from custtype where (custtype_id=<? value("custtype_id") ?>))
35 <? elseif exists("custtype_pattern") ?>
36 <? value("custtype_pattern") ?>
38 text('All Customer Types')
44 <sql>SELECT itemsite_id, coitem_id,
45 cohead_id, cohead_number, (cust_number||'-'||cust_name) AS custname,
46 item_number, item_description, uom_name, item_picklist,
47 qoh, formatQty(qoh) AS f_qoh,sobalance,
48 formatQty(sobalance) AS f_sobalance,
49 formatQty(allocated) AS f_allocated,
50 ordered, formatQty(ordered) AS f_ordered,
51 (qoh + ordered - sobalance) AS woavail,
52 <? if exists("useReservationNetting") ?>
53 formatQty(coitem_qtyreserved) AS f_soavail,
55 formatQty(qoh + ordered - sobalance) AS f_soavail,
57 (qoh + ordered - allocated) AS totalavail,
58 formatQty(qoh + ordered - allocated) AS f_totalavail,
59 atshipping,formatQty(atshipping) AS f_atshipping,
61 <? if exists(showWoSupply) ?>,
66 CASE WHEN (wo_id = -1) THEN NULL ELSE formatQty(wo_ordered) END AS f_wo_ordered,
67 formatdate(wo_startdate) AS f_wo_startdate,
68 formatdate(wo_duedate) AS f_wo_duedate,
69 COALESCE(wo_latestart,false) AS wo_latestart,
70 COALESCE(wo_latedue,false) AS wo_latedue
72 FROM ( SELECT itemsite_id, coitem_id,
73 cohead_id, cohead_number, cust_number, cust_name,
74 item_number, (item_descrip1 || ' ' || item_descrip2) AS item_description,
75 uom_name, item_picklist,
76 noNeg(qtyNetable(itemsite_id)) AS qoh,
77 noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned) AS sobalance,
78 qtyAllocated(itemsite_id, coitem_scheddate) AS allocated,
79 qtyOrdered(itemsite_id, coitem_scheddate) AS ordered,
80 qtyatshipping(coitem_id) AS atshipping,
82 CASE WHEN(itemsite_useparams) THEN itemsite_reorderlevel ELSE 0.0 END AS reorderlevel
83 <? if exists(showWoSupply) ?>,
84 COALESCE(wo_id,-1) AS wo_id,
85 formatwonumber(wo_id) AS wo_number,
86 noNeg((wo_qtyord-wo_qtyrcv)) AS wo_ordered,
87 wo_status, wo_startdate, wo_duedate,
88 ((wo_startdate <= CURRENT_DATE) AND (wo_status IN ('O','E','S','R'))) AS wo_latestart,
89 (wo_duedate<=CURRENT_DATE) AS wo_latedue
91 FROM cohead, itemsite, item, uom, custinfo, coitem
92 <? if exists(showWoSupply) ?>
94 ON ((coitem_itemsite_id=wo_itemsite_id)
95 AND (wo_status IN ('E','R','I'))
96 AND (wo_qtyord-wo_qtyrcv > 0)
97 AND (noNeg(coitem_qtyord - coitem_qtyshipped + coitem_qtyreturned-qtyatshipping(coitem_id)) >
98 (SELECT qtyNetable(itemsite_id) AS netableqoh FROM itemsite WHERE (itemsite_id=coitem_itemsite_id))))
100 WHERE((coitem_cohead_id=cohead_id)
101 AND (cohead_cust_id=cust_id)
102 AND (coitem_itemsite_id=itemsite_id)
103 AND (itemsite_item_id=item_id)
104 AND (item_inv_uom_id=uom_id)
105 AND (coitem_status NOT IN ('C', 'X'))
106 <? if exists("custtype_id") ?>
107 AND (cust_custtype_id=<? value("custtype_id") ?>)
108 <? elseif exists("custtype_pattern") ?>
109 AND (cust_custtype_id IN (SELECT custtype_id
111 WHERE(custtype_code ~ <? value("custtype_pattern") ?>)))
115 <? if exists(onlyShowShortages) ?>
116 WHERE ( ((qoh + ordered - allocated) < 0)
117 OR ((qoh + ordered - sobalance) < 0) )
119 ORDER BY cohead_id, cohead_number, item_number
120 <? if exists(showWoSupply) ?> ,
137 <weight>normal</weight>
153 <weight>bold</weight>
159 <column>f_showWoSupply</column>
172 <weight>normal</weight>
176 <string>Description</string>
195 <weight>normal</weight>
211 <weight>normal</weight>
215 <string>Customer Type:</string>
227 <weight>bold</weight>
233 <column>f_custtype</column>
246 <weight>normal</weight>
250 <string>Total Available</string>
262 <weight>normal</weight>
266 <string>Due Date</string>
278 <weight>normal</weight>
282 <string>Start Date</string>
294 <weight>bold</weight>
300 <column>f_onlyShowShortages</column>
313 <weight>normal</weight>
317 <string>At Shipping</string>
329 <weight>normal</weight>
333 <string>Status</string>
345 <weight>normal</weight>
349 <string>This Allocated</string>
361 <weight>normal</weight>
365 <string>Orders</string>
377 <weight>normal</weight>
381 <string>Qty Due</string>
393 <weight>normal</weight>
397 <string>This Available</string>
409 <weight>normal</weight>
413 <string>W/O Number</string>
425 <weight>bold</weight>
429 <string>Inventory Availability by Customer Type</string>
441 <weight>normal</weight>
445 <string>Item</string>
457 <weight>normal</weight>
461 <string>Total Allocated</string>
480 <weight>normal</weight>
484 <string>This Available</string>
496 <weight>normal</weight>
500 <string>This Allocated</string>
519 <weight>normal</weight>
523 <string>Orders</string>
535 <weight>normal</weight>
539 <string>Total Allocated</string>
551 <weight>normal</weight>
555 <string>Description</string>
567 <weight>normal</weight>
583 <weight>normal</weight>
599 <weight>normal</weight>
603 <string>Total Available</string>
615 <weight>normal</weight>
619 <string>At Shipping</string>
631 <weight>normal</weight>
635 <string>Item</string>
642 <column>cohead_id</column>
655 <weight>normal</weight>
659 <string>S/O #:</string>
671 <weight>bold</weight>
676 <query>detail</query>
677 <column>custname</column>
690 <weight>normal</weight>
694 <string>Customer:</string>
706 <weight>bold</weight>
711 <query>detail</query>
712 <column>cohead_number</column>
729 <query>detail</query>
742 <weight>bold</weight>
747 <query>detail</query>
748 <column>f_totalavail</column>
761 <weight>bold</weight>
766 <query>detail</query>
767 <column>wo_number</column>
780 <weight>bold</weight>
785 <query>detail</query>
786 <column>f_ordered</column>
799 <weight>bold</weight>
804 <query>detail</query>
805 <column>Item_description</column>
818 <weight>bold</weight>
823 <query>detail</query>
824 <column>f_wo_duedate</column>
837 <weight>bold</weight>
842 <query>detail</query>
843 <column>Item_number</column>
856 <weight>bold</weight>
861 <query>detail</query>
862 <column>f_sobalance</column>
875 <weight>bold</weight>
880 <query>detail</query>
881 <column>uom_name</column>
894 <weight>bold</weight>
899 <query>detail</query>
900 <column>wo_status</column>
913 <weight>bold</weight>
918 <query>detail</query>
919 <column>f_soavail</column>
932 <weight>bold</weight>
937 <query>detail</query>
938 <column>f_allocated</column>
951 <weight>bold</weight>
956 <query>detail</query>
957 <column>f_wo_ordered</column>
970 <weight>bold</weight>
975 <query>detail</query>
976 <column>f_qoh</column>
989 <weight>bold</weight>
994 <query>detail</query>
995 <column>f_atshipping</column>
1008 <weight>bold</weight>
1013 <query>detail</query>
1014 <column>f_wo_startdate</column>
1031 <weight>normal</weight>
1035 <string>Report Date:</string>
1047 <weight>normal</weight>
1051 <string>Page:</string>
1063 <weight>bold</weight>
1068 <query>Context Query</query>
1069 <column>report_date</column>
1082 <weight>bold</weight>
1087 <query>Context Query</query>
1088 <column>page_number</column>