-
-

Payroll Element Eligibility

ELEMENTS

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