[Home] [Help]
PACKAGE BODY: APPS.OKS_UPGRADE_PRECHECK
Source
1 PACKAGE BODY OKS_UPGRADE_PRECHECK AS
2 /* $Header: OKS22PCB.pls 115.5 2004/02/11 19:29:09 jvarghes noship $ */
3 PROCEDURE Log_Errors(P_Original_system_Reference IN VARCHAR2,
4 P_Original_System_Reference_Id IN NUMBER,
5 P_Original_System_Ref_Id_Upper IN NUMBER,
6 P_DateTime IN DATE,
7 P_Error_Message IN VARCHAR2) IS
8 PRAGMA AUTONOMOUS_TRANSACTION;
9 BEGIN
10 INSERT INTO CS_UPG_ERRORS(Orig_System_Reference,
11 Orig_System_Reference_Id,
12 Orig_System_Ref_Id_Upper,
13 Upgrade_DateTime,
14 Error_Message)
15 VALUES (P_Original_System_Reference,
16 P_Original_System_Reference_Id,
17 P_Original_System_Ref_Id_Upper,
18 P_DateTime,
19 P_Error_Message);
20 COMMIT;
21 END;
22
23 PROCEDURE Insert_Time_code_units IS
24
25 l_api_version CONSTANT NUMBER := 1.0;
26 l_init_msg_list CONSTANT VARCHAR2(1) := 'T';
27 l_return_status VARCHAR2(3);
28 l_return_msg VARCHAR2(2000);
29 l_msg_count NUMBER;
30 l_msg_data VARCHAR2(2000);
31 l_msg_index_out NUMBER;
32 l_Error_Message VARCHAR2(2000);
33
34 G_EXCEPTION_HALT_VALIDATION Exception;
35
36 CURSOR cur_timecode(p_uom_code IN varchar2,p_tce_code IN Varchar2) IS
37 SELECT 'y' FROM okc_time_code_units_v
38 WHERE uom_code = p_uom_code
39 AND tce_code = p_tce_code;
40
41 l_tcuv_rec_in okc_time_pub.tcuv_rec_type;
42 l_tcuv_rec_out okc_time_pub.tcuv_rec_type;
43
44 l_exist Varchar2(1);
45
46 BEGIN
47
48 l_exist := NULL;
49 OPEN cur_timecode('DAY','DAY');
50 FETCH cur_timecode INTO l_exist;
51 IF cur_timecode%NOTFOUND THEN
52 l_exist := 'n';
53 END IF;
54 CLOSE cur_timecode;
55
56 IF l_exist <> 'y' then
57 l_tcuv_rec_in.short_description := NULL;
58 l_tcuv_rec_in.description := NULL;
59 l_tcuv_rec_in.comments := NULL;
60 l_tcuv_rec_in.attribute_category := NULL;
61 l_tcuv_rec_in.attribute1 := NULL;
62 l_tcuv_rec_in.attribute2 := NULL;
63 l_tcuv_rec_in.attribute3 := NULL;
64 l_tcuv_rec_in.attribute4 := NULL;
65 l_tcuv_rec_in.attribute5 := NULL;
66 l_tcuv_rec_in.attribute6 := NULL;
67 l_tcuv_rec_in.attribute7 := NULL;
68 l_tcuv_rec_in.attribute8 := NULL;
69 l_tcuv_rec_in.attribute9 := NULL;
70 l_tcuv_rec_in.attribute10 := NULL;
71 l_tcuv_rec_in.attribute11 := NULL;
72 l_tcuv_rec_in.attribute12 := NULL;
73 l_tcuv_rec_in.attribute13 := NULL;
74 l_tcuv_rec_in.attribute14 := NULL;
75 l_tcuv_rec_in.attribute15 := NULL;
76 l_tcuv_rec_in.created_by := -1 ;
77 l_tcuv_rec_in.creation_date := SYSDATE;
78 l_tcuv_rec_in.last_updated_by := -1;
79 l_tcuv_rec_in.last_update_date := SYSDATE;
80 l_tcuv_rec_in.last_update_login := -1;
81 l_tcuv_rec_in.active_flag := 'Y';
82 l_tcuv_rec_in.uom_code := 'DAY';
83 l_tcuv_rec_in.tce_code := 'DAY';
84 l_tcuv_rec_in.object_version_number := 1;
85 l_tcuv_rec_in.sfwt_flag := 'N';
86 l_tcuv_rec_in.quantity := 1;
87
88 OKC_TIME_PUB.CREATE_TIME_CODE_UNITS(
89 p_api_version => l_api_version,
90 p_init_msg_list => l_init_msg_list,
91 x_return_status => l_return_status,
92 x_msg_count => l_msg_count,
93 x_msg_data => l_msg_data,
94 p_tcuv_rec => l_tcuv_rec_in,
95 x_tcuv_rec => l_tcuv_rec_out) ;
96
97 IF l_return_status <>'S' THEN
98 IF l_msg_count > 0
99 THEN
100 FOR i in 1..l_msg_count
101 LOOP
102 fnd_msg_pub.get (p_msg_index => -1,
103 p_encoded => 'F',
104 p_data => l_msg_data,
105 p_msg_index_out => l_msg_index_out);
106 END LOOP;
107 END IF;
108 l_Error_Message := 'Error while creating uom_code DAY(Day) into OKC_TIME_CODE_UNITS_B Table '||l_msg_data;
109 Log_Errors('INSERT_TIME_CODE_UNITS',
110 NULL,
111 NULL,
112 SYSDATE,
113 l_Error_Message);
114
115 raise G_EXCEPTION_HALT_VALIDATION;
116 END IF;
117 END IF;
118 l_return_status := NULL;
119 l_msg_count := NULL;
120 l_msg_data := NULL;
121 l_exist := NULL;
122
123 OPEN cur_timecode('HR','HOUR');
124 FETCH cur_timecode INTO l_exist;
125 IF cur_timecode%NOTFOUND THEN
126 l_exist := 'n';
127 END IF;
128 CLOSE cur_timecode;
129
130 IF l_exist <> 'y' then
131 l_tcuv_rec_in.uom_code := 'HR';
132 l_tcuv_rec_in.tce_code := 'HOUR';
133 l_tcuv_rec_in.object_version_number := 1;
134 l_tcuv_rec_in.sfwt_flag := 'N';
135 l_tcuv_rec_in.quantity := 1;
136
137 OKC_TIME_PUB.CREATE_TIME_CODE_UNITS(
138 p_api_version => l_api_version,
139 p_init_msg_list => l_init_msg_list,
140 x_return_status => l_return_status,
141 x_msg_count => l_msg_count,
142 x_msg_data => l_msg_data,
143 p_tcuv_rec => l_tcuv_rec_in,
144 x_tcuv_rec => l_tcuv_rec_out) ;
145
146 IF l_return_status <>'S' THEN
147 IF l_msg_count > 0
148 THEN
149 FOR i in 1..l_msg_count
150 LOOP
151 fnd_msg_pub.get (p_msg_index => -1,
152 p_encoded => 'F',
153 p_data => l_msg_data,
154 p_msg_index_out => l_msg_index_out);
155 END LOOP;
156 END IF;
157 l_Error_Message := 'Error while creating uom_code HR(Hour) into OKC_TIME_CODE_UNITS_B '||l_msg_data;
158 Log_Errors('INSERT_TIME_CODE_UNITS',
159 NULL,
160 NULL,
161 SYSDATE,
162 l_Error_Message);
163 raise G_EXCEPTION_HALT_VALIDATION;
164 END IF;
165 END IF;
166 l_return_status := NULL;
167 l_msg_count := NULL;
168 l_msg_data := NULL;
169
170
171 l_exist := NULL;
172 OPEN cur_timecode('MIN','MINUTE');
173 FETCH cur_timecode INTO l_exist;
174 IF cur_timecode%NOTFOUND THEN
175 l_exist := 'n';
176 END IF;
177 CLOSE cur_timecode;
178
179 IF l_exist <> 'y' then
180 l_tcuv_rec_in.uom_code := 'MIN';
181 l_tcuv_rec_in.tce_code := 'MINUTE';
182 l_tcuv_rec_in.object_version_number := 1;
183 l_tcuv_rec_in.sfwt_flag := 'N';
184 l_tcuv_rec_in.quantity := 1;
185
186 OKC_TIME_PUB.CREATE_TIME_CODE_UNITS(
187 p_api_version => l_api_version,
188 p_init_msg_list => l_init_msg_list,
189 x_return_status => l_return_status,
190 x_msg_count => l_msg_count,
191 x_msg_data => l_msg_data,
192 p_tcuv_rec => l_tcuv_rec_in,
193 x_tcuv_rec => l_tcuv_rec_out) ;
194
195 IF l_return_status <>'S' THEN
196
197 IF l_msg_count > 0 THEN
198
199 FOR i in 1..l_msg_count LOOP
200
201 fnd_msg_pub.get (p_msg_index => -1,
202 p_encoded => 'F',
203 p_data => l_msg_data,
204 p_msg_index_out => l_msg_index_out);
205
206 END LOOP;
207
208 END IF;
209
210 l_Error_Message := 'Error while creating uom_code MIN(Minute) into OKC_TIME_CODE_UNITS_B '||l_msg_data;
211 Log_Errors('INSERT_TIME_CODE_UNITS',
212 NULL,
213 NULL,
214 SYSDATE,
215 l_Error_Message);
216 raise G_EXCEPTION_HALT_VALIDATION;
217 END IF;
218 END IF;
219 l_return_status := NULL;
220 l_msg_count := NULL;
221 l_msg_data := NULL;
222
223 l_exist := NULL;
224 OPEN cur_timecode('WK','DAY');
225 FETCH cur_timecode INTO l_exist;
226 IF cur_timecode%NOTFOUND THEN
227 l_exist := 'n';
228 END IF;
229 CLOSE cur_timecode;
230
231 IF l_exist <> 'y' then
232 l_tcuv_rec_in.uom_code := 'WK';
233 l_tcuv_rec_in.tce_code := 'DAY';
234 l_tcuv_rec_in.object_version_number := 1;
235 l_tcuv_rec_in.sfwt_flag := 'N';
236 l_tcuv_rec_in.quantity := 7;
237
238 OKC_TIME_PUB.CREATE_TIME_CODE_UNITS(
239 p_api_version => l_api_version,
240 p_init_msg_list => l_init_msg_list,
241 x_return_status => l_return_status,
242 x_msg_count => l_msg_count,
243 x_msg_data => l_msg_data,
244 p_tcuv_rec => l_tcuv_rec_in,
245 x_tcuv_rec => l_tcuv_rec_out) ;
246 IF l_return_status <>'S' THEN
247 IF l_msg_count > 0
248 THEN
249 FOR i in 1..l_msg_count
250 LOOP
251 fnd_msg_pub.get (p_msg_index => -1,
252 p_encoded => 'F',
253 p_data => l_msg_data,
254 p_msg_index_out => l_msg_index_out);
255 END LOOP;
256 END IF;
257 l_Error_Message := 'Error while creating uom_code WK(Day) into OKC_TIME_CODE_UNITS_B '||l_msg_data;
258 Log_Errors('INSERT_TIME_CODE_UNITS',
259 NULL,
260 NULL,
261 SYSDATE,
262 l_Error_Message);
263 raise G_EXCEPTION_HALT_VALIDATION;
264 END IF;
265 END IF;
266 l_return_status := NULL;
267 l_msg_count := NULL;
268 l_msg_data := NULL;
269
270 OPEN cur_timecode('MTH','MONTH');
271 FETCH cur_timecode INTO l_exist;
272 IF cur_timecode%NOTFOUND THEN
273 l_exist := 'n';
274 END IF;
275 CLOSE cur_timecode;
276
277 IF l_exist <> 'y' then
278 l_tcuv_rec_in.uom_code := 'MTH';
279 l_tcuv_rec_in.tce_code := 'MONTH';
280 l_tcuv_rec_in.object_version_number := 1;
281 l_tcuv_rec_in.sfwt_flag :='N';
282 l_tcuv_rec_in.quantity := 1;
283
284 OKC_TIME_PUB.CREATE_TIME_CODE_UNITS(
285 p_api_version => l_api_version,
286 p_init_msg_list => l_init_msg_list,
287 x_return_status => l_return_status,
288 x_msg_count => l_msg_count,
289 x_msg_data => l_msg_data,
290 p_tcuv_rec => l_tcuv_rec_in,
291 x_tcuv_rec => l_tcuv_rec_out) ;
292 IF l_return_status <>'S' THEN
293 IF l_msg_count > 0
294 THEN
295 FOR i in 1..l_msg_count
296 LOOP
297 fnd_msg_pub.get (p_msg_index => -1,
298 p_encoded => 'F',
299 p_data => l_msg_data,
300 p_msg_index_out => l_msg_index_out);
301 END LOOP;
302 END IF;
303 l_Error_Message := 'Error while creating uom_code MTH(Month) into OKC_TIME_CODE_UNITS_B '||l_msg_data;
304 Log_Errors('INSERT_TIME_CODE_UNITS',
305 NULL,
306 NULL,
307 SYSDATE,
308 l_Error_Message);
309 raise G_EXCEPTION_HALT_VALIDATION;
310 END IF;
311 END IF;
312 l_return_status := NULL;
313 l_msg_count := NULL;
314 l_msg_data := NULL;
315
316 OPEN cur_timecode('YR','YEAR');
317 FETCH cur_timecode INTO l_exist;
318 IF cur_timecode%NOTFOUND THEN
319 l_exist := 'n';
320 END IF;
321 CLOSE cur_timecode;
322
323 IF l_exist <> 'y' then
324
325 l_tcuv_rec_in.uom_code := 'YR';
326 l_tcuv_rec_in.tce_code := 'YEAR';
327 l_tcuv_rec_in.object_version_number := 1;
328 l_tcuv_rec_in.sfwt_flag := 'N';
329 l_tcuv_rec_in.quantity := 1;
330
331 OKC_TIME_PUB.CREATE_TIME_CODE_UNITS(
332 p_api_version => l_api_version,
333 p_init_msg_list => l_init_msg_list,
334 x_return_status => l_return_status,
335 x_msg_count => l_msg_count,
336 x_msg_data => l_msg_data,
337 p_tcuv_rec => l_tcuv_rec_in,
338 x_tcuv_rec => l_tcuv_rec_out) ;
339 IF l_return_status <>'S' THEN
340 IF l_msg_count > 0
341 THEN
342 FOR i in 1..l_msg_count
343 LOOP
344 fnd_msg_pub.get (p_msg_index => -1,
345 p_encoded => 'F',
346 p_data => l_msg_data,
347 p_msg_index_out => l_msg_index_out);
348 END LOOP;
349 END IF;
350 l_Error_Message := 'Error while creating uom_code YR(Year) into OKC_TIME_CODE_UNITS_B '||l_msg_data;
351 Log_Errors('INSERT_TIME_CODE_UNITS',
352 NULL,
353 NULL,
354 SYSDATE,
355 l_Error_Message);
356 raise G_EXCEPTION_HALT_VALIDATION;
357 END IF;
358 END IF;
359 l_return_status := NULL;
360 l_msg_count := NULL;
361 l_msg_data := NULL;
362
363 COMMIT;
364
365 EXCEPTION
366 When G_EXCEPTION_HALT_VALIDATION Then
367 rollback;
368 RAISE_APPLICATION_ERROR(-20000,'Error While running Insert_Time_Code_Units for: '||l_error_message);
369
370 WHEN OTHERS THEN
371 rollback;
372 RAISE_APPLICATION_ERROR(-20001,'Error while creating records into OKC_TIME_CODE_UNITS_B '||SQLERRM);
373
374 END Insert_Time_code_units;
375
376 PROCEDURE Create_Bus_Process(x_Return_status IN OUT NOCOPY Varchar2,
377 x_Return_Msg IN OUT NOCOPY Varchar2)
378 IS
379
380 l_busproc_id NUMBER;
381 l_busproc_cnt NUMBER;
382
383 BEGIN
384 x_Return_status := NULL;
385 x_Return_Msg := NULL;
386
387 SELECT count(*)
388 INTO l_busproc_cnt
389 FROM cs_business_processes
390 WHERE UPPER(name) = UPPER('SERVICE_CONTRACTS_UPGRADE_BP');
391
392 IF l_busproc_cnt <> 0
393 THEN
394 NULL;
395 ELSE
396 SELECT max(BUSINESS_PROCESS_ID)+1
397 INTO l_busproc_id
398 FROM cs_business_processes;
399
400
401 INSERT into cs_business_processes(
402 BUSINESS_PROCESS_ID ,
403 ORDER_TYPE_ID ,
404 NAME ,
405 LAST_UPDATE_DATE ,
406 LAST_UPDATED_BY ,
407 CREATION_DATE ,
408 CREATED_BY ,
409 LAST_UPDATE_LOGIN ,
410 DESCRIPTION ,
411 START_DATE_ACTIVE ,
412 END_DATE_ACTIVE ,
413 SERVICE_REQUEST_FLAG ,
414 DEPOT_REPAIR_FLAG ,
415 FIELD_SERVICE_FLAG ,
416 CONTRACTS_FLAG ,
417 STAND_ALONE_FLAG ,
418 ATTRIBUTE1 ,
419 ATTRIBUTE2 ,
420 ATTRIBUTE3 ,
421 ATTRIBUTE4 ,
422 ATTRIBUTE5 ,
423 ATTRIBUTE6 ,
424 ATTRIBUTE7 ,
425 ATTRIBUTE8 ,
426 ATTRIBUTE9 ,
427 ATTRIBUTE10 ,
428 ATTRIBUTE11 ,
429 ATTRIBUTE12 ,
430 ATTRIBUTE13 ,
431 ATTRIBUTE14 ,
432 ATTRIBUTE15 ,
433 CONTEXT )
434 values
435 (l_busproc_id ,
436 NULL ,
437 'SERVICE_CONTRACTS_UPGRADE_BP',
438 SYSDATE ,
439 0, --LAST_UPDATED_BY ??
440 SYSDATE ,
441 -1 , --CREATED_BY ??
442 0, --LAST_UPDATE_LOGIN ??
443 'Business Process for Upgrade',
444 To_Date('01/01/1900','DD/MM/YYYY'),
445 NULL , --END_DATE_ACTIVE ??
446 'Y' ,
447 'Y' ,
448 'Y' ,
449 'Y' ,
450 NULL, -- STAND_ALONE_FLAG ??
451 NULL,
452 NULL,
453 NULL,
454 NULL,
455 NULL,
456 NULL,
457 NULL,
458 NULL,
459 NULL,
460 NULL,
461 NULL,
462 NULL,
463 NULL,
464 NULL,
465 NULL,
466 NULL);
467 END IF;
468 EXCEPTION
469 WHEN OTHERS THEN
470 x_Return_Status := 'U';
471 x_Return_Msg := 'Others Exception raised whiling creating Default Business Process'||to_char(sqlcode)||'-'||sqlerrm;
472 raise_application_error(-20001,'While Creating Default Business Process:'||sqlerrm);
473 END Create_Bus_Process;
474
475 PROCEDURE create_bltype(p_billing_type IN varchar2,
476 p_txntype_id IN number,
477 x_Return_status OUT NOCOPY Varchar2,
478 x_Return_Msg OUT NOCOPY Varchar2)
479 IS
480
481 l_txnb_rec_in CS_TXNBTYPE_PVT.TXNBTYPE_REC_TYPE;
482 l_txn_billing_type_id Number;
483
484 l_api_version CONSTANT NUMBER := 1.0;
485 l_init_msg_list CONSTANT VARCHAR2(1) := 'T';
486 l_validation_level Number :=100;
487 l_commit Varchar2(2000):='F';
488 l_return_status VARCHAR2(3);
489 l_msg_count NUMBER;
490 l_msg_data VARCHAR2(2000);
491 l_return_msg VARCHAR2(2000);
492 l_msg_index_out Number;
493 l_object_version_number Number;
494
495 e_Error EXCEPTION;
496
497 BEGIN
498 l_return_status := 'S';
499 l_return_msg := NULL;
500
501 l_txnb_rec_in.billing_type :=p_billing_type;
502 l_txnb_rec_in.transaction_type_id :=p_txntype_id;
503 l_txnb_rec_in.last_update_date := sysdate;
504 l_txnb_rec_in.last_updated_by := 0;
505 l_txnb_rec_in.creation_date := sysdate;
506 l_txnb_rec_in.created_by := -1;
507 l_txnb_rec_in.last_update_login :=0;
508 l_txnb_rec_in.attribute1 :=NULL;
509 l_txnb_rec_in.attribute2 :=NULL;
510 l_txnb_rec_in.attribute3 :=NULL;
511 l_txnb_rec_in.attribute4 :=NULL;
512 l_txnb_rec_in.attribute5 :=NULL;
513 l_txnb_rec_in.attribute6 :=NULL;
514 l_txnb_rec_in.attribute7 :=NULL;
515 l_txnb_rec_in.attribute8 :=NULL;
516 l_txnb_rec_in.attribute9 :=NULL;
517 l_txnb_rec_in.attribute10 :=NULL;
518 l_txnb_rec_in.attribute11 :=NULL;
519 l_txnb_rec_in.attribute12 :=NULL;
520 l_txnb_rec_in.attribute13 :=NULL;
521 l_txnb_rec_in.attribute14 :=NULL;
522 l_txnb_rec_in.attribute15 :=NULL;
523 l_txnb_rec_in.context :=NULL;
524 l_txnb_rec_in.object_version_number :=1;
525 /*
526 CS_TXNBTYPE_PVT.INSERT_ROW(
527 p_api_version =>l_api_version,
528 p_init_msg_list =>l_init_msg_list,
529 p_validation_level =>l_validation_level,
530 p_commit =>l_commit,
531 x_return_status =>l_return_status,
532 x_msg_count =>l_msg_count,
533 x_msg_data =>l_msg_data,
534 p_txnbtype_rec =>l_txnb_rec_in,
535 x_txn_billing_type_id =>l_txn_billing_type_id,
536 x_object_version_number =>l_object_version_number);
537
538 IF nvl(l_return_status,'*') <> 'S'
539 THEN
540 IF l_msg_count > 0
541 THEN
542 FOR i in 1..l_msg_count
543 LOOP
544 fnd_msg_pub.get (p_msg_index => -1,
545 p_encoded => 'F', -- OKC$APPLICATION.GET_FALSE,
546 p_data => l_msg_data,
547 p_msg_index_out => l_msg_index_out);
548 END LOOP;
549 END IF;
550 x_return_status := l_return_status;
551 x_return_msg := l_msg_data;
552
553 RAISE e_Error;
554 END IF;
555 */
556 EXCEPTION
557 WHEN e_ERROR
558 THEN
559 x_Return_status := 'E';
560 x_Return_msg:=l_msg_data;
561 rollback;
562 WHEN OTHERS THEN
563 x_return_status := 'U';
564 x_return_msg := SQLCODE||'-'||SQLERRM;
565 raise_application_error(-20001,'While Creating Default Bill Types:'||sqlerrm);
566
567 END Create_bltype;
568
569 PROCEDURE Create_Bus_process_Txn(p_txn_type_id IN Number,
570 x_Return_status OUT NOCOPY Varchar2,
571 x_Return_Msg OUT NOCOPY Varchar2)
572 IS
573 CURSOR Cur_bus_proc IS
574 SELECT business_process_id
575 FROM CS_BUSINESS_PROCESSES
576 WHERE NAME = 'SERVICE_CONTRACTS_UPGRADE_BP';
577
578 L_ROWID varchar2(2000) :=null;
579 L_BUSINESS_PROCESS_ID NUMBER :=null;
580 L_TRANSACTION_TYPE_ID NUMBER :=null;
581 L_START_DATE_ACTIVE DATE :=null;
582 L_END_DATE_ACTIVE DATE :=null;
583 L_ATTRIBUTE1 VARCHAR2(150) :=null;
584 L_ATTRIBUTE2 VARCHAR2(150) :=null;
585 L_ATTRIBUTE3 VARCHAR2(150) :=null;
586 L_ATTRIBUTE4 VARCHAR2(150) :=null;
587 L_ATTRIBUTE5 VARCHAR2(150) :=null;
588 L_ATTRIBUTE6 VARCHAR2(150) :=null;
589 L_ATTRIBUTE7 VARCHAR2(150) :=null;
590 L_ATTRIBUTE8 VARCHAR2(150) :=null;
591 L_ATTRIBUTE9 VARCHAR2(150) :=null;
592 L_ATTRIBUTE10 VARCHAR2(150) :=null;
593 L_ATTRIBUTE11 VARCHAR2(150) :=null;
594 L_ATTRIBUTE12 VARCHAR2(150) :=null;
595 L_ATTRIBUTE13 VARCHAR2(150) :=null;
596 L_ATTRIBUTE14 VARCHAR2(150) :=null;
597 L_ATTRIBUTE15 VARCHAR2(150) :=null;
598 L_CONTEXT VARCHAR2(150) :=null;
599 L_MODE VARCHAR2(5) :=null;
600
601 BEGIN
602 OPEN Cur_bus_proc;
603 FETCH Cur_bus_proc into L_BUSINESS_PROCESS_ID;
604 CLOSE Cur_bus_proc;
605
606 L_TRANSACTION_TYPE_ID := p_txn_type_id;
607 L_MODE := 'I';
608 /*
609 CS_BUS_PROCESS_TXNS_PKG.INSERT_ROW(
610 X_ROWID => L_ROWID,
611 X_BUSINESS_PROCESS_ID => L_BUSINESS_PROCESS_ID,
612 X_TRANSACTION_TYPE_ID => L_TRANSACTION_TYPE_ID,
613 X_START_DATE_ACTIVE => L_START_DATE_ACTIVE,
614 X_END_DATE_ACTIVE => L_END_DATE_ACTIVE,
615 X_ATTRIBUTE1 => L_ATTRIBUTE1,
616 X_ATTRIBUTE2 => L_ATTRIBUTE2,
617 X_ATTRIBUTE3 => L_ATTRIBUTE3,
618 X_ATTRIBUTE4 => L_ATTRIBUTE4,
619 X_ATTRIBUTE5 => L_ATTRIBUTE5,
620 X_ATTRIBUTE6 => L_ATTRIBUTE6,
621 X_ATTRIBUTE7 => L_ATTRIBUTE7,
622 X_ATTRIBUTE8 => L_ATTRIBUTE8,
623 X_ATTRIBUTE9 => L_ATTRIBUTE9,
624 X_ATTRIBUTE10 => L_ATTRIBUTE10,
625 X_ATTRIBUTE11 => L_ATTRIBUTE11,
626 X_ATTRIBUTE12 => L_ATTRIBUTE12,
627 X_ATTRIBUTE13 => L_ATTRIBUTE13,
628 X_ATTRIBUTE14 => L_ATTRIBUTE14,
629 X_ATTRIBUTE15 => L_ATTRIBUTE15,
630 X_CONTEXT => L_CONTEXT,
631 X_MODE => L_MODE);
632 */
633 EXCEPTION
634 WHEN OTHERS THEN
635 x_return_status := 'U';
636 x_return_msg := SQLCODE||'-'||SQLERRM;
637 raise_application_error(-20001,'While Creating Default Business Process Transaction :'||sqlerrm);
638
639 END Create_Bus_process_Txn;
640
641 PROCEDURE Create_Txn_bltypes(x_Return_status OUT NOCOPY Varchar2,
642 x_Return_Msg OUT NOCOPY Varchar2)
643 IS
644
645 CURSOR Cur_Txn_type_cnt IS
646 SELECT count(*) from CS_TRANSACTION_TYPES
647 WHERE NAME='SERVICE_CONTRACTS_UPGRADE_TXN';
648
649 CURSOR Cur_Txntype_max IS
650 SELECT max(TRANSACTION_TYPE_ID)+1
651 FROM CS_TRANSACTION_TYPES;
652
653 l_txnb_rec_in CS_TXNBTYPE_PVT.TXNBTYPE_REC_TYPE;
654
655 l_txntype_id NUMBER;
656 l_txntype_cnt NUMBER;
657
658 l_api_version CONSTANT NUMBER := 1.0;
659 l_init_msg_list CONSTANT VARCHAR2(1) := 'T';
660 l_validation_level Number :=100;
661 l_commit Varchar2(2000):='F';
662 l_return_status VARCHAR2(3);
663 l_msg_count NUMBER;
664 l_msg_data VARCHAR2(2000);
665 l_return_msg VARCHAR2(2000);
666 l_msg_index_out Number;
667
668 e_Error EXCEPTION;
669
670 l_TRANSACTION_TYPE_ID NUMBER ;
671 l_SEEDED_FLAG Varchar2(2000);
672 l_NAME Varchar2(2000);
673 l_CREATION_DATE date;
674 l_CREATED_BY number ;
675 l_LAST_UPDATE_DATE date ;
676 l_LAST_UPDATED_BY number ;
677 l_LAST_UPDATE_LOGIN number ;
678 L_ROWID varchar2(2000) :=null;
679 L_REVISION_FLAG varchar2(2000) := null;
680 L_END_DATE_ACTIVE date := null;
681 L_START_DATE_ACTIVE Date := null;
682 l_ATTRIBUTE1 varchar2(2000):=null;
683 l_ATTRIBUTE2 varchar2(2000):=null;
684 l_ATTRIBUTE3 varchar2(2000):=null;
685 l_ATTRIBUTE4 varchar2(2000):=null;
686 l_ATTRIBUTE5 varchar2(2000):=null;
687 l_ATTRIBUTE6 varchar2(2000):=null;
688 l_ATTRIBUTE7 varchar2(2000):=null;
689 l_ATTRIBUTE8 varchar2(2000):=null;
690 l_ATTRIBUTE9 varchar2(2000):=null;
691 l_ATTRIBUTE10 varchar2(2000):=null;
692 l_CONTEXT varchar2(2000):=null;
693 l_INSTALLED_CP_STATUS_ID number := null;
694 l_ATTRIBUTE11 varchar2(2000):=null;
695 l_ATTRIBUTE12 varchar2(2000):=null;
696 l_ATTRIBUTE13 varchar2(2000):=null;
697 l_ATTRIBUTE14 varchar2(2000):=null;
698 l_ATTRIBUTE15 varchar2(2000):=null;
699 l_INSTALLED_STATUS_CODE varchar2(2000) := null;
700 l_INSTALLED_CP_RETURN_REQUIRED varchar2(2000) := null;
701 l_NO_CHARGE_FLAG varchar2(2000) := NULL;
702 l_DEPOT_REPAIR_FLAG varchar2(2000) := null;
703 l_NEW_CP_STATUS_ID number := null;
704 L_NEW_CP_STATUS_CODE varchar2(2000) := null;
705 L_TRANSFER_SERVICE Varchar2(2000) := null;
706 L_NEW_CP_RETURN_REQUIRED varchar2(2000):=null;
707 L_DESCRIPTION Varchar2(2000) := null;
708 L_MOVE_COUNTERS_FLAG varchar2(2000) := null;
709 L_OBJECT_VERSION_NUMBER Number := 1;
710 L_BLTYPE Varchar2(10) := NULL;
711
712 BEGIN
713 x_Return_status := 'S';
714 x_return_msg := null;
715
716 OPEN Cur_Txn_type_cnt;
717 FETCH Cur_Txn_type_cnt into l_txntype_cnt;
718 CLOSE Cur_Txn_type_cnt;
719
720 IF l_txntype_cnt <> 0
721 THEN
722 NULL;
723 ELSE
724 OPEN Cur_txntype_max;
725 FETCH Cur_txntype_max into l_txntype_id;
726 CLOSE Cur_txntype_max;
727
728 l_SEEDED_FLAG := 'N';
729 l_NAME := 'SERVICE_CONTRACTS_UPGRADE_TXN';
730 L_DESCRIPTION := 'Transaction Type created for Upgrade';
731 l_CREATION_DATE := sysdate;
732 l_CREATED_BY := 1;
733 l_LAST_UPDATE_DATE := sysdate;
734 l_LAST_UPDATED_BY :=1;
735 l_LAST_UPDATE_LOGIN :=0;
736 /*
737
738 CS_TRANSACTION_TYPES_PKG.INSERT_ROW (
739 X_ROWID =>L_ROWID ,
740 X_TRANSACTION_TYPE_ID => l_TXNTYPE_ID,
741 X_SEEDED_FLAG => L_SEEDED_FLAG ,
742 X_REVISION_FLAG => L_REVISION_FLAG ,
743 X_END_DATE_ACTIVE => L_END_DATE_ACTIVE ,
744 X_START_DATE_ACTIVE => L_START_DATE_ACTIVE ,
745 X_ATTRIBUTE1 =>l_ATTRIBUTE1,
746 X_ATTRIBUTE2 =>l_ATTRIBUTE2,
747 X_ATTRIBUTE3 =>l_ATTRIBUTE3,
748 X_ATTRIBUTE4 =>l_ATTRIBUTE4,
749 X_ATTRIBUTE5 =>l_ATTRIBUTE5,
750 X_ATTRIBUTE6 =>l_ATTRIBUTE6,
751 X_ATTRIBUTE7 =>l_ATTRIBUTE7,
752 X_ATTRIBUTE8 =>l_ATTRIBUTE8,
753 X_ATTRIBUTE9 =>l_ATTRIBUTE9,
754 X_ATTRIBUTE10 =>l_ATTRIBUTE10,
755 X_CONTEXT => l_CONTEXT,
756 X_INSTALLED_CP_STATUS_ID => l_INSTALLED_CP_STATUS_ID,
757 X_ATTRIBUTE11 =>l_ATTRIBUTE11,
758 X_ATTRIBUTE12 =>l_ATTRIBUTE12,
759 X_ATTRIBUTE13 =>l_ATTRIBUTE13,
760 X_ATTRIBUTE14 =>l_ATTRIBUTE14,
761 X_ATTRIBUTE15 =>l_ATTRIBUTE15,
762 X_INSTALLED_STATUS_CODE => l_INSTALLED_STATUS_CODE,
763 X_INSTALLED_CP_RETURN_REQUIRED =>l_INSTALLED_CP_RETURN_REQUIRED,
764 X_NO_CHARGE_FLAG =>l_NO_CHARGE_FLAG ,
765 X_DEPOT_REPAIR_FLAG =>l_DEPOT_REPAIR_FLAG,
766 X_NEW_CP_STATUS_ID =>l_NEW_CP_STATUS_ID,
767 X_NEW_CP_STATUS_CODE =>L_NEW_CP_STATUS_CODE,
768 X_TRANSFER_SERVICE =>L_TRANSFER_SERVICE ,
769 X_NEW_CP_RETURN_REQUIRED =>L_NEW_CP_RETURN_REQUIRED,
770 X_NAME =>L_NAME ,
771 X_DESCRIPTION =>L_DESCRIPTION ,
772 X_CREATION_DATE =>L_CREATION_DATE,
773 X_CREATED_BY =>L_CREATED_BY ,
774 X_LAST_UPDATE_DATE =>L_LAST_UPDATE_DATE,
775 X_LAST_UPDATED_BY =>L_LAST_UPDATED_BY,
776 X_LAST_UPDATE_LOGIN =>L_LAST_UPDATE_LOGIN ,
777 X_MOVE_COUNTERS_FLAG =>L_MOVE_COUNTERS_FLAG,
778 X_OBJECT_VERSION_NUMBER=>L_OBJECT_VERSION_NUMBER);
779 */
780 Create_Bus_process_Txn(l_txntype_id,
781 l_Return_status,
782 l_Return_Msg);
783 IF l_return_status <> 'S'
784 THEN
785 l_return_msg := 'Error while creating Bus_process_Txn :'||l_Return_Msg;
786 RAISE e_Error;
787 END IF;
788 -- Call this API for 'M,'L','E'
789 l_bltype := 'M';
790 Create_bltype(p_billing_type => l_bltype,
791 p_txntype_id => l_txntype_id,
792 x_Return_status=> l_return_status,
793 x_Return_Msg => l_return_msg);
794
795 IF l_return_status <>'S'
796 THEN
797 raise e_Error;
798 END IF;
799
800 l_bltype := 'L';
801 Create_bltype(p_billing_type => l_bltype,
802 p_txntype_id => l_txntype_id,
803 x_Return_status=> l_return_status,
804 x_Return_Msg => l_return_msg);
805 IF l_return_status <>'S'
806 THEN
807 raise e_Error;
808 END IF;
809
810 l_bltype := 'E';
811 Create_bltype(p_billing_type => l_bltype,
812 p_txntype_id => l_txntype_id,
813 x_Return_status=> l_return_status,
814 x_Return_Msg => l_return_msg);
815 IF l_return_status <>'S'
816 THEN
817 raise e_Error;
818 END IF;
819 END IF;
820
821 EXCEPTION
822 WHEN e_ERROR
823 THEN
824 x_Return_status := 'E';
825 x_Return_msg:=l_return_msg;
826 rollback;
827 WHEN OTHERS THEN
828 x_return_status := 'U';
829 x_return_msg := SQLCODE||'-'||SQLERRM;
830 raise_application_error(-20001,'While Creating Default Txn Billing Types :'||sqlerrm);
831
832 END Create_Txn_bltypes;
833
834
835 PROCEDURE Drive_Upg_Check IS
836
837 l_return_status Varchar2(3) := NULL;
838 l_return_msg Varchar2(2000):= NULL;
839 l_exist_yn Varchar2(1);
840
841 cursor Upg_Check_cur is
842 select 'Y' from cs_cp_services_all
843 where rownum < 2;
844
845 BEGIN
846
847 Open Upg_Check_Cur;
848 Fetch Upg_Check_Cur into l_exist_yn;
849
850 if Upg_Check_Cur%notfound then
851 l_exist_yn := 'N';
852 end if;
853 close Upg_Check_Cur;
854
855 if l_exist_yn = 'Y' then
856 Insert_Time_code_units;
857
858 Create_Index;
859 end if;
860 END drive_upg_check;
861
862 PROCEDURE Update_status IS
863
864 Cursor Cur_hdr_status IS
865 select old_stat.name old_status
866 from okc_k_headers_v kh ,
867 cs_contracts_all csc,
868 cs_contract_statuses old_stat
869 where csc.contract_status_id = old_stat.contract_status_id
870 and csc.contract_id = kh.id
871 and upper(kh.sts_code) <> upper(old_stat.name)
872 and exists (select b.code from okc_statuses_v b
873 where upper(old_stat.name) = upper(b.code));
874
875 Cursor Cur_line_status IS
876 select old_stat.name old_status
877 from okc_k_lines_v kl ,
878 cs_cp_services_all ccs,
879 cs_contract_statuses old_stat
880 where ccs.contract_line_status_id = old_stat.contract_status_id
881 and ccs.cp_service_id = kl.id
882 and upper(kl.sts_code) <> upper(old_stat.name)
883 and exists (select b.code from okc_statuses_v b
884 where upper(old_stat.name) = upper(b.code));
885
886 BEGIN
887
888 FOR status_rec IN cur_hdr_status
889 LOOP
890
891 UPDATE OKC_K_HEADERS_B
892 SET STS_CODE = UPPER(status_rec.old_status)
893 WHERE id in (select cc.contract_id
894 FROM cs_contracts_all cc,
895 cs_contract_statuses stat
896 WHERE cc.contract_status_id = stat.contract_status_id
897 AND stat.name = status_rec.old_status);
898
899 END LOOP;
900
901 FOR status_rec IN cur_line_status
902 LOOP
903
904 UPDATE OKC_K_LINES_B
905 SET STS_CODE = UPPER(status_rec.old_status)
906 WHERE id in (select cs.cp_service_id
907 FROM cs_cp_services_all cs,
908 cs_contract_statuses stat
909 WHERE cs.contract_line_status_id = stat.contract_status_id
910 AND stat.name = status_rec.old_status);
911
912 END LOOP;
913 EXCEPTION when others then
914 raise_application_error(-20000,'While updating STATUS :'||sqlerrm);
915 END Update_status;
916
917
918 PROCEDURE Update_time_zone IS
919
920 CURSOR Cur_tz IS
921 SELECT distinct dnz_chr_id
922 FROM CS_COVERAGE_TXN_GROUPS ctxg,
923 OKC_K_LINES_B cle
924 WHERE cle.upg_Orig_System_Ref='CS_COVERAGE_TXN_GROUPS'
925 AND cle.upg_Orig_System_Ref_Id = ctxg.Coverage_txn_Group_id
926 AND ctxg.Time_Zone_Id IS NULL;
927
928 l_cov_timezone_id Number;
929
930 BEGIN
931
932 --l_cov_timezone_id := FND_PROFILE.value('OKS_COV_DEFAULT_TIMEZONE');
933 l_cov_timezone_id := nvl(FND_PROFILE.value('CS_UPG_CONTRACTS_TIMEZONE'),47);
934
935 -- TVCP Migration 31-OCT-2003 MCHOUDHA
936 --updating the new OKS table OKS_COVERAGE_TIMEZONES with the
937 --value of the profile option
938
939 UPDATE OKS_COVERAGE_TIMEZONES
940 SET TIMEZONE_ID = l_cov_timezone_id --tz_rec.timezone_id
941 WHERE timezone_id = nvl(FND_PROFILE.value('CS_UPG_CONTRACTS_TIMEZONE'),47)
942 AND dnz_chr_id IN --= tz_rec.chr_id
943 ( SELECT distinct dnz_chr_id
944 FROM CS_COVERAGE_TXN_GROUPS ctxg,
945 OKC_K_LINES_B cle
946 WHERE cle.upg_Orig_System_Ref='CS_COVERAGE_TXN_GROUPS'
947 AND cle.upg_Orig_System_Ref_Id = ctxg.Coverage_txn_Group_id
948 AND ctxg.Time_Zone_Id IS NULL);
949
950 -- End Changes TVCP Migration 31-OCT-2003 MCHOUDHA
951
952 EXCEPTION when others then
953 raise_application_error(-20000,'While updating TIME ZONE :'||sqlerrm);
954
955 END Update_time_zone;
956
957 Procedure Create_Index Is
958
959 l_string varchar2(5000) := NULL;
960 l_var integer;
961 rc integer;
962 l_index varchar2(150);
963 l_index_name VARCHAR2(200);
964
965 cursor get_global_schema is
966 select oracle_username
967 from fnd_oracle_userid
968 where read_only_flag = 'U';
969
970 cursor get_cov_sch_index(c_owner in varchar2) is
971 Select index_name
972 From all_indexes
973 Where index_name like 'OKC_K_LINES_B_UPG'
974 and owner = c_owner;
975
976 Cursor get_mtl_index(c_owner in varchar2) is
977 Select index_name
978 From all_indexes
979 Where index_name like 'COV_SCH_ID_N10'
980 and owner = c_owner;
981
982 l_owner varchar2(100);
983
984 BEGIN
985
986 open get_global_schema;
987 fetch get_global_schema into l_owner;
988 close get_global_schema;
989
990 open get_mtl_index(c_owner => l_owner);
991 fetch get_mtl_index into l_index_name;
992 if(get_mtl_index%notfound) then
993
994 l_string := 'CREATE INDEX COV_SCH_ID_N10 ON MTL_SYSTEM_ITEMS_B(COVERAGE_SCHEDULE_ID)';
995
996 BEGIN
997
998 l_var := dbms_sql.open_cursor;
999 dbms_sql.parse(l_var ,l_string,dbms_sql.native);
1000 rc := dbms_sql.execute(l_var);
1001 dbms_sql.close_cursor(l_var);
1002 END;
1003
1004
1005 End if;
1006 close get_mtl_index;
1007
1008 FOR get_cov_sch_index_rec in get_cov_sch_index(c_owner => l_owner) LOOP
1009 l_index := get_cov_sch_index_rec.index_name;
1010 END LOOP;
1011
1012 if l_index IS NULL then
1013
1014 l_string := 'create index okc_k_lines_b_upg on okc_k_lines_b(upg_orig_system_ref,upg_orig_system_ref_id)';
1015
1016 BEGIN
1017 l_var := dbms_sql.open_cursor;
1018 dbms_sql.parse(l_var ,l_string,dbms_sql.native);
1019 rc := dbms_sql.execute(l_var);
1020 dbms_sql.close_cursor(l_var);
1021 END;
1022
1023 end if;
1024
1025 EXCEPTION
1026 WHEN OTHERS THEN
1027 ROLLBACK;
1028 RAISE_APPLICATION_ERROR(-20000,
1029 'Error In Procedure Create_Index '||SQLERRM);
1030 END Create_Index;
1031
1032 END OKS_UPGRADE_PRECHECK;
1033