Steps to Create SQL Loader Based Program in EBS

By | August 10, 2016

Table of Contents

Business Requirment
Solution concept
Prepare data in Excel
Create data in CSV Format
Create Staging Table in database
Create SQL Loader Control File
Create Execution File
Create Concurrent Program
Add to Request Group
Submit the Program in staging table.

Business Requirement:

HR department has created an EIT for storing passport information in the oracle system. Now HR department wants to upload the data in that EIT. Creating of Passport EIT is covered in previous solution ‘Create EIT’.

Back to top

Solution Concept

There is an API that can be used to insert, update and delete data in EIT. But in order to API to work, we need data into some staging table. There could be many ways of inserting data into staging table as follows

  • Using Direct Insert Statements
  • Using Some Tool like TOAD or SQL Developer etc
  • Using SQL Loader

In this solution we are going to use SQL Loader through a concurrent program to load data in staging table.  Benefit of using SQL Loader is that once concurrent program is developed, you can give this to HR users to prepare a csv file and submit this program to upload the data in future. Otherwise they have to rely every time on IT department to upload the data.

Following are main steps of creating SQL Loader based concurrent program.

  • Prepare Data in Excel
  • Create data in CSV Format
  • Create Staging Table in database
  • Create SQL Loader Control File
  • Create Execution File
  • Create Concurrent Program
  • Add to Request Group
  • Submit the Program to Upload data in Staging Table

Back to top

Prepare Data in Excel

Following is the image of EIT structure that we had created for Passport EIT

Oracle HRMS Overview 01

Now prepare data in excel file for it. Normally it is role of HR to prepare and provide data.

Excel file looks like this

Oracle HRMS Overview 02

All these employees exist in Oracle Vision instance, We have simply added the passport number, passport issue and passport expiry date.

Back to top

Create data in CSV Format

click on File -> Save As

Oracle HRMS Overview 03

Select the .csv file type

Oracle HRMS Overview 04

Remove the headings line of the csv file. You csv file should look like this

Oracle HRMS Overview 05

We have removed the headers because in the control file that we shall create in the next step, it starts reading data from first line itself.

Back to top

Create Staging Table in database

CREATE TABLE LSG_EIT_UPDATE_STG

(
RECORD_ID NUMBER,
EMPLOYEE_NUMBER VARCHAR2(10 BYTE),
PERSON_ID NUMBER,
ASSIGNMENT_ID NUMBER,
FULL_NAME VARCHAR2(100 BYTE),
PASSPORT_NO VARCHAR2(100 BYTE),
PASS_ISS_DATE DATE,
PASS_EXP_DATE DATE,
ERR_MESSAGE VARCHAR2(200 BYTE),
UPDATE_TIME DATE,
RECORD_STATUS VARCHAR2(1 BYTE)

)

Back to top

Create SQL Loader Control File


SQL Loader control file mainly depends on the table that you have created. Control File looks like this
Oracle HRMS Overview 06

Place this control file in any place on your server. For example, we have placed the file in Bin folder of PER Top at following location.

Oracle HRMS Overview mino

Back to top

Create Execution File

Go to Application Developer responsibility

Oracle HRMS Overview 07

Go to the following navigation

Oracle HRMS Overview 08

Following screen will open

Oracle HRMS Overview 09

Save the data as shown below

Oracle HRMS Overview 10

Back to top

Create Concurrent Program


Go to following navigation to create the concurrent program

Oracle HRMS Overview 11

Following screen will open

Oracle HRMS Overview 12

Enter the data as shown below

Oracle HRMS Overview 13

Click on Parameters button to add Parameter for CSV data file path

Oracle HRMS Overview 14

Make its default type as Constant and in the default value field set its default value as
/d01/oracle/VIS/apps/apps_st/appl/per/12.0.0/bin/lsg_eit_update_stg.csv

Back to top

Add to Request Group

Go to system administrator responsibility

Oracle HRMS Overview 15

Go to following navigation

Oracle HRMS Overview 16

following screen will open

Oracle HRMS Overview 17

Find following request group ‘AE HRMS Reports & Process’. This request group is for AE HRMS Manager responsibility.

Oracle HRMS Overview 18

Click on New Button

Oracle HRMS Overview 19

Add the newly created concurrent program

Oracle HRMS Overview 20

Save it.

Back to top

Submit the Program to Upload data in Staging Table

Go to AE HRMS Responsibility

Oracle HRMS Overview 21

Click on View-> Request

Oracle HRMS Overview 22

Following screen will appear

Oracle HRMS Overview 23

Click on Submit New Request

Oracle HRMS Overview 24

Click OK. Following screen will appear

Oracle HRMS Overview 25

Select the concurrent program

Oracle HRMS Overview 26

Submit the program

Oracle HRMS Overview 27

Following screen appears

Oracle HRMS Overview 28

Click No, Click on Find button

Oracle HRMS Overview 29

It shows the program is completed

Oracle HRMS Overview 30

Click on View Log button. It will open the log file in the browser

Oracle HRMS Overview 31

Scroll down to see the details of loaded rows as shown below

Oracle HRMS Overview 32

Let’s see the status of table in the TOAD

Oracle HRMS Overview 33

Back to top

If you  have liked contents in this post and you think it can be helpful to others, please share it at least once in your circle, in this way, you will join me in my cause to Learn Share and Grow. Come on, Lets Grow Together.

Related Posts

WISTU – Oracle Workflow Builder
Free Online Test
Solved Issued of Empty Email Notification
Steps for creating custom form in Oracle Apps
How to create backend logging mechanism
Useful Apps SQL Queries Pool
Top 10 Apps Technical Interview Questions

  • afzal

    Assalamu alaikum Farhan, Thanks for the nice article. How will the end user place the .csv file on the server.. pls explain.

  • fanni339

    Dear Afzal

    You can create Samba Server and share a drive with the user or give a limited access and train then to use winscp to transfer file on server location. Its not taht difficult.

    Regards
    Farhan

  • afzal

    how about custom webadi? it will work in this conditions?
    Regards,
    Afzal.

  • fanni339

    Dear Afzal,I have not used custom wbadi, If you have some good article on it, Plz do share it.