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'
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'
|
|
|