VIEW: APPS.HR_CERIDIAN_500_EMPLOYEE_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.add_information17 , pad.postal_code , pad.date_from , decode(trunc(hr_ceridian.get_cer_extract_date), greatest(trunc(hr_ceridian.get_cer_extract_date), pad.date_to), pad.date_to, to_date(NULL)) , pps.leaving_reason , pp.date_of_death , pps.date_start , pps.final_process_date , 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 , pj.payroll_id , pp.person_id , greatest( decode( greatest(trunc(hr_ceridian.get_cer_extract_date), nvl(pad.date_to, to_date('31-12-4712','DD-MM-YYYY'))), trunc(hr_ceridian.get_cer_extract_date), greatest(nvl(pad.date_to, to_date('31-12-4712','DD-MM-YYYY')), nvl(pad.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))), nvl(pad.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))), nvl(pad.date_from,to_date('1900/01/01','YYYY/MM/DD')), nvl(pps.last_update_date,to_date('1900/01/01','YYYY/MM/DD')), nvl(pps.final_process_date,to_date('1900/01/01','YYYY/MM/DD')), nvl(pp.last_update_date,to_date('1900/01/01', 'YYYY/MM/DD')), nvl(pp.effective_start_date,to_date('1900/01/01', 'YYYY/MM/DD')), nvl(pps.last_update_date,to_date('1900/01/01','YYYY/MM/DD'))) from hr_ceridian_500_spouse_v sp , per_addresses pad , per_all_people_f pp , hr_all_organization_units hou , hr_soft_coding_keyflex hs , 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(hr_ceridian.get_cer_extract_date) between pp.effective_start_date and pp.effective_end_date and trunc(hr_ceridian.get_cer_extract_date) between pad.date_from and nvl(pad.date_to, to_date('4712/12/31', 'YYYY/MM/DD')) 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.person_id = pj.person_id and a2.assignment_id = pj.assignment_id and a2.assignment_type = 'E' and a2.primary_flag = 'Y') ) 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))

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.ADD_INFORMATION17
, PAD.POSTAL_CODE
, PAD.DATE_FROM
, DECODE(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, PAD.DATE_TO)
, PAD.DATE_TO
, TO_DATE(NULL))
, PPS.LEAVING_REASON
, PP.DATE_OF_DEATH
, PPS.DATE_START
, PPS.FINAL_PROCESS_DATE
, 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
, PJ.PAYROLL_ID
, PP.PERSON_ID
, GREATEST( DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, NVL(PAD.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL(PAD.DATE_TO
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
, NVL(PAD.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PAD.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PAD.DATE_FROM
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PPS.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PPS.FINAL_PROCESS_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PP.EFFECTIVE_START_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PPS.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
FROM HR_CERIDIAN_500_SPOUSE_V SP
, PER_ADDRESSES PAD
, PER_ALL_PEOPLE_F PP
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_SOFT_CODING_KEYFLEX HS
, 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(HR_CERIDIAN.GET_CER_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE) BETWEEN PAD.DATE_FROM
AND NVL(PAD.DATE_TO
, TO_DATE('4712/12/31'
, 'YYYY/MM/DD'))
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.PERSON_ID = PJ.PERSON_ID
AND A2.ASSIGNMENT_ID = PJ.ASSIGNMENT_ID
AND A2.ASSIGNMENT_TYPE = 'E'
AND A2.PRIMARY_FLAG = 'Y') )
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))