1 CREATE OR REPLACE FUNCTION distributeItemlocSeries(pItemlocSeries INTEGER) RETURNS INTEGER AS $$
2 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
3 -- See www.xtuple.com/CPAL for the full text of the software license.
10 _debug BOOLEAN := true;
14 RAISE NOTICE 'distributeItemlocSeries, series=%', pItemlocSeries;
19 -- March through all of the itemlocdists for pItemlocSeries
20 FOR _itemlocdist IN SELECT itemlocdist_id AS itemlocdistid,
21 itemlocdist_source_type AS type,
22 itemlocdist_source_id AS sourceid,
23 itemlocdist_qty AS qty,
24 itemlocdist_itemsite_id AS itemsiteid,
26 itemlocdist_invhist_id AS invhistid,
27 itemlocdist_ls_id AS lotserialid,
28 itemlocdist_expiration AS expiration,
30 itemlocdist_warranty AS warranty,
31 itemlocdist_series AS series
32 FROM itemlocdist, itemsite
33 WHERE ( (itemlocdist_itemsite_id=itemsite_id)
34 AND (itemlocdist_series=pItemlocSeries) )
35 ORDER BY itemlocdist_flush DESC LOOP
37 _distCounter := _distCounter + 1;
39 RAISE NOTICE 'itemlocdist loop %', _distCounter;
40 RAISE NOTICE 'itemlocdistid=%', _itemlocdist.itemlocdistid;
41 RAISE NOTICE 'type=%', _itemlocdist.type;
42 RAISE NOTICE 'sourceid=%', _itemlocdist.sourceid;
43 RAISE NOTICE 'qty=%', _itemlocdist.qty;
44 RAISE NOTICE 'itemsiteid=%', _itemlocdist.itemsiteid;
45 RAISE NOTICE 'freeze=%', _itemlocdist.itemsite_freeze;
46 RAISE NOTICE 'invhistid=%', _itemlocdist.invhistid;
47 RAISE NOTICE 'lotserialid=%', _itemlocdist.lotserialid;
48 RAISE NOTICE 'expiration=%', _itemlocdist.expiration;
49 RAISE NOTICE 'flush=%', _itemlocdist.itemlocdist_flush;
50 RAISE NOTICE 'warranty=%', _itemlocdist.warranty;
53 -- Commit invhist to itemsite
54 IF (NOT _itemlocdist.itemsite_freeze) THEN
55 PERFORM postInvHist(_itemlocdist.invhistid);
58 -- Mark the invhist tuple for the itemlocdist in question as having detail
60 SET invhist_hasdetail=TRUE
61 WHERE ( (NOT invhist_hasdetail)
62 AND (invhist_id=_itemlocdist.invhistid) );
64 -- If this itemlocdist is a flush, write a invdetail tuple that records the empty
65 IF (_itemlocdist.itemlocdist_flush) THEN
67 ( invdetail_invhist_id, invdetail_location_id, invdetail_ls_id,
68 invdetail_qty, invdetail_qty_before, invdetail_qty_after, invdetail_expiration,
70 SELECT _itemlocdist.invhistid, itemloc_location_id, itemloc_ls_id,
71 (itemloc_qty * -1), itemloc_qty, 0, itemloc_expiration,
74 WHERE ( (itemloc_qty <> 0)
75 AND (itemloc_id=_itemlocdist.sourceid) );
77 -- Delete the flushed itemloc if its parent itemsite is not frozen
78 IF (NOT _itemlocdist.itemsite_freeze) THEN
80 WHERE (itemloc_id=_itemlocdist.sourceid);
84 -- If this is a location type distribution, check to see if the target itemloc
86 IF (_itemlocdist.type = 'L') THEN
87 SELECT itemloc_id INTO _itemlocid
89 WHERE ( (itemloc_itemsite_id=_itemlocdist.itemsiteid)
90 AND (itemloc_location_id=_itemlocdist.sourceid)
91 AND (COALESCE(itemloc_ls_id,-1)=COALESCE(_itemlocdist.lotserialid,-1))
92 AND (COALESCE(itemloc_expiration,endOfTime())=COALESCE(_itemlocdist.expiration,endOfTime()))
93 AND (COALESCE(itemloc_warrpurc,endoftime())=COALESCE(_itemlocdist.warranty,endoftime())) );
97 SELECT NEXTVAL('itemloc_itemloc_id_seq') INTO _itemlocid;
100 ( itemloc_id, itemloc_itemsite_id,
101 itemloc_location_id, itemloc_qty,
102 itemloc_ls_id, itemloc_expiration,
105 ( _itemlocid, _itemlocdist.itemsiteid,
106 _itemlocdist.sourceid, 0,
107 _itemlocdist.lotserialid, _itemlocdist.expiration,
108 _itemlocdist.warranty );
112 _itemlocid = _itemlocdist.sourceid;
114 IF (_itemlocid IS NOT NULL AND (SELECT count(itemloc_id) = 0 FROM itemloc WHERE itemloc_id=_itemlocid)) THEN
115 RAISE EXCEPTION 'No record to distribute against. Someone else may have already distributed this record.';
119 -- Record the invdetail
120 INSERT INTO invdetail
121 (invdetail_invhist_id, invdetail_location_id, invdetail_ls_id,
122 invdetail_qty, invdetail_qty_before, invdetail_qty_after, invdetail_expiration,
124 SELECT _itemlocdist.invhistid, itemloc_location_id, _itemlocdist.lotserialid,
125 _itemlocdist.qty, itemloc_qty, (itemloc_qty + _itemlocdist.qty),
126 itemloc_expiration,_itemlocdist.warranty
128 WHERE (itemloc_id=_itemlocid);
130 -- Update the itemloc_qty if its parent itemsite is not frozen
131 IF (NOT _itemlocdist.itemsite_freeze) THEN
133 SET itemloc_qty = (itemloc_qty + _itemlocdist.qty)
134 WHERE (itemloc_id=_itemlocid);
139 -- If, after the distribution, the target itemloc_qty = 0, delete the itemloc
140 -- if its parent itemsite is not frozen
141 IF (NOT _itemlocdist.itemsite_freeze) THEN
143 WHERE ( (itemloc_qty=0)
144 AND (itemloc_id=_itemlocid) );
149 DELETE FROM itemlocdist
150 WHERE (itemlocdist_series=pItemlocSeries);