GL Account Analysis Bal And JE Report
SELECT 'Balances' Data_Source,
Glh.Ledger_Id Ledger_Id,
Lgr.Name Ledger_Name,
Glh.Period_Name Period,
Glcc.Segment1 Co,
Glcc.Segment2 Loc,
Glcc.Segment3 Gac,
Glcc.Segment4 Lac,
Glcc.Segment5 Dept,
-- Glcc.Segment5 Ico,
Glcc.Segment6 Bu,
Glcc.Segment7 Ico,
Glcc.Segment8 Proj,
-- Glcc.Segment7 Bu,
Glcc.Segment9 Fut,
NULL Batch_Name,
NULL Header_Name,
NULL Posted_Date,
NULL Source_Name,
NULL Category_Name,
NULL Je_Line_Num,
NULL Je_Line_Description,
NULL Je_Line_Creation_Date,
NULL Je_Line_Created_Id,
NULL Je_Line_Created_Name,
Glh.CURRENCY_CODE Entered_Curr,
Lgr.Currency_Code Ledger_Curr,
null STATUS,
sum(Nvl(Glh.Begin_Balance_Dr, 0) - Nvl(Glh.Begin_Balance_Cr, 0)) Beg_Balance_Net,
SUM((Nvl(Glh.Period_Net_Dr, 0) + Nvl(Glh.Begin_Balance_Dr, 0)) -
(Nvl(Glh.Period_Net_Cr, 0) + Nvl(Glh.Begin_Balance_Cr, 0))) End_Balance_Net,
sum(Nvl(Glh.Period_Net_Dr, 0) - Nvl(Glh.Period_Net_Cr, 0)) Acctd_Period_Net,
NULL Entered_Period_Net,
SYSDATE TIMESTAMP
FROM Gl_Balances Glh,
Gl_Ledgers Lgr,
Gl_Code_Combinations Glcc
where
Actual_Flag = 'A'
AND Lgr.Ledger_Id = Glh.Ledger_Id
AND Glcc.Code_Combination_Id = Glh.Code_Combination_Id
AND Glh.Translated_Flag IS NULL
--and Glh.Ledger_Id='300000419068712'
--and Glh.Period_Name='SEP-24'
--And nvl(:P_CONSO_SOURCE_JES, 'N')= 'Y' --added this sincs commented below condition
/* AND EXISTS
(SELECT 1
FROM Gl_Consolidation Sgc
WHERE EXISTS
(SELECT 1
FROM Gl_Cons_Map_Set_v s,
Gl_Cons_Set_Assign_v a
WHERE s.Consolidation_Set_Id = a.Consolidation_Set_Id
AND a.Consolidation_Id = Sgc.Consolidation_Id)
AND Sgc.From_Ledger_Id = Lgr.Ledger_Id
AND Sgc.From_Currency_Code = 'USD'
AND Sgc.Name NOT LIKE 'ZZZ%'
AND Nvl('&PARM10', 'N') = 'Y'
UNION ALL
SELECT 1
FROM Dual
WHERE Nvl('&PARM10', 'N') = 'N') */ --comment
and ((coalesce (null,:p_Period_Name) is null)
or (Glh.Period_Name in(:p_Period_Name))
or 'ALL' in(:p_Period_Name))
and ((coalesce (null,:p_LEDGER_NAME) is null)
or (Lgr.Name in(:p_LEDGER_NAME))
or 'ALL' in(:p_LEDGER_NAME))
and ((coalesce (null,:p_Co) is null)
or (glcc.Segment1 in(:p_Co))
or 'ALL' in(:p_Co))
and ((coalesce (null,:p_Gac) is null)
or (glcc.Segment3 in(:p_Gac))
or 'ALL' in(:p_Gac))
and ((coalesce (null,:p_Lac) is null)
or (glcc.Segment4 in(:p_Lac))
or 'ALL' in(:p_Lac))
and ((coalesce (null,:p_Dept) is null)
or (glcc.Segment5 in(:p_Dept))
or 'ALL' in(:p_Dept))
and ((coalesce (null,:p_Ico) is null)
or (glcc.Segment7 in(:p_Ico))
or 'ALL' in(:p_Ico))
and ((coalesce (null,:p_Proj) is null)
or (glcc.Segment8 in(:p_Proj))
or 'ALL' in(:p_Proj))
and ((coalesce (null,:p_Bu) is null)
or (glcc.Segment6 in(:p_Bu))
or 'ALL' in(:p_Bu))
and ((coalesce (null,:p_Loc) is null)
or (glcc.Segment2 in(:p_Loc))
or 'ALL' in(:p_Loc))
GROUP BY
Glh.Ledger_Id,
Lgr.Name,
Glh.Period_Name,
Glcc.Segment1,
Glcc.Segment2,
Glcc.Segment3,
Glcc.Segment4,
Glcc.Segment5,
-- Glcc.Segment5,
Glcc.Segment6,
Glcc.Segment7,
Glcc.Segment8,
-- Glcc.Segment7 Bu,
Glcc.Segment9,
Lgr.Currency_Code ,
Glh.CURRENCY_CODE
--Glb.STATUS
HAVING SUM(NVL(Glh.Begin_Balance_Dr, 0) - NVL(Glh.Begin_Balance_Cr, 0)) <> 0
OR SUM((NVL(Glh.Period_Net_Dr, 0) + NVL(Glh.Begin_Balance_Dr, 0)) -
(NVL(Glh.Period_Net_Cr, 0) + NVL(Glh.Begin_Balance_Cr, 0))) <> 0
UNION ALL
SELECT 'Journals' Data_Source,
Glh.Ledger_Id Ledger_Id,
Lgr.Name Ledger_Name,
Glh.Period_Name Period,
Glcc.Segment1 Co,
Glcc.Segment2 Loc,
-- Glcc.Segment1 Co,
Glcc.Segment3 Gac,
Glcc.Segment4 Lac,
Glcc.Segment5 Dept,
-- Glcc.Segment5 Ico,
Glcc.Segment6 Bu,
Glcc.Segment7 Ico,
Glcc.Segment8 Proj,
-- Glcc.Segment7 Bu,
Glcc.Segment9 Fut,
Glb.Name Batch_Name,
Glh.Name Header_Name,
Glh.Posted_Date Posted_Date,
Gljs.User_Je_Source_Name Source_Name,
Gljc.User_Je_Category_Name Category_Name,
Gll.Je_Line_Num Je_Line_Num,
Gll.Description Je_Line_Description,
Gll.Creation_Date Je_Line_Creation_Date,
Fndu.User_ID Je_Line_Created_Id, --comment
Fndu.Username Je_Line_Created_Name,--comment
NVL(Glh.Currency_Code,Gll.Currency_Code) Entered_Curr,
Lgr.Currency_Code Ledger_Curr,
Glb.STATUS,
NULL Beg_Balance_Net,
NULL End_Balance_Net,
sum(Nvl(Gll.Accounted_Dr, 0) - Nvl(Gll.Accounted_Cr, 0)) Acctd_Period_Net,
sum(Nvl(Gll.Entered_Dr, 0) - Nvl(Gll.Entered_Cr, 0)) Entered_Period_Net,
SYSDATE TIMESTAMP
FROM Gl_Ledgers Lgr,
Gl_Je_Headers Glh,
Gl_Je_Lines Gll,
Gl_Code_Combinations Glcc,
Gl_Je_Batches Glb,
per_Users Fndu, --comment
Gl_Je_Sources_Tl Gljs,
Gl_Je_Categories_Tl Gljc
WHERE
Glh.Ledger_Id = Lgr.Ledger_Id
AND Glh.Je_Header_Id = Gll.Je_Header_Id
AND Gll.Code_Combination_Id = Glcc.Code_Combination_Id
AND Glh.Je_Batch_Id = Glb.Je_Batch_Id
AND Glh.Je_Source = Gljs.Je_Source_Name
AND Gljs.Language = 'US'
AND Glh.Je_Category = Gljc.Je_Category_Name
AND Gljc.Language = 'US'
AND Fndu.Username(+)= Gll.Created_By
and UPPER(Glb.STATUS)<>'U'
--and Glb.Name='Payables A 7549348000001 7549347 Y 1'
--And nvl(:P_CONSO_SOURCE_JES, 'N')= 'Y' --added this sincs commented below condition
/* and exists
(select 1
from gl_consolidation sgc
where exists
(select 1
from gl_cons_map_set_v s, --GL_COA_MAP_SEGMENT_RULES
gl_cons_set_assign_v a --GL_ACCESS_SET_ASSIGNMENTS1
where s.consolidation_set_id = a.consolidation_set_id
and a.consolidation_id = sgc.consolidation_id)
and sgc.from_ledger_id = Lgr.ledger_id
and sgc.from_currency_code = 'USD'
and sgc.name not like 'ZZZ%'
and nvl('&parm10', 'N') = 'Y'
union
select 1
from dual
where Lgr.name = 'USD CL'
and Gljs.user_je_source_name <> 'Consolidation'
and nvl('&parm10', 'N') = 'Y'
union all
select 1
from dual
where nvl('&parm10', 'N') = 'N') */ --comment
and ((coalesce (null,:p_Period_Name) is null)
or (Glh.Period_Name in(:p_Period_Name))
or 'ALL' in(:p_Period_Name))
and ((coalesce (null,:p_LEDGER_NAME) is null)
or (Lgr.Name in(:p_LEDGER_NAME))
or 'ALL' in(:p_LEDGER_NAME))
and ((coalesce (null,:p_Co) is null)
or (glcc.Segment1 in(:p_Co))
or 'ALL' in(:p_Co))
and ((coalesce (null,:p_Gac) is null)
or (glcc.Segment3 in(:p_Gac))
or 'ALL' in(:p_Gac))
and ((coalesce (null,:p_Lac) is null)
or (glcc.Segment4 in(:p_Lac))
or 'ALL' in(:p_Lac))
and ((coalesce (null,:p_Dept) is null)
or (glcc.Segment5 in(:p_Dept))
or 'ALL' in(:p_Dept))
and ((coalesce (null,:p_Ico) is null)
or (glcc.Segment7 in(:p_Ico))
or 'ALL' in(:p_Ico))
and ((coalesce (null,:p_Proj) is null)
or (glcc.Segment8 in(:p_Proj))
or 'ALL' in(:p_Proj))
and ((coalesce (null,:p_Bu) is null)
or (glcc.Segment6 in(:p_Bu))
or 'ALL' in(:p_Bu))
and ((coalesce (null,:p_Loc) is null)
or (glcc.Segment2 in(:p_Loc))
or 'ALL' in(:p_Loc))
GROUP BY Glh.Ledger_Id,
Lgr.Name,
Glh.Period_Name,
-- Glcc.Segment1,
-- Glcc.Segment2,
-- Glcc.Segment3,
-- Glcc.Segment4,
-- Glcc.Segment5,
-- Glcc.Segment6,
-- Glcc.Segment7,
-- Glcc.Segment8,
-- Glcc.Segment4,
Glcc.Segment1,
Glcc.Segment2,
Glcc.Segment3,
Glcc.Segment4,
Glcc.Segment5,
-- Glcc.Segment5 Ico,
Glcc.Segment6,
Glcc.Segment7,
Glcc.Segment8,
-- Glcc.Segment7 Bu,
Glcc.Segment9,
Glb.Name,
Glh.Name,
Glh.Posted_Date,
Gljs.User_Je_Source_Name,
Gljc.User_Je_Category_Name,
Gll.Je_Line_Num,
Gll.Description,
Gll.Creation_Date,
Fndu.User_ID,
Fndu.Username,
Glh.Currency_Code,
Lgr.Currency_Code,
Gll.Currency_Code,
Glb.STATUS,
Gll.Created_By
HAVING SUM(NVL(Gll.Entered_Dr, 0) - NVL(Gll.Entered_Cr, 0)) <> 0
OR SUM(NVL(Gll.Accounted_Dr, 0) - NVL(Gll.Accounted_Cr, 0)) <> 0
No comments:
Post a Comment