· 9 years ago · Oct 12, 2016, 04:24 AM
1-- Function: sl_submit_do_mlm(bigint, character varying, character varying)
2
3-- DROP FUNCTION sl_submit_do_mlm(bigint, character varying, character varying);
4
5CREATE OR REPLACE FUNCTION sl_submit_do_mlm(bigint, character varying, character varying)
6 RETURNS void AS
7$BODY$
8DECLARE
9 pTenantId ALIAS FOR $1;
10 pSessionId ALIAS FOR $2;
11 pProcessNo ALIAS FOR $3;
12 vProcessId bigint;
13 vDoMlmId bigint;
14 vUserId bigint;
15 vDatetime character varying(14);
16 vFlowInvoiceArId bigint;
17 vParentOuId bigint;
18 vSoMlmId bigint;
19 vOuStructure OU_BU_STRUCTURE%ROWTYPE;
20 result RECORD;
21 vReqId character varying;
22 vCursorForProduct tt_update_product_balance_stock_for_do_mlm%ROWTYPE;
23 vCursorForDoMlm sl_do_mlm_item%ROWTYPE;
24 vCursorForInProductBalance in_product_balance_stock%ROWTYPE;
25 vDeliveryOrderMlmDocTypeId bigint;
26 vRoundingModeNonTax character varying(5);
27 vTempTotal numeric;
28 vQty numeric;
29 vQtyMinus numeric;
30 vProductId bigint;
31 vProductStatus character varying(50);
32 vStatusRelease character varying(1);
33 vTotalHargaNetto numeric;
34 vPartnerId bigint;
35 vEmptyValueId bigint;
36 vYesValue character varying;
37 vInprogressValue character varying;
38 vReturnValue character varying;
39 vDefValue character varying;
40 vGoodValue character varying;
41 vNoValue character varying;
42 vAmountConvert numeric;
43
44 vDsCommission numeric(5,2);
45 vAmountAfterCommision numeric;
46 vAdditionalCost numeric;
47 vRoundingScaleNonTax integer;
48 vDefStockLocCode character varying(30);
49 vInvoiceArDocScheme character varying;
50
51 -- untuk persiapan jurnal, NK 30 Jan 2016
52 vSignDebit character varying(1);
53 vSignCredit character varying(1);
54 vTypeRate character varying(3);
55 vProductCOA character varying(10);
56 vSystemCOA character varying(10);
57 vDocJournal DOC_JOURNAL%ROWTYPE;
58 vJournalTrxId bigint;
59 vStatusDraft character varying(1);
60 vEmptyValue character varying(1);
61
62 vConstantGLBiayaVoucher character varying;
63 vStateRelease character varying;
64 vStateApproved character varying;
65 vStateDraft character varying;
66 vFlagRole character varying;
67 vCNARDocTypeId bigint;
68 vActivityGlId bigint;
69
70 vCursorForInvoiceAr fi_invoice_ar%ROWTYPE;
71 vPriceAfterDisc numeric;
72 vVoucher numeric;
73 vSoMlmDocTypeId bigint;
74 vRoleId bigint;
75
76BEGIN
77 vDeliveryOrderMlmDocTypeId := 313;
78 vQtyMinus := 0;
79 vTempTotal :=0;
80 vStatusRelease := 'R';
81 vTotalHargaNetto :=0;
82 vInprogressValue:='I';
83 vYesValue:='Y';
84 vNoValue:='N';
85 vDefValue:='DEF';
86 vReturnValue:='RETURN';
87 vGoodValue:='GOOD';
88 vEmptyValueId:=-99;
89 vDefStockLocCode := 'ZZZZZZZZZZZZZZZZZZZZZZZZZZZZZZ';
90 vConstantGLBiayaVoucher :='PROMOSIMBR';
91 vStateApproved :='APPROVED';
92 vStateRelease :='RELEASED';
93 vStateDraft :='DRAFT';
94 vFlagRole := 'U';
95 vStatusRelease := 'R';
96 vCNARDocTypeId := 251;
97 vSoMlmDocTypeId :=306;
98 vInvoiceArDocScheme := 'DG01';
99 -- untuk persiapan jurnal, NK 30 Jan 2016
100 vSignDebit := 'D';
101 vSignCredit := 'C';
102 vTypeRate := 'COM';
103 vProductCOA := 'PRODUCT';
104 vSystemCOA := 'SYSTEM';
105 vStatusDraft := 'D';
106 vEmptyValue := ' ';
107
108 SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
109
110
111 SELECT A.process_message_id INTO vProcessId
112 FROM t_process_message A
113 WHERE A.tenant_id = pTenantId AND
114 A.process_name = 'sl_submit_do_mlm' AND
115 A.process_no = pProcessNo;
116
117 SELECT CAST(A.process_parameter_value AS bigint) INTO vDoMlmId
118 FROM t_process_parameter A
119 WHERE A.process_message_id = vProcessId AND
120 A.process_parameter_key = 'doMlmId';
121
122 SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
123 FROM t_process_parameter A
124 WHERE A.process_message_id = vProcessId AND
125 A.process_parameter_key = 'userId';
126
127 SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
128 FROM t_process_parameter A
129 WHERE A.process_message_id = vProcessId AND
130 A.process_parameter_key = 'datetime';
131
132 SELECT partner_ship_to_id INTO vPartnerId
133 FROM sl_do_mlm WHERE do_mlm_id = vDoMlmId;
134
135 SELECT f_get_value_system_config_by_param_code(pTenantId, 'ROLE.ASM.ID') INTO vRoleId;
136
137 SELECT f_get_value_system_config_by_param_code(pTenantId, 'DEFAULT.PRODUCT.STATUS.SELL') INTO vProductStatus;
138
139 SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.scale.non.tax')::integer INTO vRoundingScaleNonTax;
140
141
142
143 SELECT A.ref_id, f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
144 FROM sl_do_mlm A
145 WHERE A.do_mlm_id = vDoMlmId INTO result;
146
147 vSoMlmId := result.ref_id;
148 vOuStructure := result.ou;
149
150 -- untuk persiapan jurnal, NK 30 Jan 2016
151 vDocJournal := result.doc;
152
153 /*
154 * 1. update sl_so_mlm_balance_product untuk field qty dlv, qty cancel,
155 * 2. buat log di sl_log_so_mlm_balance_product
156 * 3. mengurangi reserved stock berdsarkan qty so
157 * 4. update status doc = R di sl_do_mlm
158 * 5. insert data ke tt_update_product_balance_stock_for_do_mlm untuk urutan prioritas pemotongan stock
159 * 6. mengisi qty minus berdasarkan urutan prioritas
160 * 7. Kembalikan 'ZZZZZZZZZZZZZZZZZZZZZ' yang berada di paling bawah menjadi 'DEF'
161 * 8. potong stock berdasarkan qty minus
162 * 9.buat log untuk potong stock berdasarkan qty minus
163 *
164 */
165
166 /* 1. update sl_so_mlm_balance_product untuk field qty dlv, qty cancel */
167 UPDATE sl_so_mlm_balance_product a
168 SET qty_dlv = c.qty_dlv_so, qty_cancel = qty_so - c.qty_dlv_so,qty_dlv_int = c.qty_dlv_so, qty_cancel_int = qty_so - c.qty_dlv_so,
169 update_datetime = vDatetime, update_user_id = vUserId, version = a.version + 1
170 FROM sl_do_mlm b
171 INNER JOIN sl_do_mlm_item c on c.do_mlm_id = b.do_mlm_id
172 WHERE a.so_mlm_id = b.ref_id AND a.so_mlm_id = vSoMlmId AND
173 c.product_id = a.product_id;
174
175 /* 2. buat log di sl_log_so_mlm_balance_product */
176 INSERT INTO sl_log_so_mlm_balance_product
177 (tenant_id,so_mlm_id,product_id,product_catalog_id,ref_doc_type_id,ref_id,ref_item_id,qty_trx,trx_uom_id,qty_int,base_uom_id,remark,version,create_datetime,
178 create_user_id,update_datetime,update_user_id)
179 SELECT a.tenant_id,a.so_mlm_id,a.product_id,a.product_catalog_id,b.doc_type_id,b.do_mlm_id,c.do_mlm_item_id,c.qty_dlv_so,c.so_uom_id ,c.qty_dlv_so as qty_int, c.base_uom_id , b.remark , 0 as version , b.create_datetime,
180 b.create_user_id,b.update_datetime,b.update_user_id
181 FROM sl_so_mlm_balance_product a
182 INNER JOIN sl_do_mlm b on b.ref_id = a.so_mlm_id
183 INNER JOIN sl_do_mlm_item c on b.do_mlm_id = c.do_mlm_id and a.product_id = c.product_id
184 WHERE a.so_mlm_id = vSoMlmId;
185
186 /**
187 * added by AY, 6 Apr 2016
188 * CR Voucher
189 * hanya yang belanja tanpa voucher
190 * */
191 INSERT INTO sl_log_promo_product_catalog_usage(
192 tenant_id, partner_id, doc_type_id,
193 doc_id, doc_no, doc_date, doc_item_id, promo_id, product_catalog_id,
194 qty, price_after_disc, version, create_datetime, create_user_id,
195 update_datetime, update_user_id)
196 SELECT A.tenant_id, A.partner_ship_to_id, A.doc_type_id,
197 A.do_mlm_id, A.doc_no, A.doc_date, B.do_mlm_item_id, B.promo_id, B.product_catalog_id,
198 C.qty_dlv_so, E.price_after_disc, 0, vDatetime, vUserId,
199 vDatetime, vUserId
200 FROM sl_do_mlm A
201 INNER JOIN sl_do_mlm_item_promo B ON A.do_mlm_id = B.do_mlm_id
202 INNER JOIN sl_do_mlm_item C ON B.do_mlm_item_id = C.do_mlm_item_id
203 INNER JOIN sl_so_mlm D ON A.ref_id = D.so_mlm_id
204 INNER JOIN sl_so_mlm_balance_product E ON D.so_mlm_id = E.so_mlm_id
205 INNER JOIN m_promo F ON B.promo_id = F.promo_id
206 WHERE B.product_catalog_id = E.product_catalog_id
207 AND A.do_mlm_id = vDoMlmId
208 AND D.doc_date BETWEEN F.giving_start_date AND F.giving_end_date
209 AND NOT EXISTS (
210 SELECT 1
211 FROM sl_so_mlm_item_voucher Z
212 JOIN m_promo_product_catalog_used Y ON Y.promo_id = Z.promo_id AND C.product_catalog_id = Y.product_catalog_id
213 WHERE Z.so_mlm_id = D.so_mlm_id
214 AND Z.qty > 0
215 );
216
217 /* 3. mengurangi reserved stock berdsarkan qty so */
218 UPDATE in_product_balance_stock_reserved a
219 SET qty = qty - c.qty_ord_so,
220 update_datetime = vDatetime, update_user_id = vUserId, version = a.version + 1
221 FROM sl_do_mlm b
222 INNER JOIN sl_do_mlm_item c on c.do_mlm_id = b.do_mlm_id
223 WHERE c.product_id = a.product_id
224 AND b.do_mlm_id = vDoMlmId;
225
226 /*4. update status doc = R di sl_do_mlm*/
227 UPDATE sl_do_mlm
228 SET status_doc = vStatusRelease , version = version+1 , update_user_id = vUserId , update_datetime = vDatetime
229 WHERE do_mlm_id = vDoMlmId;
230
231 /**
232 * 5. insert data ke tt_update_product_balance_stock_for_do_mlm untuk urutan prioritas pemotongan stock
233 * insert ke table temporary untuk bantuan pemotongan stock di table in_product_balance_stock
234 * dimana urutan pemotongan berdasarkan prioritas:
235 * 1. barang dengan product statu RETURN menjadi prioritas nomor 0
236 * 2. barang yang bukan RETURN diurutkan berdasarkan temp_warehouse_priority(gudangnya),dan whs location code
237 * 3. gudang dengan location DEF akan menjadi prioritas paling bawah
238 *
239 */
240 INSERT INTO tt_update_product_balance_stock_for_do_mlm
241 (session_id, warehouse_id, product_id, qty, qty_minus,product_balance_id,warehouse_priority,whs_location_code,product_status)
242 SELECT pSessionId,a.warehouse_id,a.product_id,SUM(a.qty),0 as qty_minus, a.product_balance_id ,
243 CASE WHEN a.product_status = vReturnValue THEN '0' ELSE d.priority_no END,
244 CASE WHEN a.whs_location_code = vDefValue THEN vDefStockLocCode ELSE a.whs_location_code END, a.product_status
245 FROM in_product_balance_stock a
246 JOIN sl_do_mlm_item b on a.product_id = b.product_id
247 JOIN m_warehouse c on a.warehouse_id = c.warehouse_Id
248 JOIN temp_warehouse_priority d on d.whs_id = c.warehouse_id
249 WHERE b.do_mlm_id = vDoMlmId AND a.product_status IN (vProductStatus , vReturnValue)
250 GROUP BY a.product_id,d.priority_no, a.warehouse_id,a.product_balance_id,a.whs_location_code, a.product_status;
251
252 -- melengkapi data stok yg tidak ada di in_product_balance_stock
253 INSERT INTO tt_update_product_balance_stock_for_do_mlm
254 (session_id, warehouse_id, product_id, qty, qty_minus,product_balance_id,warehouse_priority,whs_location_code,product_status)
255 SELECT pSessionId, C.whs_id, B.product_id, 0, 0, B.product_balance_id, C.priority_no, vDefStockLocCode, vGoodValue
256 FROM sl_do_mlm_item A, in_product_balance B, temp_warehouse_priority C
257 WHERE A.do_mlm_id = vDoMlmId AND
258 A.product_id = B.product_id AND
259 C.priority_no = '1' AND
260 NOT EXISTS (SELECT 1 FROM tt_update_product_balance_stock_for_do_mlm D
261 WHERE D.session_id = pSessionId AND
262 A.product_id = D.product_id AND
263 B.product_balance_id = D.product_balance_id AND
264 D.whs_location_code = vDefStockLocCode AND
265 C.whs_id = D.warehouse_id AND
266 D.product_status = vGoodValue);
267
268 /*
269 * 6. mengisi qty minus berdasarkan urutan prioritas
270 *
271 */
272
273 FOR vCursorForDoMlm IN
274 SELECT * FROM sl_do_mlm_item
275 WHERE do_mlm_id = vDoMlmId
276 LOOP
277 vQty = vCursorForDoMlm.qty_dlv_int;
278 vProductId = vCursorForDoMlm.product_id;
279 vTempTotal = vQty;
280 FOR vCursorForProduct IN
281 SELECT * FROM tt_update_product_balance_stock_for_do_mlm
282 WHERE session_id = pSessionId AND product_id = vProductId
283 ORDER BY warehouse_priority , whs_location_code ASC
284 LOOP
285
286 IF vTempTotal = 0 THEN
287 EXIT;
288 ELSEIF vCursorForProduct.qty > 0 AND vTempTotal > vCursorForProduct.qty THEN
289 vQtyMinus = vCursorForProduct.qty;
290 UPDATE tt_update_product_balance_stock_for_do_mlm
291 SET qty_minus = vQtyMinus
292 WHERE session_id = pSessionId AND product_id = vProductId AND id = vCursorForProduct.id;
293 vTempTotal = vTempTotal - vCursorForProduct.qty;
294
295 ELSEIF vCursorForProduct.qty > 0 THEN
296
297 vQtyMinus = vTempTotal;
298 RAISE NOTICE '%',vCursorForProduct.id;
299 UPDATE tt_update_product_balance_stock_for_do_mlm
300 SET qty_minus = vQtyMinus
301 WHERE session_id = pSessionId AND product_id = vProductId AND id = vCursorForProduct.id;
302 vTempTotal :=0;
303 END IF;
304
305 END LOOP;
306 IF vTempTotal > 0 THEN
307 UPDATE tt_update_product_balance_stock_for_do_mlm
308 SET qty_minus = qty_minus + vTempTotal
309 WHERE session_id = pSessionId AND
310 product_id = vProductId AND
311 warehouse_priority = '1' AND
312 whs_location_code = vDefStockLocCode AND
313 product_status = vGoodValue;
314 END IF;
315 END LOOP;
316
317
318 /* 7. Kembalikan 'ZZZZZZZZZZZZZZZZZZZZZ' yang berada di paling bawah menjadi 'DEF' */
319 -- kurang session id [DONE]
320 UPDATE tt_update_product_balance_stock_for_do_mlm
321 SET whs_location_code = vDefValue
322 WHERE whs_location_code = vDefStockLocCode AND session_id = pSessionId;
323
324
325
326 /*
327 * 8. potong stock berdasarkan qty minus
328 *
329 */
330/* NK, 10 Jan 2016 */
331 UPDATE in_product_balance_stock A
332 SET qty = A.qty - B.qty_minus,
333 update_datetime = vDatetime, update_user_id = vUserId, version = a.version + 1
334 FROM tt_update_product_balance_stock_for_do_mlm B
335 WHERE B.session_id = pSessionId AND
336 B.qty_minus > 0 AND
337 A.product_id = B.product_id AND
338 A.whs_location_code = B.whs_location_code AND
339 A.warehouse_id = B.warehouse_id AND
340 A.product_balance_id = B.product_balance_id AND
341 A.product_status = B.product_status;
342
343
344 INSERT INTO in_product_balance_stock
345 (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
346 version, create_datetime, create_user_id, update_datetime, update_user_id, whs_location_code)
347 SELECT pTenantId, A.warehouse_id, A.product_id, A.product_balance_id, A.product_status, C.base_uom_id, A.qty_minus * -1,
348 0,vDatetime, vUserId, vDatetime , vUserId, A.whs_location_code
349 FROM tt_update_product_balance_stock_for_do_mlm A, m_product C
350 WHERE A.session_id = pSessionId AND
351 A.product_id = C.product_id AND
352 A.qty_minus > 0 AND
353 NOT EXISTS (SELECT 1 FROM in_product_balance_stock B
354 WHERE A.product_id = B.product_id AND
355 A.whs_location_code = B.whs_location_code AND
356 A.warehouse_id = B.warehouse_id AND
357 A.product_balance_id = B.product_balance_id AND
358 A.product_status = B.product_status);
359
360/* Yang Lama bukan NK
361 FOR vCursorForProduct IN
362 SELECT * FROM tt_update_product_balance_stock_for_do_mlm
363 WHERE session_id = pSessionId AND qty_minus > 0
364 LOOP
365 UPDATE in_product_balance_stock a
366 SET qty = vCursorForProduct.qty-vCursorForProduct.qty_minus,
367 update_datetime = vDatetime, update_user_id = vUserId, version = a.version + 1
368 FROM tt_update_product_balance_stock_for_do_mlm b
369 WHERE a.product_id = vCursorForProduct.product_id
370 AND a.whs_location_code = vCursorForProduct.whs_location_code
371 AND a.warehouse_id = vCursorForProduct.warehouse_Id
372 AND a.product_balance_id = vCursorForProduct.product_balance_id
373 AND a.product_status = vCursorForProduct.product_status;
374
375 END LOOP;
376*/
377
378 /*
379 * 9.buat log untuk potong stock berdasarkan qty minus
380 * untuk barang bukan consigment log di buat di in_log_product_balance_stock
381 * kriteria bukan consignment:
382 * 1. flg_buy_konsinyasi N
383 * 2. flg_buy_konsinyasi Y dan product statusnya RETURN
384 *
385 * Ali, 18 Dec
386 * Menambahkan GROUP BY
387 * qty_minus harus di SUM() dan dikalikan -1
388 */
389
390
391
392
393 -- 1. flg_buy_konsinyasi N -- kurang join dengan product balance id, product status [DONE]
394 INSERT INTO in_log_product_balance_stock
395 (tenant_id,ou_id,doc_type_id,ref_id,doc_no,doc_date,partner_id,product_id,warehouse_id,product_balance_id ,
396 product_status,base_uom_id,qty,version,create_datetime,create_user_id,update_datetime,update_user_id,whs_location_code,product_catalog_id)
397 SELECT A.tenant_id , A.ou_id ,A.doc_type_id , A.do_mlm_id , A.doc_no, A.doc_date,A.partner_ship_to_id , E.product_id ,
398 E.warehouse_id, E.product_balance_id, E.product_status, C.base_uom_id ,
399 SUM(E.qty_minus) * -1, 0, vDatetime, vUserId, vDatetime, vUserId, E.whs_location_code,B.product_catalog_id
400 FROM sl_do_mlm A
401 JOIN sl_do_mlm_item B ON A.do_mlm_id = B.do_mlm_id
402 JOIN m_product C ON B.product_id = C.product_id
403 JOIN m_product_custom D ON C.product_id = D.product_id
404 INNER JOIN tt_update_product_balance_stock_for_do_mlm E
405 ON B.product_id = E.product_id
406 WHERE A.do_mlm_id = vDoMlmId
407 AND D.flg_buy_konsinyasi = vNoValue
408 AND E.session_id = pSessionId
409 AND E.qty_minus > 0
410 GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.do_mlm_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
411 E.product_id, E.product_balance_id, E.warehouse_id, E.product_status, C.base_uom_id, E.whs_location_code,B.product_catalog_id;
412
413 --2. flg_buy_konsinyasi Y dan product statusnya RETURN [DONE]
414 /*
415 * Ali, 18 Dec
416 * Menambahkan GROUP BY
417 * qty_minus harus di SUM() dan dikalikan -1
418 */
419 INSERT INTO in_log_product_balance_stock(tenant_id,ou_id,doc_type_id,ref_id,doc_no,doc_date,partner_id,product_id,warehouse_id,product_balance_id ,
420 product_status,base_uom_id,qty,version,create_datetime,create_user_id,update_datetime,update_user_id,whs_location_code,product_catalog_id)
421 SELECT A.tenant_id , A.ou_id ,A.doc_type_id , A.do_mlm_id , A.doc_no, A.doc_date,A.partner_ship_to_id,
422 E.product_id ,E.warehouse_id, E.product_balance_id , E.product_status, C.base_uom_id ,
423 SUM(E.qty_minus) * -1, 0, vDatetime, vUserId, vDatetime, vUserId, E.whs_location_code,B.product_catalog_id
424 FROM sl_do_mlm A
425 JOIN sl_do_mlm_item B ON A.do_mlm_id = B.do_mlm_id
426 JOIN m_product C ON B.product_id = C.product_id
427 JOIN m_product_custom D ON C.product_id = D.product_id
428 INNER JOIN tt_update_product_balance_stock_for_do_mlm E
429 ON B.product_id = E.product_id
430 WHERE A.do_mlm_id = vDoMlmId
431 AND D.flg_buy_konsinyasi = vYesValue
432 AND E.product_status = vReturnValue
433 AND E.session_id = pSessionId
434 AND E.qty_minus > 0
435 GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.do_mlm_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
436 E.product_id, E.product_balance_id, E.warehouse_id, E.product_status, C.base_uom_id, E.whs_location_code,B.product_catalog_id;
437
438
439 /*
440 * 10. insert ke in_log_product_consignment_balance_stock bila itu merupakan product consignment
441 * product status GOOD dan flg_buy_konsinyasi = 'Y'
442 *
443 * tambahkan product status GOOD [DONE]
444 *
445 * Ali, 18 Dec
446 * Menambahkan GROUP BY
447 * qty_minus harus di SUM() dan dikalikan -1
448 */
449 INSERT INTO in_log_product_consignment_balance_stock
450 (tenant_id,ou_id,doc_type_id,ref_id,doc_no,doc_date,partner_id,product_id,warehouse_id,product_balance_id ,
451 product_status,base_uom_id,qty,version,create_datetime,create_user_id,update_datetime,update_user_id,whs_location_code,product_catalog_id)
452 SELECT A.tenant_id, A.ou_id ,A.doc_type_id, A.do_mlm_id, A.doc_no, A.doc_date, A.partner_ship_to_id ,
453 E.product_id, E.warehouse_id, E.product_balance_id, E.product_status,C.base_uom_id,
454 SUM(E.qty_minus) * -1, 0, vDatetime, vUserId, vDatetime, vUserId, E.whs_location_code,B.product_catalog_id
455 FROM sl_do_mlm A
456 JOIN sl_do_mlm_item B ON A.do_mlm_id = B.do_mlm_id
457 JOIN m_product C ON B.product_id = C.product_id
458 JOIN m_product_custom D ON C.product_id = D.product_id
459 INNER JOIN tt_update_product_balance_stock_for_do_mlm E
460 ON B.product_id = E.product_id
461 WHERE A.do_mlm_id = vDoMlmId
462 AND D.flg_buy_konsinyasi = vYesValue
463 AND E.product_status <> vReturnValue
464 AND E.session_id = pSessionId
465 AND E.qty_minus > 0
466 GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.do_mlm_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
467 E.product_id, E.product_balance_id, E.warehouse_id, E.product_status, C.base_uom_id,E.whs_location_code,B.product_catalog_id;
468
469 /*
470 * TODO (fase 2 seharusnya tidak boleh dipasang flg di sl_so_mlm seharusnya di buat d table baru)
471 * update status sl so mlm
472 *
473 */
474 UPDATE sl_so_mlm SET
475 flg_scan = vYesValue , flg_packing = vYesValue , version = version + 1,
476 update_datetime = vDatetime , update_user_id = vUserId
477 WHERE so_mlm_id = vSoMlmId;
478
479 /*
480 * buat data in_balance_do_mlm_item
481 *
482 */
483 INSERT INTO in_balance_do_mlm_item
484 (do_mlm_item_id, tenant_id, ou_id, do_mlm_id, doc_no, doc_date, partner_id,
485 so_mlm_id, so_mlm_no, so_date, so_mlm_balance_product_id,
486 qty_dlv, qty_return, so_uom_id, qty_dlv_int,
487 qty_return_int, base_uom_id, status_item,
488 "version", create_datetime, create_user_id, update_datetime, update_user_id)
489 SELECT B.do_mlm_item_id, A.tenant_id, A.ou_id, A.do_mlm_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
490 A.ref_id, C.doc_no, C.doc_date, D.so_mlm_balance_product_id,
491 SUM(B.qty_dlv_so), 0, B.so_uom_id, SUM(B.qty_dlv_int),
492 0, B.base_uom_id, vStatusRelease,
493 0, vDatetime, vUserId, vDatetime, vUserId
494 FROM sl_do_mlm A, sl_do_mlm_item B, sl_so_mlm C, sl_so_mlm_balance_product D
495 WHERE A.do_mlm_id = vDoMlmId AND
496 A.do_mlm_id = B.do_mlm_id AND
497 A.ref_id = C.so_mlm_id AND
498 C.so_mlm_id = D.so_mlm_id AND
499 B.product_id = D.product_id AND
500 B.product_catalog_id = D.product_catalog_id
501 GROUP BY D.so_mlm_balance_product_id, A.tenant_id, A.ou_id, A.do_mlm_id, A.doc_no, A.doc_date, A.partner_ship_to_id,
502 A.ref_id, C.doc_no, C.doc_date, B.do_mlm_item_id, B.so_uom_id, B.base_uom_id;
503
504 -- fredi, 19 Jan 2016 : dsCommission ambil dari sl_so_mlm bersangkutan
505 SELECT B.commission_percentage INTO vDsCommission
506 FROM sl_do_mlm A
507 INNER JOIN sl_so_mlm B ON A.ref_id = B.so_mlm_id
508 WHERE A.do_mlm_id = vDoMlmId;
509
510 -- harus pastikan ke user bahwa yang dianggap sebagai usage adalah price_after_disc
511 -- Mentotalkan, usage amount yang mau dipotong berdasarkan qty_cancel (SO yang di revisi)
512 -- update: ditambahkan coalesce jika tidak ada yang di cancel maka nilai nya 0
513 SELECT
514 COALESCE((SELECT SUM(b.qty_dlv * b.price_after_disc)
515 FROM sl_do_mlm a
516 JOIN sl_so_mlm_balance_product b ON b.so_mlm_id = a.ref_id
517 WHERE a.do_mlm_id = vDoMlmId
518 GROUP BY b.so_mlm_id),0)
519 INTO vTotalHargaNetto;
520
521 SELECT f_get_amount_after_substract_by_percentage(vTotalHargaNetto, vDsCommission, vRoundingScaleNonTax) INTO vAmountAfterCommision;
522
523 SELECT f_get_so_mlm_additional_cost(a.ref_id) INTO vAdditionalCost
524 FROM sl_do_mlm a
525 WHERE a.do_mlm_id = vDoMlmId;
526
527 IF vAmountAfterCommision <> 0 THEN
528 -- Update usage amount, dikurangi dengan totalQtyCancelAmount
529
530 UPDATE sl_partner_credit_limit_usage B
531 SET usage_amount = B.usage_amount + vAmountAfterCommision + vAdditionalCost,
532 update_datetime = vDatetime, update_user_id = vUserId, version = B.version + 1
533 FROM sl_do_mlm A
534 WHERE A.do_mlm_id = vDoMlmId
535 AND B.partner_id = A.partner_ship_to_id
536 AND B.year_month = SUBSTR(A.doc_date,1,6);
537
538 -- insert sl_log_partner_credit_limit_usage
539 INSERT INTO sl_log_partner_credit_limit_usage
540 (tenant_id,partner_id,doc_type_id,doc_id,doc_no,doc_date,amount,version,create_datetime,create_user_id,update_datetime,update_user_id,record_type,remark)
541 SELECT a.tenant_id , a.partner_ship_to_id ,a.doc_type_id , a.do_mlm_id , a.doc_no,a.doc_date,vAmountAfterCommision+vAdditionalCost,0,vDatetime,vUserId,vDatetime,vUserId, 1, A.remark
542 FROM sl_do_mlm a
543 WHERE a.do_mlm_id = vDoMlmId;
544 END IF;
545
546 /**
547 * AY, 8 Sept 2015
548 * Mencatat data log produk konsinyasi yang sudah terjual
549 * Kurang A.status_item = 'R' atau 'I'
550 * */
551
552 --mengambil product id dan qty_minus dari tt_update_product_balance_stock_for_do_mlm
553 WITH summary_qty_for_insert_pu_log_po_balance_item_consignment_sold AS(
554 SELECT A.product_id, SUM(qty_minus) AS temp_qty_sell
555 FROM tt_update_product_balance_stock_for_do_mlm A
556 INNER JOIN m_product_custom B ON A.product_id = B.product_id
557 WHERE A.session_id = pSessionId
558 AND A.product_status = vGoodValue
559 AND B.flg_buy_konsinyasi = vYesValue
560 GROUP BY A.product_id )
561 -- nilai qty DLV tidak boleh ambil dari sl_so_mlm_balance_product [DONE]
562 -- harusnya dari table tt_update_product_balance_stock_for_do_mlm, hanya SUM yg product statusya GOOD [DONE]
563 INSERT INTO pu_log_po_balance_item_consignment_sold(
564 tenant_id, po_id, po_item_id, so_id, so_balance_product_id,
565 doc_type_id, doc_id, qty, flg_process, ref_doc_type_id, ref_id,
566 supplier_id, gross_price_po, margin_supp_percentage, margin_supp_amount,
567 ref_log_id, version, create_datetime, create_user_id, update_datetime,
568 update_user_id, remark, doc_date)
569 SELECT pTenantId, B.po_id, B.po_item_id, L.so_mlm_id, L.so_mlm_balance_product_id,
570 I.doc_type_id, I.do_mlm_id, temp_qty_sell, vNoValue, -99, -99,
571 G.partner_id, B.gross_price_po, B.margin_supplier_percentage, B.margin_supplier_amount,
572 -99, 0, vDatetime, vUserId, vDatetime,
573 vUserId, '' , I.doc_date
574 FROM pu_po_balance_item_consignment A
575 JOIN pu_po_item B ON A.po_item_id = B.po_item_id AND A.status_item IN(vStatusRelease,vInprogressValue)
576 JOIN pu_po G ON G.po_id = B.po_id
577 JOIN sl_do_mlm_item H ON H.product_id = B.product_id
578 JOIN sl_do_mlm I ON I.do_mlm_id = H.do_mlm_id
579 JOIN sl_so_mlm_balance_product L ON L.so_mlm_id = I.ref_id AND L.product_id = H.product_id AND L.qty_dlv <> 0
580 JOIN m_product_custom E ON H.product_id = E.product_id
581 JOIN m_product F ON F.product_id=H.product_id
582 JOIN summary_qty_for_insert_pu_log_po_balance_item_consignment_sold M ON M.product_id=L.product_id
583 WHERE I.do_mlm_id = vDoMlmId
584 AND E.flg_buy_konsinyasi = vYesValue;
585
586 WITH summary_qty_for_insert_pu_log_po_balance_item_consignment_sold AS(
587 SELECT A.product_id, SUM(qty_minus) AS temp_qty_sell
588 FROM tt_update_product_balance_stock_for_do_mlm A
589 INNER JOIN m_product_custom B ON A.product_id = B.product_id
590 WHERE A.session_id = pSessionId
591 AND A.product_status = vGoodValue
592 AND B.flg_buy_konsinyasi = vYesValue
593 GROUP BY A.product_id )
594 INSERT INTO pu_log_po_balance_item_consignment_sold_no_po(
595 tenant_id, so_id, so_balance_product_id, doc_item_id, product_id,
596 doc_type_id, doc_id, doc_date, doc_no,
597 qty, flg_process, remark,
598 version, create_datetime, create_user_id, update_datetime, update_user_id)
599 SELECT pTenantId, L.so_mlm_id, L.so_mlm_balance_product_id, H.do_mlm_item_id, H.product_id,
600 I.doc_type_id, I.do_mlm_id, I.doc_date, I.doc_no,
601 temp_qty_sell, vNoValue, 'NO COMM SHEET FOR THIS PRODUCT',
602 0, vDatetime, vUserId, vDatetime, vUserId
603 FROM sl_do_mlm_item H
604 JOIN sl_do_mlm I ON I.do_mlm_id = H.do_mlm_id
605 JOIN sl_so_mlm_balance_product L ON L.so_mlm_id = I.ref_id AND L.product_id = H.product_id AND L.qty_dlv <> 0
606 JOIN m_product_custom E ON H.product_id = E.product_id
607 JOIN m_product F ON F.product_id=H.product_id
608 JOIN summary_qty_for_insert_pu_log_po_balance_item_consignment_sold M ON M.product_id=L.product_id
609 WHERE I.do_mlm_id = vDoMlmId
610 AND E.flg_buy_konsinyasi = vYesValue
611 AND NOT EXISTS (
612 SELECT 1 FROM pu_po_balance_item_consignment Z
613 JOIN pu_po_item Y ON Z.po_item_id = Y.po_item_id
614 WHERE Z.status_item IN(vStatusRelease,vInprogressValue)
615 AND H.product_id = Y.product_id
616 );
617
618 /**
619 * AY, 15 Sept 2015
620 * Membuat saldo invoice untuk barang yang terjual
621 *
622 * NJ, 28 Oct 2015
623 * Mengubah isi column price_so memakai gross_sell_price yang di dapat dari table sl_so_mlm_balance_product
624 * Mengubah isi column item_amount ada memakai gross_sell_price yang di dapat dari table sl_so_mlm_balance_product
625 * */
626 INSERT INTO sl_so_mlm_balance_invoice(
627 tenant_id, ou_id, partner_id, so_id, ref_doc_type_id,
628 ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_dlv_so, so_uom_id, curr_code, price_so,
629 item_amount, tax_percentage, tax_id, discount_member_percentage, discount_promo_percentage,
630 flg_invoice, invoice_id, version, create_datetime, create_user_id, update_datetime, update_user_id)
631 SELECT pTenantId, a.ou_id, c.partner_bill_to_id, a.ref_id, vDeliveryOrderMlmDocTypeId,
632 a.do_mlm_id, a.doc_no, a.doc_date, b.do_mlm_item_id, b.qty_dlv_so, b.so_uom_id, c.curr_code, d.gross_sell_price,
633 --f_get_amount_before_tax(b.qty_dlv_so * d.gross_sell_price, d.flg_tax_amount, d.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderMlmDocTypeId, c.curr_code), vRoundingModeNonTax),
634 b.qty_dlv_so * d.gross_sell_price, d.tax_percentage, d.tax_id, d.discount_member_percentage, d.discount_promo_percentage,
635 vNoValue, -99, 0, vDatetime, vUserId, vDatetime, vUserId
636 FROM sl_do_mlm a
637 INNER JOIN sl_do_mlm_item b on a.do_mlm_id = b.do_mlm_id
638 INNER JOIN sl_so_mlm c on a.ref_id = c.so_mlm_id
639 INNER JOIN sl_so_mlm_balance_product d on c.so_mlm_id = d.so_mlm_id and b.product_catalog_id = d.product_catalog_id
640 WHERE a.tenant_id = pTenantId and
641 a.do_mlm_id = vDoMlmId and
642 b.qty_dlv_so > 0;
643
644 --mengambil product id dan qty_minus dari tt_update_product_balance_stock_for_do_mlm
645 WITH summary_qty_for_update_pu_po_balance_item_consignment AS(
646 SELECT A.product_id, SUM(qty_minus) AS temp_qty_sell
647 FROM tt_update_product_balance_stock_for_do_mlm A
648 INNER JOIN m_product_custom B ON A.product_id = B.product_id
649 WHERE A.session_id = pSessionId
650 AND A.product_status = vGoodValue
651 AND B.flg_buy_konsinyasi = vYesValue
652 GROUP BY A.product_id )
653
654 -- update pu_po_balance_item_consignment, qty_sell
655 -- hanya untuk barang konsinyasi
656 -- po_item_id -> product_id --> status_item R/ Is
657
658 UPDATE pu_po_balance_item_consignment A
659 SET update_datetime=vDatetime, update_user_id=vUserId, qty_int_sell=A.qty_int_sell+C.temp_qty_sell,
660 qty_sell= A.qty_sell+C.temp_qty_sell
661 FROM pu_po_item B , summary_qty_for_update_pu_po_balance_item_consignment C
662 WHERE A.po_item_id = B.po_item_id
663 AND B.product_id = C.product_id
664 AND A.status_item IN(vStatusRelease,vInprogressValue);
665
666/* Not used anymore
667 INSERT INTO sl_so_mlm_balance_invoice_tax(
668 tenant_id, ou_id, partner_id, so_id,
669 ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount, tax_percentage, curr_code,
670 base_amount,
671 tax_amount, flg_invoice, invoice_id, version, create_datetime, create_user_id, update_datetime, update_user_id)
672 SELECT pTenantId, a.ou_id, c.partner_bill_to_id, a.ref_id,
673 vDeliveryOrderMlmDocTypeId, a.do_mlm_id, b.do_mlm_item_id, d.tax_id, 'Y', d.tax_percentage, c.curr_code,
674 f_get_amount_before_tax(b.qty_dlv_so * d.price_after_disc, d.flg_tax_amount, d.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderMlmDocTypeId, c.curr_code), vRoundingModeNonTax),
675 --d.nett_sell_price * b.qty_dlv_so,
676 f_tax_rounding(a.tenant_id, f_get_amount_before_tax(b.qty_dlv_so * d.gross_sell_price, d.flg_tax_amount, d.tax_percentage, f_get_digit_decimal_doc_curr(vDeliveryOrderMlmDocTypeId, c.curr_code), vRoundingModeNonTax), d.tax_percentage),
677 --d.tax_price * b.qty_dlv_so,
678 'N', -99, 0, vDatetime, vUserId, vDatetime, vUserId
679 FROM sl_do_mlm a
680 INNER JOIN sl_do_mlm_item b on a.do_mlm_id = b.do_mlm_id
681 INNER JOIN sl_so_mlm c on a.ref_id = c.so_mlm_id
682 INNER JOIN sl_so_mlm_balance_product d on c.so_mlm_id = d.so_mlm_id and b.product_id = d.product_id
683 WHERE a.tenant_id = pTenantId and
684 a.do_mlm_id = vDoMlmId and
685 b.qty_dlv_so > 0;
686*/
687
688
689 -- insert into in_balance_item_exchange
690 INSERT INTO in_balance_item_exchange(
691 tenant_id, doc_type_id, doc_id, doc_item_id,
692 product_id, qty, qty_exchange, ref_doc_type_id, ref_doc_id, status_item, product_catalog_id,
693 version, create_datetime, create_user_id, update_datetime, update_user_id)
694 SELECT pTenantId, A.doc_type_id, A.do_mlm_id, B.do_mlm_item_id,
695 B.product_id, B.qty_dlv_int, 0, A.doc_type_id, A.do_mlm_id, vStatusRelease, B.product_catalog_id,
696 0,vDatetime , vUserId, vDatetime, vUserId
697 FROM sl_do_mlm A
698 INNER JOIN sl_do_mlm_item B ON A.do_mlm_id = B.do_mlm_id
699 WHERE A.do_mlm_id = vDoMlmId;
700
701 -- insert into in_log_balance_item_exchange
702 INSERT INTO in_log_balance_item_exchange(
703 tenant_id, doc_type_id, doc_id, product_id, product_catalog_id,
704 doc_no, doc_date, doc_item_id, ref_doc_type_id, ref_doc_id, ref_doc_no,
705 ref_doc_date, remark, qty, version, create_datetime, create_user_id,
706 update_datetime, update_user_id)
707 SELECT pTenantId, A.doc_type_id, A.do_mlm_id, B.product_id, B.product_catalog_id,
708 A.doc_no, A.doc_date, B.do_mlm_item_id, vEmptyValueId, vEmptyValueId, '',
709 '', A.remark, B.qty_dlv_int, 0, vDatetime, vUserId,
710 vDatetime, vUserId
711 FROM sl_do_mlm A
712 INNER JOIN sl_do_mlm_item B ON A.do_mlm_id = B.do_mlm_id
713 INNER JOIN sl_so_mlm C ON A.ref_id = C.so_mlm_id
714 WHERE A.do_mlm_id = vDoMlmId;
715
716
717 INSERT INTO sl_do_shipping(
718 do_mlm_id, expedition_id, expedition_amount, create_datetime,
719 create_user_id, update_datetime, update_user_id, version)
720 SELECT vDoMlmId, expedition_id, expedition_amount, vDatetime,
721 vUserId, vDatetime, vUserId, 0
722 FROM sl_do_mlm A
723 INNER JOIN m_partner_expedition B
724 ON A.partner_ship_to_id = B.partner_id
725 WHERE A.do_mlm_id = vDoMlmId;
726
727 DELETE FROM tt_update_product_balance_stock_for_do_mlm
728 WHERE session_id = pSessionId;
729
730 -- masukan kedalam table temp
731 INSERT INTO tt_product_voucher_do(session_id,promo_id,product_id,product_catalog_id,qty_deliver,price_after_disc,total_item_amount,qty_so)
732 SELECT pSessionId,B.promo_id,A.product_id,A.product_catalog_id,A.qty_dlv_int AS qty_deliver,D.price_after_disc,A.qty_dlv_int * D.price_after_disc AS total_item_amount,D.qty_so
733 FROM sl_do_mlm_item A
734 JOIN m_promo_product_catalog_used B ON A.product_catalog_id = B.product_catalog_id
735 JOIN sl_do_mlm C ON A.do_mlm_id = C.do_mlm_id
736 JOIN sl_so_mlm_balance_product D ON D.product_id = A.product_id AND D.product_catalog_id = A.product_catalog_id AND D.so_mlm_id = C.ref_id
737 WHERE A.do_mlm_id = vDoMlmId;
738
739 -- hitung total item yang bisa digunakan voucher
740 IF EXISTS(
741 SELECT 1
742 FROM tt_product_voucher_do
743 WHERE qty_so > qty_deliver
744 AND session_id = pSessionId
745 ) THEN
746
747 -- insert into table temp
748 WITH voucher_return AS(
749
750 SELECT A.promo_id,(
751 FLOOR(SUM(C.total_item_amount)/B.giving_amount)
752 ) AS qty_penggunaan, A.so_mlm_id,A.qty as qty_voucher
753 FROM sl_so_mlm_item_voucher A
754 JOIN m_promo B ON A.promo_id = B.promo_id
755 JOIN tt_product_voucher_do C ON C.promo_id = B.promo_id
756 WHERE A.so_mlm_id = vSoMlmId
757 AND C.session_id = pSessionId
758 GROUP BY A.promo_id, A.so_mlm_id,B.giving_amount,A.qty
759 )
760 INSERT INTO tt_update_voucher(
761 session_id,promo_id,qty_voucher_so,qty_voucher_do,qty_voucher_return,so_mlm_id,partner_id,
762 doc_no,doc_date,doc_type_id,doc_id
763 )
764 SELECT pSessionId,A.promo_id,A.qty_voucher,LEAST(A.qty_penggunaan,A.qty_voucher),(A.qty_voucher-LEAST(A.qty_penggunaan,A.qty_voucher)) AS qty_return,A.so_mlm_id,vPartnerId,
765 B.doc_no,B.doc_date,B.doc_type_id,B.so_mlm_id
766 FROM voucher_return A
767 JOIN sl_so_mlm B ON A.so_mlm_id = B.so_mlm_id;
768
769
770 -- Update nilai penggunaan voucher
771 UPDATE sl_so_mlm_item_voucher B
772 SET qty = A.qty_voucher_do,
773 version =B.version+1,
774 update_user_id = vUserId,
775 update_datetime = vDatetime
776 FROM tt_update_voucher A
777 WHERE A.promo_id = B.promo_id
778 AND B.so_mlm_id = vSoMlmId
779 AND A.session_id =pSessionId;
780
781 -- Buat Log voucher yang di return
782 INSERT INTO sl_log_partner_promo_balance(
783 tenant_id,partner_id,promo_id,qty_usage_so,
784 create_datetime,create_user_id,update_datetime,update_user_id,
785 version,qty_cancel,qty_usage_do,ref_doc_type_id,ref_doc_no,ref_doc_date,remark,ref_id
786 )
787 SELECT
788 A.tenant_id,A.partner_id,A.promo_id,B.qty_voucher_so,
789 vDatetime,vUserId,vDatetime,vUserId,
790 0,B.qty_voucher_return,B.qty_voucher_do,B.doc_type_id,B.doc_no,B.doc_date,
791 'Voucher '||C.promo_code||' digunakan sebanyak '||B.qty_voucher_do||', Voucher dikembalikan sebanyak '||B.qty_voucher_return,
792 B.doc_id
793 FROM sl_partner_promo_balance A
794 JOIN tt_update_voucher B ON A.promo_id = B.promo_id AND A.partner_id = B.partner_id
795 JOIN m_promo C ON B.promo_id =C.promo_id
796 WHERE B.session_id = pSessionId;
797
798 -- Update Balance voucher
799 UPDATE sl_partner_promo_balance B
800 SET qty = B.qty+ A.qty_voucher_return,
801 version =B.version+1,
802 update_user_id = vUserId,
803 update_datetime = vDatetime
804 FROM tt_update_voucher A
805 WHERE A.promo_id = B.promo_id
806 AND B.partner_id = A.partner_id
807 AND A.partner_id = vPartnerId
808 AND A.so_mlm_id = vSoMlmId
809 AND A.session_id =pSessionId;
810
811 ELSE
812 -- buat log untuk do non revisi
813 INSERT INTO sl_log_partner_promo_balance(
814 tenant_id,partner_id,promo_id,qty_usage_so,
815 create_datetime,create_user_id,update_datetime,update_user_id,
816 version,qty_cancel,qty_usage_do,ref_doc_type_id,ref_doc_no,ref_doc_date,remark,ref_id
817 )
818 SELECT
819 A.tenant_id,A.partner_id,A.promo_id,B.qty,
820 vDatetime,vUserId,vDatetime,vUserId,
821 0,0,B.qty,C.doc_type_id,C.doc_no,C.doc_date,
822 'Voucher '||E.promo_code||' digunakan sebanyak '||B.qty,C.so_mlm_id
823 FROM sl_partner_promo_balance A
824 JOIN sl_so_mlm_item_voucher B ON A.promo_id = B.promo_id
825 JOIN sl_so_mlm C ON B.so_mlm_id = C.so_mlm_id AND C.partner_id = A.partner_id
826 JOIN m_promo E ON A.promo_id = E.promo_id
827 WHERE C.so_mlm_id = vSoMlmId;
828
829 END IF;
830
831 -- Convert to CNAR
832 SELECT SUM(A.qty_dlv*A.price_after_disc) INTO vPriceAfterDisc
833 FROM sl_so_mlm_balance_product A
834 JOIN sl_so_mlm_item_voucher B ON A.so_mlm_id = B.so_mlm_id
835 JOIN m_promo_product_catalog_used C ON A.product_catalog_id = C.product_catalog_id AND B.promo_id = C.promo_id
836 WHERE A.so_mlm_id =vSoMlmId;
837
838 SELECT B.qty*D.giving_amount INTO vVoucher
839 FROM sl_so_mlm_balance_product A
840 JOIN sl_so_mlm_item_voucher B ON A.so_mlm_id = B.so_mlm_id
841 JOIN m_promo_product_catalog_used C ON A.product_catalog_id = C.product_catalog_id AND B.promo_id = C.promo_id
842 JOIN m_promo D ON B.promo_id =D.promo_id
843 WHERE A.so_mlm_id =vSoMlmId;
844
845 IF vPriceAfterDisc <= vVoucher THEN
846 vAmountConvert = vPriceAfterDisc;
847 ELSE
848 vAmountConvert = vVoucher;
849 END IF;
850
851 -- insert into fi_invoice_ar
852 INSERT INTO fi_invoice_ar(
853 tenant_id,doc_type_id,doc_no,doc_date,ou_id,
854 partner_id,ext_doc_no,ext_doc_date,ref_doc_type_id,
855 ref_id,due_date,curr_code,total_tax_base_amount,
856 total_amount,tax_amount,remark,status_doc,
857 workflow_status, version, create_datetime,create_user_id,update_datetime,update_user_id
858 )
859 SELECT pTenantId, vCNARDocTypeId, A.doc_no,A.doc_date,A.ou_id,
860 vPartnerId,'','',A.doc_type_id,
861 A.so_mlm_id, A.doc_date, A.curr_code, 0,
862 vAmountConvert,0,'VOUCHER DIKONVERSI KE MUTASI SALDO REF:'||A.doc_no,vStatusRelease,
863 vStateReleased, 0,vDatetime,vUserId,vDatetime,vUserId
864 FROM sl_so_mlm A
865 JOIN sl_so_mlm_item_voucher B ON A.so_mlm_id = B.so_mlm_id
866 JOIN m_promo C ON C.promo_id = B.promo_id
867 WHERE A.so_mlm_id = vSoMlmId
868 GROUP BY A.doc_no,A.doc_date,A.ou_id,
869 A.doc_type_id,A.so_mlm_id, A.doc_date, A.curr_code;
870
871 -- get activity gl
872 SELECT activity_gl_id INTO vActivityGlId
873 FROM m_activity_gl
874 WHERE activity_gl_code = vConstantGLBiayaVoucher;
875
876 -- insert into fi_invoice_ar_cost
877 INSERT INTO fi_invoice_ar_cost(
878 tenant_id,invoice_ar_id,line_no,activity_gl_id,
879 ou_rc_id,curr_code,add_amount,tax_id,tax_percentage,
880 tax_amount,remark,
881 version,create_datetime,create_user_id,update_datetime,update_user_id)
882
883 SELECT B.tenant_id, A.invoice_ar_id, row_number() OVER (ORDER BY A.invoice_ar_id), vActivityGlId,
884 -99, B.curr_code,vAmountConvert, -99, 0,
885 0,B.remark,
886 0 ,vDatetime,vUserId,vDatetime,vUserId
887 FROM fi_invoice_ar A
888 JOIN sl_so_mlm B ON A.ref_doc_type_id = B.doc_type_id AND A.ref_id = B.so_mlm_id
889 JOIN sl_so_mlm_item_voucher C ON C.so_mlm_id = B.so_mlm_id
890 JOIN m_promo D ON C.promo_id = D.promo_id
891 WHERE B.so_mlm_id = vSoMlmId
892 GROUP BY B.tenant_id, A.invoice_ar_id,B.curr_code,B.remark;
893
894 FOR vCursorForInvoiceAr IN
895 SELECT A.*
896 FROM fi_invoice_ar A
897 WHERE A.ref_id = vSoMlmId
898 AND A.ref_doc_type_id = vSoMlmDocTypeId
899 LOOP
900 PERFORM mlm_convert_to_cnar_for_voucher(pTenantId, pSessionId, vCursorForInvoiceAr.invoice_ar_id,vUserId,vDatetime);
901
902 -- 6.
903 -- Mendapatkan default approval flow ID yang dipakai dari sysconfig
904 SELECT awe_flow_id INTO vFlowInvoiceArId
905 FROM awe_flow WHERE scheme = vInvoiceArDocScheme AND flg_validate='Y' LIMIT 1;
906
907 -- Generate unique Request ID
908 SELECT f_make_uid() INTO vReqId;
909
910 -- Generate data awe_currdoc_status
911 INSERT INTO awe_currdoc_status(
912 req_id, tenant_id, scheme, doc_id, doc_no, doc_date, current_state,
913 remark, current_user_id, current_role_id, flg_user_role, label,
914 data, flow_id, create_datetime, create_user_id, create_role_id,
915 update_datetime, update_user_id, update_role_id, version)
916 SELECT vReqId, pTenantId,vInvoiceArDocScheme, invoice_ar_id, doc_no, doc_date, vStateApproved,
917 ' ', -99, vRoleId, vFlagRole, 'CNAR_VOUCHER_BELANJA_DS_'||doc_no,
918 '{}', vFlowInvoiceArId, vDatetime, vUserId, vRoleId, vDatetime,vUserId, vRoleId, 0
919 FROM fi_invoice_ar WHERE invoice_ar_id = vCursorForInvoiceAr.invoice_ar_id;
920
921 -- Generate data awe_worklist
922 INSERT INTO awe_worklist(
923 req_id, user_id, role_id, no_item, tenant_id, scheme, doc_id,
924 doc_no, doc_date, status, remark, flg_user_role, flg_to_do, create_datetime,
925 create_user_id, update_datetime, update_user_id, version)
926 SELECT vReqId, -99, vRoleId, 1, pTenantId, vInvoiceArDocScheme, invoice_ar_id,
927 doc_no, doc_date, vStateApproved, ' ', vFlagRole, vNoValue, vDatetime,
928 vUserId, vDatetime, vUserId, 0
929 FROM fi_invoice_ar WHERE invoice_ar_id = vCursorForInvoiceAr.invoice_ar_id;
930
931 -- Generate data awe_historydoc
932 INSERT INTO awe_historydoc(
933 tenant_id, req_id, doc_id, scheme, user_id, role_id,
934 activity, previous_state, next_state, remark, next_user_id, next_role_id,
935 flg_user_role, activity_datetime, version)
936 SELECT pTenantId, vReqId, invoice_ar_id, vInvoiceArDocScheme, -99, vRoleId,
937 'SUBMIT', vStateDraft, vStateApproved, '', -99, -99,
938 vFlagRole, vDatetime, 0
939 FROM fi_invoice_ar WHERE invoice_ar_id = vCursorForInvoiceAr.invoice_ar_id;
940 END LOOP;
941
942 /*
943 * membuat data transaksi jurnal :
944 * 1. buat admin
945 * 2. buat temlate jurnal
946 */
947
948 PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'MONTHLY', vDatetime, vUserId)
949 FROM sl_do_mlm A
950 WHERE A.do_mlm_id = vDoMlmId;
951
952 SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
953
954 INSERT INTO gl_journal_trx
955 (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
956 ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
957 ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
958 "version", create_datetime, create_user_id, update_datetime, update_user_id, ref_doc_no, ref_doc_date)
959 SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.do_mlm_id, A.doc_no, A.doc_date,
960 (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_ship_to_id, vEmptyValueId, vEmptyValueId, A.ext_doc_no, A.ext_doc_date,
961 A.ref_doc_type_id, A.ref_id, A.doc_date, B.curr_code, A.remark, vStatusDraft, 'DRAFT',
962 0, vDatetime, vUserId, vDatetime, vUserId, B.doc_no, B.doc_date
963 FROM sl_do_mlm A, sl_so_mlm B
964 WHERE A.do_mlm_id = vDoMlmId AND
965 A.ref_doc_type_id = B.doc_type_id AND
966 A.ref_id = B.so_mlm_id;
967
968 -- For Product non bundling
969 INSERT INTO tt_journal_trx_item
970 (session_id, tenant_id, journal_trx_id, line_no,
971 ref_doc_type_id, ref_id,
972 partner_id, product_id, cashbank_id, ou_rc_id,
973 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
974 coa_id, curr_code, qty, uom_id,
975 amount, journal_date, type_rate,
976 numerator_rate, denominator_rate, journal_desc, remark)
977 SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
978 A.ref_doc_type_id, A.ref_id,
979 A.partner_ship_to_id, B.product_id, vEmptyValueId, vEmptyValueId,
980 vEmptyValueId, vSignCredit, vProductCOA, vEmptyValueId,
981 f_get_product_coa_group_product(A.tenant_id, B.product_id), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty_dlv_int, B.base_uom_id,
982 0, A.doc_date, vTypeRate,
983 1, 1, 'PRODUCT_STOCK', vEmptyValue
984 FROM sl_do_mlm A, sl_do_mlm_item B
985 WHERE A.do_mlm_id = vDoMlmId AND
986 A.do_mlm_id = B.do_mlm_id AND
987 B.qty_dlv_int > 0;
988
989 INSERT INTO gl_journal_trx_item
990 (tenant_id, journal_trx_id, line_no,
991 ref_doc_type_id, ref_id,
992 partner_id, product_id, cashbank_id, ou_rc_id,
993 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
994 coa_id, curr_code, qty, uom_id,
995 amount, journal_date, type_rate,
996 numerator_rate, denominator_rate, journal_desc, remark,
997 "version", create_datetime, create_user_id, update_datetime, update_user_id)
998 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
999 A.ref_doc_type_id, A.ref_id,
1000 A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
1001 A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
1002 A.coa_id, A.curr_code, A.qty, A.uom_id,
1003 A.amount, A.journal_date, A.type_rate,
1004 A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
1005 0, vDatetime, vUserId, vDatetime, vUserId
1006 FROM tt_journal_trx_item A
1007 WHERE A.session_id = pSessionId;
1008
1009 INSERT INTO gl_journal_trx_mapping
1010 (tenant_id, journal_trx_id, line_no,
1011 ref_doc_type_id, ref_id,
1012 partner_id, product_id, cashbank_id, ou_rc_id,
1013 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
1014 coa_id, curr_code, qty, uom_id,
1015 amount, journal_date, type_rate,
1016 numerator_rate, denominator_rate, journal_desc, remark,
1017 "version", create_datetime, create_user_id, update_datetime, update_user_id)
1018 SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
1019 vEmptyValueId, vEmptyValueId,
1020 vEmptyValueId, vEmptyValueId, vEmptyValueId, vEmptyValueId,
1021 vEmptyValueId, vSignDebit, vSystemCOA, vEmptyValueId,
1022 f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), 0, vEmptyValueId,
1023 0, A.journal_date, A.type_rate,
1024 1, 1, 'COGS', vEmptyValue,
1025 0, vDatetime, vUserId, vDatetime, vUserId
1026 FROM tt_journal_trx_item A
1027 WHERE A.session_id = pSessionId
1028 GROUP BY A.tenant_id, A.journal_trx_id, A.journal_date, A.type_rate;
1029
1030 DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
1031
1032 -- [update 4 februari 2016]
1033 --Saat Buat DO (After SCAN)
1034 --sl_submit_do_mlm
1035 --Tambahkan Update
1036 --last_scan_picking_user character varying (100),
1037 --last_scan_picking_time character varying (14),
1038 UPDATE sl_so_approved_status A
1039 SET last_scan_picking_user=f_get_username(vUserId), last_scan_picking_time=vDatetime,
1040 version=A.version+1, update_datetime=vDatetime, update_user_id=vUserId
1041 FROM sl_do_mlm B
1042 WHERE A.so_mlm_id=B.ref_id AND B.do_mlm_id=vDoMlmId;
1043
1044END;
1045$BODY$
1046 LANGUAGE plpgsql VOLATILE
1047 COST 100;
1048 /