· 9 years ago · Jan 10, 2017, 07:00 AM
1CREATE OR REPLACE FUNCTION f_intgr_t29e(bigint, bigint, bigint, bigint, bigint, bigint, character varying, character varying,
2character varying, bigint, character varying, bigint, character varying, character varying, bigint, character varying, character varying,
3bigint, bigint, numeric, numeric, numeric, numeric, numeric, numeric, numeric, numeric, numeric, numeric, numeric, numeric,
4character varying, character varying)
5
6 RETURNS text AS
7$BODY$
8
9DECLARE
10 pSoMlmBalanceProductPerMemberId ALIAS FOR $1;
11 pTenantid ALIAS FOR $2;
12 pOuId ALIAS FOR $3;
13 pSoMlmId ALIAS FOR $4;
14 pMemberId ALIAS FOR $5;
15 pCartId ALIAS FOR $6;
16 pCartNo ALIAS FOR $7;
17 pCartDate ALIAS FOR $8;
18 pCartType ALIAS FOR $9;
19 pStockPointId ALIAS FOR $10;
20 pCartCheckoutDatetime ALIAS FOR $11;
21 pProductId ALIAS FOR $12;
22 pProductCode ALIAS FOR $13;
23 pProductName ALIAS FOR $14;
24 pProductCatalogId ALIAS FOR $15;
25 pProductCatalogCode ALIAS FOR $16;
26 pProductCatalogName ALIAS FOR $17;
27 pSoUomId ALIAS FOR $18;
28 pBaseUomId ALIAS FOR $19;
29 pQtySo ALIAS FOR $20;
30 pQtyDlv ALIAS FOR $21;
31 pQtySoInt ALIAS FOR $22;
32 pQtyDlvInt ALIAS FOR $23;
33 pTotalOrderedAmount ALIAS FOR $24;
34 pTotalOrderedHargaKatalog ALIAS FOR $25;
35 pTotalDeliveredAmount ALIAS FOR $26;
36 pTotalDeliveredHargaKatalog ALIAS FOR $27;
37 pTotalOrderedDiscountMemberAmount ALIAS FOR $28;
38 pTotalOrderedDiscountPromoAmount ALIAS FOR $29;
39 pTotalDeliveredDiscountMemberAmount ALIAS FOR $30;
40 pTotalDeliveredDiscountPromoAmount ALIAS FOR $31;
41 pDatetime ALIAS FOR $32;
42 pPartnerCode ALIAS FOR $33;
43
44 vDsId bigint;
45 vUserSystem bigint;
46 vProductStatus character varying(10);
47 vProductBalanceId bigint;
48
49BEGIN
50/*
51 * Berdasarkan data sl_so_mlm_balance_product_per_member_for_webds, yang didapat dari hasil kiriman dari TaskHub,
52 * didapati bahwa ada product yang diorder oleh DS dan member.
53 * yang diorder oleh DS, memberId = -99, akan menambah stock DS
54 * yang diorder oleh member, memberId <> -99, akan menambah stock member masing-masing.
55 */
56
57--ambil ds_id
58SELECT id INTO vDsId FROM mlm_member mm WHERE mm.member_code = pPartnerCode;
59
60vUserSystem := -1;
61vProductStatus := 'GOOD';
62vProductBalanceId := -99;
63
64INSERT INTO sl_so_mlm_balance_product_per_member_for_webds(
65 so_mlm_balance_product_per_member_for_webds_id, tenant_id, ou_id, so_mlm_id, member_id,
66 cart_id, cart_no, cart_date, cart_checkout_datetime, cart_type, stock_point_id,
67 product_id, product_code, product_name,
68 product_catalog_id, product_catalog_code, product_catalog_name,
69 so_uom_id, base_uom_id, qty_so, qty_dlv, qty_so_int, qty_dlv_int,
70 total_ordered_amount, total_ordered_harga_katalog, total_delivered_amount, total_delivered_harga_katalog,
71 total_ordered_discount_member_amount, total_ordered_discount_promo_amount,
72 total_delivered_discount_member_amount, total_delivered_discount_promo_amount,
73 version, create_datetime, create_user_id, update_datetime, update_user_id)
74 VALUES (pSoMlmBalanceProductPerMemberId, pTenantid, pOuId, pSoMlmId, pMemberId,
75 pCartId, pCartNo, pCartDate, pCartCheckoutDatetime, pCartType, pStockPointId,
76 pProductId, pProductCode, pProductName,
77 pProductCatalogId, pProductCatalogCode, pProductCatalogName,
78 pSoUomId, pBaseUomId, pQtySo, pQtyDlv, pQtySoInt, pQtyDlvInt,
79 pTotalOrderedAmount, pTotalOrderedHargaKatalog, pTotalDeliveredAmount, pTotalDeliveredHargaKatalog,
80 pTotalOrderedDiscountMemberAmount, pTotalOrderedDiscountPromoAmount,
81 pTotalDeliveredDiscountMemberAmount, pTotalDeliveredDiscountPromoAmount,
82 0, pDatetime, vUserSystem, pDatetime, vUserSystem);
83
84-- Jika MemberId yang dikirimkan = -99, maka insert ke table saldo DS
85IF pMemberId = -99 THEN
86 -- UPDATE stock DS.
87 UPDATE ds_product_balance
88 SET qty = qty + pQtyDlv, update_user_id = vUserSystem, update_datetime = pDatetime, version = version + 1
89 WHERE ds_id = vDsId
90 AND product_catalog_id = pProductCatalogId
91 AND product_status = vProductStatus
92 AND tenant_id = pTenantid;
93
94 -- INSERT Stock DS
95 INSERT INTO ds_product_balance(
96 ds_id, product_catalog_id, qty, create_user_id, update_user_id,
97 create_datetime, update_datetime, version, tenant_id, product_status)
98 SELECT vDsId, pProductCatalogId, pQtyDlv, vUserSystem, vUserSystem,
99 pDatetime, pDatetime, 0, pTenantId, vProductStatus
100 FROM sl_so_mlm_balance_product_per_member_for_webds A
101 WHERE NOT EXISTS (
102 SELECT 1 FROM ds_product_balance B WHERE ds_id = vDsId
103 AND B.product_catalog_id = A.product_catalog_id
104 AND B.product_status = vProductStatus
105 AND B.tenant_id = A.tenant_id)
106 AND A.so_mlm_balance_product_per_member_for_webds_id = pSoMlmBalanceProductPerMemberId;
107
108 -- INSERT ds_log_product_balance
109 INSERT INTO ds_log_product_balance(
110 tenant_id, ou_id, doc_type_id, ref_id,
111 doc_no, doc_date, partner_id, product_id, warehouse_id, product_balance_id,
112 product_status, base_uom_id, qty, create_user_id, update_user_id,
113 create_datetime, update_datetime, version, member_id, product_catalog_id, remark)
114 SELECT A.tenant_id, A.ou_id, B.doc_type_id, B.ref_id,
115 B.doc_no, B.doc_date, B.partner_ship_to_id, A.product_id, -99, vProductBalanceId,
116 C.product_status, A.base_uom_id, A.qty_dlv, vUserSystem, vUserSystem,
117 pDatetime, pDatetime, 0, A.member_id, A.product_catalog_id, ' '
118 FROM sl_so_mlm_balance_product_per_member_for_webds A
119 INNER JOIN sl_do_mlm_webds B ON A.so_mlm_id = B.ref_id
120 INNER JOIN sl_do_mlm_item_webds C ON B.do_mlm_id = C.do_mlm_id
121 WHERE A.so_mlm_balance_product_per_member_for_webds_id = pSoMlmBalanceProductPerMemberId
122 GROUP BY A.tenant_id, A.ou_id, B.doc_type_id, B.ref_id,
123 B.doc_no, B.doc_date, B.partner_ship_to_id, A.product_id,
124 C.product_status, A.base_uom_id, A.qty_dlv,
125 A.member_id, A.product_catalog_id;
126
127
128-- Jika MemberId yang dikirimkan <> -99, maka insert ke table saldo member
129ELSIF pMemberId <> -99 THEN
130 -- UPDATE stock Member
131 UPDATE member_product_balance
132 SET qty = qty + pQtyDlv, update_user_id = vUserSystem, update_datetime = pDatetime, version = version + 1
133 WHERE member_id = pMemberId
134 AND product_catalog_id = pProductCatalogId
135 AND product_status = vProductStatus
136 AND tenant_id = pTenantid;
137
138 -- INSERT stock member
139 INSERT INTO member_product_balance(
140 member_id, product_catalog_id, qty, create_user_id, update_user_id,
141 create_datetime, update_datetime, version, tenant_id, product_status)
142 SELECT pMemberId, pProductCatalogId, pQtyDlv, vUserSystem, vUserSystem,
143 pDatetime, pDatetime, 0, pTenantId, vProductStatus
144 FROM sl_so_mlm_balance_product_per_member_for_webds A
145 WHERE NOT EXISTS (
146 SELECT 1 FROM ds_product_balance B WHERE ds_id = pMemberId
147 AND B.product_catalog_id = A.product_catalog_id
148 AND B.product_status = vProductStatus
149 AND B.tenant_id = A.tenant_id)
150 AND A.so_mlm_balance_product_per_member_for_webds_id = pSoMlmBalanceProductPerMemberId;
151
152 -- INSERT member_log_product_balance
153 INSERT INTO member_log_product_balance(
154 tenant_id, ou_id, doc_type_id, ref_id,
155 doc_no, doc_date, partner_id, member_id, product_id, product_catalog_id,
156 warehouse_id, product_balance_id, product_status, base_uom_id, qty,
157 version, create_user_id, create_datetime, update_user_id, update_datetime)
158 SELECT A.tenant_id, A.ou_id, B.doc_type_id, B.ref_id,
159 B.doc_no, B.doc_date, B.partner_ship_to_id, pMemberId, A.product_id, A.product_catalog_id,
160 -99, vProductBalanceId, vProductStatus, A.base_uom_id, A.qty_dlv,
161 0, vUserSystem, pDatetime, vUserSystem, pDatetime
162 FROM sl_so_mlm_balance_product_per_member_for_webds A
163 INNER JOIN sl_do_mlm_webds B ON A.so_mlm_id = B.ref_id
164 WHERE A.so_mlm_balance_product_per_member_for_webds_id = pSoMlmBalanceProductPerMemberId
165 GROUP BY A.tenant_id, A.ou_id, B.doc_type_id, B.ref_id,
166 B.doc_no, B.doc_date, B.partner_ship_to_id, A.product_id,
167 A.product_catalog_id, A.base_uom_id, A.qty_dlv;
168
169END IF;
170
171RETURN 'OK';
172END;
173$BODY$
174 LANGUAGE plpgsql VOLATILE
175 COST 100;