· 8 years ago · Feb 27, 2018, 03:22 AM
1CREATE OR REPLACE FUNCTION f_upload_mapping_area_sales(BIGINT)
2 RETURNS INTEGER AS
3$BODY$
4DECLARE
5 pUlHeaderId ALIAS FOR $1;
6
7 vNullLongValue BIGINT := -99;
8 vTenantId BIGINT;
9 vCount BIGINT;
10 vUserId BIGINT;
11
12 vStatusOk CHARACTER VARYING(2) := 'OK';
13 vStatusFail CHARACTER VARYING(4) := 'FAIL';
14 vActionOverride CHARACTER VARYING(1) := 'O';
15 vActionSkip CHARACTER VARYING(1) := 'S';
16 vStatusX CHARACTER VARYING(1) := 'X';
17 vFlgYes CHARACTER VARYING(1) := 'Y';
18 vFlgNo CHARACTER VARYING(1) := 'N';
19 vGroupPartnerCustomer CHARACTER VARYING(1) := 'C';
20 vTypePartnerCodeSalesman CHARACTER VARYING(3) := 'SLS';
21
22 vKeyPeriodFrom CHARACTER VARYING := 'period_from';
23 vKeyActionForExistingData CHARACTER VARYING := 'action_for_existing_data';
24 vComboGroupBrand CHARACTER VARYING := 'GROUPBRANDPRODUCT';
25 vPeriodTo CHARACTER VARYING := '30001231';
26
27 vCurrentDateTime CHARACTER VARYING;
28 vPeriodFrom CHARACTER VARYING;
29 vActionForExistingData CHARACTER VARYING;
30
31
32BEGIN
33 SELECT TO_CHAR(CURRENT_DATE, 'YYYYMMDDHH24MISS')::CHARACTER VARYING INTO vCurrentDateTime;
34 SELECT tenant_id, create_user_id INTO vTenantId, vUserId FROM ul_header WHERE ul_header_id = pUlHeaderId;
35
36 IF EXISTS(SELECT 1 FROM ul_skip_detail WHERE ul_header_id = pUlHeaderId) THEN
37 RAISE EXCEPTION 'ADA ITEM YANG DI SKIP';
38 END IF;
39
40 -- GET PERIOD FROM
41 SELECT A.value::CHARACTER VARYING INTO vPeriodFrom
42 FROM ul_header_parameter A
43 WHERE A.ul_header_id = pUlHeaderId
44 AND A.key = vKeyPeriodFrom;
45
46 -- GET ACTION FOR EXISTING DATA
47 SELECT A.value::CHARACTER VARYING INTO vActionForExistingData
48 FROM ul_header_parameter A
49 WHERE A.ul_header_id = pUlHeaderId
50 AND A.key = vKeyActionForExistingData;
51
52 -- Update tenant di table ul_mapping_area_sales
53 UPDATE ul_mapping_area_sales
54 SET tenant_id = vTenantId
55 WHERE ul_header_id = pUlHeaderId;
56
57 -- Customer code yang di input dalam csv harus terdaftar, dengan group partner C dan masih active
58 UPDATE ul_mapping_area_sales A SET
59 status = vStatusX,
60 message = A.message||'Customer code tidak terdaftar di system, '
61 WHERE A.ul_header_id = pUlHeaderId
62 AND A.status <> vStatusFail
63 AND NOT EXISTS ( SELECT 1
64 FROM m_partner Z
65 INNER JOIN m_partner_type Y ON Z.partner_id = Y.partner_id
66 WHERE A.tenant_id = Z.tenant_id
67 AND A.customer_code = Z.partner_code
68 AND Z.active = vFlgYes
69 AND Y.group_partner = vGroupPartnerCustomer);
70
71 -- Salesman code yang di input dalam csv harus terdaftar, dengan type partner SLS dan masih active
72 UPDATE ul_mapping_area_sales A SET
73 status = vStatusX,
74 message = A.message||'Salesman code tidak terdaftar di system, '
75 WHERE A.ul_header_id = pUlHeaderId
76 AND A.status <> vStatusFail
77 AND NOT EXISTS ( SELECT 1
78 FROM m_partner Z
79 INNER JOIN m_partner_type Y ON Z.partner_id = Y.partner_id
80 INNER JOIN m_type_partner X ON Y.type_partner_id = X.type_partner_id
81 WHERE A.tenant_id = Z.tenant_id
82 AND A.salesman_code = Z.partner_code
83 AND Z.active = vFlgYes
84 AND X.type_partner_code = vTypePartnerCodeSalesman);
85
86 -- Sales manager code yang di input dalam csv harus terdaftar, dengan type partner SLS dan masih active
87 UPDATE ul_mapping_area_sales A SET
88 status = vStatusX,
89 message = A.message||'Sales manager code tidak terdaftar di system, '
90 WHERE A.ul_header_id = pUlHeaderId
91 AND A.status <> vStatusFail
92 AND NOT EXISTS ( SELECT 1
93 FROM m_partner Z
94 INNER JOIN m_partner_type Y ON Z.partner_id = Y.partner_id
95 INNER JOIN m_type_partner X ON Y.type_partner_id = X.type_partner_id
96 WHERE A.tenant_id = Z.tenant_id
97 AND A.sls_mgr_code = Z.partner_code
98 AND Z.active = vFlgYes
99 AND X.type_partner_code = vTypePartnerCodeSalesman);
100
101 -- Group brand yang di input dalam csv harus terdaftar di t_combo_value
102 UPDATE ul_mapping_area_sales A SET
103 status = vStatusX,
104 message = A.message||'Group brand tidak terdaftar di system, '
105 WHERE A.ul_header_id = pUlHeaderId
106 AND A.status <> vStatusFail
107 AND NOT EXISTS ( SELECT 1 FROM t_combo_value Z WHERE A.group_brand = Z.code AND Z.combo_id = vComboGroupBrand );
108
109 -- City code yang di input dalam csv harus terdaftar, dan masih active
110 UPDATE ul_mapping_area_sales A SET
111 status = vStatusX,
112 message = A.message||'City code tidak terdaftar di system, '
113 WHERE A.ul_header_id = pUlHeaderId
114 AND A.status <> vStatusFail
115 AND NOT EXISTS ( SELECT 1 FROM m_city Z WHERE A.city_code = Z.city_code AND Z.active = vFlgYes );
116
117 WITH duplicate_data AS (
118 SELECT ul_header_id, tenant_id, customer_code, group_brand, count(ul_header_id) AS count
119 FROM ul_mapping_area_sales
120 GROUP BY ul_header_id, tenant_id, customer_code, group_brand
121 HAVING count(ul_header_id) > 1
122 )
123 UPDATE ul_mapping_area_sales A SET
124 status = vStatusX,
125 message = A.message || 'Data mapping area sales berdasarkan customer_code, group_brand tidak boleh ada yang sama dalam satu file csv, '
126 WHERE A.ul_header_id = pUlHeaderId
127 AND A.status <> vStatusFail
128 AND EXISTS (SELECT 1 FROM duplicate_data B
129 WHERE A.ul_header_id = B.ul_header_id
130 AND A.tenant_id = B.tenant_id
131 AND A.customer_code = B.customer_code
132 AND A.group_brand = B.group_brand);
133
134 -- Cek apakah action existing data, jika O maka akan di update dengan data yang di input user, jika S maka akan di skip
135 IF (vActionForExistingData = vActionOverride) THEN
136 -- JIKA OVERRIDE MAKA UPDATE status menjadi O, data dengan status O akan di gunakan untuk mengupdate
137 UPDATE ul_mapping_area_sales A SET
138 status = vActionOverride,
139 message = A.message||'Data ini di gunakan untuk memperbarui data sebelum nya (override)'
140 WHERE A.ul_header_id = pUlHeaderId
141 AND A.status NOT IN (vStatusFail, vStatusX)
142 AND EXISTS ( SELECT 1 FROM sl_mapping_area_sales Z
143 WHERE A.tenant_id = Z.tenant_id
144 AND vPeriodFrom = Z.period_from
145 AND A.customer_code = Z.partner_code
146 AND A.group_brand = Z.group_brand);
147
148 ELSE
149 -- JIKA OVERRIDE MAKA UPDATE status menjadi S, data dengan status S tidak akan di gunakan untuk mengupdate/insert
150 UPDATE ul_mapping_area_sales A SET
151 status = vActionSkip,
152 message = A.message||'Data ini tidak di input ke system (skip)'
153 WHERE A.ul_header_id = pUlHeaderId
154 AND A.status NOT IN (vStatusFail, vStatusX)
155 AND EXISTS ( SELECT 1 FROM sl_mapping_area_sales Z
156 WHERE A.tenant_id = Z.tenant_id
157 AND vPeriodFrom = Z.period_from
158 AND A.customer_code = Z.partner_code
159 AND A.group_brand = Z.group_brand);
160
161 END IF;
162
163 -- JIKA DATA YANG DI INPUT DI CSV SUDAH ADA DI SYSTEM AKAN TETAPI PERIOD FROM NYA LEBIH KECIL DARI DATA YANG SUDAH ADA, MAKA TIDAK BOLEH
164 UPDATE ul_mapping_area_sales A SET
165 status = vStatusX,
166 message = A.message||'Data ini sudah ada di system maka period from tidak boleh lebih kecil dari period from data yang sudah ada, '
167 WHERE A.ul_header_id = pUlHeaderId
168 AND A.status NOT IN (vStatusFail, vStatusX, vActionOverride, vActionSkip)
169 AND EXISTS ( SELECT 1 FROM sl_mapping_area_sales Z
170 WHERE A.tenant_id = Z.tenant_id
171 AND A.customer_code = Z.partner_code
172 AND A.group_brand = Z.group_brand
173 AND Z.period_from > vPeriodFrom
174 AND Z.period_to = vPeriodTo);
175
176 --Set Status X menjadi FAIL
177 UPDATE ul_mapping_area_sales SET
178 status = vStatusFail,
179 message = SUBSTRING(message, 0, LENGTH(message)-1)
180 WHERE status = vStatusX
181 AND ul_header_id = pUlHeaderId;
182
183 --Set Status '' menjadi OK
184 UPDATE ul_mapping_area_sales SET
185 status = vStatusOk
186 WHERE status NOT IN ( vStatusFail, vActionOverride, vActionSkip )
187 AND ul_header_id = pUlHeaderId;
188
189 -- UPDATE EXISTING DATA MENGGUNAKAN DATA OVERIDE
190 UPDATE sl_mapping_area_sales A SET
191 salesman_code = B.salesman_code,
192 sls_mgr_code = B.sls_mgr_code,
193 city = B.city_code,
194 version = A.version + 1,
195 update_datetime = vCurrentDateTime,
196 update_user_id = vUserId
197 FROM ul_mapping_area_sales B
198 WHERE A.tenant_id = B.tenant_id
199 AND A.period_from = vPeriodFrom
200 AND A.partner_code = B.customer_code
201 AND A.group_brand = B.group_brand
202 AND B.status = vActionOverride;
203
204 -- UPDATE PERIOD TO EXISTING DATA JIKA DATA UPLOAD ADA YANG OVERLAP DATE FROM NYA
205 UPDATE sl_mapping_area_sales A SET
206 period_to = to_char(date_trunc('month', vPeriodFrom::date - interval '1' month), 'YYYYMMDD'),
207 version = A.version + 1,
208 update_datetime = vCurrentDateTime,
209 update_user_id = vUserId
210 FROM ul_mapping_area_sales B
211 WHERE A.tenant_id = B.tenant_id
212 AND vPeriodFrom BETWEEN A.period_from AND A.period_to
213 AND A.period_to = vPeriodTo
214 AND A.partner_code = B.customer_code
215 AND A.group_brand = B.group_brand
216 AND B.status = vStatusOk;
217
218 -- INSERT DATA CSV DENGAN STATUS OK KE TABLE MAPPING AREA SALES
219 INSERT INTO sl_mapping_area_sales(
220 tenant_id, partner_code, salesman_code,
221 sls_mgr_code, group_brand, city, period_from, period_to, create_datetime,
222 create_user_id, update_datetime, update_user_id, version)
223 SELECT A.tenant_id, A.customer_code, A.salesman_code,
224 A.sls_mgr_code, A.group_brand, A.city_code, vPeriodFrom, vPeriodTo, vCurrentDateTime,
225 vUserId, vCurrentDateTime, vUserId, 0
226 FROM ul_mapping_area_sales A
227 WHERE A.status = vStatusOk
228 AND A.ul_header_id = pUlHeaderId;
229
230 SELECT COUNT(1) INTO vCount
231 FROM ul_mapping_area_sales
232 WHERE status = vStatusFail
233 AND ul_header_id = pUlHeaderId;
234
235 RETURN vCount;
236
237END;
238$BODY$
239 LANGUAGE plpgsql VOLATILE
240 COST 100
241/