· 8 years ago · May 15, 2018, 11:28 AM
1CREATE OR REPLACE FUNCTION pb.f_confirm_goods_receipt(bigint, character varying, character varying, character varying)
2 RETURNS void AS
3$BODY$
4DECLARE
5
6 pReceiveGoodsId ALIAS FOR $1;
7 pUsername ALIAS FOR $2;
8 pDateTime ALIAS FOR $3;
9 pSessionId ALIAS FOR $4;
10
11 vStatusRelease character varying := 'R';
12 vStatusFinal character varying := 'F';
13 vUnfinishedItem bigint;
14 vPoId bigint;
15 vProductStatusGood character varying := 'GOOD';
16 vModeActionUpdate character varying := 'U';
17 vModeActionAdd character varying := 'A';
18
19BEGIN
20
21 SELECT ref_id INTO vPoId FROM pu_receive_goods WHERE receive_goods_id = pReceiveGoodsId;
22
23 --update qty_rcv & qty_int_rcv di table pu_po_balance_item
24 UPDATE pu_po_balance_item A SET qty_rcv = A.qty_rcv+B.qty_receive, qty_int_rcv = A.qty_int_rcv+B.qty_receive,
25 version = A.version+1, update_datetime = pDateTime, update_username = pUsername
26 FROM pu_receive_goods_item B
27 WHERE A.po_item_id = B.ref_item_id AND
28 B.receive_goods_id = pReceiveGoodsId;
29
30 -- update status item menjadi R jika qty_rcv < qty_po
31 UPDATE pu_po_balance_item A SET status_item = vStatusRelease, version = A.version+1, update_datetime = pDateTime, update_username = pUsername
32 FROM pu_receive_goods_item B
33 WHERE A.po_item_id = B.ref_item_id AND
34 B.receive_goods_id = pReceiveGoodsId AND
35 A.qty_po - A.qty_cancel + A.qty_add - A.qty_rcv > 0;
36
37 -- update status item menjadi F jika qty_rcv = qty_po
38 UPDATE pu_po_balance_item A SET status_item = vStatusFinal, version = A.version+1, update_datetime = pDateTime, update_username = pUsername
39 FROM pu_receive_goods_item B
40 WHERE A.po_item_id = B.ref_item_id AND
41 B.receive_goods_id = pReceiveGoodsId AND
42 A.qty_po - A.qty_cancel + A.qty_add - A.qty_rcv <= 0;
43
44 -- Update pu_receive_goods ubah status menjadi R
45 UPDATE pu_receive_goods set status_doc = vStatusRelease, update_datetime = pDateTime, update_username = pUsername
46 WHERE receive_goods_id = pReceiveGoodsId;
47
48 SELECT COUNT(1) INTO vUnfinishedItem
49 FROM pu_po_balance_item A, pu_po_item B
50 WHERE A.po_item_id = B.po_item_id AND
51 B.po_id = vPoId AND
52 A.status_item = vStatusRelease;
53
54 IF vUnfinishedItem = 0 THEN
55 UPDATE pu_po SET status_doc = vStatusFinal, update_datetime = pDateTime, update_username = pUsername
56 WHERE po_id = vPoId;
57 END IF;
58
59 -- insert ke table pu_po_balance_invoice
60 INSERT INTO pu_po_balance_invoice(
61 record_owner_id, ref_doc_type_id, ref_id, qty_recieve, buy_price, total_gross_amount, flg_invoice, invoice_id,
62 version, create_datetime, create_username, update_datetime, update_username)
63 SELECT A.record_owner_id, A.doc_type_id, A.receive_goods_id, B.qty_receive, D.unit_price, D.unit_price * B.qty_receive, 'N', -99,
64 0, pDateTime, pUsername, pDateTime, pUsername
65 FROM pu_receive_goods A
66 INNER JOIN pu_receive_goods_item B ON B.receive_goods_id = A.receive_goods_id
67 INNER JOIN pu_po_balance_item C ON C.po_item_id = B.ref_item_id
68 INNER JOIN pu_po_item D ON D.po_item_id = C.po_item_id
69 WHERE A.receive_goods_id = pReceiveGoodsId;
70
71 -- insert ke table in_product_balance
72 INSERT INTO in_product_balance(
73 record_owner_id, product_id, serial_number, lot_number, expired_date, year_made,
74 version, create_datetime, create_username, update_datetime, update_username)
75 SELECT A.record_owner_id, B.product_id, '', '', '', '',
76 0, pDateTime, pUsername, pDateTime, pUsername
77 FROM pu_receive_goods A
78 INNER JOIN pu_receive_goods_item B ON B.receive_goods_id = A.receive_goods_id
79 WHERE A.receive_goods_id = pReceiveGoodsId
80 AND NOT EXISTS (SELECT 1 FROM in_product_balance C WHERE C.record_owner_id = A.record_owner_id AND C.product_id = B.product_id);
81
82 -- insert ke table temp dengan mode Add
83 INSERT INTO tt_product_balance(
84 session_id, record_owner_id, ref_doc_type_id, ref_doc_id, ref_item_id, ref_doc_no, ref_doc_date, product_balance_id,
85 product_balance_stock_id, product_id, qty, warehouse_id, serial_number, lot_number, expired_date, year_made, product_status, mode_action)
86 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,
87 -99, B.product_id, B.qty_receive, E.warehouse_id, ' ', ' ', ' ', ' ', vProductStatusGood, vModeActionAdd
88 FROM pu_receive_goods A
89 INNER JOIN pu_receive_goods_item B ON B.receive_goods_id = A.receive_goods_id
90 INNER JOIN in_product_balance C ON C.product_id = B.product_id
91 INNER JOIN m_record_owner E ON E.record_owner_id = A.record_owner_id
92 WHERE A.receive_goods_id = pReceiveGoodsId AND A.record_owner_id = C.record_owner_id
93 AND NOT EXISTS (SELECT 1 FROM in_product_balance_stock F
94 WHERE F.record_owner_id = A.record_owner_id AND F.product_balance_id = C.product_balance_id AND F.product_id = C.product_id);
95
96 -- insert ke table temp dengan mode Update
97 INSERT INTO tt_product_balance(
98 session_id, record_owner_id, ref_doc_type_id, ref_doc_id, ref_item_id, ref_doc_no, ref_doc_date, product_balance_id,
99 product_balance_stock_id, product_id, qty, warehouse_id, serial_number, lot_number, expired_date, year_made, product_status, mode_action)
100 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,
101 D.product_balance_stock_id, B.product_id, B.qty_receive, E.warehouse_id, ' ', ' ', ' ', ' ', vProductStatusGood, vModeActionUpdate
102 FROM pu_receive_goods A
103 INNER JOIN pu_receive_goods_item B ON B.receive_goods_id = A.receive_goods_id
104 INNER JOIN in_product_balance C ON C.product_id = B.product_id
105 INNER JOIN in_product_balance_stock D ON D.product_balance_id = C.product_balance_id
106 INNER JOIN m_record_owner E ON E.record_owner_id = A.record_owner_id
107 WHERE A.receive_goods_id = pReceiveGoodsId AND A.record_owner_id = C.record_owner_id;
108
109 -- insert ke table in_product_balance_stock
110 INSERT INTO in_product_balance_stock(
111 record_owner_id, product_balance_id, product_id, warehouse_id, product_status, qty,
112 version, create_datetime, create_username, update_datetime, update_username)
113 SELECT record_owner_id, product_balance_id, product_id, warehouse_id, vProductStatusGood, qty,
114 0, pDateTime, pUsername, pDateTime, pUsername
115 FROM tt_product_balance
116 WHERE session_id = pSessionId AND mode_action = vModeActionAdd;
117
118 -- update ke table in_product_balance_stock
119 UPDATE in_product_balance_stock A SET qty = A.qty + B.qty , version = version+1, update_datetime = pDateTime, update_username = pUsername
120 FROM tt_product_balance B
121 WHERE B.session_id = pSessionId AND
122 A.product_balance_id = B.product_balance_id AND
123 A.product_id = B.product_id
124 AND B.mode_action = vModeActionUpdate;
125
126 -- insert log
127 INSERT INTO in_log_product_balance_stock(
128 record_owner_id, product_balance_id, product_id, warehouse_id, product_status, ref_doc_type_id, ref_id,
129 ref_doc_no, ref_doc_date, qty, version, create_datetime, create_username, update_datetime, update_username)
130 SELECT record_owner_id, product_balance_id, product_id, warehouse_id, product_status, ref_doc_type_id, ref_doc_id,
131 ref_doc_no, ref_doc_date, qty, 0, pDateTime, pUsername, pDateTime, pUsername
132 FROM tt_product_balance;
133
134 -- delete table temp
135 DELETE FROM tt_product_balance WHERE session_id = pSessionId;
136
137END;
138$BODY$
139 LANGUAGE plpgsql VOLATILE
140 SET search_path = public, mstr, pb, batch
141 COST 100;