VIEW: APPS.FND_MO_REPORTING_ENTITIES_V

Source

View Text - Preformatted

SELECT '1000'                      reporting_level,
           gl.name                    entity_name,
           gl.ledger_id               entity_id,
           -9999                      operating_unit_id,
           -9999                      legal_entity_id,
           gl.ledger_id               ledger_id,
           gl.currency_code           currency_code,
           gl.ledger_category_code    ledger_category_code,
           lc.completion_status_code  completion_status_code
     FROM gl_ledgers_public_all_v gl,
          gl_ledgers lg,
          gl_ledger_configurations lc
    WHERE gl.ledger_id = lg.ledger_id
      AND lg.configuration_id = lc.configuration_id
      AND 0 IN (
            SELECT  DECODE(mo_global.check_access(oi1.organization_id),
                               'Y', 0, 1)
              FROM  hr_organization_information oi1,
                    hr_organization_information oi2
             WHERE  oi1.org_information_context = 'CLASS'
               AND  oi1.org_information1 = 'OPERATING_UNIT'
               AND  oi1.org_information2 = 'Y'
               AND  oi1.organization_id = oi2.organization_id
               AND  oi2.org_information_context = 'Operating Unit Information'
               AND  oi2.org_information3 = TO_CHAR(gl.ledger_id))
   UNION ALL
   /* ALC and Secondary Ledgers */
   SELECT '1000'                     reporting_level,
          gl.name                    entity_name,
          gl.ledger_id               entity_id,
          -9999                      operating_unit_id,
          -9999                      legal_entity_id,
          gl.ledger_id               ledger_id,
          gl.currency_code           currency_code,
          gl.ledger_category_code    ledger_category_code,
          lc.completion_status_code  completion_status_code
     FROM  gl_ledgers_public_all_v gl,
           gl_ledger_configurations lc
    WHERE gl.configuration_id = lc.configuration_id
      AND gl.ledger_id IN (
             SELECT lr.target_ledger_id
               FROM gl_ledger_relationships lr
              WHERE lr.target_ledger_category_code    IN ('ALC','SECONDARY')
                AND lr.relationship_enabled_flag = 'Y'
                AND lr.relationship_type_code = 'SUBLEDGER'
                AND 0 IN (
                  SELECT DECODE(mo_global.check_access(oi1.organization_id),
                             'Y',0,1)
                  FROM hr_organization_information oi1,
                       hr_organization_information oi2
                  WHERE oi1.org_information_context = 'CLASS'
                  AND oi1.org_information1 = 'OPERATING_UNIT'
                  AND oi1.org_information2 = 'Y'
                  AND oi1.organization_id = oi2.organization_id
                  AND oi2.org_information_context = 'Operating Unit Information'
                  AND to_number(oi2.org_information3) = lr.primary_ledger_id))
UNION ALL
/* Added on Jan 13, 06 for Legal Entity */
SELECT '2000'                        reporting_level, 
        lep.name                     entity_name, 
        lep.legal_entity_id          entity_id, 
        -9999                        operating_unit_id, 
        lep.legal_entity_id          legal_entity_id, 
        -9999                        ledger_id, 
        null                         currency_code, 
        null                         ledger_category_code, 
        NULL                         completion_status_code 
   FROM xle_entity_profiles lep 
   WHERE transacting_entity_flag = 'Y'   
UNION ALL
/* Ledger by Security Profile */
SELECT '2500'                      reporting_level,
       gl.name                     entity_name,
       gl.ledger_id                entity_id,
       -9999                       operating_unit_id,
       -9999                       legal_entity_id,
       gl.ledger_id                ledger_id,
       gl.currency_code            currency_code,
       gl.ledger_category_code     ledger_category_code,
       lc.completion_status_code   completion_status_code
  FROM  gl_ledgers_public_all_v gl,
        gl_ledger_configurations lc
 WHERE gl.configuration_id = lc.configuration_id
UNION ALL
/* Operating Units */
SELECT '3000'                       reporting_level,
        o.name                       entity_name,
        o.organization_id            entity_id,
        o.organization_id            operating_unit_id,
        -9999                        legal_entity_id,
        to_number(o.set_of_books_id) ledger_id,
        null                         currency_code,
        null                         ledger_category_code,
        NULL                         completion_status_code
    FROM hr_operating_units o
   WHERE mo_global.check_access(o.organization_id) = 'Y'
   

View Text - HTML Formatted

