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