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