Join between oe_order_lines_all and mtl_serial_numbers

Join between oe_order_lines_all and mtl_serial_numbers

In this post , we are going to discuss about the joins between oe_order_lines_all and mtl_serial_numbers tables. oe_order_lines_all tables belongs to the Sales order and mtl_serial_numbers table belongs to the Inventory module in oracle apps. When we sells the finish good item who is serial controlled in Oracle Inventory system then the system do creates the joins between the oe_order_lines_all and mtl_serial_numbers. Here below I will share the Joins and the sql query using Join between oe_order_lines_all and mtl_serial_numbers.

 

 

Join between oe_order_lines_all and mtl_serial_numbers

 

1.mtl_material_transactions

2.oe_order_lines_all

3.WSH_NEW_DELIVERIES

4.oe_order_headers_all

5.MTL_TRANSACTION_LOT_NUMBERS

6.MTL_UNIT_TRANSACTIONS

 

 

Detail sql using Join between oe_order_lines_all and mtl_serial_numbers 

select OOLA.ordered_quantity,oola.order_quantity_uom,oola.ordered_item,ooha.order_number,

WND.DELIVERY_ID,MTLN.LOT_NUMBER,MUT.SERIAL_NUMBER

from apps.mtl_material_transactions A1,

apps.oe_order_lines_all oola,

apps.WSH_NEW_DELIVERIES WND,

apps.oe_order_headers_all ooha,

apps.MTL_TRANSACTION_LOT_NUMBERS mtln,

APPS.MTL_UNIT_TRANSACTIONS MUT

where a1.TRX_SOURCE_LINE_ID=oola.LINE_ID

and A1.TRX_SOURCE_DELIVERY_ID=WND.DELIVERY_ID

and oola.header_id=ooha.header_id

and  a1.transaction_id=mtln.transaction_id

and mtln.serial_transaction_id=MUT.transaction_id

and ooha.order_number=:P_ORDER_NUMBER