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_EMPLOYEE_ONLY_V

Source

View Text - Preformatted


SELECT pp.employee_number , hou.name , pp.last_name , pp.first_name , pp.middle_names , pp.suffix , pp.national_identifier , pp.sex , pp.date_of_birth , sp.spouse_date_of_birth , pad.address_line1 , pad.address_line2 , pad.town_or_city , pad.region_2 , pad.postal_code , pps.date_start , pps.final_process_date , pv1.screen_entry_value , pv2.screen_entry_value , pv3.screen_entry_value , pp.attribute1 , decode(fnd_profile.value('PER_OAB_NEW_BENEFITS_MODEL'),'Y', decode(nvl(pp.uses_tobacco_flag,'N'),'N','N','Y'), pp.attribute2) smoker_flag , decode(fnd_profile.value('PER_OAB_NEW_BENEFITS_MODEL'),'Y', pps.adjusted_svc_date, to_date(pp.attribute3,'YYYY/MM/DD HH24:MI:SS')) adjusted_service_date , pp.business_group_id , pp.person_id , pj.job_id , greatest(nvl(pp.last_update_date, to_date('01-01-1900', 'DD-MM-YYYY')), pp.effective_start_date) , greatest(nvl(pb.last_update_date, to_date('01-01-1900', 'DD-MM-YYYY')), pb.effective_start_date) , greatest(nvl(pad.last_update_date, to_date('01-01-1900', 'DD-MM-YYYY')), pad.date_from) , 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'))) , nvl(pps.last_update_date,to_date('01-01-1900','DD-MM-YYYY')) from hr_ceridian_spouse_v sp , per_addresses pad , per_all_people_f pp , pay_element_entry_values_f pv1 , pay_element_entry_values_f pv2 , pay_element_entry_values_f pv3 , pay_element_types_x pt , pay_element_links_x el , pay_element_entries_f pb , hr_organization_units hou , hr_soft_coding_keyflex hs , pay_input_values_f piv1 , pay_input_values_f piv2 , pay_input_values_f piv3 , per_all_assignments_f pj , per_periods_of_service pps WHERE pp.person_id = pad.person_id and pj.assignment_type = 'E' and pp.person_id = sp.person_id (+) and pps.person_id = pp.person_id and pps.period_of_service_id = pj.period_of_service_id and pad.style IN('US','US_GLB') 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 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.assignment_id = pj.assignment_id and a2.primary_flag = 'Y')) 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 (pv1.input_value_id + 0 = piv1.input_value_id and piv1.display_sequence = 1 and pb.element_entry_id = pv1.element_entry_id and pt.element_type_id = piv1.element_type_id and piv1.name <> 'Pay Value') and (pv2.input_value_id + 0 = piv2.input_value_id and piv2.display_sequence = 2 and pb.element_entry_id = pv2.element_entry_id and pt.element_type_id = piv2.element_type_id) and (pv3.input_value_id + 0 = piv3.input_value_id and piv3.display_sequence = 3 and pb.element_entry_id = pv3.element_entry_id and pt.element_type_id = piv3.element_type_id) and pt.element_name = 'Ceridian policy codes' 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(sysdate) 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 pv1.effective_start_date = (select max(pv12.effective_start_date) from pay_element_entry_values_f pv12 where (pv12.effective_start_date <= trunc(sysdate) and pv12.element_entry_value_id = pv1.element_entry_value_id)) and pv2.effective_start_date = (select max(pv22.effective_start_date) from pay_element_entry_values_f pv22 where (pv22.effective_start_date <= trunc(sysdate) and pv22.element_entry_value_id = pv2.element_entry_value_id)) and pv3.effective_start_date = (select max(pv32.effective_start_date) from pay_element_entry_values_f pv32 where (pv32.effective_start_date <= trunc(sysdate) and pv32.element_entry_value_id = pv3.element_entry_value_id)) and piv1.effective_start_date = (select max(piv12.effective_start_date) from pay_input_values_f piv12 where (piv12.effective_start_date <= trunc(sysdate) 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(sysdate) 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(sysdate) and piv32.input_value_id = piv3.input_value_id))

View Text - HTML Formatted

