3 <title>Inventory Availability</title>
4 <name>InventoryAvailability</name>
5 <description></description>
14 <topmargin>50</topmargin>
15 <bottommargin>50</bottommargin>
16 <rightmargin>50</rightmargin>
17 <leftmargin>50</leftmargin>
20 <sql>SELECT <? if exists("byDays") ?>
21 (text('Look ahead ') || text(<? value("byDays") ?>) || text(' days'))
22 <? elseif exists("byDate") ?>
23 (text('Cutoff date ') || formatDate(<? value("byDate") ?>))
24 <? elseif exists("byDates") ?>
25 (text('Dates from ') || formatDate(<? value("startDate") ?>) || text(' to ') || formatDate(<? value("endDate") ?>))
27 text('Item Site Lead Time')
33 <sql>SELECT item_number, item_descrip1, item_descrip2,
34 warehous_code, itemsite_leadtime,
35 formatQty(qtyonhand) AS f_qtyonhand,
36 formatQty(noNeg(qtyonhand - allocated)) AS f_unallocated,
37 formatQty(noNeg(allocated)) AS f_allocated,
38 formatQty(ordered) AS f_ordered,
39 formatQty(requests) AS f_requests,
40 formatQty(reorderlevel) AS f_reorderlevel,
41 (qtyonhand - allocated + ordered) AS available,
42 formatQty(qtyonhand - allocated + ordered) AS f_available
44 <? if reExists("[vV]end") ?>
48 CASE WHEN (item_type IN ('P', 'O')) THEN 1
49 WHEN (item_type IN ('M')) THEN 2
52 item_number, item_descrip1, item_descrip2, item_inv_uom_id,
53 warehous_id, warehous_code, itemsite_leadtime,
54 qtyAvailable(itemsite_id) AS qtyonhand,
55 CASE WHEN itemsite_useparams THEN itemsite_reorderlevel
58 CASE WHEN itemsite_useparams THEN itemsite_ordertoqty
61 <? if exists("byVend") ?>
66 <? if exists("byLeadTime") ?>
67 qtyAllocated(itemsite_id, itemsite_leadtime) AS allocated,
68 qtyOrdered(itemsite_id, itemsite_leadtime) AS ordered,
69 qtypr(itemsite_id, itemsite_leadtime) AS requests
70 <? elseif exists("byDays") ?>
71 qtyAllocated(itemsite_id, CAST(<? value("byDays") ?> AS INTEGER)) AS allocated,
72 qtyOrdered(itemsite_id, CAST(<? value("byDays") ?> AS INTEGER)) AS ordered,
73 qtypr(itemsite_id, CAST(<? value("byDays") ?> AS INTEGER)) AS requests
74 <? elseif exists("byDate") ?>
75 qtyAllocated(itemsite_id, (<? value("byDate") ?> - CURRENT_DATE)) AS allocated,
76 qtyOrdered(itemsite_id, (<? value("byDate") ?> - CURRENT_DATE)) AS ordered,
77 qtypr(itemsite_id, (<? value("byDate") ?> - CURRENT_DATE)) AS requests
78 <? elseif exists("byDates") ?>
79 qtyAllocated(itemsite_id, <? value("startDate") ?>, <? value("endDate") ?>) AS allocated,
80 qtyOrdered(itemsite_id, <? value("startDate") ?>, <? value("endDate") ?>) AS ordered,
81 qtypr(itemsite_id, <? value("startDate") ?>, <? value("endDate") ?>) AS requests
83 FROM item, itemsite, whsinfo
84 <? if reExists("[vV]end") ?>
85 , vendinfo JOIN itemsrc ON (itemsrc_vend_id=vend_id)
87 WHERE ( (itemsite_active)
88 AND (itemsite_item_id=item_id)
89 AND (itemsite_warehous_id=warehous_id)
90 <? if exists("warehous_id") ?>
91 AND (warehous_id=<? value("warehous_id") ?>)
93 <? if exists("item_id") ?>
94 AND (item_id=<? value("item_id") ?>)
95 <? elseif exists("classcode_id") ?>
96 AND (item_classcode_id=<? value("classcode_id") ?>)
97 <? elseif exists("classcode_pattern") ?>
98 AND (item_classcode_id IN (SELECT classcode_id
100 WHERE (classcode_code ~ <? value("classcode_pattern") ?>)))
101 <? elseif exists("plancode_id") ?>
102 AND (itemsite_plancode_id=<? value("plancode_id") ?>)
103 <? elseif exists("plancode_pattern") ?>
104 AND (itemsite_plancode_id IN (SELECT plancode_id
106 WHERE (plancode_code ~ <? value("plancode_pattern") ?>)))
107 <? elseif exists("itemgrp_id") ?>
108 AND (item_id IN (SELECT itemgrpitem_item_id
110 WHERE (itemgrpitem_itemgrp_id=<? value("itemgrp_id") ?>)))
111 <? elseif exists("itemgrp_pattern") ?>
112 AND (item_id IN (SELECT itemgrpitem_item_id
113 FROM itemgrpitem, itemgrp
114 WHERE ( (itemgrpitem_itemgrp_id=itemgrp_id)
115 AND (itemgrp_name ~ <? value("itemgrp_pattern") ?>) ) ))
116 <? elseif exists("itemgrp") ?>
117 AND (item_id IN (SELECT DISTINCT itemgrpitem_item_id FROM itemgrpitem))
119 <? if reExists("[vV]end") ?>
120 AND (itemsrc_item_id=item_id)
122 <? if exists("vend_id") ?>
123 AND (vend_id=<? value("vend_id") ?>)
124 <? elseif exists("vendtype_id") ?>
125 AND (vend_vendtype_id=<? value("vendtype_id") ?>)
126 <? elseif exists("vendtype_pattern") ?>
127 AND (vend_vendtype_id IN (SELECT vendtype_id
129 WHERE (vendtype_code ~ <? value("vendtype_pattern") ?>)))
132 <? if exists("showReorder") ?>
133 WHERE ( ((qtyonhand - allocated + ordered) <= reorderlevel)
134 <? if exists("ignoreReorderAtZero") ?>
135 AND (NOT ( ((qtyonhand - allocated + ordered) = 0) AND (reorderlevel = 0)) )
138 <? elseif exists("showShortages") ?>
139 WHERE ((qtyonhand - allocated + ordered) < 0)
141 ORDER BY item_number, warehous_code DESC;</sql>
155 <weight>normal</weight>
159 <string>Unallocated</string>
171 <weight>normal</weight>
175 <string>Site</string>
187 <weight>normal</weight>
191 <string>Reorder Level</string>
203 <weight>bold</weight>
207 <string>Inventory Availability</string>
219 <weight>normal</weight>
223 <string>Availability as of: </string>
242 <weight>normal</weight>
246 <string>Item Number</string>
258 <weight>normal</weight>
274 <weight>normal</weight>
290 <weight>normal</weight>
294 <string>Allocated</string>
306 <weight>normal</weight>
310 <string>Description</string>
322 <weight>normal</weight>
326 <string>On Order</string>
338 <weight>normal</weight>
342 <string>Available</string>
354 <weight>bold</weight>
360 <column>ltcriteria</column>
373 <weight>normal</weight>
377 <string>PO Requests</string>
386 <bottompad>12</bottompad>
390 <weight>bold</weight>
395 <query>Parameter Query</query>
396 <column>filter</column>
416 <weight>normal</weight>
420 <string>Unallocated</string>
432 <weight>normal</weight>
436 <string>Description</string>
448 <weight>normal</weight>
464 <weight>normal</weight>
468 <string>Reorder Level</string>
480 <weight>normal</weight>
484 <string>Item Number</string>
496 <weight>normal</weight>
500 <string>Available</string>
512 <weight>normal</weight>
528 <weight>normal</weight>
532 <string>Allocated</string>
551 <weight>normal</weight>
555 <string>Site</string>
567 <weight>normal</weight>
571 <string>On Order</string>
578 <query>detail</query>
591 <weight>bold</weight>
596 <query>detail</query>
597 <column>f_qtyonhand</column>
610 <weight>bold</weight>
615 <query>detail</query>
616 <column>f_available</column>
629 <weight>bold</weight>
634 <query>detail</query>
635 <column>item_number</column>
648 <weight>bold</weight>
653 <query>detail</query>
654 <column>f_allocated</column>
667 <weight>bold</weight>
672 <query>detail</query>
673 <column>itemsite_leadtime</column>
686 <weight>bold</weight>
691 <query>detail</query>
692 <column>f_ordered</column>
712 <weight>bold</weight>
717 <query>detail</query>
718 <column>item_descrip1</column>
731 <weight>bold</weight>
736 <query>detail</query>
737 <column>warehous_code</column>
750 <weight>bold</weight>
755 <query>detail</query>
756 <column>item_descrip2</column>
769 <weight>bold</weight>
774 <query>detail</query>
775 <column>f_unallocated</column>
788 <weight>bold</weight>
793 <query>detail</query>
794 <column>f_reorderlevel</column>
807 <weight>bold</weight>
812 <query>detail</query>
813 <column>f_requests</column>
830 <weight>bold</weight>
835 <query>Context Query</query>
836 <column>report_date</column>
849 <weight>normal</weight>
853 <string>Page:</string>
865 <weight>bold</weight>
870 <query>Context Query</query>
871 <column>page_number</column>
884 <weight>normal</weight>
888 <string>Report Date:</string>