VIEW: APPS.XLA_MO_REPORTING_ENTITIES_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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