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.
Deixe uma resposta