VIEW: APPS.HR_CERIDIAN_EFT_V

Source

View Text - Preformatted

SELECT pp.employee_number , 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 , decode(max(mpm.effective_end_date), greatest(max(mpm.effective_end_date),sysdate), 'Y', 'N') , pp.business_group_id , pp.person_id , greatest(ppm.last_update_date,ppm.effective_start_date) from per_all_people_f pp , pay_external_accounts pea , pay_personal_payment_methods_f ppm , pay_personal_payment_methods_f mpm , pay_org_payment_methods_f pop , pay_payment_types ppt , hr_all_organization_units ho , hr_soft_coding_keyflex hs , per_all_assignments_f pad WHERE ppm.external_account_id = pea.external_account_id and pad.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 = pad.person_id and hs.soft_coding_keyflex_id = pad.soft_coding_keyflex_id and hs.segment1 = ho.organization_id and ppm.assignment_id =pad.assignment_id and pad.primary_flag ='Y' and mpm.personal_payment_method_id = ppm.personal_payment_method_id and pad.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 = pad.person_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(sysdate) 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(sysdate) and pop2.org_payment_method_id = pop.org_payment_method_id)) and trunc (sysdate) between pp.effective_start_date and pp.effective_end_date group by pp.employee_number , ho.name , pea.segment5 , pea.segment6 , pea.segment3 , pea.segment2 , pea.segment4 , ppm.priority , ppm.amount , ppm.last_update_date , ppm.effective_start_date , pp.business_group_id , pp.person_id

View Text - HTML Formatted

SELECT PP.EMPLOYEE_NUMBER
, 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
, DECODE(MAX(MPM.EFFECTIVE_END_DATE)
, GREATEST(MAX(MPM.EFFECTIVE_END_DATE)
, SYSDATE)
, 'Y'
, 'N')
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID
, GREATEST(PPM.LAST_UPDATE_DATE
, PPM.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F PP
, PAY_EXTERNAL_ACCOUNTS PEA
, PAY_PERSONAL_PAYMENT_METHODS_F PPM
, PAY_PERSONAL_PAYMENT_METHODS_F MPM
, PAY_ORG_PAYMENT_METHODS_F POP
, PAY_PAYMENT_TYPES PPT
, HR_ALL_ORGANIZATION_UNITS HO
, HR_SOFT_CODING_KEYFLEX HS
, PER_ALL_ASSIGNMENTS_F PAD
WHERE PPM.EXTERNAL_ACCOUNT_ID = PEA.EXTERNAL_ACCOUNT_ID
AND PAD.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 = PAD.PERSON_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PAD.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HO.ORGANIZATION_ID
AND PPM.ASSIGNMENT_ID =PAD.ASSIGNMENT_ID
AND PAD.PRIMARY_FLAG ='Y'
AND MPM.PERSONAL_PAYMENT_METHOD_ID = PPM.PERSONAL_PAYMENT_METHOD_ID
AND PAD.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 = PAD.PERSON_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(SYSDATE)
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(SYSDATE)
AND POP2.ORG_PAYMENT_METHOD_ID = POP.ORG_PAYMENT_METHOD_ID))
AND TRUNC (SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE GROUP BY PP.EMPLOYEE_NUMBER
, HO.NAME
, PEA.SEGMENT5
, PEA.SEGMENT6
, PEA.SEGMENT3
, PEA.SEGMENT2
, PEA.SEGMENT4
, PPM.PRIORITY
, PPM.AMOUNT
, PPM.LAST_UPDATE_DATE
, PPM.EFFECTIVE_START_DATE
, PP.BUSINESS_GROUP_ID
, PP.PERSON_ID