Scribd
Upload
65 views2 pages

Inventory Transfer and Delivery Data

The JSON returns data from multiple tables related to material transactions, work orders, deliveries and inventory items by joining the tables on common fields. It selects over 50 fields covering request numbers, order numbers, lines, quantities, statuses, locations and other details. A second SQL query returns pick slip numbers, transfer orders and supply order details by joining additional tables on source headers and IDs.

Uploaded by

alexorctrainning
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
65 views2 pages

Inventory Transfer and Delivery Data

The JSON returns data from multiple tables related to material transactions, work orders, deliveries and inventory items by joining the tables on common fields. It selects over 50 fields covering request numbers, order numbers, lines, quantities, statuses, locations and other details. A second SQL query returns pick slip numbers, transfer orders and supply order details by joining additional tables on source headers and IDs.

Uploaded by

alexorctrainning
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

SELECT

mhl.request_number
,l.line_number
,h.order_number
,mrl.line_number
,mrl.line_id
,mrl.from_subinventory_code
,mrl.lot_number
,mrl.serial_number_start
,mrl.serial_number_end
,mrl.uom_code
,[Link]
,mrl.quantity_delivered
,mrl.quantity_detailed
,wdd.source_header_number
,wdd.source_header_id
,wdd.source_line_id
,wdd.shipping_instructions
,wdd.inventory_item_id
,wdd.requested_quantity_uom
,wdd.ship_method_code
,[Link]
,wdd.shipment_priority_code
,wdd.organization_id
,wdd.released_status
,wdd.source_code
,wnd.delivery_id
,[Link]
,wnd.initial_pickup_location_id
,wnd.creation_date
,msib.segment1
,[Link]
,msib.revision_qty_control_code
FROM
mtl_txn_request_lines mrl
,mtl_txn_request_headers mhl
,wsh_delivery_details wdd
,wsh_new_deliveries wnd
,wsh_delivery_assignments wda
,oe_order_lines_all l
,oe_order_headers_all h
,mtl_system_items_b msib
WHERE
mhl.header_id =mrl.header_id
AND mrl.line_id =wdd.move_order_line_id
AND wda.delivery_id =wnd.delivery_id(+)
AND wdd.delivery_detail_id = wda.delivery_detail_id
AND wdd.source_header_id =l.header_id
AND wdd.source_line_id =l.line_id
AND h.header_id =l.header_id
AND wdd.organization_id =msib.organization_id(+)
AND wdd.inventory_item_id =msib.inventory_item_id(+)
AND wda.delivery_id ='&delivery_id' ;

--------------------------------
---------------------------------

SELECT
a.pick_slip_number ,
b.source_header_number transfer_order_number,
c.supply_order_number ,
c.supply_order_reference_number
FROM
INV_PICK_SLIP_NUMBERS a ,
WSH_DELIVERY_DETAILS b ,
DOS_SUPPLY_HEADERS c ,
INV_TRANSFER_ORDER_HEADERS d,
INV_TRANSFER_ORDER_LINES e
WHERE 1 =1
AND a.PICK_SLIP_NUMBER = 3001
AND a.pick_slip_batch_id = b.batch_id
AND b.source_header_number = d.header_number
AND d.header_id = e.header_id
AND e.source_header_id = c.header_id

select to_DATE(sysdate,'MM-DD-YYYY') start_date


from dual
where 1=1

You might also like