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