Script to find Form Personlizations

SELECT FORM_NAME,
  FUNCTION_NAME,
  Description,
  SEQUENCE,
  CONDITION
FROM FND_FORM_CUSTOM_RULES
WHERE ENABLED = 'Y';

SQL to find OAF personalizations

SELECT JDR_MDS_INTERNAL.GETDOCUMENTNAME(PATH_DOCID) DOCUMENT_ID,
  jdr_mds_internal.exportDocumentAsXML(jdr_mds_internal.getDocumentName(path_docid)) personalization_xml
FROM jdr_paths,
  jdr_attributes
WHERE PATH_DOCID = ATT_COMP_DOCID
AND path_name    = SUBSTR(att_value,INSTR(att_value, '/', -1, 1)+1)
AND att_comp_seq = 0
AND ATT_NAME     = 'customizes'
AND ATT_VALUE    = ATT_VALUE
AND jdr_mds_internal.getDocumentName(path_docid) LIKE '%/responsibility/53621%'


This would return all OAF personalization for responsibility id 53621.

Regular Expression for Date Format DD-MON-YYYY HH24:MI:SS

Use below regular expression to validate if given string is in specific date format.

select COUNT(1)  from DUAL where regexp_like('12-JUN-2016 23:34:59','^(\d){2}-[A-Z]{3}-(\d){4}\s(\d){2}:(\d){2}:(\d){2}$')
1

select COUNT(1)  from DUAL where regexp_like('121-JUN-2016 23:34:59','^(\d){2}-[A-Z]{3}-(\d){4}\s(\d){2}:(\d){2}:(\d){2}$')
0


=======================

^          : Starting String
$          : Ending String
{n}      : Execute last rule n time
[A-Z]   : All CAP Alphabets check
\d         :  digit check
\s          : Space

Script to find Template and associated JSP file

SELECT 

ida.access_name,
ida.file_name,
DECODE(ida.site_id, 1, 'All', s.msite_name) site_name,
ida.default_msite,
ida.language_code,
ida.default_language
FROM IBE_DSP_ATTACHMENTS_V ida, ibe_msites_tl s
WHERE m.site_id = s.msite_id and file_name = ‘ibeSeededPage.jsp’; 

Once identify the target JSP page use above SQL to find the associated program access name.

Create a custom version of seeded JSP (i.e. xxskpibeSeededPage.jsp)

Login to iStore Administrator Responsibility >> Advanced >> Template Manager 

Search the program access name with the out of above SQL 

Update >> Add new file >> provide custom file name for all countries and US language. 

Now place the custom file at OA_HTML file path and compile JSP (Click here for compilation and migrations)

SQL - Menu and Functions assigned to Responsibilities

SELECT a.seq,
  a.ENTRY_SEQUENCE,
  (SELECT menu_name FROM fnd_menus WHERE menu_id = a.menu_id
  ) menu,
  (SELECT menu_name FROM fnd_menus WHERE menu_id = a.sub_menu_id
  ) sub_menu,
  (SELECT FUNCTION_NAME
  FROM fnd_form_functions
  WHERE FUNCTION_ID = a.FUNCTION_ID
  ) function_name ,
  prompt,
  description
FROM
  (SELECT c.*
  FROM
    (SELECT level seq,
      ENTRY_SEQUENCE,
      x.menu_id,
      sub_menu_id,
      function_id,
      prompt,
      description
    FROM fnd_menu_entries_vl x
      START WITH x.menu_id =
      (SELECT menu_id
      FROM fnd_menus
      WHERE menu_id=
        (SELECT menu_id
        FROM fnd_responsibility_vl
        WHERE responsibility_name=:Responsibility_Name
        )
      )
      CONNECT BY prior sub_menu_id = x.menu_id
    ) c
  WHERE NOT EXISTS
    (SELECT 1
    FROM FND_RESP_FUNCTIONS
    WHERE responsibility_id =
      (SELECT responsibility_id
      FROM fnd_responsibility_tl
      WHERE responsibility_name=:Responsibility_Name
      )
    AND action_id = c.menu_id
    AND rule_type ='M'
    )
  AND NOT EXISTS
    (SELECT 1
    FROM FND_RESP_FUNCTIONS
    WHERE responsibility_id =
      (SELECT responsibility_id
      FROM fnd_responsibility_tl
      WHERE responsibility_name=:Responsibility_Name
      )
    AND action_id = c.function_id
    AND rule_type ='F'
    )
  ) A
ORDER BY 1,2,3

SQL - Concurrent Programs

Concurrent Program Executable Details- 
SELECT application_name,
  user_concurrent_program_name,
  meaning Program_Type,
  output_file_type output_format,
  --  concurrent_program_id,
  --  cp.application_id,--ce.*,
  executable_name,
  execution_file_name
FROM FND_CONCURRENT_PROGRAMS_VL cp ,
  FND_EXECUTABLES_FORM_V ce ,
  fnd_lookup_values flv
WHERE cp.executable_id       = ce.executable_id
AND ce.execution_method_code = lookup_code
AND LOOKUP_TYPE              = 'CP_EXECUTION_METHOD_CODE'
AND user_concurrent_program_name LIKE '%ProgramNAME%'

List of Concurrent Program  assigned to Responsibilities -

SELECT DISTINCT USER_CONCURRENT_PROGRAM_NAME,

  CONCURRENT_PROGRAM_NAME,
  z.responsibility_name,
  REQUEST_GROUP_NAME,
  application_name,
  REQUEST_GROUP_CODE
FROM FND_REQUEST_GROUP_UNITS x ,
  FND_CONCURRENT_PROGRAMS_VL y ,
  FND_RESPONSIBILITY_VL Z,
  FND_REQUEST_GROUPS FRG,
  FND_APPLICATION_VL fa
WHERE request_unit_id    = y.concurrent_program_id
AND unit_application_id  = y.application_id
AND X.REQUEST_GROUP_ID   = Z.REQUEST_GROUP_ID
AND FRG.APPLICATION_ID   = Z.APPLICATION_ID
AND FRG.REQUEST_GROUP_ID = Z.REQUEST_GROUP_ID
AND fa.application_id    = frg.application_id
AND Y.USER_CONCURRENT_PROGRAM_NAME LIKE '%ProgramName%'
  -- AND responsibility_name LIKE '%'
ORDER BY 1,2;