VIEW: APPS.AHL_OWNER_LOCATIONS_V

Source

View Text - Preformatted

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,mo_global.get_current_org_id()) = mo_global.get_current_org_id()

View Text - HTML Formatted

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
, MO_GLOBAL.GET_CURRENT_ORG_ID()) = MO_GLOBAL.GET_CURRENT_ORG_ID()