1 CREATE OR REPLACE FUNCTION copyItemSite(pitemsiteid INTEGER,
2 pdestwhsid 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 RETURN copyItemSite(pitemsiteid, pdestwhsid, NULL);
11 $$ LANGUAGE 'plpgsql';
14 CREATE OR REPLACE FUNCTION copyItemSite(pitemsiteid INTEGER,
16 pdestitemid INTEGER) RETURNS INTEGER AS $$
17 -- Copyright (c) 1999-2014 by OpenMFG LLC, d/b/a xTuple.
18 -- See www.xtuple.com/CPAL for the full text of the software license.
20 pitemsiteid ALIAS FOR $1;
21 pdestwhsid ALIAS FOR $2;
22 _destwhs whsinfo%ROWTYPE;
23 _new itemsite%ROWTYPE;
24 _supplywarehousid INTEGER := NULL;
27 -- make a copy of the old itemsite
30 WHERE (itemsite_id=pitemsiteid);
35 -- if there is no dest warehouse then perhaps the user is manually copying it
36 IF (pdestwhsid IS NOT NULL) THEN
37 SELECT * INTO _destwhs
39 WHERE (warehous_id=pdestwhsid);
45 IF (NOT checkPrivilege('MaintainItemSites')) THEN
49 SELECT itemsite_id INTO _new.itemsite_id
51 WHERE ((itemsite_item_id=COALESCE(pdestitemid, _new.itemsite_item_id))
52 AND (itemsite_warehous_id=pdestwhsid OR
53 (itemsite_warehous_id IS NULL AND pdestwhsid IS NULL)));
55 RETURN _new.itemsite_id;
58 -- find supply itemsite
59 IF (_new.itemsite_supply_itemsite_id IS NOT NULL) THEN
60 SELECT itemsite_warehous_id INTO _supplywarehousid
62 WHERE (itemsite_id=_new.itemsite_supply_itemsite_id);
64 SELECT itemsite_id INTO _new.itemsite_supply_itemsite_id
66 WHERE (itemsite_warehous_id=_supplywarehousid)
67 AND (itemsite_item_id=pdestitemid);
71 -- now override the things we know have to change
72 _new.itemsite_id := NEXTVAL('itemsite_itemsite_id_seq');
73 _new.itemsite_warehous_id := pdestwhsid;
74 _new.itemsite_qtyonhand := 0;
75 _new.itemsite_value := 0;
76 _new.itemsite_datelastcount := NULL;
77 _new.itemsite_datelastused := NULL;
78 _new.itemsite_nnqoh := 0;
79 _new.itemsite_location_id := -1;
80 _new.itemsite_recvlocation_id := -1;
81 _new.itemsite_issuelocation_id := -1;
82 _new.itemsite_location_dist := FALSE;
83 _new.itemsite_recvlocation_dist := FALSE;
84 _new.itemsite_issuelocation_dist := FALSE;
86 IF (_destwhs.warehous_transit) THEN
87 _new.itemsite_reorderlevel := 0;
88 _new.itemsite_ordertoqty := 0;
89 _new.itemsite_soldranking := NULL;
90 _new.itemsite_posupply := FALSE;
91 _new.itemsite_wosupply := FALSE;
92 _new.itemsite_loccntrl := FALSE;
93 _new.itemsite_safetystock := 0;
94 _new.itemsite_minordqty := 0;
95 _new.itemsite_multordqty := 0;
96 _new.itemsite_leadtime := 0;
97 _new.itemsite_controlmethod := 'R';
98 IF(_new.itemsite_costmethod='N') THEN
99 _new.itemsite_costmethod := 'S';
101 _new.itemsite_active := TRUE;
102 -- ? _new.itemsite_plancode_id := -1;
103 -- ? _new.itemsite_costcat_id := -1;
104 _new.itemsite_eventfence := 1;
105 _new.itemsite_sold := FALSE;
106 _new.itemsite_stocked := FALSE;
107 _new.itemsite_location_id := -1;
108 _new.itemsite_useparams := FALSE;
109 _new.itemsite_useparamsmanual := FALSE;
110 _new.itemsite_createpr := FALSE;
111 _new.itemsite_location := NULL;
112 _new.itemsite_location_comments := NULL;
113 _new.itemsite_notes := 'Transit Warehouse';
114 _new.itemsite_nnqoh := 0;
115 _new.itemsite_createwo := FALSE;
116 _new.itemsite_costcat_id := _destwhs.warehous_costcat_id;
117 _new.itemsite_supply_itemsite_id := NULL;
120 INSERT INTO itemsite (
121 itemsite_id, itemsite_item_id,
122 itemsite_warehous_id, itemsite_qtyonhand,
123 itemsite_costmethod, itemsite_value,
124 itemsite_reorderlevel, itemsite_ordertoqty,
125 itemsite_cyclecountfreq, itemsite_datelastcount,
126 itemsite_datelastused,
127 itemsite_posupply, itemsite_wosupply,
129 itemsite_safetystock, itemsite_minordqty,
130 itemsite_multordqty, itemsite_leadtime,
131 itemsite_abcclass, itemsite_issuemethod,
132 itemsite_controlmethod, itemsite_active,
133 itemsite_plancode_id, itemsite_costcat_id,
134 itemsite_eventfence, itemsite_sold,
135 itemsite_stocked, itemsite_freeze,
136 itemsite_location_id,
137 itemsite_useparams, itemsite_useparamsmanual,
138 itemsite_soldranking, itemsite_createpr,
139 itemsite_location, itemsite_location_comments,
140 itemsite_notes, itemsite_perishable,
141 itemsite_nnqoh, itemsite_autoabcclass,
142 itemsite_ordergroup, itemsite_disallowblankwip,
143 itemsite_maxordqty, itemsite_mps_timefence,
144 itemsite_createwo, itemsite_warrpurc,
146 itemsite_planning_type, itemsite_supply_itemsite_id,
147 itemsite_createsopr, itemsite_createsopo,
149 itemsite_recvlocation_id, itemsite_issuelocation_id,
150 itemsite_location_dist, itemsite_recvlocation_dist,
151 itemsite_issuelocation_dist
153 _new.itemsite_id, COALESCE(pdestitemid, _new.itemsite_item_id),
154 _new.itemsite_warehous_id, _new.itemsite_qtyonhand,
155 _new.itemsite_costmethod, _new.itemsite_value,
156 _new.itemsite_reorderlevel, _new.itemsite_ordertoqty,
157 _new.itemsite_cyclecountfreq, _new.itemsite_datelastcount,
158 _new.itemsite_datelastused,
159 _new.itemsite_posupply, _new.itemsite_wosupply,
160 _new.itemsite_loccntrl,
161 _new.itemsite_safetystock, _new.itemsite_minordqty,
162 _new.itemsite_multordqty, _new.itemsite_leadtime,
163 _new.itemsite_abcclass, _new.itemsite_issuemethod,
164 _new.itemsite_controlmethod, _new.itemsite_active,
165 _new.itemsite_plancode_id, _new.itemsite_costcat_id,
166 _new.itemsite_eventfence, _new.itemsite_sold,
167 _new.itemsite_stocked, _new.itemsite_freeze,
168 _new.itemsite_location_id,
169 _new.itemsite_useparams, _new.itemsite_useparamsmanual,
170 _new.itemsite_soldranking, _new.itemsite_createpr,
171 _new.itemsite_location, _new.itemsite_location_comments,
172 _new.itemsite_notes, _new.itemsite_perishable,
173 _new.itemsite_nnqoh, _new.itemsite_autoabcclass,
174 _new.itemsite_ordergroup, _new.itemsite_disallowblankwip,
175 _new.itemsite_maxordqty, _new.itemsite_mps_timefence,
176 _new.itemsite_createwo, _new.itemsite_warrpurc,
177 _new.itemsite_autoreg,
178 _new.itemsite_planning_type, _new.itemsite_supply_itemsite_id,
179 _new.itemsite_createsopr, _new.itemsite_createsopo,
180 _new.itemsite_dropship,
181 _new.itemsite_recvlocation_id, _new.itemsite_issuelocation_id,
182 _new.itemsite_location_dist, _new.itemsite_recvlocation_dist,
183 _new.itemsite_issuelocation_dist
186 RETURN _new.itemsite_id;
188 $$ LANGUAGE 'plpgsql';