Problem statement:
Cannot set the fnd_apps_initialize in the DB triggers which calls the package
to submit the concurrent requests.
Requirement: To
trigger the batch concurrent programs from the DB trigger based on the batch_type and responsibility
derived from the lookups defined based on the Purchasing unit value.
Error encountered: ORA-02074: Cannot SET NLS
Related errors: ORA-02074: Cannot SET NLS in a Distributed Transaction
Useful Info:
please follow below and see its helpful for your issue:
ORA-04092: Cannot Set NLS In A Trigger: Error Occurs When Entering SIT Or Absence Information In Core HR and SSHR [ID 758435.1]
Why are Concurrent Programs Calling FND_GLOBAL.APPS_INITIALIZE Using DBLinks Failing? [ID 466800.1]
ORA-04092: Cannot SET NLS in a Trigger Error When Creating A Formula [ID 456358.1]
ORA-02074: Cannot SET NLS in a Distributed Transaction [ID 556391.1]
Related errors: ORA-02074: Cannot SET NLS in a Distributed Transaction
Oracle error -20001: ORA-20001: Oracle error -4092:
ORA-04092: cannot SET NLS in a trigger has been detected in fnd_global.set_nls.set_parameters('NLS_LANGUAGE','AMERICAN')
has been detected in fnd_global.set_nlsUseful Info:
please follow below and see its helpful for your issue:
ORA-04092: Cannot Set NLS In A Trigger: Error Occurs When Entering SIT Or Absence Information In Core HR and SSHR [ID 758435.1]
Why are Concurrent Programs Calling FND_GLOBAL.APPS_INITIALIZE Using DBLinks Failing? [ID 466800.1]
ORA-04092: Cannot SET NLS in a Trigger Error When Creating A Formula [ID 456358.1]
ORA-02074: Cannot SET NLS in a Distributed Transaction [ID 556391.1]
Solution Built:
Write a procedure call the fnd_apps_initilize and make the procedure AUTONOMUS_TRANSACTION, call the procedure from the trigger, (Ideally After Update) and then a statement to call the concurrent requests.
Write a procedure call the fnd_apps_initilize and make the procedure AUTONOMUS_TRANSACTION, call the procedure from the trigger, (Ideally After Update) and then a statement to call the concurrent requests.
XX_CUSTOM_BATCH_HEADERS a Table which stores the batch detail information
XX_CUSTOM_BATCH_DETAILS à Table to store the
dervied values such as batch_type, purchasing_unit and responsibility thru
which the concurrent program should be submitted.
Trigger on xx_custom_batch_headers
à XX_CUSTOM_BATCH_HEADERS_BIR
*************************************************************************
CREATE OR REPLACE TRIGGER XX_CUSTOM_BATCH_HEADERS_BIR
EFORE INSERT ON XX_CUSTOM.XX_CUSTOM_BATCH_HEADERS
FOR EACH ROW
WHEN (
NEW.STATUS='PENDING'
)
DECLARE
l_status VARCHAR2(25);
BEGIN
EXECUTE IMMEDIATE 'ALTER SESSION SET NLS_LANGUAGE= ''AMERICAN''';
--Procedure which filters data based on the batch_id, batch_type and inserts records into xx_custom_batch_details table this procedure can be different based on the requirement
xx_custom_common_pkg.insert_records_batch_det(:NEW.batch_id
,:NEW.batch_type
, l_status);
:NEW.ERR_MESSAGE := l_status;
IF l_status = 'SUCCESS'
THEN
:NEW.REQUEST_ID := 'Refer to xx_custom_batch_details';
:NEW.STATUS := 'COMPLETED';
ELSE
:NEW.REQUEST_ID := '';
:NEW.STATUS := 'ERROR';
END IF;
END XX_CUSTOM_BATCH_HEADERS_BIR;
/
*************************************************************************
Trigger on xx_custom_batch_details XX_CUSTOM_BATCH_DETAILS_BIR
*************************************************************************
CREATE OR REPLACE TRIGGER XX_CUSTOM_BATCH_DETAILS_BIR
AFTER INSERT ON XX_CUSTOM.XX_CUSTOM_BATCH_DETAILS
FOR EACH ROW
DECLARE
l_request_id fnd_concurrent_requests.request_id%TYPE;
lt_resp_key fnd_responsibility.responsibility_key%TYPE;
lt_user_name fnd_user.user_name%TYPE;
lt_application_short_name fnd_application.application_short_name%TYPE;
lt_concurrent_program_name fnd_concurrent_programs.concurrent_program_name%TYPE;
lt_err_message xx_custom_batch_headers.err_message%TYPE := '';
result BOOLEAN;
lv_errbuf varchar2(250);
ln_retcode number;
BEGIN
dbms_output.put_line('Responsibility'||:NEW.RESPONSIBILITY_KEY);
XX_CUSTOM_SUBMIT_REQUEST_PKG.SUBMIT_REQ_program(errbuf => lv_errbuf
,retcode => ln_retcode
,p_batch_id => :new.batch_id
,p_batch_type => :new.batch_type
,p_resp_key=>
:new.responsibility_key ,p_pur_unit=> :new.purchasing_unit);
dbms_output.put_line('Request ID'||l_request_id);
END XX_CUSTOM_BATCH_DETAILS_BIR;
/
*************************************************************************
PL/SQL Package which sets the FND_APPS_INTITIALISE and make the package PRAGMA AUTONOMOUS_TRANSACTION to run in separate session.
*************************************************************************
CREATE OR REPLACE PACKAGE BODY APPS.XX_CUSTOM_SUBMIT_REQUEST_PKG
AS
PROCEDURE submit_req_program(errbuf OUT NOCOPY VARCHAR2
,retcode OUT NOCOPY NUMBER
,p_batch_id IN NUMBER
,p_batch_type IN VARCHAR2
,p_resp_key IN VARCHAR2
,p_pur_unit IN VARCHAR2)
IS
l_request_id fnd_concurrent_requests.request_id%TYPE;
lt_resp_key fnd_responsibility.responsibility_key%TYPE;
lt_user_name fnd_user.user_name%TYPE;
lt_application_short_name fnd_application.application_short_name%TYPE;
lt_concurrent_program_name fnd_concurrent_programs.concurrent_program_name%TYPE;
lt_err_message xx_custom_batch_details.err_message%TYPE := '';
lv_batch_type VARCHAR2(50) := P_BATCH_TYPE;
lv_errbuf VARCHAR2(250);
ln_retcode NUMBER;
l_resp_id NUMBER;
l_appl_id NUMBER;
l_user_id NUMBER;
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
dbms_output.put_line('Inside 1st concurrent Program');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Inside the concurrent Program');
BEGIN
SELECT meaning
INTO lt_user_name
FROM fnd_lookup_values
WHERE lookup_type = 'XX_CUSTOM_INTERFACE'
AND lookup_code = 'USER_NAME'
AND language = xx_Custom_common_pkg.get_lang;
EXCEPTION
WHEN OTHERS THEN
lt_err_message := lt_err_message || 'Error retrieving user name';
lt_user_name := 'XXX';
FND_FILE.PUT_LINE(FND_FILE.LOG,lt_err_message);
END;
BEGIN
SELECT
flv.meaning
, flv.tag
INTO
lt_concurrent_program_name
,lt_application_short_name
FROM
fnd_lookup_values flv
WHERE
flv.lookup_type = 'XX_CUSTOM_CONC_PROG_TRIGGER'
AND flv.enabled_flag='Y'
AND flv.start_date_active <=SYSDATE
AND NVL(flv.end_date_active,SYSDATE) >=SYSDATE
AND language = xx_Custom_common_pkg.get_lang
AND UPPER(flv.lookup_code) = UPPER(p_batch_type);
EXCEPTION
WHEN OTHERS THEN
--lt_err_message := lt_err_message || ' Batch Type not defined in lookup';
--retcode := -1;
FND_FILE.PUT_LINE(FND_FILE.LOG,'Batch Type not defined in lookup');
END;
IF
-- Procedure which sets FND_APPS_INITIALIZE
xx_custom_common_pkg.setup_apps_user(p_resp_key,lt_user_name) THEN
-- Procedure which sets FND_APPS_INITIALIZE
xx_custom_common_pkg.setup_apps_user(p_resp_key,lt_user_name) THEN
FND_FILE.PUT_LINE(FND_FILE.LOG,'Before Calling the program');
dbms_output.put_line('Before Calling the program');
BEGIN
l_request_id :=
fnd_request.submit_request(application => lt_application_short_name,
program => lt_concurrent_program_name,
description => NULL,
start_time => to_char(SYSDATE,'DD-MON-YYYY HH24:MI:SS'),
sub_request => FALSE,
argument1 => p_batch_id,
argument2 => p_pur_unit);
EXCEPTION
WHEN OTHERS THEN
lt_err_message := lt_err_message || fnd_message.get;
lt_user_name := 'XXX';
FND_FILE.PUT_LINE(FND_FILE.LOG,'Error while submitting the request: '||lt_err_message);
dbms_output.put_line('Error while submitting the request: '||lt_err_message);
retcode := -1;
END;
IF l_request_id = 0 THEN
dbms_output.put_line('Failed to submit the request with Req_ID:'||l_request_id );
ELSE
dbms_output.put_line('Updating the batch details table');
dbms_output.put_line('Resp:'||p_resp_key);
dbms_output.put_line('batch_id:'||p_batch_id);
dbms_output.put_line('batch_type:'||p_batch_type);
--Updating the batch_details table with requests
-- UPDATE xx_custom_batch_details
-- SET request_id = l_request_id
-- WHERE batch_id = p_batch_id
-- AND batch_type = p_batch_type
-- AND responsibility_key = p_resp_key;
dbms_output.put_line('Submitted the request with Req_ID:'||l_request_id );
END IF;
ELSE
lt_err_message := lt_err_message || ' Error initialising responsibility/user';
FND_FILE.PUT_LINE(FND_FILE.LOG,lt_err_message);
dbms_output.put_line(' Error initialising responsibility/user');
retcode := -1;
END IF;
retcode := 0;
dbms_output.put_line('Before commit');
COMMIT;
dbms_output.put_line('After commit');
EXCEPTION
WHEN OTHERS THEN
retcode := 2;
dbms_output.put_line('Others Exception in Main Program:');
FND_FILE.PUT_LINE(FND_FILE.LOG,'Others Exception in Main Program:'||substr(SQLERRM,1,200));
END;
END;
/
*************************************************************************
Thank you so much to Sandeep Jagirdar for sharing this post with his experience on the real time issue.
Cheers,
Rajesh
