ON_GL_Balances_TO_FAP_RPT Report

 ON_GL_Balances_TO_FAP_RPT Report

WITH Prdnm AS

( select pnm.PERIOD_NAME as PERIOD_NAME from (select PERIOD_NAME 

from

GL_PERIODS GPS

WHERE

Gps.Period_Set_Name = '4-4-5'

AND TRUNC(SYSDATE)>to_date((to_char(SYSDATE,'MM')||'/'||'15/'||to_char(SYSDATE,'YYYY')),'MM/DD/YYYY')+1

AND TRUNC(SYSDATE) between  GPS.START_DATE and GPS.END_DATE

UNION

select PERIOD_NAME

from

GL_PERIODS GPS

WHERE

Gps.Period_Set_Name = '4-4-5'

AND TRUNC(SYSDATE)<to_date((to_char(SYSDATE,'MM')||'/'||'15/'||to_char(SYSDATE,'YYYY')),'MM/DD/YYYY')

AND GPS.END_DATE between (select GL.START_DATE-2 from GL_PERIODS GL where GL.Period_Set_Name = '4-4-5' and  TRUNC(SYSDATE) between GL.START_DATE and GL.END_DATE)  AND (select GL.START_DATE-1 from GL_PERIODS GL where GL.Period_Set_Name = '4-4-5' and TRUNC(SYSDATE) between GL.START_DATE and GL.END_DATE)

UNION

select PERIOD_NAME

from

GL_PERIODS GPS

WHERE

Gps.Period_Set_Name = '4-4-5'

AND TRUNC(SYSDATE) IN ( to_date((to_char(SYSDATE,'MM')||'/'||'15/'||to_char(SYSDATE,'YYYY')),'MM/DD/YYYY'),to_date((to_char(SYSDATE,'MM')||'/'||'15/'||to_char(SYSDATE,'YYYY')),'MM/DD/YYYY')+1)

AND TRUNC(SYSDATE) between  GPS.START_DATE and GPS.END_DATE

UNION

select PERIOD_NAME

from

GL_PERIODS GPS

WHERE

Gps.Period_Set_Name = '4-4-5'

AND TRUNC(SYSDATE) IN ( to_date((to_char(SYSDATE,'MM')||'/'||'15/'||to_char(SYSDATE,'YYYY')),'MM/DD/YYYY'),to_date((to_char(SYSDATE,'MM')||'/'||'15/'||to_char(SYSDATE,'YYYY')),'MM/DD/YYYY')+1)

AND GPS.END_DATE between (select GL.START_DATE-2 from GL_PERIODS GL where GL.Period_Set_Name = '4-4-5' and  TRUNC(SYSDATE) between GL.START_DATE and GL.END_DATE)  AND (select GL.START_DATE-1 from GL_PERIODS GL where GL.Period_Set_Name = '4-4-5' and TRUNC(SYSDATE) between GL.START_DATE and GL.END_DATE)

) pnm )

SELECT   1 AS KEY,

     GL.NAME LEDGER_ID,

 GLB.PERIOD_NAME,

 GLB.CURRENCY_CODE,

     GLB.ACTUAL_FLAG,

 GCC.SEGMENT1 CURR_SEGMENT1_COMPANY,

    GCC.SEGMENT2 CURR_SEGMENT2_LOCATION,

    GCC.SEGMENT3 CURR_SEGMENT3_GLOBAL_ACCOUNT,

    GCC.SEGMENT4 CURR_SEGMENT4_LOCAl_ACCOUNT,

    GCC.SEGMENT5 CURR_SEGMENT5_DEPARTMENT,

GCC.SEGMENT6 CURR_SEGMENT6_PAL_CODE,

    GCC.SEGMENT7 CURR_SEGMENT7_INTERCOMPANY,

    GCC.SEGMENT8 CURR_SEGMENT8_PROGRAM,

    GCC.SEGMENT9 CURR_SEGMENT9_FUTURE,

--GCC.SEGMENT1 SEGMENT1_CO,

--GCC.SEGMENT6 SEGMENT7_BU,

 GLB.BEGIN_BALANCE_DR,

     GLB.BEGIN_BALANCE_CR,

TO_CHAR(SUM(GLB.BEGIN_BALANCE_DR) - SUM(GLB.BEGIN_BALANCE_CR),'0000000.00') AS BEGINNING_BALANCE,

     GLB.PERIOD_NET_DR PERIOD_NET_DR,

     GLB.PERIOD_NET_CR PERIOD_NET_CR,

    TO_CHAR(SUM(GLB.PERIOD_NET_DR) - SUM(GLB.PERIOD_NET_CR), '0000000.00') AS PTD_ACTIVITY,

   TO_CHAR(SUM(GLB.BEGIN_BALANCE_DR) + SUM(GLB.PERIOD_NET_DR), '0000000.00') AS YTD_DR,

   TO_CHAR(SUM(GLB.BEGIN_BALANCE_CR) + SUM(GLB.PERIOD_NET_CR), '0000000.00') AS YTD_CR,

to_char(sysdate,'YYYYDDMMSS')||TO_CHAR(GL.LEDGER_ID)||to_char(GLB.CODE_COMBINATION_ID)||ROWNUM as RECORD_KEY

 FROM 

    GL_BALANCES GLB,

    GL_CODE_COMBINATIONS GCC,

GL_LEDGERS GL,

Prdnm

WHERE

    GLB.CODE_COMBINATION_ID = GCC.CODE_COMBINATION_ID 

AND GLB.LEDGER_ID=GL.LEDGER_ID

AND GL.NAME    = CASE WHEN :P_LEDGER_ID='All' THEN GL.NAME WHEN :P_LEDGER_ID IS NULL THEN GL.NAME ELSE :P_LEDGER_ID END

AND UPPER(GLB.PERIOD_NAME)  = CASE WHEN :P_PERIOD_NAME = 'Request fromESS Job'

                       THEN UPPER(Prdnm.PERIOD_NAME)

   ELSE UPPER(:P_PERIOD_NAME)

   END

GROUP BY

    GLB.LEDGER_ID,

    GLB.PERIOD_NAME,

    GLB.ACTUAL_FLAG,

    GLB.CURRENCY_CODE,

    GLB.BEGIN_BALANCE_DR,

    GLB.BEGIN_BALANCE_CR,

    GLB.PERIOD_NET_DR,

    GLB.PERIOD_NET_CR,

    GCC.SEGMENT1,

    GCC.SEGMENT2,

    GCC.SEGMENT3,

    GCC.SEGMENT4,

    GCC.SEGMENT5,

    GCC.SEGMENT7,

    GCC.SEGMENT8,

    GCC.SEGMENT9,

GCC.SEGMENT6,

GL.NAME,

to_char(sysdate,'YYYYDDMMSS')||TO_CHAR(GL.LEDGER_ID)||to_char(GLB.CODE_COMBINATION_ID)||ROWNUM