[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
This procedure is to validate and check whether to insert/update
for the input values assignment_group_id ,abc_class_id and item_id combination
** */
PROCEDURE Create_abc_assignments (p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
p_commit IN VARCHAR2 := fnd_api.g_false,
p_api_version_number IN NUMBER,
p_assignment_group_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_abc_class_id IN NUMBER,
x_return_status OUT nocopy VARCHAR2,
x_msg_count OUT nocopy NUMBER,
x_msg_data OUT nocopy VARCHAR2)
IS
l_debug NUMBER := Nvl(fnd_profile.Value('INV_DEBUG_TRACE'), 0);
l_last_updated_by mtl_abc_assignments.last_updated_by%TYPE;
l_last_update_date mtl_abc_assignments.last_update_date%TYPE;
SELECT assignment_group_id,
organization_id
FROM mtl_abc_assignment_groups
WHERE assignment_group_id = p_assignment_group_id;
SELECT inventory_item_id
FROM mtl_system_items_fvl
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id
AND enabled_flag = 'Y'
AND trunc(SYSDATE) between nvl(trunc(start_date_active),trunc(SYSDATE)) and nvl(trunc(end_date_active),trunc(SYSDATE)) ;
SELECT abc_class_id
FROM mtl_abc_assgn_group_classes
WHERE assignment_group_id = p_assignment_group_id
AND abc_class_id = p_abc_class_id;
SELECT abc_class_id
FROM mtl_abc_assignments
WHERE inventory_item_id = p_item_id
AND assignment_group_id = p_assignment_group_id;
l_last_updated_by := FND_GLOBAL.USER_ID; --- Needs to be updated
l_last_update_date := SYSDATE; -- Needs to be updated
l_created_by := FND_GLOBAL.USER_ID; -- Needs to be updated
l_creation_date := SYSDATE; -- Needs to be updated
update_abc_assignments ( p_assignment_group_id => l_assignment_group_id
, p_inventory_item_id => l_item_id
, p_abc_class_id => l_abc_class_id
, p_last_updated_by => l_last_updated_by
, p_last_update_date => l_last_update_date);
|| ' Inserting new record with details '
|| ' Item_id : ' || l_item_id
|| ' AssignmentGroupID : ' || l_assignment_group_id
|| ' Class Id: ' || l_abc_class_id);
insert_abc_assignments ( p_assignment_group_id => l_assignment_group_id
, p_inventory_item_id => l_item_id
, p_abc_class_id => l_abc_class_id
, p_last_updated_by => l_last_updated_by
, p_last_update_date => l_last_update_date
, p_created_by => l_created_by
, p_creation_date => l_creation_date);
Procedure : Insert_abc_assignments
This procedure is to insert the new abc assignment for the input values
assignment_group_id ,abc_class_id and item_id combination
** */
PROCEDURE Insert_abc_assignments ( p_assignment_group_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_abc_class_id IN NUMBER
, p_last_updated_by IN VARCHAR2
, p_last_update_date IN DATE
, p_created_by IN VARCHAR2
, p_creation_date IN DATE
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_ABC_ASSIGNMENTS';
Mydebug(l_api_name ||' Inserting new record into MTL_ABC_ASSIGNMENTS ');
INSERT INTO mtl_abc_assignments
( inventory_item_id
, assignment_group_id
, abc_class_id
, last_update_date
, last_updated_by
, creation_date
, created_by )
VALUES ( p_inventory_item_id
, p_assignment_group_id
, p_abc_class_id
, p_last_update_date
, p_last_updated_by
, p_creation_date
, p_created_by);
fnd_msg_pub.Add_exc_msg (g_pkg_name, 'insert_abc_assignments');
END insert_abc_assignments;
Procedure : Update_abc_assignments
This procedure is to update the new class id for the existing assignment with
the given item and abc group combination
** */
PROCEDURE Update_abc_assignments ( p_assignment_group_id IN NUMBER
, p_inventory_item_id IN NUMBER
, p_abc_class_id IN NUMBER
, p_last_updated_by IN VARCHAR2
, p_last_update_date IN DATE)
IS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ABC_ASSIGNMENTS';
UPDATE mtl_abc_assignments
SET abc_class_id = p_abc_class_id,
last_updated_by = p_last_updated_by,
last_update_date = p_last_update_date
WHERE assignment_group_id = p_assignment_group_id
AND inventory_item_id = p_inventory_item_id;
fnd_msg_pub.Add_exc_msg (g_pkg_name, 'update_abc_assignments');
END update_abc_assignments;