[Home] [Help]
PACKAGE BODY: APPS.OKC_TIME_PVT
Source
1 PACKAGE BODY OKC_TIME_PVT AS
2 /* $Header: OKCCTVEB.pls 120.0 2005/05/25 22:39:52 appldev noship $ */
3
4 l_debug VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
5
6 ---------------------------------------------------------------------------
7 -- PROCEDURE add_language
8 ---------------------------------------------------------------------------
9
10 PROCEDURE add_language IS
11 BEGIN
12 --Bug 3122962
13 null;
14
15 /* Mar/19/03 requested by Ric Ginsberg */
16 /* The following delete and update statements are commented out */
17 /* as a quick workaround to fix the time-consuming table handler issue */
18 /* Eventually we'll need to turn them into a separate fix_language procedure */
19 /*
20
21 DELETE FROM OKC_TIMEVALUES_TL T
22 WHERE NOT EXISTS (
23 SELECT NULL
24 FROM OKC_TIMEVALUES_B B
25 WHERE B.ID = T.ID
26 );
27 */
28
29 /* commented out smallya 03/04/2003 for bug 2831959 as this does not make any sense.*/
30 /* Also it takes a lot of time doing this redundant update*/
31 /* UPDATE OKC_TIMEVALUES_TL T SET (
32 DESCRIPTION,
33 SHORT_DESCRIPTION,
34 COMMENTS,
35 NAME) = (SELECT
36 B.DESCRIPTION,
37 B.SHORT_DESCRIPTION,
38 B.COMMENTS,
39 B.NAME
40 FROM OKC_TIMEVALUES_TL B
41 WHERE B.ID = T.ID
42 AND B.LANGUAGE = T.SOURCE_LANG)
43 WHERE (
44 T.ID,
45 T.LANGUAGE)
46 IN (SELECT
47 SUBT.ID,
48 SUBT.LANGUAGE
49 FROM OKC_TIMEVALUES_TL SUBB, OKC_TIMEVALUES_TL SUBT
50 WHERE SUBB.ID = SUBT.ID
51 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
52 AND (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
53 OR SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
54 OR SUBB.COMMENTS <> SUBT.COMMENTS
55 OR SUBB.NAME <> SUBT.NAME
56 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
57 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
58 OR (SUBB.SHORT_DESCRIPTION IS NULL AND SUBT.SHORT_DESCRIPTION IS NOT NULL)
59 OR (SUBB.SHORT_DESCRIPTION IS NOT NULL AND SUBT.SHORT_DESCRIPTION IS NULL)
60 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
61 OR (SUBB.COMMENTS IS NOT NULL AND SUBT.COMMENTS IS NULL)
62 OR (SUBB.NAME IS NULL AND SUBT.NAME IS NOT NULL)
63 OR (SUBB.NAME IS NOT NULL AND SUBT.NAME IS NULL)
64 )); */
65
66 --Bug 3122962
67 /*
68 INSERT INTO OKC_TIMEVALUES_TL (
69 ID,
70 LANGUAGE,
71 SOURCE_LANG,
72 SFWT_FLAG,
73 DESCRIPTION,
74 SHORT_DESCRIPTION,
75 COMMENTS,
76 NAME,
77 CREATED_BY,
78 CREATION_DATE,
79 LAST_UPDATED_BY,
80 LAST_UPDATE_DATE,
81 LAST_UPDATE_LOGIN)
82 SELECT
83 B.ID,
84 L.LANGUAGE_CODE,
85 B.SOURCE_LANG,
86 B.SFWT_FLAG,
87 B.DESCRIPTION,
88 B.SHORT_DESCRIPTION,
89 B.COMMENTS,
90 B.NAME,
91 B.CREATED_BY,
92 B.CREATION_DATE,
93 B.LAST_UPDATED_BY,
94 B.LAST_UPDATE_DATE,
95 B.LAST_UPDATE_LOGIN
96 FROM OKC_TIMEVALUES_TL B, FND_LANGUAGES L
97 WHERE L.INSTALLED_FLAG IN ('I', 'B')
98 AND B.LANGUAGE = USERENV('LANG')
99 AND NOT EXISTS(
100 SELECT NULL
101 FROM OKC_TIMEVALUES_TL T
102 WHERE T.ID = B.ID
103 AND T.LANGUAGE = L.LANGUAGE_CODE
104 );
105 okc_tcu_pvt.add_language;
106 DELETE FROM OKC_TIMEVALUES_TLH T
107 WHERE NOT EXISTS (
108 SELECT NULL
109 FROM OKC_TIMEVALUES_BH B
110 WHERE B.ID = T.ID
111 AND B.MAJOR_VERSION = T.MAJOR_VERSION
112 );
113
114 UPDATE OKC_TIMEVALUES_TLH T SET (
115 DESCRIPTION,
116 SHORT_DESCRIPTION,
117 COMMENTS,
118 NAME) = (SELECT
119 B.DESCRIPTION,
120 B.SHORT_DESCRIPTION,
121 B.COMMENTS,
122 B.NAME
123 FROM OKC_TIMEVALUES_TLH B
124 WHERE B.ID = T.ID
125 AND B.MAJOR_VERSION = T.MAJOR_VERSION
126 AND B.LANGUAGE = T.SOURCE_LANG)
127 WHERE (
128 T.ID,
129 T.MAJOR_VERSION,
130 T.LANGUAGE)
131 IN (SELECT
132 SUBT.ID,
133 SUBT.MAJOR_VERSION,
134 SUBT.LANGUAGE
135 FROM OKC_TIMEVALUES_TLH SUBB, OKC_TIMEVALUES_TLH SUBT
136 WHERE SUBB.ID = SUBT.ID
137 AND SUBB.MAJOR_VERSION = SUBT.MAJOR_VERSION
138 AND SUBB.LANGUAGE = SUBT.SOURCE_LANG
139 AND (SUBB.DESCRIPTION <> SUBT.DESCRIPTION
140 OR SUBB.SHORT_DESCRIPTION <> SUBT.SHORT_DESCRIPTION
141 OR SUBB.COMMENTS <> SUBT.COMMENTS
142 OR SUBB.NAME <> SUBT.NAME
143 OR (SUBB.DESCRIPTION IS NULL AND SUBT.DESCRIPTION IS NOT NULL)
144 OR (SUBB.DESCRIPTION IS NOT NULL AND SUBT.DESCRIPTION IS NULL)
145 OR (SUBB.SHORT_DESCRIPTION IS NULL AND SUBT.SHORT_DESCRIPTION IS NOT NULL)
146 OR (SUBB.SHORT_DESCRIPTION IS NOT NULL AND SUBT.SHORT_DESCRIPTION IS NULL)
147 OR (SUBB.COMMENTS IS NULL AND SUBT.COMMENTS IS NOT NULL)
148 OR (SUBB.COMMENTS IS NOT NULL AND SUBT.COMMENTS IS NULL)
149 OR (SUBB.NAME IS NULL AND SUBT.NAME IS NOT NULL)
150 OR (SUBB.NAME IS NOT NULL AND SUBT.NAME IS NULL)
151 ));
152
153 INSERT INTO OKC_TIMEVALUES_TLH (
154 ID,
155 LANGUAGE,
156 MAJOR_VERSION,
157 SOURCE_LANG,
158 SFWT_FLAG,
159 DESCRIPTION,
160 SHORT_DESCRIPTION,
161 COMMENTS,
162 NAME,
163 CREATED_BY,
164 CREATION_DATE,
165 LAST_UPDATED_BY,
166 LAST_UPDATE_DATE,
167 LAST_UPDATE_LOGIN)
168 SELECT
169 B.ID,
170 L.LANGUAGE_CODE,
171 B.MAJOR_VERSION,
172 B.SOURCE_LANG,
173 B.SFWT_FLAG,
174 B.DESCRIPTION,
175 B.SHORT_DESCRIPTION,
176 B.COMMENTS,
177 B.NAME,
178 B.CREATED_BY,
179 B.CREATION_DATE,
180 B.LAST_UPDATED_BY,
181 B.LAST_UPDATE_DATE,
182 B.LAST_UPDATE_LOGIN
183 FROM OKC_TIMEVALUES_TLH B, FND_LANGUAGES L
184 WHERE L.INSTALLED_FLAG IN ('I', 'B')
185 AND B.LANGUAGE = USERENV('LANG')
186 AND NOT EXISTS(
187 SELECT NULL
188 FROM OKC_TIMEVALUES_TLH T
189 WHERE T.ID = B.ID
190 AND T.MAJOR_VERSION = B.MAJOR_VERSION
191 AND T.LANGUAGE = L.LANGUAGE_CODE
192 );
193 */
194 END add_language;
195
196
197 PROCEDURE DELETE_TIMEVALUES_N_TASKS(
198 p_api_version IN NUMBER,
199 p_init_msg_list IN VARCHAR2 ,
200 x_return_status OUT NOCOPY VARCHAR2,
201 x_msg_count OUT NOCOPY NUMBER,
202 x_msg_data OUT NOCOPY VARCHAR2,
203 p_chr_id IN NUMBER ,
204 p_tve_id IN NUMBER) IS
205 CURSOR tve_csr(p_tve_id IN NUMBER) is
206 --Bug 3122962
207 SELECT TVE_TYPE, TVE_ID_OFFSET,TVE_ID_STARTED,TVE_ID_ENDED FROM OKC_TIMEVALUES
208 WHERE id = p_tve_id;
209 l_tve_rec tve_csr%ROWTYPE;
210 l_not_found BOOLEAN := TRUE;
211 item_not_found_error EXCEPTION;
212 p_talv_evt_rec OKC_TIME_PVT.talv_event_rec_type;
213 p_tgnv_rec OKC_TIME_PVT.tgnv_rec_type;
214 p_tavv_rec OKC_TIME_PVT.tavv_rec_type;
215 p_cylv_ext_rec OKC_TIME_PVT.cylv_extended_rec_type;
216 p_tgdv_ext_rec OKC_TIME_PVT.tgdv_extended_rec_type;
217 p_isev_ext_rec OKC_TIME_PVT.isev_extended_rec_type;
218 p_igsv_ext_rec OKC_TIME_PVT.igsv_extended_rec_type;
219 l_api_name CONSTANT VARCHAR2(30) := 'delete_timevalues_n_tasks';
220 BEGIN
221 x_return_status := OKC_API.G_RET_STS_SUCCESS;
222 if p_chr_id <> 0 Then
223 if OKC_TIME_RES_PUB.Check_Res_Time_N_Tasks(p_tve_id, sysdate) Then
224 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
225 p_msg_name => 'OKC_RTV_EXISTS');
226 x_return_status := OKC_API.G_RET_STS_ERROR;
227 return;
228 end if;
229 end if;
230 open tve_csr(p_tve_id);
231 fetch tve_csr into l_tve_rec;
232 l_not_found := tve_csr%NOTFOUND;
233 close tve_csr;
234 if l_not_found THEN
235 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TVE_ID');
236 raise item_not_found_error;
237 end if;
238 if l_tve_rec.tve_type = 'TAL' Then
239 p_talv_evt_rec.id := p_tve_id;
240 p_talv_evt_rec.tve_id_offset := l_tve_rec.tve_id_offset;
241 OKC_TIME_PVT.DELETE_TPA_RELTV(
242 p_api_version ,
243 p_init_msg_list ,
244 x_return_status ,
245 x_msg_count ,
246 x_msg_data ,
247 p_talv_evt_rec) ;
248 elsif l_tve_rec.tve_type = 'TGN' Then
249 p_tgnv_rec.id := p_tve_id;
250 OKC_TIME_PVT.DELETE_TPG_NAMED(
251 p_api_version,
252 p_init_msg_list,
253 x_return_status,
254 x_msg_count,
255 x_msg_data,
256 p_tgnv_rec);
257 elsif l_tve_rec.tve_type = 'TAV' Then
258 p_tavv_rec.id := p_tve_id;
259 OKC_TIME_PVT.DELETE_TPA_VALUE(
260 p_api_version,
261 p_init_msg_list,
262 x_return_status,
263 x_msg_count,
264 x_msg_data,
265 p_tavv_rec);
266 elsif l_tve_rec.tve_type = 'TGD' Then
267 p_tgdv_ext_rec.id := p_tve_id;
268 OKC_TIME_PVT.DELETE_TPG_DELIMITED(
269 p_api_version,
270 p_init_msg_list,
271 x_return_status,
272 x_msg_count,
273 x_msg_data,
274 p_tgdv_ext_rec);
275 elsif l_tve_rec.tve_type = 'CYL' Then
276 p_cylv_ext_rec.id := p_tve_id;
277 OKC_TIME_PVT.DELETE_CYCLE(
278 p_api_version,
279 p_init_msg_list,
280 x_return_status,
281 x_msg_count,
282 x_msg_data,
283 p_cylv_ext_rec);
284 elsif l_tve_rec.tve_type = 'ISE' Then
285 p_isev_ext_rec.id := p_tve_id;
286 --Bug#3080839 Timevalues not getting deleted
287 p_isev_ext_rec.tve_id_started := l_tve_rec.tve_id_started;
288 p_isev_ext_rec.tve_id_ended := l_tve_rec.tve_id_ended;
289 OKC_TIME_PVT.DELETE_IA_STARTEND(
290 p_api_version,
291 p_init_msg_list,
292 x_return_status,
293 x_msg_count,
294 x_msg_data,
295 p_isev_ext_rec);
296 elsif l_tve_rec.tve_type = 'IGS' Then
297 p_igsv_ext_rec.id := p_tve_id;
298 --Bug#3080839 Timevalues not getting deleted
299 p_igsv_ext_rec.tve_id_started := l_tve_rec.tve_id_started;
300 p_igsv_ext_rec.tve_id_ended := l_tve_rec.tve_id_ended;
301 OKC_TIME_PVT.DELETE_IG_STARTEND(
302 p_api_version,
303 p_init_msg_list,
304 x_return_status,
305 x_msg_count,
306 x_msg_data,
307 p_igsv_ext_rec);
308 end if;
309 if x_return_status = OKC_API.G_RET_STS_SUCCESS Then
310 OKC_TIME_RES_PUB.Delete_Res_Time_N_Tasks(
311 p_tve_id,
312 sysdate,
313 p_api_version,
314 p_init_msg_list,
315 x_return_status);
316 end if;
317
318 EXCEPTION
319 WHEN item_not_found_error THEN
320 x_return_status := OKC_API.G_RET_STS_ERROR;
321 WHEN OTHERS THEN
322 x_return_status := OKC_API.HANDLE_EXCEPTIONS
323 (l_api_name,
324 G_PKG_NAME,
325 'OTHERS',
326 x_msg_count,
327 x_msg_data,
328 '_PVT');
329 END DELETE_TIMEVALUES_N_TASKS;
330 --------------------------------------------------------------------------
331 ---The following procedures cater to handling of OKC_TIME_TPA_RELTV
332 --------------------------------------------------------------------------
333 PROCEDURE migrate (
334 p_from IN talv_event_rec_type,
335 p_to IN OUT NOCOPY talv_rec_type
336 ) IS
337 BEGIN
338 --Bug 3122962 p_to.sfwt_flag := p_from.sfwt_flag;
339 p_to.description := p_from.description;
340 p_to.short_description := p_from.short_description;
341 p_to.comments := p_from.comments;
342 p_to.id := p_from.id;
343 p_to.spn_id := p_from.spn_id;
344 p_to.tve_id_offset := p_from.tve_id_offset;
345 p_to.tve_id_limited := p_from.tve_id_limited;
346 p_to.tze_id := p_from.tze_id;
347 p_to.dnz_chr_id := p_from.dnz_chr_id;
348 p_to.uom_code := p_from.uom_code;
349 p_to.object_version_number := p_from.object_version_number;
350 p_to.created_by := p_from.created_by;
351 p_to.creation_date := p_from.creation_date;
352 p_to.last_updated_by := p_from.last_updated_by;
353 p_to.last_update_date := p_from.last_update_date;
354 p_to.duration := p_from.duration;
355 p_to.operator := p_from.operator;
356 p_to.before_after := p_from.before_after;
357 p_to.last_update_login := p_from.last_update_login;
358 p_to.attribute_category := p_from.attribute_category;
359 p_to.attribute1 := p_from.attribute1;
360 p_to.attribute2 := p_from.attribute2;
361 p_to.attribute3 := p_from.attribute3;
362 p_to.attribute4 := p_from.attribute4;
363 p_to.attribute5 := p_from.attribute5;
364 p_to.attribute6 := p_from.attribute6;
365 p_to.attribute7 := p_from.attribute7;
366 p_to.attribute8 := p_from.attribute8;
367 p_to.attribute9 := p_from.attribute9;
368 p_to.attribute10 := p_from.attribute10;
369 p_to.attribute11 := p_from.attribute11;
370 p_to.attribute12 := p_from.attribute12;
371 p_to.attribute13 := p_from.attribute13;
372 p_to.attribute14 := p_from.attribute14;
373 p_to.attribute15 := p_from.attribute15;
374 END migrate;
375
376 PROCEDURE migrate (
377 p_from IN talv_rec_type,
378 p_to IN OUT NOCOPY talv_event_rec_type
379 ) IS
380 BEGIN
381 --Bug 3122962 p_to.sfwt_flag := p_from.sfwt_flag;
382 p_to.description := p_from.description;
383 p_to.short_description := p_from.short_description;
384 p_to.comments := p_from.comments;
385 p_to.id := p_from.id;
386 p_to.spn_id := p_from.spn_id;
387 p_to.tve_id_offset := p_from.tve_id_offset;
388 p_to.tve_id_limited := p_from.tve_id_limited;
389 p_to.tze_id := p_from.tze_id;
390 p_to.dnz_chr_id := p_from.dnz_chr_id;
391 p_to.uom_code := p_from.uom_code;
392 p_to.object_version_number := p_from.object_version_number;
393 p_to.created_by := p_from.created_by;
394 p_to.creation_date := p_from.creation_date;
395 p_to.last_updated_by := p_from.last_updated_by;
396 p_to.last_update_date := p_from.last_update_date;
397 p_to.duration := p_from.duration;
398 p_to.operator := p_from.operator;
399 p_to.before_after := p_from.before_after;
400 p_to.last_update_login := p_from.last_update_login;
401 p_to.attribute_category := p_from.attribute_category;
402 p_to.attribute1 := p_from.attribute1;
403 p_to.attribute2 := p_from.attribute2;
404 p_to.attribute3 := p_from.attribute3;
405 p_to.attribute4 := p_from.attribute4;
406 p_to.attribute5 := p_from.attribute5;
407 p_to.attribute6 := p_from.attribute6;
408 p_to.attribute7 := p_from.attribute7;
409 p_to.attribute8 := p_from.attribute8;
410 p_to.attribute9 := p_from.attribute9;
411 p_to.attribute10 := p_from.attribute10;
412 p_to.attribute11 := p_from.attribute11;
413 p_to.attribute12 := p_from.attribute12;
414 p_to.attribute13 := p_from.attribute13;
415 p_to.attribute14 := p_from.attribute14;
416 p_to.attribute15 := p_from.attribute15;
417 END migrate;
418
419 PROCEDURE CREATE_TPA_RELTV(
420 p_api_version IN NUMBER,
421 p_init_msg_list IN VARCHAR2 ,
422 x_return_status OUT NOCOPY VARCHAR2,
423 x_msg_count OUT NOCOPY NUMBER,
424 x_msg_data OUT NOCOPY VARCHAR2,
425 p_talv_rec IN talv_rec_type,
426 x_talv_rec OUT NOCOPY talv_rec_type) IS
427 BEGIN
428 okc_tal_pvt.insert_row(
429 p_api_version,
430 p_init_msg_list,
431 x_return_status,
432 x_msg_count,
433 x_msg_data,
434 p_talv_rec,
435 x_talv_rec);
436 END CREATE_TPA_RELTV;
437
438 PROCEDURE UPDATE_TPA_RELTV(
439 p_api_version IN NUMBER,
440 p_init_msg_list IN VARCHAR2 ,
441 x_return_status OUT NOCOPY VARCHAR2,
442 x_msg_count OUT NOCOPY NUMBER,
443 x_msg_data OUT NOCOPY VARCHAR2,
444 p_talv_rec IN talv_rec_type,
445 x_talv_rec OUT NOCOPY talv_rec_type) IS
446 BEGIN
447 okc_tal_pvt.update_row(
448 p_api_version,
449 p_init_msg_list,
450 x_return_status,
451 x_msg_count,
452 x_msg_data,
453 p_talv_rec,
454 x_talv_rec);
455 END UPDATE_TPA_RELTV;
456 PROCEDURE DELETE_TPA_RELTV(
457 p_api_version IN NUMBER,
458 p_init_msg_list IN VARCHAR2 ,
459 x_return_status OUT NOCOPY VARCHAR2,
460 x_msg_count OUT NOCOPY NUMBER,
461 x_msg_data OUT NOCOPY VARCHAR2,
462 p_talv_rec IN talv_rec_type) IS
463 BEGIN
464 okc_tal_pvt.delete_row(
465 p_api_version,
466 p_init_msg_list,
467 x_return_status,
468 x_msg_count,
469 x_msg_data,
470 p_talv_rec);
471 END DELETE_TPA_RELTV;
472 PROCEDURE LOCK_TPA_RELTV(
473 p_api_version IN NUMBER,
474 p_init_msg_list IN VARCHAR2 ,
475 x_return_status OUT NOCOPY VARCHAR2,
476 x_msg_count OUT NOCOPY NUMBER,
477 x_msg_data OUT NOCOPY VARCHAR2,
478 p_talv_rec IN talv_rec_type) IS
479 BEGIN
480 okc_tal_pvt.lock_row(
481 p_api_version,
482 p_init_msg_list,
483 x_return_status,
484 x_msg_count,
485 x_msg_data,
486 p_talv_rec);
487 END LOCK_TPA_RELTV;
488
489 PROCEDURE VALID_TPA_RELTV(
490 p_api_version IN NUMBER,
491 p_init_msg_list IN VARCHAR2 ,
492 x_return_status OUT NOCOPY VARCHAR2,
493 x_msg_count OUT NOCOPY NUMBER,
494 x_msg_data OUT NOCOPY VARCHAR2,
495 p_talv_rec IN talv_rec_type) IS
496 BEGIN
497 okc_tal_pvt.validate_row(
498 p_api_version,
499 p_init_msg_list,
500 x_return_status,
501 x_msg_count,
502 x_msg_data,
503 p_talv_rec);
504 END VALID_TPA_RELTV;
505
506 PROCEDURE CREATE_TPA_RELTV(
507 p_api_version IN NUMBER,
508 p_init_msg_list IN VARCHAR2 ,
509 x_return_status OUT NOCOPY VARCHAR2,
510 x_msg_count OUT NOCOPY NUMBER,
511 x_msg_data OUT NOCOPY VARCHAR2,
512 p_talv_evt_rec IN talv_event_rec_type,
513 x_talv_evt_rec OUT NOCOPY talv_event_rec_type) IS
514 p_talv_rec talv_rec_type;
515 x_talv_rec talv_rec_type;
516 p_tgnv_rec tgnv_rec_type;
517 x_tgnv_rec tgnv_rec_type;
518 BEGIN
519 x_return_status := OKC_API.G_RET_STS_SUCCESS;
520 x_talv_evt_rec := p_talv_evt_rec;
521 p_tgnv_rec.cnh_id := p_talv_evt_rec.cnh_id;
522 p_tgnv_rec.comments := 'Generated by TAL';
523 p_tgnv_rec.dnz_chr_id := p_talv_evt_rec.dnz_chr_id;
524 OKC_TIME_pub.create_tpg_named(
525 p_api_version,
526 p_init_msg_list,
527 x_return_status,
528 x_msg_count,
529 x_msg_data,
530 p_tgnv_rec,
531 x_tgnv_rec);
532 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
533 return;
534 end if;
535 x_talv_evt_rec.tve_id_offset := x_tgnv_rec.id;
536 x_talv_evt_rec.cnh_id := x_tgnv_rec.cnh_id;
537 migrate(x_talv_evt_rec,p_talv_rec);
538 okc_tal_pvt.insert_row(
539 p_api_version,
540 p_init_msg_list,
541 x_return_status,
542 x_msg_count,
543 x_msg_data,
544 p_talv_rec,
545 x_talv_rec);
546 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
547 return;
548 end if;
549 migrate(x_talv_rec,x_talv_evt_rec);
550 END CREATE_TPA_RELTV;
551 PROCEDURE UPDATE_TPA_RELTV(
552 p_api_version IN NUMBER,
553 p_init_msg_list IN VARCHAR2 ,
554 x_return_status OUT NOCOPY VARCHAR2,
555 x_msg_count OUT NOCOPY NUMBER,
556 x_msg_data OUT NOCOPY VARCHAR2,
557 p_talv_evt_rec IN talv_event_rec_type,
558 x_talv_evt_rec OUT NOCOPY talv_event_rec_type) IS
559 p_talv_rec talv_rec_type;
560 x_talv_rec talv_rec_type;
561 p_tgnv_rec tgnv_rec_type;
562 x_tgnv_rec tgnv_rec_type;
563 BEGIN
564 x_return_status := OKC_API.G_RET_STS_SUCCESS;
565 p_tgnv_rec.cnh_id := p_talv_evt_rec.cnh_id;
566 p_tgnv_rec.id := p_talv_evt_rec.tve_id_offset;
567 OKC_TIME_pub.update_tpg_named(
568 p_api_version,
569 p_init_msg_list,
570 x_return_status,
571 x_msg_count,
572 x_msg_data,
573 p_tgnv_rec,
574 x_tgnv_rec);
575 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
576 return;
577 end if;
578 x_talv_evt_rec := p_talv_evt_rec;
579 migrate(p_talv_evt_rec,p_talv_rec);
580 okc_tal_pvt.update_row(
581 p_api_version,
582 p_init_msg_list,
583 x_return_status,
584 x_msg_count,
585 x_msg_data,
586 p_talv_rec,
587 x_talv_rec);
588 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
589 return;
590 end if;
591 migrate(x_talv_rec,x_talv_evt_rec);
592 END UPDATE_TPA_RELTV;
593 PROCEDURE DELETE_TPA_RELTV(
594 p_api_version IN NUMBER,
595 p_init_msg_list IN VARCHAR2 ,
596 x_return_status OUT NOCOPY VARCHAR2,
597 x_msg_count OUT NOCOPY NUMBER,
598 x_msg_data OUT NOCOPY VARCHAR2,
599 p_talv_evt_rec IN talv_event_rec_type) IS
600 p_talv_rec talv_rec_type;
601 x_talv_rec talv_rec_type;
602 p_tgnv_rec tgnv_rec_type;
603 BEGIN
604 x_return_status := OKC_API.G_RET_STS_SUCCESS;
605 p_tgnv_rec.id := p_talv_evt_rec.tve_id_offset;
606 OKC_TIME_pub.delete_tpg_named(
607 p_api_version,
608 p_init_msg_list,
609 x_return_status,
610 x_msg_count,
611 x_msg_data,
612 p_tgnv_rec);
613 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
614 return;
615 end if;
616 migrate(p_talv_evt_rec,p_talv_rec);
617 okc_tal_pvt.delete_row(
618 p_api_version,
619 p_init_msg_list,
620 x_return_status,
621 x_msg_count,
622 x_msg_data,
623 p_talv_rec);
624 END DELETE_TPA_RELTV;
625 PROCEDURE LOCK_TPA_RELTV(
626 p_api_version IN NUMBER,
627 p_init_msg_list IN VARCHAR2 ,
628 x_return_status OUT NOCOPY VARCHAR2,
629 x_msg_count OUT NOCOPY NUMBER,
630 x_msg_data OUT NOCOPY VARCHAR2,
631 p_talv_evt_rec IN talv_event_rec_type) IS
632 p_talv_rec talv_rec_type;
633 BEGIN
634 x_return_status := OKC_API.G_RET_STS_SUCCESS;
635 migrate(p_talv_evt_rec,p_talv_rec);
636 okc_tal_pvt.lock_row(
637 p_api_version,
638 p_init_msg_list,
639 x_return_status,
640 x_msg_count,
641 x_msg_data,
642 p_talv_rec);
643 END LOCK_TPA_RELTV;
644
645 PROCEDURE VALID_TPA_RELTV(
646 p_api_version IN NUMBER,
647 p_init_msg_list IN VARCHAR2 ,
648 x_return_status OUT NOCOPY VARCHAR2,
649 x_msg_count OUT NOCOPY NUMBER,
650 x_msg_data OUT NOCOPY VARCHAR2,
651 p_talv_evt_rec IN talv_event_rec_type) IS
652 p_talv_rec talv_rec_type;
653 p_tgnv_rec tgnv_rec_type;
654 BEGIN
655 x_return_status := OKC_API.G_RET_STS_SUCCESS;
656 p_tgnv_rec.id := p_talv_evt_rec.tve_id_offset;
657 p_tgnv_rec.cnh_id := p_talv_evt_rec.cnh_id;
658 OKC_TIME_pub.valid_tpg_named(
659 p_api_version,
660 p_init_msg_list,
661 x_return_status,
662 x_msg_count,
663 x_msg_data,
664 p_tgnv_rec);
665 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
666 return;
667 end if;
668 migrate(p_talv_evt_rec,p_talv_rec);
669 okc_tal_pvt.validate_row(
670 p_api_version,
671 p_init_msg_list,
672 x_return_status,
673 x_msg_count,
674 x_msg_data,
675 p_talv_rec);
676 END VALID_TPA_RELTV;
677
678
679 --------------------------------------------------------------------------
680 ---The following procedures cater to handling of OKC_TIME_TPA_VALUE
681 --------------------------------------------------------------------------
682 PROCEDURE CREATE_TPA_VALUE(
683 p_api_version IN NUMBER,
684 p_init_msg_list IN VARCHAR2 ,
685 x_return_status OUT NOCOPY VARCHAR2,
686 x_msg_count OUT NOCOPY NUMBER,
687 x_msg_data OUT NOCOPY VARCHAR2,
688 p_tavv_rec IN tavv_rec_type,
689 x_tavv_rec OUT NOCOPY tavv_rec_type) IS
690 BEGIN
691 okc_tav_pvt.insert_row(
692 p_api_version,
693 p_init_msg_list,
694 x_return_status,
695 x_msg_count,
696 x_msg_data,
697 p_tavv_rec,
698 x_tavv_rec);
699 END CREATE_TPA_VALUE;
700 PROCEDURE UPDATE_TPA_VALUE(
701 p_api_version IN NUMBER,
702 p_init_msg_list IN VARCHAR2 ,
703 x_return_status OUT NOCOPY VARCHAR2,
704 x_msg_count OUT NOCOPY NUMBER,
705 x_msg_data OUT NOCOPY VARCHAR2,
706 p_tavv_rec IN tavv_rec_type,
707 x_tavv_rec OUT NOCOPY tavv_rec_type) IS
708 BEGIN
709 okc_tav_pvt.update_row(
710 p_api_version,
711 p_init_msg_list,
712 x_return_status,
713 x_msg_count,
714 x_msg_data,
715 p_tavv_rec,
716 x_tavv_rec);
717 END UPDATE_TPA_VALUE;
718 PROCEDURE DELETE_TPA_VALUE(
719 p_api_version IN NUMBER,
720 p_init_msg_list IN VARCHAR2 ,
721 x_return_status OUT NOCOPY VARCHAR2,
722 x_msg_count OUT NOCOPY NUMBER,
723 x_msg_data OUT NOCOPY VARCHAR2,
724 p_tavv_rec IN tavv_rec_type) IS
725 BEGIN
726 okc_tav_pvt.delete_row(
727 p_api_version,
728 p_init_msg_list,
729 x_return_status,
730 x_msg_count,
731 x_msg_data,
732 p_tavv_rec);
733 END DELETE_TPA_VALUE;
734 PROCEDURE LOCK_TPA_VALUE(
735 p_api_version IN NUMBER,
736 p_init_msg_list IN VARCHAR2 ,
737 x_return_status OUT NOCOPY VARCHAR2,
738 x_msg_count OUT NOCOPY NUMBER,
739 x_msg_data OUT NOCOPY VARCHAR2,
740 p_tavv_rec IN tavv_rec_type) IS
741 BEGIN
742 okc_tav_pvt.lock_row(
743 p_api_version,
744 p_init_msg_list,
745 x_return_status,
746 x_msg_count,
747 x_msg_data,
748 p_tavv_rec);
749 END LOCK_TPA_VALUE;
750
751 PROCEDURE VALID_TPA_VALUE(
752 p_api_version IN NUMBER,
753 p_init_msg_list IN VARCHAR2 ,
754 x_return_status OUT NOCOPY VARCHAR2,
755 x_msg_count OUT NOCOPY NUMBER,
756 x_msg_data OUT NOCOPY VARCHAR2,
757 p_tavv_rec IN tavv_rec_type) IS
758 BEGIN
759 okc_tav_pvt.validate_row(
760 p_api_version,
761 p_init_msg_list,
762 x_return_status,
763 x_msg_count,
764 x_msg_data,
765 p_tavv_rec);
766 END VALID_TPA_VALUE;
767
768 --------------------------------------------------------------------------
769 ---The following procedures cater to handling of OKC_TIME_TPG_DELIMITED
770 --------------------------------------------------------------------------
771
772 PROCEDURE migrate (
773 p_from IN tgdv_rec_type,
774 p_to IN OUT NOCOPY tgdv_extended_rec_type
775 ) IS
776 BEGIN
777 --Bug 3122962 p_to.sfwt_flag := p_from.sfwt_flag;
778 p_to.description := p_from.description;
779 p_to.short_description := p_from.short_description;
780 p_to.comments := p_from.comments;
781 p_to.id := p_from.id;
782 p_to.tve_id_limited := p_from.tve_id_limited;
783 p_to.tze_id := p_from.tze_id;
784 p_to.dnz_chr_id := p_from.dnz_chr_id;
785 p_to.object_version_number := p_from.object_version_number;
786 p_to.created_by := p_from.created_by;
787 p_to.creation_date := p_from.creation_date;
788 p_to.last_updated_by := p_from.last_updated_by;
789 p_to.last_update_date := p_from.last_update_date;
790 p_to.month := p_from.month;
791 p_to.day := p_from.day;
792 p_to.hour := p_from.hour;
793 p_to.minute := p_from.minute;
794 p_to.second := p_from.second;
795 p_to.nth := p_from.nth;
796 p_to.day_of_week := p_from.day_of_week;
797 p_to.last_update_login := p_from.last_update_login;
798 p_to.attribute_category := p_from.attribute_category;
799 p_to.attribute1 := p_from.attribute1;
800 p_to.attribute2 := p_from.attribute2;
801 p_to.attribute3 := p_from.attribute3;
802 p_to.attribute4 := p_from.attribute4;
803 p_to.attribute5 := p_from.attribute5;
804 p_to.attribute6 := p_from.attribute6;
805 p_to.attribute7 := p_from.attribute7;
806 p_to.attribute8 := p_from.attribute8;
807 p_to.attribute9 := p_from.attribute9;
808 p_to.attribute10 := p_from.attribute10;
809 p_to.attribute11 := p_from.attribute11;
810 p_to.attribute12 := p_from.attribute12;
811 p_to.attribute13 := p_from.attribute13;
812 p_to.attribute14 := p_from.attribute14;
813 p_to.attribute15 := p_from.attribute15;
814 END migrate;
815
816 PROCEDURE migrate (
817 p_from IN tgdv_extended_rec_type,
818 p_to IN OUT NOCOPY tgdv_rec_type
819 ) IS
820 BEGIN
821 --Bug 3122962 p_to.sfwt_flag := p_from.sfwt_flag;
822 p_to.description := p_from.description;
823 p_to.short_description := p_from.short_description;
824 p_to.comments := p_from.comments;
825 p_to.id := p_from.id;
826 p_to.tve_id_limited := p_from.tve_id_limited;
827 p_to.tze_id := p_from.tze_id;
828 p_to.dnz_chr_id := p_from.dnz_chr_id;
829 p_to.object_version_number := p_from.object_version_number;
830 p_to.created_by := p_from.created_by;
831 p_to.creation_date := p_from.creation_date;
832 p_to.last_updated_by := p_from.last_updated_by;
833 p_to.last_update_date := p_from.last_update_date;
834 p_to.month := p_from.month;
835 p_to.day := p_from.day;
836 p_to.hour := p_from.hour;
837 p_to.minute := p_from.minute;
838 p_to.second := p_from.second;
839 p_to.nth := p_from.nth;
840 p_to.day_of_week := p_from.day_of_week;
841 p_to.last_update_login := p_from.last_update_login;
842 p_to.attribute_category := p_from.attribute_category;
843 p_to.attribute1 := p_from.attribute1;
844 p_to.attribute2 := p_from.attribute2;
845 p_to.attribute3 := p_from.attribute3;
846 p_to.attribute4 := p_from.attribute4;
847 p_to.attribute5 := p_from.attribute5;
848 p_to.attribute6 := p_from.attribute6;
849 p_to.attribute7 := p_from.attribute7;
850 p_to.attribute8 := p_from.attribute8;
851 p_to.attribute9 := p_from.attribute9;
852 p_to.attribute10 := p_from.attribute10;
853 p_to.attribute11 := p_from.attribute11;
854 p_to.attribute12 := p_from.attribute12;
855 p_to.attribute13 := p_from.attribute13;
856 p_to.attribute14 := p_from.attribute14;
857 p_to.attribute15 := p_from.attribute15;
858 END migrate;
859
860 PROCEDURE CREATE_TPG_DELIMITED(
861 p_api_version IN NUMBER,
862 p_init_msg_list IN VARCHAR2 ,
863 x_return_status OUT NOCOPY VARCHAR2,
864 x_msg_count OUT NOCOPY NUMBER,
865 x_msg_data OUT NOCOPY VARCHAR2,
866 p_tgdv_ext_rec IN tgdv_extended_rec_type,
867 x_tgdv_ext_rec OUT NOCOPY tgdv_extended_rec_type) IS
868 p_tgdv_rec tgdv_rec_type;
869 x_tgdv_rec tgdv_rec_type;
870 p_isev_ext_rec isev_extended_rec_type;
871 x_isev_ext_rec isev_extended_rec_type;
872 BEGIN
873 x_return_status := OKC_API.G_RET_STS_SUCCESS;
874 x_tgdv_ext_rec := p_tgdv_ext_rec;
875 if p_tgdv_ext_rec.limited_start_date <> OKC_API.G_MISS_DATE and
876 p_tgdv_ext_rec.limited_start_date is not null and
877 p_tgdv_ext_rec.limited_end_date <> OKC_API.G_MISS_DATE and
878 p_tgdv_ext_rec.limited_end_date is not null then
879 p_isev_ext_rec.start_date := p_tgdv_ext_rec.limited_start_date;
880 p_isev_ext_rec.end_date := p_tgdv_ext_rec.limited_end_date;
881 p_isev_ext_rec.description := 'Limited by of Generic';
882 p_isev_ext_rec.short_description := 'Limited by of Generic';
883 p_isev_ext_rec.comments := 'Generated by TGD';
884 p_isev_ext_rec.dnz_chr_id := p_tgdv_ext_rec.dnz_chr_id;
885 OKC_TIME_pub.create_ia_startend(
886 p_api_version,
887 p_init_msg_list,
888 x_return_status,
889 x_msg_count,
890 x_msg_data,
891 p_isev_ext_rec,
892 x_isev_ext_rec);
893 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
894 return;
895 end if;
896 x_tgdv_ext_rec.tve_id_limited := x_isev_ext_rec.id;
897 x_tgdv_ext_rec.limited_start_date := x_isev_ext_rec.start_date;
898 x_tgdv_ext_rec.limited_end_date := x_isev_ext_rec.end_date;
899 end if;
900 migrate(x_tgdv_ext_rec,p_tgdv_rec);
901 okc_tgd_pvt.insert_row(
902 p_api_version,
903 p_init_msg_list,
904 x_return_status,
905 x_msg_count,
906 x_msg_data,
907 p_tgdv_rec,
908 x_tgdv_rec);
909 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
910 return;
911 end if;
912 migrate(x_tgdv_rec,x_tgdv_ext_rec);
913 END CREATE_TPG_DELIMITED;
914
915 PROCEDURE UPDATE_TPG_DELIMITED(
916 p_api_version IN NUMBER,
917 p_init_msg_list IN VARCHAR2 ,
918 x_return_status OUT NOCOPY VARCHAR2,
919 x_msg_count OUT NOCOPY NUMBER,
920 x_msg_data OUT NOCOPY VARCHAR2,
921 p_tgdv_ext_rec IN tgdv_extended_rec_type,
922 x_tgdv_ext_rec OUT NOCOPY tgdv_extended_rec_type) IS
923 p_tgdv_rec tgdv_rec_type;
924 x_tgdv_rec tgdv_rec_type;
925 l_row_notfound BOOLEAN := TRUE;
926 l_api_name CONSTANT VARCHAR2(30) := 'update_tpg_delimited';
927 p_isev_ext_rec isev_extended_rec_type;
928 x_isev_ext_rec isev_extended_rec_type;
929 CURSOR okc_tve_csr (p_id IN NUMBER) IS
930 SELECT tve_id_limited, dnz_chr_id
931 FROM okc_timevalues
932 WHERE id = p_id
933 and tve_type = 'TGD';
934 CURSOR okc_limited_csr (p_id IN NUMBER) IS
935 SELECT id, start_date, end_date
936 FROM okc_time_ia_startend_val_v
937 WHERE id = p_id;
938 l_okc_limited okc_limited_csr%ROWTYPE;
939 l_okc_tve okc_tve_csr%ROWTYPE;
940 BEGIN
941 x_return_status := OKC_API.G_RET_STS_SUCCESS;
942 x_tgdv_ext_rec := p_tgdv_ext_rec;
943 OPEN okc_tve_csr(x_tgdv_ext_rec.id);
944 FETCH okc_tve_csr INTO l_okc_tve;
945 l_row_notfound := okc_tve_csr%NOTFOUND;
946 CLOSE okc_tve_csr;
947 if (l_okc_tve.tve_id_limited = OKC_API.G_MISS_NUM or
948 l_okc_tve.tve_id_limited is NULL) then
949 if p_tgdv_ext_rec.limited_start_date <> OKC_API.G_MISS_DATE and
950 p_tgdv_ext_rec.limited_start_date is not null and
951 p_tgdv_ext_rec.limited_end_date <> OKC_API.G_MISS_DATE and
952 p_tgdv_ext_rec.limited_end_date is not null then
953 p_isev_ext_rec.start_date := p_tgdv_ext_rec.limited_start_date;
954 p_isev_ext_rec.end_date := p_tgdv_ext_rec.limited_end_date;
955 p_isev_ext_rec.description := 'Limited by of Generic';
956 p_isev_ext_rec.short_description := 'Limited by of Generic';
957 p_isev_ext_rec.comments := 'Generated by TGD';
958 p_isev_ext_rec.dnz_chr_id := l_okc_tve.dnz_chr_id;
959 OKC_TIME_pub.create_ia_startend(
960 p_api_version,
961 p_init_msg_list,
962 x_return_status,
963 x_msg_count,
964 x_msg_data,
965 p_isev_ext_rec,
966 x_isev_ext_rec);
967 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
968 return;
969 end if;
970 x_tgdv_ext_rec.tve_id_limited := x_isev_ext_rec.id;
971 x_tgdv_ext_rec.limited_start_date := x_isev_ext_rec.start_date;
972 x_tgdv_ext_rec.limited_end_date := x_isev_ext_rec.end_date;
973 end if;
974 else
975 if p_tgdv_ext_rec.limited_start_date is null then
976 p_isev_ext_rec.id := l_okc_tve.tve_id_limited;
977 OKC_TIME_pub.delete_ia_startend(
978 p_api_version,
979 p_init_msg_list,
980 x_return_status,
981 x_msg_count,
982 x_msg_data,
983 p_isev_ext_rec);
984 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
985 return;
986 end if;
987 x_tgdv_ext_rec.tve_id_limited := NULL;
988 x_tgdv_ext_rec.limited_start_date := NULL;
989 x_tgdv_ext_rec.limited_end_date := NULL;
990 else
991 OPEN okc_limited_csr(l_okc_tve.tve_id_limited);
992 FETCH okc_limited_csr INTO l_okc_limited;
993 l_row_notfound := okc_limited_csr%NOTFOUND;
994 CLOSE okc_limited_csr;
995 if (((l_okc_limited.start_date <> p_tgdv_ext_rec.limited_start_date) and
996 (p_tgdv_ext_rec.limited_start_date <> OKC_API.G_MISS_DATE)) or
997 ((l_okc_limited.end_date <> p_tgdv_ext_rec.limited_end_date) and
998 (p_tgdv_ext_rec.limited_end_date <> OKC_API.G_MISS_DATE))) then
999 p_isev_ext_rec.start_date := p_tgdv_ext_rec.limited_start_date;
1000 p_isev_ext_rec.end_date := p_tgdv_ext_rec.limited_end_date;
1001 p_isev_ext_rec.id := l_okc_tve.tve_id_limited;
1002 OKC_TIME_pub.update_ia_startend(
1003 p_api_version,
1004 p_init_msg_list,
1005 x_return_status,
1006 x_msg_count,
1007 x_msg_data,
1008 p_isev_ext_rec,
1009 x_isev_ext_rec);
1010 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1011 return;
1012 end if;
1013 x_tgdv_ext_rec.tve_id_limited := x_isev_ext_rec.id;
1014 x_tgdv_ext_rec.limited_start_date := x_isev_ext_rec.start_date;
1015 x_tgdv_ext_rec.limited_end_date := x_isev_ext_rec.end_date;
1016 end if;
1017 end if;
1018 end if;
1019 migrate(x_tgdv_ext_rec,p_tgdv_rec);
1020 okc_tgd_pvt.update_row(
1021 p_api_version,
1022 p_init_msg_list,
1023 x_return_status,
1024 x_msg_count,
1025 x_msg_data,
1026 p_tgdv_rec,
1027 x_tgdv_rec);
1028 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1029 return;
1030 end if;
1031 migrate(x_tgdv_rec,x_tgdv_ext_rec);
1032 EXCEPTION
1033 WHEN OTHERS THEN
1034 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1035 (l_api_name,
1036 G_PKG_NAME,
1037 'OTHERS',
1038 x_msg_count,
1039 x_msg_data,
1040 '_PVT');
1041 END UPDATE_TPG_DELIMITED;
1042 PROCEDURE DELETE_TPG_DELIMITED(
1043 p_api_version IN NUMBER,
1044 p_init_msg_list IN VARCHAR2 ,
1045 x_return_status OUT NOCOPY VARCHAR2,
1046 x_msg_count OUT NOCOPY NUMBER,
1047 x_msg_data OUT NOCOPY VARCHAR2,
1048 p_tgdv_ext_rec IN tgdv_extended_rec_type) IS
1049 p_tgdv_rec tgdv_rec_type;
1050 x_tgdv_rec tgdv_rec_type;
1051 p_isev_ext_rec isev_extended_rec_type;
1052 x_isev_ext_rec isev_extended_rec_type;
1053 l_row_notfound BOOLEAN := TRUE;
1054 CURSOR okc_limited_csr (p_id IN NUMBER) IS
1055 SELECT id, tve_id_limited
1056 FROM okc_timevalues
1057 WHERE id = p_id
1058 AND tve_type = 'TGD';
1059 l_okc_limited okc_limited_csr%ROWTYPE;
1060 BEGIN
1061 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1062 OPEN okc_limited_csr(p_tgdv_ext_rec.id);
1063 FETCH okc_limited_csr INTO l_okc_limited;
1064 l_row_notfound := okc_limited_csr%NOTFOUND;
1065 CLOSE okc_limited_csr;
1066 if l_okc_limited.tve_id_limited is not null and
1067 l_okc_limited.tve_id_limited <> OKC_API.G_MISS_NUM then
1068 p_isev_ext_rec.id := l_okc_limited.tve_id_limited;
1069 OKC_TIME_pub.delete_ia_startend(
1070 p_api_version,
1071 p_init_msg_list,
1072 x_return_status,
1073 x_msg_count,
1074 x_msg_data,
1075 p_isev_ext_rec);
1076 end if;
1077 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1078 return;
1079 end if;
1080 migrate(p_tgdv_ext_rec,p_tgdv_rec);
1081 okc_tgd_pvt.delete_row(
1082 p_api_version,
1083 p_init_msg_list,
1084 x_return_status,
1085 x_msg_count,
1086 x_msg_data,
1087 p_tgdv_rec);
1088 END DELETE_TPG_DELIMITED;
1089 PROCEDURE LOCK_TPG_DELIMITED(
1090 p_api_version IN NUMBER,
1091 p_init_msg_list IN VARCHAR2 ,
1092 x_return_status OUT NOCOPY VARCHAR2,
1093 x_msg_count OUT NOCOPY NUMBER,
1094 x_msg_data OUT NOCOPY VARCHAR2,
1095 p_tgdv_ext_rec IN tgdv_extended_rec_type) IS
1096 p_tgdv_rec tgdv_rec_type;
1097 BEGIN
1098 migrate(p_tgdv_ext_rec,p_tgdv_rec);
1099 okc_tgd_pvt.lock_row(
1100 p_api_version,
1101 p_init_msg_list,
1102 x_return_status,
1103 x_msg_count,
1104 x_msg_data,
1105 p_tgdv_rec);
1106 END LOCK_TPG_DELIMITED;
1107
1108 PROCEDURE VALID_TPG_DELIMITED(
1109 p_api_version IN NUMBER,
1110 p_init_msg_list IN VARCHAR2 ,
1111 x_return_status OUT NOCOPY VARCHAR2,
1112 x_msg_count OUT NOCOPY NUMBER,
1113 x_msg_data OUT NOCOPY VARCHAR2,
1114 p_tgdv_ext_rec IN tgdv_extended_rec_type) IS
1115 p_tgdv_rec tgdv_rec_type;
1116 BEGIN
1117 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1118 migrate(p_tgdv_ext_rec,p_tgdv_rec);
1119 okc_tgd_pvt.validate_row(
1120 p_api_version,
1121 p_init_msg_list,
1122 x_return_status,
1123 x_msg_count,
1124 x_msg_data,
1125 p_tgdv_rec);
1126 END VALID_TPG_DELIMITED;
1127
1128 --------------------------------------------------------------------------
1129 ---The following procedures cater to handling of OKC_TIME_TPG_NAMED
1130 --------------------------------------------------------------------------
1131
1132 PROCEDURE CREATE_TPG_NAMED(
1133 p_api_version IN NUMBER,
1134 p_init_msg_list IN VARCHAR2 ,
1135 x_return_status OUT NOCOPY VARCHAR2,
1136 x_msg_count OUT NOCOPY NUMBER,
1137 x_msg_data OUT NOCOPY VARCHAR2,
1138 p_tgnv_rec IN tgnv_rec_type,
1139 x_tgnv_rec OUT NOCOPY tgnv_rec_type) IS
1140 BEGIN
1141 okc_tgn_pvt.insert_row(
1142 p_api_version,
1143 p_init_msg_list,
1144 x_return_status,
1145 x_msg_count,
1146 x_msg_data,
1147 p_tgnv_rec,
1148 x_tgnv_rec);
1149 END CREATE_TPG_NAMED;
1150 PROCEDURE UPDATE_TPG_NAMED(
1151 p_api_version IN NUMBER,
1152 p_init_msg_list IN VARCHAR2 ,
1153 x_return_status OUT NOCOPY VARCHAR2,
1154 x_msg_count OUT NOCOPY NUMBER,
1155 x_msg_data OUT NOCOPY VARCHAR2,
1156 p_tgnv_rec IN tgnv_rec_type,
1157 x_tgnv_rec OUT NOCOPY tgnv_rec_type) IS
1158 BEGIN
1159 okc_tgn_pvt.update_row(
1160 p_api_version,
1161 p_init_msg_list,
1162 x_return_status,
1163 x_msg_count,
1164 x_msg_data,
1165 p_tgnv_rec,
1166 x_tgnv_rec);
1167 END UPDATE_TPG_NAMED;
1168 PROCEDURE DELETE_TPG_NAMED(
1169 p_api_version IN NUMBER,
1170 p_init_msg_list IN VARCHAR2 ,
1171 x_return_status OUT NOCOPY VARCHAR2,
1172 x_msg_count OUT NOCOPY NUMBER,
1173 x_msg_data OUT NOCOPY VARCHAR2,
1174 p_tgnv_rec IN tgnv_rec_type) IS
1175 BEGIN
1176 okc_tgn_pvt.delete_row(
1177 p_api_version,
1178 p_init_msg_list,
1179 x_return_status,
1180 x_msg_count,
1181 x_msg_data,
1182 p_tgnv_rec);
1183 END DELETE_TPG_NAMED;
1184 PROCEDURE LOCK_TPG_NAMED(
1185 p_api_version IN NUMBER,
1186 p_init_msg_list IN VARCHAR2 ,
1187 x_return_status OUT NOCOPY VARCHAR2,
1188 x_msg_count OUT NOCOPY NUMBER,
1189 x_msg_data OUT NOCOPY VARCHAR2,
1190 p_tgnv_rec IN tgnv_rec_type) IS
1191 BEGIN
1192 okc_tgn_pvt.lock_row(
1193 p_api_version,
1194 p_init_msg_list,
1195 x_return_status,
1196 x_msg_count,
1197 x_msg_data,
1198 p_tgnv_rec);
1199 END LOCK_TPG_NAMED;
1200
1201 PROCEDURE VALID_TPG_NAMED(
1202 p_api_version IN NUMBER,
1203 p_init_msg_list IN VARCHAR2 ,
1204 x_return_status OUT NOCOPY VARCHAR2,
1205 x_msg_count OUT NOCOPY NUMBER,
1206 x_msg_data OUT NOCOPY VARCHAR2,
1207 p_tgnv_rec IN tgnv_rec_type) IS
1208 BEGIN
1209 okc_tgn_pvt.validate_row(
1210 p_api_version,
1211 p_init_msg_list,
1212 x_return_status,
1213 x_msg_count,
1214 x_msg_data,
1215 p_tgnv_rec);
1216 END VALID_TPG_NAMED;
1217
1218
1219 --------------------------------------------------------------------------
1220 ---The following procedures cater to handling of OKC_TIME_IA_STARTEND
1221 --------------------------------------------------------------------------
1222 PROCEDURE migrate (
1223 p_from IN isev_extended_rec_type,
1224 p_to IN OUT NOCOPY isev_rec_type
1225 ) IS
1226 BEGIN
1227 --Bug 3122962 p_to.sfwt_flag := p_from.sfwt_flag;
1228 p_to.description := p_from.description;
1229 p_to.short_description := p_from.short_description;
1230 p_to.comments := p_from.comments;
1231 p_to.id := p_from.id;
1232 p_to.spn_id := p_from.spn_id;
1233 p_to.tve_id_limited := p_from.tve_id_limited;
1234 p_to.tze_id := p_from.tze_id;
1235 p_to.dnz_chr_id := p_from.dnz_chr_id;
1236 p_to.tve_id_started := p_from.tve_id_started;
1237 p_to.tve_id_ended := p_from.tve_id_ended;
1238 p_to.duration := p_from.duration;
1239 p_to.uom_code := p_from.uom_code;
1240 p_to.before_after := p_from.before_after;
1241 p_to.object_version_number := p_from.object_version_number;
1242 p_to.created_by := p_from.created_by;
1243 p_to.creation_date := p_from.creation_date;
1244 p_to.last_updated_by := p_from.last_updated_by;
1245 p_to.last_update_date := p_from.last_update_date;
1246 p_to.operator := p_from.operator;
1247 p_to.last_update_login := p_from.last_update_login;
1248 p_to.attribute_category := p_from.attribute_category;
1249 p_to.attribute1 := p_from.attribute1;
1250 p_to.attribute2 := p_from.attribute2;
1251 p_to.attribute3 := p_from.attribute3;
1252 p_to.attribute4 := p_from.attribute4;
1253 p_to.attribute5 := p_from.attribute5;
1254 p_to.attribute6 := p_from.attribute6;
1255 p_to.attribute7 := p_from.attribute7;
1256 p_to.attribute8 := p_from.attribute8;
1257 p_to.attribute9 := p_from.attribute9;
1258 p_to.attribute10 := p_from.attribute10;
1259 p_to.attribute11 := p_from.attribute11;
1260 p_to.attribute12 := p_from.attribute12;
1261 p_to.attribute13 := p_from.attribute13;
1262 p_to.attribute14 := p_from.attribute14;
1263 p_to.attribute15 := p_from.attribute15;
1264 END migrate;
1265
1266 PROCEDURE migrate (
1267 p_from IN isev_rec_type,
1268 p_to IN OUT NOCOPY isev_extended_rec_type
1269 ) IS
1270 BEGIN
1271 --Bug 3122962 p_to.sfwt_flag := p_from.sfwt_flag;
1272 p_to.description := p_from.description;
1273 p_to.short_description := p_from.short_description;
1274 p_to.comments := p_from.comments;
1275 p_to.id := p_from.id;
1276 p_to.spn_id := p_from.spn_id;
1277 p_to.tve_id_limited := p_from.tve_id_limited;
1278 p_to.tze_id := p_from.tze_id;
1279 p_to.dnz_chr_id := p_from.dnz_chr_id;
1280 p_to.tve_id_started := p_from.tve_id_started;
1281 p_to.tve_id_ended := p_from.tve_id_ended;
1282 p_to.duration := p_from.duration;
1283 p_to.uom_code := p_from.uom_code;
1284 p_to.before_after := p_from.before_after;
1285 p_to.object_version_number := p_from.object_version_number;
1286 p_to.created_by := p_from.created_by;
1287 p_to.creation_date := p_from.creation_date;
1288 p_to.last_updated_by := p_from.last_updated_by;
1289 p_to.last_update_date := p_from.last_update_date;
1290 p_to.operator := p_from.operator;
1291 p_to.last_update_login := p_from.last_update_login;
1292 p_to.attribute_category := p_from.attribute_category;
1293 p_to.attribute1 := p_from.attribute1;
1294 p_to.attribute2 := p_from.attribute2;
1295 p_to.attribute3 := p_from.attribute3;
1296 p_to.attribute4 := p_from.attribute4;
1297 p_to.attribute5 := p_from.attribute5;
1298 p_to.attribute6 := p_from.attribute6;
1299 p_to.attribute7 := p_from.attribute7;
1300 p_to.attribute8 := p_from.attribute8;
1301 p_to.attribute9 := p_from.attribute9;
1302 p_to.attribute10 := p_from.attribute10;
1303 p_to.attribute11 := p_from.attribute11;
1304 p_to.attribute12 := p_from.attribute12;
1305 p_to.attribute13 := p_from.attribute13;
1306 p_to.attribute14 := p_from.attribute14;
1307 p_to.attribute15 := p_from.attribute15;
1308 END migrate;
1309
1310 PROCEDURE migrate (
1311 p_from IN isev_reltv_rec_type,
1312 p_to IN OUT NOCOPY isev_rec_type
1313 ) IS
1314 BEGIN
1315 --Bug 3122962 p_to.sfwt_flag := p_from.sfwt_flag;
1316 p_to.description := p_from.description;
1317 p_to.short_description := p_from.short_description;
1318 p_to.comments := p_from.comments;
1319 p_to.id := p_from.id;
1320 p_to.spn_id := p_from.spn_id;
1321 p_to.tve_id_limited := p_from.tve_id_limited;
1322 p_to.tze_id := p_from.tze_id;
1323 p_to.dnz_chr_id := p_from.dnz_chr_id;
1324 p_to.tve_id_started := p_from.tve_id_started;
1325 p_to.tve_id_ended := p_from.tve_id_ended;
1326 p_to.duration := p_from.duration;
1327 p_to.uom_code := p_from.uom_code;
1328 p_to.before_after := p_from.before_after;
1329 p_to.object_version_number := p_from.object_version_number;
1330 p_to.created_by := p_from.created_by;
1331 p_to.creation_date := p_from.creation_date;
1332 p_to.last_updated_by := p_from.last_updated_by;
1333 p_to.last_update_date := p_from.last_update_date;
1334 p_to.operator := p_from.operator;
1335 p_to.last_update_login := p_from.last_update_login;
1336 p_to.attribute_category := p_from.attribute_category;
1337 p_to.attribute1 := p_from.attribute1;
1338 p_to.attribute2 := p_from.attribute2;
1339 p_to.attribute3 := p_from.attribute3;
1340 p_to.attribute4 := p_from.attribute4;
1341 p_to.attribute5 := p_from.attribute5;
1342 p_to.attribute6 := p_from.attribute6;
1343 p_to.attribute7 := p_from.attribute7;
1344 p_to.attribute8 := p_from.attribute8;
1345 p_to.attribute9 := p_from.attribute9;
1346 p_to.attribute10 := p_from.attribute10;
1347 p_to.attribute11 := p_from.attribute11;
1348 p_to.attribute12 := p_from.attribute12;
1349 p_to.attribute13 := p_from.attribute13;
1350 p_to.attribute14 := p_from.attribute14;
1351 p_to.attribute15 := p_from.attribute15;
1352 END migrate;
1353
1354 PROCEDURE migrate (
1355 p_from IN isev_rec_type,
1356 p_to IN OUT NOCOPY isev_reltv_rec_type
1357 ) IS
1358 BEGIN
1359 --Bug 3122962 p_to.sfwt_flag := p_from.sfwt_flag;
1360 p_to.description := p_from.description;
1361 p_to.short_description := p_from.short_description;
1362 p_to.comments := p_from.comments;
1363 p_to.id := p_from.id;
1364 p_to.spn_id := p_from.spn_id;
1365 p_to.tve_id_limited := p_from.tve_id_limited;
1366 p_to.tze_id := p_from.tze_id;
1367 p_to.dnz_chr_id := p_from.dnz_chr_id;
1368 p_to.tve_id_started := p_from.tve_id_started;
1369 p_to.tve_id_ended := p_from.tve_id_ended;
1370 p_to.duration := p_from.duration;
1371 p_to.uom_code := p_from.uom_code;
1372 p_to.before_after := p_from.before_after;
1373 p_to.object_version_number := p_from.object_version_number;
1374 p_to.created_by := p_from.created_by;
1375 p_to.creation_date := p_from.creation_date;
1376 p_to.last_updated_by := p_from.last_updated_by;
1377 p_to.last_update_date := p_from.last_update_date;
1378 p_to.operator := p_from.operator;
1379 p_to.last_update_login := p_from.last_update_login;
1380 p_to.attribute_category := p_from.attribute_category;
1381 p_to.attribute1 := p_from.attribute1;
1382 p_to.attribute2 := p_from.attribute2;
1383 p_to.attribute3 := p_from.attribute3;
1384 p_to.attribute4 := p_from.attribute4;
1385 p_to.attribute5 := p_from.attribute5;
1386 p_to.attribute6 := p_from.attribute6;
1387 p_to.attribute7 := p_from.attribute7;
1388 p_to.attribute8 := p_from.attribute8;
1389 p_to.attribute9 := p_from.attribute9;
1390 p_to.attribute10 := p_from.attribute10;
1391 p_to.attribute11 := p_from.attribute11;
1392 p_to.attribute12 := p_from.attribute12;
1393 p_to.attribute13 := p_from.attribute13;
1394 p_to.attribute14 := p_from.attribute14;
1395 p_to.attribute15 := p_from.attribute15;
1396 END migrate;
1397
1398 PROCEDURE CREATE_IA_STARTEND(
1399 p_api_version IN NUMBER,
1400 p_init_msg_list IN VARCHAR2 ,
1401 x_return_status OUT NOCOPY VARCHAR2,
1402 x_msg_count OUT NOCOPY NUMBER,
1403 x_msg_data OUT NOCOPY VARCHAR2,
1404 p_isev_ext_rec IN isev_extended_rec_type,
1405 x_isev_ext_rec OUT NOCOPY isev_extended_rec_type) IS
1406 p_tavv_rec tavv_rec_type;
1407 x_tavv_rec tavv_rec_type;
1408 p_isev_rec isev_rec_type;
1409 x_isev_rec isev_rec_type;
1410 BEGIN
1411 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1412 if (p_isev_ext_rec.start_date > p_isev_ext_rec.end_date) and
1413 (p_isev_ext_rec.end_date is NOT NULL and p_isev_ext_rec.end_date <> OKC_API.G_MISS_DATE) then
1414 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1415 p_msg_name => G_DATE_ERROR,
1416 p_token1 => G_COL_NAME_TOKEN,
1417 p_token1_value => 'START_DATE');
1418 x_return_status := OKC_API.G_RET_STS_ERROR;
1419 return;
1420 end if;
1421 x_isev_ext_rec := p_isev_ext_rec;
1422 p_tavv_rec.datetime := p_isev_ext_rec.start_date;
1423 p_tavv_rec.description := 'Start date of Absolute Interval';
1424 p_tavv_rec.short_description := 'Start of Abs Intrvl';
1425 p_tavv_rec.comments := 'Generated by ISE';
1426 p_tavv_rec.dnz_chr_id := p_isev_ext_rec.dnz_chr_id;
1427 OKC_TIME_PUB.create_tpa_value(
1428 p_api_version,
1429 p_init_msg_list,
1430 x_return_status,
1431 x_msg_count,
1432 x_msg_data,
1433 p_tavv_rec,
1434 x_tavv_rec);
1435 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1436 return;
1437 end if;
1438 x_isev_ext_rec.tve_id_started := x_tavv_rec.id;
1439 if p_isev_ext_rec.duration is null or
1440 p_isev_ext_rec.duration = OKC_API.G_MISS_NUM then
1441 if p_isev_ext_rec.end_date is NOT NULL and
1442 p_isev_ext_rec.end_date <> OKC_API.G_MISS_DATE then
1443 okc_time_util_pub.get_duration(p_isev_ext_rec.start_date, p_isev_ext_rec.end_date,x_isev_ext_rec.duration,
1444 x_isev_ext_rec.uom_code,x_return_status);
1445 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1446 return;
1447 end if;
1448 else
1449 x_isev_ext_rec.duration := NULL;
1450 x_isev_ext_rec.uom_code := NULL;
1451 end if;
1452 end if;
1453 migrate(x_isev_ext_rec,p_isev_rec);
1454 okc_ise_pvt.insert_row(
1455 p_api_version,
1456 p_init_msg_list,
1457 x_return_status,
1458 x_msg_count,
1459 x_msg_data,
1460 p_isev_rec,
1461 x_isev_rec);
1462 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1463 return;
1464 end if;
1465 migrate(x_isev_rec,x_isev_ext_rec);
1466 END CREATE_IA_STARTEND;
1467
1468 PROCEDURE UPDATE_IA_STARTEND(
1469 p_api_version IN NUMBER,
1470 p_init_msg_list IN VARCHAR2 ,
1471 x_return_status OUT NOCOPY VARCHAR2,
1472 x_msg_count OUT NOCOPY NUMBER,
1473 x_msg_data OUT NOCOPY VARCHAR2,
1474 p_isev_ext_rec IN isev_extended_rec_type,
1475 x_isev_ext_rec OUT NOCOPY isev_extended_rec_type) IS
1476 p_tavv_rec tavv_rec_type;
1477 x_tavv_rec tavv_rec_type;
1478 p_isev_rec isev_rec_type;
1479 x_isev_rec isev_rec_type;
1480 l_row_notfound BOOLEAN := TRUE;
1481 l_api_name CONSTANT VARCHAR2(30) := 'update_ia_startend';
1482 item_not_found_error EXCEPTION;
1483 CURSOR okc_tve_csr (p_id IN NUMBER) IS
1484 SELECT start_date
1485 FROM okc_time_ia_startend_val_v
1486 WHERE id = p_id ;
1487 l_okc_tve okc_tve_csr%ROWTYPE;
1488 BEGIN
1489 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1490 if (p_isev_ext_rec.start_date > p_isev_ext_rec.end_date) and
1491 (p_isev_ext_rec.end_date is NOT NULL and p_isev_ext_rec.end_date <> OKC_API.G_MISS_DATE) then
1492 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1493 p_msg_name => G_DATE_ERROR,
1494 p_token1 => G_COL_NAME_TOKEN,
1495 p_token1_value => 'START_DATE');
1496 x_return_status := OKC_API.G_RET_STS_ERROR;
1497 return;
1498 end if;
1499 if p_isev_ext_rec.start_date is NULL then
1500 OKC_API.SET_MESSAGE(p_app_name => G_APP_NAME,
1501 p_msg_name => G_DATE_ERROR,
1502 p_token1 => G_COL_NAME_TOKEN,
1503 p_token1_value => 'START_DATE');
1504 x_return_status := OKC_API.G_RET_STS_ERROR;
1505 return;
1506 end if;
1507 x_isev_ext_rec := p_isev_ext_rec;
1508 IF (p_isev_ext_rec.start_date = OKC_API.G_MISS_DATE) then
1509 OPEN okc_tve_csr(p_isev_ext_rec.id);
1510 FETCH okc_tve_csr INTO l_okc_tve;
1511 l_row_notfound := okc_tve_csr%NOTFOUND;
1512 CLOSE okc_tve_csr;
1513 IF (l_row_notfound) THEN
1514 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TVE_ID_STARTED');
1515 RAISE item_not_found_error;
1516 END IF;
1517 x_isev_ext_rec.start_date := l_okc_tve.start_date;
1518 END IF;
1519 if p_isev_ext_rec.duration is null or
1520 p_isev_ext_rec.duration = OKC_API.G_MISS_NUM then
1521 if p_isev_ext_rec.end_date is NOT NULL and
1522 p_isev_ext_rec.end_date <> OKC_API.G_MISS_DATE then
1523 okc_time_util_pub.get_duration(x_isev_ext_rec.start_date, p_isev_ext_rec.end_date,x_isev_ext_rec.duration,
1524 x_isev_ext_rec.uom_code,x_return_status);
1525 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1526 return;
1527 end if;
1528 elsif p_isev_ext_rec.end_date is NULL then
1529 x_isev_ext_rec.duration := NULL;
1530 x_isev_ext_rec.uom_code := NULL;
1531 end if;
1532 end if;
1533 migrate(x_isev_ext_rec,p_isev_rec);
1534 okc_ise_pvt.update_row(
1535 p_api_version,
1536 p_init_msg_list,
1537 x_return_status,
1538 x_msg_count,
1539 x_msg_data,
1540 p_isev_rec,
1541 x_isev_rec);
1542 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1543 return;
1544 end if;
1545 migrate(x_isev_rec,x_isev_ext_rec);
1546 if (p_isev_ext_rec.start_date <> OKC_API.G_MISS_DATE) then
1547 p_tavv_rec.id := x_isev_rec.tve_id_started;
1548 p_tavv_rec.datetime := p_isev_ext_rec.start_date;
1549 OKC_TIME_pub.update_tpa_value(
1550 p_api_version,
1551 p_init_msg_list,
1552 x_return_status,
1553 x_msg_count,
1554 x_msg_data,
1555 p_tavv_rec,
1556 x_tavv_rec);
1557 end if;
1558 EXCEPTION
1559 WHEN item_not_found_error THEN
1560 x_return_status := OKC_API.G_RET_STS_ERROR;
1561 WHEN OTHERS THEN
1562 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1563 (l_api_name,
1564 G_PKG_NAME,
1565 'OTHERS',
1566 x_msg_count,
1567 x_msg_data,
1568 '_PVT');
1569 END UPDATE_IA_STARTEND;
1570
1571 PROCEDURE DELETE_IA_STARTEND(
1572 p_api_version IN NUMBER,
1573 p_init_msg_list IN VARCHAR2 ,
1574 x_return_status OUT NOCOPY VARCHAR2,
1575 x_msg_count OUT NOCOPY NUMBER,
1576 x_msg_data OUT NOCOPY VARCHAR2,
1577 p_isev_ext_rec IN isev_extended_rec_type) IS
1578 p_isev_rec isev_rec_type;
1579 x_isev_rec isev_rec_type;
1580 p_tavv_rec tavv_rec_type;
1581 x_tavv_rec tavv_rec_type;
1582 CURSOR okc_tve_csr (p_id IN NUMBER) IS
1583 SELECT tve_id_started
1584 FROM okc_timevalues
1585 WHERE id = p_id
1586 AND tve_type = 'ISE';
1587 l_okc_tve okc_tve_csr%ROWTYPE;
1588 l_api_name CONSTANT VARCHAR2(30) := 'delete_ia_startend';
1589 l_row_notfound BOOLEAN := TRUE;
1590 item_not_found_error EXCEPTION;
1591 BEGIN
1592 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1593 if p_isev_ext_rec.tve_id_started is NULL or
1594 p_isev_ext_rec.tve_id_started = OKC_API.G_MISS_NUM then
1595 OPEN okc_tve_csr(p_isev_ext_rec.id);
1596 FETCH okc_tve_csr INTO l_okc_tve;
1597 l_row_notfound := okc_tve_csr%NOTFOUND;
1598 CLOSE okc_tve_csr;
1599 IF (l_row_notfound) THEN
1600 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TVE_ID_STARTED');
1601 RAISE item_not_found_error;
1602 END IF;
1603 p_tavv_rec.id := l_okc_tve.tve_id_started;
1604 else
1605 p_tavv_rec.id := p_isev_ext_rec.tve_id_started;
1606 end if;
1607 OKC_TIME_pub.delete_tpa_value(
1608 p_api_version,
1609 p_init_msg_list,
1610 x_return_status,
1611 x_msg_count,
1612 x_msg_data,
1613 p_tavv_rec);
1614 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1615 return;
1616 end if;
1617 migrate(p_isev_ext_rec,p_isev_rec);
1618 okc_ise_pvt.delete_row(
1619 p_api_version,
1620 p_init_msg_list,
1621 x_return_status,
1622 x_msg_count,
1623 x_msg_data,
1624 p_isev_rec);
1625 EXCEPTION
1626 WHEN item_not_found_error THEN
1627 x_return_status := OKC_API.G_RET_STS_ERROR;
1628 WHEN OTHERS THEN
1629 x_return_status := OKC_API.HANDLE_EXCEPTIONS
1630 (l_api_name,
1631 G_PKG_NAME,
1632 'OTHERS',
1633 x_msg_count,
1634 x_msg_data,
1635 '_PVT');
1636 END DELETE_IA_STARTEND;
1637
1638 PROCEDURE LOCK_IA_STARTEND(
1639 p_api_version IN NUMBER,
1640 p_init_msg_list IN VARCHAR2 ,
1641 x_return_status OUT NOCOPY VARCHAR2,
1642 x_msg_count OUT NOCOPY NUMBER,
1643 x_msg_data OUT NOCOPY VARCHAR2,
1644 p_isev_ext_rec IN isev_extended_rec_type) IS
1645 p_isev_rec isev_rec_type;
1646 BEGIN
1647 migrate(p_isev_ext_rec,p_isev_rec);
1648 okc_ise_pvt.lock_row(
1649 p_api_version,
1650 p_init_msg_list,
1651 x_return_status,
1652 x_msg_count,
1653 x_msg_data,
1654 p_isev_rec);
1655 END LOCK_IA_STARTEND;
1656
1657 PROCEDURE VALID_IA_STARTEND(
1658 p_api_version IN NUMBER,
1659 p_init_msg_list IN VARCHAR2 ,
1660 x_return_status OUT NOCOPY VARCHAR2,
1661 x_msg_count OUT NOCOPY NUMBER,
1662 x_msg_data OUT NOCOPY VARCHAR2,
1663 p_isev_ext_rec IN isev_extended_rec_type) IS
1664 p_isev_rec isev_rec_type;
1665 BEGIN
1666 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1667 migrate(p_isev_ext_rec,p_isev_rec);
1668 okc_ise_pvt.validate_row(
1669 p_api_version,
1670 p_init_msg_list,
1671 x_return_status,
1672 x_msg_count,
1673 x_msg_data,
1674 p_isev_rec);
1675 END VALID_IA_STARTEND;
1676 PROCEDURE CREATE_IA_STARTEND(
1677 p_api_version IN NUMBER,
1678 p_init_msg_list IN VARCHAR2 ,
1679 x_return_status OUT NOCOPY VARCHAR2,
1680 x_msg_count OUT NOCOPY NUMBER,
1681 x_msg_data OUT NOCOPY VARCHAR2,
1682 p_isev_rel_rec IN isev_reltv_rec_type,
1683 x_isev_rel_rec OUT NOCOPY isev_reltv_rec_type) IS
1684 p_talv_rec talv_rec_type;
1685 x_talv_rec talv_rec_type;
1686 p_isev_rec isev_rec_type;
1687 x_isev_rec isev_rec_type;
1688 p_date date;
1689 l_api_name CONSTANT VARCHAR2(30) := 'create_ia_startend';
1690 l_row_notfound BOOLEAN := TRUE;
1691 item_not_found_error EXCEPTION;
1692 BEGIN
1693 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1694 x_isev_rel_rec := p_isev_rel_rec;
1695 p_talv_rec.tve_id_offset := p_isev_rel_rec.start_tve_id_offset;
1696 if p_isev_rel_rec.start_duration >= 0 then
1697 p_talv_rec.before_after := 'A';
1698 p_talv_rec.duration := p_isev_rel_rec.start_duration;
1699 else
1700 p_talv_rec.before_after := 'B';
1701 p_talv_rec.duration := -1 * p_isev_rel_rec.start_duration;
1702 end if;
1703 p_talv_rec.uom_code := p_isev_rel_rec.start_uom_code;
1704 p_talv_rec.operator := p_isev_rel_rec.start_operator;
1705 p_talv_rec.description := 'Start date of Relative Interval';
1706 p_talv_rec.short_description := 'Start of Rel Intrvl';
1707 p_talv_rec.comments := 'Generated by ISE';
1708 p_talv_rec.dnz_chr_id := p_isev_rel_rec.dnz_chr_id;
1709 OKC_TIME_pub.create_tpa_reltv(
1710 p_api_version,
1711 p_init_msg_list,
1712 x_return_status,
1713 x_msg_count,
1714 x_msg_data,
1715 p_talv_rec,
1716 x_talv_rec);
1717 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1718 return;
1719 end if;
1720 p_date := OKC_TIME_UTIL_PUB.get_enddate(p_isev_rel_rec.start_parent_date,
1721 p_isev_rel_rec.start_uom_code,
1722 p_isev_rel_rec.start_duration);
1723 if p_date is NULL THEN
1724 return;
1725 end if;
1726 if p_isev_rel_rec.end_date is NOT NULL and
1727 p_isev_rel_rec.end_date <> OKC_API.G_MISS_DATE then
1728 okc_time_util_pub.get_duration(p_date, p_isev_rel_rec.end_date,x_isev_rel_rec.duration,
1729 x_isev_rel_rec.uom_code,x_return_status);
1730 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1731 return;
1732 end if;
1733 else
1734 x_isev_rel_rec.duration := NULL;
1735 x_isev_rel_rec.uom_code := NULL;
1736 end if;
1737 x_isev_rel_rec.tve_id_started := x_talv_rec.id;
1738 migrate(x_isev_rel_rec,p_isev_rec);
1739 okc_ise_pvt.insert_row(
1740 p_api_version,
1741 p_init_msg_list,
1742 x_return_status,
1743 x_msg_count,
1744 x_msg_data,
1745 p_isev_rec,
1746 x_isev_rec);
1747 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1748 return;
1749 end if;
1750 migrate(x_isev_rec,x_isev_rel_rec);
1751 END CREATE_IA_STARTEND;
1752
1753 PROCEDURE UPDATE_IA_STARTEND(
1754 p_api_version IN NUMBER,
1755 p_init_msg_list IN VARCHAR2 ,
1756 x_return_status OUT NOCOPY VARCHAR2,
1757 x_msg_count OUT NOCOPY NUMBER,
1758 x_msg_data OUT NOCOPY VARCHAR2,
1759 p_isev_rel_rec IN isev_reltv_rec_type,
1760 x_isev_rel_rec OUT NOCOPY isev_reltv_rec_type) IS
1761 p_talv_rec talv_rec_type;
1762 x_talv_rec talv_rec_type;
1763 p_isev_rec isev_rec_type;
1764 x_isev_rec isev_rec_type;
1765 l_row_notfound BOOLEAN := TRUE;
1766 l_api_name CONSTANT VARCHAR2(30) := 'update_ia_startend';
1767 p_date date;
1768 BEGIN
1769 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1770 x_isev_rel_rec := p_isev_rel_rec;
1771 p_date := OKC_TIME_UTIL_PUB.get_enddate(p_isev_rel_rec.start_parent_date,
1772 p_isev_rel_rec.start_uom_code,
1773 p_isev_rel_rec.start_duration);
1774 if p_date is NULL THEN
1775 return;
1776 end if;
1777 if p_isev_rel_rec.end_date is NOT NULL and
1778 p_isev_rel_rec.end_date <> OKC_API.G_MISS_DATE then
1779 okc_time_util_pub.get_duration(p_date, p_isev_rel_rec.end_date,x_isev_rel_rec.duration,
1780 x_isev_rel_rec.uom_code,x_return_status);
1781 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1782 return;
1783 end if;
1784 elsif p_isev_rel_rec.end_date is NULL then
1785 x_isev_rel_rec.duration := NULL;
1786 x_isev_rel_rec.uom_code := NULL;
1787 end if;
1788 migrate(x_isev_rel_rec,p_isev_rec);
1789 okc_ise_pvt.update_row(
1790 p_api_version,
1791 p_init_msg_list,
1792 x_return_status,
1793 x_msg_count,
1794 x_msg_data,
1795 p_isev_rec,
1796 x_isev_rec);
1797 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1798 return;
1799 end if;
1800 migrate(x_isev_rec,x_isev_rel_rec);
1801 p_talv_rec.id := x_isev_rel_rec.tve_id_started;
1802 p_talv_rec.tve_id_offset := x_isev_rel_rec.start_tve_id_offset;
1803 if x_isev_rel_rec.start_duration >= 0 then
1804 p_talv_rec.duration := x_isev_rel_rec.start_duration;
1805 p_talv_rec.before_after := 'A';
1806 else
1807 p_talv_rec.before_after := 'B';
1808 p_talv_rec.duration := -1 * x_isev_rel_rec.start_duration;
1809 end if;
1810 p_talv_rec.uom_code := x_isev_rel_rec.start_uom_code;
1811 p_talv_rec.operator := x_isev_rel_rec.start_operator;
1812 OKC_TIME_pub.update_tpa_reltv(
1813 p_api_version,
1814 p_init_msg_list,
1815 x_return_status,
1816 x_msg_count,
1817 x_msg_data,
1818 p_talv_rec,
1819 x_talv_rec);
1820 END UPDATE_IA_STARTEND;
1821
1822 PROCEDURE DELETE_IA_STARTEND(
1823 p_api_version IN NUMBER,
1824 p_init_msg_list IN VARCHAR2 ,
1825 x_return_status OUT NOCOPY VARCHAR2,
1826 x_msg_count OUT NOCOPY NUMBER,
1827 x_msg_data OUT NOCOPY VARCHAR2,
1828 p_isev_rel_rec IN isev_reltv_rec_type) IS
1829 p_isev_rec isev_rec_type;
1830 x_isev_rec isev_rec_type;
1831 p_talv_rec talv_rec_type;
1832 x_talv_rec talv_rec_type;
1833 CURSOR okc_tve_csr (p_id IN NUMBER) IS
1834 SELECT tve_id_started
1835 FROM okc_timevalues
1836 WHERE id = p_id
1837 AND tve_type = 'ISE';
1838 l_okc_tve okc_tve_csr%ROWTYPE;
1839 l_api_name CONSTANT VARCHAR2(30) := 'delete_ia_startend';
1840 l_row_notfound BOOLEAN := TRUE;
1841 item_not_found_error EXCEPTION;
1842 BEGIN
1843 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1844 if p_isev_rel_rec.tve_id_started is NULL or
1845 p_isev_rel_rec.tve_id_started = OKC_API.G_MISS_NUM then
1846 OPEN okc_tve_csr(p_isev_rel_rec.id);
1847 FETCH okc_tve_csr INTO l_okc_tve;
1848 l_row_notfound := okc_tve_csr%NOTFOUND;
1849 CLOSE okc_tve_csr;
1850 IF (l_row_notfound) THEN
1851 OKC_API.set_message(G_APP_NAME, G_INVALID_VALUE,G_COL_NAME_TOKEN,'TVE_ID_STARTED');
1852 RAISE item_not_found_error;
1853 END IF;
1854 p_talv_rec.id := l_okc_tve.tve_id_started;
1855 else
1856 p_talv_rec.id := p_isev_rel_rec.tve_id_started;
1857 end if;
1858 OKC_TIME_pub.delete_tpa_reltv(
1859 p_api_version,
1860 p_init_msg_list,
1861 x_return_status,
1862 x_msg_count,
1863 x_msg_data,
1864 p_talv_rec);
1865 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
1866 return;
1867 end if;
1868 migrate(p_isev_rel_rec,p_isev_rec);
1869 okc_ise_pvt.delete_row(
1870 p_api_version,
1871 p_init_msg_list,
1872 x_return_status,
1873 x_msg_count,
1874 x_msg_data,
1875 p_isev_rec);
1876 END DELETE_IA_STARTEND;
1877 PROCEDURE LOCK_IA_STARTEND(
1878 p_api_version IN NUMBER,
1879 p_init_msg_list IN VARCHAR2 ,
1880 x_return_status OUT NOCOPY VARCHAR2,
1881 x_msg_count OUT NOCOPY NUMBER,
1882 x_msg_data OUT NOCOPY VARCHAR2,
1883 p_isev_rel_rec IN isev_reltv_rec_type) IS
1884 p_isev_rec isev_rec_type;
1885 BEGIN
1886 migrate(p_isev_rel_rec,p_isev_rec);
1887 okc_ise_pvt.lock_row(
1888 p_api_version,
1889 p_init_msg_list,
1890 x_return_status,
1891 x_msg_count,
1892 x_msg_data,
1893 p_isev_rec);
1894 END LOCK_IA_STARTEND;
1895
1896 PROCEDURE VALID_IA_STARTEND(
1897 p_api_version IN NUMBER,
1898 p_init_msg_list IN VARCHAR2 ,
1899 x_return_status OUT NOCOPY VARCHAR2,
1900 x_msg_count OUT NOCOPY NUMBER,
1901 x_msg_data OUT NOCOPY VARCHAR2,
1902 p_isev_rel_rec IN isev_reltv_rec_type) IS
1903 p_isev_rec isev_rec_type;
1904 BEGIN
1905 x_return_status := OKC_API.G_RET_STS_SUCCESS;
1906 migrate(p_isev_rel_rec,p_isev_rec);
1907 okc_ise_pvt.validate_row(
1908 p_api_version,
1909 p_init_msg_list,
1910 x_return_status,
1911 x_msg_count,
1912 x_msg_data,
1913 p_isev_rec);
1914 END VALID_IA_STARTEND;
1915 --------------------------------------------------------------------------
1916 ---The following procedures cater to handling of OKC_TIME_IG_STARTEND
1917 --------------------------------------------------------------------------
1918
1919 PROCEDURE migrate (
1920 p_from IN igsv_rec_type,
1921 p_to IN OUT NOCOPY igsv_extended_rec_type
1922 ) IS
1923 BEGIN
1924 --Bug 3122962 p_to.sfwt_flag := p_from.sfwt_flag;
1925 p_to.description := p_from.description;
1926 p_to.short_description := p_from.short_description;
1927 p_to.comments := p_from.comments;
1928 p_to.id := p_from.id;
1929 p_to.tve_id_started := p_from.tve_id_started;
1930 p_to.tve_id_ended := p_from.tve_id_ended;
1931 p_to.tve_id_limited := p_from.tve_id_limited;
1932 p_to.tze_id := p_from.tze_id;
1933 p_to.dnz_chr_id := p_from.dnz_chr_id;
1934 p_to.object_version_number := p_from.object_version_number;
1935 p_to.created_by := p_from.created_by;
1936 p_to.creation_date := p_from.creation_date;
1937 p_to.last_updated_by := p_from.last_updated_by;
1938 p_to.last_update_date := p_from.last_update_date;
1939 p_to.last_update_login := p_from.last_update_login;
1940 p_to.attribute_category := p_from.attribute_category;
1941 p_to.attribute1 := p_from.attribute1;
1942 p_to.attribute2 := p_from.attribute2;
1943 p_to.attribute3 := p_from.attribute3;
1944 p_to.attribute4 := p_from.attribute4;
1945 p_to.attribute5 := p_from.attribute5;
1946 p_to.attribute6 := p_from.attribute6;
1947 p_to.attribute7 := p_from.attribute7;
1948 p_to.attribute8 := p_from.attribute8;
1949 p_to.attribute9 := p_from.attribute9;
1950 p_to.attribute10 := p_from.attribute10;
1951 p_to.attribute11 := p_from.attribute11;
1952 p_to.attribute12 := p_from.attribute12;
1953 p_to.attribute13 := p_from.attribute13;
1954 p_to.attribute14 := p_from.attribute14;
1955 p_to.attribute15 := p_from.attribute15;
1956 END migrate;
1957
1958 PROCEDURE migrate (
1959 p_from IN igsv_extended_rec_type,
1960 p_to IN OUT NOCOPY igsv_rec_type
1961 ) IS
1962 BEGIN
1963 --Bug 3122962 p_to.sfwt_flag := p_from.sfwt_flag;
1964 p_to.description := p_from.description;
1965 p_to.short_description := p_from.short_description;
1966 p_to.comments := p_from.comments;
1967 p_to.id := p_from.id;
1968 p_to.tve_id_started := p_from.tve_id_started;
1969 p_to.tve_id_ended := p_from.tve_id_ended;
1970 p_to.tve_id_limited := p_from.tve_id_limited;
1971 p_to.tze_id := p_from.tze_id;
1972 p_to.dnz_chr_id := p_from.dnz_chr_id;
1973 p_to.object_version_number := p_from.object_version_number;
1974 p_to.created_by := p_from.created_by;
1975 p_to.creation_date := p_from.creation_date;
1976 p_to.last_updated_by := p_from.last_updated_by;
1977 p_to.last_update_date := p_from.last_update_date;
1978 p_to.last_update_login := p_from.last_update_login;
1979 p_to.attribute_category := p_from.attribute_category;
1980 p_to.attribute1 := p_from.attribute1;
1981 p_to.attribute2 := p_from.attribute2;
1982 p_to.attribute3 := p_from.attribute3;
1983 p_to.attribute4 := p_from.attribute4;
1984 p_to.attribute5 := p_from.attribute5;
1985 p_to.attribute6 := p_from.attribute6;
1986 p_to.attribute7 := p_from.attribute7;
1987 p_to.attribute8 := p_from.attribute8;
1988 p_to.attribute9 := p_from.attribute9;
1989 p_to.attribute10 := p_from.attribute10;
1990 p_to.attribute11 := p_from.attribute11;
1991 p_to.attribute12 := p_from.attribute12;
1992 p_to.attribute13 := p_from.attribute13;
1993 p_to.attribute14 := p_from.attribute14;
1994 p_to.attribute15 := p_from.attribute15;
1995 END migrate;
1996
1997 PROCEDURE CREATE_IG_STARTEND(
1998 p_api_version IN NUMBER,
1999 p_init_msg_list IN VARCHAR2 ,
2000 x_return_status OUT NOCOPY VARCHAR2,
2001 x_msg_count OUT NOCOPY NUMBER,
2002 x_msg_data OUT NOCOPY VARCHAR2,
2003 p_igsv_ext_rec IN igsv_extended_rec_type,
2004 x_igsv_ext_rec OUT NOCOPY igsv_extended_rec_type) IS
2005 p_tgdv_ext_rec tgdv_extended_rec_type;
2006 x_tgdv_ext_rec tgdv_extended_rec_type;
2007 p_igsv_rec igsv_rec_type;
2008 x_igsv_rec igsv_rec_type;
2009 BEGIN
2010 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2011 x_igsv_ext_rec := p_igsv_ext_rec;
2012 p_tgdv_ext_rec.month := p_igsv_ext_rec.start_month;
2013 p_tgdv_ext_rec.day := p_igsv_ext_rec.start_day;
2014 p_tgdv_ext_rec.day_of_week := p_igsv_ext_rec.start_day_of_week;
2015 p_tgdv_ext_rec.hour := p_igsv_ext_rec.start_hour;
2016 p_tgdv_ext_rec.minute := p_igsv_ext_rec.start_minute;
2017 p_tgdv_ext_rec.second := p_igsv_ext_rec.start_second;
2018 p_tgdv_ext_rec.nth := p_igsv_ext_rec.start_nth;
2019 p_tgdv_ext_rec.description := 'Start of Generic Interval Startend';
2020 p_tgdv_ext_rec.short_description := 'Start Generic Intrvl';
2021 p_tgdv_ext_rec.comments := 'Generated by IGS';
2022 p_tgdv_ext_rec.dnz_chr_id := p_igsv_ext_rec.dnz_chr_id;
2023 OKC_TIME_pub.create_tpg_delimited(
2024 p_api_version,
2025 p_init_msg_list,
2026 x_return_status,
2027 x_msg_count,
2028 x_msg_data,
2029 p_tgdv_ext_rec,
2030 x_tgdv_ext_rec);
2031 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2032 return;
2033 end if;
2034 x_igsv_ext_rec.tve_id_started := x_tgdv_ext_rec.id;
2035 p_tgdv_ext_rec.month := p_igsv_ext_rec.end_month;
2036 p_tgdv_ext_rec.day := p_igsv_ext_rec.end_day;
2037 p_tgdv_ext_rec.day_of_week := p_igsv_ext_rec.end_day_of_week;
2038 p_tgdv_ext_rec.hour := p_igsv_ext_rec.end_hour;
2039 p_tgdv_ext_rec.minute := p_igsv_ext_rec.end_minute;
2040 p_tgdv_ext_rec.second := p_igsv_ext_rec.end_second;
2041 p_tgdv_ext_rec.nth := p_igsv_ext_rec.end_nth;
2042 p_tgdv_ext_rec.description := 'End of Generic Interval Startend';
2043 p_tgdv_ext_rec.short_description := 'End Generic Intrvl';
2044 p_tgdv_ext_rec.comments := 'Generated by IGS';
2045 p_tgdv_ext_rec.dnz_chr_id := p_igsv_ext_rec.dnz_chr_id;
2046 OKC_TIME_pub.create_tpg_delimited(
2047 p_api_version,
2048 p_init_msg_list,
2049 x_return_status,
2050 x_msg_count,
2051 x_msg_data,
2052 p_tgdv_ext_rec,
2053 x_tgdv_ext_rec);
2054 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2055 return;
2056 end if;
2057 x_igsv_ext_rec.tve_id_ended := x_tgdv_ext_rec.id;
2058 migrate(x_igsv_ext_rec,p_igsv_rec);
2059 okc_igs_pvt.insert_row(
2060 p_api_version,
2061 p_init_msg_list,
2062 x_return_status,
2063 x_msg_count,
2064 x_msg_data,
2065 p_igsv_rec,
2066 x_igsv_rec);
2067 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2068 return;
2069 end if;
2070 migrate(x_igsv_rec,x_igsv_ext_rec);
2071 END CREATE_IG_STARTEND;
2072
2073 PROCEDURE UPDATE_IG_STARTEND(
2074 p_api_version IN NUMBER,
2075 p_init_msg_list IN VARCHAR2 ,
2076 x_return_status OUT NOCOPY VARCHAR2,
2077 x_msg_count OUT NOCOPY NUMBER,
2078 x_msg_data OUT NOCOPY VARCHAR2,
2079 p_igsv_ext_rec IN igsv_extended_rec_type,
2080 x_igsv_ext_rec OUT NOCOPY igsv_extended_rec_type) IS
2081 p_tgdv_ext_rec tgdv_extended_rec_type;
2082 x_tgdv_ext_rec tgdv_extended_rec_type;
2083 p_igsv_rec igsv_rec_type;
2084 x_igsv_rec igsv_rec_type;
2085 BEGIN
2086 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2087 x_igsv_ext_rec := p_igsv_ext_rec;
2088 migrate(p_igsv_ext_rec,p_igsv_rec);
2089 okc_igs_pvt.update_row(
2090 p_api_version,
2091 p_init_msg_list,
2092 x_return_status,
2093 x_msg_count,
2094 x_msg_data,
2095 p_igsv_rec,
2096 x_igsv_rec);
2097 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2098 return;
2099 end if;
2100 migrate(x_igsv_rec,x_igsv_ext_rec);
2101 p_tgdv_ext_rec.id := x_igsv_ext_rec.tve_id_started;
2102 p_tgdv_ext_rec.month := p_igsv_ext_rec.start_month;
2103 p_tgdv_ext_rec.day := p_igsv_ext_rec.start_day;
2104 p_tgdv_ext_rec.day_of_week := p_igsv_ext_rec.start_day_of_week;
2105 p_tgdv_ext_rec.hour := p_igsv_ext_rec.start_hour;
2106 p_tgdv_ext_rec.minute := p_igsv_ext_rec.start_minute;
2107 p_tgdv_ext_rec.second := p_igsv_ext_rec.start_second;
2108 p_tgdv_ext_rec.nth := p_igsv_ext_rec.start_nth;
2109 OKC_TIME_pub.update_tpg_delimited(
2110 p_api_version,
2111 p_init_msg_list,
2112 x_return_status,
2113 x_msg_count,
2114 x_msg_data,
2115 p_tgdv_ext_rec,
2116 x_tgdv_ext_rec);
2117 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2118 return;
2119 end if;
2120 p_tgdv_ext_rec.id := x_igsv_ext_rec.tve_id_ended;
2121 p_tgdv_ext_rec.month := p_igsv_ext_rec.end_month;
2122 p_tgdv_ext_rec.day := p_igsv_ext_rec.end_day;
2123 p_tgdv_ext_rec.day_of_week := p_igsv_ext_rec.end_day_of_week;
2124 p_tgdv_ext_rec.hour := p_igsv_ext_rec.end_hour;
2125 p_tgdv_ext_rec.minute := p_igsv_ext_rec.end_minute;
2126 p_tgdv_ext_rec.second := p_igsv_ext_rec.end_second;
2127 p_tgdv_ext_rec.nth := p_igsv_ext_rec.end_nth;
2128 OKC_TIME_pub.update_tpg_delimited(
2129 p_api_version,
2130 p_init_msg_list,
2131 x_return_status,
2132 x_msg_count,
2133 x_msg_data,
2134 p_tgdv_ext_rec,
2135 x_tgdv_ext_rec);
2136 END UPDATE_IG_STARTEND;
2137 PROCEDURE DELETE_IG_STARTEND(
2138 p_api_version IN NUMBER,
2139 p_init_msg_list IN VARCHAR2 ,
2140 x_return_status OUT NOCOPY VARCHAR2,
2141 x_msg_count OUT NOCOPY NUMBER,
2142 x_msg_data OUT NOCOPY VARCHAR2,
2143 p_igsv_ext_rec IN igsv_extended_rec_type) IS
2144 p_igsv_rec igsv_rec_type;
2145 x_igsv_rec igsv_rec_type;
2146 p_tgdv_ext_rec tgdv_extended_rec_type;
2147 x_tgdv_ext_rec tgdv_extended_rec_type;
2148 BEGIN
2149 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2150 migrate(p_igsv_ext_rec,p_igsv_rec);
2151 okc_igs_pvt.delete_row(
2152 p_api_version,
2153 p_init_msg_list,
2154 x_return_status,
2155 x_msg_count,
2156 x_msg_data,
2157 p_igsv_rec);
2158 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2159 return;
2160 end if;
2161 p_tgdv_ext_rec.id := p_igsv_rec.tve_id_started;
2162 OKC_TIME_pub.delete_tpg_delimited(
2163 p_api_version,
2164 p_init_msg_list,
2165 x_return_status,
2166 x_msg_count,
2167 x_msg_data,
2168 p_tgdv_ext_rec);
2169 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2170 return;
2171 end if;
2172 p_tgdv_ext_rec.id := p_igsv_rec.tve_id_ended;
2173 OKC_TIME_pub.delete_tpg_delimited(
2174 p_api_version,
2175 p_init_msg_list,
2176 x_return_status,
2177 x_msg_count,
2178 x_msg_data,
2179 p_tgdv_ext_rec);
2180 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2181 return;
2182 end if;
2183 END DELETE_IG_STARTEND;
2184 PROCEDURE LOCK_IG_STARTEND(
2185 p_api_version IN NUMBER,
2186 p_init_msg_list IN VARCHAR2 ,
2187 x_return_status OUT NOCOPY VARCHAR2,
2188 x_msg_count OUT NOCOPY NUMBER,
2189 x_msg_data OUT NOCOPY VARCHAR2,
2190 p_igsv_ext_rec IN igsv_extended_rec_type) IS
2191 p_igsv_rec igsv_rec_type;
2192 BEGIN
2193 migrate(p_igsv_ext_rec,p_igsv_rec);
2194 okc_igs_pvt.lock_row(
2195 p_api_version,
2196 p_init_msg_list,
2197 x_return_status,
2198 x_msg_count,
2199 x_msg_data,
2200 p_igsv_rec);
2201 END LOCK_IG_STARTEND;
2202
2203 PROCEDURE VALID_IG_STARTEND(
2204 p_api_version IN NUMBER,
2205 p_init_msg_list IN VARCHAR2 ,
2206 x_return_status OUT NOCOPY VARCHAR2,
2207 x_msg_count OUT NOCOPY NUMBER,
2208 x_msg_data OUT NOCOPY VARCHAR2,
2209 p_igsv_ext_rec IN igsv_extended_rec_type) IS
2210 p_igsv_rec igsv_rec_type;
2211 BEGIN
2212 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2213 migrate(p_igsv_ext_rec,p_igsv_rec);
2214 okc_igs_pvt.validate_row(
2215 p_api_version,
2216 p_init_msg_list,
2217 x_return_status,
2218 x_msg_count,
2219 x_msg_data,
2220 p_igsv_rec);
2221 END VALID_IG_STARTEND;
2222 --------------------------------------------------------------------------
2223 ---The following procedures cater to handling of OKC_TIME_SPAN
2224 --------------------------------------------------------------------------
2225
2226 PROCEDURE CREATE_SPAN(
2227 p_api_version IN NUMBER,
2228 p_init_msg_list IN VARCHAR2 ,
2229 x_return_status OUT NOCOPY VARCHAR2,
2230 x_msg_count OUT NOCOPY NUMBER,
2231 x_msg_data OUT NOCOPY VARCHAR2,
2232 p_spnv_rec IN spnv_rec_type,
2233 x_spnv_rec OUT NOCOPY spnv_rec_type) IS
2234 BEGIN
2235 okc_spn_pvt.insert_row(
2236 p_api_version,
2237 p_init_msg_list,
2238 x_return_status,
2239 x_msg_count,
2240 x_msg_data,
2241 p_spnv_rec,
2242 x_spnv_rec);
2243 END CREATE_SPAN;
2244 PROCEDURE UPDATE_SPAN(
2245 p_api_version IN NUMBER,
2246 p_init_msg_list IN VARCHAR2 ,
2247 x_return_status OUT NOCOPY VARCHAR2,
2248 x_msg_count OUT NOCOPY NUMBER,
2249 x_msg_data OUT NOCOPY VARCHAR2,
2250 p_spnv_rec IN spnv_rec_type,
2251 x_spnv_rec OUT NOCOPY spnv_rec_type) IS
2252 BEGIN
2253 okc_spn_pvt.update_row(
2254 p_api_version,
2255 p_init_msg_list,
2256 x_return_status,
2257 x_msg_count,
2258 x_msg_data,
2259 p_spnv_rec,
2260 x_spnv_rec);
2261 END UPDATE_SPAN;
2262 PROCEDURE DELETE_SPAN(
2263 p_api_version IN NUMBER,
2264 p_init_msg_list IN VARCHAR2 ,
2265 x_return_status OUT NOCOPY VARCHAR2,
2266 x_msg_count OUT NOCOPY NUMBER,
2267 x_msg_data OUT NOCOPY VARCHAR2,
2268 p_spnv_rec IN spnv_rec_type) IS
2269 BEGIN
2270 okc_spn_pvt.delete_row(
2271 p_api_version,
2272 p_init_msg_list,
2273 x_return_status,
2274 x_msg_count,
2275 x_msg_data,
2276 p_spnv_rec);
2277 END DELETE_SPAN;
2278 PROCEDURE LOCK_SPAN(
2279 p_api_version IN NUMBER,
2280 p_init_msg_list IN VARCHAR2 ,
2281 x_return_status OUT NOCOPY VARCHAR2,
2282 x_msg_count OUT NOCOPY NUMBER,
2283 x_msg_data OUT NOCOPY VARCHAR2,
2284 p_spnv_rec IN spnv_rec_type) IS
2285 BEGIN
2286 okc_spn_pvt.lock_row(
2287 p_api_version,
2288 p_init_msg_list,
2289 x_return_status,
2290 x_msg_count,
2291 x_msg_data,
2292 p_spnv_rec);
2293 END LOCK_SPAN;
2294
2295 PROCEDURE VALID_SPAN(
2296 p_api_version IN NUMBER,
2297 p_init_msg_list IN VARCHAR2 ,
2298 x_return_status OUT NOCOPY VARCHAR2,
2299 x_msg_count OUT NOCOPY NUMBER,
2300 x_msg_data OUT NOCOPY VARCHAR2,
2301 p_spnv_rec IN spnv_rec_type) IS
2302 BEGIN
2303 okc_spn_pvt.validate_row(
2304 p_api_version,
2305 p_init_msg_list,
2306 x_return_status,
2307 x_msg_count,
2308 x_msg_data,
2309 p_spnv_rec);
2310 END VALID_SPAN;
2311 --------------------------------------------------------------------------
2312 ---The following procedures cater to handling of OKC_TIME_CODE_UNITS
2313 --------------------------------------------------------------------------
2314
2315 PROCEDURE CREATE_TIME_CODE_UNITS(
2316 p_api_version IN NUMBER,
2317 p_init_msg_list IN VARCHAR2 ,
2318 x_return_status OUT NOCOPY VARCHAR2,
2319 x_msg_count OUT NOCOPY NUMBER,
2320 x_msg_data OUT NOCOPY VARCHAR2,
2321 p_tcuv_rec IN tcuv_rec_type,
2322 x_tcuv_rec OUT NOCOPY tcuv_rec_type) IS
2323 BEGIN
2324 okc_tcu_pvt.insert_row(
2325 p_api_version,
2326 p_init_msg_list,
2327 x_return_status,
2328 x_msg_count,
2329 x_msg_data,
2330 p_tcuv_rec,
2331 x_tcuv_rec);
2332 END CREATE_TIME_CODE_UNITS;
2333 PROCEDURE UPDATE_TIME_CODE_UNITS(
2334 p_api_version IN NUMBER,
2335 p_init_msg_list IN VARCHAR2 ,
2336 x_return_status OUT NOCOPY VARCHAR2,
2337 x_msg_count OUT NOCOPY NUMBER,
2338 x_msg_data OUT NOCOPY VARCHAR2,
2339 p_tcuv_rec IN tcuv_rec_type,
2340 x_tcuv_rec OUT NOCOPY tcuv_rec_type) IS
2341 BEGIN
2342 okc_tcu_pvt.update_row(
2343 p_api_version,
2344 p_init_msg_list,
2345 x_return_status,
2346 x_msg_count,
2347 x_msg_data,
2348 p_tcuv_rec,
2349 x_tcuv_rec);
2350 END UPDATE_TIME_CODE_UNITS;
2351 PROCEDURE DELETE_TIME_CODE_UNITS(
2352 p_api_version IN NUMBER,
2353 p_init_msg_list IN VARCHAR2 ,
2354 x_return_status OUT NOCOPY VARCHAR2,
2355 x_msg_count OUT NOCOPY NUMBER,
2356 x_msg_data OUT NOCOPY VARCHAR2,
2357 p_tcuv_rec IN tcuv_rec_type) IS
2358 BEGIN
2359 okc_tcu_pvt.delete_row(
2360 p_api_version,
2361 p_init_msg_list,
2362 x_return_status,
2363 x_msg_count,
2364 x_msg_data,
2365 p_tcuv_rec);
2366 END DELETE_TIME_CODE_UNITS;
2367 PROCEDURE LOCK_TIME_CODE_UNITS(
2368 p_api_version IN NUMBER,
2369 p_init_msg_list IN VARCHAR2 ,
2370 x_return_status OUT NOCOPY VARCHAR2,
2371 x_msg_count OUT NOCOPY NUMBER,
2372 x_msg_data OUT NOCOPY VARCHAR2,
2373 p_tcuv_rec IN tcuv_rec_type) IS
2374 BEGIN
2375 okc_tcu_pvt.lock_row(
2376 p_api_version,
2377 p_init_msg_list,
2378 x_return_status,
2379 x_msg_count,
2380 x_msg_data,
2381 p_tcuv_rec);
2382 END LOCK_TIME_CODE_UNITS;
2383
2384 PROCEDURE VALID_TIME_CODE_UNITS(
2385 p_api_version IN NUMBER,
2386 p_init_msg_list IN VARCHAR2 ,
2387 x_return_status OUT NOCOPY VARCHAR2,
2388 x_msg_count OUT NOCOPY NUMBER,
2389 x_msg_data OUT NOCOPY VARCHAR2,
2390 p_tcuv_rec IN tcuv_rec_type) IS
2391 BEGIN
2392 okc_tcu_pvt.validate_row(
2393 p_api_version,
2394 p_init_msg_list,
2395 x_return_status,
2396 x_msg_count,
2397 x_msg_data,
2398 p_tcuv_rec);
2399 END VALID_TIME_CODE_UNITS;
2400
2401
2402 --------------------------------------------------------------------------
2403 ---The following procedures cater to handling of OKC_RESOLVED_TIMEVALUES
2404 --------------------------------------------------------------------------
2405
2406 PROCEDURE CREATE_RESOLVED_TIMEVALUES(
2407 p_api_version IN NUMBER,
2408 p_init_msg_list IN VARCHAR2 ,
2409 x_return_status OUT NOCOPY VARCHAR2,
2410 x_msg_count OUT NOCOPY NUMBER,
2411 x_msg_data OUT NOCOPY VARCHAR2,
2412 p_rtvv_rec IN rtvv_rec_type,
2413 x_rtvv_rec OUT NOCOPY rtvv_rec_type) IS
2414 BEGIN
2415 okc_rtv_pvt.insert_row(
2416 p_api_version,
2417 p_init_msg_list,
2418 x_return_status,
2419 x_msg_count,
2420 x_msg_data,
2421 p_rtvv_rec,
2422 x_rtvv_rec);
2423 END CREATE_RESOLVED_TIMEVALUES;
2424
2425 PROCEDURE UPDATE_RESOLVED_TIMEVALUES(
2426 p_api_version IN NUMBER,
2427 p_init_msg_list IN VARCHAR2 ,
2428 x_return_status OUT NOCOPY VARCHAR2,
2429 x_msg_count OUT NOCOPY NUMBER,
2430 x_msg_data OUT NOCOPY VARCHAR2,
2431 p_rtvv_rec IN rtvv_rec_type,
2432 x_rtvv_rec OUT NOCOPY rtvv_rec_type) IS
2433 BEGIN
2434 okc_rtv_pvt.update_row(
2435 p_api_version,
2436 p_init_msg_list,
2437 x_return_status,
2438 x_msg_count,
2439 x_msg_data,
2440 p_rtvv_rec,
2441 x_rtvv_rec);
2442 END UPDATE_RESOLVED_TIMEVALUES;
2443
2444 PROCEDURE DELETE_RESOLVED_TIMEVALUES(
2445 p_api_version IN NUMBER,
2446 p_init_msg_list IN VARCHAR2 ,
2447 x_return_status OUT NOCOPY VARCHAR2,
2448 x_msg_count OUT NOCOPY NUMBER,
2449 x_msg_data OUT NOCOPY VARCHAR2,
2450 p_rtvv_rec IN rtvv_rec_type) IS
2451 BEGIN
2452 okc_rtv_pvt.delete_row(
2453 p_api_version,
2454 p_init_msg_list,
2455 x_return_status,
2456 x_msg_count,
2457 x_msg_data,
2458 p_rtvv_rec);
2459 END DELETE_RESOLVED_TIMEVALUES;
2460
2461 PROCEDURE LOCK_RESOLVED_TIMEVALUES(
2462 p_api_version IN NUMBER,
2463 p_init_msg_list IN VARCHAR2 ,
2464 x_return_status OUT NOCOPY VARCHAR2,
2465 x_msg_count OUT NOCOPY NUMBER,
2466 x_msg_data OUT NOCOPY VARCHAR2,
2467 p_rtvv_rec IN rtvv_rec_type) IS
2468 BEGIN
2469 okc_rtv_pvt.lock_row(
2470 p_api_version,
2471 p_init_msg_list,
2472 x_return_status,
2473 x_msg_count,
2474 x_msg_data,
2475 p_rtvv_rec);
2476 END LOCK_RESOLVED_TIMEVALUES;
2477
2478 PROCEDURE VALID_RESOLVED_TIMEVALUES(
2479 p_api_version IN NUMBER,
2480 p_init_msg_list IN VARCHAR2 ,
2481 x_return_status OUT NOCOPY VARCHAR2,
2482 x_msg_count OUT NOCOPY NUMBER,
2483 x_msg_data OUT NOCOPY VARCHAR2,
2484 p_rtvv_rec IN rtvv_rec_type) IS
2485 BEGIN
2486 okc_rtv_pvt.validate_row(
2487 p_api_version,
2488 p_init_msg_list,
2489 x_return_status,
2490 x_msg_count,
2491 x_msg_data,
2492 p_rtvv_rec);
2493 END VALID_RESOLVED_TIMEVALUES;
2494 --------------------------------------------------------------------------
2495 ---The following procedures cater to handling of OKC_TIME_CYCLE
2496 --------------------------------------------------------------------------
2497 PROCEDURE migrate (
2498 p_from IN cylv_extended_rec_type,
2499 p_to IN OUT NOCOPY cylv_rec_type
2500 ) IS
2501 BEGIN
2502 --Bug 3122962 p_to.sfwt_flag := p_from.sfwt_flag;
2503 p_to.description := p_from.description;
2504 p_to.short_description := p_from.short_description;
2505 p_to.comments := p_from.comments;
2506 p_to.id := p_from.id;
2507 p_to.spn_id := p_from.spn_id;
2508 p_to.tve_id_limited := p_from.tve_id_limited;
2509 p_to.tze_id := p_from.tze_id;
2510 p_to.name := p_from.name;
2511 p_to.interval_yn := p_from.interval_yn;
2512 p_to.dnz_chr_id := p_from.dnz_chr_id;
2513 p_to.object_version_number := p_from.object_version_number;
2514 p_to.created_by := p_from.created_by;
2515 p_to.creation_date := p_from.creation_date;
2516 p_to.last_updated_by := p_from.last_updated_by;
2517 p_to.last_update_date := p_from.last_update_date;
2518 p_to.last_update_login := p_from.last_update_login;
2519 p_to.attribute_category := p_from.attribute_category;
2520 p_to.attribute1 := p_from.attribute1;
2521 p_to.attribute2 := p_from.attribute2;
2522 p_to.attribute3 := p_from.attribute3;
2523 p_to.attribute4 := p_from.attribute4;
2524 p_to.attribute5 := p_from.attribute5;
2525 p_to.attribute6 := p_from.attribute6;
2526 p_to.attribute7 := p_from.attribute7;
2527 p_to.attribute8 := p_from.attribute8;
2528 p_to.attribute9 := p_from.attribute9;
2529 p_to.attribute10 := p_from.attribute10;
2530 p_to.attribute11 := p_from.attribute11;
2531 p_to.attribute12 := p_from.attribute12;
2532 p_to.attribute13 := p_from.attribute13;
2533 p_to.attribute14 := p_from.attribute14;
2534 p_to.attribute15 := p_from.attribute15;
2535 END migrate;
2536
2537 PROCEDURE migrate (
2538 p_from IN cylv_rec_type,
2539 p_to IN OUT NOCOPY cylv_extended_rec_type
2540 ) IS
2541 BEGIN
2542 --Bug 3122962 p_to.sfwt_flag := p_from.sfwt_flag;
2543 p_to.description := p_from.description;
2544 p_to.short_description := p_from.short_description;
2545 p_to.comments := p_from.comments;
2546 p_to.id := p_from.id;
2547 p_to.spn_id := p_from.spn_id;
2548 p_to.tve_id_limited := p_from.tve_id_limited;
2549 p_to.tze_id := p_from.tze_id;
2550 p_to.name := p_from.name;
2551 p_to.interval_yn := p_from.interval_yn;
2552 p_to.dnz_chr_id := p_from.dnz_chr_id;
2553 p_to.object_version_number := p_from.object_version_number;
2554 p_to.created_by := p_from.created_by;
2555 p_to.creation_date := p_from.creation_date;
2556 p_to.last_updated_by := p_from.last_updated_by;
2557 p_to.last_update_date := p_from.last_update_date;
2558 p_to.last_update_login := p_from.last_update_login;
2559 p_to.attribute_category := p_from.attribute_category;
2560 p_to.attribute1 := p_from.attribute1;
2561 p_to.attribute2 := p_from.attribute2;
2562 p_to.attribute3 := p_from.attribute3;
2563 p_to.attribute4 := p_from.attribute4;
2564 p_to.attribute5 := p_from.attribute5;
2565 p_to.attribute6 := p_from.attribute6;
2566 p_to.attribute7 := p_from.attribute7;
2567 p_to.attribute8 := p_from.attribute8;
2568 p_to.attribute9 := p_from.attribute9;
2569 p_to.attribute10 := p_from.attribute10;
2570 p_to.attribute11 := p_from.attribute11;
2571 p_to.attribute12 := p_from.attribute12;
2572 p_to.attribute13 := p_from.attribute13;
2573 p_to.attribute14 := p_from.attribute14;
2574 p_to.attribute15 := p_from.attribute15;
2575 END migrate;
2576
2577 PROCEDURE CREATE_CYCLE(
2578 p_api_version IN NUMBER,
2579 p_init_msg_list IN VARCHAR2 ,
2580 x_return_status OUT NOCOPY VARCHAR2,
2581 x_msg_count OUT NOCOPY NUMBER,
2582 x_msg_data OUT NOCOPY VARCHAR2,
2583 p_cylv_ext_rec IN cylv_extended_rec_type,
2584 x_cylv_ext_rec OUT NOCOPY cylv_extended_rec_type) IS
2585 p_cylv_rec cylv_rec_type;
2586 x_cylv_rec cylv_rec_type;
2587 p_spnv_rec spnv_rec_type;
2588 x_spnv_rec spnv_rec_type;
2589 p_isev_ext_rec isev_extended_rec_type;
2590 x_isev_ext_rec isev_extended_rec_type;
2591 BEGIN
2592 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2593 x_cylv_ext_rec := p_cylv_ext_rec;
2594 if p_cylv_ext_rec.limited_start_date <> OKC_API.G_MISS_DATE and
2595 p_cylv_ext_rec.limited_start_date is not null and
2596 p_cylv_ext_rec.limited_end_date <> OKC_API.G_MISS_DATE and
2597 p_cylv_ext_rec.limited_end_date is not null then
2598 p_isev_ext_rec.start_date := p_cylv_ext_rec.limited_start_date;
2599 p_isev_ext_rec.end_date := p_cylv_ext_rec.limited_end_date;
2600 p_isev_ext_rec.description := 'Limited by of Cycle';
2601 p_isev_ext_rec.short_description := 'Limited by of Cycle';
2602 p_isev_ext_rec.comments := 'Generated by cyl';
2603 p_isev_ext_rec.dnz_chr_id := p_cylv_ext_rec.dnz_chr_id;
2604 OKC_TIME_pub.create_ia_startend(
2605 p_api_version,
2606 p_init_msg_list,
2607 x_return_status,
2608 x_msg_count,
2609 x_msg_data,
2610 p_isev_ext_rec,
2611 x_isev_ext_rec);
2612 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2613 return;
2614 end if;
2615 x_cylv_ext_rec.tve_id_limited := x_isev_ext_rec.id;
2616 x_cylv_ext_rec.limited_start_date := x_isev_ext_rec.start_date;
2617 x_cylv_ext_rec.limited_end_date := x_isev_ext_rec.end_date;
2618 end if;
2619 migrate(x_cylv_ext_rec,p_cylv_rec);
2620 okc_cyl_pvt.insert_row(
2621 p_api_version,
2622 p_init_msg_list,
2623 x_return_status,
2624 x_msg_count,
2625 x_msg_data,
2626 p_cylv_rec,
2627 x_cylv_rec);
2628 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2629 return;
2630 end if;
2631 migrate(x_cylv_rec,x_cylv_ext_rec);
2632 p_spnv_rec.uom_code := x_cylv_ext_rec.uom_code;
2633 p_spnv_rec.duration := x_cylv_ext_rec.duration;
2634 p_spnv_rec.active_yn := x_cylv_ext_rec.active_yn;
2635 p_spnv_rec.tve_id := x_cylv_ext_rec.id;
2636 p_spnv_rec.object_version_number := x_cylv_ext_rec.object_version_number;
2637 OKC_TIME_pub.create_span(
2638 p_api_version,
2639 p_init_msg_list,
2640 x_return_status,
2641 x_msg_count,
2642 x_msg_data,
2643 p_spnv_rec,
2644 x_spnv_rec);
2645 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2646 return;
2647 end if;
2648 x_cylv_ext_rec.uom_code := x_spnv_rec.uom_code;
2649 x_cylv_ext_rec.duration := x_spnv_rec.duration;
2650 x_cylv_ext_rec.active_yn := x_spnv_rec.active_yn;
2651 x_cylv_ext_rec.spn_id := x_spnv_rec.id;
2652 p_cylv_rec.spn_id := x_spnv_rec.id;
2653 p_cylv_rec.id := x_cylv_ext_rec.id;
2654 UPDATE OKC_TIMEVALUES
2655 SET SPN_ID = x_cylv_ext_rec.spn_id
2656 WHERE TVE_TYPE = 'CYL'
2657 AND id = x_cylv_ext_rec.id;
2658 END CREATE_CYCLE;
2659
2660 PROCEDURE UPDATE_CYCLE(
2661 p_api_version IN NUMBER,
2662 p_init_msg_list IN VARCHAR2 ,
2663 x_return_status OUT NOCOPY VARCHAR2,
2664 x_msg_count OUT NOCOPY NUMBER,
2665 x_msg_data OUT NOCOPY VARCHAR2,
2666 p_cylv_ext_rec IN cylv_extended_rec_type,
2667 x_cylv_ext_rec OUT NOCOPY cylv_extended_rec_type) IS
2668 p_cylv_rec cylv_rec_type;
2669 x_cylv_rec cylv_rec_type;
2670 p_spnv_rec spnv_rec_type;
2671 x_spnv_rec spnv_rec_type;
2672 p_isev_ext_rec isev_extended_rec_type;
2673 x_isev_ext_rec isev_extended_rec_type;
2674 l_row_notfound BOOLEAN := TRUE;
2675 l_api_name CONSTANT VARCHAR2(30) := 'update_cycle';
2676 CURSOR okc_tve_csr (p_id IN NUMBER) IS
2677 SELECT tve_id_limited, dnz_chr_id
2678 FROM okc_timevalues
2679 WHERE id = p_id
2680 AND tve_type = 'CYL';
2681 CURSOR okc_limited_csr (p_id IN NUMBER) IS
2682 SELECT id, start_date, end_date
2683 FROM okc_time_ia_startend_val_v
2684 WHERE id = p_id;
2685 l_okc_limited okc_limited_csr%ROWTYPE;
2686 l_okc_tve okc_tve_csr%ROWTYPE;
2687 BEGIN
2688 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2689 x_cylv_ext_rec := p_cylv_ext_rec;
2690 OPEN okc_tve_csr(x_cylv_ext_rec.id);
2691 FETCH okc_tve_csr INTO l_okc_tve;
2692 l_row_notfound := okc_tve_csr%NOTFOUND;
2693 CLOSE okc_tve_csr;
2694 if (l_okc_tve.tve_id_limited = OKC_API.G_MISS_NUM or
2695 l_okc_tve.tve_id_limited is NULL) then
2696 if p_cylv_ext_rec.limited_start_date <> OKC_API.G_MISS_DATE and
2697 p_cylv_ext_rec.limited_start_date is not null and
2698 p_cylv_ext_rec.limited_end_date <> OKC_API.G_MISS_DATE and
2699 p_cylv_ext_rec.limited_end_date is not null then
2700 p_isev_ext_rec.start_date := p_cylv_ext_rec.limited_start_date;
2701 p_isev_ext_rec.end_date := p_cylv_ext_rec.limited_end_date;
2702 p_isev_ext_rec.description := 'Limited by of Generic';
2703 p_isev_ext_rec.short_description := 'Limited by of Generic';
2704 p_isev_ext_rec.comments := 'Generated by cyl';
2705 p_isev_ext_rec.dnz_chr_id := l_okc_tve.dnz_chr_id;
2706 OKC_TIME_pub.create_ia_startend(
2707 p_api_version,
2708 p_init_msg_list,
2709 x_return_status,
2710 x_msg_count,
2711 x_msg_data,
2712 p_isev_ext_rec,
2713 x_isev_ext_rec);
2714 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2715 return;
2716 end if;
2717 x_cylv_ext_rec.tve_id_limited := x_isev_ext_rec.id;
2718 end if;
2719 else
2720 if p_cylv_ext_rec.limited_start_date is null then
2721 p_isev_ext_rec.id := l_okc_tve.tve_id_limited;
2722 OKC_TIME_pub.delete_ia_startend(
2723 p_api_version,
2724 p_init_msg_list,
2725 x_return_status,
2726 x_msg_count,
2727 x_msg_data,
2728 p_isev_ext_rec);
2729 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2730 return;
2731 end if;
2732 x_cylv_ext_rec.tve_id_limited := NULL;
2733 else
2734 OPEN okc_limited_csr(l_okc_tve.tve_id_limited);
2735 FETCH okc_limited_csr INTO l_okc_limited;
2736 l_row_notfound := okc_limited_csr%NOTFOUND;
2737 CLOSE okc_limited_csr;
2738 if (((l_okc_limited.start_date <> p_cylv_ext_rec.limited_start_date) and
2739 (p_cylv_ext_rec.limited_start_date <> OKC_API.G_MISS_DATE)) or
2740 ((l_okc_limited.end_date <> p_cylv_ext_rec.limited_end_date) and
2741 (p_cylv_ext_rec.limited_end_date <> OKC_API.G_MISS_DATE))) then
2742 p_isev_ext_rec.start_date := p_cylv_ext_rec.limited_start_date;
2743 p_isev_ext_rec.end_date := p_cylv_ext_rec.limited_end_date;
2744 p_isev_ext_rec.id := l_okc_tve.tve_id_limited;
2745 OKC_TIME_pub.update_ia_startend(
2746 p_api_version,
2747 p_init_msg_list,
2748 x_return_status,
2749 x_msg_count,
2750 x_msg_data,
2751 p_isev_ext_rec,
2752 x_isev_ext_rec);
2753 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2754 return;
2755 end if;
2756 end if;
2757 end if;
2758 end if;
2759 migrate(x_cylv_ext_rec,p_cylv_rec);
2760 okc_cyl_pvt.update_row(
2761 p_api_version,
2762 p_init_msg_list,
2763 x_return_status,
2764 x_msg_count,
2765 x_msg_data,
2766 p_cylv_rec,
2767 x_cylv_rec);
2768 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2769 return;
2770 end if;
2771 migrate(x_cylv_rec,x_cylv_ext_rec);
2772 p_spnv_rec.tve_id := x_cylv_ext_rec.id;
2773 p_spnv_rec.id := x_cylv_ext_rec.spn_id;
2774 p_spnv_rec.uom_code := x_cylv_ext_rec.uom_code;
2775 p_spnv_rec.duration := x_cylv_ext_rec.duration;
2776 p_spnv_rec.active_yn := x_cylv_ext_rec.active_yn;
2777 p_spnv_rec.object_version_number := p_cylv_ext_rec.object_version_number;
2778 OKC_TIME_pub.update_span(
2779 p_api_version,
2780 p_init_msg_list,
2781 x_return_status,
2782 x_msg_count,
2783 x_msg_data,
2784 p_spnv_rec,
2785 x_spnv_rec);
2786 x_cylv_ext_rec.uom_code := x_spnv_rec.uom_code;
2787 x_cylv_ext_rec.duration := x_spnv_rec.duration;
2788 x_cylv_ext_rec.active_yn := x_spnv_rec.active_yn;
2789 END UPDATE_CYCLE;
2790
2791 PROCEDURE DELETE_CYCLE(
2792 p_api_version IN NUMBER,
2793 p_init_msg_list IN VARCHAR2 ,
2794 x_return_status OUT NOCOPY VARCHAR2,
2795 x_msg_count OUT NOCOPY NUMBER,
2796 x_msg_data OUT NOCOPY VARCHAR2,
2797 p_cylv_ext_rec IN cylv_extended_rec_type) IS
2798 p_cylv_rec cylv_rec_type;
2799 p_spnv_rec spnv_rec_type;
2800 p_isev_ext_rec isev_extended_rec_type;
2801 x_isev_ext_rec isev_extended_rec_type;
2802 l_row_notfound BOOLEAN := TRUE;
2803 CURSOR okc_limited_csr (p_id IN NUMBER) IS
2804 SELECT id, tve_id_limited, spn_id
2805 FROM okc_timevalues
2806 WHERE id = p_id
2807 AND tve_type = 'CYL';
2808 l_okc_limited okc_limited_csr%ROWTYPE;
2809 BEGIN
2810 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2811 OPEN okc_limited_csr(p_cylv_ext_rec.id);
2812 FETCH okc_limited_csr INTO l_okc_limited;
2813 l_row_notfound := okc_limited_csr%NOTFOUND;
2814 CLOSE okc_limited_csr;
2815 if l_okc_limited.tve_id_limited is not null and
2816 l_okc_limited.tve_id_limited <> OKC_API.G_MISS_NUM then
2817 p_isev_ext_rec.id := l_okc_limited.tve_id_limited;
2818 OKC_TIME_pub.delete_ia_startend(
2819 p_api_version,
2820 p_init_msg_list,
2821 x_return_status,
2822 x_msg_count,
2823 x_msg_data,
2824 p_isev_ext_rec);
2825 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2826 return;
2827 end if;
2828 end if;
2829 if p_cylv_rec.spn_id is not null and
2830 p_cylv_rec.spn_id <> OKC_API.G_MISS_NUM Then
2831 p_spnv_rec.id := p_cylv_rec.spn_id;
2832 else
2833 p_spnv_rec.id := l_okc_limited.spn_id;
2834 end if;
2835 OKC_TIME_pub.delete_span(
2836 p_api_version,
2837 p_init_msg_list,
2838 x_return_status,
2839 x_msg_count,
2840 x_msg_data,
2841 p_spnv_rec);
2842 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2843 return;
2844 end if;
2845 migrate(p_cylv_ext_rec,p_cylv_rec);
2846 okc_cyl_pvt.delete_row(
2847 p_api_version,
2848 p_init_msg_list,
2849 x_return_status,
2850 x_msg_count,
2851 x_msg_data,
2852 p_cylv_rec);
2853 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2854 return;
2855 end if;
2856 END DELETE_CYCLE;
2857
2858 PROCEDURE LOCK_CYCLE(
2859 p_api_version IN NUMBER,
2860 p_init_msg_list IN VARCHAR2 ,
2861 x_return_status OUT NOCOPY VARCHAR2,
2862 x_msg_count OUT NOCOPY NUMBER,
2863 x_msg_data OUT NOCOPY VARCHAR2,
2864 p_cylv_ext_rec IN cylv_extended_rec_type) IS
2865 p_cylv_rec cylv_rec_type;
2866 p_spnv_rec spnv_rec_type;
2867 BEGIN
2868 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2869 migrate(p_cylv_ext_rec,p_cylv_rec);
2870 okc_cyl_pvt.lock_row(
2871 p_api_version,
2872 p_init_msg_list,
2873 x_return_status,
2874 x_msg_count,
2875 x_msg_data,
2876 p_cylv_rec);
2877 END LOCK_CYCLE;
2878
2879 PROCEDURE VALID_CYCLE(
2880 p_api_version IN NUMBER,
2881 p_init_msg_list IN VARCHAR2 ,
2882 x_return_status OUT NOCOPY VARCHAR2,
2883 x_msg_count OUT NOCOPY NUMBER,
2884 x_msg_data OUT NOCOPY VARCHAR2,
2885 p_cylv_ext_rec IN cylv_extended_rec_type) IS
2886 p_cylv_rec cylv_rec_type;
2887 p_spnv_rec spnv_rec_type;
2888 BEGIN
2889 x_return_status := OKC_API.G_RET_STS_SUCCESS;
2890 migrate(p_cylv_ext_rec,p_cylv_rec);
2891 okc_cyl_pvt.validate_row(
2892 p_api_version,
2893 p_init_msg_list,
2894 x_return_status,
2895 x_msg_count,
2896 x_msg_data,
2897 p_cylv_rec);
2898 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
2899 return;
2900 end if;
2901 p_spnv_rec.id := p_cylv_rec.spn_id;
2902 OKC_TIME_pub.valid_span(
2903 p_api_version,
2904 p_init_msg_list,
2905 x_return_status,
2906 x_msg_count,
2907 x_msg_data,
2908 p_spnv_rec);
2909 END VALID_CYCLE;
2910
2911 ---------------------------------------------------------------
2912 -- Procedure for mass insert in OKC_TIMEVALUES _B and TL tables
2913 ---------------------------------------------------------------
2914 PROCEDURE INSERT_IGS_ROW_UPG(p_igsv_ext_tbl IN igsv_ext_tbl_type) IS
2915 l_tabsize NUMBER := p_igsv_ext_tbl.COUNT;
2916 l_source_lang VARCHAR2(12) := okc_util.get_userenv_lang;
2917
2918 in_id OKC_DATATYPES.NumberTabTyp;
2919 in_object_version_number OKC_DATATYPES.NumberTabTyp;
2920 --Bug 3122962 in_sfwt_flag OKC_DATATYPES.Var3TabTyp;
2921 in_spn_id OKC_DATATYPES.NumberTabTyp;
2922 in_tve_id_offset OKC_DATATYPES.NumberTabTyp;
2923 in_uom_code OKC_DATATYPES.Var3TabTyp;
2924 in_tve_id_generated_by OKC_DATATYPES.NumberTabTyp;
2925 in_tve_id_started OKC_DATATYPES.NumberTabTyp;
2926 in_tve_id_ended OKC_DATATYPES.NumberTabTyp;
2927 in_tve_id_limited OKC_DATATYPES.NumberTabTyp;
2928 in_cnh_id OKC_DATATYPES.NumberTabTyp;
2929 in_dnz_chr_id OKC_DATATYPES.NumberTabTyp;
2930 in_tze_id OKC_DATATYPES.NumberTabTyp;
2931 in_description OKC_DATATYPES.Var1995TabTyp;
2932 in_short_description OKC_DATATYPES.Var600TabTyp;
2933 in_comments OKC_DATATYPES.Var1995TabTyp;
2934 in_duration OKC_DATATYPES.NumberTabTyp;
2935 in_operator OKC_DATATYPES.Var10TabTyp;
2936 in_before_after OKC_DATATYPES.Var3TabTyp;
2937 in_datetime OKC_DATATYPES.DateTabTyp;
2938 in_month OKC_DATATYPES.NumberTabTyp;
2939 in_day OKC_DATATYPES.NumberTabTyp;
2940 in_day_of_week OKC_DATATYPES.Var10TabTyp;
2941 in_hour OKC_DATATYPES.NumberTabTyp;
2942 in_minute OKC_DATATYPES.NumberTabTyp;
2943 in_second OKC_DATATYPES.NumberTabTyp;
2944 in_name OKC_DATATYPES.Var150TabTyp;
2945 in_interval_yn OKC_DATATYPES.Var3TabTyp;
2946 in_nth OKC_DATATYPES.NumberTabTyp;
2947 in_attribute_category OKC_DATATYPES.Var90TabTyp;
2948 in_attribute1 OKC_DATATYPES.Var450TabTyp;
2949 in_attribute2 OKC_DATATYPES.Var450TabTyp;
2950 in_attribute3 OKC_DATATYPES.Var450TabTyp;
2951 in_attribute4 OKC_DATATYPES.Var450TabTyp;
2952 in_attribute5 OKC_DATATYPES.Var450TabTyp;
2953 in_attribute6 OKC_DATATYPES.Var450TabTyp;
2954 in_attribute7 OKC_DATATYPES.Var450TabTyp;
2955 in_attribute8 OKC_DATATYPES.Var450TabTyp;
2956 in_attribute9 OKC_DATATYPES.Var450TabTyp;
2957 in_attribute10 OKC_DATATYPES.Var450TabTyp;
2958 in_attribute11 OKC_DATATYPES.Var450TabTyp;
2959 in_attribute12 OKC_DATATYPES.Var450TabTyp;
2960 in_attribute13 OKC_DATATYPES.Var450TabTyp;
2961 in_attribute14 OKC_DATATYPES.Var450TabTyp;
2962 in_attribute15 OKC_DATATYPES.Var450TabTyp;
2963 in_tve_type OKC_DATATYPES.Var10TabTyp;
2964 in_created_by OKC_DATATYPES.NumberTabTyp;
2965 in_creation_date OKC_DATATYPES.DateTabTyp;
2966 in_last_updated_by OKC_DATATYPES.NumberTabTyp;
2967 in_last_update_date OKC_DATATYPES.DateTabTyp;
2968 in_last_update_login OKC_DATATYPES.NumberTabTyp;
2969 l_tve_id_started NUMBER;
2970 l_tve_id_ended NUMBER;
2971 i NUMBER := p_igsv_ext_tbl.FIRST;
2972 j NUMBER := 0;
2973 BEGIN
2974 while i is NOT NULL
2975 LOOP
2976 /* FOR TGD STARTED */
2977 j := j+1;
2978 in_id (j) := okc_p_util.raw_to_number(sys_guid());
2979 l_tve_id_started := in_id(j);
2980 in_object_version_number (j) := p_igsv_ext_tbl(i).object_version_number;
2981 --Bug 3122962 in_sfwt_flag (j) := p_igsv_ext_tbl(i).sfwt_flag;
2982 in_spn_id (j) := NULL;
2983 in_tve_id_offset (j) := NULL;
2984 in_uom_code (j) := NULL;
2985 in_tve_id_generated_by (j) := NULL;
2986 in_tve_id_started (j) := NULL;
2987 in_tve_id_ended (j) := NULL;
2988 in_tve_id_limited (j) := NULL;
2989 in_cnh_id (j) := NULL;
2990 in_dnz_chr_id (j) := p_igsv_ext_tbl(i).dnz_chr_id;
2991 in_tze_id (j) := p_igsv_ext_tbl(i).tze_id;
2992 in_description (j) := 'Start of Generic Interval Startend';
2993 in_short_description (j) := 'Start Generic Intrvl';
2994 in_comments (j) := 'Generated by IGS';
2995 in_duration (j) := NULL;
2996 in_operator (j) := NULL;
2997 in_before_after (j) := NULL;
2998 in_datetime (j) := NULL;
2999 in_month (j) := p_igsv_ext_tbl(i).start_month;
3000 in_day (j) := p_igsv_ext_tbl(i).start_day;
3001 in_day_of_week (j) := p_igsv_ext_tbl(i).start_day_of_week;
3002 in_hour (j) := p_igsv_ext_tbl(i).start_hour;
3003 in_minute (j) := p_igsv_ext_tbl(i).start_minute;
3004 in_second (j) := p_igsv_ext_tbl(i).start_second;
3005 in_name (j) := NULL;
3006 in_interval_yn (j) := NULL;
3007 in_nth (j) := p_igsv_ext_tbl(i).start_nth;
3008 in_attribute_category (j) := p_igsv_ext_tbl(i).attribute_category;
3009 in_attribute1 (j) := p_igsv_ext_tbl(i).attribute1;
3010 in_attribute2 (j) := p_igsv_ext_tbl(i).attribute2;
3011 in_attribute3 (j) := p_igsv_ext_tbl(i).attribute3;
3012 in_attribute4 (j) := p_igsv_ext_tbl(i).attribute4;
3013 in_attribute5 (j) := p_igsv_ext_tbl(i).attribute5;
3014 in_attribute6 (j) := p_igsv_ext_tbl(i).attribute6;
3015 in_attribute7 (j) := p_igsv_ext_tbl(i).attribute7;
3016 in_attribute8 (j) := p_igsv_ext_tbl(i).attribute8;
3017 in_attribute9 (j) := p_igsv_ext_tbl(i).attribute9;
3018 in_attribute10 (j) := p_igsv_ext_tbl(i).attribute10;
3019 in_attribute11 (j) := p_igsv_ext_tbl(i).attribute11;
3020 in_attribute12 (j) := p_igsv_ext_tbl(i).attribute12;
3021 in_attribute13 (j) := p_igsv_ext_tbl(i).attribute13;
3022 in_attribute14 (j) := p_igsv_ext_tbl(i).attribute14;
3023 in_attribute15 (j) := p_igsv_ext_tbl(i).attribute15;
3024 in_tve_type (j) := 'TGD';
3025 in_created_by (j) := p_igsv_ext_tbl(i).created_by;
3026 in_creation_date (j) := p_igsv_ext_tbl(i).creation_date;
3027 in_last_updated_by (j) := p_igsv_ext_tbl(i).last_updated_by;
3028 in_last_update_date (j) := p_igsv_ext_tbl(i).last_update_date;
3029 in_last_update_login (j) := p_igsv_ext_tbl(i).last_update_login;
3030 /* FOR TGD ENDED */
3031 j := j+1;
3032 in_id (j) := okc_p_util.raw_to_number(sys_guid());
3033 l_tve_id_ended := in_id(j);
3034 in_object_version_number (j) := p_igsv_ext_tbl(i).object_version_number;
3035 --Bug 3122962 in_sfwt_flag (j) := p_igsv_ext_tbl(i).sfwt_flag;
3036 in_spn_id (j) := NULL;
3037 in_tve_id_offset (j) := NULL;
3038 in_uom_code (j) := NULL;
3039 in_tve_id_generated_by (j) := NULL;
3040 in_tve_id_started (j) := NULL;
3041 in_tve_id_ended (j) := NULL;
3042 in_tve_id_limited (j) := NULL;
3043 in_cnh_id (j) := NULL;
3044 in_dnz_chr_id (j) := p_igsv_ext_tbl(i).dnz_chr_id;
3045 in_tze_id (j) := p_igsv_ext_tbl(i).tze_id;
3046 in_description (j) := 'End of Generic Interval Startend';
3047 in_short_description (j) := 'End Generic Intrvl';
3048 in_comments (j) := 'Generated by IGS';
3049 in_duration (j) := NULL;
3050 in_operator (j) := NULL;
3051 in_before_after (j) := NULL;
3052 in_datetime (j) := NULL;
3053 in_month (j) := p_igsv_ext_tbl(i).end_month;
3054 in_day (j) := p_igsv_ext_tbl(i).end_day;
3055 in_day_of_week (j) := p_igsv_ext_tbl(i).end_day_of_week;
3056 in_hour (j) := p_igsv_ext_tbl(i).end_hour;
3057 in_minute (j) := p_igsv_ext_tbl(i).end_minute;
3058 in_second (j) := p_igsv_ext_tbl(i).end_second;
3059 in_name (j) := NULL;
3060 in_interval_yn (j) := NULL;
3061 in_nth (j) := p_igsv_ext_tbl(i).end_nth;
3062 in_attribute_category (j) := p_igsv_ext_tbl(i).attribute_category;
3063 in_attribute1 (j) := p_igsv_ext_tbl(i).attribute1;
3064 in_attribute2 (j) := p_igsv_ext_tbl(i).attribute2;
3065 in_attribute3 (j) := p_igsv_ext_tbl(i).attribute3;
3066 in_attribute4 (j) := p_igsv_ext_tbl(i).attribute4;
3067 in_attribute5 (j) := p_igsv_ext_tbl(i).attribute5;
3068 in_attribute6 (j) := p_igsv_ext_tbl(i).attribute6;
3069 in_attribute7 (j) := p_igsv_ext_tbl(i).attribute7;
3070 in_attribute8 (j) := p_igsv_ext_tbl(i).attribute8;
3071 in_attribute9 (j) := p_igsv_ext_tbl(i).attribute9;
3072 in_attribute10 (j) := p_igsv_ext_tbl(i).attribute10;
3073 in_attribute11 (j) := p_igsv_ext_tbl(i).attribute11;
3074 in_attribute12 (j) := p_igsv_ext_tbl(i).attribute12;
3075 in_attribute13 (j) := p_igsv_ext_tbl(i).attribute13;
3076 in_attribute14 (j) := p_igsv_ext_tbl(i).attribute14;
3077 in_attribute15 (j) := p_igsv_ext_tbl(i).attribute15;
3078 in_tve_type (j) := 'TGD';
3079 in_created_by (j) := p_igsv_ext_tbl(i).created_by;
3080 in_creation_date (j) := p_igsv_ext_tbl(i).creation_date;
3081 in_last_updated_by (j) := p_igsv_ext_tbl(i).last_updated_by;
3082 in_last_update_date (j) := p_igsv_ext_tbl(i).last_update_date;
3083 in_last_update_login (j) := p_igsv_ext_tbl(i).last_update_login;
3084 /* FOR IGS */
3085 j := j+1;
3086 in_id (j) := p_igsv_ext_tbl(i).id;
3087 in_object_version_number (j) := p_igsv_ext_tbl(i).object_version_number;
3088 --Bug 3122962 in_sfwt_flag (j) := p_igsv_ext_tbl(i).sfwt_flag;
3089 in_spn_id (j) := NULL;
3090 in_tve_id_offset (j) := NULL;
3091 in_uom_code (j) := NULL;
3092 in_tve_id_generated_by (j) := NULL;
3093 in_tve_id_started (j) := l_tve_id_started;
3094 in_tve_id_ended (j) := l_tve_id_ended;
3095 in_tve_id_limited (j) := NULL;
3096 in_cnh_id (j) := NULL;
3097 in_dnz_chr_id (j) := p_igsv_ext_tbl(i).dnz_chr_id;
3098 in_tze_id (j) := p_igsv_ext_tbl(i).tze_id;
3099 in_description (j) := p_igsv_ext_tbl(i).description;
3100 in_short_description (j) := p_igsv_ext_tbl(i).short_description;
3101 in_comments (j) := p_igsv_ext_tbl(i).comments;
3102 in_duration (j) := NULL;
3103 in_operator (j) := NULL;
3104 in_before_after (j) := NULL;
3105 in_datetime (j) := NULL;
3106 in_month (j) := NULL;
3107 in_day (j) := NULL;
3108 in_day_of_week (j) := NULL;
3109 in_hour (j) := NULL;
3110 in_minute (j) := NULL;
3111 in_second (j) := NULL;
3112 in_name (j) := NULL;
3113 in_interval_yn (j) := NULL;
3114 in_nth (j) := NULL;
3115 in_attribute_category (j) := p_igsv_ext_tbl(i).attribute_category;
3116 in_attribute1 (j) := p_igsv_ext_tbl(i).attribute1;
3117 in_attribute2 (j) := p_igsv_ext_tbl(i).attribute2;
3118 in_attribute3 (j) := p_igsv_ext_tbl(i).attribute3;
3119 in_attribute4 (j) := p_igsv_ext_tbl(i).attribute4;
3120 in_attribute5 (j) := p_igsv_ext_tbl(i).attribute5;
3121 in_attribute6 (j) := p_igsv_ext_tbl(i).attribute6;
3122 in_attribute7 (j) := p_igsv_ext_tbl(i).attribute7;
3123 in_attribute8 (j) := p_igsv_ext_tbl(i).attribute8;
3124 in_attribute9 (j) := p_igsv_ext_tbl(i).attribute9;
3125 in_attribute10 (j) := p_igsv_ext_tbl(i).attribute10;
3126 in_attribute11 (j) := p_igsv_ext_tbl(i).attribute11;
3127 in_attribute12 (j) := p_igsv_ext_tbl(i).attribute12;
3128 in_attribute13 (j) := p_igsv_ext_tbl(i).attribute13;
3129 in_attribute14 (j) := p_igsv_ext_tbl(i).attribute14;
3130 in_attribute15 (j) := p_igsv_ext_tbl(i).attribute15;
3131 in_tve_type (j) := 'IGS';
3132 in_created_by (j) := p_igsv_ext_tbl(i).created_by;
3133 in_creation_date (j) := p_igsv_ext_tbl(i).creation_date;
3134 in_last_updated_by (j) := p_igsv_ext_tbl(i).last_updated_by;
3135 in_last_update_date (j) := p_igsv_ext_tbl(i).last_update_date;
3136 in_last_update_login (j) := p_igsv_ext_tbl(i).last_update_login;
3137 i := p_igsv_ext_tbl.NEXT(i);
3138 END LOOP;
3139 --Bug 3122962
3140 FORALL i in 1..j
3141 INSERT
3142 INTO OKC_TIMEVALUES
3143 (
3144 id,
3145 spn_id,
3146 tve_id_offset,
3147 tve_id_generated_by,
3148 tve_id_started,
3149 tve_id_ended,
3150 tve_id_limited,
3151 cnh_id,
3152 dnz_chr_id,
3153 tve_type,
3154 tze_id,
3155 object_version_number,
3156 created_by,
3157 creation_date,
3158 last_updated_by,
3159 last_update_date,
3160 uom_code,
3161 duration,
3162 operator,
3163 before_after,
3164 datetime,
3165 month,
3166 day,
3167 hour,
3168 minute,
3169 second,
3170 interval_yn,
3171 last_update_login,
3172 nth,
3173 day_of_week,
3174 attribute_category,
3175 attribute1,
3176 attribute2,
3177 attribute3,
3178 attribute4,
3179 attribute5,
3180 attribute6,
3181 attribute7,
3182 attribute8,
3183 attribute9,
3184 attribute10,
3185 attribute11,
3186 attribute12,
3187 attribute13,
3188 attribute14,
3189 attribute15,
3190 --Bug 3122962
3191 name,
3192 description,
3193 short_description,
3194 comments
3195 )
3196 VALUES (
3197 DECODE(in_id(i),OKC_API.G_MISS_NUM,NULL,in_id(i)),
3198 DECODE(in_spn_id(i),OKC_API.G_MISS_NUM,NULL,in_spn_id(i)),
3199 DECODE(in_tve_id_offset(i),OKC_API.G_MISS_NUM,NULL,in_tve_id_offset(i)),
3200 DECODE(in_tve_id_generated_by(i),OKC_API.G_MISS_NUM,NULL,in_tve_id_generated_by(i)),
3201 DECODE(in_tve_id_started(i),OKC_API.G_MISS_NUM,NULL,in_tve_id_started(i)),
3202 DECODE(in_tve_id_ended(i),OKC_API.G_MISS_NUM,NULL,in_tve_id_ended(i)),
3203 DECODE(in_tve_id_limited(i),OKC_API.G_MISS_NUM,NULL,in_tve_id_limited(i)),
3204 DECODE(in_cnh_id(i),OKC_API.G_MISS_NUM,NULL,in_cnh_id(i)),
3205 DECODE(in_dnz_chr_id(i),OKC_API.G_MISS_NUM,NULL,in_dnz_chr_id(i)),
3206 DECODE(in_tve_type(i),OKC_API.G_MISS_CHAR,NULL,in_tve_type(i)),
3207 DECODE(in_tze_id(i),OKC_API.G_MISS_NUM,NULL,in_tze_id(i)),
3208 DECODE(in_object_version_number(i),OKC_API.G_MISS_NUM,NULL,in_object_version_number(i)),
3209 DECODE(in_created_by(i),OKC_API.G_MISS_NUM,NULL,in_created_by(i)),
3210 DECODE(in_creation_date(i),OKC_API.G_MISS_DATE,NULL,in_creation_date(i)),
3211 DECODE(in_last_updated_by(i),OKC_API.G_MISS_NUM,NULL,in_last_updated_by(i)),
3212 DECODE(in_last_update_date(i),OKC_API.G_MISS_DATE,NULL,in_last_update_date(i)),
3213 DECODE(in_uom_code(i),OKC_API.G_MISS_CHAR,NULL,in_uom_code(i)),
3214 DECODE(in_duration(i),OKC_API.G_MISS_NUM,NULL,in_duration(i)),
3215 DECODE(in_operator(i),OKC_API.G_MISS_CHAR,NULL,in_operator(i)),
3216 DECODE(in_before_after(i),OKC_API.G_MISS_CHAR,NULL,in_before_after(i)),
3217 DECODE(in_datetime(i),OKC_API.G_MISS_DATE,NULL,in_datetime(i)),
3218 DECODE(in_month(i),OKC_API.G_MISS_NUM,NULL,in_month(i)),
3219 DECODE(in_day(i),OKC_API.G_MISS_NUM,NULL,in_day(i)),
3220 DECODE(in_hour(i),OKC_API.G_MISS_NUM,NULL,in_hour(i)),
3221 DECODE(in_minute(i),OKC_API.G_MISS_NUM,NULL,in_minute(i)),
3222 DECODE(in_second(i),OKC_API.G_MISS_NUM,NULL,in_second(i)),
3223 DECODE(in_interval_yn(i),OKC_API.G_MISS_CHAR,NULL,in_interval_yn(i)),
3224 DECODE(in_last_update_login(i),OKC_API.G_MISS_NUM,NULL,in_last_update_login(i)),
3225 DECODE(in_nth(i),OKC_API.G_MISS_NUM,NULL,in_nth(i)),
3226 DECODE(in_day_of_week(i),OKC_API.G_MISS_CHAR,NULL,in_day_of_week(i)),
3227 DECODE(in_attribute_category(i),OKC_API.G_MISS_CHAR,NULL,in_attribute_category(i)),
3228 DECODE(in_attribute1(i),OKC_API.G_MISS_CHAR,NULL,in_attribute1(i)),
3229 DECODE(in_attribute2(i),OKC_API.G_MISS_CHAR,NULL,in_attribute2(i)),
3230 DECODE(in_attribute3(i),OKC_API.G_MISS_CHAR,NULL,in_attribute3(i)),
3231 DECODE(in_attribute4(i),OKC_API.G_MISS_CHAR,NULL,in_attribute4(i)),
3232 DECODE(in_attribute5(i),OKC_API.G_MISS_CHAR,NULL,in_attribute5(i)),
3233 DECODE(in_attribute6(i),OKC_API.G_MISS_CHAR,NULL,in_attribute6(i)),
3234 DECODE(in_attribute7(i),OKC_API.G_MISS_CHAR,NULL,in_attribute7(i)),
3235 DECODE(in_attribute8(i),OKC_API.G_MISS_CHAR,NULL,in_attribute8(i)),
3236 DECODE(in_attribute9(i),OKC_API.G_MISS_CHAR,NULL,in_attribute9(i)),
3237 DECODE(in_attribute10(i),OKC_API.G_MISS_CHAR,NULL,in_attribute10(i)),
3238 DECODE(in_attribute11(i),OKC_API.G_MISS_CHAR,NULL,in_attribute11(i)),
3239 DECODE(in_attribute12(i),OKC_API.G_MISS_CHAR,NULL,in_attribute12(i)),
3240 DECODE(in_attribute13(i),OKC_API.G_MISS_CHAR,NULL,in_attribute13(i)),
3241 DECODE(in_attribute14(i),OKC_API.G_MISS_CHAR,NULL,in_attribute14(i)),
3242 DECODE(in_attribute15(i),OKC_API.G_MISS_CHAR,NULL,in_attribute15(i)),
3243 --Bug 3122962
3244 DECODE(in_name(i),OKC_API.G_MISS_CHAR,NULL,in_name(i)),
3245 DECODE(in_description(i),OKC_API.G_MISS_CHAR,NULL,in_description(i)),
3246 DECODE(in_short_description(i),OKC_API.G_MISS_CHAR,NULL,in_short_description(i)),
3247 DECODE(in_comments(i),OKC_API.G_MISS_CHAR,NULL,in_comments(i))
3248 );
3249
3250 --Bug 3122962
3251 /*
3252 FOR lang_i IN OKC_UTIL.g_language_code.FIRST..OKC_UTIL.g_language_code.LAST LOOP
3253 FORALL i in 1..j
3254 INSERT INTO OKC_TIMEVALUES_TL(
3255 id,
3256 language,
3257 source_lang,
3258 sfwt_flag,
3259 description,
3260 short_description,
3261 comments,
3262 name,
3263 created_by,
3264 creation_date,
3265 last_updated_by,
3266 last_update_date,
3267 last_update_login
3268 )
3269 VALUES (
3270 DECODE(in_id(i),OKC_API.G_MISS_NUM,NULL,in_id(i)),
3271 OKC_UTIL.g_language_code(lang_i),
3272 l_source_lang,
3273 DECODE(in_sfwt_flag(i),OKC_API.G_MISS_CHAR,NULL,in_sfwt_flag(i)),
3274 DECODE(in_description(i),OKC_API.G_MISS_CHAR,NULL,in_description(i)),
3275 DECODE(in_short_description(i),OKC_API.G_MISS_CHAR,NULL,in_short_description(i)),
3276 DECODE(in_comments(i),OKC_API.G_MISS_CHAR,NULL,in_comments(i)),
3277 DECODE(in_name(i),OKC_API.G_MISS_CHAR,NULL,in_name(i)),
3278 DECODE(in_created_by(i),OKC_API.G_MISS_NUM,NULL,in_created_by(i)),
3279 DECODE(in_creation_date(i),OKC_API.G_MISS_DATE,NULL,in_creation_date(i)),
3280 DECODE(in_last_updated_by(i),OKC_API.G_MISS_NUM,NULL,in_last_updated_by(i)),
3281 DECODE(in_last_update_date(i),OKC_API.G_MISS_DATE,NULL,in_last_update_date(i)),
3282 DECODE(in_last_update_login(i),OKC_API.G_MISS_NUM,NULL,in_last_update_login(i))
3283 );
3284 END LOOP;
3285 */
3286 EXCEPTION
3287 WHEN OTHERS THEN
3288 RAISE;
3289 END INSERT_IGS_ROW_UPG;
3290
3291 PROCEDURE INSERT_TGD_ROW_UPG(p_tgdv_ext_tbl IN tgdv_ext_tbl_type) IS
3292 l_tabsize NUMBER := p_tgdv_ext_tbl.COUNT;
3293 l_source_lang VARCHAR2(12) := okc_util.get_userenv_lang;
3294
3295 in_id OKC_DATATYPES.NumberTabTyp;
3296 in_object_version_number OKC_DATATYPES.NumberTabTyp;
3297 --Bug 3122962 in_sfwt_flag OKC_DATATYPES.Var3TabTyp;
3298 in_spn_id OKC_DATATYPES.NumberTabTyp;
3299 in_tve_id_offset OKC_DATATYPES.NumberTabTyp;
3300 in_uom_code OKC_DATATYPES.Var3TabTyp;
3301 in_tve_id_generated_by OKC_DATATYPES.NumberTabTyp;
3302 in_tve_id_started OKC_DATATYPES.NumberTabTyp;
3303 in_tve_id_ended OKC_DATATYPES.NumberTabTyp;
3304 in_tve_id_limited OKC_DATATYPES.NumberTabTyp;
3305 in_cnh_id OKC_DATATYPES.NumberTabTyp;
3306 in_dnz_chr_id OKC_DATATYPES.NumberTabTyp;
3307 in_tze_id OKC_DATATYPES.NumberTabTyp;
3308 in_description OKC_DATATYPES.Var1995TabTyp;
3309 in_short_description OKC_DATATYPES.Var600TabTyp;
3310 in_comments OKC_DATATYPES.Var1995TabTyp;
3311 in_duration OKC_DATATYPES.NumberTabTyp;
3312 in_operator OKC_DATATYPES.Var10TabTyp;
3313 in_before_after OKC_DATATYPES.Var3TabTyp;
3314 in_datetime OKC_DATATYPES.DateTabTyp;
3315 in_month OKC_DATATYPES.NumberTabTyp;
3316 in_day OKC_DATATYPES.NumberTabTyp;
3317 in_day_of_week OKC_DATATYPES.Var10TabTyp;
3318 in_hour OKC_DATATYPES.NumberTabTyp;
3319 in_minute OKC_DATATYPES.NumberTabTyp;
3320 in_second OKC_DATATYPES.NumberTabTyp;
3321 in_name OKC_DATATYPES.Var150TabTyp;
3322 in_interval_yn OKC_DATATYPES.Var3TabTyp;
3323 in_nth OKC_DATATYPES.NumberTabTyp;
3324 in_attribute_category OKC_DATATYPES.Var90TabTyp;
3325 in_attribute1 OKC_DATATYPES.Var450TabTyp;
3326 in_attribute2 OKC_DATATYPES.Var450TabTyp;
3327 in_attribute3 OKC_DATATYPES.Var450TabTyp;
3328 in_attribute4 OKC_DATATYPES.Var450TabTyp;
3329 in_attribute5 OKC_DATATYPES.Var450TabTyp;
3330 in_attribute6 OKC_DATATYPES.Var450TabTyp;
3331 in_attribute7 OKC_DATATYPES.Var450TabTyp;
3332 in_attribute8 OKC_DATATYPES.Var450TabTyp;
3333 in_attribute9 OKC_DATATYPES.Var450TabTyp;
3334 in_attribute10 OKC_DATATYPES.Var450TabTyp;
3335 in_attribute11 OKC_DATATYPES.Var450TabTyp;
3336 in_attribute12 OKC_DATATYPES.Var450TabTyp;
3337 in_attribute13 OKC_DATATYPES.Var450TabTyp;
3338 in_attribute14 OKC_DATATYPES.Var450TabTyp;
3339 in_attribute15 OKC_DATATYPES.Var450TabTyp;
3340 in_tve_type OKC_DATATYPES.Var10TabTyp;
3341 in_created_by OKC_DATATYPES.NumberTabTyp;
3342 in_creation_date OKC_DATATYPES.DateTabTyp;
3343 in_last_updated_by OKC_DATATYPES.NumberTabTyp;
3344 in_last_update_date OKC_DATATYPES.DateTabTyp;
3345 in_last_update_login OKC_DATATYPES.NumberTabTyp;
3346 i NUMBER := p_tgdv_ext_tbl.FIRST;
3347 j NUMBER := 0;
3348 BEGIN
3349 while i is not NULL
3350 LOOP
3351 j := j+1;
3352 in_id (j) := p_tgdv_ext_tbl(i).id;
3353 in_object_version_number (j) := p_tgdv_ext_tbl(i).object_version_number;
3354 --Bug 3122962 in_sfwt_flag (j) := p_tgdv_ext_tbl(i).sfwt_flag;
3355 in_spn_id (j) := NULL;
3356 in_tve_id_offset (j) := NULL;
3357 in_uom_code (j) := NULL;
3358 in_tve_id_generated_by (j) := NULL;
3359 in_tve_id_started (j) := NULL;
3360 in_tve_id_ended (j) := NULL;
3361 in_tve_id_limited (j) := NULL;
3362 in_cnh_id (j) := NULL;
3363 in_dnz_chr_id (j) := p_tgdv_ext_tbl(i).dnz_chr_id;
3364 in_tze_id (j) := p_tgdv_ext_tbl(i).tze_id;
3365 in_description (j) := p_tgdv_ext_tbl(i).description;
3366 in_short_description (j) := p_tgdv_ext_tbl(i).short_description;
3367 in_comments (j) := p_tgdv_ext_tbl(i).comments;
3368 in_duration (j) := NULL;
3369 in_operator (j) := NULL;
3370 in_before_after (j) := NULL;
3371 in_datetime (j) := NULL;
3372 in_month (j) := p_tgdv_ext_tbl(i).month;
3373 in_day (j) := p_tgdv_ext_tbl(i).day;
3374 in_day_of_week (j) := p_tgdv_ext_tbl(i).day_of_week;
3375 in_hour (j) := p_tgdv_ext_tbl(i).hour;
3376 in_minute (j) := p_tgdv_ext_tbl(i).minute;
3377 in_second (j) := p_tgdv_ext_tbl(i).second;
3378 in_name (j) := NULL;
3379 in_interval_yn (j) := NULL;
3380 in_nth (j) := p_tgdv_ext_tbl(i).nth;
3381 in_attribute_category (j) := p_tgdv_ext_tbl(i).attribute_category;
3382 in_attribute1 (j) := p_tgdv_ext_tbl(i).attribute1;
3383 in_attribute2 (j) := p_tgdv_ext_tbl(i).attribute2;
3384 in_attribute3 (j) := p_tgdv_ext_tbl(i).attribute3;
3385 in_attribute4 (j) := p_tgdv_ext_tbl(i).attribute4;
3386 in_attribute5 (j) := p_tgdv_ext_tbl(i).attribute5;
3387 in_attribute6 (j) := p_tgdv_ext_tbl(i).attribute6;
3388 in_attribute7 (j) := p_tgdv_ext_tbl(i).attribute7;
3389 in_attribute8 (j) := p_tgdv_ext_tbl(i).attribute8;
3390 in_attribute9 (j) := p_tgdv_ext_tbl(i).attribute9;
3391 in_attribute10 (j) := p_tgdv_ext_tbl(i).attribute10;
3392 in_attribute11 (j) := p_tgdv_ext_tbl(i).attribute11;
3393 in_attribute12 (j) := p_tgdv_ext_tbl(i).attribute12;
3394 in_attribute13 (j) := p_tgdv_ext_tbl(i).attribute13;
3395 in_attribute14 (j) := p_tgdv_ext_tbl(i).attribute14;
3396 in_attribute15 (j) := p_tgdv_ext_tbl(i).attribute15;
3397 in_tve_type (j) := 'TGD';
3398 in_created_by (j) := p_tgdv_ext_tbl(i).created_by;
3399 in_creation_date (j) := p_tgdv_ext_tbl(i).creation_date;
3400 in_last_updated_by (j) := p_tgdv_ext_tbl(i).last_updated_by;
3401 in_last_update_date (j) := p_tgdv_ext_tbl(i).last_update_date;
3402 in_last_update_login (j) := p_tgdv_ext_tbl(i).last_update_login;
3403 i := p_tgdv_ext_tbl.NEXT(i);
3404 END LOOP;
3405 --Bug 3122962
3406 FORALL i in 1..j
3407 INSERT
3408 INTO OKC_TIMEVALUES
3409 (
3410 id,
3411 spn_id,
3412 tve_id_offset,
3413 tve_id_generated_by,
3414 tve_id_started,
3415 tve_id_ended,
3416 tve_id_limited,
3417 cnh_id,
3418 dnz_chr_id,
3419 tve_type,
3420 tze_id,
3421 object_version_number,
3422 created_by,
3423 creation_date,
3424 last_updated_by,
3425 last_update_date,
3426 uom_code,
3427 duration,
3428 operator,
3429 before_after,
3430 datetime,
3431 month,
3432 day,
3433 hour,
3434 minute,
3435 second,
3436 interval_yn,
3437 last_update_login,
3438 nth,
3439 day_of_week,
3440 attribute_category,
3441 attribute1,
3442 attribute2,
3443 attribute3,
3444 attribute4,
3445 attribute5,
3446 attribute6,
3447 attribute7,
3448 attribute8,
3449 attribute9,
3450 attribute10,
3451 attribute11,
3452 attribute12,
3453 attribute13,
3454 attribute14,
3455 attribute15,
3456 --Bug 3122962
3457 name,
3458 description,
3459 short_description,
3460 comments
3461 )
3462 VALUES (
3463 DECODE(in_id(i),OKC_API.G_MISS_NUM,NULL,in_id(i)),
3464 DECODE(in_spn_id(i),OKC_API.G_MISS_NUM,NULL,in_spn_id(i)),
3465 DECODE(in_tve_id_offset(i),OKC_API.G_MISS_NUM,NULL,in_tve_id_offset(i)),
3466 DECODE(in_tve_id_generated_by(i),OKC_API.G_MISS_NUM,NULL,in_tve_id_generated_by(i)),
3467 DECODE(in_tve_id_started(i),OKC_API.G_MISS_NUM,NULL,in_tve_id_started(i)),
3468 DECODE(in_tve_id_ended(i),OKC_API.G_MISS_NUM,NULL,in_tve_id_ended(i)),
3469 DECODE(in_tve_id_limited(i),OKC_API.G_MISS_NUM,NULL,in_tve_id_limited(i)),
3470 DECODE(in_cnh_id(i),OKC_API.G_MISS_NUM,NULL,in_cnh_id(i)),
3471 DECODE(in_dnz_chr_id(i),OKC_API.G_MISS_NUM,NULL,in_dnz_chr_id(i)),
3472 DECODE(in_tve_type(i),OKC_API.G_MISS_CHAR,NULL,in_tve_type(i)),
3473 DECODE(in_tze_id(i),OKC_API.G_MISS_NUM,NULL,in_tze_id(i)),
3474 DECODE(in_object_version_number(i),OKC_API.G_MISS_NUM,NULL,in_object_version_number(i)),
3475 DECODE(in_created_by(i),OKC_API.G_MISS_NUM,NULL,in_created_by(i)),
3476 DECODE(in_creation_date(i),OKC_API.G_MISS_DATE,NULL,in_creation_date(i)),
3477 DECODE(in_last_updated_by(i),OKC_API.G_MISS_NUM,NULL,in_last_updated_by(i)),
3478 DECODE(in_last_update_date(i),OKC_API.G_MISS_DATE,NULL,in_last_update_date(i)),
3479 DECODE(in_uom_code(i),OKC_API.G_MISS_CHAR,NULL,in_uom_code(i)),
3480 DECODE(in_duration(i),OKC_API.G_MISS_NUM,NULL,in_duration(i)),
3481 DECODE(in_operator(i),OKC_API.G_MISS_CHAR,NULL,in_operator(i)),
3482 DECODE(in_before_after(i),OKC_API.G_MISS_CHAR,NULL,in_before_after(i)),
3483 DECODE(in_datetime(i),OKC_API.G_MISS_DATE,NULL,in_datetime(i)),
3484 DECODE(in_month(i),OKC_API.G_MISS_NUM,NULL,in_month(i)),
3485 DECODE(in_day(i),OKC_API.G_MISS_NUM,NULL,in_day(i)),
3486 DECODE(in_hour(i),OKC_API.G_MISS_NUM,NULL,in_hour(i)),
3487 DECODE(in_minute(i),OKC_API.G_MISS_NUM,NULL,in_minute(i)),
3488 DECODE(in_second(i),OKC_API.G_MISS_NUM,NULL,in_second(i)),
3489 DECODE(in_interval_yn(i),OKC_API.G_MISS_CHAR,NULL,in_interval_yn(i)),
3490 DECODE(in_last_update_login(i),OKC_API.G_MISS_NUM,NULL,in_last_update_login(i)),
3491 DECODE(in_nth(i),OKC_API.G_MISS_NUM,NULL,in_nth(i)),
3492 DECODE(in_day_of_week(i),OKC_API.G_MISS_CHAR,NULL,in_day_of_week(i)),
3493 DECODE(in_attribute_category(i),OKC_API.G_MISS_CHAR,NULL,in_attribute_category(i)),
3494 DECODE(in_attribute1(i),OKC_API.G_MISS_CHAR,NULL,in_attribute1(i)),
3495 DECODE(in_attribute2(i),OKC_API.G_MISS_CHAR,NULL,in_attribute2(i)),
3496 DECODE(in_attribute3(i),OKC_API.G_MISS_CHAR,NULL,in_attribute3(i)),
3497 DECODE(in_attribute4(i),OKC_API.G_MISS_CHAR,NULL,in_attribute4(i)),
3498 DECODE(in_attribute5(i),OKC_API.G_MISS_CHAR,NULL,in_attribute5(i)),
3499 DECODE(in_attribute6(i),OKC_API.G_MISS_CHAR,NULL,in_attribute6(i)),
3500 DECODE(in_attribute7(i),OKC_API.G_MISS_CHAR,NULL,in_attribute7(i)),
3501 DECODE(in_attribute8(i),OKC_API.G_MISS_CHAR,NULL,in_attribute8(i)),
3502 DECODE(in_attribute9(i),OKC_API.G_MISS_CHAR,NULL,in_attribute9(i)),
3503 DECODE(in_attribute10(i),OKC_API.G_MISS_CHAR,NULL,in_attribute10(i)),
3504 DECODE(in_attribute11(i),OKC_API.G_MISS_CHAR,NULL,in_attribute11(i)),
3505 DECODE(in_attribute12(i),OKC_API.G_MISS_CHAR,NULL,in_attribute12(i)),
3506 DECODE(in_attribute13(i),OKC_API.G_MISS_CHAR,NULL,in_attribute13(i)),
3507 DECODE(in_attribute14(i),OKC_API.G_MISS_CHAR,NULL,in_attribute14(i)),
3508 DECODE(in_attribute15(i),OKC_API.G_MISS_CHAR,NULL,in_attribute15(i)),
3509 --Bug 3122962
3510 DECODE(in_name(i),OKC_API.G_MISS_CHAR,NULL,in_name(i)),
3511 DECODE(in_description(i),OKC_API.G_MISS_CHAR,NULL,in_description(i)),
3512 DECODE(in_short_description(i),OKC_API.G_MISS_CHAR,NULL,in_short_description(i)),
3513 DECODE(in_comments(i),OKC_API.G_MISS_CHAR,NULL,in_comments(i))
3514 );
3515 --Bug 3122962
3516 /*
3517 FOR lang_i IN OKC_UTIL.g_language_code.FIRST..OKC_UTIL.g_language_code.LAST LOOP
3518 FORALL i in 1..j
3519 INSERT INTO OKC_TIMEVALUES_TL(
3520 id,
3521 language,
3522 source_lang,
3523 sfwt_flag,
3524 description,
3525 short_description,
3526 comments,
3527 name,
3528 created_by,
3529 creation_date,
3530 last_updated_by,
3531 last_update_date,
3532 last_update_login
3533 )
3534 VALUES (
3535 DECODE(in_id(i),OKC_API.G_MISS_NUM,NULL,in_id(i)),
3536 OKC_UTIL.g_language_code(lang_i),
3537 l_source_lang,
3538 DECODE(in_sfwt_flag(i),OKC_API.G_MISS_CHAR,NULL,in_sfwt_flag(i)),
3539 DECODE(in_description(i),OKC_API.G_MISS_CHAR,NULL,in_description(i)),
3540 DECODE(in_short_description(i),OKC_API.G_MISS_CHAR,NULL,in_short_description(i)),
3541 DECODE(in_comments(i),OKC_API.G_MISS_CHAR,NULL,in_comments(i)),
3542 DECODE(in_name(i),OKC_API.G_MISS_CHAR,NULL,in_name(i)),
3543 DECODE(in_created_by(i),OKC_API.G_MISS_NUM,NULL,in_created_by(i)),
3544 DECODE(in_creation_date(i),OKC_API.G_MISS_DATE,NULL,in_creation_date(i)),
3545 DECODE(in_last_updated_by(i),OKC_API.G_MISS_NUM,NULL,in_last_updated_by(i)),
3546 DECODE(in_last_update_date(i),OKC_API.G_MISS_DATE,NULL,in_last_update_date(i)),
3547 DECODE(in_last_update_login(i),OKC_API.G_MISS_NUM,NULL,in_last_update_login(i))
3548 );
3549 END LOOP;
3550 */
3551 EXCEPTION
3552 WHEN OTHERS THEN
3553 RAISE;
3554 END INSERT_TGD_ROW_UPG;
3555
3556 PROCEDURE INSERT_ISE_ROW_UPG(p_isev_ext_tbl IN isev_ext_tbl_type) IS
3557 l_tabsize NUMBER := p_isev_ext_tbl.COUNT;
3558 l_source_lang VARCHAR2(12) := okc_util.get_userenv_lang;
3559 in_id OKC_DATATYPES.NumberTabTyp;
3560 in_object_version_number OKC_DATATYPES.NumberTabTyp;
3561 --Bug 3122962 in_sfwt_flag OKC_DATATYPES.Var3TabTyp;
3562 in_spn_id OKC_DATATYPES.NumberTabTyp;
3563 in_tve_id_offset OKC_DATATYPES.NumberTabTyp;
3564 in_uom_code OKC_DATATYPES.Var3TabTyp;
3565 in_tve_id_generated_by OKC_DATATYPES.NumberTabTyp;
3566 in_tve_id_started OKC_DATATYPES.NumberTabTyp;
3567 in_tve_id_ended OKC_DATATYPES.NumberTabTyp;
3568 in_tve_id_limited OKC_DATATYPES.NumberTabTyp;
3569 in_cnh_id OKC_DATATYPES.NumberTabTyp;
3570 in_dnz_chr_id OKC_DATATYPES.NumberTabTyp;
3571 in_tze_id OKC_DATATYPES.NumberTabTyp;
3572 in_description OKC_DATATYPES.Var1995TabTyp;
3573 in_short_description OKC_DATATYPES.Var600TabTyp;
3574 in_comments OKC_DATATYPES.Var1995TabTyp;
3575 in_duration OKC_DATATYPES.NumberTabTyp;
3576 in_operator OKC_DATATYPES.Var10TabTyp;
3577 in_before_after OKC_DATATYPES.Var3TabTyp;
3578 in_datetime OKC_DATATYPES.DateTabTyp;
3579 in_month OKC_DATATYPES.NumberTabTyp;
3580 in_day OKC_DATATYPES.NumberTabTyp;
3581 in_day_of_week OKC_DATATYPES.Var10TabTyp;
3582 in_hour OKC_DATATYPES.NumberTabTyp;
3583 in_minute OKC_DATATYPES.NumberTabTyp;
3584 in_second OKC_DATATYPES.NumberTabTyp;
3585 in_name OKC_DATATYPES.Var150TabTyp;
3586 in_interval_yn OKC_DATATYPES.Var3TabTyp;
3587 in_nth OKC_DATATYPES.NumberTabTyp;
3588 in_attribute_category OKC_DATATYPES.Var90TabTyp;
3589 in_attribute1 OKC_DATATYPES.Var450TabTyp;
3590 in_attribute2 OKC_DATATYPES.Var450TabTyp;
3591 in_attribute3 OKC_DATATYPES.Var450TabTyp;
3592 in_attribute4 OKC_DATATYPES.Var450TabTyp;
3593 in_attribute5 OKC_DATATYPES.Var450TabTyp;
3594 in_attribute6 OKC_DATATYPES.Var450TabTyp;
3595 in_attribute7 OKC_DATATYPES.Var450TabTyp;
3596 in_attribute8 OKC_DATATYPES.Var450TabTyp;
3597 in_attribute9 OKC_DATATYPES.Var450TabTyp;
3598 in_attribute10 OKC_DATATYPES.Var450TabTyp;
3599 in_attribute11 OKC_DATATYPES.Var450TabTyp;
3600 in_attribute12 OKC_DATATYPES.Var450TabTyp;
3601 in_attribute13 OKC_DATATYPES.Var450TabTyp;
3602 in_attribute14 OKC_DATATYPES.Var450TabTyp;
3603 in_attribute15 OKC_DATATYPES.Var450TabTyp;
3604 in_tve_type OKC_DATATYPES.Var10TabTyp;
3605 in_created_by OKC_DATATYPES.NumberTabTyp;
3606 in_creation_date OKC_DATATYPES.DateTabTyp;
3607 in_last_updated_by OKC_DATATYPES.NumberTabTyp;
3608 in_last_update_date OKC_DATATYPES.DateTabTyp;
3609 in_last_update_login OKC_DATATYPES.NumberTabTyp;
3610 l_tve_id_started NUMBER;
3611 l_tve_id_ended NUMBER;
3612 i NUMBER := p_isev_ext_tbl.FIRST;
3613 j NUMBER := 0;
3614 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3615 BEGIN
3616 while i is NOT NULL
3617 LOOP
3618 /* FOR ISE STARTED */
3619 j := j+1;
3620 in_id (j) := okc_p_util.raw_to_number(sys_guid());
3621 l_tve_id_started := in_id(j);
3622 in_object_version_number (j) := p_isev_ext_tbl(i).object_version_number;
3623 --Bug 3122962 in_sfwt_flag (j) := p_isev_ext_tbl(i).sfwt_flag;
3624 in_spn_id (j) := NULL;
3625 in_tve_id_offset (j) := NULL;
3626 in_uom_code (j) := NULL;
3627 in_tve_id_generated_by (j) := NULL;
3628 in_tve_id_started (j) := NULL;
3629 in_tve_id_ended (j) := NULL;
3630 in_tve_id_limited (j) := NULL;
3631 in_cnh_id (j) := NULL;
3632 in_dnz_chr_id (j) := p_isev_ext_tbl(i).dnz_chr_id;
3633 in_tze_id (j) := p_isev_ext_tbl(i).tze_id;
3634 in_description (j) := 'Start of Absolute Interval Startend';
3635 in_short_description (j) := 'Start Absolute Intrvl';
3636 in_comments (j) := 'Generated by ise';
3637 in_duration (j) := NULL;
3638 in_operator (j) := NULL;
3639 in_before_after (j) := NULL;
3640 in_datetime (j) := p_isev_ext_tbl(i).start_date;
3641 in_month (j) := NULL;
3642 in_day (j) := NULL;
3643 in_day_of_week (j) := NULL;
3644 in_hour (j) := NULL;
3645 in_minute (j) := NULL;
3646 in_second (j) := NULL;
3647 in_name (j) := NULL;
3648 in_interval_yn (j) := NULL;
3649 in_nth (j) := NULL;
3650 in_attribute_category (j) := p_isev_ext_tbl(i).attribute_category;
3651 in_attribute1 (j) := p_isev_ext_tbl(i).attribute1;
3652 in_attribute2 (j) := p_isev_ext_tbl(i).attribute2;
3653 in_attribute3 (j) := p_isev_ext_tbl(i).attribute3;
3654 in_attribute4 (j) := p_isev_ext_tbl(i).attribute4;
3655 in_attribute5 (j) := p_isev_ext_tbl(i).attribute5;
3656 in_attribute6 (j) := p_isev_ext_tbl(i).attribute6;
3657 in_attribute7 (j) := p_isev_ext_tbl(i).attribute7;
3658 in_attribute8 (j) := p_isev_ext_tbl(i).attribute8;
3659 in_attribute9 (j) := p_isev_ext_tbl(i).attribute9;
3660 in_attribute10 (j) := p_isev_ext_tbl(i).attribute10;
3661 in_attribute11 (j) := p_isev_ext_tbl(i).attribute11;
3662 in_attribute12 (j) := p_isev_ext_tbl(i).attribute12;
3663 in_attribute13 (j) := p_isev_ext_tbl(i).attribute13;
3664 in_attribute14 (j) := p_isev_ext_tbl(i).attribute14;
3665 in_attribute15 (j) := p_isev_ext_tbl(i).attribute15;
3666 in_tve_type (j) := 'TAV';
3667 in_created_by (j) := p_isev_ext_tbl(i).created_by;
3668 in_creation_date (j) := p_isev_ext_tbl(i).creation_date;
3669 in_last_updated_by (j) := p_isev_ext_tbl(i).last_updated_by;
3670 in_last_update_date (j) := p_isev_ext_tbl(i).last_update_date;
3671 in_last_update_login (j) := p_isev_ext_tbl(i).last_update_login;
3672 /* FOR ISE */
3673 j := j+1;
3674 in_id (j) := p_isev_ext_tbl(i).id;
3675 in_object_version_number (j) := p_isev_ext_tbl(i).object_version_number;
3676 --Bug 3122962 in_sfwt_flag (j) := p_isev_ext_tbl(i).sfwt_flag;
3677 in_spn_id (j) := NULL;
3678 in_tve_id_offset (j) := NULL;
3679 in_tve_id_generated_by (j) := NULL;
3680 in_tve_id_started (j) := l_tve_id_started;
3681 in_tve_id_ended (j) := NULL;
3682 in_tve_id_limited (j) := NULL;
3683 in_cnh_id (j) := NULL;
3684 in_dnz_chr_id (j) := p_isev_ext_tbl(i).dnz_chr_id;
3685 in_tze_id (j) := p_isev_ext_tbl(i).tze_id;
3686 in_description (j) := p_isev_ext_tbl(i).description;
3687 in_short_description (j) := p_isev_ext_tbl(i).short_description;
3688 in_comments (j) := p_isev_ext_tbl(i).comments;
3689 if p_isev_ext_tbl(i).duration is null or
3690 p_isev_ext_tbl(i).duration = OKC_API.G_MISS_NUM then
3691 if p_isev_ext_tbl(i).end_date is NOT NULL and
3692 p_isev_ext_tbl(i).end_date <> OKC_API.G_MISS_DATE then
3693 okc_time_util_pub.get_duration(p_isev_ext_tbl(i).start_date, p_isev_ext_tbl(i).end_date,in_duration(j),
3694 in_uom_code(j),x_return_status);
3695 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
3696 return;
3697 end if;
3698 else
3699 in_duration(j) := NULL;
3700 in_uom_code(j) := NULL;
3701 end if;
3702 else
3703 in_duration(j) := p_isev_ext_tbl(i).duration;
3704 in_uom_code(j) := p_isev_ext_tbl(i).uom_code;
3705 end if;
3706 in_operator (j) := NULL;
3707 in_before_after (j) := NULL;
3708 in_datetime (j) := NULL;
3709 in_month (j) := NULL;
3710 in_day (j) := NULL;
3711 in_day_of_week (j) := NULL;
3712 in_hour (j) := NULL;
3713 in_minute (j) := NULL;
3714 in_second (j) := NULL;
3715 in_name (j) := NULL;
3716 in_interval_yn (j) := NULL;
3717 in_nth (j) := NULL;
3718 in_attribute_category (j) := p_isev_ext_tbl(i).attribute_category;
3719 in_attribute1 (j) := p_isev_ext_tbl(i).attribute1;
3720 in_attribute2 (j) := p_isev_ext_tbl(i).attribute2;
3721 in_attribute3 (j) := p_isev_ext_tbl(i).attribute3;
3722 in_attribute4 (j) := p_isev_ext_tbl(i).attribute4;
3723 in_attribute5 (j) := p_isev_ext_tbl(i).attribute5;
3724 in_attribute6 (j) := p_isev_ext_tbl(i).attribute6;
3725 in_attribute7 (j) := p_isev_ext_tbl(i).attribute7;
3726 in_attribute8 (j) := p_isev_ext_tbl(i).attribute8;
3727 in_attribute9 (j) := p_isev_ext_tbl(i).attribute9;
3728 in_attribute10 (j) := p_isev_ext_tbl(i).attribute10;
3729 in_attribute11 (j) := p_isev_ext_tbl(i).attribute11;
3730 in_attribute12 (j) := p_isev_ext_tbl(i).attribute12;
3731 in_attribute13 (j) := p_isev_ext_tbl(i).attribute13;
3732 in_attribute14 (j) := p_isev_ext_tbl(i).attribute14;
3733 in_attribute15 (j) := p_isev_ext_tbl(i).attribute15;
3734 in_tve_type (j) := 'ISE';
3735 in_created_by (j) := p_isev_ext_tbl(i).created_by;
3736 in_creation_date (j) := p_isev_ext_tbl(i).creation_date;
3737 in_last_updated_by (j) := p_isev_ext_tbl(i).last_updated_by;
3738 in_last_update_date (j) := p_isev_ext_tbl(i).last_update_date;
3739 in_last_update_login (j) := p_isev_ext_tbl(i).last_update_login;
3740 i := p_isev_ext_tbl.NEXT(i);
3741 END LOOP;
3742
3743 FORALL i in 1..j
3744 INSERT
3745 INTO OKC_TIMEVALUES
3746 (
3747 id,
3748 spn_id,
3749 tve_id_offset,
3750 tve_id_generated_by,
3751 tve_id_started,
3752 tve_id_ended,
3753 tve_id_limited,
3754 cnh_id,
3755 dnz_chr_id,
3756 tve_type,
3757 tze_id,
3758 object_version_number,
3759 created_by,
3760 creation_date,
3761 last_updated_by,
3762 last_update_date,
3763 uom_code,
3764 duration,
3765 operator,
3766 before_after,
3767 datetime,
3768 month,
3769 day,
3770 hour,
3771 minute,
3772 second,
3773 interval_yn,
3774 last_update_login,
3775 nth,
3776 day_of_week,
3777 attribute_category,
3778 attribute1,
3779 attribute2,
3780 attribute3,
3781 attribute4,
3782 attribute5,
3783 attribute6,
3784 attribute7,
3785 attribute8,
3786 attribute9,
3787 attribute10,
3788 attribute11,
3789 attribute12,
3790 attribute13,
3791 attribute14,
3792 attribute15,
3793 --Bug 3122962
3794 name,
3795 description,
3796 short_description,
3797 comments
3798 )
3799 VALUES (
3800 DECODE(in_id(i),OKC_API.G_MISS_NUM,NULL,in_id(i)),
3801 DECODE(in_spn_id(i),OKC_API.G_MISS_NUM,NULL,in_spn_id(i)),
3802 DECODE(in_tve_id_offset(i),OKC_API.G_MISS_NUM,NULL,in_tve_id_offset(i)),
3803 DECODE(in_tve_id_generated_by(i),OKC_API.G_MISS_NUM,NULL,in_tve_id_generated_by(i)),
3804 DECODE(in_tve_id_started(i),OKC_API.G_MISS_NUM,NULL,in_tve_id_started(i)),
3805 DECODE(in_tve_id_ended(i),OKC_API.G_MISS_NUM,NULL,in_tve_id_ended(i)),
3806 DECODE(in_tve_id_limited(i),OKC_API.G_MISS_NUM,NULL,in_tve_id_limited(i)),
3807 DECODE(in_cnh_id(i),OKC_API.G_MISS_NUM,NULL,in_cnh_id(i)),
3808 DECODE(in_dnz_chr_id(i),OKC_API.G_MISS_NUM,NULL,in_dnz_chr_id(i)),
3809 DECODE(in_tve_type(i),OKC_API.G_MISS_CHAR,NULL,in_tve_type(i)),
3810 DECODE(in_tze_id(i),OKC_API.G_MISS_NUM,NULL,in_tze_id(i)),
3811 DECODE(in_object_version_number(i),OKC_API.G_MISS_NUM,NULL,in_object_version_number(i)),
3812 DECODE(in_created_by(i),OKC_API.G_MISS_NUM,NULL,in_created_by(i)),
3813 DECODE(in_creation_date(i),OKC_API.G_MISS_DATE,NULL,in_creation_date(i)),
3814 DECODE(in_last_updated_by(i),OKC_API.G_MISS_NUM,NULL,in_last_updated_by(i)),
3815 DECODE(in_last_update_date(i),OKC_API.G_MISS_DATE,NULL,in_last_update_date(i)),
3816 DECODE(in_uom_code(i),OKC_API.G_MISS_CHAR,NULL,in_uom_code(i)),
3817 DECODE(in_duration(i),OKC_API.G_MISS_NUM,NULL,in_duration(i)),
3818 DECODE(in_operator(i),OKC_API.G_MISS_CHAR,NULL,in_operator(i)),
3819 DECODE(in_before_after(i),OKC_API.G_MISS_CHAR,NULL,in_before_after(i)),
3820 DECODE(in_datetime(i),OKC_API.G_MISS_DATE,NULL,in_datetime(i)),
3821 DECODE(in_month(i),OKC_API.G_MISS_NUM,NULL,in_month(i)),
3822 DECODE(in_day(i),OKC_API.G_MISS_NUM,NULL,in_day(i)),
3823 DECODE(in_hour(i),OKC_API.G_MISS_NUM,NULL,in_hour(i)),
3824 DECODE(in_minute(i),OKC_API.G_MISS_NUM,NULL,in_minute(i)),
3825 DECODE(in_second(i),OKC_API.G_MISS_NUM,NULL,in_second(i)),
3826 DECODE(in_interval_yn(i),OKC_API.G_MISS_CHAR,NULL,in_interval_yn(i)),
3827 DECODE(in_last_update_login(i),OKC_API.G_MISS_NUM,NULL,in_last_update_login(i)),
3828 DECODE(in_nth(i),OKC_API.G_MISS_NUM,NULL,in_nth(i)),
3829 DECODE(in_day_of_week(i),OKC_API.G_MISS_CHAR,NULL,in_day_of_week(i)),
3830 DECODE(in_attribute_category(i),OKC_API.G_MISS_CHAR,NULL,in_attribute_category(i)),
3831 DECODE(in_attribute1(i),OKC_API.G_MISS_CHAR,NULL,in_attribute1(i)),
3832 DECODE(in_attribute2(i),OKC_API.G_MISS_CHAR,NULL,in_attribute2(i)),
3833 DECODE(in_attribute3(i),OKC_API.G_MISS_CHAR,NULL,in_attribute3(i)),
3834 DECODE(in_attribute4(i),OKC_API.G_MISS_CHAR,NULL,in_attribute4(i)),
3835 DECODE(in_attribute5(i),OKC_API.G_MISS_CHAR,NULL,in_attribute5(i)),
3836 DECODE(in_attribute6(i),OKC_API.G_MISS_CHAR,NULL,in_attribute6(i)),
3837 DECODE(in_attribute7(i),OKC_API.G_MISS_CHAR,NULL,in_attribute7(i)),
3838 DECODE(in_attribute8(i),OKC_API.G_MISS_CHAR,NULL,in_attribute8(i)),
3839 DECODE(in_attribute9(i),OKC_API.G_MISS_CHAR,NULL,in_attribute9(i)),
3840 DECODE(in_attribute10(i),OKC_API.G_MISS_CHAR,NULL,in_attribute10(i)),
3841 DECODE(in_attribute11(i),OKC_API.G_MISS_CHAR,NULL,in_attribute11(i)),
3842 DECODE(in_attribute12(i),OKC_API.G_MISS_CHAR,NULL,in_attribute12(i)),
3843 DECODE(in_attribute13(i),OKC_API.G_MISS_CHAR,NULL,in_attribute13(i)),
3844 DECODE(in_attribute14(i),OKC_API.G_MISS_CHAR,NULL,in_attribute14(i)),
3845 DECODE(in_attribute15(i),OKC_API.G_MISS_CHAR,NULL,in_attribute15(i)),
3846 --Bug 3122962
3847 DECODE(in_name(i),OKC_API.G_MISS_CHAR,NULL,in_name(i)),
3848 DECODE(in_description(i),OKC_API.G_MISS_CHAR,NULL,in_description(i)),
3849 DECODE(in_short_description(i),OKC_API.G_MISS_CHAR,NULL,in_short_description(i)),
3850 DECODE(in_comments(i),OKC_API.G_MISS_CHAR,NULL,in_comments(i))
3851 );
3852 --Bug 3122962
3853 /*
3854 FOR lang_i IN OKC_UTIL.g_language_code.FIRST..OKC_UTIL.g_language_code.LAST LOOP
3855 FORALL i in 1..j
3856 INSERT INTO OKC_TIMEVALUES_TL(
3857 id,
3858 language,
3859 source_lang,
3860 sfwt_flag,
3861 description,
3862 short_description,
3863 comments,
3864 name,
3865 created_by,
3866 creation_date,
3867 last_updated_by,
3868 last_update_date,
3869 last_update_login
3870 )
3871 VALUES (
3872 DECODE(in_id(i),OKC_API.G_MISS_NUM,NULL,in_id(i)),
3873 OKC_UTIL.g_language_code(lang_i),
3874 l_source_lang,
3875 DECODE(in_sfwt_flag(i),OKC_API.G_MISS_CHAR,NULL,in_sfwt_flag(i)),
3876 DECODE(in_description(i),OKC_API.G_MISS_CHAR,NULL,in_description(i)),
3877 DECODE(in_short_description(i),OKC_API.G_MISS_CHAR,NULL,in_short_description(i)),
3878 DECODE(in_comments(i),OKC_API.G_MISS_CHAR,NULL,in_comments(i)),
3879 DECODE(in_name(i),OKC_API.G_MISS_CHAR,NULL,in_name(i)),
3880 DECODE(in_created_by(i),OKC_API.G_MISS_NUM,NULL,in_created_by(i)),
3881 DECODE(in_creation_date(i),OKC_API.G_MISS_DATE,NULL,in_creation_date(i)),
3882 DECODE(in_last_updated_by(i),OKC_API.G_MISS_NUM,NULL,in_last_updated_by(i)),
3883 DECODE(in_last_update_date(i),OKC_API.G_MISS_DATE,NULL,in_last_update_date(i)),
3884 DECODE(in_last_update_login(i),OKC_API.G_MISS_NUM,NULL,in_last_update_login(i))
3885 );
3886 END LOOP;
3887 */
3888 EXCEPTION
3889 WHEN OTHERS THEN
3890 RAISE;
3891 END INSERT_ISE_ROW_UPG;
3892
3893 PROCEDURE INSERT_ISE_ROW_UPG(p_isev_rel_tbl IN isev_rel_tbl_type) IS
3894 l_tabsize NUMBER := p_isev_rel_tbl.COUNT;
3895 l_source_lang VARCHAR2(12) := okc_util.get_userenv_lang;
3896 in_id OKC_DATATYPES.NumberTabTyp;
3897 in_object_version_number OKC_DATATYPES.NumberTabTyp;
3898 --Bug 3122962 in_sfwt_flag OKC_DATATYPES.Var3TabTyp;
3899 in_spn_id OKC_DATATYPES.NumberTabTyp;
3900 in_tve_id_offset OKC_DATATYPES.NumberTabTyp;
3901 in_uom_code OKC_DATATYPES.Var3TabTyp;
3902 in_tve_id_generated_by OKC_DATATYPES.NumberTabTyp;
3903 in_tve_id_started OKC_DATATYPES.NumberTabTyp;
3904 in_tve_id_ended OKC_DATATYPES.NumberTabTyp;
3905 in_tve_id_limited OKC_DATATYPES.NumberTabTyp;
3906 in_cnh_id OKC_DATATYPES.NumberTabTyp;
3907 in_dnz_chr_id OKC_DATATYPES.NumberTabTyp;
3908 in_tze_id OKC_DATATYPES.NumberTabTyp;
3909 in_description OKC_DATATYPES.Var1995TabTyp;
3910 in_short_description OKC_DATATYPES.Var600TabTyp;
3911 in_comments OKC_DATATYPES.Var1995TabTyp;
3912 in_duration OKC_DATATYPES.NumberTabTyp;
3913 in_operator OKC_DATATYPES.Var10TabTyp;
3914 in_before_after OKC_DATATYPES.Var3TabTyp;
3915 in_datetime OKC_DATATYPES.DateTabTyp;
3916 in_month OKC_DATATYPES.NumberTabTyp;
3917 in_day OKC_DATATYPES.NumberTabTyp;
3918 in_day_of_week OKC_DATATYPES.Var10TabTyp;
3919 in_hour OKC_DATATYPES.NumberTabTyp;
3920 in_minute OKC_DATATYPES.NumberTabTyp;
3921 in_second OKC_DATATYPES.NumberTabTyp;
3922 in_name OKC_DATATYPES.Var150TabTyp;
3923 in_interval_yn OKC_DATATYPES.Var3TabTyp;
3924 in_nth OKC_DATATYPES.NumberTabTyp;
3925 in_attribute_category OKC_DATATYPES.Var90TabTyp;
3926 in_attribute1 OKC_DATATYPES.Var450TabTyp;
3927 in_attribute2 OKC_DATATYPES.Var450TabTyp;
3928 in_attribute3 OKC_DATATYPES.Var450TabTyp;
3929 in_attribute4 OKC_DATATYPES.Var450TabTyp;
3930 in_attribute5 OKC_DATATYPES.Var450TabTyp;
3931 in_attribute6 OKC_DATATYPES.Var450TabTyp;
3932 in_attribute7 OKC_DATATYPES.Var450TabTyp;
3933 in_attribute8 OKC_DATATYPES.Var450TabTyp;
3934 in_attribute9 OKC_DATATYPES.Var450TabTyp;
3935 in_attribute10 OKC_DATATYPES.Var450TabTyp;
3936 in_attribute11 OKC_DATATYPES.Var450TabTyp;
3937 in_attribute12 OKC_DATATYPES.Var450TabTyp;
3938 in_attribute13 OKC_DATATYPES.Var450TabTyp;
3939 in_attribute14 OKC_DATATYPES.Var450TabTyp;
3940 in_attribute15 OKC_DATATYPES.Var450TabTyp;
3941 in_tve_type OKC_DATATYPES.Var10TabTyp;
3942 in_created_by OKC_DATATYPES.NumberTabTyp;
3943 in_creation_date OKC_DATATYPES.DateTabTyp;
3944 in_last_updated_by OKC_DATATYPES.NumberTabTyp;
3945 in_last_update_date OKC_DATATYPES.DateTabTyp;
3946 in_last_update_login OKC_DATATYPES.NumberTabTyp;
3947 l_tve_id_started NUMBER;
3948 l_tve_id_ended NUMBER;
3949 i NUMBER := p_isev_rel_tbl.FIRST;
3950 j NUMBER := 0;
3951 l_date DATE := NULL;
3952 x_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
3953 BEGIN
3954 while i is NOT NULL
3955 LOOP
3956 /* FOR TPA RELATIVE */
3957 j := j+1;
3958 in_id (j) := okc_p_util.raw_to_number(sys_guid());
3959 l_tve_id_started := in_id(j);
3960 in_object_version_number (j) := p_isev_rel_tbl(i).object_version_number;
3961 --Bug 3122962 in_sfwt_flag (j) := p_isev_rel_tbl(i).sfwt_flag;
3962 in_spn_id (j) := NULL;
3963 in_tve_id_offset (j) := p_isev_rel_tbl(i).start_tve_id_offset;
3964 in_uom_code (j) := p_isev_rel_tbl(i).start_uom_code;
3965 in_tve_id_generated_by (j) := NULL;
3966 in_tve_id_started (j) := NULL;
3967 in_tve_id_ended (j) := NULL;
3968 in_tve_id_limited (j) := NULL;
3969 in_cnh_id (j) := NULL;
3970 in_dnz_chr_id (j) := p_isev_rel_tbl(i).dnz_chr_id;
3971 in_tze_id (j) := p_isev_rel_tbl(i).tze_id;
3972 in_description (j) := 'Start Date of Relative Interval';
3973 in_short_description (j) := 'Start of Rel Intrvl';
3974 in_comments (j) := 'Generated by ISE';
3975 in_duration (j) := p_isev_rel_tbl(i).start_duration;
3976 if p_isev_rel_tbl(i).start_duration >= 0 then
3977 in_before_after(j) := 'A';
3978 else
3979 in_before_after(j) := 'B';
3980 end if;
3981 in_operator (j) := p_isev_rel_tbl(i).start_operator;
3982 in_datetime (j) := NULL;
3983 in_month (j) := NULL;
3984 in_day (j) := NULL;
3985 in_day_of_week (j) := NULL;
3986 in_hour (j) := NULL;
3987 in_minute (j) := NULL;
3988 in_second (j) := NULL;
3989 in_name (j) := NULL;
3990 in_interval_yn (j) := NULL;
3991 in_nth (j) := NULL;
3992 in_attribute_category (j) := p_isev_rel_tbl(i).attribute_category;
3993 in_attribute1 (j) := p_isev_rel_tbl(i).attribute1;
3994 in_attribute2 (j) := p_isev_rel_tbl(i).attribute2;
3995 in_attribute3 (j) := p_isev_rel_tbl(i).attribute3;
3996 in_attribute4 (j) := p_isev_rel_tbl(i).attribute4;
3997 in_attribute5 (j) := p_isev_rel_tbl(i).attribute5;
3998 in_attribute6 (j) := p_isev_rel_tbl(i).attribute6;
3999 in_attribute7 (j) := p_isev_rel_tbl(i).attribute7;
4000 in_attribute8 (j) := p_isev_rel_tbl(i).attribute8;
4001 in_attribute9 (j) := p_isev_rel_tbl(i).attribute9;
4002 in_attribute10 (j) := p_isev_rel_tbl(i).attribute10;
4003 in_attribute11 (j) := p_isev_rel_tbl(i).attribute11;
4004 in_attribute12 (j) := p_isev_rel_tbl(i).attribute12;
4005 in_attribute13 (j) := p_isev_rel_tbl(i).attribute13;
4006 in_attribute14 (j) := p_isev_rel_tbl(i).attribute14;
4007 in_attribute15 (j) := p_isev_rel_tbl(i).attribute15;
4008 in_tve_type (j) := 'TAL';
4009 in_created_by (j) := p_isev_rel_tbl(i).created_by;
4010 in_creation_date (j) := p_isev_rel_tbl(i).creation_date;
4011 in_last_updated_by (j) := p_isev_rel_tbl(i).last_updated_by;
4012 in_last_update_date (j) := p_isev_rel_tbl(i).last_update_date;
4013 in_last_update_login (j) := p_isev_rel_tbl(i).last_update_login;
4014 /* FOR ISE */
4015 j := j+1;
4016 in_id (j) := p_isev_rel_tbl(i).id;
4017 in_object_version_number (j) := p_isev_rel_tbl(i).object_version_number;
4018 --Bug 3122962 in_sfwt_flag (j) := p_isev_rel_tbl(i).sfwt_flag;
4019 in_spn_id (j) := NULL;
4020 in_tve_id_offset (j) := NULL;
4021 in_tve_id_generated_by (j) := NULL;
4022 in_tve_id_started (j) := l_tve_id_started;
4023 in_tve_id_ended (j) := NULL;
4024 in_tve_id_limited (j) := NULL;
4025 in_cnh_id (j) := NULL;
4026 in_dnz_chr_id (j) := p_isev_rel_tbl(i).dnz_chr_id;
4027 in_tze_id (j) := p_isev_rel_tbl(i).tze_id;
4028 in_description (j) := p_isev_rel_tbl(i).description;
4029 in_short_description (j) := p_isev_rel_tbl(i).short_description;
4030 in_comments (j) := p_isev_rel_tbl(i).comments;
4031 l_date := OKC_TIME_UTIL_PUB.get_enddate(p_isev_rel_tbl(i).start_parent_date,
4032 p_isev_rel_tbl(i).start_uom_code,
4033 p_isev_rel_tbl(i).start_duration);
4034 if l_date is NULL THEN
4035 return;
4036 end if;
4037 if p_isev_rel_tbl(i).end_date is NOT NULL and
4038 p_isev_rel_tbl(i).end_date <> OKC_API.G_MISS_DATE then
4039 okc_time_util_pub.get_duration(l_date, p_isev_rel_tbl(i).end_date,in_duration(j),
4040 in_uom_code(j),x_return_status);
4041 if (x_return_status <> OKC_API.G_RET_STS_SUCCESS) then
4042 return;
4043 end if;
4044 else
4045 in_duration(j) := NULL;
4046 in_uom_code(j) := NULL;
4047 end if;
4048 in_operator (j) := NULL;
4049 in_before_after (j) := NULL;
4050 in_datetime (j) := NULL;
4051 in_month (j) := NULL;
4052 in_day (j) := NULL;
4053 in_day_of_week (j) := NULL;
4054 in_hour (j) := NULL;
4055 in_minute (j) := NULL;
4056 in_second (j) := NULL;
4057 in_name (j) := NULL;
4058 in_interval_yn (j) := NULL;
4059 in_nth (j) := NULL;
4060 in_attribute_category (j) := p_isev_rel_tbl(i).attribute_category;
4061 in_attribute1 (j) := p_isev_rel_tbl(i).attribute1;
4062 in_attribute2 (j) := p_isev_rel_tbl(i).attribute2;
4063 in_attribute3 (j) := p_isev_rel_tbl(i).attribute3;
4064 in_attribute4 (j) := p_isev_rel_tbl(i).attribute4;
4065 in_attribute5 (j) := p_isev_rel_tbl(i).attribute5;
4066 in_attribute6 (j) := p_isev_rel_tbl(i).attribute6;
4067 in_attribute7 (j) := p_isev_rel_tbl(i).attribute7;
4068 in_attribute8 (j) := p_isev_rel_tbl(i).attribute8;
4069 in_attribute9 (j) := p_isev_rel_tbl(i).attribute9;
4070 in_attribute10 (j) := p_isev_rel_tbl(i).attribute10;
4071 in_attribute11 (j) := p_isev_rel_tbl(i).attribute11;
4072 in_attribute12 (j) := p_isev_rel_tbl(i).attribute12;
4073 in_attribute13 (j) := p_isev_rel_tbl(i).attribute13;
4074 in_attribute14 (j) := p_isev_rel_tbl(i).attribute14;
4075 in_attribute15 (j) := p_isev_rel_tbl(i).attribute15;
4076 in_tve_type (j) := 'ISE';
4077 in_created_by (j) := p_isev_rel_tbl(i).created_by;
4078 in_creation_date (j) := p_isev_rel_tbl(i).creation_date;
4079 in_last_updated_by (j) := p_isev_rel_tbl(i).last_updated_by;
4080 in_last_update_date (j) := p_isev_rel_tbl(i).last_update_date;
4081 in_last_update_login (j) := p_isev_rel_tbl(i).last_update_login;
4082 i := p_isev_rel_tbl.NEXT(i);
4083 END LOOP;
4084
4085 FORALL i in 1..j
4086 INSERT
4087 INTO OKC_TIMEVALUES
4088 (
4089 id,
4090 spn_id,
4091 tve_id_offset,
4092 tve_id_generated_by,
4093 tve_id_started,
4094 tve_id_ended,
4095 tve_id_limited,
4096 cnh_id,
4097 dnz_chr_id,
4098 tve_type,
4099 tze_id,
4100 object_version_number,
4101 created_by,
4102 creation_date,
4103 last_updated_by,
4104 last_update_date,
4105 uom_code,
4106 duration,
4107 operator,
4108 before_after,
4109 datetime,
4110 month,
4111 day,
4112 hour,
4113 minute,
4114 second,
4115 interval_yn,
4116 last_update_login,
4117 nth,
4118 day_of_week,
4119 attribute_category,
4120 attribute1,
4121 attribute2,
4122 attribute3,
4123 attribute4,
4124 attribute5,
4125 attribute6,
4126 attribute7,
4127 attribute8,
4128 attribute9,
4129 attribute10,
4130 attribute11,
4131 attribute12,
4132 attribute13,
4133 attribute14,
4134 attribute15,
4135 --Bug 3122962
4136 name,
4137 description,
4138 short_description,
4139 comments
4140 )
4141 VALUES (
4142 DECODE(in_id(i),OKC_API.G_MISS_NUM,NULL,in_id(i)),
4143 DECODE(in_spn_id(i),OKC_API.G_MISS_NUM,NULL,in_spn_id(i)),
4144 DECODE(in_tve_id_offset(i),OKC_API.G_MISS_NUM,NULL,in_tve_id_offset(i)),
4145 DECODE(in_tve_id_generated_by(i),OKC_API.G_MISS_NUM,NULL,in_tve_id_generated_by(i)),
4146 DECODE(in_tve_id_started(i),OKC_API.G_MISS_NUM,NULL,in_tve_id_started(i)),
4147 DECODE(in_tve_id_ended(i),OKC_API.G_MISS_NUM,NULL,in_tve_id_ended(i)),
4148 DECODE(in_tve_id_limited(i),OKC_API.G_MISS_NUM,NULL,in_tve_id_limited(i)),
4149 DECODE(in_cnh_id(i),OKC_API.G_MISS_NUM,NULL,in_cnh_id(i)),
4150 DECODE(in_dnz_chr_id(i),OKC_API.G_MISS_NUM,NULL,in_dnz_chr_id(i)),
4151 DECODE(in_tve_type(i),OKC_API.G_MISS_CHAR,NULL,in_tve_type(i)),
4152 DECODE(in_tze_id(i),OKC_API.G_MISS_NUM,NULL,in_tze_id(i)),
4153 DECODE(in_object_version_number(i),OKC_API.G_MISS_NUM,NULL,in_object_version_number(i)),
4154 DECODE(in_created_by(i),OKC_API.G_MISS_NUM,NULL,in_created_by(i)),
4155 DECODE(in_creation_date(i),OKC_API.G_MISS_DATE,NULL,in_creation_date(i)),
4156 DECODE(in_last_updated_by(i),OKC_API.G_MISS_NUM,NULL,in_last_updated_by(i)),
4157 DECODE(in_last_update_date(i),OKC_API.G_MISS_DATE,NULL,in_last_update_date(i)),
4158 DECODE(in_uom_code(i),OKC_API.G_MISS_CHAR,NULL,in_uom_code(i)),
4159 DECODE(in_duration(i),OKC_API.G_MISS_NUM,NULL,in_duration(i)),
4160 DECODE(in_operator(i),OKC_API.G_MISS_CHAR,NULL,in_operator(i)),
4161 DECODE(in_before_after(i),OKC_API.G_MISS_CHAR,NULL,in_before_after(i)),
4162 DECODE(in_datetime(i),OKC_API.G_MISS_DATE,NULL,in_datetime(i)),
4163 DECODE(in_month(i),OKC_API.G_MISS_NUM,NULL,in_month(i)),
4164 DECODE(in_day(i),OKC_API.G_MISS_NUM,NULL,in_day(i)),
4165 DECODE(in_hour(i),OKC_API.G_MISS_NUM,NULL,in_hour(i)),
4166 DECODE(in_minute(i),OKC_API.G_MISS_NUM,NULL,in_minute(i)),
4167 DECODE(in_second(i),OKC_API.G_MISS_NUM,NULL,in_second(i)),
4168 DECODE(in_interval_yn(i),OKC_API.G_MISS_CHAR,NULL,in_interval_yn(i)),
4169 DECODE(in_last_update_login(i),OKC_API.G_MISS_NUM,NULL,in_last_update_login(i)),
4170 DECODE(in_nth(i),OKC_API.G_MISS_NUM,NULL,in_nth(i)),
4171 DECODE(in_day_of_week(i),OKC_API.G_MISS_CHAR,NULL,in_day_of_week(i)),
4172 DECODE(in_attribute_category(i),OKC_API.G_MISS_CHAR,NULL,in_attribute_category(i)),
4173 DECODE(in_attribute1(i),OKC_API.G_MISS_CHAR,NULL,in_attribute1(i)),
4174 DECODE(in_attribute2(i),OKC_API.G_MISS_CHAR,NULL,in_attribute2(i)),
4175 DECODE(in_attribute3(i),OKC_API.G_MISS_CHAR,NULL,in_attribute3(i)),
4176 DECODE(in_attribute4(i),OKC_API.G_MISS_CHAR,NULL,in_attribute4(i)),
4177 DECODE(in_attribute5(i),OKC_API.G_MISS_CHAR,NULL,in_attribute5(i)),
4178 DECODE(in_attribute6(i),OKC_API.G_MISS_CHAR,NULL,in_attribute6(i)),
4179 DECODE(in_attribute7(i),OKC_API.G_MISS_CHAR,NULL,in_attribute7(i)),
4180 DECODE(in_attribute8(i),OKC_API.G_MISS_CHAR,NULL,in_attribute8(i)),
4181 DECODE(in_attribute9(i),OKC_API.G_MISS_CHAR,NULL,in_attribute9(i)),
4182 DECODE(in_attribute10(i),OKC_API.G_MISS_CHAR,NULL,in_attribute10(i)),
4183 DECODE(in_attribute11(i),OKC_API.G_MISS_CHAR,NULL,in_attribute11(i)),
4184 DECODE(in_attribute12(i),OKC_API.G_MISS_CHAR,NULL,in_attribute12(i)),
4185 DECODE(in_attribute13(i),OKC_API.G_MISS_CHAR,NULL,in_attribute13(i)),
4186 DECODE(in_attribute14(i),OKC_API.G_MISS_CHAR,NULL,in_attribute14(i)),
4187 DECODE(in_attribute15(i),OKC_API.G_MISS_CHAR,NULL,in_attribute15(i)),
4188 --Bug 3122962
4189 DECODE(in_name(i),OKC_API.G_MISS_CHAR,NULL,in_name(i)),
4190 DECODE(in_description(i),OKC_API.G_MISS_CHAR,NULL,in_description(i)),
4191 DECODE(in_short_description(i),OKC_API.G_MISS_CHAR,NULL,in_short_description(i)),
4192 DECODE(in_comments(i),OKC_API.G_MISS_CHAR,NULL,in_comments(i))
4193 );
4194 --Bug 3122962
4195 /*
4196 FOR lang_i IN OKC_UTIL.g_language_code.FIRST..OKC_UTIL.g_language_code.LAST LOOP
4197 FORALL i in 1..j
4198 INSERT INTO OKC_TIMEVALUES_TL(
4199 id,
4200 language,
4201 source_lang,
4202 sfwt_flag,
4203 description,
4204 short_description,
4205 comments,
4206 name,
4207 created_by,
4208 creation_date,
4209 last_updated_by,
4210 last_update_date,
4211 last_update_login
4212 )
4213 VALUES (
4214 DECODE(in_id(i),OKC_API.G_MISS_NUM,NULL,in_id(i)),
4215 OKC_UTIL.g_language_code(lang_i),
4216 l_source_lang,
4217 DECODE(in_sfwt_flag(i),OKC_API.G_MISS_CHAR,NULL,in_sfwt_flag(i)),
4218 DECODE(in_description(i),OKC_API.G_MISS_CHAR,NULL,in_description(i)),
4219 DECODE(in_short_description(i),OKC_API.G_MISS_CHAR,NULL,in_short_description(i)),
4220 DECODE(in_comments(i),OKC_API.G_MISS_CHAR,NULL,in_comments(i)),
4221 DECODE(in_name(i),OKC_API.G_MISS_CHAR,NULL,in_name(i)),
4222 DECODE(in_created_by(i),OKC_API.G_MISS_NUM,NULL,in_created_by(i)),
4223 DECODE(in_creation_date(i),OKC_API.G_MISS_DATE,NULL,in_creation_date(i)),
4224 DECODE(in_last_updated_by(i),OKC_API.G_MISS_NUM,NULL,in_last_updated_by(i)),
4225 DECODE(in_last_update_date(i),OKC_API.G_MISS_DATE,NULL,in_last_update_date(i)),
4226 DECODE(in_last_update_login(i),OKC_API.G_MISS_NUM,NULL,in_last_update_login(i))
4227 );
4228 END LOOP;
4229 */
4230 EXCEPTION
4231 WHEN OTHERS THEN
4232 RAISE;
4233 END INSERT_ise_ROW_UPG;
4234 END OKC_TIME_PVT;