Registering SQL*Loader as Concurrent Program

Calling Control File from Concurrent Program

Now we are going to learn about how to call the control file from concurrent program.

 Below is the sample control file.

options(skip=1)

load data
INFILE *
BADFILE 'xxsy_planner_csd_update.log'
DISCARDFILE 'xxsy_planner_csd_update.log'
APPEND into table apps.xxsy_planner_csd_update
fields terminated by ','
OPTIONALLY ENCLOSED BY ' '
TRAILING NULLCOLS
                                       (
                                         ACTION,                    
                                         CUSTOMER,
                                         ODM,
                                         OEM,
                                         SO_LINE_SHIPMENT,
                                         ORDER_TYPE,
                                         PART_NUMBER,
                                         ORDER_ENTER_DATE DATE "DD/MM/YYYY",                   
                                         ORDER_BOOK_DATE  DATE "DD/MM/YYYY",
                                         ORDERED_QUANTITY,
                                         CSD DATE "DD/MM/YYYY",
                                         MSD DATE "DD/MM/YYYY",
                                        CSD_MSD_COMMENTS,
                                        INSIDE_SALES_REP,
                                        CREATION_DATE "SYSDATE",
                                        CREATED_BY "FND_GLOBAL.USER_ID",
                                        LAST_UPDATE_DATE "SYSDATE",
                                        LAST_UPDATED_BY "FND_GLOBAL.USER_ID",
                                        LAST_UPDATE_LOGIN "FND_GLOBAL.USER_ID"
                                      ) 

Step1:- Place the control file in Custom Top($XX_TOP/bin) bin directory

Step2:- Create Concurrent executable of type SQL*Loader

            Navigation :- System Administrator == > Concurrent == > Program == > Executeble.
            Execution Method :- SQL*Loader.

            Execution File Name :- (Give the name of control file without extenstion).

            

                                                                                                                                                                                      

Step3:- Create the Concurrent Program.

Navigation: - System Administrator == > Concurrent == > Program == > Define.

Click on parameter tab and define one parameter for file path.

 Step4:- Now register the concurrent program in which responsibility you want.

            Navigation == > System Administrator == > Security == > Responsibility == > Request.

     

  Step5:- Switch to the responsibility in which you register the concurrent program and run the Program.
          
              Give the parameter :- Filename with full path where u placed the flat file(CSV file).                                   

             

           

             The control file has been completed successfully.

           

              Now the data has been loaded successfully to the staging table.

              Click on view log to see the details of execution. 

Table "XXSY"."XXSY_PLANNER_CSD_UPDATE", loaded from every logical record.

Insert option in effect for this table: APPEND

TRAILING NULLCOLS option in effect

   Column Name                  Position   Len  Term Encl Datatype

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

ACTION                              FIRST     *   ,  O(") CHARACTER           

CUSTOMER                             NEXT     *   ,  O(") CHARACTER           

ODM                                  NEXT     *   ,  O(") CHARACTER           

OEM                                  NEXT     *   ,  O(") CHARACTER           

SO_LINE_SHIPMENT                     NEXT     *   ,  O(") CHARACTER           

ORDER_TYPE                           NEXT     *   ,  O(") CHARACTER           

PART_NUMBER                          NEXT     *   ,  O(") CHARACTER           

ORDER_ENTER_DATE                     NEXT     *   ,  O(") DATE DD-MON-YYYY    

ORDER_BOOK_DATE                      NEXT     *   ,  O(") DATE DD-MON-YYYY    

ORDERED_QUANTITY                     NEXT     *   ,  O(") CHARACTER           

OLD_CSD                              NEXT     *   ,  O(") DATE DD-MON-YYYY    

CSD                                  NEXT     *   ,  O(") DATE DD-MON-YYYY    

MSD                                  NEXT     *   ,  O(") DATE DD-MON-YYYY    

CSD_MSD_COMMENTS                     NEXT     *   ,  O(") CHARACTER           

INSIDE_SALES_REP                     NEXT     *   ,  O(") CHARACTER           

LAST_UPDATE_DATE                     NEXT     *   ,  O(") DATE DD-MON-YYYY    

LAST_UPDATED_BY                      NEXT     *   ,  O(") CHARACTER           

TRANSACTION_ID                       NEXT     *   ,  O(") CHARACTER           

    SQL string for column : "XXSY_CSD_UPDATE_S.NEXTVAL"

CREATION_DATE                        NEXT     *   ,  O(") CHARACTER           

    SQL string for column : "SYSDATE"

CREATED_BY                           NEXT     *   ,  O(") CHARACTER           

    SQL string for column : "FND_GLOBAL.USER_ID"

STATUS                               NEXT     *   ,  O(") CHARACTER           

    SQL string for column : "1"

value used for ROWS parameter changed from 64 to 47

Table "XXSY"."XXSY_PLANNER_CSD_UPDATE":

  832 Rows 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.

Space allocated for bind array:                 254646 bytes(47 rows)

Read   buffer bytes: 1048576

Total logical records skipped:          1

Total logical records read:           832

Total logical records rejected:         0

Total logical records discarded:        0

Run began on Thu May 29 07:03:07 2014

Run ended on Thu May 29 07:03:08 2014

Elapsed time was:     00:00:01.06

CPU time was:         00:00:00.03

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

Executing request completion options...

           

How to run the Concurrent Program from Backend.

How to Run Concurrent Program from Backend.

Now we are going to learn about how to run the concurrent program from backend.

We can run the concurrent program from backend using FND_REQUEST.Submit_Request API.

Call the FND_REQUEST.Submit_Request and pass the necessary parameters.

Application     -           Give the application short name in which application your con 

 program was registered.

            Program           -           Give the short name of the concurrent program.

            Description      -           Give the name of the concurrent program.

            start_time        -           Null.

            sub_request     -           FALSE

            argument1       -           Give the parameters.

Declare

Request_id NUMBER := 0;

Begin

            Request_id := FND_REQUEST.Submit_Request(application => 'XX'

                                    , program => 'XX_PLANNER_CSD_UPDATE'

                                    , description => 'Planner csd update data load'

                                    , start_time => NULL

                                    , sub_request => FALSE

                                    , argument1 => p_file_path

                                    );

            IF Request_id = 0    THEN

                        DBMS_OUTPUT.PUT_LINE( 'Request submission For this store FAILED' );

            ELSE 

  DBMS_OUTPUT.PUT_LINE( 'Request submission For this store SUCCESSFUL' );

            END IF;

End;

commit;