The optimization process and recommendations:

1. Avoid Calling Functions With Indexed Columns (query line: 71): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `lot_number` is indexed, the index won’t be used as it’s wrapped with the function `substr`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
2. Avoid Calling Functions With Indexed Columns (query line: 80): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `lot_number` is indexed, the index won’t be used as it’s wrapped with the function `substr`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
3. Avoid Calling Functions With Indexed Columns (query line: 95): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `transaction_reference` is indexed, the index won’t be used as it’s wrapped with the function `substr`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
4. Avoid Calling Functions With Indexed Columns (query line: 105): When a function is used directly on an indexed column, the database's optimizer won’t be able to use the index. For example, if the column `transaction_reference` is indexed, the index won’t be used as it’s wrapped with the function `substr`. If you can’t find an alternative condition that won’t use a function call, a possible solution is to store the required value in a new indexed column.
5. Avoid LIKE Searches With Leading Wildcard (query line: 115): The database will not use an index when using like searches with a leading wildcard (e.g. '%May-15%'). Although it's not always a satisfactory solution, please consider using prefix-match LIKE patterns (e.g. 'TERM%').
6. Create Optimal Indexes (modified query below): The recommended indexes are an integral part of this optimization effort and should be created before testing the execution duration of the optimized query.
7. Prefer Inner Join Over Left Join (modified query below): We identified that one or more left joined entities (e.g. `MTL_GENERIC_DISPOSITIONS`) are used in the 'where' clause, in a way that allows to replace it with an optimized inner join. Inner joins can be fully optimized by the database, while Left joins apply limitations on the database's optimizer.
8. Prefer Inner Join Over Left Join (modified query below): We identified that one or more left joined entities (e.g. `org_acct_periods`) are used in the 'where' clause, in a way that allows to replace it with an optimized inner join. Inner joins can be fully optimized by the database, while Left joins apply limitations on the database's optimizer.
9. Use Numeric Column Types For Numeric Values (query line: 88): Referencing a numeric value (e.g. 0) as a string in a WHERE clause might result in poor performance. Possible impacts of storing numbers as varchars: more space will be used, you won't be able to perform arithmetic operations, the data won't be self-validated, aggregation functions like SUM won't work, the output may sort incorrectly and more. If the column is numeric, remove the quotes from the constant value, to make sure a numeric comparison is done.

Optimal indexes for this query:

ALTER TABLE `MTL_GENERIC_DISPOSITIONS` ADD INDEX `mtl_dispositions_idx_description` (`description`);
ALTER TABLE `fnd_user` ADD INDEX `fnd_user_idx_user_id` (`USER_ID`);
ALTER TABLE `gme_batch_steps` ADD INDEX `gme_steps_idx_attribute5` (`attribute5`);
ALTER TABLE `mtl_material_transactions` ADD INDEX `mtl_transactions_idx_transaction_id_transaction_id` (`transaction_source_id`,`transaction_id`);
ALTER TABLE `mtl_parameters` ADD INDEX `mtl_parameters_idx_organization_id` (`organization_id`);
ALTER TABLE `mtl_secondary_inventories` ADD INDEX `mtl_inventories_idx_organization_id` (`organization_id`);
ALTER TABLE `mtl_system_Items_b` ADD INDEX `mtl_items_idx_inventory_id_organization_id` (`inventory_item_id`,`organization_id`);
ALTER TABLE `mtl_transaction_lot_numbers` ADD INDEX `mtl_lot_idx_transaction_id` (`transaction_id`);
ALTER TABLE `org_acct_periods` ADD INDEX `org_periods_idx_acct_id` (`acct_period_id`);

The optimized query:

