General Ledger - Oracle EBS R12: Period Wise Trial Balance for a Particular Account (Leave Period_Name Parameter Blank)

https://generalledger-gl.blogspot.com/2017/10/period-wise-trial-balance-for.html

PLSQL Query to Get Period Wise Trial Balance for a Particular Account (Leave Period_Name Parameter Blank)

select gcc.SEGMENT4,

gb.PERIOD_NAME,

gb.PERIOD_YEAR,

gb.PERIOD_NUM,

Decode(ga.CURRENCY_CODE,
'&Currency',

Decode('&Currency_type',

'E',

sum(gb.BEGIN_BALANCE_DR_beq - gb.BEGIN_BALANCE_CR_beq),

'T',

sum(gb.BEGIN_BALANCE_DR - gb.BEGIN_BALANCE_CR),

sum(gb.BEGIN_BALANCE_DR - gb.BEGIN_BALANCE_CR)),

Decode('&Currency_type',

'E',

sum(gb.BEGIN_BALANCE_DR - gb.BEGIN_BALANCE_CR),

'C',

sum(gb.BEGIN_BALANCE_DR_beq - gb.BEGIN_BALANCE_CR_beq))) Opening_Balance_PTD,

Decode(ga.CURRENCY_CODE,
'&Currency',

Decode('&Currency_type',

'E',

sum(gb.PERIOD_NET_DR_beq),

'T',

sum(gb.PERIOD_NET_DR),

sum(gb.PERIOD_NET_DR)),

Decode('&Currency_type',

'E',

sum(gb.PERIOD_NET_DR),

'C',

sum(gb.PERIOD_NET_DR_beq))) PTD_DEBIT,

Decode(ga.CURRENCY_CODE,
'&Currency',

Decode('&Currency_type',

'E',

sum(gb.PERIOD_NET_CR_beq),

'T',

sum(gb.PERIOD_NET_CR),

sum(gb.PERIOD_NET_CR)),

Decode('&Currency_type',

'E',

sum(gb.PERIOD_NET_CR),

'C',

sum(gb.PERIOD_NET_CR_beq))) PTD_CREDIT,

------------------

Decode(ga.CURRENCY_CODE,
'&Currency',

Decode('&Currency_type',

'E',

sum(gb.PERIOD_NET_DR_beq),

'T',

sum(gb.PERIOD_NET_DR),

sum(gb.PERIOD_NET_DR)),

Decode('&Currency_type',

'E',

sum(gb.PERIOD_NET_DR),

'C',

sum(gb.PERIOD_NET_DR_beq))) -

Decode(ga.CURRENCY_CODE,
'&Currency',

Decode('&Currency_type',

'E',

sum(gb.PERIOD_NET_CR_beq),

'T',

sum(gb.PERIOD_NET_CR),

sum(gb.PERIOD_NET_CR)),

Decode('&Currency_type',

'E',

sum(gb.PERIOD_NET_CR),

'C',

sum(gb.PERIOD_NET_CR_beq))) PTD_NET,

------------------------------

NVL(hh.OPENING_BALANCE_YTD, 0) OPENING_BALANCE_YTD,

/*sum(gb.PROJECT_TO_DATE_DR_beq+gb.PERIOD_NET_DR_beq),

sum(gb.PROJECT_TO_DATE_CR_beq+gb.PERIOD_NET_CR_beq),

kk.Opening_DR,kk.Opening_CR,*/

Decode(ga.CURRENCY_CODE,
'&Currency',

Decode('&Currency_type',

'E',

sum(gb.PROJECT_TO_DATE_DR_beq + gb.PERIOD_NET_DR_beq) -

NVL(kk.Opening_DR, 0),

'T',

sum(gb.PROJECT_TO_DATE_DR + gb.PERIOD_NET_DR) -

NVL(kk.Opening_DR, 0),

sum(gb.PROJECT_TO_DATE_DR + gb.PERIOD_NET_DR) -

NVL(kk.Opening_DR, 0)),

Decode('&Currency_type',

'E',

sum(gb.PROJECT_TO_DATE_DR + gb.PERIOD_NET_DR) -

NVL(kk.Opening_DR, 0),

'C',

sum(gb.PROJECT_TO_DATE_DR_beq + gb.PERIOD_NET_DR_beq) -

NVL(kk.Opening_DR, 0))) YTD_DEBIT,

Decode(ga.CURRENCY_CODE,
'&Currency',

Decode('&Currency_type',

'E',

sum(gb.PROJECT_TO_DATE_CR_beq + gb.PERIOD_NET_CR_beq) -

NVL(kk.Opening_CR, 0),

'T',

sum(gb.PROJECT_TO_DATE_CR + gb.PERIOD_NET_CR) -

NVL(kk.Opening_CR, 0),

sum(gb.PROJECT_TO_DATE_CR + gb.PERIOD_NET_CR) -

NVL(kk.Opening_CR, 0)),

Decode('&Currency_type',

'E',

sum(gb.PROJECT_TO_DATE_CR + gb.PERIOD_NET_CR) -

NVL(kk.Opening_CR, 0),

'C',

sum(gb.PROJECT_TO_DATE_CR_beq + gb.PERIOD_NET_CR_beq) -

NVL(kk.Opening_CR, 0))) YTD_CREDIT,

Decode(ga.CURRENCY_CODE,
'&Currency',

Decode('&Currency_type',

'E',

sum(gb.PERIOD_NET_DR_beq - gb.PERIOD_NET_CR_beq) +

sum(gb.BEGIN_BALANCE_DR_beq - gb.BEGIN_BALANCE_CR_beq),

'T',

sum(gb.PERIOD_NET_DR - gb.PERIOD_NET_CR) +

sum(gb.BEGIN_BALANCE_DR - gb.BEGIN_BALANCE_CR),

sum(gb.PERIOD_NET_DR - gb.PERIOD_NET_CR) +

sum(gb.BEGIN_BALANCE_DR - gb.BEGIN_BALANCE_CR)),

Decode('&Currency_type',

'E',

sum(gb.PERIOD_NET_DR - gb.PERIOD_NET_CR) +

sum(gb.BEGIN_BALANCE_DR - gb.BEGIN_BALANCE_CR),

'C',

sum(gb.PERIOD_NET_DR_beq - gb.PERIOD_NET_CR_beq) +

sum(gb.BEGIN_BALANCE_DR_beq - gb.BEGIN_BALANCE_CR_beq))) ENDING_BALANCE

from gl_balances gb,

gl_code_combinations gcc,

gl_ledgers ga,

(select gcc.segment4 Acc,

Decode(ga.CURRENCY_CODE,
'&Currency',

Decode('&Currency_type',

'E',

sum(gb.PERIOD_NET_DR_beq - gb.PERIOD_NET_CR_beq) +

sum(gb.BEGIN_BALANCE_DR_beq -

gb.BEGIN_BALANCE_CR_beq),

'T',

sum(gb.PERIOD_NET_DR - gb.PERIOD_NET_CR) +

sum(gb.BEGIN_BALANCE_DR - gb.BEGIN_BALANCE_CR),

sum(gb.PERIOD_NET_DR - gb.PERIOD_NET_CR) +

sum(gb.BEGIN_BALANCE_DR - gb.BEGIN_BALANCE_CR)),

Decode('&Currency_type',

'E',

sum(gb.PERIOD_NET_DR - gb.PERIOD_NET_CR) +

sum(gb.BEGIN_BALANCE_DR - gb.BEGIN_BALANCE_CR),

'C',

sum(gb.PERIOD_NET_DR_beq - gb.PERIOD_NET_CR_beq) +

sum(gb.BEGIN_BALANCE_DR_beq -

gb.BEGIN_BALANCE_CR_beq))) OPENING_BALANCE_YTD

from gl_balances gb,

gl_code_combinations gcc,

FND_FLEX_VALUES_VL FND,

gl_ledgers ga

where gb.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID

AND FND.FLEX_VALUE = GCC.SEGMENT4

and ga.LEDGER_ID = gb.LEDGER_ID

and gcc.CHART_OF_ACCOUNTS_ID = ga.CHART_OF_ACCOUNTS_ID

and gb.ledger_id = '&Ledger_id'

and gcc.SEGMENT2 = NVL('&Location', gcc.SEGMENT2)

and gcc.SEGMENT4 = NVL('&Account', gcc.SEGMENT4)

and gb.CURRENCY_CODE = '&Currency'

and gb.ACTUAL_FLAG = 'A'

and gb.PERIOD_NUM = 1

and gb.PERIOD_YEAR = '&Fiscal_Year'

group by gcc.segment4, ga.CURRENCY_CODE) hh,

