SQL Query to find active vendors, their active sites with details like tax ,address,payment ,bank accounts in Oracle Account Payables

SQL Query to find active vendors, their active sites with details like tax ,address,payment ,bank accounts in Oracle Account Payables

Developed for version 11.5.10

 

Imp Note:

• Extracting only data related active sites of active vendors
• Not extracting those active vendors not having any active sites
• Considering primary bank account of active site only
• Additional columns added- latest_invoice_number, latest_invoice_date and payment_date against that latest invoice details of thatactive vendor and site

--This query to find active vendors with active sites having bank account details
Select 
Pv.Vendor_Name,
Pv.Segment1 Vendor_Number ,
Nvl(Pv.Individual_1099,Pv.Num_1099) Taxpayer_Id,
pv.vat_registration_num tax_registration_num,
(Select Plc.Displayed_Field From Apps.Po_Lookup_Codes Plc Where Plc.Lookup_Type = 'VENDOR TYPE' And Plc.Lookup_Code=Pv.Vendor_Type_Lookup_Code) Type,
Pv.Invoice_Currency_Code Invoice_Currency,
Federal_Reportable_Flag Federal_Reportable,
State_Reportable_Flag State_Reportable,
Type_1099 Income_Tax_Type,
Pvsa.Vendor_Site_Code Site_Name,
pvsa.Vendor_Site_Code_Alt alternate_site_name,
Pvsa.Country,
Pvsa.Address_Line1,
Pvsa.Address_Line2,
Pvsa.Address_Line3,
Pvsa.Address_Line4,
pvsa.address_lines_alt,
Pvsa.City,
Pvsa.State,
Pvsa.Province,
Pvsa.County,
Pvsa.Zip Postal_Code,
Pvsa.Pay_Site_Flag ,
pvsa.primary_pay_site_flag ,
Pvsa.Purchasing_Site_Flag,
Pvsa.Rfq_Only_Site_Flag,
Pvsa.Pcard_Site_Flag procurement_card_flag,
Pvsa.Area_Code Phone_Area_Code,
Pvsa.Phone phone_number,
Pvsa.Fax_Area_Code,
Pvsa.Fax fax_number,
Pvsa.Email_Address,
(Select Concatenated_Segments From apps.Gl_Code_Combinations_Kfv Gcck Where Code_Combination_Id=Pvsa.Accts_Pay_Code_Combination_Id ) Liability_Account,
(Select Concatenated_Segments From apps.Gl_Code_Combinations_Kfv Gcck Where Code_Combination_Id=Pvsa.Prepay_Code_Combination_Id ) Prepaymernt_Account,
(Select Concatenated_Segments From Apps.Gl_Code_Combinations_Kfv Gcck Where Code_Combination_Id=Pvsa.Future_Dated_Payment_Ccid ) Future_Dated_Payement_Account,
(Select Name From Ap.Ap_Terms_Tl Where Term_Id=Pvsa.Terms_Id) Terms,
Pvsa.Pay_Group_Lookup_Code Pay_Group,
Pvsa.Payment_Priority,
pvsa.remittance_email,
Pvsa.Pay_Date_Basis_Lookup_Code Pay_Date_Basis,
pvsa.terms_date_basis,
Pvsa.Payment_Method_Lookup_Code Payment_Method,
Pvsa.Always_Take_Disc_Flag,
Pvsa.Exclude_Freight_From_Discount,
Pvsa.Exclusive_Payment_Flag,
pvsa.ATTENTION_AR_FLAG,
Pvsa.Invoice_Currency_Code Site_Invoice_Currency,
pvsa.payment_Currency_Code site_payment_Currency,
Apaa.Bank_Account_Name,
Apaa.Bank_Account_Num Bank_Account_Number,
Apaa.Currency_Code Bank_Account_Currency,
Apaua.Start_Date Effective_Date_From,
apaua.end_date effective_date_to,
Abb.Bank_Name,
abb.bank_number,
Abb.Bank_Branch_Name,
Abb.Bank_Num Branch_Number,
(Select Invoice_Num From Ap.Ap_Invoices_All Aia
Where Invoice_Id =(Select Max(Invoice_Id) From Ap.Ap_Invoices_All Aia1 Where Aia1.Vendor_Site_Id=Pvsa.Vendor_Site_Id ))latest_Invoice_Num,
(Select Invoice_Date From Ap.Ap_Invoices_All Aia
Where Invoice_Id =(Select Max(Invoice_Id) From Ap.Ap_Invoices_All Aia1 Where Aia1.Vendor_Site_Id=Pvsa.Vendor_Site_Id ))latest_Invoice_Date,
(Select Check_Date From Ap.Ap_Checks_All Where Check_Id=(
SELECT MAX(CHECK_ID) FROM AP.AP_INVOICE_PAYMENTS_ALL WHERE INVOICE_ID=(select max(invoice_id) from Ap.Ap_Invoices_All aia1 where aia1.Vendor_Site_Id=pvsa.Vendor_Site_Id )))PAYMENT_DATE
From Po.Po_Vendors Pv,
Po.Po_Vendor_Sites_All Pvsa,
Ap.Ap_Bank_Account_Uses_All Apaua,
Ap.Ap_Bank_Accounts_All Apaa,
Ap.Ap_Bank_Branches Abb
Where Pv.Vendor_Id=Pvsa.Vendor_Id
And Pvsa.Vendor_Site_Id=Apaua.Vendor_Site_Id
--and (apaua.end_Date Is Null Or apaua.end_Date > Trunc(Sysdate))
and apaua.primary_flag='Y'
And Apaua.External_Bank_Account_Id=Apaa.Bank_Account_Id
And Apaa.Bank_Branch_Id=Abb.Bank_Branch_Id
And pv.Enabled_Flag <>'N' 
And (pv.End_Date_Active Is Null Or Trunc(pv.End_Date_Active)>Trunc(Sysdate))
And (Pv.Start_Date_Active Is Null Or Trunc(Pv.Start_Date_Active)<Trunc(Sysdate))
And (Pvsa.Inactive_Date Is Null Or Pvsa.Inactive_Date > Trunc(Sysdate))
Union
--This query to find active vendors with active sites not having bank account details
Select 
Pv.Vendor_Name,
Pv.Segment1 Vendor_Number ,
nvl(Pv.individual_1099,Pv.num_1099) Taxpayer_id,
pv.vat_registration_num tax_registration_num,
(Select Plc.Displayed_Field From Apps.Po_Lookup_Codes Plc Where Plc.Lookup_Type = 'VENDOR TYPE' And Plc.Lookup_Code=Pv.Vendor_Type_Lookup_Code) Type,
Pv.Invoice_Currency_Code Invoice_Currency,
Federal_Reportable_Flag Federal_Reportable,
State_Reportable_Flag State_Reportable,
Type_1099 Income_Tax_Type,
Pvsa.Vendor_Site_Code Site_Name,
pvsa.Vendor_Site_Code_Alt alternate_site_name,
Pvsa.Country,
Pvsa.Address_Line1,
Pvsa.Address_Line2,
Pvsa.Address_Line3,
Pvsa.Address_Line4,
pvsa.address_lines_alt,
Pvsa.City,
Pvsa.State,
Pvsa.Province,
Pvsa.County,
Pvsa.Zip Postal_Code,
Pvsa.Pay_Site_Flag ,
pvsa.primary_pay_site_flag ,
Pvsa.Purchasing_Site_Flag,
Pvsa.Rfq_Only_Site_Flag,
Pvsa.Pcard_Site_Flag procurement_card_flag,
Pvsa.Area_Code Phone_Area_Code,
Pvsa.Phone phone_number,
Pvsa.Fax_Area_Code,
Pvsa.Fax fax_number,
Pvsa.Email_Address,
(Select Concatenated_Segments From apps.Gl_Code_Combinations_Kfv Gcck Where Code_Combination_Id=Pvsa.Accts_Pay_Code_Combination_Id ) Liability_Account,
(Select Concatenated_Segments From apps.Gl_Code_Combinations_Kfv Gcck Where Code_Combination_Id=Pvsa.Prepay_Code_Combination_Id ) Prepaymernt_Account,
(Select Concatenated_Segments From Apps.Gl_Code_Combinations_Kfv Gcck Where Code_Combination_Id=Pvsa.Future_Dated_Payment_Ccid ) Future_Dated_Payement_Account,
(Select Name From Ap.Ap_Terms_Tl Where Term_Id=Pvsa.Terms_Id) Terms,
Pvsa.Pay_Group_Lookup_Code Pay_Group,
Pvsa.Payment_Priority,
pvsa.remittance_email,
Pvsa.Pay_Date_Basis_Lookup_Code Pay_Date_Basis,
pvsa.terms_date_basis,
Pvsa.Payment_Method_Lookup_Code Payment_Method,
Pvsa.Always_Take_Disc_Flag,
Pvsa.Exclude_Freight_From_Discount,
Pvsa.Exclusive_Payment_Flag,
pvsa.ATTENTION_AR_FLAG,
Pvsa.Invoice_Currency_Code Site_Invoice_Currency,
pvsa.payment_Currency_Code site_payment_Currency,
null Bank_Account_Name,
null Bank_Account_Number,
null Bank_Account_Currency,
null Effective_Date_From,
null Effective_Date_To,
null Bank_Name,
null Bank_Number,
null Bank_Branch_Name,
null branch_number,
(Select Invoice_Num From Ap.Ap_Invoices_All Aia
Where Invoice_Id =(Select Max(Invoice_Id) From Ap.Ap_Invoices_All Aia1 Where Aia1.Vendor_Site_Id=Pvsa.Vendor_Site_Id ))Invoice_Num,
(Select Invoice_Date From Ap.Ap_Invoices_All Aia
Where Invoice_Id =(Select Max(Invoice_Id) From Ap.Ap_Invoices_All Aia1 Where Aia1.Vendor_Site_Id=Pvsa.Vendor_Site_Id ))Invoice_Date,
(Select Check_Date From Ap.Ap_Checks_All Where Check_Id=(
SELECT MAX(CHECK_ID) FROM AP.AP_INVOICE_PAYMENTS_ALL WHERE INVOICE_ID=(select max(invoice_id) from Ap.Ap_Invoices_All aia1 where aia1.Vendor_Site_Id=pvsa.Vendor_Site_Id )))PAYMENT_DATE
From Po.Po_Vendors Pv,
Po.Po_Vendor_Sites_All Pvsa
Where Pv.Vendor_Id=Pvsa.Vendor_Id
And pv.Enabled_Flag <>'N' 
And (pv.End_Date_Active Is Null Or Trunc(pv.End_Date_Active)>Trunc(Sysdate))
And (Pv.Start_Date_Active Is Null Or Trunc(Pv.Start_Date_Active)<Trunc(Sysdate))
And (Pvsa.Inactive_Date Is Null Or Pvsa.Inactive_Date > Trunc(Sysdate))
And  Not Exists(Select 1 From Ap.Ap_Bank_Account_Uses_All Apaua
Where Apaua.Vendor_Site_Id= Pvsa.Vendor_Site_Id
--And (Apaua.End_Date Is Null Or Apaua.End_Date > Trunc(Sysdate))
)
order by vendor_name;

