[Home] [Help]
PACKAGE BODY: APPS.INV_ITEM_CATEGORY_PVT
Source
1 PACKAGE BODY INV_ITEM_CATEGORY_PVT AS
2 /* $Header: INVVCATB.pls 120.9 2007/10/16 09:31:19 lparihar ship $ */
3
4
5 ---------------------- Package variables and constants -----------------------
6
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'INV_ITEM_CATEGORY_PVT';
8
9 G_INV_APP_ID CONSTANT NUMBER := 401;
10 G_INV_APP_SHORT_NAME CONSTANT VARCHAR2(3) := 'INV';
11 G_CAT_FLEX_CODE CONSTANT VARCHAR2(4) := 'MCAT';
12
13 -- Operations
14
15 c_INSERT CONSTANT VARCHAR2(3) := 'INS';
16 c_UPDATE CONSTANT VARCHAR2(3) := 'UPD';
17 c_DELETE CONSTANT VARCHAR2(3) := 'DEL';
18
19 ------------------------------------------------------------------------------
20
21
22 ---------------------------- Validate_Assignment -----------------------------
23 /*
24 PROCEDURE Validate_Assignment
25 ( p_Debug_Level IN NUMBER
26 , p_Msg_Text IN VARCHAR2
27 ) IS
28 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
29 BEGIN
30
31 -- currently validation is done in the procedure Create_Category_Assignment
32
33 END Validate_Assignment;
34 ------------------------------------------------------------------------------
35 */
36 --Bug: 2996160
37 FUNCTION Is_Category_Leafnode
38 ( p_category_set_id IN NUMBER
39 , p_category_id IN NUMBER
40 , p_validate_flag IN VARCHAR2
41 , p_hierarchy_enabled IN VARCHAR2
42 ) RETURN BOOLEAN;
43
44 ------------------------- Create_Category_Assignment -------------------------
45
46 PROCEDURE Create_Category_Assignment
47 (
48 p_api_version IN NUMBER
49 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_FALSE
50 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_FALSE
51 , p_validation_level IN NUMBER DEFAULT INV_ITEM_CATEGORY_PVT.g_VALIDATE_ALL
52 , p_inventory_item_id IN NUMBER
53 , p_organization_id IN NUMBER
54 , p_category_set_id IN NUMBER
55 , p_category_id IN NUMBER
56 , p_transaction_id IN NUMBER
57 , p_request_id IN NUMBER
58 , x_return_status OUT NOCOPY VARCHAR2
59 , x_msg_count OUT NOCOPY NUMBER
60 , x_msg_data OUT NOCOPY VARCHAR2
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);
70 l_category_set_struct_id NUMBER;
71 l_category_struct_id NUMBER;
72 l_the_item_assign_count NUMBER;
73 l_the_cat_assign_count NUMBER;
74 l_control_level NUMBER;
75 p_master_org_id NUMBER;
76 l_request_id NUMBER;--2879647
77 l_hierarchy_enabled VARCHAR2(1);
78 l_approval_status MTL_SYSTEM_ITEMS_B.APPROVAL_STATUS%TYPE;
79 -- l_assign_exists BOOLEAN;
80
81 CURSOR org_item_exists_csr
82 ( p_inventory_item_id NUMBER
83 , p_organization_id NUMBER
84 ) IS
85 SELECT 'x',request_id, approval_status --2879647
86 FROM mtl_system_items_b
87 WHERE inventory_item_id = p_inventory_item_id
88 AND organization_id = p_organization_id;
89 --AND NVL(approval_status,'A') = 'A'; --Added for 11.5.10 PLM
90
91 CURSOR category_sets_csr (p_category_set_id NUMBER)
92 IS
93 SELECT structure_id, validate_flag, mult_item_cat_assign_flag,
94 control_level
95 ,hierarchy_enabled--Bug: 2996160
96 FROM mtl_category_sets_b
97 WHERE category_set_id = p_category_set_id;
98
99 CURSOR category_exists_csr (p_category_id NUMBER)
100 IS
101 SELECT structure_id
102 FROM mtl_categories_b
103 WHERE category_id = p_category_id
104 AND NVL(DISABLE_DATE,SYSDATE+1) > SYSDATE;--Bug: 2996160
105
106 CURSOR category_set_valid_cats_csr
107 ( p_category_set_id NUMBER
108 , p_category_id NUMBER
109 ) IS
110 SELECT 'x'
111 FROM mtl_category_set_valid_cats
112 WHERE category_set_id = p_category_set_id
113 AND category_id = p_category_id;
114
115 CURSOR item_cat_assign_count_csr
116 ( p_inventory_item_id NUMBER
117 , p_organization_id NUMBER
118 , p_category_set_id NUMBER
119 , p_category_id NUMBER
120 ) IS
121 SELECT COUNT( category_id ), COUNT( DECODE(category_id, p_category_id,1, NULL) )
122 FROM mtl_item_categories
123 WHERE
124 inventory_item_id = p_inventory_item_id
125 AND organization_id = p_organization_id
126 AND category_set_id = p_category_set_id;
127
128 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
129 l_default_cats number;
130 BEGIN
131
132 -- Set savepoint
133 SAVEPOINT Create_Category_Assignment_PVT;
134
135 -- INVPUTLI.info('Add_Message: p_Msg_Name=' || p_Msg_Name);
136
137 --dbms_output.put_line('Enter INV_ITEM_CATEGORY_PVT.Create_Category_Assignment');
138
139 -- Check for call compatibility
140 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
141 l_api_name, G_PKG_NAME)
142 THEN
143 RAISE FND_API.g_EXC_UNEXPECTED_ERROR;
144 END IF;
145
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;
155 Mctx.Procedure_Name := l_api_name;
156
157 -- Initialize API return status to success
158 x_return_status := FND_API.g_RET_STS_SUCCESS;
159
160 -- Check for NULL parameter values
161
162 /* IF ( p_Item_ID = fnd_api.g_MISS_NUM ) OR ( p_Item_ID IS NULL ) OR
163 ( p_Org_ID = fnd_api.g_MISS_NUM ) OR ( p_Org_ID IS NULL )
164 */
165
166 --dbms_output.put_line('Before IS NULL ; x_return_status = ' || x_return_status);
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 );
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
186 --dbms_output.put_line('Before OPEN org_item_exists_csr ; x_return_status = ' || x_return_status);
187
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 );
197 RAISE FND_API.g_EXC_ERROR;
198 END IF;
199
200 --6355354:Unapproved item can have categories assigned.
201 --Bug: 4046709
202 --If item is an NIR item and it is not approved
203
204 IF l_approval_status IS NOT NULL AND l_approval_status <> 'A' THEN
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;
214 END IF;
215 END IF;
216
217
218 CLOSE org_item_exists_csr;
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
228 OPEN category_sets_csr (p_category_set_id);
229 FETCH category_sets_csr INTO l_category_set_struct_id,
230 l_category_set_restrict_cats,
231 l_mult_item_cat_assign_flag,
232 l_control_level
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 );
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
252 --dbms_output.put_line('Before OPEN category_exists_csr ; x_return_status = ' || x_return_status);
253
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 );
263 RAISE FND_API.g_EXC_ERROR;
264 END IF;
265 CLOSE category_exists_csr;
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
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;
284 END IF;
285
286 -- If Category set control level is master and organization being processed is not master then error
287
288 -- Get master org
289 SELECT MASTER_ORGANIZATION_ID
290 INTO p_master_org_id
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 );
300 RAISE FND_API.g_EXC_ERROR;
301 END IF;
302
303 -- End If Category set control level is master
304
305 -- If a Category Set is defined with the VALIDATE_FLAG = 'Y' then
306 -- a Category must belong to a list of categories in the table MTL_CATEGORY_SET_VALID_CATS.
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);
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 );
325 RAISE FND_API.g_EXC_ERROR;
326 END IF;
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,
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;
343 END IF;
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
353 OPEN item_cat_assign_count_csr (p_inventory_item_id,
354 p_organization_id,
355 p_category_set_id,
356 p_category_id);
357
358 FETCH item_cat_assign_count_csr INTO l_the_item_assign_count, l_the_cat_assign_count;
359
360 -- If a Category Set is defined with the MULT_ITEM_CAT_ASSIGN_FLAG set to 'N'
361 -- then an Item may be assigned to only one Category in the Category Set.
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
371 --Modified for bug 3255128
372 IF (l_control_level = 1) THEN
373 DELETE FROM mtl_item_categories
374 WHERE inventory_item_id = p_inventory_item_id
375 AND category_set_id = p_category_set_id;
376 ELSE
377 DELETE FROM mtl_item_categories
378 WHERE organization_id = p_organization_id
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 );
388 RAISE FND_API.g_EXC_ERROR;
389 END IF;
390 --ELSIF (l_the_cat_assign_count = 0) THEN
391 -- TODO:
392 -- Check if Master Item category assignment permits the Org Item assignment.
393
394 END IF;
395
396 -- If an assignment does not exist, insert into the assignments table
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
406 -- If the category control level is 1 and we are inserting for master record then the assignemnt should also be made for child records.
407 INSERT INTO mtl_item_categories
408 (
409 inventory_item_id
410 , organization_id
411 , category_set_id
412 , category_id
413 , creation_date
414 , created_by
415 , last_update_date
416 , last_updated_by
417 , last_update_login
418 , request_id --4105867
419 )
420 SELECT
421 p_inventory_item_id
422 , p.organization_id
423 , p_category_set_id
424 , p_category_id
425 , SYSDATE
426 , FND_GLOBAL.user_id
427 , SYSDATE
428 , FND_GLOBAL.user_id
429 , FND_GLOBAL.login_id
430 , FND_GLOBAL.conc_request_id
431 FROM mtl_parameters p , mtl_system_items_b i
432 WHERE p.master_organization_id = p_master_org_id
433 AND i.inventory_item_id = p_inventory_item_id
434 AND i.organization_id = p.organization_id
435 AND not exists
436 (SELECT 'x'
437 FROM mtl_item_categories
438 whERE inventory_item_id = p_inventory_item_id
439 AND organization_id = p.organization_id
440 AND category_set_id = p_category_set_id
441 AND category_id = p_category_id);
442 ELSE
443 INSERT INTO mtl_item_categories
444 (
445 inventory_item_id
446 , organization_id
447 , category_set_id
448 , category_id
449 , creation_date
450 , created_by
451 , last_update_date
452 , last_updated_by
453 , last_update_login
454 , request_id --4105867
455 )
456 VALUES
457 (
458 p_inventory_item_id
459 , p_organization_id
460 , p_category_set_id
461 , p_category_id
462 , SYSDATE
463 , FND_GLOBAL.user_id
464 , SYSDATE
465 , FND_GLOBAL.user_id
466 , FND_GLOBAL.login_id
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
476 ( p_Msg_Name => 'INV_CAT_ASSGN_ALREADY_EXISTS'
477 , p_transaction_id => p_transaction_id
478 );
479 END IF;
480
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;
490
491 INV_ITEM_MSG.Count_And_Get
492 ( p_count => x_msg_count
493 , p_data => x_msg_data
494 );
495
496 EXCEPTION
497
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
525 , p_token2 => 'PROCEDURE_NAME'
526 , p_value2 => Mctx.Procedure_Name
527 , p_token3 => 'ERROR_TEXT'
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
537 END Create_Category_Assignment;
538 ------------------------------------------------------------------------------
539
540
541 ------------------------- Delete_Category_Assignment -------------------------
542
543 PROCEDURE Delete_Category_Assignment
544 (
545 p_api_version IN NUMBER
546 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_FALSE
547 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_FALSE
548 , p_inventory_item_id IN NUMBER
549 , p_organization_id IN NUMBER
550 , p_category_set_id IN NUMBER
551 , p_category_id IN NUMBER
552 , p_transaction_id IN NUMBER
553 , x_return_status OUT NOCOPY VARCHAR2
554 , x_msg_count OUT NOCOPY NUMBER
555 , x_msg_data OUT NOCOPY VARCHAR2
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;
565 l_category_id NUMBER;
566 l_count NUMBER;
567 FF1 VARCHAR2(1);
568 FF2 VARCHAR2(1);
569 FF3 VARCHAR2(1);
570 FF4 VARCHAR2(1);
571 FF5 VARCHAR2(1);
572 FF6 VARCHAR2(1);
573 FF7 VARCHAR2(1);
574 FF8 VARCHAR2(1);
575 FF9 VARCHAR2(1);
576 FF10 VARCHAR2(1);
577 FF11 VARCHAR2(1);
578 FF12 VARCHAR2(1); --Bug:6485437
579 FF21 VARCHAR2(1);
580 l_default_catalog_id MTL_DEFAULT_CATEGORY_SETS.CATEGORY_SET_ID%TYPE;
581 gdsn_outbound_enabled_flag MTL_SYSTEM_ITEMS_B.GDSN_OUTBOUND_ENABLED_FLAG%TYPE;
582
583 inv_item_flagg VARCHAR2(1);
584 purch_item_flagg VARCHAR2(1);
585 int_order_flagg VARCHAR2(1);
586 serv_item_flagg VARCHAR2(1);
587 cost_enab_flagg VARCHAR2(1);
588 engg_item_flagg VARCHAR2(1);
589 cust_order_flagg VARCHAR2(1);
590 mrp_plan_code number;
591 default_cat_id NUMBER;
592 cat_flagg VARCHAR2(1);
593 cnt_cat NUMBER ;
594 eam_item_type NUMBER;
595 contract_item_type VARCHAR2(60);
596
597
598
599 CURSOR category_sets_csr (p_category_set_id NUMBER)
600 IS
601 SELECT control_level,default_category_id--Bug:2527058
602 FROM mtl_category_sets_b
603 WHERE category_set_id = p_category_set_id;
604
605 CURSOR category_exists_csr (p_category_id NUMBER)
606 IS
607 SELECT structure_id
608 FROM mtl_categories_b
609 WHERE category_id = p_category_id;
610
611 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
612
613 --Bug:6485437
614 CURSOR default_catalog_csr(cp_functional_area NUMBER)
615 IS
616 SELECT category_set_id
617 FROM mtl_default_category_sets
618 WHERE functional_area_id = cp_functional_area;
619
620 CURSOR fetch_gdsn_flag(cp_inventory_item_id NUMBER,
621 cp_organization_id NUMBER)
622 IS
623 SELECT gdsn_outbound_enabled_flag
624 FROM mtl_system_items_b
625 WHERE inventory_item_id = cp_inventory_item_id
626 AND organization_id = cp_organization_id;
627
628 BEGIN
629
630 -- Set savepoint
631 SAVEPOINT Delete_Category_Assignment_PVT;
632
633 -- Check for call compatibility
634 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
635 l_api_name, G_PKG_NAME)
636 THEN
637 RAISE FND_API.g_EXC_UNEXPECTED_ERROR;
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;
647 Mctx.Procedure_Name := l_api_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;
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;
666 ELSE
667 /* Bug 4046670 To check if he item belongs to a functional area for which the category set is mandatory before deletion - Anmurali */
668 /*Raise error if the category set is a mandatory category set for the functional area to which the item belongs */
669 INVIDSCS.CHECK_CAT_SET_MANDATORY(p_category_set_id,
670 FF1, FF2, FF3, FF4, FF5, FF6, FF7, FF8, FF9, FF10, FF11);
671
672 --FF1 will be Y if category set is a mandatory set for func_area_id = 1
673 --FF2 will be Y if ......... func_area_id = 2...and so on
674
675 --Bug:6485437
676 OPEN default_catalog_csr(cp_functional_area => 12);
677 FETCH default_catalog_csr INTO l_default_catalog_id;
678 CLOSE default_catalog_csr;
679
680 IF ( l_default_catalog_id = p_category_set_id ) THEN
681 FF12 := 'Y';
682 ELSE
683 FF12 := 'N';
684 END IF;
685
686 l_default_catalog_id := null;
687 OPEN default_catalog_csr(cp_functional_area => 21);
688 FETCH default_catalog_csr INTO l_default_catalog_id;
689 CLOSE default_catalog_csr;
690
691 IF ( l_default_catalog_id = p_category_set_id ) THEN
692 FF21 := 'Y';
693 ELSE
694 FF21 := 'N';
695 END IF;
696
697
698 OPEN fetch_gdsn_flag(cp_inventory_item_id => p_inventory_item_id,
699 cp_organization_id => p_organization_id);
700 FETCH fetch_gdsn_flag INTO gdsn_outbound_enabled_flag;
701 CLOSE fetch_gdsn_flag;
702 --Bug:6485437
703
704 INVIDSCS.GET_ITEM_DEFINING_FLAGS(p_inventory_item_id,
705 p_organization_id,
706 inv_item_flagg,
707 purch_item_flagg,
708 int_order_flagg,
709 serv_item_flagg,
710 cost_enab_flagg,
711 engg_item_flagg,
712 cust_order_flagg,
713 mrp_plan_code,
714 eam_item_type,
715 contract_item_type);
716
717 if (l_category_id = p_category_id) then
718 cat_flagg := 'Y';
719 else
720 cat_flagg := 'N';
721 end if;
722
723 SELECT count(category_id)
724 INTO cnt_cat
725 FROM mtl_item_categories
726 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
727 AND ORGANIZATION_ID = p_organization_id
728 AND CATEGORY_SET_ID = p_category_set_id;
729
730 IF ((FF1 = 'Y' and inv_item_flagg = 'Y')
731 or(FF2 = 'Y' and purch_item_flagg = 'Y')
732 or(FF2 = 'Y' and int_order_flagg = 'Y') --note: there are 2 cases for FF2 = Y
733 or(FF3 = 'Y' and mrp_plan_code <> 6)
734 or(FF4 = 'Y' and serv_item_flagg = 'Y')
735 or(FF5 = 'Y' and cost_enab_flagg = 'Y')
736 or(FF6 = 'Y' and engg_item_flagg = 'Y')
737 or(FF7 = 'Y' and cust_order_flagg = 'Y')
738 or(FF9 = 'Y' and eam_item_type IS NOT NULL) --Bug: 2527058
739 or(FF10 = 'Y' and contract_item_type IS NOT NULL)
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;
749 END IF;
750 END IF;
751 -- End of bug fix for Bug 4046670 - Anmurali
752 END IF;
753 CLOSE category_sets_csr;
754
755 OPEN category_exists_csr (p_category_id);
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 );
765 RAISE FND_API.g_EXC_ERROR;
766 END IF;
767 CLOSE category_exists_csr;
768
769 SELECT MASTER_ORGANIZATION_ID
770 INTO p_master_org_id
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 );
780 RAISE FND_API.g_EXC_ERROR;
781 END IF;
782
783 IF ((l_control_level = 1) and (p_organization_id = p_master_org_id)) THEN
784 --Bug: 3561206 Added an index for performance improvement
785 DELETE /*+ INDEX(MIC MTL_ITEM_CATEGORIES_U1) */
786 FROM mtl_item_categories MIC
787 WHERE category_set_id = p_category_set_id
788 AND category_id = p_category_id
789 AND inventory_item_id = p_inventory_item_id
790 AND organization_id =
791 (SELECT organization_id
792 FROM mtl_parameters p
793 WHERE p.master_organization_id = p_master_org_id
794 AND p.organization_id = mic.organization_id);
795 ELSE
796 -- Delete a row from the table
797 --
798 DELETE FROM mtl_item_categories
799 WHERE organization_id = p_organization_id
800 AND inventory_item_id = p_inventory_item_id
801 AND category_set_id = p_category_set_id
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 );
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
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
827 EXCEPTION
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
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
854 , p_token2 => 'PROCEDURE_NAME'
855 , p_value2 => Mctx.Procedure_Name
856 , p_token3 => 'ERROR_TEXT'
857 , p_value3 => SQLERRM
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
867 END Delete_Category_Assignment;
868 ------------------------------------------------------------------------------
869
870
871 -- Get_Category_Rec_Type
872 ------------------------------------------------------------------------------
873 /*
874 FUNCTION Get_Category_Rec_Type
875 RETURN INV_ITEM_CATEGORY_PVT.CATEGORY_REC_TYPE
876 IS
877 l_category_rec_type INV_ITEM_CATEGORY_PVT.CATEGORY_REC_TYPE;
878 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
879 BEGIN
880 RETURN l_category_rec_type;
881 END;
882 */
883
884 /*Bug: 2996160 Category set dependency validations
885 Validate Flag Hieararchy Flag Validation
886 Y Y Only Valid Categories and which
887 does not have children.
888 Y N All Valid Categories
889 N Y All categories but which does not
890 have children
891 N N All Categories for that structure.
892 */
893
894 FUNCTION Is_Category_Leafnode
895 ( p_category_set_id IN NUMBER
896 , p_category_id IN NUMBER
897 , p_validate_flag IN VARCHAR2
898 , p_hierarchy_enabled IN VARCHAR2
899 ) RETURN BOOLEAN
900 IS
901
902 CURSOR hierarchy_and_validate_csr
903 ( p_category_set_id NUMBER
904 , p_category_id NUMBER
905 ) IS
906 SELECT 'x'
907 FROM mtl_Category_set_valid_cats VC
908 WHERE VC.category_set_id = p_category_set_id
909 AND VC.category_id = p_category_id
910 AND NOT EXISTS
911 (SELECT NULL FROM mtl_Category_set_valid_cats
912 WHERE parent_category_id = VC.category_id
913 AND category_set_id = p_category_set_id);
914
915 CURSOR hierarchy_and_not_validate_csr
916 ( p_category_set_id NUMBER
917 , p_category_id NUMBER
918 ) IS
919 SELECT 'x'
920 FROM mtl_Category_set_valid_cats
921 WHERE category_set_id = p_category_set_id
922 AND parent_category_id = p_category_id ;
923
924 l_exists VARCHAR2(10);
925 BEGIN
926 IF (p_hierarchy_enabled = 'Y') THEN
927 IF ( p_validate_flag = 'Y') THEN
928 OPEN hierarchy_and_validate_csr (p_category_set_id , p_category_id);
929 FETCH hierarchy_and_validate_csr INTO l_exists;
930 IF (hierarchy_and_validate_csr%NOTFOUND) THEN
931 CLOSE hierarchy_and_validate_csr;
932 RETURN false;
933 END IF;
934 CLOSE hierarchy_and_validate_csr;
935 ELSE --validate_flag is 'N'
936 OPEN hierarchy_and_not_validate_csr (p_category_set_id, p_category_id);
937 FETCH hierarchy_and_not_validate_csr INTO l_exists;
938 IF (hierarchy_and_not_validate_csr%FOUND) THEN
939 CLOSE hierarchy_and_not_validate_csr;
940 RETURN false;
941 END IF;
942 CLOSE hierarchy_and_not_validate_csr;
943 END IF;
944 END IF;
945 RETURN true;
946 END Is_Category_Leafnode;
947
948 ----------------------------------------------------------------------------
949 -- Create Valid Category
950 -- API to create a valid Category in Category Sets for ENI Upgrade
951 ----------------------------------------------------------------------------
952 PROCEDURE Create_Valid_Category(
953 p_api_version IN NUMBER,
954 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
955 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
956 p_category_set_id IN NUMBER,
957 p_category_id IN NUMBER,
958 p_parent_category_id IN NUMBER,
959 x_return_status OUT NOCOPY VARCHAR2,
960 x_errorcode OUT NOCOPY NUMBER,
961 x_msg_count OUT NOCOPY NUMBER,
962 x_msg_data OUT NOCOPY VARCHAR2
963 ) IS
964 -- Start OF comments
965 -- API name : Create_Valid_Category
966 -- TYPE : Private and USed by ENI Upgrade program alone
967 -- Pre-reqs : 11.5.10 level
968 -- FUNCTION : Create a category.
969 -- This sets the PUB API package level variable
970 -- and calls the corresponding PUB API procedure.
971 -- This will not do validations for ENABLED_FLAG and DISABLE_DATE
972 -- END OF comments
973 BEGIN
974 INV_ITEM_CATEGORY_PUB.g_eni_upgarde_flag := 'Y';
975 INV_ITEM_CATEGORY_PUB.Create_Valid_Category
976 (
977 p_api_version => p_api_version ,
978 p_init_msg_list => p_init_msg_list,
979 p_commit => p_commit ,
980 p_category_set_id => p_category_set_id ,
981 p_category_id => p_category_id ,
982 p_parent_category_id => p_parent_category_id,
983 x_return_status => x_return_status,
984 x_errorcode => x_errorcode ,
985 x_msg_count => x_msg_count ,
986 x_msg_data => x_msg_data
987 );
988 INV_ITEM_CATEGORY_PUB.g_eni_upgarde_flag := 'N';
989 EXCEPTION
990 WHEN OTHERS THEN
991 INV_ITEM_CATEGORY_PUB.g_eni_upgarde_flag := 'N';
992 RAISE;
993 END Create_Valid_Category;
994
995 ----------------------------------------------------------------------------
996 -- Update Category
997 -- API to update a valid Category for ENI Upgrade
998 ----------------------------------------------------------------------------
999 PROCEDURE Update_Valid_Category(
1000 p_api_version IN NUMBER,
1001 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1002 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1003 p_category_set_id IN NUMBER,
1004 p_category_id IN NUMBER,
1005 p_parent_category_id IN NUMBER,
1006 x_return_status OUT NOCOPY VARCHAR2,
1007 x_errorcode OUT NOCOPY NUMBER,
1008 x_msg_count OUT NOCOPY NUMBER,
1009 x_msg_data OUT NOCOPY VARCHAR2
1010 ) IS
1011 -- Start OF comments
1012 -- API name : Update_Valid_Category
1013 -- TYPE : Private and USed by ENI Upgrade program alone
1014 -- Pre-reqs : 11.5.10 level
1015 -- FUNCTION : Create a category.
1016 -- This sets the PUB API package level variable
1017 -- and calls the corresponding PUB API procedure.
1018 -- This will not do validations for ENABLED_FLAG and DISABLE_DATE
1019 -- END OF comments
1020 BEGIN
1021 INV_ITEM_CATEGORY_PUB.g_eni_upgarde_flag := 'Y';
1022 INV_ITEM_CATEGORY_PUB.Update_Valid_Category
1023 (
1024 p_api_version => p_api_version ,
1025 p_init_msg_list => p_init_msg_list,
1026 p_commit => p_commit ,
1027 p_category_set_id => p_category_set_id ,
1028 p_category_id => p_category_id ,
1029 p_parent_category_id => p_parent_category_id,
1030 x_return_status => x_return_status,
1031 x_errorcode => x_errorcode ,
1032 x_msg_count => x_msg_count ,
1033 x_msg_data => x_msg_data
1034 );
1035 INV_ITEM_CATEGORY_PUB.g_eni_upgarde_flag := 'N';
1036 EXCEPTION
1037 WHEN OTHERS THEN
1038 INV_ITEM_CATEGORY_PUB.g_eni_upgarde_flag := 'N';
1039 RAISE;
1040 END Update_Valid_Category;
1041
1042 -- Procedure Update_Category_Assignment added for Bug #3991044
1043 PROCEDURE Update_Category_Assignment
1044 (
1045 p_api_version IN NUMBER
1046 , p_init_msg_list IN VARCHAR2
1047 , p_commit IN VARCHAR2
1048 , p_inventory_item_id IN NUMBER
1049 , p_organization_id IN NUMBER
1050 , p_category_set_id IN NUMBER
1051 , p_category_id IN NUMBER
1052 , p_old_category_id IN NUMBER
1053 , p_transaction_id IN NUMBER
1054 , x_return_status OUT NOCOPY VARCHAR2
1055 , x_msg_count OUT NOCOPY NUMBER
1056 , x_msg_data OUT NOCOPY VARCHAR2
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;
1066
1067 l_return_status VARCHAR2(1);
1068 l_msg_count NUMBER;
1069 l_msg_data VARCHAR2(2000);
1070 Processing_Error EXCEPTION;
1071
1072 l_old_category_struct_id NUMBER;
1073 l_old_category_id NUMBER;
1074 l_reccount NUMBER :=0;
1075 l_category_set_restrict_cats VARCHAR2(1);
1076 l_exists VARCHAR2(1);
1077 l_category_set_struct_id NUMBER;
1078 l_hierarchy_enabled VARCHAR2(1);
1079 l_mult_item_cat_assign_flag VARCHAR2(1);
1080
1081 CURSOR category_sets_csr (p_category_set_id NUMBER)
1082 IS
1083 SELECT structure_id,
1084 validate_flag,
1085 mult_item_cat_assign_flag,
1086 control_level,
1087 hierarchy_enabled
1088 FROM mtl_category_sets_b
1089 WHERE category_set_id = p_category_set_id;
1090
1091 CURSOR category_set_valid_cats_csr
1092 ( p_category_set_id NUMBER
1093 , p_category_id NUMBER
1094 ) IS
1095 SELECT 'x'
1096 FROM mtl_category_set_valid_cats
1097 WHERE category_set_id = p_category_set_id
1098 AND category_id = p_category_id;
1099
1100
1101 CURSOR category_exists_csr (p_category_id NUMBER)
1102 IS
1103 SELECT structure_id
1104 FROM mtl_categories_b
1105 WHERE category_id = p_category_id
1106 AND NVL(DISABLE_DATE,SYSDATE+1) > SYSDATE; /*Bug no: 5946409 Checking whether the category is disabled */
1107
1108 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1109 BEGIN
1110
1111 -- Set savepoint
1112 SAVEPOINT Update_Category_Assignment_PVT;
1113
1114 -- Check for call compatibility
1115 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
1116 l_api_name, G_PKG_NAME)
1117 THEN
1118 RAISE FND_API.g_EXC_UNEXPECTED_ERROR;
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;
1128 Mctx.Procedure_Name := l_api_name;
1129
1130 -- Initialize API return status to success
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,
1140 l_mult_item_cat_assign_flag,
1141 l_control_level
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;
1151 END IF;
1152 CLOSE category_sets_csr;
1153
1154 --* Checking whether Category Assignment exists for old category id
1155 SELECT Count(1)
1156 INTO l_reccount
1157 FROM mtl_item_categories
1158 WHERE inventory_item_id = p_inventory_item_id
1159 AND organization_id = p_organization_id
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
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;
1185 END IF;
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'
1195 , p_transaction_id => p_transaction_id
1196 );
1197 RAISE FND_API.g_EXC_ERROR;
1198 END IF;
1199
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
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;
1218 END IF;
1219 CLOSE category_set_valid_cats_csr;
1220 END IF;
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;
1239 END IF;
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
1249 WHERE inventory_item_id = p_inventory_item_id
1250 AND organization_id = p_organization_id
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;
1260 END IF;
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,
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;
1277 END IF;
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) */
1287 Mtl_Item_Categories MIC
1288 SET Category_Id = p_category_id
1289 ,last_update_date = SYSDATE
1290 ,last_updated_by = FND_GLOBAL.user_id
1291 ,last_update_login = FND_GLOBAL.login_id
1292 ,request_id = FND_GLOBAL.conc_request_id -- 4105867
1293 WHERE category_set_id = p_category_set_id
1294 AND category_id = p_old_category_id
1295 AND inventory_item_id = p_inventory_item_id
1296 AND organization_id =(SELECT organization_id
1297 FROM mtl_parameters p
1298 WHERE p.master_organization_id = p_master_org_id
1299 AND p.organization_id = mic.organization_id);
1300
1301 --Bug 6008273
1302 --Category assignment is not getting updated in eni_oltp_item_star table
1303 --when user update category assignment through
1304 --INV_ITEM_CATEGORY_PUB.Update_Category_Assignment
1305
1306 INV_ENI_ITEMS_STAR_PKG.Sync_Category_Assignments(
1307 p_api_version => p_api_version
1308 ,p_init_msg_list => p_init_msg_list
1309 ,p_inventory_item_id => p_inventory_item_id
1310 ,p_organization_id => p_organization_id
1311 ,p_category_set_id => p_category_set_id
1312 ,p_old_category_id => p_old_category_id
1313 ,p_new_category_id => p_category_id
1314 ,x_return_status => l_return_status
1315 ,x_msg_count => l_msg_count
1316 ,x_msg_data => l_msg_data);
1317
1318 IF ( l_return_status = fnd_api.g_RET_STS_UNEXP_ERROR ) THEN
1319 RAISE Processing_Error;
1320 END IF;
1321 --Bug 6008273
1322
1323 ELSE
1324 UPDATE Mtl_Item_Categories
1325 SET Category_Id = p_category_id
1326 ,last_update_date = SYSDATE
1327 ,last_updated_by = FND_GLOBAL.user_id
1328 ,last_update_login = FND_GLOBAL.login_id
1329 ,request_id = FND_GLOBAL.conc_request_id --4105867
1330 WHERE organization_id = p_organization_id
1331 AND inventory_item_id = p_inventory_item_id
1332 AND category_set_id = p_category_set_id
1333 AND category_id = p_old_category_id;
1334
1335 --Bug 6008273
1336 --Category assignment is not getting updated in eni_oltp_item_star table
1337 --when user update category assignment through
1338 --INV_ITEM_CATEGORY_PUB.Update_Category_Assignment
1339
1340 INV_ENI_ITEMS_STAR_PKG.Sync_Category_Assignments(
1341 p_api_version => p_api_version
1342 ,p_init_msg_list => p_init_msg_list
1343 ,p_inventory_item_id => p_inventory_item_id
1344 ,p_organization_id => p_organization_id
1345 ,p_category_set_id => p_category_set_id
1346 ,p_old_category_id => p_old_category_id
1347 ,p_new_category_id => p_category_id
1348 ,x_return_status => l_return_status
1349 ,x_msg_count => l_msg_count
1350 ,x_msg_data => l_msg_data);
1351
1352 IF ( l_return_status = fnd_api.g_RET_STS_UNEXP_ERROR ) THEN
1353 RAISE Processing_Error;
1354 END IF;
1355 --Bug 6008273
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
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
1373 EXCEPTION
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
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
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'
1408 , p_value2 => Mctx.Procedure_Name
1409 , p_token3 => 'ERROR_TEXT'
1410 , p_value3 => SQLERRM
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
1420 END Update_Category_Assignment;
1421 -- End of code for Bug #3991044
1422
1423 END INV_ITEM_CATEGORY_PVT;