VIEW: APPS.PAYBV_ELEMENT_ENTRY_DET

Source

View Text - Preformatted

SELECT last_name, First_name, middle_names, employee_number, assignment_number, national_identifier, payroll_name, hou_org.name Organization_Name, scl.segment1 TAX_UNIT_ID, pay_costing_detail_rep_pkg.get_costing_tax_unit_name(scl.segment1) GRE_NAME, hl.location_code location_code, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,1,'ELEMENT_NAME',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) ELEMENT_NAME, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,1,'CLASSIFICATION',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) CLASSIFICATION, hr_bis.bis_decode_lookup('CREATOR_TYPE',pee.CREATOR_TYPE) Source, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,1,'RECURRING',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) RECURRING, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,1,'NAME',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) Input_value1_Name, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,1,'VALUE',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) value1, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,2,'NAME',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) Input_value2_Name, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,2,'VALUE',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) value2, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,3,'NAME',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) Input_value3_Name, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,3,'VALUE',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) value3, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,4,'NAME',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) Input_value4_Name, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,4,'VALUE',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) value4, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,5,'NAME',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) Input_value5_Name, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,5,'VALUE',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) value5, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,6,'NAME',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) Input_value6_Name, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,6,'VALUE',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) value6, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,7,'NAME',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) Input_value7_Name, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,7,'VALUE',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) value7, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,8,'NAME',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) Input_value8_Name, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,8,'VALUE',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) value8, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,9,'NAME',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) Input_value9_Name, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,9,'VALUE',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) value9, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,10,'NAME',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) Input_value10_Name, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,10,'VALUE',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) value10, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,11,'NAME',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) Input_value11_Name, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,11,'VALUE',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) value11, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,12,'NAME',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) Input_value12_Name, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,12,'VALUE',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) value12, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,13,'NAME',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) Input_value13_Name, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,13,'VALUE',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) value13, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,14,'NAME',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) Input_value14_Name, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,14,'VALUE',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) value14, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,15,'NAME',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) Input_value15_Name, PAY_ADHOC_UTILS_PKG.get_input_name(pee.element_entry_id,15,'VALUE',ptp.start_date,ptp.end_date,pee.effective_start_date,pee.effective_end_date) value15, ptp.start_date, ptp.end_date, paf.business_group_id, pee.element_entry_id, pf.payroll_id, pee.effective_start_date entry_start_date, pee.effective_end_date entry_end_date, CASE WHEN pf.business_group_id = nvl(hr_bis.get_sec_profile_bg_id, pf.business_group_id) THEN 'Y' ELSE 'N' END bg_security_flag_code from pay_element_entries_f pee, per_people_f ppf, per_assignments_f paf, pay_payrolls_f pf, hr_organization_units hou_org , hr_locations hl, per_time_periods ptp, hr_soft_coding_keyflex scl WHERE pee.CREATOR_TYPE <> 'UT' and pee.assignment_id = paf.assignment_id and paf.person_id = ppf.person_id and pf.payroll_id = paf.payroll_id and hou_org.organization_id = paf.organization_id and hl.location_id = paf.location_id and ptp.payroll_id = pf.payroll_id and (pee.EFFECTIVE_START_DATE <= ptp.end_date and pee.effective_end_date >= ptp.start_date) and ptp.start_date between ppf.EFFECTIVE_START_DATE and ppf.effective_end_date and ptp.start_date between paf.EFFECTIVE_START_DATE and paf.effective_end_date and ptp.start_date between pf.EFFECTIVE_START_DATE and pf.effective_end_date and scl.soft_coding_keyflex_id(+) = paf.soft_coding_keyflex_id

View Text - HTML Formatted

