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_EFT_V

Source

View Text - Preformatted


SELECT pp.employee_number , ppm.effective_start_date , decode(trunc(hr_ceridian.get_cer_extract_date), greatest(trunc(hr_ceridian.get_cer_extract_date), ppm.effective_end_date), ppm.effective_end_date, to_date(NULL)) , pa.assignment_number , pa.primary_flag , pp.national_identifier , ho.name , pea.segment5 , pea.segment6 , pea.segment3 , pea.segment2 , pea.segment4 , decode(ppm.priority, 1, 'A' , 2, 'B' , 3, 'C' , 4, 'D' , 5, 'E' , 6, 'F' , 7, 'G' , 8, 'H' ) , ppm.priority , ppm.amount , pa.assignment_id , pp.business_group_id , pa.payroll_id , pp.person_id , ppm.personal_payment_method_id , greatest( decode( greatest(trunc(hr_ceridian.get_cer_extract_date), nvl(ppm.effective_end_date, to_date('31-12-4712','DD-MM-YYYY'))), trunc(hr_ceridian.get_cer_extract_date), greatest(nvl(ppm.effective_end_date, to_date('31-12-4712','DD-MM-YYYY')), nvl(ppm.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))), nvl(ppm.last_update_date, to_date('01-01-1900','DD-MM-YYYY'))), nvl(ppm.effective_start_date,to_date('1900/01/01','YYYY/MM/DD'))) from per_all_people_f pp , pay_external_accounts pea , pay_personal_payment_methods_f ppm , pay_org_payment_methods_f pop , pay_payment_types ppt , hr_all_organization_units ho , per_periods_of_service pps , hr_soft_coding_keyflex hs , per_all_assignments_f pa WHERE ppm.external_account_id = pea.external_account_id and pa.assignment_type = 'E' and ppm.org_payment_method_id = pop.org_payment_method_id and pop.payment_type_id = ppt.payment_type_id and ppt.category = 'MT' and ppt.territory_code = 'US' and pp.person_id = pa.person_id and pps.person_id = pa.person_id and pps.period_of_service_id = pa.period_of_service_id and hs.soft_coding_keyflex_id = pa.soft_coding_keyflex_id and hs.segment1 = ho.organization_id and ppm.assignment_id = pa.assignment_id and pa.primary_flag = 'Y' and pa.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 = pa.person_id and a2.assignment_id = pa.assignment_id and a2.primary_flag = 'Y')) and ppm.effective_start_date = (select max(ppm2.effective_start_date) from pay_personal_payment_methods_f ppm2 where (ppm2.effective_start_date <= trunc(hr_ceridian.get_cer_extract_date) and ppm2.personal_payment_method_id = ppm.personal_payment_method_id)) and pop.effective_start_date = (select max(pop2.effective_start_date) from pay_org_payment_methods_f pop2 where (pop2.effective_start_date <= trunc(hr_ceridian.get_cer_extract_date) and pop2.org_payment_method_id = pop.org_payment_method_id)) and trunc (hr_ceridian.get_cer_extract_date) between pp.effective_start_date and pp.effective_end_date 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)) group by pp.employee_number , pa.assignment_number , pa.primary_flag , pp.national_identifier , ho.name , pea.segment5 , pea.segment6 , pea.segment3 , pea.segment2 , pea.segment4 , ppm.priority , ppm.amount , ppm.last_update_date , ppm.effective_start_date , ppm.effective_end_date , pa.assignment_id , pp.business_group_id , pa.payroll_id , pp.person_id , ppm.personal_payment_method_id

View Text - HTML Formatted

