WSH_LOCATIONS_V

Details

• Schema: FUSION

• Object owner: WSH

• Object type: VIEW

Columns

Name
WSH_LOCATION_ID SOURCE_LOCATION_ID LOCATION_SOURCE_CODE LOCATION_TYPE LOCATION_CODE UI_LOCATION_CODE ADDRESS1 ADDRESS2 ADDRESS3 ADDRESS4 COUNTRY STATE PROVINCE COUNTY CITY POSTAL_CODE INACTIVE_DATE

Query

SQL_Statement

SELECT hr.location_details_id WSH_LOCATION_ID, hr.location_id SOURCE_LOCATION_ID, 'HR' LOCATION_SOURCE_CODE, 'I' LOCATION_TYPE, hrtl.location_code LOCATION_CODE, substrb((hrtl.location_code||':'||hr.address_line_1||'-'||hr.address_line_2||'-'||hr.town_or_city||'-'||hr.region_2||'-'||hr.POSTAL_CODE||'-'||hr.COUNTRY), 1, 500) ui_location_code, hr.ADDRESS_LINE_1 ADDRESS1, hr.ADDRESS_LINE_2 ADDRESS2, hr.ADDRESS_LINE_3 ADDRESS3, hr.ADD_INFORMATION13 ADDRESS4, country, hr.REGION_2 STATE, hr.REGION_3 PROVINCE, hr.REGION_1 COUNTY, hr.TOWN_OR_CITY CITY, hr.POSTAL_CODE POSTAL_CODE, hr.EFFECTIVE_END_DATE INACTIVE_DATE FROM HR_LOCATIONS_ALL_F HR, HR_LOCATIONS_ALL_TL HRTL WHERE HRTL.LOCATION_DETAILS_ID = HR.LOCATION_DETAILS_ID AND HRTL.language = userenv('LANG') AND trunc(sysdate) between hr.effective_start_date and hr.effective_end_date AND hr.active_status = 'A' AND trunc(sysdate) between HRTL.effective_start_date and HRTL.effective_end_date UNION ALL SELECT hz.location_id WSH_LOCATION_ID, hz.location_id SOURCE_LOCATION_ID, 'HZ' location_source_code, 'E' LOCATION_TYPE, hps.party_site_number location_code, substrb((decode(hps.party_site_number,null,null, hps.party_site_number||':')||(HZ.ADDRESS1||'-'||HZ.ADDRESS2||'-'||HZ.CITY||'-'||NVL(HZ.STATE, HZ.PROVINCE)||'-'||HZ.POSTAL_CODE||'-'||HZ.COUNTRY)),1,500) ui_location_code, address1, address2, address3, address4, country, state, province, county, city, postal_code, HZ.ADDRESS_EXPIRATION_DATE inactive_date FROM hz_locations hz, hz_party_sites hps WHERE hps.location_id = hz.location_id AND hps.party_site_number = (SELECT first_value(party_site_number) OVER (ORDER BY status ASC) AS party_site_number FROM hz_party_sites hps1 WHERE hps1.location_id = hz.location_id And rownum=1)