Thursday, April 12, 2018

Oracle EBS R12 AME Important Tables


select * from AME_ACTIONS

select * from AME_ACTIONS_TL

select * from AME_ACTION_TYPES

select * from AME_ACTION_TYPES_TL

select * from AME_ACTION_TYPE_CONFIG

select * from AME_ACTION_TYPE_USAGES

select * from AME_ACTION_USAGES

select * from AME_APPROVALS_HISTORY

select * from AME_APPROVAL_GROUPS

select * from AME_APPROVAL_GROUPS_TL

select * from AME_APPROVAL_GROUP_CONFIG

select * from AME_APPROVAL_GROUP_ITEMS

select * from AME_APPROVAL_GROUP_MEMBERS

select * from AME_APPROVER_TYPES

select * from AME_APPROVER_TYPE_USAGES

select * from AME_ATTRIBUTES

select * from AME_ATTRIBUTES_TL

select * from AME_ATTRIBUTE_USAGES

select * from AME_CALLING_APPS

select * from AME_CALLING_APPS_TL

select * from AME_CONDITIONS

select * from AME_CONDITION_USAGES

select * from AME_CONFIG_VARS

select * from AME_CONFIG_VARS_TL

select * from AME_EXCEPTIONS_LOG

select * from AME_FIELD_HELP

select * from AME_HELP

select * from AME_ITEM_CLASSES

select * from AME_ITEM_CLASSES_TL

select * from AME_ITEM_CLASS_USAGES

select * from AME_MANDATORY_ATTRIBUTES

select * from AME_REC_ACCESS_TRANS

select * from AME_RULES

select * from AME_RULES_TL

select * from AME_RULE_USAGES

select * from AME_STRING_VALUES

select * from AME_TEMP_DELETIONS

select * from AME_TEMP_HANDLER_STATES

select * from AME_TEMP_INSERTIONS

select * from AME_TEMP_OLD_APPROVER_LISTS

select * from AME_TEMP_TRANSACTIONS

select * from AME_TEMP_TRANS_ATT_VALUES

select * from AME_TEMP_TRANS_LOCKS

select * from AME_TEST_TRANSACTIONS

select * from AME_TEST_TRANS_ATT_VALUES

select * from AME_TRANS_APPROVAL_HISTORY

select * from AME_TXN_APPROVERS

Friday, June 10, 2016

How to open Payables or AP Period in Oracle EBS

Navigate to Payables Manager and Click on “Control Payables Periods” sub menu under “Accounting”

Select the period you want to open and select the value as “Open” from the  List of Values

Click on Open and Save the form. You are all set.

Friday, July 31, 2015

How to remove the Concurrent Program from a Request Group


SET SERVEROUTPUT ON spool /tmp/XXCDS_HRRPTXXX_REMOVE_RG.TXT WHENEVER SQLERROR EXIT FAILURE ROLLBACK BEGIN apps.fnd_program.remove_from_group(program_short_name => 'XXCDS_HREXT002_EMP_RECT_CP', -- Program Short Name program_application => 'XXCDS', -- Application Short Name request_group => 'XXCDS Audit Request Group', -- Request Group Name group_application => 'XXCDS'); -- Application Short Name dbms_output.put_line('Concurrent Program has been removed from request group Successfully '); COMMIT; EXCEPTION WHEN NO_DATA_FOUND THEN dbms_output.put_line ('Concurrent Program has already been removed from the Request group'); WHEN OTHERS THEN dbms_output.put_line ('Others Exception Removing Conc Program. ERROR:' || SQLERRM); END; / SHOW ERROR SPOOL OFF

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

DECLARE
     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;
BEGIN
   SELECT fa.application_short_name,
                 fr.responsibility_key,
                 frg.security_group_key,
                 frt.description
       INTO v_application_name,
                 v_responsibility_key,
                 v_security_group,
                 v_description
     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);
  COMMIT;
  dbms_output.put_line ( 'Responsiblity ' || v_responsibility_name || ' is removed from the user '                                            || v_user_name || ' Successfully' );
EXCEPTION
    WHEN OTHERS THEN
         dbms_output.put_line ( 'Error encountered while deleting responsibilty from the user and                                                    the error is ' || SQLERRM );
END;

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
1000
Public

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

UPGRADE
Honors both Customization and Protection Levels of Data.
UPLOAD
Honors only Protection. Customization Levels are not respected.
FORCE
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




Oracle Data Security and its components


Data Security: Coordinating with Function Security, Data Security provides additional security on the data. It lets the administrators decide what are all the actions that users can perform on the data.
Data Security Policies can reflect access to 
a. All Instances : All instances of an object represents that all rows in the database related to that object. If you consider Purchase Order (PO) as an object and All Instances of PO means all the Purchase Orders in the database. 
b. Instance Set: Instance set of an object represents a set of the related instances of an object. In our Purchase Order as an object example, a set of Blanket Purchase Orders represent a set of Instances of the PO. 
c. A Specific Instance: This generally represents a single row in the database. It is usually identified by a Primary Key value for the object(Ex: PO Header Id)

Wednesday, February 5, 2014

How to see Account Generator Workflows in Status Monitor of Workflow Administrator

By default, Oracle Account Generator Workflows does not show up in the Workflow Status Monitor screen of Workflow Administrator. To be able to see them in the status monitor, couple of profile options should be enabled to “Yes”.
           1. PO: Set Debug Workflow ON
           2. Account Generator:Run in Debug Mode