, , , , , ,

How to Create and Register a Concurrent Program using SQL*Loader

When building customization’s for the Oracle E-Business Suite it might be needed to load data from external sources into specific tables. Tables might be seeded tables, i.e. Open Interface tables, or custom tables which you’ve added to a special customization’s database schema.

To load data from external sources into Oracle eBS you might consider using SQL*Loader to accomplish this. SQL*Loader is using comma seperated (csv) files with data and a so called control file (ctl). The control file tells the system how to import the csv file with data. The control file describes the table and columns to be loaded, what the seperator is of the incoming file etc. Next the SQL*Loader program exports a log file to give an overview of the process, a bad file of records that caused errors in the process, and a discard file for records that were not selected during the load.


Starting a SQL*Loader load can be done by command line by executing the below:

sqlldr db_user/db_password@host:port/sid control=control_file_name.ctl log=log_file_name.log

SQL*Loader can also be executed by starting a special concurrent program which you can create in Oracle E-Business Suite. Below the steps how to do this.

1) First of all you need to have a csv file with data – the csv can also contain header information. The header in a csv file can be skipped by adding a special parameter in the control file. Take note of the columns in the csv file.

2) You need a control file – for example see the below control file. There are a big number of commands you can use in the control file to completely have control on how data gets loaded into tables. If you want to know more than contact me on this topic.

SKIP = 1
LOAD DATA
INFILE ‘data_file_name.csv’
BADFILE ‘data_file_name.bad’
REPLACE INTO TABLE XXX.YOUR_CUSTOM_TABLE
FIELDS TERMINATED BY “,” OPTIONALLY ENCLOSED BY ‘”‘
TRAILING NULLCOLS
(
COLUMN1,COLUMN2,COLUMN3,…
)

SKIP=1
Tells SQL*Loader to exclude row 1 in the data file (to exclude the headers)

INFILE
Specifies the name of the incoming data csv file

BADFILE
Determines the bad file generated for any errors occured during the load

REPLACE
This command will first truncate the table and than add the records. If you change this in an APPEND command the records will be added only to the table specified without truncating first

FIELDS TERMINATED BY
Determines the seperator used in the data csv file

OPTIONALLY ENCLOSED BY
Determines an additional enclosing character like for example ” if you’re adding data which contains the column seperator used

TRAILING NULLCOLS
Is used to treat any missing data in the csv file as NULL for the table to be loaded

COLUMN1, COLUMN2, COLUMN3, …
Gives the column names to be loaded

3) Create the SQL*Loader Executable in Oracle E-Business Suite. Go to responsibility System Administrator – Concurrent – Program – Executable.


Give you executable a name, shortname and assign it to your customizations application. A description is optionally. Select Execution Method SQL*Loader to let eBS know SQL*Loader needs to be started. The Execution File Name holds the name of the control file you want to start (exclude the extension ctl here). The control file needs to be located in the bin directory of your customization application.

4) Create the concurrent program in Oracle E-Business Suite. Go to responsibility System Administrator – Concurrent – Program – Define.


Give your concurrent program a name, a short name, assign it to your customization’s application and optionally provide a description. Assign your created executable to the concurrent program.

5) Add parameters to dynamically provide the incoming data csv file. Click on Parameters.


Add Sequence 10 and give the parameter a name. In this we want to provide a full path to the incoming data csv file so we use seeded Value Set 100 Characters to hold the path. Optionally add a default value.

6) When done creating the concurrent program add it to a Concurrent Request Group and start loading data in your tables.

/by
, , , , ,

Payables Open Interface’s Control File

