[Home] [Help]
PACKAGE BODY: APPS.OKC_RGR_PVT
Source
1 PACKAGE BODY OKC_RGR_PVT AS
2 /* $Header: OKCSRGRB.pls 120.0 2005/05/25 23:11:25 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 /*********************** HAND-CODED **************************/
7
8 g_insert_mode boolean := FALSE;
9
10 FUNCTION Validate_Attributes
11 (p_rgrv_rec IN rgrv_rec_type) RETURN VARCHAR2;
12 G_NO_PARENT_RECORD CONSTANT VARCHAR2(200) := 'OKC_NO_PARENT_RECORD';
13 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXPECTED_ERROR';
14 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
15 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
16 G_VIEW CONSTANT VARCHAR2(200) := 'OKC_RG_DEF_RULES_V';
17 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
18 G_RETURN_STATUS VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
19
20 -- Start of comments
21 --
22 -- Procedure Name : validate_uniqueness
23 -- Description :
24 -- Business Rules :
25 -- Parameters :
26 -- Version : 1.0
27 -- End of comments
28
29 PROCEDURE validate_uniqueness(
30 x_return_status OUT NOCOPY VARCHAR2,
31 p_rgrv_rec IN rgrv_rec_type
32 ) IS
33 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
34 -- l_unq_tbl OKC_UTIL.unq_tbl_type;
35
36 -- ------------------------------------------------------
37 -- To check for any matching row, for unique check
38 -- ------------------------------------------------------
39 CURSOR cur_rgr IS
40 SELECT 'x'
41 FROM okc_rg_def_rules
42 WHERE rgd_code = p_rgrv_rec.RGD_CODE
43 AND rdf_code = p_rgrv_rec.RDF_CODE;
44
45 l_row_found BOOLEAN := False;
46 l_dummy VARCHAR2(1);
47
48 BEGIN
49
50 IF (l_debug = 'Y') THEN
51 okc_debug.Set_Indentation('OKC_RGR_PVT');
52 okc_debug.log('100: Entered validate_uniqueness', 2);
53 END IF;
54
55 -- ------------------------------------------------------
56 -- Bug 1636056 related changes - Shyam
57 -- OKC_UTIL.check_comp_unique call earlier was not using
58 -- the bind variables and parses everytime, replaced with
59 -- the explicit cursors above, for identical function.
60 -- ------------------------------------------------------
61 IF ( p_rgrv_rec.RGD_CODE IS NOT NULL
62 AND p_rgrv_rec.RDF_CODE IS NOT NULL
63 AND p_rgrv_rec.RGD_CODE <> OKC_API.G_MISS_CHAR
64 AND p_rgrv_rec.RDF_CODE <> OKC_API.G_MISS_CHAR )
65 THEN
66 OPEN cur_rgr;
67 FETCH cur_rgr INTO l_dummy;
68 l_row_found := cur_rgr%FOUND;
69 CLOSE cur_rgr;
70 END IF;
71
72 IF (l_row_found)
73 THEN
74 -- Display the newly defined error message
75 OKC_API.set_message(G_APP_NAME,
76 'OKC_DUP_RG_DEF_RULES');
77 l_return_status := OKC_API.G_RET_STS_ERROR;
78 END IF;
79
80 -- notify caller of the return status
81 x_return_status := l_return_status;
82
83 IF (l_debug = 'Y') THEN
84 okc_debug.log('200: Leaving validate_uniqueness', 2);
85 okc_debug.Reset_Indentation;
86 END IF;
87
88 EXCEPTION
89 WHEN OTHERS THEN
90
91 IF (l_debug = 'Y') THEN
92 okc_debug.log('300: Exiting validate_uniqueness:OTHERS Exception', 2);
93 okc_debug.Reset_Indentation;
94 END IF;
95
96 -- store SQL error message on message stack
97 OKC_API.SET_MESSAGE(
98 p_app_name => G_APP_NAME,
99 p_msg_name => G_UNEXPECTED_ERROR,
100 p_token1 => G_SQLCODE_TOKEN,
101 p_token1_value => SQLCODE,
102 p_token2 => G_SQLERRM_TOKEN,
103 p_token2_value => SQLERRM);
104
105 -- notify caller of an error as UNEXPETED error
106 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
107
108 END validate_uniqueness;
109
110 --
111 -- Start of comments
112 --
113 -- Procedure Name : validate_rgd_code
114 -- Description :
115 -- Business Rules :
116 -- Parameters :
117 -- Version : 1.0
118 -- End of comments
119
120 PROCEDURE validate_rgd_code(
121 x_return_status OUT NOCOPY VARCHAR2,
122 p_rgrv_rec IN rgrv_rec_type
123 ) IS
124 l_dummy_var VARCHAR2(1) := '?';
125 CURSOR l_rgdv_csr IS
126 SELECT 'x'
127 FROM FND_LOOKUP_VALUES rgdv
128 WHERE rgdv.LOOKUP_CODE = p_rgrv_rec.rgd_code
129 AND rgdv.lookup_type = 'OKC_RULE_GROUP_DEF';
130 BEGIN
131
132 IF (l_debug = 'Y') THEN
133 okc_debug.Set_Indentation('OKC_RGR_PVT');
134 okc_debug.log('400: Entered validate_rgd_code', 2);
135 END IF;
136
137 -- initialize return status
138 x_return_status := OKC_API.G_RET_STS_SUCCESS;
139
140 -- data is required
141 IF (p_rgrv_rec.rgd_code = OKC_API.G_MISS_CHAR OR
142 p_rgrv_rec.rgd_code IS NULL) THEN
143 OKC_API.set_message(
144 p_app_name => G_APP_NAME,
145 p_msg_name => G_REQUIRED_VALUE,
146 p_token1 => G_COL_NAME_TOKEN,
147 p_token1_value => 'rgd_code');
148
149 -- notify caller of an error
150 x_return_status := OKC_API.G_RET_STS_ERROR;
151
152 -- halt validation
153 RAISE G_EXCEPTION_HALT_VALIDATION;
154 END IF;
155
156 -- enforce foreign key
157 OPEN l_rgdv_csr;
158 FETCH l_rgdv_csr INTO l_dummy_var;
159 CLOSE l_rgdv_csr;
160
161 -- if l_dummy_var still set to default, data was not found
162 IF (l_dummy_var = '?') THEN
163 --set error message in message stack
164 OKC_API.set_message(
165 p_app_name => G_APP_NAME,
166 p_msg_name => G_INVALID_VALUE,
167 p_token1 => G_COL_NAME_TOKEN,
168 p_token1_value => 'rgd_code');
169
170 -- notify caller of an error
171 x_return_status := OKC_API.G_RET_STS_ERROR;
172 END IF;
173
174 IF (l_debug = 'Y') THEN
175 okc_debug.log('500: Leaving validate_rgd_code', 2);
176 okc_debug.Reset_Indentation;
177 END IF;
178
179 EXCEPTION
180 WHEN G_EXCEPTION_HALT_VALIDATION THEN
181
182 IF (l_debug = 'Y') THEN
183 okc_debug.log('600: Exiting validate_rgd_code:G_EXCEPTION_HALT_VALIDATION Exception', 2);
184 okc_debug.Reset_Indentation;
185 END IF;
186
187 -- no processing necessary; validation can continue with next column
188 NULL;
189 WHEN OTHERS THEN
190
191 IF (l_debug = 'Y') THEN
192 okc_debug.log('700: Exiting validate_rgd_code:OTHERS Exception', 2);
193 okc_debug.Reset_Indentation;
194 END IF;
195
196 -- store SQL error message on message stack
197 OKC_API.SET_MESSAGE(
198 p_app_name => g_app_name,
199 p_msg_name => g_unexpected_error,
200 p_token1 => g_sqlcode_token,
201 p_token1_value => sqlcode,
202 p_token2 => g_sqlerrm_token,
203 p_token2_value => sqlerrm);
204 -- notify caller of an error as UNEXPETED error
205 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
206 -- verify that cursor was closed
207 IF l_rgdv_csr%ISOPEN THEN
208 CLOSE l_rgdv_csr;
209 END IF;
210
211 END validate_rgd_code;
212 --
213 -- Start of comments
214 --
215 -- Procedure Name : validate_rdf_code
216 -- Description :
217 -- Business Rules :
218 -- Parameters :
219 -- Version : 1.0
220 -- End of comments
221
222 PROCEDURE validate_rdf_code(
223 x_return_status OUT NOCOPY VARCHAR2,
224 p_rgrv_rec IN rgrv_rec_type
225 ) IS
226 l_dummy_var VARCHAR2(1) := '?';
227 /* -- /striping/
228 CURSOR l_rdfv_csr IS
229 SELECT 'x'
230 FROM FND_LOOKUP_VALUES rdfv
231 WHERE rdfv.LOOKUP_CODE = p_rgrv_rec.rdf_code
232 AND rdfv.lookup_type = 'OKC_RULE_DEF';
233 */
234 -- /striping/
235 CURSOR l_rdfv_csr IS
236 SELECT 'x'
237 FROM okc_rule_defs_b rdfv
238 WHERE rdfv.RULE_CODE = p_rgrv_rec.rdf_code;
239 BEGIN
240
241 IF (l_debug = 'Y') THEN
242 okc_debug.Set_Indentation('OKC_RGR_PVT');
243 okc_debug.log('800: Entered validate_rdf_code', 2);
244 END IF;
245
246 -- initialize return status
247 x_return_status := OKC_API.G_RET_STS_SUCCESS;
248
249 -- data is required
250 IF (p_rgrv_rec.rdf_code = OKC_API.G_MISS_CHAR OR
251 p_rgrv_rec.rdf_code IS NULL) THEN
252 OKC_API.set_message(
253 p_app_name => G_APP_NAME,
254 p_msg_name => G_REQUIRED_VALUE,
255 p_token1 => G_COL_NAME_TOKEN,
256 p_token1_value => 'rdf_code');
257
258 -- notify caller of an error
259 x_return_status := OKC_API.G_RET_STS_ERROR;
260
261 -- halt validation
262 RAISE G_EXCEPTION_HALT_VALIDATION;
263 END IF;
264
265 -- enforce foreign key
266 OPEN l_rdfv_csr;
267 FETCH l_rdfv_csr INTO l_dummy_var;
268 CLOSE l_rdfv_csr;
269
270 -- if l_dummy_var still set to default, data was not found
271 IF (l_dummy_var = '?') THEN
272 --set error message in message stack
273 OKC_API.set_message(
274 p_app_name => G_APP_NAME,
275 p_msg_name => G_INVALID_VALUE,
276 p_token1 => G_COL_NAME_TOKEN,
277 p_token1_value => 'rdf_code');
278
279 -- notify caller of an error
280 x_return_status := OKC_API.G_RET_STS_ERROR;
281 END IF;
282
283 IF (l_debug = 'Y') THEN
284 okc_debug.log('900: Leaving validate_rdf_code', 2);
285 okc_debug.Reset_Indentation;
286 END IF;
287
288 EXCEPTION
289 WHEN G_EXCEPTION_HALT_VALIDATION THEN
290
291 IF (l_debug = 'Y') THEN
292 okc_debug.log('1000: Exiting validate_rdf_code:G_EXCEPTION_HALT_VALIDATION Exception', 2);
293 okc_debug.Reset_Indentation;
294 END IF;
295
296 -- no processing necessary; validation can continue with next column
297 NULL;
298 WHEN OTHERS THEN
299
300 IF (l_debug = 'Y') THEN
301 okc_debug.log('1100: Exiting validate_rdf_code:OTHERS Exception', 2);
302 okc_debug.Reset_Indentation;
303 END IF;
304
305 -- store SQL error message on message stack
306 OKC_API.SET_MESSAGE(
307 p_app_name => g_app_name,
308 p_msg_name => g_unexpected_error,
309 p_token1 => g_sqlcode_token,
310 p_token1_value => sqlcode,
311 p_token2 => g_sqlerrm_token,
312 p_token2_value => sqlerrm);
313 -- notify caller of an error as UNEXPETED error
314 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
315 -- verify that cursor was closed
316 IF l_rdfv_csr%ISOPEN THEN
317 CLOSE l_rdfv_csr;
318 END IF;
319
320 END validate_rdf_code;
321 --
322 -- Start of comments
323 --
324 -- Procedure Name : validate_optional_yn
325 -- Description :
326 -- Business Rules :
327 -- Parameters :
328 -- Version : 1.0
329 -- End of comments
330
331 PROCEDURE validate_optional_yn(
332 x_return_status OUT NOCOPY VARCHAR2,
333 p_rgrv_rec IN rgrv_rec_type
334 ) IS
335 BEGIN
336
337 IF (l_debug = 'Y') THEN
338 okc_debug.Set_Indentation('OKC_RGR_PVT');
339 okc_debug.log('1200: Entered validate_optional_yn', 2);
340 END IF;
341
342 -- initialize return status
343 x_return_status := OKC_API.G_RET_STS_SUCCESS;
344
345 -- check for data
346 IF (p_rgrv_rec.optional_yn <> OKC_API.G_MISS_CHAR OR
347 p_rgrv_rec.optional_yn IS NOT NULL) THEN
348
349 -- check allowed values
350 IF (UPPER(p_rgrv_rec.optional_yn) NOT IN ('Y','N')) THEN
351 OKC_API.set_message(
352 p_app_name => G_APP_NAME,
353 p_msg_name => G_INVALID_VALUE,
354 p_token1 => G_COL_NAME_TOKEN,
355 p_token1_value => 'optional_yn');
356
357 -- notify caller of an error
358 x_return_status := OKC_API.G_RET_STS_ERROR;
359 END IF;
360 END IF;
361
362 IF (l_debug = 'Y') THEN
363 okc_debug.log('1300: Leaving validate_optional_yn', 2);
364 okc_debug.Reset_Indentation;
365 END IF;
366
367 EXCEPTION
368 WHEN G_EXCEPTION_HALT_VALIDATION THEN
369
370 IF (l_debug = 'Y') THEN
371 okc_debug.log('1400: Exiting validate_optional_yn:G_EXCEPTION_HALT_VALIDATION Exception', 2);
372 okc_debug.Reset_Indentation;
373 END IF;
374
375 -- no processing necessary; validation can continue with next column
376 NULL;
377 WHEN OTHERS THEN
378
379 IF (l_debug = 'Y') THEN
380 okc_debug.log('1500: Exiting validate_optional_yn:OTHERS Exception', 2);
381 okc_debug.Reset_Indentation;
382 END IF;
383
384 -- store SQL error message on message stack
385 OKC_API.SET_MESSAGE(
386 p_app_name => G_APP_NAME,
387 p_msg_name => G_UNEXPECTED_ERROR,
388 p_token1 => G_SQLCODE_TOKEN,
389 p_token1_value => SQLCODE,
390 p_token2 => G_SQLERRM_TOKEN,
391 p_token2_value => SQLERRM);
392 -- notify caller of an error as UNEXPETED error
393 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
394 -- verify that cursor was closed
395
396 END validate_optional_yn;
397 --
398 PROCEDURE validate_min_cardinality(
399 x_return_status OUT NOCOPY VARCHAR2,
400 p_rgrv_rec IN rgrv_rec_type
401 ) IS
402 /*
403 Validations :
404 1. If OPTIONAL_YN = 'N' then MIN_CARDINALITY > 0
405 2. If OPTIONAL_YN = 'Y' then MIN_CARDINALITY = 0
406 3. MIN_CARDINALITY <= MAX_CARDINALITY
407 */
408
409 BEGIN
410
411 IF (l_debug = 'Y') THEN
412 okc_debug.Set_Indentation('OKC_RGR_PVT');
413 okc_debug.log('1600: Entered validate_min_cardinality', 2);
414 END IF;
415
416 -- initialize return status
417 x_return_status := OKC_API.G_RET_STS_SUCCESS;
418
419 -- min_cardinality > 0 for optional_yn = N
420 IF ( NVL(p_rgrv_rec.optional_yn,'Y') = 'N' AND
421 p_rgrv_rec.min_cardinality IS NOT NULL ) THEN
422
423 IF p_rgrv_rec.min_cardinality <= 0 THEN
424 OKC_API.set_message(
425 p_app_name => G_APP_NAME,
426 p_msg_name => G_INVALID_VALUE,
427 p_token1 => G_COL_NAME_TOKEN,
428 p_token1_value => 'Minimum');
429
430 -- notify caller of an error
431 x_return_status := OKC_API.G_RET_STS_ERROR;
432
433 -- halt validation
434 RAISE G_EXCEPTION_HALT_VALIDATION;
435
436 END IF; -- error message
437
438 END IF; -- case 1
439
440 -- min_cardinality = 0 for optional_yn = Y
441 IF ( NVL(p_rgrv_rec.optional_yn,'Y') = 'Y' AND
442 p_rgrv_rec.min_cardinality IS NOT NULL ) THEN
443
444 IF p_rgrv_rec.min_cardinality <> 0 THEN
445 OKC_API.set_message(
446 p_app_name => G_APP_NAME,
447 p_msg_name => G_INVALID_VALUE,
448 p_token1 => G_COL_NAME_TOKEN,
449 p_token1_value => 'Minimum');
450
451 -- notify caller of an error
452 x_return_status := OKC_API.G_RET_STS_ERROR;
453
454 -- halt validation
455 RAISE G_EXCEPTION_HALT_VALIDATION;
456
457 END IF; -- error message
458
459 END IF; -- case 2
460
461
462 -- min_cardinality <= max_cardinality
463 IF ( p_rgrv_rec.min_cardinality IS NOT NULL AND
464 p_rgrv_rec.max_cardinality IS NOT NULL ) THEN
465
466 IF p_rgrv_rec.min_cardinality > p_rgrv_rec.max_cardinality THEN
467 OKC_API.set_message(
468 p_app_name => G_APP_NAME,
469 p_msg_name => G_INVALID_VALUE,
470 p_token1 => G_COL_NAME_TOKEN,
471 p_token1_value => 'Minimum');
472
473 -- notify caller of an error
474 x_return_status := OKC_API.G_RET_STS_ERROR;
475
476 -- halt validation
477 RAISE G_EXCEPTION_HALT_VALIDATION;
478
479 END IF; -- error message
480
481 END IF; -- case 3
482
483 IF (l_debug = 'Y') THEN
484 okc_debug.log('1700: Leaving validate_min_cardinality', 2);
485 okc_debug.Reset_Indentation;
486 END IF;
487
488 EXCEPTION
489 WHEN G_EXCEPTION_HALT_VALIDATION THEN
490
491 IF (l_debug = 'Y') THEN
492 okc_debug.log('1800: Exiting validate_min_cardinality:G_EXCEPTION_HALT_VALIDATION Exception', 2);
493 okc_debug.Reset_Indentation;
494 END IF;
495
496 -- no processing necessary; validation can continue with next column
497 NULL;
498 WHEN OTHERS THEN
499
500 IF (l_debug = 'Y') THEN
501 okc_debug.log('1900: Exiting validate_min_cardinality:OTHERS Exception', 2);
502 okc_debug.Reset_Indentation;
503 END IF;
504
505 -- store SQL error message on message stack
506 OKC_API.SET_MESSAGE(
507 p_app_name => g_app_name,
508 p_msg_name => g_unexpected_error,
509 p_token1 => g_sqlcode_token,
510 p_token1_value => sqlcode,
511 p_token2 => g_sqlerrm_token,
512 p_token2_value => sqlerrm);
513 -- notify caller of an error as UNEXPETED error
514 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
515
516
517 END validate_min_cardinality;
518 --
519 -- Start of comments
520 --
521 -- Procedure Name : validate_pricing_related_yn
522 -- Description :
523 -- Business Rules :
524 -- Parameters :
525 -- Version : 1.0
526 -- End of comments
527
528 PROCEDURE validate_pricing_related_yn(
529 x_return_status OUT NOCOPY VARCHAR2,
530 p_rgrv_rec IN rgrv_rec_type
531 ) IS
532 BEGIN
533
534 IF (l_debug = 'Y') THEN
535 okc_debug.Set_Indentation('OKC_RGR_PVT');
536 okc_debug.log('2000: Entered validate_pricing_related_yn', 2);
537 END IF;
538
539 -- initialize return status
540 x_return_status := OKC_API.G_RET_STS_SUCCESS;
541
542 -- check for data
543 IF (p_rgrv_rec.pricing_related_yn <> OKC_API.G_MISS_CHAR OR
544 p_rgrv_rec.pricing_related_yn IS NOT NULL) THEN
545
546 -- check allowed values
547 IF (UPPER(p_rgrv_rec.pricing_related_yn) NOT IN ('Y','N')) THEN
548 OKC_API.set_message(
549 p_app_name => G_APP_NAME,
550 p_msg_name => G_INVALID_VALUE,
551 p_token1 => G_COL_NAME_TOKEN,
552 p_token1_value => 'pricing_related_yn');
553
554 -- notify caller of an error
555 x_return_status := OKC_API.G_RET_STS_ERROR;
556 END IF;
557 END IF;
558
559 IF (l_debug = 'Y') THEN
560 okc_debug.log('2100: Leaving validate_pricing_related_yn', 2);
561 okc_debug.Reset_Indentation;
562 END IF;
563
564 EXCEPTION
565 WHEN G_EXCEPTION_HALT_VALIDATION THEN
566
567 IF (l_debug = 'Y') THEN
568 okc_debug.log('2200: Exiting validate_pricing_related_yn:G_EXCEPTION_HALT_VALIDATION Exception', 2);
569 okc_debug.Reset_Indentation;
570 END IF;
571
572 -- no processing necessary; validation can continue with next column
573 NULL;
574 WHEN OTHERS THEN
575
576 IF (l_debug = 'Y') THEN
577 okc_debug.log('2300: Exiting validate_pricing_related_yn:OTHERS Exception', 2);
578 okc_debug.Reset_Indentation;
579 END IF;
580
581 -- store SQL error message on message stack
582 OKC_API.SET_MESSAGE(
583 p_app_name => G_APP_NAME,
584 p_msg_name => G_UNEXPECTED_ERROR,
585 p_token1 => G_SQLCODE_TOKEN,
586 p_token1_value => SQLCODE,
587 p_token2 => G_SQLERRM_TOKEN,
588 p_token2_value => SQLERRM);
589 -- notify caller of an error as UNEXPETED error
590 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
591 -- verify that cursor was closed
592
593 END validate_pricing_related_yn;
594 --
595
596 --
597 -- Start of comments
598 --
599 -- Procedure Name : validate_access_level
600 -- Description :
601 -- Business Rules :
602 -- Parameters :
603 -- Version : 1.0
604 -- End of comments
605
606 PROCEDURE validate_access_level(
607 x_return_status OUT NOCOPY VARCHAR2,
608 p_rgrv_rec IN rgrv_rec_type
609 ) IS
610
611 l_dummy_var VARCHAR2(1) := '?';
612
613 CURSOR l_rgdv_csr IS
614 SELECT 'x'
615 FROM FND_LOOKUP_VALUES rgdv
616 WHERE rgdv.LOOKUP_CODE = p_rgrv_rec.access_level
617 AND rgdv.lookup_type = 'OKC_SEED_ACCESS_LEVEL_SU';
618
619 BEGIN
620
621 IF (l_debug = 'Y') THEN
622 okc_debug.Set_Indentation('OKC_RGR_PVT');
623 okc_debug.log('2400: Entered validate_access_level', 2);
624 END IF;
625
626 -- initialize return status
627 x_return_status := OKC_API.G_RET_STS_SUCCESS;
628
629 -- check for data
630 IF (p_rgrv_rec.access_level <> OKC_API.G_MISS_CHAR OR
631 p_rgrv_rec.access_level IS NOT NULL) THEN
632
633 -- enforce foreign key
634 OPEN l_rgdv_csr;
635 FETCH l_rgdv_csr INTO l_dummy_var;
636 CLOSE l_rgdv_csr;
637
638 -- if l_dummy_var still set to default, data was not found
639 IF (l_dummy_var = '?') THEN
640 --set error message in message stack
641 OKC_API.set_message(
642 p_app_name => G_APP_NAME,
643 p_msg_name => G_INVALID_VALUE,
644 p_token1 => G_COL_NAME_TOKEN,
645 p_token1_value => 'access_level');
646
647
648 -- notify caller of an error
649 x_return_status := OKC_API.G_RET_STS_ERROR;
650 END IF;
651 END IF;
652
653 IF (l_debug = 'Y') THEN
654 okc_debug.log('2500: Leaving validate_access_level', 2);
655 okc_debug.Reset_Indentation;
656 END IF;
657
658 EXCEPTION
659 WHEN G_EXCEPTION_HALT_VALIDATION THEN
660
661 IF (l_debug = 'Y') THEN
662 okc_debug.log('2600: Exiting validate_access_level:G_EXCEPTION_HALT_VALIDATION Exception', 2);
663 okc_debug.Reset_Indentation;
664 END IF;
665
666 -- no processing necessary; validation can continue with next column
667 NULL;
668 WHEN OTHERS THEN
669
670 IF (l_debug = 'Y') THEN
671 okc_debug.log('2700: Exiting validate_access_level:OTHERS Exception', 2);
672 okc_debug.Reset_Indentation;
673 END IF;
674
675 -- store SQL error message on message stack
676 OKC_API.SET_MESSAGE(
677 p_app_name => G_APP_NAME,
678 p_msg_name => G_UNEXPECTED_ERROR,
679 p_token1 => G_SQLCODE_TOKEN,
680 p_token1_value => SQLCODE,
681 p_token2 => G_SQLERRM_TOKEN,
682 p_token2_value => SQLERRM);
683 -- notify caller of an error as UNEXPETED error
684 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
685 -- verify that cursor was closed
686
687 END validate_access_level;
688 --
689
690 --
691 -- Start of comments
692 --
693 -- Procedure Name : validate_attributes
694 -- Description :
695 -- Business Rules :
696 -- Parameters :
697 -- Version : 1.0
698 -- End of comments
699
700 FUNCTION Validate_Attributes (
701 p_rgrv_rec IN rgrv_rec_type
702 ) RETURN VARCHAR2 IS
703 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
704 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
705 BEGIN
706
707 IF (l_debug = 'Y') THEN
708 okc_debug.Set_Indentation('OKC_RGR_PVT');
709 okc_debug.log('2800: Entered Validate_Attributes', 2);
710 END IF;
711
712 -- call each column-level validation
713
714 validate_rgd_code(
715 x_return_status => l_return_status,
716 p_rgrv_rec => p_rgrv_rec);
717
718 -- store the highest degree of error
719 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
720 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
721 x_return_status := l_return_status;
722 END IF;
723 END IF;
724 --
725 validate_rdf_code(
726 x_return_status => l_return_status,
727 p_rgrv_rec => p_rgrv_rec);
728
729 -- store the highest degree of error
730 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
731 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
732 x_return_status := l_return_status;
733 END IF;
734 END IF;
735 --
736 validate_optional_yn(
737 x_return_status => l_return_status,
738 p_rgrv_rec => p_rgrv_rec);
739
740 -- store the highest degree of error
741 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
742 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
743 x_return_status := l_return_status;
744 END IF;
745 END IF;
746 --
747 validate_min_cardinality
748 (
749 x_return_status => l_return_status,
750 p_rgrv_rec => p_rgrv_rec
751 );
752
753 -- store the highest degree of error
754 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
755 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
756 x_return_status := l_return_status;
757 END IF;
758 END IF;
759 --
760 validate_pricing_related_yn(
761 x_return_status => l_return_status,
762 p_rgrv_rec => p_rgrv_rec);
763
764 -- store the highest degree of error
765 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
766 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
767 x_return_status := l_return_status;
768 END IF;
769 END IF;
770 --
771 validate_access_level(
772 x_return_status => l_return_status,
773 p_rgrv_rec => p_rgrv_rec);
774
775 -- store the highest degree of error
776 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
777 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
778 x_return_status := l_return_status;
779 END IF;
780 END IF;
781 --
782 -- Only call validate uniqueness when inserting a record
783 -- calling when updating a record will cause an error.
784 IF g_insert_mode THEN
785 validate_uniqueness(
786 x_return_status => l_return_status,
787 p_rgrv_rec => p_rgrv_rec);
788
789 -- store the highest degree of error
790 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
791 IF (x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR) THEN
792 x_return_status := l_return_status;
793 END IF;
794 END IF;
795 END IF;
796 --
797 IF (l_debug = 'Y') THEN
798 okc_debug.log('2900: Leaving Validate_Attributes', 2);
799 okc_debug.Reset_Indentation;
800 END IF;
801
802 -- return status to caller
803 RETURN(x_return_status);
804
805 EXCEPTION
806 WHEN OTHERS THEN
807
808 IF (l_debug = 'Y') THEN
809 okc_debug.log('3000: Exiting Validate_Attributes:OTHERS Exception', 2);
810 okc_debug.Reset_Indentation;
811 END IF;
812
813 -- store SQL error message on message stack for caller
814 OKC_API.SET_MESSAGE
815 (p_app_name => G_APP_NAME,
816 p_msg_name => G_UNEXPECTED_ERROR,
817 p_token1 => G_SQLCODE_TOKEN,
818 p_token1_value => SQLCODE,
819 p_token2 => G_SQLERRM_TOKEN,
820 p_token2_value => SQLERRM);
821
822 -- notify caller of an UNEXPECTED error
823 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
824
825 -- return status to caller
826 RETURN x_return_status;
827
828 END Validate_Attributes;
829
830 /*********************** END HAND-CODED **************************/
831
832 ---------------------------------------------------------------------------
833 -- FUNCTION get_seq_id
834 ---------------------------------------------------------------------------
835 FUNCTION get_seq_id RETURN NUMBER IS
836 BEGIN
837
838 RETURN(okc_p_util.raw_to_number(sys_guid()));
839
840 END get_seq_id;
841
842 ---------------------------------------------------------------------------
843 -- PROCEDURE qc
844 ---------------------------------------------------------------------------
845 PROCEDURE qc IS
846 BEGIN
847
848 null;
849
850 END qc;
851
852 ---------------------------------------------------------------------------
853 -- PROCEDURE change_version
854 ---------------------------------------------------------------------------
855 PROCEDURE change_version IS
856 BEGIN
857
858 null;
859
860 END change_version;
861
862 ---------------------------------------------------------------------------
863 -- PROCEDURE api_copy
864 ---------------------------------------------------------------------------
865 PROCEDURE api_copy IS
866 BEGIN
867
868 null;
869
870 END api_copy;
871
872 ---------------------------------------------------------------------------
873 -- FUNCTION get_rec for: OKC_RG_DEF_RULES
874 ---------------------------------------------------------------------------
875 FUNCTION get_rec (
876 p_rgr_rec IN rgr_rec_type,
877 x_no_data_found OUT NOCOPY BOOLEAN
878 ) RETURN rgr_rec_type IS
879 CURSOR rgr_pk_csr (p_rgd_code IN VARCHAR2,
880 p_rdf_code IN VARCHAR2) IS
881 SELECT
882 RGD_CODE,
883 RDF_CODE,
884 OBJECT_VERSION_NUMBER,
885 CREATED_BY,
886 CREATION_DATE,
887 LAST_UPDATED_BY,
888 LAST_UPDATE_DATE,
889 OPTIONAL_YN,
890 LAST_UPDATE_LOGIN,
891 MIN_CARDINALITY,
892 MAX_CARDINALITY,
893 PRICING_RELATED_YN,
894 ACCESS_LEVEL
895 FROM Okc_Rg_Def_Rules
896 WHERE okc_rg_def_rules.rgd_code = p_rgd_code
897 AND okc_rg_def_rules.rdf_code = p_rdf_code;
898 l_rgr_pk rgr_pk_csr%ROWTYPE;
899 l_rgr_rec rgr_rec_type;
900 BEGIN
901
902 IF (l_debug = 'Y') THEN
903 okc_debug.Set_Indentation('OKC_RGR_PVT');
904 okc_debug.log('3500: Entered get_rec', 2);
905 END IF;
906
907 x_no_data_found := TRUE;
908 -- Get current database values
909 OPEN rgr_pk_csr (p_rgr_rec.rgd_code,
910 p_rgr_rec.rdf_code);
911 FETCH rgr_pk_csr INTO
912 l_rgr_rec.RGD_CODE,
913 l_rgr_rec.RDF_CODE,
914 l_rgr_rec.OBJECT_VERSION_NUMBER,
915 l_rgr_rec.CREATED_BY,
916 l_rgr_rec.CREATION_DATE,
917 l_rgr_rec.LAST_UPDATED_BY,
918 l_rgr_rec.LAST_UPDATE_DATE,
919 l_rgr_rec.OPTIONAL_YN,
920 l_rgr_rec.LAST_UPDATE_LOGIN,
921 l_rgr_rec.MIN_CARDINALITY,
922 l_rgr_rec.MAX_CARDINALITY,
923 l_rgr_rec.PRICING_RELATED_YN,
924 l_rgr_rec.ACCESS_LEVEL;
925 x_no_data_found := rgr_pk_csr%NOTFOUND;
926 CLOSE rgr_pk_csr;
927
928 IF (l_debug = 'Y') THEN
929 okc_debug.log('2900: Leaving Validate_Attributes', 2);
930 okc_debug.Reset_Indentation;
931 END IF;
932
933 RETURN(l_rgr_rec);
934
935 END get_rec;
936
937 FUNCTION get_rec (
938 p_rgr_rec IN rgr_rec_type
939 ) RETURN rgr_rec_type IS
940 l_row_notfound BOOLEAN := TRUE;
941 BEGIN
942
943 RETURN(get_rec(p_rgr_rec, l_row_notfound));
944
945 END get_rec;
946 ---------------------------------------------------------------------------
947 -- FUNCTION get_rec for: OKC_RG_DEF_RULES_V
948 ---------------------------------------------------------------------------
949 FUNCTION get_rec (
950 p_rgrv_rec IN rgrv_rec_type,
951 x_no_data_found OUT NOCOPY BOOLEAN
952 ) RETURN rgrv_rec_type IS
953 CURSOR okc_rgrv_pk_csr (p_rgd_code IN VARCHAR2,
954 p_rdf_code IN VARCHAR2) IS
955 SELECT
956 RGD_CODE,
957 RDF_CODE,
958 OBJECT_VERSION_NUMBER,
959 OPTIONAL_YN,
960 CREATED_BY,
961 CREATION_DATE,
962 LAST_UPDATED_BY,
963 LAST_UPDATE_DATE,
964 LAST_UPDATE_LOGIN,
965 MIN_CARDINALITY,
966 MAX_CARDINALITY,
967 PRICING_RELATED_YN,
968 ACCESS_LEVEL
969 FROM Okc_Rg_Def_Rules_V
970 WHERE okc_rg_def_rules_v.rgd_code = p_rgd_code
971 AND okc_rg_def_rules_v.rdf_code = p_rdf_code;
972 l_okc_rgrv_pk okc_rgrv_pk_csr%ROWTYPE;
973 l_rgrv_rec rgrv_rec_type;
974 BEGIN
975
976 IF (l_debug = 'Y') THEN
977 okc_debug.Set_Indentation('OKC_RGR_PVT');
978 okc_debug.log('3700: Entered get_rec', 2);
979 END IF;
980
981 x_no_data_found := TRUE;
982 -- Get current database values
983 OPEN okc_rgrv_pk_csr (p_rgrv_rec.rgd_code,
984 p_rgrv_rec.rdf_code);
985 FETCH okc_rgrv_pk_csr INTO
986 l_rgrv_rec.RGD_CODE,
987 l_rgrv_rec.RDF_CODE,
988 l_rgrv_rec.OBJECT_VERSION_NUMBER,
989 l_rgrv_rec.OPTIONAL_YN,
990 l_rgrv_rec.CREATED_BY,
991 l_rgrv_rec.CREATION_DATE,
992 l_rgrv_rec.LAST_UPDATED_BY,
993 l_rgrv_rec.LAST_UPDATE_DATE,
994 l_rgrv_rec.LAST_UPDATE_LOGIN,
995 l_rgrv_rec.MIN_CARDINALITY,
996 l_rgrv_rec.MAX_CARDINALITY,
997 l_rgrv_rec.PRICING_RELATED_YN,
998 l_rgrv_rec.ACCESS_LEVEL;
999 x_no_data_found := okc_rgrv_pk_csr%NOTFOUND;
1000 CLOSE okc_rgrv_pk_csr;
1001
1002 IF (l_debug = 'Y') THEN
1003 okc_debug.log('900: Leaving Fn Get_Rec ', 2);
1004 okc_debug.Reset_Indentation;
1005 END IF;
1006
1007 RETURN(l_rgrv_rec);
1008
1009 END get_rec;
1010
1011 FUNCTION get_rec (
1012 p_rgrv_rec IN rgrv_rec_type
1013 ) RETURN rgrv_rec_type IS
1014 l_row_notfound BOOLEAN := TRUE;
1015 BEGIN
1016
1017 RETURN(get_rec(p_rgrv_rec, l_row_notfound));
1018
1019 END get_rec;
1020
1021 --------------------------------------------------------
1022 -- FUNCTION null_out_defaults for: OKC_RG_DEF_RULES_V --
1023 --------------------------------------------------------
1024 FUNCTION null_out_defaults (
1025 p_rgrv_rec IN rgrv_rec_type
1026 ) RETURN rgrv_rec_type IS
1027 l_rgrv_rec rgrv_rec_type := p_rgrv_rec;
1028 BEGIN
1029
1030 IF (l_debug = 'Y') THEN
1031 okc_debug.Set_Indentation('OKC_RGR_PVT');
1032 okc_debug.log('3900: Entered null_out_defaults', 2);
1033 END IF;
1034
1035 IF (l_rgrv_rec.rgd_code = OKC_API.G_MISS_CHAR) THEN
1036 l_rgrv_rec.rgd_code := NULL;
1037 END IF;
1038 IF (l_rgrv_rec.rdf_code = OKC_API.G_MISS_CHAR) THEN
1039 l_rgrv_rec.rdf_code := NULL;
1040 END IF;
1041 IF (l_rgrv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
1042 l_rgrv_rec.object_version_number := NULL;
1043 END IF;
1044 IF (l_rgrv_rec.optional_yn = OKC_API.G_MISS_CHAR) THEN
1045 l_rgrv_rec.optional_yn := NULL;
1046 END IF;
1047 IF (l_rgrv_rec.created_by = OKC_API.G_MISS_NUM) THEN
1048 l_rgrv_rec.created_by := NULL;
1049 END IF;
1050 IF (l_rgrv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
1051 l_rgrv_rec.creation_date := NULL;
1052 END IF;
1053 IF (l_rgrv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
1054 l_rgrv_rec.last_updated_by := NULL;
1055 END IF;
1056 IF (l_rgrv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
1057 l_rgrv_rec.last_update_date := NULL;
1058 END IF;
1059 IF (l_rgrv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
1060 l_rgrv_rec.last_update_login := NULL;
1061 END IF;
1062 IF (l_rgrv_rec.min_cardinality = OKC_API.G_MISS_NUM) THEN
1063 l_rgrv_rec.min_cardinality := NULL;
1064 END IF;
1065 IF (l_rgrv_rec.max_cardinality = OKC_API.G_MISS_NUM) THEN
1066 l_rgrv_rec.max_cardinality := NULL;
1067 END IF;
1068 IF (l_rgrv_rec.pricing_related_yn = OKC_API.G_MISS_CHAR) THEN
1069 l_rgrv_rec.pricing_related_yn := NULL;
1070 END IF;
1071 IF (l_rgrv_rec.access_level = OKC_API.G_MISS_CHAR) THEN
1072 l_rgrv_rec.access_level := NULL;
1073 END IF;
1074
1075 IF (l_debug = 'Y') THEN
1076 okc_debug.log('500: Leaving null_out_defaults ', 2);
1077 okc_debug.Reset_Indentation;
1078 END IF;
1079
1080 RETURN(l_rgrv_rec);
1081
1082 END null_out_defaults;
1083 ---------------------------------------------------------------------------
1084 -- PROCEDURE Validate_Attributes
1085 ---------------------------------------------------------------------------
1086 ------------------------------------------------
1087 -- Validate_Attributes for:OKC_RG_DEF_RULES_V --
1088 ------------------------------------------------
1089 /* commenting out nocopy generated code in favor of hand-coded procedure
1090 FUNCTION Validate_Attributes (
1091 p_rgrv_rec IN rgrv_rec_type
1092 ) RETURN VARCHAR2 IS
1093 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1094 BEGIN
1095
1096 IF (l_debug = 'Y') THEN
1097 okc_debug.Set_Indentation('OKC_RGR_PVT');
1098 okc_debug.log('4000: Entered Validate_Attributes', 2);
1099 END IF;
1100
1101 IF p_rgrv_rec.rgd_code = OKC_API.G_MISS_CHAR OR
1102 p_rgrv_rec.rgd_code IS NULL
1103 THEN
1104 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'rgd_code');
1105 l_return_status := OKC_API.G_RET_STS_ERROR;
1106 ELSIF p_rgrv_rec.rdf_code = OKC_API.G_MISS_CHAR OR
1107 p_rgrv_rec.rdf_code IS NULL
1108 THEN
1109 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'rdf_code');
1110 l_return_status := OKC_API.G_RET_STS_ERROR;
1111 ELSIF p_rgrv_rec.object_version_number = OKC_API.G_MISS_NUM OR
1112 p_rgrv_rec.object_version_number IS NULL
1113 THEN
1114 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
1115 l_return_status := OKC_API.G_RET_STS_ERROR;
1116 END IF;
1117 RETURN(l_return_status);
1118
1119 END Validate_Attributes;
1120 */
1121 ---------------------------------------------------------------------------
1122 -- PROCEDURE Validate_Record
1123 ---------------------------------------------------------------------------
1124 --------------------------------------------
1125 -- Validate_Record for:OKC_RG_DEF_RULES_V --
1126 --------------------------------------------
1127 FUNCTION Validate_Record (
1128 p_rgrv_rec IN rgrv_rec_type
1129 ) RETURN VARCHAR2 IS
1130 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1131 BEGIN
1132
1133 RETURN (l_return_status);
1134
1135 END Validate_Record;
1136
1137 ---------------------------------------------------------------------------
1138 -- PROCEDURE Migrate
1139 ---------------------------------------------------------------------------
1140 PROCEDURE migrate (
1141 p_from IN rgrv_rec_type,
1142 p_to IN OUT NOCOPY rgr_rec_type
1143 ) IS
1144 BEGIN
1145
1146 p_to.rgd_code := p_from.rgd_code;
1147 p_to.rdf_code := p_from.rdf_code;
1148 p_to.object_version_number := p_from.object_version_number;
1149 p_to.created_by := p_from.created_by;
1150 p_to.creation_date := p_from.creation_date;
1151 p_to.last_updated_by := p_from.last_updated_by;
1152 p_to.last_update_date := p_from.last_update_date;
1153 p_to.optional_yn := p_from.optional_yn;
1154 p_to.last_update_login := p_from.last_update_login;
1155 p_to.min_cardinality := p_from.min_cardinality;
1156 p_to.max_cardinality := p_from.max_cardinality;
1157 p_to.pricing_related_yn := p_from.pricing_related_yn;
1158 p_to.access_level := p_from.access_level;
1159
1160 END migrate;
1161 PROCEDURE migrate (
1162 p_from IN rgr_rec_type,
1163 p_to IN OUT NOCOPY rgrv_rec_type
1164 ) IS
1165 BEGIN
1166
1167 p_to.rgd_code := p_from.rgd_code;
1168 p_to.rdf_code := p_from.rdf_code;
1169 p_to.object_version_number := p_from.object_version_number;
1170 p_to.created_by := p_from.created_by;
1171 p_to.creation_date := p_from.creation_date;
1172 p_to.last_updated_by := p_from.last_updated_by;
1173 p_to.last_update_date := p_from.last_update_date;
1174 p_to.optional_yn := p_from.optional_yn;
1175 p_to.last_update_login := p_from.last_update_login;
1176 p_to.min_cardinality := p_from.min_cardinality;
1177 p_to.max_cardinality := p_from.max_cardinality;
1178 p_to.pricing_related_yn := p_from.pricing_related_yn;
1179 p_to.access_level := p_from.access_level;
1180
1181 END migrate;
1182
1183 ---------------------------------------------------------------------------
1184 -- PROCEDURE validate_row
1185 ---------------------------------------------------------------------------
1186 -----------------------------------------
1187 -- validate_row for:OKC_RG_DEF_RULES_V --
1188 -----------------------------------------
1189 PROCEDURE validate_row(
1190 p_api_version IN NUMBER,
1191 p_init_msg_list IN VARCHAR2,
1192 x_return_status OUT NOCOPY VARCHAR2,
1193 x_msg_count OUT NOCOPY NUMBER,
1194 x_msg_data OUT NOCOPY VARCHAR2,
1195 p_rgrv_rec IN rgrv_rec_type) IS
1196
1197 l_api_version CONSTANT NUMBER := 1;
1198 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1199 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1200 l_rgrv_rec rgrv_rec_type := p_rgrv_rec;
1201 l_rgr_rec rgr_rec_type;
1202 BEGIN
1203
1204 IF (l_debug = 'Y') THEN
1205 okc_debug.Set_Indentation('OKC_RGR_PVT');
1206 okc_debug.log('4400: Entered validate_row', 2);
1207 END IF;
1208
1209 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1210 G_PKG_NAME,
1211 p_init_msg_list,
1212 l_api_version,
1213 p_api_version,
1214 '_PVT',
1215 x_return_status);
1216 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1217 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1218 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1219 RAISE OKC_API.G_EXCEPTION_ERROR;
1220 END IF;
1221 --- Validate all non-missing attributes (Item Level Validation)
1222 l_return_status := Validate_Attributes(l_rgrv_rec);
1223 --- If any errors happen abort API
1224 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1225 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1226 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1227 RAISE OKC_API.G_EXCEPTION_ERROR;
1228 END IF;
1229 l_return_status := Validate_Record(l_rgrv_rec);
1230 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1231 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1232 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1233 RAISE OKC_API.G_EXCEPTION_ERROR;
1234 END IF;
1235 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1236
1237 IF (l_debug = 'Y') THEN
1238 okc_debug.log('4500: Leaving validate_row', 2);
1239 okc_debug.Reset_Indentation;
1240 END IF;
1241
1242 EXCEPTION
1243 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1244
1245 IF (l_debug = 'Y') THEN
1246 okc_debug.log('4600: Exiting validate_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1247 okc_debug.Reset_Indentation;
1248 END IF;
1249
1250 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1251 (
1252 l_api_name,
1253 G_PKG_NAME,
1254 'OKC_API.G_RET_STS_ERROR',
1255 x_msg_count,
1256 x_msg_data,
1257 '_PVT'
1258 );
1259 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1260
1261 IF (l_debug = 'Y') THEN
1262 okc_debug.log('4700: Exiting validate_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1263 okc_debug.Reset_Indentation;
1264 END IF;
1265
1266 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1267 (
1268 l_api_name,
1269 G_PKG_NAME,
1270 'OKC_API.G_RET_STS_UNEXP_ERROR',
1271 x_msg_count,
1272 x_msg_data,
1273 '_PVT'
1274 );
1275 WHEN OTHERS THEN
1276
1277 IF (l_debug = 'Y') THEN
1278 okc_debug.log('4800: Exiting validate_row:OTHERS Exception', 2);
1279 okc_debug.Reset_Indentation;
1280 END IF;
1281
1282 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1283 (
1284 l_api_name,
1285 G_PKG_NAME,
1286 'OTHERS',
1287 x_msg_count,
1288 x_msg_data,
1289 '_PVT'
1290 );
1291
1292 END validate_row;
1293 ------------------------------------------
1294 -- PL/SQL TBL validate_row for:RGRV_TBL --
1295 ------------------------------------------
1296 PROCEDURE validate_row(
1297 p_api_version IN NUMBER,
1298 p_init_msg_list IN VARCHAR2,
1299 x_return_status OUT NOCOPY VARCHAR2,
1300 x_msg_count OUT NOCOPY NUMBER,
1301 x_msg_data OUT NOCOPY VARCHAR2,
1302 p_rgrv_tbl IN rgrv_tbl_type) IS
1303
1304 l_api_version CONSTANT NUMBER := 1;
1305 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1306 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1307 i NUMBER := 0;
1308 BEGIN
1309
1310 IF (l_debug = 'Y') THEN
1311 okc_debug.Set_Indentation('OKC_RGR_PVT');
1312 okc_debug.log('4900: Entered validate_row', 2);
1313 END IF;
1314
1315 OKC_API.init_msg_list(p_init_msg_list);
1316 -- Make sure PL/SQL table has records in it before passing
1317 IF (p_rgrv_tbl.COUNT > 0) THEN
1318 i := p_rgrv_tbl.FIRST;
1319 LOOP
1320 validate_row (
1321 p_api_version => p_api_version,
1322 p_init_msg_list => OKC_API.G_FALSE,
1323 x_return_status => x_return_status,
1324 x_msg_count => x_msg_count,
1325 x_msg_data => x_msg_data,
1326 p_rgrv_rec => p_rgrv_tbl(i));
1327 EXIT WHEN (i = p_rgrv_tbl.LAST);
1328 i := p_rgrv_tbl.NEXT(i);
1329 END LOOP;
1330 END IF;
1331
1332 IF (l_debug = 'Y') THEN
1333 okc_debug.log('5000: Leaving validate_row', 2);
1334 okc_debug.Reset_Indentation;
1335 END IF;
1336
1337 EXCEPTION
1338 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1339
1340 IF (l_debug = 'Y') THEN
1341 okc_debug.log('5100: Exiting validate_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1342 okc_debug.Reset_Indentation;
1343 END IF;
1344
1345 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1346 (
1347 l_api_name,
1348 G_PKG_NAME,
1349 'OKC_API.G_RET_STS_ERROR',
1350 x_msg_count,
1351 x_msg_data,
1352 '_PVT'
1353 );
1354 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1355
1356 IF (l_debug = 'Y') THEN
1357 okc_debug.log('5200: Exiting validate_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1358 okc_debug.Reset_Indentation;
1359 END IF;
1360
1361 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1362 (
1363 l_api_name,
1364 G_PKG_NAME,
1365 'OKC_API.G_RET_STS_UNEXP_ERROR',
1366 x_msg_count,
1367 x_msg_data,
1368 '_PVT'
1369 );
1370 WHEN OTHERS THEN
1371
1372 IF (l_debug = 'Y') THEN
1373 okc_debug.log('5300: Exiting validate_row:OTHERS Exception', 2);
1374 okc_debug.Reset_Indentation;
1375 END IF;
1376
1377 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1378 (
1379 l_api_name,
1380 G_PKG_NAME,
1381 'OTHERS',
1382 x_msg_count,
1383 x_msg_data,
1384 '_PVT'
1385 );
1386
1387 END validate_row;
1388
1389 ---------------------------------------------------------------------------
1390 -- PROCEDURE insert_row
1391 ---------------------------------------------------------------------------
1392 -------------------------------------
1393 -- insert_row for:OKC_RG_DEF_RULES --
1394 -------------------------------------
1395 PROCEDURE insert_row(
1396 p_init_msg_list IN VARCHAR2,
1397 x_return_status OUT NOCOPY VARCHAR2,
1398 x_msg_count OUT NOCOPY NUMBER,
1399 x_msg_data OUT NOCOPY VARCHAR2,
1400 p_rgr_rec IN rgr_rec_type,
1401 x_rgr_rec OUT NOCOPY rgr_rec_type) IS
1402
1403 l_api_version CONSTANT NUMBER := 1;
1404 l_api_name CONSTANT VARCHAR2(30) := 'RULES_insert_row';
1405 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1406 l_rgr_rec rgr_rec_type := p_rgr_rec;
1407 l_def_rgr_rec rgr_rec_type;
1408 -----------------------------------------
1409 -- Set_Attributes for:OKC_RG_DEF_RULES --
1410 -----------------------------------------
1411 FUNCTION Set_Attributes (
1412 p_rgr_rec IN rgr_rec_type,
1413 x_rgr_rec OUT NOCOPY rgr_rec_type
1414 ) RETURN VARCHAR2 IS
1415 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1416 BEGIN
1417
1418 x_rgr_rec := p_rgr_rec;
1419 RETURN(l_return_status);
1420
1421 END Set_Attributes;
1422 BEGIN
1423
1424 IF (l_debug = 'Y') THEN
1425 okc_debug.Set_Indentation('OKC_RGR_PVT');
1426 okc_debug.log('5500: Entered insert_row', 2);
1427 END IF;
1428
1429 g_insert_mode := TRUE;
1430 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1431 p_init_msg_list,
1432 '_PVT',
1433 x_return_status);
1434 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1435 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1436 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1437 RAISE OKC_API.G_EXCEPTION_ERROR;
1438 END IF;
1439 --- Setting item attributes
1440 l_return_status := Set_Attributes(
1441 p_rgr_rec, -- IN
1442 l_rgr_rec); -- OUT
1443 --- If any errors happen abort API
1444 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1445 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1446 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1447 RAISE OKC_API.G_EXCEPTION_ERROR;
1448 END IF;
1449 INSERT INTO OKC_RG_DEF_RULES(
1450 rgd_code,
1451 rdf_code,
1452 object_version_number,
1453 application_id,
1454 created_by,
1455 creation_date,
1456 last_updated_by,
1457 last_update_date,
1458 optional_yn,
1459 last_update_login,
1460 min_cardinality,
1461 max_cardinality,
1462 pricing_related_yn,
1463 access_level)
1464 VALUES (
1465 l_rgr_rec.rgd_code,
1466 l_rgr_rec.rdf_code,
1467 l_rgr_rec.object_version_number,
1468 fnd_global.resp_appl_id,
1469 l_rgr_rec.created_by,
1470 l_rgr_rec.creation_date,
1471 l_rgr_rec.last_updated_by,
1472 l_rgr_rec.last_update_date,
1473 l_rgr_rec.optional_yn,
1474 l_rgr_rec.last_update_login,
1475 l_rgr_rec.min_cardinality,
1476 l_rgr_rec.max_cardinality,
1477 l_rgr_rec.pricing_related_yn,
1478 l_rgr_rec.access_level);
1479 -- Set OUT values
1480 x_rgr_rec := l_rgr_rec;
1481 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1482
1483 IF (l_debug = 'Y') THEN
1484 okc_debug.log('5600: Leaving insert_row', 2);
1485 okc_debug.Reset_Indentation;
1486 END IF;
1487
1488 EXCEPTION
1489 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1490
1491 IF (l_debug = 'Y') THEN
1492 okc_debug.log('5700: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1493 okc_debug.Reset_Indentation;
1494 END IF;
1495
1496 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1497 (
1498 l_api_name,
1499 G_PKG_NAME,
1500 'OKC_API.G_RET_STS_ERROR',
1501 x_msg_count,
1502 x_msg_data,
1503 '_PVT'
1504 );
1505 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1506
1507 IF (l_debug = 'Y') THEN
1508 okc_debug.log('5800: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1509 okc_debug.Reset_Indentation;
1510 END IF;
1511
1512 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1513 (
1514 l_api_name,
1515 G_PKG_NAME,
1516 'OKC_API.G_RET_STS_UNEXP_ERROR',
1517 x_msg_count,
1518 x_msg_data,
1519 '_PVT'
1520 );
1521 WHEN OTHERS THEN
1522
1523 IF (l_debug = 'Y') THEN
1524 okc_debug.log('5900: Exiting insert_row:OTHERS Exception', 2);
1525 okc_debug.Reset_Indentation;
1526 END IF;
1527
1528 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1529 (
1530 l_api_name,
1531 G_PKG_NAME,
1532 'OTHERS',
1533 x_msg_count,
1534 x_msg_data,
1535 '_PVT'
1536 );
1537
1538 END insert_row;
1539 ---------------------------------------
1540 -- insert_row for:OKC_RG_DEF_RULES_V --
1541 ---------------------------------------
1542 PROCEDURE insert_row(
1543 p_api_version IN NUMBER,
1544 p_init_msg_list IN VARCHAR2,
1545 x_return_status OUT NOCOPY VARCHAR2,
1546 x_msg_count OUT NOCOPY NUMBER,
1547 x_msg_data OUT NOCOPY VARCHAR2,
1548 p_rgrv_rec IN rgrv_rec_type,
1549 x_rgrv_rec OUT NOCOPY rgrv_rec_type) IS
1550
1551 l_api_version CONSTANT NUMBER := 1;
1552 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1553 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1554 l_rgrv_rec rgrv_rec_type;
1555 l_def_rgrv_rec rgrv_rec_type;
1556 l_rgr_rec rgr_rec_type;
1557 lx_rgr_rec rgr_rec_type;
1558 -------------------------------
1559 -- FUNCTION fill_who_columns --
1560 -------------------------------
1561 FUNCTION fill_who_columns (
1562 p_rgrv_rec IN rgrv_rec_type
1563 ) RETURN rgrv_rec_type IS
1564 l_rgrv_rec rgrv_rec_type := p_rgrv_rec;
1565 BEGIN
1566
1567 l_rgrv_rec.CREATION_DATE := SYSDATE;
1568 l_rgrv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1569 l_rgrv_rec.LAST_UPDATE_DATE := SYSDATE;
1570 l_rgrv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1571 l_rgrv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1572 RETURN(l_rgrv_rec);
1573
1574 END fill_who_columns;
1575 -------------------------------------------
1576 -- Set_Attributes for:OKC_RG_DEF_RULES_V --
1577 -------------------------------------------
1578 FUNCTION Set_Attributes (
1579 p_rgrv_rec IN rgrv_rec_type,
1580 x_rgrv_rec OUT NOCOPY rgrv_rec_type
1581 ) RETURN VARCHAR2 IS
1582 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1583 BEGIN
1584
1585 x_rgrv_rec := p_rgrv_rec;
1586 x_rgrv_rec.OBJECT_VERSION_NUMBER := 1;
1587 RETURN(l_return_status);
1588
1589 END Set_Attributes;
1590 BEGIN
1591
1592 IF (l_debug = 'Y') THEN
1593 okc_debug.Set_Indentation('OKC_RGR_PVT');
1594 okc_debug.log('6200: Entered insert_row', 2);
1595 END IF;
1596
1597 g_insert_mode := TRUE;
1598 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1599 G_PKG_NAME,
1600 p_init_msg_list,
1601 l_api_version,
1602 p_api_version,
1603 '_PVT',
1604 x_return_status);
1605 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1606 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1607 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1608 RAISE OKC_API.G_EXCEPTION_ERROR;
1609 END IF;
1610 l_rgrv_rec := null_out_defaults(p_rgrv_rec);
1611 --- Setting item attributes
1612 l_return_status := Set_Attributes(
1613 l_rgrv_rec, -- IN
1614 l_def_rgrv_rec); -- OUT
1615 --- If any errors happen abort API
1616 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1617 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1618 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1619 RAISE OKC_API.G_EXCEPTION_ERROR;
1620 END IF;
1621 l_def_rgrv_rec := fill_who_columns(l_def_rgrv_rec);
1622 --- Validate all non-missing attributes (Item Level Validation)
1623 l_return_status := Validate_Attributes(l_def_rgrv_rec);
1624 --- If any errors happen abort API
1625 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1626 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1627 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1628 RAISE OKC_API.G_EXCEPTION_ERROR;
1629 END IF;
1630 l_return_status := Validate_Record(l_def_rgrv_rec);
1631 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1632 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1633 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1634 RAISE OKC_API.G_EXCEPTION_ERROR;
1635 END IF;
1636 --------------------------------------
1637 -- Move VIEW record to "Child" records
1638 --------------------------------------
1639 migrate(l_def_rgrv_rec, l_rgr_rec);
1640 --------------------------------------------
1641 -- Call the INSERT_ROW for each child record
1642 --------------------------------------------
1643 insert_row(
1644 p_init_msg_list,
1645 x_return_status,
1646 x_msg_count,
1647 x_msg_data,
1648 l_rgr_rec,
1649 lx_rgr_rec
1650 );
1651 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1652 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1653 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1654 RAISE OKC_API.G_EXCEPTION_ERROR;
1655 END IF;
1656 migrate(lx_rgr_rec, l_def_rgrv_rec);
1657 -- Set OUT values
1658 x_rgrv_rec := l_def_rgrv_rec;
1659 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1660
1661 IF (l_debug = 'Y') THEN
1662 okc_debug.log('6300: Leaving insert_row', 2);
1663 okc_debug.Reset_Indentation;
1664 END IF;
1665
1666 EXCEPTION
1667 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1668
1669 IF (l_debug = 'Y') THEN
1670 okc_debug.log('6400: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1671 okc_debug.Reset_Indentation;
1672 END IF;
1673
1674 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1675 (
1676 l_api_name,
1677 G_PKG_NAME,
1678 'OKC_API.G_RET_STS_ERROR',
1679 x_msg_count,
1680 x_msg_data,
1681 '_PVT'
1682 );
1683 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1684
1685 IF (l_debug = 'Y') THEN
1686 okc_debug.log('6500: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1687 okc_debug.Reset_Indentation;
1688 END IF;
1689
1690 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1691 (
1692 l_api_name,
1693 G_PKG_NAME,
1694 'OKC_API.G_RET_STS_UNEXP_ERROR',
1695 x_msg_count,
1696 x_msg_data,
1697 '_PVT'
1698 );
1699 WHEN OTHERS THEN
1700
1701 IF (l_debug = 'Y') THEN
1702 okc_debug.log('6600: Exiting insert_row:OTHERS Exception', 2);
1703 okc_debug.Reset_Indentation;
1704 END IF;
1705
1706 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1707 (
1708 l_api_name,
1709 G_PKG_NAME,
1710 'OTHERS',
1711 x_msg_count,
1712 x_msg_data,
1713 '_PVT'
1714 );
1715
1716 END insert_row;
1717 ----------------------------------------
1718 -- PL/SQL TBL insert_row for:RGRV_TBL --
1719 ----------------------------------------
1720 PROCEDURE insert_row(
1721 p_api_version IN NUMBER,
1722 p_init_msg_list IN VARCHAR2,
1723 x_return_status OUT NOCOPY VARCHAR2,
1724 x_msg_count OUT NOCOPY NUMBER,
1725 x_msg_data OUT NOCOPY VARCHAR2,
1726 p_rgrv_tbl IN rgrv_tbl_type,
1727 x_rgrv_tbl OUT NOCOPY rgrv_tbl_type) IS
1728
1729 l_api_version CONSTANT NUMBER := 1;
1730 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1731 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1732 i NUMBER := 0;
1733 BEGIN
1734
1735 IF (l_debug = 'Y') THEN
1736 okc_debug.Set_Indentation('OKC_RGR_PVT');
1737 okc_debug.log('6700: Entered insert_row', 2);
1738 END IF;
1739
1740 OKC_API.init_msg_list(p_init_msg_list);
1741 -- Make sure PL/SQL table has records in it before passing
1742 IF (p_rgrv_tbl.COUNT > 0) THEN
1743 i := p_rgrv_tbl.FIRST;
1744 LOOP
1745 insert_row (
1746 p_api_version => p_api_version,
1747 p_init_msg_list => OKC_API.G_FALSE,
1748 x_return_status => x_return_status,
1749 x_msg_count => x_msg_count,
1750 x_msg_data => x_msg_data,
1751 p_rgrv_rec => p_rgrv_tbl(i),
1752 x_rgrv_rec => x_rgrv_tbl(i));
1753 EXIT WHEN (i = p_rgrv_tbl.LAST);
1754 i := p_rgrv_tbl.NEXT(i);
1755 END LOOP;
1756 END IF;
1757
1758 IF (l_debug = 'Y') THEN
1759 okc_debug.log('6800: Leaving insert_row', 2);
1760 okc_debug.Reset_Indentation;
1761 END IF;
1762
1763 EXCEPTION
1764 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1765
1766 IF (l_debug = 'Y') THEN
1767 okc_debug.log('6900: Exiting insert_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1768 okc_debug.Reset_Indentation;
1769 END IF;
1770
1771 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1772 (
1773 l_api_name,
1774 G_PKG_NAME,
1775 'OKC_API.G_RET_STS_ERROR',
1776 x_msg_count,
1777 x_msg_data,
1778 '_PVT'
1779 );
1780 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1781
1782 IF (l_debug = 'Y') THEN
1783 okc_debug.log('7000: Exiting insert_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1784 okc_debug.Reset_Indentation;
1785 END IF;
1786
1787 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1788 (
1789 l_api_name,
1790 G_PKG_NAME,
1791 'OKC_API.G_RET_STS_UNEXP_ERROR',
1792 x_msg_count,
1793 x_msg_data,
1794 '_PVT'
1795 );
1796 WHEN OTHERS THEN
1797
1798 IF (l_debug = 'Y') THEN
1799 okc_debug.log('7100: Exiting insert_row:OTHERS Exception', 2);
1800 okc_debug.Reset_Indentation;
1801 END IF;
1802
1803 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1804 (
1805 l_api_name,
1806 G_PKG_NAME,
1807 'OTHERS',
1808 x_msg_count,
1809 x_msg_data,
1810 '_PVT'
1811 );
1812
1813 END insert_row;
1814
1815 ---------------------------------------------------------------------------
1816 -- PROCEDURE lock_row
1817 ---------------------------------------------------------------------------
1818 -----------------------------------
1819 -- lock_row for:OKC_RG_DEF_RULES --
1820 -----------------------------------
1821 PROCEDURE lock_row(
1822 p_init_msg_list IN VARCHAR2,
1823 x_return_status OUT NOCOPY VARCHAR2,
1824 x_msg_count OUT NOCOPY NUMBER,
1825 x_msg_data OUT NOCOPY VARCHAR2,
1826 p_rgr_rec IN rgr_rec_type) IS
1827
1828 E_Resource_Busy EXCEPTION;
1829 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1830 CURSOR lock_csr (p_rgr_rec IN rgr_rec_type) IS
1831 SELECT OBJECT_VERSION_NUMBER
1832 FROM OKC_RG_DEF_RULES
1833 WHERE RGD_CODE = p_rgr_rec.rgd_code
1834 AND RDF_CODE = p_rgr_rec.rdf_code
1835 AND OBJECT_VERSION_NUMBER = p_rgr_rec.object_version_number
1836 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1837
1838 CURSOR lchk_csr (p_rgr_rec IN rgr_rec_type) IS
1839 SELECT OBJECT_VERSION_NUMBER
1840 FROM OKC_RG_DEF_RULES
1841 WHERE RGD_CODE = p_rgr_rec.rgd_code
1842 AND RDF_CODE = p_rgr_rec.rdf_code;
1843 l_api_version CONSTANT NUMBER := 1;
1844 l_api_name CONSTANT VARCHAR2(30) := 'RULES_lock_row';
1845 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1846 l_object_version_number OKC_RG_DEF_RULES.OBJECT_VERSION_NUMBER%TYPE;
1847 lc_object_version_number OKC_RG_DEF_RULES.OBJECT_VERSION_NUMBER%TYPE;
1848 l_row_notfound BOOLEAN := FALSE;
1849 lc_row_notfound BOOLEAN := FALSE;
1850 BEGIN
1851
1852 IF (l_debug = 'Y') THEN
1853 okc_debug.Set_Indentation('OKC_RGR_PVT');
1854 okc_debug.log('7200: Entered lock_row', 2);
1855 END IF;
1856
1857 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1858 p_init_msg_list,
1859 '_PVT',
1860 x_return_status);
1861 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1862 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1863 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1864 RAISE OKC_API.G_EXCEPTION_ERROR;
1865 END IF;
1866 BEGIN
1867
1868 IF (l_debug = 'Y') THEN
1869 okc_debug.Set_Indentation('OKC_RGR_PVT');
1870 okc_debug.log('7300: Entered lock_row', 2);
1871 END IF;
1872
1873 OPEN lock_csr(p_rgr_rec);
1874 FETCH lock_csr INTO l_object_version_number;
1875 l_row_notfound := lock_csr%NOTFOUND;
1876 CLOSE lock_csr;
1877
1878 IF (l_debug = 'Y') THEN
1879 okc_debug.log('7400: Leaving lock_row', 2);
1880 okc_debug.Reset_Indentation;
1881 END IF;
1882
1883 EXCEPTION
1884 WHEN E_Resource_Busy THEN
1885
1886 IF (l_debug = 'Y') THEN
1887 okc_debug.log('7500: Exiting lock_row:E_Resource_Busy Exception', 2);
1888 okc_debug.Reset_Indentation;
1889 END IF;
1890
1891 IF (lock_csr%ISOPEN) THEN
1892 CLOSE lock_csr;
1893 END IF;
1894 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1895 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1896 END;
1897
1898 IF ( l_row_notfound ) THEN
1899 OPEN lchk_csr(p_rgr_rec);
1900 FETCH lchk_csr INTO lc_object_version_number;
1901 lc_row_notfound := lchk_csr%NOTFOUND;
1902 CLOSE lchk_csr;
1903 END IF;
1904 IF (lc_row_notfound) THEN
1905 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1906 RAISE OKC_API.G_EXCEPTION_ERROR;
1907 ELSIF lc_object_version_number > p_rgr_rec.object_version_number THEN
1908 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1909 RAISE OKC_API.G_EXCEPTION_ERROR;
1910 ELSIF lc_object_version_number <> p_rgr_rec.object_version_number THEN
1911 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1912 RAISE OKC_API.G_EXCEPTION_ERROR;
1913 ELSIF lc_object_version_number = -1 THEN
1914 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1915 RAISE OKC_API.G_EXCEPTION_ERROR;
1916 END IF;
1917 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1918
1919 IF (l_debug = 'Y') THEN
1920 okc_debug.log('7600: Leaving lock_row', 2);
1921 okc_debug.Reset_Indentation;
1922 END IF;
1923
1924 EXCEPTION
1925 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1926
1927 IF (l_debug = 'Y') THEN
1928 okc_debug.log('7700: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
1929 okc_debug.Reset_Indentation;
1930 END IF;
1931
1932 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1933 (
1934 l_api_name,
1935 G_PKG_NAME,
1936 'OKC_API.G_RET_STS_ERROR',
1937 x_msg_count,
1938 x_msg_data,
1939 '_PVT'
1940 );
1941 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1942
1943 IF (l_debug = 'Y') THEN
1944 okc_debug.log('7800: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
1945 okc_debug.Reset_Indentation;
1946 END IF;
1947
1948 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1949 (
1950 l_api_name,
1951 G_PKG_NAME,
1952 'OKC_API.G_RET_STS_UNEXP_ERROR',
1953 x_msg_count,
1954 x_msg_data,
1955 '_PVT'
1956 );
1957 WHEN OTHERS THEN
1958
1959 IF (l_debug = 'Y') THEN
1960 okc_debug.log('7900: Exiting lock_row:OTHERS Exception', 2);
1961 okc_debug.Reset_Indentation;
1962 END IF;
1963
1964 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1965 (
1966 l_api_name,
1967 G_PKG_NAME,
1968 'OTHERS',
1969 x_msg_count,
1970 x_msg_data,
1971 '_PVT'
1972 );
1973
1974 END lock_row;
1975 -------------------------------------
1976 -- lock_row for:OKC_RG_DEF_RULES_V --
1977 -------------------------------------
1978 PROCEDURE lock_row(
1979 p_api_version IN NUMBER,
1980 p_init_msg_list IN VARCHAR2,
1981 x_return_status OUT NOCOPY VARCHAR2,
1982 x_msg_count OUT NOCOPY NUMBER,
1983 x_msg_data OUT NOCOPY VARCHAR2,
1984 p_rgrv_rec IN rgrv_rec_type) IS
1985
1986 l_api_version CONSTANT NUMBER := 1;
1987 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1988 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1989 l_rgr_rec rgr_rec_type;
1990 BEGIN
1991
1992 IF (l_debug = 'Y') THEN
1993 okc_debug.Set_Indentation('OKC_RGR_PVT');
1994 okc_debug.log('8000: Entered lock_row', 2);
1995 END IF;
1996
1997 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1998 G_PKG_NAME,
1999 p_init_msg_list,
2000 l_api_version,
2001 p_api_version,
2002 '_PVT',
2003 x_return_status);
2004 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2005 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2006 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2007 RAISE OKC_API.G_EXCEPTION_ERROR;
2008 END IF;
2009 --------------------------------------
2010 -- Move VIEW record to "Child" records
2011 --------------------------------------
2012 migrate(p_rgrv_rec, l_rgr_rec);
2013 --------------------------------------------
2014 -- Call the LOCK_ROW for each child record
2015 --------------------------------------------
2016 lock_row(
2017 p_init_msg_list,
2018 x_return_status,
2019 x_msg_count,
2020 x_msg_data,
2021 l_rgr_rec
2022 );
2023 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2024 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2025 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2026 RAISE OKC_API.G_EXCEPTION_ERROR;
2027 END IF;
2028 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2029
2030 IF (l_debug = 'Y') THEN
2031 okc_debug.log('8100: Leaving lock_row', 2);
2032 okc_debug.Reset_Indentation;
2033 END IF;
2034
2035 EXCEPTION
2036 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2037
2038 IF (l_debug = 'Y') THEN
2039 okc_debug.log('8200: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2040 okc_debug.Reset_Indentation;
2041 END IF;
2042
2043 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2044 (
2045 l_api_name,
2046 G_PKG_NAME,
2047 'OKC_API.G_RET_STS_ERROR',
2048 x_msg_count,
2049 x_msg_data,
2050 '_PVT'
2051 );
2052 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2053
2054 IF (l_debug = 'Y') THEN
2055 okc_debug.log('8300: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2056 okc_debug.Reset_Indentation;
2057 END IF;
2058
2059 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2060 (
2061 l_api_name,
2062 G_PKG_NAME,
2063 'OKC_API.G_RET_STS_UNEXP_ERROR',
2064 x_msg_count,
2065 x_msg_data,
2066 '_PVT'
2067 );
2068 WHEN OTHERS THEN
2069
2070 IF (l_debug = 'Y') THEN
2071 okc_debug.log('8400: Exiting lock_row:OTHERS Exception', 2);
2072 okc_debug.Reset_Indentation;
2073 END IF;
2074
2075 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2076 (
2077 l_api_name,
2078 G_PKG_NAME,
2079 'OTHERS',
2080 x_msg_count,
2081 x_msg_data,
2082 '_PVT'
2083 );
2084
2085 END lock_row;
2086 --------------------------------------
2087 -- PL/SQL TBL lock_row for:RGRV_TBL --
2088 --------------------------------------
2089 PROCEDURE lock_row(
2090 p_api_version IN NUMBER,
2091 p_init_msg_list IN VARCHAR2,
2092 x_return_status OUT NOCOPY VARCHAR2,
2093 x_msg_count OUT NOCOPY NUMBER,
2094 x_msg_data OUT NOCOPY VARCHAR2,
2095 p_rgrv_tbl IN rgrv_tbl_type) IS
2096
2097 l_api_version CONSTANT NUMBER := 1;
2098 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2099 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2100 i NUMBER := 0;
2101 BEGIN
2102
2103 IF (l_debug = 'Y') THEN
2104 okc_debug.Set_Indentation('OKC_RGR_PVT');
2105 okc_debug.log('8500: Entered lock_row', 2);
2106 END IF;
2107
2108 OKC_API.init_msg_list(p_init_msg_list);
2109 -- Make sure PL/SQL table has records in it before passing
2110 IF (p_rgrv_tbl.COUNT > 0) THEN
2111 i := p_rgrv_tbl.FIRST;
2112 LOOP
2113 lock_row (
2114 p_api_version => p_api_version,
2115 p_init_msg_list => OKC_API.G_FALSE,
2116 x_return_status => x_return_status,
2117 x_msg_count => x_msg_count,
2118 x_msg_data => x_msg_data,
2119 p_rgrv_rec => p_rgrv_tbl(i));
2120 EXIT WHEN (i = p_rgrv_tbl.LAST);
2121 i := p_rgrv_tbl.NEXT(i);
2122 END LOOP;
2123 END IF;
2124
2125 IF (l_debug = 'Y') THEN
2126 okc_debug.log('8600: Leaving lock_row', 2);
2127 okc_debug.Reset_Indentation;
2128 END IF;
2129
2130 EXCEPTION
2131 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2132
2133 IF (l_debug = 'Y') THEN
2134 okc_debug.log('8700: Exiting lock_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2135 okc_debug.Reset_Indentation;
2136 END IF;
2137
2138 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2139 (
2140 l_api_name,
2141 G_PKG_NAME,
2142 'OKC_API.G_RET_STS_ERROR',
2143 x_msg_count,
2144 x_msg_data,
2145 '_PVT'
2146 );
2147 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2148
2149 IF (l_debug = 'Y') THEN
2150 okc_debug.log('8800: Exiting lock_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2151 okc_debug.Reset_Indentation;
2152 END IF;
2153
2154 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2155 (
2156 l_api_name,
2157 G_PKG_NAME,
2158 'OKC_API.G_RET_STS_UNEXP_ERROR',
2159 x_msg_count,
2160 x_msg_data,
2161 '_PVT'
2162 );
2163 WHEN OTHERS THEN
2164
2165 IF (l_debug = 'Y') THEN
2166 okc_debug.log('8900: Exiting lock_row:OTHERS Exception', 2);
2167 okc_debug.Reset_Indentation;
2168 END IF;
2169
2170 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2171 (
2172 l_api_name,
2173 G_PKG_NAME,
2174 'OTHERS',
2175 x_msg_count,
2176 x_msg_data,
2177 '_PVT'
2178 );
2179
2180 END lock_row;
2181
2182 ---------------------------------------------------------------------------
2183 -- PROCEDURE update_row
2184 ---------------------------------------------------------------------------
2185 -------------------------------------
2186 -- update_row for:OKC_RG_DEF_RULES --
2187 -------------------------------------
2188 PROCEDURE update_row(
2189 p_init_msg_list IN VARCHAR2,
2190 x_return_status OUT NOCOPY VARCHAR2,
2191 x_msg_count OUT NOCOPY NUMBER,
2192 x_msg_data OUT NOCOPY VARCHAR2,
2193 p_rgr_rec IN rgr_rec_type,
2194 x_rgr_rec OUT NOCOPY rgr_rec_type) IS
2195
2196 l_api_version CONSTANT NUMBER := 1;
2197 l_api_name CONSTANT VARCHAR2(30) := 'RULES_update_row';
2198 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2199 l_rgr_rec rgr_rec_type := p_rgr_rec;
2200 l_def_rgr_rec rgr_rec_type;
2201 l_row_notfound BOOLEAN := TRUE;
2202 ----------------------------------
2203 -- FUNCTION populate_new_record --
2204 ----------------------------------
2205 FUNCTION populate_new_record (
2206 p_rgr_rec IN rgr_rec_type,
2207 x_rgr_rec OUT NOCOPY rgr_rec_type
2208 ) RETURN VARCHAR2 IS
2209 l_rgr_rec rgr_rec_type;
2210 l_row_notfound BOOLEAN := TRUE;
2211 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2212 BEGIN
2213
2214 IF (l_debug = 'Y') THEN
2215 okc_debug.Set_Indentation('OKC_RGR_PVT');
2216 okc_debug.log('9000: Entered populate_new_record', 2);
2217 END IF;
2218
2219 x_rgr_rec := p_rgr_rec;
2220 -- Get current database values
2221 l_rgr_rec := get_rec(p_rgr_rec, l_row_notfound);
2222 IF (l_row_notfound) THEN
2223 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2224 END IF;
2225 IF (x_rgr_rec.rgd_code = OKC_API.G_MISS_CHAR)
2226 THEN
2227 x_rgr_rec.rgd_code := l_rgr_rec.rgd_code;
2228 END IF;
2229 IF (x_rgr_rec.rdf_code = OKC_API.G_MISS_CHAR)
2230 THEN
2231 x_rgr_rec.rdf_code := l_rgr_rec.rdf_code;
2232 END IF;
2233 IF (x_rgr_rec.object_version_number = OKC_API.G_MISS_NUM)
2234 THEN
2235 x_rgr_rec.object_version_number := l_rgr_rec.object_version_number;
2236 END IF;
2237 IF (x_rgr_rec.created_by = OKC_API.G_MISS_NUM)
2238 THEN
2239 x_rgr_rec.created_by := l_rgr_rec.created_by;
2240 END IF;
2241 IF (x_rgr_rec.creation_date = OKC_API.G_MISS_DATE)
2242 THEN
2243 x_rgr_rec.creation_date := l_rgr_rec.creation_date;
2244 END IF;
2245 IF (x_rgr_rec.last_updated_by = OKC_API.G_MISS_NUM)
2246 THEN
2247 x_rgr_rec.last_updated_by := l_rgr_rec.last_updated_by;
2248 END IF;
2249 IF (x_rgr_rec.last_update_date = OKC_API.G_MISS_DATE)
2250 THEN
2251 x_rgr_rec.last_update_date := l_rgr_rec.last_update_date;
2252 END IF;
2253 IF (x_rgr_rec.optional_yn = OKC_API.G_MISS_CHAR)
2254 THEN
2255 x_rgr_rec.optional_yn := l_rgr_rec.optional_yn;
2256 END IF;
2257 IF (x_rgr_rec.last_update_login = OKC_API.G_MISS_NUM)
2258 THEN
2259 x_rgr_rec.last_update_login := l_rgr_rec.last_update_login;
2260 END IF;
2261 IF (x_rgr_rec.min_cardinality = OKC_API.G_MISS_NUM)
2262 THEN
2263 x_rgr_rec.min_cardinality := l_rgr_rec.min_cardinality;
2264 END IF;
2265 IF (x_rgr_rec.max_cardinality = OKC_API.G_MISS_NUM)
2266 THEN
2267 x_rgr_rec.max_cardinality := l_rgr_rec.max_cardinality;
2268 END IF;
2269 IF (x_rgr_rec.pricing_related_yn = OKC_API.G_MISS_CHAR)
2270 THEN
2271 x_rgr_rec.pricing_related_yn := l_rgr_rec.pricing_related_yn;
2272 END IF;
2273 IF (x_rgr_rec.access_level = OKC_API.G_MISS_CHAR)
2274 THEN
2275 x_rgr_rec.access_level := l_rgr_rec.access_level;
2276 END IF;
2277
2278 IF (l_debug = 'Y') THEN
2279 okc_debug.log('11950: Leaving populate_new_record ', 2);
2280 okc_debug.Reset_Indentation;
2281 END IF;
2282
2283 RETURN(l_return_status);
2284
2285 END populate_new_record;
2286 -----------------------------------------
2287 -- Set_Attributes for:OKC_RG_DEF_RULES --
2288 -----------------------------------------
2289 FUNCTION Set_Attributes (
2290 p_rgr_rec IN rgr_rec_type,
2291 x_rgr_rec OUT NOCOPY rgr_rec_type
2292 ) RETURN VARCHAR2 IS
2293 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2294 BEGIN
2295
2296 x_rgr_rec := p_rgr_rec;
2297 RETURN(l_return_status);
2298
2299 END Set_Attributes;
2300 BEGIN
2301
2302 IF (l_debug = 'Y') THEN
2303 okc_debug.Set_Indentation('OKC_RGR_PVT');
2304 okc_debug.log('9200: Entered update_row', 2);
2305 END IF;
2306
2307 g_insert_mode := FALSE;
2308 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2309 p_init_msg_list,
2310 '_PVT',
2311 x_return_status);
2312 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2313 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2314 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2315 RAISE OKC_API.G_EXCEPTION_ERROR;
2316 END IF;
2317 --- Setting item attributes
2318 l_return_status := Set_Attributes(
2319 p_rgr_rec, -- IN
2320 l_rgr_rec); -- OUT
2321 --- If any errors happen abort API
2322 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2323 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2324 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2325 RAISE OKC_API.G_EXCEPTION_ERROR;
2326 END IF;
2327 l_return_status := populate_new_record(l_rgr_rec, l_def_rgr_rec);
2328 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2329 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2330 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2331 RAISE OKC_API.G_EXCEPTION_ERROR;
2332 END IF;
2333 UPDATE OKC_RG_DEF_RULES
2334 SET OBJECT_VERSION_NUMBER = l_def_rgr_rec.object_version_number,
2335 CREATED_BY = l_def_rgr_rec.created_by,
2336 CREATION_DATE = l_def_rgr_rec.creation_date,
2337 LAST_UPDATED_BY = l_def_rgr_rec.last_updated_by,
2338 LAST_UPDATE_DATE = l_def_rgr_rec.last_update_date,
2339 OPTIONAL_YN = l_def_rgr_rec.optional_yn,
2340 LAST_UPDATE_LOGIN = l_def_rgr_rec.last_update_login,
2341 MIN_CARDINALITY = l_def_rgr_rec.min_cardinality,
2342 MAX_CARDINALITY = l_def_rgr_rec.max_cardinality,
2343 PRICING_RELATED_YN = l_def_rgr_rec.pricing_related_yn,
2344 ACCESS_LEVEL = l_def_rgr_rec.access_level
2345 WHERE RGD_CODE = l_def_rgr_rec.rgd_code
2346 AND RDF_CODE = l_def_rgr_rec.rdf_code;
2347
2348 x_rgr_rec := l_def_rgr_rec;
2349 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2350
2351 IF (l_debug = 'Y') THEN
2352 okc_debug.log('9300: Leaving update_row', 2);
2353 okc_debug.Reset_Indentation;
2354 END IF;
2355
2356 EXCEPTION
2357 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2358
2359 IF (l_debug = 'Y') THEN
2360 okc_debug.log('9400: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2361 okc_debug.Reset_Indentation;
2362 END IF;
2363
2364 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2365 (
2366 l_api_name,
2367 G_PKG_NAME,
2368 'OKC_API.G_RET_STS_ERROR',
2369 x_msg_count,
2370 x_msg_data,
2371 '_PVT'
2372 );
2373 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2374
2375 IF (l_debug = 'Y') THEN
2376 okc_debug.log('9500: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2377 okc_debug.Reset_Indentation;
2378 END IF;
2379
2380 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2381 (
2382 l_api_name,
2383 G_PKG_NAME,
2384 'OKC_API.G_RET_STS_UNEXP_ERROR',
2385 x_msg_count,
2386 x_msg_data,
2387 '_PVT'
2388 );
2389 WHEN OTHERS THEN
2390
2391 IF (l_debug = 'Y') THEN
2392 okc_debug.log('9600: Exiting update_row:OTHERS Exception', 2);
2393 okc_debug.Reset_Indentation;
2394 END IF;
2395
2396 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2397 (
2398 l_api_name,
2399 G_PKG_NAME,
2400 'OTHERS',
2401 x_msg_count,
2402 x_msg_data,
2403 '_PVT'
2404 );
2405
2406 END update_row;
2407 ---------------------------------------
2408 -- update_row for:OKC_RG_DEF_RULES_V --
2409 ---------------------------------------
2410 PROCEDURE update_row(
2411 p_api_version IN NUMBER,
2412 p_init_msg_list IN VARCHAR2,
2413 x_return_status OUT NOCOPY VARCHAR2,
2414 x_msg_count OUT NOCOPY NUMBER,
2415 x_msg_data OUT NOCOPY VARCHAR2,
2416 p_rgrv_rec IN rgrv_rec_type,
2417 x_rgrv_rec OUT NOCOPY rgrv_rec_type) IS
2418
2419 l_api_version CONSTANT NUMBER := 1;
2420 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2421 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2422 l_rgrv_rec rgrv_rec_type := p_rgrv_rec;
2423 l_def_rgrv_rec rgrv_rec_type;
2424 l_rgr_rec rgr_rec_type;
2425 lx_rgr_rec rgr_rec_type;
2426 -------------------------------
2427 -- FUNCTION fill_who_columns --
2428 -------------------------------
2429 FUNCTION fill_who_columns (
2430 p_rgrv_rec IN rgrv_rec_type
2431 ) RETURN rgrv_rec_type IS
2432 l_rgrv_rec rgrv_rec_type := p_rgrv_rec;
2433 BEGIN
2434
2435 l_rgrv_rec.LAST_UPDATE_DATE := SYSDATE;
2436 l_rgrv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2437 l_rgrv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2438 RETURN(l_rgrv_rec);
2439
2440 END fill_who_columns;
2441 ----------------------------------
2442 -- FUNCTION populate_new_record --
2443 ----------------------------------
2444 FUNCTION populate_new_record (
2445 p_rgrv_rec IN rgrv_rec_type,
2446 x_rgrv_rec OUT NOCOPY rgrv_rec_type
2447 ) RETURN VARCHAR2 IS
2448 l_rgrv_rec rgrv_rec_type;
2449 l_row_notfound BOOLEAN := TRUE;
2450 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2451 BEGIN
2452
2453 IF (l_debug = 'Y') THEN
2454 okc_debug.Set_Indentation('OKC_RGR_PVT');
2455 okc_debug.log('9800: Entered populate_new_record', 2);
2456 END IF;
2457
2458 x_rgrv_rec := p_rgrv_rec;
2459 -- Get current database values
2460 l_rgrv_rec := get_rec(p_rgrv_rec, l_row_notfound);
2461 IF (l_row_notfound) THEN
2462 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2463 END IF;
2464 IF (x_rgrv_rec.rgd_code = OKC_API.G_MISS_CHAR)
2465 THEN
2466 x_rgrv_rec.rgd_code := l_rgrv_rec.rgd_code;
2467 END IF;
2468 IF (x_rgrv_rec.rdf_code = OKC_API.G_MISS_CHAR)
2469 THEN
2470 x_rgrv_rec.rdf_code := l_rgrv_rec.rdf_code;
2471 END IF;
2472 IF (x_rgrv_rec.object_version_number = OKC_API.G_MISS_NUM)
2473 THEN
2474 x_rgrv_rec.object_version_number := l_rgrv_rec.object_version_number;
2475 END IF;
2476 IF (x_rgrv_rec.optional_yn = OKC_API.G_MISS_CHAR)
2477 THEN
2478 x_rgrv_rec.optional_yn := l_rgrv_rec.optional_yn;
2479 END IF;
2480 IF (x_rgrv_rec.created_by = OKC_API.G_MISS_NUM)
2481 THEN
2482 x_rgrv_rec.created_by := l_rgrv_rec.created_by;
2483 END IF;
2484 IF (x_rgrv_rec.creation_date = OKC_API.G_MISS_DATE)
2485 THEN
2486 x_rgrv_rec.creation_date := l_rgrv_rec.creation_date;
2487 END IF;
2488 IF (x_rgrv_rec.last_updated_by = OKC_API.G_MISS_NUM)
2489 THEN
2490 x_rgrv_rec.last_updated_by := l_rgrv_rec.last_updated_by;
2491 END IF;
2492 IF (x_rgrv_rec.last_update_date = OKC_API.G_MISS_DATE)
2493 THEN
2494 x_rgrv_rec.last_update_date := l_rgrv_rec.last_update_date;
2495 END IF;
2496 IF (x_rgrv_rec.last_update_login = OKC_API.G_MISS_NUM)
2497 THEN
2498 x_rgrv_rec.last_update_login := l_rgrv_rec.last_update_login;
2499 END IF;
2500 IF (x_rgrv_rec.min_cardinality = OKC_API.G_MISS_NUM)
2501 THEN
2502 x_rgrv_rec.min_cardinality := l_rgrv_rec.min_cardinality;
2503 END IF;
2504 IF (x_rgrv_rec.max_cardinality = OKC_API.G_MISS_NUM)
2505 THEN
2506 x_rgrv_rec.max_cardinality := l_rgrv_rec.max_cardinality;
2507 END IF;
2508 IF (x_rgrv_rec.pricing_related_yn = OKC_API.G_MISS_CHAR)
2509 THEN
2510 x_rgrv_rec.pricing_related_yn := l_rgrv_rec.pricing_related_yn;
2511 END IF;
2512 IF (x_rgrv_rec.access_level = OKC_API.G_MISS_CHAR)
2513 THEN
2514 x_rgrv_rec.access_level := l_rgrv_rec.access_level;
2515 END IF;
2516
2517 IF (l_debug = 'Y') THEN
2518 okc_debug.log('11950: Leaving populate_new_record ', 2);
2519 okc_debug.Reset_Indentation;
2520 END IF;
2521
2522 RETURN(l_return_status);
2523
2524 END populate_new_record;
2525 -------------------------------------------
2526 -- Set_Attributes for:OKC_RG_DEF_RULES_V --
2527 -------------------------------------------
2528 FUNCTION Set_Attributes (
2529 p_rgrv_rec IN rgrv_rec_type,
2530 x_rgrv_rec OUT NOCOPY rgrv_rec_type
2531 ) RETURN VARCHAR2 IS
2532 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2533 BEGIN
2534
2535 x_rgrv_rec := p_rgrv_rec;
2536 x_rgrv_rec.OBJECT_VERSION_NUMBER := NVL(x_rgrv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2537 RETURN(l_return_status);
2538
2539 END Set_Attributes;
2540 BEGIN
2541
2542 g_insert_mode := FALSE;
2543 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2544 G_PKG_NAME,
2545 p_init_msg_list,
2546 l_api_version,
2547 p_api_version,
2548 '_PVT',
2549 x_return_status);
2550 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2551 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2552 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2553 RAISE OKC_API.G_EXCEPTION_ERROR;
2554 END IF;
2555 --- Setting item attributes
2556 l_return_status := Set_Attributes(
2557 p_rgrv_rec, -- IN
2558 l_rgrv_rec); -- OUT
2559 --- If any errors happen abort API
2560 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2561 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2562 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2563 RAISE OKC_API.G_EXCEPTION_ERROR;
2564 END IF;
2565 l_return_status := populate_new_record(l_rgrv_rec, l_def_rgrv_rec);
2566 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2567 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2568 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2569 RAISE OKC_API.G_EXCEPTION_ERROR;
2570 END IF;
2571 l_def_rgrv_rec := fill_who_columns(l_def_rgrv_rec);
2572 --- Validate all non-missing attributes (Item Level Validation)
2573 l_return_status := Validate_Attributes(l_def_rgrv_rec);
2574 --- If any errors happen abort API
2575 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2576 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2577 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2578 RAISE OKC_API.G_EXCEPTION_ERROR;
2579 END IF;
2580 l_return_status := Validate_Record(l_def_rgrv_rec);
2581 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2582 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2583 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2584 RAISE OKC_API.G_EXCEPTION_ERROR;
2585 END IF;
2586
2587 --------------------------------------
2588 -- Move VIEW record to "Child" records
2589 --------------------------------------
2590 migrate(l_def_rgrv_rec, l_rgr_rec);
2591 --------------------------------------------
2592 -- Call the UPDATE_ROW for each child record
2593 --------------------------------------------
2594 update_row(
2595 p_init_msg_list,
2596 x_return_status,
2597 x_msg_count,
2598 x_msg_data,
2599 l_rgr_rec,
2600 lx_rgr_rec
2601 );
2602 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2603 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2604 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2605 RAISE OKC_API.G_EXCEPTION_ERROR;
2606 END IF;
2607 migrate(lx_rgr_rec, l_def_rgrv_rec);
2608 x_rgrv_rec := l_def_rgrv_rec;
2609 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2610
2611 IF (l_debug = 'Y') THEN
2612 okc_debug.log('10100: Leaving update_row', 2);
2613 okc_debug.Reset_Indentation;
2614 END IF;
2615
2616 EXCEPTION
2617 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2618
2619 IF (l_debug = 'Y') THEN
2620 okc_debug.log('10200: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2621 okc_debug.Reset_Indentation;
2622 END IF;
2623
2624 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2625 (
2626 l_api_name,
2627 G_PKG_NAME,
2628 'OKC_API.G_RET_STS_ERROR',
2629 x_msg_count,
2630 x_msg_data,
2631 '_PVT'
2632 );
2633 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2634
2635 IF (l_debug = 'Y') THEN
2636 okc_debug.log('10300: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2637 okc_debug.Reset_Indentation;
2638 END IF;
2639
2640 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2641 (
2642 l_api_name,
2643 G_PKG_NAME,
2644 'OKC_API.G_RET_STS_UNEXP_ERROR',
2645 x_msg_count,
2646 x_msg_data,
2647 '_PVT'
2648 );
2649 WHEN OTHERS THEN
2650
2651 IF (l_debug = 'Y') THEN
2652 okc_debug.log('10400: Exiting update_row:OTHERS Exception', 2);
2653 okc_debug.Reset_Indentation;
2654 END IF;
2655
2656 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2657 (
2658 l_api_name,
2659 G_PKG_NAME,
2660 'OTHERS',
2661 x_msg_count,
2662 x_msg_data,
2663 '_PVT'
2664 );
2665
2666 END update_row;
2667 ----------------------------------------
2668 -- PL/SQL TBL update_row for:RGRV_TBL --
2669 ----------------------------------------
2670 PROCEDURE update_row(
2671 p_api_version IN NUMBER,
2672 p_init_msg_list IN VARCHAR2,
2673 x_return_status OUT NOCOPY VARCHAR2,
2674 x_msg_count OUT NOCOPY NUMBER,
2675 x_msg_data OUT NOCOPY VARCHAR2,
2676 p_rgrv_tbl IN rgrv_tbl_type,
2677 x_rgrv_tbl OUT NOCOPY rgrv_tbl_type) IS
2678
2679 l_api_version CONSTANT NUMBER := 1;
2680 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2681 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2682 i NUMBER := 0;
2683 BEGIN
2684
2685 IF (l_debug = 'Y') THEN
2686 okc_debug.Set_Indentation('OKC_RGR_PVT');
2687 okc_debug.log('10500: Entered update_row', 2);
2688 END IF;
2689
2690 OKC_API.init_msg_list(p_init_msg_list);
2691 -- Make sure PL/SQL table has records in it before passing
2692 IF (p_rgrv_tbl.COUNT > 0) THEN
2693 i := p_rgrv_tbl.FIRST;
2694 LOOP
2695 update_row (
2696 p_api_version => p_api_version,
2697 p_init_msg_list => OKC_API.G_FALSE,
2698 x_return_status => x_return_status,
2699 x_msg_count => x_msg_count,
2700 x_msg_data => x_msg_data,
2701 p_rgrv_rec => p_rgrv_tbl(i),
2702 x_rgrv_rec => x_rgrv_tbl(i));
2703 EXIT WHEN (i = p_rgrv_tbl.LAST);
2704 i := p_rgrv_tbl.NEXT(i);
2705 END LOOP;
2706 END IF;
2707
2708 IF (l_debug = 'Y') THEN
2709 okc_debug.log('10600: Leaving update_row', 2);
2710 okc_debug.Reset_Indentation;
2711 END IF;
2712
2713 EXCEPTION
2714 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2715
2716 IF (l_debug = 'Y') THEN
2717 okc_debug.log('10700: Exiting update_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2718 okc_debug.Reset_Indentation;
2719 END IF;
2720
2721 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2722 (
2723 l_api_name,
2724 G_PKG_NAME,
2725 'OKC_API.G_RET_STS_ERROR',
2726 x_msg_count,
2727 x_msg_data,
2728 '_PVT'
2729 );
2730 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2731
2732 IF (l_debug = 'Y') THEN
2733 okc_debug.log('10800: Exiting update_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2734 okc_debug.Reset_Indentation;
2735 END IF;
2736
2737 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2738 (
2739 l_api_name,
2740 G_PKG_NAME,
2741 'OKC_API.G_RET_STS_UNEXP_ERROR',
2742 x_msg_count,
2743 x_msg_data,
2744 '_PVT'
2745 );
2746 WHEN OTHERS THEN
2747
2748 IF (l_debug = 'Y') THEN
2749 okc_debug.log('10900: Exiting update_row:OTHERS Exception', 2);
2750 okc_debug.Reset_Indentation;
2751 END IF;
2752
2753 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2754 (
2755 l_api_name,
2756 G_PKG_NAME,
2757 'OTHERS',
2758 x_msg_count,
2759 x_msg_data,
2760 '_PVT'
2761 );
2762
2763 END update_row;
2764
2765 ---------------------------------------------------------------------------
2766 -- PROCEDURE delete_row
2767 ---------------------------------------------------------------------------
2768 -------------------------------------
2769 -- delete_row for:OKC_RG_DEF_RULES --
2770 -------------------------------------
2771 PROCEDURE delete_row(
2772 p_init_msg_list IN VARCHAR2,
2773 x_return_status OUT NOCOPY VARCHAR2,
2774 x_msg_count OUT NOCOPY NUMBER,
2775 x_msg_data OUT NOCOPY VARCHAR2,
2776 p_rgr_rec IN rgr_rec_type) IS
2777
2778 l_api_version CONSTANT NUMBER := 1;
2779 l_api_name CONSTANT VARCHAR2(30) := 'RULES_delete_row';
2780 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2781 l_rgr_rec rgr_rec_type:= p_rgr_rec;
2782 l_row_notfound BOOLEAN := TRUE;
2783 BEGIN
2784
2785 IF (l_debug = 'Y') THEN
2786 okc_debug.Set_Indentation('OKC_RGR_PVT');
2787 okc_debug.log('11000: Entered delete_row', 2);
2788 END IF;
2789
2790 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2791 p_init_msg_list,
2792 '_PVT',
2793 x_return_status);
2794 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2795 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2796 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2797 RAISE OKC_API.G_EXCEPTION_ERROR;
2798 END IF;
2799 DELETE FROM OKC_RG_DEF_RULES
2800 WHERE RGD_CODE = l_rgr_rec.rgd_code AND
2801 RDF_CODE = l_rgr_rec.rdf_code;
2802
2803 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2804
2805 IF (l_debug = 'Y') THEN
2806 okc_debug.log('11100: Leaving delete_row', 2);
2807 okc_debug.Reset_Indentation;
2808 END IF;
2809
2810 EXCEPTION
2811 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2812
2813 IF (l_debug = 'Y') THEN
2814 okc_debug.log('11200: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2815 okc_debug.Reset_Indentation;
2816 END IF;
2817
2818 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2819 (
2820 l_api_name,
2821 G_PKG_NAME,
2822 'OKC_API.G_RET_STS_ERROR',
2823 x_msg_count,
2824 x_msg_data,
2825 '_PVT'
2826 );
2827 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2828
2829 IF (l_debug = 'Y') THEN
2830 okc_debug.log('11300: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2831 okc_debug.Reset_Indentation;
2832 END IF;
2833
2834 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2835 (
2836 l_api_name,
2837 G_PKG_NAME,
2838 'OKC_API.G_RET_STS_UNEXP_ERROR',
2839 x_msg_count,
2840 x_msg_data,
2841 '_PVT'
2842 );
2843 WHEN OTHERS THEN
2844
2845 IF (l_debug = 'Y') THEN
2846 okc_debug.log('11400: Exiting delete_row:OTHERS Exception', 2);
2847 okc_debug.Reset_Indentation;
2848 END IF;
2849
2850 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2851 (
2852 l_api_name,
2853 G_PKG_NAME,
2854 'OTHERS',
2855 x_msg_count,
2856 x_msg_data,
2857 '_PVT'
2858 );
2859
2860 END delete_row;
2861 ---------------------------------------
2862 -- delete_row for:OKC_RG_DEF_RULES_V --
2863 ---------------------------------------
2864 PROCEDURE delete_row(
2865 p_api_version IN NUMBER,
2866 p_init_msg_list IN VARCHAR2,
2867 x_return_status OUT NOCOPY VARCHAR2,
2868 x_msg_count OUT NOCOPY NUMBER,
2869 x_msg_data OUT NOCOPY VARCHAR2,
2870 p_rgrv_rec IN rgrv_rec_type) IS
2871
2872 l_api_version CONSTANT NUMBER := 1;
2873 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2874 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2875 l_rgrv_rec rgrv_rec_type := p_rgrv_rec;
2876 l_rgr_rec rgr_rec_type;
2877 BEGIN
2878
2879 IF (l_debug = 'Y') THEN
2880 okc_debug.Set_Indentation('OKC_RGR_PVT');
2881 okc_debug.log('11500: Entered delete_row', 2);
2882 END IF;
2883
2884 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2885 G_PKG_NAME,
2886 p_init_msg_list,
2887 l_api_version,
2888 p_api_version,
2889 '_PVT',
2890 x_return_status);
2891 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2892 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2893 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2894 RAISE OKC_API.G_EXCEPTION_ERROR;
2895 END IF;
2896 --------------------------------------
2897 -- Move VIEW record to "Child" records
2898 --------------------------------------
2899 migrate(l_rgrv_rec, l_rgr_rec);
2900 --------------------------------------------
2901 -- Call the DELETE_ROW for each child record
2902 --------------------------------------------
2903 delete_row(
2904 p_init_msg_list,
2905 x_return_status,
2906 x_msg_count,
2907 x_msg_data,
2908 l_rgr_rec
2909 );
2910 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2911 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2912 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2913 RAISE OKC_API.G_EXCEPTION_ERROR;
2914 END IF;
2915 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2916
2917 IF (l_debug = 'Y') THEN
2918 okc_debug.log('11600: Leaving delete_row', 2);
2919 okc_debug.Reset_Indentation;
2920 END IF;
2921
2922 EXCEPTION
2923 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2924
2925 IF (l_debug = 'Y') THEN
2926 okc_debug.log('11700: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
2927 okc_debug.Reset_Indentation;
2928 END IF;
2929
2930 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2931 (
2932 l_api_name,
2933 G_PKG_NAME,
2934 'OKC_API.G_RET_STS_ERROR',
2935 x_msg_count,
2936 x_msg_data,
2937 '_PVT'
2938 );
2939 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2940
2941 IF (l_debug = 'Y') THEN
2942 okc_debug.log('11800: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
2943 okc_debug.Reset_Indentation;
2944 END IF;
2945
2946 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2947 (
2948 l_api_name,
2949 G_PKG_NAME,
2950 'OKC_API.G_RET_STS_UNEXP_ERROR',
2951 x_msg_count,
2952 x_msg_data,
2953 '_PVT'
2954 );
2955 WHEN OTHERS THEN
2956
2957 IF (l_debug = 'Y') THEN
2958 okc_debug.log('11900: Exiting delete_row:OTHERS Exception', 2);
2959 okc_debug.Reset_Indentation;
2960 END IF;
2961
2962 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2963 (
2964 l_api_name,
2965 G_PKG_NAME,
2966 'OTHERS',
2967 x_msg_count,
2968 x_msg_data,
2969 '_PVT'
2970 );
2971
2972 END delete_row;
2973 ----------------------------------------
2974 -- PL/SQL TBL delete_row for:RGRV_TBL --
2975 ----------------------------------------
2976 PROCEDURE delete_row(
2977 p_api_version IN NUMBER,
2978 p_init_msg_list IN VARCHAR2,
2979 x_return_status OUT NOCOPY VARCHAR2,
2980 x_msg_count OUT NOCOPY NUMBER,
2981 x_msg_data OUT NOCOPY VARCHAR2,
2982 p_rgrv_tbl IN rgrv_tbl_type) IS
2983
2984 l_api_version CONSTANT NUMBER := 1;
2985 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2986 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2987 i NUMBER := 0;
2988 BEGIN
2989
2990 IF (l_debug = 'Y') THEN
2991 okc_debug.Set_Indentation('OKC_RGR_PVT');
2992 okc_debug.log('12000: Entered delete_row', 2);
2993 END IF;
2994
2995 OKC_API.init_msg_list(p_init_msg_list);
2996 -- Make sure PL/SQL table has records in it before passing
2997 IF (p_rgrv_tbl.COUNT > 0) THEN
2998 i := p_rgrv_tbl.FIRST;
2999 LOOP
3000 delete_row (
3001 p_api_version => p_api_version,
3002 p_init_msg_list => OKC_API.G_FALSE,
3003 x_return_status => x_return_status,
3004 x_msg_count => x_msg_count,
3005 x_msg_data => x_msg_data,
3006 p_rgrv_rec => p_rgrv_tbl(i));
3007 EXIT WHEN (i = p_rgrv_tbl.LAST);
3008 i := p_rgrv_tbl.NEXT(i);
3009 END LOOP;
3010 END IF;
3011
3012 IF (l_debug = 'Y') THEN
3013 okc_debug.log('12100: Leaving delete_row', 2);
3014 okc_debug.Reset_Indentation;
3015 END IF;
3016
3017 EXCEPTION
3018 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3019
3020 IF (l_debug = 'Y') THEN
3021 okc_debug.log('12200: Exiting delete_row:OKC_API.G_EXCEPTION_ERROR Exception', 2);
3022 okc_debug.Reset_Indentation;
3023 END IF;
3024
3025 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3026 (
3027 l_api_name,
3028 G_PKG_NAME,
3029 'OKC_API.G_RET_STS_ERROR',
3030 x_msg_count,
3031 x_msg_data,
3032 '_PVT'
3033 );
3034 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3035
3036 IF (l_debug = 'Y') THEN
3037 okc_debug.log('12300: Exiting delete_row:OKC_API.G_EXCEPTION_UNEXPECTED_ERROR Exception', 2);
3038 okc_debug.Reset_Indentation;
3039 END IF;
3040
3041 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3042 (
3043 l_api_name,
3044 G_PKG_NAME,
3045 'OKC_API.G_RET_STS_UNEXP_ERROR',
3046 x_msg_count,
3047 x_msg_data,
3048 '_PVT'
3049 );
3050 WHEN OTHERS THEN
3051
3052 IF (l_debug = 'Y') THEN
3053 okc_debug.log('12400: Exiting delete_row:OTHERS Exception', 2);
3054 okc_debug.Reset_Indentation;
3055 END IF;
3056
3057 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3058 (
3059 l_api_name,
3060 G_PKG_NAME,
3061 'OTHERS',
3062 x_msg_count,
3063 x_msg_data,
3064 '_PVT'
3065 );
3066
3067 END delete_row;
3068
3069 END OKC_RGR_PVT;