SELECT asg.organization_id,
emp.employee_number,
emp.full_name emp_name,
pos.NAME POSITION,
sup.employee_number sup_emp_no,
sup.full_name sup_name,
sup_pos.NAME sup_pos,
sppt.user_person_type,
emp.effective_start_date emp_esd,
emp.effective_end_date emp_eed,
asg.effective_start_date asg_esd,
asg.effective_end_date asg_eed,
sup.effective_start_date sup_esd,
sup.effective_end_date sup_eed,
sup_asg.effective_start_date sup_asg_esd,
sup_asg.effective_end_date sup_asg_eed,
sptu.effective_start_date sptu_esd,
sptu.effective_end_date sptu_eed
FROM per_all_people_f emp,
per_all_assignments_f asg,
per_all_positions pos,
per_all_people_f sup,
per_all_assignments_f sup_asg,
per_all_positions sup_pos,
per_person_type_usages_f sptu,
per_person_types sppt
WHERE asg.person_id = emp.person_id
AND pos.position_id(+) = asg.position_id
AND sup.person_id(+) = asg.supervisor_id
AND sup_asg.person_id(+) = sup.person_id
AND sup_pos.position_id(+) = sup_asg.position_id
AND sptu.person_id = emp.person_id
AND sppt.person_type_id = sptu.person_type_id
and emp.person_type_id = 1120
Best Blogger TemplatesBest Blogger Tips
Posted by Raju Ch at 12:45 AM 0 comments
Email This
BlogThis!
Share to Twitter
Share to Facebook
Share to Pinterest
Labels: HRMS, SCRIPTS
Oracle HRMS Scripts(Queries) - Employee Elements
SELECT entry.element_entry_id, entry.assignment_id,
entry.effective_start_date, entry.effective_end_date,
entry.cost_allocation_keyflex_id, entry.updating_action_id,
entry.element_link_id, entry.original_entry_id, entry.creator_type,
entry.entry_type, DECODE (entry.entry_type, 'D', 'Y', 'N'),
DECODE (entry.entry_type, 'S', 'Y', 'N'), entry.comment_id,
entry.creator_id, entry.reason,
hr_general.decode_lookup ('ELE_ENTRY_REASON', entry.reason),
entry.target_entry_id, entry.attribute_category, entry.attribute1,
entry.attribute2, entry.attribute3, entry.attribute4,
entry.attribute5, entry.attribute6, entry.attribute7,
entry.attribute8, entry.attribute9, entry.attribute10,
entry.attribute11, entry.attribute12, entry.attribute13,
entry.attribute14, entry.attribute15, entry.attribute16,
entry.attribute17, entry.attribute18, entry.attribute19,
entry.attribute20, ELEMENT.element_type_id, elementtl.element_name,
SUBSTR (ELEMENT.processing_type, 1, 1),
hr_general.decode_lookup ('PROCESSING_TYPE',
ELEMENT.processing_type),
ELEMENT.processing_priority,
SUBSTR (ELEMENT.process_in_run_flag, 1, 1),
SUBSTR (ELEMENT.closed_for_entry_flag, 1, 1),
SUBSTR (ELEMENT.additional_entry_allowed_flag, 1, 1),
SUBSTR (ELEMENT.multiple_entries_allowed_flag, 1, 1),
ELEMENT.input_currency_code,
SUBSTR (pay_paywsmee_pkg.overridden (LINK.element_link_id,
entry.assignment_id,
sesh.effective_date
),
1,
1
),
SUBSTR (pay_paywsmee_pkg.adjusted (LINK.element_link_id,
entry.assignment_id,
sesh.effective_date
),
1,
1
),
SUBSTR (pay_paywsmee_pkg.processed (entry.element_entry_id,
entry.original_entry_id,
ELEMENT.processing_type,
entry.entry_type,
sesh.effective_date
),
1,
1
),
DECODE (NVL (entry_proc.source_asg_action_id, -1),
-1, 'N',
'Y'
) retroactive,
ELEMENT.classification_id, SUBSTR (LINK.costable_type, 1, 1),
entry.subpriority, benefit.contributions_used, entry.creation_date,
entry.created_by, entry.last_update_login, entry.last_updated_by,
entry.last_update_date, entry.date_earned,
pay_paywsmee_pkg.get_original_date_earned
(entry.element_entry_id)
original_date_earned,
entry.personal_payment_method_id,
SUBSTR
(pay_paywsmee_pkg.personal_payment_method
(entry.personal_payment_method_id,
entry.assignment_id,
sesh.effective_date
),
1,
255
),
ELEMENT.third_party_pay_only_flag, entry.ROWID row_id,
entry.entry_information_category, entry.entry_information1,
entry.entry_information2, entry.entry_information3,
entry.entry_information4, entry.entry_information5,
entry.entry_information6, entry.entry_information7,
entry.entry_information8, entry.entry_information9,
entry.entry_information10, entry.entry_information11,
entry.entry_information12, entry.entry_information13,
entry.entry_information14, entry.entry_information15,
entry.entry_information16, entry.entry_information17,
entry.entry_information18, entry.entry_information19,
entry.entry_information20, entry.entry_information21,
entry.entry_information22, entry.entry_information23,
entry.entry_information24, entry.entry_information25,
entry.entry_information26, entry.entry_information27,
entry.entry_information28, entry.entry_information29,
entry.entry_information30, -999999999999999
FROM pay_element_types_f_tl elementtl,
pay_element_types_f ELEMENT,
ben_benefit_classifications benefit,
pay_element_entries_f entry,
pay_element_links_f LINK,
pay_entry_process_details entry_proc,
fnd_sessions sesh /* TABLE JOINS */
WHERE ELEMENT.element_type_id = elementtl.element_type_id
AND elementtl.LANGUAGE = 'AR'
AND ELEMENT.element_type_id = LINK.element_type_id
AND ELEMENT.benefit_classification_id = benefit.benefit_classification_id(+)
AND entry.element_link_id = LINK.element_link_id
AND entry.element_entry_id =
entry_proc.element_entry_id(+)
/* ONLY DISPLAY ENTRIES FOR CERTAIN SUB-SYSTEMS */
AND entry.creator_type IN
('H', /* MIX */
'P', /* BACKPAY */
'SP', /* SALARY PROPOSAL */
'F', /* OTHER */
'M', /* STATUTORY MATERNITY PAY */
'S', /* STATUTORY SICK PAY */
'A', /* ABSENCE */
'D', /* ADVANCE PAY */
'DF', /* ADVANCE PAY FORM */
'R', /* RETROPAY BY ACTION */
'EE', /* RETROPAY/ELEMENT EE */
'RR', /* RETROPAY/ELEMENT RR */
'AD', /* ADVANCEPAY/ELEMENT AD */
'AE', /* ADVANCEPAY/ELEMENT AE */
'PR', /* RETROPAY ELEMENT PR */
'NR', /* RETROPAY/ELEMENT NR */
'FL' /* FLSA ENTRY */
) /* ONLY DISPLAY ENTRIES OF CERTAIN TYPES */
AND entry.entry_type IN
('E', /* NORMAL ENTRY */ 'S', /* OVERRIDE */ 'D' /* ADDITIONAL */)
/* ONLY DISPLAY ENTRIES CURRENT AS OF EFFECTIVE DATE */
AND USERENV ('sessionid') = sesh.session_id
AND sesh.effective_date BETWEEN ELEMENT.effective_start_date AND ELEMENT.effective_end_date
AND sesh.effective_date BETWEEN LINK.effective_start_date AND LINK.effective_end_date
AND sesh.effective_date BETWEEN entry.effective_start_date AND entry.effective_end_date
UNION ALL
SELECT DISTINCT entry.element_entry_id, entry.assignment_id,
entry.effective_start_date, entry.effective_end_date,
entry.cost_allocation_keyflex_id, entry.updating_action_id,
entry.element_link_id, entry.original_entry_id,
entry.creator_type, entry.entry_type,
DECODE (entry.entry_type, 'D', 'Y', 'N'),
DECODE (entry.entry_type, 'S', 'Y', 'N'), entry.comment_id,
entry.creator_id, entry.reason,
hr_general.decode_lookup ('ELE_ENTRY_REASON', entry.reason),
entry.target_entry_id, entry.attribute_category,
entry.attribute1, entry.attribute2, entry.attribute3,
entry.attribute4, entry.attribute5, entry.attribute6,
entry.attribute7, entry.attribute8, entry.attribute9,
entry.attribute10, entry.attribute11, entry.attribute12,
entry.attribute13, entry.attribute14, entry.attribute15,
entry.attribute16, entry.attribute17, entry.attribute18,
entry.attribute19, entry.attribute20,
ELEMENT.element_type_id, elementtl.element_name,
SUBSTR (ELEMENT.processing_type, 1, 1),
hr_general.decode_lookup ('PROCESSING_TYPE',
ELEMENT.processing_type
),
ELEMENT.processing_priority,
SUBSTR (ELEMENT.process_in_run_flag, 1, 1),
SUBSTR (ELEMENT.closed_for_entry_flag, 1, 1),
SUBSTR (ELEMENT.additional_entry_allowed_flag, 1, 1),
SUBSTR (ELEMENT.multiple_entries_allowed_flag, 1, 1),
ELEMENT.input_currency_code,
SUBSTR (pay_paywsmee_pkg.overridden (LINK.element_link_id,
entry.assignment_id,
sesh.effective_date
),
1,
1
),
SUBSTR (pay_paywsmee_pkg.adjusted (LINK.element_link_id,
entry.assignment_id,
sesh.effective_date
),
1,
1
),
SUBSTR
(pay_paywsmee_pkg.processed (entry.element_entry_id,
entry.original_entry_id,
ELEMENT.processing_type,
entry.entry_type,
sesh.effective_date
),
1,
1
),
DECODE (NVL (entry_proc.source_asg_action_id, -1),
-1, 'N',
'Y'
) retroactive,
ELEMENT.classification_id,
SUBSTR (LINK.costable_type, 1, 1), entry.subpriority,
benefit.contributions_used, entry.creation_date,
entry.created_by, entry.last_update_login,
entry.last_updated_by, entry.last_update_date,
entry.date_earned,
pay_paywsmee_pkg.get_original_date_earned
(entry.element_entry_id)
original_date_earned,
entry.personal_payment_method_id,
SUBSTR
(pay_paywsmee_pkg.personal_payment_method
(entry.personal_payment_method_id,
entry.assignment_id,
sesh.effective_date
),
1,
255
),
ELEMENT.third_party_pay_only_flag, entry.ROWID row_id,
entry.entry_information_category, entry.entry_information1,
entry.entry_information2, entry.entry_information3,
entry.entry_information4, entry.entry_information5,
entry.entry_information6, entry.entry_information7,
entry.entry_information8, entry.entry_information9,
entry.entry_information10, entry.entry_information11,
entry.entry_information12, entry.entry_information13,
entry.entry_information14, entry.entry_information15,
entry.entry_information16, entry.entry_information17,
entry.entry_information18, entry.entry_information19,
entry.entry_information20, entry.entry_information21,
entry.entry_information22, entry.entry_information23,
entry.entry_information24, entry.entry_information25,
entry.entry_information26, entry.entry_information27,
entry.entry_information28, entry.entry_information29,
entry.entry_information30, assact.assignment_action_id
FROM pay_element_types_f_tl elementtl,
pay_element_types_f ELEMENT,
ben_benefit_classifications benefit,
pay_element_entries_f entry,
pay_element_links_f LINK,
pay_entry_process_details entry_proc,
pay_assignment_actions assact,
pay_payroll_actions payact,
fnd_sessions sesh /* TABLE JOINS */
WHERE ELEMENT.element_type_id = elementtl.element_type_id
AND elementtl.LANGUAGE = USERENV ('LANG')
AND ELEMENT.element_type_id = LINK.element_type_id
AND ELEMENT.benefit_classification_id = benefit.benefit_classification_id(+)
AND entry.element_link_id = LINK.element_link_id
AND entry.element_entry_id =
entry_proc.element_entry_id(+)
/* ONLY DISPLAY ENTRIES FOR CERTAIN SUB-SYSTEMS */
AND entry.creator_type IN
('H', /* MIX */
'P', /* BACKPAY */
'SP', /* SALARY PROPOSAL */
'F', /* OTHER */
'M', /* STATUTORY MATERNITY PAY */
'S', /* STATUTORY SICK PAY */
'A', /* ABSENCE */
'D', /* ADVANCE PAY */
'DF', /* ADVANCE PAY FORM */
'R', /* RETROPAY BY ACTION */
'EE', /* RETROPAY/ELEMENT EE */
'RR', /* RETROPAY/ELEMENT RR */
'AD', /* ADVANCEPAY/ELEMENT AD */
'AE', /* ADVANCEPAY/ELEMENT AE */
'PR', /* RETROPAY ELEMENT PR */
'NR', /* RETROPAY/ELEMENT NR */
'FL' /* FLSA ENTRY */
) /* ONLY DISPLAY ENTRIES OF CERTAIN TYPES */
AND entry.entry_type IN
('E', /* NORMAL ENTRY */ 'S', /* OVERRIDE */ 'D' /* ADDITIONAL */)
/* ONLY DISPLAY ENTRIES CURRENT AS OF EFFECTIVE DATE */
AND USERENV ('sessionid') = sesh.session_id
AND sesh.effective_date BETWEEN ELEMENT.effective_start_date
AND ELEMENT.effective_end_date
AND sesh.effective_date BETWEEN LINK.effective_start_date
AND LINK.effective_end_date
AND entry.assignment_id = assact.assignment_id
AND assact.payroll_action_id = payact.payroll_action_id
AND payact.date_earned BETWEEN ELEMENT.effective_start_date
AND ELEMENT.effective_end_date
AND payact.date_earned BETWEEN LINK.effective_start_date
AND LINK.effective_end_date
AND entry.effective_start_date <= payact.date_earned
AND entry.effective_end_date >=
DECODE
(ELEMENT.proration_group_id,
NULL, payact.date_earned,
pay_interpreter_pkg.prorate_start_date
(assact.assignment_action_id,
ELEMENT.proration_group_id
)
)
