[Home] [Help]
PACKAGE BODY: APPS.OKC_TAV_PVT
Source
1 PACKAGE BODY OKC_TAV_PVT AS
2 /* $Header: OKCSTAVB.pls 120.0 2005/05/25 19:10:58 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5 ---------------------------------------------------------------------------
6 -- FUNCTION get_seq_id
7 ---------------------------------------------------------------------------
8 FUNCTION get_seq_id RETURN NUMBER IS
9 BEGIN
10 RETURN(okc_p_util.raw_to_number(sys_guid()));
11 END get_seq_id;
12
13 ---------------------------------------------------------------------------
14 -- PROCEDURE qc
15 ---------------------------------------------------------------------------
16 PROCEDURE qc IS
17 BEGIN
18 null;
19 END qc;
20
21 ---------------------------------------------------------------------------
22 -- PROCEDURE change_version
23 ---------------------------------------------------------------------------
24 PROCEDURE change_version IS
25 BEGIN
26 null;
27 END change_version;
28
29 ---------------------------------------------------------------------------
30 -- PROCEDURE api_copy
31 ---------------------------------------------------------------------------
32 PROCEDURE api_copy IS
33 BEGIN
34 null;
35 END api_copy;
36
37 ---------------------------------------------------------------------------
38 -- FUNCTION get_rec for: OKC_TIMEVALUES_B
39 ---------------------------------------------------------------------------
40 FUNCTION get_rec (
41 p_tve_rec IN tve_rec_type,
42 x_no_data_found OUT NOCOPY BOOLEAN
43 ) RETURN tve_rec_type IS
44 CURSOR tve_pk_csr (p_id IN NUMBER) IS
45 SELECT
46 ID,
47 SPN_ID,
48 TVE_ID_OFFSET,
49 uom_code,
50 TVE_ID_GENERATED_BY,
51 TVE_ID_LIMITED,
52 TVE_ID_STARTED,
53 TVE_ID_ENDED,
54 CNH_ID,
55 TVE_TYPE,
56 OBJECT_VERSION_NUMBER,
57 CREATED_BY,
58 CREATION_DATE,
59 LAST_UPDATED_BY,
60 LAST_UPDATE_DATE,
61 DURATION,
62 OPERATOR,
63 BEFORE_AFTER,
64 DATETIME,
65 DNZ_CHR_ID,
66 TZE_ID,
67 MONTH,
68 DAY,
69 HOUR,
70 MINUTE,
71 SECOND,
72 INTERVAL_YN,
73 NTH,
74 DAY_OF_WEEK,
75 LAST_UPDATE_LOGIN,
76 ATTRIBUTE_CATEGORY,
77 --Bug 3122962
78 DESCRIPTION,
79 SHORT_DESCRIPTION,
80 COMMENTS,
81 NAME,
82
83 ATTRIBUTE1,
84 ATTRIBUTE2,
85 ATTRIBUTE3,
86 ATTRIBUTE4,
87 ATTRIBUTE5,
88 ATTRIBUTE6,
89 ATTRIBUTE7,
90 ATTRIBUTE8,
91 ATTRIBUTE9,
92 ATTRIBUTE10,
93 ATTRIBUTE11,
94 ATTRIBUTE12,
95 ATTRIBUTE13,
96 ATTRIBUTE14,
97 ATTRIBUTE15
98 FROM Okc_Timevalues
99 WHERE okc_timevalues.id = p_id;
100 l_tve_pk tve_pk_csr%ROWTYPE;
101 l_tve_rec tve_rec_type;
102 BEGIN
103 x_no_data_found := TRUE;
104 -- Get current database values
105 OPEN tve_pk_csr (p_tve_rec.id);
106 FETCH tve_pk_csr INTO
107 l_tve_rec.ID,
108 l_tve_rec.SPN_ID,
109 l_tve_rec.TVE_ID_OFFSET,
110 l_tve_rec.uom_code,
111 l_tve_rec.TVE_ID_GENERATED_BY,
112 l_tve_rec.TVE_ID_LIMITED,
113 l_tve_rec.TVE_ID_STARTED,
114 l_tve_rec.TVE_ID_ENDED,
115 l_tve_rec.CNH_ID,
116 l_tve_rec.TVE_TYPE,
117 l_tve_rec.OBJECT_VERSION_NUMBER,
118 l_tve_rec.CREATED_BY,
119 l_tve_rec.CREATION_DATE,
120 l_tve_rec.LAST_UPDATED_BY,
121 l_tve_rec.LAST_UPDATE_DATE,
122 l_tve_rec.DURATION,
123 l_tve_rec.OPERATOR,
124 l_tve_rec.BEFORE_AFTER,
125 l_tve_rec.DATETIME,
126 l_tve_rec.DNZ_CHR_ID,
127 l_tve_rec.TZE_ID,
128 l_tve_rec.MONTH,
129 l_tve_rec.DAY,
130 l_tve_rec.HOUR,
131 l_tve_rec.MINUTE,
132 l_tve_rec.SECOND,
133 l_tve_rec.INTERVAL_YN,
134 l_tve_rec.NTH,
135 l_tve_rec.DAY_OF_WEEK,
136 l_tve_rec.LAST_UPDATE_LOGIN,
137 l_tve_rec.ATTRIBUTE_CATEGORY,
138 --Bug 3122962
139 l_tve_rec.DESCRIPTION,
140 l_tve_rec.SHORT_DESCRIPTION,
141 l_tve_rec.COMMENTS,
142 l_tve_rec.NAME,
143
144 l_tve_rec.ATTRIBUTE1,
145 l_tve_rec.ATTRIBUTE2,
146 l_tve_rec.ATTRIBUTE3,
147 l_tve_rec.ATTRIBUTE4,
148 l_tve_rec.ATTRIBUTE5,
149 l_tve_rec.ATTRIBUTE6,
150 l_tve_rec.ATTRIBUTE7,
151 l_tve_rec.ATTRIBUTE8,
152 l_tve_rec.ATTRIBUTE9,
153 l_tve_rec.ATTRIBUTE10,
154 l_tve_rec.ATTRIBUTE11,
155 l_tve_rec.ATTRIBUTE12,
156 l_tve_rec.ATTRIBUTE13,
157 l_tve_rec.ATTRIBUTE14,
158 l_tve_rec.ATTRIBUTE15;
159 x_no_data_found := tve_pk_csr%NOTFOUND;
160 CLOSE tve_pk_csr;
161 RETURN(l_tve_rec);
162 END get_rec;
163
164 FUNCTION get_rec (
165 p_tve_rec IN tve_rec_type
166 ) RETURN tve_rec_type IS
167 l_row_notfound BOOLEAN := TRUE;
168 BEGIN
169 RETURN(get_rec(p_tve_rec, l_row_notfound));
170 END get_rec;
171 ---------------------------------------------------------------------------
172 -- FUNCTION get_rec for: OKC_TIMEVALUES_TL
173 ---------------------------------------------------------------------------
174 /* FUNCTION get_rec (
175 p_okc_timevalues_tl_rec IN okc_timevalues_tl_rec_type,
176 x_no_data_found OUT NOCOPY BOOLEAN
177 ) RETURN okc_timevalues_tl_rec_type IS
178 CURSOR tve_pktl_csr (p_id IN NUMBER,
179 p_language IN VARCHAR2) IS
180 SELECT
181 ID,
182 LANGUAGE,
183 SOURCE_LANG,
184 SFWT_FLAG,
185 DESCRIPTION,
186 SHORT_DESCRIPTION,
187 COMMENTS,
188 NAME,
189 CREATED_BY,
190 CREATION_DATE,
191 LAST_UPDATED_BY,
192 LAST_UPDATE_DATE,
193 LAST_UPDATE_LOGIN
194 FROM Okc_Timevalues_Tl
195 WHERE okc_timevalues_tl.id = p_id
196 AND okc_timevalues_tl.language = p_language;
197 l_tve_pktl tve_pktl_csr%ROWTYPE;
198 l_okc_timevalues_tl_rec okc_timevalues_tl_rec_type;
199 BEGIN
200 x_no_data_found := TRUE;
201 -- Get current database values
202 OPEN tve_pktl_csr (p_okc_timevalues_tl_rec.id,
203 p_okc_timevalues_tl_rec.language);
204 FETCH tve_pktl_csr INTO
205 l_okc_timevalues_tl_rec.ID,
206 l_okc_timevalues_tl_rec.LANGUAGE,
207 l_okc_timevalues_tl_rec.SOURCE_LANG,
208 l_okc_timevalues_tl_rec.SFWT_FLAG,
209 l_okc_timevalues_tl_rec.DESCRIPTION,
210 l_okc_timevalues_tl_rec.SHORT_DESCRIPTION,
211 l_okc_timevalues_tl_rec.COMMENTS,
212 l_okc_timevalues_tl_rec.NAME,
213 l_okc_timevalues_tl_rec.CREATED_BY,
214 l_okc_timevalues_tl_rec.CREATION_DATE,
215 l_okc_timevalues_tl_rec.LAST_UPDATED_BY,
216 l_okc_timevalues_tl_rec.LAST_UPDATE_DATE,
217 l_okc_timevalues_tl_rec.LAST_UPDATE_LOGIN;
218 x_no_data_found := tve_pktl_csr%NOTFOUND;
219 CLOSE tve_pktl_csr;
220 RETURN(l_okc_timevalues_tl_rec);
221 END get_rec;
222
223 FUNCTION get_rec (
224 p_okc_timevalues_tl_rec IN okc_timevalues_tl_rec_type
225 ) RETURN okc_timevalues_tl_rec_type IS
226 l_row_notfound BOOLEAN := TRUE;
227 BEGIN
228 RETURN(get_rec(p_okc_timevalues_tl_rec, l_row_notfound));
229 END get_rec;
230 ---------------------------------------------------------------------------
231 -- FUNCTION get_rec for: OKC_TIME_TPA_VALUE_V
232 ---------------------------------------------------------------------------
233 */
234 FUNCTION get_rec (
235 p_tavv_rec IN tavv_rec_type,
236 x_no_data_found OUT NOCOPY BOOLEAN
237 ) RETURN tavv_rec_type IS
238 CURSOR okc_tavv_pk_csr (p_id IN NUMBER) IS
239 SELECT
240 ID,
241 OBJECT_VERSION_NUMBER,
242 --Bug 3122962 SFWT_FLAG,
243 SPN_ID,
244 TVE_ID_GENERATED_BY,
245 TVE_ID_LIMITED,
246 DESCRIPTION,
247 SHORT_DESCRIPTION,
248 COMMENTS,
249 DATETIME,
250 DNZ_CHR_ID,
251 TZE_ID,
252 ATTRIBUTE_CATEGORY,
253 ATTRIBUTE1,
254 ATTRIBUTE2,
255 ATTRIBUTE3,
256 ATTRIBUTE4,
257 ATTRIBUTE5,
258 ATTRIBUTE6,
259 ATTRIBUTE7,
260 ATTRIBUTE8,
261 ATTRIBUTE9,
262 ATTRIBUTE10,
263 ATTRIBUTE11,
264 ATTRIBUTE12,
265 ATTRIBUTE13,
266 ATTRIBUTE14,
267 ATTRIBUTE15,
268 CREATED_BY,
269 CREATION_DATE,
270 LAST_UPDATED_BY,
271 LAST_UPDATE_DATE,
272 LAST_UPDATE_LOGIN
273 FROM Okc_Time_Tpa_Value_V
274 WHERE okc_time_tpa_value_v.id = p_id;
275 l_okc_tavv_pk okc_tavv_pk_csr%ROWTYPE;
276 l_tavv_rec tavv_rec_type;
277 BEGIN
278 x_no_data_found := TRUE;
279 -- Get current database values
280 OPEN okc_tavv_pk_csr (p_tavv_rec.id);
281 FETCH okc_tavv_pk_csr INTO
282 l_tavv_rec.ID,
283 l_tavv_rec.OBJECT_VERSION_NUMBER,
284 --Bug 3122962 l_tavv_rec.SFWT_FLAG,
285 l_tavv_rec.SPN_ID,
286 l_tavv_rec.TVE_ID_GENERATED_BY,
287 l_tavv_rec.TVE_ID_LIMITED,
288 l_tavv_rec.DESCRIPTION,
289 l_tavv_rec.SHORT_DESCRIPTION,
290 l_tavv_rec.COMMENTS,
291 l_tavv_rec.DATETIME,
292 l_tavv_rec.DNZ_CHR_ID,
293 l_tavv_rec.TZE_ID,
294 l_tavv_rec.ATTRIBUTE_CATEGORY,
295 l_tavv_rec.ATTRIBUTE1,
296 l_tavv_rec.ATTRIBUTE2,
297 l_tavv_rec.ATTRIBUTE3,
298 l_tavv_rec.ATTRIBUTE4,
299 l_tavv_rec.ATTRIBUTE5,
300 l_tavv_rec.ATTRIBUTE6,
301 l_tavv_rec.ATTRIBUTE7,
302 l_tavv_rec.ATTRIBUTE8,
303 l_tavv_rec.ATTRIBUTE9,
304 l_tavv_rec.ATTRIBUTE10,
305 l_tavv_rec.ATTRIBUTE11,
306 l_tavv_rec.ATTRIBUTE12,
307 l_tavv_rec.ATTRIBUTE13,
308 l_tavv_rec.ATTRIBUTE14,
309 l_tavv_rec.ATTRIBUTE15,
310 l_tavv_rec.CREATED_BY,
311 l_tavv_rec.CREATION_DATE,
312 l_tavv_rec.LAST_UPDATED_BY,
313 l_tavv_rec.LAST_UPDATE_DATE,
314 l_tavv_rec.LAST_UPDATE_LOGIN;
315 x_no_data_found := okc_tavv_pk_csr%NOTFOUND;
316 CLOSE okc_tavv_pk_csr;
317 RETURN(l_tavv_rec);
318 END get_rec;
319
320 FUNCTION get_rec (
321 p_tavv_rec IN tavv_rec_type
322 ) RETURN tavv_rec_type IS
323 l_row_notfound BOOLEAN := TRUE;
324 BEGIN
325 RETURN(get_rec(p_tavv_rec, l_row_notfound));
326 END get_rec;
327
328 ----------------------------------------------------------
329 -- FUNCTION null_out_defaults for: OKC_TIME_TPA_VALUE_V --
330 ----------------------------------------------------------
331 FUNCTION null_out_defaults (
332 p_tavv_rec IN tavv_rec_type
333 ) RETURN tavv_rec_type IS
334 l_tavv_rec tavv_rec_type := p_tavv_rec;
335 BEGIN
336 IF (l_tavv_rec.object_version_number = OKC_API.G_MISS_NUM) THEN
337 l_tavv_rec.object_version_number := NULL;
338 END IF;
339 --Bug 3122962
340 /* IF (l_tavv_rec.sfwt_flag = OKC_API.G_MISS_CHAR) THEN
341 l_tavv_rec.sfwt_flag := NULL;
342 END IF;
343 */
344 IF (l_tavv_rec.spn_id = OKC_API.G_MISS_NUM) THEN
345 l_tavv_rec.spn_id := NULL;
346 END IF;
347 IF (l_tavv_rec.tve_id_generated_by = OKC_API.G_MISS_NUM) THEN
348 l_tavv_rec.tve_id_generated_by := NULL;
349 END IF;
350 IF (l_tavv_rec.tve_id_limited = OKC_API.G_MISS_NUM) THEN
351 l_tavv_rec.tve_id_limited := NULL;
352 END IF;
353 IF (l_tavv_rec.dnz_chr_id = OKC_API.G_MISS_NUM) THEN
354 l_tavv_rec.dnz_chr_id := NULL;
355 END IF;
356 IF (l_tavv_rec.tze_id = OKC_API.G_MISS_NUM) THEN
357 l_tavv_rec.tze_id := NULL;
358 END IF;
359 IF (l_tavv_rec.description = OKC_API.G_MISS_CHAR) THEN
360 l_tavv_rec.description := NULL;
361 END IF;
362 IF (l_tavv_rec.short_description = OKC_API.G_MISS_CHAR) THEN
363 l_tavv_rec.short_description := NULL;
364 END IF;
365 IF (l_tavv_rec.comments = OKC_API.G_MISS_CHAR) THEN
366 l_tavv_rec.comments := NULL;
367 END IF;
368 IF (l_tavv_rec.datetime = OKC_API.G_MISS_DATE) THEN
369 l_tavv_rec.datetime := NULL;
370 END IF;
371 IF (l_tavv_rec.attribute_category = OKC_API.G_MISS_CHAR) THEN
372 l_tavv_rec.attribute_category := NULL;
373 END IF;
374 IF (l_tavv_rec.attribute1 = OKC_API.G_MISS_CHAR) THEN
375 l_tavv_rec.attribute1 := NULL;
376 END IF;
377 IF (l_tavv_rec.attribute2 = OKC_API.G_MISS_CHAR) THEN
378 l_tavv_rec.attribute2 := NULL;
379 END IF;
380 IF (l_tavv_rec.attribute3 = OKC_API.G_MISS_CHAR) THEN
381 l_tavv_rec.attribute3 := NULL;
382 END IF;
383 IF (l_tavv_rec.attribute4 = OKC_API.G_MISS_CHAR) THEN
384 l_tavv_rec.attribute4 := NULL;
385 END IF;
386 IF (l_tavv_rec.attribute5 = OKC_API.G_MISS_CHAR) THEN
387 l_tavv_rec.attribute5 := NULL;
388 END IF;
389 IF (l_tavv_rec.attribute6 = OKC_API.G_MISS_CHAR) THEN
390 l_tavv_rec.attribute6 := NULL;
391 END IF;
392 IF (l_tavv_rec.attribute7 = OKC_API.G_MISS_CHAR) THEN
393 l_tavv_rec.attribute7 := NULL;
394 END IF;
395 IF (l_tavv_rec.attribute8 = OKC_API.G_MISS_CHAR) THEN
396 l_tavv_rec.attribute8 := NULL;
397 END IF;
398 IF (l_tavv_rec.attribute9 = OKC_API.G_MISS_CHAR) THEN
399 l_tavv_rec.attribute9 := NULL;
400 END IF;
401 IF (l_tavv_rec.attribute10 = OKC_API.G_MISS_CHAR) THEN
402 l_tavv_rec.attribute10 := NULL;
403 END IF;
404 IF (l_tavv_rec.attribute11 = OKC_API.G_MISS_CHAR) THEN
405 l_tavv_rec.attribute11 := NULL;
406 END IF;
407 IF (l_tavv_rec.attribute12 = OKC_API.G_MISS_CHAR) THEN
408 l_tavv_rec.attribute12 := NULL;
409 END IF;
410 IF (l_tavv_rec.attribute13 = OKC_API.G_MISS_CHAR) THEN
411 l_tavv_rec.attribute13 := NULL;
412 END IF;
413 IF (l_tavv_rec.attribute14 = OKC_API.G_MISS_CHAR) THEN
414 l_tavv_rec.attribute14 := NULL;
415 END IF;
416 IF (l_tavv_rec.attribute15 = OKC_API.G_MISS_CHAR) THEN
417 l_tavv_rec.attribute15 := NULL;
418 END IF;
419 IF (l_tavv_rec.created_by = OKC_API.G_MISS_NUM) THEN
420 l_tavv_rec.created_by := NULL;
421 END IF;
422 IF (l_tavv_rec.creation_date = OKC_API.G_MISS_DATE) THEN
423 l_tavv_rec.creation_date := NULL;
424 END IF;
425 IF (l_tavv_rec.last_updated_by = OKC_API.G_MISS_NUM) THEN
426 l_tavv_rec.last_updated_by := NULL;
427 END IF;
428 IF (l_tavv_rec.last_update_date = OKC_API.G_MISS_DATE) THEN
429 l_tavv_rec.last_update_date := NULL;
430 END IF;
431 IF (l_tavv_rec.last_update_login = OKC_API.G_MISS_NUM) THEN
432 l_tavv_rec.last_update_login := NULL;
433 END IF;
434 RETURN(l_tavv_rec);
435 END null_out_defaults;
436
437 --**** Change from TAPI Code---follow till end of change---------------
438 ------------------------------------------------------
439 -- FUNCTION null_out_defaults for: OKC_TIMEVALUES_V --
440 ------------------------------------------------------
441 -- 1. Added null_out_defaults to overcome number initialization
442 -- problem while populating optional columns for supertypes from a subtype
443 -- 2. Moved all column validations (including FK) to Validate_column
444 -- and is called from Validate_Attributes
445 -- 3. Validate_Records will have tuple rule checks.
446
447 FUNCTION null_out_defaults (
448 p_tve_rec IN tve_rec_type
449 ) RETURN tve_rec_type IS
450 l_tve_rec tve_rec_type := p_tve_rec;
451 BEGIN
452 IF (l_tve_rec.tve_id_offset = OKC_API.G_MISS_NUM) THEN
453 l_tve_rec.tve_id_offset := NULL;
454 END IF;
455 IF (l_tve_rec.uom_code = OKC_API.G_MISS_CHAR) THEN
456 l_tve_rec.uom_code := NULL;
457 END IF;
458 IF (l_tve_rec.tve_id_started = OKC_API.G_MISS_NUM) THEN
459 l_tve_rec.tve_id_started := NULL;
460 END IF;
461 IF (l_tve_rec.tve_id_ended = OKC_API.G_MISS_NUM) THEN
462 l_tve_rec.tve_id_ended := NULL;
463 END IF;
464 IF (l_tve_rec.cnh_id = OKC_API.G_MISS_NUM) THEN
465 l_tve_rec.cnh_id := NULL;
466 END IF;
467 IF (l_tve_rec.duration = OKC_API.G_MISS_NUM) THEN
468 l_tve_rec.duration := NULL;
469 END IF;
470 IF (l_tve_rec.operator = OKC_API.G_MISS_CHAR) THEN
471 l_tve_rec.operator := NULL;
472 END IF;
473 IF (l_tve_rec.before_after = OKC_API.G_MISS_CHAR) THEN
474 l_tve_rec.before_after := NULL;
475 END IF;
476 IF (l_tve_rec.month = OKC_API.G_MISS_NUM) THEN
477 l_tve_rec.month := NULL;
478 END IF;
479 IF (l_tve_rec.day = OKC_API.G_MISS_NUM) THEN
480 l_tve_rec.day := NULL;
481 END IF;
482 IF (l_tve_rec.hour = OKC_API.G_MISS_NUM) THEN
483 l_tve_rec.hour := NULL;
484 END IF;
485 IF (l_tve_rec.minute = OKC_API.G_MISS_NUM) THEN
486 l_tve_rec.minute := NULL;
487 END IF;
488 IF (l_tve_rec.second = OKC_API.G_MISS_NUM) THEN
489 l_tve_rec.second := NULL;
490 END IF;
491 IF (l_tve_rec.day_of_week = OKC_API.G_MISS_CHAR) THEN
492 l_tve_rec.day_of_week := NULL;
493 END IF;
494 IF (l_tve_rec.interval_yn = OKC_API.G_MISS_CHAR) THEN
495 l_tve_rec.interval_yn := NULL;
496 END IF;
497 IF (l_tve_rec.nth = OKC_API.G_MISS_NUM) THEN
498 l_tve_rec.nth := NULL;
499 END IF;
500 --Bug 3122962
501 IF (l_tve_rec.description = OKC_API.G_MISS_CHAR) THEN
502 l_tve_rec.description := NULL;
503 END IF;
504 IF (l_tve_rec.short_description = OKC_API.G_MISS_CHAR) THEN
505 l_tve_rec.short_description := NULL;
506 END IF;
507 IF (l_tve_rec.comments = OKC_API.G_MISS_CHAR) THEN
508 l_tve_rec.comments := NULL;
509 END IF;
510 IF (l_tve_rec.name = OKC_API.G_MISS_CHAR) THEN
511 l_tve_rec.name := NULL;
512 END IF;
513 RETURN(l_tve_rec);
514 END null_out_defaults;
515
516 /* FUNCTION null_out_defaults (
517 p_tve_tl_rec IN okc_timevalues_tl_rec_type
518 ) RETURN okc_timevalues_tl_rec_type IS
519 l_tve_tl_rec okc_timevalues_tl_rec_type := p_tve_tl_rec;
520 BEGIN
521 IF (l_tve_tl_rec.name = OKC_API.G_MISS_CHAR) THEN
522 l_tve_tl_rec.name := NULL;
523 END IF;
524 RETURN(l_tve_tl_rec);
525 END null_out_defaults;
526 */
527 ---------------------------------------------------------------------------
528 -- PROCEDURE Validate_Attributes
529 ---------------------------------------------------------------------------
530 --------------------------------------------------
531 -- Validate_Attributes for:OKC_TIME_TPA_VALUE_V --
532 --------------------------------------------------
533 PROCEDURE Validate_Spn_Id (
534 x_return_status OUT NOCOPY VARCHAR2,
535 p_tavv_rec IN tavv_rec_type) IS
536 item_not_found_error EXCEPTION;
537 l_row_notfound BOOLEAN := TRUE;
538 CURSOR okc_spnv_pk_csr (p_id IN NUMBER) IS
539 SELECT '1'
540 FROM Okc_Span
541 WHERE id = p_id;
542 l_okc_spnv_pk okc_spnv_pk_csr%ROWTYPE;
543 BEGIN
544 x_return_status := OKC_API.G_RET_STS_SUCCESS;
545 IF (p_tavv_rec.SPN_ID IS NOT NULL AND
546 p_tavv_rec.SPN_ID <> OKC_API.G_MISS_NUM)
547 THEN
548 OPEN okc_spnv_pk_csr(p_tavv_rec.SPN_ID);
549 FETCH okc_spnv_pk_csr INTO l_okc_spnv_pk;
550 l_row_notfound := okc_spnv_pk_csr%NOTFOUND;
551 CLOSE okc_spnv_pk_csr;
552 IF (l_row_notfound) THEN
553 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'SPN_ID');
554 RAISE item_not_found_error;
555 END IF;
556 END IF;
557 EXCEPTION
558 WHEN item_not_found_error THEN
559 x_return_status := OKC_API.G_RET_STS_ERROR;
560 WHEN OTHERS THEN
561 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
562 p_msg_name => g_unexpected_error,
563 p_token1 => g_sqlcode_token,
564 p_token1_value => sqlcode,
565 p_token2 => g_col_name_token,
566 p_token2_value => 'SPN_ID',
567 p_token3 => g_sqlerrm_token,
568 p_token3_value => sqlerrm);
569 -- notify caller of an UNEXPECTED error
570 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
571 END Validate_Spn_Id ;
572
573 PROCEDURE Validate_Generated_By (
574 x_return_status OUT NOCOPY VARCHAR2,
575 p_tavv_rec IN tavv_rec_type) IS
576 item_not_found_error EXCEPTION;
577 l_row_notfound BOOLEAN := TRUE;
578 CURSOR okc_tvev_pk_csr (p_id IN NUMBER) IS
579 SELECT '1'
580 FROM Okc_Timevalues
581 WHERE id = p_id;
582 l_okc_tvev_pk okc_tvev_pk_csr%ROWTYPE;
583 BEGIN
584 x_return_status := OKC_API.G_RET_STS_SUCCESS;
585 IF (p_tavv_rec.TVE_ID_GENERATED_BY IS NOT NULL AND
586 p_tavv_rec.TVE_ID_GENERATED_BY <> OKC_API.G_MISS_NUM)
587 THEN
588 OPEN okc_tvev_pk_csr(p_tavv_rec.TVE_ID_GENERATED_BY);
589 FETCH okc_tvev_pk_csr INTO l_okc_tvev_pk;
590 l_row_notfound := okc_tvev_pk_csr%NOTFOUND;
591 CLOSE okc_tvev_pk_csr;
592 IF (l_row_notfound) THEN
593 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TVE_ID_GENERATED_BY');
594 RAISE item_not_found_error;
595 END IF;
596 END IF;
597 EXCEPTION
598 WHEN item_not_found_error THEN
599 x_return_status := OKC_API.G_RET_STS_ERROR;
600 WHEN OTHERS THEN
601 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
602 p_msg_name => g_unexpected_error,
603 p_token1 => g_sqlcode_token,
604 p_token1_value => sqlcode,
605 p_token2 => g_col_name_token,
606 p_token2_value => 'TVE_ID_GENERATED_BY',
607 p_token3 => g_sqlerrm_token,
608 p_token3_value => sqlerrm);
609 -- notify caller of an UNEXPECTED error
610 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
611 END Validate_Generated_By ;
612
613 PROCEDURE Validate_Tve_Id_Limited (
614 x_return_status OUT NOCOPY VARCHAR2,
615 p_tavv_rec IN tavv_rec_type) IS
616 item_not_found_error EXCEPTION;
617 l_row_notfound BOOLEAN := TRUE;
618 CURSOR okc_tvev_pk_csr (p_id IN NUMBER) IS
619 SELECT '1'
620 FROM Okc_Timevalues
621 WHERE id = p_id
622 and tve_type in ('ISE','IGS');
623 l_okc_tvev_pk okc_tvev_pk_csr%ROWTYPE;
624 BEGIN
625 x_return_status := OKC_API.G_RET_STS_SUCCESS;
626 IF (p_tavv_rec.TVE_ID_LIMITED IS NOT NULL AND
627 p_tavv_rec.TVE_ID_LIMITED <> OKC_API.G_MISS_NUM)
628 THEN
629 OPEN okc_tvev_pk_csr(p_tavv_rec.TVE_ID_LIMITED);
630 FETCH okc_tvev_pk_csr INTO l_okc_tvev_pk;
631 l_row_notfound := okc_tvev_pk_csr%NOTFOUND;
632 CLOSE okc_tvev_pk_csr;
633 IF (l_row_notfound) THEN
634 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TVE_ID_LIMITED');
635 RAISE item_not_found_error;
636 END IF;
637 END IF;
638 EXCEPTION
639 WHEN item_not_found_error THEN
640 x_return_status := OKC_API.G_RET_STS_ERROR;
641 WHEN OTHERS THEN
642 -- store SQL error message on message stack for caller
643 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
644 p_msg_name => g_unexpected_error,
645 p_token1 => g_sqlcode_token,
646 p_token1_value => sqlcode,
647 p_token2 => g_col_name_token,
648 p_token2_value => 'TVE_ID_LIMITED',
649 p_token3 => g_sqlerrm_token,
650 p_token3_value => sqlerrm);
651 -- notify caller of an UNEXPECTED error
652 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
653 END Validate_Tve_Id_Limited ;
654
655 PROCEDURE Validate_DNZ_Chr_ID (
656 x_return_status OUT NOCOPY VARCHAR2,
657 p_tavv_rec IN tavv_rec_type) IS
658 item_not_found_error EXCEPTION;
659 l_row_notfound BOOLEAN := TRUE;
660 CURSOR okc_chrv_pk_csr (p_id IN NUMBER) IS
661 SELECT '1'
662 FROM Okc_k_headers_b
663 WHERE id = p_id;
664 l_okc_chrv_pk okc_chrv_pk_csr%ROWTYPE;
665 BEGIN
666 x_return_status := OKC_API.G_RET_STS_SUCCESS;
667 IF (p_tavv_rec.dnz_chr_id IS NOT NULL AND
668 p_tavv_rec.dnz_chr_id <> OKC_API.G_MISS_NUM)
669 THEN
670 OPEN okc_chrv_pk_csr(p_tavv_rec.dnz_chr_id);
671 FETCH okc_chrv_pk_csr INTO l_okc_chrv_pk;
672 l_row_notfound := okc_chrv_pk_csr%NOTFOUND;
673 CLOSE okc_chrv_pk_csr;
674 IF (l_row_notfound) THEN
675 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'DNZ_CHR_ID');
676 RAISE item_not_found_error;
677 END IF;
678 ELSE
679 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'DNZ_CHR_ID');
680 x_return_status := OKC_API.G_RET_STS_ERROR;
681 END IF;
682 EXCEPTION
683 WHEN item_not_found_error THEN
684 x_return_status := OKC_API.G_RET_STS_ERROR;
685 WHEN OTHERS THEN
686 -- store SQL error message on message stack for caller
687 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
688 p_msg_name => g_unexpected_error,
689 p_token1 => g_sqlcode_token,
690 p_token1_value => sqlcode,
691 p_token2 => g_col_name_token,
692 p_token2_value => 'DNZ_CHR_ID',
693 p_token3 => g_sqlerrm_token,
694 p_token3_value => sqlerrm);
695 -- notify caller of an UNEXPECTED error
696 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
697 END Validate_DNZ_Chr_Id ;
698
699 PROCEDURE Validate_Datetime (
700 x_return_status OUT NOCOPY VARCHAR2,
701 p_tavv_rec IN tavv_rec_type) IS
702 BEGIN
703 x_return_status := OKC_API.G_RET_STS_SUCCESS;
704 IF (p_tavv_rec.datetime = OKC_API.G_MISS_DATE OR
705 p_tavv_rec.datetime IS NULL) THEN
706 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'datetime');
707 x_return_status := OKC_API.G_RET_STS_ERROR;
708 END IF;
709 END Validate_Datetime;
710
711 PROCEDURE Validate_Time_Zone_Id (
712 x_return_status OUT NOCOPY VARCHAR2,
713 p_tavv_rec IN tavv_rec_type) IS
714 item_not_found_error EXCEPTION;
715 l_row_notfound BOOLEAN := TRUE;
716 l_row_found BOOLEAN := FALSE;
717 CURSOR okx_timezones_v_pk_csr (p_tze_id IN NUMBER) IS
718 SELECT '1'
719 FROM Okx_TimeZones_V
720 WHERE okx_timezones_v.timezone_id = p_tze_id;
721 l_okx_timezones_v_pk okx_timezones_v_pk_csr%ROWTYPE;
722 BEGIN
723 x_return_status := OKC_API.G_RET_STS_SUCCESS;
724 IF (p_tavv_rec.TZE_ID IS NOT NULL AND
725 p_tavv_rec.TZE_ID <> OKC_API.G_MISS_NUM)
726 THEN
727 OPEN okx_timezones_v_pk_csr(p_tavv_rec.TZE_ID);
728 FETCH okx_timezones_v_pk_csr INTO l_okx_timezones_v_pk;
729 l_row_notfound := okx_timezones_v_pk_csr%NOTFOUND;
730 CLOSE okx_timezones_v_pk_csr;
731 IF (l_row_notfound) THEN
732 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TZE_ID');
733 RAISE item_not_found_error;
734 END IF;
735 END IF;
736 EXCEPTION
737 WHEN item_not_found_error THEN
738 x_return_status := OKC_API.G_RET_STS_ERROR;
739 WHEN OTHERS THEN
740 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
741 p_msg_name => g_unexpected_error,
742 p_token1 => g_sqlcode_token,
743 p_token1_value => sqlcode,
744 p_token2 => g_col_name_token,
745 p_token2_value => 'TZE_ID',
746 p_token3 => g_sqlerrm_token,
747 p_token3_value => sqlerrm);
748 -- notify caller of an UNEXPECTED error
749 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
750 END Validate_Time_Zone_Id ;
751
752 /* PROCEDURE Validate_SFWT_Flag (
753 x_return_status OUT NOCOPY VARCHAR2,
754 p_tavv_rec IN tavv_rec_type) IS
755 BEGIN
756 IF upper(p_tavv_rec.sfwt_flag) = 'Y' OR
757 upper(p_tavv_rec.sfwt_flag) = 'N'
758 THEN
759 IF p_tavv_rec.sfwt_flag = 'Y' OR
760 p_tavv_rec.sfwt_flag = 'N'
761 THEN
762 x_return_status := OKC_API.G_RET_STS_SUCCESS;
763 ELSE
764 OKC_API.set_message(G_APP_NAME, G_UPPERCASE_REQUIRED,G_COL_NAME_TOKEN,'SFWT_FLAG');
765 x_return_status := OKC_API.G_RET_STS_ERROR;
766 END IF;
767 ELSE
768 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'SFWT_FLAG');
769 x_return_status := OKC_API.G_RET_STS_ERROR;
770 END IF;
771 END Validate_SFWT_Flag;
772 */
773 FUNCTION Validate_Attributes (
774 p_tavv_rec IN tavv_rec_type
775 ) RETURN VARCHAR2 IS
776 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
777 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
778 BEGIN
779 IF p_tavv_rec.id = OKC_API.G_MISS_NUM OR
780 p_tavv_rec.id IS NULL
781 THEN
782 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'id');
783 x_return_status := OKC_API.G_RET_STS_ERROR;
784 ELSIF p_tavv_rec.object_version_number = OKC_API.G_MISS_NUM OR
785 p_tavv_rec.object_version_number IS NULL
786 THEN
787 OKC_API.set_message(G_APP_NAME, G_REQUIRED_VALUE,G_COL_NAME_TOKEN,'object_version_number');
788 x_return_status := OKC_API.G_RET_STS_ERROR;
789 END IF;
790 IF (p_tavv_rec.spn_id is NOT NULL AND
791 p_tavv_rec.spn_id <> OKC_API.G_MISS_NUM) THEN
792 Validate_Spn_Id (l_return_status,
793 p_tavv_rec);
794 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
795 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
796 x_return_status := l_return_status;
797 RAISE G_EXCEPTION_HALT_VALIDATION;
798 ELSE
799 x_return_status := l_return_status; -- record that there was an error
800 END IF;
801 END IF;
802 END IF;
803 IF (p_tavv_rec.tve_id_generated_by is NOT NULL AND
804 p_tavv_rec.tve_id_generated_by <> OKC_API.G_MISS_NUM) THEN
805 Validate_Generated_By (l_return_status,
806 p_tavv_rec);
807 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
808 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
809 x_return_status := l_return_status;
810 RAISE G_EXCEPTION_HALT_VALIDATION;
811 ELSE
812 x_return_status := l_return_status; -- record that there was an error
813 END IF;
814 END IF;
815 END IF;
816 IF (p_tavv_rec.tve_id_limited is NOT NULL AND
817 p_tavv_rec.tve_id_limited <> OKC_API.G_MISS_NUM) THEN
818 Validate_Tve_Id_Limited (l_return_status,
819 p_tavv_rec);
820 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
821 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
822 x_return_status := l_return_status;
823 RAISE G_EXCEPTION_HALT_VALIDATION;
824 ELSE
825 x_return_status := l_return_status; -- record that there was an error
826 END IF;
827 END IF;
828 END IF;
829 IF p_tavv_rec.dnz_chr_id <> 0 Then
830 Validate_DNZ_Chr_Id (l_return_status,
831 p_tavv_rec);
832 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
833 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
834 x_return_status := l_return_status;
835 RAISE G_EXCEPTION_HALT_VALIDATION;
836 ELSE
837 x_return_status := l_return_status; -- record that there was an error
838 END IF;
839 END IF;
840 END IF;
841 Validate_Datetime (l_return_status,
842 p_tavv_rec);
843 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
844 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
845 x_return_status := l_return_status;
846 RAISE G_EXCEPTION_HALT_VALIDATION;
847 ELSE
848 x_return_status := l_return_status; -- record that there was an error
849 END IF;
850 END IF;
851 IF (p_tavv_rec.TZE_ID IS NOT NULL AND
852 p_tavv_rec.TZE_ID <> OKC_API.G_MISS_NUM)
853 THEN
854 Validate_Time_Zone_Id (l_return_status,
855 p_tavv_rec);
856 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
857 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
858 x_return_status := l_return_status;
859 RAISE G_EXCEPTION_HALT_VALIDATION;
860 ELSE
861 x_return_status := l_return_status; -- record that there was an error
862 END IF;
863 END IF;
864 END IF;
865 /* Validate_SFWT_Flag (l_return_status,
866 p_tavv_rec);
867 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
868 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN -- need to leave
869 x_return_status := l_return_status;
870 RAISE G_EXCEPTION_HALT_VALIDATION;
871 ELSE
872 x_return_status := l_return_status; -- record that there was an error
873 END IF;
874 END IF;
875 */
876 RETURN(x_return_status);
877 EXCEPTION
878 WHEN G_EXCEPTION_HALT_VALIDATION THEN
879 RETURN(x_return_status);
880
881 WHEN OTHERS THEN
882 -- store SQL error message on message stack for caller
883 OKC_API.SET_MESSAGE(p_app_name => g_app_name,
884 p_msg_name => g_unexpected_error,
885 p_token1 => g_sqlcode_token,
886 p_token1_value => sqlcode,
887 p_token2 => g_sqlerrm_token,
888 p_token2_value => sqlerrm);
889
890 -- notify caller of an UNEXPECTED error
891 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
892 RETURN(x_return_status);
893
894 END Validate_Attributes;
895
896 ---------------------------------------------------------------------------
897 -- PROCEDURE Validate_Record
898 ---------------------------------------------------------------------------
899 ----------------------------------------------
900 -- Validate_Record for:OKC_TIME_TPA_VALUE_V --
901 ----------------------------------------------
902 FUNCTION Validate_Record (
903 p_tavv_rec IN tavv_rec_type
904 ) RETURN VARCHAR2 IS
905 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
906 BEGIN
907 RETURN (l_return_status);
908 END Validate_Record;
909
910 --**** End of Change -------------------------------------------------------
911 ---------------------------------------------------------------------------
912 -- PROCEDURE Migrate
913 ---------------------------------------------------------------------------
914 PROCEDURE migrate (
915 p_from IN tavv_rec_type,
916 p_to IN OUT NOCOPY tve_rec_type
917 ) IS
918 l_tve_rec tve_rec_type;
919 BEGIN
920 -- **** The following line is added to populate record type for Supertype
921 p_to.tve_type := 'TAV';
922 p_to.id := p_from.id;
923 p_to.spn_id := p_from.spn_id;
924 p_to.tve_id_generated_by := p_from.tve_id_generated_by;
925 p_to.tve_id_limited := p_from.tve_id_limited;
926 p_to.dnz_chr_id := p_from.dnz_chr_id;
927 p_to.tze_id := p_from.tze_id;
928 p_to.object_version_number := p_from.object_version_number;
929 p_to.created_by := p_from.created_by;
930 p_to.creation_date := p_from.creation_date;
931 p_to.last_updated_by := p_from.last_updated_by;
932 p_to.last_update_date := p_from.last_update_date;
933 p_to.datetime := p_from.datetime;
934 p_to.last_update_login := p_from.last_update_login;
935 p_to.attribute_category := p_from.attribute_category;
936 p_to.attribute1 := p_from.attribute1;
937 p_to.attribute2 := p_from.attribute2;
938 p_to.attribute3 := p_from.attribute3;
939 p_to.attribute4 := p_from.attribute4;
940 p_to.attribute5 := p_from.attribute5;
941 p_to.attribute6 := p_from.attribute6;
942 p_to.attribute7 := p_from.attribute7;
943 p_to.attribute8 := p_from.attribute8;
944 p_to.attribute9 := p_from.attribute9;
945 p_to.attribute10 := p_from.attribute10;
946 p_to.attribute11 := p_from.attribute11;
947 p_to.attribute12 := p_from.attribute12;
948 p_to.attribute13 := p_from.attribute13;
949 p_to.attribute14 := p_from.attribute14;
950 p_to.attribute15 := p_from.attribute15;
951 --Bug 3122962
952 p_to.description := p_from.description;
953 p_to.short_description := p_from.short_description;
954 p_to.comments := p_from.comments;
955 -- p_to.name := p_from.name;
956 -- **** Added null_out_defaults to overcome number initialization
957 -- problem while populating optional columns for supertypes from a subtype
958 l_tve_rec := null_out_defaults(p_to);
959 p_to := l_tve_rec;
960 END migrate;
961 PROCEDURE migrate (
962 p_from IN tve_rec_type,
963 p_to IN OUT NOCOPY tavv_rec_type
964 ) IS
965 BEGIN
966 p_to.id := p_from.id;
967 p_to.spn_id := p_from.spn_id;
968 p_to.tve_id_generated_by := p_from.tve_id_generated_by;
969 p_to.tve_id_limited := p_from.tve_id_limited;
970 p_to.dnz_chr_id := p_from.dnz_chr_id;
971 p_to.tze_id := p_from.tze_id;
972 p_to.object_version_number := p_from.object_version_number;
973 p_to.created_by := p_from.created_by;
974 p_to.creation_date := p_from.creation_date;
975 p_to.last_updated_by := p_from.last_updated_by;
976 p_to.last_update_date := p_from.last_update_date;
977 p_to.datetime := p_from.datetime;
978 p_to.last_update_login := p_from.last_update_login;
979 p_to.attribute_category := p_from.attribute_category;
980 p_to.attribute1 := p_from.attribute1;
981 p_to.attribute2 := p_from.attribute2;
982 p_to.attribute3 := p_from.attribute3;
983 p_to.attribute4 := p_from.attribute4;
984 p_to.attribute5 := p_from.attribute5;
985 p_to.attribute6 := p_from.attribute6;
986 p_to.attribute7 := p_from.attribute7;
987 p_to.attribute8 := p_from.attribute8;
988 p_to.attribute9 := p_from.attribute9;
989 p_to.attribute10 := p_from.attribute10;
990 p_to.attribute11 := p_from.attribute11;
991 p_to.attribute12 := p_from.attribute12;
992 p_to.attribute13 := p_from.attribute13;
993 p_to.attribute14 := p_from.attribute14;
994 p_to.attribute15 := p_from.attribute15;
995 --Bug 3122962
996 p_to.description := p_from.description;
997 p_to.short_description := p_from.short_description;
998 p_to.comments := p_from.comments;
999 -- p_to.name := p_from.name;
1000 END migrate;
1001 /*
1002 PROCEDURE migrate (
1003 p_from IN tavv_rec_type,
1004 p_to IN OUT NOCOPY okc_timevalues_tl_rec_type
1005 ) IS
1006 l_tve_tl_type okc_timevalues_tl_rec_type;
1007 BEGIN
1008 p_to.id := p_from.id;
1009 p_to.sfwt_flag := p_from.sfwt_flag;
1010 p_to.description := p_from.description;
1011 p_to.short_description := p_from.short_description;
1012 p_to.comments := p_from.comments;
1013 p_to.created_by := p_from.created_by;
1014 p_to.creation_date := p_from.creation_date;
1015 p_to.last_updated_by := p_from.last_updated_by;
1016 p_to.last_update_date := p_from.last_update_date;
1017 p_to.last_update_login := p_from.last_update_login;
1018 -- **** Added null_out_defaults to overcome number initialization
1019 -- problem while populating optional columns for supertypes from a subtype
1020 l_tve_tl_type := null_out_defaults(p_to);
1021 p_to := l_tve_tl_type;
1022 END migrate;
1023 PROCEDURE migrate (
1024 p_from IN okc_timevalues_tl_rec_type,
1025 p_to IN OUT NOCOPY tavv_rec_type
1026 ) IS
1027 BEGIN
1028 p_to.id := p_from.id;
1029 p_to.sfwt_flag := p_from.sfwt_flag;
1030 p_to.description := p_from.description;
1031 p_to.short_description := p_from.short_description;
1032 p_to.comments := p_from.comments;
1033 p_to.created_by := p_from.created_by;
1034 p_to.creation_date := p_from.creation_date;
1035 p_to.last_updated_by := p_from.last_updated_by;
1036 p_to.last_update_date := p_from.last_update_date;
1037 p_to.last_update_login := p_from.last_update_login;
1038 END migrate;
1039 */
1040 ---------------------------------------------------------------------------
1041 -- PROCEDURE validate_row
1042 ---------------------------------------------------------------------------
1043 -------------------------------------------
1044 -- validate_row for:OKC_TIME_TPA_VALUE_V --
1045 -------------------------------------------
1046 PROCEDURE validate_row(
1047 p_api_version IN NUMBER,
1048 p_init_msg_list IN VARCHAR2,
1049 x_return_status OUT NOCOPY VARCHAR2,
1050 x_msg_count OUT NOCOPY NUMBER,
1051 x_msg_data OUT NOCOPY VARCHAR2,
1052 p_tavv_rec IN tavv_rec_type) IS
1053
1054 l_api_version CONSTANT NUMBER := 1;
1055 l_api_name CONSTANT VARCHAR2(30) := 'V_validate_row';
1056 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1057 l_tavv_rec tavv_rec_type := p_tavv_rec;
1058 l_tve_rec tve_rec_type;
1059 --Bug 3122962 l_okc_timevalues_tl_rec okc_timevalues_tl_rec_type;
1060 BEGIN
1061 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1062 G_PKG_NAME,
1063 p_init_msg_list,
1064 l_api_version,
1065 p_api_version,
1066 '_PVT',
1067 x_return_status);
1068 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1069 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1070 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1071 RAISE OKC_API.G_EXCEPTION_ERROR;
1072 END IF;
1073 --- Validate all non-missing attributes (Item Level Validation)
1074 l_return_status := Validate_Attributes(l_tavv_rec);
1075 --- If any errors happen abort API
1076 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1077 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1078 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1079 RAISE OKC_API.G_EXCEPTION_ERROR;
1080 END IF;
1081 l_return_status := Validate_Record(l_tavv_rec);
1082 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1083 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1084 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1085 RAISE OKC_API.G_EXCEPTION_ERROR;
1086 END IF;
1087 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1088 EXCEPTION
1089 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1090 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1091 (
1092 l_api_name,
1093 G_PKG_NAME,
1094 'OKC_API.G_RET_STS_ERROR',
1095 x_msg_count,
1096 x_msg_data,
1097 '_PVT'
1098 );
1099 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1100 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1101 (
1102 l_api_name,
1103 G_PKG_NAME,
1104 'OKC_API.G_RET_STS_UNEXP_ERROR',
1105 x_msg_count,
1106 x_msg_data,
1107 '_PVT'
1108 );
1109 WHEN OTHERS THEN
1110 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1111 (
1112 l_api_name,
1113 G_PKG_NAME,
1114 'OTHERS',
1115 x_msg_count,
1116 x_msg_data,
1117 '_PVT'
1118 );
1119 END validate_row;
1120 ------------------------------------------
1121 -- PL/SQL TBL validate_row for:TAVV_TBL --
1122 ------------------------------------------
1123 PROCEDURE validate_row(
1124 p_api_version IN NUMBER,
1125 p_init_msg_list IN VARCHAR2,
1126 x_return_status OUT NOCOPY VARCHAR2,
1127 x_msg_count OUT NOCOPY NUMBER,
1128 x_msg_data OUT NOCOPY VARCHAR2,
1129 p_tavv_tbl IN tavv_tbl_type) IS
1130
1131 l_api_version CONSTANT NUMBER := 1;
1132 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_validate_row';
1133 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1134 i NUMBER := 0;
1135 BEGIN
1136 -- Make sure PL/SQL table has records in it before passing
1137 IF (p_tavv_tbl.COUNT > 0) THEN
1138 i := p_tavv_tbl.FIRST;
1139 LOOP
1140 validate_row (
1141 p_api_version => p_api_version,
1142 p_init_msg_list => p_init_msg_list,
1143 x_return_status => x_return_status,
1144 x_msg_count => x_msg_count,
1145 x_msg_data => x_msg_data,
1146 p_tavv_rec => p_tavv_tbl(i));
1147 EXIT WHEN (i = p_tavv_tbl.LAST);
1148 i := p_tavv_tbl.NEXT(i);
1149 END LOOP;
1150 END IF;
1151 EXCEPTION
1152 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1153 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1154 (
1155 l_api_name,
1156 G_PKG_NAME,
1157 'OKC_API.G_RET_STS_ERROR',
1158 x_msg_count,
1159 x_msg_data,
1160 '_PVT'
1161 );
1162 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1163 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1164 (
1165 l_api_name,
1166 G_PKG_NAME,
1167 'OKC_API.G_RET_STS_UNEXP_ERROR',
1168 x_msg_count,
1169 x_msg_data,
1170 '_PVT'
1171 );
1172 WHEN OTHERS THEN
1173 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1174 (
1175 l_api_name,
1176 G_PKG_NAME,
1177 'OTHERS',
1178 x_msg_count,
1179 x_msg_data,
1180 '_PVT'
1181 );
1182 END validate_row;
1183
1184 ---------------------------------------------------------------------------
1185 -- PROCEDURE insert_row
1186 ---------------------------------------------------------------------------
1187 -------------------------------------
1188 -- insert_row for:OKC_TIMEVALUES_B --
1189 -------------------------------------
1190 PROCEDURE insert_row(
1191 p_init_msg_list IN VARCHAR2,
1192 x_return_status OUT NOCOPY VARCHAR2,
1193 x_msg_count OUT NOCOPY NUMBER,
1194 x_msg_data OUT NOCOPY VARCHAR2,
1195 p_tve_rec IN tve_rec_type,
1196 x_tve_rec OUT NOCOPY tve_rec_type) IS
1197
1198 l_api_version CONSTANT NUMBER := 1;
1199 l_api_name CONSTANT VARCHAR2(30) := 'B_insert_row';
1200 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1201 l_tve_rec tve_rec_type := p_tve_rec;
1202 l_def_tve_rec tve_rec_type;
1203 -----------------------------------------
1204 -- Set_Attributes for:OKC_TIMEVALUES_B --
1205 -----------------------------------------
1206 FUNCTION Set_Attributes (
1207 p_tve_rec IN tve_rec_type,
1208 x_tve_rec OUT NOCOPY tve_rec_type
1209 ) RETURN VARCHAR2 IS
1210 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1211 BEGIN
1212 x_tve_rec := p_tve_rec;
1213 RETURN(l_return_status);
1214 END Set_Attributes;
1215 BEGIN
1216 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1217 p_init_msg_list,
1218 '_PVT',
1219 x_return_status);
1220 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1221 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1222 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1223 RAISE OKC_API.G_EXCEPTION_ERROR;
1224 END IF;
1225 --- Setting item attributes
1226 l_return_status := Set_Attributes(
1227 p_tve_rec, -- IN
1228 l_tve_rec); -- OUT
1229 --- If any errors happen abort API
1230 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1231 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1232 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1233 RAISE OKC_API.G_EXCEPTION_ERROR;
1234 END IF;
1235 INSERT INTO OKC_TIMEVALUES(
1236 id,
1237 spn_id,
1238 tve_id_offset,
1239 uom_code,
1240 tve_id_generated_by,
1241 dnz_chr_id,
1242 tze_id,
1243 tve_id_limited,
1244 tve_id_started,
1245 tve_id_ended,
1246 cnh_id,
1247 tve_type,
1248 object_version_number,
1249 created_by,
1250 creation_date,
1251 last_updated_by,
1252 last_update_date,
1253 duration,
1254 operator,
1255 before_after,
1256 datetime,
1257 month,
1258 day,
1259 hour,
1260 minute,
1261 second,
1262 interval_yn,
1263 nth,
1264 day_of_week,
1265 last_update_login,
1266 attribute_category,
1267 --Bug 3122962
1268 description,
1269 short_description,
1270 comments,
1271 name,
1272
1273 attribute1,
1274 attribute2,
1275 attribute3,
1276 attribute4,
1277 attribute5,
1278 attribute6,
1279 attribute7,
1280 attribute8,
1281 attribute9,
1282 attribute10,
1283 attribute11,
1284 attribute12,
1285 attribute13,
1286 attribute14,
1287 attribute15)
1288 VALUES (
1289 l_tve_rec.id,
1290 l_tve_rec.spn_id,
1291 l_tve_rec.tve_id_offset,
1292 l_tve_rec.uom_code,
1293 l_tve_rec.tve_id_generated_by,
1294 l_tve_rec.dnz_chr_id,
1295 l_tve_rec.tze_id,
1296 l_tve_rec.tve_id_limited,
1297 l_tve_rec.tve_id_started,
1298 l_tve_rec.tve_id_ended,
1299 l_tve_rec.cnh_id,
1300 l_tve_rec.tve_type,
1301 l_tve_rec.object_version_number,
1302 l_tve_rec.created_by,
1303 l_tve_rec.creation_date,
1304 l_tve_rec.last_updated_by,
1305 l_tve_rec.last_update_date,
1306 l_tve_rec.duration,
1307 l_tve_rec.operator,
1308 l_tve_rec.before_after,
1309 l_tve_rec.datetime,
1310 l_tve_rec.month,
1311 l_tve_rec.day,
1312 l_tve_rec.hour,
1313 l_tve_rec.minute,
1314 l_tve_rec.second,
1315 l_tve_rec.interval_yn,
1316 l_tve_rec.nth,
1317 l_tve_rec.day_of_week,
1318 l_tve_rec.last_update_login,
1319 l_tve_rec.attribute_category,
1320 --Bug 3122962
1321 l_tve_rec.description,
1322 l_tve_rec.short_description,
1323 l_tve_rec.comments,
1324 l_tve_rec.name,
1325
1326 l_tve_rec.attribute1,
1327 l_tve_rec.attribute2,
1328 l_tve_rec.attribute3,
1329 l_tve_rec.attribute4,
1330 l_tve_rec.attribute5,
1331 l_tve_rec.attribute6,
1332 l_tve_rec.attribute7,
1333 l_tve_rec.attribute8,
1334 l_tve_rec.attribute9,
1335 l_tve_rec.attribute10,
1336 l_tve_rec.attribute11,
1337 l_tve_rec.attribute12,
1338 l_tve_rec.attribute13,
1339 l_tve_rec.attribute14,
1340 l_tve_rec.attribute15);
1341 -- Set OUT values
1342 x_tve_rec := l_tve_rec;
1343 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1344 EXCEPTION
1345 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1346 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1347 (
1348 l_api_name,
1349 G_PKG_NAME,
1350 'OKC_API.G_RET_STS_ERROR',
1351 x_msg_count,
1352 x_msg_data,
1353 '_PVT'
1354 );
1355 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1356 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1357 (
1358 l_api_name,
1359 G_PKG_NAME,
1360 'OKC_API.G_RET_STS_UNEXP_ERROR',
1361 x_msg_count,
1362 x_msg_data,
1363 '_PVT'
1364 );
1365 WHEN OTHERS THEN
1366 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1367 (
1368 l_api_name,
1369 G_PKG_NAME,
1370 'OTHERS',
1371 x_msg_count,
1372 x_msg_data,
1373 '_PVT'
1374 );
1375 END insert_row;
1376 --------------------------------------
1377 -- insert_row for:OKC_TIMEVALUES_TL --
1378 --------------------------------------
1379 /* PROCEDURE insert_row(
1380 p_init_msg_list IN VARCHAR2,
1381 x_return_status OUT NOCOPY VARCHAR2,
1382 x_msg_count OUT NOCOPY NUMBER,
1383 x_msg_data OUT NOCOPY VARCHAR2,
1384 p_okc_timevalues_tl_rec IN okc_timevalues_tl_rec_type,
1385 x_okc_timevalues_tl_rec OUT NOCOPY okc_timevalues_tl_rec_type) IS
1386
1387 l_api_version CONSTANT NUMBER := 1;
1388 l_api_name CONSTANT VARCHAR2(30) := 'TL_insert_row';
1389 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1390 l_okc_timevalues_tl_rec okc_timevalues_tl_rec_type := p_okc_timevalues_tl_rec;
1391 l_def_okc_timevalues_tl_rec okc_timevalues_tl_rec_type;
1392 CURSOR get_languages IS
1393 SELECT *
1394 FROM FND_LANGUAGES
1395 WHERE INSTALLED_FLAG IN ('I', 'B');
1396 ------------------------------------------
1397 -- Set_Attributes for:OKC_TIMEVALUES_TL --
1398 ------------------------------------------
1399 FUNCTION Set_Attributes (
1400 p_okc_timevalues_tl_rec IN okc_timevalues_tl_rec_type,
1401 x_okc_timevalues_tl_rec OUT NOCOPY okc_timevalues_tl_rec_type
1402 ) RETURN VARCHAR2 IS
1403 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1404 BEGIN
1405 x_okc_timevalues_tl_rec := p_okc_timevalues_tl_rec;
1406 x_okc_timevalues_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
1407 x_okc_timevalues_tl_rec.SOURCE_LANG := okc_util.get_userenv_lang;
1408 RETURN(l_return_status);
1409 END Set_Attributes;
1410 BEGIN
1411 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1412 p_init_msg_list,
1413 '_PVT',
1414 x_return_status);
1415 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1416 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1417 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1418 RAISE OKC_API.G_EXCEPTION_ERROR;
1419 END IF;
1420 --- Setting item attributes
1421 l_return_status := Set_Attributes(
1422 p_okc_timevalues_tl_rec, -- IN
1423 l_okc_timevalues_tl_rec); -- OUT
1424 --- If any errors happen abort API
1425 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1426 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1427 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1428 RAISE OKC_API.G_EXCEPTION_ERROR;
1429 END IF;
1430 FOR l_lang_rec IN get_languages LOOP
1431 l_okc_timevalues_tl_rec.language := l_lang_rec.language_code;
1432 INSERT INTO OKC_TIMEVALUES_TL(
1433 id,
1434 language,
1435 source_lang,
1436 sfwt_flag,
1437 description,
1438 short_description,
1439 comments,
1440 name,
1441 created_by,
1442 creation_date,
1443 last_updated_by,
1444 last_update_date,
1445 last_update_login)
1446 VALUES (
1447 l_okc_timevalues_tl_rec.id,
1448 l_okc_timevalues_tl_rec.language,
1449 l_okc_timevalues_tl_rec.source_lang,
1450 l_okc_timevalues_tl_rec.sfwt_flag,
1451 l_okc_timevalues_tl_rec.description,
1452 l_okc_timevalues_tl_rec.short_description,
1453 l_okc_timevalues_tl_rec.comments,
1454 l_okc_timevalues_tl_rec.name,
1455 l_okc_timevalues_tl_rec.created_by,
1456 l_okc_timevalues_tl_rec.creation_date,
1457 l_okc_timevalues_tl_rec.last_updated_by,
1458 l_okc_timevalues_tl_rec.last_update_date,
1459 l_okc_timevalues_tl_rec.last_update_login);
1460 END LOOP;
1461 -- Set OUT values
1462 x_okc_timevalues_tl_rec := l_okc_timevalues_tl_rec;
1463 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1464 EXCEPTION
1465 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1466 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1467 (
1468 l_api_name,
1469 G_PKG_NAME,
1470 'OKC_API.G_RET_STS_ERROR',
1471 x_msg_count,
1472 x_msg_data,
1473 '_PVT'
1474 );
1475 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1476 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1477 (
1478 l_api_name,
1479 G_PKG_NAME,
1480 'OKC_API.G_RET_STS_UNEXP_ERROR',
1481 x_msg_count,
1482 x_msg_data,
1483 '_PVT'
1484 );
1485 WHEN OTHERS THEN
1486 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1487 (
1488 l_api_name,
1489 G_PKG_NAME,
1490 'OTHERS',
1491 x_msg_count,
1492 x_msg_data,
1493 '_PVT'
1494 );
1495 END insert_row;
1496 */
1497 -----------------------------------------
1498 -- insert_row for:OKC_TIME_TPA_VALUE_V --
1499 -----------------------------------------
1500 PROCEDURE insert_row(
1501 p_api_version IN NUMBER,
1502 p_init_msg_list IN VARCHAR2,
1503 x_return_status OUT NOCOPY VARCHAR2,
1504 x_msg_count OUT NOCOPY NUMBER,
1505 x_msg_data OUT NOCOPY VARCHAR2,
1506 p_tavv_rec IN tavv_rec_type,
1507 x_tavv_rec OUT NOCOPY tavv_rec_type) IS
1508
1509 l_api_version CONSTANT NUMBER := 1;
1510 l_api_name CONSTANT VARCHAR2(30) := 'V_insert_row';
1511 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1512 l_tavv_rec tavv_rec_type;
1513 l_def_tavv_rec tavv_rec_type;
1514 l_tve_rec tve_rec_type;
1515 lx_tve_rec tve_rec_type;
1516 --Bug 3122962 l_okc_timevalues_tl_rec okc_timevalues_tl_rec_type;
1517 --Bug 3122962 lx_okc_timevalues_tl_rec okc_timevalues_tl_rec_type;
1518 -------------------------------
1519 -- FUNCTION fill_who_columns --
1520 -------------------------------
1521 FUNCTION fill_who_columns (
1522 p_tavv_rec IN tavv_rec_type
1523 ) RETURN tavv_rec_type IS
1524 l_tavv_rec tavv_rec_type := p_tavv_rec;
1525 BEGIN
1526 l_tavv_rec.CREATION_DATE := SYSDATE;
1527 l_tavv_rec.CREATED_BY := FND_GLOBAL.USER_ID;
1528 l_tavv_rec.LAST_UPDATE_DATE := l_tavv_rec.CREATION_DATE;
1529 l_tavv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1530 l_tavv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
1531 RETURN(l_tavv_rec);
1532 END fill_who_columns;
1533 ---------------------------------------------
1534 -- Set_Attributes for:OKC_TIME_TPA_VALUE_V --
1535 ---------------------------------------------
1536 FUNCTION Set_Attributes (
1537 p_tavv_rec IN tavv_rec_type,
1538 x_tavv_rec OUT NOCOPY tavv_rec_type
1539 ) RETURN VARCHAR2 IS
1540 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1541 BEGIN
1542 x_tavv_rec := p_tavv_rec;
1543 x_tavv_rec.OBJECT_VERSION_NUMBER := 1;
1544 --Bug 3122962 x_tavv_rec.SFWT_FLAG := 'N';
1545 RETURN(l_return_status);
1546 END Set_Attributes;
1547 BEGIN
1548 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1549 G_PKG_NAME,
1550 p_init_msg_list,
1551 l_api_version,
1552 p_api_version,
1553 '_PVT',
1554 x_return_status);
1555 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1556 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1557 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1558 RAISE OKC_API.G_EXCEPTION_ERROR;
1559 END IF;
1560 l_tavv_rec := null_out_defaults(p_tavv_rec);
1561 -- Set primary key value
1562 l_tavv_rec.ID := get_seq_id;
1563 --- Setting item attributes
1564 l_return_status := Set_Attributes(
1565 l_tavv_rec, -- IN
1566 l_def_tavv_rec); -- OUT
1567 --- If any errors happen abort API
1568 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1569 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1570 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1571 RAISE OKC_API.G_EXCEPTION_ERROR;
1572 END IF;
1573 l_def_tavv_rec := fill_who_columns(l_def_tavv_rec);
1574 --- Validate all non-missing attributes (Item Level Validation)
1575 l_return_status := Validate_Attributes(l_def_tavv_rec);
1576 --- If any errors happen abort API
1577 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1578 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1579 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1580 RAISE OKC_API.G_EXCEPTION_ERROR;
1581 END IF;
1582 l_return_status := Validate_Record(l_def_tavv_rec);
1583 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1584 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1585 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1586 RAISE OKC_API.G_EXCEPTION_ERROR;
1587 END IF;
1588 --------------------------------------
1589 -- Move VIEW record to "Child" records
1590 --------------------------------------
1591 migrate(l_def_tavv_rec, l_tve_rec);
1592 --Bug 3122962 migrate(l_def_tavv_rec, l_okc_timevalues_tl_rec);
1593 --------------------------------------------
1594 -- Call the INSERT_ROW for each child record
1595 --------------------------------------------
1596 insert_row(
1597 p_init_msg_list,
1598 x_return_status,
1599 x_msg_count,
1600 x_msg_data,
1601 l_tve_rec,
1602 lx_tve_rec
1603 );
1604 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1605 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1606 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1607 RAISE OKC_API.G_EXCEPTION_ERROR;
1608 END IF;
1609 migrate(lx_tve_rec, l_def_tavv_rec);
1610 /* insert_row(
1611 p_init_msg_list,
1612 x_return_status,
1613 x_msg_count,
1614 x_msg_data,
1615 l_okc_timevalues_tl_rec,
1616 lx_okc_timevalues_tl_rec
1617 );
1618 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1619 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1620 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1621 RAISE OKC_API.G_EXCEPTION_ERROR;
1622 END IF;
1623 migrate(lx_okc_timevalues_tl_rec, l_def_tavv_rec);
1624 */
1625 -- Set OUT values
1626 x_tavv_rec := l_def_tavv_rec;
1627 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1628 EXCEPTION
1629 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1630 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1631 (
1632 l_api_name,
1633 G_PKG_NAME,
1634 'OKC_API.G_RET_STS_ERROR',
1635 x_msg_count,
1636 x_msg_data,
1637 '_PVT'
1638 );
1639 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1640 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1641 (
1642 l_api_name,
1643 G_PKG_NAME,
1644 'OKC_API.G_RET_STS_UNEXP_ERROR',
1645 x_msg_count,
1646 x_msg_data,
1647 '_PVT'
1648 );
1649 WHEN OTHERS THEN
1650 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1651 (
1652 l_api_name,
1653 G_PKG_NAME,
1654 'OTHERS',
1655 x_msg_count,
1656 x_msg_data,
1657 '_PVT'
1658 );
1659 END insert_row;
1660 ----------------------------------------
1661 -- PL/SQL TBL insert_row for:TAVV_TBL --
1662 ----------------------------------------
1663 PROCEDURE insert_row(
1664 p_api_version IN NUMBER,
1665 p_init_msg_list IN VARCHAR2,
1666 x_return_status OUT NOCOPY VARCHAR2,
1667 x_msg_count OUT NOCOPY NUMBER,
1668 x_msg_data OUT NOCOPY VARCHAR2,
1669 p_tavv_tbl IN tavv_tbl_type,
1670 x_tavv_tbl OUT NOCOPY tavv_tbl_type) IS
1671
1672 l_api_version CONSTANT NUMBER := 1;
1673 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_insert_row';
1674 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1675 i NUMBER := 0;
1676 BEGIN
1677 -- Make sure PL/SQL table has records in it before passing
1678 IF (p_tavv_tbl.COUNT > 0) THEN
1679 i := p_tavv_tbl.FIRST;
1680 LOOP
1681 insert_row (
1682 p_api_version => p_api_version,
1683 p_init_msg_list => p_init_msg_list,
1684 x_return_status => x_return_status,
1685 x_msg_count => x_msg_count,
1686 x_msg_data => x_msg_data,
1687 p_tavv_rec => p_tavv_tbl(i),
1688 x_tavv_rec => x_tavv_tbl(i));
1689 EXIT WHEN (i = p_tavv_tbl.LAST);
1690 i := p_tavv_tbl.NEXT(i);
1691 END LOOP;
1692 END IF;
1693 EXCEPTION
1694 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1695 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1696 (
1697 l_api_name,
1698 G_PKG_NAME,
1699 'OKC_API.G_RET_STS_ERROR',
1700 x_msg_count,
1701 x_msg_data,
1702 '_PVT'
1703 );
1704 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1705 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1706 (
1707 l_api_name,
1708 G_PKG_NAME,
1709 'OKC_API.G_RET_STS_UNEXP_ERROR',
1710 x_msg_count,
1711 x_msg_data,
1712 '_PVT'
1713 );
1714 WHEN OTHERS THEN
1715 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1716 (
1717 l_api_name,
1718 G_PKG_NAME,
1719 'OTHERS',
1720 x_msg_count,
1721 x_msg_data,
1722 '_PVT'
1723 );
1724 END insert_row;
1725
1726 ---------------------------------------------------------------------------
1727 -- PROCEDURE lock_row
1728 ---------------------------------------------------------------------------
1729 -----------------------------------
1730 -- lock_row for:OKC_TIMEVALUES_B --
1731 -----------------------------------
1732 PROCEDURE lock_row(
1733 p_init_msg_list IN VARCHAR2,
1734 x_return_status OUT NOCOPY VARCHAR2,
1735 x_msg_count OUT NOCOPY NUMBER,
1736 x_msg_data OUT NOCOPY VARCHAR2,
1737 p_tve_rec IN tve_rec_type) IS
1738
1739 E_Resource_Busy EXCEPTION;
1740 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1741 CURSOR lock_csr (p_tve_rec IN tve_rec_type) IS
1742 SELECT OBJECT_VERSION_NUMBER
1743 FROM OKC_TIMEVALUES
1744 WHERE ID = p_tve_rec.id
1745 AND OBJECT_VERSION_NUMBER = p_tve_rec.object_version_number
1746 FOR UPDATE OF OBJECT_VERSION_NUMBER NOWAIT;
1747
1748 CURSOR lchk_csr (p_tve_rec IN tve_rec_type) IS
1749 SELECT OBJECT_VERSION_NUMBER
1750 FROM OKC_TIMEVALUES
1751 WHERE ID = p_tve_rec.id;
1752 l_api_version CONSTANT NUMBER := 1;
1753 l_api_name CONSTANT VARCHAR2(30) := 'B_lock_row';
1754 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1755 l_object_version_number OKC_TIMEVALUES.OBJECT_VERSION_NUMBER%TYPE;
1756 lc_object_version_number OKC_TIMEVALUES.OBJECT_VERSION_NUMBER%TYPE;
1757 l_row_notfound BOOLEAN := FALSE;
1758 lc_row_notfound BOOLEAN := FALSE;
1759 BEGIN
1760 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1761 p_init_msg_list,
1762 '_PVT',
1763 x_return_status);
1764 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1765 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1766 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1767 RAISE OKC_API.G_EXCEPTION_ERROR;
1768 END IF;
1769 BEGIN
1770 OPEN lock_csr(p_tve_rec);
1771 FETCH lock_csr INTO l_object_version_number;
1772 l_row_notfound := lock_csr%NOTFOUND;
1773 CLOSE lock_csr;
1774 EXCEPTION
1775 WHEN E_Resource_Busy THEN
1776 IF (lock_csr%ISOPEN) THEN
1777 CLOSE lock_csr;
1778 END IF;
1779 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1780 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1781 END;
1782
1783 IF ( l_row_notfound ) THEN
1784 OPEN lchk_csr(p_tve_rec);
1785 FETCH lchk_csr INTO lc_object_version_number;
1786 lc_row_notfound := lchk_csr%NOTFOUND;
1787 CLOSE lchk_csr;
1788 END IF;
1789 IF (lc_row_notfound) THEN
1790 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1791 RAISE OKC_API.G_EXCEPTION_ERROR;
1792 ELSIF lc_object_version_number > p_tve_rec.object_version_number THEN
1793 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1794 RAISE OKC_API.G_EXCEPTION_ERROR;
1795 ELSIF lc_object_version_number <> p_tve_rec.object_version_number THEN
1796 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_CHANGED);
1797 RAISE OKC_API.G_EXCEPTION_ERROR;
1798 ELSIF lc_object_version_number = -1 THEN
1799 OKC_API.set_message(G_APP_NAME,G_RECORD_LOGICALLY_DELETED);
1800 RAISE OKC_API.G_EXCEPTION_ERROR;
1801 END IF;
1802 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1803 EXCEPTION
1804 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1805 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1806 (
1807 l_api_name,
1808 G_PKG_NAME,
1809 'OKC_API.G_RET_STS_ERROR',
1810 x_msg_count,
1811 x_msg_data,
1812 '_PVT'
1813 );
1814 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1815 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1816 (
1817 l_api_name,
1818 G_PKG_NAME,
1819 'OKC_API.G_RET_STS_UNEXP_ERROR',
1820 x_msg_count,
1821 x_msg_data,
1822 '_PVT'
1823 );
1824 WHEN OTHERS THEN
1825 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1826 (
1827 l_api_name,
1828 G_PKG_NAME,
1829 'OTHERS',
1830 x_msg_count,
1831 x_msg_data,
1832 '_PVT'
1833 );
1834 END lock_row;
1835 ------------------------------------
1836 -- lock_row for:OKC_TIMEVALUES_TL --
1837 ------------------------------------
1838 /* PROCEDURE lock_row(
1839 p_init_msg_list IN VARCHAR2,
1840 x_return_status OUT NOCOPY VARCHAR2,
1841 x_msg_count OUT NOCOPY NUMBER,
1842 x_msg_data OUT NOCOPY VARCHAR2,
1843 p_okc_timevalues_tl_rec IN okc_timevalues_tl_rec_type) IS
1844
1845 E_Resource_Busy EXCEPTION;
1846 PRAGMA EXCEPTION_INIT(E_Resource_Busy, -00054);
1847 CURSOR lock_csr (p_okc_timevalues_tl_rec IN okc_timevalues_tl_rec_type) IS
1848 SELECT id
1849 FROM OKC_TIMEVALUES_TL
1850 WHERE ID = p_okc_timevalues_tl_rec.id
1851 FOR UPDATE NOWAIT;
1852
1853 l_api_version CONSTANT NUMBER := 1;
1854 l_api_name CONSTANT VARCHAR2(30) := 'TL_lock_row';
1855 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1856 l_lock_var lock_csr%ROWTYPE;
1857 l_row_notfound BOOLEAN := FALSE;
1858 lc_row_notfound BOOLEAN := FALSE;
1859 BEGIN
1860 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1861 p_init_msg_list,
1862 '_PVT',
1863 x_return_status);
1864 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1865 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1866 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1867 RAISE OKC_API.G_EXCEPTION_ERROR;
1868 END IF;
1869 BEGIN
1870 OPEN lock_csr(p_okc_timevalues_tl_rec);
1871 FETCH lock_csr INTO l_lock_var;
1872 l_row_notfound := lock_csr%NOTFOUND;
1873 CLOSE lock_csr;
1874 EXCEPTION
1875 WHEN E_Resource_Busy THEN
1876 IF (lock_csr%ISOPEN) THEN
1877 CLOSE lock_csr;
1878 END IF;
1879 OKC_API.set_message(G_FND_APP,G_FORM_UNABLE_TO_RESERVE_REC);
1880 RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1881 END;
1882
1883 IF ( l_row_notfound ) THEN
1884 OKC_API.set_message(G_FND_APP,G_FORM_RECORD_DELETED);
1885 RAISE OKC_API.G_EXCEPTION_ERROR;
1886 END IF;
1887 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1888 EXCEPTION
1889 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1890 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1891 (
1892 l_api_name,
1893 G_PKG_NAME,
1894 'OKC_API.G_RET_STS_ERROR',
1895 x_msg_count,
1896 x_msg_data,
1897 '_PVT'
1898 );
1899 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1900 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1901 (
1902 l_api_name,
1903 G_PKG_NAME,
1904 'OKC_API.G_RET_STS_UNEXP_ERROR',
1905 x_msg_count,
1906 x_msg_data,
1907 '_PVT'
1908 );
1909 WHEN OTHERS THEN
1910 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1911 (
1912 l_api_name,
1913 G_PKG_NAME,
1914 'OTHERS',
1915 x_msg_count,
1916 x_msg_data,
1917 '_PVT'
1918 );
1919 END lock_row;
1920 */
1921 ---------------------------------------
1922 -- lock_row for:OKC_TIME_TPA_VALUE_V --
1923 ---------------------------------------
1924 PROCEDURE lock_row(
1925 p_api_version IN NUMBER,
1926 p_init_msg_list IN VARCHAR2,
1927 x_return_status OUT NOCOPY VARCHAR2,
1928 x_msg_count OUT NOCOPY NUMBER,
1929 x_msg_data OUT NOCOPY VARCHAR2,
1930 p_tavv_rec IN tavv_rec_type) IS
1931
1932 l_api_version CONSTANT NUMBER := 1;
1933 l_api_name CONSTANT VARCHAR2(30) := 'V_lock_row';
1934 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1935 l_tve_rec tve_rec_type;
1936 --Bug 3122962 l_okc_timevalues_tl_rec okc_timevalues_tl_rec_type;
1937 BEGIN
1938 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
1939 G_PKG_NAME,
1940 p_init_msg_list,
1941 l_api_version,
1942 p_api_version,
1943 '_PVT',
1944 x_return_status);
1945 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1946 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1947 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
1948 RAISE OKC_API.G_EXCEPTION_ERROR;
1949 END IF;
1950 --------------------------------------
1951 -- Move VIEW record to "Child" records
1952 --------------------------------------
1953 migrate(p_tavv_rec, l_tve_rec);
1954 --Bug 3122962 migrate(p_tavv_rec, l_okc_timevalues_tl_rec);
1955 --------------------------------------------
1956 -- Call the LOCK_ROW for each child record
1957 --------------------------------------------
1958 lock_row(
1959 p_init_msg_list,
1960 x_return_status,
1961 x_msg_count,
1962 x_msg_data,
1963 l_tve_rec
1964 );
1965 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1966 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1967 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1968 RAISE OKC_API.G_EXCEPTION_ERROR;
1969 END IF;
1970 /* lock_row(
1971 p_init_msg_list,
1972 x_return_status,
1973 x_msg_count,
1974 x_msg_data,
1975 l_okc_timevalues_tl_rec
1976 );
1977 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
1978 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1979 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
1980 RAISE OKC_API.G_EXCEPTION_ERROR;
1981 END IF;
1982 */
1983 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
1984 EXCEPTION
1985 WHEN OKC_API.G_EXCEPTION_ERROR THEN
1986 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1987 (
1988 l_api_name,
1989 G_PKG_NAME,
1990 'OKC_API.G_RET_STS_ERROR',
1991 x_msg_count,
1992 x_msg_data,
1993 '_PVT'
1994 );
1995 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1996 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
1997 (
1998 l_api_name,
1999 G_PKG_NAME,
2000 'OKC_API.G_RET_STS_UNEXP_ERROR',
2001 x_msg_count,
2002 x_msg_data,
2003 '_PVT'
2004 );
2005 WHEN OTHERS THEN
2006 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2007 (
2008 l_api_name,
2009 G_PKG_NAME,
2010 'OTHERS',
2011 x_msg_count,
2012 x_msg_data,
2013 '_PVT'
2014 );
2015 END lock_row;
2016 --------------------------------------
2017 -- PL/SQL TBL lock_row for:TAVV_TBL --
2018 --------------------------------------
2019 PROCEDURE lock_row(
2020 p_api_version IN NUMBER,
2021 p_init_msg_list IN VARCHAR2,
2022 x_return_status OUT NOCOPY VARCHAR2,
2023 x_msg_count OUT NOCOPY NUMBER,
2024 x_msg_data OUT NOCOPY VARCHAR2,
2025 p_tavv_tbl IN tavv_tbl_type) IS
2026
2027 l_api_version CONSTANT NUMBER := 1;
2028 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_lock_row';
2029 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2030 i NUMBER := 0;
2031 BEGIN
2032 -- Make sure PL/SQL table has records in it before passing
2033 IF (p_tavv_tbl.COUNT > 0) THEN
2034 i := p_tavv_tbl.FIRST;
2035 LOOP
2036 lock_row (
2037 p_api_version => p_api_version,
2038 p_init_msg_list => p_init_msg_list,
2039 x_return_status => x_return_status,
2040 x_msg_count => x_msg_count,
2041 x_msg_data => x_msg_data,
2042 p_tavv_rec => p_tavv_tbl(i));
2043 EXIT WHEN (i = p_tavv_tbl.LAST);
2044 i := p_tavv_tbl.NEXT(i);
2045 END LOOP;
2046 END IF;
2047 EXCEPTION
2048 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2049 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2050 (
2051 l_api_name,
2052 G_PKG_NAME,
2053 'OKC_API.G_RET_STS_ERROR',
2054 x_msg_count,
2055 x_msg_data,
2056 '_PVT'
2057 );
2058 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2059 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2060 (
2061 l_api_name,
2062 G_PKG_NAME,
2063 'OKC_API.G_RET_STS_UNEXP_ERROR',
2064 x_msg_count,
2065 x_msg_data,
2066 '_PVT'
2067 );
2068 WHEN OTHERS THEN
2069 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2070 (
2071 l_api_name,
2072 G_PKG_NAME,
2073 'OTHERS',
2074 x_msg_count,
2075 x_msg_data,
2076 '_PVT'
2077 );
2078 END lock_row;
2079
2080 ---------------------------------------------------------------------------
2081 -- PROCEDURE update_row
2082 ---------------------------------------------------------------------------
2083 -------------------------------------
2084 -- update_row for:OKC_TIMEVALUES_B --
2085 -------------------------------------
2086 PROCEDURE update_row(
2087 p_init_msg_list IN VARCHAR2,
2088 x_return_status OUT NOCOPY VARCHAR2,
2089 x_msg_count OUT NOCOPY NUMBER,
2090 x_msg_data OUT NOCOPY VARCHAR2,
2091 p_tve_rec IN tve_rec_type,
2092 x_tve_rec OUT NOCOPY tve_rec_type) IS
2093
2094 l_api_version CONSTANT NUMBER := 1;
2095 l_api_name CONSTANT VARCHAR2(30) := 'B_update_row';
2096 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2097 l_tve_rec tve_rec_type := p_tve_rec;
2098 l_def_tve_rec tve_rec_type;
2099 l_row_notfound BOOLEAN := TRUE;
2100 ----------------------------------
2101 -- FUNCTION populate_new_record --
2102 ----------------------------------
2103 FUNCTION populate_new_record (
2104 p_tve_rec IN tve_rec_type,
2105 x_tve_rec OUT NOCOPY tve_rec_type
2106 ) RETURN VARCHAR2 IS
2107 l_tve_rec tve_rec_type;
2108 l_row_notfound BOOLEAN := TRUE;
2109 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2110 BEGIN
2111 x_tve_rec := p_tve_rec;
2112 -- Get current database values
2113 l_tve_rec := get_rec(p_tve_rec, l_row_notfound);
2114 IF (l_row_notfound) THEN
2115 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2116 END IF;
2117 IF (x_tve_rec.id = OKC_API.G_MISS_NUM)
2118 THEN
2119 x_tve_rec.id := l_tve_rec.id;
2120 END IF;
2121 IF (x_tve_rec.spn_id = OKC_API.G_MISS_NUM)
2122 THEN
2123 x_tve_rec.spn_id := l_tve_rec.spn_id;
2124 END IF;
2125 IF (x_tve_rec.tve_id_offset = OKC_API.G_MISS_NUM)
2126 THEN
2127 x_tve_rec.tve_id_offset := l_tve_rec.tve_id_offset;
2128 END IF;
2129 IF (x_tve_rec.uom_code = OKC_API.G_MISS_CHAR)
2130 THEN
2131 x_tve_rec.uom_code := l_tve_rec.uom_code;
2132 END IF;
2133 IF (x_tve_rec.tve_id_generated_by = OKC_API.G_MISS_NUM)
2134 THEN
2135 x_tve_rec.tve_id_generated_by := l_tve_rec.tve_id_generated_by;
2136 END IF;
2137 IF (x_tve_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
2138 THEN
2139 x_tve_rec.dnz_chr_id := l_tve_rec.dnz_chr_id;
2140 END IF;
2141 IF (x_tve_rec.tze_id = OKC_API.G_MISS_NUM)
2142 THEN
2143 x_tve_rec.tze_id := l_tve_rec.tze_id;
2144 END IF;
2145 IF (x_tve_rec.tve_id_limited = OKC_API.G_MISS_NUM)
2146 THEN
2147 x_tve_rec.tve_id_limited := l_tve_rec.tve_id_limited;
2148 END IF;
2149 IF (x_tve_rec.tve_id_started = OKC_API.G_MISS_NUM)
2150 THEN
2151 x_tve_rec.tve_id_started := l_tve_rec.tve_id_started;
2152 END IF;
2153 IF (x_tve_rec.tve_id_ended = OKC_API.G_MISS_NUM)
2154 THEN
2155 x_tve_rec.tve_id_ended := l_tve_rec.tve_id_ended;
2156 END IF;
2157 IF (x_tve_rec.cnh_id = OKC_API.G_MISS_NUM)
2158 THEN
2159 x_tve_rec.cnh_id := l_tve_rec.cnh_id;
2160 END IF;
2161 IF (x_tve_rec.tve_type = OKC_API.G_MISS_CHAR)
2162 THEN
2163 x_tve_rec.tve_type := l_tve_rec.tve_type;
2164 END IF;
2165 IF (x_tve_rec.object_version_number = OKC_API.G_MISS_NUM)
2166 THEN
2167 x_tve_rec.object_version_number := l_tve_rec.object_version_number;
2168 END IF;
2169 IF (x_tve_rec.created_by = OKC_API.G_MISS_NUM)
2170 THEN
2171 x_tve_rec.created_by := l_tve_rec.created_by;
2172 END IF;
2173 IF (x_tve_rec.creation_date = OKC_API.G_MISS_DATE)
2174 THEN
2175 x_tve_rec.creation_date := l_tve_rec.creation_date;
2176 END IF;
2177 IF (x_tve_rec.last_updated_by = OKC_API.G_MISS_NUM)
2178 THEN
2179 x_tve_rec.last_updated_by := l_tve_rec.last_updated_by;
2180 END IF;
2181 IF (x_tve_rec.last_update_date = OKC_API.G_MISS_DATE)
2182 THEN
2183 x_tve_rec.last_update_date := l_tve_rec.last_update_date;
2184 END IF;
2185 IF (x_tve_rec.duration = OKC_API.G_MISS_NUM)
2186 THEN
2187 x_tve_rec.duration := l_tve_rec.duration;
2188 END IF;
2189 IF (x_tve_rec.operator = OKC_API.G_MISS_CHAR)
2190 THEN
2191 x_tve_rec.operator := l_tve_rec.operator;
2192 END IF;
2193 IF (x_tve_rec.before_after = OKC_API.G_MISS_CHAR)
2194 THEN
2195 x_tve_rec.before_after := l_tve_rec.before_after;
2196 END IF;
2197 IF (x_tve_rec.datetime = OKC_API.G_MISS_DATE)
2198 THEN
2199 x_tve_rec.datetime := l_tve_rec.datetime;
2200 END IF;
2201 IF (x_tve_rec.month = OKC_API.G_MISS_NUM)
2202 THEN
2203 x_tve_rec.month := l_tve_rec.month;
2204 END IF;
2205 IF (x_tve_rec.day = OKC_API.G_MISS_NUM)
2206 THEN
2207 x_tve_rec.day := l_tve_rec.day;
2208 END IF;
2209 IF (x_tve_rec.hour = OKC_API.G_MISS_NUM)
2210 THEN
2211 x_tve_rec.hour := l_tve_rec.hour;
2212 END IF;
2213 IF (x_tve_rec.minute = OKC_API.G_MISS_NUM)
2214 THEN
2215 x_tve_rec.minute := l_tve_rec.minute;
2216 END IF;
2217 IF (x_tve_rec.second = OKC_API.G_MISS_NUM)
2218 THEN
2219 x_tve_rec.second := l_tve_rec.second;
2220 END IF;
2221 IF (x_tve_rec.day_of_week = OKC_API.G_MISS_CHAR)
2222 THEN
2223 x_tve_rec.day_of_week := l_tve_rec.day_of_week;
2224 END IF;
2225 IF (x_tve_rec.interval_yn = OKC_API.G_MISS_CHAR)
2226 THEN
2227 x_tve_rec.interval_yn := l_tve_rec.interval_yn;
2228 END IF;
2229 IF (x_tve_rec.nth = OKC_API.G_MISS_NUM)
2230 THEN
2231 x_tve_rec.nth := l_tve_rec.nth;
2232 END IF;
2233 IF (x_tve_rec.last_update_login = OKC_API.G_MISS_NUM)
2234 THEN
2235 x_tve_rec.last_update_login := l_tve_rec.last_update_login;
2236 END IF;
2237 IF (x_tve_rec.attribute_category = OKC_API.G_MISS_CHAR)
2238 THEN
2239 x_tve_rec.attribute_category := l_tve_rec.attribute_category;
2240 END IF;
2241 IF (x_tve_rec.attribute1 = OKC_API.G_MISS_CHAR)
2242 THEN
2243 x_tve_rec.attribute1 := l_tve_rec.attribute1;
2244 END IF;
2245 IF (x_tve_rec.attribute2 = OKC_API.G_MISS_CHAR)
2246 THEN
2247 x_tve_rec.attribute2 := l_tve_rec.attribute2;
2248 END IF;
2249 IF (x_tve_rec.attribute3 = OKC_API.G_MISS_CHAR)
2250 THEN
2251 x_tve_rec.attribute3 := l_tve_rec.attribute3;
2252 END IF;
2253 IF (x_tve_rec.attribute4 = OKC_API.G_MISS_CHAR)
2254 THEN
2255 x_tve_rec.attribute4 := l_tve_rec.attribute4;
2256 END IF;
2257 IF (x_tve_rec.attribute5 = OKC_API.G_MISS_CHAR)
2258 THEN
2259 x_tve_rec.attribute5 := l_tve_rec.attribute5;
2260 END IF;
2261 IF (x_tve_rec.attribute6 = OKC_API.G_MISS_CHAR)
2262 THEN
2263 x_tve_rec.attribute6 := l_tve_rec.attribute6;
2264 END IF;
2265 IF (x_tve_rec.attribute7 = OKC_API.G_MISS_CHAR)
2266 THEN
2267 x_tve_rec.attribute7 := l_tve_rec.attribute7;
2268 END IF;
2269 IF (x_tve_rec.attribute8 = OKC_API.G_MISS_CHAR)
2270 THEN
2271 x_tve_rec.attribute8 := l_tve_rec.attribute8;
2272 END IF;
2273 IF (x_tve_rec.attribute9 = OKC_API.G_MISS_CHAR)
2274 THEN
2275 x_tve_rec.attribute9 := l_tve_rec.attribute9;
2276 END IF;
2277 IF (x_tve_rec.attribute10 = OKC_API.G_MISS_CHAR)
2278 THEN
2279 x_tve_rec.attribute10 := l_tve_rec.attribute10;
2280 END IF;
2281 IF (x_tve_rec.attribute11 = OKC_API.G_MISS_CHAR)
2282 THEN
2283 x_tve_rec.attribute11 := l_tve_rec.attribute11;
2284 END IF;
2285 IF (x_tve_rec.attribute12 = OKC_API.G_MISS_CHAR)
2286 THEN
2287 x_tve_rec.attribute12 := l_tve_rec.attribute12;
2288 END IF;
2289 IF (x_tve_rec.attribute13 = OKC_API.G_MISS_CHAR)
2290 THEN
2291 x_tve_rec.attribute13 := l_tve_rec.attribute13;
2292 END IF;
2293 IF (x_tve_rec.attribute14 = OKC_API.G_MISS_CHAR)
2294 THEN
2295 x_tve_rec.attribute14 := l_tve_rec.attribute14;
2296 END IF;
2297 IF (x_tve_rec.attribute15 = OKC_API.G_MISS_CHAR)
2298 THEN
2299 x_tve_rec.attribute15 := l_tve_rec.attribute15;
2300 END IF;
2301 --Bug 3122962
2302 IF (x_tve_rec.description = OKC_API.G_MISS_CHAR)
2303 THEN
2304 x_tve_rec.description := l_tve_rec.description;
2305 END IF;
2306 IF (x_tve_rec.short_description = OKC_API.G_MISS_CHAR)
2307 THEN
2308 x_tve_rec.short_description := l_tve_rec.short_description;
2309 END IF;
2310 IF (x_tve_rec.comments = OKC_API.G_MISS_CHAR)
2311 THEN
2312 x_tve_rec.comments := l_tve_rec.comments;
2313 END IF;
2314 IF (x_tve_rec.name = OKC_API.G_MISS_CHAR)
2315 THEN
2316 x_tve_rec.name := l_tve_rec.name;
2317 END IF;
2318
2319 RETURN(l_return_status);
2320 END populate_new_record;
2321 -----------------------------------------
2322 -- Set_Attributes for:OKC_TIMEVALUES_B --
2323 -----------------------------------------
2324 FUNCTION Set_Attributes (
2325 p_tve_rec IN tve_rec_type,
2326 x_tve_rec OUT NOCOPY tve_rec_type
2327 ) RETURN VARCHAR2 IS
2328 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2329 BEGIN
2330 x_tve_rec := p_tve_rec;
2331 RETURN(l_return_status);
2332 END Set_Attributes;
2333 BEGIN
2334 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2335 p_init_msg_list,
2336 '_PVT',
2337 x_return_status);
2338 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2339 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2340 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2341 RAISE OKC_API.G_EXCEPTION_ERROR;
2342 END IF;
2343 --- Setting item attributes
2344 l_return_status := Set_Attributes(
2345 p_tve_rec, -- IN
2346 l_tve_rec); -- OUT
2347 --- If any errors happen abort API
2348 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2349 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2350 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2351 RAISE OKC_API.G_EXCEPTION_ERROR;
2352 END IF;
2353 l_return_status := populate_new_record(l_tve_rec, l_def_tve_rec);
2354 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2355 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2356 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2357 RAISE OKC_API.G_EXCEPTION_ERROR;
2358 END IF;
2359 UPDATE OKC_TIMEVALUES
2360 SET SPN_ID = l_def_tve_rec.spn_id,
2361 TVE_ID_OFFSET = l_def_tve_rec.tve_id_offset,
2362 uom_code = l_def_tve_rec.uom_code,
2363 TVE_ID_GENERATED_BY = l_def_tve_rec.tve_id_generated_by,
2364 DNZ_CHR_ID = l_def_tve_rec.dnz_chr_id,
2365 TZE_ID = l_def_tve_rec.tze_id,
2366 TVE_ID_LIMITED = l_def_tve_rec.tve_id_limited,
2367 TVE_ID_STARTED = l_def_tve_rec.tve_id_started,
2368 TVE_ID_ENDED = l_def_tve_rec.tve_id_ended,
2369 CNH_ID = l_def_tve_rec.cnh_id,
2370 TVE_TYPE = l_def_tve_rec.tve_type,
2371 OBJECT_VERSION_NUMBER = l_def_tve_rec.object_version_number,
2372 CREATED_BY = l_def_tve_rec.created_by,
2373 CREATION_DATE = l_def_tve_rec.creation_date,
2374 LAST_UPDATED_BY = l_def_tve_rec.last_updated_by,
2375 LAST_UPDATE_DATE = l_def_tve_rec.last_update_date,
2376 DURATION = l_def_tve_rec.duration,
2377 OPERATOR = l_def_tve_rec.operator,
2378 BEFORE_AFTER = l_def_tve_rec.before_after,
2379 DATETIME = l_def_tve_rec.datetime,
2380 MONTH = l_def_tve_rec.month,
2381 DAY = l_def_tve_rec.day,
2382 HOUR = l_def_tve_rec.hour,
2383 MINUTE = l_def_tve_rec.minute,
2384 SECOND = l_def_tve_rec.second,
2385 INTERVAL_YN = l_def_tve_rec.interval_yn,
2386 NTH = l_def_tve_rec.nth,
2387 DAY_OF_WEEK = l_def_tve_rec.day_of_week,
2388 LAST_UPDATE_LOGIN = l_def_tve_rec.last_update_login,
2389 ATTRIBUTE_CATEGORY = l_def_tve_rec.attribute_category,
2390 --Bug 3122962
2391 DESCRIPTION = l_def_tve_rec.description,
2392 SHORT_DESCRIPTION = l_def_tve_rec.short_description,
2393 COMMENTS = l_def_tve_rec.comments,
2394 NAME = l_def_tve_rec.name,
2395
2396 ATTRIBUTE1 = l_def_tve_rec.attribute1,
2397 ATTRIBUTE2 = l_def_tve_rec.attribute2,
2398 ATTRIBUTE3 = l_def_tve_rec.attribute3,
2399 ATTRIBUTE4 = l_def_tve_rec.attribute4,
2400 ATTRIBUTE5 = l_def_tve_rec.attribute5,
2401 ATTRIBUTE6 = l_def_tve_rec.attribute6,
2402 ATTRIBUTE7 = l_def_tve_rec.attribute7,
2403 ATTRIBUTE8 = l_def_tve_rec.attribute8,
2404 ATTRIBUTE9 = l_def_tve_rec.attribute9,
2405 ATTRIBUTE10 = l_def_tve_rec.attribute10,
2406 ATTRIBUTE11 = l_def_tve_rec.attribute11,
2407 ATTRIBUTE12 = l_def_tve_rec.attribute12,
2408 ATTRIBUTE13 = l_def_tve_rec.attribute13,
2409 ATTRIBUTE14 = l_def_tve_rec.attribute14,
2410 ATTRIBUTE15 = l_def_tve_rec.attribute15
2411 WHERE ID = l_def_tve_rec.id;
2412 x_tve_rec := l_def_tve_rec;
2413 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2414 EXCEPTION
2415 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2416 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2417 (
2418 l_api_name,
2419 G_PKG_NAME,
2420 'OKC_API.G_RET_STS_ERROR',
2421 x_msg_count,
2422 x_msg_data,
2423 '_PVT'
2424 );
2425 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2426 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2427 (
2428 l_api_name,
2429 G_PKG_NAME,
2430 'OKC_API.G_RET_STS_UNEXP_ERROR',
2431 x_msg_count,
2432 x_msg_data,
2433 '_PVT'
2434 );
2435 WHEN OTHERS THEN
2436 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2437 (
2438 l_api_name,
2439 G_PKG_NAME,
2440 'OTHERS',
2441 x_msg_count,
2442 x_msg_data,
2443 '_PVT'
2444 );
2445 END update_row;
2446 --------------------------------------
2447 -- update_row for:OKC_TIMEVALUES_TL --
2448 --------------------------------------
2449 /* PROCEDURE update_row(
2450 p_init_msg_list IN VARCHAR2,
2451 x_return_status OUT NOCOPY VARCHAR2,
2452 x_msg_count OUT NOCOPY NUMBER,
2453 x_msg_data OUT NOCOPY VARCHAR2,
2454 p_okc_timevalues_tl_rec IN okc_timevalues_tl_rec_type,
2455 x_okc_timevalues_tl_rec OUT NOCOPY okc_timevalues_tl_rec_type) IS
2456
2457 l_api_version CONSTANT NUMBER := 1;
2458 l_api_name CONSTANT VARCHAR2(30) := 'TL_update_row';
2459 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2460 l_okc_timevalues_tl_rec okc_timevalues_tl_rec_type := p_okc_timevalues_tl_rec;
2461 l_def_okc_timevalues_tl_rec okc_timevalues_tl_rec_type;
2462 l_row_notfound BOOLEAN := TRUE;
2463 ----------------------------------
2464 -- FUNCTION populate_new_record --
2465 ----------------------------------
2466 FUNCTION populate_new_record (
2467 p_okc_timevalues_tl_rec IN okc_timevalues_tl_rec_type,
2468 x_okc_timevalues_tl_rec OUT NOCOPY okc_timevalues_tl_rec_type
2469 ) RETURN VARCHAR2 IS
2470 l_okc_timevalues_tl_rec okc_timevalues_tl_rec_type;
2471 l_row_notfound BOOLEAN := TRUE;
2472 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2473 BEGIN
2474 x_okc_timevalues_tl_rec := p_okc_timevalues_tl_rec;
2475 -- Get current database values
2476 l_okc_timevalues_tl_rec := get_rec(p_okc_timevalues_tl_rec, l_row_notfound);
2477 IF (l_row_notfound) THEN
2478 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2479 END IF;
2480 IF (x_okc_timevalues_tl_rec.id = OKC_API.G_MISS_NUM)
2481 THEN
2482 x_okc_timevalues_tl_rec.id := l_okc_timevalues_tl_rec.id;
2483 END IF;
2484 IF (x_okc_timevalues_tl_rec.language = OKC_API.G_MISS_CHAR)
2485 THEN
2486 x_okc_timevalues_tl_rec.language := l_okc_timevalues_tl_rec.language;
2487 END IF;
2488 IF (x_okc_timevalues_tl_rec.source_lang = OKC_API.G_MISS_CHAR)
2489 THEN
2490 x_okc_timevalues_tl_rec.source_lang := l_okc_timevalues_tl_rec.source_lang;
2491 END IF;
2492 IF (x_okc_timevalues_tl_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
2493 THEN
2494 x_okc_timevalues_tl_rec.sfwt_flag := l_okc_timevalues_tl_rec.sfwt_flag;
2495 END IF;
2496 IF (x_okc_timevalues_tl_rec.description = OKC_API.G_MISS_CHAR)
2497 THEN
2498 x_okc_timevalues_tl_rec.description := l_okc_timevalues_tl_rec.description;
2499 END IF;
2500 IF (x_okc_timevalues_tl_rec.short_description = OKC_API.G_MISS_CHAR)
2501 THEN
2502 x_okc_timevalues_tl_rec.short_description := l_okc_timevalues_tl_rec.short_description;
2503 END IF;
2504 IF (x_okc_timevalues_tl_rec.comments = OKC_API.G_MISS_CHAR)
2505 THEN
2506 x_okc_timevalues_tl_rec.comments := l_okc_timevalues_tl_rec.comments;
2507 END IF;
2508 IF (x_okc_timevalues_tl_rec.name = OKC_API.G_MISS_CHAR)
2509 THEN
2510 x_okc_timevalues_tl_rec.name := l_okc_timevalues_tl_rec.name;
2511 END IF;
2512 IF (x_okc_timevalues_tl_rec.created_by = OKC_API.G_MISS_NUM)
2513 THEN
2514 x_okc_timevalues_tl_rec.created_by := l_okc_timevalues_tl_rec.created_by;
2515 END IF;
2516 IF (x_okc_timevalues_tl_rec.creation_date = OKC_API.G_MISS_DATE)
2517 THEN
2518 x_okc_timevalues_tl_rec.creation_date := l_okc_timevalues_tl_rec.creation_date;
2519 END IF;
2520 IF (x_okc_timevalues_tl_rec.last_updated_by = OKC_API.G_MISS_NUM)
2521 THEN
2522 x_okc_timevalues_tl_rec.last_updated_by := l_okc_timevalues_tl_rec.last_updated_by;
2523 END IF;
2524 IF (x_okc_timevalues_tl_rec.last_update_date = OKC_API.G_MISS_DATE)
2525 THEN
2526 x_okc_timevalues_tl_rec.last_update_date := l_okc_timevalues_tl_rec.last_update_date;
2527 END IF;
2528 IF (x_okc_timevalues_tl_rec.last_update_login = OKC_API.G_MISS_NUM)
2529 THEN
2530 x_okc_timevalues_tl_rec.last_update_login := l_okc_timevalues_tl_rec.last_update_login;
2531 END IF;
2532 RETURN(l_return_status);
2533 END populate_new_record;
2534 ------------------------------------------
2535 -- Set_Attributes for:OKC_TIMEVALUES_TL --
2536 ------------------------------------------
2537 FUNCTION Set_Attributes (
2538 p_okc_timevalues_tl_rec IN okc_timevalues_tl_rec_type,
2539 x_okc_timevalues_tl_rec OUT NOCOPY okc_timevalues_tl_rec_type
2540 ) RETURN VARCHAR2 IS
2541 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2542 BEGIN
2543 x_okc_timevalues_tl_rec := p_okc_timevalues_tl_rec;
2544 x_okc_timevalues_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
2545 x_okc_timevalues_tl_rec.SOURCE_LANG := okc_util.get_userenv_lang;
2546 RETURN(l_return_status);
2547 END Set_Attributes;
2548 BEGIN
2549 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2550 p_init_msg_list,
2551 '_PVT',
2552 x_return_status);
2553 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2554 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2555 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2556 RAISE OKC_API.G_EXCEPTION_ERROR;
2557 END IF;
2558 --- Setting item attributes
2559 l_return_status := Set_Attributes(
2560 p_okc_timevalues_tl_rec, -- IN
2561 l_okc_timevalues_tl_rec); -- OUT
2562 --- If any errors happen abort API
2563 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2564 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2565 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2566 RAISE OKC_API.G_EXCEPTION_ERROR;
2567 END IF;
2568 l_return_status := populate_new_record(l_okc_timevalues_tl_rec, l_def_okc_timevalues_tl_rec);
2569 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2570 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2571 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2572 RAISE OKC_API.G_EXCEPTION_ERROR;
2573 END IF;
2574 UPDATE OKC_TIMEVALUES_TL
2575 SET DESCRIPTION = l_def_okc_timevalues_tl_rec.description,
2576 SHORT_DESCRIPTION = l_def_okc_timevalues_tl_rec.short_description,
2577 COMMENTS = l_def_okc_timevalues_tl_rec.comments,
2578 NAME = l_def_okc_timevalues_tl_rec.name,
2579 CREATED_BY = l_def_okc_timevalues_tl_rec.created_by,
2580 CREATION_DATE = l_def_okc_timevalues_tl_rec.creation_date,
2581 LAST_UPDATED_BY = l_def_okc_timevalues_tl_rec.last_updated_by,
2582 LAST_UPDATE_DATE = l_def_okc_timevalues_tl_rec.last_update_date,
2583 LAST_UPDATE_LOGIN = l_def_okc_timevalues_tl_rec.last_update_login
2584 WHERE ID = l_def_okc_timevalues_tl_rec.id
2585 AND USERENV('LANG') IN (SOURCE_LANG,LANGUAGE);
2586
2587 UPDATE OKC_TIMEVALUES_TL
2588 SET SFWT_FLAG = 'Y'
2589 WHERE ID = l_def_okc_timevalues_tl_rec.id
2590 AND SOURCE_LANG <> USERENV('LANG');
2591
2592 x_okc_timevalues_tl_rec := l_def_okc_timevalues_tl_rec;
2593 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2594 EXCEPTION
2595 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2596 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2597 (
2598 l_api_name,
2599 G_PKG_NAME,
2600 'OKC_API.G_RET_STS_ERROR',
2601 x_msg_count,
2602 x_msg_data,
2603 '_PVT'
2604 );
2605 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2606 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2607 (
2608 l_api_name,
2609 G_PKG_NAME,
2610 'OKC_API.G_RET_STS_UNEXP_ERROR',
2611 x_msg_count,
2612 x_msg_data,
2613 '_PVT'
2614 );
2615 WHEN OTHERS THEN
2616 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2617 (
2618 l_api_name,
2619 G_PKG_NAME,
2620 'OTHERS',
2621 x_msg_count,
2622 x_msg_data,
2623 '_PVT'
2624 );
2625 END update_row;
2626 */
2627 -----------------------------------------
2628 -- update_row for:OKC_TIME_TPA_VALUE_V --
2629 -----------------------------------------
2630 PROCEDURE update_row(
2631 p_api_version IN NUMBER,
2632 p_init_msg_list IN VARCHAR2,
2633 x_return_status OUT NOCOPY VARCHAR2,
2634 x_msg_count OUT NOCOPY NUMBER,
2635 x_msg_data OUT NOCOPY VARCHAR2,
2636 p_tavv_rec IN tavv_rec_type,
2637 x_tavv_rec OUT NOCOPY tavv_rec_type) IS
2638
2639 l_api_version CONSTANT NUMBER := 1;
2640 l_api_name CONSTANT VARCHAR2(30) := 'V_update_row';
2641 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2642 l_tavv_rec tavv_rec_type := p_tavv_rec;
2643 l_def_tavv_rec tavv_rec_type;
2644 --Bug 3122962 l_okc_timevalues_tl_rec okc_timevalues_tl_rec_type;
2645 --Bug 3122962 lx_okc_timevalues_tl_rec okc_timevalues_tl_rec_type;
2646 l_tve_rec tve_rec_type;
2647 lx_tve_rec tve_rec_type;
2648 -------------------------------
2649 -- FUNCTION fill_who_columns --
2650 -------------------------------
2651 FUNCTION fill_who_columns (
2652 p_tavv_rec IN tavv_rec_type
2653 ) RETURN tavv_rec_type IS
2654 l_tavv_rec tavv_rec_type := p_tavv_rec;
2655 BEGIN
2656 l_tavv_rec.LAST_UPDATE_DATE := SYSDATE;
2657 l_tavv_rec.LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
2658 l_tavv_rec.LAST_UPDATE_LOGIN := FND_GLOBAL.LOGIN_ID;
2659 RETURN(l_tavv_rec);
2660 END fill_who_columns;
2661 ----------------------------------
2662 -- FUNCTION populate_new_record --
2663 ----------------------------------
2664 FUNCTION populate_new_record (
2665 p_tavv_rec IN tavv_rec_type,
2666 x_tavv_rec OUT NOCOPY tavv_rec_type
2667 ) RETURN VARCHAR2 IS
2668 l_tavv_rec tavv_rec_type;
2669 l_row_notfound BOOLEAN := TRUE;
2670 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2671 BEGIN
2672 x_tavv_rec := p_tavv_rec;
2673 -- Get current database values
2674 l_tavv_rec := get_rec(p_tavv_rec, l_row_notfound);
2675 IF (l_row_notfound) THEN
2676 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2677 END IF;
2678 IF (x_tavv_rec.id = OKC_API.G_MISS_NUM)
2679 THEN
2680 x_tavv_rec.id := l_tavv_rec.id;
2681 END IF;
2682 IF (x_tavv_rec.object_version_number = OKC_API.G_MISS_NUM)
2683 THEN
2684 x_tavv_rec.object_version_number := l_tavv_rec.object_version_number;
2685 END IF;
2686 --Bug 3122962
2687 /* IF (x_tavv_rec.sfwt_flag = OKC_API.G_MISS_CHAR)
2688 THEN
2689 x_tavv_rec.sfwt_flag := l_tavv_rec.sfwt_flag;
2690 END IF;
2691 */
2692 IF (x_tavv_rec.spn_id = OKC_API.G_MISS_NUM)
2693 THEN
2694 x_tavv_rec.spn_id := l_tavv_rec.spn_id;
2695 END IF;
2696 IF (x_tavv_rec.tve_id_generated_by = OKC_API.G_MISS_NUM)
2697 THEN
2698 x_tavv_rec.tve_id_generated_by := l_tavv_rec.tve_id_generated_by;
2699 END IF;
2700 IF (x_tavv_rec.dnz_chr_id = OKC_API.G_MISS_NUM)
2701 THEN
2702 x_tavv_rec.dnz_chr_id := l_tavv_rec.dnz_chr_id;
2703 END IF;
2704 IF (x_tavv_rec.tze_id = OKC_API.G_MISS_NUM)
2705 THEN
2706 x_tavv_rec.tze_id := l_tavv_rec.tze_id;
2707 END IF;
2708 IF (x_tavv_rec.tve_id_limited = OKC_API.G_MISS_NUM)
2709 THEN
2710 x_tavv_rec.tve_id_limited := l_tavv_rec.tve_id_limited;
2711 END IF;
2712 IF (x_tavv_rec.description = OKC_API.G_MISS_CHAR)
2713 THEN
2714 x_tavv_rec.description := l_tavv_rec.description;
2715 END IF;
2716 IF (x_tavv_rec.short_description = OKC_API.G_MISS_CHAR)
2717 THEN
2718 x_tavv_rec.short_description := l_tavv_rec.short_description;
2719 END IF;
2720 IF (x_tavv_rec.comments = OKC_API.G_MISS_CHAR)
2721 THEN
2722 x_tavv_rec.comments := l_tavv_rec.comments;
2723 END IF;
2724 IF (x_tavv_rec.datetime = OKC_API.G_MISS_DATE)
2725 THEN
2726 x_tavv_rec.datetime := l_tavv_rec.datetime;
2727 END IF;
2728 IF (x_tavv_rec.attribute_category = OKC_API.G_MISS_CHAR)
2729 THEN
2730 x_tavv_rec.attribute_category := l_tavv_rec.attribute_category;
2731 END IF;
2732 IF (x_tavv_rec.attribute1 = OKC_API.G_MISS_CHAR)
2733 THEN
2734 x_tavv_rec.attribute1 := l_tavv_rec.attribute1;
2735 END IF;
2736 IF (x_tavv_rec.attribute2 = OKC_API.G_MISS_CHAR)
2737 THEN
2738 x_tavv_rec.attribute2 := l_tavv_rec.attribute2;
2739 END IF;
2740 IF (x_tavv_rec.attribute3 = OKC_API.G_MISS_CHAR)
2741 THEN
2742 x_tavv_rec.attribute3 := l_tavv_rec.attribute3;
2743 END IF;
2744 IF (x_tavv_rec.attribute4 = OKC_API.G_MISS_CHAR)
2745 THEN
2746 x_tavv_rec.attribute4 := l_tavv_rec.attribute4;
2747 END IF;
2748 IF (x_tavv_rec.attribute5 = OKC_API.G_MISS_CHAR)
2749 THEN
2750 x_tavv_rec.attribute5 := l_tavv_rec.attribute5;
2751 END IF;
2752 IF (x_tavv_rec.attribute6 = OKC_API.G_MISS_CHAR)
2753 THEN
2754 x_tavv_rec.attribute6 := l_tavv_rec.attribute6;
2755 END IF;
2756 IF (x_tavv_rec.attribute7 = OKC_API.G_MISS_CHAR)
2757 THEN
2758 x_tavv_rec.attribute7 := l_tavv_rec.attribute7;
2759 END IF;
2760 IF (x_tavv_rec.attribute8 = OKC_API.G_MISS_CHAR)
2761 THEN
2762 x_tavv_rec.attribute8 := l_tavv_rec.attribute8;
2763 END IF;
2764 IF (x_tavv_rec.attribute9 = OKC_API.G_MISS_CHAR)
2765 THEN
2766 x_tavv_rec.attribute9 := l_tavv_rec.attribute9;
2767 END IF;
2768 IF (x_tavv_rec.attribute10 = OKC_API.G_MISS_CHAR)
2769 THEN
2770 x_tavv_rec.attribute10 := l_tavv_rec.attribute10;
2771 END IF;
2772 IF (x_tavv_rec.attribute11 = OKC_API.G_MISS_CHAR)
2773 THEN
2774 x_tavv_rec.attribute11 := l_tavv_rec.attribute11;
2775 END IF;
2776 IF (x_tavv_rec.attribute12 = OKC_API.G_MISS_CHAR)
2777 THEN
2778 x_tavv_rec.attribute12 := l_tavv_rec.attribute12;
2779 END IF;
2780 IF (x_tavv_rec.attribute13 = OKC_API.G_MISS_CHAR)
2781 THEN
2782 x_tavv_rec.attribute13 := l_tavv_rec.attribute13;
2783 END IF;
2784 IF (x_tavv_rec.attribute14 = OKC_API.G_MISS_CHAR)
2785 THEN
2786 x_tavv_rec.attribute14 := l_tavv_rec.attribute14;
2787 END IF;
2788 IF (x_tavv_rec.attribute15 = OKC_API.G_MISS_CHAR)
2789 THEN
2790 x_tavv_rec.attribute15 := l_tavv_rec.attribute15;
2791 END IF;
2792 IF (x_tavv_rec.created_by = OKC_API.G_MISS_NUM)
2793 THEN
2794 x_tavv_rec.created_by := l_tavv_rec.created_by;
2795 END IF;
2796 IF (x_tavv_rec.creation_date = OKC_API.G_MISS_DATE)
2797 THEN
2798 x_tavv_rec.creation_date := l_tavv_rec.creation_date;
2799 END IF;
2800 IF (x_tavv_rec.last_updated_by = OKC_API.G_MISS_NUM)
2801 THEN
2802 x_tavv_rec.last_updated_by := l_tavv_rec.last_updated_by;
2803 END IF;
2804 IF (x_tavv_rec.last_update_date = OKC_API.G_MISS_DATE)
2805 THEN
2806 x_tavv_rec.last_update_date := l_tavv_rec.last_update_date;
2807 END IF;
2808 IF (x_tavv_rec.last_update_login = OKC_API.G_MISS_NUM)
2809 THEN
2810 x_tavv_rec.last_update_login := l_tavv_rec.last_update_login;
2811 END IF;
2812 RETURN(l_return_status);
2813 END populate_new_record;
2814 ---------------------------------------------
2815 -- Set_Attributes for:OKC_TIME_TPA_VALUE_V --
2816 ---------------------------------------------
2817 FUNCTION Set_Attributes (
2818 p_tavv_rec IN tavv_rec_type,
2819 x_tavv_rec OUT NOCOPY tavv_rec_type
2820 ) RETURN VARCHAR2 IS
2821 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2822 BEGIN
2823 x_tavv_rec := p_tavv_rec;
2824 -- **** Added the following line(s) for uppercasing *****
2825 -- x_tavv_rec.SFWT_FLAG := upper(p_tavv_rec.SFWT_FLAG);
2826 x_tavv_rec.OBJECT_VERSION_NUMBER := NVL(x_tavv_rec.OBJECT_VERSION_NUMBER, 0) + 1;
2827 RETURN(l_return_status);
2828 END Set_Attributes;
2829 BEGIN
2830 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
2831 G_PKG_NAME,
2832 p_init_msg_list,
2833 l_api_version,
2834 p_api_version,
2835 '_PVT',
2836 x_return_status);
2837 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2838 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2839 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2840 RAISE OKC_API.G_EXCEPTION_ERROR;
2841 END IF;
2842 --- Setting item attributes
2843 l_return_status := Set_Attributes(
2844 p_tavv_rec, -- IN
2845 l_tavv_rec); -- OUT
2846 --- If any errors happen abort API
2847 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2848 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2849 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2850 RAISE OKC_API.G_EXCEPTION_ERROR;
2851 END IF;
2852 l_return_status := populate_new_record(l_tavv_rec, l_def_tavv_rec);
2853 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2854 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2855 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2856 RAISE OKC_API.G_EXCEPTION_ERROR;
2857 END IF;
2858 l_def_tavv_rec := fill_who_columns(l_def_tavv_rec);
2859 --- Validate all non-missing attributes (Item Level Validation)
2860 l_return_status := Validate_Attributes(l_def_tavv_rec);
2861 --- If any errors happen abort API
2862 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2863 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2864 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2865 RAISE OKC_API.G_EXCEPTION_ERROR;
2866 END IF;
2867 l_return_status := Validate_Record(l_def_tavv_rec);
2868 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2869 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2870 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
2871 RAISE OKC_API.G_EXCEPTION_ERROR;
2872 END IF;
2873
2874 --------------------------------------
2875 -- Move VIEW record to "Child" records
2876 --------------------------------------
2877 --Bug 3122962 migrate(l_def_tavv_rec, l_okc_timevalues_tl_rec);
2878 migrate(l_def_tavv_rec, l_tve_rec);
2879 --------------------------------------------
2880 -- Call the UPDATE_ROW for each child record
2881 --------------------------------------------
2882 /* update_row(
2883 p_init_msg_list,
2884 x_return_status,
2885 x_msg_count,
2886 x_msg_data,
2887 l_okc_timevalues_tl_rec,
2888 lx_okc_timevalues_tl_rec
2889 );
2890 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2891 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2892 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2893 RAISE OKC_API.G_EXCEPTION_ERROR;
2894 END IF;
2895 migrate(lx_okc_timevalues_tl_rec, l_def_tavv_rec);
2896 */
2897 update_row(
2898 p_init_msg_list,
2899 x_return_status,
2900 x_msg_count,
2901 x_msg_data,
2902 l_tve_rec,
2903 lx_tve_rec
2904 );
2905 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
2906 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2907 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
2908 RAISE OKC_API.G_EXCEPTION_ERROR;
2909 END IF;
2910 migrate(lx_tve_rec, l_def_tavv_rec);
2911 x_tavv_rec := l_def_tavv_rec;
2912 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
2913 EXCEPTION
2914 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2915 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2916 (
2917 l_api_name,
2918 G_PKG_NAME,
2919 'OKC_API.G_RET_STS_ERROR',
2920 x_msg_count,
2921 x_msg_data,
2922 '_PVT'
2923 );
2924 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2925 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2926 (
2927 l_api_name,
2928 G_PKG_NAME,
2929 'OKC_API.G_RET_STS_UNEXP_ERROR',
2930 x_msg_count,
2931 x_msg_data,
2932 '_PVT'
2933 );
2934 WHEN OTHERS THEN
2935 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2936 (
2937 l_api_name,
2938 G_PKG_NAME,
2939 'OTHERS',
2940 x_msg_count,
2941 x_msg_data,
2942 '_PVT'
2943 );
2944 END update_row;
2945 ----------------------------------------
2946 -- PL/SQL TBL update_row for:TAVV_TBL --
2947 ----------------------------------------
2948 PROCEDURE update_row(
2949 p_api_version IN NUMBER,
2950 p_init_msg_list IN VARCHAR2,
2951 x_return_status OUT NOCOPY VARCHAR2,
2952 x_msg_count OUT NOCOPY NUMBER,
2953 x_msg_data OUT NOCOPY VARCHAR2,
2954 p_tavv_tbl IN tavv_tbl_type,
2955 x_tavv_tbl OUT NOCOPY tavv_tbl_type) IS
2956
2957 l_api_version CONSTANT NUMBER := 1;
2958 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_update_row';
2959 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
2960 i NUMBER := 0;
2961 BEGIN
2962 -- Make sure PL/SQL table has records in it before passing
2963 IF (p_tavv_tbl.COUNT > 0) THEN
2964 i := p_tavv_tbl.FIRST;
2965 LOOP
2966 update_row (
2967 p_api_version => p_api_version,
2968 p_init_msg_list => p_init_msg_list,
2969 x_return_status => x_return_status,
2970 x_msg_count => x_msg_count,
2971 x_msg_data => x_msg_data,
2972 p_tavv_rec => p_tavv_tbl(i),
2973 x_tavv_rec => x_tavv_tbl(i));
2974 EXIT WHEN (i = p_tavv_tbl.LAST);
2975 i := p_tavv_tbl.NEXT(i);
2976 END LOOP;
2977 END IF;
2978 EXCEPTION
2979 WHEN OKC_API.G_EXCEPTION_ERROR THEN
2980 x_return_status := OKC_API.HANDLE_EXCEPTIONS
2981 (
2982 l_api_name,
2983 G_PKG_NAME,
2984 'OKC_API.G_RET_STS_ERROR',
2985 x_msg_count,
2986 x_msg_data,
2987 '_PVT'
2988 );
2989 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2990 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
2991 (
2992 l_api_name,
2993 G_PKG_NAME,
2994 'OKC_API.G_RET_STS_UNEXP_ERROR',
2995 x_msg_count,
2996 x_msg_data,
2997 '_PVT'
2998 );
2999 WHEN OTHERS THEN
3000 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3001 (
3002 l_api_name,
3003 G_PKG_NAME,
3004 'OTHERS',
3005 x_msg_count,
3006 x_msg_data,
3007 '_PVT'
3008 );
3009 END update_row;
3010
3011 ---------------------------------------------------------------------------
3012 -- PROCEDURE delete_row
3013 ---------------------------------------------------------------------------
3014 -------------------------------------
3015 -- delete_row for:OKC_TIMEVALUES_B --
3016 -------------------------------------
3017 PROCEDURE delete_row(
3018 p_init_msg_list IN VARCHAR2,
3019 x_return_status OUT NOCOPY VARCHAR2,
3020 x_msg_count OUT NOCOPY NUMBER,
3021 x_msg_data OUT NOCOPY VARCHAR2,
3022 p_tve_rec IN tve_rec_type) IS
3023
3024 l_api_version CONSTANT NUMBER := 1;
3025 l_api_name CONSTANT VARCHAR2(30) := 'B_delete_row';
3026 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3027 l_tve_rec tve_rec_type:= p_tve_rec;
3028 l_row_notfound BOOLEAN := TRUE;
3029 BEGIN
3030 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3031 p_init_msg_list,
3032 '_PVT',
3033 x_return_status);
3034 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3035 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3036 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3037 RAISE OKC_API.G_EXCEPTION_ERROR;
3038 END IF;
3039 DELETE FROM OKC_TIMEVALUES
3040 WHERE ID = l_tve_rec.id;
3041
3042 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3043 EXCEPTION
3044 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3045 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3046 (
3047 l_api_name,
3048 G_PKG_NAME,
3049 'OKC_API.G_RET_STS_ERROR',
3050 x_msg_count,
3051 x_msg_data,
3052 '_PVT'
3053 );
3054 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3055 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3056 (
3057 l_api_name,
3058 G_PKG_NAME,
3059 'OKC_API.G_RET_STS_UNEXP_ERROR',
3060 x_msg_count,
3061 x_msg_data,
3062 '_PVT'
3063 );
3064 WHEN OTHERS THEN
3065 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3066 (
3067 l_api_name,
3068 G_PKG_NAME,
3069 'OTHERS',
3070 x_msg_count,
3071 x_msg_data,
3072 '_PVT'
3073 );
3074 END delete_row;
3075 --------------------------------------
3076 -- delete_row for:OKC_TIMEVALUES_TL --
3077 --------------------------------------
3078 /* PROCEDURE delete_row(
3079 p_init_msg_list IN VARCHAR2,
3080 x_return_status OUT NOCOPY VARCHAR2,
3081 x_msg_count OUT NOCOPY NUMBER,
3082 x_msg_data OUT NOCOPY VARCHAR2,
3083 p_okc_timevalues_tl_rec IN okc_timevalues_tl_rec_type) IS
3084
3085 l_api_version CONSTANT NUMBER := 1;
3086 l_api_name CONSTANT VARCHAR2(30) := 'TL_delete_row';
3087 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3088 l_okc_timevalues_tl_rec okc_timevalues_tl_rec_type:= p_okc_timevalues_tl_rec;
3089 l_row_notfound BOOLEAN := TRUE;
3090 ------------------------------------------
3091 -- Set_Attributes for:OKC_TIMEVALUES_TL --
3092 ------------------------------------------
3093 FUNCTION Set_Attributes (
3094 p_okc_timevalues_tl_rec IN okc_timevalues_tl_rec_type,
3095 x_okc_timevalues_tl_rec OUT NOCOPY okc_timevalues_tl_rec_type
3096 ) RETURN VARCHAR2 IS
3097 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3098 BEGIN
3099 x_okc_timevalues_tl_rec := p_okc_timevalues_tl_rec;
3100 x_okc_timevalues_tl_rec.LANGUAGE := okc_util.get_userenv_lang;
3101 RETURN(l_return_status);
3102 END Set_Attributes;
3103 BEGIN
3104 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3105 p_init_msg_list,
3106 '_PVT',
3107 x_return_status);
3108 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3109 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3110 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3111 RAISE OKC_API.G_EXCEPTION_ERROR;
3112 END IF;
3113 --- Setting item attributes
3114 l_return_status := Set_Attributes(
3115 p_okc_timevalues_tl_rec, -- IN
3116 l_okc_timevalues_tl_rec); -- OUT
3117 --- If any errors happen abort API
3118 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3119 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3120 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3121 RAISE OKC_API.G_EXCEPTION_ERROR;
3122 END IF;
3123 DELETE FROM OKC_TIMEVALUES_TL
3124 WHERE ID = l_okc_timevalues_tl_rec.id;
3125
3126 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3127 EXCEPTION
3128 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3129 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3130 (
3131 l_api_name,
3132 G_PKG_NAME,
3133 'OKC_API.G_RET_STS_ERROR',
3134 x_msg_count,
3135 x_msg_data,
3136 '_PVT'
3137 );
3138 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3139 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3140 (
3141 l_api_name,
3142 G_PKG_NAME,
3143 'OKC_API.G_RET_STS_UNEXP_ERROR',
3144 x_msg_count,
3145 x_msg_data,
3146 '_PVT'
3147 );
3148 WHEN OTHERS THEN
3149 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3150 (
3151 l_api_name,
3152 G_PKG_NAME,
3153 'OTHERS',
3154 x_msg_count,
3155 x_msg_data,
3156 '_PVT'
3157 );
3158 END delete_row;
3159 */
3160 -----------------------------------------
3161 -- delete_row for:OKC_TIME_TPA_VALUE_V --
3162 -----------------------------------------
3163 PROCEDURE delete_row(
3164 p_api_version IN NUMBER,
3165 p_init_msg_list IN VARCHAR2,
3166 x_return_status OUT NOCOPY VARCHAR2,
3167 x_msg_count OUT NOCOPY NUMBER,
3168 x_msg_data OUT NOCOPY VARCHAR2,
3169 p_tavv_rec IN tavv_rec_type) IS
3170
3171 l_api_version CONSTANT NUMBER := 1;
3172 l_api_name CONSTANT VARCHAR2(30) := 'V_delete_row';
3173 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3174 l_tavv_rec tavv_rec_type := p_tavv_rec;
3175 --Bug 3122962 l_okc_timevalues_tl_rec okc_timevalues_tl_rec_type;
3176 l_tve_rec tve_rec_type;
3177 BEGIN
3178 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
3179 G_PKG_NAME,
3180 p_init_msg_list,
3181 l_api_version,
3182 p_api_version,
3183 '_PVT',
3184 x_return_status);
3185 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3186 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3187 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
3188 RAISE OKC_API.G_EXCEPTION_ERROR;
3189 END IF;
3190 --------------------------------------
3191 -- Move VIEW record to "Child" records
3192 --------------------------------------
3193 --Bug 3122962 migrate(l_tavv_rec, l_okc_timevalues_tl_rec);
3194 migrate(l_tavv_rec, l_tve_rec);
3195 --------------------------------------------
3196 -- Call the DELETE_ROW for each child record
3197 --------------------------------------------
3198 /* delete_row(
3199 p_init_msg_list,
3200 x_return_status,
3201 x_msg_count,
3202 x_msg_data,
3203 l_okc_timevalues_tl_rec
3204 );
3205 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3206 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3207 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3208 RAISE OKC_API.G_EXCEPTION_ERROR;
3209 END IF;
3210 */
3211 delete_row(
3212 p_init_msg_list,
3213 x_return_status,
3214 x_msg_count,
3215 x_msg_data,
3216 l_tve_rec
3217 );
3218 IF (x_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
3219 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3220 ELSIF (x_return_status = OKC_API.G_RET_STS_ERROR) THEN
3221 RAISE OKC_API.G_EXCEPTION_ERROR;
3222 END IF;
3223 OKC_API.END_ACTIVITY(x_msg_count, x_msg_data);
3224 EXCEPTION
3225 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3226 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3227 (
3228 l_api_name,
3229 G_PKG_NAME,
3230 'OKC_API.G_RET_STS_ERROR',
3231 x_msg_count,
3232 x_msg_data,
3233 '_PVT'
3234 );
3235 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3236 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3237 (
3238 l_api_name,
3239 G_PKG_NAME,
3240 'OKC_API.G_RET_STS_UNEXP_ERROR',
3241 x_msg_count,
3242 x_msg_data,
3243 '_PVT'
3244 );
3245 WHEN OTHERS THEN
3246 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3247 (
3248 l_api_name,
3249 G_PKG_NAME,
3250 'OTHERS',
3251 x_msg_count,
3252 x_msg_data,
3253 '_PVT'
3254 );
3255 END delete_row;
3256 ----------------------------------------
3257 -- PL/SQL TBL delete_row for:TAVV_TBL --
3258 ----------------------------------------
3259 PROCEDURE delete_row(
3260 p_api_version IN NUMBER,
3261 p_init_msg_list IN VARCHAR2,
3262 x_return_status OUT NOCOPY VARCHAR2,
3263 x_msg_count OUT NOCOPY NUMBER,
3264 x_msg_data OUT NOCOPY VARCHAR2,
3265 p_tavv_tbl IN tavv_tbl_type) IS
3266
3267 l_api_version CONSTANT NUMBER := 1;
3268 l_api_name CONSTANT VARCHAR2(30) := 'V_tbl_delete_row';
3269 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3270 i NUMBER := 0;
3271 BEGIN
3272 -- Make sure PL/SQL table has records in it before passing
3273 IF (p_tavv_tbl.COUNT > 0) THEN
3274 i := p_tavv_tbl.FIRST;
3275 LOOP
3276 delete_row (
3277 p_api_version => p_api_version,
3278 p_init_msg_list => p_init_msg_list,
3279 x_return_status => x_return_status,
3280 x_msg_count => x_msg_count,
3281 x_msg_data => x_msg_data,
3282 p_tavv_rec => p_tavv_tbl(i));
3283 EXIT WHEN (i = p_tavv_tbl.LAST);
3284 i := p_tavv_tbl.NEXT(i);
3285 END LOOP;
3286 END IF;
3287 EXCEPTION
3288 WHEN OKC_API.G_EXCEPTION_ERROR THEN
3289 x_return_status := OKC_API.HANDLE_EXCEPTIONS
3290 (
3291 l_api_name,
3292 G_PKG_NAME,
3293 'OKC_API.G_RET_STS_ERROR',
3294 x_msg_count,
3295 x_msg_data,
3296 '_PVT'
3297 );
3298 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3299 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3300 (
3301 l_api_name,
3302 G_PKG_NAME,
3303 'OKC_API.G_RET_STS_UNEXP_ERROR',
3304 x_msg_count,
3305 x_msg_data,
3306 '_PVT'
3307 );
3308 WHEN OTHERS THEN
3309 x_return_status :=OKC_API.HANDLE_EXCEPTIONS
3310 (
3311 l_api_name,
3312 G_PKG_NAME,
3313 'OTHERS',
3314 x_msg_count,
3315 x_msg_data,
3316 '_PVT'
3317 );
3318 END delete_row;
3319
3320 --This function is called from versioning API OKC_VERSION_PVT
3321 --Old Location: OKCRVERB.pls
3322 --New Location: Base Table API
3323
3324 FUNCTION create_version(
3325 p_chr_id IN NUMBER,
3326 p_major_version IN NUMBER
3327 ) RETURN VARCHAR2 IS
3328
3329 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
3330
3331 BEGIN
3332 INSERT INTO okc_timevalues_h
3333 (
3334 major_version,
3335 id,
3336 spn_id,
3337 tve_id_offset,
3338 tve_id_generated_by,
3339 tve_id_started,
3340 tve_id_ended,
3341 tve_id_limited,
3342 cnh_id,
3343 dnz_chr_id,
3344 tve_type,
3345 tze_id,
3346 object_version_number,
3347 created_by,
3348 creation_date,
3349 last_updated_by,
3350 last_update_date,
3351 uom_code,
3352 duration,
3353 operator,
3354 before_after,
3355 datetime,
3356 month,
3357 day,
3358 hour,
3359 minute,
3360 second,
3361 interval_yn,
3362 last_update_login,
3363 nth,
3364 day_of_week,
3365 --Bug 3122962
3366 description,
3367 short_description,
3368 comments,
3369 name,
3370
3371 attribute_category,
3372 attribute1,
3373 attribute2,
3374 attribute3,
3375 attribute4,
3376 attribute5,
3377 attribute6,
3378 attribute7,
3379 attribute8,
3380 attribute9,
3381 attribute10,
3382 attribute11,
3383 attribute12,
3384 attribute13,
3385 attribute14,
3386 attribute15
3387 )
3388 SELECT
3389 p_major_version,
3390 id,
3391 spn_id,
3392 tve_id_offset,
3393 tve_id_generated_by,
3394 tve_id_started,
3395 tve_id_ended,
3396 tve_id_limited,
3397 cnh_id,
3398 dnz_chr_id,
3399 tve_type,
3400 tze_id,
3401 object_version_number,
3402 created_by,
3403 creation_date,
3404 last_updated_by,
3405 last_update_date,
3406 uom_code,
3407 duration,
3408 operator,
3409 before_after,
3410 datetime,
3411 month,
3412 day,
3413 hour,
3414 minute,
3415 second,
3416 interval_yn,
3417 last_update_login,
3418 nth,
3419 day_of_week,
3420 --Bug 3122962
3421 description,
3422 short_description,
3423 comments,
3424 name,
3425
3426 attribute_category,
3427 attribute1,
3428 attribute2,
3429 attribute3,
3430 attribute4,
3431 attribute5,
3432 attribute6,
3433 attribute7,
3434 attribute8,
3435 attribute9,
3436 attribute10,
3437 attribute11,
3438 attribute12,
3439 attribute13,
3440 attribute14,
3441 attribute15
3442 FROM okc_timevalues
3443 WHERE dnz_chr_id = p_chr_id;
3444
3445 -------------------------------
3446 -- Versioning TL Table
3447 -------------------------------
3448 --Bug 3122962
3449 /*
3450 INSERT INTO okc_timevalues_tlh
3451 (
3452 major_version,
3453 id,
3454 language,
3455 source_lang,
3456 sfwt_flag,
3457 description,
3458 short_description,
3459 comments,
3460 name,
3461 created_by,
3462 creation_date,
3463 last_updated_by,
3464 last_update_date,
3465 last_update_login
3466 )
3467 SELECT
3468 p_major_version,
3469 id,
3470 language,
3471 source_lang,
3472 sfwt_flag,
3473 description,
3474 short_description,
3475 comments,
3476 name,
3477 created_by,
3478 creation_date,
3479 last_updated_by,
3480 last_update_date,
3481 last_update_login
3482 FROM okc_timevalues_tl
3483 WHERE id in (SELECT id
3484 FROM okc_timevalues_b
3485 WHERE dnz_chr_id = p_chr_id);
3486 */
3487 RETURN l_return_status;
3488 EXCEPTION
3489 -- other appropriate handlers
3490 WHEN OTHERS THEN
3491 -- store SQL error message on message stack
3492 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
3493 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
3494 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
3495 p_token1_value => sqlcode,
3496 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
3497 p_token2_value => sqlerrm);
3498
3499 -- notify UNEXPECTED error
3500 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3501 return l_return_status;
3502 END create_version;
3503
3504 --This Function is called from Versioning API OKC_VERSION_PVT
3505 --Old Location:OKCRVERB.pls
3506 --New Location:Base Table API
3507
3508 FUNCTION restore_version(
3509 p_chr_id IN NUMBER,
3510 p_major_version IN NUMBER
3511 ) RETURN VARCHAR2 IS
3512
3513 l_return_status VARCHAR2(1):= OKC_API.G_RET_STS_SUCCESS;
3514
3515 BEGIN
3516 --Bug 3122962
3517 /*
3518 INSERT INTO okc_timevalues_tl
3519 (
3520 id,
3521 language,
3522 source_lang,
3523 sfwt_flag,
3524 description,
3525 short_description,
3526 comments,
3527 name,
3528 created_by,
3529 creation_date,
3530 last_updated_by,
3531 last_update_date,
3532 last_update_login
3533 )
3534 SELECT
3535 id,
3536 language,
3537 source_lang,
3538 sfwt_flag,
3539 description,
3540 short_description,
3541 comments,
3542 name,
3543 created_by,
3544 creation_date,
3545 last_updated_by,
3546 last_update_date,
3547 last_update_login
3548 FROM okc_timevalues_tlh
3549 WHERE id in (SELECT id
3550 FROM okc_timevalues_bh
3551 WHERE dnz_chr_id = p_chr_id)
3552 AND major_version = p_major_version;
3553 */
3554 -----------------------------------------
3555 -- Restoring Base Table
3556 -----------------------------------------
3557
3558 INSERT INTO okc_timevalues
3559 (
3560 id,
3561 spn_id,
3562 tve_id_offset,
3563 tve_id_generated_by,
3564 tve_id_started,
3565 tve_id_ended,
3566 tve_id_limited,
3567 cnh_id,
3568 dnz_chr_id,
3569 tve_type,
3570 tze_id,
3571 object_version_number,
3572 created_by,
3573 creation_date,
3574 last_updated_by,
3575 last_update_date,
3576 uom_code,
3577 duration,
3578 operator,
3579 before_after,
3580 datetime,
3581 month,
3582 day,
3583 hour,
3584 minute,
3585 second,
3586 interval_yn,
3587 last_update_login,
3588 nth,
3589 day_of_week,
3590 --Bug 3122962
3591 description,
3592 short_description,
3593 comments,
3594 name,
3595
3596 attribute_category,
3597 attribute1,
3598 attribute2,
3599 attribute3,
3600 attribute4,
3601 attribute5,
3602 attribute6,
3603 attribute7,
3604 attribute8,
3605 attribute9,
3606 attribute10,
3607 attribute11,
3608 attribute12,
3609 attribute13,
3610 attribute14,
3611 attribute15
3612 )
3613 SELECT
3614 id,
3615 spn_id,
3616 tve_id_offset,
3617 tve_id_generated_by,
3618 tve_id_started,
3619 tve_id_ended,
3620 tve_id_limited,
3621 cnh_id,
3622 dnz_chr_id,
3623 tve_type,
3624 tze_id,
3625 object_version_number,
3626 created_by,
3627 creation_date,
3628 last_updated_by,
3629 last_update_date,
3630 uom_code,
3631 duration,
3632 operator,
3633 before_after,
3634 datetime,
3635 month,
3636 day,
3637 hour,
3638 minute,
3639 second,
3640 interval_yn,
3641 last_update_login,
3642 nth,
3643 day_of_week,
3644 --Bug 3122962
3645 description,
3646 short_description,
3647 comments,
3648 name,
3649
3650 attribute_category,
3651 attribute1,
3652 attribute2,
3653 attribute3,
3654 attribute4,
3655 attribute5,
3656 attribute6,
3657 attribute7,
3658 attribute8,
3659 attribute9,
3660 attribute10,
3661 attribute11,
3662 attribute12,
3663 attribute13,
3664 attribute14,
3665 attribute15
3666 FROM okc_timevalues_h
3667 WHERE dnz_chr_id = p_chr_id
3668 AND major_version = p_major_version;
3669
3670 RETURN l_return_status;
3671 EXCEPTION
3672 -- other appropriate handlers
3673 WHEN OTHERS THEN
3674 -- store SQL error message on message stack
3675 OKC_API.SET_MESSAGE(p_app_name => okc_version_pvt.G_APP_NAME,
3676 p_msg_name => okc_version_pvt.G_UNEXPECTED_ERROR,
3677 p_token1 => okc_version_pvt.G_SQLCODE_TOKEN,
3678 p_token1_value => sqlcode,
3679 p_token2 => okc_version_pvt.G_SQLERRM_TOKEN,
3680 p_token2_value => sqlerrm);
3681
3682 -- notify UNEXPECTED error
3683 l_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
3684 return l_return_status;
3685 END restore_version;
3686
3687 END OKC_TAV_PVT;