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