, , , , ,

Reset Oracle Application Password

This is an anonymous PL/SQL block to reset your application password through back end.

This Code is tested in R12.1.3 Instance.

DECLARE
v_flag BOOLEAN;
BEGIN
v_flag := fnd_user_pkg.ChangePassword(‘ORACLEERP’,’learn123#’);
COMMIT;
END;

/by
, , , ,

API to Purge an person from Oracle HRMS

DECLARE
l_person_org_manager_warning VARCHAR2 (200);
BEGIN
hr_person_api.delete_person(p_validate => FALSE,
p_effective_date => SYSDATE,
p_person_id => :person_id,
p_perform_predel_validation => FALSE,
p_person_org_manager_warning => l_person_org_manager_warning
);
COMMIT;
END;

Before purging the person from Oracle HRMS we need to make sure that the employee and fnd_user link is been deleted and also the person should not have an active payroll.
If the employee has an active payroll then we cannot purge the record. The alternative way is to either end date the employee using the termination screen or you need to change the person from ‘Employee’ to ‘Applicant’ and then use the above API again to purge the record.

/by
, , , ,

Query that shows all the repsonsibilities and what functions are attached to these responsibilities

SELECT DISTINCT faa.application_name application, rtl.responsibility_name,
ffl.user_function_name, ff.function_name, ffl.description,
ff.TYPE
FROM fnd_compiled_menu_functions cmf,
fnd_form_functions ff,
fnd_form_functions_tl ffl,
fnd_responsibility r,
fnd_responsibility_vl rtl,
apps.fnd_application_all_view faa
WHERE cmf.function_id = ff.function_id
AND r.menu_id = cmf.menu_id
AND rtl.responsibility_id = r.responsibility_id
AND cmf.grant_flag = ‘Y’
AND ff.function_id = ffl.function_id
AND faa.application_id(+) = r.application_id
AND r.end_date IS NULL
AND rtl.end_date IS NULL
ORDER BY rtl.responsibility_name;
/by
, , , ,

Query to extract Employee Contact Information

SELECT papf.person_id employee_id, papf.full_name employee_name,
papf.effective_start_date employee_start_date,
papf.effective_end_date employee_end_date,
papf_cont.full_name contact_name, hl.meaning contact_type,
pcr.date_start contact_start_date, pcr.date_end contact_end_date
FROM per_contact_relationships pcr,
per_all_people_f papf,
hr_lookups hl,
per_all_people_f papf_cont
WHERE 1 = 1
AND papf.person_id = pcr.person_id
AND pcr.contact_person_id = papf_cont.person_id
AND NVL (TRUNC (papf.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND NVL (TRUNC (papf_cont.effective_end_date), SYSDATE) >= TRUNC (SYSDATE)
AND hl.lookup_type(+) = ‘CONTACT’
AND hl.lookup_code(+) = pcr.contact_type
/by
, , , ,

Workflow Tables Information in R12

WF_ITEM_TYPES – It defines an item that is transitioning through a workflow process. NAME (PK), PROTECT_LEVEL, CUSTOM_LEVEL, PERSISTENCE_TYPE

WF_ITEM_ATTRIBUTES – stores definitions of attributes associated with a process. Each row includes the sequence in which the attribute is used as well as the format of the attribute data. ITEM_TYPE (PK), NAME (PK), SEQUENCE, TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

WF_ACTIVITIES -table stores the definition of an activity. Activities can be processes, notifications, functions or folders.ITEM_TYPE (PK), NAME (PK), VERSION(PK), TYPE, RERUN, EXPAND_ROLE, PROTECT_LEVEL, CUSTOM_LEVEL, BEGIN_DATE, RROR_ITEM_TYPE, RUNNABLE_FLAG

WF_ACTIVITY_ATTRIBUTES – table defines attributes which behave as parameters for an activity. Activity attributes are only used by function activities.Examples of valid attribute types are DATE, DOCUMENT, FORM, ITEMATTR, LOOKUP, and VARCHAR2.
ACTIVITY_ITEM_TYPE (PK), ACTIVITY_NAME (PK), ACTIVITY_VERSION (PK), NAME (PK), SEQUENCE, TYPE, VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

WF_MESSAGES – contains the definitions of messages which may be sent out as notifications. TYPE (PK), NAME (PK), PROTECT_LEVEL, CUSTOM_LEVEL

WF_MESSAGE_ATTRIBUTES contains message attribute definitions.

WF_NOTIFICATIONS holds the runtime information about a specific instance of a sent message. A new row is created in the table each time a message is sent.

WF_NOTIFICATION_ATTRIBUTES holds rows created for attributes of a notification. When each new notification is created, a notification attribute row is created for each message attribute in the message definition. Initially, the values of the notification attributes are set to the default values specified in the message attribute definition.

WF_ITEMS is the runtime table for workflow processes. Each row defines one work item within the system. ITEM_TYPE (PK), ITEM_KEY (PK), ROOT_ACTIVITY, ROOT_ACTIVITY_VERSION, BEGIN_DATE

WF_ITEM_ACTIVITY_STATUSES TABLE is the runtime table for a work item. Each row includes the start and end date, result code, and any error information an activity generates. ITEM_TYPE (PK), ITEM_KEY (PK), PROCESS_ACTIVITY (PK)

WF_ITEM_ACTIVITY_STATUSES_H – table stores the history of the WF_ITEM_ACTIVITY_STATUSES table. ITEM_TYPE, ITEM_KEY, PROCESS_ACTIVITY

WF_PROCESS_ACTIVITIES stores the data for an activity within a specific process. PROCESS_ITEM_TYPE, PROCESS_NAME, PROCESS_VERSION, ACTIVITY_ITEM_TYPE, ACTIVITY_NAME, INSTANCE_ID (PK), INSTANCE_LABEL, PERFORM_ROLE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

WF_ACTIVITY_TRANSITIONS table defines the transitions from one activity to another in a process. Each row includes the activities at the beginning and end of the transition, as well as the result code and physical location of the transition in the process window. FROM_PROCESS_ACTIVITY (PK), RESULT_CODE (PK), TO_PROCESS_ACTIVITY (PK), PROTECT_LEVEL,CUSTOM_LEVEL

WF_ACTIVITY_ATTR_VALUES table contains the data for the activity attributes. Each row includes the process activity id and the associated value for the attribute. PROCESS_ACTIVITY_ID (PK), NAME (PK), VALUE_TYPE, PROTECT_LEVEL, CUSTOM_LEVEL

SELECT * FROM WF_USER_ROLE_ASSIGNMENTS
SELECT * FROM WF_USER_ROLES
SELECT * FROM WF_ROLES
SELECT * FROM WF_ITEMS
SELECT * FROM WF_ITEM_ATTRIBUTES
SELECT * FROM WF_ITEM_ATTRIBUTE_VALUES
SELECT * FROM WF_ITEM_ATTRIBUTES_TL
SELECT * FROM WF_ACTIVITIES
SELECT * FROM WF_ACTIVITIES_TL
SELECT * FROM WF_ACTIVITY_ATTRIBUTES
SELECT * FROM WF_ACTIVITY_ATTRIBUTES_TL
SELECT * FROM WF_ACTIVITY_TRANSITIONS
SELECT * FROM WF_DEFERRED
/by