Query for Pick/Delivery Materials

This post I will show a query that will help on control of material output and sales orders issue.

Pick Slip

Move Order Transaction

Ship Confirm

SELECT hp.party_name cliente,
 ooha.cust_po_number po_cliente,
 ooha.order_number ordem_venda,
 (SELECT customer_line_number
 FROM oe_order_lines_all oola
 WHERE oola.header_id = ooha.header_id
 AND oola.line_Id = wsh.source_line_id ) linha_po_cliente, 
 source_line_number linha,
 mib.segment1 item,
 mib.description descricao_item,
 wsh.date_scheduled schedule_ship_date data_entrega_programada,
 wsh.requested_quantity quantidade,
 headers.creation_Date pick_slip_date data_separação,
 headers.request_number move_order ordem_movimentacao,
 (SELECT MAX(mmt.creation_date)
 FROM mtl_material_transactions mmt
 WHERE mmt.move_order_line_id = wsh.move_order_line_id
 AND mmt.transaction_type_id = 52
 AND mmt.transaction_quantity < 0 
 AND mmt.inventory_item_id = wsh.inventory_item_id) data_tran_ordem_mov, 
 wdd.delivery_id,
 (SELECT MAX(mmt.creation_date)
 FROM mtl_material_transactions mmt
 WHERE mmt.source_line_Id = wsh.source_line_Id
 AND mmt.transaction_type_id = 33
 AND mmt.transaction_quantity < 0 
 AND mmt.inventory_item_id = wsh.inventory_item_id) data_confirmacao_entrega, 
 DECODE(released_status,'S','Released to Warehouse',
                        'B','Backordered',
                        'Y','Staged/Pick Confirmed',
                        'R','Released_Status',
                        'C','Interfaced') status_entrega
FROM wsh_delivery_details wsh,
 oe_order_headers_all ooha,
 mtl_system_items_b mib,
 mtl_txn_request_headers headers,
 mtl_txn_request_lines linhas,
 hz_cust_accounts hca,
 hz_parties hp,
 wsh_delivery_assignments wdd
WHERE released_status IN ('S','B','Y','R','C')
AND ooha.header_Id = wsh.source_header_Id
AND hca.party_id = hp.party_id
AND hca.cust_account_id = ooha.sold_to_org_id
AND wsh.inventory_item_id = mib.inventory_item_id
AND wsh.organization_id = mib.organization_id
AND headers.header_id(+) = linhas.header_id
AND wsh.move_order_line_Id = linhas.line_id(+)
AND wsh.delivery_detail_id = wdd.delivery_detail_id
AND wsh.date_scheduled < SYSDATE + 90

The query returns pick data, move order and ship confirm.