VIEW: APPS.ZX_FIRST_PARTY_ORGS_ALL_V

Source

View Text - Preformatted

SELECT
            PTP.PARTY_TAX_PROFILE_ID,
            xlep.NAME PARTY_NAME,
            PTP.PARTY_TYPE_CODE,
            hrl.ADDRESS_LINE_1,
            hrl.ADDRESS_LINE_2,
            hrl.ADDRESS_LINE_3,
            hrl.TOWN_OR_CITY,
            hrl.REGION_1,
            hrl.REGION_2,
            hrl.POSTAL_CODE,
            hrl.COUNTRY AS COUNTRY_CODE,
            PTP.USE_LE_AS_SUBSCRIBER_FLAG
          FROM ZX_PARTY_TAX_PROFILE PTP,
                HZ_PARTIES HZP,
                XLE_entity_profiles xlep,
                xle_registrations xler,
                hr_locations_all hrl
          WHERE PTP.PARTY_ID = HZP.PARTY_ID
            AND PTP.PARTY_TYPE_CODE IN ('FIRST_PARTY')
            AND hzp.party_id = xlep.party_id
            AND xlep.legal_entity_id = xler.source_id
            AND xler.source_table = 'XLE_ENTITY_PROFILES'
            AND NVL(xler.IDENTIFYING_FLAG,'N') = 'Y'
            AND xler.location_id = hrl.location_id
          UNION ALL
            SELECT PTP.PARTY_TAX_PROFILE_ID,
                  HOU.NAME  PARTY_NAME,
                  PTP.PARTY_TYPE_CODE,
                  hrloc.ADDRESS_LINE_1,
                  hrloc.ADDRESS_LINE_2,
                  hrloc.ADDRESS_LINE_3,
                  hrloc.TOWN_OR_CITY,
                  hrloc.REGION_1,
                  hrloc.REGION_2,
                  hrloc.POSTAL_CODE,
                  hrloc.COUNTRY AS COUNTRY_CODE,
                  PTP.USE_LE_AS_SUBSCRIBER_FLAG
            FROM ZX_PARTY_TAX_PROFILE PTP,
                  HR_OPERATING_UNITS HOU,
                  HR_ALL_Organization_units hro,
                  HR_Locations_all hrloc
            WHERE
                PTP.PARTY_TYPE_CODE = 'OU'
                AND PTP.PARTY_ID = HOU.ORGANIZATION_ID
                AND HOU.organization_id = hro.organization_id
                AND hro.location_id = hrloc.location_id
          UNION ALL
            SELECT
                 PTP.PARTY_TAX_PROFILE_ID,
                 LU.MEANING PARTY_NAME,
                 PTP.PARTY_TYPE_CODE,
                 '',
                 '',
                 '',
                 '',
                 '',
                 '',
                 '',
                 '',
                 PTP.USE_LE_AS_SUBSCRIBER_FLAG
               FROM ZX_PARTY_TAX_PROFILE PTP,
                    FND_LOOKUPS LU
               WHERE  PTP.PARTY_TYPE_CODE = 'GCO'
                  AND PTP.PARTY_TYPE_CODE = LU.LOOKUP_CODE
                  AND LU.LOOKUP_TYPE = 'ZX_PTP_PARTY_TYPE'

View Text - HTML Formatted

SELECT PTP.PARTY_TAX_PROFILE_ID
, XLEP.NAME PARTY_NAME
, PTP.PARTY_TYPE_CODE
, HRL.ADDRESS_LINE_1
, HRL.ADDRESS_LINE_2
, HRL.ADDRESS_LINE_3
, HRL.TOWN_OR_CITY
, HRL.REGION_1
, HRL.REGION_2
, HRL.POSTAL_CODE
, HRL.COUNTRY AS COUNTRY_CODE
, PTP.USE_LE_AS_SUBSCRIBER_FLAG
FROM ZX_PARTY_TAX_PROFILE PTP
, HZ_PARTIES HZP
, XLE_ENTITY_PROFILES XLEP
, XLE_REGISTRATIONS XLER
, HR_LOCATIONS_ALL HRL
WHERE PTP.PARTY_ID = HZP.PARTY_ID
AND PTP.PARTY_TYPE_CODE IN ('FIRST_PARTY')
AND HZP.PARTY_ID = XLEP.PARTY_ID
AND XLEP.LEGAL_ENTITY_ID = XLER.SOURCE_ID
AND XLER.SOURCE_TABLE = 'XLE_ENTITY_PROFILES'
AND NVL(XLER.IDENTIFYING_FLAG
, 'N') = 'Y'
AND XLER.LOCATION_ID = HRL.LOCATION_ID UNION ALL SELECT PTP.PARTY_TAX_PROFILE_ID
, HOU.NAME PARTY_NAME
, PTP.PARTY_TYPE_CODE
, HRLOC.ADDRESS_LINE_1
, HRLOC.ADDRESS_LINE_2
, HRLOC.ADDRESS_LINE_3
, HRLOC.TOWN_OR_CITY
, HRLOC.REGION_1
, HRLOC.REGION_2
, HRLOC.POSTAL_CODE
, HRLOC.COUNTRY AS COUNTRY_CODE
, PTP.USE_LE_AS_SUBSCRIBER_FLAG
FROM ZX_PARTY_TAX_PROFILE PTP
, HR_OPERATING_UNITS HOU
, HR_ALL_ORGANIZATION_UNITS HRO
, HR_LOCATIONS_ALL HRLOC WHERE PTP.PARTY_TYPE_CODE = 'OU'
AND PTP.PARTY_ID = HOU.ORGANIZATION_ID
AND HOU.ORGANIZATION_ID = HRO.ORGANIZATION_ID
AND HRO.LOCATION_ID = HRLOC.LOCATION_ID UNION ALL SELECT PTP.PARTY_TAX_PROFILE_ID
, LU.MEANING PARTY_NAME
, PTP.PARTY_TYPE_CODE
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, ''
, PTP.USE_LE_AS_SUBSCRIBER_FLAG
FROM ZX_PARTY_TAX_PROFILE PTP
, FND_LOOKUPS LU
WHERE PTP.PARTY_TYPE_CODE = 'GCO'
AND PTP.PARTY_TYPE_CODE = LU.LOOKUP_CODE
AND LU.LOOKUP_TYPE = 'ZX_PTP_PARTY_TYPE'