· 6 years ago · Aug 15, 2019, 06:44 AM
1-- Function: public.f_migrate_toko_pelanggan(bigint)
2
3-- DROP FUNCTION public.f_migrate_toko_pelanggan(bigint);
4
5CREATE OR REPLACE FUNCTION public.f_migrate_toko_pelanggan(bigint)
6 RETURNS void AS
7$BODY$
8DECLARE
9
10 pUploadHeaderId ALIAS FOR $1;
11
12 vTenantId bigint;
13 vUserId bigint;
14 vTypePartnerId bigint;
15 vOuCompanyId bigint;
16 vDatetime character varying(14);
17 vCurrCode character varying;
18
19 vDefaultRoleId bigint := -99;
20
21 vFail character varying(4) := 'FAIL';
22 vOk character varying(2) := 'OK';
23 vEmpty character varying(1) := '';
24 vYes character varying := 'Y';
25 vNo character varying := 'N';
26 vInProgress character varying := 'I';
27 vActionInsert character varying(1) := 'I';
28 vApplicationCode character varying := 'TS';
29 vSpaceValue character varying := ' ';
30 vGroupCustomer character varying(1) := 'C';
31
32 vEmptyId bigint := -99;
33
34BEGIN
35
36 -- siapkan parameter
37 SELECT tenant_id INTO vTenantId FROM ul_header WHERE ul_header_id = pUploadHeaderId;
38 SELECT user_id INTO vUserId FROM ul_header WHERE ul_header_id = pUploadHeaderId;
39 SELECT datetime INTO vDatetime FROM ul_header WHERE ul_header_id = pUploadHeaderId;
40
41 -- GET id type partner CUST
42 SELECT type_partner_id INTO vTypePartnerId
43 FROM m_type_partner
44 WHERE type_partner_code = 'CUST';
45
46-- -- get default role
47-- SELECT role_id INTO vDefaultRoleId
48-- FROM t_role
49-- WHERE role_name = f_get_value_system_config_by_param_code(vTenantId, 'default.role.customer');
50
51 -- GET curr code from sysconfig
52 SELECT f_get_value_system_config_by_param_code(vTenantId, 'ValutaBuku') INTO vCurrCode;
53
54 -- RESET table temp
55 DELETE FROM tt_ul_partner WHERE upload_header_id = pUploadHeaderId;
56
57 -- GET id OU company
58 SELECT A.ou_id INTO vOuCompanyId
59 FROM t_ou A
60 INNER JOIN t_ou_type B ON A.ou_type_id = B.ou_type_id
61 WHERE A.tenant_id = vTenantId
62 AND B.flg_bu = vYes
63 AND B.flg_sub_bu = vNo
64 AND B.flg_branch = vNo
65 AND B.tenant_id = vTenantId;
66
67-- IF NOT EXISTS (
68-- SELECT 1
69-- FROM ul_customer A
70-- WHERE A.upload_header_id = pUploadHeaderId
71-- AND A.status = vFail OR A.flg_validate = vNo OR A.flg_migrate = vNo
72-- ) THEN
73
74 -- Update status non FAIL to OK
75 UPDATE ul_customer
76 SET status = vOk
77 WHERE upload_header_id = pUploadHeaderId;
78
79 -- MULAI PROSES INSERT
80 -- INSERT OK data untuk customer code baru
81 WITH insert_data_customer AS (
82 INSERT INTO m_partner(
83 tenant_id, partner_code, partner_name, ctgr_partner_id,
84 partner_ext_name, npwp_id, holding_id, flg_holding, rank, create_datetime,
85 create_user_id, update_datetime, update_user_id, version, active,
86 active_datetime, non_active_datetime, line_of_business, price_level,
87 industry_type, npwp_status)
88 SELECT vTenantId, A.kode_pelanggan, A.nama_pelanggan, B.ctgr_partner_id,
89 A.nama_external, vEmptyId, vEmptyId, vNo, A.rank, vDatetime,
90 vUserId, vDatetime, vUserId, 0, A.active,
91 CASE WHEN A.active = vYes
92 THEN vDatetime
93 ELSE vSpaceValue
94 END AS active_datetime,
95 CASE WHEN A.active = vNo
96 THEN vDatetime
97 ELSE vSpaceValue
98 END AS non_active_datetime,
99 A.line_of_business, A.price_level,
100 A.industry_type, A.npwp_status
101 FROM tt_ul_customer_unique A
102 INNER JOIN m_ctgr_partner B ON A.kode_kategori = B.ctgr_partner_code AND B.tenant_id = vTenantId
103 WHERE A.upload_header_id = pUploadHeaderId
104 AND NOT EXISTS (
105 SELECT 1 FROM m_partner Z
106 WHERE A.kode_pelanggan = Z.partner_code AND Z.tenant_id = vTenantId
107 )
108 RETURNING *
109 )
110 INSERT INTO tt_ul_partner(
111 upload_header_id, action_type, partner_id, tenant_id, partner_code,
112 partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
113 flg_holding, rank, create_datetime, create_user_id, update_datetime,
114 update_user_id, version, active, active_datetime, non_active_datetime,
115 line_of_business, price_level, industry_type, npwp_status)
116 SELECT pUploadHeaderId, vActionInsert, partner_id, tenant_id, partner_code,
117 partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
118 flg_holding, rank, create_datetime, create_user_id, update_datetime,
119 update_user_id, version, active, active_datetime, non_active_datetime,
120 line_of_business, price_level, industry_type, npwp_status
121 FROM insert_data_customer;
122
123 -- INSERT data partner address
124 INSERT INTO m_partner_address(
125 tenant_id, partner_id, address_desc, address1,
126 address2, address3, city, zip_code, state_or_province, country,
127 phone1, phone2, fax1, fax2, flg_default, create_datetime, create_user_id,
128 update_datetime, update_user_id, version, active, active_datetime,
129 non_active_datetime, flg_ship, flg_bill, flg_mail, flg_official,
130 flg_others, longitude, latitude)
131 SELECT vTenantId, A.partner_id, B.address_desc, B.address_1,
132 B.address_2, B.address_3, B.city, B.zip_code, D.province_name, B.country,
133 B.phone_1, B.phone_2, B.fax_1, B.fax_2, CASE WHEN B.official_addr = vYes THEN vYes ELSE vNo END, vDatetime, vUserId,
134 vDatetime, vUserId, 0, vYes, vDatetime,
135 vSpaceValue, B.shipping_addr, B.billing_addr, vNo, B.official_addr,
136 vNo, B.longitude::numeric, B.latitude::numeric
137 FROM tt_ul_partner A
138 INNER JOIN tt_ul_customer_partner_address B ON A.partner_code = B.kode_pelanggan
139-- INNER JOIN m_city C ON UPPER(B.city) = UPPER(C.city_name)
140 INNER JOIN m_city_global CC ON B.city = CC.city_name
141 INNER JOIN m_province D ON CC.province_id = D.province_id
142 WHERE A.upload_header_id = pUploadHeaderId
143 AND A.upload_header_id = B.upload_header_id
144 AND A.action_type = vActionInsert;
145
146 -- INSERT data partner cp
147 INSERT INTO m_partner_cp(
148 tenant_id, partner_id, cp_name, cp_job, address1,
149 address2, address3, city, zip_code, country, phone1, phone2,
150 fax1, fax2, email, create_datetime, create_user_id, update_datetime,
151 update_user_id, version, active, active_datetime, non_active_datetime,
152 department, job_level, phone_ext1, phone_ext2, mobile_phone1,
153 mobile_phone2, flg_responsibility, flg_email_notif, flg_email_notif_ap,
154 flg_email_notif_ar)
155 SELECT vTenantId, A.partner_id, B.cp_name, B.cp_job, vEmpty,
156 vEmpty, vEmpty, vEmpty, vEmpty, vEmpty, B.cp_phone_1, B.cp_phone_2,
157 vEmpty, vEmpty, B.cp_email, vDatetime, vUserId, vDatetime,
158 vUserId, 0, vYes, vDatetime, vSpaceValue,
159 B.cp_department, B.cp_job_level, vEmpty, vEmpty, B.cp_mobile_1,
160 B.cp_mobile_2, vNo, vNo, vNo,
161 vNo
162 FROM tt_ul_partner A
163 INNER JOIN tt_ul_customer_partner_cp B ON A.partner_code = B.kode_pelanggan
164 WHERE A.upload_header_id = pUploadHeaderId
165 AND A.upload_header_id = B.upload_header_id
166 AND A.action_type = vActionInsert;
167
168 -- INSERT data customer ext
169 INSERT INTO m_customer_ext(
170 partner_id, tenant_id, regular_discount_percentage, create_datetime,
171 create_user_id, update_datetime, update_user_id, version, active,
172 active_datetime, non_active_datetime)
173 SELECT A.partner_id, vTenantId, B.disc_reguler_pct::numeric, vDatetime,
174 vUserId, vDatetime, vUserId, 0, vYes,
175 vDatetime, vSpaceValue
176 FROM tt_ul_partner A
177 INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
178 WHERE A.upload_header_id = pUploadHeaderId
179 AND A.upload_header_id = B.upload_header_id
180 AND A.action_type = vActionInsert;
181
182 -- INSERT data customer ext for dlg
183 INSERT INTO m_customer_ext_for_dlg(
184 partner_id, tenant_id, flg_show_inv_tax, create_datetime, create_user_id,
185 update_datetime, update_user_id, version, top_external)
186 SELECT A.partner_id, vTenantId, B.status_pkp, vDatetime, vUserId,
187 vDatetime, vUserId, 0, B.top_in_days::integer
188 FROM tt_ul_partner A
189 INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
190 WHERE A.upload_header_id = pUploadHeaderId
191 AND A.upload_header_id = B.upload_header_id
192 AND A.action_type = vActionInsert;
193
194 -- INSERT data partner npwp
195 INSERT INTO m_partner_npwp(
196 tenant_id, partner_id, npwp_name, npwp_no, npwp_date,
197 address1, address2, address3, city, zip_code, country, phone1,
198 phone2, fax1, fax2, create_datetime, create_user_id, update_datetime,
199 update_user_id, version, active, active_datetime, non_active_datetime,
200 flg_pkp, pkp_date)
201 SELECT vTenantId, A.partner_id, A.partner_name, B.npwp, to_char(current_date, 'YYYYMMDD'),
202 vEmpty, vEmpty, vEmpty, vEmpty, vEmpty, vEmpty, vEmpty,
203 vEmpty, vEmpty, vEmpty, vDatetime, vUserId, vDatetime,
204 vUserId, 0, vYes, vDatetime, vSpaceValue,
205 B.status_pkp, to_char(current_date, 'YYYYMMDD')
206 FROM tt_ul_partner A
207 INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
208 WHERE A.upload_header_id = pUploadHeaderId
209 AND A.upload_header_id = B.upload_header_id
210 AND A.action_type = vActionInsert;
211
212 -- INSERT data partner type
213 INSERT INTO m_partner_type(
214 tenant_id, partner_id, group_partner, type_partner_id,
215 due_date, curr_code, amount_limit, flg_commision, create_datetime,
216 create_user_id, update_datetime, update_user_id, version, active,
217 active_datetime, non_active_datetime)
218 SELECT vTenantId, A.partner_id, vGroupCustomer, vTypePartnerId,
219 B.top_in_days::integer, vCurrCode, B.credit_limit::numeric, vNo, vDatetime,
220 vUserId, vDatetime, vUserId, 0, vYes,
221 vDatetime, vSpaceValue
222 FROM tt_ul_partner A
223 INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
224 WHERE A.upload_header_id = pUploadHeaderId
225 AND A.upload_header_id = B.upload_header_id
226 AND A.action_type = vActionInsert;
227
228 -- INSERT data partner rel
229 INSERT INTO m_partner_rel(
230 tenant_id, partner_id, relation_id, flg_bill,
231 flg_shipp, create_datetime, create_user_id, update_datetime,
232 update_user_id, version, active, active_datetime, non_active_datetime)
233 SELECT vTenantId, A.partner_id, A.partner_id, vYes,
234 vYes, vDatetime, vUserId, vDatetime,
235 vUserId, 0, vYes, vDatetime, vSpaceValue
236 FROM tt_ul_partner A
237 WHERE A.upload_header_id = pUploadHeaderId
238 AND A.action_type = vActionInsert;
239
240 -- INSERT data cust payment
241 INSERT INTO m_cust_payment(
242 tenant_id, partner_id, flg_invoice, due_payment_days,
243 flg_payment_mode, payment_day, payment_date, partner_bank_id,
244 create_datetime, create_user_id, update_datetime, update_user_id,
245 version, active, active_datetime, non_active_datetime)
246 SELECT vTenantId, A.partner_id, vNo, B.top_in_days::integer,
247 vNo, vEmpty, vEmpty, vEmptyId,
248 vDatetime, vUserId, vDatetime, vUserId,
249 0, vYes, vDatetime, vSpaceValue
250 FROM tt_ul_partner A
251 INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
252 WHERE A.upload_header_id = pUploadHeaderId
253 AND A.upload_header_id = B.upload_header_id
254 AND A.action_type = vActionInsert;
255
256 -- INSERT data m_customer_ext_for_sas
257
258 INSERT INTO m_customer_ext_for_sas(
259 partner_id, tenant_id, ref_id, district_id, sub_district_id, custom_wilayah,
260 sparepart_type, store_status, store_start_year, store_end_year, owner_sex,
261 owner_place_birth, owner_date_birth, identity_no, remark, delivery_date,
262 sales_date, idwil, identity_pic_file_path, store_pic_file_path, external_info,
263 create_datetime, create_user_id, update_datetime, update_user_id,
264 version, active, active_datetime, non_active_datetime)
265 SELECT A.partner_id, vTenantId, vEmptyId, vEmptyId, vEmptyId, B.custom_wilayah,
266 B.tipe_sparepart, B.status_toko, B.store_year, B.store_end_year, B.owner_sex,
267 B.owner_place_birth, B.owner_date_birth, B.identity_no, B.remark, B.jx::numeric,
268 B.js::numeric, B.idwil, '', '', B.id_toko_wiser,
269 vDatetime, vUserId, vDatetime, vUserId,
270 0, vYes, vDatetime, vSpaceValue
271 FROM tt_ul_partner A
272 JOIN ul_customer B ON A.upload_header_id = B.upload_header_id
273 WHERE A.upload_header_id = pUploadHeaderId
274 AND A.action_type = vActionInsert;
275
276 -- INSERT data region customer
277-- WITH insert_region_customer AS (
278-- INSERT INTO m_region_customer(
279-- tenant_id, region_id, customer_id, version, create_datetime, create_user_id,
280-- update_datetime, update_user_id)
281-- SELECT A.tenant_id, C.region_id, A.partner_id, 0, vDatetime, vUserId,
282-- vDatetime, vUserId
283-- FROM tt_ul_partner A
284-- INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan AND A.upload_header_id = B.upload_header_id
285-- INNER JOIN m_region C ON B.kode_region = C.region_code AND A.tenant_id = C.tenant_id
286-- WHERE A.upload_header_id = pUploadHeaderId
287--
288-- RETURNING *
289-- )
290 -- INSERT data partner ou, berdasarkan region. Data ini digunakan di web admin untuk menentukan OU atas SO
291-- INSERT INTO m_partner_ou(
292-- partner_id, ou_id, version, create_datetime, create_user_id,
293-- update_datetime, update_user_id)
294-- SELECT A.customer_id, C.ou_id, A.version, A.create_datetime, A.create_user_id,
295-- A.update_datetime, A.update_user_id
296-- FROM insert_region_customer A
297-- INNER JOIN m_region C ON A.region_id = C.region_id;
298--
299-- UPDATE ul_customer
300-- SET flg_validate = vYes
301-- WHERE upload_header_id = pUploadHeaderId AND flg_migrate = vInProgress;
302
303 -- RESET TEMP TABLE
304 DELETE FROM tt_ul_customer_unique WHERE upload_header_id = pUploadHeaderId;
305 DELETE FROM tt_ul_customer_partner_address WHERE upload_header_id = pUploadHeaderId;
306 DELETE FROM tt_ul_customer_partner_cp WHERE upload_header_id = pUploadHeaderId;
307-- END IF;
308
309END ;
310$BODY$
311 LANGUAGE plpgsql VOLATILE
312 COST 100;
313/