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