From 5e50e3d15d2ffe258fe39ae68090c4e6df732d93 Mon Sep 17 00:00:00 2001 From: erickson Date: Thu, 1 Apr 2010 15:29:39 +0000 Subject: [PATCH] added a view which returns all lineitem details that are ready to be claimed. returns po, lineitem, lineitem_detail, and claim policy action data git-svn-id: svn://svn.open-ils.org/ILS/trunk@16085 dcc99617-32d9-48b4-a31d-7c20da2025e4 --- Open-ILS/examples/fm_IDL.xml | 61 ++++++++++++++++++++++++++++++++++++ 1 file changed, 61 insertions(+) diff --git a/Open-ILS/examples/fm_IDL.xml b/Open-ILS/examples/fm_IDL.xml index 7ccde24282..0497560e0a 100644 --- a/Open-ILS/examples/fm_IDL.xml +++ b/Open-ILS/examples/fm_IDL.xml @@ -7079,6 +7079,67 @@ Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301, USA + + + + SELECT + po.ordering_agency AS ordering_agency, + po.id AS purchase_order, + li.id AS lineitem, + lid.id AS lineitem_detail, + cpa.id AS claim_policy_action + FROM + acq.lineitem_detail lid + JOIN acq.lineitem li ON (li.id = lid.lineitem) + JOIN acq.purchase_order po ON (po.id = li.purchase_order) + JOIN acq.claim_policy cp ON (li.claim_policy = cp.id) + JOIN acq.claim_policy_action cpa ON ( + cpa.claim_policy = cp.id + + -- we only care about claim policy actions whose claim + -- interval we'd reached or exceeded + AND (NOW() - cpa.action_interval) > po.order_date + + -- filter out all claim policy actions where claim events + -- have occurred on or after the action's action_interval + AND NOT EXISTS ( + SELECT 1 + FROM + acq.claim_event evt + JOIN acq.claim claim ON ( + claim.id = evt.claim + AND claim.lineitem_detail = lid.id + ) + WHERE + evt.event_date >= (po.order_date + cpa.action_interval) + ) + ) + WHERE + lid.cancel_reason IS NULL + AND li.cancel_reason IS NULL -- belt/suspenders + AND po.cancel_reason IS NULL -- belt/suspenders + AND lid.recv_time IS NULL + AND po.state = 'on-order' + ORDER BY 1, 2, 3, 4, 5 + + + + + + + + + + + + + + + + + + + -- 2.43.2