OraFAQ Forum: Oracle Fusion Apps & E-Business Suite » How to submit a conc request from a trigger which gets fired when a row is inserted in custom schema

Home » Applications » Oracle Fusion Apps & E-Business Suite » How to submit a conc request from a trigger which gets fired when a row is inserted in custom schema

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to submit a conc request from a trigger which gets fired when a row is inserted in custom schema [message #121170]

Thu, 26 May 2005 11:05

rsury
Messages: 26
Registered: May 2005

Junior Member

This is the problem I am facing.
External Application using a different schema lets say extdb on 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

Report message to a moderator

Re: How to submit a conc request from a trigger which gets fired when a row is inserted in custom sc [message #121250 is a reply to message #121170]

Fri, 27 May 2005 03:30

Alien
Messages: 292
Registered: June 1999

Senior Member

Hi,

I assume you're inserting from the extdb schema? Probably it's failing because of insufficient privileges. FND_REQUEST is owned by APPS. And you'll need execute privileges on it, plus privileges on the APPLSYS tables.

When you insert manually, do you use the same user as the one inserting the rows?

Regards,

Arian

Report message to a moderator

Re: How to submit a conc request from a trigger which gets fired when a row is inserted in custom sc [message #121303 is a reply to message #121250]

Fri, 27 May 2005 09:13

rsury
Messages: 26
Registered: May 2005

Junior Member

Arian,

The extdb has privileges to insert into the custom schema under Oracle Apps db. And the insert works fine.
The trigger gets executed on insert into this custom table from external database.
Only the FND_REQUEST.SUBMIT does not get fired.

Report message to a moderator

Re: How to submit a conc request from a trigger which gets fired when a row is inserted in custom sc [message #121332 is a reply to message #121303]

Fri, 27 May 2005 11:57

Alien
Messages: 292
Registered: June 1999

Senior Member

Hi,

But to kick off the request (that is run fnd_request.submit, and then insert values into the fnd_xxxx tables), you'll need more than just privileges on the custom schema.

FND_REQUEST handles all errors, due to not being able to insert into the fnd_xxx tables. And it will not prevent the trigger from executing, or the rows from being inserted.

Am I understanding correctly, that it won't run when rows are inserted by the extdb user, but it does work when you insert the rows with apps-user?

Regards,

Arian

P.S. If you like, I can dig up an old script with grants/privileges/users that I used before.

Report message to a moderator

Re: How to submit a conc request from a trigger which gets fired when a row is inserted in custom sc [message #121346 is a reply to message #121332]

Fri, 27 May 2005 13:22

rsury
Messages: 26
Registered: May 2005

Junior Member

Arian,

Your understanding is correct. As apps user it is not giving any problem. Only when external user it gives problem.

Report message to a moderator

Re: How to submit a conc request from a trigger which gets fired when a row is inserted in custom sc [message #121412 is a reply to message #121346]

Sat, 28 May 2005 11:04

Alien
Messages: 292
Registered: June 1999

Senior Member

Ok.

I have to dig up the right script. But I can give you a sample script, to make it work. That will be after the weekend. (Monday or Tuesday).

Regards,

Arian

Report message to a moderator

Re: How to submit a conc request from a trigger which gets fired when a row is inserted in custom sc [message #121419 is a reply to message #121412]

Sat, 28 May 2005 14:35

rsury
Messages: 26
Registered: May 2005

Junior Member

Thanks for your time. I will wait for your script.
Have a nice weekend.

Report message to a moderator

Re: How to submit a conc request from a trigger which gets fired when a row is inserted in custom sc [message #122152 is a reply to message #121250]

Fri, 03 June 2005 07:50

sudhirkumar
Messages: 1
Registered: June 2005
Location: Hyderabad, India

Junior Member

Hi,

You cannot submit a concurrent request from a PL/SQL trigger. Because after submitting the concurrent request You need to apply COMMIT. A trigger wont allow you to apply COMMIT directly in its body. SO You need to write a PL/SQL procedure or packaged procedure to submit your Concurrent Program. Pass the required parameters and submit the concurrent program in the procedure body. After submitting the request, you write commit.Call this Procedure from your trigger body.
This will work

Regards
Sudhir

[Updated on: Fri, 03 June 2005 07:51]

Report message to a moderator

Re: How to submit a conc request from a trigger which gets fired when a row is inserted in custom sc [message #203981 is a reply to message #121170]

Fri, 17 November 2006 03:35

cherryparadise
Messages: 2
Registered: November 2006

Junior Member

I am encountering the same problem!

My procedure is a bit simple than rsury's...
I copy the procedure from the internet and others do work.
Would Arain mind sending me a copy of your copy as reference?
MANY MANY THANKS!

[Updated on: Fri, 17 November 2006 03:37]

Report message to a moderator