Enable Form Personalization in Oracle APPS

Here in this post we will see how to enable the personalization in oracle EBS.

Some time when you want to personalize a form in oracle apps, you need to navigate to

Help -> Diagnostics -> Custom Code -> Personalize

When doing this you might encounter the following error

The enable personalization in EBS, set the profile option "Utilities:Diagnostics" to "Yes"

Switch responsibility to make this effective and this will resolve the error.

  

For any suggestion or issues with the above code, please leave your comments. I will try to reply back as soon as possible

How to change Concurrent Request View output to view in Browser

Often we have the concurrent program output shown in default APPS viewer, sometime the user like it to be shown in a browser rather seeing it in the default viewer.

Viewing Reports in HTML, Reports that produce an output other than a text file (for example HTML or PDF) are displayed in a browser.

Here is a way how you can make your reports output to show in a Browser.

If you set the profile option VIEWER: TEXT to Browser, your text files are also displayed in a browser as opposed to the Report Viewer.

VIEWER: TEXT may also be set to display your output in a text editor such as Microsoft Word, or in a spreadsheet such as Microsoft Excel.

For any suggestion or issues with the above code, please leave your comments. I will try to reply back as soon as possible

DB Link Between Two Databases in Oracle APPS

Here in this post we will discuss on how to create a DB link between tow Databases in oracle apps

This below code snippet will create a DB Link between the schema that you are running this query from and the host database mentioned in the script and the name of the db link is 'dev_db'.

          CREATE PUBLIC DATABASE LINK

            dev_db

          CONNECT TO

            apps

          IDENTIFIED BY

            apps

          USING 'HOST:PORT/SID';    

once this link has been created, you can access the objects of one database form another referencing the db link name.

for example:

          INSERT INTO xx_emp_table@dev_db

             SELECT *

               FROM xx_emp_table;

For any suggestion or issues with the above code, please leave your comments. I will try to reply back as soon as possible

Kill Current Session and Update Concurrent Program Status

Here in this post we will discuss on how to kill a background session and update the concurrent program's status.

Kill the Session

The following query will pick up all the active session which is running. Find the one which your concurrent program makes use of.

SELECT SID, serial#, module, action, status

  FROM v$session;

 Then use the below command to the session.

ALTER SYSTEM KILL SESSION 'SID,SERIAL#' -- Use the values which corresponds to your request --

Update Concurrent Program Status

Then update the concurrent program status to Terminated

UPDATE fnd_concurrent_requests

   SET phase_code = 'C',

       status_code = 'X'

 WHERE request_id = &REQUEST_ID;

COMMIT;

For any suggestion or issues with the above code, please leave your comments. I will try to reply back as soon as possible

RTF Techniques - Get your report parameters in the XML output

Here in this post, I am telling you how to get the report parameters in XML output.

Should the report parameters of an RDF report be available in the XML output ?

The short but rather mean answer would be 'Yes- they should'. In fact they really should be but they are not available in Oracle Report XML output.

The report parameters will not be available in the XML output unless you are passing the parameter value to a formula column/placeholder col. You can however display the parameter values in the template after you run the concurrent request in the apps and put the below code in your template...

Enter <?param@begin:P_START_DATE?> into a Text Form Field at the top of the RTF and then create another Text Form Field to display <?$P_START_DATE?>.

Update the template in the Apps and then run the concurrent request for the report.

That will display the parameter P_START_DATE.

<?param@begin:PARAMTER_NAME?> where parameter name refers to the internal name on the concurrent  program form

to reference the value in the template either to display it or perform calculations on it use

<?$PARAMETER_NAME?> - note the '$' sign.

At runtime the concurrent  manager will pass these values to the template. Remember they will not magically appear on the template you must create the reference to them (param@begin) and then use them in the template explicitly.

You need to be on 11.5.10.2 or higher to get this functionality, the publisher level does not matter.

Sample codes

<?param@begin:P_WEEK_ENDING_DATE_FROM?>             -- Initialize the parameter - token name

<?xdofx:(substr($P_WEEK_ENDING_DATE_FROM,9,2)||'-'||

  decode(substr($P_WEEK_ENDING_DATE_FROM,6,2),'01','JAN','02','FEB','03','MAR','04','APR','05','MAY',

                                              '06','JUN','07','JUL','08','AUG','09','SEP','10','OCT',

                                              '11','NOV','12','DEC')||'-'||

         substr($P_WEEK_ENDING_DATE_FROM,1,4))

