VIEW: APPS.PAYBV_ELEMENT_LINK_DETAIL_V

Source

View Text - Preformatted

SELECT pectl.classification_name , pettl.element_name , hr_bis.bis_decode_lookup ('PROCESSING_TYPE',pet.processing_type) processing_type_m , hr_bis.bis_decode_lookup ('YES_NO',pel.standard_link_flag ) standard_link_m , hr_bis.bis_decode_lookup ('YES_NO',pel.link_to_all_payrolls_flag) link_to_all_payrolls_flag_m , pel.qualifying_age age_criteria , pel.qualifying_length_of_service service_criteria , pel.effective_start_date , pel.effective_end_date , ( select min(effective_start_date) from pay_element_links_f pel1 WHERE pel1.element_link_id = pel.element_link_id ) link_start_date , ( select max(effective_end_date) from pay_element_links_f pel2 where pel2.element_link_id = pel.element_link_id ) link_end_date , pel.cost_allocation_keyflex_id , pel.balancing_keyflex_id , decode(pel.link_to_all_payrolls_flag,'N',pap.payroll_name, hr_bis.bis_decode_lookup('YES_NO_ALL','A') ) payroll_name , hr_general.decode_people_group(pel.people_group_id) people_group_name , hr_bis.bis_decode_lookup('COSTABLE_TYPE',pel.costable_type) costable_type_m , pes.element_set_name distribution_set_name , hr_general.decode_organization(pel.organization_id) organization_name , hr_general.decode_job(pel.job_id) job_name , hr_general.decode_position(pel.position_id) position_name , hr_general.decode_grade(pel.grade_id) grade_name , hr_general.decode_pay_basis(pel.pay_basis_id) pay_basis_name , hr_general.decode_location(pel.location_id) location_name , hr_bis.bis_decode_lookup('EMP_CAT',pel.employment_category) employment_category_m , hr_bis.bis_decode_lookup('YES_NO',piv.hot_default_flag) hot_default_flag_m , cost.concatenated_segments costing , balcost.concatenated_segments balancing , pivtl.name input_value_name , hr_bis.bis_decode_lookup('UNITS',piv.uom) input_value_uom_m , liv.default_value input_value_default , pel.link_to_all_payrolls_flag , pet.processing_type , pel.standard_link_flag standard_link , pel.costable_type , pel.employment_category , piv.hot_default_flag , piv.uom input_value_uom , pet.element_type_id , pel.payroll_id , pel.people_group_id , pel.business_group_id , pec.classification_id , pel.organization_id , pel.job_id , pel.position_id , pel.grade_id , pel.pay_basis_id , pel.location_id , pel.element_link_id , CASE WHEN (pel.business_group_id = nvl(hr_bis.get_sec_profile_bg_id, pel.business_group_id)) THEN 'Y' ELSE 'N' END bg_security_flag_code , pel.qualifying_units qualifying_units , hr_bis.bis_decode_lookup ('QUALIFYING_UNITS', pel.qualifying_units) qualifying_units_m from pay_element_classifications pec , pay_element_classifications_tl pectl , pay_element_types_f pet , pay_element_types_f_tl pettl , pay_link_input_values_f liv , pay_input_values_f piv , pay_input_values_f_tl pivtl , pay_element_links_f pel , pay_element_sets pes , pay_payrolls_f pap , pay_cost_allocation_keyflex cost , pay_cost_allocation_keyflex balcost where pet.element_type_id = pettl.element_type_id and pettl.language = userenv('LANG') and pet.element_type_id = pel.element_type_id and pel.effective_start_date between pet.effective_start_date and pet.effective_end_date and pec.classification_id = pectl.classification_id and pectl.language = userenv('LANG') and pec.classification_id = pet.classification_id and piv.input_value_id = pivtl.input_value_id and pivtl.language = userenv('LANG') and piv.input_value_id = liv.input_value_id and pel.effective_start_date between piv.effective_start_date and piv.effective_end_date and pel.element_link_id = liv.element_link_id and pel.effective_start_date between liv.effective_start_Date and liv.effective_end_date and pel.element_set_id = pes.element_set_id (+) and pel.payroll_id = pap.payroll_id(+) and pel.effective_start_date between nvl(pap.effective_start_date, pel.effective_start_date) and nvl(pap.effective_end_date, pel.effective_start_date) and pel.cost_allocation_keyflex_id = cost.cost_allocation_keyflex_id (+) and pel.balancing_keyflex_id = balcost.cost_allocation_keyflex_id (+) WITH READ ONLY

