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