[Home] [Help]
PACKAGE BODY: APPS.OKL_ORL_PVT
Source
1 PACKAGE BODY OKL_ORL_PVT AS
2 /* $Header: OKLSORLB.pls 115.9 2002/12/18 13:01:14 kjinger noship $ */
3 ---------------------------------------------------------------------------
4 -- FUNCTION get_seq_id
5 ---------------------------------------------------------------------------
6 FUNCTION get_seq_id RETURN NUMBER IS
7 BEGIN
8 RETURN(okc_p_util.raw_to_number(sys_guid()));
9 END get_seq_id;
10
11 ---------------------------------------------------------------------------
12 -- PROCEDURE qc
13 ---------------------------------------------------------------------------
14 PROCEDURE qc IS
15 BEGIN
16 null;
17 END qc;
18
19 ---------------------------------------------------------------------------
20 -- PROCEDURE change_version
21 ---------------------------------------------------------------------------
22 PROCEDURE change_version IS
23 BEGIN
24 null;
25 END change_version;
26
27 ---------------------------------------------------------------------------
28 -- PROCEDURE api_copy
29 ---------------------------------------------------------------------------
30 PROCEDURE api_copy IS
31 BEGIN
32 null;
33 END api_copy;
34
35 ---------------------------------------------------------------------------
36 -- FUNCTION get_rec for: OKL_OPT_RULES
37 ---------------------------------------------------------------------------
38 FUNCTION get_rec (
39 p_orl_rec IN orl_rec_type,
40 x_no_data_found OUT NOCOPY BOOLEAN
41 ) RETURN orl_rec_type IS
42 CURSOR okl_opt_rules_pk_csr (p_id IN NUMBER) IS
43 SELECT
44 ID,
45 OPT_ID,
46 RGR_RGD_CODE,
47 RGR_RDF_CODE,
48 SRD_ID_FOR,
49 LRG_LSE_ID,
50 LRG_SRD_ID,
51 OBJECT_VERSION_NUMBER,
52 CREATED_BY,
53 CREATION_DATE,
54 LAST_UPDATED_BY,
55 LAST_UPDATE_DATE,
56 OVERALL_INSTRUCTIONS,
57 LAST_UPDATE_LOGIN
58 FROM Okl_Opt_Rules
59 WHERE okl_opt_rules.id = p_id;
60 l_okl_opt_rules_pk okl_opt_rules_pk_csr%ROWTYPE;
61 l_orl_rec orl_rec_type;
62 BEGIN
63 x_no_data_found := TRUE;
64 -- Get current database values
65 OPEN okl_opt_rules_pk_csr (p_orl_rec.id);
66 FETCH okl_opt_rules_pk_csr INTO
67 l_orl_rec.ID,
68 l_orl_rec.OPT_ID,
69 l_orl_rec.RGR_RGD_CODE,
70 l_orl_rec.RGR_RDF_CODE,
71 l_orl_rec.SRD_ID_FOR,
72 l_orl_rec.LRG_LSE_ID,
73 l_orl_rec.LRG_SRD_ID,
74 l_orl_rec.OBJECT_VERSION_NUMBER,
75 l_orl_rec.CREATED_BY,
76 l_orl_rec.CREATION_DATE,
77 l_orl_rec.LAST_UPDATED_BY,
78 l_orl_rec.LAST_UPDATE_DATE,
79 l_orl_rec.OVERALL_INSTRUCTIONS,
80 l_orl_rec.LAST_UPDATE_LOGIN;
81 x_no_data_found := okl_opt_rules_pk_csr%NOTFOUND;
82 CLOSE okl_opt_rules_pk_csr;
83 RETURN(l_orl_rec);
84 END get_rec;
85
86 FUNCTION get_rec (
87 p_orl_rec IN orl_rec_type
88 ) RETURN orl_rec_type IS
89 l_row_notfound BOOLEAN := TRUE;
90 BEGIN
91 RETURN(get_rec(p_orl_rec, l_row_notfound));
92 END get_rec;
93 ---------------------------------------------------------------------------
94 -- FUNCTION get_rec for: OKL_OPT_RULES_V
95 ---------------------------------------------------------------------------
96 FUNCTION get_rec (
97 p_orlv_rec IN orlv_rec_type,
98 x_no_data_found OUT NOCOPY BOOLEAN
99 ) RETURN orlv_rec_type IS
100 CURSOR okl_orlv_pk_csr (p_id IN NUMBER) IS
101 SELECT
102 ID,
103 OBJECT_VERSION_NUMBER,
104 OPT_ID,
105 SRD_ID_FOR,
106 RGR_RGD_CODE,
107 RGR_RDF_CODE,
108 LRG_LSE_ID,
109 LRG_SRD_ID,
110 OVERALL_INSTRUCTIONS,
111 CREATED_BY,
112 CREATION_DATE,
113 LAST_UPDATED_BY,
114 LAST_UPDATE_DATE,
115 LAST_UPDATE_LOGIN
116 FROM Okl_Opt_Rules_V
117 WHERE okl_opt_rules_v.id = p_id;
118 l_okl_orlv_pk okl_orlv_pk_csr%ROWTYPE;
119 l_orlv_rec orlv_rec_type;
120 BEGIN
121 x_no_data_found := TRUE;
122 -- Get current database values
123 OPEN okl_orlv_pk_csr (p_orlv_rec.id);
124 FETCH okl_orlv_pk_csr INTO
125 l_orlv_rec.ID,
126 l_orlv_rec.OBJECT_VERSION_NUMBER,
127 l_orlv_rec.OPT_ID,
128 l_orlv_rec.SRD_ID_FOR,
129 l_orlv_rec.RGR_RGD_CODE,
130 l_orlv_rec.RGR_RDF_CODE,
131 l_orlv_rec.LRG_LSE_ID,
132 l_orlv_rec.LRG_SRD_ID,
133 l_orlv_rec.OVERALL_INSTRUCTIONS,
134 l_orlv_rec.CREATED_BY,
135 l_orlv_rec.CREATION_DATE,
136 l_orlv_rec.LAST_UPDATED_BY,
137 l_orlv_rec.LAST_UPDATE_DATE,
138 l_orlv_rec.LAST_UPDATE_LOGIN;
139 x_no_data_found := okl_orlv_pk_csr%NOTFOUND;
140 CLOSE okl_orlv_pk_csr;
141 RETURN(l_orlv_rec);
142 END get_rec;
143
144 FUNCTION get_rec (
145 p_orlv_rec IN orlv_rec_type
146 ) RETURN orlv_rec_type IS
147 l_row_notfound BOOLEAN := TRUE;
148 BEGIN
149 RETURN(get_rec(p_orlv_rec, l_row_notfound));
150 END get_rec;
151
152 -----------------------------------------------------
153 -- FUNCTION null_out_defaults for: OKL_OPT_RULES_V --
154 -----------------------------------------------------
155 FUNCTION null_out_defaults (
156 p_orlv_rec IN orlv_rec_type
157 ) RETURN orlv_rec_type IS
158 l_orlv_rec orlv_rec_type := p_orlv_rec;
159 BEGIN
160 IF (l_orlv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
161 l_orlv_rec.object_version_number := NULL;
162 END IF;
163 IF (l_orlv_rec.opt_id = OKC_API.G_MISS_NUM) THEN
164 l_orlv_rec.opt_id := NULL;
165 END IF;
166 IF (l_orlv_rec.srd_id_for = OKC_API.G_MISS_NUM) THEN
167 l_orlv_rec.srd_id_for := NULL;
168 END IF;
169 IF (l_orlv_rec.rgr_rgd_code = OKC_API.G_MISS_CHAR) THEN
170 l_orlv_rec.rgr_rgd_code := NULL;
171 END IF;
172 IF (l_orlv_rec.rgr_rdf_code = OKC_API.G_MISS_CHAR) THEN
173 l_orlv_rec.rgr_rdf_code := NULL;
174 END IF;
175 IF (l_orlv_rec.lrg_lse_id = OKC_API.G_MISS_NUM) THEN
176 l_orlv_rec.lrg_lse_id := NULL;
177 END IF;
178 IF (l_orlv_rec.lrg_srd_id = OKC_API.G_MISS_NUM) THEN
179 l_orlv_rec.lrg_srd_id := NULL;
180 END IF;
181 IF (l_orlv_rec.overall_instructions = OKC_API.G_MISS_CHAR) THEN
182 l_orlv_rec.overall_instructions := NULL;
183 END IF;
184 IF (l_orlv_rec.created_by = OKC_API.G_MISS_NUM) THEN
185 l_orlv_rec.created_by := NULL;
186 END IF;
187 IF (l_orlv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
188 l_orlv_rec.creation_date := NULL;
189 END IF;
190 IF (l_orlv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
191 l_orlv_rec.last_updated_by := NULL;
192 END IF;
193 IF (l_orlv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
194 l_orlv_rec.last_update_date := NULL;
195 END IF;
196 IF (l_orlv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
197 l_orlv_rec.last_update_login := NULL;
198 END IF;
199 RETURN(l_orlv_rec);
200 END null_out_defaults;
201
202 /**********************TCHGS: Old Code******************************************
203 ---------------------------------------------------------------------------
204 -- PROCEDURE Validate_Attributes
205 ---------------------------------------------------------------------------
206 ---------------------------------------------
207 -- Validate_Attributes for:OKL_OPT_RULES_V --
208 ---------------------------------------------
209 FUNCTION Validate_Attributes (
210 p_orlv_rec IN orlv_rec_type
211 ) RETURN VARCHAR2 IS
212 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
213 BEGIN
214 IF p_orlv_rec.id = OKC_API.G_MISS_NUM OR
215 p_orlv_rec.id IS NULL
216 THEN
217 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
218 l_return_status := OKC_API.G_RET_STS_ERROR;
219 ELSIF p_orlv_rec.object_version_number = OKC_API.G_MISS_NUM OR
220 p_orlv_rec.object_version_number IS NULL
221 THEN
222 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
223 l_return_status := OKC_API.G_RET_STS_ERROR;
224 ELSIF p_orlv_rec.opt_id = OKC_API.G_MISS_NUM OR
225 p_orlv_rec.opt_id IS NULL
226 THEN
227 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'opt_id');
228 l_return_status := OKC_API.G_RET_STS_ERROR;
229 ELSIF p_orlv_rec.srd_id_for = OKC_API.G_MISS_NUM OR
230 p_orlv_rec.srd_id_for IS NULL
231 THEN
232 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'srd_id_for');
233 l_return_status := OKC_API.G_RET_STS_ERROR;
234 ELSIF p_orlv_rec.rgr_rgd_code = OKC_API.G_MISS_CHAR OR
235 p_orlv_rec.rgr_rgd_code IS NULL
236 THEN
237 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'rgr_rgd_code');
238 l_return_status := OKC_API.G_RET_STS_ERROR;
239 ELSIF p_orlv_rec.rgr_rdf_code = OKC_API.G_MISS_CHAR OR
240 p_orlv_rec.rgr_rdf_code IS NULL
241 THEN
242 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'rgr_rdf_code');
243 l_return_status := OKC_API.G_RET_STS_ERROR;
244 ELSIF p_orlv_rec.lrg_lse_id = OKC_API.G_MISS_NUM OR
245 p_orlv_rec.lrg_lse_id IS NULL
246 THEN
247 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'lrg_lse_id');
248 l_return_status := OKC_API.G_RET_STS_ERROR;
249 ELSIF p_orlv_rec.lrg_srd_id = OKC_API.G_MISS_NUM OR
250 p_orlv_rec.lrg_srd_id IS NULL
251 THEN
252 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'lrg_srd_id');
253 l_return_status := OKC_API.G_RET_STS_ERROR;
254 END IF;
255 RETURN(l_return_status);
256 END Validate_Attributes;
257
258 ---------------------------------------------------------------------------
259 -- PROCEDURE Validate_Record
260 ---------------------------------------------------------------------------
261 -----------------------------------------
262 -- Validate_Record for:OKL_OPT_RULES_V --
263 -----------------------------------------
264 FUNCTION Validate_Record (
265 p_orlv_rec IN orlv_rec_type
266 ) RETURN VARCHAR2 IS
267 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
268 BEGIN
269 RETURN (l_return_status);
270 END Validate_Record;
271 **********************TCHGS: Old Code******************************************/
272
273 /************************** TCHGS: Start New Code *****************************/
274 ---------------------------------------------------------------------------
275 -- PROCEDURE Validate_Id
276 ---------------------------------------------------------------------------
277 -- Start of comments
278 --
279 -- Procedure Name : Validate_Id
280 -- Description :
281 -- Business Rules :
282 -- Parameters :
283 -- Version : 1.0
284 -- End of comments
285 ---------------------------------------------------------------------------
286 PROCEDURE Validate_Id(p_orlv_rec IN orlv_rec_type
287 ,x_return_status OUT NOCOPY VARCHAR2)
288 IS
289
290 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
291
292 BEGIN
293 -- initialize return status
294 x_return_status := OKC_API.G_RET_STS_SUCCESS;
295
296 -- check for data before processing
297 IF (p_orlv_rec.id IS NULL) OR
298 (p_orlv_rec.id = OKC_API.G_MISS_NUM) THEN
299 OKC_API.SET_MESSAGE(p_app_name => g_app_name
300 ,p_msg_name => g_required_value
301 ,p_token1 => g_col_name_token
302 ,p_token1_value => 'id');
303 x_return_status := OKC_API.G_RET_STS_ERROR;
304 RAISE G_EXCEPTION_HALT_VALIDATION;
305 END IF;
306
307 EXCEPTION
308 WHEN G_EXCEPTION_HALT_VALIDATION THEN
309 -- no processing necessary; validation can continue
310 -- with the next column
311 NULL;
312
313 WHEN OTHERS THEN
314 -- store SQL error message on message stack for caller
315 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
316 p_msg_name => g_unexpected_error,
317 p_token1 => g_sqlcode_token,
318 p_token1_value => sqlcode,
319 p_token2 => g_sqlerrm_token,
320 p_token2_value => sqlerrm);
321
322 -- notify caller of an UNEXPECTED error
323 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
324
325 END Validate_Id;
326
327 ---------------------------------------------------------------------------
328 -- PROCEDURE Validate_Opt_Id
329 ---------------------------------------------------------------------------
330 -- Start of comments
331 --
332 -- Procedure Name : Validate_Opt_Id
333 -- Description :
334 -- Business Rules :
335 -- Parameters :
336 -- Version : 1.0
337 -- End of comments
338 ---------------------------------------------------------------------------
339 PROCEDURE Validate_Opt_Id(p_orlv_rec IN orlv_rec_type
340 ,x_return_status OUT NOCOPY VARCHAR2)
341 IS
342 CURSOR okl_optv_pk_csr (p_id IN NUMBER) IS
343 SELECT '1'
344 FROM okl_options_v
345 WHERE okl_options_v.id = p_id;
346
347 l_opt_status VARCHAR2(1);
348 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
349 l_row_notfound BOOLEAN := TRUE;
350
351 BEGIN
352 -- initialize return status
353 x_return_status := OKC_API.G_RET_STS_SUCCESS;
354
355 -- check for data before processing
356 IF (p_orlv_rec.opt_id IS NULL) OR
357 (p_orlv_rec.opt_id = OKC_API.G_MISS_NUM) THEN
358 OKC_API.SET_MESSAGE(p_app_name => g_app_name
359 ,p_msg_name => g_required_value
360 ,p_token1 => g_col_name_token
361 ,p_token1_value => 'opt_id');
362 x_return_status := OKC_API.G_RET_STS_ERROR;
363 RAISE G_EXCEPTION_HALT_VALIDATION;
364 END IF;
365
366 IF (p_orlv_rec.OPT_ID IS NOT NULL)
367 THEN
368 OPEN okl_optv_pk_csr(p_orlv_rec.OPT_ID);
369 FETCH okl_optv_pk_csr INTO l_opt_status;
370 l_row_notfound := okl_optv_pk_csr%NOTFOUND;
371 CLOSE okl_optv_pk_csr;
372 IF (l_row_notfound) THEN
373 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'OPT_ID');
374 RAISE G_ITEM_NOT_FOUND_ERROR;
375 END IF;
376 END IF;
377
378
379 EXCEPTION
380 WHEN G_EXCEPTION_HALT_VALIDATION THEN
381 -- no processing necessary; validation can continue
382 -- with the next column
383 NULL;
384 WHEN G_ITEM_NOT_FOUND_ERROR THEN
385 l_return_status := OKC_API.G_RET_STS_ERROR;
386
387 WHEN OTHERS THEN
388 -- store SQL error message on message stack for caller
389 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
390 p_msg_name => g_unexpected_error,
391 p_token1 => g_sqlcode_token,
392 p_token1_value => sqlcode,
393 p_token2 => g_sqlerrm_token,
394 p_token2_value => sqlerrm);
395
396 -- notify caller of an UNEXPECTED error
397 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
398
399 END Validate_Opt_Id;
400
401 ---------------------------------------------------------------------------
402 -- PROCEDURE Validate_Srd_Id_For
403 ---------------------------------------------------------------------------
404 -- Start of comments
405 --
406 -- Procedure Name : Validate_Srd_Id_For
407 -- Description :
408 -- Business Rules :
409 -- Parameters :
410 -- Version : 1.0
411 -- End of comments
412 ---------------------------------------------------------------------------
413 PROCEDURE Validate_Srd_Id_For(p_orlv_rec IN orlv_rec_type
414 ,x_return_status OUT NOCOPY VARCHAR2)
415 IS
416 CURSOR okl_srdv_pk_csr (p_id IN NUMBER) IS
417 SELECT '1'
418 FROM okc_subclass_rg_defs_v
419 WHERE okc_subclass_rg_defs_v.id = p_id;
420
421 l_srd_status VARCHAR2(1);
422 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
423 l_row_notfound BOOLEAN := TRUE;
424
425 BEGIN
426 -- initialize return status
427 x_return_status := OKC_API.G_RET_STS_SUCCESS;
428
429 -- check for data before processing
430 IF (p_orlv_rec.srd_id_for IS NULL) OR
431 (p_orlv_rec.srd_id_for = OKC_API.G_MISS_NUM) THEN
432 OKC_API.SET_MESSAGE(p_app_name => g_app_name
433 ,p_msg_name => g_required_value
434 ,p_token1 => g_col_name_token
435 ,p_token1_value => 'srd_id_for');
436 x_return_status := OKC_API.G_RET_STS_ERROR;
437 RAISE G_EXCEPTION_HALT_VALIDATION;
438 END IF;
439 IF (p_orlv_rec.SRD_ID_FOR IS NOT NULL)
440 THEN
441 OPEN okl_srdv_pk_csr(p_orlv_rec.SRD_ID_FOR);
442 FETCH okl_srdv_pk_csr INTO l_srd_status;
443 l_row_notfound := okl_srdv_pk_csr%NOTFOUND;
444 CLOSE okl_srdv_pk_csr;
445 IF (l_row_notfound) THEN
446 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'SRD_ID_FOR');
447 RAISE G_ITEM_NOT_FOUND_ERROR;
448 END IF;
449 END IF;
450
451
452 EXCEPTION
453 WHEN G_EXCEPTION_HALT_VALIDATION THEN
454 -- no processing necessary; validation can continue
455 -- with the next column
456 NULL;
457 WHEN G_ITEM_NOT_FOUND_ERROR THEN
458 l_return_status := OKC_API.G_RET_STS_ERROR;
459
460 WHEN OTHERS THEN
461 -- store SQL error message on message stack for caller
462 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
463 p_msg_name => g_unexpected_error,
464 p_token1 => g_sqlcode_token,
465 p_token1_value => sqlcode,
466 p_token2 => g_sqlerrm_token,
467 p_token2_value => sqlerrm);
468
469 -- notify caller of an UNEXPECTED error
470 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
471
472 END Validate_Srd_Id_For;
473
474 ---------------------------------------------------------------------------
475 -- PROCEDURE Validate_Lrg_Lse_Id
476 ---------------------------------------------------------------------------
477 -- Start of comments
478 --
479 -- Procedure Name : Validate_Lrg_Lse_Id
480 -- Description :
481 -- Business Rules :
482 -- Parameters :
483 -- Version : 1.0
484 -- End of comments
485 ---------------------------------------------------------------------------
486 PROCEDURE Validate_Lrg_Lse_Id(p_orlv_rec IN orlv_rec_type
487 ,x_return_status OUT NOCOPY VARCHAR2)
488 IS
489
490 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
491
492 BEGIN
493 -- initialize return status
494 x_return_status := OKC_API.G_RET_STS_SUCCESS;
495
496 -- check for data before processing
497 IF (p_orlv_rec.lrg_lse_id IS NULL) OR
498 (p_orlv_rec.lrg_lse_id = OKC_API.G_MISS_NUM) THEN
499 OKC_API.SET_MESSAGE(p_app_name => g_app_name
500 ,p_msg_name => g_required_value
501 ,p_token1 => g_col_name_token
502 ,p_token1_value => 'lrg_lse_id');
503 x_return_status := OKC_API.G_RET_STS_ERROR;
504 RAISE G_EXCEPTION_HALT_VALIDATION;
505 END IF;
506
507 EXCEPTION
508 WHEN G_EXCEPTION_HALT_VALIDATION THEN
509 -- no processing necessary; validation can continue
510 -- with the next column
511 NULL;
512
513 WHEN OTHERS THEN
514 -- store SQL error message on message stack for caller
515 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
516 p_msg_name => g_unexpected_error,
517 p_token1 => g_sqlcode_token,
518 p_token1_value => sqlcode,
519 p_token2 => g_sqlerrm_token,
520 p_token2_value => sqlerrm);
521
522 -- notify caller of an UNEXPECTED error
523 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
524
525 END Validate_Lrg_Lse_Id;
526
527 ---------------------------------------------------------------------------
528 -- PROCEDURE Validate_Lrg_Srd_Id
529 ---------------------------------------------------------------------------
530 -- Start of comments
531 --
532 -- Procedure Name : Validate_Lrg_Srd_Id
533 -- Description :
534 -- Business Rules :
535 -- Parameters :
536 -- Version : 1.0
537 -- End of comments
538 ---------------------------------------------------------------------------
539 PROCEDURE Validate_Lrg_Srd_Id(p_orlv_rec IN orlv_rec_type
540 ,x_return_status OUT NOCOPY VARCHAR2)
541 IS
542
543 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
544
545 BEGIN
546 -- initialize return status
547 x_return_status := OKC_API.G_RET_STS_SUCCESS;
548
549 -- check for data before processing
550 IF (p_orlv_rec.lrg_lse_id IS NULL) OR
551 (p_orlv_rec.lrg_lse_id = OKC_API.G_MISS_NUM) THEN
552 OKC_API.SET_MESSAGE(p_app_name => g_app_name
553 ,p_msg_name => g_required_value
554 ,p_token1 => g_col_name_token
555 ,p_token1_value => 'lrg_srd_id');
556 x_return_status := OKC_API.G_RET_STS_ERROR;
557 RAISE G_EXCEPTION_HALT_VALIDATION;
558 END IF;
559
560 EXCEPTION
561 WHEN G_EXCEPTION_HALT_VALIDATION THEN
562 -- no processing necessary; validation can continue
563 -- with the next column
564 NULL;
565
566 WHEN OTHERS THEN
567 -- store SQL error message on message stack for caller
568 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
569 p_msg_name => g_unexpected_error,
570 p_token1 => g_sqlcode_token,
571 p_token1_value => sqlcode,
572 p_token2 => g_sqlerrm_token,
573 p_token2_value => sqlerrm);
574
575 -- notify caller of an UNEXPECTED error
576 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
577
578 END Validate_Lrg_Srd_Id;
579
580 ---------------------------------------------------------------------------
581 -- PROCEDURE Validate_Object_Version_Number
582 ---------------------------------------------------------------------------
583 -- Start of comments
584 --
585 -- Procedure Name : Validate_Object_Version_Number
586 -- Description :
587 -- Business Rules :
588 -- Parameters :
589 -- Version : 1.0
590 -- End of comments
591 ---------------------------------------------------------------------------
592 PROCEDURE Validate_Object_Version_Number(p_orlv_rec IN orlv_rec_type
593 ,x_return_status OUT NOCOPY VARCHAR2)
594 IS
595
596 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
597
598 BEGIN
599 -- initialize return status
600 x_return_status := OKC_API.G_RET_STS_SUCCESS;
601
602 -- check for data before processing
603 IF (p_orlv_rec.object_version_number IS NULL) OR
604 (p_orlv_rec.object_version_Number = OKC_API.G_MISS_NUM) THEN
605 OKC_API.SET_MESSAGE(p_app_name => g_app_name
606 ,p_msg_name => g_required_value
607 ,p_token1 => g_col_name_token
608 ,p_token1_value => 'object_version_number');
609 x_return_status := OKC_API.G_RET_STS_ERROR;
610 RAISE G_EXCEPTION_HALT_VALIDATION;
611 END IF;
612
613 EXCEPTION
614 WHEN G_EXCEPTION_HALT_VALIDATION THEN
615 -- no processing necessary; validation can continue
616 -- with the next column
617 NULL;
618
619 WHEN OTHERS THEN
620 -- store SQL error message on message stack for caller
621 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
622 p_msg_name => g_unexpected_error,
623 p_token1 => g_sqlcode_token,
624 p_token1_value => sqlcode,
625 p_token2 => g_sqlerrm_token,
626 p_token2_value => sqlerrm);
627
628 -- notify caller of an UNEXPECTED error
629 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
630
631 END Validate_Object_Version_Number;
632
633 ---------------------------------------------------------------------------
634 -- PROCEDURE Validate_Rgr_Rgd_Code
635 ---------------------------------------------------------------------------
636 -- Start of comments
637 --
638 -- Procedure Name : Validate_Rgr_Rgd_Code
639 -- Description :
640 -- Business Rules :
641 -- Parameters :
642 -- Version : 1.0
643 -- End of comments
644 ---------------------------------------------------------------------------
645 PROCEDURE Validate_Rgr_Rgd_Code(p_orlv_rec IN orlv_rec_type
646 ,x_return_status OUT NOCOPY VARCHAR2)
647 IS
648
649 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
650
651 BEGIN
652 -- initialize return status
653 x_return_status := OKC_API.G_RET_STS_SUCCESS;
654
655 -- check for data before processing
656 IF (p_orlv_rec.rgr_rgd_code IS NULL) OR
657 (p_orlv_rec.rgr_rgd_code = OKC_API.G_MISS_CHAR) THEN
658 OKC_API.SET_MESSAGE(p_app_name => g_app_name
659 ,p_msg_name => g_required_value
660 ,p_token1 => g_col_name_token
661 ,p_token1_value => 'rgr_rgd_code');
662 x_return_status := OKC_API.G_RET_STS_ERROR;
663 RAISE G_EXCEPTION_HALT_VALIDATION;
664 END IF;
665
666 EXCEPTION
667 WHEN G_EXCEPTION_HALT_VALIDATION THEN
668 -- no processing necessary; validation can continue
669 -- with the next column
670 NULL;
671
672 WHEN OTHERS THEN
673 -- store SQL error message on message stack for caller
674 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
675 p_msg_name => g_unexpected_error,
676 p_token1 => g_sqlcode_token,
677 p_token1_value => sqlcode,
678 p_token2 => g_sqlerrm_token,
679 p_token2_value => sqlerrm);
680
681 -- notify caller of an UNEXPECTED error
682 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
683
684 END Validate_Rgr_Rgd_Code;
685
686 ---------------------------------------------------------------------------
687 -- PROCEDURE Validate_Rgr_Rdf_Code
688 ---------------------------------------------------------------------------
689 -- Start of comments
690 --
691 -- Procedure Name : Validate_Rgr_Rdf_Code
692 -- Description :
693 -- Business Rules :
694 -- Parameters :
695 -- Version : 1.0
696 -- End of comments
697 ---------------------------------------------------------------------------
698 PROCEDURE Validate_Rgr_Rdf_Code(p_orlv_rec IN orlv_rec_type
699 ,x_return_status OUT NOCOPY VARCHAR2)
700 IS
701
702 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
703
704 BEGIN
705 -- initialize return status
706 x_return_status := OKC_API.G_RET_STS_SUCCESS;
707
708 -- check for data before processing
709 IF (p_orlv_rec.rgr_rdf_code IS NULL) OR
710 (p_orlv_rec.rgr_rdf_code = OKC_API.G_MISS_CHAR) THEN
711 OKC_API.SET_MESSAGE(p_app_name => g_app_name
712 ,p_msg_name => g_required_value
713 ,p_token1 => g_col_name_token
714 ,p_token1_value => 'rgr_rdf_code');
715 x_return_status := OKC_API.G_RET_STS_ERROR;
716 RAISE G_EXCEPTION_HALT_VALIDATION;
717 END IF;
718
719 EXCEPTION
720 WHEN G_EXCEPTION_HALT_VALIDATION THEN
721 -- no processing necessary; validation can continue
722 -- with the next column
723 NULL;
724
725 WHEN OTHERS THEN
726 -- store SQL error message on message stack for caller
727 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
728 p_msg_name => g_unexpected_error,
729 p_token1 => g_sqlcode_token,
730 p_token1_value => sqlcode,
731 p_token2 => g_sqlerrm_token,
732 p_token2_value => sqlerrm);
733
734 -- notify caller of an UNEXPECTED error
735 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
736
737 END Validate_Rgr_Rdf_Code;
738
739 ---------------------------------------------------------------------------
740 -- FUNCTION Validate_Foreign_Keys
741 ---------------------------------------------------------------------------
742 -- Start of comments
743 --
744 -- Function Name : Validate_Foreign_Keys
745 -- Description :
746 -- Business Rules :
747 -- Parameters :
748 -- Version : 1.0
749 -- End of comments
750 ---------------------------------------------------------------------------
751 FUNCTION Validate_Foreign_Keys (
752 p_orlv_rec IN orlv_rec_type
753 ) RETURN VARCHAR2 IS
754 item_not_found_error EXCEPTION;
755
756 CURSOR okl_rgrv_pk_csr (p_rgd_code IN VARCHAR2,
757 p_rdf_code IN VARCHAR2) IS
758 SELECT '1'
759 FROM okc_rg_def_rules_v
760 WHERE okc_rg_def_rules_v.rgd_code = p_rgd_code
761 AND okc_rg_def_rules_v.rdf_code = p_rdf_code;
762
763 CURSOR okl_lrgv_pk_csr (p_lse_id IN NUMBER,
764 p_srd_id IN NUMBER) IS
765 SELECT '1'
766 FROM okc_lse_rule_groups_v
767 WHERE okc_lse_rule_groups_v.lse_id = p_lse_id
768 AND okc_lse_rule_groups_v.srd_id = p_srd_id;
769
770 l_rgr_status VARCHAR2(1);
771 l_lrg_status VARCHAR2(1);
772 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
773 l_row_notfound BOOLEAN := TRUE;
774
775 BEGIN
776
777 IF (p_orlv_rec.RGR_RGD_CODE IS NOT NULL AND p_orlv_rec.RGR_RDF_CODE IS NOT NULL)
778 THEN
779 OPEN okl_rgrv_pk_csr(p_orlv_rec.RGR_RGD_CODE, p_orlv_rec.RGR_RDF_CODE);
780 FETCH okl_rgrv_pk_csr INTO l_rgr_status;
781 l_row_notfound := okl_rgrv_pk_csr%NOTFOUND;
782 CLOSE okl_rgrv_pk_csr;
783 IF (l_row_notfound) THEN
784 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'RGR_RGD_CODE');
785 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'RGR_RDF_CODE');
786 RAISE G_ITEM_NOT_FOUND_ERROR;
787 END IF;
788 END IF;
789
790 IF (p_orlv_rec.LRG_LSE_ID IS NOT NULL AND p_orlv_rec.LRG_SRD_ID IS NOT NULL)
791 THEN
792 OPEN okl_lrgv_pk_csr(p_orlv_rec.LRG_LSE_ID, p_orlv_rec.LRG_SRD_ID);
793 FETCH okl_lrgv_pk_csr INTO l_lrg_status;
794 l_row_notfound := okl_lrgv_pk_csr%NOTFOUND;
795 CLOSE okl_lrgv_pk_csr;
796 IF (l_row_notfound) THEN
797 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'LRG_LSE_ID');
798 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'LRG_SRD_ID');
799 RAISE G_ITEM_NOT_FOUND_ERROR;
800 END IF;
801 END IF;
802
803 RETURN (l_return_status);
804 EXCEPTION
805 WHEN G_ITEM_NOT_FOUND_ERROR THEN
806 l_return_status := OKC_API.G_RET_STS_ERROR;
807 RETURN (l_return_status);
808 END Validate_Foreign_Keys;
809
810 ---------------------------------------------------------------------------
811 -- FUNCTION Validate_Attributes
812 ---------------------------------------------------------------------------
813 -- Start of comments
814 --
815 -- Function Name : Validate_Attributes
816 -- Description :
817 -- Business Rules :
818 -- Parameters :
819 -- Version : 1.0
820 -- End of comments
821 ---------------------------------------------------------------------------
822
823 FUNCTION Validate_Attributes (
824 p_orlv_rec IN orlv_rec_type
825 ) RETURN VARCHAR2 IS
826 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
827 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
828 BEGIN
829
830 -- Validate_Id
831 Validate_Id(p_orlv_rec, x_return_status);
832 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
833 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
834 -- need to leave
835 l_return_status := x_return_status;
836 RAISE G_EXCEPTION_HALT_VALIDATION;
837 ELSE
838 -- record that there was an error
839 l_return_status := x_return_status;
840 END IF;
841 END IF;
842
843 -- Validate_Opt_Id
844 Validate_Opt_Id(p_orlv_rec, x_return_status);
845 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
846 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
847 -- need to leave
848 l_return_status := x_return_status;
849 RAISE G_EXCEPTION_HALT_VALIDATION;
850 ELSE
851 -- record that there was an error
852 l_return_status := x_return_status;
853 END IF;
854 END IF;
855
856 -- Validate_Rgr_Rgd_Code
857 Validate_Rgr_Rgd_Code(p_orlv_rec, x_return_status);
858 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
859 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
860 -- need to leave
861 l_return_status := x_return_status;
862 RAISE G_EXCEPTION_HALT_VALIDATION;
863 ELSE
864 -- record that there was an error
865 l_return_status := x_return_status;
866 END IF;
867 END IF;
868
869 -- Validate_Rgr_Rdf_Code
870 Validate_Rgr_Rdf_Code(p_orlv_rec, x_return_status);
871 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
872 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
873 -- need to leave
874 l_return_status := x_return_status;
875 RAISE G_EXCEPTION_HALT_VALIDATION;
876 ELSE
877 -- record that there was an error
878 l_return_status := x_return_status;
879 END IF;
880 END IF;
881
882 -- Validate_Object_Version_Number
883 Validate_Object_Version_Number(p_orlv_rec, x_return_status);
884 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
885 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
886 -- need to leave
887 l_return_status := x_return_status;
888 RAISE G_EXCEPTION_HALT_VALIDATION;
889 ELSE
890 -- record that there was an error
891 l_return_status := x_return_status;
892 END IF;
893 END IF;
894
895 -- Validate_Srd_Id_For
896 If (p_orlv_rec.srd_id_for is not null) THEN
897 Validate_Srd_Id_For(p_orlv_rec, x_return_status);
898 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
899 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
900 -- need to leave
901 l_return_status := x_return_status;
902 RAISE G_EXCEPTION_HALT_VALIDATION;
903 ELSE
904 -- record that there was an error
905 l_return_status := x_return_status;
906 END IF;
907 END IF;
908 END IF;
909
910 -- Validate_Lrg_Lse_Id
911 If (p_orlv_rec.lrg_lse_id is not null) THEN
912 Validate_Lrg_Lse_Id(p_orlv_rec, x_return_status);
913 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
914 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
915 -- need to leave
916 l_return_status := x_return_status;
917 RAISE G_EXCEPTION_HALT_VALIDATION;
918 ELSE
919 -- record that there was an error
920 l_return_status := x_return_status;
921 END IF;
922 END IF;
923 END IF;
924
925 -- Validate_Lrg_Srd_Id
926 If (p_orlv_rec.lrg_srd_id is not null) THEN
927 Validate_Lrg_Srd_Id(p_orlv_rec, x_return_status);
928 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
929 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
930 -- need to leave
931 l_return_status := x_return_status;
932 RAISE G_EXCEPTION_HALT_VALIDATION;
933 ELSE
934 -- record that there was an error
935 l_return_status := x_return_status;
936 END IF;
937 END IF;
938 END IF;
939
940 RETURN(l_return_status);
941 EXCEPTION
942 WHEN G_EXCEPTION_HALT_VALIDATION THEN
943 -- just come out with return status
944 NULL;
945 RETURN (l_return_status);
946
947 WHEN OTHERS THEN
948 -- store SQL error message on message stack for caller
949 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
950 p_msg_name => g_unexpected_error,
951 p_token1 => g_sqlcode_token,
952 p_token1_value => sqlcode,
953 p_token2 => g_sqlerrm_token,
954 p_token2_value => sqlerrm);
955 -- notify caller of an UNEXPECTED error
956 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
957 RETURN(l_return_status);
958
959 END Validate_Attributes;
960
961 ---------------------------------------------------------------------------
962 -- PROCEDURE Validate_Unique_Orl_Record
963 ---------------------------------------------------------------------------
964 -- Start of comments
965 --
966 -- Procedure Name : Validate_Unique_Orl_Record
967 -- Description :
968 -- Business Rules :
969 -- Parameters :
970 -- Version : 1.0
971 -- End of comments
972 ---------------------------------------------------------------------------
973 PROCEDURE Validate_Unique_Orl_Record(p_orlv_rec IN orlv_rec_type
974 ,x_return_status OUT NOCOPY VARCHAR2)
975 IS
976
977 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
978 l_unq_tbl OKC_UTIL.unq_tbl_type;
979 l_orl_status VARCHAR2(1);
980 l_row_found Boolean := False;
981 CURSOR c1(p_opt_id okl_opt_rules_v.opt_id%TYPE,
982 p_rgr_rgd_code okl_opt_rules_v.rgr_rgd_code%TYPE,
983 p_rgr_rdf_code okl_opt_rules_v.rgr_rdf_code%TYPE,
984 p_srd_id_for okl_opt_rules_v.srd_id_for%TYPE) is
985 SELECT '1'
986 FROM okl_opt_rules_v
987 WHERE opt_id = p_opt_id
988 AND rgr_rgd_code = p_rgr_rgd_code
989 AND rgr_rdf_code = p_rgr_rdf_code
990 AND srd_id_for = p_srd_id_for
991 AND id <> nvl(p_orlv_rec.id,-9999);
992
993 CURSOR c2(p_opt_id okl_opt_rules_v.opt_id%TYPE,
994 p_rgr_rgd_code okl_opt_rules_v.rgr_rgd_code%TYPE,
995 p_rgr_rdf_code okl_opt_rules_v.rgr_rdf_code%TYPE,
996 p_lrg_lse_id okl_opt_rules_v.lrg_lse_id%TYPE,
997 p_lrg_srd_id okl_opt_rules_v.lrg_srd_id%TYPE) is
998 SELECT '1'
999 FROM okl_opt_rules_v
1000 WHERE opt_id = p_opt_id
1001 AND rgr_rgd_code = p_rgr_rgd_code
1002 AND rgr_rdf_code = p_rgr_rdf_code
1003 AND lrg_lse_id = p_lrg_lse_id
1004 AND lrg_srd_id = p_lrg_srd_id
1005 AND id <> nvl(p_orlv_rec.id,-9999);
1006
1007 BEGIN
1008
1009 -- initialize return status
1010 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1011 IF (p_orlv_rec.srd_id_for is not null) THEN
1012 OPEN c1(p_orlv_rec.opt_id,
1013 p_orlv_rec.rgr_rgd_code,
1014 p_orlv_rec.rgr_rdf_code,
1015 p_orlv_rec.srd_id_for);
1016 FETCH c1 into l_orl_status;
1017 l_row_found := c1%FOUND;
1018 CLOSE c1;
1019 IF l_row_found then
1020 OKC_API.set_message(G_APP_NAME,G_UNQS,G_TABLE_TOKEN, 'Okl_Opt_Rules_V'); ---CHG001
1021 x_return_status := OKC_API.G_RET_STS_ERROR;
1022 END IF;
1023 ELSE
1024 OPEN c2(p_orlv_rec.opt_id,
1025 p_orlv_rec.rgr_rgd_code,
1026 p_orlv_rec.rgr_rdf_code,
1027 p_orlv_rec.lrg_lse_id,
1028 p_orlv_rec.lrg_srd_id);
1029 FETCH c2 into l_orl_status;
1030 l_row_found := c2%FOUND;
1031 CLOSE c2;
1032 IF l_row_found then
1033 OKC_API.set_message(G_APP_NAME,G_UNQS,G_TABLE_TOKEN, 'Okl_Opt_Rules_V'); ---CHG001
1034 x_return_status := OKC_API.G_RET_STS_ERROR;
1035 END IF;
1036 END IF;
1037
1038 EXCEPTION
1039 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1040 -- no processing necessary; validation can continue
1041 -- with the next column
1042 NULL;
1043
1044 WHEN OTHERS THEN
1045 -- store SQL error message on message stack for caller
1046 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1047 p_msg_name => g_unexpected_error,
1048 p_token1 => g_sqlcode_token,
1049 p_token1_value => sqlcode,
1050 p_token2 => g_sqlerrm_token,
1051 p_token2_value => sqlerrm);
1052
1053 -- notify caller of an UNEXPECTED error
1054 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1055
1056 END Validate_Unique_Orl_Record;
1057
1058 ---------------------------------------------------------------------------
1059 -- PROCEDURE Validate_Record
1060 ---------------------------------------------------------------------------
1061 -- Start of comments
1062 --
1063 -- Procedure Name : Validate_Record
1064 -- Description :
1065 -- Business Rules :
1066 -- Parameters :
1067 -- Version : 1.0
1068 -- End of comments
1069 ---------------------------------------------------------------------------
1070
1071 FUNCTION Validate_Record (
1072 p_orlv_rec IN orlv_rec_type ) RETURN VARCHAR2 IS
1073 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1074 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1075 BEGIN
1076 x_return_status := Validate_Foreign_Keys(p_orlv_rec);
1077 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1078 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1079 -- need to leave
1080 l_return_status := x_return_status;
1081 RAISE G_EXCEPTION_HALT_VALIDATION;
1082 ELSE
1083 -- record that there was an error
1084 l_return_status := x_return_status;
1085 END IF;
1086 END IF;
1087
1088 -- Validate_Unique_Orl_Record
1089 Validate_Unique_Orl_Record(p_orlv_rec, x_return_status);
1090 IF (x_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
1091 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1092 -- need to leave
1093 l_return_status := x_return_status;
1094 RAISE G_EXCEPTION_HALT_VALIDATION;
1095 ELSE
1096 -- record that there was an error
1097 l_return_status := x_return_status;
1098 END IF;
1099 END IF;
1100
1101 RETURN(l_return_status);
1102 EXCEPTION
1103 WHEN G_EXCEPTION_HALT_VALIDATION THEN
1104 -- just come out with return status
1105 NULL;
1106 RETURN (l_return_status);
1107
1108 WHEN OTHERS THEN
1109 -- store SQL error message on message stack for caller
1110 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
1111 p_msg_name => g_unexpected_error,
1112 p_token1 => g_sqlcode_token,
1113 p_token1_value => sqlcode,
1114 p_token2 => g_sqlerrm_token,
1115 p_token2_value => sqlerrm);
1116 -- notify caller of an UNEXPECTED error
1117 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1118 RETURN(l_return_status);
1119 END Validate_Record;
1120
1121 /************************** TCHGS: End New Code *****************************/
1122
1123 ---------------------------------------------------------------------------
1124 -- PROCEDURE Migrate
1125 ---------------------------------------------------------------------------
1126 PROCEDURE migrate (
1127 p_from IN orlv_rec_type,
1128 p_to IN OUT NOCOPY orl_rec_type
1129 ) IS
1130 BEGIN
1131 p_to.id := p_from.id;
1132 p_to.opt_id := p_from.opt_id;
1133 p_to.rgr_rgd_code := p_from.rgr_rgd_code;
1134 p_to.rgr_rdf_code := p_from.rgr_rdf_code;
1135 p_to.srd_id_for := p_from.srd_id_for;
1136 p_to.lrg_lse_id := p_from.lrg_lse_id;
1137 p_to.lrg_srd_id := p_from.lrg_srd_id;
1138 p_to.object_version_number := p_from.object_version_number;
1139 p_to.created_by := p_from.created_by;
1140 p_to.creation_date := p_from.creation_date;
1141 p_to.last_updated_by := p_from.last_updated_by;
1142 p_to.last_update_date := p_from.last_update_date;
1143 p_to.overall_instructions := p_from.overall_instructions;
1144 p_to.last_update_login := p_from.last_update_login;
1145 END migrate;
1146 PROCEDURE migrate (
1147 p_from IN orl_rec_type,
1148 p_to IN OUT NOCOPY orlv_rec_type
1149 ) IS
1150 BEGIN
1151 p_to.id := p_from.id;
1152 p_to.opt_id := p_from.opt_id;
1153 p_to.rgr_rgd_code := p_from.rgr_rgd_code;
1154 p_to.rgr_rdf_code := p_from.rgr_rdf_code;
1155 p_to.srd_id_for := p_from.srd_id_for;
1156 p_to.lrg_lse_id := p_from.lrg_lse_id;
1157 p_to.lrg_srd_id := p_from.lrg_srd_id;
1158 p_to.object_version_number := p_from.object_version_number;
1159 p_to.created_by := p_from.created_by;
1160 p_to.creation_date := p_from.creation_date;
1161 p_to.last_updated_by := p_from.last_updated_by;
1162 p_to.last_update_date := p_from.last_update_date;
1163 p_to.overall_instructions := p_from.overall_instructions;
1164 p_to.last_update_login := p_from.last_update_login;
1165 END migrate;
1166
1167 ---------------------------------------------------------------------------
1168 -- PROCEDURE validate_row
1169 ---------------------------------------------------------------------------
1170 --------------------------------------
1171 -- validate_row for:OKL_OPT_RULES_V --
1172 --------------------------------------
1173 PROCEDURE validate_row(
1174 p_api_version IN NUMBER,
1175 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1176 x_return_status OUT NOCOPY VARCHAR2,
1177 x_msg_count OUT NOCOPY NUMBER,
1178 x_msg_data OUT NOCOPY VARCHAR2,
1179 p_orlv_rec IN orlv_rec_type) IS
1180
1181 l_api_version CONSTANT NUMBER := 1;
1182 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1183 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1184 l_orlv_rec orlv_rec_type := p_orlv_rec;
1185 l_orl_rec orl_rec_type;
1186 BEGIN
1187 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1188 G_PKG_NAME,
1189 p_init_msg_list,
1190 l_api_version,
1191 p_api_version,
1192 '_PVT',
1193 x_return_status);
1194 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1195 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1196 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1197 RAISE OKC_API.G_EXCEPTION_ERROR;
1198 END IF;
1199 --- Validate all non-missing attributes (Item Level Validation)
1200 l_return_status := Validate_Attributes(l_orlv_rec);
1201 --- If any errors happen abort API
1202 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1203 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1204 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1205 RAISE OKC_API.G_EXCEPTION_ERROR;
1206 END IF;
1207 l_return_status := Validate_Record(l_orlv_rec);
1208 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1209 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1210 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1211 RAISE OKC_API.G_EXCEPTION_ERROR;
1212 END IF;
1213 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1214 EXCEPTION
1215 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1216 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1217 (
1218 l_api_name,
1219 G_PKG_NAME,
1220 'OKC_API.G_RET_STS_ERROR',
1221 x_msg_count,
1222 x_msg_data,
1223 '_PVT'
1224 );
1225 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1226 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1227 (
1228 l_api_name,
1229 G_PKG_NAME,
1230 'OKC_API.G_RET_STS_UNEXP_ERROR',
1231 x_msg_count,
1232 x_msg_data,
1233 '_PVT'
1234 );
1235 WHEN OTHERS THEN
1236 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1237 (
1238 l_api_name,
1239 G_PKG_NAME,
1240 'OTHERS',
1241 x_msg_count,
1242 x_msg_data,
1243 '_PVT'
1244 );
1245 END validate_row;
1246 ------------------------------------------
1247 -- PL/SQL TBL validate_row for:ORLV_TBL --
1248 ------------------------------------------
1249 PROCEDURE validate_row(
1250 p_api_version IN NUMBER,
1251 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1252 x_return_status OUT NOCOPY VARCHAR2,
1253 x_msg_count OUT NOCOPY NUMBER,
1254 x_msg_data OUT NOCOPY VARCHAR2,
1255 p_orlv_tbl IN orlv_tbl_type) IS
1256
1257 l_api_version CONSTANT NUMBER := 1;
1258 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1259 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1260 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS; --TCHGS
1261 i NUMBER := 0;
1262 BEGIN
1263 OKC_API.init_msg_list(p_init_msg_list);
1264 -- Make sure PL/SQL table has records in it before passing
1265 IF (p_orlv_tbl.COUNT > 0) THEN
1266 i := p_orlv_tbl.FIRST;
1267 LOOP
1268 validate_row (
1269 p_api_version => p_api_version,
1270 p_init_msg_list => OKC_API.G_FALSE,
1271 x_return_status => x_return_status,
1272 x_msg_count => x_msg_count,
1273 x_msg_data => x_msg_data,
1274 p_orlv_rec => p_orlv_tbl(i));
1275 -- TCHGS: Store the highest degree of error
1276 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1277 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1278 l_overall_status := x_return_status;
1279 END IF;
1280 END IF;
1281 EXIT WHEN (i = p_orlv_tbl.LAST);
1282 i := p_orlv_tbl.NEXT(i);
1283 END LOOP;
1284 --TCHGS: return overall status
1285 x_return_status := l_overall_status;
1286 END IF;
1287 EXCEPTION
1288 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1289 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1290 (
1291 l_api_name,
1292 G_PKG_NAME,
1293 'OKC_API.G_RET_STS_ERROR',
1294 x_msg_count,
1295 x_msg_data,
1296 '_PVT'
1297 );
1298 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1299 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1300 (
1301 l_api_name,
1302 G_PKG_NAME,
1303 'OKC_API.G_RET_STS_UNEXP_ERROR',
1304 x_msg_count,
1305 x_msg_data,
1306 '_PVT'
1307 );
1308 WHEN OTHERS THEN
1309 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1310 (
1311 l_api_name,
1312 G_PKG_NAME,
1313 'OTHERS',
1314 x_msg_count,
1315 x_msg_data,
1316 '_PVT'
1317 );
1318 END validate_row;
1319
1320 ---------------------------------------------------------------------------
1321 -- PROCEDURE insert_row
1322 ---------------------------------------------------------------------------
1323 ----------------------------------
1324 -- insert_row for:OKL_OPT_RULES --
1325 ----------------------------------
1326 PROCEDURE insert_row(
1327 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1328 x_return_status OUT NOCOPY VARCHAR2,
1329 x_msg_count OUT NOCOPY NUMBER,
1330 x_msg_data OUT NOCOPY VARCHAR2,
1331 p_orl_rec IN orl_rec_type,
1332 x_orl_rec OUT NOCOPY orl_rec_type) IS
1333
1334 l_api_version CONSTANT NUMBER := 1;
1335 l_api_name CONSTANT VARCHAR2(30) := 'RULES_insert_row';
1336 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1337 l_orl_rec orl_rec_type := p_orl_rec;
1338 l_def_orl_rec orl_rec_type;
1339 --------------------------------------
1340 -- Set_Attributes for:OKL_OPT_RULES --
1341 --------------------------------------
1342 FUNCTION Set_Attributes (
1343 p_orl_rec IN orl_rec_type,
1344 x_orl_rec OUT NOCOPY orl_rec_type
1345 ) RETURN VARCHAR2 IS
1346 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1347 BEGIN
1348 x_orl_rec := p_orl_rec;
1349 RETURN(l_return_status);
1350 END Set_Attributes;
1351 BEGIN
1352 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1353 p_init_msg_list,
1354 '_PVT',
1355 x_return_status);
1356 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1357 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1358 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1359 RAISE OKC_API.G_EXCEPTION_ERROR;
1360 END IF;
1361 --- Setting item attributes
1362 l_return_status := Set_Attributes(
1363 p_orl_rec, -- IN
1364 l_orl_rec); -- OUT
1365 --- If any errors happen abort API
1366 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1367 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1368 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1369 RAISE OKC_API.G_EXCEPTION_ERROR;
1370 END IF;
1371 INSERT INTO OKL_OPT_RULES(
1372 id,
1373 opt_id,
1374 rgr_rgd_code,
1375 rgr_rdf_code,
1376 srd_id_for,
1377 lrg_lse_id,
1378 lrg_srd_id,
1379 object_version_number,
1380 created_by,
1381 creation_date,
1382 last_updated_by,
1383 last_update_date,
1384 overall_instructions,
1385 last_update_login)
1386 VALUES (
1387 l_orl_rec.id,
1388 l_orl_rec.opt_id,
1389 l_orl_rec.rgr_rgd_code,
1390 l_orl_rec.rgr_rdf_code,
1391 l_orl_rec.srd_id_for,
1392 l_orl_rec.lrg_lse_id,
1393 l_orl_rec.lrg_srd_id,
1394 l_orl_rec.object_version_number,
1395 l_orl_rec.created_by,
1396 l_orl_rec.creation_date,
1397 l_orl_rec.last_updated_by,
1398 l_orl_rec.last_update_date,
1399 l_orl_rec.overall_instructions,
1400 l_orl_rec.last_update_login);
1401 -- Set OUT values
1402 x_orl_rec := l_orl_rec;
1403 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1404 EXCEPTION
1405 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1406 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1407 (
1408 l_api_name,
1409 G_PKG_NAME,
1410 'OKC_API.G_RET_STS_ERROR',
1411 x_msg_count,
1412 x_msg_data,
1413 '_PVT'
1414 );
1415 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1416 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1417 (
1418 l_api_name,
1419 G_PKG_NAME,
1420 'OKC_API.G_RET_STS_UNEXP_ERROR',
1421 x_msg_count,
1422 x_msg_data,
1423 '_PVT'
1424 );
1425 WHEN OTHERS THEN
1426 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1427 (
1428 l_api_name,
1429 G_PKG_NAME,
1430 'OTHERS',
1431 x_msg_count,
1432 x_msg_data,
1433 '_PVT'
1434 );
1435 END insert_row;
1436 ------------------------------------
1437 -- insert_row for:OKL_OPT_RULES_V --
1438 ------------------------------------
1439 PROCEDURE insert_row(
1440 p_api_version IN NUMBER,
1441 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1442 x_return_status OUT NOCOPY VARCHAR2,
1443 x_msg_count OUT NOCOPY NUMBER,
1444 x_msg_data OUT NOCOPY VARCHAR2,
1445 p_orlv_rec IN orlv_rec_type,
1446 x_orlv_rec OUT NOCOPY orlv_rec_type) IS
1447
1448 l_api_version CONSTANT NUMBER := 1;
1449 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1450 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1451 l_orlv_rec orlv_rec_type;
1452 l_def_orlv_rec orlv_rec_type;
1453 l_orl_rec orl_rec_type;
1454 lx_orl_rec orl_rec_type;
1455 -------------------------------
1456 -- FUNCTION fill_who_columns --
1457 -------------------------------
1458 FUNCTION fill_who_columns (
1459 p_orlv_rec IN orlv_rec_type
1460 ) RETURN orlv_rec_type IS
1461 l_orlv_rec orlv_rec_type := p_orlv_rec;
1462 BEGIN
1463 l_orlv_rec.CREATION_DATE := SYSDATE;
1464 l_orlv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1465 l_orlv_rec.LAST_UPDATE_DATE := l_orlv_rec.CREATION_DATE;
1466 l_orlv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1467 l_orlv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1468 RETURN(l_orlv_rec);
1469 END fill_who_columns;
1470 ----------------------------------------
1471 -- Set_Attributes for:OKL_OPT_RULES_V --
1472 ----------------------------------------
1473 FUNCTION Set_Attributes (
1474 p_orlv_rec IN orlv_rec_type,
1475 x_orlv_rec OUT NOCOPY orlv_rec_type
1476 ) RETURN VARCHAR2 IS
1477 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1478 BEGIN
1479 x_orlv_rec := p_orlv_rec;
1480 x_orlv_rec.OBJECT_VERSION_NUMBER := 1;
1481 RETURN(l_return_status);
1482 END Set_Attributes;
1483 BEGIN
1484 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1485 G_PKG_NAME,
1486 p_init_msg_list,
1487 l_api_version,
1488 p_api_version,
1489 '_PVT',
1490 x_return_status);
1491 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1492 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1493 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1494 RAISE OKC_API.G_EXCEPTION_ERROR;
1495 END IF;
1496 l_orlv_rec := null_out_defaults(p_orlv_rec);
1497 -- Set primary key value
1498 l_orlv_rec.ID := get_seq_id;
1499 --- Setting item attributes
1500 l_return_status := Set_Attributes(
1501 l_orlv_rec, -- IN
1502 l_def_orlv_rec); -- OUT
1503 --- If any errors happen abort API
1504 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1505 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1506 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1507 RAISE OKC_API.G_EXCEPTION_ERROR;
1508 END IF;
1509 l_def_orlv_rec := fill_who_columns(l_def_orlv_rec);
1510 --- Validate all non-missing attributes (Item Level Validation)
1511 l_return_status := Validate_Attributes(l_def_orlv_rec);
1512 --- If any errors happen abort API
1513 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1514 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1515 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1516 RAISE OKC_API.G_EXCEPTION_ERROR;
1517 END IF;
1518 l_return_status := Validate_Record(l_def_orlv_rec);
1519 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1520 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1521 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1522 RAISE OKC_API.G_EXCEPTION_ERROR;
1523 END IF;
1524 --------------------------------------
1525 -- Move VIEW record to "Child" records
1526 --------------------------------------
1527 migrate(l_def_orlv_rec, l_orl_rec);
1528 --------------------------------------------
1529 -- Call the INSERT_ROW for each child record
1530 --------------------------------------------
1531 insert_row(
1532 p_init_msg_list,
1533 x_return_status,
1534 x_msg_count,
1535 x_msg_data,
1536 l_orl_rec,
1537 lx_orl_rec
1538 );
1539 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1540 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1541 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1542 RAISE OKC_API.G_EXCEPTION_ERROR;
1543 END IF;
1544 migrate(lx_orl_rec, l_def_orlv_rec);
1545 -- Set OUT values
1546 x_orlv_rec := l_def_orlv_rec;
1547 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1548 EXCEPTION
1549 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1550 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1551 (
1552 l_api_name,
1553 G_PKG_NAME,
1554 'OKC_API.G_RET_STS_ERROR',
1555 x_msg_count,
1556 x_msg_data,
1557 '_PVT'
1558 );
1559 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1560 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1561 (
1562 l_api_name,
1563 G_PKG_NAME,
1564 'OKC_API.G_RET_STS_UNEXP_ERROR',
1565 x_msg_count,
1566 x_msg_data,
1567 '_PVT'
1568 );
1569 WHEN OTHERS THEN
1570 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1571 (
1572 l_api_name,
1573 G_PKG_NAME,
1574 'OTHERS',
1575 x_msg_count,
1576 x_msg_data,
1577 '_PVT'
1578 );
1579 END insert_row;
1580 ----------------------------------------
1581 -- PL/SQL TBL insert_row for:ORLV_TBL --
1582 ----------------------------------------
1583 PROCEDURE insert_row(
1584 p_api_version IN NUMBER,
1585 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1586 x_return_status OUT NOCOPY VARCHAR2,
1587 x_msg_count OUT NOCOPY NUMBER,
1588 x_msg_data OUT NOCOPY VARCHAR2,
1589 p_orlv_tbl IN orlv_tbl_type,
1590 x_orlv_tbl OUT NOCOPY orlv_tbl_type) IS
1591
1592 l_api_version CONSTANT NUMBER := 1;
1593 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1594 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1595 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS; --TCHGS
1596 i NUMBER := 0;
1597 BEGIN
1598 OKC_API.init_msg_list(p_init_msg_list);
1599 -- Make sure PL/SQL table has records in it before passing
1600 IF (p_orlv_tbl.COUNT > 0) THEN
1601 i := p_orlv_tbl.FIRST;
1602 LOOP
1603 insert_row (
1604 p_api_version => p_api_version,
1605 p_init_msg_list => OKC_API.G_FALSE,
1606 x_return_status => x_return_status,
1607 x_msg_count => x_msg_count,
1608 x_msg_data => x_msg_data,
1609 p_orlv_rec => p_orlv_tbl(i),
1610 x_orlv_rec => x_orlv_tbl(i));
1611 -- TCHGS: Store the highest degree of error
1612 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1613 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1614 l_overall_status := x_return_status;
1615 END IF;
1616 END IF;
1617 EXIT WHEN (i = p_orlv_tbl.LAST);
1618 i := p_orlv_tbl.NEXT(i);
1619 END LOOP;
1620 --TCHGS: return overall status
1621 x_return_status := l_overall_status;
1622 END IF;
1623 EXCEPTION
1624 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1625 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1626 (
1627 l_api_name,
1628 G_PKG_NAME,
1629 'OKC_API.G_RET_STS_ERROR',
1630 x_msg_count,
1631 x_msg_data,
1632 '_PVT'
1633 );
1634 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1635 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1636 (
1637 l_api_name,
1638 G_PKG_NAME,
1639 'OKC_API.G_RET_STS_UNEXP_ERROR',
1640 x_msg_count,
1641 x_msg_data,
1642 '_PVT'
1643 );
1644 WHEN OTHERS THEN
1645 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1646 (
1647 l_api_name,
1648 G_PKG_NAME,
1649 'OTHERS',
1650 x_msg_count,
1651 x_msg_data,
1652 '_PVT'
1653 );
1654 END insert_row;
1655
1656 ---------------------------------------------------------------------------
1657 -- PROCEDURE lock_row
1658 ---------------------------------------------------------------------------
1659 --------------------------------
1660 -- lock_row for:OKL_OPT_RULES --
1661 --------------------------------
1662 PROCEDURE lock_row(
1663 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1664 x_return_status OUT NOCOPY VARCHAR2,
1665 x_msg_count OUT NOCOPY NUMBER,
1666 x_msg_data OUT NOCOPY VARCHAR2,
1667 p_orl_rec IN orl_rec_type) IS
1668
1669 E_Resource_Busy EXCEPTION;
1670 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1671 CURSOR lock_csr (p_orl_rec IN orl_rec_type) IS
1672 SELECT OBJECT_VERSION_NUMBER
1673 FROM OKL_OPT_RULES
1674 WHERE ID = p_orl_rec.id
1675 AND OBJECT_VERSION_NUMBER = p_orl_rec.object_version_number
1676 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1677
1678 CURSOR lchk_csr (p_orl_rec IN orl_rec_type) IS
1679 SELECT OBJECT_VERSION_NUMBER
1680 FROM OKL_OPT_RULES
1681 WHERE ID = p_orl_rec.id;
1682 l_api_version CONSTANT NUMBER := 1;
1683 l_api_name CONSTANT VARCHAR2(30) := 'RULES_lock_row';
1684 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1685 l_object_version_number OKL_OPT_RULES.OBJECT_VERSION_NUMBER%TYPE;
1686 lc_object_version_number OKL_OPT_RULES.OBJECT_VERSION_NUMBER%TYPE;
1687 l_row_notfound BOOLEAN := FALSE;
1688 lc_row_notfound BOOLEAN := FALSE;
1689 BEGIN
1690 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1691 p_init_msg_list,
1692 '_PVT',
1693 x_return_status);
1694 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1695 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1696 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1697 RAISE OKC_API.G_EXCEPTION_ERROR;
1698 END IF;
1699 BEGIN
1700 OPEN lock_csr(p_orl_rec);
1701 FETCH lock_csr INTO l_object_version_number;
1702 l_row_notfound := lock_csr%NOTFOUND;
1703 CLOSE lock_csr;
1704 EXCEPTION
1705 WHEN E_Resource_Busy THEN
1706 IF (lock_csr%ISOPEN) THEN
1707 CLOSE lock_csr;
1708 END IF;
1709 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1710 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1711 END;
1712
1713 IF ( l_row_notfound ) THEN
1714 OPEN lchk_csr(p_orl_rec);
1715 FETCH lchk_csr INTO lc_object_version_number;
1716 lc_row_notfound := lchk_csr%NOTFOUND;
1717 CLOSE lchk_csr;
1718 END IF;
1719 IF (lc_row_notfound) THEN
1720 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1721 RAISE OKC_API.G_EXCEPTION_ERROR;
1722 ELSIF lc_object_version_number > p_orl_rec.object_version_number THEN
1723 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1724 RAISE OKC_API.G_EXCEPTION_ERROR;
1725 ELSIF lc_object_version_number <> p_orl_rec.object_version_number THEN
1726 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1727 RAISE OKC_API.G_EXCEPTION_ERROR;
1728 ELSIF lc_object_version_number = -1 THEN
1729 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1730 RAISE OKC_API.G_EXCEPTION_ERROR;
1731 END IF;
1732 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1733 EXCEPTION
1734 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1735 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1736 (
1737 l_api_name,
1738 G_PKG_NAME,
1739 'OKC_API.G_RET_STS_ERROR',
1740 x_msg_count,
1741 x_msg_data,
1742 '_PVT'
1743 );
1744 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1745 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1746 (
1747 l_api_name,
1748 G_PKG_NAME,
1749 'OKC_API.G_RET_STS_UNEXP_ERROR',
1750 x_msg_count,
1751 x_msg_data,
1752 '_PVT'
1753 );
1754 WHEN OTHERS THEN
1755 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1756 (
1757 l_api_name,
1758 G_PKG_NAME,
1759 'OTHERS',
1760 x_msg_count,
1761 x_msg_data,
1762 '_PVT'
1763 );
1764 END lock_row;
1765 ----------------------------------
1766 -- lock_row for:OKL_OPT_RULES_V --
1767 ----------------------------------
1768 PROCEDURE lock_row(
1769 p_api_version IN NUMBER,
1770 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1771 x_return_status OUT NOCOPY VARCHAR2,
1772 x_msg_count OUT NOCOPY NUMBER,
1773 x_msg_data OUT NOCOPY VARCHAR2,
1774 p_orlv_rec IN orlv_rec_type) IS
1775
1776 l_api_version CONSTANT NUMBER := 1;
1777 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1778 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1779 l_orl_rec orl_rec_type;
1780 BEGIN
1781 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1782 G_PKG_NAME,
1783 p_init_msg_list,
1784 l_api_version,
1785 p_api_version,
1786 '_PVT',
1787 x_return_status);
1788 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1789 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1790 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1791 RAISE OKC_API.G_EXCEPTION_ERROR;
1792 END IF;
1793 --------------------------------------
1794 -- Move VIEW record to "Child" records
1795 --------------------------------------
1796 migrate(p_orlv_rec, l_orl_rec);
1797 --------------------------------------------
1798 -- Call the LOCK_ROW for each child record
1799 --------------------------------------------
1800 lock_row(
1801 p_init_msg_list,
1802 x_return_status,
1803 x_msg_count,
1804 x_msg_data,
1805 l_orl_rec
1806 );
1807 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1808 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1809 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1810 RAISE OKC_API.G_EXCEPTION_ERROR;
1811 END IF;
1812 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1813 EXCEPTION
1814 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1815 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1816 (
1817 l_api_name,
1818 G_PKG_NAME,
1819 'OKC_API.G_RET_STS_ERROR',
1820 x_msg_count,
1821 x_msg_data,
1822 '_PVT'
1823 );
1824 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1825 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1826 (
1827 l_api_name,
1828 G_PKG_NAME,
1829 'OKC_API.G_RET_STS_UNEXP_ERROR',
1830 x_msg_count,
1831 x_msg_data,
1832 '_PVT'
1833 );
1834 WHEN OTHERS THEN
1835 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1836 (
1837 l_api_name,
1838 G_PKG_NAME,
1839 'OTHERS',
1840 x_msg_count,
1841 x_msg_data,
1842 '_PVT'
1843 );
1844 END lock_row;
1845 --------------------------------------
1846 -- PL/SQL TBL lock_row for:ORLV_TBL --
1847 --------------------------------------
1848 PROCEDURE lock_row(
1849 p_api_version IN NUMBER,
1850 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1851 x_return_status OUT NOCOPY VARCHAR2,
1852 x_msg_count OUT NOCOPY NUMBER,
1853 x_msg_data OUT NOCOPY VARCHAR2,
1854 p_orlv_tbl IN orlv_tbl_type) IS
1855
1856 l_api_version CONSTANT NUMBER := 1;
1857 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1858 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1859 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS; --TCHGS
1860 i NUMBER := 0;
1861 BEGIN
1862 OKC_API.init_msg_list(p_init_msg_list);
1863 -- Make sure PL/SQL table has records in it before passing
1864 IF (p_orlv_tbl.COUNT > 0) THEN
1865 i := p_orlv_tbl.FIRST;
1866 LOOP
1867 lock_row (
1868 p_api_version => p_api_version,
1869 p_init_msg_list => OKC_API.G_FALSE,
1870 x_return_status => x_return_status,
1871 x_msg_count => x_msg_count,
1872 x_msg_data => x_msg_data,
1873 p_orlv_rec => p_orlv_tbl(i));
1874 -- TCHGS: Store the highest degree of error
1875 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
1876 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
1877 l_overall_status := x_return_status;
1878 END IF;
1879 END IF;
1880 EXIT WHEN (i = p_orlv_tbl.LAST);
1881 i := p_orlv_tbl.NEXT(i);
1882 END LOOP;
1883 --TCHGS: return overall status
1884 x_return_status := l_overall_status;
1885 END IF;
1886 EXCEPTION
1887 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1888 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1889 (
1890 l_api_name,
1891 G_PKG_NAME,
1892 'OKC_API.G_RET_STS_ERROR',
1893 x_msg_count,
1894 x_msg_data,
1895 '_PVT'
1896 );
1897 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1898 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1899 (
1900 l_api_name,
1901 G_PKG_NAME,
1902 'OKC_API.G_RET_STS_UNEXP_ERROR',
1903 x_msg_count,
1904 x_msg_data,
1905 '_PVT'
1906 );
1907 WHEN OTHERS THEN
1908 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1909 (
1910 l_api_name,
1911 G_PKG_NAME,
1912 'OTHERS',
1913 x_msg_count,
1914 x_msg_data,
1915 '_PVT'
1916 );
1917 END lock_row;
1918
1919 ---------------------------------------------------------------------------
1920 -- PROCEDURE update_row
1921 ---------------------------------------------------------------------------
1922 ----------------------------------
1923 -- update_row for:OKL_OPT_RULES --
1924 ----------------------------------
1925 PROCEDURE update_row(
1926 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
1927 x_return_status OUT NOCOPY VARCHAR2,
1928 x_msg_count OUT NOCOPY NUMBER,
1929 x_msg_data OUT NOCOPY VARCHAR2,
1930 p_orl_rec IN orl_rec_type,
1931 x_orl_rec OUT NOCOPY orl_rec_type) IS
1932
1933 l_api_version CONSTANT NUMBER := 1;
1934 l_api_name CONSTANT VARCHAR2(30) := 'RULES_update_row';
1935 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1936 l_orl_rec orl_rec_type := p_orl_rec;
1937 l_def_orl_rec orl_rec_type;
1938 l_row_notfound BOOLEAN := TRUE;
1939 ----------------------------------
1940 -- FUNCTION populate_new_record --
1941 ----------------------------------
1942 FUNCTION populate_new_record (
1943 p_orl_rec IN orl_rec_type,
1944 x_orl_rec OUT NOCOPY orl_rec_type
1945 ) RETURN VARCHAR2 IS
1946 l_orl_rec orl_rec_type;
1947 l_row_notfound BOOLEAN := TRUE;
1948 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1949 BEGIN
1950 x_orl_rec := p_orl_rec;
1951 -- Get current database values
1952 l_orl_rec := get_rec(p_orl_rec, l_row_notfound);
1953 IF (l_row_notfound) THEN
1954 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1955 END IF;
1956 IF (x_orl_rec.id = OKC_API.G_MISS_NUM)
1957 THEN
1958 x_orl_rec.id := l_orl_rec.id;
1959 END IF;
1960 IF (x_orl_rec.opt_id = OKC_API.G_MISS_NUM)
1961 THEN
1962 x_orl_rec.opt_id := l_orl_rec.opt_id;
1963 END IF;
1964 IF (x_orl_rec.rgr_rgd_code = OKC_API.G_MISS_CHAR)
1965 THEN
1966 x_orl_rec.rgr_rgd_code := l_orl_rec.rgr_rgd_code;
1967 END IF;
1968 IF (x_orl_rec.rgr_rdf_code = OKC_API.G_MISS_CHAR)
1969 THEN
1970 x_orl_rec.rgr_rdf_code := l_orl_rec.rgr_rdf_code;
1971 END IF;
1972 IF (x_orl_rec.srd_id_for = OKC_API.G_MISS_NUM)
1973 THEN
1974 x_orl_rec.srd_id_for := l_orl_rec.srd_id_for;
1975 END IF;
1976 IF (x_orl_rec.lrg_lse_id = OKC_API.G_MISS_NUM)
1977 THEN
1978 x_orl_rec.lrg_lse_id := l_orl_rec.lrg_lse_id;
1979 END IF;
1980 IF (x_orl_rec.lrg_srd_id = OKC_API.G_MISS_NUM)
1981 THEN
1982 x_orl_rec.lrg_srd_id := l_orl_rec.lrg_srd_id;
1983 END IF;
1984 IF (x_orl_rec.object_version_number = OKC_API.G_MISS_NUM)
1985 THEN
1986 x_orl_rec.object_version_number := l_orl_rec.object_version_number;
1987 END IF;
1988 IF (x_orl_rec.created_by = OKC_API.G_MISS_NUM)
1989 THEN
1990 x_orl_rec.created_by := l_orl_rec.created_by;
1991 END IF;
1992 IF (x_orl_rec.creation_date = OKC_API.G_MISS_DATE)
1993 THEN
1994 x_orl_rec.creation_date := l_orl_rec.creation_date;
1995 END IF;
1996 IF (x_orl_rec.last_updated_by = OKC_API.G_MISS_NUM)
1997 THEN
1998 x_orl_rec.last_updated_by := l_orl_rec.last_updated_by;
1999 END IF;
2000 IF (x_orl_rec.last_update_date = OKC_API.G_MISS_DATE)
2001 THEN
2002 x_orl_rec.last_update_date := l_orl_rec.last_update_date;
2003 END IF;
2004 IF (x_orl_rec.overall_instructions = OKC_API.G_MISS_CHAR)
2005 THEN
2006 x_orl_rec.overall_instructions := l_orl_rec.overall_instructions;
2007 END IF;
2008 IF (x_orl_rec.last_update_login = OKC_API.G_MISS_NUM)
2009 THEN
2010 x_orl_rec.last_update_login := l_orl_rec.last_update_login;
2011 END IF;
2012 RETURN(l_return_status);
2013 END populate_new_record;
2014 --------------------------------------
2015 -- Set_Attributes for:OKL_OPT_RULES --
2016 --------------------------------------
2017 FUNCTION Set_Attributes (
2018 p_orl_rec IN orl_rec_type,
2019 x_orl_rec OUT NOCOPY orl_rec_type
2020 ) RETURN VARCHAR2 IS
2021 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2022 BEGIN
2023 x_orl_rec := p_orl_rec;
2024 RETURN(l_return_status);
2025 END Set_Attributes;
2026 BEGIN
2027 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2028 p_init_msg_list,
2029 '_PVT',
2030 x_return_status);
2031 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2032 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2033 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2034 RAISE OKC_API.G_EXCEPTION_ERROR;
2035 END IF;
2036 --- Setting item attributes
2037 l_return_status := Set_Attributes(
2038 p_orl_rec, -- IN
2039 l_orl_rec); -- OUT
2040 --- If any errors happen abort API
2041 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2042 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2043 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2044 RAISE OKC_API.G_EXCEPTION_ERROR;
2045 END IF;
2046 l_return_status := populate_new_record(l_orl_rec, l_def_orl_rec);
2047 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2048 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2049 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2050 RAISE OKC_API.G_EXCEPTION_ERROR;
2051 END IF;
2052 UPDATE OKL_OPT_RULES
2053 SET OPT_ID = l_def_orl_rec.opt_id,
2054 RGR_RGD_CODE = l_def_orl_rec.rgr_rgd_code,
2055 RGR_RDF_CODE = l_def_orl_rec.rgr_rdf_code,
2056 SRD_ID_FOR = l_def_orl_rec.srd_id_for,
2057 LRG_LSE_ID = l_def_orl_rec.lrg_lse_id,
2058 LRG_SRD_ID = l_def_orl_rec.lrg_srd_id,
2059 OBJECT_VERSION_NUMBER = l_def_orl_rec.object_version_number,
2060 CREATED_BY = l_def_orl_rec.created_by,
2061 CREATION_DATE = l_def_orl_rec.creation_date,
2062 LAST_UPDATED_BY = l_def_orl_rec.last_updated_by,
2063 LAST_UPDATE_DATE = l_def_orl_rec.last_update_date,
2064 OVERALL_INSTRUCTIONS = l_def_orl_rec.overall_instructions,
2065 LAST_UPDATE_LOGIN = l_def_orl_rec.last_update_login
2066 WHERE ID = l_def_orl_rec.id;
2067
2068 x_orl_rec := l_def_orl_rec;
2069 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2070 EXCEPTION
2071 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2072 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2073 (
2074 l_api_name,
2075 G_PKG_NAME,
2076 'OKC_API.G_RET_STS_ERROR',
2077 x_msg_count,
2078 x_msg_data,
2079 '_PVT'
2080 );
2081 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2082 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2083 (
2084 l_api_name,
2085 G_PKG_NAME,
2086 'OKC_API.G_RET_STS_UNEXP_ERROR',
2087 x_msg_count,
2088 x_msg_data,
2089 '_PVT'
2090 );
2091 WHEN OTHERS THEN
2092 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2093 (
2094 l_api_name,
2095 G_PKG_NAME,
2096 'OTHERS',
2097 x_msg_count,
2098 x_msg_data,
2099 '_PVT'
2100 );
2101 END update_row;
2102 ------------------------------------
2103 -- update_row for:OKL_OPT_RULES_V --
2104 ------------------------------------
2105 PROCEDURE update_row(
2106 p_api_version IN NUMBER,
2107 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2108 x_return_status OUT NOCOPY VARCHAR2,
2109 x_msg_count OUT NOCOPY NUMBER,
2110 x_msg_data OUT NOCOPY VARCHAR2,
2111 p_orlv_rec IN orlv_rec_type,
2112 x_orlv_rec OUT NOCOPY orlv_rec_type) IS
2113
2114 l_api_version CONSTANT NUMBER := 1;
2115 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2116 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2117 l_orlv_rec orlv_rec_type := p_orlv_rec;
2118 l_def_orlv_rec orlv_rec_type;
2119 l_orl_rec orl_rec_type;
2120 lx_orl_rec orl_rec_type;
2121 -------------------------------
2122 -- FUNCTION fill_who_columns --
2123 -------------------------------
2124 FUNCTION fill_who_columns (
2125 p_orlv_rec IN orlv_rec_type
2126 ) RETURN orlv_rec_type IS
2127 l_orlv_rec orlv_rec_type := p_orlv_rec;
2128 BEGIN
2129 l_orlv_rec.LAST_UPDATE_DATE := SYSDATE;
2130 l_orlv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2131 l_orlv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2132 RETURN(l_orlv_rec);
2133 END fill_who_columns;
2134 ----------------------------------
2135 -- FUNCTION populate_new_record --
2136 ----------------------------------
2137 FUNCTION populate_new_record (
2138 p_orlv_rec IN orlv_rec_type,
2139 x_orlv_rec OUT NOCOPY orlv_rec_type
2140 ) RETURN VARCHAR2 IS
2141 l_orlv_rec orlv_rec_type;
2142 l_row_notfound BOOLEAN := TRUE;
2143 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2144 BEGIN
2145 x_orlv_rec := p_orlv_rec;
2146 -- Get current database values
2147 l_orlv_rec := get_rec(p_orlv_rec, l_row_notfound);
2148 IF (l_row_notfound) THEN
2149 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2150 END IF;
2151 IF (x_orlv_rec.id = OKC_API.G_MISS_NUM)
2152 THEN
2153 x_orlv_rec.id := l_orlv_rec.id;
2154 END IF;
2155 IF (x_orlv_rec.object_version_number = OKC_API.G_MISS_NUM)
2156 THEN
2157 x_orlv_rec.object_version_number := l_orlv_rec.object_version_number;
2158 END IF;
2159 IF (x_orlv_rec.opt_id = OKC_API.G_MISS_NUM)
2160 THEN
2161 x_orlv_rec.opt_id := l_orlv_rec.opt_id;
2162 END IF;
2163 IF (x_orlv_rec.srd_id_for = OKC_API.G_MISS_NUM)
2164 THEN
2165 x_orlv_rec.srd_id_for := l_orlv_rec.srd_id_for;
2166 END IF;
2167 IF (x_orlv_rec.rgr_rgd_code = OKC_API.G_MISS_CHAR)
2168 THEN
2169 x_orlv_rec.rgr_rgd_code := l_orlv_rec.rgr_rgd_code;
2170 END IF;
2171 IF (x_orlv_rec.rgr_rdf_code = OKC_API.G_MISS_CHAR)
2172 THEN
2173 x_orlv_rec.rgr_rdf_code := l_orlv_rec.rgr_rdf_code;
2174 END IF;
2175 IF (x_orlv_rec.lrg_lse_id = OKC_API.G_MISS_NUM)
2176 THEN
2177 x_orlv_rec.lrg_lse_id := l_orlv_rec.lrg_lse_id;
2178 END IF;
2179 IF (x_orlv_rec.lrg_srd_id = OKC_API.G_MISS_NUM)
2180 THEN
2181 x_orlv_rec.lrg_srd_id := l_orlv_rec.lrg_srd_id;
2182 END IF;
2183 IF (x_orlv_rec.overall_instructions = OKC_API.G_MISS_CHAR)
2184 THEN
2185 x_orlv_rec.overall_instructions := l_orlv_rec.overall_instructions;
2186 END IF;
2187 IF (x_orlv_rec.created_by = OKC_API.G_MISS_NUM)
2188 THEN
2189 x_orlv_rec.created_by := l_orlv_rec.created_by;
2190 END IF;
2191 IF (x_orlv_rec.creation_date = OKC_API.G_MISS_DATE)
2192 THEN
2193 x_orlv_rec.creation_date := l_orlv_rec.creation_date;
2194 END IF;
2195 IF (x_orlv_rec.last_updated_by = OKC_API.G_MISS_NUM)
2196 THEN
2197 x_orlv_rec.last_updated_by := l_orlv_rec.last_updated_by;
2198 END IF;
2199 IF (x_orlv_rec.last_update_date = OKC_API.G_MISS_DATE)
2200 THEN
2201 x_orlv_rec.last_update_date := l_orlv_rec.last_update_date;
2202 END IF;
2203 IF (x_orlv_rec.last_update_login = OKC_API.G_MISS_NUM)
2204 THEN
2205 x_orlv_rec.last_update_login := l_orlv_rec.last_update_login;
2206 END IF;
2207 RETURN(l_return_status);
2208 END populate_new_record;
2209 ----------------------------------------
2210 -- Set_Attributes for:OKL_OPT_RULES_V --
2211 ----------------------------------------
2212 FUNCTION Set_Attributes (
2213 p_orlv_rec IN orlv_rec_type,
2214 x_orlv_rec OUT NOCOPY orlv_rec_type
2215 ) RETURN VARCHAR2 IS
2216 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2217 BEGIN
2218 x_orlv_rec := p_orlv_rec;
2219 x_orlv_rec.OBJECT_VERSION_NUMBER := NVL(x_orlv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2220 RETURN(l_return_status);
2221 END Set_Attributes;
2222 BEGIN
2223 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2224 G_PKG_NAME,
2225 p_init_msg_list,
2226 l_api_version,
2227 p_api_version,
2228 '_PVT',
2229 x_return_status);
2230 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2231 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2232 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2233 RAISE OKC_API.G_EXCEPTION_ERROR;
2234 END IF;
2235 --- Setting item attributes
2236 l_return_status := Set_Attributes(
2237 p_orlv_rec, -- IN
2238 l_orlv_rec); -- OUT
2239 --- If any errors happen abort API
2240 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2241 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2242 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2243 RAISE OKC_API.G_EXCEPTION_ERROR;
2244 END IF;
2245 l_return_status := populate_new_record(l_orlv_rec, l_def_orlv_rec);
2246 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2247 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2248 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2249 RAISE OKC_API.G_EXCEPTION_ERROR;
2250 END IF;
2251 l_def_orlv_rec := fill_who_columns(l_def_orlv_rec);
2252 --- Validate all non-missing attributes (Item Level Validation)
2253 l_return_status := Validate_Attributes(l_def_orlv_rec);
2254 --- If any errors happen abort API
2255 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2256 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2257 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2258 RAISE OKC_API.G_EXCEPTION_ERROR;
2259 END IF;
2260 l_return_status := Validate_Record(l_def_orlv_rec);
2261 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2262 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2263 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2264 RAISE OKC_API.G_EXCEPTION_ERROR;
2265 END IF;
2266
2267 --------------------------------------
2268 -- Move VIEW record to "Child" records
2269 --------------------------------------
2270 migrate(l_def_orlv_rec, l_orl_rec);
2271 --------------------------------------------
2272 -- Call the UPDATE_ROW for each child record
2273 --------------------------------------------
2274 update_row(
2275 p_init_msg_list,
2276 x_return_status,
2277 x_msg_count,
2278 x_msg_data,
2279 l_orl_rec,
2280 lx_orl_rec
2281 );
2282 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2283 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2284 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2285 RAISE OKC_API.G_EXCEPTION_ERROR;
2286 END IF;
2287 migrate(lx_orl_rec, l_def_orlv_rec);
2288 x_orlv_rec := l_def_orlv_rec;
2289 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2290 EXCEPTION
2291 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2292 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2293 (
2294 l_api_name,
2295 G_PKG_NAME,
2296 'OKC_API.G_RET_STS_ERROR',
2297 x_msg_count,
2298 x_msg_data,
2299 '_PVT'
2300 );
2301 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2302 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2303 (
2304 l_api_name,
2305 G_PKG_NAME,
2306 'OKC_API.G_RET_STS_UNEXP_ERROR',
2307 x_msg_count,
2308 x_msg_data,
2309 '_PVT'
2310 );
2311 WHEN OTHERS THEN
2312 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2313 (
2314 l_api_name,
2315 G_PKG_NAME,
2316 'OTHERS',
2317 x_msg_count,
2318 x_msg_data,
2319 '_PVT'
2320 );
2321 END update_row;
2322 ----------------------------------------
2323 -- PL/SQL TBL update_row for:ORLV_TBL --
2324 ----------------------------------------
2325 PROCEDURE update_row(
2326 p_api_version IN NUMBER,
2327 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2328 x_return_status OUT NOCOPY VARCHAR2,
2329 x_msg_count OUT NOCOPY NUMBER,
2330 x_msg_data OUT NOCOPY VARCHAR2,
2331 p_orlv_tbl IN orlv_tbl_type,
2332 x_orlv_tbl OUT NOCOPY orlv_tbl_type) IS
2333
2334 l_api_version CONSTANT NUMBER := 1;
2335 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2336 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2337 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS; --TCHGS
2338 i NUMBER := 0;
2339 BEGIN
2340 OKC_API.init_msg_list(p_init_msg_list);
2341 -- Make sure PL/SQL table has records in it before passing
2342 IF (p_orlv_tbl.COUNT > 0) THEN
2343 i := p_orlv_tbl.FIRST;
2344 LOOP
2345 update_row (
2346 p_api_version => p_api_version,
2347 p_init_msg_list => OKC_API.G_FALSE,
2348 x_return_status => x_return_status,
2349 x_msg_count => x_msg_count,
2350 x_msg_data => x_msg_data,
2351 p_orlv_rec => p_orlv_tbl(i),
2352 x_orlv_rec => x_orlv_tbl(i));
2353 -- TCHGS: Store the highest degree of error
2354 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2355 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
2356 l_overall_status := x_return_status;
2357 END IF;
2358 END IF;
2359 EXIT WHEN (i = p_orlv_tbl.LAST);
2360 i := p_orlv_tbl.NEXT(i);
2361 END LOOP;
2362 --TCHGS: return overall status
2363 x_return_status := l_overall_status;
2364 END IF;
2365 EXCEPTION
2366 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2367 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2368 (
2369 l_api_name,
2370 G_PKG_NAME,
2371 'OKC_API.G_RET_STS_ERROR',
2372 x_msg_count,
2373 x_msg_data,
2374 '_PVT'
2375 );
2376 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2377 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2378 (
2379 l_api_name,
2380 G_PKG_NAME,
2381 'OKC_API.G_RET_STS_UNEXP_ERROR',
2382 x_msg_count,
2383 x_msg_data,
2384 '_PVT'
2385 );
2386 WHEN OTHERS THEN
2387 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2388 (
2389 l_api_name,
2390 G_PKG_NAME,
2391 'OTHERS',
2392 x_msg_count,
2393 x_msg_data,
2394 '_PVT'
2395 );
2396 END update_row;
2397
2398 ---------------------------------------------------------------------------
2399 -- PROCEDURE delete_row
2400 ---------------------------------------------------------------------------
2401 ----------------------------------
2402 -- delete_row for:OKL_OPT_RULES --
2403 ----------------------------------
2404 PROCEDURE delete_row(
2405 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2406 x_return_status OUT NOCOPY VARCHAR2,
2407 x_msg_count OUT NOCOPY NUMBER,
2408 x_msg_data OUT NOCOPY VARCHAR2,
2409 p_orl_rec IN orl_rec_type) IS
2410
2411 l_api_version CONSTANT NUMBER := 1;
2412 l_api_name CONSTANT VARCHAR2(30) := 'RULES_delete_row';
2413 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2414 l_orl_rec orl_rec_type:= p_orl_rec;
2415 l_row_notfound BOOLEAN := TRUE;
2416 BEGIN
2417 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2418 p_init_msg_list,
2419 '_PVT',
2420 x_return_status);
2421 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2422 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2423 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2424 RAISE OKC_API.G_EXCEPTION_ERROR;
2425 END IF;
2426 DELETE FROM OKL_OPT_RULES
2427 WHERE ID = l_orl_rec.id;
2428
2429 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2430 EXCEPTION
2431 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2432 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2433 (
2434 l_api_name,
2435 G_PKG_NAME,
2436 'OKC_API.G_RET_STS_ERROR',
2437 x_msg_count,
2438 x_msg_data,
2439 '_PVT'
2440 );
2441 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2442 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2443 (
2444 l_api_name,
2445 G_PKG_NAME,
2446 'OKC_API.G_RET_STS_UNEXP_ERROR',
2447 x_msg_count,
2448 x_msg_data,
2449 '_PVT'
2450 );
2451 WHEN OTHERS THEN
2452 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2453 (
2454 l_api_name,
2455 G_PKG_NAME,
2456 'OTHERS',
2457 x_msg_count,
2458 x_msg_data,
2459 '_PVT'
2460 );
2461 END delete_row;
2462 ------------------------------------
2463 -- delete_row for:OKL_OPT_RULES_V --
2464 ------------------------------------
2465 PROCEDURE delete_row(
2466 p_api_version IN NUMBER,
2467 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2468 x_return_status OUT NOCOPY VARCHAR2,
2469 x_msg_count OUT NOCOPY NUMBER,
2470 x_msg_data OUT NOCOPY VARCHAR2,
2471 p_orlv_rec IN orlv_rec_type) IS
2472
2473 l_api_version CONSTANT NUMBER := 1;
2474 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2475 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2476 l_orlv_rec orlv_rec_type := p_orlv_rec;
2477 l_orl_rec orl_rec_type;
2478 BEGIN
2479 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2480 G_PKG_NAME,
2481 p_init_msg_list,
2482 l_api_version,
2483 p_api_version,
2484 '_PVT',
2485 x_return_status);
2486 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2487 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2488 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2489 RAISE OKC_API.G_EXCEPTION_ERROR;
2490 END IF;
2491 --------------------------------------
2492 -- Move VIEW record to "Child" records
2493 --------------------------------------
2494 migrate(l_orlv_rec, l_orl_rec);
2495 --------------------------------------------
2496 -- Call the DELETE_ROW for each child record
2497 --------------------------------------------
2498 delete_row(
2499 p_init_msg_list,
2500 x_return_status,
2501 x_msg_count,
2502 x_msg_data,
2503 l_orl_rec
2504 );
2505 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2506 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2507 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2508 RAISE OKC_API.G_EXCEPTION_ERROR;
2509 END IF;
2510 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2511 EXCEPTION
2512 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2513 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2514 (
2515 l_api_name,
2516 G_PKG_NAME,
2517 'OKC_API.G_RET_STS_ERROR',
2518 x_msg_count,
2519 x_msg_data,
2520 '_PVT'
2521 );
2522 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2523 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2524 (
2525 l_api_name,
2526 G_PKG_NAME,
2527 'OKC_API.G_RET_STS_UNEXP_ERROR',
2528 x_msg_count,
2529 x_msg_data,
2530 '_PVT'
2531 );
2532 WHEN OTHERS THEN
2533 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2534 (
2535 l_api_name,
2536 G_PKG_NAME,
2537 'OTHERS',
2538 x_msg_count,
2539 x_msg_data,
2540 '_PVT'
2541 );
2542 END delete_row;
2543 ----------------------------------------
2544 -- PL/SQL TBL delete_row for:ORLV_TBL --
2545 ----------------------------------------
2546 PROCEDURE delete_row(
2547 p_api_version IN NUMBER,
2548 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
2549 x_return_status OUT NOCOPY VARCHAR2,
2550 x_msg_count OUT NOCOPY NUMBER,
2551 x_msg_data OUT NOCOPY VARCHAR2,
2552 p_orlv_tbl IN orlv_tbl_type) IS
2553
2554 l_api_version CONSTANT NUMBER := 1;
2555 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2556 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2557 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS; --TCHGS
2558 i NUMBER := 0;
2559 BEGIN
2560 OKC_API.init_msg_list(p_init_msg_list);
2561 -- Make sure PL/SQL table has records in it before passing
2562 IF (p_orlv_tbl.COUNT > 0) THEN
2563 i := p_orlv_tbl.FIRST;
2564 LOOP
2565 delete_row (
2566 p_api_version => p_api_version,
2567 p_init_msg_list => OKC_API.G_FALSE,
2568 x_return_status => x_return_status,
2569 x_msg_count => x_msg_count,
2570 x_msg_data => x_msg_data,
2571 p_orlv_rec => p_orlv_tbl(i));
2572 -- TCHGS: Store the highest degree of error
2573 IF x_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
2574 IF l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR THEN
2575 l_overall_status := x_return_status;
2576 END IF;
2577 END IF;
2578 EXIT WHEN (i = p_orlv_tbl.LAST);
2579 i := p_orlv_tbl.NEXT(i);
2580 END LOOP;
2581 --TCHGS: return overall status
2582 x_return_status := l_overall_status;
2583 END IF;
2584 EXCEPTION
2585 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2586 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2587 (
2588 l_api_name,
2589 G_PKG_NAME,
2590 'OKC_API.G_RET_STS_ERROR',
2591 x_msg_count,
2592 x_msg_data,
2593 '_PVT'
2594 );
2595 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2596 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2597 (
2598 l_api_name,
2599 G_PKG_NAME,
2600 'OKC_API.G_RET_STS_UNEXP_ERROR',
2601 x_msg_count,
2602 x_msg_data,
2603 '_PVT'
2604 );
2605 WHEN OTHERS THEN
2606 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2607 (
2608 l_api_name,
2609 G_PKG_NAME,
2610 'OTHERS',
2611 x_msg_count,
2612 x_msg_data,
2613 '_PVT'
2614 );
2615 END delete_row;
2616 END OKL_ORL_PVT;