GL Tax Trial Balance Report
SELECT Bal.Ledger_Id,
Bal.Name Ledger_Name,
Bal.Period_Name,
To_Number(To_Char(To_Date(Bal.Period_name, 'MON-YY','NLS_DATE_LANGUAGE=AMERICAN'), 'YYYYMM')) Period_YYYYMM,
-- To_Number(To_Char(To_Date(Bal.Period_Name, 'MON-YY'), 'YYYYMM')) Period_Yyyymm,
Xglcc.Segment1 Co,
xglcc.Segment2 Location,
Xglcc.Segment3 Gac,
xglcc.segment8 Proj,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS3_GAC'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = XGlcc.Segment3) Gac_Desc,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS1_ENT'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = XGlcc.Segment1)Co_Desc,
--nvl(Plopr.Unique_Identifier, Bsheet.Segment2) Unique_Identifier,
SUM(((Nvl(Bal.Period_Net_Dr, 0) + Nvl(Bal.Begin_Balance_Dr, 0)) -
(Nvl(Bal.Period_Net_Cr, 0) + Nvl(Bal.Begin_Balance_Cr, 0)))) Eb,
Bal.Currency_Code Book_Currency_code,
--Bsheet.Fs_Cat_2 Bsheet_Fs_Cat_2,
-- Plopr.Fs_Cat_2 Plopr_Fs_Cat_2,
--SYSDATE TIMESTAMP
to_char (SYSDATE, 'dd/mm/yyyy hh:mm:ss PM') TIMESTAMP
FROM ((SELECT Gl.Name,
Gl.Ledger_Id,
Bal.Period_Name,
Bal.Period_Year,
Bal.Currency_Code,
Bal.Period_Net_Dr,
Bal.Period_Net_Cr,
Bal.Begin_Balance_Dr,
Bal.Begin_Balance_Cr,
Code_Combination_Id
FROM Gl_Balances Bal, Gl_Ledgers Gl
WHERE Gl.Ledger_Id = Bal.Ledger_Id
AND Bal.Actual_Flag = 'A'
AND GL.Currency_Code <> 'STAT'
AND Bal.Translated_Flag IS NULL
) )BAL,
Gl_Code_Combinations_v Xglcc,
Gl_Periods Gp
WHERE 1=1
AND Xglcc.Code_Combination_Id = Bal.Code_Combination_Id
AND Gp.Period_Name = Bal.Period_Name
AND Gp.Period_Set_Name = '4-4-5'
and (CASE
WHEN Gp.Period_Name IN ( :p_period ) THEN 1
WHEN ( coalesce(NULL, :p_period) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN bal.Name IN ( :p_ledger ) THEN 1
WHEN ( coalesce(NULL, :p_ledger) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN xGlcc.Segment1 IN ( :p_company ) THEN 1
WHEN ( coalesce(NULL, :p_company) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN xGlcc.segment3 IN ( :p_gac ) THEN 1
WHEN ( coalesce(NULL, :p_gac) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN xGlcc.segment5 IN ( :p_dept ) THEN 1
WHEN ( coalesce(NULL, :p_dept) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN xGlcc.segment7 IN ( :p_ico ) THEN 1
WHEN ( coalesce(NULL, :p_ico) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN xGlcc.segment8 IN ( :p_proj ) THEN 1
WHEN ( coalesce(NULL, :p_proj) IS NULL ) THEN 1
END = 1 )
GROUP BY
Bal.Ledger_Id,
Bal.Name,
Bal.Period_Name,
-- To_Number(To_Char(To_Date(Bal.Period_name, 'MON-YY','NLS_DATE_LANGUAGE=AMERICAN'), 'YYYYMM')) Period_YYYYMM,
XGLCC.SEGMENT1,
xglcc.segment2,
XGLCC.SEGMENT3,
xglcc.segment8,
Bal.Currency_Code
HAVING SUM(((Nvl(Bal.Period_Net_Dr, 0) + Nvl(Bal.Begin_Balance_Dr, 0)) -
(Nvl(Bal.Period_Net_Cr, 0) + Nvl(Bal.Begin_Balance_Cr, 0)))) <> 0
**SELECT Bal.Ledger_Id,
Bal.Name Ledger_Name,
Bal.Period_Name,
To_Number(To_Char(To_Date(Bal.Period_name, 'MON-YY','NLS_DATE_LANGUAGE=AMERICAN'), 'YYYYMM')) Period_YYYYMM,
-- To_Number(To_Char(To_Date(Bal.Period_Name, 'MON-YY'), 'YYYYMM')) Period_Yyyymm,
Xglcc.Segment1 Co,
xglcc.Segment2 Location,
Xglcc.Segment3 Gac,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS3_GAC'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = XGlcc.Segment3) Gac_Desc,
(SELECT Ffv.Description
FROM Fnd_Flex_Value_Sets Ffvs,
Fnd_Flex_Values_Vl Ffv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GLS1_ENT'
AND Ffv.Flex_Value_Set_Id = Ffvs.Flex_Value_Set_Id
AND Ffv.Flex_Value = XGlcc.Segment1)Co_Desc,
--nvl(Plopr.Unique_Identifier, Bsheet.Segment2) Unique_Identifier,
SUM(((Nvl(Bal.Period_Net_Dr, 0) + Nvl(Bal.Begin_Balance_Dr, 0)) -
(Nvl(Bal.Period_Net_Cr, 0) + Nvl(Bal.Begin_Balance_Cr, 0)))) Eb,
Bal.Currency_Code Book_Currency_code,
--Bsheet.Fs_Cat_2 Bsheet_Fs_Cat_2,
-- Plopr.Fs_Cat_2 Plopr_Fs_Cat_2,
--SYSDATE TIMESTAMP
to_char (SYSDATE, 'dd/mm/yyyy hh:mm:ss PM') TIMESTAMP
FROM ((SELECT Gl.Name,
Gl.Ledger_Id,
Bal.Period_Name,
Bal.Period_Year,
Bal.Currency_Code,
Bal.Period_Net_Dr,
Bal.Period_Net_Cr,
Bal.Begin_Balance_Dr,
Bal.Begin_Balance_Cr,
Code_Combination_Id
FROM Gl_Balances Bal, Gl_Ledgers Gl
WHERE Gl.Ledger_Id = Bal.Ledger_Id
AND Bal.Actual_Flag = 'A'
AND GL.Currency_Code <> 'STAT'
AND Bal.Translated_Flag IS NULL
) )BAL,
Gl_Code_Combinations_v Xglcc,
Gl_Periods Gp
WHERE 1=1
AND Xglcc.Code_Combination_Id = Bal.Code_Combination_Id
AND Gp.Period_Name = Bal.Period_Name
AND Gp.Period_Set_Name = '4-4-5'
and (CASE
WHEN Gp.Period_Name IN ( :p_period ) THEN 1
WHEN ( coalesce(NULL, :p_period) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN bal.Name IN ( :p_ledger ) THEN 1
WHEN ( coalesce(NULL, :p_ledger) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN xGlcc.Segment1 IN ( :p_company ) THEN 1
WHEN ( coalesce(NULL, :p_company) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN xGlcc.segment3 IN ( :p_gac ) THEN 1
WHEN ( coalesce(NULL, :p_gac) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN xGlcc.segment5 IN ( :p_dept ) THEN 1
WHEN ( coalesce(NULL, :p_dept) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN xGlcc.segment7 IN ( :p_ico ) THEN 1
WHEN ( coalesce(NULL, :p_ico) IS NULL ) THEN 1
END = 1 )
AND ( CASE
WHEN xGlcc.segment8 IN ( :p_proj ) THEN 1
WHEN ( coalesce(NULL, :p_proj) IS NULL ) THEN 1
END = 1 )
GROUP BY
Bal.Ledger_Id,
Bal.Name,
Bal.Period_Name,
-- To_Number(To_Char(To_Date(Bal.Period_name, 'MON-YY','NLS_DATE_LANGUAGE=AMERICAN'), 'YYYYMM')) Period_YYYYMM,
XGLCC.SEGMENT1,
xglcc.segment2,
XGLCC.SEGMENT3,
Bal.Currency_Code
HAVING SUM(((Nvl(Bal.Period_Net_Dr, 0) + Nvl(Bal.Begin_Balance_Dr, 0)) -
(Nvl(Bal.Period_Net_Cr, 0) + Nvl(Bal.Begin_Balance_Cr, 0)))) <> 0
No comments:
Post a Comment