Payroll Element Eligibility
SELECT pett.element_type_id
,petf.base_element_name
,pett.element_name
,pett.reporting_name
,TO_CHAR(petf.effective_start_date, 'YYYY/MM/DD') ele_effective_start_date
,TO_CHAR(petf.effective_end_date, 'YYYY/MM/DD') ele_effective_end_date
,pecv_pri.classification_name primary_class
,pecv_sec.classification_name secondary_class
,pldgv.name legislative_data_group
,pldgv.legislation_code
,TO_CHAR(pelf.effective_start_date, 'YYYY/MM/DD') elig_effective_start_date
,TO_CHAR(pelf.effective_end_date, 'YYYY/MM/DD') elig_effective_end_date
,pelf.element_link_name element_eligibility
,CASE WHEN (petf.processing_type = 'R' AND pelf.standard_link_flag = 'Y')
THEN 'Y'
ELSE 'N'
END eligibility_automatic_entry
,pec.link_to_all_payrolls_flag
,pec.criteria_type
,hr_general.decode_lookup('PAY_EC_CRITERIA_TYPE', pec.criteria_type) criteria_type_meaning
,pec.link_level
,hr_general.decode_lookup('PAY_EC_LINK_LEVEL', pec.link_level) link_level_meaning
,pgfv.name grade_id
,pjfv.name job_id
,hlafv.location_name location_id
,haouf.name organization_id
,pcaft.collective_agreement_name collective_agreement_id
,papf_pay.payroll_name payroll_name
,hl.meaning employment_category
,ppg.group_name people_group_id
,hapf.name position_id
,(select relationship_type_name
from PAY_RELATIONSHIP_TYPES_VL
where relationship_type_id = pec.relationship_type_id) relationship_type_id
,(select name
from hr_organization_units_f_tl
where organization_id = pec.payroll_stat_unit_id
and language = USERENV('LANG')
and :P_AS_OF_DATE BETWEEN effective_start_date and effective_end_date) payroll_stat_unit_id
,leg_emp.name legal_employer_id
,hr_general.decode_lookup('BARGAINING_UNIT_CODE',pec.bargaining_unit_code) bargaining_unit_code
,pec.labour_union_member_flag
,( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = pelf.defaulting_formula_id
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) defaulting_formula
,( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = pelf.calculation_formula_id
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) calculation_formula
,( SELECT FFV.FORMULA_NAME
FROM FF_FORMULAS_VL FFV
WHERE FFV.FORMULA_ID = pelf.validation_formula_id
AND :P_AS_OF_DATE BETWEEN FFV.EFFECTIVE_START_DATE AND FFV.EFFECTIVE_END_DATE
) validation_formula
,pivl.name input_value_name
,hr_general.decode_lookup('PAY_UNITS',pivl.uom) iv_uom
,pivl.default_value iv_default_value
,pivl.max_value iv_max_value
,pivl.min_value iv_min_value
,pivl.warning_or_error
,to_char(pivl.effective_start_date,'yyyy/mm/dd') iv_effective_start_date
,to_char(pivl.effective_end_date,'yyyy/mm/dd') iv_effective_end_date
,(SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE='PAY_COSTABLE_TYPE' AND LOOKUP_CODE=PCIF.COSTABLE_TYPE)costing_type
,(SELECT MEANING FROM HR_LOOKUPS WHERE LOOKUP_TYPE='YES_NO' AND LOOKUP_CODE=PCIF.TRANSFER_TO_GL_FLAG AND ENABLED_FLAG='Y') tranf_gl
,pogv.description distribution
,pcif.costed_flag costed_input_val
,cost_acnt.segment1 cost_company
,cost_acnt.segment3 cost_account
,cost_acnt.segment4 cost_cost_cen
,offset_acnt.segment1 offset_company
,offset_acnt.segment3 offset_account
,offset_acnt.segment4 offset_cost_cen
FROM
pay_element_types_f petf
,pay_element_types_tl pett
,pay_ele_classifications_vl pecv_pri
,pay_ele_classifications_vl pecv_sec
,per_legislative_data_groups_vl pldgv
,pay_element_links_f pelf
,pay_element_criteria pec
,per_grades_f_vl pgfv
,per_jobs_f_vl pjfv
,hr_locations_all_f_vl hlafv
,hr_all_organization_units_f_vl haouf
,per_col_agreements_f_tl pcaft
,hr_lookups hl
,per_people_groups ppg
,hr_all_positions_f_vl hapf
,hr_all_organization_units_f_vl leg_emp
,pay_all_payrolls_f papf_pay
,pay_link_input_values_f plivf
,pay_input_values_vl pivl
,pay_cost_allocations_f pcaf
,pay_cost_info_f pcif
,pay_cost_alloc_accounts cost_acnt
,pay_cost_alloc_accounts offset_acnt
,pay_object_groups_vl pogv
,pay_object_group_types pogt
WHERE petf.element_type_id = pett.element_type_id
AND pett.language = USERENV ('LANG')
AND petf.element_type_id = pelf.element_type_id(+)
AND PETF.CLASSIFICATION_ID = pecv_pri.base_classification_id
AND PETF.secondary_classification_id = pecv_sec.base_classification_id
AND pecv_pri.base_classification_id = pecv_sec.parent_classification_id
AND pldgv.legislation_code = pecv_pri.legislation_code
AND pldgv.legislation_code = pecv_sec.legislation_code
AND petf.legislative_data_group_id = pldgv.legislative_data_group_id(+)
AND TRUNC(:P_AS_OF_DATE) BETWEEN petf.effective_start_date AND petf.effective_end_date
AND TRUNC(:P_AS_OF_DATE) BETWEEN pelf.effective_start_date(+) AND pelf.effective_end_date(+)
AND pelf.element_criteria_id = pec.element_criteria_id(+)
AND pec.grade_id = pgfv.grade_id(+)
AND TRUNC(:P_AS_OF_DATE) BETWEEN pgfv.EFFECTIVE_START_DATE(+) AND pgfv.EFFECTIVE_END_DATE(+)
AND pec.job_id = pjfv.job_id(+)
AND pec.location_id = hlafv.location_id(+)
AND pec.organization_id = haouf.organization_id(+)
AND pec.collective_agreement_id = pcaft.collective_agreement_id (+)
AND pcaft.language(+) = USERENV('LANG')
AND hl.LOOKUP_TYPE(+) = 'EMP_CAT'
AND hl.lookup_code(+) = pec.employment_category
AND hl.ENABLED_FLAG(+) = 'Y'
AND pec.people_group_id = ppg.people_group_id(+)
AND ppg.enabled_flag(+) = 'Y'
AND pec.position_id = hapf.position_id(+)
AND pec.legal_employer_id = leg_emp.organization_id(+)
AND pec.payroll_id = papf_pay.payroll_id(+)
AND TRUNC(:P_AS_OF_DATE) BETWEEN papf_pay.effective_start_date(+) AND papf_pay.effective_end_date(+)
AND TRUNC(:P_AS_OF_DATE) BETWEEN pgfv.EFFECTIVE_START_DATE(+) AND pgfv.EFFECTIVE_END_DATE(+)
AND TRUNC(:P_AS_OF_DATE) BETWEEN pjfv.EFFECTIVE_START_DATE(+) AND pjfv.EFFECTIVE_END_DATE(+)
AND TRUNC(:P_AS_OF_DATE) BETWEEN haouf.EFFECTIVE_START_DATE(+) AND haouf.EFFECTIVE_END_DATE(+)
AND TRUNC(:P_AS_OF_DATE) BETWEEN pcaft.EFFECTIVE_START_DATE(+) AND pcaft.EFFECTIVE_END_DATE(+)
AND TRUNC(:P_AS_OF_DATE) BETWEEN ppg.START_DATE_ACTIVE(+) AND NVL(ppg.END_DATE_ACTIVE(+),:P_AS_OF_DATE)
AND TRUNC(:P_AS_OF_DATE) BETWEEN hapf.EFFECTIVE_START_DATE(+) AND hapf.EFFECTIVE_END_DATE(+)
AND TRUNC(:P_AS_OF_DATE) BETWEEN leg_emp.EFFECTIVE_START_DATE(+) AND leg_emp.EFFECTIVE_END_DATE(+)
AND plivf.input_value_id(+) = pivl.input_value_id
AND pelf.element_link_id = plivf.element_link_id(+)
AND petf.element_type_id = pivl.element_type_id(+)
AND TRUNC(:P_AS_OF_DATE) BETWEEN plivf.effective_start_date(+) AND plivf.effective_end_date(+)
AND TRUNC(:P_AS_OF_DATE) BETWEEN pivl.effective_start_date(+) AND pivl.effective_end_date(+)
AND pelf.element_link_id = pcaf.source_id(+)
AND pcaf.source_type(+) = 'EL'
AND pcaf.source_id = pcif.source_id(+)
AND pcaf.cost_allocation_record_id = cost_acnt.cost_allocation_record_id(+)
AND cost_acnt.source_sub_type(+) = 'COST'
--AND pcaf.legislative_data_group_id = pldg.legislative_data_group_id --commented now
AND pcaf.cost_allocation_record_id = offset_acnt.cost_allocation_record_id(+)
AND offset_acnt.source_sub_type(+) = 'BAL'
AND pogv.object_group_type_id(+) = pogt.object_group_type_id
AND pogt.name_code(+) = 'ELEGRP'
AND pecv_pri.classification_name = 'Absences'
AND (
(:P_CREATION_TYPE= 'Custom' and pelf.created_by <> 'SEED_DATA_FROM_APPLICATION' )
OR (:P_CREATION_TYPE = 'Seeded' and pelf.created_by = 'SEED_DATA_FROM_APPLICATION' )
OR (:P_CREATION_TYPE = 'Seeded Modified' and pelf.created_by = 'SEED_DATA_FROM_APPLICATION' and pelf.last_updated_by <> 'SEED_DATA_FROM_APPLICATION')
OR (:P_CREATION_TYPE = 'ALL')
)
ORDER BY pldgv.name
,petf.base_element_name
,pelf.element_link_name