SELECT PP.EMPLOYEE_NUMBER
, HOU.NAME
, PP.LAST_NAME
, PP.FIRST_NAME
, PP.MIDDLE_NAMES
, PP.SUFFIX
, PP.NATIONAL_IDENTIFIER
, PP.SEX
, PP.DATE_OF_BIRTH
, SP.SPOUSE_DATE_OF_BIRTH
, PAD.ADDRESS_LINE1
, PAD.ADDRESS_LINE2
, PAD.TOWN_OR_CITY
, PAD.REGION_2
, PAD.POSTAL_CODE
, PPS.DATE_START
, PPS.FINAL_PROCESS_DATE
, PV1.SCREEN_ENTRY_VALUE
, PV2.SCREEN_ENTRY_VALUE
, PV3.SCREEN_ENTRY_VALUE
, PP.ATTRIBUTE1
, DECODE(FND_PROFILE.VALUE('PER_OAB_NEW_BENEFITS_MODEL')
, 'Y'
, DECODE(NVL(PP.USES_TOBACCO_FLAG
, 'N')
, 'N'
, 'N'
, 'Y')
, PP.ATTRIBUTE2) SMOKER_FLAG
, DECODE(FND_PROFILE.VALUE('PER_OAB_NEW_BENEFITS_MODEL')
, 'Y'
, PPS.ADJUSTED_SVC_DATE
, TO_DATE(PP.ATTRIBUTE3
, 'YYYY/MM/DD HH24:MI:SS')) ADJUSTED_SERVICE_DATE
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID
, PJ.JOB_ID
, GREATEST(NVL(PP.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, PP.EFFECTIVE_START_DATE)
, GREATEST(NVL(PB.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, PB.EFFECTIVE_START_DATE)
, GREATEST(NVL(PAD.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
, PAD.DATE_FROM)
, 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')))
, NVL(PPS.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY'))
FROM HR_CERIDIAN_SPOUSE_V SP
, PER_ADDRESSES PAD
, PER_ALL_PEOPLE_F PP
, PAY_ELEMENT_ENTRY_VALUES_F PV1
, PAY_ELEMENT_ENTRY_VALUES_F PV2
, PAY_ELEMENT_ENTRY_VALUES_F PV3
, PAY_ELEMENT_TYPES_X PT
, PAY_ELEMENT_LINKS_X EL
, PAY_ELEMENT_ENTRIES_F PB
, HR_ORGANIZATION_UNITS HOU
, HR_SOFT_CODING_KEYFLEX HS
, PAY_INPUT_VALUES_F PIV1
, PAY_INPUT_VALUES_F PIV2
, PAY_INPUT_VALUES_F PIV3
, PER_ALL_ASSIGNMENTS_F PJ
, PER_PERIODS_OF_SERVICE PPS
WHERE PP.PERSON_ID = PAD.PERSON_ID
AND PJ.ASSIGNMENT_TYPE = 'E'
AND PP.PERSON_ID = SP.PERSON_ID (+)
AND PPS.PERSON_ID = PP.PERSON_ID
AND PPS.PERIOD_OF_SERVICE_ID = PJ.PERIOD_OF_SERVICE_ID
AND PAD.STYLE IN('US'
, 'US_GLB')
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 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.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND A2.PRIMARY_FLAG = 'Y'))
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 (PV1.INPUT_VALUE_ID + 0 = PIV1.INPUT_VALUE_ID
AND PIV1.DISPLAY_SEQUENCE = 1
AND PB.ELEMENT_ENTRY_ID = PV1.ELEMENT_ENTRY_ID
AND PT.ELEMENT_TYPE_ID = PIV1.ELEMENT_TYPE_ID
AND PIV1.NAME <> 'PAY VALUE')
AND (PV2.INPUT_VALUE_ID + 0 = PIV2.INPUT_VALUE_ID
AND PIV2.DISPLAY_SEQUENCE = 2
AND PB.ELEMENT_ENTRY_ID = PV2.ELEMENT_ENTRY_ID
AND PT.ELEMENT_TYPE_ID = PIV2.ELEMENT_TYPE_ID)
AND (PV3.INPUT_VALUE_ID + 0 = PIV3.INPUT_VALUE_ID
AND PIV3.DISPLAY_SEQUENCE = 3
AND PB.ELEMENT_ENTRY_ID = PV3.ELEMENT_ENTRY_ID
AND PT.ELEMENT_TYPE_ID = PIV3.ELEMENT_TYPE_ID)
AND PT.ELEMENT_NAME = 'CERIDIAN POLICY CODES'
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(SYSDATE)
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 PV1.EFFECTIVE_START_DATE = (SELECT MAX(PV12.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRY_VALUES_F PV12
WHERE (PV12.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND PV12.ELEMENT_ENTRY_VALUE_ID = PV1.ELEMENT_ENTRY_VALUE_ID))
AND PV2.EFFECTIVE_START_DATE = (SELECT MAX(PV22.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRY_VALUES_F PV22
WHERE (PV22.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND PV22.ELEMENT_ENTRY_VALUE_ID = PV2.ELEMENT_ENTRY_VALUE_ID))
AND PV3.EFFECTIVE_START_DATE = (SELECT MAX(PV32.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRY_VALUES_F PV32
WHERE (PV32.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
AND PV32.ELEMENT_ENTRY_VALUE_ID = PV3.ELEMENT_ENTRY_VALUE_ID))
AND PIV1.EFFECTIVE_START_DATE = (SELECT MAX(PIV12.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV12
WHERE (PIV12.EFFECTIVE_START_DATE <= TRUNC(SYSDATE)
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(SYSDATE)
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(SYSDATE)
AND PIV32.INPUT_VALUE_ID = PIV3.INPUT_VALUE_ID))