General Ledger - Oracle EBS R12: GL Balances Tables (FSG PLSQL Query)

http://generalledger-gl.blogspot.com/2017/02/gl-balances-tables-fsg-query.html

FSG PLSQL Query

select

--ACCOUNT

--CURRENCY_CODE

--ACC_ALIAS

'`' || department department,

'`' || future future,

'`' || NATURal_account NATURal_account,

/*?_get_acc_aliases( department , future, NATURal_account) ALIASES,

?_get_acc_aliases_DESC ( department , future, NATURal_account ) ALIASES_DESCRIPTION,*/

(select gl_flexfields_pkg.get_description_sql(50308, 4, NATURal_account)
from dual) Account_DESC,

sum(ACTUAL) ACTUAL,

sum(FORECAST) FORECAST,

sum(FVSA) FVSA,

sum(BUDGET) BUDGET,

sum(BVSA) BVSA,

sum(YTD_ACTUAL) YTD_ACTUAL,

sum(YTD_BUDGET) YTD_BUDGET,

sum(YTD_BVSA) YTD_BVSA,

sum(ACTUAL_PLUS_FORECAST) ACTUAL_PLUS_FORECAST,

sum(FULL_YEAR_BUD) FULL_YEAR_BUD,

sum(FULL_BUDVSACTUAL) FULL_BUDVSACTUAL

from (

select Account,

department,

future,

NATURal_account,

CURRENCY_CODE /*, ACC_ALIAS*/,
ACTUAL,
FORECAST,
nvl(FORECAST, 0) - nvl(ACTUAL, 0) FVSA,
BUDGET,

nvl(BUDGET, 0) - nvl(ACTUAL, 0) BVSA,
YTD_ACTUAL,
YTD_BUDGET,
nvl(YTD_BUDGET, 0) - nvl(YTD_ACTUAL, 0) YTD_BVSA,

nvl(PREV_ACTUAL, 0) + nvl(FUTURE_FORECAST, 0) ACTUAL_PLUS_FORECAST,
FULL_YEAR_BUD,

nvl(PREV_ACTUAL, 0) + nvl(FUTURE_FORECAST, 0) -
nvl(YTD_ACTUAL, 0) FULL_BUDVSACTUAL

from (

select A.CODE_COMBINATION_ID

,
A.Account,

A.department,

A.future,

A.NATURal_account,

A.CURRENCY_CODE -- 5973

/*,(

select nvl( min(L.ALIAS_NAME),'N/A')

from FND_SHORTHAND_FLEX_ALIASES L , FND_ID_FLEX_STRUCTURES_VL H

, gl_code_combinations gcc2

where gcc2.CODE_COMBINATION_ID = A.CODE_COMBINATION_ID

and H.ID_FLEX_STRUCTURE_NAME = '?_ACCOUNTING_FLEXFIELD'

and H.ID_FLEX_CODE = 'GL#'

and H.ID_FLEX_CODE = L.ID_FLEX_CODE

and L.ID_FLEX_NUM = H.ID_FLEX_NUM

and gcc2.CHART_OF_ACCOUNTS_ID = H.ID_FLEX_NUM

and L.CONCATENATED_SEGMENTS = A.ACCOUNT

) ACC_ALIAS*/

--------------------------------------------ACTUAL----------

,
(

select nvl(sum(nvl(PERIOD_NET_DR_BEQ, 0) -
nvl(PERIOD_NET_CR_BEQ, 0)),
0)

from gl_balances bal

where

bal.LEDGER_ID = A.LEDGER_ID

and bal.ACTUAL_FLAG = 'A'

and bal.PERIOD_NAME = :PERIOD

and bal.CODE_COMBINATION_ID = A.CODE_COMBINATION_ID

and bal.CURRENCY_CODE = A.CURRENCY_CODE

and bal.LEDGER_ID = :P_LEDGER_ID

) ACTUAL,

------------------------------------------FORECAST-----------

(

select

nvl(sum((nvl(PERIOD_NET_DR, 0) - nvl(PERIOD_NET_CR, 0))),
0)

from gl_balances b, GL_BUDGETS_V bud -- 4112 1707

where (bud.budget_name like '%FORECAST%' or
bud.budget_name like '%FCST%')

and b.LEDGER_ID = A.LEDGER_ID

and b.ACTUAL_FLAG = 'B'

and b.PERIOD_NAME = :PERIOD

and b.CODE_COMBINATION_ID = A.CODE_COMBINATION_ID

and b.BUDGET_VERSION_ID = bud.budget_version_id

and b.CURRENCY_CODE = A.CURRENCY_CODE

and b.LEDGER_ID = :P_LEDGER_ID

) FORECAST,

----------------------------------------BUDGET--------------------

(

select

nvl(sum((nvl(PERIOD_NET_DR, 0) - nvl(PERIOD_NET_CR, 0))),
0)

from gl_balances b, GL_BUDGETS_V bud -- 4112 1707

where (bud.budget_name like '%BUDGET%' or
bud.budget_name like '%BUD%')

and b.LEDGER_ID = A.LEDGER_ID

and b.ACTUAL_FLAG = 'B'

and b.PERIOD_NAME = :PERIOD

and b.CODE_COMBINATION_ID = A.CODE_COMBINATION_ID

and b.BUDGET_VERSION_ID = bud.budget_version_id

and b.CURRENCY_CODE = A.CURRENCY_CODE

and b.LEDGER_ID = :P_LEDGER_ID

) BUDGET

,
(

select nvl(sum(nvl(bal.PROJECT_TO_DATE_DR_BEQ, 0) -
nvl(bal.PROJECT_TO_DATE_CR_BEQ, 0) +
(nvl(bal.PERIOD_NET_DR_BEQ, 0) -
nvl(bal.PERIOD_NET_CR_BEQ, 0))),
0)

from gl_balances bal

where

bal.LEDGER_ID = A.LEDGER_ID

and bal.ACTUAL_FLAG = 'A'

and bal.PERIOD_NAME = :PERIOD

and bal.CODE_COMBINATION_ID = A.CODE_COMBINATION_ID

and bal.CURRENCY_CODE = A.CURRENCY_CODE

and bal.LEDGER_ID = :P_LEDGER_ID

) YTD_ACTUAL

,

(

select

nvl(sum(nvl(b.PROJECT_TO_DATE_DR, 0) -
nvl(b.PROJECT_TO_DATE_CR, 0) +
(nvl(b.PERIOD_NET_DR, 0) -
nvl(b.PERIOD_NET_CR, 0))),
0)

from gl_balances b, GL_BUDGETS_V bud -- 4112 1707

where (bud.budget_name like '%BUDGET%' or
bud.budget_name like '%BUD%')

and b.LEDGER_ID = A.LEDGER_ID

and b.ACTUAL_FLAG = 'B'

and b.PERIOD_NAME = :PERIOD

and b.CODE_COMBINATION_ID = A.CODE_COMBINATION_ID

and b.BUDGET_VERSION_ID = bud.budget_version_id

and b.CURRENCY_CODE = A.CURRENCY_CODE

and b.LEDGER_ID = :P_LEDGER_ID

) YTD_BUDGET

,

(

select nvl(sum(nvl(PERIOD_NET_DR_BEQ, 0) -
nvl(PERIOD_NET_CR_BEQ, 0)),
0)

from gl_balances bal

where

bal.LEDGER_ID = A.LEDGER_ID

and bal.ACTUAL_FLAG = 'A'

and bal.PERIOD_YEAR =
(select p.period_year
from gl_periods_v p
where p.period_set_name = '?CALENDAR'
and p.period_name = :PERIOD)

and bal.PERIOD_NUM <=
(select p.period_num
from gl_periods_v p
where p.period_set_name = '?CALENDAR'
and p.period_name = :PERIOD)

and bal.CODE_COMBINATION_ID = A.CODE_COMBINATION_ID

and bal.CURRENCY_CODE = A.CURRENCY_CODE

and bal.LEDGER_ID = :P_LEDGER_ID

) PREV_ACTUAL

,
(

select

nvl(sum((nvl(PERIOD_NET_DR, 0) - nvl(PERIOD_NET_CR, 0))),
0)

from gl_balances b, GL_BUDGETS_V bud -- 4112 1707

where (bud.budget_name like '%FORECAST%' or
bud.budget_name like '%FCST%')

and b.LEDGER_ID = A.LEDGER_ID

and b.ACTUAL_FLAG = 'B'

and b.CODE_COMBINATION_ID = A.CODE_COMBINATION_ID

and b.BUDGET_VERSION_ID = bud.budget_version_id

and b.CURRENCY_CODE = A.CURRENCY_CODE

and b.PERIOD_YEAR =
(select p.period_year
from gl_periods_v p
where p.period_set_name = '?CALENDAR'
and p.period_name = :PERIOD)

and b.PERIOD_NUM >
(select p.period_num
from gl_periods_v p
where p.period_set_name = '?CALENDAR'
and p.period_name = :PERIOD)

and b.LEDGER_ID = :P_LEDGER_ID

) FUTURE_FORECAST

,
(

select

nvl(sum((nvl(PERIOD_NET_DR, 0) - nvl(PERIOD_NET_CR, 0))),
0)

from gl_balances b, GL_BUDGETS_V bud -- 4112 1707

where (bud.budget_name like '%BUDGET%' or
bud.budget_name like '%BUD%')

and b.LEDGER_ID = A.LEDGER_ID

and b.ACTUAL_FLAG = 'B'

and b.CODE_COMBINATION_ID = A.CODE_COMBINATION_ID

and b.BUDGET_VERSION_ID = bud.budget_version_id

and b.CURRENCY_CODE = A.CURRENCY_CODE

and b.PERIOD_YEAR =
(select p.period_year
from gl_periods_v p
where p.period_set_name = '?CALENDAR'
and p.period_name = :PERIOD)

and b.LEDGER_ID = :P_LEDGER_ID

) FULL_YEAR_BUD

from (

select distinct b.LEDGER_ID,
b.CODE_COMBINATION_ID,

segment3 department,

segment7 future,

segment4 NATURal_account,

b.CURRENCY_CODE,
gcc.SEGMENT2,

gcc.SEGMENT1 || '-' || gcc.SEGMENT2 || '-' ||
gcc.SEGMENT3 || '-' || gcc.SEGMENT4 || '-' ||
gcc.SEGMENT5 || '-' || gcc.SEGMENT6 || '-' ||
gcc.SEGMENT7 || '-' || gcc.SEGMENT8 ACCOUNT

from gl_balances b, gl_code_combinations gcc

where

gcc.CHART_OF_ACCOUNTS_ID = 50308

and b.LEDGER_ID = :P_LEDGER_ID

and b.CODE_COMBINATION_ID = gcc.CODE_COMBINATION_ID

and gcc.SEGMENT1 between nvl(:P_FROM_COMPANY, gcc.SEGMENT1) and
nvl(:P_TO_COMPANY, gcc.SEGMENT1)

and gcc.SEGMENT2 between nvl(:P_FROM_LOCATION, gcc.SEGMENT2) and
nvl(:P_TO_LOCATION, gcc.SEGMENT2)

and gcc.SEGMENT3 between
nvl(:P_FROM_DEPARTMENT, gcc.SEGMENT3) and
nvl(:P_TO_DEPARTMENT, gcc.SEGMENT3)

and gcc.SEGMENT4 between nvl(:P_FROM_ACCOUNT, gcc.SEGMENT4) and
nvl(:P_TO_ACCOUNT, gcc.SEGMENT4)

and gcc.SEGMENT5 between nvl(:P_FROM_SEGMENT5, gcc.SEGMENT5) and
nvl(:P_TO_SEGMENT5, gcc.SEGMENT5)

and gcc.SEGMENT7 between nvl(:P_FROM_SEGMENT7, gcc.SEGMENT7) and
nvl(:P_TO_SEGMENT7, gcc.SEGMENT7)

and gcc.SEGMENT8 between nvl(:P_FROM_SEGMENT8, gcc.SEGMENT8) and
nvl(:P_TO_SEGMENT8, gcc.SEGMENT8)

) A

)

)

