Wednesday, 14 October 2015

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;

/

No comments:

Post a Comment