[Home] [Help]
PACKAGE BODY: APPS.OKL_LAV_PVT
Source
1 PACKAGE BODY OKL_LAV_PVT AS
2 /* $Header: OKLSLAVB.pls 120.2 2005/10/19 11:54:00 varangan noship $ */
3
4 -------------------------
5 -- PROCEDURE add_language
6 -------------------------
7 PROCEDURE add_language IS
8
9 BEGIN
10
11 DELETE FROM OKL_LEASEAPP_TEMPL_VERSIONS_TL T
12 WHERE NOT EXISTS (SELECT NULL FROM OKL_LEASEAPP_TEMPL_VERSIONS_B B WHERE B.ID =T.ID);
13
14 UPDATE OKL_LEASEAPP_TEMPL_VERSIONS_TL T
15 SET (SHORT_DESCRIPTION) =
16 (SELECT
17 B.SHORT_DESCRIPTION
18 FROM
19 OKL_LEASEAPP_TEMPL_VERSIONS_TL B
20 WHERE
21 B.ID = T.ID
22 AND B.LANGUAGE = T.SOURCE_LANG)
23 WHERE (T.ID, T.LANGUAGE) IN (SELECT
24 SUBT.ID,
25 SUBT.LANGUAGE
26 FROM
27 OKL_LEASEAPP_TEMPL_VERSIONS_TL SUBB,
28 OKL_LEASEAPP_TEMPL_VERSIONS_TL SUBT
29 WHERE
30 SUBB.ID = SUBT.ID
31 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
32 AND (SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION)
33 OR (SUBB.SHORT_DESCRIPTION IS NULL AND SUBT.SHORT_DESCRIPTION IS NOT NULL)
34 );
35
36 INSERT INTO OKL_LEASEAPP_TEMPL_VERSIONS_TL (
37 ID,
38 LANGUAGE,
39 SOURCE_LANG,
40 SFWT_FLAG,
41 CREATED_BY,
42 CREATION_DATE,
43 LAST_UPDATED_BY,
44 LAST_UPDATE_DATE,
45 LAST_UPDATE_LOGIN,
46 SHORT_DESCRIPTION)
47 SELECT
48 B.ID,
49 L.LANGUAGE_CODE,
50 B.SOURCE_LANG,
51 B.SFWT_FLAG,
52 B.CREATED_BY,
53 B.CREATION_DATE,
54 B.LAST_UPDATED_BY,
55 B.LAST_UPDATE_DATE,
56 B.LAST_UPDATE_LOGIN,
57 B.SHORT_DESCRIPTION
58 FROM OKL_LEASEAPP_TEMPL_VERSIONS_TL B, FND_LANGUAGES L
59 WHERE L.INSTALLED_FLAG IN ('I', 'B')
60 AND B.LANGUAGE = USERENV('LANG')
61 AND NOT EXISTS (
62 SELECT NULL
63 FROM OKL_LEASEAPP_TEMPL_VERSIONS_TL T
64 WHERE T.ID = B.ID
65 AND T.LANGUAGE = L.LANGUAGE_CODE
66 );
67
68 END add_language;
69
70 -----------------------------
71 -- FUNCTION null_out_defaults
72 -----------------------------
73 FUNCTION null_out_defaults (p_lavv_rec IN lavv_rec_type) RETURN lavv_rec_type IS
74
75 l_lavv_rec lavv_rec_type;
76
77 BEGIN
78
79 l_lavv_rec := p_lavv_rec;
80
81 -- Not applicable to ID and OBJECT_VERSION_NUMBER
82
83 IF l_lavv_rec.attribute_category = FND_API.G_MISS_CHAR THEN
84 l_lavv_rec.attribute_category := NULL;
85 END IF;
86 IF l_lavv_rec.attribute1 = FND_API.G_MISS_CHAR THEN
87 l_lavv_rec.attribute1 := NULL;
88 END IF;
89 IF l_lavv_rec.attribute2 = FND_API.G_MISS_CHAR THEN
90 l_lavv_rec.attribute2 := NULL;
91 END IF;
92 IF l_lavv_rec.attribute3 = FND_API.G_MISS_CHAR THEN
93 l_lavv_rec.attribute3 := NULL;
94 END IF;
95 IF l_lavv_rec.attribute4 = FND_API.G_MISS_CHAR THEN
96 l_lavv_rec.attribute4 := NULL;
97 END IF;
98 IF l_lavv_rec.attribute5 = FND_API.G_MISS_CHAR THEN
99 l_lavv_rec.attribute5 := NULL;
100 END IF;
101 IF l_lavv_rec.attribute6 = FND_API.G_MISS_CHAR THEN
102 l_lavv_rec.attribute6 := NULL;
103 END IF;
104 IF l_lavv_rec.attribute7 = FND_API.G_MISS_CHAR THEN
105 l_lavv_rec.attribute7 := NULL;
106 END IF;
107 IF l_lavv_rec.attribute8 = FND_API.G_MISS_CHAR THEN
108 l_lavv_rec.attribute8 := NULL;
109 END IF;
110 IF l_lavv_rec.attribute9 = FND_API.G_MISS_CHAR THEN
111 l_lavv_rec.attribute9 := NULL;
112 END IF;
113 IF l_lavv_rec.attribute10 = FND_API.G_MISS_CHAR THEN
114 l_lavv_rec.attribute10 := NULL;
115 END IF;
116 IF l_lavv_rec.attribute11 = FND_API.G_MISS_CHAR THEN
117 l_lavv_rec.attribute11 := NULL;
118 END IF;
119 IF l_lavv_rec.attribute12 = FND_API.G_MISS_CHAR THEN
120 l_lavv_rec.attribute12 := NULL;
121 END IF;
122 IF l_lavv_rec.attribute13 = FND_API.G_MISS_CHAR THEN
123 l_lavv_rec.attribute13 := NULL;
124 END IF;
125 IF l_lavv_rec.attribute14 = FND_API.G_MISS_CHAR THEN
126 l_lavv_rec.attribute14 := NULL;
127 END IF;
128 IF l_lavv_rec.attribute15 = FND_API.G_MISS_CHAR THEN
129 l_lavv_rec.attribute15 := NULL;
130 END IF;
131 IF l_lavv_rec.leaseapp_template_id = FND_API.G_MISS_NUM THEN
132 l_lavv_rec.leaseapp_template_id := NULL;
133 END IF;
134 IF l_lavv_rec.version_status = FND_API.G_MISS_CHAR THEN
135 l_lavv_rec.version_status := NULL;
136 END IF;
137 IF l_lavv_rec.version_number = FND_API.G_MISS_CHAR THEN
138 l_lavv_rec.version_number := NULL;
139 END IF;
140 IF l_lavv_rec.valid_from = FND_API.G_MISS_DATE THEN
141 l_lavv_rec.valid_from := NULL;
142 END IF;
143 IF l_lavv_rec.valid_to = FND_API.G_MISS_DATE THEN
144 l_lavv_rec.valid_to := NULL;
145 END IF;
146 IF l_lavv_rec.checklist_id = FND_API.G_MISS_NUM THEN
147 l_lavv_rec.checklist_id := NULL;
148 END IF;
149 IF l_lavv_rec.contract_template_id = FND_API.G_MISS_NUM THEN
150 l_lavv_rec.contract_template_id := NULL;
151 END IF;
152 IF l_lavv_rec.short_description = FND_API.G_MISS_CHAR THEN
153 l_lavv_rec.short_description := NULL;
154 END IF;
155
156 RETURN l_lavv_rec;
157
158 END null_out_defaults;
159
160 -------------------
161 -- FUNCTION get_rec
162 -------------------
163 FUNCTION get_rec (p_id IN NUMBER
164 ,x_return_status OUT NOCOPY VARCHAR2) RETURN lavv_rec_type IS
165
166 l_lavv_rec lavv_rec_type;
167 l_prog_name VARCHAR2(61);
168
169 BEGIN
170
171 l_prog_name := G_PKG_NAME||'.get_rec';
172
173 SELECT
174 id
175 ,object_version_number
176 ,attribute_category
177 ,attribute1
178 ,attribute2
179 ,attribute3
180 ,attribute4
181 ,attribute5
182 ,attribute6
183 ,attribute7
184 ,attribute8
185 ,attribute9
186 ,attribute10
187 ,attribute11
188 ,attribute12
189 ,attribute13
190 ,attribute14
191 ,attribute15
192 ,leaseapp_template_id
193 ,version_status
194 ,version_number
195 ,valid_from
196 ,valid_to
197 ,checklist_id
198 ,contract_template_id
199 ,short_description
200 INTO
201 l_lavv_rec.id
202 ,l_lavv_rec.object_version_number
203 ,l_lavv_rec.attribute_category
204 ,l_lavv_rec.attribute1
205 ,l_lavv_rec.attribute2
206 ,l_lavv_rec.attribute3
207 ,l_lavv_rec.attribute4
208 ,l_lavv_rec.attribute5
209 ,l_lavv_rec.attribute6
210 ,l_lavv_rec.attribute7
211 ,l_lavv_rec.attribute8
212 ,l_lavv_rec.attribute9
213 ,l_lavv_rec.attribute10
214 ,l_lavv_rec.attribute11
215 ,l_lavv_rec.attribute12
216 ,l_lavv_rec.attribute13
217 ,l_lavv_rec.attribute14
218 ,l_lavv_rec.attribute15
219 ,l_lavv_rec.leaseapp_template_id
220 ,l_lavv_rec.version_status
221 ,l_lavv_rec.version_number
222 ,l_lavv_rec.valid_from
223 ,l_lavv_rec.valid_to
224 ,l_lavv_rec.checklist_id
225 ,l_lavv_rec.contract_template_id
226 ,l_lavv_rec.short_description
227 FROM okl_leaseapp_templ_versions_v
228 WHERE id = p_id;
229
230 x_return_status := G_RET_STS_SUCCESS;
231 RETURN l_lavv_rec;
232
233 EXCEPTION
234
235 WHEN OTHERS THEN
236
237 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
238 p_msg_name => G_DB_ERROR,
239 p_token1 => G_PROG_NAME_TOKEN,
240 p_token1_value => l_prog_name,
241 p_token2 => G_SQLCODE_TOKEN,
242 p_token2_value => sqlcode,
243 p_token3 => G_SQLERRM_TOKEN,
244 p_token3_value => sqlerrm);
245
246 x_return_status := G_RET_STS_UNEXP_ERROR;
247
248 END get_rec;
249
250 ------------------------
251 -- PROCEDURE validate_id
252 ------------------------
253 PROCEDURE validate_id (x_return_status OUT NOCOPY VARCHAR2, p_id IN NUMBER) IS
254 BEGIN
255 IF (p_id = OKL_API.G_MISS_NUM OR
256 p_id IS NULL)
257 THEN
258 OKL_API.set_message(p_app_name => G_APP_NAME,
259 p_msg_name => G_COL_ERROR,
260 p_token1 => G_COL_NAME_TOKEN,
261 p_token1_value => 'id',
262 p_token2 => G_PKG_NAME_TOKEN,
263 p_token2_value => G_PKG_NAME);
264 RAISE OKL_API.G_EXCEPTION_ERROR;
265 END IF;
266 x_return_status := G_RET_STS_SUCCESS;
267 END validate_id;
268
269 -------------------------------------------
270 -- PROCEDURE validate_object_version_number
271 -------------------------------------------
272 PROCEDURE validate_object_version_number (x_return_status OUT NOCOPY VARCHAR2, p_object_version_number IN NUMBER) IS
273 BEGIN
274 IF (p_object_version_number = OKL_API.G_MISS_NUM OR
275 p_object_version_number IS NULL)
276 THEN
277 OKL_API.set_message(p_app_name => G_APP_NAME,
278 p_msg_name => G_COL_ERROR,
279 p_token1 => G_COL_NAME_TOKEN,
280 p_token1_value => 'object_version_number',
281 p_token2 => G_PKG_NAME_TOKEN,
282 p_token2_value => G_PKG_NAME);
283 RAISE OKL_API.G_EXCEPTION_ERROR;
284 END IF;
285 x_return_status := G_RET_STS_SUCCESS;
286 END validate_object_version_number;
287
288 --------------------------------------
289 -- PROCEDURE validate_leaseapp_template_id
290 --------------------------------------
291 PROCEDURE validate_leaseapp_template_id (x_return_status OUT NOCOPY VARCHAR2, p_leaseapp_template_id IN NUMBER) IS
292 BEGIN
293 IF (p_leaseapp_template_id = OKL_API.G_MISS_NUM OR
294 p_leaseapp_template_id IS NULL)
295 THEN
296 OKL_API.set_message(p_app_name => G_APP_NAME,
297 p_msg_name => G_COL_ERROR,
298 p_token1 => G_COL_NAME_TOKEN,
299 p_token1_value => 'leaseapp_template_id',
300 p_token2 => G_PKG_NAME_TOKEN,
301 p_token2_value => G_PKG_NAME);
302 RAISE OKL_API.G_EXCEPTION_ERROR;
303 END IF;
304 x_return_status := G_RET_STS_SUCCESS;
305 END validate_leaseapp_template_id;
306
307 --------------------------------------
308 -- PROCEDURE validate_version_status
309 --------------------------------------
310 PROCEDURE validate_version_status (x_return_status OUT NOCOPY VARCHAR2, p_version_status IN VARCHAR2) IS
311 BEGIN
312 IF (p_version_status = OKL_API.G_MISS_CHAR OR
313 p_version_status IS NULL)
314 THEN
315 OKL_API.set_message(p_app_name => G_APP_NAME,
316 p_msg_name => G_COL_ERROR,
317 p_token1 => G_COL_NAME_TOKEN,
318 p_token1_value => 'version_status',
319 p_token2 => G_PKG_NAME_TOKEN,
320 p_token2_value => G_PKG_NAME);
321 RAISE OKL_API.G_EXCEPTION_ERROR;
322 END IF;
323 x_return_status := G_RET_STS_SUCCESS;
324 END validate_version_status;
325
326 --------------------------------------
327 -- PROCEDURE validate_version_number
328 --------------------------------------
329 PROCEDURE validate_version_number (x_return_status OUT NOCOPY VARCHAR2, p_version_number IN VARCHAR2) IS
330 BEGIN
331 IF (p_version_number = OKL_API.G_MISS_CHAR OR
332 p_version_number IS NULL)
333 THEN
334 OKL_API.set_message(p_app_name => G_APP_NAME,
335 p_msg_name => G_COL_ERROR,
336 p_token1 => G_COL_NAME_TOKEN,
337 p_token1_value => 'version_number',
338 p_token2 => G_PKG_NAME_TOKEN,
339 p_token2_value => G_PKG_NAME);
340 RAISE OKL_API.G_EXCEPTION_ERROR;
341 END IF;
342 x_return_status := G_RET_STS_SUCCESS;
343 END validate_version_number;
344
345 --------------------------------
346 -- PROCEDURE validate_valid_from
347 --------------------------------
348 PROCEDURE validate_valid_from (x_return_status OUT NOCOPY VARCHAR2, p_valid_from IN DATE) IS
349 BEGIN
350 IF (p_valid_from = OKL_API.G_MISS_DATE OR
351 p_valid_from IS NULL)
352 THEN
353 OKL_API.set_message(p_app_name => G_APP_NAME,
354 p_msg_name => G_COL_ERROR,
355 p_token1 => G_COL_NAME_TOKEN,
356 p_token1_value => 'valid_from',
357 p_token2 => G_PKG_NAME_TOKEN,
358 p_token2_value => G_PKG_NAME);
359 RAISE OKL_API.G_EXCEPTION_ERROR;
360 END IF;
361 x_return_status := G_RET_STS_SUCCESS;
362 END validate_valid_from;
363
364 -------------------------------
365 -- FUNCTION validate_attributes
366 -------------------------------
367 FUNCTION validate_attributes (p_lavv_rec IN lavv_rec_type) RETURN VARCHAR2 IS
368
369 l_return_status VARCHAR2(1);
370
371 BEGIN
372
373 validate_id (l_return_status, p_lavv_rec.id);
374 validate_object_version_number (l_return_status, p_lavv_rec.object_version_number);
375 validate_leaseapp_template_id (l_return_status, p_lavv_rec.leaseapp_template_id);
376 validate_version_status (l_return_status, p_lavv_rec.version_status);
377 validate_version_number (l_return_status, p_lavv_rec.version_number);
378 validate_valid_from (l_return_status, p_lavv_rec.valid_from);
379
380 RETURN l_return_status;
381
382 END validate_attributes;
383
384 ----------------------------
385 -- PROCEDURE validate_record
386 ----------------------------
387 FUNCTION validate_record (p_lavv_rec IN lavv_rec_type) RETURN VARCHAR2 IS
388 l_return_status VARCHAR2(1);
389 BEGIN
390 l_return_status := G_RET_STS_SUCCESS;
391 -- start date cannot be after end date
392 IF (p_lavv_rec.VALID_FROM IS NOT NULL AND p_lavv_rec.VALID_TO IS NOT NULL) THEN
393
394 --Begin -varangan- updated the condition to > & message to OKL_INVALID_VALID_TO for bug#4684557
395 IF (trunc(p_lavv_rec.VALID_FROM) > trunc(p_lavv_rec.VALID_TO)) THEN
396
397 -- notify caller of an error as UNEXPETED error
398 l_return_status := OKC_API.G_RET_STS_ERROR;
399
400 OKL_API.SET_MESSAGE(p_app_name => g_app_name,
401 p_msg_name => 'OKL_INVALID_VALID_TO');
402 --End -varangan- updated the condition to > & message to OKL_INVALID_VALID_TO for bug#4684557
403
404 -- notify caller of an error
405 l_return_status := OKL_API.G_RET_STS_ERROR;
406 END IF;
407 END IF;
408 RETURN l_return_status;
409 END validate_record;
410
411 -----------------------------
412 -- PROECDURE migrate (V -> B)
413 -----------------------------
414 PROCEDURE migrate (p_from IN lavv_rec_type, p_to IN OUT NOCOPY lav_rec_type) IS
415
416 BEGIN
417 p_to.id := p_from.id;
418 p_to.object_version_number := p_from.object_version_number;
419 p_to.attribute_category := p_from.attribute_category;
420 p_to.attribute1 := p_from.attribute1;
421 p_to.attribute2 := p_from.attribute2;
422 p_to.attribute3 := p_from.attribute3;
423 p_to.attribute4 := p_from.attribute4;
424 p_to.attribute5 := p_from.attribute5;
425 p_to.attribute6 := p_from.attribute6;
426 p_to.attribute7 := p_from.attribute7;
427 p_to.attribute8 := p_from.attribute8;
428 p_to.attribute9 := p_from.attribute9;
429 p_to.attribute10 := p_from.attribute10;
430 p_to.attribute11 := p_from.attribute11;
431 p_to.attribute12 := p_from.attribute12;
432 p_to.attribute13 := p_from.attribute13;
433 p_to.attribute14 := p_from.attribute14;
434 p_to.attribute15 := p_from.attribute15;
435 p_to.leaseapp_template_id := p_from.leaseapp_template_id;
436 p_to.version_status := p_from.version_status;
437 p_to.version_number := p_from.version_number;
438 p_to.valid_from := p_from.valid_from;
439 p_to.valid_to := p_from.valid_to;
440 p_to.checklist_id := p_from.checklist_id;
441 p_to.contract_template_id := p_from.contract_template_id;
442
443 END migrate;
444
445 -----------------------------
446 -- PROCEDURE migrate (V -> TL)
447 -----------------------------
448 PROCEDURE migrate (p_from IN lavv_rec_type, p_to IN OUT NOCOPY lavtl_rec_type) IS
449 BEGIN
450 p_to.id := p_from.id;
451 p_to.short_description := p_from.short_description;
452 END migrate;
453
454 ---------------------------
455 -- PROCEDURE insert_row (B)
456 ---------------------------
457 PROCEDURE insert_row (x_return_status OUT NOCOPY VARCHAR2, p_lav_rec IN lav_rec_type) IS
458
459 l_prog_name VARCHAR2(61);
460
461 BEGIN
462
463 l_prog_name := G_PKG_NAME||'.insert_row (B)';
464
465 INSERT INTO okl_leaseapp_templ_versions_b (
466 id
467 ,object_version_number
468 ,attribute_category
469 ,attribute1
470 ,attribute2
471 ,attribute3
472 ,attribute4
473 ,attribute5
474 ,attribute6
475 ,attribute7
476 ,attribute8
477 ,attribute9
478 ,attribute10
479 ,attribute11
480 ,attribute12
481 ,attribute13
482 ,attribute14
483 ,attribute15
484 ,created_by
485 ,creation_date
486 ,last_updated_by
487 ,last_update_date
488 ,last_update_login
489 ,leaseapp_template_id
490 ,version_status
491 ,version_number
492 ,valid_from
493 ,valid_to
494 ,checklist_id
495 ,contract_template_id
496 )
497 VALUES
498 (
499 p_lav_rec.id
500 ,p_lav_rec.object_version_number
501 ,p_lav_rec.attribute_category
502 ,p_lav_rec.attribute1
503 ,p_lav_rec.attribute2
504 ,p_lav_rec.attribute3
505 ,p_lav_rec.attribute4
506 ,p_lav_rec.attribute5
507 ,p_lav_rec.attribute6
508 ,p_lav_rec.attribute7
509 ,p_lav_rec.attribute8
510 ,p_lav_rec.attribute9
511 ,p_lav_rec.attribute10
512 ,p_lav_rec.attribute11
513 ,p_lav_rec.attribute12
514 ,p_lav_rec.attribute13
515 ,p_lav_rec.attribute14
516 ,p_lav_rec.attribute15
517 ,G_USER_ID
518 ,SYSDATE
519 ,G_USER_ID
520 ,SYSDATE
521 ,G_LOGIN_ID
522 ,p_lav_rec.leaseapp_template_id
523 ,p_lav_rec.version_status
524 ,p_lav_rec.version_number
525 ,p_lav_rec.valid_from
526 ,p_lav_rec.valid_to
527 ,p_lav_rec.checklist_id
528 ,p_lav_rec.contract_template_id
529 );
530
531 x_return_status := G_RET_STS_SUCCESS;
532
533 EXCEPTION
534
535 WHEN OTHERS THEN
536
537 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
538 p_msg_name => G_DB_ERROR,
539 p_token1 => G_PROG_NAME_TOKEN,
540 p_token1_value => l_prog_name,
541 p_token2 => G_SQLCODE_TOKEN,
542 p_token2_value => sqlcode,
543 p_token3 => G_SQLERRM_TOKEN,
544 p_token3_value => sqlerrm);
545
546 x_return_status := G_RET_STS_UNEXP_ERROR;
547
548 END insert_row;
549
550
551 ----------------------------
552 -- PROCEDURE insert_row (TL)
553 ----------------------------
554 PROCEDURE insert_row (x_return_status OUT NOCOPY VARCHAR2, p_lavtl_rec IN lavtl_rec_type) IS
555
556 CURSOR get_languages IS
557 SELECT language_code
558 FROM FND_LANGUAGES
559 WHERE INSTALLED_FLAG IN ('I', 'B');
560
561 l_sfwt_flag VARCHAR2(1);
562 l_prog_name VARCHAR2(61);
563
564 BEGIN
565
566 l_prog_name := G_PKG_NAME||'.insert_row (TL)';
567
568 FOR l_lang_rec IN get_languages LOOP
569
570 IF l_lang_rec.language_code = USERENV('LANG') THEN
571 l_sfwt_flag := 'N';
572 ELSE
573 l_sfwt_flag := 'Y';
574 END IF;
575
576 INSERT INTO OKL_LEASEAPP_TEMPL_VERSIONS_TL (
577 id
578 ,language
579 ,source_lang
580 ,sfwt_flag
581 ,created_by
582 ,creation_date
583 ,last_updated_by
584 ,last_update_date
585 ,last_update_login
586 ,short_description)
587 VALUES (
588 p_lavtl_rec.id
589 ,l_lang_rec.language_code
590 ,USERENV('LANG')
591 ,l_sfwt_flag
592 ,G_USER_ID
593 ,SYSDATE
594 ,G_USER_ID
595 ,SYSDATE
596 ,G_LOGIN_ID
597 ,p_lavtl_rec.short_description);
598
599 END LOOP;
600
601 x_return_status := G_RET_STS_SUCCESS;
602
603 EXCEPTION
604
605 WHEN OTHERS THEN
606
607 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
608 p_msg_name => G_DB_ERROR,
609 p_token1 => G_PROG_NAME_TOKEN,
610 p_token1_value => l_prog_name,
611 p_token2 => G_SQLCODE_TOKEN,
612 p_token2_value => sqlcode,
613 p_token3 => G_SQLERRM_TOKEN,
614 p_token3_value => sqlerrm);
615
616 x_return_status := G_RET_STS_UNEXP_ERROR;
617
618 END insert_row;
619
620 ---------------------------
621 -- PROCEDURE insert_row (V)
622 ---------------------------
623 PROCEDURE insert_row (
624 x_return_status OUT NOCOPY VARCHAR2,
625 p_lavv_rec IN lavv_rec_type,
626 x_lavv_rec OUT NOCOPY lavv_rec_type) IS
627
628 l_return_status VARCHAR2(1);
629
630 l_lavv_rec lavv_rec_type;
631 l_lav_rec lav_rec_type;
632 l_lavtl_rec lavtl_rec_type;
633
634 l_prog_name VARCHAR2(61);
635
636 BEGIN
637
638 l_prog_name := G_PKG_NAME||'.insert_row (V)';
639
640 l_lavv_rec := null_out_defaults (p_lavv_rec);
641
642 SELECT okl_lav_seq.nextval INTO l_lavv_rec.ID FROM DUAL;
643
644 l_lavv_rec.OBJECT_VERSION_NUMBER := 1;
645
646 l_return_status := validate_attributes(l_lavv_rec);
647
648 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
649 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
650 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
651 RAISE OKL_API.G_EXCEPTION_ERROR;
652 END IF;
653
654 l_return_status := validate_record(l_lavv_rec);
655
656 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
657 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
658 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
659 RAISE OKL_API.G_EXCEPTION_ERROR;
660 END IF;
661
662 migrate (l_lavv_rec, l_lav_rec);
663 migrate (l_lavv_rec, l_lavtl_rec);
664
665 insert_row (x_return_status => l_return_status, p_lav_rec => l_lav_rec);
666
667 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
668 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
669 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
670 RAISE OKL_API.G_EXCEPTION_ERROR;
671 END IF;
672
673 insert_row (x_return_status => l_return_status, p_lavtl_rec => l_lavtl_rec);
674
675 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
676 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
677 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
678 RAISE OKL_API.G_EXCEPTION_ERROR;
679 END IF;
680
681 x_lavv_rec := l_lavv_rec;
682 x_return_status := l_return_status;
683
684 EXCEPTION
685
686 WHEN OKL_API.G_EXCEPTION_ERROR THEN
687
688 x_return_status := G_RET_STS_ERROR;
689
690 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
691
692 x_return_status := G_RET_STS_UNEXP_ERROR;
693
694 WHEN OTHERS THEN
695
696 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
697 p_msg_name => G_DB_ERROR,
698 p_token1 => G_PROG_NAME_TOKEN,
699 p_token1_value => l_prog_name,
700 p_token2 => G_SQLCODE_TOKEN,
701 p_token2_value => sqlcode,
702 p_token3 => G_SQLERRM_TOKEN,
703 p_token3_value => sqlerrm);
704
705 x_return_status := G_RET_STS_UNEXP_ERROR;
706
707 END insert_row;
708
709 -----------------------------
710 -- PROCEDURE insert_row (REC)
711 -----------------------------
712 PROCEDURE insert_row(
713 p_api_version IN NUMBER,
714 p_init_msg_list IN VARCHAR2,
715 x_return_status OUT NOCOPY VARCHAR2,
716 x_msg_count OUT NOCOPY NUMBER,
717 x_msg_data OUT NOCOPY VARCHAR2,
718 p_lavv_rec IN lavv_rec_type,
719 x_lavv_rec OUT NOCOPY lavv_rec_type) IS
720
721 l_return_status VARCHAR2(1);
722
723 l_prog_name VARCHAR2(61);
724
725 BEGIN
726
727 l_prog_name := G_PKG_NAME||'.insert_row (REC)';
728
729 IF p_init_msg_list = G_TRUE THEN
730 FND_MSG_PUB.initialize;
731 END IF;
732
733 insert_row (x_return_status => l_return_status,
734 p_lavv_rec => p_lavv_rec,
735 x_lavv_rec => x_lavv_rec);
736
737 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
738 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
739 ELSIF l_return_status = G_RET_STS_ERROR THEN
740 RAISE OKL_API.G_EXCEPTION_ERROR;
741 END IF;
742
743 x_return_status := l_return_status;
744
745 EXCEPTION
746
747 WHEN OKL_API.G_EXCEPTION_ERROR THEN
748
749 x_return_status := G_RET_STS_ERROR;
750
751 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
752
753 x_return_status := G_RET_STS_UNEXP_ERROR;
754
755 WHEN OTHERS THEN
756
757 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
758 p_msg_name => G_DB_ERROR,
759 p_token1 => G_PROG_NAME_TOKEN,
760 p_token1_value => l_prog_name,
761 p_token2 => G_SQLCODE_TOKEN,
762 p_token2_value => sqlcode,
763 p_token3 => G_SQLERRM_TOKEN,
764 p_token3_value => sqlerrm);
765
766 x_return_status := G_RET_STS_UNEXP_ERROR;
767
768 END insert_row;
769
770 -----------------------------
771 -- PROCEDURE insert_row (TBL)
772 -----------------------------
773 PROCEDURE insert_row(
774 p_api_version IN NUMBER,
775 p_init_msg_list IN VARCHAR2,
776 x_return_status OUT NOCOPY VARCHAR2,
777 x_msg_count OUT NOCOPY NUMBER,
778 x_msg_data OUT NOCOPY VARCHAR2,
779 p_lavv_tbl IN lavv_tbl_type,
780 x_lavv_tbl OUT NOCOPY lavv_tbl_type) IS
781
782 l_return_status VARCHAR2(1);
783 i BINARY_INTEGER;
784
785 l_prog_name VARCHAR2(61);
786
787 BEGIN
788
789 l_prog_name := G_PKG_NAME||'.insert_row (TBL)';
790
791 IF p_init_msg_list = G_TRUE THEN
792 FND_MSG_PUB.initialize;
793 END IF;
794
795 IF (p_lavv_tbl.COUNT > 0) THEN
796 i := p_lavv_tbl.FIRST;
797 LOOP
798 IF p_lavv_tbl.EXISTS(i) THEN
799
800 insert_row (x_return_status => l_return_status,
801 p_lavv_rec => p_lavv_tbl(i),
802 x_lavv_rec => x_lavv_tbl(i));
803
804 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
805 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
806 ELSIF l_return_status = G_RET_STS_ERROR THEN
807 RAISE OKL_API.G_EXCEPTION_ERROR;
808 END IF;
809
810 EXIT WHEN (i = p_lavv_tbl.LAST);
811 i := p_lavv_tbl.NEXT(i);
812
813 END IF;
814
815 END LOOP;
816
817 ELSE
818
819 l_return_status := G_RET_STS_SUCCESS;
820
821 END IF;
822
823 x_return_status := l_return_status;
824
825 EXCEPTION
826
827 WHEN OKL_API.G_EXCEPTION_ERROR THEN
828
829 x_return_status := G_RET_STS_ERROR;
830
831 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
832
833 x_return_status := G_RET_STS_UNEXP_ERROR;
834
835 WHEN OTHERS THEN
836
837 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
838 p_msg_name => G_DB_ERROR,
839 p_token1 => G_PROG_NAME_TOKEN,
840 p_token1_value => l_prog_name,
841 p_token2 => G_SQLCODE_TOKEN,
842 p_token2_value => sqlcode,
843 p_token3 => G_SQLERRM_TOKEN,
844 p_token3_value => sqlerrm);
845
846 x_return_status := G_RET_STS_UNEXP_ERROR;
847
848 END insert_row;
849
850 ---------------------
851 -- PROCEDURE lock_row
852 ---------------------
853 PROCEDURE lock_row (x_return_status OUT NOCOPY VARCHAR2, p_lav_rec IN lav_rec_type) IS
854
855 E_Resource_Busy EXCEPTION;
856
857 PRAGMA EXCEPTION_INIT (E_Resource_Busy, -00054);
858
859 CURSOR lock_csr IS
860 SELECT OBJECT_VERSION_NUMBER
861 FROM OKL_LEASEAPP_TEMPL_VERSIONS_B
862 WHERE ID = p_lav_rec.id
863 AND OBJECT_VERSION_NUMBER = p_lav_rec.object_version_number
864 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
865
866 CURSOR lchk_csr IS
867 SELECT OBJECT_VERSION_NUMBER
868 FROM OKL_LEASEAPP_TEMPL_VERSIONS_B
869 WHERE ID = p_lav_rec.id;
870
871 l_object_version_number NUMBER;
872 lc_object_version_number NUMBER;
873
874 l_prog_name VARCHAR2(61);
875
876 BEGIN
877
878 l_prog_name := G_PKG_NAME||'.lock_row';
879
880 BEGIN
881 OPEN lock_csr;
882 FETCH lock_csr INTO l_object_version_number;
883 CLOSE lock_csr;
884 EXCEPTION
885 WHEN E_Resource_Busy THEN
886
887 IF (lock_csr%ISOPEN) THEN
888 CLOSE lock_csr;
889 END IF;
890 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
891 p_msg_name => G_OVN_ERROR2,
892 p_token1 => G_PROG_NAME_TOKEN,
893 p_token1_value => l_prog_name);
894 RAISE OKL_API.G_EXCEPTION_ERROR;
895 END;
896
897 IF l_object_version_number IS NULL THEN
898
899 OPEN lchk_csr;
900 FETCH lchk_csr INTO lc_object_version_number;
901 CLOSE lchk_csr;
902
903 IF lc_object_version_number IS NULL THEN
904
905 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
906 p_msg_name => G_OVN_ERROR3,
907 p_token1 => G_PROG_NAME_TOKEN,
908 p_token1_value => l_prog_name);
909
910 ELSIF lc_object_version_number <> p_lav_rec.object_version_number THEN
911
912 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
913 p_msg_name => G_OVN_ERROR,
914 p_token1 => G_PROG_NAME_TOKEN,
915 p_token1_value => l_prog_name);
916
917 END IF;
918
919 RAISE OKL_API.G_EXCEPTION_ERROR;
920
921 END IF;
922
923 x_return_status := G_RET_STS_SUCCESS;
924
925 EXCEPTION
926
927 WHEN OKL_API.G_EXCEPTION_ERROR THEN
928
929 x_return_status := G_RET_STS_ERROR;
930
931 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
932
933 x_return_status := G_RET_STS_UNEXP_ERROR;
934
935 WHEN OTHERS THEN
936
937 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
938 p_msg_name => G_DB_ERROR,
939 p_token1 => G_PROG_NAME_TOKEN,
940 p_token1_value => l_prog_name,
941 p_token2 => G_SQLCODE_TOKEN,
942 p_token2_value => sqlcode,
943 p_token3 => G_SQLERRM_TOKEN,
944 p_token3_value => sqlerrm);
945
946 x_return_status := G_RET_STS_UNEXP_ERROR;
947
948 END lock_row;
949
950 ---------------------------
951 -- PROCEDURE update_row (B)
952 ---------------------------
953 PROCEDURE update_row(x_return_status OUT NOCOPY VARCHAR2, p_lav_rec IN lav_rec_type) IS
954
955 l_return_status VARCHAR2(1);
956
957 l_prog_name VARCHAR2(61);
958
959 BEGIN
960
961 l_prog_name := G_PKG_NAME||'.update_row (B)';
962
963 lock_row (x_return_status => l_return_status, p_lav_rec => p_lav_rec);
964
965 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
966 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
967 ELSIF l_return_status = G_RET_STS_ERROR THEN
968 RAISE OKL_API.G_EXCEPTION_ERROR;
969 END IF;
970
971 UPDATE okl_leaseapp_templ_versions_b
972 SET
973 object_version_number = p_lav_rec.object_version_number+1
974 ,attribute_category = p_lav_rec.attribute_category
975 ,attribute1 = p_lav_rec.attribute1
976 ,attribute2 = p_lav_rec.attribute2
977 ,attribute3 = p_lav_rec.attribute3
978 ,attribute4 = p_lav_rec.attribute4
979 ,attribute5 = p_lav_rec.attribute5
980 ,attribute6 = p_lav_rec.attribute6
981 ,attribute7 = p_lav_rec.attribute7
982 ,attribute8 = p_lav_rec.attribute8
983 ,attribute9 = p_lav_rec.attribute9
984 ,attribute10 = p_lav_rec.attribute10
985 ,attribute11 = p_lav_rec.attribute11
986 ,attribute12 = p_lav_rec.attribute12
987 ,attribute13 = p_lav_rec.attribute13
988 ,attribute14 = p_lav_rec.attribute14
989 ,attribute15 = p_lav_rec.attribute15
990 ,leaseapp_template_id = p_lav_rec.leaseapp_template_id
991 ,version_status = p_lav_rec.version_status
992 ,version_number = p_lav_rec.version_number
993 ,valid_from = p_lav_rec.valid_from
994 ,valid_to = p_lav_rec.valid_to
995 ,checklist_id = p_lav_rec.checklist_id
996 ,contract_template_id = p_lav_rec.contract_template_id
997 WHERE id = p_lav_rec.id;
998
999 x_return_status := l_return_status;
1000
1001 EXCEPTION
1002
1003 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1004 x_return_status := G_RET_STS_ERROR;
1005
1006 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1007 x_return_status := G_RET_STS_UNEXP_ERROR;
1008
1009 WHEN OTHERS THEN
1010 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1011 p_msg_name => G_DB_ERROR,
1012 p_token1 => G_PROG_NAME_TOKEN,
1013 p_token1_value => l_prog_name,
1014 p_token2 => G_SQLCODE_TOKEN,
1015 p_token2_value => sqlcode,
1016 p_token3 => G_SQLERRM_TOKEN,
1017 p_token3_value => sqlerrm);
1018
1019 x_return_status := G_RET_STS_UNEXP_ERROR;
1020
1021 END update_row;
1022
1023 ----------------------------
1024 -- PROCEDURE update_row (TL)
1025 ----------------------------
1026 PROCEDURE update_row(x_return_status OUT NOCOPY VARCHAR2, p_lavtl_rec IN lavtl_rec_type) IS
1027
1028 l_prog_name VARCHAR2(61);
1029
1030 BEGIN
1031
1032 l_prog_name := G_PKG_NAME||'.update_row (TL)';
1033
1034 UPDATE OKL_LEASEAPP_TEMPL_VERSIONS_TL
1035 SET
1036 source_lang = USERENV('LANG')
1037 ,sfwt_flag = 'Y'
1038 ,last_updated_by = G_USER_ID
1039 ,last_update_date = SYSDATE
1040 ,last_update_login = G_LOGIN_ID
1041 ,short_description = p_lavtl_rec.short_description
1042 WHERE ID = p_lavtl_rec.id;
1043
1044 UPDATE OKL_LEASEAPP_TEMPL_VERSIONS_TL
1045 SET SFWT_FLAG = 'N'
1046 WHERE ID = p_lavtl_rec.id
1047 AND SOURCE_LANG = LANGUAGE;
1048
1049 x_return_status := G_RET_STS_SUCCESS;
1050
1051 EXCEPTION
1052
1053 WHEN OTHERS THEN
1054
1055 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1056 p_msg_name => G_DB_ERROR,
1057 p_token1 => G_PROG_NAME_TOKEN,
1058 p_token1_value => l_prog_name,
1059 p_token2 => G_SQLCODE_TOKEN,
1060 p_token2_value => sqlcode,
1061 p_token3 => G_SQLERRM_TOKEN,
1062 p_token3_value => sqlerrm);
1063
1064 x_return_status := G_RET_STS_UNEXP_ERROR;
1065
1066 END update_row;
1067
1068 ---------------------------
1069 -- PROCEDURE update_row (V)
1070 ---------------------------
1071 PROCEDURE update_row (
1072 x_return_status OUT NOCOPY VARCHAR2,
1073 p_lavv_rec IN lavv_rec_type,
1074 x_lavv_rec OUT NOCOPY lavv_rec_type) IS
1075
1076 l_prog_name VARCHAR2(61);
1077
1078 l_return_status VARCHAR2(1);
1079 l_lavv_rec lavv_rec_type;
1080 l_lav_rec lav_rec_type;
1081 l_lavtl_rec lavtl_rec_type;
1082
1083 ----------------------
1084 -- populate_new_record
1085 ----------------------
1086 FUNCTION populate_new_record (p_lavv_rec IN lavv_rec_type,
1087 x_lavv_rec OUT NOCOPY lavv_rec_type) RETURN VARCHAR2 IS
1088
1089 l_prog_name VARCHAR2(61) := G_PKG_NAME||'.populate_new_record';
1090 l_return_status VARCHAR2(1);
1091 l_db_lavv_rec lavv_rec_type;
1092
1093 BEGIN
1094
1095 x_lavv_rec := p_lavv_rec;
1096 l_db_lavv_rec := get_rec (p_lavv_rec.id, l_return_status);
1097
1098 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1099 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1100 ELSIF l_return_status = G_RET_STS_ERROR THEN
1101 RAISE OKL_API.G_EXCEPTION_ERROR;
1102 END IF;
1103
1104 IF x_lavv_rec.attribute_category IS NULL THEN
1105 x_lavv_rec.attribute_category := l_db_lavv_rec.attribute_category;
1106 END IF;
1107 IF x_lavv_rec.attribute1 IS NULL THEN
1108 x_lavv_rec.attribute1 := l_db_lavv_rec.attribute1;
1109 END IF;
1110 IF x_lavv_rec.attribute2 IS NULL THEN
1111 x_lavv_rec.attribute2 := l_db_lavv_rec.attribute2;
1112 END IF;
1113 IF x_lavv_rec.attribute3 IS NULL THEN
1114 x_lavv_rec.attribute3 := l_db_lavv_rec.attribute3;
1115 END IF;
1116 IF x_lavv_rec.attribute4 IS NULL THEN
1117 x_lavv_rec.attribute4 := l_db_lavv_rec.attribute4;
1118 END IF;
1119 IF x_lavv_rec.attribute5 IS NULL THEN
1120 x_lavv_rec.attribute5 := l_db_lavv_rec.attribute5;
1121 END IF;
1122 IF x_lavv_rec.attribute6 IS NULL THEN
1123 x_lavv_rec.attribute6 := l_db_lavv_rec.attribute6;
1124 END IF;
1125 IF x_lavv_rec.attribute7 IS NULL THEN
1126 x_lavv_rec.attribute7 := l_db_lavv_rec.attribute7;
1127 END IF;
1128 IF x_lavv_rec.attribute8 IS NULL THEN
1129 x_lavv_rec.attribute8 := l_db_lavv_rec.attribute8;
1130 END IF;
1131 IF x_lavv_rec.attribute9 IS NULL THEN
1132 x_lavv_rec.attribute9 := l_db_lavv_rec.attribute9;
1133 END IF;
1134 IF x_lavv_rec.attribute10 IS NULL THEN
1135 x_lavv_rec.attribute10 := l_db_lavv_rec.attribute10;
1136 END IF;
1137 IF x_lavv_rec.attribute11 IS NULL THEN
1138 x_lavv_rec.attribute11 := l_db_lavv_rec.attribute11;
1139 END IF;
1140 IF x_lavv_rec.attribute12 IS NULL THEN
1141 x_lavv_rec.attribute12 := l_db_lavv_rec.attribute12;
1142 END IF;
1143 IF x_lavv_rec.attribute13 IS NULL THEN
1144 x_lavv_rec.attribute13 := l_db_lavv_rec.attribute13;
1145 END IF;
1146 IF x_lavv_rec.attribute14 IS NULL THEN
1147 x_lavv_rec.attribute14 := l_db_lavv_rec.attribute14;
1148 END IF;
1149 IF x_lavv_rec.attribute15 IS NULL THEN
1150 x_lavv_rec.attribute15 := l_db_lavv_rec.attribute15;
1151 END IF;
1152 IF x_lavv_rec.leaseapp_template_id IS NULL THEN
1153 x_lavv_rec.leaseapp_template_id := l_db_lavv_rec.leaseapp_template_id;
1154 END IF;
1155 IF x_lavv_rec.version_status IS NULL THEN
1156 x_lavv_rec.version_status := l_db_lavv_rec.version_status;
1157 END IF;
1158 IF x_lavv_rec.version_number IS NULL THEN
1159 x_lavv_rec.version_number := l_db_lavv_rec.version_number;
1160 END IF;
1161 IF x_lavv_rec.valid_from IS NULL THEN
1162 x_lavv_rec.valid_from := l_db_lavv_rec.valid_from;
1163 END IF;
1164 IF x_lavv_rec.valid_to IS NULL THEN
1165 x_lavv_rec.valid_to := l_db_lavv_rec.valid_to;
1166 END IF;
1167 IF x_lavv_rec.checklist_id IS NULL THEN
1168 x_lavv_rec.checklist_id := l_db_lavv_rec.checklist_id;
1169 END IF;
1170 IF x_lavv_rec.contract_template_id IS NULL THEN
1171 x_lavv_rec.contract_template_id := l_db_lavv_rec.contract_template_id;
1172 END IF;
1173 IF x_lavv_rec.short_description IS NULL THEN
1174 x_lavv_rec.short_description := l_db_lavv_rec.short_description;
1175 END IF;
1176
1177 RETURN l_return_status;
1178
1179 EXCEPTION
1180 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1181
1182 x_return_status := G_RET_STS_ERROR;
1183
1184 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1185
1186 x_return_status := G_RET_STS_UNEXP_ERROR;
1187
1188 WHEN OTHERS THEN
1189
1190 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1191 p_msg_name => G_DB_ERROR,
1192 p_token1 => G_PROG_NAME_TOKEN,
1193 p_token1_value => l_prog_name,
1194 p_token2 => G_SQLCODE_TOKEN,
1195 p_token2_value => sqlcode,
1196 p_token3 => G_SQLERRM_TOKEN,
1197 p_token3_value => sqlerrm);
1198
1199 x_return_status := G_RET_STS_UNEXP_ERROR;
1200
1201 END populate_new_record;
1202
1203 BEGIN
1204
1205 l_prog_name := G_PKG_NAME||'.update_row (V)';
1206
1207 l_return_status := populate_new_record (p_lavv_rec, l_lavv_rec);
1208 l_lavv_rec := null_out_defaults(l_lavv_rec);
1209
1210 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1211 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1212 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1213 RAISE OKL_API.G_EXCEPTION_ERROR;
1214 END IF;
1215
1216 l_return_status := validate_attributes (l_lavv_rec);
1217
1218 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1219 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1220 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1221 RAISE OKL_API.G_EXCEPTION_ERROR;
1222 END IF;
1223
1224 l_return_status := validate_record (l_lavv_rec);
1225
1226 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1227 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1228 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1229 RAISE OKL_API.G_EXCEPTION_ERROR;
1230 END IF;
1231
1232 migrate (l_lavv_rec, l_lav_rec);
1233 migrate (l_lavv_rec, l_lavtl_rec);
1234
1235 update_row (x_return_status => l_return_status, p_lav_rec => l_lav_rec);
1236
1237 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1238 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1239 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1240 RAISE OKL_API.G_EXCEPTION_ERROR;
1241 END IF;
1242
1243 update_row (x_return_status => l_return_status, p_lavtl_rec => l_lavtl_rec);
1244
1245 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1246 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1247 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1248 RAISE OKL_API.G_EXCEPTION_ERROR;
1249 END IF;
1250
1251 x_return_status := l_return_status;
1252 x_lavv_rec := l_lavv_rec;
1253
1254 EXCEPTION
1255
1256 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1257
1258 x_return_status := G_RET_STS_ERROR;
1259
1260 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1261
1262 x_return_status := G_RET_STS_UNEXP_ERROR;
1263
1264 WHEN OTHERS THEN
1265
1266 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1267 p_msg_name => G_DB_ERROR,
1268 p_token1 => G_PROG_NAME_TOKEN,
1269 p_token1_value => l_prog_name,
1270 p_token2 => G_SQLCODE_TOKEN,
1271 p_token2_value => sqlcode,
1272 p_token3 => G_SQLERRM_TOKEN,
1273 p_token3_value => sqlerrm);
1274
1275 x_return_status := G_RET_STS_UNEXP_ERROR;
1276
1277 END update_row;
1278
1279 -----------------------------
1280 -- PROCEDURE update_row (REC)
1281 -----------------------------
1282 PROCEDURE update_row(
1283 p_api_version IN NUMBER,
1284 p_init_msg_list IN VARCHAR2,
1285 x_return_status OUT NOCOPY VARCHAR2,
1286 x_msg_count OUT NOCOPY NUMBER,
1287 x_msg_data OUT NOCOPY VARCHAR2,
1288 p_lavv_rec IN lavv_rec_type,
1289 x_lavv_rec OUT NOCOPY lavv_rec_type) IS
1290
1291 l_return_status VARCHAR2(1);
1292
1293 l_prog_name VARCHAR2(61);
1294
1295 BEGIN
1296
1297 l_prog_name := G_PKG_NAME||'.update_row (REC)';
1298
1299 IF p_init_msg_list = G_TRUE THEN
1300 FND_MSG_PUB.initialize;
1301 END IF;
1302
1303 update_row (x_return_status => l_return_status,
1304 p_lavv_rec => p_lavv_rec,
1305 x_lavv_rec => x_lavv_rec);
1306
1307 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1308 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1309 ELSIF l_return_status = G_RET_STS_ERROR THEN
1310 RAISE OKL_API.G_EXCEPTION_ERROR;
1311 END IF;
1312
1313 x_return_status := l_return_status;
1314
1315 EXCEPTION
1316
1317 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1318
1319 x_return_status := G_RET_STS_ERROR;
1320
1321 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1322
1323 x_return_status := G_RET_STS_UNEXP_ERROR;
1324
1325 WHEN OTHERS THEN
1326
1327 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1328 p_msg_name => G_DB_ERROR,
1329 p_token1 => G_PROG_NAME_TOKEN,
1330 p_token1_value => l_prog_name,
1331 p_token2 => G_SQLCODE_TOKEN,
1332 p_token2_value => sqlcode,
1333 p_token3 => G_SQLERRM_TOKEN,
1334 p_token3_value => sqlerrm);
1335
1336 x_return_status := G_RET_STS_UNEXP_ERROR;
1337
1338 END update_row;
1339
1340 -----------------------------
1341 -- PROCEDURE update_row (TBL)
1342 -----------------------------
1343 PROCEDURE update_row(
1344 p_api_version IN NUMBER,
1345 p_init_msg_list IN VARCHAR2,
1346 x_return_status OUT NOCOPY VARCHAR2,
1347 x_msg_count OUT NOCOPY NUMBER,
1348 x_msg_data OUT NOCOPY VARCHAR2,
1349 p_lavv_tbl IN lavv_tbl_type,
1350 x_lavv_tbl OUT NOCOPY lavv_tbl_type) IS
1351
1352 l_return_status VARCHAR2(1);
1353 i BINARY_INTEGER;
1354 l_prog_name VARCHAR2(61);
1355
1356 BEGIN
1357
1358 l_prog_name := G_PKG_NAME||'.update_row (TBL)';
1359
1360 IF p_init_msg_list = G_TRUE THEN
1361 FND_MSG_PUB.initialize;
1362 END IF;
1363
1364 x_lavv_tbl := p_lavv_tbl;
1365
1366 IF (p_lavv_tbl.COUNT > 0) THEN
1367
1368 i := p_lavv_tbl.FIRST;
1369
1370 LOOP
1371
1372 IF p_lavv_tbl.EXISTS(i) THEN
1373 update_row (x_return_status => l_return_status,
1374 p_lavv_rec => p_lavv_tbl(i),
1375 x_lavv_rec => x_lavv_tbl(i));
1376
1377 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1378 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1379 ELSIF l_return_status = G_RET_STS_ERROR THEN
1380 RAISE OKL_API.G_EXCEPTION_ERROR;
1381 END IF;
1382
1383 EXIT WHEN (i = p_lavv_tbl.LAST);
1384 i := p_lavv_tbl.NEXT(i);
1385
1386 END IF;
1387
1388 END LOOP;
1389
1390 ELSE
1391
1392 l_return_status := G_RET_STS_SUCCESS;
1393
1394 END IF;
1395
1396 x_return_status := l_return_status;
1397
1398 EXCEPTION
1399
1400 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1401
1402 x_return_status := G_RET_STS_ERROR;
1403
1404 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1405
1406 x_return_status := G_RET_STS_UNEXP_ERROR;
1407
1408 WHEN OTHERS THEN
1409
1410 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1411 p_msg_name => G_DB_ERROR,
1412 p_token1 => G_PROG_NAME_TOKEN,
1413 p_token1_value => l_prog_name,
1414 p_token2 => G_SQLCODE_TOKEN,
1415 p_token2_value => sqlcode,
1416 p_token3 => G_SQLERRM_TOKEN,
1417 p_token3_value => sqlerrm);
1418
1419 x_return_status := G_RET_STS_UNEXP_ERROR;
1420
1421 END update_row;
1422
1423 -----------------
1424 -- delete_row (V)
1425 -----------------
1426 PROCEDURE delete_row(
1427 x_return_status OUT NOCOPY VARCHAR2,
1428 p_id IN NUMBER) IS
1429
1430 l_prog_name VARCHAR2(61);
1431
1432 BEGIN
1433
1434 l_prog_name := G_PKG_NAME||'.delete_row (V)';
1435
1436 DELETE FROM OKL_LEASEAPP_TEMPL_VERSIONS_B WHERE id = p_id;
1437 DELETE FROM OKL_LEASEAPP_TEMPL_VERSIONS_TL WHERE id = p_id;
1438
1439 x_return_status := G_RET_STS_SUCCESS;
1440
1441 EXCEPTION
1442
1443 WHEN OTHERS THEN
1444
1445 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1446 p_msg_name => G_DB_ERROR,
1447 p_token1 => G_PROG_NAME_TOKEN,
1448 p_token1_value => l_prog_name,
1449 p_token2 => G_SQLCODE_TOKEN,
1450 p_token2_value => sqlcode,
1451 p_token3 => G_SQLERRM_TOKEN,
1452 p_token3_value => sqlerrm);
1453
1454 x_return_status := G_RET_STS_UNEXP_ERROR;
1455
1456 END delete_row;
1457
1458 -----------------------------
1459 -- PROCEDURE delete_row (REC)
1460 -----------------------------
1461 PROCEDURE delete_row(
1462 p_api_version IN NUMBER,
1463 p_init_msg_list IN VARCHAR2,
1464 x_return_status OUT NOCOPY VARCHAR2,
1465 x_msg_count OUT NOCOPY NUMBER,
1466 x_msg_data OUT NOCOPY VARCHAR2,
1467 p_lavv_rec IN lavv_rec_type) IS
1468
1469 l_return_status VARCHAR2(1);
1470
1471 l_prog_name VARCHAR2(61);
1472
1473 BEGIN
1474
1475 l_prog_name := G_PKG_NAME||'.delete_row (REC)';
1476
1477 IF p_init_msg_list = G_TRUE THEN
1478 FND_MSG_PUB.initialize;
1479 END IF;
1480
1481 delete_row (x_return_status => l_return_status,
1482 p_id => p_lavv_rec.id);
1483
1484 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1485 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1486 ELSIF l_return_status = G_RET_STS_ERROR THEN
1487 RAISE OKL_API.G_EXCEPTION_ERROR;
1488 END IF;
1489
1490 x_return_status := l_return_status;
1491
1492 EXCEPTION
1493
1494 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1495
1496 x_return_status := G_RET_STS_ERROR;
1497
1498 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1499
1500 x_return_status := G_RET_STS_UNEXP_ERROR;
1501
1502 WHEN OTHERS THEN
1503
1504 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1505 p_msg_name => G_DB_ERROR,
1506 p_token1 => G_PROG_NAME_TOKEN,
1507 p_token1_value => l_prog_name,
1508 p_token2 => G_SQLCODE_TOKEN,
1509 p_token2_value => sqlcode,
1510 p_token3 => G_SQLERRM_TOKEN,
1511 p_token3_value => sqlerrm);
1512
1513 x_return_status := G_RET_STS_UNEXP_ERROR;
1514
1515 END delete_row;
1516
1517 -------------------
1518 -- delete_row (TBL)
1519 -------------------
1520 PROCEDURE delete_row(
1521 p_api_version IN NUMBER,
1522 p_init_msg_list IN VARCHAR2,
1523 x_return_status OUT NOCOPY VARCHAR2,
1524 x_msg_count OUT NOCOPY NUMBER,
1525 x_msg_data OUT NOCOPY VARCHAR2,
1526 p_lavv_tbl IN lavv_tbl_type) IS
1527
1528 l_return_status VARCHAR2(1);
1529 i BINARY_INTEGER;
1530
1531 l_prog_name VARCHAR2(61);
1532
1533 BEGIN
1534
1535 l_prog_name := G_PKG_NAME||'.delete_row (TBL)';
1536
1537 IF p_init_msg_list = G_TRUE THEN
1538 FND_MSG_PUB.initialize;
1539 END IF;
1540
1541 IF (p_lavv_tbl.COUNT > 0) THEN
1542
1543 i := p_lavv_tbl.FIRST;
1544
1545 LOOP
1546
1547 IF p_lavv_tbl.EXISTS(i) THEN
1548
1549 delete_row (x_return_status => l_return_status,
1550 p_id => p_lavv_tbl(i).id);
1551
1552 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1553 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1554 ELSIF l_return_status = G_RET_STS_ERROR THEN
1555 RAISE OKL_API.G_EXCEPTION_ERROR;
1556 END IF;
1557
1558 EXIT WHEN (i = p_lavv_tbl.LAST);
1559 i := p_lavv_tbl.NEXT(i);
1560
1561 END IF;
1562
1563 END LOOP;
1564
1565 ELSE
1566
1567 l_return_status := G_RET_STS_SUCCESS;
1568
1569 END IF;
1570
1571 x_return_status := l_return_status;
1572
1573 EXCEPTION
1574
1575 WHEN OTHERS THEN
1576
1577 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1578 p_msg_name => G_DB_ERROR,
1579 p_token1 => G_PROG_NAME_TOKEN,
1580 p_token1_value => l_prog_name,
1581 p_token2 => G_SQLCODE_TOKEN,
1582 p_token2_value => sqlcode,
1583 p_token3 => G_SQLERRM_TOKEN,
1584 p_token3_value => sqlerrm);
1585
1586 x_return_status := G_RET_STS_UNEXP_ERROR;
1587
1588 END delete_row;
1589
1590 END OKL_LAV_PVT;