IBY_EXT_FD_EMP_OFFICE_ADDR

Details

• Schema: FUSION

• Object owner: IBY

• Object type: VIEW

Columns

Name
PAYMENT_ID ADDR_LINE1 ADDR_LINE2 ADDR_LINE3 ADDR_LINE4 CITY COUNTY PROVINCE STATE COUNTRY POSTAL_CODE ADDR_CONCAT

Query

SQL_Statement

SELECT pmt_office_addr.payment_id, per_loc.address_line_1 addr_line1, per_loc.address_line_2 addr_line2, per_loc.address_line_3 addr_line3, null addr_line4, per_loc.town_or_city city, DECODE( per_loc.style, 'US', NVL(per_loc.region_1, ''), 'US_GLB', NVL(per_loc.region_1, ''), 'IE', '', 'IE_GLB', '', 'GB', '', '') county, DECODE(per_loc.style, 'US', '', 'US_GLB', '', 'IE', '', 'IE_GLB', '', 'GB', '', 'CA', NVL(per_loc.region_3, ''), 'CA_GLB', NVL(per_loc.region_1, ''), 'JP', NVL(per_loc.region_1, '') ) province, DECODE(per_loc.style, 'CA', '', 'CA_GLB', '', NVL(per_loc.region_2, '')) state, per_loc.country country, per_loc.postal_code postal_code, per_loc.address_line_1 || ', ' || per_loc.address_line_2 || ', ' || per_loc.address_line_3 || ', ' || per_loc.town_or_city || ', ' || DECODE( per_loc.style, 'CA', '', 'CA_GLB', '', NVL(per_loc.region_2, '') ) || ', ' || per_loc.country || ', ' || per_loc.postal_code addr_concat FROM IBY_PAYMENTS_ALL pmt_office_addr, HR_LOCATIONS_ALL_F per_loc, PER_ALL_ASSIGNMENTS_M per_assgn, PER_ALL_PEOPLE_F_V per_people WHERE NVL(pmt_office_addr.employee_address_code, 'X') = 'OFFICE' AND pmt_office_addr.employee_person_id = per_assgn.person_id(+) AND per_assgn.location_id = per_loc.location_id(+) AND TRUNC(pmt_office_addr.payment_date) BETWEEN per_loc.effective_start_date AND per_loc.effective_end_date AND per_assgn.primary_flag(+) = 'Y' AND per_assgn.assignment_type = 'E' AND TRUNC(pmt_office_addr.payment_date) BETWEEN per_assgn.effective_start_date AND per_assgn.effective_end_date AND per_assgn.effective_latest_change = 'Y' AND per_people.person_id(+) = pmt_office_addr.employee_person_id AND TRUNC(pmt_office_addr.payment_date) BETWEEN per_people.effective_start_date AND per_people.effective_end_date