View: AHL_OWNER_LOCATIONS_V

Product:	AHL - Complex Maintenance Repair and Overhaul
Description:	This view stores the query to retrieve valid owners for an Unit Configuration.
Implementation/DBA Data:	APPS.AHL_OWNER_LOCATIONS_V

View Text

SELECT HZSITE.PARTY_SITE_ID OWNER_SITE_ID
, HZSITE.PARTY_ID OWNER_ID
, HZ.PARTY_NUMBER OWNER_NUMBER
, HZSITE.PARTY_SITE_NUMBER OWNER_SITE_NUMBER
, 'PARTY' PARTY_TYPE
, HZLOC.ADDRESS1 || DECODE(HZLOC.ADDRESS2
, NULL
, NULL
, ';'||HZLOC.ADDRESS2) || DECODE(HZLOC.ADDRESS3
, NULL
, NULL
, ';'||HZLOC.ADDRESS3) || DECODE(HZLOC.ADDRESS4
, NULL
, NULL
, ';'||HZLOC.ADDRESS4) || DECODE(HZLOC.CITY
, NULL
, NULL
, ';'|| HZLOC.CITY) || DECODE(HZLOC.POSTAL_CODE
, NULL
, NULL
, ';'||HZLOC.POSTAL_CODE) || DECODE(HZLOC.STATE
, NULL
, NULL
, ';'||HZLOC.STATE) || DECODE(HZLOC.PROVINCE
, NULL
, NULL
, ';'||HZLOC.PROVINCE) || HZLOC.COUNTRY ADDRESS
FROM HZ_PARTY_SITES HZSITE
, HZ_LOCATIONS HZLOC
, HZ_PARTIES HZ
WHERE HZSITE.LOCATION_ID = HZLOC.LOCATION_ID
AND HZSITE.PARTY_ID = HZ.PARTY_ID
AND HZSITE.STATUS <> 'I' UNION ALL SELECT PSITE.VENDOR_SITE_ID OWNER_SITE_ID
, PSITE.VENDOR_ID OWNER_ID
, PV.SEGMENT1 OWNER_NUMBER
, PSITE.VENDOR_SITE_CODE OWNER_SITE_NUMBER
, 'VENDOR' PARTY_TYPE
, DECODE(ADDRESS_LINE1
, NULL
, NULL
, ADDRESS_LINE1) || DECODE(ADDRESS_LINE2
, NULL
, NULL
, ';'||ADDRESS_LINE2) || DECODE(ADDRESS_LINE3
, NULL
, NULL
, ';'||ADDRESS_LINE3) || DECODE(CITY
, NULL
, NULL
, ';'||CITY) || DECODE(STATE
, NULL
, NULL
, ';'||STATE) || DECODE(ZIP
, NULL
, NULL
, ';'||ZIP) || DECODE(PROVINCE
, NULL
, NULL
, ';'||PROVINCE) || DECODE(COUNTRY
, NULL
, NULL
, ';'||COUNTRY) ADDRESS
FROM PO_VENDOR_SITES_ALL PSITE
, PO_VENDORS PV
WHERE PSITE.VENDOR_ID = PV.VENDOR_ID
AND TRUNC(SYSDATE) < TRUNC(NVL(INACTIVE_DATE
, SYSDATE+1))
AND NVL(ORG_ID
, NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTR(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)) = NVL(TO_NUMBER(DECODE(SUBSTR(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTR(USERENV('CLIENT_INFO')
, 1
, 10)))
, -99)

Columns