General Ledger - Oracle EBS R12: February 2017

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

http://generalledger-gl.blogspot.com/2017/02/budget-inquiry.html

PLSQL Query to Get Budget Inquiry

select

-- gjh.je_header_id

-- ,gjh.doc_sequence_value

gjh.default_effective_date

,
gjh.date_created

,
decode(gjh.status, 'P', 'Posted', 'U', 'Unposted') status

,
gjh.name

,
gjh.name

,
gjh.description h_description

,
decode(gjh.accrual_rev_status, 'R', 'Reversed', 'Not Reversed') revstatus

,
gjc.user_je_category_name

,
gjh.je_source

,
gjh.je_category

-- ,gjh.posting_acct_seq_value

,
gjh.currency_code

,
gjh.currency_conversion_type

,
gjh.currency_conversion_rate

,
gjh.currency_conversion_date

,
fu.user_name

,
fu.employee_id

,
gjl.je_line_num

-- ,gjl.code_combination_id

-- ,gcc.segment7 glcode

,
sum(gjl.entered_dr)

,
sum(gjl.entered_cr)

,
sum(gjl.accounted_dr)

,
sum(gjl.accounted_cr)

,
gjl.description

,
gcc.SEGMENT2 Loc

,
gcc.SEGMENT4 Account

/*,gcc.segment1||'-'||gcc.segment2||'-'||gcc.segment3||'-'||

gcc.segment4||'-'||gcc.segment5||'-'||gcc.segment6||'-'||gcc.segment7 code_comb*/

/* ,fs1.DESCRIPTION description1

,fs2.DESCRIPTION description2

,fs3.DESCRIPTION description3

,fs4.DESCRIPTION description4

,fs5.DESCRIPTION description5

,fs6.DESCRIPTION description6

,fs7.DESCRIPTION description7*/

/* ,fs1.DESCRIPTION ||'-'||fs2.DESCRIPTION ||'-'||fs3.DESCRIPTION||'-'||fs4.DESCRIPTION

||'-'||fs5.DESCRIPTION||'-'||fs6.DESCRIPTION||'-'||fs7.DESCRIPTION

Account_description*/

/*, (SELECT GLC.DESCRIPTION

FROM GL_JE_CATEGORIES_TL GLC

WHERE

GLC.JE_CATEGORY_NAME= gjh.JE_CATEGORY)

||'-'||gjh.doc_sequence_value doc_no2 ,

(SELECT GLC.DESCRIPTION

FROM GL_JE_CATEGORIES_TL GLC

WHERE

GLC.JE_CATEGORY_NAME= gjh.JE_CATEGORY) SSCRPT ,

(

select max( gir.SUBLEDGER_DOC_SEQUENCE_VALUE )

from

gl_import_references gir

,xla.xla_transaction_entities ent

,xla_ae_headers aeh

,xla_ae_lines ael

where

gjl.je_header_id = gir.je_header_id

and gjl.je_line_num = gir.je_line_num

and gjh.je_header_id = gir.je_header_id

and gir.gl_sl_link_id = ael.gl_sl_link_id

and gir.gl_sl_link_table = ael.gl_sl_link_table

and aeh.ae_header_id = ael.ae_header_id

and aeh.application_id = ael.application_id

and aeh.period_name = gjh.period_name

and aeh.entity_id = ent.entity_id

) SUBLEDGER_DOC_SEQUENCE_VALUE*/

from gl_je_headers gjh

,
gl_je_lines gjl

,
gl_code_combinations gcc

,
fnd_user fu

,
gl_je_categories gjc

,
fnd_flex_values_vl fs1

,
fnd_flex_values_vl fs2

,
fnd_flex_values_vl fs3

,
fnd_flex_values_vl fs4

,
fnd_flex_values_vl fs5

,
fnd_flex_values_vl fs6

,
fnd_flex_values_vl fs7

where 1 = 1

and GJH.ACTUAL_FLAG = 'B'

and gjl.ledger_id = 2021 --:P_Ledger_ID

and gjh.ledger_id = 2021 --:P_Ledger_ID

and gjh.je_header_id = gjl.je_header_id

and gjl.code_combination_id = gcc.code_combination_id

and gjh.created_by = fu.user_id

and gjh.je_category = gjc.je_category_name

and fs1.FLEX_VALUE = gcc.segment1

and fs1.FLEX_VALUE_SET_ID = 1013507

and fs2.FLEX_VALUE = gcc.segment2

and fs2.FLEX_VALUE_SET_ID = 1013508

and fs3.FLEX_VALUE = gcc.segment3

and fs3.FLEX_VALUE_SET_ID = 1013509

and fs4.FLEX_VALUE = gcc.segment4

and fs4.FLEX_VALUE_SET_ID = 1013510

and fs5.FLEX_VALUE = gcc.segment5

and fs5.FLEX_VALUE_SET_ID = 1013511

and fs6.FLEX_VALUE = gcc.segment6

and fs6.FLEX_VALUE_SET_ID = 1013512

and fs7.FLEX_VALUE = gcc.segment7

and fs7.FLEX_VALUE_SET_ID = 1013513

and gjh.je_category = nvl('&cat', gjh.je_category)

-- and gjh.je_source = nvl(:src,gjh.je_source)

-- and gjh.status = nvl(:sts,gjh.status)

and trunc(gjh.default_effective_date) between '&F_D' AND '&T_D'

-- and nvl(gjh.doc_sequence_value,0) between nvl(:v_f,0) and nvl(:v_t,9999999999999999999999999)

and gcc.SEGMENT2 between NVL('&From_Location', gcc.SEGMENT2) and
nvl('&TO_Location', gcc.segment2)

and gcc.SEGMENT4 between NVL('&From_Account', gcc.SEGMENT4) and
nvl('&TO_Account', gcc.segment4)

---and gjh.currency_code between NVL(:FROM_CCY, gjh.currency_code) and NVL(:TO_CCY,gjh.currency_code)

--AND FU.USER_ID=NVL(:P_USER_ID,FU.USER_ID)

--AND NVL(FU.FAX,'ALL')=NVL(:P_USER_SITE,NVL(FU.FAX,'ALL'))

group by --gjh.je_header_id

-- ,gjh.doc_sequence_value

gjh.default_effective_date

,
gjh.date_created

,
decode(gjh.status, 'P', 'Posted', 'U', 'Unposted')

,
gjh.name

,
gjh.name

,
gjh.description

,
decode(gjh.accrual_rev_status, 'R', 'Reversed', 'Not Reversed')

,
gjc.user_je_category_name

,
gjh.je_source

,
gjh.je_category

-- ,gjh.posting_acct_seq_value

,
gjh.currency_code

,
gjh.currency_conversion_type

,
gjh.currency_conversion_rate

,
gjh.currency_conversion_date

,
fu.user_name

,
fu.employee_id

,
gjl.je_line_num

-- ,gjl.code_combination_id

,
gjl.description

,
gcc.SEGMENT2

,
gcc.SEGMENT4

order by gjh.date_created, gjl.je_line_num --,gjl.accounted_dr,gjl.accounted_cr

General Ledger - Oracle EBS R12

Friday 24 February 2017

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

Friday 17 February 2017

Budget Inquiry PLSQL Query - Oracle EBS R12