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