1 PACKAGE BODY INV_ITEM_CATEGORY_PUB AS
2 /* $Header: INVPCATB.pls 120.6.12000000.4 2007/07/26 10:53:09 arattan 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;
355 mdebug('Validate Params: No loops after updation ');
352 RAISE fnd_api.g_EXC_ERROR;
353 END IF;
354 IF l_debug = 1 THEN
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
464 l_valid := TRUE;
461 IF l_debug = 1 THEN
462 mdebug('Validate Params: Parent category id is null for hierarchy disabled ');
463 END IF;
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,
612 attribute2,
609 segment19,
610 segment20,
611 attribute1,
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
722 x_category_rec.structure_code IS NULL) THEN
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
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
848 IF x_category_rec.segment10 = g_MISS_CHAR THEN
845 x_category_rec.segment9 := NULL;
846 END IF;
847
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;
984 /*
981 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
982 END IF;
983 CLOSE get_category_structure_id;
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
1102
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;
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
1229 IF x_category_rec.attribute8 = g_MISS_CHAR THEN
1226 x_category_rec.attribute7 := l_category_rec.attribute7;
1227 END IF;
1228
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 PROCEDURE ValueSet_Validate
1267 (
1268 p_structure_id IN NUMBER,
1269 p_concat_segs IN VARCHAR2
1270 ) IS
1271 l_success BOOLEAN;
1272 l_trim_str VARCHAR2(2000) ;
1273 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1274
1275 BEGIN
1276 l_success := fnd_flex_keyval.validate_segs(
1277 operation => 'CHECK_SEGMENTS',
1278 appl_short_name => G_INVENTORY_APP_SHORT_NAME,
1279 key_flex_code => G_CAT_FLEX_CODE,
1280 structure_number => p_structure_id,
1281 concat_segments => p_concat_segs
1282 );
1283 --Bug: 2445444 modified If condition
1284 IF (l_success OR
1285 ( NOT l_success AND
1286 (INSTR(FND_FLEX_KEYVAL.error_message,'has been disabled.')> 0 OR
1287 INSTR(FND_FLEX_KEYVAL.error_message,'has expired.')> 0 OR
1288 INSTR(FND_FLEX_KEYVAL.error_message,'This combination is disabled')>0))) THEN
1289 NULL;
1290 ELSE
1291 l_trim_str := FND_FLEX_KEYVAL.error_message;
1292 fnd_message.set_name('FND','FLEX-SSV EXCEPTION');
1293 fnd_message.set_token('MSG', 'Value set validation error in ValueSet_Validate()');
1294 fnd_msg_pub.ADD;
1295 IF (l_debug = 1) THEN
1296 mdebug('ValueSet Validation Error : '||l_trim_str);
1297 END IF;
1298
1299 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1300 END IF;
1301 END ValueSet_Validate;
1302
1303 ----------------------------------------------------------------------------
1304 PROCEDURE Flex_Validate
1305 (
1306 p_operation IN NUMBER,
1307 p_category_rec IN INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE
1308 ) IS
1309 l_category_id NUMBER;
1310 l_structure_id NUMBER;
1311 l_success BOOLEAN;
1312 l_concat_segs VARCHAR2(2000) ;
1313 l_n_segments NUMBER ;
1314 l_segment_array FND_FLEX_EXT.SegmentArray;
1315 l_delim VARCHAR2(10);
1316 l_indx NUMBER;
1317
1318 CURSOR segment_count(p_structure_id NUMBER) IS
1319 SELECT count(segment_num)
1320 FROM fnd_id_flex_segments
1321 WHERE application_id = G_INVENTORY_APP_ID
1322 AND id_flex_code = G_CAT_FLEX_CODE
1323 AND id_flex_num = p_structure_id
1324 AND (enabled_flag = 'Y' OR NVL(g_eni_upgarde_flag,'N') = 'Y');-- Added for 11.5.10 ENI Upgrade
1325
1326 --Bug: 3893482
1327 CURSOR c_get_segments(cp_flex_num NUMBER) IS
1328 SELECT application_column_name,rownum
1329 FROM fnd_id_flex_segments
1330 WHERE application_id = 401
1331 AND id_flex_code = 'MCAT'
1332 AND id_flex_num = cp_flex_num
1333 AND enabled_flag = 'Y'
1334 ORDER BY segment_num ASC;
1335
1336 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1337 BEGIN
1338 l_structure_id := p_category_rec.structure_id;
1339
1340 OPEN segment_count(l_structure_id);
1341 FETCH segment_count INTO l_n_segments;
1342 IF (segment_count%NOTFOUND) THEN
1343 IF (l_debug = 1) THEN
1344 mdebug('The Number of segments not found');
1345 END IF;
1346 END IF;
1347 CLOSE segment_count;
1348 IF (l_debug = 1) THEN
1349 mdebug('Tracing....4');
1350 END IF;
1351
1352
1353 l_delim := fnd_flex_ext.get_delimiter(G_INVENTORY_APP_SHORT_NAME,
1354 G_CAT_FLEX_CODE,
1355 l_structure_id);
1356 IF l_delim is NULL then
1357 fnd_message.set_name('OFA','FA_BUDGET_NO_SEG_DELIM');
1358 fnd_msg_pub.ADD;
1362 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1359 IF (l_debug = 1) THEN
1360 mdebug('Delimiter is NULL...Error');
1361 END IF;
1363 END IF;
1364
1365 --Start: 3893482
1366 l_indx := 1;
1367 FOR c_segments in c_get_segments(l_structure_id) LOOP
1368 IF c_segments.application_column_name = 'SEGMENT1' THEN
1369 l_segment_array(l_indx):= p_category_rec.segment1;
1370 ELSIF c_segments.application_column_name = 'SEGMENT2' THEN
1371 l_segment_array(l_indx):= p_category_rec.segment2;
1372 ELSIF c_segments.application_column_name = 'SEGMENT3' THEN
1373 l_segment_array(l_indx):= p_category_rec.segment3;
1374 ELSIF c_segments.application_column_name = 'SEGMENT4' THEN
1375 l_segment_array(l_indx):= p_category_rec.segment4;
1376 ELSIF c_segments.application_column_name = 'SEGMENT5' THEN
1377 l_segment_array(l_indx):= p_category_rec.segment5;
1378 ELSIF c_segments.application_column_name = 'SEGMENT6' THEN
1379 l_segment_array(l_indx):= p_category_rec.segment6;
1380 ELSIF c_segments.application_column_name = 'SEGMENT7' THEN
1381 l_segment_array(l_indx):= p_category_rec.segment7;
1382 ELSIF c_segments.application_column_name = 'SEGMENT8' THEN
1383 l_segment_array(l_indx):= p_category_rec.segment8;
1384 ELSIF c_segments.application_column_name = 'SEGMENT9' THEN
1385 l_segment_array(l_indx):= p_category_rec.segment9;
1386 ELSIF c_segments.application_column_name = 'SEGMENT10' THEN
1387 l_segment_array(l_indx):= p_category_rec.segment10;
1388 ELSIF c_segments.application_column_name = 'SEGMENT11' THEN
1389 l_segment_array(l_indx):= p_category_rec.segment11;
1390 ELSIF c_segments.application_column_name = 'SEGMENT12' THEN
1391 l_segment_array(l_indx):= p_category_rec.segment12;
1392 ELSIF c_segments.application_column_name = 'SEGMENT13' THEN
1393 l_segment_array(l_indx):= p_category_rec.segment13;
1394 ELSIF c_segments.application_column_name = 'SEGMENT14' THEN
1395 l_segment_array(l_indx):= p_category_rec.segment14;
1396 ELSIF c_segments.application_column_name = 'SEGMENT15' THEN
1397 l_segment_array(l_indx):= p_category_rec.segment15;
1398 ELSIF c_segments.application_column_name = 'SEGMENT16' THEN
1399 l_segment_array(l_indx):= p_category_rec.segment16;
1400 ELSIF c_segments.application_column_name = 'SEGMENT17' THEN
1401 l_segment_array(l_indx):= p_category_rec.segment17;
1402 ELSIF c_segments.application_column_name = 'SEGMENT18' THEN
1403 l_segment_array(l_indx):= p_category_rec.segment18;
1404 ELSIF c_segments.application_column_name = 'SEGMENT19' THEN
1405 l_segment_array(l_indx):= p_category_rec.segment19;
1406 ELSIF c_segments.application_column_name = 'SEGMENT20' THEN
1407 l_segment_array(l_indx):= p_category_rec.segment20;
1408 END IF;
1409 l_indx := l_indx+1;
1410 END LOOP;
1411 --End: 3893482
1412
1413 /*
1414 l_segment_array(1) := p_category_rec.segment1 ;
1415 l_segment_array(2) := p_category_rec.segment2 ;
1416 l_segment_array(3) := p_category_rec.segment3 ;
1417 l_segment_array(4) := p_category_rec.segment4 ;
1418 l_segment_array(5) := p_category_rec.segment5 ;
1419 l_segment_array(6) := p_category_rec.segment6 ;
1420 l_segment_array(7) := p_category_rec.segment7 ;
1421 l_segment_array(8) := p_category_rec.segment8 ;
1422 l_segment_array(9) := p_category_rec.segment9 ;
1423 l_segment_array(10):= p_category_rec.segment10;
1424 l_segment_array(11):= p_category_rec.segment11;
1425 l_segment_array(12):= p_category_rec.segment12;
1426 l_segment_array(13):= p_category_rec.segment13;
1427 l_segment_array(14):= p_category_rec.segment14;
1428 l_segment_array(15):= p_category_rec.segment15;
1429 l_segment_array(16):= p_category_rec.segment16;
1430 l_segment_array(17):= p_category_rec.segment17;
1431 l_segment_array(18):= p_category_rec.segment18;
1432 l_segment_array(19):= p_category_rec.segment19;
1433 l_segment_array(20):= p_category_rec.segment20;
1434 */
1435
1436 IF (l_debug = 1) THEN
1437 mdebug('Tracing....5');
1438 END IF;
1439
1440
1441 l_concat_segs :=fnd_flex_ext.concatenate_segments(l_n_segments,
1442 l_segment_array,
1443 l_delim);
1444
1445 IF (l_debug = 1) THEN
1446 mdebug('Delim : '||l_delim);
1447 mdebug('Flex code : '||G_CAT_FLEX_CODE);
1448 mdebug('struct# : '||l_structure_id);
1449 mdebug('# of segs : '||to_char(l_n_segments));
1450 mdebug('Concat segs : '||l_concat_segs);
1451 END IF;
1452
1453 l_success := fnd_flex_keyval.validate_segs(
1454 operation => 'FIND_COMBINATION',
1455 appl_short_name => G_INVENTORY_APP_SHORT_NAME,
1456 key_flex_code => G_CAT_FLEX_CODE,
1457 structure_number => l_structure_id,
1458 concat_segments => l_concat_segs
1459 );
1463 (INSTR(FND_FLEX_KEYVAL.error_message,'has been disabled.')> 0 OR
1460 --Bug: 2445444 modified If condition
1461 IF (l_success OR
1462 ( NOT l_success AND
1464 INSTR(FND_FLEX_KEYVAL.error_message,'has expired.')> 0 OR
1465 INSTR(FND_FLEX_KEYVAL.error_message,'This combination is disabled')> 0
1466 )
1467 AND (p_operation = G_UPDATE))) THEN
1468 IF (p_operation = G_INSERT) THEN
1469
1470 fnd_message.set_name('INV','INV_NEW_ENT');
1471 fnd_message.set_token('TOKEN', 'Category Segment Combination');
1472 fnd_msg_pub.ADD;
1473 IF (l_debug = 1) THEN
1474 mdebug('CCID already exists => '|| To_char(FND_FLEX_KEYVAL.combination_id));
1475 END IF;
1476 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1477
1478 ELSIF (p_operation = G_UPDATE) THEN
1479 IF (FND_FLEX_KEYVAL.combination_id <>
1480 p_category_rec.category_id) THEN
1481 fnd_message.set_name('INV','INV_NEW_ENT');
1482 fnd_message.set_token('TOKEN', 'Category segment combination. Specified Combination used by another Category.');
1483 fnd_msg_pub.ADD;
1484 IF (l_debug = 1) THEN
1485 mdebug( 'Code combination already used for another category');
1486 END IF;
1487 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1488 ELSE
1489 ValueSet_Validate(l_structure_id, l_concat_segs);
1490 IF (l_debug = 1) THEN
1491 mdebug('Updating CCID/Category_Id => '|| To_char(FND_FLEX_KEYVAL.combination_id));
1492 END IF;
1493 END IF;
1494 ELSE -- neither insert nor update
1495 NULL;
1496 END IF;
1497 ELSE -- (l_success = FALSE)
1498 IF (p_operation = G_INSERT) THEN
1499 ValueSet_Validate(l_structure_id, l_concat_segs);
1500 IF (l_debug = 1) THEN
1501 mdebug('Combination new. Creating Category....');
1502 END IF;
1503
1504 /* -------------------------------------------------------
1505 The COMBINATION need not be created using this.
1506 Calling procedure will take care of inserting record.
1507 Since the COMBINATION_ID is Category_Id, just verifying if the
1508 comb. exists through fnd_flex_keyval.validate_segs(FIND_COMB..)
1509 call and inserting directly in database through Table Handler
1510 would be enough. The folllowing could be used as alternative.
1511
1512 l_success := fnd_flex_keyval.validate_segs(
1513 operation => 'CREATE_COMBINATION',
1514 appl_short_name => G_INVENTORY_APP_SHORT_NAME,
1515 key_flex_code => G_CAT_FLEX_CODE,
1516 structure_number => l_structure_id,
1517 concat_segments => l_concat_segs
1518 );
1519 IF (l_debug = 1) THEN
1520 mdebug('The CCID : '||To_char(FND_FLEX_KEYVAL.combination_id));
1521 mdebug('Error : '||FND_FLEX_KEYVAL.error_message);
1522 END IF;
1523 --------------------------------------------------------- */
1524
1525 ELSIF (p_operation = G_UPDATE) THEN
1526 fnd_message.set_name('INV','INV_VALID_CAT');
1527 fnd_msg_pub.ADD;
1528 IF (l_debug = 1) THEN
1529 mdebug('Trying to update a non-existant ROW');
1530 END IF;
1531 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1532
1533 ELSE -- neither insert nor update
1534 NULL;
1535 END IF;
1536 END IF;
1537
1538 --mdebug('Error : '||FND_FLEX_KEYVAL.error_message);
1539 END Flex_Validate;
1540
1541 -- 1. Create_Category
1542 ----------------------------------------------------------------------------
1543 PROCEDURE Create_Category
1544 (
1545 p_api_version IN NUMBER ,
1546 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1547 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1548 x_return_status OUT NOCOPY VARCHAR2 ,
1549 x_errorcode OUT NOCOPY NUMBER,
1550 x_msg_count OUT NOCOPY NUMBER ,
1551 x_msg_data OUT NOCOPY VARCHAR2 ,
1552 p_category_rec IN INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE,
1553 x_category_id OUT NOCOPY NUMBER
1554 )
1555 IS
1556 -- Start OF comments
1557 -- API name : Create_Category
1558 -- TYPE : Public
1559 -- Pre-reqs : None
1560 -- FUNCTION : Create a category.
1561 --
1562 -- Version: Current Version 0.1
1563 -- Previous Version : None
1564 -- Notes :
1565 --
1566 -- END OF comments
1567
1568 l_api_name CONSTANT VARCHAR2(30) := 'Create_Category';
1569 -- On addition of any Required parameters the major version needs
1570 -- to change i.e. for eg. 1.X to 2.X.
1571 -- On addition of any Optional parameters the minor version needs
1572 -- to change i.e. for eg. X.6 to X.7.
1573
1574 l_api_version CONSTANT NUMBER := 1.0;
1575 l_row_count NUMBER;
1579 l_category_id NUMBER;
1576
1577 -- General variables
1578 l_category_rec INV_ITEM_CATEGORY_PUB.category_rec_type;
1580 l_success BOOLEAN; --boolean for descr. flex valiation
1581 l_row_id VARCHAR2(20);
1582 l_sys_date DATE := Sysdate;
1583
1584 CURSOR new_category_id IS
1585 SELECT mtl_categories_s.nextval
1586 FROM dual;
1587 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1588 BEGIN
1589 -- Standard Start of API savepoint
1590 SAVEPOINT Create_Category_PUB;
1591
1592 -- Check for call compatibility.
1593 IF NOT FND_API.Compatible_API_Call (l_api_version,
1594 p_api_version,
1595 l_api_name,
1596 G_PKG_NAME)
1597 THEN
1598 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1599 END IF;
1600 -- Initialize API message list if necessary.
1601 -- Initialize message list if p_init_msg_list is set to TRUE.
1602 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1603 FND_MSG_PUB.initialize;
1604 END IF;
1605
1606 IF (l_debug = 1) THEN
1607 mdebug('Tracing....1');
1608 END IF;
1609
1610 -- To set the APPS Environment context through PL/SQL.
1611 -- Apps_Initialize();
1612
1613 -- To process the Input record for any invalid values provided.
1614 Preprocess_Category_Rec(G_INSERT, p_category_rec, l_category_rec) ;
1615 Flex_Validate(G_INSERT, l_category_rec);
1616
1617 -- Category_Id is always created from sequence.
1618 OPEN new_category_id;
1619 FETCH new_category_id INTO l_category_id;
1620 IF (new_category_id%NOTFOUND) THEN
1621 IF (l_debug = 1) THEN
1622 mdebug('Dubious error with the MTL_CATEGORIES_S sequence');
1623 END IF;
1624 END IF;
1625 CLOSE new_category_id;
1626
1627 /* Need for Descriptive Flex validation
1628
1629 l_attribute_category := l_category_rec.attribute_category;
1630 l_attribute1 := l_category_rec.attribute1 ;
1631 l_attribute2 := l_category_rec.attribute2 ;
1632 l_attribute3 := l_category_rec.attribute3 ;
1633 l_attribute4 := l_category_rec.attribute4 ;
1634 l_attribute5 := l_category_rec.attribute5 ;
1635 l_attribute6 := l_category_rec.attribute6 ;
1636 l_attribute7 := l_category_rec.attribute7 ;
1637 l_attribute8 := l_category_rec.attribute8 ;
1638 l_attribute9 := l_category_rec.attribute9 ;
1639 l_attribute10 := l_category_rec.attribute10;
1640 l_attribute11 := l_category_rec.attribute11;
1641 l_attribute12 := l_category_rec.attribute12;
1642 l_attribute13 := l_category_rec.attribute13;
1643 l_attribute14 := l_category_rec.attribute14;
1644 l_attribute15 := l_category_rec.attribute15;
1645 */
1646
1647 --Final call for insertion.
1648 MTL_CATEGORIES_PKG.Insert_Row(
1649 X_ROWID => l_row_id, -- OUT variable
1650 X_CATEGORY_ID => l_category_id, -- gen from seq.
1651 X_DESCRIPTION => l_category_rec.description,
1652 X_STRUCTURE_ID => l_category_rec.structure_id,
1653 X_DISABLE_DATE => l_category_rec.disable_date,
1654 X_WEB_STATUS => l_category_rec.web_status,--Bug: 2430879
1655 X_SUPPLIER_ENABLED_FLAG => l_category_rec.supplier_enabled_flag,--Bug: 2645153
1656 X_SEGMENT1 => l_category_rec.segment1 ,
1657 X_SEGMENT2 => l_category_rec.segment2 ,
1658 X_SEGMENT3 => l_category_rec.segment3 ,
1659 X_SEGMENT4 => l_category_rec.segment4 ,
1660 X_SEGMENT5 => l_category_rec.segment5 ,
1661 X_SEGMENT6 => l_category_rec.segment6 ,
1662 X_SEGMENT7 => l_category_rec.segment7 ,
1663 X_SEGMENT8 => l_category_rec.segment8 ,
1664 X_SEGMENT9 => l_category_rec.segment9 ,
1665 X_SEGMENT10 => l_category_rec.segment10 ,
1666 X_SEGMENT11 => l_category_rec.segment11 ,
1667 X_SEGMENT12 => l_category_rec.segment12 ,
1668 X_SEGMENT13 => l_category_rec.segment13 ,
1669 X_SEGMENT14 => l_category_rec.segment14 ,
1670 X_SEGMENT15 => l_category_rec.segment15 ,
1671 X_SEGMENT16 => l_category_rec.segment16 ,
1672 X_SEGMENT17 => l_category_rec.segment17 ,
1673 X_SEGMENT18 => l_category_rec.segment18 ,
1674 X_SEGMENT19 => l_category_rec.segment19 ,
1675 X_SEGMENT20 => l_category_rec.segment20 ,
1676 X_SUMMARY_FLAG => l_category_rec.summary_flag,
1677 X_ENABLED_FLAG => l_category_rec.enabled_flag,
1678 X_START_DATE_ACTIVE => l_category_rec.start_date_active,
1679 X_END_DATE_ACTIVE => l_category_rec.end_date_active,
1680 X_ATTRIBUTE_CATEGORY => l_category_rec.attribute_category,
1681 X_ATTRIBUTE1 => l_category_rec.attribute1 ,
1682 X_ATTRIBUTE2 => l_category_rec.attribute2 ,
1686 X_ATTRIBUTE6 => l_category_rec.attribute6 ,
1683 X_ATTRIBUTE3 => l_category_rec.attribute3 ,
1684 X_ATTRIBUTE4 => l_category_rec.attribute4 ,
1685 X_ATTRIBUTE5 => l_category_rec.attribute5 ,
1687 X_ATTRIBUTE7 => l_category_rec.attribute7 ,
1688 X_ATTRIBUTE8 => l_category_rec.attribute8 ,
1689 X_ATTRIBUTE9 => l_category_rec.attribute9 ,
1690 X_ATTRIBUTE10 => l_category_rec.attribute10,
1691 X_ATTRIBUTE11 => l_category_rec.attribute11,
1692 X_ATTRIBUTE12 => l_category_rec.attribute12,
1693 X_ATTRIBUTE13 => l_category_rec.attribute13,
1694 X_ATTRIBUTE14 => l_category_rec.attribute14,
1695 X_ATTRIBUTE15 => l_category_rec.attribute15,
1696 X_LAST_UPDATE_DATE => l_sys_date,
1697 X_LAST_UPDATED_BY => fnd_global.user_id,
1698 X_CREATION_DATE => l_sys_date,
1699 X_CREATED_BY => fnd_global.user_id,
1700 X_LAST_UPDATE_LOGIN => fnd_global.login_id
1701 );
1702
1703 IF (l_debug = 1) THEN
1704 mdebug('Created New CCID/Category_ID : '|| l_category_id);
1705 END IF;
1706 -- assigning the created value to the return OUT value
1707 x_category_id := l_category_id;
1708
1709 IF (l_debug = 1) THEN
1710 mdebug('Tracing....10');
1711 END IF;
1712
1713 -- Standard check of p_commit.
1714 IF FND_API.To_Boolean( p_commit ) THEN
1715 COMMIT WORK;
1716 END IF;
1717
1718 x_return_status := FND_API.G_RET_STS_SUCCESS;
1719 -- Standard call to get message count and if count is 1,
1720 -- get message info.
1721 -- The client will directly display the x_msg_data (which is already
1722 -- translated) if the x_msg_count = 1;
1723 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
1724 -- Server-side procedure to access the messages, and consolidate them
1725 -- and display (or) to display one message after another.
1726 IF (l_debug = 1) THEN
1727 mdebug('Tracing....11');
1728 END IF;
1729 FND_MSG_PUB.Count_And_Get
1730 ( p_count => x_msg_count,
1731 p_data => x_msg_data
1732 );
1733 EXCEPTION
1734 WHEN FND_API.G_EXC_ERROR THEN
1735 IF (l_debug = 1) THEN
1736 mdebug('Ending : Returning ERROR');
1737 END IF;
1738 ROLLBACK TO Create_Category_PUB;
1739 x_return_status := FND_API.G_RET_STS_ERROR;
1740 FND_MSG_PUB.Count_And_Get
1741 ( p_count => x_msg_count,
1742 p_data => x_msg_data
1743 );
1744 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1745 ROLLBACK TO Create_Category_PUB;
1746 IF (l_debug = 1) THEN
1747 mdebug('Ending : Returning UNEXPECTED ERROR');
1748 END IF;
1749 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1750 FND_MSG_PUB.Count_And_Get
1751 ( p_count => x_msg_count,
1752 p_data => x_msg_data
1753 );
1754 WHEN OTHERS THEN
1755 ROLLBACK TO Create_Category_PUB;
1756 IF (l_debug = 1) THEN
1757 mdebug('Ending : Returning UNEXPECTED ERROR');
1758 END IF;
1759 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1760 IF FND_MSG_PUB.Check_Msg_Level
1761 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1762 THEN
1763 FND_MSG_PUB.Add_Exc_Msg
1764 ( G_PKG_NAME ,
1765 l_api_name
1766 );
1767 END IF;
1768 FND_MSG_PUB.Count_And_Get
1769 ( p_count => x_msg_count,
1770 p_data => x_msg_data
1771 );
1772
1773 END Create_Category;
1774 ----------------------------------------------------------------------------
1775
1776
1777 -- 2. Update_Category
1778 ----------------------------------------------------------------------------
1779 PROCEDURE Update_Category
1780 (
1781 p_api_version IN NUMBER ,
1782 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1783 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1784 x_return_status OUT NOCOPY VARCHAR2 ,
1785 x_errorcode OUT NOCOPY NUMBER,
1786 x_msg_count OUT NOCOPY NUMBER ,
1787 x_msg_data OUT NOCOPY VARCHAR2 ,
1788 p_category_rec IN INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE
1789 )
1790 IS
1791
1792 -- Start OF comments
1793 -- API name : Update_Category
1794 -- TYPE : Public
1795 -- Pre-reqs : None
1796 -- FUNCTION : Update a category.
1797 --
1798 -- Version: Current Version 0.1
1799 -- Previous Version : None
1803 l_api_name CONSTANT VARCHAR2(30) := 'Update_Category';
1800 -- Notes : Stub Version
1801 --
1802 -- END OF comments
1804 -- On addition of any Required parameters the major version needs
1805 -- to change i.e. for eg. 1.X to 2.X.
1806 -- On addition of any Optional parameters the minor version needs
1807 -- to change i.e. for eg. X.6 to X.7.
1808 l_api_version CONSTANT NUMBER := 1.0;
1809 l_row_count NUMBER;
1810
1811 -- General variables
1812 l_category_rec INV_ITEM_CATEGORY_PUB.category_rec_type;
1813 l_success BOOLEAN; --boolean for descr. flex valiation
1814
1815
1816 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
1817 BEGIN
1818 -- Standard Start of API savepoint
1819 SAVEPOINT Update_Category_PUB;
1820
1821
1822 IF NOT FND_API.Compatible_API_Call (l_api_version,
1823 p_api_version ,
1824 l_api_name ,
1825 G_PKG_NAME)
1826 THEN
1827 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1828 END IF;
1829 -- Initialize API message list if necessary.
1830 -- Initialize message list if p_init_msg_list is set to TRUE.
1831 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1832 FND_MSG_PUB.initialize;
1833 END IF;
1834
1835 IF (l_debug = 1) THEN
1836 mdebug('Tracing....1');
1837 END IF;
1838
1839 -- To process the Input record for any invalid values provided.
1840 Preprocess_Category_Rec(G_UPDATE, p_category_rec, l_category_rec) ;
1841 Flex_Validate(G_UPDATE, l_category_rec);
1842
1843
1844 /* Need for Descriptive Flex validation
1845
1846 l_attribute_category := l_category_rec.attribute_category;
1847 l_attribute1 := l_category_rec.attribute1 ;
1848 l_attribute2 := l_category_rec.attribute2 ;
1849 l_attribute3 := l_category_rec.attribute3 ;
1850 l_attribute4 := l_category_rec.attribute4 ;
1851 l_attribute5 := l_category_rec.attribute5 ;
1852 l_attribute6 := l_category_rec.attribute6 ;
1853 l_attribute7 := l_category_rec.attribute7 ;
1854 l_attribute8 := l_category_rec.attribute8 ;
1855 l_attribute9 := l_category_rec.attribute9 ;
1856 l_attribute10 := l_category_rec.attribute10;
1857 l_attribute11 := l_category_rec.attribute11;
1858 l_attribute12 := l_category_rec.attribute12;
1859 l_attribute13 := l_category_rec.attribute13;
1860 l_attribute14 := l_category_rec.attribute14;
1861 l_attribute15 := l_category_rec.attribute15;
1862 */
1863
1864
1865 --Final call for insertion.
1866 MTL_CATEGORIES_PKG.Update_Row(
1867 X_CATEGORY_ID => l_category_rec.category_id,
1868 X_DESCRIPTION => l_category_rec.description,
1869 X_STRUCTURE_ID => l_category_rec.structure_id,
1870 X_DISABLE_DATE => l_category_rec.disable_date,
1871 X_WEB_STATUS => l_category_rec.web_status,--Bug: 2430879
1872 X_SUPPLIER_ENABLED_FLAG => l_category_rec.supplier_enabled_flag,--Bug: 2645153
1873 X_SEGMENT1 => l_category_rec.segment1 ,
1874 X_SEGMENT2 => l_category_rec.segment2 ,
1875 X_SEGMENT3 => l_category_rec.segment3 ,
1876 X_SEGMENT4 => l_category_rec.segment4 ,
1877 X_SEGMENT5 => l_category_rec.segment5 ,
1878 X_SEGMENT6 => l_category_rec.segment6 ,
1879 X_SEGMENT7 => l_category_rec.segment7 ,
1880 X_SEGMENT8 => l_category_rec.segment8 ,
1881 X_SEGMENT9 => l_category_rec.segment9 ,
1882 X_SEGMENT10 => l_category_rec.segment10 ,
1883 X_SEGMENT11 => l_category_rec.segment11 ,
1884 X_SEGMENT12 => l_category_rec.segment12 ,
1885 X_SEGMENT13 => l_category_rec.segment13 ,
1886 X_SEGMENT14 => l_category_rec.segment14 ,
1887 X_SEGMENT15 => l_category_rec.segment15 ,
1888 X_SEGMENT16 => l_category_rec.segment16 ,
1889 X_SEGMENT17 => l_category_rec.segment17 ,
1890 X_SEGMENT18 => l_category_rec.segment18 ,
1891 X_SEGMENT19 => l_category_rec.segment19 ,
1892 X_SEGMENT20 => l_category_rec.segment20 ,
1893 X_SUMMARY_FLAG => l_category_rec.summary_flag,
1894 X_ENABLED_FLAG => l_category_rec.enabled_flag,
1895 X_START_DATE_ACTIVE => l_category_rec.start_date_active,
1896 X_END_DATE_ACTIVE => l_category_rec.end_date_active,
1897 X_ATTRIBUTE_CATEGORY => l_category_rec.attribute_category,
1898 X_ATTRIBUTE1 => l_category_rec.attribute1 ,
1899 X_ATTRIBUTE2 => l_category_rec.attribute2 ,
1900 X_ATTRIBUTE3 => l_category_rec.attribute3 ,
1901 X_ATTRIBUTE4 => l_category_rec.attribute4 ,
1902 X_ATTRIBUTE5 => l_category_rec.attribute5 ,
1903 X_ATTRIBUTE6 => l_category_rec.attribute6 ,
1904 X_ATTRIBUTE7 => l_category_rec.attribute7 ,
1908 X_ATTRIBUTE11 => l_category_rec.attribute11,
1905 X_ATTRIBUTE8 => l_category_rec.attribute8 ,
1906 X_ATTRIBUTE9 => l_category_rec.attribute9 ,
1907 X_ATTRIBUTE10 => l_category_rec.attribute10,
1909 X_ATTRIBUTE12 => l_category_rec.attribute12,
1910 X_ATTRIBUTE13 => l_category_rec.attribute13,
1911 X_ATTRIBUTE14 => l_category_rec.attribute14,
1912 X_ATTRIBUTE15 => l_category_rec.attribute15,
1913 X_LAST_UPDATE_DATE => sysdate,
1914 X_LAST_UPDATED_BY => fnd_global.user_id,
1915 X_LAST_UPDATE_LOGIN => fnd_global.login_id
1916 );
1917
1918 IF (l_debug = 1) THEN
1919 mdebug('Updated Category: '||To_char(l_category_rec.category_id));
1920 END IF;
1921
1922 IF (l_debug = 1) THEN
1923 mdebug('Update_Category:: Tracing....10');
1924 END IF;
1925
1926 -- Standard check of p_commit.
1927 IF FND_API.To_Boolean( p_commit ) THEN
1928 COMMIT WORK;
1929 END IF;
1930
1931 x_return_status := FND_API.G_RET_STS_SUCCESS;
1932 -- Standard call to get message count and if count is 1,
1933 -- get message info.
1934 -- The client will directly display the x_msg_data (which is already
1935 -- translated) if the x_msg_count = 1;
1936 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
1937 -- Server-side procedure to access the messages, and consolidate them
1938 -- and display (or) to display one message after another.
1939 FND_MSG_PUB.Count_And_Get
1940 ( p_count => x_msg_count,
1941 p_data => x_msg_data
1942 );
1943 EXCEPTION
1944 WHEN FND_API.G_EXC_ERROR THEN
1945 ROLLBACK TO Update_Category_PUB;
1946 x_return_status := FND_API.G_RET_STS_ERROR;
1947 FND_MSG_PUB.Count_And_Get
1948 ( p_count => x_msg_count,
1949 p_data => x_msg_data
1950 );
1951 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1952 ROLLBACK TO Update_Category_PUB;
1953 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1954 FND_MSG_PUB.Count_And_Get
1955 ( p_count => x_msg_count,
1956 p_data => x_msg_data
1957 );
1958 WHEN OTHERS THEN
1959 ROLLBACK TO Update_Category_PUB;
1960 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1961 IF FND_MSG_PUB.Check_Msg_Level
1962 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1963 THEN
1964 FND_MSG_PUB.Add_Exc_Msg
1965 ( G_PKG_NAME ,
1966 l_api_name
1967 );
1968 END IF;
1969 FND_MSG_PUB.Count_And_Get
1970 ( p_count => x_msg_count,
1971 p_data => x_msg_data
1972 );
1973
1974
1975
1976 END Update_Category;
1977 ----------------------------------------------------------------------------
1978
1979
1980 -- 3. Update_Category_Description
1981 ----------------------------------------------------------------------------
1982 PROCEDURE Update_Category_Description
1983 (
1984 p_api_version IN NUMBER ,
1985 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1986 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1987 x_return_status OUT NOCOPY VARCHAR2 ,
1988 x_errorcode OUT NOCOPY NUMBER,
1989 x_msg_count OUT NOCOPY NUMBER ,
1990 x_msg_data OUT NOCOPY VARCHAR2 ,
1991 p_category_id IN NUMBER,
1992 p_description IN VARCHAR2
1993 -- deleted as this can be picked up from the environment.
1994 --p_language IN VARCHAR2
1995 )
1996 IS
1997 -- Start OF comments
1998 -- API name : Update_Category_Description
1999 -- TYPE : Public
2000 -- Pre-reqs : None
2001 -- FUNCTION : Update a category description in the specified language.
2002 --
2003 -- Version: Current Version 0.1
2004 -- Previous Version : None
2005 -- Notes : Stub Version
2006 -- END OF comments
2007 l_api_name CONSTANT VARCHAR2(30) := 'Update_Category_Description';
2008 -- On addition of any Required parameters the major version needs
2009 -- to change i.e. for eg. 1.X to 2.X.
2010 -- On addition of any Optional parameters the minor version needs
2011 -- to change i.e. for eg. X.6 to X.7.
2012 l_api_version CONSTANT NUMBER := 1.0;
2013 l_row_count NUMBER;
2014 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2015 BEGIN
2016 -- Standard Start of API savepoint
2017 SAVEPOINT Update_Category_Desc_PUB;
2018
2019
2020 -- Check for call compatibility.
2021 IF NOT FND_API.Compatible_API_Call (l_api_version,
2022 p_api_version ,
2026 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2023 l_api_name ,
2024 G_PKG_NAME)
2025 THEN
2027 END IF;
2028 -- Initialize API message list if necessary.
2029 -- Initialize message list if p_init_msg_list is set to TRUE.
2030 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2031 FND_MSG_PUB.initialize;
2032 END IF;
2033
2034 UPDATE mtl_categories_tl
2035 SET
2036 description = p_description,
2037 last_update_date = Sysdate,
2038 last_updated_by = fnd_global.user_id,
2039 last_update_login = fnd_global.login_id,
2040 source_lang = userenv('LANG')
2041 WHERE category_id = p_category_id
2042 AND userenv('LANG') IN (language, source_lang) ;
2043
2044 IF (sql%notfound) THEN
2045 fnd_message.set_name('INV','INV_VALID_CAT');
2046 fnd_msg_pub.ADD;
2047 IF (l_debug = 1) THEN
2048 mdebug('Trying to Update a non-existant Category.');
2049 END IF;
2050 RAISE NO_DATA_FOUND;
2051 END IF;
2052
2053 -- Standard check of p_commit.
2054 IF FND_API.To_Boolean( p_commit ) THEN
2055 COMMIT WORK;
2056 END IF;
2057
2058 x_return_status := FND_API.G_RET_STS_SUCCESS;
2059 -- Standard call to get message count and if count is 1,
2060 -- get message info.
2061 -- The client will directly display the x_msg_data (which is already
2062 -- translated) if the x_msg_count = 1;
2063 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
2064 -- Server-side procedure to access the messages, and consolidate them
2065 -- and display (or) to display one message after another.
2066 FND_MSG_PUB.Count_And_Get
2067 ( p_count => x_msg_count,
2068 p_data => x_msg_data
2069 );
2070
2071 EXCEPTION
2072 WHEN FND_API.G_EXC_ERROR THEN
2073 ROLLBACK TO Update_Category_Desc_PUB;
2074 x_return_status := FND_API.G_RET_STS_ERROR;
2075 FND_MSG_PUB.Count_And_Get
2076 ( p_count => x_msg_count,
2077 p_data => x_msg_data
2078 );
2079 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2080 ROLLBACK TO Update_Category_Desc_PUB;
2081 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2082 FND_MSG_PUB.Count_And_Get
2083 ( p_count => x_msg_count,
2084 p_data => x_msg_data
2085 );
2086 WHEN OTHERS THEN
2087 ROLLBACK TO Update_Category_Desc_PUB;
2088 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2089 IF FND_MSG_PUB.Check_Msg_Level
2090 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2091 THEN
2092 FND_MSG_PUB.Add_Exc_Msg
2093 ( G_PKG_NAME ,
2094 l_api_name
2095 );
2096 END IF;
2097 FND_MSG_PUB.Count_And_Get
2098 ( p_count => x_msg_count,
2099 p_data => x_msg_data
2100 );
2101
2102 END Update_Category_Description;
2103 ----------------------------------------------------------------------------
2104
2105 -- 4. Delete_Category
2106 ----------------------------------------------------------------------------
2107 -- ----------------------------------------------------------------------
2108 -- Deletion of categories is not supported.
2109 -- ----------------------------------------------------------------------
2110
2111 PROCEDURE Delete_Category
2112 (
2113 p_api_version IN NUMBER ,
2114 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2115 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2116 x_return_status OUT NOCOPY VARCHAR2 ,
2117 x_errorcode OUT NOCOPY NUMBER,
2118 x_msg_count OUT NOCOPY NUMBER ,
2119 x_msg_data OUT NOCOPY VARCHAR2 ,
2120 p_category_id IN NUMBER
2121 )
2122 IS
2123 -- Start OF comments
2124 -- API name : Delete_Category
2125 -- TYPE : Public
2126 -- Pre-reqs : None
2127 -- FUNCTION : Delete a category.
2128 --
2129 -- Version: Current Version 0.1
2130 -- Previous Version : None
2131 -- Notes : Stub Version
2132 --
2133 -- END OF comments
2134 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Category';
2135 -- On addition of any Required parameters the major version needs
2136 -- to change i.e. for eg. 1.X to 2.X.
2137 -- On addition of any Optional parameters the minor version needs
2138 -- to change i.e. for eg. X.6 to X.7.
2139 l_api_version CONSTANT NUMBER := 1.0;
2140 l_row_count NUMBER;
2141 l_category_assignment_exists VARCHAR(1);
2142 l_default_category_exists VARCHAR(1);
2143 l_valid_category_exists VARCHAR(1);
2144
2145 CURSOR category_assignment_exists(p_category_id NUMBER) IS
2146 SELECT 'x'
2147 FROM dual
2151 WHERE category_id = p_category_id
2148 WHERE exists
2149 ( SELECT category_id
2150 FROM mtl_item_categories
2152 );
2153
2154 CURSOR default_category_exists(p_category_id NUMBER) IS
2155 SELECT 'x'
2156 FROM dual
2157 WHERE exists
2158 ( SELECT default_category_id
2159 FROM mtl_category_sets_b
2160 WHERE default_category_id = p_category_id
2161 );
2162
2163
2164 CURSOR valid_category_exists(p_category_id NUMBER) IS
2165 SELECT 'x'
2166 FROM dual
2167 WHERE exists
2168 ( SELECT category_id
2169 FROM mtl_category_set_valid_cats
2170 WHERE category_id = p_category_id
2171 );
2172
2173
2174 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2175 BEGIN
2176 -- Standard Start of API savepoint
2177 SAVEPOINT Delete_Category_PUB;
2178
2179 -- Check for call compatibility.
2180 IF NOT FND_API.Compatible_API_Call (l_api_version,
2181 p_api_version ,
2182 l_api_name ,
2183 G_PKG_NAME)
2184 THEN
2185 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2186 END IF;
2187 -- Initialize API message list if necessary.
2188 -- Initialize message list if p_init_msg_list is set to TRUE.
2189 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2190 FND_MSG_PUB.initialize;
2191 END IF;
2192
2193 OPEN category_assignment_exists(p_category_id);
2194 FETCH category_assignment_exists INTO l_category_assignment_exists;
2195 IF (category_assignment_exists%NOTFOUND) THEN
2196 IF (l_debug = 1) THEN
2197 mdebug('Can Delete: Category not part of any Category Assignment');
2198 END IF;
2199 END IF;
2200 CLOSE category_assignment_exists;
2201 IF (l_category_assignment_exists = 'x') THEN
2202 fnd_message.set_name('INV','INV_CATEGORY_ASSIGNED');
2203 fnd_msg_pub.ADD;
2204 IF (l_debug = 1) THEN
2205 mdebug('Cannot delete: Category part of a Category Assignment');
2206 END IF;
2207 RAISE FND_API.G_EXC_ERROR;
2208 END IF;
2209
2210 OPEN default_category_exists(p_category_id);
2211 FETCH default_category_exists INTO l_default_category_exists;
2212 IF (default_category_exists%NOTFOUND) THEN
2213 IF (l_debug = 1) THEN
2214 mdebug('Can Delete: Category not a default category');
2215 END IF;
2216 END IF;
2217 CLOSE default_category_exists;
2218 IF (l_default_category_exists = 'x') THEN
2219 fnd_message.set_name('INV','INV_CATEGORY_DEFAULT');
2220 fnd_msg_pub.ADD;
2221 IF (l_debug = 1) THEN
2222 mdebug('Cannot delete: Category specified is a default category to one of the Category Sets.');
2223 END IF;
2224 RAISE FND_API.G_EXC_ERROR;
2225 END IF;
2226
2227 OPEN valid_category_exists(p_category_id);
2228 FETCH valid_category_exists INTO l_valid_category_exists;
2229 IF (valid_category_exists%NOTFOUND) THEN
2230 IF (l_debug = 1) THEN
2231 mdebug('Can Delete: Category not part of a Valid category set');
2232 END IF;
2233 END IF;
2234 CLOSE valid_category_exists;
2235 IF (l_valid_category_exists = 'x') THEN
2236 fnd_message.set_name('INV','INV_CATEGORY_IN_USE');
2237 fnd_msg_pub.ADD;
2238 IF (l_debug = 1) THEN
2239 mdebug('Cannot delete: Category specified is part of a valid category set');
2240 END IF;
2241 RAISE FND_API.G_EXC_ERROR;
2242 END IF;
2243
2244 delete from mtl_categories_tl
2245 where category_id = p_category_id ;
2246
2247 if (sql%notfound) then
2248 fnd_message.set_name('INV','INV_VALID_CAT');
2249 fnd_msg_pub.ADD;
2250 IF (l_debug = 1) THEN
2251 mdebug('Trying to delete non-existant Category Id from MTL_CATEGORIES_TL.');
2252 END IF;
2253 RAISE NO_DATA_FOUND;
2254 end if;
2255
2256 delete from mtl_categories_b
2257 where category_id = p_category_id ;
2258
2259 if (sql%notfound) then
2260 fnd_message.set_name('INV','INV_VALID_CAT');
2261 fnd_msg_pub.ADD;
2262 IF (l_debug = 1) THEN
2263 mdebug('Trying to delete non-existant Category Id from MTL_CATEGORIES_B.');
2264 END IF;
2265 RAISE NO_DATA_FOUND;
2266 end if;
2267
2268 IF (l_debug = 1) THEN
2269 mdebug('Category deleted successfully: '||p_category_id);
2270 END IF;
2271 -- Standard check of p_commit.
2272 IF FND_API.To_Boolean( p_commit ) THEN
2273 COMMIT WORK;
2274 END IF;
2275
2276 x_return_status := FND_API.G_RET_STS_SUCCESS;
2277 -- Standard call to get message count and if count is 1,
2278 -- get message info.
2279 -- The client will directly display the x_msg_data (which is already
2280 -- translated) if the x_msg_count = 1;
2284 FND_MSG_PUB.Count_And_Get
2281 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
2282 -- Server-side procedure to access the messages, and consolidate them
2283 -- and display (or) to display one message after another.
2285 ( p_count => x_msg_count,
2286 p_data => x_msg_data
2287 );
2288 EXCEPTION
2289 WHEN FND_API.G_EXC_ERROR THEN
2290 ROLLBACK TO Delete_Category_PUB;
2291 x_return_status := FND_API.G_RET_STS_ERROR;
2292 FND_MSG_PUB.Count_And_Get
2293 ( p_count => x_msg_count,
2294 p_data => x_msg_data
2295 );
2296 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2297 ROLLBACK TO Delete_Category_PUB;
2298 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2299 FND_MSG_PUB.Count_And_Get
2300 ( p_count => x_msg_count,
2301 p_data => x_msg_data
2302 );
2303 WHEN OTHERS THEN
2304 ROLLBACK TO Delete_Category_PUB;
2305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2306 IF FND_MSG_PUB.Check_Msg_Level
2307 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2308 THEN
2309 FND_MSG_PUB.Add_Exc_Msg
2310 ( G_PKG_NAME ,
2311 l_api_name
2312 );
2313 END IF;
2314 FND_MSG_PUB.Count_And_Get
2315 ( p_count => x_msg_count,
2316 p_data => x_msg_data
2317 );
2318
2319
2320 END Delete_Category;
2321
2322 ----------------------------------------------------------------------------
2323
2324 -- 5. Create_Category_Assignment
2325 -- Bug: 2451359, All the validations are taken care in the Pvt pkg,so
2326 -- Calling private pkg instead.
2327 ----------------------------------------------------------------------------
2328 PROCEDURE Create_Category_Assignment
2329 (
2330 p_api_version IN NUMBER,
2331 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2332 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2333 x_return_status OUT NOCOPY VARCHAR2,
2334 x_errorcode OUT NOCOPY NUMBER,
2335 x_msg_count OUT NOCOPY NUMBER,
2336 x_msg_data OUT NOCOPY VARCHAR2,
2337 p_category_id IN NUMBER,
2338 p_category_set_id IN NUMBER,
2339 p_inventory_item_id IN NUMBER,
2340 p_organization_id IN NUMBER
2341 )
2342 IS
2343 -- Start OF comments
2344 -- API name : Create_Category_Assignment
2345 -- TYPE : Public
2346 -- Pre-reqs : None
2347 -- FUNCTION : Create an item category assignment.
2348 --
2349 -- Version: Current Version 0.1
2350 -- Previous Version : None
2351 -- Notes : Stub Version
2352 --
2353 -- END OF comments
2354 l_api_name CONSTANT VARCHAR2(30) := 'Create_Category_Assignment';
2355 -- On addition of any Required parameters the major version needs
2356 -- to change i.e. for eg. 1.X to 2.X.
2357 -- On addition of any Optional parameters the minor version needs
2358 -- to change i.e. for eg. X.6 to X.7.
2359 l_api_version CONSTANT NUMBER := 1.0;
2360
2361 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2362 BEGIN
2363
2364 -- Standard Start of API savepoint
2365 SAVEPOINT Create_Category_Assignment_PUB;
2366
2367 -- Check for call compatibility.
2368 IF NOT FND_API.Compatible_API_Call (l_api_version,
2369 p_api_version ,
2370 l_api_name ,
2371 G_PKG_NAME)
2372 THEN
2373 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2374 END IF;
2375 -- Initialize API message list if necessary.
2376 -- Initialize message list if p_init_msg_list is set to TRUE.
2377 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2378 FND_MSG_PUB.initialize;
2379 END IF;
2380 INV_ITEM_MSG.set_Message_Mode('PLSQL');
2381
2382 IF FND_MSG_PUB.Check_Msg_Level
2383 (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
2384 THEN
2385 INV_ITEM_MSG.set_Message_Level(INV_ITEM_MSG.g_Level_Warning);
2386 END IF;
2387
2388 INV_ITEM_CATEGORY_PVT.Create_Category_Assignment
2389 (
2390 p_api_version => p_api_version
2391 , p_init_msg_list => p_init_msg_list
2392 , p_commit => p_commit
2393 , p_validation_level => INV_ITEM_CATEGORY_PVT.g_VALIDATE_ALL
2394 , p_inventory_item_id => p_inventory_item_id
2395 , p_organization_id => p_organization_id
2396 , p_category_set_id => p_category_set_id
2397 , p_category_id => p_category_id
2398 , x_return_status => x_return_status
2399 , x_msg_count => x_msg_count
2400 , x_msg_data => x_msg_data
2401 );
2402
2406 COMMIT WORK;
2403 --mdebug('Create_Category_Assignment: Done!!');
2404 -- Standard check of p_commit.
2405 IF FND_API.To_Boolean( p_commit ) THEN
2407 END IF;
2408
2409 INV_ITEM_MSG.Write_List;
2410 FND_MSG_PUB.Count_And_Get
2411 ( p_count => x_msg_count,
2412 p_data => x_msg_data
2413 );
2414
2415 EXCEPTION
2416 WHEN FND_API.G_EXC_ERROR THEN
2417 ROLLBACK TO Create_Category_Assignment_PUB;
2418 x_return_status := FND_API.G_RET_STS_ERROR;
2419 FND_MSG_PUB.Count_And_Get
2420 ( p_count => x_msg_count,
2421 p_data => x_msg_data
2422 );
2423 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2424 ROLLBACK TO Create_Category_Assignment_PUB;
2425 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2426 FND_MSG_PUB.Count_And_Get
2427 ( p_count => x_msg_count,
2428 p_data => x_msg_data
2429 );
2430 WHEN OTHERS THEN
2431 ROLLBACK TO Create_Category_Assignment_PUB;
2432 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2433 IF FND_MSG_PUB.Check_Msg_Level
2434 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2435 THEN
2436 FND_MSG_PUB.Add_Exc_Msg
2437 ( G_PKG_NAME ,
2438 l_api_name
2439 );
2440 END IF;
2441 FND_MSG_PUB.Count_And_Get
2442 ( p_count => x_msg_count,
2443 p_data => x_msg_data
2444 );
2445
2446 END Create_Category_Assignment;
2447 ----------------------------------------------------------------------------
2448
2449
2450 -- 6. Delete_Category_Assignment
2451 ----------------------------------------------------------------------------
2452 PROCEDURE Delete_Category_Assignment
2453 (
2454 p_api_version IN NUMBER,
2455 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2456 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2457 x_return_status OUT NOCOPY VARCHAR2,
2458 x_errorcode OUT NOCOPY NUMBER,
2459 x_msg_count OUT NOCOPY NUMBER,
2460 x_msg_data OUT NOCOPY VARCHAR2,
2461 p_category_id IN NUMBER,
2462 p_category_set_id IN NUMBER,
2463 p_inventory_item_id IN NUMBER,
2464 p_organization_id IN NUMBER
2465 )
2466 IS
2467 -- Start OF comments
2468 -- API name : Delete_Category_Assignment
2469 -- TYPE : Public
2470 -- Pre-reqs : None
2471 -- FUNCTION : Delete an item category assignment.
2472 --
2473 -- Version: Current Version 0.1
2474 -- Previous Version : None
2475 -- Notes : Stub Version
2476 --
2477 -- END OF comments
2478 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Category_Assignment';
2479 -- On addition of any Required parameters the major version needs
2480 -- to change i.e. for eg. 1.X to 2.X.
2481 -- On addition of any Optional parameters the minor version needs
2482 -- to change i.e. for eg. X.6 to X.7.
2483 l_api_version CONSTANT NUMBER := 1.0;
2484 l_row_count NUMBER;
2485 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2486 BEGIN
2487 -- Standard Start of API savepoint
2488 SAVEPOINT Delete_Category_Assignment_PUB;
2489
2490 -- Check for call compatibility.
2491 IF NOT FND_API.Compatible_API_Call (l_api_version,
2492 p_api_version ,
2493 l_api_name ,
2494 G_PKG_NAME)
2495 THEN
2496 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2497 END IF;
2498 -- Initialize API message list if necessary.
2499 -- Initialize message list if p_init_msg_list is set to TRUE.
2500 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2501 FND_MSG_PUB.initialize;
2502 END IF;
2503 --Added code for bug 2527058
2504 INV_ITEM_MSG.set_Message_Mode('PLSQL');
2505
2506 IF FND_MSG_PUB.Check_Msg_Level
2507 (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
2508 THEN
2509 INV_ITEM_MSG.set_Message_Level(INV_ITEM_MSG.g_Level_Warning);
2510 END IF;
2511
2512 INV_ITEM_CATEGORY_PVT.Delete_Category_Assignment
2513 (
2514 p_api_version => p_api_version
2515 , p_init_msg_list => p_init_msg_list
2516 , p_commit => p_commit
2517 , p_inventory_item_id => p_inventory_item_id
2518 , p_organization_id => p_organization_id
2519 , p_category_set_id => p_category_set_id
2520 , p_category_id => p_category_id
2521 , x_return_status => x_return_status
2522 , x_msg_count => x_msg_count
2523 , x_msg_data => x_msg_data
2524 );
2525
2526 /* IF (l_debug = 1) THEN
2530 DELETE FROM mtl_item_categories
2527 mdebug('Delete_Category_Assignment: Tracing...1');
2528 END IF;
2529
2531 WHERE category_set_id = p_category_set_id
2532 AND organization_id = p_organization_id
2533 AND inventory_item_id = p_inventory_item_id
2534 AND category_id = p_category_id;
2535
2536 IF (SQL%NOTFOUND) THEN
2537 IF (l_debug = 1) THEN
2538 mdebug('The specified Category Assignment not found');
2539 END IF;
2540 RAISE NO_DATA_FOUND;
2541 END IF;
2542 */
2543 --Ended code for bug 2527058
2544 IF (l_debug = 1) THEN
2545 mdebug('Delete_Category_Assignment: Done!!');
2546 END IF;
2547
2548 -- Standard check of p_commit.
2549 IF FND_API.To_Boolean( p_commit ) THEN
2550 COMMIT WORK;
2551 END IF;
2552
2553 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
2554 INV_ITEM_MSG.Write_List;
2555 -- Standard call to get message count and if count is 1,
2556 -- get message info.
2557 -- The client will directly display the x_msg_data (which is already
2558 -- translated) if the x_msg_count = 1;
2559 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
2560 -- Server-side procedure to access the messages, and consolidate them
2561 -- and display (or) to display one message after another.
2562 FND_MSG_PUB.Count_And_Get
2563 ( p_count => x_msg_count,
2564 p_data => x_msg_data
2565 );
2566 EXCEPTION
2567 WHEN FND_API.G_EXC_ERROR THEN
2568 ROLLBACK TO Delete_Category_Assignment_PUB;
2569 x_return_status := FND_API.G_RET_STS_ERROR;
2570 FND_MSG_PUB.Count_And_Get
2571 ( p_count => x_msg_count,
2572 p_data => x_msg_data
2573 );
2574 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2575 ROLLBACK TO Delete_Category_Assignment_PUB;
2576 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2577 FND_MSG_PUB.Count_And_Get
2578 ( p_count => x_msg_count,
2579 p_data => x_msg_data
2580 );
2581 WHEN OTHERS THEN
2582 ROLLBACK TO Delete_Category_Assignment_PUB;
2583 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2584 IF FND_MSG_PUB.Check_Msg_Level
2585 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2586 THEN
2587 FND_MSG_PUB.Add_Exc_Msg
2588 ( G_PKG_NAME ,
2589 l_api_name
2590 );
2591 END IF;
2592 FND_MSG_PUB.Count_And_Get
2593 ( p_count => x_msg_count,
2594 p_data => x_msg_data
2595 );
2596
2597 END Delete_Category_Assignment;
2598 -----------------------------------------------------------------------------
2599 -- 7. Get_Category_Rec_Type
2600 ----------------------------------------------------------------------------
2601 FUNCTION Get_Category_Rec_Type
2602 RETURN INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE IS
2603 l_category_rec_type INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE ;
2604 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2605 BEGIN
2606 RETURN l_category_rec_type;
2607 END;
2608
2609 -----------------------------------------------------------------------------
2610 -- 8. Validate_iProcurements_flags
2611 --Bug: 2645153 validating structure and iProcurement flags
2612 ----------------------------------------------------------------------------
2613 PROCEDURE Validate_iProcurements_flags
2614 (
2615 x_category_rec IN INV_ITEM_CATEGORY_PUB.CATEGORY_REC_TYPE
2616 ) IS
2617
2618 l_po_structure_id NUMBER;
2619 --Bug: 2645153 added coide to get purchasing category structure id
2620 CURSOR get_po_structure_id IS
2621 SELECT STRUCTURE_ID
2622 FROM MTL_CATEGORY_SETS MCS,
2623 MTL_DEFAULT_CATEGORY_SETS MDCS
2624 WHERE FUNCTIONAL_AREA_ID = 2
2625 AND MCS.CATEGORY_SET_ID = MDCS.CATEGORY_SET_ID;
2626
2627 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2628 BEGIN
2629 IF (l_debug = 1) THEN
2630 mdebug('checking supplier enabled flag information provided'|| x_category_rec.supplier_enabled_flag);
2631 END IF;
2632 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
2633 fnd_message.set_name('INV','INV_NOT_VALID_FLAG');
2634 fnd_message.set_token('COLUMN_NAME', 'SUPPLIER_ENABLED_FLAG');
2635 fnd_msg_pub.ADD;
2636 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
2637 IF (l_debug = 1) THEN
2638 mdebug('Invalid supplier enabled flag information provided');
2639 END IF;
2640 END IF;
2641 IF (l_debug = 1) THEN
2642 mdebug('checking web status flag information provided');
2643 END IF;
2644 /*Bug: 4494727 Commenting out the following IF condition
2648 fnd_msg_pub.ADD;
2645 IF x_category_rec.web_status NOT IN (g_YES,g_MISS_CHAR) THEN
2646 fnd_message.set_name('INV','INV_NOT_VALID_FLAG');
2647 fnd_message.set_token('COLUMN_NAME', 'WEB_STATUS');
2649 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
2650 IF (l_debug = 1) THEN
2651 mdebug('Invalid web status flag information provided');
2652 END IF;
2653 END IF;
2654 */
2655 IF (x_category_rec.supplier_enabled_flag = g_NO) --OR Bug: 4494727
2656 -- (x_category_rec.web_status = g_YES)
2657 THEN
2658 OPEN get_po_structure_id;
2659 FETCH get_po_structure_id INTO l_po_structure_id;
2660 IF (get_po_structure_id%NOTFOUND) THEN
2661 fnd_message.set_name('INV','INV_NO_DEFAULT_CSET');
2662 fnd_msg_pub.ADD;
2663 IF (l_debug = 1) THEN
2664 mdebug('No Default purchasing category set provided');
2665 END IF;
2666 CLOSE get_po_structure_id;
2667 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
2668 ELSE
2669 IF (l_po_structure_id <> x_category_rec.structure_id) THEN
2670 IF (x_category_rec.supplier_enabled_flag = g_NO) THEN
2671 fnd_message.set_name('INV','INV_SUP_ENABLED_PO_CAT_ONLY');
2672 fnd_msg_pub.ADD;
2673 END IF;
2674 /*Bug: 4494727 Commenting out the following IF condition
2675 IF (x_category_rec.web_status = g_YES) THEN
2676 fnd_message.set_name('INV','INV_CAT_ENABLED_PO_CAT_ONLY');
2677 fnd_msg_pub.ADD;
2678 END IF;
2679 */
2680 IF (l_debug = 1) THEN
2681 mdebug('Only purchasing cat can be viewable by supplier');
2682 END IF;
2683 CLOSE get_po_structure_id;
2684 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
2685 END IF;
2686 END IF;
2687 CLOSE get_po_structure_id;
2688 END IF; --if flag = 'Y'
2689 END Validate_iProcurements_flags;
2690
2691 ----------------------------------------------------------------------------
2692 -- 9. Create Valid Category
2693 -- Bug: 3093555
2694 -- API to create a valid Category in Category Sets
2695 ----------------------------------------------------------------------------
2696 PROCEDURE Create_Valid_Category(
2697 p_api_version IN NUMBER,
2698 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2699 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2700 p_category_set_id IN NUMBER,
2701 p_category_id IN NUMBER,
2702 p_parent_category_id IN NUMBER,
2703 x_return_status OUT NOCOPY VARCHAR2,
2704 x_errorcode OUT NOCOPY NUMBER,
2705 x_msg_count OUT NOCOPY NUMBER,
2706 x_msg_data OUT NOCOPY VARCHAR2
2707 ) IS
2708 -- Start OF comments
2709 -- API name : Create_Valid_Category
2710 -- TYPE : Public
2711 -- Pre-reqs : None
2712 -- FUNCTION : Create a record in mtl_category_set_valid_cats.
2713 --
2714 -- Version: Current Version 1.0
2715 -- Previous Version : None
2716 -- Notes : Stub Version
2717 --
2718 -- END OF comments
2719 l_api_name CONSTANT VARCHAR2(30) := 'Create_Valid_Category';
2720 -- On addition of any Required parameters the major version needs
2721 -- to change i.e. for eg. 1.X to 2.X.
2722 -- On addition of any Optional parameters the minor version needs
2723 -- to change i.e. for eg. X.6 to X.7.
2724 l_api_version CONSTANT NUMBER := 1.0;
2725 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2726 -- who column variables
2727 l_user_id mtl_category_set_valid_cats.created_by%TYPE;
2728 l_login_id mtl_category_set_valid_cats.last_update_login%TYPE;
2729 l_request_id mtl_category_set_valid_cats.request_id%TYPE;
2730 l_prog_appl_id mtl_category_set_valid_cats.program_application_id%TYPE;
2731 l_program_id mtl_category_set_valid_cats.program_id%TYPE;
2732 BEGIN
2733 IF l_debug = 1 THEN
2734 mdebug('Create_Valid_Category: Tracing...1');
2735 END IF;
2736 -- Standard Start of API savepoint
2737 IF FND_API.To_Boolean( p_commit ) THEN
2738 SAVEPOINT Create_Valid_Category_PUB;
2739 END IF;
2740 -- Check for call compatibility.
2741 IF NOT FND_API.Compatible_API_Call (l_api_version,
2742 p_api_version,
2743 l_api_name,
2744 G_PKG_NAME) THEN
2745 IF l_debug = 1 THEN
2746 mdebug('Create_Valid_Category: Invalid API Call');
2747 END IF;
2748 RAISE FND_API.g_EXC_ERROR;
2749 END IF;
2750 -- Initialize API message list if necessary.
2751 -- Initialize message list if p_init_msg_list is set to TRUE.
2752 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2753 FND_MSG_PUB.initialize;
2754 END IF;
2755 IF validate_category_set_params
2756 (p_validation_type => G_INSERT
2757 ,p_category_set_id => p_category_set_id
2758 ,p_category_id => p_category_id
2762 IF l_debug = 1 THEN
2759 ,p_parent_category_id => p_parent_category_id
2760 ,p_calling_api => l_api_name
2761 ) THEN
2763 mdebug('Create_Valid_Category: Inserting data into category sets ');
2764 END IF;
2765 l_user_id := fnd_global.user_id;
2766 l_login_id := fnd_global.login_id;
2767 IF l_login_id = -1 THEN
2768 l_login_id := fnd_global.conc_login_id;
2769 END IF;
2770 l_request_id := fnd_global.conc_request_id;
2771 l_prog_appl_id := fnd_global.prog_appl_id;
2772 l_program_id := fnd_global.conc_program_id;
2773 INSERT INTO mtl_category_set_valid_cats
2774 ( category_set_id
2775 , category_id
2776 , parent_category_id
2777 , created_by
2778 , creation_date
2779 , last_updated_by
2780 , last_update_date
2781 , last_update_login
2782 , request_id
2783 , program_application_id
2784 , program_id
2785 , program_update_date
2786 )
2787 VALUES
2788 ( p_category_set_id
2789 , p_category_id
2790 , p_parent_category_id
2791 , l_user_id
2792 , SYSDATE
2793 , l_user_id
2794 , SYSDATE
2795 , l_login_id
2796 , l_request_id
2797 , l_prog_appl_id
2798 , l_program_id
2799 , SYSDATE
2800 );
2801 ELSE
2802 -- passed parameters are invalid
2803 RAISE FND_API.G_EXC_ERROR;
2804 END IF;
2805
2806 -- Standard check of p_commit.
2807 IF FND_API.To_Boolean( p_commit ) THEN
2808 COMMIT WORK;
2809 END IF;
2810
2811 x_return_status := FND_API.G_RET_STS_SUCCESS;
2812 -- Standard call to get message count and if count is 1,
2813 -- get message info.
2814 -- The client will directly display the x_msg_data (which is already
2815 -- translated) if the x_msg_count = 1;
2816 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
2817 -- Server-side procedure to access the messages, and consolidate them
2818 -- and display (or) to display one message after another.
2819 FND_MSG_PUB.Count_And_Get
2820 ( p_count => x_msg_count,
2821 p_data => x_msg_data
2822 );
2823
2824 EXCEPTION
2825 WHEN FND_API.G_EXC_ERROR THEN
2826 IF l_debug = 1 THEN
2827 mdebug('Create_Valid_Category: Apps Exception raised');
2828 END IF;
2829 IF FND_API.To_Boolean( p_commit ) THEN
2830 ROLLBACK TO Create_Valid_Category_PUB;
2831 END IF;
2832 x_return_status := FND_API.G_RET_STS_ERROR;
2833 FND_MSG_PUB.Count_And_Get
2834 ( p_count => x_msg_count,
2835 p_data => x_msg_data
2836 );
2837 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2838 IF l_debug = 1 THEN
2839 mdebug('Create_Valid_Category: Apps Unexpected Error');
2840 END IF;
2841 IF FND_API.To_Boolean( p_commit ) THEN
2842 ROLLBACK TO Create_Valid_Category_PUB;
2843 END IF;
2844 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2845 FND_MSG_PUB.Count_And_Get
2846 ( p_count => x_msg_count,
2847 p_data => x_msg_data
2848 );
2849 WHEN OTHERS THEN
2850 IF l_debug = 1 THEN
2851 mdebug('Create_Valid_Category: Exception -- OTHERS ');
2852 END IF;
2853 IF FND_API.To_Boolean( p_commit ) THEN
2854 ROLLBACK TO Create_Valid_Category_PUB;
2855 END IF;
2856 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2857 IF FND_MSG_PUB.Check_Msg_Level
2858 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2859 THEN
2860 FND_MSG_PUB.Add_Exc_Msg
2861 ( G_PKG_NAME ,
2862 l_api_name
2863 );
2864 END IF;
2865 FND_MSG_PUB.Count_And_Get
2866 ( p_count => x_msg_count,
2867 p_data => x_msg_data
2868 );
2869 END Create_Valid_Category;
2870
2871 ----------------------------------------------------------------------------
2872 -- 10. Update Category
2873 -- Bug: 3093555
2874 -- API to update a valid Category
2875 ----------------------------------------------------------------------------
2876 PROCEDURE Update_Valid_Category(
2877 p_api_version IN NUMBER,
2878 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2879 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
2880 p_category_set_id IN NUMBER,
2881 p_category_id IN NUMBER,
2882 p_parent_category_id IN NUMBER,
2883 x_return_status OUT NOCOPY VARCHAR2,
2884 x_errorcode OUT NOCOPY NUMBER,
2885 x_msg_count OUT NOCOPY NUMBER,
2886 x_msg_data OUT NOCOPY VARCHAR2
2887 ) IS
2888 -- Start OF comments
2889 -- API name : Update_Valid_Category
2890 -- TYPE : Public
2891 -- Pre-reqs : None
2892 -- FUNCTION : Update record in mtl_category_set_valid_cats.
2893 --
2894 -- Version: Current Version 1.0
2895 -- Previous Version : None
2896 -- Notes : Stub Version
2897 --
2901 -- to change i.e. for eg. 1.X to 2.X.
2898 -- END OF comments
2899 l_api_name CONSTANT VARCHAR2(30) := 'Update_Valid_Category';
2900 -- On addition of any Required parameters the major version needs
2902 -- On addition of any Optional parameters the minor version needs
2903 -- to change i.e. for eg. X.6 to X.7.
2904 l_api_version CONSTANT NUMBER := 1.0;
2905 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
2906 l_user_id mtl_category_set_valid_cats.created_by%TYPE;
2907 l_login_id mtl_category_set_valid_cats.last_update_login%TYPE;
2908 BEGIN
2909 IF l_debug = 1 THEN
2910 mdebug('Update_Valid_Category: Tracing...1');
2911 END IF;
2912 -- Standard Start of API savepoint
2913 IF FND_API.To_Boolean( p_commit ) THEN
2914 SAVEPOINT Update_Valid_Category_PUB;
2915 END IF;
2916 -- Check for call compatibility.
2917 IF NOT FND_API.Compatible_API_Call (l_api_version,
2918 p_api_version,
2919 l_api_name,
2920 G_PKG_NAME) THEN
2921 IF l_debug = 1 THEN
2922 mdebug('Update_Valid_Category: Invalid API call');
2923 END IF;
2924 RAISE FND_API.g_EXC_ERROR;
2925 END IF;
2926 -- Initialize API message list if necessary.
2927 -- Initialize message list if p_init_msg_list is set to TRUE.
2928 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2929 FND_MSG_PUB.initialize;
2930 END IF;
2931
2932 IF validate_category_set_params
2933 (p_validation_type => G_UPDATE
2934 ,p_category_set_id => p_category_set_id
2935 ,p_category_id => p_category_id
2936 ,p_parent_category_id => p_parent_category_id
2937 ,p_calling_api => l_api_name
2938 ) THEN
2939 l_user_id := fnd_global.user_id;
2940 l_login_id := fnd_global.login_id;
2941 IF l_login_id = -1 THEN
2942 l_login_id := fnd_global.conc_login_id;
2943 END IF;
2944 IF l_debug = 1 THEN
2945 mdebug('Update_Valid_Category: About to update the category record');
2946 END IF;
2947 UPDATE mtl_category_set_valid_cats
2948 SET parent_category_id = p_parent_category_id
2949 ,last_updated_by = l_user_id
2950 ,last_update_date = SYSDATE
2951 ,last_update_login = l_login_id
2952 WHERE category_set_id = p_category_set_id
2953 AND category_id = p_category_id;
2954 IF (SQL%NOTFOUND) THEN
2955 IF l_debug = 1 THEN
2956 mdebug('Update_Valid_Category: Record not available for update');
2957 END IF;
2958 fnd_message.set_name('INV','INV_CATEGORY_UNAVAIL_UPDATE');
2959 fnd_msg_pub.ADD;
2960 RAISE fnd_api.g_EXC_UNEXPECTED_ERROR;
2961 END IF;
2962 ELSE
2963 -- passed parameters are invalid
2964 RAISE FND_API.G_EXC_ERROR;
2965 END IF;
2966 -- Standard check of p_commit.
2967 IF FND_API.To_Boolean( p_commit ) THEN
2968 COMMIT WORK;
2969 END IF;
2970
2971 x_return_status := FND_API.G_RET_STS_SUCCESS;
2972 -- Standard call to get message count and if count is 1,
2973 -- get message info.
2974 -- The client will directly display the x_msg_data (which is already
2975 -- translated) if the x_msg_count = 1;
2976 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
2977 -- Server-side procedure to access the messages, and consolidate them
2978 -- and display (or) to display one message after another.
2979 FND_MSG_PUB.Count_And_Get
2980 ( p_count => x_msg_count,
2981 p_data => x_msg_data
2982 );
2983
2984 EXCEPTION
2985 WHEN FND_API.G_EXC_ERROR THEN
2986 IF l_debug = 1 THEN
2987 mdebug('Update_Valid_Category: Apps Exception raised');
2988 END IF;
2989 IF FND_API.To_Boolean( p_commit ) THEN
2990 ROLLBACK TO Update_Valid_Category_PUB;
2991 END IF;
2992 x_return_status := FND_API.G_RET_STS_ERROR;
2993 FND_MSG_PUB.Count_And_Get
2994 ( p_count => x_msg_count,
2995 p_data => x_msg_data
2996 );
2997 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2998 IF l_debug = 1 THEN
2999 mdebug('Update_Valid_Category: Apps Unexpected Error');
3000 END IF;
3001 IF FND_API.To_Boolean( p_commit ) THEN
3002 ROLLBACK TO Update_Valid_Category_PUB;
3003 END IF;
3004 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3005 FND_MSG_PUB.Count_And_Get
3006 ( p_count => x_msg_count,
3007 p_data => x_msg_data
3008 );
3009 WHEN OTHERS THEN
3010 IF l_debug = 1 THEN
3011 mdebug('Update_Valid_Category: Exception -- OTHERS ');
3012 END IF;
3013 IF FND_API.To_Boolean( p_commit ) THEN
3014 ROLLBACK TO Update_Valid_Category_PUB;
3015 END IF;
3016 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3017 IF FND_MSG_PUB.Check_Msg_Level
3018 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3019 THEN
3020 FND_MSG_PUB.Add_Exc_Msg
3021 ( G_PKG_NAME ,
3025 FND_MSG_PUB.Count_And_Get
3022 l_api_name
3023 );
3024 END IF;
3026 ( p_count => x_msg_count,
3027 p_data => x_msg_data
3028 );
3029 END Update_Valid_Category;
3030
3031 ----------------------------------------------------------------------------
3032 -- 11. Delete Category
3033 -- Bug: 3093555
3034 -- API to Delete a valid Category
3035 ----------------------------------------------------------------------------
3036 PROCEDURE Delete_Valid_Category(
3037 p_api_version IN NUMBER,
3038 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3039 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
3040 p_category_set_id IN NUMBER,
3041 p_category_id IN NUMBER,
3042 x_return_status OUT NOCOPY VARCHAR2,
3043 x_errorcode OUT NOCOPY NUMBER,
3044 x_msg_count OUT NOCOPY NUMBER,
3045 x_msg_data OUT NOCOPY VARCHAR2
3046 ) IS
3047 -- Start OF comments
3048 -- API name : Delete_Valid_Category
3049 -- TYPE : Public
3050 -- Pre-reqs : None
3051 -- FUNCTION : Delete the record from mtl_category_set_valid_cats.
3052 --
3053 -- Version: Current Version 1.0
3054 -- Previous Version : None
3055 -- Notes : Stub Version
3056 --
3057 -- END OF comments
3058 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Valid_Category';
3059 -- On addition of any Required parameters the major version needs
3060 -- to change i.e. for eg. 1.X to 2.X.
3061 -- On addition of any Optional parameters the minor version needs
3062 -- to change i.e. for eg. X.6 to X.7.
3063 l_api_version CONSTANT NUMBER := 1.0;
3064 l_count NUMBER;
3065 l_debug NUMBER := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3066 l_description mtl_categories_vl.description%TYPE;
3067 l_category_id mtl_category_set_valid_cats.category_id%TYPE;
3068 l_def_category_id mtl_category_sets_b.default_category_id%TYPE;
3069 l_hrchy_enabled mtl_category_sets_b.hierarchy_enabled%TYPE;
3070
3071 CURSOR c_get_cat_desc (cp_category_id IN NUMBER) IS
3072 SELECT description
3073 FROM mtl_categories_vl
3074 WHERE category_id = cp_category_id;
3075
3076 --Added for bug 5219692
3077 CURSOR c_get_items_in_cat (cp_category_id IN NUMBER
3078 ,cp_category_set_id IN NUMBER) IS
3079 SELECT category_id
3080 FROM mtl_item_categories item_cat
3081 WHERE item_cat.category_id = cp_category_id
3082 AND item_cat.category_set_id = cp_category_set_id
3083 AND rownum = 1;
3084
3085 CURSOR c_get_items_in_cat_hrchy (cp_category_id IN NUMBER
3086 ,cp_category_set_id IN NUMBER) IS
3087 SELECT valid_cats.category_id
3088 FROM mtl_category_set_valid_cats valid_cats
3089 WHERE EXISTS
3090 (SELECT 'X'
3091 FROM mtl_item_categories item_cat
3092 WHERE item_cat.category_id = valid_cats.category_id
3093 AND item_cat.category_set_id = cp_category_set_id
3094 )
3095 CONNECT BY PRIOR
3096 valid_cats.category_id = valid_cats.parent_category_id
3097 AND valid_cats.category_set_id = cp_category_set_id
3098 START WITH
3099 valid_cats.category_id = cp_category_id
3100 AND category_set_id = cp_category_set_id
3101 AND rownum = 1;
3102
3103 --Added for bug 5219692
3104 CURSOR c_check_default_cat (cp_category_id IN NUMBER
3105 ,cp_category_set_id IN NUMBER) IS
3106 SELECT cat_sets.default_category_id
3107 FROM mtl_category_sets_b cat_sets
3108 WHERE cat_sets.category_set_id = p_category_set_id
3109 AND cat_sets.default_category_id = p_category_id
3110 AND NVL(cat_sets.validate_flag,'N') = 'Y';
3111
3112 CURSOR c_check_default_cat_hrchy (cp_category_id IN NUMBER
3113 ,cp_category_set_id IN NUMBER) IS
3114 SELECT cat_sets.default_category_id
3115 FROM mtl_category_sets_b cat_sets
3116 WHERE cat_sets.category_set_id = p_category_set_id
3117 AND EXISTS
3118 (SELECT 'X'
3119 FROM mtl_category_set_valid_cats check_cats
3120 WHERE check_cats.category_id = cat_sets.default_category_id
3121 CONNECT BY PRIOR
3122 check_cats.category_id = check_cats.parent_category_id
3123 AND check_cats.category_set_id = cp_category_set_id
3124 START WITH
3125 check_cats.category_id = cp_category_id
3126 AND check_cats.category_set_id = cp_category_set_id
3127 )
3128 AND NVL(cat_sets.validate_flag,'N') = 'Y';
3129
3130
3131 BEGIN
3132 IF l_debug = 1 THEN
3133 mdebug('Delete_Valid_Category: Tracing...1');
3134 END IF;
3135 -- Standard Start of API savepoint
3136 IF FND_API.To_Boolean( p_commit ) THEN
3137 SAVEPOINT Delete_Valid_Category_PUB;
3138 END IF;
3139 -- Check for call compatibility.
3140 IF NOT FND_API.Compatible_API_Call (l_api_version,
3141 p_api_version,
3142 l_api_name,
3143 G_PKG_NAME) THEN
3144 IF l_debug = 1 THEN
3148 END IF;
3145 mdebug('Delete_Valid_Category: Invalid API call');
3146 END IF;
3147 RAISE FND_API.G_EXC_ERROR;
3149 -- Initialize API message list if necessary.
3150 -- Initialize message list if p_init_msg_list is set to TRUE.
3151 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3152 FND_MSG_PUB.initialize;
3153 END IF;
3154
3155 IF (p_category_set_id IS NULL OR p_category_id IS NULL) THEN
3156 IF l_debug = 1 THEN
3157 mdebug('Delete_Valid_Category: Mandatory parameters missing');
3158 END IF;
3159 fnd_message.set_name('INV','INV_MISSING_REQUIRED_PARAMETER');
3160 fnd_msg_pub.ADD;
3161 RAISE fnd_api.G_EXC_ERROR;
3162 END IF;
3163
3164 IF NOT get_category_set_type(p_category_set_id => p_category_set_id
3165 ,p_category_id => p_category_id
3166 ,x_hrchy_enabled => l_hrchy_enabled) THEN
3167 IF l_debug = 1 THEN
3168 mdebug('Delete_Valid_Category: Record not available for deletion');
3169 END IF;
3170 fnd_message.set_name('INV','INV_CATEGORY_UNAVAIL_DELETE');
3171 fnd_msg_pub.ADD;
3172 RAISE fnd_api.g_EXC_ERROR;
3173 END IF;
3174
3175 -- check if the user tries to delete default cateogy of the category set
3176 IF UPPER(l_hrchy_enabled) = 'Y' THEN
3177 OPEN c_check_default_cat_hrchy (cp_category_id => p_category_id
3178 ,cp_category_set_id => p_category_set_id);
3179 FETCH c_check_default_cat_hrchy INTO l_def_category_id;
3180 IF c_check_default_cat_hrchy%NOTFOUND THEN
3181 l_def_category_id := NULL;
3182 END IF;
3183 CLOSE c_check_default_cat_hrchy;
3184 ELSE
3185 OPEN c_check_default_cat(cp_category_id => p_category_id
3186 ,cp_category_set_id => p_category_set_id);
3187 FETCH c_check_default_cat INTO l_def_category_id;
3188 IF c_check_default_cat%NOTFOUND THEN
3189 l_def_category_id := NULL;
3190 END IF;
3191 CLOSE c_check_default_cat;
3192 END IF;
3193
3194 IF l_def_category_id IS NOT NULL THEN
3195 -- default category is in the hierarchy
3196 IF l_debug = 1 THEN
3197 mdebug('Delete_Valid_Category: Cannot delete default category');
3198 END IF;
3199 OPEN c_get_cat_desc (cp_category_id => l_def_category_id);
3200 FETCH c_get_cat_desc INTO l_description;
3201 IF c_get_cat_desc%NOTFOUND THEN
3202 l_description := NULL;
3203 END IF;
3204 fnd_message.set_name('INV','INV_DELETE_DEF_CAT_ERR');
3205 fnd_message.set_token('CATEGORY_NAME', l_description);
3206 fnd_msg_pub.ADD;
3207 RAISE fnd_api.G_EXC_ERROR;
3208 END IF;
3209
3210 -- check if there are any items associated to the category / category set
3211 IF UPPER(l_hrchy_enabled) = 'Y' THEN
3212 OPEN c_get_items_in_cat_hrchy (cp_category_id => p_category_id
3213 ,cp_category_set_id => p_category_set_id);
3214 FETCH c_get_items_in_cat_hrchy INTO l_category_id;
3215 IF c_get_items_in_cat_hrchy%NOTFOUND THEN
3216 l_category_id := NULL;
3217 END IF;
3218 CLOSE c_get_items_in_cat_hrchy;
3219 ELSE
3220 OPEN c_get_items_in_cat (cp_category_id => p_category_id
3221 ,cp_category_set_id => p_category_set_id);
3222 FETCH c_get_items_in_cat INTO l_category_id;
3223 IF c_get_items_in_cat%NOTFOUND THEN
3224 l_category_id := NULL;
3225 END IF;
3226 CLOSE c_get_items_in_cat;
3227 END IF;
3228
3229 IF l_category_id IS NULL THEN
3230 IF l_debug = 1 THEN
3231 mdebug('Delete_Valid_Category: No items associated! Delete now');
3232 END IF;
3233
3234 IF UPPER(l_hrchy_enabled) = 'Y' THEN
3235 DELETE mtl_category_set_valid_cats delete_cats
3236 WHERE category_set_id = p_category_set_id
3237 AND EXISTS
3238 (SELECT 'X'
3239 FROM mtl_category_set_valid_cats
3240 WHERE category_id = delete_cats.category_id
3241 CONNECT BY PRIOR category_id = parent_category_id
3242 AND category_set_id = p_category_set_id
3243 START WITH category_id = p_category_id
3244 AND category_set_id = p_category_set_id
3245 );
3246 ELSE --Added else part for bug 5219692
3247 DELETE mtl_category_set_valid_cats delete_cats
3248 WHERE category_set_id = p_category_set_id
3249 AND category_id = p_category_id;
3250
3251 END IF;
3252 ELSE
3253 IF l_debug = 1 THEN
3254 mdebug('Delete_Valid_Category: Items ASSOCIATED!! ');
3255 END IF;
3256 OPEN c_get_cat_desc (cp_category_id => l_def_category_id);
3257 FETCH c_get_cat_desc INTO l_description;
3258 IF c_get_cat_desc%NOTFOUND THEN
3259 l_description := NULL;
3260 END IF;
3261 fnd_message.set_name('INV','INV_CATEGORY_ITEMS_EXIST');
3262 fnd_message.set_token('CATEGORY_NAME', l_description);
3263 fnd_msg_pub.ADD;
3264 RAISE FND_API.G_EXC_ERROR;
3265 END IF;
3266
3267 -- Standard check of p_commit.
3268 IF FND_API.To_Boolean( p_commit ) THEN
3269 COMMIT WORK;
3270 END IF;
3271
3275 -- The client will directly display the x_msg_data (which is already
3272 x_return_status := FND_API.G_RET_STS_SUCCESS;
3273 -- Standard call to get message count and if count is 1,
3274 -- get message info.
3276 -- translated) if the x_msg_count = 1;
3277 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
3278 -- Server-side procedure to access the messages, and consolidate them
3279 -- and display (or) to display one message after another.
3280 FND_MSG_PUB.Count_And_Get
3281 ( p_count => x_msg_count,
3282 p_data => x_msg_data
3283 );
3284
3285 EXCEPTION
3286 WHEN FND_API.G_EXC_ERROR THEN
3287 IF l_debug = 1 THEN
3288 mdebug('Delete_Valid_Category: Apps Exception raised');
3289 END IF;
3290 IF FND_API.To_Boolean( p_commit ) THEN
3291 ROLLBACK TO Delete_Valid_Category_PUB;
3292 END IF;
3293 x_return_status := FND_API.G_RET_STS_ERROR;
3294 FND_MSG_PUB.Count_And_Get
3295 ( p_count => x_msg_count,
3296 p_data => x_msg_data
3297 );
3298 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3299 IF l_debug = 1 THEN
3300 mdebug('Delete_Valid_Category: Apps Unexpected Error');
3301 END IF;
3302 IF FND_API.To_Boolean( p_commit ) THEN
3303 ROLLBACK TO Delete_Valid_Category_PUB;
3304 END IF;
3305 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3306 FND_MSG_PUB.Count_And_Get
3307 ( p_count => x_msg_count,
3308 p_data => x_msg_data
3309 );
3310 WHEN OTHERS THEN
3311 IF l_debug = 1 THEN
3312 mdebug('Delete_Valid_Category: Exception -- OTHERS ');
3313 END IF;
3314 IF FND_API.To_Boolean( p_commit ) THEN
3315 ROLLBACK TO Delete_Valid_Category_PUB;
3316 END IF;
3317 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3318 IF c_get_items_in_cat%ISOPEN THEN
3319 CLOSE c_get_items_in_cat;
3320 END IF;
3321 IF FND_MSG_PUB.Check_Msg_Level
3322 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3323 THEN
3324 FND_MSG_PUB.Add_Exc_Msg
3325 ( G_PKG_NAME ,
3326 l_api_name
3327 );
3328 END IF;
3329 FND_MSG_PUB.Count_And_Get
3330 ( p_count => x_msg_count,
3331 p_data => x_msg_data
3332 );
3333 END Delete_Valid_Category;
3334
3335 ----------------------------------------------------------------------------
3336 -- 12. Process_dml_on_row
3337 -- Bug: 5023883, Create/Update/Delete to the EGO tables
3338 ----------------------------------------------------------------------------
3339 PROCEDURE Process_Dml_On_Row
3340 (
3341 p_api_version IN NUMBER,
3342 p_category_set_id IN NUMBER,
3343 p_category_id IN NUMBER,
3344 p_mode IN VARCHAR2,
3345 x_return_status OUT NOCOPY VARCHAR2,
3346 x_errorcode OUT NOCOPY NUMBER,
3347 x_msg_count OUT NOCOPY NUMBER,
3348 x_msg_data OUT NOCOPY VARCHAR2
3349 ) IS
3350
3351 l_pk_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3352 l_data_column_name_value_pairs EGO_COL_NAME_VALUE_PAIR_ARRAY;
3353 l_attr_group_id NUMBER;
3354
3355 BEGIN
3356
3357 /*Initialize the PK column array and the attribute data array */
3358 l_pk_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
3359 EGO_COL_NAME_VALUE_PAIR_OBJ('CATEGORY_SET_ID',
3360 p_category_set_id));
3361
3362 l_data_column_name_value_pairs := EGO_COL_NAME_VALUE_PAIR_ARRAY(
3363 EGO_COL_NAME_VALUE_PAIR_OBJ('CATEGORY_ID', p_category_id));
3364
3365 EGO_USER_ATTRS_DATA_PVT.Perform_DML_On_Row(
3366 p_api_version => 1.0
3367 ,p_object_name => 'EGO_CATEGORY_SET'
3368 ,p_application_id => 431
3369 ,p_attr_group_type => 'EGO_PRODUCT_CATEGORY_SET'
3370 ,p_attr_group_name => 'SalesAndMarketing'
3371 ,p_pk_column_name_value_pairs => l_pk_column_name_value_pairs
3372 ,p_class_code_name_value_pairs => NULL
3373 ,p_data_level_name_value_pairs => l_data_column_name_value_pairs
3374 ,p_attr_name_value_pairs => null
3375 ,p_mode => p_mode
3376 ,p_use_def_vals_on_insert => FND_API.G_TRUE
3377 ,x_return_status => x_return_status
3378 ,x_errorcode => x_errorcode
3379 ,x_msg_count => x_msg_count
3380 ,x_msg_data => x_msg_data );
3381 EXCEPTION
3382
3383 WHEN OTHERS THEN
3384 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3385 x_msg_data := 'Executing - '||G_PKG_NAME||'.Process_Dml_On_Row '||SQLERRM;
3386
3387 END Process_Dml_On_Row;
3388
3389 --* Procedure Update_Category_Assignment added for Bug #3991044
3393 -- API to Update a valid Item Category Assignment
3390 ----------------------------------------------------------------------------
3391 -- 13. Update Category Assignment
3392 -- Bug: 3991044
3394 -- All the validations are taken care in the Pvt pkg,
3395 -- so calling private pkg instead.
3396 ----------------------------------------------------------------------------
3397 PROCEDURE Update_Category_Assignment
3398 (
3399 p_api_version IN NUMBER,
3400 p_init_msg_list IN VARCHAR2 ,
3401 p_commit IN VARCHAR2 ,
3402 p_category_id IN NUMBER,
3403 p_old_category_id IN NUMBER,
3404 p_category_set_id IN NUMBER,
3405 p_inventory_item_id IN NUMBER,
3406 p_organization_id IN NUMBER,
3407 x_return_status OUT NOCOPY VARCHAR2,
3408 x_errorcode OUT NOCOPY NUMBER,
3409 x_msg_count OUT NOCOPY NUMBER,
3410 x_msg_data OUT NOCOPY VARCHAR2
3411 )
3412 IS
3413 -- Start OF comments
3414 -- API name : Delete_Category_Assignment
3415 -- TYPE : Public
3416 -- Pre-reqs : None
3417 -- FUNCTION : Delete an item category assignment.
3418 --
3419 -- Version: Current Version 0.1
3420 -- Previous Version : None
3421 -- Notes : Stub Version
3422 --
3423 -- END OF comments
3424 l_api_name CONSTANT VARCHAR2(30) := 'Update_Category_Assignment';
3425 -- On addition of any Required parameters the major version needs
3426 -- to change i.e. for eg. 1.X to 2.X.
3427 -- On addition of any Optional parameters the minor version needs
3428 -- to change i.e. for eg. X.6 to X.7.
3429 l_api_version CONSTANT NUMBER := 1.0;
3430 l_row_count NUMBER;
3431 l_debug number := NVL(FND_PROFILE.VALUE('INV_DEBUG_TRACE'),0);
3432 BEGIN
3433 -- Standard Start of API savepoint
3434 SAVEPOINT Update_Category_Assignment_PUB;
3435
3436 -- Check for call compatibility.
3437 IF NOT FND_API.Compatible_API_Call (l_api_version,
3438 p_api_version ,
3439 l_api_name ,
3440 G_PKG_NAME)
3441 THEN
3442 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3443 END IF;
3444 -- Initialize API message list if necessary.
3445 -- Initialize message list if p_init_msg_list is set to TRUE.
3446 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3447 FND_MSG_PUB.initialize;
3448 END IF;
3449
3450 INV_ITEM_MSG.set_Message_Mode('PLSQL');
3451
3452 IF FND_MSG_PUB.Check_Msg_Level
3453 (FND_MSG_PUB.G_MSG_LVL_SUCCESS)
3454 THEN
3455 INV_ITEM_MSG.set_Message_Level(INV_ITEM_MSG.g_Level_Warning);
3456 END IF;
3457
3458 INV_ITEM_CATEGORY_PVT.Update_Category_Assignment
3459 (
3460 p_api_version => p_api_version
3461 , p_init_msg_list => p_init_msg_list
3462 , p_commit => p_commit
3463 , p_inventory_item_id => p_inventory_item_id
3464 , p_organization_id => p_organization_id
3465 , p_category_set_id => p_category_set_id
3466 , p_category_id => p_category_id
3467 , p_old_category_id => p_old_category_id
3468 , x_return_status => x_return_status
3469 , x_msg_count => x_msg_count
3470 , x_msg_data => x_msg_data
3471 );
3472
3473
3474 IF (l_debug = 1) THEN
3475 mdebug('Update_Category_Assignment: Done!!');
3476 END IF;
3477
3478 -- Standard check of p_commit.
3479 IF FND_API.To_Boolean( p_commit ) THEN
3480 COMMIT WORK;
3481 END IF;
3482
3483 --- Bug 6272365 Start
3484 --- x_return_status := FND_API.G_RET_STS_SUCCESS;
3485 INV_ITEM_MSG.Write_List;
3486 --- Bug 6272365 End
3487 -- Standard call to get message count and if count is 1,
3488 -- get message info.
3489 -- The client will directly display the x_msg_data (which is already
3490 -- translated) if the x_msg_count = 1;
3491 -- Else i.e if x_msg_count > 1, client will call the FND_MSG_PUB.Get
3492 -- Server-side procedure to access the messages, and consolidate them
3493 -- and display (or) to display one message after another.
3494 FND_MSG_PUB.Count_And_Get
3495 ( p_count => x_msg_count,
3496 p_data => x_msg_data
3497 );
3498 EXCEPTION
3499 WHEN FND_API.G_EXC_ERROR THEN
3500 ROLLBACK TO Update_Category_Assignment_PUB;
3501 x_return_status := FND_API.G_RET_STS_ERROR;
3502 FND_MSG_PUB.Count_And_Get
3503 ( p_count => x_msg_count,
3504 p_data => x_msg_data
3505 );
3506 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3507 ROLLBACK TO Update_Category_Assignment_PUB;
3508 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3509 FND_MSG_PUB.Count_And_Get
3510 ( p_count => x_msg_count,
3511 p_data => x_msg_data
3512 );
3513 WHEN OTHERS THEN
3514 ROLLBACK TO Update_Category_Assignment_PUB;
3515 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3516 IF FND_MSG_PUB.Check_Msg_Level
3517 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3518 THEN
3519 FND_MSG_PUB.Add_Exc_Msg
3520 ( G_PKG_NAME ,
3521 l_api_name
3522 );
3523 END IF;
3524 FND_MSG_PUB.Count_And_Get
3525 ( p_count => x_msg_count,
3526 p_data => x_msg_data
3527 );
3528
3529 END Update_Category_Assignment;
3530 --* End of code for Bug #3991044
3531
3532
3533 END INV_ITEM_CATEGORY_PUB;
[Home] [Help]