AP Prepayment With Open Balances Report
With Usernam as (
select distinct ppf.person_id,
pu.username,
ppf.person_number Employee_Number,
ppn.full_name,
pu.user_id user_id,
ppn.display_name
from per_users pu,
per_all_people_f ppf,
per_person_names_f ppn
where pu.person_id = ppf.person_id
and ppf.person_id = ppn.person_id
and trunc(sysdate) between trunc(nvl(ppf.effective_start_date,trunc(sysdate))) and trunc(nvl(ppf.effective_end_date,trunc(sysdate)))
and trunc(sysdate) between trunc(nvl(ppn.effective_start_date,trunc(sysdate))) and trunc(nvl(ppn.effective_end_date,trunc(sysdate)))
and nvl(ppn.name_type,'GLOBAL') = 'GLOBAL'
and trunc(sysdate) between trunc(nvl(pu.start_date,trunc(sysdate))) and trunc(nvl(pu.end_date,trunc(sysdate)))
)
SELECT Invc.Org_Id Org_Id,
Org_name Org_name,
Invc.Company_Code Co,
Invc.Period_Name Period,
Invc.Invoice_Number Invoice_Number,
to_char(Invc.Invoice_Date, 'DD/MON/YYYY') Invoice_Date,
to_char(Invc.invoice_creation_date,'yyyy/mm/dd') invoice_creation_date,
Invc.Inv_Curr Inv_Cy_Cd,
Invc.Invoice_Amount Invoice_Amt,
Invc.Applied_Amount Applied_Amount,
Invc.Applied_Amt_Func Applied_Amt_Func,
(Invc.Invoice_Amount - Nvl(Invc.Applied_Amount, 0)) Open_Amount,
(Invc.Amount_Func - Nvl(Invc.Applied_Amt_Func, 0)) Open_Amt_Func,
(Invc.Amount_Func - Nvl(Invc.Applied_Amt_Hist, 0)) Open_Amt_Hist,
CASE
WHEN Invc.Inv_Curr = 'USD' THEN
(Invc.Invoice_Amount - Nvl(Invc.Applied_Amount, 0))
ELSE
Round(((nvl((Invc.Invoice_Amount - Nvl(Invc.Applied_Amount, 0)),0)) * Gdr.Conversion_Rate), 2)
END Open_Amount_usd,
Invc.Amount_Func Inv_Amt_Func,
(Trunc(SYSDATE) - Invc.Invoice_Date) Aging_Days,
Invc.Pay_Group Pay_Group,
to_char(Invc.Due_Date,'yyyy/mm/dd') Due_Date,
Invc.Inv_Pmt_Status Pmt_Status,
Invc.Cancelled_Date Cancelled_Date,
to_char(Invc.Gl_Date,'yyyy/mm/dd') Gl_Date,
Invc.Invoice_Description Invoice_Description,
Invc.Created_By_Id Created_By_Id,
-- Invc.Created_By_Name Created_By_Name,
nvl(usernam.display_name,created_by_name) Created_By_Name,
Invc.Doc_Sequence Doc_Sequence,
--Invc.Vendor_Name Vendor_Name,
Invc.party_name Vendor_Name,
Invc.Vendor_Number Vendor_Number,
Invc.Vendor_Site Vendor_Site,
Invc.Invoice_Source Invoice_Source,
Invc.Invoice_Type Invoice_Type,
Invc.Invoice_Id Invoice_Id,
Invc.Vendor_Id Vendor_Id,
Invc.Vendor_Site_Id Vendor_Site_Id,
sysdate timestamp ,
Invc.party_name
-- Applied_Amount
FROM (SELECT opr.organization_id Org_Id,
Opr.name Org_name,
usr.user_id user_id,
Substr(Opr.Name, 1, 3) Company_Code,
Per.Period_Name Period_Name,
Ppi.Invoice_Num Invoice_Number,
Ppi.Invoice_Date Invoice_Date,
Ppi.creation_date invoice_creation_date,
Ppi.Invoice_Currency_Code Inv_Curr,
Ppi.Invoice_Amount Invoice_Amount,
Nvl(Ppi.Base_Amount, Ppi.Invoice_Amount) Amount_Func,
Ppi.Description Invoice_Description,
Ppi.Doc_Sequence_Value Doc_Sequence,
Ppi.Pay_Group_Lookup_Code Pay_Group,
Ppi.Payment_Status_Flag Inv_Pmt_Status,
Ppi.Cancelled_Date Cancelled_Date,
Ppi.Gl_Date Gl_Date,
-- Ash.Vendor_Name Vendor_Name,
Ash.Segment1 Vendor_Number,
Ass.Vendor_Site_Code Vendor_Site,
Ppi.Source Invoice_Source,
Ppi.Invoice_Type_Lookup_Code Invoice_Type,
Ppi.Invoice_Id Invoice_Id,
Ppi.Vendor_Id Vendor_Id,
Ppi.Vendor_Site_Id Vendor_Site_Id,
Usr.Username Created_By_Id,
-- Usr.Description Created_By_Name,
usr.Username created_by_name,
-- APPS.XXON_AP_COMMON_UTILITY_PKG.IEXP_PREPAY_AMT_APPLIED (Ppi.invoice_id) Applied_Amount,
(SELECT aia1.invoice_amount - (SUM(ap_prepay_utils_pkg.get_line_prepay_amt_remaining(aila1.invoice_id, aila1.line_number))) applied_amount
FROM ap_invoices_all aia1,
ap_invoice_lines_all aila1
WHERE aia1.invoice_id=aila1.invoice_id
AND aia1.invoice_id= Ppi.invoice_id --300000648573733--p_invoice_id --50400490 --53535367
GROUP BY aia1.invoice_amount)Applied_Amount,
SUM(Nvl(Ppd.Base_Amount, Ppd.Amount) * -1) Applied_Amt_Func,
SUM(Nvl(Ppd.Base_Amt_At_Prepay_Pay_Xrate, Ppd.Amount) * -1) Applied_Amt_Hist,
Pay.Due_Date,
hp.party_name
FROM Ap_Prepay_History_All Pph,
Ap_Prepay_App_Dists Ppd,
Ap_Invoices_All Ppi,
POZ_Suppliers Ash,
hz_parties hp,
POZ_SUPPLIER_SITES_ALL_M Ass,
Hr_Operating_Units Opr,
PER_USERS Usr,
Gl_Periods Per,
(SELECT MIN(Due_Date) Due_Date, Invoice_Id
FROM Ap_Payment_Schedules_All
GROUP BY Invoice_Id) Pay
WHERE Pph.Prepay_Invoice_Id(+) = Ppi.Invoice_Id
AND Ppi.Invoice_Type_Lookup_Code = 'PREPAYMENT'
-- and Ppi.source lt;gt; 'TCF INTERCOMPANY'
AND Ppd.Prepay_History_Id(+) = Pph.Prepay_History_Id
AND Ppd.Prepay_Dist_Lookup_Code(+) IN
('PREPAY APPL', 'PREPAY APPL REC TAX')
AND Ash.Vendor_Id = Ppi.Vendor_Id
AND Ass.Vendor_Site_Id = Ppi.Vendor_Site_Id
AND Opr.Organization_Id = Ppi.Org_Id
-- AND Usr.User_Id = Ppi.Created_By
AND Usr.Username=Ppi.Created_By
AND Ppi.Gl_Date BETWEEN Per.Start_Date AND Per.End_Date
AND Per.Period_Set_Name = '4-4-5'
--AND Per.Period_Type = 'Month'
AND pay.invoice_id(+) = Ppi.invoice_id
and SUBSTR(OPR.NAME,1,3)=nvl(Substr(:P_Operating_unit,1,3), SUBSTR(OPR.NAME,1,3))
and Per.Period_Name=nvl(:P_Period_name,Per.Period_Name)
and Ppi.Pay_Group_Lookup_Code=nvl(:P_paygroup_lookup,Ppi.Pay_Group_Lookup_Code)
--AND trunc(Ppi.creation_date)=
and hp.party_id=Ash.party_id
AND TRUNC(Ppi.creation_date) BETWEEN NVL(:p_from_date, TRUNC(Ppi.creation_date))AND NVL(:p_to_date, TRUNC(Ppi.creation_date))
GROUP BY opr.ORGANIZATION_ID,
name,
Substr(Opr.Name, 1, 3),
Per.Period_Name,
usr.user_id,
Ppi.Invoice_Num,
Ppi.Invoice_Date,
Ppi.creation_date,
Ppi.Invoice_Currency_Code,
Ppi.Invoice_Amount,
Nvl(Ppi.Base_Amount, Ppi.Invoice_Amount),
Ppi.Description,
Ppi.Doc_Sequence_Value,
Ppi.Pay_Group_Lookup_Code,
Pay.Due_Date,
Ppi.Payment_Status_Flag,
Ppi.Cancelled_Date,
Ppi.Gl_Date,
-- Ash.Vendor_Name,
Ash.Segment1,
Ass.Vendor_Site_Code,
Ppi.Source,
Ppi.Invoice_Type_Lookup_Code,
Ppi.Invoice_Id,
Ppi.Vendor_Id,
Ppi.Vendor_Site_Id,
hp.party_name,
Usr.Username
-- Usr.User_Name,
-- Usr.Description,
-- APPS.XXON_AP_COMMON_UTILITY_PKG.IEXP_PREPAY_AMT_APPLIED (Ppi.invoice_id)) Invc,
-- Apps.Gl_Daily_Rates Gdr
) Invc,
Usernam usernam,
Gl_Daily_Rates Gdr
WHERE Invc.Invoice_Amount > 0
AND (Invc.Invoice_Amount - Nvl(Invc.Applied_Amount, 0))> 0
AND Gdr.From_Currency(+) = Invc.Inv_Curr
and usernam.user_id=invc.user_id
AND Gdr.To_Currency(+) = 'USD'
AND Gdr.Conversion_Type(+) = 'Corporate'
AND Gdr.Conversion_Date(+) = Invc.Gl_Date
-- AND Invc.invoice_number='121006'
Order by Invc.Company_Code,
Invc.Invoice_Date
-- AND Funtb.Created_By = Fndu.Username
-- AND Fndi.UserName(+) = Substr(Funtb.Attribute1, 1, 6)
No comments:
Post a Comment