load data infile ‘AP_INV.TXT’
append into table    AP_INVOICES_INTERFACE
fields terminated by ‘^’ optionally enclosed by ‘”‘
trailing nullcols
    (INVOICE_NUM              CHAR “RTRIM(:INVOICE_NUM)”,
     INVOICE_TYPE_LOOKUP_CODE   CHAR “RTRIM(:INVOICE_TYPE_LOOKUP_CODE)”,
     INVOICE_DATE               DATE “DD-MON-RRRR”,
     VENDOR_NUM                 CHAR “RTRIM(:VENDOR_NUM)”,
     VENDOR_SITE_CODE           FILLER,
     INVOICE_AMOUNT             CHAR “RTRIM(:INVOICE_AMOUNT)”,
     TERMS_NAME                 CHAR “RTRIM(:TERMS_NAME)”,
     DESCRIPTION                CHAR “RTRIM(:DESCRIPTION)”,
     DUMMY1 FILLER,
     DUMMY2 FILLER,
     DUMMY3 FILLER,
     DUMMY4 FILLER,
     GL_DATE  DATE “TO_DATE((TO_CHAR(:GL_DATE,’DD-‘)||DECODE(TO_CHAR(:GL_DATE,’MON’),’AUG’,’SEP’,’SEP’,’SEP’,’OCT’,’OCT’,TO_CHAR(:GL_DATE,’MON’))||TO_CHAR(:GL_DATE,’-YYYY’)))”,
     VOUCHER_NUM                CHAR “RTRIM(:VOUCHER_NUM)”,
     INVOICE_RECEIVED_DATE      DATE,
     AMOUNT_APPLICABLE_TO_DISCOUNT,
     TERMS_DATE                 DATE,
     SOURCE                     CONSTANT ‘MAXCIM INVOICE’,
     PAYMENT_CURRENCY_CODE      CONSTANT ‘USD’,
     PAYMENT_METHOD_LOOKUP_CODE CONSTANT ‘CHECK’,
     CALC_TAX_DURING_IMPORT_FLAG CONSTANT ‘N’,
     ORG_ID                     CONSTANT ‘166’,
     SHIP_TO_LOCATION           CONSTANT ‘Materials Warehouse’,
     INVOICE_CURRENCY_CODE      CONSTANT ‘USD’,
     creation_date              SYSDATE,
     last_updated_by            CONSTANT 1093,
     last_update_date           SYSDATE,
     created_by                 CONSTANT 1093,
     INVOICE_ID “AP_INVOICES_INTERFACE_S.NEXTVAL”)

load data infile ‘AP_LINE_TEST.TXT’
append into table    AP_INVOICE_LINES_INTERFACE
fields terminated by ‘^’ optionally enclosed by ‘”‘
trailing nullcols
    (REFERENCE_2              CHAR “RTRIM(:REFERENCE_2)”,
     TYPE              FILLER,
     ACCOUNTING_DATE         DATE “DD-MON-RRRR”,
     DESCRIPTION          CHAR “RTRIM(:DESCRIPTION)”,
     DIST_CODE_COMBINATION_ID          CHAR “RTRIM(:DIST_CODE_COMBINATION_ID)”,
     LAST_UPDATED_BY          CHAR “RTRIM(:LAST_UPDATED_BY)”,
     AMOUNT                  CHAR “RTRIM(:AMOUNT)”,
     CREATED_BY          CHAR “RTRIM(:CREATED_BY)”,
     CREATION_DATE          DATE “DD-MON-RRRR”,
     ATTRIBUTE1          CHAR “RTRIM(:ATTRIBUTE1)”,
     ATTRIBUTE2          CHAR “RTRIM(:ATTRIBUTE2)”,
     ATTRIBUTE3          CHAR “RTRIM(:ATTRIBUTE3)”,
     TYPE_1099          CHAR “RTRIM(:TYPE_1099)”,
     UNIT_OF_MEAS_LOOKUP_CODE      CHAR “RTRIM(:UNIT_OF_MEAS_LOOKUP_CODE)”,
     TAX_RATE              CHAR “RTRIM(:TAX_RATE)”,
     QUANTITY_INVOICED      CHAR “RTRIM(:QUANTITY_INVOICED)”,
     UNIT_PRICE          CHAR “RTRIM(:UNIT_PRICE)”,
     INVOICE_ID “AP_INVOICE_LINES_INTERFACE_S.NEXTVAL”)

/by
, , ,

What is SQL* Loader?

What is SQL*Loader?
SQL*loader is one of the Oracle tool which will be used to transfer the data from Flat-File to oracle Database table.

Which files in SQL*loader?
1. Flat or Data File
2. Control File
3. Bad File
4. Discard File
5. Log File

What is Flat Or Data File: This file contains the records in a special format; these records will be fetching for other legacy. The extension of these files might be .dat, .txt, or .csv (comma separated view).

What is Control File: This is SQL loader execution file, which will be used to transfer the date from file to table. In side of these control file, we will mention the Data file path, table name, column mapping. The extension of control file is .ctl

Control File Creation:

Load data
INFILE ‘Data File Path’
INSERT INTO ‘Table Name’
FIELD TERMINATED BY ‘,’
WHERE deptno = 10
TRAILING NULL COLS
(column1 , empno
column2, ename
column3, deptno)

Once we develop the control file we will execute this by using fallowing command
C:> sqlldr user/passward @ Database Control = name of control file (with extension .ctl)
This command will start the control file execution, and it will try to read the data and inserting into table. After completion of this execution, automatically three files will gets created
Bad file
Discard file
Log file

Bad File: Bad file contain the records, which are rejected by the SQL*loader. SQL*loader will reject the records, when ever the Flat file format is not correct or if any internal error occurs it will rejected. The extension of bad file is .bad

Discard File: Discard file contains the records which are rejected by the control file, control file reject the records, if record is not satisfying the conditions, which we have mentioned inside of control files the extension of discard file is .dis

