VIEW: APPS.HR_ADP_ADDRESS_V

Source

View Text - Preformatted

SELECT ref.company_code_equivalent company_code_equivalent , hou.name gre_name , hs.segment1 gre_id , pasg.organization_id organization_id , pp.employee_number employee_number , pp.business_group_id business_group_id , pp.person_id person_id , decode(ptp.number_per_fiscal_year, 52,'W',24,'S',26,'B', 12,'M', ptp.number_per_fiscal_year) pay_frequency , 0 , pad.address_id address_id , pad.primary_flag address_primary_flag , pad.address_type address_type , pad.date_from date_from , pad.date_to date_to , pad.address_line1 address_line1 , pad.address_line2 address_line2 , pad.address_line3 address_line3 , pad.town_or_city city , pad.region_2 state , pad.postal_code zip_code , pad.country country , greatest(nvl(pp.last_update_date,to_date('1900/01/01','YYYY/MM/DD')), nvl(pasg.last_update_date,to_date('1900/01/01','YYYY/MM/DD')), nvl(pad.last_update_date,to_date('1900/01/01','YYYY/MM/DD')), nvl(pad.date_from, to_date('1900/01/01','YYYY/MM/DD')), nvl(pad.date_to, to_date('1900/01/01','YYYY/MM/DD'))) from hr_adp_emp_ref_v ref , per_all_assignments_f pasg , per_addresses pad , hr_all_organization_units hou , pay_payrolls_x ppr , per_time_period_types ptp , hr_soft_coding_keyflex hs , per_all_people_f pp WHERE pasg.assignment_id = ref.assignment_id and pasg.person_id = pp.person_id and pasg.primary_flag = 'Y' and pasg.assignment_type = 'E' and pad.person_id = pp.person_id and pp.employee_number IS NOT NULL and pad.style in ('US','US_GLB') and ppr.period_type = ptp.period_type 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 trunc(hr_adp.get_adp_extract_date) between pp.effective_start_date and pp.effective_end_date 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))

View Text - HTML Formatted

SELECT REF.COMPANY_CODE_EQUIVALENT COMPANY_CODE_EQUIVALENT
, HOU.NAME GRE_NAME
, HS.SEGMENT1 GRE_ID
, PASG.ORGANIZATION_ID ORGANIZATION_ID
, PP.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, PP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, PP.PERSON_ID PERSON_ID
, DECODE(PTP.NUMBER_PER_FISCAL_YEAR
, 52
, 'W'
, 24
, 'S'
, 26
, 'B'
, 12
, 'M'
, PTP.NUMBER_PER_FISCAL_YEAR) PAY_FREQUENCY
, 0
, PAD.ADDRESS_ID ADDRESS_ID
, PAD.PRIMARY_FLAG ADDRESS_PRIMARY_FLAG
, PAD.ADDRESS_TYPE ADDRESS_TYPE
, PAD.DATE_FROM DATE_FROM
, PAD.DATE_TO DATE_TO
, PAD.ADDRESS_LINE1 ADDRESS_LINE1
, PAD.ADDRESS_LINE2 ADDRESS_LINE2
, PAD.ADDRESS_LINE3 ADDRESS_LINE3
, PAD.TOWN_OR_CITY CITY
, PAD.REGION_2 STATE
, PAD.POSTAL_CODE ZIP_CODE
, PAD.COUNTRY COUNTRY
, GREATEST(NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PAD.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PAD.DATE_FROM
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PAD.DATE_TO
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD')))
FROM HR_ADP_EMP_REF_V REF
, PER_ALL_ASSIGNMENTS_F PASG
, PER_ADDRESSES PAD
, HR_ALL_ORGANIZATION_UNITS HOU
, PAY_PAYROLLS_X PPR
, PER_TIME_PERIOD_TYPES PTP
, HR_SOFT_CODING_KEYFLEX HS
, PER_ALL_PEOPLE_F PP
WHERE PASG.ASSIGNMENT_ID = REF.ASSIGNMENT_ID
AND PASG.PERSON_ID = PP.PERSON_ID
AND PASG.PRIMARY_FLAG = 'Y'
AND PASG.ASSIGNMENT_TYPE = 'E'
AND PAD.PERSON_ID = PP.PERSON_ID
AND PP.EMPLOYEE_NUMBER IS NOT NULL
AND PAD.STYLE IN ('US'
, 'US_GLB')
AND PPR.PERIOD_TYPE = PTP.PERIOD_TYPE
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 TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
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))