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