Trial Balance All Entities Report Internal 12.2.7

Query As per Standard

For YTD :

:CURRENCY_TYPE = 'T';
:AMOUNT_YPE = 'YTD'
:FIRST_PERIOD = 'Jan-19'
:PERIOD_NAME : = 'Dec-19'


This is for  ACTUAL_FLAG = 'A' - Actual - below coding




select * from (SELECT /*+ ORDERED */
L.LEDGER_ID , L.Name,cc.segment1||'-'||cc.segment2||'-'||cc.SEGMENT3||'-'||SEGMENT4||'-'||cc.segment5||'-'||cc.segment6||'-'||cc.segment7 Code_combinations,
GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(51515,3,cc.SEGMENT3) ACCOUNT_DESCRIPTION,
  DECODE(
  :AMOUNT_YPE, 'PTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0), 'S', NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0), NVL(BEGIN_BALANCE_DR_BEQ, 0) - NVL(BEGIN_BALANCE_CR_BEQ, 0)))), 'PJTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', 0, 'S', 0, 'E', 0)), 'YTD', DECODE(
  :CURRENCY_TYPE, 'T', SUM(DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0)     - NVL(BEGIN_BALANCE_CR, 0), 0)), 'S', SUM(DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0)     - NVL(BEGIN_BALANCE_CR, 0), 0)), 'E', SUM(DECODE(BAL.TRANSLATED_FLAG, 'R', DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0)     - NVL(BEGIN_BALANCE_CR, 0), 0), DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR_BEQ, 0) - NVL(BEGIN_BALANCE_CR_BEQ, 0), 0))))) BEGIN_BALANCE,
  DECODE(
  :AMOUNT_YPE, 'PTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PERIOD_NET_DR, 0), 'S', NVL(PERIOD_NET_DR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PERIOD_NET_DR, 0), NVL(PERIOD_NET_DR_BEQ, 0)))), 'PJTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0), 'S', NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0), NVL(PROJECT_TO_DATE_DR_BEQ, 0) + NVL(PERIOD_NET_DR_BEQ, 0)))), 'YTD', DECODE(
  :CURRENCY_TYPE, 'T', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0), 0)), 'S', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0), 0)), 'E', SUM(DECODE(BAL.TRANSLATED_FLAG, 'R', DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0), 0), DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR_BEQ, 0) + NVL(PERIOD_NET_DR_BEQ, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR_BEQ, 0), 0))))) PERIOD_DR,
  DECODE(
  :AMOUNT_YPE, 'PTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PERIOD_NET_CR, 0), 'S', NVL(PERIOD_NET_CR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PERIOD_NET_CR, 0), NVL(PERIOD_NET_CR_BEQ, 0)))), 'PJTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0), 'S', NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0), NVL(PROJECT_TO_DATE_CR_BEQ, 0) + NVL(PERIOD_NET_CR_BEQ, 0)))), 'YTD', DECODE(
  :CURRENCY_TYPE, 'T', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR, 0) + NVL(PERIOD_NET_CR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR, 0), 0)), 'S', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR, 0) + NVL(PERIOD_NET_CR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR, 0), 0)), 'E', SUM(DECODE(BAL.TRANSLATED_FLAG, 'R', DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR, 0) + NVL(PERIOD_NET_CR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR, 0), 0), DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR_BEQ, 0) + NVL(PERIOD_NET_CR_BEQ, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR_BEQ, 0), 0))))) PERIOD_CR ,
  DECODE(
  :AMOUNT_YPE, 'PTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0), 'S', NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0), NVL(BEGIN_BALANCE_DR_BEQ, 0) - NVL(BEGIN_BALANCE_CR_BEQ, 0)))), 'PJTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', 0, 'S', 0, 'E', 0)), 'YTD', DECODE(
  :CURRENCY_TYPE, 'T', SUM(DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0)     - NVL(BEGIN_BALANCE_CR, 0), 0)), 'S', SUM(DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0)     - NVL(BEGIN_BALANCE_CR, 0), 0)), 'E', SUM(DECODE(BAL.TRANSLATED_FLAG, 'R', DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0)     - NVL(BEGIN_BALANCE_CR, 0), 0), DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR_BEQ, 0) - NVL(BEGIN_BALANCE_CR_BEQ, 0), 0))))) + DECODE(
  :AMOUNT_YPE, 'PTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PERIOD_NET_DR, 0), 'S', NVL(PERIOD_NET_DR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PERIOD_NET_DR, 0), NVL(PERIOD_NET_DR_BEQ, 0)))), 'PJTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0), 'S', NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0), NVL(PROJECT_TO_DATE_DR_BEQ, 0) + NVL(PERIOD_NET_DR_BEQ, 0)))), 'YTD', DECODE(
  :CURRENCY_TYPE, 'T', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0), 0)), 'S', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0), 0)), 'E', SUM(DECODE(BAL.TRANSLATED_FLAG, 'R', DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0), 0), DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR_BEQ, 0)          + NVL(PERIOD_NET_DR_BEQ, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR_BEQ, 0), 0))))) - DECODE(
  :AMOUNT_YPE, 'PTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PERIOD_NET_CR, 0), 'S', NVL(PERIOD_NET_CR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PERIOD_NET_CR, 0), NVL(PERIOD_NET_CR_BEQ, 0)))), 'PJTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0), 'S', NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0), NVL(PROJECT_TO_DATE_CR_BEQ, 0) + NVL(PERIOD_NET_CR_BEQ, 0)))), 'YTD', DECODE(
  :CURRENCY_TYPE, 'T', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR, 0) + NVL(PERIOD_NET_CR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR, 0), 0)), 'S', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR, 0) + NVL(PERIOD_NET_CR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR, 0), 0)), 'E', SUM(DECODE(BAL.TRANSLATED_FLAG, 'R', DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR, 0) + NVL(PERIOD_NET_CR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR, 0), 0), DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR_BEQ, 0) + NVL(PERIOD_NET_CR_BEQ, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR_BEQ, 0), 0))))) ENDING_BALANCE
