UTL File All Exceptions

  UTL File All Exceptions
  
  WHEN UTL_FILE.INVALID_PATH THEN
    RAISE_APPLICATION_ERROR(-20100,'Invalid Path');

  WHEN UTL_FILE.INVALID_MODE THEN
    RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');

  WHEN UTL_FILE.INVALID_FILEHANDLE THEN
    RAISE_APPLICATION_ERROR(-20102,'Invalid Filehandle');

  WHEN UTL_FILE.INVALID_OPERATION THEN
    RAISE_APPLICATION_ERROR(-20103,'Invalid Operation -- May signal a file locked by the OS');

  WHEN UTL_FILE.READ_ERROR THEN
    RAISE_APPLICATION_ERROR(-20104,'Read Error');

  WHEN UTL_FILE.WRITE_ERROR THEN
    RAISE_APPLICATION_ERROR(-20105,'Write Error');

  WHEN UTL_FILE.INTERNAL_ERROR THEN
    RAISE_APPLICATION_ERROR(-20106,'Internal Error');

  WHEN UTL_FILE.CHARSETMISMATCH THEN
RAISE_APPLICATION_ERROR(-20107,'Characterset Mismatch error');

  WHEN UTL_FILE.FILE_OPEN THEN
RAISE_APPLICATION_ERROR(-20108,'Cannot perform the operation.File is open');

  WHEN UTL_FILE.INVALID_MAXLINESIZE THEN
RAISE_APPLICATION_ERROR(-20109,'Invalid Max line size');

  WHEN UTL_FILE.INVALID_FILENAME THEN
RAISE_APPLICATION_ERROR(-20110,'Invalid Filename');

  WHEN UTL_FILE.ACCESS_DENIED THEN
RAISE_APPLICATION_ERROR(-20111,'Access Denied');

  WHEN UTL_FILE.INVALID_OFFSET THEN
RAISE_APPLICATION_ERROR(-20112,'Invalid Offset');

  WHEN UTL_FILE.DELETE_FAILED THEN
RAISE_APPLICATION_ERROR(-20113,'File Delete failed');

  WHEN UTL_FILE.RENAME_FAILED THEN
RAISE_APPLICATION_ERROR(-20114,'File Rename failed');

  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20115,'No Data Found');

  WHEN VALUE_ERROR THEN
    RAISE_APPLICATION_ERROR(-20116,'Value Error');

  WHEN OTHERS THEN
    RAISE_APPLICATION_ERROR(-20117,'Unknown UTL_FILE Error');

Filter varchar2 columns for date values only IMP for DFFs

Filter varchar2 columns for date values only IMP for DFFs

Its helpful when date values only have to filter from DFF attributes.
As the attribute can have any data type value.
So if we use below where condition the future errors can be handled.

where
regexp_like (a.segment3, '[0-9]{4}/(0[0-9]|1[0-2])/([0-2][0-9]|3[0-1])')

Get Workflow User, Item Name, Function Message and Lookups

==Workflow Users/Roles
SELECT * FROM WF_USERS where name = 'XXUSER';
SELECT * FROM WF_ROLES where name = 'XXUSER';
SELECT * FROM WF_USER_ROLES where user_name = 'XXUSER';
SELECT * FROM WF_USER_ROLE_ASSIGNMENTS where user_name = 'XXUSER';

==Workflow Item Name and Attributes
SELECT * FROM WF_ITEM_TYPES where name = 'OKCAUKAP'; --PO Approval (POAPPRV) ; PO Requisition Approval (REQAPPRV); 
--OM Order Header (OEOH); OM Order Line (OEOL)
SELECT * FROM WF_ITEM_TYPES_VL where name = 'OKCAUKAP';
SELECT * FROM WF_ITEM_TYPES_VL where display_name like '%Approval';
SELECT * FROM WF_ITEM_TYPES_VL where description like '%Order%';
SELECT * FROM WF_ITEM_ATTRIBUTES where item_type = 'OKCAUKAP';
SELECT * FROM WF_ITEM_ATTRIBUTES_TL;

==Workflow Functions
SELECT * FROM WF_ACTIVITIES where item_type = 'OKCAUKAP' and version = 1;
SELECT * FROM WF_ACTIVITIES_TL where item_type = 'OKCAUKAP';
SELECT * FROM WF_ACTIVITY_ATTRIBUTES where activity_name = 'CREATE_SOA';
SELECT * FROM WF_ACTIVITY_ATTRIBUTES_TL;
SELECT * FROM WF_ACTIVITY_TRANSITIONS;

