eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.HR_CERIDIAN_500_ASSIGNMENT_V

Source

View Text - Preformatted


SELECT /*+ ORDERED NO_INDEX(pbev PAY_ELEMENT_ENTRY_VALUES_F_N1) NO_INDEX(pv1 PAY_ELEMENT_ENTRY_VALUES_F_N1) NO_INDEX(pv2 PAY_ELEMENT_ENTRY_VALUES_F_N1) NO_INDEX(pv3 PAY_ELEMENT_ENTRY_VALUES_F_N1) NO_INDEX(pv4 PAY_ELEMENT_ENTRY_VALUES_F_N1) */ pp.employee_number, pj.assignment_number, pj.primary_flag, pp.national_identifier, pb.effective_start_date, DECODE(TRUNC(hr_ceridian.get_cer_extract_date), GREATEST(TRUNC (hr_ceridian.get_cer_extract_date), pb.effective_end_date), pb.effective_end_date, TO_DATE(NULL)), SUBSTR (DECODE (piv1.hot_default_flag, 'N', pv1.screen_entry_value, DECODE (pv1.screen_entry_value, NULL, hr_pay_interface_pkg.get_hot_default (piv1.input_value_id, el.element_link_id ), pv1.screen_entry_value ) ), 1, 60 ), SUBSTR (DECODE (piv2.hot_default_flag, 'N', pv2.screen_entry_value, DECODE (pv2.screen_entry_value, NULL, hr_pay_interface_pkg.get_hot_default (piv2.input_value_id, el.element_link_id ), pv2.screen_entry_value ) ), 1, 60 ), SUBSTR (DECODE (piv3.hot_default_flag, 'N', pv3.screen_entry_value, DECODE (pv3.screen_entry_value, NULL, hr_pay_interface_pkg.get_hot_default (piv3.input_value_id, el.element_link_id ), pv3.screen_entry_value ) ), 1, 60 ), SUBSTR (DECODE (piv4.hot_default_flag, 'N', pv4.screen_entry_value, DECODE (pv4.screen_entry_value, NULL, hr_pay_interface_pkg.get_hot_default (piv4.input_value_id, el.element_link_id ), pv4.screen_entry_value ) ), 1, 60 ), ' ' worksite_number, ' ' worksite_state_code, DECODE (ppb.pay_basis, 'HOURLY', '4', 'ANNUAL', '2', 'MONTHLY', '2', 'PERIOD', '2', ppb.pay_basis ) rate_code, DECODE (ppb.pay_basis, 'HOURLY', pbev.screen_entry_value, 'ANNUAL', TO_CHAR (TO_NUMBER (pbev.screen_entry_value) / ptp.number_per_fiscal_year ), 'MONTHLY', TO_CHAR (TO_NUMBER (pbev.screen_entry_value) * 12 / ptp.number_per_fiscal_year ), 'PERIOD', pbev.screen_entry_value ) base_rate, hou.name, 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, NULL) hourly_rate, DECODE (ppb.pay_basis, 'HOURLY', NULL, 'ANNUAL', pbev.screen_entry_value, 'MONTHLY', TO_CHAR (TO_NUMBER (pbev.screen_entry_value) * 12), 'PERIOD', TO_CHAR (TO_NUMBER (pbev.screen_entry_value) * ptp.number_per_fiscal_year ), 'ERROR' ) annual_salary, DECODE (pps.final_process_date, NULL, NVL (asta.pay_system_status, astb.pay_system_status), DECODE (GREATEST (pps.final_process_date, TRUNC (hr_ceridian.get_cer_extract_date)), pps.final_process_date, NVL (asta.pay_system_status, astb.pay_system_status ), 'D' ) ), pj.normal_hours, hs.segment5, pj.assignment_id, pp.business_group_id, pj.payroll_id, pp.person_id, ppp.change_date, greatest( nvl(pp.last_update_date,to_date('01-01-1900','DD-MM-YYYY')), decode( greatest(trunc(HR_CERIDIAN.get_cer_extract_date), nvl(pj.effective_end_date, to_date('31-12-4712','DD-MM-YYYY'))), trunc(HR_CERIDIAN.get_cer_extract_date), greatest(nvl(pj.effective_end_date, to_date('31-12-4712','DD-MM-YYYY')), nvl(pj.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))), nvl(pj.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))), nvl(pj.effective_start_date,to_date('01-01-1900','DD-MM-YYYY')), decode( greatest(trunc(HR_CERIDIAN.get_cer_extract_date), nvl(pbee.effective_end_date, to_date('31-12-4712','DD-MM-YYYY'))), trunc(HR_CERIDIAN.get_cer_extract_date), greatest(nvl(pbee.effective_end_date, to_date('31-12-4712','DD-MM-YYYY')), nvl(pbee.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))), nvl(pbee.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))), nvl(pbee.effective_start_date,to_date('01-01-1900','DD-MM-YYYY')), decode( greatest(trunc(HR_CERIDIAN.get_cer_extract_date), nvl(pbee.effective_end_date, to_date('31-12-4712','DD-MM-YYYY'))), trunc(HR_CERIDIAN.get_cer_extract_date), greatest(nvl(pbee.effective_end_date, to_date('31-12-4712','DD-MM-YYYY')), nvl(pbee.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))), nvl(pbee.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))), nvl(pbee.effective_start_date,to_date('01-01-1900','DD-MM-YYYY')), 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(astb.last_update_date,to_date('01-01-1900','DD-MM-YYYY')), nvl(asta.last_update_date,to_date('01-01-1900','DD-MM-YYYY'))) FROM pay_element_types_f pt, pay_input_values_f piv1, pay_input_values_f piv2, pay_input_values_f piv3, pay_input_values_f piv4, pay_element_links_f el, pay_element_entries_f pb, per_all_assignments_f pj, pay_payrolls_f ppr, per_time_period_types ptp, pay_job_wc_code_usages pjw, per_assignment_status_types astb, per_ass_status_type_amends asta, per_pay_bases ppb, per_pay_proposals ppp, hr_soft_coding_keyflex hs, pay_element_entry_values_f pv1, pay_element_entry_values_f pv2, pay_element_entry_values_f pv3, pay_element_entry_values_f pv4, hr_locations_all hl, pay_input_values_f ppiv, pay_element_types_f pbet, pay_element_links_f pbel, pay_element_entries_f pbee, pay_element_entry_values_f pbev, per_periods_of_service pps, per_all_people_f pp, hr_organization_units hou WHERE TRUNC(SYSDATE) BETWEEN pbet.effective_start_date AND pbet.effective_end_date AND TRUNC(SYSDATE) BETWEEN pbel.effective_start_date AND pbel.effective_end_date AND TRUNC(SYSDATE) BETWEEN ppr.effective_start_date AND ppr.effective_end_date 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 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 pp.employee_number IS NOT NULL AND pj.job_id IS NOT NULL AND TRUNC(hr_ceridian.get_cer_extract_date) BETWEEN pp.effective_start_date AND pp.effective_end_date AND pj.effective_start_date = (SELECT MAX(a2.effective_start_date) FROM per_all_assignments_f a2 WHERE a2.effective_start_date <= TRUNC(hr_ceridian.get_cer_extract_date) AND a2.assignment_type = 'E' AND a2.person_id = pj.person_id AND a2.assignment_id = pj.assignment_id AND a2.primary_flag = 'Y') AND ppp.assignment_id = pj.assignment_id AND ppp.change_date = (SELECT MAX(change_date) FROM per_pay_proposals pp2 WHERE pp2.assignment_id = pj.assignment_id AND change_date <= TRUNC(hr_ceridian.get_cer_extract_date)) AND pj.pay_basis_id = ppb.pay_basis_id AND ppb.input_value_id = pbev.input_value_id 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 pb.assignment_id = pj.assignment_id AND pb.element_link_id = el.element_link_id AND el.element_type_id = pt.element_type_id AND pt.element_name = 'Ceridian employee codes' AND (piv1.input_value_id = pv1.input_value_id AND piv1.display_sequence = 1 AND pv1.element_entry_id = pb.element_entry_id AND pv1.effective_start_date = pb.effective_start_date AND pv1.effective_end_date = pb.effective_end_date AND piv1.element_type_id = pt.element_type_id AND piv1.name <> 'Pay Value') AND (pv2.input_value_id = piv2.input_value_id AND piv2.display_sequence = 2 AND pb.element_entry_id = pv2.element_entry_id AND pv2.effective_start_date = pb.effective_start_date AND pv2.effective_end_date = pb.effective_end_date AND pt.element_type_id = piv2.element_type_id) AND (pv3.input_value_id = piv3.input_value_id AND piv3.display_sequence = 3 AND pb.element_entry_id = pv3.element_entry_id AND pv3.effective_start_date = pb.effective_start_date AND pv3.effective_end_date = pb.effective_end_date AND pt.element_type_id = piv3.element_type_id) AND (pv4.input_value_id = piv4.input_value_id AND piv4.display_sequence = 4 AND pb.element_entry_id = pv4.element_entry_id AND pv4.effective_start_date = pb.effective_start_date AND pv4.effective_end_date = pb.effective_end_date AND pt.element_type_id = piv4.element_type_id) AND pb.effective_start_date = (select max(pb2.effective_start_date) from pay_element_entries_f pb2 , pay_element_links_x el2 where (pb2.effective_start_date <= trunc(hr_ceridian.get_cer_extract_date) and pb2.element_link_id = el2.element_link_id and el2.element_type_id = pt.element_type_id and pb2.assignment_id = pb.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(hr_ceridian.get_cer_extract_date) and pbee2.element_link_id = pbel2.element_link_id and pbel2.element_type_id = pbet.element_type_id and pbee2.assignment_id = pbee.assignment_id )) AND pt.effective_start_date = (SELECT MAX(pt2.effective_start_date) FROM pay_element_types_f pt2 WHERE pt2.effective_start_date <= TRUNC(hr_ceridian.get_cer_extract_date) AND pt2.element_type_id = pt.element_type_id) AND el.effective_start_date = (SELECT MAX(el2.effective_start_date) FROM pay_element_links_f el2 WHERE el2.effective_start_date <= TRUNC(hr_ceridian.get_cer_extract_date) AND el2.element_link_id = el.element_link_id) AND piv1.effective_start_date = (SELECT MAX(piv12.effective_start_date) FROM pay_input_values_f piv12 WHERE piv12.effective_start_date <= TRUNC(hr_ceridian.get_cer_extract_date) AND piv12.input_value_id = piv1.input_value_id) AND piv2.effective_start_date = (SELECT MAX(piv22.effective_start_date) FROM pay_input_values_f piv22 WHERE piv22.effective_start_date <= TRUNC(hr_ceridian.get_cer_extract_date) AND piv22.input_value_id = piv2.input_value_id) AND piv3.effective_start_date = (SELECT MAX(piv32.effective_start_date) FROM pay_input_values_f piv32 WHERE piv32.effective_start_date <= TRUNC(hr_ceridian.get_cer_extract_date) AND piv32.input_value_id = piv3.input_value_id) AND piv4.effective_start_date = (SELECT MAX(piv42.effective_start_date) FROM pay_input_values_f piv42 WHERE piv42.effective_start_date <= TRUNC(hr_ceridian.get_cer_extract_date) AND piv42.input_value_id = piv4.input_value_id) AND pbev.effective_start_date = (SELECT MAX(pbev2.effective_start_date) FROM pay_element_entries_f pbev2 WHERE pbev2.effective_start_date <= TRUNC(hr_ceridian.get_cer_extract_date) 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(hr_ceridian.get_cer_extract_date) AND ppiv2.input_value_id = ppiv.input_value_id) AND pps.date_start = (SELECT MAX(ps.date_start) FROM per_periods_of_service ps WHERE ps.date_start <= TRUNC(hr_ceridian.get_cer_extract_date) AND ps.person_id = pp.person_id) AND astb.assignment_status_type_id = pj.assignment_status_type_id AND astb.assignment_status_type_id = asta.assignment_status_type_id (+) AND astb.business_group_id = asta.business_group_id(+)

