Oracle-EBS

R12 E-Business Suite Suppliers Query - SQL to join Suppliers, Contacts, Banks In the Oracle E-Business Suite (EBS) Release 12 the data model of Suppliers has become much more complex. The base tables have changed (Suppliers, Sites, Bank Accounts, Contacts) and some of the fields have become obsolete. Here is a query to bring many of the Supplier attributes together, with focus on banks / bank accounts, payment methods, contacts, remittance delivery (email, notification method). Please post comments if you find any issues! Adjust the WHERE clause on the first WITH query to return the suppliers that you need to report on. Hope this query helps someone out. Add additional fields to the final query (or WITH queries as required.) with vendors as ( select vendor_id from ap_suppliers where 1=1 /* COMMENT / UNCOMMENT and UPDATE THE NEXT 5 LINES AS YOU REQUIRE */ --and vendor_type_lookup_code = 'VENDOR' --and upper( vendor_name ) like 'VIRTUATE%' and ...

Will find a query which fetches both invoice and prepayment information of a payable invoice ( Invoice_id = 166014 ). All the queries given in this post and their related posts were tested in R12.1.1 Instance. SELECT  pv . vendor_name  " Vendor  Name" ,        ai . invoice_num  "Invoice Num" ,        ai . invoice_id  "Invoice Id" ,        ai . invoice_amount  "Invoice amount" ,        ail . line_number  "Invoice Line Num" ,        ai2 . invoice_id  "Prepay Invoice Id" ,        ai2 . invoice_num  "Prepay Invoice Num" ,        ail . prepay_line_number  "Prepay Invoice Line Num" ,         (- 1 )*( ail . amount  -  NVL (...

Below query will give the details about the joins between suppliers and Bank Account tables. We have tested this query in R12.1.1instance. Query: SELECT                                           /*Supplier Information*/                           aps . segment1                       oracle_supplier_number               , aps . vendor_id               , aps . vendor_name        ...