f Get Oracle Apps Traning Here: Steps for your first pl/sql Concurrent Program in Oracle Apps
Main Menu
Techie or Funcy ?
Prerequisites
Training Index   

Interview Qns
Apps Interview Qns
HRMS Interview Qns

COPYRIGHT
This work is licensed under a Creative Commons -NonCommercial 2.5 License.



About Me

Name:
Location: United Kingdom

The desire to be extraordinary is a very ordinary desire. To relax and to be ordinary is really extraordinary

  • Subscribe for Emails

    • Previous Posts

    Thursday, November 02, 2006

    Steps for your first pl/sql Concurrent Program in Oracle Apps

    I think this topic is already covered partially in one of the previous training lesson[ for concurrent programs], but I would like to touch base on this again.

    Lets revisit some basics first

    Question: What are the ingredients for a concurrent program?
    Answer: A concurrent executable and a program attached to that executable.

    Question: Will executable for a pl/sql concurrent program be a database stored procedure?
    Answer: Yes, but in addition to above you can also register a procedure within a package as an executable. However, you can't make a Function to become the executable for a stored procedure.

    Question: Does this stored procedure need to have some specific parameters, in order to become an executable of a concurrent program?
    Answer: Yes, such procedure must have at least two parameters
    ( errbuff out VARCHAR2, retcode out NUMBER)

    Question: Can we add additional parameters to such pl/sql procedures that happen to be Conc Prog Executables?
    Answer: Sure you can, but those parameters must be defined after the first two parameters. Effectively I mean first two parameters must always be errbuff and retcode. The sequence of the remaining parameters must match with the sequence in which parameters are registered in define concurrent program-parameters window.

    Question: Can those parameters be validated or will these parameters be free text?
    Answer: These parameters can be attached to a value set, hence this will avoid users passing free text values.

    Question: What are the possible things that a concurrent pl/sql program can do?
    Answer: Firstly your stored procedure would have been created in apps. This concurrent program will connect to "apps schema" from where access to all the tabes across every module will be available.
    You can do the following:-
    1. Insert records in tables(usually interface or temp tables)
    2. Update and delete records
    3. Initiate workflows
    4. Display messages in the output file or the log file of the concurrent program.
    5. Make this concurrent program complete with status Error or Warning or Normal.

    Question: Please give me an example of a pl/sql concurrent program in Oracle apps in real life?
    Answer: Lets say you have an external application which is integrated with apps. Assume that it is Siebel application where the new customer records are created. Siebel is assumingly hosted on a Oracle database from where it has database links to your Oracle apps database.
    In this case, siebel inserts sales order records into your custom staging tables.
    You can then develop a concurrent process which will do the following:--------
    Loop through the records in that staging table
    Check if the customer already exists in Oracle AR TCA
    If customer already exists, thencall the api to update existing customer
    If this is a new customer, then update existing TCA Customer/Party record

    Question: Ok, how do I do the above?
    Answer: Find the steps below for doing so

    Step 1
    Connect xxschema/password
    Create table xx_stage_siebel_customers ( customer_Id integer, customer name varchar2(400));
    Grant all on xx_stage_siebel_customers to apps ;

    Step 2
    Connect apps/apps_password
    Create or replace synonym xx_stage_siebel_customers for xxschema.xx_stage_siebel_customers ;

    Step 3 ( again in apps schema)
    Create or replace procedure xx_synch_siebel_cust ( errbuff out varchar2, retcode out varchar2 ) is
    n_ctr INTEGER := 0 ;
    Begin
    for p_rec in ( select * from     xx_synch_siebel_cust ) LOOP
    Select count(*) into n_ctr from hz_parties where party_number = p_rec.customer_number;
    If n_ctr=0 then
    Hz_party_create(pass appropriate parameters here).
    Else
    Hz_party_update(    pass appropriate parameters here);
    End if;
    END LOOP ;
    delete from     xx_synch_siebel_cust ;
    End xx_synch_siebel_cust

    Step 4
    Create concurrent program executable ( for example of screenshot, visit link )

    Step 5
    Create concurrent program for that executable

    Step 6
    Add this concurrent program to request group

    Now your program is ready to roll....

    posted by Anil Passi at 4:19 PM

    Comments on "Steps for your first pl/sql Concurrent Program in Oracle Apps"

     

    Anonymous Anonymous said ... (8:12 AM) : 

    Hi,

    I understand it's possible to call a stored procedure with parameters, but how would one go about deciding whether the procedure had completed okay, or if an error(s) had occurred.

    Is there a way of informing the user (or an administrator) that the routine they had run had just failed?

    I've thought that there are a number of potential ways of doing this. An error table could be updated, a log file could be produced, workflow could maybe be called or can the concurrent process be updated to show that a failure occurred and what this was?

    In Oracle Apps, is there a standard or preffered way of doing this?

    I'm looking at the Transaction Interface. I can catch errors but how do I inform users (in a tidy way) what has occurred?

    Thanks.

     

    Anonymous Anonymous said ... (8:13 AM) : 

    Hi,

    I understand it's possible to call a stored procedure with parameters, but how would one go about deciding whether the procedure had completed okay, or if an error(s) had occurred.

    Is there a way of informing the user (or an administrator) that the routine they had run had just failed?

    I've thought that there are a number of potential ways of doing this. An error table could be updated, a log file could be produced, workflow could maybe be called or can the concurrent process be updated to show that a failure occurred and what this was?

    In Oracle Apps, is there a standard or preffered way of doing this?

    I'm looking at the Transaction Interface. I can catch errors but how do I inform users (in a tidy way) what has occurred?

    Thanks.

     

    Blogger Anil Passi said ... (9:58 AM) : 

    Hi Simon

    When you submit a concurrent program, you can click on button labeled "Option". Here you can enter the name of the people that will receive email notification when the Concurrent Process completes.

    The way I have implemented this is, in following steps:-
    1. Make the Concurrent program complete with Warning in case of errors during interface.
    2. Set the option such that Users receive notification on completion of request(click on option button)
    3. The users will then receive email notification while will state..Your process Request Id xxx completed with Warning. Please click on Link to view the output.

    Note: In your pl/sql concurrent program you must use fnd_file.put_line(which => fnd_file.output, buff => p_msg);
    This will show all messages in output of the concurrent request.

     

    Anonymous Anonymous said ... (9:48 AM) : 

    Hi Anil,

    Thanks a lot. This worked perfectly. One thing I couldn't find though was step 1:
    "Make the Concurrent program complete with Warning in case of errors during interface."

    Where do I do this?

    Thanks again.

     

    Blogger Anil Passi said ... (9:54 AM) : 

    If errors were encountered in the pl/sql concurrent process, at the very end of the pl/sql procedure assign 1 to retcode. Or call a function as below


    FUNCTION get_ret_code RETURN VARCHAR2 IS
    CURSOR c_check IS
    SELECT 'x'
    FROM "your error table here"
    WHERE fnd_request_id = fnd_global.conc_request_id;
    p_check c_check%ROWTYPE;
    BEGIN
    OPEN c_check;
    FETCH c_check
    INTO p_check;
    IF c_check%FOUND
    THEN
    CLOSE c_check;
    RETURN '1';
    END IF;
    CLOSE c_check;
    RETURN NULL;
    END get_ret_code;


    ====MAin process=-====
    declare
    begin
    ...your code here...
    retcode := get_ret_code;
    EXCEPTION jandler here

    END ;

     

    Anonymous Anonymous said ... (4:03 AM) : 

    Hi Anil,

    Thanks again. This worked as you said. I played around with it and found that I could change the retcode to give different status results as follows:
    0 - 'Normal'
    1 - 'Warning'
    2 - 'Error'

    In the table fnd_concurrent_requests the status code was saved as follows:
    'C' - 'Normal'
    'G' - 'Warning'
    'E' - 'Error'

    I'm guessing that the codes: 0, 1 & 2 are stored in the app somewhere as constants. As opposed to setting a retcode = 0, 1 or 2 I'd like to reference the Oracle constants. Do you know where these are stored (if they are)?

    Thanks Again.

     

    Blogger Anil Passi said ... (4:20 AM) : 

    Run the below SQL...these codes are stored in tables.

    SELECT distinct lookup_code, meaning
    FROM fnd_lookup_values
    WHERE lookup_type = 'CP_STATUS_CODE'
    AND trunc(SYSDATE) BETWEEN nvl(start_date_active
    ,trunc(SYSDATE)) AND
    nvl(end_date_active
    ,trunc(SYSDATE))

     

    Anonymous Anonymous said ... (6:48 AM) : 

    Is that the same for retcode. Is its values; 0, 1 and 2 also stored in a table or are they package constants?

     

    Blogger Anil Passi said ... (7:15 AM) : 

    Hi Simon
    This appears to be hardcoded. Have a look at package FND_CONC_PRIVATE_UTILS
    It has the below text.....

    execute immediate sql_string using out errbuf,out retcode, in step;

    /* If retcode is not set, then we'll assume success, since
    * no exception was raised. */

    if retcode is null then
    retcode := 0;
    end if;


    Thanks
    Anil Passi

     

    Anonymous Anonymous said ... (9:06 AM) : 

    Thanks , this blog really helped me

     

    Anonymous Anonymous said ... (1:02 AM) : 

    Hello Anil...

    This site very useful for starters.I just moved into oracle application...I have a question ..

    can u give me some common validation to validate data coming from legacy system on all major module.so it can be also helpful to learners

    Thanks
    Raj

     

    Blogger Unknown said ... (1:21 PM) : 

    Hi Anil

    Thanks for your wonderful work doing in sharing the knowledge.

    Till now I didnt understand what exactly errbuff and retcode does when we dine procedure as concurrent prog.

    What I am aware is errbuff is to return the error message and retcode for status 0=normal, 1=waring,2=errors.

    I am looking what exactly this two parameters help in background.

    Many Thanks for your comments
    Vinod

     

    Anonymous Anonymous said ... (1:22 PM) : 

    Hi Anil

    Thanks for your wonderful work doing in sharing the knowledge.

    Till now I didnt understand what exactly errbuff and retcode does when we dine procedure as concurrent prog.

    What I am aware is errbuff is to return the error message and retcode for status 0=normal, 1=waring,2=errors.

    I am looking what exactly this two parameters help in background.

    Many Thanks for your comments
    Vinod

     

    Blogger Vinod said ... (1:39 PM) : 

    Anil

    Thanks a lot for your wonderful insight in sharing the knowledge.

    I have quick question..

    I am aware of why we use parameters
    errbuff - to return the error messages
    retcode - to return the status like 0=normal, 1=warning, 2=errors
    when we are registering procedure as conc prog.

    But till now I didnt understand what happens in the background with these parameters.

    Many Thanks for your comments
    Vinod

     

    Anonymous Anonymous said ... (7:05 PM) : 

    Hi ,

    Could you tell me wjhere the procedure gets stored in appl_top.

     

    Anonymous Anonymous said ... (2:50 AM) : 

    hi,
    Please tell if we have to register both CTL file and validation procedure in Apps…

    And do we have to make a request set combining these two…and then submit this request set?!?!



    can you please tell me the whole process of transfering data from excle sheep (.csv file)
    to staging table to gl_interface table.?

     

    Anonymous Anonymous said ... (2:04 PM) : 

    Hi Anil,

    This site is indeed incredible.. It shows the heights of efforts a person can put, in sharing knowledge and helping the newbies..Hats off for your hard work..

    I have a question regarding concurrent program in apps.

    I have a shell script which is registered as a host program.In that shell script I have a sql session too which uses fnd_global.apps_initialize(apps_user_id,apps_resp_id,application_id).

    Now I have hardcoded the values of parameters for it. I am wondering if I could pass the parameters as variables(positional variables).

    I think this will be needed when the program will be moved to other instances.

    Could you please help me?

    Thanks in advance

     

    Blogger Anil Passi said ... (2:23 PM) : 

    Hi Anonymous,

    Thanks for kind words.

    The parameters passed by concurrent manager to a host program are
    $0 - Shellscript name
    $1 - Oracle username/password
    $2 - User_id
    $3 - Oracle Applications username
    $4 - Concurrent request id

    Please see metalink note Note:29086.1 for details.

    It appears that you do have to hardcode some of the ID's if you wish to instantiate SQL*Plus Session.

    Instead, why dont you write a Java Concurrent Program?

    Thanks,
    Anil Passi

     

    Anonymous Anonymous said ... (3:01 PM) : 

    Thanks for the immediate reply Anil,

    As the user_id is passed as $2,as you said, can I pass that as $user_id in place of the parameter for the fnd_global.apps_initialize. Also can i include more positional parameters to use the resp_id and application_id;

    for example;

    shellscript_name= $1
    user_id = $2
    ..
    ..
    resp_id =$5
    application_id = $6

    and then in the sql session

    fnd_global.apps_initialize($user_id,$resp_id,$application_id)

    I have never used a java program, thats why I am proceeeding in this direction..

    I made much effort to complete this request and registered as host program, everything is working fine.. but I am concerned just about this part.

    Thanks again

     

    Blogger Anil Passi said ... (3:44 PM) : 

    OK, I understand.

    Please try the solution below:-

    1. Create Parameters to your host concurrent program
    2. These parameters will be ApplicationId and ResponsibilityId etc.
    3. Assign these default values of type SQL Statement. The SQL Statement will be select fnd_global.resp_id etc.
    5. In your hot program, access these are $5, $6 etc

    Thanks,
    Anil Passi

     

    Anonymous Anonymous said ... (8:04 PM) : 

    Thank you so much Anil,

    so I can assign the resp_id and application_id as $5 and $6 and then use it in the sql session.. This is what I wanted to know.. I shall work on this and let you know the positive results!!..

    thank you so much for your support.. I appreciate

     

    Anonymous Anonymous said ... (11:58 AM) : 

    Hi Anil,

    I have a question,
    while registering a host program,
    when passing the the parametrs to the shell script as positional parameters($1,$2,$3..), how does they know the value of these parameters?

    for example we know the

    shellscript_name = $0

    what does this really mean?

    Could you please put some light on what the positional parameters does and where the "value" of the parameters is getting assigned?

    Thanks

     

    Anonymous Anonymous said ... (6:41 AM) : 

    Hi Anil,

    Could you please explain how to assign a parameter of type sql statement?

    Well in the above posts, we need to create the parameters for resp_id and application_id.. How can I do this?

    Thanks,
    Anita

     

    Anonymous Anonymous said ... (7:53 AM) : 

    Hello Anil,

    Could you please explain how to pass the resp_id as parameter in the concurrent program parameter form screen.

    Thanks,
    Anita

     

    Anonymous Anonymous said ... (7:53 AM) : 

    Hello Anil,

    Could you please explain how to pass the resp_id as parameter in the concurrent program parameter form screen.

    Thanks,
    Anita

     

    Anonymous Anonymous said ... (7:16 AM) : 

    Hi Anil,

    This is regarding the same post, we discussed 2 days back. I shall brief it again.

    I have a host program which does start a sql session in itself and gives an fnd_request.submit_request. Hence it has a fnd_global.apps_initialize(user_id,resp_id,appl_id).

    Right now I have hard coded the parameters for fnd_global.

    Now when I register the host program, I am trying to define resp_id as a parameter for the concurrent program. Could you please explain how to pass resp_id as parameter?

    Thanks in advance
    Anita

     

    Anonymous Anonymous said ... (10:27 AM) : 

    Hi Anil ,
    Thank you for this useful site.
    My organization is recently moving to oracle apps R12. We have problem in the Arabic output of oracle reports, these report are defined to be called from concurrent requests, and the output type is HTML, but the output looks as rubbish . Any suggested solutions ??

     

    Anonymous Anonymous said ... (12:08 PM) : 

    I have a question on passing parameter to a report which is registerd as a concurrent program.

    I have a parameter "Operating unit".I have used the below part in the query of the report to enable this,


    AND hru.organization_id = :P_OPERATING_UNIT

    When the concurrent request for the report is submitted I want the parameter to act in such a way that, if the user gives null value, then the report must show all the results for all the operating unit, How can I make this possible? Now in such a situation, the report is not pulling any rows.

    Anyone please help,

    Thanks in advance.
    Anita

     

    Anonymous Anonymous said ... (11:41 AM) : 

    Hi Anil,

    Great blog!!

    I have a question regarding a report submitted as a concurrent program.

    I want to display "No Data Found" when the report output returns no rows.. How can this be made possible?

    Thanks in advance.

     

    Anonymous Anonymous said ... (5:04 AM) : 

    Hi,
    Please use the lexical parameter istead of bind parameters...
    read something that give you good idea on lexical....

    And regarding no data found to be displayed on the output...we can put a label called no_data_found in the layout and use the format trigger on it....thats it..

    Thanks,
    Kittu.

     

    Blogger Unknown said ... (2:03 AM) : 

    Hi Anil,
    Can you tell me as to what should be the syntax for calling a PL/SQL Stored Procedure concurrent program from a custom form , thru fnd_request.submit_request.....I have only 1 parameter in the procedure, other than errbuf and retcode......

     

    Anonymous Anonymous said ... (11:50 PM) : 

    Dear Anil,
    I would like to get some documents/presentations for financial modules such as AP,AR,CM,FA,GL etc..
    Mainly to know how data is flowing from one to another modules(if possible with flow chart)

    Thanks in advance..

    -Ram Prasad
    Qatar.

     

    Anonymous Anonymous said ... (12:24 AM) : 

    Hi Anita,

    You can try

    AND hru.organization_id = NVL(:P_OPERATING_UNIT,hru.organization_id)

     

    post a comment