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