AP Invoice Journal Details Report
SELECT a.Period,
a.Acctg_Date,
a.Ledger_Id,
a.Ledger_Name,
a.Co,
a.Invoice_Number,
a.Inv_Line_Amount,
a.Line_Amt_Usd,
a.Inv_Line_Amt_Funct,
case when a.Recovery_Pct is not null and a.Recovery_Pct <> 0
then a.Inv_Line_Amount * (a.Recovery_Pct/100)
else null
end RECOVERABLE_BASE_AMT,
case when a.Recovery_Pct is not null and a.Recovery_Pct <> 0
then a.Inv_Line_Amount * ((100 - a.Recovery_Pct)/100)
else null
end NONRECOVERABLE_BASE_AMT,
case when a.Recovery_Pct is not null and a.Recovery_Pct <> 0
then a.Inv_Line_Amt_Funct * (a.Recovery_Pct/100)
else null
end RECOVERABLE_BASE_AMT_FUNC,
case when a.Recovery_Pct is not null and a.Recovery_Pct <> 0
then a.Inv_Line_Amt_Funct * ((100 - a.Recovery_Pct)/100)
else null
end NONRECOVERABLE_BASE_AMT_FUNC,
a.org_name,
a.Location,
a.Inv_Cy_Cd,
a.Tax_Amount,
a.Tax_Amt_Func,
a.Tax_Amt_Usd,
a.Taxable_Amt,
a.Taxable_Func_Amt,
a.Accounted_Flag,
a.Invoice_Type,
a.Vendor_Name,
a.Vendor_Number,
a.Vendor_Site,
a.Vendor_Country_Code,
a.Invoice_Date,
a.Inv_Creation_Date,
a.Match_Opt,
a.Payment_Date,
a.Payment_Number,
a.Gac,
a.Lac,
a.Dept,
a.Proj,
a.Inv_Account_Combination,
a.Po_Number,
a.Po_Line,
a.Po_Line_Description,
a.Po_Account_Combination,
a.Dist_Line,
a.Line_Type,
a.Invoice_Line,
a.Inv_Line_Description,
a.Tax_Code,
a.Vat_Registration,
a.Doc_Sequence,
a.Vat_Code,
a.Receipt_Date,
a.Receipt_Number,
a.Po_Release_Num,
replace(REGEXP_SUBSTR(a.Approver_Inf, '[^~]+', 1, 1),'@',NULL) Approver_Name,
replace(REGEXP_SUBSTR(a.Approver_Inf, '[^~]+', 1, 2),'@',NULL) Approval_Date,
a.tax_registration_number,
sysdate timestamp
from (select DISTINCT Aid.Period_Name Period,
hou.name org_name,
to_char(Aid.Accounting_Date,'YYYY/MM/DD') Acctg_Date,
Lgr.Ledger_Id,
Lgr.Name Ledger_Name,
Glcc.Segment1 Co,
Ai.Invoice_Num Invoice_Number,
Aid.Amount Inv_Line_Amount,
CASE
WHEN Ai.Invoice_Currency_Code = 'USD' THEN
Aid.Amount
WHEN ((Ai.Invoice_Currency_Code = 'USD') AND
(Rel.Target_Currency_Code = 'USD')) THEN
Aid.Base_Amount
ELSE
Aid.Amount / Glrate.Conversion_Rate
END Line_Amt_Usd,
Nvl(Aid.Base_Amount, Aid.Amount) Inv_Line_Amt_Funct,
Ai.Invoice_Currency_Code Inv_Cy_Cd,
Aid.Posted_Flag Accounted_Flag,
Ai.Invoice_Type_Lookup_Code Invoice_Type,
hp.party_name Vendor_Name,
Sup.Segment1 Vendor_Number,
Sups.Vendor_Site_Code Vendor_Site,
Sups.COUNTRY_OF_ORIGIN_CODE Vendor_Country_Code,
To_Char(Ai.Invoice_Date, 'MM/DD/YYYY') Invoice_Date,
TO_CHAR(Ai.Creation_Date,'MM/DD/YYYY') Inv_Creation_Date,
Decode(Pln.Match_Option,
'P',
'2-Way',
'R',
'3-Way',
Pln.Match_Option) Match_Opt,
to_char(Pmts.Accounting_Date,'YYYY/MM/DD') Payment_Date,
Pmts.Check_Number Payment_Number,
glcc.segment2 Location,
Glcc.Segment3 Gac,
Glcc.Segment4 Lac,
Glcc.Segment5 Dept,
Glcc.Segment8 Proj,
Glcc.Segment1 || '-' || Glcc.Segment4 codept,
Glcc.Concatenated_Segments Inv_Account_Combination,
Phd.Segment1 Po_Number,
Pol.Line_Num Po_Line,
Pol.Item_Description Po_Line_Description,
Glcd.Concatenated_Segments Po_Account_Combination,
Aid.Distribution_Line_Number Dist_Line,
Aid.Line_Type_Lookup_Code Line_Type,
Aid.Invoice_Line_Number Invoice_Line,
Aid.Description Inv_Line_Description,
Nvl(Abs(Aid.Taxable_Amount) * Sign(Aid.Amount), 0) Taxable_Amt,
Nvl(Abs(Aid.Taxable_Base_Amount) * Sign(Aid.Amount), 0) Taxable_Func_Amt,
Nvl(Aila.Tax_Classification_Code, Aila.Tax_Rate_Code) Tax_Code,
tax.Recovery_Pct,
Sup.Vat_Registration_Num Vat_Registration,
Ai.Doc_Sequence_Value Doc_Sequence,
--Ai.Vat_Code
null Vat_Code,
to_char(Rct.Transaction_Date,'YYYY/MM/DD') Receipt_Date,
Shp.Receipt_Num Receipt_Number,
--Rls.Release_Num
null Po_Release_Num,
--(SELECT Get_Appr(Ai.Invoice_Id) FROM Dual)
null Approver_Inf,
Taxl.Tax_Amt_Inv Tax_Amount,
Taxl.Tax_Amt_Func Tax_Amt_Func,
CASE
WHEN Ai.Invoice_Currency_Code = 'USD' THEN
Nvl(Taxl.Tax_Amt_Inv, 0)
WHEN ((Ai.Invoice_Currency_Code = 'USD') AND
(Rel.Target_Currency_Code = 'USD')) THEN
Nvl(Taxl.Tax_Amt_Func, 0)
ELSE
Nvl(Taxl.Tax_Amt_Inv, 0) / Glrate.Conversion_Rate
END Tax_Amt_Usd,
ai.attribute13 tax_registration_number from
(SELECT /*+ materialize */
Txc.Tax Tax_Code,
Txn.Tax_Full_Name Tax_Name,
Txc.Tax_Type_Code Tax_Type,
Txr.Tax_Rate_Code Tax_Rate_Code,
Txr.Tax_Regime_Code Tax_Regime,
Ter.Territory_Short_Name Country,
Txr.Percentage_Rate Percentage,
Rcv.Percentage_Rate Recovery_Pct,
Txr.Effective_From Effective_From,
Txr.Effective_To Effective_To,
Txr.Offset_Tax Offset_Tax_Code,
Txr.Default_Rec_Rate_Code Recovery_Code,
Glc.Concatenated_Segments Account_Combination,
to_char(Txc.Creation_Date,'YYYY/MM/DD') Creation_Date,
to_Char(Txc.Last_Update_Date,'YYYY/MM/DD') Last_Updt_Date,
Usr.user_id Last_Updt_Id,
Usr.username Last_Updt_Name,
Txc.Tax_Id Tax_Id,
glc.Code_Combination_Id Code_Combination_Id,
Txr.tax_rate_id tax_rate_id
FROM Zx_Taxes_b Txc,
Zx_Taxes_Tl Txn,
Zx_Rates_b Txr,
Zx_Rates_b Rcv,
Zx_Accounts Txa,
Zx_Regimes_b Rgm,
Gl_Code_Combinations Glc,
Fnd_Territories_Tl Ter,
per_users Usr
WHERE Txn.Tax_Id = Txc.Tax_Id
AND Txn.Language = 'US'
AND Txr.Tax = Txc.Tax
AND Txr.Rate_Type_Code = 'PERCENTAGE'
AND Rcv.Rate_Type_Code = 'RECOVERY'
AND Rcv.Tax = Txr.Tax
AND Txa.Tax_Account_Entity_Id = Txr.Tax_Rate_Id
AND Glc.Code_Combination_Id = Txa.Tax_Account_Ccid
AND Rgm.Tax_Regime_Code = Txr.Tax_Regime_Code
AND Ter.Language = 'US'
AND Ter.Territory_Code = Rgm.Country_Code
AND Usr.username = Txc.Last_Updated_By
AND Trunc(SYSDATE) BETWEEN Nvl(Txc.Effective_From, Trunc(SYSDATE)) AND
Nvl(Txc.Effective_To, Trunc(SYSDATE))
AND Trunc(SYSDATE) BETWEEN Nvl(Txr.Effective_From, Trunc(SYSDATE)) AND
Nvl(Txr.Effective_To, Trunc(SYSDATE))
AND Trunc(SYSDATE) BETWEEN Nvl(Rcv.Effective_From, Trunc(SYSDATE)) AND
Nvl(Rcv.Effective_To, Trunc(SYSDATE))
AND (((Txc.Tax = Txc.TAX) OR (Txc.TAX IS NULL)))
AND (Txr.Tax_Regime_Code = Txr.Tax_Regime_Code)
-- AND (Ter.Territory_Short_Name = Ter.Territory_Short_Name)
) tax, Ap_Invoice_Distributions_All Aid,Ap_Invoice_Lines_All aila
,Ap_Invoices_All Ai, Gl_Code_Combinations Glcc,
POZ_Suppliers Sup,
hz_parties hp,
POZ_Supplier_Sites_All_M Sups,
Po_Distributions_All Pod, Gl_Code_Combinations Glcd,
Po_Lines_All Pol,
Po_Line_Locations_All Pln,
Po_Headers_All Phd,
Rcv_Transactions Rct,
Rcv_Shipment_Headers Shp,
Gl_Ledgers Lgr,
Gl_Daily_Rates Glrate,
Gl_Ledger_Relationships Rel,
(SELECT Pmt.Invoice_Id,
MAX(Pmt.Accounting_Date) Accounting_Date,
MAX(Chk.Check_Number) Check_Number
FROM Ap_Invoice_Payments_All Pmt,
Ap_Checks_All Chk
WHERE Nvl(Pmt.Reversal_Flag, 'N') = 'N'
AND Chk.Check_Id(+) = Pmt.Check_Id
AND Chk.Check_Number = nvl(:P_CHECK_NUMBER,Chk.Check_Number)
--AND Pmt.Accounting_Date BETWEEN to_date(:P_FROM_ACCT_DATE, 'mm/dd/yyyy')
--AND to_date(:P_TO_ACCT_DATE, 'mm/dd/yyyy')
GROUP BY Pmt.Invoice_Id) Pmts,
(SELECT Zxl.Trx_Id zx1Invoice_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 1=1 --Zxl.Application_Id = 200
AND Zxl.Entity_Code = 'AP_INVOICES'
GROUP BY Zxl.Trx_Id,
Zxl.Internal_Organization_Id,
Zxl.Trx_Line_Number) Taxl,
HR_ALL_ORGANIZATION_UNITS HOU
where 1=1
--AND (Aid.Amount= 0 OR Nvl(Aid.Base_Amount, Aid.Amount) = 0)
AND (Aid.Amount= 0 OR Nvl(Aid.Base_Amount, Aid.Amount) = 0)
and aid.DIST_CODE_COMBINATION_ID = tax.Code_Combination_Id
and tax.TAX_RATE_CODE = aila.TAX_RATE_CODE
--and tax.TAX_ID = aila.TAX_CODE_ID
and aid.invoice_id = aila.invoice_id
and tax.tax_rate_id = aila.tax_rate_id
and aid.INVOICE_LINE_NUMBER = aila.LINE_NUMBER
and ai.invoice_id = aila.invoice_id
and aid.invoice_id = ai.invoice_id
--and aid.invoice_id = 300000560192856
and Glcc.Code_Combination_Id = Aid.Dist_Code_Combination_Id
AND Sup.Vendor_Id = Ai.Vendor_Id
and sup.party_id = hp.party_id
AND Pod.Po_Distribution_Id(+) = Aid.Po_Distribution_Id
AND Sups.Vendor_Site_Id = Ai.Vendor_Site_Id
AND Glcd.Code_Combination_Id(+) = Pod.Code_Combination_Id
AND Pod.Po_Distribution_Id(+) = Aid.Po_Distribution_Id
--AND Pln.Line_Location_Id(+) = Pod.Line_Location_Id
AND Phd.Po_Header_Id(+) = Pod.Po_Header_Id
AND Pol.Po_Line_Id(+) = Pod.Po_Line_Id
AND Pln.Line_Location_Id(+) = Pod.Line_Location_Id
AND Shp.Shipment_Header_Id(+) = Rct.Shipment_Header_Id
AND Rct.Transaction_Id(+) = Aid.Rcv_Transaction_Id
AND Lgr.Ledger_Id = Aid.Set_Of_Books_Id
AND Glrate.To_Currency(+) = Ai.Invoice_Currency_Code
AND Glrate.From_Currency(+) = 'USD'
AND Glrate.Conversion_Type(+) = 'Corporate'
AND Glrate.Conversion_Date(+) = Ai.Gl_Date
AND Rel.Primary_Ledger_Id(+) = Aid.Set_Of_Books_Id
AND Rel.Relationship_Type_Code(+)='SUBLEDGER'
AND Rel.Target_Ledger_Name(+)= Lgr.Name
AND Pmts.Invoice_Id(+) = Aid.Invoice_Id
AND Taxl.zx1Invoice_Id(+) = Aid.Invoice_Id
AND Taxl.Org_Id(+) = Aid.Org_Id
and hou.ORGANIZATION_id(+) = ai.org_id
AND Taxl.Trx_Line(+) = Aid.Invoice_Line_Number
--and aid.invoice_id = 300000560192856
AND ( case when aid.Period_Name in (:P_PERIOD_NAME) then 1
when (COALESCE(NULL,:P_PERIOD_NAME) is NULL ) then 1
end = 1 )
AND ( case when Lgr.Name in (:P_LEDGER_NAME) then 1
when (COALESCE(NULL,:P_LEDGER_NAME) is NULL ) then 1
end = 1 )
AND ( case when Glcc.Segment1 in (:P_COMPANY) then 1
when (COALESCE(NULL,:P_COMPANY) is NULL ) then 1
end = 1 )
AND ( case when hp.party_name in (:P_VENDOR_NAME) then 1
when (COALESCE(NULL,:P_VENDOR_NAME) is NULL ) then 1
end = 1 )
AND ( case when Sup.Segment1 in (:P_VENDOR_NUM) then 1
when (COALESCE(NULL,:P_VENDOR_NUM) is NULL ) then 1
end = 1 )
AND ( case when Ai.Invoice_Num in (:P_INVOICE_NUM) then 1
when (COALESCE(NULL,:P_INVOICE_NUM) is NULL ) then 1
end = 1 )
AND ( case when Phd.Segment1 in (:P_PO_NUMBER) then 1
when (COALESCE(NULL,:P_PO_NUMBER) is NULL ) then 1
end = 1 )
AND Rel.Application_Id(+) = 101
and ( case when hou.name in (:p_org_id) then 1
when (COALESCE(NULL,:p_org_id) is NULL ) then 1
end = 1 )
and ( case when pmts.Accounting_Date in (:p_payment_date) then 1
when (COALESCE(NULL,:p_payment_date) is NULL ) then 1
end = 1 )
and ( case when phd.Segment1 in (:P_PO_NUMBER) then 1
when (COALESCE(NULL,:P_PO_NUMBER) is NULL ) then 1
end = 1 )
and ( case when glcc.Segment3 in (:P_GLOBAL_ACCT) then 1
when (COALESCE(NULL,:P_GLOBAL_ACCT) is NULL ) then 1
end = 1 )
AND TRUNC(AID.Accounting_Date) BETWEEN NVL(:P_FROM_ACCT_DATE,TRUNC(AID.Accounting_Date)) AND NVL(:P_TO_ACCT_DATE,TRUNC(AID.Accounting_Date))
--AND pmts.Accounting_Date <= NVL(:p_payment_date,TRUNC(AID.Accounting_Date))
--and ai.invoice_num='CH_PA_OS'
-- AND Rel.Target_Ledger_Name = Lgr.Name
)a
--NL_PA_OS AP number
---AP Invoices Hold DMAP Invoices Hold Report
--AP_S145_AP_INV_STAT_PAY_AND_HOLD_V13_ModV1_HOLD---
--AP_INV_HOLD--
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'
)
,mn as (SELECT /*+ materialize */
a.Hold_Period,
a.Scheduled_Hold_Period,
a.Ledger_Name,
a.Vendor,
a.vendor_name_alt,
a.Vendor_Num,
a.Vendor_Site,
a.Co,
a.org_id,
a.Batch_Name,
a.Doc_Category_Name,
a.Doc_Sequence_Name,
a.Invoice_Num,
a.Inv_Cy_Cd,
a.Pmt_Cy_Cd,
a.Inv_Amt,
a.Invoice_Amt_Usd,
a.Inv_Dt,
a.Inv_Creation_Dt,
a.Inv_Created_By,
a.Inv_Source,
a.Inv_Type,
/*
Decode((SELECT Xxon_Call_Ap_Invoices_Pkg.Get_Approval_Status(a.Invoice_Id,
a.Inv_Amt,
a.Payment_Status_Flag,
a.Inv_Type) 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(a.Invoice_Id,
a.Inv_Amt,
a.Payment_Status_Flag,
a.Inv_Type),
'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,
a.Due_Date,
a.Sched_Pay_Hold,
a.Sched_Pay_Hold_Reason,
a.Hold,
a.Hold_Reason,
a.Held_By,
a.Hold_Date,
a.Release_By,
a.Release_Date,
a.Release_Code,
a.Release_Reason,
a.Supplier_Site_Hold,
a.Pay_Group,
a.Pmt_Terms,
Regexp_Substr(a.Po_Inf, '[^:]+', 1, 1) Match_To,
Regexp_Substr(a.Po_Inf, '[^:]+', 1, 2) Po_Num,
Regexp_Substr(a.Po_Inf, '[^:]+', 1, 3) Line_Num,
Regexp_Substr(a.Po_Inf, '[^:]+', 1, 4) Buyer,
Regexp_Substr(a.Po_Inf, '[^:]+', 1, 5) Deliver_to_Person,
NULL Receipt_Num,
NULL Rcv_Transaction_Type,
NULL Rcv_Qty,
--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,
a.Requester,
a.Approval_Status,
a.Attachements,
a.Invoice_Id
FROM (SELECT /*+ FULL(schd) PARALLEL(schd, 4) */
NULL Hold_Period,
Gps.Period_Name Scheduled_Hold_Period,
Gl.Name Ledger_Name,
Asu.Vendor_Name Vendor,
Asu.vendor_name_alt,
Asu.Segment1 Vendor_Num,
Asa.Vendor_Site_Code Vendor_Site,
Substr(Hrou.Name, 1, 3) Co,
aia.org_id,
Aba.Batch_Name,
Fdsc.Name Doc_Category_Name,
Aia.Doc_Sequence_Value Doc_Sequence_Name,
Aia.Invoice_Num,
Aia.Invoice_Currency_Code Inv_Cy_Cd,
Aia.Payment_Currency_Code Pmt_Cy_Cd,
Nvl(Aia.Invoice_Amount, 0) Inv_Amt,
Aia.Invoice_Amount,
CASE
WHEN Aia.Invoice_Currency_Code = 'USD' THEN
Aia.Invoice_Amount
ELSE
Round((Aia.Invoice_Amount * Gdr.Conversion_Rate), 2)
END Invoice_Amt_Usd,
to_char(Aia.invoice_date,'MM/DD/YYYY') Inv_Dt,
to_char(Aia.Creation_Date,'MM/DD/YYYY') Inv_Creation_Dt,
(SELECT F.display_name from usr f WHere 1=1 AND F.username = Aia.Created_By AND ROWNUM=1) Inv_Created_By,
Aia.Source Inv_Source,
Aia.Invoice_Type_Lookup_Code Inv_Type,
Aia.Payment_Status_Flag,
To_Char(Schd.Due_Date, 'MM/DD/YYYY') Due_Date,
Schd.Hold_Flag Sched_Pay_Hold,
Schd.Iby_Hold_Reason Sched_Pay_Hold_Reason,
NULL Hold,
NULL Hold_Reason,
NULL Held_By,
NULL Hold_Date,
NULL Release_By,
NULL Release_Date,
NULL Release_Code,
NULL Release_Reason,
NULL Supplier_Site_Hold,
Aia.Pay_Group_Lookup_Code Pay_Group,
Trm.Name Pmt_Terms,
(SELECT Listagg(distinct Match_To, ', ') Within GROUP(ORDER BY Po_Num, Line_Num, Match_To, Buyer) || ':' ||
Listagg(distinct Po_Num, ', ') Within GROUP(ORDER BY Po_Num, Line_Num, Match_To, Buyer) || ':' ||
Listagg(distinct Line_Num, ', ') Within GROUP(ORDER BY Po_Num, Line_Num, Match_To, Buyer) || ':' ||
Listagg(distinct Buyer, ', ') Within GROUP(ORDER BY Po_Num, Line_Num, Match_To, Buyer) || ':' ||
Listagg(distinct Deliver_to_Person, ', ') Within GROUP(ORDER BY Po_Num, Line_Num, Match_To, Buyer)
FROM (SELECT Decode(Poll.Match_Option,
'P',
'PO',
'R',
'Receipt',
' ') Match_To,
Pha.Segment1 Po_Num,
Pol.Line_Num,
--Papf.First_Name || ' ' || Papf.Last_Name Buyer,
Papf.display_name Buyer,
--Papf2.First_Name || ' ' || Papf2.Last_Name Deliver_to_Person
Papf2.display_name Deliver_to_Person
FROM Ap_Invoice_Distributions_All Aid,
Po_Distributions_All Pod,
Po_Headers_All Pha,
Po_Lines_All Pol,
Po_Line_Locations_All Poll,
Xxon_Employee_View Papf,
Xxon_Employee_View Papf2
WHERE Pod.Po_Distribution_Id = Aid.Po_Distribution_Id
AND Pha.Po_Header_Id = Pod.Po_Header_Id
AND Pha.Po_Header_Id = Pol.Po_Header_Id
AND Pol.Po_Line_Id = Pod.Po_Line_Id
AND Pha.Po_Header_Id = Poll.Po_Header_Id
AND Pol.Po_Line_Id = Poll.Po_Line_Id
AND Aid.Invoice_Id = Aia.Invoice_Id
/*
AND Trunc(SYSDATE) BETWEEN
Papf.Person_Effective_Start_Date(+) AND
Papf.Person_Effective_End_Date(+)
*/
AND Pha.Agent_Id = Papf.Person_Id(+)
/*
AND Trunc(SYSDATE) BETWEEN
Papf2.Person_Effective_Start_Date(+) AND
Papf2.Person_Effective_End_Date(+)
*/
AND Pod.deliver_to_person_id = Papf2.Person_Id(+)
GROUP BY Decode(Poll.Match_Option,
'P',
'PO',
'R',
'Receipt',
' '),
Pha.Segment1,
Pol.Line_Num,
--Papf.First_Name || ' ' || Papf.Last_Name Buyer,
Papf.display_name ,
--Papf2.First_Name || ' ' || Papf2.Last_Name Deliver_to_Person
Papf2.display_name
ORDER BY Pha.Po_Header_Id, Pol.Line_Num)) Po_Inf,
---(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,
(select Fu3.display_name from Xxon_Employee_View Fu3 where Fu3.Person_Id= Aia.Requester_Id and rownum = 1)Requester,
Aia.Wfapproval_Status Approval_Status,
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 Attachements,
Aia.Invoice_Id
FROM Ap_Invoices_All Aia,
Ap_Terms_Tl Trm,
Ap_Batches_All Aba,
Ap_Payment_Schedules_All Schd,
Gl_Periods Gps,
Hr_Operating_Units Hrou,
Gl_Ledgers Gl,
poz_suppliers_v Asu,
POZ_SUPPLIER_SITES_V Asa,
-- usr Fu,
/*
(SELECT Person_Id, Full_Name
FROM Xxon_Employee_View
WHERE Trunc(SYSDATE) BETWEEN Person_Effective_Start_Date AND
Person_Effective_End_Date
) Fu3,
*/
-- Xxon_Employee_View Fu3,
Gl_Daily_Rates Gdr,
Fnd_Doc_Sequence_Categories Fdsc
WHERE Trm.Term_Id(+) = Aia.Terms_Id
AND Trm.Language(+) = 'US'
and Aia.Source <> 'TCF INTERCOMPANY'
AND Schd.Hold_Flag = 'Y'
AND Aia.Invoice_Id = Schd.Invoice_Id
AND Gps.Period_Set_Name = '4-4-5'
AND Trunc(Aia.Invoice_Date) BETWEEN Gps.Start_Date AND
Gps.End_Date
AND Aia.Batch_Id = Aba.Batch_Id(+)
AND Hrou.Organization_Id = Aia.Org_Id
AND Gl.Ledger_Id = Aia.Set_Of_Books_Id
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.Created_By = Fu.username
-- AND Fu3.Person_Id(+) = Aia.Requester_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 Fdsc.Code(+) = Aia.Doc_Category_Code
AND ( case when Gl.Name in (:p_ledger) then 1
when (COALESCE(NULL,:p_ledger) 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 Gps.Period_Name in (:p_period) then 1
when (COALESCE(NULL,:p_period) 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 ( 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.Invoice_Type_Lookup_Code in (:p_Inv_Type) then 1
when (COALESCE(NULL,:p_Inv_Type) 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')
) a
WHERE 1=1
/*
AND ( case when a.Sched_Pay_Hold_Reason in (:p_Sch_py_Hold) then 1
when (COALESCE(NULL,:p_Sch_py_Hold) is NULL ) then 1
end = 1 )
*/
AND ( case when a.Hold in (:p_Hold) then 1
when (COALESCE(NULL,:p_Hold) is NULL ) then 1
end = 1 )
)
SELECT
/*+ PQ_CONCURRENT_UNION(@SET$1) */
l.*
FROM(/*Regular system Hold from hold table*/
SELECT a.Hold_Period,
a.Scheduled_Hold_Period,
a.Ledger_Name,
a.Vendor,
a.vendor_name_alt,
a.Vendor_Num,
a.Vendor_Site,
a.Co,
a.Batch_Name,
a.Doc_Category_Name,
a.Doc_Sequence_Name,
a.Invoice_Num,
a.Inv_Cy_Cd,
a.Pmt_Cy_Cd,
a.Inv_Amt,
a.Invoice_Amt_Usd,
a.Inv_Dt,
a.Inv_Creation_Dt,
a.Inv_Created_By,
a.Inv_Source,
a.Inv_Type,
a.Inv_Status,
a.Due_Date,
case when :p_only_Sched_Pay_Hold='Y' then a.Sched_Pay_Hold
else 'N' end Sched_Pay_Hold,
a.Sched_Pay_Hold_Reason,
a.Hold,
a.Hold_Reason,
a.Held_By,
a.Hold_Date,
a.Release_By,
a.Release_Date,
a.Release_Code,
a.Release_Reason,
a.Supplier_Site_Hold,
a.Pay_Group,
a.Pmt_Terms,
a.Match_To,
a.Po_Num,
a.Line_Num,
a.Buyer,
(
SELECT
/*
papf.first_name
|| ' '
|| papf.last_name
*/
papf.display_name
FROM
po_distributions_all pod,
xxon_employee_view papf
WHERE
--trunc(SYSDATE) BETWEEN papf.person_effective_start_date AND papf.person_effective_end_date
pod.deliver_to_person_id = papf.person_id
AND pod.line_location_id = a.line_location_id
AND ROWNUM = 1
) deliver_to_person,
a.Receipt_Num,
a.Rcv_Transaction_Type,
a.Rcv_Qty,
--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,
a.Requester,
a.Approval_Status,
a.Attachements,
a.Invoice_Id,
SYSDATE TIMESTAMP
FROM (SELECT Aha.Period_Name Hold_Period,
NULL Scheduled_Hold_Period,
Gl.Name Ledger_Name,
Asu.Vendor_Name Vendor,
Asu.vendor_name_alt,
Asu.Segment1 Vendor_Num,
Asa.Vendor_Site_Code Vendor_Site,
Substr(Hrou.Name, 1, 3) Co,
Aba.Batch_Name,
Fdsc.Name Doc_Category_Name,
Aia.Doc_Sequence_Value Doc_Sequence_Name,
Aia.Invoice_Num,
Aia.invoice_Currency_Code Inv_Cy_Cd,
Aia.Payment_Currency_Code Pmt_Cy_Cd,
Nvl(Aia.Invoice_Amount, 0) Inv_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,
to_char(Aia.invoice_date,'MM/DD/YYYY') Inv_Dt,
to_char(Aia.Creation_Date,'MM/DD/YYYY') Inv_Creation_Dt,
(SELECT F.display_name from usr f WHere 1=1 AND F.username = Aia.Created_By AND ROWNUM=1) Inv_Created_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,
TO_CHAR(apsa.due_date,'MM/DD/YYYY') Due_Date,
NULL Sched_Pay_Hold,
NULL Sched_Pay_Hold_Reason,
Aha.Hold_Lookup_Code Hold,
Aha.Hold_Reason,
Aha.Held_By,
to_char(Aha.Hold_Date,'MM/DD/YYYY') Hold_Date,
Aha.Release_By,
CASE
WHEN (Aha.Release_Reason IS NOT NULL OR
Aha.Release_Lookup_Code IS NOT NULL) THEN
to_char(Aha.Last_Update_Date,'MM/DD/YYYY')
ELSE
NULL
END Release_Date,
Aha.Release_Lookup_Code Release_Code,
Aha.Release_Reason,
NULL Supplier_Site_Hold,
Aia.Pay_Group_Lookup_Code Pay_Group,
Trm.Name Pmt_Terms,
Po.Match_To,
Po.Po_Num,
to_char(Po.Line_Num) Line_Num,
Po.Buyer,
Rcv.Receipt_Num,
Rcv.Rcv_Transaction_Type,
Rcv.Rcv_Qty,
--(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,
(select Fu3.display_name from Xxon_Employee_View Fu3 where Fu3.Person_Id= Aia.Requester_Id and rownum = 1)Requester,
Aia.Wfapproval_Status Approval_Status,
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 Attachements,
Aia.Invoice_Id,
po.Line_Location_Id
FROM Ap_Invoices_All Aia,
Ap_Terms_Tl Trm,
Ap_Batches_All Aba,
ap_payment_schedules_all apsa,
( /*
SELECT Aha.Invoice_Id,
Aha.Hold_Lookup_Code,
Aha.Release_Lookup_Code,
Aha.Hold_Reason,
Aha.Hold_Date,
CASE
WHEN Aha.Release_Lookup_Code IS NULL THEN
NULL
ELSE
Decode(Aha.Last_Updated_By,
5,
'System',
Fu2.Description)
END Release_By,
Aha.Release_Reason,
Aha.Last_Update_Date,
Aha.Hold_Id,
Decode(Aha.Held_By, 5, 'System', Fu1.display_name) Held_By,
Gps.Period_Name,
Aha.Line_Location_Id,
Aha.Rcv_Transaction_Id
FROM Ap_Holds_All Aha,
usr Fu1,
usr Fu2,
Gl_Periods Gps
WHERE Fu1.username = Aha.Held_By
AND Fu2.username = Aha.Last_Updated_By
AND Gps.Period_Set_Name = '4-4-5'
AND Trunc(Aha.Hold_Date) BETWEEN Gps.Start_Date AND
Gps.End_Date
AND ( case when Gps.Period_Year in (:p_period_year) then 1
when (COALESCE(NULL,:p_period_year) is NULL ) then 1
end = 1 )
AND ( case when Gps.Period_Name in (:p_period) then 1
when (COALESCE(NULL,:p_period) is NULL ) then 1
end = 1 )
AND Aha.Release_Lookup_Code IS NULL
UNION ALL
*/
SELECT Aha.Invoice_Id,
Aha.Hold_Lookup_Code,
Aha.Release_Lookup_Code,
Aha.Hold_Reason,
Aha.Hold_Date,
CASE
WHEN Aha.Release_Lookup_Code IS NULL THEN
NULL
ELSE
/*
Decode(Aha.Last_Updated_By,
5,
'System',
Fu2.display_name)
*/
Decode(Aha.Last_Updated_By, '5', 'System', (select display_name from usr
where username = to_char(Aha.Last_Updated_By)
and rownum = 1))
END Release_By,
Aha.Release_Reason,
Aha.Last_Update_Date,
Aha.Hold_Id,
--Decode(Aha.Held_By, 5, 'System', Fu1.Description) Held_By,
Decode(Aha.Held_By, '5', 'System', (select display_name from usr
where username = to_char(Aha.Held_By)
and rownum = 1)) Held_By,
--Aha.Held_By Held_By,
Gps.Period_Name,
Aha.Line_Location_Id,
Aha.Rcv_Transaction_Id
FROM Ap_Holds_All Aha,
--usr Fu1,
--usr Fu2,
Gl_Periods Gps
WHERE --Fu1.username = Aha.Held_By
Gps.Period_Set_Name = '4-4-5'
AND Trunc(Aha.Hold_Date) BETWEEN Gps.Start_Date AND
Gps.End_Date
AND ( case when Gps.Period_Year in (:p_period_year) then 1
when (COALESCE(NULL,:p_period_year) is NULL ) then 1
end = 1 )
AND ( case when Gps.Period_Name in (:p_period) then 1
when (COALESCE(NULL,:p_period) is NULL ) then 1
end = 1 )
--AND Fu2.username = Aha.Last_Updated_By
) Aha,
Hr_Operating_Units Hrou,
Gl_Ledgers Gl,
poz_suppliers_v Asu,
POZ_SUPPLIER_SITES_V Asa,
-- usr Fu,
-- (SELECT Person_Id, Full_Name, display_name
-- FROM Xxon_Employee_View
-- --WHERE Trunc(SYSDATE) BETWEEN Person_Effective_Start_Date AND Person_Effective_End_Date
-- ) Fu3,
Gl_Daily_Rates Gdr,
Fnd_Doc_Sequence_Categories Fdsc,
(SELECT Decode(Poll.Match_Option,
'P',
'PO',
'R',
'Receipt',
' ') Match_To,
Poh.Segment1 Po_Num,
Pol.Line_Num,
--Papf.First_Name || ' ' || Papf.Last_Name Buyer,
Papf.display_name Buyer,
Poll.Line_Location_Id
FROM Po_Headers_All Poh,
Po_Lines_All Pol,
Po_Line_Locations_All Poll,
Xxon_Employee_View Papf
WHERE Poh.Po_Header_Id = Poll.Po_Header_Id
/*
AND Trunc(SYSDATE) BETWEEN
Papf.Person_Effective_Start_Date AND
Papf.Person_Effective_End_Date
*/
AND Poh.Agent_Id = Papf.Person_Id
AND Poh.Po_Header_Id = Pol.Po_Header_Id
AND Pol.Po_Line_Id = Poll.Po_Line_Id) Po,
(SELECT Rhdr.Receipt_Num,
Rcv.Transaction_Type Rcv_Transaction_Type,
Rcv.Quantity Rcv_Qty,
Rcv.Transaction_Id
FROM Rcv_Transactions Rcv,
Rcv_Shipment_Headers Rhdr
WHERE Rcv.Shipment_Header_Id = Rhdr.Shipment_Header_Id) Rcv
WHERE Aha.Invoice_Id = Aia.Invoice_Id
AND aia.invoice_id=apsa.invoice_id
and Aia.Source <> 'TCF INTERCOMPANY'
AND Trm.Term_Id(+) = Aia.Terms_Id
AND Trm.Language(+) = 'US'
AND Aia.Batch_Id = Aba.Batch_Id(+)
AND Hrou.Organization_Id = Aia.Org_Id
AND Gl.Ledger_Id = Aia.Set_Of_Books_Id
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.Created_By = Fu.username
-- AND Fu3.Person_Id(+) = Aia.Requester_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 Fdsc.Code(+) = Aia.Doc_Category_Code
AND Aha.Line_Location_Id = Po.Line_Location_Id(+)
AND Rcv.Transaction_Id(+) = Aha.Rcv_Transaction_Id
ANd NVL(apsa.object_version_number,999) = (SELECT MAX(NVL(OBJECT_VERSION_NUMBER,999)) from ap_payment_schedules_all where invoice_id = apsa.invoice_id)
AND ( case when Gl.Name in (:p_ledger) then 1
when (COALESCE(NULL,:p_ledger) 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 Gps.Period_Name in (:p_period) then 1
when (COALESCE(NULL,:p_period) 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 ( 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.Invoice_Type_Lookup_Code in (:p_Inv_Type) then 1
when (COALESCE(NULL,:p_Inv_Type) 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 Aha.Release_Lookup_Code in (:p_release_code) then 1
when (COALESCE(NULL,:p_release_code) is NULL ) then 1
end = 1 )
AND (
CASE
WHEN :P_Priority_hold = 'Y' THEN
CASE
WHEN 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
) THEN 1
ELSE 0
END
WHEN :P_Priority_hold IS NULL THEN 1
ELSE 0
END = 1
)
) a
WHERE 1=1
/*
AND ( case when a.Sched_Pay_Hold_Reason in (:p_Sch_py_Hold) then 1
when (COALESCE(NULL,:p_Sch_py_Hold) is NULL ) then 1
end = 1 )
*/
AND ( case when a.Hold in (:p_Hold) then 1
when (COALESCE(NULL,:p_Hold) is NULL ) then 1
end = 1 )
AND ( case when a.Po_Num in (:p_po_num) then 1
when (COALESCE(NULL,:p_po_num) is NULL ) then 1
end = 1 )
UNION ALL
SELECT * FROM(
SELECT a.Hold_Period,
a.Scheduled_Hold_Period,
a.Ledger_Name,
a.Vendor,
a.vendor_name_alt,
a.Vendor_Num,
a.Vendor_Site,
a.Co,
a.Batch_Name,
a.Doc_Category_Name,
a.Doc_Sequence_Name,
a.Invoice_Num,
a.Inv_Cy_Cd,
a.Pmt_Cy_Cd,
a.Inv_Amt,
a.Invoice_Amt_Usd,
a.Inv_Dt,
a.Inv_Creation_Dt,
a.Inv_Created_By,
a.Inv_Source,
a.Inv_Type,
/*
Decode((SELECT Xxon_Call_Ap_Invoices_Utl_Pkg.Get_Approval_Status(a.Invoice_Id,
a.Inv_Amt,
a.Payment_Status_Flag,
a.Inv_Type) 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(a.Invoice_Id,
a.Inv_Amt,
a.Payment_Status_Flag,
a.Inv_Type),
'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,
a.Due_Date,
case when :p_only_Sched_Pay_Hold='Y' then a.Sched_Pay_Hold
else 'N' end Sched_Pay_Hold,
a.Sched_Pay_Hold_Reason,
a.Hold,
a.Hold_Reason,
a.Held_By,
a.Hold_Date,
a.Release_By,
a.Release_Date,
a.Release_Code,
a.Release_Reason,
a.Supplier_Site_Hold,
a.Pay_Group,
a.Pmt_Terms,
Regexp_Substr(a.Po_Inf, '[^:]+', 1, 1) Match_To,
Regexp_Substr(a.Po_Inf, '[^:]+', 1, 2) Po_Num,
Regexp_Substr(a.Po_Inf, '[^:]+', 1, 3) Line_Num,
Regexp_Substr(a.Po_Inf, '[^:]+', 1, 4) Buyer,
Regexp_Substr(a.Po_Inf, '[^:]+', 1, 5) Deliver_to_Person,
NULL Receipt_Num,
NULL Rcv_Transaction_Type,
NULL Rcv_Qty,
--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,
a.Requester,
a.Approval_Status,
a.Attachements,
a.Invoice_Id,
SYSDATE TIMESTAMP
FROM (SELECT NULL Hold_Period,
Gps.Period_Name Scheduled_Hold_Period,
Gl.Name Ledger_Name,
Asu.Vendor_Name Vendor,
Asu.vendor_name_alt,
Asu.Segment1 Vendor_Num,
Asa.Vendor_Site_Code Vendor_Site,
Substr(Hrou.Name, 1, 3) Co,
Aba.Batch_Name,
Fdsc.Name Doc_Category_Name,
Aia.Doc_Sequence_Value Doc_Sequence_Name,
Aia.Invoice_Num,
Aia.Invoice_Currency_Code Inv_Cy_Cd,
Aia.Payment_Currency_Code Pmt_Cy_Cd,
Nvl(Aia.Invoice_Amount, 0) Inv_Amt,
Aia.Invoice_Amount,
CASE
WHEN Aia.Invoice_Currency_Code = 'USD' THEN
Aia.Invoice_Amount
ELSE
Round((Aia.Invoice_Amount * Gdr.Conversion_Rate), 2)
END Invoice_Amt_Usd,
to_char(Aia.invoice_date,'MM/DD/YYYY') Inv_Dt,
to_char(Aia.Creation_Date,'MM/DD/YYYY') Inv_Creation_Dt,
(SELECT F.display_name from usr f WHere 1=1 AND F.username = Aia.Created_By AND ROWNUM=1) Inv_Created_By,
Aia.Source Inv_Source,
Aia.Invoice_Type_Lookup_Code Inv_Type,
Aia.Payment_Status_Flag,
TO_CHAR(apsa.due_date,'MM/DD/YYYY') Due_Date,
NULL Sched_Pay_Hold,
NULL Sched_Pay_Hold_Reason,
NULL Hold,
NULL Hold_Reason,
NULL Held_By,
NULL Hold_Date,
NULL Release_By,
NULL Release_Date,
NULL Release_Code,
NULL Release_Reason,
'Y' Supplier_Site_Hold,
Aia.Pay_Group_Lookup_Code Pay_Group,
Trm.Name Pmt_Terms,
(SELECT Listagg(Match_To, ', ') Within GROUP(ORDER BY Po_Num, Line_Num, Match_To, Buyer) || ':' ||
Listagg(Po_Num, ', ') Within GROUP(ORDER BY Po_Num, Line_Num, Match_To, Buyer) || ':' ||
Listagg(Line_Num, ', ') Within GROUP(ORDER BY Po_Num, Line_Num, Match_To, Buyer) || ':' ||
Listagg(Buyer, ', ') Within GROUP(ORDER BY Po_Num, Line_Num, Match_To, Buyer) || ':' ||
Listagg(Deliver_to_Person, ', ') Within GROUP(ORDER BY Po_Num, Line_Num, Match_To, Buyer)
FROM (SELECT Decode(Poll.Match_Option,
'P',
'PO',
'R',
'Receipt',
' ') Match_To,
Pha.Segment1 Po_Num,
Pol.Line_Num,
--Papf.First_Name || ' ' || Papf.Last_Name Buyer,
Papf.display_name Buyer,
--Papf2.First_Name || ' ' || Papf2.Last_Name Deliver_to_Person
Papf2.display_name Deliver_to_Person
FROM Ap_Invoice_Distributions_All Aid,
Po_Distributions_All Pod,
Po_Headers_All Pha,
Po_Lines_All Pol,
Po_Line_Locations_All Poll,
Xxon_Employee_View Papf,
Xxon_Employee_View Papf2
WHERE Pod.Po_Distribution_Id = Aid.Po_Distribution_Id
AND Pha.Po_Header_Id = Pod.Po_Header_Id
AND Pha.Po_Header_Id = Pol.Po_Header_Id
AND Pol.Po_Line_Id = Pod.Po_Line_Id
AND Pha.Po_Header_Id = Poll.Po_Header_Id
AND Pol.Po_Line_Id = Poll.Po_Line_Id
AND Aid.Invoice_Id = Aia.Invoice_Id
/*
AND Trunc(SYSDATE) BETWEEN
Papf.Person_Effective_Start_Date(+) AND
Papf.Person_Effective_End_Date(+)
*/
AND Pha.Agent_Id = Papf.Person_Id(+)
/*
AND Trunc(SYSDATE) BETWEEN
Papf2.Person_Effective_Start_Date(+) AND
Papf2.Person_Effective_End_Date(+)
*/
AND Pod.deliver_to_person_id = Papf2.Person_Id(+)
GROUP BY Decode(Poll.Match_Option,
'P',
'PO',
'R',
'Receipt',
' '),
Pha.Segment1,
Pol.Line_Num,
--Papf.First_Name || ' ' || Papf.Last_Name Buyer,
Papf.display_name ,
--Papf2.First_Name || ' ' || Papf2.Last_Name Deliver_to_Person
Papf2.display_name
ORDER BY Pha.Po_Header_Id, Pol.Line_Num)) Po_Inf,
--(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,
(select Fu3.display_name from Xxon_Employee_View Fu3 where Fu3.Person_Id= Aia.Requester_Id and rownum = 1)Requester,
Aia.Wfapproval_Status Approval_Status,
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 Attachements,
Aia.Invoice_Id
FROM Ap_Invoices_All Aia,
Ap_Terms_Tl Trm,
Ap_Batches_All Aba,
Gl_Periods Gps,
Hr_Operating_Units Hrou,
Gl_Ledgers Gl,
poz_suppliers_v Asu,
POZ_SUPPLIER_SITES_V Asa,
-- usr Fu,
-- (SELECT Person_Id, Full_Name, display_name
-- FROM Xxon_Employee_View
-- --WHERE Trunc(SYSDATE) BETWEEN Person_Effective_Start_Date AND Person_Effective_End_Date
-- ) Fu3,
Gl_Daily_Rates Gdr,
Fnd_Doc_Sequence_Categories Fdsc
,ap_payment_schedules_all apsa
WHERE Trm.Term_Id(+) = Aia.Terms_Id
AND Aia.invoice_id = Apsa.invoice_id
and Aia.Source <> 'TCF INTERCOMPANY'
AND Trm.Language(+) = 'US'
AND Aia.Payment_Status_Flag != 'Y'
AND Asa.Hold_All_Payments_Flag = 'Y'
AND Aia.Cancelled_Date IS NULL
AND Gps.Period_Set_Name = '4-4-5'
AND Trunc(Aia.Invoice_Date) BETWEEN Gps.Start_Date AND
Gps.End_Date
AND Aia.Batch_Id = Aba.Batch_Id(+)
AND Hrou.Organization_Id = Aia.Org_Id
AND Gl.Ledger_Id = Aia.Set_Of_Books_Id
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.Created_By = Fu.username
-- AND Fu3.Person_Id(+) = Aia.Requester_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 Fdsc.Code(+) = Aia.Doc_Category_Code
ANd NVL(apsa.object_version_number,999) = (SELECT MAX(NVL(OBJECT_VERSION_NUMBER,999)) from ap_payment_schedules_all where invoice_id = apsa.invoice_id)
AND ( case when Gl.Name in (:p_ledger) then 1
when (COALESCE(NULL,:p_ledger) 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 Gps.Period_Name in (:p_period) then 1
when (COALESCE(NULL,:p_period) 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 ( 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.Invoice_Type_Lookup_Code in (:p_Inv_Type) then 1
when (COALESCE(NULL,:p_Inv_Type) 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 :P_Priority_hold = 'Y' THEN
CASE
WHEN 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
) THEN 1
ELSE 0
END
WHEN :P_Priority_hold IS NULL THEN 1
ELSE 0
END = 1
)
/*
AND ( case when Aha.Release_Lookup_Code in (:p_release_code) then 1
when (COALESCE(NULL,:p_release_code) is NULL ) then 1
end = 1 )
*/
) a
WHERE 1=1
/*
AND ( case when a.Sched_Pay_Hold_Reason in (:p_Sch_py_Hold) then 1
when (COALESCE(NULL,:p_Sch_py_Hold) is NULL ) then 1
end = 1 )
*/
AND ( case when a.Hold in (:p_Hold) then 1
when (COALESCE(NULL,:p_Hold) is NULL ) then 1
end = 1 )
AND ( case when (Regexp_Substr(a.Po_Inf, '[^:]+', 1, 2)) in (:p_po_num) then 1
when (COALESCE(NULL,:p_po_num) is NULL ) then 1
end = 1 )
)
UNION all
SELECT
a.Hold_Period,
a.Scheduled_Hold_Period,
a.Ledger_Name,
a.Vendor,
a.vendor_name_alt,
a.Vendor_Num,
a.Vendor_Site,
Substr(Hrou.Name, 1, 3) Co,
a.Batch_Name,
a.Doc_Category_Name,
a.Doc_Sequence_Name,
a.Invoice_Num,
a.Inv_Cy_Cd,
a.Pmt_Cy_Cd,
a.Inv_Amt,
a.Invoice_Amt_Usd,
a.Inv_Dt,
a.Inv_Creation_Dt,
a.Inv_Created_By,
a.Inv_Source,
a.Inv_Type,
a.Inv_Status,
a.Due_Date,
-- DECODE(:p_only_Sched_Pay_Hold,'Y','Test',0) Sched_Pay_Hold,
/* (select case when :p_only_Sched_Pay_Hold ='Y' then 'Y' else null end from dual) Sched_Pay_Hold, */
-- CAST(
-- CASE
-- WHEN :p_only_Sched_Pay_Hold <> 'N' THEN a.Sched_Pay_Hold
-- ELSE a.Sched_Pay_Hold
-- END AS NUMBER(20)
-- ) AS
-- NULL Sched_Pay_Hold,
a.Sched_Pay_Hold,
a.Sched_Pay_Hold_Reason,
a.Hold,
a.Hold_Reason,
a.Held_By,
a.Hold_Date,
a.Release_By,
a.Release_Date,
a.Release_Code,
a.Release_Reason,
a.Supplier_Site_Hold,
a.Pay_Group,
a.Pmt_Terms,
a.Match_To,
a.Po_Num,
a.Line_Num,
a.Buyer,
a.Deliver_to_Person,
a.Receipt_Num,
a.Rcv_Transaction_Type,
a.Rcv_Qty,
a.Approver_Name,
a.Notification_Date,
a.Response,
a.Requester,
a.Approval_Status,
a.Attachements,
a.Invoice_Id,
sysdate timestamp
from mn a,
Hr_Operating_Units Hrou
where a.org_id = hrou.organization_id
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 a.Po_Num in (:p_po_num) then 1
when (COALESCE(NULL,:p_po_num) is NULL ) then 1
end = 1 )
AND ( CASE WHEN :P_Priority_hold='Y' THEN (SELECT 1 FROM DUAL)
ELSE 0 END =1)
)l
No comments:
Post a Comment