OPTIONS (SKIP = 1,errors=20)
LOAD DATA
INTO TABLE "xxxx"."xxx" TRUNCATE
FIELDS TERMINATED BY '\t'
TRAILING NULLCOLS
(
CAMPAIGN_ID "TRIM(:CAMPAIGN_ID)",
CELL_ID "TRIM(:CELL_ID)",
POLICYID_1 "TRIM(:POLICYID_1)",
APPLICATION_NUMBER_1 "TRIM(replace(:APPLICATION_NUMBER_1,'null',''))",
ACCOUNT_NUMBER_1 "TRIM(:ACCOUNT_NUMBER_1)",
SPONSOR_CODE_1 "TRIM(:SPONSOR_CODE_1)",
SPONSOR_NAME_1 "TRIM(:SPONSOR_NAME_1)",
MRP_1 "TRIM(replace(:MRP_1,'.',','))",
MAX_ACTUAL_1 "TRIM(:MAX_ACTUAL_1)",
NET_PAID_1 "TRIM(replace(:NET_PAID_1,'.',','))",
EFFECTIVE_DATE_1 "TRIM(:EFFECTIVE_DATE_1)",
NUM_RETENTION_1 "TRIM(:NUM_RETENTION_1)",
NUM_MODIFICATION_1 "TRIM(:NUM_MODIFICATION_1)",
NUM_CLAIM_1 "TRIM(:NUM_CLAIM_1)",
NUM_SINISTER_1 "TRIM(:NUM_SINISTER_1)",
NUM_UNPAID_1 "TRIM(:NUM_UNPAID_1)",
CANCELLATIONREASONCODE_1 "TRIM(replace(:CANCELLATIONREASONCODE_1,'null',''))",
CANCELLATIONREASONDESC_1 "TRIM(replace(:CANCELLATIONREASONDESC_1,'null',''))",
CANCELLACIONTRANSTIMESTAMP_1 "TRIM(replace(:CANCELLACIONTRANSTIMESTAMP_1,'null',''))",
SPOUSEFIRSTNAME "TRIM(replace(:SPOUSEFIRSTNAME,'null',''))",
SPOUSEMIDNAME "TRIM(replace(:SPOUSEMIDNAME,'null',''))",
SPOUSELASTNAME "TRIM(replace(:SPOUSELASTNAME,'null',''))",
SPOUSEPERSID "TRIM(replace(:SPOUSEPERSID,'null',''))",
SPOUSEBIRTHDATE "TRIM(replace(:SPOUSEBIRTHDATE,'null',''))",
SPOUSEGENDER "TRIM(replace(:SPOUSEGENDER,'null',''))",
SPOUSEGENDERDESC "TRIM(replace(:SPOUSEGENDERDESC,'null',''))",
SPOUSEPROD "TRIM(replace(:SPOUSEPROD,'null',''))",
SPOUSEPRODBENEFLEVEL "TRIM(replace(:SPOUSEPRODBENEFLEVEL,'null',''))",
MAININSUREDPRODUCT_1 "TRIM(replace(:MAININSUREDPRODUCT_1,'null',''))",
PRODUCT_MARKETING_NAME_1 "TRIM(replace(:PRODUCT_MARKETING_NAME_1,'null',''))",
BENEFITCODE_1 "TRIM(replace(:BENEFITCODE_1,'null',''))",
BENEFITNAME_1 "TRIM(replace(:BENEFITNAME_1,'null',''))",
BENEFITAMOUNT_MAIN_1 "TRIM(replace(:BENEFITAMOUNT_MAIN_1,'null',''))",
BENEFITCODE_2 "TRIM(replace(:BENEFITCODE_2,'null',''))",
BENEFITNAME_2 "TRIM(replace(:BENEFITNAME_2,'null',''))",
BENEFITAMOUNT_MAIN_2 "TRIM(replace(:BENEFITAMOUNT_MAIN_2,'null',''))",
BENEFITCODE_3 "TRIM(replace(:BENEFITCODE_3,'null',''))",
BENEFITNAME_3 "TRIM(replace(:BENEFITNAME_3,'null',''))",
BENEFITAMOUNT_MAIN_3 "TRIM(replace(:BENEFITAMOUNT_MAIN_3,'null',''))",
BENEFITCODE_4 "TRIM(replace(:BENEFITCODE_4,'null',''))",
BENEFITNAME_4 "TRIM(replace(:BENEFITNAME_4,'null',''))",
BENEFITAMOUNT_MAIN_4 "TRIM(replace(:BENEFITAMOUNT_MAIN_4,'null',''))",
BENEFITCODE_5 "TRIM(replace(:BENEFITCODE_5,'null',''))",
BENEFITNAME_5 "TRIM(replace(:BENEFITNAME_5,'null',''))",
BENEFITAMOUNT_MAIN_5 "TRIM(replace(:BENEFITAMOUNT_MAIN_5,'null',''))",
BENEFITCODE_6 "TRIM(replace(:BENEFITCODE_6,'null',''))",
BENEFITNAME_6 "TRIM(replace(:BENEFITNAME_6,'null',''))",
BENEFITAMOUNT_MAIN_6 "TRIM(replace(:BENEFITAMOUNT_MAIN_6,'null',''))",
IDEXPERIAN "TRIM(replace(:IDEXPERIAN,'null',''))",
CUSTOMERID "TRIM(replace(:CUSTOMERID,'null',''))",
ACTIVE_POLICIES_COUNTER "TRIM(replace(:ACTIVE_POLICIES_COUNTER,'null',''))",
CANCELLED_POLICIES_COUNTER "TRIM(replace(:CANCELLED_POLICIES_COUNTER,'null',''))",
POLICIES_COUNTER "TRIM(replace(:POLICIES_COUNTER,'null',''))",
LJASPONSOR_COUNT "TRIM(replace(:LJASPONSOR_COUNT,'null',''))",
PRODUCTS_COUNT "TRIM(replace(:PRODUCTS_COUNT,'null',''))",
SUM_NET_PAID "TRIM(replace(:SUM_NET_PAID,'.',','))",
TITLE "TRIM(replace(:TITLE,'null',''))",
FIRSTNAME "TRIM(replace(:FIRSTNAME,'null',''))",
MIDDLENAME "TRIM(replace(:MIDDLENAME,'null',''))",
LASTNAME "TRIM(replace(:LASTNAME,'null',''))",
DATEOFBIRTH "TRIM(replace(:DATEOFBIRTH,'null',''))",
GENDER "TRIM(replace(:GENDER,'null',''))",
POLICYHOLDERPERSID "TRIM(replace(:POLICYHOLDERPERSID,'null',''))",
COUNTRYCODE "TRIM(replace(:COUNTRYCODE,'null',''))",
COUNTRYNAME "TRIM(replace(:COUNTRYNAME,'null',''))",
HOMEPHONE "TRIM(replace(:HOMEPHONE,'null',''))",
WORKPHONE "TRIM(replace(:WORKPHONE,'null',''))",
MOBILEPHONE "TRIM(replace(:MOBILEPHONE,'null',''))",
ADDRESSLINE1 "TRIM(replace(:ADDRESSLINE1,'null',''))",
ADDRESSLINE2 "TRIM(replace(:ADDRESSLINE2,'null',''))",
ADDRESSLINE3 "TRIM(replace(:ADDRESSLINE3,'null',''))",
ADDRESSLINE4 "TRIM(replace(:ADDRESSLINE4,'null',''))",
TOWNCITY "TRIM(replace(:TOWNCITY,'null',''))",
POSTCODE "TRIM(replace(:POSTCODE,'null',''))",
PROVINCE "TRIM(replace(:PROVINCE,'null',''))",
HAS_Y "TRIM(replace(:HAS_Y,'null',''))",
POLICYID_2 "TRIM(replace(:POLICYID_2,'null',''))",
SPONSOR_CODE_2 "TRIM(replace(:SPONSOR_CODE_2,'null',''))",
SPONSOR_NAME_2 "TRIM(replace(:SPONSOR_NAME_2,'null',''))",
MRP_2 "TRIM(replace(:MRP_2,'null',''))",
MAX_ACTUAL_2 "TRIM(replace(:MAX_ACTUAL_2,'null',''))",
NET_PAID_2 "TRIM(replace(:NET_PAID_2,'null',''))",
EFFECTIVE_DATE_2 "TRIM(replace(:EFFECTIVE_DATE_2,'null',''))",
MAININSUREDPRODUCT_2 "TRIM(replace(:MAININSUREDPRODUCT_2,'null',''))",
NUM_RETENTION_2 "TRIM(replace(:NUM_RETENTION_2,'null',''))",
NUM_MODIFICATION_2 "TRIM(replace(:NUM_MODIFICATION_2,'null',''))",
NUM_CLAIM_2 "TRIM(replace(:NUM_CLAIM_2,'null',''))",
NUM_SINISTER_2 "TRIM(replace(:NUM_SINISTER_2,'null',''))",
NUM_UNPAID_2 "TRIM(replace(:NUM_UNPAID_2,'null',''))",
CANCELLATIONREASONCODE_2 "TRIM(replace(:CANCELLATIONREASONCODE_2,'null',''))",
CANCELLATIONREASONDESC_2 "TRIM(replace(:CANCELLATIONREASONDESC_2,'null',''))",
CANCELLACIONTRANSTIMESTAMP_2 "TRIM(replace(:CANCELLACIONTRANSTIMESTAMP_2,'null',''))",
POLICYID_3 "TRIM(replace(:POLICYID_3,'null',''))",
SPONSOR_CODE_3 "TRIM(replace(:SPONSOR_CODE_3,'null',''))",
SPONSOR_NAME_3 "TRIM(replace(:SPONSOR_NAME_3,'null',''))",
MRP_3 "TRIM(replace(:MRP_3,'null',''))",
MAX_ACTUAL_3 "TRIM(replace(:MAX_ACTUAL_3,'null',''))",
NET_PAID_3 "TRIM(replace(:NET_PAID_3,'null',''))",
EFFECTIVE_DATE_3 "TRIM(replace(:EFFECTIVE_DATE_3,'null',''))",
MAININSUREDPRODUCT_3 "TRIM(replace(:MAININSUREDPRODUCT_3,'null',''))",
NUM_RETENTION_3 "TRIM(replace(:NUM_RETENTION_3,'null',''))",
NUM_MODIFICATION_3 "TRIM(replace(:NUM_MODIFICATION_3,'null',''))",
NUM_CLAIM_3 "TRIM(replace(:NUM_CLAIM_3,'null',''))",
NUM_SINISTER_3 "TRIM(replace(:NUM_SINISTER_3,'null',''))",
NUM_UNPAID_3 "TRIM(replace(:NUM_UNPAID_3,'null',''))",
CANCELLATIONREASONCODE_3 "TRIM(replace(:CANCELLATIONREASONCODE_3,'null',''))",
CANCELLATIONREASONDESC_3 "TRIM(replace(:CANCELLATIONREASONDESC_3,'null',''))",
CANCELLACIONTRANSTIMESTAMP_3 "TRIM(replace(:CANCELLACIONTRANSTIMESTAMP_3,'null',''))",
POLICYID_4 "TRIM(replace(:POLICYID_4,'null',''))",
SPONSOR_CODE_4 "TRIM(replace(:SPONSOR_CODE_4,'null',''))",
SPONSOR_NAME_4 "TRIM(replace(:SPONSOR_NAME_4,'null',''))",
MRP_4 "TRIM(replace(:MRP_4,'null',''))",
MAX_ACTUAL_4 "TRIM(replace(:MAX_ACTUAL_4,'null',''))",
NET_PAID_4 "TRIM(replace(:NET_PAID_4,'null',''))",
EFFECTIVE_DATE_4 "TRIM(replace(:EFFECTIVE_DATE_4,'null',''))",
MAININSUREDPRODUCT_4 "TRIM(replace(:MAININSUREDPRODUCT_4,'null',''))",
NUM_RETENTION_4 "TRIM(replace(:NUM_RETENTION_4,'null',''))",
NUM_MODIFICATION_4 "TRIM(replace(:NUM_MODIFICATION_4,'null',''))",
NUM_CLAIM_4 "TRIM(replace(:NUM_CLAIM_4,'null',''))",
NUM_SINISTER_4 "TRIM(replace(:NUM_SINISTER_4,'null',''))",
NUM_UNPAID_4 "TRIM(replace(:NUM_UNPAID_4,'null',''))",
CANCELLATIONREASONCODE_4 "TRIM(replace(:CANCELLATIONREASONCODE_4,'null',''))",
CANCELLATIONREASONDESC_4 "TRIM(replace(:CANCELLATIONREASONDESC_4,'null',''))",
CANCELLACIONTRANSTIMESTAMP_4 "TRIM(replace(:CANCELLACIONTRANSTIMESTAMP_4,'null',''))",
PAYMENTTYPE "TRIM(replace(:PAYMENTTYPE,'null',''))",
CARDNUMBER "TRIM(replace(:CARDNUMBER,'null',''))",
CARDEXPIRYDATE "TRIM(replace(:CARDEXPIRYDATE,'null',''))",
CARDTYPE "TRIM(replace(:CARDTYPE,'null',''))",
BANKCODE "TRIM(replace(:BANKCODE,'null',''))"
)