==Workflow Lookups
SELECT * FROM WF_LOOKUPS_TL where lookup_type = 'OKC_DB_RETRY';

==Workflow Messages
SELECT * FROM WF_MESSAGES where type = 'OKCAUKAP' and name = 'APPROVE_CONTRACT';
SELECT * FROM WF_MESSAGES_TL where type = 'OKCAUKAP' and name = 'APPROVE_CONTRACT';
SELECT * FROM WF_MESSAGE_ATTRIBUTES where message_type = 'OKCAUKAP' and message_name = 'APPROVE_CONTRACT';
SELECT * FROM WF_MESSAGE_ATTRIBUTES_TL where message_type = 'OKCAUKAP' and message_name = 'APPROVE_CONTRACT';


==Workflow Particular item run and values
SELECT * FROM WF_ITEMS where item_type = 'OKCAUKAP' order by begin_date desc;
--USER_KEY/ITEM_KEY => select * from okc_k_headers_b where contract_number = '3308531';
SELECT * FROM WF_ITEM_ATTRIBUTE_VALUES where item_type = 'OKCAUKAP' and item_key='20683312';
SELECT * FROM WF_PROCESS_ACTIVITIES where process_item_type = 'OKCAUKAP' and process_version = 1;

SELECT * FROM WF_NOTIFICATIONS WHERE MESSAGE_TYPE = 'OKCAUKAP' AND MESSAGE_NAME = 'APPROVE_CONTRACT';
SELECT * FROM WF_NOTIFICATION_ATTRIBUTES where notification_id = ;

SELECT * FROM WF_DEFERRED;

APPS and Application User Password Find by PON_FORMS_UTIL_PVT.decrypt

SELECT PON_FORMS_UTIL_PVT.decrypt((SELECT fnd_web_sec.get_guest_username_pwd
FROM dual)
,(SELECT encrypted_foundation_password
FROM fnd_user
WHERE USER_NAME='GUEST'))APPS_DB_PSWD
FROM dual;
=============================

SELECT PON_FORMS_UTIL_PVT.decrypt((SELECT PON_FORMS_UTIL_PVT.decrypt((SELECT fnd_web_sec.get_guest_username_pwd
FROM dual)
,(SELECT encrypted_foundation_password
FROM fnd_user
WHERE USER_NAME='GUEST'))APPS_DB_PSWD
FROM dual)
,(SELECT encrypted_user_password
FROM fnd_user
WHERE USER_NAME=:P_APPLICATION_USER_NAME--'SYSADMIN'
)
) APPLICATION_USER_PSWD
FROM dual;

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

JDR Utility in OAF

--JDR Utility in OAF

--1.      LISTDOCUMENTS: For extracting full path of documents.Can provide full or half path as first parameter

DECLARE
BEGIN
jdr_utils.listdocuments(‘/oracle/apps/per’, TRUE);
END;

--2.      LISTCUSTOMIZATIONS: List of all the Personalizations or Extensions for a specific object.

BEGIN
jdr_utils.listcustomizations( ‘/oracle/apps/icx/por/wf/webui/ReqLinesNotificationsRN’);
END;

--3.      PRINTDOCUMENT: To see content of personalization

DECLARE
BEGIN
jdr_utils.printdocument(/oracle/apps/per/irc/candidateSelfService/server/customizations/site/0/VisitorAM’);
END;

--4.     DELETEDOCUMENT: To delete a document or personalization

DECLARE
BEGIN
jdr_utils.deletedocument(‘/oracle/apps/per/irc/candidateSelfService/server/customizations/site/0/VisitorAM’);
END;

Global role in Global HRMS Manager responsibility

For one Role Name we can assign multiple persons and use to notification as expand role of notification

And the attribute of Node of notification is of type “Role”

Used in workflow.

Destination URI on OAF Button to call Other page with parameters

Formatè

OA.jsp?OAFunc=XXQC_EMP_PROBATION_CREATE&ProbationId={@ProbationPerformanceId}&EmployeeId={@PersonId}&FromPage=Launch&retainAM=Y

XXQC_EMP_PROBATION_CREATEèApplication Developer Function name

&ProbationId, &EmployeeId èParameter Variable name

@ProbationPerformanceId, @PersonId èVO Attribute