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