[Home] [Help]
61: )
62: IS
63: l_api_name CONSTANT VARCHAR2(30) := 'Create_Category_Assignment';
64: l_api_version CONSTANT NUMBER := 1.0;
65: Mctx INV_ITEM_MSG.Msg_Ctx_type;
66:
67: l_exists VARCHAR2(1);
68: l_category_set_restrict_cats VARCHAR2(1);
69: l_mult_item_cat_assign_flag VARCHAR2(1);
146: --dbms_output.put_line('Before Initialize message list.');
147:
148: -- Initialize message list
149: IF (FND_API.To_Boolean (p_init_msg_list)) THEN
150: INV_ITEM_MSG.Initialize;
151: END IF;
152:
153: -- Define message context
154: Mctx.Package_Name := G_PKG_NAME;
167:
168: IF ( p_inventory_item_id IS NULL ) OR ( p_organization_id IS NULL ) OR
169: ( p_category_set_id IS NULL ) OR ( p_category_id IS NULL )
170: THEN
171: -- INV_ITEM_MSG.Add_Error('INV_INVALID_ARG_NULL_VALUE');
172: INV_ITEM_MSG.Add_Message
173: ( p_Msg_Name => 'INV_INVALID_ARG_NULL_VALUE'
174: , p_transaction_id => p_transaction_id
175: );
168: IF ( p_inventory_item_id IS NULL ) OR ( p_organization_id IS NULL ) OR
169: ( p_category_set_id IS NULL ) OR ( p_category_id IS NULL )
170: THEN
171: -- INV_ITEM_MSG.Add_Error('INV_INVALID_ARG_NULL_VALUE');
172: INV_ITEM_MSG.Add_Message
173: ( p_Msg_Name => 'INV_INVALID_ARG_NULL_VALUE'
174: , p_transaction_id => p_transaction_id
175: );
176:
177: RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
178: END IF;
179:
180: IF (l_debug = 1) THEN
181: INV_ITEM_MSG.Debug(Mctx, 'Validate item/org Ids');
182: END IF;
183:
184: -- Validate item/org Ids
185:
188: OPEN org_item_exists_csr (p_inventory_item_id, p_organization_id);
189: FETCH org_item_exists_csr INTO l_exists,l_request_id, l_approval_status;
190: IF (org_item_exists_csr%NOTFOUND) THEN
191: CLOSE org_item_exists_csr;
192: --INV_ITEM_MSG.Add_Error('INV_ORGITEM_ID_NOT_FOUND');
193: INV_ITEM_MSG.Add_Message
194: ( p_Msg_Name => 'INV_ORGITEM_ID_NOT_FOUND'
195: , p_transaction_id => p_transaction_id
196: );
189: FETCH org_item_exists_csr INTO l_exists,l_request_id, l_approval_status;
190: IF (org_item_exists_csr%NOTFOUND) THEN
191: CLOSE org_item_exists_csr;
192: --INV_ITEM_MSG.Add_Error('INV_ORGITEM_ID_NOT_FOUND');
193: INV_ITEM_MSG.Add_Message
194: ( p_Msg_Name => 'INV_ORGITEM_ID_NOT_FOUND'
195: , p_transaction_id => p_transaction_id
196: );
197: RAISE FND_API.g_EXC_ERROR;
205: SELECT COUNT(*) INTO l_default_cats
206: FROM MTL_DEFAULT_CATEGORY_SETS
207: WHERE CATEGORY_SET_ID = p_category_set_id;
208: IF l_default_cats > 0 THEN
209: INV_ITEM_MSG.Add_Message
210: (p_Msg_Name => 'INV_IOI_NIR_NOT_COMPLETE'
211: ,p_transaction_id => p_transaction_id);
212:
213: RAISE FND_API.g_EXC_ERROR;
219:
220: --dbms_output.put_line('After OPEN org_item_exists_csr ; x_return_status = ' || x_return_status);
221:
222: IF (l_debug = 1) THEN
223: INV_ITEM_MSG.Debug(Mctx, 'Validate category set id');
224: END IF;
225:
226: -- Validate category set id
227:
233: ,l_hierarchy_enabled;--Bug: 2996160
234:
235: IF (category_sets_csr%NOTFOUND) THEN
236: CLOSE category_sets_csr;
237: --INV_ITEM_MSG.Add_Error('INV_CATEGORY_SET_ID_NOT_FOUND');
238: INV_ITEM_MSG.Add_Message
239: ( p_Msg_Name => 'INV_CATEGORY_SET_ID_NOT_FOUND'
240: , p_transaction_id => p_transaction_id
241: );
234:
235: IF (category_sets_csr%NOTFOUND) THEN
236: CLOSE category_sets_csr;
237: --INV_ITEM_MSG.Add_Error('INV_CATEGORY_SET_ID_NOT_FOUND');
238: INV_ITEM_MSG.Add_Message
239: ( p_Msg_Name => 'INV_CATEGORY_SET_ID_NOT_FOUND'
240: , p_transaction_id => p_transaction_id
241: );
242: RAISE FND_API.g_EXC_ERROR;
243: END IF;
244: CLOSE category_sets_csr;
245:
246: IF (l_debug = 1) THEN
247: INV_ITEM_MSG.Debug(Mctx, 'Validate category id');
248: END IF;
249:
250: -- Validate category id
251:
254: OPEN category_exists_csr (p_category_id);
255: FETCH category_exists_csr INTO l_category_struct_id;
256: IF (category_exists_csr%NOTFOUND) THEN
257: CLOSE category_exists_csr;
258: --INV_ITEM_MSG.Add_Error('INV_CATEGORY_ID_NOT_FOUND');
259: INV_ITEM_MSG.Add_Message
260: ( p_Msg_Name => 'INV_CATEGORY_ID_NOT_FOUND'
261: , p_transaction_id => p_transaction_id
262: );
255: FETCH category_exists_csr INTO l_category_struct_id;
256: IF (category_exists_csr%NOTFOUND) THEN
257: CLOSE category_exists_csr;
258: --INV_ITEM_MSG.Add_Error('INV_CATEGORY_ID_NOT_FOUND');
259: INV_ITEM_MSG.Add_Message
260: ( p_Msg_Name => 'INV_CATEGORY_ID_NOT_FOUND'
261: , p_transaction_id => p_transaction_id
262: );
263: RAISE FND_API.g_EXC_ERROR;
266:
267: --dbms_output.put_line('After OPEN category_exists_csr ; x_return_status = ' || x_return_status);
268:
269: IF (l_debug = 1) THEN
270: INV_ITEM_MSG.Debug(Mctx, 'Validate category structure_id');
271: END IF;
272:
273: -- Category structure_id must be the same as structure_id defined in the Category Set.
274:
274:
275: --dbms_output.put_line('Before IF l_category_struct_id ; x_return_status = ' || x_return_status);
276:
277: IF (l_category_struct_id <> l_category_set_struct_id) THEN
278: --INV_ITEM_MSG.Add_Error('INV_INVALID_CATEGORY_STRUCTURE');
279: INV_ITEM_MSG.Add_Message
280: ( p_Msg_Name => 'INV_INVALID_CATEGORY_STRUCTURE'
281: , p_transaction_id => p_transaction_id
282: );
275: --dbms_output.put_line('Before IF l_category_struct_id ; x_return_status = ' || x_return_status);
276:
277: IF (l_category_struct_id <> l_category_set_struct_id) THEN
278: --INV_ITEM_MSG.Add_Error('INV_INVALID_CATEGORY_STRUCTURE');
279: INV_ITEM_MSG.Add_Message
280: ( p_Msg_Name => 'INV_INVALID_CATEGORY_STRUCTURE'
281: , p_transaction_id => p_transaction_id
282: );
283: RAISE FND_API.g_EXC_ERROR;
291: FROM mtl_parameters
292: WHERE organization_id = p_organization_id;
293:
294: IF ((l_control_level = 1) and (p_organization_id <> p_master_org_id)) THEN
295: --INV_ITEM_MSG.Add_Error('INV_CAT_CANNOT_CREATE_DELETE');
296: INV_ITEM_MSG.Add_Message
297: ( p_Msg_Name => 'INV_CAT_CANNOT_CREATE_DELETE'
298: , p_transaction_id => p_transaction_id
299: );
292: WHERE organization_id = p_organization_id;
293:
294: IF ((l_control_level = 1) and (p_organization_id <> p_master_org_id)) THEN
295: --INV_ITEM_MSG.Add_Error('INV_CAT_CANNOT_CREATE_DELETE');
296: INV_ITEM_MSG.Add_Message
297: ( p_Msg_Name => 'INV_CAT_CANNOT_CREATE_DELETE'
298: , p_transaction_id => p_transaction_id
299: );
300: RAISE FND_API.g_EXC_ERROR;
307:
308: IF (l_category_set_restrict_cats = 'Y') THEN
309:
310: IF (l_debug = 1) THEN
311: INV_ITEM_MSG.Debug(Mctx, 'Category Set has a restricted list of categories');
312: INV_ITEM_MSG.Debug(Mctx, 'Validate Category Set valid category');
313: END IF;
314:
315: OPEN category_set_valid_cats_csr (p_category_set_id, p_category_id);
308: IF (l_category_set_restrict_cats = 'Y') THEN
309:
310: IF (l_debug = 1) THEN
311: INV_ITEM_MSG.Debug(Mctx, 'Category Set has a restricted list of categories');
312: INV_ITEM_MSG.Debug(Mctx, 'Validate Category Set valid category');
313: END IF;
314:
315: OPEN category_set_valid_cats_csr (p_category_set_id, p_category_id);
316: FETCH category_set_valid_cats_csr INTO l_exists;
315: OPEN category_set_valid_cats_csr (p_category_set_id, p_category_id);
316: FETCH category_set_valid_cats_csr INTO l_exists;
317: IF (category_set_valid_cats_csr%NOTFOUND) THEN
318: CLOSE category_set_valid_cats_csr;
319: -- INV_ITEM_MSG.Add_Error('INV_CATEGORY_SET_INVALID_CAT');
320: -- INV_ITEM_MSG.Add_Error('INV_CATEGORY_NOT_IN_VALID_SET');
321: INV_ITEM_MSG.Add_Message
322: ( p_Msg_Name => 'INV_CATEGORY_NOT_IN_VALID_SET'
323: , p_transaction_id => p_transaction_id
316: FETCH category_set_valid_cats_csr INTO l_exists;
317: IF (category_set_valid_cats_csr%NOTFOUND) THEN
318: CLOSE category_set_valid_cats_csr;
319: -- INV_ITEM_MSG.Add_Error('INV_CATEGORY_SET_INVALID_CAT');
320: -- INV_ITEM_MSG.Add_Error('INV_CATEGORY_NOT_IN_VALID_SET');
321: INV_ITEM_MSG.Add_Message
322: ( p_Msg_Name => 'INV_CATEGORY_NOT_IN_VALID_SET'
323: , p_transaction_id => p_transaction_id
324: );
317: IF (category_set_valid_cats_csr%NOTFOUND) THEN
318: CLOSE category_set_valid_cats_csr;
319: -- INV_ITEM_MSG.Add_Error('INV_CATEGORY_SET_INVALID_CAT');
320: -- INV_ITEM_MSG.Add_Error('INV_CATEGORY_NOT_IN_VALID_SET');
321: INV_ITEM_MSG.Add_Message
322: ( p_Msg_Name => 'INV_CATEGORY_NOT_IN_VALID_SET'
323: , p_transaction_id => p_transaction_id
324: );
325: RAISE FND_API.g_EXC_ERROR;
327: CLOSE category_set_valid_cats_csr;
328: END IF;
329: --Bug: 2996160 Added function Is_Category_Leafnode,code to validate leaf node
330: IF (l_debug = 1) THEN
331: INV_ITEM_MSG.Debug(Mctx, 'Validate Is category is leafnode or not');
332: END IF;
333: IF NOT Is_Category_Leafnode ( p_category_set_id,
334: p_category_id,
335: l_category_set_restrict_cats,
333: IF NOT Is_Category_Leafnode ( p_category_set_id,
334: p_category_id,
335: l_category_set_restrict_cats,
336: l_hierarchy_enabled ) THEN
337: --INV_ITEM_MSG.Add_Error('INV_ITEM_CAT_ASSIGN_LEAF_ONLY');
338: INV_ITEM_MSG.Add_Message
339: ( p_Msg_Name => 'INV_ITEM_CAT_ASSIGN_LEAF_ONLY'
340: , p_transaction_id => p_transaction_id
341: );
334: p_category_id,
335: l_category_set_restrict_cats,
336: l_hierarchy_enabled ) THEN
337: --INV_ITEM_MSG.Add_Error('INV_ITEM_CAT_ASSIGN_LEAF_ONLY');
338: INV_ITEM_MSG.Add_Message
339: ( p_Msg_Name => 'INV_ITEM_CAT_ASSIGN_LEAF_ONLY'
340: , p_transaction_id => p_transaction_id
341: );
342: RAISE FND_API.g_EXC_ERROR;
344:
345: --Bug: 2996160 Ends here
346:
347: IF (l_debug = 1) THEN
348: INV_ITEM_MSG.Debug(Mctx, 'Validate item cat assignments');
349: END IF;
350:
351: -- Get this item all category assignments count, and this category assignments count
352:
362:
363: IF ( l_mult_item_cat_assign_flag = 'N'
364: AND (l_the_item_assign_count - l_the_cat_assign_count) > 0 )
365: THEN
366: --INV_ITEM_MSG.Debug(Mctx, 'Multiple item category assignment is not allowed');
367: --2879647 If the Item Category Assignment is happening while creating an item
368: -- then take the user given values instead of default
369: IF (l_request_id = p_request_id ) THEN
370: -- Delete a row from the table and create with new category
379: AND inventory_item_id = p_inventory_item_id
380: AND category_set_id = p_category_set_id;
381: END IF;
382: ELSE
383: --INV_ITEM_MSG.Add_Error('INV_ITEM_CAT_ASSIGN_NO_MULT');
384: INV_ITEM_MSG.Add_Message
385: ( p_Msg_Name => 'INV_ITEM_CAT_ASSIGN_NO_MULT'
386: , p_transaction_id => p_transaction_id
387: );
380: AND category_set_id = p_category_set_id;
381: END IF;
382: ELSE
383: --INV_ITEM_MSG.Add_Error('INV_ITEM_CAT_ASSIGN_NO_MULT');
384: INV_ITEM_MSG.Add_Message
385: ( p_Msg_Name => 'INV_ITEM_CAT_ASSIGN_NO_MULT'
386: , p_transaction_id => p_transaction_id
387: );
388: RAISE FND_API.g_EXC_ERROR;
397:
398: IF (l_the_cat_assign_count = 0) THEN
399:
400: IF (l_debug = 1) THEN
401: INV_ITEM_MSG.Debug(Mctx, 'begin INSERT INTO mtl_item_categories');
402: END IF;
403:
404: IF ((l_control_level = 1) and (p_organization_id = p_master_org_id)) THEN
405:
467: , FND_GLOBAL.conc_request_id
468: );
469: END IF;
470: IF (l_debug = 1) THEN
471: INV_ITEM_MSG.Debug(Mctx, 'end INSERT INTO mtl_item_categories');
472: END IF;
473: ELSIF (l_request_id <> p_request_id ) THEN -- Bug:3260965 Incase of Default assignment donot show error
474: --INV_ITEM_MSG.Add_Warning('INV_CAT_ASSGN_ALREADY_EXISTS');
475: INV_ITEM_MSG.Add_Message
470: IF (l_debug = 1) THEN
471: INV_ITEM_MSG.Debug(Mctx, 'end INSERT INTO mtl_item_categories');
472: END IF;
473: ELSIF (l_request_id <> p_request_id ) THEN -- Bug:3260965 Incase of Default assignment donot show error
474: --INV_ITEM_MSG.Add_Warning('INV_CAT_ASSGN_ALREADY_EXISTS');
475: INV_ITEM_MSG.Add_Message
476: ( p_Msg_Name => 'INV_CAT_ASSGN_ALREADY_EXISTS'
477: , p_transaction_id => p_transaction_id
478: );
471: INV_ITEM_MSG.Debug(Mctx, 'end INSERT INTO mtl_item_categories');
472: END IF;
473: ELSIF (l_request_id <> p_request_id ) THEN -- Bug:3260965 Incase of Default assignment donot show error
474: --INV_ITEM_MSG.Add_Warning('INV_CAT_ASSGN_ALREADY_EXISTS');
475: INV_ITEM_MSG.Add_Message
476: ( p_Msg_Name => 'INV_CAT_ASSGN_ALREADY_EXISTS'
477: , p_transaction_id => p_transaction_id
478: );
479: END IF;
481: -- Standard check of p_commit
482: IF (FND_API.To_Boolean (p_commit)) THEN
483:
484: IF (l_debug = 1) THEN
485: INV_ITEM_MSG.Debug(Mctx, 'before COMMIT WORK');
486: END IF;
487:
488: COMMIT WORK;
489: END IF;
487:
488: COMMIT WORK;
489: END IF;
490:
491: INV_ITEM_MSG.Count_And_Get
492: ( p_count => x_msg_count
493: , p_data => x_msg_data
494: );
495:
498: WHEN FND_API.g_EXC_ERROR THEN
499: ROLLBACK TO Create_Category_Assignment_PVT;
500:
501: x_return_status := FND_API.g_RET_STS_ERROR;
502: INV_ITEM_MSG.Count_And_Get
503: ( p_count => x_msg_count
504: , p_data => x_msg_data
505: );
506:
507: WHEN FND_API.g_EXC_UNEXPECTED_ERROR THEN
508: ROLLBACK TO Create_Category_Assignment_PVT;
509:
510: x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
511: INV_ITEM_MSG.Count_And_Get
512: ( p_count => x_msg_count
513: , p_data => x_msg_data
514: );
515:
516: WHEN others THEN
517: ROLLBACK TO Create_Category_Assignment_PVT;
518:
519: x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
520: --INV_ITEM_MSG.Add_Unexpected_Error (Mctx, SQLERRM);
521: INV_ITEM_MSG.Add_Message
522: ( p_Msg_Name => 'INV_ITEM_UNEXPECTED_ERROR'
523: , p_token1 => 'PKG_NAME'
524: , p_value1 => Mctx.Package_Name
517: ROLLBACK TO Create_Category_Assignment_PVT;
518:
519: x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
520: --INV_ITEM_MSG.Add_Unexpected_Error (Mctx, SQLERRM);
521: INV_ITEM_MSG.Add_Message
522: ( p_Msg_Name => 'INV_ITEM_UNEXPECTED_ERROR'
523: , p_token1 => 'PKG_NAME'
524: , p_value1 => Mctx.Package_Name
525: , p_token2 => 'PROCEDURE_NAME'
528: , p_value3 => SQLERRM
529: , p_transaction_id => p_transaction_id
530: );
531:
532: INV_ITEM_MSG.Count_And_Get
533: ( p_count => x_msg_count
534: , p_data => x_msg_data
535: );
536:
556: )
557: IS
558: l_api_name CONSTANT VARCHAR2(30) := 'Delete_Category_Assignment';
559: l_api_version CONSTANT NUMBER := 1.0;
560: Mctx INV_ITEM_MSG.Msg_Ctx_type;
561: l_row_count NUMBER;
562: l_control_level NUMBER;
563: p_master_org_id NUMBER;
564: l_category_struct_id NUMBER;
638: END IF;
639:
640: -- Initialize message list
641: IF (FND_API.To_Boolean (p_init_msg_list)) THEN
642: INV_ITEM_MSG.Initialize;
643: END IF;
644:
645: -- Define message context
646: Mctx.Package_Name := G_PKG_NAME;
648:
649: -- Initialize API return status to success
650: x_return_status := FND_API.g_RET_STS_SUCCESS;
651:
652: --INV_ITEM_MSG.Debug(Mctx, 'NO VALIDATION IMPLEMENTED');
653: --INV_ITEM_MSG.Debug(Mctx, 'before DELETE FROM mtl_item_categories');
654:
655: OPEN category_sets_csr (p_category_set_id);
656: FETCH category_sets_csr INTO l_control_level, l_category_id;
649: -- Initialize API return status to success
650: x_return_status := FND_API.g_RET_STS_SUCCESS;
651:
652: --INV_ITEM_MSG.Debug(Mctx, 'NO VALIDATION IMPLEMENTED');
653: --INV_ITEM_MSG.Debug(Mctx, 'before DELETE FROM mtl_item_categories');
654:
655: OPEN category_sets_csr (p_category_set_id);
656: FETCH category_sets_csr INTO l_control_level, l_category_id;
657:
656: FETCH category_sets_csr INTO l_control_level, l_category_id;
657:
658: IF (category_sets_csr%NOTFOUND) THEN
659: CLOSE category_sets_csr;
660: --INV_ITEM_MSG.Add_Error('INV_CATEGORY_SET_ID_NOT_FOUND');
661: INV_ITEM_MSG.Add_Message
662: ( p_Msg_Name => 'INV_CATEGORY_SET_ID_NOT_FOUND'
663: , p_transaction_id => p_transaction_id
664: );
657:
658: IF (category_sets_csr%NOTFOUND) THEN
659: CLOSE category_sets_csr;
660: --INV_ITEM_MSG.Add_Error('INV_CATEGORY_SET_ID_NOT_FOUND');
661: INV_ITEM_MSG.Add_Message
662: ( p_Msg_Name => 'INV_CATEGORY_SET_ID_NOT_FOUND'
663: , p_transaction_id => p_transaction_id
664: );
665: RAISE FND_API.g_EXC_ERROR;
740: or(FF11 = 'Y' and (cust_order_flagg = 'Y' OR int_order_flagg = 'Y'))
741: or(FF12 = 'Y' and (gdsn_outbound_enabled_flag = 'Y'))
742: or(FF21 = 'Y' and (gdsn_outbound_enabled_flag = 'Y'))) THEN
743: IF ((cnt_cat <= 1) or (cat_flagg = 'Y')) then
744: INV_ITEM_MSG.Add_Message
745: ( p_Msg_Name => 'INV_DEL_MAND_CAT_SET'
746: , p_transaction_id => p_transaction_id
747: );
748: RAISE FND_API.g_EXC_ERROR;
756: FETCH category_exists_csr INTO l_category_struct_id;
757:
758: IF (category_exists_csr%NOTFOUND) THEN
759: CLOSE category_exists_csr;
760: --INV_ITEM_MSG.Add_Error('INV_CATEGORY_ID_NOT_FOUND');
761: INV_ITEM_MSG.Add_Message
762: ( p_Msg_Name => 'INV_CATEGORY_ID_NOT_FOUND'
763: , p_transaction_id => p_transaction_id
764: );
757:
758: IF (category_exists_csr%NOTFOUND) THEN
759: CLOSE category_exists_csr;
760: --INV_ITEM_MSG.Add_Error('INV_CATEGORY_ID_NOT_FOUND');
761: INV_ITEM_MSG.Add_Message
762: ( p_Msg_Name => 'INV_CATEGORY_ID_NOT_FOUND'
763: , p_transaction_id => p_transaction_id
764: );
765: RAISE FND_API.g_EXC_ERROR;
771: FROM mtl_parameters
772: WHERE organization_id = p_organization_id;
773:
774: IF ((l_control_level = 1) and (p_organization_id <> p_master_org_id)) THEN
775: --INV_ITEM_MSG.Add_Error('INV_CAT_CANNOT_CREATE_DELETE');
776: INV_ITEM_MSG.Add_Message
777: ( p_Msg_Name => 'INV_CAT_CANNOT_CREATE_DELETE'
778: , p_transaction_id => p_transaction_id
779: );
772: WHERE organization_id = p_organization_id;
773:
774: IF ((l_control_level = 1) and (p_organization_id <> p_master_org_id)) THEN
775: --INV_ITEM_MSG.Add_Error('INV_CAT_CANNOT_CREATE_DELETE');
776: INV_ITEM_MSG.Add_Message
777: ( p_Msg_Name => 'INV_CAT_CANNOT_CREATE_DELETE'
778: , p_transaction_id => p_transaction_id
779: );
780: RAISE FND_API.g_EXC_ERROR;
802: AND category_id = p_category_id;
803: END IF;
804:
805: IF (SQL%NOTFOUND) THEN
806: --INV_ITEM_MSG.Add_Warning('INV_CAT_ASSGN_NOT_FOUND');
807: INV_ITEM_MSG.Add_Message
808: ( p_Msg_Name => 'INV_CAT_ASSGN_NOT_FOUND'
809: , p_transaction_id => p_transaction_id
810: );
803: END IF;
804:
805: IF (SQL%NOTFOUND) THEN
806: --INV_ITEM_MSG.Add_Warning('INV_CAT_ASSGN_NOT_FOUND');
807: INV_ITEM_MSG.Add_Message
808: ( p_Msg_Name => 'INV_CAT_ASSGN_NOT_FOUND'
809: , p_transaction_id => p_transaction_id
810: );
811: END IF;
810: );
811: END IF;
812:
813: IF (l_debug = 1) THEN
814: INV_ITEM_MSG.Debug(Mctx, 'after DELETE FROM mtl_item_categories');
815: END IF;
816:
817: -- Standard check of p_commit
818: IF (FND_API.To_Boolean (p_commit)) THEN
818: IF (FND_API.To_Boolean (p_commit)) THEN
819: COMMIT WORK;
820: END IF;
821:
822: INV_ITEM_MSG.Count_And_Get
823: ( p_count => x_msg_count
824: , p_data => x_msg_data
825: );
826:
828:
829: WHEN FND_API.g_EXC_ERROR THEN
830: ROLLBACK TO Delete_Category_Assignment_PVT;
831: x_return_status := FND_API.g_RET_STS_ERROR;
832: INV_ITEM_MSG.Count_And_Get
833: ( p_count => x_msg_count
834: , p_data => x_msg_data
835: );
836:
836:
837: WHEN FND_API.g_EXC_UNEXPECTED_ERROR THEN
838: ROLLBACK TO Delete_Category_Assignment_PVT;
839: x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
840: INV_ITEM_MSG.Count_And_Get
841: ( p_count => x_msg_count
842: , p_data => x_msg_data
843: );
844:
845: WHEN others THEN
846: ROLLBACK TO Delete_Category_Assignment_PVT;
847: x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
848:
849: --INV_ITEM_MSG.Add_Unexpected_Error (Mctx, SQLERRM);
850: INV_ITEM_MSG.Add_Message
851: ( p_Msg_Name => 'INV_ITEM_UNEXPECTED_ERROR'
852: , p_token1 => 'PKG_NAME'
853: , p_value1 => Mctx.Package_Name
846: ROLLBACK TO Delete_Category_Assignment_PVT;
847: x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
848:
849: --INV_ITEM_MSG.Add_Unexpected_Error (Mctx, SQLERRM);
850: INV_ITEM_MSG.Add_Message
851: ( p_Msg_Name => 'INV_ITEM_UNEXPECTED_ERROR'
852: , p_token1 => 'PKG_NAME'
853: , p_value1 => Mctx.Package_Name
854: , p_token2 => 'PROCEDURE_NAME'
858: , p_transaction_id => p_transaction_id
859: );
860:
861:
862: INV_ITEM_MSG.Count_And_Get
863: ( p_count => x_msg_count
864: , p_data => x_msg_data
865: );
866:
1057: )
1058: IS
1059: l_api_name CONSTANT VARCHAR2(30) := 'Update_Category_Assignment';
1060: l_api_version CONSTANT NUMBER := 1.0;
1061: Mctx INV_ITEM_MSG.Msg_Ctx_type;
1062: l_row_count NUMBER;
1063: l_control_level NUMBER;
1064: p_master_org_id NUMBER;
1065: l_category_struct_id NUMBER;
1119: END IF;
1120:
1121: -- Initialize message list
1122: IF (FND_API.To_Boolean (p_init_msg_list)) THEN
1123: INV_ITEM_MSG.Initialize;
1124: END IF;
1125:
1126: -- Define message context
1127: Mctx.Package_Name := G_PKG_NAME;
1131: x_return_status := FND_API.g_RET_STS_SUCCESS;
1132:
1133: --* Checking whether Category Set Id is valid or not
1134: IF (l_debug = 1) THEN
1135: INV_ITEM_MSG.Debug(Mctx, 'Checking whether Category Set Id is valid or not');
1136: END IF;
1137: OPEN category_sets_csr (p_category_set_id);
1138: FETCH category_sets_csr INTO l_category_set_struct_id,
1139: l_category_set_restrict_cats,
1142: ,l_hierarchy_enabled;
1143:
1144: IF (category_sets_csr%NOTFOUND) THEN
1145: CLOSE category_sets_csr;
1146: INV_ITEM_MSG.Add_Message
1147: ( p_Msg_Name => 'INV_CATEGORY_SET_ID_NOT_FOUND'
1148: , p_transaction_id => p_transaction_id
1149: );
1150: RAISE FND_API.g_EXC_ERROR;
1160: AND category_set_id = p_category_set_id
1161: AND category_id = p_old_category_id;
1162:
1163: IF l_reccount = 0 THEN
1164: INV_ITEM_MSG.Add_Message
1165: ( p_Msg_Name => 'INV_CAT_ASSGN_NOT_FOUND'
1166: , p_transaction_id => p_transaction_id
1167: );
1168: RAISE FND_API.g_EXC_ERROR;
1169: END IF;
1170:
1171: --* Checking whether New Category Id is valid or not
1172: IF (l_debug = 1) THEN
1173: INV_ITEM_MSG.Debug(Mctx, 'Checking whether New Category Id is valid or not');
1174: END IF;
1175: OPEN category_exists_csr (p_category_id);
1176: FETCH category_exists_csr INTO l_category_struct_id;
1177:
1176: FETCH category_exists_csr INTO l_category_struct_id;
1177:
1178: IF (category_exists_csr%NOTFOUND) THEN
1179: CLOSE category_exists_csr;
1180: INV_ITEM_MSG.Add_Message
1181: ( p_Msg_Name => 'INV_CATEGORY_ID_NOT_FOUND'
1182: , p_transaction_id => p_transaction_id
1183: );
1184: RAISE FND_API.g_EXC_ERROR;
1186: CLOSE category_exists_csr;
1187:
1188: -- Category structure_id must be the same as structure_id defined in the Category Set.
1189: IF (l_debug = 1) THEN
1190: INV_ITEM_MSG.Debug(Mctx, 'Checking whether Category structure id is the same as structure_id defined in the Category Set.');
1191: END IF;
1192: IF (l_category_struct_id <> l_category_set_struct_id) THEN
1193: INV_ITEM_MSG.Add_Message
1194: ( p_Msg_Name => 'INV_INVALID_CATEGORY_STRUCTURE'
1189: IF (l_debug = 1) THEN
1190: INV_ITEM_MSG.Debug(Mctx, 'Checking whether Category structure id is the same as structure_id defined in the Category Set.');
1191: END IF;
1192: IF (l_category_struct_id <> l_category_set_struct_id) THEN
1193: INV_ITEM_MSG.Add_Message
1194: ( p_Msg_Name => 'INV_INVALID_CATEGORY_STRUCTURE'
1195: , p_transaction_id => p_transaction_id
1196: );
1197: RAISE FND_API.g_EXC_ERROR;
1200: -- If a Category Set is defined with the VALIDATE_FLAG = 'Y' then
1201: -- a Category must belong to a list of categories in the table MTL_CATEGORY_SET_VALID_CATS.
1202: IF (l_category_set_restrict_cats = 'Y') THEN
1203: IF (l_debug = 1) THEN
1204: INV_ITEM_MSG.Debug(Mctx, 'Category Set has a restricted list of categories');
1205: INV_ITEM_MSG.Debug(Mctx, 'Validate Category Set valid category');
1206: END IF;
1207:
1208: --* Validating whether new category id exists in table MTL_CATEGORY_SET_VALID_CATS
1201: -- a Category must belong to a list of categories in the table MTL_CATEGORY_SET_VALID_CATS.
1202: IF (l_category_set_restrict_cats = 'Y') THEN
1203: IF (l_debug = 1) THEN
1204: INV_ITEM_MSG.Debug(Mctx, 'Category Set has a restricted list of categories');
1205: INV_ITEM_MSG.Debug(Mctx, 'Validate Category Set valid category');
1206: END IF;
1207:
1208: --* Validating whether new category id exists in table MTL_CATEGORY_SET_VALID_CATS
1209: OPEN category_set_valid_cats_csr (p_category_set_id, p_category_id);
1209: OPEN category_set_valid_cats_csr (p_category_set_id, p_category_id);
1210: FETCH category_set_valid_cats_csr INTO l_exists;
1211: IF (category_set_valid_cats_csr%NOTFOUND) THEN
1212: CLOSE category_set_valid_cats_csr;
1213: INV_ITEM_MSG.Add_Message
1214: ( p_Msg_Name => 'INV_CATEGORY_NOT_IN_VALID_SET'
1215: , p_transaction_id => p_transaction_id
1216: );
1217: RAISE FND_API.g_EXC_ERROR;
1221:
1222: --* Disallow updation if category is master controlled and current org
1223: --* is not master org.
1224: IF (l_debug = 1) THEN
1225: INV_ITEM_MSG.Debug(Mctx, 'Select Master Org from Mtl_Parameters');
1226: END IF;
1227:
1228: SELECT MASTER_ORGANIZATION_ID
1229: INTO p_master_org_id
1230: FROM mtl_parameters
1231: WHERE organization_id = p_organization_id;
1232:
1233: IF ((l_control_level = 1) and (p_organization_id <> p_master_org_id)) THEN
1234: INV_ITEM_MSG.Add_Message
1235: ( p_Msg_Name => 'INV_CAT_CANNOT_CREATE_DELETE'
1236: , p_transaction_id => p_transaction_id
1237: );
1238: RAISE FND_API.g_EXC_ERROR;
1240:
1241: /* Commented for Bug 4609655 - Checking not required
1242: --* checking for duplicate records
1243: IF (l_debug = 1) THEN
1244: INV_ITEM_MSG.Debug(Mctx, 'Checking for duplicate records');
1245: END IF;
1246: SELECT Count(1)
1247: INTO l_reccount
1248: FROM mtl_item_categories
1251: AND category_set_id = p_category_set_id
1252: AND category_id = p_category_id;
1253:
1254: IF l_reccount > 0 THEN
1255: INV_ITEM_MSG.Add_Message
1256: ( p_Msg_Name => 'INV_CAT_ASSGN_ALREADY_EXISTS'
1257: , p_transaction_id => p_transaction_id
1258: );
1259: RAISE FND_API.g_EXC_ERROR;
1261: End of Commenting for Bug 4609655 */
1262:
1263: --* Validating if new category is leafnode or not
1264: IF (l_debug = 1) THEN
1265: INV_ITEM_MSG.Debug(Mctx, 'Validate If new category is leafnode or not');
1266: END IF;
1267:
1268: IF NOT Is_Category_Leafnode ( p_category_set_id,
1269: p_category_id,
1268: IF NOT Is_Category_Leafnode ( p_category_set_id,
1269: p_category_id,
1270: l_category_set_restrict_cats,
1271: l_hierarchy_enabled ) THEN
1272: INV_ITEM_MSG.Add_Message
1273: ( p_Msg_Name => 'INV_ITEM_CAT_ASSIGN_LEAF_ONLY'
1274: , p_transaction_id => p_transaction_id
1275: );
1276: RAISE FND_API.g_EXC_ERROR;
1278:
1279:
1280: --* Updating Master Org or Master Org + Child Orgs depending on Control Level
1281: IF (l_debug = 1) THEN
1282: INV_ITEM_MSG.Debug(Mctx, 'Updating Mtl_Item_Categories...');
1283: END IF;
1284:
1285: IF ((l_control_level = 1) and (p_organization_id = p_master_org_id)) THEN
1286: UPDATE /*+ INDEX(MIC MTL_ITEM_CATEGORIES_U1) */
1356:
1357: END IF;
1358:
1359: IF (l_debug = 1) THEN
1360: INV_ITEM_MSG.Debug(Mctx, 'after update FROM mtl_item_categories');
1361: END IF;
1362:
1363: -- Standard check of p_commit
1364: IF (FND_API.To_Boolean (p_commit)) THEN
1364: IF (FND_API.To_Boolean (p_commit)) THEN
1365: COMMIT WORK;
1366: END IF;
1367:
1368: INV_ITEM_MSG.Count_And_Get
1369: ( p_count => x_msg_count
1370: , p_data => x_msg_data
1371: );
1372:
1374:
1375: WHEN FND_API.g_EXC_ERROR THEN
1376: ROLLBACK TO Update_Category_Assignment_PVT;
1377: x_return_status := FND_API.g_RET_STS_ERROR;
1378: INV_ITEM_MSG.Count_And_Get
1379: ( p_count => x_msg_count
1380: , p_data => x_msg_data
1381: );
1382:
1382:
1383: WHEN FND_API.g_EXC_UNEXPECTED_ERROR THEN
1384: ROLLBACK TO Update_Category_Assignment_PVT;
1385: x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
1386: INV_ITEM_MSG.Count_And_Get
1387: ( p_count => x_msg_count
1388: , p_data => x_msg_data
1389: );
1390:
1390:
1391: WHEN Processing_Error THEN
1392: ROLLBACK TO Update_Category_Assignment_PVT;
1393: x_return_status := l_return_status;
1394: INV_ITEM_MSG.Count_And_Get
1395: ( p_count => x_msg_count
1396: , p_data => x_msg_data
1397: );
1398:
1399: WHEN others THEN
1400: ROLLBACK TO Update_Category_Assignment_PVT;
1401: x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
1402:
1403: INV_ITEM_MSG.Add_Message
1404: ( p_Msg_Name => 'INV_ITEM_UNEXPECTED_ERROR'
1405: , p_token1 => 'PKG_NAME'
1406: , p_value1 => Mctx.Package_Name
1407: , p_token2 => 'PROCEDURE_NAME'
1411: , p_transaction_id => p_transaction_id
1412: );
1413:
1414:
1415: INV_ITEM_MSG.Count_And_Get
1416: ( p_count => x_msg_count
1417: , p_data => x_msg_data
1418: );
1419: