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