AR tax query in oracle apps r12

AR tax query in oracle apps r12

In this post , We will be discuss about AR tax query in oracle apps r12. This query helps to extract the AR invoice information with the Tax applied against that Ar invoice. We will get the Tax amount and the tax rate for that invoice in oracle apps. Oracle do store the tax information's in different Tax related tables like 'ZX'. We need to involve these tax tables(ZX) in this query to for extracting the AR invoice and the tax related information's in oracle apps r12.

 

 

6 Important tables for AR tax query in oracle apps r12

1.zx_lines

2.ra_customer_trx_all

3.ra_customer_trx_lines_all
4.ZX_TAXES_B ZTB

5.GL_DAILY_CONVERSION_TYPES

6.ar_customers

 

Detail SQL AR tax query in oracle apps r12

SELECT rcta.trx_number,ac.customer_name,rcta.TRX_DATE,rcta.PURCHASE_ORDER, lines.TAX_AMT,lines.tax_rate,
rctala.line_number,rctala.description,rctala.quantity_ordered,rctala.quantity_invoiced,rctala.unit_standard_price,rctala.sales_order,rctala.sales_order_date,rctala.line_type,rctala.sales_order_date,rctala.inventory_item_id
         FROM apps.zx_lines lines,apps.ra_customer_trx_all rcta,apps.ra_customer_trx_lines_all rctala,
 apps.ZX_TAXES_B ZTB,apps.GL_DAILY_CONVERSION_TYPES gc,apps.ar_customers ac
  where rcta.trx_number=:P_TRX_NUMBER
  and rcta.customer_trx_id=lines.trx_id
 and rcta.customer_trx_id       = rctala.customer_trx_id
  and ZTB.Exchange_Rate_Type=gc.conversion_type
  and ztb.tax_id=lines.tax_id
  AND rctala.LINE_TYPE='LINE'
  and rcta.org_id=:P_ORG_ID
  and lines.trx_line_id=rctala.customer_trx_line_id
  AND AC.CUSTOMER_ID=rcta.BILL_TO_CUSTOMER_ID