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