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