SELECT '1000' REPORTING_LEVEL
, GL.NAME ENTITY_NAME
, GL.LEDGER_ID ENTITY_ID
, -9999 OPERATING_UNIT_ID
, -9999 LEGAL_ENTITY_ID
, GL.LEDGER_ID LEDGER_ID
, GL.CURRENCY_CODE CURRENCY_CODE
, GL.LEDGER_CATEGORY_CODE LEDGER_CATEGORY_CODE
, LC.COMPLETION_STATUS_CODE COMPLETION_STATUS_CODE
FROM GL_LEDGERS_PUBLIC_ALL_V GL
, GL_LEDGERS LG
, GL_LEDGER_CONFIGURATIONS LC
WHERE GL.LEDGER_ID = LG.LEDGER_ID
AND LG.CONFIGURATION_ID = LC.CONFIGURATION_ID
AND 0 IN ( SELECT DECODE(MO_GLOBAL.CHECK_ACCESS(OI1.ORGANIZATION_ID)
, 'Y'
, 0
, 1)
FROM HR_ORGANIZATION_INFORMATION OI1
, HR_ORGANIZATION_INFORMATION OI2
WHERE OI1.ORG_INFORMATION_CONTEXT = 'CLASS'
AND OI1.ORG_INFORMATION1 = 'OPERATING_UNIT'
AND OI1.ORG_INFORMATION2 = 'Y'
AND OI1.ORGANIZATION_ID = OI2.ORGANIZATION_ID
AND OI2.ORG_INFORMATION_CONTEXT = 'OPERATING UNIT INFORMATION'
AND OI2.ORG_INFORMATION3 = TO_CHAR(GL.LEDGER_ID)) UNION ALL /* ALC
AND SECONDARY LEDGERS */ SELECT '1000' REPORTING_LEVEL
, GL.NAME ENTITY_NAME
, GL.LEDGER_ID ENTITY_ID
, -9999 OPERATING_UNIT_ID
, -9999 LEGAL_ENTITY_ID
, GL.LEDGER_ID LEDGER_ID
, GL.CURRENCY_CODE CURRENCY_CODE
, GL.LEDGER_CATEGORY_CODE LEDGER_CATEGORY_CODE
, LC.COMPLETION_STATUS_CODE COMPLETION_STATUS_CODE
FROM GL_LEDGERS_PUBLIC_ALL_V GL
, GL_LEDGER_CONFIGURATIONS LC
WHERE GL.CONFIGURATION_ID = LC.CONFIGURATION_ID
AND GL.LEDGER_ID IN ( SELECT LR.TARGET_LEDGER_ID
FROM GL_LEDGER_RELATIONSHIPS LR
WHERE LR.TARGET_LEDGER_CATEGORY_CODE IN ('ALC'
, 'SECONDARY')
AND LR.RELATIONSHIP_ENABLED_FLAG = 'Y'
AND LR.RELATIONSHIP_TYPE_CODE = 'SUBLEDGER'
AND 0 IN ( SELECT DECODE(MO_GLOBAL.CHECK_ACCESS(OI1.ORGANIZATION_ID)
, 'Y'
, 0
, 1)
FROM HR_ORGANIZATION_INFORMATION OI1
, HR_ORGANIZATION_INFORMATION OI2
WHERE OI1.ORG_INFORMATION_CONTEXT = 'CLASS'
AND OI1.ORG_INFORMATION1 = 'OPERATING_UNIT'
AND OI1.ORG_INFORMATION2 = 'Y'
AND OI1.ORGANIZATION_ID = OI2.ORGANIZATION_ID
AND OI2.ORG_INFORMATION_CONTEXT = 'OPERATING UNIT INFORMATION'
AND TO_NUMBER(OI2.ORG_INFORMATION3) = LR.PRIMARY_LEDGER_ID)) UNION ALL /* ADDED ON JAN 13
, 06 FOR LEGAL ENTITY */ SELECT '2000' REPORTING_LEVEL
, LEP.NAME ENTITY_NAME
, LEP.LEGAL_ENTITY_ID ENTITY_ID
, -9999 OPERATING_UNIT_ID
, LEP.LEGAL_ENTITY_ID LEGAL_ENTITY_ID
, -9999 LEDGER_ID
, NULL CURRENCY_CODE
, NULL LEDGER_CATEGORY_CODE
, NULL COMPLETION_STATUS_CODE
FROM XLE_ENTITY_PROFILES LEP
WHERE TRANSACTING_ENTITY_FLAG = 'Y' UNION ALL /* LEDGER BY SECURITY PROFILE */ SELECT '2500' REPORTING_LEVEL
, GL.NAME ENTITY_NAME
, GL.LEDGER_ID ENTITY_ID
, -9999 OPERATING_UNIT_ID
, -9999 LEGAL_ENTITY_ID
, GL.LEDGER_ID LEDGER_ID
, GL.CURRENCY_CODE CURRENCY_CODE
, GL.LEDGER_CATEGORY_CODE LEDGER_CATEGORY_CODE
, LC.COMPLETION_STATUS_CODE COMPLETION_STATUS_CODE
FROM GL_LEDGERS_PUBLIC_ALL_V GL
, GL_LEDGER_CONFIGURATIONS LC
WHERE GL.CONFIGURATION_ID = LC.CONFIGURATION_ID UNION ALL /* OPERATING UNITS */ SELECT '3000' REPORTING_LEVEL
, O.NAME ENTITY_NAME
, O.ORGANIZATION_ID ENTITY_ID
, O.ORGANIZATION_ID OPERATING_UNIT_ID
, -9999 LEGAL_ENTITY_ID
, TO_NUMBER(O.SET_OF_BOOKS_ID) LEDGER_ID
, NULL CURRENCY_CODE
, NULL LEDGER_CATEGORY_CODE
, NULL COMPLETION_STATUS_CODE
FROM HR_OPERATING_UNITS O
WHERE MO_GLOBAL.CHECK_ACCESS(O.ORGANIZATION_ID) = 'Y'