Lot-Wise Onhand Availability across Inventory Orgs

Standard functionality
in the SO Form, provides the On-Hand visibility of an Item at the Specific
Inventory Org (Ship From Org) level.  In case the Visibility is required
as per below factors then this code can be used:
–          Lot-Wise
–          Visibility
across Interconnected Org’s
–          Availability
of Substitute Items

This component uses
standard API  “Inv_quantity_tree_pub.query_quantities” for
calculating lot wise availability like lot wise on hand, reservable and transaction
Quantities 


Reference Code:

PROCEDURE apps.xx_onhand_visibility (
  
p_item_id     
IN       NUMBER,
  
p_org_id      
IN       NUMBER,
  
p_session_id  
IN       NUMBER,
   x_error_msg    OUT      VARCHAR2
)
IS
  
x_return_status         
VARCHAR2 (50);
  
x_msg_count             
VARCHAR2 (50);
  
x_msg_data              
VARCHAR2 (1000);
  
lc_item_id              
NUMBER;
  
lc_qty_on_hand          
NUMBER;
  
lc_res_qty_on_hand      
NUMBER;
  
lc_avail_to_tnsct       
NUMBER;
  
lc_avail_to_reserve     
NUMBER;
  
lc_qty_reserved         
NUMBER;
  
lc_qty_suggested        
NUMBER;
  
lb_lot_control_code     
BOOLEAN;
  
lb_serial_control_code  
BOOLEAN;
  
lc_lot_number            
VARCHAR2 (80);
  
lc_message              
VARCHAR2 (100);
  
lc_lot_count            
NUMBER;
  
lc_uom_code             
VARCHAR2 (3)     :=
NULL;
  
lc_rel_item_id          
NUMBER;
  
lc_sessionid            
NUMBER;
  
lc_item_type            
VARCHAR2 (10)    :=
NULL;
   /* Initialization Parameters
for the API */
  
lc_resp_id     
CONSTANT NUMBER           :=
apps
.fnd_global.resp_id;
  
lc_appl_id     
CONSTANT NUMBER           :=
apps
.fnd_global.resp_appl_id;
  
lc_user_id     
CONSTANT NUMBER           :=
apps
.fnd_global.user_id;
  
lc_sec_grp_id  
CONSTANT NUMBER       :=
apps
.fnd_global.security_group_id;
  
lc_master_org_id        
NUMBER;
   /* For Capturing Bulk
Collection Errors */
  
ex_dml_errors           
EXCEPTION;
   PRAGMA EXCEPTION_INIT (ex_dml_errors, 24381);
  
lc_error_count          
NUMBER;
   /* Bulk Collection */
   TYPE lt_onhand_tab IS TABLE OF xx_onhand%ROWTYPE;
  
lt_fs_tbl                lt_onhand_tab
:= lt_onhand_tab ();
   /* Cursor to Select the Related
Organizations based on the Shipping NW if defined */
   CURSOR lcu_org_list (cp_org_id NUMBER)
   IS
      SELECT from_organization_id orgid, org1.organization_name org_name
        FROM mtl_shipping_network_view v1, org_organization_definitions
org1
       WHERE (   v1.from_organization_id
= cp_org_id
              OR v1.to_organization_id = cp_org_id
             )
         AND NVL (v1.attribute1, ‘N’) = ‘Y’
         AND org1.organization_id = from_organization_id
      UNION
      SELECT to_organization_id orgid, org2.organization_name org_name
        FROM mtl_shipping_network_view v1, org_organization_definitions
org2
       WHERE (   v1.from_organization_id
= cp_org_id
              OR v1.to_organization_id = cp_org_id
             )
         AND NVL (v1.attribute1, ‘N’) = ‘Y’
         AND org2.organization_id = to_organization_id
      UNION
      SELECT organization_id orgid, organization_name org_name
        FROM org_organization_definitions
       WHERE organization_id = cp_org_id;
   /* Cursor to Select the LOT
and Sub-inventory Details for a given Item and Org       Id. Pick ONLY RESERVABLE
LOTS */
   CURSOR lcu_lot_det (cp_item_id NUMBER, cp_org_id NUMBER)
   IS
      SELECT DISTINCT mlt.inventory_item_id, mlt.organization_id,
                  
   mlt
.lot_number,
                     
CEIL (SYSDATE mlt.creation_date) age_days, mlt.LENGTH,
                     
mlt
.length_uom, mlt.creation_date,
                     
oh
.subinventory_code
sub_inv_code
, oh.uom uom,
                     