View Text - HTML Formatted

SELECT PECTL.CLASSIFICATION_NAME
, PETTL.ELEMENT_NAME
, HR_BIS.BIS_DECODE_LOOKUP ('PROCESSING_TYPE'
, PET.PROCESSING_TYPE) PROCESSING_TYPE_M
, HR_BIS.BIS_DECODE_LOOKUP ('YES_NO'
, PEL.STANDARD_LINK_FLAG ) STANDARD_LINK_M
, HR_BIS.BIS_DECODE_LOOKUP ('YES_NO'
, PEL.LINK_TO_ALL_PAYROLLS_FLAG) LINK_TO_ALL_PAYROLLS_FLAG_M
, PEL.QUALIFYING_AGE AGE_CRITERIA
, PEL.QUALIFYING_LENGTH_OF_SERVICE SERVICE_CRITERIA
, PEL.EFFECTIVE_START_DATE
, PEL.EFFECTIVE_END_DATE
, ( SELECT MIN(EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_LINKS_F PEL1
WHERE PEL1.ELEMENT_LINK_ID = PEL.ELEMENT_LINK_ID ) LINK_START_DATE
, ( SELECT MAX(EFFECTIVE_END_DATE)
FROM PAY_ELEMENT_LINKS_F PEL2
WHERE PEL2.ELEMENT_LINK_ID = PEL.ELEMENT_LINK_ID ) LINK_END_DATE
, PEL.COST_ALLOCATION_KEYFLEX_ID
, PEL.BALANCING_KEYFLEX_ID
, DECODE(PEL.LINK_TO_ALL_PAYROLLS_FLAG
, 'N'
, PAP.PAYROLL_NAME
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO_ALL'
, 'A') ) PAYROLL_NAME
, HR_GENERAL.DECODE_PEOPLE_GROUP(PEL.PEOPLE_GROUP_ID) PEOPLE_GROUP_NAME
, HR_BIS.BIS_DECODE_LOOKUP('COSTABLE_TYPE'
, PEL.COSTABLE_TYPE) COSTABLE_TYPE_M
, PES.ELEMENT_SET_NAME DISTRIBUTION_SET_NAME
, HR_GENERAL.DECODE_ORGANIZATION(PEL.ORGANIZATION_ID) ORGANIZATION_NAME
, HR_GENERAL.DECODE_JOB(PEL.JOB_ID) JOB_NAME
, HR_GENERAL.DECODE_POSITION(PEL.POSITION_ID) POSITION_NAME
, HR_GENERAL.DECODE_GRADE(PEL.GRADE_ID) GRADE_NAME
, HR_GENERAL.DECODE_PAY_BASIS(PEL.PAY_BASIS_ID) PAY_BASIS_NAME
, HR_GENERAL.DECODE_LOCATION(PEL.LOCATION_ID) LOCATION_NAME
, HR_BIS.BIS_DECODE_LOOKUP('EMP_CAT'
, PEL.EMPLOYMENT_CATEGORY) EMPLOYMENT_CATEGORY_M
, HR_BIS.BIS_DECODE_LOOKUP('YES_NO'
, PIV.HOT_DEFAULT_FLAG) HOT_DEFAULT_FLAG_M
, COST.CONCATENATED_SEGMENTS COSTING
, BALCOST.CONCATENATED_SEGMENTS BALANCING
, PIVTL.NAME INPUT_VALUE_NAME
, HR_BIS.BIS_DECODE_LOOKUP('UNITS'
, PIV.UOM) INPUT_VALUE_UOM_M
, LIV.DEFAULT_VALUE INPUT_VALUE_DEFAULT
, PEL.LINK_TO_ALL_PAYROLLS_FLAG
, PET.PROCESSING_TYPE
, PEL.STANDARD_LINK_FLAG STANDARD_LINK
, PEL.COSTABLE_TYPE
, PEL.EMPLOYMENT_CATEGORY
, PIV.HOT_DEFAULT_FLAG
, PIV.UOM INPUT_VALUE_UOM
, PET.ELEMENT_TYPE_ID
, PEL.PAYROLL_ID
, PEL.PEOPLE_GROUP_ID
, PEL.BUSINESS_GROUP_ID
, PEC.CLASSIFICATION_ID
, PEL.ORGANIZATION_ID
, PEL.JOB_ID
, PEL.POSITION_ID
, PEL.GRADE_ID
, PEL.PAY_BASIS_ID
, PEL.LOCATION_ID
, PEL.ELEMENT_LINK_ID
, CASE WHEN (PEL.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PEL.BUSINESS_GROUP_ID)) THEN 'Y' ELSE 'N' END BG_SECURITY_FLAG_CODE
, PEL.QUALIFYING_UNITS QUALIFYING_UNITS
, HR_BIS.BIS_DECODE_LOOKUP ('QUALIFYING_UNITS'
, PEL.QUALIFYING_UNITS) QUALIFYING_UNITS_M
FROM PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_CLASSIFICATIONS_TL PECTL
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETTL
, PAY_LINK_INPUT_VALUES_F LIV
, PAY_INPUT_VALUES_F PIV
, PAY_INPUT_VALUES_F_TL PIVTL
, PAY_ELEMENT_LINKS_F PEL
, PAY_ELEMENT_SETS PES
, PAY_PAYROLLS_F PAP
, PAY_COST_ALLOCATION_KEYFLEX COST
, PAY_COST_ALLOCATION_KEYFLEX BALCOST
WHERE PET.ELEMENT_TYPE_ID = PETTL.ELEMENT_TYPE_ID
AND PETTL.LANGUAGE = USERENV('LANG')
AND PET.ELEMENT_TYPE_ID = PEL.ELEMENT_TYPE_ID
AND PEL.EFFECTIVE_START_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PEC.CLASSIFICATION_ID = PECTL.CLASSIFICATION_ID
AND PECTL.LANGUAGE = USERENV('LANG')
AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND PIV.INPUT_VALUE_ID = PIVTL.INPUT_VALUE_ID
AND PIVTL.LANGUAGE = USERENV('LANG')
AND PIV.INPUT_VALUE_ID = LIV.INPUT_VALUE_ID
AND PEL.EFFECTIVE_START_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PEL.ELEMENT_LINK_ID = LIV.ELEMENT_LINK_ID
AND PEL.EFFECTIVE_START_DATE BETWEEN LIV.EFFECTIVE_START_DATE
AND LIV.EFFECTIVE_END_DATE
AND PEL.ELEMENT_SET_ID = PES.ELEMENT_SET_ID (+)
AND PEL.PAYROLL_ID = PAP.PAYROLL_ID(+)
AND PEL.EFFECTIVE_START_DATE BETWEEN NVL(PAP.EFFECTIVE_START_DATE
, PEL.EFFECTIVE_START_DATE)
AND NVL(PAP.EFFECTIVE_END_DATE
, PEL.EFFECTIVE_START_DATE)
AND PEL.COST_ALLOCATION_KEYFLEX_ID = COST.COST_ALLOCATION_KEYFLEX_ID (+)
AND PEL.BALANCING_KEYFLEX_ID = BALCOST.COST_ALLOCATION_KEYFLEX_ID (+) WITH READ ONLY