SELECT GJL.EFFECTIVE_DATE ,
GCC.SEGMENT4 ACCOUNT\_CODE,
GCC.SEGMENT1 ENTITY\_CODE,
GJH.PERIOD\_NAME,
GJH.JE\_HEADER\_ID,
GJL.DESCRIPTION DESCRIPTION,
(SELECT GJL.EFFECTIVE\_DATE ,
GCC.SEGMENT4 ACCOUNT\_CODE,
GCC.SEGMENT1 ENTITY\_CODE,
GJH.PERIOD\_NAME,
GJH.JE\_HEADER\_ID,
GJL.DESCRIPTION DESCRIPTION,
NVL (GJL.ACCOUNTED\_DR, 0) DEBITS,
NVL (GJL.ACCOUNTED\_CR, 0) CREDITS,
from
GL\_JE\_LINES GJL,
GL\_JE\_HEADERS GJH,
GL\_CODE\_COMBINATIONS GCC,
GL\_JE\_SOURCES GS,
GL\_LEDGERS gl
WHERE 1 = 1
AND GJH.LEDGER\_ID = GJL.LEDGER\_ID
AND GJH.JE\_SOURCE=GS.JE\_SOURCE\_NAME
AND GJL.CODE\_COMBINATION\_ID = GCC.CODE\_COMBINATION\_ID
AND GJL.JE\_HEADER\_ID = GJH.JE\_HEADER\_ID
AND TRUNC (GJL.EFFECTIVE\_DATE) \< TO\_DATE('31-03-'|| ( SELECT GP.PERIOD\_YEAR-1
FROM GL\_PERIODS GP
WHERE 1=1
and gp.ADJUSTMENT\_PERIOD\_FLAG = 'N'
--AND GP.PERIOD\_YEAR = :P\_YEAR
AND GP.PERIOD\_YEAR||lpad(GP.PERIOD\_NUM,2,0) = :P\_PERIOD
)-1,'dd-MM-yyyy')
and ( SELECT GP.END\_DATE
FROM GL\_PERIODS GP
WHERE 1=1
and gp.ADJUSTMENT\_PERIOD\_FLAG = 'N'
--AND GP.PERIOD\_YEAR = :P\_YEAR
AND GP.PERIOD\_YEAR||lpad(GP.PERIOD\_NUM,2,0) = :P\_PERIOD
)
AND (GCC.SEGMENT1 in (:P\_ENTITY) OR 'All' IN (:P\_ENTITY||'All'))
AND GJL.CURRENCY\_CODE != 'STAT'
and GJH.Ledger\_id = GL.LEDGER\_ID
AND GL.NAME = :P\_LEDGER
) BEGINING\_BALANCE,
NVL (GJL.ACCOUNTED\_DR, 0) DEBITS,
NVL (GJL.ACCOUNTED\_CR, 0) CREDITS,
NVL (GJL.ACCOUNTED\_DR, 0)-NVL (GJL.ACCOUNTED\_CR, 0) ENDING\_BALANCE
from
GL\_JE\_LINES GJL,
GL\_JE\_HEADERS GJH,
GL\_CODE\_COMBINATIONS GCC,
GL\_JE\_SOURCES GS,
GL\_LEDGERS gl
WHERE 1 = 1
AND GJH.LEDGER\_ID = GJL.LEDGER\_ID
AND GJH.JE\_SOURCE=GS.JE\_SOURCE\_NAME
AND GJL.CODE\_COMBINATION\_ID = GCC.CODE\_COMBINATION\_ID
AND GJL.JE\_HEADER\_ID = GJH.JE\_HEADER\_ID
AND TRUNC (GJL.EFFECTIVE\_DATE) BETWEEN TO\_DATE('01-04-'|| ( SELECT GP.PERIOD\_YEAR-1
FROM GL\_PERIODS GP
WHERE 1=1
and gp.ADJUSTMENT\_PERIOD\_FLAG = 'N'
--AND GP.PERIOD\_YEAR = :P\_YEAR
AND GP.PERIOD\_YEAR||lpad(GP.PERIOD\_NUM,2,0) = :P\_PERIOD
),'dd-MM-yyyy')
and ( SELECT GP.END\_DATE
FROM GL\_PERIODS GP
WHERE 1=1
and gp.ADJUSTMENT\_PERIOD\_FLAG = 'N'
--AND GP.PERIOD\_YEAR = :P\_YEAR
AND GP.PERIOD\_YEAR||lpad(GP.PERIOD\_NUM,2,0) = :P\_PERIOD
)
AND (GCC.SEGMENT1 in (:P\_ENTITY) OR 'All' IN (:P\_ENTITY||'All'))
AND GJL.CURRENCY\_CODE != 'STAT'
and GJH.Ledger\_id = GL.LEDGER\_ID
AND GL.NAME = :P\_LEDGER