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

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


    Delivery was still open

    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

  /* Formatted on 7/17/2014 12:27:58 PM (QP5 v5.115.810.9015) */
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


    Called WSH api to close delivery


    /* Formatted on 7/17/2014 12:28:04 PM (QP5 v5.115.810.9015) */
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
          );


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

/* Formatted on 7/17/2014 12:28:14 PM (QP5 v5.115.810.9015) */
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
       );