3 <title>Substitue Availability By Root Item</title>
4 <name>SubstituteAvailabilityByRootItem</name>
5 <description></description>
8 <topmargin>50</topmargin>
9 <bottommargin>50</bottommargin>
10 <rightmargin>50</rightmargin>
11 <leftmargin>50</leftmargin>
14 <sql>SELECT item_number,
17 <? if exists("warehous_id") ?>
18 ( SELECT warehous_code
20 WHERE (warehous_id=<? value("warehous_id") ?>) )
26 WHERE (item_id=<? value("item_id") ?>);</sql>
30 <sql>SELECT warehous_code,
34 formatQty(sub.itemsite_qtyonhand) AS f_qtyonhand,
35 formatQty(CASE WHEN(sub.itemsite_useparams) THEN sub.itemsite_reorderlevel ELSE 0.0 END) AS f_reorderlevel,
36 sub.itemsite_leadtime as leadtime,
37 <? if exists("byDays") ?>
38 formatQty(qtyAllocated(sub.itemsite_id, <? value("byDays") ?>)) AS f_allocated,
39 formatQty(qtyOrdered(sub.itemsite_id, <? value("byDays") ?>)) AS f_ordered,
40 formatQty(sub.itemsite_qtyonhand + qtyOrdered(sub.itemsite_id, <? value("byDays") ?>) - qtyAllocated(sub.itemsite_id, <? value("byDays") ?>)) as f_avail
41 <? elseif exists("byDate") ?>
42 formatQty(qtyAllocated(sub.itemsite_id, (<? value("byDate") ?> - CURRENT_DATE))) AS f_allocated,
43 formatQty(qtyOrdered(sub.itemsite_id, (<? value("byDate") ?> - CURRENT_DATE))) AS f_ordered,
44 formatQty(sub.itemsite_qtyonhand + qtyOrdered(sub.itemsite_id, (<? value("byDate") ?> - CURRENT_DATE)) - qtyAllocated(sub.itemsite_id, (<? value("byDate") ?> - CURRENT_DATE))) as f_avail
46 formatQty(qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime)) AS f_allocated,
47 formatQty(qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime)) AS f_ordered,
48 formatQty(sub.itemsite_qtyonhand + qtyOrdered(sub.itemsite_id, sub.itemsite_leadtime) - qtyAllocated(sub.itemsite_id, sub.itemsite_leadtime)) as f_avail
50 FROM item, itemsite AS sub, itemsite AS root, whsinfo, itemsub
51 WHERE ((sub.itemsite_item_id=item_id)
52 AND (root.itemsite_item_id=itemsub_parent_item_id)
53 AND (sub.itemsite_item_id=itemsub_sub_item_id)
54 AND (root.itemsite_warehous_id=sub.itemsite_warehous_id)
55 AND (sub.itemsite_warehous_id=warehous_id)
56 AND (root.itemsite_item_id=<? value("item_id") ?>)
57 <? if exists("warehous_id") ?>
58 AND (root.itemsite_warehous_id=<? value("warehous_id") ?>)
61 ORDER BY itemsub_rank;</sql>
81 <column>warehouse</column>
94 <weight>normal</weight>
98 <string>Item Number</string>
110 <weight>normal</weight>
126 <weight>bold</weight>
130 <string>Substitute Availability By Root Item</string>
142 <weight>bold</weight>
148 <column>item_descrip1</column>
161 <weight>normal</weight>
165 <string>Reorder Level</string>
177 <weight>bold</weight>
183 <column>item_descrip2</column>
203 <weight>normal</weight>
207 <string>Allocated</string>
219 <weight>bold</weight>
225 <column>item_number</column>
238 <weight>normal</weight>
242 <string>Available</string>
254 <weight>normal</weight>
258 <string>Site</string>
270 <weight>normal</weight>
274 <string>Description</string>
286 <weight>normal</weight>
302 <weight>normal</weight>
306 <string>On Order</string>
318 <weight>normal</weight>
322 <string>Site:</string>
334 <weight>normal</weight>
338 <string>Item Number:</string>
357 <weight>normal</weight>
361 <string>Description</string>
380 <weight>normal</weight>
384 <string>Allocated</string>
396 <weight>normal</weight>
400 <string>Site</string>
412 <weight>normal</weight>
416 <string>Item Number</string>
428 <weight>normal</weight>
432 <string>Reorder Level</string>
444 <weight>normal</weight>
460 <weight>normal</weight>
476 <weight>normal</weight>
480 <string>Available</string>
492 <weight>normal</weight>
496 <string>On Order</string>
503 <query>detail</query>
516 <weight>bold</weight>
521 <query>detail</query>
522 <column>item_descrip2</column>
535 <weight>bold</weight>
540 <query>detail</query>
541 <column>item_descrip1</column>
554 <weight>bold</weight>
559 <query>detail</query>
560 <column>warehous_code</column>
573 <weight>bold</weight>
578 <query>detail</query>
579 <column>f_qtyonhand</column>
592 <weight>bold</weight>
597 <query>detail</query>
598 <column>leadtime</column>
611 <weight>bold</weight>
616 <query>detail</query>
617 <column>f_avail</column>
630 <weight>bold</weight>
635 <query>detail</query>
636 <column>f_ordered</column>
649 <weight>bold</weight>
654 <query>detail</query>
655 <column>f_reorderlevel</column>
668 <weight>bold</weight>
673 <query>detail</query>
674 <column>f_allocated</column>
687 <weight>bold</weight>
692 <query>detail</query>
693 <column>item_number</column>
717 <weight>bold</weight>
722 <query>Context Query</query>
723 <column>report_date</column>
736 <weight>normal</weight>
740 <string>Page:</string>
752 <weight>normal</weight>
756 <string>Report Date:</string>
768 <weight>bold</weight>
773 <query>Context Query</query>
774 <column>page_number</column>