[Home] [Help]
PACKAGE BODY: APPS.OKL_LAP_PVT
Source
1 PACKAGE BODY OKL_LAP_PVT AS
2 /* $Header: OKLSLAPB.pls 120.5.12010000.2 2008/11/13 13:37:40 kkorrapo ship $ */
3
4 -------------------------
5 -- PROCEDURE add_language
6 -------------------------
7 PROCEDURE add_language IS
8
9 BEGIN
10
11 DELETE FROM OKL_LEASE_APPLICATIONS_TL T
12 WHERE NOT EXISTS (
13 SELECT NULL
14 FROM OKL_LEASE_APPS_ALL_B B
15 WHERE B.ID =T.ID);
16
17 UPDATE OKL_LEASE_APPLICATIONS_TL T
18 SET (SHORT_DESCRIPTION,
19 COMMENTS) = (SELECT B.SHORT_DESCRIPTION
20 , B.COMMENTS
21 FROM OKL_LEASE_APPLICATIONS_TL B
22 WHERE B.ID = T.ID
23 AND B.LANGUAGE = T.SOURCE_LANG)
24 WHERE (T.ID, T.LANGUAGE) IN (SELECT SUBT.ID
25 , SUBT.LANGUAGE
26 FROM OKL_LEASE_APPLICATIONS_TL SUBB
27 , OKL_LEASE_APPLICATIONS_TL SUBT
28 WHERE SUBB.ID = SUBT.ID
29 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
30 AND (SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
31 OR (SUBB.COMMENTS <> SUBT.COMMENTS)
32 OR (SUBB.SHORT_DESCRIPTION IS NULL AND SUBT.SHORT_DESCRIPTION IS NOT NULL)
33 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
34 )
35 );
36
37 INSERT INTO OKL_LEASE_APPLICATIONS_TL (
38 ID,
39 LANGUAGE,
40 SOURCE_LANG,
41 SFWT_FLAG,
42 CREATED_BY,
43 CREATION_DATE,
44 LAST_UPDATED_BY,
45 LAST_UPDATE_DATE,
46 LAST_UPDATE_LOGIN,
47 SHORT_DESCRIPTION,
48 COMMENTS)
49 SELECT
50 B.ID,
51 L.LANGUAGE_CODE,
52 B.SOURCE_LANG,
53 B.SFWT_FLAG,
54 B.CREATED_BY,
55 B.CREATION_DATE,
56 B.LAST_UPDATED_BY,
57 B.LAST_UPDATE_DATE,
58 B.LAST_UPDATE_LOGIN,
59 B.SHORT_DESCRIPTION,
60 B.COMMENTS
61 FROM OKL_LEASE_APPLICATIONS_TL B, FND_LANGUAGES L
62 WHERE L.INSTALLED_FLAG IN ('I', 'B')
63 AND B.LANGUAGE = USERENV('LANG')
64 AND NOT EXISTS (
65 SELECT NULL
66 FROM OKL_LEASE_APPLICATIONS_TL T
67 WHERE T.ID = B.ID
68 AND T.LANGUAGE = L.LANGUAGE_CODE
69 );
70
71 END add_language;
72
73 -----------------------------
74 -- FUNCTION null_out_defaults
75 -----------------------------
76 FUNCTION null_out_defaults (p_lapv_rec IN lapv_rec_type) RETURN lapv_rec_type IS
77
78 l_lapv_rec lapv_rec_type;
79
80 BEGIN
81
82 l_lapv_rec := p_lapv_rec;
83
84 -- Not applicable to ID and OBJECT_VERSION_NUMBER
85
86 IF l_lapv_rec.attribute_category = FND_API.G_MISS_CHAR THEN
87 l_lapv_rec.attribute_category := NULL;
88 END IF;
89 IF l_lapv_rec.attribute1 = FND_API.G_MISS_CHAR THEN
90 l_lapv_rec.attribute1 := NULL;
91 END IF;
92 IF l_lapv_rec.attribute2 = FND_API.G_MISS_CHAR THEN
93 l_lapv_rec.attribute2 := NULL;
94 END IF;
95 IF l_lapv_rec.attribute3 = FND_API.G_MISS_CHAR THEN
96 l_lapv_rec.attribute3 := NULL;
97 END IF;
98 IF l_lapv_rec.attribute4 = FND_API.G_MISS_CHAR THEN
99 l_lapv_rec.attribute4 := NULL;
100 END IF;
101 IF l_lapv_rec.attribute5 = FND_API.G_MISS_CHAR THEN
102 l_lapv_rec.attribute5 := NULL;
103 END IF;
104 IF l_lapv_rec.attribute6 = FND_API.G_MISS_CHAR THEN
105 l_lapv_rec.attribute6 := NULL;
106 END IF;
107 IF l_lapv_rec.attribute7 = FND_API.G_MISS_CHAR THEN
108 l_lapv_rec.attribute7 := NULL;
109 END IF;
110 IF l_lapv_rec.attribute8 = FND_API.G_MISS_CHAR THEN
111 l_lapv_rec.attribute8 := NULL;
112 END IF;
113 IF l_lapv_rec.attribute9 = FND_API.G_MISS_CHAR THEN
114 l_lapv_rec.attribute9 := NULL;
115 END IF;
116 IF l_lapv_rec.attribute10 = FND_API.G_MISS_CHAR THEN
117 l_lapv_rec.attribute10 := NULL;
118 END IF;
119 IF l_lapv_rec.attribute11 = FND_API.G_MISS_CHAR THEN
120 l_lapv_rec.attribute11 := NULL;
121 END IF;
122 IF l_lapv_rec.attribute12 = FND_API.G_MISS_CHAR THEN
123 l_lapv_rec.attribute12 := NULL;
124 END IF;
125 IF l_lapv_rec.attribute13 = FND_API.G_MISS_CHAR THEN
126 l_lapv_rec.attribute13 := NULL;
127 END IF;
128 IF l_lapv_rec.attribute14 = FND_API.G_MISS_CHAR THEN
129 l_lapv_rec.attribute14 := NULL;
130 END IF;
131 IF l_lapv_rec.attribute15 = FND_API.G_MISS_CHAR THEN
132 l_lapv_rec.attribute15 := NULL;
133 END IF;
134 IF l_lapv_rec.reference_number = FND_API.G_MISS_CHAR THEN
135 l_lapv_rec.reference_number := NULL;
136 END IF;
137 IF l_lapv_rec.application_status = FND_API.G_MISS_CHAR THEN
138 l_lapv_rec.application_status := NULL;
139 END IF;
140 IF l_lapv_rec.valid_from = FND_API.G_MISS_DATE THEN
141 l_lapv_rec.valid_from := NULL;
142 END IF;
143 IF l_lapv_rec.valid_to = FND_API.G_MISS_DATE THEN
144 l_lapv_rec.valid_to := NULL;
145 END IF;
146 IF l_lapv_rec.org_id = FND_API.G_MISS_NUM THEN
147 l_lapv_rec.org_id := NULL;
148 END IF;
149 IF l_lapv_rec.inv_org_id = FND_API.G_MISS_NUM THEN
150 l_lapv_rec.inv_org_id := NULL;
151 END IF;
152 IF l_lapv_rec.prospect_id = FND_API.G_MISS_NUM THEN
153 l_lapv_rec.prospect_id := NULL;
154 END IF;
155 IF l_lapv_rec.prospect_address_id = FND_API.G_MISS_NUM THEN
156 l_lapv_rec.prospect_address_id := NULL;
157 END IF;
158 IF l_lapv_rec.cust_acct_id = FND_API.G_MISS_NUM THEN
159 l_lapv_rec.cust_acct_id := NULL;
160 END IF;
161 IF l_lapv_rec.industry_class = FND_API.G_MISS_CHAR THEN
162 l_lapv_rec.industry_class := NULL;
163 END IF;
164 IF l_lapv_rec.industry_code = FND_API.G_MISS_CHAR THEN
165 l_lapv_rec.industry_code := NULL;
166 END IF;
167 IF l_lapv_rec.currency_code = FND_API.G_MISS_CHAR THEN
168 l_lapv_rec.currency_code := NULL;
169 END IF;
170 IF l_lapv_rec.currency_conversion_type = FND_API.G_MISS_CHAR THEN
171 l_lapv_rec.currency_conversion_type := NULL;
172 END IF;
173 IF l_lapv_rec.currency_conversion_rate = FND_API.G_MISS_NUM THEN
174 l_lapv_rec.currency_conversion_rate := NULL;
175 END IF;
176 IF l_lapv_rec.currency_conversion_date = FND_API.G_MISS_DATE THEN
177 l_lapv_rec.currency_conversion_date := NULL;
178 END IF;
179 IF l_lapv_rec.leaseapp_template_id = FND_API.G_MISS_NUM THEN
180 l_lapv_rec.leaseapp_template_id := NULL;
181 END IF;
182 IF l_lapv_rec.parent_leaseapp_id = FND_API.G_MISS_NUM THEN
183 l_lapv_rec.parent_leaseapp_id := NULL;
184 END IF;
185 IF l_lapv_rec.credit_line_id = FND_API.G_MISS_NUM THEN
186 l_lapv_rec.credit_line_id := NULL;
187 END IF;
188 IF l_lapv_rec.program_agreement_id = FND_API.G_MISS_NUM THEN
189 l_lapv_rec.program_agreement_id := NULL;
190 END IF;
191 IF l_lapv_rec.master_lease_id = FND_API.G_MISS_NUM THEN
192 l_lapv_rec.master_lease_id := NULL;
193 END IF;
194 IF l_lapv_rec.sales_rep_id = FND_API.G_MISS_NUM THEN
195 l_lapv_rec.sales_rep_id := NULL;
196 END IF;
197 IF l_lapv_rec.sales_territory_id = FND_API.G_MISS_NUM THEN
198 l_lapv_rec.sales_territory_id := NULL;
199 END IF;
200 IF l_lapv_rec.originating_vendor_id = FND_API.G_MISS_NUM THEN
201 l_lapv_rec.originating_vendor_id := NULL;
202 END IF;
203 IF l_lapv_rec.lease_opportunity_id = FND_API.G_MISS_NUM THEN
204 l_lapv_rec.lease_opportunity_id := NULL;
205 END IF;
206 IF l_lapv_rec.short_description = FND_API.G_MISS_CHAR THEN
207 l_lapv_rec.short_description := NULL;
208 END IF;
209 IF l_lapv_rec.comments = FND_API.G_MISS_CHAR THEN
210 l_lapv_rec.comments := NULL;
211 END IF;
212 --VARANGAN for bug#4747179
213 IF l_lapv_rec.cr_exp_days = FND_API.G_MISS_NUM THEN
214 l_lapv_rec.cr_exp_days := NULL;
215 END IF;
216 --VARANGAN for bug#4747179
217 --Bug 4872271 PAGARG start
218 IF l_lapv_rec.action = FND_API.G_MISS_CHAR THEN
219 l_lapv_rec.action := NULL;
220 END IF;
221 IF l_lapv_rec.orig_status = FND_API.G_MISS_CHAR THEN
222 l_lapv_rec.orig_status := NULL;
223 END IF;
224 --Bug 4872271 PAGARG end
225 RETURN l_lapv_rec;
226
227 END null_out_defaults;
228
229 -------------------
230 -- FUNCTION get_rec
231 -------------------
232 FUNCTION get_rec (p_id IN NUMBER
233 ,x_return_status OUT NOCOPY VARCHAR2) RETURN lapv_rec_type IS
234
235 l_lapv_rec lapv_rec_type;
236 l_prog_name VARCHAR2(61);
237
238 BEGIN
239
240 l_prog_name := G_PKG_NAME||'.get_rec';
241
242 SELECT
243 id
244 ,object_version_number
245 ,attribute_category
246 ,attribute1
247 ,attribute2
248 ,attribute3
249 ,attribute4
250 ,attribute5
251 ,attribute6
252 ,attribute7
253 ,attribute8
254 ,attribute9
255 ,attribute10
256 ,attribute11
257 ,attribute12
258 ,attribute13
259 ,attribute14
260 ,attribute15
261 ,reference_number
262 ,application_status
263 ,valid_from
264 ,valid_to
265 ,org_id
266 ,inv_org_id
267 ,prospect_id
268 ,prospect_address_id
269 ,cust_acct_id
270 ,industry_class
271 ,industry_code
272 ,currency_code
273 ,currency_conversion_type
274 ,currency_conversion_rate
275 ,currency_conversion_date
276 ,leaseapp_template_id
277 ,parent_leaseapp_id
278 ,credit_line_id
279 ,program_agreement_id
280 ,master_lease_id
281 ,sales_rep_id
282 ,sales_territory_id
283 ,originating_vendor_id
284 ,lease_opportunity_id
285 ,short_description
286 ,comments
287 ,cr_exp_days --VARANGAN for bug#4747179
288 ,action
289 ,orig_status
290 INTO
291 l_lapv_rec.id
292 ,l_lapv_rec.object_version_number
293 ,l_lapv_rec.attribute_category
294 ,l_lapv_rec.attribute1
295 ,l_lapv_rec.attribute2
296 ,l_lapv_rec.attribute3
297 ,l_lapv_rec.attribute4
298 ,l_lapv_rec.attribute5
299 ,l_lapv_rec.attribute6
300 ,l_lapv_rec.attribute7
301 ,l_lapv_rec.attribute8
302 ,l_lapv_rec.attribute9
303 ,l_lapv_rec.attribute10
304 ,l_lapv_rec.attribute11
305 ,l_lapv_rec.attribute12
306 ,l_lapv_rec.attribute13
307 ,l_lapv_rec.attribute14
308 ,l_lapv_rec.attribute15
309 ,l_lapv_rec.reference_number
310 ,l_lapv_rec.application_status
311 ,l_lapv_rec.valid_from
312 ,l_lapv_rec.valid_to
313 ,l_lapv_rec.org_id
314 ,l_lapv_rec.inv_org_id
315 ,l_lapv_rec.prospect_id
316 ,l_lapv_rec.prospect_address_id
317 ,l_lapv_rec.cust_acct_id
318 ,l_lapv_rec.industry_class
319 ,l_lapv_rec.industry_code
320 ,l_lapv_rec.currency_code
321 ,l_lapv_rec.currency_conversion_type
322 ,l_lapv_rec.currency_conversion_rate
323 ,l_lapv_rec.currency_conversion_date
324 ,l_lapv_rec.leaseapp_template_id
325 ,l_lapv_rec.parent_leaseapp_id
326 ,l_lapv_rec.credit_line_id
327 ,l_lapv_rec.program_agreement_id
328 ,l_lapv_rec.master_lease_id
329 ,l_lapv_rec.sales_rep_id
330 ,l_lapv_rec.sales_territory_id
331 ,l_lapv_rec.originating_vendor_id
332 ,l_lapv_rec.lease_opportunity_id
333 ,l_lapv_rec.short_description
334 ,l_lapv_rec.comments
335 ,l_lapv_rec.cr_exp_days --VARANGAN for bug#4747179
336 ,l_lapv_rec.action
337 ,l_lapv_rec.orig_status
338 FROM okl_lease_applications_v
339 WHERE id = p_id;
340
341 x_return_status := G_RET_STS_SUCCESS;
342 RETURN l_lapv_rec;
343
344 EXCEPTION
345
346 WHEN OTHERS THEN
347
348 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
349 p_msg_name => G_DB_ERROR,
350 p_token1 => G_PROG_NAME_TOKEN,
351 p_token1_value => l_prog_name,
352 p_token2 => G_SQLCODE_TOKEN,
353 p_token2_value => sqlcode,
354 p_token3 => G_SQLERRM_TOKEN,
355 p_token3_value => sqlerrm);
356
357 x_return_status := G_RET_STS_UNEXP_ERROR;
358
359 END get_rec;
360
361 ------------------------
362 -- PROCEDURE validate_id
363 ------------------------
364 PROCEDURE validate_id (x_return_status OUT NOCOPY VARCHAR2, p_id IN NUMBER) IS
365 BEGIN
366 IF (p_id = OKL_API.G_MISS_NUM OR
367 p_id IS NULL)
368 THEN
369 OKL_API.set_message(p_app_name => G_APP_NAME,
370 p_msg_name => G_COL_ERROR,
371 p_token1 => G_COL_NAME_TOKEN,
372 p_token1_value => 'id',
373 p_token2 => G_PKG_NAME_TOKEN,
374 p_token2_value => G_PKG_NAME);
375 RAISE OKL_API.G_EXCEPTION_ERROR;
376 END IF;
377 x_return_status := G_RET_STS_SUCCESS;
378 END validate_id;
379
380 -------------------------------------------
381 -- PROCEDURE validate_object_version_number
382 -------------------------------------------
383 PROCEDURE validate_object_version_number (x_return_status OUT NOCOPY VARCHAR2, p_object_version_number IN NUMBER) IS
384 BEGIN
385 IF (p_object_version_number = OKL_API.G_MISS_NUM OR
386 p_object_version_number IS NULL)
387 THEN
388 OKL_API.set_message(p_app_name => G_APP_NAME,
389 p_msg_name => G_COL_ERROR,
390 p_token1 => G_COL_NAME_TOKEN,
391 p_token1_value => 'object_version_number',
392 p_token2 => G_PKG_NAME_TOKEN,
393 p_token2_value => G_PKG_NAME);
394 RAISE OKL_API.G_EXCEPTION_ERROR;
395 END IF;
396 x_return_status := G_RET_STS_SUCCESS;
397 END validate_object_version_number;
398
399 --------------------------------------
400 -- PROCEDURE validate_reference_number
401 --------------------------------------
402 PROCEDURE validate_reference_number (x_return_status OUT NOCOPY VARCHAR2, p_reference_number IN VARCHAR2) IS
403 BEGIN
404 IF (p_reference_number = OKL_API.G_MISS_CHAR OR
405 p_reference_number IS NULL)
406 THEN
407 OKL_API.set_message(p_app_name => G_APP_NAME,
408 p_msg_name => G_COL_ERROR,
409 p_token1 => G_COL_NAME_TOKEN,
410 p_token1_value => 'reference_number',
411 p_token2 => G_PKG_NAME_TOKEN,
412 p_token2_value => G_PKG_NAME);
413 RAISE OKL_API.G_EXCEPTION_ERROR;
414 END IF;
415
416 x_return_status := G_RET_STS_SUCCESS;
417 END validate_reference_number;
418
419 -----------------------------------------
420 -- PROCEDURE validate_application_status
421 -----------------------------------------
422 PROCEDURE validate_application_status (x_return_status OUT NOCOPY VARCHAR2, p_application_status IN VARCHAR2) IS
423 BEGIN
424 IF (p_application_status = OKL_API.G_MISS_CHAR OR
425 p_application_status IS NULL)
426 THEN
427 OKL_API.set_message(p_app_name => G_APP_NAME,
428 p_msg_name => G_COL_ERROR,
429 p_token1 => G_COL_NAME_TOKEN,
430 p_token1_value => 'application_status',
431 p_token2 => G_PKG_NAME_TOKEN,
432 p_token2_value => G_PKG_NAME);
433 RAISE OKL_API.G_EXCEPTION_ERROR;
434 END IF;
435
436 x_return_status := G_RET_STS_SUCCESS;
437 END validate_application_status;
438
439 -----------------------------------------
440 -- PROCEDURE validate_valid_from
441 -----------------------------------------
442 PROCEDURE validate_valid_from (x_return_status OUT NOCOPY VARCHAR2, p_valid_from IN DATE) IS
443 BEGIN
444 IF (p_valid_from = OKL_API.G_MISS_DATE OR
445 p_valid_from IS NULL)
446 THEN
447 OKL_API.set_message(p_app_name => G_APP_NAME,
448 p_msg_name => G_COL_ERROR,
449 p_token1 => G_COL_NAME_TOKEN,
450 p_token1_value => 'valid_from',
451 p_token2 => G_PKG_NAME_TOKEN,
452 p_token2_value => G_PKG_NAME);
453 RAISE OKL_API.G_EXCEPTION_ERROR;
454 END IF;
455 x_return_status := G_RET_STS_SUCCESS;
456 END validate_valid_from;
457
458 ----------------------------
459 -- PROCEDURE validate_org_id
460 ----------------------------
461 PROCEDURE validate_org_id (x_return_status OUT NOCOPY VARCHAR2, p_org_id IN NUMBER) IS
462 BEGIN
463 IF (p_org_id = OKL_API.G_MISS_NUM OR
464 p_org_id IS NULL)
465 THEN
466 OKL_API.set_message(p_app_name => G_APP_NAME,
467 p_msg_name => G_COL_ERROR,
468 p_token1 => G_COL_NAME_TOKEN,
469 p_token1_value => 'org_id',
470 p_token2 => G_PKG_NAME_TOKEN,
471 p_token2_value => G_PKG_NAME);
472 RAISE OKL_API.G_EXCEPTION_ERROR;
473 END IF;
474 x_return_status := G_RET_STS_SUCCESS;
475 END validate_org_id;
476
477 --------------------------------
478 -- PROCEDURE validate_inv_org_id
479 --------------------------------
480 PROCEDURE validate_inv_org_id (x_return_status OUT NOCOPY VARCHAR2, p_inv_org_id IN NUMBER) IS
481 BEGIN
482 IF (p_inv_org_id = OKL_API.G_MISS_NUM OR
483 p_inv_org_id IS NULL)
484 THEN
485 OKL_API.set_message(p_app_name => G_APP_NAME,
486 p_msg_name => G_COL_ERROR,
487 p_token1 => G_COL_NAME_TOKEN,
488 p_token1_value => 'inv_org_id',
489 p_token2 => G_PKG_NAME_TOKEN,
490 p_token2_value => G_PKG_NAME);
491 RAISE OKL_API.G_EXCEPTION_ERROR;
492 END IF;
493 x_return_status := G_RET_STS_SUCCESS;
494 END validate_inv_org_id;
495
496 ---------------------------------
497 -- PROCEDURE validate_prospect_id
498 ---------------------------------
499 PROCEDURE validate_prospect_id (x_return_status OUT NOCOPY VARCHAR2, p_prospect_id IN NUMBER) IS
500 BEGIN
501 IF (p_prospect_id = OKL_API.G_MISS_NUM OR
502 p_prospect_id IS NULL)
503 THEN
504 OKL_API.set_message(p_app_name => G_APP_NAME,
505 p_msg_name => G_COL_ERROR,
506 p_token1 => G_COL_NAME_TOKEN,
507 p_token1_value => 'prospect_id',
508 p_token2 => G_PKG_NAME_TOKEN,
509 p_token2_value => G_PKG_NAME);
510 RAISE OKL_API.G_EXCEPTION_ERROR;
511 END IF;
512 x_return_status := G_RET_STS_SUCCESS;
513 END validate_prospect_id;
514
515 -----------------------------------------
516 -- PROCEDURE validate_prospect_address_id
517 -----------------------------------------
518 PROCEDURE validate_prospect_address_id (x_return_status OUT NOCOPY VARCHAR2, p_prospect_address_id IN NUMBER) IS
519 BEGIN
520 IF (p_prospect_address_id = OKL_API.G_MISS_NUM OR
521 p_prospect_address_id IS NULL)
522 THEN
523 OKL_API.set_message(p_app_name => G_APP_NAME,
524 p_msg_name => G_COL_ERROR,
525 p_token1 => G_COL_NAME_TOKEN,
526 p_token1_value => 'prospect_address_id',
527 p_token2 => G_PKG_NAME_TOKEN,
528 p_token2_value => G_PKG_NAME);
529 RAISE OKL_API.G_EXCEPTION_ERROR;
530 END IF;
531 x_return_status := G_RET_STS_SUCCESS;
532 END validate_prospect_address_id;
533
534 -----------------------------------------
535 -- PROCEDURE validate_currency_code
536 -----------------------------------------
537 PROCEDURE validate_currency_code (x_return_status OUT NOCOPY VARCHAR2, p_currency_code IN VARCHAR2) IS
538 BEGIN
539 IF (p_currency_code = OKL_API.G_MISS_CHAR OR
540 p_currency_code IS NULL)
541 THEN
542 OKL_API.set_message(p_app_name => G_APP_NAME,
543 p_msg_name => G_COL_ERROR,
544 p_token1 => G_COL_NAME_TOKEN,
545 p_token1_value => 'currency_code',
546 p_token2 => G_PKG_NAME_TOKEN,
547 p_token2_value => G_PKG_NAME);
548 RAISE OKL_API.G_EXCEPTION_ERROR;
549 END IF;
550 x_return_status := G_RET_STS_SUCCESS;
551 END validate_currency_code;
552
553 -----------------------------------------
554 -- PROCEDURE validate_leaseapp_template_id
555 -----------------------------------------
556 PROCEDURE validate_leaseapp_template_id (x_return_status OUT NOCOPY VARCHAR2, p_leaseapp_template_id IN NUMBER) IS
557 BEGIN
558 IF (p_leaseapp_template_id = OKL_API.G_MISS_NUM OR
559 p_leaseapp_template_id IS NULL)
560 THEN
561 OKL_API.set_message(p_app_name => G_APP_NAME,
562 p_msg_name => G_COL_ERROR,
563 p_token1 => G_COL_NAME_TOKEN,
564 p_token1_value => 'leaseapp_template_id',
565 p_token2 => G_PKG_NAME_TOKEN,
566 p_token2_value => G_PKG_NAME);
567 RAISE OKL_API.G_EXCEPTION_ERROR;
568 END IF;
569 x_return_status := G_RET_STS_SUCCESS;
570 END validate_leaseapp_template_id;
571
572 -------------------------------
573 -- FUNCTION validate_attributes
574 -------------------------------
575 FUNCTION validate_attributes (p_lapv_rec IN lapv_rec_type) RETURN VARCHAR2 IS
576
577 l_return_status VARCHAR2(1);
578
579 BEGIN
580
581 validate_id (l_return_status, p_lapv_rec.id);
582 validate_object_version_number (l_return_status, p_lapv_rec.object_version_number);
583 validate_reference_number (l_return_status, p_lapv_rec.reference_number);
584 validate_application_status (l_return_status, p_lapv_rec.application_status);
585 validate_valid_from (l_return_status, p_lapv_rec.valid_from);
586 validate_org_id (l_return_status, p_lapv_rec.org_id);
587 validate_inv_org_id (l_return_status, p_lapv_rec.inv_org_id);
588 validate_prospect_id (l_return_status, p_lapv_rec.prospect_id);
589 validate_prospect_address_id (l_return_status, p_lapv_rec.prospect_address_id);
590 validate_currency_code (l_return_status, p_lapv_rec.currency_code);
591 validate_leaseapp_template_id (l_return_status, p_lapv_rec.leaseapp_template_id);
592
593 RETURN l_return_status;
594
595 END validate_attributes;
596
597 ----------------------------
598 -- PROCEDURE validate_record
599 ----------------------------
600 FUNCTION validate_record (p_lapv_rec IN lapv_rec_type) RETURN VARCHAR2 IS
601 l_return_status VARCHAR2(1);
602 --Cursor to check uniqueness of Lease Application Number
603 CURSOR chk_lse_app_num_csr(cp_lse_app_num VARCHAR2
604 ,cp_lse_app_id NUMBER) IS
605 SELECT 'x'
606 FROM OKL_LEASE_APPLICATIONS_B
607 WHERE UPPER(REFERENCE_NUMBER) = UPPER(cp_lse_app_num)
608 AND id <> cp_lse_app_id;
609
610 l_dummy VARCHAR2(1);
611 BEGIN
612 l_return_status := G_RET_STS_SUCCESS;
613 --If Valid To is not null and is less than or equal to Valid From then error
614 IF(NVL(p_lapv_rec.valid_to, TO_DATE('31-12-9999', 'dd-mm-yyyy')) <= p_lapv_rec.valid_from)
615 THEN
616 OKL_API.SET_MESSAGE(
617 p_app_name => G_APP_NAME,
618 p_msg_name => 'OKL_GREATER_THAN',
619 p_token1 => 'COL_NAME1',
620 p_token1_value => 'valid_to',
621 p_token2 => 'COL_NAME2',
622 p_token2_value => 'valid_from');
623 l_return_status := G_RET_STS_ERROR;
624 END IF;
625 -- End Valid To validation
626
627 --check uniqueness of Lease Application Number
628 IF(l_return_status = G_RET_STS_SUCCESS)
629 THEN
630 OPEN chk_lse_app_num_csr(p_lapv_rec.reference_number
631 ,p_lapv_rec.id);
632 FETCH chk_lse_app_num_csr INTO l_dummy;
633 IF chk_lse_app_num_csr%FOUND
634 THEN
635 l_return_status := G_RET_STS_ERROR;
636 OKL_API.SET_MESSAGE(
637 p_app_name => G_APP_NAME,
638 p_msg_name => 'OKL_SO_LSE_APP_NOT_UNIQ',
639 p_token1 => 'TEXT',
640 p_token1_value => p_lapv_rec.reference_number);
641 END IF;
642 CLOSE chk_lse_app_num_csr;
643 END IF;--End Lease Application Number uniquenes check
644
645 RETURN l_return_status;
646 END validate_record;
647
648 -----------------------------
649 -- PROECDURE migrate (V -> B)
650 -----------------------------
651 PROCEDURE migrate (p_from IN lapv_rec_type, p_to IN OUT NOCOPY lap_rec_type) IS
652
653 BEGIN
654
655 p_to.id := p_from.id;
656 p_to.object_version_number := p_from.object_version_number;
657 p_to.attribute_category := p_from.attribute_category;
658 p_to.attribute1 := p_from.attribute1;
659 p_to.attribute2 := p_from.attribute2;
660 p_to.attribute3 := p_from.attribute3;
661 p_to.attribute4 := p_from.attribute4;
662 p_to.attribute5 := p_from.attribute5;
663 p_to.attribute6 := p_from.attribute6;
664 p_to.attribute7 := p_from.attribute7;
665 p_to.attribute8 := p_from.attribute8;
666 p_to.attribute9 := p_from.attribute9;
667 p_to.attribute10 := p_from.attribute10;
668 p_to.attribute11 := p_from.attribute11;
669 p_to.attribute12 := p_from.attribute12;
670 p_to.attribute13 := p_from.attribute13;
671 p_to.attribute14 := p_from.attribute14;
672 p_to.attribute15 := p_from.attribute15;
673 p_to.reference_number := p_from.reference_number;
674 p_to.application_status := p_from.application_status;
675 p_to.valid_from := p_from.valid_from;
676 p_to.valid_to := p_from.valid_to;
677 p_to.org_id := p_from.org_id;
678 p_to.inv_org_id := p_from.inv_org_id;
679 p_to.prospect_id := p_from.prospect_id;
680 p_to.prospect_address_id := p_from.prospect_address_id;
681 p_to.cust_acct_id := p_from.cust_acct_id;
682 p_to.industry_class := p_from.industry_class;
683 p_to.industry_code := p_from.industry_code;
684 p_to.currency_code := p_from.currency_code;
685 p_to.currency_conversion_type := p_from.currency_conversion_type;
686 p_to.currency_conversion_rate := p_from.currency_conversion_rate;
687 p_to.currency_conversion_date := p_from.currency_conversion_date;
688 p_to.leaseapp_template_id := p_from.leaseapp_template_id;
689 p_to.parent_leaseapp_id := p_from.parent_leaseapp_id;
690 p_to.credit_line_id := p_from.credit_line_id;
691 p_to.program_agreement_id := p_from.program_agreement_id;
692 p_to.master_lease_id := p_from.master_lease_id;
693 p_to.sales_rep_id := p_from.sales_rep_id;
694 p_to.sales_territory_id := p_from.sales_territory_id;
695 p_to.originating_vendor_id := p_from.originating_vendor_id;
696 p_to.lease_opportunity_id := p_from.lease_opportunity_id;
697 p_to.cr_exp_days := p_from.cr_exp_days; --VARANGAN for bug#4747179
698 p_to.action := p_from.action;
699 p_to.orig_status := p_from.orig_status;
700 END migrate;
701
702 -----------------------------
703 -- PROCEDURE migrate (V -> TL)
704 -----------------------------
705 PROCEDURE migrate (p_from IN lapv_rec_type, p_to IN OUT NOCOPY laptl_rec_type) IS
706 BEGIN
707 p_to.id := p_from.id;
708 p_to.short_description := p_from.short_description;
709 p_to.comments := p_from.comments;
710 END migrate;
711
712 ---------------------------
713 -- PROCEDURE insert_row (B)
714 ---------------------------
715 PROCEDURE insert_row (x_return_status OUT NOCOPY VARCHAR2, p_lap_rec IN lap_rec_type) IS
716
717 l_prog_name VARCHAR2(61);
718
719 BEGIN
720
721 l_prog_name := G_PKG_NAME||'.insert_row (B)';
722
723 INSERT INTO okl_lease_applications_b (
724 id
725 ,object_version_number
726 ,attribute_category
727 ,attribute1
728 ,attribute2
729 ,attribute3
730 ,attribute4
731 ,attribute5
732 ,attribute6
733 ,attribute7
734 ,attribute8
735 ,attribute9
736 ,attribute10
737 ,attribute11
738 ,attribute12
739 ,attribute13
740 ,attribute14
741 ,attribute15
742 ,created_by
743 ,creation_date
744 ,last_updated_by
745 ,last_update_date
746 ,last_update_login
747 ,reference_number
748 ,application_status
749 ,valid_from
750 ,valid_to
751 ,org_id
752 ,inv_org_id
753 ,prospect_id
754 ,prospect_address_id
755 ,cust_acct_id
756 ,industry_class
757 ,industry_code
758 ,currency_code
759 ,currency_conversion_type
760 ,currency_conversion_rate
761 ,currency_conversion_date
762 ,leaseapp_template_id
763 ,parent_leaseapp_id
764 ,credit_line_id
765 ,program_agreement_id
766 ,master_lease_id
767 ,sales_rep_id
768 ,sales_territory_id
769 ,originating_vendor_id
770 ,lease_opportunity_id
771 ,cr_exp_days --VARANGAN for bug#4747179
772 ,action
773 ,orig_status
774 )
775 VALUES
776 (
777 p_lap_rec.id
778 ,p_lap_rec.object_version_number
779 ,p_lap_rec.attribute_category
780 ,p_lap_rec.attribute1
781 ,p_lap_rec.attribute2
782 ,p_lap_rec.attribute3
783 ,p_lap_rec.attribute4
784 ,p_lap_rec.attribute5
785 ,p_lap_rec.attribute6
786 ,p_lap_rec.attribute7
787 ,p_lap_rec.attribute8
788 ,p_lap_rec.attribute9
789 ,p_lap_rec.attribute10
790 ,p_lap_rec.attribute11
791 ,p_lap_rec.attribute12
792 ,p_lap_rec.attribute13
793 ,p_lap_rec.attribute14
794 ,p_lap_rec.attribute15
795 ,G_USER_ID
796 ,SYSDATE
797 ,G_USER_ID
798 ,SYSDATE
799 ,G_LOGIN_ID
800 ,p_lap_rec.reference_number
801 ,p_lap_rec.application_status
802 ,p_lap_rec.valid_from
803 ,p_lap_rec.valid_to
804 ,p_lap_rec.org_id
805 ,p_lap_rec.inv_org_id
806 ,p_lap_rec.prospect_id
807 ,p_lap_rec.prospect_address_id
808 ,p_lap_rec.cust_acct_id
809 ,p_lap_rec.industry_class
810 ,p_lap_rec.industry_code
811 ,p_lap_rec.currency_code
812 ,p_lap_rec.currency_conversion_type
813 ,p_lap_rec.currency_conversion_rate
814 ,p_lap_rec.currency_conversion_date
815 ,p_lap_rec.leaseapp_template_id
816 ,p_lap_rec.parent_leaseapp_id
817 ,p_lap_rec.credit_line_id
818 ,p_lap_rec.program_agreement_id
819 ,p_lap_rec.master_lease_id
820 ,p_lap_rec.sales_rep_id
821 ,p_lap_rec.sales_territory_id
822 ,p_lap_rec.originating_vendor_id
823 ,p_lap_rec.lease_opportunity_id
824 ,p_lap_rec.cr_exp_days --VARANGAN for bug#4747179
825 ,p_lap_rec.action
826 ,p_lap_rec.orig_status
827 );
828
829 x_return_status := G_RET_STS_SUCCESS;
830
831 EXCEPTION
832
833 WHEN OTHERS THEN
834
835 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
836 p_msg_name => G_DB_ERROR,
837 p_token1 => G_PROG_NAME_TOKEN,
838 p_token1_value => l_prog_name,
839 p_token2 => G_SQLCODE_TOKEN,
840 p_token2_value => sqlcode,
841 p_token3 => G_SQLERRM_TOKEN,
842 p_token3_value => sqlerrm);
843
844 x_return_status := G_RET_STS_UNEXP_ERROR;
845
846 END insert_row;
847
848 ----------------------------
849 -- PROCEDURE insert_row (TL)
850 ----------------------------
851 PROCEDURE insert_row (x_return_status OUT NOCOPY VARCHAR2, p_laptl_rec IN laptl_rec_type) IS
852
853 CURSOR get_languages IS
854 SELECT language_code
855 FROM FND_LANGUAGES
856 WHERE INSTALLED_FLAG IN ('I', 'B');
857
858 l_sfwt_flag VARCHAR2(1);
859
860 l_prog_name VARCHAR2(61);
861
862 BEGIN
863
864 l_prog_name := G_PKG_NAME||'.insert_row (TL)';
865
866 FOR l_lang_rec IN get_languages LOOP
867
868 IF l_lang_rec.language_code = USERENV('LANG') THEN
869 l_sfwt_flag := 'N';
870 ELSE
871 l_sfwt_flag := 'Y';
872 END IF;
873
874 INSERT INTO OKL_LEASE_APPLICATIONS_TL (
875 id
876 ,language
877 ,source_lang
878 ,sfwt_flag
879 ,created_by
880 ,creation_date
881 ,last_updated_by
882 ,last_update_date
883 ,last_update_login
884 ,short_description
885 ,comments)
886 VALUES (
887 p_laptl_rec.id
888 ,l_lang_rec.language_code
889 ,USERENV('LANG')
890 ,l_sfwt_flag
891 ,G_USER_ID
892 ,SYSDATE
893 ,G_USER_ID
894 ,SYSDATE
895 ,G_LOGIN_ID
896 ,p_laptl_rec.short_description
897 ,p_laptl_rec.comments);
898
899 END LOOP;
900
901 x_return_status := G_RET_STS_SUCCESS;
902
903 EXCEPTION
904
905 WHEN OTHERS THEN
906
907 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
908 p_msg_name => G_DB_ERROR,
909 p_token1 => G_PROG_NAME_TOKEN,
910 p_token1_value => l_prog_name,
911 p_token2 => G_SQLCODE_TOKEN,
912 p_token2_value => sqlcode,
913 p_token3 => G_SQLERRM_TOKEN,
914 p_token3_value => sqlerrm);
915
916 x_return_status := G_RET_STS_UNEXP_ERROR;
917
918 END insert_row;
919
920 ---------------------------
921 -- PROCEDURE insert_row (V)
922 ---------------------------
923 PROCEDURE insert_row (
924 x_return_status OUT NOCOPY VARCHAR2,
925 p_lapv_rec IN lapv_rec_type,
926 x_lapv_rec OUT NOCOPY lapv_rec_type) IS
927
928 l_return_status VARCHAR2(1);
929
930 l_lapv_rec lapv_rec_type;
931 l_lap_rec lap_rec_type;
932 l_laptl_rec laptl_rec_type;
933
934 l_prog_name VARCHAR2(61);
935
936 BEGIN
937
938 l_prog_name := G_PKG_NAME||'.insert_row (V)';
939
940 l_lapv_rec := null_out_defaults (p_lapv_rec);
941
942 SELECT okl_lap_seq.nextval INTO l_lapv_rec.ID FROM DUAL;
943
944 l_lapv_rec.OBJECT_VERSION_NUMBER := 1;
945
946 l_return_status := validate_attributes(l_lapv_rec);
947
948 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
949 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
950 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
951 RAISE OKL_API.G_EXCEPTION_ERROR;
952 END IF;
953
954 --Bug 7022258-Added by kkorrapo
955 IF (okl_util.validate_seq_num('OKL_LAP_REF_SEQ','OKL_LEASE_APPLICATIONS_B','REFERENCE_NUMBER',l_lapv_rec.reference_number) = 'N') THEN
956 RAISE okl_api.g_exception_error;
957 END IF;
958 --Bug 7022258--Addition end
959
960 l_return_status := validate_record(l_lapv_rec);
961
962 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
963 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
964 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
965 RAISE OKL_API.G_EXCEPTION_ERROR;
966 END IF;
967
968 migrate (l_lapv_rec, l_lap_rec);
969 migrate (l_lapv_rec, l_laptl_rec);
970
971 insert_row (x_return_status => l_return_status, p_lap_rec => l_lap_rec);
972
973 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
974 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
975 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
976 RAISE OKL_API.G_EXCEPTION_ERROR;
977 END IF;
978
979 insert_row (x_return_status => l_return_status, p_laptl_rec => l_laptl_rec);
980
981 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
982 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
983 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
984 RAISE OKL_API.G_EXCEPTION_ERROR;
985 END IF;
986
987 x_lapv_rec := l_lapv_rec;
988 x_return_status := l_return_status;
989
990 EXCEPTION
991
992 WHEN OKL_API.G_EXCEPTION_ERROR THEN
993
994 x_return_status := G_RET_STS_ERROR;
995
996 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
997
998 x_return_status := G_RET_STS_UNEXP_ERROR;
999
1000 WHEN OTHERS THEN
1001
1002 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1003 p_msg_name => G_DB_ERROR,
1004 p_token1 => G_PROG_NAME_TOKEN,
1005 p_token1_value => l_prog_name,
1006 p_token2 => G_SQLCODE_TOKEN,
1007 p_token2_value => sqlcode,
1008 p_token3 => G_SQLERRM_TOKEN,
1009 p_token3_value => sqlerrm);
1010
1011 x_return_status := G_RET_STS_UNEXP_ERROR;
1012
1013 END insert_row;
1014
1015
1016 -----------------------------
1017 -- PROCEDURE insert_row (REC)
1018 -----------------------------
1019 PROCEDURE insert_row(
1020 p_api_version IN NUMBER,
1021 p_init_msg_list IN VARCHAR2,
1022 x_return_status OUT NOCOPY VARCHAR2,
1023 x_msg_count OUT NOCOPY NUMBER,
1024 x_msg_data OUT NOCOPY VARCHAR2,
1025 p_lapv_rec IN lapv_rec_type,
1026 x_lapv_rec OUT NOCOPY lapv_rec_type) IS
1027
1028 l_return_status VARCHAR2(1);
1029
1030 l_prog_name VARCHAR2(61);
1031
1032 BEGIN
1033
1034 l_prog_name := G_PKG_NAME||'.insert_row (REC)';
1035
1036 IF p_init_msg_list = G_TRUE THEN
1037 FND_MSG_PUB.initialize;
1038 END IF;
1039
1040 insert_row (x_return_status => l_return_status,
1041 p_lapv_rec => p_lapv_rec,
1042 x_lapv_rec => x_lapv_rec);
1043
1044 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1045 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1046 ELSIF l_return_status = G_RET_STS_ERROR THEN
1047 RAISE OKL_API.G_EXCEPTION_ERROR;
1048 END IF;
1049
1050 x_return_status := l_return_status;
1051
1052 EXCEPTION
1053
1054 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1055
1056 x_return_status := G_RET_STS_ERROR;
1057
1058 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1059
1060 x_return_status := G_RET_STS_UNEXP_ERROR;
1061
1062 WHEN OTHERS THEN
1063
1064 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1065 p_msg_name => G_DB_ERROR,
1066 p_token1 => G_PROG_NAME_TOKEN,
1067 p_token1_value => l_prog_name,
1068 p_token2 => G_SQLCODE_TOKEN,
1069 p_token2_value => sqlcode,
1070 p_token3 => G_SQLERRM_TOKEN,
1071 p_token3_value => sqlerrm);
1072
1073 x_return_status := G_RET_STS_UNEXP_ERROR;
1074
1075 END insert_row;
1076
1077
1078 -----------------------------
1079 -- PROCEDURE insert_row (TBL)
1080 -----------------------------
1081 PROCEDURE insert_row(
1082 p_api_version IN NUMBER,
1083 p_init_msg_list IN VARCHAR2,
1084 x_return_status OUT NOCOPY VARCHAR2,
1085 x_msg_count OUT NOCOPY NUMBER,
1086 x_msg_data OUT NOCOPY VARCHAR2,
1087 p_lapv_tbl IN lapv_tbl_type,
1088 x_lapv_tbl OUT NOCOPY lapv_tbl_type) IS
1089
1090 l_return_status VARCHAR2(1);
1091 i BINARY_INTEGER;
1092
1093 l_prog_name VARCHAR2(61);
1094
1095 BEGIN
1096
1097 l_prog_name := G_PKG_NAME||'.insert_row (TBL)';
1098
1099 IF p_init_msg_list = G_TRUE THEN
1100 FND_MSG_PUB.initialize;
1101 END IF;
1102
1103 IF (p_lapv_tbl.COUNT > 0) THEN
1104 i := p_lapv_tbl.FIRST;
1105 LOOP
1106 IF p_lapv_tbl.EXISTS(i) THEN
1107
1108 insert_row (x_return_status => l_return_status,
1109 p_lapv_rec => p_lapv_tbl(i),
1110 x_lapv_rec => x_lapv_tbl(i));
1111
1112 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1113 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1114 ELSIF l_return_status = G_RET_STS_ERROR THEN
1115 RAISE OKL_API.G_EXCEPTION_ERROR;
1116 END IF;
1117
1118 EXIT WHEN (i = p_lapv_tbl.LAST);
1119 i := p_lapv_tbl.NEXT(i);
1120
1121 END IF;
1122
1123 END LOOP;
1124
1125 ELSE
1126
1127 l_return_status := G_RET_STS_SUCCESS;
1128
1129 END IF;
1130
1131 x_return_status := l_return_status;
1132
1133 EXCEPTION
1134
1135 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1136
1137 x_return_status := G_RET_STS_ERROR;
1138
1139 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1140
1141 x_return_status := G_RET_STS_UNEXP_ERROR;
1142
1143 WHEN OTHERS THEN
1144
1145 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1146 p_msg_name => G_DB_ERROR,
1147 p_token1 => G_PROG_NAME_TOKEN,
1148 p_token1_value => l_prog_name,
1149 p_token2 => G_SQLCODE_TOKEN,
1150 p_token2_value => sqlcode,
1151 p_token3 => G_SQLERRM_TOKEN,
1152 p_token3_value => sqlerrm);
1153
1154 x_return_status := G_RET_STS_UNEXP_ERROR;
1155
1156 END insert_row;
1157
1158
1159 ---------------------
1160 -- PROCEDURE lock_row
1161 ---------------------
1162 PROCEDURE lock_row (x_return_status OUT NOCOPY VARCHAR2, p_lap_rec IN lap_rec_type) IS
1163
1164 E_Resource_Busy EXCEPTION;
1165
1166 PRAGMA EXCEPTION_INIT (E_Resource_Busy, -00054);
1167
1168 CURSOR lock_csr IS
1169 SELECT OBJECT_VERSION_NUMBER
1170 FROM OKL_LEASE_APPLICATIONS_B
1171 WHERE ID = p_lap_rec.id
1172 AND OBJECT_VERSION_NUMBER = p_lap_rec.object_version_number
1173 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1174
1175 CURSOR lchk_csr IS
1176 SELECT OBJECT_VERSION_NUMBER
1177 FROM OKL_LEASE_APPLICATIONS_B
1178 WHERE ID = p_lap_rec.id;
1179
1180 l_object_version_number NUMBER;
1181 lc_object_version_number NUMBER;
1182
1183 l_prog_name VARCHAR2(61);
1184
1185 BEGIN
1186
1187 l_prog_name := G_PKG_NAME||'.lock_row';
1188
1189 BEGIN
1190 OPEN lock_csr;
1191 FETCH lock_csr INTO l_object_version_number;
1192 CLOSE lock_csr;
1193 EXCEPTION
1194 WHEN E_Resource_Busy THEN
1195
1196 IF (lock_csr%ISOPEN) THEN
1197 CLOSE lock_csr;
1198 END IF;
1199 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1200 p_msg_name => G_OVN_ERROR2,
1201 p_token1 => G_PROG_NAME_TOKEN,
1202 p_token1_value => l_prog_name);
1203 RAISE OKL_API.G_EXCEPTION_ERROR;
1204 END;
1205
1206 IF l_object_version_number IS NULL THEN
1207
1208 OPEN lchk_csr;
1209 FETCH lchk_csr INTO lc_object_version_number;
1210 CLOSE lchk_csr;
1211
1212 IF lc_object_version_number IS NULL THEN
1213
1214 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1215 p_msg_name => G_OVN_ERROR3,
1216 p_token1 => G_PROG_NAME_TOKEN,
1217 p_token1_value => l_prog_name);
1218
1219 ELSIF lc_object_version_number <> p_lap_rec.object_version_number THEN
1220
1221 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1222 p_msg_name => G_OVN_ERROR,
1223 p_token1 => G_PROG_NAME_TOKEN,
1224 p_token1_value => l_prog_name);
1225
1226 END IF;
1227
1228 RAISE OKL_API.G_EXCEPTION_ERROR;
1229
1230 END IF;
1231
1232 x_return_status := G_RET_STS_SUCCESS;
1233
1234 EXCEPTION
1235
1236 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1237
1238 x_return_status := G_RET_STS_ERROR;
1239
1240 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1241
1242 x_return_status := G_RET_STS_UNEXP_ERROR;
1243
1244 WHEN OTHERS THEN
1245
1246 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1247 p_msg_name => G_DB_ERROR,
1248 p_token1 => G_PROG_NAME_TOKEN,
1249 p_token1_value => l_prog_name,
1250 p_token2 => G_SQLCODE_TOKEN,
1251 p_token2_value => sqlcode,
1252 p_token3 => G_SQLERRM_TOKEN,
1253 p_token3_value => sqlerrm);
1254
1255 x_return_status := G_RET_STS_UNEXP_ERROR;
1256
1257 END lock_row;
1258
1259
1260 ---------------------------
1261 -- PROCEDURE update_row (B)
1262 ---------------------------
1263 PROCEDURE update_row(x_return_status OUT NOCOPY VARCHAR2, p_lap_rec IN lap_rec_type) IS
1264
1265 l_return_status VARCHAR2(1);
1266
1267 l_prog_name VARCHAR2(61);
1268
1269 BEGIN
1270
1271 l_prog_name := G_PKG_NAME||'.update_row (B)';
1272
1273 lock_row (x_return_status => l_return_status, p_lap_rec => p_lap_rec);
1274
1275 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1276 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1277 ELSIF l_return_status = G_RET_STS_ERROR THEN
1278 RAISE OKL_API.G_EXCEPTION_ERROR;
1279 END IF;
1280
1281 UPDATE okl_lease_applications_b
1282 SET
1283 object_version_number = p_lap_rec.object_version_number+1
1284 ,attribute_category = p_lap_rec.attribute_category
1285 ,attribute1 = p_lap_rec.attribute1
1286 ,attribute2 = p_lap_rec.attribute2
1287 ,attribute3 = p_lap_rec.attribute3
1288 ,attribute4 = p_lap_rec.attribute4
1289 ,attribute5 = p_lap_rec.attribute5
1290 ,attribute6 = p_lap_rec.attribute6
1291 ,attribute7 = p_lap_rec.attribute7
1292 ,attribute8 = p_lap_rec.attribute8
1293 ,attribute9 = p_lap_rec.attribute9
1294 ,attribute10 = p_lap_rec.attribute10
1295 ,attribute11 = p_lap_rec.attribute11
1296 ,attribute12 = p_lap_rec.attribute12
1297 ,attribute13 = p_lap_rec.attribute13
1298 ,attribute14 = p_lap_rec.attribute14
1299 ,attribute15 = p_lap_rec.attribute15
1300 ,reference_number = p_lap_rec.reference_number
1301 ,application_status = p_lap_rec.application_status
1302 ,valid_from = p_lap_rec.valid_from
1303 ,valid_to = p_lap_rec.valid_to
1304 ,org_id = p_lap_rec.org_id
1305 ,inv_org_id = p_lap_rec.inv_org_id
1306 ,prospect_id = p_lap_rec.prospect_id
1307 ,prospect_address_id = p_lap_rec.prospect_address_id
1308 ,cust_acct_id = p_lap_rec.cust_acct_id
1309 ,industry_class = p_lap_rec.industry_class
1310 ,industry_code = p_lap_rec.industry_code
1311 ,currency_code = p_lap_rec.currency_code
1312 ,currency_conversion_type = p_lap_rec.currency_conversion_type
1313 ,currency_conversion_rate = p_lap_rec.currency_conversion_rate
1314 ,currency_conversion_date = p_lap_rec.currency_conversion_date
1315 ,leaseapp_template_id = p_lap_rec.leaseapp_template_id
1316 ,parent_leaseapp_id = p_lap_rec.parent_leaseapp_id
1317 ,credit_line_id = p_lap_rec.credit_line_id
1318 ,program_agreement_id = p_lap_rec.program_agreement_id
1319 ,master_lease_id = p_lap_rec.master_lease_id
1320 ,sales_rep_id = p_lap_rec.sales_rep_id
1321 ,sales_territory_id = p_lap_rec.sales_territory_id
1322 ,originating_vendor_id = p_lap_rec.originating_vendor_id
1323 ,lease_opportunity_id = p_lap_rec.lease_opportunity_id
1324 ,cr_exp_days = p_lap_rec.cr_exp_days --VARANGAN for bug#4747179
1325 ,action = p_lap_rec.action
1326 ,orig_status = p_lap_rec.orig_status
1327 WHERE id = p_lap_rec.id;
1328
1329 x_return_status := l_return_status;
1330
1331 EXCEPTION
1332
1333 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1334 x_return_status := G_RET_STS_ERROR;
1335
1336 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1337 x_return_status := G_RET_STS_UNEXP_ERROR;
1338
1339 WHEN OTHERS THEN
1340 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1341 p_msg_name => G_DB_ERROR,
1342 p_token1 => G_PROG_NAME_TOKEN,
1343 p_token1_value => l_prog_name,
1344 p_token2 => G_SQLCODE_TOKEN,
1345 p_token2_value => sqlcode,
1346 p_token3 => G_SQLERRM_TOKEN,
1347 p_token3_value => sqlerrm);
1348
1349 x_return_status := G_RET_STS_UNEXP_ERROR;
1350
1351 END update_row;
1352
1353 ----------------------------
1354 -- PROCEDURE update_row (TL)
1355 ----------------------------
1356 PROCEDURE update_row(x_return_status OUT NOCOPY VARCHAR2, p_laptl_rec IN laptl_rec_type) IS
1357
1358 l_prog_name VARCHAR2(61);
1359
1360 BEGIN
1361
1362 l_prog_name := G_PKG_NAME||'.update_row (TL)';
1363
1364 UPDATE OKL_LEASE_APPLICATIONS_TL
1365 SET
1366 source_lang = USERENV('LANG')
1367 ,sfwt_flag = 'Y'
1368 ,last_updated_by = G_USER_ID
1369 ,last_update_date = SYSDATE
1370 ,last_update_login = G_LOGIN_ID
1371 ,short_description = p_laptl_rec.short_description
1372 ,comments = p_laptl_rec.comments
1373 WHERE ID = p_laptl_rec.id;
1374
1375 UPDATE OKL_LEASE_APPLICATIONS_TL
1376 SET SFWT_FLAG = 'N'
1377 WHERE ID = p_laptl_rec.id
1378 AND SOURCE_LANG = LANGUAGE;
1379
1380 x_return_status := G_RET_STS_SUCCESS;
1381
1382 EXCEPTION
1383
1384 WHEN OTHERS THEN
1385
1386 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1387 p_msg_name => G_DB_ERROR,
1388 p_token1 => G_PROG_NAME_TOKEN,
1389 p_token1_value => l_prog_name,
1390 p_token2 => G_SQLCODE_TOKEN,
1391 p_token2_value => sqlcode,
1392 p_token3 => G_SQLERRM_TOKEN,
1393 p_token3_value => sqlerrm);
1394
1395 x_return_status := G_RET_STS_UNEXP_ERROR;
1396
1397 END update_row;
1398
1399 ---------------------------
1400 -- PROCEDURE update_row (V)
1401 ---------------------------
1402 PROCEDURE update_row (
1403 x_return_status OUT NOCOPY VARCHAR2,
1404 p_lapv_rec IN lapv_rec_type,
1405 x_lapv_rec OUT NOCOPY lapv_rec_type) IS
1406
1407 l_prog_name VARCHAR2(61);
1408
1409 l_return_status VARCHAR2(1);
1410 l_lapv_rec lapv_rec_type;
1411 l_lap_rec lap_rec_type;
1412 l_laptl_rec laptl_rec_type;
1413
1414 ----------------------
1415 -- populate_new_record
1416 ----------------------
1417 FUNCTION populate_new_record (p_lapv_rec IN lapv_rec_type,
1418 x_lapv_rec OUT NOCOPY lapv_rec_type) RETURN VARCHAR2 IS
1419
1420 l_prog_name VARCHAR2(61) := G_PKG_NAME||'.populate_new_record';
1421 l_return_status VARCHAR2(1);
1422 l_db_lapv_rec lapv_rec_type;
1423
1424 BEGIN
1425 x_lapv_rec := p_lapv_rec;
1426 l_db_lapv_rec := get_rec (p_lapv_rec.id, l_return_status);
1427
1428 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1429 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1430 ELSIF l_return_status = G_RET_STS_ERROR THEN
1431 RAISE OKL_API.G_EXCEPTION_ERROR;
1432 END IF;
1433
1434 IF x_lapv_rec.attribute_category IS NULL THEN
1435 x_lapv_rec.attribute_category := l_db_lapv_rec.attribute_category;
1436 END IF;
1437 IF x_lapv_rec.attribute1 IS NULL THEN
1438 x_lapv_rec.attribute1 := l_db_lapv_rec.attribute1;
1439 END IF;
1440 IF x_lapv_rec.attribute2 IS NULL THEN
1441 x_lapv_rec.attribute2 := l_db_lapv_rec.attribute2;
1442 END IF;
1443 IF x_lapv_rec.attribute3 IS NULL THEN
1444 x_lapv_rec.attribute3 := l_db_lapv_rec.attribute3;
1445 END IF;
1446 IF x_lapv_rec.attribute4 IS NULL THEN
1447 x_lapv_rec.attribute4 := l_db_lapv_rec.attribute4;
1448 END IF;
1449 IF x_lapv_rec.attribute5 IS NULL THEN
1450 x_lapv_rec.attribute5 := l_db_lapv_rec.attribute5;
1451 END IF;
1452 IF x_lapv_rec.attribute6 IS NULL THEN
1453 x_lapv_rec.attribute6 := l_db_lapv_rec.attribute6;
1454 END IF;
1455 IF x_lapv_rec.attribute7 IS NULL THEN
1456 x_lapv_rec.attribute7 := l_db_lapv_rec.attribute7;
1457 END IF;
1458 IF x_lapv_rec.attribute8 IS NULL THEN
1459 x_lapv_rec.attribute8 := l_db_lapv_rec.attribute8;
1460 END IF;
1461 IF x_lapv_rec.attribute9 IS NULL THEN
1462 x_lapv_rec.attribute9 := l_db_lapv_rec.attribute9;
1463 END IF;
1464 IF x_lapv_rec.attribute10 IS NULL THEN
1465 x_lapv_rec.attribute10 := l_db_lapv_rec.attribute10;
1466 END IF;
1467 IF x_lapv_rec.attribute11 IS NULL THEN
1468 x_lapv_rec.attribute11 := l_db_lapv_rec.attribute11;
1469 END IF;
1470 IF x_lapv_rec.attribute12 IS NULL THEN
1471 x_lapv_rec.attribute12 := l_db_lapv_rec.attribute12;
1472 END IF;
1473 IF x_lapv_rec.attribute13 IS NULL THEN
1474 x_lapv_rec.attribute13 := l_db_lapv_rec.attribute13;
1475 END IF;
1476 IF x_lapv_rec.attribute14 IS NULL THEN
1477 x_lapv_rec.attribute14 := l_db_lapv_rec.attribute14;
1478 END IF;
1479 IF x_lapv_rec.attribute15 IS NULL THEN
1480 x_lapv_rec.attribute15 := l_db_lapv_rec.attribute15;
1481 END IF;
1482 IF x_lapv_rec.object_version_number IS NULL THEN
1483 x_lapv_rec.object_version_number := l_db_lapv_rec.object_version_number;
1484 END IF;
1485 IF x_lapv_rec.reference_number IS NULL THEN
1486 x_lapv_rec.reference_number := l_db_lapv_rec.reference_number;
1487 END IF;
1488 IF x_lapv_rec.application_status IS NULL THEN
1489 x_lapv_rec.application_status := l_db_lapv_rec.application_status;
1490 END IF;
1491 IF x_lapv_rec.valid_from IS NULL THEN
1492 x_lapv_rec.valid_from := l_db_lapv_rec.valid_from;
1493 END IF;
1494 IF x_lapv_rec.valid_to IS NULL THEN
1495 x_lapv_rec.valid_to := l_db_lapv_rec.valid_to;
1496 END IF;
1497 IF x_lapv_rec.org_id IS NULL THEN
1498 x_lapv_rec.org_id := l_db_lapv_rec.org_id;
1499 END IF;
1500 IF x_lapv_rec.inv_org_id IS NULL THEN
1501 x_lapv_rec.inv_org_id := l_db_lapv_rec.inv_org_id;
1502 END IF;
1503 IF x_lapv_rec.prospect_id IS NULL THEN
1504 x_lapv_rec.prospect_id := l_db_lapv_rec.prospect_id;
1505 END IF;
1506 IF x_lapv_rec.prospect_address_id IS NULL THEN
1507 x_lapv_rec.prospect_address_id := l_db_lapv_rec.prospect_address_id;
1508 END IF;
1509 IF x_lapv_rec.cust_acct_id IS NULL THEN
1510 x_lapv_rec.cust_acct_id := l_db_lapv_rec.cust_acct_id;
1511 END IF;
1512 IF x_lapv_rec.industry_class IS NULL THEN
1513 x_lapv_rec.industry_class := l_db_lapv_rec.industry_class;
1514 END IF;
1515 IF x_lapv_rec.industry_code IS NULL THEN
1516 x_lapv_rec.industry_code := l_db_lapv_rec.industry_code;
1517 END IF;
1518 IF x_lapv_rec.currency_code IS NULL THEN
1519 x_lapv_rec.currency_code := l_db_lapv_rec.currency_code;
1520 END IF;
1521 IF x_lapv_rec.currency_conversion_type IS NULL THEN
1522 x_lapv_rec.currency_conversion_type := l_db_lapv_rec.currency_conversion_type;
1523 END IF;
1524 IF x_lapv_rec.currency_conversion_rate IS NULL THEN
1525 x_lapv_rec.currency_conversion_rate := l_db_lapv_rec.currency_conversion_rate;
1526 END IF;
1527 IF x_lapv_rec.currency_conversion_date IS NULL THEN
1528 x_lapv_rec.currency_conversion_date := l_db_lapv_rec.currency_conversion_date;
1529 END IF;
1530 IF x_lapv_rec.leaseapp_template_id IS NULL THEN
1531 x_lapv_rec.leaseapp_template_id := l_db_lapv_rec.leaseapp_template_id;
1532 END IF;
1533 IF x_lapv_rec.parent_leaseapp_id IS NULL THEN
1534 x_lapv_rec.parent_leaseapp_id := l_db_lapv_rec.parent_leaseapp_id;
1535 END IF;
1536 IF x_lapv_rec.credit_line_id IS NULL THEN
1537 x_lapv_rec.credit_line_id := l_db_lapv_rec.credit_line_id;
1538 END IF;
1539 IF x_lapv_rec.program_agreement_id IS NULL THEN
1540 x_lapv_rec.program_agreement_id := l_db_lapv_rec.program_agreement_id;
1541 END IF;
1542 IF x_lapv_rec.master_lease_id IS NULL THEN
1543 x_lapv_rec.master_lease_id := l_db_lapv_rec.master_lease_id;
1544 END IF;
1545 IF x_lapv_rec.sales_rep_id IS NULL THEN
1546 x_lapv_rec.sales_rep_id := l_db_lapv_rec.sales_rep_id;
1547 END IF;
1548 IF x_lapv_rec.sales_territory_id IS NULL THEN
1549 x_lapv_rec.sales_territory_id := l_db_lapv_rec.sales_territory_id;
1550 END IF;
1551 IF x_lapv_rec.originating_vendor_id IS NULL THEN
1552 x_lapv_rec.originating_vendor_id := l_db_lapv_rec.originating_vendor_id;
1553 END IF;
1554 IF x_lapv_rec.lease_opportunity_id IS NULL THEN
1555 x_lapv_rec.lease_opportunity_id := l_db_lapv_rec.lease_opportunity_id;
1556 END IF;
1557 IF x_lapv_rec.short_description IS NULL THEN
1558 x_lapv_rec.short_description := l_db_lapv_rec.short_description;
1559 END IF;
1560 IF x_lapv_rec.comments IS NULL THEN
1561 x_lapv_rec.comments := l_db_lapv_rec.comments;
1562 END IF;
1563 --VARANGAN for bug#4747179
1564 IF x_lapv_rec.cr_exp_days IS NULL THEN
1565 x_lapv_rec.cr_exp_days := l_db_lapv_rec.cr_exp_days;
1566 END IF;
1567 --VARANGAN for bug#4747179
1568 --Bug 4872271 PAGARG start
1569 IF x_lapv_rec.action IS NULL THEN
1570 x_lapv_rec.action := l_db_lapv_rec.action;
1571 END IF;
1572 IF x_lapv_rec.orig_status IS NULL THEN
1573 x_lapv_rec.orig_status := l_db_lapv_rec.orig_status;
1574 END IF;
1575 --Bug 4872271 PAGARG end
1576 RETURN l_return_status;
1577
1578 EXCEPTION
1579 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1580 x_return_status := G_RET_STS_ERROR;
1581
1582 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1583
1584 x_return_status := G_RET_STS_UNEXP_ERROR;
1585
1586 WHEN OTHERS THEN
1587
1588 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1589 p_msg_name => G_DB_ERROR,
1590 p_token1 => G_PROG_NAME_TOKEN,
1591 p_token1_value => l_prog_name,
1592 p_token2 => G_SQLCODE_TOKEN,
1593 p_token2_value => sqlcode,
1594 p_token3 => G_SQLERRM_TOKEN,
1595 p_token3_value => sqlerrm);
1596
1597 x_return_status := G_RET_STS_UNEXP_ERROR;
1598
1599 END populate_new_record;
1600
1601 BEGIN
1602
1603 l_prog_name := G_PKG_NAME||'.update_row (V)';
1604
1605 l_return_status := populate_new_record (p_lapv_rec, l_lapv_rec);
1606 l_lapv_rec := null_out_defaults(l_lapv_rec);
1607
1608 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1609 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1610 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1611 RAISE OKL_API.G_EXCEPTION_ERROR;
1612 END IF;
1613
1614 l_return_status := validate_attributes (l_lapv_rec);
1615
1616 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1617 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1618 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1619 RAISE OKL_API.G_EXCEPTION_ERROR;
1620 END IF;
1621
1622 l_return_status := validate_record (l_lapv_rec);
1623
1624 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1625 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1626 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1627 RAISE OKL_API.G_EXCEPTION_ERROR;
1628 END IF;
1629
1630 migrate (l_lapv_rec, l_lap_rec);
1631 migrate (l_lapv_rec, l_laptl_rec);
1632
1633 update_row (x_return_status => l_return_status, p_lap_rec => l_lap_rec);
1634
1635 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1636 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1637 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1638 RAISE OKL_API.G_EXCEPTION_ERROR;
1639 END IF;
1640
1641 update_row (x_return_status => l_return_status, p_laptl_rec => l_laptl_rec);
1642
1643 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
1644 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1645 ELSIF (l_return_status = G_RET_STS_ERROR) THEN
1646 RAISE OKL_API.G_EXCEPTION_ERROR;
1647 END IF;
1648
1649 x_return_status := l_return_status;
1650 x_lapv_rec := l_lapv_rec;
1651
1652 EXCEPTION
1653
1654 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1655
1656 x_return_status := G_RET_STS_ERROR;
1657
1658 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1659
1660 x_return_status := G_RET_STS_UNEXP_ERROR;
1661
1662 WHEN OTHERS THEN
1663
1664 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1665 p_msg_name => G_DB_ERROR,
1666 p_token1 => G_PROG_NAME_TOKEN,
1667 p_token1_value => l_prog_name,
1668 p_token2 => G_SQLCODE_TOKEN,
1669 p_token2_value => sqlcode,
1670 p_token3 => G_SQLERRM_TOKEN,
1671 p_token3_value => sqlerrm);
1672
1673 x_return_status := G_RET_STS_UNEXP_ERROR;
1674
1675 END update_row;
1676
1677 -----------------------------
1678 -- PROCEDURE update_row (REC)
1679 -----------------------------
1680 PROCEDURE update_row(
1681 p_api_version IN NUMBER,
1682 p_init_msg_list IN VARCHAR2,
1683 x_return_status OUT NOCOPY VARCHAR2,
1684 x_msg_count OUT NOCOPY NUMBER,
1685 x_msg_data OUT NOCOPY VARCHAR2,
1686 p_lapv_rec IN lapv_rec_type,
1687 x_lapv_rec OUT NOCOPY lapv_rec_type) IS
1688
1689 l_return_status VARCHAR2(1);
1690
1691 l_prog_name VARCHAR2(61);
1692
1693 BEGIN
1694
1695 l_prog_name := G_PKG_NAME||'.update_row (REC)';
1696
1697 IF p_init_msg_list = G_TRUE THEN
1698 FND_MSG_PUB.initialize;
1699 END IF;
1700
1701 update_row (x_return_status => l_return_status,
1702 p_lapv_rec => p_lapv_rec,
1703 x_lapv_rec => x_lapv_rec);
1704
1705 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1706 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1707 ELSIF l_return_status = G_RET_STS_ERROR THEN
1708 RAISE OKL_API.G_EXCEPTION_ERROR;
1709 END IF;
1710
1711 x_return_status := l_return_status;
1712
1713 EXCEPTION
1714
1715 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1716
1717 x_return_status := G_RET_STS_ERROR;
1718
1719 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1720
1721 x_return_status := G_RET_STS_UNEXP_ERROR;
1722
1723 WHEN OTHERS THEN
1724
1725 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1726 p_msg_name => G_DB_ERROR,
1727 p_token1 => G_PROG_NAME_TOKEN,
1728 p_token1_value => l_prog_name,
1729 p_token2 => G_SQLCODE_TOKEN,
1730 p_token2_value => sqlcode,
1731 p_token3 => G_SQLERRM_TOKEN,
1732 p_token3_value => sqlerrm);
1733
1734 x_return_status := G_RET_STS_UNEXP_ERROR;
1735
1736 END update_row;
1737
1738 -----------------------------
1739 -- PROCEDURE update_row (TBL)
1740 -----------------------------
1741 PROCEDURE update_row(
1742 p_api_version IN NUMBER,
1743 p_init_msg_list IN VARCHAR2,
1744 x_return_status OUT NOCOPY VARCHAR2,
1745 x_msg_count OUT NOCOPY NUMBER,
1746 x_msg_data OUT NOCOPY VARCHAR2,
1747 p_lapv_tbl IN lapv_tbl_type,
1748 x_lapv_tbl OUT NOCOPY lapv_tbl_type) IS
1749
1750 l_return_status VARCHAR2(1);
1751 i BINARY_INTEGER;
1752 l_prog_name VARCHAR2(61);
1753
1754 BEGIN
1755
1756 l_prog_name := G_PKG_NAME||'.update_row (TBL)';
1757
1758 IF p_init_msg_list = G_TRUE THEN
1759 FND_MSG_PUB.initialize;
1760 END IF;
1761
1762 x_lapv_tbl := p_lapv_tbl;
1763
1764 IF (p_lapv_tbl.COUNT > 0) THEN
1765
1766 i := p_lapv_tbl.FIRST;
1767
1768 LOOP
1769
1770 IF p_lapv_tbl.EXISTS(i) THEN
1771 update_row (x_return_status => l_return_status,
1772 p_lapv_rec => p_lapv_tbl(i),
1773 x_lapv_rec => x_lapv_tbl(i));
1774
1775 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1776 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1777 ELSIF l_return_status = G_RET_STS_ERROR THEN
1778 RAISE OKL_API.G_EXCEPTION_ERROR;
1779 END IF;
1780
1781 EXIT WHEN (i = p_lapv_tbl.LAST);
1782 i := p_lapv_tbl.NEXT(i);
1783
1784 END IF;
1785
1786 END LOOP;
1787
1788 ELSE
1789
1790 l_return_status := G_RET_STS_SUCCESS;
1791
1792 END IF;
1793
1794 x_return_status := l_return_status;
1795
1796 EXCEPTION
1797
1798 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1799
1800 x_return_status := G_RET_STS_ERROR;
1801
1802 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1803
1804 x_return_status := G_RET_STS_UNEXP_ERROR;
1805
1806 WHEN OTHERS THEN
1807
1808 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1809 p_msg_name => G_DB_ERROR,
1810 p_token1 => G_PROG_NAME_TOKEN,
1811 p_token1_value => l_prog_name,
1812 p_token2 => G_SQLCODE_TOKEN,
1813 p_token2_value => sqlcode,
1814 p_token3 => G_SQLERRM_TOKEN,
1815 p_token3_value => sqlerrm);
1816
1817 x_return_status := G_RET_STS_UNEXP_ERROR;
1818
1819 END update_row;
1820
1821 -----------------
1822 -- delete_row (V)
1823 -----------------
1824 PROCEDURE delete_row(
1825 x_return_status OUT NOCOPY VARCHAR2,
1826 p_id IN NUMBER) IS
1827
1828 l_prog_name VARCHAR2(61);
1829
1830 BEGIN
1831
1832 l_prog_name := G_PKG_NAME||'.delete_row (V)';
1833
1834 DELETE FROM OKL_LEASE_APPLICATIONS_B WHERE id = p_id;
1835 DELETE FROM OKL_LEASE_APPLICATIONS_TL WHERE id = p_id;
1836
1837 x_return_status := G_RET_STS_SUCCESS;
1838
1839 EXCEPTION
1840
1841 WHEN OTHERS THEN
1842
1843 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1844 p_msg_name => G_DB_ERROR,
1845 p_token1 => G_PROG_NAME_TOKEN,
1846 p_token1_value => l_prog_name,
1847 p_token2 => G_SQLCODE_TOKEN,
1848 p_token2_value => sqlcode,
1849 p_token3 => G_SQLERRM_TOKEN,
1850 p_token3_value => sqlerrm);
1851
1852 x_return_status := G_RET_STS_UNEXP_ERROR;
1853
1854 END delete_row;
1855
1856 -----------------------------
1857 -- PROCEDURE delete_row (REC)
1858 -----------------------------
1859 PROCEDURE delete_row(
1860 p_api_version IN NUMBER,
1861 p_init_msg_list IN VARCHAR2,
1862 x_return_status OUT NOCOPY VARCHAR2,
1863 x_msg_count OUT NOCOPY NUMBER,
1864 x_msg_data OUT NOCOPY VARCHAR2,
1865 p_lapv_rec IN lapv_rec_type) IS
1866
1867 l_return_status VARCHAR2(1);
1868
1869 l_prog_name VARCHAR2(61);
1870
1871 BEGIN
1872
1873 l_prog_name := G_PKG_NAME||'.delete_row (REC)';
1874
1875 IF p_init_msg_list = G_TRUE THEN
1876 FND_MSG_PUB.initialize;
1877 END IF;
1878
1879 delete_row (x_return_status => l_return_status,
1880 p_id => p_lapv_rec.id);
1881
1882 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1883 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1884 ELSIF l_return_status = G_RET_STS_ERROR THEN
1885 RAISE OKL_API.G_EXCEPTION_ERROR;
1886 END IF;
1887
1888 x_return_status := l_return_status;
1889
1890 EXCEPTION
1891
1892 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1893
1894 x_return_status := G_RET_STS_ERROR;
1895
1896 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1897
1898 x_return_status := G_RET_STS_UNEXP_ERROR;
1899
1900 WHEN OTHERS THEN
1901
1902 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1903 p_msg_name => G_DB_ERROR,
1904 p_token1 => G_PROG_NAME_TOKEN,
1905 p_token1_value => l_prog_name,
1906 p_token2 => G_SQLCODE_TOKEN,
1907 p_token2_value => sqlcode,
1908 p_token3 => G_SQLERRM_TOKEN,
1909 p_token3_value => sqlerrm);
1910
1911 x_return_status := G_RET_STS_UNEXP_ERROR;
1912
1913 END delete_row;
1914
1915 -------------------
1916 -- delete_row (TBL)
1917 -------------------
1918 PROCEDURE delete_row(
1919 p_api_version IN NUMBER,
1920 p_init_msg_list IN VARCHAR2,
1921 x_return_status OUT NOCOPY VARCHAR2,
1922 x_msg_count OUT NOCOPY NUMBER,
1923 x_msg_data OUT NOCOPY VARCHAR2,
1924 p_lapv_tbl IN lapv_tbl_type) IS
1925
1926 l_return_status VARCHAR2(1);
1927 i BINARY_INTEGER;
1928
1929 l_prog_name VARCHAR2(61);
1930
1931 BEGIN
1932
1933 l_prog_name := G_PKG_NAME||'.delete_row (TBL)';
1934
1935 IF p_init_msg_list = G_TRUE THEN
1936 FND_MSG_PUB.initialize;
1937 END IF;
1938
1939 IF (p_lapv_tbl.COUNT > 0) THEN
1940
1941 i := p_lapv_tbl.FIRST;
1942
1943 LOOP
1944
1945 IF p_lapv_tbl.EXISTS(i) THEN
1946
1947 delete_row (x_return_status => l_return_status,
1948 p_id => p_lapv_tbl(i).id);
1949
1950 IF l_return_status = G_RET_STS_UNEXP_ERROR THEN
1951 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1952 ELSIF l_return_status = G_RET_STS_ERROR THEN
1953 RAISE OKL_API.G_EXCEPTION_ERROR;
1954 END IF;
1955
1956 EXIT WHEN (i = p_lapv_tbl.LAST);
1957 i := p_lapv_tbl.NEXT(i);
1958
1959 END IF;
1960
1961 END LOOP;
1962
1963 ELSE
1964
1965 l_return_status := G_RET_STS_SUCCESS;
1966
1967 END IF;
1968
1969 x_return_status := l_return_status;
1970
1971 EXCEPTION
1972
1973 WHEN OTHERS THEN
1974
1975 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
1976 p_msg_name => G_DB_ERROR,
1977 p_token1 => G_PROG_NAME_TOKEN,
1978 p_token1_value => l_prog_name,
1979 p_token2 => G_SQLCODE_TOKEN,
1980 p_token2_value => sqlcode,
1981 p_token3 => G_SQLERRM_TOKEN,
1982 p_token3_value => sqlerrm);
1983
1984 x_return_status := G_RET_STS_UNEXP_ERROR;
1985
1986 END delete_row;
1987
1988 END OKL_LAP_PVT;