Query to find DFF and there segment details along with tables in Oracle Apps

  SELECT ffv.descriptive_flexfield_name "DFF Name",
         ffv.TITLE "DFF Title",
         ffv.application_table_name "Table Name",
         ap.application_name "Application",
         ffc.descriptive_flex_context_code "Context Code",
         ffc.descriptive_flex_context_name "Context Name",
         ffc.description "Context Desc",
         ffc.enabled_flag "Context Enable Flag",
         att.column_seq_num "Segment Number",
         att.form_left_prompt "Segment Name",
         att.application_column_name "Column",
         fvs.flex_value_set_name "Value Set",
         att.display_flag "Displayed",
         att.enabled_flag "Enabled",
         att.required_flag "Required"
    FROM apps.fnd_descriptive_flexs_vl ffv,
         apps.fnd_descr_flex_contexts_vl ffc,
         apps.fnd_descr_flex_col_usage_vl att,
         apps.fnd_flex_value_sets fvs,
         apps.fnd_application_vl ap
   WHERE     ffv.descriptive_flexfield_name = att.descriptive_flexfield_name(+)
         AND ap.application_id = ffv.application_id
         AND ffv.descriptive_flexfield_name = ffc.descriptive_flexfield_name
         AND ffv.application_id = ffc.application_id
--         AND ffc.descriptive_flex_context_code = att.descriptive_flex_context_code
         AND fvs.flex_value_set_id(+) = att.flex_value_set_id
--         AND ap.application_name = 'Inventory'
         AND ffv.title = :P_DFF_Title
--         AND att.form_left_prompt LIKE '%Cross%'
--         AND ffc.descriptive_flex_context_code LIKE 'Give Context Code Value'
ORDER BY att.column_seq_num
;

Query to find All the responsibility attached with User and their end_date

SELECT FUSER.USER_NAME USER_NAME,
       FUSER.START_DATE,
       FUSER.END_DATE,
       FUSER.EMAIL_ADDRESS,
       fuser.description,
       per.FULL_NAME FULL_NAME,
       per.EMPLOYEE_NUMBER EMPLOYEE_NUMBER,
       frt.RESPONSIBILITY_NAME RESPONSIBILITY
  FROM FND_USER fuser,
       PER_PEOPLE_F per,
       FND_USER_RESP_GROUPS furg,
       FND_RESPONSIBILITY_TL FRT
 WHERE     FUSER.EMPLOYEE_ID = PER.PERSON_ID
       AND (   PER.EFFECTIVE_END_DATE > SYSDATE
            OR PER.EFFECTIVE_END_DATE IS NULL)
       AND fuser.USER_ID = furg.USER_ID
       AND (TO_CHAR (fuser.END_DATE) IS NULL OR fuser.END_DATE > SYSDATE)
       AND frt.RESPONSIBILITY_ID = furg.RESPONSIBILITY_ID
       AND (TO_CHAR (furg.END_DATE) IS NULL OR furg.END_DATE > SYSDATE)
       AND FRT.LANGUAGE = 'US'
       AND fuser.USER_NAME = :P_User_Name
;

Query to find details of Form Personzalization in Oracle Apps

Find the List of Form Personlization Rules for any specific from 


 SELECT ffv.form_id "Form ID",  ffv.form_name "Form Name",
         ffv.user_form_name "User Form Name",
         ffv.description "Form Description",
         ffcr.sequence "Sequence",
         ffcr.description "Personalization Rule Name"
    FROM fnd_form_vl ffv, fnd_form_custom_rules ffcr
   WHERE ffv.form_name = ffcr.form_name AND ffv.form_name = 'POXPOEPO' /*Name of the form for which you want to find the personalizations */
ORDER BY ffv.form_name, ffcr.sequence;
-----------------------------------------------------------------------------------------------------------


