VIEW: APPS.HR_CERIDIAN_ASSIGNMENT_V

Source

View Text - Preformatted

SELECT pp.employee_number , hou.name , pp.last_name , pp.first_name , pp.middle_names , pps.date_start , pps.final_process_date , nvl(hs.segment8, pjw.wc_code) , decode(ptp.number_per_fiscal_year, 52,'W',24,'S',26,'B', 12,'M', ptp.number_per_fiscal_year) , decode(ppb.pay_basis, 'HOURLY', pbev.screen_entry_value, 'ANNUAL', null, 'ERROR') , decode(ppb.pay_basis, 'ANNUAL', pbev.screen_entry_value, 'HOURLY', null, 'ERROR') , nvl(asta.pay_system_status, astb.pay_system_status) , pj.normal_hours , pca.segment1||pca.segment2||pca.segment3 , hs.segment5 , pp.business_group_id , pp.person_id , greatest(nvl(pp.last_update_date, to_date('01-01-1900', 'DD-MM-YYYY')), pp.effective_start_date) , greatest(nvl(pbee.last_update_date, to_date('01-01-1900', 'DD-MM-YYYY')), pbee.effective_start_date) , greatest(nvl(pad.last_update_date, to_date('01-01-1900', 'DD-MM-YYYY')), pad.date_from) , greatest(nvl(pj.last_update_date, to_date('01-01-1900', 'DD-MM-YYYY')), pj.effective_start_date) , greatest(nvl(pps.last_update_date, to_date('01-01-1900','DD-MM-YYYY')), nvl(pps.final_process_date, to_date('01-01-1900','DD-MM-YYYY'))) , greatest( nvl(pps.last_update_date,to_date('01-01-1900','DD-MM-YYYY')), nvl(pjw.last_update_date,to_date('01-01-1900','DD-MM-YYYY')), nvl(hl.last_update_date, to_date('01-01-1900','DD-MM-YYYY')), nvl(asta.last_update_date,to_date('01-01-1900','DD-MM-YYYY')), nvl(astb.last_update_date,to_date('01-01-1900','DD-MM-YYYY'))) from per_addresses pad , per_all_people_f pp , pay_element_entry_values_f pbev , pay_element_types_x pbet , pay_element_links_x pbel , pay_element_entries_f pbee , hr_organization_units hou , hr_soft_coding_keyflex hs , pay_input_values_f ppiv , per_pay_bases ppb , per_time_period_types ptp , pay_payrolls_x ppr , per_ass_status_type_amends asta , per_assignment_status_types astb , pay_cost_allocations_f pcf , pay_cost_allocation_keyflex pca , hr_locations_all hl , pay_job_wc_code_usages pjw , per_all_assignments_f pj , per_periods_of_service pps WHERE pp.person_id = pad.person_id and pj.assignment_type = 'E' and pps.person_id = pp.person_id and pps.period_of_service_id = pj.period_of_service_id and ptp.period_type = ppr.period_type and ppr.payroll_id = pj.payroll_id and pad.style IN ('US','US_GLB') and hl.region_2 = pjw.state_code and hl.location_id = pj.location_id and pj.job_id = pjw.job_id and hs.soft_coding_keyflex_id = pj.soft_coding_keyflex_id and hs.segment1 = hou.organization_id and pj.primary_flag = 'Y' and pad.primary_flag = 'Y' and pp.employee_number is not null and pj.job_id is not null and trunc(sysdate) between pp.effective_start_date and pp.effective_end_date and trunc(sysdate) between pad.date_from and nvl(pad.date_to, to_date('31-12-4712', 'DD-MM-YYYY')) and pj.effective_start_date = (select max(a2.effective_start_date) from per_all_assignments_f a2 where (a2.effective_start_date <= trunc(sysdate) and a2.assignment_type = 'E' and a2.person_id = pj.person_id and a2.primary_flag = 'Y')) and pj.pay_basis_id = ppb.pay_basis_id and ppb.input_value_id = pbev.input_value_id + 0 and ppb.input_value_id = ppiv.input_value_id and ppiv.element_type_id = pbet.element_type_id and pbev.element_entry_id = pbee.element_entry_id and pbee.element_link_id = pbel.element_link_id and pbel.element_type_id = pbet.element_type_id and pbee.assignment_id = pj.assignment_id and pbee.effective_start_date = (select max(pbee2.effective_start_date) from pay_element_entries_f pbee2, pay_element_links_x pbel2 where (pbee2.effective_start_date <= trunc(sysdate) and pbee2.assignment_id = pbee.assignment_id and pbee2.element_link_id = pbel2.element_link_id and pbel2.element_type_id = pbet.element_type_id)) and pbev.effective_start_date = (select max(pbev2.effective_start_date) from pay_element_entries_f pbev2 where (pbev2.effective_start_date <= trunc(sysdate) and pbev2.element_entry_id = pbev.element_entry_id)) and ppiv.effective_start_date = (select max(ppiv2.effective_start_date) from pay_input_values_f ppiv2 where (ppiv2.effective_start_date <= trunc(sysdate) and ppiv2.input_value_id = ppiv.input_value_id)) and pcf.effective_start_date = (select max(pcf2.effective_start_date) from pay_cost_allocations_f pcf2 where (pcf2.effective_start_date <= trunc(sysdate) and pcf2.assignment_id = pcf.assignment_id)) and astb.assignment_status_type_id = pj.assignment_status_type_id and astb.assignment_status_type_id = asta.assignment_status_type_id (+) and pca.cost_allocation_keyflex_id = pcf.cost_allocation_keyflex_id and pcf.assignment_id = pj.assignment_id

