Database Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

1 Row not loaded because all WHEN clauses were failed....SQL Loader Guru's please help

3725928Aug 13 2018 — edited Aug 18 2018

I am getting the error in loading the second table (in RED below)...the first loaded just fine here is the ctl script

OPTIONS(rows=1000,readsize=20000000,bindsize=20000000, ERRORS=99999,parallel=true)

LOAD DATA

APPEND

INTO TABLE NORD_EMS_SAE_LOAD_HEADER

WHEN RECORD_TYPE = 'EXTRACT'

TRAILING NULLCOLS

(

RECORD_TYPE char TERMINATED BY '|',

BATCH_DATE date(10) "YYYY-MM-DD" TERMINATED BY '|',

TOTAL_DETAIL_LINES INTEGER EXTERNAL TERMINATED BY '|',

TOTAL_DETAIL_AMOUNT CHAR "TRIM(:TOTAL_DETAIL_AMOUNT)",

--TOTAL_DETAIL_AMOUNT CHAR TERMINATED BY WHITESPACE,

RUN_ID "NORD_EMS_RUN_ID_S.NEXTVAL",

LAST_UPDATE_DATE SYSDATE,

LAST_UPDATED_BY CONSTANT 1108,

PROCESS_STATUS CONSTANT 'NEW',

REQUEST_ID CONSTANT '-1'

)

INTO TABLE NORD_EMS_SAE_LOAD_DETAIL

WHEN RECORD_TYPE = 'DETAIL'

FIELDS TERMINATED BY '|'

TRAILING NULLCOLS

