[Home] [Help]
View: XLE_FIRSTPARTY_INFORMATION_V
View Text
SELECT LEP.LEGAL_ENTITY_ID
,
LEP.LE_INFORMATION_CONTEXT
,
LEP.NAME
,
LEP.PARTY_ID
,
LEP.LEGAL_ENTITY_IDENTIFIER
,
LEP.ACTIVITY_CODE
,
LEP.SUB_ACTIVITY_CODE
,
LEP.TYPE_OF_COMPANY
,
(SELECT FLEX_ACTIVE.FLEX_VALUE_SET_NAME
FROM FND_DESCR_FLEX_COLUMN_USAGES COL_USAGE
,
FND_FLEX_VALUE_SETS FLEX_ACTIVE
WHERE COL_USAGE.APPLICATION_ID = 204
AND COL_USAGE.DESCRIPTIVE_FLEXFIELD_NAME = 'XLE_LE_INFO_DEV'
AND LEP.LE_INFORMATION_CONTEXT = COL_USAGE.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND COL_USAGE.APPLICATION_COLUMN_NAME = 'ACTIVITY_CODE'
AND COL_USAGE.FLEX_VALUE_SET_ID = FLEX_ACTIVE.FLEX_VALUE_SET_ID ) ACTIVITY_CATEGORY
,
(SELECT LKP.MEANING
FROM AR_LOOKUPS LKP
WHERE LKP.LOOKUP_TYPE = (SELECT FLEX_ACTIVE.FLEX_VALUE_SET_NAME
FROM FND_DESCR_FLEX_COLUMN_USAGES COL_USAGE
,
FND_FLEX_VALUE_SETS FLEX_ACTIVE
WHERE COL_USAGE.APPLICATION_ID = 204
AND COL_USAGE.DESCRIPTIVE_FLEXFIELD_NAME = 'XLE_LE_INFO_DEV'
AND LEP.LE_INFORMATION_CONTEXT = COL_USAGE.DESCRIPTIVE_FLEX_CONTEXT_CODE
AND COL_USAGE.APPLICATION_COLUMN_NAME = 'ACTIVITY_CODE'
AND COL_USAGE.FLEX_VALUE_SET_ID = FLEX_ACTIVE.FLEX_VALUE_SET_ID )
AND LKP.LOOKUP_CODE = LEP.ACTIVITY_CODE) ACTIVITY_DESCRIPTION
,
LEP.EFFECTIVE_FROM LE_EFFECTIVE_FROM
,
LEP.EFFECTIVE_TO LE_EFFECTIVE_TO
,
LEP.ATTRIBUTE_CATEGORY
,
LEP.ATTRIBUTE1
,
LEP.ATTRIBUTE2
,
LEP.ATTRIBUTE3
,
LEP.ATTRIBUTE4
,
LEP.ATTRIBUTE5
,
LEP.ATTRIBUTE6
,
LEP.ATTRIBUTE7
,
LEP.ATTRIBUTE8
,
LEP.ATTRIBUTE9
,
LEP.ATTRIBUTE10
,
LEP.ATTRIBUTE11
,
LEP.ATTRIBUTE12
,
LEP.ATTRIBUTE13
,
LEP.ATTRIBUTE14
,
LEP.ATTRIBUTE15
,
LEP.ATTRIBUTE16
,
LEP.ATTRIBUTE17
,
LEP.ATTRIBUTE18
,
LEP.ATTRIBUTE19
,
LEP.ATTRIBUTE20
,
LEP.GEOGRAPHY_ID
,
REG.REGISTRATION_NUMBER
,
JUR.LEGISLATIVE_CAT_CODE LEGISLATIVE_CAT_CODE
,
REG.EFFECTIVE_FROM
,
REG.EFFECTIVE_TO
,
REG.LOCATION_ID
,
HRL.STYLE ADDRESS_STYLE
,
HRL.ADDRESS_LINE_1
,
HRL.ADDRESS_LINE_2
,
HRL.ADDRESS_LINE_3
,
HRL.TOWN_OR_CITY
,
HRL.REGION_1
,
HRL.REGION_2
,
HRL.REGION_3
,
HRL.POSTAL_CODE
,
HRL.COUNTRY
FROM XLE_ENTITY_PROFILES LEP
,
XLE_REGISTRATIONS REG
,
XLE_JURISDICTIONS_B JUR
,
HR_LOCATIONS_ALL HRL
WHERE
LEP.TRANSACTING_ENTITY_FLAG = 'Y'
AND LEP.LEGAL_ENTITY_ID = REG.SOURCE_ID
AND REG.SOURCE_TABLE = 'XLE_ENTITY_PROFILES'
AND REG.IDENTIFYING_FLAG = 'Y'
AND JUR.JURISDICTION_ID = REG.JURISDICTION_ID
AND HRL.LOCATION_ID = REG.LOCATION_ID