[Home] [Help]
PACKAGE BODY: APPS.OKL_PCM_PVT
Source
1 PACKAGE BODY OKL_pcm_PVT AS
2 /* $Header: OKLSPCMB.pls 120.0 2007/10/05 10:41:06 asawanka noship $ */
3 ---------------------------------------------------------------------------
4 -- PROCEDURE load_error_tbl
5 ---------------------------------------------------------------------------
6 PROCEDURE load_error_tbl (
7 px_error_rec IN OUT NOCOPY OKL_API.ERROR_REC_TYPE,
8 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
9
10 j INTEGER := NVL(px_error_tbl.LAST, 0) + 1;
11 last_msg_idx INTEGER := FND_MSG_PUB.COUNT_MSG;
12 l_msg_idx INTEGER := FND_MSG_PUB.G_NEXT;
13 BEGIN
14 -- FND_MSG_PUB has a small error in it. If we call FND_MSG_PUB.COUNT_AND_GET before
15 -- we call FND_MSG_PUB.GET, the variable FND_MSG_PUB uses to control the index of the
16 -- message stack gets set to 1. This makes sense until we call FND_MSG_PUB.GET which
17 -- automatically increments the index by 1, (making it 2), however, when the GET function
18 -- attempts to pull message 2, we get a NO_DATA_FOUND exception because there isn't any
19 -- message 2. To circumvent this problem, check the amount of messages and compensate.
20 -- Again, this error only occurs when 1 message is on the stack because COUNT_AND_GET
21 -- will only update the index variable when 1 and only 1 message is on the stack.
22 IF (last_msg_idx = 1) THEN
23 l_msg_idx := FND_MSG_PUB.G_FIRST;
24 END IF;
25 LOOP
26 fnd_msg_pub.get(
27 p_msg_index => l_msg_idx,
28 p_encoded => fnd_api.g_false,
29 p_data => px_error_rec.msg_data,
30 p_msg_index_out => px_error_rec.msg_count);
31 px_error_tbl(j) := px_error_rec;
32 j := j + 1;
33 EXIT WHEN (px_error_rec.msg_count = last_msg_idx);
34 END LOOP;
35 END load_error_tbl;
36 ---------------------------------------------------------------------------
37 -- FUNCTION find_highest_exception
38 ---------------------------------------------------------------------------
39 -- Finds the highest exception (G_RET_STS_UNEXP_ERROR)
40 -- in a OKL_API.ERROR_TBL_TYPE, and returns it.
41 FUNCTION find_highest_exception(
42 p_error_tbl IN OKL_API.ERROR_TBL_TYPE
43 ) RETURN VARCHAR2 IS
44 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
45 i INTEGER := 1;
46 BEGIN
47 IF (p_error_tbl.COUNT > 0) THEN
48 i := p_error_tbl.FIRST;
49 LOOP
50 IF (p_error_tbl(i).error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
51 IF (l_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
52 l_return_status := p_error_tbl(i).error_type;
53 END IF;
54 END IF;
55 EXIT WHEN (i = p_error_tbl.LAST);
56 i := p_error_tbl.NEXT(i);
57 END LOOP;
58 END IF;
59 RETURN(l_return_status);
60 END find_highest_exception;
61 ---------------------------------------------------------------------------
62 -- FUNCTION get_seq_id
63 ---------------------------------------------------------------------------
64 FUNCTION get_seq_id RETURN NUMBER IS
65 BEGIN
66 RETURN(okc_p_util.raw_to_number(sys_guid()));
67 END get_seq_id;
68
69 ---------------------------------------------------------------------------
70 -- PROCEDURE qc
71 ---------------------------------------------------------------------------
72 PROCEDURE qc IS
73 BEGIN
74 null;
75 END qc;
76
77 ---------------------------------------------------------------------------
78 -- PROCEDURE change_version
79 ---------------------------------------------------------------------------
80 PROCEDURE change_version IS
81 BEGIN
82 null;
83 END change_version;
84
85 ---------------------------------------------------------------------------
86 -- PROCEDURE api_copy
87 ---------------------------------------------------------------------------
88 PROCEDURE api_copy IS
89 BEGIN
90 null;
91 END api_copy;
92
93 ---------------------------------------------------------------------------
94 -- FUNCTION get_rec for: okl_pmt_channel_methods
95 ---------------------------------------------------------------------------
96 FUNCTION get_rec (
97 p_pcm_rec IN pcm_rec_type,
98 x_no_data_found OUT NOCOPY BOOLEAN
99 ) RETURN pcm_rec_type IS
100 CURSOR okl_pmt_channel_csr (p_id IN NUMBER) IS
101 SELECT
102 ID,
103 OBJECT_VERSION_NUMBER,
104 CREATED_BY,
105 CREATION_DATE,
106 LAST_UPDATED_BY,
107 LAST_UPDATE_DATE,
108 LAST_UPDATE_LOGIN,
109 ORG_ID,
110 RECEIPT_METHOD_ID,
111 PAYMENT_CHANNEL_CODE
112 FROM okl_pmt_channel_methods
113 WHERE id = p_id;
114 l_okl_pmt_channel_methods_pk okl_pmt_channel_csr%ROWTYPE;
115 l_pcm_rec pcm_rec_type;
116 BEGIN
117 x_no_data_found := TRUE;
118 -- Get current database values
119 OPEN okl_pmt_channel_csr (p_pcm_rec.id);
120 FETCH okl_pmt_channel_csr INTO
121 l_pcm_rec.id,
122 l_pcm_rec.object_version_number,
123 l_pcm_rec.created_by,
124 l_pcm_rec.creation_date,
125 l_pcm_rec.last_updated_by,
126 l_pcm_rec.last_update_date,
127 l_pcm_rec.last_update_login,
128 l_pcm_rec.org_id,
129 l_pcm_rec.receipt_method_id,
130 l_pcm_rec.payment_channel_code;
131 x_no_data_found := okl_pmt_channel_csr%NOTFOUND;
132 CLOSE okl_pmt_channel_csr;
133 RETURN(l_pcm_rec);
134 END get_rec;
135
136 ------------------------------------------------------------------
137 -- This version of get_rec sets error messages if no data found --
138 ------------------------------------------------------------------
139 FUNCTION get_rec (
140 p_pcm_rec IN pcm_rec_type,
141 x_return_status OUT NOCOPY VARCHAR2
142 ) RETURN pcm_rec_type IS
143 l_pcm_rec pcm_rec_type;
144 l_row_notfound BOOLEAN := TRUE;
145 BEGIN
146 x_return_status := OKL_API.G_RET_STS_SUCCESS;
147 l_pcm_rec := get_rec(p_pcm_rec, l_row_notfound);
148 IF (l_row_notfound) THEN
149 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
150 x_return_status := OKL_API.G_RET_STS_ERROR;
151 END IF;
152 RETURN(l_pcm_rec);
153 END get_rec;
154 -----------------------------------------------------------
155 -- So we don't have to pass an "l_row_notfound" variable --
156 -----------------------------------------------------------
157 FUNCTION get_rec (
158 p_pcm_rec IN pcm_rec_type
159 ) RETURN pcm_rec_type IS
160 l_row_not_found BOOLEAN := TRUE;
161 BEGIN
162 RETURN(get_rec(p_pcm_rec, l_row_not_found));
163 END get_rec;
164 ---------------------------------------------------------------------------
165 -- FUNCTION null_out_defaults for: okl_pmt_channel_methods
166 ---------------------------------------------------------------------------
167 FUNCTION null_out_defaults (
168 p_pcm_rec IN pcm_rec_type
169 ) RETURN pcm_rec_type IS
170 l_pcm_rec pcm_rec_type := p_pcm_rec;
171 BEGIN
172 IF (l_pcm_rec.id = OKL_API.G_MISS_NUM ) THEN
173 l_pcm_rec.id := NULL;
174 END IF;
175 IF (l_pcm_rec.object_version_number = OKL_API.G_MISS_NUM ) THEN
176 l_pcm_rec.object_version_number := NULL;
177 END IF;
178 IF (l_pcm_rec.org_id = OKL_API.G_MISS_NUM ) THEN
179 l_pcm_rec.org_id := NULL;
180 END IF;
181 IF (l_pcm_rec.receipt_method_id = OKL_API.G_MISS_NUM ) THEN
182 l_pcm_rec.receipt_method_id := NULL;
183 END IF;
184 IF (l_pcm_rec.payment_Channel_code = OKL_API.G_MISS_CHAR ) THEN
185 l_pcm_rec.payment_Channel_code := NULL;
186 END IF;
187 IF (l_pcm_rec.created_by = OKL_API.G_MISS_NUM ) THEN
188 l_pcm_rec.created_by := NULL;
189 END IF;
190 IF (l_pcm_rec.creation_date = OKL_API.G_MISS_DATE ) THEN
191 l_pcm_rec.creation_date := NULL;
192 END IF;
193 IF (l_pcm_rec.last_updated_by = OKL_API.G_MISS_NUM ) THEN
194 l_pcm_rec.last_updated_by := NULL;
195 END IF;
196 IF (l_pcm_rec.last_update_date = OKL_API.G_MISS_DATE ) THEN
197 l_pcm_rec.last_update_date := NULL;
198 END IF;
199 IF (l_pcm_rec.last_update_login = OKL_API.G_MISS_NUM ) THEN
200 l_pcm_rec.last_update_login := NULL;
201 END IF;
202 RETURN(l_pcm_rec);
203 END null_out_defaults;
204 ---------------------------------
205 -- Validate_Attributes for: ID --
206 ---------------------------------
207 PROCEDURE validate_id(
208 x_return_status OUT NOCOPY VARCHAR2,
209 p_id IN NUMBER) IS
210 BEGIN
211 x_return_status := OKL_API.G_RET_STS_SUCCESS;
212 IF (p_id = OKL_API.G_MISS_NUM OR
213 p_id IS NULL)
214 THEN
215 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
216 x_return_status := OKL_API.G_RET_STS_ERROR;
217 RAISE G_EXCEPTION_HALT_VALIDATION;
218 END IF;
219 EXCEPTION
220 WHEN G_EXCEPTION_HALT_VALIDATION THEN
221 null;
222 WHEN OTHERS THEN
223 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
224 ,p_msg_name => G_UNEXPECTED_ERROR
225 ,p_token1 => G_SQLCODE_TOKEN
226 ,p_token1_value => SQLCODE
227 ,p_token2 => G_SQLERRM_TOKEN
228 ,p_token2_value => SQLERRM);
229 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
230 END validate_id;
231 ----------------------------------------------------
232 -- Validate_Attributes for: OBJECT_VERSION_NUMBER --
233 ----------------------------------------------------
234 PROCEDURE validate_object_version_number(
235 x_return_status OUT NOCOPY VARCHAR2,
236 p_object_version_number IN NUMBER) IS
237 BEGIN
238 x_return_status := OKL_API.G_RET_STS_SUCCESS;
239 IF (p_object_version_number = OKL_API.G_MISS_NUM OR
240 p_object_version_number IS NULL)
241 THEN
242 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'object_version_number');
243 x_return_status := OKL_API.G_RET_STS_ERROR;
244 RAISE G_EXCEPTION_HALT_VALIDATION;
245 END IF;
246 EXCEPTION
247 WHEN G_EXCEPTION_HALT_VALIDATION THEN
248 null;
249 WHEN OTHERS THEN
250 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
251 ,p_msg_name => G_UNEXPECTED_ERROR
252 ,p_token1 => G_SQLCODE_TOKEN
253 ,p_token1_value => SQLCODE
254 ,p_token2 => G_SQLERRM_TOKEN
255 ,p_token2_value => SQLERRM);
256 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
257 END validate_object_version_number;
258
259 ---------------------------------
260 -- Validate_Attributes for: org_ID --
261 ---------------------------------
262 PROCEDURE validate_org_id(
263 x_return_status OUT NOCOPY VARCHAR2,
264 p_org_id IN NUMBER) IS
265 BEGIN
266 x_return_status := OKL_API.G_RET_STS_SUCCESS;
267 IF (p_org_id = OKL_API.G_MISS_NUM OR
268 p_org_id IS NULL)
269 THEN
270 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'org_id');
271 x_return_status := OKL_API.G_RET_STS_ERROR;
272 RAISE G_EXCEPTION_HALT_VALIDATION;
273 END IF;
274 EXCEPTION
275 WHEN G_EXCEPTION_HALT_VALIDATION THEN
276 null;
277 WHEN OTHERS THEN
278 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
279 ,p_msg_name => G_UNEXPECTED_ERROR
280 ,p_token1 => G_SQLCODE_TOKEN
281 ,p_token1_value => SQLCODE
282 ,p_token2 => G_SQLERRM_TOKEN
283 ,p_token2_value => SQLERRM);
284 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
285 END validate_org_id;
286 --------------------------------------------------------------
287 -- Validate_Attributes for: validate_payment_Channel_code --
288 --------------------------------------------------------------
289 PROCEDURE validate_payment_Channel_code(
290 x_return_status OUT NOCOPY VARCHAR2,
291 p_payment_Channel_code IN VARCHAR2) IS
292
293 l_dummy_var VARCHAR2(1) := '?' ;
294
295 CURSOR okl_pc_csr (cp_payment_Channel_code IN VARCHAR2) IS
296 SELECT 'x'
297 FROM iby_fndcpt_all_pmt_channels_v
298 WHERE payment_Channel_code = cp_payment_Channel_code;
299 BEGIN
300 x_return_status := OKL_API.G_RET_STS_SUCCESS;
301 IF (p_payment_Channel_code = OKL_API.G_MISS_CHAR OR p_payment_Channel_code IS NULL)
302 THEN
303 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'payment_Channel_code');
304 x_return_status := OKL_API.G_RET_STS_ERROR;
305 RAISE G_EXCEPTION_HALT_VALIDATION;
306 END IF;
307 IF (p_payment_Channel_code <> OKL_API.G_MISS_CHAR AND p_payment_Channel_code IS NOT NULL)
308 THEN
309 OPEN okl_pc_csr(p_payment_Channel_code) ;
310 FETCH okl_pc_csr into l_dummy_var ;
311 CLOSE okl_pc_csr ;
312 -- still set to default means data was not found
313 IF ( l_dummy_var = '?' ) THEN
314 OKC_API.set_message(g_app_name,
315 g_no_parent_record,
316 g_col_name_token,
317 'payment_Channel_code',
318 g_child_table_token ,
319 'okl_pmt_channel_methods',
320 g_parent_table_token ,
321 'iby_fndcpt_all_pmt_channels_v');
322 x_return_status := OKC_API.G_RET_STS_ERROR;
323 RAISE G_EXCEPTION_HALT_VALIDATION;
324 END IF;
325 END IF;
326
327 EXCEPTION
328 WHEN G_EXCEPTION_HALT_VALIDATION THEN
329 null;
330 WHEN OTHERS THEN
331 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
332 ,p_msg_name => G_UNEXPECTED_ERROR
333 ,p_token1 => G_SQLCODE_TOKEN
334 ,p_token1_value => SQLCODE
335 ,p_token2 => G_SQLERRM_TOKEN
336 ,p_token2_value => SQLERRM);
337 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
338 END validate_payment_Channel_code;
339
340 --------------------------------------------------------------
341 -- Validate_Attributes for: validate_receipt_method_id --
342 --------------------------------------------------------------
343 PROCEDURE validate_receipt_method_id(
344 x_return_status OUT NOCOPY VARCHAR2,
345 p_receipt_method_id IN NUMBER) IS
346
347 l_dummy_var VARCHAR2(1) := '?' ;
348
349 CURSOR okl_rm_csr (cp_receipt_method_id IN NUMBER) IS
350 SELECT 'x'
351 FROM ar_receipt_methods
352 WHERE receipt_method_id = cp_receipt_method_id;
353 BEGIN
354 x_return_status := OKL_API.G_RET_STS_SUCCESS;
355
356 IF (p_receipt_method_id <> OKL_API.G_MISS_NUM AND p_receipt_method_id IS NOT NULL)
357 THEN
358 OPEN okl_rm_csr(p_receipt_method_id) ;
359 FETCH okl_rm_csr into l_dummy_var ;
360 CLOSE okl_rm_csr ;
361 -- still set to default means data was not found
362 IF ( l_dummy_var = '?' ) THEN
363 OKC_API.set_message(g_app_name,
364 g_no_parent_record,
365 g_col_name_token,
366 'receipt_method_id',
367 g_child_table_token ,
368 'okl_pmt_channel_methods',
369 g_parent_table_token ,
370 'okl_bpd_rcpt_mthds_uv');
371 x_return_status := OKC_API.G_RET_STS_ERROR;
372 RAISE G_EXCEPTION_HALT_VALIDATION;
373 END IF;
374 END IF;
375
376 EXCEPTION
377 WHEN G_EXCEPTION_HALT_VALIDATION THEN
378 null;
379 WHEN OTHERS THEN
380 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
381 ,p_msg_name => G_UNEXPECTED_ERROR
382 ,p_token1 => G_SQLCODE_TOKEN
383 ,p_token1_value => SQLCODE
384 ,p_token2 => G_SQLERRM_TOKEN
385 ,p_token2_value => SQLERRM);
386 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
387 END validate_receipt_method_id;
388
389 ---------------------------------------------------------------------------
390 -- FUNCTION Validate_Attributes
391 ---------------------------------------------------------------------------
392 ---------------------------------------------------------
393 -- Validate_Attributes for:okl_pmt_channel_methods --
394 ---------------------------------------------------------
395 FUNCTION Validate_Attributes (
396 p_pcm_rec IN pcm_rec_type
397 ) RETURN VARCHAR2 IS
398 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
399 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
400 BEGIN
401 -----------------------------
402 -- Column Level Validation --
403 -----------------------------
404 -- ***
405 -- id
406 -- ***
407 validate_id(x_return_status, p_pcm_rec.id);
408 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
409 l_return_status := x_return_status;
410 RAISE G_EXCEPTION_HALT_VALIDATION;
411 END IF;
412
413 -- ***
414 -- object_version_number
415 -- ***
416 validate_object_version_number(x_return_status, p_pcm_rec.object_version_number);
417 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
418 l_return_status := x_return_status;
419 RAISE G_EXCEPTION_HALT_VALIDATION;
420 END IF;
421
422 -- ***
423 -- org_id
424 -- ***
425 validate_org_id(x_return_status, p_pcm_rec.org_id);
426 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
427 l_return_status := x_return_status;
428 RAISE G_EXCEPTION_HALT_VALIDATION;
429 END IF;
430
431 -- ***
432 -- receipt_method_id
433 -- ***
434 validate_receipt_method_id(x_return_status, p_pcm_rec.receipt_method_id);
435 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
436 l_return_status := x_return_status;
437 RAISE G_EXCEPTION_HALT_VALIDATION;
438 END IF;
439
440 -- ***
441 -- payment_channel_code
442 -- ***
443 validate_payment_channel_code(x_return_status, p_pcm_rec.payment_channel_code);
444 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
445 l_return_status := x_return_status;
446 RAISE G_EXCEPTION_HALT_VALIDATION;
447 END IF;
448
449
450 RETURN(l_return_status);
451 EXCEPTION
452 WHEN G_EXCEPTION_HALT_VALIDATION THEN
453 RETURN(l_return_status);
454 WHEN OTHERS THEN
455 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME_1
456 ,p_msg_name => G_UNEXPECTED_ERROR
457 ,p_token1 => G_SQLCODE_TOKEN
458 ,p_token1_value => SQLCODE
459 ,p_token2 => G_SQLERRM_TOKEN
460 ,p_token2_value => SQLERRM);
461 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
462 RETURN(l_return_status);
463 END Validate_Attributes;
464 ---------------------------------------------------------------------------
465 -- PROCEDURE Validate_Record
466 ---------------------------------------------------------------------------
467 -----------------------------------------------------
468 -- Validate Record for:okl_pmt_channel_methods --
469 -----------------------------------------------------
470 FUNCTION Validate_Record (
471 p_pcm_rec IN pcm_rec_type
472 ) RETURN VARCHAR2 IS
473 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
474 l_dummy VARCHAR2(1);
475
476 CURSOR chk_dup_csr(cp_org_id IN NUMBER, cp_payment_channel_code IN VARCHAR2) IS
477 SELECT 'x'
478 FROM okl_pmt_channel_methods
479 WHERE org_id = cp_org_id
480 AND payment_channel_code = cp_payment_channel_code;
481
482 BEGIN
483 IF p_pcm_rec.id is null OR p_pcm_rec.id = okl_api.g_miss_num THEN
484 OPEN chk_dup_csr(p_pcm_rec.org_id, p_pcm_rec.payment_Channel_code);
485 FETCH chk_dup_csr INTO l_dummy;
486 IF chk_dup_csr%FOUND THEN
487 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
488 p_msg_name => 'OKL_DUP_PAYMENT_CHANNEL',
489 p_token1 => 'PAYMENT_CHANNEL',
490 p_token1_value => p_pcm_rec.payment_Channel_code);
491 l_return_status := OKL_API.G_RET_STS_ERROR;
492 RETURN (l_return_status);
493 END IF;
494 CLOSE chk_dup_csr;
495 END IF;
496 RETURN (l_return_status);
497 END Validate_Record;
498
499 ---------------------------------------------------------------------------
500 -- PROCEDURE validate_row
501 ---------------------------------------------------------------------------
502 --------------------------------------------------
503 -- validate_row for:okl_pmt_channel_methods --
504 --------------------------------------------------
505 PROCEDURE validate_row(
506 p_api_version IN NUMBER,
507 p_init_msg_list IN VARCHAR2,
508 x_return_status OUT NOCOPY VARCHAR2,
509 x_msg_count OUT NOCOPY NUMBER,
510 x_msg_data OUT NOCOPY VARCHAR2,
511 p_pcm_rec IN pcm_rec_type) IS
512
513 l_api_version CONSTANT NUMBER := 1;
514 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
515 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
516 l_pcm_rec pcm_rec_type := p_pcm_rec;
517 BEGIN
518 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
519 G_PKG_NAME,
520 p_init_msg_list,
521 l_api_version,
522 p_api_version,
523 '_PVT',
524 x_return_status);
525 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
526 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
527 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
528 RAISE OKL_API.G_EXCEPTION_ERROR;
529 END IF;
530 --- Validate all non-missing attributes (Item Level Validation)
531 l_return_status := Validate_Attributes(l_pcm_rec);
532 --- If any errors happen abort API
533 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
534 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
535 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
536 RAISE OKL_API.G_EXCEPTION_ERROR;
537 END IF;
538 l_return_status := Validate_Record(l_pcm_rec);
539 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
540 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
541 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
542 RAISE OKL_API.G_EXCEPTION_ERROR;
543 END IF;
544 x_return_status := l_return_status;
545 EXCEPTION
546 WHEN OKL_API.G_EXCEPTION_ERROR THEN
547 x_return_status := OKL_API.HANDLE_EXCEPTIONS
548 (
549 l_api_name,
550 G_PKG_NAME,
551 'OKL_API.G_RET_STS_ERROR',
552 x_msg_count,
553 x_msg_data,
554 '_PVT'
555 );
556 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
557 x_return_status := OKL_API.HANDLE_EXCEPTIONS
558 (
559 l_api_name,
560 G_PKG_NAME,
561 'OKL_API.G_RET_STS_UNEXP_ERROR',
562 x_msg_count,
563 x_msg_data,
564 '_PVT'
565 );
566 WHEN OTHERS THEN
567 x_return_status := OKL_API.HANDLE_EXCEPTIONS
568 (
569 l_api_name,
570 G_PKG_NAME,
571 'OTHERS',
572 x_msg_count,
573 x_msg_data,
574 '_PVT'
575 );
576 END validate_row;
577 -------------------------------------------------------------
578 -- PL/SQL TBL validate_row for:okl_pmt_channel_methods --
579 -------------------------------------------------------------
580 PROCEDURE validate_row(
581 p_api_version IN NUMBER,
582 p_init_msg_list IN VARCHAR2,
583 x_return_status OUT NOCOPY VARCHAR2,
584 x_msg_count OUT NOCOPY NUMBER,
585 x_msg_data OUT NOCOPY VARCHAR2,
586 p_pcm_tbl IN pcm_tbl_type,
587 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
588
589 l_api_version CONSTANT NUMBER := 1;
590 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
591 i NUMBER := 0;
592 BEGIN
593 OKL_API.init_msg_list(p_init_msg_list);
594 -- Make sure PL/SQL table has records in it before passing
595 IF (p_pcm_tbl.COUNT > 0) THEN
596 i := p_pcm_tbl.FIRST;
597 LOOP
598 DECLARE
599 l_error_rec OKL_API.ERROR_REC_TYPE;
600 BEGIN
601 l_error_rec.api_name := l_api_name;
602 l_error_rec.api_package := G_PKG_NAME;
603 l_error_rec.idx := i;
604 validate_row (
605 p_api_version => p_api_version,
606 p_init_msg_list => OKL_API.G_FALSE,
607 x_return_status => l_error_rec.error_type,
608 x_msg_count => l_error_rec.msg_count,
609 x_msg_data => l_error_rec.msg_data,
610 p_pcm_rec => p_pcm_tbl(i));
611 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
612 l_error_rec.sqlcode := SQLCODE;
613 load_error_tbl(l_error_rec, px_error_tbl);
614 ELSE
615 x_msg_count := l_error_rec.msg_count;
616 x_msg_data := l_error_rec.msg_data;
617 END IF;
618 EXCEPTION
619 WHEN OKL_API.G_EXCEPTION_ERROR THEN
620 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
621 l_error_rec.sqlcode := SQLCODE;
622 load_error_tbl(l_error_rec, px_error_tbl);
623 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
624 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
625 l_error_rec.sqlcode := SQLCODE;
626 load_error_tbl(l_error_rec, px_error_tbl);
627 WHEN OTHERS THEN
628 l_error_rec.error_type := 'OTHERS';
629 l_error_rec.sqlcode := SQLCODE;
630 load_error_tbl(l_error_rec, px_error_tbl);
631 END;
632 EXIT WHEN (i = p_pcm_tbl.LAST);
633 i := p_pcm_tbl.NEXT(i);
634 END LOOP;
635 END IF;
636 -- Loop through the error_tbl to find the error with the highest severity
637 -- and return it.
638 x_return_status := find_highest_exception(px_error_tbl);
639 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
640 EXCEPTION
641 WHEN OKL_API.G_EXCEPTION_ERROR THEN
642 x_return_status := OKL_API.HANDLE_EXCEPTIONS
643 (
644 l_api_name,
645 G_PKG_NAME,
646 'OKL_API.G_RET_STS_ERROR',
647 x_msg_count,
648 x_msg_data,
649 '_PVT'
650 );
651 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
652 x_return_status := OKL_API.HANDLE_EXCEPTIONS
653 (
654 l_api_name,
655 G_PKG_NAME,
656 'OKL_API.G_RET_STS_UNEXP_ERROR',
657 x_msg_count,
658 x_msg_data,
659 '_PVT'
660 );
661 WHEN OTHERS THEN
662 x_return_status := OKL_API.HANDLE_EXCEPTIONS
663 (
664 l_api_name,
665 G_PKG_NAME,
666 'OTHERS',
667 x_msg_count,
668 x_msg_data,
669 '_PVT'
670 );
671 END validate_row;
672
673 -------------------------------------------------------------
674 -- PL/SQL TBL validate_row for:okl_pmt_channel_methods --
675 -------------------------------------------------------------
676 PROCEDURE validate_row(
677 p_api_version IN NUMBER,
678 p_init_msg_list IN VARCHAR2,
679 x_return_status OUT NOCOPY VARCHAR2,
680 x_msg_count OUT NOCOPY NUMBER,
681 x_msg_data OUT NOCOPY VARCHAR2,
682 p_pcm_tbl IN pcm_tbl_type) IS
683
684 l_api_version CONSTANT NUMBER := 1;
685 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
686 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
687 l_error_tbl OKL_API.ERROR_TBL_TYPE;
688 BEGIN
689 OKL_API.init_msg_list(p_init_msg_list);
690 -- Make sure PL/SQL table has records in it before passing
691 IF (p_pcm_tbl.COUNT > 0) THEN
692 validate_row (
693 p_api_version => p_api_version,
694 p_init_msg_list => OKL_API.G_FALSE,
695 x_return_status => x_return_status,
696 x_msg_count => x_msg_count,
697 x_msg_data => x_msg_data,
698 p_pcm_tbl => p_pcm_tbl,
699 px_error_tbl => l_error_tbl);
700 END IF;
701 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
702 EXCEPTION
703 WHEN OKL_API.G_EXCEPTION_ERROR THEN
704 x_return_status := OKL_API.HANDLE_EXCEPTIONS
705 (
706 l_api_name,
707 G_PKG_NAME,
708 'OKL_API.G_RET_STS_ERROR',
709 x_msg_count,
710 x_msg_data,
711 '_PVT'
712 );
713 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
714 x_return_status := OKL_API.HANDLE_EXCEPTIONS
715 (
716 l_api_name,
717 G_PKG_NAME,
718 'OKL_API.G_RET_STS_UNEXP_ERROR',
719 x_msg_count,
720 x_msg_data,
721 '_PVT'
722 );
723 WHEN OTHERS THEN
724 x_return_status := OKL_API.HANDLE_EXCEPTIONS
725 (
726 l_api_name,
727 G_PKG_NAME,
728 'OTHERS',
729 x_msg_count,
730 x_msg_data,
731 '_PVT'
732 );
733 END validate_row;
734
735 ---------------------------------------------------------------------------
736 -- PROCEDURE insert_row
737 ---------------------------------------------------------------------------
738 ----------------------------------------------
739 -- insert_row for:okl_pmt_channel_methods --
740 ----------------------------------------------
741 PROCEDURE insert_row(
742 p_api_version IN NUMBER,
743 p_init_msg_list IN VARCHAR2,
744 x_return_status OUT NOCOPY VARCHAR2,
745 x_msg_count OUT NOCOPY NUMBER,
746 x_msg_data OUT NOCOPY VARCHAR2,
747 p_pcm_rec IN pcm_rec_type,
748 x_pcm_rec OUT NOCOPY pcm_rec_type) IS
749
750 l_api_version CONSTANT NUMBER := 1;
751 l_api_name CONSTANT VARCHAR2(30) := 'insert_row';
752 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
753 l_pcm_rec pcm_rec_type := p_pcm_rec;
754 l_def_pcm_rec pcm_rec_type;
755 --------------------------------------------------
756 -- Set_Attributes for:okl_pmt_channel_methods --
757 --------------------------------------------------
758 FUNCTION Set_Attributes (
759 p_pcm_rec IN pcm_rec_type,
760 x_pcm_rec OUT NOCOPY pcm_rec_type
761 ) RETURN VARCHAR2 IS
762 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
763 BEGIN
764 x_pcm_rec := p_pcm_rec;
765 x_pcm_rec.object_version_number := 1;
766 RETURN(l_return_status);
767 END Set_Attributes;
768
769 -------------------------------
770 -- FUNCTION fill_who_columns --
771 -------------------------------
772 FUNCTION fill_who_columns (
773 p_pcm_rec IN pcm_rec_type
774 ) RETURN pcm_rec_type IS
775 l_pcm_rec pcm_rec_type := p_pcm_rec;
776 BEGIN
777 l_pcm_rec.CREATION_DATE := SYSDATE;
778 l_pcm_rec.CREATED_BY := FND_GLOBAL.USER_ID;
779 l_pcm_rec.LAST_UPDATE_DATE := l_pcm_rec.CREATION_DATE;
780 l_pcm_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
781 l_pcm_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
782 RETURN(l_pcm_rec);
783 END fill_who_columns;
784
785 BEGIN
786 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
787 p_init_msg_list,
788 '_PVT',
789 x_return_status);
790 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
791 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
792 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
793 RAISE OKL_API.G_EXCEPTION_ERROR;
794 END IF;
795 l_def_pcm_rec := null_out_defaults(p_pcm_rec);
796 -- Set primary key value
797 l_def_pcm_rec.ID := get_seq_id;
798 -- Setting item attributes
799 l_return_Status := Set_Attributes(
800 l_def_pcm_rec, -- IN
801 l_pcm_rec); -- OUT
802 --- If any errors happen abort API
803 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
804 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
805 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
806 RAISE OKL_API.G_EXCEPTION_ERROR;
807 END IF;
808 l_pcm_rec := fill_who_columns(l_pcm_rec);
809 --- Validate all non-missing attributes (Item Level Validation)
810 l_return_status := Validate_Attributes(l_pcm_rec);
811 --- If any errors happen abort API
812 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
813 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
814 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
815 RAISE OKL_API.G_EXCEPTION_ERROR;
816 END IF;
817 l_return_status := Validate_Record(l_pcm_rec);
818 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
819 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
820 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
821 RAISE OKL_API.G_EXCEPTION_ERROR;
822 END IF;
823 INSERT INTO okl_pmt_channel_methods(
824 id,
825 object_version_number,
826 created_by,
827 creation_date,
828 last_updated_by,
829 last_update_date,
830 last_update_login,
831 org_id,
832 payment_channel_code,
833 receipt_method_id
834 )
835 VALUES (
836 l_pcm_rec.id,
837 l_pcm_rec.object_version_number,
838 l_pcm_rec.created_by,
839 l_pcm_rec.creation_date,
840 l_pcm_rec.last_updated_by,
841 l_pcm_rec.last_update_date,
842 l_pcm_rec.last_update_login,
843 l_pcm_rec.org_id,
844 l_pcm_rec.payment_channel_code,
845 l_pcm_rec.receipt_method_id
846 );
847 -- Set OUT values
848 x_pcm_rec := l_pcm_rec;
849 x_return_status := l_return_status;
850 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
851 EXCEPTION
852 WHEN OKL_API.G_EXCEPTION_ERROR THEN
853 x_return_status := OKL_API.HANDLE_EXCEPTIONS
854 (
855 l_api_name,
856 G_PKG_NAME,
857 'OKL_API.G_RET_STS_ERROR',
858 x_msg_count,
859 x_msg_data,
860 '_PVT'
861 );
862 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
863 x_return_status := OKL_API.HANDLE_EXCEPTIONS
864 (
865 l_api_name,
866 G_PKG_NAME,
867 'OKL_API.G_RET_STS_UNEXP_ERROR',
868 x_msg_count,
869 x_msg_data,
870 '_PVT'
871 );
872 WHEN OTHERS THEN
873 x_return_status := OKL_API.HANDLE_EXCEPTIONS
874 (
875 l_api_name,
876 G_PKG_NAME,
877 'OTHERS',
878 x_msg_count,
879 x_msg_data,
880 '_PVT'
881 );
882 END insert_row;
883 ----------------------------------------------------------
884 -- PL/SQL TBL insert_row for: okl_pmt_channel_methods --
885 ----------------------------------------------------------
886 PROCEDURE insert_row(
887 p_api_version IN NUMBER,
888 p_init_msg_list IN VARCHAR2,
889 x_return_status OUT NOCOPY VARCHAR2,
890 x_msg_count OUT NOCOPY NUMBER,
891 x_msg_data OUT NOCOPY VARCHAR2,
892 p_pcm_tbl IN pcm_tbl_type,
893 x_pcm_tbl OUT NOCOPY pcm_tbl_type,
894 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
895
896 l_api_version CONSTANT NUMBER := 1;
897 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
898 i NUMBER := 0;
899 BEGIN
900 OKL_API.init_msg_list(p_init_msg_list);
901 -- Make sure PL/SQL table has records in it before passing
902 IF (p_pcm_tbl.COUNT > 0) THEN
903 i := p_pcm_tbl.FIRST;
904 LOOP
905 DECLARE
906 l_error_rec OKL_API.ERROR_REC_TYPE;
907 BEGIN
908 l_error_rec.api_name := l_api_name;
909 l_error_rec.api_package := G_PKG_NAME;
910 l_error_rec.idx := i;
911 insert_row (
912 p_api_version => p_api_version,
913 p_init_msg_list => OKL_API.G_FALSE,
914 x_return_status => l_error_rec.error_type,
915 x_msg_count => l_error_rec.msg_count,
916 x_msg_data => l_error_rec.msg_data,
917 p_pcm_rec => p_pcm_tbl(i),
918 x_pcm_rec => x_pcm_tbl(i));
919 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
920 l_error_rec.sqlcode := SQLCODE;
921 load_error_tbl(l_error_rec, px_error_tbl);
922 ELSE
923 x_msg_count := l_error_rec.msg_count;
924 x_msg_data := l_error_rec.msg_data;
925 END IF;
926 EXCEPTION
927 WHEN OKL_API.G_EXCEPTION_ERROR THEN
928 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
929 l_error_rec.sqlcode := SQLCODE;
930 load_error_tbl(l_error_rec, px_error_tbl);
931 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
932 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
933 l_error_rec.sqlcode := SQLCODE;
934 load_error_tbl(l_error_rec, px_error_tbl);
935 WHEN OTHERS THEN
936 l_error_rec.error_type := 'OTHERS';
937 l_error_rec.sqlcode := SQLCODE;
938 load_error_tbl(l_error_rec, px_error_tbl);
939 END;
940 EXIT WHEN (i = p_pcm_tbl.LAST);
941 i := p_pcm_tbl.NEXT(i);
942 END LOOP;
943 END IF;
944 -- Loop through the error_tbl to find the error with the highest severity
945 -- and return it.
946 x_return_status := find_highest_exception(px_error_tbl);
947 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
948 EXCEPTION
949 WHEN OKL_API.G_EXCEPTION_ERROR THEN
950 x_return_status := OKL_API.HANDLE_EXCEPTIONS
951 (
952 l_api_name,
953 G_PKG_NAME,
954 'OKL_API.G_RET_STS_ERROR',
955 x_msg_count,
956 x_msg_data,
957 '_PVT'
958 );
959 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
960 x_return_status := OKL_API.HANDLE_EXCEPTIONS
961 (
962 l_api_name,
963 G_PKG_NAME,
964 'OKL_API.G_RET_STS_UNEXP_ERROR',
965 x_msg_count,
966 x_msg_data,
967 '_PVT'
968 );
969 WHEN OTHERS THEN
970 x_return_status := OKL_API.HANDLE_EXCEPTIONS
971 (
972 l_api_name,
973 G_PKG_NAME,
974 'OTHERS',
975 x_msg_count,
976 x_msg_data,
977 '_PVT'
978 );
979 END insert_row;
980
981 ----------------------------------------------------------
982 -- PL/SQL TBL insert_row for:okl_pmt_channel_methods --
983 ----------------------------------------------------------
984 -- This procedure is the same as the one above except it does not have a "px_error_tbl" argument.
985 -- This procedure was create for backward compatibility and simply is a wrapper for the one above.
986 PROCEDURE insert_row(
987 p_api_version IN NUMBER,
988 p_init_msg_list IN VARCHAR2,
989 x_return_status OUT NOCOPY VARCHAR2,
990 x_msg_count OUT NOCOPY NUMBER,
991 x_msg_data OUT NOCOPY VARCHAR2,
992 p_pcm_tbl IN pcm_tbl_type,
993 x_pcm_tbl OUT NOCOPY pcm_tbl_type) IS
994
995 l_api_version CONSTANT NUMBER := 1;
996 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
997 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
998 l_error_tbl OKL_API.ERROR_TBL_TYPE;
999 BEGIN
1000 OKL_API.init_msg_list(p_init_msg_list);
1001 -- Make sure PL/SQL table has records in it before passing
1002 IF (p_pcm_tbl.COUNT > 0) THEN
1003 insert_row (
1004 p_api_version => p_api_version,
1005 p_init_msg_list => OKL_API.G_FALSE,
1006 x_return_status => x_return_status,
1007 x_msg_count => x_msg_count,
1008 x_msg_data => x_msg_data,
1009 p_pcm_tbl => p_pcm_tbl,
1010 x_pcm_tbl => x_pcm_tbl,
1011 px_error_tbl => l_error_tbl);
1012 END IF;
1013 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1014 EXCEPTION
1015 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1016 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1017 (
1018 l_api_name,
1019 G_PKG_NAME,
1020 'OKL_API.G_RET_STS_ERROR',
1021 x_msg_count,
1022 x_msg_data,
1023 '_PVT'
1024 );
1025 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1026 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1027 (
1028 l_api_name,
1029 G_PKG_NAME,
1030 'OKL_API.G_RET_STS_UNEXP_ERROR',
1031 x_msg_count,
1032 x_msg_data,
1033 '_PVT'
1034 );
1035 WHEN OTHERS THEN
1036 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1037 (
1038 l_api_name,
1039 G_PKG_NAME,
1040 'OTHERS',
1041 x_msg_count,
1042 x_msg_data,
1043 '_PVT'
1044 );
1045 END insert_row;
1046
1047 ---------------------------------------------------------------------------
1048 -- PROCEDURE lock_row
1049 ---------------------------------------------------------------------------
1050 --------------------------------------------
1051 -- lock_row for:okl_pmt_channel_methods --
1052 --------------------------------------------
1053 PROCEDURE lock_row(
1054 p_init_msg_list IN VARCHAR2,
1055 x_return_status OUT NOCOPY VARCHAR2,
1056 x_msg_count OUT NOCOPY NUMBER,
1057 x_msg_data OUT NOCOPY VARCHAR2,
1058 p_pcm_rec IN pcm_rec_type) IS
1059
1060 E_Resource_Busy EXCEPTION;
1061 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1062 CURSOR lock_csr (p_pcm_rec IN pcm_rec_type) IS
1063 SELECT OBJECT_VERSION_NUMBER
1064 FROM okl_pmt_channel_methods
1065 WHERE ID = p_pcm_rec.id
1066 AND OBJECT_VERSION_NUMBER = p_pcm_rec.object_version_number
1067 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1068
1069 CURSOR lchk_csr (p_pcm_rec IN pcm_rec_type) IS
1070 SELECT OBJECT_VERSION_NUMBER
1071 FROM okl_pmt_channel_methods
1072 WHERE ID = p_pcm_rec.id;
1073 l_api_version CONSTANT NUMBER := 1;
1074 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1075 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1076 l_object_version_number okl_pmt_channel_methods.OBJECT_VERSION_NUMBER%TYPE;
1077 lc_object_version_number okl_pmt_channel_methods.OBJECT_VERSION_NUMBER%TYPE;
1078 l_row_notfound BOOLEAN := FALSE;
1079 lc_row_notfound BOOLEAN := FALSE;
1080 BEGIN
1081 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1082 p_init_msg_list,
1083 '_PVT',
1084 x_return_status);
1085 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1086 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1087 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1088 RAISE OKL_API.G_EXCEPTION_ERROR;
1089 END IF;
1090 BEGIN
1091 OPEN lock_csr(p_pcm_rec);
1092 FETCH lock_csr INTO l_object_version_number;
1093 l_row_notfound := lock_csr%NOTFOUND;
1094 CLOSE lock_csr;
1095 EXCEPTION
1096 WHEN E_Resource_Busy THEN
1097 IF (lock_csr%ISOPEN) THEN
1098 CLOSE lock_csr;
1099 END IF;
1100 OKL_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1101 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1102 END;
1103
1104 IF ( l_row_notfound ) THEN
1105 OPEN lchk_csr(p_pcm_rec);
1106 FETCH lchk_csr INTO lc_object_version_number;
1107 lc_row_notfound := lchk_csr%NOTFOUND;
1108 CLOSE lchk_csr;
1109 END IF;
1110 IF (lc_row_notfound) THEN
1111 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1112 RAISE OKL_API.G_EXCEPTION_ERROR;
1113 ELSIF lc_object_version_number > p_pcm_rec.object_version_number THEN
1114 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1115 RAISE OKL_API.G_EXCEPTION_ERROR;
1116 ELSIF lc_object_version_number <> p_pcm_rec.object_version_number THEN
1117 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1118 RAISE OKL_API.G_EXCEPTION_ERROR;
1119 ELSIF lc_object_version_number = -1 THEN
1120 OKL_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1121 RAISE OKL_API.G_EXCEPTION_ERROR;
1122 END IF;
1123 x_return_status := l_return_status;
1124 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1125 EXCEPTION
1126 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1127 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1128 (
1129 l_api_name,
1130 G_PKG_NAME,
1131 'OKL_API.G_RET_STS_ERROR',
1132 x_msg_count,
1133 x_msg_data,
1134 '_PVT'
1135 );
1136 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1137 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1138 (
1139 l_api_name,
1140 G_PKG_NAME,
1141 'OKL_API.G_RET_STS_UNEXP_ERROR',
1142 x_msg_count,
1143 x_msg_data,
1144 '_PVT'
1145 );
1146 WHEN OTHERS THEN
1147 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1148 (
1149 l_api_name,
1150 G_PKG_NAME,
1151 'OTHERS',
1152 x_msg_count,
1153 x_msg_data,
1154 '_PVT'
1155 );
1156 END lock_row;
1157 -----------------------------------------------
1158 -- lock_row for: okl_pmt_channel_methods --
1159 -----------------------------------------------
1160 PROCEDURE lock_row(
1161 p_api_version IN NUMBER,
1162 p_init_msg_list IN VARCHAR2,
1163 x_return_status OUT NOCOPY VARCHAR2,
1164 x_msg_count OUT NOCOPY NUMBER,
1165 x_msg_data OUT NOCOPY VARCHAR2,
1166 p_pcm_rec IN pcm_rec_type) IS
1167
1168 l_api_version CONSTANT NUMBER := 1;
1169 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1170 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1171 l_pcm_rec pcm_rec_type := p_pcm_rec;
1172 BEGIN
1173 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1174 G_PKG_NAME,
1175 p_init_msg_list,
1176 l_api_version,
1177 p_api_version,
1178 '_PVT',
1179 x_return_status);
1180 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1181 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1182 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1183 RAISE OKL_API.G_EXCEPTION_ERROR;
1184 END IF;
1185 ---------------------------------------------
1186 -- Call the LOCK_ROW for each child record --
1187 ---------------------------------------------
1188 lock_row(
1189 p_init_msg_list,
1190 l_return_status,
1191 x_msg_count,
1192 x_msg_data,
1193 l_pcm_rec
1194 );
1195 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1196 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1197 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1198 RAISE OKL_API.G_EXCEPTION_ERROR;
1199 END IF;
1200 x_return_status := l_return_status;
1201 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1202 EXCEPTION
1203 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1204 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1205 (
1206 l_api_name,
1207 G_PKG_NAME,
1208 'OKL_API.G_RET_STS_ERROR',
1209 x_msg_count,
1210 x_msg_data,
1211 '_PVT'
1212 );
1213 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1214 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1215 (
1216 l_api_name,
1217 G_PKG_NAME,
1218 'OKL_API.G_RET_STS_UNEXP_ERROR',
1219 x_msg_count,
1220 x_msg_data,
1221 '_PVT'
1222 );
1223 WHEN OTHERS THEN
1224 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1225 (
1226 l_api_name,
1227 G_PKG_NAME,
1228 'OTHERS',
1229 x_msg_count,
1230 x_msg_data,
1231 '_PVT'
1232 );
1233 END lock_row;
1234 --------------------------------------------------------
1235 -- PL/SQL TBL lock_row for:okl_pmt_channel_methods --
1236 --------------------------------------------------------
1237 PROCEDURE lock_row(
1238 p_api_version IN NUMBER,
1239 p_init_msg_list IN VARCHAR2,
1240 x_return_status OUT NOCOPY VARCHAR2,
1241 x_msg_count OUT NOCOPY NUMBER,
1242 x_msg_data OUT NOCOPY VARCHAR2,
1243 p_pcm_tbl IN pcm_tbl_type,
1244 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1245
1246 l_api_version CONSTANT NUMBER := 1;
1247 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
1248 i NUMBER := 0;
1249 BEGIN
1250 OKL_API.init_msg_list(p_init_msg_list);
1251 -- Make sure PL/SQL table has recrods in it before passing
1252 IF (p_pcm_tbl.COUNT > 0) THEN
1253 i := p_pcm_tbl.FIRST;
1254 LOOP
1255 DECLARE
1256 l_error_rec OKL_API.ERROR_REC_TYPE;
1257 BEGIN
1258 l_error_rec.api_name := l_api_name;
1259 l_error_rec.api_package := G_PKG_NAME;
1260 l_error_rec.idx := i;
1261 lock_row(
1262 p_api_version => p_api_version,
1263 p_init_msg_list => OKL_API.G_FALSE,
1264 x_return_status => l_error_rec.error_type,
1265 x_msg_count => l_error_rec.msg_count,
1266 x_msg_data => l_error_rec.msg_data,
1267 p_pcm_rec => p_pcm_tbl(i));
1268 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
1269 l_error_rec.sqlcode := SQLCODE;
1270 load_error_tbl(l_error_rec, px_error_tbl);
1271 ELSE
1272 x_msg_count := l_error_rec.msg_count;
1273 x_msg_data := l_error_rec.msg_data;
1274 END IF;
1275 EXCEPTION
1276 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1277 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
1278 l_error_rec.sqlcode := SQLCODE;
1279 load_error_tbl(l_error_rec, px_error_tbl);
1280 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1281 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
1282 l_error_rec.sqlcode := SQLCODE;
1283 load_error_tbl(l_error_rec, px_error_tbl);
1284 WHEN OTHERS THEN
1285 l_error_rec.error_type := 'OTHERS';
1286 l_error_rec.sqlcode := SQLCODE;
1287 load_error_tbl(l_error_rec, px_error_tbl);
1288 END;
1289 EXIT WHEN (i = p_pcm_tbl.LAST);
1290 i := p_pcm_tbl.NEXT(i);
1291 END LOOP;
1292 END IF;
1293 -- Loop through the error_tbl to find the error with the highest severity
1294 -- and return it.
1295 x_return_status := find_highest_exception(px_error_tbl);
1296 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1297 EXCEPTION
1298 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1299 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1300 (
1301 l_api_name,
1302 G_PKG_NAME,
1303 'OKL_API.G_RET_STS_ERROR',
1304 x_msg_count,
1305 x_msg_data,
1306 '_PVT'
1307 );
1308 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1309 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1310 (
1311 l_api_name,
1312 G_PKG_NAME,
1313 'OKL_API.G_RET_STS_UNEXP_ERROR',
1314 x_msg_count,
1315 x_msg_data,
1316 '_PVT'
1317 );
1318 WHEN OTHERS THEN
1319 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1320 (
1321 l_api_name,
1322 G_PKG_NAME,
1323 'OTHERS',
1324 x_msg_count,
1325 x_msg_data,
1326 '_PVT'
1327 );
1328 END lock_row;
1329 --------------------------------------------------------
1330 -- PL/SQL TBL lock_row for:okl_pmt_channel_methods --
1331 --------------------------------------------------------
1332 PROCEDURE lock_row(
1333 p_api_version IN NUMBER,
1334 p_init_msg_list IN VARCHAR2,
1335 x_return_status OUT NOCOPY VARCHAR2,
1336 x_msg_count OUT NOCOPY NUMBER,
1337 x_msg_data OUT NOCOPY VARCHAR2,
1338 p_pcm_tbl IN pcm_tbl_type) IS
1339
1340 l_api_version CONSTANT NUMBER := 1;
1341 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
1342 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1343 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1344 BEGIN
1345 OKL_API.init_msg_list(p_init_msg_list);
1346 -- Make sure PL/SQL table has recrods in it before passing
1347 IF (p_pcm_tbl.COUNT > 0) THEN
1348 lock_row(
1349 p_api_version => p_api_version,
1350 p_init_msg_list => OKL_API.G_FALSE,
1351 x_return_status => x_return_status,
1352 x_msg_count => x_msg_count,
1353 x_msg_data => x_msg_data,
1354 p_pcm_tbl => p_pcm_tbl,
1355 px_error_tbl => l_error_tbl);
1356 END IF;
1357 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1358 EXCEPTION
1359 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1360 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1361 (
1362 l_api_name,
1363 G_PKG_NAME,
1364 'OKL_API.G_RET_STS_ERROR',
1365 x_msg_count,
1366 x_msg_data,
1367 '_PVT'
1368 );
1369 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1370 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1371 (
1372 l_api_name,
1373 G_PKG_NAME,
1374 'OKL_API.G_RET_STS_UNEXP_ERROR',
1375 x_msg_count,
1376 x_msg_data,
1377 '_PVT'
1378 );
1379 WHEN OTHERS THEN
1380 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1381 (
1382 l_api_name,
1383 G_PKG_NAME,
1384 'OTHERS',
1385 x_msg_count,
1386 x_msg_data,
1387 '_PVT'
1388 );
1389 END lock_row;
1390 ---------------------------------------------------------------------------
1391 -- PROCEDURE update_row
1392 ---------------------------------------------------------------------------
1393 ----------------------------------------------
1394 -- update_row for:okl_pmt_channel_methods --
1395 ----------------------------------------------
1396 PROCEDURE update_row(
1397 p_api_version IN NUMBER,
1398 p_init_msg_list IN VARCHAR2,
1399 x_return_status OUT NOCOPY VARCHAR2,
1400 x_msg_count OUT NOCOPY NUMBER,
1401 x_msg_data OUT NOCOPY VARCHAR2,
1402 p_pcm_rec IN pcm_rec_type,
1403 x_pcm_rec OUT NOCOPY pcm_rec_type) IS
1404
1405 l_api_version CONSTANT NUMBER := 1;
1406 l_api_name CONSTANT VARCHAR2(30) := 'update_row';
1407 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1408 l_pcm_rec pcm_rec_type := p_pcm_rec;
1409 l_def_pcm_rec pcm_rec_type;
1410 l_row_notfound BOOLEAN := TRUE;
1411 ----------------------------------
1412 -- FUNCTION populate_new_record --
1413 ----------------------------------
1414 FUNCTION populate_new_record (
1415 p_pcm_rec IN pcm_rec_type,
1416 x_pcm_rec OUT NOCOPY pcm_rec_type
1417 ) RETURN VARCHAR2 IS
1418 l_pcm_rec pcm_rec_type;
1419 l_row_notfound BOOLEAN := TRUE;
1420 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1421 BEGIN
1422 x_pcm_rec := p_pcm_rec;
1423 -- Get current database values
1424 l_pcm_rec := get_rec(p_pcm_rec, l_return_status);
1425 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
1426 IF (x_pcm_rec.id = OKL_API.G_MISS_NUM)
1427 THEN
1428 x_pcm_rec.id := l_pcm_rec.id;
1429 END IF;
1430 IF (x_pcm_rec.object_version_number = OKL_API.G_MISS_NUM)
1431 THEN
1432 x_pcm_rec.object_version_number := l_pcm_rec.object_version_number;
1433 END IF;
1434 IF (x_pcm_rec.org_id = OKL_API.G_MISS_NUM)
1435 THEN
1436 x_pcm_rec.org_id := l_pcm_rec.org_id;
1437 END IF;
1438 IF (x_pcm_rec.receipt_method_id = OKL_API.G_MISS_NUM)
1439 THEN
1440 x_pcm_rec.receipt_method_id := l_pcm_rec.receipt_method_id;
1441 END IF;
1442 IF (x_pcm_rec.payment_channel_code = OKL_API.G_MISS_CHAR)
1443 THEN
1444 x_pcm_rec.payment_channel_code := l_pcm_rec.payment_channel_code;
1445 END IF;
1446 IF (x_pcm_rec.created_by = OKL_API.G_MISS_NUM)
1447 THEN
1448 x_pcm_rec.created_by := l_pcm_rec.created_by;
1449 END IF;
1450 IF (x_pcm_rec.creation_date = OKL_API.G_MISS_DATE)
1451 THEN
1452 x_pcm_rec.creation_date := l_pcm_rec.creation_date;
1453 END IF;
1454 IF (x_pcm_rec.last_updated_by = OKL_API.G_MISS_NUM)
1455 THEN
1456 x_pcm_rec.last_updated_by := l_pcm_rec.last_updated_by;
1457 END IF;
1458 IF (x_pcm_rec.last_update_date = OKL_API.G_MISS_DATE)
1459 THEN
1460 x_pcm_rec.last_update_date := l_pcm_rec.last_update_date;
1461 END IF;
1462 IF (x_pcm_rec.last_update_login = OKL_API.G_MISS_NUM)
1463 THEN
1464 x_pcm_rec.last_update_login := l_pcm_rec.last_update_login;
1465 END IF;
1466 END IF;
1467 RETURN(l_return_status);
1468 END populate_new_record;
1469 --------------------------------------------------
1470 -- Set_Attributes for:okl_pmt_channel_methods --
1471 --------------------------------------------------
1472 FUNCTION Set_Attributes (
1473 p_pcm_rec IN pcm_rec_type,
1474 x_pcm_rec OUT NOCOPY pcm_rec_type
1475 ) RETURN VARCHAR2 IS
1476 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1477 BEGIN
1478 x_pcm_rec := p_pcm_rec;
1479 x_pcm_rec.LAST_UPDATE_DATE := SYSDATE;
1480 x_pcm_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1481 x_pcm_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1482 RETURN(l_return_status);
1483 END Set_Attributes;
1484 BEGIN
1485 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1486 p_init_msg_list,
1487 '_PVT',
1488 x_return_status);
1489 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1490 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1491 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1492 RAISE OKL_API.G_EXCEPTION_ERROR;
1493 END IF;
1494
1495 l_return_status := populate_new_record(p_pcm_rec, l_pcm_rec);
1496 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1497 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1498 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1499 RAISE OKL_API.G_EXCEPTION_ERROR;
1500 END IF;
1501
1502 --- Setting item attributes
1503 l_return_status := Set_Attributes(
1504 l_pcm_rec, -- IN
1505 l_def_pcm_rec); -- OUT
1506 --- If any errors happen abort API
1507 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1508 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1509 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1510 RAISE OKL_API.G_EXCEPTION_ERROR;
1511 END IF;
1512
1513 --- Validate all non-missing attributes (Item Level Validation)
1514 l_return_status := Validate_Attributes(l_def_pcm_rec);
1515 --- If any errors happen abort API
1516 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1517 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1518 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1519 RAISE OKL_API.G_EXCEPTION_ERROR;
1520 END IF;
1521 l_return_status := Validate_Record(l_def_pcm_rec);
1522 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1523 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1524 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1525 RAISE OKL_API.G_EXCEPTION_ERROR;
1526 END IF;
1527
1528 -- Lock the Record
1529 lock_row(
1530 p_api_version => p_api_version,
1531 p_init_msg_list => p_init_msg_list,
1532 x_return_status => l_return_status,
1533 x_msg_count => x_msg_count,
1534 x_msg_data => x_msg_data,
1535 p_pcm_rec => l_def_pcm_rec); -- RMUNJULU Changed to pass lx_pcm_rec
1536 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1537 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1538 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1539 RAISE OKL_API.G_EXCEPTION_ERROR;
1540 END IF;
1541
1542 UPDATE okl_pmt_channel_methods
1543 SET OBJECT_VERSION_NUMBER = l_def_pcm_rec.object_version_number + 1,
1544 ORG_ID = l_def_pcm_rec.org_id,
1545 RECEIPT_METHOD_ID = l_def_pcm_rec.receipt_method_id,
1546 payment_channel_code = l_def_pcm_rec.payment_channel_code,
1547 CREATED_BY = l_def_pcm_rec.created_by,
1548 CREATION_DATE = l_def_pcm_rec.creation_date,
1549 LAST_UPDATED_BY = l_def_pcm_rec.last_updated_by,
1550 LAST_UPDATE_DATE = l_def_pcm_rec.last_update_date,
1551 LAST_UPDATE_LOGIN = l_def_pcm_rec.last_update_login
1552 WHERE ID = l_def_pcm_rec.id;
1553
1554 x_pcm_rec := l_def_pcm_rec;
1555 x_return_status := l_return_status;
1556 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1557 EXCEPTION
1558 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1559 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1560 (
1561 l_api_name,
1562 G_PKG_NAME,
1563 'OKL_API.G_RET_STS_ERROR',
1564 x_msg_count,
1565 x_msg_data,
1566 '_PVT'
1567 );
1568 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1569 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1570 (
1571 l_api_name,
1572 G_PKG_NAME,
1573 'OKL_API.G_RET_STS_UNEXP_ERROR',
1574 x_msg_count,
1575 x_msg_data,
1576 '_PVT'
1577 );
1578 WHEN OTHERS THEN
1579 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1580 (
1581 l_api_name,
1582 G_PKG_NAME,
1583 'OTHERS',
1584 x_msg_count,
1585 x_msg_data,
1586 '_PVT'
1587 );
1588 END update_row;
1589 ----------------------------------------------------------
1590 -- PL/SQL TBL update_row for:okl_pmt_channel_methods --
1591 ----------------------------------------------------------
1592 PROCEDURE update_row(
1593 p_api_version IN NUMBER,
1594 p_init_msg_list IN VARCHAR2,
1595 x_return_status OUT NOCOPY VARCHAR2,
1596 x_msg_count OUT NOCOPY NUMBER,
1597 x_msg_data OUT NOCOPY VARCHAR2,
1598 p_pcm_tbl IN pcm_tbl_type,
1599 x_pcm_tbl OUT NOCOPY pcm_tbl_type,
1600 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1601
1602 l_api_version CONSTANT NUMBER := 1;
1603 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
1604 i NUMBER := 0;
1605 BEGIN
1606 OKL_API.init_msg_list(p_init_msg_list);
1607 -- Make sure PL/SQL table has records in it before passing
1608 IF (p_pcm_tbl.COUNT > 0) THEN
1609 i := p_pcm_tbl.FIRST;
1610 LOOP
1611 DECLARE
1612 l_error_rec OKL_API.ERROR_REC_TYPE;
1613 BEGIN
1614 l_error_rec.api_name := l_api_name;
1615 l_error_rec.api_package := G_PKG_NAME;
1616 l_error_rec.idx := i;
1617 update_row (
1618 p_api_version => p_api_version,
1619 p_init_msg_list => OKL_API.G_FALSE,
1620 x_return_status => l_error_rec.error_type,
1621 x_msg_count => l_error_rec.msg_count,
1622 x_msg_data => l_error_rec.msg_data,
1623 p_pcm_rec => p_pcm_tbl(i),
1624 x_pcm_rec => x_pcm_tbl(i));
1625 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
1626 l_error_rec.sqlcode := SQLCODE;
1627 load_error_tbl(l_error_rec, px_error_tbl);
1628 ELSE
1629 x_msg_count := l_error_rec.msg_count;
1630 x_msg_data := l_error_rec.msg_data;
1631 END IF;
1632 EXCEPTION
1633 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1634 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
1635 l_error_rec.sqlcode := SQLCODE;
1636 load_error_tbl(l_error_rec, px_error_tbl);
1637 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1638 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
1639 l_error_rec.sqlcode := SQLCODE;
1640 load_error_tbl(l_error_rec, px_error_tbl);
1641 WHEN OTHERS THEN
1642 l_error_rec.error_type := 'OTHERS';
1643 l_error_rec.sqlcode := SQLCODE;
1644 load_error_tbl(l_error_rec, px_error_tbl);
1645 END;
1646 EXIT WHEN (i = p_pcm_tbl.LAST);
1647 i := p_pcm_tbl.NEXT(i);
1648 END LOOP;
1649 END IF;
1650 -- Loop through the error_tbl to find the error with the highest severity
1651 -- and return it.
1652 x_return_status := find_highest_exception(px_error_tbl);
1653 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1654 EXCEPTION
1655 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1656 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1657 (
1658 l_api_name,
1659 G_PKG_NAME,
1660 'OKL_API.G_RET_STS_ERROR',
1661 x_msg_count,
1662 x_msg_data,
1663 '_PVT'
1664 );
1665 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1666 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1667 (
1668 l_api_name,
1669 G_PKG_NAME,
1670 'OKL_API.G_RET_STS_UNEXP_ERROR',
1671 x_msg_count,
1672 x_msg_data,
1673 '_PVT'
1674 );
1675 WHEN OTHERS THEN
1676 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1677 (
1678 l_api_name,
1679 G_PKG_NAME,
1680 'OTHERS',
1681 x_msg_count,
1682 x_msg_data,
1683 '_PVT'
1684 );
1685 END update_row;
1686
1687 ----------------------------------------------------------
1688 -- PL/SQL TBL update_row for:okl_pmt_channel_methods --
1689 ----------------------------------------------------------
1690 -- This procedure is the same as the one above except it does not have a "px_error_tbl" argument.
1691 -- This procedure was create for backward compatibility and simply is a wrapper for the one above.
1692 PROCEDURE update_row(
1693 p_api_version IN NUMBER,
1694 p_init_msg_list IN VARCHAR2,
1695 x_return_status OUT NOCOPY VARCHAR2,
1696 x_msg_count OUT NOCOPY NUMBER,
1697 x_msg_data OUT NOCOPY VARCHAR2,
1698 p_pcm_tbl IN pcm_tbl_type,
1699 x_pcm_tbl OUT NOCOPY pcm_tbl_type) IS
1700
1701 l_api_version CONSTANT NUMBER := 1;
1702 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
1703 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1704 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1705 BEGIN
1706 OKL_API.init_msg_list(p_init_msg_list);
1707 -- Make sure PL/SQL table has records in it before passing
1708 IF (p_pcm_tbl.COUNT > 0) THEN
1709 update_row (
1710 p_api_version => p_api_version,
1711 p_init_msg_list => OKL_API.G_FALSE,
1712 x_return_status => x_return_status,
1713 x_msg_count => x_msg_count,
1714 x_msg_data => x_msg_data,
1715 p_pcm_tbl => p_pcm_tbl,
1716 x_pcm_tbl => x_pcm_tbl,
1717 px_error_tbl => l_error_tbl);
1718 END IF;
1719 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1720 EXCEPTION
1721 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1722 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1723 (
1724 l_api_name,
1725 G_PKG_NAME,
1726 'OKL_API.G_RET_STS_ERROR',
1727 x_msg_count,
1728 x_msg_data,
1729 '_PVT'
1730 );
1731 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1732 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1733 (
1734 l_api_name,
1735 G_PKG_NAME,
1736 'OKL_API.G_RET_STS_UNEXP_ERROR',
1737 x_msg_count,
1738 x_msg_data,
1739 '_PVT'
1740 );
1741 WHEN OTHERS THEN
1742 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1743 (
1744 l_api_name,
1745 G_PKG_NAME,
1746 'OTHERS',
1747 x_msg_count,
1748 x_msg_data,
1749 '_PVT'
1750 );
1751 END update_row;
1752
1753 ---------------------------------------------------------------------------
1754 -- PROCEDURE delete_row
1755 ---------------------------------------------------------------------------
1756 ----------------------------------------------
1757 -- delete_row for:okl_pmt_channel_methods --
1758 ----------------------------------------------
1759 PROCEDURE delete_row(
1760 p_api_version IN NUMBER,
1761 p_init_msg_list IN VARCHAR2,
1762 x_return_status OUT NOCOPY VARCHAR2,
1763 x_msg_count OUT NOCOPY NUMBER,
1764 x_msg_data OUT NOCOPY VARCHAR2,
1765 p_pcm_rec IN pcm_rec_type) IS
1766
1767 l_api_version CONSTANT NUMBER := 1;
1768 l_api_name CONSTANT VARCHAR2(30) := 'delete_row';
1769 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1770 l_pcm_rec pcm_rec_type := p_pcm_rec;
1771 l_row_notfound BOOLEAN := TRUE;
1772 BEGIN
1773 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1774 p_init_msg_list,
1775 '_PVT',
1776 x_return_status);
1777 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1778 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1779 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1780 RAISE OKL_API.G_EXCEPTION_ERROR;
1781 END IF;
1782
1783 DELETE FROM okl_pmt_channel_methods
1784 WHERE ID = p_pcm_rec.id;
1785
1786 x_return_status := l_return_status;
1787 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1788 EXCEPTION
1789 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1790 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1791 (
1792 l_api_name,
1793 G_PKG_NAME,
1794 'OKL_API.G_RET_STS_ERROR',
1795 x_msg_count,
1796 x_msg_data,
1797 '_PVT'
1798 );
1799 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1800 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1801 (
1802 l_api_name,
1803 G_PKG_NAME,
1804 'OKL_API.G_RET_STS_UNEXP_ERROR',
1805 x_msg_count,
1806 x_msg_data,
1807 '_PVT'
1808 );
1809 WHEN OTHERS THEN
1810 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1811 (
1812 l_api_name,
1813 G_PKG_NAME,
1814 'OTHERS',
1815 x_msg_count,
1816 x_msg_data,
1817 '_PVT'
1818 );
1819 END delete_row;
1820 -----------------------------------------------------------
1821 -- PL/SQL TBL delete_row for:okl_pmt_channel_methods --
1822 -----------------------------------------------------------
1823 PROCEDURE delete_row(
1824 p_api_version IN NUMBER,
1825 p_init_msg_list IN VARCHAR2,
1826 x_return_status OUT NOCOPY VARCHAR2,
1827 x_msg_count OUT NOCOPY NUMBER,
1828 x_msg_data OUT NOCOPY VARCHAR2,
1829 p_pcm_tbl IN pcm_tbl_type,
1830 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1831
1832 l_api_version CONSTANT NUMBER := 1;
1833 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
1834 i NUMBER := 0;
1835 BEGIN
1836 OKL_API.init_msg_list(p_init_msg_list);
1837 -- Make sure PL/SQL table has records in it before passing
1838 IF (p_pcm_tbl.COUNT > 0) THEN
1839 i := p_pcm_tbl.FIRST;
1840 LOOP
1841 DECLARE
1842 l_error_rec OKL_API.ERROR_REC_TYPE;
1843 BEGIN
1844 l_error_rec.api_name := l_api_name;
1845 l_error_rec.api_package := G_PKG_NAME;
1846 l_error_rec.idx := i;
1847 delete_row (
1848 p_api_version => p_api_version,
1849 p_init_msg_list => OKL_API.G_FALSE,
1850 x_return_status => l_error_rec.error_type,
1851 x_msg_count => l_error_rec.msg_count,
1852 x_msg_data => l_error_rec.msg_data,
1853 p_pcm_rec => p_pcm_tbl(i));
1854 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
1855 l_error_rec.sqlcode := SQLCODE;
1856 load_error_tbl(l_error_rec, px_error_tbl);
1857 ELSE
1858 x_msg_count := l_error_rec.msg_count;
1859 x_msg_data := l_error_rec.msg_data;
1860 END IF;
1861 EXCEPTION
1862 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1863 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
1864 l_error_rec.sqlcode := SQLCODE;
1865 load_error_tbl(l_error_rec, px_error_tbl);
1866 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1867 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
1868 l_error_rec.sqlcode := SQLCODE;
1869 load_error_tbl(l_error_rec, px_error_tbl);
1870 WHEN OTHERS THEN
1871 l_error_rec.error_type := 'OTHERS';
1872 l_error_rec.sqlcode := SQLCODE;
1873 load_error_tbl(l_error_rec, px_error_tbl);
1874 END;
1875 EXIT WHEN (i = p_pcm_tbl.LAST);
1876 i := p_pcm_tbl.NEXT(i);
1877 END LOOP;
1878 END IF;
1879 -- Loop through the error_tbl to find the error with the highest severity
1880 -- and return it.
1881 x_return_status := find_highest_exception(px_error_tbl);
1882 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1883 EXCEPTION
1884 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1885 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1886 (
1887 l_api_name,
1888 G_PKG_NAME,
1889 'OKL_API.G_RET_STS_ERROR',
1890 x_msg_count,
1891 x_msg_data,
1892 '_PVT'
1893 );
1894 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1895 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1896 (
1897 l_api_name,
1898 G_PKG_NAME,
1899 'OKL_API.G_RET_STS_UNEXP_ERROR',
1900 x_msg_count,
1901 x_msg_data,
1902 '_PVT'
1903 );
1904 WHEN OTHERS THEN
1905 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1906 (
1907 l_api_name,
1908 G_PKG_NAME,
1909 'OTHERS',
1910 x_msg_count,
1911 x_msg_data,
1912 '_PVT'
1913 );
1914 END delete_row;
1915
1916 -----------------------------------------------------------
1917 -- PL/SQL TBL delete_row for:okl_pmt_channel_methods --
1918 -----------------------------------------------------------
1919 PROCEDURE delete_row(
1920 p_api_version IN NUMBER,
1921 p_init_msg_list IN VARCHAR2,
1922 x_return_status OUT NOCOPY VARCHAR2,
1923 x_msg_count OUT NOCOPY NUMBER,
1924 x_msg_data OUT NOCOPY VARCHAR2,
1925 p_pcm_tbl IN pcm_tbl_type) IS
1926
1927 l_api_version CONSTANT NUMBER := 1;
1928 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
1929 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1930 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1931 BEGIN
1932 OKL_API.init_msg_list(p_init_msg_list);
1933 -- Make sure PL/SQL table has records in it before passing
1934 IF (p_pcm_tbl.COUNT > 0) THEN
1935 delete_row (
1936 p_api_version => p_api_version,
1937 p_init_msg_list => OKL_API.G_FALSE,
1938 x_return_status => x_return_status,
1939 x_msg_count => x_msg_count,
1940 x_msg_data => x_msg_data,
1941 p_pcm_tbl => p_pcm_tbl,
1942 px_error_tbl => l_error_tbl);
1943 END IF;
1944 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1945 EXCEPTION
1946 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1947 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1948 (
1949 l_api_name,
1950 G_PKG_NAME,
1951 'OKL_API.G_RET_STS_ERROR',
1952 x_msg_count,
1953 x_msg_data,
1954 '_PVT'
1955 );
1956 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1957 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1958 (
1959 l_api_name,
1960 G_PKG_NAME,
1961 'OKL_API.G_RET_STS_UNEXP_ERROR',
1962 x_msg_count,
1963 x_msg_data,
1964 '_PVT'
1965 );
1966 WHEN OTHERS THEN
1967 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1968 (
1969 l_api_name,
1970 G_PKG_NAME,
1971 'OTHERS',
1972 x_msg_count,
1973 x_msg_data,
1974 '_PVT'
1975 );
1976 END delete_row;
1977
1978 END OKL_pcm_PVT;