(

RECORD_TYPE POSITION(1) char,

BATCH_ID char "RTRIM(:BATCH_ID)" ,

BATCH_DATE char "RTRIM(:BATCH_DATE)" ,

SEQUENCE_NUMBER char "RTRIM(:SEQUENCE_NUMBER)" ,

EMP_ID char "RTRIM(:EMP_ID)" ,

LAST_NAME char "RTRIM(:LAST_NAME)" ,

FIRST_NAME char "RTRIM(:FIRST_NAME)" ,

MI char "RTRIM(:MI)" ,

GROUP_ID char "RTRIM(:GROUP_ID)" ,

EMP_ORG1 char "RTRIM(:EMP_ORG1)" ,

EMP_ORG2 char "RTRIM(:EMP_ORG2)" ,

EMP_ORG3 char "RTRIM(:EMP_ORG3)" ,

EMP_ORG4 char "RTRIM(:EMP_ORG4)" ,

EMP_ORG5 char "RTRIM(:EMP_ORG5)" ,

EMP_ORG6 char "RTRIM(:EMP_ORG6)" ,

FUTURE_USE1 char "RTRIM(:FUTURE_USE1)" ,

FUTURE_USE2 char "RTRIM(:FUTURE_USE2)" ,

FUTURE_USE3 char "RTRIM(:FUTURE_USE3)" ,

REPORT_ID char "RTRIM(:REPORT_ID)" ,

REPORT_KEY char "RTRIM(:REPORT_KEY)" ,

LEDGER char "RTRIM(:LEDGER)" ,

REIMBURSMENT_CURRENCY char "RTRIM(:REIMBURSMENT_CURRENCY)" ,

HOME_COUNTRY char "RTRIM(:HOME_COUNTRY)" ,

RPT_SUBMIT_DATE char "RTRIM(:RPT_SUBMIT_DATE)" ,

RPT_USER_DEFINED_DATE char "RTRIM(:RPT_USER_DEFINED_DATE)" ,

RPT_PAYMENT_PROCESSING_DATE char "RTRIM(:RPT_PAYMENT_PROCESSING_DATE)" ,

RPT_NAME char "RTRIM(:RPT_NAME)" ,

RPT_IMAGE_REQUIRED char "RTRIM(:RPT_IMAGE_REQUIRED)" ,

RPT_HAS_VAT_ENTRY char "RTRIM(:RPT_HAS_VAT_ENTRY)" ,

RPT_HAS_TA_ENTRY char "RTRIM(:RPT_HAS_TA_ENTRY)" ,

TOTAL_POSTED_AMOUNT char "RTRIM(:TOTAL_POSTED_AMOUNT)" ,

CT_TOTAL_APPROVED char "RTRIM(:CT_TOTAL_APPROVED)" ,

FUTURE_USE4 char "RTRIM(:FUTURE_USE4)" ,

FUTURE_USE5 char "RTRIM(:FUTURE_USE5)" ,

ORGUNIT1 char "RTRIM(:ORGUNIT1)" ,

ORGUNIT2 char "RTRIM(:ORGUNIT2)" ,

ORGUNIT3 char "RTRIM(:ORGUNIT3)" ,

ORGUNIT4 char "RTRIM(:ORGUNIT4)" ,

ORGUNIT5 char "RTRIM(:ORGUNIT5)" ,

ORGUNIT6 char "RTRIM(:ORGUNIT6)" ,

RPTCUSTOM1 char "RTRIM(:RPTCUSTOM1)" ,

RPTCUSTOM2 char "RTRIM(:RPTCUSTOM2)" ,

RPTCUSTOM3 char "RTRIM(:RPTCUSTOM3)" ,

RPTCUSTOM4 char "RTRIM(:RPTCUSTOM4)" ,

RPTCUSTOM5 char "RTRIM(:RPTCUSTOM5)" ,

RPTCUSTOM6 char "RTRIM(:RPTCUSTOM6)" ,

RPTCUSTOM7 char "RTRIM(:RPTCUSTOM7)" ,

RPTCUSTOM8 char "RTRIM(:RPTCUSTOM8)" ,

RPTCUSTOM9 char "RTRIM(:RPTCUSTOM9)" ,

RPTCUSTOM10 char "RTRIM(:RPTCUSTOM10)" ,

RPTCUSTOM11 char "RTRIM(:RPTCUSTOM11)" ,

RPTCUSTOM12 char "RTRIM(:RPTCUSTOM12)" ,

RPTCUSTOM13 char "RTRIM(:RPTCUSTOM13)" ,

RPTCUSTOM14 char "RTRIM(:RPTCUSTOM14)" ,

RPTCUSTOM15 char "RTRIM(:RPTCUSTOM15)" ,

RPTCUSTOM16 char "RTRIM(:RPTCUSTOM16)" ,

RPTCUSTOM17 char "RTRIM(:RPTCUSTOM17)" ,

RPTCUSTOM18 char "RTRIM(:RPTCUSTOM18)" ,

RPTCUSTOM19 char "RTRIM(:RPTCUSTOM19)" ,

RPTCUSTOM20 char "RTRIM(:RPTCUSTOM20)" ,

ENTRY_ID char "RTRIM(:ENTRY_ID)" ,

ENTRY_TXN_TYPE char "RTRIM(:ENTRY_TXN_TYPE)" ,

EXPENSE_TYPE char "RTRIM(:EXPENSE_TYPE)" ,

ENTRY_TXN_DATE char "RTRIM(:ENTRY_TXN_DATE)" ,

SPEND_CURRENCY_CODE char "RTRIM(:SPEND_CURRENCY_CODE)" ,

CURRENCY_EXCHANGE_RATE char "RTRIM(:CURRENCY_EXCHANGE_RATE)" ,

EXCHANGE_RATE_DIRECTION char "RTRIM(:EXCHANGE_RATE_DIRECTION)" ,

IS_PERSONAL char "RTRIM(:IS_PERSONAL)" ,

ENTRY_DESCRIPTION char "RTRIM(:ENTRY_DESCRIPTION)" ,

VENDOR_NAME char "RTRIM(:VENDOR_NAME)" ,

VENDOR_DESCRIPTION char "RTRIM(:VENDOR_DESCRIPTION)" ,

RECEIPT_RECEIVED char "RTRIM(:RECEIPT_RECEIVED)" ,

RECEIPT_TYPE char "RTRIM(:RECEIPT_TYPE)" ,

ATTENDEE_COUNT_EMP char "RTRIM(:ATTENDEE_COUNT_EMP)" ,

ATTENDEE_COUNT_SPOUSE char "RTRIM(:ATTENDEE_COUNT_SPOUSE)" ,

ATTENDEE_COUNT_BUSINESS char "RTRIM(:ATTENDEE_COUNT_BUSINESS)" ,

ENTRYORG1 char "RTRIM(:ENTRYORG1)" ,

ENTRYORG2 char "RTRIM(:ENTRYORG2)" ,

ENTRYORG3 char "RTRIM(:ENTRYORG3)" ,

ENTRYORG4 char "RTRIM(:ENTRYORG4)" ,

ENTRYORG5 char "RTRIM(:ENTRYORG5)" ,

ENTRYORG6 char "RTRIM(:ENTRYORG6)" ,

ENTRYCUSTOM1 char "RTRIM(:ENTRYCUSTOM1)" ,

ENTRYCUSTOM2 char "RTRIM(:ENTRYCUSTOM2)" ,

ENTRYCUSTOM3 char "RTRIM(:ENTRYCUSTOM3)" ,

ENTRYCUSTOM4 char "RTRIM(:ENTRYCUSTOM4)" ,

ENTRYCUSTOM5 char "RTRIM(:ENTRYCUSTOM5)" ,

ENTRYCUSTOM6 char "RTRIM(:ENTRYCUSTOM6)" ,

ENTRYCUSTOM7 char "RTRIM(:ENTRYCUSTOM7)" ,

ENTRYCUSTOM8 char "RTRIM(:ENTRYCUSTOM8)" ,

ENTRYCUSTOM9 char "RTRIM(:ENTRYCUSTOM9)" ,

ENTRYCUSTOM10 char "RTRIM(:ENTRYCUSTOM10)" ,

ENTRYCUSTOM11 char "RTRIM(:ENTRYCUSTOM11)" ,

ENTRYCUSTOM12 char "RTRIM(:ENTRYCUSTOM12)" ,

ENTRYCUSTOM13 char "RTRIM(:ENTRYCUSTOM13)" ,

ENTRYCUSTOM14 char "RTRIM(:ENTRYCUSTOM14)" ,

ENTRYCUSTOM15 char "RTRIM(:ENTRYCUSTOM15)" ,

ENTRYCUSTOM16 char "RTRIM(:ENTRYCUSTOM16)" ,

ENTRYCUSTOM17 char "RTRIM(:ENTRYCUSTOM17)" ,

ENTRYCUSTOM18 char "RTRIM(:ENTRYCUSTOM18)" ,

ENTRYCUSTOM19 char "RTRIM(:ENTRYCUSTOM19)" ,

ENTRYCUSTOM20 char "RTRIM(:ENTRYCUSTOM20)" ,

ENTRYCUSTOM21 char "RTRIM(:ENTRYCUSTOM21)" ,

ENTRYCUSTOM22 char "RTRIM(:ENTRYCUSTOM22)" ,

ENTRYCUSTOM23 char "RTRIM(:ENTRYCUSTOM23)" ,

ENTRYCUSTOM24 char "RTRIM(:ENTRYCUSTOM24)" ,

ENTRYCUSTOM25 char "RTRIM(:ENTRYCUSTOM25)" ,

ENTRYCUSTOM26 char "RTRIM(:ENTRYCUSTOM26)" ,

ENTRYCUSTOM27 char "RTRIM(:ENTRYCUSTOM27)" ,

ENTRYCUSTOM28 char "RTRIM(:ENTRYCUSTOM28)" ,

ENTRYCUSTOM29 char "RTRIM(:ENTRYCUSTOM29)" ,

ENTRYCUSTOM30 char "RTRIM(:ENTRYCUSTOM30)" ,

ENTRYCUSTOM31 char "RTRIM(:ENTRYCUSTOM31)" ,

ENTRYCUSTOM32 char "RTRIM(:ENTRYCUSTOM32)" ,

ENTRYCUSTOM33 char "RTRIM(:ENTRYCUSTOM33)" ,

ENTRYCUSTOM34 char "RTRIM(:ENTRYCUSTOM34)" ,

ENTRYCUSTOM35 char "RTRIM(:ENTRYCUSTOM35)" ,

ENTRYCUSTOM36 char "RTRIM(:ENTRYCUSTOM36)" ,

ENTRYCUSTOM37 char "RTRIM(:ENTRYCUSTOM37)" ,

ENTRYCUSTOM39 char "RTRIM(:ENTRYCUSTOM39)" ,

ENTRYCUSTOM40 char "RTRIM(:ENTRYCUSTOM40)" ,

TRANSAMOUNT char "RTRIM(:TRANSAMOUNT)" ,

POSTEDAMOUNT char "RTRIM(:POSTEDAMOUNT)" ,

APPROVEDAMOUNT char "RTRIM(:APPROVEDAMOUNT)" ,

FUTURE_USE6 char "RTRIM(:FUTURE_USE6)" ,

FUTURE_USE7 char "RTRIM(:FUTURE_USE7)" ,

FUTURE_USE8 char "RTRIM(:FUTURE_USE8)" ,

FUTURE_USE9 char "RTRIM(:FUTURE_USE9)" ,

CREDIT_CARD_ACCOUNT_NUMBER char "RTRIM(:CREDIT_CARD_ACCOUNT_NUMBER)" ,

NAME_ON_CARD char "RTRIM(:NAME_ON_CARD)" ,

TRANSACTION_RUN_KEY char "RTRIM(:TRANSACTION_RUN_KEY)" ,

REF_NO char "RTRIM(:REF_NO)" ,

CCT_KEY char "RTRIM(:CCT_KEY)" ,

CCT_TYPE char "RTRIM(:CCT_TYPE)" ,

TRANSACTION_ID char "RTRIM(:TRANSACTION_ID)" ,

TRANSACTION_AMOUNT char "RTRIM(:TRANSACTION_AMOUNT)" ,

TRANSACTION_TAX_AMOUNT char "RTRIM(:TRANSACTION_TAX_AMOUNT)" ,

TRANSACTION_CURRENCY char "RTRIM(:TRANSACTION_CURRENCY)" ,

TRANSACTION_POSTED_AMOUNT char "RTRIM(:TRANSACTION_POSTED_AMOUNT)" ,

TRANSACTION_POSTED_CURRENCY char "RTRIM(:TRANSACTION_POSTED_CURRENCY)" ,

TRANSACTION_DATE char "RTRIM(:TRANSACTION_DATE)" ,

TRANSACTION_POSTED_DATE char "RTRIM(:TRANSACTION_POSTED_DATE)" ,

TRANSACTION_DESCRIPTION char "RTRIM(:TRANSACTION_DESCRIPTION)" ,

MCC_CODE char "RTRIM(:MCC_CODE)" ,

TXN_MERCHENT_NAME char "RTRIM(:TXN_MERCHENT_NAME)" ,

TXN_MERCHANT_CITY char "RTRIM(:TXN_MERCHANT_CITY)" ,

TXN_MERCHANT_STATE char "RTRIM(:TXN_MERCHANT_STATE)" ,

TXN_MERCHANT_COUNTRY char "RTRIM(:TXN_MERCHANT_COUNTRY)" ,

TXN_MERCHANT_REF_NUM char "RTRIM(:TXN_MERCHANT_REF_NUM)" ,

TXN_BILLING_TYPE char "RTRIM(:TXN_BILLING_TYPE)" ,

FUTURE_USE10 char "RTRIM(:FUTURE_USE10)" ,

FUTURE_USE11 char "RTRIM(:FUTURE_USE11)" ,

FUTURE_USE12 char "RTRIM(:FUTURE_USE12)" ,

FUTURE_USE13 char "RTRIM(:FUTURE_USE13)" ,

FUTURE_USE14 char "RTRIM(:FUTURE_USE14)" ,

FUTURE_USE15 char "RTRIM(:FUTURE_USE15)" ,

ENTRY_COUNTRY char "RTRIM(:ENTRY_COUNTRY)" ,

ENTRY_COUNTRY_SUB char "RTRIM(:ENTRY_COUNTRY_SUB)" ,

DOMESTIC_FOREIGN char "RTRIM(:DOMESTIC_FOREIGN)" ,

FUTURE_USE16 char "RTRIM(:FUTURE_USE16)" ,

FUTURE_USE17 char "RTRIM(:FUTURE_USE17)" ,

PAYERPAYTYPE char "RTRIM(:PAYERPAYTYPE)" ,

PAYERPAYCODE char "RTRIM(:PAYERPAYCODE)" ,

PAYEEPAYTYPE char "RTRIM(:PAYEEPAYTYPE)" ,

PAYEEPAYCODE char "RTRIM(:PAYEEPAYCODE)" ,

ACCT_NUM char "RTRIM(:ACCT_NUM)" ,

DRCR char "RTRIM(:DRCR)" ,

JOURNAL_AMT char "RTRIM(:JOURNAL_AMT)" ,

FUTURE_USE18 char "RTRIM(:FUTURE_USE18)" ,

CAR_BUSINESS_DISTANCE char "RTRIM(:CAR_BUSINESS_DISTANCE)" ,

CAR_PERSONAL_DISTANCE char "RTRIM(:CAR_PERSONAL_DISTANCE)" ,

CAR_PASSANGER_COUNT char "RTRIM(:CAR_PASSANGER_COUNT)" ,

VEHICLE_ID char "RTRIM(:VEHICLE_ID)" ,

FUTURE_USE19 char "RTRIM(:FUTURE_USE19)" ,

FUTURE_USE20 char "RTRIM(:FUTURE_USE20)" ,

CASH_ADVANCE_AMOUNT char "RTRIM(:CASH_ADVANCE_AMOUNT)" ,

CA_REQUEST_CURRENCY_ALPHA_ISO char "RTRIM(:CA_REQUEST_CURRENCY_ALPHA_ISO)",

CA_REQUEST_CURRENCY_NUM_ISO char "RTRIM(:CA_REQUEST_CURRENCY_NUM_ISO)" ,

CASH_ADVANCE_EXCHANGE_RATE char "RTRIM(:CASH_ADVANCE_EXCHANGE_RATE)" ,

CA_CURRENCY_ALPHA_ISO char "RTRIM(:CA_CURRENCY_ALPHA_ISO)" ,

CA_CURRENCY_NUM_ISO char "RTRIM(:CA_CURRENCY_NUM_ISO)" ,

CASH_ADVANCE_ISSUE_DATE char "RTRIM(:CASH_ADVANCE_ISSUE_DATE)" ,

CASH_ADVANCE_PAYMENT_CODE char "RTRIM(:CASH_ADVANCE_PAYMENT_CODE)" ,

CASH_ADVANCE_TRANSACTION_TYPE char "RTRIM(:CASH_ADVANCE_TRANSACTION_TYPE)",

CASH_ADVANCE_REQUEST_DATE char "RTRIM(:CASH_ADVANCE_REQUEST_DATE)" ,

FUTURE_USE21 char "RTRIM(:FUTURE_USE21)" ,

FUTURE_USE22 char "RTRIM(:FUTURE_USE22)" ,

CT_ALLOCATION_ALLOC_KEY char "RTRIM(:CT_ALLOCATION_ALLOC_KEY)" ,

PERCENTAGE char "RTRIM(:PERCENTAGE)" ,

CTA_CUSTOM1 char "RTRIM(:CTA_CUSTOM1)" ,

CTA_CUSTOM2 char "RTRIM(:CTA_CUSTOM2)" ,

CTA_CUSTOM3 char "RTRIM(:CTA_CUSTOM3)" ,

CTA_CUSTOM4 char "RTRIM(:CTA_CUSTOM4)" ,

CTA_CUSTOM5 char "RTRIM(:CTA_CUSTOM5)" ,

CTA_CUSTOM6 char "RTRIM(:CTA_CUSTOM6)" ,

CTA_CUSTOM7 char "RTRIM(:CTA_CUSTOM7)" ,

CTA_CUSTOM8 char "RTRIM(:CTA_CUSTOM8)" ,

CTA_CUSTOM9 char "RTRIM(:CTA_CUSTOM9)" ,

CTA_CUSTOM10 char "RTRIM(:CTA_CUSTOM10)" ,

CTA_CUSTOM11 char "RTRIM(:CTA_CUSTOM11)" ,

CTA_CUSTOM12 char "RTRIM(:CTA_CUSTOM12)" ,

CTA_CUSTOM13 char "RTRIM(:CTA_CUSTOM13)" ,

CTA_CUSTOM14 char "RTRIM(:CTA_CUSTOM14)" ,

CTA_CUSTOM15 char "RTRIM(:CTA_CUSTOM15)" ,

CTA_CUSTOM16 char "RTRIM(:CTA_CUSTOM16)" ,

CTA_CUSTOM17 char "RTRIM(:CTA_CUSTOM17)" ,

CTA_CUSTOM18 char "RTRIM(:CTA_CUSTOM18)" ,

CTA_CUSTOM19 char "RTRIM(:CTA_CUSTOM19)" ,

CTA_CUSTOM20 char "RTRIM(:CTA_CUSTOM20)" ,

FUTURE_USE23 char "RTRIM(:FUTURE_USE23)" ,

TA_REIMBURSE_TYPE char "RTRIM(:TA_REIMBURSE_TYPE)" ,

TA_DAILY_LIMIT char "RTRIM(:TA_DAILY_LIMIT)" ,

TA_ALLOWANCE_LIMIT char "RTRIM(:TA_ALLOWANCE_LIMIT)" ,

TA_OVER_LIMIT char "RTRIM(:TA_OVER_LIMIT)" ,

TA_FIXED_TYPE char "RTRIM(:TA_FIXED_TYPE)" ,

TA_FIXED_BASE_AMOUNT char "RTRIM(:TA_FIXED_BASE_AMOUNT)" ,

TA_FIXED_ALLOWANCE_AMOUNT char "RTRIM(:TA_FIXED_ALLOWANCE_AMOUNT)" ,

TA_OVERNIGHT char "RTRIM(:TA_OVERNIGHT)" ,

TA_BREAKFAST_PROVIDED char "RTRIM(:TA_BREAKFAST_PROVIDED)" ,

TA_LUNCH_PROVIDED char "RTRIM(:TA_LUNCH_PROVIDED)" ,

TA_DINNER_PROVIDED char "RTRIM(:TA_DINNER_PROVIDED)" ,

FUTURE_USE24 char "RTRIM(:FUTURE_USE24)" ,

FUTURE_USE25 char "RTRIM(:FUTURE_USE25)" ,

TAX_AUTHORITY_NAME char "RTRIM(:TAX_AUTHORITY_NAME)" ,

TAX_LABEL char "RTRIM(:TAX_LABEL)" ,

ET_TRANSACTION_AMOUNT char "RTRIM(:ET_TRANSACTION_AMOUNT)" ,

ET_POSTED_AMOUNT char "RTRIM(:ET_POSTED_AMOUNT)" ,

ET_SOURCE char "RTRIM(:ET_SOURCE)" ,

TAX_RECLAIM_TXN_AMOUNT char "RTRIM(:TAX_RECLAIM_TXN_AMOUNT)" ,

CT_RPT_RECLAIM_POSTED_AMOUNT char "RTRIM(:CT_RPT_RECLAIM_POSTED_AMOUNT)" ,

TAX_CODE char "RTRIM(:TAX_CODE)" ,

RECLAIM_DOMESTIC char "RTRIM(:RECLAIM_DOMESTIC)" ,

ET_ADJUSTED_AMOUNT char "RTRIM(:ET_ADJUSTED_AMOUNT)" ,

ET_RECLAIM_ADJUSTED_AMOUNT char "RTRIM(:ET_RECLAIM_ADJUSTED_AMOUNT)" ,

ET_RECLAIM_CODE char "RTRIM(:ET_RECLAIM_CODE)" ,

ET_RECLAIM_TRANS_ADJ_AMT char "RTRIM(:ET_RECLAIM_TRANS_ADJ_AMT)" ,

FUTURE_USE26 char "RTRIM(:FUTURE_USE26)" ,

FUTURE_USE27 char "RTRIM(:FUTURE_USE27)" ,

TR_NAME char "RTRIM(:TR_NAME)" ,

TR_TOTAL_POSTED_AMOUNT char "RTRIM(:TR_TOTAL_POSTED_AMOUNT)" ,

TR_APPROVED_AMOUNT char "RTRIM(:TR_APPROVED_AMOUNT)" ,

TR_START_DATE char "RTRIM(:TR_START_DATE)" ,

TR_END_DATE char "RTRIM(:TR_END_DATE)" ,

TR_AUTHORIZED_DATE char "RTRIM(:TR_AUTHORIZED_DATE)" ,

RUN_ID "NORD_EMS_RUN_ID_S.CURRVAL",

LAST_UPDATE_DATE SYSDATE,

LAST_UPDATED_BY CONSTANT 1108,

PROCESS_STATUS CONSTANT 'NEW',

REQUEST_ID CONSTANT '-1'

)

