View: XLA_MO_REPORTING_ENTITIES_V

Product:	XLA - Subledger Accounting
Description:
Implementation/DBA Data:	APPS.XLA_MO_REPORTING_ENTITIES_V

View Text

SELECT '1000' REPORTING_LEVEL
, SOB.NAME ENTITY_NAME
, SOB.SET_OF_BOOKS_ID ENTITY_ID
, -9999 OPERATING_UNIT_ID
, -9999 LEGAL_ENTITY_ID
, TO_NUMBER(OU.ORG_INFORMATION3) SET_OF_BOOKS_ID
FROM HR_ORGANIZATION_INFORMATION OU
, GL_SETS_OF_BOOKS SOB
WHERE SOB.SET_OF_BOOKS_ID = DECODE( LTRIM(OU.ORG_INFORMATION3
, '0123456789')
, NULL
, TO_NUMBER(OU.ORG_INFORMATION3)
, NULL )
AND OU.ORG_INFORMATION_CONTEXT = 'OPERATING UNIT INFORMATION'
AND OU.ORGANIZATION_ID = NVL( TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10) ) )
, -99 )
AND EXISTS (SELECT 1
FROM FND_PRODUCT_GROUPS PG
WHERE PG.MULTI_ORG_FLAG = 'Y') UNION ALL SELECT '1000' REPORTING_LEVEL
, SOB.NAME ENTITY_NAME
, SOB.SET_OF_BOOKS_ID ENTITY_ID
, -9999 OPERATING_UNIT_ID
, -9999 LEGAL_ENTITY_ID
, SOB.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
FROM GL_SETS_OF_BOOKS SOB
WHERE SOB.SET_OF_BOOKS_ID = FND_PROFILE.VALUE_WNPS('GL_SET_OF_BKS_ID')
AND EXISTS (SELECT 1
FROM FND_PRODUCT_GROUPS PG
WHERE NVL(PG.MULTI_ORG_FLAG
, 'N') = 'N') UNION ALL SELECT '2000' REPORTING_LEVEL
, O.NAME ENTITY_NAME
, O.ORGANIZATION_ID ENTITY_ID
, -9999 OPERATING_UNIT_ID
, LE.ORGANIZATION_ID LEGAL_ENTITY_ID
, TO_NUMBER(LE.ORG_INFORMATION1) SET_BOOKS_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL O
, HR_ORGANIZATION_INFORMATION OI
, HR_ORGANIZATION_INFORMATION LE
, HR_ORGANIZATION_INFORMATION OU
WHERE O.ORGANIZATION_ID = LE.ORGANIZATION_ID
AND O.ORGANIZATION_ID = OI.ORGANIZATION_ID
AND OI.ORG_INFORMATION_CONTEXT = 'CLASS'
AND OI.ORG_INFORMATION1 = 'HR_LEGAL'
AND OI.ORG_INFORMATION2 = 'Y'
AND LE.ORG_INFORMATION_CONTEXT = 'LEGAL ENTITY ACCOUNTING'
AND LE.ORG_INFORMATION1 = OU.ORG_INFORMATION3
AND OU.ORG_INFORMATION_CONTEXT = 'OPERATING UNIT INFORMATION'
AND OU.ORGANIZATION_ID = NVL( TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10) ) )
, -99 )
AND EXISTS (SELECT 1
FROM FND_PRODUCT_GROUPS PG
WHERE PG.MULTI_ORG_FLAG = 'Y')
AND O.LANGUAGE = USERENV('LANG') UNION ALL SELECT '3000' REPORTING_LEVEL
, O.NAME ENTITY_NAME
, O.ORGANIZATION_ID ENTITY_ID
, OU1.ORGANIZATION_ID OPERATING_UNIT_ID
, TO_NUMBER(OU1.ORG_INFORMATION2) LEGAL_ENTITY_ID
, TO_NUMBER(OU1.ORG_INFORMATION3) SET_OF_BOOKS_ID
FROM HR_ALL_ORGANIZATION_UNITS_TL O
, HR_ORGANIZATION_INFORMATION OI
, HR_ORGANIZATION_INFORMATION OU1
, HR_ORGANIZATION_INFORMATION OU2
WHERE O.ORGANIZATION_ID = OU1.ORGANIZATION_ID
AND O.ORGANIZATION_ID = OI.ORGANIZATION_ID
AND OI.ORG_INFORMATION_CONTEXT = 'CLASS'
AND OI.ORG_INFORMATION1 = 'OPERATING_UNIT'
AND OI.ORG_INFORMATION2 = 'Y'
AND OU1.ORG_INFORMATION_CONTEXT = 'OPERATING UNIT INFORMATION'
AND OU1.ORG_INFORMATION3 = OU2.ORG_INFORMATION3
AND OU2.ORG_INFORMATION_CONTEXT = 'OPERATING UNIT INFORMATION'
AND OU2.ORGANIZATION_ID = NVL( TO_NUMBER(DECODE( SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 1)
, ' '
, NULL
, SUBSTRB(USERENV('CLIENT_INFO')
, 1
, 10) ) )
, -99 )
AND EXISTS (SELECT 1
FROM FND_PRODUCT_GROUPS PG
WHERE PG.MULTI_ORG_FLAG = 'Y')
AND O.LANGUAGE = USERENV('LANG') UNION ALL SELECT '1000' REPORTING_LEVEL
, GSOB.NAME ENTITY_NAME
, GSOB.SET_OF_BOOKS_ID ENTITY_ID
, -9999 OPERATING_UNIT_ID
, -9999 LEGAL_ENTITY_ID
, GSOB.SET_OF_BOOKS_ID SET_OF_BOOKS_ID
FROM GL_SETS_OF_BOOKS GSOB
, GL_MC_BOOK_ASSIGNMENTS_11I GMBA
WHERE GMBA.PRIMARY_SET_OF_BOOKS_ID = FND_PROFILE.VALUE_WNPS('GL_SET_OF_BKS_ID')
AND GSOB.SET_OF_BOOKS_ID = GMBA.REPORTING_SET_OF_BOOKS_ID

Columns