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