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