DECLARE
p_item VARCHAR2;
p_organization_id NUMBER;
p_subinventory VARCHAR2;
p_locater VARCHAR2;
p_transfer_cost_group VARCHAR2;
r_mtl_trns_rec mtl_transactions_interface%ROWTYPE;
x_return_status VARCHAR2 (3000);
x_msg_count NUMBER;
x_msg_data VARCHAR2 (3000);
l_transaction_id NUMBER;
lc_result NUMBER;
l_trans_count NUMBER;
ln_responsbility_id NUMBER;
g_l_transaction_id NUMBER;
lv_error_flag VARCHAR2 (2);
ln_inventory_item_id mtl_system_items_b.inventory_item_id%TYPE;
lv_inventory_item_status_code mtl_system_items_b.inventory_item_status_code%TYPE;
ln_c_transaction_type_id mtl_transaction_types.transaction_type_id%TYPE;
lv_c_transaction_type_name mtl_transaction_types.transaction_type_name%TYPE;
ln_c_trans_source_type_id mtl_transaction_types.transaction_source_type_id%TYPE;
ln_c_transaction_action_id mtl_transaction_types.transaction_action_id%TYPE;
lv_subinventory mtl_secondary_inventories.secondary_inventory_name%TYPE;
ln_loceter_id mtl_item_locations_kfv.inventory_location_id%TYPE;
lv_subinv_code mtl_item_locations_kfv.subinventory_code%TYPE;
ln_tranfer_cost_group_id cst_cost_groups.cost_group_id%TYPE;
l_primary_uom mtl_system_items_b.primary_unit_of_measure%TYPE;
ln_operating_unit org_organization_definitions.operating_unit%TYPE;
lv_current_cost_group cst_cost_groups.cost_group%TYPE;
ln_current_cost_group_id cst_cost_groups.cost_group_id%TYPE;
ln_on_hand_qty NUMBER;
BEGIN
lv_error_flag := NULL;
lv_subinventory := NULL;
lv_inventory_item_status_code := NULL;
ln_inventory_item_id := NULL;
ln_tranfer_cost_group_id := NULL;
lv_current_cost_group := NULL;
ln_current_cost_group_id := NULL;
DBMS_OUTPUT.put_line ( ‘Item Number ‘
|| p_item
|| ‘ Organization ID ‘
|| p_organization_id
|| ‘ Subinventory ‘
|| p_subinventory
|| ‘ Locater ‘
|| p_locater
|| ‘ Cost Group to Transfer ‘
|| p_transfer_cost_group
);
IF p_item IS NULL
OR p_organization_id IS NULL
OR p_subinventory IS NULL
OR p_locater IS NULL
OR p_transfer_cost_group IS NULL
THEN
lv_error_flag := ‘E’;
DBMS_OUTPUT.put_line ( ‘Input Parameter is Null ‘
|| ‘ Oraganization ‘
|| p_organization_id
|| ‘ Item ‘
|| p_item
|| ‘ Suninventory ‘
|| p_subinventory
|| ‘ Locater ‘
|| p_locater
|| ‘ Transfer Cost Group ‘
|| p_transfer_cost_group
);
END IF;
–Operating unit id selection for APPS IN
IF NVL (lv_error_flag, ‘X’) <> ‘E’
THEN
BEGIN
SELECT operating_unit
INTO ln_operating_unit
FROM org_organization_definitions
WHERE organization_id = p_organization_id;
EXCEPTION
WHEN OTHERS
THEN
lv_error_flag := ‘E’;
ln_operating_unit := NULL;
fnd_file.put_line
(fnd_file.LOG,
‘Not Available feth Operating Unit for the Org ID ‘
|| p_organization_id
);
END;
END IF;
— Inventory item id and item status selection
IF NVL (lv_error_flag, ‘X’) <> ‘E’
THEN
BEGIN
SELECT inventory_item_id, inventory_item_status_code,
primary_unit_of_measure
INTO ln_inventory_item_id, lv_inventory_item_status_code,
l_primary_uom
FROM mtl_system_items_b
WHERE segment1 = p_item AND organization_id = p_organization_id;
EXCEPTION
WHEN OTHERS
THEN
lv_error_flag := ‘E’;
lv_inventory_item_status_code := NULL;
ln_inventory_item_id := NULL;
l_primary_uom := NULL;
DBMS_OUTPUT.put_line ( ‘Item Number Not Available ‘
|| p_item
|| ‘ Organiozation ID ‘
|| p_organization_id
);
END;
END IF;
DBMS_OUTPUT.put_line (‘Item Status ‘ || lv_inventory_item_status_code);
IF NVL (lv_error_flag, ‘X’) <> ‘E’
THEN
BEGIN
— set the error for still (after run item status prg )item status is not active
IF lv_inventory_item_status_code IS NOT NULL
AND lv_inventory_item_status_code NOT IN (‘Active’, ‘EXCESS’)
AND NVL (lv_error_flag, ‘X’) <> ‘E’
THEN
lv_error_flag := ‘E’;
DBMS_OUTPUT.put_line ( ‘ Item Status# ‘
|| lv_inventory_item_status_code
);
END IF;
END;
END IF;
IF NVL (lv_error_flag, ‘X’) <> ‘E’
THEN
— Transaction Type and Source Type Selection
BEGIN
SELECT mtt.transaction_type_id, mtt.transaction_type_name,
mtt.transaction_source_type_id, transaction_action_id
INTO ln_c_transaction_type_id, lv_c_transaction_type_name,
ln_c_trans_source_type_id, ln_c_transaction_action_id
FROM mtl_transaction_types mtt, mtl_txn_source_types mtst
WHERE 1 = 1
AND mtt.transaction_source_type_id =
mtst.transaction_source_type_id
AND mtst.transaction_source_type_name = ‘Inventory’
AND mtt.transaction_type_name = ‘Cost Group Transfer’;
EXCEPTION
WHEN OTHERS
THEN
lv_error_flag := ‘E’;
ln_c_transaction_type_id := NULL;
lv_c_transaction_type_name := NULL;
ln_c_trans_source_type_id := NULL;
ln_c_transaction_action_id := NULL;
fnd_file.put_line
(fnd_file.LOG,
‘Cost Group Transfer Tranaction Type Not Available ‘
);
END;
END IF;
IF NVL (lv_error_flag, ‘X’) <> ‘E’
THEN
— Valdiate the subinventory code
BEGIN
SELECT secondary_inventory_name
INTO lv_subinventory
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND UPPER (secondary_inventory_name) =
UPPER (TRIM (p_subinventory));
EXCEPTION
WHEN OTHERS
THEN
lv_error_flag := ‘E’;
DBMS_OUTPUT.put_line ( ‘Subinventory Code Not Available ‘
|| p_subinventory
|| ‘ Org ID ‘
|| p_organization_id
);
END;
END IF;
IF NVL (lv_error_flag, ‘X’) <> ‘E’
THEN
—Select the locater id
BEGIN
SELECT inventory_location_id
INTO ln_loceter_id
FROM mtl_item_locations_kfv
WHERE concatenated_segments = p_locater
AND organization_id = p_organization_id
AND subinventory_code = lv_subinventory;
EXCEPTION
WHEN OTHERS
THEN
ln_loceter_id := NULL;
lv_subinv_code := NULL;
lv_error_flag := ‘E’;
DBMS_OUTPUT.put_line ( ‘Locater name not available ‘
|| p_locater
|| ‘ for the subinventory ‘
|| lv_subinventory
|| ‘ Org ID ‘
|| p_organization_id
);
END;
END IF;
IF NVL (lv_error_flag, ‘X’) <> ‘E’
THEN
— Valdiate the Transfer cost group name
BEGIN
SELECT cost_group_id
INTO ln_tranfer_cost_group_id
FROM cst_cost_groups
WHERE UPPER (cost_group) = UPPER (TRIM (p_transfer_cost_group));
EXCEPTION
WHEN OTHERS
THEN
lv_error_flag := ‘E’;
ln_tranfer_cost_group_id := NULL;
fnd_file.put_line
(fnd_file.LOG,
‘Input Transfer Cost Group is not Available ‘
|| p_transfer_cost_group
);
END;
END IF;
IF NVL (lv_error_flag, ‘X’) <> ‘E’
THEN
–Select the current cost group
BEGIN
SELECT c.cost_group, b.cost_group_id
INTO lv_current_cost_group, ln_current_cost_group_id
FROM apps.mtl_onhand_total_v b,
mtl_uom_conversions a,
cst_cost_groups c
WHERE b.organization_id = p_organization_id
AND c.organization_id = b.organization_id
AND b.cost_group_id = c.cost_group_id
AND subinventory_code = lv_subinventory
AND b.locator_id = ln_loceter_id
AND a.inventory_item_id = b.inventory_item_id
AND b.inventory_item_id = ln_inventory_item_id
AND b.uom = a.uom_code
GROUP BY b.item,
b.item_description,
b.subinventory_code,
b.uom,
a.conversion_rate,
b.cost_group_id,
c.cost_group;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
lv_error_flag := ‘E’;
lv_current_cost_group := NULL;
ln_current_cost_group_id := NULL;
fnd_file.put_line
(fnd_file.LOG,
‘Not Available to found the current cost group for the irem ‘
|| p_item
|| ‘ Suninventory ‘
|| lv_subinventory
|| ‘ Organization ID ‘
|| p_organization_id
);
WHEN TOO_MANY_ROWS
THEN
lv_error_flag := ‘E’;
lv_current_cost_group := NULL;
ln_current_cost_group_id := NULL;
DBMS_OUTPUT.put_line ( ‘To many rows found ‘
|| p_item
|| ‘ Suninventory ‘
|| lv_subinventory
|| ‘ Organization ID ‘
|| p_organization_id
);
END;
END IF;
IF NVL (lv_error_flag, ‘X’) <> ‘E’
THEN
— Onhand Qty
ln_on_hand_qty := ln_on_hand_qty; –Item Onhand Qty
END IF;
IF NVL (lv_error_flag, ‘X’) <> ‘E’
THEN
DBMS_OUTPUT.put_line ( ‘Inventory Item ID ‘
|| ln_inventory_item_id
|| ‘ Organization ID ‘
|| p_organization_id
|| ‘ Suninventory ‘
|| lv_subinventory
|| ‘ Locater ID ‘
|| ln_loceter_id
|| ‘ Current Cost Group ID ‘
|| ln_current_cost_group_id
|| ‘ Transfer Cost group id ‘
|| ln_tranfer_cost_group_id
|| ‘ Onhand qty ‘
|| ln_on_hand_qty
);
BEGIN
BEGIN
–Apps Initialize
IF ln_operating_unit = 81
THEN
ln_responsbility_id := 50173;
ELSIF ln_operating_unit = 82
THEN
ln_responsbility_id := 50406;
END IF;
call_fnd_global (l_user_id, ln_responsbility_id, l_appl_id);
BEGIN
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_transaction_id
FROM DUAL;
EXCEPTION
WHEN OTHERS
THEN
l_transaction_id := NULL;
END;
BEGIN
IF l_primary_uom = ‘Each’
THEN
l_primary_uom := ‘EA’;
ELSIF l_primary_uom = ‘Case’
THEN
l_primary_uom := ‘CA’;
END IF;
END;
r_mtl_trns_rec := NULL;
r_mtl_trns_rec.transaction_uom := l_primary_uom;
r_mtl_trns_rec.transaction_date := SYSDATE;
r_mtl_trns_rec.source_code := ‘CGXFR’;
r_mtl_trns_rec.source_line_id := l_transaction_id;
r_mtl_trns_rec.source_header_id := l_transaction_id;
r_mtl_trns_rec.process_flag := 1;
r_mtl_trns_rec.transaction_mode := 3;
r_mtl_trns_rec.lock_flag := 2;
r_mtl_trns_rec.locator_id := ln_loceter_id;
r_mtl_trns_rec.last_update_date := SYSDATE;
r_mtl_trns_rec.last_updated_by := fnd_global.user_id;
r_mtl_trns_rec.creation_date := SYSDATE;
r_mtl_trns_rec.created_by := fnd_global.user_id;
r_mtl_trns_rec.inventory_item_id := ln_inventory_item_id;
r_mtl_trns_rec.subinventory_code := lv_subinventory;
r_mtl_trns_rec.organization_id := p_organization_id;
r_mtl_trns_rec.transaction_quantity := ln_on_hand_qty;
r_mtl_trns_rec.transaction_type_id := ln_c_transaction_type_id;
r_mtl_trns_rec.transaction_interface_id := l_transaction_id;
r_mtl_trns_rec.transaction_header_id := l_transaction_id;
r_mtl_trns_rec.transaction_reference := NULL;
r_mtl_trns_rec.item_segment1 := p_item;
r_mtl_trns_rec.transfer_cost_group_id := ln_tranfer_cost_group_id;
r_mtl_trns_rec.cost_group_id := ln_current_cost_group_id;
— r_mtl_trns_rec.transaction_source_type_id :=
— ln_c_trans_source_type_id
— r_mtl_trns_rec.transaction_action_id := ln_c_transaction_action_id
— Insert MTI for inv open transactions.
INSERT INTO mtl_transactions_interface
VALUES r_mtl_trns_rec;
DBMS_OUTPUT.put_line
( ‘ #Cost Group Program–Record inserted into mtl_transactions_interface with transaction_header_id: ‘
|| l_transaction_id
);
g_l_transaction_id := l_transaction_id;
–dbms_output.put_line(‘Inserted a record’||l_transaction_id);
x_return_status := fnd_api.g_ret_sts_success;
EXCEPTION
WHEN OTHERS
THEN
DBMS_OUTPUT.put_line
( ‘ #Cost Group Program–in exception —- inserting data into MTL_TRANSACTIONS_INTERFACE : ‘
|| SQLERRM
);
DBMS_OUTPUT.put_line (‘ #Cost Group Program–Error’
|| SQLERRM
);
x_return_status := fnd_api.g_ret_sts_error;
x_msg_count := x_msg_count + 1;
x_msg_data :=
‘Error While Inserting data to MTL_TRANSACTIONS_INTERFACE :’
|| SQLERRM;
lv_error_flag := ‘E’;
END;
IF x_return_status != fnd_api.g_ret_sts_error
THEN –Successful return of first insert
–call Inventory open transaction api to perform transfer of material from source locator to destination locator.
DBMS_OUTPUT.put_line
( ‘ #Cost Group Program– Call Inventory Open Tranaction API ‘
|| ‘Tranasaction ID ‘
|| l_transaction_id
);
x_return_status := NULL;
x_msg_count := 0;
x_msg_data := NULL;
lc_result :=
inv_txn_manager_pub.process_transactions
(p_api_version => 1.0,
p_init_msg_list => fnd_api.g_false,
p_commit => fnd_api.g_true,
p_validation_level => fnd_api.g_valid_level_full,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
x_trans_count => l_trans_count,
p_table => 1,
p_header_id => l_transaction_id
);
DBMS_OUTPUT.put_line
( ‘ #Cost Group Program–Return Status $$$’
|| x_return_status
|| ‘ $$$ ‘
|| fnd_api.g_ret_sts_success
);
IF NVL (x_return_status, ‘N’) <> fnd_api.g_ret_sts_success
THEN
— ERROR PART
x_return_status := fnd_api.g_ret_sts_error;
DBMS_OUTPUT.put_line ( ‘ #Cost Group Program–Message: ‘
|| x_msg_data
);
DBMS_OUTPUT.put_line (‘ #Cost Group Program–Error Status’);
END IF;
IF NVL (x_return_status, ‘N’) = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line
(‘ #Cost Group Program Completed–Sucess’);
END IF;
END IF;
COMMIT;
END;
ELSE
DBMS_OUTPUT.put_line (‘Cost Group Transfer Program Not Calling’);
END IF;
END;
Summary
This Post described the script/logic required for change the item cost group from back end in Oracle EBS.
Got any queries?
Do drop a note by writing us at venkatesh.b@staging.doyensys.com or use the comment section below to ask your questions