SELECT PP.EMPLOYEE_NUMBER
, PPM.EFFECTIVE_START_DATE
, DECODE(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, PPM.EFFECTIVE_END_DATE)
, PPM.EFFECTIVE_END_DATE
, TO_DATE(NULL))
, PA.ASSIGNMENT_NUMBER
, PA.PRIMARY_FLAG
, PP.NATIONAL_IDENTIFIER
, HO.NAME
, PEA.SEGMENT5
, PEA.SEGMENT6
, PEA.SEGMENT3
, PEA.SEGMENT2
, PEA.SEGMENT4
, DECODE(PPM.PRIORITY
, 1
, 'A'
, 2
, 'B'
, 3
, 'C'
, 4
, 'D'
, 5
, 'E'
, 6
, 'F'
, 7
, 'G'
, 8
, 'H' )
, PPM.PRIORITY
, PPM.AMOUNT
, PA.ASSIGNMENT_ID
, PP.BUSINESS_GROUP_ID
, PA.PAYROLL_ID
, PP.PERSON_ID
, PPM.PERSONAL_PAYMENT_METHOD_ID
, GREATEST( DECODE( GREATEST(TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, NVL(PPM.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')))
, TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
, GREATEST(NVL(PPM.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
, NVL(PPM.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PPM.LAST_UPDATE_DATE
, TO_DATE('01-01-1900'
, 'DD-MM-YYYY')))
, NVL(PPM.EFFECTIVE_START_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
FROM PER_ALL_PEOPLE_F PP
, PAY_EXTERNAL_ACCOUNTS PEA
, PAY_PERSONAL_PAYMENT_METHODS_F PPM
, PAY_ORG_PAYMENT_METHODS_F POP
, PAY_PAYMENT_TYPES PPT
, HR_ALL_ORGANIZATION_UNITS HO
, PER_PERIODS_OF_SERVICE PPS
, HR_SOFT_CODING_KEYFLEX HS
, PER_ALL_ASSIGNMENTS_F PA
WHERE PPM.EXTERNAL_ACCOUNT_ID = PEA.EXTERNAL_ACCOUNT_ID
AND PA.ASSIGNMENT_TYPE = 'E'
AND PPM.ORG_PAYMENT_METHOD_ID = POP.ORG_PAYMENT_METHOD_ID
AND POP.PAYMENT_TYPE_ID = PPT.PAYMENT_TYPE_ID
AND PPT.CATEGORY = 'MT'
AND PPT.TERRITORY_CODE = 'US'
AND PP.PERSON_ID = PA.PERSON_ID
AND PPS.PERSON_ID = PA.PERSON_ID
AND PPS.PERIOD_OF_SERVICE_ID = PA.PERIOD_OF_SERVICE_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PA.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HO.ORGANIZATION_ID
AND PPM.ASSIGNMENT_ID = PA.ASSIGNMENT_ID
AND PA.PRIMARY_FLAG = 'Y'
AND PA.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 = PA.PERSON_ID
AND A2.ASSIGNMENT_ID = PA.ASSIGNMENT_ID
AND A2.PRIMARY_FLAG = 'Y'))
AND PPM.EFFECTIVE_START_DATE = (SELECT MAX(PPM2.EFFECTIVE_START_DATE)
FROM PAY_PERSONAL_PAYMENT_METHODS_F PPM2
WHERE (PPM2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND PPM2.PERSONAL_PAYMENT_METHOD_ID = PPM.PERSONAL_PAYMENT_METHOD_ID))
AND POP.EFFECTIVE_START_DATE = (SELECT MAX(POP2.EFFECTIVE_START_DATE)
FROM PAY_ORG_PAYMENT_METHODS_F POP2
WHERE (POP2.EFFECTIVE_START_DATE <= TRUNC(HR_CERIDIAN.GET_CER_EXTRACT_DATE)
AND POP2.ORG_PAYMENT_METHOD_ID = POP.ORG_PAYMENT_METHOD_ID))
AND TRUNC (HR_CERIDIAN.GET_CER_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
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)) GROUP BY PP.EMPLOYEE_NUMBER
, PA.ASSIGNMENT_NUMBER
, PA.PRIMARY_FLAG
, PP.NATIONAL_IDENTIFIER
, HO.NAME
, PEA.SEGMENT5
, PEA.SEGMENT6
, PEA.SEGMENT3
, PEA.SEGMENT2
, PEA.SEGMENT4
, PPM.PRIORITY
, PPM.AMOUNT
, PPM.LAST_UPDATE_DATE
, PPM.EFFECTIVE_START_DATE
, PPM.EFFECTIVE_END_DATE
, PA.ASSIGNMENT_ID
, PP.BUSINESS_GROUP_ID
, PA.PAYROLL_ID
, PP.PERSON_ID
, PPM.PERSONAL_PAYMENT_METHOD_ID