Wednesday, October 21, 2009

Create Request Group using oracle api

Hi All,

Recently i got a requirement to create request and attach all the standard reports of GL to these request groups. It seems a lot tedious task to do manually. So i developed this script to copy the request group content to other request groups. This is very useful if you have to create multiple request groups and attach reports to all.

DECLARE
CURSOR c1 IS
SELECT b.concurrent_program_name programname,c.application_name appname
FROM fnd_request_group_units a
,fnd_concurrent_programs b
,fnd_application_tl c
,fnd_request_groups d
WHERE a.request_Group_id=d.request_Group_id
AND a.request_unit_id=b.concurrent_program_id
AND b.application_id=c.application_id
AND d.request_Group_name='GL Concurrent Program Group';
BEGIN
FOR rec IN c1 LOOP
fnd_program.add_to_group(program_short_name =>rec.programname
,program_application=>REC.APPNAME
,request_group=>'XX_GL_REQ_GRP'
,group_application=>'Custom Application');
END LOOP;
END;

Thanks & Regards,
Anto Joe Natesh

RETCODE & ERRBUFF Parameters in Concurrent Program

Hi ALL,

As we all know there are two mandatory parameters that need to be pased for all the procedures called
1.ERRBUFF
2.RETCODE..

Based on the business process if there is any undefined exeception occured while running concurrent program, we can end the concurrent program with Error/Warning.

Define ERRBUFF as the first parameter and Retcode as the second one. Mention the OUT variable type.

CREATE PROCEDURE PROCEDURE_NAME (errbuf  OUT VARCHAR2,
                                 retcode OUT VARCHAR2)

The retcode has three values returned by the concurrent manager
0--Success
1--Success & warning
2--Error

we can set the concurrent program to any of the three status by using these values in the retcode parameter

Example:
========

BEGIN
.....
EXCEPTION
     WHEN OTHERS THEN
        FND_FILE.PUT_LINE(FND_FILE.LOG,'Unhandled exception occurred in package. ErrMsg: '||SQLERRM);
        retcode='2';
END;

Even you can use fnd_concurrent.set_completion_Status to send the concurrent program to more status than success,error and warning.

Saturday, September 26, 2009

FND_GLOBAL.APPS_INITIALIZE for initializing session in Oracle Ebusiness suite



FND_GLOBAL.APPS_INITIALIZE is used for initializing the session before calling any public or private API's in Oracle Ebusiness suite. Its not required for all the API's but its recommended that you set this profile before making any calls to either private or public API. 


Listed below is a sample call to FND_GLOBAL.APPS_INITIALIZE function


fnd_global.APPS_INITIALIZE(user_id=>l_user_id, 
                           resp_id=>l_resp_id, 
                           resp_appl_id=>l_resp_appl_id);


l_user_id is the fnd user ID which will be utilized during the call. 
l_resp_id is the responsibility ID 
l_resp_appl_id is the responsibility application ID. 
You can use either sysadmin or use some user who has all the above listed responsibilities.


For SYSADMIN, utilize the following query to get the respective values


select fnd.user_id , 
       fresp.responsibility_id, 
       fresp.application_id 
from   fnd_user fnd 
,      fnd_responsibility_tl fresp 
where  fnd.user_name = 'SYSADMIN' 
and    fresp.responsibility_name = 'Order Management Super User';


Another option is Help > Diagnostics > Examine and get the values from $profile session values.


Thanks & Regards,
Anto Joe Natesh I