· 9 years ago · Dec 14, 2016, 06:28 AM
1-- Function: sl_so_mlm_add_item_from_cart_use_voucher(bigint, character varying, bigint, character varying, bigint)
2
3-- DROP FUNCTION sl_so_mlm_add_item_from_cart_use_voucher(bigint, character varying, bigint, character varying, bigint);
4
5CREATE OR REPLACE FUNCTION sl_so_mlm_add_item_from_cart_use_voucher(bigint, character varying, bigint, character varying, bigint)
6 RETURNS SETOF refcursor AS
7$BODY$
8 DECLARE
9 pTenantId alias for $1;
10 pDateTime alias for $2;
11 pUserId alias for $3;
12 pMemberCode alias for $4;
13 pSlCartId alias for $5;
14
15 vYearMonth character varying(6);
16 vOuId bigint;
17 vStatusDraft character varying(1);
18 vStatusRelease character varying(1);
19 vSoMlmId bigint;
20 vSoMlmItemId bigint;
21 vSoMlmDocTypeId bigint;
22 vSoMlmDocNo character varying(30);
23 vSoMlmDocScheme character varying(10);
24 vStateDraft character varying(10);
25 vProductId bigint;
26 vQtySo bigint;
27 vQtyBackOrder bigint;
28 vQtyAfterRsv bigint;
29 vQtyForReserved bigint;
30
31 vPartnerId bigint;
32 vPartnerShipToId bigint;
33 vPartnerShipAddressId bigint;
34 vPartnerCpId bigint;
35 vPartnerRelBillingId bigint;
36 vPartnerCpName character varying(100);
37 vPhone1 character varying(100);
38 vMobilePhone1 character varying(50);
39
40 vRoundingMode character varying;
41 vDecimalRounding integer;
42 vTaxId bigint;
43 vTaxPercentage numeric;
44 vTaxPrice numeric;
45 vGrossPrice numeric;
46 vDiscMemberPercent numeric;
47 vDiscMemberAmount numeric;
48 vDiscPromoPercent numeric;
49 vDiscPromoAmount numeric;
50 vMaxLineNo numeric;
51
52 vSubTotalPriceAfterDisc numeric;
53 vDsCommission numeric(5,2);
54
55 vDefaultRoleId bigint;
56 vDefaultUomId bigint;
57 vFlowSoMlmId bigint;
58 vFlagYes character varying(1);
59 vFlagNo character varying(1);
60 vSpaceValue character varying(6);
61 vFlagRole character varying(1);
62 vReqId character varying(50);
63 vUserWebDS bigint;
64 vDocTypeCartId bigint;
65 vUserSms bigint;
66 vUserMobile bigint;
67
68 vDocNo1 character varying(10);
69 vDocNo2 character varying(10);
70 vDocNoSo character varying(10);
71 vDocNoPo character varying(10);
72 vSessionId character varying(50);
73
74 pRefHeader REFCURSOR := 'refHeader';
75
76 vTtTempCartSo tt_temp_cart_so%ROWTYPE;
77 vBulk bigint;
78 vReceived bigint;
79 vDisplay bigint;
80
81 vQtyPakaiTemp numeric;
82 vQtyBalanceTemp numeric;
83 vQtyReservedTemp numeric;
84
85 vCurrentStatusDoc character varying(1);
86 vCartPartnerId bigint;
87 BEGIN
88 vUserWebDS := -3;
89 vUserSms := -2;
90 vUserMobile := -4;
91
92 vDecimalRounding := 0;
93 vSubTotalPriceAfterDisc := 0;
94 vSoMlmDocTypeId := 306;
95 vSoMlmDocScheme := 'FA06';
96 vStatusDraft := 'D';
97 vStatusRelease := 'R';
98 vStateDraft := 'DRAFT';
99 vFlagRole := 'R';
100 vRoundingMode := 'RD';
101 vFlagYes := 'Y';
102 vFlagNo := 'N';
103 vSpaceValue := ' ';
104 vYearMonth := ' ';
105 vDocTypeCartId := 396;
106 vBulk := 13;
107 vReceived := 14;
108 vDisplay := 12;
109 vMaxLineNo := 0;
110
111 -- Validate sl_cart cannot status_doc = 'R'
112 -- Validate sl_cart member code is exact partner_id
113 SELECT status_doc INTO vCurrentStatusDoc FROM sl_cart WHERE sl_cart_id = pSlCartId;
114 SELECT partner_id INTO vCartPartnerId FROM sl_cart WHERE sl_cart_id = pSlCartId;
115
116 -- cari partner_id
117 SELECT B.partner_id INTO vPartnerId FROM mlm_member A JOIN mlm_ds B ON A.member_id = B.member_id WHERE A.member_code = pMemberCode;
118
119 IF vCurrentStatusDoc <> 'I' THEN
120 RAISE EXCEPTION 'Cart is not being checked out';
121 END IF;
122 IF vCartPartnerId <> vPartnerId THEN
123 RAISE EXCEPTION 'Cart partner ID is not match';
124 END IF;
125
126 --Session Id
127 SELECT pSlCartId::character varying||'_'||pDateTime INTO vSessionId;
128
129 --Ou Id
130 SELECT f_get_value_system_config_by_param_code(pTenantId, 'DEFAULT.OU.ID') INTO vOuId;
131
132 --tax Id
133 SELECT f_get_default_tax_id(pTenantId) INTO vTaxId;
134
135 -- default UOM
136 SELECT f_get_default_uom_id(pTenantId) INTO vDefaultUomId;
137
138
139 -- default Partner Ship Address Id
140 SELECT partner_address_id INTO vPartnerShipAddressId
141 FROM m_partner_address WHERE partner_id = vPartnerId AND flg_ship = vFlagYes ORDER BY partner_address_id LIMIT 1;
142
143
144 -- Untuk di isi ke sl_so_mlm
145 SELECT partner_cp_id INTO vPartnerCpId FROM m_partner_cp WHERE partner_id = vPartnerId ORDER BY partner_cp_id LIMIT 1;
146 SELECT relation_id INTO vPartnerRelBillingId
147 FROM m_partner_rel WHERE partner_id = vPartnerId AND flg_bill = vFlagYes ORDER BY partner_rel_id LIMIT 1;
148 SELECT cp_name INTO vPartnerCpName FROM m_partner_cp WHERE partner_id = vPartnerId ORDER BY partner_cp_id LIMIT 1;
149 SELECT phone1 INTO vPhone1 FROM m_partner_cp WHERE partner_id = vPartnerId ORDER BY partner_cp_id LIMIT 1;
150 SELECT mobile_phone1 INTO vMobilePhone1 FROM m_partner_cp WHERE partner_id = vPartnerId ORDER BY partner_cp_id LIMIT 1;
151 SELECT f_get_value_system_config_by_param_code(pTenantId, 'dsCommision')::numeric INTO vDsCommission;
152
153 -- cari partner ship to id
154 SELECT relation_id INTO vPartnerShipToId
155 FROM m_partner_rel WHERE partner_id = vPartnerId AND flg_shipp = vFlagYes ORDER BY partner_rel_id LIMIT 1;
156
157
158 -- calculate untuk qty_reserved
159 INSERT INTO tt_temp_cart_so(
160 session_id,sl_cart_item_id,product_catalog_id,product_id,product_catalog_code,
161 qty_cart,qty_backorder,flg_out_of_stock)
162 SELECT vSessionId,A.sl_cart_item_id,A.product_catalog_id,B.product_id,B.product_catalog_code,
163 A.qty,0,vFlagYes
164 FROM sl_cart_item A
165 JOIN m_product_catalog B ON A.product_catalog_id = B.product_catalog_id
166 WHERE A.sl_cart_id = pSlCartId;
167
168
169 INSERT INTO tt_temp_calculate_cart_so(
170 session_id,product_id,qty_balance,qty_reserved,qty_pakai)
171 SELECT A.session_id,A.product_id,0,0,0
172 FROM tt_temp_cart_so A
173 WHERE A.session_id = vSessionId
174 GROUP BY A.product_id,A.session_id;
175
176
177 -- UPDATE qty_balance
178 WITH stock_in_warehouse AS(
179 SELECT A.product_id,SUM(A.qty) as Qty
180 FROM in_product_balance_stock A
181 JOIN tt_temp_calculate_cart_so B ON A.product_id =B.product_id
182 AND A.warehouse_id IN (vBulk,vReceived,vDisplay)
183 AND B.session_id = vSessionId
184 GROUP BY A.product_id
185 )
186 UPDATE tt_temp_calculate_cart_so Z
187 SET qty_balance = A.qty
188 FROM stock_in_warehouse A
189 WHERE A.product_id = Z.product_id
190 AND Z.session_id = vSessionId;
191
192
193 -- UPDATE qty_reserved
194 UPDATE tt_temp_calculate_cart_so Z
195 SET qty_reserved = A.qty
196 FROM in_product_balance_stock_reserved A
197 WHERE A.product_id = Z.product_id
198 AND A.tenant_id = pTenantId
199 AND A.ou_id = vOuId
200 AND Z.session_id = vSessionId;
201
202 FOR vTtTempCartSo IN
203 SELECT *
204 FROM tt_temp_cart_so
205 WHERE session_id = vSessionId
206 ORDER BY sl_cart_item_id
207 LOOP
208
209 SELECT A.qty_pakai,A.qty_balance,A.qty_reserved INTO vQtyPakaiTemp,vQtyBalanceTemp,vQtyReservedTemp
210 FROM tt_temp_calculate_cart_so A
211 WHERE A.product_id = vTtTempCartSo.product_id
212 AND A.session_id = vSessionId;
213
214 IF vQtyBalanceTemp-vQtyReservedTemp-vQtyPakaiTemp > 0 THEN
215
216 --update untuk product yang tidak flg_out_of_stock
217 UPDATE tt_temp_cart_so Z
218 SET flg_out_of_stock = vFlagNo
219 WHERE Z.sl_cart_item_id = vTtTempCartSo.sl_cart_item_id
220 AND Z.session_id = vSessionId;
221
222 IF vQtyBalanceTemp-vQtyReservedTemp-vQtyPakaiTemp - vTtTempCartSo.qty_cart > 0 THEN
223 --product_id qty_cart
224 UPDATE tt_temp_calculate_cart_so Z
225 SET qty_pakai = vTtTempCartSo.qty_cart
226 WHERE Z.product_id = vTtTempCartSo.product_id
227 AND Z.session_id = vSessionId;
228
229 ELSE
230 --product_id qty_cart
231 UPDATE tt_temp_calculate_cart_so Z
232 SET qty_pakai = COALESCE(vQtyBalanceTemp-vQtyReservedTemp-vQtyPakaiTemp,0)
233 WHERE Z.product_id = vTtTempCartSo.product_id
234 AND Z.session_id = vSessionId;
235
236 vQtyBackOrder := vTtTempCartSo.qty_cart - (vQtyBalanceTemp-vQtyReservedTemp) - vQtyPakaiTemp;
237
238 END IF;
239
240 ELSE
241
242 vQtyBackOrder := vTtTempCartSo.qty_cart;
243
244 END IF;
245
246
247 UPDATE tt_temp_cart_so Z
248 SET qty_backorder = COALESCE(vQtyBackOrder,0)
249 WHERE Z.sl_cart_item_id = vTtTempCartSo.sl_cart_item_id
250 AND Z.product_id = vTtTempCartSo.product_id
251 AND Z.session_id = vSessionId;
252
253 vQtyBackOrder := 0;
254
255 END LOOP;
256
257
258
259 IF(
260 SELECT COUNT(1) > 0
261 FROM tt_temp_cart_so
262 WHERE session_id =vSessionId
263 AND flg_out_of_stock = vFlagNo
264 )THEN
265 -- Check apakah ada SO MLM yang masih DRAFT untuk vPartnerId bersangkutan dan flg_special = 'N' (Bukan BKT / Catalog)
266 IF EXISTS( SELECT 1 FROM sl_so_mlm WHERE status_doc = vStatusDraft AND partner_id = vPartnerId
267 AND doc_type_id = vSoMlmDocTypeId AND flg_special = vFlagNo
268 AND create_user_id IN (vUserWebDS, vUserSms, vUserMobile)
269 LIMIT 1)
270 THEN
271 -- ada yang draft, ambil SO MLM id nya
272 SELECT so_mlm_id INTO vSoMlmId FROM sl_so_mlm
273 WHERE status_doc = vStatusDraft AND partner_id = vPartnerId
274 AND doc_type_id = vSoMlmDocTypeId AND flg_special = vFlagNo
275 AND create_user_id IN (vUserWebDS, vUserSms, vUserMobile)
276 ORDER BY so_mlm_id DESC
277 LIMIT 1;
278
279 -- UPDATE sl_so_mlm SET update_user_id, update_datetime, version
280 UPDATE sl_so_mlm SET update_user_id = pUserId, update_datetime = pDateTime, version = version + 1 WHERE so_mlm_id = vSoMlmId;
281
282 ELSE
283 vSoMlmId := nextval('sl_so_mlm_seq');
284
285 SELECT f_get_value_system_config_by_param_code(11, 'DOC.NO.SO.PALOMAWEB') INTO vDocNoSo;
286 SELECT f_get_value_system_config_by_param_code(11, 'DOC.NO.PO.PALOMAWEB') INTO vDocNoPo;
287 SELECT substring(current_date::text from 3 for 2)||substring(current_date::text from 6 for 2)||'/' INTO vDocNo1;
288 SELECT lpad(nextval('sl_so_mlm_from_web_seq')::text, 6, '0') INTO vDocNo2;
289
290 -- Tidak ada yang draft, buat header
291 INSERT INTO sl_so_mlm(
292 so_mlm_id, tenant_id, doc_type_id,
293 doc_no,
294 doc_date, ou_id,
295 ext_doc_no,
296 ext_doc_date, ref_doc_type_id, ref_id, remark, partner_id,
297 partner_cp_id, partner_bill_to_id, partner_ship_to_id, partner_ship_address_id,
298 due_date, salesman_id, flg_delivery, curr_code, add_discount_percentage,
299 add_discount_amount, status_doc, workflow_status, version, create_datetime,
300 create_user_id, update_datetime, update_user_id, partner_ship_cp_id,
301 partner_ship_cp_name, partner_ship_cp_phone1, member_mobile_phone,
302 flg_show_inv_tax, commission_percentage, commission_amount, flg_special)
303 SELECT
304 vSoMlmId, pTenantId, vSoMlmDocTypeId,
305 vDocNoSo||vDocNo1||vDocNo2 AS doc_no, -- 'SOMLM/'|| A.partner_code || '/' || vSoMlmId,
306 substring(pDateTime,1,8) as doc_date, vOuId,
307 vDocNoPo||vDocNo1||vDocNo2 AS ext_doc_no, -- 'PO/'|| A.partner_code || '/' || vSoMlmId
308 substring(pDateTime,1,8) AS ext_doc_date,
309 -99, -99, '', vPartnerId,
310 vPartnerCpId, vPartnerRelBillingId, vPartnerShipToId, vPartnerShipAddressId,
311 substring(pDateTime,1,8) AS due_date, -99, vFlagYes, 'IDR', 0,
312 0, vStatusDraft, vStateDraft, 0, pDateTime,
313 pUserId, pDateTime, pUserId, vPartnerCpId,
314 vPartnerCpName, vPhone1, vMobilePhone1,
315 vFlagYes, vDsCommission, 0, vFlagNo
316 FROM m_partner A
317 WHERE A.partner_id = vPartnerId;
318
319 --[update 6 februari] init add so approved status
320 INSERT INTO sl_so_approved_status (so_mlm_id, last_ss_approve_user, last_ss_approve_time, last_fin_approve_user,
321 last_fin_approve_time, last_auto_approve_user, last_auto_approve_time,
322 last_print_picking_user, last_print_picking_time, last_scan_picking_user,
323 last_scan_picking_time, version, create_datetime, update_datetime,
324 create_user_id, update_user_id)
325 SELECT vSoMlmId, '', '', '' ,
326 '', '', '',
327 '', '', '',
328 '', 0, pDateTime, pDateTime,
329 pUserId, pUserId;
330
331 /**
332 Bagian ini untuk melengkapi data awe
333 Harus dipastikan sudah:
334 1. di setting system config ROLE.ASM.ID -> diisi dengan Role ID Area Sales Manager
335 2. sudah set approval flow untuk SO MLM FA06, dan hanya 1
336 **/
337
338 -- Mendapatkan default role yang dipakai dari sysconfig
339 SELECT f_get_value_system_config_by_param_code(11, 'ROLE.ASM.ID') INTO vDefaultRoleId;
340
341 -- Mendapatkan default approval flow ID yang dipakai dari sysconfig
342 SELECT awe_flow_id INTO vFlowSoMlmId
343 FROM awe_flow WHERE scheme = vSoMlmDocScheme AND flg_validate= vFlagYes LIMIT 1;
344
345 -- Generate unique Request ID
346 SELECT f_make_uid() INTO vReqId;
347
348 -- Generate data awe_currdoc_status
349 INSERT INTO awe_currdoc_status(
350 req_id, tenant_id, scheme, doc_id, doc_no, doc_date, current_state,
351 remark, current_user_id, current_role_id, flg_user_role, label,
352 data, flow_id, create_datetime, create_user_id, create_role_id,
353 update_datetime, update_user_id, update_role_id, version)
354 SELECT vReqId, pTenantId, vSoMlmDocScheme, so_mlm_id, doc_no, doc_date, vStateDraft,
355 ' ', -99, vDefaultRoleId, vFlagRole, 'SALES ORDER MLM'||doc_no,
356 '{}', vFlowSoMlmId, pDateTime, pUserId, vDefaultRoleId, pDateTime,pUserId, vDefaultRoleId, 0
357 FROM sl_so_mlm WHERE so_mlm_id = vSoMlmId;
358
359 -- Generate data awe_worklist
360 INSERT INTO awe_worklist(
361 req_id, user_id, role_id, no_item, tenant_id, scheme, doc_id,
362 doc_no, doc_date, status, remark, flg_user_role, flg_to_do, create_datetime,
363 create_user_id, update_datetime, update_user_id, version)
364 SELECT vReqId, -99, vDefaultRoleId, 1, pTenantId, vSoMlmDocScheme, so_mlm_id,
365 doc_no, doc_date, vStateDraft, ' ', vFlagRole, vFlagYes, pDateTime,
366 pUserId, pDateTime, pUserId, 0
367 FROM sl_so_mlm WHERE so_mlm_id = vSoMlmId;
368
369 -- Generate data awe_historydoc
370 INSERT INTO awe_historydoc(
371 tenant_id, req_id, doc_id, scheme, user_id, role_id,
372 activity, previous_state, next_state, remark, next_user_id, next_role_id,
373 flg_user_role, activity_datetime, version)
374 SELECT pTenantId, vReqId, so_mlm_id, vSoMlmDocScheme, -99, vDefaultRoleId,
375 'CREATE', '', vStateDraft, '', -99, -99,
376 'U', pDateTime, 0
377 FROM sl_so_mlm WHERE so_mlm_id = vSoMlmId;
378
379
380 END IF;
381
382 -- update / insert ke reserved yg
383 INSERT INTO in_product_balance_stock_reserved(
384 tenant_id,ou_id,product_id,base_uom_id,product_status,qty,version,
385 create_datetime,create_user_id,update_datetime,update_user_id
386 )
387 SELECT pTenantId,vOuId,A.product_id,vDefaultUomId,'GOOD',0,0,
388 pDateTime,pUserId,pDateTime,pUserId
389 FROM tt_temp_calculate_cart_so A
390 WHERE A.session_id =vSessionId
391 AND NOT EXISTS(
392 SELECT 1
393 FROM in_product_balance_stock_reserved Z
394 WHERE A.product_id = Z.product_id
395 );
396
397 UPDATE in_product_balance_stock_reserved Z
398 SET qty = Z.qty + A.qty_pakai
399 FROM tt_temp_calculate_cart_so A
400 WHERE A.session_id = vSessionId
401 AND Z.product_id = A.product_id
402 AND A.qty_pakai > 0
403 AND EXISTS(
404 SELECT 1
405 FROM in_product_balance_stock_reserved Z
406 WHERE A.product_id = Z.product_id
407 );
408
409 -- insert ke table temp tt_cart_to_so
410 INSERT INTO tt_cart_to_so(
411 session_id,so_mlm_item_id,tenant_id,so_mlm_id,cart_item_id,cart_id,discount_member_percentage,discount_member_amount,
412 discount_promo_percentage,discount_promo_amount,qty,gross_sell_price,
413 price_after_disc,product_catalog_id,product_id,tax_percentage,
414 tax_price,line_no)
415 SELECT vSessionId,nextval('sl_so_mlm_item_seq'),pTenantId,vSoMlmId,B.sl_cart_item_id,pSlCartId,B.discount_member,B.discount_member_amount,
416 B.discount_promo,B.discount_promo_amount,(C.qty_cart - C.qty_backorder),B.catalog_price,
417 B.price_after_discount,B.product_catalog_id,f_get_product_by_product_catalog_id(B.product_catalog_id),
418 f_get_product_tax_value (f_get_product_by_product_catalog_id(B.product_catalog_id)),
419 f_calc_tax_price_from_gross_price(B.catalog_price,B.discount_member, B.discount_promo, vDecimalRounding, vRoundingMode, vFlagYes, vTaxId, f_get_product_tax_value (f_get_product_by_product_catalog_id(B.product_catalog_id))),
420 ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY session_id)
421 FROM sl_cart A
422 JOIN sl_cart_item B ON B.sl_cart_id = A.sl_cart_id
423 JOIN tt_temp_cart_so C ON B.sl_cart_item_id = C.sl_cart_item_id
424 WHERE A.sl_cart_id = pSlCartId
425 AND (C.qty_cart - C.qty_backorder) > 0
426 AND C.session_id = vSessionId;
427
428 -- Get Max Line Number
429 SELECT COALESCE(MAX(line_no),0) into vMaxLineNo
430 FROM sl_so_mlm_item WHERE so_mlm_id = vSoMlmId;
431
432 -- insert ke sl_so_mlm_item
433 INSERT INTO sl_so_mlm_item(
434 so_mlm_item_id,
435 tenant_id,
436 so_mlm_id,
437 line_no,
438 ref_doc_type_id,
439 ref_id,
440 partner_ship_to_id,
441 partner_ship_address_id,
442 product_catalog_id,
443 qty_so,
444 so_uom_id,
445 qty_int,
446 base_uom_id,
447 curr_code,
448 gross_sell_price,
449 flg_tax_amount,
450 tax_id,
451 tax_percentage,
452 tax_price,
453 promo_code,
454 discount_member_percentage, discount_member_amount,
455 discount_promo_percentage, discount_promo_amount,
456 price_after_disc,
457 nett_sell_price, tax_amount, nett_item_amount,
458 tolerance_dlv_qty, remark, version, create_datetime, create_user_id,
459 update_datetime, update_user_id)
460 SELECT A.so_mlm_item_id,
461 A.tenant_id,
462 A.so_mlm_id,
463 vMaxLineNo+A.line_no,
464 -99,
465 -99,
466 vPartnerShipToId, -- partner ship to id
467 vPartnerShipAddressId, -- partner ship address id
468 A.product_catalog_id,
469 A.qty,
470 vDefaultUomId,
471 A.qty,
472 vDefaultUomId,
473 'IDR',
474 A.gross_sell_price, -- gross_sell_price (harus sesuai dengan harga katalok)
475 vFlagYes,
476 vTaxId, -- nilainya berdasarkan taxCode PPn (tax id)
477 A.tax_percentage, -- tax percentage
478 A.tax_price, -- tax price
479 '', -- promo_code
480 A.discount_member_percentage,A.discount_member_amount,
481 A.discount_promo_percentage,A.discount_promo_amount,
482 A.price_after_disc, -- price_after_disc
483 0, --nett_sell_price
484 0, -- tax_amount,
485 0, -- nett_item_amount,
486 0, -- tolerance_dlv_qty
487 '', -- remark
488 0, pDateTime, pUserId, pDateTime, pUserId
489 FROM tt_cart_to_so A
490 WHERE A.session_id = vSessionId;
491
492 -- Update nilai commission_amount di so_mlm
493 UPDATE sl_so_mlm SET commission_amount = f_get_commission_amount_by_so_mlm_id(pTenantId, vSoMlmId, vDecimalRounding, vRoundingMode)
494 WHERE so_mlm_id = vSoMlmId;
495
496 -- Update nilai nett sell price
497 UPDATE sl_so_mlm_item Z
498 SET nett_sell_price = Z.price_after_disc - Z.tax_price
499 FROM tt_cart_to_so A
500 WHERE A.so_mlm_id = vSoMlmId
501 AND A.so_mlm_id = Z.so_mlm_id
502 AND A.so_mlm_item_id = Z.so_mlm_item_id
503 AND A.cart_id =pSlCartId
504 AND A.session_id = vSessionId;
505
506 -- Update nilai tax_amount and nett_item_amount (dikalikan dengan qty)
507 UPDATE sl_so_mlm_item Z
508 SET tax_amount = Z.qty_so * Z.tax_price,
509 nett_item_amount =Z.qty_so * Z.nett_sell_price
510 FROM tt_cart_to_so A
511 WHERE A.so_mlm_id = vSoMlmId
512 AND A.so_mlm_id = Z.so_mlm_id
513 AND A.so_mlm_item_id = Z.so_mlm_item_id
514 AND A.cart_id =pSlCartId
515 AND A.session_id = vSessionId;
516
517 -- update sl_so_mlm_item_voucher terlebih dahulu dari pada insert sl_so_mlm_item_voucher
518 -- karena apabila condition update tidak di temukan, update akan langsung di skip
519 UPDATE sl_so_mlm_item_voucher B
520 SET qty = qty + A.promo_qty,
521 update_datetime = pDateTime,
522 update_user_id = pUserId,
523 version = B.version + 1
524 FROM sl_cart_item_voucher A
525 WHERE A.sl_cart_id = pSlCartId AND
526 B.so_mlm_id = vSoMlmId AND
527 B.promo_id = A.promo_id;
528
529 -- insert into sl_so_mlm_item_voucher
530 INSERT INTO sl_so_mlm_item_voucher(so_mlm_id,promo_id,qty,create_datetime,create_user_id,update_datetime,update_user_id,version)
531 SELECT vSoMlmId,A.promo_id,A.promo_qty,pDateTime,pUserId,pDateTime,pUserId,0
532 FROM sl_cart_item_voucher A
533 WHERE A.sl_cart_id = pSlCartId AND NOT EXISTS (
534 SELECT 1 FROM sl_so_mlm_item_voucher B WHERE B.so_mlm_id = vSoMlmId AND B.promo_id = A.promo_id
535 );
536
537 UPDATE sl_partner_promo_balance Z
538 SET qty = Z.qty - A.promo_qty,
539 update_user_id = pUserId,
540 update_datetime = pDateTime,
541 version = Z.version + 1
542 FROM sl_cart_item_voucher A,sl_cart B
543 WHERE A.sl_cart_id = B.sl_cart_id
544 AND B.partner_id = Z.partner_id
545 AND Z.promo_id = A.promo_id
546 AND A.sl_cart_id = pSlCartId;
547
548 INSERT INTO sl_log_partner_promo_balance (
549 tenant_id,partner_id,promo_id,qty_usage_so,
550 create_datetime,create_user_id, update_datetime,update_user_id,
551 version,qty_cancel,qty_usage_do,ref_doc_type_id,ref_doc_no,ref_doc_date,
552 remark,ref_id)
553 SELECT A.tenant_id,A.partner_id,B.promo_id,B.promo_qty,
554 pDateTime,pUserId,pDateTime,pUserId,
555 0,0,0,396,A.doc_no,A.doc_date,'CHECK OUT',A.sl_cart_id
556 FROM sl_cart A
557 JOIN sl_cart_item_voucher B ON A.sl_cart_id = B.sl_cart_id
558 AND B.sl_cart_id = pSlCartId;
559
560 END IF;
561
562 -- insert ke backorder
563 INSERT INTO sl_so_backorder(
564 tenant_id, ou_id, partner_id, product_id,
565 ref_doc_type_id, ref_doc_no, ref_doc_date, base_uom_id, flg_followup,
566 qty_backorder, qty_order, version, create_datetime, create_user_id,
567 update_datetime, update_user_id, product_catalog_id, ref_item_id)
568 SELECT pTenantId,vOuId,vPartnerId,A.product_id,
569 vDocTypeCartId, C.doc_no,C.doc_date,vDefaultUomId,vFlagNo,
570 A.qty_backorder,B.qty,0,pDateTime,pUserId,
571 pDateTime,pUserId,B.product_catalog_id,B.sl_cart_item_id
572 FROM tt_temp_cart_so A
573 JOIN sl_cart_item B ON A.sl_cart_item_id = B.sl_cart_item_id
574 JOIN sl_cart C ON C.sl_cart_id = B.sl_cart_id
575 WHERE A.session_id = vSessionId
576 AND C.sl_cart_id = pSlCartId
577 AND A.qty_backorder>0;
578
579
580 -- Update sl_cart
581 UPDATE sl_cart
582 SET status_doc = vStatusRelease,
583 update_datetime = pDateTime
584 WHERE sl_cart_id = pSlCartId;
585
586 -- Update sl_cart_item
587 UPDATE sl_cart_item Z
588 SET so_mlm_id = vSoMlmId,
589 so_mlm_item_id = A.so_mlm_item_id
590 FROM tt_cart_to_so A
591 WHERE Z.sl_cart_id = A.cart_id
592 AND Z.sl_cart_item_id = A.cart_item_id
593 AND A.session_id =vSessionId
594 AND A.cart_id = pSlCartId;
595 -- RAISE NOTICE 'balikan data';
596 Open pRefHeader FOR
597 SELECT sl_cart_item_id,product_catalog_id,product_id,product_catalog_code,qty_cart,qty_backorder,flg_out_of_stock
598 FROM tt_temp_cart_so
599 WHERE session_id =vSessionId;
600
601 RETURN NEXT pRefHeader;
602
603
604 END;
605 $BODY$
606 LANGUAGE plpgsql VOLATILE
607 COST 100
608 ROWS 1000;
609/