[Home] [Help]
View: XLE_LE_OU_LEDGER_V
View Text
SELECT O2.ORGANIZATION_ID OPERATING_UNIT_ID
,
LG.LEDGER_ID
,
LG.NAME LEDGER_NAME
,
LG.SHORT_NAME LEDGER_SHORT_NAME
,
CFGDET.OBJECT_ID LEGAL_ENTITY_ID
,
XLEP.NAME LEGAL_ENTITY_NAME
,
XLEP.LEGAL_ENTITY_IDENTIFIER
,
XLEP.ACTIVITY_CODE
,
XLEP.SUB_ACTIVITY_CODE
,
XLEP.TYPE_OF_COMPANY
,
XLEP.EFFECTIVE_FROM LE_EFFECTIVE_FROM
,
XLEP.EFFECTIVE_TO LE_EFFECTIVE_TO
,
REG.REGISTRATION_NUMBER
,
HRL.ADDRESS_LINE_1
,
HRL.ADDRESS_LINE_2
,
HRL.ADDRESS_LINE_3
,
HRL.REGION_1
,
HRL.REGION_2
,
HRL.REGION_3
,
HRL.TOWN_OR_CITY
,
HRL.POSTAL_CODE
,
HRL.COUNTRY
FROM GL_LEDGERS PRIMARYLG
,
GL_LEDGERS LG
,
GL_LEDGER_RELATIONSHIPS RS
,
GL_LEDGER_CONFIGURATIONS CFG
,
GL_LEDGER_CONFIG_DETAILS CFGDET
,
XLE_ENTITY_PROFILES XLEP
,
XLE_REGISTRATIONS REG
,
HR_LOCATIONS_ALL HRL
,
HR_ORGANIZATION_INFORMATION O2
,
HR_ORGANIZATION_INFORMATION O3
WHERE RS.APPLICATION_ID = 101
AND ( ( RS.TARGET_LEDGER_CATEGORY_CODE = 'SECONDARY'
AND RS.RELATIONSHIP_TYPE_CODE <> 'NONE' )
OR ( RS.TARGET_LEDGER_CATEGORY_CODE = 'PRIMARY'
AND RS.RELATIONSHIP_TYPE_CODE = 'NONE' )
OR ( RS.TARGET_LEDGER_CATEGORY_CODE = 'ALC'
AND RS.RELATIONSHIP_TYPE_CODE IN ( 'JOURNAL'
, 'SUBLEDGER') ) )
AND LG.LEDGER_ID = RS.TARGET_LEDGER_ID
AND LG.LEDGER_CATEGORY_CODE = RS.TARGET_LEDGER_CATEGORY_CODE
AND PRIMARYLG.LEDGER_ID = RS.PRIMARY_LEDGER_ID
AND PRIMARYLG.LEDGER_CATEGORY_CODE = 'PRIMARY'
AND CFG.CONFIGURATION_ID = PRIMARYLG.CONFIGURATION_ID
AND CFGDET.CONFIGURATION_ID ( + ) = CFG.CONFIGURATION_ID
AND CFGDET.OBJECT_TYPE_CODE ( + ) = 'LEGAL_ENTITY'
AND CFGDET.OBJECT_ID = XLEP.LEGAL_ENTITY_ID
AND XLEP.LEGAL_ENTITY_ID = REG.SOURCE_ID
AND REG.SOURCE_TABLE = 'XLE_ENTITY_PROFILES'
AND REG.IDENTIFYING_FLAG = 'Y'
AND REG.LOCATION_ID = HRL.LOCATION_ID
AND O3.ORG_INFORMATION3 = TO_CHAR(LG.LEDGER_ID)
AND O3.ORGANIZATION_ID = O2.ORGANIZATION_ID
AND O2.ORG_INFORMATION_CONTEXT = 'CLASS'
AND O3.ORG_INFORMATION_CONTEXT = 'OPERATING UNIT INFORMATION'
AND O2.ORG_INFORMATION1 = 'OPERATING_UNIT'
AND O2.ORG_INFORMATION2 = 'Y'
AND XLEP.TRANSACTING_ENTITY_FLAG = 'Y'