AP Recurring PO DUP Invoices Report
WITH Gps AS
(SELECT /*+ materialize */
Gps.Period_Name,
Gps.Start_Date,
Gps.End_Date
FROM Gl_Periods Gps
WHERE Gps.Period_Set_Name = '4-4-5'
AND (Gps.Period_Name = nvl(:p_Period ,Gps.Period_Name))
),
hrou AS
(SELECT /*+ materialize */
hrou.NAME,
hrou.organization_id
FROM Hr_Operating_Units hrou
where 1=1
AND ((Substr(Hrou.Name, 1, 3)) = nvl(:p_Co,(Substr(Hrou.Name, 1, 3))))
),
Mn AS
( SELECT /*+ materialize */
Gps.Period_Name Period,
Substr(Hrou.Name, 1, 3) Co,
Asu.Vendor_Name Vendor_Name,
Asu.Segment1 Vendor_Num,
Asa.Vendor_Site_Code Vendor_Site,
Po.Po_Num,
Po.Line_Num Po_Line_Num,
Po.Promised_Date,
Aia.Invoice_Num Invoice_Num,
To_Char(Aia.Invoice_Date, 'MM/DD/YYYY') Inv_Date,
Aia.Invoice_Date,
Aia.Creation_Date Inv_Creation_Date,
Aia.Source Inv_Source,
Aia.Description Inv_Desc,
To_Char(Pay.Due_Date, 'MM/DD/YYYY') Due_Date,
Aia.Invoice_Currency_Code Inv_Cy_Cd,
Aia.Invoice_Amount Inv_Amt,
Aia.Total_Tax_Amount Tax_Amt,
CASE
WHEN Aia.Invoice_Currency_Code = 'USD' THEN
Aia.Invoice_Amount
ELSE
Round((Aia.Invoice_Amount * Gdr.Conversion_Rate), 2)
END Invoice_Amt_Usd,
Paydt.Payment_Date,
Nvl(Aia.Amount_Paid, 0) Amt_Paid,
CASE
WHEN Aia.Invoice_Currency_Code = 'USD' THEN
Nvl(Aia.Amount_Paid, 0)
ELSE
Round((Nvl(Aia.Amount_Paid, 0) * Gdr.Conversion_Rate), 2)
END Amt_Paid_Usd,
CASE
WHEN Aia.Invoice_Currency_Code = 'USD' THEN
(Nvl(Aia.Invoice_Amount, 0) - Nvl(Aia.Amount_Paid, 0))
ELSE
Round(((Nvl(Aia.Invoice_Amount, 0) - Nvl(Aia.Amount_Paid, 0)) *
Gdr.Conversion_Rate),
2)
END Remaining_Amt_Usd,
Aia.Invoice_Id,
Po.Po_Line_Id
FROM Gps,
Ap_Invoices_All Aia,
POZ_SUPPLIERS_V Asu, ---replaced by Ap_Suppliers
POZ_SUPPLIER_SITES_ALL_M Asa, ----replaced by Ap_Supplier_Sites_Al
Gl_Daily_Rates Gdr,
Hrou,
(SELECT MIN(Due_Date) Due_Date,
Invoice_Id,
SUM(Nvl(Amount_Remaining, 0)) Amount_Remaining
FROM Ap_Payment_Schedules_All
GROUP BY Invoice_Id) Pay,
(SELECT a.Invoice_Id,
Apc.Check_Date Payment_Date
FROM Ap_Checks_All Apc,
(SELECT Aipa.Invoice_Id Invoice_Id,
MAX(Aipa.Check_Id) Check_Id
FROM Ap_Invoice_Payments_All Aipa
WHERE Nvl(Aipa.Reversal_Flag, 'N') = 'N'
GROUP BY Aipa.Invoice_Id) a
WHERE Apc.Check_Id = a.Check_Id ) Paydt,
(SELECT Poh.Segment1 Po_Num,
Pll.Promised_Date,
Pol.Line_Num Line_Num,
Pol.Po_Line_Id,
Aid.Invoice_Id
FROM Po_Headers_All Poh,
Po_Lines_All Pol,
Po_Distributions_All Pda,
Po_Line_Locations_All Pll,
Po_Line_Types_Tl Plt,
Ap_Invoice_Distributions_All Aid
WHERE Poh.Po_Header_Id = Pda.Po_Header_Id
AND Poh.po_Header_id = Pol.Po_Header_Id
AND Pol.Po_Line_Id = Pda.Po_Line_Id
AND Pll.Line_Location_Id = Pda.Line_Location_Id
AND poh.po_header_id = pll.po_header_id
AND pol.po_line_id = pll.po_line_id
AND Pol.Line_Type_Id = Plt.Line_Type_Id(+)
AND Plt.Language(+) = 'US'
AND Plt.Line_Type (+)= 'Recurring Service'
AND Aid.Po_Distribution_Id = Pda.Po_Distribution_Id
AND (Poh.Segment1 = nvl(:p_PO_Num,Poh.Segment1))
GROUP BY Poh.Segment1,
Pll.Promised_Date,
Pol.Line_Num,
Pol.Po_Line_Id,
Aid.Invoice_Id)po
where 1=1
and Hrou.Organization_Id = Aia.Org_Id
AND Gdr.From_Currency(+) = Aia.Invoice_Currency_Code
AND Gdr.To_Currency(+) = 'USD'
AND Gdr.Conversion_Type(+) = 'Corporate'
AND Gdr.Conversion_Date(+) = Aia.Gl_Date
AND Aia.Gl_Date BETWEEN Gps.Start_Date AND Gps.End_Date
AND Po.Invoice_Id = Aia.Invoice_Id
AND Paydt.Invoice_Id(+) = Aia.Invoice_Id
AND Pay.Invoice_Id(+) = Aia.Invoice_Id
AND Aia.VENDOR_ID = Asu.VENDOR_ID
AND Asa.VENDOR_SITE_ID = Aia.VENDOR_SITE_ID
AND Asu.Vendor_Id = Asa.Vendor_Id
AND (Asu.Vendor_Name = nvl(:p_Vendor_Name,Asu.Vendor_Name))
AND (Asu.Segment1 = nvl(:p_Vendor_Num,Asu.Segment1))
GROUP BY Gps.Period_Name,
Substr(Hrou.Name, 1, 3),
Asu.Vendor_Name,
Asu.Segment1,
Asa.Vendor_Site_Code,
Po.Po_Num,
Po.Line_Num,
Po.Promised_Date,
Aia.Invoice_Num,
To_Char(Aia.Invoice_Date, 'MM/DD/YYYY'),
Aia.Invoice_Date,
Aia.Creation_Date,
Aia.Source,
Aia.Description,
To_Char(Pay.Due_Date, 'MM/DD/YYYY'),
Aia.Invoice_Currency_Code,
Aia.Invoice_Amount,
Aia.Total_Tax_Amount,
CASE
WHEN Aia.Invoice_Currency_Code = 'USD' THEN
Aia.Invoice_Amount
ELSE
Round((Aia.Invoice_Amount * Gdr.Conversion_Rate), 2)
END,
Paydt.Payment_Date,
Nvl(Aia.Amount_Paid, 0),
CASE
WHEN Aia.Invoice_Currency_Code = 'USD' THEN
Nvl(Aia.Amount_Paid, 0)
ELSE
Round((Nvl(Aia.Amount_Paid, 0) * Gdr.Conversion_Rate), 2)
END,
CASE
WHEN Aia.Invoice_Currency_Code = 'USD' THEN
(Nvl(Aia.Invoice_Amount, 0) - Nvl(Aia.Amount_Paid, 0))
ELSE
Round(((Nvl(Aia.Invoice_Amount, 0) - Nvl(Aia.Amount_Paid, 0)) *
Gdr.Conversion_Rate),
2)
END,
Aia.Invoice_Id,
Po.Po_Line_Id )
SELECT Dp.Period,
Dp.Co,
Dp.Vendor_Name,
Dp.Vendor_Num,
Dp.Vendor_Site,
Dp.Po_Num,
Dp.Po_Line_Num,
Dp.Promised_Date,
Dp.Dup_SNo_Per_Set,
Dp.Invoice_Num,
Dp.Inv_Date,
Dp.Inv_Creation_Date,
Dp.Inv_Source,
Dp.Inv_Desc,
Dp.Due_Date,
Dp.Inv_Cy_Cd,
Dp.Inv_Amt,
Dp.Tax_Amt,
Dp.Invoice_Amt_Usd,
Dp.Payment_Date,
Dp.Amt_Paid,
Dp.Amt_Paid_Usd,
Dp.Remaining_Amt_Usd
FROM (SELECT * FROM Mn )--WHERE Mn.Invoice_Num LIKE 'SB%') - as of now commented where clause need to check respective invoice num in cloud
Match_Recognize(PARTITION BY Po_Line_Id
ORDER BY Invoice_Date,
Inv_Amt
Measures
COUNT(*) as dup_sno_per_set
ALL ROWS PER MATCH
Pattern(strt duplicate+)
Define
duplicate as inv_amt = prev(inv_amt)
AND invoice_date = prev(invoice_date)
) Dp
ORDER BY Dp.Po_Num,
Dp.Po_Line_Num,
Dp.Invoice_Date,
Dp.Inv_Amt,
Dp.Dup_SNo_Per_Set
No comments:
Post a Comment