1 PACKAGE BODY INV_ITEM_CATEGORY_PUB AS
2 /* $Header: INVPCATB.pls 120.12.12020000.2 2012/07/09 08:11:32 asugandh ship $ */
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30):= 'INV_ITEM_CATEGORY_PUB';
6 G_INVENTORY_APP_ID CONSTANT NUMBER := 401;
7 G_INVENTORY_APP_SHORT_NAME CONSTANT VARCHAR2(3) := 'INV';
8 G_CAT_FLEX_CODE CONSTANT VARCHAR2(4) := 'MCAT';
9
10 -- Used by the Preprocess_Category_Rec procedure
11 G_INSERT CONSTANT NUMBER := 1;
12 G_UPDATE CONSTANT NUMBER := 2;
13
14 -- ---------------------------------------------------------------------
15 -- ---------------------------------------------------------------------
16 -- For debugging purposes.
17 PROCEDURE mdebug(msg IN varchar2)
18 IS
19 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
20 BEGIN
21 -- dbms_output.put_line(msg);
22 -- FND_FILE.PUT_LINE(FND_FILE.LOG, msg);
23 null;
24 -- inv_debug(msg);
25 END mdebug;
26
27 ----------------------------------------------------------------------------
28 -- validate_category_set_id
29 -- Bug: 3093555
30 -- Supporting method to validate category_set_id
31 ----------------------------------------------------------------------------
32 FUNCTION validate_category_set_id
33 (p_category_set_id IN NUMBER
34 ,x_hierarchy_enabled OUT NOCOPY VARCHAR2)
35 RETURN BOOLEAN IS
36 -- Start OF comments
37 -- API name : validate_category_set_id
38 -- TYPE : Private
39 -- Called From : Create_Valid_Category
40 -- Pre-reqs : None
41 -- FUNCTION : Validates whether the category_set_id passed
42 -- returns TRUE if the category_set_id is valid
43 -- returns FALSE if the category_set_id is invalid
44 -- returns the hierarchy through the out parameter
45 --
46 -- END OF comments
47 l_hierarchy_enabled mtl_category_sets_b.hierarchy_enabled%TYPE := NULL;
48 BEGIN
49 IF p_category_set_id IS NULL THEN
50 RETURN FALSE;
51 END IF;
52 SELECT hierarchy_enabled INTO l_hierarchy_enabled
53 FROM mtl_category_sets_b
54 WHERE category_set_id = p_category_set_id;
55 IF (SQL%FOUND) THEN
56 x_hierarchy_enabled := l_hierarchy_enabled;
57 END IF;
58 RETURN TRUE;
59 EXCEPTION
60 WHEN OTHERS THEN
61 x_hierarchy_enabled := NULL;
62 RETURN FALSE;
63 END validate_category_set_id;
64
65 ----------------------------------------------------------------------------
66 -- validate_category_id
67 -- Bug: 3093555
68 -- Supporting method to validate category_id
69 ----------------------------------------------------------------------------
70 FUNCTION validate_category_id (p_category_id IN NUMBER
71 ,p_category_set_id IN NUMBER)
72 RETURN BOOLEAN IS
73 -- Start OF comments
74 -- API name : validate_category_id
75 -- TYPE : Private
76 -- Called From : Create_Valid_Category, Update_valid_category
77 -- Pre-reqs : None
78 -- FUNCTION : Validates whether the category_id passed
79 -- returns TRUE if the category_id is valid
80 -- returns FALSE if the category_id is invalid
81 --
82 -- END OF comments
83
84 CURSOR c_validate_category_id (cp_category_id IN NUMBER
85 ,cp_cat_set_id IN NUMBER) IS
86 SELECT cat.category_id
87 FROM mtl_categories_b cat, mtl_category_sets_b cat_set
88 WHERE cat_set.category_set_id = cp_cat_set_id
89 AND cat_set.structure_id = cat.structure_id
90 AND cat.category_id = cp_category_id
91 AND ((cat.enabled_flag = 'Y'
92 -- do not display today's records
93 AND TRUNC(NVL(cat.disable_date,SYSDATE+1)) > TRUNC(SYSDATE)
94 )
95 OR NVL(g_eni_upgarde_flag,'N') = 'Y' --Added for ENI 11.5.10 Upgrade
96 )
97 ;
98
99 l_category_id mtl_categories_b.category_id%TYPE;
100
101 BEGIN
102 OPEN c_validate_category_id (cp_category_id => p_category_id
103 ,cp_cat_set_id => p_category_set_id);
104 FETCH c_validate_category_id INTO l_category_id;
105 IF c_validate_category_id%FOUND THEN
106 CLOSE c_validate_category_id;
107 RETURN TRUE;
108 ELSE
109 CLOSE c_validate_category_id;
110 RETURN FALSE;
111 END IF;
112 EXCEPTION
113 WHEN OTHERS THEN
114 IF c_validate_category_id%ISOPEN THEN
115 CLOSE c_validate_category_id;
116 END IF;
117 RETURN FALSE;
118 END validate_category_id;
119
120 ----------------------------------------------------------------------------
121 -- valid_category_set_record
122 -- Bug: 3093555
123 -- Supporting method to validate record in mtl_category_set_valid_cats
124 ----------------------------------------------------------------------------
125 FUNCTION valid_category_set_record (p_category_set_id IN NUMBER
126 ,p_category_id IN NUMBER)
127 RETURN BOOLEAN IS
128 -- Start OF comments
129 -- API name : valid_category_set_record
130 -- TYPE : Private
131 -- Called From : Delete_Valid_Category, Update_valid_category
132 -- Pre-reqs : None
133 -- FUNCTION : Validates whether the record exists in
134 -- mtl_category_set_valid_cats
135 -- returns TRUE if record exists
136 -- returns FALSE if record does not exist
137 --
138 -- END OF comments
139 l_category_id mtl_categories_b.category_id%TYPE;
140
141 BEGIN
142 SELECT category_id
143 INTO l_category_id
144 FROM mtl_category_set_valid_cats
145 WHERE category_id = p_category_id
146 AND category_set_id = p_category_set_id;
147 IF (SQL%FOUND) THEN
148 RETURN TRUE;
149 ELSE
150 RETURN FALSE;
151 END IF;
152 EXCEPTION
153 WHEN OTHERS THEN
154 RETURN FALSE;
155 END valid_category_set_record;
156
157 ----------------------------------------------------------------------------
158 -- get_category_set_type
159 -- Bug: 5219692
160 -- Supporting method to validate record in mtl_category_set_valid_cats
161 -- Function is similar to valid_category_set_record
162 -- Returns FALSE if the row does not exist
163 ----------------------------------------------------------------------------
164 FUNCTION get_category_set_type (p_category_set_id IN NUMBER
165 ,p_category_id IN NUMBER
166 ,x_hrchy_enabled OUT NOCOPY VARCHAR2)
167 RETURN BOOLEAN IS
168 -- Start OF comments
169 -- FUNCTION : Validates whether the record exists in
170 -- mtl_category_set_valid_cats
171 -- returns TRUE if record exists
172 -- returns FALSE if record does not exist
173 -- Also populates the out variable with
174 -- value of column hierarchy_enabled
175 --
176 -- END OF comments
177 l_category_id mtl_categories_b.category_id%TYPE;
178
179 BEGIN
180 SELECT csv.category_id, cs.hierarchy_enabled
181 INTO l_category_id, x_hrchy_enabled
182 FROM mtl_category_set_valid_cats csv
183 ,mtl_category_sets_b cs
184 WHERE csv.category_id = p_category_id
185 AND csv.category_set_id = p_category_set_id
186 AND cs.category_set_id = csv.category_set_id;
187 IF (SQL%FOUND) THEN
188 RETURN TRUE;
189 ELSE
190 RETURN FALSE;
191 END IF;
192 EXCEPTION
193 WHEN OTHERS THEN
194 RETURN FALSE;
195 END get_category_set_type;
196
197
198 ----------------------------------------------------------------------------
199 -- validate_parent_category_id
200 -- Bug: 3093555
201 -- Supporting method to validate parent_category_id
202 ----------------------------------------------------------------------------
203 FUNCTION validate_category_set_params
204 (p_validation_type IN NUMBER
205 ,p_category_set_id IN NUMBER
206 ,p_category_id IN NUMBER
207 ,p_parent_category_id IN NUMBER
208 ,p_calling_api IN VARCHAR2
209 )
210 RETURN BOOLEAN IS
211 -- Start OF comments
212 -- API name : validate_category_set_params
213 -- TYPE : Private
214 -- Called From : Create_Valid_Category, Update_valid_category
215 -- Pre-reqs : None
216 -- FUNCTION : Validates whether the passed parameters are valid
217 -- returns TRUE if all the parameters are valid
218 -- returns FALSE if any of the parameters are invalid
219 --
220 -- END OF comments
221 l_api_name VARCHAR2(30) := 'Validate Params';
222 l_count NUMBER;
223 l_valid BOOLEAN := TRUE;
224 l_def_category_id mtl_category_sets_b.default_category_id%TYPE;
225 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
226 l_hierarchy_enabled mtl_category_sets_b.hierarchy_enabled%TYPE := NULL;
227 l_loop_may_occur BOOLEAN := FALSE;
228 l_category_id mtl_categories_b.category_id%TYPE;
229
230 CURSOR c_check_loops (cp_parent_category_id IN NUMBER
231 ,cp_category_set_id IN NUMBER) IS
232 SELECT category_id
233 FROM mtl_category_set_valid_cats
234 WHERE category_set_id = cp_category_set_id
235 CONNECT BY prior category_id = parent_category_id
236 START WITH parent_category_id = cp_parent_category_id;
237
238 CURSOR c_get_default_category_id (cp_category_set_id IN NUMBER
239 ,cp_category_id IN NUMBER) IS
240 SELECT default_category_id
241 FROM mtl_category_sets_b cat_sets
242 WHERE cat_sets.category_set_id = cp_category_set_id
243 AND cat_sets.default_category_id = cp_category_id;
244
245 CURSOR c_check_item_assocs (cp_category_set_id IN NUMBER
246 ,cp_category_id IN NUMBER) IS
247 SELECT category_id
248 FROM mtl_item_categories
249 WHERE category_id = cp_category_id
250 AND category_set_id = cp_category_set_id
251 AND rownum = 1;
252
253 BEGIN
254 IF l_debug = 1 THEN
255 mdebug('Validate Params: Tracing...1 ');
256 END IF;
257 --
258 -- all params must be present
259 --
260 IF (p_category_set_id IS NULL OR p_category_id IS NULL) THEN
261 IF l_debug = 1 THEN
262 mdebug('Validate Params: Missing reqd parameter');
263 END IF;
264 l_valid := FALSE;
265 fnd_message.set_name('INV','INV_MISSING_REQUIRED_PARAMETER');
266 fnd_msg_pub.ADD;
267 RAISE fnd_api.g_EXC_ERROR;
268 END IF;
269 IF l_debug = 1 THEN
270 mdebug('Validate Params: Required params passed. ');
271 END IF;
272 --
273 -- the category_id and parent category id must not be same
274 --
275 IF (p_category_id = p_parent_category_id) THEN
276 l_valid := FALSE;
277 IF l_debug = 1 THEN
278 mdebug('Validate Params: Same Parent and Category Set Id');
279 END IF;
280 fnd_message.set_name('INV','INV_SAME_CATEGORY_SETS');
281 fnd_msg_pub.ADD;
282 RAISE fnd_api.g_EXC_ERROR;
283 END IF;
284 IF l_debug = 1 THEN
285 mdebug('Validate Params: Parent and Child category ids are diff');
286 END IF;
287 IF p_validation_type = G_INSERT THEN
288 IF l_debug = 1 THEN
289 mdebug('Validate Params: check for Insert ');
290 END IF;
291 --
292 -- check whether the category_id is valid
293 --
294 IF NOT validate_category_id
295 (p_category_id => p_category_id
296 ,p_category_set_id => p_category_set_id) THEN
297 l_valid := FALSE;
298 IF l_debug = 1 THEN
299 mdebug('Validate Params: Invalid Category Id');
300 END IF;
301 fnd_message.set_name('INV','INV_INVALID_PARAMETER');
302 fnd_message.set_token('PARAM_NAME', 'CATEGORY_ID');
303 fnd_message.set_token('PROGRAM_NAME', G_PKG_NAME||'.'||p_calling_api);
304 fnd_msg_pub.ADD;
305 RAISE fnd_api.g_EXC_ERROR;
306 END IF;
307 IF l_debug = 1 THEN
308 mdebug('Validate Params: Category Id is valid for insert');
309 END IF;
310 ELSIF p_validation_type = G_UPDATE THEN
311 IF l_debug = 1 THEN
312 mdebug('Validate Params: check for Update ');
313 END IF;
314 --
315 -- The record must exist in mtl_category_set_valid_cats
316 --
317 IF NOT valid_category_set_record (p_category_set_id => p_category_set_id
318 ,p_category_id => p_category_id) THEN
319
320 l_valid := FALSE;
321 IF l_debug = 1 THEN
322 mdebug('Validate Params: Record not available for update');
323 END IF;
324 fnd_message.set_name('INV','INV_CATEGORY_UNAVAIL_UPDATE');
325 fnd_msg_pub.ADD;
326 RAISE fnd_api.g_EXC_ERROR;
327 END IF;
328 IF l_debug = 1 THEN
329 mdebug('Validate Params: Record exists in mtl_category_set_valid_cats ');
330 END IF;
331 --
332 -- The new parent category should not create any hierarchical loops
333 -- to be validated for Update only
334 -- the new parent, should not be amongst the
335 -- children of the current category id
336 l_loop_may_occur := FALSE;
337 FOR cr in c_check_loops
338 (cp_parent_category_id => p_category_id
339 ,cp_category_set_id => p_category_set_id) LOOP
340 IF cr.category_id = p_parent_category_id THEN
341 l_loop_may_occur := TRUE;
342 EXIT;
343 END IF;
344 END LOOP;
345 IF l_loop_may_occur THEN
346 l_valid := FALSE;
347 IF l_debug = 1 THEN
348 mdebug('Validate Params: You might create loops!! ');
349 END IF;
350 fnd_message.set_name('INV','INV_CATEGORY_LOOPS_ERR');
351 fnd_msg_pub.ADD;
352 RAISE fnd_api.g_EXC_ERROR;
353 END IF;
354 IF l_debug = 1 THEN
355 mdebug('Validate Params: No loops after updation ');
356 END IF;
357 END IF;
358 --
359 -- check whether the passed category set id is valid
360 --
361 IF validate_category_set_id
362 (p_category_set_id => p_category_set_id
363 ,x_hierarchy_enabled => l_hierarchy_enabled) THEN
364 -- category_set_id is valid, check for hierarchy enabled
365 IF (NVL(l_hierarchy_enabled, 'N') = 'Y') THEN
366 -- category is hierarchy enabled.
367 IF p_parent_category_id IS NULL THEN
368 -- not mandatory to pass.
369 l_valid := TRUE;
370 IF l_debug = 1 THEN
371 mdebug('Validate Params: User wishes to create a leaf node ');
372 END IF;
373 -- fnd_message.set_name('INV','INV_MISSING_PARENT_CAT');
374 -- fnd_msg_pub.ADD;
375 -- RAISE fnd_api.g_EXC_ERROR;
376 ELSE
377 --
378 -- check whether the parent category id is valid
379 --
380 IF NOT validate_category_id
381 (p_category_id => p_parent_category_id
382 ,p_category_set_id => p_category_set_id) THEN
383 l_valid := FALSE;
384 IF l_debug = 1 THEN
385 mdebug('Validate Params: Invalid Parent Category Id');
386 END IF;
387 fnd_message.set_name('INV','INV_INVALID_PARAMETER');
388 fnd_message.set_token('PARAM_NAME', 'PARENT_CATEGORY_ID');
389 fnd_message.set_token('PROGRAM_NAME',
390 G_PKG_NAME||'.'||p_calling_api);
391 fnd_msg_pub.ADD;
392 RAISE fnd_api.g_EXC_ERROR;
393 END IF;
394 IF l_debug = 1 THEN
395 mdebug('Validate Params: Parent category id is valid in mtl_categories_b ');
396 END IF;
397 --
398 -- the parent category cannot be the default category
399 --
400 OPEN c_get_default_category_id (cp_category_set_id => p_category_set_id
401 ,cp_category_id => p_parent_category_id);
402 FETCH c_get_default_category_id INTO l_def_category_id;
403 IF c_get_default_category_id%NOTFOUND THEN
404 l_def_category_id := NULL;
405 END IF;
406 CLOSE c_get_default_category_id;
407 IF l_def_category_id IS NULL THEN
408 IF l_debug = 1 THEN
409 mdebug('Validate Params: Parent category id is NOT default cat ');
410 END IF;
411 -- the parent category id is not the default category
412 -- check for any items associations to the prospective parent category id
413 OPEN c_check_item_assocs (cp_category_set_id => p_category_set_id
414 ,cp_category_id => p_parent_category_id);
415 FETCH c_check_item_assocs INTO l_category_id;
416 IF c_check_item_assocs%NOTFOUND THEN
417 l_category_id := NULL;
418 END IF;
419 CLOSE c_check_item_assocs;
420 IF l_category_id IS NULL THEN
421 -- no items associated
422 -- perfect to be associated as parent category
423 IF l_debug = 1 THEN
424 mdebug('Validate Params: Parent category id is valid ');
425 END IF;
426 l_valid := TRUE;
427 ELSE
428 -- child node (items associated). we cannot make this parent
429 l_valid := FALSE;
430 IF l_debug = 1 THEN
431 mdebug('Validate Params: Items attached, cannot be parent ');
432 END IF;
433 fnd_message.set_name('INV','INV_INVALID_PARAMETER');
434 fnd_message.set_token('PARAM_NAME', 'PARENT_CATEGORY_ID');
435 fnd_message.set_token('PROGRAM_NAME',
436 G_PKG_NAME||'.'||p_calling_api);
437 fnd_msg_pub.ADD;
438 END IF; -- l_count = 0
439 ELSE
440 -- the passed parent is the default category id
441 l_valid := FALSE;
442 IF l_debug = 1 THEN
443 mdebug('Validate Params: Cannot take parent as default category id ');
444 END IF;
445 fnd_message.set_name('INV','INV_DEFAULT_CATEGORY_ADD_ERR');
446 fnd_msg_pub.ADD;
447 END IF; -- l_coount = 0
448 END IF; -- p_parent_category_id IS NULL
449 ELSE
450 -- category hierarchy is disabled
451 IF p_parent_category_id IS NOT NULL THEN
452 -- parent category_id should not be passed
453 l_valid := FALSE;
454 IF l_debug = 1 THEN
455 mdebug('Validate Params: Do not pass Parent Category Id ');
456 END IF;
457 fnd_message.set_name('INV','INV_UNWANTED_PARENT_CAT');
458 fnd_msg_pub.ADD;
459 ELSE
460 -- parent category_id should be NULL
461 IF l_debug = 1 THEN
462 mdebug('Validate Params: Parent category id is null for hierarchy disabled ');
463 END IF;
464 l_valid := TRUE;
465 END IF;
466 END IF; -- hierarchy enabled.
467 ELSE
468 l_valid := FALSE;
469 IF l_debug = 1 THEN
470 mdebug('Validate Params: Invalid Category Set Id');
471 END IF;
472 fnd_message.set_name('INV','INV_INVALID_PARAMETER');
473 fnd_message.set_token('PARAM_NAME', 'CATEGORY_SET_ID');
474 fnd_message.set_token('PROGRAM_NAME', G_PKG_NAME||'.'||p_calling_api);
475 fnd_msg_pub.ADD;
476 END IF;
477 IF l_debug = 1 THEN
478 mdebug('Validate Params: Returning without exceptions');
479 END IF;
480 RETURN l_valid;
481 EXCEPTION
482 WHEN OTHERS THEN
483 IF c_check_loops%ISOPEN THEN
484 CLOSE c_check_loops;
485 END IF;
486 IF c_get_default_category_id%ISOPEN THEN
487 CLOSE c_get_default_category_id;
488 END IF;
489 IF c_check_item_assocs%ISOPEN THEN
490 CLOSE c_check_item_assocs;
491 END IF;
492 IF l_debug = 1 THEN
493 mdebug('Validate Params: Exception Raised');
494 END IF;
495 RETURN FALSE;
496 END validate_category_set_params;
497
498 -- Environment setting.
499 -- Call this procedure internally to Test the proper updation of
500 -- Created_By, Last_Updated_By, Last_Update_Login etc., columns
501 /*
502 PROCEDURE Apps_Initialize
503 IS
504 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
505 BEGIN
506 -- To set the APPS Environment context through PL/SQL.
507 fnd_global.apps_initialize(1068, 20634, 401);
508 IF (l_debug = 1) THEN
509 mdebug('User ID :'||to_char(FND_GLOBAL.user_id));
510 mdebug('User NAME :'||FND_GLOBAL.user_name);
511 mdebug('Login ID :'||to_char(FND_GLOBAL.login_id));
512 mdebug('Prog Appl ID :'||to_char(FND_GLOBAL.prog_appl_id));
513 mdebug('Application Name :'||FND_GLOBAL.application_name);
514 mdebug('Language :'||FND_GLOBAL.current_language);
515 mdebug('And many more...');
516 END IF;
517 END Apps_Initialize;
518 */
519
520
521 FUNCTION To_Boolchar
522 (
523 p_bool IN BOOLEAN
524 )
525 RETURN VARCHAR2
526 IS
527 l_api_name CONSTANT VARCHAR2(30) := 'To_Boolchar' ;
528 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
529 BEGIN
530
531 IF ( p_bool = TRUE ) THEN
532 RETURN fnd_api.g_TRUE ;
533 ELSIF ( p_bool = FALSE ) THEN
534 RETURN fnd_api.g_FALSE ;
535 ELSE
536 NULL;
537 END IF;
538
539 END To_Boolchar;
540
541
542 -- To check for invalid values in the record according to the operation is
543 -- INSERT or UPDATE, and report Errors appropriately.
544 -- Preprocess_Category_Rec
545 ----------------------------------------------------------------------------
546 PROCEDURE Preprocess_Category_Rec
547 (
548 p_operation IN NUMBER,
549 p_category_rec IN INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE,
550 x_category_rec OUT NOCOPY INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE
551
552 ) IS
553
554 l_category_rec INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE;
555 l_flexstr_exists VARCHAR2(1);
556
557 CURSOR get_structure_id(p_structure_code VARCHAR) IS
558 SELECT id_flex_num
559 FROM fnd_id_flex_structures
560 WHERE application_id = G_INVENTORY_APP_ID
561 AND id_flex_code = G_CAT_FLEX_CODE
562 AND id_flex_structure_code = p_structure_code
563 AND enabled_flag = 'Y';
564
565 CURSOR get_category_structure_id(p_category_id NUMBER) IS
566 SELECT structure_id
567 FROM mtl_categories_b
568 WHERE category_id = p_category_id;
569
570 CURSOR validate_structure_id(p_structure_id VARCHAR) IS
571 SELECT 'x'
572 FROM fnd_id_flex_structures
573 WHERE application_id = G_INVENTORY_APP_ID
574 AND id_flex_code = G_CAT_FLEX_CODE
575 AND id_flex_num = p_structure_id
576 AND enabled_flag = 'Y';
577
578 CURSOR category_rec_cursor(p_category_id NUMBER) IS
579 SELECT
580 --category_id,
581 --structure_id,
582 description,
583 attribute_category,
584 summary_flag,
585 enabled_flag,
586 start_date_active,
587 end_date_active,
588 disable_date,
589 web_status,--Bug: 2430879
590 supplier_enabled_flag,--Bug: 2645153
591 segment1,
592 segment2,
593 segment3,
594 segment4,
595 segment5,
596 segment6,
597 segment7,
598 segment8,
599 segment9,
600 segment10,
601 segment11,
602 segment12,
603 segment13,
604 segment14,
605 segment15,
606 segment16,
607 segment17,
608 segment18,
609 segment19,
610 segment20,
611 attribute1,
612 attribute2,
613 attribute3,
614 attribute4,
615 attribute5,
616 attribute6,
617 attribute7,
618 attribute8,
619 attribute9,
620 attribute10,
621 attribute11,
622 attribute12,
623 attribute13,
624 attribute14,
625 attribute15
626 --last_update_date,
627 --last_updated_by,
628 --creation_date,
629 --created_by,
630 --last_update_login
631 FROM mtl_categories_vl
632 WHERE category_id = p_category_id;
633
634 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
635 l_product_str_id NUMBER; -- Bug 5474569
636 BEGIN
637 x_category_rec.category_id := p_category_rec.category_id;
638 x_category_rec.structure_id := p_category_rec.structure_id;
639 x_category_rec.structure_code := p_category_rec.structure_code;
640 x_category_rec.attribute_category:= p_category_rec.attribute_category;
641 x_category_rec.description := p_category_rec.description;
642 x_category_rec.summary_flag := p_category_rec.summary_flag;
643 x_category_rec.enabled_flag := p_category_rec.enabled_flag;
644 x_category_rec.start_date_active := p_category_rec.start_date_active;
645 x_category_rec.end_date_active := p_category_rec.end_date_active;
646 x_category_rec.disable_date := p_category_rec.disable_date;
647 x_category_rec.web_status := p_category_rec.web_status; --Bug: 2430879
648 x_category_rec.supplier_enabled_flag := p_category_rec.supplier_enabled_flag; --Bug: 2645153
649
650 x_category_rec.segment1 := p_category_rec.segment1 ;
651 x_category_rec.segment2 := p_category_rec.segment2 ;
652 x_category_rec.segment3 := p_category_rec.segment3 ;
653 x_category_rec.segment4 := p_category_rec.segment4 ;
654 x_category_rec.segment5 := p_category_rec.segment5 ;
655 x_category_rec.segment6 := p_category_rec.segment6 ;
656 x_category_rec.segment7 := p_category_rec.segment7 ;
657 x_category_rec.segment8 := p_category_rec.segment8 ;
658 x_category_rec.segment9 := p_category_rec.segment9 ;
659 x_category_rec.segment10 := p_category_rec.segment10;
660 x_category_rec.segment11 := p_category_rec.segment11;
661 x_category_rec.segment12 := p_category_rec.segment12;
662 x_category_rec.segment13 := p_category_rec.segment13;
663 x_category_rec.segment14 := p_category_rec.segment14;
664 x_category_rec.segment15 := p_category_rec.segment15;
665 x_category_rec.segment16 := p_category_rec.segment16;
666 x_category_rec.segment17 := p_category_rec.segment17;
667 x_category_rec.segment18 := p_category_rec.segment18;
668 x_category_rec.segment19 := p_category_rec.segment19;
669 x_category_rec.segment20 := p_category_rec.segment20;
670
671 x_category_rec.attribute1 := p_category_rec.attribute1 ;
672 x_category_rec.attribute2 := p_category_rec.attribute2 ;
673 x_category_rec.attribute3 := p_category_rec.attribute3 ;
674 x_category_rec.attribute4 := p_category_rec.attribute4 ;
675 x_category_rec.attribute5 := p_category_rec.attribute5 ;
676 x_category_rec.attribute6 := p_category_rec.attribute6 ;
677 x_category_rec.attribute7 := p_category_rec.attribute7 ;
678 x_category_rec.attribute8 := p_category_rec.attribute8 ;
679 x_category_rec.attribute9 := p_category_rec.attribute9 ;
680 x_category_rec.attribute10 := p_category_rec.attribute10;
681 x_category_rec.attribute11 := p_category_rec.attribute11;
682 x_category_rec.attribute12 := p_category_rec.attribute12;
683 x_category_rec.attribute13 := p_category_rec.attribute13;
684 x_category_rec.attribute14 := p_category_rec.attribute14;
685 x_category_rec.attribute15 := p_category_rec.attribute15;
686
687 /* Bug 5474569 Start Get structure_id of PRODUCT_CATEGORIES*/
688 OPEN get_structure_id('PRODUCT_CATEGORIES');
689 FETCH get_structure_id INTO l_product_str_id;
690
691 IF (get_structure_id%NOTFOUND) THEN
692 fnd_message.set_name('INV','FLEX-NO MAIN KEY FLEX DEF');
693 fnd_message.set_token('ROUTINE', 'Preprocess_Category_Rec');
694 fnd_msg_pub.ADD;
695 IF (l_debug = 1) THEN
696 mdebug('ERR: No Product Categories structure');
697 END IF;
698 CLOSE get_structure_id;
699 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
700 END IF;
701 CLOSE get_structure_id;
702 /* Bug 5474569 End */
703
704 IF (p_operation = G_INSERT) THEN
705
706 /* The following code is not needed.
707 IF (x_category_rec.category_id = g_MISS_NUM OR
708 x_category_rec.category_id IS NOT NULL) THEN
709 x_category_rec.category_id := NULL;
710 IF (l_debug = 1) THEN
711 mdebug('Ignoring the Category Id value for Insert');
712 END IF;
713 END IF;
714 */
715 IF x_category_rec.description = g_MISS_CHAR THEN
716 x_category_rec.description := NULL;
717 END IF;
718
719 IF (x_category_rec.structure_id = g_MISS_NUM OR
720 x_category_rec.structure_id IS NULL) AND
721 ( x_category_rec.structure_code = g_MISS_CHAR OR
722 x_category_rec.structure_code IS NULL) THEN
723 fnd_message.set_name('INV','INV_FLEX_STRUCTURE_REQ');
724 fnd_msg_pub.ADD;
725 IF (l_debug = 1) THEN
726 mdebug('Flex Structure Information needed');
727 END IF;
728 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
729 END IF;
730
731 IF (x_category_rec.structure_id = g_MISS_NUM OR
732 x_category_rec.structure_id IS NULL) AND
733 (x_category_rec.structure_code <> g_MISS_CHAR AND
734 x_category_rec.structure_code IS NOT NULL) THEN
735 OPEN get_structure_id(x_category_rec.structure_code);
736 FETCH get_structure_id INTO x_category_rec.structure_id;
737 IF (l_debug = 1) THEN
738 mdebug('Flex Structure: '||To_char(x_category_rec.structure_id));
739 END IF;
740
741 IF (get_structure_id%NOTFOUND) THEN
742 fnd_message.set_name('INV','FLEX-NO MAIN KEY FLEX DEF');
743 fnd_message.set_token('ROUTINE', 'Preprocess_Category_Rec');
744 fnd_msg_pub.ADD;
745 IF (l_debug = 1) THEN
746 mdebug('ERR: Invalid Flex Structure information provided');
747 END IF;
748 CLOSE get_structure_id;
749 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
750 END IF;
751 CLOSE get_structure_id;
752 END IF;
753
754 IF (x_category_rec.structure_id <> g_MISS_NUM AND
755 x_category_rec.structure_id IS NOT NULL) THEN
756 OPEN validate_structure_id(x_category_rec.structure_id);
757 FETCH validate_structure_id INTO l_flexstr_exists;
758 IF (validate_structure_id%NOTFOUND) THEN
759 fnd_message.set_name('INV','FLEX-NO MAIN KEY FLEX DEF');
760 fnd_message.set_token('ROUTINE', 'Preprocess_Category_Rec');
761 fnd_msg_pub.ADD;
762 IF (l_debug = 1) THEN
763 mdebug('Invalid Flex Structure information provided');
764 END IF;
765 CLOSE validate_structure_id;
766 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
767 END IF;
768 CLOSE validate_structure_id;
769 END IF;
770 --Bug: 2645153
771 Validate_iProcurements_flags(x_category_rec);
772
773 /* Bug 5474569 Start */
774 if (l_product_str_id <> x_category_rec.structure_id and
775 nvl(x_category_rec.summary_flag, 'N' ) = 'Y' ) then
776 fnd_message.set_name('INV','INV_CAT_SUM_FLAG_ERR');
777 fnd_msg_pub.ADD;
778 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
779 end if;
780 /* Bug 5474569 End */
781
782 IF x_category_rec.attribute_category = g_MISS_CHAR THEN
783 x_category_rec.attribute_category := NULL;
784 END IF;
785
786 IF x_category_rec.summary_flag = g_MISS_CHAR THEN
787 x_category_rec.summary_flag := g_NO;
788 END IF;
789
790 IF x_category_rec.enabled_flag = g_MISS_CHAR THEN
791 x_category_rec.enabled_flag := g_YES;
792 END IF;
793
794 IF x_category_rec.start_date_active = g_MISS_DATE THEN
795 x_category_rec.start_date_active := NULL;
796 END IF;
797
798 IF x_category_rec.end_date_active = g_MISS_DATE THEN
799 x_category_rec.end_date_active := NULL;
800 END IF;
801
802 IF x_category_rec.disable_date = g_MISS_DATE THEN
803 x_category_rec.disable_date := NULL;
804 END IF;
805 --Bug: 2430879 added if condition
806 /**
807 IF x_category_rec.web_status = g_MISS_CHAR THEN
808 x_category_rec.web_status := g_NO;
809 END IF;
810 **/
811
812 IF x_category_rec.segment1 = g_MISS_CHAR THEN
813 x_category_rec.segment1 := NULL;
814 END IF;
815
816 IF x_category_rec.segment2 = g_MISS_CHAR THEN
817 x_category_rec.segment2 := NULL;
818 END IF;
819
820 IF x_category_rec.segment3 = g_MISS_CHAR THEN
821 x_category_rec.segment3 := NULL;
822 END IF;
823
824 IF x_category_rec.segment4 = g_MISS_CHAR THEN
825 x_category_rec.segment4 := NULL;
826 END IF;
827
828 IF x_category_rec.segment5 = g_MISS_CHAR THEN
829 x_category_rec.segment5 := NULL;
830 END IF;
831
832 IF x_category_rec.segment6 = g_MISS_CHAR THEN
833 x_category_rec.segment6 := NULL;
834 END IF;
835
836 IF x_category_rec.segment7 = g_MISS_CHAR THEN
837 x_category_rec.segment7 := NULL;
838 END IF;
839
840 IF x_category_rec.segment8 = g_MISS_CHAR THEN
841 x_category_rec.segment8 := NULL;
842 END IF;
843
844 IF x_category_rec.segment9 = g_MISS_CHAR THEN
845 x_category_rec.segment9 := NULL;
846 END IF;
847
848 IF x_category_rec.segment10 = g_MISS_CHAR THEN
849 x_category_rec.segment10 := NULL;
850 END IF;
851
852 IF x_category_rec.segment11 = g_MISS_CHAR THEN
853 x_category_rec.segment11 := NULL;
854 END IF;
855
856 IF x_category_rec.segment12 = g_MISS_CHAR THEN
857 x_category_rec.segment12 := NULL;
858 END IF;
859
860 IF x_category_rec.segment13 = g_MISS_CHAR THEN
861 x_category_rec.segment13 := NULL;
862 END IF;
863
864 IF x_category_rec.segment14 = g_MISS_CHAR THEN
865 x_category_rec.segment14 := NULL;
866 END IF;
867
868 IF x_category_rec.segment15 = g_MISS_CHAR THEN
869 x_category_rec.segment15 := NULL;
870 END IF;
871
872 IF x_category_rec.segment16 = g_MISS_CHAR THEN
873 x_category_rec.segment16 := NULL;
874 END IF;
875
876 IF x_category_rec.segment17 = g_MISS_CHAR THEN
877 x_category_rec.segment17 := NULL;
878 END IF;
879
880 IF x_category_rec.segment18 = g_MISS_CHAR THEN
881 x_category_rec.segment18 := NULL;
882 END IF;
883
884 IF x_category_rec.segment19 = g_MISS_CHAR THEN
885 x_category_rec.segment19 := NULL;
886 END IF;
887
888 IF x_category_rec.segment20 = g_MISS_CHAR THEN
889 x_category_rec.segment20 := NULL;
890 END IF;
891
892 IF x_category_rec.attribute1 = g_MISS_CHAR THEN
893 x_category_rec.attribute1 := NULL;
894 END IF;
895
896 IF x_category_rec.attribute2 = g_MISS_CHAR THEN
897 x_category_rec.attribute2 := NULL;
898 END IF;
899
900 IF x_category_rec.attribute3 = g_MISS_CHAR THEN
901 x_category_rec.attribute3 := NULL;
902 END IF;
903
904 IF x_category_rec.attribute4 = g_MISS_CHAR THEN
905 x_category_rec.attribute4 := NULL;
906 END IF;
907
908 IF x_category_rec.attribute5 = g_MISS_CHAR THEN
909 x_category_rec.attribute5 := NULL;
910 END IF;
911
912 IF x_category_rec.attribute6 = g_MISS_CHAR THEN
913 x_category_rec.attribute6 := NULL;
914 END IF;
915
916 IF x_category_rec.attribute7 = g_MISS_CHAR THEN
917 x_category_rec.attribute7 := NULL;
918 END IF;
919
920 IF x_category_rec.attribute8 = g_MISS_CHAR THEN
921 x_category_rec.attribute8 := NULL;
922 END IF;
923
924 IF x_category_rec.attribute9 = g_MISS_CHAR THEN
925 x_category_rec.attribute9 := NULL;
926 END IF;
927
928 IF x_category_rec.attribute10 = g_MISS_CHAR THEN
929 x_category_rec.attribute10 := NULL;
930 END IF;
931
932 IF x_category_rec.attribute11 = g_MISS_CHAR THEN
933 x_category_rec.attribute11 := NULL;
934 END IF;
935
936 IF x_category_rec.attribute12 = g_MISS_CHAR THEN
937 x_category_rec.attribute12 := NULL;
938 END IF;
939
940 IF x_category_rec.attribute13 = g_MISS_CHAR THEN
941 x_category_rec.attribute13 := NULL;
942 END IF;
943
944 IF x_category_rec.attribute14 = g_MISS_CHAR THEN
945 x_category_rec.attribute14 := NULL;
946 END IF;
947
948 IF x_category_rec.attribute15 = g_MISS_CHAR THEN
949 x_category_rec.attribute15 := NULL;
950 END IF;
951
952 END IF; --IF (p_operation = G_INSERT) THEN
953
954 -- Update operation.
955 IF (p_operation = G_UPDATE) THEN
956
957 IF (x_category_rec.category_id = g_MISS_NUM OR
958 x_category_rec.category_id IS NULL) THEN
959 fnd_message.set_name('INV','INV_NO_CATEGORY');
960 fnd_msg_pub.ADD;
961 IF (l_debug = 1) THEN
962 mdebug('Category Id needed for Update');
963 END IF;
964 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
965 END IF;
966
967 IF (x_category_rec.structure_id = g_MISS_NUM OR
968 x_category_rec.structure_id IS NULL) AND
969 ( x_category_rec.structure_code = g_MISS_CHAR OR
970 x_category_rec.structure_code IS NULL) THEN
971 OPEN get_category_structure_id(x_category_rec.category_id);
972 FETCH get_category_structure_id INTO x_category_rec.structure_id;
973 IF (get_category_structure_id%NOTFOUND) THEN
974 fnd_message.set_name('INV','FLEX-NO MAIN KEY FLEX DEF');
975 fnd_message.set_token('ROUTINE', 'Preprocess_Category_Rec');
976 fnd_msg_pub.ADD;
977 IF (l_debug = 1) THEN
978 mdebug('Invalid Flex Structure information provided');
979 END IF;
980 CLOSE get_category_structure_id;
981 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
982 END IF;
983 CLOSE get_category_structure_id;
984 /*
985 fnd_message.set_name('INV','INV_FLEX_STRUCTURE_REQ');
986 fnd_msg_pub.ADD;
987 IF (l_debug = 1) THEN
988 mdebug('Flex Structure Information needed');
989 END IF;
990 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
991 */
992 END IF;
993
994 IF (x_category_rec.structure_id = g_MISS_NUM OR
995 x_category_rec.structure_id IS NULL) AND
996 (x_category_rec.structure_code <> g_MISS_CHAR OR
997 x_category_rec.structure_code IS NOT NULL) THEN
998 OPEN get_structure_id(x_category_rec.structure_code);
999 FETCH get_structure_id INTO x_category_rec.structure_id;
1000 IF (get_structure_id%NOTFOUND) THEN
1001 fnd_message.set_name('INV','FLEX-NO MAIN KEY FLEX DEF');
1002 fnd_message.set_token('ROUTINE', 'Preprocess_Category_Rec');
1003 fnd_msg_pub.ADD;
1004 IF (l_debug = 1) THEN
1005 mdebug('Invalid Flex Structure information provided');
1006 END IF;
1007 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
1008 END IF;
1009 CLOSE get_structure_id;
1010 END IF;
1011
1012 --Bug: 2645153
1013 Validate_iProcurements_flags(x_category_rec);
1014
1015 /* Bug 5474569 Start */
1016 if (l_product_str_id <> x_category_rec.structure_id and
1017 nvl(x_category_rec.summary_flag, 'N' ) = 'Y' ) then
1018 fnd_message.set_name('INV','INV_CAT_SUM_FLAG_ERR');
1019 fnd_msg_pub.ADD;
1020 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
1021 end if;
1022 /* Bug 5474569 End */
1023
1024 /* Before further processing we get the info. from Database */
1025 OPEN category_rec_cursor(x_category_rec.category_id);
1026 FETCH category_rec_cursor INTO
1027 l_category_rec.description,
1028 l_category_rec.attribute_category,
1029 l_category_rec.summary_flag,
1030 l_category_rec.enabled_flag,
1031 l_category_rec.start_date_active,
1032 l_category_rec.end_date_active,
1033 l_category_rec.disable_date,
1034 l_category_rec.web_status,--Bug: 2430879 5134913
1035 l_category_rec.supplier_enabled_flag,--Bug: 2645153 5134913
1036 l_category_rec.segment1,
1037 l_category_rec.segment2,
1038 l_category_rec.segment3,
1039 l_category_rec.segment4,
1040 l_category_rec.segment5,
1041 l_category_rec.segment6,
1042 l_category_rec.segment7,
1043 l_category_rec.segment8,
1044 l_category_rec.segment9,
1045 l_category_rec.segment10,
1046 l_category_rec.segment11,
1047 l_category_rec.segment12,
1048 l_category_rec.segment13,
1049 l_category_rec.segment14,
1050 l_category_rec.segment15,
1051 l_category_rec.segment16,
1052 l_category_rec.segment17,
1053 l_category_rec.segment18,
1054 l_category_rec.segment19,
1055 l_category_rec.segment20,
1056 l_category_rec.attribute1,
1057 l_category_rec.attribute2,
1058 l_category_rec.attribute3,
1059 l_category_rec.attribute4,
1060 l_category_rec.attribute5,
1061 l_category_rec.attribute6,
1062 l_category_rec.attribute7,
1063 l_category_rec.attribute8,
1064 l_category_rec.attribute9,
1065 l_category_rec.attribute10,
1066 l_category_rec.attribute11,
1067 l_category_rec.attribute12,
1068 l_category_rec.attribute13,
1069 l_category_rec.attribute14,
1070 l_category_rec.attribute15;
1071
1072 IF (category_rec_cursor%NOTFOUND) THEN
1073 fnd_message.set_name('INV','INV_VALID_CAT');
1074 fnd_msg_pub.ADD;
1075 IF (l_debug = 1) THEN
1076 mdebug('Invalid Category Id provided');
1077 END IF;
1078 CLOSE category_rec_cursor;
1079 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
1080 END IF;
1081 CLOSE category_rec_cursor;
1082
1083 IF x_category_rec.description = g_MISS_CHAR THEN
1084 x_category_rec.description := l_category_rec.description;
1085 END IF;
1086
1087 IF x_category_rec.attribute_category = g_MISS_CHAR THEN
1088 x_category_rec.attribute_category := l_category_rec.attribute_category;
1089 END IF;
1090
1091 IF x_category_rec.summary_flag = g_MISS_CHAR THEN
1092 x_category_rec.summary_flag := l_category_rec.summary_flag;
1093 END IF;
1094
1095 IF x_category_rec.enabled_flag = g_MISS_CHAR THEN
1096 x_category_rec.enabled_flag := l_category_rec.enabled_flag;
1097 END IF;
1098
1099 IF x_category_rec.start_date_active = g_MISS_DATE THEN
1100 x_category_rec.start_date_active := l_category_rec.start_date_active;
1101 END IF;
1102
1103 IF x_category_rec.end_date_active = g_MISS_DATE THEN
1104 x_category_rec.end_date_active := l_category_rec.end_date_active;
1105 END IF;
1106
1107 IF x_category_rec.disable_date = g_MISS_DATE THEN
1108 x_category_rec.disable_date := l_category_rec.disable_date;
1109 END IF;
1110 /*Bug: 4494727 Commenting out the following IF condition
1111 --Bug: 2430879 Added If condition
1112 IF x_category_rec.web_status = g_MISS_CHAR THEN
1113 x_category_rec.web_status := l_category_rec.web_status;
1114 END IF;
1115 --Bug: 2645153 Added If condition
1116 */
1117 IF x_category_rec.supplier_enabled_flag = g_MISS_CHAR THEN
1118 x_category_rec.supplier_enabled_flag := l_category_rec.supplier_enabled_flag;
1119 END IF;
1120
1121 IF x_category_rec.segment1 = g_MISS_CHAR THEN
1122 x_category_rec.segment1 := l_category_rec.segment1;
1123 END IF;
1124
1125 IF x_category_rec.segment2 = g_MISS_CHAR THEN
1126 x_category_rec.segment2 := l_category_rec.segment2;
1127 END IF;
1128
1129 IF x_category_rec.segment3 = g_MISS_CHAR THEN
1130 x_category_rec.segment3 := l_category_rec.segment3;
1131 END IF;
1132
1133 IF x_category_rec.segment4 = g_MISS_CHAR THEN
1134 x_category_rec.segment4 := l_category_rec.segment4;
1135 END IF;
1136
1137 IF x_category_rec.segment5 = g_MISS_CHAR THEN
1138 x_category_rec.segment5 := l_category_rec.segment5;
1139 END IF;
1140
1141 IF x_category_rec.segment6 = g_MISS_CHAR THEN
1142 x_category_rec.segment6 := l_category_rec.segment6;
1143 END IF;
1144
1145 IF x_category_rec.segment7 = g_MISS_CHAR THEN
1146 x_category_rec.segment7 := l_category_rec.segment7;
1147 END IF;
1148
1149 IF x_category_rec.segment8 = g_MISS_CHAR THEN
1150 x_category_rec.segment8 := l_category_rec.segment8;
1151 END IF;
1152
1153 IF x_category_rec.segment9 = g_MISS_CHAR THEN
1154 x_category_rec.segment9 := l_category_rec.segment9;
1155 END IF;
1156
1157 IF x_category_rec.segment10 = g_MISS_CHAR THEN
1158 x_category_rec.segment10 := l_category_rec.segment10;
1159 END IF;
1160
1161 IF x_category_rec.segment11 = g_MISS_CHAR THEN
1162 x_category_rec.segment11 := l_category_rec.segment11;
1163 END IF;
1164
1165 IF x_category_rec.segment12 = g_MISS_CHAR THEN
1166 x_category_rec.segment12 := l_category_rec.segment12;
1167 END IF;
1168
1169 IF x_category_rec.segment13 = g_MISS_CHAR THEN
1170 x_category_rec.segment13 := l_category_rec.segment13;
1171 END IF;
1172
1173 IF x_category_rec.segment14 = g_MISS_CHAR THEN
1174 x_category_rec.segment14 := l_category_rec.segment14;
1175 END IF;
1176
1177 IF x_category_rec.segment15 = g_MISS_CHAR THEN
1178 x_category_rec.segment15 := l_category_rec.segment15;
1179 END IF;
1180
1181 IF x_category_rec.segment16 = g_MISS_CHAR THEN
1182 x_category_rec.segment16 := l_category_rec.segment16;
1183 END IF;
1184
1185 IF x_category_rec.segment17 = g_MISS_CHAR THEN
1186 x_category_rec.segment17 := l_category_rec.segment17;
1187 END IF;
1188
1189 IF x_category_rec.segment18 = g_MISS_CHAR THEN
1190 x_category_rec.segment18 := l_category_rec.segment18;
1191 END IF;
1192
1193 IF x_category_rec.segment19 = g_MISS_CHAR THEN
1194 x_category_rec.segment19 := l_category_rec.segment19;
1195 END IF;
1196
1197 IF x_category_rec.segment20 = g_MISS_CHAR THEN
1198 x_category_rec.segment20 := l_category_rec.segment20;
1199 END IF;
1200
1201 IF x_category_rec.attribute1 = g_MISS_CHAR THEN
1202 x_category_rec.attribute1 := l_category_rec.attribute1;
1203 END IF;
1204
1205 IF x_category_rec.attribute2 = g_MISS_CHAR THEN
1206 x_category_rec.attribute2 := l_category_rec.attribute2;
1207 END IF;
1208
1209 IF x_category_rec.attribute3 = g_MISS_CHAR THEN
1210 x_category_rec.attribute3 := l_category_rec.attribute3;
1211 END IF;
1212
1213 IF x_category_rec.attribute4 = g_MISS_CHAR THEN
1214 x_category_rec.attribute4 := l_category_rec.attribute4;
1215 END IF;
1216
1217 IF x_category_rec.attribute5 = g_MISS_CHAR THEN
1218 x_category_rec.attribute5 := l_category_rec.attribute5;
1219 END IF;
1220
1221 IF x_category_rec.attribute6 = g_MISS_CHAR THEN
1222 x_category_rec.attribute6 := l_category_rec.attribute6;
1223 END IF;
1224
1225 IF x_category_rec.attribute7 = g_MISS_CHAR THEN
1226 x_category_rec.attribute7 := l_category_rec.attribute7;
1227 END IF;
1228
1229 IF x_category_rec.attribute8 = g_MISS_CHAR THEN
1230 x_category_rec.attribute8 := l_category_rec.attribute8;
1231 END IF;
1232
1233 IF x_category_rec.attribute9 = g_MISS_CHAR THEN
1234 x_category_rec.attribute9 := l_category_rec.attribute9;
1235 END IF;
1236
1237 IF x_category_rec.attribute10 = g_MISS_CHAR THEN
1238 x_category_rec.attribute10 := l_category_rec.attribute10;
1239 END IF;
1240
1241 IF x_category_rec.attribute11 = g_MISS_CHAR THEN
1242 x_category_rec.attribute11 := l_category_rec.attribute11;
1243 END IF;
1244
1245 IF x_category_rec.attribute12 = g_MISS_CHAR THEN
1246 x_category_rec.attribute12 := l_category_rec.attribute12;
1247 END IF;
1248
1249 IF x_category_rec.attribute13 = g_MISS_CHAR THEN
1250 x_category_rec.attribute13 := l_category_rec.attribute13;
1251 END IF;
1252
1253 IF x_category_rec.attribute14 = g_MISS_CHAR THEN
1254 x_category_rec.attribute14 := l_category_rec.attribute14;
1255 END IF;
1256
1257 IF x_category_rec.attribute15 = g_MISS_CHAR THEN
1258 x_category_rec.attribute15 := l_category_rec.attribute15;
1259 END IF;
1260
1261 END IF; --IF (p_operation = G_UPDATE) THEN
1262
1263
1264 END Preprocess_Category_Rec;
1265
1266
1267 --
1268 -- Bug 13850442. Create_category allowing to create
1269 -- categories with disabled value from valueset.
1270 -- Adding new param for transaction type.
1271 -- sreharih. Wed Mar 21 13:22:46 PDT 2012
1272 --
1273 PROCEDURE ValueSet_Validate
1274 (
1275 p_structure_id IN NUMBER,
1276 p_concat_segs IN VARCHAR2,
1277 p_operation IN NUMBER -- added for 13850442
1278 ) IS
1279 l_success BOOLEAN;
1280 l_trim_str VARCHAR2(2000) ;
1281 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1282
1283 BEGIN
1284 l_success := fnd_flex_keyval.validate_segs(
1285 operation => 'CHECK_SEGMENTS',
1286 appl_short_name => G_INVENTORY_APP_SHORT_NAME,
1287 key_flex_code => G_CAT_FLEX_CODE,
1288 structure_number => p_structure_id,
1289 concat_segments => p_concat_segs
1290 );
1291
1292 --
1293 -- Bug 13850442. Create_category allowing to create
1294 -- categories with disabled value from valueset.
1295 -- Adding condition to suppress the valueset check
1296 -- only for update scenario. This is a quick fix.
1297 -- We must get rid off hardcoded msg and this logic.
1298 -- sreharih. Wed Mar 21 13:22:46 PDT 2012
1299 --
1300
1301
1302 --Bug: 2445444 modified If condition
1303 IF (l_success OR
1304 ( NOT l_success AND p_operation = G_UPDATE AND -- added for 13850442
1305 (INSTR(FND_FLEX_KEYVAL.error_message,'has been disabled.')> 0 OR
1306 INSTR(FND_FLEX_KEYVAL.error_message,'has expired.')> 0 OR
1307 INSTR(FND_FLEX_KEYVAL.error_message,'This combination is disabled')>0))) THEN
1308 NULL;
1309 ELSE
1310 l_trim_str := FND_FLEX_KEYVAL.error_message;
1311 fnd_message.set_name('FND','FLEX-SSV EXCEPTION');
1312 fnd_message.set_token('MSG', 'Value set validation error in ValueSet_Validate()');
1313 fnd_msg_pub.ADD;
1314 IF (l_debug = 1) THEN
1315 mdebug('ValueSet Validation Error : '||l_trim_str);
1316 END IF;
1317
1318 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1319 END IF;
1320 END ValueSet_Validate;
1321
1322 ----------------------------------------------------------------------------
1323 PROCEDURE Flex_Validate
1324 (
1325 p_operation IN NUMBER,
1326 p_category_rec IN INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE
1327 ) IS
1328 l_category_id NUMBER;
1329 l_structure_id NUMBER;
1330 l_success BOOLEAN;
1331 l_concat_segs VARCHAR2(2000) ;
1332 l_n_segments NUMBER ;
1333 l_segment_array FND_FLEX_EXT.SegmentArray;
1334 l_delim VARCHAR2(10);
1335 l_indx NUMBER;
1336
1337 CURSOR segment_count(p_structure_id NUMBER) IS
1338 SELECT count(segment_num)
1339 FROM fnd_id_flex_segments
1340 WHERE application_id = G_INVENTORY_APP_ID
1341 AND id_flex_code = G_CAT_FLEX_CODE
1342 AND id_flex_num = p_structure_id
1343 AND (enabled_flag = 'Y' OR NVL(g_eni_upgarde_flag,'N') = 'Y');-- Added for 11.5.10 ENI Upgrade
1344
1345 --Bug: 3893482
1346 CURSOR c_get_segments(cp_flex_num NUMBER) IS
1347 SELECT application_column_name,rownum
1348 FROM fnd_id_flex_segments
1349 WHERE application_id = 401
1350 AND id_flex_code = 'MCAT'
1351 AND id_flex_num = cp_flex_num
1352 AND enabled_flag = 'Y'
1353 ORDER BY segment_num ASC;
1354
1355 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1356 BEGIN
1357 l_structure_id := p_category_rec.structure_id;
1358
1359 OPEN segment_count(l_structure_id);
1360 FETCH segment_count INTO l_n_segments;
1361 IF (segment_count%NOTFOUND) THEN
1362 IF (l_debug = 1) THEN
1363 mdebug('The Number of segments not found');
1364 END IF;
1365 END IF;
1366 CLOSE segment_count;
1367 IF (l_debug = 1) THEN
1368 mdebug('Tracing....4');
1369 END IF;
1370
1371
1372 l_delim := fnd_flex_ext.get_delimiter(G_INVENTORY_APP_SHORT_NAME,
1373 G_CAT_FLEX_CODE,
1374 l_structure_id);
1375 IF l_delim is NULL then
1376 fnd_message.set_name('OFA','FA_BUDGET_NO_SEG_DELIM');
1377 fnd_msg_pub.ADD;
1378 IF (l_debug = 1) THEN
1379 mdebug('Delimiter is NULL...Error');
1380 END IF;
1381 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1382 END IF;
1383
1384 --Start: 3893482
1385 l_indx := 1;
1386 FOR c_segments in c_get_segments(l_structure_id) LOOP
1387 IF c_segments.application_column_name = 'SEGMENT1' THEN
1388 l_segment_array(l_indx):= p_category_rec.segment1;
1389 ELSIF c_segments.application_column_name = 'SEGMENT2' THEN
1390 l_segment_array(l_indx):= p_category_rec.segment2;
1391 ELSIF c_segments.application_column_name = 'SEGMENT3' THEN
1392 l_segment_array(l_indx):= p_category_rec.segment3;
1393 ELSIF c_segments.application_column_name = 'SEGMENT4' THEN
1394 l_segment_array(l_indx):= p_category_rec.segment4;
1395 ELSIF c_segments.application_column_name = 'SEGMENT5' THEN
1396 l_segment_array(l_indx):= p_category_rec.segment5;
1397 ELSIF c_segments.application_column_name = 'SEGMENT6' THEN
1398 l_segment_array(l_indx):= p_category_rec.segment6;
1399 ELSIF c_segments.application_column_name = 'SEGMENT7' THEN
1400 l_segment_array(l_indx):= p_category_rec.segment7;
1401 ELSIF c_segments.application_column_name = 'SEGMENT8' THEN
1402 l_segment_array(l_indx):= p_category_rec.segment8;
1403 ELSIF c_segments.application_column_name = 'SEGMENT9' THEN
1404 l_segment_array(l_indx):= p_category_rec.segment9;
1405 ELSIF c_segments.application_column_name = 'SEGMENT10' THEN
1406 l_segment_array(l_indx):= p_category_rec.segment10;
1407 ELSIF c_segments.application_column_name = 'SEGMENT11' THEN
1408 l_segment_array(l_indx):= p_category_rec.segment11;
1409 ELSIF c_segments.application_column_name = 'SEGMENT12' THEN
1410 l_segment_array(l_indx):= p_category_rec.segment12;
1411 ELSIF c_segments.application_column_name = 'SEGMENT13' THEN
1412 l_segment_array(l_indx):= p_category_rec.segment13;
1413 ELSIF c_segments.application_column_name = 'SEGMENT14' THEN
1414 l_segment_array(l_indx):= p_category_rec.segment14;
1415 ELSIF c_segments.application_column_name = 'SEGMENT15' THEN
1416 l_segment_array(l_indx):= p_category_rec.segment15;
1417 ELSIF c_segments.application_column_name = 'SEGMENT16' THEN
1418 l_segment_array(l_indx):= p_category_rec.segment16;
1419 ELSIF c_segments.application_column_name = 'SEGMENT17' THEN
1420 l_segment_array(l_indx):= p_category_rec.segment17;
1421 ELSIF c_segments.application_column_name = 'SEGMENT18' THEN
1422 l_segment_array(l_indx):= p_category_rec.segment18;
1423 ELSIF c_segments.application_column_name = 'SEGMENT19' THEN
1424 l_segment_array(l_indx):= p_category_rec.segment19;
1425 ELSIF c_segments.application_column_name = 'SEGMENT20' THEN
1426 l_segment_array(l_indx):= p_category_rec.segment20;
1427 END IF;
1428 l_indx := l_indx+1;
1429 END LOOP;
1430 --End: 3893482
1431
1432 /*
1433 l_segment_array(1) := p_category_rec.segment1 ;
1434 l_segment_array(2) := p_category_rec.segment2 ;
1435 l_segment_array(3) := p_category_rec.segment3 ;
1436 l_segment_array(4) := p_category_rec.segment4 ;
1437 l_segment_array(5) := p_category_rec.segment5 ;
1438 l_segment_array(6) := p_category_rec.segment6 ;
1439 l_segment_array(7) := p_category_rec.segment7 ;
1440 l_segment_array(8) := p_category_rec.segment8 ;
1441 l_segment_array(9) := p_category_rec.segment9 ;
1442 l_segment_array(10):= p_category_rec.segment10;
1443 l_segment_array(11):= p_category_rec.segment11;
1444 l_segment_array(12):= p_category_rec.segment12;
1445 l_segment_array(13):= p_category_rec.segment13;
1446 l_segment_array(14):= p_category_rec.segment14;
1447 l_segment_array(15):= p_category_rec.segment15;
1448 l_segment_array(16):= p_category_rec.segment16;
1449 l_segment_array(17):= p_category_rec.segment17;
1450 l_segment_array(18):= p_category_rec.segment18;
1451 l_segment_array(19):= p_category_rec.segment19;
1452 l_segment_array(20):= p_category_rec.segment20;
1453 */
1454
1455 IF (l_debug = 1) THEN
1456 mdebug('Tracing....5');
1457 END IF;
1458
1459
1460 l_concat_segs :=fnd_flex_ext.concatenate_segments(l_n_segments,
1461 l_segment_array,
1462 l_delim);
1463
1464 IF (l_debug = 1) THEN
1465 mdebug('Delim : '||l_delim);
1466 mdebug('Flex code : '||G_CAT_FLEX_CODE);
1467 mdebug('struct# : '||l_structure_id);
1468 mdebug('# of segs : '||to_char(l_n_segments));
1469 mdebug('Concat segs : '||l_concat_segs);
1470 END IF;
1471
1472 l_success := fnd_flex_keyval.validate_segs(
1473 operation => 'FIND_COMBINATION',
1474 appl_short_name => G_INVENTORY_APP_SHORT_NAME,
1475 key_flex_code => G_CAT_FLEX_CODE,
1476 structure_number => l_structure_id,
1477 concat_segments => l_concat_segs
1478 );
1479 --Bug: 2445444 modified If condition
1480 IF (l_success OR
1481 ( NOT l_success AND
1482 (INSTR(FND_FLEX_KEYVAL.error_message,'has been disabled.')> 0 OR
1483 INSTR(FND_FLEX_KEYVAL.error_message,'has expired.')> 0 OR
1484 INSTR(FND_FLEX_KEYVAL.error_message,'This combination is disabled')> 0
1485 )
1486 AND (p_operation = G_UPDATE))) THEN
1487 IF (p_operation = G_INSERT) THEN
1488
1489 fnd_message.set_name('INV','INV_NEW_ENT');
1490 fnd_message.set_token('TOKEN', 'Category Segment Combination');
1491 fnd_msg_pub.ADD;
1492 IF (l_debug = 1) THEN
1493 mdebug('CCID already exists => '|| To_char(FND_FLEX_KEYVAL.combination_id));
1494 END IF;
1495 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1496
1497 ELSIF (p_operation = G_UPDATE) THEN
1498 IF (FND_FLEX_KEYVAL.combination_id <>
1499 p_category_rec.category_id) THEN
1500 fnd_message.set_name('INV','INV_NEW_ENT');
1501 fnd_message.set_token('TOKEN', 'Category segment combination. Specified Combination used by another Category.');
1502 fnd_msg_pub.ADD;
1503 IF (l_debug = 1) THEN
1504 mdebug( 'Code combination already used for another category');
1505 END IF;
1506 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1507 ELSE
1508 ValueSet_Validate(l_structure_id, l_concat_segs, p_operation); -- added p_operation for 13850442
1509 IF (l_debug = 1) THEN
1510 mdebug('Updating CCID/Category_Id => '|| To_char(FND_FLEX_KEYVAL.combination_id));
1511 END IF;
1512 END IF;
1513 ELSE -- neither insert nor update
1514 NULL;
1515 END IF;
1516 ELSE -- (l_success = FALSE)
1517 IF (p_operation = G_INSERT) THEN
1518 ValueSet_Validate(l_structure_id, l_concat_segs, p_operation); -- added p_operation for 13850442
1519 IF (l_debug = 1) THEN
1520 mdebug('Combination new. Creating Category....');
1521 END IF;
1522
1523 /* -------------------------------------------------------
1524 The COMBINATION need not be created using this.
1525 Calling procedure will take care of inserting record.
1526 Since the COMBINATION_ID is Category_Id, just verifying if the
1527 comb. exists through fnd_flex_keyval.validate_segs(FIND_COMB..)
1528 call and inserting directly in database through Table Handler
1529 would be enough. The folllowing could be used as alternative.
1530
1531 l_success := fnd_flex_keyval.validate_segs(
1532 operation => 'CREATE_COMBINATION',
1533 appl_short_name => G_INVENTORY_APP_SHORT_NAME,
1534 key_flex_code => G_CAT_FLEX_CODE,
1535 structure_number => l_structure_id,
1536 concat_segments => l_concat_segs
1537 );
1538 IF (l_debug = 1) THEN
1539 mdebug('The CCID : '||To_char(FND_FLEX_KEYVAL.combination_id));
1540 mdebug('Error : '||FND_FLEX_KEYVAL.error_message);
1541 END IF;
1542 --------------------------------------------------------- */
1543
1544 ELSIF (p_operation = G_UPDATE) THEN
1545 fnd_message.set_name('INV','INV_VALID_CAT');
1546 fnd_msg_pub.ADD;
1547 IF (l_debug = 1) THEN
1548 mdebug('Trying to update a non-existant ROW');
1549 END IF;
1550 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1551
1552 ELSE -- neither insert nor update
1553 NULL;
1554 END IF;
1555 END IF;
1556
1557 --mdebug('Error : '||FND_FLEX_KEYVAL.error_message);
1558 END Flex_Validate;
1559
1560 -- 1. Create_Category
1561 ----------------------------------------------------------------------------
1562 PROCEDURE Create_Category
1563 (
1564 p_api_version IN NUMBER ,
1565 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1566 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1567 x_return_status OUT NOCOPY VARCHAR2 ,
1568 x_errorcode OUT NOCOPY NUMBER,
1569 x_msg_count OUT NOCOPY NUMBER ,
1570 x_msg_data OUT NOCOPY VARCHAR2 ,
1571 p_category_rec IN INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE,
1572 x_category_id OUT NOCOPY NUMBER
1573 )
1574 IS
1575 -- Start OF comments
1576 -- API name : Create_Category
1577 -- TYPE : Public
1578 -- Pre-reqs : None
1579 -- FUNCTION : Create a category.
1580 --
1581 -- Version: Current Version 0.1
1582 -- Previous Version : None
1583 -- Notes :
1584 --
1585 -- END OF comments
1586
1587 l_api_name CONSTANT VARCHAR2(30) := 'Create_Category';
1588 -- On addition of any Required parameters the major version needs
1589 -- to change i.e. for eg. 1.X to 2.X.
1590 -- On addition of any Optional parameters the minor version needs
1591 -- to change i.e. for eg. X.6 to X.7.
1592
1593 l_api_version CONSTANT NUMBER := 1.0;
1594 l_row_count NUMBER;
1595
1596 -- General variables
1597 l_category_rec INV_ITEM_CATEGORY_PUB.category_rec_type;
1598 l_category_id NUMBER;
1599 l_success BOOLEAN; --boolean for descr. flex valiation
1600 l_row_id VARCHAR2(20);
1601 l_sys_date DATE := Sysdate;
1602
1603 CURSOR new_category_id IS
1604 SELECT mtl_categories_s.nextval
1605 FROM dual;
1606 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1607 BEGIN
1608 -- Standard Start of API savepoint
1609 SAVEPOINT Create_Category_PUB;
1610
1611 -- Check for call compatibility.
1612 IF NOT FND_API.Compatible_API_Call (l_api_version,
1613 p_api_version,
1614 l_api_name,
1615 G_PKG_NAME)
1616 THEN
1617 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1618 END IF;
1619 -- Initialize API message list if necessary.
1620 -- Initialize message list if p_init_msg_list is set to TRUE.
1621 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1622 FND_MSG_PUB.initialize;
1623 END IF;
1624
1625 IF (l_debug = 1) THEN
1626 mdebug('Tracing....1');
1627 END IF;
1628
1629 -- To set the APPS Environment context through PL/SQL.
1630 -- Apps_Initialize();
1631
1632 -- To process the Input record for any invalid values provided.
1633 Preprocess_Category_Rec(G_INSERT, p_category_rec, l_category_rec) ;
1634 Flex_Validate(G_INSERT, l_category_rec);
1635
1636 -- Category_Id is always created from sequence.
1637 OPEN new_category_id;
1638 FETCH new_category_id INTO l_category_id;
1639 IF (new_category_id%NOTFOUND) THEN
1640 IF (l_debug = 1) THEN
1641 mdebug('Dubious error with the MTL_CATEGORIES_S sequence');
1642 END IF;
1643 END IF;
1644 CLOSE new_category_id;
1645
1646 /* Need for Descriptive Flex validation
1647
1648 l_attribute_category := l_category_rec.attribute_category;
1649 l_attribute1 := l_category_rec.attribute1 ;
1650 l_attribute2 := l_category_rec.attribute2 ;
1651 l_attribute3 := l_category_rec.attribute3 ;
1652 l_attribute4 := l_category_rec.attribute4 ;
1653 l_attribute5 := l_category_rec.attribute5 ;
1654 l_attribute6 := l_category_rec.attribute6 ;
1655 l_attribute7 := l_category_rec.attribute7 ;
1656 l_attribute8 := l_category_rec.attribute8 ;
1657 l_attribute9 := l_category_rec.attribute9 ;
1658 l_attribute10 := l_category_rec.attribute10;
1659 l_attribute11 := l_category_rec.attribute11;
1660 l_attribute12 := l_category_rec.attribute12;
1661 l_attribute13 := l_category_rec.attribute13;
1662 l_attribute14 := l_category_rec.attribute14;
1663 l_attribute15 := l_category_rec.attribute15;
1664 */
1665
1666 --Final call for insertion.
1667 MTL_CATEGORIES_PKG.Insert_Row(
1668 X_ROWID => l_row_id, -- OUT variable
1669 X_CATEGORY_ID => l_category_id, -- gen from seq.
1670 X_DESCRIPTION => l_category_rec.description,
1671 X_STRUCTURE_ID => l_category_rec.structure_id,
1672 X_DISABLE_DATE => l_category_rec.disable_date,
1673 X_WEB_STATUS => l_category_rec.web_status,--Bug: 2430879
1674 X_SUPPLIER_ENABLED_FLAG => l_category_rec.supplier_enabled_flag,--Bug: 2645153
1675 X_SEGMENT1 => l_category_rec.segment1 ,
1676 X_SEGMENT2 => l_category_rec.segment2 ,
1677 X_SEGMENT3 => l_category_rec.segment3 ,
1678 X_SEGMENT4 => l_category_rec.segment4 ,
1679 X_SEGMENT5 => l_category_rec.segment5 ,
1680 X_SEGMENT6 => l_category_rec.segment6 ,
1681 X_SEGMENT7 => l_category_rec.segment7 ,
1682 X_SEGMENT8 => l_category_rec.segment8 ,
1683 X_SEGMENT9 => l_category_rec.segment9 ,
1684 X_SEGMENT10 => l_category_rec.segment10 ,
1685 X_SEGMENT11 => l_category_rec.segment11 ,
1686 X_SEGMENT12 => l_category_rec.segment12 ,
1687 X_SEGMENT13 => l_category_rec.segment13 ,
1688 X_SEGMENT14 => l_category_rec.segment14 ,
1689 X_SEGMENT15 => l_category_rec.segment15 ,
1690 X_SEGMENT16 => l_category_rec.segment16 ,
1691 X_SEGMENT17 => l_category_rec.segment17 ,
1692 X_SEGMENT18 => l_category_rec.segment18 ,
1693 X_SEGMENT19 => l_category_rec.segment19 ,
1694 X_SEGMENT20 => l_category_rec.segment20 ,
1695 X_SUMMARY_FLAG => l_category_rec.summary_flag,
1696 X_ENABLED_FLAG => l_category_rec.enabled_flag,
1697 X_START_DATE_ACTIVE => l_category_rec.start_date_active,
1698 X_END_DATE_ACTIVE => l_category_rec.end_date_active,
1699 X_ATTRIBUTE_CATEGORY => l_category_rec.attribute_category,
1700 X_ATTRIBUTE1 => l_category_rec.attribute1 ,
1701 X_ATTRIBUTE2 => l_category_rec.attribute2 ,
1702 X_ATTRIBUTE3 => l_category_rec.attribute3 ,
1703 X_ATTRIBUTE4 => l_category_rec.attribute4 ,
1704 X_ATTRIBUTE5 => l_category_rec.attribute5 ,
1705 X_ATTRIBUTE6 => l_category_rec.attribute6 ,
1706 X_ATTRIBUTE7 => l_category_rec.attribute7 ,
1707 X_ATTRIBUTE8 => l_category_rec.attribute8 ,
1708 X_ATTRIBUTE9 => l_category_rec.attribute9 ,
1709 X_ATTRIBUTE10 => l_category_rec.attribute10,
1710 X_ATTRIBUTE11 => l_category_rec.attribute11,
1711 X_ATTRIBUTE12 => l_category_rec.attribute12,
1712 X_ATTRIBUTE13 => l_category_rec.attribute13,
1713 X_ATTRIBUTE14 => l_category_rec.attribute14,
1714 X_ATTRIBUTE15 => l_category_rec.attribute15,
1715 X_LAST_UPDATE_DATE => l_sys_date,
1716 X_LAST_UPDATED_BY => fnd_global.user_id,
1717 X_CREATION_DATE => l_sys_date,
1718 X_CREATED_BY => fnd_global.user_id,
1719 X_LAST_UPDATE_LOGIN => fnd_global.login_id
1720 );
1721
1722 IF (l_debug = 1) THEN
1723 mdebug('Created New CCID/Category_ID : '|| l_category_id);
1724 END IF;
1725 -- assigning the created value to the return OUT value
1726 x_category_id := l_category_id;
1727
1728 IF (l_debug = 1) THEN
1729 mdebug('Tracing....10');
1730 END IF;
1731
1732 -- Standard check of p_commit.
1733 IF FND_API.To_Boolean( p_commit ) THEN
1734 COMMIT WORK;
1735 END IF;
1736
1737 x_return_status := FND_API.G_RET_STS_SUCCESS;
1738 -- Standard call to get message count and if count is 1,
1739 -- get message info.
1740 -- The client will directly display the x_msg_data (which is already
1741 -- translated) if the x_msg_count = 1;
1742 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
1743 -- Server-side procedure to access the messages, and consolidate them
1744 -- and display (or) to display one message after another.
1745 IF (l_debug = 1) THEN
1746 mdebug('Tracing....11');
1747 END IF;
1748 FND_MSG_PUB.Count_And_Get
1749 ( p_count => x_msg_count,
1750 p_data => x_msg_data
1751 );
1752 EXCEPTION
1753 WHEN FND_API.G_EXC_ERROR THEN
1754 IF (l_debug = 1) THEN
1755 mdebug('Ending : Returning ERROR');
1756 END IF;
1757 ROLLBACK TO Create_Category_PUB;
1758 x_return_status := FND_API.G_RET_STS_ERROR;
1759 FND_MSG_PUB.Count_And_Get
1760 ( p_count => x_msg_count,
1761 p_data => x_msg_data
1762 );
1763 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1764 ROLLBACK TO Create_Category_PUB;
1765 IF (l_debug = 1) THEN
1766 mdebug('Ending : Returning UNEXPECTED ERROR');
1767 END IF;
1768 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1769 FND_MSG_PUB.Count_And_Get
1770 ( p_count => x_msg_count,
1771 p_data => x_msg_data
1772 );
1773 WHEN OTHERS THEN
1774 ROLLBACK TO Create_Category_PUB;
1775 IF (l_debug = 1) THEN
1776 mdebug('Ending : Returning UNEXPECTED ERROR');
1777 END IF;
1778 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1779 IF FND_MSG_PUB.Check_Msg_Level
1780 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1781 THEN
1782 FND_MSG_PUB.Add_Exc_Msg
1783 ( G_PKG_NAME ,
1784 l_api_name
1785 );
1786 END IF;
1787 FND_MSG_PUB.Count_And_Get
1788 ( p_count => x_msg_count,
1789 p_data => x_msg_data
1790 );
1791
1792 END Create_Category;
1793 ----------------------------------------------------------------------------
1794
1795
1796 -- 2. Update_Category
1797 ----------------------------------------------------------------------------
1798 PROCEDURE Update_Category
1799 (
1800 p_api_version IN NUMBER ,
1801 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1802 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1803 x_return_status OUT NOCOPY VARCHAR2 ,
1804 x_errorcode OUT NOCOPY NUMBER,
1805 x_msg_count OUT NOCOPY NUMBER ,
1806 x_msg_data OUT NOCOPY VARCHAR2 ,
1807 p_category_rec IN INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE
1808 )
1809 IS
1810
1811 -- Start OF comments
1812 -- API name : Update_Category
1813 -- TYPE : Public
1814 -- Pre-reqs : None
1815 -- FUNCTION : Update a category.
1816 --
1817 -- Version: Current Version 0.1
1818 -- Previous Version : None
1819 -- Notes : Stub Version
1820 --
1821 -- END OF comments
1822 l_api_name CONSTANT VARCHAR2(30) := 'Update_Category';
1823 -- On addition of any Required parameters the major version needs
1824 -- to change i.e. for eg. 1.X to 2.X.
1825 -- On addition of any Optional parameters the minor version needs
1826 -- to change i.e. for eg. X.6 to X.7.
1827 l_api_version CONSTANT NUMBER := 1.0;
1828 l_row_count NUMBER;
1829
1830 -- General variables
1831 l_category_rec INV_ITEM_CATEGORY_PUB.category_rec_type;
1832 l_success BOOLEAN; --boolean for descr. flex valiation
1833
1834
1835 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1836 BEGIN
1837 -- Standard Start of API savepoint
1838 SAVEPOINT Update_Category_PUB;
1839
1840
1841 IF NOT FND_API.Compatible_API_Call (l_api_version,
1842 p_api_version ,
1843 l_api_name ,
1844 G_PKG_NAME)
1845 THEN
1846 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1847 END IF;
1848 -- Initialize API message list if necessary.
1849 -- Initialize message list if p_init_msg_list is set to TRUE.
1850 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1851 FND_MSG_PUB.initialize;
1852 END IF;
1853
1854 IF (l_debug = 1) THEN
1855 mdebug('Tracing....1');
1856 END IF;
1857
1858 -- To process the Input record for any invalid values provided.
1859 Preprocess_Category_Rec(G_UPDATE, p_category_rec, l_category_rec) ;
1860 Flex_Validate(G_UPDATE, l_category_rec);
1861
1862
1863 /* Need for Descriptive Flex validation
1864
1865 l_attribute_category := l_category_rec.attribute_category;
1866 l_attribute1 := l_category_rec.attribute1 ;
1867 l_attribute2 := l_category_rec.attribute2 ;
1868 l_attribute3 := l_category_rec.attribute3 ;
1869 l_attribute4 := l_category_rec.attribute4 ;
1870 l_attribute5 := l_category_rec.attribute5 ;
1871 l_attribute6 := l_category_rec.attribute6 ;
1872 l_attribute7 := l_category_rec.attribute7 ;
1873 l_attribute8 := l_category_rec.attribute8 ;
1874 l_attribute9 := l_category_rec.attribute9 ;
1875 l_attribute10 := l_category_rec.attribute10;
1876 l_attribute11 := l_category_rec.attribute11;
1877 l_attribute12 := l_category_rec.attribute12;
1878 l_attribute13 := l_category_rec.attribute13;
1879 l_attribute14 := l_category_rec.attribute14;
1880 l_attribute15 := l_category_rec.attribute15;
1881 */
1882
1883
1884 --Final call for insertion.
1885 MTL_CATEGORIES_PKG.Update_Row(
1886 X_CATEGORY_ID => l_category_rec.category_id,
1887 X_DESCRIPTION => l_category_rec.description,
1888 X_STRUCTURE_ID => l_category_rec.structure_id,
1889 X_DISABLE_DATE => l_category_rec.disable_date,
1890 X_WEB_STATUS => l_category_rec.web_status,--Bug: 2430879
1891 X_SUPPLIER_ENABLED_FLAG => l_category_rec.supplier_enabled_flag,--Bug: 2645153
1892 X_SEGMENT1 => l_category_rec.segment1 ,
1893 X_SEGMENT2 => l_category_rec.segment2 ,
1894 X_SEGMENT3 => l_category_rec.segment3 ,
1895 X_SEGMENT4 => l_category_rec.segment4 ,
1896 X_SEGMENT5 => l_category_rec.segment5 ,
1897 X_SEGMENT6 => l_category_rec.segment6 ,
1898 X_SEGMENT7 => l_category_rec.segment7 ,
1899 X_SEGMENT8 => l_category_rec.segment8 ,
1900 X_SEGMENT9 => l_category_rec.segment9 ,
1901 X_SEGMENT10 => l_category_rec.segment10 ,
1902 X_SEGMENT11 => l_category_rec.segment11 ,
1903 X_SEGMENT12 => l_category_rec.segment12 ,
1904 X_SEGMENT13 => l_category_rec.segment13 ,
1905 X_SEGMENT14 => l_category_rec.segment14 ,
1906 X_SEGMENT15 => l_category_rec.segment15 ,
1907 X_SEGMENT16 => l_category_rec.segment16 ,
1908 X_SEGMENT17 => l_category_rec.segment17 ,
1909 X_SEGMENT18 => l_category_rec.segment18 ,
1910 X_SEGMENT19 => l_category_rec.segment19 ,
1911 X_SEGMENT20 => l_category_rec.segment20 ,
1912 X_SUMMARY_FLAG => l_category_rec.summary_flag,
1913 X_ENABLED_FLAG => l_category_rec.enabled_flag,
1914 X_START_DATE_ACTIVE => l_category_rec.start_date_active,
1915 X_END_DATE_ACTIVE => l_category_rec.end_date_active,
1916 X_ATTRIBUTE_CATEGORY => l_category_rec.attribute_category,
1917 X_ATTRIBUTE1 => l_category_rec.attribute1 ,
1918 X_ATTRIBUTE2 => l_category_rec.attribute2 ,
1919 X_ATTRIBUTE3 => l_category_rec.attribute3 ,
1920 X_ATTRIBUTE4 => l_category_rec.attribute4 ,
1921 X_ATTRIBUTE5 => l_category_rec.attribute5 ,
1922 X_ATTRIBUTE6 => l_category_rec.attribute6 ,
1923 X_ATTRIBUTE7 => l_category_rec.attribute7 ,
1924 X_ATTRIBUTE8 => l_category_rec.attribute8 ,
1925 X_ATTRIBUTE9 => l_category_rec.attribute9 ,
1926 X_ATTRIBUTE10 => l_category_rec.attribute10,
1927 X_ATTRIBUTE11 => l_category_rec.attribute11,
1928 X_ATTRIBUTE12 => l_category_rec.attribute12,
1929 X_ATTRIBUTE13 => l_category_rec.attribute13,
1930 X_ATTRIBUTE14 => l_category_rec.attribute14,
1931 X_ATTRIBUTE15 => l_category_rec.attribute15,
1932 X_LAST_UPDATE_DATE => sysdate,
1933 X_LAST_UPDATED_BY => fnd_global.user_id,
1934 X_LAST_UPDATE_LOGIN => fnd_global.login_id
1935 );
1936
1937 IF (l_debug = 1) THEN
1938 mdebug('Updated Category: '||To_char(l_category_rec.category_id));
1939 END IF;
1940
1941 IF (l_debug = 1) THEN
1942 mdebug('Update_Category:: Tracing....10');
1943 END IF;
1944
1945 -- Standard check of p_commit.
1946 IF FND_API.To_Boolean( p_commit ) THEN
1947 COMMIT WORK;
1948 END IF;
1949
1950 x_return_status := FND_API.G_RET_STS_SUCCESS;
1951 -- Standard call to get message count and if count is 1,
1952 -- get message info.
1953 -- The client will directly display the x_msg_data (which is already
1954 -- translated) if the x_msg_count = 1;
1955 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
1956 -- Server-side procedure to access the messages, and consolidate them
1957 -- and display (or) to display one message after another.
1958 FND_MSG_PUB.Count_And_Get
1959 ( p_count => x_msg_count,
1960 p_data => x_msg_data
1961 );
1962 EXCEPTION
1963 WHEN FND_API.G_EXC_ERROR THEN
1964 ROLLBACK TO Update_Category_PUB;
1965 x_return_status := FND_API.G_RET_STS_ERROR;
1966 FND_MSG_PUB.Count_And_Get
1967 ( p_count => x_msg_count,
1968 p_data => x_msg_data
1969 );
1970 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1971 ROLLBACK TO Update_Category_PUB;
1972 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1973 FND_MSG_PUB.Count_And_Get
1974 ( p_count => x_msg_count,
1975 p_data => x_msg_data
1976 );
1977 WHEN OTHERS THEN
1978 ROLLBACK TO Update_Category_PUB;
1979 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1980 IF FND_MSG_PUB.Check_Msg_Level
1981 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1982 THEN
1983 FND_MSG_PUB.Add_Exc_Msg
1984 ( G_PKG_NAME ,
1985 l_api_name
1986 );
1987 END IF;
1988 FND_MSG_PUB.Count_And_Get
1989 ( p_count => x_msg_count,
1990 p_data => x_msg_data
1991 );
1992
1993
1994
1995 END Update_Category;
1996 ----------------------------------------------------------------------------
1997
1998
1999 -- 3. Update_Category_Description
2000 ----------------------------------------------------------------------------
2001 PROCEDURE Update_Category_Description
2002 (
2003 p_api_version IN NUMBER ,
2004 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2005 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2006 x_return_status OUT NOCOPY VARCHAR2 ,
2007 x_errorcode OUT NOCOPY NUMBER,
2008 x_msg_count OUT NOCOPY NUMBER ,
2009 x_msg_data OUT NOCOPY VARCHAR2 ,
2010 p_category_id IN NUMBER,
2011 p_description IN VARCHAR2
2012 -- deleted as this can be picked up from the environment.
2013 --p_language IN VARCHAR2
2014 )
2015 IS
2016 -- Start OF comments
2017 -- API name : Update_Category_Description
2018 -- TYPE : Public
2019 -- Pre-reqs : None
2020 -- FUNCTION : Update a category description in the specified language.
2021 --
2022 -- Version: Current Version 0.1
2023 -- Previous Version : None
2024 -- Notes : Stub Version
2025 -- END OF comments
2026 l_api_name CONSTANT VARCHAR2(30) := 'Update_Category_Description';
2027 -- On addition of any Required parameters the major version needs
2028 -- to change i.e. for eg. 1.X to 2.X.
2029 -- On addition of any Optional parameters the minor version needs
2030 -- to change i.e. for eg. X.6 to X.7.
2031 l_api_version CONSTANT NUMBER := 1.0;
2032 l_row_count NUMBER;
2033 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2034 BEGIN
2035 -- Standard Start of API savepoint
2036 SAVEPOINT Update_Category_Desc_PUB;
2037
2038
2039 -- Check for call compatibility.
2040 IF NOT FND_API.Compatible_API_Call (l_api_version,
2041 p_api_version ,
2042 l_api_name ,
2043 G_PKG_NAME)
2044 THEN
2045 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2046 END IF;
2047 -- Initialize API message list if necessary.
2048 -- Initialize message list if p_init_msg_list is set to TRUE.
2049 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2050 FND_MSG_PUB.initialize;
2051 END IF;
2052
2053 UPDATE mtl_categories_tl
2054 SET
2055 description = p_description,
2056 last_update_date = Sysdate,
2057 last_updated_by = fnd_global.user_id,
2058 last_update_login = fnd_global.login_id,
2059 source_lang = userenv('LANG')
2060 WHERE category_id = p_category_id
2061 AND userenv('LANG') IN (language, source_lang) ;
2062
2063 IF (sql%notfound) THEN
2064 fnd_message.set_name('INV','INV_VALID_CAT');
2065 fnd_msg_pub.ADD;
2066 IF (l_debug = 1) THEN
2067 mdebug('Trying to Update a non-existant Category.');
2068 END IF;
2069 RAISE NO_DATA_FOUND;
2070 END IF;
2071
2072 -- Standard check of p_commit.
2073 IF FND_API.To_Boolean( p_commit ) THEN
2074 COMMIT WORK;
2075 END IF;
2076
2077 x_return_status := FND_API.G_RET_STS_SUCCESS;
2078 -- Standard call to get message count and if count is 1,
2079 -- get message info.
2080 -- The client will directly display the x_msg_data (which is already
2081 -- translated) if the x_msg_count = 1;
2082 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
2083 -- Server-side procedure to access the messages, and consolidate them
2084 -- and display (or) to display one message after another.
2085 FND_MSG_PUB.Count_And_Get
2086 ( p_count => x_msg_count,
2087 p_data => x_msg_data
2088 );
2089
2090 EXCEPTION
2091 WHEN FND_API.G_EXC_ERROR THEN
2092 ROLLBACK TO Update_Category_Desc_PUB;
2093 x_return_status := FND_API.G_RET_STS_ERROR;
2094 FND_MSG_PUB.Count_And_Get
2095 ( p_count => x_msg_count,
2096 p_data => x_msg_data
2097 );
2098 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2099 ROLLBACK TO Update_Category_Desc_PUB;
2100 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2101 FND_MSG_PUB.Count_And_Get
2102 ( p_count => x_msg_count,
2103 p_data => x_msg_data
2104 );
2105 WHEN OTHERS THEN
2106 ROLLBACK TO Update_Category_Desc_PUB;
2107 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2108 IF FND_MSG_PUB.Check_Msg_Level
2109 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2110 THEN
2111 FND_MSG_PUB.Add_Exc_Msg
2112 ( G_PKG_NAME ,
2113 l_api_name
2114 );
2115 END IF;
2116 FND_MSG_PUB.Count_And_Get
2117 ( p_count => x_msg_count,
2118 p_data => x_msg_data
2119 );
2120
2121 END Update_Category_Description;
2122 ----------------------------------------------------------------------------
2123
2124 -- 4. Delete_Category
2125 ----------------------------------------------------------------------------
2126 -- ----------------------------------------------------------------------
2127 -- Deletion of categories is not supported.
2128 -- ----------------------------------------------------------------------
2129
2130 PROCEDURE Delete_Category
2131 (
2132 p_api_version IN NUMBER ,
2133 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2134 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2135 x_return_status OUT NOCOPY VARCHAR2 ,
2136 x_errorcode OUT NOCOPY NUMBER,
2137 x_msg_count OUT NOCOPY NUMBER ,
2138 x_msg_data OUT NOCOPY VARCHAR2 ,
2139 p_category_id IN NUMBER
2140 )
2141 IS
2142 -- Start OF comments
2143 -- API name : Delete_Category
2144 -- TYPE : Public
2145 -- Pre-reqs : None
2146 -- FUNCTION : Delete a category.
2147 --
2148 -- Version: Current Version 0.1
2149 -- Previous Version : None
2150 -- Notes : Stub Version
2151 --
2152 -- END OF comments
2153 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Category';
2154 -- On addition of any Required parameters the major version needs
2155 -- to change i.e. for eg. 1.X to 2.X.
2156 -- On addition of any Optional parameters the minor version needs
2157 -- to change i.e. for eg. X.6 to X.7.
2158 l_api_version CONSTANT NUMBER := 1.0;
2159 l_row_count NUMBER;
2160 l_category_assignment_exists VARCHAR(1);
2161 l_default_category_exists VARCHAR(1);
2162 l_valid_category_exists VARCHAR(1);
2163
2164 CURSOR category_assignment_exists(p_category_id NUMBER) IS
2165 SELECT 'x'
2166 FROM dual
2167 WHERE exists
2168 ( SELECT category_id
2169 FROM mtl_item_categories
2170 WHERE category_id = p_category_id
2171 );
2172
2173 CURSOR default_category_exists(p_category_id NUMBER) IS
2174 SELECT 'x'
2175 FROM dual
2176 WHERE exists
2177 ( SELECT default_category_id
2178 FROM mtl_category_sets_b
2179 WHERE default_category_id = p_category_id
2180 );
2181
2182
2183 CURSOR valid_category_exists(p_category_id NUMBER) IS
2184 SELECT 'x'
2185 FROM dual
2186 WHERE exists
2187 ( SELECT category_id
2188 FROM mtl_category_set_valid_cats
2189 WHERE category_id = p_category_id
2190 );
2191
2192
2193 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2194 BEGIN
2195 -- Standard Start of API savepoint
2196 SAVEPOINT Delete_Category_PUB;
2197
2198 -- Check for call compatibility.
2199 IF NOT FND_API.Compatible_API_Call (l_api_version,
2200 p_api_version ,
2201 l_api_name ,
2202 G_PKG_NAME)
2203 THEN
2204 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2205 END IF;
2206 -- Initialize API message list if necessary.
2207 -- Initialize message list if p_init_msg_list is set to TRUE.
2208 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2209 FND_MSG_PUB.initialize;
2210 END IF;
2211
2212 OPEN category_assignment_exists(p_category_id);
2213 FETCH category_assignment_exists INTO l_category_assignment_exists;
2214 IF (category_assignment_exists%NOTFOUND) THEN
2215 IF (l_debug = 1) THEN
2216 mdebug('Can Delete: Category not part of any Category Assignment');
2217 END IF;
2218 END IF;
2219 CLOSE category_assignment_exists;
2220 IF (l_category_assignment_exists = 'x') THEN
2221 fnd_message.set_name('INV','INV_CATEGORY_ASSIGNED');
2222 fnd_msg_pub.ADD;
2223 IF (l_debug = 1) THEN
2224 mdebug('Cannot delete: Category part of a Category Assignment');
2225 END IF;
2226 RAISE FND_API.G_EXC_ERROR;
2227 END IF;
2228
2229 OPEN default_category_exists(p_category_id);
2230 FETCH default_category_exists INTO l_default_category_exists;
2231 IF (default_category_exists%NOTFOUND) THEN
2232 IF (l_debug = 1) THEN
2233 mdebug('Can Delete: Category not a default category');
2234 END IF;
2235 END IF;
2236 CLOSE default_category_exists;
2237 IF (l_default_category_exists = 'x') THEN
2238 fnd_message.set_name('INV','INV_CATEGORY_DEFAULT');
2239 fnd_msg_pub.ADD;
2240 IF (l_debug = 1) THEN
2241 mdebug('Cannot delete: Category specified is a default category to one of the Category Sets.');
2242 END IF;
2243 RAISE FND_API.G_EXC_ERROR;
2244 END IF;
2245
2246 OPEN valid_category_exists(p_category_id);
2247 FETCH valid_category_exists INTO l_valid_category_exists;
2248 IF (valid_category_exists%NOTFOUND) THEN
2249 IF (l_debug = 1) THEN
2250 mdebug('Can Delete: Category not part of a Valid category set');
2251 END IF;
2252 END IF;
2253 CLOSE valid_category_exists;
2254 IF (l_valid_category_exists = 'x') THEN
2255 fnd_message.set_name('INV','INV_CATEGORY_IN_USE');
2256 fnd_msg_pub.ADD;
2257 IF (l_debug = 1) THEN
2258 mdebug('Cannot delete: Category specified is part of a valid category set');
2259 END IF;
2260 RAISE FND_API.G_EXC_ERROR;
2261 END IF;
2262
2263 delete from mtl_categories_tl
2264 where category_id = p_category_id ;
2265
2266 if (sql%notfound) then
2267 fnd_message.set_name('INV','INV_VALID_CAT');
2268 fnd_msg_pub.ADD;
2269 IF (l_debug = 1) THEN
2270 mdebug('Trying to delete non-existant Category Id from MTL_CATEGORIES_TL.');
2271 END IF;
2272 RAISE NO_DATA_FOUND;
2273 end if;
2274
2275 delete from mtl_categories_b
2276 where category_id = p_category_id ;
2277
2278 if (sql%notfound) then
2279 fnd_message.set_name('INV','INV_VALID_CAT');
2280 fnd_msg_pub.ADD;
2281 IF (l_debug = 1) THEN
2282 mdebug('Trying to delete non-existant Category Id from MTL_CATEGORIES_B.');
2283 END IF;
2284 RAISE NO_DATA_FOUND;
2285 end if;
2286
2287 IF (l_debug = 1) THEN
2288 mdebug('Category deleted successfully: '||p_category_id);
2289 END IF;
2290 -- Standard check of p_commit.
2291 IF FND_API.To_Boolean( p_commit ) THEN
2292 COMMIT WORK;
2293 END IF;
2294
2295 x_return_status := FND_API.G_RET_STS_SUCCESS;
2296 -- Standard call to get message count and if count is 1,
2297 -- get message info.
2298 -- The client will directly display the x_msg_data (which is already
2299 -- translated) if the x_msg_count = 1;
2300 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
2301 -- Server-side procedure to access the messages, and consolidate them
2302 -- and display (or) to display one message after another.
2303 FND_MSG_PUB.Count_And_Get
2304 ( p_count => x_msg_count,
2305 p_data => x_msg_data
2306 );
2307 EXCEPTION
2308 WHEN FND_API.G_EXC_ERROR THEN
2309 ROLLBACK TO Delete_Category_PUB;
2310 x_return_status := FND_API.G_RET_STS_ERROR;
2311 FND_MSG_PUB.Count_And_Get
2312 ( p_count => x_msg_count,
2313 p_data => x_msg_data
2314 );
2315 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2316 ROLLBACK TO Delete_Category_PUB;
2317 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2318 FND_MSG_PUB.Count_And_Get
2319 ( p_count => x_msg_count,
2320 p_data => x_msg_data
2321 );
2322 WHEN OTHERS THEN
2323 ROLLBACK TO Delete_Category_PUB;
2324 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2325 IF FND_MSG_PUB.Check_Msg_Level
2326 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2327 THEN
2328 FND_MSG_PUB.Add_Exc_Msg
2329 ( G_PKG_NAME ,
2330 l_api_name
2331 );
2332 END IF;
2333 FND_MSG_PUB.Count_And_Get
2334 ( p_count => x_msg_count,
2335 p_data => x_msg_data
2336 );
2337
2338
2339 END Delete_Category;
2340
2341 ----------------------------------------------------------------------------
2342
2343 -- 5. Create_Category_Assignment
2344 -- Bug: 2451359, All the validations are taken care in the Pvt pkg,so
2345 -- Calling private pkg instead.
2346 ----------------------------------------------------------------------------
2347 PROCEDURE Create_Category_Assignment
2348 (
2349 p_api_version IN NUMBER,
2350 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2351 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2352 x_return_status OUT NOCOPY VARCHAR2,
2353 x_errorcode OUT NOCOPY NUMBER,
2354 x_msg_count OUT NOCOPY NUMBER,
2355 x_msg_data OUT NOCOPY VARCHAR2,
2356 p_category_id IN NUMBER,
2357 p_category_set_id IN NUMBER,
2358 p_inventory_item_id IN NUMBER,
2359 p_organization_id IN NUMBER
2360 )
2361 IS
2362 -- Start OF comments
2363 -- API name : Create_Category_Assignment
2364 -- TYPE : Public
2365 -- Pre-reqs : None
2366 -- FUNCTION : Create an item category assignment.
2367 --
2368 -- Version: Current Version 0.1
2369 -- Previous Version : None
2370 -- Notes : Stub Version
2371 --
2372 -- END OF comments
2373 l_api_name CONSTANT VARCHAR2(30) := 'Create_Category_Assignment';
2374 -- On addition of any Required parameters the major version needs
2375 -- to change i.e. for eg. 1.X to 2.X.
2376 -- On addition of any Optional parameters the minor version needs
2377 -- to change i.e. for eg. X.6 to X.7.
2378 l_api_version CONSTANT NUMBER := 1.0;
2379
2380 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2381 BEGIN
2382
2383 -- Standard Start of API savepoint
2384 SAVEPOINT Create_Category_Assignment_PUB;
2385
2386 -- Check for call compatibility.
2387 IF NOT FND_API.Compatible_API_Call (l_api_version,
2388 p_api_version ,
2389 l_api_name ,
2390 G_PKG_NAME)
2391 THEN
2392 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2393 END IF;
2394 -- Initialize API message list if necessary.
2395 -- Initialize message list if p_init_msg_list is set to TRUE.
2396 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2397 FND_MSG_PUB.initialize;
2398 END IF;
2399 INV_ITEM_MSG.set_Message_Mode('PLSQL');
2400
2401 IF FND_MSG_PUB.Check_Msg_Level
2402 (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
2403 THEN
2404 INV_ITEM_MSG.set_Message_Level(INV_ITEM_MSG.g_Level_Warning);
2405 END IF;
2406
2407 INV_ITEM_CATEGORY_PVT.Create_Category_Assignment
2408 (
2409 p_api_version => p_api_version
2410 , p_init_msg_list => p_init_msg_list
2411 , p_commit => p_commit
2412 , p_validation_level => INV_ITEM_CATEGORY_PVT.g_VALIDATE_ALL
2413 , p_inventory_item_id => p_inventory_item_id
2414 , p_organization_id => p_organization_id
2415 , p_category_set_id => p_category_set_id
2416 , p_category_id => p_category_id
2417 , x_return_status => x_return_status
2418 , x_msg_count => x_msg_count
2419 , x_msg_data => x_msg_data
2420 );
2421
2422 --add by geguo business event enhancement 8351807
2423 BEGIN
2424
2425 IF (l_debug = 1) THEN
2426 mdebug('begin Raise EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT business event');
2427 END IF;
2428 IF (x_return_status = fnd_api.g_RET_STS_SUCCESS) THEN
2429
2430 INV_ITEM_EVENTS_PVT.Raise_Events (
2431 p_commit => FND_API.To_Boolean(p_commit)
2432 ,p_event_name => 'EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT'
2433 ,p_dml_type => 'CREATE'
2434 ,p_inventory_item_id => p_inventory_item_id
2435 ,p_organization_id => p_organization_id
2436 ,p_category_set_id => p_category_set_id
2437 ,p_category_id => p_category_id
2438 ,p_old_category_id => null
2439 );
2440 END IF;
2441 IF (l_debug = 1) THEN
2442 mdebug('end Raise EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT business event');
2443 END IF;
2444
2445 EXCEPTION
2446 WHEN OTHERS THEN
2447 IF (l_debug = 1) THEN
2448 mdebug('error occured when Raise EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT business event');
2449 END IF;
2450 END;
2451 --mdebug('Create_Category_Assignment: Done!!');
2452 -- Standard check of p_commit.
2453 IF FND_API.To_Boolean( p_commit ) THEN
2454 COMMIT WORK;
2455 END IF;
2456
2457 INV_ITEM_MSG.Write_List;
2458 FND_MSG_PUB.Count_And_Get
2459 ( p_count => x_msg_count,
2460 p_data => x_msg_data
2461 );
2462
2463 EXCEPTION
2464 WHEN FND_API.G_EXC_ERROR THEN
2465 ROLLBACK TO Create_Category_Assignment_PUB;
2466 x_return_status := FND_API.G_RET_STS_ERROR;
2467 FND_MSG_PUB.Count_And_Get
2468 ( p_count => x_msg_count,
2469 p_data => x_msg_data
2470 );
2471 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2472 ROLLBACK TO Create_Category_Assignment_PUB;
2473 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2474 FND_MSG_PUB.Count_And_Get
2475 ( p_count => x_msg_count,
2476 p_data => x_msg_data
2477 );
2478 WHEN OTHERS THEN
2479 ROLLBACK TO Create_Category_Assignment_PUB;
2480 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2481 IF FND_MSG_PUB.Check_Msg_Level
2482 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2483 THEN
2484 FND_MSG_PUB.Add_Exc_Msg
2485 ( G_PKG_NAME ,
2486 l_api_name
2487 );
2488 END IF;
2489 FND_MSG_PUB.Count_And_Get
2490 ( p_count => x_msg_count,
2491 p_data => x_msg_data
2492 );
2493
2494 END Create_Category_Assignment;
2495 ----------------------------------------------------------------------------
2496
2497
2498 -- 6. Delete_Category_Assignment
2499 ----------------------------------------------------------------------------
2500 PROCEDURE Delete_Category_Assignment
2501 (
2502 p_api_version IN NUMBER,
2503 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2504 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2505 x_return_status OUT NOCOPY VARCHAR2,
2506 x_errorcode OUT NOCOPY NUMBER,
2507 x_msg_count OUT NOCOPY NUMBER,
2508 x_msg_data OUT NOCOPY VARCHAR2,
2509 p_category_id IN NUMBER,
2510 p_category_set_id IN NUMBER,
2511 p_inventory_item_id IN NUMBER,
2512 p_organization_id IN NUMBER
2513 )
2514 IS
2515 -- Start OF comments
2516 -- API name : Delete_Category_Assignment
2517 -- TYPE : Public
2518 -- Pre-reqs : None
2519 -- FUNCTION : Delete an item category assignment.
2520 --
2521 -- Version: Current Version 0.1
2522 -- Previous Version : None
2523 -- Notes : Stub Version
2524 --
2525 -- END OF comments
2526 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Category_Assignment';
2527 -- On addition of any Required parameters the major version needs
2528 -- to change i.e. for eg. 1.X to 2.X.
2529 -- On addition of any Optional parameters the minor version needs
2530 -- to change i.e. for eg. X.6 to X.7.
2531 l_api_version CONSTANT NUMBER := 1.0;
2532 l_row_count NUMBER;
2533 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2534 BEGIN
2535 -- Standard Start of API savepoint
2536 SAVEPOINT Delete_Category_Assignment_PUB;
2537
2538 -- Check for call compatibility.
2539 IF NOT FND_API.Compatible_API_Call (l_api_version,
2540 p_api_version ,
2541 l_api_name ,
2542 G_PKG_NAME)
2543 THEN
2544 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2545 END IF;
2546 -- Initialize API message list if necessary.
2547 -- Initialize message list if p_init_msg_list is set to TRUE.
2548 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2549 FND_MSG_PUB.initialize;
2550 END IF;
2551 --Added code for bug 2527058
2552 INV_ITEM_MSG.set_Message_Mode('PLSQL');
2553
2554 IF FND_MSG_PUB.Check_Msg_Level
2555 (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
2556 THEN
2557 INV_ITEM_MSG.set_Message_Level(INV_ITEM_MSG.g_Level_Warning);
2558 END IF;
2559
2560 INV_ITEM_CATEGORY_PVT.Delete_Category_Assignment
2561 (
2562 p_api_version => p_api_version
2563 , p_init_msg_list => p_init_msg_list
2564 , p_commit => p_commit
2565 , p_inventory_item_id => p_inventory_item_id
2566 , p_organization_id => p_organization_id
2567 , p_category_set_id => p_category_set_id
2568 , p_category_id => p_category_id
2569 , x_return_status => x_return_status
2570 , x_msg_count => x_msg_count
2571 , x_msg_data => x_msg_data
2572 );
2573
2574 /* IF (l_debug = 1) THEN
2575 mdebug('Delete_Category_Assignment: Tracing...1');
2576 END IF;
2577
2578 DELETE FROM mtl_item_categories
2579 WHERE category_set_id = p_category_set_id
2580 AND organization_id = p_organization_id
2581 AND inventory_item_id = p_inventory_item_id
2582 AND category_id = p_category_id;
2583
2584 IF (SQL%NOTFOUND) THEN
2585 IF (l_debug = 1) THEN
2586 mdebug('The specified Category Assignment not found');
2587 END IF;
2588 RAISE NO_DATA_FOUND;
2589 END IF;
2590 */
2591 --Ended code for bug 2527058
2592 IF (l_debug = 1) THEN
2593 mdebug('Delete_Category_Assignment: Done!!');
2594 END IF;
2595
2596 --add by geguo business event enhancement 8351807
2597 BEGIN
2598 IF (l_debug = 1) THEN
2599 mdebug('begin Raise EGO_WF_WRAPPER_PVP.G_ITEM_CAT_ASSIGN_EVENT business event');
2600 END IF;
2601
2602 dbms_output.put_line('return status: '|| x_return_status);
2603 IF (x_return_status = fnd_api.g_RET_STS_SUCCESS) THEN
2604 INV_ITEM_EVENTS_PVT.Raise_Events (
2605 p_commit => FND_API.To_Boolean(p_commit)
2606 ,p_event_name => 'EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT'
2607 ,p_dml_type => 'DELETE'
2608 ,p_inventory_item_id => p_inventory_item_id
2609 ,p_organization_id => p_organization_id
2610 ,p_category_set_id => p_category_set_id
2611 ,p_category_id => p_category_id
2612 ,p_old_category_id => null --add by geguo.
2613 );
2614 END IF;
2615 IF (l_debug = 1) THEN
2616 mdebug('end Raise EGO_WF_WRAPPER_PVP.G_ITEM_CAT_ASSIGN_EVENT business event');
2617 END IF;
2618
2619 EXCEPTION
2620 WHEN OTHERS THEN
2621 IF (l_debug = 1) THEN
2622 mdebug('error occured when Raise EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT business event');
2623 END IF;
2624 END;
2625
2626 -- Standard check of p_commit.
2627 IF FND_API.To_Boolean( p_commit ) THEN
2628 COMMIT WORK;
2629 END IF;
2630
2631 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
2632 INV_ITEM_MSG.Write_List;
2633 -- Standard call to get message count and if count is 1,
2634 -- get message info.
2635 -- The client will directly display the x_msg_data (which is already
2636 -- translated) if the x_msg_count = 1;
2637 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
2638 -- Server-side procedure to access the messages, and consolidate them
2639 -- and display (or) to display one message after another.
2640 FND_MSG_PUB.Count_And_Get
2641 ( p_count => x_msg_count,
2642 p_data => x_msg_data
2643 );
2644 EXCEPTION
2645 WHEN FND_API.G_EXC_ERROR THEN
2646 ROLLBACK TO Delete_Category_Assignment_PUB;
2647 x_return_status := FND_API.G_RET_STS_ERROR;
2648 FND_MSG_PUB.Count_And_Get
2649 ( p_count => x_msg_count,
2650 p_data => x_msg_data
2651 );
2652 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2653 ROLLBACK TO Delete_Category_Assignment_PUB;
2654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2655 FND_MSG_PUB.Count_And_Get
2656 ( p_count => x_msg_count,
2657 p_data => x_msg_data
2658 );
2659 WHEN OTHERS THEN
2660 ROLLBACK TO Delete_Category_Assignment_PUB;
2661 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2662 IF FND_MSG_PUB.Check_Msg_Level
2663 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2664 THEN
2665 FND_MSG_PUB.Add_Exc_Msg
2666 ( G_PKG_NAME ,
2667 l_api_name
2668 );
2669 END IF;
2670 FND_MSG_PUB.Count_And_Get
2671 ( p_count => x_msg_count,
2672 p_data => x_msg_data
2673 );
2674
2675 END Delete_Category_Assignment;
2676 -----------------------------------------------------------------------------
2677 -- 7. Get_Category_Rec_Type
2678 ----------------------------------------------------------------------------
2679 FUNCTION Get_Category_Rec_Type
2680 RETURN INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE IS
2681 l_category_rec_type INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE ;
2682 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2683 BEGIN
2684 RETURN l_category_rec_type;
2685 END;
2686
2687 -----------------------------------------------------------------------------
2688 -- 8. Validate_iProcurements_flags
2689 --Bug: 2645153 validating structure and iProcurement flags
2690 ----------------------------------------------------------------------------
2691 PROCEDURE Validate_iProcurements_flags
2692 (
2693 x_category_rec IN INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE
2694 ) IS
2695
2696 l_po_structure_id NUMBER;
2697 --Bug: 2645153 added coide to get purchasing category structure id
2698 CURSOR get_po_structure_id IS
2699 SELECT STRUCTURE_ID
2700 FROM MTL_CATEGORY_SETS MCS,
2701 MTL_DEFAULT_CATEGORY_SETS MDCS
2702 WHERE FUNCTIONAL_AREA_ID = 2
2703 AND MCS.CATEGORY_SET_ID = MDCS.CATEGORY_SET_ID;
2704
2705 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2706 BEGIN
2707 IF (l_debug = 1) THEN
2708 mdebug('checking supplier enabled flag information provided'|| x_category_rec.supplier_enabled_flag);
2709 END IF;
2710 /* Bug 11787356. Comment out following IF condition. We should allow 'Y', 'N' and NULL as the values of SUPPLIER_ENABLED_FLAG.
2711 IF x_category_rec.supplier_enabled_flag NOT IN (g_YES,g_MISS_CHAR) THEN -- g_NO is modifed to g_YES for bug#6278190
2712 fnd_message.set_name('INV','INV_NOT_VALID_FLAG');
2713 fnd_message.set_token('COLUMN_NAME', 'SUPPLIER_ENABLED_FLAG');
2714 fnd_msg_pub.ADD;
2715 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
2716 IF (l_debug = 1) THEN
2717 mdebug('Invalid supplier enabled flag information provided');
2718 END IF;
2719 END IF;*/
2720 IF (l_debug = 1) THEN
2721 mdebug('checking web status flag information provided');
2722 END IF;
2723 /*Bug: 4494727 Commenting out the following IF condition
2724 IF x_category_rec.web_status NOT IN (g_YES,g_MISS_CHAR) THEN
2725 fnd_message.set_name('INV','INV_NOT_VALID_FLAG');
2726 fnd_message.set_token('COLUMN_NAME', 'WEB_STATUS');
2727 fnd_msg_pub.ADD;
2728 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
2729 IF (l_debug = 1) THEN
2730 mdebug('Invalid web status flag information provided');
2731 END IF;
2732 END IF;
2733 */
2734 IF (x_category_rec.supplier_enabled_flag = g_NO) --OR Bug: 4494727
2735 -- (x_category_rec.web_status = g_YES)
2736 THEN
2737 OPEN get_po_structure_id;
2738 FETCH get_po_structure_id INTO l_po_structure_id;
2739 IF (get_po_structure_id%NOTFOUND) THEN
2740 fnd_message.set_name('INV','INV_NO_DEFAULT_CSET');
2741 fnd_msg_pub.ADD;
2742 IF (l_debug = 1) THEN
2743 mdebug('No Default purchasing category set provided');
2744 END IF;
2745 CLOSE get_po_structure_id;
2746 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
2747 ELSE
2748 IF (l_po_structure_id <> x_category_rec.structure_id) THEN
2749 IF (x_category_rec.supplier_enabled_flag = g_NO) THEN
2750 fnd_message.set_name('INV','INV_SUP_ENABLED_PO_CAT_ONLY');
2751 fnd_msg_pub.ADD;
2752 END IF;
2753 /*Bug: 4494727 Commenting out the following IF condition
2754 IF (x_category_rec.web_status = g_YES) THEN
2755 fnd_message.set_name('INV','INV_CAT_ENABLED_PO_CAT_ONLY');
2756 fnd_msg_pub.ADD;
2757 END IF;
2758 */
2759 IF (l_debug = 1) THEN
2760 mdebug('Only purchasing cat can be viewable by supplier');
2761 END IF;
2762 CLOSE get_po_structure_id;
2763 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
2764 END IF;
2765 END IF;
2766 CLOSE get_po_structure_id;
2767 END IF; --if flag = 'Y'
2768 END Validate_iProcurements_flags;
2769
2770 ----------------------------------------------------------------------------
2771 -- 9. Create Valid Category
2772 -- Bug: 3093555
2773 -- API to create a valid Category in Category Sets
2774 ----------------------------------------------------------------------------
2775 PROCEDURE Create_Valid_Category(
2776 p_api_version IN NUMBER,
2777 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2778 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2779 p_category_set_id IN NUMBER,
2780 p_category_id IN NUMBER,
2781 p_parent_category_id IN NUMBER,
2782 x_return_status OUT NOCOPY VARCHAR2,
2783 x_errorcode OUT NOCOPY NUMBER,
2784 x_msg_count OUT NOCOPY NUMBER,
2785 x_msg_data OUT NOCOPY VARCHAR2
2786 ) IS
2787 -- Start OF comments
2788 -- API name : Create_Valid_Category
2789 -- TYPE : Public
2790 -- Pre-reqs : None
2791 -- FUNCTION : Create a record in mtl_category_set_valid_cats.
2792 --
2793 -- Version: Current Version 1.0
2794 -- Previous Version : None
2795 -- Notes : Stub Version
2796 --
2797 -- END OF comments
2798 l_api_name CONSTANT VARCHAR2(30) := 'Create_Valid_Category';
2799 -- On addition of any Required parameters the major version needs
2800 -- to change i.e. for eg. 1.X to 2.X.
2801 -- On addition of any Optional parameters the minor version needs
2802 -- to change i.e. for eg. X.6 to X.7.
2803 l_api_version CONSTANT NUMBER := 1.0;
2804 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2805 -- who column variables
2806 l_user_id mtl_category_set_valid_cats.created_by%TYPE;
2807 l_login_id mtl_category_set_valid_cats.last_update_login%TYPE;
2808 l_request_id mtl_category_set_valid_cats.request_id%TYPE;
2809 l_prog_appl_id mtl_category_set_valid_cats.program_application_id%TYPE;
2810 l_program_id mtl_category_set_valid_cats.program_id%TYPE;
2811 BEGIN
2812 IF l_debug = 1 THEN
2813 mdebug('Create_Valid_Category: Tracing...1');
2814 END IF;
2815 -- Standard Start of API savepoint
2816 IF FND_API.To_Boolean( p_commit ) THEN
2817 SAVEPOINT Create_Valid_Category_PUB;
2818 END IF;
2819 -- Check for call compatibility.
2820 IF NOT FND_API.Compatible_API_Call (l_api_version,
2821 p_api_version,
2822 l_api_name,
2823 G_PKG_NAME) THEN
2824 IF l_debug = 1 THEN
2825 mdebug('Create_Valid_Category: Invalid API Call');
2826 END IF;
2827 RAISE FND_API.g_EXC_ERROR;
2828 END IF;
2829 -- Initialize API message list if necessary.
2830 -- Initialize message list if p_init_msg_list is set to TRUE.
2831 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2832 FND_MSG_PUB.initialize;
2833 END IF;
2834 IF validate_category_set_params
2835 (p_validation_type => G_INSERT
2836 ,p_category_set_id => p_category_set_id
2837 ,p_category_id => p_category_id
2838 ,p_parent_category_id => p_parent_category_id
2839 ,p_calling_api => l_api_name
2840 ) THEN
2841 IF l_debug = 1 THEN
2842 mdebug('Create_Valid_Category: Inserting data into category sets ');
2843 END IF;
2844 l_user_id := fnd_global.user_id;
2845 l_login_id := fnd_global.login_id;
2846 IF l_login_id = -1 THEN
2847 l_login_id := fnd_global.conc_login_id;
2848 END IF;
2849 l_request_id := fnd_global.conc_request_id;
2850 l_prog_appl_id := fnd_global.prog_appl_id;
2851 l_program_id := fnd_global.conc_program_id;
2852 INSERT INTO mtl_category_set_valid_cats
2853 ( category_set_id
2854 , category_id
2855 , parent_category_id
2856 , created_by
2857 , creation_date
2858 , last_updated_by
2859 , last_update_date
2860 , last_update_login
2861 , request_id
2862 , program_application_id
2863 , program_id
2864 , program_update_date
2865 )
2866 VALUES
2867 ( p_category_set_id
2868 , p_category_id
2869 , p_parent_category_id
2870 , l_user_id
2871 , SYSDATE
2872 , l_user_id
2873 , SYSDATE
2874 , l_login_id
2875 , l_request_id
2876 , l_prog_appl_id
2877 , l_program_id
2878 , SYSDATE
2879 );
2880 ELSE
2881 -- passed parameters are invalid
2882 RAISE FND_API.G_EXC_ERROR;
2883 END IF;
2884
2885 -- Standard check of p_commit.
2886 IF FND_API.To_Boolean( p_commit ) THEN
2887 COMMIT WORK;
2888 END IF;
2889
2890 x_return_status := FND_API.G_RET_STS_SUCCESS;
2891 -- Standard call to get message count and if count is 1,
2892 -- get message info.
2893 -- The client will directly display the x_msg_data (which is already
2894 -- translated) if the x_msg_count = 1;
2895 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
2896 -- Server-side procedure to access the messages, and consolidate them
2897 -- and display (or) to display one message after another.
2898 FND_MSG_PUB.Count_And_Get
2899 ( p_count => x_msg_count,
2900 p_data => x_msg_data
2901 );
2902
2903 EXCEPTION
2904 WHEN FND_API.G_EXC_ERROR THEN
2905 IF l_debug = 1 THEN
2906 mdebug('Create_Valid_Category: Apps Exception raised');
2907 END IF;
2908 IF FND_API.To_Boolean( p_commit ) THEN
2909 ROLLBACK TO Create_Valid_Category_PUB;
2910 END IF;
2911 x_return_status := FND_API.G_RET_STS_ERROR;
2912 FND_MSG_PUB.Count_And_Get
2913 ( p_count => x_msg_count,
2914 p_data => x_msg_data
2915 );
2916 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2917 IF l_debug = 1 THEN
2918 mdebug('Create_Valid_Category: Apps Unexpected Error');
2919 END IF;
2920 IF FND_API.To_Boolean( p_commit ) THEN
2921 ROLLBACK TO Create_Valid_Category_PUB;
2922 END IF;
2923 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2924 FND_MSG_PUB.Count_And_Get
2925 ( p_count => x_msg_count,
2926 p_data => x_msg_data
2927 );
2928 WHEN OTHERS THEN
2929 IF l_debug = 1 THEN
2930 mdebug('Create_Valid_Category: Exception -- OTHERS ');
2931 END IF;
2932 IF FND_API.To_Boolean( p_commit ) THEN
2933 ROLLBACK TO Create_Valid_Category_PUB;
2934 END IF;
2935 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2936 IF FND_MSG_PUB.Check_Msg_Level
2937 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2938 THEN
2939 FND_MSG_PUB.Add_Exc_Msg
2940 ( G_PKG_NAME ,
2941 l_api_name
2942 );
2943 END IF;
2944 FND_MSG_PUB.Count_And_Get
2945 ( p_count => x_msg_count,
2946 p_data => x_msg_data
2947 );
2948 END Create_Valid_Category;
2949
2950 ----------------------------------------------------------------------------
2951 -- 10. Update Category
2952 -- Bug: 3093555
2953 -- API to update a valid Category
2954 ----------------------------------------------------------------------------
2955 PROCEDURE Update_Valid_Category(
2956 p_api_version IN NUMBER,
2957 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2958 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2959 p_category_set_id IN NUMBER,
2960 p_category_id IN NUMBER,
2961 p_parent_category_id IN NUMBER,
2962 x_return_status OUT NOCOPY VARCHAR2,
2963 x_errorcode OUT NOCOPY NUMBER,
2964 x_msg_count OUT NOCOPY NUMBER,
2965 x_msg_data OUT NOCOPY VARCHAR2
2966 ) IS
2967 -- Start OF comments
2968 -- API name : Update_Valid_Category
2969 -- TYPE : Public
2970 -- Pre-reqs : None
2971 -- FUNCTION : Update record in mtl_category_set_valid_cats.
2972 --
2973 -- Version: Current Version 1.0
2974 -- Previous Version : None
2975 -- Notes : Stub Version
2976 --
2977 -- END OF comments
2978 l_api_name CONSTANT VARCHAR2(30) := 'Update_Valid_Category';
2979 -- On addition of any Required parameters the major version needs
2980 -- to change i.e. for eg. 1.X to 2.X.
2981 -- On addition of any Optional parameters the minor version needs
2982 -- to change i.e. for eg. X.6 to X.7.
2983 l_api_version CONSTANT NUMBER := 1.0;
2984 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2985 l_user_id mtl_category_set_valid_cats.created_by%TYPE;
2986 l_login_id mtl_category_set_valid_cats.last_update_login%TYPE;
2987 BEGIN
2988 IF l_debug = 1 THEN
2989 mdebug('Update_Valid_Category: Tracing...1');
2990 END IF;
2991 -- Standard Start of API savepoint
2992 IF FND_API.To_Boolean( p_commit ) THEN
2993 SAVEPOINT Update_Valid_Category_PUB;
2994 END IF;
2995 -- Check for call compatibility.
2996 IF NOT FND_API.Compatible_API_Call (l_api_version,
2997 p_api_version,
2998 l_api_name,
2999 G_PKG_NAME) THEN
3000 IF l_debug = 1 THEN
3001 mdebug('Update_Valid_Category: Invalid API call');
3002 END IF;
3003 RAISE FND_API.g_EXC_ERROR;
3004 END IF;
3005 -- Initialize API message list if necessary.
3006 -- Initialize message list if p_init_msg_list is set to TRUE.
3007 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3008 FND_MSG_PUB.initialize;
3009 END IF;
3010
3011 IF validate_category_set_params
3012 (p_validation_type => G_UPDATE
3013 ,p_category_set_id => p_category_set_id
3014 ,p_category_id => p_category_id
3015 ,p_parent_category_id => p_parent_category_id
3016 ,p_calling_api => l_api_name
3017 ) THEN
3018 l_user_id := fnd_global.user_id;
3019 l_login_id := fnd_global.login_id;
3020 IF l_login_id = -1 THEN
3021 l_login_id := fnd_global.conc_login_id;
3022 END IF;
3023 IF l_debug = 1 THEN
3024 mdebug('Update_Valid_Category: About to update the category record');
3025 END IF;
3026 UPDATE mtl_category_set_valid_cats
3027 SET parent_category_id = p_parent_category_id
3028 ,last_updated_by = l_user_id
3029 ,last_update_date = SYSDATE
3030 ,last_update_login = l_login_id
3031 WHERE category_set_id = p_category_set_id
3032 AND category_id = p_category_id;
3033 IF (SQL%NOTFOUND) THEN
3034 IF l_debug = 1 THEN
3035 mdebug('Update_Valid_Category: Record not available for update');
3036 END IF;
3037 fnd_message.set_name('INV','INV_CATEGORY_UNAVAIL_UPDATE');
3038 fnd_msg_pub.ADD;
3039 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
3040 END IF;
3041 ELSE
3042 -- passed parameters are invalid
3043 RAISE FND_API.G_EXC_ERROR;
3044 END IF;
3045 -- Standard check of p_commit.
3046 IF FND_API.To_Boolean( p_commit ) THEN
3047 COMMIT WORK;
3048 END IF;
3049
3050 x_return_status := FND_API.G_RET_STS_SUCCESS;
3051 -- Standard call to get message count and if count is 1,
3052 -- get message info.
3053 -- The client will directly display the x_msg_data (which is already
3054 -- translated) if the x_msg_count = 1;
3055 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
3056 -- Server-side procedure to access the messages, and consolidate them
3057 -- and display (or) to display one message after another.
3058 FND_MSG_PUB.Count_And_Get
3059 ( p_count => x_msg_count,
3060 p_data => x_msg_data
3061 );
3062
3063 EXCEPTION
3064 WHEN FND_API.G_EXC_ERROR THEN
3065 IF l_debug = 1 THEN
3066 mdebug('Update_Valid_Category: Apps Exception raised');
3067 END IF;
3068 IF FND_API.To_Boolean( p_commit ) THEN
3069 ROLLBACK TO Update_Valid_Category_PUB;
3070 END IF;
3071 x_return_status := FND_API.G_RET_STS_ERROR;
3072 FND_MSG_PUB.Count_And_Get
3073 ( p_count => x_msg_count,
3074 p_data => x_msg_data
3075 );
3076 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3077 IF l_debug = 1 THEN
3078 mdebug('Update_Valid_Category: Apps Unexpected Error');
3079 END IF;
3080 IF FND_API.To_Boolean( p_commit ) THEN
3081 ROLLBACK TO Update_Valid_Category_PUB;
3082 END IF;
3083 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3084 FND_MSG_PUB.Count_And_Get
3085 ( p_count => x_msg_count,
3086 p_data => x_msg_data
3087 );
3088 WHEN OTHERS THEN
3089 IF l_debug = 1 THEN
3090 mdebug('Update_Valid_Category: Exception -- OTHERS ');
3091 END IF;
3092 IF FND_API.To_Boolean( p_commit ) THEN
3093 ROLLBACK TO Update_Valid_Category_PUB;
3094 END IF;
3095 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3096 IF FND_MSG_PUB.Check_Msg_Level
3097 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3098 THEN
3099 FND_MSG_PUB.Add_Exc_Msg
3100 ( G_PKG_NAME ,
3101 l_api_name
3102 );
3103 END IF;
3104 FND_MSG_PUB.Count_And_Get
3105 ( p_count => x_msg_count,
3106 p_data => x_msg_data
3107 );
3108 END Update_Valid_Category;
3109
3110 ----------------------------------------------------------------------------
3111 -- 11. Delete Category
3112 -- Bug: 3093555
3113 -- API to Delete a valid Category
3114 ----------------------------------------------------------------------------
3115 PROCEDURE Delete_Valid_Category(
3116 p_api_version IN NUMBER,
3117 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3118 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3119 p_category_set_id IN NUMBER,
3120 p_category_id IN NUMBER,
3121 x_return_status OUT NOCOPY VARCHAR2,
3122 x_errorcode OUT NOCOPY NUMBER,
3123 x_msg_count OUT NOCOPY NUMBER,
3124 x_msg_data OUT NOCOPY VARCHAR2
3125 ) IS
3126 -- Start OF comments
3127 -- API name : Delete_Valid_Category
3128 -- TYPE : Public
3129 -- Pre-reqs : None
3130 -- FUNCTION : Delete the record from mtl_category_set_valid_cats.
3131 --
3132 -- Version: Current Version 1.0
3133 -- Previous Version : None
3134 -- Notes : Stub Version
3135 --
3136 -- END OF comments
3137 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Valid_Category';
3138 -- On addition of any Required parameters the major version needs
3139 -- to change i.e. for eg. 1.X to 2.X.
3140 -- On addition of any Optional parameters the minor version needs
3141 -- to change i.e. for eg. X.6 to X.7.
3142 l_api_version CONSTANT NUMBER := 1.0;
3143 l_count NUMBER;
3144 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3145 l_description mtl_categories_vl.description%TYPE;
3146 l_category_id mtl_category_set_valid_cats.category_id%TYPE;
3147 l_def_category_id mtl_category_sets_b.default_category_id%TYPE;
3148 l_hrchy_enabled mtl_category_sets_b.hierarchy_enabled%TYPE;
3149
3150 CURSOR c_get_cat_desc (cp_category_id IN NUMBER) IS
3151 SELECT description
3152 FROM mtl_categories_vl
3153 WHERE category_id = cp_category_id;
3154
3155 --Added for bug 5219692
3156 CURSOR c_get_items_in_cat (cp_category_id IN NUMBER
3157 ,cp_category_set_id IN NUMBER) IS
3158 SELECT category_id
3159 FROM mtl_item_categories item_cat
3160 WHERE item_cat.category_id = cp_category_id
3161 AND item_cat.category_set_id = cp_category_set_id
3162 AND rownum = 1;
3163
3164 CURSOR c_get_items_in_cat_hrchy (cp_category_id IN NUMBER
3165 ,cp_category_set_id IN NUMBER) IS
3166 SELECT valid_cats.category_id
3167 FROM mtl_category_set_valid_cats valid_cats
3168 WHERE EXISTS
3169 (SELECT 'X'
3170 FROM mtl_item_categories item_cat
3171 WHERE item_cat.category_id = valid_cats.category_id
3172 AND item_cat.category_set_id = cp_category_set_id
3173 )
3174 CONNECT BY PRIOR
3175 valid_cats.category_id = valid_cats.parent_category_id
3176 AND valid_cats.category_set_id = cp_category_set_id
3177 START WITH
3178 valid_cats.category_id = cp_category_id
3179 AND category_set_id = cp_category_set_id
3180 AND rownum = 1;
3181
3182 --Added for bug 5219692
3183 CURSOR c_check_default_cat (cp_category_id IN NUMBER
3184 ,cp_category_set_id IN NUMBER) IS
3185 SELECT cat_sets.default_category_id
3186 FROM mtl_category_sets_b cat_sets
3187 WHERE cat_sets.category_set_id = p_category_set_id
3188 AND cat_sets.default_category_id = p_category_id
3189 AND NVL(cat_sets.validate_flag,'N') = 'Y';
3190
3191 CURSOR c_check_default_cat_hrchy (cp_category_id IN NUMBER
3192 ,cp_category_set_id IN NUMBER) IS
3193 SELECT cat_sets.default_category_id
3194 FROM mtl_category_sets_b cat_sets
3195 WHERE cat_sets.category_set_id = p_category_set_id
3196 AND EXISTS
3197 (SELECT 'X'
3198 FROM mtl_category_set_valid_cats check_cats
3199 WHERE check_cats.category_id = cat_sets.default_category_id
3200 CONNECT BY PRIOR
3201 check_cats.category_id = check_cats.parent_category_id
3202 AND check_cats.category_set_id = cp_category_set_id
3203 START WITH
3204 check_cats.category_id = cp_category_id
3205 AND check_cats.category_set_id = cp_category_set_id
3206 )
3207 AND NVL(cat_sets.validate_flag,'N') = 'Y';
3208
3209
3210 BEGIN
3211 IF l_debug = 1 THEN
3212 mdebug('Delete_Valid_Category: Tracing...1');
3213 END IF;
3214 -- Standard Start of API savepoint
3215 IF FND_API.To_Boolean( p_commit ) THEN
3216 SAVEPOINT Delete_Valid_Category_PUB;
3217 END IF;
3218 -- Check for call compatibility.
3219 IF NOT FND_API.Compatible_API_Call (l_api_version,
3220 p_api_version,
3221 l_api_name,
3222 G_PKG_NAME) THEN
3223 IF l_debug = 1 THEN
3224 mdebug('Delete_Valid_Category: Invalid API call');
3225 END IF;
3226 RAISE FND_API.G_EXC_ERROR;
3227 END IF;
3228 -- Initialize API message list if necessary.
3229 -- Initialize message list if p_init_msg_list is set to TRUE.
3230 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3231 FND_MSG_PUB.initialize;
3232 END IF;
3233
3234 IF (p_category_set_id IS NULL OR p_category_id IS NULL) THEN
3235 IF l_debug = 1 THEN
3236 mdebug('Delete_Valid_Category: Mandatory parameters missing');
3237 END IF;
3238 fnd_message.set_name('INV','INV_MISSING_REQUIRED_PARAMETER');
3239 fnd_msg_pub.ADD;
3240 RAISE fnd_api.G_EXC_ERROR;
3241 END IF;
3242
3243 IF NOT get_category_set_type(p_category_set_id => p_category_set_id
3244 ,p_category_id => p_category_id
3245 ,x_hrchy_enabled => l_hrchy_enabled) THEN
3246 IF l_debug = 1 THEN
3247 mdebug('Delete_Valid_Category: Record not available for deletion');
3248 END IF;
3249 fnd_message.set_name('INV','INV_CATEGORY_UNAVAIL_DELETE');
3250 fnd_msg_pub.ADD;
3251 RAISE fnd_api.g_EXC_ERROR;
3252 END IF;
3253
3254 -- check if the user tries to delete default cateogy of the category set
3255 IF UPPER(l_hrchy_enabled) = 'Y' THEN
3256 OPEN c_check_default_cat_hrchy (cp_category_id => p_category_id
3257 ,cp_category_set_id => p_category_set_id);
3258 FETCH c_check_default_cat_hrchy INTO l_def_category_id;
3259 IF c_check_default_cat_hrchy%NOTFOUND THEN
3260 l_def_category_id := NULL;
3261 END IF;
3262 CLOSE c_check_default_cat_hrchy;
3263 ELSE
3264 OPEN c_check_default_cat(cp_category_id => p_category_id
3265 ,cp_category_set_id => p_category_set_id);
3266 FETCH c_check_default_cat INTO l_def_category_id;
3267 IF c_check_default_cat%NOTFOUND THEN
3268 l_def_category_id := NULL;
3269 END IF;
3270 CLOSE c_check_default_cat;
3271 END IF;
3272
3273 IF l_def_category_id IS NOT NULL THEN
3274 -- default category is in the hierarchy
3275 IF l_debug = 1 THEN
3276 mdebug('Delete_Valid_Category: Cannot delete default category');
3277 END IF;
3278 OPEN c_get_cat_desc (cp_category_id => l_def_category_id);
3279 FETCH c_get_cat_desc INTO l_description;
3280 IF c_get_cat_desc%NOTFOUND THEN
3281 l_description := NULL;
3282 END IF;
3283 fnd_message.set_name('INV','INV_DELETE_DEF_CAT_ERR');
3284 fnd_message.set_token('CATEGORY_NAME', l_description);
3285 fnd_msg_pub.ADD;
3286 RAISE fnd_api.G_EXC_ERROR;
3287 END IF;
3288
3289 -- check if there are any items associated to the category / category set
3290 IF UPPER(l_hrchy_enabled) = 'Y' THEN
3291 OPEN c_get_items_in_cat_hrchy (cp_category_id => p_category_id
3292 ,cp_category_set_id => p_category_set_id);
3293 FETCH c_get_items_in_cat_hrchy INTO l_category_id;
3294 IF c_get_items_in_cat_hrchy%NOTFOUND THEN
3295 l_category_id := NULL;
3296 END IF;
3297 CLOSE c_get_items_in_cat_hrchy;
3298 ELSE
3299 OPEN c_get_items_in_cat (cp_category_id => p_category_id
3300 ,cp_category_set_id => p_category_set_id);
3301 FETCH c_get_items_in_cat INTO l_category_id;
3302 IF c_get_items_in_cat%NOTFOUND THEN
3303 l_category_id := NULL;
3304 END IF;
3305 CLOSE c_get_items_in_cat;
3306 END IF;
3307
3308 IF l_category_id IS NULL THEN
3309 IF l_debug = 1 THEN
3310 mdebug('Delete_Valid_Category: No items associated! Delete now');
3311 END IF;
3312
3313 IF UPPER(l_hrchy_enabled) = 'Y' THEN
3314 DELETE mtl_category_set_valid_cats delete_cats
3315 WHERE category_set_id = p_category_set_id
3316 AND EXISTS
3317 (SELECT 'X'
3318 FROM mtl_category_set_valid_cats
3319 WHERE category_id = delete_cats.category_id
3320 CONNECT BY PRIOR category_id = parent_category_id
3321 AND category_set_id = p_category_set_id
3322 START WITH category_id = p_category_id
3323 AND category_set_id = p_category_set_id
3324 );
3325 ELSE --Added else part for bug 5219692
3326 DELETE mtl_category_set_valid_cats delete_cats
3327 WHERE category_set_id = p_category_set_id
3328 AND category_id = p_category_id;
3329
3330 END IF;
3331 ELSE
3332 IF l_debug = 1 THEN
3333 mdebug('Delete_Valid_Category: Items ASSOCIATED!! ');
3334 END IF;
3335 OPEN c_get_cat_desc (cp_category_id => l_def_category_id);
3336 FETCH c_get_cat_desc INTO l_description;
3337 IF c_get_cat_desc%NOTFOUND THEN
3338 l_description := NULL;
3339 END IF;
3340 fnd_message.set_name('INV','INV_CATEGORY_ITEMS_EXIST');
3341 fnd_message.set_token('CATEGORY_NAME', l_description);
3342 fnd_msg_pub.ADD;
3343 RAISE FND_API.G_EXC_ERROR;
3344 END IF;
3345
3346 -- Standard check of p_commit.
3347 IF FND_API.To_Boolean( p_commit ) THEN
3348 COMMIT WORK;
3349 END IF;
3350
3351 x_return_status := FND_API.G_RET_STS_SUCCESS;
3352 -- Standard call to get message count and if count is 1,
3353 -- get message info.
3354 -- The client will directly display the x_msg_data (which is already
3355 -- translated) if the x_msg_count = 1;
3356 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
3357 -- Server-side procedure to access the messages, and consolidate them
3358 -- and display (or) to display one message after another.
3359 FND_MSG_PUB.Count_And_Get
3360 ( p_count => x_msg_count,
3361 p_data => x_msg_data
3362 );
3363
3364 EXCEPTION
3365 WHEN FND_API.G_EXC_ERROR THEN
3366 IF l_debug = 1 THEN
3367 mdebug('Delete_Valid_Category: Apps Exception raised');
3368 END IF;
3369 IF FND_API.To_Boolean( p_commit ) THEN
3370 ROLLBACK TO Delete_Valid_Category_PUB;
3371 END IF;
3372 x_return_status := FND_API.G_RET_STS_ERROR;
3373 FND_MSG_PUB.Count_And_Get
3374 ( p_count => x_msg_count,
3375 p_data => x_msg_data
3376 );
3377 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3378 IF l_debug = 1 THEN
3379 mdebug('Delete_Valid_Category: Apps Unexpected Error');
3380 END IF;
3381 IF FND_API.To_Boolean( p_commit ) THEN
3382 ROLLBACK TO Delete_Valid_Category_PUB;
3383 END IF;
3384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3385 FND_MSG_PUB.Count_And_Get
3386 ( p_count => x_msg_count,
3387 p_data => x_msg_data
3388 );
3389 WHEN OTHERS THEN
3390 IF l_debug = 1 THEN
3391 mdebug('Delete_Valid_Category: Exception -- OTHERS ');
3392 END IF;
3393 IF FND_API.To_Boolean( p_commit ) THEN
3394 ROLLBACK TO Delete_Valid_Category_PUB;
3395 END IF;
3396 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3397 IF c_get_items_in_cat%ISOPEN THEN
3398 CLOSE c_get_items_in_cat;
3399 END IF;
3400 IF FND_MSG_PUB.Check_Msg_Level
3401 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3402 THEN
3403 FND_MSG_PUB.Add_Exc_Msg
3404 ( G_PKG_NAME ,
3405 l_api_name
3406 );
3407 END IF;
3408 FND_MSG_PUB.Count_And_Get
3409 ( p_count => x_msg_count,
3410 p_data => x_msg_data
3411 );
3412 END Delete_Valid_Category;
3413
3414 ----------------------------------------------------------------------------
3415 -- 12. Process_dml_on_row
3416 -- Bug: 5023883, Create/Update/Delete to the EGO tables
3417 ----------------------------------------------------------------------------
3418 PROCEDURE Process_Dml_On_Row
3419 (
3420 p_api_version IN NUMBER,
3421 p_category_set_id IN NUMBER,
3422 p_category_id IN NUMBER,
3423 p_mode IN VARCHAR2,
3424 x_return_status OUT NOCOPY VARCHAR2,
3425 x_errorcode OUT NOCOPY NUMBER,
3426 x_msg_count OUT NOCOPY NUMBER,
3427 x_msg_data OUT NOCOPY VARCHAR2
3428 ) IS
3429
3430 l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3431 l_data_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3432 l_attr_group_id NUMBER;
3433
3434 BEGIN
3435
3436 /*Initialize the PK column array and the attribute data array */
3437 l_pk_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
3438 EGO_COL_NAME_VALUE_PAIR_OBJ('CATEGORY_SET_ID',
3439 p_category_set_id));
3440
3441 l_data_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
3442 EGO_COL_NAME_VALUE_PAIR_OBJ('CATEGORY_ID', p_category_id));
3443
3444 EGO_USER_ATTRS_DATA_PVT.Perform_DML_On_Row(
3445 p_api_version => 1.0
3446 ,p_object_name => 'EGO_CATEGORY_SET'
3447 ,p_application_id => 431
3448 ,p_attr_group_type => 'EGO_PRODUCT_CATEGORY_SET'
3449 ,p_attr_group_name => 'SalesAndMarketing'
3450 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
3451 ,p_class_code_name_value_pairs => NULL
3452 ,p_data_level_name_value_pairs => l_data_column_name_value_pairs
3453 ,p_attr_name_value_pairs => null
3454 ,p_mode => p_mode
3455 ,p_use_def_vals_on_insert => FND_API.G_TRUE
3456 ,x_return_status => x_return_status
3457 ,x_errorcode => x_errorcode
3458 ,x_msg_count => x_msg_count
3459 ,x_msg_data => x_msg_data );
3460 EXCEPTION
3461
3462 WHEN OTHERS THEN
3463 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3464 x_msg_data := 'Executing - '||G_PKG_NAME||'.Process_Dml_On_Row '||SQLERRM;
3465
3466 END Process_Dml_On_Row;
3467
3468 --* Procedure Update_Category_Assignment added for Bug #3991044
3469 ----------------------------------------------------------------------------
3470 -- 13. Update Category Assignment
3471 -- Bug: 3991044
3472 -- API to Update a valid Item Category Assignment
3473 -- All the validations are taken care in the Pvt pkg,
3474 -- so calling private pkg instead.
3475 ----------------------------------------------------------------------------
3476 PROCEDURE Update_Category_Assignment
3477 (
3478 p_api_version IN NUMBER,
3479 p_init_msg_list IN VARCHAR2 ,
3480 p_commit IN VARCHAR2 ,
3481 p_category_id IN NUMBER,
3482 p_old_category_id IN NUMBER,
3483 p_category_set_id IN NUMBER,
3484 p_inventory_item_id IN NUMBER,
3485 p_organization_id IN NUMBER,
3486 x_return_status OUT NOCOPY VARCHAR2,
3487 x_errorcode OUT NOCOPY NUMBER,
3488 x_msg_count OUT NOCOPY NUMBER,
3489 x_msg_data OUT NOCOPY VARCHAR2
3490 )
3491 IS
3492 -- Start OF comments
3493 -- API name : Delete_Category_Assignment
3494 -- TYPE : Public
3495 -- Pre-reqs : None
3496 -- FUNCTION : Delete an item category assignment.
3497 --
3498 -- Version: Current Version 0.1
3499 -- Previous Version : None
3500 -- Notes : Stub Version
3501 --
3502 -- END OF comments
3503 l_api_name CONSTANT VARCHAR2(30) := 'Update_Category_Assignment';
3504 -- On addition of any Required parameters the major version needs
3505 -- to change i.e. for eg. 1.X to 2.X.
3506 -- On addition of any Optional parameters the minor version needs
3507 -- to change i.e. for eg. X.6 to X.7.
3508 l_api_version CONSTANT NUMBER := 1.0;
3509 l_row_count NUMBER;
3510 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3511 BEGIN
3512 -- Standard Start of API savepoint
3513 SAVEPOINT Update_Category_Assignment_PUB;
3514
3515 -- Check for call compatibility.
3516 IF NOT FND_API.Compatible_API_Call (l_api_version,
3517 p_api_version ,
3518 l_api_name ,
3519 G_PKG_NAME)
3520 THEN
3521 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3522 END IF;
3523 -- Initialize API message list if necessary.
3524 -- Initialize message list if p_init_msg_list is set to TRUE.
3525 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3526 FND_MSG_PUB.initialize;
3527 END IF;
3528
3529 INV_ITEM_MSG.set_Message_Mode('PLSQL');
3530
3531 IF FND_MSG_PUB.Check_Msg_Level
3532 (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
3533 THEN
3534 INV_ITEM_MSG.set_Message_Level(INV_ITEM_MSG.g_Level_Warning);
3535 END IF;
3536
3537 INV_ITEM_CATEGORY_PVT.Update_Category_Assignment
3538 (
3539 p_api_version => p_api_version
3540 , p_init_msg_list => p_init_msg_list
3541 , p_commit => p_commit
3542 , p_inventory_item_id => p_inventory_item_id
3543 , p_organization_id => p_organization_id
3544 , p_category_set_id => p_category_set_id
3545 , p_category_id => p_category_id
3546 , p_old_category_id => p_old_category_id
3547 , x_return_status => x_return_status
3548 , x_msg_count => x_msg_count
3549 , x_msg_data => x_msg_data
3550 );
3551
3552
3553 IF (l_debug = 1) THEN
3554 mdebug('Update_Category_Assignment: Done!!');
3555 END IF;
3556
3557 --add by geguo business event enhancement 8351807
3558 BEGIN
3559 IF (l_debug = 1) THEN
3560 mdebug('begin Raise EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT business event');
3561 END IF;
3562 IF (x_return_status = fnd_api.g_RET_STS_SUCCESS) THEN
3563 INV_ITEM_EVENTS_PVT.Raise_Events (
3564 p_commit => FND_API.To_Boolean(p_commit)
3565 ,p_event_name => 'EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT'
3566 ,p_dml_type => 'UPDATE'
3567 ,p_inventory_item_id => p_inventory_item_id
3568 ,p_organization_id => p_organization_id
3569 ,p_category_set_id => p_category_set_id
3570 ,p_category_id => p_category_id
3571 ,p_old_category_id => p_old_category_id
3572 );
3573 END IF;
3574
3575 IF (l_debug = 1) THEN
3576 mdebug('end Raise EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT business event');
3577 END IF;
3578
3579 EXCEPTION
3580 WHEN OTHERS THEN
3581 IF (l_debug = 1) THEN
3582 mdebug('error occured when Raise EGO_WF_WRAPPER_PVT.G_ITEM_CAT_ASSIGN_EVENT business event');
3583 END IF;
3584 END;
3585 -- Standard check of p_commit.
3586 IF FND_API.To_Boolean( p_commit ) THEN
3587 COMMIT WORK;
3588 END IF;
3589
3590 --- Bug 6272365 Start
3591 --- x_return_status := FND_API.G_RET_STS_SUCCESS;
3592 INV_ITEM_MSG.Write_List;
3593 --- Bug 6272365 End
3594 -- Standard call to get message count and if count is 1,
3595 -- get message info.
3596 -- The client will directly display the x_msg_data (which is already
3597 -- translated) if the x_msg_count = 1;
3598 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
3599 -- Server-side procedure to access the messages, and consolidate them
3600 -- and display (or) to display one message after another.
3601 FND_MSG_PUB.Count_And_Get
3602 ( p_count => x_msg_count,
3603 p_data => x_msg_data
3604 );
3605 EXCEPTION
3606 WHEN FND_API.G_EXC_ERROR THEN
3607 ROLLBACK TO Update_Category_Assignment_PUB;
3608 x_return_status := FND_API.G_RET_STS_ERROR;
3609 FND_MSG_PUB.Count_And_Get
3610 ( p_count => x_msg_count,
3611 p_data => x_msg_data
3612 );
3613 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3614 ROLLBACK TO Update_Category_Assignment_PUB;
3615 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3616 FND_MSG_PUB.Count_And_Get
3617 ( p_count => x_msg_count,
3618 p_data => x_msg_data
3619 );
3620 WHEN OTHERS THEN
3621 ROLLBACK TO Update_Category_Assignment_PUB;
3622 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3623 IF FND_MSG_PUB.Check_Msg_Level
3624 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3625 THEN
3626 FND_MSG_PUB.Add_Exc_Msg
3627 ( G_PKG_NAME ,
3628 l_api_name
3629 );
3630 END IF;
3631 FND_MSG_PUB.Count_And_Get
3632 ( p_count => x_msg_count,
3633 p_data => x_msg_data
3634 );
3635
3636 END Update_Category_Assignment;
3637 --* End of code for Bug #3991044
3638
3639 /* Add this procedure by geguo for bug 8547305 */
3640 PROCEDURE Get_Category_Id_From_Cat_Rec(
3641 p_category_rec IN INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE,
3642 x_category_id OUT NOCOPY NUMBER,
3643 x_return_status OUT NOCOPY VARCHAR2,
3644 x_msg_data OUT NOCOPY VARCHAR2
3645 )IS
3646 l_category_rec INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE;
3647 l_category_id NUMBER;
3648 l_structure_id NUMBER;
3649 l_success BOOLEAN;
3650 l_concat_segs VARCHAR2(2000) ;
3651 l_n_segments NUMBER ;
3652 l_segment_array FND_FLEX_EXT.SegmentArray;
3653 l_delim VARCHAR2(10);
3654 l_indx NUMBER;
3655 l_msg_text VARCHAR2(1000);
3656
3657 CURSOR segment_count(p_structure_id NUMBER) IS
3658 SELECT count(segment_num)
3659 FROM fnd_id_flex_segments
3660 WHERE application_id = G_INVENTORY_APP_ID
3661 AND id_flex_code = G_CAT_FLEX_CODE
3662 AND id_flex_num = p_structure_id
3663 AND (enabled_flag = 'Y' OR NVL(g_eni_upgarde_flag,'N') = 'Y');-- Added for 11.5.10 ENI Upgrade
3664
3665 CURSOR c_get_segments(cp_flex_num NUMBER) IS
3666 SELECT application_column_name,rownum
3667 FROM fnd_id_flex_segments
3668 WHERE application_id = 401
3669 AND id_flex_code = 'MCAT'
3670 AND id_flex_num = cp_flex_num
3671 AND enabled_flag = 'Y'
3672 ORDER BY segment_num ASC;
3673
3674 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3675
3676 BEGIN
3677 --Pre-Process the passed in category record.
3678 Preprocess_Category_Rec(G_INSERT, p_category_rec, l_category_rec) ;
3679
3680 l_structure_id := l_category_rec.structure_id;
3681 OPEN segment_count(l_structure_id);
3682 FETCH segment_count INTO l_n_segments;
3683 IF (segment_count%NOTFOUND) THEN
3684 IF (l_debug = 1) THEN
3685 mdebug('The Number of segments not found');
3686 END IF;
3687 END IF;
3688 CLOSE segment_count;
3689
3690 l_delim := fnd_flex_ext.get_delimiter(G_INVENTORY_APP_SHORT_NAME,
3691 G_CAT_FLEX_CODE,
3692 l_structure_id);
3693 IF l_delim is NULL then
3694 fnd_message.set_name('OFA','FA_BUDGET_NO_SEG_DELIM');
3695 fnd_msg_pub.ADD;
3696 IF (l_debug = 1) THEN
3697 mdebug('Delimiter is NULL...Error');
3698 END IF;
3699 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3700 END IF;
3701
3702 l_indx := 1;
3703 FOR c_segments in c_get_segments(l_structure_id) LOOP
3704 IF c_segments.application_column_name = 'SEGMENT1' THEN
3705 l_segment_array(l_indx):= l_category_rec.segment1;
3706 ELSIF c_segments.application_column_name = 'SEGMENT2' THEN
3707 l_segment_array(l_indx):= l_category_rec.segment2;
3708 ELSIF c_segments.application_column_name = 'SEGMENT3' THEN
3709 l_segment_array(l_indx):= l_category_rec.segment3;
3710 ELSIF c_segments.application_column_name = 'SEGMENT4' THEN
3711 l_segment_array(l_indx):= l_category_rec.segment4;
3712 ELSIF c_segments.application_column_name = 'SEGMENT5' THEN
3713 l_segment_array(l_indx):= l_category_rec.segment5;
3714 ELSIF c_segments.application_column_name = 'SEGMENT6' THEN
3715 l_segment_array(l_indx):= l_category_rec.segment6;
3716 ELSIF c_segments.application_column_name = 'SEGMENT7' THEN
3717 l_segment_array(l_indx):= l_category_rec.segment7;
3718 ELSIF c_segments.application_column_name = 'SEGMENT8' THEN
3719 l_segment_array(l_indx):= l_category_rec.segment8;
3720 ELSIF c_segments.application_column_name = 'SEGMENT9' THEN
3721 l_segment_array(l_indx):= l_category_rec.segment9;
3722 ELSIF c_segments.application_column_name = 'SEGMENT10' THEN
3723 l_segment_array(l_indx):= l_category_rec.segment10;
3724 ELSIF c_segments.application_column_name = 'SEGMENT11' THEN
3725 l_segment_array(l_indx):= l_category_rec.segment11;
3726 ELSIF c_segments.application_column_name = 'SEGMENT12' THEN
3727 l_segment_array(l_indx):= l_category_rec.segment12;
3728 ELSIF c_segments.application_column_name = 'SEGMENT13' THEN
3729 l_segment_array(l_indx):= l_category_rec.segment13;
3730 ELSIF c_segments.application_column_name = 'SEGMENT14' THEN
3731 l_segment_array(l_indx):= l_category_rec.segment14;
3732 ELSIF c_segments.application_column_name = 'SEGMENT15' THEN
3733 l_segment_array(l_indx):= l_category_rec.segment15;
3734 ELSIF c_segments.application_column_name = 'SEGMENT16' THEN
3735 l_segment_array(l_indx):= l_category_rec.segment16;
3736 ELSIF c_segments.application_column_name = 'SEGMENT17' THEN
3737 l_segment_array(l_indx):= l_category_rec.segment17;
3738 ELSIF c_segments.application_column_name = 'SEGMENT18' THEN
3739 l_segment_array(l_indx):= l_category_rec.segment18;
3740 ELSIF c_segments.application_column_name = 'SEGMENT19' THEN
3741 l_segment_array(l_indx):= l_category_rec.segment19;
3742 ELSIF c_segments.application_column_name = 'SEGMENT20' THEN
3743 l_segment_array(l_indx):= l_category_rec.segment20;
3744 END IF;
3745 l_indx := l_indx+1;
3746 END LOOP;
3747
3748 l_concat_segs :=fnd_flex_ext.concatenate_segments(l_n_segments,
3749 l_segment_array,
3750 l_delim);
3751
3752 IF (l_debug = 1) THEN
3753 mdebug('Delim : '||l_delim);
3754 mdebug('Flex code : '||G_CAT_FLEX_CODE);
3755 mdebug('struct# : '||l_structure_id);
3756 mdebug('# of segs : '||to_char(l_n_segments));
3757 mdebug('Concat segs : '||l_concat_segs);
3758 END IF;
3759
3760 l_success := fnd_flex_keyval.validate_segs(
3761 operation => 'FIND_COMBINATION',
3762 appl_short_name => G_INVENTORY_APP_SHORT_NAME,
3763 key_flex_code => G_CAT_FLEX_CODE,
3764 structure_number => l_structure_id,
3765 concat_segments => l_concat_segs
3766 );
3767 IF l_success THEN
3768 x_return_status := FND_API.G_RET_STS_SUCCESS;
3769 x_category_id := FND_FLEX_KEYVAL.combination_id;
3770 ELSE
3771
3772 x_msg_data := FND_FLEX_KEYVAL.error_message;
3773 FND_MESSAGE.Set_Name('FND','FLEX-NO DYNAMIC INSERTS');
3774 l_msg_text := FND_MESSAGE.Get();
3775
3776 IF (INSTR(x_msg_data,l_msg_text) > 0) THEN
3777 x_return_status := FND_API.G_RET_STS_SUCCESS;
3778 x_category_id := -1;
3779 ELSE
3780 x_return_status := FND_API.G_RET_STS_ERROR;
3781 END IF;
3782
3783 END IF;
3784 EXCEPTION
3785 WHEN OTHERS THEN
3786 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3787 x_msg_data := 'Executing - '||G_PKG_NAME||'.get_category_id_from_cat_rec: '||SQLERRM;
3788
3789 END Get_Category_Id_From_Cat_Rec;
3790
3791
3792 END INV_ITEM_CATEGORY_PUB;
[Home] [Help]