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