1 CREATE OR REPLACE FUNCTION initialDistribution(INTEGER, 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 pItemsiteid ALIAS FOR $1;
6 pLocationid ALIAS FOR $2;
9 _itemlocSeries INTEGER;
14 -- Make sure the passed itemsite points to a real item
15 IF ( (SELECT (item_type IN ('R', 'F') OR itemsite_costmethod = 'J')
17 WHERE ( (itemsite_item_id=item_id)
18 AND (itemsite_id=pItemsiteid) ) ) ) THEN
22 _itemlocSeries := NEXTVAL('itemloc_series_seq');
24 -- Reassign the location_id for all existing itemlocs if
25 -- the passed itemsite is already lot/serial controlled
26 IF ( ( SELECT (itemsite_controlmethod IN ('L', 'S'))
28 WHERE (itemsite_id=pItemsiteid) ) ) THEN
30 FOR _r IN SELECT itemloc_id, itemloc_ls_id, itemloc_qty
32 WHERE (itemloc_itemsite_id=pItemsiteid) LOOP
34 -- Create the RL transaction
35 SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;
37 ( invhist_id, invhist_itemsite_id, invhist_series,
38 invhist_transtype, invhist_invqty,
39 invhist_qoh_before, invhist_qoh_after,
41 invhist_invuom, invhist_unitcost, invhist_hasdetail,
42 invhist_costmethod, invhist_value_before, invhist_value_after )
43 SELECT _invhistid, itemsite_id, _itemlocSeries,
45 _r.itemloc_qty, _r.itemloc_qty,
46 'Initial Distribution',
47 uom_name, stdCost(item_id), TRUE,
48 itemsite_costmethod, itemsite_value, itemsite_value
49 FROM item, itemsite, uom
50 WHERE ( (itemsite_item_id=item_id)
51 AND (item_inv_uom_id=uom_id)
52 AND (itemsite_controlmethod <> 'N')
53 AND (itemsite_id=pItemsiteid) );
57 SET itemloc_location_id=pLocationid
58 WHERE (itemloc_id=_r.itemloc_id);
60 -- Record the detail transaction
62 ( invdetail_invhist_id, invdetail_location_id, invdetail_ls_id,
63 invdetail_qty, invdetail_qty_before, invdetail_qty_after )
65 ( _invhistid, pLocationid, _r.itemloc_ls_id,
66 _r.itemloc_qty, 0, _r.itemloc_qty );
68 -- Adjust QOH if this itemlocdist is to/from a non-netable location
69 IF ( SELECT (NOT location_netable)
71 WHERE (location_id=pLocationid) ) THEN
74 ( invhist_itemsite_id, invhist_series,
75 invhist_transtype, invhist_invqty,
76 invhist_qoh_before, invhist_qoh_after,
78 invhist_invuom, invhist_unitcost,
79 invhist_costmethod, invhist_value_before, invhist_value_after )
80 SELECT itemsite_id, _itemlocSeries,
81 'NN', (_r.itemloc_qty * -1),
83 'Initial Distribution',
84 uom_name, stdCost(item_id),
85 itemsite_costmethod, itemsite_value, itemsite_value
86 FROM itemsite, item, uom
87 WHERE ( (itemsite_item_id=item_id)
88 AND (item_inv_uom_id=uom_id)
89 AND (itemsite_controlmethod <> 'N')
90 AND (itemsite_id=pItemsiteid) );
93 SET itemsite_nnqoh = (itemsite_nnqoh + _r.itemloc_qty),
94 itemsite_qtyonhand = (itemsite_qtyonhand - _r.itemloc_qty)
95 WHERE (itemsite_id=pItemsiteid);
102 -- The passed itemsite is not lot/serial controlled
103 -- Make sure that there are not any stagnent itemlocs
105 WHERE (itemloc_itemsite_id=pItemsiteid);
107 -- Create the RL transaction
108 SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;
110 ( invhist_id, invhist_itemsite_id, invhist_series,
111 invhist_transtype, invhist_invqty,
112 invhist_qoh_before, invhist_qoh_after,
114 invhist_invuom, invhist_unitcost, invhist_hasdetail,
115 invhist_costmethod, invhist_value_before, invhist_value_after )
116 SELECT _invhistid, itemsite_id, _itemlocSeries,
118 itemsite_qtyonhand, itemsite_qtyonhand,
119 'Initial Distribution',
120 uom_name, stdCost(item_id), TRUE,
121 itemsite_costmethod, itemsite_value, itemsite_value
122 FROM item, itemsite, uom
123 WHERE ( (itemsite_item_id=item_id)
124 AND (item_inv_uom_id=uom_id)
125 AND (itemsite_controlmethod <> 'N')
126 AND (itemsite_id=pItemsiteid) );
128 -- Create the itemloc
129 SELECT NEXTVAL('itemloc_itemloc_id_seq') INTO _itemlocid;
131 ( itemloc_id, itemloc_itemsite_id, itemloc_location_id,
132 itemloc_expiration, itemloc_qty )
133 SELECT _itemlocid, itemsite_id, pLocationid,
134 endOfTime(), itemsite_qtyonhand
136 WHERE (itemsite_id=pItemsiteid);
138 -- Record the detail transaction
139 INSERT INTO invdetail
140 ( invdetail_invhist_id, invdetail_location_id,
141 invdetail_qty, invdetail_qty_before, invdetail_qty_after )
142 SELECT _invhistid, pLocationid,
143 itemsite_qtyonhand, 0, itemsite_qtyonhand
145 WHERE (itemsite_id=pItemsiteid);
147 -- Adjust QOH if this itemlocdist is to/from a non-netable location
148 IF ( SELECT (NOT location_netable)
150 WHERE (location_id=pLocationid) ) THEN
153 ( invhist_itemsite_id, invhist_series,
154 invhist_transtype, invhist_invqty,
155 invhist_qoh_before, invhist_qoh_after,
157 invhist_invuom, invhist_unitcost,
158 invhist_costmethod, invhist_value_before, invhist_value_after )
159 SELECT itemsite_id, _itemlocSeries,
160 'NN', (itemloc_qty * -1),
162 'Initial Distribution',
163 uom_name, stdCost(item_id),
164 itemsite_costmethod, itemsite_value, itemsite_value
165 FROM itemloc, itemsite, item, uom
166 WHERE ( (itemsite_item_id=item_id)
167 AND (item_inv_uom_id=uom_id)
168 AND (itemsite_controlmethod <> 'N')
169 AND (itemloc_itemsite_id=itemsite_id)
170 AND (itemloc_id=_itemlocid) );
173 SET itemsite_nnqoh = itemsite_qtyonhand,
174 itemsite_qtyonhand = 0
176 WHERE ( (itemloc_itemsite_id=itemsite_id)
177 AND (itemloc_id=_itemlocid) );
186 $$ LANGUAGE 'plpgsql';