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