ul_upload_customer

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;
  /