1 CREATE OR REPLACE FUNCTION initialDistribution(pItemsiteid INTEGER,
2 pLocationid INTEGER) RETURNS INTEGER AS $$
3 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
4 -- See www.xtuple.com/CPAL for the full text of the software license.
8 _itemlocSeries INTEGER;
13 -- Make sure the passed itemsite points to a real item
14 IF ( (SELECT (item_type IN ('R', 'F') OR itemsite_costmethod = 'J')
16 WHERE ( (itemsite_item_id=item_id)
17 AND (itemsite_id=pItemsiteid) ) ) ) THEN
21 _itemlocSeries := NEXTVAL('itemloc_series_seq');
23 -- Reassign the location_id for all existing itemlocs if
24 -- the passed itemsite is already lot/serial controlled
25 IF ( ( SELECT (itemsite_controlmethod IN ('L', 'S'))
27 WHERE (itemsite_id=pItemsiteid) ) ) THEN
29 FOR _r IN SELECT itemloc_id, itemloc_ls_id, itemloc_qty
31 WHERE (itemloc_itemsite_id=pItemsiteid) LOOP
33 -- Create the RL transaction
34 SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;
36 ( invhist_id, invhist_itemsite_id, invhist_series,
37 invhist_transtype, invhist_invqty,
38 invhist_qoh_before, invhist_qoh_after,
40 invhist_invuom, invhist_unitcost, invhist_hasdetail,
41 invhist_costmethod, invhist_value_before, invhist_value_after )
42 SELECT _invhistid, itemsite_id, _itemlocSeries,
44 _r.itemloc_qty, _r.itemloc_qty,
45 'Initial Distribution',
46 uom_name, stdCost(item_id), TRUE,
47 itemsite_costmethod, itemsite_value, itemsite_value
48 FROM item, itemsite, uom
49 WHERE ( (itemsite_item_id=item_id)
50 AND (item_inv_uom_id=uom_id)
51 AND (itemsite_controlmethod <> 'N')
52 AND (itemsite_id=pItemsiteid) );
56 SET itemloc_location_id=pLocationid
57 WHERE (itemloc_id=_r.itemloc_id);
59 -- Record the detail transaction
61 ( invdetail_invhist_id, invdetail_location_id, invdetail_ls_id,
62 invdetail_qty, invdetail_qty_before, invdetail_qty_after )
64 ( _invhistid, pLocationid, _r.itemloc_ls_id,
65 _r.itemloc_qty, 0, _r.itemloc_qty );
70 -- The passed itemsite is not lot/serial controlled
71 -- Make sure that there are not any stagnent itemlocs
73 WHERE (itemloc_itemsite_id=pItemsiteid);
75 -- Create the RL transaction
76 SELECT NEXTVAL('invhist_invhist_id_seq') INTO _invhistid;
78 ( invhist_id, invhist_itemsite_id, invhist_series,
79 invhist_transtype, invhist_invqty,
80 invhist_qoh_before, invhist_qoh_after,
82 invhist_invuom, invhist_unitcost, invhist_hasdetail,
83 invhist_costmethod, invhist_value_before, invhist_value_after )
84 SELECT _invhistid, itemsite_id, _itemlocSeries,
86 itemsite_qtyonhand, itemsite_qtyonhand,
87 'Initial Distribution',
88 uom_name, stdCost(item_id), TRUE,
89 itemsite_costmethod, itemsite_value, itemsite_value
90 FROM item, itemsite, uom
91 WHERE ( (itemsite_item_id=item_id)
92 AND (item_inv_uom_id=uom_id)
93 AND (itemsite_controlmethod <> 'N')
94 AND (itemsite_id=pItemsiteid) );
97 SELECT NEXTVAL('itemloc_itemloc_id_seq') INTO _itemlocid;
99 ( itemloc_id, itemloc_itemsite_id, itemloc_location_id,
100 itemloc_expiration, itemloc_qty )
101 SELECT _itemlocid, itemsite_id, pLocationid,
102 endOfTime(), itemsite_qtyonhand
104 WHERE (itemsite_id=pItemsiteid);
106 -- Record the detail transaction
107 INSERT INTO invdetail
108 ( invdetail_invhist_id, invdetail_location_id,
109 invdetail_qty, invdetail_qty_before, invdetail_qty_after )
110 SELECT _invhistid, pLocationid,
111 itemsite_qtyonhand, 0, itemsite_qtyonhand
113 WHERE (itemsite_id=pItemsiteid);