[Home] [Help]
PACKAGE BODY: APPS.OKS_COVERAGE_MIGRATION
Source
1 PACKAGE BODY OKS_COVERAGE_MIGRATION AS
2 /* $Header: OKSCOVMB.pls 120.0 2005/05/25 18:25:54 appldev noship $ */
3
4 --PROCEDURE Coverage_migration( x_return_status OUT NOCOPY VARCHAR2) IS
5 PROCEDURE Coverage_migration( p_start_rowid IN ROWID,
6 p_end_rowid IN ROWID,
7 x_return_status OUT NOCOPY VARCHAR2,
8 x_message_data OUT NOCOPY VARCHAR2) IS
9
10
11 CURSOR Csr_Get_Coverage_Rules (l_start_rowid IN ROWID ,l_end_rowid IN ROWID ) IS
12
13 SELECT
14 LINE.ID LINE_ID,
15 Line.Created_By Line_Created_By,
16 Line.Creation_Date Line_Creation_Date,
17 Line.Last_Updated_By Line_Last_Updated_By,
18 Line.Last_Update_Date Line_Last_Update_Date,
19 Line.Last_Update_Login Line_Last_Update_Login,
20 RGP.ID LINE_RGP_ID,
21 Rul.RowID Rul_Row_ID ,
22 Rul.ID Rule_ID,
23 LINE.LSE_ID LINE_LSE_ID,
24 LINE.DNZ_CHR_ID LINE_DNZ_CHR_ID,
25 OBJECT1_ID1 LINE_OBJECT1_ID1,
26 OBJECT2_ID1 LINE_OBJECT2_ID1,
27 RULE_INFORMATION1 COV_RULE_INFO1,
28 RULE_INFORMATION2 COV_RULE_INFO2,
29 RULE_INFORMATION3 COV_RULE_INFO3,
30 RULE_INFORMATION4 COV_RULE_INFO4,
31 RULE_INFORMATION5 COV_RULE_INFO5,
32 RULE_INFORMATION6 COV_RULE_INFO6,
33 RULE_INFORMATION7 COV_RULE_INFO7,
34 RULE_INFORMATION8 COV_RULE_INFO8,
35 RULE_INFORMATION9 COV_RULE_INFO9,
36 RULE_INFORMATION10 COV_RULE_INFO10,
37 RULE_INFORMATION11 COV_RULE_INFO11,
38 RULE_INFORMATION12 COV_RULE_INFO12,
39 RULE_INFORMATION13 COV_RULE_INFO13,
40 RULE_INFORMATION14 COV_RULE_INFO14,
41 RULE_INFORMATION15 COV_RULE_INFO15,
42 RULE_INFORMATION_CATEGORY COV_RULE_INFO,
43 RUL.OBJECT_VERSION_NUMBER COV_OBJ_VER_NUMBER
44 FROM
45 OKC_RULE_GROUPS_B RGP,
46 OKC_RULES_B RUL,
47 OKC_K_LINES_B LINE
48 WHERE LINE.ID = RGP.CLE_ID
49 AND RGP.ID = RUL.RGP_ID
50 AND LINE.LSE_ID IN (2,15,20)
51 -- AND RUL.RULE_INFORMATION_CATEGORY IN ('ECE','WHE','UGE','STR','CVE','PMP')
52 AND LINE.DNZ_CHR_ID = RGP.DNZ_CHR_ID
53 -- AND RUL.RULE_INFORMATION15 IS NULL
54 AND NOT EXISTS (SELECT CLE_ID FROM OKS_K_LINES_B WHERE CLE_ID = LINE.ID)
55 AND Rul.rowid BETWEEN l_start_rowid and l_end_rowid
56 ORDER BY LINE.ID;
57
58 G_Exception_Halt Exception;
59
60 TYPE RowId_Tbl_Type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
61 TYPE Num_Tbl_Type IS VARRAY(1000) OF NUMBER;
62 TYPE Date_Tbl_Type IS VARRAY(1000) OF DATE;
63 TYPE Vc15_Tbl_Type IS VARRAY(1000) OF VARCHAR2(15);
64 TYPE Vc20_Tbl_Type IS VARRAY(1000) OF VARCHAR2(20);
65 TYPE Vc150_Tbl_Type IS VARRAY(1000) OF VARCHAR2(150);
66 TYPE Vc30_Tbl_Type IS VARRAY(1000) OF VARCHAR2(30);
67 TYPE Vc1_Tbl_Type IS VARRAY(1000) OF VARCHAR2(1);
68 TYPE Vc3_Tbl_Type IS VARRAY(1000) OF VARCHAR2(3);
69 TYPE Vc2000_Tbl_Type IS VARRAY(1000) OF VARCHAR2(2000);
70 TYPE Vc50_Tbl_Type IS VARRAY(1000) OF VARCHAR2(50);
71 TYPE Vc80_Tbl_Type IS VARRAY(1000) OF VARCHAR2(80);
72 TYPE Vc120_Tbl_Type IS VARRAY(1000) OF VARCHAR2(120);
73 TYPE Vc600_Tbl_Type IS VARRAY(1000) OF VARCHAR2(600);
74
75 Rul_Row_ID_TBl RowId_Tbl_Type;
76 LINE_ID_TBL Num_Tbl_Type;
77 Line_Created_By_TBL Num_Tbl_Type;
78 Line_Creation_Date_TBL Date_Tbl_Type;
79 Line_Last_Updated_By_TBL Num_Tbl_Type;
80 Line_Last_Update_Date_TBL Date_Tbl_Type;
81 Line_Last_Update_Login_TBL Num_Tbl_Type;
82 LINE_RGP_ID_TBL Num_Tbl_Type;
83 Rule_ID_TBL Num_Tbl_Type;
84 LINE_LSE_ID_TBL Num_Tbl_Type;
85 LINE_DNZ_CHR_ID_TBL Num_Tbl_Type;
86 LINE_OBJECT1_ID1_TBL Vc150_Tbl_Type;
87 LINE_OBJECT2_ID1_TBL Vc150_Tbl_Type;
88 COV_RULE_INFO1_TBL Vc150_Tbl_Type;
89 COV_RULE_INFO2_TBL Vc150_Tbl_Type;
90 COV_RULE_INFO3_TBL Vc150_Tbl_Type;
91 COV_RULE_INFO4_TBL Vc150_Tbl_Type;
92 COV_RULE_INFO5_TBL Vc150_Tbl_Type;
93 COV_RULE_INFO6_TBL Vc150_Tbl_Type;
94 COV_RULE_INFO7_TBL Vc150_Tbl_Type;
95 COV_RULE_INFO8_TBL Vc150_Tbl_Type;
96 COV_RULE_INFO9_TBL Vc150_Tbl_Type;
97 COV_RULE_INFO10_TBL Vc150_Tbl_Type;
98 COV_RULE_INFO11_TBL Vc150_Tbl_Type;
99 COV_RULE_INFO12_TBL Vc150_Tbl_Type;
100 COV_RULE_INFO13_TBL Vc150_Tbl_Type;
101 COV_RULE_INFO14_TBL Vc150_Tbl_Type;
102 COV_RULE_INFO15_TBL Vc150_Tbl_Type;
103 COV_RULE_INFO_TBL Vc150_Tbl_Type;
104 COV_OBJ_VER_NUMBER_TBL Num_Tbl_Type;
105
106 x_clev_tbl_in oks_kln_pvt.klnv_tbl_type;
107 l_clev_tbl_in oks_kln_pvt.klnv_tbl_type;
108 l_cle_ctr NUMBER := 0;
109 tablename1 VArchar2(1000);
110 x_msg_count NUMBER := 0;
111 x_msg_data VArchar2(1000);
112 L_OLD_CLE_ID NUMBER := -99999;
113 L_CLE_ID NUMBER := -99999;
114 l_duration NUMBER;
115 l_period VArchar2(10);
116 l_return_status VArchar2(1) := OKC_API.G_RET_STS_SUCCESS;
117 l_transfer_option VArchar2(250);
118
119
120 l_start_rowid ROWID := p_start_rowid;
121 l_end_rowid ROWID := p_end_rowid;
122 l_msg_data VARCHAR2(1000);
123 l_msg_index_out NUMBER;
124 l_message VARCHAR2(2400);
125
126 PROCEDURE get_duration_period(p_id IN NUMBER,
127 x_duration OUT NOCOPY NUMBER,
128 x_period OUT NOCOPY VARCHAR2) IS
129
130 CURSOR Csr_get_duration (l_ID IN NUMBER) IS
131 SELECT UOM_CODE,Duration
132 FROM OKC_TIMEVALUES_V
133 WHERE id = l_id;
134
135 Lx_Duration NUMBER := NULL;
136 Lx_Period VARCHAR2(100) := NULL;
137
138 BEGIN
139
140 FOR Csr_get_duration_Rec in Csr_get_duration(p_id) LOOP
141
142 Lx_period := Csr_get_duration_Rec.uom_code;
143 Lx_duration := Csr_get_duration_Rec.Duration;
144
145 END LOOP;
146
147 X_duration := Lx_duration;
148 X_Period :=Lx_Period;
149
150 EXCEPTION
151 WHEN OTHERS THEN
152 OKC_API.SET_MESSAGE
153 (P_App_Name => G_APP_NAME_OKS
154 ,P_Msg_Name => G_UNEXPECTED_ERROR
155 ,P_Token1 => G_SQLCODE_TOKEN
156 ,P_Token1_Value => SQLCODE
157 ,P_Token2 => G_SQLERRM_TOKEN
158 ,P_Token2_Value => SQLERRM);
159 END;
160
161
162 BEGIN
163 -- -- dbms_output.put_line('IN Begin');
164
165 G_APP_NAME := 'Coverage_migration';
166 OPEN Csr_Get_Coverage_Rules (l_start_rowid,l_end_rowid);
167 LOOP
168 BEGIN
169 l_clev_tbl_in.DELETE;
170
171 FETCH Csr_Get_Coverage_Rules BULK COLLECT INTO
172 LINE_ID_TBL ,
173 Line_Created_By_TBL ,
174 Line_Creation_Date_TBL ,
175 Line_Last_Updated_By_TBL ,
176 Line_Last_Update_Date_TBL ,
177 Line_Last_Update_Login_TBL ,
178 LINE_RGP_ID_TBL ,
179 Rul_Row_ID_TBl ,
180 Rule_Id_Tbl ,
181 LINE_LSE_ID_TBL ,
182 LINE_DNZ_CHR_ID_TBL ,
183 LINE_OBJECT1_ID1_TBL ,
184 LINE_OBJECT2_ID1_TBL ,
185 COV_RULE_INFO1_TBL ,
186 COV_RULE_INFO2_TBL ,
187 COV_RULE_INFO3_TBL ,
188 COV_RULE_INFO4_TBL ,
189 COV_RULE_INFO5_TBL ,
190 COV_RULE_INFO6_TBL ,
191 COV_RULE_INFO7_TBL ,
192 COV_RULE_INFO8_TBL ,
193 COV_RULE_INFO9_TBL ,
194 COV_RULE_INFO10_TBL ,
195 COV_RULE_INFO11_TBL ,
196 COV_RULE_INFO12_TBL ,
197 COV_RULE_INFO13_TBL ,
198 COV_RULE_INFO14_TBL ,
199 COV_RULE_INFO15_TBL ,
200 COV_RULE_INFO_TBL ,
201 COV_OBJ_VER_NUMBER_TBL
202 LIMIT 1000;--20;
203
204
205 IF LINE_ID_TBL.COUNT > 0 THEN
206
207 -- remove dbms
208
209 FOR I IN LINE_ID_TBL.FIRST .. LINE_ID_TBL.LAST LOOP
210 L_Cle_id := LINE_ID_TBL(i);
211
212 IF (L_OLD_CLE_ID <> L_CLE_ID) THEN
213 l_cle_ctr := l_cle_ctr + 1;
214
215 -- l_clev_tbl_in(l_cle_ctr).Id := okc_p_util.raw_to_number(sys_guid());
216 l_clev_tbl_in(l_cle_ctr).Created_By := Line_Created_By_TBL(i);
217 l_clev_tbl_in(l_cle_ctr).Creation_Date := Line_Creation_Date_TBL(i);
218 l_clev_tbl_in(l_cle_ctr).Last_Updated_By := Line_Last_Updated_By_TBL(i);
219 l_clev_tbl_in(l_cle_ctr).Last_Update_Date := Line_Last_Update_Date_TBL(i);
220 l_clev_tbl_in(l_cle_ctr).Last_Update_Login := Line_Last_Update_Login_TBL(i);
221 l_clev_tbl_in(l_cle_ctr).Cle_Id := LINE_ID_TBL(i);
222 l_clev_tbl_in(l_cle_ctr).Dnz_Chr_Id := LINE_DNZ_CHR_ID_TBL(i);
223 l_clev_tbl_in(l_cle_ctr).object_version_number := 1;
224 l_clev_tbl_in(l_cle_ctr).Sync_Date_Install := 'N';
225
226 END IF;
227
228 IF COV_RULE_INFO_TBL(i) = 'CVE' THEN
229 l_clev_tbl_in(l_cle_ctr).Coverage_Type := COV_RULE_INFO1_TBL(i);--'G';
230 END IF;
231
232 IF COV_RULE_INFO_TBL(i) = 'STR' THEN
233 IF COV_RULE_INFO1_TBL(i) = 'Y' THEN
234 l_clev_tbl_in(l_cle_ctr).TRANSFER_OPTION := 'TRANS';
235 ELSE
236 l_clev_tbl_in(l_cle_ctr).TRANSFER_OPTION := 'TERMINATE';
237 END IF;
238
239 END IF;
240
241
242 IF COV_RULE_INFO_TBL(i) = 'UGE' THEN
243 l_clev_tbl_in(l_cle_ctr).Prod_Upgrade_YN := COV_RULE_INFO1_TBL(i);--'N';
244 END IF;
245
246
247 IF COV_RULE_INFO_TBL(i) = 'ECE' THEN
248 l_clev_tbl_in(l_cle_ctr).EXCEPTION_COV_ID := COV_RULE_INFO1_TBL(i);
249 END IF;
250
251 IF COV_RULE_INFO_TBL(i) = 'WHE' THEN
252 l_clev_tbl_in(l_cle_ctr).Prod_Upgrade_YN := COV_RULE_INFO1_TBL(i);
253 END IF;
254
255 IF COV_RULE_INFO_TBL(i) = 'PMP' THEN
256
257 l_clev_tbl_in(l_cle_ctr).PM_PROGRAM_ID := LINE_OBJECT1_ID1_TBL(i);
258 l_clev_tbl_in(l_cle_ctr).PM_CONF_REQ_YN := COV_RULE_INFO1_TBL(i);
259 l_clev_tbl_in(l_cle_ctr).PM_SCH_EXISTS_YN := COV_RULE_INFO2_TBL(i);
260
261 END IF;
262
263
264
265 L_OLD_CLE_ID := L_CLE_ID;
266 END LOOP;
267 END IF;
268
269 tablename1 := 'OKS_K_LINES';
270
271 IF l_clev_tbl_in.count > 0 THEN
272 oks_kln_pvt.insert_row
273 (
274 x_return_status => l_return_status,
275 p_klnv_tbl => l_clev_tbl_in,
276 p_api_version => 1,
277 p_init_msg_list => null,-- Could Not Found use standard
278 x_msg_count => x_msg_count,
279 x_msg_data => x_msg_data,
280 x_klnv_tbl => x_clev_tbl_in
281 );
282
283 IF x_msg_count > 0 THEN
284 FOR i in 1..x_msg_count LOOP
285 apps.fnd_msg_pub.get (p_msg_index => -1,
286 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
287 p_data => x_msg_data,
288 p_msg_index_out => l_msg_index_out);
289 -- l_message := l_message||' ; '||x_msg_data;
290
291 END LOOP;
292 END IF;
293 END IF;
294
295 x_return_status := 'S';
296 X_Message_Data := NULL;
297 EXCEPTION
298 WHEN G_EXCEPTION_HALT THEN
299
300 ROLLBACK;
301
302 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
303 G_APP_NAME,
304 G_PKG_NAME,
305 'OKC_API.G_RET_STS_UNEXP_ERROR',
306 x_msg_count,
307 x_msg_data,
308 '_PVT'
309 );
310
311 IF x_msg_count > 0 THEN
312 FOR i in 1..x_msg_count LOOP
313 fnd_msg_pub.get (p_msg_index => -1,
314 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
315 p_data => l_msg_data,
316 p_msg_index_out => l_msg_index_out);
317 l_message := l_message||' ; '||l_msg_data;
318
319 END LOOP;
320 END IF;
321 x_message_data := l_message;
322 x_return_status := 'E';
323 WHEN Others THEN
324
325
326 x_return_status := OKC_API.G_RET_STS_ERROR;
327
328 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
329 G_APP_NAME,
330 G_PKG_NAME,
331 'OKC_API.G_RET_STS_UNEXP_ERROR',
332 x_msg_count,
333 x_msg_data,
334 '_PVT'
335 );
336
337 IF x_msg_count > 0 THEN
338 FOR i in 1..x_msg_count LOOP
339 fnd_msg_pub.get (p_msg_index => -1,
340 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
341 p_data => l_msg_data,
342 p_msg_index_out => l_msg_index_out);
343 l_message := l_message||' ; '||l_msg_data;
344
345 END LOOP;
346 END IF;
347 x_message_data := l_message;
348 x_return_status := 'E';
349
350 END;
351
352 EXIT WHEN Csr_Get_Coverage_Rules%NOTFOUND;
353
354 END LOOP;
355
356 CLOSE Csr_Get_Coverage_Rules;
357
358
359 EXCEPTION
360 WHEN Others THEN
361
362 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
363 G_APP_NAME,
364 G_PKG_NAME,
365 'OKC_API.G_RET_STS_UNEXP_ERROR',
366 x_msg_count,
367 x_msg_data,
368 '_PVT'
369 );
370
371 IF x_msg_count > 0 THEN
372 FOR i in 1..x_msg_count LOOP
373 fnd_msg_pub.get (p_msg_index => -1,
374 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
375 p_data => l_msg_data,
376 p_msg_index_out => l_msg_index_out);
377 l_message := l_message||' ; '||l_msg_data;
378
379 END LOOP;
380 END IF;
381 x_message_data := l_message;
382 x_return_status := 'E';
383
384 END Coverage_migration;
385
386
387 --PROCEDURE Business_Process_migration( x_return_status OUT NOCOPY VARCHAR2) IS
388 PROCEDURE Business_Process_migration(p_start_rowid IN ROWID,p_end_rowid IN ROWID,x_return_status OUT NOCOPY VARCHAR2,x_message_data OUT NOCOPY VARCHAR2) IS
389
390 CURSOR Csr_Get_Buss_Process (l_start_rowid IN ROWID ,l_end_rowid IN ROWID ) IS
391 SELECT
392 LINE.ID LINE_ID,
393 Line.Created_By Line_Created_By,
394 Line.Creation_Date Line_Creation_Date,
395 Line.Last_Updated_By Line_Last_Updated_By,
396 Line.Last_Update_Date Line_Last_Update_Date,
397 Line.Last_Update_Login Line_Last_Update_Login,
398 Rul.ID Rule_Id,
399 RGP.ID LINE_RGP_ID,
400 Rul.RowId Rul_Row_Id,
401 LINE.LSE_ID LINE_LSE_ID,
402 LINE.DNZ_CHR_ID LINE_DNZ_CHR_ID,
403 OBJECT1_ID1 LINE_OBJECT1_ID1,
404 OBJECT2_ID1 LINE_OBJECT2_ID1,
405 RULE_INFORMATION1 COV_RULE_INFO1,
406 RULE_INFORMATION2 COV_RULE_INFO2,
407 RULE_INFORMATION3 COV_RULE_INFO3,
408 RULE_INFORMATION4 COV_RULE_INFO4,
409 RULE_INFORMATION5 COV_RULE_INFO5,
410 RULE_INFORMATION6 COV_RULE_INFO6,
411 RULE_INFORMATION7 COV_RULE_INFO7,
412 RULE_INFORMATION8 COV_RULE_INFO8,
413 RULE_INFORMATION9 COV_RULE_INFO9,
414 RULE_INFORMATION10 COV_RULE_INFO10,
415 RULE_INFORMATION11 COV_RULE_INFO11,
416 RULE_INFORMATION12 COV_RULE_INFO12,
417 RULE_INFORMATION13 COV_RULE_INFO13,
418 RULE_INFORMATION14 COV_RULE_INFO14,
419 RULE_INFORMATION15 COV_RULE_INFO15,
420 RULE_INFORMATION_CATEGORY COV_RULE_INFO,
421 RUL.OBJECT_VERSION_NUMBER COV_OBJ_VER_NUMBER
422 FROM
423 OKC_RULE_GROUPS_B RGP,
424 OKC_RULES_B RUL,
425 OKC_K_LINES_B LINE
426 WHERE LINE.ID = RGP.CLE_ID
427 AND RGP.ID = RUL.RGP_ID
428 AND LINE.LSE_ID in (3,16,21)
429 AND RUL.RULE_INFORMATION_CATEGORY IN ('OFS','CVR','DST','PRE','BTD')
430 AND LINE.DNZ_CHR_ID = RGP.DNZ_CHR_ID
431 -- AND RUL.RULE_INFORMATION15 IS NULL
432 AND Rul.rowid BETWEEN l_start_rowid and l_end_rowid
433 AND NOT EXISTS (SELECT CLE_ID FROM OKS_K_Lines_B where cle_id = LINE.ID)
434 ORDER BY LINE.ID;
435
436
437
438 TYPE RowId_Tbl_Type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
439 TYPE Num_Tbl_Type IS VARRAY(1000) OF NUMBER;
440 TYPE Date_Tbl_Type IS VARRAY(1000) OF DATE;
441 TYPE Vc15_Tbl_Type IS VARRAY(1000) OF VARCHAR2(15);
442 TYPE Vc20_Tbl_Type IS VARRAY(1000) OF VARCHAR2(20);
443 TYPE Vc150_Tbl_Type IS VARRAY(1000) OF VARCHAR2(150);
444 TYPE Vc30_Tbl_Type IS VARRAY(1000) OF VARCHAR2(30);
445 TYPE Vc1_Tbl_Type IS VARRAY(1000) OF VARCHAR2(1);
446 TYPE Vc3_Tbl_Type IS VARRAY(1000) OF VARCHAR2(3);
447 TYPE Vc2000_Tbl_Type IS VARRAY(1000) OF VARCHAR2(2000);
448 TYPE Vc50_Tbl_Type IS VARRAY(1000) OF VARCHAR2(50);
449 TYPE Vc80_Tbl_Type IS VARRAY(1000) OF VARCHAR2(80);
450 TYPE Vc120_Tbl_Type IS VARRAY(1000) OF VARCHAR2(120);
451 TYPE Vc600_Tbl_Type IS VARRAY(1000) OF VARCHAR2(600);
452
453
454 Rul_Row_ID_TBl RowId_Tbl_Type;
455 LINE_ID_TBL Num_Tbl_Type;
456 Line_Created_By_TBL Num_Tbl_Type;
457 Line_Creation_Date_TBl Date_Tbl_Type;
458 Line_Last_Updated_By_TBL Num_Tbl_Type;
459 Line_Last_Update_Date_TBL Date_Tbl_Type;
460 Line_Last_Update_Login_TBL Num_Tbl_Type;
461 RUL_ID_TBL Num_Tbl_Type;
462 LINE_RGP_ID_TBL Num_Tbl_Type;
463 LINE_LSE_ID_TBL Num_Tbl_Type;
464 LINE_DNZ_CHR_ID_TBL Num_Tbl_Type;
465 LINE_OBJECT1_ID1_TBL Vc150_Tbl_Type;
466 LINE_OBJECT2_ID1_TBL Vc150_Tbl_Type;
467 COV_RULE_INFO1_TBL Vc150_Tbl_Type;
468 COV_RULE_INFO2_TBL Vc150_Tbl_Type;
469 COV_RULE_INFO3_TBL Vc150_Tbl_Type;
470 COV_RULE_INFO4_TBL Vc150_Tbl_Type;
471 COV_RULE_INFO5_TBL Vc150_Tbl_Type;
472 COV_RULE_INFO6_TBL Vc150_Tbl_Type;
473 COV_RULE_INFO7_TBL Vc150_Tbl_Type;
474 COV_RULE_INFO8_TBL Vc150_Tbl_Type;
475 COV_RULE_INFO9_TBL Vc150_Tbl_Type;
476 COV_RULE_INFO10_TBL Vc150_Tbl_Type;
477 COV_RULE_INFO11_TBL Vc150_Tbl_Type;
478 COV_RULE_INFO12_TBL Vc150_Tbl_Type;
479 COV_RULE_INFO13_TBL Vc150_Tbl_Type;
480 COV_RULE_INFO14_TBL Vc150_Tbl_Type;
481 COV_RULE_INFO15_TBL Vc150_Tbl_Type;
482 COV_RULE_INFO_TBL Vc150_Tbl_Type;
483 COV_OBJ_VER_NUMBER_TBL Num_Tbl_Type;
484
485 x_clev_tbl_in oks_kln_pvt.klnv_tbl_type;
486 l_clev_tbl_in oks_kln_pvt.klnv_tbl_type;
487 l_cle_ctr NUMBER := 0;
488 tablename1 VArchar2(1000);
489 x_msg_count NUMBER := 0;
490 x_msg_data VArchar2(1000);
491 L_OLD_CLE_ID NUMBER := -99999;
492 L_CLE_ID NUMBER := -99999;
493 l_duration NUMBER;
494 l_period VArchar2(10);
495 l_return_Status VArchar2(3):= OKC_API.G_RET_STS_SUCCESS;
496 l_OU_CURRENCY VArchar2(10);
497
498
499 l_start_rowid ROWID := p_start_rowid;
500 l_end_rowid ROWID := p_end_rowid;
501 l_msg_data VARCHAR2(1000);
502 l_msg_index_out NUMBER;
503 l_message VARCHAR2(2400);
504
505
506 EXCEPTIONHALT_VALIDATION EXCEPTION;
507 G_EXCEPTION_HALT EXCEPTION;
508
509 PROCEDURE get_duration_period(p_id IN NUMBER,
510 x_duration OUT NOCOPY NUMBER,
511 x_period OUT NOCOPY VARCHAR2) IS
512
513 CURSOR Csr_get_duration (l_ID IN NUMBER) IS
514 SELECT UOM_CODE,Duration
515 FROM OKC_TIMEVALUES_V
516 WHERE id = l_id;
517
518 Lx_Duration NUMBER := NULL;
519 Lx_Period VARCHAR2(100) := NULL;
520 G_APP_NAME VARCHAR2(100) := NULL;
521
522 BEGIN
523 G_APP_NAME := 'get_duration_period';
524
525 FOR Csr_get_duration_Rec in Csr_get_duration(p_id) LOOP
526
527 Lx_period := Csr_get_duration_Rec.uom_code;
528 Lx_duration := Csr_get_duration_Rec.Duration;
529
530 END LOOP;
531
532 X_duration := Lx_duration;
533 X_Period :=Lx_Period;
534
535 EXCEPTION
536 WHEN OTHERS THEN
537 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
538 G_APP_NAME,
539 G_PKG_NAME,
540 'OKC_API.G_RET_STS_UNEXP_ERROR',
541 x_msg_count,
542 x_msg_data,
543 '_PVT'
544 );
545
546
547 END get_duration_period;
548
549 PROCEDURE Create_Coverage_Time( P_Rule_Id IN NUMBER,
550 P_Cle_Id IN NUMBER,
551 P_Dnz_Chr_ID IN NUMBER,
552 X_return_Status OUT NOCOPY VARCHAR2)IS
553
554 l_rule_ID NUMBER := P_rule_Id;
555 l_cle_Id NUMBER := P_Cle_id;
556 l_dnz_Id NUMBER :=P_Dnz_Chr_ID;
557
558 l_COV_TZE_LINE_ID NUMBER := null;
559
560 l_count NUMBER := 0;
561 G_APP_NAME VARCHAR2(100) := NULL;
562 G_EXCEPTIONHALT_VALIDATION EXCEPTION;
563
564 CURSOR Csr_get_time_zone_ID (rule_id IN NUMBER) IS
565 SELECT Times.tze_id tze_id,
566 Times.Created_By Times_Created_By,
567 Times.Last_Updated_By Times_Last_Updated_By,
568 Times.Last_Update_Date Times_Last_Update_Date,
569 Times.Last_Update_Login Times_Last_Update_Login
570 FROM okc_timevalues_v times,
571 okc_cover_times cvt
572 WHERE CVT.tve_ID = TIMES.id
573 AND CVT.rul_id = rule_id
574 AND rownum = 1;
575
576 CURSOR Csr_get_count_time_zone_ID(k_cle_Id IN NUMBER,k_dnz_Id IN NUMBER) IS
577 SELECT COUNT(*) NCOUNT
578 FROM OKS_COVERAGE_TIMEZONES
579 WHERE cle_id = k_cle_Id
580 AND dnz_chr_Id = k_dnz_Id;
581
582 BEGIN
583 G_APP_NAME := 'Create_Coverage_Time';
584
585 FOR get_count_time_zone_ID_Rec IN Csr_get_count_time_zone_ID(l_cle_Id, l_dnz_Id) LOOP
586 l_count := get_count_time_zone_ID_Rec.NCOUNT;
587 END LOOP;
588
589 IF l_count =0 THEN
590
591 l_COV_TZE_LINE_ID := null;
592 l_ctz_rec := l_ctz_rec + 1;
593
594 FOR get_time_zone_ID_Rec IN Csr_get_time_zone_ID(l_rule_ID) LOOP
595
596
597 l_ctzv_tbl_in(l_ctz_rec).Id := okc_p_util.raw_to_number(sys_guid());
598
599
600 l_ctzv_tbl_in(l_ctz_rec).Created_By := get_time_zone_ID_Rec.Times_Created_By;
601 l_ctzv_tbl_in(l_ctz_rec).Last_Updated_By := get_time_zone_ID_Rec.Times_Last_Updated_By;
602 l_ctzv_tbl_in(l_ctz_rec).Last_Update_Date := get_time_zone_ID_Rec.Times_Last_Update_Date;
603 l_ctzv_tbl_in(l_ctz_rec).Last_Update_Login := get_time_zone_ID_Rec.Times_Last_Update_Login;
604 l_ctzv_tbl_in(l_ctz_rec).Cle_Id := l_Cle_Id;
605 l_ctzv_tbl_in(l_ctz_rec).Dnz_Chr_Id := l_dnz_Id;
606 l_ctzv_tbl_in(l_ctz_rec).DEFAULT_YN := 'Y';
607 l_ctzv_tbl_in(l_ctz_rec).TIMEZONE_ID := get_time_zone_ID_Rec.tze_id;
608 l_ctzv_tbl_in(l_ctz_rec).object_version_number := 1;
609
610
611 END LOOP;
612 END IF;
613
614 X_return_Status := 'S';
615 X_Message_Data := NULL;
616
617 EXCEPTION
618 WHEN OTHERS THEN
619
620 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
621 G_APP_NAME,
622 G_PKG_NAME,
623 'OKC_API.G_RET_STS_UNEXP_ERROR',
624 x_msg_count,
625 x_msg_data,
626 '_PVT'
627 );
628 IF x_msg_count > 0 THEN
629 FOR i in 1..x_msg_count LOOP
630 fnd_msg_pub.get (p_msg_index => -1,
631 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
632 p_data => l_msg_data,
633 p_msg_index_out => l_msg_index_out);
634 l_message := l_message||' ; '||l_msg_data;
635
636 END LOOP;
637 END IF;
638 x_message_data := l_message;
639 X_return_Status := 'E';
640
641 END Create_Coverage_Time;
642
643
644 BEGIN
645
646 G_APP_NAME := 'Business_Process_migration';
647 l_OU_CURRENCY := OKC_CURRENCY_API.GET_OU_CURRENCY;
648
649 OPEN Csr_Get_Buss_Process (l_start_rowid,l_end_rowid);
650 LOOP
651 BEGIN
652 FETCH Csr_Get_Buss_Process BULK COLLECT INTO
653 LINE_ID_TBL ,
654 Line_Created_By_TBL ,
655 Line_Creation_Date_TBL ,
656 Line_Last_Updated_By_TBL ,
657 Line_Last_Update_Date_TBL ,
658 Line_Last_Update_Login_TBL ,
659 RUL_ID_TBL ,
660 LINE_RGP_ID_TBL ,
661 Rul_Row_ID_TBl ,
662 LINE_LSE_ID_TBL ,
663 LINE_DNZ_CHR_ID_TBL ,
664 LINE_OBJECT1_ID1_TBL ,
665 LINE_OBJECT2_ID1_TBL ,
666 COV_RULE_INFO1_TBL ,
667 COV_RULE_INFO2_TBL ,
668 COV_RULE_INFO3_TBL ,
669 COV_RULE_INFO4_TBL ,
670 COV_RULE_INFO5_TBL ,
671 COV_RULE_INFO6_TBL ,
672 COV_RULE_INFO7_TBL ,
673 COV_RULE_INFO8_TBL ,
674 COV_RULE_INFO9_TBL ,
675 COV_RULE_INFO10_TBL ,
676 COV_RULE_INFO11_TBL ,
677 COV_RULE_INFO12_TBL ,
678 COV_RULE_INFO13_TBL ,
679 COV_RULE_INFO14_TBL ,
680 COV_RULE_INFO15_TBL ,
681 COV_RULE_INFO_TBL ,
682 COV_OBJ_VER_NUMBER_TBL
683 LIMIT 1000;
684
685 -- -- dbms_output.put_line('Value of LINE_ID_TBL.COUNT='||TO_CHAR(LINE_ID_TBL.COUNT));
686
687 IF LINE_ID_TBL.COUNT > 0 THEN
688
689 FOR I IN LINE_ID_TBL.FIRST .. LINE_ID_TBL.LAST LOOP
690 L_Cle_id := LINE_ID_TBL(i);
691
692 IF (L_OLD_CLE_ID <> L_CLE_ID) THEN
693 l_cle_ctr := l_cle_ctr + 1;
694
695
696 -- l_clev_tbl_in(l_cle_ctr).Id := okc_p_util.raw_to_number(sys_guid());
697 l_clev_tbl_in(l_cle_ctr).Created_By := Line_Created_By_TBL(i);
698 l_clev_tbl_in(l_cle_ctr).Creation_Date := Line_Creation_Date_Tbl(i);
699 l_clev_tbl_in(l_cle_ctr).Last_Updated_By := Line_Last_Updated_By_TBL(i);
700 l_clev_tbl_in(l_cle_ctr).Last_Update_Date := Line_Last_Update_Date_TBL(i);
701 l_clev_tbl_in(l_cle_ctr).Last_Update_Login := Line_Last_Update_Login_TBL(i);
702 l_clev_tbl_in(l_cle_ctr).Cle_Id := LINE_ID_TBL(i);
703 l_clev_tbl_in(l_cle_ctr).Dnz_Chr_Id := LINE_DNZ_CHR_ID_TBL(i);
704 l_clev_tbl_in(l_cle_ctr).object_version_number := 1;
705 l_clev_tbl_in(l_cle_ctr).Sync_Date_Install := 'N';
706
707 END IF;
708
709
710 IF COV_RULE_INFO_TBL(i) = 'OFS' THEN
711 IF COV_RULE_INFO1_TBL(i) IS NOT NULL THEN
712
713 get_duration_period(p_id => COV_RULE_INFO1_TBL(i),
714 x_duration => l_duration,
715 x_period => l_period);
716
717 END IF;
718
719 l_clev_tbl_in(l_cle_ctr).OFFSET_DURATION := l_duration;
720 l_clev_tbl_in(l_cle_ctr).OFFSET_PERIOD := l_period;
721
722 END IF;
723
724
725
726 IF COV_RULE_INFO_TBL(i) = 'DST' THEN
727 l_clev_tbl_in(l_cle_ctr).DISCOUNT_LIST := LINE_OBJECT1_ID1_TBL(i);
728 END IF;
729
730
731 IF COV_RULE_INFO_TBL(i) = 'PRE' THEN
732
733 UPDATE OKC_K_LINES_B
734 SET PRICE_LIST_ID = LINE_OBJECT1_ID1_TBL(i),
735 CURRENCY_CODE = l_OU_CURRENCY
736 WHERE ID = LINE_ID_TBL(i);
737
738 END IF;
739
740
741 IF COV_RULE_INFO_TBL(i) = 'BTD' THEN
742
743 l_clev_tbl_in(l_cle_ctr).ALLOW_BT_DISCOUNT := 'Y';
744 END IF;
745
746
747 IF COV_RULE_INFO_TBL(i) = 'CVR' THEN
748
749 Create_Coverage_Time( P_Rule_Id => RUL_ID_TBL(i),
750 P_Cle_Id => LINE_ID_TBL(i),
751 P_Dnz_Chr_ID => LINE_DNZ_CHR_ID_TBL(i),
752 X_return_Status => l_return_Status);
753
754 IF l_return_Status <> 'S' THEN
755 RAISE EXCEPTIONHALT_VALIDATION;
756 END IF;
757
758 END IF;
759 --************************************************
760
761 L_OLD_CLE_ID := L_CLE_ID;
762
763 --*************************************************
764
765 END LOOP;
766 END IF;
767
768
769 tablename1 := 'OKS_K_LINES';
770
771 IF l_clev_tbl_in.count > 0 THEN
772 oks_kln_pvt.insert_row
773 (
774 x_return_status => l_return_status,
775 p_klnv_tbl => l_clev_tbl_in,
776 p_api_version =>1,
777 p_init_msg_list => null,
778 x_msg_count => x_msg_count,
779 x_msg_data => x_msg_data,
780 x_klnv_tbl => x_clev_tbl_in
781 );
782
783 END IF;
784 -- -- dbms_output.put_line('oks_kln_pvt Value of l_return_status='||l_return_status);
785
786
787 IF x_msg_count > 0 THEN
788 FOR i in 1..x_msg_count LOOP
789 apps.fnd_msg_pub.get (p_msg_index => -1,
790 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
791 p_data => x_msg_data,
792 p_msg_index_out => l_msg_index_out);
793 l_message := l_message||' ; '||x_msg_data;
794 -- -- dbms_output.put_line(x_msg_data);
795 END LOOP;
796 END IF;
797
798 -- -- dbms_output.put_line('Value of l_ctzv_tbl_in.COUNT='||TO_CHAR(l_ctzv_tbl_in.COUNT));
799
800 IF l_return_status = 'S' THEN
801 l_message := NULL;
802
803 IF l_ctzv_tbl_in.COUNT > 0 THEN
804 OKS_CTZ_PVT.insert_row
805 (
806 x_return_status => l_return_status,
807 p_oks_coverage_timezones_v_tbl => l_ctzv_tbl_in,
808 p_api_version =>1,
809 p_init_msg_list => null, --standard
810 x_msg_count => x_msg_count,
811 x_msg_data => x_msg_data,
812 x_oks_coverage_timezones_v_tbl => x_ctzv_tbl_in
813 );
814 END IF;
815 -- -- dbms_output.put_line('OKS_CTZ_PVT Value of l_return_status='||l_return_status);
816
817 IF x_msg_count > 0 THEN
818 FOR i in 1..x_msg_count LOOP
819 apps.fnd_msg_pub.get (p_msg_index => -1,
820 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
821 p_data => x_msg_data,
822 p_msg_index_out => l_msg_index_out);
823 -- l_message := l_message||' ; '||x_msg_data;
824 -- -- dbms_output.put_line(x_msg_data);
825 END LOOP;
826 END IF;
827
828
829 END IF;
830
831 l_ctzv_tbl_in.delete;
832 l_clev_tbl_in.delete;
833
834 IF l_return_status = 'S' THEN
835 x_return_status := 'S';
836 x_message_data := NULL;
837 ELSE
838 RAISE G_EXCEPTION_HALT;
839 END IF;
840
841
842
843 EXCEPTION
844
845
846 WHEN EXCEPTIONHALT_VALIDATION THEN
847 -- -- dbms_output.put_line('SQLERRM ---->'||SQLERRM);
848 ROLLBACK;
849 l_ctzv_tbl_in.delete;
850 l_clev_tbl_in.delete;
851 l_cvtv_tbl_in.delete;
852
853 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
854 G_APP_NAME,
855 G_PKG_NAME,
856 'OKC_API.G_RET_STS_UNEXP_ERROR',
857 x_msg_count,
858 x_msg_data,
859 '_PVT'
860 );
861
862 IF x_msg_count > 0 THEN
863 FOR i in 1..x_msg_count LOOP
864 fnd_msg_pub.get (p_msg_index => -1,
865 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
866 p_data => l_msg_data,
867 p_msg_index_out => l_msg_index_out);
868 l_message := l_message||' ; '||l_msg_data;
869
870 END LOOP;
871 END IF;
872
873 x_message_data := l_message;
874 x_return_status := 'E';
875 WHEN G_EXCEPTION_HALT THEN
876 -- -- dbms_output.put_line('222 --->SQLERRM ---->'||SQLERRM);
877 ROLLBACK;
878 l_ctzv_tbl_in.delete;
879 l_clev_tbl_in.delete;
880 l_cvtv_tbl_in.delete;
881
882
883 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
884 G_APP_NAME,
885 G_PKG_NAME,
886 'OKC_API.G_RET_STS_UNEXP_ERROR',
887 x_msg_count,
888 x_msg_data,
889 '_PVT'
890 );
891
892 IF x_msg_count > 0 THEN
893 FOR i in 1..x_msg_count LOOP
894 fnd_msg_pub.get (p_msg_index => -1,
895 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
896 p_data => l_msg_data,
897 p_msg_index_out => l_msg_index_out);
898 l_message := l_message||' ; '||l_msg_data;
899
900 END LOOP;
901 END IF;
902 x_message_data := l_message;
903 x_return_status := 'E';
904
905 WHEN Others THEN
906
907 ROLLBACK;
908 l_ctzv_tbl_in.delete;
909 l_clev_tbl_in.delete;
910 l_cvtv_tbl_in.delete;
911
912
913 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
914 G_APP_NAME,
915 G_PKG_NAME,
916 'OKC_API.G_RET_STS_UNEXP_ERROR',
917 x_msg_count,
918 x_msg_data,
919 '_PVT'
920 );
921 IF x_msg_count > 0 THEN
922 FOR i in 1..x_msg_count LOOP
923 fnd_msg_pub.get (p_msg_index => -1,
924 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
925 p_data => l_msg_data,
926 p_msg_index_out => l_msg_index_out);
927 l_message := l_message||' ; '||l_msg_data;
928
929 END LOOP;
930 END IF;
931 x_message_data := l_message;
932 x_return_status := 'E';
933 END;
934
935 EXIT WHEN Csr_Get_Buss_Process%NOTFOUND;
936
937 END LOOP;
938
939 CLOSE Csr_Get_Buss_Process;
940
941 EXCEPTION
942 WHEN Others THEN
943
944 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
945 G_APP_NAME,
946 G_PKG_NAME,
947 'OKC_API.G_RET_STS_UNEXP_ERROR',
948 x_msg_count,
949 x_msg_data,
950 '_PVT'
951 );
952
953 IF x_msg_count > 0 THEN
954 FOR i in 1..x_msg_count LOOP
955 fnd_msg_pub.get (p_msg_index => -1,
956 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
957 p_data => l_msg_data,
958 p_msg_index_out => l_msg_index_out);
959 l_message := l_message||' ; '||l_msg_data;
960
961 END LOOP;
962 END IF;
963 x_message_data := l_message;
964 x_return_status := 'E';
965 END Business_Process_migration;
966
967 --PROCEDURE COVERAGE_TIMES_MIGRATION ( x_return_status OUT NOCOPY VARCHAR2) IS
968 PROCEDURE COVERAGE_TIMES_MIGRATION ( p_start_rowid IN ROWID,
969 p_end_rowid IN ROWID,
970 x_return_status OUT NOCOPY VARCHAR2,
971 x_message_data OUT NOCOPY VARCHAR2) IS
972
973 CURSOR Csr_Get_Coverage_times (l_start_rowid IN ROWID ,l_end_rowid IN ROWID ) IS
974 SELECT CTZ.ID TimeZone_ID,
975 CTZ.DNZ_CHR_ID DNZ_CHR_ID,
976 TIMES.ROWID TIMES_ROW_ID,
977 TIMES.TVE_ID_STARTED TVE_ID_STARTED,
978 TIMES.TVE_ID_ENDED TVE_ID_ENDED,
979 Times.Created_By Times_Created_By,
980 Times.Last_Updated_By Times_Last_Updated_By,
981 Times.Last_Update_Date Times_Last_Update_Date,
982 Times.Last_Update_Login Times_Last_Update_Login,
983 Times.Attribute15 Times_Attribute15
984 FROM OKS_COVERAGE_TIMEZONES CTZ,
985 OKC_RULE_GROUPS_B RGP,
986 OKC_RULES_B RUL,
987 OKC_COVER_TIMES CVT,
988 OKC_TIMEVALUES_B TIMES
989 WHERE CTZ.Cle_Id = RGP.CLE_ID
990 AND CTZ.DNZ_CHR_ID = RGP.DNZ_CHR_ID
991 AND RGP.ID = RUL.RGP_ID
992 AND RGP.DNZ_CHR_ID = RUL.DNZ_CHR_ID
993 AND RUL.RULE_INFORMATION_CATEGORY = 'CVR'
994 AND RUL.ID = CVT.RUL_ID
995 AND CVT.TVE_ID = TIMES.ID
996 --AND Times.Attribute15 IS NULL
997 AND Times.rowid BETWEEN l_start_rowid and l_end_rowid
998 AND NOT EXISTS (Select COV_TZE_LINE_ID from OKS_COVERAGE_TIMES where COV_TZE_LINE_ID= CTZ.ID);
999
1000
1001 CURSOR get_Coverage_time_Cur (l_tve_id IN NUMBER) IS
1002 SELECT tve_type,day_of_week,hour,minute
1003 FROM okc_timevalues_v
1004 WHERE ID = l_tve_id;
1005
1006 TYPE Vc420_Tbl_Type IS VARRAY(1000) OF VARCHAR2(420);
1007 TYPE Num_Tbl_Type IS VARRAY(1000) OF NUMBER;
1008 TYPE RowId_Tbl_Type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
1009 TYPE Date_Tbl_Type IS VARRAY(1000) OF DATE;
1010
1011
1012 TIMES_ROW_ID RowId_Tbl_Type;
1013 TimeZone_ID_TBL Num_Tbl_Type;
1014 TVE_ID_STARTED_TBL Num_Tbl_Type;
1015 TVE_ID_ENDED_TBL Num_Tbl_Type;
1016 DNZ_CHR_ID_TBL Num_Tbl_Type;
1017 Times_Attribute15_TBL Vc420_Tbl_Type;
1018 TIMES_Created_By_TBL Num_Tbl_Type;
1019 TIMES_Last_Updated_By_TBL Num_Tbl_Type;
1020 TIMES_Last_Update_Date_TBL Date_Tbl_Type;
1021 TIMES_Last_Update_Login_TBL Num_Tbl_Type;
1022
1023 tablename1 VARCHAR2(1000);
1024 l_return_status VARCHAR2(1) :=OKC_API.G_RET_STS_SUCCESS;
1025 l_msg_count NUMBER;
1026
1027 l_start_rowid ROWID := p_start_rowid;
1028 l_end_rowid ROWID := p_end_rowid;
1029 l_msg_data VARCHAR2(1000);
1030 l_msg_index_out NUMBER;
1031 l_message VARCHAR2(2400);
1032 x_msg_count NUMBER := 0;
1033 x_msg_data VArchar2(1000);
1034
1035 G_EXCEPTION_HALT EXCEPTION;
1036 BEGIN
1037 G_APP_NAME := 'COVERAGE_TIMES_MIGRATION';
1038
1039 OPEN Csr_Get_Coverage_times (l_start_rowid,l_end_rowid);
1040 LOOP
1041 BEGIN
1042 l_cvt_rec := 0;
1043 FETCH Csr_Get_Coverage_times BULK COLLECT INTO
1044 TimeZone_ID_Tbl,
1045 DNZ_CHR_ID_TBL,
1046 TIMES_ROW_ID,
1047 TVE_ID_STARTED_Tbl,
1048 TVE_ID_ENDED_Tbl,
1049 TIMES_Created_By_TBL,
1050 TIMES_Last_Updated_By_TBL,
1051 TIMES_Last_Update_Date_TBL,
1052 TIMES_Last_Update_Login_TBL,
1053 Times_Attribute15_TBL
1054 LIMIT 1000;
1055
1056 -- -- dbms_output.put_line('Value of TimeZone_ID_Tbl.COUNT='||TO_CHAR(TimeZone_ID_Tbl.COUNT));
1057
1058 IF TimeZone_ID_Tbl.COUNT > 0 THEN
1059 FOR I IN TimeZone_ID_Tbl.FIRST .. TimeZone_ID_Tbl.LAST LOOP
1060
1061 l_cvt_rec := l_cvt_rec + 1;
1062
1063
1064 l_cvtv_tbl_in(l_cvt_rec).ID := okc_p_util.raw_to_number(sys_guid());
1065
1066
1067 l_cvtv_tbl_in(l_cvt_rec).COV_TZE_LINE_ID := TimeZone_ID_Tbl(i);
1068 l_cvtv_tbl_in(l_cvt_rec).DNZ_CHR_ID := DNZ_CHR_ID_TBL(i);
1069
1070 l_cvtv_tbl_in(l_cvt_rec).Created_By := TIMES_Created_By_TBL(i);
1071 l_cvtv_tbl_in(l_cvt_rec).Last_Updated_By := TIMES_Last_Updated_By_TBL(i);
1072 l_cvtv_tbl_in(l_cvt_rec).Last_Update_Date := TIMES_Last_Update_Date_TBL(i);
1073 l_cvtv_tbl_in(l_cvt_rec).Last_Update_Login := TIMES_Last_Update_Login_TBL(i);
1074 l_cvtv_tbl_in(l_cvt_rec).object_version_number := 1;
1075
1076 FOR get_Coverage_time_REC IN get_Coverage_time_Cur(TVE_ID_STARTED_TBL(i)) LOOP
1077
1078 IF get_Coverage_time_REC.day_of_week = 'SUN' THEN
1079
1080 l_cvtv_tbl_in(l_cvt_rec).SUNDAY_YN := 'Y';
1081
1082 ELSIF get_Coverage_time_REC.day_of_week = 'MON' THEN
1083
1084 l_cvtv_tbl_in(l_cvt_rec).MONDAY_YN := 'Y';
1085
1086 ELSIF get_Coverage_time_REC.day_of_week = 'TUE' THEN
1087 l_cvtv_tbl_in(l_cvt_rec).TUESDAY_YN := 'Y';
1088
1089 ELSIF get_Coverage_time_REC.day_of_week = 'WED' THEN
1090 l_cvtv_tbl_in(l_cvt_rec).WEDNESDAY_YN := 'Y';
1091
1092 ELSIF get_Coverage_time_REC.day_of_week = 'THU' THEN
1093 l_cvtv_tbl_in(l_cvt_rec).THURSDAY_YN := 'Y';
1094
1095 ELSIF get_Coverage_time_REC.day_of_week = 'FRI' THEN
1096 l_cvtv_tbl_in(l_cvt_rec).FRIDAY_YN := 'Y';
1097
1098 ELSIF get_Coverage_time_REC.day_of_week = 'SAT' THEN
1099 l_cvtv_tbl_in(l_cvt_rec).SATURDAY_YN := 'Y';
1100
1101 END IF;
1102
1103 l_cvtv_tbl_in(l_cvt_rec).START_HOUR := get_Coverage_time_REC.HOUR;
1104 l_cvtv_tbl_in(l_cvt_rec).START_MINUTE := get_Coverage_time_REC.MINUTE;
1105
1106 END LOOP;
1107
1108 FOR get_Coverage_time_REC IN get_Coverage_time_Cur(TVE_ID_ENDED_Tbl(i)) LOOP
1109
1110 l_cvtv_tbl_in(l_cvt_rec).END_HOUR := get_Coverage_time_REC.HOUR;
1111 l_cvtv_tbl_in(l_cvt_rec).END_MINUTE := get_Coverage_time_REC.MINUTE;
1112
1113 END LOOP;
1114
1115
1116 END LOOP;
1117 END IF;
1118
1119 tablename1 := 'oks_coverage_times';
1120 -- -- dbms_output.put_line('Value of l_cvtv_tbl_in.COUNT ='||TO_CHAR(l_cvtv_tbl_in.COUNT ));
1121
1122 IF l_cvtv_tbl_in.COUNT > 0 THEN
1123
1124 OKS_Insert_Row_Upg.INSERT_ROW_UPG_CVTV_TBL( x_return_status => l_return_status,
1125 P_CVTV_TBL =>l_cvtv_tbl_in);
1126
1127 -- -- dbms_output.put_line('Value of l_return_status='||l_return_status);
1128 IF l_return_status = 'S' THEN
1129 x_return_status := 'S';
1130 x_message_data := NULL;
1131 ELSE
1132 RAISE G_EXCEPTION_HALT;
1133 END IF;
1134
1135 END IF;
1136
1137
1138 EXIT WHEN Csr_Get_Coverage_times%NOTFOUND;
1139
1140 EXCEPTION
1141 WHEN G_EXCEPTION_HALT THEN
1142 ROLLBACK;
1143 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1144 G_APP_NAME,
1145 G_PKG_NAME,
1146 'OKC_API.G_RET_STS_UNEXP_ERROR',
1147 x_msg_count,
1148 x_msg_data,
1149 '_PVT'
1150 );
1151
1152 IF x_msg_count > 0 THEN
1153 FOR i in 1..x_msg_count LOOP
1154 fnd_msg_pub.get (p_msg_index => -1,
1155 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
1156 p_data => l_msg_data,
1157 p_msg_index_out => l_msg_index_out);
1158 l_message := l_message||' ; '||l_msg_data;
1159
1160 END LOOP;
1161 END IF;
1162
1163 x_message_data := l_message;
1164 x_return_status := 'E';
1165 WHEN Others THEN
1166 ROLLBACK;
1167 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1168 G_APP_NAME,
1169 G_PKG_NAME,
1170 'OKC_API.G_RET_STS_UNEXP_ERROR',
1171 x_msg_count,
1172 x_msg_data,
1173 '_PVT'
1174 );
1175
1176 IF x_msg_count > 0 THEN
1177 FOR i in 1..x_msg_count LOOP
1178 fnd_msg_pub.get (p_msg_index => -1,
1179 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
1180 p_data => l_msg_data,
1181 p_msg_index_out => l_msg_index_out);
1182 l_message := l_message||' ; '||l_msg_data;
1183
1184 END LOOP;
1185 END IF;
1186
1187 x_message_data := l_message;
1188 x_return_status := 'E';
1189
1190 END;
1191 EXIT WHEN Csr_Get_Coverage_times%NOTFOUND;
1192 END LOOP;
1193 CLOSE Csr_Get_Coverage_times;
1194 EXCEPTION
1195 WHEN Others THEN
1196 Raise;
1197 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1198 G_APP_NAME,
1199 G_PKG_NAME,
1200 'OKC_API.G_RET_STS_UNEXP_ERROR',
1201 x_msg_count,
1202 x_msg_data,
1203 '_PVT'
1204 );
1205
1206 IF x_msg_count > 0 THEN
1207 FOR i in 1..x_msg_count LOOP
1208 fnd_msg_pub.get (p_msg_index => -1,
1209 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
1210 p_data => l_msg_data,
1211 p_msg_index_out => l_msg_index_out);
1212 l_message := l_message||' ; '||l_msg_data;
1213
1214 END LOOP;
1215 END IF;
1216 x_message_data := l_message;
1217 x_return_status := 'E';
1218
1219 END COVERAGE_TIMES_MIGRATION;
1220
1221
1222 --PROCEDURE Reaction_Time_migration( x_return_status OUT NOCOPY VARCHAR2) IS
1223 PROCEDURE Reaction_Time_migration( p_start_rowid IN ROWID,
1224 p_end_rowid IN ROWID,
1225 x_return_status OUT NOCOPY VARCHAR2,
1226 x_message_data OUT NOCOPY VARCHAR2) IS
1227
1228 CURSOR Csr_Get_Reaction_Times (l_start_rowid IN ROWID ,l_end_rowid IN ROWID ) IS
1229 SELECT
1230 LINE.ID LINE_ID,
1231 Line.Created_By Line_Created_By,
1232 Line.Creation_Date Line_Creation_Date,
1233 Line.Last_Updated_By Line_Last_Updated_By,
1234 Line.Last_Update_Date Line_Last_Update_Date,
1235 Line.Last_Update_Login Line_Last_Update_Login,
1236 Rul.ROWID RUL_ROW_ID,
1237 Rul.ID Rul_Id,
1238 RGP.ID LINE_RGP_ID,
1239 LINE.LSE_ID LINE_LSE_ID,
1240 LINE.DNZ_CHR_ID LINE_DNZ_CHR_ID,
1241 OBJECT1_ID1 LINE_OBJECT1_ID1,
1242 OBJECT2_ID1 LINE_OBJECT2_ID1,
1243 RULE_INFORMATION1 COV_RULE_INFO1,
1244 RULE_INFORMATION2 COV_RULE_INFO2,
1245 RULE_INFORMATION3 COV_RULE_INFO3,
1246 RULE_INFORMATION4 COV_RULE_INFO4,
1247 RULE_INFORMATION5 COV_RULE_INFO5,
1248 RULE_INFORMATION6 COV_RULE_INFO6,
1249 RULE_INFORMATION7 COV_RULE_INFO7,
1250 RULE_INFORMATION8 COV_RULE_INFO8,
1251 RULE_INFORMATION9 COV_RULE_INFO9,
1252 RULE_INFORMATION10 COV_RULE_INFO10,
1253 RULE_INFORMATION11 COV_RULE_INFO11,
1254 RULE_INFORMATION12 COV_RULE_INFO12,
1255 RULE_INFORMATION13 COV_RULE_INFO13,
1256 RULE_INFORMATION14 COV_RULE_INFO14,
1257 RULE_INFORMATION15 COV_RULE_INFO15,
1258 RULE_INFORMATION_CATEGORY COV_RULE_INFO,
1259 RUL.OBJECT_VERSION_NUMBER COV_OBJ_VER_NUMBER
1260 FROM
1261 OKC_RULE_GROUPS_B RGP,
1262 OKC_RULES_B RUL,
1263 OKC_K_LINES_B LINE
1264 WHERE LINE.ID = RGP.CLE_ID
1265 AND RGP.ID = RUL.RGP_ID
1266 AND LINE.LSE_ID IN (4,17,22)
1267 AND RUL.RULE_INFORMATION_CATEGORY IN ('RCN','RSN')
1268 -- AND RUL.RULE_INFORMATION15 IS NULL
1269 AND Rul.rowid BETWEEN l_start_rowid and l_end_rowid
1270 AND NOT EXISTS (SELECT CLE_ID FROM OKS_K_LINES_B WHERE CLE_ID = LINE.ID)
1271 ORDER BY LINE.ID;
1272
1273
1274 TYPE RowId_Tbl_Type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
1275 TYPE Num_Tbl_Type IS VARRAY(1000) OF NUMBER;
1276 TYPE Date_Tbl_Type IS VARRAY(1000) OF DATE;
1277 TYPE Vc15_Tbl_Type IS VARRAY(1000) OF VARCHAR2(15);
1278 TYPE Vc20_Tbl_Type IS VARRAY(1000) OF VARCHAR2(20);
1279 TYPE Vc150_Tbl_Type IS VARRAY(1000) OF VARCHAR2(150);
1280 TYPE Vc30_Tbl_Type IS VARRAY(1000) OF VARCHAR2(30);
1281 TYPE Vc1_Tbl_Type IS VARRAY(1000) OF VARCHAR2(1);
1282 TYPE Vc3_Tbl_Type IS VARRAY(1000) OF VARCHAR2(3);
1283 TYPE Vc2000_Tbl_Type IS VARRAY(1000) OF VARCHAR2(2000);
1284 TYPE Vc50_Tbl_Type IS VARRAY(1000) OF VARCHAR2(50);
1285 TYPE Vc80_Tbl_Type IS VARRAY(1000) OF VARCHAR2(80);
1286 TYPE Vc120_Tbl_Type IS VARRAY(1000) OF VARCHAR2(120);
1287 TYPE Vc600_Tbl_Type IS VARRAY(1000) OF VARCHAR2(600);
1288
1289
1290 LINE_ID_TBL Num_Tbl_Type;
1291 Line_Created_By_TBL Num_Tbl_Type;
1292 Line_Creation_Date_Tbl Date_Tbl_Type;
1293 Line_Last_Updated_By_TBL Num_Tbl_Type;
1294 Line_Last_Update_Date_TBL Date_Tbl_Type;
1295 Line_Last_Update_Login_TBL Num_Tbl_Type;
1296 RUL_ROW_ID_TBL RowId_Tbl_Type;
1297 RUL_ID_TBL Num_Tbl_Type;
1298 LINE_RGP_ID_TBL Num_Tbl_Type;
1299 LINE_LSE_ID_TBL Num_Tbl_Type;
1300 LINE_DNZ_CHR_ID_TBL Num_Tbl_Type;
1301 LINE_OBJECT1_ID1_TBL Vc150_Tbl_Type;
1302 LINE_OBJECT2_ID1_TBL Vc150_Tbl_Type;
1303 COV_RULE_INFO1_TBL Vc150_Tbl_Type;
1304 COV_RULE_INFO2_TBL Vc150_Tbl_Type;
1305 COV_RULE_INFO3_TBL Vc150_Tbl_Type;
1306 COV_RULE_INFO4_TBL Vc150_Tbl_Type;
1307 COV_RULE_INFO5_TBL Vc150_Tbl_Type;
1308 COV_RULE_INFO6_TBL Vc150_Tbl_Type;
1309 COV_RULE_INFO7_TBL Vc150_Tbl_Type;
1310 COV_RULE_INFO8_TBL Vc150_Tbl_Type;
1311 COV_RULE_INFO9_TBL Vc150_Tbl_Type;
1312 COV_RULE_INFO10_TBL Vc150_Tbl_Type;
1313 COV_RULE_INFO11_TBL Vc150_Tbl_Type;
1314 COV_RULE_INFO12_TBL Vc150_Tbl_Type;
1315 COV_RULE_INFO13_TBL Vc150_Tbl_Type;
1316 COV_RULE_INFO14_TBL Vc150_Tbl_Type;
1317 COV_RULE_INFO15_TBL Vc150_Tbl_Type;
1318 COV_RULE_INFO_TBL Vc150_Tbl_Type;
1319 COV_OBJ_VER_NUMBER_TBL Num_Tbl_Type;
1320
1321
1322 x_clev_tbl_in oks_kln_pvt.klnv_tbl_type;
1323 l_clev_tbl_in oks_kln_pvt.klnv_tbl_type;
1324
1325 x_actv_tbl_in OKS_ACT_PVT.OksActionTimeTypesVTblType;
1326 l_actv_tbl_in OKS_ACT_PVT.OksActionTimeTypesVTblType;
1327
1328
1329 L_OLD_CLE_ID NUMBER := -99999;
1330 L_CLE_ID NUMBER := -99999;
1331 l_cle_ctr NUMBER := 0;
1332 l_return_Status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
1333 x_msg_count NUMBER := 0;
1334 x_msg_data VArchar2(1000);
1335
1336 l_start_rowid ROWID := p_start_rowid;
1337 l_end_rowid ROWID := p_end_rowid;
1338
1339 l_msg_data VARCHAR2(1000);
1340 l_msg_index_out NUMBER;
1341 l_message VARCHAR2(2400);
1342
1343 G_EXCEPTION_HALT EXCEPTION;
1344
1345 BEGIN
1346 G_APP_NAME := 'Reaction_Time_migration';
1347 OPEN Csr_Get_Reaction_Times (l_start_rowid,l_end_rowid);
1348 LOOP
1349 BEGIN
1350 FETCH Csr_Get_Reaction_Times BULK COLLECT INTO
1351 LINE_ID_TBL ,
1352 Line_Created_By_TBL ,
1353 Line_Creation_Date_TBL ,
1354 Line_Last_Updated_By_TBL ,
1355 Line_Last_Update_Date_TBL ,
1356 Line_Last_Update_Login_TBL ,
1357 RUL_ROW_ID_TBL ,
1358 RUL_ID_TBL ,
1359 LINE_RGP_ID_TBL ,
1360 LINE_LSE_ID_TBL ,
1361 LINE_DNZ_CHR_ID_TBL ,
1362 LINE_OBJECT1_ID1_TBL ,
1363 LINE_OBJECT2_ID1_TBL ,
1364 COV_RULE_INFO1_TBL ,
1365 COV_RULE_INFO2_TBL ,
1366 COV_RULE_INFO3_TBL ,
1367 COV_RULE_INFO4_TBL ,
1368 COV_RULE_INFO5_TBL ,
1369 COV_RULE_INFO6_TBL ,
1370 COV_RULE_INFO7_TBL ,
1371 COV_RULE_INFO8_TBL ,
1372 COV_RULE_INFO9_TBL ,
1373 COV_RULE_INFO10_TBL ,
1374 COV_RULE_INFO11_TBL ,
1375 COV_RULE_INFO12_TBL ,
1376 COV_RULE_INFO13_TBL ,
1377 COV_RULE_INFO14_TBL ,
1378 COV_RULE_INFO15_TBL ,
1379 COV_RULE_INFO_TBL ,
1380 COV_OBJ_VER_NUMBER_TBL
1381 LIMIT 1000;
1382
1383 IF LINE_ID_TBL.COUNT > 0 THEN --LINE_ID_TBL.COUNT > 0
1384
1385 FOR I IN LINE_ID_TBL.FIRST .. LINE_ID_TBL.LAST LOOP
1386
1387 L_Cle_id := LINE_ID_TBL(i);
1388
1389 IF (L_OLD_CLE_ID <> L_CLE_ID) THEN
1390 l_cle_ctr := l_cle_ctr + 1;
1391
1392 l_clev_tbl_in(l_cle_ctr).Id := okc_p_util.raw_to_number(sys_guid());
1393
1394 l_clev_tbl_in(l_cle_ctr).Created_By := Line_Created_By_TBL(i);
1395 l_clev_tbl_in(l_cle_ctr).Creation_Date := Line_Creation_Date_TBL(i);
1396 l_clev_tbl_in(l_cle_ctr).Last_Updated_By := Line_Last_Updated_By_TBL(i);
1397 l_clev_tbl_in(l_cle_ctr).Last_Update_Date := Line_Last_Update_Date_TBL(i);
1398 l_clev_tbl_in(l_cle_ctr).Last_Update_Login := Line_Last_Update_Login_TBL(i);
1399
1400 l_clev_tbl_in(l_cle_ctr).Cle_Id := LINE_ID_TBL(i);
1401 l_clev_tbl_in(l_cle_ctr).Dnz_Chr_Id := LINE_DNZ_CHR_ID_TBL(i);
1402
1403 l_clev_tbl_in(l_cle_ctr).object_version_number := 1;
1404 l_clev_tbl_in(l_cle_ctr).Sync_Date_Install := 'N';
1405
1406 IF ((COV_RULE_INFO_TBL(i) = 'RCN') OR (COV_RULE_INFO_TBL(i) = 'RSN')) THEN
1407
1408 l_clev_tbl_in(l_cle_ctr).INCIDENT_SEVERITY_ID := LINE_OBJECT1_ID1_TBL(i);
1409 l_clev_tbl_in(l_cle_ctr).PDF_ID := COV_RULE_INFO1_TBL(i);
1410 l_clev_tbl_in(l_cle_ctr).WORK_THRU_YN := COV_RULE_INFO3_TBL(i);
1411 l_clev_tbl_in(l_cle_ctr).REACT_ACTIVE_YN := COV_RULE_INFO4_TBL(i);
1412 l_clev_tbl_in(l_cle_ctr).REACT_TIME_NAME := COV_RULE_INFO2_TBL(i);
1413
1414 END IF;
1415
1416
1417
1418 END IF; --IF (L_OLD_CLE_ID <> L_CLE_ID) THEN
1419
1420 l_act_ctr := l_act_ctr + 1;
1421
1422 l_actv_tbl_in(l_act_ctr).Id := okc_p_util.raw_to_number(sys_guid());
1423 l_actv_tbl_in(l_act_ctr).Created_By := -9999;
1424 l_actv_tbl_in(l_act_ctr).Last_Updated_By := -9999;
1425 l_actv_tbl_in(l_act_ctr).Last_Update_Date := sysdate;
1426 l_actv_tbl_in(l_act_ctr).Last_Update_Login := -9999;
1427 l_actv_tbl_in(l_act_ctr).Cle_Id := LINE_ID_TBL(i);
1428 l_actv_tbl_in(l_act_ctr).Dnz_Chr_Id := LINE_DNZ_CHR_ID_TBL(i);
1429 l_actv_tbl_in(l_act_ctr).action_type_code := COV_RULE_INFO_TBL(i);
1430 l_actv_tbl_in(l_act_ctr).object_version_number := 1;
1431
1432
1433 L_OLD_CLE_ID := L_CLE_ID;
1434
1435
1436 END LOOP;
1437
1438 END IF; ----LINE_ID_TBL.COUNT > 0
1439
1440
1441 IF l_clev_tbl_in.count > 0 THEN
1442 oks_kln_pvt.insert_row
1443 (
1444 x_return_status => l_return_status,
1445 p_klnv_tbl => l_clev_tbl_in,
1446 p_api_version =>1,
1447 p_init_msg_list => null,
1448 x_msg_count => x_msg_count,
1449 x_msg_data => x_msg_data,
1450 x_klnv_tbl => x_clev_tbl_in
1451 );
1452 END IF;
1453
1454
1455 IF l_actv_tbl_in.count > 0 THEN
1456 OKS_ACT_PVT.insert_row
1457 (
1458 x_return_status => l_return_status,
1459 p_oks_action_time_types_v_tbl => l_actv_tbl_in,
1460 p_api_version =>1,
1461 p_init_msg_list => null,
1462 x_msg_count => x_msg_count,
1463 x_msg_data => x_msg_data,
1464 x_oks_action_time_types_v_tbl => X_actv_tbl_in
1465 );
1466 END IF;
1467
1468 l_actv_tbl_in.DELETE;
1469 l_clev_tbl_in.DELETE;
1470
1471 IF l_return_status = 'S' THEN
1472 x_return_status := 'S';
1473 x_message_data := NULL;
1474 ELSE
1475 RAISE G_EXCEPTION_HALT;
1476 END IF;
1477
1478 EXCEPTION
1479 WHEN G_EXCEPTION_HALT THEN
1480 ROLLBACK;
1481 l_actv_tbl_in.DELETE;
1482 l_clev_tbl_in.DELETE;
1483
1484 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1485 G_APP_NAME,
1486 G_PKG_NAME,
1487 'OKC_API.G_RET_STS_UNEXP_ERROR',
1488 x_msg_count,
1489 x_msg_data,
1490 '_PVT'
1491 );
1492 IF x_msg_count > 0 THEN
1493 FOR i in 1..x_msg_count LOOP
1494 fnd_msg_pub.get (p_msg_index => -1,
1495 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
1496 p_data => l_msg_data,
1497 p_msg_index_out => l_msg_index_out);
1498 l_message := l_message||' ; '||l_msg_data;
1499
1500 END LOOP;
1501 END IF;
1502 x_message_data := l_message;
1503 x_return_status := 'E';
1504 WHEN Others THEN
1505 ROLLBACK;
1506 l_actv_tbl_in.DELETE;
1507 l_clev_tbl_in.DELETE;
1508
1509 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1510 G_APP_NAME,
1511 G_PKG_NAME,
1512 'OKC_API.G_RET_STS_UNEXP_ERROR',
1513 x_msg_count,
1514 x_msg_data,
1515 '_PVT'
1516 );
1517 IF x_msg_count > 0 THEN
1518 FOR i in 1..x_msg_count LOOP
1519 fnd_msg_pub.get (p_msg_index => -1,
1520 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
1521 p_data => l_msg_data,
1522 p_msg_index_out => l_msg_index_out);
1523 l_message := l_message||' ; '||l_msg_data;
1524
1525 END LOOP;
1526 END IF;
1527 x_message_data := l_message;
1528 x_return_status := 'E';
1529 END;
1530
1531 EXIT WHEN Csr_Get_Reaction_Times%NOTFOUND;
1532
1533 END LOOP;
1534 CLOSE Csr_Get_Reaction_Times;
1535
1536 END Reaction_Time_migration;
1537
1538 PROCEDURE Reaction_TimeValues_Migration ( x_return_status OUT NOCOPY VARCHAR2,
1539 x_message_data OUT NOCOPY VARCHAR2) IS
1540
1541
1542 CURSOR Csr_Get_Timevalues IS
1543 SELECT TYP.id Action_Type_ID,
1544 TYP.cle_id Action_Type_Cle_ID ,
1545 TYP.dnz_chr_id Action_Type_Dnz_ID ,
1546 TYP.Created_By Created_By,
1547 TYP.Last_Updated_By Last_Updated_By,
1548 TYP.Last_Update_Date Last_Update_Date,
1549 TYP.Last_Update_Login Last_Update_Login,
1550 RIN.UOM_CODE UOM_CODE,
1551 RIN.DURATION DURATION,
1552 TIM.DAY_OF_WEEK DAY_OF_WEEK,
1553 TIM.TVE_TYPE TVE_TYPE,
1554 Rul.ID RUL_ID
1555 FROM oks_action_time_types_v TYP,
1556 okc_rule_groups_V RGP,okc_rules_v RUL,
1557 okc_timevalues_v TIM,okc_react_intervals RIN
1558 WHERE RGP.CLE_ID = TYP.CLE_ID
1559 AND RGP.DNZ_CHR_ID = TYP.DNZ_CHR_ID
1560 AND RGP.ID = RUL.RGP_ID
1561 -- AND typ.id = 308454467546072904212144662892351929683
1562 AND RGP.DNZ_CHR_ID = RUL.DNZ_CHR_ID
1563 AND RUL.DNZ_CHR_ID = TYP.DNZ_CHR_ID
1564 AND RUL.ID = RIN.RUL_ID
1565 AND rul.RULE_INFORMATION_CATEGORY = typ.ACTION_TYPE_CODE
1566 AND RIN.DNZ_CHR_ID = TYP.DNZ_CHR_ID
1567 AND TIM.ID = RIN.TVE_ID
1568 AND TIM.DNZ_CHR_ID = RIN.DNZ_CHR_ID
1569 AND TIM.DNZ_CHR_ID = RGP.DNZ_CHR_ID
1570 AND TIM.DNZ_CHR_ID = RUL.DNZ_CHR_ID
1571 AND TIM.DNZ_CHR_ID = TYP.DNZ_CHR_ID
1572 AND NOT EXISTS (Select cov_action_type_id FROM OKS_ACTION_TIMES WHERE COV_ACTION_TYPE_ID =TYP.id)
1573 ORDER BY RUL.ID;
1574
1575
1576 TYPE RowId_Tbl_Type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
1577 TYPE Num_Tbl_Type IS VARRAY(1000) OF NUMBER;
1578
1579 TYPE Vc15_Tbl_Type IS VARRAY(1000) OF VARCHAR2(15);
1580 TYPE Vc20_Tbl_Type IS VARRAY(1000) OF VARCHAR2(20);
1581 TYPE Date_Tbl_Type IS VARRAY(1000) OF DATE;
1582
1583 l_acm_ctr NUMBER :=0;
1584
1585 l_return_status VArchar2(2):= 'S';
1586
1587 x_msg_count NUMBER := 0;
1588 x_msg_data VArchar2(1000);
1589
1590 l_msg_data VARCHAR2(1000);
1591 l_msg_index_out NUMBER;
1592 l_message VARCHAR2(2400);
1593 --l_start_rowid ROWID := p_start_rowid;
1594 --l_end_rowid ROWID := p_end_rowid;
1595
1596 G_EXCEPTION_HALT EXCEPTION;
1597
1598 Action_Type_ID_TBL Num_Tbl_Type;
1599 Action_Type_Cle_ID_TBL Num_Tbl_Type;
1600 Action_Type_Dnz_ID_TBL Num_Tbl_Type;
1601 Created_By_TBL Num_Tbl_Type;
1602 Last_Updated_By_TBL Num_Tbl_Type;
1603 Last_Update_Date_TBL Date_Tbl_Type;
1604 Last_Update_Login_TBL Num_Tbl_Type;
1605 UOM_CODE_TBL Vc20_Tbl_Type;
1606 DURATION_TBL Num_Tbl_Type;
1607 DAY_OF_WEEK_TBL Vc20_Tbl_Type;
1608 TVE_TYPE_TBL Vc20_Tbl_Type;
1609 RUL_ID_TBL Num_Tbl_Type;
1610 l_rul_id NUMBER := -9999;
1611 BEGIN
1612
1613 G_APP_NAME := 'Reaction_TimeValues_Migration';
1614
1615 OPEN Csr_Get_Timevalues ;
1616 LOOP
1617 BEGIN
1618 FETCH Csr_Get_Timevalues BULK COLLECT INTO
1619 Action_Type_ID_TBL,
1620 Action_Type_Cle_ID_TBL,
1621 Action_Type_Dnz_ID_TBL,
1622 Created_By_TBL,
1623 Last_Updated_By_TBL,
1624 Last_Update_Date_TBL,
1625 Last_Update_Login_TBL,
1626 UOM_CODE_TBL,
1627 DURATION_TBL,
1628 DAY_OF_WEEK_TBL,
1629 TVE_TYPE_TBL,
1630 RUL_ID_TBL
1631 LIMIT 1000;
1632
1633 -- -- dbms_output.put_line('Value of Action_Type_ID_TBL.COUNT='||TO_CHAR(Action_Type_ID_TBL.COUNT));
1634
1635 IF Action_Type_ID_TBL.COUNT > 0 THEN
1636 FOR I IN Action_Type_ID_TBL.FIRST .. Action_Type_ID_TBL.LAST LOOP
1637
1638 IF l_rul_id <> RUL_ID_TBL(I) THEN
1639
1640 l_rul_id := RUL_ID_TBL(I);
1641 l_acm_ctr := l_acm_ctr + 1;
1642
1643 l_acmv_tbl_in(l_acm_ctr).Created_By := Created_By_TBL(i);
1644 l_acmv_tbl_in(l_acm_ctr).Last_Updated_By := Last_Updated_By_TBL(i);
1645 l_acmv_tbl_in(l_acm_ctr).Last_Update_Date := Last_Update_Date_TBL(i);
1646 l_acmv_tbl_in(l_acm_ctr).Last_Update_Login := Last_Update_Login_TBL(i);
1647 l_acmv_tbl_in(l_acm_ctr).SECURITY_GROUP_ID := NULL;
1648 l_acmv_tbl_in(l_acm_ctr).PROGRAM_APPLICATION_ID := NULL;
1649 l_acmv_tbl_in(l_acm_ctr).PROGRAM_ID := NULL;
1650 l_acmv_tbl_in(l_acm_ctr).PROGRAM_UPDATE_DATE := NULL;
1651 l_acmv_tbl_in(l_acm_ctr).REQUEST_ID := NULL;
1652
1653 l_acmv_tbl_in(l_acm_ctr).ID := okc_p_util.raw_to_number(sys_guid());
1654 l_acmv_tbl_in(l_acm_ctr).COV_ACTION_TYPE_ID := Action_Type_ID_TBL(i);
1655 l_acmv_tbl_in(l_acm_ctr).CLE_ID := Action_Type_Cle_ID_TBL(i);
1656 l_acmv_tbl_in(l_acm_ctr).Dnz_chr_id := Action_Type_Dnz_ID_TBL(i);
1657 l_acmv_tbl_in(l_acm_ctr).UOM_CODE := UOM_CODE_TBL(i);
1658 l_acmv_tbl_in(l_acm_ctr).object_version_number := 1;
1659
1660 l_acmv_tbl_in(l_acm_ctr).SUN_DURATION := NULL;
1661 l_acmv_tbl_in(l_acm_ctr).MON_DURATION := NULL;
1662 l_acmv_tbl_in(l_acm_ctr).TUE_DURATION := NULL;
1663 l_acmv_tbl_in(l_acm_ctr).WED_DURATION := NULL;
1664 l_acmv_tbl_in(l_acm_ctr).THU_DURATION := NULL;
1665 l_acmv_tbl_in(l_acm_ctr).FRI_DURATION := NULL;
1666 l_acmv_tbl_in(l_acm_ctr).SAT_DURATION := NULL;
1667 END IF;
1668 IF DAY_OF_WEEK_TBL(i) = 'SUN' THEN
1669 l_acmv_tbl_in(l_acm_ctr).SUN_DURATION := DURATION_TBL(i);
1670
1671 ELSIF DAY_OF_WEEK_TBL(i) = 'MON' THEN
1672 l_acmv_tbl_in(l_acm_ctr).MON_DURATION := DURATION_TBL(i);
1673
1674 ELSIF DAY_OF_WEEK_TBL(i) = 'TUE' THEN
1675 l_acmv_tbl_in(l_acm_ctr).TUE_DURATION := DURATION_TBL(i);
1676
1677 ELSIF DAY_OF_WEEK_TBL(i) = 'WED' THEN
1678 l_acmv_tbl_in(l_acm_ctr).WED_DURATION := DURATION_TBL(i);
1679
1680 ELSIF DAY_OF_WEEK_TBL(i) = 'THU' THEN
1681 l_acmv_tbl_in(l_acm_ctr).THU_DURATION := DURATION_TBL(i);
1682
1683 ELSIF DAY_OF_WEEK_TBL(i) = 'FRI' THEN
1684 l_acmv_tbl_in(l_acm_ctr).FRI_DURATION := DURATION_TBL(i);
1685
1686 ELSIF DAY_OF_WEEK_TBL(i) = 'SAT' THEN
1687 l_acmv_tbl_in(l_acm_ctr).SAT_DURATION := DURATION_TBL(i);
1688 END IF;
1689
1690 END LOOP;
1691 END IF;
1692
1693 -- -- dbms_output.put_line('Value of l_acmv_tbl_in.COUNT='||TO_CHAR(l_acmv_tbl_in.COUNT));
1694
1695 IF l_acmv_tbl_in.COUNT > 0 THEN
1696
1697 OKS_Insert_Row_Upg.INSERT_ROW_UPG_ACMV_TBL
1698 (x_return_status => l_return_status,
1699 P_ACMV_TBL => l_acmv_tbl_in);
1700 -- -- dbms_output.put_line('Value of l_return_Status='||l_return_Status);
1701 IF l_return_Status = 'S' THEN
1702 X_return_Status := 'S';
1703 X_Message_Data := NULL;
1704 COMMIT;
1705 ELSE
1706 RAISE G_EXCEPTION_HALT;
1707 END IF;
1708
1709 END IF;
1710
1711 EXIT WHEN Csr_Get_Timevalues%NOTFOUND;
1712 CLOSE Csr_Get_Timevalues;
1713
1714 EXCEPTION
1715 WHEN G_EXCEPTION_HALT THEN
1716 ROLLBACK;
1717
1718 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1719 G_APP_NAME,
1720 G_PKG_NAME,
1721 'OKC_API.G_RET_STS_UNEXP_ERROR',
1722 x_msg_count,
1723 x_msg_data,
1724 '_PVT'
1725 );
1726
1727 IF x_msg_count > 0 THEN
1728 FOR i in 1..x_msg_count LOOP
1729 fnd_msg_pub.get (p_msg_index => -1,
1730 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
1731 p_data => l_msg_data,
1732 p_msg_index_out => l_msg_index_out);
1733 l_message := l_message||' ; '||l_msg_data;
1734
1735 END LOOP;
1736 END IF;
1737 x_message_data := l_message;
1738 x_return_status := 'E';
1739
1740
1741 WHEN Others THEN
1742 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1743 G_APP_NAME,
1744 G_PKG_NAME,
1745 'OKC_API.G_RET_STS_UNEXP_ERROR',
1746 x_msg_count,
1747 x_msg_data,
1748 '_PVT'
1749 );
1750 IF x_msg_count > 0 THEN
1751 FOR i in 1..x_msg_count LOOP
1752 fnd_msg_pub.get (p_msg_index => -1,
1753 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
1754 p_data => l_msg_data,
1755 p_msg_index_out => l_msg_index_out);
1756 l_message := l_message||' ; '||l_msg_data;
1757
1758 END LOOP;
1759 END IF;
1760 x_message_data := l_message;
1761 x_return_status := 'E';
1762
1763 END;
1764
1765 EXIT WHEN Csr_Get_Timevalues%NOTFOUND;
1766 CLOSE Csr_Get_Timevalues;
1767 l_acmv_tbl_in.DELETE;
1768
1769 END LOOP;
1770
1771 IF Csr_Get_Timevalues%ISOPEN THEN
1772 CLOSE Csr_Get_Timevalues;
1773 END IF;
1774
1775
1776 --CLOSE Csr_Get_Timevalues;
1777 EXCEPTION
1778 WHEN OTHERS THEN
1779 ROLLBACK;
1780 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
1781 G_APP_NAME,
1782 G_PKG_NAME,
1783 'OKC_API.G_RET_STS_UNEXP_ERROR',
1784 x_msg_count,
1785 x_msg_data,
1786 '_PVT'
1787 );
1788 IF x_msg_count > 0 THEN
1789 FOR i in 1..x_msg_count LOOP
1790 fnd_msg_pub.get (p_msg_index => -1,
1791 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
1792 p_data => l_msg_data,
1793 p_msg_index_out => l_msg_index_out);
1794 l_message := l_message||' ; '||l_msg_data;
1795
1796 END LOOP;
1797 END IF;
1798 x_message_data := l_message;
1799 x_return_status := 'E';
1800
1801 END Reaction_TimeValues_Migration;
1802
1803
1804
1805 --PROCEDURE BILL_TYPES_MIGRATION( x_return_status OUT NOCOPY VARCHAR2) IS
1806 PROCEDURE BILL_TYPES_MIGRATION( p_start_rowid IN ROWID,
1807 p_end_rowid IN ROWID,
1808 x_return_status OUT NOCOPY VARCHAR2,
1809 x_message_data OUT NOCOPY VARCHAR2) IS
1810 CURSOR Csr_Get_Bill_Types (l_start_rowid IN ROWID ,l_end_rowid IN ROWID ) IS
1811 SELECT
1812 LINE.ID LINE_ID,
1813 Line.Created_By Line_Created_By,
1814 Line.Creation_Date Line_Creation_Date,
1815 Line.Last_Updated_By Line_Last_Updated_By,
1816 Line.Last_Update_Date Line_Last_Update_Date,
1817 Line.Last_Update_Login Line_Last_Update_Login,
1818 Rul.ROWID RUL_ROW_ID,
1819 RGP.ID LINE_RGP_ID,
1820 LINE.LSE_ID LINE_LSE_ID,
1821 LINE.DNZ_CHR_ID LINE_DNZ_CHR_ID,
1822 OBJECT1_ID1 LINE_OBJECT1_ID1,
1823 OBJECT2_ID1 LINE_OBJECT2_ID1,
1824 RULE_INFORMATION1 COV_RULE_INFO1,
1825 RULE_INFORMATION2 COV_RULE_INFO2,
1826 RULE_INFORMATION3 COV_RULE_INFO3,
1827 RULE_INFORMATION4 COV_RULE_INFO4,
1828 RULE_INFORMATION5 COV_RULE_INFO5,
1829 RULE_INFORMATION6 COV_RULE_INFO6,
1830 RULE_INFORMATION7 COV_RULE_INFO7,
1831 RULE_INFORMATION8 COV_RULE_INFO8,
1832 RULE_INFORMATION9 COV_RULE_INFO9,
1833 RULE_INFORMATION10 COV_RULE_INFO10,
1834 RULE_INFORMATION11 COV_RULE_INFO11,
1835 RULE_INFORMATION12 COV_RULE_INFO12,
1836 RULE_INFORMATION13 COV_RULE_INFO13,
1837 RULE_INFORMATION14 COV_RULE_INFO14,
1838 RULE_INFORMATION15 COV_RULE_INFO15,
1839 RULE_INFORMATION_CATEGORY COV_RULE_INFO,
1840 RUL.OBJECT_VERSION_NUMBER COV_OBJ_VER_NUMBER
1841 FROM
1842 OKC_RULE_GROUPS_B RGP,
1843 OKC_RULES_B RUL,
1844 OKC_K_LINES_B LINE
1845 WHERE LINE.ID = RGP.CLE_ID
1846 AND RGP.ID = RUL.RGP_ID
1847 AND LINE.LSE_ID IN (5,59,23)
1848 --AND RUL.RULE_INFORMATION_CATEGORY IN ('LMT')
1849 -- AND RUL.RULE_INFORMATION15 IS NULL
1850 AND Rul.rowid BETWEEN l_start_rowid and l_end_rowid
1851 AND NOT EXISTS (SELECT CLE_ID FROM OKS_K_LINES_B WHERE CLE_ID = LINE.ID)
1852 ORDER BY LINE.ID;
1853
1854
1855
1856 TYPE RowId_Tbl_Type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
1857 TYPE Num_Tbl_Type IS VARRAY(1000) OF NUMBER;
1858 TYPE Date_Tbl_Type IS VARRAY(1000) OF DATE;
1859 TYPE Vc15_Tbl_Type IS VARRAY(1000) OF VARCHAR2(15);
1860 TYPE Vc20_Tbl_Type IS VARRAY(1000) OF VARCHAR2(20);
1861 TYPE Vc150_Tbl_Type IS VARRAY(1000) OF VARCHAR2(150);
1862 TYPE Vc30_Tbl_Type IS VARRAY(1000) OF VARCHAR2(30);
1863 TYPE Vc1_Tbl_Type IS VARRAY(1000) OF VARCHAR2(1);
1864 TYPE Vc3_Tbl_Type IS VARRAY(1000) OF VARCHAR2(3);
1865 TYPE Vc2000_Tbl_Type IS VARRAY(1000) OF VARCHAR2(2000);
1866 TYPE Vc50_Tbl_Type IS VARRAY(1000) OF VARCHAR2(50);
1867 TYPE Vc80_Tbl_Type IS VARRAY(1000) OF VARCHAR2(80);
1868 TYPE Vc120_Tbl_Type IS VARRAY(1000) OF VARCHAR2(120);
1869 TYPE Vc600_Tbl_Type IS VARRAY(1000) OF VARCHAR2(600);
1870
1871
1872 LINE_ID_TBL Num_Tbl_Type;
1873 Line_Created_By_TBL Num_Tbl_Type;
1874 Line_Creation_Date_TBL Date_Tbl_Type;
1875 Line_Last_Updated_By_TBL Num_Tbl_Type;
1876 Line_Last_Update_Date_TBL Date_Tbl_Type;
1877 Line_Last_Update_Login_TBL Num_Tbl_Type;
1878 RUL_ROW_ID_TBL RowId_Tbl_Type;
1879 LINE_RGP_ID_TBL Num_Tbl_Type;
1880 LINE_LSE_ID_TBL Num_Tbl_Type;
1881 LINE_DNZ_CHR_ID_TBL Num_Tbl_Type;
1882 LINE_OBJECT1_ID1_TBL Vc150_Tbl_Type;
1883 LINE_OBJECT2_ID1_TBL Vc150_Tbl_Type;
1884 COV_RULE_INFO1_TBL Vc150_Tbl_Type;
1885 COV_RULE_INFO2_TBL Vc150_Tbl_Type;
1886 COV_RULE_INFO3_TBL Vc150_Tbl_Type;
1887 COV_RULE_INFO4_TBL Vc150_Tbl_Type;
1888 COV_RULE_INFO5_TBL Vc150_Tbl_Type;
1889 COV_RULE_INFO6_TBL Vc150_Tbl_Type;
1890 COV_RULE_INFO7_TBL Vc150_Tbl_Type;
1891 COV_RULE_INFO8_TBL Vc150_Tbl_Type;
1892 COV_RULE_INFO9_TBL Vc150_Tbl_Type;
1893 COV_RULE_INFO10_TBL Vc150_Tbl_Type;
1894 COV_RULE_INFO11_TBL Vc150_Tbl_Type;
1895 COV_RULE_INFO12_TBL Vc150_Tbl_Type;
1896 COV_RULE_INFO13_TBL Vc150_Tbl_Type;
1897 COV_RULE_INFO14_TBL Vc150_Tbl_Type;
1898 COV_RULE_INFO15_TBL Vc150_Tbl_Type;
1899 COV_RULE_INFO_TBL Vc150_Tbl_Type;
1900 COV_OBJ_VER_NUMBER_TBL Num_Tbl_Type;
1901
1902 x_clev_tbl_in oks_kln_pvt.klnv_tbl_type;
1903 l_clev_tbl_in oks_kln_pvt.klnv_tbl_type;
1904 l_cle_ctr NUMBER := 0;
1905 tablename1 VArchar2(1000);
1906 x_msg_count NUMBER := 0;
1907 x_msg_data VArchar2(1000);
1908 L_OLD_CLE_ID NUMBER := -99999;
1909 L_CLE_ID NUMBER := -99999;
1910 l_duration NUMBER;
1911 l_period VArchar2(10);
1912 l_return_status VArchar2(1) := OKC_API.G_RET_STS_SUCCESS;
1913 G_EXCEPTION_HALT EXCEPTION;
1914
1915 l_start_rowid ROWID := p_start_rowid;
1916 l_end_rowid ROWID := p_end_rowid;
1917
1918 l_msg_data VARCHAR2(1000);
1919 l_msg_index_out NUMBER;
1920 l_message VARCHAR2(2400);
1921
1922 BEGIN
1923 G_APP_NAME := 'BILL_TYPES_MIGRATION';
1924 OPEN Csr_Get_Bill_Types (l_start_rowid,l_end_rowid);
1925 LOOP
1926 BEGIN
1927 FETCH Csr_Get_Bill_Types BULK COLLECT INTO
1928 LINE_ID_TBL ,
1929 Line_Created_By_TBL ,
1930 Line_Creation_Date_TBL ,
1931 Line_Last_Updated_By_TBL ,
1932 Line_Last_Update_Date_TBL ,
1933 Line_Last_Update_Login_TBL ,
1934 RUL_ROW_ID_TBL ,
1935 LINE_RGP_ID_TBL ,
1936 LINE_LSE_ID_TBL ,
1937 LINE_DNZ_CHR_ID_TBL ,
1938 LINE_OBJECT1_ID1_TBL ,
1939 LINE_OBJECT2_ID1_TBL ,
1940 COV_RULE_INFO1_TBL ,
1941 COV_RULE_INFO2_TBL ,
1942 COV_RULE_INFO3_TBL ,
1943 COV_RULE_INFO4_TBL ,
1944 COV_RULE_INFO5_TBL ,
1945 COV_RULE_INFO6_TBL ,
1946 COV_RULE_INFO7_TBL ,
1947 COV_RULE_INFO8_TBL ,
1948 COV_RULE_INFO9_TBL ,
1949 COV_RULE_INFO10_TBL ,
1950 COV_RULE_INFO11_TBL ,
1951 COV_RULE_INFO12_TBL ,
1952 COV_RULE_INFO13_TBL ,
1953 COV_RULE_INFO14_TBL ,
1954 COV_RULE_INFO15_TBL ,
1955 COV_RULE_INFO_TBL ,
1956 COV_OBJ_VER_NUMBER_TBL
1957 LIMIT 1000;
1958
1959
1960
1961 IF LINE_ID_TBL.COUNT > 0 THEN
1962
1963 FOR I IN LINE_ID_TBL.FIRST .. LINE_ID_TBL.LAST LOOP
1964 L_Cle_id := LINE_ID_TBL(i);
1965 IF (L_OLD_CLE_ID <> L_CLE_ID) THEN
1966 l_cle_ctr := l_cle_ctr + 1;
1967 END IF;
1968
1969 l_clev_tbl_in(l_cle_ctr).Id := okc_p_util.raw_to_number(sys_guid());
1970 l_clev_tbl_in(l_cle_ctr).Created_By := Line_Created_By_tbl(i);
1971 l_clev_tbl_in(l_cle_ctr).Creation_Date := Line_Creation_Date_TBL(i);
1972 l_clev_tbl_in(l_cle_ctr).Last_Updated_By :=
1973 Line_Last_Updated_By_TBL(i);
1974 l_clev_tbl_in(l_cle_ctr).Last_Update_Date :=
1975 Line_Last_Update_Date_TBL(i);
1976 l_clev_tbl_in(l_cle_ctr).Last_Update_Login :=
1977 Line_Last_Update_Login_TBL(i);
1978 l_clev_tbl_in(l_cle_ctr).Cle_Id := LINE_ID_TBL(i);
1979 l_clev_tbl_in(l_cle_ctr).Dnz_Chr_Id := LINE_DNZ_CHR_ID_TBL(i);
1980 l_clev_tbl_in(l_cle_ctr).object_version_number := 1;
1981 l_clev_tbl_in(l_cle_ctr).Sync_Date_Install := 'N';
1982
1983 IF COV_RULE_INFO_TBL(i) = 'LMT' THEN
1984 l_clev_tbl_in(l_cle_ctr).LIMIT_UOM_QUANTIFIED := COV_RULE_INFO1_TBL(i);
1985 l_clev_tbl_in(l_cle_ctr).DISCOUNT_AMOUNT := COV_RULE_INFO2_TBL(i);
1986 l_clev_tbl_in(l_cle_ctr).DISCOUNT_PERCENT := COV_RULE_INFO4_TBL(i);
1987 END IF;
1988
1989
1990 L_OLD_CLE_ID := L_CLE_ID;
1991 END LOOP;
1992 END IF;
1993
1994
1995 tablename1 := 'OKS_K_LINES';
1996
1997 IF l_clev_tbl_in.count > 0 THEN
1998 oks_kln_pvt.insert_row
1999 (
2000 x_return_status => l_return_status,
2001 p_klnv_tbl => l_clev_tbl_in,
2002 p_api_version =>1,
2003 p_init_msg_list => null,
2004 x_msg_count => x_msg_count,
2005 x_msg_data => x_msg_data,
2006 x_klnv_tbl => x_clev_tbl_in
2007 );
2008
2009 END IF;
2010
2011
2012 IF l_return_status = 'S' THEN
2013 x_return_status := 'S';
2014 x_message_data := NULL;
2015 ELSE
2016 RAISE G_EXCEPTION_HALT;
2017 END IF;
2018
2019 EXCEPTION
2020 WHEN G_EXCEPTION_HALT THEN
2021 ROLLBACK;
2022
2023 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2024 G_APP_NAME,
2025 G_PKG_NAME,
2026 'OKC_API.G_RET_STS_UNEXP_ERROR',
2027 x_msg_count,
2028 x_msg_data,
2029 '_PVT'
2030 );
2031 l_clev_tbl_in.DELETE;
2032
2033 IF x_msg_count > 0 THEN
2034 FOR i in 1..x_msg_count LOOP
2035 fnd_msg_pub.get (p_msg_index => -1,
2036 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
2037 p_data => l_msg_data,
2038 p_msg_index_out => l_msg_index_out);
2039 l_message := l_message||' ; '||l_msg_data;
2040
2041 END LOOP;
2042 END IF;
2043 x_message_data := l_message;
2044 x_return_status := 'E';
2045 WHEN Others THEN
2046 ROLLBACK;
2047 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2048 G_APP_NAME,
2049 G_PKG_NAME,
2050 'OKC_API.G_RET_STS_UNEXP_ERROR',
2051 x_msg_count,
2052 x_msg_data,
2053 '_PVT'
2054 );
2055 l_clev_tbl_in.DELETE;
2056 IF x_msg_count > 0 THEN
2057 FOR i in 1..x_msg_count LOOP
2058 fnd_msg_pub.get (p_msg_index => -1,
2059 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
2060 p_data => l_msg_data,
2061 p_msg_index_out => l_msg_index_out);
2062 l_message := l_message||' ; '||l_msg_data;
2063
2064 END LOOP;
2065 END IF;
2066 x_message_data := l_message;
2067 x_return_status := 'E';
2068 EXIT WHEN Csr_Get_Bill_Types%NOTFOUND;
2069 END;
2070
2071 EXIT WHEN Csr_Get_Bill_Types%NOTFOUND;
2072
2073 END LOOP;
2074
2075 CLOSE Csr_Get_Bill_Types;
2076
2077 EXCEPTION
2078 WHEN Others THEN
2079 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2080 G_APP_NAME,
2081 G_PKG_NAME,
2082 'OKC_API.G_RET_STS_UNEXP_ERROR',
2083 x_msg_count,
2084 x_msg_data,
2085 '_PVT'
2086 );
2087 IF x_msg_count > 0 THEN
2088 FOR i in 1..x_msg_count LOOP
2089 fnd_msg_pub.get (p_msg_index => -1,
2090 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
2091 p_data => l_msg_data,
2092 p_msg_index_out => l_msg_index_out);
2093 l_message := l_message||' ; '||l_msg_data;
2094
2095 END LOOP;
2096 END IF;
2097 x_message_data := l_message;
2098 x_return_status := 'E';
2099
2100 END BILL_TYPES_MIGRATION;
2101
2102
2103
2104 /**********************************HISTORY***********************************************/
2105
2106 PROCEDURE Insert_Into_Klines( p_clev_tbl_in IN klnv_tbl_type,
2107 p_clet_tbl_in IN klt_tbl_type,
2108 x_return_Status OUT NOCOPY VARCHAR2) IS
2109 i_clev_tbl_in klnv_tbl_type := p_clev_tbl_in;
2110 l_clet_tbl_in klt_tbl_type := p_clet_tbl_in;
2111
2112 l_return_Status VARCHAR2(1) := 'S';
2113 x_msg_count NUMBER := 0;
2114 x_msg_data VArchar2(1000);
2115 l_msg_data VARCHAR2(1000);
2116 l_msg_index_out NUMBER;
2117 l_message VARCHAR2(2400);
2118
2119 BEGIN
2120 G_APP_NAME := 'Insert_Into_Klines';
2121
2122 IF i_clev_tbl_in.COUNT > 0 THEN
2123
2124 i := i_clev_tbl_in.FIRST; j:=0;
2125 WHILE i is not null LOOP
2126 j:=j+1;
2127
2128 In_ID(J):= i_clev_tbl_in(I).Id;
2129 In_MAJOR_VERSION (J):= i_clev_tbl_in(I).object_version_number;
2130 In_CLE_ID (J):= i_clev_tbl_in(I).CLE_ID ;
2131 In_DNZ_CHR_ID (J):= i_clev_tbl_in(I).DNZ_CHR_ID ;
2132 In_DISCOUNT_LIST(J):= i_clev_tbl_in(I).DISCOUNT_LIST ;
2133 In_ACCT_RULE_ID (J):= i_clev_tbl_in(I).ACCT_RULE_ID ;
2134 In_PAYMENT_TYPE (J):= i_clev_tbl_in(I).PAYMENT_TYPE ;
2135 In_CC_NO (J):= i_clev_tbl_in(I).CC_NO ;
2136 In_CC_EXPIRY_DATE (J):= i_clev_tbl_in(I).CC_EXPIRY_DATE;
2137 In_CC_BANK_ACCT_ID (J):= i_clev_tbl_in(I).CC_BANK_ACCT_ID ;
2138 In_CC_AUTH_CODE (J):= i_clev_tbl_in(I).CC_AUTH_CODE ;
2139 In_LOCKED_PRICE_LIST_ID (J):= i_clev_tbl_in(I).LOCKED_PRICE_LIST_ID ;
2140 In_USAGE_EST_YN (J):= i_clev_tbl_in(I).USAGE_EST_YN ;
2141 In_USAGE_EST_METHOD (J):= i_clev_tbl_in(I).USAGE_EST_METHOD ;
2142 In_USAGE_EST_START_DATE (J):= i_clev_tbl_in(I).USAGE_EST_START_DATE ;
2143 In_TERMN_METHOD (J):= i_clev_tbl_in(I).TERMN_METHOD ;
2144 In_UBT_AMOUNT (J):= i_clev_tbl_in(I).UBT_AMOUNT ;
2145 In_CREDIT_AMOUNT(J):= i_clev_tbl_in(I).CREDIT_AMOUNT ;
2146 In_SUPPRESSED_CREDIT (J):= i_clev_tbl_in(I).SUPPRESSED_CREDIT ;
2147 In_OVERRIDE_AMOUNT (J):= i_clev_tbl_in(I).OVERRIDE_AMOUNT ;
2148 In_CUST_PO_NUMBER_REQ_YN(J):= i_clev_tbl_in(I).CUST_PO_NUMBER_REQ_YN ;
2149 In_CUST_PO_NUMBER (J):= i_clev_tbl_in(I).CUST_PO_NUMBER;
2150 In_GRACE_DURATION (J):= i_clev_tbl_in(I).GRACE_DURATION;
2151 In_GRACE_PERIOD (J):= i_clev_tbl_in(I).GRACE_PERIOD ;
2152 In_INV_PRINT_FLAG (J):= i_clev_tbl_in(I).INV_PRINT_FLAG;
2153 In_PRICE_UOM (J):= i_clev_tbl_in(I).PRICE_UOM ;
2154 In_TAX_AMOUNT (J):= i_clev_tbl_in(I).TAX_AMOUNT ;
2155 In_TAX_INCLUSIVE_YN (J):= i_clev_tbl_in(I).TAX_INCLUSIVE_YN ;
2156 In_TAX_STATUS (J):= i_clev_tbl_in(I).TAX_STATUS ;
2157 In_TAX_CODE (J):= i_clev_tbl_in(I).TAX_CODE ;
2158 In_TAX_EXEMPTION_ID (J):= i_clev_tbl_in(I).TAX_EXEMPTION_ID ;
2159 In_IB_TRANS_TYPE(J):= i_clev_tbl_in(I).IB_TRANS_TYPE ;
2160 In_IB_TRANS_DATE(J):= i_clev_tbl_in(I).IB_TRANS_DATE ;
2161 In_PROD_PRICE (J):= i_clev_tbl_in(I).PROD_PRICE ;
2162 In_SERVICE_PRICE(J):= i_clev_tbl_in(I).SERVICE_PRICE ;
2163 In_CLVL_LIST_PRICE (J):= i_clev_tbl_in(I).CLVL_LIST_PRICE ;
2164 In_CLVL_QUANTITY(J):= i_clev_tbl_in(I).CLVL_QUANTITY ;
2165 In_CLVL_EXTENDED_AMT (J):= i_clev_tbl_in(I).CLVL_EXTENDED_AMT ;
2166 In_CLVL_UOM_CODE(J):= i_clev_tbl_in(I).CLVL_UOM_CODE ;
2167 In_TOPLVL_OPERAND_CODE (J):= i_clev_tbl_in(I).TOPLVL_OPERAND_CODE ;
2168 In_TOPLVL_OPERAND_VAL (J):= i_clev_tbl_in(I).TOPLVL_OPERAND_VAL ;
2169 In_TOPLVL_QUANTITY (J):= i_clev_tbl_in(I).TOPLVL_QUANTITY ;
2170 In_TOPLVL_UOM_CODE (J):= i_clev_tbl_in(I).TOPLVL_UOM_CODE ;
2171 In_TOPLVL_ADJ_PRICE (J):= i_clev_tbl_in(I).TOPLVL_ADJ_PRICE ;
2172 In_TOPLVL_PRICE_QTY (J):= i_clev_tbl_in(I).TOPLVL_PRICE_QTY ;
2173 In_AVERAGING_INTERVAL (J):= i_clev_tbl_in(I).AVERAGING_INTERVAL ;
2174 In_SETTLEMENT_INTERVAL (J):= i_clev_tbl_in(I).SETTLEMENT_INTERVAL ;
2175 In_MINIMUM_QUANTITY (J):= i_clev_tbl_in(I).MINIMUM_QUANTITY ;
2176 In_DEFAULT_QUANTITY (J):= i_clev_tbl_in(I).DEFAULT_QUANTITY ;
2177 In_AMCV_FLAG (J):= i_clev_tbl_in(I).AMCV_FLAG ;
2178 In_FIXED_QUANTITY (J):= i_clev_tbl_in(I).FIXED_QUANTITY;
2179 In_USAGE_DURATION (J):= i_clev_tbl_in(I).USAGE_DURATION;
2180 In_USAGE_PERIOD (J):= i_clev_tbl_in(I).USAGE_PERIOD ;
2181 In_LEVEL_YN (J):= i_clev_tbl_in(I).LEVEL_YN ;
2182 In_USAGE_TYPE (J):= i_clev_tbl_in(I).USAGE_TYPE ;
2183 In_UOM_QUANTIFIED (J):= i_clev_tbl_in(I).UOM_QUANTIFIED;
2184 In_BASE_READING (J):= i_clev_tbl_in(I).BASE_READING ;
2185 In_BILLING_SCHEDULE_TYPE(J):= i_clev_tbl_in(I).BILLING_SCHEDULE_TYPE ;
2186 In_COVERAGE_TYPE(J):= i_clev_tbl_in(I).COVERAGE_TYPE ;
2187 In_EXCEPTION_COV_ID (J):= i_clev_tbl_in(I).EXCEPTION_COV_ID ;
2188 In_LIMIT_UOM_QUANTIFIED (J):= i_clev_tbl_in(I).LIMIT_UOM_QUANTIFIED ;
2189 In_DISCOUNT_AMOUNT (J):= i_clev_tbl_in(I).DISCOUNT_AMOUNT ;
2190 In_DISCOUNT_PERCENT (J):= i_clev_tbl_in(I).DISCOUNT_PERCENT ;
2191 In_OFFSET_DURATION (J):= i_clev_tbl_in(I).OFFSET_DURATION ;
2192 In_OFFSET_PERIOD(J):= i_clev_tbl_in(I).OFFSET_PERIOD ;
2193 In_INCIDENT_SEVERITY_ID (J):= i_clev_tbl_in(I).INCIDENT_SEVERITY_ID ;
2194 In_PDF_ID (J):= i_clev_tbl_in(I).PDF_ID ;
2195 In_WORK_THRU_YN (J):= i_clev_tbl_in(I).WORK_THRU_YN ;
2196 In_REACT_ACTIVE_YN (J):= i_clev_tbl_in(I).REACT_ACTIVE_YN ;
2197 In_TRANSFER_OPTION (J):= i_clev_tbl_in(I).TRANSFER_OPTION ;
2198 In_PROD_UPGRADE_YN (J):= i_clev_tbl_in(I).PROD_UPGRADE_YN ;
2199 In_INHERITANCE_TYPE (J):= i_clev_tbl_in(I).INHERITANCE_TYPE ;
2200 In_PM_PROGRAM_ID(J):= i_clev_tbl_in(I).PM_PROGRAM_ID ;
2201 In_PM_CONF_REQ_YN (J):= i_clev_tbl_in(I).PM_CONF_REQ_YN;
2202 In_PM_SCH_EXISTS_YN (J):= i_clev_tbl_in(I).PM_SCH_EXISTS_YN ;
2203 In_ALLOW_BT_DISCOUNT (J):= i_clev_tbl_in(I).ALLOW_BT_DISCOUNT ;
2204 In_APPLY_DEFAULT_TIMEZONE (J):= i_clev_tbl_in(I).APPLY_DEFAULT_TIMEZONE ;
2205 In_SYNC_DATE_INSTALL (J):= i_clev_tbl_in(I).SYNC_DATE_INSTALL ;
2206 In_OBJECT_VERSION_NUMBER (J):= i_clev_tbl_in(I).OBJECT_VERSION_NUMBER ;
2207 In_SECURITY_GROUP_ID (J):= i_clev_tbl_in(I).SECURITY_GROUP_ID ;
2208 In_REQUEST_ID (J):= i_clev_tbl_in(I).REQUEST_ID ;
2209 In_CREATED_BY (J):= i_clev_tbl_in(I).CREATED_BY ;
2210 In_CREATION_DATE (J):= i_clev_tbl_in(I).CREATION_DATE ;
2211 In_LAST_UPDATED_BY(J):= i_clev_tbl_in(I).LAST_UPDATED_BY ;
2212 In_LAST_UPDATE_DATE (J):= i_clev_tbl_in(I).LAST_UPDATE_DATE ;
2213 In_LAST_UPDATE_LOGIN (J):= i_clev_tbl_in(I).LAST_UPDATE_LOGIN ;
2214 In_COMMITMENT_ID(J):= i_clev_tbl_in(I).COMMITMENT_ID ;
2215 In_FULL_CREDIT(J):= i_clev_tbl_in(I).FULL_CREDIT;
2216
2217 i:=i_clev_tbl_in.next(i);
2218
2219 END LOOP;
2220 l_tabsize := i_clev_tbl_in.COUNT;
2221
2222 END IF;
2223
2224 IF l_clet_tbl_in.count > 0 THEN
2225 i := l_clet_tbl_in.FIRST; K:=0;
2226 while i is not null LOOP
2227 k:=k+1;
2228
2229 TLn_ID(K):= l_clet_tbl_in(I).Id;
2230
2231 tln_major_version(k) := l_clet_tbl_in(I).major_version;
2232 tln_language(k) :=l_clet_tbl_in(I).language;
2233 tln_source_lang(k) :=l_clet_tbl_in(I).source_lang;
2234 tln_sfwt_flag(k) := l_clet_tbl_in(I).sfwt_flag;
2235 tln_invoice_text(k) :=NULL ; --l_clet_tbl_in(I).invoice_text;
2236 -- tln_IB_TRX_DETAILS(k):=l_clet_tbl_in(I).IB_TRX_DETAILS;
2237 -- tln_STATUS_TEXT(k):=l_clet_tbl_in(I).STATUS_TEXT;
2238 -- tln_REACT_TIME_NAME(k):=l_clet_tbl_in(I).REACT_TIME_NAME;
2239 -- tln_SECURITY_GROUP_ID(k):=l_clet_tbl_in(I).SECURITY_GROUP_ID;
2240 tln_created_by(k) :=l_clet_tbl_in(I).created_by;
2241 tln_creation_date(k) :=l_clet_tbl_in(I).creation_date;
2242 tln_last_updated_by(k) :=l_clet_tbl_in(I).last_updated_by;
2243 tln_last_update_date(k) :=l_clet_tbl_in(I).last_update_date;
2244 tln_last_update_login(k) :=l_clet_tbl_in(I).last_update_login;
2245 i:=l_clet_tbl_in.next(i);
2246 END LOOP;
2247 l_tabsize2 := l_clet_tbl_in.COUNT;
2248
2249 END IF;
2250
2251
2252 FORALL I IN 1 .. l_tabsize
2253
2254 INSERT INTO OKS_K_LINES_BH
2255 (
2256 ID,
2257 MAJOR_VERSION ,
2258 CLE_ID,
2259 DNZ_CHR_ID,
2260 DISCOUNT_LIST ,
2261 ACCT_RULE_ID,
2262 PAYMENT_TYPE,
2263 CC_NO ,
2264 CC_EXPIRY_DATE,
2265 CC_BANK_ACCT_ID ,
2266 CC_AUTH_CODE,
2267 LOCKED_PRICE_LIST_ID,
2268 USAGE_EST_YN,
2269 USAGE_EST_METHOD,
2270 USAGE_EST_START_DATE,
2271 TERMN_METHOD,
2272 UBT_AMOUNT,
2273 CREDIT_AMOUNT ,
2274 SUPPRESSED_CREDIT ,
2275 OVERRIDE_AMOUNT ,
2276 CUST_PO_NUMBER_REQ_YN ,
2277 CUST_PO_NUMBER,
2278 GRACE_DURATION,
2279 GRACE_PERIOD,
2280 INV_PRINT_FLAG,
2281 PRICE_UOM ,
2282 TAX_AMOUNT,
2283 TAX_INCLUSIVE_YN,
2284 TAX_STATUS,
2285 TAX_CODE,
2286 TAX_EXEMPTION_ID,
2287 IB_TRANS_TYPE ,
2288 IB_TRANS_DATE ,
2289 PROD_PRICE,
2290 SERVICE_PRICE ,
2291 CLVL_LIST_PRICE ,
2292 CLVL_QUANTITY ,
2293 CLVL_EXTENDED_AMT ,
2294 CLVL_UOM_CODE ,
2295 TOPLVL_OPERAND_CODE ,
2296 TOPLVL_OPERAND_VAL,
2297 TOPLVL_QUANTITY ,
2298 TOPLVL_UOM_CODE ,
2299 TOPLVL_ADJ_PRICE,
2300 TOPLVL_PRICE_QTY,
2301 AVERAGING_INTERVAL,
2302 SETTLEMENT_INTERVAL ,
2303 MINIMUM_QUANTITY,
2304 DEFAULT_QUANTITY,
2305 AMCV_FLAG ,
2306 FIXED_QUANTITY,
2307 USAGE_DURATION,
2308 USAGE_PERIOD,
2309 LEVEL_YN,
2310 USAGE_TYPE,
2311 UOM_QUANTIFIED,
2312 BASE_READING,
2313 BILLING_SCHEDULE_TYPE ,
2314 COVERAGE_TYPE ,
2315 EXCEPTION_COV_ID,
2316 LIMIT_UOM_QUANTIFIED,
2317 DISCOUNT_AMOUNT ,
2318 DISCOUNT_PERCENT,
2319 OFFSET_DURATION ,
2320 OFFSET_PERIOD ,
2321 INCIDENT_SEVERITY_ID,
2322 PDF_ID,
2323 WORK_THRU_YN,
2324 REACT_ACTIVE_YN ,
2325 TRANSFER_OPTION ,
2326 PROD_UPGRADE_YN ,
2327 INHERITANCE_TYPE,
2328 PM_PROGRAM_ID ,
2329 PM_CONF_REQ_YN,
2330 PM_SCH_EXISTS_YN,
2331 ALLOW_BT_DISCOUNT ,
2332 APPLY_DEFAULT_TIMEZONE,
2333 SYNC_DATE_INSTALL ,
2334 OBJECT_VERSION_NUMBER ,
2335 SECURITY_GROUP_ID ,
2336 REQUEST_ID,
2337 CREATED_BY,
2338 CREATION_DATE ,
2339 LAST_UPDATED_BY ,
2340 LAST_UPDATE_DATE,
2341 LAST_UPDATE_LOGIN ,
2342 COMMITMENT_ID ,
2343 FULL_CREDIT)
2344
2345 VALUES
2346 (
2347 In_ID(I),
2348 In_MAJOR_VERSION (I),
2349 In_CLE_ID(I),
2350 In_DNZ_CHR_ID(I),
2351 In_DISCOUNT_LIST (I),
2352 In_ACCT_RULE_ID(I),
2353 In_PAYMENT_TYPE(I),
2354 In_CC_NO (I),
2355 In_CC_EXPIRY_DATE(I),
2356 In_CC_BANK_ACCT_ID (I),
2357 In_CC_AUTH_CODE(I),
2358 In_LOCKED_PRICE_LIST_ID(I),
2359 In_USAGE_EST_YN(I),
2360 In_USAGE_EST_METHOD(I),
2361 In_USAGE_EST_START_DATE(I),
2362 In_TERMN_METHOD(I),
2363 In_UBT_AMOUNT(I),
2364 In_CREDIT_AMOUNT (I),
2365 In_SUPPRESSED_CREDIT (I),
2366 In_OVERRIDE_AMOUNT (I),
2367 In_CUST_PO_NUMBER_REQ_YN (I),
2368 In_CUST_PO_NUMBER(I),
2369 In_GRACE_DURATION(I),
2370 In_GRACE_PERIOD(I),
2371 In_INV_PRINT_FLAG(I),
2372 In_PRICE_UOM (I),
2373 In_TAX_AMOUNT(I),
2374 In_TAX_INCLUSIVE_YN(I),
2375 In_TAX_STATUS(I),
2376 In_TAX_CODE(I),
2377 In_TAX_EXEMPTION_ID(I),
2378 In_IB_TRANS_TYPE (I),
2379 In_IB_TRANS_DATE (I),
2380 In_PROD_PRICE(I),
2381 In_SERVICE_PRICE (I),
2382 In_CLVL_LIST_PRICE (I),
2383 In_CLVL_QUANTITY (I),
2384 In_CLVL_EXTENDED_AMT (I),
2385 In_CLVL_UOM_CODE (I),
2386 In_TOPLVL_OPERAND_CODE (I),
2387 In_TOPLVL_OPERAND_VAL(I),
2388 In_TOPLVL_QUANTITY (I),
2389 In_TOPLVL_UOM_CODE (I),
2390 In_TOPLVL_ADJ_PRICE(I),
2391 In_TOPLVL_PRICE_QTY(I),
2392 In_AVERAGING_INTERVAL(I),
2393 In_SETTLEMENT_INTERVAL (I),
2394 In_MINIMUM_QUANTITY(I),
2395 In_DEFAULT_QUANTITY(I),
2396 In_AMCV_FLAG (I),
2397 In_FIXED_QUANTITY(I),
2398 In_USAGE_DURATION(I),
2399 In_USAGE_PERIOD(I),
2400 In_LEVEL_YN(I),
2401 In_USAGE_TYPE(I),
2402 In_UOM_QUANTIFIED(I),
2403 In_BASE_READING(I),
2404 In_BILLING_SCHEDULE_TYPE (I),
2405 In_COVERAGE_TYPE (I),
2406 In_EXCEPTION_COV_ID(I),
2407 In_LIMIT_UOM_QUANTIFIED(I),
2408 In_DISCOUNT_AMOUNT (I),
2409 In_DISCOUNT_PERCENT(I),
2410 In_OFFSET_DURATION (I),
2411 In_OFFSET_PERIOD (I),
2412 In_INCIDENT_SEVERITY_ID(I),
2413 In_PDF_ID(I),
2414 In_WORK_THRU_YN(I),
2415 In_REACT_ACTIVE_YN (I),
2416 In_TRANSFER_OPTION (I),
2417 In_PROD_UPGRADE_YN (I),
2418 In_INHERITANCE_TYPE(I),
2419 In_PM_PROGRAM_ID (I),
2420 In_PM_CONF_REQ_YN(I),
2421 In_PM_SCH_EXISTS_YN(I),
2422 In_ALLOW_BT_DISCOUNT (I),
2423 In_APPLY_DEFAULT_TIMEZONE(I),
2424 In_SYNC_DATE_INSTALL (I),
2425 In_OBJECT_VERSION_NUMBER (I),
2426 In_SECURITY_GROUP_ID (I),
2427 In_REQUEST_ID(I),
2428 In_CREATED_BY(I),
2429 In_CREATION_DATE (I),
2430 In_LAST_UPDATED_BY (I),
2431 In_LAST_UPDATE_DATE(I),
2432 In_LAST_UPDATE_LOGIN (I),
2433 In_COMMITMENT_ID (I),
2434 In_FULL_CREDIT(I));
2435
2436 IF l_tabsize2 > 0 THEN
2437 FORALL I IN 1 .. l_tabsize2
2438
2439 INSERT INTO OKS_K_LINES_TLH(
2440 ID,
2441 MAJOR_VERSION,
2442 LANGUAGE,
2443 SOURCE_LANG,
2444 SFWT_FLAG,
2445 INVOICE_TEXT,
2446 IB_TRX_DETAILS,
2447 STATUS_TEXT,
2448 REACT_TIME_NAME,
2449 SECURITY_GROUP_ID,
2450 CREATED_BY,
2451 CREATION_DATE,
2452 LAST_UPDATED_BY,
2453 LAST_UPDATE_DATE,
2454 LAST_UPDATE_LOGIN) VALUES
2455 (
2456 TLn_ID(I),
2457 Tln_MAJOR_VERSION(I),
2458 tln_language(I),
2459 tln_source_lang(I) ,
2460 tln_sfwt_flag(I) ,
2461 tln_invoice_text(I) ,
2462 NULL,
2463 NULL,
2464 NULL,
2465 NULL,
2466 tln_created_by(I),
2467 tln_creation_date(I) ,
2468 tln_last_updated_by(I) ,
2469 tln_last_update_date(I) ,
2470 tln_last_update_login(I) );
2471 END IF;
2472
2473 X_RETURN_STATUS := 'S' ;
2474
2475 EXCEPTION
2476 WHEN OTHERS THEN
2477 ROLLBACK;
2478
2479 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
2480 G_APP_NAME,
2481 G_PKG_NAME,
2482 'OKC_API.G_RET_STS_UNEXP_ERROR',
2483 x_msg_count,
2484 x_msg_data,
2485 '_PVT'
2486 );
2487
2488 IF x_msg_count > 0 THEN
2489 FOR i in 1..x_msg_count LOOP
2490 fnd_msg_pub.get (p_msg_index => -1,
2491 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
2492 p_data => l_msg_data,
2493 p_msg_index_out => l_msg_index_out);
2494 l_message := l_message||' ; '||l_msg_data;
2495
2496 END LOOP;
2497 END IF;
2498
2499 x_return_status := OKC_API.G_RET_STS_ERROR;
2500 END;
2501
2502
2503 PROCEDURE COVERAGE_HISTORY_MIGRATION ( p_start_rowid IN ROWID,
2504 p_end_rowid IN ROWID,
2505 x_return_status OUT NOCOPY VARCHAR2,
2506 x_message_data OUT NOCOPY VARCHAR2) IS
2507
2508 CURSOR Get_CoverageHist_Rules (l_start_rowid IN ROWID ,l_end_rowid IN ROWID ) IS
2509 SELECT
2510 LINE.ID LINE_ID,
2511 Line.Created_By Line_Created_By,
2512 Line.Creation_Date Line_Creation_Date,
2513 Line.Last_Updated_By Line_Last_Updated_By,
2514 Line.Last_Update_Date Line_Last_Update_Date,
2515 Line.Last_Update_Login Line_Last_Update_Login,
2516 RGP.ID LINE_RGP_ID,
2517 Rul.RowID Rul_Row_ID ,
2518 Rul.ID Rule_ID,
2519 LINE.LSE_ID LINE_LSE_ID,
2520 LINE.DNZ_CHR_ID LINE_DNZ_CHR_ID,
2521 OBJECT1_ID1 LINE_OBJECT1_ID1,
2522 OBJECT2_ID1 LINE_OBJECT2_ID1,
2523 RULE_INFORMATION1 COV_RULE_INFO1,
2524 RULE_INFORMATION2 COV_RULE_INFO2,
2525 RULE_INFORMATION3 COV_RULE_INFO3,
2526 RULE_INFORMATION4 COV_RULE_INFO4,
2527 RULE_INFORMATION5 COV_RULE_INFO5,
2528 RULE_INFORMATION6 COV_RULE_INFO6,
2529 RULE_INFORMATION7 COV_RULE_INFO7,
2530 RULE_INFORMATION8 COV_RULE_INFO8,
2531 RULE_INFORMATION9 COV_RULE_INFO9,
2532 RULE_INFORMATION10 COV_RULE_INFO10,
2533 RULE_INFORMATION11 COV_RULE_INFO11,
2534 RULE_INFORMATION12 COV_RULE_INFO12,
2535 RULE_INFORMATION13 COV_RULE_INFO13,
2536 RULE_INFORMATION14 COV_RULE_INFO14,
2537 RULE_INFORMATION15 COV_RULE_INFO15,
2538 RULE_INFORMATION_CATEGORY COV_RULE_INFO,
2539 RUL.OBJECT_VERSION_NUMBER COV_OBJ_VER_NUMBER,
2540 RUL.MAJOR_VERSION MAJOR_VERSION,
2541 KINE.ID k_line_id
2542 FROM
2543 OKC_RULE_GROUPS_BH RGP,
2544 OKC_RULES_BH RUL,
2545 OKC_K_LINES_BH LINE,
2546 OKS_K_LINES_B KINE
2547 WHERE LINE.ID = KINE.CLE_ID
2548 AND LINE.DNZ_CHR_ID = KINE.DNZ_CHR_ID
2549 AND LINE.ID = RGP.CLE_ID
2550 AND RGP.ID = RUL.RGP_ID
2551 AND LINE.LSE_ID IN (2,15,20)
2552 -- AND RUL.RULE_INFORMATION_CATEGORY IN ('ECE','WHE','UGE','STR','CVE','PMP')
2553 AND LINE.DNZ_CHR_ID = RGP.DNZ_CHR_ID
2554 -- AND RUL.RULE_INFORMATION15 IS NULL
2555 AND NOT EXISTS (SELECT CLE_ID FROM OKS_K_LINES_BH WHERE CLE_ID = LINE.ID)
2556 AND Rul.rowid BETWEEN l_start_rowid and l_end_rowid
2557 AND LINE.major_version = RGP.major_version
2558 AND RGP.major_version = RUL.major_version
2559 ORDER BY LINE.ID,RUL.major_version;
2560
2561 CURSOR Get_Rule_TlH (P_ID IN NUMBER) IS
2562 SELECT ID,
2563 MAJOR_VERSION,
2564 LANGUAGE,
2565 SOURCE_LANG,
2566 SFWT_FLAG,
2567 COMMENTS,
2568 TEXT,
2569 CREATED_BY,
2570 CREATION_DATE,
2571 LAST_UPDATED_BY,
2572 LAST_UPDATE_DATE,
2573 LAST_UPDATE_LOGIN,
2574 SECURITY_GROUP_ID
2575 FROM OKC_RULES_TLH
2576 WHERE ID = P_ID;
2577
2578
2579
2580 G_Exception_Halt Exception;
2581
2582
2583 TYPE RowId_Tbl_Type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
2584 TYPE Num_Tbl_Type IS VARRAY(1000) OF NUMBER;
2585 TYPE Date_Tbl_Type IS VARRAY(1000) OF DATE;
2586 TYPE Vc15_Tbl_Type IS VARRAY(1000) OF VARCHAR2(15);
2587 TYPE Vc20_Tbl_Type IS VARRAY(1000) OF VARCHAR2(20);
2588 TYPE Vc150_Tbl_Type IS VARRAY(1000) OF VARCHAR2(150);
2589 TYPE Vc30_Tbl_Type IS VARRAY(1000) OF VARCHAR2(30);
2590 TYPE Vc1_Tbl_Type IS VARRAY(1000) OF VARCHAR2(1);
2591 TYPE Vc3_Tbl_Type IS VARRAY(1000) OF VARCHAR2(3);
2592 TYPE Vc2000_Tbl_Type IS VARRAY(1000) OF VARCHAR2(2000);
2593 TYPE Vc50_Tbl_Type IS VARRAY(1000) OF VARCHAR2(50);
2594 TYPE Vc80_Tbl_Type IS VARRAY(1000) OF VARCHAR2(80);
2595 TYPE Vc120_Tbl_Type IS VARRAY(1000) OF VARCHAR2(120);
2596 TYPE Vc600_Tbl_Type IS VARRAY(1000) OF VARCHAR2(600);
2597
2598 Rul_Row_ID_TBl RowId_Tbl_Type;
2599 LINE_ID_TBL Num_Tbl_Type;
2600 Line_Created_By_TBL Num_Tbl_Type;
2601 Line_Creation_Date_TBL Date_Tbl_Type;
2602 Line_Last_Updated_By_TBL Num_Tbl_Type;
2603 Line_Last_Update_Date_TBL Date_Tbl_Type;
2604 Line_Last_Update_Login_TBL Num_Tbl_Type;
2605 LINE_RGP_ID_TBL Num_Tbl_Type;
2606 Rule_ID_TBL Num_Tbl_Type;
2607 LINE_LSE_ID_TBL Num_Tbl_Type;
2608 LINE_DNZ_CHR_ID_TBL Num_Tbl_Type;
2609 LINE_OBJECT1_ID1_TBL Vc150_Tbl_Type;
2610 LINE_OBJECT2_ID1_TBL Vc150_Tbl_Type;
2611 COV_RULE_INFO1_TBL Vc150_Tbl_Type;
2612 COV_RULE_INFO2_TBL Vc150_Tbl_Type;
2613 COV_RULE_INFO3_TBL Vc150_Tbl_Type;
2614 COV_RULE_INFO4_TBL Vc150_Tbl_Type;
2615 COV_RULE_INFO5_TBL Vc150_Tbl_Type;
2616 COV_RULE_INFO6_TBL Vc150_Tbl_Type;
2617 COV_RULE_INFO7_TBL Vc150_Tbl_Type;
2618 COV_RULE_INFO8_TBL Vc150_Tbl_Type;
2619 COV_RULE_INFO9_TBL Vc150_Tbl_Type;
2620 COV_RULE_INFO10_TBL Vc150_Tbl_Type;
2621 COV_RULE_INFO11_TBL Vc150_Tbl_Type;
2622 COV_RULE_INFO12_TBL Vc150_Tbl_Type;
2623 COV_RULE_INFO13_TBL Vc150_Tbl_Type;
2624 COV_RULE_INFO14_TBL Vc150_Tbl_Type;
2625 COV_RULE_INFO15_TBL Vc150_Tbl_Type;
2626 COV_RULE_INFO_TBL Vc150_Tbl_Type;
2627 COV_OBJ_VER_NUMBER_TBL Num_Tbl_Type;
2628 MAJOR_VERSION_TBL Num_Tbl_Type;
2629 k_line_id_TBL Num_Tbl_Type;
2630
2631
2632 l_cle_ctr NUMBER := 0;
2633 tablename1 VArchar2(1000);
2634 x_msg_count NUMBER := 0;
2635 x_msg_data VArchar2(1000);
2636 L_OLD_CLE_ID NUMBER := -99999;
2637 L_CLE_ID NUMBER := -99999;
2638 l_duration NUMBER;
2639 l_period VArchar2(10);
2640 l_return_status VArchar2(1) := OKC_API.G_RET_STS_SUCCESS;
2641 l_transfer_option VArchar2(250);
2642 l_msg_index_out NUMBER;
2643
2644 l_start_rowid ROWID := p_start_rowid;
2645 l_end_rowid ROWID := p_end_rowid;
2646
2647 l_tabsize NUMBER := i_clev_tbl_in.COUNT;
2648 l_tabsize2 NUMBER;
2649 I NUMBER;
2650 J NUMBER;
2651 K NUMBER;
2652
2653 l_clt_ctr NUMBER := 0;
2654
2655
2656 l_old_line_id NUMBER := -9999;
2657 l_line_id NUMBER;
2658 /****************************************************/
2659 PROCEDURE get_duration_period(p_id IN NUMBER,
2660 x_duration OUT NOCOPY NUMBER,
2661 x_period OUT NOCOPY VARCHAR2) IS
2662
2663 CURSOR get_duration (l_ID IN NUMBER) IS
2664 SELECT UOM_CODE,Duration
2665 FROM OKC_TIMEVALUES_V
2666 WHERE id = l_id;
2667
2668 Lx_Duration NUMBER := NULL;
2669 Lx_Period VARCHAR2(100) := NULL;
2670
2671 BEGIN
2672
2673 FOR get_duration_Rec in get_duration(p_id) LOOP
2674
2675 Lx_period := get_duration_Rec.uom_code;
2676 Lx_duration := get_duration_Rec.Duration;
2677
2678 END LOOP;
2679
2680 X_duration := Lx_duration;
2681 X_Period :=Lx_Period;
2682
2683 EXCEPTION
2684 WHEN OTHERS THEN
2685 OKC_API.SET_MESSAGE
2686 (P_App_Name => G_APP_NAME_OKS
2687 ,P_Msg_Name => G_UNEXPECTED_ERROR
2688 ,P_Token1 => G_SQLCODE_TOKEN
2689 ,P_Token1_Value => SQLCODE
2690 ,P_Token2 => G_SQLERRM_TOKEN
2691 ,P_Token2_Value => SQLERRM);
2692 END;
2693
2694 BEGIN
2695
2696 G_APP_NAME := 'Coverage_History_migration';
2697 OPEN Get_CoverageHist_Rules (l_start_rowid,l_end_rowid);
2698 LOOP
2699 BEGIN
2700 i_clev_tbl_in.DELETE;
2701 l_clet_tbl_in.DELETE;
2702 FETCH Get_CoverageHist_Rules BULK COLLECT INTO
2703
2704 LINE_ID_TBL ,
2705 Line_Created_By_TBL ,
2706 Line_Creation_Date_TBL ,
2707 Line_Last_Updated_By_TBL ,
2708 Line_Last_Update_Date_TBL ,
2709 Line_Last_Update_Login_TBL ,
2710 LINE_RGP_ID_TBL ,
2711 Rul_Row_ID_TBl ,
2712 Rule_Id_Tbl ,
2713 LINE_LSE_ID_TBL ,
2714 LINE_DNZ_CHR_ID_TBL ,
2715 LINE_OBJECT1_ID1_TBL ,
2716 LINE_OBJECT2_ID1_TBL ,
2717 COV_RULE_INFO1_TBL ,
2718 COV_RULE_INFO2_TBL ,
2719 COV_RULE_INFO3_TBL ,
2720 COV_RULE_INFO4_TBL ,
2721 COV_RULE_INFO5_TBL ,
2722 COV_RULE_INFO6_TBL ,
2723 COV_RULE_INFO7_TBL ,
2724 COV_RULE_INFO8_TBL ,
2725 COV_RULE_INFO9_TBL ,
2726 COV_RULE_INFO10_TBL ,
2727 COV_RULE_INFO11_TBL ,
2728 COV_RULE_INFO12_TBL ,
2729 COV_RULE_INFO13_TBL ,
2730 COV_RULE_INFO14_TBL ,
2731 COV_RULE_INFO15_TBL ,
2732 COV_RULE_INFO_TBL ,
2733 COV_OBJ_VER_NUMBER_TBL,
2734 MAJOR_VERSION_TBL,
2735 k_line_id_TBL
2736 LIMIT 1000;--20;
2737
2738 -- dbms_output.put_line('Value of LINE_ID_TBL.COUNT='||TO_CHAR(LINE_ID_TBL.COUNT));
2739
2740 IF LINE_ID_TBL.COUNT > 0 THEN
2741
2742 -- remove dbms
2743
2744 FOR I IN LINE_ID_TBL.FIRST .. LINE_ID_TBL.LAST LOOP
2745 L_Cle_id := LINE_ID_TBL(i);
2746
2747 IF (L_OLD_CLE_ID <> L_CLE_ID) THEN
2748 l_cle_ctr := l_cle_ctr + 1;
2749
2750 i_clev_tbl_in(l_cle_ctr).id := k_line_id_TBL(i);
2751 l_line_id := LINE_ID_TBL(i);
2752 -- dbms_output.put_line('Value of l_line_id='||TO_CHAR(l_line_id));
2753
2754 i_clev_tbl_in(l_cle_ctr).Created_By := Line_Created_By_TBL(i);
2755 i_clev_tbl_in(l_cle_ctr).Creation_Date := Line_Creation_date_TBL(i);
2756 i_clev_tbl_in(l_cle_ctr).Last_Updated_By := Line_Last_Updated_By_TBL(i);
2757 i_clev_tbl_in(l_cle_ctr).Last_Update_Date := Line_Last_Update_Date_TBL(i);
2758 i_clev_tbl_in(l_cle_ctr).Last_Update_Login := Line_Last_Update_Login_TBL(i);
2759 i_clev_tbl_in(l_cle_ctr).Cle_Id := LINE_ID_TBL(i);
2760 i_clev_tbl_in(l_cle_ctr).Dnz_Chr_Id := LINE_DNZ_CHR_ID_TBL(i);
2761 i_clev_tbl_in(l_cle_ctr).object_version_number := MAJOR_VERSION_TBL(i);--1;
2762 i_clev_tbl_in(l_cle_ctr).Sync_Date_Install := 'N';
2763
2764 END IF;
2765
2766 IF COV_RULE_INFO_TBL(i) = 'CVE' THEN
2767 i_clev_tbl_in(l_cle_ctr).Coverage_Type := COV_RULE_INFO1_TBL(i);--'G';
2768 END IF;
2769
2770 IF COV_RULE_INFO_TBL(i) = 'STR' THEN
2771 IF COV_RULE_INFO1_TBL(i) = 'Y' THEN
2772 i_clev_tbl_in(l_cle_ctr).TRANSFER_OPTION := 'TRANS';
2773 ELSE
2774 i_clev_tbl_in(l_cle_ctr).TRANSFER_OPTION := 'NO_CHANGE';
2775 END IF;
2776
2777 END IF;
2778
2779
2780 IF COV_RULE_INFO_TBL(i) = 'UGE' THEN
2781 i_clev_tbl_in(l_cle_ctr).Prod_Upgrade_YN := COV_RULE_INFO1_TBL(i);--'N';
2782 END IF;
2783
2784
2785 IF COV_RULE_INFO_TBL(i) = 'ECE' THEN
2786 i_clev_tbl_in(l_cle_ctr).EXCEPTION_COV_ID := COV_RULE_INFO1_TBL(i);
2787 END IF;
2788
2789 IF COV_RULE_INFO_TBL(i) = 'WHE' THEN
2790 i_clev_tbl_in(l_cle_ctr).Prod_Upgrade_YN := COV_RULE_INFO1_TBL(i);
2791 END IF;
2792
2793 IF COV_RULE_INFO_TBL(i) = 'PMP' THEN
2794
2795 i_clev_tbl_in(l_cle_ctr).PM_PROGRAM_ID := LINE_OBJECT1_ID1_TBL(i);
2796 i_clev_tbl_in(l_cle_ctr).PM_CONF_REQ_YN := COV_RULE_INFO1_TBL(i);
2797 i_clev_tbl_in(l_cle_ctr).PM_SCH_EXISTS_YN := COV_RULE_INFO2_TBL(i);
2798
2799 END IF;
2800
2801 ------------------------- FOR TLH-----------------------
2802 IF l_line_id <> l_old_line_id THEN
2803 -- dbms_output.put_line('--->Value of Rule_Id_Tbl(l_cle_ctr)='||TO_CHAR(Rule_Id_Tbl(l_cle_ctr)));
2804 FOR Get_Rule_TlH_REC IN Get_Rule_TlH(Rule_Id_Tbl(l_cle_ctr)) LOOP
2805 l_clt_ctr := l_clt_ctr + 1;
2806 -- dbms_output.put_line('---------->l_clt_ctr='||TO_CHAR(l_clt_ctr));
2807 l_clet_tbl_in(l_clt_ctr).id := i_clev_tbl_in(l_cle_ctr).id;
2808 l_clet_tbl_in(l_clt_ctr).MAJOR_VERSION := Get_Rule_TlH_REC.MAJOR_VERSION;
2809 l_clet_tbl_in(l_clt_ctr).language := Get_Rule_TlH_REC.language;
2810 l_clet_tbl_in(l_clt_ctr).source_lang := Get_Rule_TlH_REC.source_lang;
2811 l_clet_tbl_in(l_clt_ctr).sfwt_flag := Get_Rule_TlH_REC.sfwt_flag;
2812 l_clet_tbl_in(l_clt_ctr).invoice_text := NULL;-- Get_Rule_TlH_REC.text;
2813 -- l_clet_tbl_in(l_clt_ctr).ib_trx_details := Get_Rule_TlH_REC.ib_trx_details;
2814 -- l_clet_tbl_in(l_clt_ctr).status_text := Get_Rule_TlH_REC.status_text;
2815 -- l_clet_tbl_in(l_clt_ctr).react_time_name := Get_Rule_TlH_REC.react_time_name;
2816 l_clet_tbl_in(l_clt_ctr).created_by := Get_Rule_TlH_REC.created_by;
2817 l_clet_tbl_in(l_clt_ctr).creation_date := Get_Rule_TlH_REC.creation_date;
2818 l_clet_tbl_in(l_clt_ctr).last_updated_by := Get_Rule_TlH_REC.last_updated_by;
2819 l_clet_tbl_in(l_clt_ctr).last_update_date := Get_Rule_TlH_REC.last_update_date;
2820 l_clet_tbl_in(l_clt_ctr).last_update_login := Get_Rule_TlH_REC.last_update_login;
2821
2822 END LOOP;
2823 l_old_line_id := l_line_id;
2824 -- dbms_output.put_line('Value of l_old_line_id='||TO_CHAR(l_old_line_id));
2825 END IF;
2826 --------------------------------------------------------------------
2827
2828 L_OLD_CLE_ID := L_CLE_ID;
2829 END LOOP;
2830 END IF;
2831
2832 tablename1 := 'OKS_K_LINES';
2833 -- dbms_output.put_line('Value of i_clev_tbl_in.countBefore Insert '||TO_CHAR(i_clev_tbl_in.count));
2834
2835 IF i_clev_tbl_in.count > 0 THEN
2836 i := i_clev_tbl_in.FIRST; j:=0;
2837 while i is not null LOOP
2838 j:=j+1;
2839
2840 In_ID(J):= i_clev_tbl_in(I).Id;
2841 In_MAJOR_VERSION (J):= i_clev_tbl_in(I).object_version_number;
2842 In_CLE_ID (J):= i_clev_tbl_in(I).CLE_ID ;
2843 In_DNZ_CHR_ID (J):= i_clev_tbl_in(I).DNZ_CHR_ID ;
2844 In_DISCOUNT_LIST(J):= i_clev_tbl_in(I).DISCOUNT_LIST ;
2845 In_ACCT_RULE_ID (J):= i_clev_tbl_in(I).ACCT_RULE_ID ;
2846 In_PAYMENT_TYPE (J):= i_clev_tbl_in(I).PAYMENT_TYPE ;
2847 In_CC_NO (J):= i_clev_tbl_in(I).CC_NO ;
2848 In_CC_EXPIRY_DATE (J):= i_clev_tbl_in(I).CC_EXPIRY_DATE;
2849 In_CC_BANK_ACCT_ID (J):= i_clev_tbl_in(I).CC_BANK_ACCT_ID ;
2850 In_CC_AUTH_CODE (J):= i_clev_tbl_in(I).CC_AUTH_CODE ;
2851 In_LOCKED_PRICE_LIST_ID (J):= i_clev_tbl_in(I).LOCKED_PRICE_LIST_ID ;
2852 In_USAGE_EST_YN (J):= i_clev_tbl_in(I).USAGE_EST_YN ;
2853 In_USAGE_EST_METHOD (J):= i_clev_tbl_in(I).USAGE_EST_METHOD ;
2854 In_USAGE_EST_START_DATE (J):= i_clev_tbl_in(I).USAGE_EST_START_DATE ;
2855 In_TERMN_METHOD (J):= i_clev_tbl_in(I).TERMN_METHOD ;
2856 In_UBT_AMOUNT (J):= i_clev_tbl_in(I).UBT_AMOUNT ;
2857 In_CREDIT_AMOUNT(J):= i_clev_tbl_in(I).CREDIT_AMOUNT ;
2858 In_SUPPRESSED_CREDIT (J):= i_clev_tbl_in(I).SUPPRESSED_CREDIT ;
2859 In_OVERRIDE_AMOUNT (J):= i_clev_tbl_in(I).OVERRIDE_AMOUNT ;
2860 In_CUST_PO_NUMBER_REQ_YN(J):= i_clev_tbl_in(I).CUST_PO_NUMBER_REQ_YN ;
2861 In_CUST_PO_NUMBER (J):= i_clev_tbl_in(I).CUST_PO_NUMBER;
2862 In_GRACE_DURATION (J):= i_clev_tbl_in(I).GRACE_DURATION;
2863 In_GRACE_PERIOD (J):= i_clev_tbl_in(I).GRACE_PERIOD ;
2864 In_INV_PRINT_FLAG (J):= i_clev_tbl_in(I).INV_PRINT_FLAG;
2865 In_PRICE_UOM (J):= i_clev_tbl_in(I).PRICE_UOM ;
2866 In_TAX_AMOUNT (J):= i_clev_tbl_in(I).TAX_AMOUNT ;
2867 In_TAX_INCLUSIVE_YN (J):= i_clev_tbl_in(I).TAX_INCLUSIVE_YN ;
2868 In_TAX_STATUS (J):= i_clev_tbl_in(I).TAX_STATUS ;
2869 In_TAX_CODE (J):= i_clev_tbl_in(I).TAX_CODE ;
2870 In_TAX_EXEMPTION_ID (J):= i_clev_tbl_in(I).TAX_EXEMPTION_ID ;
2871 In_IB_TRANS_TYPE(J):= i_clev_tbl_in(I).IB_TRANS_TYPE ;
2872 In_IB_TRANS_DATE(J):= i_clev_tbl_in(I).IB_TRANS_DATE ;
2873 In_PROD_PRICE (J):= i_clev_tbl_in(I).PROD_PRICE ;
2874 In_SERVICE_PRICE(J):= i_clev_tbl_in(I).SERVICE_PRICE ;
2875 In_CLVL_LIST_PRICE (J):= i_clev_tbl_in(I).CLVL_LIST_PRICE ;
2876 In_CLVL_QUANTITY(J):= i_clev_tbl_in(I).CLVL_QUANTITY ;
2877 In_CLVL_EXTENDED_AMT (J):= i_clev_tbl_in(I).CLVL_EXTENDED_AMT ;
2878 In_CLVL_UOM_CODE(J):= i_clev_tbl_in(I).CLVL_UOM_CODE ;
2879 In_TOPLVL_OPERAND_CODE (J):= i_clev_tbl_in(I).TOPLVL_OPERAND_CODE ;
2880 In_TOPLVL_OPERAND_VAL (J):= i_clev_tbl_in(I).TOPLVL_OPERAND_VAL ;
2881 In_TOPLVL_QUANTITY (J):= i_clev_tbl_in(I).TOPLVL_QUANTITY ;
2882 In_TOPLVL_UOM_CODE (J):= i_clev_tbl_in(I).TOPLVL_UOM_CODE ;
2883 In_TOPLVL_ADJ_PRICE (J):= i_clev_tbl_in(I).TOPLVL_ADJ_PRICE ;
2884 In_TOPLVL_PRICE_QTY (J):= i_clev_tbl_in(I).TOPLVL_PRICE_QTY ;
2885 In_AVERAGING_INTERVAL (J):= i_clev_tbl_in(I).AVERAGING_INTERVAL ;
2886 In_SETTLEMENT_INTERVAL (J):= i_clev_tbl_in(I).SETTLEMENT_INTERVAL ;
2887 In_MINIMUM_QUANTITY (J):= i_clev_tbl_in(I).MINIMUM_QUANTITY ;
2888 In_DEFAULT_QUANTITY (J):= i_clev_tbl_in(I).DEFAULT_QUANTITY ;
2889 In_AMCV_FLAG (J):= i_clev_tbl_in(I).AMCV_FLAG ;
2890 In_FIXED_QUANTITY (J):= i_clev_tbl_in(I).FIXED_QUANTITY;
2891 In_USAGE_DURATION (J):= i_clev_tbl_in(I).USAGE_DURATION;
2892 In_USAGE_PERIOD (J):= i_clev_tbl_in(I).USAGE_PERIOD ;
2893 In_LEVEL_YN (J):= i_clev_tbl_in(I).LEVEL_YN ;
2894 In_USAGE_TYPE (J):= i_clev_tbl_in(I).USAGE_TYPE ;
2895 In_UOM_QUANTIFIED (J):= i_clev_tbl_in(I).UOM_QUANTIFIED;
2896 In_BASE_READING (J):= i_clev_tbl_in(I).BASE_READING ;
2897 In_BILLING_SCHEDULE_TYPE(J):= i_clev_tbl_in(I).BILLING_SCHEDULE_TYPE ;
2898 In_COVERAGE_TYPE(J):= i_clev_tbl_in(I).COVERAGE_TYPE ;
2899 In_EXCEPTION_COV_ID (J):= i_clev_tbl_in(I).EXCEPTION_COV_ID ;
2900 In_LIMIT_UOM_QUANTIFIED (J):= i_clev_tbl_in(I).LIMIT_UOM_QUANTIFIED ;
2901 In_DISCOUNT_AMOUNT (J):= i_clev_tbl_in(I).DISCOUNT_AMOUNT ;
2902 In_DISCOUNT_PERCENT (J):= i_clev_tbl_in(I).DISCOUNT_PERCENT ;
2903 In_OFFSET_DURATION (J):= i_clev_tbl_in(I).OFFSET_DURATION ;
2904 In_OFFSET_PERIOD(J):= i_clev_tbl_in(I).OFFSET_PERIOD ;
2905 In_INCIDENT_SEVERITY_ID (J):= i_clev_tbl_in(I).INCIDENT_SEVERITY_ID ;
2906 In_PDF_ID (J):= i_clev_tbl_in(I).PDF_ID ;
2907 In_WORK_THRU_YN (J):= i_clev_tbl_in(I).WORK_THRU_YN ;
2908 In_REACT_ACTIVE_YN (J):= i_clev_tbl_in(I).REACT_ACTIVE_YN ;
2909 In_TRANSFER_OPTION (J):= i_clev_tbl_in(I).TRANSFER_OPTION ;
2910 In_PROD_UPGRADE_YN (J):= i_clev_tbl_in(I).PROD_UPGRADE_YN ;
2911 In_INHERITANCE_TYPE (J):= i_clev_tbl_in(I).INHERITANCE_TYPE ;
2912 In_PM_PROGRAM_ID(J):= i_clev_tbl_in(I).PM_PROGRAM_ID ;
2913 In_PM_CONF_REQ_YN (J):= i_clev_tbl_in(I).PM_CONF_REQ_YN;
2914 In_PM_SCH_EXISTS_YN (J):= i_clev_tbl_in(I).PM_SCH_EXISTS_YN ;
2915 In_ALLOW_BT_DISCOUNT (J):= i_clev_tbl_in(I).ALLOW_BT_DISCOUNT ;
2916 In_APPLY_DEFAULT_TIMEZONE (J):= i_clev_tbl_in(I).APPLY_DEFAULT_TIMEZONE ;
2917 In_SYNC_DATE_INSTALL (J):= i_clev_tbl_in(I).SYNC_DATE_INSTALL ;
2918 In_OBJECT_VERSION_NUMBER (J):= i_clev_tbl_in(I).OBJECT_VERSION_NUMBER ;
2919 In_SECURITY_GROUP_ID (J):= i_clev_tbl_in(I).SECURITY_GROUP_ID ;
2920 In_REQUEST_ID (J):= i_clev_tbl_in(I).REQUEST_ID ;
2921 In_CREATED_BY (J):= i_clev_tbl_in(I).CREATED_BY ;
2922 In_CREATION_DATE (J):= i_clev_tbl_in(I).CREATION_DATE ;
2923 In_LAST_UPDATED_BY(J):= i_clev_tbl_in(I).LAST_UPDATED_BY ;
2924 In_LAST_UPDATE_DATE (J):= i_clev_tbl_in(I).LAST_UPDATE_DATE ;
2925 In_LAST_UPDATE_LOGIN (J):= i_clev_tbl_in(I).LAST_UPDATE_LOGIN ;
2926 In_COMMITMENT_ID(J):= i_clev_tbl_in(I).COMMITMENT_ID ;
2927 In_FULL_CREDIT(J):= i_clev_tbl_in(I).FULL_CREDIT;
2928
2929 i:=i_clev_tbl_in.next(i);
2930
2931 END LOOP;
2932
2933 IF l_clet_tbl_in.count > 0 THEN
2934 i := l_clet_tbl_in.FIRST; K:=0;
2935 while i is not null LOOP
2936 k:=k+1;
2937
2938 TLn_ID(K):= l_clet_tbl_in(I).Id;
2939
2940 tln_major_version(k) := l_clet_tbl_in(I).major_version;
2941 tln_language(k) :=l_clet_tbl_in(I).language;
2942 tln_source_lang(k) :=l_clet_tbl_in(I).source_lang;
2943 tln_sfwt_flag(k) := l_clet_tbl_in(I).sfwt_flag;
2944 tln_invoice_text(k) := NULL; --l_clet_tbl_in(I).invoice_text;
2945 -- tln_IB_TRX_DETAILS(k):=l_clet_tbl_in(I).IB_TRX_DETAILS;
2946 -- tln_STATUS_TEXT(k):=l_clet_tbl_in(I).STATUS_TEXT;
2947 -- tln_REACT_TIME_NAME(k):=l_clet_tbl_in(I).REACT_TIME_NAME;
2948 -- tln_SECURITY_GROUP_ID(k):=l_clet_tbl_in(I).SECURITY_GROUP_ID;
2949 tln_created_by(k) :=l_clet_tbl_in(I).created_by;
2950 tln_creation_date(k) :=l_clet_tbl_in(I).creation_date;
2951 tln_last_updated_by(k) :=l_clet_tbl_in(I).last_updated_by;
2952 tln_last_update_date(k) :=l_clet_tbl_in(I).last_update_date;
2953 tln_last_update_login(k) :=l_clet_tbl_in(I).last_update_login;
2954 i:=l_clet_tbl_in.next(i);
2955 END LOOP;
2956 l_tabsize2 := l_clet_tbl_in.COUNT;
2957
2958 END IF;
2959
2960 l_tabsize := i_clev_tbl_in.COUNT;
2961 -- dbms_output.put_line('Value of l_tabsize='||TO_CHAR(l_tabsize));
2962 FORALL I IN 1 .. l_tabsize
2963
2964 INSERT INTO OKS_K_LINES_BH
2965 (
2966 ID,
2967 MAJOR_VERSION ,
2968 CLE_ID,
2969 DNZ_CHR_ID,
2970 DISCOUNT_LIST ,
2971 ACCT_RULE_ID,
2972 PAYMENT_TYPE,
2973 CC_NO ,
2974 CC_EXPIRY_DATE,
2975 CC_BANK_ACCT_ID ,
2976 CC_AUTH_CODE,
2977 LOCKED_PRICE_LIST_ID,
2978 USAGE_EST_YN,
2979 USAGE_EST_METHOD,
2980 USAGE_EST_START_DATE,
2981 TERMN_METHOD,
2982 UBT_AMOUNT,
2983 CREDIT_AMOUNT ,
2984 SUPPRESSED_CREDIT ,
2985 OVERRIDE_AMOUNT ,
2986 CUST_PO_NUMBER_REQ_YN ,
2987 CUST_PO_NUMBER,
2988 GRACE_DURATION,
2989 GRACE_PERIOD,
2990 INV_PRINT_FLAG,
2991 PRICE_UOM ,
2992 TAX_AMOUNT,
2993 TAX_INCLUSIVE_YN,
2994 TAX_STATUS,
2995 TAX_CODE,
2996 TAX_EXEMPTION_ID,
2997 IB_TRANS_TYPE ,
2998 IB_TRANS_DATE ,
2999 PROD_PRICE,
3000 SERVICE_PRICE ,
3001 CLVL_LIST_PRICE ,
3002 CLVL_QUANTITY ,
3003 CLVL_EXTENDED_AMT ,
3004 CLVL_UOM_CODE ,
3005 TOPLVL_OPERAND_CODE ,
3006 TOPLVL_OPERAND_VAL,
3007 TOPLVL_QUANTITY ,
3008 TOPLVL_UOM_CODE ,
3009 TOPLVL_ADJ_PRICE,
3010 TOPLVL_PRICE_QTY,
3011 AVERAGING_INTERVAL,
3012 SETTLEMENT_INTERVAL ,
3013 MINIMUM_QUANTITY,
3014 DEFAULT_QUANTITY,
3015 AMCV_FLAG ,
3016 FIXED_QUANTITY,
3017 USAGE_DURATION,
3018 USAGE_PERIOD,
3019 LEVEL_YN,
3020 USAGE_TYPE,
3021 UOM_QUANTIFIED,
3022 BASE_READING,
3023 BILLING_SCHEDULE_TYPE ,
3024 COVERAGE_TYPE ,
3025 EXCEPTION_COV_ID,
3026 LIMIT_UOM_QUANTIFIED,
3027 DISCOUNT_AMOUNT ,
3028 DISCOUNT_PERCENT,
3029 OFFSET_DURATION ,
3030 OFFSET_PERIOD ,
3031 INCIDENT_SEVERITY_ID,
3032 PDF_ID,
3033 WORK_THRU_YN,
3034 REACT_ACTIVE_YN ,
3035 TRANSFER_OPTION ,
3036 PROD_UPGRADE_YN ,
3037 INHERITANCE_TYPE,
3038 PM_PROGRAM_ID ,
3039 PM_CONF_REQ_YN,
3040 PM_SCH_EXISTS_YN,
3041 ALLOW_BT_DISCOUNT ,
3042 APPLY_DEFAULT_TIMEZONE,
3043 SYNC_DATE_INSTALL ,
3044 OBJECT_VERSION_NUMBER ,
3045 SECURITY_GROUP_ID ,
3046 REQUEST_ID,
3047 CREATED_BY,
3048 CREATION_DATE ,
3049 LAST_UPDATED_BY ,
3050 LAST_UPDATE_DATE,
3051 LAST_UPDATE_LOGIN ,
3052 COMMITMENT_ID ,
3053 FULL_CREDIT)
3054
3055 VALUES
3056 (
3057 In_ID(I),
3058 In_MAJOR_VERSION (I),
3059 In_CLE_ID(I),
3060 In_DNZ_CHR_ID(I),
3061 In_DISCOUNT_LIST (I),
3062 In_ACCT_RULE_ID(I),
3063 In_PAYMENT_TYPE(I),
3064 In_CC_NO (I),
3065 In_CC_EXPIRY_DATE(I),
3066 In_CC_BANK_ACCT_ID (I),
3067 In_CC_AUTH_CODE(I),
3068 In_LOCKED_PRICE_LIST_ID(I),
3069 In_USAGE_EST_YN(I),
3070 In_USAGE_EST_METHOD(I),
3071 In_USAGE_EST_START_DATE(I),
3072 In_TERMN_METHOD(I),
3073 In_UBT_AMOUNT(I),
3074 In_CREDIT_AMOUNT (I),
3075 In_SUPPRESSED_CREDIT (I),
3076 In_OVERRIDE_AMOUNT (I),
3077 In_CUST_PO_NUMBER_REQ_YN (I),
3078 In_CUST_PO_NUMBER(I),
3079 In_GRACE_DURATION(I),
3080 In_GRACE_PERIOD(I),
3081 In_INV_PRINT_FLAG(I),
3082 In_PRICE_UOM (I),
3083 In_TAX_AMOUNT(I),
3084 In_TAX_INCLUSIVE_YN(I),
3085 In_TAX_STATUS(I),
3086 In_TAX_CODE(I),
3087 In_TAX_EXEMPTION_ID(I),
3088 In_IB_TRANS_TYPE (I),
3089 In_IB_TRANS_DATE (I),
3090 In_PROD_PRICE(I),
3091 In_SERVICE_PRICE (I),
3092 In_CLVL_LIST_PRICE (I),
3093 In_CLVL_QUANTITY (I),
3094 In_CLVL_EXTENDED_AMT (I),
3095 In_CLVL_UOM_CODE (I),
3096 In_TOPLVL_OPERAND_CODE (I),
3097 In_TOPLVL_OPERAND_VAL(I),
3098 In_TOPLVL_QUANTITY (I),
3099 In_TOPLVL_UOM_CODE (I),
3100 In_TOPLVL_ADJ_PRICE(I),
3101 In_TOPLVL_PRICE_QTY(I),
3102 In_AVERAGING_INTERVAL(I),
3103 In_SETTLEMENT_INTERVAL (I),
3104 In_MINIMUM_QUANTITY(I),
3105 In_DEFAULT_QUANTITY(I),
3106 In_AMCV_FLAG (I),
3107 In_FIXED_QUANTITY(I),
3108 In_USAGE_DURATION(I),
3109 In_USAGE_PERIOD(I),
3110 In_LEVEL_YN(I),
3111 In_USAGE_TYPE(I),
3112 In_UOM_QUANTIFIED(I),
3113 In_BASE_READING(I),
3114 In_BILLING_SCHEDULE_TYPE (I),
3115 In_COVERAGE_TYPE (I),
3116 In_EXCEPTION_COV_ID(I),
3117 In_LIMIT_UOM_QUANTIFIED(I),
3118 In_DISCOUNT_AMOUNT (I),
3119 In_DISCOUNT_PERCENT(I),
3120 In_OFFSET_DURATION (I),
3121 In_OFFSET_PERIOD (I),
3122 In_INCIDENT_SEVERITY_ID(I),
3123 In_PDF_ID(I),
3124 In_WORK_THRU_YN(I),
3125 In_REACT_ACTIVE_YN (I),
3126 In_TRANSFER_OPTION (I),
3127 In_PROD_UPGRADE_YN (I),
3128 In_INHERITANCE_TYPE(I),
3129 In_PM_PROGRAM_ID (I),
3130 In_PM_CONF_REQ_YN(I),
3131 In_PM_SCH_EXISTS_YN(I),
3132 In_ALLOW_BT_DISCOUNT (I),
3133 In_APPLY_DEFAULT_TIMEZONE(I),
3134 In_SYNC_DATE_INSTALL (I),
3135 In_OBJECT_VERSION_NUMBER (I),
3136 In_SECURITY_GROUP_ID (I),
3137 In_REQUEST_ID(I),
3138 In_CREATED_BY(I),
3139 In_CREATION_DATE (I),
3140 In_LAST_UPDATED_BY (I),
3141 In_LAST_UPDATE_DATE(I),
3142 In_LAST_UPDATE_LOGIN (I),
3143 In_COMMITMENT_ID (I),
3144 In_FULL_CREDIT(I));
3145
3146 IF l_tabsize2 > 0 THEN
3147 FORALL I IN 1 .. l_tabsize2
3148
3149 INSERT INTO OKS_K_LINES_TLH
3150 (
3151 ID,
3152 MAJOR_VERSION,
3153 LANGUAGE,
3154 SOURCE_LANG,
3155 SFWT_FLAG,
3156 INVOICE_TEXT,
3157 IB_TRX_DETAILS,
3158 STATUS_TEXT,
3159 REACT_TIME_NAME,
3160 SECURITY_GROUP_ID,
3161 CREATED_BY,
3162 CREATION_DATE,
3163 LAST_UPDATED_BY,
3164 LAST_UPDATE_DATE,
3165 LAST_UPDATE_LOGIN) VALUES
3166 (
3167 TLn_ID(I),
3168 Tln_MAJOR_VERSION(I),
3169 tln_language(I),
3170 tln_source_lang(I) ,
3171 tln_sfwt_flag(I) ,
3172 tln_invoice_text(I) ,
3173 NULL,
3174 NULL,
3175 NULL,
3176 NULL,
3177 tln_created_by(I),
3178 tln_creation_date(I) ,
3179 tln_last_updated_by(I) ,
3180 tln_last_update_date(I) ,
3181 tln_last_update_login(I) );
3182 END IF;
3183
3184 END IF;
3185 x_return_status := 'S';
3186
3187
3188
3189 EXCEPTION
3190 WHEN G_EXCEPTION_HALT THEN
3191 -- dbms_output.put_line('Value of 1 ERROR '||SQLERRM);
3192 ROLLBACK;
3193
3194 OKC_API.SET_MESSAGE
3195 ( p_app_name => G_APP_NAME,
3196 p_msg_name => G_UNEXPECTED_ERROR,
3197 p_token1 => G_SQLCODE_TOKEN,
3198 p_token1_value => SQLCODE,
3199 p_token2 => G_SQLERRM_TOKEN,
3200 p_token2_value => SQLERRM
3201 );
3202 x_return_status := 'E';
3203 WHEN Others THEN
3204 -- dbms_output.put_line('Value of 2 ERROR '||SQLERRM);
3205
3206 x_return_status := OKC_API.G_RET_STS_ERROR;
3207
3208 OKC_API.SET_MESSAGE
3209 ( p_app_name => G_APP_NAME,
3210 p_msg_name => G_UNEXPECTED_ERROR,
3211 p_token1 => G_SQLCODE_TOKEN,
3212 p_token1_value => SQLCODE,
3213 p_token2 => G_SQLERRM_TOKEN,
3214 p_token2_value => SQLERRM
3215 );
3216
3217 x_return_status := 'E';
3218
3219 END;
3220
3221 EXIT WHEN Get_CoverageHist_Rules%NOTFOUND;
3222
3223 END LOOP;
3224
3225 CLOSE Get_CoverageHist_Rules;
3226
3227
3228 EXCEPTION
3229 WHEN Others THEN
3230
3231 x_return_status := OKC_API.G_RET_STS_ERROR;
3232
3233 OKC_API.SET_MESSAGE
3234 ( p_app_name => G_APP_NAME,
3235 p_msg_name => G_UNEXPECTED_ERROR,
3236 p_token1 => G_SQLCODE_TOKEN,
3237 p_token1_value => SQLCODE,
3238 p_token2 => G_SQLERRM_TOKEN,
3239 p_token2_value => SQLERRM
3240 );
3241 x_return_status := 'E';
3242
3243 END COVERAGE_HISTORY_MIGRATION;
3244
3245
3246 PROCEDURE Buss_Proc_History_migration(p_start_rowid IN ROWID,p_end_rowid IN ROWID,x_return_status OUT NOCOPY VARCHAR2,
3247 x_message_data OUT NOCOPY VARCHAR2) IS
3248
3249 CURSOR Get_Buss_Process_Cur (l_start_rowid IN ROWID ,l_end_rowid IN ROWID ) IS
3250 SELECT
3251 LINE.ID LINE_ID,
3252 Line.Created_By Line_Created_By,
3253 Line.Creation_Date Line_Creation_Date,
3254 Line.Last_Updated_By Line_Last_Updated_By,
3255 Line.Last_Update_Date Line_Last_Update_Date,
3256 Line.Last_Update_Login Line_Last_Update_Login,
3257 Rul.ID Rule_Id,
3258 RGP.ID LINE_RGP_ID,
3259 Rul.RowId Rul_Row_Id,
3260 LINE.LSE_ID LINE_LSE_ID,
3261 LINE.DNZ_CHR_ID LINE_DNZ_CHR_ID,
3262 OBJECT1_ID1 LINE_OBJECT1_ID1,
3263 OBJECT2_ID1 LINE_OBJECT2_ID1,
3264 RULE_INFORMATION1 COV_RULE_INFO1,
3265 RULE_INFORMATION2 COV_RULE_INFO2,
3266 RULE_INFORMATION3 COV_RULE_INFO3,
3267 RULE_INFORMATION4 COV_RULE_INFO4,
3268 RULE_INFORMATION5 COV_RULE_INFO5,
3269 RULE_INFORMATION6 COV_RULE_INFO6,
3270 RULE_INFORMATION7 COV_RULE_INFO7,
3271 RULE_INFORMATION8 COV_RULE_INFO8,
3272 RULE_INFORMATION9 COV_RULE_INFO9,
3273 RULE_INFORMATION10 COV_RULE_INFO10,
3274 RULE_INFORMATION11 COV_RULE_INFO11,
3275 RULE_INFORMATION12 COV_RULE_INFO12,
3276 RULE_INFORMATION13 COV_RULE_INFO13,
3277 RULE_INFORMATION14 COV_RULE_INFO14,
3278 RULE_INFORMATION15 COV_RULE_INFO15,
3279 RULE_INFORMATION_CATEGORY COV_RULE_INFO,
3280 RUL.OBJECT_VERSION_NUMBER COV_OBJ_VER_NUMBER,
3281 RUL.MAJOR_VERSION MAJOR_VERSION,
3282 KINE.ID k_line_id
3283 FROM
3284 OKC_RULE_GROUPS_BH RGP,
3285 OKC_RULES_BH RUL,
3286 OKC_K_LINES_BH LINE,
3287 OKS_K_LINES_B KINE
3288 WHERE KINE.ID > -1
3289 AND LINE.ID = KINE.CLE_ID
3290 AND LINE.DNZ_CHR_ID = KINE.DNZ_CHR_ID
3291 AND LINE.ID = RGP.CLE_ID
3292 AND RGP.ID = RUL.RGP_ID
3293 AND LINE.LSE_ID in (3,16,21)
3294 AND RUL.RULE_INFORMATION_CATEGORY IN ('OFS','CVR','DST','PRE','BTD')
3295 AND LINE.DNZ_CHR_ID = RGP.DNZ_CHR_ID
3296 -- AND RUL.RULE_INFORMATION15 IS NULL
3297 AND Rul.rowid BETWEEN l_start_rowid and l_end_rowid
3298 AND NOT EXISTS (SELECT CLE_ID FROM OKS_K_Lines_BH where cle_id = LINE.ID)
3299 AND LINE.MAJOR_VERSION = RGP.MAJOR_VERSION
3300 AND RGP.MAJOR_VERSION = RUL.MAJOR_VERSION
3301 ORDER BY LINE.ID,RUL.MAJOR_VERSION;
3302
3303 CURSOR Get_Rule_TlH (P_ID IN NUMBER) IS
3304 SELECT ID,
3305 MAJOR_VERSION,
3306 LANGUAGE,
3307 SOURCE_LANG,
3308 SFWT_FLAG,
3309 COMMENTS,
3310 TEXT,
3311 CREATED_BY,
3312 CREATION_DATE,
3313 LAST_UPDATED_BY,
3314 LAST_UPDATE_DATE,
3315 LAST_UPDATE_LOGIN,
3316 SECURITY_GROUP_ID
3317 FROM OKC_RULES_TLH
3318 WHERE ID = P_ID;
3319
3320 TYPE RowId_Tbl_Type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
3321 TYPE Num_Tbl_Type IS VARRAY(1000) OF NUMBER;
3322 TYPE Date_Tbl_Type IS VARRAY(1000) OF DATE;
3323 TYPE Vc15_Tbl_Type IS VARRAY(1000) OF VARCHAR2(15);
3324 TYPE Vc20_Tbl_Type IS VARRAY(1000) OF VARCHAR2(20);
3325 TYPE Vc150_Tbl_Type IS VARRAY(1000) OF VARCHAR2(150);
3326 TYPE Vc30_Tbl_Type IS VARRAY(1000) OF VARCHAR2(30);
3327 TYPE Vc1_Tbl_Type IS VARRAY(1000) OF VARCHAR2(1);
3328 TYPE Vc3_Tbl_Type IS VARRAY(1000) OF VARCHAR2(3);
3329 TYPE Vc2000_Tbl_Type IS VARRAY(1000) OF VARCHAR2(2000);
3330 TYPE Vc50_Tbl_Type IS VARRAY(1000) OF VARCHAR2(50);
3331 TYPE Vc80_Tbl_Type IS VARRAY(1000) OF VARCHAR2(80);
3332 TYPE Vc120_Tbl_Type IS VARRAY(1000) OF VARCHAR2(120);
3333 TYPE Vc600_Tbl_Type IS VARRAY(1000) OF VARCHAR2(600);
3334
3335
3336 Rul_Row_ID_TBl RowId_Tbl_Type;
3337 LINE_ID_TBL Num_Tbl_Type;
3338 Line_Created_By_TBL Num_Tbl_Type;
3339 Line_Creation_Date_TBL Date_Tbl_Type;
3340 Line_Last_Updated_By_TBL Num_Tbl_Type;
3341 Line_Last_Update_Date_TBL Date_Tbl_Type;
3342 Line_Last_Update_Login_TBL Num_Tbl_Type;
3343 RUL_ID_TBL Num_Tbl_Type;
3344 LINE_RGP_ID_TBL Num_Tbl_Type;
3345 LINE_LSE_ID_TBL Num_Tbl_Type;
3346 LINE_DNZ_CHR_ID_TBL Num_Tbl_Type;
3347 LINE_OBJECT1_ID1_TBL Vc150_Tbl_Type;
3348 LINE_OBJECT2_ID1_TBL Vc150_Tbl_Type;
3349 COV_RULE_INFO1_TBL Vc150_Tbl_Type;
3350 COV_RULE_INFO2_TBL Vc150_Tbl_Type;
3351 COV_RULE_INFO3_TBL Vc150_Tbl_Type;
3352 COV_RULE_INFO4_TBL Vc150_Tbl_Type;
3353 COV_RULE_INFO5_TBL Vc150_Tbl_Type;
3354 COV_RULE_INFO6_TBL Vc150_Tbl_Type;
3355 COV_RULE_INFO7_TBL Vc150_Tbl_Type;
3356 COV_RULE_INFO8_TBL Vc150_Tbl_Type;
3357 COV_RULE_INFO9_TBL Vc150_Tbl_Type;
3358 COV_RULE_INFO10_TBL Vc150_Tbl_Type;
3359 COV_RULE_INFO11_TBL Vc150_Tbl_Type;
3360 COV_RULE_INFO12_TBL Vc150_Tbl_Type;
3361 COV_RULE_INFO13_TBL Vc150_Tbl_Type;
3362 COV_RULE_INFO14_TBL Vc150_Tbl_Type;
3363 COV_RULE_INFO15_TBL Vc150_Tbl_Type;
3364 COV_RULE_INFO_TBL Vc150_Tbl_Type;
3365 COV_OBJ_VER_NUMBER_TBL Num_Tbl_Type;
3366 MAJOR_VERSION_TBL Num_Tbl_Type;
3367 k_line_id_TBL Num_Tbl_Type;
3368
3369
3370 l_cle_ctr NUMBER := 0;
3371 tablename1 VArchar2(1000);
3372 x_msg_count NUMBER := 0;
3373 x_msg_data VArchar2(1000);
3374 L_OLD_CLE_ID NUMBER := -99999;
3375 L_CLE_ID NUMBER := -99999;
3376 l_duration NUMBER;
3377 l_period VArchar2(10);
3378 l_return_Status VArchar2(3):= OKC_API.G_RET_STS_SUCCESS;
3379 l_OU_CURRENCY VArchar2(10);
3380 L_MSG_INDEX_OUT number;
3381 l_message VArchar2(1000);
3382 l_start_rowid ROWID := p_start_rowid;
3383 l_end_rowid ROWID := p_end_rowid;
3384 l_tabsize NUMBER := i_clev_tbl_in.COUNT;
3385 l_tabsize2 NUMBER;
3386 I NUMBER;
3387 J NUMBER;
3388 K NUMBER;
3389 l_status VARCHAR2(100);
3390 EXCEPTIONHALT_VALIDATION EXCEPTION;
3391 G_EXCEPTION_HALT EXCEPTION;
3392
3393
3394 l_old_line_id NUMBER := -9999;
3395 l_line_id NUMBER;
3396 /****************************************************/
3397
3398 PROCEDURE get_duration_period(p_id IN NUMBER,
3399 x_duration OUT NOCOPY NUMBER,
3400 x_period OUT NOCOPY VARCHAR2) IS
3401
3402
3403 CURSOR get_duration (l_ID IN NUMBER) IS
3404 SELECT TAL.UOM_CODE,TAL.Duration
3405 FROM OKC_TIMEVALUES_BH ISE, OKC_TIMEVALUES_BH TAL
3406 WHERE ISE.id = l_id
3407 AND ISE.TVE_ID_STARTED = TAL.ID
3408 AND ISE.TVE_TYPE = 'ISE'
3409 AND TAL.TVE_TYPE = 'TAL'
3410 AND ISE.DNZ_CHR_ID = TAL.DNZ_CHR_ID;
3411
3412 Lx_Duration NUMBER := NULL;
3413 Lx_Period VARCHAR2(100) := NULL;
3414
3415 BEGIN
3416
3417 FOR get_duration_Rec in get_duration(p_id) LOOP
3418
3419 Lx_period := get_duration_Rec.uom_code;
3420 Lx_duration := get_duration_Rec.Duration;
3421
3422 END LOOP;
3423
3424 X_duration := Lx_duration;
3425 X_Period :=Lx_Period;
3426
3427 EXCEPTION
3428 WHEN OTHERS THEN
3429 OKC_API.SET_MESSAGE
3430 (P_App_Name => G_APP_NAME_OKS
3431 ,P_Msg_Name => G_UNEXPECTED_ERROR
3432 ,P_Token1 => G_SQLCODE_TOKEN
3433 ,P_Token1_Value => SQLCODE
3434 ,P_Token2 => G_SQLERRM_TOKEN
3435 ,P_Token2_Value => SQLERRM);
3436 END get_duration_period;
3437
3438 PROCEDURE Create_Coverage_Time( P_Rule_Id IN NUMBER,
3439 P_Cle_Id IN NUMBER,
3440 P_Dnz_Chr_ID IN NUMBER,
3441 X_return_Status OUT NOCOPY VARCHAR2) IS
3442
3443 l_rule_ID NUMBER := P_rule_Id;
3444 l_cle_Id NUMBER := P_Cle_id;
3445 l_dnz_Id NUMBER :=P_Dnz_Chr_ID;
3446
3447 l_COV_TZE_LINE_ID NUMBER := null;
3448
3449 l_count NUMBER := 0;
3450
3451 G_EXCEPTIONHALT_VALIDATION EXCEPTION;
3452
3453 CURSOR get_time_zone_ID_Cur (rule_id IN NUMBER) IS
3454 SELECT Times.tze_id tze_id,
3455 Times.Created_By Times_Created_By,
3456 Times.Last_Updated_By Times_Last_Updated_By,
3457 Times.Last_Update_Date Times_Last_Update_Date,
3458 Times.Last_Update_Login Times_Last_Update_Login,
3459 Times.Object_Version_Number Times_Object_Version_Number,
3460 Times.Major_Version Times_Major_Version
3461 FROM okc_timevalues_bh times,
3462 okc_cover_times_h cvt
3463 WHERE CVT.tve_ID = TIMES.id
3464 AND CVT.rul_id = rule_id
3465 AND rownum = 1;
3466
3467 CURSOR get_count_time_zone_ID_Cur (cle_Id IN NUMBER,dnz_Id IN NUMBER) IS
3468 SELECT COUNT(*) NCOUNT
3469 FROM OKS_COVERAGE_TIMEZONES
3470 WHERE cle_id = cle_Id
3471 AND dnz_chr_Id = dnz_Id;
3472
3473 BEGIN
3474 FOR get_count_time_zone_ID_Rec IN get_count_time_zone_ID_Cur(l_cle_Id, l_dnz_Id) LOOP
3475 l_count := get_count_time_zone_ID_Rec.NCOUNT;
3476 END LOOP;
3477
3478 IF l_count =0 THEN
3479
3480 l_COV_TZE_LINE_ID := null;
3481 l_ctz_rec := l_ctz_rec + 1;
3482
3483 FOR get_time_zone_ID_Rec IN get_time_zone_ID_Cur(l_rule_ID) LOOP
3484
3485
3486 i_ctzv_tbl_in(l_ctz_rec).Id := okc_p_util.raw_to_number(sys_guid());
3487
3488
3489 i_ctzv_tbl_in(l_ctz_rec).Created_By := get_time_zone_ID_Rec.Times_Created_By;
3490 i_ctzv_tbl_in(l_ctz_rec).Last_Updated_By := get_time_zone_ID_Rec.Times_Last_Updated_By;
3491 i_ctzv_tbl_in(l_ctz_rec).Last_Update_Date := get_time_zone_ID_Rec.Times_Last_Update_Date;
3492 i_ctzv_tbl_in(l_ctz_rec).Last_Update_Login := get_time_zone_ID_Rec.Times_Last_Update_Login;
3493 i_ctzv_tbl_in(l_ctz_rec).Cle_Id := l_Cle_Id;
3494 i_ctzv_tbl_in(l_ctz_rec).Dnz_Chr_Id := l_dnz_Id;
3495 i_ctzv_tbl_in(l_ctz_rec).DEFAULT_YN := 'Y';
3496 i_ctzv_tbl_in(l_ctz_rec).TIMEZONE_ID := get_time_zone_ID_Rec.tze_id;
3497 i_ctzv_tbl_in(l_ctz_rec).object_version_number := get_time_zone_ID_Rec.Times_Object_Version_Number;
3498 i_ctzv_tbl_in(l_ctz_rec).Major_Version := get_time_zone_ID_Rec.Times_Major_Version;
3499
3500
3501 END LOOP;
3502 END IF;
3503
3504 X_return_Status := 'S';
3505 EXCEPTION
3506 WHEN OTHERS THEN
3507 X_return_Status := 'E';
3508 OKC_API.SET_MESSAGE
3509 (P_App_Name => G_APP_NAME_OKS
3510 ,P_Msg_Name => G_UNEXPECTED_ERROR
3511 ,P_Token1 => G_SQLCODE_TOKEN
3512 ,P_Token1_Value => SQLCODE
3513 ,P_Token2 => G_SQLERRM_TOKEN
3514 ,P_Token2_Value => SQLERRM);
3515
3516 END Create_Coverage_Time;
3517
3518
3519
3520 BEGIN
3521 G_APP_NAME := 'Business_Process_migration';
3522 l_OU_CURRENCY := OKC_CURRENCY_API.GET_OU_CURRENCY;
3523
3524 OPEN Get_Buss_Process_Cur (l_start_rowid,l_end_rowid);
3525 LOOP
3526 BEGIN
3527 FETCH Get_Buss_Process_Cur BULK COLLECT INTO
3528 LINE_ID_TBL ,
3529 Line_Created_By_TBL ,
3530 Line_Creation_Date_TBL ,
3531 Line_Last_Updated_By_TBL ,
3532 Line_Last_Update_Date_TBL ,
3533 Line_Last_Update_Login_TBL ,
3534 RUL_ID_TBL ,
3535 LINE_RGP_ID_TBL ,
3536 Rul_Row_ID_TBl ,
3537 LINE_LSE_ID_TBL ,
3538 LINE_DNZ_CHR_ID_TBL ,
3539 LINE_OBJECT1_ID1_TBL ,
3540 LINE_OBJECT2_ID1_TBL ,
3541 COV_RULE_INFO1_TBL ,
3542 COV_RULE_INFO2_TBL ,
3543 COV_RULE_INFO3_TBL ,
3544 COV_RULE_INFO4_TBL ,
3545 COV_RULE_INFO5_TBL ,
3546 COV_RULE_INFO6_TBL ,
3547 COV_RULE_INFO7_TBL ,
3548 COV_RULE_INFO8_TBL ,
3549 COV_RULE_INFO9_TBL ,
3550 COV_RULE_INFO10_TBL ,
3551 COV_RULE_INFO11_TBL ,
3552 COV_RULE_INFO12_TBL ,
3553 COV_RULE_INFO13_TBL ,
3554 COV_RULE_INFO14_TBL ,
3555 COV_RULE_INFO15_TBL ,
3556 COV_RULE_INFO_TBL ,
3557 COV_OBJ_VER_NUMBER_TBL,
3558 MAJOR_VERSION_TBL,
3559 k_line_id_TBL
3560 LIMIT 1000;
3561
3562 -- -- dbms_output.put_line('Value of LINE_ID_TBL.COUNT='||TO_CHAR(LINE_ID_TBL.COUNT));
3563
3564 IF LINE_ID_TBL.COUNT > 0 THEN
3565
3566 FOR I IN LINE_ID_TBL.FIRST .. LINE_ID_TBL.LAST LOOP
3567 L_Cle_id := LINE_ID_TBL(i);
3568
3569 IF (L_OLD_CLE_ID <> L_CLE_ID) THEN
3570 l_cle_ctr := l_cle_ctr + 1;
3571
3572
3573 i_clev_tbl_in(l_cle_ctr).Id := k_line_id_TBL(I);
3574 l_line_id := LINE_ID_TBL(i);
3575 i_clev_tbl_in(l_cle_ctr).Created_By := Line_Created_By_TBL(i);
3576 i_clev_tbl_in(l_cle_ctr).Creation_Date := Line_Creation_Date_TBL(i);
3577 i_clev_tbl_in(l_cle_ctr).Last_Updated_By := Line_Last_Updated_By_TBL(i);
3578 i_clev_tbl_in(l_cle_ctr).Last_Update_Date := Line_Last_Update_Date_TBL(i);
3579 i_clev_tbl_in(l_cle_ctr).Last_Update_Login := Line_Last_Update_Login_TBL(i);
3580 i_clev_tbl_in(l_cle_ctr).Cle_Id := LINE_ID_TBL(i);
3581 i_clev_tbl_in(l_cle_ctr).Dnz_Chr_Id := LINE_DNZ_CHR_ID_TBL(i);
3582 i_clev_tbl_in(l_cle_ctr).object_version_number := 1;
3583 i_clev_tbl_in(l_cle_ctr).Sync_Date_Install := 'N';
3584
3585 END IF;
3586
3587
3588 IF COV_RULE_INFO_TBL(i) = 'OFS' THEN
3589 IF COV_RULE_INFO1_TBL(i) IS NOT NULL THEN
3590
3591 get_duration_period(p_id => COV_RULE_INFO1_TBL(i),
3592 x_duration => l_duration,
3593 x_period => l_period);
3594
3595 END IF;
3596
3597 i_clev_tbl_in(l_cle_ctr).OFFSET_DURATION := l_duration;
3598 i_clev_tbl_in(l_cle_ctr).OFFSET_PERIOD := l_period;
3599
3600 END IF;
3601
3602
3603
3604 IF COV_RULE_INFO_TBL(i) = 'DST' THEN
3605 i_clev_tbl_in(l_cle_ctr).DISCOUNT_LIST := LINE_OBJECT1_ID1_TBL(i);
3606 END IF;
3607
3608
3609 IF COV_RULE_INFO_TBL(i) = 'PRE' THEN
3610
3611 UPDATE OKC_K_LINES_BH
3612 SET PRICE_LIST_ID = LINE_OBJECT1_ID1_TBL(i),
3613 CURRENCY_CODE = l_OU_CURRENCY
3614 WHERE ID = LINE_ID_TBL(i);
3615
3616 END IF;
3617
3618
3619 IF COV_RULE_INFO_TBL(i) = 'BTD' THEN
3620
3621 i_clev_tbl_in(l_cle_ctr).ALLOW_BT_DISCOUNT := 'Y';
3622 END IF;
3623
3624
3625 IF COV_RULE_INFO_TBL(i) = 'CVR' THEN
3626
3627 Create_Coverage_Time( P_Rule_Id => RUL_ID_TBL(i),
3628 P_Cle_Id => LINE_ID_TBL(i),
3629 P_Dnz_Chr_ID => LINE_DNZ_CHR_ID_TBL(i),
3630 X_return_Status => l_return_Status);
3631
3632 IF l_return_Status <> 'S' THEN
3633 RAISE EXCEPTIONHALT_VALIDATION;
3634 END IF;
3635
3636 END IF;
3637
3638 ------------------------- FOR TLH-----------------------
3639 IF l_line_id <> l_old_line_id THEN
3640 -- dbms_output.put_line('--->Value of Rule_Id_Tbl(l_cle_ctr)='||TO_CHAR(Rul_Id_Tbl(I)));
3641 FOR Get_Rule_TlH_REC IN Get_Rule_TlH(Rul_Id_Tbl(l_cle_ctr)) LOOP
3642 l_clt_ctr := l_clt_ctr + 1;
3643 -- dbms_output.put_line('---------->l_clt_ctr='||TO_CHAR(l_clt_ctr));
3644 l_clet_tbl_in(l_clt_ctr).id := i_clev_tbl_in(l_cle_ctr).id;
3645 l_clet_tbl_in(l_clt_ctr).MAJOR_VERSION := Get_Rule_TlH_REC.MAJOR_VERSION;
3646 l_clet_tbl_in(l_clt_ctr).language := Get_Rule_TlH_REC.language;
3647 l_clet_tbl_in(l_clt_ctr).source_lang := Get_Rule_TlH_REC.source_lang;
3648 l_clet_tbl_in(l_clt_ctr).sfwt_flag := Get_Rule_TlH_REC.sfwt_flag;
3649 l_clet_tbl_in(l_clt_ctr).invoice_text := NULL; -- Get_Rule_TlH_REC.text;
3650 -- l_clet_tbl_in(l_clt_ctr).ib_trx_details := Get_Rule_TlH_REC.ib_trx_details;
3651 -- l_clet_tbl_in(l_clt_ctr).status_text := Get_Rule_TlH_REC.status_text;
3652 -- l_clet_tbl_in(l_clt_ctr).react_time_name := Get_Rule_TlH_REC.react_time_name;
3653 l_clet_tbl_in(l_clt_ctr).created_by := Get_Rule_TlH_REC.created_by;
3654 l_clet_tbl_in(l_clt_ctr).creation_date := Get_Rule_TlH_REC.creation_date;
3655 l_clet_tbl_in(l_clt_ctr).last_updated_by := Get_Rule_TlH_REC.last_updated_by;
3656 l_clet_tbl_in(l_clt_ctr).last_update_date := Get_Rule_TlH_REC.last_update_date;
3657 l_clet_tbl_in(l_clt_ctr).last_update_login := Get_Rule_TlH_REC.last_update_login;
3658
3659 END LOOP;
3660 l_old_line_id := l_line_id;
3661 -- dbms_output.put_line('Value of l_old_line_id='||TO_CHAR(l_old_line_id));
3662 END IF;
3663 --------------------------------------------------------------------
3664 --************************************************
3665
3666 L_OLD_CLE_ID := L_CLE_ID;
3667
3668 --*************************************************
3669
3670 END LOOP;
3671 END IF;
3672
3673 tablename1 := 'OKS_K_LINES';
3674 -- dbms_output.put_line('Value of i_clev_tbl_in.countBefore Insert '||TO_CHAR(i_clev_tbl_in.count));
3675
3676 IF i_clev_tbl_in.count > 0 THEN
3677 Insert_Into_Klines( p_clev_tbl_in => i_clev_tbl_in,
3678 p_clet_tbl_in => l_clet_tbl_in,
3679 x_return_Status => l_return_status);
3680 END IF;
3681
3682 IF i_ctzv_tbl_in.COUNT > 0 THEN
3683 i := i_ctzv_tbl_in.FIRST; K:=0;
3684 while i is not null LOOP
3685 k:=k+1;
3686
3687 IN_ID(k) := i_ctzv_tbl_in(i).ID;
3688 IN_DNZ_CHR_ID(k) := i_ctzv_tbl_in(i).DNZ_CHR_ID;
3689 IN_CLE_ID(k) := i_ctzv_tbl_in(i).CLE_ID;
3690 IN_DEFAULT_YN(k) := i_ctzv_tbl_in(i).DEFAULT_YN;
3691 IN_TIMEZONE_ID(k) := i_ctzv_tbl_in(i).TIMEZONE_ID;
3692 IN_SECURITY_GROUP_ID(k) := i_ctzv_tbl_in(i).SECURITY_GROUP_ID;
3693 IN_PROGRAM_APPLICATION_ID(k) := i_ctzv_tbl_in(i).PROGRAM_APPLICATION_ID;
3694 IN_PROGRAM_ID(k) := i_ctzv_tbl_in(i).PROGRAM_ID;
3695 IN_PROGRAM_UPDATE_DATE(k) := i_ctzv_tbl_in(i).PROGRAM_UPDATE_DATE;
3696 IN_REQUEST_ID(k) := i_ctzv_tbl_in(i).REQUEST_ID;
3697 IN_CREATED_BY(k) := i_ctzv_tbl_in(i).CREATED_BY;
3698 IN_CREATION_DATE(k) := i_ctzv_tbl_in(i).CREATION_DATE;
3699 IN_LAST_UPDATED_BY(k) := i_ctzv_tbl_in(i).LAST_UPDATED_BY;
3700 IN_LAST_UPDATE_DATE(k) := i_ctzv_tbl_in(i).LAST_UPDATE_DATE;
3701 IN_LAST_UPDATE_LOGIN(k) := i_ctzv_tbl_in(i).LAST_UPDATE_LOGIN;
3702 IN_OBJECT_VERSION_NUMBER(k) := i_ctzv_tbl_in(i).OBJECT_VERSION_NUMBER;
3703 IN_MAJOR_VERSION(k) := i_ctzv_tbl_in(i).MAJOR_VERSION;
3704
3705 i:=i_ctzv_tbl_in.next(i);
3706
3707 END LOOP;
3708
3709 l_tabsize := i_ctzv_tbl_in.COUNT;
3710 l_status := 'Before Insert';
3711
3712 FORALL I IN 1 .. l_tabsize
3713
3714 INSERT INTO OKS_COVERAGE_TIMEZONES_H(
3715 ID,
3716 DNZ_CHR_ID,
3717 CLE_ID,
3718 DEFAULT_YN,
3719 TIMEZONE_ID,
3720 SECURITY_GROUP_ID,
3721 PROGRAM_APPLICATION_ID,
3722 PROGRAM_ID,
3723 PROGRAM_UPDATE_DATE,
3724 REQUEST_ID,
3725 CREATED_BY,
3726 CREATION_DATE,
3727 LAST_UPDATED_BY,
3728 LAST_UPDATE_DATE,
3729 LAST_UPDATE_LOGIN,
3730 OBJECT_VERSION_NUMBER,
3731 MAJOR_VERSION)
3732 VALUES
3733 (IN_ID(i),
3734 IN_DNZ_CHR_ID(i),
3735 IN_CLE_ID(i),
3736 IN_DEFAULT_YN(i),
3737 IN_TIMEZONE_ID(i),
3738 IN_SECURITY_GROUP_ID(i),
3739 IN_PROGRAM_APPLICATION_ID(i),
3740 IN_PROGRAM_ID(i),
3741 IN_PROGRAM_UPDATE_DATE(i),
3742 IN_REQUEST_ID(i),
3743 IN_CREATED_BY(i),
3744 IN_CREATION_DATE(i),
3745 IN_LAST_UPDATED_BY(i),
3746 IN_LAST_UPDATE_DATE(i),
3747 IN_LAST_UPDATE_LOGIN(i),
3748 IN_OBJECT_VERSION_NUMBER(i),
3749 IN_MAJOR_VERSION(i));
3750
3751 END IF;
3752
3753
3754 i_ctzv_tbl_in.delete;
3755 i_clev_tbl_in.delete;
3756
3757 IF l_return_status = 'S' THEN
3758 x_return_status := 'S';
3759 ELSE
3760 RAISE G_EXCEPTION_HALT;
3761 END IF;
3762
3763
3764 EXCEPTION
3765
3766
3767 WHEN EXCEPTIONHALT_VALIDATION THEN
3768 -- -- dbms_output.put_line('SQLERRM ---->'||SQLERRM);
3769 ROLLBACK;
3770 i_ctzv_tbl_in.delete;
3771 i_clev_tbl_in.delete;
3772 i_cvtv_tbl_in.delete;
3773
3774
3775 x_return_status := OKC_API.G_RET_STS_ERROR;
3776 OKC_API.SET_MESSAGE
3777 ( p_app_name => G_APP_NAME,
3778 p_msg_name => G_UNEXPECTED_ERROR,
3779 p_token1 => G_SQLCODE_TOKEN,
3780 p_token1_value => SQLCODE,
3781 p_token2 => G_SQLERRM_TOKEN,
3782 p_token2_value => SQLERRM
3783 );
3784 x_return_status := 'E';
3785 WHEN G_EXCEPTION_HALT THEN
3786
3787 ROLLBACK;
3788 i_ctzv_tbl_in.delete;
3789 i_clev_tbl_in.delete;
3790 i_cvtv_tbl_in.delete;
3791
3792 x_return_status := OKC_API.G_RET_STS_ERROR;
3793 OKC_API.SET_MESSAGE
3794 ( p_app_name => G_APP_NAME,
3795 p_msg_name => G_UNEXPECTED_ERROR,
3796 p_token1 => G_SQLCODE_TOKEN,
3797 p_token1_value => SQLCODE,
3798 p_token2 => G_SQLERRM_TOKEN,
3799 p_token2_value => SQLERRM
3800 );
3801 x_return_status := 'E';
3802 WHEN Others THEN
3803
3804 ROLLBACK;
3805 i_ctzv_tbl_in.delete;
3806 i_clev_tbl_in.delete;
3807 i_cvtv_tbl_in.delete;
3808
3809 x_return_status := OKC_API.G_RET_STS_ERROR;
3810 OKC_API.SET_MESSAGE
3811 ( p_app_name => G_APP_NAME,
3812 p_msg_name => G_UNEXPECTED_ERROR,
3813 p_token1 => G_SQLCODE_TOKEN,
3814 p_token1_value => SQLCODE,
3815 p_token2 => G_SQLERRM_TOKEN,
3816 p_token2_value => SQLERRM
3817 );
3818 x_return_status := 'E';
3819 END;
3820
3821 EXIT WHEN Get_Buss_Process_Cur%NOTFOUND;
3822
3823 END LOOP;
3824
3825 CLOSE Get_Buss_Process_Cur;
3826
3827 EXCEPTION
3828 WHEN Others THEN
3829 x_return_status := OKC_API.G_RET_STS_ERROR;
3830 OKC_API.SET_MESSAGE
3831 ( p_app_name => G_APP_NAME,
3832 p_msg_name => G_UNEXPECTED_ERROR,
3833 p_token1 => G_SQLCODE_TOKEN,
3834 p_token1_value => SQLCODE,
3835 p_token2 => G_SQLERRM_TOKEN,
3836 p_token2_value => SQLERRM
3837 );
3838 x_return_status := 'E';
3839 END Buss_Proc_History_migration;
3840
3841
3842
3843
3844 PROCEDURE COV_TIMES_History_MIGRATION( p_start_rowid IN ROWID,
3845 p_end_rowid IN ROWID,
3846 x_return_status OUT NOCOPY VARCHAR2,
3847 x_message_data OUT NOCOPY VARCHAR2)IS
3848
3849
3850
3851 CURSOR Csr_Get_Coverage_times (l_start_rowid IN ROWID ,l_end_rowid IN ROWID ) IS
3852 SELECT CTZ.ID TimeZone_ID,
3853 CTZ.DNZ_CHR_ID DNZ_CHR_ID,
3854 Times.ID Times_Id,
3855 TIMES.ROWID TIMES_ROW_ID,
3856 TIMES.TVE_ID_STARTED TVE_ID_STARTED,
3857 TIMES.TVE_ID_ENDED TVE_ID_ENDED,
3858 Times.Created_By Times_Created_By,
3859 Times.Last_Updated_By Times_Last_Updated_By,
3860 Times.Last_Update_Date Times_Last_Update_Date,
3861 Times.Last_Update_Login Times_Last_Update_Login,
3862 Times.Attribute15 Times_Attribute15,
3863 Times.object_version_number Times_object_version_number,
3864 Times.major_version Times_major_version
3865 FROM OKS_COVERAGE_TIMEZONES CTZ,
3866 OKC_RULE_GROUPS_BH RGP,
3867 OKC_RULES_BH RUL,
3868 OKC_COVER_TIMES_H CVT,
3869 OKC_TIMEVALUES_BH TIMES
3870 WHERE CTZ.Cle_Id = RGP.CLE_ID
3871 AND CTZ.DNZ_CHR_ID = RGP.DNZ_CHR_ID
3872 AND RGP.ID = RUL.RGP_ID
3873 AND RGP.DNZ_CHR_ID = RUL.DNZ_CHR_ID
3874 AND RUL.RULE_INFORMATION_CATEGORY = 'CVR'
3875 AND RUL.ID = CVT.RUL_ID
3876 AND CVT.TVE_ID = TIMES.ID
3877 --AND times.id = 304783990308709311929711428529893238167
3878 AND RGP.MAJOR_VERSION = RUL.MAJOR_VERSION
3879 AND RUL.MAJOR_VERSION = CVT.MAJOR_VERSION
3880 AND CVT.MAJOR_VERSION = TIMES.MAJOR_VERSION
3881 AND Times.rowid BETWEEN l_start_rowid and l_end_rowid
3882 AND NOT EXISTS (Select COV_TZE_LINE_ID from OKS_COVERAGE_TIMES_H where COV_TZE_LINE_ID= CTZ.ID);
3883
3884
3885 CURSOR get_Coverage_time_Cur (l_tve_id IN NUMBER) IS
3886 SELECT tve_type,day_of_week,hour,minute
3887 FROM okc_timevalues_BH
3888 WHERE ID = l_tve_id;
3889
3890 TYPE Vc420_Tbl_Type IS VARRAY(1000) OF VARCHAR2(420);
3891 TYPE Num_Tbl_Type IS VARRAY(1000) OF NUMBER;
3892 TYPE RowId_Tbl_Type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
3893 TYPE Date_Tbl_Type IS VARRAY(1000) OF DATE;
3894
3895 Times_Id_tbl Num_Tbl_Type;
3896 TIMES_ROW_ID RowId_Tbl_Type;
3897 TimeZone_ID_TBL Num_Tbl_Type;
3898 TVE_ID_STARTED_TBL Num_Tbl_Type;
3899 TVE_ID_ENDED_TBL Num_Tbl_Type;
3900 DNZ_CHR_ID_TBL Num_Tbl_Type;
3901 Times_Attribute15_TBL Vc420_Tbl_Type;
3902 TIMES_Created_By_TBL Num_Tbl_Type;
3903 TIMES_Last_Updated_By_TBL Num_Tbl_Type;
3904 TIMES_Last_Update_Date_TBL Date_Tbl_Type;
3905 TIMES_Last_Update_Login_TBL Num_Tbl_Type;
3906 Times_obj_version_number_TBL Num_Tbl_Type;
3907 Times_major_version_TBL Num_Tbl_Type;
3908
3909 tablename1 VARCHAR2(1000);
3910 l_return_status VARCHAR2(1) :=OKC_API.G_RET_STS_SUCCESS;
3911 l_msg_count NUMBER;
3912
3913 l_start_rowid ROWID := p_start_rowid;
3914 l_end_rowid ROWID := p_end_rowid;
3915 l_msg_data VARCHAR2(1000);
3916 l_msg_index_out NUMBER;
3917 l_message VARCHAR2(2400);
3918 x_msg_count NUMBER := 0;
3919 x_msg_data VArchar2(1000);
3920
3921 G_EXCEPTION_HALT EXCEPTION;
3922 l_status VARCHAR2(100) := NULL;
3923 /************************************************************/
3924
3925
3926
3927 IN_ID OKC_DATATYPES.NumberTabTyp;
3928 IN_DNZ_CHR_ID OKC_DATATYPES.NumberTabTyp;
3929 IN_COV_TZE_LINE_ID OKC_DATATYPES.NumberTabTyp;
3930 IN_START_HOUR OKC_DATATYPES.NumberTabTyp;
3931 IN_START_MINUTE OKC_DATATYPES.NumberTabTyp;
3932 IN_END_HOUR OKC_DATATYPES.NumberTabTyp;
3933 IN_END_MINUTE OKC_DATATYPES.NumberTabTyp;
3934 IN_MONDAY_YN OKC_DATATYPES.VAR3TabTyp;
3935 IN_TUESDAY_YN OKC_DATATYPES.VAR3TabTyp;
3936 IN_WEDNESDAY_YN OKC_DATATYPES.VAR3TabTyp;
3937 IN_THURSDAY_YN OKC_DATATYPES.VAR3TabTyp;
3938 IN_FRIDAY_YN OKC_DATATYPES.VAR3TabTyp;
3939 IN_SATURDAY_YN OKC_DATATYPES.VAR3TabTyp;
3940 IN_SUNDAY_YN OKC_DATATYPES.VAR3TabTyp;
3941 IN_SECURITY_GROUP_ID OKC_DATATYPES.NumberTabTyp;
3942 IN_PROGRAM_APPLICATION_ID OKC_DATATYPES.NumberTabTyp;
3943 IN_PROGRAM_ID OKC_DATATYPES.NumberTabTyp;
3944 IN_PROGRAM_UPDATE_DATE OKC_DATATYPES.DateTabTyp;
3945 IN_REQUEST_ID OKC_DATATYPES.NumberTabTyp;
3946 IN_CREATED_BY OKC_DATATYPES.NumberTabTyp;
3947 IN_CREATION_DATE OKC_DATATYPES.DateTabTyp;
3948 IN_LAST_UPDATED_BY OKC_DATATYPES.NumberTabTyp;
3949 IN_LAST_UPDATE_DATE OKC_DATATYPES.DateTabTyp;
3950 IN_LAST_UPDATE_LOGIN OKC_DATATYPES.NumberTabTyp;
3951 IN_OBJECT_VERSION_NUMBER OKC_DATATYPES.NumberTabTyp;
3952 IN_MAJOR_VERSION OKC_DATATYPES.NumberTabTyp;
3953
3954 /************************************************************/
3955
3956
3957
3958
3959 BEGIN
3960 G_APP_NAME := 'COVERAGE_TIMES_HISTORY_MIGRATION';
3961
3962 OPEN Csr_Get_Coverage_times (l_start_rowid,l_end_rowid);
3963 LOOP
3964 BEGIN
3965 l_cvt_rec := 0;
3966 i_cvtv_tbl_in.DELETE;
3967 l_status := 'Before fetch';
3968
3969 FETCH Csr_Get_Coverage_times BULK COLLECT INTO
3970 TimeZone_ID_Tbl,
3971 DNZ_CHR_ID_TBL,
3972 Times_Id_tbl,
3973 TIMES_ROW_ID,
3974 TVE_ID_STARTED_Tbl,
3975 TVE_ID_ENDED_Tbl,
3976 TIMES_Created_By_TBL,
3977 TIMES_Last_Updated_By_TBL,
3978 TIMES_Last_Update_Date_TBL,
3979 TIMES_Last_Update_Login_TBL,
3980 Times_Attribute15_TBL,
3981 Times_obj_version_number_tbl,
3982 Times_major_version_TBL
3983 LIMIT 1000;
3984
3985 -- -- dbms_output.put_line('Value of TimeZone_ID_Tbl.COUNT='||TO_CHAR(TimeZone_ID_Tbl.COUNT));
3986
3987 l_status := 'In Fetch';
3988
3989 IF TimeZone_ID_Tbl.COUNT > 0 THEN
3990 FOR I IN TimeZone_ID_Tbl.FIRST .. TimeZone_ID_Tbl.LAST LOOP
3991
3992 l_cvt_rec := l_cvt_rec + 1;
3993
3994
3995 i_cvtv_tbl_in(l_cvt_rec).ID := Times_Id_tbl(i);
3996 --okc_p_util.raw_to_number(sys_guid());
3997
3998 i_cvtv_tbl_in(l_cvt_rec).COV_TZE_LINE_ID := TimeZone_ID_Tbl(i);
3999 i_cvtv_tbl_in(l_cvt_rec).DNZ_CHR_ID := DNZ_CHR_ID_TBL(i);
4000
4001 i_cvtv_tbl_in(l_cvt_rec).Created_By := TIMES_Created_By_TBL(i);
4002 i_cvtv_tbl_in(l_cvt_rec).Last_Updated_By := TIMES_Last_Updated_By_TBL(i);
4003 i_cvtv_tbl_in(l_cvt_rec).Last_Update_Date := TIMES_Last_Update_Date_TBL(i);
4004 i_cvtv_tbl_in(l_cvt_rec).Last_Update_Login := TIMES_Last_Update_Login_TBL(i);
4005 i_cvtv_tbl_in(l_cvt_rec).object_version_number := Times_obj_version_number_tbl(i);
4006 i_cvtv_tbl_in(l_cvt_rec).major_version := Times_major_version_TBL(i);
4007 i_cvtv_tbl_in(l_cvt_rec).SECURITY_GROUP_ID := NULL;
4008 i_cvtv_tbl_in(l_cvt_rec).PROGRAM_APPLICATION_ID:= NULL;
4009 i_cvtv_tbl_in(l_cvt_rec).PROGRAM_ID:= NULL;
4010 i_cvtv_tbl_in(l_cvt_rec).REQUEST_ID:= NULL;
4011
4012 FOR get_Coverage_time_REC IN get_Coverage_time_Cur(TVE_ID_STARTED_TBL(i)) LOOP
4013
4014 IF get_Coverage_time_REC.day_of_week = 'SUN' THEN
4015
4016 i_cvtv_tbl_in(l_cvt_rec).SUNDAY_YN := 'Y';
4017
4018 ELSIF get_Coverage_time_REC.day_of_week = 'MON' THEN
4019
4020 i_cvtv_tbl_in(l_cvt_rec).MONDAY_YN := 'Y';
4021
4022 ELSIF get_Coverage_time_REC.day_of_week = 'TUE' THEN
4023 i_cvtv_tbl_in(l_cvt_rec).TUESDAY_YN := 'Y';
4024
4025 ELSIF get_Coverage_time_REC.day_of_week = 'WED' THEN
4026 i_cvtv_tbl_in(l_cvt_rec).WEDNESDAY_YN := 'Y';
4027
4028 ELSIF get_Coverage_time_REC.day_of_week = 'THU' THEN
4029 i_cvtv_tbl_in(l_cvt_rec).THURSDAY_YN := 'Y';
4030
4031 ELSIF get_Coverage_time_REC.day_of_week = 'FRI' THEN
4032 i_cvtv_tbl_in(l_cvt_rec).FRIDAY_YN := 'Y';
4033
4034 ELSIF get_Coverage_time_REC.day_of_week = 'SAT' THEN
4035 i_cvtv_tbl_in(l_cvt_rec).SATURDAY_YN := 'Y';
4036
4037 END IF;
4038
4039 i_cvtv_tbl_in(l_cvt_rec).START_HOUR := get_Coverage_time_REC.HOUR;
4040 i_cvtv_tbl_in(l_cvt_rec).START_MINUTE := get_Coverage_time_REC.MINUTE;
4041
4042 END LOOP;
4043
4044 FOR get_Coverage_time_REC IN get_Coverage_time_Cur(TVE_ID_ENDED_Tbl(i)) LOOP
4045
4046 i_cvtv_tbl_in(l_cvt_rec).END_HOUR := get_Coverage_time_REC.HOUR;
4047 i_cvtv_tbl_in(l_cvt_rec).END_MINUTE := get_Coverage_time_REC.MINUTE;
4048
4049 END LOOP;
4050
4051
4052 END LOOP;
4053 END IF;
4054
4055 tablename1 := 'oks_coverage_times';
4056
4057 -- dbms_output.put_line('Value of i_cvtv_tbl_in.COUNT='||TO_CHAR(i_cvtv_tbl_in.COUNT));
4058
4059 IF i_cvtv_tbl_in.COUNT > 0 THEN
4060
4061 i := i_cvtv_tbl_in.FIRST; K:=0;
4062 while i is not null LOOP
4063 k:=k+1;
4064
4065 IN_ID(k) := i_cvtv_tbl_in(i).ID;
4066 IN_DNZ_CHR_ID(k) := i_cvtv_tbl_in(i).DNZ_CHR_ID;
4067 IN_COV_TZE_LINE_ID(k) := i_cvtv_tbl_in(i).COV_TZE_LINE_ID;
4068 IN_START_HOUR(k) := i_cvtv_tbl_in(i).START_HOUR;
4069 IN_START_MINUTE(k) := i_cvtv_tbl_in(i).START_MINUTE;
4070 IN_END_HOUR(k) := i_cvtv_tbl_in(i).END_HOUR;
4071 IN_END_MINUTE(k) := i_cvtv_tbl_in(i).END_MINUTE;
4072 IN_MONDAY_YN(k) := i_cvtv_tbl_in(i).MONDAY_YN;
4073 IN_TUESDAY_YN(k) := i_cvtv_tbl_in(i).TUESDAY_YN;
4074 IN_WEDNESDAY_YN(k) := i_cvtv_tbl_in(i).WEDNESDAY_YN;
4075 IN_THURSDAY_YN(k) := i_cvtv_tbl_in(i).THURSDAY_YN;
4076 IN_FRIDAY_YN(k) := i_cvtv_tbl_in(i).FRIDAY_YN;
4077 IN_SATURDAY_YN(k) := i_cvtv_tbl_in(i).SATURDAY_YN;
4078 IN_SUNDAY_YN(k) := i_cvtv_tbl_in(i).SUNDAY_YN;
4079 IN_SECURITY_GROUP_ID(k) := i_cvtv_tbl_in(i).SECURITY_GROUP_ID;
4080 IN_PROGRAM_APPLICATION_ID(k) := i_cvtv_tbl_in(i).PROGRAM_APPLICATION_ID;
4081 IN_PROGRAM_ID(k) := i_cvtv_tbl_in(i).PROGRAM_ID;
4082 IN_PROGRAM_UPDATE_DATE(k) := i_cvtv_tbl_in(i).PROGRAM_UPDATE_DATE;
4083 IN_REQUEST_ID(k) := i_cvtv_tbl_in(i).REQUEST_ID;
4084 IN_CREATED_BY(k) := i_cvtv_tbl_in(i).CREATED_BY;
4085 IN_CREATION_DATE(k) := i_cvtv_tbl_in(i).CREATION_DATE;
4086 IN_LAST_UPDATED_BY(k) := i_cvtv_tbl_in(i).LAST_UPDATED_BY;
4087 IN_LAST_UPDATE_DATE(k) := i_cvtv_tbl_in(i).LAST_UPDATE_DATE;
4088 IN_LAST_UPDATE_LOGIN(k) := i_cvtv_tbl_in(i).LAST_UPDATE_LOGIN;
4089 IN_OBJECT_VERSION_NUMBER(k) := i_cvtv_tbl_in(i).OBJECT_VERSION_NUMBER;
4090 IN_MAJOR_VERSION(k) := i_cvtv_tbl_in(i).MAJOR_VERSION;
4091
4092 i:=i_cvtv_tbl_in.next(i);
4093
4094 END LOOP;
4095
4096 l_tabsize := i_cvtv_tbl_in.COUNT;
4097 l_status := 'Before Insert';
4098
4099 FORALL I IN 1 .. l_tabsize
4100
4101 INSERT INTO OKS_COVERAGE_TIMES_H(
4102 ID,
4103 DNZ_CHR_ID,
4104 COV_TZE_LINE_ID,
4105 START_HOUR,
4106 START_MINUTE,
4107 END_HOUR,
4108 END_MINUTE,
4109 MONDAY_YN,
4110 TUESDAY_YN,
4111 WEDNESDAY_YN,
4112 THURSDAY_YN,
4113 FRIDAY_YN,
4114 SATURDAY_YN,
4115 SUNDAY_YN,
4116 SECURITY_GROUP_ID,
4117 PROGRAM_APPLICATION_ID,
4118 PROGRAM_ID,
4119 PROGRAM_UPDATE_DATE,
4120 REQUEST_ID,
4121 CREATED_BY,
4122 CREATION_DATE,
4123 LAST_UPDATED_BY,
4124 LAST_UPDATE_DATE,
4125 LAST_UPDATE_LOGIN,
4126 OBJECT_VERSION_NUMBER,
4127 MAJOR_VERSION)VALUES
4128 (
4129 IN_ID (i),
4130 IN_DNZ_CHR_ID (i),
4131 IN_COV_TZE_LINE_ID (i),
4132 IN_START_HOUR (i),
4133 IN_START_MINUTE (i),
4134 IN_END_HOUR (i),
4135 IN_END_MINUTE (i),
4136 IN_MONDAY_YN (i),
4137 IN_TUESDAY_YN (i),
4138 IN_WEDNESDAY_YN (i),
4139 IN_THURSDAY_YN (i),
4140 IN_FRIDAY_YN (i),
4141 IN_SATURDAY_YN (i),
4142 IN_SUNDAY_YN (i),
4143 IN_SECURITY_GROUP_ID (i),
4144 IN_PROGRAM_APPLICATION_ID (i),
4145 IN_PROGRAM_ID (i),
4146 IN_PROGRAM_UPDATE_DATE (i),
4147 IN_REQUEST_ID (i),
4148 IN_CREATED_BY (i),
4149 IN_CREATION_DATE (i),
4150 IN_LAST_UPDATED_BY (i),
4151 IN_LAST_UPDATE_DATE (i),
4152 IN_LAST_UPDATE_LOGIN (i),
4153 IN_OBJECT_VERSION_NUMBER (i),
4154 IN_MAJOR_VERSION (i));
4155
4156
4157
4158 x_return_status := 'S';
4159
4160 END IF;
4161
4162
4163 EXIT WHEN Csr_Get_Coverage_times%NOTFOUND;
4164
4165 EXCEPTION
4166 WHEN G_EXCEPTION_HALT THEN
4167 ROLLBACK;
4168 x_return_status := OKC_API.G_RET_STS_ERROR;
4169 OKC_API.SET_MESSAGE
4170 ( p_app_name => G_APP_NAME,
4171 p_msg_name => G_UNEXPECTED_ERROR,
4172 p_token1 => G_SQLCODE_TOKEN,
4173 p_token1_value => SQLCODE,
4174 p_token2 => G_SQLERRM_TOKEN,
4175 p_token2_value => SQLERRM
4176 );
4177
4178 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4179 G_APP_NAME,
4180 G_PKG_NAME,
4181 'OKC_API.G_RET_STS_UNEXP_ERROR',
4182 x_msg_count,
4183 x_msg_data,
4184 '_PVT'
4185 );
4186
4187 IF x_msg_count > 0 THEN
4188 FOR i in 1..x_msg_count LOOP
4189 fnd_msg_pub.get (p_msg_index => -1,
4190 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
4191 p_data => l_msg_data,
4192 p_msg_index_out => l_msg_index_out);
4193 l_message := l_message||' ; '||l_msg_data;
4194
4195 END LOOP;
4196 END IF;
4197
4198 x_message_data := l_message;
4199 x_return_status := 'E';
4200 WHEN Others THEN
4201 ROLLBACK;
4202 x_return_status := OKC_API.G_RET_STS_ERROR;
4203 OKC_API.SET_MESSAGE
4204 ( p_app_name => G_APP_NAME,
4205 p_msg_name => G_UNEXPECTED_ERROR,
4206 p_token1 => G_SQLCODE_TOKEN,
4207 p_token1_value => SQLCODE,
4208 p_token2 => G_SQLERRM_TOKEN,
4209 p_token2_value => SQLERRM
4210 );
4211
4212 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4213 G_APP_NAME,
4214 G_PKG_NAME,
4215 'OKC_API.G_RET_STS_UNEXP_ERROR',
4216 x_msg_count,
4217 x_msg_data,
4218 '_PVT'
4219 );
4220
4221 IF x_msg_count > 0 THEN
4222 FOR i in 1..x_msg_count LOOP
4223 fnd_msg_pub.get (p_msg_index => -1,
4224 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
4225 p_data => l_msg_data,
4226 p_msg_index_out => l_msg_index_out);
4227 l_message := l_message||' ; '||l_msg_data;
4228
4229 END LOOP;
4230 END IF;
4231
4232 x_message_data := l_message;
4233 x_return_status := 'E';
4234
4235 END;
4236 EXIT WHEN Csr_Get_Coverage_times%NOTFOUND;
4237 END LOOP;
4238 CLOSE Csr_Get_Coverage_times;
4239 EXCEPTION
4240 WHEN Others THEN
4241 Raise;
4242 x_return_status := OKC_API.G_RET_STS_ERROR;
4243 OKC_API.SET_MESSAGE
4244 ( p_app_name => G_APP_NAME,
4245 p_msg_name => G_UNEXPECTED_ERROR,
4246 p_token1 => G_SQLCODE_TOKEN,
4247 p_token1_value => SQLCODE,
4248 p_token2 => G_SQLERRM_TOKEN,
4249 p_token2_value => SQLERRM
4250 );
4251
4252 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4253 G_APP_NAME,
4254 G_PKG_NAME,
4255 'OKC_API.G_RET_STS_UNEXP_ERROR',
4256 x_msg_count,
4257 x_msg_data,
4258 '_PVT'
4259 );
4260
4261 IF x_msg_count > 0 THEN
4262 FOR i in 1..x_msg_count LOOP
4263 fnd_msg_pub.get (p_msg_index => -1,
4264 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
4265 p_data => l_msg_data,
4266 p_msg_index_out => l_msg_index_out);
4267 l_message := l_message||' ; '||l_msg_data;
4268
4269 END LOOP;
4270 END IF;
4271 x_message_data := l_message;
4272 x_return_status := 'E';
4273
4274
4275
4276 END COV_TIMES_History_MIGRATION;
4277
4278 PROCEDURE Reaction_Time_Hist_migration( p_start_rowid IN ROWID,
4279 p_end_rowid IN ROWID,
4280 x_return_status OUT NOCOPY VARCHAR2,
4281 x_message_data OUT NOCOPY VARCHAR2) IS
4282
4283
4284 CURSOR Csr_Get_Reaction_Times (l_start_rowid IN ROWID ,l_end_rowid IN ROWID ) IS
4285 SELECT
4286 LINE.ID LINE_ID,
4287 Line.Created_By Line_Created_By,
4288 Line.Creation_Date Line_Creation_Date,
4289 Line.Last_Updated_By Line_Last_Updated_By,
4290 Line.Last_Update_Date Line_Last_Update_Date,
4291 Line.Last_Update_Login Line_Last_Update_Login,
4292 Rul.ROWID RUL_ROW_ID,
4293 Rul.ID Rul_Id,
4294 RGP.ID LINE_RGP_ID,
4295 LINE.LSE_ID LINE_LSE_ID,
4296 LINE.DNZ_CHR_ID LINE_DNZ_CHR_ID,
4297 OBJECT1_ID1 LINE_OBJECT1_ID1,
4298 OBJECT2_ID1 LINE_OBJECT2_ID1,
4299 RULE_INFORMATION1 COV_RULE_INFO1,
4300 RULE_INFORMATION2 COV_RULE_INFO2,
4301 RULE_INFORMATION3 COV_RULE_INFO3,
4302 RULE_INFORMATION4 COV_RULE_INFO4,
4303 RULE_INFORMATION5 COV_RULE_INFO5,
4304 RULE_INFORMATION6 COV_RULE_INFO6,
4305 RULE_INFORMATION7 COV_RULE_INFO7,
4306 RULE_INFORMATION8 COV_RULE_INFO8,
4307 RULE_INFORMATION9 COV_RULE_INFO9,
4308 RULE_INFORMATION10 COV_RULE_INFO10,
4309 RULE_INFORMATION11 COV_RULE_INFO11,
4310 RULE_INFORMATION12 COV_RULE_INFO12,
4311 RULE_INFORMATION13 COV_RULE_INFO13,
4312 RULE_INFORMATION14 COV_RULE_INFO14,
4313 RULE_INFORMATION15 COV_RULE_INFO15,
4314 RULE_INFORMATION_CATEGORY COV_RULE_INFO,
4315 RUL.OBJECT_VERSION_NUMBER COV_OBJ_VER_NUMBER,
4316 KINE.ID k_line_id,
4317 Rul.major_version Rul_major_version
4318 FROM
4319 OKC_RULE_GROUPS_BH RGP,
4320 OKC_RULES_BH RUL,
4321 OKC_K_LINES_BH LINE,
4322 OKS_K_LINES_B KINE
4323 WHERE LINE.ID = KINE.CLE_ID
4324 AND LINE.DNZ_CHR_ID = KINE.DNZ_CHR_ID
4325 AND LINE.ID = RGP.CLE_ID
4326 AND RGP.ID = RUL.RGP_ID
4327 AND LINE.LSE_ID IN (4,17,22)
4328 AND RUL.RULE_INFORMATION_CATEGORY IN ('RCN','RSN')
4329 AND RGP.MAJOR_VERSION = RUL.MAJOR_VERSION
4330 AND LINE.MAJOR_VERSION = RGP.MAJOR_VERSION
4331 -- AND RUL.RULE_INFORMATION15 IS NULL
4332 AND Rul.rowid BETWEEN l_start_rowid and l_end_rowid
4333 AND NOT EXISTS (SELECT CLE_ID FROM OKS_K_LINES_BH WHERE CLE_ID = LINE.ID)
4334 ORDER BY LINE.ID;
4335
4336 CURSOR Get_Rule_TlH (P_ID IN NUMBER) IS
4337 SELECT ID,
4338 MAJOR_VERSION,
4339 LANGUAGE,
4340 SOURCE_LANG,
4341 SFWT_FLAG,
4342 COMMENTS,
4343 TEXT,
4344 CREATED_BY,
4345 CREATION_DATE,
4346 LAST_UPDATED_BY,
4347 LAST_UPDATE_DATE,
4348 LAST_UPDATE_LOGIN,
4349 SECURITY_GROUP_ID
4350 FROM OKC_RULES_TLH
4351 WHERE ID = P_ID;
4352
4353 CURSOR Get_Action_Time_Types (p_Cle_Id IN NUMBER)Is
4354 Select ID
4355 FROM OKS_ACTION_TIME_TYPES
4356 WHERE CLE_ID = p_Cle_ID;
4357
4358 CURSOR Get_Action_Type_Id (p_Id IN NUMBER) IS
4359 SELECT CLE_ID,object_version_number --CLE_ID
4360 FROM OKS_K_LINES_B
4361 WHERE ID = P_ID;
4362
4363
4364 TYPE RowId_Tbl_Type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
4365 TYPE Num_Tbl_Type IS VARRAY(1000) OF NUMBER;
4366 TYPE Date_Tbl_Type IS VARRAY(1000) OF DATE;
4367 TYPE Vc15_Tbl_Type IS VARRAY(1000) OF VARCHAR2(15);
4368 TYPE Vc20_Tbl_Type IS VARRAY(1000) OF VARCHAR2(20);
4369 TYPE Vc150_Tbl_Type IS VARRAY(1000) OF VARCHAR2(150);
4370 TYPE Vc30_Tbl_Type IS VARRAY(1000) OF VARCHAR2(30);
4371 TYPE Vc1_Tbl_Type IS VARRAY(1000) OF VARCHAR2(1);
4372 TYPE Vc3_Tbl_Type IS VARRAY(1000) OF VARCHAR2(3);
4373 TYPE Vc2000_Tbl_Type IS VARRAY(1000) OF VARCHAR2(2000);
4374 TYPE Vc50_Tbl_Type IS VARRAY(1000) OF VARCHAR2(50);
4375 TYPE Vc80_Tbl_Type IS VARRAY(1000) OF VARCHAR2(80);
4376 TYPE Vc120_Tbl_Type IS VARRAY(1000) OF VARCHAR2(120);
4377 TYPE Vc600_Tbl_Type IS VARRAY(1000) OF VARCHAR2(600);
4378
4379
4380 LINE_ID_TBL Num_Tbl_Type;
4381 Line_Created_By_TBL Num_Tbl_Type;
4382 Line_Creation_Date_TBL Date_Tbl_Type;
4383 Line_Last_Updated_By_TBL Num_Tbl_Type;
4384 Line_Last_Update_Date_TBL Date_Tbl_Type;
4385 Line_Last_Update_Login_TBL Num_Tbl_Type;
4386 RUL_ROW_ID_TBL RowId_Tbl_Type;
4387 RUL_ID_TBL Num_Tbl_Type;
4388 LINE_RGP_ID_TBL Num_Tbl_Type;
4389 LINE_LSE_ID_TBL Num_Tbl_Type;
4390 LINE_DNZ_CHR_ID_TBL Num_Tbl_Type;
4391 LINE_OBJECT1_ID1_TBL Vc150_Tbl_Type;
4392 LINE_OBJECT2_ID1_TBL Vc150_Tbl_Type;
4393 COV_RULE_INFO1_TBL Vc150_Tbl_Type;
4394 COV_RULE_INFO2_TBL Vc150_Tbl_Type;
4395 COV_RULE_INFO3_TBL Vc150_Tbl_Type;
4396 COV_RULE_INFO4_TBL Vc150_Tbl_Type;
4397 COV_RULE_INFO5_TBL Vc150_Tbl_Type;
4398 COV_RULE_INFO6_TBL Vc150_Tbl_Type;
4399 COV_RULE_INFO7_TBL Vc150_Tbl_Type;
4400 COV_RULE_INFO8_TBL Vc150_Tbl_Type;
4401 COV_RULE_INFO9_TBL Vc150_Tbl_Type;
4402 COV_RULE_INFO10_TBL Vc150_Tbl_Type;
4403 COV_RULE_INFO11_TBL Vc150_Tbl_Type;
4404 COV_RULE_INFO12_TBL Vc150_Tbl_Type;
4405 COV_RULE_INFO13_TBL Vc150_Tbl_Type;
4406 COV_RULE_INFO14_TBL Vc150_Tbl_Type;
4407 COV_RULE_INFO15_TBL Vc150_Tbl_Type;
4408 COV_RULE_INFO_TBL Vc150_Tbl_Type;
4409 COV_OBJ_VER_NUMBER_TBL Num_Tbl_Type;
4410 k_line_id_tbl Num_Tbl_Type;
4411 Rul_major_version_tbl Num_Tbl_Type;
4412
4413
4414
4415
4416 L_OLD_CLE_ID NUMBER := -99999;
4417 L_CLE_ID NUMBER := -99999;
4418 l_cle_ctr NUMBER := 0;
4419 l_return_Status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
4420 x_msg_count NUMBER := 0;
4421 x_msg_data VArchar2(1000);
4422
4423 l_start_rowid ROWID := p_start_rowid;
4424 l_end_rowid ROWID := p_end_rowid;
4425
4426 l_msg_data VARCHAR2(1000);
4427 l_msg_index_out NUMBER;
4428 l_message VARCHAR2(2400);
4429
4430 l_old_line_id NUMBER := -9999;
4431 l_line_id NUMBER;
4432
4433 G_EXCEPTION_HALT EXCEPTION;
4434
4435 BEGIN
4436 G_APP_NAME := 'Reaction_Time_migration';
4437 OPEN Csr_Get_Reaction_Times (l_start_rowid,l_end_rowid);
4438 LOOP
4439 BEGIN
4440 FETCH Csr_Get_Reaction_Times BULK COLLECT INTO
4441 LINE_ID_TBL ,
4442 Line_Created_By_TBL ,
4443 Line_Creation_Date_Tbl ,
4444 Line_Last_Updated_By_TBL ,
4445 Line_Last_Update_Date_TBL ,
4446 Line_Last_Update_Login_TBL ,
4447 RUL_ROW_ID_TBL ,
4448 RUL_ID_TBL ,
4449 LINE_RGP_ID_TBL ,
4450 LINE_LSE_ID_TBL ,
4451 LINE_DNZ_CHR_ID_TBL ,
4452 LINE_OBJECT1_ID1_TBL ,
4453 LINE_OBJECT2_ID1_TBL ,
4454 COV_RULE_INFO1_TBL ,
4455 COV_RULE_INFO2_TBL ,
4456 COV_RULE_INFO3_TBL ,
4457 COV_RULE_INFO4_TBL ,
4458 COV_RULE_INFO5_TBL ,
4459 COV_RULE_INFO6_TBL ,
4460 COV_RULE_INFO7_TBL ,
4461 COV_RULE_INFO8_TBL ,
4462 COV_RULE_INFO9_TBL ,
4463 COV_RULE_INFO10_TBL ,
4464 COV_RULE_INFO11_TBL ,
4465 COV_RULE_INFO12_TBL ,
4466 COV_RULE_INFO13_TBL ,
4467 COV_RULE_INFO14_TBL ,
4468 COV_RULE_INFO15_TBL ,
4469 COV_RULE_INFO_TBL ,
4470 COV_OBJ_VER_NUMBER_TBL ,
4471 k_line_id_tbl ,
4472 Rul_major_version_tbl
4473 LIMIT 1000;
4474
4475 IF LINE_ID_TBL.COUNT > 0 THEN --LINE_ID_TBL.COUNT > 0
4476
4477 l_act_ctr := 0;
4478 l_cle_ctr := 0;
4479
4480 FOR I IN LINE_ID_TBL.FIRST .. LINE_ID_TBL.LAST LOOP
4481
4482 L_Cle_id := LINE_ID_TBL(i);
4483
4484 IF (L_OLD_CLE_ID <> L_CLE_ID) THEN
4485 l_cle_ctr := l_cle_ctr + 1;
4486
4487 i_clev_tbl_in(l_cle_ctr).Id := k_line_id_tbl(i); --okc_p_util.raw_to_number(sys_guid());
4488 l_line_id := LINE_ID_TBL(i);
4489 i_clev_tbl_in(l_cle_ctr).Created_By := Line_Created_By_TBL(i);
4490 i_clev_tbl_in(l_cle_ctr).Creation_Date := Line_Creation_Date_TBL(i);
4491 i_clev_tbl_in(l_cle_ctr).Last_Updated_By := Line_Last_Updated_By_TBL(i);
4492 i_clev_tbl_in(l_cle_ctr).Last_Update_Date := Line_Last_Update_Date_TBL(i);
4493 i_clev_tbl_in(l_cle_ctr).Last_Update_Login := Line_Last_Update_Login_TBL(i);
4494
4495 i_clev_tbl_in(l_cle_ctr).Cle_Id := LINE_ID_TBL(i);
4496 i_clev_tbl_in(l_cle_ctr).Dnz_Chr_Id := LINE_DNZ_CHR_ID_TBL(i);
4497
4498 i_clev_tbl_in(l_cle_ctr).object_version_number := 1;
4499 i_clev_tbl_in(l_cle_ctr).Sync_Date_Install := 'N';
4500
4501 IF ((COV_RULE_INFO_TBL(i) = 'RCN') OR (COV_RULE_INFO_TBL(i) = 'RSN')) THEN
4502
4503 i_clev_tbl_in(l_cle_ctr).INCIDENT_SEVERITY_ID := LINE_OBJECT1_ID1_TBL(i);
4504 i_clev_tbl_in(l_cle_ctr).PDF_ID := COV_RULE_INFO1_TBL(i);
4505 i_clev_tbl_in(l_cle_ctr).WORK_THRU_YN := COV_RULE_INFO3_TBL(i);
4506 i_clev_tbl_in(l_cle_ctr).REACT_ACTIVE_YN := COV_RULE_INFO4_TBL(i);
4507 i_clev_tbl_in(l_cle_ctr).REACT_TIME_NAME := COV_RULE_INFO2_TBL(i);
4508 i_clev_tbl_in(l_cle_ctr).major_version := Rul_major_version_tbl(i);
4509
4510 END IF;
4511
4512
4513 ------------------------- FOR TLH-----------------------
4514 IF l_line_id <> l_old_line_id THEN
4515 -- dbms_output.put_line('--->Value of Rule_Id_Tbl(l_cle_ctr)='||TO_CHAR(Rul_Id_Tbl(I)));
4516 FOR Get_Rule_TlH_REC IN Get_Rule_TlH(Rul_Id_Tbl(l_cle_ctr)) LOOP
4517 l_clt_ctr := l_clt_ctr + 1;
4518 -- dbms_output.put_line('---------->l_clt_ctr='||TO_CHAR(l_clt_ctr));
4519
4520 l_clet_tbl_in(l_clt_ctr).id := i_clev_tbl_in(l_cle_ctr).id;
4521 l_clet_tbl_in(l_clt_ctr).MAJOR_VERSION := Get_Rule_TlH_REC.MAJOR_VERSION;
4522 l_clet_tbl_in(l_clt_ctr).language := Get_Rule_TlH_REC.language;
4523 l_clet_tbl_in(l_clt_ctr).source_lang := Get_Rule_TlH_REC.source_lang;
4524 l_clet_tbl_in(l_clt_ctr).sfwt_flag := Get_Rule_TlH_REC.sfwt_flag;
4525 l_clet_tbl_in(l_clt_ctr).invoice_text := NULL;-- Get_Rule_TlH_REC.text;
4526 -- l_clet_tbl_in(l_clt_ctr).ib_trx_details := Get_Rule_TlH_REC.ib_trx_details;
4527 -- l_clet_tbl_in(l_clt_ctr).status_text := Get_Rule_TlH_REC.status_text;
4528 -- l_clet_tbl_in(l_clt_ctr).react_time_name := Get_Rule_TlH_REC.react_time_name;
4529 l_clet_tbl_in(l_clt_ctr).created_by := Get_Rule_TlH_REC.created_by;
4530 l_clet_tbl_in(l_clt_ctr).creation_date := Get_Rule_TlH_REC.creation_date;
4531 l_clet_tbl_in(l_clt_ctr).last_updated_by := Get_Rule_TlH_REC.last_updated_by;
4532 l_clet_tbl_in(l_clt_ctr).last_update_date := Get_Rule_TlH_REC.last_update_date;
4533 l_clet_tbl_in(l_clt_ctr).last_update_login := Get_Rule_TlH_REC.last_update_login;
4534
4535 END LOOP;
4536
4537 l_old_line_id := l_line_id;
4538 -- dbms_output.put_line('Value of l_old_line_id='||TO_CHAR(l_old_line_id));
4539 END IF;
4540
4541 END IF; --IF (L_OLD_CLE_ID <> L_CLE_ID) THEN
4542
4543
4544 L_OLD_CLE_ID := L_CLE_ID;
4545
4546
4547 END LOOP;
4548
4549 END IF; ----LINE_ID_TBL.COUNT > 0
4550
4551
4552 IF i_clev_tbl_in.count > 0 THEN
4553 Insert_Into_Klines( p_clev_tbl_in => i_clev_tbl_in,
4554 p_clet_tbl_in => l_clet_tbl_in,
4555 x_return_Status => l_return_status);
4556
4557 FOR I IN i_clev_tbl_in.FIRST .. i_clev_tbl_in.LAST LOOP
4558 -- dbms_output.put_line('11-->Value of i_clev_tbl_in(I).ID='||TO_CHAR(i_clev_tbl_in(I).ID));
4559 For Get_Action_Type_Id_Rec IN Get_Action_Type_Id(i_clev_tbl_in(I).ID) LOOP
4560 FOR Get_Action_Time_Types_Rec IN Get_Action_Time_Types(Get_Action_Type_Id_Rec.CLE_ID) LOOP
4561
4562 l_return_Status := OKS_ACT_PVT.Create_Version(
4563 p_id => Get_Action_Time_Types_Rec.ID,
4564 p_major_version => 1) ;
4565 END LOOP;
4566 END LOOP;
4567 END LOOP;
4568 END IF;
4569
4570 i_clev_tbl_in.DELETE;
4571
4572 IF l_return_status = 'S' THEN
4573 x_return_status := 'S';
4574 x_message_data := NULL;
4575 ELSE
4576 RAISE G_EXCEPTION_HALT;
4577 END IF;
4578
4579 EXCEPTION
4580 WHEN G_EXCEPTION_HALT THEN
4581 ROLLBACK;
4582 i_actv_tbl_in.DELETE;
4583 i_clev_tbl_in.DELETE;
4584
4585 x_return_status := OKC_API.G_RET_STS_ERROR;
4586 OKC_API.SET_MESSAGE
4587 ( p_app_name => G_APP_NAME,
4588 p_msg_name => G_UNEXPECTED_ERROR,
4589 p_token1 => G_SQLCODE_TOKEN,
4590 p_token1_value => SQLCODE,
4591 p_token2 => G_SQLERRM_TOKEN,
4592 p_token2_value => SQLERRM
4593 );
4594
4595 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4596 G_APP_NAME,
4597 G_PKG_NAME,
4598 'OKC_API.G_RET_STS_UNEXP_ERROR',
4599 x_msg_count,
4600 x_msg_data,
4601 '_PVT'
4602 );
4603 IF x_msg_count > 0 THEN
4604 FOR i in 1..x_msg_count LOOP
4605 fnd_msg_pub.get (p_msg_index => -1,
4606 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
4607 p_data => l_msg_data,
4608 p_msg_index_out => l_msg_index_out);
4609 l_message := l_message||' ; '||l_msg_data;
4610
4611 END LOOP;
4612 END IF;
4613 x_message_data := l_message;
4614 x_return_status := 'E';
4615 WHEN Others THEN
4616 ROLLBACK;
4617 i_actv_tbl_in.DELETE;
4618 i_clev_tbl_in.DELETE;
4619
4620 x_return_status := OKC_API.G_RET_STS_ERROR;
4621 OKC_API.SET_MESSAGE
4622 ( p_app_name => G_APP_NAME,
4623 p_msg_name => G_UNEXPECTED_ERROR,
4624 p_token1 => G_SQLCODE_TOKEN,
4625 p_token1_value => SQLCODE,
4626 p_token2 => G_SQLERRM_TOKEN,
4627 p_token2_value => SQLERRM
4628 );
4629
4630 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4631 G_APP_NAME,
4632 G_PKG_NAME,
4633 'OKC_API.G_RET_STS_UNEXP_ERROR',
4634 x_msg_count,
4635 x_msg_data,
4636 '_PVT'
4637 );
4638 IF x_msg_count > 0 THEN
4639 FOR i in 1..x_msg_count LOOP
4640 fnd_msg_pub.get (p_msg_index => -1,
4641 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
4642 p_data => l_msg_data,
4643 p_msg_index_out => l_msg_index_out);
4644 l_message := l_message||' ; '||l_msg_data;
4645
4646 END LOOP;
4647 END IF;
4648 x_message_data := l_message;
4649 x_return_status := 'E';
4650 END;
4651
4652 EXIT WHEN Csr_Get_Reaction_Times%NOTFOUND;
4653
4654 END LOOP;
4655
4656 CLOSE Csr_Get_Reaction_Times;
4657 END Reaction_Time_Hist_migration;
4658
4659
4660 PROCEDURE React_TimeVal_Hist_Migration ( x_return_status OUT NOCOPY VARCHAR2,
4661 x_message_data OUT NOCOPY VARCHAR2) IS
4662
4663
4664 CURSOR Csr_Get_Timevalues IS
4665
4666 SELECT TYP.id Action_Type_ID,
4667 TYP.cle_id Action_Type_Cle_ID ,
4668 TYP.dnz_chr_id Action_Type_Dnz_ID ,
4669 TYP.Created_By Created_By,
4670 TYP.Last_Updated_By Last_Updated_By,
4671 TYP.Last_Update_Date Last_Update_Date,
4672 TYP.Last_Update_Login Last_Update_Login,
4673 RIN.UOM_CODE UOM_CODE,
4674 RIN.DURATION DURATION,
4675 TIM.DAY_OF_WEEK DAY_OF_WEEK,
4676 TIM.TVE_TYPE TVE_TYPE,
4677 Rul.ID RUL_ID,
4678 Rul.major_version Rul_major_version
4679 FROM oks_action_time_types_h TYP,
4680 okc_rule_groups_bh RGP,okc_rules_bh RUL,
4681 okc_timevalues_bh TIM,okc_react_intervals_h RIN
4682 WHERE RGP.CLE_ID = TYP.CLE_ID
4683 AND RGP.DNZ_CHR_ID = TYP.DNZ_CHR_ID
4684 AND RGP.ID = RUL.RGP_ID
4685 AND RGP.DNZ_CHR_ID = RUL.DNZ_CHR_ID
4686 AND RUL.DNZ_CHR_ID = TYP.DNZ_CHR_ID
4687 AND RUL.ID = RIN.RUL_ID
4688 AND rul.RULE_INFORMATION_CATEGORY = typ.ACTION_TYPE_CODE
4689 AND RIN.DNZ_CHR_ID = TYP.DNZ_CHR_ID
4690 AND TIM.ID = RIN.TVE_ID
4691 AND TIM.DNZ_CHR_ID = RIN.DNZ_CHR_ID
4692 AND TIM.DNZ_CHR_ID = RGP.DNZ_CHR_ID
4693 AND TIM.DNZ_CHR_ID = RUL.DNZ_CHR_ID
4694 AND TIM.DNZ_CHR_ID = TYP.DNZ_CHR_ID
4695 AND RGP.major_version = TYP.major_version
4696 AND RGP.major_version = rul.major_version
4697 AND rul.major_version = TYP.major_version
4698 AND RIN.major_version = TYP.major_version
4699 AND RIN.major_version = RUL.major_version
4700 AND TIM.major_version = RIN.major_version
4701 AND NOT EXISTS (Select cle_id FROM OKS_ACTION_TIMES_H WHERE CLE_ID = TYP.cle_id and major_version = typ.major_version)
4702 ORDER BY RUL.ID;
4703
4704
4705 TYPE RowId_Tbl_Type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
4706 TYPE Num_Tbl_Type IS VARRAY(1000) OF NUMBER;
4707
4708 TYPE Vc15_Tbl_Type IS VARRAY(1000) OF VARCHAR2(15);
4709 TYPE Vc20_Tbl_Type IS VARRAY(1000) OF VARCHAR2(20);
4710 TYPE Date_Tbl_Type IS VARRAY(1000) OF DATE;
4711
4712 l_acm_ctr NUMBER :=0;
4713
4714 l_return_status VArchar2(2):= 'S';
4715
4716 x_msg_count NUMBER := 0;
4717 x_msg_data VArchar2(1000);
4718
4719 l_msg_data VARCHAR2(1000);
4720 l_msg_index_out NUMBER;
4721 l_message VARCHAR2(2400);
4722 --l_start_rowid ROWID := p_start_rowid;
4723 --l_end_rowid ROWID := p_end_rowid;
4724 l_rul_id NUMBER := -9999;
4725
4726 G_EXCEPTION_HALT EXCEPTION;
4727
4728 Action_Type_ID_TBL Num_Tbl_Type;
4729 Action_Type_Cle_ID_TBL Num_Tbl_Type;
4730 Action_Type_Dnz_ID_TBL Num_Tbl_Type;
4731 Created_By_TBL Num_Tbl_Type;
4732 Last_Updated_By_TBL Num_Tbl_Type;
4733 Last_Update_Date_TBL Date_Tbl_Type;
4734 Last_Update_Login_TBL Num_Tbl_Type;
4735 UOM_CODE_TBL Vc20_Tbl_Type;
4736 DURATION_TBL Num_Tbl_Type;
4737 DAY_OF_WEEK_TBL Vc20_Tbl_Type;
4738 TVE_TYPE_TBL Vc20_Tbl_Type;
4739 RUL_ID_TBL Num_Tbl_Type;
4740 Rul_major_version_tbl Num_Tbl_Type;
4741
4742 BEGIN
4743
4744 G_APP_NAME := 'Reaction_TimeValues_Migration';
4745
4746 OPEN Csr_Get_Timevalues ;
4747 LOOP
4748 BEGIN
4749 FETCH Csr_Get_Timevalues BULK COLLECT INTO
4750 Action_Type_ID_TBL,
4751 Action_Type_Cle_ID_TBL,
4752 Action_Type_Dnz_ID_TBL,
4753 Created_By_TBL,
4754 Last_Updated_By_TBL,
4755 Last_Update_Date_TBL,
4756 Last_Update_Login_TBL,
4757 UOM_CODE_TBL,
4758 DURATION_TBL,
4759 DAY_OF_WEEK_TBL,
4760 TVE_TYPE_TBL,
4761 RUL_ID_TBL,
4762 Rul_major_version_tbl
4763 LIMIT 1000;
4764
4765 -- -- dbms_output.put_line('Value of Action_Type_ID_TBL.COUNT='||TO_CHAR(Action_Type_ID_TBL.COUNT));
4766
4767 IF Action_Type_ID_TBL.COUNT > 0 THEN
4768 FOR I IN Action_Type_ID_TBL.FIRST .. Action_Type_ID_TBL.LAST LOOP
4769
4770 IF l_rul_id <> RUL_ID_TBL(I) THEN
4771
4772 l_rul_id := RUL_ID_TBL(I);
4773 l_acm_ctr := l_acm_ctr + 1;
4774
4775 i_acmv_tbl_in(l_acm_ctr).Created_By := Created_By_TBL(i);
4776 i_acmv_tbl_in(l_acm_ctr).Last_Updated_By := Last_Updated_By_TBL(i);
4777 i_acmv_tbl_in(l_acm_ctr).Last_Update_Date := Last_Update_Date_TBL(i);
4778 i_acmv_tbl_in(l_acm_ctr).Last_Update_Login := Last_Update_Login_TBL(i);
4779 i_acmv_tbl_in(l_acm_ctr).SECURITY_GROUP_ID := NULL;
4780 i_acmv_tbl_in(l_acm_ctr).PROGRAM_APPLICATION_ID := NULL;
4781 i_acmv_tbl_in(l_acm_ctr).PROGRAM_ID := NULL;
4782 i_acmv_tbl_in(l_acm_ctr).PROGRAM_UPDATE_DATE := NULL;
4783 i_acmv_tbl_in(l_acm_ctr).REQUEST_ID := NULL;
4784
4785 i_acmv_tbl_in(l_acm_ctr).ID := okc_p_util.raw_to_number(sys_guid());
4786 i_acmv_tbl_in(l_acm_ctr).COV_ACTION_TYPE_ID := Action_Type_ID_TBL(i);
4787 i_acmv_tbl_in(l_acm_ctr).CLE_ID := Action_Type_Cle_ID_TBL(i);
4788 i_acmv_tbl_in(l_acm_ctr).Dnz_chr_id := Action_Type_Dnz_ID_TBL(i);
4789 i_acmv_tbl_in(l_acm_ctr).UOM_CODE := UOM_CODE_TBL(i);
4790 i_acmv_tbl_in(l_acm_ctr).object_version_number := 1;
4791
4792 i_acmv_tbl_in(l_acm_ctr).SUN_DURATION := NULL;
4793 i_acmv_tbl_in(l_acm_ctr).MON_DURATION := NULL;
4794 i_acmv_tbl_in(l_acm_ctr).TUE_DURATION := NULL;
4795 i_acmv_tbl_in(l_acm_ctr).WED_DURATION := NULL;
4796 i_acmv_tbl_in(l_acm_ctr).THU_DURATION := NULL;
4797 i_acmv_tbl_in(l_acm_ctr).FRI_DURATION := NULL;
4798 i_acmv_tbl_in(l_acm_ctr).SAT_DURATION := NULL;
4799 i_acmv_tbl_in(l_acm_ctr).major_version := Rul_major_version_tbl(I);
4800 END IF;
4801
4802 IF DAY_OF_WEEK_TBL(i) = 'SUN' THEN
4803 i_acmv_tbl_in(l_acm_ctr).SUN_DURATION := DURATION_TBL(i);
4804
4805 ELSIF DAY_OF_WEEK_TBL(i) = 'MON' THEN
4806 i_acmv_tbl_in(l_acm_ctr).MON_DURATION := DURATION_TBL(i);
4807
4808 ELSIF DAY_OF_WEEK_TBL(i) = 'TUE' THEN
4809 i_acmv_tbl_in(l_acm_ctr).TUE_DURATION := DURATION_TBL(i);
4810
4811 ELSIF DAY_OF_WEEK_TBL(i) = 'WED' THEN
4812 i_acmv_tbl_in(l_acm_ctr).WED_DURATION := DURATION_TBL(i);
4813
4814 ELSIF DAY_OF_WEEK_TBL(i) = 'THU' THEN
4815 i_acmv_tbl_in(l_acm_ctr).THU_DURATION := DURATION_TBL(i);
4816
4817 ELSIF DAY_OF_WEEK_TBL(i) = 'FRI' THEN
4818 i_acmv_tbl_in(l_acm_ctr).FRI_DURATION := DURATION_TBL(i);
4819
4820 ELSIF DAY_OF_WEEK_TBL(i) = 'SAT' THEN
4821 i_acmv_tbl_in(l_acm_ctr).SAT_DURATION := DURATION_TBL(i);
4822 END IF;
4823
4824 END LOOP;
4825 END IF;
4826
4827
4828 IF i_acmv_tbl_in.COUNT > 0 THEN
4829 i := i_acmv_tbl_in.FIRST; K:=0;
4830
4831 WHILE I is not null LOOP
4832
4833 k:=k+1;
4834
4835 IN_ID(K) := i_acmv_tbl_in(I).ID ;
4836 IN_COV_ACTION_TYPE_ID(K) := i_acmv_tbl_in(I).COV_ACTION_TYPE_ID ;
4837 IN_CLE_ID(K) := i_acmv_tbl_in(I).CLE_ID ;
4838 IN_DNZ_CHR_ID(K) := i_acmv_tbl_in(I).DNZ_CHR_ID ;
4839 IN_UOM_CODE(K) := i_acmv_tbl_in(I).UOM_CODE ;
4840 IN_SUN_DURATION(K) := i_acmv_tbl_in(I).SUN_DURATION ;
4841 IN_MON_DURATION(K) := i_acmv_tbl_in(I).MON_DURATION ;
4842 IN_TUE_DURATION(K) := i_acmv_tbl_in(I).TUE_DURATION ;
4843 IN_WED_DURATION(K) := i_acmv_tbl_in(I).WED_DURATION ;
4844 IN_THU_DURATION(K) := i_acmv_tbl_in(I).THU_DURATION ;
4845 IN_FRI_DURATION(K) := i_acmv_tbl_in(I).FRI_DURATION ;
4846 IN_SAT_DURATION(K) := i_acmv_tbl_in(I).SAT_DURATION ;
4847 IN_SECURITY_GROUP_ID(K) := i_acmv_tbl_in(I).SECURITY_GROUP_ID ;
4848 IN_PROGRAM_APPLICATION_ID(K) := i_acmv_tbl_in(I).PROGRAM_APPLICATION_ID ;
4849 IN_PROGRAM_ID(K) := i_acmv_tbl_in(I).PROGRAM_ID ;
4850 IN_PROGRAM_UPDATE_DATE(K) := i_acmv_tbl_in(I).PROGRAM_UPDATE_DATE ;
4851 IN_REQUEST_ID(K) := i_acmv_tbl_in(I).REQUEST_ID ;
4852 IN_CREATED_BY(K) := i_acmv_tbl_in(I).CREATED_BY ;
4853 IN_CREATION_DATE(K) := i_acmv_tbl_in(I).CREATION_DATE ;
4854 IN_LAST_UPDATED_BY(K) := i_acmv_tbl_in(I).LAST_UPDATED_BY ;
4855 IN_LAST_UPDATE_DATE(K) := i_acmv_tbl_in(I).LAST_UPDATE_DATE ;
4856 IN_LAST_UPDATE_LOGIN(K) := i_acmv_tbl_in(I).LAST_UPDATE_LOGIN ;
4857 IN_OBJECT_VERSION_NUMBER(K) := i_acmv_tbl_in(I).OBJECT_VERSION_NUMBER ;
4858 IN_MAJOR_VERSION(K) := i_acmv_tbl_in(I).MAJOR_VERSION ;
4859
4860 i:=i_acmv_tbl_in.next(i);
4861
4862 END LOOP;
4863
4864 l_tabsize := i_acmv_tbl_in.COUNT;
4865 -- l_status := 'Before Insert';
4866
4867 FORALL I IN 1 .. l_tabsize
4868 INSERT INTO OKS_ACTION_TIMES_H(
4869 ID,
4870 COV_ACTION_TYPE_ID,
4871 CLE_ID,
4872 DNZ_CHR_ID,
4873 UOM_CODE,
4874 SUN_DURATION,
4875 MON_DURATION,
4876 TUE_DURATION,
4877 WED_DURATION,
4878 THU_DURATION,
4879 FRI_DURATION,
4880 SAT_DURATION,
4881 SECURITY_GROUP_ID,
4882 PROGRAM_APPLICATION_ID,
4883 PROGRAM_ID,
4884 PROGRAM_UPDATE_DATE,
4885 REQUEST_ID,
4886 CREATED_BY,
4887 CREATION_DATE,
4888 LAST_UPDATED_BY,
4889 LAST_UPDATE_DATE,
4890 LAST_UPDATE_LOGIN,
4891 OBJECT_VERSION_NUMBER,
4892 MAJOR_VERSION)
4893 VALUES (
4894 IN_ID(I),
4895 IN_COV_ACTION_TYPE_ID(I),
4896 IN_CLE_ID(I),
4897 IN_DNZ_CHR_ID(I),
4898 IN_UOM_CODE(I),
4899 IN_SUN_DURATION(I),
4900 IN_MON_DURATION(I),
4901 IN_TUE_DURATION(I),
4902 IN_WED_DURATION(I),
4903 IN_THU_DURATION(I),
4904 IN_FRI_DURATION(I),
4905 IN_SAT_DURATION(I),
4906 IN_SECURITY_GROUP_ID(I),
4907 IN_PROGRAM_APPLICATION_ID(I),
4908 IN_PROGRAM_ID(I),
4909 IN_PROGRAM_UPDATE_DATE(I),
4910 IN_REQUEST_ID(I),
4911 IN_CREATED_BY(I),
4912 IN_CREATION_DATE(I),
4913 IN_LAST_UPDATED_BY(I),
4914 IN_LAST_UPDATE_DATE(I),
4915 IN_LAST_UPDATE_LOGIN(I),
4916 IN_OBJECT_VERSION_NUMBER(I),
4917 IN_MAJOR_VERSION(I));
4918
4919 IF l_return_Status = 'S' THEN
4920 X_return_Status := 'S';
4921 X_Message_Data := NULL;
4922 COMMIT;
4923 ELSE
4924 RAISE G_EXCEPTION_HALT;
4925 END IF;
4926
4927 END IF;
4928
4929 EXIT WHEN Csr_Get_Timevalues%NOTFOUND;
4930
4931 EXCEPTION
4932 WHEN G_EXCEPTION_HALT THEN
4933
4934 ROLLBACK;
4935 OKC_API.SET_MESSAGE
4936 ( p_app_name => G_APP_NAME,
4937 p_msg_name => G_UNEXPECTED_ERROR,
4938 p_token1 => G_SQLCODE_TOKEN,
4939 p_token1_value => SQLCODE,
4940 p_token2 => G_SQLERRM_TOKEN,
4941 p_token2_value => SQLERRM
4942 );
4943
4944 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4945 G_APP_NAME,
4946 G_PKG_NAME,
4947 'OKC_API.G_RET_STS_UNEXP_ERROR',
4948 x_msg_count,
4949 x_msg_data,
4950 '_PVT'
4951 );
4952
4953 IF x_msg_count > 0 THEN
4954 FOR i in 1..x_msg_count LOOP
4955 fnd_msg_pub.get (p_msg_index => -1,
4956 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
4957 p_data => l_msg_data,
4958 p_msg_index_out => l_msg_index_out);
4959 l_message := l_message||' ; '||l_msg_data;
4960
4961 END LOOP;
4962 END IF;
4963 x_message_data := l_message;
4964 x_return_status := 'E';
4965
4966
4967 WHEN Others THEN
4968 x_return_status := OKC_API.G_RET_STS_ERROR;
4969 OKC_API.SET_MESSAGE
4970 ( p_app_name => G_APP_NAME,
4971 p_msg_name => G_UNEXPECTED_ERROR,
4972 p_token1 => G_SQLCODE_TOKEN,
4973 p_token1_value => SQLCODE,
4974 p_token2 => G_SQLERRM_TOKEN,
4975 p_token2_value => SQLERRM
4976 );
4977 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
4978 G_APP_NAME,
4979 G_PKG_NAME,
4980 'OKC_API.G_RET_STS_UNEXP_ERROR',
4981 x_msg_count,
4982 x_msg_data,
4983 '_PVT'
4984 );
4985 IF x_msg_count > 0 THEN
4986 FOR i in 1..x_msg_count LOOP
4987 fnd_msg_pub.get (p_msg_index => -1,
4988 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
4989 p_data => l_msg_data,
4990 p_msg_index_out => l_msg_index_out);
4991 l_message := l_message||' ; '||l_msg_data;
4992
4993 END LOOP;
4994 END IF;
4995 x_message_data := l_message;
4996 x_return_status := 'E';
4997 -- EXIT;
4998 END;
4999
5000 EXIT WHEN Csr_Get_Timevalues%NOTFOUND;
5001
5002 i_acmv_tbl_in.DELETE;
5003
5004 END LOOP;
5005
5006 IF Csr_Get_Timevalues%ISOPEN THEN
5007 CLOSE Csr_Get_Timevalues;
5008 END IF;
5009
5010
5011 --CLOSE Csr_Get_Timevalues;
5012 EXCEPTION
5013 WHEN OTHERS THEN
5014 ROLLBACK;
5015
5016 END React_TimeVal_Hist_Migration;
5017
5018
5019
5020 PROCEDURE BILL_TYPE_HIST_MIGRATION( p_start_rowid IN ROWID,
5021 p_end_rowid IN ROWID,
5022 x_return_status OUT NOCOPY VARCHAR2,
5023 x_message_data OUT NOCOPY VARCHAR2) IS
5024
5025 CURSOR Csr_Get_Bill_Types (l_start_rowid IN ROWID ,l_end_rowid IN ROWID ) IS
5026 SELECT
5027 LINE.ID LINE_ID,
5028 Line.Created_By Line_Created_By,
5029 Line.Creation_Date Line_Creation_Date,
5030 Line.Last_Updated_By Line_Last_Updated_By,
5031 Line.Last_Update_Date Line_Last_Update_Date,
5032 Line.Last_Update_Login Line_Last_Update_Login,
5033 Rul.ROWID RUL_ROW_ID,
5034 Rul.ID Rul_Id,
5035 RGP.ID LINE_RGP_ID,
5036 LINE.LSE_ID LINE_LSE_ID,
5037 LINE.DNZ_CHR_ID LINE_DNZ_CHR_ID,
5038 OBJECT1_ID1 LINE_OBJECT1_ID1,
5039 OBJECT2_ID1 LINE_OBJECT2_ID1,
5040 RULE_INFORMATION1 COV_RULE_INFO1,
5041 RULE_INFORMATION2 COV_RULE_INFO2,
5042 RULE_INFORMATION3 COV_RULE_INFO3,
5043 RULE_INFORMATION4 COV_RULE_INFO4,
5044 RULE_INFORMATION5 COV_RULE_INFO5,
5045 RULE_INFORMATION6 COV_RULE_INFO6,
5046 RULE_INFORMATION7 COV_RULE_INFO7,
5047 RULE_INFORMATION8 COV_RULE_INFO8,
5048 RULE_INFORMATION9 COV_RULE_INFO9,
5049 RULE_INFORMATION10 COV_RULE_INFO10,
5050 RULE_INFORMATION11 COV_RULE_INFO11,
5051 RULE_INFORMATION12 COV_RULE_INFO12,
5052 RULE_INFORMATION13 COV_RULE_INFO13,
5053 RULE_INFORMATION14 COV_RULE_INFO14,
5054 RULE_INFORMATION15 COV_RULE_INFO15,
5055 RULE_INFORMATION_CATEGORY COV_RULE_INFO,
5056 RUL.OBJECT_VERSION_NUMBER COV_OBJ_VER_NUMBER,
5057 RUL.MAJOR_VERSION RUL_MAJOR_VERSION
5058 FROM
5059 OKC_RULE_GROUPS_BH RGP,
5060 OKC_RULES_BH RUL,
5061 OKC_K_LINES_BH LINE
5062 WHERE LINE.ID = RGP.CLE_ID
5063 AND RGP.ID = RUL.RGP_ID
5064 AND LINE.LSE_ID IN (5,59,23)
5065 --AND RUL.RULE_INFORMATION_CATEGORY IN ('LMT')
5066 AND Rul.rowid BETWEEN l_start_rowid and l_end_rowid
5067 AND RGP.MAJOR_VERSION = LINE.MAJOR_VERSION
5068 AND RGP.MAJOR_VERSION = RUL.MAJOR_VERSION
5069 AND NOT EXISTS (SELECT CLE_ID FROM OKS_K_LINES_BH WHERE CLE_ID = LINE.ID)
5070 ORDER BY LINE.ID;
5071
5072 CURSOR Get_Rule_TlH (P_ID IN NUMBER) IS
5073 SELECT ID,
5074 MAJOR_VERSION,
5075 LANGUAGE,
5076 SOURCE_LANG,
5077 SFWT_FLAG,
5078 COMMENTS,
5079 TEXT,
5080 CREATED_BY,
5081 CREATION_DATE,
5082 LAST_UPDATED_BY,
5083 LAST_UPDATE_DATE,
5084 LAST_UPDATE_LOGIN,
5085 SECURITY_GROUP_ID
5086 FROM OKC_RULES_TLH
5087 WHERE ID = P_ID;
5088
5089
5090 TYPE RowId_Tbl_Type IS TABLE OF ROWID INDEX BY BINARY_INTEGER;
5091 TYPE Num_Tbl_Type IS VARRAY(1000) OF NUMBER;
5092 TYPE Date_Tbl_Type IS VARRAY(1000) OF DATE;
5093 TYPE Vc15_Tbl_Type IS VARRAY(1000) OF VARCHAR2(15);
5094 TYPE Vc20_Tbl_Type IS VARRAY(1000) OF VARCHAR2(20);
5095 TYPE Vc150_Tbl_Type IS VARRAY(1000) OF VARCHAR2(150);
5096 TYPE Vc30_Tbl_Type IS VARRAY(1000) OF VARCHAR2(30);
5097 TYPE Vc1_Tbl_Type IS VARRAY(1000) OF VARCHAR2(1);
5098 TYPE Vc3_Tbl_Type IS VARRAY(1000) OF VARCHAR2(3);
5099 TYPE Vc2000_Tbl_Type IS VARRAY(1000) OF VARCHAR2(2000);
5100 TYPE Vc50_Tbl_Type IS VARRAY(1000) OF VARCHAR2(50);
5101 TYPE Vc80_Tbl_Type IS VARRAY(1000) OF VARCHAR2(80);
5102 TYPE Vc120_Tbl_Type IS VARRAY(1000) OF VARCHAR2(120);
5103 TYPE Vc600_Tbl_Type IS VARRAY(1000) OF VARCHAR2(600);
5104
5105
5106 LINE_ID_TBL Num_Tbl_Type;
5107 Line_Created_By_TBL Num_Tbl_Type;
5108 Line_Creation_Date_TBL Date_Tbl_Type;
5109 Line_Last_Updated_By_TBL Num_Tbl_Type;
5110 Line_Last_Update_Date_TBL Date_Tbl_Type;
5111 Line_Last_Update_Login_TBL Num_Tbl_Type;
5112 RUL_ROW_ID_TBL RowId_Tbl_Type;
5113 LINE_RGP_ID_TBL Num_Tbl_Type;
5114 LINE_LSE_ID_TBL Num_Tbl_Type;
5115 LINE_DNZ_CHR_ID_TBL Num_Tbl_Type;
5116 LINE_OBJECT1_ID1_TBL Vc150_Tbl_Type;
5117 LINE_OBJECT2_ID1_TBL Vc150_Tbl_Type;
5118 COV_RULE_INFO1_TBL Vc150_Tbl_Type;
5119 COV_RULE_INFO2_TBL Vc150_Tbl_Type;
5120 COV_RULE_INFO3_TBL Vc150_Tbl_Type;
5121 COV_RULE_INFO4_TBL Vc150_Tbl_Type;
5122 COV_RULE_INFO5_TBL Vc150_Tbl_Type;
5123 COV_RULE_INFO6_TBL Vc150_Tbl_Type;
5124 COV_RULE_INFO7_TBL Vc150_Tbl_Type;
5125 COV_RULE_INFO8_TBL Vc150_Tbl_Type;
5126 COV_RULE_INFO9_TBL Vc150_Tbl_Type;
5127 COV_RULE_INFO10_TBL Vc150_Tbl_Type;
5128 COV_RULE_INFO11_TBL Vc150_Tbl_Type;
5129 COV_RULE_INFO12_TBL Vc150_Tbl_Type;
5130 COV_RULE_INFO13_TBL Vc150_Tbl_Type;
5131 COV_RULE_INFO14_TBL Vc150_Tbl_Type;
5132 COV_RULE_INFO15_TBL Vc150_Tbl_Type;
5133 COV_RULE_INFO_TBL Vc150_Tbl_Type;
5134 COV_OBJ_VER_NUMBER_TBL Num_Tbl_Type;
5135 RUL_MAJOR_VERSION_TBL Num_Tbl_Type;
5136 Rul_Id_Tbl Num_Tbl_Type;
5137
5138 --h_clev_tbl_in oks_kln_pvt.klnv_tbl_type;
5139 --i_clev_tbl_in oks_kln_pvt.klnv_tbl_type;
5140
5141 l_cle_ctr NUMBER := 0;
5142 tablename1 VArchar2(1000);
5143 x_msg_count NUMBER := 0;
5144 x_msg_data VArchar2(1000);
5145 L_OLD_CLE_ID NUMBER := -99999;
5146 L_CLE_ID NUMBER := -99999;
5147 l_duration NUMBER;
5148 l_period VArchar2(10);
5149 l_return_status VArchar2(1) := OKC_API.G_RET_STS_SUCCESS;
5150 G_EXCEPTION_HALT EXCEPTION;
5151
5152 l_start_rowid ROWID := p_start_rowid;
5153 l_end_rowid ROWID := p_end_rowid;
5154
5155 l_msg_data VARCHAR2(1000);
5156 l_msg_index_out NUMBER;
5157 l_message VARCHAR2(2400);
5158
5159 l_line_id NUMBER := -9999;
5160 l_old_line_id NUMBER := -9999;
5161
5162 BEGIN
5163 G_APP_NAME := 'BILL_TYPES_MIGRATION';
5164 OPEN Csr_Get_Bill_Types (l_start_rowid,l_end_rowid);
5165 LOOP
5166 BEGIN
5167 FETCH Csr_Get_Bill_Types BULK COLLECT INTO
5168 LINE_ID_TBL ,
5169 Line_Created_By_TBL ,
5170 Line_Creation_Date_Tbl ,
5171 Line_Last_Updated_By_TBL ,
5172 Line_Last_Update_Date_TBL ,
5173 Line_Last_Update_Login_TBL ,
5174 RUL_ROW_ID_TBL ,
5175 Rul_Id_Tbl ,
5176 LINE_RGP_ID_TBL ,
5177 LINE_LSE_ID_TBL ,
5178 LINE_DNZ_CHR_ID_TBL ,
5179 LINE_OBJECT1_ID1_TBL ,
5180 LINE_OBJECT2_ID1_TBL ,
5181 COV_RULE_INFO1_TBL ,
5182 COV_RULE_INFO2_TBL ,
5183 COV_RULE_INFO3_TBL ,
5184 COV_RULE_INFO4_TBL ,
5185 COV_RULE_INFO5_TBL ,
5186 COV_RULE_INFO6_TBL ,
5187 COV_RULE_INFO7_TBL ,
5188 COV_RULE_INFO8_TBL ,
5189 COV_RULE_INFO9_TBL ,
5190 COV_RULE_INFO10_TBL ,
5191 COV_RULE_INFO11_TBL ,
5192 COV_RULE_INFO12_TBL ,
5193 COV_RULE_INFO13_TBL ,
5194 COV_RULE_INFO14_TBL ,
5195 COV_RULE_INFO15_TBL ,
5196 COV_RULE_INFO_TBL ,
5197 COV_OBJ_VER_NUMBER_TBL ,
5198 RUL_MAJOR_VERSION_TBL
5199 LIMIT 1000;
5200
5201
5202 -- dbms_output.put_line('Value of LINE_ID_TBL.COUNT='||TO_CHAR(LINE_ID_TBL.COUNT));
5203
5204 IF LINE_ID_TBL.COUNT > 0 THEN
5205
5206 FOR I IN LINE_ID_TBL.FIRST .. LINE_ID_TBL.LAST LOOP
5207 L_Cle_id := LINE_ID_TBL(i);
5208 IF (L_OLD_CLE_ID <> L_CLE_ID) THEN
5209 l_cle_ctr := l_cle_ctr + 1;
5210 END IF;
5211 l_line_id := LINE_ID_TBL(i);
5212
5213 i_clev_tbl_in(l_cle_ctr).Id := okc_p_util.raw_to_number(sys_guid());
5214 i_clev_tbl_in(l_cle_ctr).Created_By := Line_Created_By_tbl(i);
5215 i_clev_tbl_in(l_cle_ctr).Creation_Date := Line_Creation_Date_TBL(i);
5216 i_clev_tbl_in(l_cle_ctr).Last_Updated_By :=
5217 Line_Last_Updated_By_TBL(i);
5218 i_clev_tbl_in(l_cle_ctr).Last_Update_Date :=
5219 Line_Last_Update_Date_TBL(i);
5220 i_clev_tbl_in(l_cle_ctr).Last_Update_Login := Line_Last_Update_Login_TBL(i);
5221 i_clev_tbl_in(l_cle_ctr).Cle_Id := LINE_ID_TBL(i);
5222 i_clev_tbl_in(l_cle_ctr).Dnz_Chr_Id := LINE_DNZ_CHR_ID_TBL(i);
5223 i_clev_tbl_in(l_cle_ctr).object_version_number := 1;
5224 i_clev_tbl_in(l_cle_ctr).Sync_Date_Install := 'N';
5225
5226 IF COV_RULE_INFO_TBL(i) = 'LMT' THEN
5227 i_clev_tbl_in(l_cle_ctr).LIMIT_UOM_QUANTIFIED := COV_RULE_INFO1_TBL(i);
5228 i_clev_tbl_in(l_cle_ctr).DISCOUNT_AMOUNT := COV_RULE_INFO2_TBL(i);
5229 i_clev_tbl_in(l_cle_ctr).DISCOUNT_PERCENT := COV_RULE_INFO4_TBL(i);
5230 i_clev_tbl_in(l_cle_ctr).major_version := Rul_major_version_tbl(i);
5231 END IF;
5232
5233
5234 ------------------------- FOR TLH-----------------------
5235 IF l_line_id <> l_old_line_id THEN
5236 -- dbms_output.put_line('--->Value of Rule_Id_Tbl(l_cle_ctr)='||TO_CHAR(Rul_Id_Tbl(I)));
5237 FOR Get_Rule_TlH_REC IN Get_Rule_TlH(Rul_Id_Tbl(l_cle_ctr)) LOOP
5238 l_clt_ctr := l_clt_ctr + 1;
5239 -- dbms_output.put_line('---------->l_clt_ctr='||TO_CHAR(l_clt_ctr));
5240 l_clet_tbl_in(l_clt_ctr).id := i_clev_tbl_in(l_cle_ctr).id;
5241 l_clet_tbl_in(l_clt_ctr).MAJOR_VERSION := Get_Rule_TlH_REC.MAJOR_VERSION;
5242 l_clet_tbl_in(l_clt_ctr).language := Get_Rule_TlH_REC.language;
5243 l_clet_tbl_in(l_clt_ctr).source_lang := Get_Rule_TlH_REC.source_lang;
5244 l_clet_tbl_in(l_clt_ctr).sfwt_flag := Get_Rule_TlH_REC.sfwt_flag;
5245 l_clet_tbl_in(l_clt_ctr).invoice_text := NULL; --Get_Rule_TlH_REC.text;
5246 -- l_clet_tbl_in(l_clt_ctr).ib_trx_details := Get_Rule_TlH_REC.ib_trx_details;
5247 -- l_clet_tbl_in(l_clt_ctr).status_text := Get_Rule_TlH_REC.status_text;
5248 -- l_clet_tbl_in(l_clt_ctr).react_time_name := Get_Rule_TlH_REC.react_time_name;
5249 l_clet_tbl_in(l_clt_ctr).created_by := Get_Rule_TlH_REC.created_by;
5250 l_clet_tbl_in(l_clt_ctr).creation_date := Get_Rule_TlH_REC.creation_date;
5251 l_clet_tbl_in(l_clt_ctr).last_updated_by := Get_Rule_TlH_REC.last_updated_by;
5252 l_clet_tbl_in(l_clt_ctr).last_update_date := Get_Rule_TlH_REC.last_update_date;
5253 l_clet_tbl_in(l_clt_ctr).last_update_login := Get_Rule_TlH_REC.last_update_login;
5254
5255 END LOOP;
5256
5257 l_old_line_id := l_line_id;
5258 -- dbms_output.put_line('Value of l_old_line_id='||TO_CHAR(l_old_line_id));
5259 END IF;
5260
5261
5262
5263
5264 L_OLD_CLE_ID := L_CLE_ID;
5265 END LOOP;
5266 END IF;
5267
5268
5269 tablename1 := 'OKS_K_LINES';
5270
5271 IF i_clev_tbl_in.count > 0 THEN
5272 -- dbms_output.put_line('Value of i_clev_tbl_in='||TO_CHAR(i_clev_tbl_in.COUNT));
5273 -- dbms_output.put_line('Value of l_clet_tbl_in='||TO_CHAR(l_clet_tbl_in.COUNT));
5274 Insert_Into_Klines( p_clev_tbl_in => i_clev_tbl_in,
5275 p_clet_tbl_in => l_clet_tbl_in,
5276 x_return_Status => l_return_status);
5277 END IF;
5278
5279
5280 IF l_return_status = 'S' THEN
5281 x_return_status := 'S';
5282 x_message_data := NULL;
5283 ELSE
5284 RAISE G_EXCEPTION_HALT;
5285 END IF;
5286
5287 EXCEPTION
5288 WHEN G_EXCEPTION_HALT THEN
5289 ROLLBACK;
5290
5291 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
5292 G_APP_NAME,
5293 G_PKG_NAME,
5294 'OKC_API.G_RET_STS_UNEXP_ERROR',
5295 x_msg_count,
5296 x_msg_data,
5297 '_PVT'
5298 );
5299 i_clev_tbl_in.DELETE;
5300
5301 IF x_msg_count > 0 THEN
5302 FOR i in 1..x_msg_count LOOP
5303 fnd_msg_pub.get (p_msg_index => -1,
5304 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
5305 p_data => l_msg_data,
5306 p_msg_index_out => l_msg_index_out);
5307 l_message := l_message||' ; '||l_msg_data;
5308
5309 END LOOP;
5310 END IF;
5311 x_message_data := l_message;
5312 x_return_status := 'E';
5313 WHEN Others THEN
5314 ROLLBACK;
5315 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
5316 G_APP_NAME,
5317 G_PKG_NAME,
5318 'OKC_API.G_RET_STS_UNEXP_ERROR',
5319 x_msg_count,
5320 x_msg_data,
5321 '_PVT'
5322 );
5323 i_clev_tbl_in.DELETE;
5324 IF x_msg_count > 0 THEN
5325 FOR i in 1..x_msg_count LOOP
5326 fnd_msg_pub.get (p_msg_index => -1,
5327 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
5328 p_data => l_msg_data,
5329 p_msg_index_out => l_msg_index_out);
5330 l_message := l_message||' ; '||l_msg_data;
5331
5332 END LOOP;
5333 END IF;
5334 x_message_data := l_message;
5335 x_return_status := 'E';
5336 EXIT WHEN Csr_Get_Bill_Types%NOTFOUND;
5337 END;
5338
5339 EXIT WHEN Csr_Get_Bill_Types%NOTFOUND;
5340
5341 END LOOP;
5342
5343 CLOSE Csr_Get_Bill_Types;
5344
5345 EXCEPTION
5346 WHEN Others THEN
5347 x_return_status := OKC_API.HANDLE_EXCEPTIONS(
5348 G_APP_NAME,
5349 G_PKG_NAME,
5350 'OKC_API.G_RET_STS_UNEXP_ERROR',
5351 x_msg_count,
5352 x_msg_data,
5353 '_PVT'
5354 );
5355 IF x_msg_count > 0 THEN
5356 FOR i in 1..x_msg_count LOOP
5357 fnd_msg_pub.get (p_msg_index => -1,
5358 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
5359 p_data => l_msg_data,
5360 p_msg_index_out => l_msg_index_out);
5361 l_message := l_message||' ; '||l_msg_data;
5362
5363 END LOOP;
5364 END IF;
5365 x_message_data := l_message;
5366 x_return_status := 'E';
5367
5368 END BILL_TYPE_HIST_MIGRATION;
5369
5370
5371
5372 /*********************************** HISTORY ****************************************/
5373
5374
5375
5376
5377 END OKS_COVERAGE_MIGRATION;
5378
5379