[Home] [Help]
PACKAGE BODY: APPS.INV_ITEM_CATEGORY_PVT
Source
1 PACKAGE BODY INV_ITEM_CATEGORY_PVT AS
2 /* $Header: INVVCATB.pls 120.13 2011/09/23 06:11:58 leizhzha 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 l_is_gpc_catalog VARCHAR2(1); -- Bug 8208540
81
82 CURSOR org_item_exists_csr
83 ( p_inventory_item_id NUMBER
84 , p_organization_id NUMBER
85 ) IS
86 SELECT 'x',request_id, approval_status --2879647
87 FROM mtl_system_items_b
88 WHERE inventory_item_id = p_inventory_item_id
89 AND organization_id = p_organization_id;
90 --AND NVL(approval_status,'A') = 'A'; --Added for 11.5.10 PLM
91
92 CURSOR category_sets_csr (p_category_set_id NUMBER)
93 IS
94 SELECT structure_id, validate_flag, mult_item_cat_assign_flag,
95 control_level
96 ,hierarchy_enabled--Bug: 2996160
97 FROM mtl_category_sets_b
98 WHERE category_set_id = p_category_set_id;
99
100 CURSOR category_exists_csr (p_category_id NUMBER)
101 IS
102 SELECT structure_id
103 FROM mtl_categories_b
104 WHERE category_id = p_category_id
105 AND NVL(DISABLE_DATE,SYSDATE+1) > SYSDATE;--Bug: 2996160
106
107 CURSOR category_set_valid_cats_csr
108 ( p_category_set_id NUMBER
109 , p_category_id NUMBER
110 ) IS
111 SELECT 'x'
112 FROM mtl_category_set_valid_cats
113 WHERE category_set_id = p_category_set_id
114 AND category_id = p_category_id;
115
116 CURSOR item_cat_assign_count_csr
117 ( p_inventory_item_id NUMBER
118 , p_organization_id NUMBER
119 , p_category_set_id NUMBER
120 , p_category_id NUMBER
121 ) IS
122 SELECT COUNT( category_id ), COUNT( DECODE(category_id, p_category_id,1, NULL) )
123 FROM mtl_item_categories
124 WHERE
125 inventory_item_id = p_inventory_item_id
126 AND organization_id = p_organization_id
127 AND category_set_id = p_category_set_id;
128
129 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
130 l_default_cats number;
131 BEGIN
132
133 -- Set savepoint
134 SAVEPOINT Create_Category_Assignment_PVT;
135
136 -- INVPUTLI.info('Add_Message: p_Msg_Name=' || p_Msg_Name);
137
138 --dbms_output.put_line('Enter INV_ITEM_CATEGORY_PVT.Create_Category_Assignment');
139
140 -- Check for call compatibility
141 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
142 l_api_name, G_PKG_NAME)
143 THEN
144 RAISE FND_API.g_EXC_UNEXPECTED_ERROR;
145 END IF;
146
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;
156 Mctx.Procedure_Name := l_api_name;
157
158 -- Initialize API return status to success
159 x_return_status := FND_API.g_RET_STS_SUCCESS;
160
161 -- Check for NULL parameter values
162
163 /* IF ( p_Item_ID = fnd_api.g_MISS_NUM ) OR ( p_Item_ID IS NULL ) OR
164 ( p_Org_ID = fnd_api.g_MISS_NUM ) OR ( p_Org_ID IS NULL )
165 */
166
167 --dbms_output.put_line('Before IS NULL ; x_return_status = ' || x_return_status);
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 );
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
187 --dbms_output.put_line('Before OPEN org_item_exists_csr ; x_return_status = ' || x_return_status);
188
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 );
198 RAISE FND_API.g_EXC_ERROR;
199 END IF;
200
201 /* Bug 8208540 :Added the below query, check if the catalog is a GPC catalog or not*/
202 select Decode(Count(1), 0,'N','Y')
203 INTO l_is_gpc_catalog
204 FROM mtl_default_category_sets
205 WHERE functional_area_id = 21
206 AND category_set_id = p_category_set_id;
207
208 --6355354:Unapproved item can have categories assigned.
209 --Bug: 4046709
210 --If item is an NIR item and it is not approved
211
212 IF l_approval_status IS NOT NULL AND l_approval_status <> 'A'
213 AND l_is_gpc_catalog = 'N' /* Bug 8208540: Added an extra check, If the catalog is GPC then allow category assignment to an unapproved item. */
214 THEN
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;
224 END IF;
225 END IF;
226
227
228 CLOSE org_item_exists_csr;
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
238 OPEN category_sets_csr (p_category_set_id);
239 FETCH category_sets_csr INTO l_category_set_struct_id,
240 l_category_set_restrict_cats,
241 l_mult_item_cat_assign_flag,
242 l_control_level
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 );
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
262 --dbms_output.put_line('Before OPEN category_exists_csr ; x_return_status = ' || x_return_status);
263
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 );
273 RAISE FND_API.g_EXC_ERROR;
274 END IF;
275 CLOSE category_exists_csr;
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
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;
294 END IF;
295
296 -- If Category set control level is master and organization being processed is not master then error
297
298 -- Get master org
299 SELECT MASTER_ORGANIZATION_ID
300 INTO p_master_org_id
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 );
310 RAISE FND_API.g_EXC_ERROR;
311 END IF;
312
313 -- End If Category set control level is master
314
315 -- If a Category Set is defined with the VALIDATE_FLAG = 'Y' then
316 -- a Category must belong to a list of categories in the table MTL_CATEGORY_SET_VALID_CATS.
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);
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 );
335 RAISE FND_API.g_EXC_ERROR;
336 END IF;
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,
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;
353 END IF;
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
363 OPEN item_cat_assign_count_csr (p_inventory_item_id,
364 p_organization_id,
365 p_category_set_id,
366 p_category_id);
367
368 FETCH item_cat_assign_count_csr INTO l_the_item_assign_count, l_the_cat_assign_count;
369
370 -- If a Category Set is defined with the MULT_ITEM_CAT_ASSIGN_FLAG set to 'N'
371 -- then an Item may be assigned to only one Category in the Category Set.
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
381 --Modified for bug 3255128
382 IF (l_control_level = 1) THEN
383 DELETE FROM mtl_item_categories
384 WHERE inventory_item_id = p_inventory_item_id
385 AND category_set_id = p_category_set_id;
386 ELSE
387 DELETE FROM mtl_item_categories
388 WHERE organization_id = p_organization_id
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 );
398 RAISE FND_API.g_EXC_ERROR;
399 END IF;
400 --ELSIF (l_the_cat_assign_count = 0) THEN
401 -- TODO:
402 -- Check if Master Item category assignment permits the Org Item assignment.
403
404 END IF;
405
406 -- If an assignment does not exist, insert into the assignments table
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
416 -- If the category control level is 1 and we are inserting for master record then the assignemnt should also be made for child records.
417 INSERT INTO mtl_item_categories
418 (
419 inventory_item_id
420 , organization_id
421 , category_set_id
422 , category_id
423 , creation_date
424 , created_by
425 , last_update_date
426 , last_updated_by
427 , last_update_login
428 , request_id --4105867
429 )
430 SELECT
431 p_inventory_item_id
432 , p.organization_id
433 , p_category_set_id
434 , p_category_id
435 , SYSDATE
436 , FND_GLOBAL.user_id
437 , SYSDATE
438 , FND_GLOBAL.user_id
439 , FND_GLOBAL.login_id
440 , FND_GLOBAL.conc_request_id
441 FROM mtl_parameters p , mtl_system_items_b i
442 WHERE p.master_organization_id = p_master_org_id
443 AND i.inventory_item_id = p_inventory_item_id
444 AND i.organization_id = p.organization_id
445 AND not exists
446 (SELECT 'x'
447 FROM mtl_item_categories
448 whERE inventory_item_id = p_inventory_item_id
449 AND organization_id = p.organization_id
450 AND category_set_id = p_category_set_id
451 AND category_id = p_category_id);
452 ELSE
453 INSERT INTO mtl_item_categories
454 (
455 inventory_item_id
456 , organization_id
457 , category_set_id
458 , category_id
459 , creation_date
460 , created_by
461 , last_update_date
462 , last_updated_by
463 , last_update_login
464 , request_id --4105867
465 )
466 VALUES
467 (
468 p_inventory_item_id
469 , p_organization_id
470 , p_category_set_id
471 , p_category_id
472 , SYSDATE
473 , FND_GLOBAL.user_id
474 , SYSDATE
475 , FND_GLOBAL.user_id
476 , FND_GLOBAL.login_id
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
486 ( p_Msg_Name => 'INV_CAT_ASSGN_ALREADY_EXISTS'
487 , p_transaction_id => p_transaction_id
488 );
489 END IF;
490
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;
500
501 INV_ITEM_MSG.Count_And_Get
502 ( p_count => x_msg_count
503 , p_data => x_msg_data
504 );
505
506 EXCEPTION
507
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
535 , p_token2 => 'PROCEDURE_NAME'
536 , p_value2 => Mctx.Procedure_Name
537 , p_token3 => 'ERROR_TEXT'
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
547 END Create_Category_Assignment;
548 ------------------------------------------------------------------------------
549
550
551 ------------------------- Delete_Category_Assignment -------------------------
552
553 PROCEDURE Delete_Category_Assignment
554 (
555 p_api_version IN NUMBER
556 , p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.g_FALSE
557 , p_commit IN VARCHAR2 DEFAULT fnd_api.g_FALSE
558 , p_inventory_item_id IN NUMBER
559 , p_organization_id IN NUMBER
560 , p_category_set_id IN NUMBER
561 , p_category_id IN NUMBER
562 , p_transaction_id IN NUMBER
563 , x_return_status OUT NOCOPY VARCHAR2
564 , x_msg_count OUT NOCOPY NUMBER
565 , x_msg_data OUT NOCOPY VARCHAR2
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;
575 l_category_id NUMBER;
576 l_count NUMBER;
577 FF1 VARCHAR2(1);
578 FF2 VARCHAR2(1);
579 FF3 VARCHAR2(1);
580 FF4 VARCHAR2(1);
581 FF5 VARCHAR2(1);
582 FF6 VARCHAR2(1);
583 FF7 VARCHAR2(1);
584 FF8 VARCHAR2(1);
585 FF9 VARCHAR2(1);
586 FF10 VARCHAR2(1);
587 FF11 VARCHAR2(1);
588 FF12 VARCHAR2(1); --Bug:6485437
589 FF21 VARCHAR2(1);
590 l_default_catalog_id MTL_DEFAULT_CATEGORY_SETS.CATEGORY_SET_ID%TYPE;
591 gdsn_outbound_enabled_flag MTL_SYSTEM_ITEMS_B.GDSN_OUTBOUND_ENABLED_FLAG%TYPE;
592
593 inv_item_flagg VARCHAR2(1);
594 purch_item_flagg VARCHAR2(1);
595 int_order_flagg VARCHAR2(1);
596 serv_item_flagg VARCHAR2(1);
597 cost_enab_flagg VARCHAR2(1);
598 engg_item_flagg VARCHAR2(1);
599 cust_order_flagg VARCHAR2(1);
600 mrp_plan_code number;
601 default_cat_id NUMBER;
602 cat_flagg VARCHAR2(1);
603 cnt_cat NUMBER ;
604 eam_item_type NUMBER;
605 contract_item_type VARCHAR2(60);
606
607
608
609 CURSOR category_sets_csr (p_category_set_id NUMBER)
610 IS
611 SELECT control_level,default_category_id--Bug:2527058
612 FROM mtl_category_sets_b
613 WHERE category_set_id = p_category_set_id;
614
615 CURSOR category_exists_csr (p_category_id NUMBER)
616 IS
617 SELECT structure_id
618 FROM mtl_categories_b
619 WHERE category_id = p_category_id;
620
621 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
622
623 --Bug:6485437
624 CURSOR default_catalog_csr(cp_functional_area NUMBER)
625 IS
626 SELECT category_set_id
627 FROM mtl_default_category_sets
628 WHERE functional_area_id = cp_functional_area;
629
630 CURSOR fetch_gdsn_flag(cp_inventory_item_id NUMBER,
631 cp_organization_id NUMBER)
632 IS
633 SELECT gdsn_outbound_enabled_flag
634 FROM mtl_system_items_b
635 WHERE inventory_item_id = cp_inventory_item_id
636 AND organization_id = cp_organization_id;
637
638 BEGIN
639
640 -- Set savepoint
641 SAVEPOINT Delete_Category_Assignment_PVT;
642
643 -- Check for call compatibility
644 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
645 l_api_name, G_PKG_NAME)
646 THEN
647 RAISE FND_API.g_EXC_UNEXPECTED_ERROR;
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;
657 Mctx.Procedure_Name := l_api_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;
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;
676 ELSE
677 /* Bug 4046670 To check if he item belongs to a functional area for which the category set is mandatory before deletion - Anmurali */
678 /*Raise error if the category set is a mandatory category set for the functional area to which the item belongs */
679 INVIDSCS.CHECK_CAT_SET_MANDATORY(p_category_set_id,
680 FF1, FF2, FF3, FF4, FF5, FF6, FF7, FF8, FF9, FF10, FF11);
681
682 --FF1 will be Y if category set is a mandatory set for func_area_id = 1
683 --FF2 will be Y if ......... func_area_id = 2...and so on
684
685 --Bug:6485437
686 OPEN default_catalog_csr(cp_functional_area => 12);
687 FETCH default_catalog_csr INTO l_default_catalog_id;
688 CLOSE default_catalog_csr;
689
690 IF ( l_default_catalog_id = p_category_set_id ) THEN
691 FF12 := 'Y';
692 ELSE
693 FF12 := 'N';
694 END IF;
695
696 l_default_catalog_id := null;
697 OPEN default_catalog_csr(cp_functional_area => 21);
698 FETCH default_catalog_csr INTO l_default_catalog_id;
699 CLOSE default_catalog_csr;
700
701 IF ( l_default_catalog_id = p_category_set_id ) THEN
702 FF21 := 'Y';
703 ELSE
704 FF21 := 'N';
705 END IF;
706
707
708 OPEN fetch_gdsn_flag(cp_inventory_item_id => p_inventory_item_id,
709 cp_organization_id => p_organization_id);
710 FETCH fetch_gdsn_flag INTO gdsn_outbound_enabled_flag;
711 CLOSE fetch_gdsn_flag;
712 --Bug:6485437
713
714 INVIDSCS.GET_ITEM_DEFINING_FLAGS(p_inventory_item_id,
715 p_organization_id,
716 inv_item_flagg,
717 purch_item_flagg,
718 int_order_flagg,
719 serv_item_flagg,
720 cost_enab_flagg,
721 engg_item_flagg,
722 cust_order_flagg,
723 mrp_plan_code,
724 eam_item_type,
725 contract_item_type);
726
727 if (l_category_id = p_category_id) then
728 cat_flagg := 'Y';
729 else
730 cat_flagg := 'N';
731 end if;
732
733 SELECT count(category_id)
734 INTO cnt_cat
735 FROM mtl_item_categories
736 WHERE INVENTORY_ITEM_ID = p_inventory_item_id
737 AND ORGANIZATION_ID = p_organization_id
738 AND CATEGORY_SET_ID = p_category_set_id;
739
740 IF ((FF1 = 'Y' and inv_item_flagg = 'Y')
741 or(FF2 = 'Y' and purch_item_flagg = 'Y')
742 or(FF2 = 'Y' and int_order_flagg = 'Y') --note: there are 2 cases for FF2 = Y
743 or(FF3 = 'Y' and mrp_plan_code <> 6)
744 or(FF4 = 'Y' and serv_item_flagg = 'Y')
745 or(FF5 = 'Y' and cost_enab_flagg = 'Y')
746 or(FF6 = 'Y' and engg_item_flagg = 'Y')
747 or(FF7 = 'Y' and cust_order_flagg = 'Y')
748 or(FF9 = 'Y' and eam_item_type IS NOT NULL) --Bug: 2527058
749 or(FF10 = 'Y' and contract_item_type IS NOT NULL)
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;
759 END IF;
760 END IF;
761 -- End of bug fix for Bug 4046670 - Anmurali
762 END IF;
763 CLOSE category_sets_csr;
764
765 OPEN category_exists_csr (p_category_id);
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 );
775 RAISE FND_API.g_EXC_ERROR;
776 END IF;
777 CLOSE category_exists_csr;
778
779 SELECT MASTER_ORGANIZATION_ID
780 INTO p_master_org_id
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 );
790 RAISE FND_API.g_EXC_ERROR;
791 END IF;
792
793 IF ((l_control_level = 1) and (p_organization_id = p_master_org_id)) THEN
794 --Bug: 3561206 Added an index for performance improvement
795 DELETE /*+ INDEX(MIC MTL_ITEM_CATEGORIES_U1) */
796 FROM mtl_item_categories MIC
797 WHERE category_set_id = p_category_set_id
798 AND category_id = p_category_id
799 AND inventory_item_id = p_inventory_item_id
800 AND organization_id =
801 (SELECT organization_id
802 FROM mtl_parameters p
803 WHERE p.master_organization_id = p_master_org_id
804 AND p.organization_id = mic.organization_id);
805 ELSE
806 -- Delete a row from the table
807 --
808 DELETE FROM mtl_item_categories
809 WHERE organization_id = p_organization_id
810 AND inventory_item_id = p_inventory_item_id
811 AND category_set_id = p_category_set_id
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 );
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
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
839 EXCEPTION
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
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
866 , p_token2 => 'PROCEDURE_NAME'
867 , p_value2 => Mctx.Procedure_Name
868 , p_token3 => 'ERROR_TEXT'
869 , p_value3 => SQLERRM
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
879 END Delete_Category_Assignment;
880 ------------------------------------------------------------------------------
881
882
883 -- Get_Category_Rec_Type
884 ------------------------------------------------------------------------------
885 /*
886 FUNCTION Get_Category_Rec_Type
887 RETURN INV_ITEM_CATEGORY_PVT.CATEGORY_REC_TYPE
888 IS
889 l_category_rec_type INV_ITEM_CATEGORY_PVT.CATEGORY_REC_TYPE;
890 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
891 BEGIN
892 RETURN l_category_rec_type;
893 END;
894 */
895
896 /*Bug: 2996160 Category set dependency validations
897 Validate Flag Hieararchy Flag Validation
898 Y Y Only Valid Categories and which
899 does not have children.
900 Y N All Valid Categories
901 N Y All categories but which does not
902 have children
903 N N All Categories for that structure.
904 */
905
906 FUNCTION Is_Category_Leafnode
907 ( p_category_set_id IN NUMBER
908 , p_category_id IN NUMBER
909 , p_validate_flag IN VARCHAR2
910 , p_hierarchy_enabled IN VARCHAR2
911 ) RETURN BOOLEAN
912 IS
913
914 CURSOR hierarchy_and_validate_csr
915 ( p_category_set_id NUMBER
916 , p_category_id NUMBER
917 ) IS
918 SELECT 'x'
919 FROM mtl_Category_set_valid_cats VC
920 WHERE VC.category_set_id = p_category_set_id
921 AND VC.category_id = p_category_id
922 AND NOT EXISTS
923 (SELECT NULL FROM mtl_Category_set_valid_cats
924 WHERE parent_category_id = VC.category_id
925 AND category_set_id = p_category_set_id);
926
927 CURSOR hierarchy_and_not_validate_csr
928 ( p_category_set_id NUMBER
929 , p_category_id NUMBER
930 ) IS
931 SELECT 'x'
932 FROM mtl_Category_set_valid_cats
933 WHERE category_set_id = p_category_set_id
934 AND parent_category_id = p_category_id ;
935
936 l_exists VARCHAR2(10);
937 BEGIN
938 IF (p_hierarchy_enabled = 'Y') THEN
939 IF ( p_validate_flag = 'Y') THEN
940 OPEN hierarchy_and_validate_csr (p_category_set_id , p_category_id);
941 FETCH hierarchy_and_validate_csr INTO l_exists;
942 IF (hierarchy_and_validate_csr%NOTFOUND) THEN
943 CLOSE hierarchy_and_validate_csr;
944 RETURN false;
945 END IF;
946 CLOSE hierarchy_and_validate_csr;
947 ELSE --validate_flag is 'N'
948 OPEN hierarchy_and_not_validate_csr (p_category_set_id, p_category_id);
949 FETCH hierarchy_and_not_validate_csr INTO l_exists;
950 IF (hierarchy_and_not_validate_csr%FOUND) THEN
951 CLOSE hierarchy_and_not_validate_csr;
952 RETURN false;
953 END IF;
954 CLOSE hierarchy_and_not_validate_csr;
955 END IF;
956 END IF;
957 RETURN true;
958 END Is_Category_Leafnode;
959
960 ----------------------------------------------------------------------------
961 -- Create Valid Category
962 -- API to create a valid Category in Category Sets for ENI Upgrade
963 ----------------------------------------------------------------------------
964 PROCEDURE Create_Valid_Category(
965 p_api_version IN NUMBER,
966 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
967 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
968 p_category_set_id IN NUMBER,
969 p_category_id IN NUMBER,
970 p_parent_category_id IN NUMBER,
971 x_return_status OUT NOCOPY VARCHAR2,
972 x_errorcode OUT NOCOPY NUMBER,
973 x_msg_count OUT NOCOPY NUMBER,
974 x_msg_data OUT NOCOPY VARCHAR2
975 ) IS
976 -- Start OF comments
977 -- API name : Create_Valid_Category
978 -- TYPE : Private and USed by ENI Upgrade program alone
979 -- Pre-reqs : 11.5.10 level
980 -- FUNCTION : Create a category.
981 -- This sets the PUB API package level variable
982 -- and calls the corresponding PUB API procedure.
983 -- This will not do validations for ENABLED_FLAG and DISABLE_DATE
984 -- END OF comments
985 BEGIN
986 INV_ITEM_CATEGORY_PUB.g_eni_upgarde_flag := 'Y';
987 INV_ITEM_CATEGORY_PUB.Create_Valid_Category
988 (
989 p_api_version => p_api_version ,
990 p_init_msg_list => p_init_msg_list,
991 p_commit => p_commit ,
992 p_category_set_id => p_category_set_id ,
993 p_category_id => p_category_id ,
994 p_parent_category_id => p_parent_category_id,
995 x_return_status => x_return_status,
996 x_errorcode => x_errorcode ,
997 x_msg_count => x_msg_count ,
998 x_msg_data => x_msg_data
999 );
1000 INV_ITEM_CATEGORY_PUB.g_eni_upgarde_flag := 'N';
1001 EXCEPTION
1002 WHEN OTHERS THEN
1003 INV_ITEM_CATEGORY_PUB.g_eni_upgarde_flag := 'N';
1004 RAISE;
1005 END Create_Valid_Category;
1006
1007 ----------------------------------------------------------------------------
1008 -- Update Category
1009 -- API to update a valid Category for ENI Upgrade
1010 ----------------------------------------------------------------------------
1011 PROCEDURE Update_Valid_Category(
1012 p_api_version IN NUMBER,
1013 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1014 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1015 p_category_set_id IN NUMBER,
1016 p_category_id IN NUMBER,
1017 p_parent_category_id IN NUMBER,
1018 x_return_status OUT NOCOPY VARCHAR2,
1019 x_errorcode OUT NOCOPY NUMBER,
1020 x_msg_count OUT NOCOPY NUMBER,
1021 x_msg_data OUT NOCOPY VARCHAR2
1022 ) IS
1023 -- Start OF comments
1024 -- API name : Update_Valid_Category
1025 -- TYPE : Private and USed by ENI Upgrade program alone
1026 -- Pre-reqs : 11.5.10 level
1027 -- FUNCTION : Create a category.
1028 -- This sets the PUB API package level variable
1029 -- and calls the corresponding PUB API procedure.
1030 -- This will not do validations for ENABLED_FLAG and DISABLE_DATE
1031 -- END OF comments
1032 BEGIN
1033 INV_ITEM_CATEGORY_PUB.g_eni_upgarde_flag := 'Y';
1034 INV_ITEM_CATEGORY_PUB.Update_Valid_Category
1035 (
1036 p_api_version => p_api_version ,
1037 p_init_msg_list => p_init_msg_list,
1038 p_commit => p_commit ,
1039 p_category_set_id => p_category_set_id ,
1040 p_category_id => p_category_id ,
1041 p_parent_category_id => p_parent_category_id,
1042 x_return_status => x_return_status,
1043 x_errorcode => x_errorcode ,
1044 x_msg_count => x_msg_count ,
1045 x_msg_data => x_msg_data
1046 );
1047 INV_ITEM_CATEGORY_PUB.g_eni_upgarde_flag := 'N';
1048 EXCEPTION
1049 WHEN OTHERS THEN
1050 INV_ITEM_CATEGORY_PUB.g_eni_upgarde_flag := 'N';
1051 RAISE;
1052 END Update_Valid_Category;
1053
1054 -- Procedure Update_Category_Assignment added for Bug #3991044
1055 PROCEDURE Update_Category_Assignment
1056 (
1057 p_api_version IN NUMBER
1058 , p_init_msg_list IN VARCHAR2
1059 , p_commit IN VARCHAR2
1060 , p_inventory_item_id IN NUMBER
1061 , p_organization_id IN NUMBER
1062 , p_category_set_id IN NUMBER
1063 , p_category_id IN NUMBER
1064 , p_old_category_id IN NUMBER
1065 , p_transaction_id IN NUMBER
1066 , x_return_status OUT NOCOPY VARCHAR2
1067 , x_msg_count OUT NOCOPY NUMBER
1068 , x_msg_data OUT NOCOPY VARCHAR2
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;
1078
1079 l_return_status VARCHAR2(1);
1080 l_msg_count NUMBER;
1081 l_msg_data VARCHAR2(2000);
1082 Processing_Error EXCEPTION;
1083
1084 l_old_category_struct_id NUMBER;
1085 l_old_category_id NUMBER;
1086 l_reccount NUMBER :=0;
1087 l_category_set_restrict_cats VARCHAR2(1);
1088 l_exists VARCHAR2(1);
1089 l_category_set_struct_id NUMBER;
1090 l_hierarchy_enabled VARCHAR2(1);
1091 l_mult_item_cat_assign_flag VARCHAR2(1);
1092
1093 CURSOR category_sets_csr (p_category_set_id NUMBER)
1094 IS
1095 SELECT structure_id,
1096 validate_flag,
1097 mult_item_cat_assign_flag,
1098 control_level,
1099 hierarchy_enabled
1100 FROM mtl_category_sets_b
1101 WHERE category_set_id = p_category_set_id;
1102
1103 CURSOR category_set_valid_cats_csr
1104 ( p_category_set_id NUMBER
1105 , p_category_id NUMBER
1106 ) IS
1107 SELECT 'x'
1108 FROM mtl_category_set_valid_cats
1109 WHERE category_set_id = p_category_set_id
1110 AND category_id = p_category_id;
1111
1112
1113 CURSOR category_exists_csr (p_category_id NUMBER)
1114 IS
1115 SELECT structure_id
1116 FROM mtl_categories_b
1117 WHERE category_id = p_category_id
1118 AND NVL(DISABLE_DATE,SYSDATE+1) > SYSDATE; /*Bug no: 5946409 Checking whether the category is disabled */
1119
1120 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1121 BEGIN
1122
1123 -- Set savepoint
1124 SAVEPOINT Update_Category_Assignment_PVT;
1125
1126 -- Check for call compatibility
1127 IF NOT FND_API.Compatible_API_Call (l_api_version, p_api_version,
1128 l_api_name, G_PKG_NAME)
1129 THEN
1130 RAISE FND_API.g_EXC_UNEXPECTED_ERROR;
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;
1140 Mctx.Procedure_Name := l_api_name;
1141
1142 -- Initialize API return status to success
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,
1152 l_mult_item_cat_assign_flag,
1153 l_control_level
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;
1163 END IF;
1164 CLOSE category_sets_csr;
1165
1166 --* Checking whether Category Assignment exists for old category id
1167 SELECT Count(1)
1168 INTO l_reccount
1169 FROM mtl_item_categories
1170 WHERE inventory_item_id = p_inventory_item_id
1171 AND organization_id = p_organization_id
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
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;
1197 END IF;
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'
1207 , p_transaction_id => p_transaction_id
1208 );
1209 RAISE FND_API.g_EXC_ERROR;
1210 END IF;
1211
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
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;
1230 END IF;
1231 CLOSE category_set_valid_cats_csr;
1232 END IF;
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;
1251 END IF;
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
1261 WHERE inventory_item_id = p_inventory_item_id
1262 AND organization_id = p_organization_id
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;
1272 END IF;
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,
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;
1289 END IF;
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) */
1299 Mtl_Item_Categories MIC
1300 SET Category_Id = p_category_id
1301 ,last_update_date = SYSDATE
1302 ,last_updated_by = FND_GLOBAL.user_id
1303 ,last_update_login = FND_GLOBAL.login_id
1304 ,request_id = FND_GLOBAL.conc_request_id -- 4105867
1305 WHERE category_set_id = p_category_set_id
1306 AND category_id = p_old_category_id
1307 AND inventory_item_id = p_inventory_item_id
1308 --Bug 12901485
1309 --For performance tunning, change it to exists sub-query
1310 AND exists (SELECT p.organization_id
1311 --End Bug 12901485
1312 FROM mtl_parameters p
1313 WHERE p.master_organization_id = p_master_org_id
1314 AND p.organization_id = mic.organization_id);
1315 -- for bug 12657660
1316 FOR c_Get_Revised_Orgs IN ( SELECT organization_id
1317 FROM mtl_parameters p
1318 WHERE p.master_organization_id = p_master_org_id )
1319 LOOP
1320
1321 INV_ITEM_EVENTS_PVT.Invoke_ICX_wrapper(
1322 p_entity_type => 'ITEM_CATEGORY',
1323 p_dml_type => 'UPDATE',
1324 p_inventory_item_id => p_inventory_item_id ,
1325 p_organization_id => c_Get_Revised_Orgs.organization_id ,
1326 p_category_set_id => p_category_set_id ,
1327 p_category_id => p_category_id );
1328
1329 END LOOP;
1330
1331 --Bug 6008273
1332 --Category assignment is not getting updated in eni_oltp_item_star table
1333 --when user update category assignment through
1334 --INV_ITEM_CATEGORY_PUB.Update_Category_Assignment
1335
1336 INV_ENI_ITEMS_STAR_PKG.Sync_Category_Assignments(
1337 p_api_version => p_api_version
1338 ,p_init_msg_list => p_init_msg_list
1339 ,p_inventory_item_id => p_inventory_item_id
1340 ,p_organization_id => p_organization_id
1341 ,p_category_set_id => p_category_set_id
1342 ,p_old_category_id => p_old_category_id
1343 ,p_new_category_id => p_category_id
1344 ,x_return_status => l_return_status
1345 ,x_msg_count => l_msg_count
1346 ,x_msg_data => l_msg_data);
1347
1348 IF ( l_return_status = fnd_api.g_RET_STS_UNEXP_ERROR ) THEN
1349 RAISE Processing_Error;
1350 END IF;
1351 --Bug 6008273
1352
1353 ELSE
1354 UPDATE Mtl_Item_Categories
1355 SET Category_Id = p_category_id
1356 ,last_update_date = SYSDATE
1357 ,last_updated_by = FND_GLOBAL.user_id
1358 ,last_update_login = FND_GLOBAL.login_id
1359 ,request_id = FND_GLOBAL.conc_request_id --4105867
1360 WHERE organization_id = p_organization_id
1361 AND inventory_item_id = p_inventory_item_id
1362 AND category_set_id = p_category_set_id
1363 AND category_id = p_old_category_id;
1364
1365 INV_ITEM_EVENTS_PVT.Invoke_ICX_wrapper(
1366 p_entity_type => 'ITEM_CATEGORY',
1367 p_dml_type => 'UPDATE',
1368 p_inventory_item_id => p_inventory_item_id ,
1369 p_organization_id => p_organization_id ,
1370 p_category_set_id => p_category_set_id ,
1371 p_category_id => p_category_id );
1372
1373 --Bug 6008273
1374 --Category assignment is not getting updated in eni_oltp_item_star table
1375 --when user update category assignment through
1376 --INV_ITEM_CATEGORY_PUB.Update_Category_Assignment
1377
1378 INV_ENI_ITEMS_STAR_PKG.Sync_Category_Assignments(
1379 p_api_version => p_api_version
1380 ,p_init_msg_list => p_init_msg_list
1381 ,p_inventory_item_id => p_inventory_item_id
1382 ,p_organization_id => p_organization_id
1383 ,p_category_set_id => p_category_set_id
1384 ,p_old_category_id => p_old_category_id
1385 ,p_new_category_id => p_category_id
1386 ,x_return_status => l_return_status
1387 ,x_msg_count => l_msg_count
1388 ,x_msg_data => l_msg_data);
1389
1390 IF ( l_return_status = fnd_api.g_RET_STS_UNEXP_ERROR ) THEN
1391 RAISE Processing_Error;
1392 END IF;
1393 --Bug 6008273
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
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
1411 EXCEPTION
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
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
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'
1446 , p_value2 => Mctx.Procedure_Name
1447 , p_token3 => 'ERROR_TEXT'
1448 , p_value3 => SQLERRM
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
1458 END Update_Category_Assignment;
1459 -- End of code for Bug #3991044
1460
1461 END INV_ITEM_CATEGORY_PVT;