VIEW: APPS.EDW_GEOG_POSTCODE_CITY_LCV

Source

View Text - Preformatted

SELECT /* Supplier Site */ city || '-' || zip || '-' || decode(state, null, province, state) || '-' || country postcode_city_pk, city || '-' || decode(state, null, province, state) || '-' || country city_fk, zip || '-' || decode(state,null,province,state) || '-' || country postcode_fk, city || '(' || zip || ',' || decode(state, null, province, state) || ')' postcode_city_dp, city || '(' || zip || ',' || decode(state, null, province, state) || ')' name, eli.instance_code instance, pvs.creation_date creation_date, pvs.last_update_date last_update_date, NULL user_attribute1, NULL user_attribute2, NULL user_attribute3, NULL user_attribute4, NULL user_attribute5 from po_vendor_sites_all pvs, edw_local_instance eli union all /* HR_LOCATIONS EXTRACT */ select town_or_city || '-' || postal_code || '-' || region_2 || '-' || country postcode_city_pk, town_or_city || '-' || region_2 || '-' || country city_fk, postal_code || '-' || region_2 || '-' || country postcode_fk, town_or_city || '(' || postal_code || ',' || region_2 || ')' postcode_city_dp, town_or_city || '(' || postal_code || ',' || region_2 || ')' name, eli.instance_code instance, hrl.creation_date creation_date, hrl.last_update_date last_update_date, NULL user_attribute1, NULL user_attribute2, NULL user_attribute3, NULL user_attribute4, NULL user_attribute5 from hr_locations_all hrl, edw_local_instance eli union all /* RA_ADDRESSES (customers, HZ_LOCATIONS) EXTRACT */ select city || '-' || substrb(postal_code, 1,57) || '-' || decode(state, null, province, state) || '-' || country postcode_city_pk, city || '-' || decode(state, null, province, state) || '-' || country city_fk, postal_code || '-' || decode(state,null,province,state) || '-' || country postcode_fk, city || '(' || postal_code || ',' || decode(state,null, province, state) || ')' postcode_city_dp, city || '(' || postal_code || ',' || decode(state,null, province, state) || ')' name, eli.instance_code instance, hlo.creation_date creation_date, hlo.last_update_date last_update_date, NULL user_attribute1, NULL user_attribute2, NULL user_attribute3, NULL user_attribute4, NULL user_attribute5 from HZ_LOCATIONS hlo, edw_local_instance eli

View Text - HTML Formatted

SELECT /* SUPPLIER SITE */ CITY || '-' || ZIP || '-' || DECODE(STATE
, NULL
, PROVINCE
, STATE) || '-' || COUNTRY POSTCODE_CITY_PK
, CITY || '-' || DECODE(STATE
, NULL
, PROVINCE
, STATE) || '-' || COUNTRY CITY_FK
, ZIP || '-' || DECODE(STATE
, NULL
, PROVINCE
, STATE) || '-' || COUNTRY POSTCODE_FK
, CITY || '(' || ZIP || '
, ' || DECODE(STATE
, NULL
, PROVINCE
, STATE) || ')' POSTCODE_CITY_DP
, CITY || '(' || ZIP || '
, ' || DECODE(STATE
, NULL
, PROVINCE
, STATE) || ')' NAME
, ELI.INSTANCE_CODE INSTANCE
, PVS.CREATION_DATE CREATION_DATE
, PVS.LAST_UPDATE_DATE LAST_UPDATE_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM PO_VENDOR_SITES_ALL PVS
, EDW_LOCAL_INSTANCE ELI UNION ALL /* HR_LOCATIONS EXTRACT */ SELECT TOWN_OR_CITY || '-' || POSTAL_CODE || '-' || REGION_2 || '-' || COUNTRY POSTCODE_CITY_PK
, TOWN_OR_CITY || '-' || REGION_2 || '-' || COUNTRY CITY_FK
, POSTAL_CODE || '-' || REGION_2 || '-' || COUNTRY POSTCODE_FK
, TOWN_OR_CITY || '(' || POSTAL_CODE || '
, ' || REGION_2 || ')' POSTCODE_CITY_DP
, TOWN_OR_CITY || '(' || POSTAL_CODE || '
, ' || REGION_2 || ')' NAME
, ELI.INSTANCE_CODE INSTANCE
, HRL.CREATION_DATE CREATION_DATE
, HRL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM HR_LOCATIONS_ALL HRL
, EDW_LOCAL_INSTANCE ELI UNION ALL /* RA_ADDRESSES (CUSTOMERS
, HZ_LOCATIONS) EXTRACT */ SELECT CITY || '-' || SUBSTRB(POSTAL_CODE
, 1
, 57) || '-' || DECODE(STATE
, NULL
, PROVINCE
, STATE) || '-' || COUNTRY POSTCODE_CITY_PK
, CITY || '-' || DECODE(STATE
, NULL
, PROVINCE
, STATE) || '-' || COUNTRY CITY_FK
, POSTAL_CODE || '-' || DECODE(STATE
, NULL
, PROVINCE
, STATE) || '-' || COUNTRY POSTCODE_FK
, CITY || '(' || POSTAL_CODE || '
, ' || DECODE(STATE
, NULL
, PROVINCE
, STATE) || ')' POSTCODE_CITY_DP
, CITY || '(' || POSTAL_CODE || '
, ' || DECODE(STATE
, NULL
, PROVINCE
, STATE) || ')' NAME
, ELI.INSTANCE_CODE INSTANCE
, HLO.CREATION_DATE CREATION_DATE
, HLO.LAST_UPDATE_DATE LAST_UPDATE_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM HZ_LOCATIONS HLO
, EDW_LOCAL_INSTANCE ELI