Same above Query but instead of union used join-

Select 
Pv.Vendor_Name,
Pv.Segment1 Vendor_Number ,
Nvl(Pv.Individual_1099,Pv.Num_1099) Taxpayer_Id,
pv.vat_registration_num tax_registration_num,
(Select Plc.Displayed_Field From Apps.Po_Lookup_Codes Plc Where Plc.Lookup_Type = 'VENDOR TYPE' And Plc.Lookup_Code=Pv.Vendor_Type_Lookup_Code) Type,
Pv.Invoice_Currency_Code Invoice_Currency,
Federal_Reportable_Flag Federal_Reportable,
State_Reportable_Flag State_Reportable,
Type_1099 Income_Tax_Type,
Pvsa.Vendor_Site_Code Site_Name,
pvsa.Vendor_Site_Code_Alt alternate_site_name,
Pvsa.Country,
Pvsa.Address_Line1,
Pvsa.Address_Line2,
Pvsa.Address_Line3,
Pvsa.Address_Line4,
pvsa.address_lines_alt,
Pvsa.City,
Pvsa.State,
Pvsa.Province,
Pvsa.County,
Pvsa.Zip Postal_Code,
Pvsa.Pay_Site_Flag ,
pvsa.primary_pay_site_flag ,
Pvsa.Purchasing_Site_Flag,
Pvsa.Rfq_Only_Site_Flag,
Pvsa.Pcard_Site_Flag procurement_card_flag,
Pvsa.Area_Code Phone_Area_Code,
Pvsa.Phone phone_number,
Pvsa.Fax_Area_Code,
Pvsa.Fax fax_number,
Pvsa.Email_Address,
(Select Concatenated_Segments From apps.Gl_Code_Combinations_Kfv Gcck Where Code_Combination_Id=Pvsa.Accts_Pay_Code_Combination_Id ) Liability_Account,
(Select Concatenated_Segments From apps.Gl_Code_Combinations_Kfv Gcck Where Code_Combination_Id=Pvsa.Prepay_Code_Combination_Id ) Prepaymernt_Account,
(Select Concatenated_Segments From Apps.Gl_Code_Combinations_Kfv Gcck Where Code_Combination_Id=Pvsa.Future_Dated_Payment_Ccid ) Future_Dated_Payement_Account,
(Select Name From Ap.Ap_Terms_Tl Where Term_Id=Pvsa.Terms_Id) Terms,
Pvsa.Pay_Group_Lookup_Code Pay_Group,
Pvsa.Payment_Priority,
pvsa.remittance_email,
Pvsa.Pay_Date_Basis_Lookup_Code Pay_Date_Basis,
pvsa.terms_date_basis,
Pvsa.Payment_Method_Lookup_Code Payment_Method,
Pvsa.Always_Take_Disc_Flag,
Pvsa.Exclude_Freight_From_Discount,
Pvsa.Exclusive_Payment_Flag,
pvsa.ATTENTION_AR_FLAG,
Pvsa.Invoice_Currency_Code Site_Invoice_Currency,
pvsa.payment_Currency_Code site_payment_Currency,
Apaa.Bank_Account_Name,
Apaa.Bank_Account_Num Bank_Account_Number,
Apaa.Currency_Code Bank_Account_Currency,
Apaua.Start_Date Effective_Date_From,
apaua.end_date effective_date_to,
Abb.Bank_Name,
abb.bank_number,
Abb.Bank_Branch_Name,
Abb.Bank_Num Branch_Number,
(Select Invoice_Num From Ap.Ap_Invoices_All Aia
Where Invoice_Id =(Select Max(Invoice_Id) From Ap.Ap_Invoices_All Aia1 Where Aia1.Vendor_Site_Id=Pvsa.Vendor_Site_Id ))latest_Invoice_Num,
(Select Invoice_Date From Ap.Ap_Invoices_All Aia
Where Invoice_Id =(Select Max(Invoice_Id) From Ap.Ap_Invoices_All Aia1 Where Aia1.Vendor_Site_Id=Pvsa.Vendor_Site_Id ))latest_Invoice_Date,
(Select Check_Date From Ap.Ap_Checks_All Where Check_Id=(
SELECT MAX(CHECK_ID) FROM AP.AP_INVOICE_PAYMENTS_ALL WHERE INVOICE_ID=(select max(invoice_id) from Ap.Ap_Invoices_All aia1 where aia1.Vendor_Site_Id=pvsa.Vendor_Site_Id )))PAYMENT_DATE
From Po.Po_Vendors Pv INNER JOIN Po.Po_Vendor_Sites_All Pvsa ON Pv.Vendor_Id=Pvsa.Vendor_Id AND pv.Enabled_Flag <>'N'
LEFT OUTER JOIN Ap.Ap_Bank_Account_Uses_All Apaua ON Pvsa.Vendor_Site_Id=Apaua.Vendor_Site_Id and apaua.primary_flag='Y' 
LEFT OUTER JOIN Ap.Ap_Bank_Accounts_All Apaa  ON Apaua.External_Bank_Account_Id=Apaa.Bank_Account_Id
LEFT OUTER JOIN Ap.Ap_Bank_Branches Abb ON Apaa.Bank_Branch_Id=Abb.Bank_Branch_Id
Where
 (pv.End_Date_Active Is Null Or Trunc(pv.End_Date_Active)>Trunc(Sysdate))
And (Pv.Start_Date_Active Is Null Or Trunc(Pv.Start_Date_Active)<Trunc(Sysdate))
And (Pvsa.Inactive_Date Is Null Or Pvsa.Inactive_Date > Trunc(Sysdate))
order by vendor_name ;