1 CREATE OR REPLACE FUNCTION distributeItemlocSeries(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.
5 pItemlocSeries ALIAS FOR $1;
11 _debug BOOLEAN := true;
15 RAISE NOTICE 'distributeItemlocSeries, series=%', pItemlocSeries;
20 -- March through all of the itemlocdists for pItemlocSeries
21 FOR _itemlocdist IN SELECT itemlocdist_id AS itemlocdistid,
22 itemlocdist_source_type AS type,
23 itemlocdist_source_id AS sourceid,
24 itemlocdist_qty AS qty,
25 itemlocdist_itemsite_id AS itemsiteid,
27 itemlocdist_invhist_id AS invhistid,
28 itemlocdist_ls_id AS lotserialid,
29 itemlocdist_expiration AS expiration,
31 itemlocdist_warranty AS warranty,
32 itemlocdist_series AS series
33 FROM itemlocdist, itemsite
34 WHERE ( (itemlocdist_itemsite_id=itemsite_id)
35 AND (itemlocdist_series=pItemlocSeries) )
36 ORDER BY itemlocdist_flush DESC LOOP
38 _distCounter := _distCounter + 1;
40 RAISE NOTICE 'itemlocdist loop %', _distCounter;
41 RAISE NOTICE 'itemlocdistid=%', _itemlocdist.itemlocdistid;
42 RAISE NOTICE 'type=%', _itemlocdist.type;
43 RAISE NOTICE 'sourceid=%', _itemlocdist.sourceid;
44 RAISE NOTICE 'qty=%', _itemlocdist.qty;
45 RAISE NOTICE 'itemsiteid=%', _itemlocdist.itemsiteid;
46 RAISE NOTICE 'freeze=%', _itemlocdist.itemsite_freeze;
47 RAISE NOTICE 'invhistid=%', _itemlocdist.invhistid;
48 RAISE NOTICE 'lotserialid=%', _itemlocdist.lotserialid;
49 RAISE NOTICE 'expiration=%', _itemlocdist.expiration;
50 RAISE NOTICE 'flush=%', _itemlocdist.itemlocdist_flush;
51 RAISE NOTICE 'warranty=%', _itemlocdist.warranty;
54 -- Commit invhist to itemsite
55 IF (NOT _itemlocdist.itemsite_freeze) THEN
56 PERFORM postInvHist(_itemlocdist.invhistid);
59 -- Mark the invhist tuple for the itemlocdist in question as having detail
61 SET invhist_hasdetail=TRUE
62 WHERE ( (NOT invhist_hasdetail)
63 AND (invhist_id=_itemlocdist.invhistid) );
65 -- If this itemlocdist is a flush, write a invdetail tuple that records the empty
66 IF (_itemlocdist.itemlocdist_flush) THEN
68 ( invdetail_invhist_id, invdetail_location_id, invdetail_ls_id,
69 invdetail_qty, invdetail_qty_before, invdetail_qty_after, invdetail_expiration,
71 SELECT _itemlocdist.invhistid, itemloc_location_id, itemloc_ls_id,
72 (itemloc_qty * -1), itemloc_qty, 0, itemloc_expiration,
75 WHERE ( (itemloc_qty <> 0)
76 AND (itemloc_id=_itemlocdist.sourceid) );
78 -- Delete the flushed itemloc if its parent itemsite is not frozen
79 IF (NOT _itemlocdist.itemsite_freeze) THEN
81 WHERE (itemloc_id=_itemlocdist.sourceid);
85 -- If this is a location type distribution, check to see if the target itemloc
87 IF (_itemlocdist.type = 'L') THEN
88 SELECT itemloc_id INTO _itemlocid
90 WHERE ( (itemloc_itemsite_id=_itemlocdist.itemsiteid)
91 AND (itemloc_location_id=_itemlocdist.sourceid)
92 AND (COALESCE(itemloc_ls_id,-1)=COALESCE(_itemlocdist.lotserialid,-1))
93 AND (COALESCE(itemloc_expiration,endOfTime())=COALESCE(_itemlocdist.expiration,endOfTime()))
94 AND (COALESCE(itemloc_warrpurc,endoftime())=COALESCE(_itemlocdist.warranty,endoftime())) );
98 SELECT NEXTVAL('itemloc_itemloc_id_seq') INTO _itemlocid;
101 ( itemloc_id, itemloc_itemsite_id,
102 itemloc_location_id, itemloc_qty,
103 itemloc_ls_id, itemloc_expiration,
106 ( _itemlocid, _itemlocdist.itemsiteid,
107 _itemlocdist.sourceid, 0,
108 _itemlocdist.lotserialid, _itemlocdist.expiration,
109 _itemlocdist.warranty );
113 _itemlocid = _itemlocdist.sourceid;
115 IF (_itemlocid IS NOT NULL AND (SELECT count(itemloc_id) = 0 FROM itemloc WHERE itemloc_id=_itemlocid)) THEN
116 RAISE EXCEPTION 'No record to distribute against. Someone else may have already distributed this record.';
120 -- Record the invdetail
121 INSERT INTO invdetail
122 (invdetail_invhist_id, invdetail_location_id, invdetail_ls_id,
123 invdetail_qty, invdetail_qty_before, invdetail_qty_after, invdetail_expiration,
125 SELECT _itemlocdist.invhistid, itemloc_location_id, _itemlocdist.lotserialid,
126 _itemlocdist.qty, itemloc_qty, (itemloc_qty + _itemlocdist.qty),
127 itemloc_expiration,_itemlocdist.warranty
129 WHERE (itemloc_id=_itemlocid);
131 -- Update the itemloc_qty if its parent itemsite is not frozen
132 IF (NOT _itemlocdist.itemsite_freeze) THEN
134 SET itemloc_qty = (itemloc_qty + _itemlocdist.qty)
135 WHERE (itemloc_id=_itemlocid);
138 -- Adjust QOH if this itemlocdist is to/from a non-netable location
139 IF ( SELECT (NOT location_netable)
140 FROM itemloc, location
141 WHERE ( (itemloc_location_id=location_id)
142 AND (itemloc_id=_itemlocid) ) ) THEN
144 -- Record the netable->non-netable (or visaveras) invhist
145 SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;
147 ( invhist_id, invhist_itemsite_id,
148 invhist_transtype, invhist_invqty,
149 invhist_qoh_before, invhist_qoh_after,
150 invhist_docnumber, invhist_comments,
151 invhist_invuom, invhist_unitcost,
152 invhist_costmethod, invhist_value_before, invhist_value_after,
154 SELECT _invhistid, itemsite_id,
155 'NN', (_itemlocdist.qty * -1),
156 itemsite_qtyonhand, (itemsite_qtyonhand - _itemlocdist.qty),
157 invhist_docnumber, invhist_comments,
158 uom_name, stdCost(item_id),
159 itemsite_costmethod, itemsite_value,
160 (itemsite_value + (_itemlocdist.qty * -1 * CASE WHEN(itemsite_costmethod='A') THEN avgcost(itemsite_id)
161 ELSE stdCost(itemsite_item_id)
164 FROM item, itemsite, invhist, uom
165 WHERE ((itemsite_item_id=item_id)
166 AND (item_inv_uom_id=uom_id)
167 AND (itemsite_controlmethod <> 'N')
168 AND (itemsite_id=_itemlocdist.itemsiteid)
169 AND (invhist_id=_itemlocdist.invhistid));
171 -- Adjust the parent itemsite
172 IF (NOT _itemlocdist.itemsite_freeze) THEN
174 SET itemsite_qtyonhand = (itemsite_qtyonhand - _itemlocdist.qty),
175 itemsite_nnqoh = (itemsite_nnqoh + _itemlocdist.qty)
177 WHERE ((itemloc_itemsite_id=itemsite_id)
178 AND (itemloc_id=_itemlocid));
184 -- If, after the distribution, the target itemloc_qty = 0, delete the itemloc
185 -- if its parent itemsite is not frozen
186 IF (NOT _itemlocdist.itemsite_freeze) THEN
188 WHERE ( (itemloc_qty=0)
189 AND (itemloc_id=_itemlocid) );
194 DELETE FROM itemlocdist
195 WHERE (itemlocdist_series=pItemlocSeries);
200 $$ LANGUAGE 'plpgsql';