[Home] [Help]
PACKAGE BODY: APPS.OKL_PROCESS_TRX_TYPES_PVT
Source
1 PACKAGE BODY OKL_PROCESS_TRX_TYPES_PVT AS
2 /* $Header: OKLRTXTB.pls 115.3 2002/11/01 23:16:01 santonyr noship $ */
3
4 -- Added by Santonyr 09-19-2002
5
6 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
7 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_UNEXP_ERROR;
8 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := OKL_API.G_RET_STS_ERROR;
9 G_EXCEPTION_UNEXPECTED_ERROR EXCEPTION;
10 G_EXCEPTION_ERROR EXCEPTION;
11
12
13 PROCEDURE insert_trx_types(
14 p_api_version IN NUMBER,
15 p_init_msg_list IN VARCHAR2,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2,
19 p_tryv_rec IN tryv_rec_type,
20 x_tryv_rec OUT NOCOPY tryv_rec_type)
21
22 IS
23
24 l_api_version NUMBER := 1.0;
25 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
26 l_api_name VARCHAR2(30) := 'INSERT_TRX_TYPES';
27
28 BEGIN
29
30 x_return_status := OKL_API.G_RET_STS_SUCCESS;
31
32 -- Set save point
33
34 l_return_status := Okl_Api.START_ACTIVITY(l_api_name
35 ,G_PKG_NAME
36 ,p_init_msg_list
37 ,l_api_version
38 ,p_api_version
39 ,'_PVT'
40 ,l_return_status);
41 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
42 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
43 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
44 RAISE G_EXCEPTION_ERROR;
45 END IF;
46
47
48 -- 08/30/2002 Added by Santonyr if check to see if the name is not null
49
50 IF (p_tryv_rec.name IS NULL) OR
51 (p_tryv_rec.name = OKC_API.G_MISS_CHAR) THEN
52 OKC_API.SET_MESSAGE(p_app_name => 'OKL',
53 p_msg_name => 'OKL_NAME_REQUIRED' );
54
55 RAISE G_EXCEPTION_ERROR;
56 END IF;
57
58 OKL_TRX_TYPES_PUB.insert_trx_types(p_api_version => l_api_version,
59 p_init_msg_list => p_init_msg_list,
60 x_return_status => l_return_status,
61 x_msg_count => x_msg_count,
62 x_msg_data => x_msg_data,
63 p_tryv_rec => p_tryv_rec,
64 x_tryv_rec => x_tryv_rec);
65
66 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
67 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
68 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
69 RAISE G_EXCEPTION_ERROR;
70 END IF;
71
72 x_return_status := l_return_status;
73 Okl_Api.end_activity(x_msg_count, x_msg_data);
74
75
76 EXCEPTION
77 WHEN G_EXCEPTION_ERROR THEN
78 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
79 ,g_pkg_name
80 ,'OKL_API.G_RET_STS_ERROR'
81 ,x_msg_count
82 ,x_msg_data
83 ,'_PVT');
84 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
85 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
86 ,g_pkg_name
87 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
88 ,x_msg_count
89 ,x_msg_data
90 ,'_PVT');
91 WHEN OTHERS THEN
92 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
93 ,g_pkg_name
94 ,'OTHERS'
95 ,x_msg_count
96 ,x_msg_data
97 ,'_PVT');
98
99 END insert_trx_types;
100
101
102 PROCEDURE insert_trx_types(
103 p_api_version IN NUMBER,
104 p_init_msg_list IN VARCHAR2,
105 x_return_status OUT NOCOPY VARCHAR2,
106 x_msg_count OUT NOCOPY NUMBER,
107 x_msg_data OUT NOCOPY VARCHAR2,
108 p_tryv_tbl IN tryv_tbl_type,
109 x_tryv_tbl OUT NOCOPY tryv_tbl_type)
110
111 IS
112
113 l_api_version NUMBER := 1.0;
114 l_api_name VARCHAR2(30) := 'INSERT_TRX_TYPES';
115 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
116 i NUMBER;
117
118 BEGIN
119
120 x_return_status := OKL_API.G_RET_STS_SUCCESS;
121
122 -- Set save point
123 l_return_status := Okl_Api.START_ACTIVITY(l_api_name
124 ,G_PKG_NAME
125 ,p_init_msg_list
126 ,l_api_version
127 ,p_api_version
128 ,'_PVT'
129 ,l_return_status);
130 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
131 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
132 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
133 RAISE G_EXCEPTION_ERROR;
134 END IF;
135
136 -- 08/30/2002 Added by Santonyr. Check to see if the name is not null
137
138 IF (p_tryv_tbl.COUNT > 0) THEN
139 i := p_tryv_tbl.FIRST;
140 LOOP
141 IF (p_tryv_tbl(i).name IS NULL) OR
142 (p_tryv_tbl(i).name = OKC_API.G_MISS_CHAR) THEN
143 OKC_API.SET_MESSAGE(p_app_name => 'OKL',
144 p_msg_name => 'OKL_NAME_REQUIRED' );
145
146 RAISE G_EXCEPTION_ERROR;
147 END IF;
148
149 EXIT WHEN (i = p_tryv_tbl.LAST);
150 i := p_tryv_tbl.NEXT(i);
151 END LOOP;
152 END IF;
153
154
155 OKL_TRX_TYPES_PUB.insert_trx_types(p_api_version => l_api_version,
156 p_init_msg_list => p_init_msg_list,
157 x_return_status => l_return_status,
158 x_msg_count => x_msg_count,
159 x_msg_data => x_msg_data,
160 p_tryv_tbl => p_tryv_tbl,
161 x_tryv_tbl => x_tryv_tbl);
162
163
164 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
165 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
166 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
167 RAISE G_EXCEPTION_ERROR;
168 END IF;
169
170 x_return_status := l_return_status;
171 Okl_Api.end_activity(x_msg_count, x_msg_data);
172
173 EXCEPTION
174 WHEN G_EXCEPTION_ERROR THEN
175 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
176 ,g_pkg_name
177 ,'OKL_API.G_RET_STS_ERROR'
178 ,x_msg_count
179 ,x_msg_data
180 ,'_PVT');
181 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
182 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
183 ,g_pkg_name
184 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
185 ,x_msg_count
186 ,x_msg_data
187 ,'_PVT');
188 WHEN OTHERS THEN
189 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
190 ,g_pkg_name
191 ,'OTHERS'
192 ,x_msg_count
193 ,x_msg_data
194 ,'_PVT');
195
196
197 END insert_trx_types;
198
199
200 -- 09/19/2002 Added by Santonyr
201 -- To Check if the update is allowed for this record. If the record is seeded,
202 -- do not allow updation.
203
204
205 FUNCTION Check_Update_Allowed (p_tryv_rec IN tryv_rec_type )
206 RETURN VARCHAR2
207 IS
208
209 CURSOR try_lub_cur (l_id NUMBER) IS
210 SELECT last_updated_by
211 FROM okl_trx_types_v
212 WHERE id = l_id;
213
214
215 l_last_updated_by okl_trx_types_v.last_updated_by%TYPE;
216 l_update_allowed VARCHAR2(1) := 'Y';
217
218 BEGIN
219
220 FOR try_lub_rec IN try_lub_cur (p_tryv_rec.id) LOOP
221 l_last_updated_by := try_lub_rec.last_updated_by;
222 END LOOP;
223
224 IF l_last_updated_by = 1 THEN
225 l_update_allowed := 'N';
226 END IF;
227
228 RETURN l_update_allowed;
229
230 END Check_Update_Allowed;
231
232
233 PROCEDURE update_trx_types(
234 p_api_version IN NUMBER,
235 p_init_msg_list IN VARCHAR2,
236 x_return_status OUT NOCOPY VARCHAR2,
237 x_msg_count OUT NOCOPY NUMBER,
238 x_msg_data OUT NOCOPY VARCHAR2,
239 p_tryv_rec IN tryv_rec_type,
240 x_tryv_rec OUT NOCOPY tryv_rec_type)
241
242 IS
243
244 l_api_version NUMBER := 1.0;
245 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
246 l_update_allowed VARCHAR2(1) := 'Y';
247 l_api_name VARCHAR2(30) := 'UPDATE_TRX_TYPES';
248
249
250 BEGIN
251
252 x_return_status := OKL_API.G_RET_STS_SUCCESS;
253
254 -- Set save point
255 l_return_status := Okl_Api.START_ACTIVITY(l_api_name
256 ,G_PKG_NAME
257 ,p_init_msg_list
258 ,l_api_version
259 ,p_api_version
260 ,'_PVT'
261 ,l_return_status);
262 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
263 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
264 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
265 RAISE G_EXCEPTION_ERROR;
266 END IF;
267
268
269 -- 08/30/2002 Added by Santonyr Mandatory Check for Name
270
271 IF (p_tryv_rec.name IS NULL) OR
272 (p_tryv_rec.name = OKC_API.G_MISS_CHAR) THEN
273 OKC_API.SET_MESSAGE(p_app_name => 'OKL',
274 p_msg_name => 'OKL_NAME_REQUIRED' );
275
276 RAISE G_EXCEPTION_ERROR;
277 END IF;
278
279
280 -- 09/19/2002 Added by Santonyr To Check if the updation is allowed.
281
282 l_update_allowed := Check_Update_Allowed(p_tryv_rec);
283
284 IF l_update_allowed = 'N' THEN
285 OKC_API.SET_MESSAGE(p_app_name => 'OKL',
286 p_msg_name => 'OKL_NO_UPD_TRX_TYPE' );
287 RAISE G_EXCEPTION_ERROR;
288 END IF;
289
290
291 OKL_TRX_TYPES_PUB.update_trx_types(p_api_version => l_api_version,
292 p_init_msg_list => p_init_msg_list,
293 x_return_status => l_return_status,
294 x_msg_count => x_msg_count,
295 x_msg_data => x_msg_data,
296 p_tryv_rec => p_tryv_rec,
297 x_tryv_rec => x_tryv_rec);
298
299 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
300 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
301 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
302 RAISE G_EXCEPTION_ERROR;
303 END IF;
304
305
306 x_return_status := l_return_status;
307 Okl_Api.end_activity(x_msg_count, x_msg_data);
308
309
310 EXCEPTION
311 WHEN G_EXCEPTION_ERROR THEN
312 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
313 ,g_pkg_name
314 ,'OKL_API.G_RET_STS_ERROR'
315 ,x_msg_count
316 ,x_msg_data
317 ,'_PVT');
318 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
319 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
320 ,g_pkg_name
321 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
322 ,x_msg_count
323 ,x_msg_data
324 ,'_PVT');
325 WHEN OTHERS THEN
326 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
327 ,g_pkg_name
328 ,'OTHERS'
329 ,x_msg_count
330 ,x_msg_data
331 ,'_PVT');
332
333 END update_trx_types;
334
335
336
337 PROCEDURE update_trx_types(
338 p_api_version IN NUMBER,
339 p_init_msg_list IN VARCHAR2,
340 x_return_status OUT NOCOPY VARCHAR2,
341 x_msg_count OUT NOCOPY NUMBER,
342 x_msg_data OUT NOCOPY VARCHAR2,
343 p_tryv_tbl IN tryv_tbl_type,
344 x_tryv_tbl OUT NOCOPY tryv_tbl_type)
345
346 IS
347
348 l_api_version NUMBER := 1.0;
349 l_update_allowed VARCHAR2(1) := 'Y';
350 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
351 l_api_name VARCHAR2(30) := 'UPDATE_TRX_TYPES';
352 i NUMBER;
353
354 BEGIN
355 x_return_status := OKL_API.G_RET_STS_SUCCESS;
356
357 -- Set save point
358 l_return_status := Okl_Api.START_ACTIVITY(l_api_name
359 ,G_PKG_NAME
360 ,p_init_msg_list
361 ,l_api_version
362 ,p_api_version
363 ,'_PVT'
364 ,l_return_status);
365
366 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
367 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
368 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
369 RAISE G_EXCEPTION_ERROR;
370 END IF;
371
372 -- 08/30/2002 Added by Santonyr Check to see if the name is not null
373
374 IF (p_tryv_tbl.COUNT > 0) THEN
375 i := p_tryv_tbl.FIRST;
376
377 LOOP
378 IF (p_tryv_tbl(i).name IS NULL) OR
379 (p_tryv_tbl(i).name = OKC_API.G_MISS_CHAR) THEN
380 OKC_API.SET_MESSAGE(p_app_name => 'OKL',
381 p_msg_name => 'OKL_NAME_REQUIRED' );
382
383 RAISE G_EXCEPTION_ERROR;
384 END IF;
385
386 -- 09/19/2002 Added by Santonyr To Check if the updation is allowed.
387
388 l_update_allowed := Check_Update_Allowed(p_tryv_tbl(i));
389
390 IF l_update_allowed = 'N' THEN
391 OKC_API.SET_MESSAGE(p_app_name => 'OKL',
392 p_msg_name => 'OKL_NO_UPD_TRX_TYPE' );
393 RAISE G_EXCEPTION_ERROR;
394 END IF;
395
396 EXIT WHEN (i = p_tryv_tbl.LAST);
397 i := p_tryv_tbl.NEXT(i);
398 END LOOP;
399 END IF;
400
401
402 OKL_TRX_TYPES_PUB.update_trx_types(p_api_version => l_api_version,
403 p_init_msg_list => p_init_msg_list,
404 x_return_status => l_return_status,
405 x_msg_count => x_msg_count,
406 x_msg_data => x_msg_data,
407 p_tryv_tbl => p_tryv_tbl,
408 x_tryv_tbl => x_tryv_tbl);
409
410 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
411 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
412 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
413 RAISE G_EXCEPTION_ERROR;
414 END IF;
415
416
417 x_return_status := l_return_status;
418 Okl_Api.end_activity(x_msg_count, x_msg_data);
419
420
421 EXCEPTION
422 WHEN G_EXCEPTION_ERROR THEN
423 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
424 ,g_pkg_name
425 ,'OKL_API.G_RET_STS_ERROR'
426 ,x_msg_count
427 ,x_msg_data
428 ,'_PVT');
429 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
430 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
431 ,g_pkg_name
432 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
433 ,x_msg_count
434 ,x_msg_data
435 ,'_PVT');
436 WHEN OTHERS THEN
437 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
438 ,g_pkg_name
439 ,'OTHERS'
440 ,x_msg_count
441 ,x_msg_data
442 ,'_PVT');
443
444 END update_trx_types;
445
446
447 PROCEDURE delete_trx_types(
448 p_api_version IN NUMBER,
449 p_init_msg_list IN VARCHAR2,
450 x_return_status OUT NOCOPY VARCHAR2,
451 x_msg_count OUT NOCOPY NUMBER,
452 x_msg_data OUT NOCOPY VARCHAR2,
453 p_tryv_rec IN tryv_rec_type)
454
455 IS
456
457 l_api_version NUMBER := 1.0;
458 l_api_name VARCHAR2(30) := 'DELETE_TRX_TYPES';
459 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
460
461 BEGIN
462
463 x_return_status := OKL_API.G_RET_STS_SUCCESS;
464
465 -- Set save point
466 l_return_status := Okl_Api.START_ACTIVITY(l_api_name
467 ,G_PKG_NAME
468 ,p_init_msg_list
469 ,l_api_version
470 ,p_api_version
471 ,'_PVT'
472 ,l_return_status);
473 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
474 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
475 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
476 RAISE G_EXCEPTION_ERROR;
477 END IF;
478
479
480 OKL_TRX_TYPES_PUB.delete_trx_types(p_api_version => l_api_version,
481 p_init_msg_list => p_init_msg_list,
482 x_return_status => l_return_status,
483 x_msg_count => x_msg_count,
484 x_msg_data => x_msg_data,
485 p_tryv_rec => p_tryv_rec);
486
487
488 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
489 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
490 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
491 RAISE G_EXCEPTION_ERROR;
492 END IF;
493
494 x_return_status := l_return_status;
495 Okl_Api.end_activity(x_msg_count, x_msg_data);
496
497 EXCEPTION
498 WHEN G_EXCEPTION_ERROR THEN
499 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
500 ,g_pkg_name
501 ,'OKL_API.G_RET_STS_ERROR'
502 ,x_msg_count
503 ,x_msg_data
504 ,'_PVT');
505 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
506 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
507 ,g_pkg_name
508 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
509 ,x_msg_count
510 ,x_msg_data
511 ,'_PVT');
512 WHEN OTHERS THEN
513 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
514 ,g_pkg_name
515 ,'OTHERS'
516 ,x_msg_count
517 ,x_msg_data
518 ,'_PVT');
519
520
521 END delete_trx_types;
522
523
524
525 PROCEDURE delete_trx_types(
526 p_api_version IN NUMBER,
527 p_init_msg_list IN VARCHAR2,
528 x_return_status OUT NOCOPY VARCHAR2,
529 x_msg_count OUT NOCOPY NUMBER,
530 x_msg_data OUT NOCOPY VARCHAR2,
531 p_tryv_tbl IN tryv_tbl_type)
532
533 IS
534
535 l_api_version NUMBER := 1.0;
536 l_api_name VARCHAR2(30) := 'DELETE_TRX_TYPES';
537 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
538
539 BEGIN
540
541 x_return_status := OKL_API.G_RET_STS_SUCCESS;
542
543 -- Set save point
544 l_return_status := Okl_Api.START_ACTIVITY(l_api_name
545 ,G_PKG_NAME
546 ,p_init_msg_list
547 ,l_api_version
548 ,p_api_version
549 ,'_PVT'
550 ,l_return_status);
551 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
552 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
553 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
554 RAISE G_EXCEPTION_ERROR;
555 END IF;
556
557
558 OKL_TRX_TYPES_PUB.delete_trx_types(p_api_version => l_api_version,
559 p_init_msg_list => p_init_msg_list,
560 x_return_status => l_return_status,
561 x_msg_count => x_msg_count,
562 x_msg_data => x_msg_data,
563 p_tryv_tbl => p_tryv_tbl);
564
565
566 IF (l_return_status = G_RET_STS_UNEXP_ERROR) THEN
567 RAISE G_EXCEPTION_UNEXPECTED_ERROR;
568 ELSIF (l_return_Status = G_RET_STS_ERROR) THEN
569 RAISE G_EXCEPTION_ERROR;
570 END IF;
571
572 x_return_status := l_return_status;
573 Okl_Api.end_activity(x_msg_count, x_msg_data);
574
575
576 EXCEPTION
577 WHEN G_EXCEPTION_ERROR THEN
578 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
579 ,g_pkg_name
580 ,'OKL_API.G_RET_STS_ERROR'
581 ,x_msg_count
582 ,x_msg_data
583 ,'_PVT');
584 WHEN G_EXCEPTION_UNEXPECTED_ERROR THEN
585 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
586 ,g_pkg_name
587 ,'OKL_API.G_RET_STS_UNEXP_ERROR'
588 ,x_msg_count
589 ,x_msg_data
590 ,'_PVT');
591 WHEN OTHERS THEN
592 x_return_status := OKL_API.HANDLE_EXCEPTIONS(l_api_name
593 ,g_pkg_name
594 ,'OTHERS'
595 ,x_msg_count
596 ,x_msg_data
597 ,'_PVT');
598
599
600 END delete_trx_types;
601
602
603 END OKL_PROCESS_TRX_TYPES_PVT;