[Home] [Help]
PACKAGE BODY: APPS.OKL_POX_PVT
Source
1 PACKAGE BODY Okl_Pox_Pvt AS
2 /* $Header: OKLSPOXB.pls 120.4 2007/12/07 09:06:17 sosharma 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_trans_num
95 ---------------------------------------------------------------------------
96 FUNCTION get_trans_num RETURN NUMBER IS
97 l_newvalue NUMBER;
98 BEGIN
99 SELECT OKL_POX_SEQ.NEXTVAL INTO l_newvalue FROM dual;
100 RETURN(l_newvalue);
101 END get_trans_num;
102
103 ---------------------------------------------------------------------------
104 -- FUNCTION get_rec for: OKL_POOL_TRANSACTIONS_V
105 ---------------------------------------------------------------------------
106 FUNCTION get_rec (
107 p_poxv_rec IN poxv_rec_type,
108 x_no_data_found OUT NOCOPY BOOLEAN
109 ) RETURN poxv_rec_type IS
110 CURSOR okl_poxv_pk_csr (p_id IN NUMBER) IS
111 SELECT
112 ID,
113 OBJECT_VERSION_NUMBER,
114 POL_ID,
115 TRANSACTION_NUMBER,
116 TRANSACTION_DATE,
117 TRANSACTION_TYPE,
118 TRANSACTION_SUB_TYPE,
119 DATE_EFFECTIVE,
120 CURRENCY_CODE,
121 CURRENCY_CONVERSION_TYPE,
122 CURRENCY_CONVERSION_DATE,
123 CURRENCY_CONVERSION_RATE,
124 TRANSACTION_REASON,
125 ATTRIBUTE_CATEGORY,
126 ATTRIBUTE1,
127 ATTRIBUTE2,
128 ATTRIBUTE3,
129 ATTRIBUTE4,
130 ATTRIBUTE5,
131 ATTRIBUTE6,
132 ATTRIBUTE7,
133 ATTRIBUTE8,
134 ATTRIBUTE9,
135 ATTRIBUTE10,
136 ATTRIBUTE11,
137 ATTRIBUTE12,
138 ATTRIBUTE13,
139 ATTRIBUTE14,
140 ATTRIBUTE15,
141 REQUEST_ID,
142 PROGRAM_APPLICATION_ID,
143 PROGRAM_ID,
144 PROGRAM_UPDATE_DATE,
145 CREATED_BY,
146 CREATION_DATE,
147 LAST_UPDATED_BY,
148 LAST_UPDATE_DATE,
149 LAST_UPDATE_LOGIN,
150 LEGAL_ENTITY_ID,
151 TRANSACTION_STATUS
152 FROM OKL_POOL_TRANSACTIONS_V
153 WHERE OKL_POOL_TRANSACTIONS_V.id = p_id;
154 l_okl_poxv_pk okl_poxv_pk_csr%ROWTYPE;
155 l_poxv_rec poxv_rec_type;
156 BEGIN
157 x_no_data_found := TRUE;
158 -- Get current database values
159 OPEN okl_poxv_pk_csr (p_poxv_rec.id);
160 FETCH okl_poxv_pk_csr INTO
161 l_poxv_rec.id,
162 l_poxv_rec.object_version_number,
163 l_poxv_rec.pol_id,
164 l_poxv_rec.transaction_number,
165 l_poxv_rec.transaction_date,
166 l_poxv_rec.transaction_type,
167 l_poxv_rec.transaction_sub_type,
168 l_poxv_rec.date_effective,
169 l_poxv_rec.currency_code,
170 l_poxv_rec.currency_conversion_type,
171 l_poxv_rec.currency_conversion_date,
172 l_poxv_rec.currency_conversion_rate,
173 l_poxv_rec.transaction_reason,
174 l_poxv_rec.attribute_category,
175 l_poxv_rec.attribute1,
176 l_poxv_rec.attribute2,
177 l_poxv_rec.attribute3,
178 l_poxv_rec.attribute4,
179 l_poxv_rec.attribute5,
180 l_poxv_rec.attribute6,
181 l_poxv_rec.attribute7,
182 l_poxv_rec.attribute8,
183 l_poxv_rec.attribute9,
184 l_poxv_rec.attribute10,
185 l_poxv_rec.attribute11,
186 l_poxv_rec.attribute12,
187 l_poxv_rec.attribute13,
188 l_poxv_rec.attribute14,
189 l_poxv_rec.attribute15,
190 l_poxv_rec.request_id,
191 l_poxv_rec.program_application_id,
192 l_poxv_rec.program_id,
193 l_poxv_rec.program_update_date,
194 l_poxv_rec.created_by,
195 l_poxv_rec.creation_date,
196 l_poxv_rec.last_updated_by,
197 l_poxv_rec.last_update_date,
198 l_poxv_rec.last_update_login,
199 l_poxv_rec.legal_entity_id,
200 l_poxv_rec.transaction_status;
201 x_no_data_found := okl_poxv_pk_csr%NOTFOUND;
202 CLOSE okl_poxv_pk_csr;
203 RETURN(l_poxv_rec);
204 END get_rec;
205
206 ------------------------------------------------------------------
207 -- This version of get_rec sets error messages if no data found --
208 ------------------------------------------------------------------
209 FUNCTION get_rec (
210 p_poxv_rec IN poxv_rec_type,
211 x_return_status OUT NOCOPY VARCHAR2
212 ) RETURN poxv_rec_type IS
213 l_poxv_rec poxv_rec_type;
214 l_row_notfound BOOLEAN := TRUE;
215 BEGIN
216 x_return_status := OKL_API.G_RET_STS_SUCCESS;
217 l_poxv_rec := get_rec(p_poxv_rec, l_row_notfound);
218 IF (l_row_notfound) THEN
219 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
220 x_return_status := OKL_API.G_RET_STS_ERROR;
221 END IF;
222 RETURN(l_poxv_rec);
223 END get_rec;
224 -----------------------------------------------------------
225 -- So we don't have to pass an "l_row_notfound" variable --
226 -----------------------------------------------------------
227 FUNCTION get_rec (
228 p_poxv_rec IN poxv_rec_type
229 ) RETURN poxv_rec_type IS
230 l_row_not_found BOOLEAN := TRUE;
231 BEGIN
232 RETURN(get_rec(p_poxv_rec, l_row_not_found));
233 END get_rec;
234 ---------------------------------------------------------------------------
235 -- FUNCTION get_rec for: okl_pool_transactions
236 ---------------------------------------------------------------------------
237 FUNCTION get_rec (
238 p_pox_rec IN pox_rec_type,
239 x_no_data_found OUT NOCOPY BOOLEAN
240 ) RETURN pox_rec_type IS
241 CURSOR okl_pool_transactions_pk_csr (p_id IN NUMBER) IS
242 SELECT
243 ID,
244 OBJECT_VERSION_NUMBER,
245 POL_ID,
246 TRANSACTION_NUMBER,
247 TRANSACTION_DATE,
248 TRANSACTION_TYPE,
249 TRANSACTION_SUB_TYPE,
250 DATE_EFFECTIVE,
251 CURRENCY_CODE,
252 CURRENCY_CONVERSION_TYPE,
253 CURRENCY_CONVERSION_DATE,
254 CURRENCY_CONVERSION_RATE,
255 TRANSACTION_REASON,
256 ATTRIBUTE_CATEGORY,
257 ATTRIBUTE1,
258 ATTRIBUTE2,
259 ATTRIBUTE3,
260 ATTRIBUTE4,
261 ATTRIBUTE5,
262 ATTRIBUTE6,
263 ATTRIBUTE7,
264 ATTRIBUTE8,
265 ATTRIBUTE9,
266 ATTRIBUTE10,
267 ATTRIBUTE11,
268 ATTRIBUTE12,
269 ATTRIBUTE13,
270 ATTRIBUTE14,
271 ATTRIBUTE15,
272 REQUEST_ID,
273 PROGRAM_APPLICATION_ID,
274 PROGRAM_ID,
275 PROGRAM_UPDATE_DATE,
276 CREATED_BY,
277 CREATION_DATE,
278 LAST_UPDATED_BY,
279 LAST_UPDATE_DATE,
280 LAST_UPDATE_LOGIN,
281 LEGAL_ENTITY_ID,
282 TRANSACTION_STATUS
283 FROM okl_pool_transactions
284 WHERE okl_pool_transactions.id = p_id;
285 l_okl_pool_transactions_pk okl_pool_transactions_pk_csr%ROWTYPE;
286 l_pox_rec pox_rec_type;
287 BEGIN
288 x_no_data_found := TRUE;
289 -- Get current database values
290 OPEN okl_pool_transactions_pk_csr (p_pox_rec.id);
291 FETCH okl_pool_transactions_pk_csr INTO
292 l_pox_rec.id,
293 l_pox_rec.object_version_number,
294 l_pox_rec.pol_id,
295 l_pox_rec.transaction_number,
296 l_pox_rec.transaction_date,
297 l_pox_rec.transaction_type,
298 l_pox_rec.transaction_sub_type,
299 l_pox_rec.date_effective,
300 l_pox_rec.currency_code,
301 l_pox_rec.currency_conversion_type,
302 l_pox_rec.currency_conversion_date,
303 l_pox_rec.currency_conversion_rate,
304 l_pox_rec.transaction_reason,
305 l_pox_rec.attribute_category,
306 l_pox_rec.attribute1,
307 l_pox_rec.attribute2,
308 l_pox_rec.attribute3,
309 l_pox_rec.attribute4,
310 l_pox_rec.attribute5,
311 l_pox_rec.attribute6,
312 l_pox_rec.attribute7,
313 l_pox_rec.attribute8,
314 l_pox_rec.attribute9,
315 l_pox_rec.attribute10,
316 l_pox_rec.attribute11,
317 l_pox_rec.attribute12,
318 l_pox_rec.attribute13,
319 l_pox_rec.attribute14,
320 l_pox_rec.attribute15,
321 l_pox_rec.request_id,
322 l_pox_rec.program_application_id,
323 l_pox_rec.program_id,
324 l_pox_rec.program_update_date,
325 l_pox_rec.created_by,
326 l_pox_rec.creation_date,
327 l_pox_rec.last_updated_by,
328 l_pox_rec.last_update_date,
329 l_pox_rec.last_update_login,
330 l_pox_rec.legal_entity_id,
331 l_pox_rec.transaction_status;
332 x_no_data_found := okl_pool_transactions_pk_csr%NOTFOUND;
333 CLOSE okl_pool_transactions_pk_csr;
334 RETURN(l_pox_rec);
335 END get_rec;
336
337 ------------------------------------------------------------------
338 -- This version of get_rec sets error messages if no data found --
339 ------------------------------------------------------------------
340 FUNCTION get_rec (
341 p_pox_rec IN pox_rec_type,
342 x_return_status OUT NOCOPY VARCHAR2
343 ) RETURN pox_rec_type IS
344 l_pox_rec pox_rec_type;
345 l_row_notfound BOOLEAN := TRUE;
346 BEGIN
347 x_return_status := OKL_API.G_RET_STS_SUCCESS;
348 l_pox_rec := get_rec(p_pox_rec, l_row_notfound);
349 IF (l_row_notfound) THEN
350 OKL_API.set_message(G_APP_NAME,G_INVALID_VALUE,G_COL_NAME_TOKEN,'ID');
351 x_return_status := OKL_API.G_RET_STS_ERROR;
352 END IF;
353 RETURN(l_pox_rec);
354 END get_rec;
355 -----------------------------------------------------------
356 -- So we don't have to pass an "l_row_notfound" variable --
357 -----------------------------------------------------------
358 FUNCTION get_rec (
359 p_pox_rec IN pox_rec_type
360 ) RETURN pox_rec_type IS
361 l_row_not_found BOOLEAN := TRUE;
362 BEGIN
363 RETURN(get_rec(p_pox_rec, l_row_not_found));
364 END get_rec;
365
366 ---------------------------------------------------------------------------
367 -- FUNCTION null_out_defaults for: OKL_POOL_TRANSACTIONS_V
368 -- mvasudev, hold this off for now
369 ---------------------------------------------------------------------------
370 FUNCTION null_out_defaults (
371 p_poxv_rec IN poxv_rec_type
372 ) RETURN poxv_rec_type IS
373 l_poxv_rec poxv_rec_type := p_poxv_rec;
374 BEGIN
375 IF (l_poxv_rec.id = OKL_API.G_MISS_NUM ) THEN
376 l_poxv_rec.id := NULL;
377 END IF;
378 IF (l_poxv_rec.object_version_number = OKL_API.G_MISS_NUM ) THEN
379 l_poxv_rec.object_version_number := NULL;
380 END IF;
381 IF (l_poxv_rec.pol_id = OKL_API.G_MISS_NUM ) THEN
382 l_poxv_rec.pol_id := NULL;
383 END IF;
384 IF (l_poxv_rec.transaction_number = OKL_API.G_MISS_NUM ) THEN
385 l_poxv_rec.transaction_number := NULL;
386 END IF;
387 IF (l_poxv_rec.transaction_date = OKL_API.G_MISS_DATE ) THEN
388 l_poxv_rec.transaction_date := NULL;
389 END IF;
390 IF (l_poxv_rec.transaction_type = OKL_API.G_MISS_CHAR ) THEN
391 l_poxv_rec.transaction_type := NULL;
392 END IF;
393 IF (l_poxv_rec.transaction_sub_type = OKL_API.G_MISS_CHAR ) THEN
394 l_poxv_rec.transaction_sub_type := NULL;
395 END IF;
396 IF (l_poxv_rec.date_effective = OKL_API.G_MISS_DATE ) THEN
397 l_poxv_rec.date_effective := NULL;
398 END IF;
399 IF (l_poxv_rec.currency_code = OKL_API.G_MISS_CHAR ) THEN
400 l_poxv_rec.currency_code := NULL;
401 END IF;
402 IF (l_poxv_rec.currency_conversion_type = OKL_API.G_MISS_CHAR ) THEN
403 l_poxv_rec.currency_conversion_type := NULL;
404 END IF;
405 IF (l_poxv_rec.currency_conversion_date = OKL_API.G_MISS_DATE ) THEN
406 l_poxv_rec.currency_conversion_date := NULL;
407 END IF;
408 IF (l_poxv_rec.currency_conversion_rate = OKL_API.G_MISS_NUM ) THEN
409 l_poxv_rec.currency_conversion_rate := NULL;
410 END IF;
411 IF (l_poxv_rec.transaction_reason = OKL_API.G_MISS_CHAR ) THEN
412 l_poxv_rec.transaction_reason := NULL;
413 END IF;
414 IF (l_poxv_rec.attribute_category = OKL_API.G_MISS_CHAR ) THEN
415 l_poxv_rec.attribute_category := NULL;
416 END IF;
417 IF (l_poxv_rec.attribute1 = OKL_API.G_MISS_CHAR ) THEN
418 l_poxv_rec.attribute1 := NULL;
419 END IF;
420 IF (l_poxv_rec.attribute2 = OKL_API.G_MISS_CHAR ) THEN
421 l_poxv_rec.attribute2 := NULL;
422 END IF;
423 IF (l_poxv_rec.attribute3 = OKL_API.G_MISS_CHAR ) THEN
424 l_poxv_rec.attribute3 := NULL;
425 END IF;
426 IF (l_poxv_rec.attribute4 = OKL_API.G_MISS_CHAR ) THEN
427 l_poxv_rec.attribute4 := NULL;
428 END IF;
429 IF (l_poxv_rec.attribute5 = OKL_API.G_MISS_CHAR ) THEN
430 l_poxv_rec.attribute5 := NULL;
431 END IF;
432 IF (l_poxv_rec.attribute6 = OKL_API.G_MISS_CHAR ) THEN
433 l_poxv_rec.attribute6 := NULL;
434 END IF;
435 IF (l_poxv_rec.attribute7 = OKL_API.G_MISS_CHAR ) THEN
436 l_poxv_rec.attribute7 := NULL;
437 END IF;
438 IF (l_poxv_rec.attribute8 = OKL_API.G_MISS_CHAR ) THEN
439 l_poxv_rec.attribute8 := NULL;
440 END IF;
441 IF (l_poxv_rec.attribute9 = OKL_API.G_MISS_CHAR ) THEN
442 l_poxv_rec.attribute9 := NULL;
443 END IF;
444 IF (l_poxv_rec.attribute10 = OKL_API.G_MISS_CHAR ) THEN
445 l_poxv_rec.attribute10 := NULL;
446 END IF;
447 IF (l_poxv_rec.attribute11 = OKL_API.G_MISS_CHAR ) THEN
448 l_poxv_rec.attribute11 := NULL;
449 END IF;
450 IF (l_poxv_rec.attribute12 = OKL_API.G_MISS_CHAR ) THEN
451 l_poxv_rec.attribute12 := NULL;
452 END IF;
453 IF (l_poxv_rec.attribute13 = OKL_API.G_MISS_CHAR ) THEN
454 l_poxv_rec.attribute13 := NULL;
455 END IF;
456 IF (l_poxv_rec.attribute14 = OKL_API.G_MISS_CHAR ) THEN
457 l_poxv_rec.attribute14 := NULL;
458 END IF;
459 IF (l_poxv_rec.attribute15 = OKL_API.G_MISS_CHAR ) THEN
460 l_poxv_rec.attribute15 := NULL;
461 END IF;
462 IF (l_poxv_rec.attribute1 = OKL_API.G_MISS_CHAR ) THEN
463 l_poxv_rec.attribute1 := NULL;
464 END IF;
465 IF (l_poxv_rec.request_id = OKL_API.G_MISS_NUM ) THEN
466 l_poxv_rec.request_id := NULL;
467 END IF;
468 IF (l_poxv_rec.program_application_id = OKL_API.G_MISS_NUM ) THEN
469 l_poxv_rec.program_application_id := NULL;
470 END IF;
471 IF (l_poxv_rec.program_id = OKL_API.G_MISS_NUM ) THEN
472 l_poxv_rec.program_id := NULL;
473 END IF;
474 IF (l_poxv_rec.program_update_date = OKL_API.G_MISS_DATE ) THEN
475 l_poxv_rec.program_update_date := NULL;
476 END IF;
477 IF (l_poxv_rec.created_by = OKL_API.G_MISS_NUM ) THEN
478 l_poxv_rec.created_by := NULL;
479 END IF;
480 IF (l_poxv_rec.creation_date = OKL_API.G_MISS_DATE ) THEN
481 l_poxv_rec.creation_date := NULL;
482 END IF;
483 IF (l_poxv_rec.last_updated_by = OKL_API.G_MISS_NUM ) THEN
484 l_poxv_rec.last_updated_by := NULL;
485 END IF;
486 IF (l_poxv_rec.last_update_date = OKL_API.G_MISS_DATE ) THEN
487 l_poxv_rec.last_update_date := NULL;
488 END IF;
489 IF (l_poxv_rec.last_update_login = OKL_API.G_MISS_NUM ) THEN
490 l_poxv_rec.last_update_login := NULL;
491 END IF;
492 IF (l_poxv_rec.legal_entity_id = OKL_API.G_MISS_NUM ) THEN
493 l_poxv_rec.legal_entity_id := NULL;
494 END IF;
495 IF (l_poxv_rec.transaction_status = OKL_API.G_MISS_CHAR ) THEN
496 l_poxv_rec.transaction_status := NULL;
497 END IF;
498
499 RETURN(l_poxv_rec);
500 END null_out_defaults;
501
502 ---------------------------------
503 -- Validate_Attributes for: ID --
504 ---------------------------------
505 PROCEDURE validate_id(
506 x_return_status OUT NOCOPY VARCHAR2,
507 p_id IN NUMBER) IS
508 BEGIN
509 x_return_status := OKL_API.G_RET_STS_SUCCESS;
510 IF (p_id = OKL_API.G_MISS_NUM OR
511 p_id IS NULL)
512 THEN
513 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');
514 x_return_status := OKL_API.G_RET_STS_ERROR;
515 RAISE G_EXCEPTION_HALT_VALIDATION;
516 END IF;
517 EXCEPTION
518 WHEN G_EXCEPTION_HALT_VALIDATION THEN
519 NULL;
520 WHEN OTHERS THEN
521 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
522 ,p_msg_name => G_UNEXPECTED_ERROR
523 ,p_token1 => G_SQLCODE_TOKEN
524 ,p_token1_value => SQLCODE
525 ,p_token2 => G_SQLERRM_TOKEN
526 ,p_token2_value => SQLERRM);
527 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
528 END validate_id;
529
530 ----------------------------------------------------
531 -- Validate_Attributes for: OBJECT_VERSION_NUMBER --
532 ----------------------------------------------------
533 PROCEDURE validate_object_version_number(
534 x_return_status OUT NOCOPY VARCHAR2,
535 p_object_version_number IN NUMBER) IS
536 BEGIN
537 x_return_status := OKL_API.G_RET_STS_SUCCESS;
538 IF (p_object_version_number = OKL_API.G_MISS_NUM OR
539 p_object_version_number IS NULL)
540 THEN
541 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'object_version_number');
542 x_return_status := OKL_API.G_RET_STS_ERROR;
543 RAISE G_EXCEPTION_HALT_VALIDATION;
544 END IF;
545 EXCEPTION
546 WHEN G_EXCEPTION_HALT_VALIDATION THEN
547 NULL;
548 WHEN OTHERS THEN
549 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
550 ,p_msg_name => G_UNEXPECTED_ERROR
551 ,p_token1 => G_SQLCODE_TOKEN
552 ,p_token1_value => SQLCODE
553 ,p_token2 => G_SQLERRM_TOKEN
554 ,p_token2_value => SQLERRM);
555 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
556 END validate_object_version_number;
557
558 -------------------------------------
559 -- Validate_Attributes for: pol_id --
560 -------------------------------------
561 PROCEDURE validate_pol_id(
562 x_return_status OUT NOCOPY VARCHAR2,
563 p_pol_id IN NUMBER) IS
564
565 l_dummy VARCHAR2(1) := '?';
566 l_row_not_found BOOLEAN := FALSE;
567
568 -- Cursor For OKL_POL_POX_FK;
569 CURSOR okl_polv_pk_csr (p_id IN OKL_POOL_TRANSACTIONS_V.pol_id%TYPE) IS
570 SELECT '1'
571 FROM OKL_POOLS
572 WHERE OKL_POOLS.id = p_id;
573
574 BEGIN
575 x_return_status := OKL_API.G_RET_STS_SUCCESS;
576 IF (p_pol_id = OKL_API.G_MISS_NUM OR
577 p_pol_id IS NULL)
578 THEN
579 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'pol_id');
580 x_return_status := OKL_API.G_RET_STS_ERROR;
581 RAISE G_EXCEPTION_HALT_VALIDATION;
582 END IF;
583
584 OPEN okl_polv_pk_csr(p_pol_id);
585 FETCH okl_polv_pk_csr INTO l_dummy;
586 l_row_not_found := okl_polv_pk_csr%NOTFOUND;
587 CLOSE okl_polv_pk_csr;
588
589 IF l_row_not_found THEN
590 OKL_API.set_message(G_OKC_APP,G_INVALID_VALUE,G_COL_NAME_TOKEN,'pol_id');
591 x_return_status := OKL_API.G_RET_STS_ERROR;
592 END IF;
593
594 EXCEPTION
595 WHEN G_EXCEPTION_HALT_VALIDATION THEN
596 NULL;
597 WHEN OTHERS THEN
598 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
599 ,p_msg_name => G_UNEXPECTED_ERROR
600 ,p_token1 => G_SQLCODE_TOKEN
601 ,p_token1_value => SQLCODE
602 ,p_token2 => G_SQLERRM_TOKEN
603 ,p_token2_value => SQLERRM);
604 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
605 IF okl_polv_pk_csr%ISOPEN THEN
606 CLOSE okl_polv_pk_csr;
607 END IF;
608 END validate_pol_id;
609
610 -------------------------------------
611 -- Validate_Attributes for: transaction_number --
612 -------------------------------------
613 PROCEDURE validate_transaction_number(
614 x_return_status OUT NOCOPY VARCHAR2,
615 p_transaction_number IN NUMBER) IS
616
617 BEGIN
618 x_return_status := OKL_API.G_RET_STS_SUCCESS;
619
620 IF (p_transaction_number = OKL_API.G_MISS_NUM OR
621 p_transaction_number IS NULL)
622 THEN
623 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'transaction_number');
624 x_return_status := OKL_API.G_RET_STS_ERROR;
625 RAISE G_EXCEPTION_HALT_VALIDATION;
626 END IF;
627
628 EXCEPTION
629 WHEN G_EXCEPTION_HALT_VALIDATION THEN
630 NULL;
631 WHEN OTHERS THEN
632 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
633 ,p_msg_name => G_UNEXPECTED_ERROR
634 ,p_token1 => G_SQLCODE_TOKEN
635 ,p_token1_value => SQLCODE
636 ,p_token2 => G_SQLERRM_TOKEN
637 ,p_token2_value => SQLERRM);
638 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
639 END validate_transaction_number;
640
641 -------------------------------------
642 -- Validate_Attributes for: Legal Entity ID --
643 -------------------------------------
644 PROCEDURE validate_legal_entity_id(
645 x_return_status OUT NOCOPY VARCHAR2,
646 p_legal_entity_id IN NUMBER) IS
647 l_dummy VARCHAR2(1);
648 BEGIN
649 x_return_status := OKL_API.G_RET_STS_SUCCESS;
650 IF (p_legal_entity_id = OKL_API.G_MISS_NUM OR
651 p_legal_entity_id IS NULL)
652 THEN
653 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'legal_entity_id');
654 x_return_status := OKL_API.G_RET_STS_ERROR;
655 RAISE G_EXCEPTION_HALT_VALIDATION;
656 ELSE
657 l_dummy := OKL_LEGAL_ENTITY_UTIL.check_le_id_exists(p_legal_entity_id);
658 IF l_dummy <>1 THEN
659 Okl_Api.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'legal_entity_id');
660 x_return_status := OKL_API.G_RET_STS_ERROR;
661 RAISE G_EXCEPTION_HALT_VALIDATION;
662 END IF;
663 END IF;
664 EXCEPTION
665 WHEN G_EXCEPTION_HALT_VALIDATION THEN
666 NULL;
667 WHEN OTHERS THEN
668 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
669 ,p_msg_name => G_UNEXPECTED_ERROR
670 ,p_token1 => G_SQLCODE_TOKEN
671 ,p_token1_value => SQLCODE
672 ,p_token2 => G_SQLERRM_TOKEN
673 ,p_token2_value => SQLERRM);
674 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
675 END validate_legal_entity_id;
676
677
678 -------------------------------------
679 -- Validate_Attributes for: transaction_date --
680 -------------------------------------
681 PROCEDURE validate_transaction_date(
682 x_return_status OUT NOCOPY VARCHAR2,
683 p_transaction_date IN DATE) IS
684
685 l_dummy VARCHAR2(1) := '?';
686 l_row_not_found BOOLEAN := FALSE;
687
688 BEGIN
689 x_return_status := OKL_API.G_RET_STS_SUCCESS;
690
691 IF (p_transaction_date = OKL_API.G_MISS_DATE OR
692 p_transaction_date IS NULL)
693 THEN
694 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'transaction_date');
695 x_return_status := OKL_API.G_RET_STS_ERROR;
696 RAISE G_EXCEPTION_HALT_VALIDATION;
697 END IF;
698 EXCEPTION
699 WHEN G_EXCEPTION_HALT_VALIDATION THEN
700 NULL;
701 WHEN OTHERS THEN
702 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
703 ,p_msg_name => G_UNEXPECTED_ERROR
704 ,p_token1 => G_SQLCODE_TOKEN
705 ,p_token1_value => SQLCODE
706 ,p_token2 => G_SQLERRM_TOKEN
707 ,p_token2_value => SQLERRM);
708 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
709 END validate_transaction_date;
710
711 -------------------------------------
712 -- Validate_Attributes for: transaction_type --
713 -------------------------------------
714 PROCEDURE validate_transaction_type(
715 x_return_status OUT NOCOPY VARCHAR2,
716 p_transaction_type IN VARCHAR2) IS
717
718 l_found VARCHAR2(1);
719
720 BEGIN
721 x_return_status := OKL_API.G_RET_STS_SUCCESS;
722
723 IF (p_transaction_type = OKL_API.G_MISS_CHAR OR
724 p_transaction_type IS NULL)
725 THEN
726 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'transaction_type');
727 x_return_status := OKL_API.G_RET_STS_ERROR;
728 RAISE G_EXCEPTION_HALT_VALIDATION;
729 END IF;
730
731 --Check if transaction_type exists in the fnd_common_lookups or not
732 l_found := okl_accounting_util.validate_lookup_code(p_lookup_type => 'OKL_POOL_TRANSACTION_TYPE',
733 p_lookup_code => p_transaction_type);
734
735
736 IF (l_found <> OKL_API.G_TRUE ) THEN
737 OKL_API.set_message(G_OKC_APP, G_INVALID_VALUE,G_COL_NAME_TOKEN,'transaction_type');
738 x_return_status := OKL_API.G_RET_STS_ERROR;
739 -- raise the exception as there's no matching foreign key value
740 RAISE G_EXCEPTION_HALT_VALIDATION;
741 END IF;
742
743 EXCEPTION
744 WHEN G_EXCEPTION_HALT_VALIDATION THEN
745 NULL;
746 WHEN OTHERS THEN
747 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
748 ,p_msg_name => G_UNEXPECTED_ERROR
749 ,p_token1 => G_SQLCODE_TOKEN
750 ,p_token1_value => SQLCODE
751 ,p_token2 => G_SQLERRM_TOKEN
752 ,p_token2_value => SQLERRM);
753 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
754 END validate_transaction_type;
755
756 -------------------------------------
757 -- Validate_Attributes for: transaction_reason --
758 -------------------------------------
759 PROCEDURE validate_transaction_reason(
760 x_return_status OUT NOCOPY VARCHAR2,
761 p_transaction_reason IN VARCHAR2) IS
762
763 l_found VARCHAR2(1);
764
765 BEGIN
766 x_return_status := OKL_API.G_RET_STS_SUCCESS;
767
768 IF (p_transaction_reason = OKL_API.G_MISS_CHAR OR
769 p_transaction_reason IS NULL)
770 THEN
771 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'transaction_reason');
772 x_return_status := OKL_API.G_RET_STS_ERROR;
773 RAISE G_EXCEPTION_HALT_VALIDATION;
774 END IF;
775
776 --Check if transaction_reason exists in the fnd_common_lookups or not
777 l_found := okl_accounting_util.validate_lookup_code(p_lookup_type => 'OKL_POOL_TRANSACTION_REASON',
778 p_lookup_code => p_transaction_reason);
779
780
781 IF (l_found <> OKL_API.G_TRUE ) THEN
782 OKL_API.set_message(G_OKC_APP, G_INVALID_VALUE,G_COL_NAME_TOKEN,'transaction_reason');
783 x_return_status := OKL_API.G_RET_STS_ERROR;
784 -- raise the exception as there's no matching foreign key value
785 RAISE G_EXCEPTION_HALT_VALIDATION;
786 END IF;
787
788 EXCEPTION
789 WHEN G_EXCEPTION_HALT_VALIDATION THEN
790 NULL;
791 WHEN OTHERS THEN
792 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
793 ,p_msg_name => G_UNEXPECTED_ERROR
794 ,p_token1 => G_SQLCODE_TOKEN
795 ,p_token1_value => SQLCODE
796 ,p_token2 => G_SQLERRM_TOKEN
797 ,p_token2_value => SQLERRM);
798 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
799 END validate_transaction_reason;
800 ---------------------------------------------------------------------------
801 -- PROCEDURE Validate_transaction_Status_Record
802 ---------------------------------------------------------------------------
803 /*
804 PROCEDURE validate_transaction_status(
805 x_return_status OUT NOCOPY VARCHAR2,
806 p_transaction_status IN VARCHAR2) IS
807
808 l_found VARCHAR2(1);
809
810 BEGIN
811 x_return_status := OKL_API.G_RET_STS_SUCCESS;
812
813 IF (p_transaction_status = OKL_API.G_MISS_CHAR OR
814 p_transaction_status IS NULL)
815 THEN
816 OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'p_transaction_status');
817 x_return_status := OKL_API.G_RET_STS_ERROR;
818 RAISE G_EXCEPTION_HALT_VALIDATION;
819 END IF;
820
821 --Check if transaction_status exists in the fnd_common_lookups or not
822 l_found := okl_accounting_util.validate_lookup_code(p_lookup_type => 'OKL_POOL_TRANSACTION_STATUS',
823 p_lookup_code => p_transaction_status);
824
825
826 IF (l_found <> OKL_API.G_TRUE ) THEN
827 OKL_API.set_message(G_OKC_APP, G_INVALID_VALUE,G_COL_NAME_TOKEN,'transaction_status');
828 x_return_status := OKL_API.G_RET_STS_ERROR;
829 -- raise the exception as there's no matching foreign key value
830 RAISE G_EXCEPTION_HALT_VALIDATION;
831 END IF;
832
833 EXCEPTION
834 WHEN G_EXCEPTION_HALT_VALIDATION THEN
835 NULL;
836 WHEN OTHERS THEN
837 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
838 ,p_msg_name => G_UNEXPECTED_ERROR
839 ,p_token1 => G_SQLCODE_TOKEN
840 ,p_token1_value => SQLCODE
841 ,p_token2 => G_SQLERRM_TOKEN
842 ,p_token2_value => SQLERRM);
843 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
844 END validate_transaction_status;
845 */
846 ---------------------------------------------------------------------------
847 -- PROCEDURE Validate_Unique_Pox_Record
848 ---------------------------------------------------------------------------
849 -- Start of comments
850 --
851 -- Procedure Name : Validate_Unique_Pox_Record
852 -- Description :
853 -- Business Rules :
854 -- Parameters :
855 -- Version : 1.0
856 -- End of comments
857 ---------------------------------------------------------------------------
858 PROCEDURE Validate_Unique_Pox_Record(p_poxv_rec IN poxv_rec_type
859 ,x_return_status OUT NOCOPY VARCHAR2)
860 IS
861
862 l_dummy VARCHAR2(1) := '?';
863 l_row_found BOOLEAN := FALSE;
864
865 -- Cursor for Pox Unique Key
866 CURSOR okl_pox_uk_csr(p_poxv_rec IN poxv_rec_type) IS
867 SELECT '1'
868 FROM OKL_POOL_TRANSACTIONS_V
869 WHERE transaction_number = p_poxv_rec.transaction_number
870 AND id <> NVL(p_poxv_rec.id,-9999);
871
872 BEGIN
873 -- initialize return status
874 x_return_status := OKL_API.G_RET_STS_SUCCESS;
875 OPEN okl_pox_uk_csr(p_poxv_rec);
876 FETCH okl_pox_uk_csr INTO l_dummy;
877 l_row_found := okl_pox_uk_csr%FOUND;
878 CLOSE okl_pox_uk_csr;
879 IF l_row_found THEN
880 OKL_API.set_message(G_APP_NAME,G_OKL_UNQS);
881 x_return_status := OKL_API.G_RET_STS_ERROR;
882 END IF;
883
884 EXCEPTION
885 WHEN G_EXCEPTION_HALT_VALIDATION THEN
886 -- no processing necessary; validation can continue
887 -- with the next column
888 NULL;
889
890 WHEN OTHERS THEN
891 -- store SQL error message on message stack for caller
892 OKL_API.SET_MESSAGE(p_app_name => G_APP_NAME,
893 p_msg_name => G_UNEXPECTED_ERROR,
894 p_token1 => G_SQLCODE_TOKEN,
895 p_token1_value => SQLCODE,
896 p_token2 => G_SQLERRM_TOKEN,
897 p_token2_value => SQLERRM);
898 -- notify caller of an UNEXPECTED error
899 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
900
901 IF okl_pox_uk_csr%isopen THEN
902 CLOSE okl_pox_uk_csr;
903 END IF;
904
905 END Validate_Unique_Pox_Record;
906
907
908 ---------------------------------------------------------------------------
909 -- FUNCTION Validate_Attributes
910 ---------------------------------------------------------------------------
911 ---------------------------------------------------
912 -- Validate_Attributes for:OKL_POOL_TRANSACTIONS_V --
913 ---------------------------------------------------
914 FUNCTION Validate_Attributes (
915 p_poxv_rec IN poxv_rec_type
916 ) RETURN VARCHAR2 IS
917 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
918 x_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
919 BEGIN
920 -----------------------------
921 -- Column Level Validation --
922 -----------------------------
923 -- ***
924 -- id
925 -- ***
926 validate_id(x_return_status, p_poxv_rec.id);
927 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
928 l_return_status := x_return_status;
929 RAISE G_EXCEPTION_HALT_VALIDATION;
930 END IF;
931
932 -- ***
933 -- object_version_number
934 -- ***
935 validate_object_version_number(x_return_status, p_poxv_rec.object_version_number);
936 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
937 l_return_status := x_return_status;
938 RAISE G_EXCEPTION_HALT_VALIDATION;
939 END IF;
940
941 -- ***
942 -- pol_id
943 -- ***
944 validate_pol_id(x_return_status, p_poxv_rec.pol_id);
945 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
946 l_return_status := x_return_status;
947 RAISE G_EXCEPTION_HALT_VALIDATION;
948 END IF;
949
950 -- ***
951 -- transaction_number
952 -- ***
953 validate_transaction_number(x_return_status, p_poxv_rec.transaction_number);
954 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
955 l_return_status := x_return_status;
956 RAISE G_EXCEPTION_HALT_VALIDATION;
957 END IF;
958
959 -- ***
960 -- transaction_date
961 -- ***
962 validate_transaction_date(x_return_status, p_poxv_rec.transaction_date);
963 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
964 l_return_status := x_return_status;
965 RAISE G_EXCEPTION_HALT_VALIDATION;
966 END IF;
967
968 -- ***
969 -- transaction_type
970 -- ***
971 validate_transaction_type(x_return_status, p_poxv_rec.transaction_type);
972 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
973 l_return_status := x_return_status;
974 RAISE G_EXCEPTION_HALT_VALIDATION;
975 END IF;
976
977 -- ***
978 -- transaction_reason
979 -- ***
980 validate_transaction_reason(x_return_status, p_poxv_rec.transaction_reason);
981 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
982 l_return_status := x_return_status;
983 RAISE G_EXCEPTION_HALT_VALIDATION;
984 END IF;
985
986 -- ***
987 -- legal_entity_id
988 -- ***
989 validate_legal_entity_id(x_return_status, p_poxv_rec.legal_entity_id);
990 IF (x_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
991 l_return_status := x_return_status;
992 RAISE G_EXCEPTION_HALT_VALIDATION;
993 END IF;
994
995 RETURN(l_return_status);
996 EXCEPTION
997 WHEN G_EXCEPTION_HALT_VALIDATION THEN
998 RETURN(l_return_status);
999 WHEN OTHERS THEN
1000 OKL_API.SET_MESSAGE( p_app_name => G_APP_NAME
1001 ,p_msg_name => G_UNEXPECTED_ERROR
1002 ,p_token1 => G_SQLCODE_TOKEN
1003 ,p_token1_value => SQLCODE
1004 ,p_token2 => G_SQLERRM_TOKEN
1005 ,p_token2_value => SQLERRM);
1006 l_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1007 RETURN(l_return_status);
1008 END Validate_Attributes;
1009 ---------------------------------------------------------------------------
1010 -- PROCEDURE Validate_Record
1011 ---------------------------------------------------------------------------
1012 -----------------------------------------
1013 -- Validate Record for:OKL_POOL_TRANSACTIONS_V --
1014 -----------------------------------------
1015 FUNCTION Validate_Record (
1016 p_poxv_rec IN poxv_rec_type,
1017 p_db_poxv_rec IN poxv_rec_type
1018 ) RETURN VARCHAR2 IS
1019 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1020 ------------------------------------
1021 -- FUNCTION validate_foreign_keys --
1022 ------------------------------------
1023 FUNCTION validate_foreign_keys (
1024 p_poxv_rec IN poxv_rec_type,
1025 p_db_poxv_rec IN poxv_rec_type
1026 ) RETURN VARCHAR2 IS
1027 item_not_found_error EXCEPTION;
1028 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1029 l_row_notfound BOOLEAN := TRUE;
1030 BEGIN
1031 RETURN (l_return_status);
1032 EXCEPTION
1033 WHEN item_not_found_error THEN
1034 l_return_status := OKL_API.G_RET_STS_ERROR;
1035 RETURN (l_return_status);
1036 END validate_foreign_keys;
1037 BEGIN
1038 l_return_status := validate_foreign_keys(p_poxv_rec, p_db_poxv_rec);
1039 -- mvasudev added, 11/08/2002
1040 validate_unique_pox_record(p_poxv_rec, l_return_status );
1041 RETURN (l_return_status);
1042 END Validate_Record;
1043 FUNCTION Validate_Record (
1044 p_poxv_rec IN poxv_rec_type
1045 ) RETURN VARCHAR2 IS
1046 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1047 l_db_poxv_rec poxv_rec_type := get_rec(p_poxv_rec);
1048 BEGIN
1049 l_return_status := Validate_Record(p_poxv_rec => p_poxv_rec,
1050 p_db_poxv_rec => l_db_poxv_rec);
1051 RETURN (l_return_status);
1052 END Validate_Record;
1053
1054
1055 ---------------------------------------------------------------------------
1056 -- PROCEDURE Migrate
1057 ---------------------------------------------------------------------------
1058 PROCEDURE migrate (
1059 p_from IN poxv_rec_type,
1060 p_to IN OUT NOCOPY pox_rec_type
1061 ) IS
1062 BEGIN
1063 p_to.id := p_from.id;
1064 p_to.object_version_number := p_from.object_version_number;
1065 p_to.pol_id := p_from.pol_id;
1066 p_to.transaction_number := p_from.transaction_number;
1067 p_to.transaction_date := p_from.transaction_date;
1068 p_to.transaction_type := p_from.transaction_type;
1069 p_to.transaction_sub_type := p_from.transaction_sub_type;
1070 p_to.date_effective := p_from.date_effective;
1071 p_to.currency_code := p_from.currency_code;
1072 p_to.currency_conversion_type := p_from.currency_conversion_type;
1073 p_to.currency_conversion_date := p_from.currency_conversion_date;
1074 p_to.currency_conversion_rate := p_from.currency_conversion_rate;
1075 p_to.transaction_reason := p_from.transaction_reason;
1076 p_to.attribute_category := p_from.attribute_category;
1077 p_to.attribute1 := p_from.attribute1;
1078 p_to.attribute2 := p_from.attribute2;
1079 p_to.attribute3 := p_from.attribute3;
1080 p_to.attribute4 := p_from.attribute4;
1081 p_to.attribute5 := p_from.attribute5;
1082 p_to.attribute6 := p_from.attribute6;
1083 p_to.attribute7 := p_from.attribute7;
1084 p_to.attribute8 := p_from.attribute8;
1085 p_to.attribute9 := p_from.attribute9;
1086 p_to.attribute10 := p_from.attribute10;
1087 p_to.attribute11 := p_from.attribute11;
1088 p_to.attribute12 := p_from.attribute12;
1089 p_to.attribute13 := p_from.attribute13;
1090 p_to.attribute14 := p_from.attribute14;
1091 p_to.attribute15 := p_from.attribute15;
1092 p_to.request_id := p_from.request_id;
1093 p_to.program_application_id := p_from.program_application_id;
1094 p_to.program_id := p_from.program_id;
1095 p_to.program_update_date := p_from.program_update_date;
1096 p_to.created_by := p_from.created_by;
1097 p_to.creation_date := p_from.creation_date;
1098 p_to.last_updated_by := p_from.last_updated_by;
1099 p_to.last_update_date := p_from.last_update_date;
1100 p_to.last_update_login := p_from.last_update_login;
1101 p_to.legal_entity_id := p_from.legal_entity_id;
1102 p_to.transaction_status := p_from.transaction_status;
1103
1104 END migrate;
1105 PROCEDURE migrate (
1106 p_from IN pox_rec_type,
1107 p_to IN OUT NOCOPY poxv_rec_type
1108 ) IS
1109 BEGIN
1110 p_to.id := p_from.id;
1111 p_to.object_version_number := p_from.object_version_number;
1112 p_to.pol_id := p_from.pol_id;
1113 p_to.transaction_number := p_from.transaction_number;
1114 p_to.transaction_date := p_from.transaction_date;
1115 p_to.transaction_type := p_from.transaction_type;
1116 p_to.transaction_sub_type := p_from.transaction_sub_type;
1117 p_to.date_effective := p_from.date_effective;
1118 p_to.currency_code := p_from.currency_code;
1119 p_to.currency_conversion_type := p_from.currency_conversion_type;
1120 p_to.currency_conversion_date := p_from.currency_conversion_date;
1121 p_to.currency_conversion_rate := p_from.currency_conversion_rate;
1122 p_to.transaction_reason := p_from.transaction_reason;
1123 p_to.attribute_category := p_from.attribute_category;
1124 p_to.attribute1 := p_from.attribute1;
1125 p_to.attribute2 := p_from.attribute2;
1126 p_to.attribute3 := p_from.attribute3;
1127 p_to.attribute4 := p_from.attribute4;
1128 p_to.attribute5 := p_from.attribute5;
1129 p_to.attribute6 := p_from.attribute6;
1130 p_to.attribute7 := p_from.attribute7;
1131 p_to.attribute8 := p_from.attribute8;
1132 p_to.attribute9 := p_from.attribute9;
1133 p_to.attribute10 := p_from.attribute10;
1134 p_to.attribute11 := p_from.attribute11;
1135 p_to.attribute12 := p_from.attribute12;
1136 p_to.attribute13 := p_from.attribute13;
1137 p_to.attribute14 := p_from.attribute14;
1138 p_to.attribute15 := p_from.attribute15;
1139 p_to.request_id := p_from.request_id;
1140 p_to.program_application_id := p_from.program_application_id;
1141 p_to.program_id := p_from.program_id;
1142 p_to.program_update_date := p_from.program_update_date;
1143 p_to.created_by := p_from.created_by;
1144 p_to.creation_date := p_from.creation_date;
1145 p_to.last_updated_by := p_from.last_updated_by;
1146 p_to.last_update_date := p_from.last_update_date;
1147 p_to.last_update_login := p_from.last_update_login;
1148 p_to.legal_entity_id := p_from.legal_entity_id;
1149 p_to.transaction_status := p_from.transaction_status;
1150 END migrate;
1151 ---------------------------------------------------------------------------
1152 -- PROCEDURE validate_row
1153 ---------------------------------------------------------------------------
1154 --------------------------------------------
1155 -- validate_row for:OKL_POOL_TRANSACTIONS_V --
1156 --------------------------------------------
1157 PROCEDURE validate_row(
1158 p_api_version IN NUMBER,
1159 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1160 x_return_status OUT NOCOPY VARCHAR2,
1161 x_msg_count OUT NOCOPY NUMBER,
1162 x_msg_data OUT NOCOPY VARCHAR2,
1163 p_poxv_rec IN poxv_rec_type) IS
1164
1165 l_api_version CONSTANT NUMBER := 1;
1166 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1167 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1168 l_poxv_rec poxv_rec_type := p_poxv_rec;
1169 l_pox_rec pox_rec_type;
1170 l_pox_rec pox_rec_type;
1171 BEGIN
1172 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1173 G_PKG_NAME,
1174 p_init_msg_list,
1175 l_api_version,
1176 p_api_version,
1177 '_PVT',
1178 x_return_status);
1179 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1180 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1181 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1182 RAISE OKL_API.G_EXCEPTION_ERROR;
1183 END IF;
1184 --- Validate all non-missing attributes (Item Level Validation)
1185 l_return_status := Validate_Attributes(l_poxv_rec);
1186 --- If any errors happen abort API
1187 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1188 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1189 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1190 RAISE OKL_API.G_EXCEPTION_ERROR;
1191 END IF;
1192 l_return_status := Validate_Record(l_poxv_rec);
1193 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1194 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1195 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1196 RAISE OKL_API.G_EXCEPTION_ERROR;
1197 END IF;
1198 x_return_status := l_return_status;
1199 EXCEPTION
1200 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1201 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1202 (
1203 l_api_name,
1204 G_PKG_NAME,
1205 'OKL_API.G_RET_STS_ERROR',
1206 x_msg_count,
1207 x_msg_data,
1208 '_PVT'
1209 );
1210 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1211 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1212 (
1213 l_api_name,
1214 G_PKG_NAME,
1215 'OKL_API.G_RET_STS_UNEXP_ERROR',
1216 x_msg_count,
1217 x_msg_data,
1218 '_PVT'
1219 );
1220 WHEN OTHERS THEN
1221 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1222 (
1223 l_api_name,
1224 G_PKG_NAME,
1225 'OTHERS',
1226 x_msg_count,
1227 x_msg_data,
1228 '_PVT'
1229 );
1230 END validate_row;
1231 -------------------------------------------------------
1232 -- PL/SQL TBL validate_row for:OKL_POOL_TRANSACTIONS_V --
1233 -------------------------------------------------------
1234 PROCEDURE validate_row(
1235 p_api_version IN NUMBER,
1236 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1237 x_return_status OUT NOCOPY VARCHAR2,
1238 x_msg_count OUT NOCOPY NUMBER,
1239 x_msg_data OUT NOCOPY VARCHAR2,
1240 p_poxv_tbl IN poxv_tbl_type,
1241 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1242
1243 l_api_version CONSTANT NUMBER := 1;
1244 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_validate_row';
1245 i NUMBER := 0;
1246 BEGIN
1247 OKL_API.init_msg_list(p_init_msg_list);
1248 -- Make sure PL/SQL table has records in it before passing
1249 IF (p_poxv_tbl.COUNT > 0) THEN
1250 i := p_poxv_tbl.FIRST;
1251 LOOP
1252 DECLARE
1253 l_error_rec OKL_API.ERROR_REC_TYPE;
1254 BEGIN
1255 l_error_rec.api_name := l_api_name;
1256 l_error_rec.api_package := G_PKG_NAME;
1257 l_error_rec.idx := i;
1258 validate_row (
1259 p_api_version => p_api_version,
1260 p_init_msg_list => OKL_API.G_FALSE,
1261 x_return_status => l_error_rec.error_type,
1262 x_msg_count => l_error_rec.msg_count,
1263 x_msg_data => l_error_rec.msg_data,
1264 p_poxv_rec => p_poxv_tbl(i));
1265 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
1266 l_error_rec.SQLCODE := SQLCODE;
1267 load_error_tbl(l_error_rec, px_error_tbl);
1268 ELSE
1269 x_msg_count := l_error_rec.msg_count;
1270 x_msg_data := l_error_rec.msg_data;
1271 END IF;
1272 EXCEPTION
1273 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1274 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
1275 l_error_rec.SQLCODE := SQLCODE;
1276 load_error_tbl(l_error_rec, px_error_tbl);
1277 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1278 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
1279 l_error_rec.SQLCODE := SQLCODE;
1280 load_error_tbl(l_error_rec, px_error_tbl);
1281 WHEN OTHERS THEN
1282 l_error_rec.error_type := 'OTHERS';
1283 l_error_rec.SQLCODE := SQLCODE;
1284 load_error_tbl(l_error_rec, px_error_tbl);
1285 END;
1286 EXIT WHEN (i = p_poxv_tbl.LAST);
1287 i := p_poxv_tbl.NEXT(i);
1288 END LOOP;
1289 END IF;
1290 -- Loop through the error_tbl to find the error with the highest severity
1291 -- and return it.
1292 x_return_status := find_highest_exception(px_error_tbl);
1293 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1294 EXCEPTION
1295 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1296 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1297 (
1298 l_api_name,
1299 G_PKG_NAME,
1300 'OKL_API.G_RET_STS_ERROR',
1301 x_msg_count,
1302 x_msg_data,
1303 '_PVT'
1304 );
1305 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1306 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1307 (
1308 l_api_name,
1309 G_PKG_NAME,
1310 'OKL_API.G_RET_STS_UNEXP_ERROR',
1311 x_msg_count,
1312 x_msg_data,
1313 '_PVT'
1314 );
1315 WHEN OTHERS THEN
1316 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1317 (
1318 l_api_name,
1319 G_PKG_NAME,
1320 'OTHERS',
1321 x_msg_count,
1322 x_msg_data,
1323 '_PVT'
1324 );
1325 END validate_row;
1326
1327 -------------------------------------------------------
1328 -- PL/SQL TBL validate_row for:OKL_POOL_TRANSACTIONS_V --
1329 -------------------------------------------------------
1330 PROCEDURE validate_row(
1331 p_api_version IN NUMBER,
1332 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1333 x_return_status OUT NOCOPY VARCHAR2,
1334 x_msg_count OUT NOCOPY NUMBER,
1335 x_msg_data OUT NOCOPY VARCHAR2,
1336 p_poxv_tbl IN poxv_tbl_type) IS
1337
1338 l_api_version CONSTANT NUMBER := 1;
1339 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1340 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1341 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1342 BEGIN
1343 OKL_API.init_msg_list(p_init_msg_list);
1344 -- Make sure PL/SQL table has records in it before passing
1345 IF (p_poxv_tbl.COUNT > 0) THEN
1346 validate_row (
1347 p_api_version => p_api_version,
1348 p_init_msg_list => OKL_API.G_FALSE,
1349 x_return_status => x_return_status,
1350 x_msg_count => x_msg_count,
1351 x_msg_data => x_msg_data,
1352 p_poxv_tbl => p_poxv_tbl,
1353 px_error_tbl => l_error_tbl);
1354 END IF;
1355 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1356 EXCEPTION
1357 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1358 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1359 (
1360 l_api_name,
1361 G_PKG_NAME,
1362 'OKL_API.G_RET_STS_ERROR',
1363 x_msg_count,
1364 x_msg_data,
1365 '_PVT'
1366 );
1367 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1368 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1369 (
1370 l_api_name,
1371 G_PKG_NAME,
1372 'OKL_API.G_RET_STS_UNEXP_ERROR',
1373 x_msg_count,
1374 x_msg_data,
1375 '_PVT'
1376 );
1377 WHEN OTHERS THEN
1378 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1379 (
1380 l_api_name,
1381 G_PKG_NAME,
1382 'OTHERS',
1383 x_msg_count,
1384 x_msg_data,
1385 '_PVT'
1386 );
1387 END validate_row;
1388
1389 ---------------------------------------------------------------------------
1390 -- PROCEDURE insert_row
1391 ---------------------------------------------------------------------------
1392 ----------------------------------------
1393 -- insert_row for:okl_pool_transactions --
1394 ----------------------------------------
1395 PROCEDURE insert_row(
1396 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1397 x_return_status OUT NOCOPY VARCHAR2,
1398 x_msg_count OUT NOCOPY NUMBER,
1399 x_msg_data OUT NOCOPY VARCHAR2,
1400 p_pox_rec IN pox_rec_type,
1401 x_pox_rec OUT NOCOPY pox_rec_type) IS
1402
1403 l_api_version CONSTANT NUMBER := 1;
1404 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1405 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1406 l_pox_rec pox_rec_type := p_pox_rec;
1407 l_def_pox_rec pox_rec_type;
1408 --------------------------------------------
1409 -- Set_Attributes for:okl_pool_transactions --
1410 --------------------------------------------
1411 FUNCTION Set_Attributes (
1412 p_pox_rec IN pox_rec_type,
1413 x_pox_rec OUT NOCOPY pox_rec_type
1414 ) RETURN VARCHAR2 IS
1415 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1416 BEGIN
1417 x_pox_rec := p_pox_rec;
1418 RETURN(l_return_status);
1419 END Set_Attributes;
1420 BEGIN
1421 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1422 p_init_msg_list,
1423 '_PVT',
1424 x_return_status);
1425 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1426 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1427 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1428 RAISE OKL_API.G_EXCEPTION_ERROR;
1429 END IF;
1430 --- Setting item atributes
1431 l_return_status := Set_Attributes(
1432 p_pox_rec, -- IN
1433 l_pox_rec); -- OUT
1434 --- If any errors happen abort API
1435 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1436 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1437 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1438 RAISE OKL_API.G_EXCEPTION_ERROR;
1439 END IF;
1440 INSERT INTO okl_pool_transactions(
1441 ID,
1442 OBJECT_VERSION_NUMBER,
1443 POL_ID,
1444 TRANSACTION_NUMBER,
1445 TRANSACTION_DATE,
1446 TRANSACTION_TYPE,
1447 TRANSACTION_SUB_TYPE,
1448 DATE_EFFECTIVE,
1449 CURRENCY_CODE,
1450 CURRENCY_CONVERSION_TYPE,
1451 CURRENCY_CONVERSION_DATE,
1452 CURRENCY_CONVERSION_RATE,
1453 TRANSACTION_REASON,
1454 ATTRIBUTE_CATEGORY,
1455 ATTRIBUTE1,
1456 ATTRIBUTE2,
1457 ATTRIBUTE3,
1458 ATTRIBUTE4,
1459 ATTRIBUTE5,
1460 ATTRIBUTE6,
1461 ATTRIBUTE7,
1462 ATTRIBUTE8,
1463 ATTRIBUTE9,
1464 ATTRIBUTE10,
1465 ATTRIBUTE11,
1466 ATTRIBUTE12,
1467 ATTRIBUTE13,
1468 ATTRIBUTE14,
1469 ATTRIBUTE15,
1470 REQUEST_ID,
1471 PROGRAM_APPLICATION_ID,
1472 PROGRAM_ID,
1473 PROGRAM_UPDATE_DATE,
1474 CREATED_BY,
1475 CREATION_DATE,
1476 LAST_UPDATED_BY,
1477 LAST_UPDATE_DATE,
1478 LAST_UPDATE_LOGIN,
1479 LEGAL_ENTITY_ID,
1480 TRANSACTION_STATUS)
1481 VALUES (
1482 l_pox_rec.id,
1483 l_pox_rec.object_version_number,
1484 l_pox_rec.pol_id,
1485 l_pox_rec.transaction_number,
1486 l_pox_rec.transaction_date,
1487 l_pox_rec.transaction_type,
1488 l_pox_rec.transaction_sub_type,
1489 l_pox_rec.date_effective,
1490 l_pox_rec.currency_code,
1491 l_pox_rec.currency_conversion_type,
1492 l_pox_rec.currency_conversion_date,
1493 l_pox_rec.currency_conversion_rate,
1494 l_pox_rec.transaction_reason,
1495 l_pox_rec.attribute_category,
1496 l_pox_rec.attribute1,
1497 l_pox_rec.attribute2,
1498 l_pox_rec.attribute3,
1499 l_pox_rec.attribute4,
1500 l_pox_rec.attribute5,
1501 l_pox_rec.attribute6,
1502 l_pox_rec.attribute7,
1503 l_pox_rec.attribute8,
1504 l_pox_rec.attribute9,
1505 l_pox_rec.attribute10,
1506 l_pox_rec.attribute11,
1507 l_pox_rec.attribute12,
1508 l_pox_rec.attribute13,
1509 l_pox_rec.attribute14,
1510 l_pox_rec.attribute15,
1511 l_pox_rec.request_id,
1512 l_pox_rec.program_application_id,
1513 l_pox_rec.program_id,
1514 l_pox_rec.program_update_date,
1515 l_pox_rec.created_by,
1516 l_pox_rec.creation_date,
1517 l_pox_rec.last_updated_by,
1518 l_pox_rec.last_update_date,
1519 l_pox_rec.last_update_login,
1520 l_pox_rec.legal_entity_id,
1521 l_pox_rec.transaction_status);
1522 -- Set OUT values
1523 x_pox_rec := l_pox_rec;
1524 x_return_status := l_return_status;
1525 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1526 EXCEPTION
1527 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1528 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1529 (
1530 l_api_name,
1531 G_PKG_NAME,
1532 'OKL_API.G_RET_STS_ERROR',
1533 x_msg_count,
1534 x_msg_data,
1535 '_PVT'
1536 );
1537 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1538 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1539 (
1540 l_api_name,
1541 G_PKG_NAME,
1542 'OKL_API.G_RET_STS_UNEXP_ERROR',
1543 x_msg_count,
1544 x_msg_data,
1545 '_PVT'
1546 );
1547 WHEN OTHERS THEN
1548 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1549 (
1550 l_api_name,
1551 G_PKG_NAME,
1552 'OTHERS',
1553 x_msg_count,
1554 x_msg_data,
1555 '_PVT'
1556 );
1557 END insert_row;
1558 -------------------------------------------
1559 -- insert_row for :OKL_POOL_TRANSACTIONS_V --
1560 -------------------------------------------
1561 PROCEDURE insert_row(
1562 p_api_version IN NUMBER,
1563 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1564 x_return_status OUT NOCOPY VARCHAR2,
1565 x_msg_count OUT NOCOPY NUMBER,
1566 x_msg_data OUT NOCOPY VARCHAR2,
1567 p_poxv_rec IN poxv_rec_type,
1568 x_poxv_rec OUT NOCOPY poxv_rec_type) IS
1569
1570 l_api_version CONSTANT NUMBER := 1;
1571 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1572 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1573 l_poxv_rec poxv_rec_type := p_poxv_rec;
1574 l_def_poxv_rec poxv_rec_type;
1575 l_pox_rec pox_rec_type;
1576 lx_pox_rec pox_rec_type;
1577 -------------------------------
1578 -- FUNCTION fill_who_columns --
1579 -------------------------------
1580 FUNCTION fill_who_columns (
1581 p_poxv_rec IN poxv_rec_type
1582 ) RETURN poxv_rec_type IS
1583 l_poxv_rec poxv_rec_type := p_poxv_rec;
1584 BEGIN
1585 l_poxv_rec.CREATION_DATE := SYSDATE;
1586 l_poxv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1587 l_poxv_rec.LAST_UPDATE_DATE := l_poxv_rec.CREATION_DATE;
1588 l_poxv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1589 l_poxv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1590 RETURN(l_poxv_rec);
1591 END fill_who_columns;
1592 ----------------------------------------------
1593 -- Set_Attributes for:OKL_POOL_TRANSACTIONS_V --
1594 ----------------------------------------------
1595 FUNCTION Set_Attributes (
1596 p_poxv_rec IN poxv_rec_type,
1597 x_poxv_rec OUT NOCOPY poxv_rec_type
1598 ) RETURN VARCHAR2 IS
1599 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1600 BEGIN
1601 x_poxv_rec := p_poxv_rec;
1602 x_poxv_rec.OBJECT_VERSION_NUMBER := 1;
1603
1604 -- concurrent program columns
1605 SELECT DECODE(Fnd_Global.CONC_REQUEST_ID, -1, NULL,Fnd_Global.CONC_REQUEST_ID),
1606 DECODE(Fnd_Global.PROG_APPL_ID, -1, NULL,Fnd_Global.PROG_APPL_ID),
1607 DECODE(Fnd_Global.CONC_PROGRAM_ID, -1, NULL,Fnd_Global.CONC_PROGRAM_ID),
1608 DECODE(Fnd_Global.CONC_REQUEST_ID, -1, NULL, SYSDATE)
1609 INTO x_poxv_rec.REQUEST_ID
1610 ,x_poxv_rec.PROGRAM_APPLICATION_ID
1611 ,x_poxv_rec.PROGRAM_ID
1612 ,x_poxv_rec.PROGRAM_UPDATE_DATE
1613 FROM DUAL;
1614 RETURN(l_return_status);
1615 END Set_Attributes;
1616 BEGIN
1617 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1618 G_PKG_NAME,
1619 p_init_msg_list,
1620 l_api_version,
1621 p_api_version,
1622 '_PVT',
1623 x_return_status);
1624 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1625 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1626 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1627 RAISE OKL_API.G_EXCEPTION_ERROR;
1628 END IF;
1629 l_poxv_rec := null_out_defaults(p_poxv_rec);
1630 -- Set primary key value
1631 l_poxv_rec.ID := get_seq_id;
1632 l_poxv_rec.transaction_number := get_trans_num;
1633 -- Setting item attributes
1634 l_return_Status := Set_Attributes(
1635 l_poxv_rec, -- IN
1636 l_def_poxv_rec); -- OUT
1637 --- If any errors happen abort API
1638 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1639 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1640 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1641 RAISE OKL_API.G_EXCEPTION_ERROR;
1642 END IF;
1643 l_def_poxv_rec := fill_who_columns(l_def_poxv_rec);
1644 --- Validate all non-missing attributes (Item Level Validation)
1645 l_return_status := Validate_Attributes(l_def_poxv_rec);
1646 --- If any errors happen abort API
1647 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1648 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1649 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1650 RAISE OKL_API.G_EXCEPTION_ERROR;
1651 END IF;
1652 l_return_status := Validate_Record(l_def_poxv_rec);
1653 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1654 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1655 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1656 RAISE OKL_API.G_EXCEPTION_ERROR;
1657 END IF;
1658 -----------------------------------------
1659 -- Move VIEW record to "Child" records --
1660 -----------------------------------------
1661 migrate(l_def_poxv_rec, l_pox_rec);
1662 -----------------------------------------------
1663 -- Call the INSERT_ROW for each child record --
1664 -----------------------------------------------
1665 insert_row(
1666 p_init_msg_list,
1667 l_return_status,
1668 x_msg_count,
1669 x_msg_data,
1670 l_pox_rec,
1671 lx_pox_rec
1672 );
1673 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1674 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1675 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1676 RAISE OKL_API.G_EXCEPTION_ERROR;
1677 END IF;
1678 migrate(lx_pox_rec, l_def_poxv_rec);
1679 -- Set OUT values
1680 x_poxv_rec := l_def_poxv_rec;
1681 x_return_status := l_return_status;
1682 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1683 EXCEPTION
1684 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1685 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1686 (
1687 l_api_name,
1688 G_PKG_NAME,
1689 'OKL_API.G_RET_STS_ERROR',
1690 x_msg_count,
1691 x_msg_data,
1692 '_PVT'
1693 );
1694 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1695 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1696 (
1697 l_api_name,
1698 G_PKG_NAME,
1699 'OKL_API.G_RET_STS_UNEXP_ERROR',
1700 x_msg_count,
1701 x_msg_data,
1702 '_PVT'
1703 );
1704 WHEN OTHERS THEN
1705 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1706 (
1707 l_api_name,
1708 G_PKG_NAME,
1709 'OTHERS',
1710 x_msg_count,
1711 x_msg_data,
1712 '_PVT'
1713 );
1714 END insert_row;
1715 ----------------------------------------
1716 -- PL/SQL TBL insert_row for:poxv_TBL --
1717 ----------------------------------------
1718 PROCEDURE insert_row(
1719 p_api_version IN NUMBER,
1720 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1721 x_return_status OUT NOCOPY VARCHAR2,
1722 x_msg_count OUT NOCOPY NUMBER,
1723 x_msg_data OUT NOCOPY VARCHAR2,
1724 p_poxv_tbl IN poxv_tbl_type,
1725 x_poxv_tbl OUT NOCOPY poxv_tbl_type,
1726 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
1727
1728 l_api_version CONSTANT NUMBER := 1;
1729 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_insert_row';
1730 i NUMBER := 0;
1731 BEGIN
1732 OKL_API.init_msg_list(p_init_msg_list);
1733 -- Make sure PL/SQL table has records in it before passing
1734 IF (p_poxv_tbl.COUNT > 0) THEN
1735 i := p_poxv_tbl.FIRST;
1736 LOOP
1737 DECLARE
1738 l_error_rec OKL_API.ERROR_REC_TYPE;
1739 BEGIN
1740 l_error_rec.api_name := l_api_name;
1741 l_error_rec.api_package := G_PKG_NAME;
1742 l_error_rec.idx := i;
1743 insert_row (
1744 p_api_version => p_api_version,
1745 p_init_msg_list => OKL_API.G_FALSE,
1746 x_return_status => l_error_rec.error_type,
1747 x_msg_count => l_error_rec.msg_count,
1748 x_msg_data => l_error_rec.msg_data,
1749 p_poxv_rec => p_poxv_tbl(i),
1750 x_poxv_rec => x_poxv_tbl(i));
1751 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
1752 l_error_rec.SQLCODE := SQLCODE;
1753 load_error_tbl(l_error_rec, px_error_tbl);
1754 ELSE
1755 x_msg_count := l_error_rec.msg_count;
1756 x_msg_data := l_error_rec.msg_data;
1757 END IF;
1758 EXCEPTION
1759 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1760 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
1761 l_error_rec.SQLCODE := SQLCODE;
1762 load_error_tbl(l_error_rec, px_error_tbl);
1763 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1764 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
1765 l_error_rec.SQLCODE := SQLCODE;
1766 load_error_tbl(l_error_rec, px_error_tbl);
1767 WHEN OTHERS THEN
1768 l_error_rec.error_type := 'OTHERS';
1769 l_error_rec.SQLCODE := SQLCODE;
1770 load_error_tbl(l_error_rec, px_error_tbl);
1771 END;
1772 EXIT WHEN (i = p_poxv_tbl.LAST);
1773 i := p_poxv_tbl.NEXT(i);
1774 END LOOP;
1775 END IF;
1776 -- Loop through the error_tbl to find the error with the highest severity
1777 -- and return it.
1778 x_return_status := find_highest_exception(px_error_tbl);
1779 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1780 EXCEPTION
1781 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1782 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1783 (
1784 l_api_name,
1785 G_PKG_NAME,
1786 'OKL_API.G_RET_STS_ERROR',
1787 x_msg_count,
1788 x_msg_data,
1789 '_PVT'
1790 );
1791 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1792 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1793 (
1794 l_api_name,
1795 G_PKG_NAME,
1796 'OKL_API.G_RET_STS_UNEXP_ERROR',
1797 x_msg_count,
1798 x_msg_data,
1799 '_PVT'
1800 );
1801 WHEN OTHERS THEN
1802 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1803 (
1804 l_api_name,
1805 G_PKG_NAME,
1806 'OTHERS',
1807 x_msg_count,
1808 x_msg_data,
1809 '_PVT'
1810 );
1811 END insert_row;
1812
1813 ----------------------------------------
1814 -- PL/SQL TBL insert_row for:poxv_TBL --
1815 ----------------------------------------
1816 PROCEDURE insert_row(
1817 p_api_version IN NUMBER,
1818 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1819 x_return_status OUT NOCOPY VARCHAR2,
1820 x_msg_count OUT NOCOPY NUMBER,
1821 x_msg_data OUT NOCOPY VARCHAR2,
1822 p_poxv_tbl IN poxv_tbl_type,
1823 x_poxv_tbl OUT NOCOPY poxv_tbl_type) IS
1824
1825 l_api_version CONSTANT NUMBER := 1;
1826 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1827 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1828 l_error_tbl OKL_API.ERROR_TBL_TYPE;
1829 BEGIN
1830 OKL_API.init_msg_list(p_init_msg_list);
1831 -- Make sure PL/SQL table has records in it before passing
1832 IF (p_poxv_tbl.COUNT > 0) THEN
1833 insert_row (
1834 p_api_version => p_api_version,
1835 p_init_msg_list => OKL_API.G_FALSE,
1836 x_return_status => x_return_status,
1837 x_msg_count => x_msg_count,
1838 x_msg_data => x_msg_data,
1839 p_poxv_tbl => p_poxv_tbl,
1840 x_poxv_tbl => x_poxv_tbl,
1841 px_error_tbl => l_error_tbl);
1842 END IF;
1843 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1844 EXCEPTION
1845 WHEN OKL_API.G_EXCEPTION_ERROR THEN
1846 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1847 (
1848 l_api_name,
1849 G_PKG_NAME,
1850 'OKL_API.G_RET_STS_ERROR',
1851 x_msg_count,
1852 x_msg_data,
1853 '_PVT'
1854 );
1855 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1856 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1857 (
1858 l_api_name,
1859 G_PKG_NAME,
1860 'OKL_API.G_RET_STS_UNEXP_ERROR',
1861 x_msg_count,
1862 x_msg_data,
1863 '_PVT'
1864 );
1865 WHEN OTHERS THEN
1866 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1867 (
1868 l_api_name,
1869 G_PKG_NAME,
1870 'OTHERS',
1871 x_msg_count,
1872 x_msg_data,
1873 '_PVT'
1874 );
1875 END insert_row;
1876
1877 ---------------------------------------------------------------------------
1878 -- PROCEDURE lock_row
1879 ---------------------------------------------------------------------------
1880 --------------------------------------
1881 -- lock_row for:okl_pool_transactions --
1882 --------------------------------------
1883 PROCEDURE lock_row(
1884 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1885 x_return_status OUT NOCOPY VARCHAR2,
1886 x_msg_count OUT NOCOPY NUMBER,
1887 x_msg_data OUT NOCOPY VARCHAR2,
1888 p_pox_rec IN pox_rec_type) IS
1889
1890 E_Resource_Busy EXCEPTION;
1891 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1892 CURSOR lock_csr (p_pox_rec IN pox_rec_type) IS
1893 SELECT OBJECT_VERSION_NUMBER
1894 FROM okl_pool_transactions
1895 WHERE ID = p_pox_rec.id
1896 AND OBJECT_VERSION_NUMBER = p_pox_rec.object_version_number
1897 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1898
1899 CURSOR lchk_csr (p_pox_rec IN pox_rec_type) IS
1900 SELECT OBJECT_VERSION_NUMBER
1901 FROM okl_pool_transactions
1902 WHERE ID = p_pox_rec.id;
1903 l_api_version CONSTANT NUMBER := 1;
1904 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1905 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
1906 l_object_version_number okl_pool_transactions.OBJECT_VERSION_NUMBER%TYPE;
1907 lc_object_version_number okl_pool_transactions.OBJECT_VERSION_NUMBER%TYPE;
1908 l_row_notfound BOOLEAN := FALSE;
1909 lc_row_notfound BOOLEAN := FALSE;
1910 BEGIN
1911 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
1912 p_init_msg_list,
1913 '_PVT',
1914 x_return_status);
1915 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
1916 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
1917 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
1918 RAISE OKL_API.G_EXCEPTION_ERROR;
1919 END IF;
1920 BEGIN
1921 OPEN lock_csr(p_pox_rec);
1922 FETCH lock_csr INTO l_object_version_number;
1923 l_row_notfound := lock_csr%NOTFOUND;
1924 CLOSE lock_csr;
1925 EXCEPTION
1926 WHEN E_Resource_Busy THEN
1927 IF (lock_csr%ISOPEN) THEN
1928 CLOSE lock_csr;
1929 END IF;
1930 OKL_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1931 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1932 END;
1933
1934 IF ( l_row_notfound ) THEN
1935 OPEN lchk_csr(p_pox_rec);
1936 FETCH lchk_csr INTO lc_object_version_number;
1937 lc_row_notfound := lchk_csr%NOTFOUND;
1938 CLOSE lchk_csr;
1939 END IF;
1940 IF (lc_row_notfound) THEN
1941 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1942 RAISE OKL_API.G_EXCEPTION_ERROR;
1943 ELSIF lc_object_version_number > p_pox_rec.object_version_number THEN
1944 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1945 RAISE OKL_API.G_EXCEPTION_ERROR;
1946 ELSIF lc_object_version_number <> p_pox_rec.object_version_number THEN
1947 OKL_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1948 RAISE OKL_API.G_EXCEPTION_ERROR;
1949 ELSIF lc_object_version_number = -1 THEN
1950 OKL_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1951 RAISE OKL_API.G_EXCEPTION_ERROR;
1952 END IF;
1953 x_return_status := l_return_status;
1954 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
1955 EXCEPTION
1956 WHEN OKL_API.G_EXCEPTION_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_ERROR',
1962 x_msg_count,
1963 x_msg_data,
1964 '_PVT'
1965 );
1966 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1967 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1968 (
1969 l_api_name,
1970 G_PKG_NAME,
1971 'OKL_API.G_RET_STS_UNEXP_ERROR',
1972 x_msg_count,
1973 x_msg_data,
1974 '_PVT'
1975 );
1976 WHEN OTHERS THEN
1977 x_return_status := OKL_API.HANDLE_EXCEPTIONS
1978 (
1979 l_api_name,
1980 G_PKG_NAME,
1981 'OTHERS',
1982 x_msg_count,
1983 x_msg_data,
1984 '_PVT'
1985 );
1986 END lock_row;
1987 -----------------------------------------
1988 -- lock_row for: OKL_POOL_TRANSACTIONS_V --
1989 -----------------------------------------
1990 PROCEDURE lock_row(
1991 p_api_version IN NUMBER,
1992 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
1993 x_return_status OUT NOCOPY VARCHAR2,
1994 x_msg_count OUT NOCOPY NUMBER,
1995 x_msg_data OUT NOCOPY VARCHAR2,
1996 p_poxv_rec IN poxv_rec_type) IS
1997
1998 l_api_version CONSTANT NUMBER := 1;
1999 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
2000 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2001 l_pox_rec pox_rec_type;
2002 BEGIN
2003 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2004 G_PKG_NAME,
2005 p_init_msg_list,
2006 l_api_version,
2007 p_api_version,
2008 '_PVT',
2009 x_return_status);
2010 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2011 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2012 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2013 RAISE OKL_API.G_EXCEPTION_ERROR;
2014 END IF;
2015 -----------------------------------------
2016 -- Move VIEW record to "Child" records --
2017 -----------------------------------------
2018 migrate(p_poxv_rec, l_pox_rec);
2019 ---------------------------------------------
2020 -- Call the LOCK_ROW for each child record --
2021 ---------------------------------------------
2022 lock_row(
2023 p_init_msg_list,
2024 l_return_status,
2025 x_msg_count,
2026 x_msg_data,
2027 l_pox_rec
2028 );
2029 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2030 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2031 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2032 RAISE OKL_API.G_EXCEPTION_ERROR;
2033 END IF;
2034 x_return_status := l_return_status;
2035 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2036 EXCEPTION
2037 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2038 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2039 (
2040 l_api_name,
2041 G_PKG_NAME,
2042 'OKL_API.G_RET_STS_ERROR',
2043 x_msg_count,
2044 x_msg_data,
2045 '_PVT'
2046 );
2047 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2048 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2049 (
2050 l_api_name,
2051 G_PKG_NAME,
2052 'OKL_API.G_RET_STS_UNEXP_ERROR',
2053 x_msg_count,
2054 x_msg_data,
2055 '_PVT'
2056 );
2057 WHEN OTHERS THEN
2058 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2059 (
2060 l_api_name,
2061 G_PKG_NAME,
2062 'OTHERS',
2063 x_msg_count,
2064 x_msg_data,
2065 '_PVT'
2066 );
2067 END lock_row;
2068 --------------------------------------
2069 -- PL/SQL TBL lock_row for:poxv_TBL --
2070 --------------------------------------
2071 PROCEDURE lock_row(
2072 p_api_version IN NUMBER,
2073 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2074 x_return_status OUT NOCOPY VARCHAR2,
2075 x_msg_count OUT NOCOPY NUMBER,
2076 x_msg_data OUT NOCOPY VARCHAR2,
2077 p_poxv_tbl IN poxv_tbl_type,
2078 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
2079
2080 l_api_version CONSTANT NUMBER := 1;
2081 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_lock_row';
2082 i NUMBER := 0;
2083 BEGIN
2084 OKL_API.init_msg_list(p_init_msg_list);
2085 -- Make sure PL/SQL table has recrods in it before passing
2086 IF (p_poxv_tbl.COUNT > 0) THEN
2087 i := p_poxv_tbl.FIRST;
2088 LOOP
2089 DECLARE
2090 l_error_rec OKL_API.ERROR_REC_TYPE;
2091 BEGIN
2092 l_error_rec.api_name := l_api_name;
2093 l_error_rec.api_package := G_PKG_NAME;
2094 l_error_rec.idx := i;
2095 lock_row(
2096 p_api_version => p_api_version,
2097 p_init_msg_list => OKL_API.G_FALSE,
2098 x_return_status => l_error_rec.error_type,
2099 x_msg_count => l_error_rec.msg_count,
2100 x_msg_data => l_error_rec.msg_data,
2101 p_poxv_rec => p_poxv_tbl(i));
2102 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
2103 l_error_rec.SQLCODE := SQLCODE;
2104 load_error_tbl(l_error_rec, px_error_tbl);
2105 ELSE
2106 x_msg_count := l_error_rec.msg_count;
2107 x_msg_data := l_error_rec.msg_data;
2108 END IF;
2109 EXCEPTION
2110 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2111 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
2112 l_error_rec.SQLCODE := SQLCODE;
2113 load_error_tbl(l_error_rec, px_error_tbl);
2114 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2115 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
2116 l_error_rec.SQLCODE := SQLCODE;
2117 load_error_tbl(l_error_rec, px_error_tbl);
2118 WHEN OTHERS THEN
2119 l_error_rec.error_type := 'OTHERS';
2120 l_error_rec.SQLCODE := SQLCODE;
2121 load_error_tbl(l_error_rec, px_error_tbl);
2122 END;
2123 EXIT WHEN (i = p_poxv_tbl.LAST);
2124 i := p_poxv_tbl.NEXT(i);
2125 END LOOP;
2126 END IF;
2127 -- Loop through the error_tbl to find the error with the highest severity
2128 -- and return it.
2129 x_return_status := find_highest_exception(px_error_tbl);
2130 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2131 EXCEPTION
2132 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2133 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2134 (
2135 l_api_name,
2136 G_PKG_NAME,
2137 'OKL_API.G_RET_STS_ERROR',
2138 x_msg_count,
2139 x_msg_data,
2140 '_PVT'
2141 );
2142 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2143 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2144 (
2145 l_api_name,
2146 G_PKG_NAME,
2147 'OKL_API.G_RET_STS_UNEXP_ERROR',
2148 x_msg_count,
2149 x_msg_data,
2150 '_PVT'
2151 );
2152 WHEN OTHERS THEN
2153 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2154 (
2155 l_api_name,
2156 G_PKG_NAME,
2157 'OTHERS',
2158 x_msg_count,
2159 x_msg_data,
2160 '_PVT'
2161 );
2162 END lock_row;
2163 --------------------------------------
2164 -- PL/SQL TBL lock_row for:poxv_TBL --
2165 --------------------------------------
2166 PROCEDURE lock_row(
2167 p_api_version IN NUMBER,
2168 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2169 x_return_status OUT NOCOPY VARCHAR2,
2170 x_msg_count OUT NOCOPY NUMBER,
2171 x_msg_data OUT NOCOPY VARCHAR2,
2172 p_poxv_tbl IN poxv_tbl_type) IS
2173
2174 l_api_version CONSTANT NUMBER := 1;
2175 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2176 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2177 l_error_tbl OKL_API.ERROR_TBL_TYPE;
2178 BEGIN
2179 OKL_API.init_msg_list(p_init_msg_list);
2180 -- Make sure PL/SQL table has recrods in it before passing
2181 IF (p_poxv_tbl.COUNT > 0) THEN
2182 lock_row(
2183 p_api_version => p_api_version,
2184 p_init_msg_list => OKL_API.G_FALSE,
2185 x_return_status => x_return_status,
2186 x_msg_count => x_msg_count,
2187 x_msg_data => x_msg_data,
2188 p_poxv_tbl => p_poxv_tbl,
2189 px_error_tbl => l_error_tbl);
2190 END IF;
2191 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2192 EXCEPTION
2193 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2194 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2195 (
2196 l_api_name,
2197 G_PKG_NAME,
2198 'OKL_API.G_RET_STS_ERROR',
2199 x_msg_count,
2200 x_msg_data,
2201 '_PVT'
2202 );
2203 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2204 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2205 (
2206 l_api_name,
2207 G_PKG_NAME,
2208 'OKL_API.G_RET_STS_UNEXP_ERROR',
2209 x_msg_count,
2210 x_msg_data,
2211 '_PVT'
2212 );
2213 WHEN OTHERS THEN
2214 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2215 (
2216 l_api_name,
2217 G_PKG_NAME,
2218 'OTHERS',
2219 x_msg_count,
2220 x_msg_data,
2221 '_PVT'
2222 );
2223 END lock_row;
2224 ---------------------------------------------------------------------------
2225 -- PROCEDURE update_row
2226 ---------------------------------------------------------------------------
2227 ----------------------------------------
2228 -- update_row for:okl_pool_transactions --
2229 ----------------------------------------
2230 PROCEDURE update_row(
2231 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2232 x_return_status OUT NOCOPY VARCHAR2,
2233 x_msg_count OUT NOCOPY NUMBER,
2234 x_msg_data OUT NOCOPY VARCHAR2,
2235 p_pox_rec IN pox_rec_type,
2236 x_pox_rec OUT NOCOPY pox_rec_type) IS
2237
2238 l_api_version CONSTANT NUMBER := 1;
2239 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
2240 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2241 l_pox_rec pox_rec_type := p_pox_rec;
2242 l_def_pox_rec pox_rec_type;
2243 l_row_notfound BOOLEAN := TRUE;
2244 ----------------------------------
2245 -- FUNCTION populate_new_record --
2246 ----------------------------------
2247 FUNCTION populate_new_record (
2248 p_pox_rec IN pox_rec_type,
2249 x_pox_rec OUT NOCOPY pox_rec_type
2250 ) RETURN VARCHAR2 IS
2251 l_pox_rec pox_rec_type;
2252 l_row_notfound BOOLEAN := TRUE;
2253 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2254 BEGIN
2255 x_pox_rec := p_pox_rec;
2256 -- Get current database values
2257 l_pox_rec := get_rec(p_pox_rec, l_return_status);
2258 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
2259
2260 IF (x_pox_rec.id = OKL_API.G_MISS_NUM)
2261 THEN
2262 x_pox_rec.id := l_pox_rec.id;
2263 END IF;
2264 IF (x_pox_rec.object_version_number = OKL_API.G_MISS_NUM)
2265 THEN
2266 x_pox_rec.object_version_number := l_pox_rec.object_version_number;
2267 END IF;
2268 IF (x_pox_rec.pol_id = OKL_API.G_MISS_NUM)
2269 THEN
2270 x_pox_rec.pol_id := l_pox_rec.pol_id;
2271 END IF;
2272 IF (x_pox_rec.transaction_number = OKL_API.G_MISS_NUM)
2273 THEN
2274 x_pox_rec.transaction_number := l_pox_rec.transaction_number;
2275 END IF;
2276 IF (x_pox_rec.transaction_date = OKL_API.G_MISS_DATE)
2277 THEN
2278 x_pox_rec.transaction_date := l_pox_rec.transaction_date;
2279 END IF;
2280 IF (x_pox_rec.transaction_type = OKL_API.G_MISS_CHAR)
2281 THEN
2282 x_pox_rec.transaction_type := l_pox_rec.transaction_type;
2283 END IF;
2284 IF (x_pox_rec.transaction_sub_type = OKL_API.G_MISS_CHAR)
2285 THEN
2286 x_pox_rec.transaction_sub_type := l_pox_rec.transaction_sub_type;
2287 END IF;
2288 IF (x_pox_rec.date_effective = OKL_API.G_MISS_DATE)
2289 THEN
2290 x_pox_rec.date_effective := l_pox_rec.date_effective;
2291 END IF;
2292 IF (x_pox_rec.currency_code = OKL_API.G_MISS_CHAR)
2293 THEN
2294 x_pox_rec.currency_code := l_pox_rec.currency_code;
2295 END IF;
2296 IF (x_pox_rec.currency_conversion_type = OKL_API.G_MISS_CHAR)
2297 THEN
2298 x_pox_rec.currency_conversion_type := l_pox_rec.currency_conversion_type;
2299 END IF;
2300 IF (x_pox_rec.currency_conversion_date = OKL_API.G_MISS_DATE)
2301 THEN
2302 x_pox_rec.currency_conversion_date := l_pox_rec.currency_conversion_date;
2303 END IF;
2304 IF (x_pox_rec.currency_conversion_rate = OKL_API.G_MISS_NUM)
2305 THEN
2306 x_pox_rec.currency_conversion_rate := l_pox_rec.currency_conversion_rate;
2307 END IF;
2308 IF (x_pox_rec.transaction_reason = OKL_API.G_MISS_CHAR)
2309 THEN
2310 x_pox_rec.transaction_reason := l_pox_rec.transaction_reason;
2311 END IF;
2312 IF (x_pox_rec.attribute_category = OKL_API.G_MISS_CHAR)
2313 THEN
2314 x_pox_rec.attribute_category := l_pox_rec.attribute_category;
2315 END IF;
2316 IF (x_pox_rec.attribute1 = OKL_API.G_MISS_CHAR)
2317 THEN
2318 x_pox_rec.attribute1 := l_pox_rec.attribute1;
2319 END IF;
2320 IF (x_pox_rec.attribute2 = OKL_API.G_MISS_CHAR)
2321 THEN
2322 x_pox_rec.attribute2 := l_pox_rec.attribute2;
2323 END IF;
2324 IF (x_pox_rec.attribute3 = OKL_API.G_MISS_CHAR)
2325 THEN
2326 x_pox_rec.attribute3 := l_pox_rec.attribute3;
2327 END IF;
2328 IF (x_pox_rec.attribute4 = OKL_API.G_MISS_CHAR)
2329 THEN
2330 x_pox_rec.attribute4 := l_pox_rec.attribute4;
2331 END IF;
2332 IF (x_pox_rec.attribute5 = OKL_API.G_MISS_CHAR)
2333 THEN
2334 x_pox_rec.attribute5 := l_pox_rec.attribute5;
2335 END IF;
2336 IF (x_pox_rec.attribute6 = OKL_API.G_MISS_CHAR)
2337 THEN
2338 x_pox_rec.attribute6 := l_pox_rec.attribute6;
2339 END IF;
2340 IF (x_pox_rec.attribute7 = OKL_API.G_MISS_CHAR)
2341 THEN
2342 x_pox_rec.attribute7 := l_pox_rec.attribute7;
2343 END IF;
2344 IF (x_pox_rec.attribute8 = OKL_API.G_MISS_CHAR)
2345 THEN
2346 x_pox_rec.attribute8 := l_pox_rec.attribute8;
2347 END IF;
2348 IF (x_pox_rec.attribute9 = OKL_API.G_MISS_CHAR)
2349 THEN
2350 x_pox_rec.attribute9 := l_pox_rec.attribute9;
2351 END IF;
2352 IF (x_pox_rec.attribute10 = OKL_API.G_MISS_CHAR)
2353 THEN
2354 x_pox_rec.attribute10 := l_pox_rec.attribute10;
2355 END IF;
2356 IF (x_pox_rec.attribute11 = OKL_API.G_MISS_CHAR)
2357 THEN
2358 x_pox_rec.attribute11 := l_pox_rec.attribute11;
2359 END IF;
2360 IF (x_pox_rec.attribute12 = OKL_API.G_MISS_CHAR)
2361 THEN
2362 x_pox_rec.attribute12 := l_pox_rec.attribute12;
2363 END IF;
2364 IF (x_pox_rec.attribute13 = OKL_API.G_MISS_CHAR)
2365 THEN
2366 x_pox_rec.attribute13 := l_pox_rec.attribute13;
2367 END IF;
2368 IF (x_pox_rec.attribute14 = OKL_API.G_MISS_CHAR)
2369 THEN
2370 x_pox_rec.attribute14 := l_pox_rec.attribute14;
2371 END IF;
2372 IF (x_pox_rec.attribute15 = OKL_API.G_MISS_CHAR)
2373 THEN
2374 x_pox_rec.attribute15 := l_pox_rec.attribute15;
2375 END IF;
2376 IF (x_pox_rec.request_id = OKL_API.G_MISS_NUM)
2377 THEN
2378 x_pox_rec.request_id := l_pox_rec.request_id;
2379 END IF;
2380 IF (x_pox_rec.program_application_id = OKL_API.G_MISS_NUM)
2381 THEN
2382 x_pox_rec.program_application_id := l_pox_rec.program_application_id;
2383 END IF;
2384 IF (x_pox_rec.program_id = OKL_API.G_MISS_NUM)
2385 THEN
2386 x_pox_rec.program_id := l_pox_rec.program_id;
2387 END IF;
2388 IF (x_pox_rec.program_update_date = OKL_API.G_MISS_DATE)
2389 THEN
2390 x_pox_rec.program_update_date := l_pox_rec.program_update_date;
2391 END IF;
2392 IF (x_pox_rec.created_by = OKL_API.G_MISS_NUM)
2393 THEN
2394 x_pox_rec.created_by := l_pox_rec.created_by;
2395 END IF;
2396 IF (x_pox_rec.creation_date = OKL_API.G_MISS_DATE)
2397 THEN
2398 x_pox_rec.creation_date := l_pox_rec.creation_date;
2399 END IF;
2400 IF (x_pox_rec.last_updated_by = OKL_API.G_MISS_NUM)
2401 THEN
2402 x_pox_rec.last_updated_by := l_pox_rec.last_updated_by;
2403 END IF;
2404 IF (x_pox_rec.last_update_date = OKL_API.G_MISS_DATE)
2405 THEN
2406 x_pox_rec.last_update_date := l_pox_rec.last_update_date;
2407 END IF;
2408 IF (x_pox_rec.last_update_login = OKL_API.G_MISS_NUM)
2409 THEN
2410 x_pox_rec.last_update_login := l_pox_rec.last_update_login;
2411 END IF;
2412 IF (x_pox_rec.legal_entity_id = OKL_API.G_MISS_NUM)
2413 THEN
2414 x_pox_rec.legal_entity_id := l_pox_rec.legal_entity_id;
2415 END IF;
2416 IF (x_pox_rec.transaction_status = OKL_API.G_MISS_CHAR)
2417 THEN
2418 x_pox_rec.transaction_status := l_pox_rec.transaction_status;
2419 END IF;
2420 END IF;
2421 RETURN(l_return_status);
2422 END populate_new_record;
2423 --------------------------------------------
2424 -- Set_Attributes for:okl_pool_transactions --
2425 --------------------------------------------
2426 FUNCTION Set_Attributes (
2427 p_pox_rec IN pox_rec_type,
2428 x_pox_rec OUT NOCOPY pox_rec_type
2429 ) RETURN VARCHAR2 IS
2430 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2431 BEGIN
2432 x_pox_rec := p_pox_rec;
2433 x_pox_rec.OBJECT_VERSION_NUMBER := p_pox_rec.OBJECT_VERSION_NUMBER + 1;
2434 RETURN(l_return_status);
2435 END Set_Attributes;
2436 BEGIN
2437 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2438 p_init_msg_list,
2439 '_PVT',
2440 x_return_status);
2441 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2442 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2443 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2444 RAISE OKL_API.G_EXCEPTION_ERROR;
2445 END IF;
2446 --- Setting item attributes
2447 l_return_status := Set_Attributes(
2448 p_pox_rec, -- IN
2449 l_pox_rec); -- OUT
2450 --- If any errors happen abort API
2451 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2452 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2453 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2454 RAISE OKL_API.G_EXCEPTION_ERROR;
2455 END IF;
2456 l_return_status := populate_new_record(l_pox_rec, l_def_pox_rec);
2457 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2458 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2459 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2460 RAISE OKL_API.G_EXCEPTION_ERROR;
2461 END IF;
2462 UPDATE okl_pool_transactions
2463 SET OBJECT_VERSION_NUMBER = l_def_pox_rec.object_version_number,
2464 POL_ID = l_def_pox_rec.pol_id,
2465 transaction_number = l_def_pox_rec.transaction_number,
2466 transaction_date = l_def_pox_rec.transaction_date,
2467 transaction_type = l_def_pox_rec.transaction_type,
2468 transaction_sub_type = l_def_pox_rec.transaction_sub_type,
2469 date_effective = l_def_pox_rec.date_effective,
2470 currency_code = l_def_pox_rec.currency_code,
2471 currency_conversion_type = l_def_pox_rec.currency_conversion_type,
2472 currency_conversion_date = l_def_pox_rec.currency_conversion_date,
2473 currency_conversion_rate = l_def_pox_rec.currency_conversion_rate,
2474 transaction_reason = l_def_pox_rec.transaction_reason,
2475 ATTRIBUTE_CATEGORY = l_def_pox_rec.attribute_category,
2476 ATTRIBUTE1 = l_def_pox_rec.attribute1,
2477 ATTRIBUTE2 = l_def_pox_rec.attribute2,
2478 ATTRIBUTE3 = l_def_pox_rec.attribute3,
2479 ATTRIBUTE4 = l_def_pox_rec.attribute4,
2480 ATTRIBUTE5 = l_def_pox_rec.attribute5,
2481 ATTRIBUTE6 = l_def_pox_rec.attribute6,
2482 ATTRIBUTE7 = l_def_pox_rec.attribute7,
2483 ATTRIBUTE8 = l_def_pox_rec.attribute8,
2484 ATTRIBUTE9 = l_def_pox_rec.attribute9,
2485 ATTRIBUTE10 = l_def_pox_rec.attribute10,
2486 ATTRIBUTE11 = l_def_pox_rec.attribute11,
2487 ATTRIBUTE12 = l_def_pox_rec.attribute12,
2488 ATTRIBUTE13 = l_def_pox_rec.attribute13,
2489 ATTRIBUTE14 = l_def_pox_rec.attribute14,
2490 ATTRIBUTE15 = l_def_pox_rec.attribute15,
2491 REQUEST_ID = l_def_pox_rec.request_id,
2492 PROGRAM_APPLICATION_ID = l_def_pox_rec.program_application_id,
2493 PROGRAM_ID = l_def_pox_rec.program_id,
2494 PROGRAM_UPDATE_DATE = l_def_pox_rec.program_update_date,
2495 CREATED_BY = l_def_pox_rec.created_by,
2496 CREATION_DATE = l_def_pox_rec.creation_date,
2497 LAST_UPDATED_BY = l_def_pox_rec.last_updated_by,
2498 LAST_UPDATE_DATE = l_def_pox_rec.last_update_date,
2499 LAST_UPDATE_LOGIN = l_def_pox_rec.last_update_login,
2500 LEGAL_ENTITY_ID = l_def_pox_rec.legal_entity_id,
2501 TRANSACTION_STATUS = l_def_pox_rec.transaction_status
2502 WHERE ID = l_def_pox_rec.id;
2503
2504 x_pox_rec := l_pox_rec;
2505 x_return_status := l_return_status;
2506 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2507 EXCEPTION
2508 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2509 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2510 (
2511 l_api_name,
2512 G_PKG_NAME,
2513 'OKL_API.G_RET_STS_ERROR',
2514 x_msg_count,
2515 x_msg_data,
2516 '_PVT'
2517 );
2518 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2519 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2520 (
2521 l_api_name,
2522 G_PKG_NAME,
2523 'OKL_API.G_RET_STS_UNEXP_ERROR',
2524 x_msg_count,
2525 x_msg_data,
2526 '_PVT'
2527 );
2528 WHEN OTHERS THEN
2529 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2530 (
2531 l_api_name,
2532 G_PKG_NAME,
2533 'OTHERS',
2534 x_msg_count,
2535 x_msg_data,
2536 '_PVT'
2537 );
2538 END update_row;
2539 ------------------------------------------
2540 -- update_row for:OKL_POOL_TRANSACTIONS_V --
2541 ------------------------------------------
2542 PROCEDURE update_row(
2543 p_api_version IN NUMBER,
2544 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2545 x_return_status OUT NOCOPY VARCHAR2,
2546 x_msg_count OUT NOCOPY NUMBER,
2547 x_msg_data OUT NOCOPY VARCHAR2,
2548 p_poxv_rec IN poxv_rec_type,
2549 x_poxv_rec OUT NOCOPY poxv_rec_type) IS
2550
2551 l_api_version CONSTANT NUMBER := 1;
2552 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2553 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2554 l_poxv_rec poxv_rec_type := p_poxv_rec;
2555 l_def_poxv_rec poxv_rec_type;
2556 l_db_poxv_rec poxv_rec_type;
2557 l_pox_rec pox_rec_type;
2558 lx_pox_rec pox_rec_type;
2559 -------------------------------
2560 -- FUNCTION fill_who_columns --
2561 -------------------------------
2562 FUNCTION fill_who_columns (
2563 p_poxv_rec IN poxv_rec_type
2564 ) RETURN poxv_rec_type IS
2565 l_poxv_rec poxv_rec_type := p_poxv_rec;
2566 BEGIN
2567 l_poxv_rec.LAST_UPDATE_DATE := SYSDATE;
2568 l_poxv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2569 l_poxv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2570 RETURN(l_poxv_rec);
2571 END fill_who_columns;
2572 ----------------------------------
2573 -- FUNCTION populate_new_record --
2574 ----------------------------------
2575 FUNCTION populate_new_record (
2576 p_poxv_rec IN poxv_rec_type,
2577 x_poxv_rec OUT NOCOPY poxv_rec_type
2578 ) RETURN VARCHAR2 IS
2579 l_row_notfound BOOLEAN := TRUE;
2580 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2581 BEGIN
2582 x_poxv_rec := p_poxv_rec;
2583 -- Get current database values
2584 -- NOTE: Never assign the OBJECT_VERSION_NUMBER. Force the user to pass it
2585 -- so it may be verified through LOCK_ROW.
2586 l_db_poxv_rec := get_rec(p_poxv_rec, l_return_status);
2587 IF (l_return_status = OKL_API.G_RET_STS_SUCCESS) THEN
2588 IF (x_poxv_rec.id = OKL_API.G_MISS_NUM)
2589 THEN
2590 x_poxv_rec.id := l_db_poxv_rec.id;
2591 END IF;
2592 IF (x_poxv_rec.object_version_number = OKL_API.G_MISS_NUM)
2593 THEN
2594 x_poxv_rec.object_version_number := l_db_poxv_rec.object_version_number;
2595 END IF;
2596 IF (x_poxv_rec.pol_id = OKL_API.G_MISS_NUM)
2597 THEN
2598 x_poxv_rec.pol_id := l_db_poxv_rec.pol_id;
2599 END IF;
2600 IF (x_poxv_rec.transaction_number = OKL_API.G_MISS_NUM)
2601 THEN
2602 x_poxv_rec.transaction_number := l_db_poxv_rec.transaction_number;
2603 END IF;
2604 IF (x_poxv_rec.transaction_date = OKL_API.G_MISS_DATE)
2605 THEN
2606 x_poxv_rec.transaction_date := l_db_poxv_rec.transaction_date;
2607 END IF;
2608 IF (x_poxv_rec.transaction_type = OKL_API.G_MISS_CHAR)
2609 THEN
2610 x_poxv_rec.transaction_type := l_db_poxv_rec.transaction_type;
2611 END IF;
2612 IF (x_poxv_rec.transaction_sub_type = OKL_API.G_MISS_CHAR)
2613 THEN
2614 x_poxv_rec.transaction_sub_type := l_db_poxv_rec.transaction_sub_type;
2615 END IF;
2616 IF (x_poxv_rec.date_effective = OKL_API.G_MISS_DATE)
2617 THEN
2618 x_poxv_rec.date_effective := l_db_poxv_rec.date_effective;
2619 END IF;
2620 IF (x_poxv_rec.currency_code = OKL_API.G_MISS_CHAR)
2621 THEN
2622 x_poxv_rec.currency_code := l_db_poxv_rec.currency_code;
2623 END IF;
2624 IF (x_poxv_rec.currency_conversion_type = OKL_API.G_MISS_CHAR)
2625 THEN
2626 x_poxv_rec.currency_conversion_type := l_db_poxv_rec.currency_conversion_type;
2627 END IF;
2628 IF (x_poxv_rec.currency_conversion_date = OKL_API.G_MISS_DATE)
2629 THEN
2630 x_poxv_rec.currency_conversion_date := l_db_poxv_rec.currency_conversion_date;
2631 END IF;
2632 IF (x_poxv_rec.currency_conversion_rate = OKL_API.G_MISS_NUM)
2633 THEN
2634 x_poxv_rec.currency_conversion_rate := l_db_poxv_rec.currency_conversion_rate;
2635 END IF;
2636 IF (x_poxv_rec.transaction_reason = OKL_API.G_MISS_CHAR)
2637 THEN
2638 x_poxv_rec.transaction_reason := l_db_poxv_rec.transaction_reason;
2639 END IF;
2640 IF (x_poxv_rec.attribute_category = OKL_API.G_MISS_CHAR)
2641 THEN
2642 x_poxv_rec.attribute_category := l_db_poxv_rec.attribute_category;
2643 END IF;
2644 IF (x_poxv_rec.attribute1 = OKL_API.G_MISS_CHAR)
2645 THEN
2646 x_poxv_rec.attribute1 := l_db_poxv_rec.attribute1;
2647 END IF;
2648 IF (x_poxv_rec.attribute2 = OKL_API.G_MISS_CHAR)
2649 THEN
2650 x_poxv_rec.attribute2 := l_db_poxv_rec.attribute2;
2651 END IF;
2652 IF (x_poxv_rec.attribute3 = OKL_API.G_MISS_CHAR)
2653 THEN
2654 x_poxv_rec.attribute3 := l_db_poxv_rec.attribute3;
2655 END IF;
2656 IF (x_poxv_rec.attribute4 = OKL_API.G_MISS_CHAR)
2657 THEN
2658 x_poxv_rec.attribute4 := l_db_poxv_rec.attribute4;
2659 END IF;
2660 IF (x_poxv_rec.attribute5 = OKL_API.G_MISS_CHAR)
2661 THEN
2662 x_poxv_rec.attribute5 := l_db_poxv_rec.attribute5;
2663 END IF;
2664 IF (x_poxv_rec.attribute6 = OKL_API.G_MISS_CHAR)
2665 THEN
2666 x_poxv_rec.attribute6 := l_db_poxv_rec.attribute6;
2667 END IF;
2668 IF (x_poxv_rec.attribute7 = OKL_API.G_MISS_CHAR)
2669 THEN
2670 x_poxv_rec.attribute7 := l_db_poxv_rec.attribute7;
2671 END IF;
2672 IF (x_poxv_rec.attribute8 = OKL_API.G_MISS_CHAR)
2673 THEN
2674 x_poxv_rec.attribute8 := l_db_poxv_rec.attribute8;
2675 END IF;
2676 IF (x_poxv_rec.attribute9 = OKL_API.G_MISS_CHAR)
2677 THEN
2678 x_poxv_rec.attribute9 := l_db_poxv_rec.attribute9;
2679 END IF;
2680 IF (x_poxv_rec.attribute10 = OKL_API.G_MISS_CHAR)
2681 THEN
2682 x_poxv_rec.attribute10 := l_db_poxv_rec.attribute10;
2683 END IF;
2684 IF (x_poxv_rec.attribute11 = OKL_API.G_MISS_CHAR)
2685 THEN
2686 x_poxv_rec.attribute11 := l_db_poxv_rec.attribute11;
2687 END IF;
2688 IF (x_poxv_rec.attribute12 = OKL_API.G_MISS_CHAR)
2689 THEN
2690 x_poxv_rec.attribute12 := l_db_poxv_rec.attribute12;
2691 END IF;
2692 IF (x_poxv_rec.attribute13 = OKL_API.G_MISS_CHAR)
2693 THEN
2694 x_poxv_rec.attribute13 := l_db_poxv_rec.attribute13;
2695 END IF;
2696 IF (x_poxv_rec.attribute14 = OKL_API.G_MISS_CHAR)
2697 THEN
2698 x_poxv_rec.attribute14 := l_db_poxv_rec.attribute14;
2699 END IF;
2700 IF (x_poxv_rec.attribute15 = OKL_API.G_MISS_CHAR)
2701 THEN
2702 x_poxv_rec.attribute15 := l_db_poxv_rec.attribute15;
2703 END IF;
2704 IF (x_poxv_rec.request_id = OKL_API.G_MISS_NUM)
2705 THEN
2706 x_poxv_rec.request_id := l_db_poxv_rec.request_id;
2707 END IF;
2708 IF (x_poxv_rec.program_application_id = OKL_API.G_MISS_NUM)
2709 THEN
2710 x_poxv_rec.program_application_id := l_db_poxv_rec.program_application_id;
2711 END IF;
2712 IF (x_poxv_rec.program_id = OKL_API.G_MISS_NUM)
2713 THEN
2714 x_poxv_rec.program_id := l_db_poxv_rec.program_id;
2715 END IF;
2716 IF (x_poxv_rec.program_update_date = OKL_API.G_MISS_DATE)
2717 THEN
2718 x_poxv_rec.program_update_date := l_db_poxv_rec.program_update_date;
2719 END IF;
2720 IF (x_poxv_rec.created_by = OKL_API.G_MISS_NUM)
2721 THEN
2722 x_poxv_rec.created_by := l_db_poxv_rec.created_by;
2723 END IF;
2724 IF (x_poxv_rec.creation_date = OKL_API.G_MISS_DATE)
2725 THEN
2726 x_poxv_rec.creation_date := l_db_poxv_rec.creation_date;
2727 END IF;
2728 IF (x_poxv_rec.last_updated_by = OKL_API.G_MISS_NUM)
2729 THEN
2730 x_poxv_rec.last_updated_by := l_db_poxv_rec.last_updated_by;
2731 END IF;
2732 IF (x_poxv_rec.last_update_date = OKL_API.G_MISS_DATE)
2733 THEN
2734 x_poxv_rec.last_update_date := l_db_poxv_rec.last_update_date;
2735 END IF;
2736 IF (x_poxv_rec.last_update_login = OKL_API.G_MISS_NUM)
2737 THEN
2738 x_poxv_rec.last_update_login := l_db_poxv_rec.last_update_login;
2739 END IF;
2740 IF (x_poxv_rec.legal_entity_id = OKL_API.G_MISS_NUM)
2741 THEN
2742 x_poxv_rec.legal_entity_id := l_db_poxv_rec.legal_entity_id;
2743 END IF;
2744 IF (x_poxv_rec.transaction_status = OKL_API.G_MISS_CHAR)
2745 THEN
2746 x_poxv_rec.transaction_status := l_db_poxv_rec.transaction_status;
2747 END IF;
2748 END IF;
2749 RETURN(l_return_status);
2750 END populate_new_record;
2751 ----------------------------------------------
2752 -- Set_Attributes for:OKL_POOL_TRANSACTIONS_V --
2753 ----------------------------------------------
2754 FUNCTION Set_Attributes (
2755 p_poxv_rec IN poxv_rec_type,
2756 x_poxv_rec OUT NOCOPY poxv_rec_type
2757 ) RETURN VARCHAR2 IS
2758 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2759 BEGIN
2760 x_poxv_rec := p_poxv_rec;
2761 RETURN(l_return_status);
2762 END Set_Attributes;
2763 BEGIN
2764 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
2765 G_PKG_NAME,
2766 p_init_msg_list,
2767 l_api_version,
2768 p_api_version,
2769 '_PVT',
2770 x_return_status);
2771 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2772 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2773 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2774 RAISE OKL_API.G_EXCEPTION_ERROR;
2775 END IF;
2776 --- Setting item attributes
2777 l_return_status := Set_Attributes(
2778 p_poxv_rec, -- IN
2779 x_poxv_rec); -- OUT
2780 --- If any errors happen abort API
2781 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2782 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2783 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2784 RAISE OKL_API.G_EXCEPTION_ERROR;
2785 END IF;
2786 l_return_status := populate_new_record(l_poxv_rec, l_def_poxv_rec);
2787 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2788 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2789 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2790 RAISE OKL_API.G_EXCEPTION_ERROR;
2791 END IF;
2792 l_def_poxv_rec := fill_who_columns(l_def_poxv_rec);
2793 --- Validate all non-missing attributes (Item Level Validation)
2794 l_return_status := Validate_Attributes(l_def_poxv_rec);
2795 --- If any errors happen abort API
2796 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2797 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2798 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2799 RAISE OKL_API.G_EXCEPTION_ERROR;
2800 END IF;
2801 l_return_status := Validate_Record(l_def_poxv_rec, l_db_poxv_rec);
2802 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2803 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2804 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2805 RAISE OKL_API.G_EXCEPTION_ERROR;
2806 END IF;
2807
2808 /*
2809 -- mvasudev commented
2810 -- Lock the Record
2811 lock_row(
2812 p_api_version => p_api_version,
2813 p_init_msg_list => p_init_msg_list,
2814 x_return_status => l_return_status,
2815 x_msg_count => x_msg_count,
2816 x_msg_data => x_msg_data,
2817 p_poxv_rec => p_poxv_rec);
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 */
2824
2825 -----------------------------------------
2826 -- Move VIEW record to "Child" records --
2827 -----------------------------------------
2828 migrate(l_def_poxv_rec, l_pox_rec);
2829 -----------------------------------------------
2830 -- Call the UPDATE_ROW for each child record --
2831 -----------------------------------------------
2832 update_row(
2833 p_init_msg_list,
2834 l_return_status,
2835 x_msg_count,
2836 x_msg_data,
2837 l_pox_rec,
2838 lx_pox_rec
2839 );
2840 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2841 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2842 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
2843 RAISE OKL_API.G_EXCEPTION_ERROR;
2844 END IF;
2845 migrate(lx_pox_rec, l_def_poxv_rec);
2846 x_poxv_rec := l_def_poxv_rec;
2847 x_return_status := l_return_status;
2848 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2849 EXCEPTION
2850 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2851 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2852 (
2853 l_api_name,
2854 G_PKG_NAME,
2855 'OKL_API.G_RET_STS_ERROR',
2856 x_msg_count,
2857 x_msg_data,
2858 '_PVT'
2859 );
2860 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2861 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2862 (
2863 l_api_name,
2864 G_PKG_NAME,
2865 'OKL_API.G_RET_STS_UNEXP_ERROR',
2866 x_msg_count,
2867 x_msg_data,
2868 '_PVT'
2869 );
2870 WHEN OTHERS THEN
2871 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2872 (
2873 l_api_name,
2874 G_PKG_NAME,
2875 'OTHERS',
2876 x_msg_count,
2877 x_msg_data,
2878 '_PVT'
2879 );
2880 END update_row;
2881 ----------------------------------------
2882 -- PL/SQL TBL update_row for:poxv_tbl --
2883 ----------------------------------------
2884 PROCEDURE update_row(
2885 p_api_version IN NUMBER,
2886 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2887 x_return_status OUT NOCOPY VARCHAR2,
2888 x_msg_count OUT NOCOPY NUMBER,
2889 x_msg_data OUT NOCOPY VARCHAR2,
2890 p_poxv_tbl IN poxv_tbl_type,
2891 x_poxv_tbl OUT NOCOPY poxv_tbl_type,
2892 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
2893
2894 l_api_version CONSTANT NUMBER := 1;
2895 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_update_row';
2896 i NUMBER := 0;
2897 BEGIN
2898 OKL_API.init_msg_list(p_init_msg_list);
2899 -- Make sure PL/SQL table has records in it before passing
2900 IF (p_poxv_tbl.COUNT > 0) THEN
2901 i := p_poxv_tbl.FIRST;
2902 LOOP
2903 DECLARE
2904 l_error_rec OKL_API.ERROR_REC_TYPE;
2905 BEGIN
2906 l_error_rec.api_name := l_api_name;
2907 l_error_rec.api_package := G_PKG_NAME;
2908 l_error_rec.idx := i;
2909 update_row (
2910 p_api_version => p_api_version,
2911 p_init_msg_list => OKL_API.G_FALSE,
2912 x_return_status => l_error_rec.error_type,
2913 x_msg_count => l_error_rec.msg_count,
2914 x_msg_data => l_error_rec.msg_data,
2915 p_poxv_rec => p_poxv_tbl(i),
2916 x_poxv_rec => x_poxv_tbl(i));
2917 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
2918 l_error_rec.SQLCODE := SQLCODE;
2919 load_error_tbl(l_error_rec, px_error_tbl);
2920 ELSE
2921 x_msg_count := l_error_rec.msg_count;
2922 x_msg_data := l_error_rec.msg_data;
2923 END IF;
2924 EXCEPTION
2925 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2926 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
2927 l_error_rec.SQLCODE := SQLCODE;
2928 load_error_tbl(l_error_rec, px_error_tbl);
2929 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2930 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
2931 l_error_rec.SQLCODE := SQLCODE;
2932 load_error_tbl(l_error_rec, px_error_tbl);
2933 WHEN OTHERS THEN
2934 l_error_rec.error_type := 'OTHERS';
2935 l_error_rec.SQLCODE := SQLCODE;
2936 load_error_tbl(l_error_rec, px_error_tbl);
2937 END;
2938 EXIT WHEN (i = p_poxv_tbl.LAST);
2939 i := p_poxv_tbl.NEXT(i);
2940 END LOOP;
2941 END IF;
2942 -- Loop through the error_tbl to find the error with the highest severity
2943 -- and return it.
2944 x_return_status := find_highest_exception(px_error_tbl);
2945 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
2946 EXCEPTION
2947 WHEN OKL_API.G_EXCEPTION_ERROR THEN
2948 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2949 (
2950 l_api_name,
2951 G_PKG_NAME,
2952 'OKL_API.G_RET_STS_ERROR',
2953 x_msg_count,
2954 x_msg_data,
2955 '_PVT'
2956 );
2957 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2958 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2959 (
2960 l_api_name,
2961 G_PKG_NAME,
2962 'OKL_API.G_RET_STS_UNEXP_ERROR',
2963 x_msg_count,
2964 x_msg_data,
2965 '_PVT'
2966 );
2967 WHEN OTHERS THEN
2968 x_return_status := OKL_API.HANDLE_EXCEPTIONS
2969 (
2970 l_api_name,
2971 G_PKG_NAME,
2972 'OTHERS',
2973 x_msg_count,
2974 x_msg_data,
2975 '_PVT'
2976 );
2977 END update_row;
2978
2979 ----------------------------------------
2980 -- PL/SQL TBL update_row for:poxv_TBL --
2981 ----------------------------------------
2982 PROCEDURE update_row(
2983 p_api_version IN NUMBER,
2984 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
2985 x_return_status OUT NOCOPY VARCHAR2,
2986 x_msg_count OUT NOCOPY NUMBER,
2987 x_msg_data OUT NOCOPY VARCHAR2,
2988 p_poxv_tbl IN poxv_tbl_type,
2989 x_poxv_tbl OUT NOCOPY poxv_tbl_type) IS
2990
2991 l_api_version CONSTANT NUMBER := 1;
2992 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2993 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
2994 l_error_tbl OKL_API.ERROR_TBL_TYPE;
2995 BEGIN
2996 OKL_API.init_msg_list(p_init_msg_list);
2997 -- Make sure PL/SQL table has records in it before passing
2998 IF (p_poxv_tbl.COUNT > 0) THEN
2999 update_row (
3000 p_api_version => p_api_version,
3001 p_init_msg_list => OKL_API.G_FALSE,
3002 x_return_status => x_return_status,
3003 x_msg_count => x_msg_count,
3004 x_msg_data => x_msg_data,
3005 p_poxv_tbl => p_poxv_tbl,
3006 x_poxv_tbl => x_poxv_tbl,
3007 px_error_tbl => l_error_tbl);
3008 END IF;
3009 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3010 EXCEPTION
3011 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3012 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3013 (
3014 l_api_name,
3015 G_PKG_NAME,
3016 'OKL_API.G_RET_STS_ERROR',
3017 x_msg_count,
3018 x_msg_data,
3019 '_PVT'
3020 );
3021 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3022 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3023 (
3024 l_api_name,
3025 G_PKG_NAME,
3026 'OKL_API.G_RET_STS_UNEXP_ERROR',
3027 x_msg_count,
3028 x_msg_data,
3029 '_PVT'
3030 );
3031 WHEN OTHERS THEN
3032 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3033 (
3034 l_api_name,
3035 G_PKG_NAME,
3036 'OTHERS',
3037 x_msg_count,
3038 x_msg_data,
3039 '_PVT'
3040 );
3041 END update_row;
3042
3043 ---------------------------------------------------------------------------
3044 -- PROCEDURE delete_row
3045 ---------------------------------------------------------------------------
3046 ----------------------------------------
3047 -- delete_row for:okl_pool_transactions --
3048 ----------------------------------------
3049 PROCEDURE delete_row(
3050 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
3051 x_return_status OUT NOCOPY VARCHAR2,
3052 x_msg_count OUT NOCOPY NUMBER,
3053 x_msg_data OUT NOCOPY VARCHAR2,
3054 p_pox_rec IN pox_rec_type) IS
3055
3056 l_api_version CONSTANT NUMBER := 1;
3057 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
3058 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3059 l_pox_rec pox_rec_type := p_pox_rec;
3060 l_row_notfound BOOLEAN := TRUE;
3061 BEGIN
3062 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3063 p_init_msg_list,
3064 '_PVT',
3065 x_return_status);
3066 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3067 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3068 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3069 RAISE OKL_API.G_EXCEPTION_ERROR;
3070 END IF;
3071
3072 DELETE FROM okl_pool_transactions
3073 WHERE ID = p_pox_rec.id;
3074
3075 x_return_status := l_return_status;
3076 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3077 EXCEPTION
3078 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3079 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3080 (
3081 l_api_name,
3082 G_PKG_NAME,
3083 'OKL_API.G_RET_STS_ERROR',
3084 x_msg_count,
3085 x_msg_data,
3086 '_PVT'
3087 );
3088 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3089 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3090 (
3091 l_api_name,
3092 G_PKG_NAME,
3093 'OKL_API.G_RET_STS_UNEXP_ERROR',
3094 x_msg_count,
3095 x_msg_data,
3096 '_PVT'
3097 );
3098 WHEN OTHERS THEN
3099 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3100 (
3101 l_api_name,
3102 G_PKG_NAME,
3103 'OTHERS',
3104 x_msg_count,
3105 x_msg_data,
3106 '_PVT'
3107 );
3108 END delete_row;
3109 ------------------------------------------
3110 -- delete_row for:OKL_POOL_TRANSACTIONS_V --
3111 ------------------------------------------
3112 PROCEDURE delete_row(
3113 p_api_version IN NUMBER,
3114 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
3115 x_return_status OUT NOCOPY VARCHAR2,
3116 x_msg_count OUT NOCOPY NUMBER,
3117 x_msg_data OUT NOCOPY VARCHAR2,
3118 p_poxv_rec IN poxv_rec_type) IS
3119
3120 l_api_version CONSTANT NUMBER := 1;
3121 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3122 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3123 l_poxv_rec poxv_rec_type := p_poxv_rec;
3124 l_pox_rec pox_rec_type;
3125 BEGIN
3126 l_return_status := OKL_API.START_ACTIVITY(l_api_name,
3127 G_PKG_NAME,
3128 p_init_msg_list,
3129 l_api_version,
3130 p_api_version,
3131 '_PVT',
3132 x_return_status);
3133 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3134 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3135 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3136 RAISE OKL_API.G_EXCEPTION_ERROR;
3137 END IF;
3138 -----------------------------------------
3139 -- Move VIEW record to "Child" records --
3140 -----------------------------------------
3141 migrate(l_poxv_rec, l_pox_rec);
3142 -----------------------------------------------
3143 -- Call the DELETE_ROW for each child record --
3144 -----------------------------------------------
3145 delete_row(
3146 p_init_msg_list,
3147 l_return_status,
3148 x_msg_count,
3149 x_msg_data,
3150 l_pox_rec
3151 );
3152 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3153 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3154 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR) THEN
3155 RAISE OKL_API.G_EXCEPTION_ERROR;
3156 END IF;
3157 x_return_status := l_return_status;
3158 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3159 EXCEPTION
3160 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3161 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3162 (
3163 l_api_name,
3164 G_PKG_NAME,
3165 'OKL_API.G_RET_STS_ERROR',
3166 x_msg_count,
3167 x_msg_data,
3168 '_PVT'
3169 );
3170 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3171 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3172 (
3173 l_api_name,
3174 G_PKG_NAME,
3175 'OKL_API.G_RET_STS_UNEXP_ERROR',
3176 x_msg_count,
3177 x_msg_data,
3178 '_PVT'
3179 );
3180 WHEN OTHERS THEN
3181 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3182 (
3183 l_api_name,
3184 G_PKG_NAME,
3185 'OTHERS',
3186 x_msg_count,
3187 x_msg_data,
3188 '_PVT'
3189 );
3190 END delete_row;
3191 -----------------------------------------------------
3192 -- PL/SQL TBL delete_row for:OKL_POOL_TRANSACTIONS_V --
3193 -----------------------------------------------------
3194 PROCEDURE delete_row(
3195 p_api_version IN NUMBER,
3196 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
3197 x_return_status OUT NOCOPY VARCHAR2,
3198 x_msg_count OUT NOCOPY NUMBER,
3199 x_msg_data OUT NOCOPY VARCHAR2,
3200 p_poxv_tbl IN poxv_tbl_type,
3201 px_error_tbl IN OUT NOCOPY OKL_API.ERROR_TBL_TYPE) IS
3202
3203 l_api_version CONSTANT NUMBER := 1;
3204 l_api_name CONSTANT VARCHAR2(30) := 'V_error_tbl_delete_row';
3205 i NUMBER := 0;
3206 BEGIN
3207 OKL_API.init_msg_list(p_init_msg_list);
3208 -- Make sure PL/SQL table has records in it before passing
3209 IF (p_poxv_tbl.COUNT > 0) THEN
3210 i := p_poxv_tbl.FIRST;
3211 LOOP
3212 DECLARE
3213 l_error_rec OKL_API.ERROR_REC_TYPE;
3214 BEGIN
3215 l_error_rec.api_name := l_api_name;
3216 l_error_rec.api_package := G_PKG_NAME;
3217 l_error_rec.idx := i;
3218 delete_row (
3219 p_api_version => p_api_version,
3220 p_init_msg_list => OKL_API.G_FALSE,
3221 x_return_status => l_error_rec.error_type,
3222 x_msg_count => l_error_rec.msg_count,
3223 x_msg_data => l_error_rec.msg_data,
3224 p_poxv_rec => p_poxv_tbl(i));
3225 IF (l_error_rec.error_type <> OKL_API.G_RET_STS_SUCCESS) THEN
3226 l_error_rec.SQLCODE := SQLCODE;
3227 load_error_tbl(l_error_rec, px_error_tbl);
3228 ELSE
3229 x_msg_count := l_error_rec.msg_count;
3230 x_msg_data := l_error_rec.msg_data;
3231 END IF;
3232 EXCEPTION
3233 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3234 l_error_rec.error_type := OKL_API.G_RET_STS_ERROR;
3235 l_error_rec.SQLCODE := SQLCODE;
3236 load_error_tbl(l_error_rec, px_error_tbl);
3237 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3238 l_error_rec.error_type := OKL_API.G_RET_STS_UNEXP_ERROR;
3239 l_error_rec.SQLCODE := SQLCODE;
3240 load_error_tbl(l_error_rec, px_error_tbl);
3241 WHEN OTHERS THEN
3242 l_error_rec.error_type := 'OTHERS';
3243 l_error_rec.SQLCODE := SQLCODE;
3244 load_error_tbl(l_error_rec, px_error_tbl);
3245 END;
3246 EXIT WHEN (i = p_poxv_tbl.LAST);
3247 i := p_poxv_tbl.NEXT(i);
3248 END LOOP;
3249 END IF;
3250 -- Loop through the error_tbl to find the error with the highest severity
3251 -- and return it.
3252 x_return_status := find_highest_exception(px_error_tbl);
3253 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3254 EXCEPTION
3255 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3256 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3257 (
3258 l_api_name,
3259 G_PKG_NAME,
3260 'OKL_API.G_RET_STS_ERROR',
3261 x_msg_count,
3262 x_msg_data,
3263 '_PVT'
3264 );
3265 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3266 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3267 (
3268 l_api_name,
3269 G_PKG_NAME,
3270 'OKL_API.G_RET_STS_UNEXP_ERROR',
3271 x_msg_count,
3272 x_msg_data,
3273 '_PVT'
3274 );
3275 WHEN OTHERS THEN
3276 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3277 (
3278 l_api_name,
3279 G_PKG_NAME,
3280 'OTHERS',
3281 x_msg_count,
3282 x_msg_data,
3283 '_PVT'
3284 );
3285 END delete_row;
3286
3287 -----------------------------------------------------
3288 -- PL/SQL TBL delete_row for:OKL_POOL_TRANSACTIONS_V --
3289 -----------------------------------------------------
3290 PROCEDURE delete_row(
3291 p_api_version IN NUMBER,
3292 p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
3293 x_return_status OUT NOCOPY VARCHAR2,
3294 x_msg_count OUT NOCOPY NUMBER,
3295 x_msg_data OUT NOCOPY VARCHAR2,
3296 p_poxv_tbl IN poxv_tbl_type) IS
3297
3298 l_api_version CONSTANT NUMBER := 1;
3299 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3300 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3301 l_error_tbl OKL_API.ERROR_TBL_TYPE;
3302 BEGIN
3303 OKL_API.init_msg_list(p_init_msg_list);
3304 -- Make sure PL/SQL table has records in it before passing
3305 IF (p_poxv_tbl.COUNT > 0) THEN
3306 delete_row (
3307 p_api_version => p_api_version,
3308 p_init_msg_list => OKL_API.G_FALSE,
3309 x_return_status => x_return_status,
3310 x_msg_count => x_msg_count,
3311 x_msg_data => x_msg_data,
3312 p_poxv_tbl => p_poxv_tbl,
3313 px_error_tbl => l_error_tbl);
3314 END IF;
3315 OKL_API.END_ACTIVITY(x_msg_count, x_msg_data);
3316 EXCEPTION
3317 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3318 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3319 (
3320 l_api_name,
3321 G_PKG_NAME,
3322 'OKL_API.G_RET_STS_ERROR',
3323 x_msg_count,
3324 x_msg_data,
3325 '_PVT'
3326 );
3327 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3328 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3329 (
3330 l_api_name,
3331 G_PKG_NAME,
3332 'OKL_API.G_RET_STS_UNEXP_ERROR',
3333 x_msg_count,
3334 x_msg_data,
3335 '_PVT'
3336 );
3337 WHEN OTHERS THEN
3338 x_return_status := OKL_API.HANDLE_EXCEPTIONS
3339 (
3340 l_api_name,
3341 G_PKG_NAME,
3342 'OTHERS',
3343 x_msg_count,
3344 x_msg_data,
3345 '_PVT'
3346 );
3347 END delete_row;
3348
3349 END Okl_Pox_Pvt;