SELECT
        mp.organization_code 'ORG',
        mmt.organization_id,
        mtln.lot_number,
        mmt.transaction_quantity 'QTY',
        msi.segment1 'Item_ID',
        mgd.description 'Trans_Source',
        mtln.grade_code 'Lot_Trans_Grade',
        mmt.SUBINVENTORY_CODE 'Subinventory',
        msi2.description 'Location',
        fu.description 'User_Name',
        mmt.transaction_id,
        mmt.last_update_date 'Trans_Date',
        oap.period_name,
        gbh.batch_id,
        substr(mtln.lot_number,
        instr(mtln.lot_number,
        '(',
        -1) + 1,
        instr(mtln.lot_number,
        ')',
        -1) - instr(mtln.lot_number,
        '(',
        -1) - 1) 'Batch',
        substr(mtln.lot_number,
        0,
        instr(mtln.lot_number,
        '-') - 1) 'Heat',
        gbh.attribute13 'Project_Material_Request',
        CASE 
            WHEN gbs.attribute5 IS NULL THEN 'No DDR' 
            ELSE gbs.attribute5 END 'DDR',
mmt.transaction_reference,
(CASE 
    WHEN gbs.attribute5 <> 'No DDR' THEN majd.major_deviation 
    ELSE (CASE 
        WHEN substr(mmt.transaction_reference,
        2,
        1) = '-' THEN majd.major_deviation 
        ELSE 'No DDR' END) END) AS 'Major_Reason',
(CASE 
    WHEN gbs.attribute5 <> 'No DDR' THEN mind.minor_deviation 
    ELSE (CASE 
        WHEN substr(mmt.transaction_reference,
        2,
        1) = '-' THEN mind.minor_deviation 
        ELSE 'No DDR' END) END) AS 'Minor_Reason' 
FROM
mtl_material_transactions mmt 
LEFT OUTER JOIN
mtl_transaction_lot_numbers mtln 
    ON mmt.transaction_id = mtln.transaction_id 
LEFT OUTER JOIN
mtl_system_Items_b msi 
    ON mtln.INVENTORY_ITEM_ID = msi.inventory_item_id 
    AND mtln.organization_id = msi.organization_id 
LEFT OUTER JOIN
mtl_parameters mp 
    ON mtln.organization_id = mp.organization_id 
INNER JOIN
MTL_GENERIC_DISPOSITIONS mgd 
    ON mmt.transaction_source_id = mgd.disposition_id 
LEFT OUTER JOIN
apps.fnd_user fu 
    ON mmt.LAST_UPDATED_BY = fu.USER_ID 
INNER JOIN
apps.org_acct_periods oap 
    ON mmt.acct_period_id = oap.acct_period_id 
LEFT OUTER JOIN
gme_batch_header gbh 
    ON substr(mtln.lot_number,
instr(mtln.lot_number,
'(',
-1) + 1,
instr(mtln.lot_number,
')',
-1) - instr(mtln.lot_number,
'(',
-1) - 1) = gbh.batch_no 
AND substr(mtln.lot_number,
0,
instr(mtln.lot_number,
'-') - 1) = gbh.attribute12 
LEFT OUTER JOIN
gme_batch_steps gbs 
    ON mmt.transaction_quantity = -gbs.actual_step_qty 
    AND gbh.batch_id = gbs.batch_id 
    AND gbs.attribute5 > '0' 
LEFT OUTER JOIN
bolinf.xxalv_qa_ncr xqn 
    ON gbs.attribute5 = xqn.ncr_no 
LEFT OUTER JOIN
bolinf.xxalv_qa_major_deviations majd 
    ON xqn.major_deviation = majd.major_code 
    OR majd.major_code = substr(mmt.transaction_reference,
1,
1) 
LEFT OUTER JOIN
bolinf.xxalv_qa_minor_deviations mind 
    ON (
        xqn.minor_deviation = mind.minor_code 
        AND majd.deviation_id = mind.deviation_id
    ) 
    OR (
        substr(mmt.transaction_reference,
    3,
    2) = mind.minor_code 
    AND majd.deviation_id = mind.deviation_id) 
LEFT OUTER JOIN
    mtl_secondary_inventories msi2 
        ON msi2.organization_id = mp.organization_id 
        AND mmt.subinventory_code = msi2.secondary_inventory_Name 
WHERE
    mgd.description = 'Scrapped Lot' 
    AND oap.period_name LIKE '%May-15%' 
ORDER BY
    mmt.transaction_id