This script deletes records from MTL_item_categories table.<\/a>( sktypt)<\/p>\nDelete Category Assignments for which there exist Multiple Item Category Assignments
\ninspite of having the flag 'Allow Multiple Item Category Assignments’ disabled.
\nDelete all Item Category Assignment records except the one which is in GMI_item_categories.
\nWe also make sure that gmi_item_categories table does not have multiple category a
\nssignments
\nNote: Take backup of MTL_item_categories table before running this script. Ref: Bug 5517940.
\n**********************************************************************************************\/<\/p>\n
set serveroutput on
\nset verify off<\/p>\n
DECLARE<\/p>\n
v_inventory_item_id NUMBER := 0;
\nv_organization_id NUMBER := 0;
\nv_category_set_id NUMBER := 0;
\nv_count NUMBER := 0;<\/p>\n
err_num NUMBER;
\nerr_msg VARCHAR2(100);<\/p>\n
CURSOR gmicat IS
\nselect 1
\nfrom gmi_item_categories gic, mtl_category_sets_b mtlcset
\nwhere gic.category_set_id = mtlcset.category_set_id
\nand mtlcset.mult_item_cat_assign_flag = 'N’
\nand exists(select count(*),item_id,category_set_id
\nfrom gmi_item_categories gic2
\nwhere gic2.item_id = gic.item_id
\nand gic2.category_set_id = gic.category_set_id
\ngroup by item_id,category_set_id
\nhaving count(*) > 1);<\/p>\n
v_gmicat_rec gmicat%ROWTYPE;<\/p>\n
CURSOR mtlcat IS
\nselect mic.inventory_item_id, mic.organization_id, mic.category_set_id
\nfrom mtl_item_categories mic, mtl_category_sets_b mtlcset
\nwhere mic.category_set_id = mtlcset.category_set_id
\nand mtlcset.mult_item_cat_assign_flag = 'N’
\nand exists(select count(*), mic2.inventory_item_id, mic2.organization_id,
\nmic.category_set_id
\nfrom mtl_item_categories mic2
\nwhere mic2.inventory_item_id = mic.inventory_item_id
\nand mic2.organization_id = mic.organization_id
\nand mic2.category_set_id = mic.category_set_id
\ngroup by mic2.inventory_item_id, mic2.organization_id, mic2.category_set_id
\nhaving count(*) > 1)
\nand exists(select 1
\nfrom gmi_item_categories gic3, ic_item_mst_b iim, mtl_system_items_b msi
\nwhere gic3.category_set_id = mic.category_set_id
\nand gic3.item_id = iim.item_id
\nand msi.segment1 = iim.item_no
\nand msi.inventory_item_id = mic.inventory_item_id
\nand msi.organization_id = mic.organization_id)
\norder by mic.inventory_item_id, mic.organization_id, mic.category_set_id, mic.creation_da
\nte;<\/p>\n
BEGIN<\/p>\n
OPEN gmicat;
\nFETCH gmicat INTO v_gmicat_rec;
\nIF gmicat%FOUND THEN
\ndbms_output.put_line(’ERROR: Multiple category assignments exist in GMI_item_categories.’);
\nCLOSE gmicat;
\nRETURN;
\nEND IF;
\nCLOSE gmicat;<\/p>\n
FOR rec_mtlcat in mtlcat LOOP<\/p>\n
IF (rec_mtlcat.inventory_item_id <> v_inventory_item_id OR\nrec_mtlcat.organization_id <> v_organization_id OR\nrec_mtlcat.category_set_id <> v_category_set_id) THEN\n\ndelete from mtl_item_categories\nwhere inventory_item_id = rec_mtlcat.inventory_item_id\nand organization_id = rec_mtlcat.organization_id\nand category_set_id = rec_mtlcat.category_set_id\nand category_id <> (select gic.category_id\nfrom gmi_item_categories gic, ic_item_mst_b iim, mtl_system_items_b msi\nwhere gic.item_id = iim.item_id\nand msi.segment1 = iim.item_no\nand msi.inventory_item_id = rec_mtlcat.inventory_item_id\n\nand msi.organization_id = rec_mtlcat.organization_id\nand gic.category_set_id = rec_mtlcat.category_set_id);\n\nv_count := SQL%ROWCOUNT + v_count;\n\nEND IF;\n\nv_inventory_item_id := rec_mtlcat.inventory_item_id;\nv_organization_id := rec_mtlcat.organization_id;\nv_category_set_id := rec_mtlcat.category_set_id;\n\nEND LOOP;\n\ndbms_output.put_line('Total Number of records deleted from MTL_item_categories = ' || to_char(v_cou\nnt));\ndbms_output.put_line('Verify that the data is correct and COMMIT...');\n\nEXCEPTION\n\nWHEN OTHERS THEN\nerr_num := SQLCODE;\nerr_msg := SUBSTRB(SQLERRM, 1, 100);\nRAISE_APPLICATION_ERROR(-20000, err_msg);\n\nEND;\n\/\n\nset serveroutput off\nset verify on\n<\/p>\n","protected":false},"excerpt":{"rendered":"
Aby umo\u017cliwi\u0107 zmian\u0119 kategorii w OPM po tym jak wyskakuje komunikat, \u017ce nie mo\u017cna zmieni\u0107 kategorii bo taki przydzia\u0142 ju\u017c istnieje nale\u017cy uruchomi\u0107 poni\u017cszy skrypt. Zawsze nale\u017cy zastosowac si\u0119 do polece\u0144 asysty i wykona\u0107 backup tabeli MTL_ITEM_CATEGORIES<\/p>\n","protected":false},"author":20,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":[],"categories":[9],"tags":[225,226],"_links":{"self":[{"href":"http:\/\/oracle.malin.pl\/index.php?rest_route=\/wp\/v2\/posts\/250"}],"collection":[{"href":"http:\/\/oracle.malin.pl\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/oracle.malin.pl\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/oracle.malin.pl\/index.php?rest_route=\/wp\/v2\/users\/20"}],"replies":[{"embeddable":true,"href":"http:\/\/oracle.malin.pl\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=250"}],"version-history":[{"count":0,"href":"http:\/\/oracle.malin.pl\/index.php?rest_route=\/wp\/v2\/posts\/250\/revisions"}],"wp:attachment":[{"href":"http:\/\/oracle.malin.pl\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=250"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/oracle.malin.pl\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=250"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/oracle.malin.pl\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=250"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}