[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);
147: --dbms_output.put_line('Before Initialize message list.');
148:
149: -- Initialize message list
150: IF (FND_API.To_Boolean (p_init_msg_list)) THEN
151: INV_ITEM_MSG.Initialize;
152: END IF;
153:
154: -- Define message context
155: Mctx.Package_Name := G_PKG_NAME;
168:
169: IF ( p_inventory_item_id IS NULL ) OR ( p_organization_id IS NULL ) OR
170: ( p_category_set_id IS NULL ) OR ( p_category_id IS NULL )
171: THEN
172: -- INV_ITEM_MSG.Add_Error('INV_INVALID_ARG_NULL_VALUE');
173: INV_ITEM_MSG.Add_Message
174: ( p_Msg_Name => 'INV_INVALID_ARG_NULL_VALUE'
175: , p_transaction_id => p_transaction_id
176: );
169: IF ( p_inventory_item_id IS NULL ) OR ( p_organization_id IS NULL ) OR
170: ( p_category_set_id IS NULL ) OR ( p_category_id IS NULL )
171: THEN
172: -- INV_ITEM_MSG.Add_Error('INV_INVALID_ARG_NULL_VALUE');
173: INV_ITEM_MSG.Add_Message
174: ( p_Msg_Name => 'INV_INVALID_ARG_NULL_VALUE'
175: , p_transaction_id => p_transaction_id
176: );
177:
178: RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
179: END IF;
180:
181: IF (l_debug = 1) THEN
182: INV_ITEM_MSG.Debug(Mctx, 'Validate item/org Ids');
183: END IF;
184:
185: -- Validate item/org Ids
186:
189: OPEN org_item_exists_csr (p_inventory_item_id, p_organization_id);
190: FETCH org_item_exists_csr INTO l_exists,l_request_id, l_approval_status;
191: IF (org_item_exists_csr%NOTFOUND) THEN
192: CLOSE org_item_exists_csr;
193: --INV_ITEM_MSG.Add_Error('INV_ORGITEM_ID_NOT_FOUND');
194: INV_ITEM_MSG.Add_Message
195: ( p_Msg_Name => 'INV_ORGITEM_ID_NOT_FOUND'
196: , p_transaction_id => p_transaction_id
197: );
190: FETCH org_item_exists_csr INTO l_exists,l_request_id, l_approval_status;
191: IF (org_item_exists_csr%NOTFOUND) THEN
192: CLOSE org_item_exists_csr;
193: --INV_ITEM_MSG.Add_Error('INV_ORGITEM_ID_NOT_FOUND');
194: INV_ITEM_MSG.Add_Message
195: ( p_Msg_Name => 'INV_ORGITEM_ID_NOT_FOUND'
196: , p_transaction_id => p_transaction_id
197: );
198: RAISE FND_API.g_EXC_ERROR;
215: SELECT COUNT(*) INTO l_default_cats
216: FROM MTL_DEFAULT_CATEGORY_SETS
217: WHERE CATEGORY_SET_ID = p_category_set_id;
218: IF l_default_cats > 0 THEN
219: INV_ITEM_MSG.Add_Message
220: (p_Msg_Name => 'INV_IOI_NIR_NOT_COMPLETE'
221: ,p_transaction_id => p_transaction_id);
222:
223: RAISE FND_API.g_EXC_ERROR;
229:
230: --dbms_output.put_line('After OPEN org_item_exists_csr ; x_return_status = ' || x_return_status);
231:
232: IF (l_debug = 1) THEN
233: INV_ITEM_MSG.Debug(Mctx, 'Validate category set id');
234: END IF;
235:
236: -- Validate category set id
237:
243: ,l_hierarchy_enabled;--Bug: 2996160
244:
245: IF (category_sets_csr%NOTFOUND) THEN
246: CLOSE category_sets_csr;
247: --INV_ITEM_MSG.Add_Error('INV_CATEGORY_SET_ID_NOT_FOUND');
248: INV_ITEM_MSG.Add_Message
249: ( p_Msg_Name => 'INV_CATEGORY_SET_ID_NOT_FOUND'
250: , p_transaction_id => p_transaction_id
251: );
244:
245: IF (category_sets_csr%NOTFOUND) THEN
246: CLOSE category_sets_csr;
247: --INV_ITEM_MSG.Add_Error('INV_CATEGORY_SET_ID_NOT_FOUND');
248: INV_ITEM_MSG.Add_Message
249: ( p_Msg_Name => 'INV_CATEGORY_SET_ID_NOT_FOUND'
250: , p_transaction_id => p_transaction_id
251: );
252: RAISE FND_API.g_EXC_ERROR;
253: END IF;
254: CLOSE category_sets_csr;
255:
256: IF (l_debug = 1) THEN
257: INV_ITEM_MSG.Debug(Mctx, 'Validate category id');
258: END IF;
259:
260: -- Validate category id
261:
264: OPEN category_exists_csr (p_category_id);
265: FETCH category_exists_csr INTO l_category_struct_id;
266: IF (category_exists_csr%NOTFOUND) THEN
267: CLOSE category_exists_csr;
268: --INV_ITEM_MSG.Add_Error('INV_CATEGORY_ID_NOT_FOUND');
269: INV_ITEM_MSG.Add_Message
270: ( p_Msg_Name => 'INV_CATEGORY_ID_NOT_FOUND'
271: , p_transaction_id => p_transaction_id
272: );
265: FETCH category_exists_csr INTO l_category_struct_id;
266: IF (category_exists_csr%NOTFOUND) THEN
267: CLOSE category_exists_csr;
268: --INV_ITEM_MSG.Add_Error('INV_CATEGORY_ID_NOT_FOUND');
269: INV_ITEM_MSG.Add_Message
270: ( p_Msg_Name => 'INV_CATEGORY_ID_NOT_FOUND'
271: , p_transaction_id => p_transaction_id
272: );
273: RAISE FND_API.g_EXC_ERROR;
276:
277: --dbms_output.put_line('After OPEN category_exists_csr ; x_return_status = ' || x_return_status);
278:
279: IF (l_debug = 1) THEN
280: INV_ITEM_MSG.Debug(Mctx, 'Validate category structure_id');
281: END IF;
282:
283: -- Category structure_id must be the same as structure_id defined in the Category Set.
284:
284:
285: --dbms_output.put_line('Before IF l_category_struct_id ; x_return_status = ' || x_return_status);
286:
287: IF (l_category_struct_id <> l_category_set_struct_id) THEN
288: --INV_ITEM_MSG.Add_Error('INV_INVALID_CATEGORY_STRUCTURE');
289: INV_ITEM_MSG.Add_Message
290: ( p_Msg_Name => 'INV_INVALID_CATEGORY_STRUCTURE'
291: , p_transaction_id => p_transaction_id
292: );
285: --dbms_output.put_line('Before IF l_category_struct_id ; x_return_status = ' || x_return_status);
286:
287: IF (l_category_struct_id <> l_category_set_struct_id) THEN
288: --INV_ITEM_MSG.Add_Error('INV_INVALID_CATEGORY_STRUCTURE');
289: INV_ITEM_MSG.Add_Message
290: ( p_Msg_Name => 'INV_INVALID_CATEGORY_STRUCTURE'
291: , p_transaction_id => p_transaction_id
292: );
293: RAISE FND_API.g_EXC_ERROR;
301: FROM mtl_parameters
302: WHERE organization_id = p_organization_id;
303:
304: IF ((l_control_level = 1) and (p_organization_id <> p_master_org_id)) THEN
305: --INV_ITEM_MSG.Add_Error('INV_CAT_CANNOT_CREATE_DELETE');
306: INV_ITEM_MSG.Add_Message
307: ( p_Msg_Name => 'INV_CAT_CANNOT_CREATE_DELETE'
308: , p_transaction_id => p_transaction_id
309: );
302: WHERE organization_id = p_organization_id;
303:
304: IF ((l_control_level = 1) and (p_organization_id <> p_master_org_id)) THEN
305: --INV_ITEM_MSG.Add_Error('INV_CAT_CANNOT_CREATE_DELETE');
306: INV_ITEM_MSG.Add_Message
307: ( p_Msg_Name => 'INV_CAT_CANNOT_CREATE_DELETE'
308: , p_transaction_id => p_transaction_id
309: );
310: RAISE FND_API.g_EXC_ERROR;
317:
318: IF (l_category_set_restrict_cats = 'Y') THEN
319:
320: IF (l_debug = 1) THEN
321: INV_ITEM_MSG.Debug(Mctx, 'Category Set has a restricted list of categories');
322: INV_ITEM_MSG.Debug(Mctx, 'Validate Category Set valid category');
323: END IF;
324:
325: OPEN category_set_valid_cats_csr (p_category_set_id, p_category_id);
318: IF (l_category_set_restrict_cats = 'Y') THEN
319:
320: IF (l_debug = 1) THEN
321: INV_ITEM_MSG.Debug(Mctx, 'Category Set has a restricted list of categories');
322: INV_ITEM_MSG.Debug(Mctx, 'Validate Category Set valid category');
323: END IF;
324:
325: OPEN category_set_valid_cats_csr (p_category_set_id, p_category_id);
326: FETCH category_set_valid_cats_csr INTO l_exists;
325: OPEN category_set_valid_cats_csr (p_category_set_id, p_category_id);
326: FETCH category_set_valid_cats_csr INTO l_exists;
327: IF (category_set_valid_cats_csr%NOTFOUND) THEN
328: CLOSE category_set_valid_cats_csr;
329: -- INV_ITEM_MSG.Add_Error('INV_CATEGORY_SET_INVALID_CAT');
330: -- INV_ITEM_MSG.Add_Error('INV_CATEGORY_NOT_IN_VALID_SET');
331: INV_ITEM_MSG.Add_Message
332: ( p_Msg_Name => 'INV_CATEGORY_NOT_IN_VALID_SET'
333: , p_transaction_id => p_transaction_id
326: FETCH category_set_valid_cats_csr INTO l_exists;
327: IF (category_set_valid_cats_csr%NOTFOUND) THEN
328: CLOSE category_set_valid_cats_csr;
329: -- INV_ITEM_MSG.Add_Error('INV_CATEGORY_SET_INVALID_CAT');
330: -- INV_ITEM_MSG.Add_Error('INV_CATEGORY_NOT_IN_VALID_SET');
331: INV_ITEM_MSG.Add_Message
332: ( p_Msg_Name => 'INV_CATEGORY_NOT_IN_VALID_SET'
333: , p_transaction_id => p_transaction_id
334: );
327: IF (category_set_valid_cats_csr%NOTFOUND) THEN
328: CLOSE category_set_valid_cats_csr;
329: -- INV_ITEM_MSG.Add_Error('INV_CATEGORY_SET_INVALID_CAT');
330: -- INV_ITEM_MSG.Add_Error('INV_CATEGORY_NOT_IN_VALID_SET');
331: INV_ITEM_MSG.Add_Message
332: ( p_Msg_Name => 'INV_CATEGORY_NOT_IN_VALID_SET'
333: , p_transaction_id => p_transaction_id
334: );
335: RAISE FND_API.g_EXC_ERROR;
337: CLOSE category_set_valid_cats_csr;
338: END IF;
339: --Bug: 2996160 Added function Is_Category_Leafnode,code to validate leaf node
340: IF (l_debug = 1) THEN
341: INV_ITEM_MSG.Debug(Mctx, 'Validate Is category is leafnode or not');
342: END IF;
343: IF NOT Is_Category_Leafnode ( p_category_set_id,
344: p_category_id,
345: l_category_set_restrict_cats,
343: IF NOT Is_Category_Leafnode ( p_category_set_id,
344: p_category_id,
345: l_category_set_restrict_cats,
346: l_hierarchy_enabled ) THEN
347: --INV_ITEM_MSG.Add_Error('INV_ITEM_CAT_ASSIGN_LEAF_ONLY');
348: INV_ITEM_MSG.Add_Message
349: ( p_Msg_Name => 'INV_ITEM_CAT_ASSIGN_LEAF_ONLY'
350: , p_transaction_id => p_transaction_id
351: );
344: p_category_id,
345: l_category_set_restrict_cats,
346: l_hierarchy_enabled ) THEN
347: --INV_ITEM_MSG.Add_Error('INV_ITEM_CAT_ASSIGN_LEAF_ONLY');
348: INV_ITEM_MSG.Add_Message
349: ( p_Msg_Name => 'INV_ITEM_CAT_ASSIGN_LEAF_ONLY'
350: , p_transaction_id => p_transaction_id
351: );
352: RAISE FND_API.g_EXC_ERROR;
354:
355: --Bug: 2996160 Ends here
356:
357: IF (l_debug = 1) THEN
358: INV_ITEM_MSG.Debug(Mctx, 'Validate item cat assignments');
359: END IF;
360:
361: -- Get this item all category assignments count, and this category assignments count
362:
372:
373: IF ( l_mult_item_cat_assign_flag = 'N'
374: AND (l_the_item_assign_count - l_the_cat_assign_count) > 0 )
375: THEN
376: --INV_ITEM_MSG.Debug(Mctx, 'Multiple item category assignment is not allowed');
377: --2879647 If the Item Category Assignment is happening while creating an item
378: -- then take the user given values instead of default
379: IF (l_request_id = p_request_id ) THEN
380: -- Delete a row from the table and create with new category
389: AND inventory_item_id = p_inventory_item_id
390: AND category_set_id = p_category_set_id;
391: END IF;
392: ELSE
393: --INV_ITEM_MSG.Add_Error('INV_ITEM_CAT_ASSIGN_NO_MULT');
394: INV_ITEM_MSG.Add_Message
395: ( p_Msg_Name => 'INV_ITEM_CAT_ASSIGN_NO_MULT'
396: , p_transaction_id => p_transaction_id
397: );
390: AND category_set_id = p_category_set_id;
391: END IF;
392: ELSE
393: --INV_ITEM_MSG.Add_Error('INV_ITEM_CAT_ASSIGN_NO_MULT');
394: INV_ITEM_MSG.Add_Message
395: ( p_Msg_Name => 'INV_ITEM_CAT_ASSIGN_NO_MULT'
396: , p_transaction_id => p_transaction_id
397: );
398: RAISE FND_API.g_EXC_ERROR;
407:
408: IF (l_the_cat_assign_count = 0) THEN
409:
410: IF (l_debug = 1) THEN
411: INV_ITEM_MSG.Debug(Mctx, 'begin INSERT INTO mtl_item_categories');
412: END IF;
413:
414: IF ((l_control_level = 1) and (p_organization_id = p_master_org_id)) THEN
415:
477: , FND_GLOBAL.conc_request_id
478: );
479: END IF;
480: IF (l_debug = 1) THEN
481: INV_ITEM_MSG.Debug(Mctx, 'end INSERT INTO mtl_item_categories');
482: END IF;
483: ELSIF (l_request_id <> p_request_id ) THEN -- Bug:3260965 Incase of Default assignment donot show error
484: --INV_ITEM_MSG.Add_Warning('INV_CAT_ASSGN_ALREADY_EXISTS');
485: INV_ITEM_MSG.Add_Message
480: IF (l_debug = 1) THEN
481: INV_ITEM_MSG.Debug(Mctx, 'end INSERT INTO mtl_item_categories');
482: END IF;
483: ELSIF (l_request_id <> p_request_id ) THEN -- Bug:3260965 Incase of Default assignment donot show error
484: --INV_ITEM_MSG.Add_Warning('INV_CAT_ASSGN_ALREADY_EXISTS');
485: INV_ITEM_MSG.Add_Message
486: ( p_Msg_Name => 'INV_CAT_ASSGN_ALREADY_EXISTS'
487: , p_transaction_id => p_transaction_id
488: );
481: INV_ITEM_MSG.Debug(Mctx, 'end INSERT INTO mtl_item_categories');
482: END IF;
483: ELSIF (l_request_id <> p_request_id ) THEN -- Bug:3260965 Incase of Default assignment donot show error
484: --INV_ITEM_MSG.Add_Warning('INV_CAT_ASSGN_ALREADY_EXISTS');
485: INV_ITEM_MSG.Add_Message
486: ( p_Msg_Name => 'INV_CAT_ASSGN_ALREADY_EXISTS'
487: , p_transaction_id => p_transaction_id
488: );
489: END IF;
491: -- Standard check of p_commit
492: IF (FND_API.To_Boolean (p_commit)) THEN
493:
494: IF (l_debug = 1) THEN
495: INV_ITEM_MSG.Debug(Mctx, 'before COMMIT WORK');
496: END IF;
497:
498: COMMIT WORK;
499: END IF;
497:
498: COMMIT WORK;
499: END IF;
500:
501: INV_ITEM_MSG.Count_And_Get
502: ( p_count => x_msg_count
503: , p_data => x_msg_data
504: );
505:
508: WHEN FND_API.g_EXC_ERROR THEN
509: ROLLBACK TO Create_Category_Assignment_PVT;
510:
511: x_return_status := FND_API.g_RET_STS_ERROR;
512: INV_ITEM_MSG.Count_And_Get
513: ( p_count => x_msg_count
514: , p_data => x_msg_data
515: );
516:
517: WHEN FND_API.g_EXC_UNEXPECTED_ERROR THEN
518: ROLLBACK TO Create_Category_Assignment_PVT;
519:
520: x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
521: INV_ITEM_MSG.Count_And_Get
522: ( p_count => x_msg_count
523: , p_data => x_msg_data
524: );
525:
526: WHEN others THEN
527: ROLLBACK TO Create_Category_Assignment_PVT;
528:
529: x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
530: --INV_ITEM_MSG.Add_Unexpected_Error (Mctx, SQLERRM);
531: INV_ITEM_MSG.Add_Message
532: ( p_Msg_Name => 'INV_ITEM_UNEXPECTED_ERROR'
533: , p_token1 => 'PKG_NAME'
534: , p_value1 => Mctx.Package_Name
527: ROLLBACK TO Create_Category_Assignment_PVT;
528:
529: x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
530: --INV_ITEM_MSG.Add_Unexpected_Error (Mctx, SQLERRM);
531: INV_ITEM_MSG.Add_Message
532: ( p_Msg_Name => 'INV_ITEM_UNEXPECTED_ERROR'
533: , p_token1 => 'PKG_NAME'
534: , p_value1 => Mctx.Package_Name
535: , p_token2 => 'PROCEDURE_NAME'
538: , p_value3 => SQLERRM
539: , p_transaction_id => p_transaction_id
540: );
541:
542: INV_ITEM_MSG.Count_And_Get
543: ( p_count => x_msg_count
544: , p_data => x_msg_data
545: );
546:
566: )
567: IS
568: l_api_name CONSTANT VARCHAR2(30) := 'Delete_Category_Assignment';
569: l_api_version CONSTANT NUMBER := 1.0;
570: Mctx INV_ITEM_MSG.Msg_Ctx_type;
571: l_row_count NUMBER;
572: l_control_level NUMBER;
573: p_master_org_id NUMBER;
574: l_category_struct_id NUMBER;
648: END IF;
649:
650: -- Initialize message list
651: IF (FND_API.To_Boolean (p_init_msg_list)) THEN
652: INV_ITEM_MSG.Initialize;
653: END IF;
654:
655: -- Define message context
656: Mctx.Package_Name := G_PKG_NAME;
658:
659: -- Initialize API return status to success
660: x_return_status := FND_API.g_RET_STS_SUCCESS;
661:
662: --INV_ITEM_MSG.Debug(Mctx, 'NO VALIDATION IMPLEMENTED');
663: --INV_ITEM_MSG.Debug(Mctx, 'before DELETE FROM mtl_item_categories');
664:
665: OPEN category_sets_csr (p_category_set_id);
666: FETCH category_sets_csr INTO l_control_level, l_category_id;
659: -- Initialize API return status to success
660: x_return_status := FND_API.g_RET_STS_SUCCESS;
661:
662: --INV_ITEM_MSG.Debug(Mctx, 'NO VALIDATION IMPLEMENTED');
663: --INV_ITEM_MSG.Debug(Mctx, 'before DELETE FROM mtl_item_categories');
664:
665: OPEN category_sets_csr (p_category_set_id);
666: FETCH category_sets_csr INTO l_control_level, l_category_id;
667:
666: FETCH category_sets_csr INTO l_control_level, l_category_id;
667:
668: IF (category_sets_csr%NOTFOUND) THEN
669: CLOSE category_sets_csr;
670: --INV_ITEM_MSG.Add_Error('INV_CATEGORY_SET_ID_NOT_FOUND');
671: INV_ITEM_MSG.Add_Message
672: ( p_Msg_Name => 'INV_CATEGORY_SET_ID_NOT_FOUND'
673: , p_transaction_id => p_transaction_id
674: );
667:
668: IF (category_sets_csr%NOTFOUND) THEN
669: CLOSE category_sets_csr;
670: --INV_ITEM_MSG.Add_Error('INV_CATEGORY_SET_ID_NOT_FOUND');
671: INV_ITEM_MSG.Add_Message
672: ( p_Msg_Name => 'INV_CATEGORY_SET_ID_NOT_FOUND'
673: , p_transaction_id => p_transaction_id
674: );
675: RAISE FND_API.g_EXC_ERROR;
750: or(FF11 = 'Y' and (cust_order_flagg = 'Y' OR int_order_flagg = 'Y'))
751: or(FF12 = 'Y' and (gdsn_outbound_enabled_flag = 'Y'))
752: or(FF21 = 'Y' and (gdsn_outbound_enabled_flag = 'Y'))) THEN
753: IF ((cnt_cat <= 1) or (cat_flagg = 'Y')) then
754: INV_ITEM_MSG.Add_Message
755: ( p_Msg_Name => 'INV_DEL_MAND_CAT_SET'
756: , p_transaction_id => p_transaction_id
757: );
758: RAISE FND_API.g_EXC_ERROR;
766: FETCH category_exists_csr INTO l_category_struct_id;
767:
768: IF (category_exists_csr%NOTFOUND) THEN
769: CLOSE category_exists_csr;
770: --INV_ITEM_MSG.Add_Error('INV_CATEGORY_ID_NOT_FOUND');
771: INV_ITEM_MSG.Add_Message
772: ( p_Msg_Name => 'INV_CATEGORY_ID_NOT_FOUND'
773: , p_transaction_id => p_transaction_id
774: );
767:
768: IF (category_exists_csr%NOTFOUND) THEN
769: CLOSE category_exists_csr;
770: --INV_ITEM_MSG.Add_Error('INV_CATEGORY_ID_NOT_FOUND');
771: INV_ITEM_MSG.Add_Message
772: ( p_Msg_Name => 'INV_CATEGORY_ID_NOT_FOUND'
773: , p_transaction_id => p_transaction_id
774: );
775: RAISE FND_API.g_EXC_ERROR;
781: FROM mtl_parameters
782: WHERE organization_id = p_organization_id;
783:
784: IF ((l_control_level = 1) and (p_organization_id <> p_master_org_id)) THEN
785: --INV_ITEM_MSG.Add_Error('INV_CAT_CANNOT_CREATE_DELETE');
786: INV_ITEM_MSG.Add_Message
787: ( p_Msg_Name => 'INV_CAT_CANNOT_CREATE_DELETE'
788: , p_transaction_id => p_transaction_id
789: );
782: WHERE organization_id = p_organization_id;
783:
784: IF ((l_control_level = 1) and (p_organization_id <> p_master_org_id)) THEN
785: --INV_ITEM_MSG.Add_Error('INV_CAT_CANNOT_CREATE_DELETE');
786: INV_ITEM_MSG.Add_Message
787: ( p_Msg_Name => 'INV_CAT_CANNOT_CREATE_DELETE'
788: , p_transaction_id => p_transaction_id
789: );
790: RAISE FND_API.g_EXC_ERROR;
812: AND category_id = p_category_id;
813: END IF;
814:
815: IF (SQL%NOTFOUND) THEN
816: --INV_ITEM_MSG.Add_Warning('INV_CAT_ASSGN_NOT_FOUND');
817: INV_ITEM_MSG.Add_Message
818: ( p_Msg_Name => 'INV_CAT_ASSGN_NOT_FOUND'
819: , p_transaction_id => p_transaction_id
820: );
813: END IF;
814:
815: IF (SQL%NOTFOUND) THEN
816: --INV_ITEM_MSG.Add_Warning('INV_CAT_ASSGN_NOT_FOUND');
817: INV_ITEM_MSG.Add_Message
818: ( p_Msg_Name => 'INV_CAT_ASSGN_NOT_FOUND'
819: , p_transaction_id => p_transaction_id
820: );
821: --add 8310065 with base bug 8351807
822: RAISE FND_API.g_EXC_ERROR;
823: END IF;
824:
825: IF (l_debug = 1) THEN
826: INV_ITEM_MSG.Debug(Mctx, 'after DELETE FROM mtl_item_categories');
827: END IF;
828:
829: -- Standard check of p_commit
830: IF (FND_API.To_Boolean (p_commit)) THEN
830: IF (FND_API.To_Boolean (p_commit)) THEN
831: COMMIT WORK;
832: END IF;
833:
834: INV_ITEM_MSG.Count_And_Get
835: ( p_count => x_msg_count
836: , p_data => x_msg_data
837: );
838:
840:
841: WHEN FND_API.g_EXC_ERROR THEN
842: ROLLBACK TO Delete_Category_Assignment_PVT;
843: x_return_status := FND_API.g_RET_STS_ERROR;
844: INV_ITEM_MSG.Count_And_Get
845: ( p_count => x_msg_count
846: , p_data => x_msg_data
847: );
848:
848:
849: WHEN FND_API.g_EXC_UNEXPECTED_ERROR THEN
850: ROLLBACK TO Delete_Category_Assignment_PVT;
851: x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
852: INV_ITEM_MSG.Count_And_Get
853: ( p_count => x_msg_count
854: , p_data => x_msg_data
855: );
856:
857: WHEN others THEN
858: ROLLBACK TO Delete_Category_Assignment_PVT;
859: x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
860:
861: --INV_ITEM_MSG.Add_Unexpected_Error (Mctx, SQLERRM);
862: INV_ITEM_MSG.Add_Message
863: ( p_Msg_Name => 'INV_ITEM_UNEXPECTED_ERROR'
864: , p_token1 => 'PKG_NAME'
865: , p_value1 => Mctx.Package_Name
858: ROLLBACK TO Delete_Category_Assignment_PVT;
859: x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
860:
861: --INV_ITEM_MSG.Add_Unexpected_Error (Mctx, SQLERRM);
862: INV_ITEM_MSG.Add_Message
863: ( p_Msg_Name => 'INV_ITEM_UNEXPECTED_ERROR'
864: , p_token1 => 'PKG_NAME'
865: , p_value1 => Mctx.Package_Name
866: , p_token2 => 'PROCEDURE_NAME'
870: , p_transaction_id => p_transaction_id
871: );
872:
873:
874: INV_ITEM_MSG.Count_And_Get
875: ( p_count => x_msg_count
876: , p_data => x_msg_data
877: );
878:
1069: )
1070: IS
1071: l_api_name CONSTANT VARCHAR2(30) := 'Update_Category_Assignment';
1072: l_api_version CONSTANT NUMBER := 1.0;
1073: Mctx INV_ITEM_MSG.Msg_Ctx_type;
1074: l_row_count NUMBER;
1075: l_control_level NUMBER;
1076: p_master_org_id NUMBER;
1077: l_category_struct_id NUMBER;
1131: END IF;
1132:
1133: -- Initialize message list
1134: IF (FND_API.To_Boolean (p_init_msg_list)) THEN
1135: INV_ITEM_MSG.Initialize;
1136: END IF;
1137:
1138: -- Define message context
1139: Mctx.Package_Name := G_PKG_NAME;
1143: x_return_status := FND_API.g_RET_STS_SUCCESS;
1144:
1145: --* Checking whether Category Set Id is valid or not
1146: IF (l_debug = 1) THEN
1147: INV_ITEM_MSG.Debug(Mctx, 'Checking whether Category Set Id is valid or not');
1148: END IF;
1149: OPEN category_sets_csr (p_category_set_id);
1150: FETCH category_sets_csr INTO l_category_set_struct_id,
1151: l_category_set_restrict_cats,
1154: ,l_hierarchy_enabled;
1155:
1156: IF (category_sets_csr%NOTFOUND) THEN
1157: CLOSE category_sets_csr;
1158: INV_ITEM_MSG.Add_Message
1159: ( p_Msg_Name => 'INV_CATEGORY_SET_ID_NOT_FOUND'
1160: , p_transaction_id => p_transaction_id
1161: );
1162: RAISE FND_API.g_EXC_ERROR;
1172: AND category_set_id = p_category_set_id
1173: AND category_id = p_old_category_id;
1174:
1175: IF l_reccount = 0 THEN
1176: INV_ITEM_MSG.Add_Message
1177: ( p_Msg_Name => 'INV_CAT_ASSGN_NOT_FOUND'
1178: , p_transaction_id => p_transaction_id
1179: );
1180: RAISE FND_API.g_EXC_ERROR;
1181: END IF;
1182:
1183: --* Checking whether New Category Id is valid or not
1184: IF (l_debug = 1) THEN
1185: INV_ITEM_MSG.Debug(Mctx, 'Checking whether New Category Id is valid or not');
1186: END IF;
1187: OPEN category_exists_csr (p_category_id);
1188: FETCH category_exists_csr INTO l_category_struct_id;
1189:
1188: FETCH category_exists_csr INTO l_category_struct_id;
1189:
1190: IF (category_exists_csr%NOTFOUND) THEN
1191: CLOSE category_exists_csr;
1192: INV_ITEM_MSG.Add_Message
1193: ( p_Msg_Name => 'INV_CATEGORY_ID_NOT_FOUND'
1194: , p_transaction_id => p_transaction_id
1195: );
1196: RAISE FND_API.g_EXC_ERROR;
1198: CLOSE category_exists_csr;
1199:
1200: -- Category structure_id must be the same as structure_id defined in the Category Set.
1201: IF (l_debug = 1) THEN
1202: INV_ITEM_MSG.Debug(Mctx, 'Checking whether Category structure id is the same as structure_id defined in the Category Set.');
1203: END IF;
1204: IF (l_category_struct_id <> l_category_set_struct_id) THEN
1205: INV_ITEM_MSG.Add_Message
1206: ( p_Msg_Name => 'INV_INVALID_CATEGORY_STRUCTURE'
1201: IF (l_debug = 1) THEN
1202: INV_ITEM_MSG.Debug(Mctx, 'Checking whether Category structure id is the same as structure_id defined in the Category Set.');
1203: END IF;
1204: IF (l_category_struct_id <> l_category_set_struct_id) THEN
1205: INV_ITEM_MSG.Add_Message
1206: ( p_Msg_Name => 'INV_INVALID_CATEGORY_STRUCTURE'
1207: , p_transaction_id => p_transaction_id
1208: );
1209: RAISE FND_API.g_EXC_ERROR;
1212: -- If a Category Set is defined with the VALIDATE_FLAG = 'Y' then
1213: -- a Category must belong to a list of categories in the table MTL_CATEGORY_SET_VALID_CATS.
1214: IF (l_category_set_restrict_cats = 'Y') THEN
1215: IF (l_debug = 1) THEN
1216: INV_ITEM_MSG.Debug(Mctx, 'Category Set has a restricted list of categories');
1217: INV_ITEM_MSG.Debug(Mctx, 'Validate Category Set valid category');
1218: END IF;
1219:
1220: --* Validating whether new category id exists in table MTL_CATEGORY_SET_VALID_CATS
1213: -- a Category must belong to a list of categories in the table MTL_CATEGORY_SET_VALID_CATS.
1214: IF (l_category_set_restrict_cats = 'Y') THEN
1215: IF (l_debug = 1) THEN
1216: INV_ITEM_MSG.Debug(Mctx, 'Category Set has a restricted list of categories');
1217: INV_ITEM_MSG.Debug(Mctx, 'Validate Category Set valid category');
1218: END IF;
1219:
1220: --* Validating whether new category id exists in table MTL_CATEGORY_SET_VALID_CATS
1221: OPEN category_set_valid_cats_csr (p_category_set_id, p_category_id);
1221: OPEN category_set_valid_cats_csr (p_category_set_id, p_category_id);
1222: FETCH category_set_valid_cats_csr INTO l_exists;
1223: IF (category_set_valid_cats_csr%NOTFOUND) THEN
1224: CLOSE category_set_valid_cats_csr;
1225: INV_ITEM_MSG.Add_Message
1226: ( p_Msg_Name => 'INV_CATEGORY_NOT_IN_VALID_SET'
1227: , p_transaction_id => p_transaction_id
1228: );
1229: RAISE FND_API.g_EXC_ERROR;
1233:
1234: --* Disallow updation if category is master controlled and current org
1235: --* is not master org.
1236: IF (l_debug = 1) THEN
1237: INV_ITEM_MSG.Debug(Mctx, 'Select Master Org from Mtl_Parameters');
1238: END IF;
1239:
1240: SELECT MASTER_ORGANIZATION_ID
1241: INTO p_master_org_id
1242: FROM mtl_parameters
1243: WHERE organization_id = p_organization_id;
1244:
1245: IF ((l_control_level = 1) and (p_organization_id <> p_master_org_id)) THEN
1246: INV_ITEM_MSG.Add_Message
1247: ( p_Msg_Name => 'INV_CAT_CANNOT_CREATE_DELETE'
1248: , p_transaction_id => p_transaction_id
1249: );
1250: RAISE FND_API.g_EXC_ERROR;
1252:
1253: /* Commented for Bug 4609655 - Checking not required
1254: --* checking for duplicate records
1255: IF (l_debug = 1) THEN
1256: INV_ITEM_MSG.Debug(Mctx, 'Checking for duplicate records');
1257: END IF;
1258: SELECT Count(1)
1259: INTO l_reccount
1260: FROM mtl_item_categories
1263: AND category_set_id = p_category_set_id
1264: AND category_id = p_category_id;
1265:
1266: IF l_reccount > 0 THEN
1267: INV_ITEM_MSG.Add_Message
1268: ( p_Msg_Name => 'INV_CAT_ASSGN_ALREADY_EXISTS'
1269: , p_transaction_id => p_transaction_id
1270: );
1271: RAISE FND_API.g_EXC_ERROR;
1273: End of Commenting for Bug 4609655 */
1274:
1275: --* Validating if new category is leafnode or not
1276: IF (l_debug = 1) THEN
1277: INV_ITEM_MSG.Debug(Mctx, 'Validate If new category is leafnode or not');
1278: END IF;
1279:
1280: IF NOT Is_Category_Leafnode ( p_category_set_id,
1281: p_category_id,
1280: IF NOT Is_Category_Leafnode ( p_category_set_id,
1281: p_category_id,
1282: l_category_set_restrict_cats,
1283: l_hierarchy_enabled ) THEN
1284: INV_ITEM_MSG.Add_Message
1285: ( p_Msg_Name => 'INV_ITEM_CAT_ASSIGN_LEAF_ONLY'
1286: , p_transaction_id => p_transaction_id
1287: );
1288: RAISE FND_API.g_EXC_ERROR;
1290:
1291:
1292: --* Updating Master Org or Master Org + Child Orgs depending on Control Level
1293: IF (l_debug = 1) THEN
1294: INV_ITEM_MSG.Debug(Mctx, 'Updating Mtl_Item_Categories...');
1295: END IF;
1296:
1297: IF ((l_control_level = 1) and (p_organization_id = p_master_org_id)) THEN
1298: UPDATE /*+ INDEX(MIC MTL_ITEM_CATEGORIES_U1) */
1394:
1395: END IF;
1396:
1397: IF (l_debug = 1) THEN
1398: INV_ITEM_MSG.Debug(Mctx, 'after update FROM mtl_item_categories');
1399: END IF;
1400:
1401: -- Standard check of p_commit
1402: IF (FND_API.To_Boolean (p_commit)) THEN
1402: IF (FND_API.To_Boolean (p_commit)) THEN
1403: COMMIT WORK;
1404: END IF;
1405:
1406: INV_ITEM_MSG.Count_And_Get
1407: ( p_count => x_msg_count
1408: , p_data => x_msg_data
1409: );
1410:
1412:
1413: WHEN FND_API.g_EXC_ERROR THEN
1414: ROLLBACK TO Update_Category_Assignment_PVT;
1415: x_return_status := FND_API.g_RET_STS_ERROR;
1416: INV_ITEM_MSG.Count_And_Get
1417: ( p_count => x_msg_count
1418: , p_data => x_msg_data
1419: );
1420:
1420:
1421: WHEN FND_API.g_EXC_UNEXPECTED_ERROR THEN
1422: ROLLBACK TO Update_Category_Assignment_PVT;
1423: x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
1424: INV_ITEM_MSG.Count_And_Get
1425: ( p_count => x_msg_count
1426: , p_data => x_msg_data
1427: );
1428:
1428:
1429: WHEN Processing_Error THEN
1430: ROLLBACK TO Update_Category_Assignment_PVT;
1431: x_return_status := l_return_status;
1432: INV_ITEM_MSG.Count_And_Get
1433: ( p_count => x_msg_count
1434: , p_data => x_msg_data
1435: );
1436:
1437: WHEN others THEN
1438: ROLLBACK TO Update_Category_Assignment_PVT;
1439: x_return_status := FND_API.g_RET_STS_UNEXP_ERROR;
1440:
1441: INV_ITEM_MSG.Add_Message
1442: ( p_Msg_Name => 'INV_ITEM_UNEXPECTED_ERROR'
1443: , p_token1 => 'PKG_NAME'
1444: , p_value1 => Mctx.Package_Name
1445: , p_token2 => 'PROCEDURE_NAME'
1449: , p_transaction_id => p_transaction_id
1450: );
1451:
1452:
1453: INV_ITEM_MSG.Count_And_Get
1454: ( p_count => x_msg_count
1455: , p_data => x_msg_data
1456: );
1457: