My Oracle Support Banner

Cash Draw Report Points Towards End Of Period Balance As Opposed To The Ending Balance (Doc ID 2227416.1)

Last updated on SEPTEMBER 11, 2020

Applies to:

Oracle Fusion General Ledger Cloud Service - Version 11.1.11.1.0 and later
Information in this document applies to any platform.

Goal

We created a custom trial balance based report that could be run at any day. Could you help us get the ending balance as opposed to the period balance for each account? We need this report to continue with our operations, because without knowing every week how much cash we have for each grant, we can not make payments.
SELECT --GLL.NAME
segvals.description
, GCC.SEGMENT2 "ACCOUNT"
, GCC.SEGMENT4 "PROGRAM"
, SUM( NVL(GJL.ACCOUNTED_DR,0) - NVL(GJL.ACCOUNTED_CR,0))"END BALANCE"
FROM GL_BALANCES GB, GL_CODE_COMBINATIONS GCC, GL_LEDGERS GLL, GL_JE_HEADERS GJH, GL_JE_LINES GJL, fusion.fnd_vs_values_vl segvals, fusion.fnd_vs_value_sets segvalsets
WHERE GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID
AND GB.ACTUAL_FLAG = 'A'
AND GB.CURRENCY_CODE = GLL.CURRENCY_CODE
AND GB.TEMPLATE_ID IS NULL
AND GB.LEDGER_ID = GLL.LEDGER_ID
AND GLL.NAME=:P_GL_LEDGER
AND GJL.EFFECTIVE_DATE between nvl(:P_GL_Date1, GJL.EFFECTIVE_DATE) and nvl(:P_GL_Date2, GJL.EFFECTIVE_DATE)
AND GB.PERIOD_NAME=:GL_PERIOD
AND GCC.SEGMENT3=:P_SEGMENT3
AND (GCC.SEGMENT2='12000'
OR GCC.SEGMENT2='13100')
AND GJL.CODE_COMBINATION_ID=GB.CODE_COMBINATION_ID
AND GJH.JE_HEADER_ID=GJL.JE_HEADER_ID
AND segvals.value=GCC.SEGMENT4
AND segvals.value_set_id = segvalsets.value_Set_id
AND segvalsets.value_Set_id IN
(SELECT value_set_id
  FROM FUSION.fnd_kf_segment_instances
 WHERE structure_instance_id IN
 (SELECT fusion.fnd_kf_str_instances_b.structure_instance_id
  FROM FUSION.fnd_kf_str_instances_b
  WHERE application_id =101
 AND key_flexfield_code = 'GL#'
 ))
AND segvalsets.value_set_code='PROGRAM CSNCFL'
GROUP BY GCC.SEGMENT2
, GCC.SEGMENT4
, GLL.NAME
,segvals.description
HAVING SUM(( NVL(GB.PERIOD_NET_DR,0) + NVL(GB.BEGIN_BALANCE_DR,0))) - SUM(NVL(GB.PERIOD_NET_CR,0)+NVL(GB.BEGIN_BALANCE_CR,0)) <> 0
ORDER BY GCC.SEGMENT4 ASC
 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.