GL Accounts Highrarcy Query R12

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