***********************************************************************************************

log file indicates load failed for when clause of the second table below (in RED):

Table "NORD"."NORD_EMS_SAE_LOAD_HEADER":

1 Row successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Tow not loaded because all WHEN clauses were failed.

0 Rows successfully loaded.

0 Rows not loaded due to data errors.

1 Row not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Space allocated for bind array: 19981104 bytes(312 rows)

Read buffer bytes:20000000

Total logical records skipped: 0

Total logical records read: 1

Total logical records rejected: 0

Total logical records discarded: 0

Run began on Mon Aug 13 15:23:58 2018

Run ended on Mon Aug 13 15:23:59 2018

Elapsed time was: 00:00:01.15

CPU time was: 00:00:00.12

End of Log File Details

\n--------------------------------------------

Loading process is successful

\nDeleting the data file after loading process

\nLoad Data Process Completed

End of main function: 13-Aug-18[15:23:59]

+---------------------------------------------------------------------------+

No completion options were requested.

Output file size:

+---------------------------------------------------------------------------+

Concurrent request completed successfully

Current system time is 13-AUG-2018 15:23:59

+---------------------------------------------------------------------------+

here is sample data

EXTRACT|2018-07-25|2398|297938.07

DETAIL|4164|2018-07-25|1|10021046|burgueno|kaleb||REST|200|0426|18020|0000|0000|0000||||7B3B9402BF044AE58113|1013338|Nord|USD|UNITED STATES|2018-07-23|2018-07-22|2018-07-25|Commute to hillsdale for coverag|N|N|N|28.34|28.34|||200|0420|18020|0000|0000|0000|||8021||||1585898||||||||||||||11909364|REG|Mileage - Meeting - 77391|2018-07-22|USD|1.00|M|N||||N|N||||||Coverage||||||||Y||||||||||||||||||||||||||||||||||0.00|28.34|28.34|28.34|28.34|||||||||||||||||||||||||||||||||||HOME|||Company|Company/Employee Pseudo Payment Code|Employee|Company/Employee Pseudo Payment Code|77391|DR|+28.34||52||||||||||||||||||15218937|100.00|200|0420|18020|0000|0000|0000||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

DETAIL|4164|2018-07-25|2|10040202|Johnston|Sarah|S|UNASSIGNED|500|5210|46108|0000|0000|0000||||ABA8891BFF6048998F42|1007840|Nord|USD|UNITED STATES|2018-07-19|2018-07-02|2018-07-24|June Expenses (06/01/2018)|N|N|N|426.98|426.98|||500|5210|46108|0000|0000|0000|||8882||||1590110||||||||||||||11852181|REG|Airfare - 77311|2018-06-27|USD|1.00|M|N||Alaska Airlines|Alaska Airlines|N|N|||||||||||||||||||||2018-07-10|JOHNSTON/SARAH SUSAN||||||||||||||||||||||US||||0.00|415.59|415.59|415.59|415.59|||||YYYYYYYYYYYYYYYY|SARAH JOHNSTON|344010|24431068179331901035451-1|7156122|RPE|503f118|415.59||USD|415.59|USD|2018-06-27|2018-06-29||3256|ALASKA AIR 0277189680484|SEATTLE|WA|US|||||||||US|US-WA|HOME|||Company|Company/Employee Pseudo Payment Code|US Bank Corporate Card|Company Billed/Company Paid|77311|DR|+415.59||||||||||||||||||||15142460|100.00|500|5210|46108|0000|0000|0000||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

DETAIL|4164|2018-07-25|3|10040202|Johnston|Sarah|S|UNASSIGNED|500|5210|46108|0000|0000|0000||||ABA8891BFF6048998F42|1007840|Nord|USD|UNITED STATES|2018-07-19|2018-07-02|2018-07-24|June Expenses (06/01/2018)|N|N|N|426.98|426.98|||500|5210|46108|0000|0000|0000|||8882||||1590110||||||||||||||11866521|REG|Non-car rental transportation - 77331|2018-06-29|USD|1.00|M|N|||LYFT *RIDE FRI 8AM|N|N||||||Local Market||||||||||||N||||||||||||||||||||||||||||||0.00|11.39|11.39|11.39|11.39|||||YYYYYYYYYYYYYYYY|SARAH JOHNSTON|344052|24492158180637019539123-1|7159784|RPE|ddbe2ccf|11.39||USD|11.39|USD|2018-06-29|2018-07-02||4121|LYFT *RIDE FRI 8AM|LYFT.COM|CA|US|||||||||||HOME|||Company|Company/Employee Pseudo Payment Code|US Bank Corporate Card|Company Billed/Company Paid|77331|DR|+11.39||||||||||||||||||||15163403|100.00|500|5210|46108|0000|0000|0000||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

DETAIL|4164|2018-07-25|4|10043594|Jewell|Kevin|M|UNASSIGNED|101|0182|45325|0000|0000|0000||||4F096664C33D4DE1A568|1005256|Nord|USD|UNITED STATES|2018-07-24|2018-06-22|2018-07-24|July 2018 Report|N|N|N|90.91|90.91|||101|0182|45325|0000|0000|0000|||2899||||1590670||||||||||||||11820360|REG|Non-Travel Food/Bev (no gift cards) ? 79839|2018-06-14|USD|1.00|M|N|||TST* VICTROLA COFFEE ROAS|N|N||||||Employee appreciation||||||||||||N|||||||||||Employee appreciation|||||||||||||||US||||0.00|63.66|63.66|63.66|63.66|||||YYYYYYYYYYYYYYYY|KEVIN JEWELL|343811|24445008165300451472436-1|7128298|RPE|4f03aabc|63.66||USD|63.66|USD|2018-06-14|2018-06-15||5812|TST* VICTROLA COFFEE ROAS|SEATTLE|WA|US|||||||||US|US-WA|HOME|||Company|Company/Employee Pseudo Payment Code|US Bank Corporate Card|Company Billed/Company Paid|79839|DR|+63.66||||||||||||||||||||15099100|100.00|101|0182|45325|0000|0000|0000||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

DETAIL|4164|2018-07-25|5|10043594|Jewell|Kevin|M|UNASSIGNED|101|0182|45325|0000|0000|0000||||4F096664C33D4DE1A568|1005256|Nord|USD|UNITED STATES|2018-07-24|2018-06-22|2018-07-24|July 2018 Report|N|N|N|90.91|90.91|||101|0182|45325|0000|0000|0000|||2899||||1590670||||||||||||||11917076|REG|Non-car rental transportation - 77331|2018-07-12|USD|1.00|M|N|||LYFT *RIDE WED 8PM|N|N||||||Non-car rental transportation||||||||||||N||||||||||||||||||||||||||||||0.00|14.19|14.19|14.19|14.19|||||YYYYYYYYYYYYYYYY|KEVIN JEWELL|344208|24492158193637524960827-1|7175699|RPE|ef85d617|14.19||USD|14.19|USD|2018-07-12|2018-07-13||4121|LYFT *RIDE WED 8PM|LYFT.COM|CA|US|||||||||||HOME|||Company|Company/Employee Pseudo Payment Code|US Bank Corporate Card|Company Billed/Company Paid|77331|DR|+14.19||||||||||||||||||||15229290|100.00|101|0182|45325|0000|0000|0000||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

DETAIL|4164|2018-07-25|6|10043594|Jewell|Kevin|M|UNASSIGNED|101|0182|45325|0000|0000|0000||||4F096664C33D4DE1A568|1005256|Nord|USD|UNITED STATES|2018-07-24|2018-06-22|2018-07-24|July 2018 Report|N|N|N|90.91|90.91|||101|0182|45325|0000|0000|0000|||2899||||1590670||||||||||||||11917077|REG|Non-car rental transportation - 77331|2018-07-12|USD|1.00|M|N|||LYFT *RIDE THU 3AM|N|N||||||Non-car rental transportation||||||||||||N||||||||||||||||||||||||||||||0.00|13.06|13.06|13.06|13.06|||||YYYYYYYYYYYYYYYY|KEVIN JEWELL|344208|24492158193637525161359-2|7175700|RPE|107b46d4|13.06||USD|13.06|USD|2018-07-12|2018-07-13||4121|LYFT *RIDE THU 3AM|LYFT.COM|CA|US|||||||||||HOME|||Company|Company/Employee Pseudo Payment Code|US Bank Corporate Card|Company Billed/Company Paid|77331|DR|+13.06||||||||||||||||||||15229291|100.00|101|0182|45325|0000|0000|0000||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||

AND FINALLY THE TABLES where data will be stored

NORD_EMS_SAE_LOAD_HEADER

Name Null? Type

------------------- ----- --------------

RUN_ID NUMBER

RECORD_TYPE VARCHAR2(15)

BATCH_DATE VARCHAR2(10)

TOTAL_DETAIL_LINES VARCHAR2(15)

TOTAL_DETAIL_AMOUNT VARCHAR2(15)

LAST_UPDATE_DATE DATE

LAST_UPDATED_BY NUMBER

PROCESS_STATUS VARCHAR2(35)

ERROR_MESSAGE VARCHAR2(2000)

REQUEST_ID NUMBER

NORD_EMS_SAE_LOAD_DETAIL

Name Null? Type

----------------------------- ----- --------------

RUN_ID NUMBER

RECORD_TYPE VARCHAR2(15)

BATCH_ID VARCHAR2(15)

BATCH_DATE VARCHAR2(15)

SEQUENCE_NUMBER VARCHAR2(15)

EMP_ID VARCHAR2(15)

LAST_NAME VARCHAR2(40)

FIRST_NAME VARCHAR2(40)

MI VARCHAR2(15)

GROUP_ID VARCHAR2(15)

EMP_ORG1 VARCHAR2(15)

EMP_ORG2 VARCHAR2(15)

EMP_ORG3 VARCHAR2(15)

EMP_ORG4 VARCHAR2(15)

EMP_ORG5 VARCHAR2(15)

EMP_ORG6 VARCHAR2(15)

FUTURE_USE1 VARCHAR2(15)

FUTURE_USE2 VARCHAR2(15)

FUTURE_USE3 VARCHAR2(15)

REPORT_ID VARCHAR2(25)

REPORT_KEY VARCHAR2(15)

INVOICE_NUMBER VARCHAR2(35)

LEDGER VARCHAR2(15)

REIMBURSMENT_CURRENCY VARCHAR2(15)

HOME_COUNTRY VARCHAR2(15)

RPT_SUBMIT_DATE VARCHAR2(15)

RPT_USER_DEFINED_DATE VARCHAR2(10)

RPT_PAYMENT_PROCESSING_DATE VARCHAR2(10)

RPT_NAME VARCHAR2(240)

RPT_IMAGE_REQUIRED VARCHAR2(15)

RPT_HAS_VAT_ENTRY VARCHAR2(15)

RPT_HAS_TA_ENTRY VARCHAR2(15)

TOTAL_POSTED_AMOUNT VARCHAR2(15)

CT_TOTAL_APPROVED VARCHAR2(15)

FUTURE_USE4 VARCHAR2(15)

FUTURE_USE5 VARCHAR2(15)

ORGUNIT1 VARCHAR2(15)

ORGUNIT2 VARCHAR2(15)

ORGUNIT3 VARCHAR2(15)

ORGUNIT4 VARCHAR2(15)

ORGUNIT5 VARCHAR2(15)

ORGUNIT6 VARCHAR2(15)

RPTCUSTOM1 VARCHAR2(15)

RPTCUSTOM2 VARCHAR2(15)

RPTCUSTOM3 VARCHAR2(15)

RPTCUSTOM4 VARCHAR2(15)

RPTCUSTOM5 VARCHAR2(15)

RPTCUSTOM6 VARCHAR2(15)

RPTCUSTOM7 VARCHAR2(15)

RPTCUSTOM8 VARCHAR2(15)

RPTCUSTOM9 VARCHAR2(15)

RPTCUSTOM10 VARCHAR2(15)

RPTCUSTOM11 VARCHAR2(15)

RPTCUSTOM12 VARCHAR2(15)

RPTCUSTOM13 VARCHAR2(15)

RPTCUSTOM14 VARCHAR2(15)

RPTCUSTOM15 VARCHAR2(15)

RPTCUSTOM16 VARCHAR2(15)

RPTCUSTOM17 VARCHAR2(15)

RPTCUSTOM18 VARCHAR2(15)

RPTCUSTOM19 VARCHAR2(15)

RPTCUSTOM20 VARCHAR2(15)

ENTRY_ID VARCHAR2(15)

ENTRY_TXN_TYPE VARCHAR2(30)

EXPENSE_TYPE VARCHAR2(50)

ENTRY_TXN_DATE VARCHAR2(10)

SPEND_CURRENCY_CODE VARCHAR2(15)

CURRENCY_EXCHANGE_RATE VARCHAR2(15)

EXCHANGE_RATE_DIRECTION VARCHAR2(15)

IS_PERSONAL VARCHAR2(15)

ENTRY_DESCRIPTION VARCHAR2(255)

VENDOR_NAME VARCHAR2(40)

VENDOR_DESCRIPTION VARCHAR2(80)

RECEIPT_RECEIVED VARCHAR2(15)

RECEIPT_TYPE VARCHAR2(15)

ATTENDEE_COUNT_EMP VARCHAR2(15)

ATTENDEE_COUNT_SPOUSE VARCHAR2(15)

ATTENDEE_COUNT_BUSINESS VARCHAR2(15)

ENTRYORG1 VARCHAR2(140)

ENTRYORG2 VARCHAR2(140)

ENTRYORG3 VARCHAR2(140)

ENTRYORG4 VARCHAR2(140)

ENTRYORG5 VARCHAR2(140)

ENTRYORG6 VARCHAR2(140)

ENTRYCUSTOM1 VARCHAR2(25)

ENTRYCUSTOM2 VARCHAR2(25)

ENTRYCUSTOM3 VARCHAR2(25)

ENTRYCUSTOM4 VARCHAR2(25)

ENTRYCUSTOM5 VARCHAR2(25)

ENTRYCUSTOM6 VARCHAR2(25)

ENTRYCUSTOM7 VARCHAR2(25)

ENTRYCUSTOM8 VARCHAR2(25)

ENTRYCUSTOM9 VARCHAR2(25)

ENTRYCUSTOM10 VARCHAR2(25)

ENTRYCUSTOM11 VARCHAR2(25)

ENTRYCUSTOM12 VARCHAR2(25)

ENTRYCUSTOM13 VARCHAR2(25)

ENTRYCUSTOM14 VARCHAR2(25)

ENTRYCUSTOM15 VARCHAR2(25)

ENTRYCUSTOM16 VARCHAR2(25)

ENTRYCUSTOM17 VARCHAR2(25)

ENTRYCUSTOM18 VARCHAR2(25)

ENTRYCUSTOM19 VARCHAR2(25)

ENTRYCUSTOM20 VARCHAR2(40)

ENTRYCUSTOM21 VARCHAR2(80)

ENTRYCUSTOM22 VARCHAR2(80)

ENTRYCUSTOM23 VARCHAR2(80)

ENTRYCUSTOM24 VARCHAR2(80)

ENTRYCUSTOM25 VARCHAR2(80)

ENTRYCUSTOM26 VARCHAR2(80)

ENTRYCUSTOM27 VARCHAR2(25)

ENTRYCUSTOM28 VARCHAR2(25)

ENTRYCUSTOM29 VARCHAR2(25)

ENTRYCUSTOM30 VARCHAR2(25)

ENTRYCUSTOM31 VARCHAR2(25)

ENTRYCUSTOM32 VARCHAR2(25)

ENTRYCUSTOM33 VARCHAR2(25)

ENTRYCUSTOM34 VARCHAR2(25)

ENTRYCUSTOM35 VARCHAR2(25)

ENTRYCUSTOM36 VARCHAR2(25)

ENTRYCUSTOM37 VARCHAR2(25)

ENTRYCUSTOM38 VARCHAR2(25)

ENTRYCUSTOM39 VARCHAR2(25)

ENTRYCUSTOM40 VARCHAR2(25)

TRANSAMOUNT VARCHAR2(15)

POSTEDAMOUNT VARCHAR2(15)

APPROVEDAMOUNT VARCHAR2(15)

FUTURE_USE6 VARCHAR2(15)

FUTURE_USE7 VARCHAR2(15)

FUTURE_USE8 VARCHAR2(15)

FUTURE_USE9 VARCHAR2(15)

CREDIT_CARD_ACCOUNT_NUMBER VARCHAR2(25)

NAME_ON_CARD VARCHAR2(50)

TRANSACTION_RUN_KEY VARCHAR2(15)

REF_NO VARCHAR2(40)

CCT_KEY VARCHAR2(15)

CCT_TYPE VARCHAR2(15)

TRANSACTION_ID VARCHAR2(15)

TRANSACTION_AMOUNT VARCHAR2(15)

TRANSACTION_TAX_AMOUNT VARCHAR2(15)

TRANSACTION_CURRENCY VARCHAR2(15)

TRANSACTION_POSTED_AMOUNT VARCHAR2(15)

TRANSACTION_POSTED_CURRENCY VARCHAR2(15)

TRANSACTION_DATE VARCHAR2(10)

TRANSACTION_POSTED_DATE VARCHAR2(10)

TRANSACTION_DESCRIPTION VARCHAR2(35)

MCC_CODE VARCHAR2(15)

TXN_MERCHENT_NAME VARCHAR2(25)

TXN_MERCHANT_CITY VARCHAR2(15)

TXN_MERCHANT_STATE VARCHAR2(15)

TXN_MERCHANT_COUNTRY VARCHAR2(15)

TXN_MERCHANT_REF_NUM VARCHAR2(15)

TXN_BILLING_TYPE VARCHAR2(15)

FUTURE_USE10 VARCHAR2(15)

FUTURE_USE11 VARCHAR2(15)

This post has been answered by KayK on Aug 14 2018

Jump to Answer

Locked Post

New comments cannot be posted to this locked post.

Locked on Sep 15 2018

Added on Aug 13 2018

#database-key-features, #export-import-sql-loader-external-tables

9 comments

2,977 views