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!

calling a concurrent program from backend

Discussion in 'Oracle Apps Technical' started by vamsioracle, Mar 19, 2009.

  1. vamsioracle

    vamsioracle Forum Expert

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


    I registered a stored procedure in apps with the following details,

    short name: delegateproc
    attached to application object library

    now i want to run this from back end and in a trigger code

    Code (Text):


    create or replace trigger xx
    on wf_routing_rules
    after insert
    for each row

    begin

    fnd_request.submit_request('FND','DELEGATEPROC',NULL,sysdate,'FALSE','','',''.......);

    end;

     

    is the above syntax correct. I tried this annd it is not working.

    vamsi
     
  2. Kirti

    Kirti Forum Advisor

    Messages:
    46
    Likes Received:
    12
    Trophy Points:
    130
    Hi Vamsi, try this

    Code (Text):

    CREATE OR REPLACE TRIGGER xx
       AFTER INSERT
       ON wf_routing_rules
       FOR EACH ROW
    BEGIN
       concreqid := 0;
       returncode := fnd_request.set_mode (TRUE);
       concreqid :=
          fnd_request.submit_request ('FND',
                                      'DELEGATEPROC',
                                      '',
                                      SYSDATE,
                                      FALSE,
                                      CHR (0)
                                     );

       IF concreqid = 0
       THEN
          DBMS_OUTPUT.put_line ('Problem Submitting Program DELEGATEPROC');
    /* Handle Error */
       END IF;
    END;
     
     
  3. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    i tried the code, the trigger was sucesful. but i got the error at vacatin rules page

    ORA-04092: cannot SET SAVEPOINT in a trigger
     
  4. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    I cahnged the code:

    I created a procedure to submit the concurrent request:

    Code (Text):

    CREATE OR REPLACE PROCEDURE cbtt_delegate_rule_proc
    IS
       concreqid   NUMBER;
       PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
       concreqid := 0;
    --returncode := fnd_request.set_mode (TRUE);
       concreqid :=
          fnd_request.submit_request ('FND',
                                      'DELEGATEPROC',
                                      '',
                                      SYSDATE,
                                      FALSE,
                                      CHR (0)
                                     );
       COMMIT;
    END cbtt_delegate_rule_proc;
     
    In the Trigger i am calling this procedure

    Code (Text):

    ---Trigger Code----------
    CREATE OR REPLACE TRIGGER cbtt_delegate_rule_trigger
       AFTER INSERT
       ON wf_routing_rules
       FOR EACH ROW
    DECLARE
       PRAGMA AUTONOMOUS_TRANSACTION;
    BEGIN
       cbtt_delegate_rule_proc ();
    END cbtt_delegate_rule_trigger;
    --------
     
    Now everything is compiled and i am able to create a vacation rule. But the procedure didn't submit the concurrent request.
     
  5. apps_expert

    apps_expert Forum Expert

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

    First you have to try and see whether your concurrent job is at all executed from backend or not. Just run this code in the backend

    Code (Text):

    l_conc_id := FND_REQUEST.SUBMIT_REQUEST('APP_SHORT_NAME','CONC_REQUEST_NAME', '','',FALSE, PARAMETER1, PARAMETER2,PARAMETER3, CHR(0));
     
    which in your case will be
    Code (Text):

    l_conc_id := FND_REQUEST.SUBMIT_REQUEST('FND','DELEGATEPROC', '','',FALSE, CHR(0));
     
    Whether you submit from a trigger or procedure is secondary. first check whether this at all calls your concurrent request. If it returns 0, it means there is something wrong with the way you have registered the stored procedure in apps and attached it to application object library.

    Also you need to check the trace of your program if it is returning 0. To enable the trace, as oracle application developer responsibility open the concurrent program and set the trace enable to yes. Run the program and check the trace.
     
  6. apps_expert

    apps_expert Forum Expert

    Messages:
    325
    Likes Received:
    28
    Trophy Points:
    330
    Location:
    Chennai, India
    hmmm i forgot... don't forget to fnd_request.set_mode(TRUE); before you run. Ok the script will be
    Code (Text):

    BEGIN
       l_boolean := fnd_request.set_mode (TRUE);
       l_conc_id :=
          fnd_request.submit_request ('FND','DELEGATEPROC','',FALSE,CHR (0));

       IF l_conc_id = 0
       THEN
          DBMS_OUTPUT.put_line ('Not Submitted');
       ELSE
          DBMS_OUTPUT.put_line ('Submitted with ID ' || l_conc_id);
       END IF;
    END;
     
     
  7. vamsioracle

    vamsioracle Forum Expert

    Messages:
    98
    Likes Received:
    1
    Trophy Points:
    140
    Thanks all,

    It worked for me.

    I was getting Mutating error in the trigger was operating on the same table on which i select the data. In order to avoid this, i had to create a procedure to select the data, one more procedure to call the first procedure and a trigger to call the second procedure and the trigger is run with autonomous transaction. Only this combination worked and i tried with all the other possibilities

    Anyways, thanks a lot, it s working for me.