mlt
.attribute4 nsl_type
                
FROM mtl_lot_numbers_all_v mlt,
                     
mtl_onhand_total_mwb_v oh
,
                     
mtl_secondary_inventories sub
,
                     
mtl_material_statuses_vl mls
               
WHERE mlt.inventory_item_id = cp_item_id
                 
AND mlt.organization_id = cp_org_id
                 
AND oh.organization_id = mlt.organization_id
                 
AND oh.inventory_item_id = mlt.inventory_item_id
                 
AND oh.lot_number = mlt.lot_number
                  AND sub.secondary_inventory_name = oh.subinventory_code
                 
AND sub.organization_id = oh.organization_id
                 
AND mls.status_id = mlt.status_id
                 
AND mls.reservable_type = 1;
   /* Cursor to Select the
Related Item Details for a given Item and Org Id. Item Relations are always
defined
       */
   CURSOR lcu_rel_item (
     
cp_item_id     
NUMBER,
     
cp_org_id      
NUMBER,
      cp_master_org   NUMBER
   )
   IS
      SELECT DISTINCT itm.inventory_item_id inv_item_id,
                     
itm
.segment1 itm_code
                
FROM mtl_related_items_all_v rel1, mtl_system_items_b itm
               
WHERE (   rel1.inventory_item_id
= cp_item_id
                       OR rel1.related_item_id = cp_item_id
                     
)
                 
AND rel1.organization_id = cp_master_org
                 
AND rel1.relationship_type_id = 1                 — Related
              
   
AND itm.organization_id = rel1.organization_id
                 
AND (   itm.inventory_item_id
= rel1.related_item_id
                       OR itm.inventory_item_id = rel1.inventory_item_id
                     
)
      UNION
      SELECT itm.inventory_item_id inv_item_id, itm.segment1 itm_code
        FROM mtl_system_items_b itm
       WHERE itm.inventory_item_id = cp_item_id
         AND itm.organization_id = cp_org_id;
BEGIN
  
lc_sessionid
:= p_session_id;
   /* GET THE IMO Organization
ID. All Item Relations are Defined at IMO level */
   BEGIN
      SELECT organization_id
        INTO lc_master_org_id
        FROM org_organization_definitions
org
       WHERE organization_code = ‘IMO’;
   EXCEPTION
      WHEN NO_DATA_FOUND
      THEN
         lc_master_org_id
:= NULL;
      WHEN OTHERS
      THEN
        
lc_master_org_id
:= NULL;
   END;
   /* Getting the List of Items
both Base and Related */
   FOR c_itm IN lcu_rel_item (p_item_id, p_org_id, lc_master_org_id)
   LOOP
      IF c_itm.inv_item_id = p_item_id
      THEN
        
lc_item_type
:= ‘Base’;
      ELSE
        
lc_item_type
:= ‘Related’;
      END IF;
      /* Getting the List of
Organizations  */
      FOR c_rec IN lcu_org_list (p_org_id)
      LOOP
        
lc_lot_count
:= 0;
         — To Check if LOT is defined or not for an Item and ORG
         /* Getting the List of
Organizations  */
         FOR c_lot IN lcu_lot_det (c_itm.inv_item_id, c_rec.orgid)
         LOOP
           
lc_lot_count
:= lc_lot_count + 1;
            BEGIN
               — Set the org context
              
fnd_global.apps_initialize (user_id                => lc_user_id,
                                          
resp_id               
=> lc_resp_id,
                                          
resp_appl_id           
=> lc_appl_id,
                                          
security_group_id     
=>                                                          lc_sec_grp_id
                                          );
              
inv_quantity_tree_grp.clear_quantity_cache;
              
— Clear Quantity
cache
               lb_lot_control_code := TRUE;
              
lb_serial_control_code
:= FALSE;
              
inv_quantity_tree_pub.query_quantities
                 
