Oracle Apps

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.

In the custom.pll, modify the below given function and procedure:

function zoom_available return boolean is
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
begin
if (form_name = 'POXPOEPO' and block_name = 'PO_LINES')
return TRUE;
else
return FALSE;
end if;
end zoom_available;

procedure event(event_name varchar2) is
form_name varchar2(30) := name_in('system.current_form');
block_name varchar2(30) := name_in('system.cursor_block');
param_to_pass1 VARCHAR2(10) := ' ';
param_to_pass2 VARCHAR2(10) := ' ';
Begin
if (event_name = 'ZOOM') then
if ((form_name = 'POXPOEPO' and block_name = 'PO_LINES')) then
FND_PROFILE.GET('MFG_ORGANIZATION_ID',param_to_pass1);
param_to_pass2 := name_in('PO_LINES.item_id');
fnd_function.execute
(
FUNCTION_NAME=>'XXONHAND',
OPEN_FLAG=>'Y',
SESSION_FLAG=>'Y',
OTHER_PARAMS=>'ORGANIZATION_ID="'||param_to_pass1||'"INVENTORY_ITEM_ID="'||param_to_pass2||'"'
);
end if;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
END;

Attach the form function 'XXONHAND' to the menu which is attached to the responsibility to which
we want the zoom function to be available.

Once this is done. we can see the zoom functionality on the form_name = 'POXPOEPO' and block_name = 'PO_LINES'

In the custom form XXONHAND, create a datablock using a view named, MTL_ONHAND_SUB_V
In the form, add two parameters: INVENTORY_ITEM_ID and ORGANIZATION_ID
and in the where clause of the datablock put the below string:
WHERE (:parameter.INVENTORY_ITEM_ID = MTL_ONHAND_SUB_V.INVENTORY_ITEM_ID)

By this we will be able to see the onhand availability of the item on the XXONHAND form.

Cheers...
Khwaja Hassan