Financial tables in Oracle Fusion

Financial tables in Oracle Fusion

Hi friends , we are going to discuss about the financial tables in oracle fusion. We will share complete list of important financials tables which belongs to all financial modules in oracle fusion. We will try to share the financial tables for AP , Financial tables for GL , Financial tables for AR and financial tables for FA. There are many financial tables in oracle fusion but in this post , we will share some of the most important financial tables which helps to store most important financial data in oracle fusion. We will also try to share the financial s1.ql queries too using these financial tables using these financial sql queries you can use to extract the AP , AR,GL,FA modules data in oracle fusion application. Using these sql financial sql queries we can also develop the custom BIP reports in oracle fusion. Please find below the complete detail about the Financial tables in Oracle Fusion.

Financial tables in Oracle Fusion

Important Financial Tables in Oracle Fusion

Here below is the list of oracle fusion tables which helps to store the financial details from different financial modules in oracle fusion.

9 AP Financial Tables in Oracle Fusion

1.AP_INVOICES_ALL

2.AP_INVOICE_LINES_ALL

3.AP_INVOICE_DISTRIBUTIONS_ALL

4.AP_CHECKS_ALL

5.AP_INVOICE_PAYMENTS_ALL

6.AP_PAYMENTS_ALL

7.AP_HOLDS_ALL

8.POZ_SUPPLIERS

9.POZ_SUPPLIER_SITES_ALL

7 AR Financial Tables in Oracle Fusion

1.RA_CUSTOMER_TRX_ALL

2.RA_CUSTOMER_TRX_LINES_ALL

3. ra_cust_trx_line_gl_dist_all

4.HZ_CUST_ACCOUNTS_ALL

5.HZ_CUST_ACCT_SITES_ALL

6.HZ_CUST_SITE_USES_ALL

7.HZ_PARTY_SITES

4 GL Financial Tables in Oracle Fusion

1.GL_JE_HEADERS

2.GL_JE_LINES

3.GL_BATCHES

4.GL_BALANCES

6 FA Financial Tables in Oracle Fusion

1.FA_ADDITIONS_B

2.FA_ASSET_HISTORY

3.FA_ASSET_INVOICES

4.FA_BOOKS

5.FA_CATEGORIES_B

6.FA_DEPRN_SUMMARY

Complete AP SQL Query using Financial tables in Oracle Fusion

Here below is the detail sql query which helps to extract the complete AP financial information's in oracle fusion. You can use this query to develop the AP financial custom BIP reports in oracle fusion.

select aia.invoice_num "Invoice Number",

hp.party_name "Vendor Name"

,site.vendor_site_code "Supplier Site",

aia.invoice_currency_code Invoice_Currency,

aia.payment_currency_code Payment_Currency,

aia.invoice_amount "Invoice Amount",

aia.amount_paid "Amount Paid",

aia.invoice_date Invoice_Date,

ats.name terms,

aia.terms_dateTerms_Date,

inv_typ_lookup.meaning "Invoice Type",

pay_grp_lookup.meaning "Invoice Pay Group",

pmnt.meaning "Invoice Payment Method",

TO_CHAR(aia.gl_date, ‘MM/DD/YYYY’) "Invoice Gl Date",

aia.vendor_site_id

from ap_Invoices_all aia,

fnd_lookup_values pmnt,

ap_terms ats,

fnd_lookup_values pay_grp_lookup,

hz_parties hp

, poz_supplier_sites_all_m site

,fnd_lookup_values Inv_Typ_lookup

Where 1=1

AND hp.party_id = aia.party_id

AND site.vendor_site_id = aia.vendor_site_id

AND Inv_Typ_lookup.lookup_type = 'INVOICE TYPE'

AND inv_typ_lookup.lookup_code = aia.INVOICE_TYPE_LOOKUP_CODE

AND pay_grp_lookup.lookup_type(+) = 'PAY GROUP'

AND pay_grp_lookup.lookup_code (+) = aia.PAY_GROUP_LOOKUP_CODE

AND aia.invoice_num = :p_invoice_num

AND pmnt.lookup_type(+) = ‘PAYMENT METHOD’

AND pmnt.lookup_code(+) = aia.payment_method_code

AND ats.term_id (+) = aia.terms_id

Financial tables in Oracle Fusion

Financial tables in Oracle Fusion

Thanks !!