(select gb.PERIOD_NAME,

gcc.SEGMENT4,

Decode(ga.CURRENCY_CODE,
'&Currency',

Decode('&Currency_type',

'E',

sum(gb.PROJECT_TO_DATE_DR_beq),

'T',

sum(gb.PROJECT_TO_DATE_DR),

sum(gb.PROJECT_TO_DATE_DR)),

Decode('&Currency_type',

'E',

sum(gb.PROJECT_TO_DATE_DR),

'C',

sum(gb.PROJECT_TO_DATE_DR_beq))) Opening_DR,

Decode(ga.CURRENCY_CODE,
'&Currency',

Decode('&Currency_type',

'E',

sum(gb.PROJECT_TO_DATE_CR_beq),

'T',

sum(gb.PROJECT_TO_DATE_CR),

sum(gb.PROJECT_TO_DATE_CR)),

Decode('&Currency_type',

'E',

sum(gb.PROJECT_TO_DATE_CR),

'C',

sum(gb.PROJECT_TO_DATE_CR_beq))) Opening_CR

from gl_balances gb,
gl_code_combinations gcc,
gl_periods gp,
gl_ledgers ga

where gb.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID

and gb.PERIOD_YEAR = gp.PERIOD_YEAR

and gb.PERIOD_NUM = gp.PERIOD_NUM

and ga.LEDGER_ID = gb.LEDGER_ID

and gcc.CHART_OF_ACCOUNTS_ID = ga.CHART_OF_ACCOUNTS_ID

and gb.ledger_id = '&Ledger_id'

and gcc.SEGMENT2 = NVL('&Location', gcc.SEGMENT2)

and gcc.SEGMENT4 = NVL('&Account', gcc.SEGMENT4)

and gb.CURRENCY_CODE = '&Currency'

and gb.ACTUAL_FLAG = 'A'

and gp.PERIOD_NUM =
(select min(Period_num)
from gl_periods
where period_year = '&Fiscal_Year') --and gp.ENTERED_PERIOD_NAME= 'JUL-OP' --(First Period)

and gp.PERIOD_YEAR = '&Fiscal_Year'

group by gb.PERIOD_NAME, gcc.SEGMENT4, ga.CURRENCY_CODE) kk

where gb.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID

and kk.segment4(+) = gcc.SEGMENT4

and hh.Acc(+) = gcc.segment4

and ga.LEDGER_ID = gb.LEDGER_ID

and gcc.CHART_OF_ACCOUNTS_ID = ga.CHART_OF_ACCOUNTS_ID

--and gb.PERIOD_NAME=yy.period_name

and gb.ledger_id = '&Ledger_id'

and gcc.SEGMENT2 = NVL('&Location', gcc.SEGMENT2)

and gcc.SEGMENT4 = NVL('&Account', gcc.SEGMENT4)

and gb.CURRENCY_CODE = '&Currency'

and gb.ACTUAL_FLAG = 'A'

and gb.PERIOD_YEAR = '&Fiscal_Year'

and gb.PERIOD_NAME = NVL('&Period_Name', gb.PERIOD_NAME)

group by gcc.SEGMENT4,

gb.PERIOD_NAME,

gb.PERIOD_year,

gb.PERIOD_NUM,

ga.CURRENCY_CODE,

hh.OPENING_BALANCE_YTD,

kk.Opening_DR,

kk.Opening_CR

order by gb.PERIOD_YEAR, gb.PERIOD_NUM asc

General Ledger - Oracle EBS R12

Monday, 2 October 2017

Period Wise Trial Balance for a Particular Account (Leave Period_Name Parameter Blank) - Oracle EBS R12

No comments:

Post a Comment