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
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 Go to next message
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
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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
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.
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 Go to previous messageGo to next message
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.

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 Go to previous messageGo to next message
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.


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 Go to previous messageGo to next message
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
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 Go to previous messageGo to next message
rsury
Messages: 26
Registered: May 2005
Junior Member
Thanks for your time. I will wait for your script.
Have a nice weekend.
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 Go to previous messageGo to next message
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

icon14.gif  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 Go to previous messageGo to next message
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

Re: How to submit a conc request from a trigger which gets fired when a row is inserted in custom sc [message #241440 is a reply to message #203981] Tue, 29 May 2007 10:55 Go to previous messageGo to next message
sujayts
Messages: 1
Registered: November 2005
Junior Member
Hi,

I have the same problem too.

it would be of great help if you Could mail the script to me as well.

Thanks,
Sujay
Re: How to submit a conc request from a trigger which gets fired when a row is inserted in custom sc [message #241548 is a reply to message #121170] Tue, 29 May 2007 23:53 Go to previous message
ramkic
Messages: 17
Registered: May 2004
Junior Member
Hi,
Did you follow Sudhirkumar's posting? Please follow that, it will work.

Thanks
Regards
ramkic
Previous Topic: Question - Look up Workflow Notification Preferences via SQL
Next Topic: Item Number Field in Autolockbox
Goto Forum:
  


Current Time: Thu Dec 12 07:58:45 CST 2024