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