[Home] [Help]
50: retcode := G_ERROR;
51: END proc_io_shippable_flags;
52:
53: -- added as part of bug 12927243
54: /* Procedure to call generic script that updates the mtl_item_categories table
55: to correct the following conditions
56: Note: This is valid only for Master Controlled Category Sets and is valid
57: for both single and multi assignment category sets.
58: Case 1: There are category assignments at the master and child
78: SELECT inventory_item_id,
79: organization_id,
80: category_set_id,
81: category_id
82: FROM mtl_item_categories a
83: WHERE EXISTS (SELECT 'X'
84: FROM mtl_category_sets_b
85: WHERE control_level = 1
86: AND category_set_id = a.category_set_id)
103:
104: FOR rec_cur IN master_org_cur LOOP
105: /*Case 1 and 2: This will delete additional category records from the child organization
106: that are not present in the Master Organization*/
107: DELETE FROM mtl_item_categories a
108: WHERE a.organization_id IN (SELECT m.organization_id
109: FROM mtl_parameters m
110: WHERE m.master_organization_id = rec_cur.master_organization_id
111: AND m.organization_id <> m.master_organization_id)
113: FROM mtl_category_sets_b
114: WHERE control_level = 1
115: AND category_set_id = a.category_set_id)
116: AND NOT EXISTS (SELECT 'x'
117: FROM mtl_item_categories b
118: WHERE b.category_set_id = a.category_set_id
119: AND b.inventory_item_id = a.inventory_item_id
120: AND b.category_id = a.category_id
121: AND b.organization_id = rec_cur.master_organization_id);
128: the same category set and category as that of the master org*/
129: BEGIN
130: SELECT 'FOUND'
131: INTO l_cat_set_exists
132: FROM mtl_item_categories
133: WHERE inventory_item_id = i.inventory_item_id
134: AND organization_id = j.organization_id
135: AND category_set_id = i.category_set_id
136: AND category_id = i.category_id;
138: WHEN no_data_found THEN
139: l_cat_set_exists := 'NOTFOUND';
140: END;
141:
142: /*If not assigned at the child org level then insert details into the mtl_item_categories table*/
143: IF l_cat_set_exists = 'NOTFOUND' THEN
144: INSERT INTO mtl_item_categories
145: (inventory_item_id,
146: organization_id,
140: END;
141:
142: /*If not assigned at the child org level then insert details into the mtl_item_categories table*/
143: IF l_cat_set_exists = 'NOTFOUND' THEN
144: INSERT INTO mtl_item_categories
145: (inventory_item_id,
146: organization_id,
147: category_set_id,
148: category_id,
169: program_application_id,
170: program_id,
171: program_update_date,
172: wh_update_date
173: FROM mtl_item_categories
174: WHERE inventory_item_id = i.inventory_item_id
175: AND organization_id = i.organization_id
176: AND category_set_id = i.category_set_id
177: AND category_id = i.category_id;