[Home] [Help]
PACKAGE BODY: APPS.OKE_K_USER_ATTRIBUTES_PUB
Source
1 PACKAGE BODY oke_k_user_attributes_pub AS
2 /* $Header: OKEPUATB.pls 120.0.12020000.2 2013/02/22 06:55:15 ansraj noship $ */
3 g_api_type CONSTANT VARCHAR2 (4) := '_PUB';
4 -- GLOBAL MESSAGE CONSTANTS
5 g_fnd_app CONSTANT VARCHAR2 (200)
6 := oke_api.g_fnd_app;
7 g_form_unable_to_reserve_rec CONSTANT VARCHAR2 (200)
8 := oke_api.g_form_unable_to_reserve_rec;
9 g_form_record_deleted CONSTANT VARCHAR2 (200)
10 := oke_api.g_form_record_deleted;
11 g_form_record_changed CONSTANT VARCHAR2 (200)
12 := oke_api.g_form_record_changed;
13 g_record_logically_deleted CONSTANT VARCHAR2 (200)
14 := oke_api.g_record_logically_deleted;
15 g_required_value CONSTANT VARCHAR2 (200)
16 := oke_api.g_required_value;
17 g_invalid_value CONSTANT VARCHAR2 (200)
18 := oke_api.g_invalid_value;
19 g_col_name_token CONSTANT VARCHAR2 (200)
20 := oke_api.g_col_name_token;
21 g_parent_table_token CONSTANT VARCHAR2 (200)
22 := oke_api.g_parent_table_token;
23 g_child_table_token CONSTANT VARCHAR2 (200)
24 := oke_api.g_child_table_token;
25 g_no_parent_record CONSTANT VARCHAR2 (200)
26 := 'OKE_NO_PARENT_RECORD';
27 g_unexpected_error CONSTANT VARCHAR2 (200)
28 := 'OKE_CONTRACTS_UNEXPECTED_ERROR';
29 g_sqlerrm_token CONSTANT VARCHAR2 (200) := 'SQLerrm';
30 g_sqlcode_token CONSTANT VARCHAR2 (200) := 'SQLcode';
31 g_exception_halt_validation EXCEPTION;
32
33 TYPE flex_ctx_col_usg_type IS RECORD (
34 end_user_column_name VARCHAR2 (30),
35 enabled_flag VARCHAR2 (1),
36 application_column_name VARCHAR2 (30),
37 DEFAULT_VALUE VARCHAR2 (2000),
38 required_flag VARCHAR2 (1),
39 form_left_prompt VARCHAR2 (80),
40 flex_value_set_id NUMBER (10)
41 );
42
43 TYPE flex_ctx_col_usg_tbl_type IS TABLE OF flex_ctx_col_usg_type
44 INDEX BY VARCHAR2 (30);
45
46 TYPE flex_ctx_rec_type IS RECORD (
47 descriptive_flex_context_name VARCHAR2 (240),
48 enabled_flag VARCHAR2 (1),
49 global_flag VARCHAR2 (1),
50 flex_ctx_col_usg_tbl flex_ctx_col_usg_tbl_type
51 );
52
53 TYPE g_flex_ctx_tbl_type IS TABLE OF flex_ctx_rec_type
54 INDEX BY VARCHAR2 (30);
55
56 g_flex_ctx_tbl g_flex_ctx_tbl_type;
57
58 CURSOR cur_desc_context
59 IS
60 SELECT descriptive_flex_context_code, descriptive_flex_context_name,
61 enabled_flag, global_flag -- Global_flag is not null
62 FROM fnd_descr_flex_contexts_vl
63 WHERE (application_id = 777)
64 AND (descriptive_flexfield_name LIKE 'OKE_K_USER_ATTRIBUTES')
65 AND enabled_flag = 'Y' --enabled_flag is not null
66 ORDER BY DECODE (global_flag, 'Y', 1, 2), descriptive_flex_context_code;
67
68 CURSOR cur_desc_context_col_usg (p_desc_flex_ctx_code VARCHAR2)
69 IS
70 SELECT end_user_column_name, enabled_flag, application_column_name,
71 DEFAULT_VALUE, required_flag, form_left_prompt,
72 flex_value_set_id
73 FROM fnd_descr_flex_col_usage_vl
74 WHERE (application_id = 777)
75 AND (descriptive_flexfield_name LIKE 'OKE_K_USER_ATTRIBUTES')
76 AND (descriptive_flex_context_code = p_desc_flex_ctx_code)
77 AND enabled_flag = 'Y';
78
79 FUNCTION get_attribute_value (
80 p_k_user_attributes_rec IN k_user_attributes_rec_type,
81 p_attribute IN VARCHAR2
82 )
83 RETURN VARCHAR2
84 IS
85 BEGIN
86 CASE UPPER (p_attribute)
87 WHEN 'USER_ATTRIBUTE01'
88 THEN
89 RETURN p_k_user_attributes_rec.user_attribute01;
90 WHEN 'USER_ATTRIBUTE02'
91 THEN
92 RETURN p_k_user_attributes_rec.user_attribute02;
93 WHEN 'USER_ATTRIBUTE03'
94 THEN
95 RETURN p_k_user_attributes_rec.user_attribute03;
96 WHEN 'USER_ATTRIBUTE04'
97 THEN
98 RETURN p_k_user_attributes_rec.user_attribute04;
99 WHEN 'USER_ATTRIBUTE05'
100 THEN
101 RETURN p_k_user_attributes_rec.user_attribute05;
102 WHEN 'USER_ATTRIBUTE06'
103 THEN
104 RETURN p_k_user_attributes_rec.user_attribute06;
105 WHEN 'USER_ATTRIBUTE07'
106 THEN
107 RETURN p_k_user_attributes_rec.user_attribute07;
108 WHEN 'USER_ATTRIBUTE08'
109 THEN
110 RETURN p_k_user_attributes_rec.user_attribute08;
111 WHEN 'USER_ATTRIBUTE09'
112 THEN
113 RETURN p_k_user_attributes_rec.user_attribute09;
114 WHEN 'USER_ATTRIBUTE10'
115 THEN
116 RETURN p_k_user_attributes_rec.user_attribute10;
117 WHEN 'USER_ATTRIBUTE11'
118 THEN
119 RETURN p_k_user_attributes_rec.user_attribute11;
120 WHEN 'USER_ATTRIBUTE12'
121 THEN
122 RETURN p_k_user_attributes_rec.user_attribute12;
123 WHEN 'USER_ATTRIBUTE13'
124 THEN
125 RETURN p_k_user_attributes_rec.user_attribute13;
126 WHEN 'USER_ATTRIBUTE14'
127 THEN
128 RETURN p_k_user_attributes_rec.user_attribute14;
129 WHEN 'USER_ATTRIBUTE15'
130 THEN
131 RETURN p_k_user_attributes_rec.user_attribute15;
132 WHEN 'USER_ATTRIBUTE16'
133 THEN
134 RETURN p_k_user_attributes_rec.user_attribute16;
135 WHEN 'USER_ATTRIBUTE17'
136 THEN
137 RETURN p_k_user_attributes_rec.user_attribute17;
138 WHEN 'USER_ATTRIBUTE18'
139 THEN
140 RETURN p_k_user_attributes_rec.user_attribute18;
141 WHEN 'USER_ATTRIBUTE19'
142 THEN
143 RETURN p_k_user_attributes_rec.user_attribute19;
144 WHEN 'USER_ATTRIBUTE20'
145 THEN
146 RETURN p_k_user_attributes_rec.user_attribute20;
147 WHEN 'USER_ATTRIBUTE21'
148 THEN
149 RETURN p_k_user_attributes_rec.user_attribute21;
150 WHEN 'USER_ATTRIBUTE22'
151 THEN
152 RETURN p_k_user_attributes_rec.user_attribute22;
153 WHEN 'USER_ATTRIBUTE23'
154 THEN
155 RETURN p_k_user_attributes_rec.user_attribute23;
156 WHEN 'USER_ATTRIBUTE24'
157 THEN
158 RETURN p_k_user_attributes_rec.user_attribute24;
159 WHEN 'USER_ATTRIBUTE25'
160 THEN
161 RETURN p_k_user_attributes_rec.user_attribute25;
162 WHEN 'USER_ATTRIBUTE26'
163 THEN
164 RETURN p_k_user_attributes_rec.user_attribute26;
165 WHEN 'USER_ATTRIBUTE27'
166 THEN
167 RETURN p_k_user_attributes_rec.user_attribute27;
168 WHEN 'USER_ATTRIBUTE28'
169 THEN
170 RETURN p_k_user_attributes_rec.user_attribute28;
171 WHEN 'USER_ATTRIBUTE29'
172 THEN
173 RETURN p_k_user_attributes_rec.user_attribute29;
174 WHEN 'USER_ATTRIBUTE30'
175 THEN
176 RETURN p_k_user_attributes_rec.user_attribute30;
177 ELSE
178 RETURN NULL;
179 END CASE;
180 END get_attribute_value;
181
182 PROCEDURE validate_value_with_valueset (
183 p_attr_value IN VARCHAR2,
184 p_flex_value_set_id IN NUMBER,
185 x_return_status OUT NOCOPY VARCHAR2
186 )
187 IS
188 CURSOR cur_val_type
189 IS
190 SELECT validation_type
191 FROM fnd_flex_value_sets
192 WHERE flex_value_set_id = p_flex_value_set_id;
193
194 l_validation_type VARCHAR2 (10);
195
196 CURSOR c1
197 IS
198 SELECT VALUE.flex_value
199 --,value.description
200 FROM fnd_flex_values_vl VALUE
201 WHERE VALUE.flex_value_set_id = p_flex_value_set_id
202 AND enabled_flag = 'Y'
203 AND TRUNC (SYSDATE) BETWEEN NVL (TRUNC (start_date_active),
204 TRUNC (SYSDATE)
205 )
206 AND NVL (TRUNC (end_date_active),
207 TRUNC (SYSDATE)
208 )
209 AND VALUE.flex_value = p_attr_value;
210
211 CURSOR c2
212 IS
213 SELECT val_tab.application_table_name, val_tab.value_column_name,
214 val_tab.id_column_name, val_tab.additional_where_clause,
215 val_tab.meaning_column_name
216 FROM fnd_flex_validation_tables val_tab
217 WHERE val_tab.flex_value_set_id = p_flex_value_set_id;
218
219 l_value VARCHAR2 (1000) := NULL;
220 c2rec c2%ROWTYPE;
221 l_select_stmt VARCHAR2 (2000);
222 value_cursor_id INTEGER;
223 ret_val INTEGER;
224 BEGIN
225 x_return_status := oke_api.g_ret_sts_success;
226
227 --
228
229 -- Get the valueset validation type
230 OPEN cur_val_type;
231
232 FETCH cur_val_type
233 INTO l_validation_type;
234
235 CLOSE cur_val_type;
236
237 -- if value set type is independent or independent translatable
238 -- Added independent translatable validation for Policy Deviations
239 IF (l_validation_type = 'I' OR l_validation_type = 'X')
240 THEN
241 OPEN c1;
242
243 FETCH c1
244 INTO l_value;
245
246 IF c1%NOTFOUND
247 THEN
248 CLOSE c1;
249
250 x_return_status := oke_api.g_ret_sts_error;
251 RETURN;
252 END IF;
253
254 CLOSE c1;
255
256 RETURN;
257 ELSIF (l_validation_type = 'F')
258 THEN
259 --set the sql statement for valueset
260 OPEN c2;
261
262 FETCH c2
263 INTO c2rec;
264
265 CLOSE c2;
266
267 IF c2rec.id_column_name IS NULL
268 THEN
269 x_return_status := oke_api.g_ret_sts_error;
270 /* Need to change this error message - First needs to test from UI and throw the same error*/
271 oke_api.set_message (p_app_name => g_app_name,
272 p_msg_name => g_invalid_value,
273 p_token1 => g_col_name_token,
274 p_token1_value => 'FLEX_VLAUE_SET'
275 );
276 RETURN;
277 END IF;
278
279 l_select_stmt :=
280 ' SELECT '
281 || NVL (c2rec.id_column_name, NULL)
282 || ' as Flex_value_id,'
283 || NVL (c2rec.value_column_name, 'null')
284 || ' as Flex_value,'
285 || NVL (c2rec.meaning_column_name, 'null')
286 || ' as Flex_meaning FROM '
287 || c2rec.application_table_name;
288
289 IF c2rec.additional_where_clause IS NOT NULL
290 THEN
291 -- If no WHERE keyword, add it
292 IF (UPPER (SUBSTR (LTRIM (c2rec.additional_where_clause), 1,
293 5)
294 ) <> 'WHERE'
295 )
296 AND (UPPER (SUBSTR (LTRIM (c2rec.additional_where_clause), 1,
297 8)
298 ) <> 'ORDER BY'
299 )
300 THEN
301 l_select_stmt := l_select_stmt || ' WHERE';
302 END IF;
303
304 -- add where clause
305 l_select_stmt :=
306 l_select_stmt || ' ' || c2rec.additional_where_clause;
307 END IF;
308
309 --doing this becuase order by may exist in where clause
310 l_select_stmt :=
311 'SELECT FLEX_VALUE FROM ('
312 || l_select_stmt
313 || ') WHERE FLEX_VALUE_ID = :1';
314 value_cursor_id := DBMS_SQL.open_cursor;
315 --parse the query
316 DBMS_SQL.parse (value_cursor_id, l_select_stmt, DBMS_SQL.native);
317 --Bind the input variable
318 DBMS_SQL.bind_variable (value_cursor_id, ':1', p_attr_value);
319 --define select lis
320 DBMS_SQL.define_column (value_cursor_id, 1, l_value, 1000);
321 --execute the query
322 ret_val := DBMS_SQL.EXECUTE (value_cursor_id);
323
324 IF DBMS_SQL.fetch_rows (value_cursor_id) <> 0
325 THEN
326 DBMS_SQL.COLUMN_VALUE (value_cursor_id, 1, l_value);
327 ELSE
328 x_return_status := oke_api.g_ret_sts_error;
329 END IF;
330
331 DBMS_SQL.close_cursor (value_cursor_id);
332 END IF;
333 EXCEPTION
334 WHEN NO_DATA_FOUND
335 THEN
336 x_return_status := oke_api.g_ret_sts_error;
337
338 --close cursors
339 IF c1%ISOPEN
340 THEN
341 CLOSE c1;
342 END IF;
343
344 IF c2%ISOPEN
345 THEN
346 CLOSE c2;
347 END IF;
348 WHEN OTHERS
349 THEN
350 x_return_status := oke_api.g_ret_sts_error;
351
352 --close cursors
353 IF c1%ISOPEN
354 THEN
355 CLOSE c1;
356 END IF;
357
358 IF c2%ISOPEN
359 THEN
360 CLOSE c2;
361 END IF;
362 END validate_value_with_valueset;
363
364 PROCEDURE validate_flex_col_usages (
365 p_context IN VARCHAR2,
366 p_k_user_attributes_rec IN k_user_attributes_rec_type,
367 x_return_status OUT NOCOPY VARCHAR2
368 )
369 IS
370 l_col_usg VARCHAR2 (240);
371 l_col_usg_rec flex_ctx_col_usg_type;
372 l_attr_value VARCHAR2 (240);
373 l_return_status VARCHAR2 (1);
374 BEGIN
375 x_return_status := oke_api.g_ret_sts_success;
376
377 IF g_flex_ctx_tbl (p_context).flex_ctx_col_usg_tbl.COUNT > 0
378 THEN
379 l_col_usg := g_flex_ctx_tbl (p_context).flex_ctx_col_usg_tbl.FIRST;
380 ELSE
381 RETURN;
382 END IF;
383
384 WHILE l_col_usg IS NOT NULL
385 LOOP
386 l_col_usg_rec :=
387 g_flex_ctx_tbl (p_context).flex_ctx_col_usg_tbl (l_col_usg);
388 -- Validate required flag
389 l_attr_value :=
390 get_attribute_value (p_k_user_attributes_rec,
391 l_col_usg_rec.application_column_name
392 );
393
394 IF NVL (l_col_usg_rec.required_flag, 'N') = 'Y'
395 THEN
396 IF l_attr_value = oke_api.g_miss_char OR l_attr_value IS NULL
397 THEN
398 x_return_status := oke_api.g_ret_sts_error;
399 oke_api.set_message
400 (p_app_name => g_app_name,
401 p_msg_name => g_required_value,
402 p_token1 => g_col_name_token,
403 p_token1_value => l_col_usg_rec.end_user_column_name
404 || '('
405 || l_col_usg_rec.application_column_name
406 || ')'
407 );
408 RAISE oke_api.g_exception_error;
409 END IF;
410 END IF;
411
412 IF l_col_usg_rec.flex_value_set_id IS NOT NULL
413 AND l_attr_value IS NOT NULL
414 AND l_attr_value <> oke_api.g_miss_char
415 THEN
416 validate_value_with_valueset (l_attr_value,
417 l_col_usg_rec.flex_value_set_id,
418 l_return_status
419 );
420
421 --- If any errors happen abort API
422 IF (l_return_status = oke_api.g_ret_sts_unexp_error)
423 THEN
424 x_return_status := l_return_status;
425 oke_api.set_message
426 (p_app_name => g_app_name,
427 p_msg_name => g_invalid_value,
428 p_token1 => g_col_name_token,
429 p_token1_value => l_col_usg_rec.end_user_column_name
430 || '('
431 || l_col_usg_rec.application_column_name
432 || ')'
433 );
434 RAISE oke_api.g_exception_unexpected_error;
435 ELSIF (l_return_status = oke_api.g_ret_sts_error)
436 THEN
437 x_return_status := l_return_status;
438 oke_api.set_message
439 (p_app_name => g_app_name,
440 p_msg_name => g_invalid_value,
441 p_token1 => g_col_name_token,
442 p_token1_value => l_col_usg_rec.end_user_column_name
443 || '('
444 || l_col_usg_rec.application_column_name
445 || ')'
446 );
447 RAISE oke_api.g_exception_error;
448 END IF;
449 END IF;
450
451 l_col_usg :=
452 g_flex_ctx_tbl (p_context).flex_ctx_col_usg_tbl.NEXT (l_col_usg);
453 END LOOP;
454 EXCEPTION
455 WHEN OTHERS
456 THEN
457 RAISE;
458 END validate_flex_col_usages;
459
460 PROCEDURE validate_flex_context (
461 p_k_user_attributes_rec IN k_user_attributes_rec_type
462 )
463 IS
464 l_ctx VARCHAR2 (61);
465 l_return_status VARCHAR2 (1);
466 BEGIN
467 -- First always,validate the global segments.
468 l_ctx := g_flex_ctx_tbl.FIRST;
469
470 IF g_flex_ctx_tbl (l_ctx).global_flag = 'Y'
471 THEN
472 validate_flex_col_usages (l_ctx,
473 p_k_user_attributes_rec,
474 l_return_status
475 );
476 END IF;
477
478 -- valdiate context segements
479 validate_flex_col_usages
480 (p_k_user_attributes_rec.user_attribute_context,
481 p_k_user_attributes_rec,
482 l_return_status
483 );
484 EXCEPTION
485 WHEN OTHERS
486 THEN
487 RAISE;
488 END validate_flex_context;
489
490 PROCEDURE validate_header_id (
491 p_k_header_id IN NUMBER,
492 x_return_status OUT NOCOPY VARCHAR2
493 )
494 IS
495 CURSOR c_val_header
496 IS
497 SELECT 'X'
498 FROM okc_k_headers_all_b c, oke_k_headers e
499 WHERE e.k_header_id = p_k_header_id AND e.k_header_id = c.ID;
500
501 l_value VARCHAR2 (1);
502 BEGIN
503 x_return_status := oke_api.g_ret_sts_success;
504
505 OPEN c_val_header;
506
507 FETCH c_val_header
508 INTO l_value;
509
510 IF c_val_header%NOTFOUND
511 THEN
512 CLOSE c_val_header;
513
514 x_return_status := oke_api.g_ret_sts_error;
515 oke_api.set_message (p_app_name => g_app_name,
516 p_msg_name => g_invalid_value,
517 p_token1 => g_col_name_token,
518 p_token1_value => 'K_HEADER_ID'
519 );
520 END IF;
521
522 CLOSE c_val_header;
523 END validate_header_id;
524
525 PROCEDURE validate_line_id (
526 p_k_header_id IN NUMBER,
527 p_k_line_id IN NUMBER,
528 x_return_status OUT NOCOPY VARCHAR2
529 )
530 IS
531 CURSOR c_val_line
532 IS
533 SELECT 'X'
534 FROM okc_k_lines_b c, oke_k_lines e
535 WHERE e.k_line_id = p_k_line_id
536 AND c.dnz_chr_id = p_k_header_id
537 AND e.k_line_id = c.ID;
538
539 l_value VARCHAR2 (1);
540 BEGIN
541 x_return_status := oke_api.g_ret_sts_success;
542
543 OPEN c_val_line;
544
545 FETCH c_val_line
546 INTO l_value;
547
548 IF c_val_line%NOTFOUND
549 THEN
550 CLOSE c_val_line;
551
552 x_return_status := oke_api.g_ret_sts_error;
553 oke_api.set_message (p_app_name => g_app_name,
554 p_msg_name => g_invalid_value,
555 p_token1 => g_col_name_token,
556 p_token1_value => 'K_LINE_ID'
557 );
558 END IF;
559
560 CLOSE c_val_line;
561 END validate_line_id;
562
563 PROCEDURE validate_user_attr_rec (
564 p_context IN VARCHAR2,
565 p_k_header_id IN NUMBER,
566 p_k_line_id IN NUMBER,
567 x_return_status OUT NOCOPY VARCHAR2
568 )
569 IS
570 CURSOR c_exists
571 IS
572 SELECT 'x'
573 FROM oke_k_user_attributes
574 WHERE k_header_id = p_k_header_id
575 AND ( k_line_id = p_k_line_id
576 OR (k_line_id IS NULL AND p_k_line_id IS NULL)
577 )
578 AND user_attribute_context = p_context;
579
580 l_exist VARCHAR2 (1) := '?';
581 BEGIN
582 x_return_status := oke_api.g_ret_sts_success;
583
584 OPEN c_exists;
585
586 FETCH c_exists
587 INTO l_exist;
588
589 CLOSE c_exists;
590
591 IF l_exist = 'x'
592 THEN
593 x_return_status := oke_api.g_ret_sts_error;
594 oke_api.set_message (p_app_name => g_app_name,
595 p_msg_name => 'OKE_KAUWB_DUPLICATE_USER_ATTR'
596 );
597 END IF;
598 END validate_user_attr_rec;
599
600 PROCEDURE populate_out_rec (
601 p_k_user_attributes_rec IN k_user_attributes_rec_type,
602 x_k_user_attributes_rec OUT NOCOPY k_user_attributes_rec_type
603 )
604 IS
605 BEGIN
606 IF p_k_user_attributes_rec.user_attribute01 = oke_api.g_miss_char
607 THEN
608 x_k_user_attributes_rec.user_attribute01 := NULL;
609 ELSE
610 x_k_user_attributes_rec.user_attribute01 :=
611 p_k_user_attributes_rec.user_attribute01;
612 END IF;
613
614 IF p_k_user_attributes_rec.user_attribute02 = oke_api.g_miss_char
615 THEN
616 x_k_user_attributes_rec.user_attribute02 := NULL;
617 ELSE
618 x_k_user_attributes_rec.user_attribute02 :=
619 p_k_user_attributes_rec.user_attribute02;
620 END IF;
621
622 IF p_k_user_attributes_rec.user_attribute03 = oke_api.g_miss_char
623 THEN
624 x_k_user_attributes_rec.user_attribute03 := NULL;
625 ELSE
626 x_k_user_attributes_rec.user_attribute03 :=
627 p_k_user_attributes_rec.user_attribute03;
628 END IF;
629
630 IF p_k_user_attributes_rec.user_attribute04 = oke_api.g_miss_char
631 THEN
632 x_k_user_attributes_rec.user_attribute04 := NULL;
633 ELSE
634 x_k_user_attributes_rec.user_attribute04 :=
635 p_k_user_attributes_rec.user_attribute04;
636 END IF;
637
638 IF p_k_user_attributes_rec.user_attribute05 = oke_api.g_miss_char
639 THEN
640 x_k_user_attributes_rec.user_attribute05 := NULL;
641 ELSE
642 x_k_user_attributes_rec.user_attribute05 :=
643 p_k_user_attributes_rec.user_attribute05;
644 END IF;
645
646 IF p_k_user_attributes_rec.user_attribute06 = oke_api.g_miss_char
647 THEN
648 x_k_user_attributes_rec.user_attribute06 := NULL;
649 ELSE
650 x_k_user_attributes_rec.user_attribute06 :=
651 p_k_user_attributes_rec.user_attribute06;
652 END IF;
653
654 IF p_k_user_attributes_rec.user_attribute07 = oke_api.g_miss_char
655 THEN
656 x_k_user_attributes_rec.user_attribute07 := NULL;
657 ELSE
658 x_k_user_attributes_rec.user_attribute07 :=
659 p_k_user_attributes_rec.user_attribute07;
660 END IF;
661
662 IF p_k_user_attributes_rec.user_attribute08 = oke_api.g_miss_char
663 THEN
664 x_k_user_attributes_rec.user_attribute08 := NULL;
665 ELSE
666 x_k_user_attributes_rec.user_attribute08 :=
667 p_k_user_attributes_rec.user_attribute08;
668 END IF;
669
670 IF p_k_user_attributes_rec.user_attribute09 = oke_api.g_miss_char
671 THEN
672 x_k_user_attributes_rec.user_attribute09 := NULL;
673 ELSE
674 x_k_user_attributes_rec.user_attribute09 :=
675 p_k_user_attributes_rec.user_attribute09;
676 END IF;
677
678 IF p_k_user_attributes_rec.user_attribute10 = oke_api.g_miss_char
679 THEN
680 x_k_user_attributes_rec.user_attribute10 := NULL;
681 ELSE
682 x_k_user_attributes_rec.user_attribute10 :=
683 p_k_user_attributes_rec.user_attribute10;
684 END IF;
685
686 IF p_k_user_attributes_rec.user_attribute11 = oke_api.g_miss_char
687 THEN
688 x_k_user_attributes_rec.user_attribute11 := NULL;
689 ELSE
690 x_k_user_attributes_rec.user_attribute11 :=
691 p_k_user_attributes_rec.user_attribute11;
692 END IF;
693
694 IF p_k_user_attributes_rec.user_attribute12 = oke_api.g_miss_char
695 THEN
696 x_k_user_attributes_rec.user_attribute12 := NULL;
697 ELSE
698 x_k_user_attributes_rec.user_attribute12 :=
699 p_k_user_attributes_rec.user_attribute12;
700 END IF;
701
702 IF p_k_user_attributes_rec.user_attribute13 = oke_api.g_miss_char
703 THEN
704 x_k_user_attributes_rec.user_attribute13 := NULL;
705 ELSE
706 x_k_user_attributes_rec.user_attribute13 :=
707 p_k_user_attributes_rec.user_attribute13;
708 END IF;
709
710 IF p_k_user_attributes_rec.user_attribute14 = oke_api.g_miss_char
711 THEN
712 x_k_user_attributes_rec.user_attribute14 := NULL;
713 ELSE
714 x_k_user_attributes_rec.user_attribute14 :=
715 p_k_user_attributes_rec.user_attribute14;
716 END IF;
717
718 IF p_k_user_attributes_rec.user_attribute15 = oke_api.g_miss_char
719 THEN
720 x_k_user_attributes_rec.user_attribute15 := NULL;
721 ELSE
722 x_k_user_attributes_rec.user_attribute16 :=
723 p_k_user_attributes_rec.user_attribute15;
724 END IF;
725
726 IF p_k_user_attributes_rec.user_attribute16 = oke_api.g_miss_char
727 THEN
728 x_k_user_attributes_rec.user_attribute16 := NULL;
729 ELSE
730 x_k_user_attributes_rec.user_attribute16 :=
731 p_k_user_attributes_rec.user_attribute16;
732 END IF;
733
734 IF p_k_user_attributes_rec.user_attribute17 = oke_api.g_miss_char
735 THEN
736 x_k_user_attributes_rec.user_attribute17 := NULL;
737 ELSE
738 x_k_user_attributes_rec.user_attribute17 :=
739 p_k_user_attributes_rec.user_attribute17;
740 END IF;
741
742 IF p_k_user_attributes_rec.user_attribute18 = oke_api.g_miss_char
743 THEN
744 x_k_user_attributes_rec.user_attribute18 := NULL;
745 ELSE
746 x_k_user_attributes_rec.user_attribute18 :=
747 p_k_user_attributes_rec.user_attribute18;
748 END IF;
749
750 IF p_k_user_attributes_rec.user_attribute19 = oke_api.g_miss_char
751 THEN
752 x_k_user_attributes_rec.user_attribute19 := NULL;
753 ELSE
754 x_k_user_attributes_rec.user_attribute19 :=
755 p_k_user_attributes_rec.user_attribute19;
756 END IF;
757
758 IF p_k_user_attributes_rec.user_attribute20 = oke_api.g_miss_char
759 THEN
760 x_k_user_attributes_rec.user_attribute20 := NULL;
761 ELSE
762 x_k_user_attributes_rec.user_attribute20 :=
763 p_k_user_attributes_rec.user_attribute20;
764 END IF;
765
766 IF p_k_user_attributes_rec.user_attribute21 = oke_api.g_miss_char
767 THEN
768 x_k_user_attributes_rec.user_attribute21 := NULL;
769 ELSE
770 x_k_user_attributes_rec.user_attribute21 :=
771 p_k_user_attributes_rec.user_attribute21;
772 END IF;
773
774 IF p_k_user_attributes_rec.user_attribute22 = oke_api.g_miss_char
775 THEN
776 x_k_user_attributes_rec.user_attribute22 := NULL;
777 ELSE
778 x_k_user_attributes_rec.user_attribute22 :=
779 p_k_user_attributes_rec.user_attribute22;
780 END IF;
781
782 IF p_k_user_attributes_rec.user_attribute23 = oke_api.g_miss_char
783 THEN
784 x_k_user_attributes_rec.user_attribute23 := NULL;
785 ELSE
786 x_k_user_attributes_rec.user_attribute23 :=
787 p_k_user_attributes_rec.user_attribute23;
788 END IF;
789
790 IF p_k_user_attributes_rec.user_attribute24 = oke_api.g_miss_char
791 THEN
792 x_k_user_attributes_rec.user_attribute24 := NULL;
793 ELSE
794 x_k_user_attributes_rec.user_attribute24 :=
795 p_k_user_attributes_rec.user_attribute24;
796 END IF;
797
798 IF p_k_user_attributes_rec.user_attribute25 = oke_api.g_miss_char
799 THEN
800 x_k_user_attributes_rec.user_attribute25 := NULL;
801 ELSE
802 x_k_user_attributes_rec.user_attribute26 :=
803 p_k_user_attributes_rec.user_attribute25;
804 END IF;
805
806 IF p_k_user_attributes_rec.user_attribute26 = oke_api.g_miss_char
807 THEN
808 x_k_user_attributes_rec.user_attribute26 := NULL;
809 ELSE
810 x_k_user_attributes_rec.user_attribute26 :=
811 p_k_user_attributes_rec.user_attribute26;
812 END IF;
813
814 IF p_k_user_attributes_rec.user_attribute27 = oke_api.g_miss_char
815 THEN
816 x_k_user_attributes_rec.user_attribute27 := NULL;
817 ELSE
818 x_k_user_attributes_rec.user_attribute27 :=
819 p_k_user_attributes_rec.user_attribute27;
820 END IF;
821
822 IF p_k_user_attributes_rec.user_attribute28 = oke_api.g_miss_char
823 THEN
824 x_k_user_attributes_rec.user_attribute28 := NULL;
825 ELSE
826 x_k_user_attributes_rec.user_attribute28 :=
827 p_k_user_attributes_rec.user_attribute28;
828 END IF;
829
830 IF p_k_user_attributes_rec.user_attribute29 = oke_api.g_miss_char
831 THEN
832 x_k_user_attributes_rec.user_attribute29 := NULL;
833 ELSE
834 x_k_user_attributes_rec.user_attribute29 :=
835 p_k_user_attributes_rec.user_attribute29;
836 END IF;
837
838 IF p_k_user_attributes_rec.user_attribute30 = oke_api.g_miss_char
839 THEN
840 x_k_user_attributes_rec.user_attribute30 := NULL;
841 ELSE
842 x_k_user_attributes_rec.user_attribute30 :=
843 p_k_user_attributes_rec.user_attribute30;
844 END IF;
845 END populate_out_rec;
846
847 ----------------- PUBLIC PROCEDURES -----------
848 PROCEDURE create_k_user_attributes (
849 p_api_version IN NUMBER,
850 p_init_msg_list IN VARCHAR2
851 DEFAULT oke_api.g_false,
852 p_k_user_attributes_rec IN k_user_attributes_rec_type,
853 x_k_user_attributes_rec OUT NOCOPY k_user_attributes_rec_type,
854 x_return_status OUT NOCOPY VARCHAR2,
855 x_msg_count OUT NOCOPY NUMBER,
856 x_msg_data OUT NOCOPY VARCHAR2
857 )
858 IS
859 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_K_USER_ATTRIBUTES';
860 l_api_version CONSTANT NUMBER := 1.0;
861 l_return_status VARCHAR2 (1) := oke_api.g_ret_sts_success;
862 l_msg_count NUMBER;
863 l_msg_data VARCHAR2 (2000);
864 x_k_user_attribute_id NUMBER;
865
866 CURSOR cur_hdr_id (p_line_id NUMBER)
867 IS
868 SELECT dnz_chr_id
869 FROM okc_k_lines_b
870 WHERE ID = p_line_id;
871
872 l_k_header_id NUMBER;
873 l_global_flag VARCHAR2 (1);
874 BEGIN
875 -- call START_ACTIVITY to create savepoint, check compatibility
876 -- and initialize message list
877 l_return_status :=
878 oke_api.start_activity (p_api_name => l_api_name,
879 p_pkg_name => g_pkg_name,
880 p_init_msg_list => p_init_msg_list,
881 l_api_version => l_api_version,
882 p_api_version => p_api_version,
883 p_api_type => g_api_type,
884 x_return_status => x_return_status
885 );
886
887 -- check if activity started successfully
888 IF (l_return_status = oke_api.g_ret_sts_unexp_error)
889 THEN
890 RAISE oke_api.g_exception_unexpected_error;
891 ELSIF (l_return_status = oke_api.g_ret_sts_error)
892 THEN
893 RAISE oke_api.g_exception_error;
894 END IF;
895
896 -- Check atleast Header id or line id must be populated. If not throw error
897 IF ( ( p_k_user_attributes_rec.k_header_id = oke_api.g_miss_num
898 OR p_k_user_attributes_rec.k_header_id IS NULL
899 )
900 AND ( p_k_user_attributes_rec.k_line_id = oke_api.g_miss_num
901 OR p_k_user_attributes_rec.k_line_id IS NULL
902 )
903 )
904 THEN
905 x_return_status := oke_api.g_ret_sts_error;
906 oke_api.set_message (p_app_name => g_app_name,
907 p_msg_name => g_required_value,
908 p_token1 => g_col_name_token,
909 p_token1_value => 'K_HEADER_ID'
910 );
911 RAISE oke_api.g_exception_error;
912 END IF;
913
914 l_k_header_id := p_k_user_attributes_rec.k_header_id;
915
916 -- If line id is populated and no header id then derive the header id.
917 IF ( ( p_k_user_attributes_rec.k_line_id <> oke_api.g_miss_num
918 AND p_k_user_attributes_rec.k_line_id IS NOT NULL
919 )
920 AND ( p_k_user_attributes_rec.k_header_id = oke_api.g_miss_num
921 OR p_k_user_attributes_rec.k_header_id IS NULL
922 )
923 )
924 THEN
925 OPEN cur_hdr_id (p_k_user_attributes_rec.k_line_id);
926
927 FETCH cur_hdr_id
928 INTO l_k_header_id;
929
930 IF cur_hdr_id%NOTFOUND
931 THEN
932 CLOSE cur_hdr_id;
933
934 x_return_status := oke_api.g_ret_sts_error;
935 oke_api.set_message (p_app_name => g_app_name,
936 p_msg_name => g_invalid_value,
937 p_token1 => g_col_name_token,
938 p_token1_value => 'K_LINE_ID'
939 );
940 RAISE oke_api.g_exception_error;
941 END IF;
942
943 CLOSE cur_hdr_id;
944 END IF;
945
946 -- validate user_attribute_context
947 IF ( p_k_user_attributes_rec.user_attribute_context =
948 oke_api.g_miss_char
949 OR p_k_user_attributes_rec.user_attribute_context IS NULL
950 )
951 THEN
952 x_return_status := oke_api.g_ret_sts_error;
953 oke_api.set_message (p_app_name => g_app_name,
954 p_msg_name => g_required_value,
955 p_token1 => g_col_name_token,
956 p_token1_value => 'USER_ATTRIBUTE_CONTEXT'
957 );
958 RAISE oke_api.g_exception_error;
959 END IF;
960
961 -- Do the existence check for header_id and line_id.
962 IF l_k_header_id IS NOT NULL AND l_k_header_id <> oke_api.g_miss_num
963 THEN
964 validate_header_id (l_k_header_id, l_return_status);
965
966 --- If any errors happen abort API
967 IF (l_return_status = oke_api.g_ret_sts_unexp_error)
968 THEN
969 RAISE oke_api.g_exception_unexpected_error;
970 ELSIF (l_return_status = oke_api.g_ret_sts_error)
971 THEN
972 RAISE oke_api.g_exception_error;
973 END IF;
974 END IF;
975
976 IF p_k_user_attributes_rec.k_line_id IS NOT NULL
977 AND p_k_user_attributes_rec.k_line_id <> oke_api.g_miss_num
978 THEN
979 validate_line_id (l_k_header_id,
980 p_k_user_attributes_rec.k_line_id,
981 l_return_status
982 );
983
984 --- If any errors happen abort API
985 IF (l_return_status = oke_api.g_ret_sts_unexp_error)
986 THEN
987 RAISE oke_api.g_exception_unexpected_error;
988 ELSIF (l_return_status = oke_api.g_ret_sts_error)
989 THEN
990 RAISE oke_api.g_exception_error;
991 END IF;
992 END IF;
993
994 IF g_flex_ctx_tbl.COUNT = 0
995 THEN
996 x_return_status := oke_api.g_ret_sts_error;
997 oke_api.set_message (p_app_name => g_app_name,
998 p_msg_name => g_invalid_value,
999 p_token1 => g_col_name_token,
1000 p_token1_value => 'DFF_SET_UP'
1001 );
1002 RAISE oke_api.g_exception_error;
1003 END IF;
1004
1005 -- Validate the context code.
1006 BEGIN
1007 l_global_flag :=
1008 g_flex_ctx_tbl (p_k_user_attributes_rec.user_attribute_context).global_flag;
1009
1010 IF (l_global_flag = 'Y')
1011 THEN
1012 x_return_status := oke_api.g_ret_sts_error;
1013 oke_api.set_message (p_app_name => g_app_name,
1014 p_msg_name => g_invalid_value,
1015 p_token1 => g_col_name_token,
1016 p_token1_value => 'USER_ATTRIBUTE_CONTEXT'
1017 );
1018 RAISE oke_api.g_exception_error;
1019 END IF;
1020 EXCEPTION
1021 WHEN NO_DATA_FOUND
1022 THEN
1023 x_return_status := oke_api.g_ret_sts_error;
1024 oke_api.set_message (p_app_name => g_app_name,
1025 p_msg_name => g_invalid_value,
1026 p_token1 => g_col_name_token,
1027 p_token1_value => 'USER_ATTRIBUTE_CONTEXT'
1028 );
1029 RAISE oke_api.g_exception_error;
1030 END;
1031
1032 -- Validate Context and its segments (col usages)
1033 validate_flex_context (p_k_user_attributes_rec);
1034 populate_out_rec (p_k_user_attributes_rec, x_k_user_attributes_rec);
1035 -- Build the out record.
1036 x_k_user_attributes_rec.creation_date := SYSDATE;
1037 x_k_user_attributes_rec.created_by := fnd_global.user_id;
1038 x_k_user_attributes_rec.last_update_date := SYSDATE;
1039 x_k_user_attributes_rec.last_updated_by := fnd_global.user_id;
1040 x_k_user_attributes_rec.last_update_login := fnd_global.login_id;
1041 x_k_user_attributes_rec.record_version_number := 1;
1042 x_k_user_attributes_rec.k_header_id := l_k_header_id;
1043
1044 IF p_k_user_attributes_rec.k_line_id = oke_api.g_miss_num
1045 THEN
1046 x_k_user_attributes_rec.k_line_id := NULL;
1047 ELSE
1048 x_k_user_attributes_rec.k_line_id :=
1049 p_k_user_attributes_rec.k_line_id;
1050 END IF;
1051
1052 x_k_user_attributes_rec.user_attribute_context :=
1053 p_k_user_attributes_rec.user_attribute_context;
1054 -- Validate for duplicate context for a given header/line id.
1055 validate_user_attr_rec (x_k_user_attributes_rec.user_attribute_context,
1056 l_k_header_id,
1057 x_k_user_attributes_rec.k_line_id,
1058 l_return_status
1059 );
1060
1061 -- BY now we would have checked for mandatory fields are populated or not
1062 -- We validate all referencial checks in the validate record.
1063 INSERT INTO oke_k_user_attributes
1064 (k_user_attribute_id,
1065 creation_date,
1066 created_by,
1067 last_update_date,
1068 last_updated_by,
1069 last_update_login,
1070 record_version_number,
1071 k_header_id,
1072 k_line_id,
1073 user_attribute_context,
1074 user_attribute01,
1075 user_attribute02,
1076 user_attribute03,
1077 user_attribute04,
1078 user_attribute05,
1079 user_attribute06,
1080 user_attribute07,
1081 user_attribute08,
1082 user_attribute09,
1083 user_attribute10,
1084 user_attribute11,
1085 user_attribute12,
1086 user_attribute13,
1087 user_attribute14,
1088 user_attribute15,
1089 user_attribute16,
1090 user_attribute17,
1091 user_attribute18,
1092 user_attribute19,
1093 user_attribute20,
1094 user_attribute21,
1095 user_attribute22,
1096 user_attribute23,
1097 user_attribute24,
1098 user_attribute25,
1099 user_attribute26,
1100 user_attribute27,
1101 user_attribute28,
1102 user_attribute29,
1103 user_attribute30
1104 )
1105 VALUES (oke_k_user_attributes_s.NEXTVAL,
1106 x_k_user_attributes_rec.creation_date,
1107 x_k_user_attributes_rec.created_by,
1108 x_k_user_attributes_rec.last_update_date,
1109 x_k_user_attributes_rec.last_updated_by,
1110 x_k_user_attributes_rec.last_update_login,
1111 x_k_user_attributes_rec.record_version_number,
1112 x_k_user_attributes_rec.k_header_id,
1113 x_k_user_attributes_rec.k_line_id,
1114 x_k_user_attributes_rec.user_attribute_context,
1115 x_k_user_attributes_rec.user_attribute01,
1116 x_k_user_attributes_rec.user_attribute02,
1117 x_k_user_attributes_rec.user_attribute03,
1118 x_k_user_attributes_rec.user_attribute04,
1119 x_k_user_attributes_rec.user_attribute05,
1120 x_k_user_attributes_rec.user_attribute06,
1121 x_k_user_attributes_rec.user_attribute07,
1122 x_k_user_attributes_rec.user_attribute08,
1123 x_k_user_attributes_rec.user_attribute09,
1124 x_k_user_attributes_rec.user_attribute10,
1125 x_k_user_attributes_rec.user_attribute11,
1126 x_k_user_attributes_rec.user_attribute12,
1127 x_k_user_attributes_rec.user_attribute13,
1128 x_k_user_attributes_rec.user_attribute14,
1129 x_k_user_attributes_rec.user_attribute15,
1130 x_k_user_attributes_rec.user_attribute16,
1131 x_k_user_attributes_rec.user_attribute17,
1132 x_k_user_attributes_rec.user_attribute18,
1133 x_k_user_attributes_rec.user_attribute19,
1134 x_k_user_attributes_rec.user_attribute20,
1135 x_k_user_attributes_rec.user_attribute21,
1136 x_k_user_attributes_rec.user_attribute22,
1137 x_k_user_attributes_rec.user_attribute23,
1138 x_k_user_attributes_rec.user_attribute24,
1139 x_k_user_attributes_rec.user_attribute25,
1140 x_k_user_attributes_rec.user_attribute26,
1141 x_k_user_attributes_rec.user_attribute27,
1142 x_k_user_attributes_rec.user_attribute28,
1143 x_k_user_attributes_rec.user_attribute29,
1144 x_k_user_attributes_rec.user_attribute30
1145 )
1146 RETURNING k_user_attribute_id
1147 INTO x_k_user_attributes_rec.k_user_attribute_id;
1148
1149 --- If any errors happen abort API
1150 IF (l_return_status = oke_api.g_ret_sts_unexp_error)
1151 THEN
1152 RAISE oke_api.g_exception_unexpected_error;
1153 ELSIF (l_return_status = oke_api.g_ret_sts_error)
1154 THEN
1155 RAISE oke_api.g_exception_error;
1156 END IF;
1157
1158 oke_api.end_activity (x_msg_count => x_msg_count,
1159 x_msg_data => x_msg_data
1160 );
1161 EXCEPTION
1162 WHEN oke_api.g_exception_error
1163 THEN
1164 x_return_status :=
1165 oke_api.handle_exceptions
1166 (p_api_name => l_api_name,
1167 p_pkg_name => g_pkg_name,
1168 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1169 x_msg_count => x_msg_count,
1170 x_msg_data => x_msg_data,
1171 p_api_type => g_api_type
1172 );
1173 WHEN oke_api.g_exception_unexpected_error
1174 THEN
1175 x_return_status :=
1176 oke_api.handle_exceptions
1177 (p_api_name => l_api_name,
1178 p_pkg_name => g_pkg_name,
1179 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1180 x_msg_count => x_msg_count,
1181 x_msg_data => x_msg_data,
1182 p_api_type => g_api_type
1183 );
1184 WHEN OTHERS
1185 THEN
1186 x_return_status :=
1187 oke_api.handle_exceptions (p_api_name => l_api_name,
1188 p_pkg_name => g_pkg_name,
1189 p_exc_name => 'OTHERS',
1190 x_msg_count => x_msg_count,
1191 x_msg_data => x_msg_data,
1192 p_api_type => g_api_type
1193 );
1194 END create_k_user_attributes;
1195
1196 PROCEDURE update_k_user_attributes (
1197 p_api_version IN NUMBER,
1198 p_init_msg_list IN VARCHAR2
1199 DEFAULT oke_api.g_false,
1200 p_k_user_attributes_rec IN k_user_attributes_rec_type,
1201 x_k_user_attributes_rec OUT NOCOPY k_user_attributes_rec_type,
1202 x_return_status OUT NOCOPY VARCHAR2,
1203 x_msg_count OUT NOCOPY NUMBER,
1204 x_msg_data OUT NOCOPY VARCHAR2
1205 )
1206 IS
1207 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_K_USER_ATTRIBUTES';
1208 l_api_version CONSTANT NUMBER := 1.0;
1209 l_return_status VARCHAR2 (1) := oke_api.g_ret_sts_success;
1210 l_msg_count NUMBER;
1211 l_msg_data VARCHAR2 (2000);
1212 l_global_flag VARCHAR2 (1);
1213
1214 PROCEDURE get_out_rec (
1215 p_k_user_attribute_id IN NUMBER,
1216 x_k_user_attributes_rec OUT NOCOPY k_user_attributes_rec_type
1217 )
1218 IS
1219 l_db_rec oke_k_user_attributes%ROWTYPE;
1220 BEGIN
1221 BEGIN
1222 SELECT *
1223 INTO l_db_rec
1224 FROM oke_k_user_attributes
1225 WHERE k_user_attribute_id = p_k_user_attribute_id;
1226 EXCEPTION
1227 WHEN NO_DATA_FOUND
1228 THEN
1229 oke_api.set_message (p_app_name => g_app_name,
1230 p_msg_name => g_invalid_value,
1231 p_token1 => g_col_name_token,
1232 p_token1_value => 'K_USER_ATTRIBUTE_ID'
1233 );
1234 RAISE oke_api.g_exception_error;
1235 END;
1236
1237 x_k_user_attributes_rec.record_version_number :=
1238 l_db_rec.record_version_number + 1;
1239 x_k_user_attributes_rec.k_header_id := l_db_rec.k_header_id;
1240 x_k_user_attributes_rec.k_line_id := l_db_rec.k_line_id;
1241
1242 IF p_k_user_attributes_rec.user_attribute_context =
1243 oke_api.g_miss_char
1244 THEN
1245 x_k_user_attributes_rec.user_attribute_context :=
1246 l_db_rec.user_attribute_context;
1247 ELSE
1248 x_k_user_attributes_rec.user_attribute_context :=
1249 p_k_user_attributes_rec.user_attribute_context;
1250 END IF;
1251
1252 IF p_k_user_attributes_rec.user_attribute01 = oke_api.g_miss_char
1253 THEN
1254 x_k_user_attributes_rec.user_attribute01 :=
1255 l_db_rec.user_attribute01;
1256 ELSE
1257 x_k_user_attributes_rec.user_attribute01 :=
1258 p_k_user_attributes_rec.user_attribute01;
1259 END IF;
1260
1261 IF p_k_user_attributes_rec.user_attribute02 = oke_api.g_miss_char
1262 THEN
1263 x_k_user_attributes_rec.user_attribute02 :=
1264 l_db_rec.user_attribute02;
1265 ELSE
1266 x_k_user_attributes_rec.user_attribute02 :=
1267 p_k_user_attributes_rec.user_attribute02;
1268 END IF;
1269
1270 IF p_k_user_attributes_rec.user_attribute03 = oke_api.g_miss_char
1271 THEN
1272 x_k_user_attributes_rec.user_attribute03 :=
1273 l_db_rec.user_attribute03;
1274 ELSE
1275 x_k_user_attributes_rec.user_attribute03 :=
1276 p_k_user_attributes_rec.user_attribute03;
1277 END IF;
1278
1279 IF p_k_user_attributes_rec.user_attribute04 = oke_api.g_miss_char
1280 THEN
1281 x_k_user_attributes_rec.user_attribute04 :=
1282 l_db_rec.user_attribute04;
1283 ELSE
1284 x_k_user_attributes_rec.user_attribute04 :=
1285 p_k_user_attributes_rec.user_attribute04;
1286 END IF;
1287
1288 IF p_k_user_attributes_rec.user_attribute05 = oke_api.g_miss_char
1289 THEN
1290 x_k_user_attributes_rec.user_attribute05 :=
1291 l_db_rec.user_attribute05;
1292 ELSE
1293 x_k_user_attributes_rec.user_attribute05 :=
1294 p_k_user_attributes_rec.user_attribute05;
1295 END IF;
1296
1297 IF p_k_user_attributes_rec.user_attribute06 = oke_api.g_miss_char
1298 THEN
1299 x_k_user_attributes_rec.user_attribute06 :=
1300 l_db_rec.user_attribute06;
1301 ELSE
1302 x_k_user_attributes_rec.user_attribute06 :=
1303 p_k_user_attributes_rec.user_attribute06;
1304 END IF;
1305
1306 IF p_k_user_attributes_rec.user_attribute07 = oke_api.g_miss_char
1307 THEN
1308 x_k_user_attributes_rec.user_attribute07 :=
1309 l_db_rec.user_attribute07;
1310 ELSE
1311 x_k_user_attributes_rec.user_attribute07 :=
1312 p_k_user_attributes_rec.user_attribute07;
1313 END IF;
1314
1315 IF p_k_user_attributes_rec.user_attribute08 = oke_api.g_miss_char
1316 THEN
1317 x_k_user_attributes_rec.user_attribute08 :=
1318 l_db_rec.user_attribute08;
1319 ELSE
1320 x_k_user_attributes_rec.user_attribute08 :=
1321 p_k_user_attributes_rec.user_attribute08;
1322 END IF;
1323
1324 IF p_k_user_attributes_rec.user_attribute09 = oke_api.g_miss_char
1325 THEN
1326 x_k_user_attributes_rec.user_attribute09 :=
1327 l_db_rec.user_attribute09;
1328 ELSE
1329 x_k_user_attributes_rec.user_attribute09 :=
1330 p_k_user_attributes_rec.user_attribute09;
1331 END IF;
1332
1333 IF p_k_user_attributes_rec.user_attribute10 = oke_api.g_miss_char
1334 THEN
1335 x_k_user_attributes_rec.user_attribute10 :=
1336 l_db_rec.user_attribute10;
1337 ELSE
1338 x_k_user_attributes_rec.user_attribute10 :=
1339 p_k_user_attributes_rec.user_attribute10;
1340 END IF;
1341
1342 IF p_k_user_attributes_rec.user_attribute11 = oke_api.g_miss_char
1343 THEN
1344 x_k_user_attributes_rec.user_attribute11 :=
1345 l_db_rec.user_attribute11;
1346 ELSE
1347 x_k_user_attributes_rec.user_attribute11 :=
1348 p_k_user_attributes_rec.user_attribute11;
1349 END IF;
1350
1351 IF p_k_user_attributes_rec.user_attribute12 = oke_api.g_miss_char
1352 THEN
1353 x_k_user_attributes_rec.user_attribute12 :=
1354 l_db_rec.user_attribute12;
1355 ELSE
1356 x_k_user_attributes_rec.user_attribute12 :=
1357 p_k_user_attributes_rec.user_attribute12;
1358 END IF;
1359
1360 IF p_k_user_attributes_rec.user_attribute13 = oke_api.g_miss_char
1361 THEN
1362 x_k_user_attributes_rec.user_attribute13 :=
1363 l_db_rec.user_attribute13;
1364 ELSE
1365 x_k_user_attributes_rec.user_attribute13 :=
1366 p_k_user_attributes_rec.user_attribute13;
1367 END IF;
1368
1369 IF p_k_user_attributes_rec.user_attribute14 = oke_api.g_miss_char
1370 THEN
1371 x_k_user_attributes_rec.user_attribute14 :=
1372 l_db_rec.user_attribute14;
1373 ELSE
1374 x_k_user_attributes_rec.user_attribute14 :=
1375 p_k_user_attributes_rec.user_attribute14;
1376 END IF;
1377
1378 IF p_k_user_attributes_rec.user_attribute15 = oke_api.g_miss_char
1379 THEN
1380 x_k_user_attributes_rec.user_attribute15 :=
1381 l_db_rec.user_attribute15;
1382 ELSE
1383 x_k_user_attributes_rec.user_attribute15 :=
1384 p_k_user_attributes_rec.user_attribute15;
1385 END IF;
1386
1387 IF p_k_user_attributes_rec.user_attribute16 = oke_api.g_miss_char
1388 THEN
1389 x_k_user_attributes_rec.user_attribute16 :=
1390 l_db_rec.user_attribute16;
1391 ELSE
1392 x_k_user_attributes_rec.user_attribute16 :=
1393 p_k_user_attributes_rec.user_attribute16;
1394 END IF;
1395
1396 IF p_k_user_attributes_rec.user_attribute17 = oke_api.g_miss_char
1397 THEN
1398 x_k_user_attributes_rec.user_attribute17 :=
1399 l_db_rec.user_attribute17;
1400 ELSE
1401 x_k_user_attributes_rec.user_attribute17 :=
1402 p_k_user_attributes_rec.user_attribute17;
1403 END IF;
1404
1405 IF p_k_user_attributes_rec.user_attribute18 = oke_api.g_miss_char
1406 THEN
1407 x_k_user_attributes_rec.user_attribute18 :=
1408 l_db_rec.user_attribute18;
1409 ELSE
1410 x_k_user_attributes_rec.user_attribute18 :=
1411 p_k_user_attributes_rec.user_attribute18;
1412 END IF;
1413
1414 IF p_k_user_attributes_rec.user_attribute19 = oke_api.g_miss_char
1415 THEN
1416 x_k_user_attributes_rec.user_attribute19 :=
1417 l_db_rec.user_attribute19;
1418 ELSE
1419 x_k_user_attributes_rec.user_attribute19 :=
1420 p_k_user_attributes_rec.user_attribute19;
1421 END IF;
1422
1423 IF p_k_user_attributes_rec.user_attribute20 = oke_api.g_miss_char
1424 THEN
1425 x_k_user_attributes_rec.user_attribute20 :=
1426 l_db_rec.user_attribute20;
1427 ELSE
1428 x_k_user_attributes_rec.user_attribute20 :=
1429 p_k_user_attributes_rec.user_attribute20;
1430 END IF;
1431
1432 IF p_k_user_attributes_rec.user_attribute21 = oke_api.g_miss_char
1433 THEN
1434 x_k_user_attributes_rec.user_attribute21 :=
1435 l_db_rec.user_attribute21;
1436 ELSE
1437 x_k_user_attributes_rec.user_attribute21 :=
1438 p_k_user_attributes_rec.user_attribute21;
1439 END IF;
1440
1441 IF p_k_user_attributes_rec.user_attribute22 = oke_api.g_miss_char
1442 THEN
1443 x_k_user_attributes_rec.user_attribute22 :=
1444 l_db_rec.user_attribute22;
1445 ELSE
1446 x_k_user_attributes_rec.user_attribute22 :=
1447 p_k_user_attributes_rec.user_attribute22;
1448 END IF;
1449
1450 IF p_k_user_attributes_rec.user_attribute23 = oke_api.g_miss_char
1451 THEN
1452 x_k_user_attributes_rec.user_attribute23 :=
1453 l_db_rec.user_attribute23;
1454 ELSE
1455 x_k_user_attributes_rec.user_attribute23 :=
1456 p_k_user_attributes_rec.user_attribute23;
1457 END IF;
1458
1459 IF p_k_user_attributes_rec.user_attribute24 = oke_api.g_miss_char
1460 THEN
1461 x_k_user_attributes_rec.user_attribute24 :=
1462 l_db_rec.user_attribute24;
1463 ELSE
1464 x_k_user_attributes_rec.user_attribute24 :=
1465 p_k_user_attributes_rec.user_attribute24;
1466 END IF;
1467
1468 IF p_k_user_attributes_rec.user_attribute25 = oke_api.g_miss_char
1469 THEN
1470 x_k_user_attributes_rec.user_attribute25 :=
1471 l_db_rec.user_attribute25;
1472 ELSE
1473 x_k_user_attributes_rec.user_attribute26 :=
1474 p_k_user_attributes_rec.user_attribute25;
1475 END IF;
1476
1477 IF p_k_user_attributes_rec.user_attribute26 = oke_api.g_miss_char
1478 THEN
1479 x_k_user_attributes_rec.user_attribute26 :=
1480 l_db_rec.user_attribute26;
1481 ELSE
1482 x_k_user_attributes_rec.user_attribute26 :=
1483 p_k_user_attributes_rec.user_attribute26;
1484 END IF;
1485
1486 IF p_k_user_attributes_rec.user_attribute27 = oke_api.g_miss_char
1487 THEN
1488 x_k_user_attributes_rec.user_attribute27 :=
1489 l_db_rec.user_attribute27;
1490 ELSE
1491 x_k_user_attributes_rec.user_attribute27 :=
1492 p_k_user_attributes_rec.user_attribute27;
1493 END IF;
1494
1495 IF p_k_user_attributes_rec.user_attribute28 = oke_api.g_miss_char
1496 THEN
1497 x_k_user_attributes_rec.user_attribute28 :=
1498 l_db_rec.user_attribute28;
1499 ELSE
1500 x_k_user_attributes_rec.user_attribute28 :=
1501 p_k_user_attributes_rec.user_attribute28;
1502 END IF;
1503
1504 IF p_k_user_attributes_rec.user_attribute29 = oke_api.g_miss_char
1505 THEN
1506 x_k_user_attributes_rec.user_attribute29 :=
1507 l_db_rec.user_attribute29;
1508 ELSE
1509 x_k_user_attributes_rec.user_attribute29 :=
1510 p_k_user_attributes_rec.user_attribute29;
1511 END IF;
1512
1513 IF p_k_user_attributes_rec.user_attribute30 = oke_api.g_miss_char
1514 THEN
1515 x_k_user_attributes_rec.user_attribute30 :=
1516 l_db_rec.user_attribute30;
1517 ELSE
1518 x_k_user_attributes_rec.user_attribute30 :=
1519 p_k_user_attributes_rec.user_attribute30;
1520 END IF;
1521 END get_out_rec;
1522 BEGIN
1523 -- call START_ACTIVITY to create savepoint, check compatibility
1524 -- and initialize message list
1525 l_return_status :=
1526 oke_api.start_activity (p_api_name => l_api_name,
1527 p_pkg_name => g_pkg_name,
1528 p_init_msg_list => p_init_msg_list,
1529 l_api_version => l_api_version,
1530 p_api_version => p_api_version,
1531 p_api_type => g_api_type,
1532 x_return_status => x_return_status
1533 );
1534
1535 -- check if activity started successfully
1536 IF (l_return_status = oke_api.g_ret_sts_unexp_error)
1537 THEN
1538 RAISE oke_api.g_exception_unexpected_error;
1539 ELSIF (l_return_status = oke_api.g_ret_sts_error)
1540 THEN
1541 RAISE oke_api.g_exception_error;
1542 END IF;
1543
1544 IF p_k_user_attributes_rec.k_user_attribute_id IS NULL
1545 OR p_k_user_attributes_rec.k_user_attribute_id = oke_api.g_miss_num
1546 THEN
1547 x_return_status := oke_api.g_ret_sts_error;
1548 oke_api.set_message (p_app_name => g_app_name,
1549 p_msg_name => g_required_value,
1550 p_token1 => g_col_name_token,
1551 p_token1_value => 'K_USER_ATTRIBUTE_ID'
1552 );
1553 RAISE oke_api.g_exception_error;
1554 END IF;
1555
1556 -- Build the record.
1557 get_out_rec (p_k_user_attributes_rec.k_user_attribute_id,
1558 x_k_user_attributes_rec
1559 );
1560
1561 -- As we are resetting the k_header_id/k_line_id, No need to valdiate the header and line id.
1562
1563 -- Do flex validaitons
1564 -- validate user_attribute_context
1565 IF ( x_k_user_attributes_rec.user_attribute_context =
1566 oke_api.g_miss_char
1567 OR x_k_user_attributes_rec.user_attribute_context IS NULL
1568 )
1569 THEN
1570 x_return_status := oke_api.g_ret_sts_error;
1571 oke_api.set_message (p_app_name => g_app_name,
1572 p_msg_name => g_required_value,
1573 p_token1 => g_col_name_token,
1574 p_token1_value => 'USER_ATTRIBUTE_CONTEXT'
1575 );
1576 RAISE oke_api.g_exception_error;
1577 END IF;
1578
1579 IF g_flex_ctx_tbl.COUNT = 0
1580 THEN
1581 x_return_status := oke_api.g_ret_sts_error;
1582 oke_api.set_message (p_app_name => g_app_name,
1583 p_msg_name => g_invalid_value,
1584 p_token1 => g_col_name_token,
1585 p_token1_value => 'DFF_SET_UP'
1586 );
1587 RAISE oke_api.g_exception_error;
1588 END IF;
1589
1590 -- Validate the context code.
1591 BEGIN
1592 l_global_flag :=
1593 g_flex_ctx_tbl (x_k_user_attributes_rec.user_attribute_context).global_flag;
1594
1595 IF (l_global_flag = 'Y')
1596 THEN
1597 x_return_status := oke_api.g_ret_sts_error;
1598 oke_api.set_message (p_app_name => g_app_name,
1599 p_msg_name => g_invalid_value,
1600 p_token1 => g_col_name_token,
1601 p_token1_value => 'USER_ATTRIBUTE_CONTEXT'
1602 );
1603 RAISE oke_api.g_exception_error;
1604 END IF;
1605 EXCEPTION
1606 WHEN NO_DATA_FOUND
1607 THEN
1608 x_return_status := oke_api.g_ret_sts_error;
1609 oke_api.set_message (p_app_name => g_app_name,
1610 p_msg_name => g_invalid_value,
1611 p_token1 => g_col_name_token,
1612 p_token1_value => 'USER_ATTRIBUTE_CONTEXT'
1613 );
1614 RAISE oke_api.g_exception_error;
1615 END;
1616
1617 -- Validate Context and its segments (col usages)
1618 validate_flex_context (x_k_user_attributes_rec);
1619 x_k_user_attributes_rec.k_user_attribute_id :=
1620 p_k_user_attributes_rec.k_user_attribute_id;
1621 x_k_user_attributes_rec.last_update_date := SYSDATE;
1622 x_k_user_attributes_rec.last_updated_by := fnd_global.user_id;
1623 x_k_user_attributes_rec.last_update_login := fnd_global.login_id;
1624
1625 -- Do update.
1626 UPDATE oke_k_user_attributes
1627 SET last_update_date = x_k_user_attributes_rec.last_update_date,
1628 last_updated_by = x_k_user_attributes_rec.last_updated_by,
1629 last_update_login = x_k_user_attributes_rec.last_update_login,
1630 record_version_number =
1631 x_k_user_attributes_rec.record_version_number,
1632 user_attribute_context =
1633 x_k_user_attributes_rec.user_attribute_context,
1634 user_attribute01 = x_k_user_attributes_rec.user_attribute01,
1635 user_attribute02 = x_k_user_attributes_rec.user_attribute02,
1636 user_attribute03 = x_k_user_attributes_rec.user_attribute03,
1637 user_attribute04 = x_k_user_attributes_rec.user_attribute04,
1638 user_attribute05 = x_k_user_attributes_rec.user_attribute05,
1639 user_attribute06 = x_k_user_attributes_rec.user_attribute06,
1640 user_attribute07 = x_k_user_attributes_rec.user_attribute07,
1641 user_attribute08 = x_k_user_attributes_rec.user_attribute08,
1642 user_attribute09 = x_k_user_attributes_rec.user_attribute09,
1643 user_attribute10 = x_k_user_attributes_rec.user_attribute10,
1644 user_attribute11 = x_k_user_attributes_rec.user_attribute11,
1645 user_attribute12 = x_k_user_attributes_rec.user_attribute12,
1646 user_attribute13 = x_k_user_attributes_rec.user_attribute13,
1647 user_attribute14 = x_k_user_attributes_rec.user_attribute14,
1648 user_attribute15 = x_k_user_attributes_rec.user_attribute15,
1649 user_attribute16 = x_k_user_attributes_rec.user_attribute16,
1650 user_attribute17 = x_k_user_attributes_rec.user_attribute17,
1651 user_attribute18 = x_k_user_attributes_rec.user_attribute18,
1652 user_attribute19 = x_k_user_attributes_rec.user_attribute19,
1653 user_attribute20 = x_k_user_attributes_rec.user_attribute20,
1654 user_attribute21 = x_k_user_attributes_rec.user_attribute21,
1655 user_attribute22 = x_k_user_attributes_rec.user_attribute22,
1656 user_attribute23 = x_k_user_attributes_rec.user_attribute23,
1657 user_attribute24 = x_k_user_attributes_rec.user_attribute24,
1658 user_attribute25 = x_k_user_attributes_rec.user_attribute25,
1659 user_attribute26 = x_k_user_attributes_rec.user_attribute26,
1660 user_attribute27 = x_k_user_attributes_rec.user_attribute27,
1661 user_attribute28 = x_k_user_attributes_rec.user_attribute28,
1662 user_attribute29 = x_k_user_attributes_rec.user_attribute29,
1663 user_attribute30 = x_k_user_attributes_rec.user_attribute30
1664 WHERE k_user_attribute_id = x_k_user_attributes_rec.k_user_attribute_id;
1665
1666 oke_api.end_activity (x_msg_count => x_msg_count,
1667 x_msg_data => x_msg_data
1668 );
1669 EXCEPTION
1670 WHEN oke_api.g_exception_error
1671 THEN
1672 x_return_status :=
1673 oke_api.handle_exceptions
1674 (p_api_name => l_api_name,
1675 p_pkg_name => g_pkg_name,
1676 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
1677 x_msg_count => x_msg_count,
1678 x_msg_data => x_msg_data,
1679 p_api_type => g_api_type
1680 );
1681 WHEN oke_api.g_exception_unexpected_error
1682 THEN
1683 x_return_status :=
1684 oke_api.handle_exceptions
1685 (p_api_name => l_api_name,
1686 p_pkg_name => g_pkg_name,
1687 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
1688 x_msg_count => x_msg_count,
1689 x_msg_data => x_msg_data,
1690 p_api_type => g_api_type
1691 );
1692 WHEN OTHERS
1693 THEN
1694 x_return_status :=
1695 oke_api.handle_exceptions (p_api_name => l_api_name,
1696 p_pkg_name => g_pkg_name,
1697 p_exc_name => 'OTHERS',
1698 x_msg_count => x_msg_count,
1699 x_msg_data => x_msg_data,
1700 p_api_type => g_api_type
1701 );
1702 END update_k_user_attributes;
1703 BEGIN
1704 -- Build the 'User Attribute' Desc Flexfield Data Structure.
1705 FOR context_rec IN cur_desc_context
1706 LOOP
1707 -- Read the Context
1708 g_flex_ctx_tbl (context_rec.descriptive_flex_context_code).descriptive_flex_context_name :=
1709 context_rec.descriptive_flex_context_name;
1710 g_flex_ctx_tbl (context_rec.descriptive_flex_context_code).enabled_flag :=
1711 context_rec.enabled_flag;
1712 g_flex_ctx_tbl (context_rec.descriptive_flex_context_code).global_flag :=
1713 context_rec.global_flag;
1714 -- Read the segments (column usages)
1715 FOR context_col_usg_rec IN
1716 cur_desc_context_col_usg (context_rec.descriptive_flex_context_code)
1717 LOOP
1718 g_flex_ctx_tbl (context_rec.descriptive_flex_context_code).flex_ctx_col_usg_tbl
1719 (context_col_usg_rec.end_user_column_name).end_user_column_name :=
1720 context_col_usg_rec.end_user_column_name;
1721 g_flex_ctx_tbl (context_rec.descriptive_flex_context_code).flex_ctx_col_usg_tbl
1722 (context_col_usg_rec.end_user_column_name).enabled_flag :=
1723 context_col_usg_rec.enabled_flag;
1724 g_flex_ctx_tbl (context_rec.descriptive_flex_context_code).flex_ctx_col_usg_tbl
1725 (context_col_usg_rec.end_user_column_name).application_column_name :=
1726 context_col_usg_rec.application_column_name;
1727 g_flex_ctx_tbl (context_rec.descriptive_flex_context_code).flex_ctx_col_usg_tbl
1728 (context_col_usg_rec.end_user_column_name).DEFAULT_VALUE :=
1729 context_col_usg_rec.DEFAULT_VALUE;
1730 g_flex_ctx_tbl (context_rec.descriptive_flex_context_code).flex_ctx_col_usg_tbl
1731 (context_col_usg_rec.end_user_column_name).required_flag :=
1732 context_col_usg_rec.required_flag;
1733 g_flex_ctx_tbl (context_rec.descriptive_flex_context_code).flex_ctx_col_usg_tbl
1734 (context_col_usg_rec.end_user_column_name).form_left_prompt :=
1735 context_col_usg_rec.form_left_prompt;
1736 g_flex_ctx_tbl (context_rec.descriptive_flex_context_code).flex_ctx_col_usg_tbl
1737 (context_col_usg_rec.end_user_column_name).flex_value_set_id :=
1738 context_col_usg_rec.flex_value_set_id;
1739 END LOOP;
1740 END LOOP;
1741 END oke_k_user_attributes_pub;