SELECT LAST_NAME
, FIRST_NAME
, MIDDLE_NAMES
, EMPLOYEE_NUMBER
, ASSIGNMENT_NUMBER
, NATIONAL_IDENTIFIER
, PAYROLL_NAME
, HOU_ORG.NAME ORGANIZATION_NAME
, SCL.SEGMENT1 TAX_UNIT_ID
, PAY_COSTING_DETAIL_REP_PKG.GET_COSTING_TAX_UNIT_NAME(SCL.SEGMENT1) GRE_NAME
, HL.LOCATION_CODE LOCATION_CODE
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 1
, 'ELEMENT_NAME'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) ELEMENT_NAME
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 1
, 'CLASSIFICATION'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) CLASSIFICATION
, HR_BIS.BIS_DECODE_LOOKUP('CREATOR_TYPE'
, PEE.CREATOR_TYPE) SOURCE
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 1
, 'RECURRING'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) RECURRING
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 1
, 'NAME'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) INPUT_VALUE1_NAME
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 1
, 'VALUE'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) VALUE1
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 2
, 'NAME'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) INPUT_VALUE2_NAME
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 2
, 'VALUE'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) VALUE2
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 3
, 'NAME'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) INPUT_VALUE3_NAME
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 3
, 'VALUE'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) VALUE3
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 4
, 'NAME'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) INPUT_VALUE4_NAME
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 4
, 'VALUE'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) VALUE4
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 5
, 'NAME'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) INPUT_VALUE5_NAME
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 5
, 'VALUE'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) VALUE5
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 6
, 'NAME'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) INPUT_VALUE6_NAME
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 6
, 'VALUE'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) VALUE6
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 7
, 'NAME'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) INPUT_VALUE7_NAME
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 7
, 'VALUE'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) VALUE7
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 8
, 'NAME'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) INPUT_VALUE8_NAME
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 8
, 'VALUE'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) VALUE8
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 9
, 'NAME'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) INPUT_VALUE9_NAME
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 9
, 'VALUE'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) VALUE9
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 10
, 'NAME'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) INPUT_VALUE10_NAME
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 10
, 'VALUE'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) VALUE10
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 11
, 'NAME'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) INPUT_VALUE11_NAME
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 11
, 'VALUE'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) VALUE11
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 12
, 'NAME'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) INPUT_VALUE12_NAME
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 12
, 'VALUE'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) VALUE12
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 13
, 'NAME'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) INPUT_VALUE13_NAME
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 13
, 'VALUE'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) VALUE13
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 14
, 'NAME'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) INPUT_VALUE14_NAME
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 14
, 'VALUE'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) VALUE14
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 15
, 'NAME'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) INPUT_VALUE15_NAME
, PAY_ADHOC_UTILS_PKG.GET_INPUT_NAME(PEE.ELEMENT_ENTRY_ID
, 15
, 'VALUE'
, PTP.START_DATE
, PTP.END_DATE
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE) VALUE15
, PTP.START_DATE
, PTP.END_DATE
, PAF.BUSINESS_GROUP_ID
, PEE.ELEMENT_ENTRY_ID
, PF.PAYROLL_ID
, PEE.EFFECTIVE_START_DATE ENTRY_START_DATE
, PEE.EFFECTIVE_END_DATE ENTRY_END_DATE
, CASE WHEN PF.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PF.BUSINESS_GROUP_ID) THEN 'Y' ELSE 'N' END BG_SECURITY_FLAG_CODE
FROM PAY_ELEMENT_ENTRIES_F PEE
, PER_PEOPLE_F PPF
, PER_ASSIGNMENTS_F PAF
, PAY_PAYROLLS_F PF
, HR_ORGANIZATION_UNITS HOU_ORG
, HR_LOCATIONS HL
, PER_TIME_PERIODS PTP
, HR_SOFT_CODING_KEYFLEX SCL
WHERE PEE.CREATOR_TYPE <> 'UT'
AND PEE.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND PAF.PERSON_ID = PPF.PERSON_ID
AND PF.PAYROLL_ID = PAF.PAYROLL_ID
AND HOU_ORG.ORGANIZATION_ID = PAF.ORGANIZATION_ID
AND HL.LOCATION_ID = PAF.LOCATION_ID
AND PTP.PAYROLL_ID = PF.PAYROLL_ID
AND (PEE.EFFECTIVE_START_DATE <= PTP.END_DATE
AND PEE.EFFECTIVE_END_DATE >= PTP.START_DATE)
AND PTP.START_DATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PTP.START_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PTP.START_DATE BETWEEN PF.EFFECTIVE_START_DATE
AND PF.EFFECTIVE_END_DATE
AND SCL.SOFT_CODING_KEYFLEX_ID(+) = PAF.SOFT_CODING_KEYFLEX_ID