?>                                                   -- To format the date parameter in RTF.

Hint: you got to have the data in xml as this format:

YYYY-MM-DDThh:mm:ss+HH:MM or YYYY-MM-DD to format those fields.

For any suggestion or issues with the above code, please leave your comments. I will try to reply back as soon as possible. 

How to Add Responsibility to User from Back-end in Oracle Apps

In Oracle Applications, A responsibility can be added to a user using the “AddResp” procedure of “fnd_user_pkg”.

the "AddResp" Procedure has the following Parameters.

AddResp Procedure parameters

-----------------------------------------------------

PROCEDURE AddResp(

    username       VARCHAR2,    -- User Name --

    resp_app       VARCHAR2,    -- Responsibility Application Short Name --

    resp_key       VARCHAR2,    -- Responsibility Key --

    security_group VARCHAR2,    -- Security Group Id --

    description    VARCHAR2,    -- Any Description --

    start_date DATE,            -- Strat Date -- Default SYSDATE --

    end_date DATE               -- End Date --Default NULL --

  );

Here is the query to find the parameter values

--------------------------------------------------------

SELECT fa.application_short_name,

       fr.responsibility_key,

       fsg.security_group_key,

       frt.description

  FROM apps.fnd_responsibility fr,

       fnd_application fa,

       fnd_security_groups fsg,

       fnd_responsibility_tl frt

 WHERE frt.responsibility_name = 'System Administrator'

   AND frt.LANGUAGE = USERENV ('LANG')

   AND frt.responsibility_id = fr.responsibility_id

   AND fr.application_id = fa.application_id

   AND fr.data_group_id = fsg.security_group_id;

API to add responsibility to user

-------------------------------------------------------------

-- You can use following api to add responsibility to the user you want. It --- will prompt you for the username and responsibility name.

-------------------------------------------------------------

DECLARE

   v_user_name             VARCHAR2 (30)  := '&user_name';

   v_responsibility_name   VARCHAR2 (100) := '&responsibility_name';

   v_application_name      VARCHAR2 (100) := NULL;

   v_responsibility_key    VARCHAR2 (100) := NULL;

   v_security_group        VARCHAR2 (100) := NULL;

   v_description           VARCHAR2 (100) := NULL;

BEGIN

   SELECT fa.application_short_name, fr.responsibility_key,

          fsg.security_group_key, frt.description

     INTO v_application_name, v_responsibility_key,

          v_security_group, v_description

     FROM apps.fnd_responsibility fr,

          fnd_application fa,

          fnd_security_groups fsg,

          fnd_responsibility_tl frt

    WHERE frt.responsibility_name = v_responsibility_name

      AND frt.LANGUAGE = USERENV ('LANG')

      AND frt.responsibility_id = fr.responsibility_id

      AND fr.application_id = fa.application_id

      AND fr.data_group_id = fsg.security_group_id;

   fnd_user_pkg.addresp (username            => v_user_name,

                         resp_app            => v_application_name,

                         resp_key            => v_responsibility_key,

                         security_group      => v_security_group,

                         description         => v_description,

                         start_date          => SYSDATE,

                         end_date            => NULL

                        );

   COMMIT;

   DBMS_OUTPUT.put_line (   'Responsiblity '

                         || v_responsibility_name

                         || ' is attached to the user '

                         || v_user_name

                         || ' Successfully'

                        );

EXCEPTION

   WHEN OTHERS

   THEN

      DBMS_OUTPUT.put_line

                         (   'Unable to attach responsibility to user due to'

                          || SQLCODE

                          || ' '

                          || SUBSTR (SQLERRM, 1, 100)

                         );

END;

Once this is complete, Here is the query to verify whether the responsibility is attached to the user.

----------------------------------------------------------------

SELECT frt.responsibility_name

  FROM fnd_user_resp_groups furg, fnd_user fu, fnd_responsibility_tl frt

 WHERE fu.user_name = '&USER_NAME'

   AND fu.user_id = furg.user_id

   AND furg.responsibility_id = frt.responsibility_id;

For any suggestion or issues with the above code, please leave your comments. I will try to reply back as soon as possible.