group by department,

future,

NATURal_account

having

sum(ACTUAL) != 0 or

sum(FORECAST) != 0 or

sum(FVSA) != 0 or

sum(BUDGET) != 0 or

sum(BVSA) != 0 or

sum(YTD_ACTUAL) != 0 or

sum(YTD_BUDGET) != 0 or

sum(YTD_BVSA) != 0 or

sum(ACTUAL_PLUS_FORECAST) != 0 or

sum(FULL_YEAR_BUD) != 0 or

sum(FULL_BUDVSACTUAL) != 0

order by department,

future,

NATURAL_account

2 comments:

saud8821 June 2018 at 04:05
Testing Required :)
Achima Abelard10 September 2020 at 09:45
We would like to acknowledge the exceptional service that we received during the entire refinancing process. Mr Lee professionalism and knowledge of the loan company was impressive and truly appreciated. Mr Lee is a reliable loan officer.In the past, we have had experience with several others banks and have found the process frustrating and tedious. Mr Lee went above and beyond to ensure that all of our needs were met and that everything was handled thoroughly and efficiently. We have and will continue to recommend him in the future.”Mr Lee Contact Email /Whatsapp 247officedept@gmail.com+1-989-394-3740

General Ledger - Oracle EBS R12

Friday, 24 February 2017

GL Balances Tables (FSG PLSQL Query) - Oracle EBS R12

2 comments: