View: ZX_FIRST_PARTY_ORGS_ALL_V

Product:	FND - Application Object Library
Description:
Implementation/DBA Data:	APPS.ZX_FIRST_PARTY_ORGS_ALL_V

View Text

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'