2 -- Name: purchaserequests
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 SELECT pr_id, itemsite_id, itemsite_qtyonhand, itemsite_reorderlevel,
8 item_number, (item_descrip1 || ' ' || item_descrip2) AS description,
10 CASE WHEN (pr_order_type='W') THEN ('W/O ' || ( SELECT formatWoNumber(womatl_wo_id)
12 WHERE (womatl_id=pr_order_id) ) )
13 WHEN (pr_order_type='S') THEN ('S/O ' || (SELECT formatSoNumber(pr_order_id)))
14 WHEN (pr_order_type='F') THEN ('Planned Order')
15 WHEN (pr_order_type='M') THEN <? value("manual") ?>
16 ELSE <? value("other") ?>
18 'qty' AS itemsite_qtyonhand_xtnumericrole,
19 'qty' AS itemsite_reorderlevel_xtnumericrole,
20 'qty' AS pr_qtyreq_xtnumericrole
21 FROM pr, itemsite, item
22 WHERE ((pr_itemsite_id=itemsite_id)
23 AND (itemsite_item_id=item_id)
24 <? if exists("startDate") ?>
25 AND (pr_duedate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
27 <? if exists("warehous_id") ?>
28 AND (itemsite_warehous_id=<? value("warehous_id") ?>)
30 <? if exists("item_id") ?>
31 AND (itemsite_item_id=<? value("item_id") ?>)
32 <? elseif exists("plancode_id") ?>
33 AND (itemsite_plancode_id=<? value("plancode_id") ?>)
34 <? elseif exists("plancode_pattern") ?>
35 AND (itemsite_plancode_id IN (SELECT plancode_id FROM
37 WHERE (plancode_code ~ <? value("plancode_pattern") ?>) ) )