Oracle Apps

Query to find concurrent requests’ status for a given request id.

select fcr.request_id
,decode(fcr.phase_code,’P',decode(fcr.hold_flag,’Y',’Inactive’,fl_p.meaning),fl_p.meaning) phase
,decode(fcr.phase_code,’P',decode(fcr.hold_flag,’Y',’On Hold’,decode(sign(fcr.requested_start_date - sysdate),1,’Scheduled’,fl_s.meaning)),fl_s.meaning) status
from fnd_concurrent_requests fcr
,fnd_lookups fl_p
,fnd_lookups fl_s
where 1=1
and fcr.phase_code = fl_p.lookup_code
and fl_p.lookup_type = ‘CP_PHASE_CODE’
and fcr.status_code = fl_s.lookup_code
and fl_s.lookup_type = ‘CP_STATUS_CODE’
and fcr.request_id = &request_id
order by fcr.request_id desc;

Query to Get the Responsibility Name to which a concurrent program is attached:

SELECT frt.responsibility_name,
frg.request_group_name,
frgu.request_unit_type,
frgu.request_unit_id,
fcpt.user_concurrent_program_name
FROM fnd_responsibility fr,
fnd_responsibility_tl frt,
fnd_request_groups frg,
fnd_request_group_units frgu,
fnd_concurrent_programs_tl fcpt
WHERE frt.responsibility_id = fr.responsibility_id
AND frg.request_group_id = fr.request_group_id
AND frgu.request_group_id = frg.request_group_id
AND fcpt.concurrent_program_id = frgu.request_unit_id
AND frt.LANGUAGE = userenv('LANG')
AND fcpt.LANGUAGE = userenv('LANG')
AND fcpt.user_concurrent_program_name = 'BBY Payc Update Costs'
ORDER BY 1,2,3,4;

Applicant Creation:
===================
A Record is created in per_all_people_f with person_id of Applicant and also the applicant_number column gets populated.
Also a record gets created in hz_parties table with party_type as PERSON and party_id is link between per_all_people_f and hz_parties.
Also a record gets created in PER_ALL_ASSIGNMENTS_F table but with no assignment number.
THERE WON’T BE ANY RECORD IN PER_PERIODS_OF_SERVICE TABLE

Applicant Hired as Employee:
============================
When an applicant is originally hired, an additional record is created in PER_ALL_PEOPLE_F with a person type of EMPLOYEE and an effective start date of the hire date and effective end date of "the end of time" (represented by the date December 31, 4712). At the same time, one additional record is created in PER_ALL_ASSIGNMENTS_F with start date as Hire Date and end date as 31-Dec-4712. The Applicant Assignment Record in end dated. And one record is created in PER_PERIODS_OF_SERVICE table.

Employee Termination:
=====================
1) Navigate to US HR or US HRMS Manager (or equivalent) > People > Enter and Maintain >
Others > End Employment.

Termination of employee with Same Actual, Last Standard and Final Process Date:

An additional row is created in PER_ALL_PEOPLE_F with a person type of EX_EMPLOYEE and at the same time the original EMPLOYEE record will be end dated as will the assignment record.
A new assignment record is created in the PER_ALL_ASSIGNEMNTS_F table for terminated assignment with start date as termination date and end date as 31-Dec-4712.

Also the period of service record in the table, PER_PERIODS_OF_SERVICE is updated with values for Actual, Last Standard and Final Process Date.

Reverse Termination:
The EX_EMPLOYEE record is removed from the PER_ALL_PEOPLE_F table and the employee record is made end dated in Future (31-Dec-4712).
Also the Termination assignment records are removed from PER_ALL_ASSIGNMENTS_F
The PER_PERIODS_OF_SERVICE is updated with no values for Actual, Last Standard and Final Process Date.

Termination of employee with an Actual date and different but same Last Standard and Final Process Date:

Actual Date: 31-Jan-2009
Last Standard Process: 05-Feb-2009
Final Process Date: 05-Feb-2009

An additional row is created in PER_ALL_PEOPLE_F with a person type of EX_EMPLOYEE and at the same time the original EMPLOYEE record will be end dated as will the assignment record.

Two assitional assignment records will be created after end dating the current assignment record.
One Employee Assignment Type Record with Terminate Assignment status from 01-FEB-2009 to 05-FEB-2009
One Benefits Active Assignment record from 01-FEB-2009 to 31-DEC-4712
The PER_PERSON_TYPE_USAGES_F will have a record from 01-FEB-2009 to 31-DEC-4712 for person type, EX_EMP - Ex-employee.

Termination of employee with same Actual date and Last Standard Process Date but Different Final Process Date:

Actual Date: 31-Jan-2009
Last Standard Process: 31-Jan-2009
Final Process Date: 05-Feb-2009

An additional row is created in PER_ALL_PEOPLE_F with a person type of EX_EMPLOYEE and at the same time the original EMPLOYEE record will be end dated as will the assignment record.

Two assitional assignment records will be created after end dating the current assignment record.
One Employee Assignment Type Record with Terminate Assignment status from 01-FEB-2009 to 05-FEB-2009
One Benefits Active Assignment record from 01-FEB-2009 to 31-DEC-4712

Employee Rehired:
=================
If the employee is rehired, another record would be created, similar to the original one.
In PER_ALL_PEOPLE_F, an additional record would be created with start date as new hire date and end date as 31-Dec-12 with person_type of Employee.
In PER_ALL_ASSIGNMENTS_F, a new record will be created with start date as new hire date and end date as 31-Dec-12.
In PER_PERIODS_OF_SERVICE, a new would be created with start date as new hire date.

Things to note about this section:
1) The person ID does not change when the employee is rehired.
2) The assignment ID does change when the employee is rehired.