Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION ul_upload_customer(bigint)
- RETURNS bigint AS
- $BODY$
- DECLARE
- pUploadHeaderId ALIAS FOR $1;
- vFail character varying(4) := 'FAIL';
- vOk character varying(2) := 'OK';
- vYes character varying(1) := 'Y';
- vNo character varying(1) := 'N';
- vGood character varying(1) := 'G';
- vActionInsert character varying(1) := 'I';
- vActionUpdate character varying(1) := 'U';
- vActionError character varying(1) := 'E';
- vGroupCustomer character varying(1) := 'C';
- vEmpty character varying(1) := '';
- vSpaceValue character varying(1) := ' ';
- vTenantId bigint;
- vUserId bigint;
- vTypePartnerId bigint;
- vOuCompanyId bigint;
- vDatetime character varying(14);
- vCurrCode character varying;
- vDefaultRoleId bigint := -99;
- vDefaultPolicyId bigint := -99;
- vEmptyId bigint := -99;
- vCountFail bigint := 0;
- BEGIN
- -- siapkan parameter
- vTenantId = CAST( f_get_upload_parameter(pUploadHeaderId, 'tenantId') AS bigint );
- vUserId = CAST( f_get_upload_parameter(pUploadHeaderId, 'userId') AS bigint );
- vDatetime = CAST( f_get_upload_parameter(pUploadHeaderId, 'datetime') AS character varying );
- -- get default role
- SELECT role_id INTO vDefaultRoleId
- FROM t_role
- WHERE role_name = f_get_value_system_config_by_param_code(vTenantId, 'default.role.customer');
- IF FOUND THEN
- -- RESET TEMP TABLE
- DELETE FROM tt_ul_customer_unique WHERE upload_header_id = pUploadHeaderId;
- DELETE FROM tt_ul_customer_partner_address WHERE upload_header_id = pUploadHeaderId;
- DELETE FROM tt_ul_customer_partner_cp WHERE upload_header_id = pUploadHeaderId;
- -- INIT data temp table
- INSERT INTO tt_ul_customer_unique(
- upload_header_id, kode_pelanggan, nama_pelanggan, nama_external,
- kode_kategori, rank, line_of_business, industry_type, npwp_status,
- price_level, top_in_days, kode_region, credit_limit, disc_reguler_pct,
- npwp, status_pkp, active)
- SELECT upload_header_id, kode_pelanggan, nama_pelanggan, nama_external,
- kode_kategori, rank, line_of_business, industry_type, npwp_status,
- price_level, top_in_days, kode_region, credit_limit, disc_reguler_pct,
- npwp, status_pkp, active
- FROM ul_customer
- WHERE upload_header_id = pUploadHeaderId
- AND trim(kode_pelanggan) <> vEmpty
- GROUP BY upload_header_id, kode_pelanggan, nama_pelanggan, nama_external,
- kode_kategori, rank, line_of_business, industry_type, npwp_status,
- price_level, top_in_days, kode_region, credit_limit, disc_reguler_pct,
- npwp, status_pkp, active;
- INSERT INTO tt_ul_customer_partner_address(
- upload_header_id, kode_pelanggan, address_desc, address_1, address_2,
- address_3, zip_code, city, state_or_province, country, official_addr,
- shipping_addr, billing_addr, longitude, latitude, phone_1, phone_2,
- fax_1, fax_2)
- SELECT upload_header_id, kode_pelanggan, address_desc, address_1, address_2,
- address_3, zip_code, city, state_or_province, country, official_addr,
- shipping_addr, billing_addr, longitude, latitude, phone_1, phone_2,
- fax_1, fax_2
- FROM ul_customer
- WHERE upload_header_id = pUploadHeaderId
- AND trim(kode_pelanggan) <> vEmpty
- AND trim(address_desc) <> vEmpty;
- INSERT INTO tt_ul_customer_partner_cp(
- upload_header_id, kode_pelanggan, cp_name, cp_job, cp_department,
- cp_job_level, cp_email, cp_phone_1, cp_phone_2, cp_mobile_1,
- cp_mobile_2)
- SELECT upload_header_id, kode_pelanggan, cp_name, cp_job, cp_department,
- cp_job_level, cp_email, cp_phone_1, cp_phone_2, cp_mobile_1,
- cp_mobile_2
- FROM ul_customer
- WHERE upload_header_id = pUploadHeaderId
- AND trim(kode_pelanggan) <> vEmpty
- AND trim(cp_name) <> vEmpty;
- -- GET id type partner CUST
- SELECT type_partner_id INTO vTypePartnerId
- FROM m_type_partner
- WHERE type_partner_code = 'CUST';
- -- Update kolom yang kosong ke nilai default
- UPDATE ul_customer A
- SET longitude = '0'
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.longitude) = vEmpty;
- UPDATE ul_customer A
- SET latitude = '0'
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.latitude) = vEmpty;
- -- Validasi
- -- # Kode pelanggan harus diisi, dan Max character kode pelanggan 50
- -- # Nama pelanggan harus diisi, Max character nama pelanggan adalah 1024
- -- # Harus tidak boleh ada data csv dengan kode = kode partner non customer di existing data
- -- # Nama external jika diisi, maka max characternya harus 100
- -- # Kode kategori wajib diisi, Kode kategori harus terdaftar disystem (m_ctgr_partner)
- -- # Rank harus diisi & harus terdaftar disystem (t_combo_value dengan combo id = PARTNERRANK)
- -- # Line of business jika diisi, maka harus terdaftar disystem (t_combo_value dengan combo id = LINEOFBUSINESS)
- -- # Industry Type wajib diisi & harus terdaftar disystem (t_combo_value dengan combo id = INDUSTRYTYPE)
- -- # NPWP Status wajib diisi & harus terdaftar disystem (t_combo_value dengan combo id = TAXCODE)
- -- # Price level wajib diisi & harus terdaftar disystem (m_level_price)
- -- # TOP in days wajib diisi & harus integer
- -- # Kode region wajib diisi & harus terdaftar disystem (m_region)
- -- # Credit limit wajib diisi & harus numeric dan lebih dari sama dengan -1
- -- # Disc reguler pct wajib diisi >= 0 dan <= 100, type data harus numeric
- -- # Address Desc wajib diisi & Max character Address Desc adalah 100
- -- # Address 1 Wajib diisi & Max character Address 1 adalah 100
- -- # Max character Address 2 adalah 100 jika diisi
- -- # Max character Address 3 adalah 100 jika diisi
- -- # Max character Zip Code adalah 100 jika diisi
- -- # City wajib diisi & harus terdaftar di system (m_city)
- -- # Country wajib diisi, dan harus terdaftar disystem (t_combo_value dengan combo id = COUNTRY)
- -- # Official addr wajib diisi dengan nilai Y/N
- -- # Shipping Addr Wajib diisi dengan nilai Y/N
- -- # Billing Addr Wajib diisi dengan nilai Y/N
- -- # Longitude harus numeric jika diisi, jika dikosongi maka di anggap 0
- -- # Latitude harus numeric jika diisi, jika dikosongi maka di anggap 0
- -- # Max character Phone 1 adalah 100, jika diisi
- -- # Max character Phone 2 adalah 100, jika diisi
- -- # Max character Fax 1 adalah 100, jika diisi
- -- # Max character Fax 2 adalah 100, jika diisi
- -- # CP Name wajib diisi dan max character adalah 100
- -- # CP Job wajib diisi dan max character adalah 100
- -- # CP Department wajib diisi dan harus terdaftar di system (t_combo_value dengan combo id = DEPARTMENT)
- -- # CP Job level wajib diisi dan harus terdaftar di system (t_combo_value dengan combo id = JOBLEVEL)
- -- # Max character CP Email adalah 100 jika diisi
- -- # Max character CP Phone 1 adalah 100 jika diisi
- -- # Max character CP Phone 2 adalah 100 jika diisi
- -- # Max character CP Mobile 1 adalah 100 jika diisi
- -- # Max character CP Mobile 2 adalah 100 jika diisi
- -- # Jika NPWP diisi maka max characternya adalah 50
- -- # Jika Status PKP diisi, maka nilainya harus Y/N
- -- # Active harus diisi dengan nilai Y/N
- -- # Nilai dari kolom berikut harus sama untuk kode produk yang sama : "Nama Pelanggan, Nama external, Kode Kategori, Rank, Line of business, Industry Type, NPWP Status, Price level, TOP in days, Kode region, Credit limit, Disc reguler pct, npwp, Status PKP, Active"-- # Nilai dari kolom berikut harus sama untuk kode produk yang sama : "Nama Pelanggan, Nama external, Kode Kategori, Rank, Line of business, Industry Type, NPWP Status, Price level, TOP in days, Kode region, Credit limit, Disc reguler pct, npwp, Status PKP, Active"
- -- # Validasi Address desc harus unique untuk kode pelanggan yang sama
- -- # Validasi CP Name harus unique untuk kode pelanggan yang sama
- -- # Kode pelanggan harus diisi, dan Max character kode pelanggan 50
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Customer code must be filled, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.kode_pelanggan) = vEmpty;
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Max customer code is 50 character, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND length(A.kode_pelanggan) > 50;
- -- # Harus tidak boleh ada data csv dengan kode = kode partner non customer di existing data
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Code already registered on system for non customer partner, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND EXISTS (
- SELECT 1 FROM m_partner Z
- INNER JOIN m_partner_type Y ON Z.partner_id = Y.partner_id
- WHERE A.kode_pelanggan = Z.partner_code AND Z.tenant_id = vTenantId
- AND Y.type_partner_id <> vTypePartnerId
- );
- -- # Nama pelanggan harus diisi, Max character nama pelanggan adalah 1024
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Customer name must be filled, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.nama_pelanggan) = vEmpty;
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Max Customer name is 1024 character, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND length(A.nama_pelanggan) > 1024;
- -- # Nama external jika diisi, maka max characternya harus 100
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Max external name is 100 character, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.nama_external) <> vEmpty
- AND length(A.nama_external) > 100;
- -- # Kode kategori wajib diisi, Kode kategori harus terdaftar disystem (m_ctgr_partner)
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Category code must be filled, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.kode_kategori) = vEmpty;
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Category is not registred on system, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.kode_kategori) <> vEmpty
- AND NOT EXISTS(
- SELECT 1 FROM m_ctgr_partner Z WHERE Z.ctgr_partner_code = A.kode_kategori AND Z.tenant_id = vTenantId
- );
- -- # Rank harus diisi & harus terdaftar disystem (t_combo_value dengan combo id = PARTNERRANK)
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Rank must be filled, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.rank) = vEmpty;
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Rank is not registred on system, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.rank) <> vEmpty
- AND NOT EXISTS(
- SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = 'PARTNERRANK' AND Z.code = A.rank
- );
- -- # Line of business jika diisi, maka harus terdaftar disystem (t_combo_value dengan combo id = LINEOFBUSINESS)
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Line of business is not registred on system, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.line_of_business) <> vEmpty
- AND NOT EXISTS(
- SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = 'LINEOFBUSINESS' AND Z.code = A.line_of_business
- );
- -- # Industry Type wajib diisi & harus terdaftar disystem (t_combo_value dengan combo id = INDUSTRYTYPE)
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Industry Type must be filled, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.industry_type) = vEmpty;
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Industry Type is not registred on system, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.industry_type) <> vEmpty
- AND NOT EXISTS(
- SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = 'INDUSTRYTYPE' AND Z.code = A.industry_type
- );
- -- # NPWP Status wajib diisi & harus terdaftar disystem (t_combo_value dengan combo id = TAXCODE)
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'NPWP status must be filled, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.npwp_status) = vEmpty;
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'NPWP status is not registred on system, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.npwp_status) <> vEmpty
- AND NOT EXISTS(
- SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = 'TAXCODE' AND Z.code = A.npwp_status
- );
- -- # Price level wajib diisi & harus terdaftar disystem (m_level_price)
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Price level must be filled, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.price_level) = vEmpty;
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Price level is not registred on system, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.price_level) <> vEmpty
- AND NOT EXISTS(
- SELECT 1 FROM m_level_price Z WHERE Z.level_price_code = A.price_level AND Z.tenant_id = vTenantId
- );
- -- # TOP in days wajib diisi & harus integer, dan harus >= 0
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'TOP in days must be filled, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.top_in_days) = vEmpty;
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'TOP in days must be integer, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(top_in_days) <> vEmpty
- AND NOT is_integer(A.top_in_days);
- WITH data_customer AS (
- SELECT ul_customer_id, top_in_days
- FROM ul_customer
- WHERE upload_header_id = pUploadHeaderId
- AND TRIM(top_in_days) <> vEmpty
- AND is_integer(top_in_days)
- )
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'TOP in days must be greater than or equals 0, '
- FROM data_customer B
- WHERE A.ul_customer_id = B.ul_customer_id
- AND B.top_in_days::numeric < 0;
- -- # Kode region wajib diisi & harus terdaftar disystem (m_region)
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Region code must be filled, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.kode_region) = vEmpty;
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Region code is not registred on system, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.kode_region) <> vEmpty
- AND NOT EXISTS(
- SELECT 1 FROM m_region Z WHERE Z.region_code = A.kode_region AND Z.tenant_id = vTenantId
- );
- -- # Credit limit wajib diisi & harus numeric dan lebih dari sama dengan -1
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Credit limit must be filled, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.credit_limit) = vEmpty;
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Credit limit must be numeric, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(credit_limit) <> vEmpty
- AND NOT is_numeric(A.credit_limit);
- WITH data_customer AS (
- SELECT ul_customer_id, credit_limit
- FROM ul_customer
- WHERE upload_header_id = pUploadHeaderId
- AND TRIM(credit_limit) <> vEmpty
- AND is_numeric(credit_limit)
- )
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Credit limit must be greater than or equals -1, '
- FROM data_customer B
- WHERE A.ul_customer_id = B.ul_customer_id
- AND B.credit_limit::numeric < -1;
- -- # Disc reguler pct wajib diisi >= 0 dan <= 100, type data harus numeric
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Disc reguler pct must be filled, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.disc_reguler_pct) = vEmpty;
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Disc reguler pct must be numeric, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(disc_reguler_pct) <> vEmpty
- AND NOT is_numeric(A.disc_reguler_pct);
- WITH data_customer AS (
- SELECT ul_customer_id, disc_reguler_pct
- FROM ul_customer
- WHERE upload_header_id = pUploadHeaderId
- AND TRIM(disc_reguler_pct) <> vEmpty
- AND is_numeric(disc_reguler_pct)
- ), val_greater_or_equals_zero AS (
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Disc reguler pct must be greater than or equals to zero, '
- FROM data_customer B
- WHERE A.ul_customer_id = B.ul_customer_id
- AND B.disc_reguler_pct::numeric < 0
- )
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Disc reguler pct must be less than or equals to 100, '
- FROM data_customer B
- WHERE A.ul_customer_id = B.ul_customer_id
- AND B.disc_reguler_pct::numeric > 100;
- -- # Setiap kode customer baru harus memiliki minimal 1 partner address baru
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Customer must have address at least 1, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.kode_pelanggan) <> vEmpty
- AND NOT EXISTS ( SELECT 1 FROM m_partner Z WHERE Z.partner_code = A.kode_pelanggan AND Z.tenant_id = vTenantId )
- AND NOT EXISTS ( SELECT 1 FROM tt_ul_customer_partner_address Z
- WHERE Z.upload_header_id = pUploadHeaderId
- AND Z.kode_pelanggan = A.kode_pelanggan);
- -- # Partner address harus unique by address desc untuk setiap kode pelanggan
- WITH data_partner_address AS (
- SELECT A.kode_pelanggan, A.address_desc
- FROM tt_ul_customer_partner_address A
- WHERE A.upload_header_id = pUploadHeaderId
- GROUP BY A.kode_pelanggan, A.address_desc
- HAVING COUNT(1) > 1
- )
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Duplicate address desc, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND EXISTS (
- SELECT 1 FROM data_partner_address Z WHERE A.kode_pelanggan = Z.kode_pelanggan AND A.address_desc = Z.address_desc
- );
- -- # Pada partner address desc yang sudah ada di system, address 1 & address 2 & address 3 nya harus belum ada di system
- WITH partner_desc_existing AS (
- SELECT A.upload_header_id, A.kode_pelanggan, A.address_desc, A.address_1, A.address_2, A.address_3
- FROM tt_ul_customer_partner_address A
- WHERE A.upload_header_id = pUploadHeaderId
- AND EXISTS (
- SELECT 1 FROM m_partner_address Z
- WHERE A.kode_pelanggan = f_get_partner_code(Z.partner_id)
- AND Z.tenant_id = vTenantId
- )
- )
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Address 1 and address 2 and address 3 already exists on system, '
- FROM partner_desc_existing B
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.upload_header_id = B.upload_header_id
- AND A.kode_pelanggan = B.kode_pelanggan
- AND A.address_1 = B.address_1
- AND A.address_2 = B.address_2
- AND A.address_3 = B.address_3
- AND EXISTS (
- SELECT 1 FROM m_partner_address Z
- WHERE B.kode_pelanggan = f_get_partner_code(Z.partner_id)
- AND Z.address1 = B.address_1
- AND Z.address2 = B.address_2
- AND Z.address3 = B.address_3
- AND Z.tenant_id = vTenantId
- AND Z.address_desc <> B.address_desc
- );
- -- # Partner address harus unique by address 1, address 2, address 3 untuk setiap kode pelanggan
- WITH data_partner_address AS (
- SELECT A.kode_pelanggan, A.address_1, A.address_2, A.address_3
- FROM tt_ul_customer_partner_address A
- WHERE A.upload_header_id = pUploadHeaderId
- GROUP BY A.kode_pelanggan, A.address_1, A.address_2, A.address_3
- HAVING COUNT(1) > 1
- )
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Duplicate address 1 and address 2 and address 3, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND EXISTS (
- SELECT 1 FROM data_partner_address Z WHERE A.kode_pelanggan = Z.kode_pelanggan AND A.address_1 = Z.address_1 AND A.address_2 = Z.address_2 AND A.address_3 = Z.address_3
- );
- -- # Official address yang bernilai Y harus cuma ada 1 untuk tiap customer
- WITH data_official_addr AS (
- SELECT A.kode_pelanggan, A.official_addr
- FROM tt_ul_customer_partner_address A
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.official_addr = vYes
- GROUP BY A.kode_pelanggan, A.official_addr
- HAVING COUNT(1) > 1
- )
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Customer can only have 1 official address, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND EXISTS (
- SELECT 1 FROM data_official_addr Z WHERE A.kode_pelanggan = Z.kode_pelanggan AND A.official_addr = Z.official_addr
- );
- -- # Untuk customer baru harus punya partner address dengan flg official = Y minimal 1
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Customer must have exactly one official address, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.kode_pelanggan) <> vEmpty
- AND NOT EXISTS ( SELECT 1 FROM m_partner Z WHERE Z.partner_code = A.kode_pelanggan AND Z.tenant_id = vTenantId )
- AND NOT EXISTS ( SELECT 1 FROM tt_ul_customer_partner_address Z
- WHERE Z.upload_header_id = pUploadHeaderId
- AND Z.kode_pelanggan = A.kode_pelanggan
- AND Z.official_addr = vYes);
- -- # Untuk customer existing hanya boleh mempunyai 1 Official address
- WITH data_official_address AS (
- SELECT A.kode_pelanggan, COUNT(1) AS result
- FROM tt_ul_customer_partner_address A
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.official_addr = vYes
- GROUP BY A.kode_pelanggan
- UNION ALL
- SELECT f_get_partner_code(A.partner_id) AS kode_pelanggan, COUNT(1) AS result
- FROM m_partner_address A
- WHERE A.flg_official = vYes
- AND EXISTS (
- SELECT 1 FROM tt_ul_customer_unique Z WHERE f_get_partner_code(A.partner_id) = Z.kode_pelanggan AND Z.upload_header_id = pUploadHeaderId
- )
- AND NOT EXISTS (
- SELECT 1 FROM tt_ul_customer_partner_address Z WHERE f_get_partner_code(A.partner_id) = Z.kode_pelanggan AND Z.upload_header_id = pUploadHeaderId
- AND A.address_desc = Z.address_desc
- )
- GROUP BY A.partner_id
- ), sum_data_official_address AS (
- SELECT kode_pelanggan, SUM(result)
- FROM data_official_address
- GROUP BY kode_pelanggan
- HAVING SUM(result) > 1
- )
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Customer must have exactly 1 official address include existing address, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND EXISTS (
- SELECT 1 FROM m_partner Z WHERE A.kode_pelanggan = Z.partner_code AND Z.tenant_id = vTenantId
- )
- AND EXISTS (
- SELECT 1 FROM sum_data_official_address Z WHERE A.kode_pelanggan = Z.kode_pelanggan
- );
- -- # Address Desc wajib diisi apabila kolom partner address lainya ada yang diisi & Max character Address Desc adalah 100
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Address desc must be filled, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.kode_pelanggan) <> vEmpty
- AND TRIM(A.address_desc) = vEmpty
- AND TRIM(A.address_1
- ||A.address_2
- ||A.address_3
- ||A.zip_code
- ||A.city
- ||A.country
- ||A.official_addr
- ||A.shipping_addr
- ||A.billing_addr
- ||CASE WHEN A.longitude='0' THEN vEmpty ELSE A.longitude END
- ||CASE WHEN A.latitude='0' THEN vEmpty ELSE A.latitude END
- ||A.phone_1
- ||A.phone_2
- ||A.fax_1
- ||A.fax_2) <> vEmpty;
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Max address desc is 100 character, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.address_desc) <> vEmpty
- AND length(A.address_desc) > 100;
- -- # Address 1 Wajib diisi & Max character Address 1 adalah 100
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Address 1 must be filled, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.address_desc) <> vEmpty
- AND TRIM(A.address_1) = vEmpty;
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Max address 1 is 100 character, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.address_1) <> vEmpty
- AND length(A.address_1) > 100;
- -- # Max character Address 2 adalah 100 jika diisi
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Max address 2 is 100 character, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.address_2) <> vEmpty
- AND length(A.address_2) > 100;
- -- # Max character Address 3 adalah 100 jika diisi
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Max address 3 is 100 character, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.address_3) <> vEmpty
- AND length(A.address_3) > 100;
- -- # Max character Zip Code adalah 100 jika diisi
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Max zip code is 100 character, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.zip_code) <> vEmpty
- AND length(A.zip_code) > 100;
- -- # City wajib diisi & harus terdaftar di system (m_city)
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'City 1 must be filled, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.address_desc) <> vEmpty
- AND TRIM(A.city) = vEmpty;
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'City is not registred on system, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.city) <> vEmpty
- AND NOT EXISTS(
- SELECT 1 FROM m_city Z WHERE Z.city_code = A.city AND Z.tenant_id = vTenantId
- );
- -- # Country wajib diisi, dan harus terdaftar disystem (t_combo_value dengan combo id = COUNTRY)
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Country must be filled, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.address_desc) <> vEmpty
- AND TRIM(A.country) = vEmpty;
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Country is not registred on system, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.country) <> vEmpty
- AND NOT EXISTS(
- SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = 'COUNTRY' AND Z.code = A.country
- );
- -- # Official addr wajib diisi dengan nilai Y/N
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Official addr can only be filled with Y or N, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.address_desc) <> vEmpty
- AND A.official_addr NOT IN (vYes, vNo);
- -- # Shipping Addr Wajib diisi dengan nilai Y/N
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Shipping Addr can only be filled with Y or N, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.address_desc) <> vEmpty
- AND A.shipping_addr NOT IN (vYes, vNo);
- -- # Billing Addr Wajib diisi dengan nilai Y/N
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Billing Addr can only be filled with Y or N, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.address_desc) <> vEmpty
- AND A.billing_addr NOT IN (vYes, vNo);
- -- # Longitude harus numeric jika diisi, jika dikosongi maka di anggap 0
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Longitude must be numeric, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.longitude) <> vEmpty
- AND NOT is_numeric(A.longitude);
- -- # Latitude harus numeric jika diisi, jika dikosongi maka di anggap 0
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Latitude must be numeric, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.latitude) <> vEmpty
- AND NOT is_numeric(A.latitude);
- -- # Max character Phone 1 adalah 100, jika diisi
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Max phone 1 is 100 character, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.phone_1) <> vEmpty
- AND length(A.phone_1) > 100;
- -- # Max character Phone 2 adalah 100, jika diisi
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Max phone 2 is 100 character, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.phone_2) <> vEmpty
- AND length(A.phone_2) > 100;
- -- # Max character Fax 1 adalah 100, jika diisi
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Max fax 1 is 100 character, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.fax_1) <> vEmpty
- AND length(A.fax_1) > 100;
- -- # Max character Fax 2 adalah 100, jika diisi
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Max fax 2 is 100 character, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.fax_2) <> vEmpty
- AND length(A.fax_2) > 100;
- -- # Setiap kode customer baru harus memiliki minimal 1 partner baru
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Customer must have Partner CP at least 1, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.kode_pelanggan) <> vEmpty
- AND NOT EXISTS ( SELECT 1 FROM m_partner Z WHERE Z.partner_code = A.kode_pelanggan AND Z.tenant_id = vTenantId )
- AND NOT EXISTS ( SELECT 1 FROM tt_ul_customer_partner_cp Z
- WHERE Z.upload_header_id = pUploadHeaderId
- AND Z.kode_pelanggan = A.kode_pelanggan);
- -- # Partner CP harus unique by CP Name untuk setiap kode pelanggan
- WITH data_partner_cp AS (
- SELECT A.kode_pelanggan, A.cp_name
- FROM tt_ul_customer_partner_cp A
- WHERE A.upload_header_id = pUploadHeaderId
- GROUP BY A.kode_pelanggan, A.cp_name
- HAVING COUNT(1) > 1
- )
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Duplicate CP Name, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND EXISTS (
- SELECT 1 FROM data_partner_cp Z WHERE A.kode_pelanggan = Z.kode_pelanggan AND A.cp_name = Z.cp_name
- );
- -- # CP Name wajib diisi apabila kolom partner cp lainya ada yang diisi & Max character Address Desc adalah 100
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'CP name must be filled, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.kode_pelanggan) <> vEmpty
- AND TRIM(A.cp_name) = vEmpty
- AND TRIM(A.cp_job
- ||A.cp_department
- ||A.cp_job_level
- ||A.cp_email
- ||A.cp_phone_1
- ||A.cp_phone_2
- ||A.cp_mobile_1
- ||A.cp_mobile_2) <> vEmpty;
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Max CP name is 100 character, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.cp_name) <> vEmpty
- AND length(A.cp_name) > 100;
- -- # CP Job wajib diisi dan max character adalah 100
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'CP job must be filled, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.cp_name) <> vEmpty
- AND TRIM(A.cp_job) = vEmpty;
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Max CP job is 100 character, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.cp_job) <> vEmpty
- AND length(A.cp_job) > 100;
- -- # CP Department wajib diisi dan harus terdaftar di system (t_combo_value dengan combo id = DEPARTMENT)
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'CP Department must be filled, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.cp_name) <> vEmpty
- AND TRIM(A.cp_department) = vEmpty;
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'CP department is not registred on system, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.cp_department) <> vEmpty
- AND NOT EXISTS(
- SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = 'DEPARTMENT' AND Z.code = A.cp_department
- );
- -- # CP Job level wajib diisi dan harus terdaftar di system (t_combo_value dengan combo id = JOBLEVEL)
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'CP job level must be filled, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.cp_name) <> vEmpty
- AND TRIM(A.cp_job_level) = vEmpty;
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'CP job level is not registred on system, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.cp_job_level) <> vEmpty
- AND NOT EXISTS(
- SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = 'JOBLEVEL' AND Z.code = A.cp_job_level
- );
- -- # Max character CP Email adalah 100 jika diisi
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Max CP Email is 100 character, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.cp_email) <> vEmpty
- AND length(A.cp_email) > 100;
- -- # Max character CP Phone 1 adalah 100 jika diisi
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Max CP Phone 1 is 100 character, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.cp_phone_1) <> vEmpty
- AND length(A.cp_phone_1) > 100;
- -- # Max character CP Phone 2 adalah 100 jika diisi
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Max CP Phone 2 is 100 character, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.cp_phone_2) <> vEmpty
- AND length(A.cp_phone_2) > 100;
- -- # Max character CP Mobile 1 adalah 100 jika diisi
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Max CP Mobile 1 is 100 character, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.cp_mobile_1) <> vEmpty
- AND length(A.cp_mobile_1) > 100;
- -- # Max character CP Mobile 2 adalah 100 jika diisi
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Max CP Mobile 2 is 100 character, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.cp_mobile_2) <> vEmpty
- AND length(A.cp_mobile_2) > 100;
- -- # NPWP harus diisi jika Status NPWP = Y
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'NPWP must be filled when Status NPWP is Y, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.npwp) = vEmpty
- AND A.status_pkp = vYes;
- -- # Jika NPWP diisi maka max characternya adalah 50
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Max NPWP is 50 character, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND TRIM(A.npwp) <> vEmpty
- AND length(A.npwp) > 50;
- -- # Status PKP wajib diisi dan nilainya harus Y/N
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Status PKP can only be filled with Y or N, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.status_pkp NOT IN (vYes, vNo);
- -- # Active harus diisi dengan nilai Y/N
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Active can only be filled with Y or N, '
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.active NOT IN (vYes, vNo);
- WITH invalid_data_customer AS (
- SELECT A.kode_pelanggan
- FROM tt_ul_customer_unique A
- WHERE A.upload_header_id = pUploadHeaderId
- GROUP BY A.kode_pelanggan
- HAVING COUNT(1) > 1
- )
- -- # Nilai dari kolom berikut harus sama untuk kode produk yang sama : "Nama Pelanggan, Nama external, Kode Kategori, Rank, Line of business, Industry Type, NPWP Status, Price level, TOP in days, Kode region, Credit limit, Disc reguler pct, npwp, Status PKP, Active"
- UPDATE ul_customer A
- SET status = vFail,
- message = message || 'Nilai dari kolom berikut harus sama untuk kode produk yang sama : "Nama Pelanggan, Nama external, Kode Kategori, Rank, Line of business, Industry Type, NPWP Status, Price level, TOP in days, Kode region, Credit limit, Disc reguler pct, npwp, Status PKP, Active", '
- WHERE A.upload_header_id = pUploadHeaderId
- AND EXISTS (
- SELECT 1 FROM invalid_data_customer Z WHERE A.kode_pelanggan = Z.kode_pelanggan
- );
- -- Cek apakah ada item CSV yang statusnya fail
- IF EXISTS ( SELECT 1 FROM ul_customer B
- WHERE B.upload_header_id = pUploadHeaderId
- AND B.status = vFail ) THEN
- -- Sesuaikan message, agar message paling belakang tidak ada koma
- UPDATE ul_customer A
- SET message = substr(A.message, 1, length(A.message)-2)
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.status = vFail
- AND TRIM(A.message) != vEmpty;
- -- Update semua status menjadi FAIL jika ada salah satu item yang terkena validasi
- -- Karena jika ada salah 1 item yang fail, maka 1 csv dianggap tidak valid
- -- ubah update_status menjadi E untuk menandakan bahwa data error / tidak digunakan untuk membuat data
- UPDATE ul_customer A
- SET status = vFail,
- update_status = vActionError
- WHERE upload_header_id = pUploadHeaderId;
- -- Hitung jumlah data yang FAIL
- SELECT COUNT(1) INTO vCountFail
- FROM ul_customer
- WHERE upload_header_id = pUploadHeaderId
- AND status = vFail;
- ELSE
- -- RESET table temp
- DELETE FROM tt_ul_partner WHERE upload_header_id = pUploadHeaderId;
- -- GET id OU company
- SELECT A.ou_id INTO vOuCompanyId
- FROM t_ou A
- INNER JOIN t_ou_type B ON A.ou_type_id = B.ou_type_id
- WHERE A.tenant_id = vTenantId
- AND B.flg_bu = vYes
- AND B.flg_sub_bu = vNo
- AND B.flg_branch = vNo
- AND B.tenant_id = vTenantId;
- -- Update status non FAIL to OK
- UPDATE ul_customer
- SET status = vOk
- WHERE upload_header_id = pUploadHeaderId;
- -- UPDATE OK data untuk customer code terdaftar
- WITH update_data_customer AS (
- UPDATE m_partner A SET
- partner_name=B.nama_pelanggan,
- ctgr_partner_id=C.ctgr_partner_id,
- partner_ext_name=B.nama_external,
- rank=B.rank,
- line_of_business=B.line_of_business,
- industry_type=B.industry_type,
- npwp_status=B.npwp_status,
- price_level=B.price_level,
- update_datetime=vDatetime,
- update_user_id=vUserId,
- version=A.version+1,
- active=B.active,
- active_datetime=CASE WHEN A.active = vNo AND B.active = vYes
- THEN vDatetime
- ELSE A.active_datetime
- END,
- non_active_datetime=CASE WHEN A.active = vYes AND B.active = vNo
- THEN vDatetime
- ELSE A.non_active_datetime
- END
- FROM tt_ul_customer_unique B
- INNER JOIN m_ctgr_partner C ON B.kode_kategori = C.ctgr_partner_code AND c.tenant_id = vTenantId
- WHERE B.upload_header_id = pUploadHeaderId
- AND A.partner_code = B.kode_pelanggan
- AND A.tenant_id = vTenantId
- RETURNING A.*
- )
- INSERT INTO tt_ul_partner(
- upload_header_id, action_type, partner_id, tenant_id, partner_code,
- partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
- flg_holding, rank, create_datetime, create_user_id, update_datetime,
- update_user_id, version, active, active_datetime, non_active_datetime,
- line_of_business, price_level, industry_type, npwp_status)
- SELECT pUploadHeaderId, vActionUpdate, partner_id, tenant_id, partner_code,
- partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
- flg_holding, rank, create_datetime, create_user_id, update_datetime,
- update_user_id, version, active, active_datetime, non_active_datetime,
- line_of_business, price_level, industry_type, npwp_status
- FROM update_data_customer;
- -- INSERT OK data untuk customer code baru
- WITH insert_data_customer AS (
- INSERT INTO m_partner(
- tenant_id, partner_code, partner_name, ctgr_partner_id,
- partner_ext_name, npwp_id, holding_id, flg_holding, rank, create_datetime,
- create_user_id, update_datetime, update_user_id, version, active,
- active_datetime, non_active_datetime, line_of_business, price_level,
- industry_type, npwp_status)
- SELECT vTenantId, A.kode_pelanggan, A.nama_pelanggan, B.ctgr_partner_id,
- A.nama_external, vEmptyId, vEmptyId, vNo, A.rank, vDatetime,
- vUserId, vDatetime, vUserId, 0, A.active,
- CASE WHEN A.active = vYes
- THEN vDatetime
- ELSE vSpaceValue
- END AS active_datetime,
- CASE WHEN A.active = vNo
- THEN vDatetime
- ELSE vSpaceValue
- END AS non_active_datetime,
- vSpaceValue, vSpaceValue,
- vSpaceValue, vSpaceValue
- FROM tt_ul_customer_unique A
- INNER JOIN m_ctgr_partner B ON A.kode_kategori = B.ctgr_partner_code AND B.tenant_id = vTenantId
- WHERE A.upload_header_id = pUploadHeaderId
- AND NOT EXISTS (
- SELECT 1 FROM m_partner Z
- WHERE A.kode_pelanggan = Z.partner_code AND Z.tenant_id = vTenantId
- )
- RETURNING *
- )
- INSERT INTO tt_ul_partner(
- upload_header_id, action_type, partner_id, tenant_id, partner_code,
- partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
- flg_holding, rank, create_datetime, create_user_id, update_datetime,
- update_user_id, version, active, active_datetime, non_active_datetime,
- line_of_business, price_level, industry_type, npwp_status)
- SELECT pUploadHeaderId, vActionInsert, partner_id, tenant_id, partner_code,
- partner_name, ctgr_partner_id, partner_ext_name, npwp_id, holding_id,
- flg_holding, rank, create_datetime, create_user_id, update_datetime,
- update_user_id, version, active, active_datetime, non_active_datetime,
- line_of_business, price_level, industry_type, npwp_status
- FROM insert_data_customer;
- -- ACTION FOR UPPDATE
- IF EXISTS (SELECT 1 FROM tt_ul_partner WHERE upload_header_id = pUploadHeaderId AND action_type = vActionUpdate) THEN
- -- Ubah update_status menjadi U jika data digunakan untuk Update
- UPDATE ul_customer A SET
- update_status = vActionUpdate
- WHERE A.upload_header_id = pUploadHeaderId
- AND EXISTS (
- SELECT 1 FROM tt_ul_partner B
- WHERE A.kode_pelanggan = B.partner_code
- AND B.tenant_id = vTenantId
- AND B.action_type = vActionUpdate
- );
- -- UPDATE data partner address
- UPDATE m_partner_address A SET
- address1=C.address_1,
- address2=C.address_2,
- address3=C.address_3,
- city=C.city,
- zip_code=C.zip_code,
- state_or_province=D.province,
- country=C.country,
- phone1=C.phone_1,
- phone2=C.phone_2,
- fax1=C.fax_1,
- fax2=C.fax_2,
- flg_default=CASE WHEN C.official_addr = vYes THEN vYes ELSE vNo END,
- update_datetime=vDatetime,
- update_user_id=vUserId,
- version=A.version+1,
- flg_ship=C.shipping_addr,
- flg_bill=C.billing_addr,
- flg_official=C.official_addr,
- longitude=C.longitude::numeric,
- latitude=C.latitude::numeric
- FROM tt_ul_partner B
- INNER JOIN tt_ul_customer_partner_address C ON B.partner_code = C.kode_pelanggan
- INNER JOIN m_city D ON C.city = D.city_code AND D.tenant_id = vTenantId
- WHERE B.upload_header_id = pUploadHeaderId
- AND B.upload_header_id = C.upload_header_id
- AND A.partner_id = B.partner_id
- AND A.address_desc = C.address_desc
- AND A.tenant_id = B.tenant_id
- AND B.action_type = vActionUpdate;
- -- INSERT data partner new address
- INSERT INTO m_partner_address(
- tenant_id, partner_id, address_desc, address1,
- address2, address3, city, zip_code, state_or_province, country,
- phone1, phone2, fax1, fax2, flg_default, create_datetime, create_user_id,
- update_datetime, update_user_id, version, active, active_datetime,
- non_active_datetime, flg_ship, flg_bill, flg_mail, flg_official,
- flg_others, longitude, latitude)
- SELECT vTenantId, A.partner_id, B.address_desc, B.address_1,
- B.address_2, B.address_3, B.city, B.zip_code, C.province, B.country,
- 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,
- vDatetime, vUserId, 0, vYes, vDatetime,
- vSpaceValue, B.shipping_addr, B.billing_addr, vNo, B.official_addr,
- vNo, B.longitude::numeric, B.latitude::numeric
- FROM tt_ul_partner A
- INNER JOIN tt_ul_customer_partner_address B ON A.partner_code = B.kode_pelanggan
- INNER JOIN m_city C ON B.city = C.city_code AND C.tenant_id = vTenantId
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.upload_header_id = B.upload_header_id
- AND A.action_type = vActionUpdate
- AND NOT EXISTS (
- SELECT 1 FROM m_partner_address Z
- WHERE B.address_desc = Z.address_desc
- AND A.partner_id = Z.partner_id
- AND Z.tenant_id = vTenantId
- );
- -- UPDATE data partner CP
- UPDATE m_partner_cp A SET
- cp_job=C.cp_job,
- phone1=C.cp_phone_1,
- phone2=C.cp_phone_2,
- email=C.cp_email,
- update_datetime=vDatetime,
- update_user_id=vUserId,
- version=A.version+1,
- department=C.cp_department,
- job_level=C.cp_job_level,
- mobile_phone1=C.cp_mobile_1,
- mobile_phone2=C.cp_mobile_2
- FROM tt_ul_partner B
- INNER JOIN tt_ul_customer_partner_cp C ON B.partner_code = C.kode_pelanggan
- WHERE B.upload_header_id = pUploadHeaderId
- AND B.upload_header_id = C.upload_header_id
- AND A.partner_id = B.partner_id
- AND A.cp_name = C.cp_name
- AND B.action_type = vActionUpdate;
- -- INSERT data partner cp
- INSERT INTO m_partner_cp(
- tenant_id, partner_id, cp_name, cp_job, address1,
- address2, address3, city, zip_code, country, phone1, phone2,
- fax1, fax2, email, create_datetime, create_user_id, update_datetime,
- update_user_id, version, active, active_datetime, non_active_datetime,
- department, job_level, phone_ext1, phone_ext2, mobile_phone1,
- mobile_phone2, flg_responsibility, flg_email_notif, flg_email_notif_ap,
- flg_email_notif_ar)
- SELECT vTenantId, A.partner_id, B.cp_name, B.cp_job, vEmpty,
- vEmpty, vEmpty, vEmpty, vEmpty, vEmpty, B.cp_phone_1, B.cp_phone_2,
- vEmpty, vEmpty, B.cp_email, vDatetime, vUserId, vDatetime,
- vUserId, 0, vYes, vDatetime, vSpaceValue,
- B.cp_department, B.cp_job_level, vEmpty, vEmpty, B.cp_mobile_1,
- B.cp_mobile_2, vNo, vNo, vNo,
- vNo
- FROM tt_ul_partner A
- INNER JOIN tt_ul_customer_partner_cp B ON A.partner_code = B.kode_pelanggan
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.upload_header_id = B.upload_header_id
- AND A.action_type = vActionUpdate
- AND NOT EXISTS (
- SELECT 1 FROM m_partner_cp Z
- WHERE B.cp_name = Z.cp_name
- AND A.partner_id = Z.partner_id
- AND Z.tenant_id = vTenantId
- );
- -- UPDATE data partner type
- UPDATE m_partner_type A SET
- due_date=C.top_in_days::integer,
- amount_limit=C.credit_limit::numeric,
- update_datetime=vDatetime,
- update_user_id=vUserId,
- version=A.version+1
- FROM tt_ul_partner B
- INNER JOIN tt_ul_customer_unique C ON B.partner_code = C.kode_pelanggan
- WHERE B.upload_header_id = pUploadHeaderId
- AND B.upload_header_id = C.upload_header_id
- AND A.partner_id = B.partner_id
- AND B.action_type = vActionUpdate;
- -- UPDATE data customer ext
- UPDATE m_customer_ext A SET
- regular_discount_percentage=C.disc_reguler_pct::numeric,
- update_datetime=vDatetime,
- update_user_id=vUserId,
- version=A.version+1
- FROM tt_ul_partner B
- INNER JOIN tt_ul_customer_unique C ON B.partner_code = C.kode_pelanggan
- WHERE B.upload_header_id = pUploadHeaderId
- AND B.upload_header_id = C.upload_header_id
- AND A.partner_id = B.partner_id
- AND B.action_type = vActionUpdate;
- -- UPDATE data customer ext for dlg
- UPDATE m_customer_ext_for_dlg A SET
- top_external=C.top_in_days::integer,
- update_datetime=vDatetime,
- update_user_id=vUserId,
- version=A.version+1
- FROM tt_ul_partner B
- INNER JOIN tt_ul_customer_unique C ON B.partner_code = C.kode_pelanggan
- WHERE B.upload_header_id = pUploadHeaderId
- AND B.upload_header_id = C.upload_header_id
- AND A.partner_id = B.partner_id
- AND B.action_type = vActionUpdate;
- -- DELETE existing data partner npwp
- DELETE FROM m_partner_npwp A
- WHERE EXISTS (
- SELECT 1 FROM tt_ul_partner Z WHERE A.partner_id = Z.partner_id
- );
- -- INSERT data partner npwp
- INSERT INTO m_partner_npwp(
- tenant_id, partner_id, npwp_name, npwp_no, npwp_date,
- address1, address2, address3, city, zip_code, country, phone1,
- phone2, fax1, fax2, create_datetime, create_user_id, update_datetime,
- update_user_id, version, active, active_datetime, non_active_datetime,
- flg_pkp, pkp_date)
- SELECT vTenantId, A.partner_id, A.partner_name, B.npwp, to_char(current_date, 'YYYYMMDD'),
- vEmpty, vEmpty, vEmpty, vEmpty, vEmpty, vEmpty, vEmpty,
- vEmpty, vEmpty, vEmpty, vDatetime, vUserId, vDatetime,
- vUserId, 0, vYes, vDatetime, vSpaceValue,
- B.status_pkp, to_char(current_date, 'YYYYMMDD')
- FROM tt_ul_partner A
- INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.upload_header_id = B.upload_header_id
- AND A.action_type = vActionUpdate;
- -- UPDATE data cust payment
- UPDATE m_cust_payment A SET
- due_payment_days=C.top_in_days::integer,
- update_datetime=vDatetime,
- update_user_id=vUserId,
- version=A.version+1
- FROM tt_ul_partner B
- INNER JOIN tt_ul_customer_unique C ON B.partner_code = C.kode_pelanggan
- WHERE B.upload_header_id = pUploadHeaderId
- AND B.upload_header_id = C.upload_header_id
- AND A.partner_id = B.partner_id
- AND B.action_type = vActionUpdate;
- END IF;
- -- ACTION FOR INSERT
- IF EXISTS (SELECT 1 FROM tt_ul_partner WHERE upload_header_id = pUploadHeaderId AND action_type = vActionInsert) THEN
- -- GET curr code from sysconfig
- SELECT f_get_value_system_config_by_param_code(vTenantId, 'ValutaBuku') INTO vCurrCode;
- -- INSERT data partner address
- INSERT INTO m_partner_address(
- tenant_id, partner_id, address_desc, address1,
- address2, address3, city, zip_code, state_or_province, country,
- phone1, phone2, fax1, fax2, flg_default, create_datetime, create_user_id,
- update_datetime, update_user_id, version, active, active_datetime,
- non_active_datetime, flg_ship, flg_bill, flg_mail, flg_official,
- flg_others, longitude, latitude)
- SELECT vTenantId, A.partner_id, B.address_desc, B.address_1,
- B.address_2, B.address_3, B.city, B.zip_code, C.province, B.country,
- 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,
- vDatetime, vUserId, 0, vYes, vDatetime,
- vSpaceValue, B.shipping_addr, B.billing_addr, vNo, B.official_addr,
- vNo, B.longitude::numeric, B.latitude::numeric
- FROM tt_ul_partner A
- INNER JOIN tt_ul_customer_partner_address B ON A.partner_code = B.kode_pelanggan
- INNER JOIN m_city C ON B.city = C.city_code AND C.tenant_id = vTenantId
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.upload_header_id = B.upload_header_id
- AND A.action_type = vActionInsert;
- -- INSERT data partner cp
- INSERT INTO m_partner_cp(
- tenant_id, partner_id, cp_name, cp_job, address1,
- address2, address3, city, zip_code, country, phone1, phone2,
- fax1, fax2, email, create_datetime, create_user_id, update_datetime,
- update_user_id, version, active, active_datetime, non_active_datetime,
- department, job_level, phone_ext1, phone_ext2, mobile_phone1,
- mobile_phone2, flg_responsibility, flg_email_notif, flg_email_notif_ap,
- flg_email_notif_ar)
- SELECT vTenantId, A.partner_id, B.cp_name, B.cp_job, vEmpty,
- vEmpty, vEmpty, vEmpty, vEmpty, vEmpty, B.cp_phone_1, B.cp_phone_2,
- vEmpty, vEmpty, B.cp_email, vDatetime, vUserId, vDatetime,
- vUserId, 0, vYes, vDatetime, vSpaceValue,
- B.cp_department, B.cp_job_level, vEmpty, vEmpty, B.cp_mobile_1,
- B.cp_mobile_2, vNo, vNo, vNo,
- vNo
- FROM tt_ul_partner A
- INNER JOIN tt_ul_customer_partner_cp B ON A.partner_code = B.kode_pelanggan
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.upload_header_id = B.upload_header_id
- AND A.action_type = vActionInsert;
- -- INSERT data customer ext
- INSERT INTO m_customer_ext(
- partner_id, tenant_id, regular_discount_percentage, create_datetime,
- create_user_id, update_datetime, update_user_id, version, active,
- active_datetime, non_active_datetime)
- SELECT A.partner_id, vTenantId, B.disc_reguler_pct::numeric, vDatetime,
- vUserId, vDatetime, vUserId, 0, vYes,
- vDatetime, vSpaceValue
- FROM tt_ul_partner A
- INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.upload_header_id = B.upload_header_id
- AND A.action_type = vActionInsert;
- -- INSERT data customer ext for dlg
- INSERT INTO m_customer_ext_for_dlg(
- partner_id, tenant_id, flg_show_inv_tax, create_datetime, create_user_id,
- update_datetime, update_user_id, version, top_external)
- SELECT A.partner_id, vTenantId, B.status_pkp, vDatetime, vUserId,
- vDatetime, vUserId, 0, B.top_in_days::integer
- FROM tt_ul_partner A
- INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.upload_header_id = B.upload_header_id
- AND A.action_type = vActionInsert;
- -- INSERT data partner npwp
- INSERT INTO m_partner_npwp(
- tenant_id, partner_id, npwp_name, npwp_no, npwp_date,
- address1, address2, address3, city, zip_code, country, phone1,
- phone2, fax1, fax2, create_datetime, create_user_id, update_datetime,
- update_user_id, version, active, active_datetime, non_active_datetime,
- flg_pkp, pkp_date)
- SELECT vTenantId, A.partner_id, A.partner_name, B.npwp, to_char(current_date, 'YYYYMMDD'),
- vEmpty, vEmpty, vEmpty, vEmpty, vEmpty, vEmpty, vEmpty,
- vEmpty, vEmpty, vEmpty, vDatetime, vUserId, vDatetime,
- vUserId, 0, vYes, vDatetime, vSpaceValue,
- B.status_pkp, to_char(current_date, 'YYYYMMDD')
- FROM tt_ul_partner A
- INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.upload_header_id = B.upload_header_id
- AND A.action_type = vActionInsert;
- -- INSERT data partner ou
- INSERT INTO m_partner_ou(
- partner_id, ou_id, version, create_datetime, create_user_id,
- update_datetime, update_user_id)
- SELECT A.partner_id, vOuCompanyId, 0, vDatetime, vUserId,
- vDatetime, vUserId
- FROM tt_ul_partner A
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.action_type = vActionInsert;
- -- INSERT data partner type
- INSERT INTO m_partner_type(
- tenant_id, partner_id, group_partner, type_partner_id,
- due_date, curr_code, amount_limit, flg_commision, create_datetime,
- create_user_id, update_datetime, update_user_id, version, active,
- active_datetime, non_active_datetime)
- SELECT vTenantId, A.partner_id, vGroupCustomer, vTypePartnerId,
- B.top_in_days::integer, vCurrCode, B.credit_limit::numeric, vNo, vDatetime,
- vUserId, vDatetime, vUserId, 0, vYes,
- vDatetime, vSpaceValue
- FROM tt_ul_partner A
- INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.upload_header_id = B.upload_header_id
- AND A.action_type = vActionInsert;
- -- INSERT data partner rel
- INSERT INTO m_partner_rel(
- tenant_id, partner_id, relation_id, flg_bill,
- flg_shipp, create_datetime, create_user_id, update_datetime,
- update_user_id, version, active, active_datetime, non_active_datetime)
- SELECT vTenantId, A.partner_id, A.partner_id, vYes,
- vYes, vDatetime, vUserId, vDatetime,
- vUserId, 0, vYes, vDatetime, vSpaceValue
- FROM tt_ul_partner A
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.action_type = vActionInsert;
- -- INSERT data cust payment
- INSERT INTO m_cust_payment(
- tenant_id, partner_id, flg_invoice, due_payment_days,
- flg_payment_mode, payment_day, payment_date, partner_bank_id,
- create_datetime, create_user_id, update_datetime, update_user_id,
- version, active, active_datetime, non_active_datetime)
- SELECT vTenantId, A.partner_id, vNo, B.top_in_days::integer,
- vNo, vEmpty, vEmpty, vEmptyId,
- vDatetime, vUserId, vDatetime, vUserId,
- 0, vYes, vDatetime, vSpaceValue
- FROM tt_ul_partner A
- INNER JOIN tt_ul_customer_unique B ON A.partner_code = B.kode_pelanggan
- WHERE A.upload_header_id = pUploadHeaderId
- AND A.upload_header_id = B.upload_header_id
- AND A.action_type = vActionInsert;
- END IF;
- /**
- * Membuatkan data policy untuk OU yang belum ada pada data policy OU
- */
- WITH insert_data_policy AS (
- INSERT INTO t_policy(
- tenant_id, policy_code, policy_name, create_datetime,
- create_user_id, update_datetime, update_user_id, version, active,
- active_datetime, non_active_datetime)
- SELECT vTenantId, f_get_ou_code(vOuCompanyId)||'_'||vDatetime, f_get_ou_name(vOuCompanyId), vDatetime,
- vUserId, vDatetime, vUserId, 0, vYes,
- vDatetime, vSpaceValue
- WHERE NOT EXISTS (
- SELECT 1 FROM t_policy_ou Z
- WHERE Z.ou_id = vOuCompanyId
- )
- RETURNING *
- )
- INSERT INTO t_policy_ou(
- policy_id, ou_id, create_datetime, create_user_id,
- update_datetime, update_user_id, version, active, active_datetime,
- non_active_datetime)
- SELECT A.policy_id, vOuCompanyId, vDatetime, vUserId,
- vDatetime, vUserId, 0, vYes, vDatetime,
- vSpaceValue
- FROM insert_data_policy A;
- /**
- * Membuat / Update data user
- */
- -- Update default role data user jika data user sudah ada
- UPDATE t_user A SET
- role_default_id=vDefaultRoleId,
- version=A.version+1,
- update_datetime=vDatetime,
- update_user_id=vUserId
- FROM tt_ul_partner B
- WHERE B.upload_header_id = pUploadHeaderId
- AND A.username = B.partner_code
- AND A.role_default_id <> vDefaultRoleId;
- -- Buat data user role untuk user existing
- INSERT INTO t_user_role(
- user_id, role_id, policy_id, create_datetime, create_user_id,
- update_datetime, update_user_id, version, active, active_datetime,
- non_active_datetime)
- SELECT A.user_id, vDefaultRoleId, A.policy_default_id, vDatetime, vUserId,
- vDatetime, vUserId, 0, vYes, vDatetime,
- vSpaceValue
- FROM t_user A
- WHERE EXISTS (
- SELECT 1 FROM tt_ul_partner Z WHERE Z.upload_header_id = pUploadHeaderId AND A.username = Z.partner_code
- )
- AND NOT EXISTS (
- SELECT 1 FROM t_user_role Z
- WHERE Z.role_id = vDefaultRoleId
- AND A.user_id = Z.user_id
- );
- -- Buat data user jika belum ada data user
- WITH insert_data_user AS (
- INSERT INTO t_user(
- tenant_id, username, email, fullname, password, phone,
- role_default_id, private_key, create_datetime, create_user_id,
- update_datetime, update_user_id, version, active, active_datetime,
- non_active_datetime, ou_default_id, policy_default_id)
- SELECT vTenantId, A.partner_code, vEmpty, CASE WHEN trim(A.partner_ext_name) <> vEmpty THEN A.partner_ext_name ELSE substr(A.partner_name,1,255) END, A.partner_code||'_'||vDatetime, vEmpty,
- vDefaultRoleId, md5(A.partner_code||vDatetime), vDatetime, vUserId,
- vDatetime, vUserId, 0, vYes, vDatetime,
- vSpaceValue, vOuCompanyId, f_get_policy_ou_for_upload(vOuCompanyId)
- FROM tt_ul_partner A
- WHERE A.upload_header_id = pUploadHeaderId
- AND NOT EXISTS (
- SELECT 1 FROM t_user Z WHERE Z.username = A.partner_code
- )
- RETURNING *
- ), insert_data_user_prop AS (
- -- Buat data user props
- INSERT INTO t_user_props(
- prop_key, user_id, prop_val, create_datetime, create_user_id,
- update_datetime, update_user_id, version)
- SELECT 'count.login', A.user_id, 0::character varying, vDatetime, vUserId,
- vDatetime, vUserId, 0
- FROM insert_data_user A
- UNION ALL
- SELECT 'last.login', A.user_id, ' ', vDatetime, vUserId,
- vDatetime, vUserId, 0
- FROM insert_data_user A
- )
- -- Buat data user role
- INSERT INTO t_user_role(
- user_id, role_id, policy_id, create_datetime, create_user_id,
- update_datetime, update_user_id, version, active, active_datetime,
- non_active_datetime)
- SELECT A.user_id, A.role_default_id, A.policy_default_id, vDatetime, vUserId,
- vDatetime, vUserId, 0, vYes, vDatetime,
- vSpaceValue
- FROM insert_data_user A;
- -- RESET table temp
- DELETE FROM tt_ul_partner WHERE upload_header_id = pUploadHeaderId;
- END IF;
- -- RESET TEMP TABLE
- DELETE FROM tt_ul_customer_unique WHERE upload_header_id = pUploadHeaderId;
- DELETE FROM tt_ul_customer_partner_address WHERE upload_header_id = pUploadHeaderId;
- DELETE FROM tt_ul_customer_partner_cp WHERE upload_header_id = pUploadHeaderId;
- ELSE
- -- JIKA DEFAULT ROLE TIDAK DITEMUKAN, MAKA UPLOAD AKA DIGAGALKAN
- UPDATE ul_customer A
- SET status = vFail,
- update_status = vActionError,
- message = 'Default Role is not registered on system'
- WHERE upload_header_id = pUploadHeaderId;
- -- Hitung jumlah data yang FAIL
- SELECT COUNT(1) INTO vCountFail
- FROM ul_customer
- WHERE upload_header_id = pUploadHeaderId
- AND status = vFail;
- END IF;
- RETURN vCountFail;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement