Skip to Main Content

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

\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]

\n

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

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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Sep 15 2018
Added on Aug 13 2018
9 comments
2,977 views