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