VIEW: APPS.EDW_GEOG_STATE_REGION_LCV

Source

View Text - Preformatted

SELECT /* Supplier Site */ decode(state,null,province,state) ||'-' || country || '-' || 'STAT' state_region_pk, decode(state,null,province,state) ||'-' || country state_fk, decode(state,null,province,state) || '(' || country || ')' state_region_dp, decode(state,null,province,state) || '(' || country || ')' 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 region_2 ||'-' || country ||'-'|| 'STAT' state_region_pk, region_2 ||'-' || country state_fk, region_2 || '(' || country || ')' state_region_dp, region_2 || '(' || country || ')' 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 decode(state,null,province,state) ||'-' || country || '-' || 'STAT' state_region_pk, decode(state,null,province,state) ||'-' || country state_fk, decode(state,null,province,state) || '(' || country || ')' state_region_dp, decode(state,null,province,state) || '(' || country || ')' name, eli.instance_code instance, hzl.creation_date creation_date, hzl.last_update_date last_update_date, NULL user_attribute1, NULL user_attribute2, NULL user_attribute3, NULL user_attribute4, NULL user_attribute5 from HZ_LOCATIONS hzl, edw_local_instance eli

View Text - HTML Formatted

SELECT /* SUPPLIER SITE */ DECODE(STATE
, NULL
, PROVINCE
, STATE) ||'-' || COUNTRY || '-' || 'STAT' STATE_REGION_PK
, DECODE(STATE
, NULL
, PROVINCE
, STATE) ||'-' || COUNTRY STATE_FK
, DECODE(STATE
, NULL
, PROVINCE
, STATE) || '(' || COUNTRY || ')' STATE_REGION_DP
, DECODE(STATE
, NULL
, PROVINCE
, STATE) || '(' || COUNTRY || ')' 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 REGION_2 ||'-' || COUNTRY ||'-'|| 'STAT' STATE_REGION_PK
, REGION_2 ||'-' || COUNTRY STATE_FK
, REGION_2 || '(' || COUNTRY || ')' STATE_REGION_DP
, REGION_2 || '(' || COUNTRY || ')' 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 DECODE(STATE
, NULL
, PROVINCE
, STATE) ||'-' || COUNTRY || '-' || 'STAT' STATE_REGION_PK
, DECODE(STATE
, NULL
, PROVINCE
, STATE) ||'-' || COUNTRY STATE_FK
, DECODE(STATE
, NULL
, PROVINCE
, STATE) || '(' || COUNTRY || ')' STATE_REGION_DP
, DECODE(STATE
, NULL
, PROVINCE
, STATE) || '(' || COUNTRY || ')' NAME
, ELI.INSTANCE_CODE INSTANCE
, HZL.CREATION_DATE CREATION_DATE
, HZL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
FROM HZ_LOCATIONS HZL
, EDW_LOCAL_INSTANCE ELI