GL CTA Report
SELECT Ledger_Id Ledger_Id,
NAME Ledger_Name,
Segment1 Co,
Segment2 Gac,
(SELECT Ffvv.Description
FROM Fnd_Flex_Value_Sets Ffvs, Fnd_Flex_Values_Vl Ffvv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GL_GLOBAL_ACCT'
AND Ffvs.Flex_Value_Set_Id = Ffvv.Flex_Value_Set_Id
AND Ffvv.Flex_Value = Segment2) Gac_Desc,
Currency_Code Curr_Code,
Period_Name Period,
Beginning_Balance Beg_Balance,
Ptd_Transactions Period_Amt,
Ending_Balance End_Balance,
Round(Beg_Bal, 2) Beg_Bal_Usd,
Round(Net, 2) Ptd_Usd,
Round(End_Bal, 2) End_Bal_Usd,
Round(((Beg_Bal + Net) - End_Bal), 2) Cta_Amount
FROM (SELECT a.*,
Gdr1.Conversion_Rate Eop_Rate_Prior,
(a.Beginning_Balance * Gdr1.Conversion_Rate) Beg_Bal,
Gdr2.Conversion_Rate Avg_Rate,
(a.Ptd_Transactions * Gdr2.Conversion_Rate) Net,
Gdr3.Conversion_Rate Eop_Rate_Current,
(a.Ending_Balance * Gdr3.Conversion_Rate) End_Bal
FROM (SELECT Glbal.Ledger_Id,
Glled.Name,
Glcc.Segment1,
Glcc.Segment2,
Glbal.Currency_Code,
Glbal.Period_Name,
SUM((Nvl(Glbal.Begin_Balance_Dr, 0) -
Nvl(Glbal.Begin_Balance_Cr, 0))) Beginning_Balance,
SUM((Nvl(Glbal.Period_Net_Dr, 0) -
Nvl(Glbal.Period_Net_Cr, 0))) Ptd_Transactions,
SUM((Nvl(Glbal.Period_Net_Dr, 0) -
Nvl(Glbal.Period_Net_Cr, 0) +
Nvl(Glbal.Begin_Balance_Dr, 0) -
Nvl(Glbal.Begin_Balance_Cr, 0))) Ending_Balance
FROM Gl_Balances Glbal,
Gl_Ledgers Glled,
Gl_Code_Combinations Glcc,
Gl_Periods Pd12
WHERE Glbal.Ledger_Id = Glled.Ledger_Id
AND Glbal.Code_Combination_Id = Glcc.Code_Combination_Id
AND Glbal.Ledger_Id IN
(2095, 2262, 2260, 2268, 2267, 2266, 3264, 3268)
AND Glbal.Translated_Flag IS NULL
AND Pd12.Period_Set_Name = '4-4-5'
-- AND Pd12.Period_Type = 'Month'
AND Pd12.Period_Name = Glbal.Period_Name
AND Glbal.Currency_Code <>'STAT'
AND (Glbal.Period_Name = :p_period)
GROUP BY Glbal.Ledger_Id,
Glled.Name,
Glcc.Segment1,
Glcc.Segment2,
Glbal.Currency_Code,
Glbal.Period_Name) a,
Gl_Daily_Rates Gdr1,
Gl_Periods Glp1,
Gl_Daily_Rates Gdr2,
Gl_Periods Glp2,
Gl_Daily_Rates Gdr3,
Gl_Periods Glp3
WHERE TO_CHAR(ADD_MONTHS(TO_DATE(A.Period_Name,'MON-YY'),-1),'MON-YY') = Glp1.Period_Name
AND Glp1.Period_Set_Name = '4-4-5'
AND Glp1.End_Date = Gdr1.Conversion_Date
AND Gdr1.From_Currency = a.Currency_Code
AND Gdr1.To_Currency = 'USD'
AND Gdr1.Conversion_Type = '1001'
AND a.Period_Name = Glp2.Period_Name
AND Glp2.Period_Set_Name = '4-4-5'
AND Glp2.End_Date = Gdr2.Conversion_Date
AND Gdr2.From_Currency = a.Currency_Code
AND Gdr2.To_Currency = 'USD'
AND Gdr2.Conversion_Type = 'Corporate'
AND a.Period_Name = Glp3.Period_Name
AND Glp3.Period_Set_Name = '4-4-5'
AND Glp3.End_Date = Gdr3.Conversion_Date
AND Gdr3.From_Currency = a.Currency_Code
AND Gdr3.To_Currency = 'USD'
AND Gdr3.Conversion_Type = '1001')
WHERE 1=1
and :p_flag = 'Y'
UNION
SELECT Ledger_Id Ledger_Id,
NAME Ledger_Name,
Segment1 Co,
Segment2 Gac,
(SELECT Ffvv.Description
FROM Fnd_Flex_Value_Sets Ffvs, Fnd_Flex_Values_Vl Ffvv
WHERE Ffvs.Flex_Value_Set_Name = 'XXON_GL_GLOBAL_ACCT'
AND Ffvs.Flex_Value_Set_Id = Ffvv.Flex_Value_Set_Id
AND Ffvv.Flex_Value = Segment2) Gac_Desc,
Currency_Code Curr_Code,
Period_Name Period,
Beginning_Balance Beg_Balance,
Ptd_Transactions Period_Amt,
Ending_Balance End_Balance,
Round(Beg_Bal, 2) Beg_Bal_Usd,
Round(Net, 2) Ptd_Usd,
Round(End_Bal, 2) End_Bal_Usd,
Round(((Beg_Bal + Net) - End_Bal), 2) Cta_Amount
FROM (SELECT a.*,
Gdr1.Conversion_Rate Eop_Rate_Prior,
(a.Beginning_Balance * Gdr1.Conversion_Rate) Beg_Bal,
Gdr2.Conversion_Rate Avg_Rate,
(a.Ptd_Transactions * Gdr2.Conversion_Rate) Net,
Gdr3.Conversion_Rate Eop_Rate_Current,
(a.Ending_Balance * Gdr3.Conversion_Rate) End_Bal
FROM (SELECT Glbal.Ledger_Id,
Glled.Name,
Glcc.Segment1,
Glcc.Segment2,
Glbal.Currency_Code,
Glbal.Period_Name,
SUM((Nvl(Glbal.Begin_Balance_Dr, 0) -
Nvl(Glbal.Begin_Balance_Cr, 0))) Beginning_Balance,
SUM((Nvl(Glbal.Period_Net_Dr, 0) -
Nvl(Glbal.Period_Net_Cr, 0))) Ptd_Transactions,
SUM((Nvl(Glbal.Period_Net_Dr, 0) -
Nvl(Glbal.Period_Net_Cr, 0) +
Nvl(Glbal.Begin_Balance_Dr, 0) -
Nvl(Glbal.Begin_Balance_Cr, 0))) Ending_Balance
FROM Gl_Balances Glbal,
Gl_Ledgers Glled,
Gl_Code_Combinations Glcc,
Gl_Periods Pd12
WHERE Glbal.Ledger_Id = Glled.Ledger_Id
AND Glbal.Code_Combination_Id = Glcc.Code_Combination_Id
AND Glbal.Ledger_Id IN
(2095, 2262, 2260, 2268, 2267, 2266, 3264, 3268)
AND Glbal.Translated_Flag IS NULL
AND Pd12.Period_Set_Name = '4-4-5'
-- AND Pd12.Period_Type = 'Month'
AND Pd12.Period_Name = Glbal.Period_Name
AND Glbal.Currency_Code <>'STAT'
AND (Glbal.Period_Name = :p_period)
GROUP BY Glbal.Ledger_Id,
Glled.Name,
Glcc.Segment1,
Glcc.Segment2,
Glbal.Currency_Code,
Glbal.Period_Name) a,
Gl_Daily_Rates Gdr1,
Gl_Periods Glp1,
Gl_Daily_Rates Gdr2,
Gl_Periods Glp2,
Gl_Daily_Rates Gdr3,
Gl_Periods Glp3
WHERE TO_CHAR(ADD_MONTHS(TO_DATE(A.Period_Name,'MON-YY'),-1),'MON-YY') = Glp1.Period_Name
AND Glp1.Period_Set_Name = '4-4-5'
AND Glp1.End_Date = Gdr1.Conversion_Date
AND Gdr1.From_Currency = a.Currency_Code
AND Gdr1.To_Currency = 'USD'
AND Gdr1.Conversion_Type = '1001'
AND a.Period_Name = Glp2.Period_Name
AND Glp2.Period_Set_Name = '4-4-5'
AND Glp2.End_Date = Gdr2.Conversion_Date
AND Gdr2.From_Currency = a.Currency_Code
AND Gdr2.To_Currency = 'USD'
AND Gdr2.Conversion_Type = 'Corporate'
AND a.Period_Name = Glp3.Period_Name
AND Glp3.Period_Set_Name = '4-4-5'
AND Glp3.End_Date = Gdr3.Conversion_Date
AND Gdr3.From_Currency = a.Currency_Code
AND Gdr3.To_Currency = 'USD'
AND Gdr3.Conversion_Type = '1001')
WHERE 1=1
and :p_flag <> 'Y'
AND (Beginning_Balance<> 0 OR Ending_Balance<>0)
No comments:
Post a Comment