Elaborates From Personalization along with Conditions, Trigger_Event and Action details  


  SELECT DISTINCT A.Id,
                  A.Form_Name,
                  A.Enabled,
                  C.User_Form_Name,
                  D.Application_Name,
                  a.sequence,
                  A.Description,
                  a.trigger_event,
                  a.trigger_object,
                  a.condition,
                  Ca.Action_Type,
                  ca.summary Action_description,
                  Ca.Enabled,
                  Ca.Object_Type,
                  ca.TARGET_OBJECT,
                  ca.property_value,
                  ca.MESSAGE_TYPE,
                  ca.MESSAGE_TEXT
    FROM FND_FORM_CUSTOM_RULES a,
         FND_FORM b,
         FND_FORM_TL c,
         Fnd_Application_Tl D,
         Fnd_Form_Custom_Actions ca
   WHERE     a.form_name = b.form_name
         AND B.Form_Id = C.Form_Id
         AND B.Application_Id = D.Application_Id
         --       AND D.Application_Id = 660                       --For Order Management
         --       AND C.User_Form_Name LIKE 'Sales%' --All the Forms that Start with Sales
         AND A.Enabled = 'Y'
         AND a.id = ca.rule_id
         AND (   CA.TARGET_OBJECT LIKE '%REQUEST_DATE%'
              OR CA.property_value LIKE '%REQUEST_DATE%'
              OR UPPER (A.Description) LIKE '%REQUEST_DATE%'
              OR UPPER (ca.summary) LIKE '%REQUEST_DATE%')
ORDER BY A.description
;
-----------------------------------------------------------------------------------------------------------

List of All Tables use to track Form Personlizations

SELECT * FROM Fnd_Form_Custom_Actions where

SELECT * FROM all_tables where table_name like 'FND%FORM%'

SELECT * FROM FND_FORM_CUSTOM_SCOPES

SELECT * FROM FND_FORM_CUSTOM_PARAMS

SELECT * FROM FND_FORM_CUSTOM_RULES

SELECT * FROM FND_FORM_CUSTOM_PROP_VALUES

SELECT * FROM FND_FORM_CUSTOM_PROP_LIST

SELECT * FROM FND_FORM_CUSTOM_ACTIONS

FND_FORM_CUSTOM_RULES - The Rules for the form customization. A rule must have 1 or more FND_FORM_CUSTOM_SCOPES and a rule may have 1 or more FND_FORM_CUSTOM_ACTIONS.

FND_FORM_CUSTOM_ACTIONS - Holds the Actions for a specified Rule 

FND_FORM - stores information about your registered application forms.

Query to Find Defaulting Rules in Order Management for specific Attribute

Query to Find Defaulting Rules in Order Management for specific Attribute  like in below case finding the defaulting rules for REQUEST_DATE (Customer_Request_date or Shcedule_Request_date) field


SELECT dadr.DATABASE_OBJECT_NAME,
       dadr.ATTRIBUTE_CODE,
       dac.ATTRIBUTE_CODE,
       dac.DATABASE_OBJECT_NAME,
       aoa.column_name,
       aoa.attribute_application_id,
       aoa.REQUIRED_FLAG,
       DADR.*
  FROM oe_def_attr_def_rules dadr,
       oe_def_attr_condns dac,
       ak_object_attributes aoa
 WHERE     dadr.ATTR_DEF_CONDITION_ID = dac.ATTR_DEF_CONDITION_ID
       AND dac.ENABLED_FLAG = 'Y'
       AND dac.ATTRIBUTE_CODE = aoa.ATTRIBUTE_CODE
       AND dac.DATABASE_OBJECT_NAME = aoa.DATABASE_OBJECT_NAME
       AND UPPER (DADR.SRC_API_FN) LIKE '%REQUEST_DATE%'

Query to find the last run date of a Concurrent Program in Oracle Apps

Query to find the last run date of a Concurrent Program

--input:     Concurrent Program name

SELECT MAX (fcr.REQUEST_DATE)

  FROM fnd_concurrent_requests          fcr,

       fnd_concurrent_programs_tl       fcpt

 WHERE     fcr.concurrent_program_id = fcpt.concurrent_program_id

       AND fcr.program_application_id = fcpt.application_id

       AND fcr.actual_start_date > SYSDATE - :days_to_check

       AND fcpt.user_concurrent_program_name LIKE '%' || :Program_Name || '%'

Query to find the responsibilities which can run the concurrent program in Oracle Apps

Query to find the responsibilities which can run the concurrent program

-- input:     Concurrent Program name


SELECT frv.responsibility_name
  FROM apps.fnd_request_group_units frgu,
       apps.fnd_responsibility_vl frv,
       apps.fnd_concurrent_programs_vl fcpv
 WHERE     frv.REQUEST_GROUP_ID = frgu.REQUEST_GROUP_ID
       AND fcpv.concurrent_program_id = frgu.REQUEST_UNIT_ID
       AND NVL (frv.end_date, SYSDATE + 1) > SYSDATE
       AND EXISTS
              (SELECT 'Y'
                 FROM apps.fnd_user_resp_groups grps
                WHERE     grps.responsibility_id = frv.responsibility_id
                      AND NVL (grps.end_date, SYSDATE + 1) > SYSDATE)
       AND fcpv.user_concurrent_program_name like '%'||:Program_Name||'%'
Order By 1

Concurrent Program - Query to find Status of Concurrent Requests in Oracle Apps

Query will list the details for the concurrent program like 
Concurrent Program name, Arguments, log / output file path and the Status 


   SELECT DISTINCT fcp.user_concurrent_program_name,
                fcp.concurrent_program_name,
                fcr.request_id,
                fcr.request_date,
                flv.meaning status,
                fcr.status_code,
                fcr.completion_text,
                fcr.logfile_name,
                fcr.outfile_name,
                fcr.argument_text
  FROM apps.fnd_concurrent_programs_vl fcp,
       apps.fnd_concurrent_requests    fcr,
       apps.fnd_lookup_values          flv
 WHERE fcr.concurrent_program_id = fcp.concurrent_program_id
   AND trunc(fcr.last_update_date) <= trunc(SYSDATE)
   AND flv.lookup_code = fcr.status_code
   AND flv.lookup_type = 'CP_STATUS_CODE'
   AND flv.language = 'US'
   AND fcr.request_id = :P_Request_ID
 ORDER BY fcr.request_date,
          fcr.request_id DESC;

FND Load - Form (entire or Personalization) download and Upload commands

You should take a note that upon uploading, all prior personalizations for that function are first deleted, and then the contents of the loader file are inserted. So be careful..)

 FNDLOAD - A pain killer for migration

you can either Download for a specific form or specfic function of the form. In both the case same affrmcus.lct is used.

Download -specific form< /STRONG>

FNDLOAD <userid>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct <filename.ldt> FND_FORM_CUSTOM_RULES

form_name=<form name>< /FONT>

Download -for all personalizations< /FONT>

FNDLOAD <userid>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct <filename.ldt> FND_FORM_CUSTOM_RULES function_name=<function name>< /FONT>

• Function_name is a required parameter

• You cann't download lct file with out any supplied value.

e.g. 

FNDLOAD apps/password 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct OEXOEORD_PERSONALIZATION.ldt FND_FORM_CUSTOM_RULES form_name =OEXOEORD

OEXOEORD_PERSONALIZATION.ldt :- Any name for downloaded file

FND_FORM_CUSTOM_RULES          :- Specifies to download form personalization

Upload

FNDLOAD <userid>/<password> 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct <filename.ldt>

e.g. 

FNDLOAD apps/appsmv 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct OEXOEORDNEW.ldt

Uasge

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct xxapperiod.lct FND_FORM_CUSTOM_RULES form_name=APXSUMPS

FNDLOAD apps/apps 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct xxapperiod.lct

Because the architecture has built-in support for all languages, you don’t translate ldt files, but instead you enter multiple Actions for different languages.

Query to Find Concurrent Program Details using Executable Name in Oracle Apps

--Find Concurrent Program Details using Executable Name 

