Query for Intercompany Imbalance Report

 SELECT A.*, A.US_Ledger_amount + A.CA_Ledger_amount Imbalance

FROM (

    SELECT  --LEDGER             ,

Comp_Inter_acct    ,

SUM(US_Ledger_amount) US_Ledger_amount   ,

SUM(CA_Ledger_amount) CA_Ledger_amount   ,

Accounting_Year    ,

Accounting_Period

FROM  

( SELECT  

GL.NAME LEDGER,

CASE WHEN GL.NAME = 'XXXXXXXXXXXXXX'

THEN GCC.SEGMENT1||'-'||GCC.SEGMENT7||'-'|| GCC.SEGMENT4

WHEN GL.NAME = 'XXXXXXXXX'

THEN GCC.SEGMENT7||'-'||GCC.SEGMENT1||'-'|| GCC.SEGMENT4

END Comp_Inter_acct,

CASE WHEN GL.NAME = 'XXXXXXXXXXX'

THEN SUM( NVL(GB.PERIOD_NET_DR,0) -  NVL(GB.PERIOD_NET_CR ,0 )) 

   ELSE 0 

   END US_Ledger_amount,

CASE WHEN GL.NAME = 'XXXXXXXX'

-- AND ((GB.translated_flag is null) or (GB.translated_flag = 'Y'))

AND ((GB.translated_flag = 'N') or (GB.translated_flag = 'Y'))

THEN SUM( NVL(GB.PERIOD_NET_DR,0) -  NVL(GB.PERIOD_NET_CR ,0 )) 

ELSE 0 

END CA_Ledger_amount,

GB.PERIOD_YEAR Accounting_Year,

GB.PERIOD_NUM Accounting_Period

FROM GL_CODE_COMBINATIONS GCC,

GL_BALANCES GB,

GL_LEDGERS GL

-- GL_JE_HEADERS GJH,

-- GL_JE_LINES GJL

WHERE 1=1

AND GCC.SEGMENT4 IN ('370500') --, '370000') 

AND  NVL(GB.PERIOD_NET_DR,0) -  NVL(GB.PERIOD_NET_CR ,0 ) != 0

-- AND GCC.SEGMENT1||'-'||GCC.SEGMENT7||'-'|| GCC.SEGMENT4 IN ( 'Z01378-C01031-370500')

-- AND GB.PERIOD_NAME  = 'May-24' 

AND GCC.CODE_COMBINATION_ID = GB.CODE_COMBINATION_ID

-- AND GB.CURRENCY_CODE = GL.CURRENCY_CODE

AND GB.LEDGER_ID = GL.LEDGER_ID  

AND GB.CURRENCY_CODE = 'USD'

AND GL.NAME IN (  XXXXXXXXXX )

AND GL.LEDGER_ID     IN (SELECT gl.ledger_id

FROM fun_user_role_data_asgnmnts f,

per_roles_dn_vl p,

per_users pu,

gl_ledgers gl

   WHERE p.role_common_name = f.role_name

AND pu.user_guid = f.user_guid

AND gl.ledger_id = f.ledger_id

AND f.active_flag = 'Y' 

and upper(pu.username) = upper(:xdo_user_name))

AND GB.PERIOD_YEAR = :P_YEAR

AND GB.PERIOD_NUM =   :P_NUM

-- AND GCC.SEGMENT4 =   NVL(:P_ACCOUNT, GCC.SEGMENT4)  

-- AND GJH.JE_HEADER_ID = GJL.JE_HEADER_ID

-- AND GJH.ledger_id = GJL.ledger_id

-- AND GJH.LEDGER_ID = GL.LEDGER_ID  

-- AND GCC.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID 

-- AND GJH.status = 'P'

-- AND GJL.PERIOD_NAME  = 'May-24'  

-- AND GB.PERIOD_NAME =   GJL.PERIOD_NAME

-- AND GB.LEDGER_ID = GJL.LEDGER_ID  

-- AND GB.CODE_COMBINATION_ID = GJL.CODE_COMBINATION_ID 

GROUP BY GL.NAME, GCC.SEGMENT1, GCC.SEGMENT7, GCC.SEGMENT4, 

GB.TRANSLATED_FLAG, GB.PERIOD_YEAR, GB.PERIOD_NUM 

)

WHERE 1=1

   AND (US_Ledger_amount + CA_Ledger_amount) != 0    

GROUP BY --LEDGER             ,

Comp_Inter_acct    ,

Accounting_Year    ,

Accounting_Period

)A

WHERE 1=1

AND (A.US_Ledger_amount + A.CA_Ledger_amount) != 0