How to submit conc. request from a trigger on insert into custom table.
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: How to submit conc. request from a trigger on insert into custom table.

  1. #1
    Join Date
    May 2005
    Posts
    1

    How to submit conc. request from a trigger on insert into custom table.

    This is the problem I am facing.
    External Application using a different schema lets say extdb on a different server. This writes into a table in a schema called appscustom schema. Appscustom schema is under Oracle Apps .
    A trigger in Oracle Apps to be executed on insert into the appscustom schema table.
    This trigger inturn will run a concurrent job to create the invoice and gl interface tables.
    THe problem I am facing is, The trigger get executed but the concurrent job does not get triggered/run.
    Here is the trigger. Let me know, what might be wrong.
    THe values for vRESP_APPL_Id,vRESP_ID, vUSER_ID becomes null in the test_pims table.
    Whereas when I manually insert from backend into appscustom schema table the concurrent job gets submitted.
    ~~~~~~~~~~~~~~~~~~~~~~~~~~
    -- Start of DDL Script for Trigger APPS.RWJF_PIMS_ORACLE_INT
    -- Generated 5/25/2005 22:10:52 from APPS@HENRY_DEVL
    CREATE OR REPLACE TRIGGER rwjf_pims_oracle_int
    AFTER
    INSERT
    ON rwjf_pimstxnbatch
    REFERENCING NEW AS NEW OLD AS OLD
    FOR EACH ROW
    WHEN (new.status = 'U' )
    Declare
    ReturnCode BOOLEAN;
    ConcReqID NUMBER := 0;
    vRESP_APPL_ID NUMBER;
    vRESP_ID NUMBER;
    vUSER_ID NUMBER;
    LoadToAp NUMBER :=0;
    LoadToGl NUMBER :=0;
    Begin

    -- vRESP_APPL_ID := apps.fnd_profile.value(200); -- AP --('RESP_APPL_ID');
    -- vRESP_ID := apps.fnd_profile.value(20639); -- 20639 for payables mgr. for rwjf_payables Mgr (50001); --('RESP_ID');
    -- vUSER_ID := apps.fnd_profile.value(1247); -- 1247 for sury 1065 for interface -- ('USER_ID');

    LoadToAp := 0;

    SELECT COUNT(*)
    INTO LoadToAp
    FROM rwjf.RWJF_PimsTxnBatchDtl
    WHERE GL_OR_AP = 'A'
    AND pims_txn_batch_id = :NEW.Pims_Txn_Batch_id;

    LoadToGl := 0;

    SELECT COUNT(*)
    INTO LoadToGl
    FROM rwjf.RWJF_PimsTxnBatchDtl
    WHERE GL_OR_AP = 'G'
    AND pims_txn_batch_id = :NEW.Pims_Txn_Batch_id;

    IF LoadToAp > 0 THEN
    vRESP_APPL_ID := 200; -- Application Id 200 Account payables
    vRESP_ID := 50001; -- RWJF_Payables Mgr for user interface
    vUSER_ID := 1065; -- user id for user name interface

    apps.fnd_global.apps_initialize(vUSER_ID,vRESP_ID,vRESP_APPL_ID);

    ConcReqID := 0;

    ReturnCode := FND_REQUEST.SET_MODE(TRUE);
    ConcReqID := FND_REQUEST.SUBMIT_REQUEST('RWJF',
    'RWJF_PIMS_INT',
    '', '', FALSE,
    :NEW.Pims_Txn_Batch_id,'AP', chr(0),
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','');
    END IF;

    IF LoadToGl > 0 THEN
    vRESP_APPL_ID := 20003;
    vRESP_ID := 50003;
    vUSER_ID := 1065;

    apps.fnd_global.apps_initialize(vUSER_ID,vRESP_ID,vRESP_APPL_ID);

    ConcReqID := 0;

    ReturnCode := FND_REQUEST.SET_MODE(TRUE);
    ConcReqID := FND_REQUEST.SUBMIT_REQUEST('RWJF',
    'RWJF_PIMS_INT',
    '', '', FALSE,
    :NEW.Pims_Txn_Batch_id,'GL',chr(0),
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','','','','',
    '','','','','','','');
    END IF;

    insert into test_pims
    values
    ('Test3',:New.Pims_Txn_Batch_id,vUSER_ID,vRESP_ID,vRESP_APPL_ID);
    insert into test_pims
    values
    ('Test3',ConcReqID,vUSER_ID,vRESP_ID,vRESP_APPL_ID);
    IF ConcReqID = 0 THEN
    DBMS_OUTPUT.PUT_LINE('Problem Submitting Program to get pims txn batch'); /* Handle Error */
    END IF;
    End;



    -- End of DDL Script for Trigger APPS.RWJF_PIMS_ORACLE_INT

  2. #2
    Join Date
    Dec 2000
    Posts
    138
    since its apps, you are using FND_REQUEST.SUBMIT_REQUEST, I am assuming this is the using job submit. If its using dbms_job, shouldnt it need a commit. And since you are insided a trigger you may want to reconsider how you are going to do.
    HTH
    -dharma

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width