View: EDW_GEOG_LOCATION_LCV

View Text

SELECT PVS.VENDOR_SITE_ID || '-' || PVS.ORG_ID || '-' || ELI.INSTANCE_CODE || '-' || 'SUPPLIER_SITE' LOCATION_PK
, PVS.CITY || '-'|| PVS.ZIP || '-' || DECODE(PVS.STATE
, NULL
, PVS.PROVINCE
, PVS.STATE) || '-' || PVS.COUNTRY POSTCODE_CITY_FK
, PVS.ADDRESS_LINE1 ADDRESS_LINE_1
, PVS.ADDRESS_LINE2 ADDRESS_LINE_2
, PVS.ADDRESS_LINE3 ADDRESS_LINE_3
, NULL ADDRESS_LINE_4
, PVS.VENDOR_SITE_CODE || '(' || POV.VENDOR_NAME || ')' LOCATION_DP
, PVS.VENDOR_SITE_CODE || '(' || POV.VENDOR_NAME || ')' NAME
, ELI.INSTANCE_CODE INSTANCE
, PVS.VENDOR_SITE_ID LOCATION_ID
, 'SUPPLIER_SITE' LOCATION_TYPE
, PVS.ORG_ID ORG_ID
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
, PVS.CREATION_DATE CREATION_DATE
, GREATEST(PVS.LAST_UPDATE_DATE
, POV.LAST_UPDATE_DATE) LAST_UPDATE_DATE
FROM EDW_LOCAL_INSTANCE ELI
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL PVS
WHERE PVS.VENDOR_ID = POV.VENDOR_ID UNION ALL /* HR_LOCATIONS EXTRACT
, USE HR_LOCATIONS_ALL_TL FOR CODE*/ SELECT LOCATION_ID || '-' || ELI.INSTANCE_CODE || '-' || 'HR_LOC' LOCATION_PK
, TOWN_OR_CITY || '-' || POSTAL_CODE || '-' || REGION_2 || '-' || COUNTRY POSTCODE_CODE_FK
, ADDRESS_LINE_1 ADDRESS_LINE_1
, ADDRESS_LINE_2 ADDRESS_LINE_2
, ADDRESS_LINE_3 ADDRESS_LINE_3
, NULL ADDRESS_LINE4
, LOCATION_CODE LOCATION_DP
, LOCATION_CODE NAME
, ELI.INSTANCE_CODE INSTANCE
, LOCATION_ID LOCATION_ID
, 'HR_LOCATION' LOCATION_TYPE
, TO_NUMBER(NULL) ORG_ID
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
, HRL.CREATION_DATE CREATION_DATE
, HRL.LAST_UPDATE_DATE LAST_UPDATE_DATE
FROM EDW_LOCAL_INSTANCE ELI
, HR_LOCATIONS_ALL HRL UNION ALL /* PARTY SITES
, HZ_PARTY_SITES EXTRACT */ SELECT HZPS.PARTY_SITE_ID || '-' || ELI.INSTANCE_CODE || '-' || 'PARTY_SITE' LOCATION_PK
, HZL.CITY || '-' || SUBSTRB(HZL.POSTAL_CODE
, 1
, 57) || '-' || DECODE(HZL.STATE
, NULL
, HZL.PROVINCE
, HZL.STATE) || '-' || HZL.COUNTRY POSTCODE_CITY_FK
, HZL.ADDRESS1 ADDRESS_LINE1
, HZL.ADDRESS2 ADDRESS_LINE2
, HZL.ADDRESS3 ADDRESS_LINE3
, HZL.ADDRESS4 ADDRESS_LINE4
, SUBSTRB(HZPS.PARTY_SITE_NAME
, 1
, 100) || '(' || SUBSTRB(HZP.PARTY_NAME
, 1
, 100) || ')' LOCATION_DP
, SUBSTRB(HZPS.PARTY_SITE_NAME
, 1
, 100) || '(' || SUBSTRB(HZP.PARTY_NAME
, 1
, 100) || ')' NAME
, ELI.INSTANCE_CODE INSTANCE
, HZPS.PARTY_SITE_ID LOCATION_ID
, 'PARTY_SITE' LOCATION_TYPE
, TO_NUMBER(NULL) ORG_ID
, NULL USER_ATTRIBUTE1
, NULL USER_ATTRIBUTE2
, NULL USER_ATTRIBUTE3
, NULL USER_ATTRIBUTE4
, NULL USER_ATTRIBUTE5
, HZPS.CREATION_DATE CREATION_DATE
, GREATEST(HZPS.LAST_UPDATE_DATE
, HZP.LAST_UPDATE_DATE
, HZL.LAST_UPDATE_DATE) LAST_UPDATE_DATE
FROM EDW_LOCAL_INSTANCE ELI
, HZ_LOCATIONS HZL
, HZ_PARTIES HZP
, HZ_PARTY_SITES HZPS
WHERE HZPS.LOCATION_ID = HZL.LOCATION_ID
AND HZPS.PARTY_ID = HZP.PARTY_ID

Columns