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_MISC_V

Source

View Text - Preformatted


SELECT pasg.assignment_number , pasg.primary_flag , pp.national_identifier , nvl(pcf1.segment1, pcf2.segment1) , nvl(pcf1.segment2, pcf2.segment2) , nvl(pcf1.segment3, pcf2.segment3) , nvl(pcf1.segment4, pcf2.segment4) , nvl(pcf1.segment5, pcf2.segment5) , nvl(pcf1.segment6, pcf2.segment6) , nvl(pcf1.segment7, pcf2.segment7) , nvl(pcf1.segment8, pcf2.segment8) , nvl(pcf1.segment9, pcf2.segment9) , nvl(pcf1.segment10, pcf2.segment10) , pp.employee_number , hou.name , hl.location_code , pasg.assignment_id , pp.business_group_id , pasg.payroll_id , pasg.organization_id , pp.person_id , greatest ( decode( greatest(trunc(hr_ceridian.get_cer_extract_date), nvl(pcf1.effective_end_date, to_date('31-12-4712','DD-MM-YYYY'))), trunc(hr_ceridian.get_cer_extract_date), greatest(nvl(pcf1.effective_end_date, to_date('31-12-4712','DD-MM-YYYY')), nvl(pcf1.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))), nvl(pcf1.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))) , nvl(pcf1.effective_start_date,to_date('1900/01/01','YYYY/MM/DD'))) cost_last_update_date , greatest ( greatest(nvl(pp.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), pp.effective_start_date) , greatest(nvl(pasg.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), pasg.effective_start_date) , greatest(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'))) , greatest ( decode( greatest(trunc(hr_ceridian.get_cer_extract_date), nvl(pcf1.effective_end_date, to_date('31-12-4712','DD-MM-YYYY'))), trunc(hr_ceridian.get_cer_extract_date), greatest(nvl(pcf1.effective_end_date, to_date('31-12-4712','DD-MM-YYYY')), nvl(pcf1.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))), nvl(pcf1.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))), nvl(pcf1.effective_start_date,to_date('1900/01/01','YYYY/MM/DD'))) ) last_update_date from per_all_people_f pp , hr_all_organization_units hou , hr_soft_coding_keyflex hs , (select pcf.assignment_id assignment_id, pcf.effective_start_date effective_start_date, pcf.effective_end_date effective_end_date, pcf.last_update_date last_update_date, pca.segment1 segment1, pca.segment2 segment2, pca.segment3 segment3, pca.segment4 segment4, pca.segment5 segment5, pca.segment6 segment6, pca.segment7 segment7, pca.segment8 segment8, pca.segment9 segment9, pca.segment10 segment10 from pay_cost_allocations_f pcf, pay_cost_allocation_keyflex pca where pcf.cost_allocation_keyflex_id = pca.cost_allocation_keyflex_id and pcf.effective_start_date=(select max(pcf2.effective_start_date) from pay_cost_allocations_f pcf2 where pcf2.effective_start_date <= trunc(hr_ceridian.get_cer_extract_date) and pcf2.assignment_id = pcf.assignment_id)) pcf1 , (select cost_allocation_keyflex_id, segment1, segment2, segment3, segment4, segment5, segment6, segment7, segment8, segment9, segment10 from pay_cost_allocation_keyflex) pcf2 , hr_locations_all hl , per_all_assignments_f pasg , per_periods_of_service pps WHERE pasg.person_id = pp.person_id and pasg.assignment_type = 'E' and pasg.location_id = hl.location_id and pps.person_id = pasg.person_id and pps.period_of_service_id= pasg.period_of_service_id and hs.soft_coding_keyflex_id = pasg.soft_coding_keyflex_id and hs.segment1 = hou.organization_id and pasg.assignment_id = pcf1.assignment_id (+) and hou.cost_allocation_keyflex_id = pcf2.cost_allocation_keyflex_id (+) and pp.employee_number is not null and pasg.primary_flag = 'Y' and trunc(hr_ceridian.get_cer_extract_date) between pp.effective_start_date and pp.effective_end_date and pasg.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 = pp.person_id and a2.assignment_id = pasg.assignment_id 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 PASG.ASSIGNMENT_NUMBER
, PASG.PRIMARY_FLAG
, PP.NATIONAL_IDENTIFIER
, NVL(PCF1.SEGMENT1
, PCF2.SEGMENT1)
, NVL(PCF1.SEGMENT2
, PCF2.SEGMENT2)
, NVL(PCF1.SEGMENT3
, PCF2.SEGMENT3)
, NVL(PCF1.SEGMENT4
, PCF2.SEGMENT4)
, NVL(PCF1.SEGMENT5
, PCF2.SEGMENT5)
, NVL(PCF1.SEGMENT6
, PCF2.SEGMENT6)
, NVL(PCF1.SEGMENT7
, PCF2.SEGMENT7)
, NVL(PCF1.SEGMENT8
, PCF2.SEGMENT8)
, NVL(PCF1.SEGMENT9
, PCF2.SEGMENT9)
, NVL(PCF1.SEGMENT10
, PCF2.SEGMENT10)
, PP.EMPLOYEE_NUMBER
, HOU.NAME
, HL.LOCATION_CODE
, PASG.ASSIGNMENT_ID
, PP.BUSINESS_GROUP_ID
, PASG.PAYROLL_ID
, PASG.ORGANIZATION_ID
, PP.PERSON_ID
, GREATEST ( DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, NVL(PCF1.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL(PCF1.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
, NVL(PCF1.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PCF1.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PCF1.EFFECTIVE_START_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))) COST_LAST_UPDATE_DATE
, GREATEST ( GREATEST(NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE)
, GREATEST(NVL(PASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE)
, GREATEST(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')))
, GREATEST ( DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, NVL(PCF1.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL(PCF1.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
, NVL(PCF1.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PCF1.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PCF1.EFFECTIVE_START_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))) ) LAST_UPDATE_DATE
FROM PER_ALL_PEOPLE_F PP
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_SOFT_CODING_KEYFLEX HS
, (SELECT PCF.ASSIGNMENT_ID ASSIGNMENT_ID
, PCF.EFFECTIVE_START_DATE EFFECTIVE_START_DATE
, PCF.EFFECTIVE_END_DATE EFFECTIVE_END_DATE
, PCF.LAST_UPDATE_DATE LAST_UPDATE_DATE
, PCA.SEGMENT1 SEGMENT1
, PCA.SEGMENT2 SEGMENT2
, PCA.SEGMENT3 SEGMENT3
, PCA.SEGMENT4 SEGMENT4
, PCA.SEGMENT5 SEGMENT5
, PCA.SEGMENT6 SEGMENT6
, PCA.SEGMENT7 SEGMENT7
, PCA.SEGMENT8 SEGMENT8
, PCA.SEGMENT9 SEGMENT9
, PCA.SEGMENT10 SEGMENT10
FROM PAY_COST_ALLOCATIONS_F PCF
, PAY_COST_ALLOCATION_KEYFLEX PCA
WHERE PCF.COST_ALLOCATION_KEYFLEX_ID = PCA.COST_ALLOCATION_KEYFLEX_ID
AND PCF.EFFECTIVE_START_DATE=(SELECT MAX(PCF2.EFFECTIVE_START_DATE)
FROM PAY_COST_ALLOCATIONS_F PCF2
WHERE PCF2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PCF2.ASSIGNMENT_ID = PCF.ASSIGNMENT_ID)) PCF1
, (SELECT COST_ALLOCATION_KEYFLEX_ID
, SEGMENT1
, SEGMENT2
, SEGMENT3
, SEGMENT4
, SEGMENT5
, SEGMENT6
, SEGMENT7
, SEGMENT8
, SEGMENT9
, SEGMENT10
FROM PAY_COST_ALLOCATION_KEYFLEX) PCF2
, HR_LOCATIONS_ALL HL
, PER_ALL_ASSIGNMENTS_F PASG
, PER_PERIODS_OF_SERVICE PPS
WHERE PASG.PERSON_ID = PP.PERSON_ID
AND PASG.ASSIGNMENT_TYPE = 'E'
AND PASG.LOCATION_ID = HL.LOCATION_ID
AND PPS.PERSON_ID = PASG.PERSON_ID
AND PPS.PERIOD_OF_SERVICE_ID= PASG.PERIOD_OF_SERVICE_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PASG.ASSIGNMENT_ID = PCF1.ASSIGNMENT_ID (+)
AND HOU.COST_ALLOCATION_KEYFLEX_ID = PCF2.COST_ALLOCATION_KEYFLEX_ID (+)
AND PP.EMPLOYEE_NUMBER IS NOT NULL
AND PASG.PRIMARY_FLAG = 'Y'
AND TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND PASG.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 = PP.PERSON_ID
AND A2.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID
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))