[Home] [Help]
PACKAGE BODY: APPS.OKC_ACY_PVT
Source
1 PACKAGE BODY OKC_ACY_PVT AS
2 /* $Header: OKCSACYB.pls 120.0 2005/05/26 09:28:25 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 /************************ HAND-CODED *********************************/
7 FUNCTION Validate_Attributes ( p_acyv_rec IN acyv_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) := 'SQLerrm';
12 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
13 G_VIEW CONSTANT VARCHAR2(200) := 'OKC_ANCESTRYS_V';
14 G_EXCEPTION_HALT_VALIDATION exception;
15
16 -- Start of comments
17 --
18 -- Procedure Name : validate_cle_id
19 -- Description :
20 -- Business Rules :
21 -- Parameters :
22 -- Version : 1.0
23 -- End of comments
24 PROCEDURE validate_cle_id(x_return_status OUT NOCOPY VARCHAR2,
25 p_acyv_rec IN acyv_rec_type) is
26
27 l_dummy_var VARCHAR2(1) := '?';
28 Cursor l_clev_csr Is
29 select 'x'
30 from OKC_K_LINES_B
31 where ID = p_acyv_rec.cle_id;
32 Begin
33 -- initialize return status
34 x_return_status := OKC_API.G_RET_STS_SUCCESS;
35
36 -- check that data exists
37 If (p_acyv_rec.cle_id = OKC_API.G_MISS_NUM or
38 p_acyv_rec.cle_id IS NULL)
39 Then
40 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
41 p_msg_name => g_required_value,
42 p_token1 => g_col_name_token,
43 p_token1_value => 'cle_id');
44 -- set error flag
45 x_return_status := OKC_API.G_RET_STS_ERROR;
46
47 -- halt validation
48 raise G_EXCEPTION_HALT_VALIDATION;
49 End If;
50
51 -- enforce foreign key
52 Open l_clev_csr;
53 Fetch l_clev_csr Into l_dummy_var;
54 Close l_clev_csr;
55
56 -- if l_dummy_var still set to default, data was not found
57 If (l_dummy_var = '?') Then
61 p_token1_value => 'cle_id',
58 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
59 p_msg_name => G_NO_PARENT_RECORD,
60 p_token1 => G_COL_NAME_TOKEN,
62 p_token2 => G_CHILD_TABLE_TOKEN,
63 p_token2_value => G_VIEW,
64 p_token3 => G_PARENT_TABLE_TOKEN,
65 p_token3_value => 'OKC_K_LINES_V');
66 -- notify caller of an error
67 x_return_status := OKC_API.G_RET_STS_ERROR;
68 End If;
69 exception
70 when G_EXCEPTION_HALT_VALIDATION then
71 -- no processing necessary; validation can continue with next column
72 null;
73
74 when OTHERS then
75 -- store SQL error message on message stack
76 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
77 p_msg_name => g_unexpected_error,
78 p_token1 => g_sqlcode_token,
79 p_token1_value => sqlcode,
80 p_token2 => g_sqlerrm_token,
81 p_token2_value => sqlerrm);
82 -- set error flag as UNEXPETED error
83 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
84
85 -- verify that cursor was closed
86 if l_clev_csr%ISOPEN then
87 close l_clev_csr;
88 end if;
89
90 End validate_cle_id;
91
92 -- Start of comments
93 --
94 -- Procedure Name : validate_cle_id_ascendant
95 -- Description :
96 -- Business Rules :
97 -- Parameters :
98 -- Version : 1.0
99 -- End of comments
100 PROCEDURE validate_cle_id_ascendant(x_return_status OUT NOCOPY VARCHAR2,
101 p_acyv_rec IN acyv_rec_type) is
102
103 l_dummy_var VARCHAR2(1) := '?';
104 Cursor l_clev_csr Is
105 select 'x'
106 from OKC_K_LINES_B
107 where ID = p_acyv_rec.cle_id_ascendant;
108
109 Begin
110 -- initialize return status
111 x_return_status := OKC_API.G_RET_STS_SUCCESS;
112
113 -- check that data exists
114 If (p_acyv_rec.cle_id_ascendant = OKC_API.G_MISS_NUM or
115 p_acyv_rec.cle_id_ascendant IS NULL)
116 Then
117 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
118 p_msg_name => g_required_value,
119 p_token1 => g_col_name_token,
120 p_token1_value => 'cle_id_ascendant');
121 -- set error flag
122 x_return_status := OKC_API.G_RET_STS_ERROR;
123
124 -- halt validation
125 raise G_EXCEPTION_HALT_VALIDATION;
126 End If;
127
128 -- enforce foreign key
129 Open l_clev_csr;
130 Fetch l_clev_csr Into l_dummy_var;
131 Close l_clev_csr;
132
133 -- if l_dummy_var still set to default, data was not found
134 If (l_dummy_var = '?') Then
135 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
136 p_msg_name => g_no_parent_record,
137 p_token1 => g_col_name_token,
138 p_token1_value => 'cle_id_ascendant',
139 p_token2 => g_child_table_token,
140 p_token2_value => G_VIEW,
141 p_token3 => g_parent_table_token,
142 p_token3_value => 'OKC_K_LINES_V');
143 -- notify caller of an error
144 x_return_status := OKC_API.G_RET_STS_ERROR;
145 End If;
146
147 exception
148 when G_EXCEPTION_HALT_VALIDATION then
149 -- no processing necessary; validation can continue with next column
150 null;
151
152 when OTHERS then
153 -- store SQL error message on message stack
154 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
155 p_msg_name => g_unexpected_error,
156 p_token1 => g_sqlcode_token,
157 p_token1_value => sqlcode,
158 p_token2 => g_sqlerrm_token,
159 p_token2_value => sqlerrm);
160 -- set error flag as UNEXPETED error
161 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
162
163 -- verify that cursor was closed
164 if l_clev_csr%ISOPEN then
165 close l_clev_csr;
166 end if;
167
168 End validate_cle_id_ascendant;
169
170 -- Start of comments
171 --
172 -- Procedure Name : validate_level_sequence
173 -- Description :
174 -- Business Rules :
175 -- Parameters :
176 -- Version : 1.0
177 -- End of comments
178 PROCEDURE validate_level_sequence(x_return_status OUT NOCOPY VARCHAR2,
179 p_acyv_rec IN acyv_rec_type) is
180
181 Begin
182 -- initialize return status
183 x_return_status := OKC_API.G_RET_STS_SUCCESS;
184
185 -- check that data exists
186 If (p_acyv_rec.level_sequence = OKC_API.G_MISS_NUM or
187 p_acyv_rec.level_sequence IS NULL)
188 Then
189 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
190 p_msg_name => g_required_value,
191 p_token1 => g_col_name_token,
192 p_token1_value => 'level_sequence');
193 -- set error flag
194 x_return_status := OKC_API.G_RET_STS_ERROR;
195
196 -- halt validation
197 raise G_EXCEPTION_HALT_VALIDATION;
198 End If;
199
200 exception
201 when G_EXCEPTION_HALT_VALIDATION then
202 -- no processing necessary; validation can continue with next column
203 null;
204
205 when OTHERS then
206 -- store SQL error message on message stack
207 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
208 p_msg_name => g_unexpected_error,
209 p_token1 => g_sqlcode_token,
210 p_token1_value => sqlcode,
211 p_token2 => g_sqlerrm_token,
215 End validate_level_sequence;
212 p_token2_value => sqlerrm);
213 -- set error flag as UNEXPETED error
214 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
216
217 /*********************** END HAND-CODED ********************************/
218 ---------------------------------------------------------------------------
219 -- FUNCTION get_seq_id
220 ---------------------------------------------------------------------------
221 FUNCTION get_seq_id RETURN NUMBER IS
222 BEGIN
223 RETURN(okc_p_util.raw_to_number(sys_guid()));
224 END get_seq_id;
225
226 ---------------------------------------------------------------------------
227 -- PROCEDURE qc
228 ---------------------------------------------------------------------------
229 PROCEDURE qc IS
230 BEGIN
231 null;
232 END qc;
233
234 ---------------------------------------------------------------------------
235 -- PROCEDURE change_version
236 ---------------------------------------------------------------------------
237 PROCEDURE change_version IS
238 BEGIN
239 null;
240 END change_version;
241
242 ---------------------------------------------------------------------------
243 -- PROCEDURE api_copy
244 ---------------------------------------------------------------------------
245 PROCEDURE api_copy IS
246 BEGIN
247 null;
248 END api_copy;
249
250 ---------------------------------------------------------------------------
251 -- FUNCTION get_rec for: OKC_ANCESTRYS
252 ---------------------------------------------------------------------------
253 FUNCTION get_rec (
254 p_acy_rec IN acy_rec_type,
255 x_no_data_found OUT NOCOPY BOOLEAN
256 ) RETURN acy_rec_type IS
257 CURSOR acy_pk_csr (p_cle_id IN NUMBER,
258 p_cle_id_ascendant IN NUMBER) IS
259 SELECT
260 CLE_ID,
261 CLE_ID_ASCENDANT,
262 OBJECT_VERSION_NUMBER,
263 LEVEL_SEQUENCE,
264 CREATED_BY,
265 CREATION_DATE,
266 LAST_UPDATED_BY,
267 LAST_UPDATE_DATE,
268 LAST_UPDATE_LOGIN
269 FROM Okc_Ancestrys
270 WHERE okc_ancestrys.cle_id = p_cle_id
271 AND okc_ancestrys.cle_id_ascendant = p_cle_id_ascendant;
272 l_acy_pk acy_pk_csr%ROWTYPE;
273 l_acy_rec acy_rec_type;
274 BEGIN
275 x_no_data_found := TRUE;
276 -- Get current database values
277 OPEN acy_pk_csr (p_acy_rec.cle_id,
278 p_acy_rec.cle_id_ascendant);
279 FETCH acy_pk_csr INTO
280 l_acy_rec.CLE_ID,
281 l_acy_rec.CLE_ID_ASCENDANT,
282 l_acy_rec.OBJECT_VERSION_NUMBER,
283 l_acy_rec.LEVEL_SEQUENCE,
284 l_acy_rec.CREATED_BY,
285 l_acy_rec.CREATION_DATE,
286 l_acy_rec.LAST_UPDATED_BY,
287 l_acy_rec.LAST_UPDATE_DATE,
288 l_acy_rec.LAST_UPDATE_LOGIN;
289 x_no_data_found := acy_pk_csr%NOTFOUND;
290 CLOSE acy_pk_csr;
291 RETURN(l_acy_rec);
292 END get_rec;
293
294 FUNCTION get_rec (
295 p_acy_rec IN acy_rec_type
296 ) RETURN acy_rec_type IS
297 l_row_notfound BOOLEAN := TRUE;
298 BEGIN
299 RETURN(get_rec(p_acy_rec, l_row_notfound));
300 END get_rec;
301 ---------------------------------------------------------------------------
302 -- FUNCTION get_rec for: OKC_ANCESTRYS_V
303 ---------------------------------------------------------------------------
304 FUNCTION get_rec (
305 p_acyv_rec IN acyv_rec_type,
306 x_no_data_found OUT NOCOPY BOOLEAN
307 ) RETURN acyv_rec_type IS
308 CURSOR okc_acyv_pk_csr (p_cle_id IN NUMBER,
309 p_cle_id_ascendant IN NUMBER) IS
310 SELECT
311 CLE_ID,
312 CLE_ID_ASCENDANT,
313 OBJECT_VERSION_NUMBER,
314 LEVEL_SEQUENCE,
315 CREATED_BY,
316 CREATION_DATE,
317 LAST_UPDATED_BY,
318 LAST_UPDATE_DATE,
319 LAST_UPDATE_LOGIN
320 FROM Okc_Ancestrys_V
321 WHERE okc_ancestrys_v.cle_id = p_cle_id
322 AND okc_ancestrys_v.cle_id_ascendant = p_cle_id_ascendant;
323 l_okc_acyv_pk okc_acyv_pk_csr%ROWTYPE;
324 l_acyv_rec acyv_rec_type;
325 BEGIN
326 x_no_data_found := TRUE;
327 -- Get current database values
328 OPEN okc_acyv_pk_csr (p_acyv_rec.cle_id,
329 p_acyv_rec.cle_id_ascendant);
330 FETCH okc_acyv_pk_csr INTO
331 l_acyv_rec.CLE_ID,
332 l_acyv_rec.CLE_ID_ASCENDANT,
333 l_acyv_rec.OBJECT_VERSION_NUMBER,
334 l_acyv_rec.LEVEL_SEQUENCE,
335 l_acyv_rec.CREATED_BY,
336 l_acyv_rec.CREATION_DATE,
337 l_acyv_rec.LAST_UPDATED_BY,
338 l_acyv_rec.LAST_UPDATE_DATE,
339 l_acyv_rec.LAST_UPDATE_LOGIN;
340 x_no_data_found := okc_acyv_pk_csr%NOTFOUND;
341 CLOSE okc_acyv_pk_csr;
342 RETURN(l_acyv_rec);
343 END get_rec;
344
345 FUNCTION get_rec (
346 p_acyv_rec IN acyv_rec_type
347 ) RETURN acyv_rec_type IS
348 l_row_notfound BOOLEAN := TRUE;
349 BEGIN
353 -----------------------------------------------------
350 RETURN(get_rec(p_acyv_rec, l_row_notfound));
351 END get_rec;
352
354 -- FUNCTION null_out_defaults for: OKC_ANCESTRYS_V --
355 -----------------------------------------------------
356 FUNCTION null_out_defaults (
357 p_acyv_rec IN acyv_rec_type
358 ) RETURN acyv_rec_type IS
359 l_acyv_rec acyv_rec_type := p_acyv_rec;
360 BEGIN
361 IF (l_acyv_rec.cle_id = OKC_API.G_MISS_NUM) THEN
362 l_acyv_rec.cle_id := NULL;
363 END IF;
364 IF (l_acyv_rec.cle_id_ascendant = OKC_API.G_MISS_NUM) THEN
365 l_acyv_rec.cle_id_ascendant := NULL;
366 END IF;
367 IF (l_acyv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
368 l_acyv_rec.object_version_number := NULL;
369 END IF;
370 IF (l_acyv_rec.level_sequence = OKC_API.G_MISS_NUM) THEN
371 l_acyv_rec.level_sequence := NULL;
372 END IF;
373 IF (l_acyv_rec.created_by = OKC_API.G_MISS_NUM) THEN
374 l_acyv_rec.created_by := NULL;
375 END IF;
376 IF (l_acyv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
377 l_acyv_rec.creation_date := NULL;
378 END IF;
379 IF (l_acyv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
380 l_acyv_rec.last_updated_by := NULL;
381 END IF;
382 IF (l_acyv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
383 l_acyv_rec.last_update_date := NULL;
384 END IF;
385 IF (l_acyv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
386 l_acyv_rec.last_update_login := NULL;
387 END IF;
388 RETURN(l_acyv_rec);
389 END null_out_defaults;
390 ---------------------------------------------------------------------------
391 -- PROCEDURE Validate_Attributes
392 ---------------------------------------------------------------------------
393 ---------------------------------------------
394 -- Validate_Attributes for:OKC_ANCESTRYS_V --
395 ---------------------------------------------
396 FUNCTION Validate_Attributes (
397 p_acyv_rec IN acyv_rec_type
398 ) RETURN VARCHAR2 IS
399 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
400 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
401 BEGIN
402 /************************ HAND-CODED *********************************/
403 validate_cle_id
404 (x_return_status => l_return_status,
405 p_acyv_rec => p_acyv_rec);
406
407 -- store the highest degree of error
408 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
409 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
410 x_return_status := l_return_status;
411 End If;
412 End If;
413
414 validate_cle_id_ascendant
415 (x_return_status => l_return_status,
416 p_acyv_rec => p_acyv_rec);
417
418 -- store the highest degree of error
419 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
420 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
421 x_return_status := l_return_status;
422 End If;
423 End If;
424
425 validate_level_sequence
426 (x_return_status => l_return_status,
427 p_acyv_rec => p_acyv_rec);
428
429 -- store the highest degree of error
430 If l_return_status <> OKC_API.G_RET_STS_SUCCESS Then
431 If x_return_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
432 x_return_status := l_return_status;
433 End If;
434 End If;
435
436 RETURN(x_return_status);
437 exception
438 when OTHERS then
439 -- store SQL error message on message stack
440 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
441 p_msg_name => g_unexpected_error,
442 p_token1 => g_sqlcode_token,
443 p_token1_value => sqlcode,
444 p_token2 => g_sqlerrm_token,
445 p_token2_value => sqlerrm);
446
447 -- notify caller of an UNEXPETED error
448 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
449
450 -- return status to caller
451 RETURN(x_return_status);
452 /*********************** END HAND-CODED ********************************/
453 END Validate_Attributes;
454
455 ---------------------------------------------------------------------------
456 -- PROCEDURE Validate_Record
457 ---------------------------------------------------------------------------
458 -----------------------------------------
459 -- Validate_Record for:OKC_ANCESTRYS_V --
460 -----------------------------------------
461 FUNCTION Validate_Record (
462 p_acyv_rec IN acyv_rec_type
463 ) RETURN VARCHAR2 IS
464 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
465 BEGIN
466 RETURN (l_return_status);
467 END Validate_Record;
468
469 ---------------------------------------------------------------------------
470 -- PROCEDURE Migrate
471 ---------------------------------------------------------------------------
472 PROCEDURE migrate (
473 p_from IN acyv_rec_type,
474 p_to IN OUT NOCOPY acy_rec_type
475 ) IS
476 BEGIN
477 p_to.cle_id := p_from.cle_id;
478 p_to.cle_id_ascendant := p_from.cle_id_ascendant;
479 p_to.object_version_number := p_from.object_version_number;
480 p_to.level_sequence := p_from.level_sequence;
481 p_to.created_by := p_from.created_by;
482 p_to.creation_date := p_from.creation_date;
483 p_to.last_updated_by := p_from.last_updated_by;
484 p_to.last_update_date := p_from.last_update_date;
485 p_to.last_update_login := p_from.last_update_login;
486 END migrate;
487 PROCEDURE migrate (
488 p_from IN acy_rec_type,
489 p_to IN OUT NOCOPY acyv_rec_type
490 ) IS
491 BEGIN
492 p_to.cle_id := p_from.cle_id;
496 p_to.created_by := p_from.created_by;
493 p_to.cle_id_ascendant := p_from.cle_id_ascendant;
494 p_to.object_version_number := p_from.object_version_number;
495 p_to.level_sequence := p_from.level_sequence;
497 p_to.creation_date := p_from.creation_date;
498 p_to.last_updated_by := p_from.last_updated_by;
499 p_to.last_update_date := p_from.last_update_date;
500 p_to.last_update_login := p_from.last_update_login;
501 END migrate;
502
503 ---------------------------------------------------------------------------
504 -- PROCEDURE validate_row
505 ---------------------------------------------------------------------------
506 --------------------------------------
507 -- validate_row for:OKC_ANCESTRYS_V --
508 --------------------------------------
509 PROCEDURE validate_row(
510 p_api_version IN NUMBER,
511 p_init_msg_list IN VARCHAR2 ,
512 x_return_status OUT NOCOPY VARCHAR2,
513 x_msg_count OUT NOCOPY NUMBER,
514 x_msg_data OUT NOCOPY VARCHAR2,
515 p_acyv_rec IN acyv_rec_type) IS
516
517 l_api_version CONSTANT NUMBER := 1;
518 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
519 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
520 l_acyv_rec acyv_rec_type := p_acyv_rec;
521 l_acy_rec acy_rec_type;
522 BEGIN
523 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
524 G_PKG_NAME,
525 p_init_msg_list,
526 l_api_version,
527 p_api_version,
528 '_PVT',
529 x_return_status);
530 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
531 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
532 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
533 RAISE OKC_API.G_EXCEPTION_ERROR;
534 END IF;
535 --- Validate all non-missing attributes (Item Level Validation)
536 l_return_status := Validate_Attributes(l_acyv_rec);
537 --- If any errors happen abort API
538 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
539 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
540 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
541 RAISE OKC_API.G_EXCEPTION_ERROR;
542 END IF;
543 l_return_status := Validate_Record(l_acyv_rec);
544 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
545 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
546 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
547 RAISE OKC_API.G_EXCEPTION_ERROR;
548 END IF;
549 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
550 EXCEPTION
551 WHEN OKC_API.G_EXCEPTION_ERROR THEN
552 x_return_status := OKC_API.HANDLE_EXCEPTIONS
553 (
554 l_api_name,
555 G_PKG_NAME,
556 'OKC_API.G_RET_STS_ERROR',
557 x_msg_count,
558 x_msg_data,
559 '_PVT'
560 );
561 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
562 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
563 (
564 l_api_name,
565 G_PKG_NAME,
566 'OKC_API.G_RET_STS_UNEXP_ERROR',
567 x_msg_count,
568 x_msg_data,
569 '_PVT'
570 );
571 WHEN OTHERS THEN
572 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
573 (
574 l_api_name,
575 G_PKG_NAME,
576 'OTHERS',
577 x_msg_count,
578 x_msg_data,
579 '_PVT'
580 );
581 END validate_row;
582 ------------------------------------------
583 -- PL/SQL TBL validate_row for:ACYV_TBL --
584 ------------------------------------------
585 PROCEDURE validate_row(
586 p_api_version IN NUMBER,
587 p_init_msg_list IN VARCHAR2 ,
588 x_return_status OUT NOCOPY VARCHAR2,
589 x_msg_count OUT NOCOPY NUMBER,
590 x_msg_data OUT NOCOPY VARCHAR2,
591 p_acyv_tbl IN acyv_tbl_type) IS
592
593 l_api_version CONSTANT NUMBER := 1;
594 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
595 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
596 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
597 i NUMBER := 0;
598 BEGIN
599 OKC_API.init_msg_list(p_init_msg_list);
600 -- Make sure PL/SQL table has records in it before passing
601 IF (p_acyv_tbl.COUNT > 0) THEN
602 i := p_acyv_tbl.FIRST;
603 LOOP
604 validate_row (
605 p_api_version => p_api_version,
606 p_init_msg_list => OKC_API.G_FALSE,
607 x_return_status => x_return_status,
608 x_msg_count => x_msg_count,
609 x_msg_data => x_msg_data,
610 p_acyv_rec => p_acyv_tbl(i));
611
612 -- store the highest degree of error
613 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
614 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
615 l_overall_status := x_return_status;
616 End If;
617 End If;
618
619 EXIT WHEN (i = p_acyv_tbl.LAST);
620 i := p_acyv_tbl.NEXT(i);
621 END LOOP;
622 -- return overall status
623 x_return_status := l_overall_status;
624 END IF;
625 EXCEPTION
629 l_api_name,
626 WHEN OKC_API.G_EXCEPTION_ERROR THEN
627 x_return_status := OKC_API.HANDLE_EXCEPTIONS
628 (
630 G_PKG_NAME,
631 'OKC_API.G_RET_STS_ERROR',
632 x_msg_count,
633 x_msg_data,
634 '_PVT'
635 );
636 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
637 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
638 (
639 l_api_name,
640 G_PKG_NAME,
641 'OKC_API.G_RET_STS_UNEXP_ERROR',
642 x_msg_count,
643 x_msg_data,
644 '_PVT'
645 );
646 WHEN OTHERS THEN
647 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
648 (
649 l_api_name,
650 G_PKG_NAME,
651 'OTHERS',
652 x_msg_count,
653 x_msg_data,
654 '_PVT'
655 );
656 END validate_row;
657
658 ---------------------------------------------------------------------------
659 -- PROCEDURE insert_row
660 ---------------------------------------------------------------------------
661 ----------------------------------
662 -- insert_row for:OKC_ANCESTRYS --
663 ----------------------------------
664 PROCEDURE insert_row(
665 p_init_msg_list IN VARCHAR2 ,
666 x_return_status OUT NOCOPY VARCHAR2,
667 x_msg_count OUT NOCOPY NUMBER,
668 x_msg_data OUT NOCOPY VARCHAR2,
669 p_acy_rec IN acy_rec_type,
670 x_acy_rec OUT NOCOPY acy_rec_type) IS
671
672 l_api_version CONSTANT NUMBER := 1;
673 l_api_name CONSTANT VARCHAR2(30) := 'ANCESTRYS_insert_row';
674 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
675 l_acy_rec acy_rec_type := p_acy_rec;
676 l_def_acy_rec acy_rec_type;
677 --------------------------------------
678 -- Set_Attributes for:OKC_ANCESTRYS --
679 --------------------------------------
680 FUNCTION Set_Attributes (
681 p_acy_rec IN acy_rec_type,
682 x_acy_rec OUT NOCOPY acy_rec_type
683 ) RETURN VARCHAR2 IS
684 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
685 BEGIN
686 x_acy_rec := p_acy_rec;
687 RETURN(l_return_status);
688 END Set_Attributes;
689 BEGIN
690 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
691 p_init_msg_list,
692 '_PVT',
693 x_return_status);
694 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
695 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
696 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
697 RAISE OKC_API.G_EXCEPTION_ERROR;
698 END IF;
699 --- Setting item attributes
700 l_return_status := Set_Attributes(
701 p_acy_rec, -- IN
702 l_acy_rec); -- OUT
703 --- If any errors happen abort API
704 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
705 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
706 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
707 RAISE OKC_API.G_EXCEPTION_ERROR;
708 END IF;
709 INSERT INTO OKC_ANCESTRYS(
710 cle_id,
711 cle_id_ascendant,
712 object_version_number,
713 level_sequence,
714 created_by,
715 creation_date,
716 last_updated_by,
717 last_update_date,
718 last_update_login)
719 VALUES (
720 l_acy_rec.cle_id,
721 l_acy_rec.cle_id_ascendant,
722 l_acy_rec.object_version_number,
723 l_acy_rec.level_sequence,
724 l_acy_rec.created_by,
725 l_acy_rec.creation_date,
726 l_acy_rec.last_updated_by,
727 l_acy_rec.last_update_date,
728 l_acy_rec.last_update_login);
729 -- Set OUT values
730 x_acy_rec := l_acy_rec;
731 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
732 EXCEPTION
733 WHEN OKC_API.G_EXCEPTION_ERROR THEN
734 x_return_status := OKC_API.HANDLE_EXCEPTIONS
735 (
736 l_api_name,
737 G_PKG_NAME,
738 'OKC_API.G_RET_STS_ERROR',
739 x_msg_count,
740 x_msg_data,
741 '_PVT'
742 );
743 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
744 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
745 (
746 l_api_name,
747 G_PKG_NAME,
748 'OKC_API.G_RET_STS_UNEXP_ERROR',
749 x_msg_count,
750 x_msg_data,
751 '_PVT'
752 );
753 WHEN OTHERS THEN
754 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
755 (
756 l_api_name,
757 G_PKG_NAME,
758 'OTHERS',
759 x_msg_count,
760 x_msg_data,
761 '_PVT'
762 );
763 END insert_row;
764 ------------------------------------
765 -- insert_row for:OKC_ANCESTRYS_V --
766 ------------------------------------
767 PROCEDURE insert_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_acyv_rec IN acyv_rec_type,
774 x_acyv_rec OUT NOCOPY acyv_rec_type) IS
775
776 l_api_version CONSTANT NUMBER := 1;
780 l_def_acyv_rec acyv_rec_type;
777 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
778 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
779 l_acyv_rec acyv_rec_type;
781 l_acy_rec acy_rec_type;
782 lx_acy_rec acy_rec_type;
783 -------------------------------
784 -- FUNCTION fill_who_columns --
785 -------------------------------
786 FUNCTION fill_who_columns (
787 p_acyv_rec IN acyv_rec_type
788 ) RETURN acyv_rec_type IS
789 l_acyv_rec acyv_rec_type := p_acyv_rec;
790 BEGIN
791 l_acyv_rec.CREATION_DATE := SYSDATE;
792 l_acyv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
793 l_acyv_rec.LAST_UPDATE_DATE := l_acyv_rec.CREATION_DATE;
794 l_acyv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
795 l_acyv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
796 RETURN(l_acyv_rec);
797 END fill_who_columns;
798 ----------------------------------------
799 -- Set_Attributes for:OKC_ANCESTRYS_V --
800 ----------------------------------------
801 FUNCTION Set_Attributes (
802 p_acyv_rec IN acyv_rec_type,
803 x_acyv_rec OUT NOCOPY acyv_rec_type
804 ) RETURN VARCHAR2 IS
805 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
806 BEGIN
807 x_acyv_rec := p_acyv_rec;
808 x_acyv_rec.OBJECT_VERSION_NUMBER := 1;
809 RETURN(l_return_status);
810 END Set_Attributes;
811 BEGIN
812 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
813 G_PKG_NAME,
814 p_init_msg_list,
815 l_api_version,
816 p_api_version,
817 '_PVT',
818 x_return_status);
819 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
820 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
821 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
822 RAISE OKC_API.G_EXCEPTION_ERROR;
823 END IF;
824 l_acyv_rec := null_out_defaults(p_acyv_rec);
825 --- Setting item attributes
826 l_return_status := Set_Attributes(
827 l_acyv_rec, -- IN
828 l_def_acyv_rec); -- OUT
829 --- If any errors happen abort API
830 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
831 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
832 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
833 RAISE OKC_API.G_EXCEPTION_ERROR;
834 END IF;
835 l_def_acyv_rec := fill_who_columns(l_def_acyv_rec);
836 --- Validate all non-missing attributes (Item Level Validation)
837 l_return_status := Validate_Attributes(l_def_acyv_rec);
838 --- If any errors happen abort API
839 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
840 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
841 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
842 RAISE OKC_API.G_EXCEPTION_ERROR;
843 END IF;
844 l_return_status := Validate_Record(l_def_acyv_rec);
845 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
846 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
847 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
848 RAISE OKC_API.G_EXCEPTION_ERROR;
849 END IF;
850 --------------------------------------
851 -- Move VIEW record to "Child" records
852 --------------------------------------
853 migrate(l_def_acyv_rec, l_acy_rec);
854 --------------------------------------------
855 -- Call the INSERT_ROW for each child record
856 --------------------------------------------
857 insert_row(
858 p_init_msg_list,
859 x_return_status,
860 x_msg_count,
861 x_msg_data,
862 l_acy_rec,
863 lx_acy_rec
864 );
865 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
866 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
867 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
868 RAISE OKC_API.G_EXCEPTION_ERROR;
869 END IF;
870 migrate(lx_acy_rec, l_def_acyv_rec);
871 -- Set OUT values
872 x_acyv_rec := l_def_acyv_rec;
873 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
874 EXCEPTION
875 WHEN OKC_API.G_EXCEPTION_ERROR THEN
876 x_return_status := OKC_API.HANDLE_EXCEPTIONS
877 (
878 l_api_name,
879 G_PKG_NAME,
880 'OKC_API.G_RET_STS_ERROR',
881 x_msg_count,
882 x_msg_data,
883 '_PVT'
884 );
885 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
886 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
887 (
888 l_api_name,
889 G_PKG_NAME,
890 'OKC_API.G_RET_STS_UNEXP_ERROR',
891 x_msg_count,
892 x_msg_data,
893 '_PVT'
894 );
895 WHEN OTHERS THEN
896 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
897 (
898 l_api_name,
899 G_PKG_NAME,
900 'OTHERS',
901 x_msg_count,
902 x_msg_data,
903 '_PVT'
904 );
905 END insert_row;
906 ----------------------------------------
907 -- PL/SQL TBL insert_row for:ACYV_TBL --
908 ----------------------------------------
909 PROCEDURE insert_row(
910 p_api_version IN NUMBER,
911 p_init_msg_list IN VARCHAR2 ,
912 x_return_status OUT NOCOPY VARCHAR2,
913 x_msg_count OUT NOCOPY NUMBER,
917
914 x_msg_data OUT NOCOPY VARCHAR2,
915 p_acyv_tbl IN acyv_tbl_type,
916 x_acyv_tbl OUT NOCOPY acyv_tbl_type) IS
918 l_api_version CONSTANT NUMBER := 1;
919 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
920 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
921 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
922 i NUMBER := 0;
923 BEGIN
924 OKC_API.init_msg_list(p_init_msg_list);
925 -- Make sure PL/SQL table has records in it before passing
926 IF (p_acyv_tbl.COUNT > 0) THEN
927 i := p_acyv_tbl.FIRST;
928 LOOP
929 insert_row (
930 p_api_version => p_api_version,
931 p_init_msg_list => OKC_API.G_FALSE,
932 x_return_status => x_return_status,
933 x_msg_count => x_msg_count,
934 x_msg_data => x_msg_data,
935 p_acyv_rec => p_acyv_tbl(i),
936 x_acyv_rec => x_acyv_tbl(i));
937
938 -- store the highest degree of error
939 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
940 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
941 l_overall_status := x_return_status;
942 End If;
943 End If;
944
945 EXIT WHEN (i = p_acyv_tbl.LAST);
946 i := p_acyv_tbl.NEXT(i);
947 END LOOP;
948 -- return overall status
949 x_return_status := l_overall_status;
950 END IF;
951 EXCEPTION
952 WHEN OKC_API.G_EXCEPTION_ERROR THEN
953 x_return_status := OKC_API.HANDLE_EXCEPTIONS
954 (
955 l_api_name,
956 G_PKG_NAME,
957 'OKC_API.G_RET_STS_ERROR',
958 x_msg_count,
959 x_msg_data,
960 '_PVT'
961 );
962 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
963 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
964 (
965 l_api_name,
966 G_PKG_NAME,
967 'OKC_API.G_RET_STS_UNEXP_ERROR',
968 x_msg_count,
969 x_msg_data,
970 '_PVT'
971 );
972 WHEN OTHERS THEN
973 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
974 (
975 l_api_name,
976 G_PKG_NAME,
977 'OTHERS',
978 x_msg_count,
979 x_msg_data,
980 '_PVT'
981 );
982 END insert_row;
983
984 ---------------------------------------------------------------------------
985 -- PROCEDURE lock_row
986 ---------------------------------------------------------------------------
987 --------------------------------
988 -- lock_row for:OKC_ANCESTRYS --
989 --------------------------------
990 PROCEDURE lock_row(
991 p_init_msg_list IN VARCHAR2 ,
992 x_return_status OUT NOCOPY VARCHAR2,
993 x_msg_count OUT NOCOPY NUMBER,
994 x_msg_data OUT NOCOPY VARCHAR2,
995 p_acy_rec IN acy_rec_type) IS
996
997 E_Resource_Busy EXCEPTION;
998 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
999 CURSOR lock_csr (p_acy_rec IN acy_rec_type) IS
1000 SELECT OBJECT_VERSION_NUMBER
1001 FROM OKC_ANCESTRYS
1002 WHERE CLE_ID = p_acy_rec.cle_id
1003 AND CLE_ID_ASCENDANT = p_acy_rec.cle_id_ascendant
1004 AND OBJECT_VERSION_NUMBER = p_acy_rec.object_version_number
1005 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1006
1007 CURSOR lchk_csr (p_acy_rec IN acy_rec_type) IS
1008 SELECT OBJECT_VERSION_NUMBER
1009 FROM OKC_ANCESTRYS
1010 WHERE CLE_ID = p_acy_rec.cle_id
1011 AND CLE_ID_ASCENDANT = p_acy_rec.cle_id_ascendant;
1012 l_api_version CONSTANT NUMBER := 1;
1013 l_api_name CONSTANT VARCHAR2(30) := 'ANCESTRYS_lock_row';
1014 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1015 l_object_version_number OKC_ANCESTRYS.OBJECT_VERSION_NUMBER%TYPE;
1016 lc_object_version_number OKC_ANCESTRYS.OBJECT_VERSION_NUMBER%TYPE;
1017 l_row_notfound BOOLEAN := FALSE;
1018 lc_row_notfound BOOLEAN := FALSE;
1019 BEGIN
1020 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1021 p_init_msg_list,
1022 '_PVT',
1023 x_return_status);
1024 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1025 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1026 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1027 RAISE OKC_API.G_EXCEPTION_ERROR;
1028 END IF;
1029 BEGIN
1030 OPEN lock_csr(p_acy_rec);
1031 FETCH lock_csr INTO l_object_version_number;
1032 l_row_notfound := lock_csr%NOTFOUND;
1033 CLOSE lock_csr;
1034 EXCEPTION
1035 WHEN E_Resource_Busy THEN
1036 IF (lock_csr%ISOPEN) THEN
1037 CLOSE lock_csr;
1038 END IF;
1039 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1040 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1041 END;
1042
1043 IF ( l_row_notfound ) THEN
1044 OPEN lchk_csr(p_acy_rec);
1045 FETCH lchk_csr INTO lc_object_version_number;
1046 lc_row_notfound := lchk_csr%NOTFOUND;
1047 CLOSE lchk_csr;
1048 END IF;
1049 IF (lc_row_notfound) THEN
1050 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1054 RAISE OKC_API.G_EXCEPTION_ERROR;
1051 RAISE OKC_API.G_EXCEPTION_ERROR;
1052 ELSIF lc_object_version_number > p_acy_rec.object_version_number THEN
1053 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1055 ELSIF lc_object_version_number <> p_acy_rec.object_version_number THEN
1056 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1057 RAISE OKC_API.G_EXCEPTION_ERROR;
1058 ELSIF lc_object_version_number = -1 THEN
1059 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1060 RAISE OKC_API.G_EXCEPTION_ERROR;
1061 END IF;
1062 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1063 EXCEPTION
1064 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1065 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1066 (
1067 l_api_name,
1068 G_PKG_NAME,
1069 'OKC_API.G_RET_STS_ERROR',
1070 x_msg_count,
1071 x_msg_data,
1072 '_PVT'
1073 );
1074 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1075 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1076 (
1077 l_api_name,
1078 G_PKG_NAME,
1079 'OKC_API.G_RET_STS_UNEXP_ERROR',
1080 x_msg_count,
1081 x_msg_data,
1082 '_PVT'
1083 );
1084 WHEN OTHERS THEN
1085 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1086 (
1087 l_api_name,
1088 G_PKG_NAME,
1089 'OTHERS',
1090 x_msg_count,
1091 x_msg_data,
1092 '_PVT'
1093 );
1094 END lock_row;
1095 ----------------------------------
1096 -- lock_row for:OKC_ANCESTRYS_V --
1097 ----------------------------------
1098 PROCEDURE lock_row(
1099 p_api_version IN NUMBER,
1100 p_init_msg_list IN VARCHAR2 ,
1101 x_return_status OUT NOCOPY VARCHAR2,
1102 x_msg_count OUT NOCOPY NUMBER,
1103 x_msg_data OUT NOCOPY VARCHAR2,
1104 p_acyv_rec IN acyv_rec_type) IS
1105
1106 l_api_version CONSTANT NUMBER := 1;
1107 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1108 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1109 l_acy_rec acy_rec_type;
1110 BEGIN
1111 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1112 G_PKG_NAME,
1113 p_init_msg_list,
1114 l_api_version,
1115 p_api_version,
1116 '_PVT',
1117 x_return_status);
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(p_acyv_rec, l_acy_rec);
1127 --------------------------------------------
1128 -- Call the LOCK_ROW for each child record
1129 --------------------------------------------
1130 lock_row(
1131 p_init_msg_list,
1132 x_return_status,
1133 x_msg_count,
1134 x_msg_data,
1135 l_acy_rec
1136 );
1137 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1138 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1139 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1140 RAISE OKC_API.G_EXCEPTION_ERROR;
1141 END IF;
1142 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1143 EXCEPTION
1144 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1145 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1146 (
1147 l_api_name,
1148 G_PKG_NAME,
1149 'OKC_API.G_RET_STS_ERROR',
1150 x_msg_count,
1151 x_msg_data,
1152 '_PVT'
1153 );
1154 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1155 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1156 (
1157 l_api_name,
1158 G_PKG_NAME,
1159 'OKC_API.G_RET_STS_UNEXP_ERROR',
1160 x_msg_count,
1161 x_msg_data,
1162 '_PVT'
1163 );
1164 WHEN OTHERS THEN
1165 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1166 (
1167 l_api_name,
1168 G_PKG_NAME,
1169 'OTHERS',
1170 x_msg_count,
1171 x_msg_data,
1172 '_PVT'
1173 );
1174 END lock_row;
1175 --------------------------------------
1176 -- PL/SQL TBL lock_row for:ACYV_TBL --
1177 --------------------------------------
1178 PROCEDURE lock_row(
1179 p_api_version IN NUMBER,
1180 p_init_msg_list IN VARCHAR2 ,
1181 x_return_status OUT NOCOPY VARCHAR2,
1182 x_msg_count OUT NOCOPY NUMBER,
1183 x_msg_data OUT NOCOPY VARCHAR2,
1184 p_acyv_tbl IN acyv_tbl_type) IS
1185
1186 l_api_version CONSTANT NUMBER := 1;
1187 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1188 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1189 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1190 i NUMBER := 0;
1191 BEGIN
1192 OKC_API.init_msg_list(p_init_msg_list);
1196 LOOP
1193 -- Make sure PL/SQL table has records in it before passing
1194 IF (p_acyv_tbl.COUNT > 0) THEN
1195 i := p_acyv_tbl.FIRST;
1197 lock_row (
1198 p_api_version => p_api_version,
1199 p_init_msg_list => OKC_API.G_FALSE,
1200 x_return_status => x_return_status,
1201 x_msg_count => x_msg_count,
1202 x_msg_data => x_msg_data,
1203 p_acyv_rec => p_acyv_tbl(i));
1204
1205 -- store the highest degree of error
1206 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1207 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1208 l_overall_status := x_return_status;
1209 End If;
1210 End If;
1211
1212 EXIT WHEN (i = p_acyv_tbl.LAST);
1213 i := p_acyv_tbl.NEXT(i);
1214 END LOOP;
1215 -- return overall status
1216 x_return_status := l_overall_status;
1217 END IF;
1218 EXCEPTION
1219 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1220 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1221 (
1222 l_api_name,
1223 G_PKG_NAME,
1224 'OKC_API.G_RET_STS_ERROR',
1225 x_msg_count,
1226 x_msg_data,
1227 '_PVT'
1228 );
1229 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1230 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1231 (
1232 l_api_name,
1233 G_PKG_NAME,
1234 'OKC_API.G_RET_STS_UNEXP_ERROR',
1235 x_msg_count,
1236 x_msg_data,
1237 '_PVT'
1238 );
1239 WHEN OTHERS THEN
1240 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1241 (
1242 l_api_name,
1243 G_PKG_NAME,
1244 'OTHERS',
1245 x_msg_count,
1246 x_msg_data,
1247 '_PVT'
1248 );
1249 END lock_row;
1250
1251 ---------------------------------------------------------------------------
1252 -- PROCEDURE update_row
1253 ---------------------------------------------------------------------------
1254 ----------------------------------
1255 -- update_row for:OKC_ANCESTRYS --
1256 ----------------------------------
1257 PROCEDURE update_row(
1258 p_init_msg_list IN VARCHAR2 ,
1259 x_return_status OUT NOCOPY VARCHAR2,
1260 x_msg_count OUT NOCOPY NUMBER,
1261 x_msg_data OUT NOCOPY VARCHAR2,
1262 p_acy_rec IN acy_rec_type,
1263 x_acy_rec OUT NOCOPY acy_rec_type) IS
1264
1265 l_api_version CONSTANT NUMBER := 1;
1266 l_api_name CONSTANT VARCHAR2(30) := 'ANCESTRYS_update_row';
1267 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1268 l_acy_rec acy_rec_type := p_acy_rec;
1269 l_def_acy_rec acy_rec_type;
1270 l_row_notfound BOOLEAN := TRUE;
1271 ----------------------------------
1272 -- FUNCTION populate_new_record --
1273 ----------------------------------
1274 FUNCTION populate_new_record (
1275 p_acy_rec IN acy_rec_type,
1276 x_acy_rec OUT NOCOPY acy_rec_type
1277 ) RETURN VARCHAR2 IS
1278 l_acy_rec acy_rec_type;
1279 l_row_notfound BOOLEAN := TRUE;
1280 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1281 BEGIN
1282 x_acy_rec := p_acy_rec;
1283 -- Get current database values
1284 l_acy_rec := get_rec(p_acy_rec, l_row_notfound);
1285 IF (l_row_notfound) THEN
1286 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1287 END IF;
1288 IF (x_acy_rec.cle_id = OKC_API.G_MISS_NUM)
1289 THEN
1290 x_acy_rec.cle_id := l_acy_rec.cle_id;
1291 END IF;
1292 IF (x_acy_rec.cle_id_ascendant = OKC_API.G_MISS_NUM)
1293 THEN
1294 x_acy_rec.cle_id_ascendant := l_acy_rec.cle_id_ascendant;
1295 END IF;
1296 IF (x_acy_rec.object_version_number = OKC_API.G_MISS_NUM)
1297 THEN
1298 x_acy_rec.object_version_number := l_acy_rec.object_version_number;
1299 END IF;
1300 IF (x_acy_rec.level_sequence = OKC_API.G_MISS_NUM)
1301 THEN
1302 x_acy_rec.level_sequence := l_acy_rec.level_sequence;
1303 END IF;
1304 IF (x_acy_rec.created_by = OKC_API.G_MISS_NUM)
1305 THEN
1306 x_acy_rec.created_by := l_acy_rec.created_by;
1307 END IF;
1308 IF (x_acy_rec.creation_date = OKC_API.G_MISS_DATE)
1309 THEN
1310 x_acy_rec.creation_date := l_acy_rec.creation_date;
1311 END IF;
1312 IF (x_acy_rec.last_updated_by = OKC_API.G_MISS_NUM)
1313 THEN
1314 x_acy_rec.last_updated_by := l_acy_rec.last_updated_by;
1315 END IF;
1316 IF (x_acy_rec.last_update_date = OKC_API.G_MISS_DATE)
1317 THEN
1318 x_acy_rec.last_update_date := l_acy_rec.last_update_date;
1319 END IF;
1320 IF (x_acy_rec.last_update_login = OKC_API.G_MISS_NUM)
1321 THEN
1322 x_acy_rec.last_update_login := l_acy_rec.last_update_login;
1323 END IF;
1324 RETURN(l_return_status);
1325 END populate_new_record;
1326 --------------------------------------
1327 -- Set_Attributes for:OKC_ANCESTRYS --
1328 --------------------------------------
1329 FUNCTION Set_Attributes (
1330 p_acy_rec IN acy_rec_type,
1331 x_acy_rec OUT NOCOPY acy_rec_type
1332 ) RETURN VARCHAR2 IS
1336 RETURN(l_return_status);
1333 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1334 BEGIN
1335 x_acy_rec := p_acy_rec;
1337 END Set_Attributes;
1338 BEGIN
1339 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1340 p_init_msg_list,
1341 '_PVT',
1342 x_return_status);
1343 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1344 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1345 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1346 RAISE OKC_API.G_EXCEPTION_ERROR;
1347 END IF;
1348 --- Setting item attributes
1349 l_return_status := Set_Attributes(
1350 p_acy_rec, -- IN
1351 l_acy_rec); -- OUT
1352 --- If any errors happen abort API
1353 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1354 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1355 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1356 RAISE OKC_API.G_EXCEPTION_ERROR;
1357 END IF;
1358 l_return_status := populate_new_record(l_acy_rec, l_def_acy_rec);
1359 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1360 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1361 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1362 RAISE OKC_API.G_EXCEPTION_ERROR;
1363 END IF;
1364 UPDATE OKC_ANCESTRYS
1365 SET OBJECT_VERSION_NUMBER = l_def_acy_rec.object_version_number,
1366 LEVEL_SEQUENCE = l_def_acy_rec.level_sequence,
1367 CREATED_BY = l_def_acy_rec.created_by,
1368 CREATION_DATE = l_def_acy_rec.creation_date,
1369 LAST_UPDATED_BY = l_def_acy_rec.last_updated_by,
1370 LAST_UPDATE_DATE = l_def_acy_rec.last_update_date,
1371 LAST_UPDATE_LOGIN = l_def_acy_rec.last_update_login
1372 WHERE CLE_ID = l_def_acy_rec.cle_id
1373 AND CLE_ID_ASCENDANT = l_def_acy_rec.cle_id_ascendant;
1374
1375 x_acy_rec := l_def_acy_rec;
1376 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1377 EXCEPTION
1378 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1379 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1380 (
1381 l_api_name,
1382 G_PKG_NAME,
1383 'OKC_API.G_RET_STS_ERROR',
1384 x_msg_count,
1385 x_msg_data,
1386 '_PVT'
1387 );
1388 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1389 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1390 (
1391 l_api_name,
1392 G_PKG_NAME,
1393 'OKC_API.G_RET_STS_UNEXP_ERROR',
1394 x_msg_count,
1395 x_msg_data,
1396 '_PVT'
1397 );
1398 WHEN OTHERS THEN
1399 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1400 (
1401 l_api_name,
1402 G_PKG_NAME,
1403 'OTHERS',
1404 x_msg_count,
1405 x_msg_data,
1406 '_PVT'
1407 );
1408 END update_row;
1409 ------------------------------------
1410 -- update_row for:OKC_ANCESTRYS_V --
1411 ------------------------------------
1412 PROCEDURE update_row(
1413 p_api_version IN NUMBER,
1414 p_init_msg_list IN VARCHAR2 ,
1415 x_return_status OUT NOCOPY VARCHAR2,
1416 x_msg_count OUT NOCOPY NUMBER,
1417 x_msg_data OUT NOCOPY VARCHAR2,
1418 p_acyv_rec IN acyv_rec_type,
1419 x_acyv_rec OUT NOCOPY acyv_rec_type) IS
1420
1421 l_api_version CONSTANT NUMBER := 1;
1422 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
1423 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1424 l_acyv_rec acyv_rec_type := p_acyv_rec;
1425 l_def_acyv_rec acyv_rec_type;
1426 l_acy_rec acy_rec_type;
1427 lx_acy_rec acy_rec_type;
1428 -------------------------------
1429 -- FUNCTION fill_who_columns --
1430 -------------------------------
1431 FUNCTION fill_who_columns (
1432 p_acyv_rec IN acyv_rec_type
1433 ) RETURN acyv_rec_type IS
1434 l_acyv_rec acyv_rec_type := p_acyv_rec;
1435 BEGIN
1436 l_acyv_rec.LAST_UPDATE_DATE := SYSDATE;
1437 l_acyv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1438 l_acyv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1439 RETURN(l_acyv_rec);
1440 END fill_who_columns;
1441 ----------------------------------
1442 -- FUNCTION populate_new_record --
1443 ----------------------------------
1444 FUNCTION populate_new_record (
1445 p_acyv_rec IN acyv_rec_type,
1446 x_acyv_rec OUT NOCOPY acyv_rec_type
1447 ) RETURN VARCHAR2 IS
1448 l_acyv_rec acyv_rec_type;
1449 l_row_notfound BOOLEAN := TRUE;
1450 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1451 BEGIN
1452 x_acyv_rec := p_acyv_rec;
1453 -- Get current database values
1454 l_acyv_rec := get_rec(p_acyv_rec, l_row_notfound);
1455 IF (l_row_notfound) THEN
1456 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
1457 END IF;
1458 IF (x_acyv_rec.cle_id = OKC_API.G_MISS_NUM)
1459 THEN
1460 x_acyv_rec.cle_id := l_acyv_rec.cle_id;
1461 END IF;
1462 IF (x_acyv_rec.cle_id_ascendant = OKC_API.G_MISS_NUM)
1463 THEN
1464 x_acyv_rec.cle_id_ascendant := l_acyv_rec.cle_id_ascendant;
1465 END IF;
1466 IF (x_acyv_rec.object_version_number = OKC_API.G_MISS_NUM)
1467 THEN
1468 x_acyv_rec.object_version_number := l_acyv_rec.object_version_number;
1469 END IF;
1473 END IF;
1470 IF (x_acyv_rec.level_sequence = OKC_API.G_MISS_NUM)
1471 THEN
1472 x_acyv_rec.level_sequence := l_acyv_rec.level_sequence;
1474 IF (x_acyv_rec.created_by = OKC_API.G_MISS_NUM)
1475 THEN
1476 x_acyv_rec.created_by := l_acyv_rec.created_by;
1477 END IF;
1478 IF (x_acyv_rec.creation_date = OKC_API.G_MISS_DATE)
1479 THEN
1480 x_acyv_rec.creation_date := l_acyv_rec.creation_date;
1481 END IF;
1482 IF (x_acyv_rec.last_updated_by = OKC_API.G_MISS_NUM)
1483 THEN
1484 x_acyv_rec.last_updated_by := l_acyv_rec.last_updated_by;
1485 END IF;
1486 IF (x_acyv_rec.last_update_date = OKC_API.G_MISS_DATE)
1487 THEN
1488 x_acyv_rec.last_update_date := l_acyv_rec.last_update_date;
1489 END IF;
1490 IF (x_acyv_rec.last_update_login = OKC_API.G_MISS_NUM)
1491 THEN
1492 x_acyv_rec.last_update_login := l_acyv_rec.last_update_login;
1493 END IF;
1494 RETURN(l_return_status);
1495 END populate_new_record;
1496 ----------------------------------------
1497 -- Set_Attributes for:OKC_ANCESTRYS_V --
1498 ----------------------------------------
1499 FUNCTION Set_Attributes (
1500 p_acyv_rec IN acyv_rec_type,
1501 x_acyv_rec OUT NOCOPY acyv_rec_type
1502 ) RETURN VARCHAR2 IS
1503 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1504 BEGIN
1505 x_acyv_rec := p_acyv_rec;
1506 x_acyv_rec.OBJECT_VERSION_NUMBER := NVL(x_acyv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
1507 RETURN(l_return_status);
1508 END Set_Attributes;
1509 BEGIN
1510 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1511 G_PKG_NAME,
1512 p_init_msg_list,
1513 l_api_version,
1514 p_api_version,
1515 '_PVT',
1516 x_return_status);
1517 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1518 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1519 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1520 RAISE OKC_API.G_EXCEPTION_ERROR;
1521 END IF;
1522 --- Setting item attributes
1523 l_return_status := Set_Attributes(
1524 p_acyv_rec, -- IN
1525 l_acyv_rec); -- OUT
1526 --- If any errors happen abort API
1527 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1528 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1529 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1530 RAISE OKC_API.G_EXCEPTION_ERROR;
1531 END IF;
1532 l_return_status := populate_new_record(l_acyv_rec, l_def_acyv_rec);
1533 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1534 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1535 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1536 RAISE OKC_API.G_EXCEPTION_ERROR;
1537 END IF;
1538 l_def_acyv_rec := fill_who_columns(l_def_acyv_rec);
1539 --- Validate all non-missing attributes (Item Level Validation)
1540 l_return_status := Validate_Attributes(l_def_acyv_rec);
1541 --- If any errors happen abort API
1542 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1543 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1544 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1545 RAISE OKC_API.G_EXCEPTION_ERROR;
1546 END IF;
1547 l_return_status := Validate_Record(l_def_acyv_rec);
1548 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1549 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1550 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1551 RAISE OKC_API.G_EXCEPTION_ERROR;
1552 END IF;
1553
1554 --------------------------------------
1555 -- Move VIEW record to "Child" records
1556 --------------------------------------
1557 migrate(l_def_acyv_rec, l_acy_rec);
1558 --------------------------------------------
1559 -- Call the UPDATE_ROW for each child record
1560 --------------------------------------------
1561 update_row(
1562 p_init_msg_list,
1563 x_return_status,
1564 x_msg_count,
1565 x_msg_data,
1566 l_acy_rec,
1567 lx_acy_rec
1568 );
1569 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1570 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1571 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1572 RAISE OKC_API.G_EXCEPTION_ERROR;
1573 END IF;
1574 migrate(lx_acy_rec, l_def_acyv_rec);
1575 x_acyv_rec := l_def_acyv_rec;
1576 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1577 EXCEPTION
1578 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1579 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1580 (
1581 l_api_name,
1582 G_PKG_NAME,
1583 'OKC_API.G_RET_STS_ERROR',
1584 x_msg_count,
1585 x_msg_data,
1586 '_PVT'
1587 );
1588 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1589 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1590 (
1591 l_api_name,
1592 G_PKG_NAME,
1593 'OKC_API.G_RET_STS_UNEXP_ERROR',
1594 x_msg_count,
1595 x_msg_data,
1596 '_PVT'
1597 );
1598 WHEN OTHERS THEN
1599 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1600 (
1601 l_api_name,
1602 G_PKG_NAME,
1603 'OTHERS',
1604 x_msg_count,
1605 x_msg_data,
1606 '_PVT'
1607 );
1608 END update_row;
1609 ----------------------------------------
1613 p_api_version IN NUMBER,
1610 -- PL/SQL TBL update_row for:ACYV_TBL --
1611 ----------------------------------------
1612 PROCEDURE update_row(
1614 p_init_msg_list IN VARCHAR2 ,
1615 x_return_status OUT NOCOPY VARCHAR2,
1616 x_msg_count OUT NOCOPY NUMBER,
1617 x_msg_data OUT NOCOPY VARCHAR2,
1618 p_acyv_tbl IN acyv_tbl_type,
1619 x_acyv_tbl OUT NOCOPY acyv_tbl_type) IS
1620
1621 l_api_version CONSTANT NUMBER := 1;
1622 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
1623 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1624 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1625 i NUMBER := 0;
1626 BEGIN
1627 OKC_API.init_msg_list(p_init_msg_list);
1628 -- Make sure PL/SQL table has records in it before passing
1629 IF (p_acyv_tbl.COUNT > 0) THEN
1630 i := p_acyv_tbl.FIRST;
1631 LOOP
1632 update_row (
1633 p_api_version => p_api_version,
1634 p_init_msg_list => OKC_API.G_FALSE,
1635 x_return_status => x_return_status,
1636 x_msg_count => x_msg_count,
1637 x_msg_data => x_msg_data,
1638 p_acyv_rec => p_acyv_tbl(i),
1639 x_acyv_rec => x_acyv_tbl(i));
1640
1641 -- store the highest degree of error
1642 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1643 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1644 l_overall_status := x_return_status;
1645 End If;
1646 End If;
1647
1648 EXIT WHEN (i = p_acyv_tbl.LAST);
1649 i := p_acyv_tbl.NEXT(i);
1650 END LOOP;
1651 -- return overall status
1652 x_return_status := l_overall_status;
1653 END IF;
1654 EXCEPTION
1655 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1656 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1657 (
1658 l_api_name,
1659 G_PKG_NAME,
1660 'OKC_API.G_RET_STS_ERROR',
1661 x_msg_count,
1662 x_msg_data,
1663 '_PVT'
1664 );
1665 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1666 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1667 (
1668 l_api_name,
1669 G_PKG_NAME,
1670 'OKC_API.G_RET_STS_UNEXP_ERROR',
1671 x_msg_count,
1672 x_msg_data,
1673 '_PVT'
1674 );
1675 WHEN OTHERS THEN
1676 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1677 (
1678 l_api_name,
1679 G_PKG_NAME,
1680 'OTHERS',
1681 x_msg_count,
1682 x_msg_data,
1683 '_PVT'
1684 );
1685 END update_row;
1686
1687 ---------------------------------------------------------------------------
1688 -- PROCEDURE delete_row
1689 ---------------------------------------------------------------------------
1690 ----------------------------------
1691 -- delete_row for:OKC_ANCESTRYS --
1692 ----------------------------------
1693 PROCEDURE delete_row(
1694 p_init_msg_list IN VARCHAR2 ,
1695 x_return_status OUT NOCOPY VARCHAR2,
1696 x_msg_count OUT NOCOPY NUMBER,
1697 x_msg_data OUT NOCOPY VARCHAR2,
1698 p_acy_rec IN acy_rec_type) IS
1699
1700 l_api_version CONSTANT NUMBER := 1;
1701 l_api_name CONSTANT VARCHAR2(30) := 'ANCESTRYS_delete_row';
1702 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1703 l_acy_rec acy_rec_type:= p_acy_rec;
1704 l_row_notfound BOOLEAN := TRUE;
1705 BEGIN
1706 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1707 p_init_msg_list,
1708 '_PVT',
1709 x_return_status);
1710 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1711 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1712 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1713 RAISE OKC_API.G_EXCEPTION_ERROR;
1714 END IF;
1715 DELETE FROM OKC_ANCESTRYS
1716 WHERE CLE_ID = l_acy_rec.cle_id AND
1717 CLE_ID_ASCENDANT = l_acy_rec.cle_id_ascendant;
1718
1719 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1720 EXCEPTION
1721 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1722 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1723 (
1724 l_api_name,
1725 G_PKG_NAME,
1726 'OKC_API.G_RET_STS_ERROR',
1727 x_msg_count,
1728 x_msg_data,
1729 '_PVT'
1730 );
1731 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1732 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1733 (
1734 l_api_name,
1735 G_PKG_NAME,
1736 'OKC_API.G_RET_STS_UNEXP_ERROR',
1737 x_msg_count,
1738 x_msg_data,
1739 '_PVT'
1740 );
1741 WHEN OTHERS THEN
1742 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1743 (
1744 l_api_name,
1745 G_PKG_NAME,
1746 'OTHERS',
1747 x_msg_count,
1748 x_msg_data,
1749 '_PVT'
1750 );
1751 END delete_row;
1752 ------------------------------------
1753 -- delete_row for:OKC_ANCESTRYS_V --
1754 ------------------------------------
1755 PROCEDURE delete_row(
1759 x_msg_count OUT NOCOPY NUMBER,
1756 p_api_version IN NUMBER,
1757 p_init_msg_list IN VARCHAR2 ,
1758 x_return_status OUT NOCOPY VARCHAR2,
1760 x_msg_data OUT NOCOPY VARCHAR2,
1761 p_acyv_rec IN acyv_rec_type) IS
1762
1763 l_api_version CONSTANT NUMBER := 1;
1764 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
1765 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1766 l_acyv_rec acyv_rec_type := p_acyv_rec;
1767 l_acy_rec acy_rec_type;
1768 BEGIN
1769 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1770 G_PKG_NAME,
1771 p_init_msg_list,
1772 l_api_version,
1773 p_api_version,
1774 '_PVT',
1775 x_return_status);
1776 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1777 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1778 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1779 RAISE OKC_API.G_EXCEPTION_ERROR;
1780 END IF;
1781 --------------------------------------
1782 -- Move VIEW record to "Child" records
1783 --------------------------------------
1784 migrate(l_acyv_rec, l_acy_rec);
1785 --------------------------------------------
1786 -- Call the DELETE_ROW for each child record
1787 --------------------------------------------
1788 delete_row(
1789 p_init_msg_list,
1790 x_return_status,
1791 x_msg_count,
1792 x_msg_data,
1793 l_acy_rec
1794 );
1795 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1796 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1797 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1798 RAISE OKC_API.G_EXCEPTION_ERROR;
1799 END IF;
1800 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1801 EXCEPTION
1802 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1803 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1804 (
1805 l_api_name,
1806 G_PKG_NAME,
1807 'OKC_API.G_RET_STS_ERROR',
1808 x_msg_count,
1809 x_msg_data,
1810 '_PVT'
1811 );
1812 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1813 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1814 (
1815 l_api_name,
1816 G_PKG_NAME,
1817 'OKC_API.G_RET_STS_UNEXP_ERROR',
1818 x_msg_count,
1819 x_msg_data,
1820 '_PVT'
1821 );
1822 WHEN OTHERS THEN
1823 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1824 (
1825 l_api_name,
1826 G_PKG_NAME,
1827 'OTHERS',
1828 x_msg_count,
1829 x_msg_data,
1830 '_PVT'
1831 );
1832 END delete_row;
1833 ----------------------------------------
1834 -- PL/SQL TBL delete_row for:ACYV_TBL --
1835 ----------------------------------------
1836 PROCEDURE delete_row(
1837 p_api_version IN NUMBER,
1838 p_init_msg_list IN VARCHAR2 ,
1839 x_return_status OUT NOCOPY VARCHAR2,
1840 x_msg_count OUT NOCOPY NUMBER,
1841 x_msg_data OUT NOCOPY VARCHAR2,
1842 p_acyv_tbl IN acyv_tbl_type) IS
1843
1844 l_api_version CONSTANT NUMBER := 1;
1845 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
1846 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1847 l_overall_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1848 i NUMBER := 0;
1849 BEGIN
1850 OKC_API.init_msg_list(p_init_msg_list);
1851 -- Make sure PL/SQL table has records in it before passing
1852 IF (p_acyv_tbl.COUNT > 0) THEN
1853 i := p_acyv_tbl.FIRST;
1854 LOOP
1855 delete_row (
1856 p_api_version => p_api_version,
1857 p_init_msg_list => OKC_API.G_FALSE,
1858 x_return_status => x_return_status,
1859 x_msg_count => x_msg_count,
1860 x_msg_data => x_msg_data,
1861 p_acyv_rec => p_acyv_tbl(i));
1862
1863 -- store the highest degree of error
1864 If x_return_status <> OKC_API.G_RET_STS_SUCCESS Then
1865 If l_overall_status <> OKC_API.G_RET_STS_UNEXP_ERROR Then
1866 l_overall_status := x_return_status;
1867 End If;
1868 End If;
1869
1870 EXIT WHEN (i = p_acyv_tbl.LAST);
1871 i := p_acyv_tbl.NEXT(i);
1872 END LOOP;
1873 -- return overall status
1874 x_return_status := l_overall_status;
1875 END IF;
1876 EXCEPTION
1877 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1878 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1879 (
1880 l_api_name,
1881 G_PKG_NAME,
1882 'OKC_API.G_RET_STS_ERROR',
1883 x_msg_count,
1884 x_msg_data,
1885 '_PVT'
1886 );
1887 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1888 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1889 (
1890 l_api_name,
1891 G_PKG_NAME,
1892 'OKC_API.G_RET_STS_UNEXP_ERROR',
1893 x_msg_count,
1894 x_msg_data,
1895 '_PVT'
1896 );
1897 WHEN OTHERS THEN
1898 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1899 (
1900 l_api_name,
1904 x_msg_data,
1901 G_PKG_NAME,
1902 'OTHERS',
1903 x_msg_count,
1905 '_PVT'
1906 );
1907 END delete_row;
1908 END OKC_ACY_PVT;