Sales order invoice query in oracle apps

Sales order invoice query in oracle apps

In this post , We will be discuss about the Sales order invoice query in oracle apps. This query will help to extract the Sales Order In formations and the AR Invoices created against the sales order in oracle apps r12. if we want to know which the AR invoices created for the sales order , we can refer this SQL query to extract the Sales order invoice information in oracle apps. This query will help to extract the Sales Order Number , Delivery Number/Shipment Number , AR Invoice number in oracle apps. Here below i will share the important tables and the SQL query using Sales order invoice query in oracle apps.

4 Important Table of Sales order invoice query in oracle apps

These below tables we are using in the sales order invoice query. We are Using sales order tables , Shipment Tables and the AR Tables for the query.

1.oe_order_headers_all

2.ar_customers

3.wsh_delivery_details

4.oe_order_lines_all

5.wsh_delivery_assignments

6.hz_cust_accounts

7.ra_customer_trx_lines_all

8.ra_customer_trx_all

9.mtl_system_items

Detail Sales order invoice query in oracle apps

SELECT distinct

ooh.order_number,

ac.customer_name,

ooh.org_id,

ooh.ORDERED_DATE,

ooh.FLOW_STATUS_CODE SO_Status,

ool.line_number,

msi.SEGMENT1 Item_Name,

ool.ordered_quantity,

wdd.shipped_quantity,

rctl.QUANTITY_invoiced,

wda.delivery_id shipment_number,

rct.TRX_NUMBER Invoice_Num,

rct.TRX_date Invoice_Date,

rct.STATUS_TRX,

decode(rct.COMPLETE_FLAG,'Y','Completed','In Complete') Inv_Status,

ool.UNIT_SELLING_price*ool.ordered_quantity 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 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 msi.INVENTORY_ITEM_ID=ool.INVENTORY_ITEM_ID

and msi.ORGANIZATION_ID=ool.SHIP_FROM_ORG_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 rctl.interface_line_attribute1 = to_char(ooh.ORDER_NUMBER)

and  rctl.interface_line_attribute3=to_char(wda.delivery_id)

--and rctl.QUANTITY_invoiced = ool.ORDERED_QUANTITY

and ooh.order_number=:P_order_number

order by ool.line_number;