GL Balances info and Query


To Check the Available funds

GL Super User –> Inquiry –> Funds

Now you can query the information that you need. To show it month wise you can go to Tools –> Period Balances and then if you need more information about the particulare Periods Information you can again go to Tools –> Budget Lines, Actual Lines and Encumbrance Lines accordingly.

Below is the Query for GL Balances

Funds Available in Oracle apps

SELECT
SUM (NVL (gb.begin_balance_dr, 0) – NVL (gb.begin_balance_cr, 0))
beginning_bal,
SUM(  NVL (gb.begin_balance_dr, 0)
– NVL (gb.begin_balance_cr, 0)
+ (NVL (gb.period_net_Dr, 0) – NVL (gb.period_net_cr, 0)))
end_bal
FROM   gl_balances gb, gl_code_combinations_kfv gcc
WHERE   gb.code_combination_id = gcc.code_combination_id
AND gcc.CONCATENATED_SEGMENTS =
’01-00-52217-000-00-00′   — Enter GL Account
–AND gb.ledger_id             = 2021 — Enter the Ledger
AND gb.Actual_flag = ‘E’–Encumbrance
AND gb.period_name = ‘JAN-16’                    –Enter the Period
AND gb.currency_code = ‘OMR’;

For a detailed amount where you are in need of PR and PO information you can use the below Query.

SELECT T.*, T.”Budget”-T.”Encumbrance”-T.”Actual” “Funds Available” FROM (Select      C.Segment1
|| ‘-‘
|| C.Segment2
|| ‘-‘
|| C.Segment3
|| ‘-‘
|| C.Segment4
|| ‘-‘
|| C.Segment5
|| ‘-‘
|| C.Segment6 “COA”,
TO_NUMBER(nvl(Sum (Decode (B.Actual_Flag,
‘B’, (  NVL (B.begin_balance_dr, 0)
– NVL (B.begin_balance_cr, 0)
+ (NVL (B.period_net_Dr, 0) – NVL (B.period_net_cr, 0))
)
)
) ,0) )  “Budget”,
TO_NUMBER(nvl( Sum (Decode (B.Actual_Flag,
‘E’, (  NVL (B.begin_balance_dr, 0)
– NVL (B.begin_balance_cr, 0)
+ (NVL (B.period_net_Dr, 0) – NVL (B.period_net_cr, 0))
)
)
) ,0)) + nvl(v.req_amount, 0) “Encumbrance”,
TO_NUMBER( nvl(Sum (Decode (B.Actual_Flag,
‘A’, (  NVL (B.begin_balance_dr, 0)
– NVL (B.begin_balance_cr, 0)
+ (NVL (B.period_net_Dr, 0) – NVL (B.period_net_cr, 0))
)
)
),0))  “Actual”

From Gl_Balances B, Gl_Code_Combinations C,
(select nvl(sum(LINE_ACCOUNTED_DR) – sum(LINE_ACCOUNTED_CR),0) req_amount, period_name, LINE_CODE_COMBINATION_ID ccid from  PSA_JE_BCP_LINES_V
where 1=1 –and Nvl (:P_Period, Period_Name)
and Actual_flag = ‘E’  and JE_CATEGORY = ‘Requisitions’
group by Period_Name, LINE_CODE_COMBINATION_ID ) V
Where B.Code_Combination_Id = C.Code_Combination_Id
And B.Period_Name = Nvl (:P_Period, B.Period_Name)
AND B.currency_code = ‘OMR’
&WHERE_FLEX
and V.ccid(+) = b.Code_Combination_Id
and v.period_name(+) = b.period_name
–And C.Code_Combination_Id = 2955
Group By B.Code_Combination_Id,
C.Segment1,
C.Segment2,
C.Segment3,
C.Segment4,
C.Segment5,
C.Segment6 ,
B.period_name,
v.req_amount) T
WHERE
( T.”Encumbrance”<0 OR T.”Actual”<0 );

 

About pacesettergraam

A good and realistic person

Posted on March 29, 2016, in Oracle, oracle apps and tagged , , , , , . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: