[Home] [Help]
PACKAGE BODY: APPS.OKC_RGP_PVT
Source
1 PACKAGE BODY OKC_RGP_PVT AS
2 /* $Header: OKCSRGPB.pls 120.1 2008/02/29 06:05:43 veramach ship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 /*********************** HAND-CODED **************************/
7 FUNCTION Validate_Attributes
8 (p_rgpv_rec IN rgpv_rec_type) RETURN VARCHAR2;
9 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'OKC_NO_PARENT_RECORD';
10 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
11 G_SQLERRM_TOKEn CONSTANT VARCHAR2(200) := 'ERROR_MESSAGE';
12 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'ERROR_CODE';
13 G_VIEW CONSTANT VARCHAR2(200) := 'OKC_RULE_GROUPS_V';
14 G_EXCEPTION_HALT_VALIDATION exception;
15 g_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
16
17 -- Start of comments
18 --
19 -- Procedure Name : validate_rgd_code
20 -- Description :
21 -- Business Rules :
22 -- Parameters :
23 -- Version : 1.0
24 -- End of comments
25
26 PROCEDURE validate_rgd_code(
27 x_return_status OUT NOCOPY VARCHAR2,
28 p_scs_code IN VARCHAR2,
29 p_rgpv_rec IN rgpv_rec_type
30 ) IS
31 l_dummy_var VARCHAR2(1) := '?';
32 l_cnt_rg NUMBER(9) := 0;
33
34 -- Bug 1367397: changed subclass views in cursor to base table
35
36 CURSOR l_rgdv_csr IS
37 SELECT 'x'
38 FROM FND_LOOKUP_VALUES rgdv
39 WHERE (p_rgpv_rec.rgd_code IN
40 (SELECT srdv.RGD_CODE
41 FROM OKC_SUBCLASS_RG_DEFS srdv,
42 OKC_SUBCLASSES_B scsv,
43 OKC_K_HEADERS_B chrv
44 WHERE srdv.SCS_CODE = scsv.CODE
45 AND scsv.CODE = chrv.SCS_CODE
46 AND chrv.ID = p_rgpv_rec.dnz_chr_id) OR
47 (p_rgpv_rec.rgp_type = 'SRG'))
48 AND rgdv.LOOKUP_CODE = p_rgpv_rec.rgd_code
49 AND rgdv.lookup_type = 'OKC_RULE_GROUP_DEF';
50
51 -- The following cursor has been changed by MSENGUPT on 12/08/2001 to use dnz_chr_id instead of chr_id
52
53 CURSOR csr_chr_rg_cnt IS
54 SELECT count('X')
55 FROM okc_rule_groups_b
56 WHERE rgd_code = p_rgpv_rec.rgd_code
57 AND (dnz_chr_id = p_rgpv_rec.chr_id and cle_id IS NULL)
58 AND id <> NVL(p_rgpv_rec.id,-1);
59
60 CURSOR csr_cle_rg_cnt IS
61 SELECT count('X')
62 FROM okc_rule_groups_b
63 WHERE rgd_code = p_rgpv_rec.rgd_code
64 AND (dnz_chr_id = p_rgpv_rec.dnz_chr_id and cle_id = p_rgpv_rec.cle_id)
65 AND id <> NVL(p_rgpv_rec.id,-1);
66
67 CURSOR csr_rg_subject_object (p_scs_code IN VARCHAR2, p_rgd_code IN VARCHAR2) IS
68 select 'x' from okc_rg_role_defs rrd,
69 okc_subclass_rg_defs srd
70 where srd_id = srd.id
71 and scs_code = p_scs_code
72 and rgd_code = p_rgd_code
73 and rownum < 2;
74
75
76 BEGIN
77 -- initialize return status
78 x_return_status := OKC_API.G_RET_STS_SUCCESS;
79
80 -- data is required
81 IF (p_rgpv_rec.rgd_code = OKC_API.G_MISS_CHAR OR
82 p_rgpv_rec.rgd_code IS NULL) THEN
83 OKC_API.set_message(
84 p_app_name => G_APP_NAME,
85 p_msg_name => G_REQUIRED_VALUE,
86 p_token1 => G_COL_NAME_TOKEN,
87 p_token1_value => 'rgd_code');
88
89 -- notify caller of an error
90 x_return_status := OKC_API.G_RET_STS_ERROR;
91
92 -- halt validation
93 RAISE G_EXCEPTION_HALT_VALIDATION;
94 END IF;
95
96 -- enforce foreign key
97 OPEN l_rgdv_csr;
98 FETCH l_rgdv_csr INTO l_dummy_var;
99 CLOSE l_rgdv_csr;
100
101 -- if l_dummy_var still set to default, data was not found
102 IF (l_dummy_var = '?') THEN
103 --set error message in message stack
104 OKC_API.set_message(
105 p_app_name => G_APP_NAME,
106 p_msg_name => G_INVALID_VALUE,
107 p_token1 => G_COL_NAME_TOKEN,
108 p_token1_value => 'rgd_code');
109
110 -- notify caller of an error
111 x_return_status := OKC_API.G_RET_STS_ERROR;
112 END IF;
113
114 -- bug#2241759: Added by Msengupt to bypass check of single occurence of rule group for std articles or
115 -- if the subject object exists
116
117 IF (p_rgpv_rec.rgp_type = 'SRG') THEN
118 return;
119 END IF;
120 l_dummy_var := '?';
121 OPEN csr_rg_subject_object (p_scs_code, p_rgpv_rec.rgd_code);
122 FETCH csr_rg_subject_object INTO l_dummy_var;
123 CLOSE csr_rg_subject_object;
124 IF l_dummy_var = 'x' THEN
125 return;
126 END IF;
127
128 -- end bug#2241759:
129
130 -- Same Rule Group can be attched only 1 time to k-header
131 IF p_rgpv_rec.chr_id IS NOT NULL THEN
132 OPEN csr_chr_rg_cnt;
133 FETCH csr_chr_rg_cnt INTO l_cnt_rg;
134 CLOSE csr_chr_rg_cnt;
135
136 IF l_cnt_rg <> 0 THEN
137 --set error message
138 OKC_API.set_message(
139 p_app_name => G_APP_NAME,
140 p_msg_name => 'OKC_DUP_RG_KH',
141 p_token1 => 'RULEGROUP',
142 p_token1_value => p_rgpv_rec.rgd_code);
143
144 -- notify caller of an error
145 x_return_status := OKC_API.G_RET_STS_ERROR;
146
147 -- halt validation
148 RAISE G_EXCEPTION_HALT_VALIDATION;
149 END IF;
150 END IF;
151 IF p_rgpv_rec.cle_id IS NOT NULL THEN
152 OPEN csr_cle_rg_cnt;
153 FETCH csr_cle_rg_cnt INTO l_cnt_rg;
154 CLOSE csr_cle_rg_cnt;
155
156 IF l_cnt_rg <> 0 THEN
157 --set error message
158 OKC_API.set_message(
159 p_app_name => G_APP_NAME,
160 p_msg_name => 'OKC_DUP_RG_KH',
161 p_token1 => 'RULEGROUP',
162 p_token1_value => p_rgpv_rec.rgd_code);
163
164 -- notify caller of an error
165 x_return_status := OKC_API.G_RET_STS_ERROR;
166
167 -- halt validation
168 RAISE G_EXCEPTION_HALT_VALIDATION;
169 END IF;
170 END IF;
171
172
173
174
175 EXCEPTION
176 WHEN G_EXCEPTION_HALT_VALIDATION THEN
177 -- no processing necessary; validation can continue with next column
178 NULL;
179 WHEN OTHERS THEN
180 -- store SQL error message on message stack
181 OKC_API.SET_MESSAGE(
182 p_app_name => g_app_name,
183 p_msg_name => g_unexpected_error,
184 p_token1 => g_sqlcode_token,
185 p_token1_value => sqlcode,
186 p_token2 => g_sqlerrm_token,
187 p_token2_value => sqlerrm);
188 -- notify caller of an error as UNEXPETED error
189 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
190 -- verify that cursor was closed
191 IF l_rgdv_csr%ISOPEN THEN
192 CLOSE l_rgdv_csr;
193 END IF;
194 END validate_rgd_code;
195 --
196 -- Start of comments
197 --
198 -- Procedure Name : validate_sat_code
199 -- Description :
200 -- Business Rules :
201 -- Parameters :
202 -- Version : 1.0
203 -- End of comments
204
205 PROCEDURE validate_sat_code(
206 x_return_status OUT NOCOPY VARCHAR2,
207 p_rgpv_rec IN rgpv_rec_type
208 ) IS
209 l_dummy_var VARCHAR2(1) := '?';
210 CURSOR l_satv_csr IS
211 SELECT 'x'
212 FROM FND_LOOKUP_VALUES satv
213 WHERE satv.LOOKUP_CODE = p_rgpv_rec.sat_code
214 AND satv.lookup_type = 'OKC_ARTICLE_SET';
215 BEGIN
216 -- initialize return status
217 x_return_status := OKC_API.G_RET_STS_SUCCESS;
218
219 -- data is required for subtype SRG only
220 IF (p_rgpv_rec.rgp_type = 'SRG') THEN
221 -- data is required
222 IF (p_rgpv_rec.sat_code = OKC_API.G_MISS_CHAR OR
223 p_rgpv_rec.sat_code IS NULL) THEN
224 OKC_API.set_message(
225 p_app_name => G_APP_NAME,
226 p_msg_name => G_REQUIRED_VALUE,
227 p_token1 => G_COL_NAME_TOKEN,
228 p_token1_value => 'rgd_code');
229
230 -- notify caller of an error
231 x_return_status := OKC_API.G_RET_STS_ERROR;
232
233 -- halt validation
234 RAISE G_EXCEPTION_HALT_VALIDATION;
235 END IF;
236
237 -- enforce foreign key
238 OPEN l_satv_csr;
239 FETCH l_satv_csr INTO l_dummy_var;
240 CLOSE l_satv_csr;
241
242 -- if l_dummy_var still set to default, data was not found
243 IF (l_dummy_var = '?') THEN
244 --set error message in message stack
245 OKC_API.set_message(
246 p_app_name => G_APP_NAME,
247 p_msg_name => G_INVALID_VALUE,
248 p_token1 => G_COL_NAME_TOKEN,
249 p_token1_value => 'rgd_code');
250
251 -- notify caller of an error
252 x_return_status := OKC_API.G_RET_STS_ERROR;
253 END IF;
254 ELSE
255 -- sat_code must be null
256 IF (p_rgpv_rec.sat_code <> OKC_API.G_MISS_CHAR OR
257 p_rgpv_rec.sat_code IS NOT NULL) THEN
258
259 OKC_API.set_message(
260 p_app_name => G_APP_NAME,
261 p_msg_name => G_INVALID_VALUE,
262 p_token1 => G_COL_NAME_TOKEN,
263 p_token1_value => 'sat_code');
264
265 -- notify caller of an error
266 x_return_status := OKC_API.G_RET_STS_ERROR;
267 END IF;
268 END IF;
269 EXCEPTION
270 WHEN G_EXCEPTION_HALT_VALIDATION THEN
271 -- no processing necessary; validation can continue with next column
272 NULL;
273 WHEN OTHERS THEN
274 -- store SQL error message on message stack
275 OKC_API.SET_MESSAGE(
276 p_app_name => g_app_name,
277 p_msg_name => g_unexpected_error,
278 p_token1 => g_sqlcode_token,
279 p_token1_value => sqlcode,
280 p_token2 => g_sqlerrm_token,
281 p_token2_value => sqlerrm);
282 -- notify caller of an error as UNEXPETED error
283 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
284 -- verify that cursor was closed
285 IF l_satv_csr%ISOPEN THEN
286 CLOSE l_satv_csr;
287 END IF;
288 END validate_sat_code;
289 --
290 -- Start of comments
291 --
292 -- Procedure Name : validate_rgp_type
293 -- Description :
294 -- Business Rules :
295 -- Parameters :
296 -- Version : 1.0
297 -- End of comments
298
299 PROCEDURE validate_rgp_type(
300 x_return_status OUT NOCOPY VARCHAR2,
301 p_rgpv_rec IN rgpv_rec_type
302 ) IS
303 BEGIN
304 -- initialize return status
305 x_return_status := OKC_API.G_RET_STS_SUCCESS;
306
307 -- data is required
308 IF (p_rgpv_rec.rgp_type = OKC_API.G_MISS_CHAR OR
309 p_rgpv_rec.rgp_type IS NULL) THEN
310 OKC_API.set_message(
311 p_app_name => G_APP_NAME,
312 p_msg_name => G_REQUIRED_VALUE,
313 p_token1 => G_COL_NAME_TOKEN,
314 p_token1_value => 'rgp_type');
315
316 -- notify caller of an error
317 x_return_status := OKC_API.G_RET_STS_ERROR;
318
319 -- halt validation
320 RAISE G_EXCEPTION_HALT_VALIDATION;
321 END IF;
322
323 -- check allowed values
324 IF (UPPER(p_rgpv_rec.rgp_type) NOT IN ('SRG','KRG')) THEN
325 OKC_API.set_message(
326 p_app_name => G_APP_NAME,
327 p_msg_name => G_INVALID_VALUE,
328 p_token1 => G_COL_NAME_TOKEN,
329 p_token1_value => 'rgp_type');
330
331 -- notify caller of an error
332 x_return_status := OKC_API.G_RET_STS_ERROR;
333 END IF;
334 EXCEPTION
335 WHEN G_EXCEPTION_HALT_VALIDATION THEN
336 -- no processing necessary; validation can continue with next column
337 NULL;
338 WHEN OTHERS THEN
339 -- store SQL error message on message stack
340 OKC_API.SET_MESSAGE(
341 p_app_name => G_APP_NAME,
342 p_msg_name => G_UNEXPECTED_ERROR,
343 p_token1 => G_SQLCODE_TOKEN,
344 p_token1_value => SQLCODE,
345 p_token2 => G_SQLERRM_TOKEN,
346 p_token2_value => SQLERRM);
347 -- notify caller of an error as UNEXPETED error
348 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
349 -- verify that cursor was closed
350 END validate_rgp_type;
351 --
352 -- Start of comments
353 --
354 -- Procedure Name : validate_cle_id
355 -- Description :
356 -- Business Rules :
357 -- Parameters :
358 -- Version : 1.0
359 -- End of comments
360
361 PROCEDURE validate_cle_id(
362 x_return_status OUT NOCOPY VARCHAR2,
363 p_rgpv_rec IN rgpv_rec_type
364 ) IS
365 l_dummy_var VARCHAR2(1) := '?';
366 CURSOR l_clev_csr IS
367 SELECT 'x'
368 FROM OKC_K_LINES_B clev
369 WHERE clev.ID = p_rgpv_rec.CLE_ID;
370 BEGIN
371 -- initialize return status
372 x_return_status := OKC_API.G_RET_STS_SUCCESS;
373
374 -- data is allowed for subtype KRG only
375 IF (p_rgpv_rec.rgp_type = 'KRG') THEN
376 -- cle id or chr id is required
377 IF ((p_rgpv_rec.cle_id = OKC_API.G_MISS_NUM OR
378 p_rgpv_rec.cle_id IS NULL) AND
379 (p_rgpv_rec.chr_id = OKC_API.G_MISS_NUM OR
380 p_rgpv_rec.chr_id IS NULL)) THEN
381
382 OKC_API.set_message(
383 p_app_name => G_APP_NAME,
384 p_msg_name => G_REQUIRED_VALUE,
385 p_token1 => G_COL_NAME_TOKEN,
386 p_token1_value => 'chr_id or cle_id');
387
388 -- notify caller of an error
389 x_return_status := OKC_API.G_RET_STS_ERROR;
390
391 -- halt validation
392 RAISE G_EXCEPTION_HALT_VALIDATION;
393 -- cle id or chr id can only be allowed, not both
394 ELSIF ((p_rgpv_rec.cle_id <> OKC_API.G_MISS_NUM OR
395 p_rgpv_rec.cle_id IS NOT NULL) AND
396 (p_rgpv_rec.chr_id <> OKC_API.G_MISS_NUM OR
397 p_rgpv_rec.chr_id IS NOT NULL)) THEN
398
399 OKC_API.set_message(
400 p_app_name => G_APP_NAME,
401 p_msg_name => G_INVALID_VALUE,
402 p_token1 => G_COL_NAME_TOKEN,
403 p_token1_value => 'chr_id and cle_id');
404
405 -- notify caller of an error
406 x_return_status := OKC_API.G_RET_STS_ERROR;
407
408 -- halt validation
409 RAISE G_EXCEPTION_HALT_VALIDATION;
410 END IF;
411 ELSE
412 -- must be null
413 IF (p_rgpv_rec.cle_id <> OKC_API.G_MISS_NUM OR
414 p_rgpv_rec.cle_id IS NOT NULL) THEN
415
416 OKC_API.set_message(
417 p_app_name => G_APP_NAME,
418 p_msg_name => G_INVALID_VALUE,
419 p_token1 => G_COL_NAME_TOKEN,
420 p_token1_value => 'cle_id');
421
422 -- notify caller of an error
423 x_return_status := OKC_API.G_RET_STS_ERROR;
424
425 -- halt validation
426 RAISE G_EXCEPTION_HALT_VALIDATION;
427 END IF;
428 END IF;
429
430 -- check for data
431 IF (p_rgpv_rec.cle_id <> OKC_API.G_MISS_NUM OR
432 p_rgpv_rec.cle_id IS NOT NULL) THEN
433
434 -- enforce foreign key
435 OPEN l_clev_csr;
436 FETCH l_clev_csr INTO l_dummy_var;
437 CLOSE l_clev_csr;
438
439 -- if l_dummy_var still set to default, data was not found
440 IF (l_dummy_var = '?') THEN
441 OKC_API.set_message(
442 p_app_name => G_APP_NAME,
443 p_msg_name => G_NO_PARENT_RECORD,
444 p_token1 => G_COL_NAME_TOKEN,
445 p_token1_value => 'cle_id',
446 p_token2 => G_CHILD_TABLE_TOKEN,
447 p_token2_value => G_VIEW,
448 p_token3 => G_PARENT_TABLE_TOKEN,
449 p_token3_value => 'OKC_CONTRACT_LINES_V');
450 -- notify caller of an error
451 x_return_status := OKC_API.G_RET_STS_ERROR;
452 END IF;
453 END IF;
454 EXCEPTION
455 WHEN G_EXCEPTION_HALT_VALIDATION THEN
456 -- no processing necessary; validation can continue with next column
457 NULL;
458 WHEN OTHERS THEN
459 -- store SQL error message on message stack
460 OKC_API.SET_MESSAGE(
461 p_app_name => g_app_name,
462 p_msg_name => g_unexpected_error,
463 p_token1 => g_sqlcode_token,
464 p_token1_value => sqlcode,
465 p_token2 => g_sqlerrm_token,
466 p_token2_value => sqlerrm);
467 -- notify caller of an error as UNEXPETED error
468 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
469 -- verify that cursor was closed
470 IF l_clev_csr%ISOPEN THEN
471 CLOSE l_clev_csr;
472 END IF;
473 END validate_cle_id;
474 --
475 -- Start of comments
476 --
477 -- Procedure Name : validate_chr_id
478 -- Description :
479 -- Business Rules :
480 -- Parameters :
481 -- Version : 1.0
482 -- End of comments
483
484 PROCEDURE validate_chr_id(
485 x_return_status OUT NOCOPY VARCHAR2,
486 p_rgpv_rec IN rgpv_rec_type
487 ) IS
488 l_dummy_var VARCHAR2(1) := '?';
489 CURSOR l_chrv_csr IS
490 SELECT 'x'
491 FROM OKC_K_HEADERS_B chrv
492 WHERE chrv.ID = p_rgpv_rec.CHR_ID;
493 BEGIN
494 -- initialize return status
495 x_return_status := OKC_API.G_RET_STS_SUCCESS;
496
497 -- data is allowed for subtype KRG only
498 IF (p_rgpv_rec.rgp_type = 'SRG') THEN
499 -- must be null
500 IF (p_rgpv_rec.cle_id <> OKC_API.G_MISS_NUM OR
501 p_rgpv_rec.cle_id IS NOT NULL) THEN
502
503 OKC_API.set_message(
504 p_app_name => G_APP_NAME,
505 p_msg_name => G_INVALID_VALUE,
506 p_token1 => G_COL_NAME_TOKEN,
507 p_token1_value => 'chr_id');
508
509 -- notify caller of an error
510 x_return_status := OKC_API.G_RET_STS_ERROR;
511
512 -- halt validation
513 RAISE G_EXCEPTION_HALT_VALIDATION;
514 END IF;
515 END IF;
516
517 -- check for data
518 IF (p_rgpv_rec.chr_id <> OKC_API.G_MISS_NUM OR
519 p_rgpv_rec.chr_id IS NOT NULL) THEN
520
521 -- enforce foreign key
522 OPEN l_chrv_csr;
523 FETCH l_chrv_csr INTO l_dummy_var;
524 CLOSE l_chrv_csr;
525
526 -- if l_dummy_var still set to default, data was not found
527 IF (l_dummy_var = '?') THEN
528 OKC_API.set_message(
529 p_app_name => G_APP_NAME,
530 p_msg_name => G_NO_PARENT_RECORD,
531 p_token1 => G_COL_NAME_TOKEN,
532 p_token1_value => 'chr_id',
533 p_token2 => G_CHILD_TABLE_TOKEN,
534 p_token2_value => G_VIEW,
535 p_token3 => G_PARENT_TABLE_TOKEN,
536 p_token3_value => 'OKC_CONTRACT_HEADERS_V');
537 -- notify caller of an error
538 x_return_status := OKC_API.G_RET_STS_ERROR;
539 END IF;
540 END IF;
541 EXCEPTION
542 WHEN G_EXCEPTION_HALT_VALIDATION THEN
543 -- no processing necessary; validation can continue with next column
544 NULL;
545 WHEN OTHERS THEN
546 -- store SQL error message on message stack
547 OKC_API.SET_MESSAGE(
548 p_app_name => g_app_name,
549 p_msg_name => g_unexpected_error,
550 p_token1 => g_sqlcode_token,
551 p_token1_value => sqlcode,
552 p_token2 => g_sqlerrm_token,
553 p_token2_value => sqlerrm);
554 -- notify caller of an error as UNEXPETED error
555 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
556 -- verify that cursor was closed
557 IF l_chrv_csr%ISOPEN THEN
558 CLOSE l_chrv_csr;
559 END IF;
560 END validate_chr_id;
561 --
562 -- Start of comments
563 --
564 -- Procedure Name : validate_parent_rgp_id
565 -- Description :
566 -- Business Rules :
567 -- Parameters :
568 -- Version : 1.0
569 -- End of comments
570
571 PROCEDURE validate_parent_rgp_id(
572 x_return_status OUT NOCOPY VARCHAR2,
573 p_rgpv_rec IN rgpv_rec_type
574 ) IS
575 l_dummy_var VARCHAR2(1) := '?';
576 CURSOR l_rgpv_csr IS
577 SELECT 'x'
578 FROM OKC_RULE_GROUPS_B rgpv
579 WHERE rgpv.ID = p_rgpv_rec.PARENT_RGP_ID;
580 BEGIN
581 -- initialize return status
582 x_return_status := OKC_API.G_RET_STS_SUCCESS;
583
584 -- data is allowed for subtype KRG only
585 IF (p_rgpv_rec.rgp_type = 'SRG') THEN
586 -- must be null
587 IF (p_rgpv_rec.cle_id <> OKC_API.G_MISS_NUM OR
588 p_rgpv_rec.cle_id IS NOT NULL) THEN
589
590 OKC_API.set_message(
591 p_app_name => G_APP_NAME,
592 p_msg_name => G_INVALID_VALUE,
593 p_token1 => G_COL_NAME_TOKEN,
594 p_token1_value => 'parent_rgp_id');
595
596 -- notify caller of an error
597 x_return_status := OKC_API.G_RET_STS_ERROR;
598
599 -- halt validation
600 RAISE G_EXCEPTION_HALT_VALIDATION;
601 END IF;
602 END IF;
603
604 -- check for data
605 IF (p_rgpv_rec.parent_rgp_id <> OKC_API.G_MISS_NUM OR
606 p_rgpv_rec.parent_rgp_id IS NOT NULL) THEN
607
608 -- enforce foreign key
609 OPEN l_rgpv_csr;
610 FETCH l_rgpv_csr INTO l_dummy_var;
611 CLOSE l_rgpv_csr;
612
613 -- if l_dummy_var still set to default, data was not found
614 IF (l_dummy_var = '?') THEN
615 OKC_API.set_message(
616 p_app_name => G_APP_NAME,
617 p_msg_name => G_NO_PARENT_RECORD,
618 p_token1 => G_COL_NAME_TOKEN,
619 p_token1_value => 'parent_rgp_id',
620 p_token2 => G_CHILD_TABLE_TOKEN,
621 p_token2_value => G_VIEW,
622 p_token3 => G_PARENT_TABLE_TOKEN,
623 p_token3_value => 'OKC_RULE_GROUPS_V');
624 -- notify caller of an error
625 x_return_status := OKC_API.G_RET_STS_ERROR;
626 END IF;
627 END IF;
628 EXCEPTION
629 WHEN G_EXCEPTION_HALT_VALIDATION THEN
630 -- no processing necessary; validation can continue with next column
631 NULL;
632 WHEN OTHERS THEN
633 -- store SQL error message on message stack
634 OKC_API.SET_MESSAGE(
635 p_app_name => g_app_name,
636 p_msg_name => g_unexpected_error,
637 p_token1 => g_sqlcode_token,
638 p_token1_value => sqlcode,
639 p_token2 => g_sqlerrm_token,
640 p_token2_value => sqlerrm);
641 -- notify caller of an error as UNEXPETED error
642 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
643 -- verify that cursor was closed
644 IF l_rgpv_csr%ISOPEN THEN
645 CLOSE l_rgpv_csr;
646 END IF;
647 END validate_parent_rgp_id;
648 --
649 -- Start of comments
650 --
651 -- Procedure Name : validate_dnz_chr_id
652 -- Description :
653 -- Business Rules :
654 -- Parameters :
655 -- Version : 1.0
656 -- End of comments
657
658 PROCEDURE validate_dnz_chr_id(
659 x_return_status OUT NOCOPY VARCHAR2,
660 x_scs_code OUT NOCOPY VARCHAR2,
661 p_rgpv_rec IN rgpv_rec_type
662 ) IS
663 l_scs_code VARCHAR2(30) := NULL;
664 CURSOR l_chrv_csr IS
665 SELECT scs_code
666 FROM OKC_K_HEADERS_B chrv
667 WHERE chrv.ID = p_rgpv_rec.DNZ_CHR_ID;
668 BEGIN
669 -- initialize return status
670 x_return_status := OKC_API.G_RET_STS_SUCCESS;
671
672 -- data is allowed for subtype KRG only
673 IF (p_rgpv_rec.rgp_type = 'SRG') THEN
674 -- must be null
675 IF (p_rgpv_rec.dnz_chr_id <> OKC_API.G_MISS_NUM OR
676 p_rgpv_rec.dnz_chr_id IS NOT NULL) THEN
677
678 OKC_API.set_message(
679 p_app_name => G_APP_NAME,
680 p_msg_name => G_INVALID_VALUE,
681 p_token1 => G_COL_NAME_TOKEN,
682 p_token1_value => 'dnz_chr_id');
683
684 -- notify caller of an error
685 x_return_status := OKC_API.G_RET_STS_ERROR;
686
687 -- halt validation
688 RAISE G_EXCEPTION_HALT_VALIDATION;
689 END IF;
690 ELSE
691 -- data required
692 IF (p_rgpv_rec.dnz_chr_id = OKC_API.G_MISS_NUM OR
693 p_rgpv_rec.dnz_chr_id IS NULL) THEN
694
695 OKC_API.set_message(
696 p_app_name => G_APP_NAME,
697 p_msg_name => G_REQUIRED_VALUE,
698 p_token1 => G_COL_NAME_TOKEN,
699 p_token1_value => 'dnz_chr_id');
700
701 -- notify caller of an error
702 x_return_status := OKC_API.G_RET_STS_ERROR;
703
704 -- halt validation
705 RAISE G_EXCEPTION_HALT_VALIDATION;
706 END IF;
707
708 -- enforce foreign key
709 OPEN l_chrv_csr;
710 FETCH l_chrv_csr INTO l_scs_code;
711 CLOSE l_chrv_csr;
712 x_scs_code := l_scs_code;
713 -- if l_dummy_var still set to default, data was not found
714 IF (l_scs_code is NULL) THEN
715 OKC_API.set_message(
716 p_app_name => G_APP_NAME,
717 p_msg_name => G_NO_PARENT_RECORD,
718 p_token1 => G_COL_NAME_TOKEN,
719 p_token1_value => 'dnz_chr_id',
720 p_token2 => G_CHILD_TABLE_TOKEN,
721 p_token2_value => G_VIEW,
722 p_token3 => G_PARENT_TABLE_TOKEN,
723 p_token3_value => 'OKC_K_HEADERS_V');
724
725 -- notify caller of an error
726 x_return_status := OKC_API.G_RET_STS_ERROR;
727 END IF;
728 END IF;
729 EXCEPTION
730 WHEN G_EXCEPTION_HALT_VALIDATION THEN
731 -- no processing necessary; validation can continue with next column
732 NULL;
733 WHEN OTHERS THEN
734 -- store SQL error message on message stack
735 OKC_API.SET_MESSAGE(
736 p_app_name => g_app_name,
737 p_msg_name => g_unexpected_error,
738 p_token1 => g_sqlcode_token,
739 p_token1_value => sqlcode,
740 p_token2 => g_sqlerrm_token,
741 p_token2_value => sqlerrm);
742 -- notify caller of an error as UNEXPETED error
743 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
744 -- verify that cursor was closed
745 IF l_chrv_csr%ISOPEN THEN
746 CLOSE l_chrv_csr;
747 END IF;
748 END validate_dnz_chr_id;
749 --
750 -- Start of comments
751 --
752 -- Procedure Name : validate_attributes
753 -- Description :
754 -- Business Rules :
755 -- Parameters :
756 -- Version : 1.0
757 -- End of comments
758
759 FUNCTION Validate_Attributes (
760 p_rgpv_rec IN rgpv_rec_type
761 ) RETURN VARCHAR2 IS
762 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
763 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
764 l_scs_code VARCHAR2(50);
765 BEGIN
766 -- call each column-level validation
767
768 validate_sat_code(
769 x_return_status => l_return_status,
770 p_rgpv_rec => p_rgpv_rec);
771
772 -- store the highest degree of error
773 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
774 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
775 x_return_status := l_return_status;
776 END IF;
777 END IF;
778 --
779 validate_rgp_type(
780 x_return_status => l_return_status,
781 p_rgpv_rec => p_rgpv_rec);
782
783 -- store the highest degree of error
784 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
785 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
786 x_return_status := l_return_status;
787 END IF;
788 END IF;
789 --
790 validate_cle_id(
791 x_return_status => l_return_status,
792 p_rgpv_rec => p_rgpv_rec);
793
794 -- store the highest degree of error
795 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
796 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
797 x_return_status := l_return_status;
798 END IF;
799 END IF;
800 --
801 validate_dnz_chr_id(
802 x_return_status => l_return_status,
803 x_scs_code => l_scs_code,
804 p_rgpv_rec => p_rgpv_rec);
805
806 -- store the highest degree of error
807 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
808 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
809 x_return_status := l_return_status;
810 END IF;
811 END IF;
812 --
813 validate_rgd_code(
814 x_return_status => l_return_status,
815 p_scs_code => l_scs_code,
816 p_rgpv_rec => p_rgpv_rec);
817
818 -- store the highest degree of error
819 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
820 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
821 x_return_status := l_return_status;
822 END IF;
823 END IF;
824 --
825 validate_chr_id(
826 x_return_status => l_return_status,
827 p_rgpv_rec => p_rgpv_rec);
828
829 -- store the highest degree of error
830 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
831 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
832 x_return_status := l_return_status;
833 END IF;
834 END IF;
835 --
836 validate_parent_rgp_id(
837 x_return_status => l_return_status,
838 p_rgpv_rec => p_rgpv_rec);
839
840 -- store the highest degree of error
841 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
842 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
843 x_return_status := l_return_status;
844 END IF;
845 END IF;
846 -- return status to caller
847 RETURN(x_return_status);
848
849 EXCEPTION
850 WHEN OTHERS THEN
851 -- store SQL error message on message stack for caller
852 OKC_API.SET_MESSAGE
853 (p_app_name => G_APP_NAME,
854 p_msg_name => G_UNEXPECTED_ERROR,
855 p_token1 => G_SQLCODE_TOKEN,
856 p_token1_value => SQLCODE,
857 p_token2 => G_SQLERRM_TOKEN,
858 p_token2_value => SQLERRM);
859
860 -- notify caller of an UNEXPECTED error
861 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
862
863 -- return status to caller
864 RETURN x_return_status;
865
866 END Validate_Attributes;
867
868 /*********************** END HAND-CODED **************************/
869
870 ---------------------------------------------------------------------------
871 -- FUNCTION get_seq_id
872 ---------------------------------------------------------------------------
873 FUNCTION get_seq_id RETURN NUMBER IS
874 BEGIN
875 RETURN(okc_p_util.raw_to_number(sys_guid()));
876 END get_seq_id;
877
878 ---------------------------------------------------------------------------
879 -- PROCEDURE qc
880 ---------------------------------------------------------------------------
881 PROCEDURE qc IS
882 BEGIN
883 null;
884 END qc;
885
886 ---------------------------------------------------------------------------
887 -- PROCEDURE change_version
888 ---------------------------------------------------------------------------
889 PROCEDURE change_version IS
890 BEGIN
891 null;
892 END change_version;
893
894 ---------------------------------------------------------------------------
895 -- PROCEDURE api_copy
896 ---------------------------------------------------------------------------
897 PROCEDURE api_copy IS
898 BEGIN
899 null;
900 END api_copy;
901
902 ---------------------------------------------------------------------------
903 -- PROCEDURE add_language
904 ---------------------------------------------------------------------------
905 PROCEDURE add_language IS
906 BEGIN
907 /* Mar/19/03 requested by Ric Ginsberg */
908 /* The following delete and update statements are commented out */
909 /* as a quick workaround to fix the time-consuming table handler issue */
910 /* Eventually we'll need to turn them into a separate fix_language procedure */
911
912 DELETE FROM OKC_RULE_GROUPS_TL T
913 WHERE NOT EXISTS (
914 SELECT NULL
915 FROM OKC_RULE_GROUPS_B B
916 WHERE B.ID = T.ID
917 AND T.LANGUAGE = USERENV('LANG')
918 );
919
920 UPDATE OKC_RULE_GROUPS_TL T SET (
921 COMMENTS) = (SELECT
922 B.COMMENTS
923 FROM OKC_RULE_GROUPS_TL B
924 WHERE B.ID = T.ID
925 AND B.LANGUAGE = T.SOURCE_LANG)
926 WHERE (
927 T.ID,
928 T.LANGUAGE)
929 IN (SELECT
930 SUBT.ID,
931 SUBT.LANGUAGE
932 FROM OKC_RULE_GROUPS_TL SUBB, OKC_RULE_GROUPS_TL SUBT
933 WHERE SUBB.ID = SUBT.ID
934 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
935 AND (SUBB.COMMENTS <> SUBT.COMMENTS
936 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
937 OR (SUBB.COMMENTS IS NOT NULL AND SUBT.COMMENTS IS NULL)
938 ));
939
940 INSERT INTO OKC_RULE_GROUPS_TL (
941 ID,
942 LANGUAGE,
943 SOURCE_LANG,
944 SFWT_FLAG,
945 COMMENTS,
946 CREATED_BY,
947 CREATION_DATE,
948 LAST_UPDATED_BY,
949 LAST_UPDATE_DATE,
950 LAST_UPDATE_LOGIN)
951 SELECT
952 B.ID,
953 L.LANGUAGE_CODE,
954 B.SOURCE_LANG,
955 B.SFWT_FLAG,
956 B.COMMENTS,
957 B.CREATED_BY,
958 B.CREATION_DATE,
959 B.LAST_UPDATED_BY,
960 B.LAST_UPDATE_DATE,
961 B.LAST_UPDATE_LOGIN
962 FROM OKC_RULE_GROUPS_TL B, FND_LANGUAGES L
963 WHERE L.INSTALLED_FLAG IN ('I', 'B')
964 AND B.LANGUAGE = USERENV('LANG')
965 AND NOT EXISTS(
966 SELECT NULL
967 FROM OKC_RULE_GROUPS_TL T
968 WHERE T.ID = B.ID
969 AND T.LANGUAGE = L.LANGUAGE_CODE
970 );
971 DELETE FROM OKC_RULE_GROUPS_TLH T
972 WHERE NOT EXISTS (
973 SELECT NULL
974 FROM OKC_RULE_GROUPS_BH B
975 WHERE B.ID = T.ID
976 AND B.MAJOR_VERSION = T.MAJOR_VERSION
977 );
978
979 UPDATE OKC_RULE_GROUPS_TLH T SET (
980 COMMENTS) = (SELECT
981 B.COMMENTS
982 FROM OKC_RULE_GROUPS_TLH B
983 WHERE B.ID = T.ID
984 AND B.MAJOR_VERSION = T.MAJOR_VERSION
985 AND B.LANGUAGE = T.SOURCE_LANG)
986 WHERE (
987 T.ID,
988 T.MAJOR_VERSION,
989 T.LANGUAGE)
990 IN (SELECT
991 SUBT.ID,
992 SUBT.MAJOR_VERSION,
993 SUBT.LANGUAGE
994 FROM OKC_RULE_GROUPS_TLH SUBB, OKC_RULE_GROUPS_TLH SUBT
995 WHERE SUBB.ID = SUBT.ID
996 AND SUBB.MAJOR_VERSION = SUBT.MAJOR_VERSION
997 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
998 AND (SUBB.COMMENTS <> SUBT.COMMENTS
999 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
1000 OR (SUBB.COMMENTS IS NOT NULL AND SUBT.COMMENTS IS NULL)
1001 ));
1002
1003 INSERT INTO OKC_RULE_GROUPS_TLH (
1004 ID,
1005 LANGUAGE,
1006 MAJOR_VERSION,
1007 SOURCE_LANG,
1008 SFWT_FLAG,
1009 COMMENTS,
1010 CREATED_BY,
1011 CREATION_DATE,
1012 LAST_UPDATED_BY,
1013 LAST_UPDATE_DATE,
1014 LAST_UPDATE_LOGIN)
1015 SELECT
1016 B.ID,
1017 L.LANGUAGE_CODE,
1018 B.MAJOR_VERSION,
1019 B.SOURCE_LANG,
1020 B.SFWT_FLAG,
1021 B.COMMENTS,
1022 B.CREATED_BY,
1023 B.CREATION_DATE,
1024 B.LAST_UPDATED_BY,
1025 B.LAST_UPDATE_DATE,
1026 B.LAST_UPDATE_LOGIN
1027 FROM OKC_RULE_GROUPS_TLH B, FND_LANGUAGES L
1028 WHERE L.INSTALLED_FLAG IN ('I', 'B')
1029 AND B.LANGUAGE = USERENV('LANG')
1030 AND NOT EXISTS(
1031 SELECT NULL
1032 FROM OKC_RULE_GROUPS_TLH T
1033 WHERE T.ID = B.ID
1034 AND T.MAJOR_VERSION = B.MAJOR_VERSION
1035 AND T.LANGUAGE = L.LANGUAGE_CODE
1036 );
1037
1038
1039 END add_language;
1040
1041 ---------------------------------------------------------------------------
1042 -- FUNCTION get_rec for: OKC_RULE_GROUPS_B
1043 ---------------------------------------------------------------------------
1044 FUNCTION get_rec (
1045 p_rgp_rec IN rgp_rec_type,
1046 x_no_data_found OUT NOCOPY BOOLEAN
1047 ) RETURN rgp_rec_type IS
1048 CURSOR rgp_pk_csr (p_id IN NUMBER) IS
1049 SELECT
1050 ID,
1051 RGD_CODE,
1052 SAT_CODE,
1053 RGP_TYPE,
1054 CHR_ID,
1055 CLE_ID,
1056 DNZ_CHR_ID,
1057 PARENT_RGP_ID,
1058 OBJECT_VERSION_NUMBER,
1059 CREATED_BY,
1060 CREATION_DATE,
1061 LAST_UPDATED_BY,
1062 LAST_UPDATE_DATE,
1063 LAST_UPDATE_LOGIN,
1064 ATTRIBUTE_CATEGORY,
1065 ATTRIBUTE1,
1066 ATTRIBUTE2,
1067 ATTRIBUTE3,
1068 ATTRIBUTE4,
1069 ATTRIBUTE5,
1070 ATTRIBUTE6,
1071 ATTRIBUTE7,
1072 ATTRIBUTE8,
1073 ATTRIBUTE9,
1074 ATTRIBUTE10,
1075 ATTRIBUTE11,
1076 ATTRIBUTE12,
1077 ATTRIBUTE13,
1078 ATTRIBUTE14,
1079 ATTRIBUTE15
1080 FROM Okc_Rule_Groups_B
1081 WHERE okc_rule_groups_b.id = p_id;
1082 l_rgp_pk rgp_pk_csr%ROWTYPE;
1083 l_rgp_rec rgp_rec_type;
1084 BEGIN
1085 x_no_data_found := TRUE;
1086 -- Get current database values
1087 OPEN rgp_pk_csr (p_rgp_rec.id);
1088 FETCH rgp_pk_csr INTO
1089 l_rgp_rec.ID,
1090 l_rgp_rec.RGD_CODE,
1091 l_rgp_rec.SAT_CODE,
1092 l_rgp_rec.RGP_TYPE,
1093 l_rgp_rec.CHR_ID,
1094 l_rgp_rec.CLE_ID,
1095 l_rgp_rec.DNZ_CHR_ID,
1096 l_rgp_rec.PARENT_RGP_ID,
1097 l_rgp_rec.OBJECT_VERSION_NUMBER,
1098 l_rgp_rec.CREATED_BY,
1099 l_rgp_rec.CREATION_DATE,
1100 l_rgp_rec.LAST_UPDATED_BY,
1101 l_rgp_rec.LAST_UPDATE_DATE,
1102 l_rgp_rec.LAST_UPDATE_LOGIN,
1103 l_rgp_rec.ATTRIBUTE_CATEGORY,
1104 l_rgp_rec.ATTRIBUTE1,
1105 l_rgp_rec.ATTRIBUTE2,
1106 l_rgp_rec.ATTRIBUTE3,
1107 l_rgp_rec.ATTRIBUTE4,
1108 l_rgp_rec.ATTRIBUTE5,
1109 l_rgp_rec.ATTRIBUTE6,
1110 l_rgp_rec.ATTRIBUTE7,
1111 l_rgp_rec.ATTRIBUTE8,
1112 l_rgp_rec.ATTRIBUTE9,
1113 l_rgp_rec.ATTRIBUTE10,
1114 l_rgp_rec.ATTRIBUTE11,
1115 l_rgp_rec.ATTRIBUTE12,
1116 l_rgp_rec.ATTRIBUTE13,
1117 l_rgp_rec.ATTRIBUTE14,
1118 l_rgp_rec.ATTRIBUTE15;
1119 x_no_data_found := rgp_pk_csr%NOTFOUND;
1120 CLOSE rgp_pk_csr;
1121 RETURN(l_rgp_rec);
1122 END get_rec;
1123
1124 FUNCTION get_rec (
1125 p_rgp_rec IN rgp_rec_type
1126 ) RETURN rgp_rec_type IS
1127 l_row_notfound BOOLEAN := TRUE;
1128 BEGIN
1129 RETURN(get_rec(p_rgp_rec, l_row_notfound));
1130 END get_rec;
1131 ---------------------------------------------------------------------------
1132 -- FUNCTION get_rec for: OKC_RULE_GROUPS_TL
1133 ---------------------------------------------------------------------------
1134 FUNCTION get_rec (
1135 p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type,
1136 x_no_data_found OUT NOCOPY BOOLEAN
1137 ) RETURN okc_rule_groups_tl_rec_type IS
1138 CURSOR rgp_pktl_csr (p_id IN NUMBER,
1139 p_language IN VARCHAR2) IS
1140 SELECT
1141 ID,
1142 LANGUAGE,
1143 SOURCE_LANG,
1144 SFWT_FLAG,
1145 COMMENTS,
1146 CREATED_BY,
1147 CREATION_DATE,
1148 LAST_UPDATED_BY,
1149 LAST_UPDATE_DATE,
1150 LAST_UPDATE_LOGIN
1151 FROM Okc_Rule_Groups_Tl
1152 WHERE okc_rule_groups_tl.id = p_id
1153 AND okc_rule_groups_tl.language = p_language;
1154 l_rgp_pktl rgp_pktl_csr%ROWTYPE;
1155 l_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
1156 BEGIN
1157 x_no_data_found := TRUE;
1158 -- Get current database values
1159 OPEN rgp_pktl_csr (p_okc_rule_groups_tl_rec.id,
1160 p_okc_rule_groups_tl_rec.language);
1161 FETCH rgp_pktl_csr INTO
1162 l_okc_rule_groups_tl_rec.ID,
1163 l_okc_rule_groups_tl_rec.LANGUAGE,
1164 l_okc_rule_groups_tl_rec.SOURCE_LANG,
1165 l_okc_rule_groups_tl_rec.SFWT_FLAG,
1166 l_okc_rule_groups_tl_rec.COMMENTS,
1167 l_okc_rule_groups_tl_rec.CREATED_BY,
1168 l_okc_rule_groups_tl_rec.CREATION_DATE,
1169 l_okc_rule_groups_tl_rec.LAST_UPDATED_BY,
1170 l_okc_rule_groups_tl_rec.LAST_UPDATE_DATE,
1171 l_okc_rule_groups_tl_rec.LAST_UPDATE_LOGIN;
1172 x_no_data_found := rgp_pktl_csr%NOTFOUND;
1173 CLOSE rgp_pktl_csr;
1174 RETURN(l_okc_rule_groups_tl_rec);
1175 END get_rec;
1176
1177 FUNCTION get_rec (
1178 p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type
1179 ) RETURN okc_rule_groups_tl_rec_type IS
1180 l_row_notfound BOOLEAN := TRUE;
1181 BEGIN
1182 RETURN(get_rec(p_okc_rule_groups_tl_rec, l_row_notfound));
1183 END get_rec;
1184 ---------------------------------------------------------------------------
1185 -- FUNCTION get_rec for: OKC_RULE_GROUPS_V
1186 ---------------------------------------------------------------------------
1187 FUNCTION get_rec (
1188 p_rgpv_rec IN rgpv_rec_type,
1189 x_no_data_found OUT NOCOPY BOOLEAN
1190 ) RETURN rgpv_rec_type IS
1191 CURSOR okc_rgpv_pk_csr (p_id IN NUMBER) IS
1192 SELECT
1193 ID,
1194 OBJECT_VERSION_NUMBER,
1195 SFWT_FLAG,
1196 RGD_CODE,
1197 SAT_CODE,
1198 RGP_TYPE,
1199 CLE_ID,
1200 CHR_ID,
1201 DNZ_CHR_ID,
1202 PARENT_RGP_ID,
1203 COMMENTS,
1204 ATTRIBUTE_CATEGORY,
1205 ATTRIBUTE1,
1206 ATTRIBUTE2,
1207 ATTRIBUTE3,
1208 ATTRIBUTE4,
1209 ATTRIBUTE5,
1210 ATTRIBUTE6,
1211 ATTRIBUTE7,
1212 ATTRIBUTE8,
1213 ATTRIBUTE9,
1214 ATTRIBUTE10,
1215 ATTRIBUTE11,
1216 ATTRIBUTE12,
1217 ATTRIBUTE13,
1218 ATTRIBUTE14,
1219 ATTRIBUTE15,
1220 CREATED_BY,
1221 CREATION_DATE,
1222 LAST_UPDATED_BY,
1223 LAST_UPDATE_DATE,
1224 LAST_UPDATE_LOGIN
1225 FROM Okc_Rule_Groups_V
1226 WHERE okc_rule_groups_v.id = p_id;
1227 l_okc_rgpv_pk okc_rgpv_pk_csr%ROWTYPE;
1228 l_rgpv_rec rgpv_rec_type;
1229 BEGIN
1230 x_no_data_found := TRUE;
1231 -- Get current database values
1232 OPEN okc_rgpv_pk_csr (p_rgpv_rec.id);
1233 FETCH okc_rgpv_pk_csr INTO
1234 l_rgpv_rec.ID,
1235 l_rgpv_rec.OBJECT_VERSION_NUMBER,
1236 l_rgpv_rec.SFWT_FLAG,
1237 l_rgpv_rec.RGD_CODE,
1238 l_rgpv_rec.SAT_CODE,
1239 l_rgpv_rec.RGP_TYPE,
1240 l_rgpv_rec.CLE_ID,
1241 l_rgpv_rec.CHR_ID,
1242 l_rgpv_rec.DNZ_CHR_ID,
1243 l_rgpv_rec.PARENT_RGP_ID,
1244 l_rgpv_rec.COMMENTS,
1245 l_rgpv_rec.ATTRIBUTE_CATEGORY,
1246 l_rgpv_rec.ATTRIBUTE1,
1247 l_rgpv_rec.ATTRIBUTE2,
1248 l_rgpv_rec.ATTRIBUTE3,
1249 l_rgpv_rec.ATTRIBUTE4,
1250 l_rgpv_rec.ATTRIBUTE5,
1251 l_rgpv_rec.ATTRIBUTE6,
1252 l_rgpv_rec.ATTRIBUTE7,
1253 l_rgpv_rec.ATTRIBUTE8,
1254 l_rgpv_rec.ATTRIBUTE9,
1255 l_rgpv_rec.ATTRIBUTE10,
1256 l_rgpv_rec.ATTRIBUTE11,
1257 l_rgpv_rec.ATTRIBUTE12,
1258 l_rgpv_rec.ATTRIBUTE13,
1259 l_rgpv_rec.ATTRIBUTE14,
1260 l_rgpv_rec.ATTRIBUTE15,
1261 l_rgpv_rec.CREATED_BY,
1262 l_rgpv_rec.CREATION_DATE,
1263 l_rgpv_rec.LAST_UPDATED_BY,
1264 l_rgpv_rec.LAST_UPDATE_DATE,
1265 l_rgpv_rec.LAST_UPDATE_LOGIN;
1266 x_no_data_found := okc_rgpv_pk_csr%NOTFOUND;
1267 CLOSE okc_rgpv_pk_csr;
1268 RETURN(l_rgpv_rec);
1269 END get_rec;
1270
1271 FUNCTION get_rec (
1272 p_rgpv_rec IN rgpv_rec_type
1273 ) RETURN rgpv_rec_type IS
1274 l_row_notfound BOOLEAN := TRUE;
1275 BEGIN
1276 RETURN(get_rec(p_rgpv_rec, l_row_notfound));
1277 END get_rec;
1278
1279 -------------------------------------------------------
1280 -- FUNCTION null_out_defaults for: OKC_RULE_GROUPS_V --
1281 -------------------------------------------------------
1282 FUNCTION null_out_defaults (
1283 p_rgpv_rec IN rgpv_rec_type
1284 ) RETURN rgpv_rec_type IS
1285 l_rgpv_rec rgpv_rec_type := p_rgpv_rec;
1286 BEGIN
1287 IF (l_rgpv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
1288 l_rgpv_rec.object_version_number := NULL;
1289 END IF;
1290 IF (l_rgpv_rec.sfwt_flag = OKC_API.G_MISS_CHAR) THEN
1291 l_rgpv_rec.sfwt_flag := NULL;
1292 END IF;
1293 IF (l_rgpv_rec.rgd_code = OKC_API.G_MISS_CHAR) THEN
1294 l_rgpv_rec.rgd_code := NULL;
1295 END IF;
1296 IF (l_rgpv_rec.sat_code = OKC_API.G_MISS_CHAR) THEN
1297 l_rgpv_rec.sat_code := NULL;
1298 END IF;
1299 IF (l_rgpv_rec.rgp_type = OKC_API.G_MISS_CHAR) THEN
1300 l_rgpv_rec.rgp_type := NULL;
1301 END IF;
1302 IF (l_rgpv_rec.cle_id = OKC_API.G_MISS_NUM) THEN
1303 l_rgpv_rec.cle_id := NULL;
1304 END IF;
1305 IF (l_rgpv_rec.chr_id = OKC_API.G_MISS_NUM) THEN
1306 l_rgpv_rec.chr_id := NULL;
1307 END IF;
1308 IF (l_rgpv_rec.dnz_chr_id = OKC_API.G_MISS_NUM) THEN
1309 l_rgpv_rec.dnz_chr_id := NULL;
1310 END IF;
1311 IF (l_rgpv_rec.parent_rgp_id = OKC_API.G_MISS_NUM) THEN
1312 l_rgpv_rec.parent_rgp_id := NULL;
1313 END IF;
1314 IF (l_rgpv_rec.comments = OKC_API.G_MISS_CHAR) THEN
1315 l_rgpv_rec.comments := NULL;
1316 END IF;
1317 IF (l_rgpv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
1318 l_rgpv_rec.attribute_category := NULL;
1319 END IF;
1320 IF (l_rgpv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
1321 l_rgpv_rec.attribute1 := NULL;
1322 END IF;
1323 IF (l_rgpv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
1324 l_rgpv_rec.attribute2 := NULL;
1325 END IF;
1326 IF (l_rgpv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
1327 l_rgpv_rec.attribute3 := NULL;
1328 END IF;
1329 IF (l_rgpv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
1330 l_rgpv_rec.attribute4 := NULL;
1331 END IF;
1332 IF (l_rgpv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
1333 l_rgpv_rec.attribute5 := NULL;
1334 END IF;
1335 IF (l_rgpv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
1336 l_rgpv_rec.attribute6 := NULL;
1337 END IF;
1338 IF (l_rgpv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
1339 l_rgpv_rec.attribute7 := NULL;
1340 END IF;
1341 IF (l_rgpv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
1342 l_rgpv_rec.attribute8 := NULL;
1343 END IF;
1344 IF (l_rgpv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
1345 l_rgpv_rec.attribute9 := NULL;
1346 END IF;
1347 IF (l_rgpv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
1348 l_rgpv_rec.attribute10 := NULL;
1349 END IF;
1350 IF (l_rgpv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
1351 l_rgpv_rec.attribute11 := NULL;
1352 END IF;
1353 IF (l_rgpv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
1354 l_rgpv_rec.attribute12 := NULL;
1355 END IF;
1356 IF (l_rgpv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
1357 l_rgpv_rec.attribute13 := NULL;
1358 END IF;
1359 IF (l_rgpv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
1360 l_rgpv_rec.attribute14 := NULL;
1361 END IF;
1362 IF (l_rgpv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
1363 l_rgpv_rec.attribute15 := NULL;
1364 END IF;
1365 IF (l_rgpv_rec.created_by = OKC_API.G_MISS_NUM) THEN
1366 l_rgpv_rec.created_by := NULL;
1367 END IF;
1368 IF (l_rgpv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
1369 l_rgpv_rec.creation_date := NULL;
1370 END IF;
1371 IF (l_rgpv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
1372 l_rgpv_rec.last_updated_by := NULL;
1373 END IF;
1374 IF (l_rgpv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
1375 l_rgpv_rec.last_update_date := NULL;
1376 END IF;
1377 IF (l_rgpv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
1378 l_rgpv_rec.last_update_login := NULL;
1379 END IF;
1380 RETURN(l_rgpv_rec);
1381 END null_out_defaults;
1382 ---------------------------------------------------------------------------
1383 -- PROCEDURE Validate_Attributes
1384 ---------------------------------------------------------------------------
1385 -----------------------------------------------
1386 -- Validate_Attributes for:OKC_RULE_GROUPS_V --
1387 -----------------------------------------------
1388 /* commenting out nocopy generated code in favor of hand-coded procedure
1389 FUNCTION Validate_Attributes (
1390 p_rgpv_rec IN rgpv_rec_type
1391 ) RETURN VARCHAR2 IS
1392 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1393 BEGIN
1394 IF p_rgpv_rec.id = OKC_API.G_MISS_NUM OR
1395 p_rgpv_rec.id IS NULL
1396 THEN
1397 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
1398 l_return_status := OKC_API.G_RET_STS_ERROR;
1399 ELSIF p_rgpv_rec.object_version_number = OKC_API.G_MISS_NUM OR
1400 p_rgpv_rec.object_version_number IS NULL
1401 THEN
1402 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
1403 l_return_status := OKC_API.G_RET_STS_ERROR;
1404 ELSIF p_rgpv_rec.rgd_code = OKC_API.G_MISS_CHAR OR
1405 p_rgpv_rec.rgd_code IS NULL
1406 THEN
1407 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'rgd_code');
1408 l_return_status := OKC_API.G_RET_STS_ERROR;
1409 ELSIF p_rgpv_rec.dnz_chr_id = OKC_API.G_MISS_NUM OR
1410 p_rgpv_rec.dnz_chr_id IS NULL
1411 THEN
1412 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'dnz_chr_id');
1413 l_return_status := OKC_API.G_RET_STS_ERROR;
1414 END IF;
1415 RETURN(l_return_status);
1416 END Validate_Attributes;
1417 */
1418 ---------------------------------------------------------------------------
1419 -- PROCEDURE Validate_Record
1420 ---------------------------------------------------------------------------
1421 -------------------------------------------
1422 -- Validate_Record for:OKC_RULE_GROUPS_V --
1423 -------------------------------------------
1424 FUNCTION Validate_Record (
1425 p_rgpv_rec IN rgpv_rec_type
1426 ) RETURN VARCHAR2 IS
1427 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1428 BEGIN
1429 RETURN (l_return_status);
1430 END Validate_Record;
1431
1432 ---------------------------------------------------------------------------
1433 -- PROCEDURE Migrate
1434 ---------------------------------------------------------------------------
1435 PROCEDURE migrate (
1436 p_from IN rgpv_rec_type,
1437 p_to IN OUT NOCOPY rgp_rec_type
1438 ) IS
1439 BEGIN
1440 p_to.id := p_from.id;
1441 p_to.rgd_code := p_from.rgd_code;
1442 p_to.sat_code := p_from.sat_code;
1443 p_to.rgp_type := p_from.rgp_type;
1444 p_to.chr_id := p_from.chr_id;
1445 p_to.cle_id := p_from.cle_id;
1446 p_to.dnz_chr_id := p_from.dnz_chr_id;
1447 p_to.parent_rgp_id := p_from.parent_rgp_id;
1448 p_to.object_version_number := p_from.object_version_number;
1449 p_to.created_by := p_from.created_by;
1450 p_to.creation_date := p_from.creation_date;
1451 p_to.last_updated_by := p_from.last_updated_by;
1452 p_to.last_update_date := p_from.last_update_date;
1453 p_to.last_update_login := p_from.last_update_login;
1454 p_to.attribute_category := p_from.attribute_category;
1455 p_to.attribute1 := p_from.attribute1;
1456 p_to.attribute2 := p_from.attribute2;
1457 p_to.attribute3 := p_from.attribute3;
1458 p_to.attribute4 := p_from.attribute4;
1459 p_to.attribute5 := p_from.attribute5;
1460 p_to.attribute6 := p_from.attribute6;
1461 p_to.attribute7 := p_from.attribute7;
1462 p_to.attribute8 := p_from.attribute8;
1463 p_to.attribute9 := p_from.attribute9;
1464 p_to.attribute10 := p_from.attribute10;
1465 p_to.attribute11 := p_from.attribute11;
1466 p_to.attribute12 := p_from.attribute12;
1467 p_to.attribute13 := p_from.attribute13;
1468 p_to.attribute14 := p_from.attribute14;
1469 p_to.attribute15 := p_from.attribute15;
1470 END migrate;
1471 PROCEDURE migrate (
1472 p_from IN rgp_rec_type,
1473 p_to IN OUT NOCOPY rgpv_rec_type
1474 ) IS
1475 BEGIN
1476 p_to.id := p_from.id;
1477 p_to.rgd_code := p_from.rgd_code;
1478 p_to.sat_code := p_from.sat_code;
1479 p_to.rgp_type := p_from.rgp_type;
1480 p_to.chr_id := p_from.chr_id;
1481 p_to.cle_id := p_from.cle_id;
1482 p_to.dnz_chr_id := p_from.dnz_chr_id;
1483 p_to.parent_rgp_id := p_from.parent_rgp_id;
1484 p_to.object_version_number := p_from.object_version_number;
1485 p_to.created_by := p_from.created_by;
1486 p_to.creation_date := p_from.creation_date;
1487 p_to.last_updated_by := p_from.last_updated_by;
1488 p_to.last_update_date := p_from.last_update_date;
1489 p_to.last_update_login := p_from.last_update_login;
1490 p_to.attribute_category := p_from.attribute_category;
1491 p_to.attribute1 := p_from.attribute1;
1492 p_to.attribute2 := p_from.attribute2;
1493 p_to.attribute3 := p_from.attribute3;
1494 p_to.attribute4 := p_from.attribute4;
1495 p_to.attribute5 := p_from.attribute5;
1496 p_to.attribute6 := p_from.attribute6;
1497 p_to.attribute7 := p_from.attribute7;
1498 p_to.attribute8 := p_from.attribute8;
1499 p_to.attribute9 := p_from.attribute9;
1500 p_to.attribute10 := p_from.attribute10;
1501 p_to.attribute11 := p_from.attribute11;
1502 p_to.attribute12 := p_from.attribute12;
1503 p_to.attribute13 := p_from.attribute13;
1504 p_to.attribute14 := p_from.attribute14;
1505 p_to.attribute15 := p_from.attribute15;
1506 END migrate;
1507 PROCEDURE migrate (
1508 p_from IN rgpv_rec_type,
1509 p_to IN OUT NOCOPY okc_rule_groups_tl_rec_type
1510 ) IS
1511 BEGIN
1512 p_to.id := p_from.id;
1513 p_to.sfwt_flag := p_from.sfwt_flag;
1514 p_to.comments := p_from.comments;
1515 p_to.created_by := p_from.created_by;
1516 p_to.creation_date := p_from.creation_date;
1517 p_to.last_updated_by := p_from.last_updated_by;
1518 p_to.last_update_date := p_from.last_update_date;
1519 p_to.last_update_login := p_from.last_update_login;
1520 END migrate;
1521 PROCEDURE migrate (
1522 p_from IN okc_rule_groups_tl_rec_type,
1523 p_to IN OUT NOCOPY rgpv_rec_type
1524 ) IS
1525 BEGIN
1526 p_to.id := p_from.id;
1527 p_to.sfwt_flag := p_from.sfwt_flag;
1528 p_to.comments := p_from.comments;
1529 p_to.created_by := p_from.created_by;
1530 p_to.creation_date := p_from.creation_date;
1531 p_to.last_updated_by := p_from.last_updated_by;
1532 p_to.last_update_date := p_from.last_update_date;
1533 p_to.last_update_login := p_from.last_update_login;
1534 END migrate;
1535
1536 ---------------------------------------------------------------------------
1537 -- PROCEDURE validate_row
1538 ---------------------------------------------------------------------------
1539 ----------------------------------------
1540 -- validate_row for:OKC_RULE_GROUPS_V --
1541 ----------------------------------------
1542 PROCEDURE validate_row(
1543 p_api_version IN NUMBER,
1544 p_init_msg_list IN VARCHAR2,
1545 x_return_status OUT NOCOPY VARCHAR2,
1546 x_msg_count OUT NOCOPY NUMBER,
1547 x_msg_data OUT NOCOPY VARCHAR2,
1548 p_rgpv_rec IN rgpv_rec_type) IS
1549
1550 l_api_version CONSTANT NUMBER := 1;
1551 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1552 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1553 l_rgpv_rec rgpv_rec_type := p_rgpv_rec;
1554 l_rgp_rec rgp_rec_type;
1555 l_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
1556 BEGIN
1557 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1558 G_PKG_NAME,
1559 p_init_msg_list,
1560 l_api_version,
1561 p_api_version,
1562 '_PVT',
1563 x_return_status);
1564 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1565 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1566 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1567 RAISE OKC_API.G_EXCEPTION_ERROR;
1568 END IF;
1569 --- Validate all non-missing attributes (Item Level Validation)
1570 l_return_status := Validate_Attributes(l_rgpv_rec);
1571 --- If any errors happen abort API
1572 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1573 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1574 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1575 RAISE OKC_API.G_EXCEPTION_ERROR;
1576 END IF;
1577 l_return_status := Validate_Record(l_rgpv_rec);
1578 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1579 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1580 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1581 RAISE OKC_API.G_EXCEPTION_ERROR;
1582 END IF;
1583 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1584 EXCEPTION
1585 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1586 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1587 (
1588 l_api_name,
1589 G_PKG_NAME,
1590 'OKC_API.G_RET_STS_ERROR',
1591 x_msg_count,
1592 x_msg_data,
1593 '_PVT'
1594 );
1595 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1596 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1597 (
1598 l_api_name,
1599 G_PKG_NAME,
1600 'OKC_API.G_RET_STS_UNEXP_ERROR',
1601 x_msg_count,
1602 x_msg_data,
1603 '_PVT'
1604 );
1605 WHEN OTHERS THEN
1606 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1607 (
1608 l_api_name,
1609 G_PKG_NAME,
1610 'OTHERS',
1611 x_msg_count,
1612 x_msg_data,
1613 '_PVT'
1614 );
1615 END validate_row;
1616 ------------------------------------------
1617 -- PL/SQL TBL validate_row for:RGPV_TBL --
1618 ------------------------------------------
1619 PROCEDURE validate_row(
1620 p_api_version IN NUMBER,
1621 p_init_msg_list IN VARCHAR2,
1622 x_return_status OUT NOCOPY VARCHAR2,
1623 x_msg_count OUT NOCOPY NUMBER,
1624 x_msg_data OUT NOCOPY VARCHAR2,
1625 p_rgpv_tbl IN rgpv_tbl_type) IS
1626
1627 l_api_version CONSTANT NUMBER := 1;
1628 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1629 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1630 i NUMBER := 0;
1631 BEGIN
1632 OKC_API.init_msg_list(p_init_msg_list);
1633 -- Make sure PL/SQL table has records in it before passing
1634 IF (p_rgpv_tbl.COUNT > 0) THEN
1635 i := p_rgpv_tbl.FIRST;
1636 LOOP
1637 validate_row (
1638 p_api_version => p_api_version,
1639 p_init_msg_list => OKC_API.G_FALSE,
1640 x_return_status => x_return_status,
1641 x_msg_count => x_msg_count,
1642 x_msg_data => x_msg_data,
1643 p_rgpv_rec => p_rgpv_tbl(i));
1644 EXIT WHEN (i = p_rgpv_tbl.LAST);
1645 i := p_rgpv_tbl.NEXT(i);
1646 END LOOP;
1647 END IF;
1648 EXCEPTION
1649 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1650 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1651 (
1652 l_api_name,
1653 G_PKG_NAME,
1654 'OKC_API.G_RET_STS_ERROR',
1655 x_msg_count,
1656 x_msg_data,
1657 '_PVT'
1658 );
1659 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1660 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1661 (
1662 l_api_name,
1663 G_PKG_NAME,
1664 'OKC_API.G_RET_STS_UNEXP_ERROR',
1665 x_msg_count,
1666 x_msg_data,
1667 '_PVT'
1668 );
1669 WHEN OTHERS THEN
1670 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1671 (
1672 l_api_name,
1673 G_PKG_NAME,
1674 'OTHERS',
1675 x_msg_count,
1676 x_msg_data,
1677 '_PVT'
1678 );
1679 END validate_row;
1680
1681 ---------------------------------------------------------------------------
1682 -- PROCEDURE insert_row
1683 ---------------------------------------------------------------------------
1684 --------------------------------------
1685 -- insert_row for:OKC_RULE_GROUPS_B --
1686 --------------------------------------
1687 PROCEDURE insert_row(
1688 p_init_msg_list IN VARCHAR2,
1689 x_return_status OUT NOCOPY VARCHAR2,
1690 x_msg_count OUT NOCOPY NUMBER,
1691 x_msg_data OUT NOCOPY VARCHAR2,
1692 p_rgp_rec IN rgp_rec_type,
1693 x_rgp_rec OUT NOCOPY rgp_rec_type) IS
1694
1695 l_api_version CONSTANT NUMBER := 1;
1696 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1697 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1698 l_rgp_rec rgp_rec_type := p_rgp_rec;
1699 l_def_rgp_rec rgp_rec_type;
1700 ------------------------------------------
1701 -- Set_Attributes for:OKC_RULE_GROUPS_B --
1702 ------------------------------------------
1703 FUNCTION Set_Attributes (
1704 p_rgp_rec IN rgp_rec_type,
1705 x_rgp_rec OUT NOCOPY rgp_rec_type
1706 ) RETURN VARCHAR2 IS
1707 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1708 BEGIN
1709 x_rgp_rec := p_rgp_rec;
1710 RETURN(l_return_status);
1711 END Set_Attributes;
1712 BEGIN
1713 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1714 p_init_msg_list,
1715 '_PVT',
1716 x_return_status);
1717 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1718 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1719 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1720 RAISE OKC_API.G_EXCEPTION_ERROR;
1721 END IF;
1722 --- Setting item attributes
1723 l_return_status := Set_Attributes(
1724 p_rgp_rec, -- IN
1725 l_rgp_rec); -- OUT
1726 --- If any errors happen abort API
1727 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1728 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1729 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1730 RAISE OKC_API.G_EXCEPTION_ERROR;
1731 END IF;
1732 INSERT INTO OKC_RULE_GROUPS_B(
1733 id,
1734 rgd_code,
1735 sat_code,
1736 rgp_type,
1737 chr_id,
1738 cle_id,
1739 dnz_chr_id,
1740 parent_rgp_id,
1741 object_version_number,
1742 created_by,
1743 creation_date,
1744 last_updated_by,
1745 last_update_date,
1746 last_update_login,
1747 attribute_category,
1748 attribute1,
1749 attribute2,
1750 attribute3,
1751 attribute4,
1752 attribute5,
1753 attribute6,
1754 attribute7,
1755 attribute8,
1756 attribute9,
1757 attribute10,
1758 attribute11,
1759 attribute12,
1760 attribute13,
1761 attribute14,
1762 attribute15)
1763 VALUES (
1764 l_rgp_rec.id,
1765 l_rgp_rec.rgd_code,
1766 l_rgp_rec.sat_code,
1767 l_rgp_rec.rgp_type,
1768 l_rgp_rec.chr_id,
1769 l_rgp_rec.cle_id,
1770 l_rgp_rec.dnz_chr_id,
1771 l_rgp_rec.parent_rgp_id,
1772 l_rgp_rec.object_version_number,
1773 l_rgp_rec.created_by,
1774 l_rgp_rec.creation_date,
1775 l_rgp_rec.last_updated_by,
1776 l_rgp_rec.last_update_date,
1777 l_rgp_rec.last_update_login,
1778 l_rgp_rec.attribute_category,
1779 l_rgp_rec.attribute1,
1780 l_rgp_rec.attribute2,
1781 l_rgp_rec.attribute3,
1782 l_rgp_rec.attribute4,
1783 l_rgp_rec.attribute5,
1784 l_rgp_rec.attribute6,
1785 l_rgp_rec.attribute7,
1786 l_rgp_rec.attribute8,
1787 l_rgp_rec.attribute9,
1788 l_rgp_rec.attribute10,
1789 l_rgp_rec.attribute11,
1790 l_rgp_rec.attribute12,
1791 l_rgp_rec.attribute13,
1792 l_rgp_rec.attribute14,
1793 l_rgp_rec.attribute15);
1794 -- Set OUT values
1795 x_rgp_rec := l_rgp_rec;
1796 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1797 EXCEPTION
1798 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1799 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1800 (
1801 l_api_name,
1802 G_PKG_NAME,
1803 'OKC_API.G_RET_STS_ERROR',
1804 x_msg_count,
1805 x_msg_data,
1806 '_PVT'
1807 );
1808 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1809 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1810 (
1811 l_api_name,
1812 G_PKG_NAME,
1813 'OKC_API.G_RET_STS_UNEXP_ERROR',
1814 x_msg_count,
1815 x_msg_data,
1816 '_PVT'
1817 );
1818 WHEN OTHERS THEN
1819 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1820 (
1821 l_api_name,
1822 G_PKG_NAME,
1823 'OTHERS',
1824 x_msg_count,
1825 x_msg_data,
1826 '_PVT'
1827 );
1828 END insert_row;
1829 ---------------------------------------
1830 -- insert_row for:OKC_RULE_GROUPS_TL --
1831 ---------------------------------------
1832 PROCEDURE insert_row(
1833 p_init_msg_list IN VARCHAR2,
1834 x_return_status OUT NOCOPY VARCHAR2,
1835 x_msg_count OUT NOCOPY NUMBER,
1836 x_msg_data OUT NOCOPY VARCHAR2,
1837 p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type,
1838 x_okc_rule_groups_tl_rec OUT NOCOPY okc_rule_groups_tl_rec_type) IS
1839
1840 l_api_version CONSTANT NUMBER := 1;
1841 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
1842 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1843 l_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type := p_okc_rule_groups_tl_rec;
1844 l_def_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
1845 CURSOR get_languages IS
1846 SELECT *
1847 FROM FND_LANGUAGES
1848 WHERE INSTALLED_FLAG IN ('I', 'B');
1849 -------------------------------------------
1850 -- Set_Attributes for:OKC_RULE_GROUPS_TL --
1851 -------------------------------------------
1852 FUNCTION Set_Attributes (
1853 p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type,
1854 x_okc_rule_groups_tl_rec OUT NOCOPY okc_rule_groups_tl_rec_type
1855 ) RETURN VARCHAR2 IS
1856 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1857 BEGIN
1858 x_okc_rule_groups_tl_rec := p_okc_rule_groups_tl_rec;
1859 x_okc_rule_groups_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
1860 x_okc_rule_groups_tl_rec.SOURCE_LANG := okc_util.get_userenv_lang;
1861 RETURN(l_return_status);
1862 END Set_Attributes;
1863 BEGIN
1864 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1865 p_init_msg_list,
1866 '_PVT',
1867 x_return_status);
1868 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1869 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1870 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1871 RAISE OKC_API.G_EXCEPTION_ERROR;
1872 END IF;
1873 --- Setting item attributes
1874 l_return_status := Set_Attributes(
1875 p_okc_rule_groups_tl_rec, -- IN
1876 l_okc_rule_groups_tl_rec); -- OUT
1877 --- If any errors happen abort API
1878 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1879 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1880 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1881 RAISE OKC_API.G_EXCEPTION_ERROR;
1882 END IF;
1883 FOR l_lang_rec IN get_languages LOOP
1884 l_okc_rule_groups_tl_rec.language := l_lang_rec.language_code;
1885 INSERT INTO OKC_RULE_GROUPS_TL(
1886 id,
1887 language,
1888 source_lang,
1889 sfwt_flag,
1890 comments,
1891 created_by,
1892 creation_date,
1893 last_updated_by,
1894 last_update_date,
1895 last_update_login)
1896 VALUES (
1897 l_okc_rule_groups_tl_rec.id,
1898 l_okc_rule_groups_tl_rec.language,
1899 l_okc_rule_groups_tl_rec.source_lang,
1900 l_okc_rule_groups_tl_rec.sfwt_flag,
1901 l_okc_rule_groups_tl_rec.comments,
1902 l_okc_rule_groups_tl_rec.created_by,
1903 l_okc_rule_groups_tl_rec.creation_date,
1904 l_okc_rule_groups_tl_rec.last_updated_by,
1905 l_okc_rule_groups_tl_rec.last_update_date,
1906 l_okc_rule_groups_tl_rec.last_update_login);
1907 END LOOP;
1908 -- Set OUT values
1909 x_okc_rule_groups_tl_rec := l_okc_rule_groups_tl_rec;
1910 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1911 EXCEPTION
1912 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1913 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1914 (
1915 l_api_name,
1916 G_PKG_NAME,
1917 'OKC_API.G_RET_STS_ERROR',
1918 x_msg_count,
1919 x_msg_data,
1920 '_PVT'
1921 );
1922 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1923 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1924 (
1925 l_api_name,
1926 G_PKG_NAME,
1927 'OKC_API.G_RET_STS_UNEXP_ERROR',
1928 x_msg_count,
1929 x_msg_data,
1930 '_PVT'
1931 );
1932 WHEN OTHERS THEN
1933 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1934 (
1935 l_api_name,
1936 G_PKG_NAME,
1937 'OTHERS',
1938 x_msg_count,
1939 x_msg_data,
1940 '_PVT'
1941 );
1942 END insert_row;
1943 --------------------------------------
1944 -- insert_row for:OKC_RULE_GROUPS_V --
1945 --------------------------------------
1946 PROCEDURE insert_row(
1947 p_api_version IN NUMBER,
1948 p_init_msg_list IN VARCHAR2,
1949 x_return_status OUT NOCOPY VARCHAR2,
1950 x_msg_count OUT NOCOPY NUMBER,
1951 x_msg_data OUT NOCOPY VARCHAR2,
1952 p_rgpv_rec IN rgpv_rec_type,
1953 x_rgpv_rec OUT NOCOPY rgpv_rec_type) IS
1954
1955 l_api_version CONSTANT NUMBER := 1;
1956 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1957 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1958 l_rgpv_rec rgpv_rec_type;
1959 l_def_rgpv_rec rgpv_rec_type;
1960 l_rgp_rec rgp_rec_type;
1961 lx_rgp_rec rgp_rec_type;
1962 l_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
1963 lx_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
1964 -------------------------------
1965 -- FUNCTION fill_who_columns --
1966 -------------------------------
1967 FUNCTION fill_who_columns (
1968 p_rgpv_rec IN rgpv_rec_type
1969 ) RETURN rgpv_rec_type IS
1970 l_rgpv_rec rgpv_rec_type := p_rgpv_rec;
1971 BEGIN
1972 l_rgpv_rec.CREATION_DATE := SYSDATE;
1973 l_rgpv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1974 l_rgpv_rec.LAST_UPDATE_DATE := SYSDATE;
1975 l_rgpv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1976 l_rgpv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1977 RETURN(l_rgpv_rec);
1978 END fill_who_columns;
1979 ------------------------------------------
1980 -- Set_Attributes for:OKC_RULE_GROUPS_V --
1981 ------------------------------------------
1982 FUNCTION Set_Attributes (
1983 p_rgpv_rec IN rgpv_rec_type,
1984 x_rgpv_rec OUT NOCOPY rgpv_rec_type
1985 ) RETURN VARCHAR2 IS
1986 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1987 BEGIN
1988 x_rgpv_rec := p_rgpv_rec;
1989 x_rgpv_rec.OBJECT_VERSION_NUMBER := 1;
1990 x_rgpv_rec.SFWT_FLAG := 'N';
1991 RETURN(l_return_status);
1992 END Set_Attributes;
1993 BEGIN
1994 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1995 G_PKG_NAME,
1996 p_init_msg_list,
1997 l_api_version,
1998 p_api_version,
1999 '_PVT',
2000 x_return_status);
2001 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2002 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2003 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2004 RAISE OKC_API.G_EXCEPTION_ERROR;
2005 END IF;
2006 l_rgpv_rec := null_out_defaults(p_rgpv_rec);
2007 -- Set primary key value
2008 l_rgpv_rec.ID := get_seq_id;
2009 --- Setting item attributes
2010 l_return_status := Set_Attributes(
2011 l_rgpv_rec, -- IN
2012 l_def_rgpv_rec); -- OUT
2013 --- If any errors happen abort API
2014 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2015 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2016 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2017 RAISE OKC_API.G_EXCEPTION_ERROR;
2018 END IF;
2019 l_def_rgpv_rec := fill_who_columns(l_def_rgpv_rec);
2020 --- Validate all non-missing attributes (Item Level Validation)
2021 l_return_status := Validate_Attributes(l_def_rgpv_rec);
2022 --- If any errors happen abort API
2023 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2024 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2025 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2026 RAISE OKC_API.G_EXCEPTION_ERROR;
2027 END IF;
2028 l_return_status := Validate_Record(l_def_rgpv_rec);
2029 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2030 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2031 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2032 RAISE OKC_API.G_EXCEPTION_ERROR;
2033 END IF;
2034 --------------------------------------
2035 -- Move VIEW record to "Child" records
2036 --------------------------------------
2037 migrate(l_def_rgpv_rec, l_rgp_rec);
2038 migrate(l_def_rgpv_rec, l_okc_rule_groups_tl_rec);
2039 --------------------------------------------
2040 -- Call the INSERT_ROW for each child record
2041 --------------------------------------------
2042 insert_row(
2043 p_init_msg_list,
2044 x_return_status,
2045 x_msg_count,
2046 x_msg_data,
2047 l_rgp_rec,
2048 lx_rgp_rec
2049 );
2050 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2051 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2052 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2053 RAISE OKC_API.G_EXCEPTION_ERROR;
2054 END IF;
2055 migrate(lx_rgp_rec, l_def_rgpv_rec);
2056 insert_row(
2057 p_init_msg_list,
2058 x_return_status,
2059 x_msg_count,
2060 x_msg_data,
2061 l_okc_rule_groups_tl_rec,
2062 lx_okc_rule_groups_tl_rec
2063 );
2064 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2065 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2066 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2067 RAISE OKC_API.G_EXCEPTION_ERROR;
2068 END IF;
2069 migrate(lx_okc_rule_groups_tl_rec, l_def_rgpv_rec);
2070 -- Set OUT values
2071 x_rgpv_rec := l_def_rgpv_rec;
2072 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2073 EXCEPTION
2074 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2075 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2076 (
2077 l_api_name,
2078 G_PKG_NAME,
2079 'OKC_API.G_RET_STS_ERROR',
2080 x_msg_count,
2081 x_msg_data,
2082 '_PVT'
2083 );
2084 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2085 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2086 (
2087 l_api_name,
2088 G_PKG_NAME,
2089 'OKC_API.G_RET_STS_UNEXP_ERROR',
2090 x_msg_count,
2091 x_msg_data,
2092 '_PVT'
2093 );
2094 WHEN OTHERS THEN
2095 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2096 (
2097 l_api_name,
2098 G_PKG_NAME,
2099 'OTHERS',
2100 x_msg_count,
2101 x_msg_data,
2102 '_PVT'
2103 );
2104 END insert_row;
2105 ----------------------------------------
2106 -- PL/SQL TBL insert_row for:RGPV_TBL --
2107 ----------------------------------------
2108 PROCEDURE insert_row(
2109 p_api_version IN NUMBER,
2110 p_init_msg_list IN VARCHAR2,
2111 x_return_status OUT NOCOPY VARCHAR2,
2112 x_msg_count OUT NOCOPY NUMBER,
2113 x_msg_data OUT NOCOPY VARCHAR2,
2114 p_rgpv_tbl IN rgpv_tbl_type,
2115 x_rgpv_tbl OUT NOCOPY rgpv_tbl_type) IS
2116
2117 l_api_version CONSTANT NUMBER := 1;
2118 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
2119 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2120 i NUMBER := 0;
2121 BEGIN
2122 OKC_API.init_msg_list(p_init_msg_list);
2123 -- Make sure PL/SQL table has records in it before passing
2124 IF (p_rgpv_tbl.COUNT > 0) THEN
2125 i := p_rgpv_tbl.FIRST;
2126 LOOP
2127 insert_row (
2128 p_api_version => p_api_version,
2129 p_init_msg_list => OKC_API.G_FALSE,
2130 x_return_status => x_return_status,
2131 x_msg_count => x_msg_count,
2132 x_msg_data => x_msg_data,
2133 p_rgpv_rec => p_rgpv_tbl(i),
2134 x_rgpv_rec => x_rgpv_tbl(i));
2135 EXIT WHEN (i = p_rgpv_tbl.LAST);
2136 i := p_rgpv_tbl.NEXT(i);
2137 END LOOP;
2138 END IF;
2139 EXCEPTION
2140 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2141 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2142 (
2143 l_api_name,
2144 G_PKG_NAME,
2145 'OKC_API.G_RET_STS_ERROR',
2146 x_msg_count,
2147 x_msg_data,
2148 '_PVT'
2149 );
2150 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2151 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2152 (
2153 l_api_name,
2154 G_PKG_NAME,
2155 'OKC_API.G_RET_STS_UNEXP_ERROR',
2156 x_msg_count,
2157 x_msg_data,
2158 '_PVT'
2159 );
2160 WHEN OTHERS THEN
2161 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2162 (
2163 l_api_name,
2164 G_PKG_NAME,
2165 'OTHERS',
2166 x_msg_count,
2167 x_msg_data,
2168 '_PVT'
2169 );
2170 END insert_row;
2171
2172 ---------------------------------------------------------------------------
2173 -- PROCEDURE lock_row
2174 ---------------------------------------------------------------------------
2175 ------------------------------------
2176 -- lock_row for:OKC_RULE_GROUPS_B --
2177 ------------------------------------
2178 PROCEDURE lock_row(
2179 p_init_msg_list IN VARCHAR2,
2180 x_return_status OUT NOCOPY VARCHAR2,
2181 x_msg_count OUT NOCOPY NUMBER,
2182 x_msg_data OUT NOCOPY VARCHAR2,
2183 p_rgp_rec IN rgp_rec_type) IS
2184
2185 E_Resource_Busy EXCEPTION;
2186 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2187 CURSOR lock_csr (p_rgp_rec IN rgp_rec_type) IS
2188 SELECT OBJECT_VERSION_NUMBER
2189 FROM OKC_RULE_GROUPS_B
2190 WHERE ID = p_rgp_rec.id
2191 AND OBJECT_VERSION_NUMBER = p_rgp_rec.object_version_number
2192 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
2193
2194 CURSOR lchk_csr (p_rgp_rec IN rgp_rec_type) IS
2195 SELECT OBJECT_VERSION_NUMBER
2196 FROM OKC_RULE_GROUPS_B
2197 WHERE ID = p_rgp_rec.id;
2198 l_api_version CONSTANT NUMBER := 1;
2199 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
2200 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2201 l_object_version_number OKC_RULE_GROUPS_B.OBJECT_VERSION_NUMBER%TYPE;
2202 lc_object_version_number OKC_RULE_GROUPS_B.OBJECT_VERSION_NUMBER%TYPE;
2203 l_row_notfound BOOLEAN := FALSE;
2204 lc_row_notfound BOOLEAN := FALSE;
2205 BEGIN
2206 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2207 p_init_msg_list,
2208 '_PVT',
2209 x_return_status);
2210 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2211 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2212 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2213 RAISE OKC_API.G_EXCEPTION_ERROR;
2214 END IF;
2215 BEGIN
2216 OPEN lock_csr(p_rgp_rec);
2217 FETCH lock_csr INTO l_object_version_number;
2218 l_row_notfound := lock_csr%NOTFOUND;
2219 CLOSE lock_csr;
2220 EXCEPTION
2221 WHEN E_Resource_Busy THEN
2222 IF (lock_csr%ISOPEN) THEN
2223 CLOSE lock_csr;
2224 END IF;
2225 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2226 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2227 END;
2228
2229 IF ( l_row_notfound ) THEN
2230 OPEN lchk_csr(p_rgp_rec);
2231 FETCH lchk_csr INTO lc_object_version_number;
2232 lc_row_notfound := lchk_csr%NOTFOUND;
2233 CLOSE lchk_csr;
2234 END IF;
2235 IF (lc_row_notfound) THEN
2236 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2237 RAISE OKC_API.G_EXCEPTION_ERROR;
2238 ELSIF lc_object_version_number > p_rgp_rec.object_version_number THEN
2239 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2240 RAISE OKC_API.G_EXCEPTION_ERROR;
2241 ELSIF lc_object_version_number <> p_rgp_rec.object_version_number THEN
2242 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2243 RAISE OKC_API.G_EXCEPTION_ERROR;
2244 ELSIF lc_object_version_number = -1 THEN
2245 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2246 RAISE OKC_API.G_EXCEPTION_ERROR;
2247 END IF;
2248 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2249 EXCEPTION
2250 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2251 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2252 (
2253 l_api_name,
2254 G_PKG_NAME,
2255 'OKC_API.G_RET_STS_ERROR',
2256 x_msg_count,
2257 x_msg_data,
2258 '_PVT'
2259 );
2260 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2261 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2262 (
2263 l_api_name,
2264 G_PKG_NAME,
2265 'OKC_API.G_RET_STS_UNEXP_ERROR',
2266 x_msg_count,
2267 x_msg_data,
2268 '_PVT'
2269 );
2270 WHEN OTHERS THEN
2271 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2272 (
2273 l_api_name,
2274 G_PKG_NAME,
2275 'OTHERS',
2276 x_msg_count,
2277 x_msg_data,
2278 '_PVT'
2279 );
2280 END lock_row;
2281 -------------------------------------
2282 -- lock_row for:OKC_RULE_GROUPS_TL --
2283 -------------------------------------
2284 PROCEDURE lock_row(
2285 p_init_msg_list IN VARCHAR2,
2286 x_return_status OUT NOCOPY VARCHAR2,
2287 x_msg_count OUT NOCOPY NUMBER,
2288 x_msg_data OUT NOCOPY VARCHAR2,
2289 p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type) IS
2290
2291 E_Resource_Busy EXCEPTION;
2292 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2293 CURSOR lock_csr (p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type) IS
2294 SELECT *
2295 FROM OKC_RULE_GROUPS_TL
2296 WHERE ID = p_okc_rule_groups_tl_rec.id
2297 FOR UPDATE NOWAIT;
2298
2299 l_api_version CONSTANT NUMBER := 1;
2300 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
2301 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2302 l_lock_var lock_csr%ROWTYPE;
2303 l_row_notfound BOOLEAN := FALSE;
2304 lc_row_notfound BOOLEAN := FALSE;
2305 BEGIN
2306 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2307 p_init_msg_list,
2308 '_PVT',
2309 x_return_status);
2310 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2311 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2312 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2313 RAISE OKC_API.G_EXCEPTION_ERROR;
2314 END IF;
2315 BEGIN
2316 OPEN lock_csr(p_okc_rule_groups_tl_rec);
2317 FETCH lock_csr INTO l_lock_var;
2318 l_row_notfound := lock_csr%NOTFOUND;
2319 CLOSE lock_csr;
2320 EXCEPTION
2321 WHEN E_Resource_Busy THEN
2322 IF (lock_csr%ISOPEN) THEN
2323 CLOSE lock_csr;
2324 END IF;
2325 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2326 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2327 END;
2328
2329 IF ( l_row_notfound ) THEN
2330 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2331 RAISE OKC_API.G_EXCEPTION_ERROR;
2332 END IF;
2333 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2334 EXCEPTION
2335 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2336 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2337 (
2338 l_api_name,
2339 G_PKG_NAME,
2340 'OKC_API.G_RET_STS_ERROR',
2341 x_msg_count,
2342 x_msg_data,
2343 '_PVT'
2344 );
2345 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2346 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2347 (
2348 l_api_name,
2349 G_PKG_NAME,
2350 'OKC_API.G_RET_STS_UNEXP_ERROR',
2351 x_msg_count,
2352 x_msg_data,
2353 '_PVT'
2354 );
2355 WHEN OTHERS THEN
2356 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2357 (
2358 l_api_name,
2359 G_PKG_NAME,
2360 'OTHERS',
2361 x_msg_count,
2362 x_msg_data,
2363 '_PVT'
2364 );
2365 END lock_row;
2366 ------------------------------------
2367 -- lock_row for:OKC_RULE_GROUPS_V --
2368 ------------------------------------
2369 PROCEDURE lock_row(
2370 p_api_version IN NUMBER,
2371 p_init_msg_list IN VARCHAR2,
2372 x_return_status OUT NOCOPY VARCHAR2,
2373 x_msg_count OUT NOCOPY NUMBER,
2374 x_msg_data OUT NOCOPY VARCHAR2,
2375 p_rgpv_rec IN rgpv_rec_type) IS
2376
2377 l_api_version CONSTANT NUMBER := 1;
2378 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2379 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2380 l_rgp_rec rgp_rec_type;
2381 l_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
2382 BEGIN
2383 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2384 G_PKG_NAME,
2385 p_init_msg_list,
2386 l_api_version,
2387 p_api_version,
2388 '_PVT',
2389 x_return_status);
2390 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2391 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2392 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2393 RAISE OKC_API.G_EXCEPTION_ERROR;
2394 END IF;
2395 --------------------------------------
2396 -- Move VIEW record to "Child" records
2397 --------------------------------------
2398 migrate(p_rgpv_rec, l_rgp_rec);
2399 migrate(p_rgpv_rec, l_okc_rule_groups_tl_rec);
2400 --------------------------------------------
2401 -- Call the LOCK_ROW for each child record
2402 --------------------------------------------
2403 lock_row(
2404 p_init_msg_list,
2405 x_return_status,
2406 x_msg_count,
2407 x_msg_data,
2408 l_rgp_rec
2409 );
2410 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2411 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2412 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2413 RAISE OKC_API.G_EXCEPTION_ERROR;
2414 END IF;
2415 lock_row(
2416 p_init_msg_list,
2417 x_return_status,
2418 x_msg_count,
2419 x_msg_data,
2420 l_okc_rule_groups_tl_rec
2421 );
2422 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2423 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2424 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2425 RAISE OKC_API.G_EXCEPTION_ERROR;
2426 END IF;
2427 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2428 EXCEPTION
2429 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2430 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2431 (
2432 l_api_name,
2433 G_PKG_NAME,
2434 'OKC_API.G_RET_STS_ERROR',
2435 x_msg_count,
2436 x_msg_data,
2437 '_PVT'
2438 );
2439 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2440 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2441 (
2442 l_api_name,
2443 G_PKG_NAME,
2444 'OKC_API.G_RET_STS_UNEXP_ERROR',
2445 x_msg_count,
2446 x_msg_data,
2447 '_PVT'
2448 );
2449 WHEN OTHERS THEN
2450 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2451 (
2452 l_api_name,
2453 G_PKG_NAME,
2454 'OTHERS',
2455 x_msg_count,
2456 x_msg_data,
2457 '_PVT'
2458 );
2459 END lock_row;
2460 --------------------------------------
2461 -- PL/SQL TBL lock_row for:RGPV_TBL --
2462 --------------------------------------
2463 PROCEDURE lock_row(
2464 p_api_version IN NUMBER,
2465 p_init_msg_list IN VARCHAR2,
2466 x_return_status OUT NOCOPY VARCHAR2,
2467 x_msg_count OUT NOCOPY NUMBER,
2468 x_msg_data OUT NOCOPY VARCHAR2,
2469 p_rgpv_tbl IN rgpv_tbl_type) IS
2470
2471 l_api_version CONSTANT NUMBER := 1;
2472 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2473 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2474 i NUMBER := 0;
2475 BEGIN
2476 OKC_API.init_msg_list(p_init_msg_list);
2477 -- Make sure PL/SQL table has records in it before passing
2478 IF (p_rgpv_tbl.COUNT > 0) THEN
2479 i := p_rgpv_tbl.FIRST;
2480 LOOP
2481 lock_row (
2482 p_api_version => p_api_version,
2483 p_init_msg_list => OKC_API.G_FALSE,
2484 x_return_status => x_return_status,
2485 x_msg_count => x_msg_count,
2486 x_msg_data => x_msg_data,
2487 p_rgpv_rec => p_rgpv_tbl(i));
2488 EXIT WHEN (i = p_rgpv_tbl.LAST);
2489 i := p_rgpv_tbl.NEXT(i);
2490 END LOOP;
2491 END IF;
2492 EXCEPTION
2493 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2494 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2495 (
2496 l_api_name,
2497 G_PKG_NAME,
2498 'OKC_API.G_RET_STS_ERROR',
2499 x_msg_count,
2500 x_msg_data,
2501 '_PVT'
2502 );
2503 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2504 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2505 (
2506 l_api_name,
2507 G_PKG_NAME,
2508 'OKC_API.G_RET_STS_UNEXP_ERROR',
2509 x_msg_count,
2510 x_msg_data,
2511 '_PVT'
2512 );
2513 WHEN OTHERS THEN
2514 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2515 (
2516 l_api_name,
2517 G_PKG_NAME,
2518 'OTHERS',
2519 x_msg_count,
2520 x_msg_data,
2521 '_PVT'
2522 );
2523 END lock_row;
2524
2525 ---------------------------------------------------------------------------
2526 -- PROCEDURE update_row
2527 ---------------------------------------------------------------------------
2528 --------------------------------------
2529 -- update_row for:OKC_RULE_GROUPS_B --
2530 --------------------------------------
2531 PROCEDURE update_row(
2532 p_init_msg_list IN VARCHAR2,
2533 x_return_status OUT NOCOPY VARCHAR2,
2534 x_msg_count OUT NOCOPY NUMBER,
2535 x_msg_data OUT NOCOPY VARCHAR2,
2536 p_rgp_rec IN rgp_rec_type,
2537 x_rgp_rec OUT NOCOPY rgp_rec_type) IS
2538
2539 l_api_version CONSTANT NUMBER := 1;
2540 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
2541 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2542 l_rgp_rec rgp_rec_type := p_rgp_rec;
2543 l_def_rgp_rec rgp_rec_type;
2544 l_row_notfound BOOLEAN := TRUE;
2545 ----------------------------------
2546 -- FUNCTION populate_new_record --
2547 ----------------------------------
2548 FUNCTION populate_new_record (
2549 p_rgp_rec IN rgp_rec_type,
2550 x_rgp_rec OUT NOCOPY rgp_rec_type
2551 ) RETURN VARCHAR2 IS
2552 l_rgp_rec rgp_rec_type;
2553 l_row_notfound BOOLEAN := TRUE;
2554 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2555 BEGIN
2556 x_rgp_rec := p_rgp_rec;
2557 -- Get current database values
2558 l_rgp_rec := get_rec(p_rgp_rec, l_row_notfound);
2559 IF (l_row_notfound) THEN
2560 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2561 END IF;
2562 IF (x_rgp_rec.id = OKC_API.G_MISS_NUM)
2563 THEN
2564 x_rgp_rec.id := l_rgp_rec.id;
2565 END IF;
2566 IF (x_rgp_rec.rgd_code = OKC_API.G_MISS_CHAR)
2567 THEN
2568 x_rgp_rec.rgd_code := l_rgp_rec.rgd_code;
2569 END IF;
2570 IF (x_rgp_rec.sat_code = OKC_API.G_MISS_CHAR)
2571 THEN
2572 x_rgp_rec.sat_code := l_rgp_rec.sat_code;
2573 END IF;
2574 IF (x_rgp_rec.rgp_type = OKC_API.G_MISS_CHAR)
2575 THEN
2576 x_rgp_rec.rgp_type := l_rgp_rec.rgp_type;
2577 END IF;
2578 IF (x_rgp_rec.chr_id = OKC_API.G_MISS_NUM)
2579 THEN
2580 x_rgp_rec.chr_id := l_rgp_rec.chr_id;
2581 END IF;
2582 IF (x_rgp_rec.cle_id = OKC_API.G_MISS_NUM)
2583 THEN
2584 x_rgp_rec.cle_id := l_rgp_rec.cle_id;
2585 END IF;
2586 IF (x_rgp_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
2587 THEN
2588 x_rgp_rec.dnz_chr_id := l_rgp_rec.dnz_chr_id;
2589 END IF;
2590 IF (x_rgp_rec.parent_rgp_id = OKC_API.G_MISS_NUM)
2591 THEN
2592 x_rgp_rec.parent_rgp_id := l_rgp_rec.parent_rgp_id;
2593 END IF;
2594 IF (x_rgp_rec.object_version_number = OKC_API.G_MISS_NUM)
2595 THEN
2596 x_rgp_rec.object_version_number := l_rgp_rec.object_version_number;
2597 END IF;
2598 IF (x_rgp_rec.created_by = OKC_API.G_MISS_NUM)
2599 THEN
2600 x_rgp_rec.created_by := l_rgp_rec.created_by;
2601 END IF;
2602 IF (x_rgp_rec.creation_date = OKC_API.G_MISS_DATE)
2603 THEN
2604 x_rgp_rec.creation_date := l_rgp_rec.creation_date;
2605 END IF;
2606 IF (x_rgp_rec.last_updated_by = OKC_API.G_MISS_NUM)
2607 THEN
2608 x_rgp_rec.last_updated_by := l_rgp_rec.last_updated_by;
2609 END IF;
2610 IF (x_rgp_rec.last_update_date = OKC_API.G_MISS_DATE)
2611 THEN
2612 x_rgp_rec.last_update_date := l_rgp_rec.last_update_date;
2613 END IF;
2614 IF (x_rgp_rec.last_update_login = OKC_API.G_MISS_NUM)
2615 THEN
2616 x_rgp_rec.last_update_login := l_rgp_rec.last_update_login;
2617 END IF;
2618 IF (x_rgp_rec.attribute_category = OKC_API.G_MISS_CHAR)
2619 THEN
2620 x_rgp_rec.attribute_category := l_rgp_rec.attribute_category;
2621 END IF;
2622 IF (x_rgp_rec.attribute1 = OKC_API.G_MISS_CHAR)
2623 THEN
2624 x_rgp_rec.attribute1 := l_rgp_rec.attribute1;
2625 END IF;
2626 IF (x_rgp_rec.attribute2 = OKC_API.G_MISS_CHAR)
2627 THEN
2628 x_rgp_rec.attribute2 := l_rgp_rec.attribute2;
2629 END IF;
2630 IF (x_rgp_rec.attribute3 = OKC_API.G_MISS_CHAR)
2631 THEN
2632 x_rgp_rec.attribute3 := l_rgp_rec.attribute3;
2633 END IF;
2634 IF (x_rgp_rec.attribute4 = OKC_API.G_MISS_CHAR)
2635 THEN
2636 x_rgp_rec.attribute4 := l_rgp_rec.attribute4;
2637 END IF;
2638 IF (x_rgp_rec.attribute5 = OKC_API.G_MISS_CHAR)
2639 THEN
2640 x_rgp_rec.attribute5 := l_rgp_rec.attribute5;
2641 END IF;
2642 IF (x_rgp_rec.attribute6 = OKC_API.G_MISS_CHAR)
2643 THEN
2644 x_rgp_rec.attribute6 := l_rgp_rec.attribute6;
2645 END IF;
2646 IF (x_rgp_rec.attribute7 = OKC_API.G_MISS_CHAR)
2647 THEN
2648 x_rgp_rec.attribute7 := l_rgp_rec.attribute7;
2649 END IF;
2650 IF (x_rgp_rec.attribute8 = OKC_API.G_MISS_CHAR)
2651 THEN
2652 x_rgp_rec.attribute8 := l_rgp_rec.attribute8;
2653 END IF;
2654 IF (x_rgp_rec.attribute9 = OKC_API.G_MISS_CHAR)
2655 THEN
2656 x_rgp_rec.attribute9 := l_rgp_rec.attribute9;
2657 END IF;
2658 IF (x_rgp_rec.attribute10 = OKC_API.G_MISS_CHAR)
2659 THEN
2660 x_rgp_rec.attribute10 := l_rgp_rec.attribute10;
2661 END IF;
2662 IF (x_rgp_rec.attribute11 = OKC_API.G_MISS_CHAR)
2663 THEN
2664 x_rgp_rec.attribute11 := l_rgp_rec.attribute11;
2665 END IF;
2666 IF (x_rgp_rec.attribute12 = OKC_API.G_MISS_CHAR)
2667 THEN
2668 x_rgp_rec.attribute12 := l_rgp_rec.attribute12;
2669 END IF;
2670 IF (x_rgp_rec.attribute13 = OKC_API.G_MISS_CHAR)
2671 THEN
2672 x_rgp_rec.attribute13 := l_rgp_rec.attribute13;
2673 END IF;
2674 IF (x_rgp_rec.attribute14 = OKC_API.G_MISS_CHAR)
2675 THEN
2676 x_rgp_rec.attribute14 := l_rgp_rec.attribute14;
2677 END IF;
2678 IF (x_rgp_rec.attribute15 = OKC_API.G_MISS_CHAR)
2679 THEN
2680 x_rgp_rec.attribute15 := l_rgp_rec.attribute15;
2681 END IF;
2682 RETURN(l_return_status);
2683 END populate_new_record;
2684 ------------------------------------------
2685 -- Set_Attributes for:OKC_RULE_GROUPS_B --
2686 ------------------------------------------
2687 FUNCTION Set_Attributes (
2688 p_rgp_rec IN rgp_rec_type,
2689 x_rgp_rec OUT NOCOPY rgp_rec_type
2690 ) RETURN VARCHAR2 IS
2691 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2692 BEGIN
2693 x_rgp_rec := p_rgp_rec;
2694 RETURN(l_return_status);
2695 END Set_Attributes;
2696 BEGIN
2697 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2698 p_init_msg_list,
2699 '_PVT',
2700 x_return_status);
2701 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2702 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2703 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2704 RAISE OKC_API.G_EXCEPTION_ERROR;
2705 END IF;
2706 --- Setting item attributes
2707 l_return_status := Set_Attributes(
2708 p_rgp_rec, -- IN
2709 l_rgp_rec); -- OUT
2710 --- If any errors happen abort API
2711 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2712 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2713 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2714 RAISE OKC_API.G_EXCEPTION_ERROR;
2715 END IF;
2716 l_return_status := populate_new_record(l_rgp_rec, l_def_rgp_rec);
2717 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2718 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2719 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2720 RAISE OKC_API.G_EXCEPTION_ERROR;
2721 END IF;
2722 UPDATE OKC_RULE_GROUPS_B
2723 SET RGD_CODE = l_def_rgp_rec.rgd_code,
2724 SAT_CODE = l_def_rgp_rec.sat_code,
2725 RGP_TYPE = l_def_rgp_rec.rgp_type,
2726 CHR_ID = l_def_rgp_rec.chr_id,
2727 CLE_ID = l_def_rgp_rec.cle_id,
2728 DNZ_CHR_ID = l_def_rgp_rec.dnz_chr_id,
2729 PARENT_RGP_ID = l_def_rgp_rec.parent_rgp_id,
2730 OBJECT_VERSION_NUMBER = l_def_rgp_rec.object_version_number,
2731 CREATED_BY = l_def_rgp_rec.created_by,
2732 CREATION_DATE = l_def_rgp_rec.creation_date,
2733 LAST_UPDATED_BY = l_def_rgp_rec.last_updated_by,
2734 LAST_UPDATE_DATE = l_def_rgp_rec.last_update_date,
2735 LAST_UPDATE_LOGIN = l_def_rgp_rec.last_update_login,
2736 ATTRIBUTE_CATEGORY = l_def_rgp_rec.attribute_category,
2737 ATTRIBUTE1 = l_def_rgp_rec.attribute1,
2738 ATTRIBUTE2 = l_def_rgp_rec.attribute2,
2739 ATTRIBUTE3 = l_def_rgp_rec.attribute3,
2740 ATTRIBUTE4 = l_def_rgp_rec.attribute4,
2741 ATTRIBUTE5 = l_def_rgp_rec.attribute5,
2742 ATTRIBUTE6 = l_def_rgp_rec.attribute6,
2743 ATTRIBUTE7 = l_def_rgp_rec.attribute7,
2744 ATTRIBUTE8 = l_def_rgp_rec.attribute8,
2745 ATTRIBUTE9 = l_def_rgp_rec.attribute9,
2746 ATTRIBUTE10 = l_def_rgp_rec.attribute10,
2747 ATTRIBUTE11 = l_def_rgp_rec.attribute11,
2748 ATTRIBUTE12 = l_def_rgp_rec.attribute12,
2749 ATTRIBUTE13 = l_def_rgp_rec.attribute13,
2750 ATTRIBUTE14 = l_def_rgp_rec.attribute14,
2751 ATTRIBUTE15 = l_def_rgp_rec.attribute15
2752 WHERE ID = l_def_rgp_rec.id;
2753
2754 x_rgp_rec := l_def_rgp_rec;
2755 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2756 EXCEPTION
2757 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2758 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2759 (
2760 l_api_name,
2761 G_PKG_NAME,
2762 'OKC_API.G_RET_STS_ERROR',
2763 x_msg_count,
2764 x_msg_data,
2765 '_PVT'
2766 );
2767 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2768 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2769 (
2770 l_api_name,
2771 G_PKG_NAME,
2772 'OKC_API.G_RET_STS_UNEXP_ERROR',
2773 x_msg_count,
2774 x_msg_data,
2775 '_PVT'
2776 );
2777 WHEN OTHERS THEN
2778 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2779 (
2780 l_api_name,
2781 G_PKG_NAME,
2782 'OTHERS',
2783 x_msg_count,
2784 x_msg_data,
2785 '_PVT'
2786 );
2787 END update_row;
2788 ---------------------------------------
2789 -- update_row for:OKC_RULE_GROUPS_TL --
2790 ---------------------------------------
2791 PROCEDURE update_row(
2792 p_init_msg_list IN VARCHAR2,
2793 x_return_status OUT NOCOPY VARCHAR2,
2794 x_msg_count OUT NOCOPY NUMBER,
2795 x_msg_data OUT NOCOPY VARCHAR2,
2796 p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type,
2797 x_okc_rule_groups_tl_rec OUT NOCOPY okc_rule_groups_tl_rec_type) IS
2798
2799 l_api_version CONSTANT NUMBER := 1;
2800 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
2801 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2802 l_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type := p_okc_rule_groups_tl_rec;
2803 l_def_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
2804 l_row_notfound BOOLEAN := TRUE;
2805 ----------------------------------
2806 -- FUNCTION populate_new_record --
2807 ----------------------------------
2808 FUNCTION populate_new_record (
2809 p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type,
2810 x_okc_rule_groups_tl_rec OUT NOCOPY okc_rule_groups_tl_rec_type
2811 ) RETURN VARCHAR2 IS
2812 l_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
2813 l_row_notfound BOOLEAN := TRUE;
2814 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2815 BEGIN
2816 x_okc_rule_groups_tl_rec := p_okc_rule_groups_tl_rec;
2817 -- Get current database values
2818 l_okc_rule_groups_tl_rec := get_rec(p_okc_rule_groups_tl_rec, l_row_notfound);
2819 IF (l_row_notfound) THEN
2820 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2821 END IF;
2822 IF (x_okc_rule_groups_tl_rec.id = OKC_API.G_MISS_NUM)
2823 THEN
2824 x_okc_rule_groups_tl_rec.id := l_okc_rule_groups_tl_rec.id;
2825 END IF;
2826 IF (x_okc_rule_groups_tl_rec.language = OKC_API.G_MISS_CHAR)
2827 THEN
2828 x_okc_rule_groups_tl_rec.language := l_okc_rule_groups_tl_rec.language;
2829 END IF;
2830 IF (x_okc_rule_groups_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
2831 THEN
2832 x_okc_rule_groups_tl_rec.source_lang := l_okc_rule_groups_tl_rec.source_lang;
2833 END IF;
2834 IF (x_okc_rule_groups_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
2835 THEN
2836 x_okc_rule_groups_tl_rec.sfwt_flag := l_okc_rule_groups_tl_rec.sfwt_flag;
2837 END IF;
2838 IF (x_okc_rule_groups_tl_rec.comments = OKC_API.G_MISS_CHAR)
2839 THEN
2840 x_okc_rule_groups_tl_rec.comments := l_okc_rule_groups_tl_rec.comments;
2841 END IF;
2842 IF (x_okc_rule_groups_tl_rec.created_by = OKC_API.G_MISS_NUM)
2843 THEN
2844 x_okc_rule_groups_tl_rec.created_by := l_okc_rule_groups_tl_rec.created_by;
2845 END IF;
2846 IF (x_okc_rule_groups_tl_rec.creation_date = OKC_API.G_MISS_DATE)
2847 THEN
2848 x_okc_rule_groups_tl_rec.creation_date := l_okc_rule_groups_tl_rec.creation_date;
2849 END IF;
2850 IF (x_okc_rule_groups_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
2851 THEN
2852 x_okc_rule_groups_tl_rec.last_updated_by := l_okc_rule_groups_tl_rec.last_updated_by;
2853 END IF;
2854 IF (x_okc_rule_groups_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
2855 THEN
2856 x_okc_rule_groups_tl_rec.last_update_date := l_okc_rule_groups_tl_rec.last_update_date;
2857 END IF;
2858 IF (x_okc_rule_groups_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
2859 THEN
2860 x_okc_rule_groups_tl_rec.last_update_login := l_okc_rule_groups_tl_rec.last_update_login;
2861 END IF;
2862 RETURN(l_return_status);
2863 END populate_new_record;
2864 -------------------------------------------
2865 -- Set_Attributes for:OKC_RULE_GROUPS_TL --
2866 -------------------------------------------
2867 FUNCTION Set_Attributes (
2868 p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type,
2869 x_okc_rule_groups_tl_rec OUT NOCOPY okc_rule_groups_tl_rec_type
2870 ) RETURN VARCHAR2 IS
2871 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2872 BEGIN
2873 x_okc_rule_groups_tl_rec := p_okc_rule_groups_tl_rec;
2874 x_okc_rule_groups_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
2875 x_okc_rule_groups_tl_rec.SOURCE_LANG := okc_util.get_userenv_lang;
2876 RETURN(l_return_status);
2877 END Set_Attributes;
2878 BEGIN
2879 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2880 p_init_msg_list,
2881 '_PVT',
2882 x_return_status);
2883 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2884 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2885 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2886 RAISE OKC_API.G_EXCEPTION_ERROR;
2887 END IF;
2888 --- Setting item attributes
2889 l_return_status := Set_Attributes(
2890 p_okc_rule_groups_tl_rec, -- IN
2891 l_okc_rule_groups_tl_rec); -- OUT
2892 --- If any errors happen abort API
2893 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2894 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2895 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2896 RAISE OKC_API.G_EXCEPTION_ERROR;
2897 END IF;
2898 l_return_status := populate_new_record(l_okc_rule_groups_tl_rec, l_def_okc_rule_groups_tl_rec);
2899 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2900 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2901 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2902 RAISE OKC_API.G_EXCEPTION_ERROR;
2903 END IF;
2904 UPDATE OKC_RULE_GROUPS_TL
2905 SET COMMENTS = l_def_okc_rule_groups_tl_rec.comments,
2906 CREATED_BY = l_def_okc_rule_groups_tl_rec.created_by,
2907 CREATION_DATE = l_def_okc_rule_groups_tl_rec.creation_date,
2908 LAST_UPDATED_BY = l_def_okc_rule_groups_tl_rec.last_updated_by,
2909 LAST_UPDATE_DATE = l_def_okc_rule_groups_tl_rec.last_update_date,
2910 LAST_UPDATE_LOGIN = l_def_okc_rule_groups_tl_rec.last_update_login
2911 WHERE ID = l_def_okc_rule_groups_tl_rec.id
2912 AND SOURCE_LANG = USERENV('LANG');
2913
2914 UPDATE OKC_RULE_GROUPS_TL
2915 SET SFWT_FLAG = 'Y'
2916 WHERE ID = l_def_okc_rule_groups_tl_rec.id
2917 AND SOURCE_LANG <> USERENV('LANG');
2918
2919 x_okc_rule_groups_tl_rec := l_def_okc_rule_groups_tl_rec;
2920 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2921 EXCEPTION
2922 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2923 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2924 (
2925 l_api_name,
2926 G_PKG_NAME,
2927 'OKC_API.G_RET_STS_ERROR',
2928 x_msg_count,
2929 x_msg_data,
2930 '_PVT'
2931 );
2932 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2933 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2934 (
2935 l_api_name,
2936 G_PKG_NAME,
2937 'OKC_API.G_RET_STS_UNEXP_ERROR',
2938 x_msg_count,
2939 x_msg_data,
2940 '_PVT'
2941 );
2942 WHEN OTHERS THEN
2943 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2944 (
2945 l_api_name,
2946 G_PKG_NAME,
2947 'OTHERS',
2948 x_msg_count,
2949 x_msg_data,
2950 '_PVT'
2951 );
2952 END update_row;
2953 --------------------------------------
2954 -- update_row for:OKC_RULE_GROUPS_V --
2955 --------------------------------------
2956 PROCEDURE update_row(
2957 p_api_version IN NUMBER,
2958 p_init_msg_list IN VARCHAR2,
2959 x_return_status OUT NOCOPY VARCHAR2,
2960 x_msg_count OUT NOCOPY NUMBER,
2961 x_msg_data OUT NOCOPY VARCHAR2,
2962 p_rgpv_rec IN rgpv_rec_type,
2963 x_rgpv_rec OUT NOCOPY rgpv_rec_type) IS
2964
2965 l_api_version CONSTANT NUMBER := 1;
2966 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2967 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2968 l_rgpv_rec rgpv_rec_type := p_rgpv_rec;
2969 l_def_rgpv_rec rgpv_rec_type;
2970 l_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
2971 lx_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
2972 l_rgp_rec rgp_rec_type;
2973 lx_rgp_rec rgp_rec_type;
2974 -------------------------------
2975 -- FUNCTION fill_who_columns --
2976 -------------------------------
2977 FUNCTION fill_who_columns (
2978 p_rgpv_rec IN rgpv_rec_type
2979 ) RETURN rgpv_rec_type IS
2980 l_rgpv_rec rgpv_rec_type := p_rgpv_rec;
2981 BEGIN
2982 l_rgpv_rec.LAST_UPDATE_DATE := SYSDATE;
2983 l_rgpv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2984 l_rgpv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2985 RETURN(l_rgpv_rec);
2986 END fill_who_columns;
2987 ----------------------------------
2988 -- FUNCTION populate_new_record --
2989 ----------------------------------
2990 FUNCTION populate_new_record (
2991 p_rgpv_rec IN rgpv_rec_type,
2992 x_rgpv_rec OUT NOCOPY rgpv_rec_type
2993 ) RETURN VARCHAR2 IS
2994 l_rgpv_rec rgpv_rec_type;
2995 l_row_notfound BOOLEAN := TRUE;
2996 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2997 BEGIN
2998 x_rgpv_rec := p_rgpv_rec;
2999 -- Get current database values
3000 l_rgpv_rec := get_rec(p_rgpv_rec, l_row_notfound);
3001 IF (l_row_notfound) THEN
3002 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3003 END IF;
3004 IF (x_rgpv_rec.id = OKC_API.G_MISS_NUM)
3005 THEN
3006 x_rgpv_rec.id := l_rgpv_rec.id;
3007 END IF;
3008 IF (x_rgpv_rec.object_version_number = OKC_API.G_MISS_NUM)
3009 THEN
3010 x_rgpv_rec.object_version_number := l_rgpv_rec.object_version_number;
3011 END IF;
3012 IF (x_rgpv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
3013 THEN
3014 x_rgpv_rec.sfwt_flag := l_rgpv_rec.sfwt_flag;
3015 END IF;
3016 IF (x_rgpv_rec.rgd_code = OKC_API.G_MISS_CHAR)
3017 THEN
3018 x_rgpv_rec.rgd_code := l_rgpv_rec.rgd_code;
3019 END IF;
3020 IF (x_rgpv_rec.sat_code = OKC_API.G_MISS_CHAR)
3021 THEN
3022 x_rgpv_rec.sat_code := l_rgpv_rec.sat_code;
3023 END IF;
3024 IF (x_rgpv_rec.rgp_type = OKC_API.G_MISS_CHAR)
3025 THEN
3026 x_rgpv_rec.rgp_type := l_rgpv_rec.rgp_type;
3027 END IF;
3028 IF (x_rgpv_rec.cle_id = OKC_API.G_MISS_NUM)
3029 THEN
3030 x_rgpv_rec.cle_id := l_rgpv_rec.cle_id;
3031 END IF;
3032 IF (x_rgpv_rec.chr_id = OKC_API.G_MISS_NUM)
3033 THEN
3034 x_rgpv_rec.chr_id := l_rgpv_rec.chr_id;
3035 END IF;
3036 IF (x_rgpv_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
3037 THEN
3038 x_rgpv_rec.dnz_chr_id := l_rgpv_rec.dnz_chr_id;
3039 END IF;
3040 IF (x_rgpv_rec.parent_rgp_id = OKC_API.G_MISS_NUM)
3041 THEN
3042 x_rgpv_rec.parent_rgp_id := l_rgpv_rec.parent_rgp_id;
3043 END IF;
3044 IF (x_rgpv_rec.comments = OKC_API.G_MISS_CHAR)
3045 THEN
3046 x_rgpv_rec.comments := l_rgpv_rec.comments;
3047 END IF;
3048 IF (x_rgpv_rec.attribute_category = OKC_API.G_MISS_CHAR)
3049 THEN
3050 x_rgpv_rec.attribute_category := l_rgpv_rec.attribute_category;
3051 END IF;
3052 IF (x_rgpv_rec.attribute1 = OKC_API.G_MISS_CHAR)
3053 THEN
3054 x_rgpv_rec.attribute1 := l_rgpv_rec.attribute1;
3055 END IF;
3056 IF (x_rgpv_rec.attribute2 = OKC_API.G_MISS_CHAR)
3057 THEN
3058 x_rgpv_rec.attribute2 := l_rgpv_rec.attribute2;
3059 END IF;
3060 IF (x_rgpv_rec.attribute3 = OKC_API.G_MISS_CHAR)
3061 THEN
3062 x_rgpv_rec.attribute3 := l_rgpv_rec.attribute3;
3063 END IF;
3064 IF (x_rgpv_rec.attribute4 = OKC_API.G_MISS_CHAR)
3065 THEN
3066 x_rgpv_rec.attribute4 := l_rgpv_rec.attribute4;
3067 END IF;
3068 IF (x_rgpv_rec.attribute5 = OKC_API.G_MISS_CHAR)
3069 THEN
3070 x_rgpv_rec.attribute5 := l_rgpv_rec.attribute5;
3071 END IF;
3072 IF (x_rgpv_rec.attribute6 = OKC_API.G_MISS_CHAR)
3073 THEN
3074 x_rgpv_rec.attribute6 := l_rgpv_rec.attribute6;
3075 END IF;
3076 IF (x_rgpv_rec.attribute7 = OKC_API.G_MISS_CHAR)
3077 THEN
3078 x_rgpv_rec.attribute7 := l_rgpv_rec.attribute7;
3079 END IF;
3080 IF (x_rgpv_rec.attribute8 = OKC_API.G_MISS_CHAR)
3081 THEN
3082 x_rgpv_rec.attribute8 := l_rgpv_rec.attribute8;
3083 END IF;
3084 IF (x_rgpv_rec.attribute9 = OKC_API.G_MISS_CHAR)
3085 THEN
3086 x_rgpv_rec.attribute9 := l_rgpv_rec.attribute9;
3087 END IF;
3088 IF (x_rgpv_rec.attribute10 = OKC_API.G_MISS_CHAR)
3089 THEN
3090 x_rgpv_rec.attribute10 := l_rgpv_rec.attribute10;
3091 END IF;
3092 IF (x_rgpv_rec.attribute11 = OKC_API.G_MISS_CHAR)
3093 THEN
3094 x_rgpv_rec.attribute11 := l_rgpv_rec.attribute11;
3095 END IF;
3096 IF (x_rgpv_rec.attribute12 = OKC_API.G_MISS_CHAR)
3097 THEN
3098 x_rgpv_rec.attribute12 := l_rgpv_rec.attribute12;
3099 END IF;
3100 IF (x_rgpv_rec.attribute13 = OKC_API.G_MISS_CHAR)
3101 THEN
3102 x_rgpv_rec.attribute13 := l_rgpv_rec.attribute13;
3103 END IF;
3104 IF (x_rgpv_rec.attribute14 = OKC_API.G_MISS_CHAR)
3105 THEN
3106 x_rgpv_rec.attribute14 := l_rgpv_rec.attribute14;
3107 END IF;
3108 IF (x_rgpv_rec.attribute15 = OKC_API.G_MISS_CHAR)
3109 THEN
3110 x_rgpv_rec.attribute15 := l_rgpv_rec.attribute15;
3111 END IF;
3112 IF (x_rgpv_rec.created_by = OKC_API.G_MISS_NUM)
3113 THEN
3114 x_rgpv_rec.created_by := l_rgpv_rec.created_by;
3115 END IF;
3116 IF (x_rgpv_rec.creation_date = OKC_API.G_MISS_DATE)
3117 THEN
3118 x_rgpv_rec.creation_date := l_rgpv_rec.creation_date;
3119 END IF;
3120 IF (x_rgpv_rec.last_updated_by = OKC_API.G_MISS_NUM)
3121 THEN
3122 x_rgpv_rec.last_updated_by := l_rgpv_rec.last_updated_by;
3123 END IF;
3124 IF (x_rgpv_rec.last_update_date = OKC_API.G_MISS_DATE)
3125 THEN
3126 x_rgpv_rec.last_update_date := l_rgpv_rec.last_update_date;
3127 END IF;
3128 IF (x_rgpv_rec.last_update_login = OKC_API.G_MISS_NUM)
3129 THEN
3130 x_rgpv_rec.last_update_login := l_rgpv_rec.last_update_login;
3131 END IF;
3132 RETURN(l_return_status);
3133 END populate_new_record;
3134 ------------------------------------------
3135 -- Set_Attributes for:OKC_RULE_GROUPS_V --
3136 ------------------------------------------
3137 FUNCTION Set_Attributes (
3138 p_rgpv_rec IN rgpv_rec_type,
3139 x_rgpv_rec OUT NOCOPY rgpv_rec_type
3140 ) RETURN VARCHAR2 IS
3141 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3142 BEGIN
3143 x_rgpv_rec := p_rgpv_rec;
3144 x_rgpv_rec.OBJECT_VERSION_NUMBER := NVL(x_rgpv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
3145 RETURN(l_return_status);
3146 END Set_Attributes;
3147 BEGIN
3148 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3149 G_PKG_NAME,
3150 p_init_msg_list,
3151 l_api_version,
3152 p_api_version,
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_rgpv_rec, -- IN
3163 l_rgpv_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 l_return_status := populate_new_record(l_rgpv_rec, l_def_rgpv_rec);
3171 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3172 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3173 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3174 RAISE OKC_API.G_EXCEPTION_ERROR;
3175 END IF;
3176 l_def_rgpv_rec := fill_who_columns(l_def_rgpv_rec);
3177 --- Validate all non-missing attributes (Item Level Validation)
3178 l_return_status := Validate_Attributes(l_def_rgpv_rec);
3179 --- If any errors happen abort API
3180 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3181 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3182 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3183 RAISE OKC_API.G_EXCEPTION_ERROR;
3184 END IF;
3185 l_return_status := Validate_Record(l_def_rgpv_rec);
3186 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3187 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3188 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3189 RAISE OKC_API.G_EXCEPTION_ERROR;
3190 END IF;
3191
3192 --------------------------------------
3193 -- Move VIEW record to "Child" records
3194 --------------------------------------
3195 migrate(l_def_rgpv_rec, l_okc_rule_groups_tl_rec);
3196 migrate(l_def_rgpv_rec, l_rgp_rec);
3197 --------------------------------------------
3198 -- Call the UPDATE_ROW for each child record
3199 --------------------------------------------
3200 update_row(
3201 p_init_msg_list,
3202 x_return_status,
3203 x_msg_count,
3204 x_msg_data,
3205 l_okc_rule_groups_tl_rec,
3206 lx_okc_rule_groups_tl_rec
3207 );
3208 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3209 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3210 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3211 RAISE OKC_API.G_EXCEPTION_ERROR;
3212 END IF;
3213 migrate(lx_okc_rule_groups_tl_rec, l_def_rgpv_rec);
3214 update_row(
3215 p_init_msg_list,
3216 x_return_status,
3217 x_msg_count,
3218 x_msg_data,
3219 l_rgp_rec,
3220 lx_rgp_rec
3221 );
3222 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3223 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3224 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3225 RAISE OKC_API.G_EXCEPTION_ERROR;
3226 END IF;
3227 migrate(lx_rgp_rec, l_def_rgpv_rec);
3228 x_rgpv_rec := l_def_rgpv_rec;
3229 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3230 EXCEPTION
3231 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3232 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3233 (
3234 l_api_name,
3235 G_PKG_NAME,
3236 'OKC_API.G_RET_STS_ERROR',
3237 x_msg_count,
3238 x_msg_data,
3239 '_PVT'
3240 );
3241 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3242 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3243 (
3244 l_api_name,
3245 G_PKG_NAME,
3246 'OKC_API.G_RET_STS_UNEXP_ERROR',
3247 x_msg_count,
3248 x_msg_data,
3249 '_PVT'
3250 );
3251 WHEN OTHERS THEN
3252 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3253 (
3254 l_api_name,
3255 G_PKG_NAME,
3256 'OTHERS',
3257 x_msg_count,
3258 x_msg_data,
3259 '_PVT'
3260 );
3261 END update_row;
3262 ----------------------------------------
3263 -- PL/SQL TBL update_row for:RGPV_TBL --
3264 ----------------------------------------
3265 PROCEDURE update_row(
3266 p_api_version IN NUMBER,
3267 p_init_msg_list IN VARCHAR2,
3268 x_return_status OUT NOCOPY VARCHAR2,
3269 x_msg_count OUT NOCOPY NUMBER,
3270 x_msg_data OUT NOCOPY VARCHAR2,
3271 p_rgpv_tbl IN rgpv_tbl_type,
3272 x_rgpv_tbl OUT NOCOPY rgpv_tbl_type) IS
3273
3274 l_api_version CONSTANT NUMBER := 1;
3275 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
3276 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3277 i NUMBER := 0;
3278 BEGIN
3279 OKC_API.init_msg_list(p_init_msg_list);
3280 -- Make sure PL/SQL table has records in it before passing
3281 IF (p_rgpv_tbl.COUNT > 0) THEN
3282 i := p_rgpv_tbl.FIRST;
3283 LOOP
3284 update_row (
3285 p_api_version => p_api_version,
3286 p_init_msg_list => OKC_API.G_FALSE,
3287 x_return_status => x_return_status,
3288 x_msg_count => x_msg_count,
3289 x_msg_data => x_msg_data,
3290 p_rgpv_rec => p_rgpv_tbl(i),
3291 x_rgpv_rec => x_rgpv_tbl(i));
3292 EXIT WHEN (i = p_rgpv_tbl.LAST);
3293 i := p_rgpv_tbl.NEXT(i);
3294 END LOOP;
3295 END IF;
3296 EXCEPTION
3297 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3298 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3299 (
3300 l_api_name,
3301 G_PKG_NAME,
3302 'OKC_API.G_RET_STS_ERROR',
3303 x_msg_count,
3304 x_msg_data,
3305 '_PVT'
3306 );
3307 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3308 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3309 (
3310 l_api_name,
3311 G_PKG_NAME,
3312 'OKC_API.G_RET_STS_UNEXP_ERROR',
3313 x_msg_count,
3314 x_msg_data,
3315 '_PVT'
3316 );
3317 WHEN OTHERS THEN
3318 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3319 (
3320 l_api_name,
3321 G_PKG_NAME,
3322 'OTHERS',
3323 x_msg_count,
3324 x_msg_data,
3325 '_PVT'
3326 );
3327 END update_row;
3328
3329 ---------------------------------------------------------------------------
3330 -- PROCEDURE delete_row
3331 ---------------------------------------------------------------------------
3332 --------------------------------------
3333 -- delete_row for:OKC_RULE_GROUPS_B --
3334 --------------------------------------
3335 PROCEDURE delete_row(
3336 p_init_msg_list IN VARCHAR2,
3337 x_return_status OUT NOCOPY VARCHAR2,
3338 x_msg_count OUT NOCOPY NUMBER,
3339 x_msg_data OUT NOCOPY VARCHAR2,
3340 p_rgp_rec IN rgp_rec_type) IS
3341
3342 l_api_version CONSTANT NUMBER := 1;
3343 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
3344 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3345 l_rgp_rec rgp_rec_type:= p_rgp_rec;
3346 l_row_notfound BOOLEAN := TRUE;
3347 BEGIN
3348 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3349 p_init_msg_list,
3350 '_PVT',
3351 x_return_status);
3352 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3353 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3354 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3355 RAISE OKC_API.G_EXCEPTION_ERROR;
3356 END IF;
3357 DELETE FROM OKC_RULE_GROUPS_B
3358 WHERE ID = l_rgp_rec.id;
3359
3360 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3361 EXCEPTION
3362 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3363 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3364 (
3365 l_api_name,
3366 G_PKG_NAME,
3367 'OKC_API.G_RET_STS_ERROR',
3368 x_msg_count,
3369 x_msg_data,
3370 '_PVT'
3371 );
3372 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3373 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3374 (
3375 l_api_name,
3376 G_PKG_NAME,
3377 'OKC_API.G_RET_STS_UNEXP_ERROR',
3378 x_msg_count,
3379 x_msg_data,
3380 '_PVT'
3381 );
3382 WHEN OTHERS THEN
3383 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3384 (
3385 l_api_name,
3386 G_PKG_NAME,
3387 'OTHERS',
3388 x_msg_count,
3389 x_msg_data,
3390 '_PVT'
3391 );
3392 END delete_row;
3393 ---------------------------------------
3394 -- delete_row for:OKC_RULE_GROUPS_TL --
3395 ---------------------------------------
3396 PROCEDURE delete_row(
3397 p_init_msg_list IN VARCHAR2,
3398 x_return_status OUT NOCOPY VARCHAR2,
3399 x_msg_count OUT NOCOPY NUMBER,
3400 x_msg_data OUT NOCOPY VARCHAR2,
3401 p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type) IS
3402
3403 l_api_version CONSTANT NUMBER := 1;
3404 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
3405 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3406 l_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type:= p_okc_rule_groups_tl_rec;
3407 l_row_notfound BOOLEAN := TRUE;
3408 -------------------------------------------
3409 -- Set_Attributes for:OKC_RULE_GROUPS_TL --
3410 -------------------------------------------
3411 FUNCTION Set_Attributes (
3412 p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type,
3413 x_okc_rule_groups_tl_rec OUT NOCOPY okc_rule_groups_tl_rec_type
3414 ) RETURN VARCHAR2 IS
3415 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3416 BEGIN
3417 x_okc_rule_groups_tl_rec := p_okc_rule_groups_tl_rec;
3418 x_okc_rule_groups_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
3419 RETURN(l_return_status);
3420 END Set_Attributes;
3421 BEGIN
3422 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3423 p_init_msg_list,
3424 '_PVT',
3425 x_return_status);
3426 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3427 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3428 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3429 RAISE OKC_API.G_EXCEPTION_ERROR;
3430 END IF;
3431 --- Setting item attributes
3432 l_return_status := Set_Attributes(
3433 p_okc_rule_groups_tl_rec, -- IN
3434 l_okc_rule_groups_tl_rec); -- OUT
3435 --- If any errors happen abort API
3436 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3437 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3438 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3439 RAISE OKC_API.G_EXCEPTION_ERROR;
3440 END IF;
3441 DELETE FROM OKC_RULE_GROUPS_TL
3442 WHERE ID = l_okc_rule_groups_tl_rec.id;
3443
3444 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3445 EXCEPTION
3446 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3447 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3448 (
3449 l_api_name,
3450 G_PKG_NAME,
3451 'OKC_API.G_RET_STS_ERROR',
3452 x_msg_count,
3453 x_msg_data,
3454 '_PVT'
3455 );
3456 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3457 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3458 (
3459 l_api_name,
3460 G_PKG_NAME,
3461 'OKC_API.G_RET_STS_UNEXP_ERROR',
3462 x_msg_count,
3463 x_msg_data,
3464 '_PVT'
3465 );
3466 WHEN OTHERS THEN
3467 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3468 (
3469 l_api_name,
3470 G_PKG_NAME,
3471 'OTHERS',
3472 x_msg_count,
3473 x_msg_data,
3474 '_PVT'
3475 );
3476 END delete_row;
3477 --------------------------------------
3478 -- delete_row for:OKC_RULE_GROUPS_V --
3479 --------------------------------------
3480 PROCEDURE delete_row(
3481 p_api_version IN NUMBER,
3482 p_init_msg_list IN VARCHAR2,
3483 x_return_status OUT NOCOPY VARCHAR2,
3484 x_msg_count OUT NOCOPY NUMBER,
3485 x_msg_data OUT NOCOPY VARCHAR2,
3486 p_rgpv_rec IN rgpv_rec_type) IS
3487
3488 l_api_version CONSTANT NUMBER := 1;
3489 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3490 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3491 l_rgpv_rec rgpv_rec_type := p_rgpv_rec;
3492 l_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
3493 l_rgp_rec rgp_rec_type;
3494 BEGIN
3495 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3496 G_PKG_NAME,
3497 p_init_msg_list,
3498 l_api_version,
3499 p_api_version,
3500 '_PVT',
3501 x_return_status);
3502 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3503 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3504 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3505 RAISE OKC_API.G_EXCEPTION_ERROR;
3506 END IF;
3507 --------------------------------------
3508 -- Move VIEW record to "Child" records
3509 --------------------------------------
3510 migrate(l_rgpv_rec, l_okc_rule_groups_tl_rec);
3511 migrate(l_rgpv_rec, l_rgp_rec);
3512 --------------------------------------------
3513 -- Call the DELETE_ROW for each child record
3514 --------------------------------------------
3515 delete_row(
3516 p_init_msg_list,
3517 x_return_status,
3518 x_msg_count,
3519 x_msg_data,
3520 l_okc_rule_groups_tl_rec
3521 );
3522 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3523 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3524 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3525 RAISE OKC_API.G_EXCEPTION_ERROR;
3526 END IF;
3527 delete_row(
3528 p_init_msg_list,
3529 x_return_status,
3530 x_msg_count,
3531 x_msg_data,
3532 l_rgp_rec
3533 );
3534 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3535 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3536 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3537 RAISE OKC_API.G_EXCEPTION_ERROR;
3538 END IF;
3539 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3540 EXCEPTION
3541 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3542 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3543 (
3544 l_api_name,
3545 G_PKG_NAME,
3546 'OKC_API.G_RET_STS_ERROR',
3547 x_msg_count,
3548 x_msg_data,
3549 '_PVT'
3550 );
3551 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3552 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3553 (
3554 l_api_name,
3555 G_PKG_NAME,
3556 'OKC_API.G_RET_STS_UNEXP_ERROR',
3557 x_msg_count,
3558 x_msg_data,
3559 '_PVT'
3560 );
3561 WHEN OTHERS THEN
3562 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3563 (
3564 l_api_name,
3565 G_PKG_NAME,
3566 'OTHERS',
3567 x_msg_count,
3568 x_msg_data,
3569 '_PVT'
3570 );
3571 END delete_row;
3572 ----------------------------------------
3573 -- PL/SQL TBL delete_row for:RGPV_TBL --
3574 ----------------------------------------
3575 PROCEDURE delete_row(
3576 p_api_version IN NUMBER,
3577 p_init_msg_list IN VARCHAR2,
3578 x_return_status OUT NOCOPY VARCHAR2,
3579 x_msg_count OUT NOCOPY NUMBER,
3580 x_msg_data OUT NOCOPY VARCHAR2,
3581 p_rgpv_tbl IN rgpv_tbl_type) IS
3582
3583 l_api_version CONSTANT NUMBER := 1;
3584 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3585 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3586 i NUMBER := 0;
3587 BEGIN
3588 OKC_API.init_msg_list(p_init_msg_list);
3589 -- Make sure PL/SQL table has records in it before passing
3590 IF (p_rgpv_tbl.COUNT > 0) THEN
3591 i := p_rgpv_tbl.FIRST;
3592 LOOP
3593 delete_row (
3594 p_api_version => p_api_version,
3595 p_init_msg_list => OKC_API.G_FALSE,
3596 x_return_status => x_return_status,
3597 x_msg_count => x_msg_count,
3598 x_msg_data => x_msg_data,
3599 p_rgpv_rec => p_rgpv_tbl(i));
3600 EXIT WHEN (i = p_rgpv_tbl.LAST);
3601 i := p_rgpv_tbl.NEXT(i);
3602 END LOOP;
3603 END IF;
3604 EXCEPTION
3605 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3606 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3607 (
3608 l_api_name,
3609 G_PKG_NAME,
3610 'OKC_API.G_RET_STS_ERROR',
3611 x_msg_count,
3612 x_msg_data,
3613 '_PVT'
3614 );
3615 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3616 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3617 (
3618 l_api_name,
3619 G_PKG_NAME,
3620 'OKC_API.G_RET_STS_UNEXP_ERROR',
3621 x_msg_count,
3622 x_msg_data,
3623 '_PVT'
3624 );
3625 WHEN OTHERS THEN
3626 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3627 (
3628 l_api_name,
3629 G_PKG_NAME,
3630 'OTHERS',
3631 x_msg_count,
3632 x_msg_data,
3633 '_PVT'
3634 );
3635 END delete_row;
3636
3637 ---------------------------------------------------------------
3638 -- Procedure for mass insert in OKC_RULE_GROUPS _B and TL tables
3639 ---------------------------------------------------------------
3640 PROCEDURE INSERT_ROW_UPG(x_return_status OUT NOCOPY VARCHAR2,p_rgpv_tbl rgpv_tbl_type) IS
3641 l_tabsize NUMBER := p_rgpv_tbl.COUNT;
3642 l_source_lang VARCHAR2(12) := okc_util.get_userenv_lang;
3643
3644 in_id OKC_DATATYPES.NumberTabTyp;
3645 in_object_version_number OKC_DATATYPES.NumberTabTyp;
3646 in_rgp_type OKC_DATATYPES.Var10TabTyp;
3647 in_sfwt_flag OKC_DATATYPES.Var3TabTyp;
3648 in_rgd_code OKC_DATATYPES.Var30TabTyp;
3649 in_cle_id OKC_DATATYPES.NumberTabTyp;
3650 in_chr_id OKC_DATATYPES.NumberTabTyp;
3651 in_dnz_chr_id OKC_DATATYPES.NumberTabTyp;
3652 in_parent_rgp_id OKC_DATATYPES.NumberTabTyp;
3653 in_sat_code OKC_DATATYPES.Var30TabTyp;
3654 in_comments OKC_DATATYPES.Var1995TabTyp;
3655 in_attribute_category OKC_DATATYPES.Var90TabTyp;
3656 in_attribute1 OKC_DATATYPES.Var450TabTyp;
3657 in_attribute2 OKC_DATATYPES.Var450TabTyp;
3658 in_attribute3 OKC_DATATYPES.Var450TabTyp;
3659 in_attribute4 OKC_DATATYPES.Var450TabTyp;
3660 in_attribute5 OKC_DATATYPES.Var450TabTyp;
3661 in_attribute6 OKC_DATATYPES.Var450TabTyp;
3662 in_attribute7 OKC_DATATYPES.Var450TabTyp;
3663 in_attribute8 OKC_DATATYPES.Var450TabTyp;
3664 in_attribute9 OKC_DATATYPES.Var450TabTyp;
3665 in_attribute10 OKC_DATATYPES.Var450TabTyp;
3666 in_attribute11 OKC_DATATYPES.Var450TabTyp;
3667 in_attribute12 OKC_DATATYPES.Var450TabTyp;
3668 in_attribute13 OKC_DATATYPES.Var450TabTyp;
3669 in_attribute14 OKC_DATATYPES.Var450TabTyp;
3670 in_attribute15 OKC_DATATYPES.Var450TabTyp;
3671 in_created_by OKC_DATATYPES.NumberTabTyp;
3672 in_creation_date OKC_DATATYPES.DateTabTyp;
3673 in_last_updated_by OKC_DATATYPES.NumberTabTyp;
3674 in_last_update_date OKC_DATATYPES.DateTabTyp;
3675 in_last_update_login OKC_DATATYPES.NumberTabTyp;
3676 i number;
3677 j number;
3678 BEGIN
3679 --Initialize return status
3680 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3681 i := p_rgpv_tbl.FIRST; j:=0;
3682 while i is not null
3683 LOOP
3684 j:=j+1;
3685 in_id (j) := p_rgpv_tbl(i).id;
3686 in_object_version_number (j) := p_rgpv_tbl(i).object_version_number;
3687 in_rgp_type (j) := p_rgpv_tbl(i).rgp_type;
3688 in_sfwt_flag (j) := p_rgpv_tbl(i).sfwt_flag;
3689 in_rgd_code (j) := p_rgpv_tbl(i).rgd_code;
3690 in_cle_id (j) := p_rgpv_tbl(i).cle_id;
3691 in_chr_id (j) := p_rgpv_tbl(i).chr_id;
3692 in_dnz_chr_id (j) := p_rgpv_tbl(i).dnz_chr_id;
3693 in_parent_rgp_id (j) := p_rgpv_tbl(i).parent_rgp_id;
3694 in_sat_code (j) := p_rgpv_tbl(i).sat_code;
3695 in_comments (j) := p_rgpv_tbl(i).comments;
3696 in_attribute_category (j) := p_rgpv_tbl(i).attribute_category;
3697 in_attribute1 (j) := p_rgpv_tbl(i).attribute1;
3698 in_attribute2 (j) := p_rgpv_tbl(i).attribute2;
3699 in_attribute3 (j) := p_rgpv_tbl(i).attribute3;
3700 in_attribute4 (j) := p_rgpv_tbl(i).attribute4;
3701 in_attribute5 (j) := p_rgpv_tbl(i).attribute5;
3702 in_attribute6 (j) := p_rgpv_tbl(i).attribute6;
3703 in_attribute7 (j) := p_rgpv_tbl(i).attribute7;
3704 in_attribute8 (j) := p_rgpv_tbl(i).attribute8;
3705 in_attribute9 (j) := p_rgpv_tbl(i).attribute9;
3706 in_attribute10 (j) := p_rgpv_tbl(i).attribute10;
3707 in_attribute11 (j) := p_rgpv_tbl(i).attribute11;
3708 in_attribute12 (j) := p_rgpv_tbl(i).attribute12;
3709 in_attribute13 (j) := p_rgpv_tbl(i).attribute13;
3710 in_attribute14 (j) := p_rgpv_tbl(i).attribute14;
3711 in_attribute15 (j) := p_rgpv_tbl(i).attribute15;
3712 in_created_by (j) := p_rgpv_tbl(i).created_by;
3713 in_creation_date (j) := p_rgpv_tbl(i).creation_date;
3714 in_last_updated_by (j) := p_rgpv_tbl(i).last_updated_by;
3715 in_last_update_date (j) := p_rgpv_tbl(i).last_update_date;
3716 in_last_update_login (j) := p_rgpv_tbl(i).last_update_login;
3717 i:=p_rgpv_tbl.next(i);
3718 END LOOP;
3719
3720 FORALL i in 1..l_tabsize
3721 INSERT
3722 INTO OKC_RULE_GROUPS_B
3723 (
3724 id,
3725 rgd_code,
3726 chr_id,
3727 cle_id,
3728 dnz_chr_id,
3729 parent_rgp_id,
3730 sat_code,
3731 object_version_number,
3732 rgp_type,
3733 created_by,
3734 creation_date,
3735 last_updated_by,
3736 last_update_date,
3737 last_update_login,
3738 attribute_category,
3739 attribute1,
3740 attribute2,
3741 attribute3,
3742 attribute4,
3743 attribute5,
3744 attribute6,
3745 attribute7,
3746 attribute8,
3747 attribute9,
3748 attribute10,
3749 attribute11,
3750 attribute12,
3751 attribute13,
3752 attribute14,
3753 attribute15
3754 -- REMOVE comma from the previous line
3755 )
3756 VALUES (
3757 in_id(i),
3758 in_rgd_code(i),
3759 in_chr_id(i),
3760 in_cle_id(i),
3761 in_dnz_chr_id(i),
3762 in_parent_rgp_id(i),
3763 in_sat_code(i),
3764 in_object_version_number(i),
3765 in_rgp_type(i),
3766 in_created_by(i),
3767 in_creation_date(i),
3768 in_last_updated_by(i),
3769 in_last_update_date(i),
3770 in_last_update_login(i),
3771 in_attribute_category(i),
3772 in_attribute1(i),
3773 in_attribute2(i),
3774 in_attribute3(i),
3775 in_attribute4(i),
3776 in_attribute5(i),
3777 in_attribute6(i),
3778 in_attribute7(i),
3779 in_attribute8(i),
3780 in_attribute9(i),
3781 in_attribute10(i),
3782 in_attribute11(i),
3783 in_attribute12(i),
3784 in_attribute13(i),
3785 in_attribute14(i),
3786 in_attribute15(i)
3787 -- REMOVE comma from the previous line
3788 );
3789
3790 FOR lang_i IN OKC_UTIL.g_language_code.FIRST..OKC_UTIL.g_language_code.LAST LOOP
3791 FORALL i in 1..l_tabsize
3792 INSERT INTO OKC_RULE_GROUPS_TL(
3793 id,
3794 language,
3795 source_lang,
3796 sfwt_flag,
3797 comments,
3798 created_by,
3799 creation_date,
3800 last_updated_by,
3801 last_update_date,
3802 last_update_login
3803 -- REMOVE comma from the previous line
3804 )
3805 VALUES (
3806 in_id(i),
3807 OKC_UTIL.g_language_code(lang_i),
3808 l_source_lang,
3809 in_sfwt_flag(i),
3810 in_comments(i),
3811 in_created_by(i),
3812 in_creation_date(i),
3813 in_last_updated_by(i),
3814 in_last_update_date(i),
3815 in_last_update_login(i)
3816 -- REMOVE comma from the previous line
3817 );
3818 END LOOP;
3819 EXCEPTION
3820 WHEN OTHERS THEN
3821 -- store SQL error message on message stack
3822 OKC_API.SET_MESSAGE(
3823 p_app_name => G_APP_NAME,
3824 p_msg_name => G_UNEXPECTED_ERROR,
3825 p_token1 => G_SQLCODE_TOKEN,
3826 p_token1_value => SQLCODE,
3827 p_token2 => G_SQLERRM_TOKEN,
3828 p_token2_value => SQLERRM);
3829 -- notify caller of an error as UNEXPECTED error
3830 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3831 --RAISE;
3832 END INSERT_ROW_UPG;
3833
3834 --This function is called from versioning API OKC_VERSION_PVT
3835 --Old Location: OKCRVERB.pls
3836 --New Location: Base Table API
3837
3838 FUNCTION create_version(
3839 p_chr_id IN NUMBER,
3840 p_major_version IN NUMBER
3841 ) RETURN VARCHAR2 IS
3842
3843 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
3844
3845 BEGIN
3846 INSERT INTO okc_rule_groups_bh
3847 (
3848 major_version,
3849 id,
3850 rgd_code,
3851 chr_id,
3852 cle_id,
3853 dnz_chr_id,
3854 parent_rgp_id,
3855 sat_code,
3856 object_version_number,
3857 rgp_type,
3858 created_by,
3859 creation_date,
3860 last_updated_by,
3861 last_update_date,
3862 last_update_login,
3863 attribute_category,
3864 attribute1,
3865 attribute2,
3866 attribute3,
3867 attribute4,
3868 attribute5,
3869 attribute6,
3870 attribute7,
3871 attribute8,
3872 attribute9,
3873 attribute10,
3874 attribute11,
3875 attribute12,
3876 attribute13,
3877 attribute14,
3878 attribute15
3879 )
3880 SELECT
3881 p_major_version,
3882 id,
3883 rgd_code,
3884 chr_id,
3885 cle_id,
3886 dnz_chr_id,
3887 parent_rgp_id,
3888 sat_code,
3889 object_version_number,
3890 rgp_type,
3891 created_by,
3892 creation_date,
3893 last_updated_by,
3894 last_update_date,
3895 last_update_login,
3896 attribute_category,
3897 attribute1,
3898 attribute2,
3899 attribute3,
3900 attribute4,
3901 attribute5,
3902 attribute6,
3903 attribute7,
3904 attribute8,
3905 attribute9,
3906 attribute10,
3907 attribute11,
3908 attribute12,
3909 attribute13,
3910 attribute14,
3911 attribute15
3912 FROM okc_rule_groups_b
3913 WHERE dnz_chr_id = p_chr_id;
3914
3915 -------------------------------
3916 -- Versioning TL Table
3917 -------------------------------
3918
3919 INSERT INTO okc_rule_groups_tlh
3920 (
3921 major_version,
3922 id,
3923 language,
3924 source_lang,
3925 sfwt_flag,
3926 comments,
3927 created_by,
3928 creation_date,
3929 last_updated_by,
3930 last_update_date,
3931 last_update_login
3932 )
3933 SELECT
3934 p_major_version,
3935 id,
3936 language,
3937 source_lang,
3938 sfwt_flag,
3939 comments,
3940 created_by,
3941 creation_date,
3942 last_updated_by,
3943 last_update_date,
3944 last_update_login
3945 FROM okc_rule_groups_tl
3946 WHERE id in (select id
3947 from okc_rule_groups_b
3948 where dnz_chr_id = p_chr_id);
3949
3950 -- Lines changed above Dated 10/06/2000
3951
3952 RETURN l_return_status;
3953 EXCEPTION
3954 -- other appropriate handlers
3955 WHEN OTHERS THEN
3956 -- store SQL error message on message stack
3957 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
3958 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
3959 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
3960 p_token1_value => sqlcode,
3961 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
3962 p_token2_value => sqlerrm);
3963
3964 -- notify UNEXPECTED error
3965 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3966 return l_return_status;
3967 END create_version;
3968
3969 --This Function is called from Versioning API OKC_VERSION_PVT
3970 --Old Location:OKCRVERB.pls
3971 --New Location:Base Table API
3972
3973 FUNCTION restore_version(
3974 p_chr_id IN NUMBER,
3975 p_major_version IN NUMBER
3976 ) RETURN VARCHAR2 IS
3977
3978 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
3979
3980 BEGIN
3981 INSERT INTO okc_rule_groups_tl
3982 (
3983 id,
3984 language,
3985 source_lang,
3986 sfwt_flag,
3987 comments,
3988 created_by,
3989 creation_date,
3990 last_updated_by,
3991 last_update_date,
3992 last_update_login
3993 )
3994 SELECT
3995 id,
3996 language,
3997 source_lang,
3998 sfwt_flag,
3999 comments,
4000 created_by,
4001 creation_date,
4002 last_updated_by,
4003 last_update_date,
4004 last_update_login
4005 FROM okc_rule_groups_tlh
4006 WHERE id in (SELECT id
4007 FROM okc_rule_groups_bh
4008 WHERE dnz_chr_id = p_chr_id)
4009 AND major_version = p_major_version;
4010
4011 -----------------------------------------
4012 -- Restoring Base Table
4013 -----------------------------------------
4014
4015 INSERT INTO okc_rule_groups_b
4016 (
4017 id,
4018 rgd_code,
4019 chr_id,
4020 cle_id,
4021 dnz_chr_id,
4022 parent_rgp_id,
4023 sat_code,
4024 object_version_number,
4025 rgp_type,
4026 created_by,
4027 creation_date,
4028 last_updated_by,
4029 last_update_date,
4030 last_update_login,
4031 attribute_category,
4032 attribute1,
4033 attribute2,
4034 attribute3,
4035 attribute4,
4036 attribute5,
4037 attribute6,
4038 attribute7,
4039 attribute8,
4040 attribute9,
4041 attribute10,
4042 attribute11,
4043 attribute12,
4044 attribute13,
4045 attribute14,
4046 attribute15
4047 )
4048 SELECT
4049 id,
4050 rgd_code,
4051 chr_id,
4052 cle_id,
4053 dnz_chr_id,
4054 parent_rgp_id,
4055 sat_code,
4056 object_version_number,
4057 rgp_type,
4058 created_by,
4059 creation_date,
4060 last_updated_by,
4061 last_update_date,
4062 last_update_login,
4063 attribute_category,
4064 attribute1,
4065 attribute2,
4066 attribute3,
4067 attribute4,
4068 attribute5,
4069 attribute6,
4070 attribute7,
4071 attribute8,
4072 attribute9,
4073 attribute10,
4074 attribute11,
4075 attribute12,
4076 attribute13,
4077 attribute14,
4078 attribute15
4079 FROM okc_rule_groups_bh
4080 WHERE dnz_chr_id = p_chr_id
4081 AND major_version = p_major_version;
4082
4083 RETURN l_return_status;
4084 EXCEPTION
4085 -- other appropriate handlers
4086 WHEN OTHERS THEN
4087 -- store SQL error message on message stack
4088 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
4089 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
4090 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
4091 p_token1_value => sqlcode,
4092 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
4093 p_token2_value => sqlerrm);
4094
4095 -- notify UNEXPECTED error
4096 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4097 return l_return_status;
4098 END restore_version;
4099
4100 END OKC_RGP_PVT;