[Home] [Help]
PACKAGE BODY: APPS.OKL_SVF_PVT
Source
4 -- FUNCTION get_seq_id
1 PACKAGE BODY OKL_SVF_PVT AS
2 /* $Header: OKLSSVFB.pls 120.5 2007/08/08 12:52:58 arajagop noship $ */
3 ---------------------------------------------------------------------------
5 ---------------------------------------------------------------------------
6 FUNCTION get_seq_id RETURN NUMBER IS
7 BEGIN
8 RETURN(Okc_P_Util.raw_to_number(sys_guid()));
9 END get_seq_id;
10
11 ---------------------------------------------------------------------------
12 -- PROCEDURE qc
13 ---------------------------------------------------------------------------
14 PROCEDURE qc IS
15 BEGIN
16 NULL;
17 END qc;
18
19 ---------------------------------------------------------------------------
20 -- PROCEDURE change_version
21 ---------------------------------------------------------------------------
22 PROCEDURE change_version IS
23 BEGIN
24 NULL;
25 END change_version;
26
27 ---------------------------------------------------------------------------
28 -- PROCEDURE api_copy
29 ---------------------------------------------------------------------------
30 PROCEDURE api_copy IS
31 BEGIN
32 NULL;
33 END api_copy;
34
35 ---------------------------------------------------------------------------
36 -- PROCEDURE add_language
37 ---------------------------------------------------------------------------
38 PROCEDURE add_language IS
39 BEGIN
40 DELETE FROM OKL_SERVICE_FEES_TL T
41 WHERE NOT EXISTS (
42 SELECT NULL
43 FROM OKL_SERVICE_FEES_ALL_B B --changed _TL to _B by rvaduri for MLS compliance.
44 WHERE B.ID = T.ID
45 );
46
47 UPDATE OKL_SERVICE_FEES_TL T SET (
48 NAME,
49 DESCRIPTION) = (SELECT
50 B.NAME,
51 B.DESCRIPTION
52 FROM OKL_SERVICE_FEES_TL B
53 WHERE B.ID = T.ID
54 AND B.LANGUAGE = T.SOURCE_LANG)
55 WHERE (
56 T.ID,
57 T.LANGUAGE)
58 IN (SELECT
59 SUBT.ID,
60 SUBT.LANGUAGE
61 FROM OKL_SERVICE_FEES_TL SUBB, OKL_SERVICE_FEES_TL SUBT
62 WHERE SUBB.ID = SUBT.ID
63 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
64 AND (SUBB.NAME <> SUBT.NAME
65 OR SUBB.DESCRIPTION <> SUBT.DESCRIPTION
66 OR (SUBB.NAME IS NULL AND SUBT.NAME IS NOT NULL)
67 OR (SUBB.NAME IS NOT NULL AND SUBT.NAME IS NULL)
68 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
69 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
70 ));
71
72 INSERT INTO OKL_SERVICE_FEES_TL (
73 ID,
74 LANGUAGE,
75 SOURCE_LANG,
76 SFWT_FLAG,
77 NAME,
78 DESCRIPTION,
82 LAST_UPDATE_DATE,
79 CREATED_BY,
80 CREATION_DATE,
81 LAST_UPDATED_BY,
83 LAST_UPDATE_LOGIN)
84 SELECT
85 B.ID,
86 L.LANGUAGE_CODE,
87 B.SOURCE_LANG,
88 B.SFWT_FLAG,
89 B.NAME,
90 B.DESCRIPTION,
91 B.CREATED_BY,
92 B.CREATION_DATE,
93 B.LAST_UPDATED_BY,
94 B.LAST_UPDATE_DATE,
95 B.LAST_UPDATE_LOGIN
96 FROM OKL_SERVICE_FEES_TL B, FND_LANGUAGES L
97 WHERE L.INSTALLED_FLAG IN ('I', 'B')
98 AND B.LANGUAGE = USERENV('LANG')
99 AND NOT EXISTS(
100 SELECT NULL
101 FROM OKL_SERVICE_FEES_TL T
102 WHERE T.ID = B.ID
103 AND T.LANGUAGE = L.LANGUAGE_CODE
104 );
105
106 END add_language;
107
108 ---------------------------------------------------------------------------
109 -- FUNCTION get_rec for: OKL_SERVICE_FEES_B
110 ---------------------------------------------------------------------------
111 FUNCTION get_rec (
112 p_svf_rec IN svf_rec_type,
113 x_no_data_found OUT NOCOPY BOOLEAN
114 ) RETURN svf_rec_type IS
115 CURSOR okl_service_fees_b_pk_csr (p_id IN NUMBER) IS
116 SELECT
117 ID,
118 SRV_CODE,
119 OBJECT_VERSION_NUMBER,
120 AMOUNT,
121 START_DATE,
122 END_DATE,
123 ORGANIZATION_ID,
124 ATTRIBUTE_CATEGORY,
125 ATTRIBUTE1,
126 ATTRIBUTE2,
127 ATTRIBUTE3,
128 ATTRIBUTE4,
129 ATTRIBUTE5,
130 ATTRIBUTE6,
131 ATTRIBUTE7,
132 ATTRIBUTE8,
133 ATTRIBUTE9,
134 ATTRIBUTE10,
135 ATTRIBUTE11,
136 ATTRIBUTE12,
137 ATTRIBUTE13,
138 ATTRIBUTE14,
139 ATTRIBUTE15,
140 CREATED_BY,
141 CREATION_DATE,
142 LAST_UPDATED_BY,
143 LAST_UPDATE_DATE,
144 LAST_UPDATE_LOGIN,
145 ORG_ID
146 FROM Okl_Service_Fees_B
147 WHERE okl_service_fees_b.id = p_id;
148 l_okl_service_fees_b_pk okl_service_fees_b_pk_csr%ROWTYPE;
149 l_svf_rec svf_rec_type;
150 BEGIN
151 x_no_data_found := TRUE;
152 -- Get current database values
153 OPEN okl_service_fees_b_pk_csr (p_svf_rec.id);
154 FETCH okl_service_fees_b_pk_csr INTO
155 l_svf_rec.ID,
156 l_svf_rec.SRV_CODE,
157 l_svf_rec.OBJECT_VERSION_NUMBER,
158 l_svf_rec.AMOUNT,
159 l_svf_rec.START_DATE,
160 l_svf_rec.END_DATE,
161 l_svf_rec.ORGANIZATION_ID,
162 l_svf_rec.ATTRIBUTE_CATEGORY,
163 l_svf_rec.ATTRIBUTE1,
164 l_svf_rec.ATTRIBUTE2,
165 l_svf_rec.ATTRIBUTE3,
166 l_svf_rec.ATTRIBUTE4,
167 l_svf_rec.ATTRIBUTE5,
168 l_svf_rec.ATTRIBUTE6,
169 l_svf_rec.ATTRIBUTE7,
170 l_svf_rec.ATTRIBUTE8,
171 l_svf_rec.ATTRIBUTE9,
172 l_svf_rec.ATTRIBUTE10,
173 l_svf_rec.ATTRIBUTE11,
174 l_svf_rec.ATTRIBUTE12,
175 l_svf_rec.ATTRIBUTE13,
176 l_svf_rec.ATTRIBUTE14,
177 l_svf_rec.ATTRIBUTE15,
178 l_svf_rec.CREATED_BY,
179 l_svf_rec.CREATION_DATE,
180 l_svf_rec.LAST_UPDATED_BY,
181 l_svf_rec.LAST_UPDATE_DATE,
182 l_svf_rec.LAST_UPDATE_LOGIN,
183 l_svf_rec.ORG_ID;
184 x_no_data_found := okl_service_fees_b_pk_csr%NOTFOUND;
185 CLOSE okl_service_fees_b_pk_csr;
186 RETURN(l_svf_rec);
187 END get_rec;
188
189 FUNCTION get_rec (
190 p_svf_rec IN svf_rec_type
191 ) RETURN svf_rec_type IS
192 l_row_notfound BOOLEAN := TRUE;
193 BEGIN
194 RETURN(get_rec(p_svf_rec, l_row_notfound));
195 END get_rec;
196 ---------------------------------------------------------------------------
197 -- FUNCTION get_rec for: OKL_SERVICE_FEES_TL
198 ---------------------------------------------------------------------------
199 FUNCTION get_rec (
200 p_okl_service_fees_tl_rec IN okl_service_fees_tl_rec_type,
201 x_no_data_found OUT NOCOPY BOOLEAN
202 ) RETURN okl_service_fees_tl_rec_type IS
203 CURSOR okl_service_fees_tl_pk_csr (p_id IN NUMBER,
204 p_language IN VARCHAR2) IS
205 SELECT
206 ID,
207 LANGUAGE,
208 SOURCE_LANG,
209 SFWT_FLAG,
210 NAME,
211 DESCRIPTION,
212 CREATED_BY,
213 CREATION_DATE,
214 LAST_UPDATED_BY,
215 LAST_UPDATE_DATE,
216 LAST_UPDATE_LOGIN
217 FROM Okl_Service_Fees_Tl
218 WHERE okl_service_fees_tl.id = p_id
219 AND okl_service_fees_tl.LANGUAGE = p_language;
220 l_okl_service_fees_tl_pk okl_service_fees_tl_pk_csr%ROWTYPE;
221 l_okl_service_fees_tl_rec okl_service_fees_tl_rec_type;
222 BEGIN
223 x_no_data_found := TRUE;
224 -- Get current database values
225 OPEN okl_service_fees_tl_pk_csr (p_okl_service_fees_tl_rec.id,
226 p_okl_service_fees_tl_rec.LANGUAGE);
230 l_okl_service_fees_tl_rec.SOURCE_LANG,
227 FETCH okl_service_fees_tl_pk_csr INTO
228 l_okl_service_fees_tl_rec.ID,
229 l_okl_service_fees_tl_rec.LANGUAGE,
231 l_okl_service_fees_tl_rec.SFWT_FLAG,
232 l_okl_service_fees_tl_rec.NAME,
233 l_okl_service_fees_tl_rec.DESCRIPTION,
234 l_okl_service_fees_tl_rec.CREATED_BY,
235 l_okl_service_fees_tl_rec.CREATION_DATE,
236 l_okl_service_fees_tl_rec.LAST_UPDATED_BY,
237 l_okl_service_fees_tl_rec.LAST_UPDATE_DATE,
238 l_okl_service_fees_tl_rec.LAST_UPDATE_LOGIN;
239 x_no_data_found := okl_service_fees_tl_pk_csr%NOTFOUND;
240 CLOSE okl_service_fees_tl_pk_csr;
241 RETURN(l_okl_service_fees_tl_rec);
242 END get_rec;
243
244 FUNCTION get_rec (
245 p_okl_service_fees_tl_rec IN okl_service_fees_tl_rec_type
246 ) RETURN okl_service_fees_tl_rec_type IS
247 l_row_notfound BOOLEAN := TRUE;
248 BEGIN
249 RETURN(get_rec(p_okl_service_fees_tl_rec, l_row_notfound));
250 END get_rec;
251 ---------------------------------------------------------------------------
252 -- FUNCTION get_rec for: OKL_SERVICE_FEES_V
253 ---------------------------------------------------------------------------
254 FUNCTION get_rec (
255 p_svfv_rec IN svfv_rec_type,
256 x_no_data_found OUT NOCOPY BOOLEAN
257 ) RETURN svfv_rec_type IS
258 CURSOR okl_svfv_pk_csr (p_id IN NUMBER) IS
259 SELECT
260 ID,
261 OBJECT_VERSION_NUMBER,
262 SFWT_FLAG,
263 SRV_CODE,
264 NAME,
265 DESCRIPTION,
266 AMOUNT,
267 START_DATE,
268 END_DATE,
269 ORGANIZATION_ID,
270 ATTRIBUTE_CATEGORY,
271 ATTRIBUTE1,
272 ATTRIBUTE2,
273 ATTRIBUTE3,
274 ATTRIBUTE4,
275 ATTRIBUTE5,
276 ATTRIBUTE6,
277 ATTRIBUTE7,
278 ATTRIBUTE8,
279 ATTRIBUTE9,
280 ATTRIBUTE10,
281 ATTRIBUTE11,
282 ATTRIBUTE12,
283 ATTRIBUTE13,
284 ATTRIBUTE14,
285 ATTRIBUTE15,
286 CREATED_BY,
287 CREATION_DATE,
288 LAST_UPDATED_BY,
289 LAST_UPDATE_DATE,
290 LAST_UPDATE_LOGIN
291 FROM Okl_Service_Fees_V
292 WHERE okl_service_fees_v.id = p_id;
293 l_okl_svfv_pk okl_svfv_pk_csr%ROWTYPE;
294 l_svfv_rec svfv_rec_type;
295 BEGIN
296 x_no_data_found := TRUE;
297 -- Get current database values
298 OPEN okl_svfv_pk_csr (p_svfv_rec.id);
299 FETCH okl_svfv_pk_csr INTO
300 l_svfv_rec.ID,
301 l_svfv_rec.OBJECT_VERSION_NUMBER,
302 l_svfv_rec.SFWT_FLAG,
303 l_svfv_rec.SRV_CODE,
304 l_svfv_rec.NAME,
305 l_svfv_rec.DESCRIPTION,
306 l_svfv_rec.AMOUNT,
307 l_svfv_rec.START_DATE,
308 l_svfv_rec.END_DATE,
309 l_svfv_rec.ORGANIZATION_ID,
310 l_svfv_rec.ATTRIBUTE_CATEGORY,
311 l_svfv_rec.ATTRIBUTE1,
312 l_svfv_rec.ATTRIBUTE2,
313 l_svfv_rec.ATTRIBUTE3,
314 l_svfv_rec.ATTRIBUTE4,
315 l_svfv_rec.ATTRIBUTE5,
316 l_svfv_rec.ATTRIBUTE6,
317 l_svfv_rec.ATTRIBUTE7,
318 l_svfv_rec.ATTRIBUTE8,
319 l_svfv_rec.ATTRIBUTE9,
320 l_svfv_rec.ATTRIBUTE10,
321 l_svfv_rec.ATTRIBUTE11,
322 l_svfv_rec.ATTRIBUTE12,
323 l_svfv_rec.ATTRIBUTE13,
324 l_svfv_rec.ATTRIBUTE14,
325 l_svfv_rec.ATTRIBUTE15,
326 l_svfv_rec.CREATED_BY,
327 l_svfv_rec.CREATION_DATE,
328 l_svfv_rec.LAST_UPDATED_BY,
329 l_svfv_rec.LAST_UPDATE_DATE,
330 l_svfv_rec.LAST_UPDATE_LOGIN;
331 x_no_data_found := okl_svfv_pk_csr%NOTFOUND;
332 CLOSE okl_svfv_pk_csr;
333 RETURN(l_svfv_rec);
334 END get_rec;
335
336 FUNCTION get_rec (
337 p_svfv_rec IN svfv_rec_type
338 ) RETURN svfv_rec_type IS
339 l_row_notfound BOOLEAN := TRUE;
340 BEGIN
341 RETURN(get_rec(p_svfv_rec, l_row_notfound));
342 END get_rec;
343
344 --------------------------------------------------------
345 -- FUNCTION null_out_defaults for: OKL_SERVICE_FEES_V --
346 --------------------------------------------------------
347 FUNCTION null_out_defaults (
348 p_svfv_rec IN svfv_rec_type
349 ) RETURN svfv_rec_type IS
350 l_svfv_rec svfv_rec_type := p_svfv_rec;
351 BEGIN
352 IF (l_svfv_rec.object_version_number = OKL_API.G_MISS_NUM) THEN
353 l_svfv_rec.object_version_number := NULL;
354 END IF;
355 IF (l_svfv_rec.sfwt_flag = OKL_API.G_MISS_CHAR) THEN
356 l_svfv_rec.sfwt_flag := NULL;
357 END IF;
358 IF (l_svfv_rec.srv_code = OKL_API.G_MISS_CHAR) THEN
359 l_svfv_rec.srv_code := NULL;
360 END IF;
361 IF (l_svfv_rec.name = OKL_API.G_MISS_CHAR) THEN
362 l_svfv_rec.name := NULL;
363 END IF;
364 IF (l_svfv_rec.description = OKL_API.G_MISS_CHAR) THEN
365 l_svfv_rec.description := NULL;
366 END IF;
370 IF (l_svfv_rec.start_date = OKL_API.G_MISS_DATE) THEN
367 IF (l_svfv_rec.amount = OKL_API.G_MISS_NUM) THEN
368 l_svfv_rec.amount := NULL;
369 END IF;
371 l_svfv_rec.start_date := NULL;
372 END IF;
373 IF (l_svfv_rec.end_date = OKL_API.G_MISS_DATE) THEN
374 l_svfv_rec.end_date := NULL;
375 END IF;
376 IF (l_svfv_rec.organization_id = OKL_API.G_MISS_NUM) THEN
377 l_svfv_rec.organization_id := NULL;
378 END IF;
379 IF (l_svfv_rec.attribute_category = OKL_API.G_MISS_CHAR) THEN
380 l_svfv_rec.attribute_category := NULL;
381 END IF;
382 IF (l_svfv_rec.attribute1 = OKL_API.G_MISS_CHAR) THEN
383 l_svfv_rec.attribute1 := NULL;
384 END IF;
385 IF (l_svfv_rec.attribute2 = OKL_API.G_MISS_CHAR) THEN
386 l_svfv_rec.attribute2 := NULL;
387 END IF;
388 IF (l_svfv_rec.attribute3 = OKL_API.G_MISS_CHAR) THEN
389 l_svfv_rec.attribute3 := NULL;
390 END IF;
391 IF (l_svfv_rec.attribute4 = OKL_API.G_MISS_CHAR) THEN
392 l_svfv_rec.attribute4 := NULL;
393 END IF;
394 IF (l_svfv_rec.attribute5 = OKL_API.G_MISS_CHAR) THEN
395 l_svfv_rec.attribute5 := NULL;
396 END IF;
397 IF (l_svfv_rec.attribute6 = OKL_API.G_MISS_CHAR) THEN
398 l_svfv_rec.attribute6 := NULL;
399 END IF;
400 IF (l_svfv_rec.attribute7 = OKL_API.G_MISS_CHAR) THEN
401 l_svfv_rec.attribute7 := NULL;
402 END IF;
403 IF (l_svfv_rec.attribute8 = OKL_API.G_MISS_CHAR) THEN
404 l_svfv_rec.attribute8 := NULL;
405 END IF;
406 IF (l_svfv_rec.attribute9 = OKL_API.G_MISS_CHAR) THEN
407 l_svfv_rec.attribute9 := NULL;
408 END IF;
409 IF (l_svfv_rec.attribute10 = OKL_API.G_MISS_CHAR) THEN
410 l_svfv_rec.attribute10 := NULL;
411 END IF;
412 IF (l_svfv_rec.attribute11 = OKL_API.G_MISS_CHAR) THEN
413 l_svfv_rec.attribute11 := NULL;
414 END IF;
415 IF (l_svfv_rec.attribute12 = OKL_API.G_MISS_CHAR) THEN
416 l_svfv_rec.attribute12 := NULL;
417 END IF;
418 IF (l_svfv_rec.attribute13 = OKL_API.G_MISS_CHAR) THEN
419 l_svfv_rec.attribute13 := NULL;
420 END IF;
421 IF (l_svfv_rec.attribute14 = OKL_API.G_MISS_CHAR) THEN
422 l_svfv_rec.attribute14 := NULL;
423 END IF;
424 IF (l_svfv_rec.attribute15 = OKL_API.G_MISS_CHAR) THEN
425 l_svfv_rec.attribute15 := NULL;
426 END IF;
427 IF (l_svfv_rec.created_by = OKL_API.G_MISS_NUM) THEN
428 l_svfv_rec.created_by := NULL;
429 END IF;
430 IF (l_svfv_rec.creation_date = OKL_API.G_MISS_DATE) THEN
431 l_svfv_rec.creation_date := NULL;
432 END IF;
433 IF (l_svfv_rec.last_updated_by = OKL_API.G_MISS_NUM) THEN
434 l_svfv_rec.last_updated_by := NULL;
435 END IF;
436 IF (l_svfv_rec.last_update_date = OKL_API.G_MISS_DATE) THEN
437 l_svfv_rec.last_update_date := NULL;
438 END IF;
439 IF (l_svfv_rec.last_update_login = OKL_API.G_MISS_NUM) THEN
440 l_svfv_rec.last_update_login := NULL;
441 END IF;
442 RETURN(l_svfv_rec);
443 END null_out_defaults;
444 ---------------------------------------------------------------------------
445 -- PROCEDURE Validate_Attributes
446 ---------------------------------------------------------------------------
447 ---------------------------------------------------------------------------------------
448 --Attribute Level Validattion Procedures Starts(Modification on TAPI generated Code.)--
449 ---------------------------------------------------------------------------------------
450 ---------------------------------------------------------------------------
451 -- PROCEDURE Validate_svfv_id
452 ---------------------------------------------------------------------------
453 PROCEDURE validate_svfv_id(
454 p_svfv_rec IN svfv_rec_type,
455 x_return_status OUT NOCOPY VARCHAR2) IS
456 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
457 BEGIN
458 IF p_svfv_rec.id = OKL_API.G_MISS_NUM OR
459 p_svfv_rec.id IS NULL
460 THEN
461 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
462 l_return_status := OKL_API.G_RET_STS_ERROR;
463 END IF;
464 x_return_status := l_return_status;
465 EXCEPTION
466 WHEN OTHERS THEN
467 -- store SQL error message on message stack for caller
468 OKL_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
469 -- notify caller of an UNEXPECTED error
470 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
471 END validate_svfv_id;
472
473
474 ---------------------------------------------------------------------------
475 -- PROCEDURE Validate_object_version_number
476 ---------------------------------------------------------------------------
477 PROCEDURE validate_object_version_number(
478 p_svfv_rec IN svfv_rec_type,
479 x_return_status OUT NOCOPY VARCHAR2) IS
480 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
481 BEGIN
482 IF p_svfv_rec.object_version_number = OKL_API.G_MISS_NUM OR
483 p_svfv_rec.object_version_number IS NULL
484 THEN
485 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
486 l_return_status := OKL_API.G_RET_STS_ERROR;
487 END IF;
488 x_return_status := l_return_status;
489 EXCEPTION
490 WHEN OTHERS THEN
491 -- store SQL error message on message stack for caller
492 OKL_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
496
493 -- notify caller of an UNEXPECTED error
494 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
495 END validate_object_version_number;
497
498 --------------------------------------------------------------------------
499 -- PROCEDURE Validate_srv_code
500 ---------------------------------------------------------------------------
501 PROCEDURE validate_srv_code(p_svfv_rec IN svfv_rec_type, x_return_status OUT NOCOPY VARCHAR2) IS
502
503 BEGIN
504
505 x_return_status := OKL_API.G_RET_STS_SUCCESS;
506
507 IF p_svfv_rec.srv_code = OKL_API.G_MISS_CHAR OR p_svfv_rec.srv_code IS NULL
508 THEN
509 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'srv_code');
510 x_return_status := OKL_API.G_RET_STS_ERROR;
511 RETURN;
512 END IF;
513
514 x_return_status := OKL_UTIL.check_lookup_code('OKL_SERVICE_FEES', p_svfv_rec.srv_code);
515
516 IF NOT (x_return_status = OKL_API.G_RET_STS_SUCCESS ) THEN
517 OKL_API.set_message(p_app_name => G_APP_NAME,
518 p_msg_name => G_NO_PARENT_RECORD,
519 p_token1 => G_COL_NAME_TOKEN,
520 p_token1_value => 'SRV_CODE',
521 p_token2 => g_child_table_token,
522 p_token2_value => 'OKL_SERVICE_FEES_B',
523 p_token3 => g_parent_table_token,
524 p_token3_value => 'FND_LOOKUPS');
525
526 x_return_status := OKL_API.G_RET_STS_ERROR;
527 RETURN;
528 END IF;
529
530 EXCEPTION
531 WHEN OTHERS THEN
532 -- store SQL error message on message stack for caller
533 OKL_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
534 -- notify caller of an UNEXPECTED error
535 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
536
537 END validate_srv_code;
538
539 ---------------------------------------------------------------------------
540 -- PROCEDURE Validate_amount
541 ---------------------------------------------------------------------------
542 PROCEDURE validate_amount(
543 p_svfv_rec IN svfv_rec_type,
544 x_return_status OUT NOCOPY VARCHAR2) IS
545 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
546 BEGIN
547
548 IF p_svfv_rec.amount = OKL_API.G_MISS_NUM OR p_svfv_rec.amount IS NULL
549 THEN
550 OKL_API.set_message(G_APP_NAME, G_OKL_AMOUNT_GREATER_THAN_ZERO);
551 l_return_status := OKL_API.G_RET_STS_ERROR;
552 END IF;
553
554 IF p_svfv_rec.amount < 0
555 THEN
556 OKL_API.set_message(G_APP_NAME, G_OKL_AMOUNT_GREATER_THAN_ZERO);
557 l_return_status := OKL_API.G_RET_STS_ERROR;
558 END IF;
559 x_return_status := l_return_status;
560 EXCEPTION
561 WHEN OTHERS THEN
562 -- store SQL error message on message stack for caller
563 OKL_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
564 -- notify caller of an UNEXPECTED error
565 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
566 END validate_amount;
567
568 ---------------------------------------------------------------------------
569 -- PROCEDURE Validate_sfwt_flag
570 ---------------------------------------------------------------------------
571 PROCEDURE validate_sfwt_flag(
572 p_svfv_rec IN svfv_rec_type,
573 x_return_status OUT NOCOPY VARCHAR2) IS
574 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
575 BEGIN
576 IF p_svfv_rec.sfwt_flag = OKL_API.G_MISS_CHAR OR p_svfv_rec.sfwt_flag IS NULL
577 THEN
578 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'SFWT_FLAG');
579 l_return_status := OKL_API.G_RET_STS_ERROR;
580 END IF;
581 x_return_status := l_return_status;
582 EXCEPTION
583 WHEN OTHERS THEN
584 -- store SQL error message on message stack for caller
585 OKL_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
586 -- notify caller of an UNEXPECTED error
587 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
588 END validate_sfwt_flag;
589
590 ---------------------------------------------------------------------------
591 -- PROCEDURE Validate_start_date
592 ---------------------------------------------------------------------------
593 PROCEDURE validate_start_date(
594 p_svfv_rec IN svfv_rec_type,
595 x_return_status OUT NOCOPY VARCHAR2) IS
596 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
597 BEGIN
598 IF p_svfv_rec.start_date = OKL_API.G_MISS_DATE OR p_svfv_rec.start_date IS NULL
599 THEN
600 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'EFFECTIVE_FROM');
601 l_return_status := OKL_API.G_RET_STS_ERROR;
602 END IF;
603
604 x_return_status := l_return_status;
605 EXCEPTION
606 WHEN OTHERS THEN
607 -- store SQL error message on message stack for caller
608 OKL_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
609 -- notify caller of an UNEXPECTED error
610 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
611 END validate_start_date;
612
613
614
615
616 ------------------------------------------------
617 -- Validate_Attributes for:OKL_SERVICE_FEES_V --
618 ------------------------------------------------
619 FUNCTION Validate_Attributes (
620 p_svfv_rec IN svfv_rec_type
621 ) RETURN VARCHAR2 IS
622 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
626 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
623 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
624 BEGIN
625 validate_svfv_id(p_svfv_rec, l_return_status);
627 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
628 x_return_status := l_return_status;
629 RAISE G_EXCEPTION_HALT_VALIDATION;
630 ELSE
631 x_return_status := l_return_status; -- record that there was an error
632 END IF;
633 END IF;
634
635 validate_object_version_number(p_svfv_rec, l_return_status);
636 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
637 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
638 x_return_status := l_return_status;
639 RAISE G_EXCEPTION_HALT_VALIDATION;
640 ELSE
641 x_return_status := l_return_status; -- record that there was an error
642 END IF;
643 END IF;
644
645 validate_srv_code(p_svfv_rec, l_return_status);
646 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
647 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
648 x_return_status := l_return_status;
649 RAISE G_EXCEPTION_HALT_VALIDATION;
650 ELSE
651 x_return_status := l_return_status; -- record that there was an error
652 END IF;
653 END IF;
654
655 validate_amount(p_svfv_rec, l_return_status);
656 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
657 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
658 x_return_status := l_return_status;
659 RAISE G_EXCEPTION_HALT_VALIDATION;
660 ELSE
661 x_return_status := l_return_status; -- record that there was an error
662 END IF;
663 END IF;
664
665 validate_sfwt_flag(p_svfv_rec, l_return_status);
666 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
667 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
668 x_return_status := l_return_status;
669 RAISE G_EXCEPTION_HALT_VALIDATION;
670 ELSE
671 x_return_status := l_return_status; -- record that there was an error
672 END IF;
673 END IF;
674
675 validate_start_date(p_svfv_rec, l_return_status);
676 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
677 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
678 x_return_status := l_return_status;
679 RAISE G_EXCEPTION_HALT_VALIDATION;
680 ELSE
681 x_return_status := l_return_status; -- record that there was an error
682 END IF;
683 END IF;
684
685
686 RETURN(x_return_status);
687
688 EXCEPTION
689
690 WHEN G_EXCEPTION_HALT_VALIDATION THEN
691 RETURN(x_return_status);
692 WHEN OTHERS THEN
693 -- store SQL error message on message stack for caller
694 OKL_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
695 -- notify caller of an UNEXPECTED error
696 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
697 RETURN(x_return_status);
698
699 END Validate_Attributes;
700
701 ---------------------------------------------------------------------------
702 -- PROCEDURE Validate_Record
703 ---------------------------------------------------------------------------
704 --------------------------------------------
705 -- Validate_Record for:OKL_SERVICE_FEES_V --
706 --------------------------------------------
707 FUNCTION Validate_Record (
708 p_svfv_rec IN svfv_rec_type
709 ) RETURN VARCHAR2 IS
710 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
711
712 CURSOR c_relevant_records IS
713 SELECT id, srv_code, organization_id, end_date, start_date
714 FROM okl_service_fees_b
715 WHERE id = p_svfv_rec.id
716 OR organization_id = p_svfv_rec.organization_id AND id <> p_svfv_rec.id;
717
718 BEGIN
719
720 IF p_svfv_rec.end_date < p_svfv_rec.start_date THEN
721 OKL_API.set_message(G_APP_NAME, G_OKL_INVALID_END_DATE);
722 l_return_status := OKL_API.G_RET_STS_ERROR;
723 RETURN l_return_status;
724 END IF;
725
726 FOR v_rec IN c_relevant_records LOOP
727
728 IF v_rec.id <> p_svfv_rec.id AND
729 v_rec.organization_id = p_svfv_rec.organization_id AND
730 v_rec.srv_code = p_svfv_rec.srv_code AND
731 TRUNC(v_rec.start_date)<= TRUNC(p_svfv_rec.start_date) AND
732 (TRUNC(v_rec.end_date) >= TRUNC(p_svfv_rec.start_date) OR v_rec.end_date IS NULL) THEN
733
734 OKL_API.set_message(G_APP_NAME, G_OKL_DUPLICATE_SERVICE_FEE);
735 l_return_status := OKL_API.G_RET_STS_ERROR;
736 RETURN l_return_status;
737 END IF;
738
739 IF v_rec.id <> p_svfv_rec.id AND
740 v_rec.organization_id = p_svfv_rec.organization_id AND
741 v_rec.srv_code = p_svfv_rec.srv_code AND
742 TRUNC(v_rec.start_date)>= TRUNC(p_svfv_rec.start_date) AND
743 (TRUNC(p_svfv_rec.end_date) >= TRUNC(v_rec.start_date) OR p_svfv_rec.end_date IS NULL) THEN
744
745 OKL_API.set_message(G_APP_NAME, G_OKL_DUPLICATE_SERVICE_FEE);
746 l_return_status := OKL_API.G_RET_STS_ERROR;
747 RETURN l_return_status;
748 END IF;
749
750 END LOOP;
751
752 RETURN (l_return_status);
753
754
755 EXCEPTION
756
757 WHEN OTHERS THEN
758
759 OKL_API.set_message(p_app_name => g_app_name,
760 p_msg_name => g_unexpected_error,
761 p_token1 => g_sqlcode_token,
765 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
762 p_token1_value => sqlcode,
763 p_token2 => g_sqlerrm_token,
764 p_token2_value => sqlerrm);
766
767 RETURN(l_return_status);
768
769 ----------------------------------------------------------------------------
770 -- End Post Tapi Generation validation Code
771 ----------------------------------------------------------------------------
772
773 END Validate_Record;
774
775 ---------------------------------------------------------------------------
776 -- PROCEDURE Migrate
777 ---------------------------------------------------------------------------
778 PROCEDURE migrate (
779 p_from IN svfv_rec_type,
780 p_to IN OUT NOCOPY svf_rec_type
781 ) IS
782 BEGIN
783 p_to.id := p_from.id;
784 p_to.srv_code := p_from.srv_code;
785 p_to.object_version_number := p_from.object_version_number;
786 p_to.amount := p_from.amount;
787 p_to.start_date := p_from.start_date;
788 p_to.end_date := p_from.end_date;
789 p_to.organization_id := p_from.organization_id;
790 p_to.attribute_category := p_from.attribute_category;
791 p_to.attribute1 := p_from.attribute1;
792 p_to.attribute2 := p_from.attribute2;
793 p_to.attribute3 := p_from.attribute3;
794 p_to.attribute4 := p_from.attribute4;
795 p_to.attribute5 := p_from.attribute5;
796 p_to.attribute6 := p_from.attribute6;
797 p_to.attribute7 := p_from.attribute7;
798 p_to.attribute8 := p_from.attribute8;
799 p_to.attribute9 := p_from.attribute9;
800 p_to.attribute10 := p_from.attribute10;
801 p_to.attribute11 := p_from.attribute11;
802 p_to.attribute12 := p_from.attribute12;
803 p_to.attribute13 := p_from.attribute13;
804 p_to.attribute14 := p_from.attribute14;
805 p_to.attribute15 := p_from.attribute15;
806 p_to.created_by := p_from.created_by;
807 p_to.creation_date := p_from.creation_date;
808 p_to.last_updated_by := p_from.last_updated_by;
809 p_to.last_update_date := p_from.last_update_date;
810 p_to.last_update_login := p_from.last_update_login;
811 END migrate;
812 PROCEDURE migrate (
813 p_from IN svf_rec_type,
814 p_to OUT NOCOPY svfv_rec_type
815 ) IS
816 BEGIN
817 p_to.id := p_from.id;
818 p_to.srv_code := p_from.srv_code;
819 p_to.object_version_number := p_from.object_version_number;
820 p_to.amount := p_from.amount;
821 p_to.start_date := p_from.start_date;
822 p_to.end_date := p_from.end_date;
823 p_to.organization_id := p_from.organization_id;
824 p_to.attribute_category := p_from.attribute_category;
825 p_to.attribute1 := p_from.attribute1;
826 p_to.attribute2 := p_from.attribute2;
827 p_to.attribute3 := p_from.attribute3;
828 p_to.attribute4 := p_from.attribute4;
829 p_to.attribute5 := p_from.attribute5;
830 p_to.attribute6 := p_from.attribute6;
831 p_to.attribute7 := p_from.attribute7;
832 p_to.attribute8 := p_from.attribute8;
833 p_to.attribute9 := p_from.attribute9;
834 p_to.attribute10 := p_from.attribute10;
835 p_to.attribute11 := p_from.attribute11;
836 p_to.attribute12 := p_from.attribute12;
837 p_to.attribute13 := p_from.attribute13;
838 p_to.attribute14 := p_from.attribute14;
839 p_to.attribute15 := p_from.attribute15;
840 p_to.created_by := p_from.created_by;
841 p_to.creation_date := p_from.creation_date;
842 p_to.last_updated_by := p_from.last_updated_by;
843 p_to.last_update_date := p_from.last_update_date;
844 p_to.last_update_login := p_from.last_update_login;
845 END migrate;
846 PROCEDURE migrate (
847 p_from IN svfv_rec_type,
848 p_to OUT NOCOPY okl_service_fees_tl_rec_type
849 ) IS
850 BEGIN
851 p_to.id := p_from.id;
852 p_to.sfwt_flag := p_from.sfwt_flag;
853 p_to.name := p_from.name;
854 p_to.description := p_from.description;
855 p_to.created_by := p_from.created_by;
856 p_to.creation_date := p_from.creation_date;
857 p_to.last_updated_by := p_from.last_updated_by;
858 p_to.last_update_date := p_from.last_update_date;
859 p_to.last_update_login := p_from.last_update_login;
860 END migrate;
861 PROCEDURE migrate (
862 p_from IN okl_service_fees_tl_rec_type,
863 p_to OUT NOCOPY svfv_rec_type
864 ) IS
865 BEGIN
866 p_to.id := p_from.id;
867 p_to.sfwt_flag := p_from.sfwt_flag;
868 p_to.name := p_from.name;
869 p_to.description := p_from.description;
870 p_to.created_by := p_from.created_by;
871 p_to.creation_date := p_from.creation_date;
872 p_to.last_updated_by := p_from.last_updated_by;
873 p_to.last_update_date := p_from.last_update_date;
874 p_to.last_update_login := p_from.last_update_login;
875 END migrate;
876
877 ---------------------------------------------------------------------------
878 -- PROCEDURE validate_row
879 ---------------------------------------------------------------------------
880 -----------------------------------------
881 -- validate_row for:OKL_SERVICE_FEES_V --
882 -----------------------------------------
883 PROCEDURE validate_row(
884 p_api_version IN NUMBER,
885 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
886 x_return_status OUT NOCOPY VARCHAR2,
887 x_msg_count OUT NOCOPY NUMBER,
888 x_msg_data OUT NOCOPY VARCHAR2,
889 p_svfv_rec IN svfv_rec_type) IS
890
891 l_api_version CONSTANT NUMBER := 1;
892 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
896 l_okl_service_fees_tl_rec okl_service_fees_tl_rec_type;
893 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
894 l_svfv_rec svfv_rec_type := p_svfv_rec;
895 l_svf_rec svf_rec_type;
897 BEGIN
898 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
899 G_PKG_NAME,
900 p_init_msg_list,
901 l_api_version,
902 p_api_version,
903 '_PVT',
904 x_return_status);
905 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
906 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
907 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
908 RAISE OKL_API.G_EXCEPTION_ERROR;
909 END IF;
910 --- Validate all non-missing attributes (Item Level Validation)
911 l_return_status := Validate_Attributes(l_svfv_rec);
912 --- If any errors happen abort API
913 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
914 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
915 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
916 RAISE OKL_API.G_EXCEPTION_ERROR;
917 END IF;
918 l_return_status := Validate_Record(l_svfv_rec);
919 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
920 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
921 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
922 RAISE OKL_API.G_EXCEPTION_ERROR;
923 END IF;
924 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
925 EXCEPTION
926 WHEN OKL_API.G_EXCEPTION_ERROR THEN
927 x_return_status := OKL_API.HANDLE_EXCEPTIONS
928 (
929 l_api_name,
930 G_PKG_NAME,
931 'OKL_API.G_RET_STS_ERROR',
932 x_msg_count,
933 x_msg_data,
934 '_PVT'
935 );
936 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
937 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
938 (
939 l_api_name,
940 G_PKG_NAME,
941 'OKL_API.G_RET_STS_UNEXP_ERROR',
942 x_msg_count,
943 x_msg_data,
944 '_PVT'
945 );
946 WHEN OTHERS THEN
947 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
948 (
949 l_api_name,
950 G_PKG_NAME,
951 'OTHERS',
952 x_msg_count,
953 x_msg_data,
954 '_PVT'
955 );
956 END validate_row;
957 ------------------------------------------
958 -- PL/SQL TBL validate_row for:SVFV_TBL --
959 ------------------------------------------
960 PROCEDURE validate_row(
961 p_api_version IN NUMBER,
962 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
963 x_return_status OUT NOCOPY VARCHAR2,
964 x_msg_count OUT NOCOPY NUMBER,
965 x_msg_data OUT NOCOPY VARCHAR2,
966 p_svfv_tbl IN svfv_tbl_type) IS
967
968 l_api_version CONSTANT NUMBER := 1;
969 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
970 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
971 i NUMBER := 0;
972 BEGIN
973 OKL_API.init_msg_list(p_init_msg_list);
974 -- Make sure PL/SQL table has records in it before passing
975 IF (p_svfv_tbl.COUNT > 0) THEN
976 i := p_svfv_tbl.FIRST;
977 LOOP
978 validate_row (
979 p_api_version => p_api_version,
980 p_init_msg_list => OKL_API.G_FALSE,
981 x_return_status => x_return_status,
982 x_msg_count => x_msg_count,
983 x_msg_data => x_msg_data,
984 p_svfv_rec => p_svfv_tbl(i));
985 EXIT WHEN (i = p_svfv_tbl.LAST);
986 i := p_svfv_tbl.NEXT(i);
987 END LOOP;
988 END IF;
989 EXCEPTION
990 WHEN OKL_API.G_EXCEPTION_ERROR THEN
991 x_return_status := OKL_API.HANDLE_EXCEPTIONS
992 (
993 l_api_name,
994 G_PKG_NAME,
995 'OKL_API.G_RET_STS_ERROR',
996 x_msg_count,
997 x_msg_data,
998 '_PVT'
999 );
1000 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1001 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1002 (
1003 l_api_name,
1004 G_PKG_NAME,
1005 'OKL_API.G_RET_STS_UNEXP_ERROR',
1006 x_msg_count,
1007 x_msg_data,
1008 '_PVT'
1009 );
1010 WHEN OTHERS THEN
1011 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1012 (
1013 l_api_name,
1014 G_PKG_NAME,
1015 'OTHERS',
1016 x_msg_count,
1017 x_msg_data,
1018 '_PVT'
1019 );
1020 END validate_row;
1021
1022 ---------------------------------------------------------------------------
1023 -- PROCEDURE insert_row
1024 ---------------------------------------------------------------------------
1025 ---------------------------------------
1026 -- insert_row for:OKL_SERVICE_FEES_B --
1027 ---------------------------------------
1028 PROCEDURE insert_row(
1029 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1033 p_svf_rec IN svf_rec_type,
1030 x_return_status OUT NOCOPY VARCHAR2,
1031 x_msg_count OUT NOCOPY NUMBER,
1032 x_msg_data OUT NOCOPY VARCHAR2,
1034 x_svf_rec OUT NOCOPY svf_rec_type) IS
1035
1036 l_api_version CONSTANT NUMBER := 1;
1037 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1038 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1039 l_svf_rec svf_rec_type := p_svf_rec;
1040 l_def_svf_rec svf_rec_type;
1041 -------------------------------------------
1042 -- Set_Attributes for:OKL_SERVICE_FEES_B --
1043 -------------------------------------------
1044 FUNCTION Set_Attributes (
1045 p_svf_rec IN svf_rec_type,
1046 x_svf_rec OUT NOCOPY svf_rec_type
1047 ) RETURN VARCHAR2 IS
1048 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1049 BEGIN
1050 x_svf_rec := p_svf_rec;
1051 RETURN(l_return_status);
1052 END Set_Attributes;
1053 BEGIN
1054 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1055 p_init_msg_list,
1056 '_PVT',
1057 x_return_status);
1058 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1059 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1060 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1061 RAISE OKL_API.G_EXCEPTION_ERROR;
1062 END IF;
1063 --- Setting item attributes
1064 l_return_status := Set_Attributes(
1065 p_svf_rec, -- IN
1066 l_svf_rec); -- OUT
1067 --- If any errors happen abort API
1068 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1069 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1070 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1071 RAISE OKL_API.G_EXCEPTION_ERROR;
1072 END IF;
1073 INSERT INTO OKL_SERVICE_FEES_B(
1074 id,
1075 srv_code,
1076 object_version_number,
1077 amount,
1078 start_date,
1079 end_date,
1080 organization_id,
1081 attribute_category,
1082 attribute1,
1083 attribute2,
1084 attribute3,
1085 attribute4,
1086 attribute5,
1087 attribute6,
1088 attribute7,
1089 attribute8,
1090 attribute9,
1091 attribute10,
1092 attribute11,
1093 attribute12,
1094 attribute13,
1095 attribute14,
1096 attribute15,
1097 created_by,
1098 creation_date,
1099 last_updated_by,
1100 last_update_date,
1101 last_update_login,
1102 org_id)
1103 VALUES (
1104 l_svf_rec.id,
1105 l_svf_rec.srv_code,
1106 l_svf_rec.object_version_number,
1107 l_svf_rec.amount,
1108 l_svf_rec.start_date,
1109 l_svf_rec.end_date,
1110 l_svf_rec.organization_id,
1111 l_svf_rec.attribute_category,
1112 l_svf_rec.attribute1,
1113 l_svf_rec.attribute2,
1114 l_svf_rec.attribute3,
1115 l_svf_rec.attribute4,
1116 l_svf_rec.attribute5,
1117 l_svf_rec.attribute6,
1118 l_svf_rec.attribute7,
1119 l_svf_rec.attribute8,
1120 l_svf_rec.attribute9,
1121 l_svf_rec.attribute10,
1122 l_svf_rec.attribute11,
1123 l_svf_rec.attribute12,
1124 l_svf_rec.attribute13,
1125 l_svf_rec.attribute14,
1126 l_svf_rec.attribute15,
1127 l_svf_rec.created_by,
1128 l_svf_rec.creation_date,
1129 l_svf_rec.last_updated_by,
1130 l_svf_rec.last_update_date,
1131 l_svf_rec.last_update_login,
1132 l_svf_rec.organization_id);
1133 -- Set OUT values
1134 x_svf_rec := l_svf_rec;
1135 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1136 EXCEPTION
1137 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1138 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1139 (
1140 l_api_name,
1141 G_PKG_NAME,
1142 'OKL_API.G_RET_STS_ERROR',
1143 x_msg_count,
1144 x_msg_data,
1145 '_PVT'
1146 );
1147 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1148 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1149 (
1150 l_api_name,
1151 G_PKG_NAME,
1152 'OKL_API.G_RET_STS_UNEXP_ERROR',
1153 x_msg_count,
1154 x_msg_data,
1155 '_PVT'
1156 );
1157 WHEN OTHERS THEN
1158 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1159 (
1160 l_api_name,
1161 G_PKG_NAME,
1162 'OTHERS',
1163 x_msg_count,
1164 x_msg_data,
1165 '_PVT'
1166 );
1167 END insert_row;
1168 ----------------------------------------
1169 -- insert_row for:OKL_SERVICE_FEES_TL --
1170 ----------------------------------------
1171 PROCEDURE insert_row(
1172 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1173 x_return_status OUT NOCOPY VARCHAR2,
1174 x_msg_count OUT NOCOPY NUMBER,
1175 x_msg_data OUT NOCOPY VARCHAR2,
1176 p_okl_service_fees_tl_rec IN okl_service_fees_tl_rec_type,
1177 x_okl_service_fees_tl_rec OUT NOCOPY okl_service_fees_tl_rec_type) IS
1178
1179 l_api_version CONSTANT NUMBER := 1;
1183 ldefoklservicefeestlrec okl_service_fees_tl_rec_type;
1180 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
1181 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1182 l_okl_service_fees_tl_rec okl_service_fees_tl_rec_type := p_okl_service_fees_tl_rec;
1184 CURSOR get_languages IS
1185 SELECT *
1186 FROM FND_LANGUAGES
1187 WHERE INSTALLED_FLAG IN ('I', 'B');
1188 --------------------------------------------
1189 -- Set_Attributes for:OKL_SERVICE_FEES_TL --
1190 --------------------------------------------
1191 FUNCTION Set_Attributes (
1192 p_okl_service_fees_tl_rec IN okl_service_fees_tl_rec_type,
1193 x_okl_service_fees_tl_rec OUT NOCOPY okl_service_fees_tl_rec_type
1194 ) RETURN VARCHAR2 IS
1195 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1196 BEGIN
1197 x_okl_service_fees_tl_rec := p_okl_service_fees_tl_rec;
1198 x_okl_service_fees_tl_rec.LANGUAGE := USERENV('LANG');
1199 x_okl_service_fees_tl_rec.SOURCE_LANG := USERENV('LANG');
1200 RETURN(l_return_status);
1201 END Set_Attributes;
1202 BEGIN
1203 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1204 p_init_msg_list,
1205 '_PVT',
1206 x_return_status);
1207 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1208 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1209 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1210 RAISE OKL_API.G_EXCEPTION_ERROR;
1211 END IF;
1212 --- Setting item attributes
1213 l_return_status := Set_Attributes(
1214 p_okl_service_fees_tl_rec, -- IN
1215 l_okl_service_fees_tl_rec); -- OUT
1216 --- If any errors happen abort API
1217 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1218 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1219 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1220 RAISE OKL_API.G_EXCEPTION_ERROR;
1221 END IF;
1222 FOR l_lang_rec IN get_languages LOOP
1223 l_okl_service_fees_tl_rec.LANGUAGE := l_lang_rec.language_code;
1224 INSERT INTO OKL_SERVICE_FEES_TL(
1225 id,
1226 LANGUAGE,
1227 source_lang,
1228 sfwt_flag,
1229 name,
1230 description,
1231 created_by,
1232 creation_date,
1233 last_updated_by,
1234 last_update_date,
1235 last_update_login)
1236 VALUES (
1237 l_okl_service_fees_tl_rec.id,
1238 l_okl_service_fees_tl_rec.LANGUAGE,
1239 l_okl_service_fees_tl_rec.source_lang,
1240 l_okl_service_fees_tl_rec.sfwt_flag,
1241 l_okl_service_fees_tl_rec.name,
1242 l_okl_service_fees_tl_rec.description,
1243 l_okl_service_fees_tl_rec.created_by,
1244 l_okl_service_fees_tl_rec.creation_date,
1245 l_okl_service_fees_tl_rec.last_updated_by,
1246 l_okl_service_fees_tl_rec.last_update_date,
1247 l_okl_service_fees_tl_rec.last_update_login);
1248 END LOOP;
1249 -- Set OUT values
1250 x_okl_service_fees_tl_rec := l_okl_service_fees_tl_rec;
1251 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1252 EXCEPTION
1253 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1254 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1255 (
1256 l_api_name,
1257 G_PKG_NAME,
1258 'OKL_API.G_RET_STS_ERROR',
1259 x_msg_count,
1260 x_msg_data,
1261 '_PVT'
1262 );
1263 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1264 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1265 (
1266 l_api_name,
1267 G_PKG_NAME,
1268 'OKL_API.G_RET_STS_UNEXP_ERROR',
1269 x_msg_count,
1270 x_msg_data,
1271 '_PVT'
1272 );
1273 WHEN OTHERS THEN
1274 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1275 (
1276 l_api_name,
1277 G_PKG_NAME,
1278 'OTHERS',
1279 x_msg_count,
1280 x_msg_data,
1281 '_PVT'
1282 );
1283 END insert_row;
1284 ---------------------------------------
1285 -- insert_row for:OKL_SERVICE_FEES_V --
1286 ---------------------------------------
1287 PROCEDURE insert_row(
1288 p_api_version IN NUMBER,
1289 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1290 x_return_status OUT NOCOPY VARCHAR2,
1291 x_msg_count OUT NOCOPY NUMBER,
1292 x_msg_data OUT NOCOPY VARCHAR2,
1293 p_svfv_rec IN svfv_rec_type,
1294 x_svfv_rec OUT NOCOPY svfv_rec_type) IS
1295
1296 l_api_version CONSTANT NUMBER := 1;
1297 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1298 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1299 l_svfv_rec svfv_rec_type;
1300 l_def_svfv_rec svfv_rec_type;
1301 l_svf_rec svf_rec_type;
1302 lx_svf_rec svf_rec_type;
1303 l_okl_service_fees_tl_rec okl_service_fees_tl_rec_type;
1304 lx_okl_service_fees_tl_rec okl_service_fees_tl_rec_type;
1305 -------------------------------
1306 -- FUNCTION fill_who_columns --
1307 -------------------------------
1308 FUNCTION fill_who_columns (
1309 p_svfv_rec IN svfv_rec_type
1310 ) RETURN svfv_rec_type IS
1311 l_svfv_rec svfv_rec_type := p_svfv_rec;
1312 BEGIN
1313 l_svfv_rec.CREATION_DATE := SYSDATE;
1317 l_svfv_rec.LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
1314 l_svfv_rec.CREATED_BY := Fnd_Global.USER_ID;
1315 l_svfv_rec.LAST_UPDATE_DATE := l_svfv_rec.CREATION_DATE;
1316 l_svfv_rec.LAST_UPDATED_BY := Fnd_Global.USER_ID;
1318 RETURN(l_svfv_rec);
1319 END fill_who_columns;
1320 -------------------------------------------
1321 -- Set_Attributes for:OKL_SERVICE_FEES_V --
1322 -------------------------------------------
1323 FUNCTION Set_Attributes (
1324 p_svfv_rec IN svfv_rec_type,
1325 x_svfv_rec OUT NOCOPY svfv_rec_type
1326 ) RETURN VARCHAR2 IS
1327 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1328 l_org_id hr_operating_units.organization_id%TYPE;
1329
1330 BEGIN
1331
1332 --fnd_profile.get('ORG_ID', l_org_id);
1333
1334 l_org_id:= mo_global.get_current_org_id();
1335
1336 x_svfv_rec := p_svfv_rec;
1337 x_svfv_rec.OBJECT_VERSION_NUMBER := 1;
1338 x_svfv_rec.SFWT_FLAG := 'N';
1339 x_svfv_rec.organization_id := l_org_id;
1340 RETURN(l_return_status);
1341 END Set_Attributes;
1342 ------------------------------------------------------------------------
1343 -- New function to validate start date when inserting --
1344 -- Start date must be > todays date when inserting --
1345 ------------------------------------------------------------------------
1346
1347
1348
1349 FUNCTION Validate_Create_Start_Date (
1350 p_svfv_rec IN svfv_rec_type
1351 ) RETURN VARCHAR2 IS
1352 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1353
1354 BEGIN
1355
1356 IF TRUNC(p_svfv_rec.start_date) < TRUNC(SYSDATE)
1357 THEN
1358 OKL_API.set_message(G_APP_NAME, G_OKL_START_DATE);
1359 l_return_status := OKL_API.G_RET_STS_ERROR;
1360 END IF;
1361
1362 RETURN(l_return_status);
1363
1364 END Validate_Create_Start_Date;
1365
1366
1367 BEGIN
1368 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1369 G_PKG_NAME,
1370 p_init_msg_list,
1371 l_api_version,
1372 p_api_version,
1373 '_PVT',
1374 x_return_status);
1375 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1376 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1377 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1378 RAISE OKL_API.G_EXCEPTION_ERROR;
1379 END IF;
1380 l_svfv_rec := null_out_defaults(p_svfv_rec);
1381 -- Set primary key value
1382 l_svfv_rec.ID := get_seq_id;
1383 --- Setting item attributes
1384 l_return_status := Set_Attributes(
1385 l_svfv_rec, -- IN
1386 l_def_svfv_rec); -- OUT
1387 --- If any errors happen abort API
1388 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1389 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1390 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1391 RAISE OKL_API.G_EXCEPTION_ERROR;
1392 END IF;
1393 l_def_svfv_rec := fill_who_columns(l_def_svfv_rec);
1394
1395 --------------------------------------------------------
1396 -- Added Validation to Check start_date > todays date
1397 -- during an insert_row
1398 --------------------------------------------------------
1399
1400 l_return_status := Validate_Create_Start_Date(l_def_svfv_rec);
1401 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1402 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1403 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1404 RAISE OKL_API.G_EXCEPTION_ERROR;
1405 END IF;
1406
1407 --- Validate all non-missing attributes (Item Level Validation)
1408 l_return_status := Validate_Attributes(l_def_svfv_rec);
1409 --- If any errors happen abort API
1410 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1411 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1412 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1413 RAISE OKL_API.G_EXCEPTION_ERROR;
1414 END IF;
1415 l_return_status := Validate_Record(l_def_svfv_rec);
1416 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1417 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1418 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1419 RAISE OKL_API.G_EXCEPTION_ERROR;
1420 END IF;
1421
1422
1423 --------------------------------------
1424 -- Move VIEW record to "Child" records
1425 --------------------------------------
1426 migrate(l_def_svfv_rec, l_svf_rec);
1427 migrate(l_def_svfv_rec, l_okl_service_fees_tl_rec);
1428 --------------------------------------------
1429 -- Call the INSERT_ROW for each child record
1430 --------------------------------------------
1431 insert_row(
1432 p_init_msg_list,
1433 x_return_status,
1434 x_msg_count,
1435 x_msg_data,
1436 l_svf_rec,
1437 lx_svf_rec
1438 );
1439 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1440 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1441 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1442 RAISE OKL_API.G_EXCEPTION_ERROR;
1443 END IF;
1444 migrate(lx_svf_rec, l_def_svfv_rec);
1445 insert_row(
1446 p_init_msg_list,
1447 x_return_status,
1448 x_msg_count,
1449 x_msg_data,
1450 l_okl_service_fees_tl_rec,
1451 lx_okl_service_fees_tl_rec
1452 );
1456 RAISE OKL_API.G_EXCEPTION_ERROR;
1453 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1454 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1455 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1457 END IF;
1458 migrate(lx_okl_service_fees_tl_rec, l_def_svfv_rec);
1459 -- Set OUT values
1460 x_svfv_rec := l_def_svfv_rec;
1461 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1462 EXCEPTION
1463 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1464 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1465 (
1466 l_api_name,
1467 G_PKG_NAME,
1468 'OKL_API.G_RET_STS_ERROR',
1469 x_msg_count,
1470 x_msg_data,
1471 '_PVT'
1472 );
1473 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1474 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1475 (
1476 l_api_name,
1477 G_PKG_NAME,
1478 'OKL_API.G_RET_STS_UNEXP_ERROR',
1479 x_msg_count,
1480 x_msg_data,
1481 '_PVT'
1482 );
1483 WHEN OTHERS THEN
1484 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1485 (
1486 l_api_name,
1487 G_PKG_NAME,
1488 'OTHERS',
1489 x_msg_count,
1490 x_msg_data,
1491 '_PVT'
1492 );
1493 END insert_row;
1494 ----------------------------------------
1495 -- PL/SQL TBL insert_row for:SVFV_TBL --
1496 ----------------------------------------
1497 PROCEDURE insert_row(
1498 p_api_version IN NUMBER,
1499 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1500 x_return_status OUT NOCOPY VARCHAR2,
1501 x_msg_count OUT NOCOPY NUMBER,
1502 x_msg_data OUT NOCOPY VARCHAR2,
1503 p_svfv_tbl IN svfv_tbl_type,
1504 x_svfv_tbl OUT NOCOPY svfv_tbl_type) IS
1505
1506 l_api_version CONSTANT NUMBER := 1;
1507 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1508 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1509 i NUMBER := 0;
1510 BEGIN
1511 OKL_API.init_msg_list(p_init_msg_list);
1512 -- Make sure PL/SQL table has records in it before passing
1513 IF (p_svfv_tbl.COUNT > 0) THEN
1514 i := p_svfv_tbl.FIRST;
1515 LOOP
1516 insert_row (
1517 p_api_version => p_api_version,
1518 p_init_msg_list => OKL_API.G_FALSE,
1519 x_return_status => x_return_status,
1520 x_msg_count => x_msg_count,
1521 x_msg_data => x_msg_data,
1522 p_svfv_rec => p_svfv_tbl(i),
1523 x_svfv_rec => x_svfv_tbl(i));
1524 EXIT WHEN (i = p_svfv_tbl.LAST);
1525 i := p_svfv_tbl.NEXT(i);
1526 END LOOP;
1527 END IF;
1528 EXCEPTION
1529 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1530 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1531 (
1532 l_api_name,
1533 G_PKG_NAME,
1534 'OKL_API.G_RET_STS_ERROR',
1535 x_msg_count,
1536 x_msg_data,
1537 '_PVT'
1538 );
1539 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1540 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1541 (
1542 l_api_name,
1543 G_PKG_NAME,
1544 'OKL_API.G_RET_STS_UNEXP_ERROR',
1545 x_msg_count,
1546 x_msg_data,
1547 '_PVT'
1548 );
1549 WHEN OTHERS THEN
1550 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1551 (
1552 l_api_name,
1553 G_PKG_NAME,
1554 'OTHERS',
1555 x_msg_count,
1556 x_msg_data,
1557 '_PVT'
1558 );
1559 END insert_row;
1560
1561 ---------------------------------------------------------------------------
1562 -- PROCEDURE lock_row
1563 ---------------------------------------------------------------------------
1564 -------------------------------------
1565 -- lock_row for:OKL_SERVICE_FEES_B --
1566 -------------------------------------
1567 PROCEDURE lock_row(
1568 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1569 x_return_status OUT NOCOPY VARCHAR2,
1570 x_msg_count OUT NOCOPY NUMBER,
1571 x_msg_data OUT NOCOPY VARCHAR2,
1572 p_svf_rec IN svf_rec_type) IS
1573
1574 E_Resource_Busy EXCEPTION;
1575 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1576 CURSOR lock_csr (p_svf_rec IN svf_rec_type) IS
1577 SELECT OBJECT_VERSION_NUMBER
1578 FROM OKL_SERVICE_FEES_B
1579 WHERE ID = p_svf_rec.id
1580 AND OBJECT_VERSION_NUMBER = p_svf_rec.object_version_number
1581 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1582
1583 CURSOR lchk_csr (p_svf_rec IN svf_rec_type) IS
1584 SELECT OBJECT_VERSION_NUMBER
1585 FROM OKL_SERVICE_FEES_B
1586 WHERE ID = p_svf_rec.id;
1587 l_api_version CONSTANT NUMBER := 1;
1588 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1589 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1590 l_object_version_number OKL_SERVICE_FEES_B.OBJECT_VERSION_NUMBER%TYPE;
1591 lc_object_version_number OKL_SERVICE_FEES_B.OBJECT_VERSION_NUMBER%TYPE;
1592 l_row_notfound BOOLEAN := FALSE;
1593 lc_row_notfound BOOLEAN := FALSE;
1594 BEGIN
1595 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1599 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1596 p_init_msg_list,
1597 '_PVT',
1598 x_return_status);
1600 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1601 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1602 RAISE OKL_API.G_EXCEPTION_ERROR;
1603 END IF;
1604 BEGIN
1605 OPEN lock_csr(p_svf_rec);
1606 FETCH lock_csr INTO l_object_version_number;
1607 l_row_notfound := lock_csr%NOTFOUND;
1608 CLOSE lock_csr;
1609 EXCEPTION
1610 WHEN E_Resource_Busy THEN
1611 IF (lock_csr%ISOPEN) THEN
1612 CLOSE lock_csr;
1613 END IF;
1614 OKL_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1615 RAISE App_Exceptions.RECORD_LOCK_EXCEPTION;
1616 END;
1617
1618 IF ( l_row_notfound ) THEN
1619 OPEN lchk_csr(p_svf_rec);
1620 FETCH lchk_csr INTO lc_object_version_number;
1621 lc_row_notfound := lchk_csr%NOTFOUND;
1622 CLOSE lchk_csr;
1623 END IF;
1624 IF (lc_row_notfound) THEN
1625 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1626 RAISE OKL_API.G_EXCEPTION_ERROR;
1627 ELSIF lc_object_version_number > p_svf_rec.object_version_number THEN
1628 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1629 RAISE OKL_API.G_EXCEPTION_ERROR;
1630 ELSIF lc_object_version_number <> p_svf_rec.object_version_number THEN
1631 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1632 RAISE OKL_API.G_EXCEPTION_ERROR;
1633 ELSIF lc_object_version_number = -1 THEN
1634 OKL_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1635 RAISE OKL_API.G_EXCEPTION_ERROR;
1636 END IF;
1637 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1638 EXCEPTION
1639 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1640 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1641 (
1642 l_api_name,
1643 G_PKG_NAME,
1644 'OKL_API.G_RET_STS_ERROR',
1645 x_msg_count,
1646 x_msg_data,
1647 '_PVT'
1648 );
1649 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1650 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1651 (
1652 l_api_name,
1653 G_PKG_NAME,
1654 'OKL_API.G_RET_STS_UNEXP_ERROR',
1655 x_msg_count,
1656 x_msg_data,
1657 '_PVT'
1658 );
1659 WHEN OTHERS THEN
1660 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1661 (
1662 l_api_name,
1663 G_PKG_NAME,
1664 'OTHERS',
1665 x_msg_count,
1666 x_msg_data,
1667 '_PVT'
1668 );
1669 END lock_row;
1670 --------------------------------------
1671 -- lock_row for:OKL_SERVICE_FEES_TL --
1672 --------------------------------------
1673 PROCEDURE lock_row(
1674 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1675 x_return_status OUT NOCOPY VARCHAR2,
1676 x_msg_count OUT NOCOPY NUMBER,
1677 x_msg_data OUT NOCOPY VARCHAR2,
1678 p_okl_service_fees_tl_rec IN okl_service_fees_tl_rec_type) IS
1679
1680 E_Resource_Busy EXCEPTION;
1681 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1682 CURSOR lock_csr (p_okl_service_fees_tl_rec IN okl_service_fees_tl_rec_type) IS
1683 SELECT *
1684 FROM OKL_SERVICE_FEES_TL
1685 WHERE ID = p_okl_service_fees_tl_rec.id
1686 FOR UPDATE NOWAIT;
1687
1688 l_api_version CONSTANT NUMBER := 1;
1689 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
1690 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1691 l_lock_var lock_csr%ROWTYPE;
1692 l_row_notfound BOOLEAN := FALSE;
1693 lc_row_notfound BOOLEAN := FALSE;
1694 BEGIN
1695 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1696 p_init_msg_list,
1697 '_PVT',
1698 x_return_status);
1699 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1700 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1701 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1702 RAISE OKL_API.G_EXCEPTION_ERROR;
1703 END IF;
1704 BEGIN
1705 OPEN lock_csr(p_okl_service_fees_tl_rec);
1706 FETCH lock_csr INTO l_lock_var;
1707 l_row_notfound := lock_csr%NOTFOUND;
1708 CLOSE lock_csr;
1709 EXCEPTION
1710 WHEN E_Resource_Busy THEN
1711 IF (lock_csr%ISOPEN) THEN
1712 CLOSE lock_csr;
1713 END IF;
1714 OKL_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1715 RAISE App_Exceptions.RECORD_LOCK_EXCEPTION;
1716 END;
1717
1718 IF ( l_row_notfound ) THEN
1719 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1720 RAISE OKL_API.G_EXCEPTION_ERROR;
1721 END IF;
1722 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1723 EXCEPTION
1724 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1725 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1726 (
1727 l_api_name,
1728 G_PKG_NAME,
1729 'OKL_API.G_RET_STS_ERROR',
1730 x_msg_count,
1731 x_msg_data,
1732 '_PVT'
1733 );
1734 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1735 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1736 (
1737 l_api_name,
1738 G_PKG_NAME,
1739 'OKL_API.G_RET_STS_UNEXP_ERROR',
1740 x_msg_count,
1744 WHEN OTHERS THEN
1741 x_msg_data,
1742 '_PVT'
1743 );
1745 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1746 (
1747 l_api_name,
1748 G_PKG_NAME,
1749 'OTHERS',
1750 x_msg_count,
1751 x_msg_data,
1752 '_PVT'
1753 );
1754 END lock_row;
1755 -------------------------------------
1756 -- lock_row for:OKL_SERVICE_FEES_V --
1757 -------------------------------------
1758 PROCEDURE lock_row(
1759 p_api_version IN NUMBER,
1760 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1761 x_return_status OUT NOCOPY VARCHAR2,
1762 x_msg_count OUT NOCOPY NUMBER,
1763 x_msg_data OUT NOCOPY VARCHAR2,
1764 p_svfv_rec IN svfv_rec_type) IS
1765
1766 l_api_version CONSTANT NUMBER := 1;
1767 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1768 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1769 l_svf_rec svf_rec_type;
1770 l_okl_service_fees_tl_rec okl_service_fees_tl_rec_type;
1771 BEGIN
1772 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1773 G_PKG_NAME,
1774 p_init_msg_list,
1775 l_api_version,
1776 p_api_version,
1777 '_PVT',
1778 x_return_status);
1779 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1780 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1781 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1782 RAISE OKL_API.G_EXCEPTION_ERROR;
1783 END IF;
1784 --------------------------------------
1785 -- Move VIEW record to "Child" records
1786 --------------------------------------
1787 migrate(p_svfv_rec, l_svf_rec);
1788 migrate(p_svfv_rec, l_okl_service_fees_tl_rec);
1789 --------------------------------------------
1790 -- Call the LOCK_ROW for each child record
1791 --------------------------------------------
1792 lock_row(
1793 p_init_msg_list,
1794 x_return_status,
1795 x_msg_count,
1796 x_msg_data,
1797 l_svf_rec
1798 );
1799 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1800 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1801 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1802 RAISE OKL_API.G_EXCEPTION_ERROR;
1803 END IF;
1804 lock_row(
1805 p_init_msg_list,
1806 x_return_status,
1807 x_msg_count,
1808 x_msg_data,
1809 l_okl_service_fees_tl_rec
1810 );
1811 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1812 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1813 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
1814 RAISE OKL_API.G_EXCEPTION_ERROR;
1815 END IF;
1816 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1817 EXCEPTION
1818 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1819 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1820 (
1821 l_api_name,
1822 G_PKG_NAME,
1823 'OKL_API.G_RET_STS_ERROR',
1824 x_msg_count,
1825 x_msg_data,
1826 '_PVT'
1827 );
1828 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1829 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1830 (
1831 l_api_name,
1832 G_PKG_NAME,
1833 'OKL_API.G_RET_STS_UNEXP_ERROR',
1834 x_msg_count,
1835 x_msg_data,
1836 '_PVT'
1837 );
1838 WHEN OTHERS THEN
1839 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1840 (
1841 l_api_name,
1842 G_PKG_NAME,
1843 'OTHERS',
1844 x_msg_count,
1845 x_msg_data,
1846 '_PVT'
1847 );
1848 END lock_row;
1849 --------------------------------------
1850 -- PL/SQL TBL lock_row for:SVFV_TBL --
1851 --------------------------------------
1852 PROCEDURE lock_row(
1853 p_api_version IN NUMBER,
1854 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1855 x_return_status OUT NOCOPY VARCHAR2,
1856 x_msg_count OUT NOCOPY NUMBER,
1857 x_msg_data OUT NOCOPY VARCHAR2,
1858 p_svfv_tbl IN svfv_tbl_type) IS
1859
1860 l_api_version CONSTANT NUMBER := 1;
1861 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1862 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1863 i NUMBER := 0;
1864 BEGIN
1865 OKL_API.init_msg_list(p_init_msg_list);
1866 -- Make sure PL/SQL table has records in it before passing
1867 IF (p_svfv_tbl.COUNT > 0) THEN
1868 i := p_svfv_tbl.FIRST;
1869 LOOP
1870 lock_row (
1871 p_api_version => p_api_version,
1872 p_init_msg_list => OKL_API.G_FALSE,
1873 x_return_status => x_return_status,
1874 x_msg_count => x_msg_count,
1875 x_msg_data => x_msg_data,
1876 p_svfv_rec => p_svfv_tbl(i));
1877 EXIT WHEN (i = p_svfv_tbl.LAST);
1878 i := p_svfv_tbl.NEXT(i);
1879 END LOOP;
1880 END IF;
1881 EXCEPTION
1882 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1883 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1884 (
1885 l_api_name,
1889 x_msg_data,
1886 G_PKG_NAME,
1887 'OKL_API.G_RET_STS_ERROR',
1888 x_msg_count,
1890 '_PVT'
1891 );
1892 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1893 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1894 (
1895 l_api_name,
1896 G_PKG_NAME,
1897 'OKL_API.G_RET_STS_UNEXP_ERROR',
1898 x_msg_count,
1899 x_msg_data,
1900 '_PVT'
1901 );
1902 WHEN OTHERS THEN
1903 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
1904 (
1905 l_api_name,
1906 G_PKG_NAME,
1907 'OTHERS',
1908 x_msg_count,
1909 x_msg_data,
1910 '_PVT'
1911 );
1912 END lock_row;
1913
1914 ---------------------------------------------------------------------------
1915 -- PROCEDURE update_row
1916 ---------------------------------------------------------------------------
1917 ---------------------------------------
1918 -- update_row for:OKL_SERVICE_FEES_B --
1919 ---------------------------------------
1920 PROCEDURE update_row(
1921 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1922 x_return_status OUT NOCOPY VARCHAR2,
1923 x_msg_count OUT NOCOPY NUMBER,
1924 x_msg_data OUT NOCOPY VARCHAR2,
1925 p_svf_rec IN svf_rec_type,
1926 x_svf_rec OUT NOCOPY svf_rec_type) IS
1927
1928 l_api_version CONSTANT NUMBER := 1;
1929 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
1930 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1931 l_svf_rec svf_rec_type := p_svf_rec;
1932 l_def_svf_rec svf_rec_type;
1933 l_row_notfound BOOLEAN := TRUE;
1934 ----------------------------------
1935 -- FUNCTION populate_new_record --
1936 ----------------------------------
1937 FUNCTION populate_new_record (
1938 p_svf_rec IN svf_rec_type,
1939 x_svf_rec OUT NOCOPY svf_rec_type
1940 ) RETURN VARCHAR2 IS
1941 l_svf_rec svf_rec_type;
1942 l_row_notfound BOOLEAN := TRUE;
1943 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1944 BEGIN
1945 x_svf_rec := p_svf_rec;
1946 -- Get current database values
1947 l_svf_rec := get_rec(p_svf_rec, l_row_notfound);
1948 IF (l_row_notfound) THEN
1949 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1950 END IF;
1951 IF (x_svf_rec.id = OKL_API.G_MISS_NUM)
1952 THEN
1953 x_svf_rec.id := l_svf_rec.id;
1954 END IF;
1955 IF (x_svf_rec.srv_code = OKL_API.G_MISS_CHAR)
1956 THEN
1957 x_svf_rec.srv_code := l_svf_rec.srv_code;
1958 END IF;
1959 IF (x_svf_rec.object_version_number = OKL_API.G_MISS_NUM)
1960 THEN
1961 x_svf_rec.object_version_number := l_svf_rec.object_version_number;
1962 END IF;
1963 IF (x_svf_rec.amount = OKL_API.G_MISS_NUM)
1964 THEN
1965 x_svf_rec.amount := l_svf_rec.amount;
1966 END IF;
1967 IF (x_svf_rec.start_date = OKL_API.G_MISS_DATE)
1968 THEN
1969 x_svf_rec.start_date := l_svf_rec.start_date;
1970 END IF;
1971 IF (x_svf_rec.end_date = OKL_API.G_MISS_DATE)
1972 THEN
1973 x_svf_rec.end_date := l_svf_rec.end_date;
1974 END IF;
1975 IF (x_svf_rec.organization_id = OKL_API.G_MISS_NUM)
1976 THEN
1977 x_svf_rec.organization_id := l_svf_rec.organization_id;
1978 END IF;
1979 IF (x_svf_rec.attribute_category = OKL_API.G_MISS_CHAR)
1980 THEN
1981 x_svf_rec.attribute_category := l_svf_rec.attribute_category;
1982 END IF;
1983 IF (x_svf_rec.attribute1 = OKL_API.G_MISS_CHAR)
1984 THEN
1985 x_svf_rec.attribute1 := l_svf_rec.attribute1;
1986 END IF;
1987 IF (x_svf_rec.attribute2 = OKL_API.G_MISS_CHAR)
1988 THEN
1989 x_svf_rec.attribute2 := l_svf_rec.attribute2;
1990 END IF;
1991 IF (x_svf_rec.attribute3 = OKL_API.G_MISS_CHAR)
1992 THEN
1993 x_svf_rec.attribute3 := l_svf_rec.attribute3;
1994 END IF;
1995 IF (x_svf_rec.attribute4 = OKL_API.G_MISS_CHAR)
1996 THEN
1997 x_svf_rec.attribute4 := l_svf_rec.attribute4;
1998 END IF;
1999 IF (x_svf_rec.attribute5 = OKL_API.G_MISS_CHAR)
2000 THEN
2001 x_svf_rec.attribute5 := l_svf_rec.attribute5;
2002 END IF;
2003 IF (x_svf_rec.attribute6 = OKL_API.G_MISS_CHAR)
2004 THEN
2005 x_svf_rec.attribute6 := l_svf_rec.attribute6;
2006 END IF;
2007 IF (x_svf_rec.attribute7 = OKL_API.G_MISS_CHAR)
2008 THEN
2009 x_svf_rec.attribute7 := l_svf_rec.attribute7;
2010 END IF;
2011 IF (x_svf_rec.attribute8 = OKL_API.G_MISS_CHAR)
2012 THEN
2013 x_svf_rec.attribute8 := l_svf_rec.attribute8;
2014 END IF;
2015 IF (x_svf_rec.attribute9 = OKL_API.G_MISS_CHAR)
2016 THEN
2017 x_svf_rec.attribute9 := l_svf_rec.attribute9;
2018 END IF;
2019 IF (x_svf_rec.attribute10 = OKL_API.G_MISS_CHAR)
2020 THEN
2021 x_svf_rec.attribute10 := l_svf_rec.attribute10;
2022 END IF;
2023 IF (x_svf_rec.attribute11 = OKL_API.G_MISS_CHAR)
2024 THEN
2025 x_svf_rec.attribute11 := l_svf_rec.attribute11;
2026 END IF;
2027 IF (x_svf_rec.attribute12 = OKL_API.G_MISS_CHAR)
2028 THEN
2029 x_svf_rec.attribute12 := l_svf_rec.attribute12;
2030 END IF;
2031 IF (x_svf_rec.attribute13 = OKL_API.G_MISS_CHAR)
2035 IF (x_svf_rec.attribute14 = OKL_API.G_MISS_CHAR)
2032 THEN
2033 x_svf_rec.attribute13 := l_svf_rec.attribute13;
2034 END IF;
2036 THEN
2037 x_svf_rec.attribute14 := l_svf_rec.attribute14;
2038 END IF;
2039 IF (x_svf_rec.attribute15 = OKL_API.G_MISS_CHAR)
2040 THEN
2041 x_svf_rec.attribute15 := l_svf_rec.attribute15;
2042 END IF;
2043 IF (x_svf_rec.created_by = OKL_API.G_MISS_NUM)
2044 THEN
2045 x_svf_rec.created_by := l_svf_rec.created_by;
2046 END IF;
2047 IF (x_svf_rec.creation_date = OKL_API.G_MISS_DATE)
2048 THEN
2049 x_svf_rec.creation_date := l_svf_rec.creation_date;
2050 END IF;
2051 IF (x_svf_rec.last_updated_by = OKL_API.G_MISS_NUM)
2052 THEN
2053 x_svf_rec.last_updated_by := l_svf_rec.last_updated_by;
2054 END IF;
2055 IF (x_svf_rec.last_update_date = OKL_API.G_MISS_DATE)
2056 THEN
2057 x_svf_rec.last_update_date := l_svf_rec.last_update_date;
2058 END IF;
2059 IF (x_svf_rec.last_update_login = OKL_API.G_MISS_NUM)
2060 THEN
2061 x_svf_rec.last_update_login := l_svf_rec.last_update_login;
2062 END IF;
2063 IF (x_svf_rec.org_id = OKL_API.G_MISS_NUM)
2064 THEN
2065 x_svf_rec.org_id := l_svf_rec.organization_id;
2066 END IF;
2067
2068 RETURN(l_return_status);
2069 END populate_new_record;
2070 -------------------------------------------
2071 -- Set_Attributes for:OKL_SERVICE_FEES_B --
2072 -------------------------------------------
2073 FUNCTION Set_Attributes (
2074 p_svf_rec IN svf_rec_type,
2075 x_svf_rec OUT NOCOPY svf_rec_type
2076 ) RETURN VARCHAR2 IS
2077 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2078 BEGIN
2079 x_svf_rec := p_svf_rec;
2080 RETURN(l_return_status);
2081 END Set_Attributes;
2082 BEGIN
2083 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2084 p_init_msg_list,
2085 '_PVT',
2086 x_return_status);
2087 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2088 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2089 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2090 RAISE OKL_API.G_EXCEPTION_ERROR;
2091 END IF;
2092 --- Setting item attributes
2093 l_return_status := Set_Attributes(
2094 p_svf_rec, -- IN
2095 l_svf_rec); -- OUT
2096 --- If any errors happen abort API
2097 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2098 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2099 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2100 RAISE OKL_API.G_EXCEPTION_ERROR;
2101 END IF;
2102 l_return_status := populate_new_record(l_svf_rec, l_def_svf_rec);
2103 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2104 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2105 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2106 RAISE OKL_API.G_EXCEPTION_ERROR;
2107 END IF;
2108 UPDATE OKL_SERVICE_FEES_B
2109 SET SRV_CODE = l_def_svf_rec.srv_code,
2110 OBJECT_VERSION_NUMBER = l_def_svf_rec.object_version_number,
2111 AMOUNT = l_def_svf_rec.amount,
2112 START_DATE = l_def_svf_rec.start_date,
2113 END_DATE = l_def_svf_rec.end_date,
2114 ORGANIZATION_ID = l_def_svf_rec.organization_id,
2115 ATTRIBUTE_CATEGORY = l_def_svf_rec.attribute_category,
2116 ATTRIBUTE1 = l_def_svf_rec.attribute1,
2117 ATTRIBUTE2 = l_def_svf_rec.attribute2,
2118 ATTRIBUTE3 = l_def_svf_rec.attribute3,
2119 ATTRIBUTE4 = l_def_svf_rec.attribute4,
2120 ATTRIBUTE5 = l_def_svf_rec.attribute5,
2121 ATTRIBUTE6 = l_def_svf_rec.attribute6,
2122 ATTRIBUTE7 = l_def_svf_rec.attribute7,
2123 ATTRIBUTE8 = l_def_svf_rec.attribute8,
2124 ATTRIBUTE9 = l_def_svf_rec.attribute9,
2125 ATTRIBUTE10 = l_def_svf_rec.attribute10,
2126 ATTRIBUTE11 = l_def_svf_rec.attribute11,
2127 ATTRIBUTE12 = l_def_svf_rec.attribute12,
2128 ATTRIBUTE13 = l_def_svf_rec.attribute13,
2129 ATTRIBUTE14 = l_def_svf_rec.attribute14,
2130 ATTRIBUTE15 = l_def_svf_rec.attribute15,
2134 LAST_UPDATE_DATE = l_def_svf_rec.last_update_date,
2131 CREATED_BY = l_def_svf_rec.created_by,
2132 CREATION_DATE = l_def_svf_rec.creation_date,
2133 LAST_UPDATED_BY = l_def_svf_rec.last_updated_by,
2135 LAST_UPDATE_LOGIN = l_def_svf_rec.last_update_login,
2136 ORG_ID=l_def_svf_rec.organization_id
2137
2138 WHERE ID = l_def_svf_rec.id;
2139
2140 x_svf_rec := l_def_svf_rec;
2141 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2142 EXCEPTION
2146 l_api_name,
2143 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2144 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2145 (
2147 G_PKG_NAME,
2148 'OKL_API.G_RET_STS_ERROR',
2149 x_msg_count,
2150 x_msg_data,
2151 '_PVT'
2152 );
2153 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2154 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2155 (
2156 l_api_name,
2157 G_PKG_NAME,
2158 'OKL_API.G_RET_STS_UNEXP_ERROR',
2159 x_msg_count,
2160 x_msg_data,
2161 '_PVT'
2162 );
2163 WHEN OTHERS THEN
2164 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2165 (
2166 l_api_name,
2167 G_PKG_NAME,
2168 'OTHERS',
2169 x_msg_count,
2170 x_msg_data,
2171 '_PVT'
2172 );
2173 END update_row;
2174 ----------------------------------------
2175 -- update_row for:OKL_SERVICE_FEES_TL --
2176 ----------------------------------------
2177 PROCEDURE update_row(
2178 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2179 x_return_status OUT NOCOPY VARCHAR2,
2180 x_msg_count OUT NOCOPY NUMBER,
2181 x_msg_data OUT NOCOPY VARCHAR2,
2182 p_okl_service_fees_tl_rec IN okl_service_fees_tl_rec_type,
2183 x_okl_service_fees_tl_rec OUT NOCOPY okl_service_fees_tl_rec_type) IS
2184
2185 l_api_version CONSTANT NUMBER := 1;
2186 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
2187 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2188 l_okl_service_fees_tl_rec okl_service_fees_tl_rec_type := p_okl_service_fees_tl_rec;
2189 ldefoklservicefeestlrec okl_service_fees_tl_rec_type;
2190 l_row_notfound BOOLEAN := TRUE;
2191 ----------------------------------
2192 -- FUNCTION populate_new_record --
2193 ----------------------------------
2194 FUNCTION populate_new_record (
2195 p_okl_service_fees_tl_rec IN okl_service_fees_tl_rec_type,
2196 x_okl_service_fees_tl_rec OUT NOCOPY okl_service_fees_tl_rec_type
2197 ) RETURN VARCHAR2 IS
2198 l_okl_service_fees_tl_rec okl_service_fees_tl_rec_type;
2199 l_row_notfound BOOLEAN := TRUE;
2200 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2201 BEGIN
2202 x_okl_service_fees_tl_rec := p_okl_service_fees_tl_rec;
2203 -- Get current database values
2204 l_okl_service_fees_tl_rec := get_rec(p_okl_service_fees_tl_rec, l_row_notfound);
2205 IF (l_row_notfound) THEN
2206 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2207 END IF;
2208 IF (x_okl_service_fees_tl_rec.id = OKL_API.G_MISS_NUM)
2209 THEN
2210 x_okl_service_fees_tl_rec.id := l_okl_service_fees_tl_rec.id;
2211 END IF;
2212 IF (x_okl_service_fees_tl_rec.LANGUAGE = OKL_API.G_MISS_CHAR)
2213 THEN
2214 x_okl_service_fees_tl_rec.LANGUAGE := l_okl_service_fees_tl_rec.LANGUAGE;
2215 END IF;
2216 IF (x_okl_service_fees_tl_rec.source_lang = OKL_API.G_MISS_CHAR)
2217 THEN
2218 x_okl_service_fees_tl_rec.source_lang := l_okl_service_fees_tl_rec.source_lang;
2219 END IF;
2220 IF (x_okl_service_fees_tl_rec.sfwt_flag = OKL_API.G_MISS_CHAR)
2221 THEN
2222 x_okl_service_fees_tl_rec.sfwt_flag := l_okl_service_fees_tl_rec.sfwt_flag;
2223 END IF;
2224 IF (x_okl_service_fees_tl_rec.name = OKL_API.G_MISS_CHAR)
2225 THEN
2226 x_okl_service_fees_tl_rec.name := l_okl_service_fees_tl_rec.name;
2227 END IF;
2228 IF (x_okl_service_fees_tl_rec.description = OKL_API.G_MISS_CHAR)
2229 THEN
2230 x_okl_service_fees_tl_rec.description := l_okl_service_fees_tl_rec.description;
2231 END IF;
2232 IF (x_okl_service_fees_tl_rec.created_by = OKL_API.G_MISS_NUM)
2233 THEN
2234 x_okl_service_fees_tl_rec.created_by := l_okl_service_fees_tl_rec.created_by;
2235 END IF;
2236 IF (x_okl_service_fees_tl_rec.creation_date = OKL_API.G_MISS_DATE)
2237 THEN
2238 x_okl_service_fees_tl_rec.creation_date := l_okl_service_fees_tl_rec.creation_date;
2239 END IF;
2240 IF (x_okl_service_fees_tl_rec.last_updated_by = OKL_API.G_MISS_NUM)
2241 THEN
2242 x_okl_service_fees_tl_rec.last_updated_by := l_okl_service_fees_tl_rec.last_updated_by;
2243 END IF;
2244 IF (x_okl_service_fees_tl_rec.last_update_date = OKL_API.G_MISS_DATE)
2245 THEN
2246 x_okl_service_fees_tl_rec.last_update_date := l_okl_service_fees_tl_rec.last_update_date;
2247 END IF;
2248 IF (x_okl_service_fees_tl_rec.last_update_login = OKL_API.G_MISS_NUM)
2249 THEN
2250 x_okl_service_fees_tl_rec.last_update_login := l_okl_service_fees_tl_rec.last_update_login;
2251 END IF;
2252 RETURN(l_return_status);
2253 END populate_new_record;
2254 --------------------------------------------
2255 -- Set_Attributes for:OKL_SERVICE_FEES_TL --
2256 --------------------------------------------
2257 FUNCTION Set_Attributes (
2258 p_okl_service_fees_tl_rec IN okl_service_fees_tl_rec_type,
2259 x_okl_service_fees_tl_rec OUT NOCOPY okl_service_fees_tl_rec_type
2260 ) RETURN VARCHAR2 IS
2261 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2262 BEGIN
2263 x_okl_service_fees_tl_rec := p_okl_service_fees_tl_rec;
2264 x_okl_service_fees_tl_rec.LANGUAGE := USERENV('LANG');
2265 x_okl_service_fees_tl_rec.SOURCE_LANG := USERENV('LANG');
2269 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2266 RETURN(l_return_status);
2267 END Set_Attributes;
2268 BEGIN
2270 p_init_msg_list,
2271 '_PVT',
2272 x_return_status);
2273 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2274 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2275 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2276 RAISE OKL_API.G_EXCEPTION_ERROR;
2277 END IF;
2278 --- Setting item attributes
2282 --- If any errors happen abort API
2279 l_return_status := Set_Attributes(
2280 p_okl_service_fees_tl_rec, -- IN
2281 l_okl_service_fees_tl_rec); -- OUT
2283 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2284 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2285 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2286 RAISE OKL_API.G_EXCEPTION_ERROR;
2287 END IF;
2288 l_return_status := populate_new_record(l_okl_service_fees_tl_rec, ldefoklservicefeestlrec);
2289 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2290 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2291 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2292 RAISE OKL_API.G_EXCEPTION_ERROR;
2293 END IF;
2294 UPDATE OKL_SERVICE_FEES_TL
2295 SET NAME = ldefoklservicefeestlrec.name,
2296 DESCRIPTION = ldefoklservicefeestlrec.description,
2297 SOURCE_LANG = ldefoklservicefeestlrec.source_lang,--Fix for 3637102
2298 CREATED_BY = ldefoklservicefeestlrec.created_by,
2299 CREATION_DATE = ldefoklservicefeestlrec.creation_date,
2300 LAST_UPDATED_BY = ldefoklservicefeestlrec.last_updated_by,
2301 LAST_UPDATE_DATE = ldefoklservicefeestlrec.last_update_date,
2302 LAST_UPDATE_LOGIN = ldefoklservicefeestlrec.last_update_login
2303 WHERE ID = ldefoklservicefeestlrec.id
2304 AND USERENV('LANG') in (SOURCE_LANG,LANGUAGE);--Fix for bug 3637102
2305 --AND SOURCE_LANG = USERENV('LANG');
2306
2307 UPDATE OKL_SERVICE_FEES_TL
2308 SET SFWT_FLAG = 'Y'
2309 WHERE ID = ldefoklservicefeestlrec.id
2310 AND SOURCE_LANG <> USERENV('LANG');
2311
2312 x_okl_service_fees_tl_rec := ldefoklservicefeestlrec;
2313 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2314 EXCEPTION
2315 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2316 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2317 (
2318 l_api_name,
2319 G_PKG_NAME,
2320 'OKL_API.G_RET_STS_ERROR',
2321 x_msg_count,
2322 x_msg_data,
2323 '_PVT'
2324 );
2325 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2326 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2327 (
2328 l_api_name,
2329 G_PKG_NAME,
2330 'OKL_API.G_RET_STS_UNEXP_ERROR',
2331 x_msg_count,
2332 x_msg_data,
2333 '_PVT'
2334 );
2335 WHEN OTHERS THEN
2336 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2337 (
2338 l_api_name,
2339 G_PKG_NAME,
2340 'OTHERS',
2341 x_msg_count,
2342 x_msg_data,
2343 '_PVT'
2344 );
2345 END update_row;
2346 ---------------------------------------
2347 -- update_row for:OKL_SERVICE_FEES_V --
2348 ---------------------------------------
2349 PROCEDURE update_row(
2350 p_api_version IN NUMBER,
2351 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2352 x_return_status OUT NOCOPY VARCHAR2,
2353 x_msg_count OUT NOCOPY NUMBER,
2354 x_msg_data OUT NOCOPY VARCHAR2,
2355 p_svfv_rec IN svfv_rec_type,
2356 x_svfv_rec OUT NOCOPY svfv_rec_type) IS
2357
2358 l_api_version CONSTANT NUMBER := 1;
2359 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2360 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2361 l_svfv_rec svfv_rec_type := p_svfv_rec;
2362 l_def_svfv_rec svfv_rec_type;
2363 l_okl_service_fees_tl_rec okl_service_fees_tl_rec_type;
2364 lx_okl_service_fees_tl_rec okl_service_fees_tl_rec_type;
2365 l_svf_rec svf_rec_type;
2366 lx_svf_rec svf_rec_type;
2367 -------------------------------
2368 -- FUNCTION fill_who_columns --
2369 -------------------------------
2370 FUNCTION fill_who_columns (
2371 p_svfv_rec IN svfv_rec_type
2372 ) RETURN svfv_rec_type IS
2373 l_svfv_rec svfv_rec_type := p_svfv_rec;
2374 BEGIN
2375 l_svfv_rec.LAST_UPDATE_DATE := SYSDATE;
2376 l_svfv_rec.LAST_UPDATED_BY := Fnd_Global.USER_ID;
2377 l_svfv_rec.LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
2378 RETURN(l_svfv_rec);
2382 ----------------------------------
2379 END fill_who_columns;
2380 ----------------------------------
2381 -- FUNCTION populate_new_record --
2383 FUNCTION populate_new_record (
2384 p_svfv_rec IN svfv_rec_type,
2385 x_svfv_rec OUT NOCOPY svfv_rec_type
2386 ) RETURN VARCHAR2 IS
2387 l_svfv_rec svfv_rec_type;
2388 l_row_notfound BOOLEAN := TRUE;
2389 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2390 BEGIN
2391 x_svfv_rec := p_svfv_rec;
2392 -- Get current database values
2393 l_svfv_rec := get_rec(p_svfv_rec, l_row_notfound);
2394 IF (l_row_notfound) THEN
2395 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
2396 END IF;
2397 IF (x_svfv_rec.id = OKL_API.G_MISS_NUM)
2398 THEN
2399 x_svfv_rec.id := l_svfv_rec.id;
2400 END IF;
2401 IF (x_svfv_rec.object_version_number = OKL_API.G_MISS_NUM)
2402 THEN
2403 x_svfv_rec.object_version_number := l_svfv_rec.object_version_number;
2404 END IF;
2405 IF (x_svfv_rec.sfwt_flag = OKL_API.G_MISS_CHAR)
2406 THEN
2407 x_svfv_rec.sfwt_flag := l_svfv_rec.sfwt_flag;
2408 END IF;
2409 IF (x_svfv_rec.srv_code = OKL_API.G_MISS_CHAR)
2410 THEN
2411 x_svfv_rec.srv_code := l_svfv_rec.srv_code;
2412 END IF;
2413 IF (x_svfv_rec.name = OKL_API.G_MISS_CHAR)
2414 THEN
2415 x_svfv_rec.name := l_svfv_rec.name;
2416 END IF;
2417 IF (x_svfv_rec.description = OKL_API.G_MISS_CHAR)
2418 THEN
2419 x_svfv_rec.description := l_svfv_rec.description;
2420 END IF;
2421 IF (x_svfv_rec.amount = OKL_API.G_MISS_NUM)
2422 THEN
2423 x_svfv_rec.amount := l_svfv_rec.amount;
2424 END IF;
2425 IF (x_svfv_rec.start_date = OKL_API.G_MISS_DATE)
2426 THEN
2427 x_svfv_rec.start_date := l_svfv_rec.start_date;
2428 END IF;
2429 IF (x_svfv_rec.end_date = OKL_API.G_MISS_DATE)
2430 THEN
2431 x_svfv_rec.end_date := l_svfv_rec.end_date;
2432 END IF;
2433 IF (x_svfv_rec.organization_id = OKL_API.G_MISS_NUM)
2434 THEN
2435 x_svfv_rec.organization_id := l_svfv_rec.organization_id;
2436 END IF;
2437 IF (x_svfv_rec.attribute_category = OKL_API.G_MISS_CHAR)
2438 THEN
2439 x_svfv_rec.attribute_category := l_svfv_rec.attribute_category;
2440 END IF;
2441 IF (x_svfv_rec.attribute1 = OKL_API.G_MISS_CHAR)
2442 THEN
2443 x_svfv_rec.attribute1 := l_svfv_rec.attribute1;
2444 END IF;
2445 IF (x_svfv_rec.attribute2 = OKL_API.G_MISS_CHAR)
2446 THEN
2447 x_svfv_rec.attribute2 := l_svfv_rec.attribute2;
2448 END IF;
2449 IF (x_svfv_rec.attribute3 = OKL_API.G_MISS_CHAR)
2450 THEN
2451 x_svfv_rec.attribute3 := l_svfv_rec.attribute3;
2452 END IF;
2453 IF (x_svfv_rec.attribute4 = OKL_API.G_MISS_CHAR)
2454 THEN
2455 x_svfv_rec.attribute4 := l_svfv_rec.attribute4;
2456 END IF;
2457 IF (x_svfv_rec.attribute5 = OKL_API.G_MISS_CHAR)
2458 THEN
2459 x_svfv_rec.attribute5 := l_svfv_rec.attribute5;
2460 END IF;
2461 IF (x_svfv_rec.attribute6 = OKL_API.G_MISS_CHAR)
2462 THEN
2463 x_svfv_rec.attribute6 := l_svfv_rec.attribute6;
2464 END IF;
2465 IF (x_svfv_rec.attribute7 = OKL_API.G_MISS_CHAR)
2466 THEN
2467 x_svfv_rec.attribute7 := l_svfv_rec.attribute7;
2468 END IF;
2469 IF (x_svfv_rec.attribute8 = OKL_API.G_MISS_CHAR)
2470 THEN
2471 x_svfv_rec.attribute8 := l_svfv_rec.attribute8;
2472 END IF;
2473 IF (x_svfv_rec.attribute9 = OKL_API.G_MISS_CHAR)
2474 THEN
2475 x_svfv_rec.attribute9 := l_svfv_rec.attribute9;
2476 END IF;
2477 IF (x_svfv_rec.attribute10 = OKL_API.G_MISS_CHAR)
2478 THEN
2479 x_svfv_rec.attribute10 := l_svfv_rec.attribute10;
2480 END IF;
2481 IF (x_svfv_rec.attribute11 = OKL_API.G_MISS_CHAR)
2482 THEN
2483 x_svfv_rec.attribute11 := l_svfv_rec.attribute11;
2484 END IF;
2485 IF (x_svfv_rec.attribute12 = OKL_API.G_MISS_CHAR)
2489 IF (x_svfv_rec.attribute13 = OKL_API.G_MISS_CHAR)
2486 THEN
2487 x_svfv_rec.attribute12 := l_svfv_rec.attribute12;
2488 END IF;
2490 THEN
2491 x_svfv_rec.attribute13 := l_svfv_rec.attribute13;
2492 END IF;
2493 IF (x_svfv_rec.attribute14 = OKL_API.G_MISS_CHAR)
2494 THEN
2495 x_svfv_rec.attribute14 := l_svfv_rec.attribute14;
2496 END IF;
2497 IF (x_svfv_rec.attribute15 = OKL_API.G_MISS_CHAR)
2498 THEN
2499 x_svfv_rec.attribute15 := l_svfv_rec.attribute15;
2500 END IF;
2501 IF (x_svfv_rec.created_by = OKL_API.G_MISS_NUM)
2502 THEN
2503 x_svfv_rec.created_by := l_svfv_rec.created_by;
2504 END IF;
2505 IF (x_svfv_rec.creation_date = OKL_API.G_MISS_DATE)
2506 THEN
2507 x_svfv_rec.creation_date := l_svfv_rec.creation_date;
2508 END IF;
2509 IF (x_svfv_rec.last_updated_by = OKL_API.G_MISS_NUM)
2510 THEN
2511 x_svfv_rec.last_updated_by := l_svfv_rec.last_updated_by;
2512 END IF;
2513 IF (x_svfv_rec.last_update_date = OKL_API.G_MISS_DATE)
2514 THEN
2515 x_svfv_rec.last_update_date := l_svfv_rec.last_update_date;
2516 END IF;
2517 IF (x_svfv_rec.last_update_login = OKL_API.G_MISS_NUM)
2518 THEN
2519 x_svfv_rec.last_update_login := l_svfv_rec.last_update_login;
2520 END IF;
2521 RETURN(l_return_status);
2522 END populate_new_record;
2523 -------------------------------------------
2524 -- Set_Attributes for:OKL_SERVICE_FEES_V --
2525 -------------------------------------------
2526 FUNCTION Set_Attributes (
2527 p_svfv_rec IN svfv_rec_type,
2528 x_svfv_rec OUT NOCOPY svfv_rec_type
2529 ) RETURN VARCHAR2 IS
2530 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2531 BEGIN
2532 x_svfv_rec := p_svfv_rec;
2533 x_svfv_rec.OBJECT_VERSION_NUMBER := NVL(x_svfv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2534 x_svfv_rec.SFWT_FLAG := 'N';
2535
2536 RETURN(l_return_status);
2537 END Set_Attributes;
2538 BEGIN
2539 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2540 G_PKG_NAME,
2541 p_init_msg_list,
2542 l_api_version,
2543 p_api_version,
2544 '_PVT',
2545 x_return_status);
2546 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2547 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2548 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2549 RAISE OKL_API.G_EXCEPTION_ERROR;
2550 END IF;
2551 --- Setting item attributes
2552 l_return_status := Set_Attributes(
2553 p_svfv_rec, -- IN
2554 l_svfv_rec); -- OUT
2555 --- If any errors happen abort API
2556 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2557 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2558 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2559 RAISE OKL_API.G_EXCEPTION_ERROR;
2560 END IF;
2561 l_return_status := populate_new_record(l_svfv_rec, l_def_svfv_rec);
2562 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2563 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2564 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2565 RAISE OKL_API.G_EXCEPTION_ERROR;
2566 END IF;
2567 l_def_svfv_rec := fill_who_columns(l_def_svfv_rec);
2568 --- Validate all non-missing attributes (Item Level Validation)
2569 l_return_status := Validate_Attributes(l_def_svfv_rec);
2570 --- If any errors happen abort API
2571 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2572 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2573 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2574 RAISE OKL_API.G_EXCEPTION_ERROR;
2575 END IF;
2576 l_return_status := Validate_Record(l_def_svfv_rec);
2577 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2578 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2579 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2580 RAISE OKL_API.G_EXCEPTION_ERROR;
2581 END IF;
2582
2583
2584 --------------------------------------
2585 -- Move VIEW record to "Child" records
2586 --------------------------------------
2587 migrate(l_def_svfv_rec, l_okl_service_fees_tl_rec);
2588 migrate(l_def_svfv_rec, l_svf_rec);
2589 --------------------------------------------
2590 -- Call the UPDATE_ROW for each child record
2591 --------------------------------------------
2592 update_row(
2593 p_init_msg_list,
2594 x_return_status,
2595 x_msg_count,
2596 x_msg_data,
2597 l_okl_service_fees_tl_rec,
2598 lx_okl_service_fees_tl_rec
2599 );
2600 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2601 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2602 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2603 RAISE OKL_API.G_EXCEPTION_ERROR;
2604 END IF;
2605 migrate(lx_okl_service_fees_tl_rec, l_def_svfv_rec);
2606 update_row(
2607 p_init_msg_list,
2608 x_return_status,
2609 x_msg_count,
2610 x_msg_data,
2611 l_svf_rec,
2612 lx_svf_rec
2613 );
2614 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2615 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2616 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2617 RAISE OKL_API.G_EXCEPTION_ERROR;
2621 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2618 END IF;
2619 migrate(lx_svf_rec, l_def_svfv_rec);
2620 x_svfv_rec := l_def_svfv_rec;
2622 EXCEPTION
2623 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2624 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2625 (
2626 l_api_name,
2627 G_PKG_NAME,
2628 'OKL_API.G_RET_STS_ERROR',
2629 x_msg_count,
2630 x_msg_data,
2631 '_PVT'
2632 );
2633 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2634 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2635 (
2636 l_api_name,
2637 G_PKG_NAME,
2638 'OKL_API.G_RET_STS_UNEXP_ERROR',
2639 x_msg_count,
2640 x_msg_data,
2641 '_PVT'
2642 );
2643 WHEN OTHERS THEN
2644 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2645 (
2646 l_api_name,
2647 G_PKG_NAME,
2648 'OTHERS',
2649 x_msg_count,
2650 x_msg_data,
2651 '_PVT'
2652 );
2653 END update_row;
2654 ----------------------------------------
2655 -- PL/SQL TBL update_row for:SVFV_TBL --
2656 ----------------------------------------
2657 PROCEDURE update_row(
2658 p_api_version IN NUMBER,
2659 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2660 x_return_status OUT NOCOPY VARCHAR2,
2661 x_msg_count OUT NOCOPY NUMBER,
2662 x_msg_data OUT NOCOPY VARCHAR2,
2663 p_svfv_tbl IN svfv_tbl_type,
2664 x_svfv_tbl OUT NOCOPY svfv_tbl_type) IS
2665
2666 l_api_version CONSTANT NUMBER := 1;
2667 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2668 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2669 i NUMBER := 0;
2670 BEGIN
2671 OKL_API.init_msg_list(p_init_msg_list);
2672 -- Make sure PL/SQL table has records in it before passing
2673 IF (p_svfv_tbl.COUNT > 0) THEN
2674 i := p_svfv_tbl.FIRST;
2675 LOOP
2676 update_row (
2677 p_api_version => p_api_version,
2678 p_init_msg_list => OKL_API.G_FALSE,
2679 x_return_status => x_return_status,
2680 x_msg_count => x_msg_count,
2681 x_msg_data => x_msg_data,
2682 p_svfv_rec => p_svfv_tbl(i),
2683 x_svfv_rec => x_svfv_tbl(i));
2684 EXIT WHEN (i = p_svfv_tbl.LAST);
2685 i := p_svfv_tbl.NEXT(i);
2686 END LOOP;
2687 END IF;
2688 EXCEPTION
2689 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2690 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2691 (
2692 l_api_name,
2693 G_PKG_NAME,
2694 'OKL_API.G_RET_STS_ERROR',
2695 x_msg_count,
2696 x_msg_data,
2697 '_PVT'
2698 );
2699 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2700 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2701 (
2702 l_api_name,
2703 G_PKG_NAME,
2704 'OKL_API.G_RET_STS_UNEXP_ERROR',
2705 x_msg_count,
2706 x_msg_data,
2707 '_PVT'
2708 );
2709 WHEN OTHERS THEN
2710 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2711 (
2712 l_api_name,
2713 G_PKG_NAME,
2714 'OTHERS',
2715 x_msg_count,
2716 x_msg_data,
2717 '_PVT'
2718 );
2719 END update_row;
2720
2721 ---------------------------------------------------------------------------
2722 -- PROCEDURE delete_row
2723 ---------------------------------------------------------------------------
2724 ---------------------------------------
2725 -- delete_row for:OKL_SERVICE_FEES_B --
2726 ---------------------------------------
2727 PROCEDURE delete_row(
2728 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2729 x_return_status OUT NOCOPY VARCHAR2,
2730 x_msg_count OUT NOCOPY NUMBER,
2731 x_msg_data OUT NOCOPY VARCHAR2,
2732 p_svf_rec IN svf_rec_type) IS
2733
2734 l_api_version CONSTANT NUMBER := 1;
2735 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
2736 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2737 l_svf_rec svf_rec_type:= p_svf_rec;
2738 l_row_notfound BOOLEAN := TRUE;
2739 BEGIN
2740 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2741 p_init_msg_list,
2742 '_PVT',
2743 x_return_status);
2744 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2745 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2746 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2747 RAISE OKL_API.G_EXCEPTION_ERROR;
2748 END IF;
2749 DELETE FROM OKL_SERVICE_FEES_B
2750 WHERE ID = l_svf_rec.id;
2751
2752 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2753 EXCEPTION
2754 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2755 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2756 (
2757 l_api_name,
2758 G_PKG_NAME,
2759 'OKL_API.G_RET_STS_ERROR',
2760 x_msg_count,
2761 x_msg_data,
2765 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2762 '_PVT'
2763 );
2764 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2766 (
2767 l_api_name,
2768 G_PKG_NAME,
2769 'OKL_API.G_RET_STS_UNEXP_ERROR',
2770 x_msg_count,
2771 x_msg_data,
2772 '_PVT'
2773 );
2774 WHEN OTHERS THEN
2775 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2776 (
2777 l_api_name,
2778 G_PKG_NAME,
2779 'OTHERS',
2780 x_msg_count,
2781 x_msg_data,
2782 '_PVT'
2783 );
2784 END delete_row;
2785 ----------------------------------------
2786 -- delete_row for:OKL_SERVICE_FEES_TL --
2787 ----------------------------------------
2788 PROCEDURE delete_row(
2789 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2790 x_return_status OUT NOCOPY VARCHAR2,
2791 x_msg_count OUT NOCOPY NUMBER,
2792 x_msg_data OUT NOCOPY VARCHAR2,
2793 p_okl_service_fees_tl_rec IN okl_service_fees_tl_rec_type) IS
2794
2795 l_api_version CONSTANT NUMBER := 1;
2796 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
2797 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2798 l_okl_service_fees_tl_rec okl_service_fees_tl_rec_type:= p_okl_service_fees_tl_rec;
2799 l_row_notfound BOOLEAN := TRUE;
2800 --------------------------------------------
2801 -- Set_Attributes for:OKL_SERVICE_FEES_TL --
2802 --------------------------------------------
2803 FUNCTION Set_Attributes (
2804 p_okl_service_fees_tl_rec IN okl_service_fees_tl_rec_type,
2805 x_okl_service_fees_tl_rec OUT NOCOPY okl_service_fees_tl_rec_type
2806 ) RETURN VARCHAR2 IS
2807 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2808 BEGIN
2809 x_okl_service_fees_tl_rec := p_okl_service_fees_tl_rec;
2810 x_okl_service_fees_tl_rec.LANGUAGE := USERENV('LANG');
2811 RETURN(l_return_status);
2812 END Set_Attributes;
2813 BEGIN
2814 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2815 p_init_msg_list,
2816 '_PVT',
2817 x_return_status);
2818 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2819 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2820 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2821 RAISE OKL_API.G_EXCEPTION_ERROR;
2822 END IF;
2823 --- Setting item attributes
2824 l_return_status := Set_Attributes(
2825 p_okl_service_fees_tl_rec, -- IN
2826 l_okl_service_fees_tl_rec); -- OUT
2827 --- If any errors happen abort API
2828 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2829 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2830 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2831 RAISE OKL_API.G_EXCEPTION_ERROR;
2832 END IF;
2833 DELETE FROM OKL_SERVICE_FEES_TL
2834 WHERE ID = l_okl_service_fees_tl_rec.id;
2835
2836 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2837 EXCEPTION
2838 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2839 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2840 (
2841 l_api_name,
2842 G_PKG_NAME,
2843 'OKL_API.G_RET_STS_ERROR',
2844 x_msg_count,
2845 x_msg_data,
2846 '_PVT'
2847 );
2848 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2849 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2850 (
2851 l_api_name,
2852 G_PKG_NAME,
2853 'OKL_API.G_RET_STS_UNEXP_ERROR',
2854 x_msg_count,
2855 x_msg_data,
2856 '_PVT'
2857 );
2858 WHEN OTHERS THEN
2859 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2860 (
2861 l_api_name,
2862 G_PKG_NAME,
2863 'OTHERS',
2864 x_msg_count,
2865 x_msg_data,
2866 '_PVT'
2867 );
2868 END delete_row;
2869 ---------------------------------------
2870 -- delete_row for:OKL_SERVICE_FEES_V --
2871 ---------------------------------------
2872 PROCEDURE delete_row(
2873 p_api_version IN NUMBER,
2874 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2875 x_return_status OUT NOCOPY VARCHAR2,
2876 x_msg_count OUT NOCOPY NUMBER,
2877 x_msg_data OUT NOCOPY VARCHAR2,
2878 p_svfv_rec IN svfv_rec_type) IS
2879
2880 l_api_version CONSTANT NUMBER := 1;
2881 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
2882 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2883 l_svfv_rec svfv_rec_type := p_svfv_rec;
2884 l_okl_service_fees_tl_rec okl_service_fees_tl_rec_type;
2885 l_svf_rec svf_rec_type;
2886 BEGIN
2887 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2888 G_PKG_NAME,
2889 p_init_msg_list,
2890 l_api_version,
2891 p_api_version,
2892 '_PVT',
2893 x_return_status);
2894 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2895 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2896 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2897 RAISE OKL_API.G_EXCEPTION_ERROR;
2898 END IF;
2899 --------------------------------------
2900 -- Move VIEW record to "Child" records
2901 --------------------------------------
2902 migrate(l_svfv_rec, l_okl_service_fees_tl_rec);
2903 migrate(l_svfv_rec, l_svf_rec);
2904 --------------------------------------------
2905 -- Call the DELETE_ROW for each child record
2906 --------------------------------------------
2907 delete_row(
2908 p_init_msg_list,
2909 x_return_status,
2910 x_msg_count,
2911 x_msg_data,
2912 l_okl_service_fees_tl_rec
2913 );
2914 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2915 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2916 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2917 RAISE OKL_API.G_EXCEPTION_ERROR;
2918 END IF;
2919 delete_row(
2920 p_init_msg_list,
2921 x_return_status,
2922 x_msg_count,
2923 x_msg_data,
2924 l_svf_rec
2925 );
2926 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2927 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2928 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2929 RAISE OKL_API.G_EXCEPTION_ERROR;
2930 END IF;
2931 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2932 EXCEPTION
2933 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2934 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2935 (
2936 l_api_name,
2937 G_PKG_NAME,
2938 'OKL_API.G_RET_STS_ERROR',
2939 x_msg_count,
2940 x_msg_data,
2941 '_PVT'
2942 );
2943 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2944 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2945 (
2946 l_api_name,
2947 G_PKG_NAME,
2948 'OKL_API.G_RET_STS_UNEXP_ERROR',
2949 x_msg_count,
2950 x_msg_data,
2951 '_PVT'
2952 );
2953 WHEN OTHERS THEN
2954 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
2955 (
2956 l_api_name,
2957 G_PKG_NAME,
2958 'OTHERS',
2959 x_msg_count,
2960 x_msg_data,
2961 '_PVT'
2962 );
2963 END delete_row;
2964 ----------------------------------------
2965 -- PL/SQL TBL delete_row for:SVFV_TBL --
2966 ----------------------------------------
2967 PROCEDURE delete_row(
2968 p_api_version IN NUMBER,
2969 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2970 x_return_status OUT NOCOPY VARCHAR2,
2971 x_msg_count OUT NOCOPY NUMBER,
2972 x_msg_data OUT NOCOPY VARCHAR2,
2973 p_svfv_tbl IN svfv_tbl_type) IS
2974
2975 l_api_version CONSTANT NUMBER := 1;
2976 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
2977 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2978 i NUMBER := 0;
2979 BEGIN
2980 OKL_API.init_msg_list(p_init_msg_list);
2981 -- Make sure PL/SQL table has records in it before passing
2982 IF (p_svfv_tbl.COUNT > 0) THEN
2983 i := p_svfv_tbl.FIRST;
2984 LOOP
2985 delete_row (
2986 p_api_version => p_api_version,
2987 p_init_msg_list => OKL_API.G_FALSE,
2988 x_return_status => x_return_status,
2989 x_msg_count => x_msg_count,
2990 x_msg_data => x_msg_data,
2991 p_svfv_rec => p_svfv_tbl(i));
2992 EXIT WHEN (i = p_svfv_tbl.LAST);
2993 i := p_svfv_tbl.NEXT(i);
2994 END LOOP;
2995 END IF;
2996 EXCEPTION
2997 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2998 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2999 (
3000 l_api_name,
3001 G_PKG_NAME,
3002 'OKL_API.G_RET_STS_ERROR',
3003 x_msg_count,
3004 x_msg_data,
3005 '_PVT'
3006 );
3007 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3008 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
3009 (
3010 l_api_name,
3011 G_PKG_NAME,
3012 'OKL_API.G_RET_STS_UNEXP_ERROR',
3013 x_msg_count,
3014 x_msg_data,
3015 '_PVT'
3016 );
3017 WHEN OTHERS THEN
3018 x_return_status :=OKL_API.HANDLE_EXCEPTIONS
3019 (
3020 l_api_name,
3021 G_PKG_NAME,
3022 'OTHERS',
3023 x_msg_count,
3024 x_msg_data,
3025 '_PVT'
3026 );
3027 END delete_row;
3028 END Okl_Svf_Pvt;