Oracle Expense Report (Approval Limits)

Expense reports will fail is the manager does not have approval authority for a particular cost center, or an appropriate approval level for the cost center of the employee submitting the expense report. The query to show the employee approval limits are as follows:

SELECT b.full_name, a.cost_center, a.org_id, c.NAME org_name, a.signing_limit
FROM ap_web_signing_limits_all a,
per_all_people_f b,
hr_organization_units c
WHERE a.employee_id = b.person_id
AND a.org_id = c.organization_id
AND b.effective_start_date = (SELECT MAX (effective_start_date)
FROM apps.per_all_people_f
WHERE person_id = b.person_id)
AND a.document_type = 'APEXP'

The navigation to check/update the approval limits for an expense report of an employee is as follows:

Payables Manager -> Employees -> Signing Limits

GL Setups

Defining Types of Calendar
Nav: Setup -> Financials -> Calendar -> Type

Accounting the Calendar
Nav: Setup -> Financials -> Calendar -> Accounting

Defining Currency
Nav: Setup -> Financials -> Currency -> Define

Define rate
Nav: Setup -> Financials -> Currency -> Rate -> Daily

Create a value set
Nav: Setup -> Financials -> Flexfields -> Validation -> Sets

Create a structure:
Nav: Setup -> Financials -> Flexfields -> key -> Segment

Define values:
Nav: Setup -> Financials -> Flexfeild -> Key -> Values

Creating a set of Books:
Nav: Setup -> Financials -> Books -> Define

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

Script to delete the concurrent program from Oracle

Begin
fnd_program.delete_program('program short name','schema');
fnd_program.delete_executable('program short name','schema');
commit;
End;

Person Types in Oracle HRMS

There are two tables (per_all_people_f,per_person_type_usages_f) where you can find the person_types for an employee. Developers usually gets misguided to which person_type_id we need to consider.

The answer is we should always need to use the table per_person_type_usages_f to retrieve the correct person_id. Though the person_type_id in the two tables appear to be same but they are different and that can perty much mislead us. Here are the reasons below:

1. The per_all_people_f.person_type_id holds the default user_person_type for a given system_person_type. It is not maintained to reflect the true user person type. For each system_person_type in each business group, there can only be one USER_PERSON_TYPE with a default_flag = 'Y'. However, an employee can have a user_person_type = Contractor, What will show in person_type_id of the two tables with a CONTRACTOR user_person_type will be as follows:

PER_ALL_PEOPLE_F : 2
PER_PERSON_TYPE_USAGES_F: 4

where 'Contractor' is the subgroup of the person type 'Employee' and 2 is the person_type_id for the employee and 4 is the person_type_id of contractor..

So, only the person_type_usages_f table will give us a true picture of user_person_type and should be used in ALL HR transactions. The per_all_people_f.person_type_id is for use by non HR applications, such as Purchasing which doesn't care about the various user flavors but the system_person_type of 'EMP'. When HR Development made the change to use per_person_type_usages_f table, they retain the person_type_id column in per_all_people_f table for compatibility with other applications. This scheme will enable other non-HR applications still work.

NOTE: When accessing per_person_type_usages_f table for a given person at a given time, there can be multiple rows returned because per_person_type_usages_f table will have a row for each person_type. When an employee is an 'EMP_APL', there'll be two rows returned: one for a system_person_type = 'EMP'with user_person_type = whatever, and another row for system_person_type = 'APL' and user_person_type = whatever. Any SQL statement or cursor needs to handle the multiple rows returned condition. Otherwise, one will get an error something like "Exact fetch returns more than 1 row".

Workflow Error

Error Name: WFNTF_ROLE
Error Message 3205: 'FND_RESP...' is not a valid role or user name.

Resolution: The reason for the error is due to the users not been assigned to the responsibility. The workflow notification was looking for all the users that are been assigned to the responsibility, but as there were no users been assigned the workflow was going in error status.

The query to find the roles that are been assigned to the users or not is as follows:
SELECT *
FROM wf_user_roles
WHERE UPPER (role_name) LIKE 'FND_RESP...'

SELECT c.user_name, c.start_date, c.end_date, b.responsibility_name
FROM fnd_user_resp_groups_all a,
fnd_responsibility_tl b,
fnd_user c,
fnd_application d
WHERE a.user_id = c.user_id
AND a.responsibility_id = b.responsibility_id
AND b.application_id = d.application_id
AND b.responsibility_id = :responsibility_id

For more information on 'Workflow Directory Service Views' please visit:
Oracle Workflow Administrator's -> Workflow Directory Service Views

Query to retrieve active Alert information

SELECT aa.alert_name, aa.start_date_active, al.meaning "FREQUENCY_TYPE",
DECODE (frequency_type,
'B', days_between_checks,
'W', weekly_check_day,
'M', monthly_check_day_num,
'C', days_between_checks,
NULL
) "Days",
aav.to_recipients, aav.cc_recipients, aav.bcc_recipients, aav.subject,
aav.msg_body
FROM alr_actions_v aav, alr_alerts aa, alr_lookups al
WHERE 1 = 1
-- AND UPPER (aa.alert_name) LIKE UPPER ('%HR%')
AND aav.alert_id = aa.alert_id
AND aa.enabled_flag = 'Y'
AND aa.frequency_type = al.lookup_code
AND al.lookup_type = 'ALERT_FREQUENCY_TYPE'