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