Picked line cancellation did not close Delivery and did not move inventory

At my work site, we wanted to allow sales order cancellation after it is picked. We changed some of custom processing constraints and users were able to cancel picked line but order line cancellation created two problems

1. Delivery was still open


2. Material did not move from staging inventory to original inventory

So we had to modify our custom order cancellation procedure and added following code.

This sql get delivery id that needs to be closed and sub inventory location where material should be moved back

    SELECT wdd.subinventory
         , wdd.locator_id
         , wdd.last_update_date
         , wda.delivery_id  
         , wdd.requested_quantity cancelled_quantity
         , ool.inventory_item_id
         , ool.ship_from_org_id
         , ool.order_quantity_uom
         , ool.line_id
         , mmt.subinventory_code to_subinventory
         , mmt.locator_id        to_locator_id
      FROM wsh_delivery_details       wdd
         , wsh_delivery_assignments   wda
         , oe_order_lines_all         ool
         , mtl_material_transactions  mmt
     WHERE ool.header_id          = p_header_id
       AND ool.line_id            = NVL(p_line_id, line_id)
       AND wdd.source_line_id     = ool.line_id
       AND wdd.delivery_detail_id = wda.delivery_detail_id
       AND mmt.transaction_id     = wdd.transaction_id 
       AND wda.delivery_id IS NOT NULL

Stored query output into a pl/sql table l_delivery_tab and for each record selected by the above sql, we did the following

1. Called WSH api to close delivery
   

   
         wsh_deliveries_pub.Delivery_Action
         ( p_api_version_number     => 1.0
         , p_init_msg_list          => FND_API.G_TRUE
         , x_return_status          => l_return_status
         , x_msg_count              => l_msg_count
         , x_msg_data               => l_msg_data
         , p_action_code            => 'CLOSE'
         , p_delivery_id            => l_delivery_tab(i).delivery_id
         , x_trip_id                => l_trip_id
         , x_trip_name              => l_trip_name
         );
   

   
   


2. For each record selected by the above query, inserted a record into material transaction interface table as given below
   

        INSERT INTO mtl_transactions_interface
                     ( source_code
                     , source_line_id
                     , source_header_id
                     , process_flag
                     , transaction_mode
                     , last_update_date
                     , last_updated_by
                     , creation_date
                     , created_by
                     , inventory_item_id
                     , organization_id
                     , transaction_quantity
                     , transaction_uom
                     , transaction_date
                     , subinventory_code
                     , locator_id
                     , transaction_type_id
                     , transfer_subinventory
                     , transfer_organization
                     , transfer_locator
                     )
         VALUES      ( 'CANCEL_ORDER'                --SOURCE_CODE,
                     , l_delivery_tab(i).line_id     --SOURCE_LINE_ID,
                     , l_header_id                   --SOURCE_HEADER_ID,
                     , 1                             --PROCESS_FLAG,
                     , 3                             --TRANSACTION_MODE,
                     , SYSDATE                       --LAST_UPDATE_DATE,
                     , -1                            --LAST_UPDATED_BY,
                     , SYSDATE                       --CREATION_DATE,
                     , -1                            --CREATED_BY,
                     , l_delivery_tab(i).inventory_item_id   --INVENTORY_ITEM_ID,
                     , l_delivery_tab(i).ship_from_org_id    --ORGANIZATION_ID,
                     , l_delivery_tab(i).cancelled_quantity  --TRANSACTION_QUANTITY,
                     , l_delivery_tab(i).order_quantity_uom  --TRANSACTION_UOM,
                     , SYSDATE                               --TRANSACTION_DATE,
                     , l_delivery_tab(i).subinventory        --SUBINVENTORY_CODE,
                     , l_delivery_tab(i).locator_id          --LOCATOR_ID,
                     , 2                                     --TRANSACTION_TYPE_ID,
                     , l_delivery_tab(i).to_subinventory     --TRANSFER_SUBINVENTORY,
                     , l_delivery_tab(i).ship_from_org_id    --TRANSFER_ORGANIZATION,
                     , l_delivery_tab(i).to_locator_id        --TRANSFER_LOCATOR
                     );