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