Logfile: It contains the complete info of the process, like no of records successfully loaded in to the table
No of records successfully loaded in to the bad file & discard file.
And where the bad, discard file gets created and time taken to complete the process.
Taking the complete log.
SQL* Loader Modes:
INSERT
APPEND
REPLACE
We can replaced the data in to the table by using any one of the allowing method

INSERT: When we are using this statement, table should be empty. SQL * loader will insert the new data form the file.

APPEND: This mode will be use to attach the new record to the existing records.

REPLACE: This will replace the existing records with new records.
C:> sqlldr userid/passward@Database control=text1.ctl path=direct

SQL* Loader Paths: We can execution SQL* loader in two paths or nodes
Direct
Conventional
By default SQL*loader will be running in conventional mode, if we want to run in direct mode will use the fallowing syntax
C:> sqlldr userid/passward@Database control=text1.ctl path=direct
Direct mode will disable the table and column constrains and it will insert the data.
Conventional path will check every constrains, if it is satisfied it will insert the record
Conventional path is just like ‘insert statement’
SQL Commands Limitations:
to_date, to_char, upper, lower, Initcap, string, decode, nvl
when clause
sequence_name.next_value, Ref-Cursor
sysdate, ltrim, rtrim, constant

/by
, , , ,

A comparative analysis between SQL*LOADER and UTL_FILE utility

In implementing new systems we come across problems of importing “alien” data. This may be coming from a legacy system or an on-going system. This data is transported via extract files from the legacy system to the Oracle system. The gateway to Oracle for this data is SQL*Loader and data is loaded into tables via a control script into tables.

Typically, the older systems do not have very normalized data, nor have they been operating with fully implemented database constraints. The lack of constraints over the years in legacy system can lead to bad data that has crept in. Therefore, while bringing external data into oracle system we need a refined set of checks and balances to ensure that we get good data. This requires a lot of programmatic control in the process of data-loading.

The approach applied in case of SQL* Loader is as follows :
1. Load the data into temporary tables via SQL*Loader via control file and make the data native to ORACLE.
2. Write a PL/SQL program to do the processing.
3. Load the data into live tables.

This approach has a lot of dependencies as well as a strong lack of integration of steps and programmatic control. To overcome this, we have analyzed another facility in that has been release Oracle 7.3.x onwards. It is called the UTL_FILE package. With some creative use of this package we can achieve whatever  SQL*LOADER offers and in addition to that do some high level validation and complex data loading. In the following discussion a study of two tools is done.

A BRIEF OVERVIEW OF SQL*Loader:
SQL*Loader is a server utility for loading data from external data files into Oracle database. The basic advantage of using SQL*Loader is for simple loads and fast loading of data. It can load data into myriad data formats, perform elementary filtering, load data into multiple tables, and create one logical record from one or more physical records.

It creates a detailed log file, a bad file that contains rejected records and a discard file to hold the records that are selectively not loaded. The tool is executed from a command line and a username and password and the control file name and location are required to run it.

A BRIEF OVERVIEW OF UTL_FILE:
PL/SQL does not have text file input output capabilities but acquires it via UTL_FILE package. It provides rudimentary utility for reading ( as well as writing) files from within a PL/SQL program. The lines in the file are read sequentially and hence it effects the performance of the program.

The UTL_FILE package can be wrapped around with a PL/SQL program and since this package is integrated with PL/SQL it provides us the tremendous ability for flexing our “programming muscles.” Some procedures and functions can be added to this wrapper program that serve as a handy “tool” for doing normal file reading operations. With this approach we can achieve whatever SQL*Loader can do and much more. The security mechanism for UTL_FILE is achieved by defining a parameter in INIT.ora file called utl_file_dir parameter. The directories that UTL_FILE can read from and write to need to have permissions of Oracle
instance owner and the user running the package.

CONCLUSIONS:
The comparative analysis of SQL*Loader and UTL_FILE reveals that these tools are suitable to our environment subject to the conditions of our needs.

If the data load is complex (as is the case in relational databases) UTL_FILE seems to be the tool of choice. This tool does require programmatic effort in terms of writing a wrapper package but the subsequent effort in this direction is greatly reduced once the initial tool kit is built for your environment.

The UTL_FILE tightly integrates the file input with the programmatic control and the data manipulation inside a single PL/SQL unit. There are disadvantages of speed in loading in case of UTL_FILE but these are totally offset by the programmatic control it offers and the integration it brings in.

Thus we find that UTL_FILE tool bridges the gap left by SQL*Loader for complex data loads.

/by
, , , , , , , ,

Required and optional columns of Payables Open interface import of PO matched invoices (R12)

