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 qtyNetable(itemsite_id) AS netableqoh, qtyNetable(itemsite_id, FALSE) AS nonnetableqoh,
9 item_number, (item_descrip1 || ' ' || item_descrip2) AS description,
11 CASE WHEN (pr_order_type='W') THEN ('W/O ' || ( SELECT formatWoNumber(womatl_wo_id)
13 WHERE (womatl_id=pr_order_id) ) )
14 WHEN (pr_order_type='S') THEN ('S/O ' || (SELECT formatSoNumber(pr_order_id)))
15 WHEN (pr_order_type='F') THEN ('Planned Order')
16 WHEN (pr_order_type='M') THEN <? value("manual") ?>
17 ELSE <? value("other") ?>
19 'qty' AS itemsite_qtyonhand_xtnumericrole,
20 'qty' AS itemsite_reorderlevel_xtnumericrole,
21 'qty' AS pr_qtyreq_xtnumericrole
22 FROM pr, itemsite, item
23 WHERE ((pr_itemsite_id=itemsite_id)
24 AND (itemsite_item_id=item_id)
25 <? if exists("startDate") ?>
26 AND (pr_duedate BETWEEN <? value("startDate") ?> AND <? value("endDate") ?>)
28 <? if exists("warehous_id") ?>
29 AND (itemsite_warehous_id=<? value("warehous_id") ?>)
31 <? if exists("item_id") ?>
32 AND (itemsite_item_id=<? value("item_id") ?>)
33 <? elseif exists("plancode_id") ?>
34 AND (itemsite_plancode_id=<? value("plancode_id") ?>)
35 <? elseif exists("plancode_pattern") ?>
36 AND (itemsite_plancode_id IN (SELECT plancode_id FROM
38 WHERE (plancode_code ~ <? value("plancode_pattern") ?>) ) )