Monday 19 October 2015

Register Custom Tables in Oracle Apps


                                               Say you have a custom table called “ERPS_EMPLOYEE” with columns EMP_ID, EMP_NAME and EMP_TYPE in your database.  You need to create a  TABLE type Value set  that pulls up information from  this table as LOV. If you give in the custom table name in “TABLE NAME” field in the “Validation Table Information” Form, Oracle Apps will not recognize it and you will get the below error saying table does not exist.

reg1
So to make your custom table  visible in front end ( while creating Value Set or in Alerts or Audits etc), you have to register it in Oracle Apps.
Let’s now see how to register a custom table.  You will need API named AD_DD for this.

1.  First you register the table using the below API:
2. Secondly register each of the columns as below:
 
Register Column EMP_ID
Register Column EMP_NAME
Register Column EMP_TYPE
3. Thirdly you register Primary Key if the table has any using the below code snippet:
4. Finally you register Primary Key column if your table has a primary key:
Navigate to Application Developer responsibility > Application > Database > Table


Reg2

Query for the table name that we have registered – “ERPS_EMPLOYEE”. Please note that you cannot register your table using this form in the front end. You will have to use API. This form is only meant for viewing the information.


Reg3

Check for the primary key information by clicking on the Primary Key button


Reg4

Now in your Value set, you will be able to  use the table ERPS_EMPLOYEE without any errors.


Reg5

To delete the registered Tables and its columns, use the below API:
 
AD_DD.DELETE_COLUMN(appl_short_name,
                                                       table_name,
                                                       column_name);
 
 AD_DD.DELETE_TABLE( appl_short_name, table_name);

Query: Menus and Function Name for a Particular Responsibility in oracle apps

/* Formatted on 10/19/2015 11:03:28 AM (QP5 v5.240.12305.39446) */
  SELECT lvl r_lvl,
         rownumber rw_num,
         entry_sequence seq,
         (lvl || '.' || rownumber || '.' || entry_sequence) menu_seq,
         menu_name,
         sub_menu_name,
         prompt,
         fm.description,
         TYPE,
         function_name,
         user_function_name,
         fff.description form_description
    FROM (    SELECT LEVEL lvl,
                     ROW_NUMBER ()
                     OVER (PARTITION BY LEVEL, menu_id, entry_sequence
                           ORDER BY entry_sequence)
                        AS rownumber,
                     entry_sequence,
                     (SELECT user_menu_name
                        FROM fnd_menus_vl fmvl
                       WHERE 1 = 1 AND fmvl.menu_id = fmv.menu_id)
                        menu_name,
                     (SELECT user_menu_name
                        FROM fnd_menus_vl fmvl
                       WHERE 1 = 1 AND fmvl.menu_id = fmv.sub_menu_id)
                        sub_menu_name,
                     function_id,
                     prompt,
                     description
                FROM apps.fnd_menu_entries_vl fmv
          START WITH menu_id =
                        (SELECT menu_id
                           FROM apps.fnd_responsibility_vl
                          WHERE UPPER (responsibility_name) =
                                   UPPER (:resp_name))
          CONNECT BY PRIOR sub_menu_id = menu_id) fm,
         apps.fnd_form_functions_vl fff
   WHERE fff.function_id(+) = fm.function_id
ORDER BY lvl, entry_sequence

Thursday 15 October 2015

Oracle apps MOAC setup in R12

The Security Profiles form allows you to group together Operating Units
Define the security profile for the order management responsibility:
Navigate: HRMS Management responsibility:
  1. HRMS Manager > Security > Profile
  2. Verify that the security profile is defined for the OM responsibility.
  3. If the security profile is not yet setup, enter it and attach the operating units
  4. SAVE



The Security List Maintenance concurrent program must be run each time you add or change Security Profiles.
There are three Profile Options you need to be aware of related to Multi-Org that should be set at the Responsibility Level.
         The R12 profile option ‘MO: Security Profile’ is always evaluated first.
         The pre-R12 profile option ‘MO: Operating Unit’ still works in R12.  It is just a secondary priority being evaluated after ‘MO: Security Profile’.
         The R12 profile option ‘MO: Default Operating Unit’ sets the default Operating Unit for transactions when running under a Security Profile.
Many R12 applications modules do not work with ‘MO: Security Profile’ set for a given responsibility. 
         They must only use ‘MO: Operating Unit’.
         Some even require all three Profile Options set.
Examples:
         CRM Modules
         Certain GL Drill Down Functions
(Trial and error determination of setups, no clear direction)


Now check the operating unit field in the order’s screen
Vision Operations defaults as the operating unit in the form per profile option setting for ‘OM: Default Operating Unit’ at responsibility level

SQL Queries for checking Profile Option Values

The following queries are useful to get the profile option values of a profile option at site, application, responsibility and user level
1) Obtain Profile Option values for Profile Option name like ‘%Ledger%’ and  Responsibility name like ‘%General%Ledger%’
 

/* Formatted on 10/15/2015 12:35:37 PM (QP5 v5.240.12305.39446) */
  SELECT SUBSTR (pro1.user_profile_option_name, 1, 35) Profile,
         DECODE (pov.level_id,
                 10001, 'Site',
                 10002, 'Application',
                 10003, 'Resp',
                 10004, 'User')
            Option_Level,
         DECODE (pov.level_id,
                 10001, 'Site',
                 10002, appl.application_short_name,
                 10003, resp.responsibility_name,
                 10004, u.user_name)
            Level_Value,
         NVL (pov.profile_option_value, 'Is Null') Profile_option_Value
    FROM fnd_profile_option_values pov,
         fnd_responsibility_tl resp,
         fnd_application appl,
         fnd_user u,
         fnd_profile_options pro,
         fnd_profile_options_tl pro1
   WHERE     pro1.user_profile_option_name LIKE ('%Ledger%')
         AND pro.profile_option_name = pro1.profile_option_name
         AND pro.profile_option_id = pov.profile_option_id
         AND resp.responsibility_name LIKE '%General%Ledger%' /* comment this line  if you need to check profiles for all responsibilities */
         AND pov.level_value = resp.responsibility_id(+)
         AND pov.level_value = appl.application_id(+)
         AND pov.level_value = u.user_id(+)
ORDER BY 1, 2;
 
2) Obtain all Profile Option values setup for a particular responsibility. Replace the responsibility name as per your requirement.
 
/* Formatted on 10/15/2015 12:36:49 PM (QP5 v5.240.12305.39446) */
  SELECT SUBSTR (pro1.user_profile_option_name, 1, 35) Profile,
         DECODE (pov.level_id,
                 10001, 'Site',
                 10002, 'Application',
                 10003, 'Resp',
                 10004, 'User')
            Option_Level,
         DECODE (pov.level_id,
                 10001, 'Site',
                 10002, appl.application_short_name,
                 10003, resp.responsibility_name,
                 10004, u.user_name)
            Level_Value,
         NVL (pov.profile_option_value, 'Is Null') Profile_option_Value
    FROM fnd_profile_option_values pov,
         fnd_responsibility_tl resp,
         fnd_application appl,
         fnd_user u,
         fnd_profile_options pro,
         fnd_profile_options_tl pro1
   WHERE     pro.profile_option_name = pro1.profile_option_name
         AND pro.profile_option_id = pov.profile_option_id
         AND resp.responsibility_name LIKE '%General%Ledger%'
         AND pov.level_value = resp.responsibility_id(+)
         AND pov.level_value = appl.application_id(+)
         AND pov.level_value = u.user_id(+)
ORDER BY 1, 2;
 
 
Similarly, you can tweak the above queries to obtain Profile Option Values set for a particular User or a particular application.