DECLARE
v_api_return_status VARCHAR2 (1);
v_qty_oh NUMBER;
v_qty_res_oh NUMBER;
v_qty_res NUMBER;
v_qty_sug NUMBER;
v_qty_att NUMBER;
v_qty_atr NUMBER;
v_msg_count NUMBER;
v_msg_data VARCHAR2 (1000);
part_number VARCHAR2 (40);
organization VARCHAR2 (40);
locator VARCHAR2 (100);
CURSOR C1
IS
SELECT ORGANIZATION_ID,
INVENTORY_ITEM_ID,
SUBINVENTORY_CODE,
LOCATOR_ID
FROM MTL_ONHAND_QUANTITIES_DETAIL
ORDER BY ORGANIZATION_ID, INVENTORY_ITEM_ID;
BEGIN
delete from item_tmp;
commit;
FOR I IN C1
LOOP
part_number := NULL;
organization := NULL;
inv_quantity_tree_grp.clear_quantity_cache;
apps.INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES (
p_api_version_number => 1.0,
p_init_msg_lst => apps.fnd_api.g_false,
x_return_status => v_api_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
p_organization_id => i.organization_id,
p_inventory_item_id => i.inventory_item_id,
p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,
p_onhand_source => 3,
p_is_revision_control => FALSE,
p_is_lot_control => FALSE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => i.subinventory_code,
p_locator_id => i.locator_id,
x_qoh => v_qty_oh,
x_rqoh => v_qty_res_oh,
x_qr => v_qty_res,
x_qs => v_qty_sug,
x_att => v_qty_att,
x_atr => v_qty_atr
);
SELECT segment1
INTO part_number
FROM mtl_system_items_b
WHERE inventory_item_id = i.inventory_item_id
AND organization_id = i.organization_id;
SELECT organization_name
INTO organization
FROM org_organization_Definitions
WHERE organization_id = i.organization_id;
BEGIN
SELECT concatenated_segments
INTO locator
FROM MTL_ITEM_LOCATIONS_kfv
WHERE inventory_location_id = i.locator_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
locator := NULL;
END;
IF V_QTY_ATR <0 THEN
INSERT INTO item_tmp
VALUES (part_number,
organization,
v_qty_oh,
v_qty_att,
v_qty_atr,
i.subinventory_code,
locator);
COMMIT;
END IF;
END LOOP;
END;
v_api_return_status VARCHAR2 (1);
v_qty_oh NUMBER;
v_qty_res_oh NUMBER;
v_qty_res NUMBER;
v_qty_sug NUMBER;
v_qty_att NUMBER;
v_qty_atr NUMBER;
v_msg_count NUMBER;
v_msg_data VARCHAR2 (1000);
part_number VARCHAR2 (40);
organization VARCHAR2 (40);
locator VARCHAR2 (100);
CURSOR C1
IS
SELECT ORGANIZATION_ID,
INVENTORY_ITEM_ID,
SUBINVENTORY_CODE,
LOCATOR_ID
FROM MTL_ONHAND_QUANTITIES_DETAIL
ORDER BY ORGANIZATION_ID, INVENTORY_ITEM_ID;
BEGIN
delete from item_tmp;
commit;
FOR I IN C1
LOOP
part_number := NULL;
organization := NULL;
inv_quantity_tree_grp.clear_quantity_cache;
apps.INV_QUANTITY_TREE_PUB.QUERY_QUANTITIES (
p_api_version_number => 1.0,
p_init_msg_lst => apps.fnd_api.g_false,
x_return_status => v_api_return_status,
x_msg_count => v_msg_count,
x_msg_data => v_msg_data,
p_organization_id => i.organization_id,
p_inventory_item_id => i.inventory_item_id,
p_tree_mode => apps.inv_quantity_tree_pub.g_transaction_mode,
p_onhand_source => 3,
p_is_revision_control => FALSE,
p_is_lot_control => FALSE,
p_is_serial_control => FALSE,
p_revision => NULL,
p_lot_number => NULL,
p_subinventory_code => i.subinventory_code,
p_locator_id => i.locator_id,
x_qoh => v_qty_oh,
x_rqoh => v_qty_res_oh,
x_qr => v_qty_res,
x_qs => v_qty_sug,
x_att => v_qty_att,
x_atr => v_qty_atr
);
SELECT segment1
INTO part_number
FROM mtl_system_items_b
WHERE inventory_item_id = i.inventory_item_id
AND organization_id = i.organization_id;
SELECT organization_name
INTO organization
FROM org_organization_Definitions
WHERE organization_id = i.organization_id;
BEGIN
SELECT concatenated_segments
INTO locator
FROM MTL_ITEM_LOCATIONS_kfv
WHERE inventory_location_id = i.locator_id;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
locator := NULL;
END;
IF V_QTY_ATR <0 THEN
INSERT INTO item_tmp
VALUES (part_number,
organization,
v_qty_oh,
v_qty_att,
v_qty_atr,
i.subinventory_code,
locator);
COMMIT;
END IF;
END LOOP;
END;
No comments:
Post a Comment