(p_api_version_number       => 1.0,
                  
p_init_msg_lst            
=> NULL,
                   x_return_status            => x_return_status,
                  
x_msg_count               
=> x_msg_count,
                  
x_msg_data                
=> x_msg_data,
                  
p_organization_id         
=> c_rec.orgid,
                   p_inventory_item_id        => c_itm.inv_item_id,
                  
p_tree_mode               
=>                                                     apps.inv_quantity_tree_pub.g_transaction_mode,
                  
p_is_revision_control     
=> FALSE,
                  
p_is_lot_control          
=> lb_lot_control_code,
                   p_is_serial_control        => lb_serial_control_code,
                  
p_revision                
=> NULL  — p_revision,
                   p_lot_number               => c_lot.lot_number.
                    p_lot_expiration_date      => SYSDATE,
                  
p_subinventory_code       
=> c_lot.sub_inv_code,
                   p_locator_id               => NULL        
                   x_qoh                      => lc_qty_on_hand
                   x_rqoh                     => lc_res_qty_on_hand
                   x_qr                       => lc_qty_reserved,
                  
x_qs                      
=> lc_qty_suggested,
                  
x_att                     
=> lc_avail_to_tnsct
                   x_atr                      => lc_avail_to_reserve
                 
— available to
reserve
                 
);
            END;
            /* Bulk Collection */
           
lt_fs_tbl
.EXTEND;
           
lt_fs_tbl
(lt_fs_tbl.LAST).session_id := lc_sessionid;
           
lt_fs_tbl
(lt_fs_tbl.LAST).header_id := NULL;
           
lt_fs_tbl
(lt_fs_tbl.LAST).line_id := NULL;
           
lt_fs_tbl
(lt_fs_tbl.LAST).item_type := lc_item_type;
           
lt_fs_tbl
(lt_fs_tbl.LAST).org_id := c_rec.orgid;
           
lt_fs_tbl
(lt_fs_tbl.LAST).wh_name := c_rec.org_name;
           
lt_fs_tbl
(lt_fs_tbl.LAST).item_id := c_itm.inv_item_id;
           
lt_fs_tbl
(lt_fs_tbl.LAST).item_code := c_itm.itm_code;
           
lt_fs_tbl
(lt_fs_tbl.LAST).lot_num := c_lot.lot_number;
           
lt_fs_tbl
(lt_fs_tbl.LAST).itm_length := c_lot.LENGTH;
           
lt_fs_tbl
(lt_fs_tbl.LAST).length_uom := c_lot.uom;
           
lt_fs_tbl
(lt_fs_tbl.LAST).qty_onhand := lc_qty_on_hand;
           
lt_fs_tbl
(lt_fs_tbl.LAST).reservbl_qoh := lc_res_qty_on_hand;
           
lt_fs_tbl
(lt_fs_tbl.LAST).reserved_qoh := lc_qty_reserved;
            lt_fs_tbl
(lt_fs_tbl.LAST).suggested_qty := lc_qty_suggested;
           
lt_fs_tbl
(lt_fs_tbl.LAST).transact_qty := lc_avail_to_tnsct;
           
lt_fs_tbl
(lt_fs_tbl.LAST).al_to_res_qty := lc_avail_to_reserve;
           
lt_fs_tbl
(lt_fs_tbl.LAST).sub_inv_code := c_lot.sub_inv_code;
           
lt_fs_tbl
(lt_fs_tbl.LAST).age_in_days := c_lot.age_days;
           
lt_fs_tbl
(lt_fs_tbl.LAST).xx_msg_count := x_msg_count;
            —  lt_fs_tbl(lt_fs_tbl.last).XX_MSG_DATA     := x_msg_data;
            lt_fs_tbl
(lt_fs_tbl.LAST).xx_msg_data := c_lot.nsl_type;
            — Using this field to
accommodate the NSL Type reqmt
           
lt_fs_tbl
(lt_fs_tbl.LAST).xx_ret_status := x_return_status;
         END LOOP;
         /* For items with NO lots
defined – Get the Onhand */
         IF lc_lot_count = 0
         THEN
            — Set the org context
            fnd_global.apps_initialize (user_id                => lc_user_id,
                                       
resp_id               
=> lc_resp_id,
                                       
resp_appl_id          
=> lc_appl_id,
                                       
security_group_id     
=> lc_sec_grp_id
                                       );
            inv_quantity_tree_grp.clear_quantity_cache;
            — Clear Quantity cache
            BEGIN
              
SELECT itm.primary_uom_code
                
INTO lc_uom_code
                
FROM mtl_system_items_b itm
               
WHERE inventory_item_id = c_itm.inv_item_id
                 
AND itm.organization_id = c_rec.orgid;
            EXCEPTION
              
WHEN NO_DATA_FOUND
              
THEN
                 
lc_uom_code
:= NULL;
            END;
           
lb_lot_control_code
:= FALSE;
           
lb_serial_control_code
:= FALSE;
            inv_quantity_tree_pub.query_quantities
               
