Concurrent program to execute stored procedure in EBS

Creating Concurrent program to execute stored procedure


You have a PL/SQL procedure in your database and you want the user to be able to execute it from Oracle Apps front end.

To do so, we have to register the procedure in Oracle Apps. Now let’s see how to do that.

Step to execute PL/SQL Procedure.


1. Create package

2. Define set of procedure in Packages

3. Create Executable and Select method PL/SQL Stored Procedure

4. Create Concurrent program

5. Add this program to the required request group


If you want to register a procedure, the procedure should be created with the two below mandatory parameters:

errbuf : For printing out the error messages.
retcode: retcode return 0 or 1 or 2

return 0 for successful execution

return 1 to end the concurrent program in warning

return 2 to end the concurrent program in error

Package Specification


CREATE OR REPLACE package APPS.xxgst_asp_extraction_pkgas

PROCEDURE test;

procedure extractor_main(

errbuf OUT NOCOPY VARCHAR2,

retcode OUT NOCOPY VARCHAR2,

p_curr_sync_date IN VARCHAR2

end xxgst_asp_extraction_pkgas;

/

Package Body


CREATE OR REPLACE package body APPS. xxgst_asp_extraction_pkgas

PROCEDURE test is

 errbuf VARCHAR2(2000);

 retcode VARCHAR2(1);

 begin

 xxgst_asp_extraction_pkg.extractor_main(errbuf,retcode,'06-SEP-2017');

 end test;

procedure extractor_main(

 errbuf OUT NOCOPY VARCHAR2,

 retcode OUT NOCOPY VARCHAR2,

 p_curr_sync_date IN VARCHAR2

 ) IS

 l_last_sync_date DATE;

 l_curr_sync_seq_num VARCHAR2(10);

 begin

 /*

 Your logic for your procedure

 */ 

 extractor_core(l_last_sync_date, l_curr_sync_date, l_curr_sync_seq_num, l_sysdate, null);

 debug('End');

 

 EXCEPTION

 WHEN OTHERS THEN

 debug('Error: Exception in extractor_main: ' || sqlerrm);

 debug('Call Stack: ' || dbms_utility.format_call_stack);

 

 delete_extraction(errbuf, retcode, l_curr_sync_seq_num, null, null);

 retcode := 2;

 

 end extractor_main;

end xxgst_asp_extraction_pkg;

 

Creating Executable


Creating concurrent program


Assigning concurrent program into request group


Now You can run concurrent program

Creating Concurrent program using .ldt file

Let’s say someone has  already created concurrent program.

So Now you can Download That program and give it  to other client to create the same concurrent program in another instance.

To download Existing concurrent program you need to run below command in putty.

FNDLOAD apps/db_password O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct XXOLALOAN_TEST.ldt PROGRAM APPLICATION_SHORT_NAME="XXOLA" CONCURRENT_PROGRAM_NAME="Concurrent_program_name"

apps/db_password :  database user/password

PROGRAM APPLICATION_SHORT_NAME=”XXOLA”

CONCURRENT_PROGRAM_NAME=”Concurrent_program_name

This command will generate .ldt file.

so now we can use this .ldt file to create concurrent program into another instance.

Let’s understand it with detail steps:

Open putty and Go to the directory where your ldt file is present


Note : You can use

cd directory location command to go to the directory.

You can check your directory location using pwd command.

Put .ldt file onto server


Execute below command to create concurrent program

 

1] XXGST ASP Extraction Data Reprocess Program

FNDLOAD apps/db_password 0 Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct XXGST_ASP_DATA_REPROCESS_PRG.ldt - WARNING=YES UPLOAD_MODE=REPLACE CUSTOM_MODE=FORCE

Now you can see Concurrent program is created in EBS Frontend.