Finally Close PO Requisition API Oracle Apps R12

Below API is used to Finally Close PO Requisitions.

declare
     v_returned          BOOLEAN;
     v_return_code_h     VARCHAR2(1);
     lc_return_status    VARCHAR2(50);
     lc_exception_msg    VARCHAR2(4000);
     lc_return_code      VARCHAR2(50);
     ln_online_report_id NUMBER;
     v_error_code        VARCHAR2(4000);
     lc_req_control_error VARCHAR2(4000);
BEGIN
      FND_GLOBAL.APPS_INITIALIZE (user_id,resp_id, 201);
      mo_global.set_policy_context('S',org_id);

     FOR po_req IN
     (SELECT      *
             FROM req_closed_tbl opc
     )
     LOOP
po_reqs_control_sv.update_reqs_status( X_req_header_id => po_req.requisition_header_id 
                                             , x_req_line_id =>  po_req.requisition_line_id 
                                             , x_agent_id => po_req.preparer_id 
                                             , x_req_doc_type => po_req.type_lookup_code--TYPE_LOOKUP_CODE
                                             , x_req_doc_subtype => po_req.document_type_code--DOCUMENT_TYPE_CODE
                                             , X_req_control_action => 'FINALLY CLOSE' 
                                             , X_req_control_reason => 'Put your description' 
                                             , X_req_action_date => SYSDATE 
                                             , X_encumbrance_flag => 'N' 
                                             , X_oe_installed_flag => 'Y' 
                                             , X_req_control_error_rc => v_error_code);

              IF v_error_code is not null then
              ---put logic here 
              END IF;
          COMMIT;

     END LOOP;

EXCEPTION

WHEN OTHERS THEN
     dbms_output.put_line(' Exception closing Requisition using po_reqs_control_sv.update_reqs_status'||SQLERRM);

END;

Avaialble to Reserve and Availble to Transact quantities using inv_quantity_tree_pub.query_quantities API

There is much to mention about this API which makes life easy to get available to Reserve/Transact quantities, since we cannot perform a quantity transaction based on On hand quantity.

Use inv_quantity_tree_pub.query_quantities API to get the exact quantities.

Things to consider.

1. Set the value TRUE for lot /serial controlled only if your item is controlled by those attributes else set to FALSE.
2. Advice to call inv_quantity_tree_grp.clear_quantity_cache to delete all the tree quantities in the memory.
3. Most importantly if you are here because you are not getting right quantity by directly calling this function please follow the note after API.

FUNCTION available_to_reserve (
   p_inv_item_id       NUMBER
  ,p_subinv_code       VARCHAR2
  ,p_locator_id        NUMBER
  ,p_lot_number        VARCHAR2
  ,p_organization_id   NUMBER
)
   RETURN NUMBER
IS
   x_return_status         VARCHAR2 (50);
   x_msg_count             VARCHAR2 (50);
   x_msg_data              VARCHAR2 (50);
   l_item_id               NUMBER;
   l_organization_id       NUMBER;
   l_qty_on_hand           NUMBER;
   l_res_qty_on_hand       NUMBER;
   l_avail_to_tnsct        NUMBER;
   l_avail_to_reserve      NUMBER;
   l_qty_reserved          NUMBER;
   l_qty_suggested         NUMBER;
   l_lot_control_code      BOOLEAN;
   l_serial_control_code   BOOLEAN;
BEGIN
   /* Used to clear cache*/
   inv_quantity_tree_grp.clear_quantity_cache;
  
   -- Initialize apps by passing correct values
   fnd_global.apps_initialize (user_id,resp_id,resp_appl_id);
  
   -- Set the variable values
   l_item_id := p_inv_item_id;
   l_organization_id := p_organization_id;
   l_lot_control_code := TRUE;   --Only When Lot number is passed  TRUE else FALSE
   l_serial_control_code := FALSE;   --if item is serial controlled pass  TRUE else FALSE
   -- Call API
   inv_quantity_tree_pub.query_quantities
         (p_api_version_number       => 1.0
         ,p_init_msg_lst             => 'F'
         ,x_return_status            => x_return_status
         ,x_msg_count                => x_msg_count
         ,x_msg_data                 => x_msg_data
         ,p_organization_id          => l_organization_id
         ,p_inventory_item_id        => l_item_id
         ,p_tree_mode                => apps.inv_quantity_tree_pub.g_transaction_mode   -- or 3
         ,p_is_revision_control      => FALSE
         ,p_is_lot_control           => l_lot_control_code   -- is_lot_control,
         ,p_is_serial_control        => l_serial_control_code
         ,p_revision                 => NULL   -- p_revision,
         ,p_lot_number               => p_lot_number   -- p_lot_number,
         ,p_lot_expiration_date      => SYSDATE
         ,p_subinventory_code        => p_subinv_code   -- p_subinventory,
         ,p_locator_id               => p_locator_id
         ,p_cost_group_id            => NULL
         ,p_onhand_source            => 3   -- Can also pass NULL
         ,x_qoh                      => l_qty_on_hand   -- Quantity on-hand
         ,x_rqoh                     => l_res_qty_on_hand   --reservable quantity on-hand
         ,x_qr                       => l_qty_reserved
         ,x_qs                       => l_qty_suggested
         ,x_att                      => l_avail_to_tnsct   -- available to transact
         ,x_atr                      => l_avail_to_reserve   -- available to reserve
         );
   DBMS_OUTPUT.put_line ('On hand Quantity: ' || l_qty_on_hand);
   DBMS_OUTPUT.put_line ('Avaialble to Reserve : ' || l_avail_to_reserve);
   RETURN NVL (l_avail_to_reserve, 0);
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('ERROR: ' || SQLERRM);
      RETURN 0;
END;

Note: For some reason i am not able to achieve right results even after passing the correct values and initializing correctly through SQL/Concurrent Prgoram. After hours of debug i found that this API cannot be run directly from SQL/Toad just by declare begin and end. It has to be created as function/procedure. Also function created cannot be called directly . Its advisable to create a main function which in turn calls the quantity API. This was i was able to get right quantity. 

Here is example :

function quantity_call (arguments)
RETURN NUMBER IS
l_avail_qty NUMBER;
BEGIN
---This condition is to clear the quantity with incorrect inputs by hardcoding
         ln_avaialble_qty :=
              avail_to_reserve (1234567890, 'AAAA', 12345000, 'AAA.3.0', organization_id);
---Then pass correct values         
ln_avaialble_qty := avail_to_reserve (arguments);

 ---This condition is to clear the quantity with  incorrect inputs and try again
     IF ln_avaialble_qty = 0
    THEN
      ---This condition is to clear the quantity with incorrect inputs by hardcoding
         ln_avaialble_qty :=
              avail_to_reserve (1234567890, 'AAAA', 12345000, 'AAA.3.0', organization_id);
     ---Then pass correct values  
            ln_avaialble_qty := avail_to_reserve (arguments);
         END IF;

l_avail_qty := availalbe_to_reserve (arguments);
RETURN  l_avail_qty;
END;

Use quantity_call (arguments) where ever needed.