Query : Operating Unit and Inventory Org link

SELECT hou.NAME operating_unit_name, hou.short_code,
hou.organization_id operating_unit_id, hou.set_of_books_id,
hou.business_group_id,
ood.organization_name inventory_organization_name,
ood.organization_code Inv_organization_code, ood.organization_id Inv_organization_id, ood.chart_of_accounts_id
FROM hr_operating_units hou, org_organization_definitions ood
WHERE 1 = 1 AND hou.organization_id = ood.operating_unit
ORDER BY hou.organization_id ASC 

improving the performance of Work Order and Material transactions

The key to improving the performance of a work order and a material transaction is by feeding the organization details to gme_batch_header

After several sessions, here is the optimized query

SELECT msib.segment1,
                           msib.description,
                           mtln.lot_number,
                           gbh.batch_no,
                           mp.organization_code,
                           mp.organization_id,
                           mmt.transaction_uom,
                           mtln.transaction_id,
                           mtln.transaction_quantity,
                           mtln.transaction_date
                      FROM inv.mtl_transaction_lot_numbers mtln,
                           inv.mtl_material_transactions mmt,
                           inv.mtl_system_items_b msib,
                           inv.mtl_lot_numbers mln,
                           gme.gme_batch_header gbh,
                           gme.gme_material_details gmd,
                           mtl_parameters mp
                     WHERE     1 = 1
                           AND mtln.transaction_id = mmt.transaction_id
                           AND mtln.inventory_item_id = mmt.inventory_item_id
                           AND mtln.organization_id = mmt.organization_id
                           AND mmt.transaction_source_type_id = 5 /* Job Or Schedule */
                           AND mtln.transaction_source_type_id = 5 /* Job Or Schedule */
                           AND mmt.inventory_item_id = msib.inventory_item_id
                           AND mmt.organization_id = msib.organization_id
                           AND mmt.transaction_source_id = gbh.batch_id
                           AND mmt.transaction_type_id IN (44, 17) /* WIP Completion and WIP completion Return */
                           AND mtln.lot_number = mln.lot_number
                           AND mtln.inventory_item_id = mln.inventory_item_id
                           AND mtln.organization_id = mln.organization_id
                           AND mtln.status_id = 20    /* Quarantine Routine */
                           AND mln.status_id = 20     /* Quarantine Routine */
                           AND mln.disable_flag IS NULL
                           AND mp.organization_id = mmt.organization_id
                           AND mp.organization_id = gbh.organization_id
                           AND gmd.batch_id = gbh.batch_id
                           AND mmt.trx_source_line_id = gmd.material_detail_id
                           AND gmd.inventory_item_id = mmt.inventory_item_id
                           AND mmt.inventory_item_id = mtln.inventory_item_id
                           AND gmd.line_type = 1
                           AND msib.segment1 = NVL (:P_ITEM_NUM, msib.segment1)
                           AND mtln.lot_number = NVL (:P_LOT_NUM, mtln.lot_number)
                  GROUP BY msib.segment1,
                           msib.description,
                           mtln.lot_number,
                           gbh.batch_no,
                           mp.organization_code,
                           mp.organization_id,
                           mmt.transaction_uom,
                           mtln.transaction_id,
                           mtln.transaction_quantity,
                           mtln.transaction_date