VIEW: APPS.EDW_GEOG_LOCATION_LCV

Source

View Text - Preformatted

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

View Text - HTML Formatted

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