AR Invoice Query in Oracle Fusion

AR Invoice Query in Oracle Fusion

Hi friends, we are going to discuss about the ar invoice query in Oracle Fusion. We will share the details sql query which helps to extract the complete ar invoice details in oracle fusion. This is the complete sql query for AR Fusion invoices. Using this sql query , we will be able to develop the custom AR invoice BIP report in oracle fusion. This is one of the most important sql query for the AR module. We will also share some of the important AR tables which do stores the AR invoice informations in oracle fusion. The best part of this sql query is , we can also extract the AR invoice tax details using this sql query. Please find below the complete detail about AR Invoice Query in Oracle Fusion.

AR Invoice Query in Oracle Fusion

10 Important Tables of AR Invoice Query in Oracle Fusion

1.hz_customer_profiles_f

2.ra_customer_trx_all

3.ra_customer_trx_lines_all

4.ra_customer_trx_lines_all

5.hz_cust_acct_sites_all

6.hz_cust_site_uses_all

7.hz_cust_accounts

8.hz_party_sites

9.zx_lines

10.fun_all_business_units_v

Detail SQL Query to extract AR Invoice details in Oracle Fusion

Here below is the complete sql query which helps to fetch the complete ar Invoice informations in oracle fusion. We can use this sql query to develop the custom BIP reports in oracle fusion.

SELECT distinct rcta.trx_number AS INV_NUM,

rcta.customer_trx_id ,

rcta.trx_date ,

rcta.invoice_currency_code ,

  hca.account_number,

  hp.party_name,

  hps.party_site_number,

  hps.party_site_name,

  hl.address1,

  hl.address2,

  hl.address3,

  hl.address4,

  hl.city,

  hl.state,

  hl.country,

(select name from ra_terms_vl rt where rt.term_id = rcta.term_id and rownum=1) pay_term, 

(select description from ra_terms_vl rt where rt.term_id = rcta.term_id and rownum=1) pay_term_desc, 

(select ((select due_days 

from ra_terms_vl rt , ra_terms_lines rtl

where rt.term_id = hcp.STANDARD_TERMS 

and rt.term_id =rtl.term_id and rtl.SEQUENCE_NUM = 1)  + (rcta.trx_date)) from dual) due_date,

rctl.description description,

rctl.EXTENDED_AMOUNT line_amt,

rctl_tax.eXTENDED_AMOUNT tax_amt_tot,

zl.tax,

zl.tax_rate_code,

zl.tax_amt 

FROM hz_customer_profiles_f hcp,

  ra_customer_trx_all rcta,

  ra_customer_trx_lines_all rctl,

  ra_customer_trx_lines_all rctl_tax,

  hz_cust_acct_sites_all hcas,

    hz_cust_site_uses_all hcsu,

  hz_cust_accounts hca,

  hz_party_sites hps,

  hz_parties hp,

  hz_locations hl,

  zx_lines zl,

  fun_all_business_units_v fabu

WHERE rcta.customer_trx_id = rctl.customer_trx_id

and rctl_tax.tax_line_id = zl.tax_line_id(+)

and zl.application_id(+) = 222

and rcta.trx_number between :trx_num_low and :trx_num_high

and rcta.customer_trx_id = rctl_tax.customer_trx_id(+)

and rctl.customer_trx_line_id = rctl_tax.LINK_TO_CUST_TRX_LINE_ID(+)

and rcta.BILL_TO_CUSTOMER_ID  = hca.CUST_ACCOUNT_ID

and hcsu.SITE_USE_ID = rcta.BILL_TO_SITE_USE_ID

and hcsu.CUST_ACCT_SITE_ID = hcas.CUST_ACCT_SITE_ID

and rcta.org_id = fabu.bu_id

and rctl.LINE_TYPE = 'LINE'

and rctl_tax.LINE_TYPE(+) = 'TAX'

and hcp.cust_account_id <> '-1'

AND hcp.site_use_id          is null

and hcp.party_id = hca.party_id

AND TRUNC (SYSDATE) BETWEEN TRUNC (hcp.effective_start_date) AND TRUNC (hcp.effective_end_date)

AND hcas.cust_account_id     = hca.cust_account_id

AND hcas.party_site_id       = hps.party_site_id

AND hps.party_id             = hp.party_id

AND hps.location_id          = hl.location_id

AND fabu.bu_name             = :p_bu_name

AR Invoice Query in Oracle Fusion