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