VIEW: APPS.HR_ADP_WORKERS_COMP_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) , pasg.assignment_id , pasg.assignment_number , decode(pasg.primary_flag, 'Y',0,pasg.assignment_sequence) , pasg.primary_flag , pj.name , nvl(hs.segment8, pjw.wc_code) , hl.region_2 , 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(pj.last_update_date, to_date('1900/01/01','YYYY/MM/DD')), pj.date_from) , nvl(pjw.last_update_date, to_date('1900/01/01','YYYY/MM/DD'))) from hr_adp_emp_ref_v ref , hr_all_organization_units hou , hr_soft_coding_keyflex hs , hr_locations_all hl , pay_job_wc_code_usages pjw , per_jobs pj , per_all_people_f pp , per_all_assignments_f pasg , per_time_period_types ptp , pay_payrolls_x ppr WHERE ppr.period_type = ptp.period_type and pasg.assignment_type = 'E' and ppr.payroll_id = pasg.payroll_id and hs.soft_coding_keyflex_id = pasg.soft_coding_keyflex_id and hs.segment1 = hou.organization_id and pasg.person_id = pp.person_id and pasg.assignment_id = ref.assignment_id and pasg.rowid = ref.asg_rowid and pasg.job_id = pj.job_id and pasg.location_id = hl.location_id and pasg.job_id = pjw.job_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 pp.employee_number is not null and trunc(hr_adp.get_adp_extract_date) between pp.effective_start_date and pp.effective_end_date and hl.region_2 = pjw.state_code

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)
, PASG.ASSIGNMENT_ID
, PASG.ASSIGNMENT_NUMBER
, DECODE(PASG.PRIMARY_FLAG
, 'Y'
, 0
, PASG.ASSIGNMENT_SEQUENCE)
, PASG.PRIMARY_FLAG
, PJ.NAME
, NVL(HS.SEGMENT8
, PJW.WC_CODE)
, HL.REGION_2
, 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(PJ.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PJ.DATE_FROM)
, NVL(PJW.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
FROM HR_ADP_EMP_REF_V REF
, HR_ALL_ORGANIZATION_UNITS HOU
, HR_SOFT_CODING_KEYFLEX HS
, HR_LOCATIONS_ALL HL
, PAY_JOB_WC_CODE_USAGES PJW
, PER_JOBS PJ
, PER_ALL_PEOPLE_F PP
, PER_ALL_ASSIGNMENTS_F PASG
, PER_TIME_PERIOD_TYPES PTP
, PAY_PAYROLLS_X PPR
WHERE PPR.PERIOD_TYPE = PTP.PERIOD_TYPE
AND PASG.ASSIGNMENT_TYPE = 'E'
AND PPR.PAYROLL_ID = PASG.PAYROLL_ID
AND HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID
AND PASG.PERSON_ID = PP.PERSON_ID
AND PASG.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
AND PASG.ROWID = REF.ASG_ROWID
AND PASG.JOB_ID = PJ.JOB_ID
AND PASG.LOCATION_ID = HL.LOCATION_ID
AND PASG.JOB_ID = PJW.JOB_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 PP.EMPLOYEE_NUMBER IS NOT NULL
AND TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND HL.REGION_2 = PJW.STATE_CODE