3 CREATE OR REPLACE FUNCTION x_fix_recv_post(i_id int)
12 v_itemlocdist_series INTEGER;
19 RAISE NOTICE 'Fixing recv_id %', i_id;
20 -- check current status...
29 invhist_ordnumber = CONCAT(pohead_number, '-', poitem_linenumber)
33 invhist_transtype = 'RP'
35 invhist_ordtype = 'PO'
37 invhist_itemsite_id = recv_itemsite_id
39 ),0.0) as total_tx_qty
48 recvgrp_id = recv_recvgrp_id
52 poitem_id = recv_orderitem_id
56 pohead_id = poitem_pohead_id
60 itemsite_id = recv_itemsite_id
64 item_id = itemsite_item_id
68 (recvgrp_void IS NULL OR recvgrp_void = 0)
71 recv_recvgrp_id is not null;
74 RAISE NOTICE 'skip - not found';
77 IF _r.total_tx_qty > 0.0 THEN
78 RAISE NOTICE 'skip - total recived is > 0';
82 IF _r.recv_qty < 1.0 THEN
83 RAISE NOTICE 'skip - total in recv is < 1';
87 UPDATE recv SET recv_posted = false where recv_id = i_id;
89 -- update the poitem..
92 SELECT postReceipt(recv_id, 0) INTO v_itemlocdist_series FROM recv WHERE (recv_id=i_id);
93 IF v_itemlocdist_series < 1 THEN
94 RAISE EXCEPTION 'postReceipt returned %', v_itemlocdist_series ;
101 SELECT itemlocdist_id,
102 itemlocdist_reqlotserial,
103 itemlocdist_distlotserial,
106 itemsite_controlmethod,
109 COALESCE(itemsite_lsseq_id,-1) AS itemsite_lsseq_id,
110 COALESCE(itemlocdist_source_id,-1) AS itemlocdist_source_id
113 FROM itemlocdist, itemsite
114 WHERE ( (itemlocdist_itemsite_id=itemsite_id) AND (itemlocdist_series=v_itemlocdist_series) ) ORDER BY itemlocdist_id;
116 if _loc.itemlocdist_id < 1 THEN
117 RAISE EXCEPTION 'SEARCHING FOR itemlocdist_id returned % FOR %' , _loc.itemlocdist_id , _r.item_number;
121 INSERT INTO itemlocdist (
122 itemlocdist_itemlocdist_id, itemlocdist_source_type,
123 itemlocdist_source_id, itemlocdist_qty,
124 itemlocdist_ls_id, itemlocdist_expiration
127 _r.recvgrp_location_id , _r.recv_qty,
128 itemlocdist_ls_id, endOfTime()
129 FROM itemlocdist WHERE
130 (itemlocdist_id=_loc.itemlocdist_id );
132 SELECT distributeToLocations(_loc.itemlocdist_id) INTO v_return;
134 RAISE EXCEPTION 'distribute to locations failed: %',v_return;
136 SELECT postItemlocseries(v_itemlocdist_series) INTO v_breturn;
137 if NOT v_breturn THEN
138 RAISE EXCEPTION 'postItemlocseries failed: %', v_breturn;
144 poitem_qty_received =
150 invhist_ordnumber = CONCAT(_r.pohead_number, '-', _r.poitem_linenumber)
154 invhist_transtype = 'RP'
156 invhist_ordtype = 'PO'
158 invhist_itemsite_id = _r.recv_itemsite_id
164 poitem_qty_returned =
170 invhist_ordnumber = CONCAT(_r.pohead_number, '-', _r.poitem_linenumber)
174 invhist_transtype = 'RP'
176 invhist_ordtype = 'PO'
178 invhist_itemsite_id = _r.recv_itemsite_id
184 WHERE poitem_id = _r.poitem_id;
187 RETURN v_itemlocdist_series;
191 LANGUAGE plpgsql VOLATILE
193 ALTER FUNCTION x_fix_recv_post(int)