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

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.