1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

passing parameter using fnd_request

Discussion in 'Oracle Apps Technical' started by vamsioracle, Apr 27, 2009.

  1. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    Hi


    I have a procedure that i need to schedule to run. I created a concurrent program for that. But the procedure has four parameters. I need to call this concurrent program from one more procedure where the values are retrieved.

    Code (SQL):
    CREATE OR REPLACE PROCEDURE cbtt_update(fromname IN Varchar2, sender IN varchar2, begindate IN DATE, enddate IN varchar2)
    IS
    BEGIN
    ..............

    Business logic
    ...........

    END cbtt_update
    I created a concurrent program cbttupdate for the above code. Now i have one more procedure which calls this concurrent program and also the parent procedure for getting the parameter values.

    Code (SQL):

    CREATE OR REPLACE PROCEDURE cbtt_getvalues
    IS
       fromname    VARCHAR2 (100);
       sender      VARCHAR2 (100);
       begindate   DATE;
       enddate     DATE;
       conreqid    NUMBER         := 0;
    BEGIN
       SELECT creator_id, selector, begin_date, end_date
         INTO fromname, sender, begindate, enddate
         FROM TABLE
         WHERE ...;

       concreqid :=
          fnd_request.submit_request ('FND',
                                      cbttupdate,
                                      '',
                                      SYSDATE,
                                      FALSE,
                                      CHR (0)
                                     );
    END cbtt_getvalues;
     
    Now the above request wil just cal the procedure. Where should i add the parameters. Is this the right way to call.

    concreqid := fnd_request.submit_request ('FND',cbttupdate,'',SYSDATE,FALSE,CHR (0),fromname,sender,begindate,enddate);

    vamsi
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    hi vamsi,

    I am a little confused. Let me see

    1. You have procedure1 that is retrieving certain parameter values.
    2. You have procedure2 that is doing all the work.
    3. To schedule procedure2, you have created a concurrent request.
    4. Now your problem is how to pass the parameter values from procedure1 to procedure 2.

    If I understood correctly, why can't you just do everything in a single procedure and schedule it's execution as a job?
     
  3. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    hi vamsi bro, how r u

    well in your call to fnd_request.submit_request you can pass the arguments to your request (report) in the order in which you registered them for the report. But in your case, i am not too sure whether passing the arguments for the procedure parameters will work. I don't think it will. I haven't tried anything like this so you have to experiment and find out (and please let me know).

    Also I would advice you that you have the oracle applications developers guide handy when trying out. It explains in detail how to use the fnd_request function.
     
  4. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    Hi Sadik,

    You are right, we can do that in one procedure . the codes i posted are just models of my procedures. Actually, the cbtt_getvalues has 278 lines of code and i need just 4 values from it, and this procedure has a lot of dependencies with other modules.

    Now i want the four values from this and use in the other procedure which should run only once nad should be scheduled. that is the reason why i had to do this.

    vamsi
     
  5. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    i see

    I would first suggest that if it's just a question of scheduling an update , do it the normal oracle database way, through jobs. In your situation the most practical way seems to be write a procedure that can get the values and do the update and schedule it with a job. This might be difficult to write but it would be so much easier to administrate. Writing the code would be a one time process but administrating a continuous one. Else you might be having a hell time debugging if something goes wrong.
     
  6. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    Hi Apps_expert bro , sori i don't knoow ur name :) . I am fine.

    I used dbms_job.submit instead of fnd....


    thanks
    vamsi
     
  7. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    Hi Vamsi,

    Good Decision. However i read your other thread where you are having problems submitting the job. Post the procedure code in that thread as simply_dba asked for.

    And my name is Sunil Raghunathan.... been an old friend of our admin Sadik :)