Tag Archives: CTL File

Bank Statement SQL Loader Scripts

Before uploading bank statement file (SQL Loader data file), ensure that the data file exactly maps to the lines.ctl and the headers.ctl.

Sample control files (Files may need to altered to best match statement)
 # HEADER.CTL
LOAD DATA
APPEND INTO TABLE ce_statement_headers_int_all
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( BANK_ACCOUNT_NUM CONSTANT 'XXXXXXXXXXXX',
#ADD BANK ACCT NUMBER ORG_ID CONSTANT '23',
#DETERMINE ORG ID STATEMENT_NUMBER,
STATEMENT_DATE "TO_DATE(:STATEMENT_DATE,'YYYYMMDD')",
DUMMY_COLUMN FILLER,
DUMMY_COLUMN2 FILLER,
DUMMY_COLUMN3 FILLER,
DUMMY_COLUMN4 FILLER,
DUMMY_COLUMN5 FILLER,
DUMMY_COLUMN6 FILLER,
DUMMY_COLUMN7 FILLER,
DUMMY_COLUMN8 FILLER,
DUMMY_COLUMN9 FILLER,
DUMMY_COLUMN10 FILLER,
CURRENCY_CODE CONSTANT 'TTD' )

# LINES.CTL
load data
append into table ce_statement_lines_interface
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
( STATEMENT_NUMBER,
TRX_DATE "TO_DATE(:TRX_DATE, 'YYYYMMDD')",
BANK_ACCOUNT_NUM CONSTANT 'XXXXXXXXXXXX',
#ADD BANK ACCT NUMBER LINE_NUMBER SEQUENCE(COUNT),
currency_code constant 'TTD',
dummy_filler2 filler,
dummy_filler3 filler,
dummy_filler4 filler,
dummy_filler1 filler,
TRX_CODE,
TRX_TEXT,
AMOUNT,
BANK_TRX_NUMBER,
dummy_filler5 filler,
dummy_filler6 filler )

Save the data file as a .csv file.

Note:

To determine org_id, issue the following command in SQL*Plus:

Select ORGANIZATION_ID, NAME From HR_ALL_ORGANIZATION_UNITS;