· 6 years ago · Nov 11, 2019, 03:24 PM
1-- FUNCTION: pb.f_confirm_goods_receipt(bigint, character varying, character varying, character varying, character varying, text)
2
3-- DROP FUNCTION pb.f_confirm_goods_receipt(bigint, character varying, character varying, character varying, character varying, text);
4
5CREATE OR REPLACE FUNCTION pb.f_confirm_goods_receipt(
6 bigint,
7 character varying,
8 character varying,
9 character varying,
10 character varying,
11 text)
12 RETURNS void
13 LANGUAGE 'plpgsql'
14
15 COST 100
16 VOLATILE
17 SET search_path='public, pb, mstr, batch'
18AS $BODY$
19DECLARE
20
21 pReceiveGoodsId ALIAS FOR $1;
22 pUsername ALIAS FOR $2;
23 pDateTime ALIAS FOR $3;
24 pSessionId ALIAS FOR $4;
25 pDateConfirm ALIAS FOR $5;
26 pRemarkConfirm ALIAS FOR $6;
27
28 vStatusRelease character varying := 'R';
29 vStatusFinal character varying := 'F';
30 vUnfinishedItem bigint;
31 vPoId bigint;
32 vVersion bigint;
33 vProductStatusGood character varying := 'GOOD';
34 vModeActionUpdate character varying := 'U';
35 vModeActionAdd character varying := 'A';
36 vEmptySpace character varying := '';
37 vBaseUomCode character varying := 'PCS';
38 vEmptyId bigint := -99;
39 vUserId bigint := -99;
40 vYes character varying := 'Y';
41 vNo character varying := 'N';
42
43BEGIN
44
45 SELECT ref_id, version INTO vPoId, vVersion FROM pu_receive_goods WHERE receive_goods_id = pReceiveGoodsId;
46 SELECT user_id INTO vUserId FROM t_user WHERE username = pUsername;
47
48 -- update status item menjadi R jika qty_rcv < qty_po
49 UPDATE pu_po_balance_item A SET status_item = vStatusRelease, version = A.version+1, update_datetime = pDateTime, update_username = pUsername
50 FROM pu_receive_goods_item B
51 WHERE A.po_item_id = B.ref_item_id AND
52 B.receive_goods_id = pReceiveGoodsId AND
53 A.qty_po - A.qty_cancel + A.qty_add - A.qty_rcv > 0;
54
55 -- update status item menjadi F jika qty_rcv = qty_po
56 UPDATE pu_po_balance_item A SET status_item = vStatusFinal, version = A.version+1, update_datetime = pDateTime, update_username = pUsername
57 FROM pu_receive_goods_item B
58 WHERE A.po_item_id = B.ref_item_id AND
59 B.receive_goods_id = pReceiveGoodsId AND
60 A.qty_po - A.qty_cancel + A.qty_add - A.qty_rcv <= 0;
61
62 -- Update pu_receive_goods ubah status menjadi R, doc_date & remark_confirm
63 UPDATE pu_receive_goods set status_doc = vStatusRelease, doc_date = pDateConfirm, version = vVersion+1, remark_confirm = pRemarkConfirm, update_datetime = pDateTime, update_username = pUsername
64 WHERE receive_goods_id = pReceiveGoodsId;
65
66 SELECT COUNT(1) INTO vUnfinishedItem
67 FROM pu_po_balance_item A, pu_po_item B
68 WHERE A.po_item_id = B.po_item_id AND
69 B.po_id = vPoId AND
70 A.status_item = vStatusRelease;
71
72 IF vUnfinishedItem = 0 THEN
73 UPDATE pu_po SET status_doc = vStatusFinal, update_datetime = pDateTime, update_username = pUsername
74 WHERE po_id = vPoId;
75 END IF;
76
77 -- insert ke table pu_po_balance_invoice
78 INSERT INTO pu_po_balance_invoice(
79 record_owner_id, ref_doc_type_id, ref_id, qty_recieve, buy_price, total_gross_amount, flg_invoice, invoice_id,
80 version, create_datetime, create_username, update_datetime, update_username, ref_item_id, po_id, po_item_id)
81 SELECT A.record_owner_id, A.doc_type_id, A.receive_goods_id, B.qty_receive, B.unit_price, B.unit_price * B.qty_receive, vNo, vEmptyId,
82 0, pDateTime, pUsername, pDateTime, pUsername, B.receive_goods_item_id, D.po_id, D.po_item_id
83 FROM pu_receive_goods A
84 INNER JOIN pu_receive_goods_item B ON B.receive_goods_id = A.receive_goods_id
85 INNER JOIN pu_po_balance_item C ON C.po_item_id = B.ref_item_id
86 INNER JOIN pu_po_item D ON D.po_item_id = C.po_item_id
87 WHERE A.receive_goods_id = pReceiveGoodsId;
88
89 -- insert ke table in_balance_receive_goods_item
90 INSERT INTO in_balance_receive_goods_item (
91 receive_goods_item_id, record_owner_id, receive_goods_id, doc_no, doc_date, supplier_id,
92 po_id, po_no, po_date, po_item_id, qty_rcv, qty_return,
93 po_uom_code, qty_int_rcv, qty_int_return, base_uom_code, status_item, version,
94 create_datetime, create_username, update_datetime, update_username)
95 SELECT B.receive_goods_item_id, A.record_owner_id, A.receive_goods_id, A.doc_no, A.doc_date, A.supplier_id,
96 C.po_id, C.doc_no, C.doc_date, D.po_item_id, B.qty_receive, 0,
97 vBaseUomCode, B.qty_receive, 0, vBaseUomCode, vStatusRelease, 0,
98 A.create_datetime, pUsername, A.update_datetime, pUsername
99 FROM pu_receive_goods A
100 INNER JOIN pu_receive_goods_item B ON A.receive_goods_id = B.receive_goods_id
101 INNER JOIN pu_po C ON A.ref_doc_type_id = C.doc_type_id
102 AND A.ref_id = C.po_id
103 INNER JOIN pu_po_item D ON C.po_id = D.po_id
104 AND B.ref_item_id = D.po_item_id
105 WHERE A.receive_goods_id = pReceiveGoodsId;
106
107 -- Insert ke product balance stock dimana produk nya belum terdafar di product balance stock dengan record_owner_id = -99 (SAS)
108 INSERT INTO in_product_balance(
109 record_owner_id, product_id, serial_number, lot_number, product_expired_date, product_year_made,
110 version, create_datetime, create_user_id, update_datetime, update_user_id)
111 SELECT vEmptyId, B.product_id, vEmptySpace, vEmptySpace, vEmptySpace, vEmptySpace,
112 0, pDateTime, vUserId, pDateTime, vUserId
113 FROM pu_receive_goods A
114 INNER JOIN pu_receive_goods_item B ON B.receive_goods_id = A.receive_goods_id
115 WHERE A.receive_goods_id = pReceiveGoodsId
116 AND NOT EXISTS (SELECT 1 FROM in_product_balance C WHERE C.record_owner_id = vEmptyId AND C.product_id = B.product_id);
117
118 -- insert ke table temp dengan mode Add
119 INSERT INTO tt_product_balance(
120 session_id, record_owner_id, ref_doc_type_id, ref_doc_id, ref_item_id, ref_doc_no, ref_doc_date, product_balance_id,
121 product_balance_stock_id, product_id, qty, warehouse_id, serial_number, lot_number, expired_date, year_made, product_status, mode_action)
122 SELECT pSessionId, A.record_owner_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, A.doc_no, A.doc_date, C.product_balance_id,
123 -99, B.product_id, B.qty_receive, E.warehouse_id, vEmptySpace, vEmptySpace, vEmptySpace, vEmptySpace, vProductStatusGood, vModeActionAdd
124 FROM pu_receive_goods A
125 INNER JOIN pu_receive_goods_item B ON B.receive_goods_id = A.receive_goods_id
126 INNER JOIN in_product_balance C ON C.product_id = B.product_id
127 AND C.record_owner_id = vEmptyId
128 INNER JOIN m_record_owner E ON E.record_owner_id = A.record_owner_id
129 WHERE A.receive_goods_id = pReceiveGoodsId
130 AND NOT EXISTS (
131 SELECT 1 FROM in_product_balance_stock F
132 WHERE F.record_owner_id = A.record_owner_id AND
133 F.product_balance_id = C.product_balance_id AND
134 F.product_id = C.product_id AND
135 F.warehouse_id = A.warehouse_id);
136
137 -- insert ke table temp dengan mode Update
138 INSERT INTO tt_product_balance(
139 session_id, record_owner_id, ref_doc_type_id, ref_doc_id, ref_item_id, ref_doc_no, ref_doc_date, product_balance_id,
140 product_balance_stock_id, product_id, qty, warehouse_id, serial_number, lot_number, expired_date, year_made, product_status, mode_action)
141 SELECT pSessionId, A.record_owner_id, A.doc_type_id, A.receive_goods_id, B.receive_goods_item_id, A.doc_no, A.doc_date, C.product_balance_id,
142 D.product_balance_stock_id, B.product_id, B.qty_receive, E.warehouse_id, vEmptySpace, vEmptySpace, vEmptySpace, vEmptySpace, vProductStatusGood, vModeActionUpdate
143 FROM pu_receive_goods A
144 INNER JOIN pu_receive_goods_item B ON B.receive_goods_id = A.receive_goods_id
145 INNER JOIN in_product_balance C ON C.product_id = B.product_id
146 INNER JOIN in_product_balance_stock D ON D.product_balance_id = C.product_balance_id
147 INNER JOIN m_record_owner E ON E.record_owner_id = A.record_owner_id
148 WHERE A.receive_goods_id = pReceiveGoodsId AND A.record_owner_id = D.record_owner_id;
149
150 -- insert ke table in_product_balance_stock
151 INSERT INTO in_product_balance_stock(
152 record_owner_id, product_balance_id, product_id, warehouse_id, product_status, qty,
153 version, create_datetime, create_username, update_datetime, update_username)
154 SELECT record_owner_id, product_balance_id, product_id, warehouse_id, vProductStatusGood, qty,
155 0, pDateTime, pUsername, pDateTime, pUsername
156 FROM tt_product_balance
157 WHERE session_id = pSessionId AND mode_action = vModeActionAdd;
158
159 -- update ke table in_product_balance_stock
160 UPDATE in_product_balance_stock A SET qty = A.qty + B.qty , version = version+1, update_datetime = pDateTime, update_username = pUsername
161 FROM tt_product_balance B
162 WHERE B.session_id = pSessionId
163 AND A.product_balance_id = B.product_balance_id
164 AND A.product_id = B.product_id
165 AND A.record_owner_id = B.record_owner_id
166 AND B.mode_action = vModeActionUpdate;
167
168 -- insert log
169 INSERT INTO in_log_product_balance_stock(
170 record_owner_id, product_balance_id, product_id, warehouse_id, product_status, ref_doc_type_id, ref_id,
171 ref_doc_no, ref_doc_date, qty, version, create_datetime, create_username, update_datetime, update_username)
172 SELECT record_owner_id, product_balance_id, product_id, warehouse_id, product_status, ref_doc_type_id, ref_doc_id,
173 ref_doc_no, ref_doc_date, qty, 0, pDateTime, pUsername, pDateTime, pUsername
174 FROM tt_product_balance
175 WHERE session_id = pSessionId;
176
177 -- delete table temp
178 DELETE FROM tt_product_balance WHERE session_id = pSessionId;
179
180 SELECT f_generate_fi_invoice_ap_from_receive_goods(pSessionId,pReceiveGoodsId,substring(pDateConfirm,1,4));
181
182END;
183$BODY$;
184
185ALTER FUNCTION pb.f_confirm_goods_receipt(bigint, character varying, character varying, character varying, character varying, text)
186 OWNER TO postgres;