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
