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_ADP_EFT_V

Source

View Text - Preformatted


SELECT ref.company_code_equivalent , hou.name , hs.segment1 , pasg.organization_id , pp.employee_number , pp.person_id , pp.business_group_id , pasg.assignment_id , pasg.assignment_number , decode(pasg.primary_flag, 'Y',0,pasg.assignment_sequence) , pasg.primary_flag , decode(ptp.number_per_fiscal_year, 52,'W',24,'S',26,'B', 12,'M', ptp.number_per_fiscal_year) , ppm.priority , pea.segment3 , ppm.amount , ppm.percentage , pea.segment2 , pea.prenote_date , pea.segment4 , decode(ppm.amount, NULL, 'Y', 'N') , pea.segment5 , pea.segment6 , decode(ppm.priority, hr_adp.get_max_ppm_priority(pasg.assignment_id), 'Y', 'N') , ppm.effective_start_date , ppm.effective_end_date , 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(ppm.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), ppm.effective_start_date) , greatest( decode( greatest( trunc(hr_adp.get_adp_extract_date), ppm.effective_end_date), trunc(hr_adp.get_adp_extract_date), greatest (ppm.effective_end_date, nvl(pp.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), pp.effective_start_date, nvl(pasg.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), pasg.effective_start_date, nvl(pp.last_update_date, to_date ('1900/01/01', 'YYYY/MM/DD'))), greatest (nvl(pp.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), pp.effective_start_date, nvl(pasg.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')), pasg.effective_start_date, nvl(ppm.last_update_date, to_date('1900/01/01', 'YYYY/MM/DD')))), ppm.effective_start_date) from hr_adp_emp_ref_v ref , per_all_people_f pp , pay_external_accounts pea , hr_all_organization_units hou , hr_soft_coding_keyflex hs , pay_payment_types ppt , pay_org_payment_methods_f pop , pay_personal_payment_methods_f ppm , per_time_period_types ptp , per_all_assignments_f pasg , pay_payrolls_x ppr WHERE ppm.external_account_id = pea.external_account_id and pasg.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 = pasg.person_id and pasg.assignment_id = ref.assignment_id and hs.soft_coding_keyflex_id = pasg.soft_coding_keyflex_id and hs.segment1 = hou.organization_id and ppm.assignment_id = pasg.assignment_id and ptp.period_type = ppr.period_type and ppr.payroll_id = pasg.payroll_id and pasg.effective_start_date = (select max(a2.effective_start_date) from per_all_assignments_f a2 where (a2.effective_start_date <= trunc(hr_adp.get_adp_extract_date) and a2.assignment_id = pasg.assignment_id)) 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_adp.get_adp_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_adp.get_adp_extract_date) and pop2.org_payment_method_id = pop.org_payment_method_id)) and trunc (hr_adp.get_adp_extract_date) between pp.effective_start_date and pp.effective_end_date

View Text - HTML Formatted

SELECT REF.COMPANY_CODE_EQUIVALENT
, HOU.NAME
, HS.SEGMENT1
, PASG.ORGANIZATION_ID
, PP.EMPLOYEE_NUMBER
, PP.PERSON_ID
, PP.BUSINESS_GROUP_ID
, PASG.ASSIGNMENT_ID
, PASG.ASSIGNMENT_NUMBER
, DECODE(PASG.PRIMARY_FLAG
, 'Y'
, 0
, PASG.ASSIGNMENT_SEQUENCE)
, PASG.PRIMARY_FLAG
, DECODE(PTP.NUMBER_PER_FISCAL_YEAR
, 52
, 'W'
, 24
, 'S'
, 26
, 'B'
, 12
, 'M'
, PTP.NUMBER_PER_FISCAL_YEAR)
, PPM.PRIORITY
, PEA.SEGMENT3
, PPM.AMOUNT
, PPM.PERCENTAGE
, PEA.SEGMENT2
, PEA.PRENOTE_DATE
, PEA.SEGMENT4
, DECODE(PPM.AMOUNT
, NULL
, 'Y'
, 'N')
, PEA.SEGMENT5
, PEA.SEGMENT6
, DECODE(PPM.PRIORITY
, HR_ADP.GET_MAX_PPM_PRIORITY(PASG.ASSIGNMENT_ID)
, 'Y'
, 'N')
, PPM.EFFECTIVE_START_DATE
, PPM.EFFECTIVE_END_DATE
, 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(PPM.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PPM.EFFECTIVE_START_DATE)
, GREATEST( DECODE( GREATEST( TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, PPM.EFFECTIVE_END_DATE)
, TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, GREATEST (PPM.EFFECTIVE_END_DATE
, NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE
, NVL(PASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE
, NVL(PP.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')))
, GREATEST (NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE
, NVL(PASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE
, NVL(PPM.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))))
, PPM.EFFECTIVE_START_DATE)
FROM HR_ADP_EMP_REF_V REF
, PER_ALL_PEOPLE_F PP
, PAY_EXTERNAL_ACCOUNTS PEA
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_SOFT_CODING_KEYFLEX HS
, PAY_PAYMENT_TYPES PPT
, PAY_ORG_PAYMENT_METHODS_F POP
, PAY_PERSONAL_PAYMENT_METHODS_F PPM
, PER_TIME_PERIOD_TYPES PTP
, PER_ALL_ASSIGNMENTS_F PASG
, PAY_PAYROLLS_X PPR
WHERE PPM.EXTERNAL_ACCOUNT_ID = PEA.EXTERNAL_ACCOUNT_ID
AND PASG.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 = PASG.PERSON_ID
AND PASG.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PPM.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID
AND PTP.PERIOD_TYPE = PPR.PERIOD_TYPE
AND PPR.PAYROLL_ID = PASG.PAYROLL_ID
AND PASG.EFFECTIVE_START_DATE = (SELECT MAX(A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE (A2.EFFECTIVE_START_DATE <= TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND A2.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID))
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_ADP.GET_ADP_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_ADP.GET_ADP_EXTRACT_DATE)
AND POP2.ORG_PAYMENT_METHOD_ID = POP.ORG_PAYMENT_METHOD_ID))
AND TRUNC (HR_ADP.GET_ADP_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE