Brak możliwości zmiany kategorii OPM (Klasa KG Pozycji lub inne)

Aby umożliwić zmianę kategorii w OPM po tym jak wyskakuje komunikat, że nie można zmienić kategorii bo taki przydział już istnieje należy uruchomić poniższy skrypt. Zawsze należy zastosowac się do poleceń asysty i wykonać backup tabeli MTL_ITEM_CATEGORIES

/*********************************************************************************************
This script deletes records from MTL_item_categories table.( sktypt)

Delete Category Assignments for which there exist Multiple Item Category Assignments
inspite of having the flag 'Allow Multiple Item Category Assignments’ disabled.
Delete all Item Category Assignment records except the one which is in GMI_item_categories.
We also make sure that gmi_item_categories table does not have multiple category a
ssignments
Note: Take backup of MTL_item_categories table before running this script. Ref: Bug 5517940.
**********************************************************************************************/

set serveroutput on
set verify off

DECLARE

v_inventory_item_id NUMBER := 0;
v_organization_id NUMBER := 0;
v_category_set_id NUMBER := 0;
v_count NUMBER := 0;

err_num NUMBER;
err_msg VARCHAR2(100);

CURSOR gmicat IS
select 1
from gmi_item_categories gic, mtl_category_sets_b mtlcset
where gic.category_set_id = mtlcset.category_set_id
and mtlcset.mult_item_cat_assign_flag = 'N’
and exists(select count(*),item_id,category_set_id
from gmi_item_categories gic2
where gic2.item_id = gic.item_id
and gic2.category_set_id = gic.category_set_id
group by item_id,category_set_id
having count(*) > 1);

v_gmicat_rec gmicat%ROWTYPE;

CURSOR mtlcat IS
select mic.inventory_item_id, mic.organization_id, mic.category_set_id
from mtl_item_categories mic, mtl_category_sets_b mtlcset
where mic.category_set_id = mtlcset.category_set_id
and mtlcset.mult_item_cat_assign_flag = 'N’
and exists(select count(*), mic2.inventory_item_id, mic2.organization_id,
mic.category_set_id
from mtl_item_categories mic2
where mic2.inventory_item_id = mic.inventory_item_id
and mic2.organization_id = mic.organization_id
and mic2.category_set_id = mic.category_set_id
group by mic2.inventory_item_id, mic2.organization_id, mic2.category_set_id
having count(*) > 1)
and exists(select 1
from gmi_item_categories gic3, ic_item_mst_b iim, mtl_system_items_b msi
where gic3.category_set_id = mic.category_set_id
and gic3.item_id = iim.item_id
and msi.segment1 = iim.item_no
and msi.inventory_item_id = mic.inventory_item_id
and msi.organization_id = mic.organization_id)
order by mic.inventory_item_id, mic.organization_id, mic.category_set_id, mic.creation_da
te;

BEGIN

OPEN gmicat;
FETCH gmicat INTO v_gmicat_rec;
IF gmicat%FOUND THEN
dbms_output.put_line(’ERROR: Multiple category assignments exist in GMI_item_categories.’);
CLOSE gmicat;
RETURN;
END IF;
CLOSE gmicat;

FOR rec_mtlcat in mtlcat LOOP

IF (rec_mtlcat.inventory_item_id <> v_inventory_item_id OR rec_mtlcat.organization_id <> v_organization_id OR rec_mtlcat.category_set_id <> v_category_set_id) THEN delete from mtl_item_categories where inventory_item_id = rec_mtlcat.inventory_item_id and organization_id = rec_mtlcat.organization_id and category_set_id = rec_mtlcat.category_set_id and category_id <> (select gic.category_id from gmi_item_categories gic, ic_item_mst_b iim, mtl_system_items_b msi where gic.item_id = iim.item_id and msi.segment1 = iim.item_no and msi.inventory_item_id = rec_mtlcat.inventory_item_id and msi.organization_id = rec_mtlcat.organization_id and gic.category_set_id = rec_mtlcat.category_set_id); v_count := SQL%ROWCOUNT + v_count; END IF; v_inventory_item_id := rec_mtlcat.inventory_item_id; v_organization_id := rec_mtlcat.organization_id; v_category_set_id := rec_mtlcat.category_set_id; END LOOP; dbms_output.put_line('Total Number of records deleted from MTL_item_categories = ' || to_char(v_cou nt)); dbms_output.put_line('Verify that the data is correct and COMMIT...'); EXCEPTION WHEN OTHERS THEN err_num := SQLCODE; err_msg := SUBSTRB(SQLERRM, 1, 100); RAISE_APPLICATION_ERROR(-20000, err_msg); END; / set serveroutput off set verify on