In this post, I tried to explain the required and optional columns in the payables invoice import tables for importing PO Matched Invoices.

 Table:  AP_INVOICE_INTERFACE
  Required Columns

Column Name
Validation
INVOICE_ID                            
Populated from AP_INVOICES_INTERFACE_S.NEXTVAL
INVOICE_NUM                           
Must be unique to the supplier
PO_NUMBER
An approved, not cancelled, not closed or final closed PO
VENDOR_ID or VENDOR_NUM or VENDOR_NAME
An active vendor. Validated against PO_VENDORS
VENDOR_SITE_ID or VENDOR_SITE_CODE
An active pay site. Validated against PO_VENDOR_SITES
INVOICE_AMOUNT                        
Positive amount for ‘STANDARD’ type, Negative amount for ‘CREDIT’ type
ORG_ID                                
Required in Multi-Org Environment. Validated against AP_SYSTEM_PARAMETERS.ORG_ID
SOURCE                                
Must be in
SELECT lookup_code
  FROM ap_lookup_codes
 WHERE lookup_type = ‘SOURCE’;
    
 
Optional Columns
    Column Name
Validation
INVOICE_DATE
Defaulted to SYSDATE
INVOICE_TYPE_LOOKUP_CODE              
Defaulted to ‘STANDARD’. It can be ‘STANDARD’ or ‘CREDIT’
INVOICE_CURRENCY_CODE                 
Defaulted from PO_VENDOR_SITES.
INVOICE_CURRENCY_CODE
EXCHANGE_RATE_TYPE                    
Defaulted from AP_SYSTEM_PARAMETERS.
DEFAULT_EXCHANGE_RATE_TYPE
TERMS_ID or TERMS_NAME                
Defaulted from
 PO_VENDOR_SITES.TERMS_ID
DOC_CATEGORY_CODE                      
Only populated if using automatic voucher number
PAYMENT_METHOD_LOOKUP_CODE
Defaulted from PO_VENDOR_SITES
.PAYMENT_METHOD_LOOKUP_CODE
PAY_GROUP_LOOKUP_CODE
Defaulted from PO_VENDOR_SITES.
PAY_GROUP_LOOKUP_CODE
ACCTS_PAY_CODE_COMBINATION_ID
Defaulted from PO_VENDOR_SITES.
ACCTS_PAY_CODE_COMBINAITON_ID
GROUP_ID
Group identifier. Suggest to use it
STATUS
DO NOT POPULATE IT


Table:  AP_INVOICE_LINES_INTERFACE
 Required Columns 
Column Name
Validation
INVOICE_ID                            
Populated from AP_INVOICES_INTERFACE.INVOICE_ID
INVOICE_LINE_ID                       
Populated from AP_INVOICE_LINES_INTERFACE_S.
NEXTVAL
LINE_NUMBER        
A unique number to the invoice
TAX_CODE or TAX_CODE_ID
Validated against AP_TAX_CODES_ALL
LINE_TYPE_LOOKUP_CODE                 
‘ITEM’
AMOUNT                              
Should be QUANTITY_INVOICED * UNIT_PRICE
If MATCH_OPTION is ‘P’, then populate
RELEASE_NUM or PO_RELEASE_ID              
For Blanket Release only,
 validated against PO_RELEASES_ALL
PO_NUMBER or PO_HEADER_ID
Validated against PO_HEADER_ALL
PO_LINE_NUMBER or PO_LINE_ID
Validated against PO_LINES_ALL
PO_SHIPMENT_NUM or PO_LINE_LOCATION_ID    
Validated against PO_LINE_LOCATIONS_ALL
If MATCH_OPTION is ‘R’, then populate
RECEIPT_NUMBER                            
Validated against RCV_SHIPMENT_HEADERS.RECEIPT_NUM
RCV_TRANSACTION_ID or PO_LINE_LOCATION_ID
Validated against RCV_TRANSACTIONS
Optional Columns
Column Name
Validation
QUANTITY_INVOICED
Populated if different from PO shipment
UNIT_PRICE
Populated if different from PO shipment
MATCH_OPTION
‘P’ or ‘R’ or Defaulted from PO_VENDOR_SITES.MATCH_OPTION
ACCOUNTING_DATE
Defaulted from INVOICE_DATE or SYSDATE
FINAL_MATCH_FLAG
Populated ‘Y’ if it is final matching
INVENTORY_ITEM_ID
Validated against PO_LINES.INVENTORY_ITEM_ID
INVENTORY_DESCRIPTION
Validated against PO_LINES.INVENTORY_ITEM_DESCRIPTION
SHIP_TO_LOCATION_CODE
Populated if different from PO shipment
PRICE_CORRECTION_FLAG
Populated ‘Y’ if it is price correction
/by