ul_upload_partner BACKUP

CREATE OR REPLACE FUNCTION ul_upload_partner(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;

    vEmptyId                bigint := -99;
    vCountFail              bigint := 0;

    vComboIdPriceLevel      character varying := 'PRICELEVEL';
    vComboIdPartnerRank     character varying := 'PARTNERRANK';
    vComboIdCountry         character varying := 'COUNTRY';
    vComboIdLineOfBusiness  character varying := 'LINEOFBUSINESS';
    vComboIdIndustryType    character varying := 'INDUSTRYTYPE';
    vComboIdTaxCode         character varying := 'TAXCODE';
    vComboIdDepartment      character varying := 'DEPARTMENT';
    vComboIdJobLevel        character varying := 'JOBLEVEL';
    vComboIdYesno           character varying := 'YESNO';
    vComboIdBank            character varying := 'BANK';
    vComboIdCurrency        character varying := 'CURRENCY';
    vComboIdDay             character varying := 'DAY';
    vComboIdDate            character varying := 'DATE';
    vComboIdPaymentMode     character varying := 'PAYMENTMODE';

    vGroupPartnerEmployee   character varying := 'E';
    vGroupPartnerSupplier   character varying := 'S';
    vGroupPartnerCustomer   character varying := 'C';
    vTypePartnerSupplierInternal    character varying := 'SUPI';
    vTypePartnerCustomerInternal    character varying := 'CUSI';

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

    -- Update kolom yang kosong ke nilai default
    UPDATE ul_import_data_partner A
    SET garis_bujur = '0'
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.garis_bujur) = vEmpty;

    UPDATE ul_import_data_partner A
    SET garis_lintang = '0'
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.garis_lintang) = vEmpty;

    -- Validasi
    -- # Kode partner harus diisi, dan Max character kode partner 50
    -- # Kode partner harus belum ada dalam sistem
    -- # Nama partner harus diisi, Max character nama partner adalah 1024
    -- # Kode kategori wajib diisi, Kode kategori harus terdaftar disystem (m_ctgr_partner)
    -- # Nama eksternal jika diisi, maka max characternya harus 100
    -- # Price level wajib diisi & harus terdaftar disystem (m_level_price)
    -- # 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)
    -- # Flg Holding wajib diisi dengan nilai Y/N
    -- # Jika diisi, Kode Partner Holding tidak boleh dirirnya sendiri
    -- # Jika diisi, Kode Partner Holding harus ada dalam master partner dengan flag holding = Y atau file upload
    -- # 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 City adalah 100 jika diisi
    -- # Max character Zip Code adalah 100 jika diisi
    -- # Max character State or Province adalah 100 jika diisi
    -- # Country wajib diisi, dan harus terdaftar disystem (t_combo_value dengan combo id = COUNTRY)
    -- # 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
    -- # Mailing Addr Wajib diisi dengan nilai Y/N
    -- # Shipping Addr Wajib diisi dengan nilai Y/N
    -- # Billing Addr Wajib diisi dengan nilai Y/N
    -- # Others Addr Wajib diisi dengan nilai Y/N
    -- # Validasi tidak boleh ada partner code yang duplikat dalam 1 csv

    -- # PARTNER CP
    -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
    -- # Nama CP, Pekerjaan CP, Kode Departemen CP, Kode Level Jabatan CP, Flag Notifikasi AP, Flag Notifikasi AR, Flag Responsibility
    -- # Nama CP wajib diisi, dan max character Nama CP adalah 100
    -- # Pekerjaan CP wajib diisi, dan max character Pekerjaan CP adalah 100
    -- # Kode Departemen CP wajib diisi, dan harus ada dalam combo DEPARTMENT
    -- # Kode Level Jabatan CP wajib diisi, dan harus ada dalam combo JOBLEVEL
    -- # Jika diisi, max character Email CP adalah 100 dan harus dalam format email
    -- # Flag Notifikasi AP wajib diisi, dan harus ada dalam combo YESNO
    -- # Flag Notifikasi AR wajib diisi, dan harus ada dalam combo YESNO
    -- # Jika diisi, max character Telepon 1 CP adalah 100
    -- # Jika diisi, max character Telepon 2 CP adalah 100
    -- # Jika diisi, max character Telepon Eksternal 1 CP adalah 100
    -- # Jika diisi, max character Telepon Eksternal 2 CP adalah 100
    -- # Jika diisi, max character Ponsel 1 CP adalah 100
    -- # Jika diisi, max character Ponsel 2 CP adalah 100
    -- # Jika diisi, max character Fax 1 CP adalah 100
    -- # Jika diisi, max character Fax 2 CP adalah 100
    -- # Flag Responsible wajib diisi, dan harus ada dalam combo YESNO

    -- # PARTNER NPWP
    -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
    -- # Nomor NPWP, Nama NPWP, Negara NPWP, Flag PKP
    -- # Nomor NPWP wajib diisi, dan max character Nomor NPWP adalah 50
    -- # Nama NPWP wajib diisi, dan max character Nama NPWP adalah 1024
    -- # Jika diisi, Tanggal NPWP harus dalam format tanggal
    -- # Jika diisi, max character Alamat 1 NPWP adalah 100
    -- # Jika diisi, max character Alamat 2 NPWP adalah 100
    -- # Jika diisi, max character Alamat 3 NPWP adalah 100
    -- # Jika diisi, max character Kota NPWP adalah 100
    -- # Jika diisi, max character Kode Pos NPWP adalah 100
    -- # Jika diisi, max character Telepon 1 NPWP adalah 100
    -- # Jika diisi, max character Telepon 2 NPWP adalah 100
    -- # Jika diisi, max character Fax 1 NPWP adalah 100
    -- # Jika diisi, max character Fax 2 NPWP adalah 100
    -- # Negara NPWP wajib diisi, dan Negara NPWP harus ada dalam combo COUNTRY
    -- # Flag PKP wajib diisi, dan Flag PKP harus ada dalam combo YESNO
    -- # Validasi tidak boleh ada Nomor NPWP yang duplikat dalam 1 csv

    -- # PARTNER BANK
    -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
    -- # Kode Bank, Nomor Rekening, Kode Valuta Rekening, Nama Rekening 1
    -- # Kode Bank wajib diisi, dan harus ada dalam combo BANK
    -- # Nomor Rekening wajib diisi, max character Nomor Rekening adalah 50
    -- # Kode Valuta Rekening wajib diisi, dan harus ada dalam combo CURRENCY
    -- # Nama Rekening 1 wajib diisi, max character Nama Rekening 1 adalah 100
    -- # Jika diisi, max character Nama Rekening 2 adalah 100
    -- # Validasi tidak boleh ada Kode Partner - Kode Bank - Nomor Rekening yang duplikat dalam 1 csv

    -- # PARTNER REL
    -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
    -- # Kode Partner Relasi, Flag Tagihan, Flag Pengiriman
    -- # Data Manage As Customer atas partner harus diisi
    -- # Kode Partner Relasi wajib diisi, dan harus merupakkan partner dengan tipe customer,
    --      bukan employee, dan bukan dirinya sendiri
    -- # Flag Tagihan wajib diisi, dan Flag Tagihan harus ada dalam combo YESNO
    -- # Flag Pengiriman wajib diisi, dan Flag Pengiriman harus ada dalam combo YESNO

    -- # PARTNER AS SUPPLIER
    -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
    -- # Kode Tipe Supplier, Kode Valuta Hutang, Batas Jumlah Hutang, Batas Pembayaran Supplier, Lama Pengiriman
    -- # Kode Tipe Supplier wajib diisi, dan harus ada dalam sistem dengan group partner S, dan bukan Internal
    -- # Kode Valuta Hutang wajib diisi, dan harus ada dalam combo CURRENCY
    -- # Batas Jumlah Hutang wajib diisi, harus berupa angka dan harus > 0
    -- # Batas Pembayaran Supplier wajib diisi, harus berupa angka dan harus >= 0
    -- # Lama Pengiriman wajib diisi, harus berupa angka dan harus >= 0

    -- # PARTNER AS CUSTOMER
    -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
    -- # Kode Tipe Customer, Kode Valuta Piutang, Batas Jumlah Piutang, Flag Komisi, Flag Referensi Invoice,
    --      Batas Pembayaran Customer, Cara Pembayaran Customer, Hari Pembayaran Customer,
    --      Tanggal Pembayaran Customer, Diskon Reguler
    -- # Kode Tipe Customer wajib diisi, dan harus ada dalam sistem dengan group partner C
    -- # Kode Valuta Piutang wajib diisi, dan harus ada dalam combo CURRENCY
    -- # Batas Jumlah Piutang wajib diisi, harus berupa angka dan harus > 0
    -- # Flag Komisi wajib diisi, dan harus ada dalam combo YESNO
    -- # Flag Referensi Invoice wajib diisi, dan harus ada dalam combo YESNO
    -- # Batas Pembayaran Customer wajib diisi, harus berupa angka dan harus >= 0
    -- # Cara Pembayaran Customer wajib diisi, dan harus ada dalam combo PAYMENTMODE
    -- # Kode Bank Customer dan Nomor Rekening Customer harus diisi sekaligus atau tidak sama sekali
    -- # Jika Kode Bank Customer dan Nomor Rekening Customer diisi, harus sama dengan Kode Bank dan Nomor Rekening
    -- # Hari Pembayaran Customer wajib diisi, dan harus ada dalam combo DAY
    -- # Tanggal Pembayaran Customer wajib diisi, dan harus ada dalam combo DATE
    -- # Diskon Reguler wajib diisi, harus berupa angka dan harus bernilai di antara 0 s/d 100


    -- # Kode partner harus diisi, dan Max character kode partner 50
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Partner harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_partner) = vEmpty;

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Partner maksimal 50 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.kode_partner) > 50;

    -- # Kode partner harus belum ada dalam sistem
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Partner sudah ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND EXISTS (
            SELECT 1
            FROM m_partner Z
            WHERE Z.partner_code = A.kode_partner
                AND Z.tenant_id = vTenantId
        );

    -- # Nama partner harus diisi, Max character nama partner adalah 1024
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Nama Partner harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.nama_partner) = vEmpty;

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Nama Partner maksimal 1024 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.nama_partner) > 1024;

    -- # Kode kategori wajib diisi, Kode kategori harus terdaftar disystem (m_ctgr_partner)
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Kategori Partner harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_kategori_partner) = vEmpty;

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Kategori Partner tidak ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_kategori_partner) <> vEmpty
        AND NOT EXISTS(
            SELECT 1 FROM m_ctgr_partner Z WHERE Z.ctgr_partner_code = A.kode_kategori_partner AND Z.tenant_id = vTenantId
        );

    -- # Nama external jika diisi, maka max characternya harus 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Nama Eksternal maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.nama_eksternal) <> vEmpty
        AND length(A.nama_eksternal) > 100;

    -- # Price level wajib diisi & harus terdaftar disystem (m_level_price)
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Level Harga harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.level_harga) = vEmpty;

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Level Harga tidak ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.level_harga) <> vEmpty
        AND NOT EXISTS(
            SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = vComboIdPriceLevel AND Z.code = A.level_harga
        );

    -- # Rank harus diisi & harus terdaftar disystem (t_combo_value dengan combo id = PARTNERRANK)
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Rank harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.ranking) = vEmpty;

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Rank tidak ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.ranking) <> vEmpty
        AND NOT EXISTS(
            SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = vComboIdPartnerRank AND Z.code = A.ranking
        );

    -- # Line of business jika diisi, maka harus terdaftar disystem (t_combo_value dengan combo id = LINEOFBUSINESS)
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Bidang Usaha tidak ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.bidang_usaha) <> vEmpty
        AND NOT EXISTS(
            SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = vComboIdLineOfBusiness AND Z.code = A.bidang_usaha
        );

    -- # Industry Type wajib diisi & harus terdaftar disystem (t_combo_value dengan combo id = INDUSTRYTYPE)
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Jenis Industri harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.jenis_industri) = vEmpty;

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Jenis industri tidak ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.jenis_industri) <> vEmpty
        AND NOT EXISTS(
            SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = vComboIdIndustryType AND Z.code = A.jenis_industri
        );

    -- # NPWP Status wajib diisi & harus terdaftar disystem (t_combo_value dengan combo id = TAXCODE)
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Status NPWP harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_status_npwp) = vEmpty;

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Status NPWP tidak ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_status_npwp) <> vEmpty
        AND NOT EXISTS(
            SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = vComboIdTaxCode AND Z.code = A.kode_status_npwp
        );

    -- # Flg Holding wajib diisi dengan nilai Y/N
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Flag Holding hanya dapat berisi Y atau N, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND A.flag_holding NOT IN (vYes, vNo);

    -- # Jika diisi, Kode Partner Holding tidak boleh dirinya sendiri
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Partner Holding harus berbeda dengan Kode Partner, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_partner_holding) <> vEmpty
        AND A.kode_partner_holding = A.kode_partner;

    -- # Jika diisi, Kode Partner Holding harus ada dalam master partner dengan flag holding = Y atau file upload
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Partner Holding tidak ada dalam sistem ataupun CSV, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_partner_holding) <> vEmpty
        AND A.kode_partner_holding <> A.kode_partner
        AND NOT EXISTS(
                SELECT 1
                FROM m_partner Z
                WHERE Z.flg_holding = vYes
                AND Z.tenant_id = vTenantId
                AND Z.active = vYes
                AND Z.partner_code = A.kode_partner_holding
        ) AND NOT EXISTS(
                SELECT 1
                FROM ul_import_data_partner Z
                WHERE Z.upload_header_id = A.upload_header_id
                AND Z.flag_holding = vYes
                AND Z.kode_partner = A.kode_partner_holding
        );

    -- # Address Desc wajib diisi apabila kolom partner address lainya ada yang diisi & Max character Address Desc adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Deskripsi Alamat harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_partner) <> vEmpty
        AND TRIM(A.deskripsi_alamat) = vEmpty;

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Deskripsi Alamat maksima 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.deskripsi_alamat) <> vEmpty
        AND length(A.deskripsi_alamat) > 100;

    -- # Address 1 Wajib diisi & Max character Address 1 adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Alamat 1 wajib diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.alamat_1) = vEmpty;

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Alamat 1 maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.alamat_1) > 100;

    -- # Max character Address 2 adalah 100 jika diisi
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Alamat 2 maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.alamat_2) <> vEmpty
        AND length(A.alamat_2) > 100;

    -- # Max character Address 3 adalah 100 jika diisi
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Alamat 3 maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.alamat_3) <> vEmpty
        AND length(A.alamat_3) > 100;

    -- # Max character City adalah 100 jika diisi
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kota harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kota) = vEmpty;

    -- # Max character Zip Code adalah 100 jika diisi
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Pos maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.kode_pos) > 100;

        -- # Max character State or Province adalah 100 jika diisi
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Provinsi maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.provinsi) > 100;

    -- # Country wajib diisi, dan harus terdaftar disystem (t_combo_value dengan combo id = COUNTRY)
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Negara harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_negara) = vEmpty;

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Negara tidak ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_negara) <> vEmpty
        AND NOT EXISTS(
            SELECT 1 FROM t_combo_value Z WHERE Z.combo_id = vComboIdCountry AND Z.code = A.kode_negara
        );

    -- # Longitude harus numeric jika diisi, jika dikosongi maka di anggap 0
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Garis Bujur harus numerik, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.garis_bujur) <> vEmpty
        AND NOT is_numeric(A.garis_bujur);

    -- # Latitude harus numeric jika diisi, jika dikosongi maka di anggap 0
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Garis Lintang harus numerik, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.garis_lintang) <> vEmpty
        AND NOT is_numeric(A.garis_lintang);

    -- # Max character Phone 1 adalah 100, jika diisi
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Telepon 1 maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.telepon_1) <> vEmpty
        AND length(A.telepon_1) > 100;

    -- # Max character Phone 2 adalah 100, jika diisi
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Telepon 2 maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.telepon_2) <> vEmpty
        AND length(A.telepon_2) > 100;

    -- # Max character Fax 1 adalah 100, jika diisi
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Fax 1 maksimal 100 karakter, '
    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_import_data_partner A
    SET status = vFail,
        message = message || 'Fax 2 maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.fax_2) <> vEmpty
        AND length(A.fax_2) > 100;

    -- # Mailing Addr wajib diisi dengan nilai Y/N
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Flag Mailing hanya dapat berisi Y atau N, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND A.flag_mailing NOT IN (vYes, vNo);

    -- # Shipping Addr Wajib diisi dengan nilai Y/N
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Flag Shipping hanya dapat berisi Y atau N, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND A.flag_shipping NOT IN (vYes, vNo);

    -- # Billing Addr Wajib diisi dengan nilai Y/N
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Flag Billing hanya dapat berisi Y atau N, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND A.flag_billing NOT IN (vYes, vNo);

    -- # Others Addr Wajib diisi dengan nilai Y/N
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Flag Others hanya dapat berisi Y atau N, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND A.flag_others NOT IN (vYes, vNo);

    -- # Validasi tidak boleh ada partner code yang duplikat dalam 1 csv
    WITH duplicate_data_partner AS (
        SELECT A.kode_partner
        FROM ul_import_data_partner A
        WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_partner) <> vEmpty
        GROUP BY A.kode_partner
        HAVING COUNT(1) > 1
    )
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Partner duplikat, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND EXISTS (
            SELECT 1 FROM duplicate_data_partner B WHERE A.kode_partner = B.kode_partner
        );

    -- # PARTNER CP
    -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
    -- # Nama CP, Pekerjaan CP, Kode Departemen CP, Kode Level Jabatan CP, Flag Notifikasi AP, Flag Notifikasi AR, Flag Responsible

    UPDATE ul_import_data_partner A
    SET flg_cp = vYes
    WHERE A.upload_header_id = pUploadHeaderId
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty
            OR TRIM(A.email_cp) <> vEmpty
            OR TRIM(A.telepon_1_cp) <> vEmpty
            OR TRIM(A.telepon_2_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
            OR TRIM(A.ponsel_1_cp) <> vEmpty
            OR TRIM(A.ponsel_2_cp) <> vEmpty
            OR TRIM(A.fax_1_cp) <> vEmpty
            OR TRIM(A.fax_2_cp) <> vEmpty);

    -- # Nama CP wajib diisi, dan max character Nama CP adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Nama CP harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.nama_cp) = vEmpty
        AND (TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty
            OR TRIM(A.email_cp) <> vEmpty
            OR TRIM(A.telepon_1_cp) <> vEmpty
            OR TRIM(A.telepon_2_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
            OR TRIM(A.ponsel_1_cp) <> vEmpty
            OR TRIM(A.ponsel_2_cp) <> vEmpty
            OR TRIM(A.fax_1_cp) <> vEmpty
            OR TRIM(A.fax_2_cp) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Nama CP maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.nama_cp) > 100
        AND (TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty
            OR TRIM(A.email_cp) <> vEmpty
            OR TRIM(A.telepon_1_cp) <> vEmpty
            OR TRIM(A.telepon_2_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
            OR TRIM(A.ponsel_1_cp) <> vEmpty
            OR TRIM(A.ponsel_2_cp) <> vEmpty
            OR TRIM(A.fax_1_cp) <> vEmpty
            OR TRIM(A.fax_2_cp) <> vEmpty);

    -- # Pekerjaan CP wajib diisi, dan max character Pekerjaan CP adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Pekerjaan CP harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.pekerjaan_cp) = vEmpty
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty
            OR TRIM(A.email_cp) <> vEmpty
            OR TRIM(A.telepon_1_cp) <> vEmpty
            OR TRIM(A.telepon_2_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
            OR TRIM(A.ponsel_1_cp) <> vEmpty
            OR TRIM(A.ponsel_2_cp) <> vEmpty
            OR TRIM(A.fax_1_cp) <> vEmpty
            OR TRIM(A.fax_2_cp) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Pekerjaan CP maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.pekerjaan_cp) > 100
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty
            OR TRIM(A.email_cp) <> vEmpty
            OR TRIM(A.telepon_1_cp) <> vEmpty
            OR TRIM(A.telepon_2_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
            OR TRIM(A.ponsel_1_cp) <> vEmpty
            OR TRIM(A.ponsel_2_cp) <> vEmpty
            OR TRIM(A.fax_1_cp) <> vEmpty
            OR TRIM(A.fax_2_cp) <> vEmpty);

    -- # Kode Departemen CP wajib diisi, dan harus ada dalam combo DEPARTMENT
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Departemen CP harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_departemen_cp) = vEmpty
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty
            OR TRIM(A.email_cp) <> vEmpty
            OR TRIM(A.telepon_1_cp) <> vEmpty
            OR TRIM(A.telepon_2_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
            OR TRIM(A.ponsel_1_cp) <> vEmpty
            OR TRIM(A.ponsel_2_cp) <> vEmpty
            OR TRIM(A.fax_1_cp) <> vEmpty
            OR TRIM(A.fax_2_cp) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Departemen CP tidak ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_departemen_cp) <> vEmpty
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty
            OR TRIM(A.email_cp) <> vEmpty
            OR TRIM(A.telepon_1_cp) <> vEmpty
            OR TRIM(A.telepon_2_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
            OR TRIM(A.ponsel_1_cp) <> vEmpty
            OR TRIM(A.ponsel_2_cp) <> vEmpty
            OR TRIM(A.fax_1_cp) <> vEmpty
            OR TRIM(A.fax_2_cp) <> vEmpty
        ) AND NOT EXISTS(
            SELECT 1
            FROM t_combo_value Z
            WHERE Z.combo_id = vComboIdDepartment
                AND Z.code = A.kode_departemen_cp
        );

    -- # Kode Level Jabatan CP wajib diisi, dan harus ada dalam combo JOBLEVEL
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Level Jabatan CP harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_level_jabatan_cp) = vEmpty
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty
            OR TRIM(A.email_cp) <> vEmpty
            OR TRIM(A.telepon_1_cp) <> vEmpty
            OR TRIM(A.telepon_2_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
            OR TRIM(A.ponsel_1_cp) <> vEmpty
            OR TRIM(A.ponsel_2_cp) <> vEmpty
            OR TRIM(A.fax_1_cp) <> vEmpty
            OR TRIM(A.fax_2_cp) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Level Jabatan CP tidak ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_level_jabatan_cp) <> vEmpty
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty
            OR TRIM(A.email_cp) <> vEmpty
            OR TRIM(A.telepon_1_cp) <> vEmpty
            OR TRIM(A.telepon_2_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
            OR TRIM(A.ponsel_1_cp) <> vEmpty
            OR TRIM(A.ponsel_2_cp) <> vEmpty
            OR TRIM(A.fax_1_cp) <> vEmpty
            OR TRIM(A.fax_2_cp) <> vEmpty
        ) AND NOT EXISTS(
            SELECT 1
            FROM t_combo_value Z
            WHERE Z.combo_id = vComboIdJobLevel
                AND Z.code = A.kode_level_jabatan_cp
        );

    -- # Jika diisi, max character Email CP adalah 100 dan harus dalam format email
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Email CP maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.email_cp) > 100
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Email CP tidak dalam format email, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND NOT is_email(A.email_cp)
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty);

    -- # Flag Notifikasi AP wajib diisi, dan harus ada dalam combo YESNO
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Flag Notifikasi AP harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.flag_notifikasi_ap) = vEmpty
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty
            OR TRIM(A.email_cp) <> vEmpty
            OR TRIM(A.telepon_1_cp) <> vEmpty
            OR TRIM(A.telepon_2_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
            OR TRIM(A.ponsel_1_cp) <> vEmpty
            OR TRIM(A.ponsel_2_cp) <> vEmpty
            OR TRIM(A.fax_1_cp) <> vEmpty
            OR TRIM(A.fax_2_cp) <> vEmpty
        );

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Flag Notifikasi AP hanya dapat berisi Y atau N, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.flag_notifikasi_ap) <> vEmpty
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty
            OR TRIM(A.email_cp) <> vEmpty
            OR TRIM(A.telepon_1_cp) <> vEmpty
            OR TRIM(A.telepon_2_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
            OR TRIM(A.ponsel_1_cp) <> vEmpty
            OR TRIM(A.ponsel_2_cp) <> vEmpty
            OR TRIM(A.fax_1_cp) <> vEmpty
            OR TRIM(A.fax_2_cp) <> vEmpty
        ) AND NOT EXISTS(
            SELECT 1
            FROM t_combo_value Z
            WHERE Z.combo_id = vComboIdYesNo
                AND Z.code = A.flag_notifikasi_ap
        );

    -- # Flag Notifikasi AR wajib diisi, dan harus ada dalam combo YESNO
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Flag Notifikasi AR harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.flag_notifikasi_ar) = vEmpty
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty
            OR TRIM(A.email_cp) <> vEmpty
            OR TRIM(A.telepon_1_cp) <> vEmpty
            OR TRIM(A.telepon_2_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
            OR TRIM(A.ponsel_1_cp) <> vEmpty
            OR TRIM(A.ponsel_2_cp) <> vEmpty
            OR TRIM(A.fax_1_cp) <> vEmpty
            OR TRIM(A.fax_2_cp) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Flag Notifikasi AR hanya dapat berisi Y atau N, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.flag_notifikasi_ar) <> vEmpty
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty
            OR TRIM(A.email_cp) <> vEmpty
            OR TRIM(A.telepon_1_cp) <> vEmpty
            OR TRIM(A.telepon_2_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
            OR TRIM(A.ponsel_1_cp) <> vEmpty
            OR TRIM(A.ponsel_2_cp) <> vEmpty
            OR TRIM(A.fax_1_cp) <> vEmpty
            OR TRIM(A.fax_2_cp) <> vEmpty
        ) AND NOT EXISTS(
            SELECT 1
            FROM t_combo_value Z
            WHERE Z.combo_id = vComboIdYesNo
                AND Z.code = A.flag_notifikasi_ar
        );

    -- # Jika diisi, max character Telepon 1 CP adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Telepon 1 CP maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.telepon_1_cp) > 100
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty);

    -- # Jika diisi, max character Telepon 2 CP adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Telepon 2 CP maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.telepon_2_cp) > 100
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty);

    -- # Jika diisi, max character Telepon Eksternal 1 CP adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Telepon Eksternal 1 CP maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.telepon_eksternal_1_cp) > 100
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty);

    -- # Jika diisi, max character Telepon Eksternal 2 CP adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Telepon Eksternal 2 CP maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.telepon_eksternal_2_cp) > 100
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty);

    -- # Jika diisi, max character Ponsel 1 CP adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Ponsel 1 CP maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.ponsel_1_cp) > 100
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty);

    -- # Jika diisi, max character Ponsel 2 CP adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Ponsel 2 CP maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.ponsel_2_cp) > 100
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty);

    -- # Jika diisi, max character Fax 1 CP adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Fax 1 CP maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.fax_1_cp) > 100
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty);

    -- # Jika diisi, max character Fax 2 CP adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Fax 2 CP maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.fax_2_cp) > 100
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.flag_responsible) <> vEmpty);

    -- # Flag Responsible wajib diisi, dan harus ada dalam combo YESNO
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Flag Responsible harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.flag_responsible) = vEmpty
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.email_cp) <> vEmpty
            OR TRIM(A.telepon_1_cp) <> vEmpty
            OR TRIM(A.telepon_2_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
            OR TRIM(A.ponsel_1_cp) <> vEmpty
            OR TRIM(A.ponsel_2_cp) <> vEmpty
            OR TRIM(A.fax_1_cp) <> vEmpty
            OR TRIM(A.fax_2_cp) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Flag Responsible hanya dapat berisi Y atau N, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.flag_responsible) <> vEmpty
        AND (TRIM(A.nama_cp) <> vEmpty
            OR TRIM(A.pekerjaan_cp) <> vEmpty
            OR TRIM(A.kode_departemen_cp) <> vEmpty
            OR TRIM(A.kode_level_jabatan_cp) <> vEmpty
            OR TRIM(A.flag_notifikasi_ap) <> vEmpty
            OR TRIM(A.flag_notifikasi_ar) <> vEmpty
            OR TRIM(A.email_cp) <> vEmpty
            OR TRIM(A.telepon_1_cp) <> vEmpty
            OR TRIM(A.telepon_2_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_1_cp) <> vEmpty
            OR TRIM(A.telepon_eksternal_2_cp) <> vEmpty
            OR TRIM(A.ponsel_1_cp) <> vEmpty
            OR TRIM(A.ponsel_2_cp) <> vEmpty
            OR TRIM(A.fax_1_cp) <> vEmpty
            OR TRIM(A.fax_2_cp) <> vEmpty
        ) AND NOT EXISTS(
            SELECT 1
            FROM t_combo_value Z
            WHERE Z.combo_id = vComboIdYesNo
                AND Z.code = A.flag_responsible
        );

    -- # PARTNER NPWP
    -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
    -- # Nomor NPWP, Nama NPWP, Negara NPWP, Flag PKP

    UPDATE ul_import_data_partner A
    SET flg_npwp = vYes
    WHERE A.upload_header_id = pUploadHeaderId
        AND (TRIM(A.nomor_npwp) <> vEmpty
            OR TRIM(A.nama_npwp) <> vEmpty
            OR TRIM(A.negara_npwp) <> vEmpty
            OR TRIM(A.flg_pkp) <> vEmpty
            OR TRIM(A.tanggal_npwp) <> vEmpty
            OR TRIM(A.alamat_1_npwp) <> vEmpty
            OR TRIM(A.alamat_2_npwp) <> vEmpty
            OR TRIM(A.alamat_3_npwp) <> vEmpty
            OR TRIM(A.kota_npwp) <> vEmpty
            OR TRIM(A.kode_pos_npwp) <> vEmpty
            OR TRIM(A.telepon_1_npwp) <> vEmpty
            OR TRIM(A.telepon_2_npwp) <> vEmpty
            OR TRIM(A.fax_1_npwp) <> vEmpty
            OR TRIM(A.fax_2_npwp) <> vEmpty);

    -- # Nomor NPWP wajib diisi, dan max character Nomor NPWP adalah 50
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Nomor NPWP harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.nomor_npwp) = vEmpty
        AND (TRIM(A.nama_npwp) <> vEmpty
            OR TRIM(A.negara_npwp) <> vEmpty
            OR TRIM(A.flg_pkp) <> vEmpty
            OR TRIM(A.tanggal_npwp) <> vEmpty
            OR TRIM(A.alamat_1_npwp) <> vEmpty
            OR TRIM(A.alamat_2_npwp) <> vEmpty
            OR TRIM(A.alamat_3_npwp) <> vEmpty
            OR TRIM(A.kota_npwp) <> vEmpty
            OR TRIM(A.kode_pos_npwp) <> vEmpty
            OR TRIM(A.telepon_1_npwp) <> vEmpty
            OR TRIM(A.telepon_2_npwp) <> vEmpty
            OR TRIM(A.fax_1_npwp) <> vEmpty
            OR TRIM(A.fax_2_npwp) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Nomor NPWP maksimal 50 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.nomor_npwp) > 50
        AND (TRIM(A.nama_npwp) <> vEmpty
            OR TRIM(A.negara_npwp) <> vEmpty
            OR TRIM(A.flg_pkp) <> vEmpty
            OR TRIM(A.tanggal_npwp) <> vEmpty
            OR TRIM(A.alamat_1_npwp) <> vEmpty
            OR TRIM(A.alamat_2_npwp) <> vEmpty
            OR TRIM(A.alamat_3_npwp) <> vEmpty
            OR TRIM(A.kota_npwp) <> vEmpty
            OR TRIM(A.kode_pos_npwp) <> vEmpty
            OR TRIM(A.telepon_1_npwp) <> vEmpty
            OR TRIM(A.telepon_2_npwp) <> vEmpty
            OR TRIM(A.fax_1_npwp) <> vEmpty
            OR TRIM(A.fax_2_npwp) <> vEmpty);

    -- # Nama NPWP wajib diisi, dan max character Nama NPWP adalah 1024
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Nama NPWP harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.nama_npwp) = vEmpty
        AND (TRIM(A.nomor_npwp) <> vEmpty
            OR TRIM(A.negara_npwp) <> vEmpty
            OR TRIM(A.flg_pkp) <> vEmpty
            OR TRIM(A.tanggal_npwp) <> vEmpty
            OR TRIM(A.alamat_1_npwp) <> vEmpty
            OR TRIM(A.alamat_2_npwp) <> vEmpty
            OR TRIM(A.alamat_3_npwp) <> vEmpty
            OR TRIM(A.kota_npwp) <> vEmpty
            OR TRIM(A.kode_pos_npwp) <> vEmpty
            OR TRIM(A.telepon_1_npwp) <> vEmpty
            OR TRIM(A.telepon_2_npwp) <> vEmpty
            OR TRIM(A.fax_1_npwp) <> vEmpty
            OR TRIM(A.fax_2_npwp) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Nama NPWP maksimal 1024 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.nama_npwp) > 1024
        AND (TRIM(A.nomor_npwp) <> vEmpty
            OR TRIM(A.negara_npwp) <> vEmpty
            OR TRIM(A.flg_pkp) <> vEmpty
            OR TRIM(A.tanggal_npwp) <> vEmpty
            OR TRIM(A.alamat_1_npwp) <> vEmpty
            OR TRIM(A.alamat_2_npwp) <> vEmpty
            OR TRIM(A.alamat_3_npwp) <> vEmpty
            OR TRIM(A.kota_npwp) <> vEmpty
            OR TRIM(A.kode_pos_npwp) <> vEmpty
            OR TRIM(A.telepon_1_npwp) <> vEmpty
            OR TRIM(A.telepon_2_npwp) <> vEmpty
            OR TRIM(A.fax_1_npwp) <> vEmpty
            OR TRIM(A.fax_2_npwp) <> vEmpty);

    -- # Jika diisi, Tanggal NPWP harus dalam format tanggal
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Tanggal NPWP harus dalam format tanggal, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND NOT is_date(A.tanggal_npwp) > 1024
        AND (TRIM(A.nomor_npwp) <> vEmpty
            OR TRIM(A.nama_npwp) <> vEmpty
            OR TRIM(A.negara_npwp) <> vEmpty
            OR TRIM(A.flg_pkp) <> vEmpty);

    -- # Jika diisi, max character Alamat 1 NPWP adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Alamat 1 NPWP maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.alamat_1_npwp) > 100
        AND (TRIM(A.nomor_npwp) <> vEmpty
            OR TRIM(A.nama_npwp) <> vEmpty
            OR TRIM(A.negara_npwp) <> vEmpty
            OR TRIM(A.flg_pkp) <> vEmpty);

    -- # Jika diisi, max character Alamat 2 NPWP adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Alamat 2 NPWP maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.alamat_2_npwp) > 100
        AND (TRIM(A.nomor_npwp) <> vEmpty
            OR TRIM(A.nama_npwp) <> vEmpty
            OR TRIM(A.negara_npwp) <> vEmpty
            OR TRIM(A.flg_pkp) <> vEmpty);

    -- # Jika diisi, max character Alamat 3 NPWP adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Alamat 3 NPWP maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.alamat_3_npwp) > 100
        AND (TRIM(A.nomor_npwp) <> vEmpty
            OR TRIM(A.nama_npwp) <> vEmpty
            OR TRIM(A.negara_npwp) <> vEmpty
            OR TRIM(A.flg_pkp) <> vEmpty);

    -- # Jika diisi, max character Kota NPWP adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kota NPWP maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.kota_npwp) > 100
        AND (TRIM(A.nomor_npwp) <> vEmpty
            OR TRIM(A.nama_npwp) <> vEmpty
            OR TRIM(A.negara_npwp) <> vEmpty
            OR TRIM(A.flg_pkp) <> vEmpty);

    -- # Jika diisi, max character Kode Pos NPWP adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Pos NPWP maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.kode_pos) > 100
        AND (TRIM(A.nomor_npwp) <> vEmpty
            OR TRIM(A.nama_npwp) <> vEmpty
            OR TRIM(A.negara_npwp) <> vEmpty
            OR TRIM(A.flg_pkp) <> vEmpty);

    -- # Jika diisi, max character Telepon 1 NPWP adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Telepon 1 NPWP maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.telepon_1) > 100
        AND (TRIM(A.nomor_npwp) <> vEmpty
            OR TRIM(A.nama_npwp) <> vEmpty
            OR TRIM(A.negara_npwp) <> vEmpty
            OR TRIM(A.flg_pkp) <> vEmpty);

    -- # Jika diisi, max character Telepon 2 NPWP adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Telepon 2 NPWP maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.telepon_2) > 100
        AND (TRIM(A.nomor_npwp) <> vEmpty
            OR TRIM(A.nama_npwp) <> vEmpty
            OR TRIM(A.negara_npwp) <> vEmpty
            OR TRIM(A.flg_pkp) <> vEmpty);

    -- # Jika diisi, max character Fax 1 NPWP adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Fax 1 NPWP maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.fax_1_npwp) > 100
        AND (TRIM(A.nomor_npwp) <> vEmpty
            OR TRIM(A.nama_npwp) <> vEmpty
            OR TRIM(A.negara_npwp) <> vEmpty
            OR TRIM(A.flg_pkp) <> vEmpty);

    -- # Jika diisi, max character Fax 2 NPWP adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Fax 2 NPWP maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.fax_2_npwp) > 100
        AND (TRIM(A.nomor_npwp) <> vEmpty
            OR TRIM(A.nama_npwp) <> vEmpty
            OR TRIM(A.negara_npwp) <> vEmpty
            OR TRIM(A.flg_pkp) <> vEmpty);

    -- # Negara NPWP wajib diisi, dan Negara NPWP harus ada dalam combo COUNTRY
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Negara NPWP harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.negara_npwp) = vEmpty
        AND (TRIM(A.nomor_npwp) <> vEmpty
            OR TRIM(A.nama_npwp) <> vEmpty
            OR TRIM(A.flg_pkp) <> vEmpty
            OR TRIM(A.tanggal_npwp) <> vEmpty
            OR TRIM(A.alamat_1_npwp) <> vEmpty
            OR TRIM(A.alamat_2_npwp) <> vEmpty
            OR TRIM(A.alamat_3_npwp) <> vEmpty
            OR TRIM(A.kota_npwp) <> vEmpty
            OR TRIM(A.kode_pos_npwp) <> vEmpty
            OR TRIM(A.telepon_1_npwp) <> vEmpty
            OR TRIM(A.telepon_2_npwp) <> vEmpty
            OR TRIM(A.fax_1_npwp) <> vEmpty
            OR TRIM(A.fax_2_npwp) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Negara NPWP tidak ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.negara_npwp) > 1024
        AND (TRIM(A.nomor_npwp) <> vEmpty
            OR TRIM(A.nama_npwp) <> vEmpty
            OR TRIM(A.flg_pkp) <> vEmpty
            OR TRIM(A.tanggal_npwp) <> vEmpty
            OR TRIM(A.alamat_1_npwp) <> vEmpty
            OR TRIM(A.alamat_2_npwp) <> vEmpty
            OR TRIM(A.alamat_3_npwp) <> vEmpty
            OR TRIM(A.kota_npwp) <> vEmpty
            OR TRIM(A.kode_pos_npwp) <> vEmpty
            OR TRIM(A.telepon_1_npwp) <> vEmpty
            OR TRIM(A.telepon_2_npwp) <> vEmpty
            OR TRIM(A.fax_1_npwp) <> vEmpty
            OR TRIM(A.fax_2_npwp) <> vEmpty);

    -- # Flag PKP wajib diisi, dan Flag PKP harus ada dalam combo YESNO
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Flag PKP harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.flg_pkp) = vEmpty
        AND (TRIM(A.nomor_npwp) <> vEmpty
            OR TRIM(A.nama_npwp) <> vEmpty
            OR TRIM(A.negara_npwp) <> vEmpty
            OR TRIM(A.tanggal_npwp) <> vEmpty
            OR TRIM(A.alamat_1_npwp) <> vEmpty
            OR TRIM(A.alamat_2_npwp) <> vEmpty
            OR TRIM(A.alamat_3_npwp) <> vEmpty
            OR TRIM(A.kota_npwp) <> vEmpty
            OR TRIM(A.kode_pos_npwp) <> vEmpty
            OR TRIM(A.telepon_1_npwp) <> vEmpty
            OR TRIM(A.telepon_2_npwp) <> vEmpty
            OR TRIM(A.fax_1_npwp) <> vEmpty
            OR TRIM(A.fax_2_npwp) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Flag PKP hanya dapat berisi Y atau N, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.flg_pkp) > 1024
        AND (TRIM(A.nomor_npwp) <> vEmpty
            OR TRIM(A.nama_npwp) <> vEmpty
            OR TRIM(A.negara_npwp) <> vEmpty
            OR TRIM(A.tanggal_npwp) <> vEmpty
            OR TRIM(A.alamat_1_npwp) <> vEmpty
            OR TRIM(A.alamat_2_npwp) <> vEmpty
            OR TRIM(A.alamat_3_npwp) <> vEmpty
            OR TRIM(A.kota_npwp) <> vEmpty
            OR TRIM(A.kode_pos_npwp) <> vEmpty
            OR TRIM(A.telepon_1_npwp) <> vEmpty
            OR TRIM(A.telepon_2_npwp) <> vEmpty
            OR TRIM(A.fax_1_npwp) <> vEmpty
            OR TRIM(A.fax_2_npwp) <> vEmpty);

    -- # Validasi tidak boleh ada Nomor NPWP yang duplikat dalam 1 csv
    WITH duplicate_data_partner_npwp AS (
        SELECT A.nomor_npwp
        FROM ul_import_data_partner A
        WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.nomor_npwp) <> vEmpty
        GROUP BY A.nomor_npwp
        HAVING COUNT(1) > 1
    )
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Nomor NPWP duplikat, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND EXISTS (
            SELECT 1 FROM duplicate_data_partner_npwp B WHERE A.nomor_npwp = B.nomor_npwp
        );

    -- # PARTNER BANK
    -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
    -- # Kode Bank, Nomor Rekening, Kode Valuta Rekening, Nama Rekening 1

    UPDATE ul_import_data_partner A
    SET flg_bank = vYes
    WHERE A.upload_header_id = pUploadHeaderId
        AND (TRIM(A.kode_bank) <> vEmpty
            OR TRIM(A.nomor_rekening) <> vEmpty
            OR TRIM(A.kode_valuta_rekening) <> vEmpty
            OR TRIM(A.nama_rekening_1) <> vEmpty
            OR TRIM(A.nama_rekening_2) <> vEmpty);

    -- # Kode Bank wajib diisi, dan harus ada dalam combo BANK
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Bank harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_bank) = vEmpty
        AND (TRIM(A.nomor_rekening) <> vEmpty
            OR TRIM(A.kode_valuta_rekening) <> vEmpty
            OR TRIM(A.nama_rekening_1) <> vEmpty
            OR TRIM(A.nama_rekening_2) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Bank tidak ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_bank) <> vEmpty
        AND (TRIM(A.nomor_rekening) <> vEmpty
            OR TRIM(A.kode_valuta_rekening) <> vEmpty
            OR TRIM(A.nama_rekening_1) <> vEmpty
            OR TRIM(A.nama_rekening_2) <> vEmpty
        ) AND NOT EXISTS(
            SELECT 1
            FROM t_combo_value Z
            WHERE Z.combo_id = vComboIdBank
                AND Z.code = A.kode_bank
        );

    -- # Nomor Rekening wajib diisi, max character Nomor Rekening adalah 50
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Nomor Rekening harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.nomor_rekening) = vEmpty
        AND (TRIM(A.kode_bank) <> vEmpty
            OR TRIM(A.kode_valuta_rekening) <> vEmpty
            OR TRIM(A.nama_rekening_1) <> vEmpty
            OR TRIM(A.nama_rekening_2) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Nomor Rekening harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.nomor_rekening) > 50
        AND (TRIM(A.kode_bank) <> vEmpty
            OR TRIM(A.kode_valuta_rekening) <> vEmpty
            OR TRIM(A.nama_rekening_1) <> vEmpty
            OR TRIM(A.nama_rekening_2) <> vEmpty);

    -- # Kode Valuta Rekening wajib diisi, dan harus ada dalam combo CURRENCY
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Valuta Rekening harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_valuta_rekening) = vEmpty
        AND (TRIM(A.kode_bank) <> vEmpty
            OR TRIM(A.nomor_rekening) <> vEmpty
            OR TRIM(A.nama_rekening_1) <> vEmpty
            OR TRIM(A.nama_rekening_2) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Valuta Rekening tidak ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_valuta_rekening) <> vEmpty
        AND (TRIM(A.nomor_rekening) <> vEmpty
            OR TRIM(A.kode_bank) <> vEmpty
            OR TRIM(A.nama_rekening_1) <> vEmpty
            OR TRIM(A.nama_rekening_2) <> vEmpty
        ) AND NOT EXISTS(
            SELECT 1
            FROM t_combo_value Z
            WHERE Z.combo_id = vComboIdCurrency
                AND Z.code = A.kode_valuta_rekening
        );

    -- # Nama 1 Rekening wajib diisi, max character Nama 1 Rekening adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Nama Rekening 1 harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.nama_rekening_1) = vEmpty
        AND (TRIM(A.nomor_rekening) <> vEmpty
            OR TRIM(A.kode_bank) <> vEmpty
            OR TRIM(A.kode_valuta_rekening) <> vEmpty
            OR TRIM(A.nama_rekening_2) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Nama Rekening 1 maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.nama_rekening_1) > 100
        AND (TRIM(A.nomor_rekening) <> vEmpty
            OR TRIM(A.kode_bank) <> vEmpty
            OR TRIM(A.kode_valuta_rekening) <> vEmpty
            OR TRIM(A.nama_rekening_2) <> vEmpty);

    -- # Jika diisi, max character Nama 2 Rekening adalah 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Nama Rekening 2 maksimal 100 karakter, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND length(A.nama_rekening_2) > 100
        AND (TRIM(A.nomor_rekening) <> vEmpty
            OR TRIM(A.kode_bank) <> vEmpty
            OR TRIM(A.kode_valuta_rekening) <> vEmpty
            OR TRIM(A.nama_rekening_1) <> vEmpty);

    -- # Validasi tidak boleh ada Kode Partner - Kode Bank - Nomor Rekening yang duplikat dalam 1 csv
    WITH duplicate_data_partner_bank AS (
        SELECT A.kode_partner, A.kode_bank, A.nomor_rekening
        FROM ul_import_data_partner A
        WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_partner) <> vEmpty
        AND TRIM(A.kode_bank) <> vEmpty
        AND TRIM(A.nomor_rekening) <> vEmpty
        GROUP BY A.kode_partner, A.kode_bank, A.nomor_rekening
        HAVING COUNT(1) > 1
    )
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Pasangan Kode Partner - Kode Bank - Nomor Rekening duplikat, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND EXISTS (
            SELECT 1
            FROM duplicate_data_partner_bank B
            WHERE A.kode_partner = B.kode_partner
                AND A.kode_bank = B.kode_bank
                AND A.nomor_rekening = B.nomor_rekening
        );

    -- # PARTNER REL
    -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
    -- # Kode Partner Relasi, Flag Tagihan, Flag Pengiriman

    UPDATE ul_import_data_partner A
    SET flg_rel = vYes
    WHERE A.upload_header_id = pUploadHeaderId
        AND (TRIM(A.kode_partner_relasi) <> vEmpty
            OR TRIM(A.flag_tagihan) <> vEmpty
            OR TRIM(A.flag_pengiriman) <> vEmpty);

    -- # Data Manage As Customer atas partner harus diisi
    -- Kode Tipe Customer, Kode Valuta Piutang, Batas Jumlah Piutang, Flag Komisi, Flag Referensi Invoice,
    --      Batas Pembayaran Customer, Cara Pembayaran Customer, Hari Pembayaran Customer,
    --      Tanggal Pembayaran Customer, Diskon Reguler
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Data Manage As Customer harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND flg_rel = vYes
        AND (TRIM(A.kode_tipe_customer) = vEmpty
            OR TRIM(A.kode_valuta_piutang) = vEmpty
            OR TRIM(A.batas_jumlah_piutang) = vEmpty
            OR TRIM(A.flag_komisi) = vEmpty
            OR TRIM(A.flag_referensi_invoice) = vEmpty
            OR TRIM(A.batas_pembayaran_customer) = vEmpty
            OR TRIM(A.cara_pembayaran_customer) = vEmpty
            OR TRIM(A.cara_pembayaran_customer) = vEmpty
            OR TRIM(A.hari_pembayaran_customer) = vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) = vEmpty
            OR TRIM(A.diskon_reguler) = vEmpty);

    -- # Kode Partner Relasi wajib diisi, dan harus merupakkan partner dengan tipe customer
    --      bukan employee, dan bukan dirinya sendiri
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Partner Relasi harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_partner_relasi) = vEmpty
        AND (TRIM(A.flag_tagihan) <> vEmpty
            OR TRIM(A.flag_pengiriman) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Partner Relasi tidak ada dalam sistem ataupun file CSV, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_partner_relasi) <> vEmpty
        AND (TRIM(A.flag_tagihan) <> vEmpty
            OR TRIM(A.flag_pengiriman) <> vEmpty)
        AND (
            NOT EXISTS (
                SELECT 1
                FROM m_partner Z
                INNER JOIN m_partner_type Y ON Z.partner_id = Y.partner_id
                WHERE Z.partner_code = A.kode_partner_relasi
                    AND Z.tenant_id = vTenantId
                    AND Z.active = vYes
            ) OR NOT EXISTS (
                SELECT 1
                FROM ul_import_data_partner Z
                WHERE Z.upload_header_id = A.upload_header_id
                    AND Z.kode_partner = A.kode_partner
            )
        );

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Partner Relasi bukan Customer, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_partner_relasi) <> vEmpty
        AND (TRIM(A.flag_tagihan) <> vEmpty
            OR TRIM(A.flag_pengiriman) <> vEmpty)
        AND (
            NOT EXISTS (
                SELECT 1
                FROM m_partner Z
                INNER JOIN m_partner_type Y ON Z.partner_id = Y.partner_id
                WHERE Y.group_partner = vGroupPartnerCustomer
                    AND Z.partner_code = A.kode_partner_relasi
                    AND Z.partner_id IN (
                        SELECT X.partner_id
                        FROM m_partner_type X
                        WHERE X.tenant_id = vTenantId
                            AND X.group_partner = vGroupPartnerEmployee
                    )
                    AND Z.tenant_id = vTenantId
                    AND Z.active = vYes
            ) OR NOT EXISTS (
                SELECT 1
                FROM ul_import_data_partner Z
                WHERE Z.upload_header_id = A.upload_header_id
                    AND Z.kode_partner = A.kode_partner
                    AND TRIM(Z.kode_tipe_customer) <> vEmpty
                    AND TRIM(Z.kode_valuta_piutang) <> vEmpty
                    AND TRIM(Z.batas_jumlah_piutang) <> vEmpty
                    AND TRIM(Z.flag_komisi) <> vEmpty
                    AND TRIM(Z.flag_referensi_invoice) <> vEmpty
                    AND TRIM(Z.batas_pembayaran_customer) <> vEmpty
                    AND TRIM(Z.cara_pembayaran_customer) <> vEmpty
                    AND TRIM(Z.cara_pembayaran_customer) <> vEmpty
                    AND TRIM(Z.hari_pembayaran_customer) <> vEmpty
                    AND TRIM(Z.tanggal_pembayaran_customer) <> vEmpty
                    AND TRIM(Z.diskon_reguler) <> vEmpty
            )
        );

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Partner Relasi tidak boleh sama dengan Kode Partner, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_partner_relasi) <> vEmpty
        AND (TRIM(A.flag_tagihan) <> vEmpty
            OR TRIM(A.flag_pengiriman) <> vEmpty)
        AND A.kode_partner_relasi = Z.kode_partner;

    -- # Flag Tagihan wajib diisi, dan Flag Tagihan harus ada dalam combo YESNO
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Flag Tagihan harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.flag_tagihan) = vEmpty
        AND (TRIM(A.kode_partner_relasi) <> vEmpty
            OR TRIM(A.flag_pengiriman) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Flag Tagihan hanya dapat berisi Y atau N, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.flag_tagihan) <> vEmpty
        AND (TRIM(A.kode_partner_relasi) <> vEmpty
            OR TRIM(A.flag_pengiriman) <> vEmpty
        ) AND NOT EXISTS(
            SELECT 1
            FROM t_combo_value Z
            WHERE Z.combo_id = vComboIdYesNo
                AND Z.code = A.flag_tagihan
        );

    -- # Flag Pengiriman wajib diisi, dan Flag Pengiriman harus ada dalam combo YESNO
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Flag Pengiriman harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.flag_pengiriman) = vEmpty
        AND (TRIM(A.kode_partner_relasi) <> vEmpty
            OR TRIM(A.flag_tagihan) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Flag Pengiriman hanya dapat berisi Y atau N, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.flag_pengiriman) <> vEmpty
        AND (TRIM(A.kode_partner_relasi) <> vEmpty
            OR TRIM(A.flag_tagihan) <> vEmpty
        ) AND NOT EXISTS(
            SELECT 1
            FROM t_combo_value Z
            WHERE Z.combo_id = vComboIdYesNo
                AND Z.code = A.flag_pengiriman
        );

    -- # PARTNER AS SUPPLIER
    -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
    -- # Kode Tipe Supplier, Kode Valuta Hutang, Batas Jumlah Hutang, Batas Pembayaran Supplier, Lama Pengiriman

    UPDATE ul_import_data_partner A
    SET flg_customer = vYes
    WHERE A.upload_header_id = pUploadHeaderId
        AND (TRIM(A.kode_tipe_supplier) = vEmpty
            OR TRIM(A.kode_valuta_hutang) <> vEmpty
            OR TRIM(A.batas_jumlah_hutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_supplier) <> vEmpty
            OR TRIM(A.lama_pengiriman) <> vEmpty);

    -- # Kode Tipe Supplier wajib diisi, dan harus ada dalam sistem dengan group partner S, dan bukan Internal
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Tipe Supplier harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_tipe_supplier) = vEmpty
        AND (TRIM(A.kode_valuta_hutang) <> vEmpty
            OR TRIM(A.batas_jumlah_hutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_supplier) <> vEmpty
            OR TRIM(A.lama_pengiriman) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Tipe Supplier tidak ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_tipe_supplier) <> vEmpty
        AND (TRIM(A.kode_valuta_hutang) <> vEmpty
            OR TRIM(A.batas_jumlah_hutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_supplier) <> vEmpty
            OR TRIM(A.lama_pengiriman) <> vEmpty
        ) AND NOT EXISTS(
            SELECT 1
            FROM m_type_partner Z
            WHERE Z.tenant_id = vTenantId
                AND Z.group_partner = vGroupPartnerSupplier
                AND Z.type_partner_code NOT IN (vTypePartnerSupplierInternal, vTypePartnerCustomerInternal)
        );

    -- # Kode Valuta Hutang wajib diisi, dan harus ada dalam combo CURRENCY
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Valuta Hutang harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_valuta_hutang) = vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.batas_jumlah_hutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_supplier) <> vEmpty
            OR TRIM(A.lama_pengiriman) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Valuta Hutang harus tidak ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_valuta_hutang) <> vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.batas_jumlah_hutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_supplier) <> vEmpty
            OR TRIM(A.lama_pengiriman) <> vEmpty
        ) AND NOT EXISTS(
            SELECT 1
            FROM t_combo_value Z
            WHERE Z.combo_id = vComboIdCurrency
                AND Z.code = A.kode_valuta_hutang
        );

    -- # Batas Jumlah Hutang wajib diisi, harus berupa angka dan harus > 0
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Batas Jumlah Hutang harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.batas_jumlah_hutang) = vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_hutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_supplier) <> vEmpty
            OR TRIM(A.lama_pengiriman) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Batas Jumlah Hutang harus berupa angka, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.batas_jumlah_hutang) <> vEmpty
        AND NOT is_numeric(A.batas_jumlah_hutang)
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_hutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_supplier) <> vEmpty
            OR TRIM(A.lama_pengiriman) <> vEmpty);

    WITH data_hutang_supplier AS (
        SELECT ul_import_data_partner_id, batas_jumlah_hutang
        FROM ul_import_data_partner
        WHERE upload_header_id = pUploadHeaderId
        AND is_numeric(batas_jumlah_hutang)
    )
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Batas Jumlah Hutang harus > 0, '
    FROM data_hutang_supplier B
    WHERE A.ul_import_data_partner_id = B.ul_import_data_partner_id
        AND B.batas_jumlah_hutang::numeric <= 0
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_hutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_supplier) <> vEmpty
            OR TRIM(A.lama_pengiriman) <> vEmpty);

    -- # Batas Pembayaran Supplier wajib diisi, harus berupa angka dan harus >= 0
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Batas Pembayaran Supplier harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.batas_pembayaran_supplier) = vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_hutang) <> vEmpty
            OR TRIM(A.batas_jumlah_hutang) <> vEmpty
            OR TRIM(A.lama_pengiriman) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Batas Pembayaran Supplier harus berupa angka, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.batas_pembayaran_supplier) <> vEmpty
        AND NOT is_numeric(A.batas_pembayaran_supplier)
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_hutang) <> vEmpty
            OR TRIM(A.batas_jumlah_hutang) <> vEmpty
            OR TRIM(A.lama_pengiriman) <> vEmpty);

    WITH data_pembayaran_supplier AS (
        SELECT ul_import_data_partner_id, batas_pembayaran_supplier
        FROM ul_import_data_partner
        WHERE upload_header_id = pUploadHeaderId
        AND is_numeric(batas_pembayaran_supplier)
    )
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Batas Pembayaran Supplier >= 0, '
    FROM data_pembayaran_supplier B
    WHERE A.ul_import_data_partner_id = B.ul_import_data_partner_id
        AND B.batas_pembayaran_supplier::numeric < 0
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_hutang) <> vEmpty
            OR TRIM(A.batas_jumlah_hutang) <> vEmpty
            OR TRIM(A.lama_pengiriman) <> vEmpty);

    -- # Lama Pengiriman wajib diisi, harus berupa angka dan harus >= 0
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Lama Pengiriman harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.lama_pengiriman) = vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_hutang) <> vEmpty
            OR TRIM(A.batas_jumlah_hutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_supplier) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Lama Pengiriman harus berupa angka, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.lama_pengiriman) <> vEmpty
        AND NOT is_numeric(A.lama_pengiriman)
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_hutang) <> vEmpty
            OR TRIM(A.batas_jumlah_hutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_supplier) <> vEmpty);

    WITH data_pembayaran_supplier AS (
        SELECT ul_import_data_partner_id, lama_pengiriman
        FROM ul_import_data_partner
        WHERE upload_header_id = pUploadHeaderId
        AND is_numeric(lama_pengiriman)
    )
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Lama Pengiriman harus >= 0, '
    FROM data_pembayaran_supplier B
    WHERE A.ul_import_data_partner_id = B.ul_import_data_partner_id
        AND B.lama_pengiriman::numeric < 0
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_hutang) <> vEmpty
            OR TRIM(A.batas_jumlah_hutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_supplier) <> vEmpty);


    -- # PARTNER AS CUSTOMER
    -- # Jika akan diisi, maka harus mengisi kolom-kolom berikut sekaligus:
    -- # Kode Tipe Customer, Kode Valuta Piutang, Batas Jumlah Piutang, Flag Komisi, Flag Referensi Invoice,
    --      Batas Pembayaran Customer, Cara Pembayaran Customer, Hari Pembayaran Customer,
    --      Tanggal Pembayaran Customer, Diskon Reguler

    UPDATE ul_import_data_partner A
    SET flg_customer = vYes
    WHERE A.upload_header_id = pUploadHeaderId
        AND (TRIM(A.kode_tipe_customer) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty);

    -- # Kode Tipe Customer wajib diisi, dan harus ada dalam sistem dengan group partner C, dan bukan Internal
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Tipe Customer harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_tipe_customer) = vEmpty
        AND (TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Tipe Supplier tidak ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_tipe_supplier) <> vEmpty
        AND (TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty
        ) AND NOT EXISTS(
            SELECT 1
            FROM m_type_partner Z
            WHERE Z.tenant_id = vTenantId
                AND Z.group_partner = vGroupPartnerCustomer
                AND Z.type_partner_code NOT IN (vTypePartnerSupplierInternal, vTypePartnerCustomerInternal)
        );

    -- # Kode Valuta Piutang wajib diisi, dan harus ada dalam combo CURRENCY
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Valuta Piutang harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_valuta_piutang) = vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Valuta Piutang tidak ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_valuta_piutang) <> vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty
        ) AND NOT EXISTS(
            SELECT 1
            FROM t_combo_value Z
            WHERE Z.combo_id = vComboIdCurrency
                AND Z.code = A.kode_valuta_piutang
        );

    -- # Batas Jumlah Piutang wajib diisi, harus berupa angka dan harus > 0
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Batas Jumlah Piutang harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.batas_jumlah_piutang) = vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Batas Jumlah Piutang harus berupa angka, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.batas_jumlah_piutang) <> vEmpty
        AND NOT is_numeric(A.batas_jumlah_piutang)
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty);

    WITH data_piutang_customer AS (
        SELECT ul_import_data_partner_id, batas_jumlah_piutang
        FROM ul_import_data_partner
        WHERE upload_header_id = pUploadHeaderId
        AND is_numeric(batas_jumlah_piutang)
    )
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Batas Jumlah Piutang harus > 0, '
    FROM data_piutang_customer B
    WHERE A.ul_import_data_partner_id = B.ul_import_data_partner_id
        AND B.batas_jumlah_piutang::numeric <= 0
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty);

    -- # Flag Komisi wajib diisi, dan harus ada dalam combo YESNO
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Flag Komisi harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.flag_komisi) = vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Flag Komisi hanya dapat berisi Y atau N, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.flag_komisi) <> vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty
        ) AND NOT EXISTS(
            SELECT 1
            FROM t_combo_value Z
            WHERE Z.combo_id = vComboIdYesNo
                AND Z.code = A.flag_komisi
        );

    -- # Flag Referensi Invoice wajib diisi, dan harus ada dalam combo YESNO
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Flag Referensi Invoice harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.flag_referensi_invoice) = vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Flag Referensi Invoice hanya dapat berisi Y atau N, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.flag_referensi_invoice) <> vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty
        ) AND NOT EXISTS(
            SELECT 1
            FROM t_combo_value Z
            WHERE Z.combo_id = vComboIdYesNo
                AND Z.code = A.flag_referensi_invoice
        );

    -- # Batas Pembayaran Customer wajib diisi, harus berupa angka dan harus >= 0
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Batas Pembayaran Customer harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.batas_pembayaran_customer) = vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Batas Pembayaran Customer harus berupa angka, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.batas_pembayaran_customer) <> vEmpty
        AND NOT is_numeric(A.batas_pembayaran_customer)
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty);

    WITH data_pembayaran_customer AS (
        SELECT ul_import_data_partner_id, batas_pembayaran_customer
        FROM ul_import_data_partner
        WHERE upload_header_id = pUploadHeaderId
        AND is_numeric(batas_pembayaran_customer)
    )
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Batas Pembayaran Customer harus >= 0, '
    FROM data_piutang_customer B
    WHERE A.ul_import_data_partner_id = B.ul_import_data_partner_id
        AND B.batas_pembayaran_customer::numeric < 0
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty);

    -- # Cara Pembayaran Customer wajib diisi, dan harus ada dalam combo PAYMENTMODE
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Cara Pembayaran Customer harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.cara_pembayaran_customer) = vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Cara Pembayaran Customer hanya dapat berisi Y atau N, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.cara_pembayaran_customer) <> vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty
        ) AND NOT EXISTS(
            SELECT 1
            FROM t_combo_value Z
            WHERE Z.combo_id = vComboIdYesNo
                AND Z.code = A.cara_pembayaran_customer
        );

    -- # Kode Bank Customer dan Nomor Rekening Customer harus diisi sekaligus atau tidak sama sekali
    -- # Jika Kode Bank Customer dan Nomor Rekening Customer diisi, harus sama dengan Kode Bank dan Nomor Rekening
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Bank Customer harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_bank_customer) = vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Nomor Rekening Customer harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.nomor_rekening_customer) = vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Kode Bank Customer harus sama dengan Kode Bank, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.kode_bank_customer) <> vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty)
        AND A.kode_bank_customer <> kode_bank;

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Nomor Rekening Customer harus sama dengan Kode Bank, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.nomor_rekening_customer) <> vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty)
        AND A.nomor_rekening_customer <> nomor_rekening;

    -- # Hari Pembayaran Customer wajib diisi, dan harus ada dalam combo DAY
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Hari Pembayaran Customer harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.hari_pembayaran_customer) = vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Hari Pembayaran Customer tidak ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.hari_pembayaran_customer) <> vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty
        ) AND NOT EXISTS(
            SELECT 1
            FROM t_combo_value Z
            WHERE Z.combo_id = vComboIdDay
                AND Z.code = A.hari_pembayaran_customer
        );

    -- # Tanggal Pembayaran Customer wajib diisi, dan harus ada dalam combo DATE
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Hari Pembayaran Customer harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.tanggal_pembayaran_customer) = vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Hari Pembayaran Customer tidak ada dalam sistem, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.tanggal_pembayaran_customer) <> vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.diskon_reguler) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty
        ) AND NOT EXISTS(
            SELECT 1
            FROM t_combo_value Z
            WHERE Z.combo_id = vComboIdDate
                AND Z.code = A.tanggal_pembayaran_customer
        );

    -- # Diskon Reguler wajib diisi, harus berupa angka dan harus bernilai di antara 0 s/d 100
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Diskon Reguler harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.diskon_reguler) = vEmpty
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty);

    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Diskon Reguler harus diisi, '
    WHERE A.upload_header_id = pUploadHeaderId
        AND TRIM(A.diskon_reguler) <> vEmpty
        AND NOT is_numeric(A.diskon_reguler)
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty);

    WITH data_diskon_customer AS (
        SELECT ul_import_data_partner_id, diskon_reguler
        FROM ul_import_data_partner
        WHERE upload_header_id = pUploadHeaderId
        AND is_numeric(diskon_reguler)
    )
    UPDATE ul_import_data_partner A
    SET status = vFail,
        message = message || 'Diskon Reguler harus bernilai antara 0 s/d 100, '
    FROM data_diskon_customer B
    WHERE A.ul_import_data_partner_id = B.ul_import_data_partner_id
        AND (B.diskon_reguler::numeric < 0 OR B.diskon_reguler::numeric > 100)
        AND (TRIM(A.kode_tipe_supplier) <> vEmpty
            OR TRIM(A.kode_valuta_piutang) <> vEmpty
            OR TRIM(A.flag_komisi) <> vEmpty
            OR TRIM(A.flag_referensi_invoice) <> vEmpty
            OR TRIM(A.batas_jumlah_piutang) <> vEmpty
            OR TRIM(A.cara_pembayaran_customer) <> vEmpty
            OR TRIM(A.hari_pembayaran_customer) <> vEmpty
            OR TRIM(A.tanggal_pembayaran_customer) <> vEmpty
            OR TRIM(A.batas_pembayaran_customer) <> vEmpty
            OR TRIM(A.kode_bank_customer) <> vEmpty
            OR TRIM(A.nomor_rekening_customer) <> vEmpty);


    -- Cek apakah ada item CSV yang statusnya fail
    IF EXISTS ( SELECT 1 FROM ul_import_data_partner B
                WHERE B.upload_header_id = pUploadHeaderId
                AND B.status = vFail ) THEN

        -- Sesuaikan message, agar message paling belakang tidak ada koma
        UPDATE ul_import_data_partner 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_import_data_partner A
        SET status = vFail,
            update_status = vActionError
        WHERE upload_header_id = pUploadHeaderId;

        -- Hitung jumlah data yang FAIL
        SELECT COUNT(1) INTO vCountFail
        FROM ul_import_data_partner
        WHERE upload_header_id = pUploadHeaderId
            AND status = vFail;
    ELSE

        -- RESET table temp
        DELETE FROM tt_ul_partner WHERE upload_header_id = pUploadHeaderId;

        -- Update status non FAIL to OK
        UPDATE ul_import_data_partner
        SET status = vOk
        WHERE upload_header_id = pUploadHeaderId;

        -- INSERT OK data untuk partner code baru dengan kode_partner holding kosong
        WITH insert_data_partner 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_partner, A.nama_partner, B.ctgr_partner_id,
                    A.nama_eksternal, vEmptyId, vEmptyId, A.flag_holding, A.ranking, vDatetime,
                    vUserId, vDatetime, vUserId, 0, vYes,
                    vDatetime, vSpaceValue,
                    A.bidang_usaha, A.level_harga,
                    A.jenis_industri, A.kode_status_npwp
            FROM ul_import_data_partner A
            INNER JOIN m_ctgr_partner B ON A.kode_kategori_partner = B.ctgr_partner_code AND B.tenant_id = vTenantId
            WHERE A.upload_header_id = pUploadHeaderId
            AND TRIM(A.kode_partner_holding) = vEmpty
            AND NOT EXISTS (
                SELECT 1 FROM m_partner Z
                WHERE A.kode_partner = Z.partner_code AND Z.tenant_id = vTenantId
            )
            RETURNING *
        ), update_ul AS (
            -- Ubah update_status menjadi I jika data digunakan untuk Insert
            UPDATE ul_import_data_partner A SET
                update_status = vActionInsert
            WHERE A.upload_header_id = pUploadHeaderId
                AND A.status = vOk
                AND EXISTS (
                    SELECT 1 FROM insert_data_partner B WHERE A.kode_partner = B.partner_code AND B.tenant_id = vTenantId
            )
        )
        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_partner;

        WHILE EXISTS (SELECT 1 FROM ul_import_data_partner B
                                WHERE B.upload_header_id = pUploadHeaderId
                                AND B.status = vOk
                                AND COALESCE(B.update_status, vEmpty) = vEmpty) LOOP

            -- INSERT OK data untuk partner code baru dengan kode partner holding tidak kosong
            WITH insert_data_partner 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_partner, A.nama_partner, B.ctgr_partner_id,
                        A.nama_eksternal, vEmptyId, C.partner_id, A.flag_holding, A.ranking, vDatetime,
                        vUserId, vDatetime, vUserId, 0, vYes,
                        vDatetime, vSpaceValue,
                        A.bidang_usaha, A.level_harga,
                        A.jenis_industri, A.kode_status_npwp
                FROM ul_import_data_partner A
                INNER JOIN m_ctgr_partner B ON A.kode_kategori_partner = B.ctgr_partner_code AND B.tenant_id = vTenantId
                INNER JOIN m_partner C ON A.kode_partner_holding = C.partner_code AND C.tenant_id = vTenantId
                WHERE A.upload_header_id = pUploadHeaderId
                AND TRIM(A.kode_partner_holding) <> vEmpty
                AND COALESCE(A.update_status, vEmpty) = vEmpty
                AND NOT EXISTS (
                    SELECT 1 FROM m_partner Z
                    WHERE A.kode_partner = Z.partner_code AND Z.tenant_id = vTenantId
                )
                RETURNING *
            ), update_ul AS (
                -- Ubah update_status menjadi I jika data digunakan untuk Insert
                UPDATE ul_import_data_partner A SET
                    update_status = vActionInsert
                WHERE A.upload_header_id = pUploadHeaderId
                    AND A.status = vOk
                    AND EXISTS (
                        SELECT 1 FROM insert_data_partner B WHERE A.kode_partner = B.partner_code AND B.tenant_id = vTenantId
                )
            )
            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_partner;

        END LOOP ;

        -- 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.deskripsi_alamat, B.alamat_1,
            B.alamat_2, B.alamat_3, B.kota, B.kode_pos, B.provinsi, B.kode_negara,
            B.telepon_1, B.telepon_1, B.fax_1, B.fax_2, vYes, vDatetime, vUserId,
            vDatetime, vUserId, 0, vYes, vDatetime,
            vSpaceValue, B.flag_shipping, B.flag_billing, B.flag_mailing, vYes,
            B.flag_others, B.garis_bujur::numeric, B.garis_lintang::numeric
        FROM tt_ul_partner A
        INNER JOIN ul_import_data_partner B ON A.partner_code = B.kode_partner
        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.nama_cp, B.pekerjaan_cp, B.alamat_1_cp,
            B.alamat_2_cp, B.alamat_3_cp, B.kota_cp, B.kode_pos_cp, B.negara_cp, B.telepon_1_cp, B.telepon_2_cp,
            B.fax_1_cp, B.fax_2_cp, B.email_cp, vDatetime, vUserId, vDatetime,
            vUserId, 0, vYes, vDatetime, vSpaceValue,
            B.departemen_cp, B.level_pekerjaan_cp, B.telepon_eksternal_1_cp, B.telepon_eksternal_2_cp, B.ponsel_1_cp,
            B.ponsel_2_cp, B.flag_responsible, vNo, B.flag_notifikasi_ap,
            B.flag_notifikasi_ar
        FROM tt_ul_partner A
        INNER JOIN ul_import_data_partner B ON A.partner_code = B.kode_partner
        WHERE A.upload_header_id = pUploadHeaderId
        AND A.upload_header_id = B.upload_header_id
        AND A.action_type = vActionInsert
        AND B.flg_cp = vYes;

        -- RESET table temp
        DELETE FROM tt_ul_partner WHERE upload_header_id = pUploadHeaderId;

    END IF;


    RETURN vCountFail;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
  /