· 8 years ago · Aug 27, 2018, 08:00 AM
1CREATE OR REPLACE FUNCTION f_upload_product_stock(character varying, bigint, character varying, character varying, character varying)
2 RETURNS bigint AS
3 $BODY$
4DECLARE
5
6 pSessionId ALIAS FOR $1;
7 pRecordOwnerId ALIAS FOR $2;
8 pDocDate ALIAS FOR $3;
9 pUsername ALIAS FOR $4;
10 pDatetime ALIAS FOR $5;
11
12 vEmptyId bigint := -99;
13 vProductId bigint;
14 vUomId bigint;
15 vFindProductId bigint;
16 vResult bigint;
17
18 vFail character varying := 'FAIL';
19 vOk character varying := 'OK';
20
21 vYes character varying := 'Y';
22 vNo character varying := 'N';
23 vCurrentDateTime character varying;
24 vEmpty character varying := ' ';
25 vInventoryId bigint;
26 vWarehouseId bigint;
27 vDocumentNo character varying;
28 vPrefixDocNo character varying := 'ADJ';
29 vDocTypeId bigint := 521;
30 vReleased character varying := 'R';
31 vProductStatusGood character varying := 'GOOD';
32 vModeActionAdd character varying := 'A';
33 vModeActionUpdate character varying := 'U';
34
35BEGIN
36
37 SELECT warehouse_id INTO vWarehouseId FROM m_warehouse WHERE record_owner_id = pRecordOwnerId;
38
39 -- Validasi produk tidak boleh duplikate saat upload
40 WITH duplicateCode AS (
41 SELECT product_code, COUNT(product_code)
42 FROM tt_ul_product_stock
43 WHERE session_id = pSessionId
44 AND record_owner_id = pRecordOwnerId
45 GROUP BY product_code
46 HAVING COUNT(product_code) > 1
47 )
48 UPDATE tt_ul_product_stock Z
49 SET status = vFail, message = concat(Z.message, 'Kode Produk ', A.product_code, ' Duplikat, ')
50 FROM tt_ul_product_stock A
51 INNER JOIN duplicateCode B ON A.product_code = B.product_code
52 WHERE A.session_id = pSessionId
53 AND Z.product_code = A.product_code
54 AND Z.record_owner_id = pRecordOwnerId;
55
56 UPDATE tt_ul_product_stock Z
57 SET status = vFail, message = concat(Z.message, 'Kode Produk ', A.product_code, ' Tidak terdaftar, ')
58 FROM tt_ul_product_stock A
59 LEFT JOIN m_product B ON A.product_code = B.product_code
60 AND A.record_owner_id = B.record_owner_id
61 WHERE A.session_id = pSessionId
62 AND Z.product_code = A.product_code
63 AND B.product_id IS NULL;
64
65 SELECT COUNT(1) INTO vResult
66 FROM tt_ul_product_stock
67 WHERE session_id = pSessionId
68 AND status = vFail;
69
70 IF vResult = 0 THEN
71
72 SELECT nextval('in_inventory_seq') INTO vInventoryId;
73 SELECT f_gen_autonum(concat(vPrefixDocNo,'/',substring(pDocDate, 3, 4),'/'), vPrefixDocNo) INTO vDocumentNo;
74
75 INSERT INTO tt_in_inventory (
76 session_id, inventory_id, record_owner_id, doc_type_id, doc_no,
77 doc_date, ext_doc_date, ext_doc_no, remark, warehouse_id,
78 status_doc, version, create_datetime, create_username, update_datetime,
79 update_username
80 ) VALUES (
81 pSessionId, vInventoryId, pRecordOwnerId, vDocTypeId, vDocumentNo,
82 pDocDate, vEmpty, vEmpty, 'Penyesuaian Stok Dari Upload', vWarehouseId,
83 vReleased, 0 , pDatetime, pUsername, pDatetime,
84 pUsername
85 );
86
87 -- Insert Produk yg terdaftar di in_product_balance
88 INSERT INTO tt_in_inventory_item(
89 inventory_id, product_id, product_balance_id, qty, version,
90 create_datetime, create_username, update_datetime, update_username
91 )
92 SELECT vInventoryId, B.product_id, C.product_balance_id, qty, 0,
93 A.create_datetime, A.create_username, A.update_datetime, A.update_username
94 FROM tt_ul_product_stock A
95 INNER JOIN m_product B ON A.product_code = B.product_code
96 AND A.record_owner_id = B.record_owner_id
97 INNER JOIN in_product_balance C ON B.product_id = C.product_id
98 WHERE A.session_id = pSessionId;
99
100 -- Insert Produk yg tidak terdaftar di in_product_balance
101 INSERT INTO tt_in_inventory_item(
102 inventory_id, product_id, product_balance_id, qty, version,
103 create_datetime, create_username, update_datetime, update_username
104 )
105 SELECT vInventoryId, B.product_id, vEmptyId, qty, 0,
106 A.create_datetime, A.create_username, A.update_datetime, A.update_username
107 FROM tt_ul_product_stock A
108 INNER JOIN m_product B ON A.product_code = B.product_code
109 AND A.record_owner_id = B.record_owner_id
110 WHERE A.session_id = pSessionId
111 AND NOT EXISTS (SELECT 1 FROM in_product_balance Z WHERE B.product_id = Z.product_id);
112
113 INSERT INTO in_inventory (
114 inventory_id, record_owner_id, doc_type_id, doc_no,
115 doc_date, ext_doc_date, ext_doc_no, remark, warehouse_id,
116 status_doc, version, create_datetime, create_username, update_datetime,
117 update_username
118 )
119 SELECT
120 inventory_id, record_owner_id, doc_type_id, doc_no,
121 doc_date, ext_doc_date, ext_doc_no, remark, warehouse_id,
122 status_doc, version, create_datetime, create_username, update_datetime,
123 update_username
124 FROM tt_in_inventory
125 WHERE session_id = pSessionId;
126
127 INSERT INTO in_inventory_item(
128 inventory_item_id, inventory_id, product_id, product_balance_id, qty, version,
129 create_datetime, create_username, update_datetime, update_username
130 )
131 SELECT
132 inventory_item_id, inventory_id, product_id, product_balance_id, qty, version,
133 create_datetime, create_username, update_datetime, update_username
134 FROM tt_in_inventory_item
135 WHERE inventory_id = vInventoryId;
136
137 -- Insert ke table in_product_balance dengan product_balance_id = -99 dari in_inventory_item
138 INSERT INTO in_product_balance(
139 record_owner_id, product_id, serial_number, lot_number, expired_date, year_made,
140 version, create_datetime, create_username, update_datetime, update_username)
141 SELECT pRecordOwnerId, product_id, vEmpty, vEmpty, vEmpty, vEmpty,
142 0, pDatetime, pUsername, pDatetime, pUsername
143 FROM in_inventory_item
144 WHERE inventory_id = vInventoryId
145 AND product_balance_id = vEmptyId;
146
147 INSERT INTO tt_product_balance(
148 session_id, record_owner_id, ref_doc_type_id, ref_doc_id, ref_item_id, ref_doc_no, ref_doc_date, product_balance_id,
149 product_balance_stock_id, product_id, qty, warehouse_id, serial_number, lot_number, expired_date, year_made, product_status, mode_action)
150 SELECT pSessionId, A.record_owner_id, A.doc_type_id, A.inventory_id, B.inventory_item_id, A.doc_no, A.doc_date, C.product_balance_id,
151 -99, B.product_id, B.qty, E.warehouse_id, vEmpty, vEmpty, vEmpty, vEmpty, vProductStatusGood, vModeActionAdd
152 FROM in_inventory A
153 INNER JOIN in_inventory_item B ON B.inventory_id = A.inventory_id
154 INNER JOIN in_product_balance C ON C.product_id = B.product_id
155 AND C.record_owner_id = pRecordOwnerId
156 INNER JOIN m_record_owner E ON E.record_owner_id = A.record_owner_id
157 WHERE A.inventory_id = vInventoryId
158 AND NOT EXISTS (
159 SELECT 1
160 FROM in_product_balance_stock F
161 WHERE F.record_owner_id = A.record_owner_id AND
162 F.product_balance_id = C.product_balance_id AND
163 F.product_id = C.product_id AND
164 F.warehouse_id = A.warehouse_id);
165
166 INSERT INTO tt_product_balance(
167 session_id, record_owner_id, ref_doc_type_id, ref_doc_id, ref_item_id, ref_doc_no, ref_doc_date, product_balance_id,
168 product_balance_stock_id, product_id, qty, warehouse_id, serial_number, lot_number, expired_date, year_made, product_status, mode_action)
169 SELECT pSessionId, A.record_owner_id, A.doc_type_id, A.inventory_id, B.inventory_item_id, A.doc_no, A.doc_date, C.product_balance_id,
170 D.product_balance_stock_id, B.product_id, B.qty, E.warehouse_id, vEmpty, vEmpty, vEmpty, vEmpty, vProductStatusGood, vModeActionUpdate
171 FROM in_inventory A
172 INNER JOIN in_inventory_item B ON B.inventory_id = A.inventory_id
173 INNER JOIN in_product_balance C ON C.product_id = B.product_id
174 INNER JOIN in_product_balance_stock D ON D.product_balance_id = C.product_balance_id
175 INNER JOIN m_record_owner E ON E.record_owner_id = A.record_owner_id
176 WHERE A.inventory_id = vInventoryId
177 AND A.record_owner_id = D.record_owner_id;
178
179 -- insert ke table in_product_balance_stock
180 INSERT INTO in_product_balance_stock(
181 record_owner_id, product_balance_id, product_id, warehouse_id, product_status, qty,
182 version, create_datetime, create_username, update_datetime, update_username)
183 SELECT record_owner_id, product_balance_id, product_id, warehouse_id, vProductStatusGood, qty,
184 0, pDatetime, pUsername, pDatetime, pUsername
185 FROM tt_product_balance
186 WHERE session_id = pSessionId
187 AND mode_action = vModeActionAdd;
188
189 -- update ke table in_product_balance_stock
190 UPDATE in_product_balance_stock A SET qty = A.qty + B.qty , version = version+1, update_datetime = pDateTime, update_username = pUsername
191 FROM tt_product_balance B
192 WHERE B.session_id = pSessionId
193 AND A.product_balance_id = B.product_balance_id
194 AND A.product_id = B.product_id
195 AND B.mode_action = vModeActionUpdate;
196
197 -- insert log
198 INSERT INTO in_log_product_balance_stock(
199 record_owner_id, product_balance_id, product_id, warehouse_id, product_status, ref_doc_type_id, ref_id,
200 ref_doc_no, ref_doc_date, qty, version, create_datetime, create_username, update_datetime, update_username)
201 SELECT record_owner_id, product_balance_id, product_id, warehouse_id, product_status, ref_doc_type_id, ref_doc_id,
202 ref_doc_no, ref_doc_date, qty, 0, pDateTime, pUsername, pDateTime, pUsername
203 FROM tt_product_balance
204 WHERE session_id = pSessionId;
205
206 -- delete table temp
207 DELETE FROM tt_product_balance WHERE session_id = pSessionId;
208
209 END IF;
210
211 return vResult;
212
213END;
214$BODY$
215 LANGUAGE plpgsql VOLATILE
216 SET search_path = public, mstr, batch
217 COST 100;
218 /