SELECT fa.application_short_name,
       frg.request_group_name,
       fe.execution_file_name, 
       fe.executable_name
  FROM fnd_request_group_units frgu,
       fnd_concurrent_programs fcp,
       fnd_request_groups frg,
       fnd_executables fe,
       fnd_application fa
 WHERE frgu.request_unit_id = fcp.concurrent_program_id
       AND frgu.request_group_id = frg.request_group_id 
       AND fe.executable_id = fcp.executable_id 
       AND FRG.application_id = fa.application_id
       AND fe.executable_name = :P_Executable_Name

  ;

Query to find out Concurrent programs attached to request group in Oracle Apps

Find Out Concurrent Program attache to user

SELECT request_group_name,
       DECODE (request_unit_type,
               'A', 'Application',
               'P', 'Program',
               'S', 'Request Set',
               '-NA-'
              ) request_unit_type,
       user_concurrent_program_name program_name
  FROM fnd_request_groups a,
       fnd_request_group_units b,
       fnd_concurrent_programs_vl c
 WHERE a.request_group_id = b.request_group_id
   AND b.request_unit_id = c.concurrent_program_id
   AND (c.CONCURRENT_PROGRAM_NAME = :P_Conc_Prog_Short_Name
   OR request_group_name = 'OM Concurrent Programs')
   AND request_unit_type = 'P'
;

List of Responsibility Key along with application short name attached with your fnd_user profile in Oracle Apps

  SELECT r.responsibility_key, fa.application_short_name
    FROM fnd_user u,
         fnd_user_resp_groups ur,
         fnd_responsibility r,
         fnd_application_tl a,
         fnd_application fa
   WHERE     u.user_id = ur.user_id
         AND u.user_name = :p_user_name
         AND r.version = 'W'
         AND ur.responsibility_id = r.responsibility_id
         AND fa.application_id = a.application_id
         AND r.application_id = a.application_id
ORDER BY 1

FND_PROFILE and FND_GLOBAL values in Oracle Apps

Following are the FND_PROFILE values that can be used in the PL/SQL code:

   fnd_profile.value('PROFILEOPTION');

   fnd_profile.value('MFG_ORGANIZATION_ID');

   fnd_profile.value('ORG_ID');

   fnd_profile.value('LOGIN_ID');

   fnd_profile.value('USER_ID');

   fnd_profile.value('USERNAME');

   fnd_profile.value('CONCURRENT_REQUEST_ID');

   fnd_profile.value('GL_SET_OF_BKS_ID');

   fnd_profile.value('SO_ORGANIZATION_ID');

   fnd_profile.value('APPL_SHRT_NAME');

   fnd_profile.value('RESP_NAME');

   fnd_profile.value('RESP_ID');

Following are the FND_GLOBAL values that can be used in the PL/SQL code:

   FND_GLOBAL.USER_ID;

   FND_GLOBAL.APPS_INTIALIZE;

   FND_GLOBAL.LOGIN_ID;

   FND_GLOBAL.CONC_LOGIN_ID;

   FND_GLOBAL.PROG_APPL_ID;

   FND_GLOBAL.CONC_PROGRAM_ID;

   FND_GLOBAL.CONC_REQUEST_ID;

For example, I almost always use the following global variable assignments in my package specification to use throughout the entire package body:

   g_user_id      PLS_INTEGER  :=  fnd_global.user_id;

   g_login_id     PLS_INTEGER  :=  fnd_global.login_id;

   g_conc_req_id  PLS_INTEGER  :=  fnd_global.conc_request_id;

   g_org_id       PLS_INTEGER  :=  fnd_profile.value('ORG_ID');

   g_sob_id       PLS_INTEGER  :=  fnd_profile.value('GL_SET_OF_BKS_ID');

And initialize the application environment as follows:

   v_resp_appl_id  := fnd_global.resp_appl_id;

   v_resp_id       := fnd_global.resp_id;

   v_user_id       := fnd_global.user_id;

    

   FND_GLOBAL.APPS_INITIALIZE(v_user_id,v_resp_id, v_resp_appl_id);