Assignment
Set:
Once sourcing rules has been defined, you
must assign them to particular items and/or organizations. These assignments
are grouped together in assignment sets. In an assignment set you can assign
your sourcing rules at different levels as below,
Item,
Item Category,
Item-Organization,
Item Category-Organization,
Organization,
Global.
After defining the sourcing rule and assignment set one needs to enter the default assignment set to be used in the profile option MRP:Default Sourcing Assignment Set.
Possible
Validations:
1. Valid Sourcing Rule.
2. Valid Item.
3. Valid Organization.
Code
for reference:
PROCEDURE create_src_rule_assignment
IS
lc_return_status
VARCHAR2 (1);
ln_msg_count
NUMBER
:= 0;
lc_msg_data
VARCHAR2 (1000);
lc_msg_data_temp
VARCHAR2 (1000);
ln_msg_index_out NUMBER;
ln_count NUMBER;
ln_org_cnt NUMBER;
ln_vendor_cnt NUMBER;
lc_org_class VARCHAR2 (3);
ln_org_num NUMBER;
ln_line_num
NUMBER
:= 0;
ln_err_count
NUMBER
:= 0;
l_assignment_set_rec
mrp_src_assignment_pub.assignment_set_rec_type;
l_assignment_set_val_rec
mrp_src_assignment_pub.assignment_set_val_rec_type;
l_assignment_tbl
mrp_src_assignment_pub.assignment_tbl_type;
l_assignment_val_tbl
mrp_src_assignment_pub.assignment_val_tbl_type;
x_assignment_set_rec
mrp_src_assignment_pub.assignment_set_rec_type;
x_assignment_set_val_rec
mrp_src_assignment_pub.assignment_set_val_rec_type;
x_assignment_tbl mrp_src_assignment_pub.assignment_tbl_type;
x_assignment_val_tbl
mrp_src_assignment_pub.assignment_val_tbl_type;
ln_assigned_to NUMBER;
ln_org NUMBER;
ln_rule_id NUMBER;
ln_item NUMBER;
ln_assign_id NUMBER;
BEGIN
fnd_global.apps_initialize (fnd_profile.VALUE ('USER_ID'),
fnd_profile.VALUE ('RESP_ID'),
fnd_profile.VALUE
('RESP_APPL_ID'),
NULL,
NULL
);
FOR i IN (SELECT *
FROM XX_STG_TBL2
WHERE process_flag = 'V')
LOOP
ln_org := NULL;
ln_item := NULL;
ln_assigned_to := NULL;
ln_rule_id := NULL;
ln_assign_id := NULL;
BEGIN
SELECT organization_id
INTO ln_org
FROM org_organization_definitions
WHERE organization_code = i.organization_code;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error in
Selecting Organization'
);
END;
DBMS_OUTPUT.put_line ('After Organization Id');
BEGIN
SELECT inventory_item_id
INTO ln_item
FROM mtl_system_items_b
WHERE segment1 = i.item AND organization_id = ln_org;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error in Selecting item');
END;
DBMS_OUTPUT.put_line ('After Item Id');
BEGIN
SELECT sourcing_rule_id
INTO ln_rule_id
FROM mrp_sourcing_rules
WHERE sourcing_rule_name = i.sourcing_rule_name;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error in
Selecting sourcing rule'
);
END;
DBMS_OUTPUT.put_line ('After Sourcing Rule');
BEGIN
SELECT lookup_code
INTO ln_assigned_to
FROM mfg_lookups
WHERE lookup_type = 'MRP_ASSIGNMENT_TYPE'
AND UPPER (meaning) = UPPER
(i.assigned_to);
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error in
Selecting Assignment type'
);
END;
DBMS_OUTPUT.put_line ('After Assignment Set');
BEGIN
SELECT NVL (assignment_set_id, 0)
INTO ln_assign_id
FROM mrp_assignment_sets
WHERE UPPER (assignment_set_name) = UPPER (i.assignment_set);
EXCEPTION
WHEN NO_DATA_FOUND
THEN
ln_assign_id := 0;
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG,
'Error in
Selecting Assignment set id'
);
END;
IF ln_assign_id = 0
THEN
l_assignment_set_rec.assignment_set_name := i.assignment_set;
l_assignment_set_rec.description := i.description;
l_assignment_set_rec.operation := 'CREATE';
ELSE
l_assignment_set_rec.assignment_set_id := ln_assign_id;
l_assignment_set_rec.assignment_set_name := NULL;
l_assignment_set_rec.operation := NULL;
END IF;
l_assignment_tbl (1).assignment_type := ln_assigned_to;
l_assignment_tbl (1).operation := 'CREATE';
l_assignment_tbl
(1).organization_id := ln_org;
l_assignment_tbl (1).inventory_item_id := ln_item;
l_assignment_tbl (1).sourcing_rule_id := ln_rule_id;
l_assignment_tbl (1).sourcing_rule_type := 1;
DBMS_OUTPUT.put_line ('Before Assignment');
mrp_src_assignment_pub.process_assignment
(p_api_version_number
=> 1.0,
p_init_msg_list => 'T',
p_return_values => fnd_api.g_false,
p_commit => fnd_api.g_false,
x_return_status => lc_return_status,
x_msg_count => ln_msg_count,
x_msg_data => lc_msg_data,
p_assignment_set_rec
=> l_assignment_set_rec,
p_assignment_set_val_rec
=> l_assignment_set_val_rec,
p_assignment_tbl => l_assignment_tbl,
p_assignment_val_tbl
=> l_assignment_val_tbl,
x_assignment_set_rec
=> x_assignment_set_rec,
x_assignment_set_val_rec => x_assignment_set_val_rec,
x_assignment_tbl => x_assignment_tbl,
x_assignment_val_tbl
=> x_assignment_val_tbl
);
COMMIT;
IF lc_return_status = fnd_api.g_ret_sts_success
THEN
DBMS_OUTPUT.put_line ('Success!');
fnd_file.put_line (fnd_file.LOG, 'Success!');
BEGIN
UPDATE XX_STG_TBL2
SET process_flag = 'S',
assignment_set_id =
x_assignment_set_rec.assignment_set_id
WHERE record_id = i.record_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error in
Updating Assignment set id'
);
END;
ELSE
fnd_file.put_line (fnd_file.LOG, lc_return_status);
fnd_file.put_line (fnd_file.LOG, ln_msg_count);
DBMS_OUTPUT.put_line ('lc_return_status' || lc_return_status);
DBMS_OUTPUT.put_line ('count:' || ln_msg_count);
IF (ln_msg_count > 0)
THEN
FOR l_index IN 1 .. ln_msg_count
LOOP
lc_msg_data := lc_msg_data ||
CHR (13) || lc_msg_data_temp;
lc_msg_data_temp :=
fnd_msg_pub.get
(p_msg_index => l_index,
p_encoded => fnd_api.g_false
);
fnd_file.put_line
(fnd_file.LOG, lc_msg_data_temp);
END LOOP;
BEGIN
UPDATE XX_STG_TBL2
SET process_flag = 'E',
error_message =
lc_msg_data_temp
WHERE record_id =
i.record_id;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line
(fnd_file.LOG,
'Error
in Updating Error Flag'
);
DBMS_OUTPUT.put_line
('EXCEPTION' || SQLERRM);
END;
END IF;
COMMIT;
DBMS_OUTPUT.put_line
('Failure!');
END IF;
END LOOP;
EXCEPTION
WHEN OTHERS
THEN
fnd_file.put_line (fnd_file.LOG, 'Error in Calling API');
END;
Base
Tables:
MRP_ASSIGNMENT_SETS.
MRP_SR_ASSIGNMENTS_V.
Hi..
ReplyDeleteThank you for the article. I wanted to know if we can use the same API to update the sourcing rule for an existing customer? if yes can you please provide a sample script. Much appreciated.
Thanks..
yes use L_ASSIGNMENT_TBL(1).ASSIGNMENT_ID:= XXXX and L_ASSIGNMENT_TBL(v_count).OPERATION := 'UPDATE'
ReplyDeleteAssignment_id is required when you want to do update.
replace XXXX with your assignment id for item.