· 8 years ago · Dec 15, 2017, 04:26 AM
1CREATE OR REPLACE FUNCTION f_generate_commsheet_supp_to_erp(bigint, bigint, character varying, character varying, character varying, character varying)
2 RETURNS void AS
3$BODY$
4DECLARE
5 pTenantId ALIAS FOR $1;
6 pUserId ALIAS FOR $2;
7 pCatalogCode ALIAS FOR $3;
8 pDocNo ALIAS FOR $4;
9 pDocDate ALIAS FOR $5;
10 pDateTime ALIAS FOR $6;
11
12 vSessionId character varying(255);
13 vComSheetDocTypeId bigint;
14 vOuId bigint;
15 vNullValue bigint;
16 vNo character varying(1);
17 vYes character varying(1);
18 vPurchaseOfficerId bigint;
19 vWarehouseReceiveId bigint;
20 vCurrCodeIDR character varying(3);
21 vEmptyString character varying;
22 vSpaceValue character varying(1);
23 vStatusRelease character varying(1);
24 vWorkflowStatusApproved character varying(255);
25 vDefaultVersion bigint;
26 vMarginCatalog numeric;
27 vEmptyId bigint;
28 vRemark character varying(255);
29 vPoId bigint;
30 vMaxLineNo bigint;
31 vCatalogId bigint;
32
33BEGIN
34
35 vDefaultVersion := 0;
36 vComSheetDocTypeId := 106;
37 vOuId := 14; -- default OU ID
38 vEmptyId := -99;
39 vNullValue := -99;
40 vNo := 'N';
41 vYes := 'Y';
42 vPurchaseOfficerId := 4175; -- Purchase Office MD Admin
43 vWarehouseReceiveId := 14; -- GUDANG TERIMA
44 vCurrCodeIDR := 'IDR';
45 vEmptyString := '';
46 vSpaceValue := ' ';
47 vStatusRelease := 'R';
48 vWorkflowStatusApproved := 'APPROVED';
49 vMarginCatalog := 5; -- 5%
50 vRemark := 'Commsheet generated by Integration supp to erp';
51 vMaxLineNo := 0;
52
53 DELETE FROM tt_m_product_catalog_preparation WHERE session_id = vSessionId;
54 DELETE FROM tt_pu_po WHERE session_id = vSessionId;
55 DELETE FROM tt_pu_po_item WHERE session_id = vSessionId;
56 DELETE FROM tt_pu_po_ext WHERE session_id = vSessionId;
57 DELETE FROM tt_pu_po_tax WHERE session_id = vSessionId;
58 DELETE FROM tt_pu_po_balance_item_consignment WHERE session_id = vSessionId;
59 DELETE FROM tt_pu_log_po_balance_item_consignment WHERE session_id = vSessionId;
60
61 ---------------------------------------------------------------------------------------
62 -- Generate vSessionId
63 ---------------------------------------------------------------------------------------
64
65 SELECT f_make_uid() INTO vSessionId;
66
67 ---------------------------------------------------------------------------------------
68 -- Get catalog_id from catalog_code
69 ---------------------------------------------------------------------------------------
70 SELECT catalog_id INTO vCatalogId FROM m_catalog WHERE catalog_code = pCatalogCode;
71
72 ---------------------------------------------------------------------------------------
73 -- 1. Buat yang belum ada comsheet untuk yang flag_lanjut_catalog
74 -- Ambil semua yang belum ada comsheet, po_id -99 dan po_item_id -99
75 -- masukkan ke table temporer hilat juga ke table temporer tt_create_comsheet
76 ---------------------------------------------------------------------------------------
77 INSERT INTO tt_m_product_catalog_preparation(
78 session_id, product_catalog_id, supplier_code, brand_code, catalog_code,
79 flg_lanjut_catalog, remark, qty_commit, product_catalog_code,
80 product_code, flg_consign, supplier_price, cogs, catalog_price,
81 disc_member_percent, disc_promo_percent, price_after_disc, product_catalog_name,
82 product_catalog_desc, flg_discontinue, halaman_katalok, pemakaian_halaman,
83 po_id, po_item_id,
84 create_datetime, create_user_id, update_datetime,
85 update_user_id, version, margin_supplier_percentage, brand_id, supplier_id)
86 SELECT
87 vSessionId,A.product_catalog_id, A.supplier_code, C.brand_code, A.catalog_code,
88 A.flg_lanjut_catalog, A.remark, A.qty_commit, A.product_catalog_code,
89 A.product_code, A.flg_consign, A.supplier_price, A.cogs, A.catalog_price,
90 A.disc_member_percent, A.disc_promo_percent, A.price_after_disc, A.product_catalog_name,
91 A.product_catalog_desc, A.flg_discontinue, A.halaman_katalok, A.pemakaian_halaman,
92 A.po_id, A.po_item_id, A.create_datetime, A.create_user_id, A.update_datetime,
93 A.update_user_id, A.version, B.margin_supplier_percentage, C.brand_id, D.partner_id
94 FROM m_product_catalog_preparation A
95 INNER JOIN temp_data_integration_supp_to_erp B
96 ON A.product_catalog_code = B.product_catalog_code
97 AND A.brand_code = B.brand_code
98 AND A.catalog_code = B.catalog_code
99 INNER JOIN m_brand C ON A.brand_code = C.brand_code
100 INNER JOIN m_partner D ON A.supplier_code = D.partner_code
101 INNER JOIN m_catalog E ON A.catalog_code = E.catalog_code
102 WHERE flg_consign = vYes
103 AND po_id = vNullValue
104 AND po_item_id = vNullValue
105 AND B.doc_no = pDocNo;
106
107 ---------------------------------------------------------------------------------------
108 -- insert ke table temporer untuk data pu_po_item
109 ---------------------------------------------------------------------------------------
110 INSERT INTO tt_pu_po_item(
111 session_id, tenant_id, po_id, line_no, ref_doc_type_id,
112 ref_id, warehouse_id, product_id, flg_stock, curr_code, gross_price_po,
113 flg_tax_amount, tax_id, tax_percentage, tax_price, nett_price_po,
114 qty_po, po_uom_id, qty_int, base_uom_id, discount_percentage,
115 discount_amount, gross_item_amount, nett_item_amount, tax_amount,
116 activity_gl_id, product_coa_id, ou_rc_id, eta, tolerance_rcv_qty,
117 remark, version, create_datetime, create_user_id, update_datetime,
118 update_user_id, segment_id, eta_day, flg_indent, supplier_price,
119 supplier_product_code, catalog_price, disc_member, disc_promo,
120 nett_price, margin_supplier_percentage, margin_supplier_calculate_amount,
121 margin_supplier_amount, margin_catalog_percentage, margin_catalog_calculate_amount,
122 margin_catalog_amount, product_catalog_code, product_catalog_name, brand_id, supplier_id)
123 SELECT vSessionId,
124 pTenantId, -99, ROW_NUMBER() OVER ( PARTITION BY pTenantId), vNullValue,
125 vNullValue, -99, B.product_id, vYes, vCurrCodeIDR, A.cogs::numeric,
126 vNo, vNullValue, vNullValue, 0, 0,
127 A.qty_commit::numeric, B.base_uom_id, A.qty_commit::numeric, B.base_uom_id, 0,
128 0, 0, 0, 0,
129 vNullValue, vNullValue, vNullValue, vSpaceValue, 0,
130 A.remark, vDefaultVersion, pDateTime, pUserId, pDateTime,
131 pUserId, vNullValue, 'ETADAY', vNo, A.supplier_price::numeric,
132 vSpaceValue,
133 A.catalog_price::numeric,
134 A.disc_member_percent::numeric,
135 A.disc_promo_percent::numeric,
136 A.price_after_disc::numeric,
137 A.margin_supplier_percentage::numeric,
138 A.cogs::numeric,
139 A.cogs::numeric,
140 vMarginCatalog,
141 ROUND(vMarginCatalog*0.01*supplier_price::numeric + supplier_price::numeric),
142 A.catalog_price::numeric,
143 product_catalog_code,
144 product_catalog_name,
145 A.brand_id, A.supplier_id
146 FROM tt_m_product_catalog_preparation A,
147 m_product B
148 WHERE A.session_id = vSessionId
149 AND A.product_code= B.product_code
150 AND A.po_id = -99
151 AND A.po_item_id = -99;
152
153 IF NOT EXISTS (SELECT 1 FROM pu_po WHERE doc_no = pDocNo) THEN
154 ---------------------------------------------------------------------------------------
155 -- Update po_id di tt_pu_po_item
156 ---------------------------------------------------------------------------------------
157 WITH data_po AS (
158 SELECT nextval('pu_po_seq') as po_id, supplier_id, brand_id FROM tt_pu_po_item
159 WHERE session_id = vSessionId
160 GROUP BY supplier_id, brand_id
161 )
162 UPDATE tt_pu_po_item Z
163 SET po_id = B.po_id
164 FROM data_po B
165 WHERE Z.session_id = vSessionId
166 AND Z.supplier_id = B.supplier_id
167 AND Z.brand_id = B.brand_id;
168
169 ---------------------------------------------------------------------------------------
170 -- insert ke table temporer untuk data pu_po yang baru
171 ---------------------------------------------------------------------------------------
172 INSERT INTO tt_pu_po(
173 session_id, po_id, tenant_id, doc_type_id, doc_no, doc_date,
174 ou_id, ext_doc_no, ext_doc_date, ref_doc_type_id, ref_id, remark,
175 partner_id, purchaser_id, warehouse_id, flg_delivery, curr_code,
176 add_discount_percentage, add_discount_amount, top_code, status_doc,
177 workflow_status, version, create_datetime, create_user_id, update_datetime,
178 update_user_id, brand_id)
179 SELECT vSessionId,
180 A.po_id,
181 pTenantId, vComSheetDocTypeId,
182 pDocNo, pDocDate,
183 vOuId, '-', pDocDate, vNullValue, vNullValue, vRemark,
184 A.supplier_id, vPurchaseOfficerId, vWarehouseReceiveId, vYes, vCurrCodeIDR,
185 0, 0, vEmptyString, vStatusRelease,
186 vWorkflowStatusApproved,
187 vDefaultVersion, pDateTime, pUserId, pDateTime, pUserId, A.brand_id
188 FROM tt_pu_po_item A
189 WHERE session_id = vSessionId
190 GROUP BY A.po_id, A.supplier_id, A.brand_id;
191
192 ---------------------------------------------------------------------------------------
193 -- Insert into table tt_pu_po_ext
194 ---------------------------------------------------------------------------------------
195 INSERT INTO tt_pu_po_ext
196 (session_id, po_id, flg_buy_consignment,
197 "version", create_datetime, create_user_id, update_datetime, update_user_id,
198 brand_id, catalog_id, flg_continue_po)
199 SELECT vSessionId, A.po_id, vYes,
200 0, pDatetime, pUserId, pDatetime, pUserId,
201 A.brand_id, B.catalog_id, 'N'
202 FROM tt_pu_po A, m_catalog B
203 WHERE A.session_id = vSessionId
204 AND B.catalog_code = pCatalogCode;
205 ELSE
206 SELECT po_id INTO vPoId FROM pu_po WHERE doc_no = pDocNo;
207
208 ---------------------------------------------------------------------------------------
209 -- Update po_id di tt_pu_po_item
210 ---------------------------------------------------------------------------------------
211 WITH data_po AS (
212 SELECT vPoId as po_id, supplier_id, brand_id FROM tt_pu_po_item
213 WHERE session_id = vSessionId
214 GROUP BY supplier_id, brand_id
215 )
216 UPDATE tt_pu_po_item Z
217 SET po_id = B.po_id
218 FROM data_po B
219 WHERE Z.session_id = vSessionId
220 AND Z.supplier_id = B.supplier_id
221 AND Z.brand_id = B.brand_id;
222
223 SELECT max(B.line_no) INTO vMaxLineNo
224 FROM pu_po A
225 INNER JOIN pu_po_item B ON A.po_id = B.po_id
226 WHERE A.doc_no = pDocNo;
227
228 ---------------------------------------------------------------------------------------
229 -- Insert into table tt_pu_po_balance_item_consignment
230 ---------------------------------------------------------------------------------------
231 INSERT INTO tt_pu_po_balance_item_consignment
232 (session_id, po_item_id, tenant_id, ou_id,
233 qty_po, qty_rcv, qty_return, qty_cancel, qty_add, po_uom_id,
234 qty_int_po, qty_int_rcv, qty_int_return, qty_int_cancel, qty_int_add, base_uom_id,
235 tolerance_rcv_qty, status_item,
236 "version", create_datetime, create_user_id, update_datetime, update_user_id)
237 SELECT vSessionId, A.po_item_id, A.tenant_id, B.ou_id,
238 A.qty_po, 0, 0, 0, 0, A.po_uom_id,
239 A.qty_int, 0, 0, 0, 0, A.base_uom_id,
240 A.tolerance_rcv_qty, vStatusRelease,
241 0, pDatetime, pUserId, pDatetime, pUserId
242 FROM tt_pu_po_item A, pu_po B
243 WHERE A.session_id = vSessionId
244 AND A.po_id = B.po_id;
245
246 ---------------------------------------------------------------------------------------
247 -- hanya po item id yang benar ( dari max po item id ) dan kuantiti nya sudah diakumulasi
248 ---------------------------------------------------------------------------------------
249 INSERT INTO tt_pu_log_po_balance_item_consignment
250 (session_id, tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
251 qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
252 version, create_datetime, create_user_id, update_datetime, update_user_id)
253 SELECT vSessionId, B.tenant_id, A.po_id, A.po_item_id, vEmptyId, vEmptyId, vEmptyId,
254 A.qty_po, A.po_uom_id, A.qty_po, A.po_uom_id, B.remark,
255 0, pDatetime, pUserId, pDatetime, pUserId
256 FROM tt_pu_po_item A, pu_po B
257 WHERE A.session_id = vSessionId AND
258 A.po_id = B.po_id;
259
260 END IF;
261 ---------------------------------------------------------------------------------------
262 -- Insert into table tt_pu_po_balance_item_consignment
263 ---------------------------------------------------------------------------------------
264 INSERT INTO tt_pu_po_balance_item_consignment
265 (session_id, po_item_id, tenant_id, ou_id,
266 qty_po, qty_rcv, qty_return, qty_cancel, qty_add, po_uom_id,
267 qty_int_po, qty_int_rcv, qty_int_return, qty_int_cancel, qty_int_add, base_uom_id,
268 tolerance_rcv_qty, status_item,
269 "version", create_datetime, create_user_id, update_datetime, update_user_id)
270 SELECT vSessionId, A.po_item_id, A.tenant_id, B.ou_id,
271 A.qty_po, 0, 0, 0, 0, A.po_uom_id,
272 A.qty_int, 0, 0, 0, 0, A.base_uom_id,
273 A.tolerance_rcv_qty, vStatusRelease,
274 0, pDatetime, pUserId, pDatetime, pUserId
275 FROM tt_pu_po_item A, tt_pu_po B
276 WHERE A.session_id = vSessionId AND
277 A.session_id = B.session_id AND
278 A.po_id = B.po_id;
279
280 ---------------------------------------------------------------------------------------
281 -- hanya po item id yang benar ( dari max po item id ) dan kuantiti nya sudah diakumulasi
282 ---------------------------------------------------------------------------------------
283 INSERT INTO tt_pu_log_po_balance_item_consignment
284 (session_id, tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
285 qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
286 version, create_datetime, create_user_id, update_datetime, update_user_id)
287 SELECT vSessionId, B.tenant_id, A.po_id, A.po_item_id, vEmptyId, vEmptyId, vEmptyId,
288 A.qty_po, A.po_uom_id, A.qty_po, A.po_uom_id, B.remark,
289 0, pDatetime, pUserId, pDatetime, pUserId
290 FROM tt_pu_po_item A, tt_pu_po B
291 WHERE A.session_id = vSessionId AND
292 A.session_id = B.session_id AND
293 A.po_id = B.po_id;
294
295 -- INSERT ke table asli
296 -- tt_pu_po ke pu_po
297 -- tt_pu_po_item ke pu_po_item
298 -- tt_pu_po_ext ke pu_po_ext
299 -- tt_pu_po_balance_item_consignment ke pu_po_balance_item_consignment
300 -- tt_pu_log_po_balance_item_consignment ke pu_log_po_balance_item_consignment
301
302 ---------------------------------------------------------------------------------------
303 -- Insert into table pu_po
304 ---------------------------------------------------------------------------------------
305 INSERT INTO pu_po(
306 po_id, tenant_id, doc_type_id, doc_no, doc_date, ou_id, ext_doc_no,
307 ext_doc_date, ref_doc_type_id, ref_id, remark, partner_id, purchaser_id,
308 warehouse_id, flg_delivery, curr_code, add_discount_percentage,
309 add_discount_amount, top_code, status_doc, workflow_status, version,
310 create_datetime, create_user_id, update_datetime, update_user_id)
311 SELECT po_id, tenant_id, doc_type_id, doc_no, doc_date, ou_id, ext_doc_no,
312 ext_doc_date, ref_doc_type_id, ref_id, remark, partner_id, purchaser_id,
313 warehouse_id, flg_delivery, curr_code, add_discount_percentage,
314 add_discount_amount, top_code, status_doc, workflow_status, version,
315 create_datetime, create_user_id, update_datetime, update_user_id
316 FROM tt_pu_po
317 WHERE session_id = vSessionId;
318
319 ---------------------------------------------------------------------------------------
320 -- Insert into table pu_po_item
321 ---------------------------------------------------------------------------------------
322 INSERT INTO pu_po_item(
323 po_item_id, tenant_id, po_id, line_no, ref_doc_type_id, ref_id,
324 warehouse_id, product_id, flg_stock, curr_code, gross_price_po,
325 flg_tax_amount, tax_id, tax_percentage, tax_price, nett_price_po,
326 qty_po, po_uom_id, qty_int, base_uom_id, discount_percentage,
327 discount_amount, gross_item_amount, nett_item_amount, tax_amount,
328 activity_gl_id, product_coa_id, ou_rc_id, eta, tolerance_rcv_qty,
329 remark, version, create_datetime, create_user_id, update_datetime,
330 update_user_id, segment_id, eta_day, flg_indent, supplier_price,
331 supplier_product_code, catalog_price, disc_member, disc_promo,
332 nett_price, margin_supplier_percentage, margin_supplier_calculate_amount,
333 margin_supplier_amount, margin_catalog_percentage, margin_catalog_calculate_amount,
334 margin_catalog_amount, product_catalog_code, product_catalog_name)
335 SELECT po_item_id, tenant_id, po_id, line_no+vMaxLineNo, ref_doc_type_id, ref_id,
336 warehouse_id, product_id, flg_stock, curr_code, gross_price_po,
337 flg_tax_amount, tax_id, tax_percentage, tax_price, nett_price_po,
338 qty_po, po_uom_id, qty_int, base_uom_id, discount_percentage,
339 discount_amount, gross_item_amount, nett_item_amount, tax_amount,
340 activity_gl_id, product_coa_id, ou_rc_id, eta, tolerance_rcv_qty,
341 remark, version, create_datetime, create_user_id, update_datetime,
342 update_user_id, segment_id, eta_day, flg_indent, supplier_price,
343 supplier_product_code, catalog_price, disc_member, disc_promo,
344 nett_price, margin_supplier_percentage, margin_supplier_calculate_amount,
345 margin_supplier_amount, margin_catalog_percentage, margin_catalog_calculate_amount,
346 margin_catalog_amount, product_catalog_code, product_catalog_name
347 FROM tt_pu_po_item
348 WHERE session_id = vSessionId;
349
350 ---------------------------------------------------------------------------------------
351 -- Insert into table pu_po_ext
352 ---------------------------------------------------------------------------------------
353 INSERT INTO pu_po_ext(
354 po_id, flg_buy_consignment, version, create_datetime, create_user_id,
355 update_datetime, update_user_id, brand_id, catalog_id, flg_continue_po)
356 SELECT po_id, flg_buy_consignment, version, create_datetime, create_user_id,
357 update_datetime, update_user_id, brand_id, catalog_id, flg_continue_po
358 FROM tt_pu_po_ext
359 WHERE session_id = vSessionId;
360
361 ---------------------------------------------------------------------------------------
362 -- Insert into table pu_po_balance_item_consignment
363 ---------------------------------------------------------------------------------------
364 INSERT INTO pu_po_balance_item_consignment(
365 po_item_id, tenant_id, ou_id, qty_po, qty_rcv, qty_return, qty_cancel,
366 qty_add, po_uom_id, qty_int_po, qty_int_rcv, qty_int_return,
367 qty_int_cancel, qty_int_add, base_uom_id, tolerance_rcv_qty,
368 status_item, version, create_datetime, create_user_id, update_datetime,
369 update_user_id, qty_int_sell, qty_sell, qty_move)
370 SELECT po_item_id, tenant_id, ou_id, qty_po, qty_rcv, qty_return, qty_cancel,
371 qty_add, po_uom_id, qty_int_po, qty_int_rcv, qty_int_return,
372 qty_int_cancel, qty_int_add, base_uom_id, tolerance_rcv_qty,
373 status_item, version, create_datetime, create_user_id, update_datetime,
374 update_user_id, qty_int_sell, qty_sell, qty_move
375 FROM tt_pu_po_balance_item_consignment
376 WHERE session_id = vSessionId;
377
378 ---------------------------------------------------------------------------------------
379 -- Insert into table pu_log_po_balance_item_consignment
380 ---------------------------------------------------------------------------------------
381 INSERT INTO pu_log_po_balance_item_consignment(
382 log_po_balance_item_consignment_id, tenant_id, po_id, po_item_id,
383 ref_doc_type_id, ref_id, ref_item_id, qty_trx, trx_uom_id, qty_int,
384 base_uom_id, remark, version, create_datetime, create_user_id,
385 update_datetime, update_user_id)
386 SELECT log_po_balance_item_consignment_id, tenant_id, po_id, po_item_id,
387 ref_doc_type_id, ref_id, ref_item_id, qty_trx, trx_uom_id, qty_int,
388 base_uom_id, remark, version, create_datetime, create_user_id,
389 update_datetime, update_user_id
390 FROM tt_pu_log_po_balance_item_consignment
391 WHERE session_id = vSessionId;
392
393 ---------------------------------------------------------------------------------------
394 -- Update kembali po_id dan po_item_id setelah comsheet dilengkapi
395 ---------------------------------------------------------------------------------------
396 UPDATE m_product_catalog_preparation Z
397 SET po_id = C.po_id, po_item_id = C.po_item_id, version=Z.version+1,
398 update_datetime = pDateTime, update_user_id = pUserId
399 FROM m_product_catalog_preparation A, pu_po_item C, pu_po_balance_item_consignment D, m_product E,pu_po_ext F
400 WHERE C.po_item_id = D.po_item_id
401 AND C.product_id = E.product_id
402 AND D.status_item IN ('I','R')
403 AND A.product_code = E.product_code
404 AND Z.product_catalog_id = A.product_catalog_id
405 AND A.flg_consign = 'Y'
406 AND A.po_id = -99
407 AND A.po_item_id = -99
408 AND F.catalog_id = vCatalogId;
409
410 ---------------------------------------------------------------------------------------
411 -- Update doc_no dan doc_date berdasarkan po_id pada tabel m_product_catalog_preparation
412 ---------------------------------------------------------------------------------------
413 UPDATE m_product_catalog_preparation Z
414 SET doc_no = A.doc_no, doc_date = A.doc_date
415 FROM pu_po A
416 WHERE A.po_id = Z.po_id
417 AND Z.po_id <> -99
418 AND Z.po_item_id <> -99;
419
420 ---------------------------------------------------------------------------------------
421 -- Delete from tt_m_product_catalog_preparation
422 ---------------------------------------------------------------------------------------
423 DELETE FROM tt_m_product_catalog_preparation WHERE session_id = vSessionId;
424 DELETE FROM tt_pu_po WHERE session_id = vSessionId;
425 DELETE FROM tt_pu_po_item WHERE session_id = vSessionId;
426 DELETE FROM tt_pu_po_ext WHERE session_id = vSessionId;
427 DELETE FROM tt_pu_po_balance_item_consignment WHERE session_id = vSessionId;
428 DELETE FROM tt_pu_log_po_balance_item_consignment WHERE session_id = vSessionId;
429
430END;
431$BODY$
432 LANGUAGE plpgsql VOLATILE
433 COST 100;