View Text - HTML Formatted

SELECT PP.EMPLOYEE_NUMBER
, HOU.NAME
, PP.LAST_NAME
, PP.FIRST_NAME
, PP.MIDDLE_NAMES
, PPS.DATE_START
, PPS.FINAL_PROCESS_DATE
, NVL(HS.SEGMENT8
, PJW.WC_CODE)
, DECODE(PTP.NUMBER_PER_FISCAL_YEAR
, 52
, 'W'
, 24
, 'S'
, 26
, 'B'
, 12
, 'M'
, PTP.NUMBER_PER_FISCAL_YEAR)
, DECODE(PPB.PAY_BASIS
, 'HOURLY'
, PBEV.SCREEN_ENTRY_VALUE
, 'ANNUAL'
, NULL
, 'ERROR')
, DECODE(PPB.PAY_BASIS
, 'ANNUAL'
, PBEV.SCREEN_ENTRY_VALUE
, 'HOURLY'
, NULL
, 'ERROR')
, NVL(ASTA.PAY_SYSTEM_STATUS
, ASTB.PAY_SYSTEM_STATUS)
, PJ.NORMAL_HOURS
, PCA.SEGMENT1||PCA.SEGMENT2||PCA.SEGMENT3
, HS.SEGMENT5
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID
, GREATEST(NVL(PP.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, PP.EFFECTIVE_START_DATE)
, GREATEST(NVL(PBEE.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, PBEE.EFFECTIVE_START_DATE)
, GREATEST(NVL(PAD.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, PAD.DATE_FROM)
, GREATEST(NVL(PJ.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, PJ.EFFECTIVE_START_DATE)
, GREATEST(NVL(PPS.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, NVL(PPS.FINAL_PROCESS_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, GREATEST( NVL(PPS.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, NVL(PJW.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, NVL(HL.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, NVL(ASTA.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, NVL(ASTB.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
FROM PER_ADDRESSES PAD
, PER_ALL_PEOPLE_F PP
, PAY_ELEMENT_ENTRY_VALUES_F PBEV
, PAY_ELEMENT_TYPES_X PBET
, PAY_ELEMENT_LINKS_X PBEL
, PAY_ELEMENT_ENTRIES_F PBEE
, HR_ORGANIZATION_UNITS HOU
, HR_SOFT_CODING_KEYFLEX HS
, PAY_INPUT_VALUES_F PPIV
, PER_PAY_BASES PPB
, PER_TIME_PERIOD_TYPES PTP
, PAY_PAYROLLS_X PPR
, PER_ASS_STATUS_TYPE_AMENDS ASTA
, PER_ASSIGNMENT_STATUS_TYPES ASTB
, PAY_COST_ALLOCATIONS_F PCF
, PAY_COST_ALLOCATION_KEYFLEX PCA
, HR_LOCATIONS_ALL HL
, PAY_JOB_WC_CODE_USAGES PJW
, PER_ALL_ASSIGNMENTS_F PJ
, PER_PERIODS_OF_SERVICE PPS
WHERE PP.PERSON_ID = PAD.PERSON_ID
AND PJ.ASSIGNMENT_TYPE = 'E'
AND PPS.PERSON_ID = PP.PERSON_ID
AND PPS.PERIOD_OF_SERVICE_ID = PJ.PERIOD_OF_SERVICE_ID
AND PTP.PERIOD_TYPE = PPR.PERIOD_TYPE
AND PPR.PAYROLL_ID = PJ.PAYROLL_ID
AND PAD.STYLE IN ('US'
, 'US_GLB')
AND HL.REGION_2 = PJW.STATE_CODE
AND HL.LOCATION_ID = PJ.LOCATION_ID
AND PJ.JOB_ID = PJW.JOB_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PJ.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PJ.PRIMARY_FLAG = 'Y'
AND PAD.PRIMARY_FLAG = 'Y'
AND PP.EMPLOYEE_NUMBER IS NOT NULL
AND PJ.JOB_ID IS NOT NULL
AND TRUNC(SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PAD.DATE_FROM
AND NVL(PAD.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND PJ.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE (A2.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND A2.ASSIGNMENT_TYPE = 'E'
AND A2.PERSON_ID = PJ.PERSON_ID
AND A2.PRIMARY_FLAG = 'Y'))
AND PJ.PAY_BASIS_ID = PPB.PAY_BASIS_ID
AND PPB.INPUT_VALUE_ID = PBEV.INPUT_VALUE_ID + 0
AND PPB.INPUT_VALUE_ID = PPIV.INPUT_VALUE_ID
AND PPIV.ELEMENT_TYPE_ID = PBET.ELEMENT_TYPE_ID
AND PBEV.ELEMENT_ENTRY_ID = PBEE.ELEMENT_ENTRY_ID
AND PBEE.ELEMENT_LINK_ID = PBEL.ELEMENT_LINK_ID
AND PBEL.ELEMENT_TYPE_ID = PBET.ELEMENT_TYPE_ID
AND PBEE.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND PBEE.EFFECTIVE_START_DATE = (SELECT MAX(PBEE2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PBEE2
, PAY_ELEMENT_LINKS_X PBEL2
WHERE (PBEE2.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND PBEE2.ASSIGNMENT_ID = PBEE.ASSIGNMENT_ID
AND PBEE2.ELEMENT_LINK_ID = PBEL2.ELEMENT_LINK_ID
AND PBEL2.ELEMENT_TYPE_ID = PBET.ELEMENT_TYPE_ID))
AND PBEV.EFFECTIVE_START_DATE = (SELECT MAX(PBEV2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PBEV2
WHERE (PBEV2.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND PBEV2.ELEMENT_ENTRY_ID = PBEV.ELEMENT_ENTRY_ID))
AND PPIV.EFFECTIVE_START_DATE = (SELECT MAX(PPIV2.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PPIV2
WHERE (PPIV2.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND PPIV2.INPUT_VALUE_ID = PPIV.INPUT_VALUE_ID))
AND PCF.EFFECTIVE_START_DATE = (SELECT MAX(PCF2.EFFECTIVE_START_DATE)
FROM PAY_COST_ALLOCATIONS_F PCF2
WHERE (PCF2.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND PCF2.ASSIGNMENT_ID = PCF.ASSIGNMENT_ID))
AND ASTB.ASSIGNMENT_STATUS_TYPE_ID = PJ.ASSIGNMENT_STATUS_TYPE_ID
AND ASTB.ASSIGNMENT_STATUS_TYPE_ID = ASTA.ASSIGNMENT_STATUS_TYPE_ID (+)
AND PCA.COST_ALLOCATION_KEYFLEX_ID = PCF.COST_ALLOCATION_KEYFLEX_ID
AND PCF.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID