[Home] [Help]
PACKAGE BODY: APPS.OKC_RUL_PVT
Source
1 PACKAGE BODY OKC_RUL_PVT AS
2 /* $Header: OKCSRULB.pls 120.0 2005/05/26 09:43:31 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 -- skekkar
7 -- Global variables
8 --TYPE num_tbl_type is table of number index by binary_integer;
9 --TYPE varchar_tbl_type is table of varchar2(30) index by binary_integer;
10
11 /* added by marat -- begin */
12 --TYPE varchar_tbl_type1 is table of fnd_descr_flex_col_usage_vl.descriptive_flex_context_code%type index by binary_integer;
13 --TYPE varchar_tbl_type2 is table of fnd_descr_flex_col_usage_vl.end_user_column_name%type index by binary_integer;
14 --TYPE varchar_tbl_type3 is table of fnd_descr_flex_col_usage_vl.required_flag%type index by binary_integer;
15 --TYPE varchar_tbl_type4 is table of fnd_descr_flex_col_usage_vl.application_column_name%type index by binary_integer;
16 --TYPE varchar_tbl_type5 is table of fnd_descr_flex_col_usage_vl.form_left_prompt%type index by binary_integer;
17 /* added by marat -- end */
18
19 /* modified by marat -- begin */
20 --g_ddf_context_code_tbl varchar_tbl_type1; -- holds descriptive_flex_context_code
21 --g_end_user_col_name_tbl varchar_tbl_type2; -- holds END_USER_COLUMN_NAME
22 --g_flex_value_set_id_tbl num_tbl_type; -- holds FLEX_VALUE_SET_ID
23 --g_required_flag_tbl varchar_tbl_type3; -- holds REQUIRED_FLAG
24 --g_app_col_name_tbl varchar_tbl_type4; -- holds APPLICATION_COLUMN_NAME
25 --g_col_seq_no_tbl varchar_tbl_type; -- holds column seqence no
26 --g_form_left_prompt_tbl varchar_tbl_type5; -- holds form_left_prompt
27 /* modified by marat -- end */
28
29 -- changed by msengupt regarding bug#2195697 tbl_type to tbl_type1 and tbl_type5
30
31 --g_obj_ddf_context_code_tbl varchar_tbl_type1; -- holds descriptive_flex_context_code
32 --g_obj_x_tbl varchar_tbl_type; -- holds x
33 --g_obj_col_seq_no_tbl varchar_tbl_type; -- holds column seqence no
34 --g_obj_form_left_prompt_tbl varchar_tbl_type5; -- holds form_left_prompt
35
36 -- Cursors -- added 19-MAR-2002 by rgalipo -- performance bug
37
38 CURSOR c_flex_col_usage (p_context_code varchar2) IS
39 SELECT descriptive_flex_context_code
40 ,end_user_column_name
41 ,flex_value_set_id
42 ,required_flag
43 ,application_column_name
44 ,seq_no
45 ,form_left_prompt
46 FROM okc_ddf_contextcode_tmp
47 WHERE descriptive_flex_context_code = p_context_code
48 ORDER BY seq_no;
49
50 CURSOR c_descr_flex_col (p_context_code varchar2) IS
51 SELECT descriptive_flex_context_code
52 ,dummy_col
53 ,seq_no
54 ,form_left_prompt
55 FROM okc_obj_ddf_ctxcode_tmp
56 WHERE descriptive_flex_context_code = p_context_code
57 ORDER BY seq_no;
58
59
60 PROCEDURE populate_global_tab
61 (
62 p_rulv_rec IN rulv_rec_type,
63 x_return_status OUT NOCOPY VARCHAR2
64 );
65
66 PROCEDURE populate_obj_global_tab
67 (
68 p_rulv_rec IN rulv_rec_type,
69 x_return_status OUT NOCOPY VARCHAR2
70 );
71
72 p_rule_code OKC_RULE_DEFS_B.rule_code%TYPE;
73 p_appl_id OKC_RULE_DEFS_B.application_id%TYPE;
74 p_dff_name OKC_RULE_DEFS_B.descriptive_flexfield_name%TYPE;
75
76 -- skekkar
77 --
78
79
80 /*********************** HAND-CODED **************************/
81 FUNCTION Validate_Attributes
82 (p_rulv_rec IN rulv_rec_type) RETURN VARCHAR2;
83 -- G_DESCRIPTIVE_FLEXFIELD_NAME CONSTANT VARCHAR2(200) := 'OKC Rule Developer DF'; -- don't use the constant /striping/
84 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'OKC_NO_PARENT_RECORD';
85 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
86 G_LEN_CHK CONSTANT VARCHAR2(200) := 'OKC_LENGTH_EXCEEDS';
87 G_COL_LEN CONSTANT VARCHAR2(30) := 'COL_LEN';
88 G_NO_DEVELOPER_FLEX_DEFINED CONSTANT VARCHAR2(200) := 'OKC_NO_DEVELOPER_FLEX_DEFINED';
89 G_NO_VALUE_SET_DEFINED CONSTANT VARCHAR2(200) := 'OKC_NO_VALUE_SET_DEFINED';
90 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
91 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
92 G_VIEW CONSTANT VARCHAR2(200) := 'OKC_RULES_V';
93 G_DF_COUNT CONSTANT NUMBER(2) := 15;
94 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
95 g_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
96 g_clob_used VARCHAR2(1) := 'N';
97 g_package varchar2(33) := ' OKC_RUL_PVT.';
98
99 -- Start of comments
100 --
101 -- Procedure Name : validate_rgp_id
102 -- Description :
103 -- Business Rules :
104 -- Parameters :
105 -- Version : 1.0
106 -- End of comments
107
108 PROCEDURE validate_rgp_id(
109 x_return_status OUT NOCOPY VARCHAR2,
110 p_rulv_rec IN rulv_rec_type
111 ) IS
112 l_dummy_var VARCHAR2(1) := '?';
113 CURSOR l_rgpv_csr IS
114 SELECT 'x'
115 FROM OKC_RULE_GROUPS_B rgpv
116 WHERE rgpv.ID = p_rulv_rec.RGP_ID;
117 --
118 l_proc varchar2(72) := g_package||'validate_rgp_id';
119 --
120 BEGIN
121
122 -- initialize return status
123 x_return_status := OKC_API.G_RET_STS_SUCCESS;
124
125 /* The following part has been changed to check for DFF in case of rule templates */
126
127 -- data is required only if template_yn <> 'Y'
128 IF (p_rulv_rec.rgp_id = OKC_API.G_MISS_NUM OR
129 p_rulv_rec.rgp_id IS NULL) THEN
130 IF NVL(p_rulv_rec.template_yn,'N') <> 'Y' THEN
131 OKC_API.set_message(
132 p_app_name => G_APP_NAME,
133 p_msg_name => G_REQUIRED_VALUE,
134 p_token1 => G_COL_NAME_TOKEN,
135 p_token1_value => 'rgp_id');
136
137 -- notify caller of an error
138 x_return_status := OKC_API.G_RET_STS_ERROR;
139
140 -- halt validation
141 RAISE G_EXCEPTION_HALT_VALIDATION;
142 ELSE
143 RETURN;
144 END IF;
145 END IF;
146
147 -- enforce foreign key
148 OPEN l_rgpv_csr;
149 FETCH l_rgpv_csr INTO l_dummy_var;
150 CLOSE l_rgpv_csr;
151
152 -- if l_dummy_var still set to default, data was not found
153 IF (l_dummy_var = '?') THEN
154 OKC_API.set_message(
155 p_app_name => G_APP_NAME,
156 p_msg_name => G_NO_PARENT_RECORD,
157 p_token1 => G_COL_NAME_TOKEN,
158 p_token1_value => 'rgp_id',
159 p_token2 => G_CHILD_TABLE_TOKEN,
160 p_token2_value => G_VIEW,
161 p_token3 => G_PARENT_TABLE_TOKEN,
162 p_token3_value => 'OKC_RULE_GROUPS_V');
163 -- notify caller of an error
164 x_return_status := OKC_API.G_RET_STS_ERROR;
165 END IF;
166
167 EXCEPTION
168 WHEN G_EXCEPTION_HALT_VALIDATION THEN
169
170
171 -- no processing necessary; validation can continue with next column
172 NULL;
173 WHEN OTHERS THEN
174
175
176 -- store SQL error message on message stack
177 OKC_API.SET_MESSAGE(
178 p_app_name => G_APP_NAME,
179 p_msg_name => G_UNEXPECTED_ERROR,
180 p_token1 => G_SQLCODE_TOKEN,
181 p_token1_value => SQLCODE,
182 p_token2 => G_SQLERRM_TOKEN,
183 p_token2_value => SQLERRM);
184 -- notify caller of an error as UNEXPETED error
185 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
186 -- verify that cursor was closed
187 IF l_rgpv_csr%ISOPEN THEN
188 CLOSE l_rgpv_csr;
189 END IF;
190 END validate_rgp_id;
191 --
192 -- Start of comments
193 --
194 -- Procedure Name : validate_std_template_yn
195 -- Description :
196 -- Business Rules :
197 -- Parameters :
198 -- Version : 1.0
199 -- End of comments
200
201 PROCEDURE validate_std_template_yn(
202 x_return_status OUT NOCOPY VARCHAR2,
203 p_rulv_rec IN rulv_rec_type
204 ) IS
205 --
206 l_proc varchar2(72) := g_package||'validate_std_template_yn';
207 --
208 BEGIN
209
210 -- initialize return status
211 x_return_status := OKC_API.G_RET_STS_SUCCESS;
212
213 -- data is required
214 IF (p_rulv_rec.std_template_yn = OKC_API.G_MISS_CHAR OR
215 p_rulv_rec.std_template_yn IS NULL) THEN
216 OKC_API.set_message(
217 p_app_name => G_APP_NAME,
218 p_msg_name => G_REQUIRED_VALUE,
219 p_token1 => G_COL_NAME_TOKEN,
220 p_token1_value => 'std_template_yn');
221
222 -- notify caller of an error
223 x_return_status := OKC_API.G_RET_STS_ERROR;
224
225 -- halt validation
226 RAISE G_EXCEPTION_HALT_VALIDATION;
227 END IF;
228
229 -- check allowed values
230 IF (UPPER(p_rulv_rec.std_template_yn) NOT IN ('Y','N')) THEN
231 OKC_API.set_message(
232 p_app_name => G_APP_NAME,
233 p_msg_name => G_INVALID_VALUE,
234 p_token1 => G_COL_NAME_TOKEN,
235 p_token1_value => 'std_template_yn');
236
237 -- notify caller of an error
238 x_return_status := OKC_API.G_RET_STS_ERROR;
239 END IF;
240
241
242
243
244 EXCEPTION
245 WHEN G_EXCEPTION_HALT_VALIDATION THEN
246
247
248 -- no processing necessary; validation can continue with next column
249 NULL;
250 WHEN OTHERS THEN
251
252
253 -- store SQL error message on message stack
254 OKC_API.SET_MESSAGE(
255 p_app_name => G_APP_NAME,
256 p_msg_name => G_UNEXPECTED_ERROR,
257 p_token1 => G_SQLCODE_TOKEN,
258 p_token1_value => SQLCODE,
259 p_token2 => G_SQLERRM_TOKEN,
260 p_token2_value => SQLERRM);
261 -- notify caller of an error as UNEXPETED error
262 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
263 -- verify that cursor was closed
264 END validate_std_template_yn;
265 --
266 -- Start of comments
267 --
268 -- Procedure Name : validate_template_yn
269 -- Description :
270 -- Business Rules :
271 -- Parameters :
272 -- Version : 1.0
273 -- End of comments
274
275 PROCEDURE validate_template_yn(
276 x_return_status OUT NOCOPY VARCHAR2,
277 p_rulv_rec IN rulv_rec_type
278 ) IS
279 --
280 l_proc varchar2(72) := g_package||'validate_template_yn';
281 --
282 BEGIN
283
284 -- initialize return status
285 x_return_status := OKC_API.G_RET_STS_SUCCESS;
286
287 -- check allowed values
288 IF (UPPER(NVL(p_rulv_rec.template_yn,'N')) NOT IN ('Y','N')) THEN
289 OKC_API.set_message(
290 p_app_name => G_APP_NAME,
291 p_msg_name => G_INVALID_VALUE,
292 p_token1 => G_COL_NAME_TOKEN,
293 p_token1_value => 'template_yn');
294
295 -- notify caller of an error
296 x_return_status := OKC_API.G_RET_STS_ERROR;
297 END IF;
298
299
300
301
302 EXCEPTION
303 WHEN G_EXCEPTION_HALT_VALIDATION THEN
304
305
306 -- no processing necessary; validation can continue with next column
307 NULL;
308 WHEN OTHERS THEN
309
310
311 -- store SQL error message on message stack
312 OKC_API.SET_MESSAGE(
313 p_app_name => G_APP_NAME,
314 p_msg_name => G_UNEXPECTED_ERROR,
315 p_token1 => G_SQLCODE_TOKEN,
316 p_token1_value => SQLCODE,
317 p_token2 => G_SQLERRM_TOKEN,
318 p_token2_value => SQLERRM);
319 -- notify caller of an error as UNEXPETED error
320 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
321 -- verify that cursor was closed
322 END validate_template_yn;
323
324
325 -- Start of comments
326 --
327 -- Procedure Name : validate_dnz_chr_id
328 -- Description :
329 -- Business Rules :
330 -- Parameters :
331 -- Version : 1.0
332 -- End of comments
333
334 PROCEDURE validate_dnz_chr_id(
335 x_return_status OUT NOCOPY VARCHAR2,
336 p_rulv_rec IN rulv_rec_type
337 ) IS
338 l_dummy_var VARCHAR2(1) := '?';
339 CURSOR l_chrv_csr IS
340 SELECT 'x'
341 FROM OKC_K_HEADERS_B chrv
342 WHERE chrv.ID = p_rulv_rec.DNZ_CHR_ID;
343 --
344 l_proc varchar2(72) := g_package||'validate_dnz_chr_id';
345 --
346 BEGIN
347
348 -- initialize return status
349 x_return_status := OKC_API.G_RET_STS_SUCCESS;
350
351 -- data is required if std template is no
352 /* Commented as per Bug 2734573/2754239
353 IF p_rulv_rec.std_template_yn = 'N' or
354 nvl(p_rulv_rec.template_yn,'N') <> 'Y' THEN */
355 -- Modified as per Bug 2734573/2754239 - No validation is required for dnz_chr_id if it is Template
356 IF p_rulv_rec.std_template_yn = 'Y' or
357 nvl(p_rulv_rec.template_yn,'N') = 'Y' THEN
358 NULL;
359 ELSE
360 -- data required
361 IF (p_rulv_rec.dnz_chr_id = OKC_API.G_MISS_NUM OR
362 p_rulv_rec.dnz_chr_id IS NULL) THEN
363 OKC_API.set_message(
364 p_app_name => G_APP_NAME,
365 p_msg_name => G_REQUIRED_VALUE,
366 p_token1 => G_COL_NAME_TOKEN,
367 p_token1_value => 'dnz_chr_id');
368
369 -- notify caller of an error
370 x_return_status := OKC_API.G_RET_STS_ERROR;
371
372 -- halt validation
373 RAISE G_EXCEPTION_HALT_VALIDATION;
374 END IF;
375 END IF;
376
377 -- check for data before processing
378 IF (p_rulv_rec.dnz_chr_id <> OKC_API.G_MISS_NUM OR
379 p_rulv_rec.dnz_chr_id IS NOT NULL) THEN
380
381 -- enforce foreign key
382 OPEN l_chrv_csr;
383 FETCH l_chrv_csr INTO l_dummy_var;
384 CLOSE l_chrv_csr;
385
386 -- if l_dummy_var still set to default, data was not found
387 IF (l_dummy_var = '?') THEN
388 OKC_API.set_message(
389 p_app_name => G_APP_NAME,
390 p_msg_name => G_NO_PARENT_RECORD,
391 p_token1 => G_COL_NAME_TOKEN,
392 p_token1_value => 'dnz_chr_id',
393 p_token2 => G_CHILD_TABLE_TOKEN,
394 p_token2_value => G_VIEW,
395 p_token3 => G_PARENT_TABLE_TOKEN,
396 p_token3_value => 'OKC_K_HEADERS_V');
397
398 -- notify caller of an error
399 x_return_status := OKC_API.G_RET_STS_ERROR;
400 END IF;
401 END IF;
402
403
404
405
406 EXCEPTION
407 WHEN G_EXCEPTION_HALT_VALIDATION THEN
408
409
410 -- no processing necessary; validation can continue with next column
411 NULL;
412 WHEN OTHERS THEN
413
414
415 -- store SQL error message on message stack
416 OKC_API.SET_MESSAGE(
417 p_app_name => g_app_name,
418 p_msg_name => g_unexpected_error,
419 p_token1 => g_sqlcode_token,
420 p_token1_value => sqlcode,
421 p_token2 => g_sqlerrm_token,
422 p_token2_value => sqlerrm);
423 -- notify caller of an error as UNEXPETED error
424 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
425 -- verify that cursor was closed
426 IF l_chrv_csr%ISOPEN THEN
427 CLOSE l_chrv_csr;
428 END IF;
429 END validate_dnz_chr_id;
430 --
431 -- Start of comments
432 --
433 -- Procedure Name : validate_warn_yn
434 -- Description :
435 -- Business Rules :
436 -- Parameters :
437 -- Version : 1.0
438 -- End of comments
439
440 PROCEDURE validate_warn_yn(
441 x_return_status OUT NOCOPY VARCHAR2,
442 p_rulv_rec IN rulv_rec_type
443 ) IS
444 --
445 l_proc varchar2(72) := g_package||'validate_warn_yn';
446 --
447 BEGIN
448
449
450
451
452 -- initialize return status
453 x_return_status := OKC_API.G_RET_STS_SUCCESS;
454
455 -- data is required
456 IF (p_rulv_rec.warn_yn = OKC_API.G_MISS_CHAR OR
457 p_rulv_rec.warn_yn IS NULL) THEN
458 OKC_API.set_message(
459 p_app_name => G_APP_NAME,
460 p_msg_name => G_REQUIRED_VALUE,
461 p_token1 => G_COL_NAME_TOKEN,
462 p_token1_value => 'warn_yn');
463
464 -- notify caller of an error
465 x_return_status := OKC_API.G_RET_STS_ERROR;
466
467 -- halt validation
468 RAISE G_EXCEPTION_HALT_VALIDATION;
469 END IF;
470
471 -- check allowed values
472 IF (UPPER(p_rulv_rec.warn_yn) NOT IN ('Y','N')) THEN
473 OKC_API.set_message(
474 p_app_name => G_APP_NAME,
475 p_msg_name => G_INVALID_VALUE,
476 p_token1 => G_COL_NAME_TOKEN,
477 p_token1_value => 'warn_yn');
478
479 -- notify caller of an error
480 x_return_status := OKC_API.G_RET_STS_ERROR;
481 END IF;
482
483
484
485
486 EXCEPTION
487 WHEN G_EXCEPTION_HALT_VALIDATION THEN
488
489
490 -- no processing necessary; validation can continue with next column
491 NULL;
492 WHEN OTHERS THEN
493
494
495 -- store SQL error message on message stack
496 OKC_API.SET_MESSAGE(
497 p_app_name => G_APP_NAME,
498 p_msg_name => G_UNEXPECTED_ERROR,
499 p_token1 => G_SQLCODE_TOKEN,
500 p_token1_value => SQLCODE,
501 p_token2 => G_SQLERRM_TOKEN,
502 p_token2_value => SQLERRM);
503 -- notify caller of an error as UNEXPETED error
504 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
505 -- verify that cursor was closed
506 END validate_warn_yn;
507 --
508 -- Start of comments
509 --
510 -- Procedure Name : validate_rule_info_catagory
511 -- Description :
512 -- Business Rules :
513 -- Parameters :
514 -- Version : 1.0
515 -- End of comments
516
517 /* The following procedure has been changed to check for DFF in case of rule templates */
518
519 PROCEDURE validate_rule_info_category(
520 x_return_status OUT NOCOPY VARCHAR2,
521 p_rulv_rec IN rulv_rec_type
522 ) IS
523
524 l_dummy_var VARCHAR2(1) := '?';
525 CURSOR l_dfc_csr (appl_id number, dff_name varchar2) IS
526 SELECT 'x'
527 FROM FND_DESCR_FLEX_CONTEXTS_VL dfc
528 -- WHERE dfc.application_id = 510 -- Application id for Contracts -- /striping/
529 WHERE dfc.application_id = appl_id -- Application id for Contracts
530 -- AND dfc.descriptive_flexfield_name = G_DESCRIPTIVE_FLEXFIELD_NAME -- /striping/
531 AND dfc.descriptive_flexfield_name = dff_name
532 AND dfc.descriptive_flex_context_code = p_rulv_rec.rule_information_category;
533
534 CURSOR l_rgrv_csr IS
535 SELECT 'x'
536 FROM OKC_RULE_GROUPS_B rgpv,
537 OKC_RG_DEF_RULES rgrv
538 WHERE rgrv.RGD_CODE = rgpv.RGD_CODE
539 AND rgpv.ID = p_rulv_rec.rgp_id
540 AND rgrv.RDF_CODE = p_rulv_rec.rule_information_category;
541 --
542 l_proc varchar2(72) := g_package||'validate_rule_info_category';
543 --
544 BEGIN
545
546 -- initialize return status
547 x_return_status := OKC_API.G_RET_STS_SUCCESS;
548
549 -- data is required
550 IF (p_rulv_rec.rule_information_category = OKC_API.G_MISS_CHAR OR
551 p_rulv_rec.rule_information_category IS NULL) THEN
552 OKC_API.set_message(
553 p_app_name => G_APP_NAME,
554 p_msg_name => G_REQUIRED_VALUE,
555 p_token1 => G_COL_NAME_TOKEN,
556 p_token1_value => 'rule_information_category');
557
558 -- notify caller of an error
559 x_return_status := OKC_API.G_RET_STS_ERROR;
560
561 -- halt validation
562 RAISE G_EXCEPTION_HALT_VALIDATION;
563 END IF;
564
565 -- /striping/
566 p_appl_id := okc_rld_pvt.get_appl_id(p_rulv_rec.rule_information_category);
567 p_dff_name := okc_rld_pvt.get_dff_name(p_rulv_rec.rule_information_category);
568
569 /* The following part has been changed to check for DFF in case of rule templates */
570 -- check for descriptive flex, it must be defined
571 IF (p_rulv_rec.rgp_id IS NULL OR
572 p_rulv_rec.rgp_id = OKC_API.G_MISS_NUM) Then
573 -- OPEN l_dfc_csr; -- /striping/
574 OPEN l_dfc_csr(p_appl_id,p_dff_name);
575 FETCH l_dfc_csr INTO l_dummy_var;
576 CLOSE l_dfc_csr;
577
578 -- if l_dummy_var still set to default, data was not found
579 IF (l_dummy_var = '?') THEN
580 OKC_API.set_message(
581 p_app_name => G_APP_NAME,
582 p_msg_name => G_INVALID_VALUE,
583 p_token1 => G_COL_NAME_TOKEN,
584 p_token1_value => 'rule_information_category');
585 -- notify caller of an error
586 x_return_status := OKC_API.G_RET_STS_ERROR;
587 END IF;
588 ELSE
589 OPEN l_rgrv_csr;
590 FETCH l_rgrv_csr INTO l_dummy_var;
591 CLOSE l_rgrv_csr;
592
593 -- if l_dummy_var still set to default, data was not found
594 IF (l_dummy_var = '?') THEN
595 OKC_API.set_message(
596 p_app_name => G_APP_NAME,
597 p_msg_name => G_INVALID_VALUE,
598 p_token1 => G_COL_NAME_TOKEN,
599 p_token1_value => 'rule_information_category');
600 -- notify caller of an error
601 x_return_status := OKC_API.G_RET_STS_ERROR;
602 END IF;
603 END IF;
604
605 EXCEPTION
606 WHEN G_EXCEPTION_HALT_VALIDATION THEN
607
608 -- no processing necessary; validation can continue with next column
609 NULL;
610 WHEN OTHERS THEN
611
612 -- store SQL error message on message stack
613 OKC_API.SET_MESSAGE(
614 p_app_name => G_APP_NAME,
615 p_msg_name => G_UNEXPECTED_ERROR,
616 p_token1 => G_SQLCODE_TOKEN,
617 p_token1_value => SQLCODE,
618 p_token2 => G_SQLERRM_TOKEN,
619 p_token2_value => SQLERRM);
620 -- notify caller of an error as UNEXPETED error
621 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
622 -- verify that cursor was closed
623 IF l_rgrv_csr%ISOPEN THEN
624 CLOSE l_rgrv_csr;
625 END IF;
626 END validate_rule_info_category;
627 --
628 -- Start of comments
629 --
630 -- Procedure Name : checknumlen
631 -- Description :
632 -- Business Rules :
633 -- Parameters :
634 -- Version : 1.0
635 -- End of comments
636 PROCEDURE checknumlen(
637 p_col_name IN VARCHAR2,
638 p_col_value IN NUMBER,
639 p_length IN OUT NOCOPY NUMBER,
640 p_scale IN NUMBER,
641 x_return_status OUT NOCOPY VARCHAR2
642 ) IS
643 i NUMBER := 1;
644 l_pre NUMBER := 0;
645 l_scale NUMBER := 0;
646 l_str_pos VARCHAR2(40) := '';
647 l_pos NUMBER := 0;
648 l_neg NUMBER := 0;
649 l_value NUMBER := 0;
650 l_format varchar2(10);
651 --
652 l_proc varchar2(72) := g_package||'checknumlen';
653 --
654 BEGIN
655
656 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
657
658 -- There is a bug in the flex field form that will not allow you
659 -- to set the maximum size of a number to more than 38. But 39 is the max.
660 IF p_length = 38 THEN
661 p_length := 39;
662 END IF;
663
664 l_value := NVL(p_col_value,0);
665 l_pre := p_length - ABS(p_scale);
666 for j in 1..l_pre loop
667 l_str_pos := l_str_pos||'9';
668 end loop;
669 l_scale := p_scale;
670 IF (l_scale>0) THEN
671 l_str_pos:=l_str_pos||'.';
672 FOR j in 1..l_scale LOOP
673 l_str_pos := l_str_pos||'9';
674 END LOOP;
675 ELSIF (l_scale < 0) THEN
676 FOR j in 1..ABS(l_scale) LOOP
677 l_str_pos := l_str_pos||'0';
678 END LOOP;
679 END IF;
680 l_pos:=to_number(l_str_pos);
681 l_neg:=(-1)*l_pos;
682 IF l_value<=l_pos and l_value>=l_neg THEN
683 x_return_status := OKC_API.G_RET_STS_SUCCESS;
684 ELSE
685 x_return_status := OKC_API.G_RET_STS_ERROR;
686 if (p_scale is not NULL) then
687 l_format := '('||to_char(p_length)||'.'||to_char(p_scale)||')';
688 else
689 l_format := to_char(p_length);
690 end if;
691 OKC_API.SET_MESSAGE
692 (p_app_name => G_APP_NAME,
693 p_msg_name => G_LEN_CHK,
694 p_token1 => G_COL_NAME_TOKEN,
695 p_token1_value => 'Column '||UPPER(p_col_name)|| ' in view ' || G_VIEW,
696 p_token2 => G_COL_LEN,
697 p_token2_value => l_format);
698 END IF;
699
700
701
702
703 EXCEPTION
704 WHEN OTHERS THEN
705
706
707 -- store SQL error message on message stack
708 OKC_API.SET_MESSAGE(
709 p_app_name => G_APP_NAME,
710 p_msg_name => G_UNEXPECTED_ERROR,
711 p_token1 => G_SQLCODE_TOKEN,
712 p_token1_value => SQLCODE,
713 p_token2 => G_SQLERRM_TOKEN,
714 p_token2_value => SQLERRM);
715 -- notify caller of an error as UNEXPETED error
716 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
717 END checknumlen;
718 --
719 -- Start of comments
720 --
721 -- Procedure Name : checkcharlen
722 -- Description :
723 -- Business Rules :
724 -- Parameters :
725 -- Version : 1.0
726 -- End of comments
727 PROCEDURE checkcharlen(
728 p_col_name IN VARCHAR2,
729 p_col_value IN VARCHAR2,
730 p_length IN NUMBER,
731 x_return_status OUT NOCOPY VARCHAR2
732 ) IS
733 col_len number:=0;
734 --
735 l_proc varchar2(72) := g_package||'checkcharlen';
736 --
737 BEGIN
738
739
740
741
742 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
743
744 col_len := nvl(length(p_col_value),0);
745 IF col_len <= TRUNC((p_length)/3) THEN
746 x_return_status:=OKC_API.G_RET_STS_SUCCESS;
747 ELSE
748 x_return_status := OKC_API.G_RET_STS_ERROR;
749 OKC_API.SET_MESSAGE
750 (p_app_name => G_APP_NAME,
751 p_msg_name => G_LEN_CHK,
752 p_token1 => G_COL_NAME_TOKEN,
753 p_token1_value => 'Column '||UPPER(p_col_name)|| ' in view ' || G_VIEW,
754 p_token2 => G_COL_LEN,
755 p_token2_value => to_char(TRUNC((p_length)/3)));
756
757 END IF;
758
759
760
761
762 EXCEPTION
763 WHEN OTHERS THEN
764
765
766 -- store SQL error message on message stack
767 OKC_API.SET_MESSAGE(
768 p_app_name => G_APP_NAME,
769 p_msg_name => G_UNEXPECTED_ERROR,
770 p_token1 => G_SQLCODE_TOKEN,
771 p_token1_value => SQLCODE,
772 p_token2 => G_SQLERRM_TOKEN,
773 p_token2_value => SQLERRM);
774 -- notify caller of an error as UNEXPETED error
775 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
776 END checkcharlen;
777 --
778 -- Start of comments
779 --
780 -- Procedure Name : validate_rule_information
781 -- Description :
782 -- Business Rules :
783 -- Parameters :
784 -- Version : 1.0
785 -- End of comments
786
787 PROCEDURE validate_rule_information(
788 x_return_status OUT NOCOPY VARCHAR2,
789 p_rulv_rec IN rulv_rec_type
790 ) IS
791 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
792 TYPE l_info_type IS REF CURSOR;
793 l_info_csr l_info_type;
794 sql_stmt VARCHAR2(4000);
795 l_end_user_column_name FND_DESCR_FLEX_COL_USAGE_VL.END_USER_COLUMN_NAME%TYPE;
796 l_rule_information OKC_RULES_V.RULE_INFORMATION1%TYPE;
797 l_flex_value_set_id FND_DESCR_FLEX_COL_USAGE_VL.FLEX_VALUE_SET_ID%TYPE;
798 l_required_flag FND_DESCR_FLEX_COL_USAGE_VL.REQUIRED_FLAG%TYPE;
799 l_row_notfound Boolean;
800
801 CURSOR l_fvs_csr IS
802 SELECT VALIDATION_TYPE, FORMAT_TYPE, MAXIMUM_SIZE,
803 UPPERCASE_ONLY_FLAG, NUMERIC_MODE_ENABLED_FLAG,
804 NUMBER_PRECISION
805 FROM FND_FLEX_VALUE_SETS fvs
806 WHERE fvs.FLEX_VALUE_SET_ID = l_flex_value_set_id;
807 l_fvs_rec l_fvs_csr%ROWTYPE;
808
809 CURSOR l_fvt_csr IS
810 SELECT application_table_name, id_column_name, id_column_type,
811 ADDITIONAL_WHERE_CLAUSE
812 FROM FND_FLEX_VALIDATION_TABLES fvt
813 WHERE fvt.flex_value_set_id = l_flex_value_set_id;
814 l_fvt_rec l_fvt_csr%ROWTYPE;
815 l_where_clause VARCHAR2(4000);
816
817 CURSOR l_fvl_csr IS
818 SELECT /*+ first_rows */ 'x'
819 FROM FND_FLEX_VALUES fvl
820 WHERE fvl.flex_value_set_id = l_flex_value_set_id
821 AND rownum < 2 ;
822
823 CURSOR l_fvl1_csr IS
824 SELECT /*+ first_rows */ 'x'
825 FROM FND_FLEX_VALUES fvl
826 WHERE fvl.flex_value_set_id = l_flex_value_set_id
827 AND fvl.flex_value = l_rule_information
828 AND rownum < 2 ;
829
830 -- actually nobody uses the cusor /striping/
831 /*
832 cursor l_flex_csr(p_rule_cat varchar2, p_attribute varchar2) is
833 SELECT END_USER_COLUMN_NAME, FLEX_VALUE_SET_ID, REQUIRED_FLAG
834 FROM FND_DESCR_FLEX_COLUMN_USAGES dfcu
835 WHERE dfcu.descriptive_flexfield_name = 'OKC Rule Developer DF'
836 AND dfcu.descriptive_flex_context_code = p_rule_cat
837 AND dfcu.application_column_name = p_attribute
838 AND dfcu.application_id =510;
839 */
840
841 l_dummy_var VARCHAR2(1) := '?';
842 TYPE l_fvt_rc_type IS REF CURSOR;
843 l_fvt_rc l_fvt_rc_type;
844
845 --ricagraw
846 i number;
847 --ricagraw
848
849
850 --
851 l_proc varchar2(72) := g_package||'validate_rule_information';
852 --
853 BEGIN
854
855 -- skekkar
856
857 -- initialize return status
858 x_return_status := OKC_API.G_RET_STS_SUCCESS;
859
860 populate_global_tab( p_rulv_rec => p_rulv_rec, x_return_status => l_return_status );
861 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
862 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
863 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
864 RAISE OKC_API.G_EXCEPTION_ERROR;
865 END IF;
866
867
868 -- added 19-MAR-2002 by rgalipo -- performance bug
869 -- added call to cursor on temporary table
870 -- removed dependency on pl/sql tables
871
872 /* FOR r IN NVL(g_ddf_context_code_tbl.first,0)..NVL(g_ddf_context_code_tbl.last,-1) LOOP */
873
874 FOR r_flex_col_usage IN c_flex_col_usage (p_rulv_rec.rule_information_category) LOOP
875
876 /* IF g_ddf_context_code_tbl(r) = p_rulv_rec.rule_information_category THEN */
877 -- IF r_flex_col_usage.descriptive_flex_context_code = p_rulv_rec.rule_information_category THEN
878
879 -- SQL statement to retrieve the developer descriptive flex field information
880 l_end_user_column_name := NULL;
881 l_flex_value_set_id := NULL;
882 l_rule_information := NULL;
883
884
885 --i := i + 1; -- g_col_seq_no_tbl(r);
886 --ricagraw
887 i := TO_NUMBER(r_flex_col_usage.seq_no);
888 --ricagraw
889
890 IF i = 1 THEN
891 l_rule_information := p_rulv_rec.RULE_INFORMATION1;
892 ELSIF i = 2 THEN
893 l_rule_information := p_rulv_rec.RULE_INFORMATION2;
894 ELSIF i = 3 THEN
895 l_rule_information := p_rulv_rec.RULE_INFORMATION3;
896 ELSIF i = 4 THEN
897 l_rule_information := p_rulv_rec.RULE_INFORMATION4;
898 ELSIF i = 5 THEN
899 l_rule_information := p_rulv_rec.RULE_INFORMATION5;
900 ELSIF i = 6 THEN
901 l_rule_information := p_rulv_rec.RULE_INFORMATION6;
902 ELSIF i = 7 THEN
903 l_rule_information := p_rulv_rec.RULE_INFORMATION7;
904 ELSIF i = 8 THEN
905 l_rule_information := p_rulv_rec.RULE_INFORMATION8;
906 ELSIF i = 9 THEN
907 l_rule_information := p_rulv_rec.RULE_INFORMATION9;
908 ELSIF i = 10 THEN
909 l_rule_information := p_rulv_rec.RULE_INFORMATION10;
910 ELSIF i = 11 THEN
911 l_rule_information := p_rulv_rec.RULE_INFORMATION11;
912 ELSIF i = 12 THEN
913 l_rule_information := p_rulv_rec.RULE_INFORMATION12;
914 ELSIF i = 13 THEN
915 l_rule_information := p_rulv_rec.RULE_INFORMATION13;
916 ELSIF i = 14 THEN
917 l_rule_information := p_rulv_rec.RULE_INFORMATION14;
918 ELSIF i = 15 THEN
919 l_rule_information := p_rulv_rec.RULE_INFORMATION15;
920 END IF;
921
922
923 l_end_user_column_name := r_flex_col_usage.end_user_column_name; -- g_end_user_col_name_tbl(r);
924 l_flex_value_set_id := r_flex_col_usage.flex_value_set_id; -- g_flex_value_set_id_tbl(r);
925 l_required_flag := r_flex_col_usage.required_flag; -- g_required_flag_tbl(r);
926
927 -- skekkar
928 --
929 -- if no column has been defined then the rule information
930 -- must be null
931 IF l_end_user_column_name IS NULL AND
932 l_rule_information IS NOT NULL THEN
933 OKC_API.set_message(
934 p_app_name => G_APP_NAME,
935 p_msg_name => G_NO_DEVELOPER_FLEX_DEFINED,
936 p_token1 => G_COL_NAME_TOKEN,
937 p_token1_value => 'rule_information'||LTRIM(TO_CHAR(i)));
938
939 -- notify caller of an error
940 x_return_status := OKC_API.G_RET_STS_ERROR;
941
942 -- halt validation
943 RAISE G_EXCEPTION_HALT_VALIDATION;
944 END IF;
945
946 IF l_end_user_column_name = 'CLOB_USED' THEN
947 l_end_user_column_name := '';
948 g_clob_used := 'Y';
949 END IF;
950
951 -- Check that the passed in value is correct for the
952 -- defined attribute.
953 IF l_end_user_column_name IS NOT NULL THEN
954
955 -- a value set must be defined.
956 IF l_flex_value_set_id IS NULL THEN
957 OKC_API.set_message(
958 p_app_name => G_APP_NAME,
959 p_msg_name => G_NO_VALUE_SET_DEFINED,
960 p_token1 => G_COL_NAME_TOKEN,
961 p_token1_value => 'rule_information'||LTRIM(TO_CHAR(i)));
962
963 -- notify caller of an error
964 x_return_status := OKC_API.G_RET_STS_ERROR;
965
966 -- halt validation
967 RAISE G_EXCEPTION_HALT_VALIDATION;
968 END IF;
969
970 -- check if data is required
971 -- The following has been commented out so
972 -- that rules can be built and saved without
973 -- all of the required data present. The required
974 -- data will be checked during the QA processes
975 --IF l_required_flag = 'Y' THEN
976 -- -- data is required
977 -- IF (l_rule_information = OKC_API.G_MISS_CHAR OR
978 -- l_rule_information IS NULL) THEN
979 -- OKC_API.set_message(
980 -- p_app_name => G_APP_NAME,
981 -- p_msg_name => G_REQUIRED_VALUE,
982 -- p_token1 => G_COL_NAME_TOKEN,
983 -- p_token1_value => l_end_user_column_name);
984 -- -- notify caller of an error
985 -- x_return_status := OKC_API.G_RET_STS_ERROR;
986 -- -- halt validation
987 -- RAISE G_EXCEPTION_HALT_VALIDATION;
988 -- END IF;
989 --END IF;
990
991 -- Check for value
992 IF (l_rule_information <> OKC_API.G_MISS_CHAR OR
993 l_rule_information IS NOT NULL) THEN
994
995 -- get value set information for the descriptive flex
996 OPEN l_fvs_csr;
997 FETCH l_fvs_csr INTO l_fvs_rec;
998 CLOSE l_fvs_csr;
999
1000 -- Check type
1001 DECLARE
1002 l_date DATE;
1003 l_number NUMBER;
1004 l_char VARCHAR2(4000);
1005 BEGIN
1006 -- Modified for Bug 2292300
1007 IF l_fvs_rec.format_type IN ('D','X') THEN
1008 l_date := fnd_date.canonical_to_date(l_rule_information);
1009 --IF l_fvs_rec.format_type = 'D' THEN
1010 -- l_date := to_date(l_rule_information, 'YYYY/MM/DD HH24:MI:SS');
1011 -- do we want any checks for character?
1012 --ELSIF l_fvs_rec.format_type = 'C' THEN
1013 -- l_char := l_rule_information;
1014 ELSIF l_fvs_rec.format_type = 'N' THEN
1015 l_number := to_number(l_rule_information);
1016 END IF;
1017 EXCEPTION
1018 WHEN OTHERS THEN
1019 OKC_API.set_message(
1020 p_app_name => G_APP_NAME,
1021 p_msg_name => G_INVALID_VALUE,
1022 p_token1 => G_COL_NAME_TOKEN,
1023 p_token1_value => l_end_user_column_name);
1024 -- notify caller of an error
1025 x_return_status := OKC_API.G_RET_STS_ERROR;
1026 -- halt validation
1027 RAISE G_EXCEPTION_HALT_VALIDATION;
1028 END;
1029
1030 -- Check foriegn key validation
1031 IF l_fvs_rec.VALIDATION_TYPE = 'F' THEN
1032 OPEN l_fvt_csr;
1033 FETCH l_fvt_csr INTO l_fvt_rec;
1034 CLOSE l_fvt_csr;
1035
1036 l_where_clause := l_fvt_rec.additional_where_clause;
1037 IF l_where_clause IS NOT NULL AND
1038 UPPER(SUBSTR(l_where_clause, 1, 5)) <> 'ORDER' THEN
1039 IF UPPER(SUBSTR(l_where_clause, 1, 5)) = 'WHERE' THEN
1040 l_where_clause := SUBSTR(l_where_clause, 6, LENGTH(l_where_clause));
1041 END IF;
1042 l_where_clause := 'AND ' ||l_where_clause;
1043 END IF;
1044
1045 -- validate forien key
1046 sql_stmt := 'SELECT ''x'' ' ||
1047 ' FROM ' || l_fvt_rec.application_table_name ||
1048 ' WHERE ' || l_fvt_rec.id_column_name || ' = :col_val ' ||
1049 ' AND rownum < 2 ' ||
1050 l_where_clause;
1051 --dbms_output.put_line(' l sql stmt '||sql_stmt);
1052 IF l_fvt_rec.id_column_type = 'N' THEN
1053 OPEN l_fvt_rc FOR sql_stmt USING to_number(l_rule_information);
1054 ELSE
1055 OPEN l_fvt_rc FOR sql_stmt USING l_rule_information;
1056 END IF;
1057 FETCH l_fvt_rc INTO l_dummy_var;
1058 l_row_notfound := l_fvt_rc%NotFound;
1059 CLOSE l_fvt_rc;
1060 -- if l_dummy_var still set to default, data was not found
1061 IF l_row_notfound THEN
1062 OKC_API.set_message(
1063 p_app_name => G_APP_NAME,
1064 p_msg_name => G_NO_PARENT_RECORD,
1065 p_token1 => G_COL_NAME_TOKEN,
1066 p_token1_value => l_end_user_column_name,
1067 p_token2 => G_CHILD_TABLE_TOKEN,
1068 p_token2_value => G_VIEW,
1069 p_token3 => G_PARENT_TABLE_TOKEN,
1070 p_token3_value => l_fvt_rec.application_table_name);
1071 -- notify caller of an error
1072 x_return_status := OKC_API.G_RET_STS_ERROR;
1073 END IF;
1074 END IF;
1075 -- Check independent value
1076 IF l_fvs_rec.VALIDATION_TYPE = 'I' THEN
1077 l_dummy_var := '?';
1078 OPEN l_fvl_csr;
1079 FETCH l_fvl_csr INTO l_dummy_var;
1080 CLOSE l_fvl_csr;
1081
1082 --IF l_dummy_var = 'X' THEN
1083 IF l_dummy_var = 'x' THEN
1084 -- validate forien key
1085 l_dummy_var := '?';
1086 OPEN l_fvl1_csr;
1087 FETCH l_fvl1_csr INTO l_dummy_var;
1088 CLOSE l_fvl1_csr;
1089
1090 -- if l_dummy_var still set to default, data was not found
1091 IF (l_dummy_var = '?') THEN
1092 OKC_API.set_message(
1093 p_app_name => G_APP_NAME,
1094 p_msg_name => G_INVALID_VALUE,
1095 p_token1 => G_COL_NAME_TOKEN,
1096 p_token1_value => l_end_user_column_name);
1097
1098 -- notify caller of an error
1099 x_return_status := OKC_API.G_RET_STS_ERROR;
1100 END IF;
1101 END IF;
1102 END IF; -- validate independent value set
1103 END IF; -- value is not null
1104 END IF; -- l_end_user_column_name IS NOT NULL
1105
1106 -- skekkar
1107
1108 -- END IF; -- for a Rule
1109 END LOOP; -- c_flex_col_usage
1110
1111 -- skekkar
1112
1113 EXCEPTION
1114 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1115 -- no processing necessary; validation can continue with next column
1116 NULL;
1117 WHEN OTHERS THEN
1118 -- store SQL error message on message stack
1119 OKC_API.SET_MESSAGE(
1120 p_app_name => G_APP_NAME,
1121 p_msg_name => G_UNEXPECTED_ERROR,
1122 p_token1 => G_SQLCODE_TOKEN,
1123 p_token1_value => SQLCODE,
1124 p_token2 => G_SQLERRM_TOKEN,
1125 p_token2_value => SQLERRM);
1126 -- notify caller of an error as UNEXPETED error
1127 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1128 -- verify that cursor was closed
1129 IF l_info_csr%ISOPEN THEN
1130 CLOSE l_info_csr;
1131 END IF;
1132 IF l_fvs_csr%ISOPEN THEN
1133 CLOSE l_fvs_csr;
1134 END IF;
1135 IF l_fvt_csr%ISOPEN THEN
1136 CLOSE l_fvt_csr;
1137 END IF;
1138 IF l_fvt_rc%ISOPEN THEN
1139 CLOSE l_fvt_rc;
1140 END IF;
1141 IF l_info_csr%ISOPEN THEN
1142 CLOSE l_info_csr;
1143 END IF;
1144 IF l_fvl_csr%ISOPEN THEN
1145 CLOSE l_fvl_csr;
1146 END IF;
1147 IF l_fvl1_csr%ISOPEN THEN
1148 CLOSE l_fvl1_csr;
1149 END IF;
1150 END validate_rule_information;
1151 --
1152 -- Start of comments
1153 --
1154 -- Procedure Name : validate_text
1155 -- Description :
1156 -- Business Rules :
1157 -- Parameters :
1158 -- Version : 1.0
1159 -- End of comments
1160 /*--Bug 3055393
1161 PROCEDURE validate_text(
1162 x_return_status OUT NOCOPY VARCHAR2,
1163 p_rulv_rec IN rulv_rec_type
1164 ) IS
1165 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1166 sql_stmt VARCHAR2(4000);
1167 TYPE l_info_type IS REF CURSOR;
1168 l_info_csr l_info_type;
1169 l_dummy_var VARCHAR2(1) := '?';
1170
1171 --
1172 l_proc varchar2(72) := g_package||'validate_text';
1173 --
1174 BEGIN
1175
1176 -- initialize return status
1177 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1178
1179 -- check for data before processing
1180 IF (p_rulv_rec.text IS NOT NULL) THEN
1181
1182 -- Make sure that the object has been defined in the developer
1183 -- descriptive flex field
1184
1185 -- bug 1857663
1186 -- FROM clause missing in the sql_stmt constructed below
1187
1188 -- /striping/
1189 p_appl_id := okc_rld_pvt.get_appl_id(p_rulv_rec.rule_information_category);
1190 p_dff_name := okc_rld_pvt.get_dff_name(p_rulv_rec.rule_information_category);
1191
1192 sql_stmt := 'SELECT ''x'' ' ||
1193 ' FROM FND_DESCR_FLEX_COLUMN_USAGES dfcu ' ||
1194 -- ' WHERE dfcu.descriptive_flexfield_name = ' || -- /striping/
1195 ' WHERE dfcu.descriptive_flexfield_name = :dff_name ' ||
1196 -- ''''||G_DESCRIPTIVE_FLEXFIELD_NAME||'''' || -- /striping/
1197 ' AND dfcu.descriptive_flex_context_code = :rule_cat ' ||
1198 ' AND dfcu.end_user_column_name = :attribute' ||
1199 -- ' AND dfcu.APPLICATION_ID=510'; -- /striping/
1200 ' AND dfcu.APPLICATION_ID = :appl_id';
1201
1202 -- ' AND dfcu.application_column_name = :attribute' ;
1203
1204 OPEN l_info_csr
1205 FOR sql_stmt
1206 -- USING p_rulv_rec.rule_information_category, 'TEXT'; -- /striping/
1207 USING p_dff_name, p_rulv_rec.rule_information_category, 'TEXT', p_appl_id;
1208 FETCH l_info_csr INTO l_dummy_var;
1209 CLOSE l_info_csr;
1210
1211 -- if no column has been defined then the object information
1212 -- must be null
1213 -- if l_dummy_var still set to default, data was not found
1214 IF (l_dummy_var = '?') THEN
1215 OKC_API.set_message(
1216 p_app_name => G_APP_NAME,
1217 p_msg_name => G_NO_DEVELOPER_FLEX_DEFINED,
1218 p_token1 => G_COL_NAME_TOKEN,
1219 p_token1_value => 'text');
1220
1221 -- notify caller of an error
1222 x_return_status := OKC_API.G_RET_STS_ERROR;
1223
1224 END IF;
1225 END IF;
1226
1227 EXCEPTION
1228 WHEN OTHERS THEN
1229
1230 -- store SQL error message on message stack
1231 OKC_API.SET_MESSAGE(
1232 p_app_name => G_APP_NAME,
1233 p_msg_name => G_UNEXPECTED_ERROR,
1234 p_token1 => G_SQLCODE_TOKEN,
1235 p_token1_value => SQLCODE,
1236 p_token2 => G_SQLERRM_TOKEN,
1237 p_token2_value => SQLERRM);
1238 -- notify caller of an error as UNEXPETED error
1239 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1240 -- verify that cursor was closed
1241 IF l_info_csr%ISOPEN THEN
1242 CLOSE l_info_csr;
1243 END IF;
1244 END validate_text;
1245 */
1246 --
1247 -- Start of comments
1248 --
1249 -- Procedure Name : validate_object
1250 -- Description :
1251 -- Business Rules :
1252 -- Parameters :
1253 -- Version : 1.0
1254 -- End of comments
1255
1256 PROCEDURE validate_object(
1257 x_return_status OUT NOCOPY VARCHAR2,
1258 p_rulv_rec IN rulv_rec_type
1259 ) IS
1260 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1261 TYPE l_int_type IS REF CURSOR;
1262 l_int_csr l_int_type;
1263 sql_stmt VARCHAR2(4000);
1264 l_object_id1 OKC_RULES_V.OBJECT1_ID1%TYPE;
1265 l_object_id2 OKC_RULES_V.OBJECT1_ID2%TYPE;
1266 jtot_object_code OKC_RULES_V.JTOT_OBJECT1_CODE%TYPE;
1267 l_from_table JTF_OBJECTS_VL.FROM_TABLE%TYPE;
1268 l_where_clause varchar2(4000);
1269 l_flex_value_set_id FND_DESCR_FLEX_COL_USAGE_VL.FLEX_VALUE_SET_ID%TYPE;
1270 TYPE l_info_type IS REF CURSOR;
1271 l_info_csr l_info_type;
1272
1273 CURSOR l_jtot_csr IS
1274 SELECT decode(where_clause,'','','and '||where_clause) where_clause,
1275 from_table
1276 FROM JTF_OBJECTS_B jtot
1277 WHERE jtot.object_code = jtot_object_code;
1278
1279 l_dummy_var VARCHAR2(1) := '?';
1280
1281 -- skekkar
1282 i number := 0;
1283 -- skekkar
1284
1285 --
1286 l_proc varchar2(72) := g_package||'validate_object';
1287 --
1288 BEGIN
1289
1290
1291 -- skekkar
1292
1293 -- initialize return status
1294 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1295
1296 populate_obj_global_tab( p_rulv_rec => p_rulv_rec, x_return_status => l_return_status );
1297 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1298 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1299 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1300 RAISE OKC_API.G_EXCEPTION_ERROR;
1301 END IF;
1302
1303 -- added 19-MAR-2002 by rgalipo -- performance bug
1304 -- added call to cursor on temporary table
1305 -- removed dependency on pl/sql tables
1306 /* FOR r IN NVL(g_obj_ddf_context_code_tbl.first,0)..NVL(g_obj_ddf_context_code_tbl.last,-1) LOOP */
1307
1308 FOR r_descr_flex_col IN c_descr_flex_col (p_rulv_rec.rule_information_category) LOOP
1309
1310 /* IF g_obj_ddf_context_code_tbl(r) = p_rulv_rec.rule_information_category THEN */
1311 -- IF r_descr_flex_col.descriptive_flex_context_code = p_rulv_rec.rule_information_category THEN
1312
1313 -- SQL statement to retrieve the developer descriptive flex field information
1314 l_object_id1 := NULL;
1315 l_object_id2 := NULL;
1316 jtot_object_code := NULL;
1317
1318 i := i + 1; -- := g_obj_col_seq_no_tbl(r);
1319
1320 IF i = 1 THEN
1321 l_object_id1 := p_rulv_rec.OBJECT1_ID1;
1322 l_object_id2 := p_rulv_rec.OBJECT1_ID2;
1323 jtot_object_code := p_rulv_rec.JTOT_OBJECT1_CODE;
1324 ELSIF i = 2 THEN
1325 l_object_id1 := p_rulv_rec.OBJECT2_ID1;
1326 l_object_id2 := p_rulv_rec.OBJECT2_ID2;
1327 jtot_object_code := p_rulv_rec.JTOT_OBJECT2_CODE;
1328 ELSIF i = 3 THEN
1329 l_object_id1 := p_rulv_rec.OBJECT3_ID1;
1330 l_object_id2 := p_rulv_rec.OBJECT3_ID2;
1331 jtot_object_code := p_rulv_rec.JTOT_OBJECT3_CODE;
1332 END IF;
1333
1334 l_dummy_var := r_descr_flex_col.dummy_col; -- g_obj_x_tbl(r);
1335
1336 -- skekkar
1337
1338 -- if no column has been defined then the object id
1339 -- must be null
1340 IF l_object_id1 IS NOT NULL AND
1341 jtot_object_code IS NULL THEN
1342
1343 OKC_API.set_message(
1344 p_app_name => G_APP_NAME,
1345 p_msg_name => G_REQUIRED_VALUE,
1346 p_token1 => G_COL_NAME_TOKEN,
1347 p_token1_value => 'jtot_object_'||LTRIM(TO_CHAR(i))||'_code');
1348
1349 -- notify caller of an error
1350 x_return_status := OKC_API.G_RET_STS_ERROR;
1351
1352 -- halt validation
1353 RAISE G_EXCEPTION_HALT_VALIDATION;
1354 ELSIF l_object_id1 IS NULL AND
1355 jtot_object_code IS NOT NULL THEN
1356
1357 OKC_API.set_message(
1358 p_app_name => G_APP_NAME,
1359 p_msg_name => G_REQUIRED_VALUE,
1360 p_token1 => G_COL_NAME_TOKEN,
1361 p_token1_value => 'object'||LTRIM(TO_CHAR(i))||'_id1');
1362
1363 -- notify caller of an error
1364 x_return_status := OKC_API.G_RET_STS_ERROR;
1365
1366 -- halt validation
1367 RAISE G_EXCEPTION_HALT_VALIDATION;
1368 ELSIF l_object_id1 IS NOT NULL AND
1369 jtot_object_code IS NOT NULL THEN
1370
1371 -- if l_dummy_var still set to default, data was not found
1372 IF (l_dummy_var = '?') THEN
1373 OKC_API.set_message(
1374 p_app_name => G_APP_NAME,
1375 p_msg_name => G_NO_DEVELOPER_FLEX_DEFINED,
1376 p_token1 => G_COL_NAME_TOKEN,
1377 p_token1_value => 'object'||LTRIM(TO_CHAR(i)));
1378
1379 -- notify caller of an error
1380 x_return_status := OKC_API.G_RET_STS_ERROR;
1381
1382 -- halt validation
1383 RAISE G_EXCEPTION_HALT_VALIDATION;
1384 END IF;
1385
1386 -- check jtot_object_code must be defined
1387 OPEN l_jtot_csr;
1388 FETCH l_jtot_csr INTO l_where_clause, l_from_table;
1389 CLOSE l_jtot_csr;
1390
1391 -- if l_dummy_var still set to default, data was not found
1392 IF (l_dummy_var = '?') THEN
1393 OKC_API.set_message(
1394 p_app_name => G_APP_NAME,
1395 p_msg_name => G_INVALID_VALUE,
1396 p_token1 => G_COL_NAME_TOKEN,
1397 p_token1_value => 'jtot_object'||LTRIM(TO_CHAR(i))||'code');
1398 -- notify caller of an error
1399 x_return_status := OKC_API.G_RET_STS_ERROR;
1400 END IF;
1401
1402 -- Do integration foriegn key validation
1403
1404 sql_stmt := 'SELECT ''x'' ' ||
1405 ' FROM ' || l_from_table ||
1406 ' WHERE ID1 = :col_val ' ||
1407 ' AND ID2 = :col_val2 '||
1408 ' AND rownum < 2 '|| l_where_clause;
1409
1410 OPEN l_int_csr FOR sql_stmt USING l_object_id1, nvl(l_object_id2, '#');
1411 FETCH l_int_csr INTO l_dummy_var;
1412 CLOSE l_int_csr;
1413
1414 -- if l_dummy_var still set to default, data was not found
1415 IF (l_dummy_var = '?') THEN
1416 OKC_API.set_message(
1417 p_app_name => G_APP_NAME,
1418 p_msg_name => G_NO_PARENT_RECORD,
1419 p_token1 => G_COL_NAME_TOKEN,
1420 p_token1_value => 'object_id1, object2',
1421 p_token2 => G_CHILD_TABLE_TOKEN,
1422 p_token2_value => G_VIEW,
1423 p_token3 => G_PARENT_TABLE_TOKEN,
1424 p_token3_value => l_from_table);
1425
1426 -- notify caller of an error
1427 x_return_status := OKC_API.G_RET_STS_ERROR;
1428 END IF;
1429 END IF;
1430
1431 -- skekkar
1432
1433 -- END IF; -- for a Rule
1434
1435 END LOOP; -- c_descr_flex_col
1436
1437 EXCEPTION
1438 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1439 -- no processing necessary; validation can continue with next column
1440 NULL;
1441 WHEN OTHERS THEN
1442 -- store SQL error message on message stack
1443 OKC_API.SET_MESSAGE(
1444 p_app_name => G_APP_NAME,
1445 p_msg_name => G_UNEXPECTED_ERROR,
1446 p_token1 => G_SQLCODE_TOKEN,
1447 p_token1_value => SQLCODE,
1448 p_token2 => G_SQLERRM_TOKEN,
1449 p_token2_value => SQLERRM);
1450 -- notify caller of an error as UNEXPETED error
1451 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1452 -- verify that cursor was closed
1453 IF l_info_csr%ISOPEN THEN
1454 CLOSE l_info_csr;
1455 END IF;
1456 IF l_jtot_csr%ISOPEN THEN
1457 CLOSE l_jtot_csr;
1458 END IF;
1459 IF l_jtot_csr%ISOPEN THEN
1460 CLOSE l_jtot_csr;
1461 END IF;
1462 END validate_object;
1463 --
1464 -- Start of comments
1465 --
1466 -- Procedure Name : validate_attributes
1467 -- Description :
1468 -- Business Rules :
1469 -- Parameters :
1470 -- Version : 1.0
1471 -- End of comments
1472
1473 FUNCTION Validate_Attributes (
1474 p_rulv_rec IN rulv_rec_type
1475 ) RETURN VARCHAR2 IS
1476 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1477 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1478 --
1479 l_proc varchar2(72) := g_package||'Validate_Attributes';
1480 --
1481 BEGIN
1482
1483 -- call each column-level validation for the rule super type columns
1484
1485 validate_rgp_id(
1486 x_return_status => l_return_status,
1487 p_rulv_rec => p_rulv_rec);
1488
1489 -- store the highest degree of error
1490 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1491 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1492 x_return_status := l_return_status;
1493 END IF;
1494 END IF;
1495 --
1496 validate_std_template_yn(
1497 x_return_status => l_return_status,
1498 p_rulv_rec => p_rulv_rec);
1499
1500 validate_template_yn(
1501 x_return_status => l_return_status,
1502 p_rulv_rec => p_rulv_rec);
1503
1504
1505 -- store the highest degree of error
1506 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1507 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1508 x_return_status := l_return_status;
1509 END IF;
1510 END IF;
1511 --
1512 validate_dnz_chr_id(
1513 x_return_status => l_return_status,
1514 p_rulv_rec => p_rulv_rec);
1515
1516 -- store the highest degree of error
1517 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1518 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1519 x_return_status := l_return_status;
1520 END IF;
1521 END IF;
1522 --
1523 validate_warn_yn(
1524 x_return_status => l_return_status,
1525 p_rulv_rec => p_rulv_rec);
1526
1527 -- store the highest degree of error
1528 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1529 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1530 x_return_status := l_return_status;
1531 END IF;
1532 END IF;
1533 --
1534 validate_rule_info_category(
1535 x_return_status => l_return_status,
1536 p_rulv_rec => p_rulv_rec);
1537
1538 -- store the highest degree of error
1539 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1540 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1541 x_return_status := l_return_status;
1542 END IF;
1543 END IF;
1544 --
1545 g_clob_used := 'N';
1546 validate_rule_information(
1547 x_return_status => l_return_status,
1548 p_rulv_rec => p_rulv_rec);
1549
1550 -- store the highest degree of error
1551 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1552 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1553 x_return_status := l_return_status;
1554 END IF;
1555 END IF;
1556 --
1557 /*--Bug 3055393
1558 validate_text(
1559 x_return_status => l_return_status,
1560 p_rulv_rec => p_rulv_rec);
1561
1562 -- store the highest degree of error
1563 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1564 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1565 x_return_status := l_return_status;
1566 END IF;
1567 END IF;
1568 */
1569 --
1570 validate_object(
1571 x_return_status => l_return_status,
1572 p_rulv_rec => p_rulv_rec);
1573
1574 -- store the highest degree of error
1575 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1576 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1577 x_return_status := l_return_status;
1578 END IF;
1579 END IF;
1580 --
1581
1582 -- return status to caller
1583 RETURN(x_return_status);
1584
1585 EXCEPTION
1586 WHEN OTHERS THEN
1587 -- store SQL error message on message stack for caller
1588 OKC_API.SET_MESSAGE
1589 (p_app_name => G_APP_NAME,
1590 p_msg_name => G_UNEXPECTED_ERROR,
1591 p_token1 => G_SQLCODE_TOKEN,
1592 p_token1_value => SQLCODE,
1593 p_token2 => G_SQLERRM_TOKEN,
1594 p_token2_value => SQLERRM);
1595
1596 -- notify caller of an UNEXPECTED error
1597 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1598
1599 -- return status to caller
1600 RETURN x_return_status;
1601
1602 END Validate_Attributes;
1603
1604 /*********************** create comments **************************/
1605 --+
1606 function set_fk_comments(p_rulv_rec rulv_rec_type) return varchar2 as
1607 TYPE l_info_type IS REF CURSOR;
1608 l_info_csr l_info_type;
1609 sql_stmt VARCHAR2(4000);
1610 l_object_id1 OKC_RULES_V.OBJECT1_ID1%TYPE;
1611 l_object_id2 OKC_RULES_V.OBJECT1_ID2%TYPE;
1612 l_jtot_object_code OKC_RULES_V.JTOT_OBJECT1_CODE%TYPE;
1613 l_from_table JTF_OBJECTS_VL.FROM_TABLE%TYPE;
1614 l_where_clause varchar2(4000);
1615 l_flex_value_set_id FND_DESCR_FLEX_COL_USAGE_VL.FLEX_VALUE_SET_ID%TYPE;
1616 CURSOR l_jtot_csr IS
1617 SELECT decode(where_clause,'','','and '||where_clause) where_clause,
1618 from_table
1619 FROM JTF_OBJECTS_B jtot
1620 WHERE jtot.object_code = l_jtot_object_code;
1621 l_get varchar2(2000);
1622 l_assemble VARCHAR2(32000);
1623 i number ;
1624 --
1625 l_proc varchar2(72) := g_package||'set_fk_comments';
1626 --
1627 begin
1628 i:=0;
1629 -- skekkar
1630
1631 -- added 19-MAR-2002 by rgalipo -- performance bug
1632 -- added call to cursor on temporary table
1633 -- removed dependency on pl/sql tables
1634 /* FOR r IN NVL(g_obj_ddf_context_code_tbl.first,0)..NVL(g_obj_ddf_context_code_tbl.last,-1) LOOP */
1635
1636 FOR r_descr_flex_col IN c_descr_flex_col (p_rulv_rec.rule_information_category) LOOP
1637
1638 /* IF g_obj_ddf_context_code_tbl(r) = p_rulv_rec.rule_information_category THEN */
1639 -- IF r_descr_flex_col.descriptive_flex_context_code = p_rulv_rec.rule_information_category THEN
1640
1641 l_object_id1 := NULL;
1642 l_object_id2 := NULL;
1643 l_jtot_object_code := NULL;
1644 l_get := NULL;
1645
1646 i := i + 1; -- g_obj_col_seq_no_tbl(r);
1647
1648 IF i = 1 THEN
1649 l_object_id1 := p_rulv_rec.OBJECT1_ID1;
1650 l_object_id2 := p_rulv_rec.OBJECT1_ID2;
1651 l_jtot_object_code := p_rulv_rec.JTOT_OBJECT1_CODE;
1652 ELSIF i = 2 THEN
1653 l_object_id1 := p_rulv_rec.OBJECT2_ID1;
1654 l_object_id2 := p_rulv_rec.OBJECT2_ID2;
1655 l_jtot_object_code := p_rulv_rec.JTOT_OBJECT2_CODE;
1656 ELSIF i = 3 THEN
1657 l_object_id1 := p_rulv_rec.OBJECT3_ID1;
1658 l_object_id2 := p_rulv_rec.OBJECT3_ID2;
1659 l_jtot_object_code := p_rulv_rec.JTOT_OBJECT3_CODE;
1660 END IF;
1661
1662 l_get := r_descr_flex_col.form_left_prompt; -- g_obj_form_left_prompt_tbl(r);
1663
1664 -- skekkar
1665
1666 IF ((l_jtot_object_code IS NOT NULL) AND (l_object_id1 IS NOT NULL)) THEN
1667 BEGIN
1668 l_assemble:=l_assemble||l_get||'=';
1669 OPEN l_jtot_csr;
1670 FETCH l_jtot_csr INTO l_where_clause, l_from_table;
1671 CLOSE l_jtot_csr;
1672 sql_stmt := 'SELECT name ' ||
1673 ' FROM ' || l_from_table ||
1674 ' WHERE ID1 = :col_val ' ||
1675 ' AND ID2 = :col_val2 ' || l_where_clause;
1676 OPEN l_info_csr FOR sql_stmt USING l_object_id1, nvl(l_object_id2, '#');
1677 l_get:=NULL;
1678 FETCH l_info_csr INTO l_get;
1679 CLOSE l_info_csr;
1680 l_assemble := l_assemble||l_get||', ';
1681 EXCEPTION
1682 WHEN OTHERS THEN
1683 if (l_info_csr%ISOPEN) then close l_info_csr; end if;
1684 if (l_jtot_csr%ISOPEN) then close l_jtot_csr; end if;
1685 END;
1686 END IF; -- l_jtot_object_code IS NOT NULL
1687 -- END IF; -- r_descr_flex_col.descriptive_flex_context_code = p_rulv_rec.rule_information_category
1688 END LOOP; -- c_descr_flex_col
1689
1690
1691 IF (l_debug = 'Y') THEN
1692 okc_util.print_trace(5, ' RUL set_fk_comments:' || l_assemble);
1693 END IF;
1694
1695
1696 return l_assemble;
1697 end;
1698
1699 --+
1700 function set_bt_comments(p_rulv_rec rulv_rec_type) return varchar2 as
1701 l_get varchar2(100);
1702 l_assemble VARCHAR2(4000);
1703 i number ;
1704 --
1705 TYPE l_info_type IS REF CURSOR;
1706 l_info_csr l_info_type;
1707 sql_stmt VARCHAR2(4000);
1708 l_end_user_column_name FND_DESCR_FLEX_COL_USAGE_VL.END_USER_COLUMN_NAME%TYPE;
1709 l_rule_information OKC_RULES_V.RULE_INFORMATION1%TYPE;
1710 l_flex_value_set_id FND_DESCR_FLEX_COL_USAGE_VL.FLEX_VALUE_SET_ID%TYPE;
1711 l_required_flag FND_DESCR_FLEX_COL_USAGE_VL.REQUIRED_FLAG%TYPE;
1712
1713 CURSOR l_fvs_csr IS
1714 SELECT VALIDATION_TYPE, FORMAT_TYPE, MAXIMUM_SIZE,
1715 UPPERCASE_ONLY_FLAG, NUMERIC_MODE_ENABLED_FLAG,
1716 NUMBER_PRECISION
1717 FROM FND_FLEX_VALUE_SETS fvs
1718 WHERE fvs.FLEX_VALUE_SET_ID = l_flex_value_set_id;
1719 l_fvs_rec l_fvs_csr%ROWTYPE;
1720
1721 CURSOR l_fvt_csr IS
1722 SELECT application_table_name, id_column_name, id_column_type,
1723 ADDITIONAL_WHERE_CLAUSE, value_column_name
1724 FROM FND_FLEX_VALIDATION_TABLES fvt
1725 WHERE fvt.flex_value_set_id = l_flex_value_set_id;
1726 l_fvt_rec l_fvt_csr%ROWTYPE;
1727 l_where_clause VARCHAR2(4000);
1728
1729 TYPE l_fvt_rc_type IS REF CURSOR;
1730 l_fvt_rc l_fvt_rc_type;
1731 --
1732 l_proc varchar2(72) := g_package||'set_bt_comments';
1733 --
1734 begin
1735 i:=0;
1736 -- skekkar
1737
1738 -- added 19-MAR-2002 by rgalipo -- performance bug
1739 -- added call to cursor on temporary table
1740 -- removed dependency on pl/sql tables
1741
1742 FOR r_flex_col_usage IN c_flex_col_usage (p_rulv_rec.rule_information_category) LOOP
1743
1744 /* FOR r IN NVL(g_ddf_context_code_tbl.first,0)..NVL(g_ddf_context_code_tbl.last,-1) LOOP */
1745 /* IF g_ddf_context_code_tbl(r) = p_rulv_rec.rule_information_category THEN */
1746 -- IF r_flex_col_usage.descriptive_flex_context_code = p_rulv_rec.rule_information_category THEN
1747
1748 -- SQL statement to retrieve the developer descriptive flex field information
1749 l_end_user_column_name := NULL;
1750 l_flex_value_set_id := NULL;
1751 l_rule_information := NULL;
1752
1753 i := i + 1; -- g_col_seq_no_tbl(r);
1754
1755
1756 IF i = 1 THEN
1757 l_rule_information := p_rulv_rec.RULE_INFORMATION1;
1758 ELSIF i = 2 THEN
1759 l_rule_information := p_rulv_rec.RULE_INFORMATION2;
1760 ELSIF i = 3 THEN
1761 l_rule_information := p_rulv_rec.RULE_INFORMATION3;
1762 ELSIF i = 4 THEN
1763 l_rule_information := p_rulv_rec.RULE_INFORMATION4;
1764 ELSIF i = 5 THEN
1765 l_rule_information := p_rulv_rec.RULE_INFORMATION5;
1766 ELSIF i = 6 THEN
1767 l_rule_information := p_rulv_rec.RULE_INFORMATION6;
1768 ELSIF i = 7 THEN
1769 l_rule_information := p_rulv_rec.RULE_INFORMATION7;
1770 ELSIF i = 8 THEN
1771 l_rule_information := p_rulv_rec.RULE_INFORMATION8;
1772 ELSIF i = 9 THEN
1773 l_rule_information := p_rulv_rec.RULE_INFORMATION9;
1774 ELSIF i = 10 THEN
1775 l_rule_information := p_rulv_rec.RULE_INFORMATION10;
1776 ELSIF i = 11 THEN
1777 l_rule_information := p_rulv_rec.RULE_INFORMATION11;
1778 ELSIF i = 12 THEN
1779 l_rule_information := p_rulv_rec.RULE_INFORMATION12;
1780 ELSIF i = 13 THEN
1781 l_rule_information := p_rulv_rec.RULE_INFORMATION13;
1782 ELSIF i = 14 THEN
1783 l_rule_information := p_rulv_rec.RULE_INFORMATION14;
1784 ELSIF i = 15 THEN
1785 l_rule_information := p_rulv_rec.RULE_INFORMATION15;
1786 END IF;
1787
1788 l_end_user_column_name := r_flex_col_usage.end_user_column_name; -- g_end_user_col_name_tbl(r);
1789 l_flex_value_set_id := r_flex_col_usage.flex_value_set_id; -- g_flex_value_set_id_tbl(r);
1790 l_required_flag := r_flex_col_usage.required_flag; -- g_required_flag_tbl(r);
1791 l_get := r_flex_col_usage.form_left_prompt; -- g_form_left_prompt_tbl(r);
1792
1793 -- skekkar
1794 if (l_rule_information is not NULL) then
1795 begin
1796 --
1797 l_assemble:=l_assemble||l_get||'=';
1798 OPEN l_fvs_csr;
1799 FETCH l_fvs_csr INTO l_fvs_rec;
1800 CLOSE l_fvs_csr;
1801 --
1802 IF l_fvs_rec.VALIDATION_TYPE <>'F' THEN
1803 l_assemble:=l_assemble||l_rule_information||', ';
1804 else
1805 OPEN l_fvt_csr;
1806 FETCH l_fvt_csr INTO l_fvt_rec;
1807 CLOSE l_fvt_csr;
1808 --
1809 l_where_clause := l_fvt_rec.additional_where_clause;
1810 IF l_where_clause IS NOT NULL AND
1811 UPPER(SUBSTR(l_where_clause, 1, 5)) <> 'ORDER' THEN
1812 IF UPPER(SUBSTR(l_where_clause, 1, 5)) = 'WHERE' THEN
1813 l_where_clause := SUBSTR(l_where_clause, 6, LENGTH(l_where_clause));
1814 END IF;
1815 l_where_clause := 'AND ' ||l_where_clause;
1816 END IF;
1817 if (UPPER(l_fvt_rec.application_table_name)='OKC_TIMEVALUES_V') then
1818 l_fvt_rec.value_column_name := 'COMMENTS';
1819 end if;
1820 sql_stmt := 'SELECT ' ||l_fvt_rec.value_column_name||
1821 ' FROM ' || l_fvt_rec.application_table_name ||
1822 ' WHERE ' || l_fvt_rec.id_column_name || ' = :col_val ' ||
1823 l_where_clause;
1824 IF l_fvt_rec.id_column_type = 'N' THEN
1825 OPEN l_fvt_rc FOR sql_stmt USING to_number(l_rule_information);
1826 ELSE
1827 OPEN l_fvt_rc FOR sql_stmt USING l_rule_information;
1828 END IF;
1829 l_get := NULL;
1830 FETCH l_fvt_rc INTO l_get;
1831 CLOSE l_fvt_rc;
1832 l_assemble:=l_assemble||l_get||', ';
1833 end if; -- l_fvs_rec.VALIDATION_TYPE <>'F'
1834 exception when others then
1835 if (l_info_csr%ISOPEN) then close l_info_csr; end if;
1836 if (l_fvs_csr%ISOPEN) then close l_fvs_csr; end if;
1837 if (l_fvt_csr%ISOPEN) then close l_fvt_csr; end if;
1838 if (l_fvt_rc%ISOPEN) then close l_fvt_rc; end if;
1839 end; -- begin
1840 end if; -- l_rule_information is not NULL
1841 -- end if; -- r_flex_col_usage.descriptive_flex_context_code = p_rulv_rec.rule_information_category
1842 END LOOP; -- c_flex_col_usage
1843
1844
1845 IF (l_debug = 'Y') THEN
1846 okc_util.print_trace(5, ' RUL set_bt_comments:' || l_assemble);
1847 END IF;
1848
1849
1850 return l_assemble;
1851 end;
1852
1853 function set_comments(p_rulv_rec rulv_rec_type) return varchar2 as
1854 S varchar2(32000);
1855 L number;
1856 --
1857 l_proc varchar2(72) := g_package||'set_comments';
1858 --
1859 begin
1860
1861 S:=set_fk_comments(p_rulv_rec)||set_bt_comments(p_rulv_rec);
1862 L:=length(S);
1863 if (L>0) then L:=L-2; end if;
1864
1865 return substr(s,1,least(1995,L));
1866
1867 exception when others then
1868 return '';
1869 end;
1870
1871 /*********************** END HAND-CODED **************************/
1872
1873 ---------------------------------------------------------------------------
1874 -- FUNCTION get_seq_id
1875 ---------------------------------------------------------------------------
1876 FUNCTION get_seq_id RETURN NUMBER IS
1877 --
1878 l_proc varchar2(72) := g_package||'get_seq_id';
1879 --
1880 BEGIN
1881 RETURN(okc_p_util.raw_to_number(sys_guid()));
1882 END get_seq_id;
1883
1884 ---------------------------------------------------------------------------
1885 -- PROCEDURE qc
1886 ---------------------------------------------------------------------------
1887 PROCEDURE qc IS
1888 --
1889 l_proc varchar2(72) := g_package||'qc';
1890 --
1891 BEGIN
1892 null;
1893 END qc;
1894
1895 ---------------------------------------------------------------------------
1896 -- PROCEDURE change_version
1897 ---------------------------------------------------------------------------
1898 PROCEDURE change_version IS
1899 --
1900 l_proc varchar2(72) := g_package||'change_version';
1901 --
1902 BEGIN
1903 null;
1904 END change_version;
1905
1906 ---------------------------------------------------------------------------
1907 -- PROCEDURE api_copy
1908 ---------------------------------------------------------------------------
1909 PROCEDURE api_copy IS
1910 --
1911 l_proc varchar2(72) := g_package||'api_copy';
1912 --
1913 BEGIN
1914 null;
1915 END api_copy;
1916
1917 ---------------------------------------------------------------------------
1918 -- PROCEDURE add_language
1919 ---------------------------------------------------------------------------
1920 PROCEDURE add_language IS
1921 --
1922 l_proc varchar2(72) := g_package||'add_language';
1923 --
1924 BEGIN
1925 null;
1926
1927 /*Bug 3055393 This function is obsolete as okc_rules_tl is removed
1928
1929 DELETE FROM OKC_RULES_TL T
1930 WHERE NOT EXISTS (
1931 SELECT NULL
1932 FROM OKC_RULES_B B
1933 WHERE B.ID = T.ID
1934 );
1935
1936
1937 -- Commented Update Statement for Bug 2801195
1938 UPDATE OKC_RULES_TL T SET (
1939 COMMENTS,
1940 TEXT) = (SELECT
1941 B.COMMENTS,
1942 B.TEXT
1943 FROM OKC_RULES_TL B
1944 WHERE B.ID = T.ID
1945 AND B.LANGUAGE = T.SOURCE_LANG)
1946 WHERE (
1947 T.ID,
1948 T.LANGUAGE)
1949 IN (SELECT
1950 SUBT.ID,
1951 SUBT.LANGUAGE
1952 FROM OKC_RULES_TL SUBB, OKC_RULES_TL SUBT
1953 WHERE SUBB.ID = SUBT.ID
1954 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
1955 AND (SUBB.COMMENTS <> SUBT.COMMENTS
1956 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
1957 OR (SUBB.COMMENTS IS NOT NULL AND SUBT.COMMENTS IS NULL)
1958 OR ((SUBB.TEXT IS NOT NULL AND SUBT.TEXT IS NOT NULL) AND
1959 (DBMS_LOB.COMPARE(SUBB.TEXT,SUBT.TEXT) <> 0))
1960 ));
1961
1962
1963 INSERT INTO OKC_RULES_TL (
1964 ID,
1965 LANGUAGE,
1966 SOURCE_LANG,
1967 SFWT_FLAG,
1968 COMMENTS,
1969 TEXT,
1970 CREATED_BY,
1971 CREATION_DATE,
1972 LAST_UPDATED_BY,
1973 LAST_UPDATE_DATE,
1974 LAST_UPDATE_LOGIN)
1975 SELECT
1976 B.ID,
1977 L.LANGUAGE_CODE,
1978 B.SOURCE_LANG,
1979 B.SFWT_FLAG,
1980 B.COMMENTS,
1981 B.TEXT,
1982 B.CREATED_BY,
1983 B.CREATION_DATE,
1984 B.LAST_UPDATED_BY,
1985 B.LAST_UPDATE_DATE,
1986 B.LAST_UPDATE_LOGIN
1987 FROM OKC_RULES_TL B, FND_LANGUAGES L
1988 WHERE L.INSTALLED_FLAG IN ('I', 'B')
1989 AND B.LANGUAGE = USERENV('LANG')
1990 AND NOT EXISTS(
1991 SELECT NULL
1992 FROM OKC_RULES_TL T
1993 WHERE T.ID = B.ID
1994 AND T.LANGUAGE = L.LANGUAGE_CODE
1995 );
1996 DELETE FROM OKC_RULES_TLH T
1997 WHERE NOT EXISTS (
1998 SELECT NULL
1999 FROM OKC_RULES_BH B
2000 WHERE B.ID = T.ID
2001 AND B.MAJOR_VERSION = T.MAJOR_VERSION
2002 );
2003
2004 UPDATE OKC_RULES_TLH T SET (
2005 COMMENTS,
2006 TEXT) = (SELECT
2007 B.COMMENTS,
2008 B.TEXT
2009 FROM OKC_RULES_TLH B
2010 WHERE B.ID = T.ID
2011 AND B.MAJOR_VERSION = T.MAJOR_VERSION
2012 AND B.LANGUAGE = T.SOURCE_LANG)
2013 WHERE (
2014 T.ID,
2015 T.MAJOR_VERSION,
2016 T.LANGUAGE)
2017 IN (SELECT
2018 SUBT.ID,
2019 SUBT.MAJOR_VERSION,
2020 SUBT.LANGUAGE
2021 FROM OKC_RULES_TLH SUBB, OKC_RULES_TLH SUBT
2022 WHERE SUBB.ID = SUBT.ID
2023 AND SUBB.MAJOR_VERSION = SUBT.MAJOR_VERSION
2024 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
2025 AND (SUBB.COMMENTS <> SUBT.COMMENTS
2026 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
2027 OR (SUBB.COMMENTS IS NOT NULL AND SUBT.COMMENTS IS NULL)
2028 OR ((SUBB.TEXT IS NOT NULL AND SUBT.TEXT IS NOT NULL) AND
2029 (DBMS_LOB.COMPARE(SUBB.TEXT,SUBT.TEXT) <> 0))
2030 ));
2031
2032 INSERT INTO OKC_RULES_TLH (
2033 ID,
2034 LANGUAGE,
2035 MAJOR_VERSION,
2036 SOURCE_LANG,
2037 SFWT_FLAG,
2038 COMMENTS,
2039 TEXT,
2040 CREATED_BY,
2041 CREATION_DATE,
2042 LAST_UPDATED_BY,
2043 LAST_UPDATE_DATE,
2044 LAST_UPDATE_LOGIN)
2045 SELECT
2046 B.ID,
2047 L.LANGUAGE_CODE,
2048 B.MAJOR_VERSION,
2049 B.SOURCE_LANG,
2050 B.SFWT_FLAG,
2051 B.COMMENTS,
2052 B.TEXT,
2053 B.CREATED_BY,
2054 B.CREATION_DATE,
2055 B.LAST_UPDATED_BY,
2056 B.LAST_UPDATE_DATE,
2057 B.LAST_UPDATE_LOGIN
2058 FROM OKC_RULES_TLH B, FND_LANGUAGES L
2059 WHERE L.INSTALLED_FLAG IN ('I', 'B')
2060 AND B.LANGUAGE = USERENV('LANG')
2061 AND NOT EXISTS(
2062 SELECT NULL
2063 FROM OKC_RULES_TLH T
2064 WHERE T.ID = B.ID
2065 AND T.MAJOR_VERSION = B.MAJOR_VERSION
2066 AND T.LANGUAGE = L.LANGUAGE_CODE
2067 );
2068
2069 */
2070 END add_language;
2071
2072 ---------------------------------------------------------------------------
2073 -- FUNCTION get_rec for: OKC_RULES_B
2074 ---------------------------------------------------------------------------
2075 FUNCTION get_rec (
2076 p_rul_rec IN rul_rec_type,
2077 x_no_data_found OUT NOCOPY BOOLEAN
2078 ) RETURN rul_rec_type IS
2079 CURSOR rul_pk_csr (p_id IN NUMBER) IS
2080 SELECT
2081 ID,
2082 RGP_ID,
2083 OBJECT1_ID1,
2084 OBJECT2_ID1,
2085 OBJECT3_ID1,
2086 OBJECT1_ID2,
2087 OBJECT2_ID2,
2088 OBJECT3_ID2,
2089 JTOT_OBJECT1_CODE,
2090 JTOT_OBJECT2_CODE,
2091 JTOT_OBJECT3_CODE,
2092 DNZ_CHR_ID,
2093 STD_TEMPLATE_YN,
2094 WARN_YN,
2095 PRIORITY,
2096 OBJECT_VERSION_NUMBER,
2097 CREATED_BY,
2098 CREATION_DATE,
2099 LAST_UPDATED_BY,
2100 LAST_UPDATE_DATE,
2101 LAST_UPDATE_LOGIN,
2102 ATTRIBUTE_CATEGORY,
2103 ATTRIBUTE1,
2104 ATTRIBUTE2,
2105 ATTRIBUTE3,
2106 ATTRIBUTE4,
2107 ATTRIBUTE5,
2108 ATTRIBUTE6,
2109 ATTRIBUTE7,
2110 ATTRIBUTE8,
2111 ATTRIBUTE9,
2112 ATTRIBUTE10,
2113 ATTRIBUTE11,
2114 ATTRIBUTE12,
2115 ATTRIBUTE13,
2116 ATTRIBUTE14,
2117 ATTRIBUTE15,
2118 RULE_INFORMATION_CATEGORY,
2119 RULE_INFORMATION1,
2120 RULE_INFORMATION2,
2121 RULE_INFORMATION3,
2122 RULE_INFORMATION4,
2123 RULE_INFORMATION5,
2124 RULE_INFORMATION6,
2125 RULE_INFORMATION7,
2126 RULE_INFORMATION8,
2127 RULE_INFORMATION9,
2128 RULE_INFORMATION10,
2129 RULE_INFORMATION11,
2130 RULE_INFORMATION12,
2131 RULE_INFORMATION13,
2132 RULE_INFORMATION14,
2133 RULE_INFORMATION15,
2134 TEMPLATE_YN,
2135 ans_set_jtot_object_code,
2136 ans_set_jtot_object_id1,
2137 ans_set_jtot_object_id2,
2138 DISPLAY_SEQUENCE,
2139 --Bug 3055393
2140 comments
2141 FROM Okc_Rules_B
2142 WHERE okc_rules_b.id = p_id;
2143 l_rul_pk rul_pk_csr%ROWTYPE;
2144 l_rul_rec rul_rec_type;
2145 --
2146 l_proc varchar2(72) := g_package||'get_rec';
2147 --
2148 BEGIN
2149
2150 x_no_data_found := TRUE;
2151 -- Get current database values
2152 OPEN rul_pk_csr (p_rul_rec.id);
2153 FETCH rul_pk_csr INTO
2154 l_rul_rec.ID,
2155 l_rul_rec.RGP_ID,
2156 l_rul_rec.OBJECT1_ID1,
2157 l_rul_rec.OBJECT2_ID1,
2158 l_rul_rec.OBJECT3_ID1,
2159 l_rul_rec.OBJECT1_ID2,
2160 l_rul_rec.OBJECT2_ID2,
2161 l_rul_rec.OBJECT3_ID2,
2162 l_rul_rec.JTOT_OBJECT1_CODE,
2163 l_rul_rec.JTOT_OBJECT2_CODE,
2164 l_rul_rec.JTOT_OBJECT3_CODE,
2165 l_rul_rec.DNZ_CHR_ID,
2166 l_rul_rec.STD_TEMPLATE_YN,
2167 l_rul_rec.WARN_YN,
2168 l_rul_rec.PRIORITY,
2169 l_rul_rec.OBJECT_VERSION_NUMBER,
2170 l_rul_rec.CREATED_BY,
2171 l_rul_rec.CREATION_DATE,
2172 l_rul_rec.LAST_UPDATED_BY,
2173 l_rul_rec.LAST_UPDATE_DATE,
2174 l_rul_rec.LAST_UPDATE_LOGIN,
2175 l_rul_rec.ATTRIBUTE_CATEGORY,
2176 l_rul_rec.ATTRIBUTE1,
2177 l_rul_rec.ATTRIBUTE2,
2178 l_rul_rec.ATTRIBUTE3,
2179 l_rul_rec.ATTRIBUTE4,
2180 l_rul_rec.ATTRIBUTE5,
2181 l_rul_rec.ATTRIBUTE6,
2182 l_rul_rec.ATTRIBUTE7,
2183 l_rul_rec.ATTRIBUTE8,
2184 l_rul_rec.ATTRIBUTE9,
2185 l_rul_rec.ATTRIBUTE10,
2186 l_rul_rec.ATTRIBUTE11,
2187 l_rul_rec.ATTRIBUTE12,
2188 l_rul_rec.ATTRIBUTE13,
2189 l_rul_rec.ATTRIBUTE14,
2190 l_rul_rec.ATTRIBUTE15,
2191 l_rul_rec.RULE_INFORMATION_CATEGORY,
2192 l_rul_rec.RULE_INFORMATION1,
2193 l_rul_rec.RULE_INFORMATION2,
2194 l_rul_rec.RULE_INFORMATION3,
2195 l_rul_rec.RULE_INFORMATION4,
2196 l_rul_rec.RULE_INFORMATION5,
2197 l_rul_rec.RULE_INFORMATION6,
2198 l_rul_rec.RULE_INFORMATION7,
2199 l_rul_rec.RULE_INFORMATION8,
2200 l_rul_rec.RULE_INFORMATION9,
2201 l_rul_rec.RULE_INFORMATION10,
2202 l_rul_rec.RULE_INFORMATION11,
2203 l_rul_rec.RULE_INFORMATION12,
2204 l_rul_rec.RULE_INFORMATION13,
2205 l_rul_rec.RULE_INFORMATION14,
2206 l_rul_rec.RULE_INFORMATION15,
2207 l_rul_rec.TEMPLATE_YN,
2208 l_rul_rec.ans_set_jtot_object_code,
2209 l_rul_rec.ans_set_jtot_object_id1,
2210 l_rul_rec.ans_set_jtot_object_id2,
2211 l_rul_rec.DISPLAY_SEQUENCE,
2212 --Bug 3055393
2213 l_rul_rec.comments ;
2214 x_no_data_found := rul_pk_csr%NOTFOUND;
2215 CLOSE rul_pk_csr;
2216 RETURN(l_rul_rec);
2217 END get_rec;
2218
2219 FUNCTION get_rec (
2220 p_rul_rec IN rul_rec_type
2221 ) RETURN rul_rec_type IS
2222 l_row_notfound BOOLEAN := TRUE;
2223 --
2224 l_proc varchar2(72) := g_package||'get_rec';
2225 --
2226 BEGIN
2227 RETURN(get_rec(p_rul_rec, l_row_notfound));
2228 END get_rec;
2229 ---------------------------------------------------------------------------
2230 -- FUNCTION get_rec for: OKC_RULES_TL
2231 ---------------------------------------------------------------------------
2232 /*--Bug 3055393
2233 FUNCTION get_rec (
2234 p_okc_rules_tl_rec IN okc_rules_tl_rec_type,
2235 x_no_data_found OUT NOCOPY BOOLEAN
2236 ) RETURN okc_rules_tl_rec_type IS
2237 CURSOR rul_pktl_csr (p_id IN NUMBER,
2238 p_language IN VARCHAR2) IS
2239 SELECT
2240 ID,
2241 LANGUAGE,
2242 SOURCE_LANG,
2243 SFWT_FLAG,
2244 COMMENTS,
2245 TEXT,
2246 CREATED_BY,
2247 CREATION_DATE,
2248 LAST_UPDATED_BY,
2249 LAST_UPDATE_DATE,
2250 LAST_UPDATE_LOGIN
2251 FROM Okc_Rules_Tl
2252 WHERE okc_rules_tl.id = p_id
2253 AND okc_rules_tl.language = p_language;
2254 l_rul_pktl rul_pktl_csr%ROWTYPE;
2255 l_okc_rules_tl_rec okc_rules_tl_rec_type;
2256 --
2257 l_proc varchar2(72) := g_package||'get_rec';
2258 --
2259 BEGIN
2260 x_no_data_found := TRUE;
2261 -- Get current database values
2262 OPEN rul_pktl_csr (p_okc_rules_tl_rec.id,
2263 p_okc_rules_tl_rec.language);
2264 FETCH rul_pktl_csr INTO
2265 l_okc_rules_tl_rec.ID,
2266 l_okc_rules_tl_rec.LANGUAGE,
2267 l_okc_rules_tl_rec.SOURCE_LANG,
2268 l_okc_rules_tl_rec.SFWT_FLAG,
2269 l_okc_rules_tl_rec.COMMENTS,
2270 l_okc_rules_tl_rec.TEXT,
2271 l_okc_rules_tl_rec.CREATED_BY,
2272 l_okc_rules_tl_rec.CREATION_DATE,
2273 l_okc_rules_tl_rec.LAST_UPDATED_BY,
2274 l_okc_rules_tl_rec.LAST_UPDATE_DATE,
2275 l_okc_rules_tl_rec.LAST_UPDATE_LOGIN;
2276 x_no_data_found := rul_pktl_csr%NOTFOUND;
2277 CLOSE rul_pktl_csr;
2278
2279 RETURN(l_okc_rules_tl_rec);
2280 END get_rec;
2281
2282 FUNCTION get_rec (
2283 p_okc_rules_tl_rec IN okc_rules_tl_rec_type
2284 ) RETURN okc_rules_tl_rec_type IS
2285 l_row_notfound BOOLEAN := TRUE;
2286 --
2287 l_proc varchar2(72) := g_package||'get_rec';
2288 --
2289 BEGIN
2290 RETURN(get_rec(p_okc_rules_tl_rec, l_row_notfound));
2291 END get_rec;
2292 */
2293 ---------------------------------------------------------------------------
2294 -- FUNCTION get_rec for: OKC_RULES_V
2295 ---------------------------------------------------------------------------
2296 FUNCTION get_rec (
2297 p_rulv_rec IN rulv_rec_type,
2298 x_no_data_found OUT NOCOPY BOOLEAN
2299 ) RETURN rulv_rec_type IS
2300 CURSOR okc_rulv_pk_csr (p_id IN NUMBER) IS
2301 SELECT
2302 ID,
2303 OBJECT_VERSION_NUMBER,
2304 --Bug 3055393 SFWT_FLAG,
2305 OBJECT1_ID1,
2306 OBJECT2_ID1,
2307 OBJECT3_ID1,
2308 OBJECT1_ID2,
2309 OBJECT2_ID2,
2310 OBJECT3_ID2,
2311 JTOT_OBJECT1_CODE,
2312 JTOT_OBJECT2_CODE,
2313 JTOT_OBJECT3_CODE,
2314 DNZ_CHR_ID,
2315 RGP_ID,
2316 PRIORITY,
2317 STD_TEMPLATE_YN,
2318 COMMENTS,
2319 WARN_YN,
2320 ATTRIBUTE_CATEGORY,
2321 ATTRIBUTE1,
2322 ATTRIBUTE2,
2323 ATTRIBUTE3,
2324 ATTRIBUTE4,
2325 ATTRIBUTE5,
2326 ATTRIBUTE6,
2327 ATTRIBUTE7,
2328 ATTRIBUTE8,
2329 ATTRIBUTE9,
2330 ATTRIBUTE10,
2331 ATTRIBUTE11,
2332 ATTRIBUTE12,
2333 ATTRIBUTE13,
2334 ATTRIBUTE14,
2335 ATTRIBUTE15,
2336 CREATED_BY,
2337 CREATION_DATE,
2338 LAST_UPDATED_BY,
2339 LAST_UPDATE_DATE,
2340 LAST_UPDATE_LOGIN,
2341 --Bug 3055393 TEXT,
2342 RULE_INFORMATION_CATEGORY,
2343 RULE_INFORMATION1,
2344 RULE_INFORMATION2,
2345 RULE_INFORMATION3,
2346 RULE_INFORMATION4,
2347 RULE_INFORMATION5,
2348 RULE_INFORMATION6,
2349 RULE_INFORMATION7,
2350 RULE_INFORMATION8,
2351 RULE_INFORMATION9,
2352 RULE_INFORMATION10,
2353 RULE_INFORMATION11,
2354 RULE_INFORMATION12,
2355 RULE_INFORMATION13,
2356 RULE_INFORMATION14,
2357 RULE_INFORMATION15,
2358 TEMPLATE_YN,
2359 ans_set_jtot_object_code,
2360 ans_set_jtot_object_id1,
2361 ans_set_jtot_object_id2,
2362 DISPLAY_SEQUENCE
2363 FROM Okc_Rules_V
2364 WHERE okc_rules_v.id = p_id;
2365 l_okc_rulv_pk okc_rulv_pk_csr%ROWTYPE;
2366 l_rulv_rec rulv_rec_type;
2367 --
2368 l_proc varchar2(72) := g_package||'get_rec';
2369 --
2370 BEGIN
2371 x_no_data_found := TRUE;
2372 -- Get current database values
2373 OPEN okc_rulv_pk_csr (p_rulv_rec.id);
2374 FETCH okc_rulv_pk_csr INTO
2375 l_rulv_rec.ID,
2376 l_rulv_rec.OBJECT_VERSION_NUMBER,
2377 --Bug 3055393 l_rulv_rec.SFWT_FLAG,
2378 l_rulv_rec.OBJECT1_ID1,
2379 l_rulv_rec.OBJECT2_ID1,
2380 l_rulv_rec.OBJECT3_ID1,
2381 l_rulv_rec.OBJECT1_ID2,
2382 l_rulv_rec.OBJECT2_ID2,
2383 l_rulv_rec.OBJECT3_ID2,
2384 l_rulv_rec.JTOT_OBJECT1_CODE,
2385 l_rulv_rec.JTOT_OBJECT2_CODE,
2386 l_rulv_rec.JTOT_OBJECT3_CODE,
2387 l_rulv_rec.DNZ_CHR_ID,
2388 l_rulv_rec.RGP_ID,
2389 l_rulv_rec.PRIORITY,
2390 l_rulv_rec.STD_TEMPLATE_YN,
2391 l_rulv_rec.COMMENTS,
2392 l_rulv_rec.WARN_YN,
2393 l_rulv_rec.ATTRIBUTE_CATEGORY,
2394 l_rulv_rec.ATTRIBUTE1,
2395 l_rulv_rec.ATTRIBUTE2,
2396 l_rulv_rec.ATTRIBUTE3,
2397 l_rulv_rec.ATTRIBUTE4,
2398 l_rulv_rec.ATTRIBUTE5,
2399 l_rulv_rec.ATTRIBUTE6,
2400 l_rulv_rec.ATTRIBUTE7,
2401 l_rulv_rec.ATTRIBUTE8,
2402 l_rulv_rec.ATTRIBUTE9,
2403 l_rulv_rec.ATTRIBUTE10,
2404 l_rulv_rec.ATTRIBUTE11,
2405 l_rulv_rec.ATTRIBUTE12,
2406 l_rulv_rec.ATTRIBUTE13,
2407 l_rulv_rec.ATTRIBUTE14,
2408 l_rulv_rec.ATTRIBUTE15,
2409 l_rulv_rec.CREATED_BY,
2410 l_rulv_rec.CREATION_DATE,
2411 l_rulv_rec.LAST_UPDATED_BY,
2412 l_rulv_rec.LAST_UPDATE_DATE,
2413 l_rulv_rec.LAST_UPDATE_LOGIN,
2414 --Bug 3055393 l_rulv_rec.TEXT,
2415 l_rulv_rec.RULE_INFORMATION_CATEGORY,
2416 l_rulv_rec.RULE_INFORMATION1,
2417 l_rulv_rec.RULE_INFORMATION2,
2418 l_rulv_rec.RULE_INFORMATION3,
2419 l_rulv_rec.RULE_INFORMATION4,
2420 l_rulv_rec.RULE_INFORMATION5,
2421 l_rulv_rec.RULE_INFORMATION6,
2422 l_rulv_rec.RULE_INFORMATION7,
2423 l_rulv_rec.RULE_INFORMATION8,
2424 l_rulv_rec.RULE_INFORMATION9,
2425 l_rulv_rec.RULE_INFORMATION10,
2426 l_rulv_rec.RULE_INFORMATION11,
2427 l_rulv_rec.RULE_INFORMATION12,
2428 l_rulv_rec.RULE_INFORMATION13,
2429 l_rulv_rec.RULE_INFORMATION14,
2430 l_rulv_rec.RULE_INFORMATION15,
2431 l_rulv_rec.TEMPLATE_YN,
2432 l_rulv_rec.ans_set_jtot_object_code,
2433 l_rulv_rec.ans_set_jtot_object_id1,
2434 l_rulv_rec.ans_set_jtot_object_id2,
2435 l_rulv_rec.DISPLAY_SEQUENCE ;
2436 x_no_data_found := okc_rulv_pk_csr%NOTFOUND;
2437 CLOSE okc_rulv_pk_csr;
2438 RETURN(l_rulv_rec);
2439 END get_rec;
2440
2441 FUNCTION get_rec (
2442 p_rulv_rec IN rulv_rec_type
2443 ) RETURN rulv_rec_type IS
2444 l_row_notfound BOOLEAN := TRUE;
2445 --
2446 l_proc varchar2(72) := g_package||'get_rec';
2447 --
2448 BEGIN
2449 RETURN(get_rec(p_rulv_rec, l_row_notfound));
2450 END get_rec;
2451
2452 -------------------------------------------------
2453 -- FUNCTION null_out_defaults for: OKC_RULES_V --
2454 -------------------------------------------------
2455 FUNCTION null_out_defaults (
2456 p_rulv_rec IN rulv_rec_type
2457 ) RETURN rulv_rec_type IS
2458 l_rulv_rec rulv_rec_type := p_rulv_rec;
2459 --
2460 l_proc varchar2(72) := g_package||'null_out_defaults';
2461 --
2462 BEGIN
2463 IF (l_rulv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
2464 l_rulv_rec.object_version_number := NULL;
2465 END IF;
2466 /*--Bug 3055393
2467 IF (l_rulv_rec.sfwt_flag = OKC_API.G_MISS_CHAR) THEN
2468 l_rulv_rec.sfwt_flag := NULL;
2469 END IF;
2470 */
2471 IF (l_rulv_rec.object1_id1 = OKC_API.G_MISS_CHAR) THEN
2472 l_rulv_rec.object1_id1 := NULL;
2473 END IF;
2474 IF (l_rulv_rec.object2_id1 = OKC_API.G_MISS_CHAR) THEN
2475 l_rulv_rec.object2_id1 := NULL;
2476 END IF;
2477 IF (l_rulv_rec.object3_id1 = OKC_API.G_MISS_CHAR) THEN
2478 l_rulv_rec.object3_id1 := NULL;
2479 END IF;
2480 IF (l_rulv_rec.object1_id2 = OKC_API.G_MISS_CHAR) THEN
2481 l_rulv_rec.object1_id2 := NULL;
2482 END IF;
2483 IF (l_rulv_rec.object2_id2 = OKC_API.G_MISS_CHAR) THEN
2484 l_rulv_rec.object2_id2 := NULL;
2485 END IF;
2486 IF (l_rulv_rec.object3_id2 = OKC_API.G_MISS_CHAR) THEN
2487 l_rulv_rec.object3_id2 := NULL;
2488 END IF;
2489 IF (l_rulv_rec.jtot_object1_code = OKC_API.G_MISS_CHAR) THEN
2490 l_rulv_rec.jtot_object1_code := NULL;
2491 END IF;
2492 IF (l_rulv_rec.jtot_object2_code = OKC_API.G_MISS_CHAR) THEN
2493 l_rulv_rec.jtot_object2_code := NULL;
2494 END IF;
2495 IF (l_rulv_rec.jtot_object3_code = OKC_API.G_MISS_CHAR) THEN
2496 l_rulv_rec.jtot_object3_code := NULL;
2497 END IF;
2498 IF (l_rulv_rec.dnz_chr_id = OKC_API.G_MISS_NUM) THEN
2499 l_rulv_rec.dnz_chr_id := NULL;
2500 END IF;
2501 IF (l_rulv_rec.rgp_id = OKC_API.G_MISS_NUM) THEN
2502 l_rulv_rec.rgp_id := NULL;
2503 END IF;
2504 IF (l_rulv_rec.priority = OKC_API.G_MISS_NUM) THEN
2505 l_rulv_rec.priority := NULL;
2506 END IF;
2507 IF (l_rulv_rec.std_template_yn = OKC_API.G_MISS_CHAR) THEN
2508 l_rulv_rec.std_template_yn := NULL;
2509 END IF;
2510 IF (l_rulv_rec.comments = OKC_API.G_MISS_CHAR) THEN
2511 l_rulv_rec.comments := NULL;
2512 END IF;
2513 IF (l_rulv_rec.warn_yn = OKC_API.G_MISS_CHAR) THEN
2514 l_rulv_rec.warn_yn := NULL;
2515 END IF;
2516 IF (l_rulv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
2517 l_rulv_rec.attribute_category := NULL;
2518 END IF;
2519 IF (l_rulv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
2520 l_rulv_rec.attribute1 := NULL;
2521 END IF;
2522 IF (l_rulv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
2523 l_rulv_rec.attribute2 := NULL;
2524 END IF;
2525 IF (l_rulv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
2526 l_rulv_rec.attribute3 := NULL;
2527 END IF;
2528 IF (l_rulv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
2529 l_rulv_rec.attribute4 := NULL;
2530 END IF;
2531 IF (l_rulv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
2532 l_rulv_rec.attribute5 := NULL;
2533 END IF;
2534 IF (l_rulv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
2535 l_rulv_rec.attribute6 := NULL;
2536 END IF;
2537 IF (l_rulv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
2538 l_rulv_rec.attribute7 := NULL;
2539 END IF;
2540 IF (l_rulv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
2541 l_rulv_rec.attribute8 := NULL;
2542 END IF;
2543 IF (l_rulv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
2544 l_rulv_rec.attribute9 := NULL;
2545 END IF;
2546 IF (l_rulv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
2547 l_rulv_rec.attribute10 := NULL;
2548 END IF;
2549 IF (l_rulv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
2550 l_rulv_rec.attribute11 := NULL;
2551 END IF;
2552 IF (l_rulv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
2553 l_rulv_rec.attribute12 := NULL;
2554 END IF;
2555 IF (l_rulv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
2556 l_rulv_rec.attribute13 := NULL;
2557 END IF;
2558 IF (l_rulv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
2559 l_rulv_rec.attribute14 := NULL;
2560 END IF;
2561 IF (l_rulv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
2562 l_rulv_rec.attribute15 := NULL;
2563 END IF;
2564 IF (l_rulv_rec.created_by = OKC_API.G_MISS_NUM) THEN
2565 l_rulv_rec.created_by := NULL;
2566 END IF;
2567 IF (l_rulv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
2568 l_rulv_rec.creation_date := NULL;
2569 END IF;
2570 IF (l_rulv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
2571 l_rulv_rec.last_updated_by := NULL;
2572 END IF;
2573 IF (l_rulv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
2574 l_rulv_rec.last_update_date := NULL;
2575 END IF;
2576 IF (l_rulv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
2577 l_rulv_rec.last_update_login := NULL;
2578 END IF;
2579 --IF (DBMS_LOB.COMPARE(l_rulv_rec.text,G_MISS_CLOB)=0) THEN
2580 -- l_rulv_rec.text := NULL;
2581 --END IF;
2582 IF (l_rulv_rec.rule_information_category = OKC_API.G_MISS_CHAR) THEN
2583 l_rulv_rec.rule_information_category := NULL;
2584 END IF;
2585 IF (l_rulv_rec.rule_information1 = OKC_API.G_MISS_CHAR) THEN
2586 l_rulv_rec.rule_information1 := NULL;
2587 END IF;
2588 IF (l_rulv_rec.rule_information2 = OKC_API.G_MISS_CHAR) THEN
2589 l_rulv_rec.rule_information2 := NULL;
2590 END IF;
2591 IF (l_rulv_rec.rule_information3 = OKC_API.G_MISS_CHAR) THEN
2592 l_rulv_rec.rule_information3 := NULL;
2593 END IF;
2594 IF (l_rulv_rec.rule_information4 = OKC_API.G_MISS_CHAR) THEN
2595 l_rulv_rec.rule_information4 := NULL;
2596 END IF;
2597 IF (l_rulv_rec.rule_information5 = OKC_API.G_MISS_CHAR) THEN
2598 l_rulv_rec.rule_information5 := NULL;
2599 END IF;
2600 IF (l_rulv_rec.rule_information6 = OKC_API.G_MISS_CHAR) THEN
2601 l_rulv_rec.rule_information6 := NULL;
2602 END IF;
2603 IF (l_rulv_rec.rule_information7 = OKC_API.G_MISS_CHAR) THEN
2604 l_rulv_rec.rule_information7 := NULL;
2605 END IF;
2606 IF (l_rulv_rec.rule_information8 = OKC_API.G_MISS_CHAR) THEN
2607 l_rulv_rec.rule_information8 := NULL;
2608 END IF;
2609 IF (l_rulv_rec.rule_information9 = OKC_API.G_MISS_CHAR) THEN
2610 l_rulv_rec.rule_information9 := NULL;
2611 END IF;
2612 IF (l_rulv_rec.rule_information10 = OKC_API.G_MISS_CHAR) THEN
2613 l_rulv_rec.rule_information10 := NULL;
2614 END IF;
2615 IF (l_rulv_rec.rule_information11 = OKC_API.G_MISS_CHAR) THEN
2616 l_rulv_rec.rule_information11 := NULL;
2617 END IF;
2618 IF (l_rulv_rec.rule_information12 = OKC_API.G_MISS_CHAR) THEN
2619 l_rulv_rec.rule_information12 := NULL;
2620 END IF;
2621 IF (l_rulv_rec.rule_information13 = OKC_API.G_MISS_CHAR) THEN
2622 l_rulv_rec.rule_information13 := NULL;
2623 END IF;
2624 IF (l_rulv_rec.rule_information14 = OKC_API.G_MISS_CHAR) THEN
2625 l_rulv_rec.rule_information14 := NULL;
2626 END IF;
2627 IF (l_rulv_rec.rule_information15 = OKC_API.G_MISS_CHAR) THEN
2628 l_rulv_rec.rule_information15 := NULL;
2629 END IF;
2630 IF (l_rulv_rec.template_yn = OKC_API.G_MISS_CHAR) THEN
2631 l_rulv_rec.template_yn := 'N';
2632 END IF;
2633 IF (l_rulv_rec.ans_set_jtot_object_code = OKC_API.G_MISS_CHAR) THEN
2634 l_rulv_rec.ans_set_jtot_object_code := NULL;
2635 END IF;
2636 IF (l_rulv_rec.ans_set_jtot_object_id1 = OKC_API.G_MISS_CHAR) THEN
2637 l_rulv_rec.ans_set_jtot_object_id1 := NULL;
2638 END IF;
2639 IF (l_rulv_rec.ans_set_jtot_object_id2 = OKC_API.G_MISS_CHAR) THEN
2640 l_rulv_rec.ans_set_jtot_object_id2 := NULL;
2641 END IF;
2642 IF (l_rulv_rec.display_sequence = OKC_API.G_MISS_NUM) THEN
2643 l_rulv_rec.display_sequence := NULL;
2644 END IF;
2645
2646 RETURN(l_rulv_rec);
2647 END null_out_defaults;
2648 ---------------------------------------------------------------------------
2649 -- PROCEDURE Validate_Attributes
2650 ---------------------------------------------------------------------------
2651 -----------------------------------------
2652 -- Validate_Attributes for:OKC_RULES_V --
2653 -----------------------------------------
2654 /* commenting out nocopy generated code in favor of hand-coded procedure
2655 FUNCTION Validate_Attributes (
2656 p_rulv_rec IN rulv_rec_type
2657 ) RETURN VARCHAR2 IS
2658 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2659 --
2660 l_proc varchar2(72) := g_package||'Validate_Attributes';
2661 --
2662 BEGIN
2663
2664
2665
2666
2667 IF p_rulv_rec.id = OKC_API.G_MISS_NUM OR
2668 p_rulv_rec.id IS NULL
2669 THEN
2670 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
2671 l_return_status := OKC_API.G_RET_STS_ERROR;
2672 ELSIF p_rulv_rec.object_version_number = OKC_API.G_MISS_NUM OR
2673 p_rulv_rec.object_version_number IS NULL
2674 THEN
2675 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
2676 l_return_status := OKC_API.G_RET_STS_ERROR;
2677 ELSIF p_rulv_rec.dnz_chr_id = OKC_API.G_MISS_NUM OR
2678 p_rulv_rec.dnz_chr_id IS NULL
2679 THEN
2680 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'dnz_chr_id');
2681 l_return_status := OKC_API.G_RET_STS_ERROR;
2682 ELSIF p_rulv_rec.std_template_yn = OKC_API.G_MISS_CHAR OR
2683 p_rulv_rec.std_template_yn IS NULL
2684 THEN
2685 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'std_template_yn');
2686 l_return_status := OKC_API.G_RET_STS_ERROR;
2687 ELSIF p_rulv_rec.warn_yn = OKC_API.G_MISS_CHAR OR
2688 p_rulv_rec.warn_yn IS NULL
2689 THEN
2690 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'warn_yn');
2691 l_return_status := OKC_API.G_RET_STS_ERROR;
2692 ELSIF p_rulv_rec.rule_information_category = OKC_API.G_MISS_CHAR OR
2693 p_rulv_rec.rule_information_category IS NULL
2694 THEN
2695 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'rule_information_category');
2696 l_return_status := OKC_API.G_RET_STS_ERROR;
2697 END IF;
2698
2699
2700
2701
2702 RETURN(l_return_status);
2703 END Validate_Attributes;
2704 */
2705 ---------------------------------------------------------------------------
2706 -- PROCEDURE Validate_Record
2707 ---------------------------------------------------------------------------
2708 -------------------------------------
2709 -- Validate_Record for:OKC_RULES_V --
2710 -------------------------------------
2711 FUNCTION Validate_Record (
2712 p_rulv_rec IN rulv_rec_type
2713 ) RETURN VARCHAR2 IS
2714 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2715 --
2716 l_proc varchar2(72) := g_package||'Validate_Record';
2717 --
2718 BEGIN
2719 RETURN (l_return_status);
2720 END Validate_Record;
2721
2722 ---------------------------------------------------------------------------
2723 -- PROCEDURE Migrate
2724 ---------------------------------------------------------------------------
2725 PROCEDURE migrate (
2726 p_from IN rulv_rec_type,
2727 p_to IN OUT NOCOPY rul_rec_type
2728 ) IS
2729 --
2730 l_proc varchar2(72) := g_package||'migrate';
2731 --
2732 BEGIN
2733 p_to.id := p_from.id;
2734 p_to.rgp_id := p_from.rgp_id;
2735 p_to.object1_id1 := p_from.object1_id1;
2736 p_to.object2_id1 := p_from.object2_id1;
2737 p_to.object3_id1 := p_from.object3_id1;
2738 p_to.object1_id2 := p_from.object1_id2;
2739 p_to.object2_id2 := p_from.object2_id2;
2740 p_to.object3_id2 := p_from.object3_id2;
2741 p_to.jtot_object1_code := p_from.jtot_object1_code;
2742 p_to.jtot_object2_code := p_from.jtot_object2_code;
2743 p_to.jtot_object3_code := p_from.jtot_object3_code;
2744 p_to.dnz_chr_id := p_from.dnz_chr_id;
2745 p_to.std_template_yn := p_from.std_template_yn;
2746 p_to.warn_yn := p_from.warn_yn;
2747 p_to.priority := p_from.priority;
2748 p_to.object_version_number := p_from.object_version_number;
2749 p_to.created_by := p_from.created_by;
2750 p_to.creation_date := p_from.creation_date;
2751 p_to.last_updated_by := p_from.last_updated_by;
2752 p_to.last_update_date := p_from.last_update_date;
2753 p_to.last_update_login := p_from.last_update_login;
2754 p_to.attribute_category := p_from.attribute_category;
2755 p_to.attribute1 := p_from.attribute1;
2756 p_to.attribute2 := p_from.attribute2;
2757 p_to.attribute3 := p_from.attribute3;
2758 p_to.attribute4 := p_from.attribute4;
2759 p_to.attribute5 := p_from.attribute5;
2760 p_to.attribute6 := p_from.attribute6;
2761 p_to.attribute7 := p_from.attribute7;
2762 p_to.attribute8 := p_from.attribute8;
2763 p_to.attribute9 := p_from.attribute9;
2764 p_to.attribute10 := p_from.attribute10;
2765 p_to.attribute11 := p_from.attribute11;
2766 p_to.attribute11 := p_from.rule_information11;
2767 p_to.attribute12 := p_from.attribute12;
2768 p_to.attribute13 := p_from.attribute13;
2769 p_to.attribute14 := p_from.attribute14;
2770 p_to.attribute15 := p_from.attribute15;
2771 p_to.rule_information_category := p_from.rule_information_category;
2772 p_to.rule_information1 := p_from.rule_information1;
2773 p_to.rule_information2 := p_from.rule_information2;
2774 p_to.rule_information3 := p_from.rule_information3;
2775 p_to.rule_information4 := p_from.rule_information4;
2776 p_to.rule_information5 := p_from.rule_information5;
2777 p_to.rule_information6 := p_from.rule_information6;
2778 p_to.rule_information7 := p_from.rule_information7;
2779 p_to.rule_information8 := p_from.rule_information8;
2780 p_to.rule_information9 := p_from.rule_information9;
2781 p_to.rule_information10 := p_from.rule_information10;
2782 p_to.rule_information11 := p_from.rule_information11;
2783 p_to.rule_information12 := p_from.rule_information12;
2784 p_to.rule_information13 := p_from.rule_information13;
2785 p_to.rule_information14 := p_from.rule_information14;
2786 p_to.rule_information15 := p_from.rule_information15;
2787 p_to.template_yn := p_from.template_yn;
2788 p_to.ans_set_jtot_object_code := p_from.ans_set_jtot_object_code;
2789 p_to.ans_set_jtot_object_id1 := p_from.ans_set_jtot_object_id1;
2790 p_to.ans_set_jtot_object_id2 := p_from.ans_set_jtot_object_id2;
2791 p_to.display_sequence := p_from.display_sequence;
2792 --Bug 3055393
2793 p_to.comments := p_from.comments;
2794
2795
2796 END migrate;
2797
2798 PROCEDURE migrate (
2799 p_from IN rul_rec_type,
2800 p_to IN OUT NOCOPY rulv_rec_type
2801 ) IS
2802 --
2803 l_proc varchar2(72) := g_package||'migrate';
2804 --
2805 BEGIN
2806
2807
2808
2809
2810 p_to.id := p_from.id;
2811 p_to.rgp_id := p_from.rgp_id;
2812 p_to.object1_id1 := p_from.object1_id1;
2813 p_to.object2_id1 := p_from.object2_id1;
2814 p_to.object3_id1 := p_from.object3_id1;
2815 p_to.object1_id2 := p_from.object1_id2;
2816 p_to.object2_id2 := p_from.object2_id2;
2817 p_to.object3_id2 := p_from.object3_id2;
2818 p_to.jtot_object1_code := p_from.jtot_object1_code;
2819 p_to.jtot_object2_code := p_from.jtot_object2_code;
2820 p_to.jtot_object3_code := p_from.jtot_object3_code;
2821 p_to.dnz_chr_id := p_from.dnz_chr_id;
2822 p_to.std_template_yn := p_from.std_template_yn;
2823 p_to.warn_yn := p_from.warn_yn;
2824 p_to.priority := p_from.priority;
2825 p_to.object_version_number := p_from.object_version_number;
2826 p_to.created_by := p_from.created_by;
2827 p_to.creation_date := p_from.creation_date;
2828 p_to.last_updated_by := p_from.last_updated_by;
2829 p_to.last_update_date := p_from.last_update_date;
2830 p_to.last_update_login := p_from.last_update_login;
2831 p_to.attribute_category := p_from.attribute_category;
2832 p_to.attribute1 := p_from.attribute1;
2833 p_to.attribute2 := p_from.attribute2;
2834 p_to.attribute3 := p_from.attribute3;
2835 p_to.attribute4 := p_from.attribute4;
2836 p_to.attribute5 := p_from.attribute5;
2837 p_to.attribute6 := p_from.attribute6;
2838 p_to.attribute7 := p_from.attribute7;
2839 p_to.attribute8 := p_from.attribute8;
2840 p_to.attribute9 := p_from.attribute9;
2841 p_to.attribute10 := p_from.attribute10;
2842 p_to.attribute11 := p_from.attribute11;
2843 p_to.attribute11 := p_from.rule_information11;
2844 p_to.attribute12 := p_from.attribute12;
2845 p_to.attribute13 := p_from.attribute13;
2846 p_to.attribute14 := p_from.attribute14;
2847 p_to.attribute15 := p_from.attribute15;
2848 p_to.rule_information_category := p_from.rule_information_category;
2849 p_to.rule_information1 := p_from.rule_information1;
2850 p_to.rule_information2 := p_from.rule_information2;
2851 p_to.rule_information3 := p_from.rule_information3;
2852 p_to.rule_information4 := p_from.rule_information4;
2853 p_to.rule_information5 := p_from.rule_information5;
2854 p_to.rule_information6 := p_from.rule_information6;
2855 p_to.rule_information7 := p_from.rule_information7;
2856 p_to.rule_information8 := p_from.rule_information8;
2857 p_to.rule_information9 := p_from.rule_information9;
2858 p_to.rule_information10 := p_from.rule_information10;
2859 p_to.rule_information11 := p_from.rule_information11;
2860 p_to.rule_information12 := p_from.rule_information12;
2861 p_to.rule_information13 := p_from.rule_information13;
2862 p_to.rule_information14 := p_from.rule_information14;
2863 p_to.rule_information15 := p_from.rule_information15;
2864 p_to.template_yn := p_from.template_yn;
2865 p_to.ans_set_jtot_object_code := p_from.ans_set_jtot_object_code;
2866 p_to.ans_set_jtot_object_id1 := p_from.ans_set_jtot_object_id1;
2867 p_to.ans_set_jtot_object_id2 := p_from.ans_set_jtot_object_id2;
2868 p_to.display_sequence := p_from.display_sequence;
2869 --Bug 3055393
2870 p_to.comments := p_from.comments;
2871
2872 END migrate;
2873 /*--Bug 3055393
2874 PROCEDURE migrate (
2875 p_from IN rulv_rec_type,
2876 p_to IN OUT NOCOPY okc_rules_tl_rec_type
2877 ) IS
2878 --
2879 l_proc varchar2(72) := g_package||'migrate';
2880 --
2881 BEGIN
2882
2883
2884
2885
2886 p_to.id := p_from.id;
2887 p_to.sfwt_flag := p_from.sfwt_flag;
2888 p_to.comments := p_from.comments;
2889 p_to.text := p_from.text;
2890 p_to.created_by := p_from.created_by;
2891 p_to.creation_date := p_from.creation_date;
2892 p_to.last_updated_by := p_from.last_updated_by;
2893 p_to.last_update_date := p_from.last_update_date;
2894 p_to.last_update_login := p_from.last_update_login;
2895
2896
2897
2898
2899 END migrate;
2900 */
2901 /*--Bug 3055393
2902 PROCEDURE migrate (
2903 p_from IN okc_rules_tl_rec_type,
2904 p_to IN OUT NOCOPY rulv_rec_type
2905 ) IS
2906 --
2907 l_proc varchar2(72) := g_package||'migrate';
2908 --
2909 BEGIN
2910
2911
2912
2913
2914 p_to.id := p_from.id;
2915 p_to.sfwt_flag := p_from.sfwt_flag;
2916 p_to.comments := p_from.comments;
2917 p_to.text := p_from.text;
2918 p_to.created_by := p_from.created_by;
2919 p_to.creation_date := p_from.creation_date;
2920 p_to.last_updated_by := p_from.last_updated_by;
2921 p_to.last_update_date := p_from.last_update_date;
2922 p_to.last_update_login := p_from.last_update_login;
2923
2924
2925
2926
2927 END migrate;
2928 */
2929 ---------------------------------------------------------------------------
2930 -- PROCEDURE validate_row
2931 ---------------------------------------------------------------------------
2932 ----------------------------------
2933 -- validate_row for:OKC_RULES_V --
2934 ----------------------------------
2935 PROCEDURE validate_row(
2936 p_api_version IN NUMBER,
2937 p_init_msg_list IN VARCHAR2,
2938 x_return_status OUT NOCOPY VARCHAR2,
2939 x_msg_count OUT NOCOPY NUMBER,
2940 x_msg_data OUT NOCOPY VARCHAR2,
2941 p_rulv_rec IN rulv_rec_type) IS
2942
2943 l_api_version CONSTANT NUMBER := 1;
2944 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
2945 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2946 l_rulv_rec rulv_rec_type := p_rulv_rec;
2947 l_rul_rec rul_rec_type;
2948 --Bug 3055393 l_okc_rules_tl_rec okc_rules_tl_rec_type;
2949 --
2950 l_proc varchar2(72) := g_package||'validate_row';
2951 --
2952 BEGIN
2953
2954
2955
2956
2957 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2958 G_PKG_NAME,
2959 p_init_msg_list,
2960 l_api_version,
2961 p_api_version,
2962 '_PVT',
2963 x_return_status);
2964 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2965 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2966 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2967 RAISE OKC_API.G_EXCEPTION_ERROR;
2968 END IF;
2969
2970 IF p_rulv_rec.VALIDATE_YN = 'Y' THEN
2971 --- Validate all non-missing attributes (Item Level Validation)
2972 l_return_status := Validate_Attributes(l_rulv_rec);
2973 --- If any errors happen abort API
2974 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2975 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2976 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2977 RAISE OKC_API.G_EXCEPTION_ERROR;
2978 END IF;
2979 END IF; --end of VALIDATE_YN
2980
2981 l_return_status := Validate_Record(l_rulv_rec);
2982 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2983 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2984 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2985 RAISE OKC_API.G_EXCEPTION_ERROR;
2986 END IF;
2987 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2988
2989
2990
2991
2992 EXCEPTION
2993 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2994
2995
2996 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2997 (
2998 l_api_name,
2999 G_PKG_NAME,
3000 'OKC_API.G_RET_STS_ERROR',
3001 x_msg_count,
3002 x_msg_data,
3003 '_PVT'
3004 );
3005 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3006
3007
3008 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3009 (
3010 l_api_name,
3011 G_PKG_NAME,
3012 'OKC_API.G_RET_STS_UNEXP_ERROR',
3013 x_msg_count,
3014 x_msg_data,
3015 '_PVT'
3016 );
3017 WHEN OTHERS THEN
3018
3019
3020 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3021 (
3022 l_api_name,
3023 G_PKG_NAME,
3024 'OTHERS',
3025 x_msg_count,
3026 x_msg_data,
3027 '_PVT'
3028 );
3029 END validate_row;
3030 ------------------------------------------
3031 -- PL/SQL TBL validate_row for:RULV_TBL --
3032 ------------------------------------------
3033 PROCEDURE validate_row(
3034 p_api_version IN NUMBER,
3035 p_init_msg_list IN VARCHAR2,
3036 x_return_status OUT NOCOPY VARCHAR2,
3037 x_msg_count OUT NOCOPY NUMBER,
3038 x_msg_data OUT NOCOPY VARCHAR2,
3039 p_rulv_tbl IN rulv_tbl_type) IS
3040
3041 l_api_version CONSTANT NUMBER := 1;
3042 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
3043 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3044 i NUMBER := 0;
3045 --
3046 l_proc varchar2(72) := g_package||'validate_row';
3047 --
3048 BEGIN
3049
3050
3051
3052
3053 OKC_API.init_msg_list(p_init_msg_list);
3054 -- Make sure PL/SQL table has records in it before passing
3055 IF (p_rulv_tbl.COUNT > 0) THEN
3056 i := p_rulv_tbl.FIRST;
3057 LOOP
3058 validate_row (
3059 p_api_version => p_api_version,
3060 p_init_msg_list => OKC_API.G_FALSE,
3061 x_return_status => x_return_status,
3062 x_msg_count => x_msg_count,
3063 x_msg_data => x_msg_data,
3064 p_rulv_rec => p_rulv_tbl(i));
3065 EXIT WHEN (i = p_rulv_tbl.LAST);
3066 i := p_rulv_tbl.NEXT(i);
3067 END LOOP;
3068 END IF;
3069
3070
3071
3072
3073 EXCEPTION
3074 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3075
3076
3077 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3078 (
3079 l_api_name,
3080 G_PKG_NAME,
3081 'OKC_API.G_RET_STS_ERROR',
3082 x_msg_count,
3083 x_msg_data,
3084 '_PVT'
3085 );
3086 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3087
3088
3089 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3090 (
3091 l_api_name,
3092 G_PKG_NAME,
3093 'OKC_API.G_RET_STS_UNEXP_ERROR',
3094 x_msg_count,
3095 x_msg_data,
3096 '_PVT'
3097 );
3098 WHEN OTHERS THEN
3099
3100
3101 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3102 (
3103 l_api_name,
3104 G_PKG_NAME,
3105 'OTHERS',
3106 x_msg_count,
3107 x_msg_data,
3108 '_PVT'
3109 );
3110 END validate_row;
3111
3112 ---------------------------------------------------------------------------
3113 -- PROCEDURE insert_row
3114 ---------------------------------------------------------------------------
3115 --------------------------------
3116 -- insert_row for:OKC_RULES_B --
3117 --------------------------------
3118 PROCEDURE insert_row(
3119 p_init_msg_list IN VARCHAR2,
3120 x_return_status OUT NOCOPY VARCHAR2,
3121 x_msg_count OUT NOCOPY NUMBER,
3122 x_msg_data OUT NOCOPY VARCHAR2,
3123 p_rul_rec IN rul_rec_type,
3124 x_rul_rec OUT NOCOPY rul_rec_type) IS
3125
3126 l_api_version CONSTANT NUMBER := 1;
3127 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
3128 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3129 l_rul_rec rul_rec_type := p_rul_rec;
3130 l_def_rul_rec rul_rec_type;
3131 --
3132 l_proc varchar2(72) := g_package||'insert_row';
3133 --
3134 ------------------------------------
3135 -- Set_Attributes for:OKC_RULES_B --
3136 ------------------------------------
3137 FUNCTION Set_Attributes (
3138 p_rul_rec IN rul_rec_type,
3139 x_rul_rec OUT NOCOPY rul_rec_type
3140 ) RETURN VARCHAR2 IS
3141 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3142 BEGIN
3143 x_rul_rec := p_rul_rec;
3144 RETURN(l_return_status);
3145 END Set_Attributes;
3146 BEGIN
3147
3148
3149
3150
3151 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3152 p_init_msg_list,
3153 '_PVT',
3154 x_return_status);
3155 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3156 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3157 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3158 RAISE OKC_API.G_EXCEPTION_ERROR;
3159 END IF;
3160 --- Setting item attributes
3161 l_return_status := Set_Attributes(
3162 p_rul_rec, -- IN
3163 l_rul_rec); -- OUT
3164 --- If any errors happen abort API
3165 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3166 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3167 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3168 RAISE OKC_API.G_EXCEPTION_ERROR;
3169 END IF;
3170 INSERT INTO OKC_RULES_B(
3171 id,
3172 rgp_id,
3173 object1_id1,
3174 object2_id1,
3175 object3_id1,
3176 object1_id2,
3177 object2_id2,
3178 object3_id2,
3179 jtot_object1_code,
3180 jtot_object2_code,
3181 jtot_object3_code,
3182 dnz_chr_id,
3183 std_template_yn,
3184 warn_yn,
3185 priority,
3186 object_version_number,
3187 created_by,
3188 creation_date,
3189 last_updated_by,
3190 last_update_date,
3191 last_update_login,
3192 attribute_category,
3193 attribute1,
3194 attribute2,
3195 attribute3,
3196 attribute4,
3197 attribute5,
3198 attribute6,
3199 attribute7,
3200 attribute8,
3201 attribute9,
3202 attribute10,
3203 attribute11,
3204 attribute12,
3205 attribute13,
3206 attribute14,
3207 attribute15,
3208 rule_information_category,
3209 rule_information1,
3210 rule_information2,
3211 rule_information3,
3212 rule_information4,
3213 rule_information5,
3214 rule_information6,
3215 rule_information7,
3216 rule_information8,
3217 rule_information9,
3218 rule_information10,
3219 rule_information11,
3220 rule_information12,
3221 rule_information13,
3222 rule_information14,
3223 rule_information15,
3224 template_yn,
3225 ans_set_jtot_object_code,
3226 ans_set_jtot_object_id1,
3227 ans_set_jtot_object_id2,
3228 display_sequence,
3229 --Bug 3055393
3230 comments)
3231 VALUES (
3232 l_rul_rec.id,
3233 l_rul_rec.rgp_id,
3234 l_rul_rec.object1_id1,
3235 l_rul_rec.object2_id1,
3236 l_rul_rec.object3_id1,
3237 l_rul_rec.object1_id2,
3238 l_rul_rec.object2_id2,
3239 l_rul_rec.object3_id2,
3240 l_rul_rec.jtot_object1_code,
3241 l_rul_rec.jtot_object2_code,
3242 l_rul_rec.jtot_object3_code,
3243 l_rul_rec.dnz_chr_id,
3244 l_rul_rec.std_template_yn,
3245 l_rul_rec.warn_yn,
3246 l_rul_rec.priority,
3247 l_rul_rec.object_version_number,
3248 l_rul_rec.created_by,
3249 l_rul_rec.creation_date,
3250 l_rul_rec.last_updated_by,
3251 l_rul_rec.last_update_date,
3252 l_rul_rec.last_update_login,
3253 l_rul_rec.attribute_category,
3254 l_rul_rec.attribute1,
3255 l_rul_rec.attribute2,
3256 l_rul_rec.attribute3,
3257 l_rul_rec.attribute4,
3258 l_rul_rec.attribute5,
3259 l_rul_rec.attribute6,
3260 l_rul_rec.attribute7,
3261 l_rul_rec.attribute8,
3262 l_rul_rec.attribute9,
3263 l_rul_rec.attribute10,
3264 l_rul_rec.attribute11,
3265 l_rul_rec.attribute12,
3266 l_rul_rec.attribute13,
3267 l_rul_rec.attribute14,
3268 l_rul_rec.attribute15,
3269 l_rul_rec.rule_information_category,
3270 l_rul_rec.rule_information1,
3271 l_rul_rec.rule_information2,
3272 l_rul_rec.rule_information3,
3273 l_rul_rec.rule_information4,
3274 l_rul_rec.rule_information5,
3275 l_rul_rec.rule_information6,
3276 l_rul_rec.rule_information7,
3277 l_rul_rec.rule_information8,
3278 l_rul_rec.rule_information9,
3279 l_rul_rec.rule_information10,
3280 l_rul_rec.rule_information11,
3281 l_rul_rec.rule_information12,
3282 l_rul_rec.rule_information13,
3283 l_rul_rec.rule_information14,
3284 l_rul_rec.rule_information15,
3285 l_rul_rec.template_yn,
3286 l_rul_rec.ans_set_jtot_object_code,
3287 l_rul_rec.ans_set_jtot_object_id1,
3288 l_rul_rec.ans_set_jtot_object_id2,
3289 l_rul_rec.display_sequence,
3290 --Bug 3055393
3291 l_rul_rec.comments);
3292 -- Set OUT values
3293 x_rul_rec := l_rul_rec;
3294 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3295
3296
3297
3298
3299 EXCEPTION
3300 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3301
3302
3303 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3304 (
3305 l_api_name,
3306 G_PKG_NAME,
3307 'OKC_API.G_RET_STS_ERROR',
3308 x_msg_count,
3309 x_msg_data,
3310 '_PVT'
3311 );
3312 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3313
3314
3315 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3316 (
3317 l_api_name,
3318 G_PKG_NAME,
3319 'OKC_API.G_RET_STS_UNEXP_ERROR',
3320 x_msg_count,
3321 x_msg_data,
3322 '_PVT'
3323 );
3324 WHEN OTHERS THEN
3325
3326
3327 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3328 (
3329 l_api_name,
3330 G_PKG_NAME,
3331 'OTHERS',
3332 x_msg_count,
3333 x_msg_data,
3334 '_PVT'
3335 );
3336 END insert_row;
3337 ---------------------------------
3338 -- insert_row for:OKC_RULES_TL --
3339 ---------------------------------
3340 /*--Bug 3055393
3341 PROCEDURE insert_row(
3342 p_init_msg_list IN VARCHAR2,
3343 x_return_status OUT NOCOPY VARCHAR2,
3344 x_msg_count OUT NOCOPY NUMBER,
3345 x_msg_data OUT NOCOPY VARCHAR2,
3346 p_okc_rules_tl_rec IN okc_rules_tl_rec_type,
3347 x_okc_rules_tl_rec OUT NOCOPY okc_rules_tl_rec_type) IS
3348
3349 l_api_version CONSTANT NUMBER := 1;
3350 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
3351 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3352 l_okc_rules_tl_rec okc_rules_tl_rec_type := p_okc_rules_tl_rec;
3353 l_def_okc_rules_tl_rec okc_rules_tl_rec_type;
3354 CURSOR get_languages IS
3355 SELECT *
3356 FROM FND_LANGUAGES
3357 WHERE INSTALLED_FLAG IN ('I', 'B');
3358 --
3359 l_proc varchar2(72) := g_package||'insert_row';
3360 --
3361 -------------------------------------
3362 -- Set_Attributes for:OKC_RULES_TL --
3363 -------------------------------------
3364 FUNCTION Set_Attributes (
3365 p_okc_rules_tl_rec IN okc_rules_tl_rec_type,
3366 x_okc_rules_tl_rec OUT NOCOPY okc_rules_tl_rec_type
3367 ) RETURN VARCHAR2 IS
3368 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3369 BEGIN
3370 x_okc_rules_tl_rec := p_okc_rules_tl_rec;
3371 x_okc_rules_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
3372 x_okc_rules_tl_rec.SOURCE_LANG := okc_util.get_userenv_lang;
3373 RETURN(l_return_status);
3374 END Set_Attributes;
3375 BEGIN
3376
3377
3378
3379
3380 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3381 p_init_msg_list,
3382 '_PVT',
3383 x_return_status);
3384 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3385 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3386 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3387 RAISE OKC_API.G_EXCEPTION_ERROR;
3388 END IF;
3389 --- Setting item attributes
3390 l_return_status := Set_Attributes(
3391 p_okc_rules_tl_rec, -- IN
3392 l_okc_rules_tl_rec); -- OUT
3393 --- If any errors happen abort API
3394 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3395 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3396 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3397 RAISE OKC_API.G_EXCEPTION_ERROR;
3398 END IF;
3399 FOR l_lang_rec IN get_languages LOOP
3400 l_okc_rules_tl_rec.language := l_lang_rec.language_code;
3401 INSERT INTO OKC_RULES_TL(
3402 id,
3403 language,
3404 source_lang,
3405 sfwt_flag,
3406 comments,
3407 text,
3408 created_by,
3409 creation_date,
3410 last_updated_by,
3411 last_update_date,
3412 last_update_login)
3413 VALUES (
3414 l_okc_rules_tl_rec.id,
3415 l_okc_rules_tl_rec.language,
3416 l_okc_rules_tl_rec.source_lang,
3417 l_okc_rules_tl_rec.sfwt_flag,
3418 l_okc_rules_tl_rec.comments,
3419 l_okc_rules_tl_rec.text,
3420 l_okc_rules_tl_rec.created_by,
3421 l_okc_rules_tl_rec.creation_date,
3422 l_okc_rules_tl_rec.last_updated_by,
3423 l_okc_rules_tl_rec.last_update_date,
3424 l_okc_rules_tl_rec.last_update_login);
3425 END LOOP;
3426 -- Set OUT values
3427 x_okc_rules_tl_rec := l_okc_rules_tl_rec;
3428 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3429
3430
3431
3432
3433 EXCEPTION
3434 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3435
3436
3437 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3438 (
3439 l_api_name,
3440 G_PKG_NAME,
3441 'OKC_API.G_RET_STS_ERROR',
3442 x_msg_count,
3443 x_msg_data,
3444 '_PVT'
3445 );
3446 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3447
3448
3449 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3450 (
3451 l_api_name,
3452 G_PKG_NAME,
3453 'OKC_API.G_RET_STS_UNEXP_ERROR',
3454 x_msg_count,
3455 x_msg_data,
3456 '_PVT'
3457 );
3458 WHEN OTHERS THEN
3459
3460
3461 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3462 (
3463 l_api_name,
3464 G_PKG_NAME,
3465 'OTHERS',
3466 x_msg_count,
3467 x_msg_data,
3468 '_PVT'
3469 );
3470 END insert_row;
3471 */
3472 --------------------------------
3473 -- insert_row for:OKC_RULES_V --
3474 --------------------------------
3475 PROCEDURE insert_row(
3476 p_api_version IN NUMBER,
3477 p_init_msg_list IN VARCHAR2,
3478 x_return_status OUT NOCOPY VARCHAR2,
3479 x_msg_count OUT NOCOPY NUMBER,
3480 x_msg_data OUT NOCOPY VARCHAR2,
3481 p_rulv_rec IN rulv_rec_type,
3482 x_rulv_rec OUT NOCOPY rulv_rec_type) IS
3483
3484 l_api_version CONSTANT NUMBER := 1;
3485 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
3486 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3487 l_rulv_rec rulv_rec_type;
3488 l_def_rulv_rec rulv_rec_type;
3489 l_rul_rec rul_rec_type;
3490 lx_rul_rec rul_rec_type;
3491 --Bug 3055393 l_okc_rules_tl_rec okc_rules_tl_rec_type;
3492 --Bug 3055393 lx_okc_rules_tl_rec okc_rules_tl_rec_type;
3493 --
3494 l_proc varchar2(72) := g_package||'insert_row';
3495 --
3496 -------------------------------
3497 -- FUNCTION fill_who_columns --
3498 -------------------------------
3499 FUNCTION fill_who_columns (
3500 p_rulv_rec IN rulv_rec_type
3501 ) RETURN rulv_rec_type IS
3502 l_rulv_rec rulv_rec_type := p_rulv_rec;
3503 BEGIN
3504 l_rulv_rec.CREATION_DATE := SYSDATE;
3505 l_rulv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
3506 l_rulv_rec.LAST_UPDATE_DATE := l_rulv_rec.creation_date;
3507 l_rulv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
3508 l_rulv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
3509 RETURN(l_rulv_rec);
3510 END fill_who_columns;
3511 ------------------------------------
3512 -- Set_Attributes for:OKC_RULES_V --
3513 ------------------------------------
3514 FUNCTION Set_Attributes (
3515 p_rulv_rec IN rulv_rec_type,
3516 x_rulv_rec OUT NOCOPY rulv_rec_type
3517 ) RETURN VARCHAR2 IS
3518 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3519 BEGIN
3520 x_rulv_rec := p_rulv_rec;
3521 x_rulv_rec.OBJECT_VERSION_NUMBER := 1;
3522 --Bug 3055393 x_rulv_rec.SFWT_FLAG := 'N';
3523 /************************ HAND-CODED *********************************/
3524 x_rulv_rec.STD_TEMPLATE_YN := UPPER(x_rulv_rec.STD_TEMPLATE_YN);
3525 x_rulv_rec.WARN_YN := UPPER(x_rulv_rec.WARN_YN);
3526 /*********************** END HAND-CODED ********************************/
3527 RETURN(l_return_status);
3528 END Set_Attributes;
3529 BEGIN
3530
3531
3532
3533
3534 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3535 G_PKG_NAME,
3536 p_init_msg_list,
3537 l_api_version,
3538 p_api_version,
3539 '_PVT',
3540 x_return_status);
3541 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3542 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3543 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3544 RAISE OKC_API.G_EXCEPTION_ERROR;
3545 END IF;
3546 l_rulv_rec := null_out_defaults(p_rulv_rec);
3547 -- Set primary key value
3548 l_rulv_rec.ID := get_seq_id;
3549 --- Setting item attributes
3550 l_return_status := Set_Attributes(
3551 l_rulv_rec, -- IN
3552 l_def_rulv_rec); -- OUT
3553 --- If any errors happen abort API
3554 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3555 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3556 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3557 RAISE OKC_API.G_EXCEPTION_ERROR;
3558 END IF;
3559 l_def_rulv_rec := fill_who_columns(l_def_rulv_rec);
3560
3561 IF p_rulv_rec.VALIDATE_YN = 'Y' THEN
3562 --- Validate all non-missing attributes (Item Level Validation)
3563 l_return_status := Validate_Attributes(l_def_rulv_rec);
3564 --- If any errors happen abort API
3565 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3566 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3567 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3568 RAISE OKC_API.G_EXCEPTION_ERROR;
3569 END IF;
3570 END IF; --end of VALIDATE_YN
3571
3572 l_return_status := Validate_Record(l_def_rulv_rec);
3573 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3574 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3575 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3576 RAISE OKC_API.G_EXCEPTION_ERROR;
3577 END IF;
3578 --+added override comments
3579 l_def_rulv_rec.comments := set_comments(l_def_rulv_rec);
3580 --+
3581 --------------------------------------
3582 -- Move VIEW record to "Child" records
3583 --------------------------------------
3584 migrate(l_def_rulv_rec, l_rul_rec);
3585 --Bug 3055393 migrate(l_def_rulv_rec, l_okc_rules_tl_rec);
3586 --------------------------------------------
3587 -- Call the INSERT_ROW for each child record
3588 --------------------------------------------
3589 insert_row(
3590 p_init_msg_list,
3591 x_return_status,
3592 x_msg_count,
3593 x_msg_data,
3594 l_rul_rec,
3595 lx_rul_rec
3596 );
3597 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3598 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3599 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3600 RAISE OKC_API.G_EXCEPTION_ERROR;
3601 END IF;
3602 migrate(lx_rul_rec, l_def_rulv_rec);
3603 /*--Bug 3055393
3604 insert_row(
3605 p_init_msg_list,
3606 x_return_status,
3607 x_msg_count,
3608 x_msg_data,
3609 l_okc_rules_tl_rec,
3610 lx_okc_rules_tl_rec
3611 );
3612 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3613 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3614 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3615 RAISE OKC_API.G_EXCEPTION_ERROR;
3616 END IF;
3617 migrate(lx_okc_rules_tl_rec, l_def_rulv_rec);
3618 */
3619 -- Set OUT values
3620 x_rulv_rec := l_def_rulv_rec;
3621 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3622
3623
3624
3625
3626 EXCEPTION
3627 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3628
3629
3630 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3631 (
3632 l_api_name,
3633 G_PKG_NAME,
3634 'OKC_API.G_RET_STS_ERROR',
3635 x_msg_count,
3636 x_msg_data,
3637 '_PVT'
3638 );
3639 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3640
3641
3642 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3643 (
3644 l_api_name,
3645 G_PKG_NAME,
3646 'OKC_API.G_RET_STS_UNEXP_ERROR',
3647 x_msg_count,
3648 x_msg_data,
3649 '_PVT'
3650 );
3651 WHEN OTHERS THEN
3652
3653
3654 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3655 (
3656 l_api_name,
3657 G_PKG_NAME,
3658 'OTHERS',
3659 x_msg_count,
3660 x_msg_data,
3661 '_PVT'
3662 );
3663 END insert_row;
3664 ----------------------------------------
3665 -- PL/SQL TBL insert_row for:RULV_TBL --
3666 ----------------------------------------
3667 PROCEDURE insert_row(
3668 p_api_version IN NUMBER,
3669 p_init_msg_list IN VARCHAR2,
3670 x_return_status OUT NOCOPY VARCHAR2,
3671 x_msg_count OUT NOCOPY NUMBER,
3672 x_msg_data OUT NOCOPY VARCHAR2,
3673 p_rulv_tbl IN rulv_tbl_type,
3674 x_rulv_tbl OUT NOCOPY rulv_tbl_type) IS
3675
3676 l_api_version CONSTANT NUMBER := 1;
3677 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
3678 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3679 i NUMBER := 0;
3680 --
3681 l_proc varchar2(72) := g_package||'insert_row';
3682 --
3683 BEGIN
3684
3685
3686
3687
3688 OKC_API.init_msg_list(p_init_msg_list);
3689 -- Make sure PL/SQL table has records in it before passing
3690 IF (p_rulv_tbl.COUNT > 0) THEN
3691 i := p_rulv_tbl.FIRST;
3692 LOOP
3693 insert_row (
3694 p_api_version => p_api_version,
3695 p_init_msg_list => OKC_API.G_FALSE,
3696 x_return_status => x_return_status,
3697 x_msg_count => x_msg_count,
3698 x_msg_data => x_msg_data,
3699 p_rulv_rec => p_rulv_tbl(i),
3700 x_rulv_rec => x_rulv_tbl(i));
3701 EXIT WHEN (i = p_rulv_tbl.LAST);
3702 i := p_rulv_tbl.NEXT(i);
3703 END LOOP;
3704 END IF;
3705
3706
3707
3708
3709 EXCEPTION
3710 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3711
3712
3713 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3714 (
3715 l_api_name,
3716 G_PKG_NAME,
3717 'OKC_API.G_RET_STS_ERROR',
3718 x_msg_count,
3719 x_msg_data,
3720 '_PVT'
3721 );
3722 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3723
3724
3725 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3726 (
3727 l_api_name,
3728 G_PKG_NAME,
3729 'OKC_API.G_RET_STS_UNEXP_ERROR',
3730 x_msg_count,
3731 x_msg_data,
3732 '_PVT'
3733 );
3734 WHEN OTHERS THEN
3735
3736
3737 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3738 (
3739 l_api_name,
3740 G_PKG_NAME,
3741 'OTHERS',
3742 x_msg_count,
3743 x_msg_data,
3744 '_PVT'
3745 );
3746 END insert_row;
3747
3748 ---------------------------------------------------------------------------
3749 -- PROCEDURE lock_row
3750 ---------------------------------------------------------------------------
3751 ------------------------------
3752 -- lock_row for:OKC_RULES_B --
3753 ------------------------------
3754 PROCEDURE lock_row(
3755 p_init_msg_list IN VARCHAR2,
3756 x_return_status OUT NOCOPY VARCHAR2,
3757 x_msg_count OUT NOCOPY NUMBER,
3758 x_msg_data OUT NOCOPY VARCHAR2,
3759 p_rul_rec IN rul_rec_type) IS
3760
3761 E_Resource_Busy EXCEPTION;
3762 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
3763 CURSOR lock_csr (p_rul_rec IN rul_rec_type) IS
3764 SELECT OBJECT_VERSION_NUMBER
3765 FROM OKC_RULES_B
3766 WHERE ID = p_rul_rec.id
3767 AND OBJECT_VERSION_NUMBER = p_rul_rec.object_version_number
3768 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
3769
3770 CURSOR lchk_csr (p_rul_rec IN rul_rec_type) IS
3771 SELECT OBJECT_VERSION_NUMBER
3772 FROM OKC_RULES_B
3773 WHERE ID = p_rul_rec.id;
3774 l_api_version CONSTANT NUMBER := 1;
3775 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
3776 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3777 l_object_version_number OKC_RULES_B.OBJECT_VERSION_NUMBER%TYPE;
3778 lc_object_version_number OKC_RULES_B.OBJECT_VERSION_NUMBER%TYPE;
3779 l_row_notfound BOOLEAN := FALSE;
3780 lc_row_notfound BOOLEAN := FALSE;
3781 --
3782 l_proc varchar2(72) := g_package||'insert_row';
3783 --
3784 BEGIN
3785
3786
3787
3788
3789 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3790 p_init_msg_list,
3791 '_PVT',
3792 x_return_status);
3793 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3794 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3795 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3796 RAISE OKC_API.G_EXCEPTION_ERROR;
3797 END IF;
3798 BEGIN
3799 OPEN lock_csr(p_rul_rec);
3800 FETCH lock_csr INTO l_object_version_number;
3801 l_row_notfound := lock_csr%NOTFOUND;
3802 CLOSE lock_csr;
3803 EXCEPTION
3804 WHEN E_Resource_Busy THEN
3805 IF (lock_csr%ISOPEN) THEN
3806 CLOSE lock_csr;
3807 END IF;
3808 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
3809 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
3810 END;
3811
3812 IF ( l_row_notfound ) THEN
3813 OPEN lchk_csr(p_rul_rec);
3814 FETCH lchk_csr INTO lc_object_version_number;
3815 lc_row_notfound := lchk_csr%NOTFOUND;
3816 CLOSE lchk_csr;
3817 END IF;
3818 IF (lc_row_notfound) THEN
3819 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
3820 RAISE OKC_API.G_EXCEPTION_ERROR;
3821 ELSIF lc_object_version_number > p_rul_rec.object_version_number THEN
3822 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
3823 RAISE OKC_API.G_EXCEPTION_ERROR;
3824 ELSIF lc_object_version_number <> p_rul_rec.object_version_number THEN
3825 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
3826 RAISE OKC_API.G_EXCEPTION_ERROR;
3827 ELSIF lc_object_version_number = -1 THEN
3828 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
3829 RAISE OKC_API.G_EXCEPTION_ERROR;
3830 END IF;
3831 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3832
3833
3834
3835
3836 EXCEPTION
3837 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3838
3839
3840 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3841 (
3842 l_api_name,
3843 G_PKG_NAME,
3844 'OKC_API.G_RET_STS_ERROR',
3845 x_msg_count,
3846 x_msg_data,
3847 '_PVT'
3848 );
3849 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3850
3851
3852 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3853 (
3854 l_api_name,
3855 G_PKG_NAME,
3856 'OKC_API.G_RET_STS_UNEXP_ERROR',
3857 x_msg_count,
3858 x_msg_data,
3859 '_PVT'
3860 );
3861 WHEN OTHERS THEN
3862
3863
3864 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3865 (
3866 l_api_name,
3867 G_PKG_NAME,
3868 'OTHERS',
3869 x_msg_count,
3870 x_msg_data,
3871 '_PVT'
3872 );
3873 END lock_row;
3874 -------------------------------
3875 -- lock_row for:OKC_RULES_TL --
3876 -------------------------------
3877 /*--Bug 3055393
3878 PROCEDURE lock_row(
3879 p_init_msg_list IN VARCHAR2,
3880 x_return_status OUT NOCOPY VARCHAR2,
3881 x_msg_count OUT NOCOPY NUMBER,
3882 x_msg_data OUT NOCOPY VARCHAR2,
3883 p_okc_rules_tl_rec IN okc_rules_tl_rec_type) IS
3884
3885 E_Resource_Busy EXCEPTION;
3886 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
3887 CURSOR lock_csr (p_okc_rules_tl_rec IN okc_rules_tl_rec_type) IS
3888 SELECT *
3889 FROM OKC_RULES_TL
3890 WHERE ID = p_okc_rules_tl_rec.id
3891 FOR UPDATE NOWAIT;
3892
3893 l_api_version CONSTANT NUMBER := 1;
3894 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
3895 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3896 l_lock_var lock_csr%ROWTYPE;
3897 l_row_notfound BOOLEAN := FALSE;
3898 lc_row_notfound BOOLEAN := FALSE;
3899 --
3900 l_proc varchar2(72) := g_package||'insert_row';
3901 --
3902 BEGIN
3903
3904
3905
3906
3907 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3908 p_init_msg_list,
3909 '_PVT',
3910 x_return_status);
3911 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3912 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3913 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3914 RAISE OKC_API.G_EXCEPTION_ERROR;
3915 END IF;
3916 BEGIN
3917 OPEN lock_csr(p_okc_rules_tl_rec);
3918 FETCH lock_csr INTO l_lock_var;
3919 l_row_notfound := lock_csr%NOTFOUND;
3920 CLOSE lock_csr;
3921 EXCEPTION
3922 WHEN E_Resource_Busy THEN
3923 IF (lock_csr%ISOPEN) THEN
3924 CLOSE lock_csr;
3925 END IF;
3926 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
3927 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
3928 END;
3929
3930 IF ( l_row_notfound ) THEN
3931 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
3932 RAISE OKC_API.G_EXCEPTION_ERROR;
3933 END IF;
3934 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3935
3936
3937
3938
3939 EXCEPTION
3940 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3941
3942
3943 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3944 (
3945 l_api_name,
3946 G_PKG_NAME,
3947 'OKC_API.G_RET_STS_ERROR',
3948 x_msg_count,
3949 x_msg_data,
3950 '_PVT'
3951 );
3952 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3953
3954
3955 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3956 (
3957 l_api_name,
3958 G_PKG_NAME,
3959 'OKC_API.G_RET_STS_UNEXP_ERROR',
3960 x_msg_count,
3961 x_msg_data,
3962 '_PVT'
3963 );
3964 WHEN OTHERS THEN
3965
3966
3967 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3968 (
3969 l_api_name,
3970 G_PKG_NAME,
3971 'OTHERS',
3972 x_msg_count,
3973 x_msg_data,
3974 '_PVT'
3975 );
3976 END lock_row;
3977 */
3978 ------------------------------
3979 -- lock_row for:OKC_RULES_V --
3980 ------------------------------
3981 PROCEDURE lock_row(
3982 p_api_version IN NUMBER,
3983 p_init_msg_list IN VARCHAR2,
3984 x_return_status OUT NOCOPY VARCHAR2,
3985 x_msg_count OUT NOCOPY NUMBER,
3986 x_msg_data OUT NOCOPY VARCHAR2,
3987 p_rulv_rec IN rulv_rec_type) IS
3988
3989 l_api_version CONSTANT NUMBER := 1;
3990 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
3991 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3992 l_rul_rec rul_rec_type;
3993 --Bug 3055393 l_okc_rules_tl_rec okc_rules_tl_rec_type;
3994 --
3995 l_proc varchar2(72) := g_package||'insert_row';
3996 --
3997 BEGIN
3998
3999
4000
4001
4002 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4003 G_PKG_NAME,
4004 p_init_msg_list,
4005 l_api_version,
4006 p_api_version,
4007 '_PVT',
4008 x_return_status);
4009 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4010 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4011 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4012 RAISE OKC_API.G_EXCEPTION_ERROR;
4013 END IF;
4014 --------------------------------------
4015 -- Move VIEW record to "Child" records
4016 --------------------------------------
4017 migrate(p_rulv_rec, l_rul_rec);
4018 --Bug 3055393 migrate(p_rulv_rec, l_okc_rules_tl_rec);
4019 --------------------------------------------
4020 -- Call the LOCK_ROW for each child record
4021 --------------------------------------------
4022 lock_row(
4023 p_init_msg_list,
4024 x_return_status,
4025 x_msg_count,
4026 x_msg_data,
4027 l_rul_rec
4028 );
4029 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4030 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4031 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4032 RAISE OKC_API.G_EXCEPTION_ERROR;
4033 END IF;
4034 /*
4035 lock_row(
4036 p_init_msg_list,
4037 x_return_status,
4038 x_msg_count,
4039 x_msg_data,
4040 l_okc_rules_tl_rec
4041 );
4042 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4043 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4044 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
4045 RAISE OKC_API.G_EXCEPTION_ERROR;
4046 END IF;
4047 */
4048 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4049
4050
4051
4052
4053 EXCEPTION
4054 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4055
4056
4057 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4058 (
4059 l_api_name,
4060 G_PKG_NAME,
4061 'OKC_API.G_RET_STS_ERROR',
4062 x_msg_count,
4063 x_msg_data,
4064 '_PVT'
4065 );
4066 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4067
4068
4069 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4070 (
4071 l_api_name,
4072 G_PKG_NAME,
4073 'OKC_API.G_RET_STS_UNEXP_ERROR',
4074 x_msg_count,
4075 x_msg_data,
4076 '_PVT'
4077 );
4078 WHEN OTHERS THEN
4079
4080
4081 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4082 (
4083 l_api_name,
4084 G_PKG_NAME,
4085 'OTHERS',
4086 x_msg_count,
4087 x_msg_data,
4088 '_PVT'
4089 );
4090 END lock_row;
4091 --------------------------------------
4092 -- PL/SQL TBL lock_row for:RULV_TBL --
4093 --------------------------------------
4094 PROCEDURE lock_row(
4095 p_api_version IN NUMBER,
4096 p_init_msg_list IN VARCHAR2,
4097 x_return_status OUT NOCOPY VARCHAR2,
4098 x_msg_count OUT NOCOPY NUMBER,
4099 x_msg_data OUT NOCOPY VARCHAR2,
4100 p_rulv_tbl IN rulv_tbl_type) IS
4101
4102 l_api_version CONSTANT NUMBER := 1;
4103 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
4104 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4105 i NUMBER := 0;
4106 --
4107 l_proc varchar2(72) := g_package||'insert_row';
4108 --
4109 BEGIN
4110
4111
4112
4113
4114 OKC_API.init_msg_list(p_init_msg_list);
4115 -- Make sure PL/SQL table has records in it before passing
4116 IF (p_rulv_tbl.COUNT > 0) THEN
4117 i := p_rulv_tbl.FIRST;
4118 LOOP
4119 lock_row (
4120 p_api_version => p_api_version,
4121 p_init_msg_list => OKC_API.G_FALSE,
4122 x_return_status => x_return_status,
4123 x_msg_count => x_msg_count,
4124 x_msg_data => x_msg_data,
4125 p_rulv_rec => p_rulv_tbl(i));
4126 EXIT WHEN (i = p_rulv_tbl.LAST);
4127 i := p_rulv_tbl.NEXT(i);
4128 END LOOP;
4129 END IF;
4130
4131
4132
4133
4134 EXCEPTION
4135 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4136
4137
4138 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4139 (
4140 l_api_name,
4141 G_PKG_NAME,
4142 'OKC_API.G_RET_STS_ERROR',
4143 x_msg_count,
4144 x_msg_data,
4145 '_PVT'
4146 );
4147 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4148
4149
4150 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4151 (
4152 l_api_name,
4153 G_PKG_NAME,
4154 'OKC_API.G_RET_STS_UNEXP_ERROR',
4155 x_msg_count,
4156 x_msg_data,
4157 '_PVT'
4158 );
4159 WHEN OTHERS THEN
4160
4161
4162 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4163 (
4164 l_api_name,
4165 G_PKG_NAME,
4166 'OTHERS',
4167 x_msg_count,
4168 x_msg_data,
4169 '_PVT'
4170 );
4171 END lock_row;
4172
4173 ---------------------------------------------------------------------------
4174 -- PROCEDURE update_row
4175 ---------------------------------------------------------------------------
4176 --------------------------------
4177 -- update_row for:OKC_RULES_B --
4178 --------------------------------
4179 PROCEDURE update_row(
4180 p_init_msg_list IN VARCHAR2,
4181 x_return_status OUT NOCOPY VARCHAR2,
4182 x_msg_count OUT NOCOPY NUMBER,
4183 x_msg_data OUT NOCOPY VARCHAR2,
4184 p_rul_rec IN rul_rec_type,
4185 x_rul_rec OUT NOCOPY rul_rec_type) IS
4186
4187 l_api_version CONSTANT NUMBER := 1;
4188 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
4189 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4190 l_rul_rec rul_rec_type := p_rul_rec;
4191 l_def_rul_rec rul_rec_type;
4192 l_row_notfound BOOLEAN := TRUE;
4193 --
4194 l_proc varchar2(72) := g_package||'update_row';
4195 --
4196 ----------------------------------
4197 -- FUNCTION populate_new_record --
4198 ----------------------------------
4199 FUNCTION populate_new_record (
4200 p_rul_rec IN rul_rec_type,
4201 x_rul_rec OUT NOCOPY rul_rec_type
4202 ) RETURN VARCHAR2 IS
4203 l_rul_rec rul_rec_type;
4204 l_row_notfound BOOLEAN := TRUE;
4205 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4206 --
4207 l_proc varchar2(72) := g_package||'populate_new_record';
4208 --
4209 BEGIN
4210
4211
4212
4213
4214 x_rul_rec := p_rul_rec;
4215 -- Get current database values
4216 l_rul_rec := get_rec(p_rul_rec, l_row_notfound);
4217 IF (l_row_notfound) THEN
4218 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4219 END IF;
4220 IF (x_rul_rec.id = OKC_API.G_MISS_NUM)
4221 THEN
4222 x_rul_rec.id := l_rul_rec.id;
4223 END IF;
4224 IF (x_rul_rec.rgp_id = OKC_API.G_MISS_NUM)
4225 THEN
4226 x_rul_rec.rgp_id := l_rul_rec.rgp_id;
4227 END IF;
4228 IF (x_rul_rec.object1_id1 = OKC_API.G_MISS_CHAR)
4229 THEN
4230 x_rul_rec.object1_id1 := l_rul_rec.object1_id1;
4231 END IF;
4232 IF (x_rul_rec.object2_id1 = OKC_API.G_MISS_CHAR)
4233 THEN
4234 x_rul_rec.object2_id1 := l_rul_rec.object2_id1;
4235 END IF;
4236 IF (x_rul_rec.object3_id1 = OKC_API.G_MISS_CHAR)
4237 THEN
4238 x_rul_rec.object3_id1 := l_rul_rec.object3_id1;
4239 END IF;
4240 IF (x_rul_rec.object1_id2 = OKC_API.G_MISS_CHAR)
4241 THEN
4242 x_rul_rec.object1_id2 := l_rul_rec.object1_id2;
4243 END IF;
4244 IF (x_rul_rec.object2_id2 = OKC_API.G_MISS_CHAR)
4245 THEN
4246 x_rul_rec.object2_id2 := l_rul_rec.object2_id2;
4247 END IF;
4248 IF (x_rul_rec.object3_id2 = OKC_API.G_MISS_CHAR)
4249 THEN
4250 x_rul_rec.object3_id2 := l_rul_rec.object3_id2;
4251 END IF;
4252 IF (x_rul_rec.jtot_object1_code = OKC_API.G_MISS_CHAR)
4253 THEN
4254 x_rul_rec.jtot_object1_code := l_rul_rec.jtot_object1_code;
4255 END IF;
4256 IF (x_rul_rec.jtot_object2_code = OKC_API.G_MISS_CHAR)
4257 THEN
4258 x_rul_rec.jtot_object2_code := l_rul_rec.jtot_object2_code;
4259 END IF;
4260 IF (x_rul_rec.jtot_object3_code = OKC_API.G_MISS_CHAR)
4261 THEN
4262 x_rul_rec.jtot_object3_code := l_rul_rec.jtot_object3_code;
4263 END IF;
4264 IF (x_rul_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
4265 THEN
4266 x_rul_rec.dnz_chr_id := l_rul_rec.dnz_chr_id;
4267 END IF;
4268 IF (x_rul_rec.std_template_yn = OKC_API.G_MISS_CHAR)
4269 THEN
4270 x_rul_rec.std_template_yn := l_rul_rec.std_template_yn;
4271 END IF;
4272 IF (x_rul_rec.warn_yn = OKC_API.G_MISS_CHAR)
4273 THEN
4274 x_rul_rec.warn_yn := l_rul_rec.warn_yn;
4275 END IF;
4276 IF (x_rul_rec.priority = OKC_API.G_MISS_NUM)
4277 THEN
4278 x_rul_rec.priority := l_rul_rec.priority;
4279 END IF;
4280 IF (x_rul_rec.object_version_number = OKC_API.G_MISS_NUM)
4281 THEN
4282 x_rul_rec.object_version_number := l_rul_rec.object_version_number;
4283 END IF;
4284 IF (x_rul_rec.created_by = OKC_API.G_MISS_NUM)
4285 THEN
4286 x_rul_rec.created_by := l_rul_rec.created_by;
4287 END IF;
4288 IF (x_rul_rec.creation_date = OKC_API.G_MISS_DATE)
4289 THEN
4290 x_rul_rec.creation_date := l_rul_rec.creation_date;
4291 END IF;
4292 IF (x_rul_rec.last_updated_by = OKC_API.G_MISS_NUM)
4293 THEN
4294 x_rul_rec.last_updated_by := l_rul_rec.last_updated_by;
4295 END IF;
4296 IF (x_rul_rec.last_update_date = OKC_API.G_MISS_DATE)
4297 THEN
4298 x_rul_rec.last_update_date := l_rul_rec.last_update_date;
4299 END IF;
4300 IF (x_rul_rec.last_update_login = OKC_API.G_MISS_NUM)
4301 THEN
4302 x_rul_rec.last_update_login := l_rul_rec.last_update_login;
4303 END IF;
4304 IF (x_rul_rec.attribute_category = OKC_API.G_MISS_CHAR)
4305 THEN
4306 x_rul_rec.attribute_category := l_rul_rec.attribute_category;
4307 END IF;
4308 IF (x_rul_rec.attribute1 = OKC_API.G_MISS_CHAR)
4309 THEN
4310 x_rul_rec.attribute1 := l_rul_rec.attribute1;
4311 END IF;
4312 IF (x_rul_rec.attribute2 = OKC_API.G_MISS_CHAR)
4313 THEN
4314 x_rul_rec.attribute2 := l_rul_rec.attribute2;
4315 END IF;
4316 IF (x_rul_rec.attribute3 = OKC_API.G_MISS_CHAR)
4317 THEN
4318 x_rul_rec.attribute3 := l_rul_rec.attribute3;
4319 END IF;
4320 IF (x_rul_rec.attribute4 = OKC_API.G_MISS_CHAR)
4321 THEN
4322 x_rul_rec.attribute4 := l_rul_rec.attribute4;
4323 END IF;
4324 IF (x_rul_rec.attribute5 = OKC_API.G_MISS_CHAR)
4325 THEN
4326 x_rul_rec.attribute5 := l_rul_rec.attribute5;
4327 END IF;
4328 IF (x_rul_rec.attribute6 = OKC_API.G_MISS_CHAR)
4329 THEN
4330 x_rul_rec.attribute6 := l_rul_rec.attribute6;
4331 END IF;
4332 IF (x_rul_rec.attribute7 = OKC_API.G_MISS_CHAR)
4333 THEN
4334 x_rul_rec.attribute7 := l_rul_rec.attribute7;
4335 END IF;
4336 IF (x_rul_rec.attribute8 = OKC_API.G_MISS_CHAR)
4337 THEN
4338 x_rul_rec.attribute8 := l_rul_rec.attribute8;
4339 END IF;
4340 IF (x_rul_rec.attribute9 = OKC_API.G_MISS_CHAR)
4341 THEN
4342 x_rul_rec.attribute9 := l_rul_rec.attribute9;
4343 END IF;
4344 IF (x_rul_rec.attribute10 = OKC_API.G_MISS_CHAR)
4345 THEN
4346 x_rul_rec.attribute10 := l_rul_rec.attribute10;
4347 END IF;
4348 IF (x_rul_rec.attribute11 = OKC_API.G_MISS_CHAR)
4349 THEN
4350 x_rul_rec.attribute11 := l_rul_rec.attribute11;
4351 END IF;
4352 IF (x_rul_rec.attribute12 = OKC_API.G_MISS_CHAR)
4353 THEN
4354 x_rul_rec.attribute12 := l_rul_rec.attribute12;
4355 END IF;
4356 IF (x_rul_rec.attribute13 = OKC_API.G_MISS_CHAR)
4357 THEN
4358 x_rul_rec.attribute13 := l_rul_rec.attribute13;
4359 END IF;
4360 IF (x_rul_rec.attribute14 = OKC_API.G_MISS_CHAR)
4361 THEN
4362 x_rul_rec.attribute14 := l_rul_rec.attribute14;
4363 END IF;
4364 IF (x_rul_rec.attribute15 = OKC_API.G_MISS_CHAR)
4365 THEN
4366 x_rul_rec.attribute15 := l_rul_rec.attribute15;
4367 END IF;
4368 IF (x_rul_rec.rule_information_category = OKC_API.G_MISS_CHAR)
4369 THEN
4370 x_rul_rec.rule_information_category := l_rul_rec.rule_information_category;
4371 END IF;
4372 IF (x_rul_rec.rule_information1 = OKC_API.G_MISS_CHAR)
4373 THEN
4374 x_rul_rec.rule_information1 := l_rul_rec.rule_information1;
4375 END IF;
4376 IF (x_rul_rec.rule_information2 = OKC_API.G_MISS_CHAR)
4377 THEN
4378 x_rul_rec.rule_information2 := l_rul_rec.rule_information2;
4379 END IF;
4380 IF (x_rul_rec.rule_information3 = OKC_API.G_MISS_CHAR)
4381 THEN
4382 x_rul_rec.rule_information3 := l_rul_rec.rule_information3;
4383 END IF;
4384 IF (x_rul_rec.rule_information4 = OKC_API.G_MISS_CHAR)
4385 THEN
4386 x_rul_rec.rule_information4 := l_rul_rec.rule_information4;
4387 END IF;
4388 IF (x_rul_rec.rule_information5 = OKC_API.G_MISS_CHAR)
4389 THEN
4390 x_rul_rec.rule_information5 := l_rul_rec.rule_information5;
4391 END IF;
4392 IF (x_rul_rec.rule_information6 = OKC_API.G_MISS_CHAR)
4393 THEN
4394 x_rul_rec.rule_information6 := l_rul_rec.rule_information6;
4395 END IF;
4396 IF (x_rul_rec.rule_information7 = OKC_API.G_MISS_CHAR)
4397 THEN
4398 x_rul_rec.rule_information7 := l_rul_rec.rule_information7;
4399 END IF;
4400 IF (x_rul_rec.rule_information8 = OKC_API.G_MISS_CHAR)
4401 THEN
4402 x_rul_rec.rule_information8 := l_rul_rec.rule_information8;
4403 END IF;
4404 IF (x_rul_rec.rule_information9 = OKC_API.G_MISS_CHAR)
4405 THEN
4406 x_rul_rec.rule_information9 := l_rul_rec.rule_information9;
4407 END IF;
4408 IF (x_rul_rec.rule_information10 = OKC_API.G_MISS_CHAR)
4409 THEN
4410 x_rul_rec.rule_information10 := l_rul_rec.rule_information10;
4411 END IF;
4412 IF (x_rul_rec.rule_information11 = OKC_API.G_MISS_CHAR)
4413 THEN
4414 x_rul_rec.rule_information11 := l_rul_rec.rule_information11;
4415 END IF;
4416 IF (x_rul_rec.rule_information12 = OKC_API.G_MISS_CHAR)
4417 THEN
4418 x_rul_rec.rule_information12 := l_rul_rec.rule_information12;
4419 END IF;
4420 IF (x_rul_rec.rule_information13 = OKC_API.G_MISS_CHAR)
4421 THEN
4422 x_rul_rec.rule_information13 := l_rul_rec.rule_information13;
4423 END IF;
4424 IF (x_rul_rec.rule_information14 = OKC_API.G_MISS_CHAR)
4425 THEN
4426 x_rul_rec.rule_information14 := l_rul_rec.rule_information14;
4427 END IF;
4428 IF (x_rul_rec.rule_information15 = OKC_API.G_MISS_CHAR)
4429 THEN
4430 x_rul_rec.rule_information15 := l_rul_rec.rule_information15;
4431 END IF;
4432 IF (x_rul_rec.template_yn = OKC_API.G_MISS_CHAR)
4433 THEN
4434 x_rul_rec.template_yn := l_rul_rec.template_yn;
4435 END IF;
4436 IF (x_rul_rec.ans_set_jtot_object_code = OKC_API.G_MISS_CHAR)
4437 THEN
4438 x_rul_rec.ans_set_jtot_object_code := l_rul_rec.ans_set_jtot_object_code;
4439 END IF;
4440 IF (x_rul_rec.ans_set_jtot_object_id1 = OKC_API.G_MISS_CHAR)
4441 THEN
4442 x_rul_rec.ans_set_jtot_object_id1 := l_rul_rec.ans_set_jtot_object_id1;
4443 END IF;
4444 IF (x_rul_rec.ans_set_jtot_object_id2 = OKC_API.G_MISS_CHAR)
4445 THEN
4446 x_rul_rec.ans_set_jtot_object_id2 := l_rul_rec.ans_set_jtot_object_id2;
4447 END IF;
4448 IF (x_rul_rec.display_sequence = OKC_API.G_MISS_NUM)
4449 THEN
4450 x_rul_rec.display_sequence := l_rul_rec.display_sequence;
4451 END IF;
4452 --Bug 3055393
4453 IF (x_rul_rec.comments = OKC_API.G_MISS_CHAR)
4454 THEN
4455 x_rul_rec.comments := l_rul_rec.comments;
4456 END IF;
4457
4458
4459
4460
4461 RETURN(l_return_status);
4462 END populate_new_record;
4463 ------------------------------------
4464 -- Set_Attributes for:OKC_RULES_B --
4465 ------------------------------------
4466 FUNCTION Set_Attributes (
4467 p_rul_rec IN rul_rec_type,
4468 x_rul_rec OUT NOCOPY rul_rec_type
4469 ) RETURN VARCHAR2 IS
4470 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4471 BEGIN
4472 x_rul_rec := p_rul_rec;
4473 RETURN(l_return_status);
4474 END Set_Attributes;
4475 BEGIN
4476
4477
4478
4479
4480 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4481 p_init_msg_list,
4482 '_PVT',
4483 x_return_status);
4484 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4485 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4486 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4487 RAISE OKC_API.G_EXCEPTION_ERROR;
4488 END IF;
4489 --- Setting item attributes
4490 l_return_status := Set_Attributes(
4491 p_rul_rec, -- IN
4492 l_rul_rec); -- OUT
4493 --- If any errors happen abort API
4494 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4495 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4496 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4497 RAISE OKC_API.G_EXCEPTION_ERROR;
4498 END IF;
4499 l_return_status := populate_new_record(l_rul_rec, l_def_rul_rec);
4500 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4501 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4502 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4503 RAISE OKC_API.G_EXCEPTION_ERROR;
4504 END IF;
4505
4506 UPDATE OKC_RULES_B
4507 SET RGP_ID = l_def_rul_rec.rgp_id,
4508 OBJECT1_ID1 = l_def_rul_rec.object1_id1,
4509 OBJECT2_ID1 = l_def_rul_rec.object2_id1,
4510 OBJECT3_ID1 = l_def_rul_rec.object3_id1,
4511 OBJECT1_ID2 = l_def_rul_rec.object1_id2,
4512 OBJECT2_ID2 = l_def_rul_rec.object2_id2,
4513 OBJECT3_ID2 = l_def_rul_rec.object3_id2,
4514 JTOT_OBJECT1_CODE = l_def_rul_rec.jtot_object1_code,
4515 JTOT_OBJECT2_CODE = l_def_rul_rec.jtot_object2_code,
4516 JTOT_OBJECT3_CODE = l_def_rul_rec.jtot_object3_code,
4517 DNZ_CHR_ID = l_def_rul_rec.dnz_chr_id,
4518 STD_TEMPLATE_YN = l_def_rul_rec.std_template_yn,
4519 WARN_YN = l_def_rul_rec.warn_yn,
4520 PRIORITY = l_def_rul_rec.priority,
4521 OBJECT_VERSION_NUMBER = l_def_rul_rec.object_version_number,
4522 CREATED_BY = l_def_rul_rec.created_by,
4523 CREATION_DATE = l_def_rul_rec.creation_date,
4524 LAST_UPDATED_BY = l_def_rul_rec.last_updated_by,
4525 LAST_UPDATE_DATE = l_def_rul_rec.last_update_date,
4526 LAST_UPDATE_LOGIN = l_def_rul_rec.last_update_login,
4527 ATTRIBUTE_CATEGORY = l_def_rul_rec.attribute_category,
4528 ATTRIBUTE1 = l_def_rul_rec.attribute1,
4529 ATTRIBUTE2 = l_def_rul_rec.attribute2,
4530 ATTRIBUTE3 = l_def_rul_rec.attribute3,
4531 ATTRIBUTE4 = l_def_rul_rec.attribute4,
4532 ATTRIBUTE5 = l_def_rul_rec.attribute5,
4533 ATTRIBUTE6 = l_def_rul_rec.attribute6,
4534 ATTRIBUTE7 = l_def_rul_rec.attribute7,
4535 ATTRIBUTE8 = l_def_rul_rec.attribute8,
4536 ATTRIBUTE9 = l_def_rul_rec.attribute9,
4537 ATTRIBUTE10 = l_def_rul_rec.attribute10,
4538 ATTRIBUTE11 = l_def_rul_rec.attribute11,
4539 ATTRIBUTE12 = l_def_rul_rec.attribute12,
4540 ATTRIBUTE13 = l_def_rul_rec.attribute13,
4541 ATTRIBUTE14 = l_def_rul_rec.attribute14,
4542 ATTRIBUTE15 = l_def_rul_rec.attribute15,
4543 RULE_INFORMATION_CATEGORY = l_def_rul_rec.rule_information_category,
4544 RULE_INFORMATION1 = l_def_rul_rec.rule_information1,
4545 RULE_INFORMATION2 = l_def_rul_rec.rule_information2,
4546 RULE_INFORMATION3 = l_def_rul_rec.rule_information3,
4547 RULE_INFORMATION4 = l_def_rul_rec.rule_information4,
4548 RULE_INFORMATION5 = l_def_rul_rec.rule_information5,
4549 RULE_INFORMATION6 = l_def_rul_rec.rule_information6,
4550 RULE_INFORMATION7 = l_def_rul_rec.rule_information7,
4551 RULE_INFORMATION8 = l_def_rul_rec.rule_information8,
4552 RULE_INFORMATION9 = l_def_rul_rec.rule_information9,
4553 RULE_INFORMATION10 = l_def_rul_rec.rule_information10,
4554 RULE_INFORMATION11 = l_def_rul_rec.rule_information11,
4555 RULE_INFORMATION12 = l_def_rul_rec.rule_information12,
4556 RULE_INFORMATION13 = l_def_rul_rec.rule_information13,
4557 RULE_INFORMATION14 = l_def_rul_rec.rule_information14,
4558 RULE_INFORMATION15 = l_def_rul_rec.rule_information15,
4559 TEMPLATE_YN = l_def_rul_rec.template_yn,
4560 ans_set_jtot_object_code = l_def_rul_rec.ans_set_jtot_object_code,
4561 ans_set_jtot_object_id1 = l_def_rul_rec.ans_set_jtot_object_id1,
4562 ans_set_jtot_object_id2 = l_def_rul_rec.ans_set_jtot_object_id2,
4563 DISPLAY_SEQUENCE = l_def_rul_rec.display_sequence,
4564 --Bug 3055393
4565 comments = l_def_rul_rec.comments
4566 WHERE ID = l_def_rul_rec.id;
4567
4568 x_rul_rec := l_def_rul_rec;
4569 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4570
4571
4572
4573
4574 EXCEPTION
4575 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4576
4577
4578 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4579 (
4580 l_api_name,
4581 G_PKG_NAME,
4582 'OKC_API.G_RET_STS_ERROR',
4583 x_msg_count,
4584 x_msg_data,
4585 '_PVT'
4586 );
4587 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4588
4589
4590 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4591 (
4592 l_api_name,
4593 G_PKG_NAME,
4594 'OKC_API.G_RET_STS_UNEXP_ERROR',
4595 x_msg_count,
4596 x_msg_data,
4597 '_PVT'
4598 );
4599 WHEN OTHERS THEN
4600
4601
4602 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4603 (
4604 l_api_name,
4605 G_PKG_NAME,
4606 'OTHERS',
4607 x_msg_count,
4608 x_msg_data,
4609 '_PVT'
4610 );
4611 END update_row;
4612 ---------------------------------
4613 -- update_row for:OKC_RULES_TL --
4614 ---------------------------------
4615 /*--Bug 3055393
4616 PROCEDURE update_row(
4617 p_init_msg_list IN VARCHAR2,
4618 x_return_status OUT NOCOPY VARCHAR2,
4619 x_msg_count OUT NOCOPY NUMBER,
4620 x_msg_data OUT NOCOPY VARCHAR2,
4621 p_okc_rules_tl_rec IN okc_rules_tl_rec_type,
4622 x_okc_rules_tl_rec OUT NOCOPY okc_rules_tl_rec_type) IS
4623
4624 l_api_version CONSTANT NUMBER := 1;
4625 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
4626 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4627 l_okc_rules_tl_rec okc_rules_tl_rec_type := p_okc_rules_tl_rec;
4628 l_def_okc_rules_tl_rec okc_rules_tl_rec_type;
4629 l_row_notfound BOOLEAN := TRUE;
4630 --
4631 l_proc varchar2(72) := g_package||'update_row';
4632 --
4633 ----------------------------------
4634 -- FUNCTION populate_new_record --
4635 ----------------------------------
4636 FUNCTION populate_new_record (
4637 p_okc_rules_tl_rec IN okc_rules_tl_rec_type,
4638 x_okc_rules_tl_rec OUT NOCOPY okc_rules_tl_rec_type
4639 ) RETURN VARCHAR2 IS
4640 l_okc_rules_tl_rec okc_rules_tl_rec_type;
4641 l_row_notfound BOOLEAN := TRUE;
4642 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4643 BEGIN
4644 x_okc_rules_tl_rec := p_okc_rules_tl_rec;
4645 -- Get current database values
4646 l_okc_rules_tl_rec := get_rec(p_okc_rules_tl_rec, l_row_notfound);
4647 IF (l_row_notfound) THEN
4648 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4649 END IF;
4650 IF (x_okc_rules_tl_rec.id = OKC_API.G_MISS_NUM)
4651 THEN
4652 x_okc_rules_tl_rec.id := l_okc_rules_tl_rec.id;
4653 END IF;
4654 IF (x_okc_rules_tl_rec.language = OKC_API.G_MISS_CHAR)
4655 THEN
4656 x_okc_rules_tl_rec.language := l_okc_rules_tl_rec.language;
4657 END IF;
4658 IF (x_okc_rules_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
4659 THEN
4660 x_okc_rules_tl_rec.source_lang := l_okc_rules_tl_rec.source_lang;
4661 END IF;
4662 IF (x_okc_rules_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
4663 THEN
4664 x_okc_rules_tl_rec.sfwt_flag := l_okc_rules_tl_rec.sfwt_flag;
4665 END IF;
4666 IF (x_okc_rules_tl_rec.comments = OKC_API.G_MISS_CHAR)
4667 THEN
4668 x_okc_rules_tl_rec.comments := l_okc_rules_tl_rec.comments;
4669 END IF;
4670 IF (x_okc_rules_tl_rec.text IS NULL)
4671 THEN
4672 x_okc_rules_tl_rec.text := l_okc_rules_tl_rec.text;
4673 END IF;
4674 IF (x_okc_rules_tl_rec.created_by = OKC_API.G_MISS_NUM)
4675 THEN
4676 x_okc_rules_tl_rec.created_by := l_okc_rules_tl_rec.created_by;
4677 END IF;
4678 IF (x_okc_rules_tl_rec.creation_date = OKC_API.G_MISS_DATE)
4679 THEN
4680 x_okc_rules_tl_rec.creation_date := l_okc_rules_tl_rec.creation_date;
4681 END IF;
4682 IF (x_okc_rules_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
4683 THEN
4684 x_okc_rules_tl_rec.last_updated_by := l_okc_rules_tl_rec.last_updated_by;
4685 END IF;
4686 IF (x_okc_rules_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
4687 THEN
4688 x_okc_rules_tl_rec.last_update_date := l_okc_rules_tl_rec.last_update_date;
4689 END IF;
4690 IF (x_okc_rules_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
4691 THEN
4692 x_okc_rules_tl_rec.last_update_login := l_okc_rules_tl_rec.last_update_login;
4693 END IF;
4694 RETURN(l_return_status);
4695 END populate_new_record;
4696 -------------------------------------
4697 -- Set_Attributes for:OKC_RULES_TL --
4698 -------------------------------------
4699 FUNCTION Set_Attributes (
4700 p_okc_rules_tl_rec IN okc_rules_tl_rec_type,
4701 x_okc_rules_tl_rec OUT NOCOPY okc_rules_tl_rec_type
4702 ) RETURN VARCHAR2 IS
4703 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4704 BEGIN
4705 x_okc_rules_tl_rec := p_okc_rules_tl_rec;
4706 x_okc_rules_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
4707 x_okc_rules_tl_rec.SOURCE_LANG := okc_util.get_userenv_lang;
4708 RETURN(l_return_status);
4709 END Set_Attributes;
4710 BEGIN
4711
4712
4713
4714
4715 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
4716 p_init_msg_list,
4717 '_PVT',
4718 x_return_status);
4719 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4720 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4721 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4722 RAISE OKC_API.G_EXCEPTION_ERROR;
4723 END IF;
4724 --- Setting item attributes
4725 l_return_status := Set_Attributes(
4726 p_okc_rules_tl_rec, -- IN
4727 l_okc_rules_tl_rec); -- OUT
4728 --- If any errors happen abort API
4729 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4730 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4731 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4732 RAISE OKC_API.G_EXCEPTION_ERROR;
4733 END IF;
4734 l_return_status := populate_new_record(l_okc_rules_tl_rec, l_def_okc_rules_tl_rec);
4735 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
4736 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
4737 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
4738 RAISE OKC_API.G_EXCEPTION_ERROR;
4739 END IF;
4740 UPDATE OKC_RULES_TL
4741 SET COMMENTS = l_def_okc_rules_tl_rec.comments,
4742 TEXT = l_def_okc_rules_tl_rec.text,
4743 CREATED_BY = l_def_okc_rules_tl_rec.created_by,
4744 CREATION_DATE = l_def_okc_rules_tl_rec.creation_date,
4745 LAST_UPDATED_BY = l_def_okc_rules_tl_rec.last_updated_by,
4746 LAST_UPDATE_DATE = l_def_okc_rules_tl_rec.last_update_date,
4747 LAST_UPDATE_LOGIN = l_def_okc_rules_tl_rec.last_update_login
4748 WHERE ID = l_def_okc_rules_tl_rec.id
4749 AND SOURCE_LANG = USERENV('LANG');
4750
4751 UPDATE OKC_RULES_TL
4752 SET SFWT_FLAG = 'Y'
4753 WHERE ID = l_def_okc_rules_tl_rec.id
4754 AND SOURCE_LANG <> USERENV('LANG');
4755
4756 x_okc_rules_tl_rec := l_def_okc_rules_tl_rec;
4757 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
4758
4759
4760
4761
4762 EXCEPTION
4763 WHEN OKC_API.G_EXCEPTION_ERROR THEN
4764
4765
4766 x_return_status := OKC_API.HANDLE_EXCEPTIONS
4767 (
4768 l_api_name,
4769 G_PKG_NAME,
4770 'OKC_API.G_RET_STS_ERROR',
4771 x_msg_count,
4772 x_msg_data,
4773 '_PVT'
4774 );
4775 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4776
4777
4778 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4779 (
4780 l_api_name,
4781 G_PKG_NAME,
4782 'OKC_API.G_RET_STS_UNEXP_ERROR',
4783 x_msg_count,
4784 x_msg_data,
4785 '_PVT'
4786 );
4787 WHEN OTHERS THEN
4788
4789
4790 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
4791 (
4792 l_api_name,
4793 G_PKG_NAME,
4794 'OTHERS',
4795 x_msg_count,
4796 x_msg_data,
4797 '_PVT'
4798 );
4799 END update_row;
4800 */
4801 --------------------------------
4802 -- update_row for:OKC_RULES_V --
4803 --------------------------------
4804 PROCEDURE update_row(
4805 p_api_version IN NUMBER,
4806 p_init_msg_list IN VARCHAR2,
4807 x_return_status OUT NOCOPY VARCHAR2,
4808 x_msg_count OUT NOCOPY NUMBER,
4809 x_msg_data OUT NOCOPY VARCHAR2,
4810 p_rulv_rec IN rulv_rec_type,
4811 x_rulv_rec OUT NOCOPY rulv_rec_type) IS
4812
4813 l_api_version CONSTANT NUMBER := 1;
4814 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
4815 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4816 l_rulv_rec rulv_rec_type := p_rulv_rec;
4817 l_def_rulv_rec rulv_rec_type;
4818 --Bug 3055393 l_okc_rules_tl_rec okc_rules_tl_rec_type;
4819 --Bug 3055393 lx_okc_rules_tl_rec okc_rules_tl_rec_type;
4820 l_rul_rec rul_rec_type;
4821 lx_rul_rec rul_rec_type;
4822 --
4823 l_proc varchar2(72) := g_package||'update_row';
4824 --
4825 -------------------------------
4826 -- FUNCTION fill_who_columns --
4827 -------------------------------
4828 FUNCTION fill_who_columns (
4829 p_rulv_rec IN rulv_rec_type
4830 ) RETURN rulv_rec_type IS
4831 l_rulv_rec rulv_rec_type := p_rulv_rec;
4832 BEGIN
4833 l_rulv_rec.LAST_UPDATE_DATE := SYSDATE;
4834 l_rulv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
4835 l_rulv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
4836 RETURN(l_rulv_rec);
4837 END fill_who_columns;
4838 ----------------------------------
4839 -- FUNCTION populate_new_record --
4840 ----------------------------------
4841 FUNCTION populate_new_record (
4842 p_rulv_rec IN rulv_rec_type,
4843 x_rulv_rec OUT NOCOPY rulv_rec_type
4844 ) RETURN VARCHAR2 IS
4845 l_rulv_rec rulv_rec_type;
4846 l_row_notfound BOOLEAN := TRUE;
4847 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4848 BEGIN
4849 x_rulv_rec := p_rulv_rec;
4850 -- Get current database values
4851 l_rulv_rec := get_rec(p_rulv_rec, l_row_notfound);
4852 IF (l_row_notfound) THEN
4853 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4854 END IF;
4855 IF (x_rulv_rec.id = OKC_API.G_MISS_NUM)
4856 THEN
4857 x_rulv_rec.id := l_rulv_rec.id;
4858 END IF;
4859 IF (x_rulv_rec.object_version_number = OKC_API.G_MISS_NUM)
4860 THEN
4861 x_rulv_rec.object_version_number := l_rulv_rec.object_version_number;
4862 END IF;
4863 /*--Bug 3055393
4864 IF (x_rulv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
4865 THEN
4866 x_rulv_rec.sfwt_flag := l_rulv_rec.sfwt_flag;
4867 END IF;
4868 */
4869 IF (x_rulv_rec.object1_id1 = OKC_API.G_MISS_CHAR)
4870 THEN
4871 x_rulv_rec.object1_id1 := l_rulv_rec.object1_id1;
4872 END IF;
4873 IF (x_rulv_rec.object2_id1 = OKC_API.G_MISS_CHAR)
4874 THEN
4875 x_rulv_rec.object2_id1 := l_rulv_rec.object2_id1;
4876 END IF;
4877 IF (x_rulv_rec.object3_id1 = OKC_API.G_MISS_CHAR)
4878 THEN
4879 x_rulv_rec.object3_id1 := l_rulv_rec.object3_id1;
4880 END IF;
4881 IF (x_rulv_rec.object1_id2 = OKC_API.G_MISS_CHAR)
4882 THEN
4883 x_rulv_rec.object1_id2 := l_rulv_rec.object1_id2;
4884 END IF;
4885 IF (x_rulv_rec.object2_id2 = OKC_API.G_MISS_CHAR)
4886 THEN
4887 x_rulv_rec.object2_id2 := l_rulv_rec.object2_id2;
4888 END IF;
4889 IF (x_rulv_rec.object3_id2 = OKC_API.G_MISS_CHAR)
4890 THEN
4891 x_rulv_rec.object3_id2 := l_rulv_rec.object3_id2;
4892 END IF;
4893 IF (x_rulv_rec.jtot_object1_code = OKC_API.G_MISS_CHAR)
4894 THEN
4895 x_rulv_rec.jtot_object1_code := l_rulv_rec.jtot_object1_code;
4896 END IF;
4897 IF (x_rulv_rec.jtot_object2_code = OKC_API.G_MISS_CHAR)
4898 THEN
4899 x_rulv_rec.jtot_object2_code := l_rulv_rec.jtot_object2_code;
4900 END IF;
4901 IF (x_rulv_rec.jtot_object3_code = OKC_API.G_MISS_CHAR)
4902 THEN
4903 x_rulv_rec.jtot_object3_code := l_rulv_rec.jtot_object3_code;
4904 END IF;
4905 IF (x_rulv_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
4906 THEN
4907 x_rulv_rec.dnz_chr_id := l_rulv_rec.dnz_chr_id;
4908 END IF;
4909 IF (x_rulv_rec.rgp_id = OKC_API.G_MISS_NUM)
4910 THEN
4911 x_rulv_rec.rgp_id := l_rulv_rec.rgp_id;
4912 END IF;
4913 IF (x_rulv_rec.priority = OKC_API.G_MISS_NUM)
4914 THEN
4915 x_rulv_rec.priority := l_rulv_rec.priority;
4916 END IF;
4917 IF (x_rulv_rec.std_template_yn = OKC_API.G_MISS_CHAR)
4918 THEN
4919 x_rulv_rec.std_template_yn := l_rulv_rec.std_template_yn;
4920 END IF;
4921 IF (x_rulv_rec.comments = OKC_API.G_MISS_CHAR)
4922 THEN
4923 x_rulv_rec.comments := l_rulv_rec.comments;
4924 END IF;
4925 IF (x_rulv_rec.warn_yn = OKC_API.G_MISS_CHAR)
4926 THEN
4927 x_rulv_rec.warn_yn := l_rulv_rec.warn_yn;
4928 END IF;
4929 IF (x_rulv_rec.attribute_category = OKC_API.G_MISS_CHAR)
4930 THEN
4931 x_rulv_rec.attribute_category := l_rulv_rec.attribute_category;
4932 END IF;
4933 IF (x_rulv_rec.attribute1 = OKC_API.G_MISS_CHAR)
4934 THEN
4935 x_rulv_rec.attribute1 := l_rulv_rec.attribute1;
4936 END IF;
4937 IF (x_rulv_rec.attribute2 = OKC_API.G_MISS_CHAR)
4938 THEN
4939 x_rulv_rec.attribute2 := l_rulv_rec.attribute2;
4940 END IF;
4941 IF (x_rulv_rec.attribute3 = OKC_API.G_MISS_CHAR)
4942 THEN
4943 x_rulv_rec.attribute3 := l_rulv_rec.attribute3;
4944 END IF;
4945 IF (x_rulv_rec.attribute4 = OKC_API.G_MISS_CHAR)
4946 THEN
4947 x_rulv_rec.attribute4 := l_rulv_rec.attribute4;
4948 END IF;
4949 IF (x_rulv_rec.attribute5 = OKC_API.G_MISS_CHAR)
4950 THEN
4951 x_rulv_rec.attribute5 := l_rulv_rec.attribute5;
4952 END IF;
4953 IF (x_rulv_rec.attribute6 = OKC_API.G_MISS_CHAR)
4954 THEN
4955 x_rulv_rec.attribute6 := l_rulv_rec.attribute6;
4956 END IF;
4957 IF (x_rulv_rec.attribute7 = OKC_API.G_MISS_CHAR)
4958 THEN
4959 x_rulv_rec.attribute7 := l_rulv_rec.attribute7;
4960 END IF;
4961 IF (x_rulv_rec.attribute8 = OKC_API.G_MISS_CHAR)
4962 THEN
4963 x_rulv_rec.attribute8 := l_rulv_rec.attribute8;
4964 END IF;
4965 IF (x_rulv_rec.attribute9 = OKC_API.G_MISS_CHAR)
4966 THEN
4967 x_rulv_rec.attribute9 := l_rulv_rec.attribute9;
4968 END IF;
4969 IF (x_rulv_rec.attribute10 = OKC_API.G_MISS_CHAR)
4970 THEN
4971 x_rulv_rec.attribute10 := l_rulv_rec.attribute10;
4972 END IF;
4973 IF (x_rulv_rec.attribute11 = OKC_API.G_MISS_CHAR)
4974 THEN
4975 x_rulv_rec.attribute11 := l_rulv_rec.attribute11;
4976 END IF;
4977 IF (x_rulv_rec.attribute12 = OKC_API.G_MISS_CHAR)
4978 THEN
4979 x_rulv_rec.attribute12 := l_rulv_rec.attribute12;
4980 END IF;
4981 IF (x_rulv_rec.attribute13 = OKC_API.G_MISS_CHAR)
4982 THEN
4983 x_rulv_rec.attribute13 := l_rulv_rec.attribute13;
4984 END IF;
4985 IF (x_rulv_rec.attribute14 = OKC_API.G_MISS_CHAR)
4986 THEN
4987 x_rulv_rec.attribute14 := l_rulv_rec.attribute14;
4988 END IF;
4989 IF (x_rulv_rec.attribute15 = OKC_API.G_MISS_CHAR)
4990 THEN
4991 x_rulv_rec.attribute15 := l_rulv_rec.attribute15;
4992 END IF;
4993 IF (x_rulv_rec.created_by = OKC_API.G_MISS_NUM)
4994 THEN
4995 x_rulv_rec.created_by := l_rulv_rec.created_by;
4996 END IF;
4997 IF (x_rulv_rec.creation_date = OKC_API.G_MISS_DATE)
4998 THEN
4999 x_rulv_rec.creation_date := l_rulv_rec.creation_date;
5000 END IF;
5001 IF (x_rulv_rec.last_updated_by = OKC_API.G_MISS_NUM)
5002 THEN
5003 x_rulv_rec.last_updated_by := l_rulv_rec.last_updated_by;
5004 END IF;
5005 IF (x_rulv_rec.last_update_date = OKC_API.G_MISS_DATE)
5006 THEN
5007 x_rulv_rec.last_update_date := l_rulv_rec.last_update_date;
5008 END IF;
5009 IF (x_rulv_rec.last_update_login = OKC_API.G_MISS_NUM)
5010 THEN
5011 x_rulv_rec.last_update_login := l_rulv_rec.last_update_login;
5012 END IF;
5013 /*--Bug 3055393
5014 IF (x_rulv_rec.text IS NULL)
5015 THEN
5016 x_rulv_rec.text := l_rulv_rec.text;
5017 END IF;
5018 */
5019 IF (x_rulv_rec.rule_information_category = OKC_API.G_MISS_CHAR)
5020 THEN
5021 x_rulv_rec.rule_information_category := l_rulv_rec.rule_information_category;
5022 END IF;
5023 IF (x_rulv_rec.rule_information1 = OKC_API.G_MISS_CHAR)
5024 THEN
5025 x_rulv_rec.rule_information1 := l_rulv_rec.rule_information1;
5026 END IF;
5027 IF (x_rulv_rec.rule_information2 = OKC_API.G_MISS_CHAR)
5028 THEN
5029 x_rulv_rec.rule_information2 := l_rulv_rec.rule_information2;
5030 END IF;
5031 IF (x_rulv_rec.rule_information3 = OKC_API.G_MISS_CHAR)
5032 THEN
5033 x_rulv_rec.rule_information3 := l_rulv_rec.rule_information3;
5034 END IF;
5035 IF (x_rulv_rec.rule_information4 = OKC_API.G_MISS_CHAR)
5036 THEN
5037 x_rulv_rec.rule_information4 := l_rulv_rec.rule_information4;
5038 END IF;
5039 IF (x_rulv_rec.rule_information5 = OKC_API.G_MISS_CHAR)
5040 THEN
5041 x_rulv_rec.rule_information5 := l_rulv_rec.rule_information5;
5042 END IF;
5043 IF (x_rulv_rec.rule_information6 = OKC_API.G_MISS_CHAR)
5044 THEN
5045 x_rulv_rec.rule_information6 := l_rulv_rec.rule_information6;
5046 END IF;
5047 IF (x_rulv_rec.rule_information7 = OKC_API.G_MISS_CHAR)
5048 THEN
5049 x_rulv_rec.rule_information7 := l_rulv_rec.rule_information7;
5050 END IF;
5051 IF (x_rulv_rec.rule_information8 = OKC_API.G_MISS_CHAR)
5052 THEN
5053 x_rulv_rec.rule_information8 := l_rulv_rec.rule_information8;
5054 END IF;
5055 IF (x_rulv_rec.rule_information9 = OKC_API.G_MISS_CHAR)
5056 THEN
5057 x_rulv_rec.rule_information9 := l_rulv_rec.rule_information9;
5058 END IF;
5059 IF (x_rulv_rec.rule_information10 = OKC_API.G_MISS_CHAR)
5060 THEN
5061 x_rulv_rec.rule_information10 := l_rulv_rec.rule_information10;
5062 END IF;
5063 IF (x_rulv_rec.rule_information11 = OKC_API.G_MISS_CHAR)
5064 THEN
5065 x_rulv_rec.rule_information11 := l_rulv_rec.rule_information11;
5066 END IF;
5067 IF (x_rulv_rec.rule_information12 = OKC_API.G_MISS_CHAR)
5068 THEN
5069 x_rulv_rec.rule_information12 := l_rulv_rec.rule_information12;
5070 END IF;
5071 IF (x_rulv_rec.rule_information13 = OKC_API.G_MISS_CHAR)
5072 THEN
5073 x_rulv_rec.rule_information13 := l_rulv_rec.rule_information13;
5074 END IF;
5075 IF (x_rulv_rec.rule_information14 = OKC_API.G_MISS_CHAR)
5076 THEN
5077 x_rulv_rec.rule_information14 := l_rulv_rec.rule_information14;
5078 END IF;
5079 IF (x_rulv_rec.rule_information15 = OKC_API.G_MISS_CHAR)
5080 THEN
5081 x_rulv_rec.rule_information15 := l_rulv_rec.rule_information15;
5082 END IF;
5083 IF (x_rulv_rec.template_yn = OKC_API.G_MISS_CHAR)
5084 THEN
5085 x_rulv_rec.template_yn := l_rulv_rec.template_yn;
5086 END IF;
5087 IF (x_rulv_rec.ans_set_jtot_object_code = OKC_API.G_MISS_CHAR)
5088 THEN
5089 x_rulv_rec.ans_set_jtot_object_code := l_rulv_rec.ans_set_jtot_object_code;
5090 END IF;
5091 IF (x_rulv_rec.ans_set_jtot_object_id1 = OKC_API.G_MISS_CHAR)
5092 THEN
5093 x_rulv_rec.ans_set_jtot_object_id1 := l_rulv_rec.ans_set_jtot_object_id1;
5094 END IF;
5095 IF (x_rulv_rec.ans_set_jtot_object_id2 = OKC_API.G_MISS_CHAR)
5096 THEN
5097 x_rulv_rec.ans_set_jtot_object_id2 := l_rulv_rec.ans_set_jtot_object_id2;
5098 END IF;
5099 IF (x_rulv_rec.display_sequence = OKC_API.G_MISS_NUM)
5100 THEN
5101 x_rulv_rec.display_sequence := l_rulv_rec.display_sequence;
5102 END IF;
5103 RETURN(l_return_status);
5104 END populate_new_record;
5105 ------------------------------------
5106 -- Set_Attributes for:OKC_RULES_V --
5107 ------------------------------------
5108 FUNCTION Set_Attributes (
5109 p_rulv_rec IN rulv_rec_type,
5110 x_rulv_rec OUT NOCOPY rulv_rec_type
5111 ) RETURN VARCHAR2 IS
5112 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5113 BEGIN
5114 x_rulv_rec := p_rulv_rec;
5115 x_rulv_rec.OBJECT_VERSION_NUMBER := NVL(x_rulv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
5116 RETURN(l_return_status);
5117 END Set_Attributes;
5118 BEGIN
5119
5120
5121
5122
5123 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
5124 G_PKG_NAME,
5125 p_init_msg_list,
5126 l_api_version,
5127 p_api_version,
5128 '_PVT',
5129 x_return_status);
5130 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5131 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5132 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5133 RAISE OKC_API.G_EXCEPTION_ERROR;
5134 END IF;
5135 --- Setting item attributes
5136 l_return_status := Set_Attributes(
5137 p_rulv_rec, -- IN
5138 l_rulv_rec); -- OUT
5139 --- If any errors happen abort API
5140 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5141 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5142 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5143 RAISE OKC_API.G_EXCEPTION_ERROR;
5144 END IF;
5145 l_return_status := populate_new_record(l_rulv_rec, l_def_rulv_rec);
5146 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5147 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5148 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5149 RAISE OKC_API.G_EXCEPTION_ERROR;
5150 END IF;
5151 l_def_rulv_rec := fill_who_columns(l_def_rulv_rec);
5152
5153 IF p_rulv_rec.VALIDATE_YN = 'Y' THEN
5154 --- Validate all non-missing attributes (Item Level Validation)
5155 l_return_status := Validate_Attributes(l_def_rulv_rec);
5156 --- If any errors happen abort API
5157 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5158 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5159 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5160 RAISE OKC_API.G_EXCEPTION_ERROR;
5161 END IF;
5162 END IF; -- end of VALIDATE_YN
5163
5164 l_return_status := Validate_Record(l_def_rulv_rec);
5165 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5166 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5167 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5168 RAISE OKC_API.G_EXCEPTION_ERROR;
5169 END IF;
5170
5171 --+added override comments
5172 l_def_rulv_rec.comments := set_comments(l_def_rulv_rec);
5173 --+
5174
5175 --------------------------------------
5176 -- Move VIEW record to "Child" records
5177 --------------------------------------
5178 --Bug 3055393 migrate(l_def_rulv_rec, l_okc_rules_tl_rec);
5179 migrate(l_def_rulv_rec, l_rul_rec);
5180 --------------------------------------------
5181 -- Call the UPDATE_ROW for each child record
5182 --------------------------------------------
5183 /*--Bug 3055393
5184 update_row(
5185 p_init_msg_list,
5186 x_return_status,
5187 x_msg_count,
5188 x_msg_data,
5189 l_okc_rules_tl_rec,
5190 lx_okc_rules_tl_rec
5191 );
5192 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5193 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5194 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5195 RAISE OKC_API.G_EXCEPTION_ERROR;
5196 END IF;
5197 migrate(lx_okc_rules_tl_rec, l_def_rulv_rec);
5198 */
5199 update_row(
5200 p_init_msg_list,
5201 x_return_status,
5202 x_msg_count,
5203 x_msg_data,
5204 l_rul_rec,
5205 lx_rul_rec
5206 );
5207 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5208 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5209 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5210 RAISE OKC_API.G_EXCEPTION_ERROR;
5211 END IF;
5212 migrate(lx_rul_rec, l_def_rulv_rec);
5213 x_rulv_rec := l_def_rulv_rec;
5214 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
5215
5216
5217
5218
5219 EXCEPTION
5220 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5221
5222
5223 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5224 (
5225 l_api_name,
5226 G_PKG_NAME,
5227 'OKC_API.G_RET_STS_ERROR',
5228 x_msg_count,
5229 x_msg_data,
5230 '_PVT'
5231 );
5232 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5233
5234
5235 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5236 (
5237 l_api_name,
5238 G_PKG_NAME,
5239 'OKC_API.G_RET_STS_UNEXP_ERROR',
5240 x_msg_count,
5241 x_msg_data,
5242 '_PVT'
5243 );
5244 WHEN OTHERS THEN
5245
5246
5247 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5248 (
5249 l_api_name,
5250 G_PKG_NAME,
5251 'OTHERS',
5252 x_msg_count,
5253 x_msg_data,
5254 '_PVT'
5255 );
5256 END update_row;
5257 ----------------------------------------
5258 -- PL/SQL TBL update_row for:RULV_TBL --
5259 ----------------------------------------
5260 PROCEDURE update_row(
5261 p_api_version IN NUMBER,
5262 p_init_msg_list IN VARCHAR2,
5263 x_return_status OUT NOCOPY VARCHAR2,
5264 x_msg_count OUT NOCOPY NUMBER,
5265 x_msg_data OUT NOCOPY VARCHAR2,
5266 p_rulv_tbl IN rulv_tbl_type,
5267 x_rulv_tbl OUT NOCOPY rulv_tbl_type) IS
5268
5269 l_api_version CONSTANT NUMBER := 1;
5270 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
5271 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5272 i NUMBER := 0;
5273 --
5274 l_proc varchar2(72) := g_package||'update_row';
5275 --
5276 BEGIN
5277
5278
5279
5280
5281 OKC_API.init_msg_list(p_init_msg_list);
5282 -- Make sure PL/SQL table has records in it before passing
5283 IF (p_rulv_tbl.COUNT > 0) THEN
5284 i := p_rulv_tbl.FIRST;
5285 LOOP
5286 update_row (
5287 p_api_version => p_api_version,
5288 p_init_msg_list => OKC_API.G_FALSE,
5289 x_return_status => x_return_status,
5290 x_msg_count => x_msg_count,
5291 x_msg_data => x_msg_data,
5292 p_rulv_rec => p_rulv_tbl(i),
5293 x_rulv_rec => x_rulv_tbl(i));
5294 EXIT WHEN (i = p_rulv_tbl.LAST);
5295 i := p_rulv_tbl.NEXT(i);
5296 END LOOP;
5297 END IF;
5298
5299
5300
5301
5302 EXCEPTION
5303 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5304
5305
5306 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5307 (
5308 l_api_name,
5309 G_PKG_NAME,
5310 'OKC_API.G_RET_STS_ERROR',
5311 x_msg_count,
5312 x_msg_data,
5313 '_PVT'
5314 );
5315 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5316
5317
5318 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5319 (
5320 l_api_name,
5321 G_PKG_NAME,
5322 'OKC_API.G_RET_STS_UNEXP_ERROR',
5323 x_msg_count,
5324 x_msg_data,
5325 '_PVT'
5326 );
5327 WHEN OTHERS THEN
5328
5329
5330 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5331 (
5332 l_api_name,
5333 G_PKG_NAME,
5334 'OTHERS',
5335 x_msg_count,
5336 x_msg_data,
5337 '_PVT'
5338 );
5339 END update_row;
5340
5341 ---------------------------------------------------------------------------
5342 -- PROCEDURE delete_row
5343 ---------------------------------------------------------------------------
5344 --------------------------------
5345 -- delete_row for:OKC_RULES_B --
5346 --------------------------------
5347 PROCEDURE delete_row(
5348 p_init_msg_list IN VARCHAR2,
5349 x_return_status OUT NOCOPY VARCHAR2,
5350 x_msg_count OUT NOCOPY NUMBER,
5351 x_msg_data OUT NOCOPY VARCHAR2,
5352 p_rul_rec IN rul_rec_type) IS
5353
5354 l_api_version CONSTANT NUMBER := 1;
5355 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
5356 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5357 l_rul_rec rul_rec_type:= p_rul_rec;
5358 l_row_notfound BOOLEAN := TRUE;
5359 --
5360 l_proc varchar2(72) := g_package||'delete_row';
5361 --
5362 BEGIN
5363
5364
5365
5366
5367 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
5368 p_init_msg_list,
5369 '_PVT',
5370 x_return_status);
5371 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5372 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5373 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5374 RAISE OKC_API.G_EXCEPTION_ERROR;
5375 END IF;
5376 DELETE FROM OKC_RULES_B
5377 WHERE ID = l_rul_rec.id;
5378
5379 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
5380
5381
5382
5383
5384 EXCEPTION
5385 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5386
5387
5388 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5389 (
5390 l_api_name,
5391 G_PKG_NAME,
5392 'OKC_API.G_RET_STS_ERROR',
5393 x_msg_count,
5394 x_msg_data,
5395 '_PVT'
5396 );
5397 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5398
5399
5400 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5401 (
5402 l_api_name,
5403 G_PKG_NAME,
5404 'OKC_API.G_RET_STS_UNEXP_ERROR',
5405 x_msg_count,
5406 x_msg_data,
5407 '_PVT'
5408 );
5409 WHEN OTHERS THEN
5410
5411
5412 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5413 (
5414 l_api_name,
5415 G_PKG_NAME,
5416 'OTHERS',
5417 x_msg_count,
5418 x_msg_data,
5419 '_PVT'
5420 );
5421 END delete_row;
5422 ---------------------------------
5423 -- delete_row for:OKC_RULES_TL --
5424 ---------------------------------
5425 /*--Bug 3055393
5426 PROCEDURE delete_row(
5427 p_init_msg_list IN VARCHAR2,
5428 x_return_status OUT NOCOPY VARCHAR2,
5429 x_msg_count OUT NOCOPY NUMBER,
5430 x_msg_data OUT NOCOPY VARCHAR2,
5431 p_okc_rules_tl_rec IN okc_rules_tl_rec_type) IS
5432
5433 l_api_version CONSTANT NUMBER := 1;
5434 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
5435 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5436 l_okc_rules_tl_rec okc_rules_tl_rec_type:= p_okc_rules_tl_rec;
5437 l_row_notfound BOOLEAN := TRUE;
5438 --
5439 l_proc varchar2(72) := g_package||'delete_row';
5440 --
5441 -------------------------------------
5442 -- Set_Attributes for:OKC_RULES_TL --
5443 -------------------------------------
5444 FUNCTION Set_Attributes (
5445 p_okc_rules_tl_rec IN okc_rules_tl_rec_type,
5446 x_okc_rules_tl_rec OUT NOCOPY okc_rules_tl_rec_type
5447 ) RETURN VARCHAR2 IS
5448 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5449 BEGIN
5450 x_okc_rules_tl_rec := p_okc_rules_tl_rec;
5451 x_okc_rules_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
5452 RETURN(l_return_status);
5453 END Set_Attributes;
5454 BEGIN
5455
5456
5457
5458
5459 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
5460 p_init_msg_list,
5461 '_PVT',
5462 x_return_status);
5463 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5464 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5465 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5466 RAISE OKC_API.G_EXCEPTION_ERROR;
5467 END IF;
5468 --- Setting item attributes
5469 l_return_status := Set_Attributes(
5470 p_okc_rules_tl_rec, -- IN
5471 l_okc_rules_tl_rec); -- OUT
5472 --- If any errors happen abort API
5473 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5474 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5475 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5476 RAISE OKC_API.G_EXCEPTION_ERROR;
5477 END IF;
5478 DELETE FROM OKC_RULES_TL
5479 WHERE ID = l_okc_rules_tl_rec.id;
5480
5481 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
5482
5483
5484
5485
5486 EXCEPTION
5487 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5488
5489
5490 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5491 (
5492 l_api_name,
5493 G_PKG_NAME,
5494 'OKC_API.G_RET_STS_ERROR',
5495 x_msg_count,
5496 x_msg_data,
5497 '_PVT'
5498 );
5499 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5500
5501
5502 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5503 (
5504 l_api_name,
5505 G_PKG_NAME,
5506 'OKC_API.G_RET_STS_UNEXP_ERROR',
5507 x_msg_count,
5508 x_msg_data,
5509 '_PVT'
5510 );
5511 WHEN OTHERS THEN
5512
5513
5514 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5515 (
5516 l_api_name,
5517 G_PKG_NAME,
5518 'OTHERS',
5519 x_msg_count,
5520 x_msg_data,
5521 '_PVT'
5522 );
5523 END delete_row;
5524 */
5525 --------------------------------
5526 -- delete_row for:OKC_RULES_V --
5527 --------------------------------
5528 PROCEDURE delete_row(
5529 p_api_version IN NUMBER,
5530 p_init_msg_list IN VARCHAR2,
5531 x_return_status OUT NOCOPY VARCHAR2,
5532 x_msg_count OUT NOCOPY NUMBER,
5533 x_msg_data OUT NOCOPY VARCHAR2,
5534 p_rulv_rec IN rulv_rec_type) IS
5535
5536 l_api_version CONSTANT NUMBER := 1;
5537 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
5538 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5539 l_rulv_rec rulv_rec_type := p_rulv_rec;
5540 --Bug 3055393 l_okc_rules_tl_rec okc_rules_tl_rec_type;
5541 l_rul_rec rul_rec_type;
5542 --
5543 l_proc varchar2(72) := g_package||'delete_row';
5544 --
5545 BEGIN
5546
5547
5548
5549
5550 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
5551 G_PKG_NAME,
5552 p_init_msg_list,
5553 l_api_version,
5554 p_api_version,
5555 '_PVT',
5556 x_return_status);
5557 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5558 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5559 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
5560 RAISE OKC_API.G_EXCEPTION_ERROR;
5561 END IF;
5562 --------------------------------------
5563 -- Move VIEW record to "Child" records
5564 --------------------------------------
5565 --Bug 3055393 migrate(l_rulv_rec, l_okc_rules_tl_rec);
5566 migrate(l_rulv_rec, l_rul_rec);
5567 --------------------------------------------
5568 -- Call the DELETE_ROW for each child record
5569 --------------------------------------------
5570 /*--Bug 3055393
5571 delete_row(
5572 p_init_msg_list,
5573 x_return_status,
5574 x_msg_count,
5575 x_msg_data,
5576 l_okc_rules_tl_rec
5577 );
5578 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5579 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5580 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5581 RAISE OKC_API.G_EXCEPTION_ERROR;
5582 END IF;
5583 */
5584 delete_row(
5585 p_init_msg_list,
5586 x_return_status,
5587 x_msg_count,
5588 x_msg_data,
5589 l_rul_rec
5590 );
5591 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
5592 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5593 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
5594 RAISE OKC_API.G_EXCEPTION_ERROR;
5595 END IF;
5596 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
5597
5598
5599
5600
5601 EXCEPTION
5602 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5603
5604
5605 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5606 (
5607 l_api_name,
5608 G_PKG_NAME,
5609 'OKC_API.G_RET_STS_ERROR',
5610 x_msg_count,
5611 x_msg_data,
5612 '_PVT'
5613 );
5614 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5615
5616
5617 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5618 (
5619 l_api_name,
5620 G_PKG_NAME,
5621 'OKC_API.G_RET_STS_UNEXP_ERROR',
5622 x_msg_count,
5623 x_msg_data,
5624 '_PVT'
5625 );
5626 WHEN OTHERS THEN
5627
5628
5629 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5630 (
5631 l_api_name,
5632 G_PKG_NAME,
5633 'OTHERS',
5634 x_msg_count,
5635 x_msg_data,
5636 '_PVT'
5637 );
5638 END delete_row;
5639 ----------------------------------------
5640 -- PL/SQL TBL delete_row for:RULV_TBL --
5641 ----------------------------------------
5642 PROCEDURE delete_row(
5643 p_api_version IN NUMBER,
5644 p_init_msg_list IN VARCHAR2,
5645 x_return_status OUT NOCOPY VARCHAR2,
5646 x_msg_count OUT NOCOPY NUMBER,
5647 x_msg_data OUT NOCOPY VARCHAR2,
5648 p_rulv_tbl IN rulv_tbl_type) IS
5649
5650 l_api_version CONSTANT NUMBER := 1;
5651 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
5652 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
5653 i NUMBER := 0;
5654 --
5655 l_proc varchar2(72) := g_package||'delete_row';
5656 --
5657 BEGIN
5658
5659
5660
5661
5662 OKC_API.init_msg_list(p_init_msg_list);
5663 -- Make sure PL/SQL table has records in it before passing
5664 IF (p_rulv_tbl.COUNT > 0) THEN
5665 i := p_rulv_tbl.FIRST;
5666 LOOP
5667 delete_row (
5668 p_api_version => p_api_version,
5669 p_init_msg_list => OKC_API.G_FALSE,
5670 x_return_status => x_return_status,
5671 x_msg_count => x_msg_count,
5672 x_msg_data => x_msg_data,
5673 p_rulv_rec => p_rulv_tbl(i));
5674 EXIT WHEN (i = p_rulv_tbl.LAST);
5675 i := p_rulv_tbl.NEXT(i);
5676 END LOOP;
5677 END IF;
5678
5679
5680
5681
5682 EXCEPTION
5683 WHEN OKC_API.G_EXCEPTION_ERROR THEN
5684
5685
5686 x_return_status := OKC_API.HANDLE_EXCEPTIONS
5687 (
5688 l_api_name,
5689 G_PKG_NAME,
5690 'OKC_API.G_RET_STS_ERROR',
5691 x_msg_count,
5692 x_msg_data,
5693 '_PVT'
5694 );
5695 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5696
5697
5698 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5699 (
5700 l_api_name,
5701 G_PKG_NAME,
5702 'OKC_API.G_RET_STS_UNEXP_ERROR',
5703 x_msg_count,
5704 x_msg_data,
5705 '_PVT'
5706 );
5707 WHEN OTHERS THEN
5708
5709
5710 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
5711 (
5712 l_api_name,
5713 G_PKG_NAME,
5714 'OTHERS',
5715 x_msg_count,
5716 x_msg_data,
5717 '_PVT'
5718 );
5719 END delete_row;
5720
5721 ---------------------------------------------------------------
5722 -- Procedure for mass insert in OKC_RULES _B and TL tables
5723 ---------------------------------------------------------------
5724 PROCEDURE INSERT_ROW_UPG(x_return_status OUT NOCOPY VARCHAR2, p_rulv_tbl rulv_tbl_type) IS
5725 l_tabsize NUMBER := p_rulv_tbl.COUNT;
5726 l_source_lang VARCHAR2(12) := okc_util.get_userenv_lang;
5727
5728 in_id OKC_DATATYPES.NumberTabTyp;
5729 in_object_version_number OKC_DATATYPES.NumberTabTyp;
5730 --Bug 3055393 in_sfwt_flag OKC_DATATYPES.Var3TabTyp;
5731 in_object1_id1 OKC_DATATYPES.Var40TabTyp;
5732 in_object2_id1 OKC_DATATYPES.Var40TabTyp;
5733 in_object3_id1 OKC_DATATYPES.Var40TabTyp;
5734 in_object1_id2 OKC_DATATYPES.Var200TabTyp;
5735 in_object2_id2 OKC_DATATYPES.Var200TabTyp;
5736 in_object3_id2 OKC_DATATYPES.Var200TabTyp;
5737 in_jtot_object1_code OKC_DATATYPES.Var30TabTyp;
5738 in_jtot_object2_code OKC_DATATYPES.Var30TabTyp;
5739 in_jtot_object3_code OKC_DATATYPES.Var30TabTyp;
5740 in_dnz_chr_id OKC_DATATYPES.NumberTabTyp;
5741 in_rgp_id OKC_DATATYPES.NumberTabTyp;
5742 in_priority OKC_DATATYPES.NumberTabTyp;
5743 in_std_template_yn OKC_DATATYPES.Var3TabTyp;
5744 in_comments OKC_DATATYPES.Var1995TabTyp;
5745 in_warn_yn OKC_DATATYPES.Var3TabTyp;
5746 in_attribute_category OKC_DATATYPES.Var90TabTyp;
5747 in_attribute1 OKC_DATATYPES.Var450TabTyp;
5748 in_attribute2 OKC_DATATYPES.Var450TabTyp;
5749 in_attribute3 OKC_DATATYPES.Var450TabTyp;
5750 in_attribute4 OKC_DATATYPES.Var450TabTyp;
5751 in_attribute5 OKC_DATATYPES.Var450TabTyp;
5752 in_attribute6 OKC_DATATYPES.Var450TabTyp;
5753 in_attribute7 OKC_DATATYPES.Var450TabTyp;
5754 in_attribute8 OKC_DATATYPES.Var450TabTyp;
5755 in_attribute9 OKC_DATATYPES.Var450TabTyp;
5756 in_attribute10 OKC_DATATYPES.Var450TabTyp;
5757 in_attribute11 OKC_DATATYPES.Var450TabTyp;
5758 in_attribute12 OKC_DATATYPES.Var450TabTyp;
5759 in_attribute13 OKC_DATATYPES.Var450TabTyp;
5760 in_attribute14 OKC_DATATYPES.Var450TabTyp;
5761 in_attribute15 OKC_DATATYPES.Var450TabTyp;
5762 in_created_by OKC_DATATYPES.NumberTabTyp;
5763 in_creation_date OKC_DATATYPES.DateTabTyp;
5764 in_last_updated_by OKC_DATATYPES.NumberTabTyp;
5765 in_last_update_date OKC_DATATYPES.DateTabTyp;
5766 in_last_update_login OKC_DATATYPES.NumberTabTyp;
5767 --Bug 3055393 in_text OKC_DATATYPES.clobTabTyp;
5768 in_rule_information_category OKC_DATATYPES.Var90TabTyp;
5769 in_rule_information1 OKC_DATATYPES.Var450TabTyp;
5770 in_rule_information2 OKC_DATATYPES.Var450TabTyp;
5771 in_rule_information3 OKC_DATATYPES.Var450TabTyp;
5772 in_rule_information4 OKC_DATATYPES.Var450TabTyp;
5773 in_rule_information5 OKC_DATATYPES.Var450TabTyp;
5774 in_rule_information6 OKC_DATATYPES.Var450TabTyp;
5775 in_rule_information7 OKC_DATATYPES.Var450TabTyp;
5776 in_rule_information8 OKC_DATATYPES.Var450TabTyp;
5777 in_rule_information9 OKC_DATATYPES.Var450TabTyp;
5778 in_rule_information10 OKC_DATATYPES.Var450TabTyp;
5779 in_rule_information11 OKC_DATATYPES.Var450TabTyp;
5780 in_rule_information12 OKC_DATATYPES.Var450TabTyp;
5781 in_rule_information13 OKC_DATATYPES.Var450TabTyp;
5782 in_rule_information14 OKC_DATATYPES.Var450TabTyp;
5783 in_rule_information15 OKC_DATATYPES.Var450TabTyp;
5784 in_template_yn OKC_DATATYPES.Var3TabTyp;
5785 in_ans_set_jtot_object_code OKC_DATATYPES.Var90TabTyp;
5786 in_ans_set_jtot_object_id1 OKC_DATATYPES.Var90TabTyp;
5787 in_ans_set_jtot_object_id2 OKC_DATATYPES.Var90TabTyp;
5788 in_display_sequence OKC_DATATYPES.NumberTabTyp;
5789 i number;
5790 j number;
5791 --
5792 l_proc varchar2(72) := g_package||'INSERT_ROW_UPG';
5793 --
5794 BEGIN
5795 -- Initialize return status
5796 x_return_status := OKC_API.G_RET_STS_SUCCESS;
5797
5798 i := p_rulv_tbl.FIRST; j:=0;
5799 while i is not null
5800 LOOP
5801 j:=j+1;
5802 in_id (j) := p_rulv_tbl(i).id;
5803 in_object_version_number (j) := p_rulv_tbl(i).object_version_number;
5804 --Bug 3055393 in_sfwt_flag (j) := p_rulv_tbl(i).sfwt_flag;
5805 in_object1_id1 (j) := p_rulv_tbl(i).object1_id1;
5806 in_object2_id1 (j) := p_rulv_tbl(i).object2_id1;
5807 in_object3_id1 (j) := p_rulv_tbl(i).object3_id1;
5808 in_object1_id2 (j) := p_rulv_tbl(i).object1_id2;
5809 in_object2_id2 (j) := p_rulv_tbl(i).object2_id2;
5810 in_object3_id2 (j) := p_rulv_tbl(i).object3_id2;
5811 in_jtot_object1_code (j) := p_rulv_tbl(i).jtot_object1_code;
5812 in_jtot_object2_code (j) := p_rulv_tbl(i).jtot_object2_code;
5813 in_jtot_object3_code (j) := p_rulv_tbl(i).jtot_object3_code;
5814 in_dnz_chr_id (j) := p_rulv_tbl(i).dnz_chr_id;
5815 in_rgp_id (j) := p_rulv_tbl(i).rgp_id;
5816 in_priority (j) := p_rulv_tbl(i).priority;
5817 in_std_template_yn (j) := p_rulv_tbl(i).std_template_yn;
5818 in_comments (j) := p_rulv_tbl(i).comments;
5819 in_warn_yn (j) := p_rulv_tbl(i).warn_yn;
5820 in_attribute_category (j) := p_rulv_tbl(i).attribute_category;
5821 in_attribute1 (j) := p_rulv_tbl(i).attribute1;
5822 in_attribute2 (j) := p_rulv_tbl(i).attribute2;
5823 in_attribute3 (j) := p_rulv_tbl(i).attribute3;
5824 in_attribute4 (j) := p_rulv_tbl(i).attribute4;
5825 in_attribute5 (j) := p_rulv_tbl(i).attribute5;
5826 in_attribute6 (j) := p_rulv_tbl(i).attribute6;
5827 in_attribute7 (j) := p_rulv_tbl(i).attribute7;
5828 in_attribute8 (j) := p_rulv_tbl(i).attribute8;
5829 in_attribute9 (j) := p_rulv_tbl(i).attribute9;
5830 in_attribute10 (j) := p_rulv_tbl(i).attribute10;
5831 in_attribute11 (j) := p_rulv_tbl(i).attribute11;
5832 in_attribute12 (j) := p_rulv_tbl(i).attribute12;
5833 in_attribute13 (j) := p_rulv_tbl(i).attribute13;
5834 in_attribute14 (j) := p_rulv_tbl(i).attribute14;
5835 in_attribute15 (j) := p_rulv_tbl(i).attribute15;
5836 in_created_by (j) := p_rulv_tbl(i).created_by;
5837 in_creation_date (j) := p_rulv_tbl(i).creation_date;
5838 in_last_updated_by (j) := p_rulv_tbl(i).last_updated_by;
5839 in_last_update_date (j) := p_rulv_tbl(i).last_update_date;
5840 in_last_update_login (j) := p_rulv_tbl(i).last_update_login;
5841 --Bug 3055393 in_text (j) := p_rulv_tbl(i).text;
5842 in_rule_information_category(j) := p_rulv_tbl(i).rule_information_category;
5843 in_rule_information1 (j) := p_rulv_tbl(i).rule_information1;
5844 in_rule_information2 (j) := p_rulv_tbl(i).rule_information2;
5845 in_rule_information3 (j) := p_rulv_tbl(i).rule_information3;
5846 in_rule_information4 (j) := p_rulv_tbl(i).rule_information4;
5847 in_rule_information5 (j) := p_rulv_tbl(i).rule_information5;
5848 in_rule_information6 (j) := p_rulv_tbl(i).rule_information6;
5849 in_rule_information7 (j) := p_rulv_tbl(i).rule_information7;
5850 in_rule_information8 (j) := p_rulv_tbl(i).rule_information8;
5851 in_rule_information9 (j) := p_rulv_tbl(i).rule_information9;
5852 in_rule_information10 (j) := p_rulv_tbl(i).rule_information10;
5853 in_rule_information11 (j) := p_rulv_tbl(i).rule_information11;
5854 in_rule_information12 (j) := p_rulv_tbl(i).rule_information12;
5855 in_rule_information13 (j) := p_rulv_tbl(i).rule_information13;
5856 in_rule_information14 (j) := p_rulv_tbl(i).rule_information14;
5857 in_rule_information15 (j) := p_rulv_tbl(i).rule_information15;
5858 in_template_yn (j) := p_rulv_tbl(i).template_yn;
5859 in_ans_set_jtot_object_code (j) := p_rulv_tbl(i).ans_set_jtot_object_code;
5860 in_ans_set_jtot_object_id1 (j) := p_rulv_tbl(i).ans_set_jtot_object_id1;
5861 in_ans_set_jtot_object_id2 (j) := p_rulv_tbl(i).ans_set_jtot_object_id2;
5862 in_display_sequence (j) := p_rulv_tbl(i).display_sequence;
5863 i:=p_rulv_tbl.next(i);
5864 END LOOP;
5865
5866 FORALL i in 1..l_tabsize
5867 INSERT
5868 INTO OKC_RULES_B
5869 (
5870 id,
5871 rgp_id,
5872 object1_id1,
5873 object2_id1,
5874 object3_id1,
5875 object1_id2,
5876 object2_id2,
5877 object3_id2,
5878 jtot_object1_code,
5879 jtot_object2_code,
5880 jtot_object3_code,
5881 dnz_chr_id,
5882 std_template_yn,
5883 warn_yn,
5884 priority,
5885 object_version_number,
5886 created_by,
5887 creation_date,
5888 last_updated_by,
5889 last_update_date,
5890 last_update_login,
5891 attribute_category,
5892 attribute1,
5893 attribute2,
5894 attribute3,
5895 attribute4,
5896 attribute5,
5897 attribute6,
5898 attribute7,
5899 attribute8,
5900 attribute9,
5901 attribute10,
5902 attribute11,
5903 attribute12,
5904 attribute13,
5905 attribute14,
5906 attribute15,
5907 rule_information_category,
5908 rule_information1,
5909 rule_information2,
5910 rule_information3,
5911 rule_information4,
5912 rule_information5,
5913 rule_information6,
5914 rule_information7,
5915 rule_information8,
5916 rule_information9,
5917 rule_information10,
5918 rule_information11,
5919 rule_information12,
5920 rule_information13,
5921 rule_information14,
5922 rule_information15,
5923 template_yn,
5924 ans_set_jtot_object_code,
5925 ans_set_jtot_object_id1,
5926 ans_set_jtot_object_id2,
5927 display_sequence,
5928 --Bug 3055393
5929 comments
5930 )
5931 VALUES (
5932 in_id(i),
5933 in_rgp_id(i),
5934 in_object1_id1(i),
5935 in_object2_id1(i),
5936 in_object3_id1(i),
5937 in_object1_id2(i),
5938 in_object2_id2(i),
5939 in_object3_id2(i),
5940 in_jtot_object1_code(i),
5941 in_jtot_object2_code(i),
5942 in_jtot_object3_code(i),
5943 in_dnz_chr_id(i),
5944 in_std_template_yn(i),
5945 in_warn_yn(i),
5946 in_priority(i),
5947 in_object_version_number(i),
5948 in_created_by(i),
5949 in_creation_date(i),
5950 in_last_updated_by(i),
5951 in_last_update_date(i),
5952 in_last_update_login(i),
5953 in_attribute_category(i),
5954 in_attribute1(i),
5955 in_attribute2(i),
5956 in_attribute3(i),
5957 in_attribute4(i),
5958 in_attribute5(i),
5959 in_attribute6(i),
5960 in_attribute7(i),
5961 in_attribute8(i),
5962 in_attribute9(i),
5963 in_attribute10(i),
5964 in_attribute11(i),
5965 in_attribute12(i),
5966 in_attribute13(i),
5967 in_attribute14(i),
5968 in_attribute15(i),
5969 in_rule_information_category(i),
5970 in_rule_information1(i),
5971 in_rule_information2(i),
5972 in_rule_information3(i),
5973 in_rule_information4(i),
5974 in_rule_information5(i),
5975 in_rule_information6(i),
5976 in_rule_information7(i),
5977 in_rule_information8(i),
5978 in_rule_information9(i),
5979 in_rule_information10(i),
5980 in_rule_information11(i),
5981 in_rule_information12(i),
5982 in_rule_information13(i),
5983 in_rule_information14(i),
5984 in_rule_information15(i),
5985 in_template_yn(i),
5986 in_ans_set_jtot_object_code(i),
5987 in_ans_set_jtot_object_id1(i),
5988 in_ans_set_jtot_object_id2(i),
5989 in_display_sequence(i),
5990 --Bug 3055393
5991 in_comments(i)
5992 );
5993 /*--Bug 3055393
5994 FOR lang_i IN OKC_UTIL.g_language_code.FIRST..OKC_UTIL.g_language_code.LAST LOOP
5995 FORALL i in 1..l_tabsize
5996 INSERT INTO OKC_RULES_TL(
5997 id,
5998 language,
5999 source_lang,
6000 sfwt_flag,
6001 comments,
6002 --text,
6003 created_by,
6004 creation_date,
6005 last_updated_by,
6006 last_update_date,
6007 last_update_login
6008 )
6009 VALUES (
6010 in_id(i),
6011 OKC_UTIL.g_language_code(lang_i),
6012 l_source_lang,
6013 in_sfwt_flag(i),
6014 in_comments(i),
6015 --in_text(i),
6016 in_created_by(i),
6017 in_creation_date(i),
6018 in_last_updated_by(i),
6019 in_last_update_date(i),
6020 in_last_update_login(i)
6021 );
6022 END LOOP;
6023 */
6024 EXCEPTION
6025 WHEN OTHERS THEN
6026
6027 -- store SQL error message on message stack
6028 OKC_API.SET_MESSAGE(
6029 p_app_name => G_APP_NAME,
6030 p_msg_name => G_UNEXPECTED_ERROR,
6031 p_token1 => G_SQLCODE_TOKEN,
6032 p_token1_value => SQLCODE,
6033 p_token2 => G_SQLERRM_TOKEN,
6034 p_token2_value => SQLERRM);
6035 -- notify caller of an error as UNEXPETED error
6036 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
6037 --RAISE;
6038 END INSERT_ROW_UPG;
6039
6040 --This function is called from versioning API OKC_VERSION_PVT
6041 --Old Location: OKCRVERB.pls
6042 --New Location: Base Table API
6043
6044 FUNCTION create_version(
6045 p_chr_id IN NUMBER,
6046 p_major_version IN NUMBER
6047 ) RETURN VARCHAR2 IS
6048
6049 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
6050
6051 --
6052 l_proc varchar2(72) := g_package||'create_version';
6053 --
6054 BEGIN
6055
6056
6057
6058
6059 INSERT INTO okc_rules_bh
6060 (
6061 major_version,
6062 id,
6063 rgp_id,
6064 object1_id1,
6065 object2_id1,
6066 object3_id1,
6067 object1_id2,
6068 object2_id2,
6069 object3_id2,
6070 jtot_object1_code,
6071 jtot_object2_code,
6072 jtot_object3_code,
6073 dnz_chr_id,
6074 std_template_yn,
6075 warn_yn,
6076 priority,
6077 object_version_number,
6078 created_by,
6079 creation_date,
6080 last_updated_by,
6081 last_update_date,
6082 last_update_login,
6083 attribute_category,
6084 attribute1,
6085 attribute2,
6086 attribute3,
6087 attribute4,
6088 attribute5,
6089 attribute6,
6090 attribute7,
6091 attribute8,
6092 attribute9,
6093 attribute10,
6094 attribute11,
6095 attribute12,
6096 attribute13,
6097 attribute14,
6098 attribute15,
6099 rule_information_category,
6100 rule_information1,
6101 rule_information2,
6102 rule_information3,
6103 rule_information4,
6104 rule_information5,
6105 rule_information6,
6106 rule_information7,
6107 rule_information8,
6108 rule_information9,
6109 rule_information10,
6110 rule_information11,
6111 rule_information12,
6112 rule_information13,
6113 rule_information14,
6114 rule_information15,
6115 template_yn,
6116 ans_set_jtot_object_code,
6117 ans_set_jtot_object_id1,
6118 ans_set_jtot_object_id2,
6119 display_sequence,
6120 --Bug 3055393
6121 comments
6122 )
6123 SELECT
6124 p_major_version,
6125 id,
6126 rgp_id,
6127 object1_id1,
6128 object2_id1,
6129 object3_id1,
6130 object1_id2,
6131 object2_id2,
6132 object3_id2,
6133 jtot_object1_code,
6134 jtot_object2_code,
6135 jtot_object3_code,
6136 dnz_chr_id,
6137 std_template_yn,
6138 warn_yn,
6139 priority,
6140 object_version_number,
6141 created_by,
6142 creation_date,
6143 last_updated_by,
6144 last_update_date,
6145 last_update_login,
6146 attribute_category,
6147 attribute1,
6148 attribute2,
6149 attribute3,
6150 attribute4,
6151 attribute5,
6152 attribute6,
6153 attribute7,
6154 attribute8,
6155 attribute9,
6156 attribute10,
6157 attribute11,
6158 attribute12,
6159 attribute13,
6160 attribute14,
6161 attribute15,
6162 rule_information_category,
6163 rule_information1,
6164 rule_information2,
6165 rule_information3,
6166 rule_information4,
6167 rule_information5,
6168 rule_information6,
6169 rule_information7,
6170 rule_information8,
6171 rule_information9,
6172 rule_information10,
6173 rule_information11,
6174 rule_information12,
6175 rule_information13,
6176 rule_information14,
6177 rule_information15,
6178 template_yn,
6179 ans_set_jtot_object_code,
6180 ans_set_jtot_object_id1,
6181 ans_set_jtot_object_id2,
6182 display_sequence,
6183 --Bug 3055393
6184 comments
6185 FROM okc_rules_b
6186 WHERE dnz_chr_id = p_chr_id;
6187
6188 --------------------------------
6189 -- Versioning TL Table
6190 --------------------------------
6191 /*--Bug 3055393
6192 INSERT INTO okc_rules_tlh
6193 (
6194 major_version,
6195 id,
6196 language,
6197 source_lang,
6198 sfwt_flag,
6199 comments,
6200 text,
6201 created_by,
6202 creation_date,
6203 last_updated_by,
6204 last_update_date,
6205 last_update_login
6206 )
6207 SELECT
6208 p_major_version,
6209 id,
6210 language,
6211 source_lang,
6212 sfwt_flag,
6213 comments,
6214 text,
6215 created_by,
6216 creation_date,
6217 last_updated_by,
6218 last_update_date,
6219 last_update_login
6220 FROM okc_rules_tl
6221 WHERE id in (select id
6222 from okc_rules_b
6223 where dnz_chr_id = p_chr_id);
6224
6225 */
6226
6227 RETURN l_return_status;
6228 EXCEPTION
6229 -- other appropriate handlers
6230 WHEN OTHERS THEN
6231
6232
6233 -- store SQL error message on message stack
6234 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
6235 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
6236 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
6237 p_token1_value => sqlcode,
6238 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
6239 p_token2_value => sqlerrm);
6240
6241 -- notify UNEXPECTED error
6242 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
6243 return l_return_status;
6244 END create_version;
6245
6246 --This Function is called from Versioning API OKC_VERSION_PVT
6247 --Old Location:OKCRVERB.pls
6248 --New Location:Base Table API
6249
6250 FUNCTION restore_version(
6251 p_chr_id IN NUMBER,
6252 p_major_version IN NUMBER
6253 ) RETURN VARCHAR2 IS
6254
6255 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
6256
6257 --
6258 l_proc varchar2(72) := g_package||'restore_version';
6259 --
6260 BEGIN
6261
6262
6263 /*--Bug 3055393
6264
6265 INSERT INTO okc_rules_tl
6266 (
6267 id,
6268 language,
6269 source_lang,
6270 sfwt_flag,
6271 comments,
6272 text,
6273 created_by,
6274 creation_date,
6275 last_updated_by,
6276 last_update_date,
6277 last_update_login
6278 )
6279 SELECT
6280 id,
6281 language,
6282 source_lang,
6283 sfwt_flag,
6284 comments,
6285 text,
6286 created_by,
6287 creation_date,
6288 last_updated_by,
6289 last_update_date,
6290 last_update_login
6291 FROM okc_rules_tlh
6292 WHERE id in (SELECT id
6293 FROM okc_rules_bh
6294 WHERE dnz_chr_id = p_chr_id)
6295 AND major_version = p_major_version;
6296 */
6297 -----------------------------------------
6298 -- Restoring Base Table
6299 -----------------------------------------
6300
6301 INSERT INTO okc_rules_b
6302 (
6303 id,
6304 rgp_id,
6305 object1_id1,
6306 object2_id1,
6307 object3_id1,
6308 object1_id2,
6309 object2_id2,
6310 object3_id2,
6311 jtot_object1_code,
6312 jtot_object2_code,
6313 jtot_object3_code,
6314 dnz_chr_id,
6315 std_template_yn,
6316 warn_yn,
6317 priority,
6318 object_version_number,
6319 created_by,
6320 creation_date,
6321 last_updated_by,
6322 last_update_date,
6323 last_update_login,
6324 attribute_category,
6325 attribute1,
6326 attribute2,
6327 attribute3,
6328 attribute4,
6329 attribute5,
6330 attribute6,
6331 attribute7,
6332 attribute8,
6333 attribute9,
6334 attribute10,
6335 attribute11,
6336 attribute12,
6337 attribute13,
6338 attribute14,
6339 attribute15,
6340 rule_information_category,
6341 rule_information1,
6342 rule_information2,
6343 rule_information3,
6344 rule_information4,
6345 rule_information5,
6346 rule_information6,
6347 rule_information7,
6348 rule_information8,
6349 rule_information9,
6350 rule_information10,
6351 rule_information11,
6352 rule_information12,
6353 rule_information13,
6354 rule_information14,
6355 rule_information15,
6356 template_yn,
6357 ans_set_jtot_object_code,
6358 ans_set_jtot_object_id1,
6359 ans_set_jtot_object_id2,
6360 display_sequence,
6361 comments
6362 )
6363 SELECT
6364 id,
6365 rgp_id,
6366 object1_id1,
6367 object2_id1,
6368 object3_id1,
6369 object1_id2,
6370 object2_id2,
6371 object3_id2,
6372 jtot_object1_code,
6373 jtot_object2_code,
6374 jtot_object3_code,
6375 dnz_chr_id,
6376 std_template_yn,
6377 warn_yn,
6378 priority,
6379 object_version_number,
6380 created_by,
6381 creation_date,
6382 last_updated_by,
6383 last_update_date,
6384 last_update_login,
6385 attribute_category,
6386 attribute1,
6387 attribute2,
6388 attribute3,
6389 attribute4,
6390 attribute5,
6391 attribute6,
6392 attribute7,
6393 attribute8,
6394 attribute9,
6395 attribute10,
6396 attribute11,
6397 attribute12,
6398 attribute13,
6399 attribute14,
6400 attribute15,
6401 rule_information_category,
6402 rule_information1,
6403 rule_information2,
6404 rule_information3,
6405 rule_information4,
6406 rule_information5,
6407 rule_information6,
6408 rule_information7,
6409 rule_information8,
6410 rule_information9,
6411 rule_information10,
6412 rule_information11,
6413 rule_information12,
6414 rule_information13,
6415 rule_information14,
6416 rule_information15,
6417 template_yn,
6418 ans_set_jtot_object_code,
6419 ans_set_jtot_object_id1,
6420 ans_set_jtot_object_id2,
6421 display_sequence,
6422 --Bug 3055393
6423 comments
6424 FROM okc_rules_bh
6425 WHERE dnz_chr_id = p_chr_id
6426 AND major_version = p_major_version;
6427
6428
6429
6430
6431
6432 RETURN l_return_status;
6433 EXCEPTION
6434 -- other appropriate handlers
6435 WHEN OTHERS THEN
6436
6437
6438 -- store SQL error message on message stack
6439 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
6440 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
6441 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
6442 p_token1_value => sqlcode,
6443 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
6444 p_token2_value => sqlerrm);
6445
6446 -- notify UNEXPECTED error
6447 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
6448 return l_return_status;
6449 END restore_version;
6450
6451
6452 PROCEDURE populate_global_tab
6453 (
6454 p_rulv_rec IN rulv_rec_type,
6455 x_return_status OUT NOCOPY VARCHAR2
6456 )
6457 IS
6458
6459 CURSOR csr_tmp (p_context_code IN VARCHAR2) is
6460 SELECT 'Y'
6461 FROM okc_ddf_contextcode_tmp
6462 WHERE descriptive_flex_context_code = p_context_code;
6463
6464 l_found VARCHAR2(10) := 'N';
6465 l_app_col_name VARCHAR2(30) := 'RULE_INFORMATION%';
6466 -- l_desc_ff_name VARCHAR2(30) := 'OKC Rule Developer DF'; -- /striping/
6467
6468 BEGIN
6469
6470 -- initialize return status
6471 x_return_status := OKC_API.G_RET_STS_SUCCESS;
6472 OPEN csr_tmp(p_rulv_rec.rule_information_category);
6473 FETCH csr_tmp into l_found;
6474 CLOSE csr_tmp;
6475 IF l_found = 'Y' THEN
6476 RETURN;
6477 END IF;
6478
6479 -- /striping/
6480 p_appl_id := okc_rld_pvt.get_appl_id(p_rulv_rec.rule_information_category);
6481 p_dff_name := okc_rld_pvt.get_dff_name(p_rulv_rec.rule_information_category);
6482
6483 INSERT INTO okc_ddf_contextcode_tmp
6484 (descriptive_flex_context_code, end_user_column_name, flex_value_set_id,
6485 required_flag, application_column_name, seq_no, form_left_prompt)
6486 SELECT
6487 descriptive_flex_context_code,
6488 end_user_column_name,
6489 flex_value_set_id,
6490 required_flag,
6491 application_column_name,
6492 SUBSTR(application_column_name,17,2) seq_no,
6493 form_left_prompt
6494 FROM fnd_descr_flex_col_usage_vl dfcu
6495 -- WHERE dfcu.descriptive_flexfield_name = l_desc_ff_name -- /striping/
6496 WHERE dfcu.descriptive_flexfield_name = p_dff_name
6497 AND application_column_name like l_app_col_name
6498 -- AND dfcu.application_id =510 -- /striping/
6499 AND dfcu.application_id = p_appl_id
6500 AND dfcu.descriptive_flex_context_code IN
6501 (
6502 SELECT /*+ NO_UNNEST */ rdf_code
6503 FROM OKC_K_HEADERS_B K ,
6504 okc_subclass_rg_defs B,
6505 okc_rg_def_rules A
6506 WHERE A.rgd_code = B.rgd_code
6507 AND B.scs_code = K.scs_code
6508 AND k.id = p_rulv_rec.dnz_chr_id
6509 )
6510 AND NOT EXISTS
6511 (SELECT 1 FROM okc_ddf_contextcode_tmp
6512 WHERE descriptive_flex_context_code =
6513 dfcu.descriptive_flex_context_code
6514 AND application_column_name =
6515 dfcu.application_column_name);
6516
6517 EXCEPTION
6518 WHEN OTHERS THEN
6519 -- store SQL error message on message stack
6520 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
6521 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
6522 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
6523 p_token1_value => sqlcode,
6524 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
6525 p_token2_value => sqlerrm);
6526 -- notify UNEXPECTED error
6527 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
6528 END populate_global_tab;
6529
6530
6531 PROCEDURE populate_obj_global_tab
6532 (
6533 p_rulv_rec IN rulv_rec_type,
6534 x_return_status OUT NOCOPY VARCHAR2
6535 )
6536 IS
6537
6538 CURSOR csr_tmp (p_context_code IN VARCHAR2) is
6539 SELECT 'Y'
6540 FROM okc_obj_ddf_ctxcode_tmp
6541 WHERE descriptive_flex_context_code = p_context_code;
6542
6543 l_found VARCHAR2(10) := 'N';
6544
6545 --
6546 -- Bug 2197451: following variables are defined and used
6547 -- to resolve performance issue - jkodiyan
6548 --
6549 l_app_col_name VARCHAR2(30) := 'JTOT_OBJECT%_CODE';
6550 -- l_desc_ff_name VARCHAR2(30) := 'OKC Rule Developer DF'; -- /striping/
6551
6552 BEGIN
6553
6554 -- initialize return status
6555 x_return_status := OKC_API.G_RET_STS_SUCCESS;
6556
6557 OPEN csr_tmp(p_rulv_rec.rule_information_category);
6558 FETCH csr_tmp into l_found;
6559 CLOSE csr_tmp;
6560 IF l_found = 'Y' THEN
6561 RETURN;
6562 END IF;
6563
6564 -- /striping/
6565 p_appl_id := okc_rld_pvt.get_appl_id(p_rulv_rec.rule_information_category);
6566 p_dff_name := okc_rld_pvt.get_dff_name(p_rulv_rec.rule_information_category);
6567
6568 INSERT INTO okc_obj_ddf_ctxcode_tmp
6569 (descriptive_flex_context_code, dummy_col, seq_no, form_left_prompt)
6570 SELECT
6571 descriptive_flex_context_code ,
6572 'x',
6573 SUBSTR(application_column_name,12,1) seq_no ,
6574 form_left_prompt
6575 FROM fnd_descr_flex_col_usage_vl dfcu
6576 -- WHERE dfcu.descriptive_flexfield_name = l_desc_ff_name -- /striping/
6577 WHERE dfcu.descriptive_flexfield_name = p_dff_name
6578 AND application_column_name like l_app_col_name
6579 -- AND dfcu.application_id =510 -- /striping/
6580 AND dfcu.application_id = p_appl_id
6581 AND dfcu.descriptive_flex_context_code IN
6582 (
6583 SELECT /*+ NO_UNNEST */ rdf_code
6584 FROM OKC_K_HEADERS_B K ,
6585 okc_subclass_rg_defs B,
6586 okc_rg_def_rules A
6587 WHERE A.rgd_code = B.rgd_code
6588 AND B.scs_code = K.scs_code
6589 AND k.id = p_rulv_rec.dnz_chr_id
6590 )
6591 AND NOT EXISTS
6592 (SELECT 1 FROM okc_obj_ddf_ctxcode_tmp
6593 WHERE descriptive_flex_context_code =
6594 dfcu.descriptive_flex_context_code
6595 AND seq_no = SUBSTR(application_column_name,12,1));
6596
6597 EXCEPTION
6598 WHEN OTHERS THEN
6599 -- store SQL error message on message stack
6600 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
6601 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
6602 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
6603 p_token1_value => sqlcode,
6604 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
6605 p_token2_value => sqlerrm);
6606 -- notify UNEXPECTED error
6607 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
6608 END populate_obj_global_tab;
6609
6610 END OKC_RUL_PVT;