AP Paid Capital Invoices Report
select * from(
SELECT l.Period,
l.Co,
l.Vendor_Name,
l.Invoice_Number,
l.Applied_To_Invoice,
l.Invoice_Type,
l.Invoice_Description,
l.Invoice_Date,
l.Payment_Date,
l.Gl_Date,
l.Inv_Cy_Cd,
l.Paid_Amount,
CASE WHEN l.ct = 0 THEN l.total_tax_amount
ELSE l.total_tax_amount/ct
END tax_amt,
(l.Paid_Amount - ( CASE WHEN l.ct = 0 THEN l.total_tax_amount
ELSE l.total_tax_amount/l.ct
END)) Paid_Amt_Excl_Tax,
l.Paid_Amt_Usd,
(l.Paid_Amt_Usd - ( CASE WHEN l.ct = 0 THEN l.total_tax_amount_usd
ELSE l.total_tax_amount_usd/l.ct
END)) Paid_Amt_Usd_Excl_Tax,
l.Inv_Dist_Amount,
l.Inv_Dist_Amt_Usd,
l.Po_Num,
l.Dept,
l.Proj,
sysdate timestamp
FROM (
SELECT Al1.Period_Name Period,
Al1.Company_Code Co,
Al1.Vendor_Name,
Al1.Invoice_Number,
CASE
WHEN Al1.Invoice_Type = 'PREPAYMENT' THEN
Al2.Invoice_Number
ELSE
NULL
END Applied_To_Invoice,
Al1.Invoice_Type,
Al2.Invoice_Description,
Al1.Invoice_Date,
Al1.Payment_Date,
Al1.Gl_Date,
Al1.Inv_Curr_Cd Inv_Cy_Cd,
Al1.Paid_Amount,
Al1.Paid_Amt_Usd,
Al2.Inv_Dist_Amount,
Al2.Inv_Dist_Amt_Usd,
Al2.Po_Number Po_Num,
Al2.Department Dept,
Al2.Project Proj,
COUNT(*) OVER (PARTITION BY Al1.Invoice_Id) ct,
Al1.total_tax_amount,
Al1.total_tax_amount_usd
FROM (SELECT Pmt.Org_Id Org_Id,
Substr(Opr.Name, 1, 3) Company_Code,
Pmt.Period_Name Period_Name,
Apc.Check_Date Payment_Date,
Inv.gl_date,
Pmt.Invoice_Id Invoice_Id,
Inv.Invoice_Num Invoice_Number,
Inv.Invoice_Date Invoice_Date,
Inv.Invoice_Type_Lookup_Code Invoice_Type,
Inv.Vendor_Id Vendor_Id,
hp.party_Name Vendor_Name,
Vnd.Segment1 Vendor_Number,
Inv.Invoice_Currency_Code Inv_Curr_Cd,
SUM(Pmt.Amount) Paid_Amount,
SUM(Pmt.Payment_Base_Amount) Paid_Amt_Funct,
SUM(CASE
WHEN Inv.Invoice_Currency_Code = 'USD' THEN
Pmt.Amount
WHEN ((Inv.Invoice_Currency_Code <> 'USD') AND
(Lgr.Currency_Code = 'USD')) THEN
Pmt.Payment_Base_Amount
ELSE
Round((Pmt.Amount * Drt.Conversion_Rate), 2)
END) Paid_Amt_Usd,
Inv.total_tax_amount total_tax_amount,
CASE
WHEN Inv.Invoice_Currency_Code = 'USD' THEN
Inv.total_tax_amount
ELSE
Round((Inv.total_tax_amount * Drt.Conversion_Rate), 2)
END total_tax_amount_Usd
FROM Ap_Invoice_Payments_All Pmt,
Ap_Checks_All Apc,
Ap_Invoices_All Inv,
--Po_Vendors Vnd,
poz_suppliers vnd,
--Po_Vendor_Sites_All Vst,
poz_supplier_sites_all_m Vst,
Hr_Operating_Units Opr,
Gl_Daily_Rates Drt,
Gl_Ledgers Lgr,
hz_parties hp
WHERE Inv.Invoice_Id = Pmt.Invoice_Id
AND Apc.Check_Id = Pmt.Check_Id
AND Vnd.Vendor_Id = Inv.Vendor_Id
AND Opr.Organization_Id = Pmt.Org_Id
AND Drt.Conversion_Date(+) = Inv.Gl_Date
AND Drt.To_Currency(+) = 'USD'
AND Drt.Conversion_Type(+) = 'Corporate'
AND Drt.From_Currency(+) = Inv.Invoice_Currency_Code
AND Vst.Vendor_Site_Id = Inv.Vendor_Site_Id
AND Lgr.Ledger_Id = Pmt.Set_Of_Books_Id
AND Pmt.Amount <> 0
AND hp.party_id = vnd.party_id
GROUP BY Pmt.Org_Id,
Substr(Opr.Name, 1, 3),
Pmt.Period_Name,
Apc.Check_Date,
Inv.gl_date,
Pmt.Invoice_Id,
Inv.Invoice_Num,
Inv.Invoice_Date,
Inv.Invoice_Type_Lookup_Code,
Inv.Vendor_Id,
hp.party_Name,
Vnd.Segment1,
Inv.Invoice_Currency_Code,
Inv.total_tax_amount,
CASE
WHEN Inv.Invoice_Currency_Code = 'USD' THEN
Inv.total_tax_amount
ELSE
Round((Inv.total_tax_amount * Drt.Conversion_Rate), 2)
END) Al1,
(SELECT Cap.Org_Id Org_Id,
Cap.Company Company_Code,
Cap.Invoice_Id Invoice_Id,
Cap.Invoice_Number Invoice_Number,
Cap.Invoice_Date Invoice_Date,
Cap.Invoice_Description Invoice_Description,
--Cap.Vendor_Name Vendor_Name,
Cap.Vendor_Name Vendor_Name,
Cap.Vendor_Number Vendor_Number,
Cap.Po_Number Po_Number,
Cap.Account_Combination Account_Combination,
Cap.Department Department,
Cap.Project Project,
Cap.Inv_Curr Inv_Curr_Cd,
SUM(Cap.Inv_Dist_Amount) Inv_Dist_Amount,
SUM(Cap.Inv_Dist_Amt_Funct) Inv_Dist_Amt_Funct,
SUM(Cap.Inv_Dist_Amt_Usd) Inv_Dist_Amt_Usd
FROM (SELECT Dst.Org_Id Org_Id,
Glc.Segment1 Company,
Dst.Invoice_Id Invoice_Id,
Inv.Invoice_Num Invoice_Number,
Inv.Invoice_Date Invoice_Date,
Inv.Description Invoice_Description,
--Sup.Vendor_Name Vendor_Name,
hp.party_name Vendor_Name,
Sup.Segment1 Vendor_Number,
Cmb.Po_Number Po_Number,
Cmb.Account_Combination Account_Combination,
Substr(Cmb.Account_Combination, 7, 4) Department,
Substr(Cmb.Account_Combination, 12, 5) Project,
Inv.Invoice_Currency_Code Inv_Curr,
SUM(Dst.Amount) Inv_Dist_Amount,
SUM(Nvl(Dst.Base_Amount, Dst.Amount)) Inv_Dist_Amt_Funct,
SUM(CASE
WHEN Inv.Invoice_Currency_Code = 'USD' THEN
Dst.Amount
WHEN ((Inv.Invoice_Currency_Code <> 'USD') AND
(Lgr.Currency_Code = 'USD')) THEN
Dst.Base_Amount
ELSE
Round((Dst.Amount * Drt.Conversion_Rate), 2)
END) Inv_Dist_Amt_Usd
FROM Ap_Invoice_Distributions_All Dst,
Ap_Invoices_All Inv,
Gl_Code_Combinations Glc,
Gl_Code_Combinations Cde,
Po_Distributions_All Pdt,
Gl_Daily_Rates Drt,
Gl_Ledgers Lgr,
--Ap_Suppliers Sup,
poz_suppliers Sup,
hz_parties hp,
(SELECT Ids.Invoice_Id Invoice_Id,
MIN(Phd.Segment1) Po_Number,
MIN(Cdc.Segment2 || '.' || Cdc.Segment4 || '.' ||
Cdc.Segment6) Account_Combination
FROM Ap_Invoice_Distributions_All Ids,
Po_Distributions_All Pod,
Gl_Code_Combinations Cdc,
Po_Headers_All Phd
WHERE Cdc.Code_Combination_Id =
Pod.Code_Combination_Id
AND Pod.Po_Distribution_Id = Ids.Po_Distribution_Id
AND Phd.Po_Header_Id = Pod.Po_Header_Id
AND Cdc.Segment2 IN ('12145', '12155')
GROUP BY Ids.Invoice_Id) Cmb
WHERE Dst.Amount <> 0
AND Inv.Invoice_Id = Dst.Invoice_Id
AND Glc.Code_Combination_Id = Dst.Dist_Code_Combination_Id
AND Pdt.Po_Distribution_Id(+) = Dst.Po_Distribution_Id
AND Cde.Code_Combination_Id(+) = Pdt.Code_Combination_Id
AND Nvl(Cde.Segment2, Glc.Segment2) IN ('12145', '12155')
AND Drt.Conversion_Date(+) = Inv.Gl_Date
AND Drt.To_Currency(+) = 'USD'
AND Drt.From_Currency(+) = Inv.Invoice_Currency_Code
AND Drt.Conversion_Type(+) = 'Corporate'
AND Lgr.Ledger_Id = Dst.Set_Of_Books_Id
AND Sup.Vendor_Id = Inv.Vendor_Id
AND Cmb.Invoice_Id(+) = Dst.Invoice_Id
AND hp.party_id = sup.party_id
GROUP BY Dst.Org_Id,
Glc.Segment1,
Dst.Invoice_Id,
Inv.Invoice_Num,
Inv.Invoice_Date,
Inv.Description,
--Sup.Vendor_Name,
hp.party_name,
Sup.Segment1,
Cmb.Po_Number,
Cmb.Account_Combination,
Inv.Invoice_Currency_Code
UNION ALL /*reverse sign of applied prepayments*/
SELECT Dst.Org_Id Org_Id,
Glc.Segment1 Company,
Ppy.Invoice_Id Invoice_Id,
Inv.Invoice_Num Invoice_Number,
Inv.Invoice_Date Invoice_Date,
Inv.Description Invoice_Description,
--Sup.Vendor_Name Vendor_Name,
hp.party_name Vendor_Name,
Sup.Segment1 Vendor_Number,
Cmb.Po_Number Po_Number,
Cmb.Account_Combination Account_Combination,
Substr(Cmb.Account_Combination, 7, 4) Department,
Substr(Cmb.Account_Combination, 12, 5) Project,
Inv.Invoice_Currency_Code Inv_Curr,
MIN(Ppy.Inv_Dist_Amount) Inv_Dist_Amount,
MIN(Ppy.Inv_Dist_Amt_Funct) Inv_Dist_Amt_Funct,
MIN(Ppy.Inv_Dist_Amt_Usd) Inv_Dist_Amt_Usd
FROM Ap_Invoice_Distributions_All Dst,
Ap_Invoices_All Inv,
Gl_Code_Combinations Glc,
Gl_Code_Combinations Cde,
Po_Distributions_All Pdt,
--Ap_Suppliers Sup,
poz_suppliers sup,
hz_parties hp,
(SELECT Ids.Invoice_Id Invoice_Id,
MIN(Phd.Segment1) Po_Number,
MIN(Cdc.Segment2 || '.' || Cdc.Segment4 || '.' ||
Cdc.Segment6) Account_Combination
FROM Ap_Invoice_Distributions_All Ids,
Po_Distributions_All Pod,
Gl_Code_Combinations Cdc,
Po_Headers_All Phd
WHERE Cdc.Code_Combination_Id =
Pod.Code_Combination_Id
AND Pod.Po_Distribution_Id = Ids.Po_Distribution_Id
AND Phd.Po_Header_Id = Pod.Po_Header_Id
AND Cdc.Segment2 IN ('12145', '12155')
GROUP BY Ids.Invoice_Id) Cmb,
(SELECT Prp.Invoice_Id,
Prp.Prepay_Invoice_Id,
SUM(Ivd.Amount) Inv_Dist_Amount,
SUM(Nvl(Ivd.Base_Amount, Ivd.Amount)) Inv_Dist_Amt_Funct,
SUM(CASE
WHEN Iva.Invoice_Currency_Code = 'USD' THEN
Ivd.Amount
WHEN ((Iva.Invoice_Currency_Code <> 'USD') AND
(Lgr.Currency_Code = 'USD')) THEN
Ivd.Base_Amount
ELSE
Round((Ivd.Amount * Drt.Conversion_Rate), 2)
END) Inv_Dist_Amt_Usd
FROM Ap_Prepay_History_All Prp,
Ap_Invoices_All Iva,
Ap_Invoice_Distributions_All Ivd,
Gl_Daily_Rates Drt,
Gl_Ledgers Lgr
WHERE Prp.Transaction_Type = 'PREPAYMENT APPLIED'
AND Iva.Invoice_Id = Prp.Invoice_Id
AND Ivd.Invoice_Id = Prp.Invoice_Id
AND Ivd.Line_Type_Lookup_Code = 'PREPAY'
AND Drt.Conversion_Date(+) = Iva.Gl_Date
AND Drt.To_Currency(+) = 'USD'
AND Drt.From_Currency(+) =
Iva.Invoice_Currency_Code
AND Drt.Conversion_Type(+) = 'Corporate'
AND Lgr.Ledger_Id = Iva.Set_Of_Books_Id
AND Prp.Prepay_History_Id =
(SELECT MIN(Prepay_History_Id)
FROM Ap_Prepay_History_All
WHERE Transaction_Type = 'PREPAYMENT APPLIED'
AND Invoice_Id = Prp.Invoice_Id)
GROUP BY Prp.Invoice_Id, Prp.Prepay_Invoice_Id) Ppy
WHERE Dst.Amount <> 0
AND Inv.Invoice_Id = Dst.Invoice_Id
AND Glc.Code_Combination_Id = Dst.Dist_Code_Combination_Id
AND Pdt.Po_Distribution_Id = Dst.Po_Distribution_Id
AND Cde.Code_Combination_Id = Pdt.Code_Combination_Id
AND Nvl(Cde.Segment2, Glc.Segment2) IN ('12145', '12155')
AND Sup.Vendor_Id = Inv.Vendor_Id
AND Cmb.Invoice_Id = Dst.Invoice_Id
AND Ppy.Invoice_Id = Dst.Invoice_Id
AND sup.party_id = hp.party_id
GROUP BY Dst.Org_Id,
Glc.Segment1,
Ppy.Invoice_Id,
Inv.Invoice_Num,
Inv.Invoice_Date,
Inv.Description,
--Sup.Vendor_Name,
hp.party_name,
Sup.Segment1,
Cmb.Po_Number,
Cmb.Account_Combination,
Inv.Invoice_Currency_Code
UNION ALL /*original prepayment*/
SELECT Dst.Org_Id Org_Id,
Glc.Segment1 Company,
Ppy.Prepay_Invoice_Id Invoice_Id,
Inv.Invoice_Num Invoice_Number,
Inv.Invoice_Date Invoice_Date,
Inv.Description Invoice_Description,
--Sup.Vendor_Name Vendor_Name,
hp.party_name party_name,
Sup.Segment1 Vendor_Number,
Cmb.Po_Number Po_Number,
Cmb.Account_Combination Account_Combination,
Substr(Cmb.Account_Combination, 7, 4) Department,
Substr(Cmb.Account_Combination, 12, 5) Project,
Inv.Invoice_Currency_Code Inv_Curr,
MIN(Ppy.Inv_Dist_Amount) Inv_Dist_Amount,
MIN(Ppy.Inv_Dist_Amt_Funct) Inv_Dist_Amt_Funct,
MIN(Ppy.Inv_Dist_Amt_Usd) Inv_Dist_Amt_Usd
FROM Ap_Invoice_Distributions_All Dst,
Ap_Invoices_All Inv,
Gl_Code_Combinations Glc,
Gl_Code_Combinations Cde,
Po_Distributions_All Pdt,
--Ap_Suppliers Sup,
poz_suppliers sup,
hz_parties hp,
(SELECT Ids.Invoice_Id Invoice_Id,
MIN(Phd.Segment1) Po_Number,
MIN(Cdc.Segment2 || '.' || Cdc.Segment4 || '.' ||
Cdc.Segment6) Account_Combination
FROM Ap_Invoice_Distributions_All Ids,
Po_Distributions_All Pod,
Gl_Code_Combinations Cdc,
Po_Headers_All Phd
WHERE Cdc.Code_Combination_Id =
Pod.Code_Combination_Id
AND Pod.Po_Distribution_Id = Ids.Po_Distribution_Id
AND Phd.Po_Header_Id = Pod.Po_Header_Id
AND Cdc.Segment2 IN ('12145', '12155')
GROUP BY Ids.Invoice_Id) Cmb,
(SELECT Prp.Invoice_Id,
Prp.Prepay_Invoice_Id,
MAX(Iva.Invoice_Amount) Inv_Dist_Amount,
MAX(Nvl(Iva.Base_Amount, Iva.Invoice_Amount)) Inv_Dist_Amt_Funct,
MAX(CASE
WHEN Iva.Invoice_Currency_Code = 'USD' THEN
Iva.Invoice_Amount
WHEN ((Iva.Invoice_Currency_Code <> 'USD') AND
(Lgr.Currency_Code = 'USD')) THEN
Iva.Base_Amount
ELSE
Round((Iva.Invoice_Amount *
Drt.Conversion_Rate),
2)
END) Inv_Dist_Amt_Usd
FROM Ap_Prepay_History_All Prp,
Ap_Invoices_All Iva,
Gl_Daily_Rates Drt,
Gl_Ledgers Lgr
WHERE Prp.Transaction_Type = 'PREPAYMENT APPLIED'
AND Iva.Invoice_Id = Prp.Prepay_Invoice_Id
AND Drt.Conversion_Date(+) = Iva.Gl_Date
AND Drt.To_Currency(+) = 'USD'
AND Drt.From_Currency(+) =
Iva.Invoice_Currency_Code
AND Drt.Conversion_Type(+) = 'Corporate'
AND Lgr.Ledger_Id = Iva.Set_Of_Books_Id
GROUP BY Prp.Invoice_Id, Prp.Prepay_Invoice_Id) Ppy
WHERE Dst.Amount <> 0
AND Inv.Invoice_Id = Dst.Invoice_Id
AND Glc.Code_Combination_Id = Dst.Dist_Code_Combination_Id
AND Pdt.Po_Distribution_Id = Dst.Po_Distribution_Id
AND Cde.Code_Combination_Id = Pdt.Code_Combination_Id
AND Nvl(Cde.Segment2, Glc.Segment2) IN ('12145', '12155')
AND Sup.Vendor_Id = Inv.Vendor_Id
AND Cmb.Invoice_Id = Dst.Invoice_Id
AND Ppy.Invoice_Id = Dst.Invoice_Id
AND sup.party_id = hp.party_id
GROUP BY Dst.Org_Id,
Glc.Segment1,
Ppy.Prepay_Invoice_Id,
Inv.Invoice_Num,
Inv.Invoice_Date,
Inv.Description,
--Sup.Vendor_Name,
hp.party_name,
Sup.Segment1,
Cmb.Po_Number,
Cmb.Account_Combination,
Inv.Invoice_Currency_Code) Cap
GROUP BY Cap.Org_Id,
Cap.Company,
Cap.Invoice_Id,
Cap.Invoice_Number,
Cap.Invoice_Date,
Cap.Invoice_Description,
Cap.Vendor_Name,
Cap.Vendor_Number,
Cap.Po_Number,
Cap.Account_Combination,
Cap.Department,
Cap.Project,
Cap.Inv_Curr) Al2
WHERE Al1.Invoice_Id = Al2.Invoice_Id
AND Al1.Org_Id = Al2.Org_Id
AND Al1.Period_Name = NVL(:P_PERIOD_NAME,Al1.Period_Name)
AND Al1.Company_Code = NVL(:P_COMPANY_CODE,Al1.Company_Code)
AND Al1.Org_Id = NVL(:P_ORG_ID,Al1.Org_Id)
AND Al2.Po_Number = NVL(:P_PO_NUM,Al2.Po_Number)
AND Al1.Invoice_Number = NVL(:P_INV_NUMBER,Al1.Invoice_Number)
) l
) m
where 1=1
and m.Dept = NVL(:P_DEPT,m.Dept)
AND m.Proj = NVL(:P_PROJ,m.Proj)
No comments:
Post a Comment