IBY_EXT_FD_EMP_MAIL_ADDR_V

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_mail_addr.payment_id, per_addr.address_line_1 addr_line1, per_addr.address_line_2 addr_line2, per_addr.address_line_3 addr_line3, null addr_line4, per_addr.town_or_city city, DECODE( per_addr.country, 'US', NVL(per_addr.region_1, ''), 'US_GLB', NVL(per_addr.region_1, ''), 'IE', '', 'IE_GLB', '', 'GB', '', '') COUNTY, DECODE(per_addr.country, 'US', '', 'US_GLB', '', 'IE', '', 'IE_GLB', '', 'GB', '', 'CA', NVL(per_addr.region_3, ''), 'CA_GLB', NVL(per_addr.region_1, ''), 'JP', NVL(per_addr.region_1, '') ) province, DECODE(per_addr.country, 'CA', '', 'CA_GLB', '', NVL(per_addr.region_2, '')) state, per_addr.country country, per_addr.postal_code postal_code, per_addr.address_line_1 || ', ' || per_addr.address_line_2 || ', ' || per_addr.address_line_3 || ', ' || per_addr.town_or_city || ', ' || DECODE( per_addr.country, 'CA', '', 'CA_GLB', '', NVL(per_addr.region_2, '') ) || ', ' || per_addr.country || ', ' || per_addr.postal_code addr_concat FROM IBY_PAYMENTS_ALL pmt_mail_addr, PER_ADDRESSES_F per_addr, PER_PERSON_ADDR_USAGES_F addr_use, PER_ALL_PEOPLE_F per_people WHERE NVL(pmt_mail_addr.employee_address_code, 'X') = 'ORA_MAIL' AND addr_use.address_id = per_addr.address_id AND TRUNC(pmt_mail_addr.payment_date) BETWEEN addr_use.effective_start_date AND addr_use.effective_end_date AND addr_use.person_id(+) = per_people.person_id AND per_people.person_id(+) = pmt_mail_addr.employee_person_id AND TRUNC(pmt_mail_addr.payment_date) BETWEEN per_addr.effective_start_date AND per_addr.effective_end_date AND TRUNC(pmt_mail_addr.payment_date) BETWEEN per_people.effective_start_date AND per_people.effective_end_date