Scribd
Upload
7 views2 pages

Year-to-Date Balance Query SQL

The document contains a SQL query that calculates the Year-To-Date (YTD) balance for accounts based on various filtering criteria such as account type, period year, and specific segments. It utilizes common table expressions to gather relevant data from GL_BALANCES, GL_CODE_COMBINATIONS, and GL_PERIODS. The final output groups the results by account and period details, ordering them accordingly.

Uploaded by

arunforgood123
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
7 views2 pages

Year-to-Date Balance Query SQL

The document contains a SQL query that calculates the Year-To-Date (YTD) balance for accounts based on various filtering criteria such as account type, period year, and specific segments. It utilizes common table expressions to gather relevant data from GL_BALANCES, GL_CODE_COMBINATIONS, and GL_PERIODS. The final output groups the results by account and period details, ordering them accordingly.

Uploaded by

arunforgood123
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

WITH MAIN

AS (
SELECT CC.SEGMENT4 ACCOUNT

,case when CC.account_type in ('R','E') and gp.period_num =1 and


gp.quarter_num=1 then 0
else (NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0) )
end begining_bal
,GP.PERIOD_YEAR
,GP.PERIOD_NAME
,GP.PERIOD_NUM
,GP.QUARTER_NUM
FROM GL_BALANCES GLB
,GL_CODE_COMBINATIONS CC
,GL_PERIODS GP
WHERE GLB.ACTUAL_FLAG = 'A'
AND GLB.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID
AND CC.SUMMARY_FLAG = 'N'
AND CC.TEMPLATE_ID IS NULL
AND GP.ADJUSTMENT_PERIOD_FLAG = 'N'
AND GLB.PERIOD_TYPE = GP.PERIOD_TYPE
AND GLB.PERIOD_NAME = GP.PERIOD_NAME
AND GLB.PERIOD_YEAR = GP.PERIOD_YEAR
AND cc.segment1 between :P_COMPANY_FROM and :P_COMPANY_TO
AND cc.segment2 between :P_COSTCENTER_FROM and :P_COSTCENTER_TO
AND cc.segment3 between :P_STORE_FROM and :P_STORE_TO
AND cc.segment4 between substr(:P_ACCOUNT_FROM,1,6) and
substr(:P_ACCOUNT_TO,1,6)
AND cc.segment5 between :P_FUTURE1_FROM and :P_FUTURE1_TO
AND cc.segment6 BETWEEN :P_FUTURE2_FROM and :P_FUTURE2_TO
AND CC.SEGMENT3 IN NVL((
SELECT FTN.PK1_START_VALUE
FROM FND_TREE_NODE FTN
,FND_FLEX_VALUES_VL FVV
WHERE FTN.TREE_STRUCTURE_CODE = 'GL_ACCT_FLEX'
AND FTN.TREE_CODE = 'Location Five Below'
AND FTN.PK1_START_VALUE = CC.SEGMENT3
AND FTN.PARENT_PK1_VALUE = FVV.FLEX_VALUE
AND FTN.TREE_VERSION_ID IN (
SELECT TREE_VERSION_ID
FROM FND_TREE_VERSION_VL
WHERE TREE_STRUCTURE_CODE = 'GL_ACCT_FLEX'
AND TREE_CODE = 'Location Five Below'
AND TREE_VERSION_NAME = 'Location Five
Below State'
AND TRUNC(SYSDATE) BETWEEN
EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE
)
AND [Link] = nvl(:P_STATE,[Link])
), CC.SEGMENT3)
AND GP.PERIOD_YEAR IN (
SELECT DISTINCT PERIOD_YEAR
FROM GL_PERIODS
WHERE PERIOD_NAME between :p_period_name_from
and :p_period_name_to
)
AND GLB.LEDGER_ID IN (:p_ledger)
ORDER BY GP.PERIOD_NUM
,GP.QUARTER_NUM
)

SELECT /*+ PARALLEL(A,32)*/


SUM(A.begining_BAL) YTD_BAL
FROM MAIN A
where A.PERIOD_NAME between :p_period_name_from and :p_period_name_to
GROUP BY [Link]
----,A.ACCOUNT_DESC
,A.PERIOD_NAME
,A.PERIOD_YEAR
,A.PERIOD_NUM
,A.QUARTER_NUM
ORDER BY [Link]
,A.PERIOD_YEAR DESC
,A.PERIOD_NUM ASC

You might also like