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_EMPLOYEE_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, DECODE (ptp.number_per_fiscal_year, 52, 'W', 24, 'S', 26, 'B', 12, 'M', ptp.number_per_fiscal_year), 0, pp.first_name, pp.last_name, pp.middle_names, pp.suffix, pp.sex, pad.telephone_number_1, pp.national_identifier, pad.region_2, pad.postal_code, pasg.source_type, DECODE (asta.per_system_status, 'TERM_ASSIGN', 'T', 'ACTIVE_ASSIGN', DECODE (DECODE (LEAST (pps.final_process_date + 1, TRUNC (hr_adp.get_adp_extract_date)), pps.final_process_date + 1, 'Y', 'N1'), DECODE (NVL (TO_CHAR (pps.actual_termination_date), 'ATP_NULL'), NVL (TO_CHAR (pps.final_process_date), 'FPD_NULL'), 'Y', 'N2'), 'T', 'A'), 'SUSP_ASSIGN', DECODE (DECODE (LEAST (pps.final_process_date + 1, TRUNC (hr_adp.get_adp_extract_date)), pps.final_process_date + 1, 'Y', 'N1'), DECODE (NVL (TO_CHAR (pps.actual_termination_date), 'ATP_NULL'), NVL (TO_CHAR (pps.final_process_date), 'FPD_NULL'), 'Y', 'N2'), 'T', 'S'), 'O') assignment_status, pps.leaving_reason, pasg.employment_category, hou2.name, jobs.name, hs.segment5, ppg.segment1, pp.date_of_birth, pp.date_of_death, nvl(pp.original_date_of_hire, pps2.date_start), decode(nvl(pp.original_date_of_hire, pps2.date_start), pps.date_start, to_date(null,'YYYY/MM/DD'), pps.date_start), pps.actual_termination_date, pp.marital_status, pad.address_line1, pad.address_line2, pad.town_or_city, pp.expense_check_send_to_address, decode(pp.date_of_death, null, 'N', 'Y'), pp.per_information1, spo.spouse_date_of_birth, pps.final_process_date, pps.last_standard_process_date, GREATEST (NVL (pp.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD')), pp.effective_start_date), GREATEST (NVL (pad.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD')), pad.date_from), GREATEST (NVL (pasg.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD')), pasg.effective_start_date), GREATEST( DECODE( GREATEST( TRUNC(hr_adp.get_adp_extract_date), nvl(pps.final_process_date, to_date ('1900/01/01','YYYY/MM/DD'))), TRUNC(hr_adp.get_adp_extract_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'))), nvl(pps.last_update_date, to_date ('1900/01/01', 'YYYY/MM/DD'))), pps.date_start), NVL (asta.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD')), GREATEST ( DECODE( GREATEST( TRUNC(hr_adp.get_adp_extract_date), nvl(pps.final_process_date, to_date ('1900/01/01','YYYY/MM/DD'))), TRUNC(hr_adp.get_adp_extract_date), GREATEST( 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')), pp.effective_start_date, NVL(pad.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD')), pad.date_from, NVL(pasg.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD')), pasg.effective_start_date, NVL(pps.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(pad.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD')), pad.date_from, NVL(pasg.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD')), pasg.effective_start_date, NVL(pps.last_update_date, TO_DATE ('1900/01/01', 'YYYY/MM/DD')))), pps.date_start) FROM hr_adp_spouse_v spo, hr_adp_emp_ref_v ref, per_jobs jobs, pay_people_groups ppg, per_assignment_status_types asta, per_periods_of_service pps, per_periods_of_service pps2, per_addresses pad, per_all_people_f pp, hr_all_organization_units hou, hr_all_organization_units hou2, hr_soft_coding_keyflex hs, per_time_period_types ptp, per_all_assignments_f pasg WHERE ref.period_type = ptp.period_type AND pasg.assignment_type = 'E' AND pps.period_of_service_id = pasg.period_of_service_id AND pps.person_id = pp.person_id AND pps2.person_id = pp.person_id AND pps2.date_start = (SELECT MIN(pps3.date_start) FROM per_periods_of_service pps3 WHERE pps3.person_id = pps2.person_id) AND hs.soft_coding_keyflex_id = pasg.soft_coding_keyflex_id AND hs.segment1 = hou.organization_id and pasg.rowid = ref.asg_rowid and pp.rowid = ref.per_rowid AND pasg.people_group_id = ppg.people_group_id (+) AND pasg.organization_id = hou2.organization_id AND pasg.person_id = pp.person_id AND pasg.job_id = jobs.job_id (+) AND pasg.primary_flag = 'Y' AND pasg.assignment_status_type_id = asta.assignment_status_type_id AND pp.person_id = spo.person_id (+) AND pp.person_id = pad.person_id AND pad.style IN ('US', 'US_GLB') AND pad.primary_flag = 'Y' AND ((TRUNC(hr_adp.get_adp_extract_date) >= pad.date_from) AND (TRUNC(hr_adp.get_adp_extract_date) <= pad.date_to OR pad.date_to IS NULL))

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
, DECODE (PTP.NUMBER_PER_FISCAL_YEAR
, 52
, 'W'
, 24
, 'S'
, 26
, 'B'
, 12
, 'M'
, PTP.NUMBER_PER_FISCAL_YEAR)
, 0
, PP.FIRST_NAME
, PP.LAST_NAME
, PP.MIDDLE_NAMES
, PP.SUFFIX
, PP.SEX
, PAD.TELEPHONE_NUMBER_1
, PP.NATIONAL_IDENTIFIER
, PAD.REGION_2
, PAD.POSTAL_CODE
, PASG.SOURCE_TYPE
, DECODE (ASTA.PER_SYSTEM_STATUS
, 'TERM_ASSIGN'
, 'T'
, 'ACTIVE_ASSIGN'
, DECODE (DECODE (LEAST (PPS.FINAL_PROCESS_DATE + 1
, TRUNC (HR_ADP.GET_ADP_EXTRACT_DATE))
, PPS.FINAL_PROCESS_DATE + 1
, 'Y'
, 'N1')
, DECODE (NVL (TO_CHAR (PPS.ACTUAL_TERMINATION_DATE)
, 'ATP_NULL')
, NVL (TO_CHAR (PPS.FINAL_PROCESS_DATE)
, 'FPD_NULL')
, 'Y'
, 'N2')
, 'T'
, 'A')
, 'SUSP_ASSIGN'
, DECODE (DECODE (LEAST (PPS.FINAL_PROCESS_DATE + 1
, TRUNC (HR_ADP.GET_ADP_EXTRACT_DATE))
, PPS.FINAL_PROCESS_DATE + 1
, 'Y'
, 'N1')
, DECODE (NVL (TO_CHAR (PPS.ACTUAL_TERMINATION_DATE)
, 'ATP_NULL')
, NVL (TO_CHAR (PPS.FINAL_PROCESS_DATE)
, 'FPD_NULL')
, 'Y'
, 'N2')
, 'T'
, 'S')
, 'O') ASSIGNMENT_STATUS
, PPS.LEAVING_REASON
, PASG.EMPLOYMENT_CATEGORY
, HOU2.NAME
, JOBS.NAME
, HS.SEGMENT5
, PPG.SEGMENT1
, PP.DATE_OF_BIRTH
, PP.DATE_OF_DEATH
, NVL(PP.ORIGINAL_DATE_OF_HIRE
, PPS2.DATE_START)
, DECODE(NVL(PP.ORIGINAL_DATE_OF_HIRE
, PPS2.DATE_START)
, PPS.DATE_START
, TO_DATE(NULL
, 'YYYY/MM/DD')
, PPS.DATE_START)
, PPS.ACTUAL_TERMINATION_DATE
, PP.MARITAL_STATUS
, PAD.ADDRESS_LINE1
, PAD.ADDRESS_LINE2
, PAD.TOWN_OR_CITY
, PP.EXPENSE_CHECK_SEND_TO_ADDRESS
, DECODE(PP.DATE_OF_DEATH
, NULL
, 'N'
, 'Y')
, PP.PER_INFORMATION1
, SPO.SPOUSE_DATE_OF_BIRTH
, PPS.FINAL_PROCESS_DATE
, PPS.LAST_STANDARD_PROCESS_DATE
, GREATEST (NVL (PP.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE)
, GREATEST (NVL (PAD.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PAD.DATE_FROM)
, GREATEST (NVL (PASG.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE)
, GREATEST( DECODE( GREATEST( TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, NVL(PPS.FINAL_PROCESS_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')))
, TRUNC(HR_ADP.GET_ADP_EXTRACT_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')))
, NVL(PPS.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')))
, PPS.DATE_START)
, NVL (ASTA.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, GREATEST ( DECODE( GREATEST( TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, NVL(PPS.FINAL_PROCESS_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD')))
, TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
, GREATEST( 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'))
, PP.EFFECTIVE_START_DATE
, NVL(PAD.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PAD.DATE_FROM
, NVL(PASG.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE
, NVL(PPS.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(PAD.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PAD.DATE_FROM
, NVL(PASG.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))
, PASG.EFFECTIVE_START_DATE
, NVL(PPS.LAST_UPDATE_DATE
, TO_DATE ('1900/01/01'
, 'YYYY/MM/DD'))))
, PPS.DATE_START)
FROM HR_ADP_SPOUSE_V SPO
, HR_ADP_EMP_REF_V REF
, PER_JOBS JOBS
, PAY_PEOPLE_GROUPS PPG
, PER_ASSIGNMENT_STATUS_TYPES ASTA
, PER_PERIODS_OF_SERVICE PPS
, PER_PERIODS_OF_SERVICE PPS2
, PER_ADDRESSES PAD
, PER_ALL_PEOPLE_F PP
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_ALL_ORGANIZATION_UNITS HOU2
, HR_SOFT_CODING_KEYFLEX HS
, PER_TIME_PERIOD_TYPES PTP
, PER_ALL_ASSIGNMENTS_F PASG
WHERE REF.PERIOD_TYPE = PTP.PERIOD_TYPE
AND PASG.ASSIGNMENT_TYPE = 'E'
AND PPS.PERIOD_OF_SERVICE_ID = PASG.PERIOD_OF_SERVICE_ID
AND PPS.PERSON_ID = PP.PERSON_ID
AND PPS2.PERSON_ID = PP.PERSON_ID
AND PPS2.DATE_START = (SELECT MIN(PPS3.DATE_START)
FROM PER_PERIODS_OF_SERVICE PPS3
WHERE PPS3.PERSON_ID = PPS2.PERSON_ID)
AND HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PASG.ROWID = REF.ASG_ROWID
AND PP.ROWID = REF.PER_ROWID
AND PASG.PEOPLE_GROUP_ID = PPG.PEOPLE_GROUP_ID (+)
AND PASG.ORGANIZATION_ID = HOU2.ORGANIZATION_ID
AND PASG.PERSON_ID = PP.PERSON_ID
AND PASG.JOB_ID = JOBS.JOB_ID (+)
AND PASG.PRIMARY_FLAG = 'Y'
AND PASG.ASSIGNMENT_STATUS_TYPE_ID = ASTA.ASSIGNMENT_STATUS_TYPE_ID
AND PP.PERSON_ID = SPO.PERSON_ID (+)
AND PP.PERSON_ID = PAD.PERSON_ID
AND PAD.STYLE IN ('US'
, 'US_GLB')
AND PAD.PRIMARY_FLAG = 'Y'
AND ((TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) >= PAD.DATE_FROM)
AND (TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) <= PAD.DATE_TO OR PAD.DATE_TO IS NULL))