[Home] [Help]
PACKAGE BODY: APPS.OKS_COVERAGES_MIGRATION
Source
1 PACKAGE BODY OKS_COVERAGES_MIGRATION AS
2 /* $Header: OKSIMCVB.pls 115.2 2003/02/05 00:14:25 hmedheka noship $ */
3
4
5 PROCEDURE COVERAGE_MIGRATE (P_FromId IN NUMBER,
6 P_ToId IN NUMBER,
7 P_VALIDATE_FLAG IN VARCHAR2,
8 P_LOG_PARAMETER IN VARCHAR2) IS
9
10 CURSOR get_coverages_cur (p_FromId in number,p_ToId in number) is
11 select
12 nvl(cov.COVERAGE_ID,0) COVERAGE_ID,
13 cov.NAME,
14 cov.DESCRIPTION,
15 cov.START_DATE,
16 cov.END_DATE,
17 cov.FREE_UPGRADE_YN,
18 cov.COVERAGE_TYPE_CODE,
19 cov.EXCEPTION_COVERAGE_YN,
20 cov.EXC_COVERAGE_ID,
21 cov.TEMPLATE_FLAG_YN,
22 cov.WARRANTY_YN,
23 cov.WARRANTY_INHERITANCE_CODE,
24 cov.TRANSFER_ALLOWED_YN,
25 cov.ATTRIBUTE_CATEGORY,
26 cov.ATTRIBUTE1,
27 cov.ATTRIBUTE2,
28 cov.ATTRIBUTE3,
29 cov.ATTRIBUTE4,
30 cov.ATTRIBUTE5,
31 cov.ATTRIBUTE6,
32 cov.ATTRIBUTE7,
33 cov.ATTRIBUTE8,
34 cov.ATTRIBUTE9,
35 cov.ATTRIBUTE10,
36 cov.ATTRIBUTE11,
37 cov.ATTRIBUTE12,
38 cov.ATTRIBUTE13,
39 cov.ATTRIBUTE14,
40 cov.ATTRIBUTE15
41 from oks_coverages_int_all cov,
42 OKS_CON_LINES_INT_ALL lines,
43 OKS_CON_HEADERS_INT_ALL head
44 where cov.coverage_id <> 0
45 and cov.interfaced_status_flag is null
46 and head.batch_number between p_FromId and p_ToID
47 and head.interfaced_status_flag = 'S'
48 and head.contract_id = lines.contract_id
49 and lines.interfaced_status_flag = 'S'
50 and lines.coverage_id = cov.coverage_id;
51
52 get_coverages_rec get_coverages_cur%ROWTYPE;
53
54 CURSOR get_okc_lines_cur(p_id in number) is
55 SELECT cle1.Id Id,
56 cle1.Lse_Id Lse_Id,
57 cle1.Start_Date Start_Date,
58 cle1.Sts_Code Sts_Code,
59 cle1.End_Date End_Date,
60 cle1.Dnz_Chr_Id Dnz_Chr_Id,
61 cle1.Currency_Code Currency_Code,
62 cle1.display_sequence display_sequence,
63 cle1.Line_Number Line_Number,
64 cle1.CREATED_BY CREATED_BY,
65 cle1.CREATION_DATE CREATION_DATE,
66 cle1.LAST_UPDATED_BY LAST_UPDATED_BY,
67 cle1.LAST_UPDATE_DATE LAST_UPDATE_DATE,
68 cle1.LAST_UPDATE_LOGIN LAST_UPDATE_LOGIN
69 FROM OKC_K_LINES_B cle1,OKS_CON_LINES_INT_ALL ocl
70 WHERE cle1.Lse_Id = 1
71 AND cle1.UPG_ORIG_SYSTEM_REF_ID = ocl.contract_line_id
72 AND cle1.dnz_chr_Id <> -1
73 AND cle1.UPG_ORIG_SYSTEM_REF = g_line_ref
74 AND ocl.coverage_id = p_id;
75
76 get_okc_lines_rec get_okc_lines_cur%rowtype;
77
78 CURSOR get_cov_id_cur is
79 select nvl(coverage_id,0) id,nvl(coverage_template_id,0) tid
80 from oks_con_lines_int_all
81 where coverage_id <> 0;
82
83 CURSOR get_mtl_cov_id_cur is
84 select distinct coverage_schedule_id id
85 from mtl_system_items mtl,
86 Okc_k_Lines_b cle,
87 okc_k_Items cim
88 WHERE to_char(mtl.Inventory_Item_Id) = cim.object1_Id1
89 AND to_char(mtl.Organization_Id) = cim.object1_Id2
90 AND cle.Id = cim.Cle_Id
91 AND mtl.Coverage_Schedule_Id IS NOT NULL ;
92 /*---------------------*/
93 i NUMBER := 0;
94 l_old_id Number := 0;
95 g_seq_id Number := 0;
96 G_SEQ_CONSTANT Number := 0;
97 g_CREATION_DATE date := sysdate;
98 l_get_cov_id number;
99 l_get_cov_tmp_id number;
100 l_upg_flag VARCHAR2(1) := 'N';
101 l_transfer_all_flag VARCHAR2(1) := 'N';
102 l_duration NUMBER;
103 l_timeunits VARCHAR2(240);
104 l_return_status VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
105 TYPE K_Status_rec IS RECORD (old_status Varchar2(30),new_status Varchar2(30));
106 TYPE K_Status_Tab is TABLE OF K_Status_Rec INDEX BY BINARY_Integer;
107
108 l_status_tab K_Status_Tab;
109
110 l_validate_flag VARCHAR2(1) := p_validate_flag;
111
112 /*---------------------*/
113 FUNCTION Get_new_status(p_k_status_id IN Number) RETURN Varchar2 IS
114 l_new_status Varchar2(30);
115 BEGIN
116 Return(l_status_tab(p_k_status_id).new_status);
117 END Get_new_status;
118
119 FUNCTION Get_Seq_Id (P_id IN Number) RETURN Number IS
120 BEGIN
121
122 IF l_old_id <> P_id
123 THEN
124 l_old_id := P_id;
125 g_seq_id := (P_id * 10000)+G_SEQ_CONSTANT ;
126 ELSE
127 g_seq_id := g_seq_id + 1;
128 END IF;
129 Return(g_seq_id);
130 END Get_Seq_Id;
131
132 FUNCTION Get_Cov_Type (P_contract_type IN Varchar2) RETURN Varchar2 IS
133 CURSOR Cur_cov_code IS
134 SELECT LOOKUP_CODE
135 FROM FND_LOOKUPS
136 WHERE LOOKUP_TYPE = 'OKSCVETYPE'
137 AND MEANING = P_contract_type;
138 l_cov_type Varchar2(30);
139 BEGIN
140 IF P_contract_type = 'Gold' THEN
141 l_cov_type := 'G';
142 ELSIF P_contract_type = 'Silver' THEN
143 l_cov_type := 'S';
144 ELSIF P_contract_type = 'Bronze' THEN
145 l_cov_type := 'B';
146 ELSE
147 OPEN Cur_cov_code;
148 FETCH Cur_cov_code INTO l_cov_type;
149 IF Cur_cov_code%NOTFOUND THEN
150 l_cov_type := NULL;
151 END IF;
152 CLOSE Cur_cov_code;
153 END IF;
154 RETURN l_cov_type;
155 END;
156
157 BEGIN --- MAIN BEGIN OF coverage_migrate procedure ----
158
159 open get_coverages_cur (p_fromid,p_toId);
160 loop
161 fetch get_coverages_cur into get_coverages_rec;
162
163 exit when get_coverages_cur%notfound;
164 -- dbms_output.put_line('Value of get_coverages_rec.COVERAGE_ID='||TO_CHAR(get_coverages_rec.COVERAGE_ID));
165 open get_okc_lines_cur(get_coverages_rec.COVERAGE_ID) ;
166 loop
167 fetch get_okc_lines_cur into get_okc_lines_rec;
168 exit when get_okc_lines_cur%notfound;
169
170 -- dbms_output.put_line('1111111111');
171 -- dbms_output.put_line('Value of get_okc_lines_rec.dnz_chr_id='||TO_CHAR(get_okc_lines_rec.dnz_chr_id));
172 l_clev_tbl_in(i).cle_id_renewed :=NULL;
173 l_clev_tbl_in(i).comments :=NULL;
174 l_clev_tbl_in(i).price_unit :=NULL;
175 l_clev_tbl_in(i).price_unit_percent :=NULL;
176 l_clev_tbl_in(i).price_negotiated :=NULL;
177 l_clev_tbl_in(i).price_level_ind :='N';
178 l_clev_tbl_in(i).block23text :=NULL;
179 l_clev_tbl_in(i).id := okc_p_util.raw_to_number(sys_guid()); --Get_Seq_Id(get_coverages_rec.Coverage_ID)
180 l_clev_tbl_in(i).CREATION_DATE := g_CREATION_DATE;
181 l_clev_tbl_in(i).CREATED_BY := -1;
182 l_clev_tbl_in(i).LAST_UPDATE_DATE := sysdate;
183 l_clev_tbl_in(i).LAST_UPDATED_BY := -1;
184 l_clev_tbl_in(i).LAST_UPDATE_LOGIN := -1;
185 l_clev_tbl_in(i).object_version_number := 1;
186 l_clev_tbl_in(i).chr_id := Null;
187 l_clev_tbl_in(i).cle_id := get_okc_lines_rec.id;
188 l_clev_tbl_in(i).dnz_chr_id := get_okc_lines_rec.dnz_chr_id;
189 l_clev_tbl_in(i).line_number :='1';
190 l_clev_tbl_in(i).sfwt_flag :='N';
191 l_clev_tbl_in(i).lse_id := 2;
192 l_clev_tbl_in(i).sts_code := get_coverages_rec.COVERAGE_TYPE_CODE;
193 l_clev_tbl_in(i).display_sequence :=1;
194 l_clev_tbl_in(i).exception_yn :='Y';
195 l_clev_tbl_in(i).item_description := get_coverages_rec.description;
196 l_clev_tbl_in(i).Name := get_coverages_rec.NAME;
197 l_clev_tbl_in(i).start_date := get_coverages_rec.start_date;
198 l_clev_tbl_in(i).end_date := get_coverages_rec.end_date;
199 l_clev_tbl_in(i).upg_orig_system_ref_id := get_coverages_rec.coverage_id;
200 l_clev_tbl_in(i).upg_orig_system_ref := g_covline_ref;
201 l_clev_tbl_in(i).currency_code := get_okc_lines_rec.currency_code;
202 -- l_clev_tbl_in(i).orig_system_source_code := NULL;
203 -- l_clev_tbl_in(i).orig_system_id1 := NULL;
204 l_clev_tbl_in(i).ATTRIBUTE_CATEGORY := get_coverages_rec.ATTRIBUTE_CATEGORY;
205 l_clev_tbl_in(i).attribute1 :=get_coverages_rec.Attribute1 ;
206 l_clev_tbl_in(i).attribute2 :=get_coverages_rec.Attribute2 ;
207 l_clev_tbl_in(i).attribute3 :=get_coverages_rec.Attribute3 ;
208 l_clev_tbl_in(i).attribute4 :=get_coverages_rec.Attribute4 ;
209 l_clev_tbl_in(i).attribute5 :=get_coverages_rec.Attribute5 ;
210 l_clev_tbl_in(i).attribute6 :=get_coverages_rec.Attribute6 ;
211 l_clev_tbl_in(i).attribute7 :=get_coverages_rec.Attribute7 ;
212 l_clev_tbl_in(i).attribute8 :=get_coverages_rec.Attribute8 ;
213 l_clev_tbl_in(i).attribute9 :=get_coverages_rec.Attribute9 ;
214 l_clev_tbl_in(i).attribute10 :=get_coverages_rec.Attribute10 ;
215 l_clev_tbl_in(i).attribute11 :=get_coverages_rec.Attribute11 ;
216 l_clev_tbl_in(i).attribute12 :=get_coverages_rec.Attribute12 ;
217 l_clev_tbl_in(i).attribute13 :=get_coverages_rec.Attribute13 ;
218 l_clev_tbl_in(i).attribute14 :=get_coverages_rec.Attribute14 ;
219 l_clev_tbl_in(i).attribute15 :=get_coverages_rec.Attribute15;
220 l_clev_tbl_in(i).INVOICE_LINE_LEVEL_IND:= Null;
221 l_clev_tbl_in(i).DPAS_RATING := Null;
222 l_clev_tbl_in(i).TEMPLATE_USED := Null;
223 l_clev_tbl_in(i).PRICE_TYPE := Null;
224 --l_clev_tbl_in(i).UOM_CODE :=Null;
225 l_clev_tbl_in(i).TRN_CODE := Null;
226 l_clev_tbl_in(i).HIDDEN_IND := Null;
227 l_clev_tbl_in(i).DATE_TERMINATED := Null;
228 l_clev_tbl_in(i).CLE_ID_RENEWED_TO := Null;
229 l_clev_tbl_in(i).CURRENCY_CODE_RENEWED := Null;
230 l_clev_tbl_in(i).PRICE_NEGOTIATED_RENEWED:= Null;
231
232 l_clev_tbl_in(i).program_application_id:= fnd_global.prog_appl_id;
233 l_clev_tbl_in(i).program_id:= fnd_global.CONC_PROGRAM_ID;
234
235 -- dbms_output.put_line('Before Insert');
236
237 If l_validate_flag = 'Y' THEN
238
239
240 If l_clev_tbl_in.count > 0 Then
241
242 okc_cle_pvt.Insert_Row(
243 p_api_version => l_api_version,
244 p_init_msg_list => l_init_msg_list,
245 x_return_status => l_return_status ,
246 x_msg_count => l_msg_count,
247 x_msg_data => l_msg_data,
248 p_clev_tbl => l_clev_tbl_in,
249 x_clev_tbl => x_clev_tbl_in);
250
251 dbms_output.put_line('Value of l_return_status='||l_return_status);
252 End If;
253 ELSE
254 okc_cle_pvt.Insert_Row_Upg( l_return_status , l_clev_tbl_in);
255
256 END IF;
257
258 -- l_clev_tbl_in.delete;
259 -- dbms_output.put_line('----222222222222');
260
261 -- Rule Group Creation----
262 l_rgpv_tbl_in(i).id :=okc_p_util.raw_to_number(sys_guid()); --Get_Seq_Id(get_coverages_rec.Coverage_ID);
263 -- dbms_output.put_line('Value of l_rgpv_tbl_in(i).id='||TO_CHAR(l_rgpv_tbl_in(i).id));
264 l_rgpv_tbl_in(i).CREATION_DATE := g_CREATION_DATE;
265 l_rgpv_tbl_in(i).CREATED_BY := -1;
266 l_rgpv_tbl_in(i).LAST_UPDATE_DATE := sysdate;
267 l_rgpv_tbl_in(i).LAST_UPDATED_BY := -1;
268 l_rgpv_tbl_in(i).LAST_UPDATE_LOGIN := -1;
269 l_rgpv_tbl_in(i).object_version_number := 1;
270 l_rgpv_tbl_in(i).cle_id := l_clev_tbl_in(i).Id;
271 -- dbms_output.put_line('Value of l_rgpv_tbl_in(i).cle_id='||TO_CHAR(l_rgpv_tbl_in(i).cle_id));
272 l_rgpv_tbl_in(i).dnz_chr_id := l_clev_tbl_In(i).dnz_chr_Id;
273 -- dbms_output.put_line('Value of l_rgpv_tbl_in(i).dnz_chr_id='||TO_CHAR(l_rgpv_tbl_in(i).dnz_chr_id));
274 l_rgpv_tbl_in(i).sfwt_flag :='N';
275 l_rgpv_tbl_in(i).rgd_code := 'SVC_K';
276 l_rgpv_tbl_in(i).rgp_type := 'KRG';
277 -- dbms_output.put_line('333333333333333');
278 l_rgpv_tbl_in(i).CHR_ID :=Null;
279 l_rgpv_tbl_in(i).PARENT_RGP_ID :=Null;
280 l_rgpv_tbl_in(i).SAT_CODE :=Null;
281 l_rgpv_tbl_in(i).COMMENTS :=Null;
282 l_rgpv_tbl_in(i).ATTRIBUTE_CATEGORY :=Null;
283 l_rgpv_tbl_in(i).ATTRIBUTE1 :=Null;
284 l_rgpv_tbl_in(i).ATTRIBUTE2 :=Null;
285 l_rgpv_tbl_in(i).ATTRIBUTE3 :=Null;
286 l_rgpv_tbl_in(i).ATTRIBUTE4 :=Null;
287 l_rgpv_tbl_in(i).ATTRIBUTE5 :=Null;
288 l_rgpv_tbl_in(i).ATTRIBUTE6 :=Null;
289 l_rgpv_tbl_in(i).ATTRIBUTE7 :=Null;
290 l_rgpv_tbl_in(i).ATTRIBUTE8 :=Null;
291 l_rgpv_tbl_in(i).ATTRIBUTE9 :=Null;
292 l_rgpv_tbl_in(i).ATTRIBUTE10 :=Null;
293 l_rgpv_tbl_in(i).ATTRIBUTE11 :=Null;
294 l_rgpv_tbl_in(i).ATTRIBUTE12 :=Null;
295 l_rgpv_tbl_in(i).ATTRIBUTE13 :=Null;
296 l_rgpv_tbl_in(i).ATTRIBUTE14 :=Null;
297 l_rgpv_tbl_in(i).ATTRIBUTE15 :=Null;
298 -- dbms_output.put_line('4444444444444');
299 If l_validate_flag = 'Y' THEN
300
301
302 If l_rgpv_tbl_in.count > 0 Then
303
304 okc_rgp_pvt.Insert_Row(
305 p_api_version => l_api_version,
306 p_init_msg_list => l_init_msg_list,
307 x_return_status => l_return_status ,
308 x_msg_count => l_msg_count,
309 x_msg_data => l_msg_data,
310 p_rgpv_tbl => l_rgpv_tbl_in,
311 x_rgpv_tbl => x_rgpv_tbl_in);
312
313 dbms_output.put_line('Value of l_return_status='||l_return_status);
314 End If;
315 ELSE
316 okc_rgp_pvt.Insert_Row_Upg( l_return_status , l_rgpv_tbl_in);
317
318 END IF;
319
320
321 -- dbms_output.put_line('---55555555555');
322 /*Inserting Rule Informations */
323 --OFS Rule
324 l_rulv_tbl_in(i).rgp_id := l_rgpv_tbl_in(i).id;
325 l_rulv_tbl_in(i).sfwt_flag := 'N';
326 l_rulv_tbl_in(i).rule_information_category := 'OFS';
327 l_rulv_tbl_in(i).rule_information1 := okc_p_util.raw_to_number(sys_guid());
328 l_rulv_tbl_in(i).std_template_yn := 'N';
329 l_rulv_tbl_in(i).warn_yn := 'Y';
330 l_rulv_tbl_in(i).dnz_chr_id := l_clev_tbl_In(i).dnz_chr_Id;
331 l_rulv_tbl_in(i).id := okc_p_util.raw_to_number(sys_guid()); --Get_Seq_Id(get_coverages_rec.Coverage_ID);
332 l_rulv_tbl_in(i).CREATION_DATE := sysdate;
333 l_rulv_tbl_in(i).CREATED_BY := -1;
334 l_rulv_tbl_in(i).LAST_UPDATE_DATE := sysdate;
335 l_rulv_tbl_in(i).LAST_UPDATED_BY := -1;
336 l_rulv_tbl_in(i).LAST_UPDATE_LOGIN := -1;
337 l_rulv_tbl_in(i).object_version_number := 1;
338 l_rulv_tbl_in(i).PRIORITY := NULL;
339 l_rulv_tbl_in(i).OBJECT1_ID1 := NULL;
340 l_rulv_tbl_in(i).PRIORITY := NULL;
341 l_rulv_tbl_in(i).OBJECT2_ID1 := NULL;
342 l_rulv_tbl_in(i).OBJECT3_ID1 := NULL;
343 l_rulv_tbl_in(i).OBJECT2_ID2 := NULL;
344 l_rulv_tbl_in(i).OBJECT3_ID2 := NULL;
345 l_rulv_tbl_in(i).OBJECT1_ID2 := NULL;
346 l_rulv_tbl_in(i).JTOT_OBJECT1_CODE := NULL;
347 l_rulv_tbl_in(i).JTOT_OBJECT2_CODE := NULL;
348 l_rulv_tbl_in(i).JTOT_OBJECT3_CODE := NULL;
349 l_rulv_tbl_in(i).PRIORITY := NULL;
350 l_rulv_tbl_in(i).COMMENTS := NULL;
351 l_rulv_tbl_in(i).ATTRIBUTE_CATEGORY := NULL;
352 l_rulv_tbl_in(i).ATTRIBUTE1 := NULL;
353 l_rulv_tbl_in(i).ATTRIBUTE2 := NULL;
354 l_rulv_tbl_in(i).ATTRIBUTE3 := NULL;
355 l_rulv_tbl_in(i).ATTRIBUTE4 := NULL;
356 l_rulv_tbl_in(i).ATTRIBUTE5 := NULL;
357 l_rulv_tbl_in(i).ATTRIBUTE6 := NULL;
358 l_rulv_tbl_in(i).ATTRIBUTE7 := NULL;
359 l_rulv_tbl_in(i).ATTRIBUTE8 := NULL;
360 l_rulv_tbl_in(i).ATTRIBUTE9 := NULL;
361 l_rulv_tbl_in(i).ATTRIBUTE10 := NULL;
362 l_rulv_tbl_in(i).ATTRIBUTE11 := NULL;
363 l_rulv_tbl_in(i).ATTRIBUTE12 := NULL;
364 l_rulv_tbl_in(i).ATTRIBUTE13 := NULL;
365 l_rulv_tbl_in(i).ATTRIBUTE14 := NULL;
366 l_rulv_tbl_in(i).ATTRIBUTE15 := NULL;
367 l_rulv_tbl_in(i).TEXT := NULL;
368 l_rulv_tbl_in(i).RULE_INFORMATION2 := NULL;
369 l_rulv_tbl_in(i).RULE_INFORMATION3 := NULL;
370 l_rulv_tbl_in(i).RULE_INFORMATION4 := NULL;
371 l_rulv_tbl_in(i).RULE_INFORMATION5 := NULL;
372 l_rulv_tbl_in(i).RULE_INFORMATION6 := NULL;
373 l_rulv_tbl_in(i).RULE_INFORMATION7 := NULL;
374 l_rulv_tbl_in(i).RULE_INFORMATION8 := NULL;
375 l_rulv_tbl_in(i).RULE_INFORMATION9 := NULL;
376 l_rulv_tbl_in(i).RULE_INFORMATION10 := NULL;
377 l_rulv_tbl_in(i).RULE_INFORMATION11 := NULL;
378 l_rulv_tbl_in(i).RULE_INFORMATION12 := NULL;
379 l_rulv_tbl_in(i).RULE_INFORMATION13 := NULL;
380 l_rulv_tbl_in(i).RULE_INFORMATION14 := NULL;
381 l_rulv_tbl_in(i).RULE_INFORMATION15 := NULL;
382
383 If l_validate_flag = 'Y' THEN
384
385
386 If l_rulv_tbl_in.count > 0 Then
387
388 okc_rul_pvt.Insert_Row(
389 p_api_version => l_api_version,
390 p_init_msg_list => l_init_msg_list,
391 x_return_status => l_return_status ,
392 x_msg_count => l_msg_count,
393 x_msg_data => l_msg_data,
394 p_rulv_tbl => l_rulv_tbl_in,
395 x_rulv_tbl => x_rulv_tbl_in);
396
397 dbms_output.put_line('Value of l_return_status='||l_return_status);
398 End If;
399 ELSE
400 okc_rul_pvt.Insert_Row_Upg( l_return_status , l_rulv_tbl_in);
401
402 END IF;
403
404
405 okc_time_util_pub.get_duration
406 (
407 p_start_date => get_okc_lines_rec.start_date,
408 p_end_date => get_okc_lines_rec.end_date,
409 x_duration => l_duration,
410 x_timeunit => l_timeunits,
411 x_return_status => l_return_status
412 );
413 -- dbms_output.put_line('232323323232');
414 l_isev_ext_tbl_in(i).id :=l_rulv_tbl_in(i).rule_information1;
415 l_isev_ext_tbl_in(i).object_version_number :=1;
416 l_isev_ext_tbl_in(i).sfwt_flag :='N';
417 l_isev_ext_tbl_in(i).spn_id :=Null;
418 l_isev_ext_tbl_in(i).uom_code :=l_timeunits;
419 l_isev_ext_tbl_in(i).tve_id_started :=okc_p_util.raw_to_number(sys_guid());
420 l_isev_ext_tbl_in(i).tve_id_ended :=Null;
421 l_isev_ext_tbl_in(i).tve_id_limited :=Null;
422 l_isev_ext_tbl_in(i).dnz_chr_id :=l_clev_tbl_In(i).dnz_chr_Id;
423 l_isev_ext_tbl_in(i).tze_id :=Null;
424 l_isev_ext_tbl_in(i).description :=Null;
425 l_isev_ext_tbl_in(i).short_description :=Null;
426 l_isev_ext_tbl_in(i).comments :=Null;
427 l_isev_ext_tbl_in(i).duration :=l_duration;
428 l_isev_ext_tbl_in(i).operator :=Null;
429 l_isev_ext_tbl_in(i).before_after :=Null;
430 l_isev_ext_tbl_in(i).attribute_category :=Null;
431 l_isev_ext_tbl_in(i).attribute1 :=Null;
432 l_isev_ext_tbl_in(i).attribute2 :=Null;
433 l_isev_ext_tbl_in(i).attribute3 :=Null;
434 l_isev_ext_tbl_in(i).attribute4 :=Null;
435 l_isev_ext_tbl_in(i).attribute5 :=Null;
436 l_isev_ext_tbl_in(i).attribute6 :=Null;
437 l_isev_ext_tbl_in(i).attribute7 :=Null;
438 l_isev_ext_tbl_in(i).attribute8 :=Null;
439 l_isev_ext_tbl_in(i).attribute9 :=Null;
440 l_isev_ext_tbl_in(i).attribute10 :=Null;
441 l_isev_ext_tbl_in(i).attribute11 :=Null;
442 l_isev_ext_tbl_in(i).attribute12 :=Null;
443 l_isev_ext_tbl_in(i).attribute13 :=Null;
444 l_isev_ext_tbl_in(i).attribute14 :=Null;
445 l_isev_ext_tbl_in(i).attribute15 :=Null;
446 l_isev_ext_tbl_in(i).CREATION_DATE := sysdate;
447 l_isev_ext_tbl_in(i).CREATED_BY := -1;
448 l_isev_ext_tbl_in(i).LAST_UPDATE_DATE := sysdate;
449 l_isev_ext_tbl_in(i).LAST_UPDATED_BY := -1;
450 l_isev_ext_tbl_in(i).LAST_UPDATE_LOGIN := -1;
451
452 okc_time_pvt.Insert_ise_Row_Upg( l_isev_ext_tbl_in);
453
454 l_rulv_tbl_in.delete;
455 l_isev_ext_tbl_in.delete;
456 --UGE Rule
457 if (get_coverages_rec.FREE_UPGRADE_YN = 'Y') then
458 l_upg_flag := 'Y';
459 end if;
460 l_rulv_tbl_in(i).rgp_id := l_rgpv_tbl_in(i).id;
461 l_rulv_tbl_in(i).sfwt_flag := 'N';
462 l_rulv_tbl_in(i).rule_information_category := 'UGE';
463 l_rulv_tbl_in(i).rule_information1 := l_upg_flag ;
464
465 l_rulv_tbl_in(i).std_template_yn := 'N';
466 l_rulv_tbl_in(i).warn_yn := 'Y';
467 l_rulv_tbl_in(i).dnz_chr_id := l_clev_tbl_In(i).dnz_chr_Id;
468 l_rulv_tbl_in(i).id := okc_p_util.raw_to_number(sys_guid()); --Get_Seq_Id(get_coverages_rec.Coverage_ID);
469 l_rulv_tbl_in(i).CREATION_DATE := sysdate;
470 l_rulv_tbl_in(i).CREATED_BY := -1;
471 l_rulv_tbl_in(i).LAST_UPDATE_DATE := sysdate;
472 l_rulv_tbl_in(i).LAST_UPDATED_BY := -1;
473 l_rulv_tbl_in(i).LAST_UPDATE_LOGIN := -1;
474 l_rulv_tbl_in(i).object_version_number := 1;
475 l_rulv_tbl_in(i).PRIORITY := NULL;
476 l_rulv_tbl_in(i).OBJECT1_ID1 := NULL;
477 l_rulv_tbl_in(i).PRIORITY := NULL;
478 l_rulv_tbl_in(i).OBJECT2_ID1 := NULL;
479 l_rulv_tbl_in(i).OBJECT3_ID1 := NULL;
480 l_rulv_tbl_in(i).OBJECT2_ID2 := NULL;
481 l_rulv_tbl_in(i).OBJECT3_ID2 := NULL;
482 l_rulv_tbl_in(i).OBJECT1_ID2 := NULL;
483 l_rulv_tbl_in(i).JTOT_OBJECT1_CODE := NULL;
484 l_rulv_tbl_in(i).JTOT_OBJECT2_CODE := NULL;
485 l_rulv_tbl_in(i).JTOT_OBJECT3_CODE := NULL;
486 l_rulv_tbl_in(i).PRIORITY := NULL;
487 l_rulv_tbl_in(i).COMMENTS := NULL;
488 l_rulv_tbl_in(i).ATTRIBUTE_CATEGORY := NULL;
489 l_rulv_tbl_in(i).ATTRIBUTE1 := NULL;
490 l_rulv_tbl_in(i).ATTRIBUTE2 := NULL;
491 l_rulv_tbl_in(i).ATTRIBUTE3 := NULL;
492 l_rulv_tbl_in(i).ATTRIBUTE4 := NULL;
493 l_rulv_tbl_in(i).ATTRIBUTE5 := NULL;
494 l_rulv_tbl_in(i).ATTRIBUTE6 := NULL;
495 l_rulv_tbl_in(i).ATTRIBUTE7 := NULL;
496 l_rulv_tbl_in(i).ATTRIBUTE8 := NULL;
497 l_rulv_tbl_in(i).ATTRIBUTE9 := NULL;
498 l_rulv_tbl_in(i).ATTRIBUTE10 := NULL;
499 l_rulv_tbl_in(i).ATTRIBUTE11 := NULL;
500 l_rulv_tbl_in(i).ATTRIBUTE12 := NULL;
501 l_rulv_tbl_in(i).ATTRIBUTE13 := NULL;
502 l_rulv_tbl_in(i).ATTRIBUTE14 := NULL;
503 l_rulv_tbl_in(i).ATTRIBUTE15 := NULL;
504 l_rulv_tbl_in(i).TEXT := NULL;
505 l_rulv_tbl_in(i).RULE_INFORMATION2 := NULL;
506 l_rulv_tbl_in(i).RULE_INFORMATION3 := NULL;
507 l_rulv_tbl_in(i).RULE_INFORMATION4 := NULL;
508 l_rulv_tbl_in(i).RULE_INFORMATION5 := NULL;
509 l_rulv_tbl_in(i).RULE_INFORMATION6 := NULL;
510 l_rulv_tbl_in(i).RULE_INFORMATION7 := NULL;
511 l_rulv_tbl_in(i).RULE_INFORMATION8 := NULL;
512 l_rulv_tbl_in(i).RULE_INFORMATION9 := NULL;
513 l_rulv_tbl_in(i).RULE_INFORMATION10 := NULL;
514 l_rulv_tbl_in(i).RULE_INFORMATION11 := NULL;
515 l_rulv_tbl_in(i).RULE_INFORMATION12 := NULL;
516 l_rulv_tbl_in(i).RULE_INFORMATION13 := NULL;
517 l_rulv_tbl_in(i).RULE_INFORMATION14 := NULL;
518 l_rulv_tbl_in(i).RULE_INFORMATION15 := NULL;
519 If l_validate_flag = 'Y' THEN
520
521 If l_rulv_tbl_in.count > 0 Then
522
523 okc_rul_pvt.Insert_Row(
524 p_api_version => l_api_version,
525 p_init_msg_list => l_init_msg_list,
526 x_return_status => l_return_status ,
527 x_msg_count => l_msg_count,
528 x_msg_data => l_msg_data,
529 p_rulv_tbl => l_rulv_tbl_in,
530 x_rulv_tbl => x_rulv_tbl_in);
531
532 dbms_output.put_line('Value of l_return_status='||l_return_status);
533 End If;
534 ELSE
535 okc_rul_pvt.Insert_Row_Upg( l_return_status , l_rulv_tbl_in);
536
537 END IF;
538
539 l_rulv_tbl_in.delete;
540
541 --STR Rule
542 if (get_coverages_rec.TRANSFER_ALLOWED_YN = 'Y') then
543 l_transfer_all_flag := 'Y';
544 end if;
545 l_rulv_tbl_in(i).rgp_id := l_rgpv_tbl_in(i).id;
546 l_rulv_tbl_in(i).sfwt_flag := 'N';
547 l_rulv_tbl_in(i).rule_information_category := 'STR';
548 l_rulv_tbl_in(i).rule_information1 := l_transfer_all_flag;
549 l_rulv_tbl_in(i).std_template_yn := 'N';
550 l_rulv_tbl_in(i).warn_yn := 'Y';
551 l_rulv_tbl_in(i).dnz_chr_id := l_clev_tbl_In(i).dnz_chr_Id;
552 l_rulv_tbl_in(i).id := okc_p_util.raw_to_number(sys_guid()); --Get_Seq_Id(get_coverages_rec.Coverage_ID);
553 l_rulv_tbl_in(i).CREATION_DATE := sysdate;
554 l_rulv_tbl_in(i).CREATED_BY := -1;
555 l_rulv_tbl_in(i).LAST_UPDATE_DATE := sysdate;
556 l_rulv_tbl_in(i).LAST_UPDATED_BY := -1;
557 l_rulv_tbl_in(i).LAST_UPDATE_LOGIN := -1;
558 l_rulv_tbl_in(i).object_version_number := 1;
559 l_rulv_tbl_in(i).PRIORITY := NULL;
560 l_rulv_tbl_in(i).OBJECT1_ID1 := NULL;
561 l_rulv_tbl_in(i).PRIORITY := NULL;
562 l_rulv_tbl_in(i).OBJECT1_ID2 := NULL;
563 l_rulv_tbl_in(i).JTOT_OBJECT1_CODE := NULL;
564 l_rulv_tbl_in(i).OBJECT2_ID1 := NULL;
565 l_rulv_tbl_in(i).OBJECT3_ID1 := NULL;
566 l_rulv_tbl_in(i).OBJECT2_ID2 := NULL;
567 l_rulv_tbl_in(i).OBJECT3_ID2 := NULL;
568 l_rulv_tbl_in(i).JTOT_OBJECT2_CODE := NULL;
569 l_rulv_tbl_in(i).JTOT_OBJECT3_CODE := NULL;
570 l_rulv_tbl_in(i).PRIORITY := NULL;
571 l_rulv_tbl_in(i).COMMENTS := NULL;
572 l_rulv_tbl_in(i).ATTRIBUTE_CATEGORY := NULL;
573 l_rulv_tbl_in(i).ATTRIBUTE1 := NULL;
574 l_rulv_tbl_in(i).ATTRIBUTE2 := NULL;
575 l_rulv_tbl_in(i).ATTRIBUTE3 := NULL;
576 l_rulv_tbl_in(i).ATTRIBUTE4 := NULL;
577 l_rulv_tbl_in(i).ATTRIBUTE5 := NULL;
578 l_rulv_tbl_in(i).ATTRIBUTE6 := NULL;
579 l_rulv_tbl_in(i).ATTRIBUTE7 := NULL;
580 l_rulv_tbl_in(i).ATTRIBUTE8 := NULL;
581 l_rulv_tbl_in(i).ATTRIBUTE9 := NULL;
582 l_rulv_tbl_in(i).ATTRIBUTE10 := NULL;
583 l_rulv_tbl_in(i).ATTRIBUTE11 := NULL;
584 l_rulv_tbl_in(i).ATTRIBUTE12 := NULL;
585 l_rulv_tbl_in(i).ATTRIBUTE13 := NULL;
586 l_rulv_tbl_in(i).ATTRIBUTE14 := NULL;
587 l_rulv_tbl_in(i).ATTRIBUTE15 := NULL;
588 l_rulv_tbl_in(i).TEXT := NULL;
589 l_rulv_tbl_in(i).RULE_INFORMATION2 := NULL;
590 l_rulv_tbl_in(i).RULE_INFORMATION3 := NULL;
591 l_rulv_tbl_in(i).RULE_INFORMATION4 := NULL;
592 l_rulv_tbl_in(i).RULE_INFORMATION5 := NULL;
593 l_rulv_tbl_in(i).RULE_INFORMATION6 := NULL;
594 l_rulv_tbl_in(i).RULE_INFORMATION7 := NULL;
595 l_rulv_tbl_in(i).RULE_INFORMATION8 := NULL;
596 l_rulv_tbl_in(i).RULE_INFORMATION9 := NULL;
597 l_rulv_tbl_in(i).RULE_INFORMATION10 := NULL;
598 l_rulv_tbl_in(i).RULE_INFORMATION11 := NULL;
599 l_rulv_tbl_in(i).RULE_INFORMATION12 := NULL;
600 l_rulv_tbl_in(i).RULE_INFORMATION13 := NULL;
601 l_rulv_tbl_in(i).RULE_INFORMATION14 := NULL;
602 l_rulv_tbl_in(i).RULE_INFORMATION15 := NULL;
603 If l_validate_flag = 'Y' THEN
604 If l_rulv_tbl_in.count > 0 Then
605
606 okc_rul_pvt.Insert_Row(
607 p_api_version => l_api_version,
608 p_init_msg_list => l_init_msg_list,
609 x_return_status => l_return_status ,
610 x_msg_count => l_msg_count,
611 x_msg_data => l_msg_data,
612 p_rulv_tbl => l_rulv_tbl_in,
613 x_rulv_tbl => x_rulv_tbl_in);
614
615 dbms_output.put_line('Value of l_return_status='||l_return_status);
616 End If;
617 ELSE
618 okc_rul_pvt.Insert_Row_Upg( l_return_status , l_rulv_tbl_in);
619
620 END IF;
621 l_rulv_tbl_in.delete;
622
623 IF get_coverages_rec.COVERAGE_TYPE_CODE IS NOT NULL THEN
624
625 l_rulv_tbl_in(i).rgp_id := l_rgpv_tbl_in(i).id;
626 l_rulv_tbl_in(i).sfwt_flag := 'N';
627 l_rulv_tbl_in(i).rule_information_category := 'CVE';
628 l_rulv_tbl_in(i).rule_information1 := Get_Cov_Type(get_coverages_rec.COVERAGE_TYPE_CODE);
629 l_rulv_tbl_in(i).std_template_yn := 'N';
630 l_rulv_tbl_in(i).warn_yn := 'Y';
631 l_rulv_tbl_in(i).dnz_chr_id := l_clev_tbl_In(i).dnz_chr_Id;
632 l_rulv_tbl_in(i).id := okc_p_util.raw_to_number(sys_guid());
633 l_rulv_tbl_in(i).CREATION_DATE := sysdate;
634 l_rulv_tbl_in(i).CREATED_BY := -1;
635 l_rulv_tbl_in(i).LAST_UPDATE_DATE := sysdate;
636 l_rulv_tbl_in(i).LAST_UPDATED_BY := -1;
637 l_rulv_tbl_in(i).LAST_UPDATE_LOGIN := -1;
638 l_rulv_tbl_in(i).object_version_number := 1;
639 l_rulv_tbl_in(i).PRIORITY := NULL;
640 l_rulv_tbl_in(i).OBJECT1_ID1 := NULL;
641 l_rulv_tbl_in(i).PRIORITY := NULL;
642 l_rulv_tbl_in(i).OBJECT2_ID1 := NULL;
643 l_rulv_tbl_in(i).OBJECT3_ID1 := NULL;
644 l_rulv_tbl_in(i).OBJECT2_ID2 := NULL;
645 l_rulv_tbl_in(i).OBJECT3_ID2 := NULL;
646 l_rulv_tbl_in(i).JTOT_OBJECT2_CODE := NULL;
647 l_rulv_tbl_in(i).JTOT_OBJECT3_CODE := NULL;
648 l_rulv_tbl_in(i).PRIORITY := NULL;
649 l_rulv_tbl_in(i).COMMENTS := NULL;
650 l_rulv_tbl_in(i).ATTRIBUTE_CATEGORY := NULL;
651 l_rulv_tbl_in(i).ATTRIBUTE1 := NULL;
652 l_rulv_tbl_in(i).ATTRIBUTE2 := NULL;
653 l_rulv_tbl_in(i).ATTRIBUTE3 := NULL;
654 l_rulv_tbl_in(i).ATTRIBUTE4 := NULL;
655 l_rulv_tbl_in(i).ATTRIBUTE5 := NULL;
656 l_rulv_tbl_in(i).ATTRIBUTE6 := NULL;
657 l_rulv_tbl_in(i).ATTRIBUTE7 := NULL;
658 l_rulv_tbl_in(i).ATTRIBUTE8 := NULL;
659 l_rulv_tbl_in(i).ATTRIBUTE9 := NULL;
660 l_rulv_tbl_in(i).ATTRIBUTE10 := NULL;
661 l_rulv_tbl_in(i).ATTRIBUTE11 := NULL;
662 l_rulv_tbl_in(i).ATTRIBUTE12 := NULL;
663 l_rulv_tbl_in(i).ATTRIBUTE13 := NULL;
664 l_rulv_tbl_in(i).ATTRIBUTE14 := NULL;
665 l_rulv_tbl_in(i).ATTRIBUTE15 := NULL;
666 l_rulv_tbl_in(i).TEXT := NULL;
667 l_rulv_tbl_in(i).RULE_INFORMATION2 := NULL;
668 l_rulv_tbl_in(i).RULE_INFORMATION3 := NULL;
669 l_rulv_tbl_in(i).RULE_INFORMATION4 := NULL;
670 l_rulv_tbl_in(i).RULE_INFORMATION5 := NULL;
671 l_rulv_tbl_in(i).RULE_INFORMATION6 := NULL;
672 l_rulv_tbl_in(i).RULE_INFORMATION7 := NULL;
673 l_rulv_tbl_in(i).RULE_INFORMATION8 := NULL;
674 l_rulv_tbl_in(i).RULE_INFORMATION9 := NULL;
675 l_rulv_tbl_in(i).RULE_INFORMATION10 := NULL;
676 l_rulv_tbl_in(i).RULE_INFORMATION11 := NULL;
677 l_rulv_tbl_in(i).RULE_INFORMATION12 := NULL;
678 l_rulv_tbl_in(i).RULE_INFORMATION13 := NULL;
679 l_rulv_tbl_in(i).RULE_INFORMATION14 := NULL;
680 l_rulv_tbl_in(i).RULE_INFORMATION15 := NULL;
681
682 -- okc_rul_pvt.Insert_Row_Upg( l_return_status , l_rulv_tbl_in);
683 If l_validate_flag = 'Y' THEN
684 If l_rulv_tbl_in.count > 0 Then
685
686 okc_rul_pvt.Insert_Row(
687 p_api_version => l_api_version,
688 p_init_msg_list => l_init_msg_list,
689 x_return_status => l_return_status ,
690 x_msg_count => l_msg_count,
691 x_msg_data => l_msg_data,
692 p_rulv_tbl => l_rulv_tbl_in,
693 x_rulv_tbl => x_rulv_tbl_in);
694
695 dbms_output.put_line('Value of l_return_status='||l_return_status);
696 End If;
697 ELSE
698 okc_rul_pvt.Insert_Row_Upg( l_return_status , l_rulv_tbl_in);
699
700 END IF;
701 l_rulv_tbl_in.delete;
702 END IF; -- End of CVE Rule COVERAGE_TYPE_CODE --
703
704 IF get_coverages_rec.EXC_COVERAGE_ID IS NOT NULL then
705 -- dbms_output.put_line('6666666666666');
706 l_rulv_tbl_in(i).rgp_id := l_rgpv_tbl_in(i).id;
707 l_rulv_tbl_in(i).sfwt_flag := 'N';
708 l_rulv_tbl_in(i).rule_information_category := 'ECE';
709 l_rulv_tbl_in(i).rule_information1 := get_coverages_rec.EXC_COVERAGE_ID;
710 l_rulv_tbl_in(i).std_template_yn := 'N';
711 l_rulv_tbl_in(i).warn_yn := 'Y';
712 l_rulv_tbl_in(i).dnz_chr_id := l_clev_tbl_In(i).dnz_chr_Id;
713 l_rulv_tbl_in(i).id := okc_p_util.raw_to_number(sys_guid()); --Get_Seq_Id(get_coverages_rec.Coverage_ID);
714 l_rulv_tbl_in(i).CREATION_DATE := g_CREATION_DATE;
715 l_rulv_tbl_in(i).CREATED_BY := -1;
716 l_rulv_tbl_in(i).LAST_UPDATE_DATE := sysdate;
717 l_rulv_tbl_in(i).LAST_UPDATED_BY := -1;
718 l_rulv_tbl_in(i).LAST_UPDATE_LOGIN := -1;
719 l_rulv_tbl_in(i).object_version_number := 1;
720 l_rulv_tbl_in(i).OBJECT2_ID1 := NULL;
721 l_rulv_tbl_in(i).OBJECT3_ID1 := NULL;
722 l_rulv_tbl_in(i).OBJECT2_ID2 := NULL;
723 l_rulv_tbl_in(i).OBJECT3_ID2 := NULL;
724 l_rulv_tbl_in(i).JTOT_OBJECT2_CODE := NULL;
725 l_rulv_tbl_in(i).JTOT_OBJECT3_CODE := NULL;
726 l_rulv_tbl_in(i).PRIORITY := NULL;
727 l_rulv_tbl_in(i).COMMENTS := NULL;
728 l_rulv_tbl_in(i).ATTRIBUTE_CATEGORY := NULL;
729 l_rulv_tbl_in(i).ATTRIBUTE1 := NULL;
730 l_rulv_tbl_in(i).ATTRIBUTE2 := NULL;
731 l_rulv_tbl_in(i).ATTRIBUTE3 := NULL;
732 l_rulv_tbl_in(i).ATTRIBUTE4 := NULL;
733 l_rulv_tbl_in(i).ATTRIBUTE5 := NULL;
734 l_rulv_tbl_in(i).ATTRIBUTE6 := NULL;
735 l_rulv_tbl_in(i).ATTRIBUTE7 := NULL;
736 l_rulv_tbl_in(i).ATTRIBUTE8 := NULL;
737 l_rulv_tbl_in(i).ATTRIBUTE9 := NULL;
738 l_rulv_tbl_in(i).ATTRIBUTE10 := NULL;
739 l_rulv_tbl_in(i).ATTRIBUTE11 := NULL;
740 l_rulv_tbl_in(i).ATTRIBUTE12 := NULL;
741 l_rulv_tbl_in(i).ATTRIBUTE13 := NULL;
742 l_rulv_tbl_in(i).ATTRIBUTE14 := NULL;
743 l_rulv_tbl_in(i).ATTRIBUTE15 := NULL;
744 l_rulv_tbl_in(i).TEXT := NULL;
745 l_rulv_tbl_in(i).RULE_INFORMATION2 := NULL;
746 l_rulv_tbl_in(i).RULE_INFORMATION3 := NULL;
747 l_rulv_tbl_in(i).RULE_INFORMATION4 := NULL;
748 l_rulv_tbl_in(i).RULE_INFORMATION5 := NULL;
749 l_rulv_tbl_in(i).RULE_INFORMATION6 := NULL;
750 l_rulv_tbl_in(i).RULE_INFORMATION7 := NULL;
751 l_rulv_tbl_in(i).RULE_INFORMATION8 := NULL;
752 l_rulv_tbl_in(i).RULE_INFORMATION9 := NULL;
753 l_rulv_tbl_in(i).RULE_INFORMATION10 := NULL;
754 l_rulv_tbl_in(i).RULE_INFORMATION11 := NULL;
755 l_rulv_tbl_in(i).RULE_INFORMATION12 := NULL;
756 l_rulv_tbl_in(i).RULE_INFORMATION13 := NULL;
757 l_rulv_tbl_in(i).RULE_INFORMATION14 := NULL;
758 l_rulv_tbl_in(i).RULE_INFORMATION15 := NULL;
759
760
761 -- okc_rul_pvt.Insert_Row_Upg( l_return_status , l_rulv_tbl_in);
762 If l_validate_flag = 'Y' THEN
763 If l_rulv_tbl_in.count > 0 Then
764
765 okc_rul_pvt.Insert_Row(
766 p_api_version => l_api_version,
767 p_init_msg_list => l_init_msg_list,
768 x_return_status => l_return_status ,
769 x_msg_count => l_msg_count,
770 x_msg_data => l_msg_data,
771 p_rulv_tbl => l_rulv_tbl_in,
772 x_rulv_tbl => x_rulv_tbl_in);
773
774 dbms_output.put_line('Value of l_return_status='||l_return_status);
775 End If;
776 ELSE
777 okc_rul_pvt.Insert_Row_Upg( l_return_status , l_rulv_tbl_in);
778
779 END IF;
780 l_rulv_tbl_in.delete;
781 end if ;---End rule for EXC_COVERAGE_ID (EXECPTION COVERAGE ID)
782
783 /*Complete Inserting Rule Informations */
784
785
786 -- dbms_output.put_line('Before 0 Commit');
787 commit;
788 l_clev_tbl_in.delete;
789 l_rgpv_tbl_in.delete;
790 -- dbms_output.put_line('After 0 Delete');
791
792 end loop;
793 UPDATE oks_coverages_int_all
794 SET INTERFACED_STATUS_FLAG = 'S',
795 LAST_UPDATED_BY = -1,
796 LAST_UPDATE_DATE = sysdate,
797 LAST_UPDATE_LOGIN = -1
798 WHERE COVERAGE_ID = get_coverages_rec.coverage_id;
799
800 if (get_okc_lines_cur%notfound) then
801 close get_okc_lines_cur;
802 end if;
803
804 end loop;
805
806
807 if (get_coverages_cur%notfound)
808 then
809 close get_coverages_cur;
810 end if;
811 -- dbms_output.put_line('Before 1 Commit');
812 commit;
813 --end loop; --get_cov_id_cur
814 exception when others then
815 ROLLBACK;
816 RAISE_APPLICATION_ERROR(-20000,'Error in Business Process Interface');
817
818 end coverage_migrate;
819
820
821 PROCEDURE Business_Processes_migrate(P_FromId IN NUMBER,
822 P_ToId IN NUMBER,
823 P_VALIDATE_FLAG IN VARCHAR2,
824 P_LOG_PARAMETER IN VARCHAR2) IS
825
826 CURSOR get_buss_process_cur (p_fromid IN NUMBER,p_toid IN NUMBER) IS
827 SELECT
828 obp.coverage_bus_process_id,
829 obp.coverage_id,
830 obp.business_process_id,
831 obp.offset_duration,
832 obp.offset_period,
833 obp.start_date,
834 obp.end_date,
835 obp.price_list_id,
836 obp.discount_id,
837 obp.coverage_sunday_start_time,
838 obp.coverage_sunday_end_time,
839 obp.coverage_monday_start_time,
840 obp.coverage_monday_end_time,
841 obp.coverage_tuesday_start_time,
842 obp.coverage_tuesday_end_time,
843 obp.coverage_wednesday_start_time,
844 obp.coverage_wednesday_end_time,
845 obp.coverage_thursday_start_time,
846 obp.coverage_thursday_end_time,
847 obp.coverage_friday_start_time,
848 obp.coverage_friday_end_time,
849 obp.coverage_saturday_start_time,
850 obp.coverage_saturday_end_time,
851 obp.time_zone_id,
852 obp.reaction_name,
853 obp.reaction_description,
854 obp.reaction_time_id,
855 obp.reaction_severity_code,
856 obp.Incident_severity_id,
857 obp.sunday_reaction_times,
858 obp.monday_reaction_times,
859 obp.tuesday_reaction_times,
860 obp.wednesday_reaction_times,
861 obp.thursday_reaction_times,
862 obp.friday_reaction_times,
863 obp.saturday_reaction_times,
864 obp.work_through_yn,
865 obp.active_yn,
866 obp.sunday_resolution_times,
867 obp.monday_resolution_times,
868 obp.tuesday_resolution_times,
869 obp.wednesday_resolution_times,
870 obp.thursday_resolution_times,
871 obp.friday_resolution_times,
872 obp.saturday_resolution_times,
873 obp.preferred_resource_type,
874 obp.preferred_resource_id,
875 obp.attribute_category,
876 obp.attribute1,
877 obp.attribute2,
878 obp.attribute3,
879 obp.attribute4,
880 obp.attribute5,
881 obp.attribute6,
882 obp.attribute7,
883 obp.attribute8,
884 obp.attribute9,
885 obp.attribute10,
886 obp.attribute11,
887 obp.attribute12,
888 obp.attribute13,
889 obp.attribute14,
890 obp.attribute15,
891 okl.id cle_id,
892 okl.dnz_chr_id,
893 okl.sts_code,
894 okl.currency_code
895 FROM oks_cov_txn_groups_int_all obp,
896 okc_k_lines_b okl,
897 oks_coverages_int_all cov,
898 oks_con_lines_int_all lines,
899 oks_con_headers_int_all head
900 WHERE okl.upg_Orig_System_Ref= g_covline_ref
901 AND okl.upg_Orig_System_Ref_Id = obp.Coverage_Id
902 AND obp.coverage_id = cov.coverage_id
903 AND cov.INTERFACED_STATUS_FLAG = 'S'
904 AND cov.coverage_id = lines.coverage_id
905 AND lines.INTERFACED_STATUS_FLAG = 'S'
906 AND head.contract_id = lines.contract_id
907 AND head.INTERFACED_STATUS_FLAG = 'S'
908 AND head.batch_number between p_fromid and p_toid
909 AND obp.interfaced_status_flag is null;
910
911 get_buss_process_rec get_buss_process_cur%rowtype;
912
913 l_validate_flag VARCHAR2(1) := p_validate_flag;
914
915 TYPE K_Status_rec IS RECORD (old_status Varchar2(30),new_status Varchar2(30));
916 TYPE K_Status_Tab is TABLE OF K_Status_Rec INDEX BY BINARY_Integer;
917 g_creation_date DATE;
918 g_created_by NUMBER;
919 g_Last_updated_by NUMBER;
920 g_Last_update_date date;
921 g_Last_Update_Login NUMBER;
922 g_COVERAGE_TXN_GROUP_ID NUMBER;
923
924 /*-----------------------------------------*/
925 COVERAGE_BUSS_PROCESS_ID_tbl NUM_tbl_type;
926 COVERAGE_ID_tbl NUM_tbl_type;
927 BUSINESS_PROCESS_ID_tbl NUM_tbl_type;
928 OFFSET_DURATION_tbl NUM_tbl_type;
929 OFFSET_PERIOD_tbl VC15_tbl_type;
930 CURRENCY_CODE_Tbl Vc15_Tbl_Type;
931 START_DATE_tbl DATE_tbl_type;
932 END_DATE_tbl DATE_tbl_type;
933 PRICE_LIST_ID_tbl NUM_tbl_type;
934 DISCOUNT_ID_tbl NUM_tbl_type;
935 CONTEXT_tbl VC150_tbl_type;
936 MANUFACTURING_ORG_ID_tbl NUM_tbl_type;
937 SUBINVENTORY_CODE_tbl VC15_tbl_type;
938 TIME_ZONE_ID_TBL NUM_tbl_type;
939 COVERAGE_SUN_START_TIME_tbl DATE_tbl_type;
940 COVERAGE_SUN_END_TIME_tbl DATE_tbl_type;
941 COVERAGE_MON_START_TIME_tbl DATE_tbl_type;
942 COVERAGE_MON_END_TIME_tbl DATE_tbl_type;
943 COVERAGE_TUE_START_TIME_tbl DATE_tbl_type;
944 COVERAGE_TUE_END_TIME_tbl DATE_tbl_type;
945 COVERAGE_WED_START_TIME_tbl DATE_tbl_type;
946 COVERAGE_WED_END_TIME_tbl DATE_tbl_type;
947 COVERAGE_THU_START_TIME_tbl DATE_tbl_type;
948 COVERAGE_THU_END_TIME_tbl DATE_tbl_type;
949 COVERAGE_FRI_START_TIME_tbl DATE_tbl_type;
950 COVERAGE_FRI_END_TIME_tbl DATE_tbl_type;
951 COVERAGE_SAT_START_TIME_tbl DATE_tbl_type;
952 COVERAGE_SAT_END_TIME_tbl DATE_tbl_type;
953 REACTION_NAME_tbl VC15_tbl_type;
954 Incident_severity_id_Tbl NUM_TBL_TYPE;
955 REACTION_TIME_ID_tbl NUM_tbl_type;
956 REACTION_DESCRIPTION_tbl VC150_tbl_type;
957 REACTION_security_code_tbl VC150_tbl_type;
958 SUN_REACTION_TIMES_tbl Num_Tbl_Type;
959 MON_REACTION_TIMES_tbl Num_Tbl_Type;
960 TUE_REACTION_TIMES_tbl Num_Tbl_Type;
961 WED_REACTION_TIMES_tbl Num_Tbl_Type;
962 THU_REACTION_TIMES_tbl Num_Tbl_Type;
963 FRI_REACTION_TIMES_tbl Num_Tbl_Type;
964 SAT_REACTION_TIMES_tbl Num_Tbl_Type;
965 ALWAYS_COVERED_tbl VC1_tbl_type;
966 RESOLUTION_TIME_ID_tbl NUM_tbl_type;
967 RESOLUTION_NAME_tbl VC150_tbl_type;
968 RESOLUTION_DESCRIPTION_tbl VC150_tbl_type;
969 SUN_RESOLUTION_TIMES_tbl NUM_tbl_type;
970 MON_RESOLUTION_TIMES_tbl NUM_tbl_type;
971 TUE_RESOLUTION_TIMES_tbl NUM_tbl_type;
972 WED_RESOLUTION_TIMES_tbl NUM_tbl_type;
973 THU_RESOLUTION_TIMES_tbl NUM_tbl_type;
974 FRI_RESOLUTION_TIMES_tbl NUM_tbl_type;
975 SAT_RESOLUTION_TIMES_tbl NUM_tbl_type;
976 work_through_yn_tbl VC1_tbl_type;
977 PREFERRED_RESOURCE_TYPE_tbl VC150_tbl_type;
978 active_yn_tbl VC1_tbl_type;
979 START_DATE_ACTIVE_tbl DATE_tbl_type;
980 END_DATE_ACTIVE_tbl DATE_tbl_type;
981 PREFERRED_RESOURCE_ID_tbl NUM_tbl_type;
982 ATTRIBUTE_CATEGORY_tbl VC150_tbl_type;
983 ATTRIBUTE1_tbl VC150_tbl_type;
984 ATTRIBUTE2_tbl VC150_tbl_type;
985 ATTRIBUTE3_tbl VC150_tbl_type;
986 ATTRIBUTE4_tbl VC150_tbl_type;
987 ATTRIBUTE5_tbl VC150_tbl_type;
988 ATTRIBUTE6_tbl VC150_tbl_type;
989 ATTRIBUTE7_tbl VC150_tbl_type;
990 ATTRIBUTE8_tbl VC150_tbl_type;
991 ATTRIBUTE9_tbl VC150_tbl_type;
992 ATTRIBUTE10_tbl VC150_tbl_type;
993 ATTRIBUTE11_tbl VC150_tbl_type;
994 ATTRIBUTE12_tbl VC150_tbl_type;
995 ATTRIBUTE13_tbl VC150_tbl_type;
996 ATTRIBUTE14_tbl VC150_tbl_type;
997 ATTRIBUTE15_tbl VC150_tbl_type;
998 id_tbl NUM_TBL_TYPE;
999 dnz_chr_id_tbl NUM_TBL_TYPE;
1000 sts_code_tbl VC30_tbl_type;
1001
1002 l_status_tab K_Status_Tab;
1003 l_Time_Unit VARCHAR2(3);
1004 l_return_status VARCHAR2(3);
1005 l_BP_start_date Date;
1006 l_error_message VARCHAR2(2000);
1007 FUNCTION Get_new_status(p_k_status_id IN Number)
1008 RETURN Varchar2 IS
1009 l_new_status Varchar2(30);
1010 BEGIN
1011 Return(l_status_tab(p_k_status_id).new_status);
1012 END Get_new_status;
1013
1014 FUNCTION Get_PrefEng(P_Name IN VARCHAR2)
1015 RETURN VARCHAR2 IS
1016 l_Id1 Varchar2(30);
1017 CURSOR Res_Cur IS
1018 SELECT Id1 FROM OKX_RESOURCES_V
1019 WHERE Description = P_Name;
1020 BEGIN
1021 OPEN Res_Cur;
1022 FETCH Res_Cur INTO l_Id1;
1023 IF Res_Cur%NOTFOUND
1024 THEN l_Id1:=NULL;
1025 END IF;
1026 CLOSE Res_Cur;
1027 RETURN l_Id1;
1028 END Get_PrefEng;
1029
1030
1031 FUNCTION GetTimeUom(P_Uom_Code IN VARCHAR2)
1032 RETURN Varchar2 IS
1033 l_TimeUnit VARCHAR2(3):= NULL;
1034 BEGIN
1035 IF Upper(P_Uom_Code)='DAY'
1036 THEN l_TimeUnit:='DAY';
1037 ELSIF Upper(P_Uom_Code)='HOUR'
1038 THEN l_TimeUnit:='HR';
1039 ELSIF Upper(P_Uom_Code)='MINUTE'
1040 THEN l_TimeUnit:='MIN';
1041 ELSIF Upper(P_Uom_Code)='WEEK'
1042 THEN l_TimeUnit:='WK';
1043 ELSIF Upper(P_Uom_Code)='MONTH'
1044 THEN l_TimeUnit:='MTH';
1045 ELSIF Upper(P_Uom_Code)='YEAR'
1046 THEN l_TimeUnit:='YR';
1047 END IF;
1048 RETURN l_TimeUnit;
1049 END GetTimeUom;
1050
1051 PROCEDURE Fetch_K_Status IS
1052 CURSOR K_Status_Cur IS
1053 SELECT old.contract_status_id k_id, upper(old.name) old_stat,
1054 decode(new.code, NULL,'ENTERED',upper(old.name)) new_stat
1055 FROM OKC_STATUSES_V new, CS_CONTRACT_STATUSES old
1056 WHERE upper(new.code(+)) = upper(old.name);
1057 BEGIN
1058 FOR K_Status_Rec IN K_Status_Cur
1059 LOOP
1060 l_status_tab(K_Status_Rec.k_id).old_status := K_Status_Rec.old_stat;
1061 l_status_tab(K_Status_Rec.k_id).new_status := K_Status_Rec.new_stat;
1062 END LOOP;
1063 END Fetch_K_Status;
1064
1065 BEGIN --- MAIN BEGIN OF Business_Processes_migrate procedure -----
1066 Fetch_K_Status;
1067 open get_buss_process_cur (p_fromid,p_toid);
1068 loop
1069 BEGIN
1070 FETCH get_buss_process_cur BULK COLLECT into
1071
1072 COVERAGE_BUSS_PROCESS_ID_tbl,
1073 COVERAGE_ID_tbl,
1074 BUSINESS_PROCESS_ID_tbl,
1075 OFFSET_DURATION_tbl,
1076 OFFSET_PERIOD_tbl,
1077 START_DATE_tbl,
1078 END_DATE_tbl,
1079 PRICE_LIST_ID_tbl,
1080 DISCOUNT_ID_tbl,
1081 COVERAGE_SUN_START_TIME_tbl,
1082 COVERAGE_SUN_END_TIME_tbl,
1083 COVERAGE_MON_START_TIME_tbl,
1084 COVERAGE_MON_END_TIME_tbl,
1085 COVERAGE_TUE_START_TIME_tbl,
1086 COVERAGE_TUE_END_TIME_tbl,
1087 COVERAGE_WED_START_TIME_tbl,
1088 COVERAGE_WED_END_TIME_tbl,
1089 COVERAGE_THU_START_TIME_tbl,
1090 COVERAGE_THU_END_TIME_tbl,
1091 COVERAGE_FRI_START_TIME_tbl,
1092 COVERAGE_FRI_END_TIME_tbl,
1093 COVERAGE_SAT_START_TIME_tbl,
1094 COVERAGE_SAT_END_TIME_tbl,
1095 time_zone_id_tbl,
1096 REACTION_NAME_tbl,
1097 REACTION_DESCRIPTION_TBL,
1098 REACTION_TIME_ID_TBL,
1099 REACTION_security_code_tbl,
1100 Incident_severity_id_Tbl,
1101 SUN_REACTION_TIMES_tbl,
1102 MON_REACTION_TIMES_tbl,
1103 TUE_REACTION_TIMES_tbl,
1104 WED_REACTION_TIMES_tbl,
1105 THU_REACTION_TIMES_tbl,
1106 FRI_REACTION_TIMES_tbl,
1107 SAT_REACTION_TIMES_tbl,
1108 work_through_yn_tbl,
1109 active_yn_tbl,
1110 SUN_RESOLUTION_TIMES_tbl,
1111 MON_RESOLUTION_TIMES_tbl,
1112 TUE_RESOLUTION_TIMES_tbl,
1113 WED_RESOLUTION_TIMES_tbl,
1114 THU_RESOLUTION_TIMES_tbl,
1115 FRI_RESOLUTION_TIMES_tbl,
1116 SAT_RESOLUTION_TIMES_tbl,
1117 PREFERRED_RESOURCE_TYPE_tbl,
1118 PREFERRED_RESOURCE_ID_tbl,
1119 ATTRIBUTE_CATEGORY_tbl,
1120 ATTRIBUTE1_tbl,
1121 ATTRIBUTE2_tbl,
1122 ATTRIBUTE3_tbl,
1123 ATTRIBUTE4_tbl,
1124 ATTRIBUTE5_tbl,
1125 ATTRIBUTE6_tbl,
1126 ATTRIBUTE7_tbl,
1127 ATTRIBUTE8_tbl,
1128 ATTRIBUTE9_tbl,
1129 ATTRIBUTE10_tbl,
1130 ATTRIBUTE11_tbl,
1131 ATTRIBUTE12_tbl,
1132 ATTRIBUTE13_tbl,
1133 ATTRIBUTE14_tbl,
1134 ATTRIBUTE15_tbl,
1135 id_tbl,
1136 dnz_chr_id_tbl,
1137 sts_code_tbl,
1138 currency_code_tbl
1139 LIMIT 1000;
1140
1141
1142 IF (COVERAGE_BUSS_PROCESS_ID_tbl.COUNT > 0) then
1143 ---- dbms_output.put_line('Test');
1144 FOR i IN COVERAGE_BUSS_PROCESS_ID_tbl.FIRST .. COVERAGE_BUSS_PROCESS_ID_tbl.LAST
1145 LOOP
1146 g_CREATION_DATE := sysdate;
1147 g_CREATED_BY := -1;
1148 g_LAST_UPDATE_DATE := sysdate;
1149 g_LAST_UPDATED_BY := -1;
1150 g_LAST_UPDATE_LOGIN := -1;
1151 -- g_COVERAGE_TXN_GROUP_ID := COVERAGE_TXN_GROUP_ID_Tbl(i);
1152
1153 ----Line Creation For Business Process with LSE_ID = 3 --
1154 l_Time_Unit := GetTimeUOM(offset_period_Tbl(i));
1155
1156 l_BP_start_date := OKC_TIME_UTIL_PUB.get_enddate(
1157 START_DATE_tbl(i),
1158 l_time_unit,
1159 offset_duration_Tbl(i));
1160
1161 l_clev_tbl_in(i).id := okc_p_util.raw_to_number(sys_guid()); --Get_Seq_Id(g_COVERAGE_TXN_GROUP_ID); --okc_p_util.raw_to_number(sys_guid());
1162 l_clev_tbl_in(i).CREATION_DATE := sysdate;
1163 l_clev_tbl_in(i).CREATED_BY := g_CREATED_BY;
1164 l_clev_tbl_in(i).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
1165 l_clev_tbl_in(i).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
1166 l_clev_tbl_in(i).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
1167 l_clev_tbl_in(i).object_version_number := 1;
1168 l_clev_tbl_in(i).dnz_chr_id := dnz_chr_id_tbl(i);
1169 l_clev_tbl_in(i).cle_id := ID_Tbl(i);
1170 l_clev_tbl_in(i).sfwt_flag :='N';
1171 l_clev_tbl_in(i).lse_id := 3;
1172 -- l_clev_tbl_in(i).sts_code :=Get_New_Status(Contract_Line_Status_Id_Tbl(i));
1173 l_clev_tbl_in(i).sts_code :=sts_code_tbl(i);
1174 l_clev_tbl_in(i).display_sequence :=1;
1175 l_clev_tbl_in(i).Name :='MIGRATED'; --Name;
1176 l_clev_tbl_in(i).Currency_Code :=Currency_Code_Tbl(i);
1177 l_clev_tbl_in(i).exception_yn :='N'; -- clarify
1178 l_clev_tbl_in(i).start_date :=START_DATE_tbl(i);
1179 l_clev_tbl_in(i).end_date :=END_DATE_tbl(i);
1180 -- dbms_output.put_line('Test3');
1181 l_clev_tbl_in(i).attribute1 :=Attribute1_Tbl(i);
1182 l_clev_tbl_in(i).attribute2 :=Attribute2_Tbl(i);
1183 l_clev_tbl_in(i).attribute3 :=Attribute3_Tbl(i);
1184 l_clev_tbl_in(i).attribute4 :=Attribute4_Tbl(i);
1185 l_clev_tbl_in(i).attribute5 :=Attribute5_Tbl(i);
1186 l_clev_tbl_in(i).attribute6 :=Attribute6_Tbl(i);
1187 l_clev_tbl_in(i).attribute7 :=Attribute7_Tbl(i);
1188 l_clev_tbl_in(i).attribute8 :=Attribute8_Tbl(i);
1189 l_clev_tbl_in(i).attribute9 :=Attribute9_Tbl(i);
1190 l_clev_tbl_in(i).attribute10 :=Attribute10_Tbl(i);
1191 l_clev_tbl_in(i).attribute11 :=Attribute11_Tbl(i);
1192 l_clev_tbl_in(i).attribute12 :=Attribute12_Tbl(i);
1193 l_clev_tbl_in(i).attribute13 :=Attribute13_Tbl(i);
1194 l_clev_tbl_in(i).attribute14 :=Attribute14_Tbl(i);
1195 l_clev_tbl_in(i).attribute15 :=Attribute15_Tbl(i);
1196 l_clev_tbl_in(i).attribute_Category := attribute_Category_tbl(i);
1197 -- -- dbms_output.put_line('Test4');
1198 l_clev_tbl_in(i).Upg_Orig_System_Ref :=g_bpline_ref;
1199 l_clev_tbl_in(i).Upg_Orig_System_Ref_Id :=COVERAGE_BUSS_PROCESS_ID_tbl(i);
1200 l_clev_tbl_in(i).INVOICE_LINE_LEVEL_IND :=Null;
1201 l_clev_tbl_in(i).DPAS_RATING :=Null;
1202 l_clev_tbl_in(i).TEMPLATE_USED :=Null;
1203 l_clev_tbl_in(i).PRICE_TYPE :=Null;
1204 --l_clev_tbl_in(i).UOM_CODE :=Null;
1205 l_clev_tbl_in(i).LINE_NUMBER :='3';
1206 l_clev_tbl_in(i).CHR_ID :=Null;
1207 l_clev_tbl_in(i).TRN_CODE :=Null;
1208 l_clev_tbl_in(i).LAST_UPDATE_LOGIN :=Null;
1209 l_clev_tbl_in(i).HIDDEN_IND :=Null;
1210 l_clev_tbl_in(i).DATE_TERMINATED :=Null;
1211 l_clev_tbl_in(i).CLE_ID_RENEWED_TO :=Null;
1212 l_clev_tbl_in(i).CURRENCY_CODE_RENEWED :=Null;
1213 l_clev_tbl_in(i).PRICE_NEGOTIATED_RENEWED :=Null;
1214 l_clev_tbl_in(i).cle_id_renewed :=NULL;
1215 l_clev_tbl_in(i).comments :=NULL;
1216 l_clev_tbl_in(i).price_unit :=NULL;
1217 l_clev_tbl_in(i).price_unit_percent :=NULL;
1218 l_clev_tbl_in(i).price_negotiated :=NULL;
1219 l_clev_tbl_in(i).price_level_ind :='N';
1220 l_clev_tbl_in(i).block23text :=NULL;
1221 l_clev_tbl_in(i).program_application_id:= fnd_global.prog_appl_id;
1222 l_clev_tbl_in(i).program_id:= fnd_global.CONC_PROGRAM_ID;
1223 -- -- dbms_output.put_line('Test5');
1224 IF COVERAGE_BUSS_PROCESS_ID_tbl(i) IS NOT NULL
1225 THEN
1226
1227 l_cimv_tbl_in(i).cle_id := l_clev_tbl_in(i).Id;
1228 l_cimv_tbl_in(i).object1_id1 := COVERAGE_BUSS_PROCESS_ID_tbl(i);
1229 l_cimv_tbl_in(i).object1_id2 := '#';
1230 l_cimv_tbl_in(i).jtot_object1_code := 'OKX_BUSIPROC';
1231 l_cimv_tbl_in(i).uom_code := Null;
1232 l_cimv_tbl_in(i).exception_yn := 'N';
1233 l_cimv_tbl_in(i).number_of_items := 1;
1234 l_cimv_tbl_in(i).dnz_chr_id := dnz_chr_id_tbl(i);
1235 l_cimv_tbl_in(i).id := okc_p_util.raw_to_number(sys_guid()); --Get_Seq_Id(g_COVERAGE_TXN_GROUP_ID); --okc_p_util.raw_to_number(sys_guid());
1236 l_cimv_tbl_in(i).CREATION_DATE := g_CREATION_DATE;
1237 l_cimv_tbl_in(i).CREATED_BY := g_CREATED_BY;
1238 l_cimv_tbl_in(i).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
1239 l_cimv_tbl_in(i).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
1240 l_cimv_tbl_in(i).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
1241 l_cimv_tbl_in(i).object_version_number := 1;
1242 l_cimv_tbl_in(i).CHR_ID := Null;
1243 l_cimv_tbl_in(i).CLE_ID_FOR := Null;
1244 l_cimv_tbl_in(i).PRICED_ITEM_YN := Null;
1245 l_cimv_tbl_in(i).UPG_ORIG_SYSTEM_REF := Null;
1246 l_cimv_tbl_in(i).UPG_ORIG_SYSTEM_REF_ID := Null;
1247 l_cimv_tbl_in(i).program_application_id:= fnd_global.prog_appl_id;
1248 l_cimv_tbl_in(i).program_id:= fnd_global.CONC_PROGRAM_ID;
1249 EnD if;
1250
1251 ---- Creation For Rule_Groups in OKC_RULE_GROUPS_V
1252
1253 l_rgpv_tbl_in(i).cle_id := l_clev_tbl_in(i).Id;
1254 l_rgpv_tbl_in(i).sfwt_flag := 'N';
1255 l_rgpv_tbl_in(i).rgd_code := 'SVC_K';
1256 l_rgpv_tbl_in(i).rgp_type := 'KRG';
1257 l_rgpv_tbl_in(i).dnz_chr_id := dnz_chr_id_tbl(i);
1258 l_rgpv_tbl_in(i).id := okc_p_util.raw_to_number(sys_guid());
1259 l_rgpv_tbl_in(i).CREATION_DATE := g_CREATION_DATE;
1260 l_rgpv_tbl_in(i).CREATED_BY := g_CREATED_BY;
1261 l_rgpv_tbl_in(i).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
1262 l_rgpv_tbl_in(i).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
1263 l_rgpv_tbl_in(i).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
1264 l_rgpv_tbl_in(i).object_version_number := 1;
1265 l_rgpv_tbl_in(i).CHR_ID :=Null;
1266 l_rgpv_tbl_in(i).PARENT_RGP_ID :=Null;
1267 l_rgpv_tbl_in(i).SAT_CODE :=Null;
1268 l_rgpv_tbl_in(i).COMMENTS :=Null;
1269 l_rgpv_tbl_in(i).ATTRIBUTE_CATEGORY :=Null;
1270 l_rgpv_tbl_in(i).ATTRIBUTE1 :=Null;
1271 l_rgpv_tbl_in(i).ATTRIBUTE2 :=Null;
1272 l_rgpv_tbl_in(i).ATTRIBUTE3 :=Null;
1273 l_rgpv_tbl_in(i).ATTRIBUTE4 :=Null;
1274 l_rgpv_tbl_in(i).ATTRIBUTE5 :=Null;
1275 l_rgpv_tbl_in(i).ATTRIBUTE6 :=Null;
1276 l_rgpv_tbl_in(i).ATTRIBUTE7 :=Null;
1277 l_rgpv_tbl_in(i).ATTRIBUTE8 :=Null;
1278 l_rgpv_tbl_in(i).ATTRIBUTE9 :=Null;
1279 l_rgpv_tbl_in(i).ATTRIBUTE10 :=Null;
1280 l_rgpv_tbl_in(i).ATTRIBUTE11 :=Null;
1281 l_rgpv_tbl_in(i).ATTRIBUTE12 :=Null;
1282 l_rgpv_tbl_in(i).ATTRIBUTE13 :=Null;
1283 l_rgpv_tbl_in(i).ATTRIBUTE14 :=Null;
1284 l_rgpv_tbl_in(i).ATTRIBUTE15 :=Null;
1285
1286
1287 -- dbms_output.put_line('Before Time Period');
1288
1289
1290 -------------------OFFSET PERIOD -----
1291
1292 l_Time_Unit:=GetTimeUOM(offset_period_Tbl(i));
1293 -- dbms_output.put_line('Value of l_time_unit='||l_time_unit);
1294 IF l_Time_Unit IS NULL THEN NULL;
1295 -- RAISE e_Error;
1296 -- dbms_output.put_line('Error');
1297 ELSE
1298 IF l_Time_Unit is NOT NULL THEN
1299 /*------------------------------------------------------------------------------------------------*/
1300 l_isev_rel_tbl_in(i).dnz_chr_id :=dnz_chr_id_tbl(i);
1301 l_isev_rel_tbl_in(i).id :=okc_p_util.raw_to_number(sys_guid());
1302 l_isev_rel_tbl_in(i).object_version_number :=1;
1303 l_isev_rel_tbl_in(i).sfwt_flag :='N';
1304 l_isev_rel_tbl_in(i).spn_id :=Null;
1305 l_isev_rel_tbl_in(i).uom_code :=l_time_unit;
1306 l_isev_rel_tbl_in(i).start_tve_id_offset :=okc_p_util.raw_to_number(sys_guid());
1307 l_isev_rel_tbl_in(i).tve_id_ended :=Null;
1308 l_isev_rel_tbl_in(i).tve_id_limited :=Null;
1309 l_isev_rel_tbl_in(i).tze_id :=Null;
1310 l_isev_rel_tbl_in(i).description :=Null;
1311 l_isev_rel_tbl_in(i).short_description :=Null;
1312 l_isev_rel_tbl_in(i).comments :=Null;
1313 l_isev_rel_tbl_in(i).duration :=offset_duration_Tbl(i);
1314 l_isev_rel_tbl_in(i).operator :=Null;
1315 l_isev_rel_tbl_in(i).before_after :='A';
1316 l_isev_rel_tbl_in(i).attribute_category :=Null;
1317 l_isev_rel_tbl_in(i).attribute1 :=Null;
1318 l_isev_rel_tbl_in(i).attribute2 :=Null;
1319 l_isev_rel_tbl_in(i).attribute3 :=Null;
1320 l_isev_rel_tbl_in(i).attribute4 :=Null;
1321 l_isev_rel_tbl_in(i).attribute5 :=Null;
1322 l_isev_rel_tbl_in(i).attribute6 :=Null;
1323 l_isev_rel_tbl_in(i).attribute7 :=Null;
1324 l_isev_rel_tbl_in(i).attribute8 :=Null;
1325 l_isev_rel_tbl_in(i).attribute9 :=Null;
1326 l_isev_rel_tbl_in(i).attribute10 :=Null;
1327 l_isev_rel_tbl_in(i).attribute11 :=Null;
1328 l_isev_rel_tbl_in(i).attribute12 :=Null;
1329 l_isev_rel_tbl_in(i).attribute13 :=Null;
1330 l_isev_rel_tbl_in(i).attribute14 :=Null;
1331 l_isev_rel_tbl_in(i).attribute15 :=Null;
1332 l_isev_rel_tbl_in(i).CREATION_DATE := sysdate;
1333 l_isev_rel_tbl_in(i).CREATED_BY := -1;
1334 l_isev_rel_tbl_in(i).LAST_UPDATE_DATE := sysdate;
1335 l_isev_rel_tbl_in(i).LAST_UPDATED_BY := -1;
1336 l_isev_rel_tbl_in(i).LAST_UPDATE_LOGIN := -1;
1337 /*------------------------------------------------------------------------------------------------*/
1338
1339 -- dbms_output.put_line('After Time Period');
1340 -- dbms_output.put_line('Value of offset_duration_Tbl(i='||offset_duration_Tbl(i));
1341 -- dbms_output.put_line('Value of l_time_unit='||l_time_unit);
1342 -- dbms_output.put_line('Value of START_DATE_tbl(i)='||TO_CHAR(START_DATE_tbl(i)));
1343 l_isev_ext_tbl_in(i).start_date:=OKC_TIME_UTIL_PUB.get_enddate(
1344 START_DATE_tbl(i),
1345 l_time_unit,
1346 offset_duration_Tbl(i)
1347 );
1348 -- dbms_output.put_line('Value of l_isev_ext_tbl_in(i).start_date='||TO_CHAR(l_isev_ext_tbl_in(i).start_date));
1349
1350 l_isev_ext_tbl_in(i).end_date := end_date_tbl(i);
1351 -- dbms_output.put_line('Value of l_isev_ext_tbl_in(i).end_date='||TO_CHAR(l_isev_ext_tbl_in(i).end_date));
1352
1353 l_isev_ext_tbl_in(i).dnz_chr_id :=dnz_chr_id_tbl(i);
1354 -- l_isev_ext_tbl_in(i).id :=okc_p_util.raw_to_number(sys_guid());
1355 l_isev_ext_tbl_in(i).id :=l_isev_rel_tbl_in(i).start_tve_id_offset;
1356 l_isev_ext_tbl_in(i).object_version_number :=1;
1357 l_isev_ext_tbl_in(i).sfwt_flag :='N';
1358 l_isev_ext_tbl_in(i).spn_id :=Null;
1359 l_isev_ext_tbl_in(i).uom_code :=l_time_unit;
1360 l_isev_ext_tbl_in(i).tve_id_ended :=Null;
1361 l_isev_ext_tbl_in(i).tve_id_limited :=Null;
1362 l_isev_ext_tbl_in(i).tze_id :=Null;
1363 l_isev_ext_tbl_in(i).description :=Null;
1364 l_isev_ext_tbl_in(i).short_description :=Null;
1365 l_isev_ext_tbl_in(i).comments :=Null;
1366 l_isev_ext_tbl_in(i).duration :=offset_duration_Tbl(i);
1367 l_isev_ext_tbl_in(i).operator :=Null;
1368 l_isev_ext_tbl_in(i).before_after :=Null;
1369 l_isev_ext_tbl_in(i).attribute_category :=Null;
1370 l_isev_ext_tbl_in(i).attribute1 :=Null;
1371 l_isev_ext_tbl_in(i).attribute2 :=Null;
1372 l_isev_ext_tbl_in(i).attribute3 :=Null;
1373 l_isev_ext_tbl_in(i).attribute4 :=Null;
1374 l_isev_ext_tbl_in(i).attribute5 :=Null;
1375 l_isev_ext_tbl_in(i).attribute6 :=Null;
1376 l_isev_ext_tbl_in(i).attribute7 :=Null;
1377 l_isev_ext_tbl_in(i).attribute8 :=Null;
1378 l_isev_ext_tbl_in(i).attribute9 :=Null;
1379 l_isev_ext_tbl_in(i).attribute10 :=Null;
1380 l_isev_ext_tbl_in(i).attribute11 :=Null;
1381 l_isev_ext_tbl_in(i).attribute12 :=Null;
1382 l_isev_ext_tbl_in(i).attribute13 :=Null;
1383 l_isev_ext_tbl_in(i).attribute14 :=Null;
1384 l_isev_ext_tbl_in(i).attribute15 :=Null;
1385 l_isev_ext_tbl_in(i).CREATION_DATE := sysdate;
1386 l_isev_ext_tbl_in(i).CREATED_BY := -1;
1387 l_isev_ext_tbl_in(i).LAST_UPDATE_DATE := sysdate;
1388 l_isev_ext_tbl_in(i).LAST_UPDATED_BY := -1;
1389 l_isev_ext_tbl_in(i).LAST_UPDATE_LOGIN := -1;
1390
1391 -- dbms_output.put_line('Value of l_isev_ext_tbl_in(i).uom_code='||l_isev_ext_tbl_in(i).uom_code);
1392
1393 l_rulv_tbl_in(i).rgp_id := l_rgpv_tbl_in(i).id;
1394 l_rulv_tbl_in(i).sfwt_flag := 'N';
1395 l_rulv_tbl_in(i).rule_information_category := 'OFS';
1396 l_rulv_tbl_in(i).rule_information1 := l_isev_ext_tbl_in(i).id;
1397 l_rulv_tbl_in(i).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_id;
1398 l_rulv_tbl_in(i).std_template_yn := 'N';
1399 l_rulv_tbl_in(i).warn_YN := 'N';
1400 l_rulv_tbl_in(i).id :=okc_p_util.raw_to_number(sys_guid()); --okc_p_util.raw_to_number(sys_guid());
1401 l_rulv_tbl_in(i).CREATION_DATE := sysdate;
1402 l_rulv_tbl_in(i).CREATED_BY := -1;
1403 l_rulv_tbl_in(i).LAST_UPDATE_DATE := sysdate;
1404 l_rulv_tbl_in(i).LAST_UPDATED_BY := -1;
1405 l_rulv_tbl_in(i).LAST_UPDATE_LOGIN := -1;
1406 l_rulv_tbl_in(i).object_version_number := 1;
1407 l_rulv_tbl_in(i).PRIORITY := NULL;
1408 l_rulv_tbl_in(i).OBJECT1_ID1 := NULL;
1409 l_rulv_tbl_in(i).PRIORITY := NULL;
1410 l_rulv_tbl_in(i).OBJECT2_ID1 := NULL;
1411 l_rulv_tbl_in(i).OBJECT3_ID1 := NULL;
1412 l_rulv_tbl_in(i).OBJECT2_ID2 := NULL;
1413 l_rulv_tbl_in(i).OBJECT3_ID2 := NULL;
1414 l_rulv_tbl_in(i).JTOT_OBJECT2_CODE := NULL;
1415 l_rulv_tbl_in(i).JTOT_OBJECT3_CODE := NULL;
1416 l_rulv_tbl_in(i).PRIORITY := NULL;
1417 l_rulv_tbl_in(i).COMMENTS := NULL;
1418 l_rulv_tbl_in(i).ATTRIBUTE_CATEGORY := NULL;
1419 l_rulv_tbl_in(i).ATTRIBUTE1 := NULL;
1420 l_rulv_tbl_in(i).ATTRIBUTE2 := NULL;
1421 l_rulv_tbl_in(i).ATTRIBUTE3 := NULL;
1422 l_rulv_tbl_in(i).ATTRIBUTE4 := NULL;
1423 l_rulv_tbl_in(i).ATTRIBUTE5 := NULL;
1424 l_rulv_tbl_in(i).ATTRIBUTE6 := NULL;
1425 l_rulv_tbl_in(i).ATTRIBUTE7 := NULL;
1426 l_rulv_tbl_in(i).ATTRIBUTE8 := NULL;
1427 l_rulv_tbl_in(i).ATTRIBUTE9 := NULL;
1428 l_rulv_tbl_in(i).ATTRIBUTE10 := NULL;
1429 l_rulv_tbl_in(i).ATTRIBUTE11 := NULL;
1430 l_rulv_tbl_in(i).ATTRIBUTE12 := NULL;
1431 l_rulv_tbl_in(i).ATTRIBUTE13 := NULL;
1432 l_rulv_tbl_in(i).ATTRIBUTE14 := NULL;
1433 l_rulv_tbl_in(i).ATTRIBUTE15 := NULL;
1434 l_rulv_tbl_in(i).TEXT := NULL;
1435 l_rulv_tbl_in(i).RULE_INFORMATION2 := NULL;
1436 l_rulv_tbl_in(i).RULE_INFORMATION3 := NULL;
1437 l_rulv_tbl_in(i).RULE_INFORMATION4 := NULL;
1438 l_rulv_tbl_in(i).RULE_INFORMATION5 := NULL;
1439 l_rulv_tbl_in(i).RULE_INFORMATION6 := NULL;
1440 l_rulv_tbl_in(i).RULE_INFORMATION7 := NULL;
1441 l_rulv_tbl_in(i).RULE_INFORMATION8 := NULL;
1442 l_rulv_tbl_in(i).RULE_INFORMATION9 := NULL;
1443 l_rulv_tbl_in(i).RULE_INFORMATION10 := NULL;
1444 l_rulv_tbl_in(i).RULE_INFORMATION11 := NULL;
1445 l_rulv_tbl_in(i).RULE_INFORMATION12 := NULL;
1446 l_rulv_tbl_in(i).RULE_INFORMATION13 := NULL;
1447 l_rulv_tbl_in(i).RULE_INFORMATION14 := NULL;
1448 l_rulv_tbl_in(i).RULE_INFORMATION15 := NULL;
1449
1450
1451 /*----------------------------------------------------------------------
1452
1453 -- -- dbms_output.put_line('Value of l_isev_rel_tbl_in(i+1000.end_date='||TO_CHAR(l_isev_rel_tbl_in(i+1000.end_date));
1454
1455
1456
1457 -------------------------------------------------------------------------*/
1458 END IF;
1459 END IF;
1460
1461
1462 IF PRICE_LIST_ID_Tbl(i) IS NOT NULL THEN
1463 /*
1464 rulv_ctr := rulv_ctr + 1;
1465 INT_INITIALIZE_ALL_PVT.Clear_Rules_Table;
1466 */
1467 l_rulv_tbl_in(i+1000).rgp_id := l_rgpv_tbl_in(i).id;
1468 l_rulv_tbl_in(i+1000).sfwt_flag := 'N';
1469 l_rulv_tbl_in(i+1000).rule_information_category := 'PRE';
1470 l_rulv_tbl_in(i+1000).std_template_yn := 'N';
1471 l_rulv_tbl_in(i+1000).warn_YN := 'N';
1472 l_rulv_tbl_in(i+1000).OBJECT1_ID1 := PRICE_LIST_ID_Tbl(i);
1473 l_rulv_tbl_in(i+1000).OBJECT1_ID2 := '#';
1474 l_rulv_tbl_in(i+1000).JTOT_OBJECT1_code := 'OKX_PRICE';
1475 l_rulv_tbl_in(i+1000).dnz_chr_id := dnz_chr_id_tbl(i);
1476 l_rulv_tbl_in(i+1000).id := okc_p_util.raw_to_number(sys_guid()); --okc_p_util.raw_to_number(sys_guid());
1477 l_rulv_tbl_in(i+1000).CREATION_DATE := sysdate;
1478 l_rulv_tbl_in(i+1000).CREATED_BY := -1;
1479 l_rulv_tbl_in(i+1000).LAST_UPDATE_DATE := sysdate;
1480 l_rulv_tbl_in(i+1000).LAST_UPDATED_BY := -1;
1481 l_rulv_tbl_in(i+1000).LAST_UPDATE_LOGIN := -1;
1482 l_rulv_tbl_in(i+1000).object_version_number := 1;
1483 l_rulv_tbl_in(i+1000).PRIORITY := NULL;
1484 l_rulv_tbl_in(i+1000).OBJECT2_ID1 := NULL;
1485 l_rulv_tbl_in(i+1000).OBJECT3_ID1 := NULL;
1486 l_rulv_tbl_in(i+1000).OBJECT2_ID2 := NULL;
1487 l_rulv_tbl_in(i+1000).OBJECT3_ID2 := NULL;
1488 l_rulv_tbl_in(i+1000).JTOT_OBJECT2_CODE := NULL;
1489 l_rulv_tbl_in(i+1000).JTOT_OBJECT3_CODE := NULL;
1490 l_rulv_tbl_in(i+1000).PRIORITY := NULL;
1491 l_rulv_tbl_in(i+1000).COMMENTS := NULL;
1492 l_rulv_tbl_in(i+1000).ATTRIBUTE_CATEGORY := NULL;
1493 l_rulv_tbl_in(i+1000).ATTRIBUTE1 := NULL;
1494 l_rulv_tbl_in(i+1000).ATTRIBUTE2 := NULL;
1495 l_rulv_tbl_in(i+1000).ATTRIBUTE3 := NULL;
1496 l_rulv_tbl_in(i+1000).ATTRIBUTE4 := NULL;
1497 l_rulv_tbl_in(i+1000).ATTRIBUTE5 := NULL;
1498 l_rulv_tbl_in(i+1000).ATTRIBUTE6 := NULL;
1499 l_rulv_tbl_in(i+1000).ATTRIBUTE7 := NULL;
1500 l_rulv_tbl_in(i+1000).ATTRIBUTE8 := NULL;
1501 l_rulv_tbl_in(i+1000).ATTRIBUTE9 := NULL;
1502 l_rulv_tbl_in(i+1000).ATTRIBUTE10 := NULL;
1503 l_rulv_tbl_in(i+1000).ATTRIBUTE11 := NULL;
1504 l_rulv_tbl_in(i+1000).ATTRIBUTE12 := NULL;
1505 l_rulv_tbl_in(i+1000).ATTRIBUTE13 := NULL;
1506 l_rulv_tbl_in(i+1000).ATTRIBUTE14 := NULL;
1507 l_rulv_tbl_in(i+1000).ATTRIBUTE15 := NULL;
1508 l_rulv_tbl_in(i+1000).TEXT := NULL;
1509 l_rulv_tbl_in(i+1000).RULE_INFORMATION2 := NULL;
1510 l_rulv_tbl_in(i+1000).RULE_INFORMATION3 := NULL;
1511 l_rulv_tbl_in(i+1000).RULE_INFORMATION4 := NULL;
1512 l_rulv_tbl_in(i+1000).RULE_INFORMATION5 := NULL;
1513 l_rulv_tbl_in(i+1000).RULE_INFORMATION6 := NULL;
1514 l_rulv_tbl_in(i+1000).RULE_INFORMATION7 := NULL;
1515 l_rulv_tbl_in(i+1000).RULE_INFORMATION8 := NULL;
1516 l_rulv_tbl_in(i+1000).RULE_INFORMATION9 := NULL;
1517 l_rulv_tbl_in(i+1000).RULE_INFORMATION10 := NULL;
1518 l_rulv_tbl_in(i+1000).RULE_INFORMATION11 := NULL;
1519 l_rulv_tbl_in(i+1000).RULE_INFORMATION12 := NULL;
1520 l_rulv_tbl_in(i+1000).RULE_INFORMATION13 := NULL;
1521 l_rulv_tbl_in(i+1000).RULE_INFORMATION14 := NULL;
1522 l_rulv_tbl_in(i+1000).RULE_INFORMATION15 := NULL;
1523
1524 End If;
1525
1526 l_rulv_tbl_in(i+3000).rgp_id := l_rgpv_tbl_in(i).id;
1527 l_rulv_tbl_in(i+3000).sfwt_flag := 'N';
1528 l_rulv_tbl_in(i+3000).rule_information_category := 'CVR';
1529 l_rulv_tbl_in(i+3000).rule_information1 := null;
1530 l_rulv_tbl_in(i+3000).std_template_yn := 'N';
1531 l_rulv_tbl_in(i+3000).warn_YN := 'N';
1532 l_rulv_tbl_in(i+3000).dnz_chr_id := dnz_chr_id_tbl(i);
1533 l_rulv_tbl_in(i+3000).id := okc_p_util.raw_to_number(sys_guid());--Get_Seq_Id(g_COVERAGE_TXN_GROUP_ID); --okc_p_util.raw_to_number(sys_guid());
1534 l_rulv_tbl_in(i+3000).CREATION_DATE := g_CREATION_DATE;
1535 l_rulv_tbl_in(i+3000).CREATED_BY := g_CREATED_BY;
1536 l_rulv_tbl_in(i+3000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
1537 l_rulv_tbl_in(i+3000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
1538 l_rulv_tbl_in(i+3000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
1539 l_rulv_tbl_in(i+3000).object_version_number := 1;
1540 l_rulv_tbl_in(i+3000).PRIORITY := NULL;
1541 l_rulv_tbl_in(i+3000).OBJECT2_ID1 := NULL;
1542 l_rulv_tbl_in(i+3000).OBJECT3_ID1 := NULL;
1543 l_rulv_tbl_in(i+3000).OBJECT2_ID2 := NULL;
1544 l_rulv_tbl_in(i+3000).OBJECT3_ID2 := NULL;
1545 l_rulv_tbl_in(i+3000).JTOT_OBJECT2_CODE := NULL;
1546 l_rulv_tbl_in(i+3000).JTOT_OBJECT3_CODE := NULL;
1547 l_rulv_tbl_in(i+3000).PRIORITY := NULL;
1548 l_rulv_tbl_in(i+3000).COMMENTS := NULL;
1549 l_rulv_tbl_in(i+3000).ATTRIBUTE_CATEGORY := NULL;
1550 l_rulv_tbl_in(i+3000).ATTRIBUTE1 := NULL;
1551 l_rulv_tbl_in(i+3000).ATTRIBUTE2 := NULL;
1552 l_rulv_tbl_in(i+3000).ATTRIBUTE3 := NULL;
1553 l_rulv_tbl_in(i+3000).ATTRIBUTE4 := NULL;
1554 l_rulv_tbl_in(i+3000).ATTRIBUTE5 := NULL;
1555 l_rulv_tbl_in(i+3000).ATTRIBUTE6 := NULL;
1556 l_rulv_tbl_in(i+3000).ATTRIBUTE7 := NULL;
1557 l_rulv_tbl_in(i+3000).ATTRIBUTE8 := NULL;
1558 l_rulv_tbl_in(i+3000).ATTRIBUTE9 := NULL;
1559 l_rulv_tbl_in(i+3000).ATTRIBUTE10 := NULL;
1560 l_rulv_tbl_in(i+3000).ATTRIBUTE11 := NULL;
1561 l_rulv_tbl_in(i+3000).ATTRIBUTE12 := NULL;
1562 l_rulv_tbl_in(i+3000).ATTRIBUTE13 := NULL;
1563 l_rulv_tbl_in(i+3000).ATTRIBUTE14 := NULL;
1564 l_rulv_tbl_in(i+3000).ATTRIBUTE15 := NULL;
1565 l_rulv_tbl_in(i+3000).TEXT := NULL;
1566 l_rulv_tbl_in(i+3000).RULE_INFORMATION2 := NULL;
1567 l_rulv_tbl_in(i+3000).RULE_INFORMATION3 := NULL;
1568 l_rulv_tbl_in(i+3000).RULE_INFORMATION4 := NULL;
1569 l_rulv_tbl_in(i+3000).RULE_INFORMATION5 := NULL;
1570 l_rulv_tbl_in(i+3000).RULE_INFORMATION6 := NULL;
1571 l_rulv_tbl_in(i+3000).RULE_INFORMATION7 := NULL;
1572 l_rulv_tbl_in(i+3000).RULE_INFORMATION8 := NULL;
1573 l_rulv_tbl_in(i+3000).RULE_INFORMATION9 := NULL;
1574 l_rulv_tbl_in(i+3000).RULE_INFORMATION10 := NULL;
1575 l_rulv_tbl_in(i+3000).RULE_INFORMATION11 := NULL;
1576 l_rulv_tbl_in(i+3000).RULE_INFORMATION12 := NULL;
1577 l_rulv_tbl_in(i+3000).RULE_INFORMATION13 := NULL;
1578 l_rulv_tbl_in(i+3000).RULE_INFORMATION14 := NULL;
1579 l_rulv_tbl_in(i+3000).RULE_INFORMATION15 := NULL;
1580
1581 IF COVERAGE_SUN_START_TIME_tbl(i) IS NOT NULL THEN
1582 l_igsv_ext_tbl_in(i).id :=okc_p_util.raw_to_number(sys_guid());
1583 l_igsv_ext_tbl_in(i).start_day_of_week := 'SUN';
1584 l_igsv_ext_tbl_in(i).start_hour := to_char(COVERAGE_SUN_START_TIME_tbl(i),'HH24');
1585 l_igsv_ext_tbl_in(i).start_minute := to_char(COVERAGE_SUN_START_TIME_tbl(i),'MI');
1586 l_igsv_ext_tbl_in(i).start_second := to_char(COVERAGE_SUN_START_TIME_tbl(i),'SS');
1587 l_igsv_ext_tbl_in(i).end_month := Null;
1588 l_igsv_ext_tbl_in(i).end_day_of_week := 'SUN';
1589 l_igsv_ext_tbl_in(i).end_hour := to_char(COVERAGE_SUN_END_TIME_tbl(i),'HH24');
1590 l_igsv_ext_tbl_in(i).end_minute := to_char(COVERAGE_SUN_END_TIME_tbl(i),'MI');
1591 l_igsv_ext_tbl_in(i).end_second := to_char(COVERAGE_SUN_END_TIME_tbl(i),'SS');
1592 l_igsv_ext_tbl_in(i).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
1593 l_igsv_ext_tbl_in(i).tze_id := nvl(Time_Zone_Id_Tbl(i),g_timezone_id);
1594 l_igsv_ext_tbl_in(i).CREATION_DATE := g_CREATION_DATE;
1595 l_igsv_ext_tbl_in(i).CREATED_BY := g_CREATED_BY;
1596 l_igsv_ext_tbl_in(i).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
1597 l_igsv_ext_tbl_in(i).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
1598 l_igsv_ext_tbl_in(i).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
1599 l_igsv_ext_tbl_in(i).Object_version_Number :=1;
1600 l_igsv_ext_tbl_in(i).sfwt_flag :='N';
1601 /*---------------------------------------*/
1602 l_igsv_ext_tbl_in(i).tve_id_ended :=Null;
1603 l_igsv_ext_tbl_in(i).tve_id_limited :=Null;
1604 l_igsv_ext_tbl_in(i).description :=Null;
1605 l_igsv_ext_tbl_in(i).short_description :=Null;
1606 l_igsv_ext_tbl_in(i).comments :=Null;
1607 l_igsv_ext_tbl_in(i).attribute_category :=Null;
1608 l_igsv_ext_tbl_in(i).attribute1 :=Null;
1609 l_igsv_ext_tbl_in(i).attribute2 :=Null;
1610 l_igsv_ext_tbl_in(i).attribute3 :=Null;
1611 l_igsv_ext_tbl_in(i).attribute4 :=Null;
1612 l_igsv_ext_tbl_in(i).attribute5 :=Null;
1613 l_igsv_ext_tbl_in(i).attribute6 :=Null;
1614 l_igsv_ext_tbl_in(i).attribute7 :=Null;
1615 l_igsv_ext_tbl_in(i).attribute8 :=Null;
1616 l_igsv_ext_tbl_in(i).attribute9 :=Null;
1617 l_igsv_ext_tbl_in(i).attribute10 :=Null;
1618 l_igsv_ext_tbl_in(i).attribute11 :=Null;
1619 l_igsv_ext_tbl_in(i).attribute12 :=Null;
1620 l_igsv_ext_tbl_in(i).attribute13 :=Null;
1621 l_igsv_ext_tbl_in(i).attribute14 :=Null;
1622 l_igsv_ext_tbl_in(i).attribute15 :=Null;
1623
1624 l_ctiv_tbl_In(i).rul_id :=l_rulv_tbl_in(i+3000).id;
1625 l_ctiv_tbl_in(i).tve_id :=l_igsv_ext_tbl_in(i).Id;
1626 l_ctiv_tbl_in(i).dnz_chr_id :=l_clev_tbl_in(i).dnz_chr_Id;
1627 l_ctiv_tbl_in(i).CREATION_DATE := g_CREATION_DATE;
1628 l_ctiv_tbl_in(i).CREATED_BY := g_CREATED_BY;
1629 l_ctiv_tbl_in(i).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
1630 l_ctiv_tbl_in(i).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
1631 l_ctiv_tbl_in(i).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
1632 l_ctiv_tbl_in(i).object_version_number := 1;
1633
1634 End If;
1635
1636
1637 IF COVERAGE_MON_START_TIME_tbl(i) IS NOT NULL THEN
1638 l_igsv_ext_tbl_in(i+1000).id :=okc_p_util.raw_to_number(sys_guid());
1639 l_igsv_ext_tbl_in(i+1000).start_day_of_week := 'MON';
1640 l_igsv_ext_tbl_in(i+1000).start_hour := to_char(COVERAGE_MON_START_TIME_tbl(i),'HH24');
1641 l_igsv_ext_tbl_in(i+1000).start_minute := to_char(COVERAGE_MON_START_TIME_tbl(i),'MI');
1642 l_igsv_ext_tbl_in(i+1000).start_second := to_char(COVERAGE_MON_START_TIME_tbl(i),'SS');
1643 l_igsv_ext_tbl_in(i+1000).end_MONth := Null;
1644 l_igsv_ext_tbl_in(i+1000).end_day_of_week := 'MON';
1645 l_igsv_ext_tbl_in(i+1000).end_hour := to_char(COVERAGE_MON_END_TIME_tbl(i),'HH24');
1646 l_igsv_ext_tbl_in(i+1000).end_minute := to_char(COVERAGE_MON_END_TIME_tbl(i),'MI');
1647 l_igsv_ext_tbl_in(i+1000).end_second := to_char(COVERAGE_MON_END_TIME_tbl(i),'SS');
1648 l_igsv_ext_tbl_in(i+1000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
1649 l_igsv_ext_tbl_in(i+1000).tze_id := nvl(Time_Zone_Id_Tbl(i),g_timezone_id);
1650 l_igsv_ext_tbl_in(i+1000).CREATION_DATE := g_CREATION_DATE;
1651 l_igsv_ext_tbl_in(i+1000).CREATED_BY := g_CREATED_BY;
1652 l_igsv_ext_tbl_in(i+1000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
1653 l_igsv_ext_tbl_in(i+1000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
1654 l_igsv_ext_tbl_in(i+1000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
1655 l_igsv_ext_tbl_in(i+1000).Object_version_Number :=1;
1656 l_igsv_ext_tbl_in(i+1000).sfwt_flag :='N';
1657 l_igsv_ext_tbl_in(i+1000).tve_id_ended :=Null;
1658 l_igsv_ext_tbl_in(i+1000).tve_id_limited :=Null;
1659 l_igsv_ext_tbl_in(i+1000).description :=Null;
1660 l_igsv_ext_tbl_in(i+1000).short_description :=Null;
1661 l_igsv_ext_tbl_in(i+1000).comments :=Null;
1662 l_igsv_ext_tbl_in(i+1000).attribute_category :=Null;
1663 l_igsv_ext_tbl_in(i+1000).attribute1 :=Null;
1664 l_igsv_ext_tbl_in(i+1000).attribute2 :=Null;
1665 l_igsv_ext_tbl_in(i+1000).attribute3 :=Null;
1666 l_igsv_ext_tbl_in(i+1000).attribute4 :=Null;
1667 l_igsv_ext_tbl_in(i+1000).attribute5 :=Null;
1668 l_igsv_ext_tbl_in(i+1000).attribute6 :=Null;
1669 l_igsv_ext_tbl_in(i+1000).attribute7 :=Null;
1670 l_igsv_ext_tbl_in(i+1000).attribute8 :=Null;
1671 l_igsv_ext_tbl_in(i+1000).attribute9 :=Null;
1672 l_igsv_ext_tbl_in(i+1000).attribute10 :=Null;
1673 l_igsv_ext_tbl_in(i+1000).attribute11 :=Null;
1674 l_igsv_ext_tbl_in(i+1000).attribute12 :=Null;
1675 l_igsv_ext_tbl_in(i+1000).attribute13 :=Null;
1676 l_igsv_ext_tbl_in(i+1000).attribute14 :=Null;
1677 l_igsv_ext_tbl_in(i+1000).attribute15 :=Null;
1678
1679 l_ctiv_tbl_In(i+1000).rul_id :=l_rulv_tbl_in(i+3000).id;
1680 l_ctiv_tbl_in(i+1000).tve_id :=l_igsv_ext_tbl_in(i+1000).Id;
1681 l_ctiv_tbl_in(i+1000).dnz_chr_id :=l_clev_tbl_in(i).dnz_chr_Id;
1682 l_ctiv_tbl_in(i+1000).CREATION_DATE := g_CREATION_DATE;
1683 l_ctiv_tbl_in(i+1000).CREATED_BY := g_CREATED_BY;
1684 l_ctiv_tbl_in(i+1000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
1685 l_ctiv_tbl_in(i+1000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
1686 l_ctiv_tbl_in(i+1000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
1687 l_ctiv_tbl_in(i+1000).object_version_number := 1;
1688 End If;
1689
1690 IF COVERAGE_TUE_START_TIME_tbl(i) IS NOT NULL THEN
1691 l_igsv_ext_tbl_in(i+2000).id :=okc_p_util.raw_to_number(sys_guid());
1692 l_igsv_ext_tbl_in(i+2000).start_day_of_week := 'TUE';
1693 l_igsv_ext_tbl_in(i+2000).start_hour := to_char(COVERAGE_TUE_START_TIME_tbl(i),'HH24');
1694 l_igsv_ext_tbl_in(i+2000).start_minute := to_char(COVERAGE_TUE_START_TIME_tbl(i),'MI');
1695 l_igsv_ext_tbl_in(i+2000).start_second := to_char(COVERAGE_TUE_START_TIME_tbl(i),'SS');
1696 l_igsv_ext_tbl_in(i+2000).end_month := Null;
1697 l_igsv_ext_tbl_in(i+2000).end_day_of_week := 'TUE';
1698 l_igsv_ext_tbl_in(i+2000).end_hour := to_char(COVERAGE_TUE_END_TIME_tbl(i),'HH24');
1699 l_igsv_ext_tbl_in(i+2000).end_minute := to_char(COVERAGE_TUE_END_TIME_tbl(i),'MI');
1700 l_igsv_ext_tbl_in(i+2000).end_second := to_char(COVERAGE_TUE_END_TIME_tbl(i),'SS');
1701 l_igsv_ext_tbl_in(i+2000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
1702 l_igsv_ext_tbl_in(i+2000).tze_id := nvl(Time_Zone_Id_Tbl(i),g_timezone_id);
1703 l_igsv_ext_tbl_in(i+2000).CREATION_DATE := g_CREATION_DATE;
1704 l_igsv_ext_tbl_in(i+2000).CREATED_BY := g_CREATED_BY;
1705 l_igsv_ext_tbl_in(i+2000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
1706 l_igsv_ext_tbl_in(i+2000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
1707 l_igsv_ext_tbl_in(i+2000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
1708 l_igsv_ext_tbl_in(i+2000).Object_version_Number :=1;
1709 l_igsv_ext_tbl_in(i+2000).sfwt_flag :='N';
1710 l_igsv_ext_tbl_in(i+2000).tve_id_ended :=Null;
1711 l_igsv_ext_tbl_in(i+2000).tve_id_limited :=Null;
1712 l_igsv_ext_tbl_in(i+2000).description :=Null;
1713 l_igsv_ext_tbl_in(i+2000).short_description :=Null;
1714 l_igsv_ext_tbl_in(i+2000).comments :=Null;
1715 l_igsv_ext_tbl_in(i+2000).attribute_category :=Null;
1716 l_igsv_ext_tbl_in(i+2000).attribute1 :=Null;
1717 l_igsv_ext_tbl_in(i+2000).attribute2 :=Null;
1718 l_igsv_ext_tbl_in(i+2000).attribute3 :=Null;
1719 l_igsv_ext_tbl_in(i+2000).attribute4 :=Null;
1720 l_igsv_ext_tbl_in(i+2000).attribute5 :=Null;
1721 l_igsv_ext_tbl_in(i+2000).attribute6 :=Null;
1722 l_igsv_ext_tbl_in(i+2000).attribute7 :=Null;
1723 l_igsv_ext_tbl_in(i+2000).attribute8 :=Null;
1724 l_igsv_ext_tbl_in(i+2000).attribute9 :=Null;
1725 l_igsv_ext_tbl_in(i+2000).attribute10 :=Null;
1726 l_igsv_ext_tbl_in(i+2000).attribute11 :=Null;
1727 l_igsv_ext_tbl_in(i+2000).attribute12 :=Null;
1728 l_igsv_ext_tbl_in(i+2000).attribute13 :=Null;
1729 l_igsv_ext_tbl_in(i+2000).attribute14 :=Null;
1730 l_igsv_ext_tbl_in(i+2000).attribute15 :=Null;
1731
1732 l_ctiv_tbl_In(i+2000).rul_id :=l_rulv_tbl_in(i+3000).id;
1733 l_ctiv_tbl_in(i+2000).tve_id :=l_igsv_ext_tbl_in(i+2000).Id;
1734 l_ctiv_tbl_in(i+2000).dnz_chr_id :=l_clev_tbl_in(i).dnz_chr_Id;
1735 l_ctiv_tbl_in(i+2000).CREATION_DATE := g_CREATION_DATE;
1736 l_ctiv_tbl_in(i+2000).CREATED_BY := g_CREATED_BY;
1737 l_ctiv_tbl_in(i+2000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
1738 l_ctiv_tbl_in(i+2000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
1739 l_ctiv_tbl_in(i+2000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
1740 l_ctiv_tbl_in(i+2000).object_version_number := 1;
1741 End If;
1742
1743 IF COVERAGE_WED_START_TIME_tbl(i) IS NOT NULL THEN
1744 l_igsv_ext_tbl_in(i+3000).id :=okc_p_util.raw_to_number(sys_guid());
1745 l_igsv_ext_tbl_in(i+3000).start_day_of_week := 'WED';
1746 l_igsv_ext_tbl_in(i+3000).start_hour := to_char(COVERAGE_WED_START_TIME_tbl(i),'HH24');
1747 l_igsv_ext_tbl_in(i+3000).start_minute := to_char(COVERAGE_WED_START_TIME_tbl(i),'MI');
1748 l_igsv_ext_tbl_in(i+3000).start_second := to_char(COVERAGE_WED_START_TIME_tbl(i),'SS');
1749 l_igsv_ext_tbl_in(i+3000).end_month := Null;
1750 l_igsv_ext_tbl_in(i+3000).end_day_of_week := 'WED';
1751 l_igsv_ext_tbl_in(i+3000).end_hour := to_char(COVERAGE_WED_END_TIME_tbl(i),'HH24');
1752 l_igsv_ext_tbl_in(i+3000).end_minute := to_char(COVERAGE_WED_END_TIME_tbl(i),'MI');
1753 l_igsv_ext_tbl_in(i+3000).end_second := to_char(COVERAGE_WED_END_TIME_tbl(i),'SS');
1754 l_igsv_ext_tbl_in(i+3000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
1755 l_igsv_ext_tbl_in(i+3000).tze_id := nvl(Time_Zone_Id_Tbl(i),g_timezone_id);
1756 l_igsv_ext_tbl_in(i+3000).CREATION_DATE := g_CREATION_DATE;
1757 l_igsv_ext_tbl_in(i+3000).CREATED_BY := g_CREATED_BY;
1758 l_igsv_ext_tbl_in(i+3000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
1759 l_igsv_ext_tbl_in(i+3000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
1760 l_igsv_ext_tbl_in(i+3000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
1761 l_igsv_ext_tbl_in(i+3000).Object_version_Number :=1;
1762 l_igsv_ext_tbl_in(i+3000).sfwt_flag :='N';
1763 l_igsv_ext_tbl_in(i+3000).tve_id_ended :=Null;
1764 l_igsv_ext_tbl_in(i+3000).tve_id_limited :=Null;
1765 l_igsv_ext_tbl_in(i+3000).description :=Null;
1766 l_igsv_ext_tbl_in(i+3000).short_description :=Null;
1767 l_igsv_ext_tbl_in(i+3000).comments :=Null;
1768 l_igsv_ext_tbl_in(i+3000).attribute_category :=Null;
1769 l_igsv_ext_tbl_in(i+3000).attribute1 :=Null;
1770 l_igsv_ext_tbl_in(i+3000).attribute2 :=Null;
1771 l_igsv_ext_tbl_in(i+3000).attribute3 :=Null;
1772 l_igsv_ext_tbl_in(i+3000).attribute4 :=Null;
1773 l_igsv_ext_tbl_in(i+3000).attribute5 :=Null;
1774 l_igsv_ext_tbl_in(i+3000).attribute6 :=Null;
1775 l_igsv_ext_tbl_in(i+3000).attribute7 :=Null;
1776 l_igsv_ext_tbl_in(i+3000).attribute8 :=Null;
1777 l_igsv_ext_tbl_in(i+3000).attribute9 :=Null;
1778 l_igsv_ext_tbl_in(i+3000).attribute10 :=Null;
1779 l_igsv_ext_tbl_in(i+3000).attribute11 :=Null;
1780 l_igsv_ext_tbl_in(i+3000).attribute12 :=Null;
1781 l_igsv_ext_tbl_in(i+3000).attribute13 :=Null;
1782 l_igsv_ext_tbl_in(i+3000).attribute14 :=Null;
1783 l_igsv_ext_tbl_in(i+3000).attribute15 :=Null;
1784
1785 l_ctiv_tbl_In(i+3000).rul_id :=l_rulv_tbl_in(i+3000).id;
1786 l_ctiv_tbl_in(i+3000).tve_id :=l_igsv_ext_tbl_in(i+3000).Id;
1787 l_ctiv_tbl_in(i+3000).dnz_chr_id :=l_clev_tbl_in(i).dnz_chr_Id;
1788 l_ctiv_tbl_in(i+3000).CREATION_DATE := g_CREATION_DATE;
1789 l_ctiv_tbl_in(i+3000).CREATED_BY := g_CREATED_BY;
1790 l_ctiv_tbl_in(i+3000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
1791 l_ctiv_tbl_in(i+3000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
1792 l_ctiv_tbl_in(i+3000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
1793 l_ctiv_tbl_in(i+3000).object_version_number := 1;
1794 End If;
1795
1796 IF COVERAGE_THU_START_TIME_tbl(i) IS NOT NULL THEN
1797 ---- dbms_output.put_line('Test');
1798 l_igsv_ext_tbl_in(i+4000).id :=okc_p_util.raw_to_number(sys_guid());
1799 l_igsv_ext_tbl_in(i+4000).start_day_of_week := 'THU';
1800 l_igsv_ext_tbl_in(i+4000).start_hour := to_char(COVERAGE_THU_START_TIME_tbl(i),'HH24');
1801 l_igsv_ext_tbl_in(i+4000).start_minute := to_char(COVERAGE_THU_START_TIME_tbl(i),'MI');
1802 l_igsv_ext_tbl_in(i+4000).start_second := to_char(COVERAGE_THU_START_TIME_tbl(i),'SS');
1803 l_igsv_ext_tbl_in(i+4000).end_month := Null;
1804 l_igsv_ext_tbl_in(i+4000).end_day_of_week := 'THU';
1805 l_igsv_ext_tbl_in(i+4000).end_hour := to_char(COVERAGE_THU_END_TIME_tbl(i),'HH24');
1806 l_igsv_ext_tbl_in(i+4000).end_minute := to_char(COVERAGE_THU_END_TIME_tbl(i),'MI');
1807 l_igsv_ext_tbl_in(i+4000).end_second := to_char(COVERAGE_THU_END_TIME_tbl(i),'SS');
1808 l_igsv_ext_tbl_in(i+4000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
1809 l_igsv_ext_tbl_in(i+4000).tze_id := nvl(Time_Zone_Id_Tbl(i),g_timezone_id);
1810 l_igsv_ext_tbl_in(i+4000).CREATION_DATE := g_CREATION_DATE;
1811 l_igsv_ext_tbl_in(i+4000).CREATED_BY := g_CREATED_BY;
1812 l_igsv_ext_tbl_in(i+4000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
1813 l_igsv_ext_tbl_in(i+4000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
1814 l_igsv_ext_tbl_in(i+4000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
1815 l_igsv_ext_tbl_in(i+4000).Object_version_Number :=1;
1816 -- -- dbms_output.put_line('Test1');
1817 l_igsv_ext_tbl_in(i+4000).sfwt_flag :='N';
1818 l_igsv_ext_tbl_in(i+4000).tve_id_ended :=Null;
1819 l_igsv_ext_tbl_in(i+4000).tve_id_limited :=Null;
1820 l_igsv_ext_tbl_in(i+4000).description :=Null;
1821 l_igsv_ext_tbl_in(i+4000).short_description :=Null;
1822 l_igsv_ext_tbl_in(i+4000).comments :=Null;
1823 l_igsv_ext_tbl_in(i+4000).attribute_category :=Null;
1824 l_igsv_ext_tbl_in(i+4000).attribute1 :=Null;
1825 l_igsv_ext_tbl_in(i+4000).attribute2 :=Null;
1826 l_igsv_ext_tbl_in(i+4000).attribute3 :=Null;
1827 l_igsv_ext_tbl_in(i+4000).attribute4 :=Null;
1828 l_igsv_ext_tbl_in(i+4000).attribute5 :=Null;
1829 l_igsv_ext_tbl_in(i+4000).attribute6 :=Null;
1830 l_igsv_ext_tbl_in(i+4000).attribute7 :=Null;
1831 l_igsv_ext_tbl_in(i+4000).attribute8 :=Null;
1832 l_igsv_ext_tbl_in(i+4000).attribute9 :=Null;
1833 l_igsv_ext_tbl_in(i+4000).attribute10 :=Null;
1834 l_igsv_ext_tbl_in(i+4000).attribute11 :=Null;
1835 l_igsv_ext_tbl_in(i+4000).attribute12 :=Null;
1836 l_igsv_ext_tbl_in(i+4000).attribute13 :=Null;
1837 l_igsv_ext_tbl_in(i+4000).attribute14 :=Null;
1838 l_igsv_ext_tbl_in(i+4000).attribute15 :=Null;
1839
1840 l_ctiv_tbl_In(i+4000).rul_id :=l_rulv_tbl_in(i+3000).id;
1841 l_ctiv_tbl_in(i+4000).tve_id :=l_igsv_ext_tbl_in(i+4000).Id;
1842 l_ctiv_tbl_in(i+4000).dnz_chr_id :=l_clev_tbl_in(i).dnz_chr_Id;
1843 l_ctiv_tbl_in(i+4000).CREATION_DATE := g_CREATION_DATE;
1844 l_ctiv_tbl_in(i+4000).CREATED_BY := g_CREATED_BY;
1845 l_ctiv_tbl_in(i+4000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
1846 l_ctiv_tbl_in(i+4000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
1847 l_ctiv_tbl_in(i+4000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
1848 l_ctiv_tbl_in(i+4000).object_version_number := 1;
1849
1850 End If;
1851
1852 IF COVERAGE_FRI_START_TIME_tbl(i) IS NOT NULL THEN
1853 l_igsv_ext_tbl_in(i+5000).id :=okc_p_util.raw_to_number(sys_guid());
1854 l_igsv_ext_tbl_in(i+5000).start_day_of_week := 'FRI';
1855 l_igsv_ext_tbl_in(i+5000).start_hour := to_char(COVERAGE_FRI_START_TIME_tbl(i),'HH24');
1856 l_igsv_ext_tbl_in(i+5000).start_minute := to_char(COVERAGE_FRI_START_TIME_tbl(i),'MI');
1857 l_igsv_ext_tbl_in(i+5000).start_second := to_char(COVERAGE_FRI_START_TIME_tbl(i),'SS');
1858 l_igsv_ext_tbl_in(i+5000).end_month := Null;
1859 l_igsv_ext_tbl_in(i+5000).end_day_of_week := 'FRI';
1860 l_igsv_ext_tbl_in(i+5000).end_hour := to_char(COVERAGE_FRI_END_TIME_tbl(i),'HH24');
1861 l_igsv_ext_tbl_in(i+5000).end_minute := to_char(COVERAGE_FRI_END_TIME_tbl(i),'MI');
1862 l_igsv_ext_tbl_in(i+5000).end_second := to_char(COVERAGE_FRI_END_TIME_tbl(i),'SS');
1863 l_igsv_ext_tbl_in(i+5000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
1864 l_igsv_ext_tbl_in(i+5000).tze_id := nvl(Time_Zone_Id_Tbl(i),g_timezone_id);
1865 l_igsv_ext_tbl_in(i+5000).CREATION_DATE := g_CREATION_DATE;
1866 l_igsv_ext_tbl_in(i+5000).CREATED_BY := g_CREATED_BY;
1867 l_igsv_ext_tbl_in(i+5000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
1868 l_igsv_ext_tbl_in(i+5000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
1869 l_igsv_ext_tbl_in(i+5000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
1870 l_igsv_ext_tbl_in(i+5000).Object_version_Number :=1;
1871 l_igsv_ext_tbl_in(i+5000).sfwt_flag :='N';
1872 l_igsv_ext_tbl_in(i+5000).tve_id_ended :=Null;
1873 l_igsv_ext_tbl_in(i+5000).tve_id_limited :=Null;
1874 l_igsv_ext_tbl_in(i+5000).description :=Null;
1875 l_igsv_ext_tbl_in(i+5000).short_description :=Null;
1876 l_igsv_ext_tbl_in(i+5000).comments :=Null;
1877 l_igsv_ext_tbl_in(i+5000).attribute_category :=Null;
1878 l_igsv_ext_tbl_in(i+5000).attribute1 :=Null;
1879 l_igsv_ext_tbl_in(i+5000).attribute2 :=Null;
1880 l_igsv_ext_tbl_in(i+5000).attribute3 :=Null;
1881 l_igsv_ext_tbl_in(i+5000).attribute4 :=Null;
1882 l_igsv_ext_tbl_in(i+5000).attribute5 :=Null;
1883 l_igsv_ext_tbl_in(i+5000).attribute6 :=Null;
1884 l_igsv_ext_tbl_in(i+5000).attribute7 :=Null;
1885 l_igsv_ext_tbl_in(i+5000).attribute8 :=Null;
1886 l_igsv_ext_tbl_in(i+5000).attribute9 :=Null;
1887 l_igsv_ext_tbl_in(i+5000).attribute10 :=Null;
1888 l_igsv_ext_tbl_in(i+5000).attribute11 :=Null;
1889 l_igsv_ext_tbl_in(i+5000).attribute12 :=Null;
1890 l_igsv_ext_tbl_in(i+5000).attribute13 :=Null;
1891 l_igsv_ext_tbl_in(i+5000).attribute14 :=Null;
1892 l_igsv_ext_tbl_in(i+5000).attribute15 :=Null;
1893
1894 l_ctiv_tbl_In(i+5000).rul_id :=l_rulv_tbl_in(i+3000).id;
1895 l_ctiv_tbl_in(i+5000).tve_id :=l_igsv_ext_tbl_in(i+5000).Id;
1896 l_ctiv_tbl_in(i+5000).dnz_chr_id :=l_clev_tbl_in(i).dnz_chr_Id;
1897 l_ctiv_tbl_in(i+5000).CREATION_DATE := g_CREATION_DATE;
1898 l_ctiv_tbl_in(i+5000).CREATED_BY := g_CREATED_BY;
1899 l_ctiv_tbl_in(i+5000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
1900 l_ctiv_tbl_in(i+5000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
1901 l_ctiv_tbl_in(i+5000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
1902 l_ctiv_tbl_in(i+5000).object_version_number := 1;
1903 End If;
1904 IF COVERAGE_SAT_START_TIME_tbl(i) IS NOT NULL THEN
1905 l_igsv_ext_tbl_in(i+6000).id :=okc_p_util.raw_to_number(sys_guid());
1906 l_igsv_ext_tbl_in(i+6000).start_day_of_week := 'SAT';
1907 l_igsv_ext_tbl_in(i+6000).start_hour := to_char(COVERAGE_SAT_START_TIME_tbl(i),'HH24');
1908 l_igsv_ext_tbl_in(i+6000).start_minute := to_char(COVERAGE_SAT_START_TIME_tbl(i),'MI');
1909 l_igsv_ext_tbl_in(i+6000).start_second := to_char(COVERAGE_SAT_START_TIME_tbl(i),'SS');
1910 l_igsv_ext_tbl_in(i+6000).end_month := Null;
1911 l_igsv_ext_tbl_in(i+6000).end_day_of_week := 'SAT';
1912 l_igsv_ext_tbl_in(i+6000).end_hour := to_char(COVERAGE_SAT_END_TIME_tbl(i),'HH24');
1913 l_igsv_ext_tbl_in(i+6000).end_minute := to_char(COVERAGE_SAT_END_TIME_tbl(i),'MI');
1914 l_igsv_ext_tbl_in(i+6000).end_second := to_char(COVERAGE_SAT_END_TIME_tbl(i),'SS');
1915 l_igsv_ext_tbl_in(i+6000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
1916 l_igsv_ext_tbl_in(i+6000).tze_id := nvl(Time_Zone_Id_Tbl(i),g_timezone_id);
1917 l_igsv_ext_tbl_in(i+6000).CREATION_DATE := g_CREATION_DATE;
1918 l_igsv_ext_tbl_in(i+6000).CREATED_BY := g_CREATED_BY;
1919 l_igsv_ext_tbl_in(i+6000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
1920 l_igsv_ext_tbl_in(i+6000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
1921 l_igsv_ext_tbl_in(i+6000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
1922 l_igsv_ext_tbl_in(i+6000).Object_version_Number :=1;
1923 l_igsv_ext_tbl_in(i+6000).sfwt_flag :='N';
1924 l_igsv_ext_tbl_in(i+6000).tve_id_ended :=Null;
1925 l_igsv_ext_tbl_in(i+6000).tve_id_limited :=Null;
1926 l_igsv_ext_tbl_in(i+6000).description :=Null;
1927 l_igsv_ext_tbl_in(i+6000).short_description :=Null;
1928 l_igsv_ext_tbl_in(i+6000).comments :=Null;
1929 l_igsv_ext_tbl_in(i+6000).attribute_category :=Null;
1930 l_igsv_ext_tbl_in(i+6000).attribute1 :=Null;
1931 l_igsv_ext_tbl_in(i+6000).attribute2 :=Null;
1932 l_igsv_ext_tbl_in(i+6000).attribute3 :=Null;
1933 l_igsv_ext_tbl_in(i+6000).attribute4 :=Null;
1934 l_igsv_ext_tbl_in(i+6000).attribute5 :=Null;
1935 l_igsv_ext_tbl_in(i+6000).attribute6 :=Null;
1936 l_igsv_ext_tbl_in(i+6000).attribute7 :=Null;
1937 l_igsv_ext_tbl_in(i+6000).attribute8 :=Null;
1938 l_igsv_ext_tbl_in(i+6000).attribute9 :=Null;
1939 l_igsv_ext_tbl_in(i+6000).attribute10 :=Null;
1940 l_igsv_ext_tbl_in(i+6000).attribute11 :=Null;
1941 l_igsv_ext_tbl_in(i+6000).attribute12 :=Null;
1942 l_igsv_ext_tbl_in(i+6000).attribute13 :=Null;
1943 l_igsv_ext_tbl_in(i+6000).attribute14 :=Null;
1944 l_igsv_ext_tbl_in(i+6000).attribute15 :=Null;
1945
1946 l_ctiv_tbl_In(i+6000).rul_id :=l_rulv_tbl_in(i+3000).id;
1947 l_ctiv_tbl_in(i+6000).tve_id :=l_igsv_ext_tbl_in(i+6000).Id;
1948 l_ctiv_tbl_in(i+6000).dnz_chr_id :=l_clev_tbl_in(i).dnz_chr_Id;
1949 l_ctiv_tbl_in(i+6000).CREATION_DATE := g_CREATION_DATE;
1950 l_ctiv_tbl_in(i+6000).CREATED_BY := g_CREATED_BY;
1951 l_ctiv_tbl_in(i+6000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
1952 l_ctiv_tbl_in(i+6000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
1953 l_ctiv_tbl_in(i+6000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
1954 l_ctiv_tbl_in(i+6000).object_version_number := 1;
1955 End If;
1956
1957 /* Temparary Changes Made to pass through QA check.
1958 l_cplv_tbl_in(i).id :=okc_p_util.raw_to_number(sys_guid());
1959 l_cplv_tbl_in(i).chr_id := NULL;
1960 l_cplv_tbl_in(i).sfwt_flag :='N';
1961 l_cplv_tbl_in(i).cle_id := l_clev_tbl_in(i).Id;
1962 l_cplv_tbl_in(i).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
1963 l_cplv_tbl_in(i).rle_code := 'VENDOR';
1964 -- l_cplv_tbl_in(i).object1_id1 := '204';
1965 l_cplv_tbl_in(i).object1_id2 := '#';
1966 l_cplv_tbl_in(i).jtot_object1_code := 'OKX_OPERUNIT';
1967 l_cplv_tbl_in(i).CREATION_DATE := g_CREATION_DATE;
1968 l_cplv_tbl_in(i).CREATED_BY := g_CREATED_BY;
1969 l_cplv_tbl_in(i).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
1970 l_cplv_tbl_in(i).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
1971 l_cplv_tbl_in(i).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
1972 l_cplv_tbl_in(i).object_version_number := 1;
1973 l_cplv_tbl_in(i).cpl_id := NULL;
1974 l_cplv_tbl_in(i).CODE :=null;
1975 l_cplv_tbl_in(i).FACILITY :=null;
1976 l_cplv_tbl_in(i).MINORITY_GROUP_LOOKUP_CODE :=null;
1977 l_cplv_tbl_in(i).SMALL_BUSINESS_FLAG :=null;
1978 l_cplv_tbl_in(i).WOMEN_OWNED_FLAG :=null;
1979 l_cplv_tbl_in(i).LAST_UPDATE_LOGIN :=null;
1980 l_cplv_tbl_in(i).ATTRIBUTE_CATEGORY :=null;
1981 l_cplv_tbl_in(i).ATTRIBUTE1 :=null;
1982 l_cplv_tbl_in(i).ATTRIBUTE2 :=null;
1983 l_cplv_tbl_in(i).ATTRIBUTE3 :=null;
1984 l_cplv_tbl_in(i).ATTRIBUTE4 :=null;
1985 l_cplv_tbl_in(i).ATTRIBUTE5 :=null;
1986 l_cplv_tbl_in(i).ATTRIBUTE6 :=null;
1987 l_cplv_tbl_in(i).ATTRIBUTE7 :=null;
1988 l_cplv_tbl_in(i).ATTRIBUTE8 :=null;
1989 l_cplv_tbl_in(i).ATTRIBUTE9 :=null;
1990 l_cplv_tbl_in(i).ATTRIBUTE10 :=null;
1991 l_cplv_tbl_in(i).ATTRIBUTE11 :=null;
1992 l_cplv_tbl_in(i).ATTRIBUTE12 :=null;
1993 l_cplv_tbl_in(i).ATTRIBUTE13 :=null;
1994 l_cplv_tbl_in(i).ATTRIBUTE14 :=null;
1995 l_cplv_tbl_in(i).ATTRIBUTE15 :=null;
1996
1997 l_ctcv_tbl_in(i).id :=okc_p_util.raw_to_number(sys_guid());
1998 l_ctcv_tbl_in(i).cpl_id := l_cplv_tbl_in(i).id;
1999 l_ctcv_tbl_in(i).cro_code := 'ENGINEER';
2000 l_ctcv_tbl_in(i).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
2001 l_ctcv_tbl_in(i).contact_sequence := 1;
2002 l_ctcv_tbl_in(i).CREATION_DATE := g_CREATION_DATE;
2003 l_ctcv_tbl_in(i).CREATED_BY := g_CREATED_BY;
2004 l_ctcv_tbl_in(i).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2005 l_ctcv_tbl_in(i).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2006 l_ctcv_tbl_in(i).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2007 l_ctcv_tbl_in(i).object_version_number := 1;
2008 l_ctcv_tbl_in(i).object1_id1 := Get_PrefEng(PREFERRED_RESOURCE_ID_tbl(i));
2009 l_ctcv_tbl_in(i).object1_id2 := '#';
2010 l_ctcv_tbl_in(i).jtot_object1_code := 'OKX_RESOURCE';
2011 l_ctcv_tbl_in(i).LAST_UPDATE_LOGIN :=null;
2012 l_ctcv_tbl_in(i).ATTRIBUTE_CATEGORY :=null;
2013 l_ctcv_tbl_in(i).ATTRIBUTE1 :=null;
2014 l_ctcv_tbl_in(i).ATTRIBUTE2 :=null;
2015 l_ctcv_tbl_in(i).ATTRIBUTE3 :=null;
2016 l_ctcv_tbl_in(i).ATTRIBUTE4 :=null;
2017 l_ctcv_tbl_in(i).ATTRIBUTE5 :=null;
2018 l_ctcv_tbl_in(i).ATTRIBUTE6 :=null;
2019 l_ctcv_tbl_in(i).ATTRIBUTE7 :=null;
2020 l_ctcv_tbl_in(i).ATTRIBUTE8 :=null;
2021 l_ctcv_tbl_in(i).ATTRIBUTE9 :=null;
2022 l_ctcv_tbl_in(i).ATTRIBUTE10 :=null;
2023 l_ctcv_tbl_in(i).ATTRIBUTE11 :=null;
2024 l_ctcv_tbl_in(i).ATTRIBUTE12 :=null;
2025 l_ctcv_tbl_in(i).ATTRIBUTE13 :=null;
2026 l_ctcv_tbl_in(i).ATTRIBUTE14 :=null;
2027 l_ctcv_tbl_in(i).ATTRIBUTE15 :=null;
2028
2029 */
2030 IF REACTION_TIME_ID_TBL(i) IS NOT NULL THEN
2031 -- -- dbms_output.put_line('Test1');
2032 l_clev_tbl_in(i+1000).id := okc_p_util.raw_to_number(sys_guid());
2033 l_clev_tbl_in(i+1000).CREATION_DATE := g_CREATION_DATE;
2034 l_clev_tbl_in(i+1000).CREATED_BY := g_CREATED_BY;
2035 l_clev_tbl_in(i+1000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2036 l_clev_tbl_in(i+1000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2037 l_clev_tbl_in(i+1000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2038 l_clev_tbl_in(i+1000).object_version_number := 1;
2039 l_clev_tbl_in(i+1000).cle_id := l_clev_tbl_in(i).Id;
2040 l_clev_tbl_in(i+1000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
2041 l_clev_tbl_in(i+1000).sfwt_flag :='N';
2042 l_clev_tbl_in(i+1000).lse_id :=4;
2043 l_clev_tbl_in(i+1000).line_number :=1;
2044 -- -- dbms_output.put_line('Test2');
2045 -- l_clev_tbl_in(i+1000).sts_code :='ENTERED'; --Get_New_Status(Contract_Line_Status_Id_Tbl(i));
2046 l_clev_tbl_in(i+1000).sts_code :=sts_code_tbl(i);
2047 l_clev_tbl_in(i+1000).display_sequence :=1;
2048 l_clev_tbl_in(i+1000).item_description :=reaction_Description_Tbl(i);
2049 l_clev_tbl_in(i+1000).Name :=reaction_name_Tbl(i);
2050 l_clev_tbl_in(i+1000).exception_yn :='N'; -- clarify
2051 l_clev_tbl_in(i+1000).Currency_Code :=Currency_Code_Tbl(i);
2052 l_clev_tbl_in(i+1000).start_date :=start_date_tbl(i);
2053 l_clev_tbl_in(i+1000).end_date :=end_date_tbl(i);
2054 -- -- dbms_output.put_line('Test3');
2055 l_clev_tbl_in(i+1000).attribute1 :=Attribute1_Tbl(i);
2056 l_clev_tbl_in(i+1000).attribute2 :=Attribute2_Tbl(i);
2057 l_clev_tbl_in(i+1000).attribute3 :=Attribute3_Tbl(i);
2058 l_clev_tbl_in(i+1000).attribute4 :=Attribute4_Tbl(i);
2059 l_clev_tbl_in(i+1000).attribute5 :=Attribute5_Tbl(i);
2060 l_clev_tbl_in(i+1000).attribute6 :=Attribute6_Tbl(i);
2061 l_clev_tbl_in(i+1000).attribute7 :=Attribute7_Tbl(i);
2062 l_clev_tbl_in(i+1000).attribute8 :=Attribute8_Tbl(i);
2063 l_clev_tbl_in(i+1000).attribute9 :=Attribute9_Tbl(i);
2064 l_clev_tbl_in(i+1000).attribute10 :=Attribute10_Tbl(i);
2065 l_clev_tbl_in(i+1000).attribute11 :=Attribute11_Tbl(i);
2066 l_clev_tbl_in(i+1000).attribute12 :=Attribute12_Tbl(i);
2067 l_clev_tbl_in(i+1000).attribute13 :=Attribute13_Tbl(i);
2068 l_clev_tbl_in(i+1000).attribute14 :=Attribute14_Tbl(i);
2069 l_clev_tbl_in(i+1000).attribute15 :=Attribute15_Tbl(i);
2070 -- -- dbms_output.put_line('Test4');
2071 l_clev_tbl_in(i+1000).attribute_Category :=attribute_Category_Tbl(i);
2072 l_clev_tbl_in(i+1000).Upg_Orig_System_Ref :='oks_cov_txn_groups_int_all REACTION_TIMES';
2073 l_clev_tbl_in(i+1000).Upg_Orig_System_Ref_Id :=Reaction_Time_ID_Tbl(i);
2074 l_clev_tbl_in(i+1000).INVOICE_LINE_LEVEL_IND :=Null;
2075 l_clev_tbl_in(i+1000).DPAS_RATING :=Null;
2076 l_clev_tbl_in(i+1000).TEMPLATE_USED :=Null;
2077 l_clev_tbl_in(i+1000).PRICE_TYPE :=Null;
2078 -- l_clev_tbl_in(i+1000).UOM_CODE :=Null;
2079 l_clev_tbl_in(i+1000).CHR_ID :=Null;
2080 l_clev_tbl_in(i+1000).TRN_CODE :=Null;
2081 l_clev_tbl_in(i+1000).HIDDEN_IND :=Null;
2082 l_clev_tbl_in(i+1000).DATE_TERMINATED :=Null;
2083 l_clev_tbl_in(i+1000).CLE_ID_RENEWED_TO :=Null;
2084 l_clev_tbl_in(i+1000).CURRENCY_CODE_RENEWED :=Null;
2085 l_clev_tbl_in(i+1000).PRICE_NEGOTIATED_RENEWED :=Null;
2086 l_clev_tbl_in(i+1000).cle_id_renewed :=NULL;
2087 l_clev_tbl_in(i+1000).comments :=NULL;
2088 l_clev_tbl_in(i+1000).price_unit :=NULL;
2089 l_clev_tbl_in(i+1000).price_unit_percent :=NULL;
2090 l_clev_tbl_in(i+1000).price_negotiated :=NULL;
2091 l_clev_tbl_in(i+1000).price_level_ind :='N';
2092 l_clev_tbl_in(i+1000).block23text :=NULL;
2093 l_clev_tbl_in(i+1000).program_application_id:= fnd_global.prog_appl_id;
2094 l_clev_tbl_in(i+1000).program_id:= fnd_global.CONC_PROGRAM_ID;
2095 -- -- dbms_output.put_line('Test5');
2096
2097
2098 l_rgpv_tbl_in(i+1000).cle_id := l_clev_tbl_in(i+1000).id;
2099 l_rgpv_tbl_in(i+1000).sfwt_flag := 'N';
2100 l_rgpv_tbl_in(i+1000).rgd_code := 'SVC_K';
2101 l_rgpv_tbl_in(i+1000).rgp_type := 'KRG';
2102 l_rgpv_tbl_in(i+1000).dnz_chr_id := l_clev_tbl_in(i+1000).dnz_chr_Id;
2103 l_rgpv_tbl_in(i+1000).id := okc_p_util.raw_to_number(sys_guid());
2104 l_rgpv_tbl_in(i+1000).CREATION_DATE := g_CREATION_DATE;
2105 l_rgpv_tbl_in(i+1000).CREATED_BY := g_CREATED_BY;
2106 l_rgpv_tbl_in(i+1000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2107 l_rgpv_tbl_in(i+1000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2108 l_rgpv_tbl_in(i+1000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2109 l_rgpv_tbl_in(i+1000).object_version_number := 1;
2110 l_rgpv_tbl_in(i+1000).CHR_ID :=Null;
2111 l_rgpv_tbl_in(i+1000).PARENT_RGP_ID :=Null;
2112 l_rgpv_tbl_in(i+1000).SAT_CODE :=Null;
2113 l_rgpv_tbl_in(i+1000).COMMENTS :=Null;
2114 l_rgpv_tbl_in(i+1000).ATTRIBUTE_CATEGORY :=Null;
2115 l_rgpv_tbl_in(i+1000).ATTRIBUTE1 :=Null;
2116 l_rgpv_tbl_in(i+1000).ATTRIBUTE2 :=Null;
2117 l_rgpv_tbl_in(i+1000).ATTRIBUTE3 :=Null;
2118 l_rgpv_tbl_in(i+1000).ATTRIBUTE4 :=Null;
2119 l_rgpv_tbl_in(i+1000).ATTRIBUTE5 :=Null;
2120 l_rgpv_tbl_in(i+1000).ATTRIBUTE6 :=Null;
2121 l_rgpv_tbl_in(i+1000).ATTRIBUTE7 :=Null;
2122 l_rgpv_tbl_in(i+1000).ATTRIBUTE8 :=Null;
2123 l_rgpv_tbl_in(i+1000).ATTRIBUTE9 :=Null;
2124 l_rgpv_tbl_in(i+1000).ATTRIBUTE10 :=Null;
2125 l_rgpv_tbl_in(i+1000).ATTRIBUTE11 :=Null;
2126 l_rgpv_tbl_in(i+1000).ATTRIBUTE12 :=Null;
2127 l_rgpv_tbl_in(i+1000).ATTRIBUTE13 :=Null;
2128 l_rgpv_tbl_in(i+1000).ATTRIBUTE14 :=Null;
2129 l_rgpv_tbl_in(i+1000).ATTRIBUTE15 :=Null;
2130
2131
2132 END IF ;------REACTION_TIME_ID_TBL(i)
2133
2134 IF Incident_severity_id_Tbl(i) IS Not NULL then
2135
2136
2137 l_rulv_tbl_in(i+4000).rgp_id := l_rgpv_tbl_in(i+1000).id;
2138 l_rulv_tbl_in(i+4000).sfwt_flag := 'N';
2139 l_rulv_tbl_in(i+4000).rule_information_category := 'RCN';
2140 l_rulv_tbl_in(i+4000).id := okc_p_util.raw_to_number(sys_guid());
2141 l_rulv_tbl_in(i+4000).CREATION_DATE := g_CREATION_DATE;
2142 l_rulv_tbl_in(i+4000).CREATED_BY := g_CREATED_BY;
2143 l_rulv_tbl_in(i+4000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2144 l_rulv_tbl_in(i+4000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2145 l_rulv_tbl_in(i+4000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2146 l_rulv_tbl_in(i+4000).object_version_number := 1;
2147 l_rulv_tbl_in(i+4000).std_template_yn := 'N';
2148 l_rulv_tbl_in(i+4000).warn_yn := 'N';
2149 l_rulv_tbl_in(i+4000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
2150 l_rulv_tbl_in(i+4000).rule_information1 := NULL; --WORKFLOW_Tbl(i); --- PDF
2151 l_rulv_tbl_in(i+4000).rule_information2 := reaction_name_Tbl(i);
2152 l_rulv_tbl_in(i+4000).rule_information3 := 'N'; --ALWAYS_COVERED_Tbl(i); --'N';--always_covered;
2153 l_rulv_tbl_in(i+4000).rule_information4 := 'N'; --Use_For_SR_Date_Calc;
2154 l_rulv_tbl_in(i+4000).object1_id1 :=Incident_severity_id_Tbl(i);
2155 l_rulv_tbl_in(i+4000).object1_id2 := '#';
2156 l_rulv_tbl_in(i+4000).jtot_object1_code := 'OKX_REACTIME';
2157 l_rulv_tbl_in(i+4000).PRIORITY := NULL;
2158 l_rulv_tbl_in(i+4000).OBJECT2_ID1 := NULL;
2159 l_rulv_tbl_in(i+4000).OBJECT3_ID1 := NULL;
2160 l_rulv_tbl_in(i+4000).OBJECT2_ID2 := NULL;
2161 l_rulv_tbl_in(i+4000).OBJECT3_ID2 := NULL;
2162 l_rulv_tbl_in(i+4000).JTOT_OBJECT2_CODE := NULL;
2163 l_rulv_tbl_in(i+4000).JTOT_OBJECT3_CODE := NULL;
2164 l_rulv_tbl_in(i+4000).PRIORITY := NULL;
2165 l_rulv_tbl_in(i+4000).COMMENTS := NULL;
2166 l_rulv_tbl_in(i+4000).ATTRIBUTE_CATEGORY := NULL;
2167 l_rulv_tbl_in(i+4000).ATTRIBUTE1 := NULL;
2168 l_rulv_tbl_in(i+4000).ATTRIBUTE2 := NULL;
2169 l_rulv_tbl_in(i+4000).ATTRIBUTE3 := NULL;
2170 l_rulv_tbl_in(i+4000).ATTRIBUTE4 := NULL;
2171 l_rulv_tbl_in(i+4000).ATTRIBUTE5 := NULL;
2172 l_rulv_tbl_in(i+4000).ATTRIBUTE6 := NULL;
2173 l_rulv_tbl_in(i+4000).ATTRIBUTE7 := NULL;
2174 l_rulv_tbl_in(i+4000).ATTRIBUTE8 := NULL;
2175 l_rulv_tbl_in(i+4000).ATTRIBUTE9 := NULL;
2176 l_rulv_tbl_in(i+4000).ATTRIBUTE10 := NULL;
2177 l_rulv_tbl_in(i+4000).ATTRIBUTE11 := NULL;
2178 l_rulv_tbl_in(i+4000).ATTRIBUTE12 := NULL;
2179 l_rulv_tbl_in(i+4000).ATTRIBUTE13 := NULL;
2180 l_rulv_tbl_in(i+4000).ATTRIBUTE14 := NULL;
2181 l_rulv_tbl_in(i+4000).ATTRIBUTE15 := NULL;
2182 l_rulv_tbl_in(i+4000).TEXT := NULL;
2183 l_rulv_tbl_in(i+4000).RULE_INFORMATION5 := NULL;
2184 l_rulv_tbl_in(i+4000).RULE_INFORMATION6 := NULL;
2185 l_rulv_tbl_in(i+4000).RULE_INFORMATION7 := NULL;
2186 l_rulv_tbl_in(i+4000).RULE_INFORMATION8 := NULL;
2187 l_rulv_tbl_in(i+4000).RULE_INFORMATION9 := NULL;
2188 l_rulv_tbl_in(i+4000).RULE_INFORMATION10 := NULL;
2189 l_rulv_tbl_in(i+4000).RULE_INFORMATION11 := NULL;
2190 l_rulv_tbl_in(i+4000).RULE_INFORMATION12 := NULL;
2191 l_rulv_tbl_in(i+4000).RULE_INFORMATION13 := NULL;
2192 l_rulv_tbl_in(i+4000).RULE_INFORMATION14 := NULL;
2193 l_rulv_tbl_in(i+4000).RULE_INFORMATION15 := NULL;
2194
2195 l_rulv_tbl_in(i+5000).rgp_id := l_rgpv_tbl_in(i+1000).id;
2196 l_rulv_tbl_in(i+5000).sfwt_flag := 'N';
2197 l_rulv_tbl_in(i+5000).rule_information_category := 'RSN';
2198 l_rulv_tbl_in(i+5000).id := okc_p_util.raw_to_number(sys_guid());
2199 l_rulv_tbl_in(i+5000).CREATION_DATE := g_CREATION_DATE;
2200 l_rulv_tbl_in(i+5000).CREATED_BY := g_CREATED_BY;
2201 l_rulv_tbl_in(i+5000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2202 l_rulv_tbl_in(i+5000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2203 l_rulv_tbl_in(i+5000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2204 l_rulv_tbl_in(i+5000).object_version_number := 1;
2205 l_rulv_tbl_in(i+5000).std_template_yn := 'N';
2206 l_rulv_tbl_in(i+5000).warn_yn := 'N';
2207 l_rulv_tbl_in(i+5000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
2208 l_rulv_tbl_in(i+5000).rule_information1 := NULL; --WORKFLOW_Tbl(i); --- PDF
2209 l_rulv_tbl_in(i+5000).rule_information2 := reaction_name_Tbl(i);
2210 l_rulv_tbl_in(i+5000).rule_information3 := 'N'; --ALWAYS_COVERED_Tbl(i); --'N';--always_covered;
2211 l_rulv_tbl_in(i+5000).rule_information4 := 'N'; --Use_For_SR_Date_Calc;
2212 l_rulv_tbl_in(i+5000).object1_id1 :=Incident_severity_id_Tbl(i);
2213 l_rulv_tbl_in(i+5000).object1_id2 := '#';
2214 l_rulv_tbl_in(i+5000).jtot_object1_code := 'OKX_REACTIME';
2215 l_rulv_tbl_in(i+5000).PRIORITY := NULL;
2216 l_rulv_tbl_in(i+5000).OBJECT2_ID1 := NULL;
2217 l_rulv_tbl_in(i+5000).OBJECT3_ID1 := NULL;
2218 l_rulv_tbl_in(i+5000).OBJECT2_ID2 := NULL;
2219 l_rulv_tbl_in(i+5000).OBJECT3_ID2 := NULL;
2220 l_rulv_tbl_in(i+5000).JTOT_OBJECT2_CODE := NULL;
2221 l_rulv_tbl_in(i+5000).JTOT_OBJECT3_CODE := NULL;
2222 l_rulv_tbl_in(i+5000).PRIORITY := NULL;
2223 l_rulv_tbl_in(i+5000).COMMENTS := NULL;
2224 l_rulv_tbl_in(i+5000).ATTRIBUTE_CATEGORY := NULL;
2225 l_rulv_tbl_in(i+5000).ATTRIBUTE1 := NULL;
2226 l_rulv_tbl_in(i+5000).ATTRIBUTE2 := NULL;
2227 l_rulv_tbl_in(i+5000).ATTRIBUTE3 := NULL;
2228 l_rulv_tbl_in(i+5000).ATTRIBUTE4 := NULL;
2229 l_rulv_tbl_in(i+5000).ATTRIBUTE5 := NULL;
2230 l_rulv_tbl_in(i+5000).ATTRIBUTE6 := NULL;
2231 l_rulv_tbl_in(i+5000).ATTRIBUTE7 := NULL;
2232 l_rulv_tbl_in(i+5000).ATTRIBUTE8 := NULL;
2233 l_rulv_tbl_in(i+5000).ATTRIBUTE9 := NULL;
2234 l_rulv_tbl_in(i+5000).ATTRIBUTE10 := NULL;
2235 l_rulv_tbl_in(i+5000).ATTRIBUTE11 := NULL;
2236 l_rulv_tbl_in(i+5000).ATTRIBUTE12 := NULL;
2237 l_rulv_tbl_in(i+5000).ATTRIBUTE13 := NULL;
2238 l_rulv_tbl_in(i+5000).ATTRIBUTE14 := NULL;
2239 l_rulv_tbl_in(i+5000).ATTRIBUTE15 := NULL;
2240 l_rulv_tbl_in(i+5000).TEXT := NULL;
2241 l_rulv_tbl_in(i+5000).RULE_INFORMATION5 := NULL;
2242 l_rulv_tbl_in(i+5000).RULE_INFORMATION6 := NULL;
2243 l_rulv_tbl_in(i+5000).RULE_INFORMATION7 := NULL;
2244 l_rulv_tbl_in(i+5000).RULE_INFORMATION8 := NULL;
2245 l_rulv_tbl_in(i+5000).RULE_INFORMATION9 := NULL;
2246 l_rulv_tbl_in(i+5000).RULE_INFORMATION10 := NULL;
2247 l_rulv_tbl_in(i+5000).RULE_INFORMATION11 := NULL;
2248 l_rulv_tbl_in(i+5000).RULE_INFORMATION12 := NULL;
2249 l_rulv_tbl_in(i+5000).RULE_INFORMATION13 := NULL;
2250 l_rulv_tbl_in(i+5000).RULE_INFORMATION14 := NULL;
2251 l_rulv_tbl_in(i+5000).RULE_INFORMATION15 := NULL;
2252 END IF; -------Incident_severity_id_Tbl(i)
2253
2254 IF SUN_REACTION_TIMES_tbl(i) IS NOT NULL
2255 THEN
2256 l_tgdv_ext_tbl_in(i).id :=okc_p_util.raw_to_number(sys_guid());
2257 l_tgdv_ext_tbl_in(i).day_of_week := 'SUN';
2258 l_tgdv_ext_tbl_in(i).dnz_chr_id :=l_clev_tbl_in(i).dnz_chr_Id;
2259 l_tgdv_ext_tbl_in(i).CREATION_DATE := g_CREATION_DATE;
2260 l_tgdv_ext_tbl_in(i).CREATED_BY := g_CREATED_BY;
2261 l_tgdv_ext_tbl_in(i).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2262 l_tgdv_ext_tbl_in(i).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2263 l_tgdv_ext_tbl_in(i).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2264 l_tgdv_ext_tbl_in(i).Object_Version_Number := 1;
2265 l_tgdv_ext_tbl_in(i).SFWT_FLAG := 'N' ;
2266 l_tgdv_ext_tbl_in(i).TZE_ID := NULL ;
2267 l_tgdv_ext_tbl_in(i).TVE_ID_LIMITED := NULL ;
2268 l_tgdv_ext_tbl_in(i).DESCRIPTION := NULL ;
2269 l_tgdv_ext_tbl_in(i).SHORT_DESCRIPTION := NULL ;
2270 l_tgdv_ext_tbl_in(i).COMMENTS := NULL ;
2271 l_tgdv_ext_tbl_in(i).MONTH := NULL ;
2272 l_tgdv_ext_tbl_in(i).DAY := NULL ;
2273 l_tgdv_ext_tbl_in(i).HOUR := NULL ;
2274 l_tgdv_ext_tbl_in(i).MINUTE := NULL ;
2275 l_tgdv_ext_tbl_in(i).SECOND := NULL ;
2276 l_tgdv_ext_tbl_in(i).NTH := NULL ;
2277 l_tgdv_ext_tbl_in(i).ATTRIBUTE_CATEGORY := NULL ;
2278 l_tgdv_ext_tbl_in(i).ATTRIBUTE1 := NULL ;
2279 l_tgdv_ext_tbl_in(i).ATTRIBUTE2 := NULL ;
2280 l_tgdv_ext_tbl_in(i).ATTRIBUTE3 := NULL ;
2281 l_tgdv_ext_tbl_in(i).ATTRIBUTE4 := NULL ;
2282 l_tgdv_ext_tbl_in(i).ATTRIBUTE5 := NULL ;
2283 l_tgdv_ext_tbl_in(i).ATTRIBUTE6 := NULL ;
2284 l_tgdv_ext_tbl_in(i).ATTRIBUTE7 := NULL ;
2285 l_tgdv_ext_tbl_in(i).ATTRIBUTE8 := NULL ;
2286 l_tgdv_ext_tbl_in(i).ATTRIBUTE9 := NULL ;
2287 l_tgdv_ext_tbl_in(i).ATTRIBUTE10 := NULL ;
2288 l_tgdv_ext_tbl_in(i).ATTRIBUTE11 := NULL ;
2289 l_tgdv_ext_tbl_in(i).ATTRIBUTE12 := NULL ;
2290 l_tgdv_ext_tbl_in(i).ATTRIBUTE13 := NULL ;
2291 l_tgdv_ext_tbl_in(i).ATTRIBUTE14 := NULL ;
2292 l_tgdv_ext_tbl_in(i).ATTRIBUTE15 := NULL ;
2293
2294
2295 l_rilv_tbl_in(i).tve_id :=l_tgdv_ext_tbl_in(i).id;
2296 l_rilv_tbl_in(i).rul_id :=l_rulv_tbl_in(i+ 4000).id;
2297 l_rilv_tbl_in(i).uom_code :='HR';
2298 l_rilv_tbl_in(i).duration :=SUN_REACTION_TIMES_tbl(i);
2299 l_rilv_tbl_in(i).object_version_number :=1;
2300 l_rilv_tbl_in(i).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
2301 l_rilv_tbl_in(i).CREATION_DATE := g_CREATION_DATE;
2302 l_rilv_tbl_in(i).CREATED_BY := g_CREATED_BY;
2303 l_rilv_tbl_in(i).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2304 l_rilv_tbl_in(i).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2305 l_rilv_tbl_in(i).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2306 l_rilv_tbl_in(i).object_version_number := 1;
2307
2308 END IF; ---SUN_REACTION_TIMES_tbl
2309
2310 IF MON_REACTION_TIMES_tbl(i) IS NOT NULL
2311 THEN
2312 l_tgdv_ext_tbl_in(i+1000).id :=okc_p_util.raw_to_number(sys_guid());
2313 l_tgdv_ext_tbl_in(i+1000).day_of_week := 'MON';
2314 l_tgdv_ext_tbl_in(i+1000).dnz_chr_id :=l_clev_tbl_in(i).dnz_chr_Id;
2315 l_tgdv_ext_tbl_in(i+1000).CREATION_DATE := g_CREATION_DATE;
2316 l_tgdv_ext_tbl_in(i+1000).CREATED_BY := g_CREATED_BY;
2317 l_tgdv_ext_tbl_in(i+1000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2318 l_tgdv_ext_tbl_in(i+1000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2319 l_tgdv_ext_tbl_in(i+1000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2320 l_tgdv_ext_tbl_in(i+1000).Object_Version_Number := 1;
2321 l_tgdv_ext_tbl_in(i+1000).SFWT_FLAG := 'N' ;
2322 l_tgdv_ext_tbl_in(i+1000).TZE_ID := NULL ;
2323 l_tgdv_ext_tbl_in(i+1000).TVE_ID_LIMITED := NULL ;
2324 l_tgdv_ext_tbl_in(i+1000).DESCRIPTION := NULL ;
2325 l_tgdv_ext_tbl_in(i+1000).SHORT_DESCRIPTION := NULL ;
2326 l_tgdv_ext_tbl_in(i+1000).COMMENTS := NULL ;
2327 l_tgdv_ext_tbl_in(i+1000).MONTH := NULL ;
2328 l_tgdv_ext_tbl_in(i+1000).DAY := NULL ;
2329 l_tgdv_ext_tbl_in(i+1000).HOUR := NULL ;
2330 l_tgdv_ext_tbl_in(i+1000).MINUTE := NULL ;
2331 l_tgdv_ext_tbl_in(i+1000).SECOND := NULL ;
2332 l_tgdv_ext_tbl_in(i+1000).NTH := NULL ;
2333 l_tgdv_ext_tbl_in(i+1000).ATTRIBUTE_CATEGORY := NULL ;
2334 l_tgdv_ext_tbl_in(i+1000).ATTRIBUTE1 := NULL ;
2335 l_tgdv_ext_tbl_in(i+1000).ATTRIBUTE2 := NULL ;
2336 l_tgdv_ext_tbl_in(i+1000).ATTRIBUTE3 := NULL ;
2337 l_tgdv_ext_tbl_in(i+1000).ATTRIBUTE4 := NULL ;
2338 l_tgdv_ext_tbl_in(i+1000).ATTRIBUTE5 := NULL ;
2339 l_tgdv_ext_tbl_in(i+1000).ATTRIBUTE6 := NULL ;
2340 l_tgdv_ext_tbl_in(i+1000).ATTRIBUTE7 := NULL ;
2341 l_tgdv_ext_tbl_in(i+1000).ATTRIBUTE8 := NULL ;
2342 l_tgdv_ext_tbl_in(i+1000).ATTRIBUTE9 := NULL ;
2343 l_tgdv_ext_tbl_in(i+1000).ATTRIBUTE10 := NULL ;
2344 l_tgdv_ext_tbl_in(i+1000).ATTRIBUTE11 := NULL ;
2345 l_tgdv_ext_tbl_in(i+1000).ATTRIBUTE12 := NULL ;
2346 l_tgdv_ext_tbl_in(i+1000).ATTRIBUTE13 := NULL ;
2347 l_tgdv_ext_tbl_in(i+1000).ATTRIBUTE14 := NULL ;
2348 l_tgdv_ext_tbl_in(i+1000).ATTRIBUTE15 := NULL ;
2349
2350
2351 l_rilv_tbl_in(i+1000).tve_id :=l_tgdv_ext_tbl_in(i+1000).id;
2352 l_rilv_tbl_in(i+1000).rul_id :=l_rulv_tbl_in(i+4000).id;
2353 l_rilv_tbl_in(i+1000).uom_code :='HR';
2354 l_rilv_tbl_in(i+1000).duration :=MON_REACTION_TIMES_tbl(i);
2355 l_rilv_tbl_in(i+1000).object_version_number :=1;
2356 l_rilv_tbl_in(i+1000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
2357 l_rilv_tbl_in(i+1000).CREATION_DATE := g_CREATION_DATE;
2358 l_rilv_tbl_in(i+1000).CREATED_BY := g_CREATED_BY;
2359 l_rilv_tbl_in(i+1000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2360 l_rilv_tbl_in(i+1000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2361 l_rilv_tbl_in(i+1000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2362 l_rilv_tbl_in(i+1000).object_version_number := 1;
2363 END IF; ---MON_REACTION_TIMES_tbl
2364
2365
2366 IF TUE_REACTION_TIMES_tbl(i) IS NOT NULL
2367 THEN
2368 l_tgdv_ext_tbl_in(i+2000).day_of_week := 'TUE';
2369 l_tgdv_ext_tbl_in(i+2000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
2370 l_tgdv_ext_tbl_in(i+2000).CREATION_DATE := g_CREATION_DATE;
2371 l_tgdv_ext_tbl_in(i+2000).CREATED_BY := g_CREATED_BY;
2372 l_tgdv_ext_tbl_in(i+2000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2373 l_tgdv_ext_tbl_in(i+2000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2374 l_tgdv_ext_tbl_in(i+2000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2375 l_tgdv_ext_tbl_in(i+2000).object_version_number := 1;
2376 l_tgdv_ext_tbl_in(i+2000).SFWT_FLAG := 'N' ;
2377 l_tgdv_ext_tbl_in(i+2000).TZE_ID := NULL ;
2378 l_tgdv_ext_tbl_in(i+2000).TVE_ID_LIMITED := NULL ;
2379 l_tgdv_ext_tbl_in(i+2000).DESCRIPTION := NULL ;
2380 l_tgdv_ext_tbl_in(i+2000).SHORT_DESCRIPTION := NULL ;
2381 l_tgdv_ext_tbl_in(i+2000).COMMENTS := NULL ;
2382 l_tgdv_ext_tbl_in(i+2000).MONTH := NULL ;
2383 l_tgdv_ext_tbl_in(i+2000).DAY := NULL ;
2384 l_tgdv_ext_tbl_in(i+2000).HOUR := NULL ;
2385 l_tgdv_ext_tbl_in(i+2000).MINUTE := NULL ;
2386 l_tgdv_ext_tbl_in(i+2000).SECOND := NULL ;
2387 l_tgdv_ext_tbl_in(i+2000).NTH := NULL ;
2388 l_tgdv_ext_tbl_in(i+2000).ATTRIBUTE_CATEGORY := NULL ;
2389 l_tgdv_ext_tbl_in(i+2000).ATTRIBUTE1 := NULL ;
2390 l_tgdv_ext_tbl_in(i+2000).ATTRIBUTE2 := NULL ;
2391 l_tgdv_ext_tbl_in(i+2000).ATTRIBUTE3 := NULL ;
2392 l_tgdv_ext_tbl_in(i+2000).ATTRIBUTE4 := NULL ;
2393 l_tgdv_ext_tbl_in(i+2000).ATTRIBUTE5 := NULL ;
2394 l_tgdv_ext_tbl_in(i+2000).ATTRIBUTE6 := NULL ;
2395 l_tgdv_ext_tbl_in(i+2000).ATTRIBUTE7 := NULL ;
2396 l_tgdv_ext_tbl_in(i+2000).ATTRIBUTE8 := NULL ;
2397 l_tgdv_ext_tbl_in(i+2000).ATTRIBUTE9 := NULL ;
2398 l_tgdv_ext_tbl_in(i+2000).ATTRIBUTE10 := NULL ;
2399 l_tgdv_ext_tbl_in(i+2000).ATTRIBUTE11 := NULL ;
2400 l_tgdv_ext_tbl_in(i+2000).ATTRIBUTE12 := NULL ;
2401 l_tgdv_ext_tbl_in(i+2000).ATTRIBUTE13 := NULL ;
2402 l_tgdv_ext_tbl_in(i+2000).ATTRIBUTE14 := NULL ;
2403 l_tgdv_ext_tbl_in(i+2000).ATTRIBUTE15 := NULL ;
2404
2405 l_rilv_tbl_in(i+2000).tve_id :=l_tgdv_ext_tbl_in(i+2000).id;
2406 l_rilv_tbl_in(i+2000).rul_id :=l_rulv_tbl_in(i+4000).id;
2407 l_rilv_tbl_in(i+2000).uom_code :='HR';
2408 l_rilv_tbl_in(i+2000).duration :=TUE_REACTION_TIMES_tbl(i);
2409 l_rilv_tbl_in(i+2000).object_version_number :=1;
2410 l_rilv_tbl_in(i+2000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
2411 l_rilv_tbl_in(i+2000).CREATION_DATE := g_CREATION_DATE;
2412 l_rilv_tbl_in(i+2000).CREATED_BY := g_CREATED_BY;
2413 l_rilv_tbl_in(i+2000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2414 l_rilv_tbl_in(i+2000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2415 l_rilv_tbl_in(i+2000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2416 l_rilv_tbl_in(i+2000).object_version_number := 1;
2417 END IF; ---TUE_REACTION_TIMES_tbl
2418 IF WED_REACTION_TIMES_tbl(i) IS NOT NULL
2419 THEN
2420 l_tgdv_ext_tbl_in(i+3000).id :=okc_p_util.raw_to_number(sys_guid());
2421 l_tgdv_ext_tbl_in(i+3000).day_of_week := 'WED';
2422 l_tgdv_ext_tbl_in(i+3000).dnz_chr_id :=l_clev_tbl_in(i).dnz_chr_Id;
2423 l_tgdv_ext_tbl_in(i+3000).CREATION_DATE := g_CREATION_DATE;
2424 l_tgdv_ext_tbl_in(i+3000).CREATED_BY := g_CREATED_BY;
2425 l_tgdv_ext_tbl_in(i+3000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2426 l_tgdv_ext_tbl_in(i+3000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2427 l_tgdv_ext_tbl_in(i+3000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2428 l_tgdv_ext_tbl_in(i+3000).Object_Version_Number := 1;
2429 l_tgdv_ext_tbl_in(i+3000).SFWT_FLAG := 'N' ;
2430 l_tgdv_ext_tbl_in(i+3000).TZE_ID := NULL ;
2431 l_tgdv_ext_tbl_in(i+3000).TVE_ID_LIMITED := NULL ;
2432 l_tgdv_ext_tbl_in(i+3000).DESCRIPTION := NULL ;
2433 l_tgdv_ext_tbl_in(i+3000).SHORT_DESCRIPTION := NULL ;
2434 l_tgdv_ext_tbl_in(i+3000).COMMENTS := NULL ;
2435 l_tgdv_ext_tbl_in(i+3000).MONTH := NULL ;
2436 l_tgdv_ext_tbl_in(i+3000).DAY := NULL ;
2437 l_tgdv_ext_tbl_in(i+3000).HOUR := NULL ;
2438 l_tgdv_ext_tbl_in(i+3000).MINUTE := NULL ;
2439 l_tgdv_ext_tbl_in(i+3000).SECOND := NULL ;
2440 l_tgdv_ext_tbl_in(i+3000).NTH := NULL ;
2441 l_tgdv_ext_tbl_in(i+3000).ATTRIBUTE_CATEGORY := NULL ;
2442 l_tgdv_ext_tbl_in(i+3000).ATTRIBUTE1 := NULL ;
2443 l_tgdv_ext_tbl_in(i+3000).ATTRIBUTE2 := NULL ;
2444 l_tgdv_ext_tbl_in(i+3000).ATTRIBUTE3 := NULL ;
2445 l_tgdv_ext_tbl_in(i+3000).ATTRIBUTE4 := NULL ;
2446 l_tgdv_ext_tbl_in(i+3000).ATTRIBUTE5 := NULL ;
2447 l_tgdv_ext_tbl_in(i+3000).ATTRIBUTE6 := NULL ;
2448 l_tgdv_ext_tbl_in(i+3000).ATTRIBUTE7 := NULL ;
2449 l_tgdv_ext_tbl_in(i+3000).ATTRIBUTE8 := NULL ;
2450 l_tgdv_ext_tbl_in(i+3000).ATTRIBUTE9 := NULL ;
2451 l_tgdv_ext_tbl_in(i+3000).ATTRIBUTE10 := NULL ;
2452 l_tgdv_ext_tbl_in(i+3000).ATTRIBUTE11 := NULL ;
2453 l_tgdv_ext_tbl_in(i+3000).ATTRIBUTE12 := NULL ;
2454 l_tgdv_ext_tbl_in(i+3000).ATTRIBUTE13 := NULL ;
2455 l_tgdv_ext_tbl_in(i+3000).ATTRIBUTE14 := NULL ;
2456 l_tgdv_ext_tbl_in(i+3000).ATTRIBUTE15 := NULL ;
2457
2458
2459 l_rilv_tbl_in(i+3000).tve_id :=l_tgdv_ext_tbl_in(i+3000).id;
2460 l_rilv_tbl_in(i+3000).rul_id :=l_rulv_tbl_in(i+4000).id;
2461 l_rilv_tbl_in(i+3000).uom_code :='HR';
2462 l_rilv_tbl_in(i+3000).duration :=WED_REACTION_TIMES_tbl(i);
2463 l_rilv_tbl_in(i+3000).object_version_number :=1;
2464 l_rilv_tbl_in(i+3000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
2465 l_rilv_tbl_in(i+3000).CREATION_DATE := g_CREATION_DATE;
2466 l_rilv_tbl_in(i+3000).CREATED_BY := g_CREATED_BY;
2467 l_rilv_tbl_in(i+3000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2468 l_rilv_tbl_in(i+3000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2469 l_rilv_tbl_in(i+3000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2470 l_rilv_tbl_in(i+3000).object_version_number := 1;
2471 END IF; ---WED_REACTION_TIMES_tbl
2472
2473 IF THU_REACTION_TIMES_tbl(i) IS NOT NULL
2474 THEN
2475 l_tgdv_ext_tbl_in(i+4000).id :=okc_p_util.raw_to_number(sys_guid());
2476 l_tgdv_ext_tbl_in(i+4000).day_of_week := 'THU';
2477 l_tgdv_ext_tbl_in(i+4000).dnz_chr_id :=l_clev_tbl_in(i).dnz_chr_Id;
2478 l_tgdv_ext_tbl_in(i+4000).CREATION_DATE := g_CREATION_DATE;
2479 l_tgdv_ext_tbl_in(i+4000).CREATED_BY := g_CREATED_BY;
2480 l_tgdv_ext_tbl_in(i+4000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2481 l_tgdv_ext_tbl_in(i+4000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2482 l_tgdv_ext_tbl_in(i+4000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2483 l_tgdv_ext_tbl_in(i+4000).Object_Version_Number := 1;
2484 l_tgdv_ext_tbl_in(i+4000).SFWT_FLAG := 'N' ;
2485 l_tgdv_ext_tbl_in(i+4000).TZE_ID := NULL ;
2486 l_tgdv_ext_tbl_in(i+4000).TVE_ID_LIMITED := NULL ;
2487 l_tgdv_ext_tbl_in(i+4000).DESCRIPTION := NULL ;
2488 l_tgdv_ext_tbl_in(i+4000).SHORT_DESCRIPTION := NULL ;
2489 l_tgdv_ext_tbl_in(i+4000).COMMENTS := NULL ;
2490 l_tgdv_ext_tbl_in(i+4000).MONTH := NULL ;
2491 l_tgdv_ext_tbl_in(i+4000).DAY := NULL ;
2492 l_tgdv_ext_tbl_in(i+4000).HOUR := NULL ;
2493 l_tgdv_ext_tbl_in(i+4000).MINUTE := NULL ;
2494 l_tgdv_ext_tbl_in(i+4000).SECOND := NULL ;
2495 l_tgdv_ext_tbl_in(i+4000).NTH := NULL ;
2496 l_tgdv_ext_tbl_in(i+4000).ATTRIBUTE_CATEGORY := NULL ;
2497 l_tgdv_ext_tbl_in(i+4000).ATTRIBUTE1 := NULL ;
2498 l_tgdv_ext_tbl_in(i+4000).ATTRIBUTE2 := NULL ;
2499 l_tgdv_ext_tbl_in(i+4000).ATTRIBUTE3 := NULL ;
2500 l_tgdv_ext_tbl_in(i+4000).ATTRIBUTE4 := NULL ;
2501 l_tgdv_ext_tbl_in(i+4000).ATTRIBUTE5 := NULL ;
2502 l_tgdv_ext_tbl_in(i+4000).ATTRIBUTE6 := NULL ;
2503 l_tgdv_ext_tbl_in(i+4000).ATTRIBUTE7 := NULL ;
2504 l_tgdv_ext_tbl_in(i+4000).ATTRIBUTE8 := NULL ;
2505 l_tgdv_ext_tbl_in(i+4000).ATTRIBUTE9 := NULL ;
2506 l_tgdv_ext_tbl_in(i+4000).ATTRIBUTE10 := NULL ;
2507 l_tgdv_ext_tbl_in(i+4000).ATTRIBUTE11 := NULL ;
2508 l_tgdv_ext_tbl_in(i+4000).ATTRIBUTE12 := NULL ;
2509 l_tgdv_ext_tbl_in(i+4000).ATTRIBUTE13 := NULL ;
2510 l_tgdv_ext_tbl_in(i+4000).ATTRIBUTE14 := NULL ;
2511 l_tgdv_ext_tbl_in(i+4000).ATTRIBUTE15 := NULL ;
2512
2513
2514 l_rilv_tbl_in(i+4000).tve_id :=l_tgdv_ext_tbl_in(i+4000).id;
2515 l_rilv_tbl_in(i+4000).rul_id :=l_rulv_tbl_in(i+4000).id;
2516 l_rilv_tbl_in(i+4000).uom_code :='HR';
2517 l_rilv_tbl_in(i+4000).duration :=THU_REACTION_TIMES_tbl(i);
2518 l_rilv_tbl_in(i+4000).object_version_number :=1;
2519 l_rilv_tbl_in(i+4000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
2520 l_rilv_tbl_in(i+4000).CREATION_DATE := g_CREATION_DATE;
2521 l_rilv_tbl_in(i+4000).CREATED_BY := g_CREATED_BY;
2522 l_rilv_tbl_in(i+4000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2523 l_rilv_tbl_in(i+4000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2524 l_rilv_tbl_in(i+4000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2525 l_rilv_tbl_in(i+4000).object_version_number := 1;
2526 END IF; ---THU_REACTION_TIMES_tbl
2527
2528 IF FRI_REACTION_TIMES_tbl(i) IS NOT NULL
2529 THEN
2530 l_tgdv_ext_tbl_in(i+5000).id :=okc_p_util.raw_to_number(sys_guid());
2531 l_tgdv_ext_tbl_in(i+5000).day_of_week := 'FRI';
2532 l_tgdv_ext_tbl_in(i+5000).dnz_chr_id :=l_clev_tbl_in(i).dnz_chr_Id;
2533 l_tgdv_ext_tbl_in(i+5000).CREATION_DATE := g_CREATION_DATE;
2534 l_tgdv_ext_tbl_in(i+5000).CREATED_BY := g_CREATED_BY;
2535 l_tgdv_ext_tbl_in(i+5000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2536 l_tgdv_ext_tbl_in(i+5000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2537 l_tgdv_ext_tbl_in(i+5000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2538 l_tgdv_ext_tbl_in(i+5000).Object_Version_Number := 1;
2539 l_tgdv_ext_tbl_in(i+5000).SFWT_FLAG := 'N' ;
2540 l_tgdv_ext_tbl_in(i+5000).TZE_ID := NULL ;
2541 l_tgdv_ext_tbl_in(i+5000).TVE_ID_LIMITED := NULL ;
2542 l_tgdv_ext_tbl_in(i+5000).DESCRIPTION := NULL ;
2543 l_tgdv_ext_tbl_in(i+5000).SHORT_DESCRIPTION := NULL ;
2544 l_tgdv_ext_tbl_in(i+5000).COMMENTS := NULL ;
2545 l_tgdv_ext_tbl_in(i+5000).MONTH := NULL ;
2546 l_tgdv_ext_tbl_in(i+5000).DAY := NULL ;
2547 l_tgdv_ext_tbl_in(i+5000).HOUR := NULL ;
2548 l_tgdv_ext_tbl_in(i+5000).MINUTE := NULL ;
2549 l_tgdv_ext_tbl_in(i+5000).SECOND := NULL ;
2550 l_tgdv_ext_tbl_in(i+5000).NTH := NULL ;
2551 l_tgdv_ext_tbl_in(i+5000).ATTRIBUTE_CATEGORY := NULL ;
2552 l_tgdv_ext_tbl_in(i+5000).ATTRIBUTE1 := NULL ;
2553 l_tgdv_ext_tbl_in(i+5000).ATTRIBUTE2 := NULL ;
2554 l_tgdv_ext_tbl_in(i+5000).ATTRIBUTE3 := NULL ;
2555 l_tgdv_ext_tbl_in(i+5000).ATTRIBUTE4 := NULL ;
2556 l_tgdv_ext_tbl_in(i+5000).ATTRIBUTE5 := NULL ;
2557 l_tgdv_ext_tbl_in(i+5000).ATTRIBUTE6 := NULL ;
2558 l_tgdv_ext_tbl_in(i+5000).ATTRIBUTE7 := NULL ;
2559 l_tgdv_ext_tbl_in(i+5000).ATTRIBUTE8 := NULL ;
2560 l_tgdv_ext_tbl_in(i+5000).ATTRIBUTE9 := NULL ;
2561 l_tgdv_ext_tbl_in(i+5000).ATTRIBUTE10 := NULL ;
2562 l_tgdv_ext_tbl_in(i+5000).ATTRIBUTE11 := NULL ;
2563 l_tgdv_ext_tbl_in(i+5000).ATTRIBUTE12 := NULL ;
2564 l_tgdv_ext_tbl_in(i+5000).ATTRIBUTE13 := NULL ;
2565 l_tgdv_ext_tbl_in(i+5000).ATTRIBUTE14 := NULL ;
2566 l_tgdv_ext_tbl_in(i+5000).ATTRIBUTE15 := NULL ;
2567
2568
2569 l_rilv_tbl_in(i+5000).tve_id :=l_tgdv_ext_tbl_in(i+5000).id;
2570 l_rilv_tbl_in(i+5000).rul_id :=l_rulv_tbl_in(i+4000).id;
2571 l_rilv_tbl_in(i+5000).uom_code :='HR';
2572 l_rilv_tbl_in(i+5000).duration :=FRI_REACTION_TIMES_tbl(i);
2573 l_rilv_tbl_in(i+5000).object_version_number :=1;
2574 l_rilv_tbl_in(i+5000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
2575 l_rilv_tbl_in(i+5000).CREATION_DATE := g_CREATION_DATE;
2576 l_rilv_tbl_in(i+5000).CREATED_BY := g_CREATED_BY;
2577 l_rilv_tbl_in(i+5000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2578 l_rilv_tbl_in(i+5000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2579 l_rilv_tbl_in(i+5000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2580 l_rilv_tbl_in(i+5000).object_version_number := 1;
2581 END IF; ---FRI_REACTION_TIMES_tbl
2582
2583 IF SAT_REACTION_TIMES_tbl(i) IS NOT NULL
2584 THEN
2585 l_tgdv_ext_tbl_in(i+6000).id :=okc_p_util.raw_to_number(sys_guid());
2586 l_tgdv_ext_tbl_in(i+6000).day_of_week := 'SAT';
2587 l_tgdv_ext_tbl_in(i+6000).dnz_chr_id :=l_clev_tbl_in(i).dnz_chr_Id;
2588 l_tgdv_ext_tbl_in(i+6000).CREATION_DATE := g_CREATION_DATE;
2589 l_tgdv_ext_tbl_in(i+6000).CREATED_BY := g_CREATED_BY;
2590 l_tgdv_ext_tbl_in(i+6000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2591 l_tgdv_ext_tbl_in(i+6000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2592 l_tgdv_ext_tbl_in(i+6000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2593 l_tgdv_ext_tbl_in(i+6000).Object_Version_Number := 1;
2594 l_tgdv_ext_tbl_in(i+6000).SFWT_FLAG := 'N' ;
2595 l_tgdv_ext_tbl_in(i+6000).TZE_ID := NULL ;
2596 l_tgdv_ext_tbl_in(i+6000).TVE_ID_LIMITED := NULL ;
2597 l_tgdv_ext_tbl_in(i+6000).DESCRIPTION := NULL ;
2598 l_tgdv_ext_tbl_in(i+6000).SHORT_DESCRIPTION := NULL ;
2599 l_tgdv_ext_tbl_in(i+6000).COMMENTS := NULL ;
2600 l_tgdv_ext_tbl_in(i+6000).MONTH := NULL ;
2601 l_tgdv_ext_tbl_in(i+6000).DAY := NULL ;
2602 l_tgdv_ext_tbl_in(i+6000).HOUR := NULL ;
2603 l_tgdv_ext_tbl_in(i+6000).MINUTE := NULL ;
2604 l_tgdv_ext_tbl_in(i+6000).SECOND := NULL ;
2605 l_tgdv_ext_tbl_in(i+6000).NTH := NULL ;
2606 l_tgdv_ext_tbl_in(i+6000).ATTRIBUTE_CATEGORY := NULL ;
2607 l_tgdv_ext_tbl_in(i+6000).ATTRIBUTE1 := NULL ;
2608 l_tgdv_ext_tbl_in(i+6000).ATTRIBUTE2 := NULL ;
2609 l_tgdv_ext_tbl_in(i+6000).ATTRIBUTE3 := NULL ;
2610 l_tgdv_ext_tbl_in(i+6000).ATTRIBUTE4 := NULL ;
2611 l_tgdv_ext_tbl_in(i+6000).ATTRIBUTE5 := NULL ;
2612 l_tgdv_ext_tbl_in(i+6000).ATTRIBUTE6 := NULL ;
2613 l_tgdv_ext_tbl_in(i+6000).ATTRIBUTE7 := NULL ;
2614 l_tgdv_ext_tbl_in(i+6000).ATTRIBUTE8 := NULL ;
2615 l_tgdv_ext_tbl_in(i+6000).ATTRIBUTE9 := NULL ;
2616 l_tgdv_ext_tbl_in(i+6000).ATTRIBUTE10 := NULL ;
2617 l_tgdv_ext_tbl_in(i+6000).ATTRIBUTE11 := NULL ;
2618 l_tgdv_ext_tbl_in(i+6000).ATTRIBUTE12 := NULL ;
2619 l_tgdv_ext_tbl_in(i+6000).ATTRIBUTE13 := NULL ;
2620 l_tgdv_ext_tbl_in(i+6000).ATTRIBUTE14 := NULL ;
2621 l_tgdv_ext_tbl_in(i+6000).ATTRIBUTE15 := NULL ;
2622
2623
2624 l_rilv_tbl_in(i+6000).tve_id :=l_tgdv_ext_tbl_in(i+6000).id;
2625 l_rilv_tbl_in(i+6000).rul_id :=l_rulv_tbl_in(i+4000).id;
2626 l_rilv_tbl_in(i+6000).uom_code :='HR';
2627 l_rilv_tbl_in(i+6000).duration :=SAT_REACTION_TIMES_tbl(i);
2628 l_rilv_tbl_in(i+6000).object_version_number :=1;
2629 l_rilv_tbl_in(i+6000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
2630 l_rilv_tbl_in(i+6000).CREATION_DATE := g_CREATION_DATE;
2631 l_rilv_tbl_in(i+6000).CREATED_BY := g_CREATED_BY;
2632 l_rilv_tbl_in(i+6000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2633 l_rilv_tbl_in(i+6000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2634 l_rilv_tbl_in(i+6000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2635 l_rilv_tbl_in(i+6000).object_version_number := 1;
2636 END IF; ---SAT_REACTION_TIMES_tbl
2637 IF SUN_RESOLUTION_TIMES_tbl(i) IS NOT NULL
2638 THEN
2639 l_tgdv_rcn_tbl_in(i).id :=okc_p_util.raw_to_number(sys_guid());
2640 l_tgdv_rcn_tbl_in(i).day_of_week := 'SUN';
2641 l_tgdv_rcn_tbl_in(i).dnz_chr_id :=l_clev_tbl_in(i).dnz_chr_Id;
2642 l_tgdv_rcn_tbl_in(i).CREATION_DATE := g_CREATION_DATE;
2643 l_tgdv_rcn_tbl_in(i).CREATED_BY := g_CREATED_BY;
2644 l_tgdv_rcn_tbl_in(i).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2645 l_tgdv_rcn_tbl_in(i).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2646 l_tgdv_rcn_tbl_in(i).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2647 l_tgdv_rcn_tbl_in(i).Object_Version_Number := 1;
2648 l_tgdv_rcn_tbl_in(i).SFWT_FLAG := 'N' ;
2649 l_tgdv_rcn_tbl_in(i).TZE_ID := NULL ;
2650 l_tgdv_rcn_tbl_in(i).TVE_ID_LIMITED := NULL ;
2651 l_tgdv_rcn_tbl_in(i).DESCRIPTION := NULL ;
2652 l_tgdv_rcn_tbl_in(i).SHORT_DESCRIPTION := NULL ;
2653 l_tgdv_rcn_tbl_in(i).COMMENTS := NULL ;
2654 l_tgdv_rcn_tbl_in(i).MONTH := NULL ;
2655 l_tgdv_rcn_tbl_in(i).DAY := NULL ;
2656 l_tgdv_rcn_tbl_in(i).HOUR := NULL ;
2657 l_tgdv_rcn_tbl_in(i).MINUTE := NULL ;
2658 l_tgdv_rcn_tbl_in(i).SECOND := NULL ;
2659 l_tgdv_rcn_tbl_in(i).NTH := NULL ;
2660 l_tgdv_rcn_tbl_in(i).ATTRIBUTE_CATEGORY := NULL ;
2661 l_tgdv_rcn_tbl_in(i).ATTRIBUTE1 := NULL ;
2662 l_tgdv_rcn_tbl_in(i).ATTRIBUTE2 := NULL ;
2663 l_tgdv_rcn_tbl_in(i).ATTRIBUTE3 := NULL ;
2664 l_tgdv_rcn_tbl_in(i).ATTRIBUTE4 := NULL ;
2665 l_tgdv_rcn_tbl_in(i).ATTRIBUTE5 := NULL ;
2666 l_tgdv_rcn_tbl_in(i).ATTRIBUTE6 := NULL ;
2667 l_tgdv_rcn_tbl_in(i).ATTRIBUTE7 := NULL ;
2668 l_tgdv_rcn_tbl_in(i).ATTRIBUTE8 := NULL ;
2669 l_tgdv_rcn_tbl_in(i).ATTRIBUTE9 := NULL ;
2670 l_tgdv_rcn_tbl_in(i).ATTRIBUTE10 := NULL ;
2671 l_tgdv_rcn_tbl_in(i).ATTRIBUTE11 := NULL ;
2672 l_tgdv_rcn_tbl_in(i).ATTRIBUTE12 := NULL ;
2673 l_tgdv_rcn_tbl_in(i).ATTRIBUTE13 := NULL ;
2674 l_tgdv_rcn_tbl_in(i).ATTRIBUTE14 := NULL ;
2675 l_tgdv_rcn_tbl_in(i).ATTRIBUTE15 := NULL ;
2676
2677
2678 l_rilt_tbl_in(i).tve_id :=l_tgdv_rcn_tbl_in(i).id;
2679 l_rilt_tbl_in(i).rul_id :=l_rulv_tbl_in(i+ 5000).id;
2680 l_rilt_tbl_in(i).uom_code :='HR';
2681 l_rilt_tbl_in(i).duration :=SUN_RESOLUTION_TIMES_tbl(i);
2682 l_rilt_tbl_in(i).object_version_number :=1;
2683 l_rilt_tbl_in(i).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
2684 l_rilt_tbl_in(i).CREATION_DATE := g_CREATION_DATE;
2685 l_rilt_tbl_in(i).CREATED_BY := g_CREATED_BY;
2686 l_rilt_tbl_in(i).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2687 l_rilt_tbl_in(i).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2688 l_rilt_tbl_in(i).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2689 l_rilt_tbl_in(i).object_version_number := 1;
2690
2691 END IF; ---SUN_RESOLUTION_TIMES_tbl
2692
2693 IF MON_RESOLUTION_TIMES_tbl(i) IS NOT NULL
2694 THEN
2695 l_tgdv_rcn_tbl_in(i+1000).id :=okc_p_util.raw_to_number(sys_guid());
2696 l_tgdv_rcn_tbl_in(i+1000).day_of_week := 'MON';
2697 l_tgdv_rcn_tbl_in(i+1000).dnz_chr_id :=l_clev_tbl_in(i).dnz_chr_Id;
2698 l_tgdv_rcn_tbl_in(i+1000).CREATION_DATE := g_CREATION_DATE;
2699 l_tgdv_rcn_tbl_in(i+1000).CREATED_BY := g_CREATED_BY;
2700 l_tgdv_rcn_tbl_in(i+1000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2701 l_tgdv_rcn_tbl_in(i+1000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2702 l_tgdv_rcn_tbl_in(i+1000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2703 l_tgdv_rcn_tbl_in(i+1000).Object_Version_Number := 1;
2704 l_tgdv_rcn_tbl_in(i+1000).SFWT_FLAG := 'N' ;
2705 l_tgdv_rcn_tbl_in(i+1000).TZE_ID := NULL ;
2706 l_tgdv_rcn_tbl_in(i+1000).TVE_ID_LIMITED := NULL ;
2707 l_tgdv_rcn_tbl_in(i+1000).DESCRIPTION := NULL ;
2708 l_tgdv_rcn_tbl_in(i+1000).SHORT_DESCRIPTION := NULL ;
2709 l_tgdv_rcn_tbl_in(i+1000).COMMENTS := NULL ;
2710 l_tgdv_rcn_tbl_in(i+1000).MONTH := NULL ;
2711 l_tgdv_rcn_tbl_in(i+1000).DAY := NULL ;
2712 l_tgdv_rcn_tbl_in(i+1000).HOUR := NULL ;
2713 l_tgdv_rcn_tbl_in(i+1000).MINUTE := NULL ;
2714 l_tgdv_rcn_tbl_in(i+1000).SECOND := NULL ;
2715 l_tgdv_rcn_tbl_in(i+1000).NTH := NULL ;
2716 l_tgdv_rcn_tbl_in(i+1000).ATTRIBUTE_CATEGORY := NULL ;
2717 l_tgdv_rcn_tbl_in(i+1000).ATTRIBUTE1 := NULL ;
2718 l_tgdv_rcn_tbl_in(i+1000).ATTRIBUTE2 := NULL ;
2719 l_tgdv_rcn_tbl_in(i+1000).ATTRIBUTE3 := NULL ;
2720 l_tgdv_rcn_tbl_in(i+1000).ATTRIBUTE4 := NULL ;
2721 l_tgdv_rcn_tbl_in(i+1000).ATTRIBUTE5 := NULL ;
2722 l_tgdv_rcn_tbl_in(i+1000).ATTRIBUTE6 := NULL ;
2723 l_tgdv_rcn_tbl_in(i+1000).ATTRIBUTE7 := NULL ;
2724 l_tgdv_rcn_tbl_in(i+1000).ATTRIBUTE8 := NULL ;
2725 l_tgdv_rcn_tbl_in(i+1000).ATTRIBUTE9 := NULL ;
2726 l_tgdv_rcn_tbl_in(i+1000).ATTRIBUTE10 := NULL ;
2727 l_tgdv_rcn_tbl_in(i+1000).ATTRIBUTE11 := NULL ;
2728 l_tgdv_rcn_tbl_in(i+1000).ATTRIBUTE12 := NULL ;
2729 l_tgdv_rcn_tbl_in(i+1000).ATTRIBUTE13 := NULL ;
2730 l_tgdv_rcn_tbl_in(i+1000).ATTRIBUTE14 := NULL ;
2731 l_tgdv_rcn_tbl_in(i+1000).ATTRIBUTE15 := NULL ;
2732
2733
2734 l_rilt_tbl_in(i+1000).tve_id :=l_tgdv_rcn_tbl_in(i+1000).id;
2735 l_rilt_tbl_in(i+1000).rul_id :=l_rulv_tbl_in(i+5000).id;
2736 l_rilt_tbl_in(i+1000).uom_code :='HR';
2737 l_rilt_tbl_in(i+1000).duration :=MON_RESOLUTION_TIMES_tbl(i);
2738 l_rilt_tbl_in(i+1000).object_version_number :=1;
2739 l_rilt_tbl_in(i+1000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
2740 l_rilt_tbl_in(i+1000).CREATION_DATE := g_CREATION_DATE;
2741 l_rilt_tbl_in(i+1000).CREATED_BY := g_CREATED_BY;
2742 l_rilt_tbl_in(i+1000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2743 l_rilt_tbl_in(i+1000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2744 l_rilt_tbl_in(i+1000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2745 l_rilt_tbl_in(i+1000).object_version_number := 1;
2746 END IF; ---MON_RESOLUTION_TIMES_tbl
2747
2748
2749 IF TUE_RESOLUTION_TIMES_tbl(i) IS NOT NULL
2750 THEN
2751 l_tgdv_rcn_tbl_in(i+2000).day_of_week := 'TUE';
2752 l_tgdv_rcn_tbl_in(i+2000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
2753 l_tgdv_rcn_tbl_in(i+2000).CREATION_DATE := g_CREATION_DATE;
2754 l_tgdv_rcn_tbl_in(i+2000).CREATED_BY := g_CREATED_BY;
2755 l_tgdv_rcn_tbl_in(i+2000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2756 l_tgdv_rcn_tbl_in(i+2000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2757 l_tgdv_rcn_tbl_in(i+2000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2758 l_tgdv_rcn_tbl_in(i+2000).object_version_number := 1;
2759 l_tgdv_rcn_tbl_in(i+2000).SFWT_FLAG := 'N' ;
2760 l_tgdv_rcn_tbl_in(i+2000).TZE_ID := NULL ;
2761 l_tgdv_rcn_tbl_in(i+2000).TVE_ID_LIMITED := NULL ;
2762 l_tgdv_rcn_tbl_in(i+2000).DESCRIPTION := NULL ;
2763 l_tgdv_rcn_tbl_in(i+2000).SHORT_DESCRIPTION := NULL ;
2764 l_tgdv_rcn_tbl_in(i+2000).COMMENTS := NULL ;
2765 l_tgdv_rcn_tbl_in(i+2000).MONTH := NULL ;
2766 l_tgdv_rcn_tbl_in(i+2000).DAY := NULL ;
2767 l_tgdv_rcn_tbl_in(i+2000).HOUR := NULL ;
2768 l_tgdv_rcn_tbl_in(i+2000).MINUTE := NULL ;
2769 l_tgdv_rcn_tbl_in(i+2000).SECOND := NULL ;
2770 l_tgdv_rcn_tbl_in(i+2000).NTH := NULL ;
2771 l_tgdv_rcn_tbl_in(i+2000).ATTRIBUTE_CATEGORY := NULL ;
2772 l_tgdv_rcn_tbl_in(i+2000).ATTRIBUTE1 := NULL ;
2773 l_tgdv_rcn_tbl_in(i+2000).ATTRIBUTE2 := NULL ;
2774 l_tgdv_rcn_tbl_in(i+2000).ATTRIBUTE3 := NULL ;
2775 l_tgdv_rcn_tbl_in(i+2000).ATTRIBUTE4 := NULL ;
2776 l_tgdv_rcn_tbl_in(i+2000).ATTRIBUTE5 := NULL ;
2777 l_tgdv_rcn_tbl_in(i+2000).ATTRIBUTE6 := NULL ;
2778 l_tgdv_rcn_tbl_in(i+2000).ATTRIBUTE7 := NULL ;
2779 l_tgdv_rcn_tbl_in(i+2000).ATTRIBUTE8 := NULL ;
2780 l_tgdv_rcn_tbl_in(i+2000).ATTRIBUTE9 := NULL ;
2781 l_tgdv_rcn_tbl_in(i+2000).ATTRIBUTE10 := NULL ;
2782 l_tgdv_rcn_tbl_in(i+2000).ATTRIBUTE11 := NULL ;
2783 l_tgdv_rcn_tbl_in(i+2000).ATTRIBUTE12 := NULL ;
2784 l_tgdv_rcn_tbl_in(i+2000).ATTRIBUTE13 := NULL ;
2785 l_tgdv_rcn_tbl_in(i+2000).ATTRIBUTE14 := NULL ;
2786 l_tgdv_rcn_tbl_in(i+2000).ATTRIBUTE15 := NULL ;
2787
2788 l_rilt_tbl_in(i+2000).tve_id :=l_tgdv_rcn_tbl_in(i+2000).id;
2789 l_rilt_tbl_in(i+2000).rul_id :=l_rulv_tbl_in(i+5000).id;
2790 l_rilt_tbl_in(i+2000).uom_code :='HR';
2791 l_rilt_tbl_in(i+2000).duration :=TUE_RESOLUTION_TIMES_tbl(i);
2792 l_rilt_tbl_in(i+2000).object_version_number :=1;
2793 l_rilt_tbl_in(i+2000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
2794 l_rilt_tbl_in(i+2000).CREATION_DATE := g_CREATION_DATE;
2795 l_rilt_tbl_in(i+2000).CREATED_BY := g_CREATED_BY;
2796 l_rilt_tbl_in(i+2000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2797 l_rilt_tbl_in(i+2000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2798 l_rilt_tbl_in(i+2000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2799 l_rilt_tbl_in(i+2000).object_version_number := 1;
2800 END IF; ---TUE_RESOLUTION_TIMES_tbl
2801 IF WED_RESOLUTION_TIMES_tbl(i) IS NOT NULL
2802 THEN
2803 l_tgdv_rcn_tbl_in(i+3000).id :=okc_p_util.raw_to_number(sys_guid());
2804 l_tgdv_rcn_tbl_in(i+3000).day_of_week := 'WED';
2805 l_tgdv_rcn_tbl_in(i+3000).dnz_chr_id :=l_clev_tbl_in(i).dnz_chr_Id;
2806 l_tgdv_rcn_tbl_in(i+3000).CREATION_DATE := g_CREATION_DATE;
2807 l_tgdv_rcn_tbl_in(i+3000).CREATED_BY := g_CREATED_BY;
2808 l_tgdv_rcn_tbl_in(i+3000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2809 l_tgdv_rcn_tbl_in(i+3000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2810 l_tgdv_rcn_tbl_in(i+3000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2811 l_tgdv_rcn_tbl_in(i+3000).Object_Version_Number := 1;
2812 l_tgdv_rcn_tbl_in(i+3000).SFWT_FLAG := 'N' ;
2813 l_tgdv_rcn_tbl_in(i+3000).TZE_ID := NULL ;
2814 l_tgdv_rcn_tbl_in(i+3000).TVE_ID_LIMITED := NULL ;
2815 l_tgdv_rcn_tbl_in(i+3000).DESCRIPTION := NULL ;
2816 l_tgdv_rcn_tbl_in(i+3000).SHORT_DESCRIPTION := NULL ;
2817 l_tgdv_rcn_tbl_in(i+3000).COMMENTS := NULL ;
2818 l_tgdv_rcn_tbl_in(i+3000).MONTH := NULL ;
2819 l_tgdv_rcn_tbl_in(i+3000).DAY := NULL ;
2820 l_tgdv_rcn_tbl_in(i+3000).HOUR := NULL ;
2821 l_tgdv_rcn_tbl_in(i+3000).MINUTE := NULL ;
2822 l_tgdv_rcn_tbl_in(i+3000).SECOND := NULL ;
2823 l_tgdv_rcn_tbl_in(i+3000).NTH := NULL ;
2824 l_tgdv_rcn_tbl_in(i+3000).ATTRIBUTE_CATEGORY := NULL ;
2825 l_tgdv_rcn_tbl_in(i+3000).ATTRIBUTE1 := NULL ;
2826 l_tgdv_rcn_tbl_in(i+3000).ATTRIBUTE2 := NULL ;
2827 l_tgdv_rcn_tbl_in(i+3000).ATTRIBUTE3 := NULL ;
2828 l_tgdv_rcn_tbl_in(i+3000).ATTRIBUTE4 := NULL ;
2829 l_tgdv_rcn_tbl_in(i+3000).ATTRIBUTE5 := NULL ;
2830 l_tgdv_rcn_tbl_in(i+3000).ATTRIBUTE6 := NULL ;
2831 l_tgdv_rcn_tbl_in(i+3000).ATTRIBUTE7 := NULL ;
2832 l_tgdv_rcn_tbl_in(i+3000).ATTRIBUTE8 := NULL ;
2833 l_tgdv_rcn_tbl_in(i+3000).ATTRIBUTE9 := NULL ;
2834 l_tgdv_rcn_tbl_in(i+3000).ATTRIBUTE10 := NULL ;
2835 l_tgdv_rcn_tbl_in(i+3000).ATTRIBUTE11 := NULL ;
2836 l_tgdv_rcn_tbl_in(i+3000).ATTRIBUTE12 := NULL ;
2837 l_tgdv_rcn_tbl_in(i+3000).ATTRIBUTE13 := NULL ;
2838 l_tgdv_rcn_tbl_in(i+3000).ATTRIBUTE14 := NULL ;
2839 l_tgdv_rcn_tbl_in(i+3000).ATTRIBUTE15 := NULL ;
2840
2841
2842 l_rilt_tbl_in(i+3000).tve_id :=l_tgdv_rcn_tbl_in(i+3000).id;
2843 l_rilt_tbl_in(i+3000).rul_id :=l_rulv_tbl_in(i+5000).id;
2844 l_rilt_tbl_in(i+3000).uom_code :='HR';
2845 l_rilt_tbl_in(i+3000).duration :=WED_RESOLUTION_TIMES_tbl(i);
2846 l_rilt_tbl_in(i+3000).object_version_number :=1;
2847 l_rilt_tbl_in(i+3000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
2848 l_rilt_tbl_in(i+3000).CREATION_DATE := g_CREATION_DATE;
2849 l_rilt_tbl_in(i+3000).CREATED_BY := g_CREATED_BY;
2850 l_rilt_tbl_in(i+3000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2851 l_rilt_tbl_in(i+3000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2852 l_rilt_tbl_in(i+3000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2853 l_rilt_tbl_in(i+3000).object_version_number := 1;
2854 END IF; ---WED_RESOLUTION_TIMES_tbl
2855
2856 IF THU_RESOLUTION_TIMES_tbl(i) IS NOT NULL
2857 THEN
2858 l_tgdv_rcn_tbl_in(i+4000).id :=okc_p_util.raw_to_number(sys_guid());
2859 l_tgdv_rcn_tbl_in(i+4000).day_of_week := 'THU';
2860 l_tgdv_rcn_tbl_in(i+4000).dnz_chr_id :=l_clev_tbl_in(i).dnz_chr_Id;
2861 l_tgdv_rcn_tbl_in(i+4000).CREATION_DATE := g_CREATION_DATE;
2862 l_tgdv_rcn_tbl_in(i+4000).CREATED_BY := g_CREATED_BY;
2863 l_tgdv_rcn_tbl_in(i+4000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2864 l_tgdv_rcn_tbl_in(i+4000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2865 l_tgdv_rcn_tbl_in(i+4000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2866 l_tgdv_rcn_tbl_in(i+4000).Object_Version_Number := 1;
2867 l_tgdv_rcn_tbl_in(i+4000).SFWT_FLAG := 'N' ;
2868 l_tgdv_rcn_tbl_in(i+4000).TZE_ID := NULL ;
2869 l_tgdv_rcn_tbl_in(i+4000).TVE_ID_LIMITED := NULL ;
2870 l_tgdv_rcn_tbl_in(i+4000).DESCRIPTION := NULL ;
2871 l_tgdv_rcn_tbl_in(i+4000).SHORT_DESCRIPTION := NULL ;
2872 l_tgdv_rcn_tbl_in(i+4000).COMMENTS := NULL ;
2873 l_tgdv_rcn_tbl_in(i+4000).MONTH := NULL ;
2874 l_tgdv_rcn_tbl_in(i+4000).DAY := NULL ;
2875 l_tgdv_rcn_tbl_in(i+4000).HOUR := NULL ;
2876 l_tgdv_rcn_tbl_in(i+4000).MINUTE := NULL ;
2877 l_tgdv_rcn_tbl_in(i+4000).SECOND := NULL ;
2878 l_tgdv_rcn_tbl_in(i+4000).NTH := NULL ;
2879 l_tgdv_rcn_tbl_in(i+4000).ATTRIBUTE_CATEGORY := NULL ;
2880 l_tgdv_rcn_tbl_in(i+4000).ATTRIBUTE1 := NULL ;
2881 l_tgdv_rcn_tbl_in(i+4000).ATTRIBUTE2 := NULL ;
2882 l_tgdv_rcn_tbl_in(i+4000).ATTRIBUTE3 := NULL ;
2883 l_tgdv_rcn_tbl_in(i+4000).ATTRIBUTE4 := NULL ;
2884 l_tgdv_rcn_tbl_in(i+4000).ATTRIBUTE5 := NULL ;
2885 l_tgdv_rcn_tbl_in(i+4000).ATTRIBUTE6 := NULL ;
2886 l_tgdv_rcn_tbl_in(i+4000).ATTRIBUTE7 := NULL ;
2887 l_tgdv_rcn_tbl_in(i+4000).ATTRIBUTE8 := NULL ;
2888 l_tgdv_rcn_tbl_in(i+4000).ATTRIBUTE9 := NULL ;
2889 l_tgdv_rcn_tbl_in(i+4000).ATTRIBUTE10 := NULL ;
2890 l_tgdv_rcn_tbl_in(i+4000).ATTRIBUTE11 := NULL ;
2891 l_tgdv_rcn_tbl_in(i+4000).ATTRIBUTE12 := NULL ;
2892 l_tgdv_rcn_tbl_in(i+4000).ATTRIBUTE13 := NULL ;
2893 l_tgdv_rcn_tbl_in(i+4000).ATTRIBUTE14 := NULL ;
2894 l_tgdv_rcn_tbl_in(i+4000).ATTRIBUTE15 := NULL ;
2895
2896
2897 l_rilt_tbl_in(i+4000).tve_id :=l_tgdv_rcn_tbl_in(i+4000).id;
2898 l_rilt_tbl_in(i+4000).rul_id :=l_rulv_tbl_in(i+5000).id;
2899 l_rilt_tbl_in(i+4000).uom_code :='HR';
2900 l_rilt_tbl_in(i+4000).duration :=THU_RESOLUTION_TIMES_tbl(i);
2901 l_rilt_tbl_in(i+4000).object_version_number :=1;
2902 l_rilt_tbl_in(i+4000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
2903 l_rilt_tbl_in(i+4000).CREATION_DATE := g_CREATION_DATE;
2904 l_rilt_tbl_in(i+4000).CREATED_BY := g_CREATED_BY;
2905 l_rilt_tbl_in(i+4000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2906 l_rilt_tbl_in(i+4000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2907 l_rilt_tbl_in(i+4000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2908 l_rilt_tbl_in(i+4000).object_version_number := 1;
2909 END IF; ---THU_RESOLUTION_TIMES_tbl
2910
2911 IF FRI_RESOLUTION_TIMES_tbl(i) IS NOT NULL
2912 THEN
2913 l_tgdv_rcn_tbl_in(i+5000).id :=okc_p_util.raw_to_number(sys_guid());
2914 l_tgdv_rcn_tbl_in(i+5000).day_of_week := 'FRI';
2915 l_tgdv_rcn_tbl_in(i+5000).dnz_chr_id :=l_clev_tbl_in(i).dnz_chr_Id;
2916 l_tgdv_rcn_tbl_in(i+5000).CREATION_DATE := g_CREATION_DATE;
2917 l_tgdv_rcn_tbl_in(i+5000).CREATED_BY := g_CREATED_BY;
2918 l_tgdv_rcn_tbl_in(i+5000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2919 l_tgdv_rcn_tbl_in(i+5000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2920 l_tgdv_rcn_tbl_in(i+5000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2921 l_tgdv_rcn_tbl_in(i+5000).Object_Version_Number := 1;
2922 l_tgdv_rcn_tbl_in(i+5000).SFWT_FLAG := 'N' ;
2923 l_tgdv_rcn_tbl_in(i+5000).TZE_ID := NULL ;
2924 l_tgdv_rcn_tbl_in(i+5000).TVE_ID_LIMITED := NULL ;
2925 l_tgdv_rcn_tbl_in(i+5000).DESCRIPTION := NULL ;
2926 l_tgdv_rcn_tbl_in(i+5000).SHORT_DESCRIPTION := NULL ;
2927 l_tgdv_rcn_tbl_in(i+5000).COMMENTS := NULL ;
2928 l_tgdv_rcn_tbl_in(i+5000).MONTH := NULL ;
2929 l_tgdv_rcn_tbl_in(i+5000).DAY := NULL ;
2930 l_tgdv_rcn_tbl_in(i+5000).HOUR := NULL ;
2931 l_tgdv_rcn_tbl_in(i+5000).MINUTE := NULL ;
2932 l_tgdv_rcn_tbl_in(i+5000).SECOND := NULL ;
2933 l_tgdv_rcn_tbl_in(i+5000).NTH := NULL ;
2934 l_tgdv_rcn_tbl_in(i+5000).ATTRIBUTE_CATEGORY := NULL ;
2935 l_tgdv_rcn_tbl_in(i+5000).ATTRIBUTE1 := NULL ;
2936 l_tgdv_rcn_tbl_in(i+5000).ATTRIBUTE2 := NULL ;
2937 l_tgdv_rcn_tbl_in(i+5000).ATTRIBUTE3 := NULL ;
2938 l_tgdv_rcn_tbl_in(i+5000).ATTRIBUTE4 := NULL ;
2939 l_tgdv_rcn_tbl_in(i+5000).ATTRIBUTE5 := NULL ;
2940 l_tgdv_rcn_tbl_in(i+5000).ATTRIBUTE6 := NULL ;
2941 l_tgdv_rcn_tbl_in(i+5000).ATTRIBUTE7 := NULL ;
2942 l_tgdv_rcn_tbl_in(i+5000).ATTRIBUTE8 := NULL ;
2943 l_tgdv_rcn_tbl_in(i+5000).ATTRIBUTE9 := NULL ;
2944 l_tgdv_rcn_tbl_in(i+5000).ATTRIBUTE10 := NULL ;
2945 l_tgdv_rcn_tbl_in(i+5000).ATTRIBUTE11 := NULL ;
2946 l_tgdv_rcn_tbl_in(i+5000).ATTRIBUTE12 := NULL ;
2947 l_tgdv_rcn_tbl_in(i+5000).ATTRIBUTE13 := NULL ;
2948 l_tgdv_rcn_tbl_in(i+5000).ATTRIBUTE14 := NULL ;
2949 l_tgdv_rcn_tbl_in(i+5000).ATTRIBUTE15 := NULL ;
2950
2951
2952 l_rilt_tbl_in(i+5000).tve_id :=l_tgdv_rcn_tbl_in(i+5000).id;
2953 l_rilt_tbl_in(i+5000).rul_id :=l_rulv_tbl_in(i+5000).id;
2954 l_rilt_tbl_in(i+5000).uom_code :='HR';
2955 l_rilt_tbl_in(i+5000).duration :=FRI_RESOLUTION_TIMES_tbl(i);
2956 l_rilt_tbl_in(i+5000).object_version_number :=1;
2957 l_rilt_tbl_in(i+5000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
2958 l_rilt_tbl_in(i+5000).CREATION_DATE := g_CREATION_DATE;
2959 l_rilt_tbl_in(i+5000).CREATED_BY := g_CREATED_BY;
2960 l_rilt_tbl_in(i+5000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2961 l_rilt_tbl_in(i+5000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2962 l_rilt_tbl_in(i+5000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2963 l_rilt_tbl_in(i+5000).object_version_number := 1;
2964 END IF; ---FRI_RESOLUTION_TIMES_tbl
2965
2966 IF SAT_RESOLUTION_TIMES_tbl(i) IS NOT NULL
2967 THEN
2968 l_tgdv_rcn_tbl_in(i+6000).id :=okc_p_util.raw_to_number(sys_guid());
2969 l_tgdv_rcn_tbl_in(i+6000).day_of_week := 'SAT';
2970 l_tgdv_rcn_tbl_in(i+6000).dnz_chr_id :=l_clev_tbl_in(i).dnz_chr_Id;
2971 l_tgdv_rcn_tbl_in(i+6000).CREATION_DATE := g_CREATION_DATE;
2972 l_tgdv_rcn_tbl_in(i+6000).CREATED_BY := g_CREATED_BY;
2973 l_tgdv_rcn_tbl_in(i+6000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
2974 l_tgdv_rcn_tbl_in(i+6000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
2975 l_tgdv_rcn_tbl_in(i+6000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
2976 l_tgdv_rcn_tbl_in(i+6000).Object_Version_Number := 1;
2977 l_tgdv_rcn_tbl_in(i+6000).SFWT_FLAG := 'N' ;
2978 l_tgdv_rcn_tbl_in(i+6000).TZE_ID := NULL ;
2979 l_tgdv_rcn_tbl_in(i+6000).TVE_ID_LIMITED := NULL ;
2980 l_tgdv_rcn_tbl_in(i+6000).DESCRIPTION := NULL ;
2981 l_tgdv_rcn_tbl_in(i+6000).SHORT_DESCRIPTION := NULL ;
2982 l_tgdv_rcn_tbl_in(i+6000).COMMENTS := NULL ;
2983 l_tgdv_rcn_tbl_in(i+6000).MONTH := NULL ;
2984 l_tgdv_rcn_tbl_in(i+6000).DAY := NULL ;
2985 l_tgdv_rcn_tbl_in(i+6000).HOUR := NULL ;
2986 l_tgdv_rcn_tbl_in(i+6000).MINUTE := NULL ;
2987 l_tgdv_rcn_tbl_in(i+6000).SECOND := NULL ;
2988 l_tgdv_rcn_tbl_in(i+6000).NTH := NULL ;
2989 l_tgdv_rcn_tbl_in(i+6000).ATTRIBUTE_CATEGORY := NULL ;
2990 l_tgdv_rcn_tbl_in(i+6000).ATTRIBUTE1 := NULL ;
2991 l_tgdv_rcn_tbl_in(i+6000).ATTRIBUTE2 := NULL ;
2992 l_tgdv_rcn_tbl_in(i+6000).ATTRIBUTE3 := NULL ;
2993 l_tgdv_rcn_tbl_in(i+6000).ATTRIBUTE4 := NULL ;
2994 l_tgdv_rcn_tbl_in(i+6000).ATTRIBUTE5 := NULL ;
2995 l_tgdv_rcn_tbl_in(i+6000).ATTRIBUTE6 := NULL ;
2996 l_tgdv_rcn_tbl_in(i+6000).ATTRIBUTE7 := NULL ;
2997 l_tgdv_rcn_tbl_in(i+6000).ATTRIBUTE8 := NULL ;
2998 l_tgdv_rcn_tbl_in(i+6000).ATTRIBUTE9 := NULL ;
2999 l_tgdv_rcn_tbl_in(i+6000).ATTRIBUTE10 := NULL ;
3000 l_tgdv_rcn_tbl_in(i+6000).ATTRIBUTE11 := NULL ;
3001 l_tgdv_rcn_tbl_in(i+6000).ATTRIBUTE12 := NULL ;
3002 l_tgdv_rcn_tbl_in(i+6000).ATTRIBUTE13 := NULL ;
3003 l_tgdv_rcn_tbl_in(i+6000).ATTRIBUTE14 := NULL ;
3004 l_tgdv_rcn_tbl_in(i+6000).ATTRIBUTE15 := NULL ;
3005
3006
3007 l_rilt_tbl_in(i+6000).tve_id :=l_tgdv_rcn_tbl_in(i+6000).id;
3008 l_rilt_tbl_in(i+6000).rul_id :=l_rulv_tbl_in(i+5000).id;
3009 l_rilt_tbl_in(i+6000).uom_code :='HR';
3010 l_rilt_tbl_in(i+6000).duration :=SAT_RESOLUTION_TIMES_tbl(i);
3011 l_rilt_tbl_in(i+6000).object_version_number :=1;
3012 l_rilt_tbl_in(i+6000).dnz_chr_id := l_clev_tbl_in(i).dnz_chr_Id;
3013 l_rilt_tbl_in(i+6000).CREATION_DATE := g_CREATION_DATE;
3014 l_rilt_tbl_in(i+6000).CREATED_BY := g_CREATED_BY;
3015 l_rilt_tbl_in(i+6000).LAST_UPDATE_DATE := g_LAST_UPDATE_DATE;
3016 l_rilt_tbl_in(i+6000).LAST_UPDATED_BY := g_LAST_UPDATED_BY;
3017 l_rilt_tbl_in(i+6000).LAST_UPDATE_LOGIN := g_LAST_UPDATE_LOGIN;
3018 l_rilt_tbl_in(i+6000).object_version_number := 1;
3019 END IF; ---SAT_RESOLUTION_TIMES_tbl
3020
3021 END LOOP;
3022 -- -- dbms_output.put_line('Test6');
3023 END IF;
3024 IF L_validate_flag = 'Y' THEN
3025
3026
3027
3028
3029 If l_clev_tbl_in.count > 0 Then
3030 l_error_message := 'okc_cle_pvt -';
3031 okc_cle_pvt.Insert_Row(
3032 p_api_version => l_api_version,
3033 p_init_msg_list => l_init_msg_list,
3034 x_return_status => l_return_status ,
3035 x_msg_count => l_msg_count,
3036 x_msg_data => l_msg_data,
3037 p_clev_tbl => l_clev_tbl_in,
3038 x_clev_tbl => x_clev_tbl_in);
3039
3040 dbms_output.put_line('Value of l_return_status='||l_return_status);
3041 End If;
3042
3043 l_Clev_tbl_in.DELETE;
3044
3045 IF l_Cimv_tbl_in.COUNT>0
3046 THEN
3047 l_error_message := 'okc_cim_pvt -';
3048 okc_cim_pvt.Insert_Row(
3049 p_api_version => l_api_version,
3050 p_init_msg_list => l_init_msg_list,
3051 x_return_status => l_return_status ,
3052 x_msg_count => l_msg_count,
3053 x_msg_data => l_msg_data,
3054 p_cimv_tbl => l_cimv_tbl_in,
3055 x_cimv_tbl => x_cimv_tbl_in);
3056 dbms_output.put_line('Value of l_return_status='||l_return_status);
3057 END IF;
3058 l_Cimv_tbl_in.DELETE;
3059
3060
3061 IF l_rgpv_tbl_in.COUNT>0
3062 THEN
3063 l_error_message := 'okc_rgp_pvt -';
3064 okc_rgp_pvt.Insert_Row(
3065 p_api_version => l_api_version,
3066 p_init_msg_list => l_init_msg_list,
3067 x_return_status => l_return_status ,
3068 x_msg_count => l_msg_count,
3069 x_msg_data => l_msg_data,
3070 p_rgpv_tbl => l_rgpv_tbl_in,
3071 x_rgpv_tbl => x_rgpv_tbl_in);
3072
3073 dbms_output.put_line('Value of l_return_status='||l_return_status);
3074 END IF;
3075 l_rgpv_tbl_in.DELETE;
3076
3077
3078 IF l_rulv_tbl_in.COUNT>0
3079 THEN
3080 l_error_message := 'okc_rul_pvt -';
3081 okc_rul_pvt.Insert_Row(
3082 p_api_version => l_api_version,
3083 p_init_msg_list => l_init_msg_list,
3084 x_return_status => l_return_status ,
3085 x_msg_count => l_msg_count,
3086 x_msg_data => l_msg_data,
3087 p_rulv_tbl => l_rulv_tbl_in,
3088 x_rulv_tbl => x_rulv_tbl_in);
3089
3090 dbms_output.put_line('Value of l_return_status='||l_return_status);
3091 END IF;
3092 l_rulv_tbl_in.delete;
3093
3094 IF l_Cplv_tbl_In.COUNT>0
3095 THEN
3096 l_error_message := 'okc_cpl_pvt -';
3097 okc_cpl_pvt.Insert_Row(
3098 p_api_version => l_api_version,
3099 p_init_msg_list => l_init_msg_list,
3100 x_return_status => l_return_status ,
3101 x_msg_count => l_msg_count,
3102 x_msg_data => l_msg_data,
3103 p_cplv_tbl => l_cplv_tbl_in,
3104 x_cplv_tbl => x_cplv_tbl_in);
3105
3106 dbms_output.put_line('Value of l_return_status='||l_return_status);
3107 END IF;
3108 l_Cplv_Tbl_In.DELETE;
3109
3110 IF l_ctcv_tbl_In.COUNT>0
3111 THEN
3112 l_error_message := 'okc_ctc_pvt -';
3113 okc_ctc_pvt.Insert_Row(
3114 p_api_version => l_api_version,
3115 p_init_msg_list => l_init_msg_list,
3116 x_return_status => l_return_status ,
3117 x_msg_count => l_msg_count,
3118 x_msg_data => l_msg_data,
3119 p_ctcv_tbl => l_ctcv_tbl_in,
3120 x_ctcv_tbl => x_ctcv_tbl_in);
3121
3122 dbms_output.put_line('Value of l_return_status='||l_return_status);
3123 END IF;
3124 l_ctcv_Tbl_In.DELETE;
3125
3126 /*----------------------------------------*/
3127
3128 IF l_isev_rel_tbl_in.count > 0
3129 THEN
3130 l_error_message := 'okc_time_pvt.Insert_ise_Row_Upg -';
3131 okc_time_pvt.Insert_ise_Row_Upg(l_isev_rel_tbl_in);
3132 END IF;
3133 l_isev_rel_tbl_in.delete;
3134 /*-----------------------------------------*/
3135 IF l_isev_ext_tbl_In.COUNT>0
3136 THEN
3137 l_error_message := 'okc_time_pvt.Insert_ise_Row_Upg -';
3138 okc_time_pvt.Insert_ise_Row_Upg(l_isev_ext_tbl_in);
3139 END IF;
3140 l_isev_ext_Tbl_In.DELETE;
3141
3142 IF l_igsv_ext_tbl_In.COUNT>0
3143 THEN
3144 l_error_message := 'okc_time_pvt.Insert_igs_Row_Upg -';
3145 okc_time_pvt.Insert_igs_Row_Upg( l_igsv_ext_tbl_in);
3146 END IF;
3147 l_igsv_ext_Tbl_In.DELETE;
3148
3149
3150 IF l_ctiv_tbl_In.COUNT>0
3151 THEN
3152 l_error_message := 'okc_cti_pvt -';
3153 -- okc_cti_pvt.Insert_Row_Upg( l_return_status , l_ctiv_tbl_in);
3154 okc_cti_pvt.Insert_Row(
3155 p_api_version => l_api_version,
3156 p_init_msg_list => l_init_msg_list,
3157 x_return_status => l_return_status ,
3158 x_msg_count => l_msg_count,
3159 x_msg_data => l_msg_data,
3160 p_ctiv_tbl => l_ctiv_tbl_in,
3161 x_ctiv_tbl => x_ctiv_tbl_in);
3162
3163 dbms_output.put_line('Value of l_return_status='||l_return_status);
3164
3165 END IF;
3166 l_ctiv_Tbl_In.DELETE;
3167 IF l_tgdv_ext_tbl_in.COUNT>0
3168 THEN
3169 l_error_message := 'okc_Time_pvt -';
3170 okc_Time_pvt.Insert_Tgd_Row_Upg( l_tgdv_ext_tbl_in);
3171 END IF;
3172 l_tgdv_ext_tbl_in.DELETE;
3173
3174 IF l_rilv_tbl_in.COUNT>0
3175 THEN
3176 l_error_message := 'okc_ril_pvt -';
3177 -- okc_ril_pvt.Insert_Row_Upg( l_return_status , l_rilv_tbl_in);
3178 okc_ril_pvt.Insert_Row(
3179 p_api_version => l_api_version,
3180 p_init_msg_list => l_init_msg_list,
3181 x_return_status => l_return_status ,
3182 x_msg_count => l_msg_count,
3183 x_msg_data => l_msg_data,
3184 p_rilv_tbl => l_rilv_tbl_in,
3185 x_rilv_tbl => x_rilv_tbl_in);
3186
3187 dbms_output.put_line('Value of l_return_status='||l_return_status);
3188 END IF;
3189 l_rilv_tbl_in.DELETE;
3190
3191 IF l_tgdv_rcn_tbl_in.COUNT>0
3192 THEN
3193 l_error_message := 'okc_Time_pvt -';
3194 okc_Time_pvt.Insert_Tgd_Row_Upg( l_tgdv_rcn_tbl_in);
3195 END IF;
3196 l_tgdv_rcn_tbl_in.DELETE;
3197
3198 IF l_rilt_tbl_in.COUNT>0
3199 THEN
3200 l_error_message := 'okc_ril_pvt -';
3201 -- okc_ril_pvt.Insert_Row_Upg( l_return_status , l_rilt_tbl_in);
3202 okc_ril_pvt.Insert_Row(
3203 p_api_version => l_api_version,
3204 p_init_msg_list => l_init_msg_list,
3205 x_return_status => l_return_status ,
3206 x_msg_count => l_msg_count,
3207 x_msg_data => l_msg_data,
3208 p_rilv_tbl => l_rilt_tbl_in,
3209 x_rilv_tbl => x_rilt_tbl_in);
3210
3211 dbms_output.put_line('Value of l_return_status='||l_return_status);
3212 END IF;
3213 l_rilt_tbl_in.DELETE;
3214
3215
3216
3217 END IF;
3218
3219 ----------===================N=============================
3220
3221 IF L_validate_flag = 'N' THEN
3222
3223 IF l_Clev_tbl_in.COUNT>0
3224 THEN
3225 l_error_message := 'okc_cle_pvt -';
3226 -- dbms_output.put_line('In Sert');
3227 okc_cle_pvt.Insert_Row_Upg( l_return_status , l_clev_tbl_in);
3228 END IF;
3229 l_Clev_tbl_in.DELETE;
3230
3231 IF l_Cimv_tbl_in.COUNT>0
3232 THEN
3233 l_error_message := 'okc_cim_pvt -';
3234 okc_cim_pvt.Insert_Row_Upg( l_return_status , l_cimv_tbl_in);
3235 END IF;
3236 l_Cimv_tbl_in.DELETE;
3237
3238
3239 IF l_rgpv_tbl_in.COUNT>0
3240 THEN
3241 l_error_message := 'okc_rgp_pvt -';
3242 okc_rgp_pvt.Insert_Row_Upg( l_return_status , l_rgpv_tbl_in);
3243 END IF;
3244 l_rgpv_tbl_in.DELETE;
3245
3246
3247 IF l_rulv_tbl_in.COUNT>0
3248 THEN
3249 l_error_message := 'okc_rul_pvt -';
3250 okc_rul_pvt.Insert_Row_Upg( l_return_status , l_rulv_tbl_in);
3251 END IF;
3252 l_rulv_tbl_in.delete;
3253
3254 IF l_Cplv_tbl_In.COUNT>0
3255 THEN
3256 l_error_message := 'okc_cpl_pvt -';
3257 okc_cpl_pvt.Insert_Row_Upg( l_return_status , l_cplv_tbl_in);
3258 END IF;
3259 l_Cplv_Tbl_In.DELETE;
3260 IF l_ctcv_tbl_In.COUNT>0
3261 THEN
3262 l_error_message := 'okc_ctc_pvt -';
3263 okc_ctc_pvt.Insert_Row_Upg( l_return_status , l_ctcv_tbl_in);
3264 END IF;
3265 l_ctcv_Tbl_In.DELETE;
3266 /*----------------------------------------*/
3267 IF l_isev_rel_tbl_in.count > 0
3268 THEN
3269 l_error_message := 'okc_time_pvt.Insert_ise_Row_Upg -';
3270 okc_time_pvt.Insert_ise_Row_Upg(l_isev_rel_tbl_in);
3271 END IF;
3272 l_isev_rel_tbl_in.delete;
3273 /*-----------------------------------------*/
3274 IF l_isev_ext_tbl_In.COUNT>0
3275 THEN
3276 l_error_message := 'okc_time_pvt.Insert_ise_Row_Upg -';
3277 okc_time_pvt.Insert_ise_Row_Upg(l_isev_ext_tbl_in);
3278 END IF;
3279 l_isev_ext_Tbl_In.DELETE;
3280
3281 IF l_igsv_ext_tbl_In.COUNT>0
3282 THEN
3283 l_error_message := 'okc_time_pvt.Insert_igs_Row_Upg -';
3284 okc_time_pvt.Insert_igs_Row_Upg( l_igsv_ext_tbl_in);
3285 END IF;
3286 l_igsv_ext_Tbl_In.DELETE;
3287
3288
3289 IF l_ctiv_tbl_In.COUNT>0
3290 THEN
3291 l_error_message := 'okc_cti_pvt -';
3292 okc_cti_pvt.Insert_Row_Upg( l_return_status , l_ctiv_tbl_in);
3293 END IF;
3294 l_ctiv_Tbl_In.DELETE;
3295 IF l_tgdv_ext_tbl_in.COUNT>0
3296 THEN
3297 l_error_message := 'okc_Time_pvt -';
3298 okc_Time_pvt.Insert_Tgd_Row_Upg( l_tgdv_ext_tbl_in);
3299 END IF;
3300 l_tgdv_ext_tbl_in.DELETE;
3301
3302 IF l_rilv_tbl_in.COUNT>0
3303 THEN
3304 l_error_message := 'okc_ril_pvt -';
3305 okc_ril_pvt.Insert_Row_Upg( l_return_status , l_rilv_tbl_in);
3306 END IF;
3307 l_rilv_tbl_in.DELETE;
3308
3309 IF l_tgdv_rcn_tbl_in.COUNT>0
3310 THEN
3311 l_error_message := 'okc_Time_pvt -';
3312 okc_Time_pvt.Insert_Tgd_Row_Upg( l_tgdv_rcn_tbl_in);
3313 END IF;
3314 l_tgdv_rcn_tbl_in.DELETE;
3315
3316 IF l_rilt_tbl_in.COUNT>0
3317 THEN
3318 l_error_message := 'okc_ril_pvt -';
3319 okc_ril_pvt.Insert_Row_Upg( l_return_status , l_rilt_tbl_in);
3320 END IF;
3321 l_rilt_tbl_in.DELETE;
3322
3323 END IF;
3324 FORALL i in 1 .. COVERAGE_BUSS_PROCESS_ID_tbl.COUNT
3325
3326 UPDATE oks_cov_txn_groups_int_all
3327 SET INTERFACED_STATUS_FLAG = 'S',
3328 LAST_UPDATED_BY = -1,
3329 LAST_UPDATE_DATE = sysdate,
3330 LAST_UPDATE_LOGIN = -1
3331 WHERE coverage_bus_process_id = COVERAGE_BUSS_PROCESS_ID_tbl(i);
3332
3333 commit;
3334
3335 exit when get_buss_process_cur%notfound;
3336 EXCEPTION
3337 WHEN OTHERS THEN
3338 ROLLBACK;
3339 RAISE_APPLICATION_ERROR(-20000,'Error in Business Process Interface');
3340 END;
3341 END LOOP;
3342
3343 IF get_buss_process_cur%ISOPEN
3344 THEN
3345 CLOSE get_buss_process_cur;
3346 END IF;
3347 exception when others then
3348 ROLLBACK;
3349 RAISE_APPLICATION_ERROR(-20000,'Error in Business Process Interface');
3350 -- dbms_output.put_line('Outer Block'||sqlerrm);
3351
3352 END; ---Business_Processes_migrate
3353
3354 PROCEDURE Bill_Types_Migrate (P_FromId IN NUMBER,
3355 P_ToId IN NUMBER,
3356 P_VALIDATE_FLAG IN VARCHAR2,
3357 P_LOG_PARAMETER IN VARCHAR2) IS
3358
3359 CURSOR get_bill_types (p_fromId IN NUMBER,p_toId IN NUMBER) IS
3360 SELECT
3361 obt.COV_BP_BILLING_TYPE_ID ,
3362 obt.UPTO_AMOUNT ,
3363 obt.PERCENT_COVER ,
3364 obt.BILLING_TYPE_ID ,
3365 obt.COVERAGE_BUS_PROCESS_ID ,
3366 obt.ATTRIBUTE_CATEGORY ,
3367 obt.ATTRIBUTE1 ,
3368 obt.ATTRIBUTE2 ,
3369 obt.ATTRIBUTE3 ,
3370 obt.ATTRIBUTE4 ,
3371 obt.ATTRIBUTE5 ,
3372 obt.ATTRIBUTE6 ,
3373 obt.ATTRIBUTE7 ,
3374 obt.ATTRIBUTE8 ,
3375 obt.ATTRIBUTE9 ,
3376 obt.ATTRIBUTE10 ,
3377 obt.ATTRIBUTE11 ,
3378 obt.ATTRIBUTE12 ,
3379 obt.ATTRIBUTE13 ,
3380 obt.ATTRIBUTE14 ,
3381 obt.ATTRIBUTE15 ,
3382 okl.id ,
3383 okl.dnz_chr_id ,
3384 okl.sts_code ,
3385 okl.start_date ,
3386 okl.end_date ,
3387 okl.currency_code
3388 FROM
3389 oks_cov_bill_types_int_all obt ,
3390 oks_cov_txn_groups_int_all obp,
3391 okc_k_lines_b okl,
3392 oks_coverages_int_all cov,
3393 oks_con_lines_int_all lines,
3394 oks_con_headers_int_all head
3395
3396 WHERE
3397 obt.COVERAGE_BUS_PROCESS_ID = obp.COVERAGE_BUS_PROCESS_ID
3398 AND okl.upg_Orig_System_Ref= g_bpline_ref --'Int_Buss_Process_Line'
3399 AND okl.upg_Orig_System_Ref_id = obp.coverage_bus_process_id
3400 AND okl.lse_id = 3
3401 AND obt.coverage_id = obp.coverage_id
3402 AND obt.interfaced_status_flag is null
3403 AND cov.interfaced_status_flag = 'S'
3404 AND lines.interfaced_status_flag = 'S'
3405 AND head.interfaced_status_flag = 'S'
3406 AND obp.coverage_id = cov.coverage_id
3407 AND cov.coverage_id = lines.coverage_id
3408 AND lines.contract_id = head.contract_id
3409 AND head.batch_number between p_FromId and P_ToId;
3410
3411 l_validate_flag VARCHAR2(1) := p_validate_flag;
3412
3413 l_error_message varchar2(2000);
3414 l_return_status varchar2(2);
3415 COV_BP_BILLING_TYPE_ID_tbl Num_Tbl_type;
3416 UPTO_AMOUNT_tbl Num_Tbl_type;
3417 PERCENT_COVER_tbl Num_Tbl_type;
3418 BILLING_TYPE_ID_tbl Num_Tbl_type;
3419 COVERAGE_BUS_PROCESS_ID_tbl Num_Tbl_type;
3420 CURRENCY_CODE_Tbl Vc15_Tbl_Type;
3421 ATTRIBUTE_CATEGORY_tbl Vc150_Tbl_Type;
3422 ATTRIBUTE1_tbl Vc150_Tbl_Type;
3423 ATTRIBUTE2_tbl Vc150_Tbl_Type;
3424 ATTRIBUTE3_tbl Vc150_Tbl_Type;
3425 ATTRIBUTE4_tbl Vc150_Tbl_Type;
3426 ATTRIBUTE5_tbl Vc150_Tbl_Type;
3427 ATTRIBUTE6_tbl Vc150_Tbl_Type;
3428 ATTRIBUTE7_tbl Vc150_Tbl_Type;
3429 ATTRIBUTE8_tbl Vc150_Tbl_Type;
3430 ATTRIBUTE9_tbl Vc150_Tbl_Type;
3431 ATTRIBUTE10_tbl Vc150_Tbl_Type;
3432 ATTRIBUTE11_tbl Vc150_Tbl_Type;
3433 ATTRIBUTE12_tbl Vc150_Tbl_Type;
3434 ATTRIBUTE13_tbl Vc150_Tbl_Type;
3435 ATTRIBUTE14_tbl Vc150_Tbl_Type;
3436 ATTRIBUTE15_tbl Vc150_Tbl_Type;
3437 id_tbl NUM_Tbl_type;
3438 dnz_chr_id_tbl NUM_Tbl_type;
3439 sts_code_tbl Vc30_Tbl_Type;
3440 start_date_tbl Date_Tbl_Type;
3441 end_date_tbl Date_Tbl_Type;
3442
3443 TYPE K_Status_rec IS RECORD (old_status Varchar2(30),new_status Varchar2(30));
3444 TYPE K_Status_Tab is TABLE OF K_Status_Rec INDEX BY BINARY_Integer;
3445 l_status_tab K_Status_Tab;
3446 FUNCTION Get_new_status(p_k_status_id IN Number)
3447 RETURN Varchar2 IS
3448 l_new_status Varchar2(30);
3449 BEGIN
3450 Return(l_status_tab(p_k_status_id).new_status);
3451 END Get_new_status;
3452
3453 Begin -- Main Begin of Bill Types --
3454 ---- dbms_output.put_line('Test');
3455 open get_bill_types (p_FromId,p_ToID);
3456 loop
3457 BEGIN
3458 fetch get_bill_types bulk collect into
3459 COV_BP_BILLING_TYPE_ID_tbl,
3460 UPTO_AMOUNT_tbl,
3461 PERCENT_COVER_tbl,
3462 BILLING_TYPE_ID_tbl,
3463 COVERAGE_BUS_PROCESS_ID_tbl,
3464 ATTRIBUTE_CATEGORY_tbl,
3465 ATTRIBUTE1_tbl,
3466 ATTRIBUTE2_tbl,
3467 ATTRIBUTE3_tbl,
3468 ATTRIBUTE4_tbl,
3469 ATTRIBUTE5_tbl,
3470 ATTRIBUTE6_tbl,
3471 ATTRIBUTE7_tbl,
3472 ATTRIBUTE8_tbl,
3473 ATTRIBUTE9_tbl,
3474 ATTRIBUTE10_tbl,
3475 ATTRIBUTE11_tbl,
3476 ATTRIBUTE12_tbl,
3477 ATTRIBUTE13_tbl,
3478 ATTRIBUTE14_tbl,
3479 ATTRIBUTE15_tbl,
3480 id_tbl,
3481 dnz_chr_id_tbl,
3482 sts_code_tbl,
3483 start_date_tbl,
3484 end_date_tbl,
3485 currency_code_tbl
3486 LIMIT 1000;
3487
3488 -- dbms_output.put_line('Value of COV_BP_BILLING_TYPE_ID_tbl.COUNT='||TO_CHAR(COV_BP_BILLING_TYPE_ID_tbl.COUNT));
3489 /*
3490 if (get_bill_types%notfound) then
3491 close get_bill_types;
3492 end if;
3493 */
3494 IF ( COV_BP_BILLING_TYPE_ID_tbl.COUNT > 0) THEN
3495 FOR i IN COV_BP_BILLING_TYPE_ID_tbl.FIRST .. COV_BP_BILLING_TYPE_ID_tbl.LAST
3496 LOOP
3497 -- dbms_output.put_line('Test44');
3498 l_clev_tbl_in(i).id := okc_p_util.raw_to_number(sys_guid());
3499 l_clev_tbl_in(i).CREATION_DATE := sysdate;
3500 l_clev_tbl_in(i).CREATED_BY := -1;
3501 l_clev_tbl_in(i).LAST_UPDATE_DATE := sysdate;
3502 l_clev_tbl_in(i).LAST_UPDATED_BY := -1;
3503 l_clev_tbl_in(i).LAST_UPDATE_LOGIN := -1;
3504 l_clev_tbl_in(i).object_version_number := 1;
3505 l_clev_tbl_in(i).dnz_chr_id := dnz_chr_id_tbl(i);
3506 l_clev_tbl_in(i).cle_id := Id_Tbl(i);
3507 l_clev_tbl_in(i).chr_id := null;
3508 l_clev_tbl_in(i).sfwt_flag :='N';
3509 l_clev_tbl_in(i).lse_id := 5;
3510 l_clev_tbl_in(i).sts_code :=sts_code_tbl(i);
3511 -- l_clev_tbl_in(i).orig_system_id1 := NULL;
3512 l_clev_tbl_in(i).display_sequence :=1;
3513 -- dbms_output.put_line('Test55');
3514 l_clev_tbl_in(i).Name :='Upgraded'; --Name;
3515 l_clev_tbl_in(i).exception_yn :='N'; -- clarify
3516 l_clev_tbl_in(i).Currency_Code :=Currency_Code_Tbl(i);
3517 l_clev_tbl_in(i).start_date :=start_date_tbl(i);
3518 l_clev_tbl_in(i).end_date :=end_date_tbl(i);
3519 l_clev_tbl_in(i).attribute1 :=Attribute1_Tbl(i);
3520 l_clev_tbl_in(i).attribute2 :=Attribute2_Tbl(i);
3521 l_clev_tbl_in(i).attribute3 :=Attribute3_Tbl(i);
3522 l_clev_tbl_in(i).attribute4 :=Attribute4_Tbl(i);
3523 l_clev_tbl_in(i).attribute5 :=Attribute5_Tbl(i);
3524 l_clev_tbl_in(i).attribute6 :=Attribute6_Tbl(i);
3525 l_clev_tbl_in(i).attribute7 :=Attribute7_Tbl(i);
3526 l_clev_tbl_in(i).attribute8 :=Attribute8_Tbl(i);
3527 l_clev_tbl_in(i).attribute9 :=Attribute9_Tbl(i);
3528 l_clev_tbl_in(i).attribute10 :=Attribute10_Tbl(i);
3529 l_clev_tbl_in(i).attribute11 :=Attribute11_Tbl(i);
3530 l_clev_tbl_in(i).attribute12 :=Attribute12_Tbl(i);
3531 l_clev_tbl_in(i).attribute13 :=Attribute13_Tbl(i);
3532 l_clev_tbl_in(i).attribute14 :=Attribute14_Tbl(i);
3533 l_clev_tbl_in(i).attribute15 :=Attribute15_Tbl(i);
3534 l_clev_tbl_in(i).attribute_Category :=ATTRIBUTE_CATEGORY_tbl(i);
3535 l_clev_tbl_in(i).Upg_Orig_System_Ref :=g_btline_ref ;
3536 -- dbms_output.put_line('Test66');
3537 l_clev_tbl_in(i).Upg_Orig_System_Ref_Id :=COV_BP_BILLING_TYPE_ID_tbl(i);
3538 l_clev_tbl_in(i).INVOICE_LINE_LEVEL_IND :=Null;
3539 l_clev_tbl_in(i).DPAS_RATING :=Null;
3540 l_clev_tbl_in(i).TEMPLATE_USED :=Null;
3541 l_clev_tbl_in(i).PRICE_TYPE :=Null;
3542 --l_clev_tbl_in(i).UOM_CODE :=Null;
3543 l_clev_tbl_in(i).LINE_NUMBER :='4';
3544 l_clev_tbl_in(i).TRN_CODE :=Null;
3545 l_clev_tbl_in(i).LAST_UPDATE_LOGIN :=Null;
3546 l_clev_tbl_in(i).HIDDEN_IND :=Null;
3547 l_clev_tbl_in(i).DATE_TERMINATED :=Null;
3548 l_clev_tbl_in(i).CLE_ID_RENEWED_TO :=Null;
3549 l_clev_tbl_in(i).CURRENCY_CODE_RENEWED :=Null;
3550 l_clev_tbl_in(i).PRICE_NEGOTIATED_RENEWED :=Null;
3551 -- dbms_output.put_line('Test77');
3552 l_clev_tbl_in(i).cle_id_renewed :=NULL;
3553 l_clev_tbl_in(i).comments :=NULL;
3554 l_clev_tbl_in(i).price_unit :=NULL;
3555 l_clev_tbl_in(i).price_unit_percent :=NULL;
3556 l_clev_tbl_in(i).price_negotiated :=NULL;
3557 l_clev_tbl_in(i).price_level_ind :='N';
3558 l_clev_tbl_in(i).block23text :=NULL;
3559 l_clev_tbl_in(i).program_application_id:= fnd_global.prog_appl_id;
3560 l_clev_tbl_in(i).program_id:= fnd_global.CONC_PROGRAM_ID;
3561 -- -- dbms_output.put_line('Test');
3562
3563 l_cimv_tbl_in(i).cle_id := l_clev_tbl_in(i).Id;
3564 l_cimv_tbl_in(i).chr_id := null;
3565 l_cimv_tbl_in(i).cle_id_for := Null;
3566 l_cimv_tbl_in(i).object1_id1 := Billing_Type_Id_Tbl(i);
3567 l_cimv_tbl_in(i).object1_id2 := '#';
3568 l_cimv_tbl_in(i).JTOT_OBJECT1_CODE := 'OKX_BILLTYPE';
3569 l_cimv_tbl_in(i).exception_yn := 'N';
3570 l_cimv_tbl_in(i).number_of_items := 1;
3571 l_cimv_tbl_in(i).dnz_chr_id := dnz_chr_id_tbl(i);
3572 l_cimv_tbl_in(i).id := okc_p_util.raw_to_number(sys_guid());
3573 l_cimv_tbl_in(i).CREATION_DATE := sysdate;
3574 l_cimv_tbl_in(i).CREATED_BY := -1 ;
3575 l_cimv_tbl_in(i).LAST_UPDATE_DATE := sysdate;
3576 l_cimv_tbl_in(i).LAST_UPDATED_BY := -1 ;
3577 l_cimv_tbl_in(i).LAST_UPDATE_LOGIN := -1 ;
3578 l_cimv_tbl_in(i).object_version_number := 1;
3579 l_cimv_tbl_in(i).CLE_ID_FOR :=Null;
3580 l_cimv_tbl_in(i).PRICED_ITEM_YN :=Null;
3581 l_cimv_tbl_in(i).UPG_ORIG_SYSTEM_REF :=Null;
3582 l_cimv_tbl_in(i).UPG_ORIG_SYSTEM_REF_ID :=Null;
3583 l_cimv_tbl_in(i).uom_code := Null;
3584
3585 l_cimv_tbl_in(i).program_application_id:= fnd_global.prog_appl_id;
3586 l_cimv_tbl_in(i).program_id:= fnd_global.CONC_PROGRAM_ID;
3587
3588 l_rgpv_tbl_in(i).cle_id := l_clev_tbl_in(i).Id;
3589 l_rgpv_tbl_in(i).sfwt_flag := 'N';
3590 l_rgpv_tbl_in(i).rgd_code := 'SVC_K';
3591 l_rgpv_tbl_in(i).rgp_type := 'KRG';
3592 l_rgpv_tbl_in(i).id := okc_p_util.raw_to_number(sys_guid());
3593 l_rgpv_tbl_in(i).CREATION_DATE := sysdate;
3594 l_rgpv_tbl_in(i).CREATED_BY := -1 ;
3595 l_rgpv_tbl_in(i).LAST_UPDATE_DATE := sysdate;
3596 l_rgpv_tbl_in(i).LAST_UPDATED_BY := -1 ;
3597 l_rgpv_tbl_in(i).LAST_UPDATE_LOGIN := -1 ;
3598 l_rgpv_tbl_in(i).object_version_number := 1;
3599 l_rgpv_tbl_in(i).dnz_chr_id:=dnz_chr_id_tbl(i);
3600 l_rgpv_tbl_in(i).CHR_ID :=Null;
3601 l_rgpv_tbl_in(i).PARENT_RGP_ID :=Null;
3602 l_rgpv_tbl_in(i).SAT_CODE :=Null;
3603 l_rgpv_tbl_in(i).COMMENTS :=Null;
3604 l_rgpv_tbl_in(i).ATTRIBUTE_CATEGORY :=Null;
3605 l_rgpv_tbl_in(i).ATTRIBUTE1 :=Null;
3606 l_rgpv_tbl_in(i).ATTRIBUTE2 :=Null;
3607 l_rgpv_tbl_in(i).ATTRIBUTE3 :=Null;
3608 l_rgpv_tbl_in(i).ATTRIBUTE4 :=Null;
3609 l_rgpv_tbl_in(i).ATTRIBUTE5 :=Null;
3610 l_rgpv_tbl_in(i).ATTRIBUTE6 :=Null;
3611 l_rgpv_tbl_in(i).ATTRIBUTE7 :=Null;
3612 l_rgpv_tbl_in(i).ATTRIBUTE8 :=Null;
3613 l_rgpv_tbl_in(i).ATTRIBUTE9 :=Null;
3614 l_rgpv_tbl_in(i).ATTRIBUTE10 :=Null;
3615 l_rgpv_tbl_in(i).ATTRIBUTE11 :=Null;
3616 l_rgpv_tbl_in(i).ATTRIBUTE12 :=Null;
3617 l_rgpv_tbl_in(i).ATTRIBUTE13 :=Null;
3618 l_rgpv_tbl_in(i).ATTRIBUTE14 :=Null;
3619 l_rgpv_tbl_in(i).ATTRIBUTE15 :=Null;
3620
3621
3622
3623 IF ((upto_amount_Tbl(i) IS NOT NULL) OR (Percent_cover_Tbl(i) IS NOT NULL)) THEN
3624
3625 l_rulv_tbl_in(i).rgp_id := l_rgpv_tbl_in(i).id;
3626 l_rulv_tbl_in(i).sfwt_flag := 'N';
3627 l_rulv_tbl_in(i).rule_information_category := 'LMT';
3628 l_rulv_tbl_in(i).rule_information2 := upto_amount_Tbl(i);
3629 l_rulv_tbl_in(i).rule_information4 := Percent_cover_Tbl(i);
3630 l_rulv_tbl_in(i).std_template_yn := 'N';
3631 l_rulv_tbl_in(i).warn_yn := 'N';
3632 l_rulv_tbl_in(i).dnz_chr_id :=dnz_chr_id_tbl(i);
3633 l_rulv_tbl_in(i).id := okc_p_util.raw_to_number(sys_guid());
3634 l_rulv_tbl_in(i).CREATION_DATE := sysdate;
3635 l_rulv_tbl_in(i).CREATED_BY := -1 ;
3636 l_rulv_tbl_in(i).LAST_UPDATE_DATE := sysdate;
3637 l_rulv_tbl_in(i).LAST_UPDATED_BY := -1;
3638 l_rulv_tbl_in(i).LAST_UPDATE_LOGIN := -1;
3639 l_rulv_tbl_in(i).object_version_number := 1;
3640 l_rulv_tbl_in(i).OBJECT1_ID1 :=NULL;
3641 l_rulv_tbl_in(i).OBJECT2_ID1 :=NULL;
3642 l_rulv_tbl_in(i).OBJECT3_ID1 :=NULL;
3643 l_rulv_tbl_in(i).OBJECT1_ID2 :=NULL;
3644 l_rulv_tbl_in(i).OBJECT2_ID2 :=NULL;
3645 l_rulv_tbl_in(i).OBJECT3_ID2 :=NULL;
3646 l_rulv_tbl_in(i).JTOT_OBJECT1_CODE :=NULL;
3647 l_rulv_tbl_in(i).JTOT_OBJECT2_CODE :=NULL;
3648 l_rulv_tbl_in(i).JTOT_OBJECT3_CODE :=NULL;
3649 l_rulv_tbl_in(i).PRIORITY :=NULL;
3650 l_rulv_tbl_in(i).COMMENTS :=NULL;
3651 l_rulv_tbl_in(i).ATTRIBUTE_CATEGORY :=NULL;
3652 l_rulv_tbl_in(i).ATTRIBUTE1 :=NULL;
3653 l_rulv_tbl_in(i).ATTRIBUTE2 :=NULL;
3654 l_rulv_tbl_in(i).ATTRIBUTE3 :=NULL;
3655 l_rulv_tbl_in(i).ATTRIBUTE4 :=NULL;
3656 l_rulv_tbl_in(i).ATTRIBUTE5 :=NULL;
3657 l_rulv_tbl_in(i).ATTRIBUTE6 :=NULL;
3658 l_rulv_tbl_in(i).ATTRIBUTE7 :=NULL;
3659 l_rulv_tbl_in(i).ATTRIBUTE8 :=NULL;
3660 l_rulv_tbl_in(i).ATTRIBUTE9 :=NULL;
3661 l_rulv_tbl_in(i).ATTRIBUTE10 :=NULL;
3662 l_rulv_tbl_in(i).ATTRIBUTE11 :=NULL;
3663 l_rulv_tbl_in(i).ATTRIBUTE12 :=NULL;
3664 l_rulv_tbl_in(i).ATTRIBUTE13 :=NULL;
3665 l_rulv_tbl_in(i).ATTRIBUTE14 :=NULL;
3666 l_rulv_tbl_in(i).ATTRIBUTE15 :=NULL;
3667 l_rulv_tbl_in(i).TEXT :=NULL;
3668 l_rulv_tbl_in(i).RULE_INFORMATION1 :=NULL;
3669 l_rulv_tbl_in(i).RULE_INFORMATION3 :=NULL;
3670 l_rulv_tbl_in(i).RULE_INFORMATION5 :=NULL;
3671 l_rulv_tbl_in(i).RULE_INFORMATION6 :=NULL;
3672 l_rulv_tbl_in(i).RULE_INFORMATION7 :=NULL;
3673 l_rulv_tbl_in(i).RULE_INFORMATION8 :=NULL;
3674 l_rulv_tbl_in(i).RULE_INFORMATION9 :=NULL;
3675 l_rulv_tbl_in(i).RULE_INFORMATION10 :=NULL;
3676 l_rulv_tbl_in(i).RULE_INFORMATION11 :=NULL;
3677 l_rulv_tbl_in(i).RULE_INFORMATION12 :=NULL;
3678 l_rulv_tbl_in(i).RULE_INFORMATION13 :=NULL;
3679 l_rulv_tbl_in(i).RULE_INFORMATION14 :=NULL;
3680 l_rulv_tbl_in(i).RULE_INFORMATION15 :=NULL;
3681 End If;
3682
3683 end loop;
3684
3685 end if;
3686
3687 If l_validate_flag = 'Y' THEN
3688 IF l_Clev_tbl_in.COUNT>0 THEN
3689 l_Error_Message := 'okc_cle_pvt';
3690 okc_cle_pvt.Insert_Row(
3691 p_api_version => l_api_version,
3692 p_init_msg_list => l_init_msg_list,
3693 x_return_status => l_return_status ,
3694 x_msg_count => l_msg_count,
3695 x_msg_data => l_msg_data,
3696 p_clev_tbl => l_clev_tbl_in,
3697 x_clev_tbl => x_clev_tbl_in);
3698 END IF;
3699
3700 IF l_rgpv_tbl_in.COUNT>0 THEN
3701 l_Error_Message := 'okc_rgp_pvt';
3702 okc_rgp_pvt.Insert_Row(
3703 p_api_version => l_api_version,
3704 p_init_msg_list => l_init_msg_list,
3705 x_return_status => l_return_status ,
3706 x_msg_count => l_msg_count,
3707 x_msg_data => l_msg_data,
3708 p_rgpv_tbl => l_rgpv_tbl_in,
3709 x_rgpv_tbl => x_rgpv_tbl_in);
3710 END IF;
3711
3712 IF l_rulv_tbl_in.COUNT>0 THEN
3713
3714 l_Error_Message := 'okc_rul_pvt';
3715 okc_rul_pvt.Insert_Row(
3716 p_api_version => l_api_version,
3717 p_init_msg_list => l_init_msg_list,
3718 x_return_status => l_return_status ,
3719 x_msg_count => l_msg_count,
3720 x_msg_data => l_msg_data,
3721 p_rulv_tbl => l_rulv_tbl_in,
3722 x_rulv_tbl => x_rulv_tbl_in);
3723
3724 END IF;
3725
3726 IF l_Cimv_tbl_in.COUNT>0 THEN
3727 l_Error_Message := 'okc_cim_pvt';
3728 okc_cim_pvt.Insert_Row(
3729 p_api_version => l_api_version,
3730 p_init_msg_list => l_init_msg_list,
3731 x_return_status => l_return_status ,
3732 x_msg_count => l_msg_count,
3733 x_msg_data => l_msg_data,
3734 p_cimv_tbl => l_cimv_tbl_in,
3735 x_cimv_tbl => x_cimv_tbl_in);
3736 END IF;
3737
3738 END IF;
3739
3740
3741 if l_validate_flag = 'N' THEN
3742
3743 IF l_Clev_tbl_in.COUNT>0 THEN
3744 l_Error_Message := 'okc_cle_pvt';
3745 okc_cle_pvt.Insert_Row_Upg( l_return_status , l_clev_tbl_in);
3746 END IF;
3747
3748 IF l_rgpv_tbl_in.COUNT>0 THEN
3749 l_Error_Message := 'okc_rgp_pvt';
3750 okc_rgp_pvt.Insert_Row_Upg( l_return_status , l_rgpv_tbl_in);
3751 END IF;
3752
3753 IF l_rulv_tbl_in.COUNT>0 THEN
3754
3755 l_Error_Message := 'okc_rul_pvt';
3756 okc_rul_pvt.Insert_Row_Upg( l_return_status , l_rulv_tbl_in);
3757 END IF;
3758
3759
3760 IF l_Cimv_tbl_in.COUNT>0 THEN
3761 l_Error_Message := 'okc_cim_pvt';
3762 okc_cim_pvt.Insert_Row_Upg( l_return_status , l_cimv_tbl_in);
3763 END IF;
3764
3765 END IF;
3766 l_Cimv_tbl_in.DELETE;
3767 l_rgpv_tbl_in.DELETE;
3768 l_rulv_tbl_in.DELETE;
3769 l_Clev_tbl_in.DELETE;
3770 -- dbms_output.put_line('Before Commit');
3771
3772 FORALL i in 1 .. COV_BP_BILLING_TYPE_ID_tbl.COUNT
3773
3774 UPDATE oks_cov_bill_types_int_all
3775 SET INTERFACED_STATUS_FLAG = 'S',
3776 LAST_UPDATED_BY = -1,
3777 LAST_UPDATE_DATE = sysdate,
3778 LAST_UPDATE_LOGIN = -1
3779 WHERE COV_BP_BILLING_TYPE_ID = COV_BP_BILLING_TYPE_ID_tbl(i);
3780
3781 commit;
3782
3783 exit when get_bill_types%notfound;
3784 EXCEPTION
3785 WHEN OTHERS THEN
3786 ROLLBACK;
3787 RAISE_APPLICATION_ERROR(-20000,'Error in Bill Type Interface');
3788 END;
3789
3790
3791 end loop;
3792 IF get_bill_types%ISOPEN
3793 THEN
3794 CLOSE get_bill_types;
3795 END IF;
3796
3797
3798 end; -- Bill_Types_Migrate
3799
3800
3801 PROCEDURE Bill_Rates_Migrate (P_FromId IN NUMBER,
3802 P_ToId IN NUMBER,
3803 P_VALIDATE_FLAG IN VARCHAR2,
3804 P_LOG_PARAMETER IN VARCHAR2) IS
3805
3806 CURSOR get_bill_rates_cur (P_FromId IN NUMBER,P_TOId IN NUMBER) IS
3807 select
3808 obr.COVERAGE_BILL_RATE_ID,
3809 obr.COVERAGE_BILLING_TYPE_ID,
3810 obr.BILL_RATE_CODE,
3811 obr.UNIT_OF_MEASURE_CODE,
3812 obr.FLAT_RATE,
3813 obr.PERCENT_RATE,
3814 obr.ATTRIBUTE_CATEGORY,
3815 obr.ATTRIBUTE1,
3816 obr.ATTRIBUTE2,
3817 obr.ATTRIBUTE3,
3818 obr.ATTRIBUTE4,
3819 obr.ATTRIBUTE5,
3820 obr.ATTRIBUTE6,
3821 obr.ATTRIBUTE7,
3822 obr.ATTRIBUTE8,
3823 obr.ATTRIBUTE9,
3824 obr.ATTRIBUTE10,
3825 obr.ATTRIBUTE11,
3826 obr.ATTRIBUTE12,
3827 obr.ATTRIBUTE13,
3828 obr.ATTRIBUTE14,
3829 obr.ATTRIBUTE15,
3830 okl.id,
3831 okl.sts_code,
3832 okl.start_date,
3833 okl.end_date,
3834 okl.dnz_chr_id,
3835 okl.currency_code
3836 FROM oks_cov_bill_rates_int_all obr ,
3837 oks_cov_bill_types_int_all obt,
3838 okc_k_lines_b okl,
3839 oks_cov_txn_groups_int_all obp,
3840 oks_coverages_int_all cov,
3841 oks_con_lines_int_all lines,
3842 oks_con_headers_int_all head
3843 WHERE obr.COVERAGE_BILLING_TYPE_ID = obt.COV_BP_BILLING_TYPE_ID
3844 AND okl.upg_Orig_System_Ref= g_btline_ref
3845 AND okl.upg_Orig_System_Ref_Id = obt.COV_BP_BILLING_TYPE_ID
3846 AND okl.lse_id = 5
3847 AND obr.interfaced_status_flag is null
3848 AND obt.interfaced_status_flag = 'S'
3849 AND head.interfaced_status_flag = 'S'
3850 AND lines.interfaced_status_flag = 'S'
3851 AND cov.interfaced_status_flag = 'S'
3852 AND obp.interfaced_status_flag = 'S'
3853 AND obt.interfaced_status_flag = 'S'
3854 AND head.batch_number between p_fromid and p_toid
3855 AND head.contract_id = lines.contract_id
3856 AND lines.coverage_id = cov.coverage_id
3857 AND cov.coverage_id = obp.coverage_id
3858 AND obp.COVERAGE_BUS_PROCESS_ID = obt.COVERAGE_BUS_PROCESS_ID
3859 AND obp.coverage_id = obt.coverage_id;
3860
3861
3862
3863 l_validate_flag VARCHAR2(1) := p_validate_flag;
3864
3865 l_error_message varchar2(2000);
3866 l_return_status varchar2(2);
3867 COVERAGE_BILL_RATE_ID_tbl NUM_TBL_TYPE ;
3868 COVERAGE_BILLING_TYPE_ID_tbl NUM_TBL_TYPE ;
3869 BILL_RATE_CODE_tbl VC30_TBL_TYPE;
3870 UNIT_OF_MEASURE_CODE_tbl Vc15_Tbl_Type;
3871 FLAT_RATE_tbl NUM_TBL_TYPE;
3872 PERCENT_RATE_tbl NUM_TBL_TYPE;
3873 ATTRIBUTE_CATEGORY_tbl Vc150_Tbl_Type;
3874 ATTRIBUTE1_tbl Vc150_Tbl_Type;
3875 ATTRIBUTE2_tbl Vc150_Tbl_Type;
3876 ATTRIBUTE3_tbl Vc150_Tbl_Type;
3877 ATTRIBUTE4_tbl Vc150_Tbl_Type;
3878 ATTRIBUTE5_tbl Vc150_Tbl_Type;
3879 ATTRIBUTE6_tbl Vc150_Tbl_Type;
3880 ATTRIBUTE7_tbl Vc150_Tbl_Type;
3881 ATTRIBUTE8_tbl Vc150_Tbl_Type;
3882 ATTRIBUTE9_tbl Vc150_Tbl_Type;
3883 ATTRIBUTE10_tbl Vc150_Tbl_Type;
3884 ATTRIBUTE11_tbl Vc150_Tbl_Type;
3885 ATTRIBUTE12_tbl Vc150_Tbl_Type;
3886 ATTRIBUTE13_tbl Vc150_Tbl_Type;
3887 ATTRIBUTE14_tbl Vc150_Tbl_Type;
3888 ATTRIBUTE15_tbl Vc150_Tbl_Type;
3889 ID_tbl NUM_TBL_TYPE ;
3890 sts_code_tbl Vc30_Tbl_Type;
3891 start_date_tbl Date_Tbl_Type;
3892 end_date_tbl Date_Tbl_Type;
3893 dnz_chr_id_tbl Num_Tbl_Type;
3894 CURRENCY_CODE_Tbl Vc15_Tbl_Type;
3895
3896 FUNCTION GetTimeUom(P_Uom_Code IN VARCHAR2)
3897 RETURN Varchar2 IS
3898 l_TimeUnit VARCHAR2(3):= NULL;
3899 BEGIN
3900 IF Upper(P_Uom_Code)='DAY'
3901 THEN l_TimeUnit:='DAY';
3902 ELSIF Upper(P_Uom_Code)='HOUR'
3903 THEN l_TimeUnit:='HR';
3904 ELSIF Upper(P_Uom_Code)='MINUTE'
3905 THEN l_TimeUnit:='MIN';
3906 ELSIF Upper(P_Uom_Code)='WEEK'
3907 THEN l_TimeUnit:='WK';
3908 ELSIF Upper(P_Uom_Code)='MONTH'
3909 THEN l_TimeUnit:='MTH';
3910 ELSIF Upper(P_Uom_Code)='YEAR'
3911 THEN l_TimeUnit:='YR';
3912 END IF;
3913 RETURN l_TimeUnit;
3914 END GetTimeUom;
3915 begin
3916 ---- dbms_output.put_line('Test');
3917
3918 open get_bill_rates_cur (p_fromid,p_toid);
3919 loop
3920 begin
3921 fetch get_bill_rates_cur bulk collect into
3922 COVERAGE_BILL_RATE_ID_tbl,
3923 COVERAGE_BILLING_TYPE_ID_tbl,
3924 BILL_RATE_CODE_tbl,
3925 UNIT_OF_MEASURE_CODE_tbl,
3926 FLAT_RATE_tbl,
3927 PERCENT_RATE_tbl,
3928 ATTRIBUTE_CATEGORY_tbl,
3929 ATTRIBUTE1_tbl,
3930 ATTRIBUTE2_tbl,
3931 ATTRIBUTE3_tbl,
3932 ATTRIBUTE4_tbl,
3933 ATTRIBUTE5_tbl,
3934 ATTRIBUTE6_tbl,
3935 ATTRIBUTE7_tbl,
3936 ATTRIBUTE8_tbl,
3937 ATTRIBUTE9_tbl,
3938 ATTRIBUTE10_tbl,
3939 ATTRIBUTE11_tbl,
3940 ATTRIBUTE12_tbl,
3941 ATTRIBUTE13_tbl,
3942 ATTRIBUTE14_tbl,
3943 ATTRIBUTE15_tbl,
3944 id_tbl ,
3945 sts_code_tbl,
3946 start_date_tbl,
3947 end_date_tbl,
3948 dnz_chr_id_tbl,
3949 currency_code_tbl
3950 limit 1000;
3951 /*
3952 if (get_bill_rates_cur%notfound) then
3953 close get_bill_rates_cur;
3954 end if;
3955 */
3956 if (COVERAGE_BILL_RATE_ID_tbl.count > 0) then
3957 ---- dbms_output.put_line('Test2');
3958 FOR i IN COVERAGE_BILL_RATE_ID_tbl.FIRST .. COVERAGE_BILL_RATE_ID_tbl.LAST
3959 LOOP
3960 l_clev_tbl_in(i).id := okc_p_util.raw_to_number(sys_guid());
3961 l_clev_tbl_in(i).CREATION_DATE := sysdate;
3962 l_clev_tbl_in(i).CREATED_BY := -1;
3963 l_clev_tbl_in(i).LAST_UPDATE_DATE := sysdate;
3964 l_clev_tbl_in(i).LAST_UPDATED_BY := -1 ;
3965 l_clev_tbl_in(i).LAST_UPDATE_LOGIN := -1 ;
3966 l_clev_tbl_in(i).object_version_number := 1;
3967 l_clev_tbl_in(i).cle_id := Id_Tbl(i);
3968 l_clev_tbl_in(i).chr_id := null;
3969 l_clev_tbl_in(i).sfwt_flag :='N';
3970 l_clev_tbl_in(i).lse_id := 6;
3971 l_clev_tbl_in(i).sts_code := sts_code_tbl(i); --Get_New_Status(Contract_Line_Status_Id_Tbl(i));
3972 l_clev_tbl_in(i).display_sequence :=1;
3973 l_clev_tbl_in(i).start_date := Start_Date_Tbl(i);
3974 l_clev_tbl_in(i).end_date :=END_Date_Tbl(i);
3975 l_clev_tbl_in(i).Currency_Code :=Currency_Code_Tbl(i);
3976 l_clev_tbl_in(i).Name :='Migrated Bill Rate ';
3977 l_clev_tbl_in(i).exception_yn :='N'; -- clarify
3978 l_clev_tbl_in(i).attribute1 :=Attribute1_Tbl(i);
3979 l_clev_tbl_in(i).attribute2 :=Attribute2_Tbl(i);
3980 l_clev_tbl_in(i).attribute3 :=Attribute3_Tbl(i);
3981 l_clev_tbl_in(i).attribute4 :=Attribute4_Tbl(i);
3982 l_clev_tbl_in(i).attribute5 :=Attribute5_Tbl(i);
3983 l_clev_tbl_in(i).attribute6 :=Attribute6_Tbl(i);
3984 l_clev_tbl_in(i).attribute7 :=Attribute7_Tbl(i);
3985 l_clev_tbl_in(i).attribute8 :=Attribute8_Tbl(i);
3986 l_clev_tbl_in(i).attribute9 :=Attribute9_Tbl(i);
3987 l_clev_tbl_in(i).attribute10 :=Attribute10_Tbl(i);
3988 l_clev_tbl_in(i).attribute11 :=Attribute11_Tbl(i);
3989 l_clev_tbl_in(i).attribute12 :=Attribute12_Tbl(i);
3990 l_clev_tbl_in(i).attribute13 :=Attribute13_Tbl(i);
3991 l_clev_tbl_in(i).attribute14 :=Attribute14_Tbl(i);
3992 l_clev_tbl_in(i).attribute15 :=Attribute15_Tbl(i);
3993 l_clev_tbl_in(i).attribute_Category :=ATTRIBUTE_CATEGORY_tbl(i);
3994 l_clev_tbl_in(i).dnz_chr_id := dnz_chr_id_tbl(i);
3995 l_clev_tbl_in(i).Upg_Orig_System_Ref :='OKS_COV_BILL_RATES_INT_ALL'; --'Migrated_BILL_RATES';
3996 l_clev_tbl_in(i).Upg_Orig_System_Ref_Id :=Coverage_Bill_Rate_ID_Tbl(i);
3997 l_clev_tbl_in(i).INVOICE_LINE_LEVEL_IND :=Null;
3998 l_clev_tbl_in(i).DPAS_RATING :=Null;
3999 l_clev_tbl_in(i).TEMPLATE_USED :=Null;
4000 l_clev_tbl_in(i).PRICE_TYPE :=Null;
4001 -- l_clev_tbl_in(i).UOM_CODE :=Null;
4002 l_clev_tbl_in(i).LINE_NUMBER :='6';
4003 l_clev_tbl_in(i).TRN_CODE :=Null;
4004 l_clev_tbl_in(i).HIDDEN_IND :=Null;
4005 l_clev_tbl_in(i).DATE_TERMINATED :=Null;
4006 l_clev_tbl_in(i).CLE_ID_RENEWED_TO :=Null;
4007 l_clev_tbl_in(i).CURRENCY_CODE_RENEWED :=Null;
4008 l_clev_tbl_in(i).PRICE_NEGOTIATED_RENEWED :=Null;
4009 l_clev_tbl_in(i).cle_id_renewed :=NULL;
4010 l_clev_tbl_in(i).comments :=NULL;
4011 l_clev_tbl_in(i).price_unit :=NULL;
4012 l_clev_tbl_in(i).price_unit_percent :=NULL;
4013 l_clev_tbl_in(i).price_negotiated :=NULL;
4014 l_clev_tbl_in(i).price_level_ind :='N';
4015 l_clev_tbl_in(i).block23text :=NULL;
4016 l_clev_tbl_in(i).program_application_id:= fnd_global.prog_appl_id;
4017 l_clev_tbl_in(i).program_id:= fnd_global.CONC_PROGRAM_ID;
4018
4019 l_rgpv_tbl_in(i).cle_id := l_clev_tbl_in(i).Id;
4020 l_rgpv_tbl_in(i).sfwt_flag := 'N';
4021 l_rgpv_tbl_in(i).rgd_code := 'SVC_K';
4022 l_rgpv_tbl_in(i).rgp_type := 'KRG';
4023 l_rgpv_tbl_in(i).id := okc_p_util.raw_to_number(sys_guid());
4024 l_rgpv_tbl_in(i).CREATION_DATE := sysdate;
4025 l_rgpv_tbl_in(i).CREATED_BY := -1;
4026 l_rgpv_tbl_in(i).LAST_UPDATE_DATE := sysdate;
4027 l_rgpv_tbl_in(i).LAST_UPDATED_BY := -1;
4028 l_rgpv_tbl_in(i).LAST_UPDATE_LOGIN := -1;
4029 l_rgpv_tbl_in(i).object_version_number := 1;
4030 l_rgpv_tbl_in(i).dnz_chr_id :=dnz_chr_id_tbl(i);
4031 l_rgpv_tbl_in(i).CHR_ID :=Null;
4032 l_rgpv_tbl_in(i).PARENT_RGP_ID :=Null;
4033 l_rgpv_tbl_in(i).SAT_CODE :=Null;
4034 l_rgpv_tbl_in(i).COMMENTS :=Null;
4035 l_rgpv_tbl_in(i).ATTRIBUTE_CATEGORY :=Null;
4036 l_rgpv_tbl_in(i).ATTRIBUTE1 :=Null;
4037 l_rgpv_tbl_in(i).ATTRIBUTE2 :=Null;
4038 l_rgpv_tbl_in(i).ATTRIBUTE3 :=Null;
4039 l_rgpv_tbl_in(i).ATTRIBUTE4 :=Null;
4040 l_rgpv_tbl_in(i).ATTRIBUTE5 :=Null;
4041 l_rgpv_tbl_in(i).ATTRIBUTE6 :=Null;
4042 l_rgpv_tbl_in(i).ATTRIBUTE7 :=Null;
4043 l_rgpv_tbl_in(i).ATTRIBUTE8 :=Null;
4044 l_rgpv_tbl_in(i).ATTRIBUTE9 :=Null;
4045 l_rgpv_tbl_in(i).ATTRIBUTE10 :=Null;
4046 l_rgpv_tbl_in(i).ATTRIBUTE11 :=Null;
4047 l_rgpv_tbl_in(i).ATTRIBUTE12 :=Null;
4048 l_rgpv_tbl_in(i).ATTRIBUTE13 :=Null;
4049 l_rgpv_tbl_in(i).ATTRIBUTE14 :=Null;
4050 l_rgpv_tbl_in(i).ATTRIBUTE15 :=Null;
4051
4052
4053 IF ((BILL_RATE_CODE_TBL(i) IS Not NULL)
4054 AND (Unit_Of_Measure_Code_Tbl(i) IS Not NULL))
4055 THEN
4056 l_rulv_tbl_in(i).rgp_id := l_rgpv_tbl_in(i).id;
4057 l_rulv_tbl_in(i).sfwt_flag := 'N';
4058 l_rulv_tbl_in(i).OBJECT1_ID1 := Null;
4059 l_rulv_tbl_in(i).OBJECT1_ID2 := Null;
4060 l_rulv_tbl_in(i).JTOT_OBJECT1_code := Null;
4061 l_rulv_tbl_in(i).rule_information_category := 'RSL';
4062 l_rulv_tbl_in(i).rule_information1 := gettimeuom(Unit_Of_Measure_Code_Tbl(i));
4063 l_rulv_tbl_in(i).rule_information2 := Flat_Rate_Tbl(i);
4064 l_rulv_tbl_in(i).rule_information3 := Percent_Rate_Tbl(i);
4065 l_rulv_tbl_in(i).rule_information4 := BILL_RATE_CODE_Tbl(i);
4066 l_rulv_tbl_in(i).std_template_yn := 'N';
4067 l_rulv_tbl_in(i).warn_yn := 'N';
4068 l_rulv_tbl_in(i).dnz_chr_id := dnz_chr_id_Tbl(i);
4069 l_rulv_tbl_in(i).id := okc_p_util.raw_to_number(sys_guid());
4070 l_rulv_tbl_in(i).CREATION_DATE :=sysdate;
4071 l_rulv_tbl_in(i).CREATED_BY := -1;
4072 l_rulv_tbl_in(i).LAST_UPDATE_DATE := sysdate;
4073 l_rulv_tbl_in(i).LAST_UPDATED_BY := -1;
4074 l_rulv_tbl_in(i).LAST_UPDATE_LOGIN:= -1;
4075 l_rulv_tbl_in(i).object_version_number := 1;
4076 l_rulv_tbl_in(i).OBJECT2_ID1 :=NULL;
4077 l_rulv_tbl_in(i).OBJECT3_ID1 :=NULL;
4078 l_rulv_tbl_in(i).OBJECT2_ID2 :=NULL;
4079 l_rulv_tbl_in(i).OBJECT3_ID2 :=NULL;
4080 l_rulv_tbl_in(i).JTOT_OBJECT2_CODE :=NULL;
4081 l_rulv_tbl_in(i).JTOT_OBJECT3_CODE :=NULL;
4082 l_rulv_tbl_in(i).PRIORITY :=NULL;
4083 l_rulv_tbl_in(i).COMMENTS :=NULL;
4084 l_rulv_tbl_in(i).ATTRIBUTE_CATEGORY :=NULL;
4085 l_rulv_tbl_in(i).ATTRIBUTE1 :=NULL;
4086 l_rulv_tbl_in(i).ATTRIBUTE2 :=NULL;
4087 l_rulv_tbl_in(i).ATTRIBUTE3 :=NULL;
4088 l_rulv_tbl_in(i).ATTRIBUTE4 :=NULL;
4089 l_rulv_tbl_in(i).ATTRIBUTE5 :=NULL;
4090 l_rulv_tbl_in(i).ATTRIBUTE6 :=NULL;
4091 l_rulv_tbl_in(i).ATTRIBUTE7 :=NULL;
4092 l_rulv_tbl_in(i).ATTRIBUTE8 :=NULL;
4093 l_rulv_tbl_in(i).ATTRIBUTE9 :=NULL;
4094 l_rulv_tbl_in(i).ATTRIBUTE10 :=NULL;
4095 l_rulv_tbl_in(i).ATTRIBUTE11 :=NULL;
4096 l_rulv_tbl_in(i).ATTRIBUTE12 :=NULL;
4097 l_rulv_tbl_in(i).ATTRIBUTE13 :=NULL;
4098 l_rulv_tbl_in(i).ATTRIBUTE14 :=NULL;
4099 l_rulv_tbl_in(i).ATTRIBUTE15 :=NULL;
4100 l_rulv_tbl_in(i).TEXT :=NULL;
4101 l_rulv_tbl_in(i).RULE_INFORMATION5 :=NULL;
4102 l_rulv_tbl_in(i).RULE_INFORMATION6 :=NULL;
4103 l_rulv_tbl_in(i).RULE_INFORMATION7 :=NULL;
4104 l_rulv_tbl_in(i).RULE_INFORMATION8 :=NULL;
4105 l_rulv_tbl_in(i).RULE_INFORMATION9 :=NULL;
4106 l_rulv_tbl_in(i).RULE_INFORMATION10 :=NULL;
4107 l_rulv_tbl_in(i).RULE_INFORMATION11 :=NULL;
4108 l_rulv_tbl_in(i).RULE_INFORMATION12 :=NULL;
4109 l_rulv_tbl_in(i).RULE_INFORMATION13 :=NULL;
4110 l_rulv_tbl_in(i).RULE_INFORMATION14 :=NULL;
4111 l_rulv_tbl_in(i).RULE_INFORMATION15 :=NULL;
4112 end if;
4113 end loop;
4114 end if;
4115 IF l_validate_flag = 'Y' THEN
4116 IF l_Clev_tbl_in.COUNT>0 THEN
4117 l_Error_Message := 'okc_cle_pvt';
4118 okc_cle_pvt.Insert_Row(
4119 p_api_version => l_api_version,
4120 p_init_msg_list => l_init_msg_list,
4121 x_return_status => l_return_status ,
4122 x_msg_count => l_msg_count,
4123 x_msg_data => l_msg_data,
4124 p_clev_tbl => l_clev_tbl_in,
4125 x_clev_tbl => x_clev_tbl_in);
4126 END IF;
4127
4128 IF l_rgpv_tbl_in.COUNT>0 THEN
4129 l_Error_Message := 'okc_rgp_pvt';
4130 okc_rgp_pvt.Insert_Row(
4131 p_api_version => l_api_version,
4132 p_init_msg_list => l_init_msg_list,
4133 x_return_status => l_return_status ,
4134 x_msg_count => l_msg_count,
4135 x_msg_data => l_msg_data,
4136 p_rgpv_tbl => l_rgpv_tbl_in,
4137 x_rgpv_tbl => x_rgpv_tbl_in);
4138 END IF;
4139
4140 IF l_rulv_tbl_in.COUNT>0 THEN
4141 l_Error_Message := 'okc_rul_pvt';
4142 okc_rul_pvt.Insert_Row(
4143 p_api_version => l_api_version,
4144 p_init_msg_list => l_init_msg_list,
4145 x_return_status => l_return_status ,
4146 x_msg_count => l_msg_count,
4147 x_msg_data => l_msg_data,
4148 p_rulv_tbl => l_rulv_tbl_in,
4149 x_rulv_tbl => x_rulv_tbl_in);
4150
4151 END IF;
4152
4153
4154 END IF;
4155
4156 IF l_validate_flag = 'N' THEN
4157
4158 IF l_Clev_tbl_in.COUNT>0 THEN
4159 l_error_message := 'okc_cle_pvt -';
4160 okc_cle_pvt.Insert_Row_Upg( l_return_status , l_clev_tbl_in);
4161 END IF;
4162 l_Clev_tbl_in.DELETE;
4163
4164
4165 IF l_rgpv_tbl_in.COUNT>0 THEN
4166 l_error_message := 'okc_rgp_pvt -';
4167 okc_rgp_pvt.Insert_Row_Upg( l_return_status , l_rgpv_tbl_in);
4168 END IF;
4169 l_rgpv_tbl_in.DELETE;
4170
4171
4172 IF l_rulv_tbl_in.COUNT>0 THEN
4173 l_error_message := 'okc_rul_pvt -';
4174 okc_rul_pvt.Insert_Row_Upg( l_return_status , l_rulv_tbl_in);
4175 END IF;
4176 l_rulv_tbl_in.delete;
4177
4178 END IF;
4179 FORALL i in 1 .. COVERAGE_BILL_RATE_ID_tbl.COUNT
4180
4181 UPDATE oks_cov_bill_rates_int_all
4182 SET INTERFACED_STATUS_FLAG = 'S',
4183 LAST_UPDATED_BY = -1,
4184 LAST_UPDATE_DATE = sysdate,
4185 LAST_UPDATE_LOGIN = -1
4186 WHERE COVERAGE_BILL_RATE_ID = COVERAGE_BILL_RATE_ID_tbl(i);
4187
4188 commit;
4189
4190 exit when get_bill_rates_cur%notfound;
4191 EXCEPTION
4192 WHEN OTHERS THEN
4193 ROLLBACK;
4194 RAISE_APPLICATION_ERROR(-20000,'Error in Bill Rate Interface');
4195 END;
4196
4197 end loop;
4198
4199 IF get_bill_rates_cur%ISOPEN
4200 THEN
4201 CLOSE get_bill_rates_cur;
4202 END IF;
4203
4204 end ;---Bill_Rates_Migrate
4205
4206 PROCEDURE Print_report ( P_FromId IN NUMBER,
4207 P_ToId IN NUMBER) IS
4208
4209
4210 Cursor get_con_headers (p_from_id in NUMBER,p_to_id in NUMBER)
4211 IS SELECT contract_number,
4212 contract_id,
4213 interfaced_status_flag,
4214 batch_number
4215 FROM OKS_CON_HEADERS_INT_ALL
4216 WHERE BATCH_NUMBER between p_from_id and p_to_id;
4217
4218 Cursor get_con_lines(p_id IN NUMBER)
4219 IS SELECT Contract_Id,
4220 Contract_line_id,
4221 Coverage_Id,
4222 Interfaced_status_flag
4223 FROM OKS_CON_LINES_INT_ALL lines
4224 WHERE lines.contract_id = p_id;
4225
4226 Cursor get_coverages(p_id IN NUMBER)
4227 IS SELECT Coverage_id,
4228 NAME,
4229 Interfaced_status_flag
4230 FROM OKS_COVERAGES_INT_ALL cov
4231 WHERE cov.coverage_id = p_id;
4232
4233 Cursor get_Buss_Process (p_id in NUMBER)
4234 IS SELECT Coverage_Bus_process_id,
4235 Business_process_id,
4236 Interfaced_Status_flag
4237 FROM oks_cov_txn_groups_int_all txn
4238 WHERE txn.coverage_id = p_id;
4239
4240 Cursor get_bill_types (p_id in NUMBER)
4241 IS SELECT Interfaced_Status_flag,
4242 COV_BP_BILLING_TYPE_ID,
4243 Upto_amount,
4244 Percent_Cover
4245 FROM oks_cov_bill_types_int_all
4246 WHERE coverage_bus_process_id = p_id;
4247
4248 Cursor get_bill_rates (p_id in NUMBER)
4249 IS SELECT Interfaced_Status_flag,
4250 COVERAGE_BILL_RATE_ID
4251 FROM oks_cov_bill_rates_int_all
4252 WHERE COVERAGE_BILLING_TYPE_ID = p_id;
4253
4254 BEGIN
4255
4256 for gen_con_head_rec in get_con_headers(p_fromid,p_toid) LOOP
4257
4258 fnd_file.new_line(FND_FILE.OUTPUT, 2);
4259 fnd_file.new_line(FND_FILE.OUTPUT, 2);
4260
4261 fnd_file.put_line(FND_FILE.OUTPUT,'Batch Number :'||gen_con_head_rec.batch_number);
4262 fnd_file.put_line(FND_FILE.OUTPUT,'Contract Number:'||gen_con_head_rec.Contract_number);
4263 fnd_file.put_line(FND_FILE.OUTPUT,'Migrated :'||gen_con_head_rec.interfaced_status_flag);
4264
4265 If gen_con_head_rec.interfaced_status_flag = 'S' THEN
4266
4267 for gen_con_line_rec in get_con_lines(gen_con_head_rec.contract_id) LOOP
4268
4269 fnd_file.put_line(FND_FILE.OUTPUT,'Service Line :'||gen_con_line_rec.Contract_line_id);
4270 fnd_file.put_line(FND_FILE.OUTPUT,'Migrated :'||gen_con_line_rec.interfaced_status_flag);
4271
4272 If gen_con_line_rec.interfaced_status_flag = 'S' THEN
4273
4274 for get_coverages_rec in get_coverages(gen_con_line_rec.coverage_id) Loop
4275
4276 fnd_file.put_line(FND_FILE.OUTPUT,'Coverage :'||get_coverages_rec.NAME);
4277 fnd_file.put_line(FND_FILE.OUTPUT,'Migrated :'||get_coverages_rec.interfaced_status_flag);
4278
4279 END LOOP;
4280
4281 END IF;
4282
4283 END LOOP;
4284
4285 END IF;
4286
4287 END LOOP;
4288
4289 END; --Print_report
4290
4291
4292 END OKS_COVERAGES_MIGRATION;
4293