Friday, July 31, 2015

How to add the Request Set to a Request Group

SET SERVEROUTPUT ON spool /tmp/XXCDS_HRXXXXX_ADD_RG.TXT WHENEVER SQLERROR EXIT FAILURE ROLLBACK BEGIN fnd_set.add_set_to_group (request_set => 'FNDRSSUB1817', -- Request Set Code set_application => 'XXCDS', -- Application Code Short Name request_group => 'XXCDS Audit Request Group', -- Request Group Name group_application => 'XXCDS'); -- Application Code Short Name dbms_output.put_line('Request Set has been attached to Request Group Successfully '); COMMIT; EXCEPTION WHEN DUP_VAL_ON_INDEX THEN dbms_output.put_line ('Request Set is already available in the Request group'); WHEN OTHERS THEN dbms_output.put_line ('Others Exception adding Request Set. ERROR:' || SQLERRM); END; / SHOW ERROR SPOOL OFF

Wednesday, June 18, 2014

How to Remove/End Date a Responsibility assigned to the User in Oracle Apps

     v_user_name                   VARCHAR2 (100) := 'TEST_USER';
     v_responsibility_name   VARCHAR2 (100) := 'System Administrator';
     v_application_name        VARCHAR2 (100) := NULL;
     v_responsibility_key       VARCHAR2 (100) := NULL;
     v_security_group            VARCHAR2 (100) := NULL;
   SELECT fa.application_short_name,
       INTO v_application_name,
     FROM fnd_responsibility fr,
                 fnd_application fa,
                 fnd_security_groups frg,
                 fnd_responsibility_tl frt
   WHERE fr.application_id     = fa.application_id
        AND fr.data_group_id        = frg.security_group_id
        AND fr.responsibility_id    = frt.responsibility_id
        AND frt.LANGUAGE            = USERENV ('LANG')
        AND frt.responsibility_name = v_responsibility_name;
  fnd_user_pkg.delresp (username => v_user_name,
                                            resp_app => v_application_name,
                                            resp_key => v_responsibility_key,
                                            security_group => v_security_group);
  dbms_output.put_line ( 'Responsiblity ' || v_responsibility_name || ' is removed from the user '                                            || v_user_name || ' Successfully' );
         dbms_output.put_line ( 'Error encountered while deleting responsibilty from the user and                                                    the error is ' || SQLERRM );

Source: Thanks for Sharing

Monday, February 17, 2014

Error "Each row in the Query Result Columns must be mapped to a unique Query Attribute in Mapped Entity columns" while extending a VO

I was trying to extend the seeded iProcurement VO “ReqsApprovalsVO” with out adding any custom attributes.

When I clicked “Next” button on the Step 4/7 (Attribute Mappings), I hit this roadblock which keeps saying “Each row in the Query Result Columns must be mapped to a unique Query Attribute in Mapped Entity columns” and does not let me extend the VO after this.

I thought of several reasons which could be causing this error. I thought it had something do with the file version of the seeded VO I am trying to extend or it could be due to the jDeveloper version I am using.
I ruled out all the possibilities one by one including the jDeveloper issues. Then I looked in metalink and one of the notes gave me a little clue which would solve my issue.

Source: Metalink Note: 1524622.1

I went back to the seeded VO to see if any of the attributes are having this issue. Right Click on “ReqsApprovalsVO”
à Edit ReqsApprovalsVO
I found this attribute OrgId which is “Mapped to Column or SQL” and has the Query column as “OrgId”

When I verified the SQL Statement for this VO, OrgId attribute has been pulled as “Org_Id” in the query which is not same as “OrgId” and this explains me why the error has been occurring when I am trying to extend the VO.

To resolve the issue, I changed the OrgId to ORG_ID in the seeded VO and was able to extend the VO there after.

Tuesday, February 11, 2014

Upload or Download Oracle Workflows using WFLOAD Command Line Utility

Oracle Workflows are Database Applications whose definitions are stored in different Database Tables.
We can easily Upload/Download these definitions of the Workflows to the flat files using WFLOAD command line utility.
Workflow files are stored under <Application Top>/patch/115/import/US/ (Ex: PO_TOP/patch/115/import/US/powfpoag.wft)

To Upload
WFLOAD apps/<apps_password> <Access_Level> Y <upload_mode> <file_name>.wft
Example: WFLOAD apps/appspwd 0 Y FORCE powfpoag.wft

To Download
WFLOAD apps/<apps_password> <Access_Level> Y DOWNLOAD <file_name>.wft
Example: WFLOAD apps/appspwd 0 Y DOWNLOAD powfpoag.wft

Different Access Levels Used:
0 -  9
Reserved for Oracle Workflow
10 - 19
Reserved for Oracle Application Library
20 - 99
Reserved for Oracle E-Business Suite
100 - 999
Reserved for Customer Organizations

Different UPLOAD modes used while uploading the WFT file to the database.

Honors both Customization and Protection Levels of Data.
Honors only Protection. Customization Levels are not respected.
Both Customization and Protection Levels are not honored.

Usually all the Oracle Patches run the Workflows in UPGRADE mode and access level of 20 and hence our customizations are protected if properly preserved.

Thursday, February 6, 2014

Basics about Profile Options

Profile Options are like Global Variables for Oracle Applications. They provide flexibility to Applications and how they work.

There are 2 types of Profile Options

1    1. System Profile Options
      2. User Profile Options

When logged in as System Administrator, the difference between System Profile and Personal Profile are to whom you are making the changes for.
To Navigate to System Profiles
System Administrator
à Profile à System

From the System Profile screen, you are making changes to the profile options at any level including personal/user

From the User Profile Screen, you are making changes to the profile option values for yourself /user(who logged in). To navigate to Personal Profile:
System Administrator --> Profile --> Personal

User Profile Option is also available for all the users through Edit à Preferences à Profiles