FROM GL_BALANCES BAL,
  GL_CODE_COMBINATIONS CC,
  GL_LEDGERS L,
  GL_LEDGER_SET_ASSIGNMENTS ASG,
  GL_LEDGER_RELATIONSHIPS LR
WHERE BAL.ACTUAL_FLAG = 'A'
AND BAL.CURRENCY_CODE = (select CURRENCY_CODE from gl_ledgers where ledger_id  = l.ledger_id)--nvl(:ENTERED_CURRENCY,BAL.CURRENCY_CODE)
AND BAL.PERIOD_NAME IN (
  :PERIOD_NAME, DECODE(
  :AMOUNT_YPE, 'PTD',
  :PERIOD_NAME, 'PJTD',
  :PERIOD_NAME, 'YTD',
  :FIRST_PERIOD))
AND BAL.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID
  -- AND CC.CHART_OF_ACCOUNTS_ID = &STRUCT_NUM
AND CC.TEMPLATE_ID IS NULL
AND CC.SUMMARY_FLAG = 'N'
AND L.LEDGER_ID     = nvl(:P_LEDGER_ID,L.LEDGER_ID)
AND ASG.LEDGER_SET_ID(+)    = L.LEDGER_ID
AND LR.TARGET_LEDGER_ID     = NVL(ASG.LEDGER_ID, L.LEDGER_ID)
AND LR.SOURCE_LEDGER_ID     = NVL(ASG.LEDGER_ID, L.LEDGER_ID)
AND LR.TARGET_CURRENCY_CODE =  (select TARGET_CURRENCY_CODE from GL_LEDGER_RELATIONSHIPS where PRIMARY_LEDGER_ID = l.ledger_id and TARGET_LEDGER_SHORT_NAME = l.SHORT_NAME) --nvl(:LEDGER_CURRENCY,LR.TARGET_CURRENCY_CODE)--nvl(:LEDGER_CURRENCY,LR.TARGET_CURRENCY_CODE)
AND LR.SOURCE_LEDGER_ID = BAL.LEDGER_ID
AND LR.TARGET_LEDGER_ID = BAL.LEDGER_ID
 and  nvl(bal.translated_flag, 'x') != 'R'
 --and BAL.LEDGER_ID IN ( SELECT acc.ledger_id FROM gl_access_set_ledgers acc WHERE acc.access_set_id = 1521 )
 --and CC.SEGMENT1 = :SEGMENT1_LOW--  and cc.segment3 = '1121024'
GROUP BY cc.SEGMENT4 , cc.SEGMENT3 , cc.segment1,L.LEDGER_ID ,cc.segment5,cc.segment6,cc.segment7,cc.segment2,L.Name
ORDER BY L.LEDGER_ID , segment3
) where nvl(BEGIN_BALANCE,0) != 0 or nvl(PERIOD_DR,0) != 0 or nvl(PERIOD_CR,0) != 0 or nvl(ENDING_BALANCE,0) != 0