(p_api_version_number       => 1.0,
                
p_init_msg_lst            
=> NULL,
                
x_return_status           
=> x_return_status,
                
x_msg_count                
=> x_msg_count,
                
x_msg_data                
=> x_msg_data,
                
p_organization_id         
=> c_rec.orgid,
                
p_inventory_item_id       
=> c_itm.inv_item_id,
                
p_tree_mode               
=>                                                    apps.inv_quantity_tree_pub.g_transaction_mode,
                
p_is_revision_control     
=> FALSE,
                
p_is_lot_control          
=> lb_lot_control_code,
                 p_is_serial_control        => lb_serial_control_code,
                
p_revision                
=> NULL,             — p_revision,
                 p_lot_number               => NULL,          — p_lot_number,
                 p_lot_expiration_date      => SYSDATE,
                
p_subinventory_code       
=> NULL  ,
                
p_locator_id              
=> NULL ,      
                 x_qoh                      => lc_qty_on_hand,
                 x_rqoh                     => lc_res_qty_on_hand,
                 x_qr                       => lc_qty_reserved,
                
x_qs                      
=> lc_qty_suggested,
                 x_att                      => lc_avail_to_tnsct ,
                
x_atr                     
=> lc_avail_to_reserve
               
— available to reserve
               
);
            /* Bulk Collection */
           
lt_fs_tbl
.EXTEND;
           
lt_fs_tbl
(lt_fs_tbl.LAST).session_id := lc_sessionid;
           
lt_fs_tbl
(lt_fs_tbl.LAST).header_id := NULL;
           
lt_fs_tbl
(lt_fs_tbl.LAST).line_id := NULL;
           
lt_fs_tbl
(lt_fs_tbl.LAST).item_type := lc_item_type;
           
lt_fs_tbl
(lt_fs_tbl.LAST).org_id := c_rec.orgid;
           
lt_fs_tbl
(lt_fs_tbl.LAST).wh_name := c_rec.org_name;
           
lt_fs_tbl
(lt_fs_tbl.LAST).item_id := c_itm.inv_item_id;
           
lt_fs_tbl
(lt_fs_tbl.LAST).item_code := c_itm.itm_code;
           
lt_fs_tbl
(lt_fs_tbl.LAST).lot_num := NULL;
           
lt_fs_tbl
(lt_fs_tbl.LAST).itm_length := NULL;
           
lt_fs_tbl
(lt_fs_tbl.LAST).length_uom := lc_uom_code;
           
lt_fs_tbl
(lt_fs_tbl.LAST).qty_onhand := lc_qty_on_hand;
           
lt_fs_tbl
(lt_fs_tbl.LAST).reservbl_qoh := lc_res_qty_on_hand;
           
lt_fs_tbl
(lt_fs_tbl.LAST).reserved_qoh := lc_qty_reserved;
           
lt_fs_tbl
(lt_fs_tbl.LAST).suggested_qty := lc_qty_suggested;
           
lt_fs_tbl
(lt_fs_tbl.LAST).transact_qty := lc_avail_to_tnsct;
           
lt_fs_tbl
(lt_fs_tbl.LAST).al_to_res_qty := lc_avail_to_reserve;
           
lt_fs_tbl
(lt_fs_tbl.LAST).sub_inv_code := NULL;
           
lt_fs_tbl
(lt_fs_tbl.LAST).age_in_days := NULL;
           
lt_fs_tbl
(lt_fs_tbl.LAST).xx_msg_count := x_msg_count;
            —    lt_fs_tbl(lt_fs_tbl.last).XX_MSG_DATA     := x_msg_data;
           
lt_fs_tbl
(lt_fs_tbl.LAST).xx_msg_data := NULL;
            — This field is used for LOT
data – NSL TYPE
           
lt_fs_tbl
(lt_fs_tbl.LAST).xx_ret_status := x_return_status;
         END IF;
         IF c_rec.orgid IS NOT NULL AND c_itm.inv_item_id IS NOT NULL
         THEN
            — Set the org context
            fnd_global.apps_initialize (user_id                => lc_user_id,
                                       
resp_id               
=> lc_resp_id,
                                       
resp_appl_id          
=> lc_appl_id,
                    
                   security_group_id     
=> lc_sec_grp_id
                                       );
            inv_quantity_tree_grp.clear_quantity_cache;
            — Clear Quantity cache
           
lb_lot_control_code
:= FALSE;
           
lb_serial_control_code
:= FALSE;
            inv_quantity_tree_pub.query_quantities
               
