GL Accounts Highrarcy Query R12

/* Formatted on 10/3/2013 2:30:57 PM (QP5 v5.114.809.3010) */
  SELECT   DISTINCT
           ALEVEL.PARENT_FLEX_VALUE Alevel,
           (SELECT   DESCRIPTION
              FROM   FND_FLEX_VALUES_VL
             WHERE   FLEX_VALUE = ALEVEL.PARENT_FLEX_VALUE
                     AND FLEX_VALUE_SET_ID = 1014368)
              ALEVELDESCRIPTION,
           BLEVEL.PARENT_FLEX_VALUE Blevel,
           (SELECT   DESCRIPTION
              FROM   FND_FLEX_VALUES_VL
             WHERE   FLEX_VALUE = BLEVEL.PARENT_FLEX_VALUE
                     AND FLEX_VALUE_SET_ID = 1014368)
              BLEVELDESCRIPTION,
           CLEVEL.PARENT_FLEX_VALUE Clevel,
           (SELECT   DESCRIPTION
              FROM   FND_FLEX_VALUES_VL
             WHERE   FLEX_VALUE = CLEVEL.PARENT_FLEX_VALUE
                     AND FLEX_VALUE_SET_ID = 1014368)
              CLEVELDESCRIPTION,
           DLEVEL.PARENT_FLEX_VALUE Dlevel,
           (SELECT   DESCRIPTION
              FROM   FND_FLEX_VALUES_VL
             WHERE   FLEX_VALUE = DLEVEL.PARENT_FLEX_VALUE
                     AND FLEX_VALUE_SET_ID = 1014368)
              DlevelDescription,
           glbss.PERIOD_NAME PERIOD,
           GLCC.SEGMENT2 Costcenter,
           ffv2.description CostcenterDescription,
           GLCC.SEGMENT3 Subjective,
           ffv.description SubjectiveDescription,
           GLCC.SEGMENT4 Analysis,
           ffv1.Description AnalysisDescription,
           (SELECT   NVL (
                        SUM(GL_BALANCES.PERIOD_NET_DR
                            - GL_BALANCES.PERIOD_NET_CR),
                        0
                     )
              FROM   GL_BALANCES, GL_CODE_COMBINATIONS
             WHERE   ACTUAL_FLAG = 'A' AND PERIOD_YEAR = glbss.PERIOD_YEAR - 1
                     AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID =
                           GL_BALANCES.CODE_COMBINATION_ID
                     AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
                     AND GL_BALANCES.CODE_COMBINATION_ID =
                           glcc.CODE_COMBINATION_ID)
              PriorYearActual,
           (SELECT   NVL (
                        SUM(GL_BALANCES.PERIOD_NET_DR
                            - GL_BALANCES.PERIOD_NET_CR),
                        0
                     )
              FROM   GL_BALANCES, GL_CODE_COMBINATIONS
             WHERE       ACTUAL_FLAG = 'A'
                     AND PERIOD_NAME = glbss.PERIOD_NAME
                     AND PERIOD_YEAR = glbss.PERIOD_YEAR
                     AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID =
                           GL_BALANCES.CODE_COMBINATION_ID
                     AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
                     AND GL_BALANCES.CODE_COMBINATION_ID =
                           glcc.CODE_COMBINATION_ID)
              ThisPeriodActual,
           (SELECT   NVL (
                        SUM(  GL_BALANCES.BEGIN_BALANCE_DR
                            + GL_BALANCES.PERIOD_NET_DR
                            - GL_BALANCES.BEGIN_BALANCE_CR
                            - GL_BALANCES.PERIOD_NET_CR),
                        0
                     )
              FROM   GL_BALANCES, GL_CODE_COMBINATIONS, GL_BUDGET_VERSIONS
             WHERE       ACTUAL_FLAG = 'B'
                     AND PERIOD_YEAR = glbss.PERIOD_YEAR
                     AND PERIOD_NAME = glbss.PERIOD_NAME
                     AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID =
                           GL_BALANCES.CODE_COMBINATION_ID
                     AND GL_BALANCES.BUDGET_VERSION_ID =
                           GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
                     AND GL_BUDGET_VERSIONS.BUDGET_NAME = 'STC CURRENT'
                     AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
                     AND GL_BALANCES.CODE_COMBINATION_ID =
                           glcc.CODE_COMBINATION_ID)
              YTDCurrentBudget,
           (SELECT   NVL (
                        SUM(  GL_BALANCES.BEGIN_BALANCE_DR
                            + GL_BALANCES.PERIOD_NET_DR
                            - GL_BALANCES.BEGIN_BALANCE_CR
                            - GL_BALANCES.PERIOD_NET_CR),
                        0
                     )
              FROM   GL_BALANCES, GL_CODE_COMBINATIONS
             WHERE       ACTUAL_FLAG = 'A'
                     AND PERIOD_YEAR = glbss.PERIOD_YEAR
                     AND PERIOD_NAME = glbss.PERIOD_NAME
                     AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID =
                           GL_BALANCES.CODE_COMBINATION_ID
                     AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
                     AND GL_BALANCES.CODE_COMBINATION_ID =
                           glcc.CODE_COMBINATION_ID)
              YTDActual,
           ( (SELECT   NVL (
                          SUM(  GL_BALANCES.BEGIN_BALANCE_DR
                              + GL_BALANCES.PERIOD_NET_DR
                              - GL_BALANCES.BEGIN_BALANCE_CR
                              - GL_BALANCES.PERIOD_NET_CR),
                          0
                       )
                FROM   GL_BALANCES, GL_CODE_COMBINATIONS
               WHERE       ACTUAL_FLAG = 'A'
                       AND PERIOD_YEAR = glbss.PERIOD_YEAR
                       AND PERIOD_NAME = glbss.PERIOD_NAME
                       AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID =
                             GL_BALANCES.CODE_COMBINATION_ID
                       AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
                       AND GL_BALANCES.CODE_COMBINATION_ID =
                             glcc.CODE_COMBINATION_ID)
            - (SELECT   NVL (
                           SUM(  GL_BALANCES.BEGIN_BALANCE_DR
                               + GL_BALANCES.PERIOD_NET_DR
                               - GL_BALANCES.BEGIN_BALANCE_CR
                               - GL_BALANCES.PERIOD_NET_CR),
                           0
                        )
                 FROM   GL_BALANCES, GL_CODE_COMBINATIONS, GL_BUDGET_VERSIONS
                WHERE       ACTUAL_FLAG = 'B'
                        AND PERIOD_YEAR = glbss.PERIOD_YEAR
                        AND PERIOD_NAME = glbss.PERIOD_NAME
                        AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID =
                              GL_BALANCES.CODE_COMBINATION_ID
                        AND GL_BALANCES.BUDGET_VERSION_ID =
                              GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
                        AND GL_BUDGET_VERSIONS.BUDGET_NAME = 'STC CURRENT'
                        AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
                        AND GL_BALANCES.CODE_COMBINATION_ID =
                              glcc.CODE_COMBINATION_ID))
              YTDVariance,
           (SELECT   NVL (
                        SUM(GL_BALANCES.PERIOD_NET_DR
                            - GL_BALANCES.PERIOD_NET_CR),
                        0
                     )
              FROM   GL_BALANCES, GL_CODE_COMBINATIONS, GL_BUDGET_VERSIONS
             WHERE   ACTUAL_FLAG = 'B' AND PERIOD_YEAR = glbss.PERIOD_YEAR
                     AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID =
                           GL_BALANCES.CODE_COMBINATION_ID
                     AND GL_BALANCES.BUDGET_VERSION_ID =
                           GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
                     AND GL_BUDGET_VERSIONS.BUDGET_NAME = 'STC CURRENT'
                     AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
                     AND GL_BALANCES.CODE_COMBINATION_ID =
                           glcc.CODE_COMBINATION_ID)
              FullYearBudget,
           (SELECT   NVL (
                        SUM(GL_BALANCES.PERIOD_NET_DR
                            - GL_BALANCES.PERIOD_NET_CR),
                        0
                     )
              FROM   GL_BALANCES, GL_CODE_COMBINATIONS
             WHERE   ACTUAL_FLAG = 'A' AND PERIOD_YEAR = glbss.PERIOD_YEAR
                     AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID =
                           GL_BALANCES.CODE_COMBINATION_ID
                     AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
                     AND GL_BALANCES.CODE_COMBINATION_ID =
                           GLCC.CODE_COMBINATION_ID)
              FULLYEARACTUAL,
           (SELECT   NVL (
                        SUM(GL_BALANCES.PERIOD_NET_DR
                            - GL_BALANCES.PERIOD_NET_CR),
                        0
                     )
              FROM   GL_BALANCES, GL_CODE_COMBINATIONS, GL_BUDGET_VERSIONS
             WHERE   ACTUAL_FLAG = 'E' AND PERIOD_YEAR = glbss.PERIOD_YEAR
                     AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID =
                           GL_BALANCES.CODE_COMBINATION_ID
                     AND GL_BALANCES.BUDGET_VERSION_ID =
                           GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
                     AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
                     AND GL_BALANCES.CODE_COMBINATION_ID =
                           GLCC.CODE_COMBINATION_ID)
              FULLYEARENCUMBR,
           ( ( (SELECT   NVL (
                            SUM(GL_BALANCES.PERIOD_NET_DR
                                - GL_BALANCES.PERIOD_NET_CR),
                            0
                         )
                  FROM   GL_BALANCES, GL_CODE_COMBINATIONS
                 WHERE   ACTUAL_FLAG = 'A' AND PERIOD_YEAR = glbss.PERIOD_YEAR
                         AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID =
                               GL_BALANCES.CODE_COMBINATION_ID
                         AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
                         AND GL_BALANCES.CODE_COMBINATION_ID =
                               GLCC.CODE_COMBINATION_ID))
            + ( (SELECT   NVL (
                             SUM(GL_BALANCES.PERIOD_NET_DR
                                 - GL_BALANCES.PERIOD_NET_CR),
                             0
                          )
                   FROM   GL_BALANCES, GL_CODE_COMBINATIONS, GL_BUDGET_VERSIONS
                  WHERE   ACTUAL_FLAG = 'E' AND PERIOD_YEAR = glbss.PERIOD_YEAR
                          AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID =
                                GL_BALANCES.CODE_COMBINATION_ID
                          AND GL_BALANCES.BUDGET_VERSION_ID =
                                GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
                          AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
                          AND GL_BALANCES.CODE_COMBINATION_ID =
                                GLCC.CODE_COMBINATION_ID)))
              FULLYEARTOTAL,
           ( (SELECT   NVL (
                          SUM(GL_BALANCES.PERIOD_NET_DR
                              - GL_BALANCES.PERIOD_NET_CR),
                          0
                       )
                FROM   GL_BALANCES, GL_CODE_COMBINATIONS, GL_BUDGET_VERSIONS
               WHERE   ACTUAL_FLAG = 'B' AND PERIOD_YEAR = glbss.PERIOD_YEAR
                       AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID =
                             GL_BALANCES.CODE_COMBINATION_ID
                       AND GL_BALANCES.BUDGET_VERSION_ID =
                             GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
                       AND GL_BUDGET_VERSIONS.BUDGET_NAME = 'STC CURRENT'
                       AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
                       AND GL_BALANCES.CODE_COMBINATION_ID =
                             GLCC.CODE_COMBINATION_ID)
            - ( (SELECT   NVL (
                             SUM(GL_BALANCES.PERIOD_NET_DR
                                 - GL_BALANCES.PERIOD_NET_CR),
                             0
                          )
                   FROM   GL_BALANCES, GL_CODE_COMBINATIONS
                  WHERE   ACTUAL_FLAG = 'A' AND PERIOD_YEAR = glbss.PERIOD_YEAR
                          AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID =
                                GL_BALANCES.CODE_COMBINATION_ID
                          AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
                          AND GL_BALANCES.CODE_COMBINATION_ID =
                                GLCC.CODE_COMBINATION_ID))
            + ( (SELECT   NVL (
                             SUM(GL_BALANCES.PERIOD_NET_DR
                                 - GL_BALANCES.PERIOD_NET_CR),
                             0
                          )
                   FROM   GL_BALANCES, GL_CODE_COMBINATIONS, GL_BUDGET_VERSIONS
                  WHERE   ACTUAL_FLAG = 'E' AND PERIOD_YEAR = glbss.PERIOD_YEAR
                          AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID =
                                GL_BALANCES.CODE_COMBINATION_ID
                          AND GL_BALANCES.BUDGET_VERSION_ID =
                                GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
                          AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
                          AND GL_BALANCES.CODE_COMBINATION_ID =
                                GLCC.CODE_COMBINATION_ID)))
              FULLYEAREMAINNG,
           (SELECT   NVL (
                        SUM(  GL_BALANCES.BEGIN_BALANCE_DR
                            + GL_BALANCES.PERIOD_NET_DR
                            - GL_BALANCES.BEGIN_BALANCE_CR
                            - GL_BALANCES.PERIOD_NET_CR),
                        0
                     )
              FROM   GL_BALANCES, GL_CODE_COMBINATIONS, GL_BUDGET_VERSIONS
             WHERE       ACTUAL_FLAG = 'B'
                     AND PERIOD_YEAR = glbss.PERIOD_YEAR
                     AND PERIOD_NAME = glbss.PERIOD_NAME
                     AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID =
                           GL_BALANCES.CODE_COMBINATION_ID
                     AND GL_BALANCES.BUDGET_VERSION_ID =
                           GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
                     AND GL_BUDGET_VERSIONS.BUDGET_NAME = 'STC CURRENT'
                     AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
                     AND GL_BALANCES.CODE_COMBINATION_ID =
                           GLCC.CODE_COMBINATION_ID)
              FORECASTBUDGET,
           (SELECT   NVL (
                        SUM(  GL_BALANCES.BEGIN_BALANCE_DR
                            + GL_BALANCES.PERIOD_NET_DR
                            - GL_BALANCES.BEGIN_BALANCE_CR
                            - GL_BALANCES.PERIOD_NET_CR),
                        0
                     )
              FROM   GL_BALANCES, GL_CODE_COMBINATIONS, GL_BUDGET_VERSIONS
             WHERE       ACTUAL_FLAG = 'B'
                     AND PERIOD_YEAR = glbss.PERIOD_YEAR
                     AND PERIOD_NAME = glbss.PERIOD_NAME
                     AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID =
                           GL_BALANCES.CODE_COMBINATION_ID
                     AND GL_BALANCES.BUDGET_VERSION_ID =
                           GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
                     AND GL_BUDGET_VERSIONS.BUDGET_NAME = 'STC FORECAST'
                     AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
                     AND GL_BALANCES.CODE_COMBINATION_ID =
                           GLCC.CODE_COMBINATION_ID)
              FORECAST,
           ( (SELECT   NVL (
                          SUM(  GL_BALANCES.BEGIN_BALANCE_DR
                              + GL_BALANCES.PERIOD_NET_DR
                              - GL_BALANCES.BEGIN_BALANCE_CR
                              - GL_BALANCES.PERIOD_NET_CR),
                          0
                       )
                FROM   GL_BALANCES, GL_CODE_COMBINATIONS, GL_BUDGET_VERSIONS
               WHERE       ACTUAL_FLAG = 'B'
                       AND PERIOD_YEAR = glbss.PERIOD_YEAR
                       AND PERIOD_NAME = glbss.PERIOD_NAME
                       AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID =
                             GL_BALANCES.CODE_COMBINATION_ID
                       AND GL_BALANCES.BUDGET_VERSION_ID =
                             GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
                       AND GL_BUDGET_VERSIONS.BUDGET_NAME = 'STC CURRENT'
                       AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
                       AND GL_BALANCES.CODE_COMBINATION_ID =
                             GLCC.CODE_COMBINATION_ID)
            - (SELECT   NVL (
                           SUM(  GL_BALANCES.BEGIN_BALANCE_DR
                               + GL_BALANCES.PERIOD_NET_DR
                               - GL_BALANCES.BEGIN_BALANCE_CR
                               - GL_BALANCES.PERIOD_NET_CR),
                           0
                        )
                 FROM   GL_BALANCES, GL_CODE_COMBINATIONS, GL_BUDGET_VERSIONS
                WHERE       ACTUAL_FLAG = 'B'
                        AND PERIOD_YEAR = glbss.PERIOD_YEAR
                        AND PERIOD_NAME = glbss.PERIOD_NAME
                        AND GL_CODE_COMBINATIONS.CODE_COMBINATION_ID =
                              GL_BALANCES.CODE_COMBINATION_ID
                        AND GL_BALANCES.BUDGET_VERSION_ID =
                              GL_BUDGET_VERSIONS.BUDGET_VERSION_ID
                        AND GL_BUDGET_VERSIONS.BUDGET_NAME = 'STC FORECAST'
                        AND GL_CODE_COMBINATIONS.SEGMENT2 = GLCC.SEGMENT2
                        AND GL_BALANCES.CODE_COMBINATION_ID =
                              GLCC.CODE_COMBINATION_ID))
              FORECASTVARIANCE
    FROM   GL_CODE_COMBINATIONS GLCC,
           GL_BALANCES glbss,
           FND_FLEX_VALUES_vl FFV,
           FND_FLEX_VALUES_VL FFV1,
           FND_FLEX_VALUES_VL FFV2,
           (SELECT   *
              FROM   fnd_flex_value_norm_hierarchy a, fnd_flex_values_vl b
             WHERE       A.FLEX_VALUE_SET_ID = 1014368
                     AND A.FLEX_VALUE_SET_ID = B.FLEX_VALUE_SET_ID
                     AND parent_flex_value LIKE 'D%'
                     AND B.FLEX_VALUE BETWEEN A.CHILD_FLEX_VALUE_LOW
                                          AND  A.CHILD_FLEX_VALUE_HIGH) DLEVEL,
           (SELECT   *
              FROM   fnd_flex_value_norm_hierarchy a, fnd_flex_values_vl b
             WHERE       A.FLEX_VALUE_SET_ID = 1014368
                     AND A.FLEX_VALUE_SET_ID = B.FLEX_VALUE_SET_ID
                     AND PARENT_FLEX_VALUE LIKE 'C%'
                     AND b.flex_value BETWEEN a.child_flex_value_low
                                          AND  a.child_flex_value_high) Clevel,
           (SELECT   *
              FROM   fnd_flex_value_norm_hierarchy a, fnd_flex_values_vl b
             WHERE       A.FLEX_VALUE_SET_ID = 1014368
                     AND A.FLEX_VALUE_SET_ID = B.FLEX_VALUE_SET_ID
                     AND PARENT_FLEX_VALUE LIKE 'B%'
                     AND B.FLEX_VALUE BETWEEN A.CHILD_FLEX_VALUE_LOW
                                          AND  A.CHILD_FLEX_VALUE_HIGH) BLEVEL,
           (SELECT   *
              FROM   fnd_flex_value_norm_hierarchy a, fnd_flex_values_vl b
             WHERE       A.FLEX_VALUE_SET_ID = 1014368
                     AND A.FLEX_VALUE_SET_ID = B.FLEX_VALUE_SET_ID
                     AND PARENT_FLEX_VALUE LIKE 'A%'
                     AND b.flex_value BETWEEN a.child_flex_value_low
                                          AND  a.child_flex_value_high) Alevel
   WHERE       glcc.segment3 = ffv.flex_value
           AND glcc.segment4 = ffv1.flex_value
           AND GLCC.SEGMENT2 = FFV2.FLEX_VALUE
           AND DLEVEL.FLEX_VALUE = GLCC.SEGMENT2
           AND CLEVEL.FLEX_VALUE = DLEVEL.PARENT_FLEX_VALUE
           AND BLEVEL.FLEX_VALUE = CLEVEL.PARENT_FLEX_VALUE
           AND Alevel.flex_value = BLEVEL.parent_flex_value
           AND glcc.CODE_COMBINATION_ID = glbss.CODE_COMBINATION_ID
           AND ffv.flex_value_set_id = 1014369
           AND ffv1.flex_value_set_id = 1014370
           AND FFV2.FLEX_VALUE_SET_ID = 1014368
           AND ACTUAL_FLAG IN ('A', 'B', 'E')
ORDER BY   1, 2