AR Balance USD Theoretical DSO Report
SELECT Al1.Company_Code,
Al1.Corp_Code,
Al1.Corp_Name,
Al1.Customer_Code,
Al1.Customer_Name,
SUM(Al1.Remaining_Amt_Usd) Remaining_Amt_Usd,
CASE
WHEN (SUM(Al1.Dso_Theo_Amtdays) = 0)
THEN 0
ELSE
SUM(Al1.Dso_Theo_Amtdays)/SUM(Al1.Dso_Theo_Due_Amt)
END Theoretical_DSO_Days,
SUM(Al1.Dso_Theo_Amtdays) Dso_Theo_Amtdays,
SUM(Al1.Dso_Theo_Due_Amt) Dso_Theo_Due_Amt,
to_char(SYSDATE,'MM/DD/YYYY HH12:MM:SS AM') timestamp
FROM (SELECT /*+ Parallel (8) */
Pmt.Org_Id Org_Id,
Substr(Opr.Name, 1, 3) Company_Code,
Cac.Account_Number Corp_Code,
Par.Party_Name Corp_Name,
Cst.Location Customer_Code,
--Pts.Party_Site_Name Customer_Name,
Sit.attribute1 Customer_Name,
Pmt.Class Item_Type,
Pmt.Status Item_Status,
Pmt.Due_Date Due_Date,
CASE
WHEN Pmt.Class IN ('CM', 'PMT') THEN
Pmt.Due_Date + 30
ELSE
Pmt.Due_Date
END Due_Date_Aging,
CASE
WHEN (Trunc(SYSDATE) - Pmt.Due_Date) > 0 THEN
(Trunc(SYSDATE) - Pmt.Due_Date)
ELSE
0
END Days_Late,
Pmt.Amount_Due_Original Orig_Amt_Iv_Cur,
Pmt.Amount_Due_Remaining Remaining_Amt_Iv_Cur,
Pmt.Amount_Credited Credited_Amt,
CASE
WHEN Pmt.Invoice_Currency_Code = 'USD' THEN
Pmt.Amount_Due_Remaining
ELSE
Round((Pmt.Amount_Due_Remaining * Drt.Conversion_Rate), 2)
END Remaining_Amt_Usd,
CASE
WHEN Pmt.Class IN ('INV', 'DM') THEN
CASE
WHEN Pmt.Invoice_Currency_Code = 'USD' THEN
Pmt.Amount_Due_Original
ELSE
Round((Pmt.Amount_Due_Original * Drt.Conversion_Rate), 2)
END * (Pmt.Due_Date - Ctx.Trx_Date)
ELSE
0
END AS Dso_Theo_Amtdays,
CASE
WHEN Pmt.Class IN ('INV', 'DM') THEN
CASE
WHEN Pmt.Invoice_Currency_Code = 'USD' THEN
Pmt.Amount_Due_Original
ELSE
Round((Pmt.Amount_Due_Original * Drt.Conversion_Rate), 2)
END
ELSE
0
END AS Dso_Theo_Due_Amt,
rcp.cash_src,
Ctx.cst_src
FROM (select rc.Cash_Receipt_Id,
arm.name cash_src
from Ar_Cash_Receipts_All rc,
ar_receipt_methods arm
where rc.receipt_method_id = arm.receipt_method_id
) Rcp,
Ar_Payment_Schedules_All Pmt,
Hr_Operating_Units Opr,
Hz_Cust_Accounts Cac,
Hz_Parties Par,
Hz_Cust_Acct_Sites_All Sit,
Hz_Party_Sites Pts,
Hz_Locations Loc,
(select rcta.Trx_Date,
rcta.Customer_Trx_Id,
rbsa.name cst_src
from
Ra_Customer_Trx_All rcta,
ra_batch_sources_all rbsa
where
--rcta.batch_source_id = rbsa.batch_source_id -- Removed Part of Cloud Changes
rcta.batch_source_seq_id = rbsa.batch_source_seq_id -- Added Part of Cloud Changes
) Ctx,
Hz_Cust_Site_Uses_All Cst,
--Hz_Customer_Profiles Prf, -- Removed part of cloud changes
Hz_Customer_Profiles_f Prf, -- Added Part of Cloud Changes
Gl_Daily_Rates Drt,
Gl_Daily_Rates Drh,
(SELECT Eop.From_Currency, Eop.Conversion_Rate
FROM Gl_Daily_Rates Eop, Gl_Periods Prd
WHERE Eop.Conversion_Type = '1001'
AND Eop.To_Currency = 'USD'
AND Eop.Conversion_Date = Prd.Start_Date - 1
AND Prd.Period_Set_Name = '4-4-5'
AND Prd.Period_Type = 'Month'
AND Trunc(SYSDATE) BETWEEN Prd.Start_Date AND Prd.End_Date) Trl
WHERE Rcp.Cash_Receipt_Id(+) = Pmt.Cash_Receipt_Id
AND Opr.Organization_Id = Pmt.Org_Id
AND Cac.Cust_Account_Id(+) = Pmt.Customer_Id
AND Cst.Site_Use_Id(+) = Pmt.Customer_Site_Use_Id
AND Prf.Site_Use_Id(+) = Cst.Site_Use_Id
AND Par.Party_Id(+) = Cac.Party_Id
AND Ctx.Customer_Trx_Id(+) = Pmt.Customer_Trx_Id
AND Drt.Conversion_Type(+) = 'Corporate'
AND Drt.To_Currency(+) = 'USD'
AND Drt.From_Currency(+) = Pmt.Invoice_Currency_Code
AND Drt.Conversion_Date(+) = Trunc(SYSDATE) - 3
AND Drh.Conversion_Type(+) = 'Corporate'
AND Drh.To_Currency(+) = 'USD'
AND Drh.From_Currency(+) = Pmt.Invoice_Currency_Code
AND Drh.Conversion_Date(+) = Pmt.Gl_Date
AND Trl.From_Currency(+) = Pmt.Invoice_Currency_Code
AND Sit.Cust_Acct_Site_Id(+) = Cst.Cust_Acct_Site_Id
AND Pts.Party_Site_Id(+) = Sit.Party_Site_Id
AND Loc.Location_Id(+) = Pts.Location_Id
/*
AND (Substr(Opr.Name, 1, 3)) = nvl(:P_NAME,(Substr(Opr.Name, 1, 3)))
AND Cac.Account_Number = nvl(:P_ACCT_NUM,Cac.Account_Number)
AND Cst.Location = nvl(:P_LOC,Cst.Location) */
AND ( case when Substr(Opr.Name, 1, 3) in (:P_NAME) then 1
when (COALESCE(NULL,:P_NAME) is NULL ) then 1
end = 1 )
AND ( case when Cac.Account_Number in (:P_ACCT_NUM) then 1
when (COALESCE(NULL,:P_ACCT_NUM) is NULL ) then 1
end = 1 )
AND ( case when Cst.Location in (:P_LOC) then 1
when (COALESCE(NULL,:P_LOC) is NULL ) then 1
end = 1 )
AND Pmt.Amount_Due_Remaining <> 0
AND Pmt.Status = 'OP') Al1
WHERE nvl(Al1.cash_src,'x') not like 'TCF%'
and nvl(Al1.cst_src, 'x') <> 'TCF Intercompany'
GROUP BY Al1.Company_Code,
Al1.Corp_Code,
Al1.Corp_Name,
Al1.Customer_Code,
Al1.Customer_Name
ORDER BY Al1.Company_Code,
Al1.Corp_Code,
Al1.Customer_Code
No comments:
Post a Comment