AR Aging USD EOP Daily Rates Report
with PERIOD AS
(SELECT End_Date
FROM (SELECT 'CM_' || (Month_Num - 1) Period,
Period_Name Period_Name,
Period_Year,
Period_Num,
End_Date
FROM (
SELECT
Gph.Period_Name,
Gph.Period_Year,
Gph.Period_Num,
ROW_NUMBER() OVER (ORDER BY Gph.Start_Date DESC) AS Month_Num,
Gph.End_Date
FROM Gl_Periods Gph
JOIN (
SELECT Gp.Period_Set_Name, Gp.Start_Date
FROM Gl_Periods Gp
JOIN Gl_Ledgers Gl ON Gl.Period_Set_Name = Gp.Period_Set_Name
WHERE Gp.Period_Name = (
SELECT Pd1.Period_Name
FROM Gl_Periods Pd1
WHERE Pd1.Period_Set_Name = '4-4-5'
AND TRUNC(SYSDATE) BETWEEN Pd1.Start_Date AND Pd1.End_Date
)
) Gp ON Gph.Period_Set_Name = Gp.Period_Set_Name
AND Gph.Start_Date <= Gp.Start_Date
ORDER BY Gph.Start_Date DESC)t
WHERE Month_Num <= 2)
WHERE Period = 'CM_1'
) */
WITH period AS (
SELECT End_Date
FROM (SELECT 'CM_' || (Month_Num - 1) Period,
Period_Name Period_Name,
Period_Year,
Period_Num,
End_Date
FROM (SELECT Gph.Period_Name,
Gph.Period_Year,
Gph.Period_Num,
(Row_Number() Over(ORDER BY Gph.Start_Date DESC)) Month_Num,
Gph.End_Date
FROM (SELECT Gp.Period_Set_Name,
Gp.Period_Name,
Gp.Period_Year,
Gp.Start_Date,
Gp.End_Date,
Gl.Name Ledger_Name,
Gl.Ledger_Id
FROM Gl_Periods Gp, Gl_Ledgers Gl
WHERE Gl.Period_Set_Name = Gp.Period_Set_Name
-- AND Gl.Name = 'USD CL'
) Gp,
Gl_Periods Gph
WHERE Gp.Period_Set_Name = Gph.Period_Set_Name
AND Gph.Start_Date <= Gp.Start_Date
START WITH Gp.Period_Name =
(SELECT Pd1.Period_Name
FROM Gl_Periods Pd1
WHERE Pd1.Period_Set_Name = '4-4-5'
AND Trunc(SYSDATE) BETWEEN Pd1.Start_Date AND
Pd1.End_Date)
CONNECT BY NOCYCLE PRIOR Gph.End_Date = Gp.Start_Date
ORDER BY Gph.Start_Date DESC) t
WHERE Month_Num <= 2)
WHERE Period = 'CM_1')
SELECT Al1.Region,
Al1.Company_Code Co,
Al1.Corp_Code Corp_Cd,
Al1.Corp_Name,
Al1.Customer_Code Customer_Cd,
Al1.Customer_Name,
AL1.Site_Use_Id,--trx_number,
SUM(CASE
WHEN (case when :P_RATE = 'DAILY' then sysdate else period.end_date end - Al1.Due_Date_Aging) <= 0 THEN
Al1.Remain_Amt_Usd_Eop
ELSE
nvl(Conversion_Rate,1)
END) Current_Usd,
SUM(CASE
WHEN (case when :P_RATE = 'DAILY' then sysdate else period.end_date end - Al1.Due_Date_Aging) BETWEEN 1 AND 15 THEN
Al1.Remain_Amt_Usd_Eop
ELSE
0
END) Delinq_1_15_Usd,
SUM(CASE
WHEN (case when :P_RATE = 'DAILY' then sysdate else period.end_date end - Al1.Due_Date_Aging) BETWEEN 16 AND 30 THEN
Al1.Remain_Amt_Usd_Eop
ELSE
0
END) Delinq_16_30_Usd,
SUM(CASE
WHEN (case when :P_RATE = 'DAILY' then sysdate else period.end_date end - Al1.Due_Date_Aging) BETWEEN 31 AND 60 THEN
Al1.Remain_Amt_Usd_Eop
ELSE
0
END) DELINQ_31_60_USD,
SUM(CASE
WHEN (case when :P_RATE = 'DAILY' then sysdate else period.end_date end - Al1.Due_Date_Aging) BETWEEN 61 AND 120 THEN
Al1.Remain_Amt_Usd_Eop
ELSE
0
END) Delinq_61_120_Usd,
SUM(CASE
WHEN (case when :P_RATE = 'DAILY' then sysdate else period.end_date end - Al1.Due_Date_Aging) > 120 THEN
Al1.Remain_Amt_Usd_Eop
ELSE
0
END) Delinq_Over_120,
SUM(Al1.Remain_Amt_Usd_Eop) TOTAL_DUE_USD,
SUM(CASE
WHEN (case when :P_RATE = 'DAILY' then sysdate else period.end_date end - Al1.Due_Date_Aging) > 0 THEN
Al1.Remain_Amt_Usd_Eop
ELSE
0
END) Delinquent_Usd,
SUM(CASE
WHEN (case when :P_RATE = 'DAILY' then sysdate else period.end_date end - Al1.Due_Date_Aging) BETWEEN -5 AND 0 THEN
Al1.Remain_Amt_Usd_Eop
ELSE
0
END) Due_Within_5_Days_Usd,
SUM(Remaining_Amt_Usd) Remaining_Amt_Usd,
DECODE(SUM(Al1.Remain_Amt_Usd_Eop),0,0,((SUM(CASE
WHEN (case when :P_RATE = 'DAILY' then sysdate else period.end_date end - Al1.Due_Date_Aging) > 0 THEN
Al1.Remain_Amt_Usd_Eop
ELSE
0
END))/SUM(Al1.Remain_Amt_Usd_Eop))) * 100 DELINQUENT,
CASE WHEN :P_DISPLAY_ITEM_TYPE = 'Yes' THEN Al1.Item_Type else null end Item_Type,
Al1.Country_Code,
SITE_USE_CODE,
Al1.Collector_Name,
to_char(SYSDATE,'MM/DD/YYYY HH12:MM:SS AM') timestamp
FROM
(
SELECT
--PMT.TRx_NUMBER,
Pmt.Org_Id Org_Id,
Substr(Opr.Name, 1, 3) Company_Code,
/*Decode(Pmt.Org_Id,
89,
'AMR',
85,
'AMR',
116,
'EUR',
165,
'EUR',
167,
'EUR',
119,
'AP',
130,
'AP',
113,
'AP',
134,
'AP',
123,
'JPN',
To_Char(Pmt.Org_Id)) Region,*/
NULL Region,
Cac.Account_Number Corp_Code,
Par.Party_Name Corp_Name,
Cst.Location Customer_Code,
sit.attribute1 Customer_Name,
Pmt.Class Item_Type,
Pmt.Status Item_Status,
Pmt.Due_Date Due_Date,
cst.Site_Use_Id Site_Use_Id,
cst.SITE_USE_CODE SITE_USE_CODE,
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,
trl.Conversion_Rate Conversion_Rate,
Loc.Country Country_Code,
Col.Name Collector_Name,
CASE
WHEN Pmt.Invoice_Currency_Code = 'USD' THEN
Pmt.Amount_Due_Original
ELSE
Round((Pmt.Amount_Due_Original * Drt.Conversion_Rate), 2)
END Orig_Amt_Usd,
CASE
WHEN Pmt.Invoice_Currency_Code = 'USD' THEN
Pmt.Amount_Due_Remaining
ELSE
CASE WHEN :P_RATE = 'DAILY' THEN
Round((Pmt.Amount_Due_Remaining * drt.Conversion_Rate), 2)
ELSE
Round((Pmt.Amount_Due_Remaining * Trl.Conversion_Rate), 2)
END
END Remaining_Amt_Usd,
CASE
WHEN Pmt.Invoice_Currency_Code = 'USD' THEN
Pmt.Amount_Due_Original
ELSE
Round((Pmt.Amount_Due_Original * Trl.Conversion_Rate), 2)
END Orig_Amt_Usd_Eop,
CASE
WHEN Pmt.Invoice_Currency_Code = 'USD' THEN
Pmt.Amount_Due_Remaining
ELSE
CASE WHEN :P_RATE = 'DAILY' THEN
Round((Pmt.AMOUNT_DUE_ORIGINAL * drt.Conversion_Rate), 2)
else
Round((Pmt.AMOUNT_DUE_ORIGINAL * Trl.Conversion_Rate), 2)
--else Round((Pmt.AMOUNT_DUE_ORIGINAL * drt.Conversion_Rate), 2)
END
END Remain_Amt_Usd_Eop
from (select Cash_Receipt_Id
from Ar_Cash_Receipts_All rc,
ar_receipt_methods arm
where rc.receipt_method_id = arm.receipt_method_id
and arm.name not like 'TCF%')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,
Hz_Cust_Site_Uses_All Cst,
Hz_Customer_Profiles_f Prf,
Gl_Daily_Rates Drt,
Gl_Daily_Rates Drh,
Ar_Collectors Col
,
(SELECT Eop.From_Currency, Eop.Conversion_Rate
FROM Gl_Daily_Rates Eop, Gl_Periods Prd
WHERE 1=1
-- and 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 ,
Ra_Customer_Trx_Lines_All RCTX,
period PERIOD_IN
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 Par.Party_Id(+) = Cac.Party_Id
AND UPPER(CST.SITE_USE_CODE)='BILL_TO'
--AND CST.LOCATION IN ('ARAXX')
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 Col.Collector_Id(+) = Prf.Collector_Id
AND Drt.Conversion_Type(+) = 'Corporate'
AND Drt.To_Currency(+) = 'USD'
AND Drt.From_Currency(+) = Pmt.Invoice_Currency_Code
AND Drt.Conversion_Date(+) = PERIOD_IN.end_date
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 Col.Collector_Id(+) = Prf.Collector_Id
AND RCTX.CUSTOMER_TRX_ID = PMT.CUSTOMER_TRX_ID
AND RCTX.line_number=1
--and col.name <> 'Default Collector'
AND COL.COLLECTOR_ID <> 1
AND EXISTS
(SELECT 1
FROM Ra_Customer_Trx_Lines_All Ctxl
WHERE ctxl.line_number = 1
AND Ctxl.Inventory_Item_Id IN
(SELECT distinct Msib.Inventory_Item_Id
--FROM Mtl_System_Items_b Msib
FROM egp_system_items_b msib
WHERE 1=1
-- AND Msib.Segment1 = NVL(:P_PART_NUMBER,Msib.Segment1))
--AND msib.item_number IN nvl(:P_PART_NUMBER,msib.item_number)
AND ( case when MSIB.item_number in (:P_PART_NUMBER) then 1 when (COALESCE(NULL,:P_PART_NUMBER) is NULL ) then 1
end = 1 )
)
AND Ctxl.Customer_Trx_Id = Pmt.Customer_Trx_Id)
AND ( case when Substr(Opr.Name, 1, 3) in (:P_CO) then 1
when (COALESCE(NULL,:P_CO) is NULL ) then 1
end = 1 )
--AND Substr(Opr.Name, 1, 3) = NVL(:P_CO,Substr(Opr.Name, 1, 3))
AND Pmt.Gl_Date <= case when :P_RATE = 'DAILY' then Pmt.Gl_Date else period_in.end_date end
AND Pmt.Amount_Due_Remaining <> 0
AND Pmt.Status = 'OP'
)AL1,
PERIOD PERIOD
WHERE 1=1
--AND Al1.Region = NVL(:P_REGION,Al1.Region)
GROUP BY Al1.Company_Code,
Al1.Region,
Al1.Corp_Name,
Al1.Customer_Code,
Al1.Customer_Name,
Al1.Corp_Code,
Al1.Country_Code,
Al1.Collector_Name,
Al1.Item_Type,
--Org_Id
SITE_USE_CODE,Site_Use_Id--,trx_number
ORDER BY Al1.Region,
Al1.Company_Code,
Al1.Corp_Name,
Al1.Customer_Code--,trx_number
No comments:
Post a Comment