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