Link between sales order and AR invoice in oracle apps

Hi friends, We are going to discuss about the Link between sales order and AR invoice in oracle apps. We will share the complete joins between the sales order and the AR invoice tables in oracle apps. If we want to prepare the sql query or wants to develop the custom Oracle apps sales order reports we should have the complete knowledge about the sales orders tables and the AR invoice tables too . Besides this , we should also have the complete understanding about the DB links between the sales order and AR invoices tables. In this post , We will try to share the complete information's about Sales order and AR invoices SQL queries and the Tables links in oracle apps. We will share the detail sql query which helps to extract the completes sales order invoices data and the Sales order AR invoice details using the DB tables link between the sales order and the AR Invoice. Please find below the more details about Link between sales order and AR invoice in oracle apps.

Link between sales order and AR invoice in oracle apps

6 Most Important DB Tables to Link between Sales Order and AR Invoice

1.oe_order_headers_all

2.oe_order_lines_all

3.wsh_delivery_details

4.wsh_delivery_assignments

5.ra_customer_trx_lines_all

6.ra_customer_trx_all

2 Important DB Links between Sales order and AR Invoice Tables

Here below is the sample of DB Tables links between the sales order and AR invoice Tables in oracle apps.

ra_customer_trx_lines_all(interface_line_attribute1) = oe_order_headers_all(ORDER_NUMBER)

ra_customer_trx_lines_all(interface_line_attribute3)=wsh_delivery_assignments(delivery_id)

SQL Query to Extract Sales Order and AR Invoice Information's in Oracle Apps

SELECT distinct

ac.customer_name,

ooh.order_number,

ooh.ORDERED_DATE,

ool.line_number,

msi.SEGMENT1 Item_Name,

ool.ordered_quantity,

wdd.shipped_quantity,

rctl.QUANTITY_invoiced,

wda.delivery_id shipment_number,

rct.TRX_NUMBER "AR Invoice Num",

rct.TRX_date "AR Invoice Date",

rct.STATUS_TRX "AR Status TRX",

decode(rct.COMPLETE_FLAG,'Y','Completed','In Complete') "AR Invoice Status",

ool.UNIT_SELLING_price*ool.ordered_quantity "AR Invoice Line Total"

from apps.oe_order_headers_all ooh,

apps.ar_customers ac,

apps.wsh_delivery_details wdd,

apps.oe_order_lines_all ool,

apps.wsh_delivery_assignments wda,

apps.hz_cust_accounts hca,

apps.ra_customer_trx_lines_all rctl,

apps.ra_customer_trx_all rct,

apps.mtl_system_items msi

where

ooh.header_id=ool.header_id

and msi.INVENTORY_ITEM_ID=ool.INVENTORY_ITEM_ID

and msi.ORGANIZATION_ID=ool.SHIP_FROM_ORG_ID

and ooh.sold_to_org_id=hca.cust_account_id

and ooh.header_id=wdd.source_header_id

and ool.line_id=wdd.source_line_id

and hca.cust_account_id=ac.customer_id

and rctl.interface_line_attribute1 = to_char(ooh.ORDER_NUMBER)

and  rctl.interface_line_attribute3=to_char(wda.delivery_id)

and wda.delivery_detail_id=wdd.delivery_detail_id

and ooh.org_id=:P_ORG_ID

and rct.CUSTOMER_TRX_ID = rctl.CUSTOMER_TRX_ID

and rctl.LINE_TYPE = 'LINE'

and ooh.order_number=:P_order_number

order by 2

Link between sales order and AR invoice in oracle apps