View Text - HTML Formatted

SELECT /*+ ORDERED NO_INDEX(PBEV PAY_ELEMENT_ENTRY_VALUES_F_N1) NO_INDEX(PV1 PAY_ELEMENT_ENTRY_VALUES_F_N1) NO_INDEX(PV2 PAY_ELEMENT_ENTRY_VALUES_F_N1) NO_INDEX(PV3 PAY_ELEMENT_ENTRY_VALUES_F_N1) NO_INDEX(PV4 PAY_ELEMENT_ENTRY_VALUES_F_N1) */ PP.EMPLOYEE_NUMBER
, PJ.ASSIGNMENT_NUMBER
, PJ.PRIMARY_FLAG
, PP.NATIONAL_IDENTIFIER
, PB.EFFECTIVE_START_DATE
, DECODE(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(TRUNC (HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, PB.EFFECTIVE_END_DATE)
, PB.EFFECTIVE_END_DATE
, TO_DATE(NULL))
, SUBSTR (DECODE (PIV1.HOT_DEFAULT_FLAG
, 'N'
, PV1.SCREEN_ENTRY_VALUE
, DECODE (PV1.SCREEN_ENTRY_VALUE
, NULL
, HR_PAY_INTERFACE_PKG.GET_HOT_DEFAULT (PIV1.INPUT_VALUE_ID
, EL.ELEMENT_LINK_ID )
, PV1.SCREEN_ENTRY_VALUE ) )
, 1
, 60 )
, SUBSTR (DECODE (PIV2.HOT_DEFAULT_FLAG
, 'N'
, PV2.SCREEN_ENTRY_VALUE
, DECODE (PV2.SCREEN_ENTRY_VALUE
, NULL
, HR_PAY_INTERFACE_PKG.GET_HOT_DEFAULT (PIV2.INPUT_VALUE_ID
, EL.ELEMENT_LINK_ID )
, PV2.SCREEN_ENTRY_VALUE ) )
, 1
, 60 )
, SUBSTR (DECODE (PIV3.HOT_DEFAULT_FLAG
, 'N'
, PV3.SCREEN_ENTRY_VALUE
, DECODE (PV3.SCREEN_ENTRY_VALUE
, NULL
, HR_PAY_INTERFACE_PKG.GET_HOT_DEFAULT (PIV3.INPUT_VALUE_ID
, EL.ELEMENT_LINK_ID )
, PV3.SCREEN_ENTRY_VALUE ) )
, 1
, 60 )
, SUBSTR (DECODE (PIV4.HOT_DEFAULT_FLAG
, 'N'
, PV4.SCREEN_ENTRY_VALUE
, DECODE (PV4.SCREEN_ENTRY_VALUE
, NULL
, HR_PAY_INTERFACE_PKG.GET_HOT_DEFAULT (PIV4.INPUT_VALUE_ID
, EL.ELEMENT_LINK_ID )
, PV4.SCREEN_ENTRY_VALUE ) )
, 1
, 60 )
, ' ' WORKSITE_NUMBER
, ' ' WORKSITE_STATE_CODE
, DECODE (PPB.PAY_BASIS
, 'HOURLY'
, '4'
, 'ANNUAL'
, '2'
, 'MONTHLY'
, '2'
, 'PERIOD'
, '2'
, PPB.PAY_BASIS ) RATE_CODE
, DECODE (PPB.PAY_BASIS
, 'HOURLY'
, PBEV.SCREEN_ENTRY_VALUE
, 'ANNUAL'
, TO_CHAR (TO_NUMBER (PBEV.SCREEN_ENTRY_VALUE) / PTP.NUMBER_PER_FISCAL_YEAR )
, 'MONTHLY'
, TO_CHAR (TO_NUMBER (PBEV.SCREEN_ENTRY_VALUE) * 12 / PTP.NUMBER_PER_FISCAL_YEAR )
, 'PERIOD'
, PBEV.SCREEN_ENTRY_VALUE ) BASE_RATE
, HOU.NAME
, 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
, NULL) HOURLY_RATE
, DECODE (PPB.PAY_BASIS
, 'HOURLY'
, NULL
, 'ANNUAL'
, PBEV.SCREEN_ENTRY_VALUE
, 'MONTHLY'
, TO_CHAR (TO_NUMBER (PBEV.SCREEN_ENTRY_VALUE) * 12)
, 'PERIOD'
, TO_CHAR (TO_NUMBER (PBEV.SCREEN_ENTRY_VALUE) * PTP.NUMBER_PER_FISCAL_YEAR )
, 'ERROR' ) ANNUAL_SALARY
, DECODE (PPS.FINAL_PROCESS_DATE
, NULL
, NVL (ASTA.PAY_SYSTEM_STATUS
, ASTB.PAY_SYSTEM_STATUS)
, DECODE (GREATEST (PPS.FINAL_PROCESS_DATE
, TRUNC (HR_CERIDIAN.GET_CER_EXTRACT_DATE))
, PPS.FINAL_PROCESS_DATE
, NVL (ASTA.PAY_SYSTEM_STATUS
, ASTB.PAY_SYSTEM_STATUS )
, 'D' ) )
, PJ.NORMAL_HOURS
, HS.SEGMENT5
, PJ.ASSIGNMENT_ID
, PP.BUSINESS_GROUP_ID
, PJ.PAYROLL_ID
, PP.PERSON_ID
, PPP.CHANGE_DATE
, GREATEST( NVL(PP.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, NVL(PJ.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL(PJ.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
, NVL(PJ.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PJ.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PJ.EFFECTIVE_START_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, NVL(PBEE.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL(PBEE.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
, NVL(PBEE.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PBEE.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PBEE.EFFECTIVE_START_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, NVL(PBEE.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL(PBEE.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
, NVL(PBEE.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PBEE.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PBEE.EFFECTIVE_START_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, 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(ASTB.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, NVL(ASTA.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
FROM PAY_ELEMENT_TYPES_F PT
, PAY_INPUT_VALUES_F PIV1
, PAY_INPUT_VALUES_F PIV2
, PAY_INPUT_VALUES_F PIV3
, PAY_INPUT_VALUES_F PIV4
, PAY_ELEMENT_LINKS_F EL
, PAY_ELEMENT_ENTRIES_F PB
, PER_ALL_ASSIGNMENTS_F PJ
, PAY_PAYROLLS_F PPR
, PER_TIME_PERIOD_TYPES PTP
, PAY_JOB_WC_CODE_USAGES PJW
, PER_ASSIGNMENT_STATUS_TYPES ASTB
, PER_ASS_STATUS_TYPE_AMENDS ASTA
, PER_PAY_BASES PPB
, PER_PAY_PROPOSALS PPP
, HR_SOFT_CODING_KEYFLEX HS
, PAY_ELEMENT_ENTRY_VALUES_F PV1
, PAY_ELEMENT_ENTRY_VALUES_F PV2
, PAY_ELEMENT_ENTRY_VALUES_F PV3
, PAY_ELEMENT_ENTRY_VALUES_F PV4
, HR_LOCATIONS_ALL HL
, PAY_INPUT_VALUES_F PPIV
, PAY_ELEMENT_TYPES_F PBET
, PAY_ELEMENT_LINKS_F PBEL
, PAY_ELEMENT_ENTRIES_F PBEE
, PAY_ELEMENT_ENTRY_VALUES_F PBEV
, PER_PERIODS_OF_SERVICE PPS
, PER_ALL_PEOPLE_F PP
, HR_ORGANIZATION_UNITS HOU
WHERE TRUNC(SYSDATE) BETWEEN PBET.EFFECTIVE_START_DATE
AND PBET.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PBEL.EFFECTIVE_START_DATE
AND PBEL.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PPR.EFFECTIVE_START_DATE
AND PPR.EFFECTIVE_END_DATE
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 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 PP.EMPLOYEE_NUMBER IS NOT NULL
AND PJ.JOB_ID IS NOT NULL
AND TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND PJ.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE A2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND A2.ASSIGNMENT_TYPE = 'E'
AND A2.PERSON_ID = PJ.PERSON_ID
AND A2.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND A2.PRIMARY_FLAG = 'Y')
AND PPP.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND PPP.CHANGE_DATE = (SELECT MAX(CHANGE_DATE)
FROM PER_PAY_PROPOSALS PP2
WHERE PP2.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND CHANGE_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE))
AND PJ.PAY_BASIS_ID = PPB.PAY_BASIS_ID
AND PPB.INPUT_VALUE_ID = PBEV.INPUT_VALUE_ID
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 PB.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND PB.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID
AND EL.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID
AND PT.ELEMENT_NAME = 'CERIDIAN EMPLOYEE CODES'
AND (PIV1.INPUT_VALUE_ID = PV1.INPUT_VALUE_ID
AND PIV1.DISPLAY_SEQUENCE = 1
AND PV1.ELEMENT_ENTRY_ID = PB.ELEMENT_ENTRY_ID
AND PV1.EFFECTIVE_START_DATE = PB.EFFECTIVE_START_DATE
AND PV1.EFFECTIVE_END_DATE = PB.EFFECTIVE_END_DATE
AND PIV1.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID
AND PIV1.NAME <> 'PAY VALUE')
AND (PV2.INPUT_VALUE_ID = PIV2.INPUT_VALUE_ID
AND PIV2.DISPLAY_SEQUENCE = 2
AND PB.ELEMENT_ENTRY_ID = PV2.ELEMENT_ENTRY_ID
AND PV2.EFFECTIVE_START_DATE = PB.EFFECTIVE_START_DATE
AND PV2.EFFECTIVE_END_DATE = PB.EFFECTIVE_END_DATE
AND PT.ELEMENT_TYPE_ID = PIV2.ELEMENT_TYPE_ID)
AND (PV3.INPUT_VALUE_ID = PIV3.INPUT_VALUE_ID
AND PIV3.DISPLAY_SEQUENCE = 3
AND PB.ELEMENT_ENTRY_ID = PV3.ELEMENT_ENTRY_ID
AND PV3.EFFECTIVE_START_DATE = PB.EFFECTIVE_START_DATE
AND PV3.EFFECTIVE_END_DATE = PB.EFFECTIVE_END_DATE
AND PT.ELEMENT_TYPE_ID = PIV3.ELEMENT_TYPE_ID)
AND (PV4.INPUT_VALUE_ID = PIV4.INPUT_VALUE_ID
AND PIV4.DISPLAY_SEQUENCE = 4
AND PB.ELEMENT_ENTRY_ID = PV4.ELEMENT_ENTRY_ID
AND PV4.EFFECTIVE_START_DATE = PB.EFFECTIVE_START_DATE
AND PV4.EFFECTIVE_END_DATE = PB.EFFECTIVE_END_DATE
AND PT.ELEMENT_TYPE_ID = PIV4.ELEMENT_TYPE_ID)
AND PB.EFFECTIVE_START_DATE = (SELECT MAX(PB2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PB2
, PAY_ELEMENT_LINKS_X EL2
WHERE (PB2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PB2.ELEMENT_LINK_ID = EL2.ELEMENT_LINK_ID
AND EL2.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID
AND PB2.ASSIGNMENT_ID = PB.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(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PBEE2.ELEMENT_LINK_ID = PBEL2.ELEMENT_LINK_ID
AND PBEL2.ELEMENT_TYPE_ID = PBET.ELEMENT_TYPE_ID
AND PBEE2.ASSIGNMENT_ID = PBEE.ASSIGNMENT_ID ))
AND PT.EFFECTIVE_START_DATE = (SELECT MAX(PT2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_TYPES_F PT2
WHERE PT2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PT2.ELEMENT_TYPE_ID = PT.ELEMENT_TYPE_ID)
AND EL.EFFECTIVE_START_DATE = (SELECT MAX(EL2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_LINKS_F EL2
WHERE EL2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND EL2.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID)
AND PIV1.EFFECTIVE_START_DATE = (SELECT MAX(PIV12.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV12
WHERE PIV12.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PIV12.INPUT_VALUE_ID = PIV1.INPUT_VALUE_ID)
AND PIV2.EFFECTIVE_START_DATE = (SELECT MAX(PIV22.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV22
WHERE PIV22.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PIV22.INPUT_VALUE_ID = PIV2.INPUT_VALUE_ID)
AND PIV3.EFFECTIVE_START_DATE = (SELECT MAX(PIV32.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV32
WHERE PIV32.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PIV32.INPUT_VALUE_ID = PIV3.INPUT_VALUE_ID)
AND PIV4.EFFECTIVE_START_DATE = (SELECT MAX(PIV42.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV42
WHERE PIV42.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PIV42.INPUT_VALUE_ID = PIV4.INPUT_VALUE_ID)
AND PBEV.EFFECTIVE_START_DATE = (SELECT MAX(PBEV2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PBEV2
WHERE PBEV2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
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(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PPIV2.INPUT_VALUE_ID = PPIV.INPUT_VALUE_ID)
AND PPS.DATE_START = (SELECT MAX(PS.DATE_START)
FROM PER_PERIODS_OF_SERVICE PS
WHERE PS.DATE_START <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PS.PERSON_ID = PP.PERSON_ID)
AND ASTB.ASSIGNMENT_STATUS_TYPE_ID = PJ.ASSIGNMENT_STATUS_TYPE_ID
AND ASTB.ASSIGNMENT_STATUS_TYPE_ID = ASTA.ASSIGNMENT_STATUS_TYPE_ID (+)
AND ASTB.BUSINESS_GROUP_ID = ASTA.BUSINESS_GROUP_ID(+)