优化以下Oracle语句:
SELECT SUBSTR(msn.serial_number, 1, 10) genset_sn,
msi2.segment1 Genset_BOM_NUM,
msi2.inventory_item_id,
msi.segment1 key_component,
mut1.serial_number component_sn,
msi.description component_desc,
wdj.date_completed,
(SELECT MAX(aps.vendor_name)
FROM ap_suppliers aps,
bom_resources bor,
mtl_unit_transactions mut,
po_headers_all poh,
po_lines_all pol,
wip_osp_resources_val_v wor
WHERE aps.vendor_id = poh.vendor_id
AND bor.resource_id = wor.resource_id
AND poh.po_header_id = pol.po_header_id
AND pol.item_id = bor.purchase_item_id
AND wor.wip_entity_id = mut.transaction_source_id
AND mut.serial_number = mut1.serial_number
AND mut.inventory_item_id = mut1.inventory_item_id
AND mut.organization_id = mut1.organization_id
AND mut.receipt_issue_type = 2
AND mut.transaction_source_type_id = 5
) supplier
FROM mtl_material_transactions mmt1,
mtl_material_transactions mmt2,
mtl_parameters mpa,
mtl_serial_numbers msn,
mtl_system_items msi,
mtl_system_items msi2,
mtl_transaction_types mtt1,
mtl_transaction_types mtt2,
mtl_unit_transactions mut1,
mtl_unit_transactions mut2,
wip_discrete_jobs_v wdj
WHERE mmt1.inventory_item_id = mut1.inventory_item_id
AND mmt1.organization_id = mut1.organization_id
AND WDJ.PRIMARY_ITEM_ID = msi2.INVENTORY_ITEM_ID
AND mmt1.transaction_id = mut1.transaction_id
AND mmt1.transaction_source_id = wdj.wip_entity_id
AND mmt1.transaction_type_id = mtt1.transaction_type_id
AND mtt1.transaction_type_name = 'WIP Issue'
AND NOT EXISTS
(SELECT 'WIP Negative Issue or WIP Return'
FROM mtl_material_transactions mmt3,
mtl_transaction_types mtt3,
mtl_unit_transactions mut3
WHERE mmt3.transaction_id = mut3.transaction_id
AND mmt3.transaction_type_id = mtt3.transaction_type_id
AND mmt3.transaction_date > mmt1.transaction_date
AND mtt3.transaction_type_name IN
('WIP Negative Issue', 'WIP Return')
AND mut3.serial_number = mut1.serial_number
AND mut3.inventory_item_id = mut1.inventory_item_id)
AND mmt2.transaction_id = mut2.transaction_id
AND mmt2.transaction_source_id = wdj.wip_entity_id
AND mmt2.transaction_type_id = mtt2.transaction_type_id
AND mtt2.transaction_type_name = 'WIP Completion'
AND mpa.organization_code = 'WHP'
AND msn.current_organization_id = mpa.organization_id
AND LENGTH(msn.serial_number) >= 10
AND msi.inventory_item_id = mmt1.inventory_item_id
AND msi.organization_id = mmt1.organization_id
AND (msi.planning_make_buy_code = 2
OR msi.segment1 LIKE 'SO%')
AND mut2.serial_number = msn.serial_number
AND mut2.inventory_item_id = msn.inventory_item_id
AND mut2.organization_id = mpa.organization_id
AND msi2.ORGANIZATION_ID = '323'
最新发布