AP Invoices Status Report
WITH get_appr as (
SELECT His.Invoice_Id,
--His.Line_Number,
His.Response,
His.Approver_Id,
papf.display_name Approver_Name,
--His.Amount_Approved,
His.Approver_Comments,
to_char(His.Creation_Date,'MM/DD/YYYY') Notification_Date,
to_char(His.Last_Update_Date,'MM/DD/YYYY') Last_Update_Date,
--Alc1.Displayed_Field,
His.Iteration,
His.History_Type
--Alc2.Displayed_Field,
---1 APPROVER_ORDER_NUMBER
FROM Ap_Inv_Aprvl_Hist_All His,
--Ap_Lookup_Codes Alc1,
--Ap_Lookup_Codes Alc2,
(
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'
) Papf,
per_users pu
WHERE his.approver_id = pu.username(+)
AND pu.person_id = Papf.person_id(+)
and his.history_type = 'DOCUMENTAPPROVAL'
and his.response = 'APPROVED'
--AND Alc1.Lookup_Code(+) = His.Response
--AND Alc1.Lookup_Type(+) = 'AP_WFAPPROVAL_STATUS'
--AND Alc2.Lookup_Type(+) = 'AP_WFAPPROVAL_CONTEXT'
--AND Alc2.Lookup_Code(+) = His.History_Type
order by his.creation_date DESC
,his.last_update_date DESC
,his.object_version_number --desc fetch first 1 rows only--
/*
group by His.Invoice_Id,
--His.Line_Number,
His.Response,
His.Approver_Id,
papf.display_name ,
--His.Amount_Approved,
His.Approver_Comments,
His.Creation_Date,
His.Last_Update_Date,
--Alc1.Displayed_Field,
His.Iteration,
His.History_Type
*/
)
,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'
)
,PO_Info as (SELECT Aida.Invoice_Id, Poh.Segment1 Po_Num,
--Papf.First_Name || ' ' || Papf.Last_Name Deliver_to_Person,
papf.display_name Deliver_to_Person
--pra.release_num
FROM Po_Headers_All Poh,
Po_Lines_All Pol,
Po_Distributions_All Pda,
Ap_Invoice_Distributions_All Aida,
--po_releases_all pra,
Xxon_Employee_View Papf
WHERE Poh.Po_Header_Id = Pol.Po_Header_Id
AND Poh.Po_Header_Id = Pda.Po_Header_Id
AND Pda.Po_Line_Id = Pol.Po_Line_Id
--and pra.po_release_id(+) = pda.po_release_id
AND Pda.Po_Distribution_Id = Aida.Po_Distribution_Id
/*
AND Trunc(SYSDATE) BETWEEN
Papf.Person_Effective_Start_Date(+) AND
Papf.Person_Effective_End_Date(+)
*/
AND Pda.deliver_to_person_id = Papf.Person_Id(+)
AND ( case when Poh.Segment1 in (:p_po_num) then 1
when (COALESCE(NULL,:p_po_num) is NULL ) then 1
end = 1 )
GROUP BY Aida.Invoice_Id,Poh.Segment1,
--Papf.First_Name || ' ' || Papf.Last_Name ,
papf.display_name
---pra.release_num
)
/*
Delg AS( SELECT --+ materialize --
Wrr.Role Approver,
Wrr.Action_Argument Delegated_To
FROM Wf_Routing_Rules Wrr
WHERE Wrr.Message_Type = 'APINVAPR'
AND SYSDATE <= Wrr.End_Date)
*/
SELECT a.Period,
a.Ledger_Name,
a.Org_Name,
a.Co,
a.Displayed_Field Invoice_Type,
a.Doc_Category_Name,
a.Doc_Sequence_Name,
a.Invoice_Num,
a.Inv_Amt,
a.Tax_Amt,
a.Invoice_Amt_Usd,
a.Invoice_Amt_Func,
a.Inv_Cy_Cd,
a.Amt_Paid,
a.Remaining_Amt,
a.Remaining_Amt_Usd,
a.Remaining_Amt_Func,
a.Pmt_Cy_Cd,
--Regexp_Substr(a.Po_Inf, '[^:]+', 1, 1) Po_Num,
--Regexp_Substr(a.Po_Inf, '[^:]+', 1, 2) Deliver_to_Person,
---Regexp_Substr(a.Po_Inf, '[^:]+', 1, 3) Po_Release_num,
a.Po_Num,
a.Deliver_to_Person,
a.Batch_Name,
a.Vendor,
a.vendor_name_alt,
a.Vendor_Num,
a.Vendor_Site,
a.Inv_Dt,
a.Inv_Creation_Dt,
a.Created_BY_ID,
a.Created_By,
a.Last_Update_Date,
a.Last_Update_BY_ID,
a.Last_Update_By,
a.Inv_Source,
a.Inv_Type,
a.Inv_Status,
a.Inv_Desc,
a.Terms,
a.Terms_Dt,
a.Tax_Country,
a.Requester,
a.Approval_Status,
--replace(REGEXP_SUBSTR(a.Approver_Inf, '[^~]+', 1, 1),'@',NULL) Approver_Name,
--replace(REGEXP_SUBSTR(a.Approver_Inf, '[^~]+', 1, 2),'@',NULL) Notification_Date,
--replace(REGEXP_SUBSTR(a.Approver_Inf, '[^~]+', 1, 3),'@',NULL) Response,
a.Approver_Name,
a.Notification_Date,
a.Response,
--Fus.Description Delegated_To,
null Delegated_To,
a.Accounted,
a.Pay_Group,
a.Due_Date,
a.Days_Due,
a.Aging_Bucket,
a.Doc_Num,
a.Payment_Date,
a.Payment_Method_Name,
a.Payment_Status,
a.Cancelled_Date,
a.payment_reason_code,
a.bank_charge_bearer,
/*
CASE WHEN a.Attachements = 'N' THEN
NULL
ELSE Coalesce(a.Attachements,
(SELECT Listagg(Chk, ',') Within GROUP(ORDER BY NULL) Po_Info
FROM (SELECT CASE WHEN Fd.Url LIKE '%' || '/' || a.Invoice_Num || '.html' THEN
'Y'
ELSE
'N'
END Chk
FROM Fnd_Documents Fd,
Fnd_Attached_Documents Fad
WHERE Fad.Document_Id = Fd.Document_Id
AND Fad.Entity_Name = 'AP_INVOICES_ALL'
AND Fd.Url IS NOT NULL
AND Fad.Pk1_Value =
To_Char(a.Invoice_Id))))
END Url_Inv_Chk,
*/
null Url_Inv_Chk,
CASE WHEN a.Attachements IS NULL THEN
'Y'
ELSE
a.Attachements
END Attachements,
a.Invoice_Id,
a.Pay_Creation_Date,
a.attribute3 uuid,
a.bill_of_lading,
a.packing_slip,
--apscan.scandatum apro_scanned_date,
null apro_scanned_date,
to_char(SYSDATE, 'mm/dd/yyyy hh:mi:ss AM') TIMESTAMP
FROM (SELECT --COUNT(Aia.Invoice_Num) invoice_count
Gps.Period_Name Period,
Gl.Name Ledger_Name,
Substr(Hrou.Name, 1, 3) Co,
alc.Displayed_Field,
Hrou.Name Org_Name,
Aia.Invoice_Type_Lookup_Code,
Fdsc.Name Doc_Category_Name,
Aia.Doc_Sequence_Value Doc_Sequence_Name,
Aia.Invoice_Num Invoice_Num,
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,
nvl(aia.base_amount,Aia.Invoice_Amount) Invoice_Amt_Func,
Aia.Invoice_Currency_Code Inv_Cy_Cd,
Nvl(Aia.Amount_Paid, 0) Amt_Paid,
nvl(pay.Amount_Remaining,0) Remaining_Amt,
CASE
WHEN Aia.Invoice_Currency_Code = 'USD' THEN
nvl(pay.Amount_Remaining,0)
ELSE
Round(((nvl(pay.Amount_Remaining,0)) * Gdr.Conversion_Rate), 2)
END Remaining_Amt_Usd,
CASE
WHEN Aia.Invoice_Currency_Code = Gl.Currency_Code THEN
nvl(pay.Amount_Remaining,0)
ELSE
case when Aia.base_amount is null then
Round((nvl(pay.Amount_Remaining,0) * Gdr2.Conversion_Rate), 2)
else
Round((nvl(pay.Amount_Remaining,0) * Aia.exchange_rate), 2)
end
END Remaining_Amt_Func,
Aia.Payment_Currency_Code Pmt_Cy_Cd,
Decode(:P_display_PO_num,'YES',( select Po_Num from PO_Info
where Invoice_Id = Aia.Invoice_Id
and rownum = 1
),'') Po_Num,
( select Deliver_to_Person from PO_Info
where Invoice_Id = Aia.Invoice_Id
and rownum = 1
) Deliver_to_Person,
Aba.Batch_Name,
Asu.Vendor_Name Vendor,
Asu.vendor_name_alt,
Asu.Segment1 Vendor_Num,
Asa.Vendor_Site_Code Vendor_Site,
DECODE(Asa.bank_charge_bearer,'I','ON pay','Supplier Pay') bank_charge,
To_Char(Aia.Invoice_Date, 'MM/DD/YYYY') Inv_Dt,
To_Char(Aia.Creation_Date, 'MM/DD/YYYY') Inv_Creation_Dt,
Fu.username Created_BY_ID,
Fu.display_name Created_By,
To_Char(Aia.Last_Update_Date, 'MM/DD/YYYY') Last_Update_Date,
Fu2.username Last_Update_BY_ID,
Fu2.display_name Last_Update_By,
Aia.Source Inv_Source,
Aia.Invoice_Type_Lookup_Code Inv_Type,
/*
Decode((SELECT Xxon_Call_Ap_Invoices_Utl_Pkg.Get_Approval_Status(Aia.Invoice_Id,
Aia.Invoice_Amount,
Aia.Payment_Status_Flag,
Aia.Invoice_Type_Lookup_Code) FROM dual),
'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,
*/
Decode(ap_invoices_pkg.get_approval_status(Aia.Invoice_Id,
Aia.Invoice_Amount,
Aia.Payment_Status_Flag,
Aia.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,
Aia.Description Inv_Desc,
Apt.Name Terms,
To_Char(Aia.Terms_Date, 'MM/DD/YYYY') Terms_Dt,
Aia.Taxation_Country Tax_Country,
Fu1.display_name Requester,
Aia.Wfapproval_Status Approval_Status,
--(SELECT get_appr(Aia.Invoice_id) FROM dual) Approver_Inf,
(select approver_name from get_appr where invoice_id = aia.invoice_id
and rownum = 1) approver_name,
(select notification_date from get_appr where invoice_id = aia.invoice_id
and rownum = 1) notification_date,
(select response from get_appr where invoice_id = aia.invoice_id
and rownum = 1) response,
/*
Decode((SELECT Xxon_Call_Ap_Invoices_Utl_Pkg.Get_Posting_Status(Aia.Invoice_Id) FROM dual),
'S',
'Selected',
'P',
'Partial',
'N',
'No',
'Y',
'Yes',
NULL) Accounted,
*/
Decode((SELECT AP_INVOICES_PKG.GET_POSTING_STATUS(Aia.Invoice_Id) FROM dual),
'S',
'Selected',
'P',
'Partial',
'N',
'No',
'Y',
'Yes',
NULL) Accounted,
Aia.Pay_Group_Lookup_Code Pay_Group,
To_Char(Pay.Due_Date, 'MM/DD/YYYY') Due_Date,
Trunc(SYSDATE) - Pay.Due_Date Days_Due,
CASE
WHEN Trunc(SYSDATE) - (Pay.Due_Date) <= 0 THEN
'DUE_0'
WHEN Trunc(SYSDATE) - (Pay.Due_Date) > 0 AND
Trunc(SYSDATE) - (Pay.Due_Date) <= 30 THEN
'DUE_1_30'
WHEN Trunc(SYSDATE) - (Pay.Due_Date) > 30 AND
Trunc(SYSDATE) - (Pay.Due_Date) <= 60 THEN
'DUE_31_60'
WHEN Trunc(SYSDATE) - (Pay.Due_Date) > 60 AND
Trunc(SYSDATE) - (Pay.Due_Date) <= 90 THEN
'DUE_61_90'
WHEN Trunc(SYSDATE) - (Pay.Due_Date) > 90 THEN
'DUE_OVR_90'
ELSE
NULL
END Aging_Bucket,
Payinf.Doc_Num,
--To_date(Payinf.Payment_Date,'MM/DD/YYYY') Payment_Date,
Payinf.Payment_Date Payment_Date,
Iby.Payment_Method_Name,
Aia.Payment_Status_Flag Payment_Status,
-- To_Date(Aia.Cancelled_Date,'MM/DD/YYYY') Cancelled_Date,
Aia.Cancelled_Date Cancelled_Date,
Aia.bank_charge_bearer,
Aia.payment_reason_code,
CASE
WHEN EXISTS
(SELECT 1
FROM Fnd_Attached_Documents Fad
WHERE Fad.Entity_Name = 'AP_INVOICES_ALL'
AND Fad.Pk1_Value = To_Char(Aia.Invoice_Id)) THEN
NULL
ELSE
'N'
END Attachements,
Aia.Invoice_Id,
--To_Date(Payinf.Pay_CReation_Date,'MM/DD/YYYY') Pay_CReation_Date,
Payinf.Pay_CReation_Date Pay_CReation_Date,
rshinfo.packing_slip, ---ANNL 08-19-24
rshinfo.bill_of_lading,
Aia.attribute3 ---end ANNL 08-19-24
FROM Gl_Ledgers Gl,
Gl_Periods Gps,
Ap_Terms Apt,
usr Fu,
usr Fu2,
(SELECT Person_Id, Full_Name, display_name
FROM Xxon_Employee_View
--WHERE Trunc(SYSDATE) BETWEEN Person_Effective_Start_Date AND Person_Effective_End_Date
) Fu1,
Ap_Invoices_All Aia,
poz_suppliers_v Asu,
POZ_SUPPLIER_SITES_V Asa,
Gl_Daily_Rates Gdr,
Gl_Daily_Rates Gdr2,
Fnd_Doc_Sequence_Categories Fdsc,
(SELECT Aba.Batch_Name, Aba.Batch_Id
FROM Ap_Batches_All Aba) Aba,
Hr_Operating_Units Hrou,
(SELECT MIN(Due_Date) Due_Date,
Invoice_Id,
SUM(nvl(Amount_Remaining,0)) Amount_Remaining
FROM Ap_Payment_Schedules_All
where ( trunc(Due_Date) >= nvl((:p_pay_from_date),trunc(Due_Date))
and trunc(Due_Date) <= nvl((:p_pay_to_date),trunc(Due_Date)) )
GROUP BY Invoice_Id) Pay,
Iby_Payment_Methods_Vl Iby,
(SELECT
Displayed_Field,
Lookup_Code
FROM Ap_Lookup_Codes
WHERE Lookup_Type = 'INVOICE TYPE'
AND ( case when Displayed_Field in (:p_inv_type) then 1
when (COALESCE(NULL,:p_inv_type) is NULL ) then 1
end = 1 )
) alc,
(SELECT Aipa.Invoice_Id,
Listagg(Apc.Check_Number,
', ' ON Overflow Truncate '...') Within GROUP(ORDER BY Apc.Check_Date) Doc_Num,
Listagg(To_Char(Trunc(Apc.Check_Date),
'mm/dd/yyyy'),
', ' ON Overflow Truncate '...') Within GROUP(ORDER BY Apc.Check_Date) Payment_Date,
Listagg(To_Char(Trunc(Apc.Creation_Date),
'mm/dd/yyyy'),
', ' ON Overflow Truncate '...') Within GROUP(ORDER BY Apc.Creation_Date) Pay_Creation_Date
FROM Ap_Invoice_Payments_All Aipa,
Ap_Checks_All Apc
WHERE Apc.Check_Id = Aipa.Check_Id
GROUP BY Aipa.Invoice_Id) Payinf,
(
SELECT Rsh1.Invoice_id,Rcv1.Packing_Slip,
Rcv1.bill_of_lading
FROM Rcv_Shipment_Headers Rcv1,
Rcv_Transactions Rt1,
(
SELECT
Invd.Invoice_id, max(Invd.Rcv_Transaction_Id) Transaction_Id
FROM
Ap_Invoice_Distributions_All Invd,
Rcv_Transactions Rt,
Rcv_Shipment_Headers Rsh
WHERE
Invd.Rcv_Transaction_Id(+) = Rt.Transaction_Id
AND Rt.Shipment_Header_Id = Rsh.Shipment_Header_Id
AND Invd.Rcv_Transaction_Id IS NOT NULL
GROUP BY ( Invd.Invoice_id)
)Rsh1
Where Rsh1.Transaction_Id=Rt1.Transaction_Id
and Rt1.Shipment_Header_Id = Rcv1.Shipment_Header_Id
)rshinfo --ANNL 08-22-24
WHERE 1=1
and Hrou.Organization_Id = Aia.Org_Id
and Aia.Source <> 'TCF INTERCOMPANY'
AND Aia.Batch_Id = Aba.Batch_Id(+)
AND ( case when Aba.Batch_Name in (:p_inv_batch) then 1
when (COALESCE(NULL,:p_inv_batch) is NULL ) then 1
end = 1 )
AND Aia.Created_By = Fu.username(+)
AND Aia.Last_Updated_By = Fu2.username(+)
AND Apt.Term_Id(+) = Aia.Terms_Id
AND Fu1.Person_Id(+) = Aia.Requester_Id
AND Payinf.Invoice_Id(+) = Aia.Invoice_Id
AND Pay.Invoice_Id(+) = Aia.Invoice_Id
AND Gl.Ledger_Id = Aia.Set_Of_Books_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 Gdr2.From_Currency(+) = Aia.Invoice_Currency_Code
AND Gdr2.To_Currency(+) = Gl.Currency_Code
AND Gdr2.Conversion_Type(+) = 'Corporate'
AND Gdr2.Conversion_Date(+) = Aia.Gl_Date
AND Fdsc.Code(+) = Aia.Doc_Category_Code
AND Gps.Period_Set_Name = '4-4-5'
AND Aia.Gl_Date BETWEEN Gps.Start_Date AND Gps.End_Date
AND Aia.Vendor_Id = Asu.Vendor_Id(+)
AND Aia.Vendor_Site_Id = Asa.Vendor_Site_Id(+)
AND Asu.Vendor_Id = Asa.Vendor_Id(+)
AND Aia.Payment_Method_Code = Iby.Payment_Method_Code(+)
AND Alc.Lookup_Code = aia.Invoice_Type_Lookup_Code
AND rshinfo.Invoice_id(+)= Aia.Invoice_id --ANNL 08-19-24
-- AND Aia.Invoice_Id IN
-- (SELECT Aida.Invoice_Id
-- FROM Po_Headers_All Poh,
-- Po_Lines_All Pol,
-- Po_Distributions_All Pda,
-- Ap_Invoice_Distributions_All Aida
-- WHERE Poh.Po_Header_Id = Pol.Po_Header_Id
-- AND Poh.Po_Header_Id = Pda.Po_Header_Id
-- AND Pda.Po_Line_Id = Pol.Po_Line_Id
-- AND Pda.Po_Distribution_Id = Aida.Po_Distribution_Id
-- AND ( case when Poh.Segment1 in (:p_po_num) then 1
-- when (COALESCE(NULL,:p_po_num) is NULL ) then 1
-- end = 1 )
-- GROUP BY Aida.Invoice_Id)
AND (nvl(:P_hold,'X') <>'Y' OR ( EXISTS (SELECT 1
FROM Ap_Holds_All Aha
WHERE Aha.Invoice_Id = Aia.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 = Aia.Invoice_Id
UNION ALL
SELECT 1
FROM Dual
WHERE Aia.Payment_Status_Flag != 'Y'
AND Asa.Hold_All_Payments_Flag = 'Y'
AND Aia.Cancelled_Date IS NULL))
)
-- AND EXISTS (SELECT 1
-- FROM Ap_Holds_All Aha
-- WHERE Aha.Invoice_Id = Aia.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 = Aia.Invoice_Id
-- UNION ALL
-- SELECT 1
-- FROM Dual
-- WHERE Aia.Payment_Status_Flag != 'Y'
-- AND Asa.Hold_All_Payments_Flag = 'Y'
-- AND Aia.Cancelled_Date IS NULL)
AND ( case when Gl.Name in (:p_ledger) then 1
when (COALESCE(NULL,:p_ledger) is NULL ) then 1
end = 1 )
AND ((CASE
WHEN EXISTS
(SELECT 1
FROM Fnd_Attached_Documents Fad
WHERE Fad.Entity_Name = 'AP_INVOICES_ALL'
AND Fad.Pk1_Value = To_Char(Aia.Invoice_Id)) THEN
'Y'
ELSE
'N'
END ) = :p_attachment
or 'All' = :p_attachment|| 'All')
AND ( case when Gps.Period_Name in (:p_period) then 1
when (COALESCE(NULL,:p_period) is NULL ) then 1
end = 1 )
AND ( case when Substr(Hrou.Name, 1, 3) in (:p_co) then 1
when (COALESCE(NULL,:p_co) is NULL ) then 1
end = 1 )
AND ( case when Aia.Invoice_Num in (:p_inv_num) then 1
when (COALESCE(NULL,:p_inv_num) is NULL ) then 1
end = 1 )
and ( trunc(Aia.Invoice_Date) >= nvl((:p_inv_from_date),trunc(Aia.Invoice_Date))
and trunc(Aia.Invoice_Date) <= nvl((:p_inv_to_date),trunc(Aia.Invoice_Date)) )
AND ( case when Asu.Vendor_Name in (:p_vendor_name) then 1
when (COALESCE(NULL,:p_vendor_name) is NULL ) then 1
end = 1 )
AND ( case when Asu.Segment1 in (:p_vendor_num) then 1
when (COALESCE(NULL,:p_vendor_num) is NULL ) then 1
end = 1 )
AND ( case when Asa.Vendor_Site_Code in (:p_vendor_site) then 1
when (COALESCE(NULL,:p_vendor_site) is NULL ) then 1
end = 1 )
AND ( case when Aia.Wfapproval_Status in (:p_inv_appr_status) then 1
when (COALESCE(NULL,:p_inv_appr_status) is NULL ) then 1
end = 1 )
AND ( case when Aia.Payment_Status_Flag in (:p_pay_status) then 1
when (COALESCE(NULL,:p_pay_status) is NULL ) then 1
end = 1 )
AND ((case when (Aia.Invoice_Amount <> 0)
then 'Y' else 'N' end ) = :p_excl_zero_inv
or 'All' = :p_excl_zero_inv|| 'All')
AND ( case when fu.username in (:p_create_by) then 1
when (COALESCE(NULL,:p_create_by) is NULL ) then 1
end = 1 )
AND ( case when fu2.username in (:p_update_by) then 1
when (COALESCE(NULL,:p_update_by) is NULL ) then 1
end = 1 )
AND ( case when Aia.Pay_Group_Lookup_Code in (:p_pay_group) then 1
when (COALESCE(NULL,:p_pay_group) is NULL ) then 1
end = 1 )
AND ( case when Aia.Source in (:p_inv_source) then 1
when (COALESCE(NULL,:p_inv_source) is NULL ) then 1
end = 1 )
and ( trunc(Aia.Creation_Date) >= nvl((:p_create_from_date),trunc(Aia.Creation_Date))
and trunc(Aia.Creation_Date) <= nvl((:p_create_to_date),trunc(Aia.Creation_Date)) )
) a
--delg,
--fnd_user Fus,
--xxaimg_fakturen apscan
WHERE 1=1
--AND Delg.Approver(+) = replace(REGEXP_SUBSTR(a.Approver_Inf, '[^~]+', 1, 4),'@',NULL)
--AND Fus.User_Name(+) = Delg.Delegated_To
--and a.invoice_id = apscan.invoiceid(+)
AND ( case when a.Po_Num in (:p_po_num) then 1
when (COALESCE(NULL,:p_po_num) is NULL ) then 1
end = 1 )
AND ( case when a.Inv_Status in (:p_inv_status) then 1
when (COALESCE(NULL,:p_inv_status) is NULL ) then 1
end = 1 )
No comments:
Post a Comment