(p_api_version_number       => 1.0,
                
p_init_msg_lst            
=> NULL,
                
x_return_status           
=> x_return_status,
                 x_msg_count                => x_msg_count,
                
x_msg_data                
=> x_msg_data,
                
p_organization_id         
=> c_rec.orgid,
                
p_inventory_item_id       
=> c_itm.inv_item_id,
                
p_tree_mode               
=> apps.inv_quantity_tree_pub.g_transaction_mode,
                
p_is_revision_control     
=> FALSE,
                
p_is_lot_control          
=> lb_lot_control_code,
                 p_is_serial_control        => lb_serial_control_code,
                
p_revision                
=> NULL             — p_revision,
                 p_lot_number               => NULL           — p_lot_number,
                 p_lot_expiration_date      => SYSDATE,
                
p_subinventory_code       
=> NULL    — p_subinventory_code,
                 p_locator_id               => NULL           — p_locator_id,
                 x_qoh                      => lc_qty_on_hand
                 x_rqoh                     => lc_res_qty_on_hand
                 x_qr                       => lc_qty_reserved,
                
x_qs                      
=> lc_qty_suggested,
                
x_att                     
=> lc_avail_to_tnsct
                 x_atr                      => lc_avail_to_reserve
                — available to reserve
               
);
            /* Bulk Collection */
           
lt_fs_tbl
.EXTEND;
           
lt_fs_tbl
(lt_fs_tbl.LAST).session_id := lc_sessionid;
           
lt_fs_tbl
(lt_fs_tbl.LAST).header_id := NULL;
           
lt_fs_tbl
(lt_fs_tbl.LAST).line_id := NULL;
           
lt_fs_tbl
(lt_fs_tbl.LAST).item_type := UPPER (lc_item_type);
           
lt_fs_tbl
(lt_fs_tbl.LAST).org_id := c_rec.orgid;
           
lt_fs_tbl
(lt_fs_tbl.LAST).wh_name := c_rec.org_name;
           
lt_fs_tbl
(lt_fs_tbl.LAST).item_id := c_itm.inv_item_id;
           
lt_fs_tbl
(lt_fs_tbl.LAST).item_code := c_itm.itm_code;
           
lt_fs_tbl
(lt_fs_tbl.LAST).lot_num := NULL;
           
lt_fs_tbl
(lt_fs_tbl.LAST).itm_length := NULL;
           
lt_fs_tbl
(lt_fs_tbl.LAST).length_uom := lc_uom_code;
           
lt_fs_tbl
(lt_fs_tbl.LAST).qty_onhand := lc_qty_on_hand;
           
lt_fs_tbl
(lt_fs_tbl.LAST).reservbl_qoh := lc_res_qty_on_hand;
            lt_fs_tbl
(lt_fs_tbl.LAST).reserved_qoh := lc_qty_reserved;
           
lt_fs_tbl
(lt_fs_tbl.LAST).suggested_qty := lc_qty_suggested;
           
lt_fs_tbl
(lt_fs_tbl.LAST).transact_qty := lc_avail_to_tnsct;
           
lt_fs_tbl
(lt_fs_tbl.LAST).al_to_res_qty := lc_avail_to_reserve;
           
lt_fs_tbl
(lt_fs_tbl.LAST).sub_inv_code := NULL;
           
lt_fs_tbl
(lt_fs_tbl.LAST).age_in_days := NULL;
           
lt_fs_tbl
(lt_fs_tbl.LAST).xx_msg_count := x_msg_count;
            —    lt_fs_tbl(lt_fs_tbl.last).XX_MSG_DATA     := x_msg_data;
           
lt_fs_tbl
(lt_fs_tbl.LAST).xx_msg_data := NULL;
           
lt_fs_tbl
(lt_fs_tbl.LAST).xx_ret_status := x_return_status;
         END IF;
      END LOOP;
   END LOOP;
   /* BULK Collect */
   BEGIN
      FORALL i IN lt_fs_tbl.FIRST .. lt_fs_tbl.LAST SAVE EXCEPTIONS
         INSERT INTO xx_onhand
              VALUES lt_fs_tbl (i);
   EXCEPTION
      WHEN ex_dml_errors
      THEN
        
lc_error_count
:= SQL%BULK_EXCEPTIONS.COUNT;
         FOR i IN 1 .. lc_error_count
         LOOP
           
x_error_msg
:=
              
SUBSTR (   x_error_msg
                       || ‘-‘
                       || SQLERRM (-SQL%BULK_EXCEPTIONS (i).ERROR_CODE),
                       1,
                       1000
                     
);
         END LOOP;
   END;
END;

/
  • October 14, 2015 | 21 views