AP Posted Invoice By Vendor and Match Type Report
---AP_S107_POSTED_INV_BY_VENDOR_AND_MATCH_TYPE_ModV1---
--AP Posted Invoice By Vendor and Match Type DM--
With Usr as (
select distinct ppf.person_id,
pu.username,
ppf.person_number Employee_Number,
ppn.full_name,
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)))
)
,Xxon_Employee_View as (
select ppf.person_id,
ppf.person_number Employee_Number,
ppn.full_name,
ppn.display_name
from per_all_people_f ppf,
per_person_names_f ppn
where 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'
)
SELECT Al1.Period,
Al1.Company_Code Co,
Al1.Ledger_Id,
Al1.Ledger_Name,
Al1.Vendor_Name,
Al1.Vendor_Number,
Al1.Vendor_Site,
Al1.Source,
Al1.Vendor_Type,
Al1.Invoice_Num,
To_Char(Al1.Invoice_Date, 'MM/DD/YYYY') Invoice_Date,
To_Char(Al1.Inv_Creation_Date, 'MM/DD/YYYY') Inv_Creation_Date,
To_Char(Al1.Accounting_Date, 'MM/DD/YYYY') Accounting_Date,
Al1.Inv_Description,
Al1.Doc_Sequence_Value Doc_Sequence,
Al1.Ap_Batch_Name,
Al1.Ap_Gac,
Al1.Ap_Lac,
Al1.Ap_Dept,
Al1.Ap_Ico,
Al1.Ap_Proj,
Al1.Inv_Currency_Code,
Al1.Account,
Al1.Local_Account Lac,
Al1.Dept,
Al1.Project Proj,
Al1.Po_Number,
Al1.Site_Country Vendor_Country_Cd,
Al1.Territory_Short_Name Vendor_Country,
Al1.Line_Type_Lookup_Code Line_Type_Dist,
/*Al1.Line_Type_Line,*/
Al1.Requester_Name,
Al1.Pur_Category_Number,
Al1.Pur_Category_Description,
--Al1.Parent_Pur_Categ,
--Al1.Parent_Pur_Cat_Desc,
--Al1.Po_Release_Num,
Al1.Po_Line_Num,
Al1.Item_Number,
Al1.Item_Description Item_Desc,
Al1.Buyer_Name,
Al1.Po_Amount Po_Amt,
Al1.Amount_Billed,
Al1.Po_Amt_Balance,
SUM(Al1.Func_Curr_Amt) Func_Curr_Amt,
SUM(Al1.Usd_Amt) Usd_Amt,
SUM(Al1.Inv_Dist_Amount) Inv_Dist_Amt,
SUM(Al1.Qty_Invoiced) Qty_Invoiced,
SUM(Al1.Qty_Received) Qty_Received,
SUM(Al1.Tax_Amt_Usd) Self_Assessed_Tax,
Al1.Tax_Code Tax_Code,
Al1.Tax_Regime Tax_Regime,
Al1.Tax_Rate_Code Tax_Rate_Code,
Al1.Tax_Jurisdiction Tax_Jurisdiction,
Al1.Tax_Rate Tax_Rate,
/*Al1.Tax_Classification_Code,*/
Al1.Match_Type,
Al1.Deliver_To_Name,
Al1.Receipt_Num,
Al1.Inv_Created_Id Inv_Created_By,
Al1.Inv_Created_Name Inv_Created_By_Desc,
Al1.Reversal_Flag,
Al1.Accounted,
Al1.Inv_Status,
Al1.Payment_Number last_payment_number,
To_Char(Al1.Payment_Date, 'MM/DD/YYYY') last_payment_date,
---nvl(Al1.Pay_Site,Al1.remit_to_supplier_Site) Pay_site,
Al1.Pay_Site Pay_site,
Al1.Payment_Terms,
To_Char(Al1.Due_Date, 'MM/DD/YYYY') Due_Date,
al1.hold,
--al1.attribute8 environmental_attribute,
CASE
WHEN EXISTS
(SELECT 1
FROM Fnd_Attached_Documents Fad
WHERE Fad.Entity_Name = 'AP_INVOICES_ALL'
AND Fad.Pk1_Value = To_Char(Al1.Invoice_Id)) THEN
'Y'
ELSE
'N'
END Attachements,
CASE
WHEN EXISTS (
SELECT
1
FROM
fnd_documents fd,
fnd_attached_documents fad
--fnd_lobs fl
WHERE
--fd.media_id = fl.file_id (+)
fad.document_id = fd.document_id
AND fad.entity_name = 'AP_INVOICES_ALL'
--AND upper(fl.file_content_type) LIKE '%PDF'
AND fad.pk1_value = to_char(al1.invoice_id)
) THEN
'Y'
ELSE
'N'
END pdf_attached,
--Al1.wf_approval_catg,
Al1.invoice_id,
Al1.Po_Distribution_Id,
sysdate timestamp
FROM (SELECT Tbl1.Source,
Tbl1.Ap_Batch_Name,
Tbl1.Ledger_Id,
Tbl1.invoice_id,
Tbl1.Ledger_Name,
Tbl1.Company_Code Company_Code,
Tbl1.Ap_Gac,
Tbl1.Ap_Lac,
Tbl1.Ap_Dept,
Tbl1.Ap_Ico,
Tbl1.Ap_Proj,
Tbl1.Vendor_Name Vendor_Name,
Tbl1.Vendor_Number Vendor_Number,
Tbl1.Vendor_Site,
Tbl1.Vendor_Type_Lookup_Code Vendor_Type,
Tbl1.Dist_Match_Type Match_Type,
Decode(Tbl1.Invoice_Currency_Code,
'USD',
Tbl1.Amount,
Round(Tbl1.Je_Line_Amt_Usd, 2)) Usd_Amt,
Tbl1.Je_Line_Amt_Func_Curr Func_Curr_Amt,
Tbl2.Po_Number Po_Number,
Tbl2.Line_Num Po_Line_Num,
Tbl2.Global_Acct Account,
Tbl2.Local_Acct Local_Account,
Tbl2.Dept Dept,
Tbl2.Project Project,
Tbl2.Intco Intco,
Tbl2.Buyer_Name Buyer_Name,
Tbl2.Deliver_To_Name Deliver_To_Name,
Tbl2.Po_Amount Po_Amount,
Tbl2.Amount_Billed Amount_Billed,
Tbl2.Po_Amount - Nvl(Tbl2.Amount_Billed, 0) Po_Amt_Balance,
Tbl1.Period_Name Period,
Tbl1.Invoice_Num Invoice_Num,
Tbl1.Invoice_Date Invoice_Date,
Tbl1.Cancelled_Date Cancelled_Date,
Tbl1.Cancelled_Amount Cancelled_Amount,
Tbl1.Inv_Creation_Date Inv_Creation_Date,
Tbl1.Inv_Hdr_Description Inv_Hdr_Description,
Tbl1.Inv_Description Inv_Description,
--Tbl1.remit_to_supplier_Site,
Tbl2.Item_Description Item_Description,
Tbl1.Amount Inv_Dist_Amount,
Tbl1.Site_Country Site_Country,
Tbl1.Territory_Short_Name,
Tbl1.Line_Type_Lookup_Code Line_Type_Lookup_Code,
/*Tbl1.Line_Type_Line,*/
Tbl1.Accounting_Date Accounting_Date,
Tbl1.Doc_Sequence_Value Doc_Sequence_Value,
Tbl1.Org_Id Org_Id,
Tbl1.Payment_Status_Flag Payment_Status_Flag,
Tbl1.Po_Distribution_Id Po_Distribution_Id,
Tbl1.Posted_Flag Posted_Flag,
Tbl1.Reversal_Flag Reversal_Flag,
Tbl1.Invoice_Currency_Code Inv_Currency_Code,
Tbl1.Receipt_Num,
Tbl1.Inv_Total_Amount Inv_Total_Amt,
Round(Tbl1.Inv_Total_Amt_Usd, 2) Inv_Total_Amt_Usd,
Tbl1.Tax_Amt_Usd Tax_Amt_Usd,
Tbl1.Tax_Code,
Tbl1.Tax_Regime,
Tbl1.Tax_Rate_Code,
Tbl1.Tax_Jurisdiction,
Tbl1.Tax_Rate,
/*Tbl1.Tax_Classification_Code,*/
Tbl1.Due_Date,
Tbl1.Accounted,
Tbl1.Inv_Status,
Tbl2.Item_Number Item_Number,
--Tbl2.Po_Release_Num,
Tbl2.Pur_Category_Number,
Tbl2.Pur_Category_Description,
--Tbl2.Parent_Pur_Categ,
--Tbl2.Parent_Pur_Cat_Desc,
Tbl1.Qty_Invoiced Qty_Invoiced,
Tbl1.Qty_Received,
Tbl1.Requester_Name Requester_Name,
Tbl1.Created_By_Id Inv_Created_Id,
Tbl1.Created_By_Name Inv_Created_Name,
Tbl1.Payment_Terms,
Tbl4.Check_Number Payment_Number,
Tbl4.Payment_Date,
/* --Remit_To_Supplier_Site NA in Ap_Invoice_Payments_All---
Coalesce(Tbl4.Pay_Site, (SELECT max(a.Remit_To_Supplier_Site)
FROM Ap_Invoice_Payments_All a
WHERE a.check_id = Tbl4.check_id)) Pay_Site,
*/
Tbl4.Pay_Site Pay_Site,
tbl1.hold
--tbl1.attribute8,
--tbl1.wf_approval_catg
FROM (SELECT Api.Source,
Aba.Batch_Name Ap_Batch_Name,
Glbook.Ledger_Id,
Glbook.Name Ledger_Name,
Apid.Org_Id Org_Id,
Apid.Period_Name,
Api.Description Inv_Hdr_Description,
Apid.Description Inv_Description,
Glc.Segment1 Company_Code,
Glc.Segment3 Ap_Gac,
Glc.Segment4 Ap_Lac,
Glc.Segment5 Ap_Dept,
Glc.Segment7 Ap_Ico,
Glc.Segment8 Ap_Proj,
Apid.Amount Amount,
Apid.Po_Distribution_Id Po_Distribution_Id,
Apid.Dist_Match_Type Dist_Match_Type,
Apid.Line_Type_Lookup_Code Line_Type_Lookup_Code,
Api.Invoice_Id,
Api.Invoice_Num Invoice_Num,
Api.Invoice_Date Invoice_Date,
Api.Creation_Date Inv_Creation_Date,
Api.Cancelled_Date Cancelled_Date,
Api.Cancelled_Amount Cancelled_Amount,
Api.Payment_Status_Flag Payment_Status_Flag,
--Api.remit_to_supplier_Site,
Nvl(Apid.Reversal_Flag, 'N') Reversal_Flag,
Apid.Posted_Flag Posted_Flag,
Apid.Accounting_Date Accounting_Date,
Decode(Apid.Posted_Flag,
'E',
'Error',
'N',
'Unprocessed',
'P',
'Partial',
'S',
'Processing',
'Y',
'Processed',
'NA') Accounted,
/*
(SELECT get_inv_status(Api.Invoice_Id,
Api.Invoice_Amount,
Api.Payment_Status_Flag,
Api.Invoice_Type_Lookup_Code) FROM dual) Inv_Status,
*/
Decode(ap_invoices_pkg.get_approval_status(Api.Invoice_Id,
Api.Invoice_Amount,
Api.Payment_Status_Flag,
Api.Invoice_Type_Lookup_Code),
'FULL', 'Fully Applied Prepayment',
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Re-validation',
'CANCELLED', 'Cancelled',
'UNPAID', 'Unpaid Prepayment',
'AVAILABLE', 'Available Prepayment',
'UNAPPROVED', 'Unvalidated Prepayment',
'APPROVED', 'Validated',
'PERMANENT', 'Permanent Prepayment',
NULL) Inv_Status,
Api.Doc_Sequence_Value Doc_Sequence_Value,
Api.Invoice_Currency_Code Invoice_Currency_Code,
Pov.Vendor_Name Vendor_Name,
Pov.Segment1 Vendor_Number,
Pov.Vendor_Type_Lookup_Code Vendor_Type_Lookup_Code,
Pvs.Vendor_Site_Code Vendor_Site,
Pvs.Country Site_Country,
Ftt.Territory_Short_Name Territory_Short_Name,
Shp.Receipt_Num Receipt_Num,
Trm.Name Payment_Terms,
Schd.Due_Date Due_Date,
Rcv.Quantity Qty_Received,
CASE
WHEN Api.Invoice_Currency_Code = 'USD' THEN
Apid.Amount
WHEN (Api.Invoice_Currency_Code <> 'USD' AND
Glbook.Currency_Code = 'USD') THEN
Apid.Base_Amount
ELSE
case when nvl(api.exchange_rate_type,'Corporate') = 'User'
then Apid.Amount * api.exchange_rate * Glrate2.Conversion_Rate
else
Apid.Amount * Glrate.Conversion_Rate
end
END Je_Line_Amt_Usd,
CASE
WHEN Api.Invoice_Currency_Code = Glbook.Currency_Code THEN
Apid.Amount
ELSE
Apid.Base_Amount
END Je_Line_Amt_Func_Curr,
Api.Invoice_Amount Inv_Total_Amount,
CASE
WHEN Api.Invoice_Currency_Code = 'USD' THEN
Api.Invoice_Amount
ELSE
Api.Invoice_Amount * Glrate.Conversion_Rate
END Inv_Total_Amt_Usd,
CASE
WHEN Api.Invoice_Currency_Code = 'USD' THEN
Nvl(Taxl.Tax_Amt_Inv, 0)
WHEN ((Api.Invoice_Currency_Code <> 'USD') AND
(Glbook.Currency_Code = 'USD')) THEN
Nvl(Taxl.Tax_Amt_Func, 0)
ELSE
Round((Nvl(Taxl.Tax_Amt_Inv, 0) *
Glrate.Conversion_Rate),
2)
END Tax_Amt_Usd,
--Api.attribute8,
--Api.attribute10 wf_approval_catg,
Taxt.Tax_Code,
Taxt.Tax_Regime,
Taxt.Tax_Rate_Code,
Taxt.Tax_Jurisdiction,
Taxt.Tax_Rate,
Apid.Quantity_Invoiced Qty_Invoiced,
Rqu.Full_Name Requester_Name,
Usr.Username Created_By_Id,
Usr.display_name Created_By_Name,
CASE
WHEN EXISTS (
SELECT
1
FROM
ap_holds_all aha
WHERE
aha.invoice_id = api.invoice_id
AND aha.release_lookup_code IS NULL
UNION ALL
SELECT
1
FROM
ap_payment_schedules_all apsa
WHERE
apsa.hold_flag = 'Y'
AND apsa.invoice_id = api.invoice_id
UNION ALL
SELECT
1
FROM
dual
WHERE
api.payment_status_flag != 'Y'
---AND pov.hold_all_payments_flag = 'Y' --NA---
AND api.cancelled_date IS NULL
) THEN
'Y'
ELSE
NULL
END hold
FROM Ap_Invoices_All Api,
Ap_Batches_All Aba,
Ap_Invoice_Distributions_All Apid,
Gl_Code_Combinations Glc,
Gl_Ledgers Glbook,
--Ap_Suppliers Pov,
poz_suppliers_v Pov,
Gl_Daily_Rates Glrate,
Gl_Daily_Rates Glrate2,
--Ap_Supplier_Sites_All Pvs,
POZ_SUPPLIER_SITES_V Pvs,
Xxon_Employee_View Rqu,
Usr Usr,
Rcv_Shipment_Headers Shp,
Rcv_Transactions Rcv,
Ap_Terms Trm,
Fnd_Territories_Tl Ftt,
(SELECT Invoice_Id, MIN(Due_Date) Due_Date
FROM Ap_Payment_Schedules_All
GROUP BY Invoice_Id) Schd,
(SELECT Zxl.Trx_Id Invoice_Id,
Zxl.Internal_Organization_Id Org_Id,
Zxl.Trx_Line_Number Trx_Line,
SUM(Zxl.Tax_Amt) Tax_Amt_Inv,
SUM(Nvl(Zxl.Tax_Amt_Funcl_Curr, Zxl.Tax_Amt)) Tax_Amt_Func
FROM Zx_Lines Zxl
WHERE Zxl.Application_Id = 200
AND Zxl.Entity_Code = 'AP_INVOICES'
---AND Zxl.Internal_Organization_Id IN (85, 192, 193, 194, 7955, 8014, 8603)
AND Zxl.Self_Assessed_Flag = 'Y'
GROUP BY Zxl.Trx_Id,
Zxl.Internal_Organization_Id,
Zxl.Trx_Line_Number) Taxl,
(SELECT Zrn.Tax Tax_Code,
Zrn.Tax_Rate_Code,
Zrn.Tax_Regime_Code Tax_Regime,
Zrn.Tax_Rate,
Zj.Tax_Jurisdiction_Code Tax_Jurisdiction,
Zrn.Summary_Tax_Line_Id,
Zrn.Rec_Nrec_Tax_Dist_Id
FROM Zx_Rec_Nrec_Dist Zrn,
Zx_Jurisdictions_b Zj
WHERE Zrn.Tax_Jurisdiction_Id =
Zj.Tax_Jurisdiction_Id
AND Zrn.Application_Id = 200
AND Zrn.Entity_Code = 'AP_INVOICES') Taxt
WHERE Glbook.Ledger_Id = Apid.Set_Of_Books_Id
AND Api.Batch_Id = Aba.Batch_Id(+)
and Api.source <> 'TCF INTERCOMPANY'
AND Glc.Code_Combination_Id =
Apid.Dist_Code_Combination_Id
/*AND Apid.Invoice_Id = Ail.Invoice_Id(+)
AND Apid.Distribution_Line_Number = Ail.Line_Number(+)*/
AND Api.Invoice_Id = Apid.Invoice_Id(+)
AND Pov.Vendor_Id = Api.Vendor_Id
AND Taxl.Invoice_Id(+) = Apid.Invoice_Id
AND Taxl.Org_Id(+) = Apid.Org_Id
AND Taxl.Trx_Line(+) = Apid.Invoice_Line_Number
AND Apid.Summary_Tax_Line_Id = Taxt.Summary_Tax_Line_Id(+)
AND Apid.Detail_Tax_Dist_Id = Taxt.Rec_Nrec_Tax_Dist_Id(+)
AND Shp.Shipment_Header_Id(+) = Rcv.Shipment_Header_Id
AND Rcv.Transaction_Id(+) = Apid.Rcv_Transaction_Id
AND Schd.Invoice_Id(+) = Api.Invoice_Id
AND Glrate.From_Currency(+) = Api.Invoice_Currency_Code
AND Trm.Term_Id(+) = Api.Terms_Id
AND Glrate.Conversion_Date(+) = Trunc(Api.Gl_Date)
AND Glrate.Conversion_Type(+) = 'Corporate'
AND Glrate.To_Currency(+) = 'USD'
AND Glrate2.From_Currency(+) = Glbook.Currency_Code
AND Glrate2.Conversion_Date(+) = Trunc(Api.Gl_Date)
AND Glrate2.Conversion_Type(+) = 'Corporate'
AND Glrate2.To_Currency(+) = 'USD'
AND Pvs.Vendor_Site_Id(+) = Api.Vendor_Site_Id
AND Ftt.Territory_Code(+) = Pvs.Country
AND Ftt.Language(+) = 'US'
AND Rqu.Person_Id(+) = Api.Requester_Id
/*
AND Trunc(SYSDATE) BETWEEN
Rqu.Person_Effective_Start_Date(+) AND
Rqu.Person_Effective_End_Date(+)
*/
AND Usr.Username(+) = Api.Created_By
AND ((case when (Apid.Amount <> 0 OR nvl(Apid.Base_Amount,0) <> 0)
then 'Y' else 'N' end ) = :P_IGN_ZERO_DIST_AMT
or 'All' = :P_IGN_ZERO_DIST_AMT|| 'All')
AND ( case when Nvl(Apid.Reversal_Flag, 'N') in (:P_REVERSAL_FLAG) then 1
when (COALESCE(NULL,:P_REVERSAL_FLAG) is NULL ) then 1
end = 1 )
) Tbl1,
(SELECT Pol.Item_Description Item_Description,
Pol.Line_Num Line_Num,
--Dst.Org_Id Org_Id,
Dst.Po_Header_Id Po_Header_Id,
Poh.Segment1 Po_Number,
Dst.Po_Distribution_Id Po_Distribution_Id,
--Rel.Release_Num Po_Release_Num,
Glc.Segment3 Global_Acct,
Glc.Segment4 Local_Acct,
Glc.Segment5 Dept,
Glc.Segment7 Intco,
Glc.Segment8 Project,
Round((Pll.Quantity * Pol.Unit_Price), 2) Po_Amount,
Dst.Amount_Billed Amount_Billed,
/*
CASE
WHEN Buy.First_Name IS NULL THEN
NULL
ELSE
Buy.First_Name || ' ' || Buy.Last_Name
END Buyer_Name,
CASE
WHEN Pap.First_Name IS NULL THEN
NULL
ELSE
Pap.First_Name || ' ' || Pap.Last_Name
END Deliver_To_Name,
*/
Buy.display_name Buyer_Name,
Pap.display_name Deliver_To_Name,
Msi.ITEM_NUMBER Item_Number,
Ctb.Segment1 Pur_Category_Number,
Ctl.Description Pur_Category_Description
--regexp_substr(ffv.cat, '[^~]+', 1, 2) parent_pur_categ,
--regexp_substr(ffv.cat, '[^~]+', 1, 1) parent_pur_cat_desc
FROM Po_Headers_All Poh,
Po_Lines_All Pol,
Po_Distributions_All Dst,
--Po_Releases_All Rel,
Po_Line_Locations_All Pll,
Gl_Code_Combinations Glc,
Xxon_Employee_View Pap,
Xxon_Employee_View Buy,
--Mtl_System_Items_b Msi,
egp_System_Items_v Msi,
--Mtl_Categories_b Ctb,
egp_categories_b Ctb,
--Mtl_Categories_Tl Ctl,
egp_categories_tl Ctl
/*
lateral (
SELECT
LISTAGG(ffv.description, ',') WITHIN GROUP(
ORDER BY
ffv.flex_value
)
|| '~'
||
LISTAGG(ffv.flex_value, ',') WITHIN GROUP(
ORDER BY
ffv.flex_value
)
cat
FROM
fnd_flex_value_sets ffvs,
fnd_flex_values_vl ffv
WHERE
ffvs.flex_value_set_name = 'XXON_PO_ITEM_CATEGORY'
AND ffv.flex_value_set_id = ffvs.flex_value_set_id
AND ffv.enabled_flag = 'Y'
AND ffv.summary_flag = 'Y'
AND substr(ffv.flex_value(+), 1, 3) = substr(ctb.Segment1, 1, 3)
) (+) ffv
*/
WHERE Poh.Po_Header_Id = Dst.Po_Header_Id
AND Pol.Po_Line_Id = Dst.Po_Line_Id
AND Pll.Line_Location_Id = Dst.Line_Location_Id
--AND Rel.Po_Release_Id(+) = Dst.Po_Release_Id
AND Glc.Code_Combination_Id = Dst.Code_Combination_Id
AND Pap.Person_Id(+) = Dst.Deliver_To_Person_Id
/*
AND Trunc(SYSDATE) BETWEEN
Pap.Person_Effective_Start_Date(+) AND
Pap.Person_Effective_End_Date(+)
AND Trunc(SYSDATE) BETWEEN Buy.Person_Effective_Start_Date AND
Buy.Person_Effective_End_Date
*/
AND Buy.Person_Id = Poh.Agent_Id
AND Ctb.Category_Id(+) = Pol.Category_Id
AND Ctb.Structure_Id(+) = 201
AND Ctl.Category_Id(+) = Pol.Category_Id
AND Ctl.Language(+) = 'US'
AND Msi.Organization_Id(+) = Pll.Ship_To_Organization_Id
AND Msi.Inventory_Item_Id(+) = Pol.Item_Id
AND ( case when msi.ITEM_NUMBER in (:P_ITEM_NUMBER) then 1
when (COALESCE(NULL,:P_ITEM_NUMBER) is NULL ) then 1
end = 1 )
) Tbl2,
(SELECT a.Invoice_Id,
Apc.Check_Date Payment_Date,
Apc.Check_Number Check_Number,
Apc.Vendor_Site_Code Pay_Site,
Apc.Check_id
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) Tbl4
WHERE
Tbl2.Po_Distribution_Id(+) = Tbl1.Po_Distribution_Id
AND Tbl4.Invoice_Id(+) = Tbl1.Invoice_Id
) Al1
WHERE 1 = 1
AND ( case when Al1.Period in (:P_PERIOD) then 1
when (COALESCE(NULL,:P_PERIOD) is NULL ) then 1
end = 1 )
AND ( case when Al1.Company_Code in (:P_CO) then 1
when (COALESCE(NULL,:P_CO) is NULL ) then 1
end = 1 )
AND ( case when Al1.Org_Id in (:P_ORG_ID) then 1
when (COALESCE(NULL,:P_ORG_ID) is NULL ) then 1
end = 1 )
AND ( case when Al1.Vendor_Name in (:p_Vendor_Name) then 1
when (COALESCE(NULL,:p_Vendor_Name) is NULL ) then 1
end = 1 )
AND ( case when Al1.Vendor_Number in (:P_VENDOR_NUMBER) then 1
when (COALESCE(NULL,:P_VENDOR_NUMBER) is NULL ) then 1
end = 1 )
AND ( case when Al1.Source in (:P_SOURCE) then 1
when (COALESCE(NULL,:P_SOURCE) is NULL ) then 1
end = 1 )
AND ( case when Al1.Accounted in (:P_ACCOUNTED) then 1
when (COALESCE(NULL,:P_ACCOUNTED) is NULL ) then 1
end = 1 )
AND ( case when Al1.invoice_num in (:P_INVOICE_NUM) then 1
when (COALESCE(NULL,:P_INVOICE_NUM) is NULL ) then 1
end = 1 )
AND ( case when Al1.po_number in (:P_PO_NUMBER) then 1
when (COALESCE(NULL,:P_PO_NUMBER) is NULL ) then 1
end = 1 )
and ( trunc(Al1.Accounting_Date) >= nvl((:p_acct_date_from),trunc(Al1.Accounting_Date))
and trunc(Al1.Accounting_Date) <= nvl((:p_acct_date_to),trunc(Al1.Accounting_Date)) )
AND ( case when Al1.Account in (:P_PO_GAC) then 1
when (COALESCE(NULL,:P_PO_GAC) is NULL ) then 1
end = 1 )
AND ( case when Al1.Ap_Gac in (:P_AP_GAC) then 1
when (COALESCE(NULL,:P_AP_GAC) is NULL ) then 1
end = 1 )
GROUP BY Al1.Period,
Al1.Company_Code,
Al1.Ledger_Id,
Al1.Ledger_Name,
Al1.Vendor_Name,
Al1.Vendor_Number,
Al1.Vendor_Site,
Al1.Source,
Al1.Vendor_Type,
Al1.Invoice_Num,
To_Char(Al1.Invoice_Date, 'MM/DD/YYYY'),
Al1.Inv_Creation_Date,
To_Char(Al1.Accounting_Date, 'MM/DD/YYYY'),
Al1.Doc_Sequence_Value,
Al1.Inv_Description,
Al1.Ap_Batch_Name,
Al1.Ap_Gac,
Al1.Ap_Lac,
Al1.Ap_Dept,
Al1.Ap_Ico,
Al1.Ap_Proj,
Al1.Inv_Currency_Code,
Al1.Account,
Al1.Local_Account,
Al1.Dept,
Al1.Project,
Al1.Po_Number,
Al1.Site_Country,
Al1.Territory_Short_Name,
Al1.Line_Type_Lookup_Code,
/*Al1.Line_Type_Line,*/
Al1.Requester_Name,
Al1.Pur_Category_Number,
Al1.Pur_Category_Description,
--Al1.Parent_Pur_Categ,
--Al1.Parent_Pur_Cat_Desc,
--Al1.Po_Release_Num,
Al1.Po_Line_Num,
Al1.Item_Number,
Al1.Item_Description,
Al1.Buyer_Name,
Al1.Po_Amount,
Al1.Amount_Billed,
Al1.Po_Amt_Balance,
Al1.Tax_Code,
Al1.Tax_Regime,
Al1.Tax_Rate_Code,
Al1.Tax_Jurisdiction,
Al1.Tax_Rate,
/*Al1.Tax_Classification_Code,*/
Al1.Match_Type,
Al1.Deliver_To_Name,
Al1.Receipt_Num,
Al1.Inv_Created_Id,
Al1.Inv_Created_Name,
Al1.Reversal_Flag,
Al1.Accounted,
Al1.Inv_Status,
Al1.Payment_Number,
To_Char(Al1.Payment_Date, 'MM/DD/YYYY'),
--nvl(Al1.Pay_Site,Al1.remit_to_supplier_Site),
Al1.Pay_Site,
Al1.Payment_Terms,
To_Char(Al1.Due_Date, 'MM/DD/YYYY'),
al1.hold,
--al1.attribute8,
--Al1.wf_approval_catg,
Al1.invoice_id,
Al1.Po_Distribution_Id
union all
SELECT Al1.Period,
Al1.Company_Code Co,
Al1.Ledger_Id,
Al1.Ledger_Name,
Al1.Vendor_Name,
Al1.Vendor_Number,
Al1.Vendor_Site,
Al1.Source,
Al1.Vendor_Type,
Al1.Invoice_Num,
To_Char(Al1.Invoice_Date, 'MM/DD/YYYY') Invoice_Date,
To_Char(Al1.Inv_Creation_Date, 'MM/DD/YYYY') Inv_Creation_Date,
To_Char(Al1.Accounting_Date, 'MM/DD/YYYY') Accounting_Date,
Al1.Inv_Description,
Al1.Doc_Sequence_Value Doc_Sequence,
Al1.Ap_Batch_Name,
Al1.Ap_Gac,
Al1.Ap_Lac,
Al1.Ap_Dept,
Al1.Ap_Ico,
Al1.Ap_Proj,
Al1.Inv_Currency_Code,
Al1.Account,
Al1.Local_Account Lac,
Al1.Dept,
Al1.Project Proj,
Al1.Po_Number,
Al1.Site_Country Vendor_Country_Cd,
Al1.Territory_Short_Name Vendor_Country,
Al1.Line_Type_Lookup_Code Line_Type_Dist,
/*Al1.Line_Type_Line,*/
Al1.Requester_Name,
Al1.Pur_Category_Number,
Al1.Pur_Category_Description,
--Al1.Parent_Pur_Categ,
--Al1.Parent_Pur_Cat_Desc,
--Al1.Po_Release_Num,
Al1.Po_Line_Num,
Al1.Item_Number,
Al1.Item_Description Item_Desc,
Al1.Buyer_Name,
Al1.Po_Amount Po_Amt,
Al1.Amount_Billed,
Al1.Po_Amt_Balance,
SUM(Al1.Func_Curr_Amt) Func_Curr_Amt,
SUM(Al1.Usd_Amt) Usd_Amt,
SUM(Al1.Inv_Dist_Amount) Inv_Dist_Amt,
SUM(Al1.Qty_Invoiced) Qty_Invoiced,
SUM(Al1.Qty_Received) Qty_Received,
SUM(Al1.Tax_Amt_Usd) Self_Assessed_Tax,
Al1.Tax_Code Tax_Code,
Al1.Tax_Regime Tax_Regime,
Al1.Tax_Rate_Code Tax_Rate_Code,
Al1.Tax_Jurisdiction Tax_Jurisdiction,
Al1.Tax_Rate Tax_Rate,
/*Al1.Tax_Classification_Code,*/
Al1.Match_Type,
Al1.Deliver_To_Name,
Al1.Receipt_Num,
Al1.Inv_Created_Id Inv_Created_By,
Al1.Inv_Created_Name Inv_Created_By_Desc,
Al1.Reversal_Flag,
Al1.Accounted,
Al1.Inv_Status,
Al1.Payment_Number last_payment_number,
To_Char(Al1.Payment_Date, 'MM/DD/YYYY') last_payment_date,
---nvl(Al1.Pay_Site,Al1.remit_to_supplier_Site) Pay_site,
Al1.Pay_Site Pay_site,
Al1.Payment_Terms,
To_Char(Al1.Due_Date, 'MM/DD/YYYY') Due_Date,
al1.hold,
---al1.attribute8 environmental_attribute,
CASE
WHEN EXISTS
(SELECT 1
FROM Fnd_Attached_Documents Fad
WHERE Fad.Entity_Name = 'AP_INVOICES_ALL'
AND Fad.Pk1_Value = To_Char(Al1.Invoice_Id)) THEN
'Y'
ELSE
'N'
END Attachements,
CASE
WHEN EXISTS (
SELECT
1
FROM
fnd_documents fd,
fnd_attached_documents fad
--fnd_lobs fl
WHERE
--fd.media_id = fl.file_id (+)
fad.document_id = fd.document_id
AND fad.entity_name = 'AP_INVOICES_ALL'
--AND upper(fl.file_content_type) LIKE '%PDF'
AND fad.pk1_value = to_char(al1.invoice_id)
) THEN
'Y'
ELSE
'N'
END pdf_attached,
--Al1.wf_approval_catg,
Al1.invoice_id,
Al1.Po_Distribution_Id,
sysdate timestamp
FROM (SELECT Tbl1.Source,
Tbl1.Ap_Batch_Name,
Tbl1.Ledger_Id,
Tbl1.invoice_id,
Tbl1.Ledger_Name,
Tbl1.Company_Code Company_Code,
Tbl1.Ap_Gac,
Tbl1.Ap_Lac,
Tbl1.Ap_Dept,
Tbl1.Ap_Ico,
Tbl1.Ap_Proj,
Tbl1.Vendor_Name Vendor_Name,
Tbl1.Vendor_Number Vendor_Number,
Tbl1.Vendor_Site,
Tbl1.Vendor_Type_Lookup_Code Vendor_Type,
Tbl1.Dist_Match_Type Match_Type,
Decode(Tbl1.Invoice_Currency_Code,
'USD',
Tbl1.Amount,
Round(Tbl1.Je_Line_Amt_Usd, 2)) Usd_Amt,
Tbl1.Je_Line_Amt_Func_Curr Func_Curr_Amt,
Tbl2.Po_Number Po_Number,
Tbl2.Line_Num Po_Line_Num,
Tbl2.Global_Acct Account,
Tbl2.Local_Acct Local_Account,
Tbl2.Dept Dept,
Tbl2.Project Project,
Tbl2.Intco Intco,
Tbl2.Buyer_Name Buyer_Name,
Tbl2.Deliver_To_Name Deliver_To_Name,
Tbl2.Po_Amount Po_Amount,
Tbl2.Amount_Billed Amount_Billed,
Tbl2.Po_Amount - Nvl(Tbl2.Amount_Billed, 0) Po_Amt_Balance,
Tbl1.Period_Name Period,
Tbl1.Invoice_Num Invoice_Num,
Tbl1.Invoice_Date Invoice_Date,
Tbl1.Cancelled_Date Cancelled_Date,
Tbl1.Cancelled_Amount Cancelled_Amount,
Tbl1.Inv_Creation_Date Inv_Creation_Date,
Tbl1.Inv_Hdr_Description Inv_Hdr_Description,
Tbl1.Inv_Description Inv_Description,
--Tbl1.remit_to_supplier_Site,
Tbl2.Item_Description Item_Description,
Tbl1.Amount Inv_Dist_Amount,
Tbl1.Site_Country Site_Country,
Tbl1.Territory_Short_Name,
Tbl1.Line_Type_Lookup_Code Line_Type_Lookup_Code,
/*Tbl1.Line_Type_Line,*/
Tbl1.Accounting_Date Accounting_Date,
Tbl1.Doc_Sequence_Value Doc_Sequence_Value,
Tbl1.Org_Id Org_Id,
Tbl1.Payment_Status_Flag Payment_Status_Flag,
Tbl1.Po_Distribution_Id Po_Distribution_Id,
Tbl1.Posted_Flag Posted_Flag,
Tbl1.Reversal_Flag Reversal_Flag,
Tbl1.Invoice_Currency_Code Inv_Currency_Code,
Tbl1.Receipt_Num,
Tbl1.Inv_Total_Amount Inv_Total_Amt,
Round(Tbl1.Inv_Total_Amt_Usd, 2) Inv_Total_Amt_Usd,
Tbl1.Tax_Amt_Usd Tax_Amt_Usd,
Tbl1.Tax_Code,
Tbl1.Tax_Regime,
Tbl1.Tax_Rate_Code,
Tbl1.Tax_Jurisdiction,
Tbl1.Tax_Rate,
/*Tbl1.Tax_Classification_Code,*/
Tbl1.Due_Date,
Tbl1.Accounted,
Tbl1.Inv_Status,
Tbl2.Item_Number Item_Number,
--Tbl2.Po_Release_Num,
Tbl2.Pur_Category_Number,
Tbl2.Pur_Category_Description,
--Tbl2.Parent_Pur_Categ,
--Tbl2.Parent_Pur_Cat_Desc,
Tbl1.Qty_Invoiced Qty_Invoiced,
Tbl1.Qty_Received,
Tbl1.Requester_Name Requester_Name,
Tbl1.Created_By_Id Inv_Created_Id,
Tbl1.Created_By_Name Inv_Created_Name,
Tbl1.Payment_Terms,
Tbl4.Check_Number Payment_Number,
Tbl4.Payment_Date,
/* --Remit_To_Supplier_Site NA in Ap_Invoice_Payments_All---
Coalesce(Tbl4.Pay_Site, (SELECT max(a.Remit_To_Supplier_Site)
FROM Ap_Invoice_Payments_All a
WHERE a.check_id = Tbl4.check_id)) Pay_Site,
*/
Tbl4.Pay_Site Pay_Site,
tbl1.hold
--tbl1.attribute8,
--tbl1.wf_approval_catg
FROM (SELECT Api.Source,
Aba.Batch_Name Ap_Batch_Name,
Glbook.Ledger_Id,
Glbook.Name Ledger_Name,
Apid.Org_Id Org_Id,
Apid.Period_Name,
Api.Description Inv_Hdr_Description,
Apid.Description Inv_Description,
Glc.Segment1 Company_Code,
Glc.Segment3 Ap_Gac,
Glc.Segment4 Ap_Lac,
Glc.Segment5 Ap_Dept,
Glc.Segment7 Ap_Ico,
Glc.Segment8 Ap_Proj,
Apid.Amount Amount,
Apid.Po_Distribution_Id Po_Distribution_Id,
Apid.Dist_Match_Type Dist_Match_Type,
Apid.Line_Type_Lookup_Code Line_Type_Lookup_Code,
Api.Invoice_Id,
Api.Invoice_Num Invoice_Num,
Api.Invoice_Date Invoice_Date,
Api.Creation_Date Inv_Creation_Date,
Api.Cancelled_Date Cancelled_Date,
Api.Cancelled_Amount Cancelled_Amount,
Api.Payment_Status_Flag Payment_Status_Flag,
--Api.remit_to_supplier_Site,
Nvl(Apid.Reversal_Flag, 'N') Reversal_Flag,
Apid.Posted_Flag Posted_Flag,
Apid.Accounting_Date Accounting_Date,
Decode(Apid.Posted_Flag,
'E',
'Error',
'N',
'Unprocessed',
'P',
'Partial',
'S',
'Processing',
'Y',
'Processed',
'NA') Accounted,
/*
(SELECT get_inv_status(Api.Invoice_Id,
Api.Invoice_Amount,
Api.Payment_Status_Flag,
Api.Invoice_Type_Lookup_Code) FROM dual) Inv_Status,
*/
Decode(ap_invoices_pkg.get_approval_status(Api.Invoice_Id,
Api.Invoice_Amount,
Api.Payment_Status_Flag,
Api.Invoice_Type_Lookup_Code),
'FULL', 'Fully Applied Prepayment',
'NEVER APPROVED', 'Never Validated',
'NEEDS REAPPROVAL', 'Needs Re-validation',
'CANCELLED', 'Cancelled',
'UNPAID', 'Unpaid Prepayment',
'AVAILABLE', 'Available Prepayment',
'UNAPPROVED', 'Unvalidated Prepayment',
'APPROVED', 'Validated',
'PERMANENT', 'Permanent Prepayment',
NULL) Inv_Status,
Api.Doc_Sequence_Value Doc_Sequence_Value,
Api.Invoice_Currency_Code Invoice_Currency_Code,
Pov.Vendor_Name Vendor_Name,
Pov.Segment1 Vendor_Number,
Pov.Vendor_Type_Lookup_Code Vendor_Type_Lookup_Code,
Pvs.Vendor_Site_Code Vendor_Site,
Pvs.Country Site_Country,
Ftt.Territory_Short_Name Territory_Short_Name,
Shp.Receipt_Num Receipt_Num,
Trm.Name Payment_Terms,
Schd.Due_Date Due_Date,
Rcv.Quantity Qty_Received,
CASE
WHEN Api.Invoice_Currency_Code = 'USD' THEN
Apid.Amount
WHEN (Api.Invoice_Currency_Code <> 'USD' AND
Glbook.Currency_Code = 'USD') THEN
Apid.Base_Amount
ELSE
case when nvl(api.exchange_rate_type,'Corporate') = 'User'
then Apid.Amount * api.exchange_rate * Glrate2.Conversion_Rate
else
Apid.Amount * Glrate.Conversion_Rate
end
END Je_Line_Amt_Usd,
CASE
WHEN Api.Invoice_Currency_Code = Glbook.Currency_Code THEN
Apid.Amount
ELSE
Apid.Base_Amount
END Je_Line_Amt_Func_Curr,
Api.Invoice_Amount Inv_Total_Amount,
CASE
WHEN Api.Invoice_Currency_Code = 'USD' THEN
Api.Invoice_Amount
ELSE
Api.Invoice_Amount * Glrate.Conversion_Rate
END Inv_Total_Amt_Usd,
CASE
WHEN Api.Invoice_Currency_Code = 'USD' THEN
Nvl(Taxl.Tax_Amt_Inv, 0)
WHEN ((Api.Invoice_Currency_Code <> 'USD') AND
(Glbook.Currency_Code = 'USD')) THEN
Nvl(Taxl.Tax_Amt_Func, 0)
ELSE
Round((Nvl(Taxl.Tax_Amt_Inv, 0) *
Glrate.Conversion_Rate),
2)
END Tax_Amt_Usd,
--Api.attribute8,
--Api.attribute10 wf_approval_catg,
Taxt.Tax_Code,
Taxt.Tax_Regime,
Taxt.Tax_Rate_Code,
Taxt.Tax_Jurisdiction,
Taxt.Tax_Rate,
Apid.Quantity_Invoiced Qty_Invoiced,
Rqu.Full_Name Requester_Name,
Usr.Username Created_By_Id,
Usr.display_name Created_By_Name,
CASE
WHEN EXISTS (
SELECT
1
FROM
ap_holds_all aha
WHERE
aha.invoice_id = api.invoice_id
AND aha.release_lookup_code IS NULL
UNION ALL
SELECT
1
FROM
ap_payment_schedules_all apsa
WHERE
apsa.hold_flag = 'Y'
AND apsa.invoice_id = api.invoice_id
UNION ALL
SELECT
1
FROM
dual
WHERE
api.payment_status_flag != 'Y'
---AND pov.hold_all_payments_flag = 'Y' ---NA---
AND api.cancelled_date IS NULL
) THEN
'Y'
ELSE
NULL
END hold
FROM Ap_Invoices_All Api,
Ap_Batches_All Aba,
Ap_Invoice_Distributions_All Apid,
Gl_Code_Combinations Glc,
Gl_Ledgers Glbook,
--Ap_Suppliers Pov,
poz_suppliers_v Pov,
Gl_Daily_Rates Glrate,
Gl_Daily_Rates Glrate2,
--Ap_Supplier_Sites_All Pvs,
POZ_SUPPLIER_SITES_V Pvs,
Xxon_Employee_View Rqu,
Usr Usr,
Rcv_Shipment_Headers Shp,
Rcv_Transactions Rcv,
Ap_Terms Trm,
Fnd_Territories_Tl Ftt,
(SELECT Invoice_Id, MIN(Due_Date) Due_Date
FROM Ap_Payment_Schedules_All
GROUP BY Invoice_Id) Schd,
(SELECT Zxl.Trx_Id Invoice_Id,
Zxl.Internal_Organization_Id Org_Id,
Zxl.Trx_Line_Number Trx_Line,
SUM(Zxl.Tax_Amt) Tax_Amt_Inv,
SUM(Nvl(Zxl.Tax_Amt_Funcl_Curr, Zxl.Tax_Amt)) Tax_Amt_Func
FROM Zx_Lines Zxl
WHERE Zxl.Application_Id = 200
AND Zxl.Entity_Code = 'AP_INVOICES'
--AND Zxl.Internal_Organization_Id IN (85, 192, 193, 194, 7955, 8014, 8603)
AND Zxl.Self_Assessed_Flag = 'Y'
GROUP BY Zxl.Trx_Id,
Zxl.Internal_Organization_Id,
Zxl.Trx_Line_Number) Taxl,
(SELECT Zrn.Tax Tax_Code,
Zrn.Tax_Rate_Code,
Zrn.Tax_Regime_Code Tax_Regime,
Zrn.Tax_Rate,
Zj.Tax_Jurisdiction_Code Tax_Jurisdiction,
Zrn.Summary_Tax_Line_Id,
Zrn.Rec_Nrec_Tax_Dist_Id
FROM Zx_Rec_Nrec_Dist Zrn,
Zx_Jurisdictions_b Zj
WHERE Zrn.Tax_Jurisdiction_Id =
Zj.Tax_Jurisdiction_Id
AND Zrn.Application_Id = 200
AND Zrn.Entity_Code = 'AP_INVOICES') Taxt
WHERE Glbook.Ledger_Id = Apid.Set_Of_Books_Id
AND Api.Batch_Id = Aba.Batch_Id(+)
and Api.source <> 'TCF INTERCOMPANY'
AND Glc.Code_Combination_Id =
Apid.Dist_Code_Combination_Id
/*AND Apid.Invoice_Id = Ail.Invoice_Id(+)
AND Apid.Distribution_Line_Number = Ail.Line_Number(+)*/
AND Api.Invoice_Id = Apid.Invoice_Id(+)
AND Pov.Vendor_Id = Api.Vendor_Id
AND Taxl.Invoice_Id(+) = Apid.Invoice_Id
AND Taxl.Org_Id(+) = Apid.Org_Id
AND Taxl.Trx_Line(+) = Apid.Invoice_Line_Number
AND Apid.Summary_Tax_Line_Id = Taxt.Summary_Tax_Line_Id(+)
AND Apid.Detail_Tax_Dist_Id = Taxt.Rec_Nrec_Tax_Dist_Id(+)
AND Shp.Shipment_Header_Id(+) = Rcv.Shipment_Header_Id
AND Rcv.Transaction_Id(+) = Apid.Rcv_Transaction_Id
AND Schd.Invoice_Id(+) = Api.Invoice_Id
AND Glrate.From_Currency(+) = Api.Invoice_Currency_Code
AND Trm.Term_Id(+) = Api.Terms_Id
AND Glrate.Conversion_Date(+) = Trunc(Api.Gl_Date)
AND Glrate.Conversion_Type(+) = 'Corporate'
AND Glrate.To_Currency(+) = 'USD'
AND Glrate2.From_Currency(+) = Glbook.Currency_Code
AND Glrate2.Conversion_Date(+) = Trunc(Api.Gl_Date)
AND Glrate2.Conversion_Type(+) = 'Corporate'
AND Glrate2.To_Currency(+) = 'USD'
AND Pvs.Vendor_Site_Id(+) = Api.Vendor_Site_Id
AND Ftt.Territory_Code(+) = Pvs.Country
AND Ftt.Language(+) = 'US'
AND Rqu.Person_Id(+) = Api.Requester_Id
/*
AND Trunc(SYSDATE) BETWEEN
Rqu.Person_Effective_Start_Date(+) AND
Rqu.Person_Effective_End_Date(+)
*/
AND Usr.Username(+) = Api.Created_By
AND ((case when (Apid.Amount <> 0 OR nvl(Apid.Base_Amount,0) <> 0)
then 'Y' else 'N' end ) = :P_IGN_ZERO_DIST_AMT
or 'All' = :P_IGN_ZERO_DIST_AMT|| 'All')
AND ( case when Nvl(Apid.Reversal_Flag, 'N') in (:P_REVERSAL_FLAG) then 1
when (COALESCE(NULL,:P_REVERSAL_FLAG) is NULL ) then 1
end = 1 )
) Tbl1,
(SELECT Pol.Item_Description Item_Description,
Pol.Line_Num Line_Num,
--Dst.Org_Id Org_Id,
Dst.Po_Header_Id Po_Header_Id,
Poh.Segment1 Po_Number,
Dst.Po_Distribution_Id Po_Distribution_Id,
--Rel.Release_Num Po_Release_Num,
Glc.Segment3 Global_Acct,
Glc.Segment4 Local_Acct,
Glc.Segment5 Dept,
Glc.Segment7 Intco,
Glc.Segment8 Project,
Round((Pll.Quantity * Pol.Unit_Price), 2) Po_Amount,
Dst.Amount_Billed Amount_Billed,
/*
CASE
WHEN Buy.First_Name IS NULL THEN
NULL
ELSE
Buy.First_Name || ' ' || Buy.Last_Name
END Buyer_Name,
CASE
WHEN Pap.First_Name IS NULL THEN
NULL
ELSE
Pap.First_Name || ' ' || Pap.Last_Name
END Deliver_To_Name,
*/
Buy.display_name Buyer_Name,
Pap.display_name Deliver_To_Name,
Msi.ITEM_NUMBER Item_Number,
Ctb.Segment1 Pur_Category_Number,
Ctl.Description Pur_Category_Description
--regexp_substr(ffv.cat, '[^~]+', 1, 2) parent_pur_categ,
--regexp_substr(ffv.cat, '[^~]+', 1, 1) parent_pur_cat_desc
FROM Po_Headers_All Poh,
Po_Lines_All Pol,
Po_Distributions_All Dst,
--Po_Releases_All Rel,
Po_Line_Locations_All Pll,
Gl_Code_Combinations Glc,
Xxon_Employee_View Pap,
Xxon_Employee_View Buy,
--Mtl_System_Items_b Msi,
egp_System_Items_v Msi,
--Mtl_Categories_b Ctb,
egp_categories_b Ctb,
--Mtl_Categories_Tl Ctl,
egp_categories_tl Ctl
--esg_nonitems enon,
/*
lateral (
SELECT
LISTAGG(ffv.description, ',') WITHIN GROUP(
ORDER BY
ffv.flex_value
)
|| '~'
||
LISTAGG(ffv.flex_value, ',') WITHIN GROUP(
ORDER BY
ffv.flex_value
)
cat
FROM
fnd_flex_value_sets ffvs,
fnd_flex_values_vl ffv
WHERE
ffvs.flex_value_set_name = 'XXON_PO_ITEM_CATEGORY'
AND ffv.flex_value_set_id = ffvs.flex_value_set_id
AND ffv.enabled_flag = 'Y'
AND ffv.summary_flag = 'Y'
AND substr(ffv.flex_value(+), 1, 3) = substr(ctb.Segment1, 1, 3)
) (+) ffv
*/
WHERE Poh.Po_Header_Id = Dst.Po_Header_Id
AND Pol.Po_Line_Id = Dst.Po_Line_Id
AND Pll.Line_Location_Id = Dst.Line_Location_Id
--AND Rel.Po_Release_Id(+) = Dst.Po_Release_Id
AND Glc.Code_Combination_Id = Dst.Code_Combination_Id
AND Pap.Person_Id(+) = Dst.Deliver_To_Person_Id
/*
AND Trunc(SYSDATE) BETWEEN
Pap.Person_Effective_Start_Date(+) AND
Pap.Person_Effective_End_Date(+)
AND Trunc(SYSDATE) BETWEEN Buy.Person_Effective_Start_Date AND
Buy.Person_Effective_End_Date
*/
AND Buy.Person_Id = Poh.Agent_Id
AND Ctb.Category_Id(+) = Pol.Category_Id
AND Ctb.Structure_Id(+) = 201
AND Ctl.Category_Id(+) = Pol.Category_Id
AND Ctl.Language(+) = 'US'
---and pol.attribute4 = enon.val
AND Msi.Organization_Id(+) = Pll.Ship_To_Organization_Id
AND Msi.Inventory_Item_Id(+) = Pol.Item_Id
AND ( case when msi.ITEM_NUMBER in (:P_ITEM_NUMBER) then 1
when (COALESCE(NULL,:P_ITEM_NUMBER) is NULL ) then 1
end = 1 )
) Tbl2,
(SELECT a.Invoice_Id,
Apc.Check_Date Payment_Date,
Apc.Check_Number Check_Number,
Apc.Vendor_Site_Code Pay_Site,
Apc.Check_id
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) Tbl4
WHERE
Tbl2.Po_Distribution_Id = Tbl1.Po_Distribution_Id
AND Tbl4.Invoice_Id(+) = Tbl1.Invoice_Id
) Al1
WHERE 1 = 1
AND ( case when Al1.Period in (:P_PERIOD) then 1
when (COALESCE(NULL,:P_PERIOD) is NULL ) then 1
end = 1 )
AND ( case when Al1.Company_Code in (:P_CO) then 1
when (COALESCE(NULL,:P_CO) is NULL ) then 1
end = 1 )
AND ( case when Al1.Org_Id in (:P_ORG_ID) then 1
when (COALESCE(NULL,:P_ORG_ID) is NULL ) then 1
end = 1 )
AND ( case when Al1.Vendor_Name in (:p_Vendor_Name) then 1
when (COALESCE(NULL,:p_Vendor_Name) is NULL ) then 1
end = 1 )
AND ( case when Al1.Vendor_Number in (:P_VENDOR_NUMBER) then 1
when (COALESCE(NULL,:P_VENDOR_NUMBER) is NULL ) then 1
end = 1 )
AND ( case when Al1.Source in (:P_SOURCE) then 1
when (COALESCE(NULL,:P_SOURCE) is NULL ) then 1
end = 1 )
AND ( case when Al1.Accounted in (:P_ACCOUNTED) then 1
when (COALESCE(NULL,:P_ACCOUNTED) is NULL ) then 1
end = 1 )
AND ( case when Al1.invoice_num in (:P_INVOICE_NUM) then 1
when (COALESCE(NULL,:P_INVOICE_NUM) is NULL ) then 1
end = 1 )
AND ( case when Al1.po_number in (:P_PO_NUMBER) then 1
when (COALESCE(NULL,:P_PO_NUMBER) is NULL ) then 1
end = 1 )
and ( trunc(Al1.Accounting_Date) >= nvl((:p_acct_date_from),trunc(Al1.Accounting_Date))
and trunc(Al1.Accounting_Date) <= nvl((:p_acct_date_to),trunc(Al1.Accounting_Date)) )
AND ( case when Al1.Account in (:P_PO_GAC) then 1
when (COALESCE(NULL,:P_PO_GAC) is NULL ) then 1
end = 1 )
AND ( case when Al1.Ap_Gac in (:P_AP_GAC) then 1
when (COALESCE(NULL,:P_AP_GAC) is NULL ) then 1
end = 1 )
GROUP BY Al1.Period,
Al1.Company_Code,
Al1.Ledger_Id,
Al1.Ledger_Name,
Al1.Vendor_Name,
Al1.Vendor_Number,
Al1.Vendor_Site,
Al1.Source,
Al1.Vendor_Type,
Al1.Invoice_Num,
To_Char(Al1.Invoice_Date, 'MM/DD/YYYY'),
To_Char(Al1.Inv_Creation_Date, 'MM/DD/YYYY'),
To_Char(Al1.Accounting_Date, 'MM/DD/YYYY'),
Al1.Doc_Sequence_Value,
Al1.Inv_Description,
Al1.Ap_Batch_Name,
Al1.Ap_Gac,
Al1.Ap_Lac,
Al1.Ap_Dept,
Al1.Ap_Ico,
Al1.Ap_Proj,
Al1.Inv_Currency_Code,
Al1.Account,
Al1.Local_Account,
Al1.Dept,
Al1.Project,
Al1.Po_Number,
Al1.Site_Country,
Al1.Territory_Short_Name,
Al1.Line_Type_Lookup_Code,
/*Al1.Line_Type_Line,*/
Al1.Requester_Name,
Al1.Pur_Category_Number,
Al1.Pur_Category_Description,
--Al1.Parent_Pur_Categ,
--Al1.Parent_Pur_Cat_Desc,
--Al1.Po_Release_Num,
Al1.Po_Line_Num,
Al1.Item_Number,
Al1.Item_Description,
Al1.Buyer_Name,
Al1.Po_Amount,
Al1.Amount_Billed,
Al1.Po_Amt_Balance,
Al1.Tax_Code,
Al1.Tax_Regime,
Al1.Tax_Rate_Code,
Al1.Tax_Jurisdiction,
Al1.Tax_Rate,
/*Al1.Tax_Classification_Code,*/
Al1.Match_Type,
Al1.Deliver_To_Name,
Al1.Receipt_Num,
Al1.Inv_Created_Id,
Al1.Inv_Created_Name,
Al1.Reversal_Flag,
Al1.Accounted,
Al1.Inv_Status,
Al1.Payment_Number,
To_Char(Al1.Payment_Date, 'MM/DD/YYYY'),
---nvl(Al1.Pay_Site,Al1.remit_to_supplier_Site),
Al1.Pay_Site,
Al1.Payment_Terms,
To_Char(Al1.Due_Date, 'MM/DD/YYYY'),
al1.hold,
--al1.attribute8,
--al1.wf_approval_catg,
Al1.invoice_id,
Al1.Po_Distribution_Id
order by 10
No comments:
Post a Comment