[Home] [Help]
PACKAGE BODY: APPS.OKC_RGP_PVT
Source
1 PACKAGE BODY OKC_RGP_PVT AS
2 /* $Header: OKCSRGPB.pls 120.3.12020000.2 2012/07/16 16:16:10 harchand 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 /*added hint as per appsperf team suggestion - bug13627948*/
912 /* Commented DELETE and UPDATE queres for 13781121
913 DELETE FROM OKC_RULE_GROUPS_TL T
914 WHERE NOT EXISTS (
915 SELECT NULL
916 FROM OKC_RULE_GROUPS_B B
917 WHERE B.ID = T.ID
918 AND T.LANGUAGE = USERENV('LANG')
919 );
920
921 UPDATE OKC_RULE_GROUPS_TL T SET (
922 COMMENTS) = (SELECT
923 B.COMMENTS
924 FROM OKC_RULE_GROUPS_TL B
925 WHERE B.ID = T.ID
926 AND B.LANGUAGE = T.SOURCE_LANG)
927 WHERE (
928 T.ID,
929 T.LANGUAGE)
930 IN (SELECT
931 SUBT.ID,
932 SUBT.LANGUAGE
933 FROM OKC_RULE_GROUPS_TL SUBB, OKC_RULE_GROUPS_TL SUBT
934 WHERE SUBB.ID = SUBT.ID
935 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
936 AND (SUBB.COMMENTS <> SUBT.COMMENTS
937 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
938 OR (SUBB.COMMENTS IS NOT NULL AND SUBT.COMMENTS IS NULL)
939 ));
940 */
941
942
943 /*added hint as per appsperf team suggestion - bug13627948*/
944 INSERT /*+ append parallel ( OKC_RULE_GROUPS_TL ) */ INTO OKC_RULE_GROUPS_TL (
945 ID,
946 LANGUAGE,
947 SOURCE_LANG,
948 SFWT_FLAG,
949 COMMENTS,
950 CREATED_BY,
951 CREATION_DATE,
952 LAST_UPDATED_BY,
953 LAST_UPDATE_DATE,
954 LAST_UPDATE_LOGIN)
955 SELECT
956 B.ID,
957 L.LANGUAGE_CODE,
958 B.SOURCE_LANG,
959 B.SFWT_FLAG,
960 B.COMMENTS,
961 B.CREATED_BY,
962 B.CREATION_DATE,
963 B.LAST_UPDATED_BY,
964 B.LAST_UPDATE_DATE,
965 B.LAST_UPDATE_LOGIN
966 FROM OKC_RULE_GROUPS_TL B, FND_LANGUAGES L
967 WHERE L.INSTALLED_FLAG IN ('I', 'B')
968 AND B.LANGUAGE = USERENV('LANG')
969 AND NOT EXISTS(
970 SELECT NULL
971 FROM OKC_RULE_GROUPS_TL T
972 WHERE T.ID = B.ID
973 AND T.LANGUAGE = L.LANGUAGE_CODE
974 );
975 DELETE FROM OKC_RULE_GROUPS_TLH T
976 WHERE NOT EXISTS (
977 SELECT NULL
978 FROM OKC_RULE_GROUPS_BH B
979 WHERE B.ID = T.ID
980 AND B.MAJOR_VERSION = T.MAJOR_VERSION
981 );
982
983 UPDATE OKC_RULE_GROUPS_TLH T SET (
984 COMMENTS) = (SELECT
985 B.COMMENTS
986 FROM OKC_RULE_GROUPS_TLH B
987 WHERE B.ID = T.ID
988 AND B.MAJOR_VERSION = T.MAJOR_VERSION
989 AND B.LANGUAGE = T.SOURCE_LANG)
990 WHERE (
991 T.ID,
992 T.MAJOR_VERSION,
993 T.LANGUAGE)
994 IN (SELECT
995 SUBT.ID,
996 SUBT.MAJOR_VERSION,
997 SUBT.LANGUAGE
998 FROM OKC_RULE_GROUPS_TLH SUBB, OKC_RULE_GROUPS_TLH SUBT
999 WHERE SUBB.ID = SUBT.ID
1000 AND SUBB.MAJOR_VERSION = SUBT.MAJOR_VERSION
1001 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
1002 AND (SUBB.COMMENTS <> SUBT.COMMENTS
1003 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
1004 OR (SUBB.COMMENTS IS NOT NULL AND SUBT.COMMENTS IS NULL)
1005 ));
1006
1007 INSERT INTO OKC_RULE_GROUPS_TLH (
1008 ID,
1009 LANGUAGE,
1010 MAJOR_VERSION,
1011 SOURCE_LANG,
1012 SFWT_FLAG,
1013 COMMENTS,
1014 CREATED_BY,
1015 CREATION_DATE,
1016 LAST_UPDATED_BY,
1017 LAST_UPDATE_DATE,
1018 LAST_UPDATE_LOGIN)
1019 SELECT
1020 B.ID,
1021 L.LANGUAGE_CODE,
1022 B.MAJOR_VERSION,
1023 B.SOURCE_LANG,
1024 B.SFWT_FLAG,
1025 B.COMMENTS,
1026 B.CREATED_BY,
1027 B.CREATION_DATE,
1028 B.LAST_UPDATED_BY,
1029 B.LAST_UPDATE_DATE,
1030 B.LAST_UPDATE_LOGIN
1031 FROM OKC_RULE_GROUPS_TLH B, FND_LANGUAGES L
1032 WHERE L.INSTALLED_FLAG IN ('I', 'B')
1033 AND B.LANGUAGE = USERENV('LANG')
1034 AND NOT EXISTS(
1035 SELECT NULL
1036 FROM OKC_RULE_GROUPS_TLH T
1037 WHERE T.ID = B.ID
1038 AND T.MAJOR_VERSION = B.MAJOR_VERSION
1039 AND T.LANGUAGE = L.LANGUAGE_CODE
1040 );
1041
1042
1043 END add_language;
1044
1045 ---------------------------------------------------------------------------
1046 -- FUNCTION get_rec for: OKC_RULE_GROUPS_B
1047 ---------------------------------------------------------------------------
1048 FUNCTION get_rec (
1049 p_rgp_rec IN rgp_rec_type,
1050 x_no_data_found OUT NOCOPY BOOLEAN
1051 ) RETURN rgp_rec_type IS
1052 CURSOR rgp_pk_csr (p_id IN NUMBER) IS
1053 SELECT
1054 ID,
1055 RGD_CODE,
1056 SAT_CODE,
1057 RGP_TYPE,
1058 CHR_ID,
1059 CLE_ID,
1060 DNZ_CHR_ID,
1061 PARENT_RGP_ID,
1062 OBJECT_VERSION_NUMBER,
1063 CREATED_BY,
1064 CREATION_DATE,
1065 LAST_UPDATED_BY,
1066 LAST_UPDATE_DATE,
1067 LAST_UPDATE_LOGIN,
1068 ATTRIBUTE_CATEGORY,
1069 ATTRIBUTE1,
1070 ATTRIBUTE2,
1071 ATTRIBUTE3,
1072 ATTRIBUTE4,
1073 ATTRIBUTE5,
1074 ATTRIBUTE6,
1075 ATTRIBUTE7,
1076 ATTRIBUTE8,
1077 ATTRIBUTE9,
1078 ATTRIBUTE10,
1079 ATTRIBUTE11,
1080 ATTRIBUTE12,
1081 ATTRIBUTE13,
1082 ATTRIBUTE14,
1083 ATTRIBUTE15
1084 FROM Okc_Rule_Groups_B
1085 WHERE okc_rule_groups_b.id = p_id;
1086 l_rgp_pk rgp_pk_csr%ROWTYPE;
1087 l_rgp_rec rgp_rec_type;
1088 BEGIN
1089 x_no_data_found := TRUE;
1090 -- Get current database values
1091 OPEN rgp_pk_csr (p_rgp_rec.id);
1092 FETCH rgp_pk_csr INTO
1093 l_rgp_rec.ID,
1094 l_rgp_rec.RGD_CODE,
1095 l_rgp_rec.SAT_CODE,
1096 l_rgp_rec.RGP_TYPE,
1097 l_rgp_rec.CHR_ID,
1098 l_rgp_rec.CLE_ID,
1099 l_rgp_rec.DNZ_CHR_ID,
1100 l_rgp_rec.PARENT_RGP_ID,
1101 l_rgp_rec.OBJECT_VERSION_NUMBER,
1102 l_rgp_rec.CREATED_BY,
1103 l_rgp_rec.CREATION_DATE,
1104 l_rgp_rec.LAST_UPDATED_BY,
1105 l_rgp_rec.LAST_UPDATE_DATE,
1106 l_rgp_rec.LAST_UPDATE_LOGIN,
1107 l_rgp_rec.ATTRIBUTE_CATEGORY,
1108 l_rgp_rec.ATTRIBUTE1,
1109 l_rgp_rec.ATTRIBUTE2,
1110 l_rgp_rec.ATTRIBUTE3,
1111 l_rgp_rec.ATTRIBUTE4,
1112 l_rgp_rec.ATTRIBUTE5,
1113 l_rgp_rec.ATTRIBUTE6,
1114 l_rgp_rec.ATTRIBUTE7,
1115 l_rgp_rec.ATTRIBUTE8,
1116 l_rgp_rec.ATTRIBUTE9,
1117 l_rgp_rec.ATTRIBUTE10,
1118 l_rgp_rec.ATTRIBUTE11,
1119 l_rgp_rec.ATTRIBUTE12,
1120 l_rgp_rec.ATTRIBUTE13,
1121 l_rgp_rec.ATTRIBUTE14,
1122 l_rgp_rec.ATTRIBUTE15;
1123 x_no_data_found := rgp_pk_csr%NOTFOUND;
1124 CLOSE rgp_pk_csr;
1125 RETURN(l_rgp_rec);
1126 END get_rec;
1127
1128 FUNCTION get_rec (
1129 p_rgp_rec IN rgp_rec_type
1130 ) RETURN rgp_rec_type IS
1131 l_row_notfound BOOLEAN := TRUE;
1132 BEGIN
1133 RETURN(get_rec(p_rgp_rec, l_row_notfound));
1134 END get_rec;
1135 ---------------------------------------------------------------------------
1136 -- FUNCTION get_rec for: OKC_RULE_GROUPS_TL
1137 ---------------------------------------------------------------------------
1138 FUNCTION get_rec (
1139 p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type,
1140 x_no_data_found OUT NOCOPY BOOLEAN
1141 ) RETURN okc_rule_groups_tl_rec_type IS
1142 CURSOR rgp_pktl_csr (p_id IN NUMBER,
1143 p_language IN VARCHAR2) IS
1144 SELECT
1145 ID,
1146 LANGUAGE,
1147 SOURCE_LANG,
1148 SFWT_FLAG,
1149 COMMENTS,
1150 CREATED_BY,
1151 CREATION_DATE,
1152 LAST_UPDATED_BY,
1153 LAST_UPDATE_DATE,
1154 LAST_UPDATE_LOGIN
1155 FROM Okc_Rule_Groups_Tl
1156 WHERE okc_rule_groups_tl.id = p_id
1157 AND okc_rule_groups_tl.language = p_language;
1158 l_rgp_pktl rgp_pktl_csr%ROWTYPE;
1159 l_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
1160 BEGIN
1161 x_no_data_found := TRUE;
1162 -- Get current database values
1163 OPEN rgp_pktl_csr (p_okc_rule_groups_tl_rec.id,
1164 p_okc_rule_groups_tl_rec.language);
1165 FETCH rgp_pktl_csr INTO
1166 l_okc_rule_groups_tl_rec.ID,
1167 l_okc_rule_groups_tl_rec.LANGUAGE,
1168 l_okc_rule_groups_tl_rec.SOURCE_LANG,
1169 l_okc_rule_groups_tl_rec.SFWT_FLAG,
1170 l_okc_rule_groups_tl_rec.COMMENTS,
1171 l_okc_rule_groups_tl_rec.CREATED_BY,
1172 l_okc_rule_groups_tl_rec.CREATION_DATE,
1173 l_okc_rule_groups_tl_rec.LAST_UPDATED_BY,
1174 l_okc_rule_groups_tl_rec.LAST_UPDATE_DATE,
1175 l_okc_rule_groups_tl_rec.LAST_UPDATE_LOGIN;
1176 x_no_data_found := rgp_pktl_csr%NOTFOUND;
1177 CLOSE rgp_pktl_csr;
1178 RETURN(l_okc_rule_groups_tl_rec);
1179 END get_rec;
1180
1181 FUNCTION get_rec (
1182 p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type
1183 ) RETURN okc_rule_groups_tl_rec_type IS
1184 l_row_notfound BOOLEAN := TRUE;
1185 BEGIN
1186 RETURN(get_rec(p_okc_rule_groups_tl_rec, l_row_notfound));
1187 END get_rec;
1188 ---------------------------------------------------------------------------
1189 -- FUNCTION get_rec for: OKC_RULE_GROUPS_V
1190 ---------------------------------------------------------------------------
1191 FUNCTION get_rec (
1192 p_rgpv_rec IN rgpv_rec_type,
1193 x_no_data_found OUT NOCOPY BOOLEAN
1194 ) RETURN rgpv_rec_type IS
1195 CURSOR okc_rgpv_pk_csr (p_id IN NUMBER) IS
1196 SELECT
1197 ID,
1198 OBJECT_VERSION_NUMBER,
1199 SFWT_FLAG,
1200 RGD_CODE,
1201 SAT_CODE,
1202 RGP_TYPE,
1203 CLE_ID,
1204 CHR_ID,
1205 DNZ_CHR_ID,
1206 PARENT_RGP_ID,
1207 COMMENTS,
1208 ATTRIBUTE_CATEGORY,
1209 ATTRIBUTE1,
1210 ATTRIBUTE2,
1211 ATTRIBUTE3,
1212 ATTRIBUTE4,
1213 ATTRIBUTE5,
1214 ATTRIBUTE6,
1215 ATTRIBUTE7,
1216 ATTRIBUTE8,
1217 ATTRIBUTE9,
1218 ATTRIBUTE10,
1219 ATTRIBUTE11,
1220 ATTRIBUTE12,
1221 ATTRIBUTE13,
1222 ATTRIBUTE14,
1223 ATTRIBUTE15,
1224 CREATED_BY,
1225 CREATION_DATE,
1226 LAST_UPDATED_BY,
1227 LAST_UPDATE_DATE,
1228 LAST_UPDATE_LOGIN
1229 FROM Okc_Rule_Groups_V
1230 WHERE okc_rule_groups_v.id = p_id;
1231 l_okc_rgpv_pk okc_rgpv_pk_csr%ROWTYPE;
1232 l_rgpv_rec rgpv_rec_type;
1233 BEGIN
1234 x_no_data_found := TRUE;
1235 -- Get current database values
1236 OPEN okc_rgpv_pk_csr (p_rgpv_rec.id);
1237 FETCH okc_rgpv_pk_csr INTO
1238 l_rgpv_rec.ID,
1239 l_rgpv_rec.OBJECT_VERSION_NUMBER,
1240 l_rgpv_rec.SFWT_FLAG,
1241 l_rgpv_rec.RGD_CODE,
1242 l_rgpv_rec.SAT_CODE,
1243 l_rgpv_rec.RGP_TYPE,
1244 l_rgpv_rec.CLE_ID,
1245 l_rgpv_rec.CHR_ID,
1246 l_rgpv_rec.DNZ_CHR_ID,
1247 l_rgpv_rec.PARENT_RGP_ID,
1248 l_rgpv_rec.COMMENTS,
1249 l_rgpv_rec.ATTRIBUTE_CATEGORY,
1250 l_rgpv_rec.ATTRIBUTE1,
1251 l_rgpv_rec.ATTRIBUTE2,
1252 l_rgpv_rec.ATTRIBUTE3,
1253 l_rgpv_rec.ATTRIBUTE4,
1254 l_rgpv_rec.ATTRIBUTE5,
1255 l_rgpv_rec.ATTRIBUTE6,
1256 l_rgpv_rec.ATTRIBUTE7,
1257 l_rgpv_rec.ATTRIBUTE8,
1258 l_rgpv_rec.ATTRIBUTE9,
1259 l_rgpv_rec.ATTRIBUTE10,
1260 l_rgpv_rec.ATTRIBUTE11,
1261 l_rgpv_rec.ATTRIBUTE12,
1262 l_rgpv_rec.ATTRIBUTE13,
1263 l_rgpv_rec.ATTRIBUTE14,
1264 l_rgpv_rec.ATTRIBUTE15,
1265 l_rgpv_rec.CREATED_BY,
1266 l_rgpv_rec.CREATION_DATE,
1267 l_rgpv_rec.LAST_UPDATED_BY,
1268 l_rgpv_rec.LAST_UPDATE_DATE,
1269 l_rgpv_rec.LAST_UPDATE_LOGIN;
1270 x_no_data_found := okc_rgpv_pk_csr%NOTFOUND;
1271 CLOSE okc_rgpv_pk_csr;
1272 RETURN(l_rgpv_rec);
1273 END get_rec;
1274
1275 FUNCTION get_rec (
1276 p_rgpv_rec IN rgpv_rec_type
1277 ) RETURN rgpv_rec_type IS
1278 l_row_notfound BOOLEAN := TRUE;
1279 BEGIN
1280 RETURN(get_rec(p_rgpv_rec, l_row_notfound));
1281 END get_rec;
1282
1283 -------------------------------------------------------
1284 -- FUNCTION null_out_defaults for: OKC_RULE_GROUPS_V --
1285 -------------------------------------------------------
1286 FUNCTION null_out_defaults (
1287 p_rgpv_rec IN rgpv_rec_type
1288 ) RETURN rgpv_rec_type IS
1289 l_rgpv_rec rgpv_rec_type := p_rgpv_rec;
1290 BEGIN
1291 IF (l_rgpv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
1292 l_rgpv_rec.object_version_number := NULL;
1293 END IF;
1294 IF (l_rgpv_rec.sfwt_flag = OKC_API.G_MISS_CHAR) THEN
1295 l_rgpv_rec.sfwt_flag := NULL;
1296 END IF;
1297 IF (l_rgpv_rec.rgd_code = OKC_API.G_MISS_CHAR) THEN
1298 l_rgpv_rec.rgd_code := NULL;
1299 END IF;
1300 IF (l_rgpv_rec.sat_code = OKC_API.G_MISS_CHAR) THEN
1301 l_rgpv_rec.sat_code := NULL;
1302 END IF;
1303 IF (l_rgpv_rec.rgp_type = OKC_API.G_MISS_CHAR) THEN
1304 l_rgpv_rec.rgp_type := NULL;
1305 END IF;
1306 IF (l_rgpv_rec.cle_id = OKC_API.G_MISS_NUM) THEN
1307 l_rgpv_rec.cle_id := NULL;
1308 END IF;
1309 IF (l_rgpv_rec.chr_id = OKC_API.G_MISS_NUM) THEN
1310 l_rgpv_rec.chr_id := NULL;
1311 END IF;
1312 IF (l_rgpv_rec.dnz_chr_id = OKC_API.G_MISS_NUM) THEN
1313 l_rgpv_rec.dnz_chr_id := NULL;
1314 END IF;
1315 IF (l_rgpv_rec.parent_rgp_id = OKC_API.G_MISS_NUM) THEN
1316 l_rgpv_rec.parent_rgp_id := NULL;
1317 END IF;
1318 IF (l_rgpv_rec.comments = OKC_API.G_MISS_CHAR) THEN
1319 l_rgpv_rec.comments := NULL;
1320 END IF;
1321 IF (l_rgpv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
1322 l_rgpv_rec.attribute_category := NULL;
1323 END IF;
1324 IF (l_rgpv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
1325 l_rgpv_rec.attribute1 := NULL;
1326 END IF;
1327 IF (l_rgpv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
1328 l_rgpv_rec.attribute2 := NULL;
1329 END IF;
1330 IF (l_rgpv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
1331 l_rgpv_rec.attribute3 := NULL;
1332 END IF;
1333 IF (l_rgpv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
1334 l_rgpv_rec.attribute4 := NULL;
1335 END IF;
1336 IF (l_rgpv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
1337 l_rgpv_rec.attribute5 := NULL;
1338 END IF;
1339 IF (l_rgpv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
1340 l_rgpv_rec.attribute6 := NULL;
1341 END IF;
1342 IF (l_rgpv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
1343 l_rgpv_rec.attribute7 := NULL;
1344 END IF;
1345 IF (l_rgpv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
1346 l_rgpv_rec.attribute8 := NULL;
1347 END IF;
1348 IF (l_rgpv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
1349 l_rgpv_rec.attribute9 := NULL;
1350 END IF;
1351 IF (l_rgpv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
1352 l_rgpv_rec.attribute10 := NULL;
1353 END IF;
1354 IF (l_rgpv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
1355 l_rgpv_rec.attribute11 := NULL;
1356 END IF;
1357 IF (l_rgpv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
1358 l_rgpv_rec.attribute12 := NULL;
1359 END IF;
1360 IF (l_rgpv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
1361 l_rgpv_rec.attribute13 := NULL;
1362 END IF;
1363 IF (l_rgpv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
1364 l_rgpv_rec.attribute14 := NULL;
1365 END IF;
1366 IF (l_rgpv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
1367 l_rgpv_rec.attribute15 := NULL;
1368 END IF;
1369 IF (l_rgpv_rec.created_by = OKC_API.G_MISS_NUM) THEN
1370 l_rgpv_rec.created_by := NULL;
1371 END IF;
1372 IF (l_rgpv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
1373 l_rgpv_rec.creation_date := NULL;
1374 END IF;
1375 IF (l_rgpv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
1376 l_rgpv_rec.last_updated_by := NULL;
1377 END IF;
1378 IF (l_rgpv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
1379 l_rgpv_rec.last_update_date := NULL;
1380 END IF;
1381 IF (l_rgpv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
1382 l_rgpv_rec.last_update_login := NULL;
1383 END IF;
1384 RETURN(l_rgpv_rec);
1385 END null_out_defaults;
1386 ---------------------------------------------------------------------------
1387 -- PROCEDURE Validate_Attributes
1388 ---------------------------------------------------------------------------
1389 -----------------------------------------------
1390 -- Validate_Attributes for:OKC_RULE_GROUPS_V --
1391 -----------------------------------------------
1392 /* commenting out nocopy generated code in favor of hand-coded procedure
1393 FUNCTION Validate_Attributes (
1394 p_rgpv_rec IN rgpv_rec_type
1395 ) RETURN VARCHAR2 IS
1396 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1397 BEGIN
1398 IF p_rgpv_rec.id = OKC_API.G_MISS_NUM OR
1399 p_rgpv_rec.id IS NULL
1400 THEN
1401 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
1402 l_return_status := OKC_API.G_RET_STS_ERROR;
1403 ELSIF p_rgpv_rec.object_version_number = OKC_API.G_MISS_NUM OR
1404 p_rgpv_rec.object_version_number IS NULL
1405 THEN
1406 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
1407 l_return_status := OKC_API.G_RET_STS_ERROR;
1408 ELSIF p_rgpv_rec.rgd_code = OKC_API.G_MISS_CHAR OR
1409 p_rgpv_rec.rgd_code IS NULL
1410 THEN
1411 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'rgd_code');
1412 l_return_status := OKC_API.G_RET_STS_ERROR;
1413 ELSIF p_rgpv_rec.dnz_chr_id = OKC_API.G_MISS_NUM OR
1414 p_rgpv_rec.dnz_chr_id IS NULL
1415 THEN
1416 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'dnz_chr_id');
1417 l_return_status := OKC_API.G_RET_STS_ERROR;
1418 END IF;
1419 RETURN(l_return_status);
1420 END Validate_Attributes;
1421 */
1422 ---------------------------------------------------------------------------
1423 -- PROCEDURE Validate_Record
1424 ---------------------------------------------------------------------------
1425 -------------------------------------------
1426 -- Validate_Record for:OKC_RULE_GROUPS_V --
1427 -------------------------------------------
1428 FUNCTION Validate_Record (
1429 p_rgpv_rec IN rgpv_rec_type
1430 ) RETURN VARCHAR2 IS
1431 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1432 BEGIN
1433 RETURN (l_return_status);
1434 END Validate_Record;
1435
1436 ---------------------------------------------------------------------------
1437 -- PROCEDURE Migrate
1438 ---------------------------------------------------------------------------
1439 PROCEDURE migrate (
1440 p_from IN rgpv_rec_type,
1441 p_to IN OUT NOCOPY rgp_rec_type
1442 ) IS
1443 BEGIN
1444 p_to.id := p_from.id;
1445 p_to.rgd_code := p_from.rgd_code;
1446 p_to.sat_code := p_from.sat_code;
1447 p_to.rgp_type := p_from.rgp_type;
1448 p_to.chr_id := p_from.chr_id;
1449 p_to.cle_id := p_from.cle_id;
1450 p_to.dnz_chr_id := p_from.dnz_chr_id;
1451 p_to.parent_rgp_id := p_from.parent_rgp_id;
1452 p_to.object_version_number := p_from.object_version_number;
1453 p_to.created_by := p_from.created_by;
1454 p_to.creation_date := p_from.creation_date;
1455 p_to.last_updated_by := p_from.last_updated_by;
1456 p_to.last_update_date := p_from.last_update_date;
1457 p_to.last_update_login := p_from.last_update_login;
1458 p_to.attribute_category := p_from.attribute_category;
1459 p_to.attribute1 := p_from.attribute1;
1460 p_to.attribute2 := p_from.attribute2;
1461 p_to.attribute3 := p_from.attribute3;
1462 p_to.attribute4 := p_from.attribute4;
1463 p_to.attribute5 := p_from.attribute5;
1464 p_to.attribute6 := p_from.attribute6;
1465 p_to.attribute7 := p_from.attribute7;
1466 p_to.attribute8 := p_from.attribute8;
1467 p_to.attribute9 := p_from.attribute9;
1468 p_to.attribute10 := p_from.attribute10;
1469 p_to.attribute11 := p_from.attribute11;
1470 p_to.attribute12 := p_from.attribute12;
1471 p_to.attribute13 := p_from.attribute13;
1472 p_to.attribute14 := p_from.attribute14;
1473 p_to.attribute15 := p_from.attribute15;
1474 END migrate;
1475 PROCEDURE migrate (
1476 p_from IN rgp_rec_type,
1477 p_to IN OUT NOCOPY rgpv_rec_type
1478 ) IS
1479 BEGIN
1480 p_to.id := p_from.id;
1481 p_to.rgd_code := p_from.rgd_code;
1482 p_to.sat_code := p_from.sat_code;
1483 p_to.rgp_type := p_from.rgp_type;
1484 p_to.chr_id := p_from.chr_id;
1485 p_to.cle_id := p_from.cle_id;
1486 p_to.dnz_chr_id := p_from.dnz_chr_id;
1487 p_to.parent_rgp_id := p_from.parent_rgp_id;
1488 p_to.object_version_number := p_from.object_version_number;
1489 p_to.created_by := p_from.created_by;
1490 p_to.creation_date := p_from.creation_date;
1491 p_to.last_updated_by := p_from.last_updated_by;
1492 p_to.last_update_date := p_from.last_update_date;
1493 p_to.last_update_login := p_from.last_update_login;
1494 p_to.attribute_category := p_from.attribute_category;
1495 p_to.attribute1 := p_from.attribute1;
1496 p_to.attribute2 := p_from.attribute2;
1497 p_to.attribute3 := p_from.attribute3;
1498 p_to.attribute4 := p_from.attribute4;
1499 p_to.attribute5 := p_from.attribute5;
1500 p_to.attribute6 := p_from.attribute6;
1501 p_to.attribute7 := p_from.attribute7;
1502 p_to.attribute8 := p_from.attribute8;
1503 p_to.attribute9 := p_from.attribute9;
1504 p_to.attribute10 := p_from.attribute10;
1505 p_to.attribute11 := p_from.attribute11;
1506 p_to.attribute12 := p_from.attribute12;
1507 p_to.attribute13 := p_from.attribute13;
1508 p_to.attribute14 := p_from.attribute14;
1509 p_to.attribute15 := p_from.attribute15;
1510 END migrate;
1511 PROCEDURE migrate (
1512 p_from IN rgpv_rec_type,
1513 p_to IN OUT NOCOPY okc_rule_groups_tl_rec_type
1514 ) IS
1515 BEGIN
1516 p_to.id := p_from.id;
1517 p_to.sfwt_flag := p_from.sfwt_flag;
1518 p_to.comments := p_from.comments;
1519 p_to.created_by := p_from.created_by;
1520 p_to.creation_date := p_from.creation_date;
1521 p_to.last_updated_by := p_from.last_updated_by;
1522 p_to.last_update_date := p_from.last_update_date;
1523 p_to.last_update_login := p_from.last_update_login;
1524 END migrate;
1525 PROCEDURE migrate (
1526 p_from IN okc_rule_groups_tl_rec_type,
1527 p_to IN OUT NOCOPY rgpv_rec_type
1528 ) IS
1529 BEGIN
1530 p_to.id := p_from.id;
1531 p_to.sfwt_flag := p_from.sfwt_flag;
1532 p_to.comments := p_from.comments;
1533 p_to.created_by := p_from.created_by;
1534 p_to.creation_date := p_from.creation_date;
1535 p_to.last_updated_by := p_from.last_updated_by;
1536 p_to.last_update_date := p_from.last_update_date;
1537 p_to.last_update_login := p_from.last_update_login;
1538 END migrate;
1539
1540 ---------------------------------------------------------------------------
1541 -- PROCEDURE validate_row
1542 ---------------------------------------------------------------------------
1543 ----------------------------------------
1544 -- validate_row for:OKC_RULE_GROUPS_V --
1545 ----------------------------------------
1546 PROCEDURE validate_row(
1547 p_api_version IN NUMBER,
1548 p_init_msg_list IN VARCHAR2,
1549 x_return_status OUT NOCOPY VARCHAR2,
1550 x_msg_count OUT NOCOPY NUMBER,
1551 x_msg_data OUT NOCOPY VARCHAR2,
1552 p_rgpv_rec IN rgpv_rec_type) IS
1553
1554 l_api_version CONSTANT NUMBER := 1;
1555 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1556 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1557 l_rgpv_rec rgpv_rec_type := p_rgpv_rec;
1558 l_rgp_rec rgp_rec_type;
1559 l_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
1560 BEGIN
1561 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1562 G_PKG_NAME,
1563 p_init_msg_list,
1564 l_api_version,
1565 p_api_version,
1566 '_PVT',
1567 x_return_status);
1568 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1569 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1570 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1571 RAISE OKC_API.G_EXCEPTION_ERROR;
1572 END IF;
1573 --- Validate all non-missing attributes (Item Level Validation)
1574 l_return_status := Validate_Attributes(l_rgpv_rec);
1575 --- If any errors happen abort API
1576 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1577 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1578 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1579 RAISE OKC_API.G_EXCEPTION_ERROR;
1580 END IF;
1581 l_return_status := Validate_Record(l_rgpv_rec);
1582 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1583 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1584 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1585 RAISE OKC_API.G_EXCEPTION_ERROR;
1586 END IF;
1587 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1588 EXCEPTION
1589 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1590 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1591 (
1592 l_api_name,
1593 G_PKG_NAME,
1594 'OKC_API.G_RET_STS_ERROR',
1595 x_msg_count,
1596 x_msg_data,
1597 '_PVT'
1598 );
1599 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1600 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1601 (
1602 l_api_name,
1603 G_PKG_NAME,
1604 'OKC_API.G_RET_STS_UNEXP_ERROR',
1605 x_msg_count,
1606 x_msg_data,
1607 '_PVT'
1608 );
1609 WHEN OTHERS THEN
1610 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1611 (
1612 l_api_name,
1613 G_PKG_NAME,
1614 'OTHERS',
1615 x_msg_count,
1616 x_msg_data,
1617 '_PVT'
1618 );
1619 END validate_row;
1620 ------------------------------------------
1621 -- PL/SQL TBL validate_row for:RGPV_TBL --
1622 ------------------------------------------
1623 PROCEDURE validate_row(
1624 p_api_version IN NUMBER,
1625 p_init_msg_list IN VARCHAR2,
1626 x_return_status OUT NOCOPY VARCHAR2,
1627 x_msg_count OUT NOCOPY NUMBER,
1628 x_msg_data OUT NOCOPY VARCHAR2,
1629 p_rgpv_tbl IN rgpv_tbl_type) IS
1630
1631 l_api_version CONSTANT NUMBER := 1;
1632 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1633 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1634 i NUMBER := 0;
1635 BEGIN
1636 OKC_API.init_msg_list(p_init_msg_list);
1637 -- Make sure PL/SQL table has records in it before passing
1638 IF (p_rgpv_tbl.COUNT > 0) THEN
1639 i := p_rgpv_tbl.FIRST;
1640 LOOP
1641 validate_row (
1642 p_api_version => p_api_version,
1643 p_init_msg_list => OKC_API.G_FALSE,
1644 x_return_status => x_return_status,
1645 x_msg_count => x_msg_count,
1646 x_msg_data => x_msg_data,
1647 p_rgpv_rec => p_rgpv_tbl(i));
1648 EXIT WHEN (i = p_rgpv_tbl.LAST);
1649 i := p_rgpv_tbl.NEXT(i);
1650 END LOOP;
1651 END IF;
1652 EXCEPTION
1653 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1654 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1655 (
1656 l_api_name,
1657 G_PKG_NAME,
1658 'OKC_API.G_RET_STS_ERROR',
1659 x_msg_count,
1660 x_msg_data,
1661 '_PVT'
1662 );
1663 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1664 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1665 (
1666 l_api_name,
1667 G_PKG_NAME,
1668 'OKC_API.G_RET_STS_UNEXP_ERROR',
1669 x_msg_count,
1670 x_msg_data,
1671 '_PVT'
1672 );
1673 WHEN OTHERS THEN
1674 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1675 (
1676 l_api_name,
1677 G_PKG_NAME,
1678 'OTHERS',
1679 x_msg_count,
1680 x_msg_data,
1681 '_PVT'
1682 );
1683 END validate_row;
1684
1685 ---------------------------------------------------------------------------
1686 -- PROCEDURE insert_row
1687 ---------------------------------------------------------------------------
1688 --------------------------------------
1689 -- insert_row for:OKC_RULE_GROUPS_B --
1690 --------------------------------------
1691 PROCEDURE insert_row(
1692 p_init_msg_list IN VARCHAR2,
1693 x_return_status OUT NOCOPY VARCHAR2,
1694 x_msg_count OUT NOCOPY NUMBER,
1695 x_msg_data OUT NOCOPY VARCHAR2,
1696 p_rgp_rec IN rgp_rec_type,
1697 x_rgp_rec OUT NOCOPY rgp_rec_type) IS
1698
1699 l_api_version CONSTANT NUMBER := 1;
1700 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1701 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1702 l_rgp_rec rgp_rec_type := p_rgp_rec;
1703 l_def_rgp_rec rgp_rec_type;
1704 ------------------------------------------
1705 -- Set_Attributes for:OKC_RULE_GROUPS_B --
1706 ------------------------------------------
1707 FUNCTION Set_Attributes (
1708 p_rgp_rec IN rgp_rec_type,
1709 x_rgp_rec OUT NOCOPY rgp_rec_type
1710 ) RETURN VARCHAR2 IS
1711 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1712 BEGIN
1713 x_rgp_rec := p_rgp_rec;
1714 RETURN(l_return_status);
1715 END Set_Attributes;
1716 BEGIN
1717 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1718 p_init_msg_list,
1719 '_PVT',
1720 x_return_status);
1721 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1722 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1723 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1724 RAISE OKC_API.G_EXCEPTION_ERROR;
1725 END IF;
1726 --- Setting item attributes
1727 l_return_status := Set_Attributes(
1728 p_rgp_rec, -- IN
1729 l_rgp_rec); -- OUT
1730 --- If any errors happen abort API
1731 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1732 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1733 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1734 RAISE OKC_API.G_EXCEPTION_ERROR;
1735 END IF;
1736 INSERT INTO OKC_RULE_GROUPS_B(
1737 id,
1738 rgd_code,
1739 sat_code,
1740 rgp_type,
1741 chr_id,
1742 cle_id,
1743 dnz_chr_id,
1744 parent_rgp_id,
1745 object_version_number,
1746 created_by,
1747 creation_date,
1748 last_updated_by,
1749 last_update_date,
1750 last_update_login,
1751 attribute_category,
1752 attribute1,
1753 attribute2,
1754 attribute3,
1755 attribute4,
1756 attribute5,
1757 attribute6,
1758 attribute7,
1759 attribute8,
1760 attribute9,
1761 attribute10,
1762 attribute11,
1763 attribute12,
1764 attribute13,
1765 attribute14,
1766 attribute15)
1767 VALUES (
1768 l_rgp_rec.id,
1769 l_rgp_rec.rgd_code,
1770 l_rgp_rec.sat_code,
1771 l_rgp_rec.rgp_type,
1772 l_rgp_rec.chr_id,
1773 l_rgp_rec.cle_id,
1774 l_rgp_rec.dnz_chr_id,
1775 l_rgp_rec.parent_rgp_id,
1776 l_rgp_rec.object_version_number,
1777 l_rgp_rec.created_by,
1778 l_rgp_rec.creation_date,
1779 l_rgp_rec.last_updated_by,
1780 l_rgp_rec.last_update_date,
1781 l_rgp_rec.last_update_login,
1782 l_rgp_rec.attribute_category,
1783 l_rgp_rec.attribute1,
1784 l_rgp_rec.attribute2,
1785 l_rgp_rec.attribute3,
1786 l_rgp_rec.attribute4,
1787 l_rgp_rec.attribute5,
1788 l_rgp_rec.attribute6,
1789 l_rgp_rec.attribute7,
1790 l_rgp_rec.attribute8,
1791 l_rgp_rec.attribute9,
1792 l_rgp_rec.attribute10,
1793 l_rgp_rec.attribute11,
1794 l_rgp_rec.attribute12,
1795 l_rgp_rec.attribute13,
1796 l_rgp_rec.attribute14,
1797 l_rgp_rec.attribute15);
1798 -- Set OUT values
1799 x_rgp_rec := l_rgp_rec;
1800 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1801 EXCEPTION
1802 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1803 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1804 (
1805 l_api_name,
1806 G_PKG_NAME,
1807 'OKC_API.G_RET_STS_ERROR',
1808 x_msg_count,
1809 x_msg_data,
1810 '_PVT'
1811 );
1812 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1813 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1814 (
1815 l_api_name,
1816 G_PKG_NAME,
1817 'OKC_API.G_RET_STS_UNEXP_ERROR',
1818 x_msg_count,
1819 x_msg_data,
1820 '_PVT'
1821 );
1822 WHEN OTHERS THEN
1823 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1824 (
1825 l_api_name,
1826 G_PKG_NAME,
1827 'OTHERS',
1828 x_msg_count,
1829 x_msg_data,
1830 '_PVT'
1831 );
1832 END insert_row;
1833 ---------------------------------------
1834 -- insert_row for:OKC_RULE_GROUPS_TL --
1835 ---------------------------------------
1836 PROCEDURE insert_row(
1837 p_init_msg_list IN VARCHAR2,
1838 x_return_status OUT NOCOPY VARCHAR2,
1839 x_msg_count OUT NOCOPY NUMBER,
1840 x_msg_data OUT NOCOPY VARCHAR2,
1841 p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type,
1842 x_okc_rule_groups_tl_rec OUT NOCOPY okc_rule_groups_tl_rec_type) IS
1843
1844 l_api_version CONSTANT NUMBER := 1;
1845 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
1846 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1847 l_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type := p_okc_rule_groups_tl_rec;
1848 l_def_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
1849 CURSOR get_languages IS
1850 SELECT *
1851 FROM FND_LANGUAGES
1852 WHERE INSTALLED_FLAG IN ('I', 'B');
1853 -------------------------------------------
1854 -- Set_Attributes for:OKC_RULE_GROUPS_TL --
1855 -------------------------------------------
1856 FUNCTION Set_Attributes (
1857 p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type,
1858 x_okc_rule_groups_tl_rec OUT NOCOPY okc_rule_groups_tl_rec_type
1859 ) RETURN VARCHAR2 IS
1860 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1861 BEGIN
1862 x_okc_rule_groups_tl_rec := p_okc_rule_groups_tl_rec;
1863 x_okc_rule_groups_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
1864 x_okc_rule_groups_tl_rec.SOURCE_LANG := okc_util.get_userenv_lang;
1865 RETURN(l_return_status);
1866 END Set_Attributes;
1867 BEGIN
1868 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1869 p_init_msg_list,
1870 '_PVT',
1871 x_return_status);
1872 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1873 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1874 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1875 RAISE OKC_API.G_EXCEPTION_ERROR;
1876 END IF;
1877 --- Setting item attributes
1878 l_return_status := Set_Attributes(
1879 p_okc_rule_groups_tl_rec, -- IN
1880 l_okc_rule_groups_tl_rec); -- OUT
1881 --- If any errors happen abort API
1882 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1883 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1884 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1885 RAISE OKC_API.G_EXCEPTION_ERROR;
1886 END IF;
1887 FOR l_lang_rec IN get_languages LOOP
1888 l_okc_rule_groups_tl_rec.language := l_lang_rec.language_code;
1889 INSERT INTO OKC_RULE_GROUPS_TL(
1890 id,
1891 language,
1892 source_lang,
1893 sfwt_flag,
1894 comments,
1895 created_by,
1896 creation_date,
1897 last_updated_by,
1898 last_update_date,
1899 last_update_login)
1900 VALUES (
1901 l_okc_rule_groups_tl_rec.id,
1902 l_okc_rule_groups_tl_rec.language,
1903 l_okc_rule_groups_tl_rec.source_lang,
1904 l_okc_rule_groups_tl_rec.sfwt_flag,
1905 l_okc_rule_groups_tl_rec.comments,
1906 l_okc_rule_groups_tl_rec.created_by,
1907 l_okc_rule_groups_tl_rec.creation_date,
1908 l_okc_rule_groups_tl_rec.last_updated_by,
1909 l_okc_rule_groups_tl_rec.last_update_date,
1910 l_okc_rule_groups_tl_rec.last_update_login);
1911 END LOOP;
1912 -- Set OUT values
1913 x_okc_rule_groups_tl_rec := l_okc_rule_groups_tl_rec;
1914 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1915 EXCEPTION
1916 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1917 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1918 (
1919 l_api_name,
1920 G_PKG_NAME,
1921 'OKC_API.G_RET_STS_ERROR',
1922 x_msg_count,
1923 x_msg_data,
1924 '_PVT'
1925 );
1926 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1927 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1928 (
1929 l_api_name,
1930 G_PKG_NAME,
1931 'OKC_API.G_RET_STS_UNEXP_ERROR',
1932 x_msg_count,
1933 x_msg_data,
1934 '_PVT'
1935 );
1936 WHEN OTHERS THEN
1937 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1938 (
1939 l_api_name,
1940 G_PKG_NAME,
1941 'OTHERS',
1942 x_msg_count,
1943 x_msg_data,
1944 '_PVT'
1945 );
1946 END insert_row;
1947 --------------------------------------
1948 -- insert_row for:OKC_RULE_GROUPS_V --
1949 --------------------------------------
1950 PROCEDURE insert_row(
1951 p_api_version IN NUMBER,
1952 p_init_msg_list IN VARCHAR2,
1953 x_return_status OUT NOCOPY VARCHAR2,
1954 x_msg_count OUT NOCOPY NUMBER,
1955 x_msg_data OUT NOCOPY VARCHAR2,
1956 p_rgpv_rec IN rgpv_rec_type,
1957 x_rgpv_rec OUT NOCOPY rgpv_rec_type) IS
1958
1959 l_api_version CONSTANT NUMBER := 1;
1960 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1961 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1962 l_rgpv_rec rgpv_rec_type;
1963 l_def_rgpv_rec rgpv_rec_type;
1964 l_rgp_rec rgp_rec_type;
1965 lx_rgp_rec rgp_rec_type;
1966 l_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
1967 lx_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
1968 -------------------------------
1969 -- FUNCTION fill_who_columns --
1970 -------------------------------
1971 FUNCTION fill_who_columns (
1972 p_rgpv_rec IN rgpv_rec_type
1973 ) RETURN rgpv_rec_type IS
1974 l_rgpv_rec rgpv_rec_type := p_rgpv_rec;
1975 BEGIN
1976 l_rgpv_rec.CREATION_DATE := SYSDATE;
1977 l_rgpv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1978 l_rgpv_rec.LAST_UPDATE_DATE := SYSDATE;
1979 l_rgpv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1980 l_rgpv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1981 RETURN(l_rgpv_rec);
1982 END fill_who_columns;
1983 ------------------------------------------
1984 -- Set_Attributes for:OKC_RULE_GROUPS_V --
1985 ------------------------------------------
1986 FUNCTION Set_Attributes (
1987 p_rgpv_rec IN rgpv_rec_type,
1988 x_rgpv_rec OUT NOCOPY rgpv_rec_type
1989 ) RETURN VARCHAR2 IS
1990 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1991 BEGIN
1992 x_rgpv_rec := p_rgpv_rec;
1993 x_rgpv_rec.OBJECT_VERSION_NUMBER := 1;
1994 x_rgpv_rec.SFWT_FLAG := 'N';
1995 RETURN(l_return_status);
1996 END Set_Attributes;
1997 BEGIN
1998 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1999 G_PKG_NAME,
2000 p_init_msg_list,
2001 l_api_version,
2002 p_api_version,
2003 '_PVT',
2004 x_return_status);
2005 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2006 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2007 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2008 RAISE OKC_API.G_EXCEPTION_ERROR;
2009 END IF;
2010 l_rgpv_rec := null_out_defaults(p_rgpv_rec);
2011 -- Set primary key value
2012 l_rgpv_rec.ID := get_seq_id;
2013 --- Setting item attributes
2014 l_return_status := Set_Attributes(
2015 l_rgpv_rec, -- IN
2016 l_def_rgpv_rec); -- OUT
2017 --- If any errors happen abort API
2018 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2019 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2020 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2021 RAISE OKC_API.G_EXCEPTION_ERROR;
2022 END IF;
2023 l_def_rgpv_rec := fill_who_columns(l_def_rgpv_rec);
2024 --- Validate all non-missing attributes (Item Level Validation)
2025 l_return_status := Validate_Attributes(l_def_rgpv_rec);
2026 --- If any errors happen abort API
2027 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2028 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2029 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2030 RAISE OKC_API.G_EXCEPTION_ERROR;
2031 END IF;
2032 l_return_status := Validate_Record(l_def_rgpv_rec);
2033 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2034 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2035 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2036 RAISE OKC_API.G_EXCEPTION_ERROR;
2037 END IF;
2038 --------------------------------------
2039 -- Move VIEW record to "Child" records
2040 --------------------------------------
2041 migrate(l_def_rgpv_rec, l_rgp_rec);
2042 migrate(l_def_rgpv_rec, l_okc_rule_groups_tl_rec);
2043 --------------------------------------------
2044 -- Call the INSERT_ROW for each child record
2045 --------------------------------------------
2046 insert_row(
2047 p_init_msg_list,
2048 x_return_status,
2049 x_msg_count,
2050 x_msg_data,
2051 l_rgp_rec,
2052 lx_rgp_rec
2053 );
2054 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2055 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2056 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2057 RAISE OKC_API.G_EXCEPTION_ERROR;
2058 END IF;
2059 migrate(lx_rgp_rec, l_def_rgpv_rec);
2060 insert_row(
2061 p_init_msg_list,
2062 x_return_status,
2063 x_msg_count,
2064 x_msg_data,
2065 l_okc_rule_groups_tl_rec,
2066 lx_okc_rule_groups_tl_rec
2067 );
2068 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2069 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2070 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2071 RAISE OKC_API.G_EXCEPTION_ERROR;
2072 END IF;
2073 migrate(lx_okc_rule_groups_tl_rec, l_def_rgpv_rec);
2074 -- Set OUT values
2075 x_rgpv_rec := l_def_rgpv_rec;
2076 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2077 EXCEPTION
2078 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2079 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2080 (
2081 l_api_name,
2082 G_PKG_NAME,
2083 'OKC_API.G_RET_STS_ERROR',
2084 x_msg_count,
2085 x_msg_data,
2086 '_PVT'
2087 );
2088 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2089 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2090 (
2091 l_api_name,
2092 G_PKG_NAME,
2093 'OKC_API.G_RET_STS_UNEXP_ERROR',
2094 x_msg_count,
2095 x_msg_data,
2096 '_PVT'
2097 );
2098 WHEN OTHERS THEN
2099 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2100 (
2101 l_api_name,
2102 G_PKG_NAME,
2103 'OTHERS',
2104 x_msg_count,
2105 x_msg_data,
2106 '_PVT'
2107 );
2108 END insert_row;
2109 ----------------------------------------
2110 -- PL/SQL TBL insert_row for:RGPV_TBL --
2111 ----------------------------------------
2112 PROCEDURE insert_row(
2113 p_api_version IN NUMBER,
2114 p_init_msg_list IN VARCHAR2,
2115 x_return_status OUT NOCOPY VARCHAR2,
2116 x_msg_count OUT NOCOPY NUMBER,
2117 x_msg_data OUT NOCOPY VARCHAR2,
2118 p_rgpv_tbl IN rgpv_tbl_type,
2119 x_rgpv_tbl OUT NOCOPY rgpv_tbl_type) IS
2120
2121 l_api_version CONSTANT NUMBER := 1;
2122 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
2123 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2124 i NUMBER := 0;
2125 BEGIN
2126 OKC_API.init_msg_list(p_init_msg_list);
2127 -- Make sure PL/SQL table has records in it before passing
2128 IF (p_rgpv_tbl.COUNT > 0) THEN
2129 i := p_rgpv_tbl.FIRST;
2130 LOOP
2131 insert_row (
2132 p_api_version => p_api_version,
2133 p_init_msg_list => OKC_API.G_FALSE,
2134 x_return_status => x_return_status,
2135 x_msg_count => x_msg_count,
2136 x_msg_data => x_msg_data,
2137 p_rgpv_rec => p_rgpv_tbl(i),
2138 x_rgpv_rec => x_rgpv_tbl(i));
2139 EXIT WHEN (i = p_rgpv_tbl.LAST);
2140 i := p_rgpv_tbl.NEXT(i);
2141 END LOOP;
2142 END IF;
2143 EXCEPTION
2144 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2145 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2146 (
2147 l_api_name,
2148 G_PKG_NAME,
2149 'OKC_API.G_RET_STS_ERROR',
2150 x_msg_count,
2151 x_msg_data,
2152 '_PVT'
2153 );
2154 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2155 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2156 (
2157 l_api_name,
2158 G_PKG_NAME,
2159 'OKC_API.G_RET_STS_UNEXP_ERROR',
2160 x_msg_count,
2161 x_msg_data,
2162 '_PVT'
2163 );
2164 WHEN OTHERS THEN
2165 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2166 (
2167 l_api_name,
2168 G_PKG_NAME,
2169 'OTHERS',
2170 x_msg_count,
2171 x_msg_data,
2172 '_PVT'
2173 );
2174 END insert_row;
2175
2176 ---------------------------------------------------------------------------
2177 -- PROCEDURE lock_row
2178 ---------------------------------------------------------------------------
2179 ------------------------------------
2180 -- lock_row for:OKC_RULE_GROUPS_B --
2181 ------------------------------------
2182 PROCEDURE lock_row(
2183 p_init_msg_list IN VARCHAR2,
2184 x_return_status OUT NOCOPY VARCHAR2,
2185 x_msg_count OUT NOCOPY NUMBER,
2186 x_msg_data OUT NOCOPY VARCHAR2,
2187 p_rgp_rec IN rgp_rec_type) IS
2188
2189 E_Resource_Busy EXCEPTION;
2190 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2191 CURSOR lock_csr (p_rgp_rec IN rgp_rec_type) IS
2192 SELECT OBJECT_VERSION_NUMBER
2193 FROM OKC_RULE_GROUPS_B
2194 WHERE ID = p_rgp_rec.id
2195 AND OBJECT_VERSION_NUMBER = p_rgp_rec.object_version_number
2196 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
2197
2198 CURSOR lchk_csr (p_rgp_rec IN rgp_rec_type) IS
2199 SELECT OBJECT_VERSION_NUMBER
2200 FROM OKC_RULE_GROUPS_B
2201 WHERE ID = p_rgp_rec.id;
2202 l_api_version CONSTANT NUMBER := 1;
2203 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
2204 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2205 l_object_version_number OKC_RULE_GROUPS_B.OBJECT_VERSION_NUMBER%TYPE;
2206 lc_object_version_number OKC_RULE_GROUPS_B.OBJECT_VERSION_NUMBER%TYPE;
2207 l_row_notfound BOOLEAN := FALSE;
2208 lc_row_notfound BOOLEAN := FALSE;
2209 BEGIN
2210 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2211 p_init_msg_list,
2212 '_PVT',
2213 x_return_status);
2214 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2215 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2216 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2217 RAISE OKC_API.G_EXCEPTION_ERROR;
2218 END IF;
2219 BEGIN
2220 OPEN lock_csr(p_rgp_rec);
2221 FETCH lock_csr INTO l_object_version_number;
2222 l_row_notfound := lock_csr%NOTFOUND;
2223 CLOSE lock_csr;
2224 EXCEPTION
2225 WHEN E_Resource_Busy THEN
2226 IF (lock_csr%ISOPEN) THEN
2227 CLOSE lock_csr;
2228 END IF;
2229 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2230 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2231 END;
2232
2233 IF ( l_row_notfound ) THEN
2234 OPEN lchk_csr(p_rgp_rec);
2235 FETCH lchk_csr INTO lc_object_version_number;
2236 lc_row_notfound := lchk_csr%NOTFOUND;
2237 CLOSE lchk_csr;
2238 END IF;
2239 IF (lc_row_notfound) THEN
2240 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2241 RAISE OKC_API.G_EXCEPTION_ERROR;
2242 ELSIF lc_object_version_number > p_rgp_rec.object_version_number THEN
2243 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2244 RAISE OKC_API.G_EXCEPTION_ERROR;
2245 ELSIF lc_object_version_number <> p_rgp_rec.object_version_number THEN
2246 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
2247 RAISE OKC_API.G_EXCEPTION_ERROR;
2248 ELSIF lc_object_version_number = -1 THEN
2249 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
2250 RAISE OKC_API.G_EXCEPTION_ERROR;
2251 END IF;
2252 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2253 EXCEPTION
2254 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2255 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2256 (
2257 l_api_name,
2258 G_PKG_NAME,
2259 'OKC_API.G_RET_STS_ERROR',
2260 x_msg_count,
2261 x_msg_data,
2262 '_PVT'
2263 );
2264 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2265 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2266 (
2267 l_api_name,
2268 G_PKG_NAME,
2269 'OKC_API.G_RET_STS_UNEXP_ERROR',
2270 x_msg_count,
2271 x_msg_data,
2272 '_PVT'
2273 );
2274 WHEN OTHERS THEN
2275 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2276 (
2277 l_api_name,
2278 G_PKG_NAME,
2279 'OTHERS',
2280 x_msg_count,
2281 x_msg_data,
2282 '_PVT'
2283 );
2284 END lock_row;
2285 -------------------------------------
2286 -- lock_row for:OKC_RULE_GROUPS_TL --
2287 -------------------------------------
2288 PROCEDURE lock_row(
2289 p_init_msg_list IN VARCHAR2,
2290 x_return_status OUT NOCOPY VARCHAR2,
2291 x_msg_count OUT NOCOPY NUMBER,
2292 x_msg_data OUT NOCOPY VARCHAR2,
2293 p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type) IS
2294
2295 E_Resource_Busy EXCEPTION;
2296 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
2297 CURSOR lock_csr (p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type) IS
2298 SELECT *
2299 FROM OKC_RULE_GROUPS_TL
2300 WHERE ID = p_okc_rule_groups_tl_rec.id
2301 FOR UPDATE NOWAIT;
2302
2303 l_api_version CONSTANT NUMBER := 1;
2304 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
2305 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2306 l_lock_var lock_csr%ROWTYPE;
2307 l_row_notfound BOOLEAN := FALSE;
2308 lc_row_notfound BOOLEAN := FALSE;
2309 BEGIN
2310 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2311 p_init_msg_list,
2312 '_PVT',
2313 x_return_status);
2314 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2315 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2316 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2317 RAISE OKC_API.G_EXCEPTION_ERROR;
2318 END IF;
2319 BEGIN
2320 OPEN lock_csr(p_okc_rule_groups_tl_rec);
2321 FETCH lock_csr INTO l_lock_var;
2322 l_row_notfound := lock_csr%NOTFOUND;
2323 CLOSE lock_csr;
2324 EXCEPTION
2325 WHEN E_Resource_Busy THEN
2326 IF (lock_csr%ISOPEN) THEN
2327 CLOSE lock_csr;
2328 END IF;
2329 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
2330 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
2331 END;
2332
2333 IF ( l_row_notfound ) THEN
2334 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
2335 RAISE OKC_API.G_EXCEPTION_ERROR;
2336 END IF;
2337 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2338 EXCEPTION
2339 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2340 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2341 (
2342 l_api_name,
2343 G_PKG_NAME,
2344 'OKC_API.G_RET_STS_ERROR',
2345 x_msg_count,
2346 x_msg_data,
2347 '_PVT'
2348 );
2349 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2350 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2351 (
2352 l_api_name,
2353 G_PKG_NAME,
2354 'OKC_API.G_RET_STS_UNEXP_ERROR',
2355 x_msg_count,
2356 x_msg_data,
2357 '_PVT'
2358 );
2359 WHEN OTHERS THEN
2360 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2361 (
2362 l_api_name,
2363 G_PKG_NAME,
2364 'OTHERS',
2365 x_msg_count,
2366 x_msg_data,
2367 '_PVT'
2368 );
2369 END lock_row;
2370 ------------------------------------
2371 -- lock_row for:OKC_RULE_GROUPS_V --
2372 ------------------------------------
2373 PROCEDURE lock_row(
2374 p_api_version IN NUMBER,
2375 p_init_msg_list IN VARCHAR2,
2376 x_return_status OUT NOCOPY VARCHAR2,
2377 x_msg_count OUT NOCOPY NUMBER,
2378 x_msg_data OUT NOCOPY VARCHAR2,
2379 p_rgpv_rec IN rgpv_rec_type) IS
2380
2381 l_api_version CONSTANT NUMBER := 1;
2382 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2383 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2384 l_rgp_rec rgp_rec_type;
2385 l_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
2386 BEGIN
2387 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2388 G_PKG_NAME,
2389 p_init_msg_list,
2390 l_api_version,
2391 p_api_version,
2392 '_PVT',
2393 x_return_status);
2394 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2395 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2396 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2397 RAISE OKC_API.G_EXCEPTION_ERROR;
2398 END IF;
2399 --------------------------------------
2400 -- Move VIEW record to "Child" records
2401 --------------------------------------
2402 migrate(p_rgpv_rec, l_rgp_rec);
2403 migrate(p_rgpv_rec, l_okc_rule_groups_tl_rec);
2404 --------------------------------------------
2405 -- Call the LOCK_ROW for each child record
2406 --------------------------------------------
2407 lock_row(
2408 p_init_msg_list,
2409 x_return_status,
2410 x_msg_count,
2411 x_msg_data,
2412 l_rgp_rec
2413 );
2414 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2415 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2416 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2417 RAISE OKC_API.G_EXCEPTION_ERROR;
2418 END IF;
2419 lock_row(
2420 p_init_msg_list,
2421 x_return_status,
2422 x_msg_count,
2423 x_msg_data,
2424 l_okc_rule_groups_tl_rec
2425 );
2426 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2427 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2428 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2429 RAISE OKC_API.G_EXCEPTION_ERROR;
2430 END IF;
2431 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2432 EXCEPTION
2433 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2434 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2435 (
2436 l_api_name,
2437 G_PKG_NAME,
2438 'OKC_API.G_RET_STS_ERROR',
2439 x_msg_count,
2440 x_msg_data,
2441 '_PVT'
2442 );
2443 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2444 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2445 (
2446 l_api_name,
2447 G_PKG_NAME,
2448 'OKC_API.G_RET_STS_UNEXP_ERROR',
2449 x_msg_count,
2450 x_msg_data,
2451 '_PVT'
2452 );
2453 WHEN OTHERS THEN
2454 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2455 (
2456 l_api_name,
2457 G_PKG_NAME,
2458 'OTHERS',
2459 x_msg_count,
2460 x_msg_data,
2461 '_PVT'
2462 );
2463 END lock_row;
2464 --------------------------------------
2465 -- PL/SQL TBL lock_row for:RGPV_TBL --
2466 --------------------------------------
2467 PROCEDURE lock_row(
2468 p_api_version IN NUMBER,
2469 p_init_msg_list IN VARCHAR2,
2470 x_return_status OUT NOCOPY VARCHAR2,
2471 x_msg_count OUT NOCOPY NUMBER,
2472 x_msg_data OUT NOCOPY VARCHAR2,
2473 p_rgpv_tbl IN rgpv_tbl_type) IS
2474
2475 l_api_version CONSTANT NUMBER := 1;
2476 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2477 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2478 i NUMBER := 0;
2479 BEGIN
2480 OKC_API.init_msg_list(p_init_msg_list);
2481 -- Make sure PL/SQL table has records in it before passing
2482 IF (p_rgpv_tbl.COUNT > 0) THEN
2483 i := p_rgpv_tbl.FIRST;
2484 LOOP
2485 lock_row (
2486 p_api_version => p_api_version,
2487 p_init_msg_list => OKC_API.G_FALSE,
2488 x_return_status => x_return_status,
2489 x_msg_count => x_msg_count,
2490 x_msg_data => x_msg_data,
2491 p_rgpv_rec => p_rgpv_tbl(i));
2492 EXIT WHEN (i = p_rgpv_tbl.LAST);
2493 i := p_rgpv_tbl.NEXT(i);
2494 END LOOP;
2495 END IF;
2496 EXCEPTION
2497 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2498 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2499 (
2500 l_api_name,
2501 G_PKG_NAME,
2502 'OKC_API.G_RET_STS_ERROR',
2503 x_msg_count,
2504 x_msg_data,
2505 '_PVT'
2506 );
2507 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2508 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2509 (
2510 l_api_name,
2511 G_PKG_NAME,
2512 'OKC_API.G_RET_STS_UNEXP_ERROR',
2513 x_msg_count,
2514 x_msg_data,
2515 '_PVT'
2516 );
2517 WHEN OTHERS THEN
2518 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2519 (
2520 l_api_name,
2521 G_PKG_NAME,
2522 'OTHERS',
2523 x_msg_count,
2524 x_msg_data,
2525 '_PVT'
2526 );
2527 END lock_row;
2528
2529 ---------------------------------------------------------------------------
2530 -- PROCEDURE update_row
2531 ---------------------------------------------------------------------------
2532 --------------------------------------
2533 -- update_row for:OKC_RULE_GROUPS_B --
2534 --------------------------------------
2535 PROCEDURE update_row(
2536 p_init_msg_list IN VARCHAR2,
2537 x_return_status OUT NOCOPY VARCHAR2,
2538 x_msg_count OUT NOCOPY NUMBER,
2539 x_msg_data OUT NOCOPY VARCHAR2,
2540 p_rgp_rec IN rgp_rec_type,
2541 x_rgp_rec OUT NOCOPY rgp_rec_type) IS
2542
2543 l_api_version CONSTANT NUMBER := 1;
2544 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
2545 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2546 l_rgp_rec rgp_rec_type := p_rgp_rec;
2547 l_def_rgp_rec rgp_rec_type;
2548 l_row_notfound BOOLEAN := TRUE;
2549 ----------------------------------
2550 -- FUNCTION populate_new_record --
2551 ----------------------------------
2552 FUNCTION populate_new_record (
2553 p_rgp_rec IN rgp_rec_type,
2554 x_rgp_rec OUT NOCOPY rgp_rec_type
2555 ) RETURN VARCHAR2 IS
2556 l_rgp_rec rgp_rec_type;
2557 l_row_notfound BOOLEAN := TRUE;
2558 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2559 BEGIN
2560 x_rgp_rec := p_rgp_rec;
2561 -- Get current database values
2562 l_rgp_rec := get_rec(p_rgp_rec, l_row_notfound);
2563 IF (l_row_notfound) THEN
2564 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2565 END IF;
2566 IF (x_rgp_rec.id = OKC_API.G_MISS_NUM)
2567 THEN
2568 x_rgp_rec.id := l_rgp_rec.id;
2569 END IF;
2570 IF (x_rgp_rec.rgd_code = OKC_API.G_MISS_CHAR)
2571 THEN
2572 x_rgp_rec.rgd_code := l_rgp_rec.rgd_code;
2573 END IF;
2574 IF (x_rgp_rec.sat_code = OKC_API.G_MISS_CHAR)
2575 THEN
2576 x_rgp_rec.sat_code := l_rgp_rec.sat_code;
2577 END IF;
2578 IF (x_rgp_rec.rgp_type = OKC_API.G_MISS_CHAR)
2579 THEN
2580 x_rgp_rec.rgp_type := l_rgp_rec.rgp_type;
2581 END IF;
2582 IF (x_rgp_rec.chr_id = OKC_API.G_MISS_NUM)
2583 THEN
2584 x_rgp_rec.chr_id := l_rgp_rec.chr_id;
2585 END IF;
2586 IF (x_rgp_rec.cle_id = OKC_API.G_MISS_NUM)
2587 THEN
2588 x_rgp_rec.cle_id := l_rgp_rec.cle_id;
2589 END IF;
2590 IF (x_rgp_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
2591 THEN
2592 x_rgp_rec.dnz_chr_id := l_rgp_rec.dnz_chr_id;
2593 END IF;
2594 IF (x_rgp_rec.parent_rgp_id = OKC_API.G_MISS_NUM)
2595 THEN
2596 x_rgp_rec.parent_rgp_id := l_rgp_rec.parent_rgp_id;
2597 END IF;
2598 IF (x_rgp_rec.object_version_number = OKC_API.G_MISS_NUM)
2599 THEN
2600 x_rgp_rec.object_version_number := l_rgp_rec.object_version_number;
2601 END IF;
2602 IF (x_rgp_rec.created_by = OKC_API.G_MISS_NUM)
2603 THEN
2604 x_rgp_rec.created_by := l_rgp_rec.created_by;
2605 END IF;
2606 IF (x_rgp_rec.creation_date = OKC_API.G_MISS_DATE)
2607 THEN
2608 x_rgp_rec.creation_date := l_rgp_rec.creation_date;
2609 END IF;
2610 IF (x_rgp_rec.last_updated_by = OKC_API.G_MISS_NUM)
2611 THEN
2612 x_rgp_rec.last_updated_by := l_rgp_rec.last_updated_by;
2613 END IF;
2614 IF (x_rgp_rec.last_update_date = OKC_API.G_MISS_DATE)
2615 THEN
2616 x_rgp_rec.last_update_date := l_rgp_rec.last_update_date;
2617 END IF;
2618 IF (x_rgp_rec.last_update_login = OKC_API.G_MISS_NUM)
2619 THEN
2620 x_rgp_rec.last_update_login := l_rgp_rec.last_update_login;
2621 END IF;
2622 IF (x_rgp_rec.attribute_category = OKC_API.G_MISS_CHAR)
2623 THEN
2624 x_rgp_rec.attribute_category := l_rgp_rec.attribute_category;
2625 END IF;
2626 IF (x_rgp_rec.attribute1 = OKC_API.G_MISS_CHAR)
2627 THEN
2628 x_rgp_rec.attribute1 := l_rgp_rec.attribute1;
2629 END IF;
2630 IF (x_rgp_rec.attribute2 = OKC_API.G_MISS_CHAR)
2631 THEN
2632 x_rgp_rec.attribute2 := l_rgp_rec.attribute2;
2633 END IF;
2634 IF (x_rgp_rec.attribute3 = OKC_API.G_MISS_CHAR)
2635 THEN
2636 x_rgp_rec.attribute3 := l_rgp_rec.attribute3;
2637 END IF;
2638 IF (x_rgp_rec.attribute4 = OKC_API.G_MISS_CHAR)
2639 THEN
2640 x_rgp_rec.attribute4 := l_rgp_rec.attribute4;
2641 END IF;
2642 IF (x_rgp_rec.attribute5 = OKC_API.G_MISS_CHAR)
2643 THEN
2644 x_rgp_rec.attribute5 := l_rgp_rec.attribute5;
2645 END IF;
2646 IF (x_rgp_rec.attribute6 = OKC_API.G_MISS_CHAR)
2647 THEN
2648 x_rgp_rec.attribute6 := l_rgp_rec.attribute6;
2649 END IF;
2650 IF (x_rgp_rec.attribute7 = OKC_API.G_MISS_CHAR)
2651 THEN
2652 x_rgp_rec.attribute7 := l_rgp_rec.attribute7;
2653 END IF;
2654 IF (x_rgp_rec.attribute8 = OKC_API.G_MISS_CHAR)
2655 THEN
2656 x_rgp_rec.attribute8 := l_rgp_rec.attribute8;
2657 END IF;
2658 IF (x_rgp_rec.attribute9 = OKC_API.G_MISS_CHAR)
2659 THEN
2660 x_rgp_rec.attribute9 := l_rgp_rec.attribute9;
2661 END IF;
2662 IF (x_rgp_rec.attribute10 = OKC_API.G_MISS_CHAR)
2663 THEN
2664 x_rgp_rec.attribute10 := l_rgp_rec.attribute10;
2665 END IF;
2666 IF (x_rgp_rec.attribute11 = OKC_API.G_MISS_CHAR)
2667 THEN
2668 x_rgp_rec.attribute11 := l_rgp_rec.attribute11;
2669 END IF;
2670 IF (x_rgp_rec.attribute12 = OKC_API.G_MISS_CHAR)
2671 THEN
2672 x_rgp_rec.attribute12 := l_rgp_rec.attribute12;
2673 END IF;
2674 IF (x_rgp_rec.attribute13 = OKC_API.G_MISS_CHAR)
2675 THEN
2676 x_rgp_rec.attribute13 := l_rgp_rec.attribute13;
2677 END IF;
2678 IF (x_rgp_rec.attribute14 = OKC_API.G_MISS_CHAR)
2679 THEN
2680 x_rgp_rec.attribute14 := l_rgp_rec.attribute14;
2681 END IF;
2682 IF (x_rgp_rec.attribute15 = OKC_API.G_MISS_CHAR)
2683 THEN
2684 x_rgp_rec.attribute15 := l_rgp_rec.attribute15;
2685 END IF;
2686 RETURN(l_return_status);
2687 END populate_new_record;
2688 ------------------------------------------
2689 -- Set_Attributes for:OKC_RULE_GROUPS_B --
2690 ------------------------------------------
2691 FUNCTION Set_Attributes (
2692 p_rgp_rec IN rgp_rec_type,
2693 x_rgp_rec OUT NOCOPY rgp_rec_type
2694 ) RETURN VARCHAR2 IS
2695 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2696 BEGIN
2697 x_rgp_rec := p_rgp_rec;
2698 RETURN(l_return_status);
2699 END Set_Attributes;
2700 BEGIN
2701 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2702 p_init_msg_list,
2703 '_PVT',
2704 x_return_status);
2705 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2706 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2707 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2708 RAISE OKC_API.G_EXCEPTION_ERROR;
2709 END IF;
2710 --- Setting item attributes
2711 l_return_status := Set_Attributes(
2712 p_rgp_rec, -- IN
2713 l_rgp_rec); -- OUT
2714 --- If any errors happen abort API
2715 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2716 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2717 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2718 RAISE OKC_API.G_EXCEPTION_ERROR;
2719 END IF;
2720 l_return_status := populate_new_record(l_rgp_rec, l_def_rgp_rec);
2721 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2722 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2723 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2724 RAISE OKC_API.G_EXCEPTION_ERROR;
2725 END IF;
2726 UPDATE OKC_RULE_GROUPS_B
2727 SET RGD_CODE = l_def_rgp_rec.rgd_code,
2728 SAT_CODE = l_def_rgp_rec.sat_code,
2729 RGP_TYPE = l_def_rgp_rec.rgp_type,
2730 CHR_ID = l_def_rgp_rec.chr_id,
2731 CLE_ID = l_def_rgp_rec.cle_id,
2732 DNZ_CHR_ID = l_def_rgp_rec.dnz_chr_id,
2733 PARENT_RGP_ID = l_def_rgp_rec.parent_rgp_id,
2734 OBJECT_VERSION_NUMBER = l_def_rgp_rec.object_version_number,
2735 CREATED_BY = l_def_rgp_rec.created_by,
2736 CREATION_DATE = l_def_rgp_rec.creation_date,
2737 LAST_UPDATED_BY = l_def_rgp_rec.last_updated_by,
2738 LAST_UPDATE_DATE = l_def_rgp_rec.last_update_date,
2739 LAST_UPDATE_LOGIN = l_def_rgp_rec.last_update_login,
2740 ATTRIBUTE_CATEGORY = l_def_rgp_rec.attribute_category,
2741 ATTRIBUTE1 = l_def_rgp_rec.attribute1,
2742 ATTRIBUTE2 = l_def_rgp_rec.attribute2,
2743 ATTRIBUTE3 = l_def_rgp_rec.attribute3,
2744 ATTRIBUTE4 = l_def_rgp_rec.attribute4,
2745 ATTRIBUTE5 = l_def_rgp_rec.attribute5,
2746 ATTRIBUTE6 = l_def_rgp_rec.attribute6,
2747 ATTRIBUTE7 = l_def_rgp_rec.attribute7,
2748 ATTRIBUTE8 = l_def_rgp_rec.attribute8,
2749 ATTRIBUTE9 = l_def_rgp_rec.attribute9,
2750 ATTRIBUTE10 = l_def_rgp_rec.attribute10,
2751 ATTRIBUTE11 = l_def_rgp_rec.attribute11,
2752 ATTRIBUTE12 = l_def_rgp_rec.attribute12,
2753 ATTRIBUTE13 = l_def_rgp_rec.attribute13,
2754 ATTRIBUTE14 = l_def_rgp_rec.attribute14,
2755 ATTRIBUTE15 = l_def_rgp_rec.attribute15
2756 WHERE ID = l_def_rgp_rec.id;
2757
2758 x_rgp_rec := l_def_rgp_rec;
2759 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2760 EXCEPTION
2761 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2762 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2763 (
2764 l_api_name,
2765 G_PKG_NAME,
2766 'OKC_API.G_RET_STS_ERROR',
2767 x_msg_count,
2768 x_msg_data,
2769 '_PVT'
2770 );
2771 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2772 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2773 (
2774 l_api_name,
2775 G_PKG_NAME,
2776 'OKC_API.G_RET_STS_UNEXP_ERROR',
2777 x_msg_count,
2778 x_msg_data,
2779 '_PVT'
2780 );
2781 WHEN OTHERS THEN
2782 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2783 (
2784 l_api_name,
2785 G_PKG_NAME,
2786 'OTHERS',
2787 x_msg_count,
2788 x_msg_data,
2789 '_PVT'
2790 );
2791 END update_row;
2792 ---------------------------------------
2793 -- update_row for:OKC_RULE_GROUPS_TL --
2794 ---------------------------------------
2795 PROCEDURE update_row(
2796 p_init_msg_list IN VARCHAR2,
2797 x_return_status OUT NOCOPY VARCHAR2,
2798 x_msg_count OUT NOCOPY NUMBER,
2799 x_msg_data OUT NOCOPY VARCHAR2,
2800 p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type,
2801 x_okc_rule_groups_tl_rec OUT NOCOPY okc_rule_groups_tl_rec_type) IS
2802
2803 l_api_version CONSTANT NUMBER := 1;
2804 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
2805 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2806 l_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type := p_okc_rule_groups_tl_rec;
2807 l_def_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
2808 l_row_notfound BOOLEAN := TRUE;
2809 ----------------------------------
2810 -- FUNCTION populate_new_record --
2811 ----------------------------------
2812 FUNCTION populate_new_record (
2813 p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type,
2814 x_okc_rule_groups_tl_rec OUT NOCOPY okc_rule_groups_tl_rec_type
2815 ) RETURN VARCHAR2 IS
2816 l_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
2817 l_row_notfound BOOLEAN := TRUE;
2818 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2819 BEGIN
2820 x_okc_rule_groups_tl_rec := p_okc_rule_groups_tl_rec;
2821 -- Get current database values
2822 l_okc_rule_groups_tl_rec := get_rec(p_okc_rule_groups_tl_rec, l_row_notfound);
2823 IF (l_row_notfound) THEN
2824 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2825 END IF;
2826 IF (x_okc_rule_groups_tl_rec.id = OKC_API.G_MISS_NUM)
2827 THEN
2828 x_okc_rule_groups_tl_rec.id := l_okc_rule_groups_tl_rec.id;
2829 END IF;
2830 IF (x_okc_rule_groups_tl_rec.language = OKC_API.G_MISS_CHAR)
2831 THEN
2832 x_okc_rule_groups_tl_rec.language := l_okc_rule_groups_tl_rec.language;
2833 END IF;
2834 IF (x_okc_rule_groups_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
2835 THEN
2836 x_okc_rule_groups_tl_rec.source_lang := l_okc_rule_groups_tl_rec.source_lang;
2837 END IF;
2838 IF (x_okc_rule_groups_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
2839 THEN
2840 x_okc_rule_groups_tl_rec.sfwt_flag := l_okc_rule_groups_tl_rec.sfwt_flag;
2841 END IF;
2842 IF (x_okc_rule_groups_tl_rec.comments = OKC_API.G_MISS_CHAR)
2843 THEN
2844 x_okc_rule_groups_tl_rec.comments := l_okc_rule_groups_tl_rec.comments;
2845 END IF;
2846 IF (x_okc_rule_groups_tl_rec.created_by = OKC_API.G_MISS_NUM)
2847 THEN
2848 x_okc_rule_groups_tl_rec.created_by := l_okc_rule_groups_tl_rec.created_by;
2849 END IF;
2850 IF (x_okc_rule_groups_tl_rec.creation_date = OKC_API.G_MISS_DATE)
2851 THEN
2852 x_okc_rule_groups_tl_rec.creation_date := l_okc_rule_groups_tl_rec.creation_date;
2853 END IF;
2854 IF (x_okc_rule_groups_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
2855 THEN
2856 x_okc_rule_groups_tl_rec.last_updated_by := l_okc_rule_groups_tl_rec.last_updated_by;
2857 END IF;
2858 IF (x_okc_rule_groups_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
2859 THEN
2860 x_okc_rule_groups_tl_rec.last_update_date := l_okc_rule_groups_tl_rec.last_update_date;
2861 END IF;
2862 IF (x_okc_rule_groups_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
2863 THEN
2864 x_okc_rule_groups_tl_rec.last_update_login := l_okc_rule_groups_tl_rec.last_update_login;
2865 END IF;
2866 RETURN(l_return_status);
2867 END populate_new_record;
2868 -------------------------------------------
2869 -- Set_Attributes for:OKC_RULE_GROUPS_TL --
2870 -------------------------------------------
2871 FUNCTION Set_Attributes (
2872 p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type,
2873 x_okc_rule_groups_tl_rec OUT NOCOPY okc_rule_groups_tl_rec_type
2874 ) RETURN VARCHAR2 IS
2875 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2876 BEGIN
2877 x_okc_rule_groups_tl_rec := p_okc_rule_groups_tl_rec;
2878 x_okc_rule_groups_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
2879 x_okc_rule_groups_tl_rec.SOURCE_LANG := okc_util.get_userenv_lang;
2880 RETURN(l_return_status);
2881 END Set_Attributes;
2882 BEGIN
2883 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2884 p_init_msg_list,
2885 '_PVT',
2886 x_return_status);
2887 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2888 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2889 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2890 RAISE OKC_API.G_EXCEPTION_ERROR;
2891 END IF;
2892 --- Setting item attributes
2893 l_return_status := Set_Attributes(
2894 p_okc_rule_groups_tl_rec, -- IN
2895 l_okc_rule_groups_tl_rec); -- OUT
2896 --- If any errors happen abort API
2897 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2898 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2899 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2900 RAISE OKC_API.G_EXCEPTION_ERROR;
2901 END IF;
2902 l_return_status := populate_new_record(l_okc_rule_groups_tl_rec, l_def_okc_rule_groups_tl_rec);
2903 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2904 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2905 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2906 RAISE OKC_API.G_EXCEPTION_ERROR;
2907 END IF;
2908 UPDATE OKC_RULE_GROUPS_TL
2909 SET COMMENTS = l_def_okc_rule_groups_tl_rec.comments,
2910 CREATED_BY = l_def_okc_rule_groups_tl_rec.created_by,
2911 CREATION_DATE = l_def_okc_rule_groups_tl_rec.creation_date,
2912 LAST_UPDATED_BY = l_def_okc_rule_groups_tl_rec.last_updated_by,
2913 LAST_UPDATE_DATE = l_def_okc_rule_groups_tl_rec.last_update_date,
2914 LAST_UPDATE_LOGIN = l_def_okc_rule_groups_tl_rec.last_update_login
2915 WHERE ID = l_def_okc_rule_groups_tl_rec.id
2916 AND SOURCE_LANG = USERENV('LANG');
2917
2918 UPDATE OKC_RULE_GROUPS_TL
2919 SET SFWT_FLAG = 'Y'
2920 WHERE ID = l_def_okc_rule_groups_tl_rec.id
2921 AND SOURCE_LANG <> USERENV('LANG');
2922
2923 x_okc_rule_groups_tl_rec := l_def_okc_rule_groups_tl_rec;
2924 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2925 EXCEPTION
2926 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2927 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2928 (
2929 l_api_name,
2930 G_PKG_NAME,
2931 'OKC_API.G_RET_STS_ERROR',
2932 x_msg_count,
2933 x_msg_data,
2934 '_PVT'
2935 );
2936 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2937 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2938 (
2939 l_api_name,
2940 G_PKG_NAME,
2941 'OKC_API.G_RET_STS_UNEXP_ERROR',
2942 x_msg_count,
2943 x_msg_data,
2944 '_PVT'
2945 );
2946 WHEN OTHERS THEN
2947 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2948 (
2949 l_api_name,
2950 G_PKG_NAME,
2951 'OTHERS',
2952 x_msg_count,
2953 x_msg_data,
2954 '_PVT'
2955 );
2956 END update_row;
2957 --------------------------------------
2958 -- update_row for:OKC_RULE_GROUPS_V --
2959 --------------------------------------
2960 PROCEDURE update_row(
2961 p_api_version IN NUMBER,
2962 p_init_msg_list IN VARCHAR2,
2963 x_return_status OUT NOCOPY VARCHAR2,
2964 x_msg_count OUT NOCOPY NUMBER,
2965 x_msg_data OUT NOCOPY VARCHAR2,
2966 p_rgpv_rec IN rgpv_rec_type,
2967 x_rgpv_rec OUT NOCOPY rgpv_rec_type) IS
2968
2969 l_api_version CONSTANT NUMBER := 1;
2970 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2971 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2972 l_rgpv_rec rgpv_rec_type := p_rgpv_rec;
2973 l_def_rgpv_rec rgpv_rec_type;
2974 l_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
2975 lx_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
2976 l_rgp_rec rgp_rec_type;
2977 lx_rgp_rec rgp_rec_type;
2978 -------------------------------
2979 -- FUNCTION fill_who_columns --
2980 -------------------------------
2981 FUNCTION fill_who_columns (
2982 p_rgpv_rec IN rgpv_rec_type
2983 ) RETURN rgpv_rec_type IS
2984 l_rgpv_rec rgpv_rec_type := p_rgpv_rec;
2985 BEGIN
2986 l_rgpv_rec.LAST_UPDATE_DATE := SYSDATE;
2987 l_rgpv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2988 l_rgpv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2989 RETURN(l_rgpv_rec);
2990 END fill_who_columns;
2991 ----------------------------------
2992 -- FUNCTION populate_new_record --
2993 ----------------------------------
2994 FUNCTION populate_new_record (
2995 p_rgpv_rec IN rgpv_rec_type,
2996 x_rgpv_rec OUT NOCOPY rgpv_rec_type
2997 ) RETURN VARCHAR2 IS
2998 l_rgpv_rec rgpv_rec_type;
2999 l_row_notfound BOOLEAN := TRUE;
3000 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3001 BEGIN
3002 x_rgpv_rec := p_rgpv_rec;
3003 -- Get current database values
3004 l_rgpv_rec := get_rec(p_rgpv_rec, l_row_notfound);
3005 IF (l_row_notfound) THEN
3006 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3007 END IF;
3008 IF (x_rgpv_rec.id = OKC_API.G_MISS_NUM)
3009 THEN
3010 x_rgpv_rec.id := l_rgpv_rec.id;
3011 END IF;
3012 IF (x_rgpv_rec.object_version_number = OKC_API.G_MISS_NUM)
3013 THEN
3014 x_rgpv_rec.object_version_number := l_rgpv_rec.object_version_number;
3015 END IF;
3016 IF (x_rgpv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
3017 THEN
3018 x_rgpv_rec.sfwt_flag := l_rgpv_rec.sfwt_flag;
3019 END IF;
3020 IF (x_rgpv_rec.rgd_code = OKC_API.G_MISS_CHAR)
3021 THEN
3022 x_rgpv_rec.rgd_code := l_rgpv_rec.rgd_code;
3023 END IF;
3024 IF (x_rgpv_rec.sat_code = OKC_API.G_MISS_CHAR)
3025 THEN
3026 x_rgpv_rec.sat_code := l_rgpv_rec.sat_code;
3027 END IF;
3028 IF (x_rgpv_rec.rgp_type = OKC_API.G_MISS_CHAR)
3029 THEN
3030 x_rgpv_rec.rgp_type := l_rgpv_rec.rgp_type;
3031 END IF;
3032 IF (x_rgpv_rec.cle_id = OKC_API.G_MISS_NUM)
3033 THEN
3034 x_rgpv_rec.cle_id := l_rgpv_rec.cle_id;
3035 END IF;
3036 IF (x_rgpv_rec.chr_id = OKC_API.G_MISS_NUM)
3037 THEN
3038 x_rgpv_rec.chr_id := l_rgpv_rec.chr_id;
3039 END IF;
3040 IF (x_rgpv_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
3041 THEN
3042 x_rgpv_rec.dnz_chr_id := l_rgpv_rec.dnz_chr_id;
3043 END IF;
3044 IF (x_rgpv_rec.parent_rgp_id = OKC_API.G_MISS_NUM)
3045 THEN
3046 x_rgpv_rec.parent_rgp_id := l_rgpv_rec.parent_rgp_id;
3047 END IF;
3048 IF (x_rgpv_rec.comments = OKC_API.G_MISS_CHAR)
3049 THEN
3050 x_rgpv_rec.comments := l_rgpv_rec.comments;
3051 END IF;
3052 IF (x_rgpv_rec.attribute_category = OKC_API.G_MISS_CHAR)
3053 THEN
3054 x_rgpv_rec.attribute_category := l_rgpv_rec.attribute_category;
3055 END IF;
3056 IF (x_rgpv_rec.attribute1 = OKC_API.G_MISS_CHAR)
3057 THEN
3058 x_rgpv_rec.attribute1 := l_rgpv_rec.attribute1;
3059 END IF;
3060 IF (x_rgpv_rec.attribute2 = OKC_API.G_MISS_CHAR)
3061 THEN
3062 x_rgpv_rec.attribute2 := l_rgpv_rec.attribute2;
3063 END IF;
3064 IF (x_rgpv_rec.attribute3 = OKC_API.G_MISS_CHAR)
3065 THEN
3066 x_rgpv_rec.attribute3 := l_rgpv_rec.attribute3;
3067 END IF;
3068 IF (x_rgpv_rec.attribute4 = OKC_API.G_MISS_CHAR)
3069 THEN
3070 x_rgpv_rec.attribute4 := l_rgpv_rec.attribute4;
3071 END IF;
3072 IF (x_rgpv_rec.attribute5 = OKC_API.G_MISS_CHAR)
3073 THEN
3074 x_rgpv_rec.attribute5 := l_rgpv_rec.attribute5;
3075 END IF;
3076 IF (x_rgpv_rec.attribute6 = OKC_API.G_MISS_CHAR)
3077 THEN
3078 x_rgpv_rec.attribute6 := l_rgpv_rec.attribute6;
3079 END IF;
3080 IF (x_rgpv_rec.attribute7 = OKC_API.G_MISS_CHAR)
3081 THEN
3082 x_rgpv_rec.attribute7 := l_rgpv_rec.attribute7;
3083 END IF;
3084 IF (x_rgpv_rec.attribute8 = OKC_API.G_MISS_CHAR)
3085 THEN
3086 x_rgpv_rec.attribute8 := l_rgpv_rec.attribute8;
3087 END IF;
3088 IF (x_rgpv_rec.attribute9 = OKC_API.G_MISS_CHAR)
3089 THEN
3090 x_rgpv_rec.attribute9 := l_rgpv_rec.attribute9;
3091 END IF;
3092 IF (x_rgpv_rec.attribute10 = OKC_API.G_MISS_CHAR)
3093 THEN
3094 x_rgpv_rec.attribute10 := l_rgpv_rec.attribute10;
3095 END IF;
3096 IF (x_rgpv_rec.attribute11 = OKC_API.G_MISS_CHAR)
3097 THEN
3098 x_rgpv_rec.attribute11 := l_rgpv_rec.attribute11;
3099 END IF;
3100 IF (x_rgpv_rec.attribute12 = OKC_API.G_MISS_CHAR)
3101 THEN
3102 x_rgpv_rec.attribute12 := l_rgpv_rec.attribute12;
3103 END IF;
3104 IF (x_rgpv_rec.attribute13 = OKC_API.G_MISS_CHAR)
3105 THEN
3106 x_rgpv_rec.attribute13 := l_rgpv_rec.attribute13;
3107 END IF;
3108 IF (x_rgpv_rec.attribute14 = OKC_API.G_MISS_CHAR)
3109 THEN
3110 x_rgpv_rec.attribute14 := l_rgpv_rec.attribute14;
3111 END IF;
3112 IF (x_rgpv_rec.attribute15 = OKC_API.G_MISS_CHAR)
3113 THEN
3114 x_rgpv_rec.attribute15 := l_rgpv_rec.attribute15;
3115 END IF;
3116 IF (x_rgpv_rec.created_by = OKC_API.G_MISS_NUM)
3117 THEN
3118 x_rgpv_rec.created_by := l_rgpv_rec.created_by;
3119 END IF;
3120 IF (x_rgpv_rec.creation_date = OKC_API.G_MISS_DATE)
3121 THEN
3122 x_rgpv_rec.creation_date := l_rgpv_rec.creation_date;
3123 END IF;
3124 IF (x_rgpv_rec.last_updated_by = OKC_API.G_MISS_NUM)
3125 THEN
3126 x_rgpv_rec.last_updated_by := l_rgpv_rec.last_updated_by;
3127 END IF;
3128 IF (x_rgpv_rec.last_update_date = OKC_API.G_MISS_DATE)
3129 THEN
3130 x_rgpv_rec.last_update_date := l_rgpv_rec.last_update_date;
3131 END IF;
3132 IF (x_rgpv_rec.last_update_login = OKC_API.G_MISS_NUM)
3133 THEN
3134 x_rgpv_rec.last_update_login := l_rgpv_rec.last_update_login;
3135 END IF;
3136 RETURN(l_return_status);
3137 END populate_new_record;
3138 ------------------------------------------
3139 -- Set_Attributes for:OKC_RULE_GROUPS_V --
3140 ------------------------------------------
3141 FUNCTION Set_Attributes (
3142 p_rgpv_rec IN rgpv_rec_type,
3143 x_rgpv_rec OUT NOCOPY rgpv_rec_type
3144 ) RETURN VARCHAR2 IS
3145 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3146 BEGIN
3147 x_rgpv_rec := p_rgpv_rec;
3148 x_rgpv_rec.OBJECT_VERSION_NUMBER := NVL(x_rgpv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
3149 RETURN(l_return_status);
3150 END Set_Attributes;
3151 BEGIN
3152 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3153 G_PKG_NAME,
3154 p_init_msg_list,
3155 l_api_version,
3156 p_api_version,
3157 '_PVT',
3158 x_return_status);
3159 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3160 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3161 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3162 RAISE OKC_API.G_EXCEPTION_ERROR;
3163 END IF;
3164 --- Setting item attributes
3165 l_return_status := Set_Attributes(
3166 p_rgpv_rec, -- IN
3167 l_rgpv_rec); -- OUT
3168 --- If any errors happen abort API
3169 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3170 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3171 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3172 RAISE OKC_API.G_EXCEPTION_ERROR;
3173 END IF;
3174 l_return_status := populate_new_record(l_rgpv_rec, l_def_rgpv_rec);
3175 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3176 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3177 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3178 RAISE OKC_API.G_EXCEPTION_ERROR;
3179 END IF;
3180 l_def_rgpv_rec := fill_who_columns(l_def_rgpv_rec);
3181 --- Validate all non-missing attributes (Item Level Validation)
3182 l_return_status := Validate_Attributes(l_def_rgpv_rec);
3183 --- If any errors happen abort API
3184 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3185 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3186 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3187 RAISE OKC_API.G_EXCEPTION_ERROR;
3188 END IF;
3189 l_return_status := Validate_Record(l_def_rgpv_rec);
3190 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3191 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3192 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3193 RAISE OKC_API.G_EXCEPTION_ERROR;
3194 END IF;
3195
3196 --------------------------------------
3197 -- Move VIEW record to "Child" records
3198 --------------------------------------
3199 migrate(l_def_rgpv_rec, l_okc_rule_groups_tl_rec);
3200 migrate(l_def_rgpv_rec, l_rgp_rec);
3201 --------------------------------------------
3202 -- Call the UPDATE_ROW for each child record
3203 --------------------------------------------
3204 update_row(
3205 p_init_msg_list,
3206 x_return_status,
3207 x_msg_count,
3208 x_msg_data,
3209 l_okc_rule_groups_tl_rec,
3210 lx_okc_rule_groups_tl_rec
3211 );
3212 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3213 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3214 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3215 RAISE OKC_API.G_EXCEPTION_ERROR;
3216 END IF;
3217 migrate(lx_okc_rule_groups_tl_rec, l_def_rgpv_rec);
3218 update_row(
3219 p_init_msg_list,
3220 x_return_status,
3221 x_msg_count,
3222 x_msg_data,
3223 l_rgp_rec,
3224 lx_rgp_rec
3225 );
3226 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3227 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3228 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3229 RAISE OKC_API.G_EXCEPTION_ERROR;
3230 END IF;
3231 migrate(lx_rgp_rec, l_def_rgpv_rec);
3232 x_rgpv_rec := l_def_rgpv_rec;
3233 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3234 EXCEPTION
3235 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3236 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3237 (
3238 l_api_name,
3239 G_PKG_NAME,
3240 'OKC_API.G_RET_STS_ERROR',
3241 x_msg_count,
3242 x_msg_data,
3243 '_PVT'
3244 );
3245 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3246 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3247 (
3248 l_api_name,
3249 G_PKG_NAME,
3250 'OKC_API.G_RET_STS_UNEXP_ERROR',
3251 x_msg_count,
3252 x_msg_data,
3253 '_PVT'
3254 );
3255 WHEN OTHERS THEN
3256 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3257 (
3258 l_api_name,
3259 G_PKG_NAME,
3260 'OTHERS',
3261 x_msg_count,
3262 x_msg_data,
3263 '_PVT'
3264 );
3265 END update_row;
3266 ----------------------------------------
3267 -- PL/SQL TBL update_row for:RGPV_TBL --
3268 ----------------------------------------
3269 PROCEDURE update_row(
3270 p_api_version IN NUMBER,
3271 p_init_msg_list IN VARCHAR2,
3272 x_return_status OUT NOCOPY VARCHAR2,
3273 x_msg_count OUT NOCOPY NUMBER,
3274 x_msg_data OUT NOCOPY VARCHAR2,
3275 p_rgpv_tbl IN rgpv_tbl_type,
3276 x_rgpv_tbl OUT NOCOPY rgpv_tbl_type) IS
3277
3278 l_api_version CONSTANT NUMBER := 1;
3279 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
3280 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3281 i NUMBER := 0;
3282 BEGIN
3283 OKC_API.init_msg_list(p_init_msg_list);
3284 -- Make sure PL/SQL table has records in it before passing
3285 IF (p_rgpv_tbl.COUNT > 0) THEN
3286 i := p_rgpv_tbl.FIRST;
3287 LOOP
3288 update_row (
3289 p_api_version => p_api_version,
3290 p_init_msg_list => OKC_API.G_FALSE,
3291 x_return_status => x_return_status,
3292 x_msg_count => x_msg_count,
3293 x_msg_data => x_msg_data,
3294 p_rgpv_rec => p_rgpv_tbl(i),
3295 x_rgpv_rec => x_rgpv_tbl(i));
3296 EXIT WHEN (i = p_rgpv_tbl.LAST);
3297 i := p_rgpv_tbl.NEXT(i);
3298 END LOOP;
3299 END IF;
3300 EXCEPTION
3301 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3302 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3303 (
3304 l_api_name,
3305 G_PKG_NAME,
3306 'OKC_API.G_RET_STS_ERROR',
3307 x_msg_count,
3308 x_msg_data,
3309 '_PVT'
3310 );
3311 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3312 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3313 (
3314 l_api_name,
3315 G_PKG_NAME,
3316 'OKC_API.G_RET_STS_UNEXP_ERROR',
3317 x_msg_count,
3318 x_msg_data,
3319 '_PVT'
3320 );
3321 WHEN OTHERS THEN
3322 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3323 (
3324 l_api_name,
3325 G_PKG_NAME,
3326 'OTHERS',
3327 x_msg_count,
3328 x_msg_data,
3329 '_PVT'
3330 );
3331 END update_row;
3332
3333 ---------------------------------------------------------------------------
3334 -- PROCEDURE delete_row
3335 ---------------------------------------------------------------------------
3336 --------------------------------------
3337 -- delete_row for:OKC_RULE_GROUPS_B --
3338 --------------------------------------
3339 PROCEDURE delete_row(
3340 p_init_msg_list IN VARCHAR2,
3341 x_return_status OUT NOCOPY VARCHAR2,
3342 x_msg_count OUT NOCOPY NUMBER,
3343 x_msg_data OUT NOCOPY VARCHAR2,
3344 p_rgp_rec IN rgp_rec_type) IS
3345
3346 l_api_version CONSTANT NUMBER := 1;
3347 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
3348 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3349 l_rgp_rec rgp_rec_type:= p_rgp_rec;
3350 l_row_notfound BOOLEAN := TRUE;
3351 BEGIN
3352 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3353 p_init_msg_list,
3354 '_PVT',
3355 x_return_status);
3356 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3357 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3358 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3359 RAISE OKC_API.G_EXCEPTION_ERROR;
3360 END IF;
3361 DELETE FROM OKC_RULE_GROUPS_B
3362 WHERE ID = l_rgp_rec.id;
3363
3364 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3365 EXCEPTION
3366 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3367 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3368 (
3369 l_api_name,
3370 G_PKG_NAME,
3371 'OKC_API.G_RET_STS_ERROR',
3372 x_msg_count,
3373 x_msg_data,
3374 '_PVT'
3375 );
3376 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3377 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3378 (
3379 l_api_name,
3380 G_PKG_NAME,
3381 'OKC_API.G_RET_STS_UNEXP_ERROR',
3382 x_msg_count,
3383 x_msg_data,
3384 '_PVT'
3385 );
3386 WHEN OTHERS THEN
3387 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3388 (
3389 l_api_name,
3390 G_PKG_NAME,
3391 'OTHERS',
3392 x_msg_count,
3393 x_msg_data,
3394 '_PVT'
3395 );
3396 END delete_row;
3397 ---------------------------------------
3398 -- delete_row for:OKC_RULE_GROUPS_TL --
3399 ---------------------------------------
3400 PROCEDURE delete_row(
3401 p_init_msg_list IN VARCHAR2,
3402 x_return_status OUT NOCOPY VARCHAR2,
3403 x_msg_count OUT NOCOPY NUMBER,
3404 x_msg_data OUT NOCOPY VARCHAR2,
3405 p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type) IS
3406
3407 l_api_version CONSTANT NUMBER := 1;
3408 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
3409 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3410 l_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type:= p_okc_rule_groups_tl_rec;
3411 l_row_notfound BOOLEAN := TRUE;
3412 -------------------------------------------
3413 -- Set_Attributes for:OKC_RULE_GROUPS_TL --
3414 -------------------------------------------
3415 FUNCTION Set_Attributes (
3416 p_okc_rule_groups_tl_rec IN okc_rule_groups_tl_rec_type,
3417 x_okc_rule_groups_tl_rec OUT NOCOPY okc_rule_groups_tl_rec_type
3418 ) RETURN VARCHAR2 IS
3419 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3420 BEGIN
3421 x_okc_rule_groups_tl_rec := p_okc_rule_groups_tl_rec;
3422 x_okc_rule_groups_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
3423 RETURN(l_return_status);
3424 END Set_Attributes;
3425 BEGIN
3426 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3427 p_init_msg_list,
3428 '_PVT',
3429 x_return_status);
3430 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3431 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3432 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3433 RAISE OKC_API.G_EXCEPTION_ERROR;
3434 END IF;
3435 --- Setting item attributes
3436 l_return_status := Set_Attributes(
3437 p_okc_rule_groups_tl_rec, -- IN
3438 l_okc_rule_groups_tl_rec); -- OUT
3439 --- If any errors happen abort API
3440 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3441 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3442 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3443 RAISE OKC_API.G_EXCEPTION_ERROR;
3444 END IF;
3445 DELETE FROM OKC_RULE_GROUPS_TL
3446 WHERE ID = l_okc_rule_groups_tl_rec.id;
3447
3448 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3449 EXCEPTION
3450 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3451 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3452 (
3453 l_api_name,
3454 G_PKG_NAME,
3455 'OKC_API.G_RET_STS_ERROR',
3456 x_msg_count,
3457 x_msg_data,
3458 '_PVT'
3459 );
3460 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3461 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3462 (
3463 l_api_name,
3464 G_PKG_NAME,
3465 'OKC_API.G_RET_STS_UNEXP_ERROR',
3466 x_msg_count,
3467 x_msg_data,
3468 '_PVT'
3469 );
3470 WHEN OTHERS THEN
3471 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3472 (
3473 l_api_name,
3474 G_PKG_NAME,
3475 'OTHERS',
3476 x_msg_count,
3477 x_msg_data,
3478 '_PVT'
3479 );
3480 END delete_row;
3481 --------------------------------------
3482 -- delete_row for:OKC_RULE_GROUPS_V --
3483 --------------------------------------
3484 PROCEDURE delete_row(
3485 p_api_version IN NUMBER,
3486 p_init_msg_list IN VARCHAR2,
3487 x_return_status OUT NOCOPY VARCHAR2,
3488 x_msg_count OUT NOCOPY NUMBER,
3489 x_msg_data OUT NOCOPY VARCHAR2,
3490 p_rgpv_rec IN rgpv_rec_type) IS
3491
3492 l_api_version CONSTANT NUMBER := 1;
3493 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3494 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3495 l_rgpv_rec rgpv_rec_type := p_rgpv_rec;
3496 l_okc_rule_groups_tl_rec okc_rule_groups_tl_rec_type;
3497 l_rgp_rec rgp_rec_type;
3498 BEGIN
3499 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3500 G_PKG_NAME,
3501 p_init_msg_list,
3502 l_api_version,
3503 p_api_version,
3504 '_PVT',
3505 x_return_status);
3506 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3507 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3508 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3509 RAISE OKC_API.G_EXCEPTION_ERROR;
3510 END IF;
3511 --------------------------------------
3512 -- Move VIEW record to "Child" records
3513 --------------------------------------
3514 migrate(l_rgpv_rec, l_okc_rule_groups_tl_rec);
3515 migrate(l_rgpv_rec, l_rgp_rec);
3516 --------------------------------------------
3517 -- Call the DELETE_ROW for each child record
3518 --------------------------------------------
3519 delete_row(
3520 p_init_msg_list,
3521 x_return_status,
3522 x_msg_count,
3523 x_msg_data,
3524 l_okc_rule_groups_tl_rec
3525 );
3526 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3527 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3528 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3529 RAISE OKC_API.G_EXCEPTION_ERROR;
3530 END IF;
3531 delete_row(
3532 p_init_msg_list,
3533 x_return_status,
3534 x_msg_count,
3535 x_msg_data,
3536 l_rgp_rec
3537 );
3538 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3539 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3540 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3541 RAISE OKC_API.G_EXCEPTION_ERROR;
3542 END IF;
3543 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3544 EXCEPTION
3545 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3546 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3547 (
3548 l_api_name,
3549 G_PKG_NAME,
3550 'OKC_API.G_RET_STS_ERROR',
3551 x_msg_count,
3552 x_msg_data,
3553 '_PVT'
3554 );
3555 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3556 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3557 (
3558 l_api_name,
3559 G_PKG_NAME,
3560 'OKC_API.G_RET_STS_UNEXP_ERROR',
3561 x_msg_count,
3562 x_msg_data,
3563 '_PVT'
3564 );
3565 WHEN OTHERS THEN
3566 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3567 (
3568 l_api_name,
3569 G_PKG_NAME,
3570 'OTHERS',
3571 x_msg_count,
3572 x_msg_data,
3573 '_PVT'
3574 );
3575 END delete_row;
3576 ----------------------------------------
3577 -- PL/SQL TBL delete_row for:RGPV_TBL --
3578 ----------------------------------------
3579 PROCEDURE delete_row(
3580 p_api_version IN NUMBER,
3581 p_init_msg_list IN VARCHAR2,
3582 x_return_status OUT NOCOPY VARCHAR2,
3583 x_msg_count OUT NOCOPY NUMBER,
3584 x_msg_data OUT NOCOPY VARCHAR2,
3585 p_rgpv_tbl IN rgpv_tbl_type) IS
3586
3587 l_api_version CONSTANT NUMBER := 1;
3588 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3589 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3590 i NUMBER := 0;
3591 BEGIN
3592 OKC_API.init_msg_list(p_init_msg_list);
3593 -- Make sure PL/SQL table has records in it before passing
3594 IF (p_rgpv_tbl.COUNT > 0) THEN
3595 i := p_rgpv_tbl.FIRST;
3596 LOOP
3597 delete_row (
3598 p_api_version => p_api_version,
3599 p_init_msg_list => OKC_API.G_FALSE,
3600 x_return_status => x_return_status,
3601 x_msg_count => x_msg_count,
3602 x_msg_data => x_msg_data,
3603 p_rgpv_rec => p_rgpv_tbl(i));
3604 EXIT WHEN (i = p_rgpv_tbl.LAST);
3605 i := p_rgpv_tbl.NEXT(i);
3606 END LOOP;
3607 END IF;
3608 EXCEPTION
3609 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3610 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3611 (
3612 l_api_name,
3613 G_PKG_NAME,
3614 'OKC_API.G_RET_STS_ERROR',
3615 x_msg_count,
3616 x_msg_data,
3617 '_PVT'
3618 );
3619 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3620 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3621 (
3622 l_api_name,
3623 G_PKG_NAME,
3624 'OKC_API.G_RET_STS_UNEXP_ERROR',
3625 x_msg_count,
3626 x_msg_data,
3627 '_PVT'
3628 );
3629 WHEN OTHERS THEN
3630 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3631 (
3632 l_api_name,
3633 G_PKG_NAME,
3634 'OTHERS',
3635 x_msg_count,
3636 x_msg_data,
3637 '_PVT'
3638 );
3639 END delete_row;
3640
3641 ---------------------------------------------------------------
3642 -- Procedure for mass insert in OKC_RULE_GROUPS _B and TL tables
3643 ---------------------------------------------------------------
3644 PROCEDURE INSERT_ROW_UPG(x_return_status OUT NOCOPY VARCHAR2,p_rgpv_tbl rgpv_tbl_type) IS
3645 l_tabsize NUMBER := p_rgpv_tbl.COUNT;
3646 l_source_lang VARCHAR2(12) := okc_util.get_userenv_lang;
3647
3648 in_id OKC_DATATYPES.NumberTabTyp;
3649 in_object_version_number OKC_DATATYPES.NumberTabTyp;
3650 in_rgp_type OKC_DATATYPES.Var10TabTyp;
3651 in_sfwt_flag OKC_DATATYPES.Var3TabTyp;
3652 in_rgd_code OKC_DATATYPES.Var30TabTyp;
3653 in_cle_id OKC_DATATYPES.NumberTabTyp;
3654 in_chr_id OKC_DATATYPES.NumberTabTyp;
3655 in_dnz_chr_id OKC_DATATYPES.NumberTabTyp;
3656 in_parent_rgp_id OKC_DATATYPES.NumberTabTyp;
3657 in_sat_code OKC_DATATYPES.Var30TabTyp;
3658 in_comments OKC_DATATYPES.Var1995TabTyp;
3659 in_attribute_category OKC_DATATYPES.Var90TabTyp;
3660 in_attribute1 OKC_DATATYPES.Var450TabTyp;
3661 in_attribute2 OKC_DATATYPES.Var450TabTyp;
3662 in_attribute3 OKC_DATATYPES.Var450TabTyp;
3663 in_attribute4 OKC_DATATYPES.Var450TabTyp;
3664 in_attribute5 OKC_DATATYPES.Var450TabTyp;
3665 in_attribute6 OKC_DATATYPES.Var450TabTyp;
3666 in_attribute7 OKC_DATATYPES.Var450TabTyp;
3667 in_attribute8 OKC_DATATYPES.Var450TabTyp;
3668 in_attribute9 OKC_DATATYPES.Var450TabTyp;
3669 in_attribute10 OKC_DATATYPES.Var450TabTyp;
3670 in_attribute11 OKC_DATATYPES.Var450TabTyp;
3671 in_attribute12 OKC_DATATYPES.Var450TabTyp;
3672 in_attribute13 OKC_DATATYPES.Var450TabTyp;
3673 in_attribute14 OKC_DATATYPES.Var450TabTyp;
3674 in_attribute15 OKC_DATATYPES.Var450TabTyp;
3675 in_created_by OKC_DATATYPES.NumberTabTyp;
3676 in_creation_date OKC_DATATYPES.DateTabTyp;
3677 in_last_updated_by OKC_DATATYPES.NumberTabTyp;
3678 in_last_update_date OKC_DATATYPES.DateTabTyp;
3679 in_last_update_login OKC_DATATYPES.NumberTabTyp;
3680 i number;
3681 j number;
3682 BEGIN
3683 --Initialize return status
3684 x_return_status := OKC_API.G_RET_STS_SUCCESS;
3685 i := p_rgpv_tbl.FIRST; j:=0;
3686 while i is not null
3687 LOOP
3688 j:=j+1;
3689 in_id (j) := p_rgpv_tbl(i).id;
3690 in_object_version_number (j) := p_rgpv_tbl(i).object_version_number;
3691 in_rgp_type (j) := p_rgpv_tbl(i).rgp_type;
3692 in_sfwt_flag (j) := p_rgpv_tbl(i).sfwt_flag;
3693 in_rgd_code (j) := p_rgpv_tbl(i).rgd_code;
3694 in_cle_id (j) := p_rgpv_tbl(i).cle_id;
3695 in_chr_id (j) := p_rgpv_tbl(i).chr_id;
3696 in_dnz_chr_id (j) := p_rgpv_tbl(i).dnz_chr_id;
3697 in_parent_rgp_id (j) := p_rgpv_tbl(i).parent_rgp_id;
3698 in_sat_code (j) := p_rgpv_tbl(i).sat_code;
3699 in_comments (j) := p_rgpv_tbl(i).comments;
3700 in_attribute_category (j) := p_rgpv_tbl(i).attribute_category;
3701 in_attribute1 (j) := p_rgpv_tbl(i).attribute1;
3702 in_attribute2 (j) := p_rgpv_tbl(i).attribute2;
3703 in_attribute3 (j) := p_rgpv_tbl(i).attribute3;
3704 in_attribute4 (j) := p_rgpv_tbl(i).attribute4;
3705 in_attribute5 (j) := p_rgpv_tbl(i).attribute5;
3706 in_attribute6 (j) := p_rgpv_tbl(i).attribute6;
3707 in_attribute7 (j) := p_rgpv_tbl(i).attribute7;
3708 in_attribute8 (j) := p_rgpv_tbl(i).attribute8;
3709 in_attribute9 (j) := p_rgpv_tbl(i).attribute9;
3710 in_attribute10 (j) := p_rgpv_tbl(i).attribute10;
3711 in_attribute11 (j) := p_rgpv_tbl(i).attribute11;
3712 in_attribute12 (j) := p_rgpv_tbl(i).attribute12;
3713 in_attribute13 (j) := p_rgpv_tbl(i).attribute13;
3714 in_attribute14 (j) := p_rgpv_tbl(i).attribute14;
3715 in_attribute15 (j) := p_rgpv_tbl(i).attribute15;
3716 in_created_by (j) := p_rgpv_tbl(i).created_by;
3717 in_creation_date (j) := p_rgpv_tbl(i).creation_date;
3718 in_last_updated_by (j) := p_rgpv_tbl(i).last_updated_by;
3719 in_last_update_date (j) := p_rgpv_tbl(i).last_update_date;
3720 in_last_update_login (j) := p_rgpv_tbl(i).last_update_login;
3721 i:=p_rgpv_tbl.next(i);
3722 END LOOP;
3723
3724 FORALL i in 1..l_tabsize
3725 INSERT
3726 INTO OKC_RULE_GROUPS_B
3727 (
3728 id,
3729 rgd_code,
3730 chr_id,
3731 cle_id,
3732 dnz_chr_id,
3733 parent_rgp_id,
3734 sat_code,
3735 object_version_number,
3736 rgp_type,
3737 created_by,
3738 creation_date,
3739 last_updated_by,
3740 last_update_date,
3741 last_update_login,
3742 attribute_category,
3743 attribute1,
3744 attribute2,
3745 attribute3,
3746 attribute4,
3747 attribute5,
3748 attribute6,
3749 attribute7,
3750 attribute8,
3751 attribute9,
3752 attribute10,
3753 attribute11,
3754 attribute12,
3755 attribute13,
3756 attribute14,
3757 attribute15
3758 -- REMOVE comma from the previous line
3759 )
3760 VALUES (
3761 in_id(i),
3762 in_rgd_code(i),
3763 in_chr_id(i),
3764 in_cle_id(i),
3765 in_dnz_chr_id(i),
3766 in_parent_rgp_id(i),
3767 in_sat_code(i),
3768 in_object_version_number(i),
3769 in_rgp_type(i),
3770 in_created_by(i),
3771 in_creation_date(i),
3772 in_last_updated_by(i),
3773 in_last_update_date(i),
3774 in_last_update_login(i),
3775 in_attribute_category(i),
3776 in_attribute1(i),
3777 in_attribute2(i),
3778 in_attribute3(i),
3779 in_attribute4(i),
3780 in_attribute5(i),
3781 in_attribute6(i),
3782 in_attribute7(i),
3783 in_attribute8(i),
3784 in_attribute9(i),
3785 in_attribute10(i),
3786 in_attribute11(i),
3787 in_attribute12(i),
3788 in_attribute13(i),
3789 in_attribute14(i),
3790 in_attribute15(i)
3791 -- REMOVE comma from the previous line
3792 );
3793
3794 FOR lang_i IN OKC_UTIL.g_language_code.FIRST..OKC_UTIL.g_language_code.LAST LOOP
3795 FORALL i in 1..l_tabsize
3796 INSERT INTO OKC_RULE_GROUPS_TL(
3797 id,
3798 language,
3799 source_lang,
3800 sfwt_flag,
3801 comments,
3802 created_by,
3803 creation_date,
3804 last_updated_by,
3805 last_update_date,
3806 last_update_login
3807 -- REMOVE comma from the previous line
3808 )
3809 VALUES (
3810 in_id(i),
3811 OKC_UTIL.g_language_code(lang_i),
3812 l_source_lang,
3813 in_sfwt_flag(i),
3814 in_comments(i),
3815 in_created_by(i),
3816 in_creation_date(i),
3817 in_last_updated_by(i),
3818 in_last_update_date(i),
3819 in_last_update_login(i)
3820 -- REMOVE comma from the previous line
3821 );
3822 END LOOP;
3823 EXCEPTION
3824 WHEN OTHERS THEN
3825 -- store SQL error message on message stack
3826 OKC_API.SET_MESSAGE(
3827 p_app_name => G_APP_NAME,
3828 p_msg_name => G_UNEXPECTED_ERROR,
3829 p_token1 => G_SQLCODE_TOKEN,
3830 p_token1_value => SQLCODE,
3831 p_token2 => G_SQLERRM_TOKEN,
3832 p_token2_value => SQLERRM);
3833 -- notify caller of an error as UNEXPECTED error
3834 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3835 --RAISE;
3836 END INSERT_ROW_UPG;
3837
3838 --This function is called from versioning API OKC_VERSION_PVT
3839 --Old Location: OKCRVERB.pls
3840 --New Location: Base Table API
3841
3842 FUNCTION create_version(
3843 p_chr_id IN NUMBER,
3844 p_major_version IN NUMBER
3845 ) RETURN VARCHAR2 IS
3846
3847 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
3848
3849 BEGIN
3850 INSERT INTO okc_rule_groups_bh
3851 (
3852 major_version,
3853 id,
3854 rgd_code,
3855 chr_id,
3856 cle_id,
3857 dnz_chr_id,
3858 parent_rgp_id,
3859 sat_code,
3860 object_version_number,
3861 rgp_type,
3862 created_by,
3863 creation_date,
3864 last_updated_by,
3865 last_update_date,
3866 last_update_login,
3867 attribute_category,
3868 attribute1,
3869 attribute2,
3870 attribute3,
3871 attribute4,
3872 attribute5,
3873 attribute6,
3874 attribute7,
3875 attribute8,
3876 attribute9,
3877 attribute10,
3878 attribute11,
3879 attribute12,
3880 attribute13,
3881 attribute14,
3882 attribute15
3883 )
3884 SELECT
3885 p_major_version,
3886 id,
3887 rgd_code,
3888 chr_id,
3889 cle_id,
3890 dnz_chr_id,
3891 parent_rgp_id,
3892 sat_code,
3893 object_version_number,
3894 rgp_type,
3895 created_by,
3896 creation_date,
3897 last_updated_by,
3898 last_update_date,
3899 last_update_login,
3900 attribute_category,
3901 attribute1,
3902 attribute2,
3903 attribute3,
3904 attribute4,
3905 attribute5,
3906 attribute6,
3907 attribute7,
3908 attribute8,
3909 attribute9,
3910 attribute10,
3911 attribute11,
3912 attribute12,
3913 attribute13,
3914 attribute14,
3915 attribute15
3916 FROM okc_rule_groups_b
3917 WHERE dnz_chr_id = p_chr_id;
3918
3919 -------------------------------
3920 -- Versioning TL Table
3921 -------------------------------
3922
3923 INSERT INTO okc_rule_groups_tlh
3924 (
3925 major_version,
3926 id,
3927 language,
3928 source_lang,
3929 sfwt_flag,
3930 comments,
3931 created_by,
3932 creation_date,
3933 last_updated_by,
3934 last_update_date,
3935 last_update_login
3936 )
3937 SELECT
3938 p_major_version,
3939 id,
3940 language,
3941 source_lang,
3942 sfwt_flag,
3943 comments,
3944 created_by,
3945 creation_date,
3946 last_updated_by,
3947 last_update_date,
3948 last_update_login
3949 FROM okc_rule_groups_tl
3950 WHERE id in (select id
3951 from okc_rule_groups_b
3952 where dnz_chr_id = p_chr_id);
3953
3954 -- Lines changed above Dated 10/06/2000
3955
3956 RETURN l_return_status;
3957 EXCEPTION
3958 -- other appropriate handlers
3959 WHEN OTHERS THEN
3960 -- store SQL error message on message stack
3961 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
3962 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
3963 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
3964 p_token1_value => sqlcode,
3965 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
3966 p_token2_value => sqlerrm);
3967
3968 -- notify UNEXPECTED error
3969 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3970 return l_return_status;
3971 END create_version;
3972
3973 --This Function is called from Versioning API OKC_VERSION_PVT
3974 --Old Location:OKCRVERB.pls
3975 --New Location:Base Table API
3976
3977 FUNCTION restore_version(
3978 p_chr_id IN NUMBER,
3979 p_major_version IN NUMBER
3980 ) RETURN VARCHAR2 IS
3981
3982 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
3983
3984 BEGIN
3985 INSERT INTO okc_rule_groups_tl
3986 (
3987 id,
3988 language,
3989 source_lang,
3990 sfwt_flag,
3991 comments,
3992 created_by,
3993 creation_date,
3994 last_updated_by,
3995 last_update_date,
3996 last_update_login
3997 )
3998 SELECT
3999 id,
4000 language,
4001 source_lang,
4002 sfwt_flag,
4003 comments,
4004 created_by,
4005 creation_date,
4006 last_updated_by,
4007 last_update_date,
4008 last_update_login
4009 FROM okc_rule_groups_tlh
4010 WHERE id in (SELECT id
4011 FROM okc_rule_groups_bh
4012 WHERE dnz_chr_id = p_chr_id)
4013 AND major_version = p_major_version;
4014
4015 -----------------------------------------
4016 -- Restoring Base Table
4017 -----------------------------------------
4018
4019 INSERT INTO okc_rule_groups_b
4020 (
4021 id,
4022 rgd_code,
4023 chr_id,
4024 cle_id,
4025 dnz_chr_id,
4026 parent_rgp_id,
4027 sat_code,
4028 object_version_number,
4029 rgp_type,
4030 created_by,
4031 creation_date,
4032 last_updated_by,
4033 last_update_date,
4034 last_update_login,
4035 attribute_category,
4036 attribute1,
4037 attribute2,
4038 attribute3,
4039 attribute4,
4040 attribute5,
4041 attribute6,
4042 attribute7,
4043 attribute8,
4044 attribute9,
4045 attribute10,
4046 attribute11,
4047 attribute12,
4048 attribute13,
4049 attribute14,
4050 attribute15
4051 )
4052 SELECT
4053 id,
4054 rgd_code,
4055 chr_id,
4056 cle_id,
4057 dnz_chr_id,
4058 parent_rgp_id,
4059 sat_code,
4060 object_version_number,
4061 rgp_type,
4062 created_by,
4063 creation_date,
4064 last_updated_by,
4065 last_update_date,
4066 last_update_login,
4067 attribute_category,
4068 attribute1,
4069 attribute2,
4070 attribute3,
4071 attribute4,
4072 attribute5,
4073 attribute6,
4074 attribute7,
4075 attribute8,
4076 attribute9,
4077 attribute10,
4078 attribute11,
4079 attribute12,
4080 attribute13,
4081 attribute14,
4082 attribute15
4083 FROM okc_rule_groups_bh
4084 WHERE dnz_chr_id = p_chr_id
4085 AND major_version = p_major_version;
4086
4087 RETURN l_return_status;
4088 EXCEPTION
4089 -- other appropriate handlers
4090 WHEN OTHERS THEN
4091 -- store SQL error message on message stack
4092 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
4093 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
4094 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
4095 p_token1_value => sqlcode,
4096 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
4097 p_token2_value => sqlerrm);
4098
4099 -- notify UNEXPECTED error
4100 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
4101 return l_return_status;
4102 END restore_version;
4103
4104 END OKC_RGP_PVT;