· 4 years ago · Jul 21, 2021, 07:08 AM
1CREATE OR REPLACE FUNCTION r_master_product_sell_price_for_so(character varying, bigint, bigint, bigint, character varying, bigint, character varying, character varying, character varying, character varying, character varying, character varying, character varying)
2 RETURNS SETOF refcursor AS
3$BODY$
4DECLARE
5 pRefHeader REFCURSOR := 'refHeader';
6 pRefDetail REFCURSOR := 'refDetail';
7 pRefItemLevelPrice REFCURSOR := 'refItemLevelPrice';
8 pRefLevelPrice REFCURSOR := 'refLevelPrice';
9 pSessionId ALIAS FOR $1;
10 pTenantId ALIAS FOR $2;
11 pUserId ALIAS FOR $3;
12 pRoleId ALIAS FOR $4;
13 pDatetime ALIAS FOR $5;
14 pOuId ALIAS FOR $6;
15 pProductCodeName ALIAS FOR $7;
16 pDateFrom ALIAS FOR $8;
17 pGroupBrandName ALIAS FOR $9;
18 pParentGroupBrand ALIAS FOR $10;
19 pFlgGenuine ALIAS FOR $11;
20 pPriceLevel ALIAS FOR $12;
21 pActive ALIAS FOR $13;
22
23 vOuEmpty bigint = -99;
24 vComboCurrency character varying := 'CURRENCY';
25 vComboPriceLevel character varying := 'PRICELEVEL';
26 vFilterOuId text := '';
27 vFilterProductCodeName text := '';
28 vFilterGroupBrandName text := '';
29 vFilterParentGroupBrand text := '';
30 vFilterFlgGenuine text := '';
31 vFilterPriceLevel text := '';
32 vFilterActive text := '';
33 vEmpty text := '';
34
35 vUpperGroupBrand character varying := '';
36
37BEGIN
38 DELETE FROM tt_r_sell_price_for_so_item_data WHERE session_id = pSessionId;
39 DELETE FROM tt_r_sell_price_for_so_level_price WHERE session_id = pSessionId;
40
41 IF (pOuId <> vOuEmpty) THEN
42 vFilterOuId := ' AND a.ou_id = ' || pOuId;
43 END IF;
44
45 IF(pProductCodeName <> vEmpty) THEN
46 vFilterProductCodeName := ' AND ( UPPER(c.product_code) LIKE UPPER(''%'||pProductCodeName||'%'')
47 OR UPPER(c.product_name) LIKE UPPER(''%'||pProductCodeName||'%'') )';
48 END IF;
49
50 IF (pGroupBrandName) <> vEmpty THEN
51 vUpperGroupBrand := UPPER(pGroupBrandName);
52 vFilterGroupBrandName := ' AND (UPPER(h.group_brand_name) LIKE ''%' || vUpperGroupBrand || '%'' OR UPPER(h.group_brand_code) LIKE ''%' || vUpperGroupBrand || '%'')';
53 END IF;
54
55 IF (pParentGroupBrand) <> vEmpty THEN
56 vFilterParentGroupBrand := ' AND UPPER(h.parent_group_brand) = UPPER(''' || pParentGroupBrand || ''')';
57 END IF;
58
59 IF (pFlgGenuine) <> vEmpty THEN
60 vFilterFlgGenuine := ' AND UPPER(h.flg_genuine) = UPPER(''' || pFlgGenuine || ''')';
61 END IF;
62
63 IF (pPriceLevel <> vEmpty) THEN
64 vFilterPriceLevel := ' AND UPPER(a.price_level) = UPPER(''' || pPriceLevel || ''')';
65 END IF;
66
67 IF (pActive <> vEmpty) THEN
68 vFilterActive := ' AND UPPER(a.active) = ' || pActive;
69 END IF;
70
71 -- 2. insert data utama ke temp table
72 EXECUTE '
73 INSERT INTO tt_r_sell_price_for_so_item_data(
74 session_id, tenant_id, sell_price_product_for_so_id,
75 ou_id, ou_code, ou_name,
76 product_id, group_brand_code, group_brand_name, parent_group_brand,
77 flg_genuine, date_from, date_to, curr_code, gross_sell_price, min_sell_price,
78 flg_tax_amount, tax_id, tax_name, price_level, active)
79 SELECT $5, a.tenant_id, a.sell_price_product_for_so_id,
80 a.ou_id, COALESCE(b.ou_code, ''''), COALESCE(b.ou_name, ''''),
81 a.product_id, h.group_brand_code, h.group_brand_name, h.parent_group_brand,
82 h.flg_genuine, a.date_from, a.date_to, a.curr_code, a.gross_sell_price, a.min_sell_price,
83 a.flg_tax_amount, a.tax_id, COALESCE(e.tax_name, ''''), a.price_level, a.active
84 FROM m_sell_price_product_for_so a
85 LEFT JOIN t_ou b ON b.tenant_id = a.tenant_id AND b.ou_id = a.ou_id
86 INNER JOIN m_product c ON c.tenant_id = a.tenant_id AND c.product_id = a.product_id
87 INNER JOIN t_combo_value d ON d.code = a.curr_code AND d.combo_id = $1
88 LEFT OUTER JOIN m_tax e ON e.tenant_id = a.tenant_id AND e.tax_id = a.tax_id
89 INNER JOIN m_product_custom_for_sasa g ON a.tenant_id = g.tenant_id AND a.product_id = g.product_id
90 INNER JOIN m_group_brand h ON a.tenant_id = h.tenant_id AND g.group_brand_id = h.group_brand_id
91 WHERE a.tenant_id = $3 '
92 || vFilterOuId || vFilterProductCodeName || '
93 AND $4 BETWEEN a.date_from AND a.date_to '
94 || vFilterGroupBrandName || vFilterParentGroupBrand || vFilterFlgGenuine || vFilterPriceLevel || vFilterActive || '
95 ORDER BY c.product_code, c.product_name ASC '
96 USING vComboCurrency, vComboPriceLevel, pTenantId, pDateFrom, pSessionId;
97
98 -- 3. insert data min sell price per level price
99 INSERT INTO tt_r_sell_price_for_so_level_price(
100 session_id, tenant_id, product_id, price_level, min_sell_price)
101 SELECT pSessionId, A.tenant_id, A.product_id, A.price_level, A.min_sell_price
102 FROM tt_r_sell_price_for_so_item_data A
103 WHERE A.session_id = pSessionId
104 AND A.tenant_id = pTenantId;
105
106 -- 4. insert untuk data level price yang belum ada, dengan default min sell price = 0
107 WITH data_cek AS (
108 SELECT A.product_id
109 FROM tt_r_sell_price_for_so_level_price A
110 GROUP BY A.product_id
111 ) INSERT INTO tt_r_sell_price_for_so_level_price(
112 session_id, tenant_id, product_id, price_level, min_sell_price)
113 SELECT pSessionId, pTenantId, A.product_id, B.code, 0
114 FROM data_cek A, t_combo_value B
115 WHERE B.combo_id = vComboPriceLevel
116 AND NOT EXISTS(
117 SELECT 1
118 FROM tt_r_sell_price_for_so_level_price X
119 WHERE X.product_id = A.product_id
120 AND X.price_level = B.code);
121
122 Open pRefHeader FOR
123 SELECT CASE WHEN pOuId = vOuEmpty THEN 'All' ELSE f_get_ou_name(pOuId) END AS ou_name,
124 CASE WHEN pProductCodeName = vEmpty THEN 'All' ELSE pProductCodeName END AS product_code_name,
125 f_get_username(pUserId) AS user_name, f_get_role_name(pRoleId) AS role_name,
126 pDatetime AS datetime,
127 pDateFrom AS date_from,
128 CASE WHEN pGroupBrandName = vEmpty THEN 'All' ELSE pGroupBrandName END AS group_brand_name,
129 CASE WHEN pParentGroupBrand = vEmpty THEN 'All' ELSE pParentGroupBrand END AS parent_group_brand,
130 CASE WHEN pFlgGenuine = vEmpty THEN 'All' ELSE (CASE WHEN pFlgGenuine = 'Y' THEN 'Yes' ELSE 'No' END) END AS flg_genuine,
131 CASE WHEN pPriceLevel = vEmpty THEN 'All' ELSE pPriceLevel END as price_level;
132 RETURN NEXT pRefHeader;
133
134 Open pRefDetail FOR
135 WITH get_latest_data AS (
136 SELECT MAX(sell_price_product_for_so_id) AS id, a.product_id
137 FROM tt_r_sell_price_for_so_item_data a
138 GROUP BY a.product_id
139 ) SELECT a.sell_price_product_for_so_id, a.tenant_id, a.ou_id, a.ou_code, a.ou_name,
140 a.product_id, c.product_code, c.product_name, a.group_brand_code, a.group_brand_name, a.parent_group_brand,
141 CASE WHEN a.flg_genuine = 'Y' THEN 'Yes' ELSE 'No' END AS flg_genuine,
142 a.date_from, a.date_to, a.curr_code, a.gross_sell_price, a.min_sell_price,
143 CASE WHEN a.flg_tax_amount = 'Y' THEN 'Yes' ELSE 'No' END AS flg_tax_amount,
144 a.tax_id, A.tax_name, a.price_level AS price_level_code
145 FROM tt_r_sell_price_for_so_item_data A
146 INNER JOIN get_latest_data B ON A.sell_price_product_for_so_id = B.id
147 INNER JOIN m_product c ON c.tenant_id = a.tenant_id AND c.product_id = a.product_id
148 WHERE a.session_id = pSessionId
149 ORDER BY c.product_code, c.product_name ASC;
150 RETURN NEXT pRefDetail;
151
152 Open pRefItemLevelPrice FOR
153 SELECT A.product_id, A.price_level, A.min_sell_price
154 FROM tt_r_sell_price_for_so_level_price A
155 ORDER BY A.product_id, A.price_level ASC;
156 RETURN NEXT pRefItemLevelPrice;
157
158 Open pRefLevelPrice FOR
159 SELECT combo_id, code AS price_level_code
160 FROM t_combo_value WHERE combo_id = vComboPriceLevel
161 ORDER BY code ASC;
162 RETURN NEXT pRefLevelPrice;
163
164 DELETE FROM tt_r_sell_price_for_so_item_data WHERE session_id = pSessionId;
165 DELETE FROM tt_r_sell_price_for_so_level_price WHERE session_id = pSessionId;
166END;
167$BODY$
168 LANGUAGE plpgsql VOLATILE
169 COST 100
170 ROWS 1000;
171 /
172