AP Invoices Payment Report
WITH get_tax_type as (
SELECT Ivl.Invoice_Id, MAX(Ivl.Type_1099) Type_1099
FROM Ap_Invoice_Lines_All Ivl
WHERE Ivl.Line_Type_Lookup_Code = 'ITEM'
AND Ivl.Amount <> 0
group by Ivl.Invoice_Id
)
,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 l.Period,
l.Ledger_Name,
l.Co,
l.Vendor,
l.vendor_name_alt,
l.Vendor_Num,
l.Vendor_Site,
l.Pay_Flag,
l.Payment_Batch,
l.Doc_Num,
l.Doc_Category_Name,
l.Doc_Sequence_Name,
l.Invoice_Num,
l.Invoice_Currency_Code Inv_Cy_Cd,
l.Payment_Currency_Code Inv_Paid_Cy_Cd,
l.Currency Cy_Cd,
l.Amt_Paid,
SUM(l.Amt_Paid) Over(PARTITION BY l.Doc_Num ORDER BY l.Doc_Num) Total_Amt_Paid_Per_Doc_num,
l.Amt_Paid_Func_Cy_Cd,
l.Amt_Paid_Usd,
l.Cleared_Amount Cleared_Amt_per_Payment_Batch,
l.Cleared_Date,
l.Discount_Taken,
l.Inv_Amt total_inv_amt,
l.Invoice_Amt_Usd total_Invoice_Amt_Usd,
l.Inv_Amt_Func_Cy_Cd,
l.Inv_Dt,
l.Gl_Date Gl_Date_Inv_Hdr,
l.Inv_Desc,
l.Terms,
l.Terms_Date,
to_char(l.Payment_Date,'MM/DD/YYYY') Payment_Date,
l.Pmt_Creation_Date,
l.Pay_Group,
l.Due_Date,
l.Status,
l.Statement_Number,
l.Statement_Gl_Date,
l.Int_Bank_Name,
l.Int_Branch_Name,
l.Int_Bank_Account_Name,
l.Bank_Account,
l.Cash_Clearing_Acct,
l.Pay_Method,
l.Void_Date,
CASE when :P_DISPLAY_PO_NUM <> 'NO' THEN
(select segment1 from po_headers_all where PO_HEADER_ID = L.PO_HEADER_ID) ELSE NULL END
Po_Num,
--Regexp_Substr(l.All_Po_Num, '[^:]+', 1, 1) Po_Num,
replace(REGEXP_SUBSTR(All_Po_Num, '[^:]+', 1, 2),'@',NULL) Buyer,
replace(REGEXP_SUBSTR(All_Po_Num, '[^:]+', 1, 3),'@',NULL) Match_Type,
replace(REGEXP_SUBSTR(All_Po_Num, '[^:]+', 1, 4),'@',NULL) Match_Option,
replace(REGEXP_SUBSTR(All_Po_Num, '[^:]+', 1, 5),'@',NULL) line_num,
replace(REGEXP_SUBSTR(All_Po_Num, '[^:]+', 1, 6),'@',NULL) Release_Num,
replace(REGEXP_SUBSTR(All_Po_Num, '[^:]+', 1, 7),'@',NULL) Line_Type,
l.Tax_Reporting_Name,
l.Name_Control,
l.Verification_Date,
l.Organization_Type,
--l.Payee_Tax_Id,
l.Federal_Reportable,
l.Income_Tax_Type,
(SELECT CASE WHEN Type_1099='MISC7' THEN 'NEC1' ELSE Type_1099 END
FROM get_tax_type
where Invoice_Id = l.Invoice_Id) Iv_Ln_Inc_Tax_Type,
CASE WHEN l.Income_Tax_Type ='NEC1'
then 'Non-employee compensation for MISC7' else l.Income_Tax_Type_Descripion end Income_Tax_Type_Descripion ,
--l.Individual_1099,
l.State_Reportable,
l.Vendor_Type,
l.Address_Line1,
l.Address_Line2,
l.Address_Line3,
l.Address_Line4,
l.City,
l.State,
l.Zip_Code,
l.Country,
l.Tax_Rept_Site_Flag,
l.Tax_Reporting_Site,
l.Tax_Rpt_Address1,
l.Tax_Rpt_Address2,
l.Tax_Rpt_City,
l.Tax_Rpt_State,
l.Tax_Rpt_Zip,
l.Site_Status,
l.Org_Name,
l.bu_name,
l.Invoice_Id,
l.Int_Bank_Account_Num,
l.Pmt_Instruction_Reference,
l.Payment_Reference,
l.country_name,
l.process_type,
l.ext_bank_number,
l.ext_branch_number,
l.ext_bank_name,
l.payee_name,
l.ext_bank_acct_alt_name,
l.ext_bank_account_name,
l.payee_address_concat,
l.ext_bank_account,
l.internal_seq_number,
l.Paper_Document,
l.vendor_bank_country,
l.Attachements,
SYSDATE TIMESTAMP
FROM (SELECT Gps.Period_Name Period,
Gl.Name Ledger_Name,
Substr(Hrou.Name, 1, 3) Co,
Asu.Vendor_Name Vendor,
Asu.vendor_name_alt,
Asu.Segment1 Vendor_Num,
Asa.Vendor_Site_Code Vendor_Site,
Apc.Payment_Type_Flag Pay_Flag,
Apc.Checkrun_Name Payment_Batch,
Apc.Check_Number Doc_Num,
Fdsc.Name Doc_Category_Name,
Aia.Doc_Sequence_Value Doc_Sequence_Name,
Aia.Invoice_Num,
AIA.PO_HEADER_ID,
Aia.Payment_Currency_Code,
Aia.Invoice_Currency_Code,
Apc.Currency_Code Currency,
Nvl(Aipa.Amount, 0) Amt_Paid,
/* CASE
WHEN APC.Status_Lookup_Code = 'VOIDED'
THEN COALESCE(Aipa.Payment_Base_Amount, Aipa.Amount, 0)
ELSE COALESCE(Aipa.Payment_Base_Amount, Aipa.Amount, 0)
END AS Amt_Paid_Func_Cy_Cd, */
COALESCE(Aipa.Payment_Base_Amount, Aipa.Amount, 0)
Amt_Paid_Func_Cy_Cd,
CASE
WHEN Aia.Invoice_Currency_Code = 'USD' THEN
Aipa.Amount
WHEN (Aia.Invoice_Currency_Code <> 'USD') AND
(Gl.Currency_Code = 'USD') THEN
Aipa.Payment_Base_Amount
ELSE
Round((Aipa.Amount * Gdr.Conversion_Rate), 2)
END Amt_Paid_Usd,
Nvl(Apc.Cleared_Amount, 0) Cleared_Amount,
To_Char(Apc.Cleared_Date, 'MM/DD/YYYY') Cleared_Date,
Aipa.Discount_Taken,
Aia.Invoice_Amount Inv_Amt,
nvl(Aia.Base_Amount, Aia.Invoice_Amount) Inv_Amt_Func_Cy_Cd,
CASE
WHEN Aia.Invoice_Currency_Code = 'USD' THEN
Aia.Invoice_Amount
ELSE
Round((Aia.Invoice_Amount * Gdr.Conversion_Rate), 2)
END AS Invoice_Amt_Usd,
To_Char(Aia.Invoice_Date, 'MM/DD/YYYY') Inv_Dt,
To_Char(Aia.Gl_Date, 'MM/DD/YYYY') Gl_Date,
To_Char(Apc.Void_Date, 'MM/DD/YYYY') Void_Date,
Aia.Description Inv_Desc,
Apt.Name Terms,
To_Char(Aia.Terms_Date, 'MM/DD/YYYY') Terms_Date,
trunc(Apc.Check_Date) Payment_Date,
To_Char(Apc.Creation_Date, 'MM/DD/YYYY') Pmt_Creation_Date,
Aia.Pay_Group_Lookup_Code Pay_Group,
To_Char(Pay.Due_Date, 'MM/DD/YYYY') Due_Date,
Apc.Status_Lookup_Code Status,
/*
(SELECT Statement_Number
FROM (SELECT DISTINCT Shd.Statement_Number
FROM Ce_Statement_Headers Shd,
Ce_Statement_Lines Sln,
Ce_Statement_Reconcils_All Src
WHERE Sln.Statement_Header_Id =
Shd.Statement_Header_Id
AND Src.Statement_Line_Id = Sln.Statement_Line_Id
AND Src.Reference_Id = Apc.Check_Id)
WHERE Rownum = 1) Statement_Number,
*/
null Statement_Number,
/*
(SELECT Gl_Date
FROM (SELECT DISTINCT Shd.Gl_Date
FROM Ce_Statement_Headers Shd,
Ce_Statement_Lines Sln,
Ce_Statement_Reconcils_All Src
WHERE Sln.Statement_Header_Id =
Shd.Statement_Header_Id
AND Src.Statement_Line_Id = Sln.Statement_Line_Id
AND Src.Reference_Id = Apc.Check_Id)
WHERE Rownum = 1) Statement_Gl_Date,
*/
null Statement_Gl_Date,
Acct_Dtl.Int_Bank_Name,
Acct_Dtl.Int_Branch_Name,
Acct_Dtl.Int_Bank_Account_Name,
Acct_Dtl.Int_Bank_Account_Num,
Apc.Bank_Account_Name Bank_Account,
Acct_Dtl.Concatenated_Segments Cash_Clearing_Acct,
supp_bank.vendor_bank_country,
Apc.Payment_Method_Code Pay_Method,
Ibp.Payment_Instruction_Id Pmt_Instruction_Reference,
Ibp.Payment_Reference_Number Payment_Reference,
ter.territory_short_name country_name,
ibp.process_type,
ibp.ext_bank_number,
ibp.ext_branch_number,
ibp.ext_bank_name,
ibp.payee_name,
ibp.ext_bank_account_alt_name ext_bank_acct_alt_name,
ibp.ext_bank_account_name,
decode(ibp.employee_payment_flag, 'N', ibp.payee_address_concat, '****') payee_address_concat,
decode(ibp.employee_payment_flag, 'N', ibp.ext_bank_account_number, '****') ext_bank_account,
Ibp.Paper_Document_Number Paper_Document,
Aia.tax_invoice_internal_seq internal_seq_number,
/*
(SELECT Listagg(distinct Po_Num, ', ') Within GROUP(ORDER BY Po_Num) || ':' ||
Listagg(distinct Coalesce(Buyer, '@'), ', ') Within GROUP(ORDER BY Po_Num) || ':' ||
Listagg(distinct Coalesce(Match_Type, '@'), ', ') Within GROUP(ORDER BY Po_Num) || ':' ||
Listagg(distinct Coalesce(Match_Option, '@'), ', ') Within GROUP(ORDER BY Po_Num) || ':' ||
Listagg(distinct Coalesce(to_char(line_num), '@'), ', ') Within GROUP(ORDER BY Po_Num,line_num) || ':' ||
Listagg(distinct Coalesce(to_char(Release_Num), '@'), ', ') Within GROUP(ORDER BY Po_Num,Release_Num) || ':' ||
Listagg(distinct Coalesce(Line_Type, '@'), ', ') Within GROUP(ORDER BY Po_Num) All_Po_Num
FROM (SELECT Pha.Segment1 Po_Num,
--Papf.First_Name || ' ' || Papf.Last_Name Buyer,
papf.display_name Buyer,
Ail.Match_Type,
PLC.Match_Option,
pla.line_num,
Plt.Line_Type,
Pra.Release_Num
FROM Ap_Invoice_Lines_All Ail,
PO_LINE_LOCATIONS_ALL PLC,
Po_Lines_All Pla,
Po_Line_Types_Tl plt,
Po_Releases_All Pra,
Po_Headers_All Pha,
Xxon_Employee_View Papf
WHERE Ail.Po_Header_Id = Pha.Po_Header_Id
AND Ail.Invoice_Id = Aia.Invoice_Id
AND PLC.LINE_LOCATION_ID(+) = AIL.PO_LINE_LOCATION_ID
AND Pha.Po_Header_Id = Pla.Po_Header_Id(+)
AND Pla.Po_Line_Id(+) = PLC.Po_Line_Id
AND Pla.Line_Type_Id = Plt.Line_Type_Id(+)
and Plt.language = 'US'
AND PLC.Po_Release_Id = Pra.Po_Release_Id(+)
AND Pha.Agent_Id = Papf.Person_Id(+)
group by Pha.Segment1,
--Papf.First_Name || ' ' || Papf.Last_Name Buyer,
papf.display_name ,
Ail.Match_Type,
PLC.Match_Option,
pla.line_num,
Plt.Line_Type,
Pra.Release_Num)) All_Po_Num,
*/
null All_Po_Num,
Asu.Tax_Reporting_Name Tax_Reporting_Name,
Asu.Name_Control Name_Control,
to_char(Asu.Tax_Verification_Date,'MM/DD/YYYY') Verification_Date,
Asu.Organization_Type_Lookup_Code Organization_Type,
--TRIM(Asu.Num_1099) Payee_Tax_Id,
Asu.STATE_REPORTABLE_FLAG State_Reportable,
Asu.Federal_Reportable_Flag Federal_Reportable,
CASE WHEN Asu.Type_1099='MISC7' THEN 'NEC1' ELSE ASU.Type_1099 END Income_Tax_Type,
(SELECT Itt.Description
FROM Ap_Income_Tax_Types Itt
WHERE Asu.Type_1099 = Itt.Income_Tax_Type) Income_Tax_Type_Descripion,
--Asu.Individual_1099 Individual_1099,
Asu.Vendor_Type_Lookup_Code Vendor_Type,
Asa.Address_Line1 Address_Line1,
Asa.Address_Line2 Address_Line2,
Asa.Address_Line3 Address_Line3,
Asa.Address_Line4 Address_Line4,
Asa.City City,
Nvl(Asa.State, Asa.Province) State,
Asa.Zip Zip_Code,
Asa.Country Country,
Asa.Tax_Reporting_Site_Flag Tax_Rept_Site_Flag,
Txr.Vendor_Site_Code Tax_Reporting_Site,
Txr.Address_Line1 Tax_Rpt_Address1,
Txr.Address_Line2 Tax_Rpt_Address2,
Txr.City Tax_Rpt_City,
Txr.State Tax_Rpt_State,
Txr.Zip Tax_Rpt_Zip,
CASE
WHEN Nvl(Asa.Inactive_Date, SYSDATE) < SYSDATE THEN
'Inactive'
ELSE
'Active'
END Site_Status,
--Aia.Org_Id,
Hrou.name Org_Name,
fabuv.bu_name,
Aipa.Invoice_Id,
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,
Nvl(Aipa.Reversal_Flag, 'N') Reversal_Flag
FROM Ap_Invoices_All Aia,
Ap_Terms Apt,
Ap_Invoice_Payments_All Aipa,
Ap_Checks_All Apc,
Iby_Payments_All Ibp,
fnd_territories_tl ter,
(SELECT Glcc.Concatenated_Segments,
Cba.Bank_Account_Name,
Cba.Bank_Account_Id,
Bnk.Party_Name Int_Bank_Name,
Brn.Party_Name Int_Branch_Name,
Cba.Bank_Account_Name Int_Bank_Account_Name,
Cba.Bank_Account_Num Int_Bank_Account_Num
FROM Ce_Bank_Accounts Cba,
Gl_Code_Combinations Glcc,
Hz_Parties Bnk,
Hz_Parties Brn
WHERE Cba.Cash_Clearing_Ccid = Glcc.Code_Combination_Id
AND Bnk.Party_Id = Cba.Bank_Id
AND Brn.Party_Id = Cba.Bank_Branch_Id
AND ( case when Bnk.Party_Name in (:p_int_bank) then 1
when (COALESCE(NULL,:p_int_bank) is NULL ) then 1
end = 1 )
) Acct_Dtl,
(SELECT Schd.Due_Date Due_Date,
Trunc(SYSDATE) - Schd.Due_Date Days_Due,
Api.Invoice_Id
FROM Ap_Invoices_All Api,
(SELECT MIN(Due_Date) Due_Date, Invoice_Id
FROM Ap_Payment_Schedules_All
GROUP BY Invoice_Id) Schd
WHERE Api.Invoice_Id = Schd.Invoice_Id) Pay,
(SELECT
ter.territory_short_name vendor_bank_country,
site_supp.party_site_id,
asa.vendor_site_id
FROM
hz_party_sites site_supp,
hz_parties prs,
fnd_territories_tl ter,
POZ_SUPPLIER_SITES_V asa
WHERE
prs.party_id = site_supp.party_id
AND site_supp.party_site_id = asa.party_site_id
AND ter.territory_code = prs.country
AND ter.language = 'US') supp_bank,
Gl_Period_Statuses Gps,
Gl_Ledgers Gl,
poz_suppliers_v Asu,
POZ_SUPPLIER_SITES_V Asa,
POZ_SUPPLIER_SITES_V Txr,
Gl_Daily_Rates Gdr,
Hr_Operating_Units Hrou,
Fnd_Doc_Sequence_Categories Fdsc,
Ce_Bank_Acct_Uses_All Cbaua,
fun_all_business_units_v fabuv
WHERE Apc.Check_Id = Aipa.Check_Id
and aipa.REVERSAL_INV_PMT_ID is NULL ---Added by Dhiraj on 16-jul-25 (to get positive amount line against voided payment )
and Aia.Source <> 'TCF INTERCOMPANY'
AND Apt.Term_Id(+) = Aia.Terms_Id
AND Aia.Invoice_Id = Aipa.Invoice_Id
AND Gps.Application_Id = 200
AND Gps.Period_Name = Aipa.Period_Name
AND Gps.Set_Of_Books_Id = Aipa.Set_Of_Books_Id
AND Cbaua.Bank_Acct_Use_Id = Apc.Ce_Bank_Acct_Use_Id
AND Acct_Dtl.Bank_Account_Id(+) = Cbaua.Bank_Account_Id
AND Apc.Org_Id = Ibp.Org_Id(+)
AND Apc.Payment_Id = Ibp.Payment_Id(+)
AND ter.territory_code (+) = apc.country
AND ter.language (+) = 'US'
AND Pay.Invoice_Id(+) = Aia.Invoice_Id
AND Gl.Ledger_Id = Aipa.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 Txr.Tax_Reporting_Site_Flag(+) = 'Y'
AND Txr.Vendor_Id(+) = Asa.Vendor_Id
AND Txr.PRC_BU_ID(+) = Asa.PRC_BU_ID
AND supp_bank.party_site_id(+) = asa.party_site_id
AND supp_bank.vendor_site_id(+) = asa.vendor_site_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 Hrou.Organization_Id = Aia.Org_Id
and aia.org_id = fabuv.bu_id
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 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 ( 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 Apc.Checkrun_Name in (:p_checrun) then 1
when (COALESCE(NULL,:p_checrun) is NULL ) then 1
end = 1 )
AND ( case when Apc.Check_Number in (:p_check_num) then 1
when (COALESCE(NULL,:p_check_num) is NULL ) then 1
end = 1 )
AND ( case when Apc.Status_Lookup_Code in (:p_pay_status) then 1
when (COALESCE(NULL,:p_pay_status) is NULL ) then 1
end = 1 )
and ( trunc(Apc.Check_Date) >= nvl((:p_check_from_date),trunc(Apc.Check_Date))
and trunc(Apc.Check_Date) <= nvl((:p_check_to_date),trunc(Apc.Check_Date)) )
AND ( case when Apc.Currency_Code in (:p_currency_code) then 1
when (COALESCE(NULL,:p_currency_code) 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_Amount <> 0)
then 'Y' else 'N' end ) = :p_excl_zero_pay
or 'All' = :p_excl_zero_pay|| 'All')
AND ( case when Apc.Bank_Account_Name in (:p_bank_acc) then 1
when (COALESCE(NULL,:p_bank_acc) 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 (Nvl(Asu.Vendor_Type_Lookup_Code, 'XXX') != 'EMPLOYEE')
then 'Y' else 'N' end ) = :p_excl_emp
or 'All' = :p_excl_emp|| 'All')
AND ( case when Asa.Country in (:p_vendor_country) then 1
when (COALESCE(NULL,:p_vendor_country) is NULL ) then 1
end = 1 )
) l
WHERE 1 = 1
AND (l.Reversal_Flag = :p_reversal_flag
or 'All' = :p_reversal_flag|| 'All')
No comments:
Post a Comment