[Home] [Help]
PACKAGE BODY: APPS.OKI_DBI_LOAD_CLEB_PVT
Source
1 PACKAGE BODY OKI_DBI_LOAD_CLEB_PVT AS
2 /* $Header: OKIRILEB.pls 120.28 2008/01/10 19:08:30 syeddana ship $ */
3
4 g_debug BOOLEAN;
5 g_4712_date date;
6 g_9999_date date;
7 g_0001_date date;
8 g_euro_start_date date;
9 g_global_start_date DATE;
10 g_no_of_workers NUMBER;
11 g_true_incr NUMBER;
12 g_del_count NUMBER;
13 g_contracts_count NUMBER;
14 g_batch_size NUMBER;
15 g_renewal_id number;
16 g_ren_con_id number;
17
18 TYPE WorkerList is table of NUMBER index by binary_integer;
19 g_worker WorkerList;
20
21 G_CHILD_PROCESS_ISSUE EXCEPTION;
22
23 /* *****************************************************************************
24 Procedure:rlog
25 Description:Procedure to write messages to the log file
26 Parameters: p_string : The message to be written onto the log
27 p_indent : Indentation of the message
28 ************************************************************************** */
29 PROCEDURE rlog ( p_string IN VARCHAR2, p_indent IN NUMBER ) IS
30 BEGIN
31 BIS_COLLECTION_UTILITIES.log(p_string,p_indent);
32 EXCEPTION
33 WHEN OTHERS THEN
34 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
35 fnd_message.set_name( application => 'FND'
36 , name => 'CRM-DEBUG ERROR' ) ;
37 fnd_message.set_token( token => 'ROUTINE'
38 , value => 'OKI_DBI_LOAD_CLEB_PVT.log' ) ;
39 bis_collection_utilities.put_line(fnd_message.get) ;
40 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
41 END rlog;
42
43 /* *****************************************************************************
44 Procedure:rout
45 Description:Procedure to write messages to the output file
46 Parameters: p_string : The message to be written onto the log
47 p_indent : Indentation of the message
48 ************************************************************************** */
49
50 PROCEDURE rout ( p_string IN VARCHAR2, p_indent IN NUMBER ) IS
51 BEGIN
52 BIS_COLLECTION_UTILITIES.out(p_string,p_indent);
53 EXCEPTION
54 WHEN OTHERS THEN
55 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
56 fnd_message.set_name( application => 'FND'
57 , name => 'CRM-DEBUG ERROR' ) ;
58 fnd_message.set_token( token => 'ROUTINE'
59 , value => 'OKI_DBI_LOAD_CLEB_PVT.out ' ) ;
60 bis_collection_utilities.put_line(fnd_message.get) ;
61 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
62 END rout;
63
64 /* *****************************************************************************
65 Procedure:gather_table_stats
66 Description:Procedure to gather table stats
67 Parameters: tabname : The name of the table/mv on which statistics should be gathered
68 ************************************************************************** */
69 PROCEDURE gather_table_stats (tabname varchar2)
70 IS
71
72 l_sql_string VARCHAR2(2000);
73 BEGIN
74 FND_STATS.GATHER_TABLE_STATS( OWNNAME=>'OKI' ,
75 TABNAME=>tabname );
76
77 l_sql_string := 'alter session enable parallel dml' ;
78 EXECUTE IMMEDIATE l_sql_string ;
79
80 EXCEPTION
81 WHEN OTHERS THEN
82 rlog( 'Unexpected Error while gathering statistics on '|| tabname ||' table :'|| SQLERRM, 2 );
83 END;
84 /* *****************************************************************************
85 Procedure:Initial_load
86 Description:Performs the inital steps like altering hash and sort areas before calling
87 populate_base_tables. This is called during initial load
88 Parameters:
89 errbuf -Mandatory out parameter containing error message to be passed back to the concurrent manager
90 retcode-Mandatory out parameter containing the Oracle error number to be passed back to the concurrent manager
91 p_startdate - user entered from date
92 p_end_date - user entered to date
93 ************************************************************************** */
94
95 PROCEDURE initial_load(
96 errbuf OUT NOCOPY VARCHAR2,
97 retcode OUT NOCOPY VARCHAR2,
98 p_start_date IN VARCHAR2,
99 p_end_date IN VARCHAR2
100 ) IS
101 l_sql_string VARCHAR2(2000);
102 l_setup_ok BOOLEAN ;
103 l_run_date DATE;
104 BEGIN
105
106 l_setup_ok := FALSE;
107 g_4712_date := to_date('01/01/4712' , 'MM/DD/YYYY');
108 g_9999_date := to_date('12-31-9999' ,'MM-DD-YYYY');
109 g_0001_date := to_date('01-01-0001' ,'MM-DD-YYYY');
110 g_euro_start_date := to_date('01/01/1999','MM/DD/RRRR');
111 l_run_date := SYSDATE;
112 l_setup_ok := BIS_COLLECTION_UTILITIES.setup('OKIDBICLEB');
113 IF (NOT l_setup_ok) THEN
114 errbuf := fnd_message.get;
115 rlog( 'BIS Setup Failure ',0);
116 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
117 END IF;
118
119 rlog( 'Service Contracts Intelligence - INITIAL LOAD: ' ||
120 fnd_date.date_to_displayDT(sysdate),0);
121 rlog( 'Parameter : Start date '|| fnd_date.date_to_displayDT(to_date(p_start_date,'yyyy/mm/dd hh24:mi:ss')), 1);
122 rlog( 'Parameter : End date '|| fnd_date.date_to_displayDT(to_date(p_end_date,'yyyy/mm/dd hh24:mi:ss')), 1);
123
124 l_sql_string := 'alter session set hash_area_size=100000000';
125 EXECUTE IMMEDIATE l_sql_string;
126 l_sql_string := 'alter session set sort_area_size=100000000';
127 EXECUTE IMMEDIATE l_sql_string;
128 -- Lesters feedback 5/19/04
129 l_sql_string := 'alter session set workarea_size_policy = manual';
130 EXECUTE IMMEDIATE l_sql_string;
131
132 OKI_DBI_LOAD_CLEB_PVT.g_load_type := 'INITIAL LOAD';
133 rlog( 'Resetting base tables and BIS log file ' ||
134 fnd_date.date_to_displayDT(sysdate),1);
135 reset_base_tables(errbuf, retcode);
136
137 IF(retcode = 0 )
138 THEN
139 populate_base_tables(errbuf,
140 retcode,
141 p_start_date,
142 p_end_date,
143 1);
144 END IF;
145
146 rlog( 'DONE : Initial Load Successfully completed ' ||
147 fnd_date.date_to_displayDT(sysdate),0);
148 DECLARE
149 l_days NUMBER;
150 l_hours NUMBER;
151 l_minutes NUMBER;
152 l_seconds NUMBER;
153 l_date TIMESTAMP;
154 l_Str VARCHAR2(1000);
155 BEGIN
156 l_date := TO_TIMESTAMP(SYSDATE);
157 l_days := EXTRACT (day FROM l_date - l_run_date);
158 l_hours := EXTRACT (hour FROM l_date - l_run_date);
159 l_minutes := EXTRACT (minute FROM l_date - l_run_date);
160 l_seconds := EXTRACT (second FROM l_date - l_run_date);
161 l_str := 'Load Successfully Completed in ';
162 IF ( l_days <> 0 ) THEN
163 l_str := l_str || l_days || ' Days ';
164 END IF;
165 IF ( l_hours <> 0 ) THEN
166 l_str := l_str || l_hours || ' Hours ';
167 END IF;
168 if ( l_minutes <> 0 ) THEN
169 l_str := l_str || l_minutes || ' Minutes ';
170 END IF;
171 if ( l_seconds <> 0 ) THEN
172 l_str := l_str || l_seconds || ' Seconds ';
173 END IF;
174 rlog (l_str,0);
175 EXCEPTION
176 WHEN OTHERS THEN
177 rlog('Unable to calculate load ran time ', 0);
178 END;
179 EXCEPTION
180 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
181 RAISE;
182 WHEN OTHERS THEN
183 errbuf := SQLERRM ;
184 retcode := SQLCODE ;
185 bis_collection_utilities.put_line(errbuf || '' || retcode ) ;
186 fnd_message.set_name( application => 'FND'
187 , name => 'CRM-DEBUG ERROR' ) ;
188 fnd_message.set_token(
189 token => 'ROUTINE'
190 , value => 'OKI_DBI_LOAD_CLEB_PVT.INITIAL_LOAD ' ) ;
191 bis_collection_utilities.put_line(fnd_message.get) ;
192 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
193 END initial_load;
194
195 /* *****************************************************************************
196 Procedure:reset_base_tables
197 Description:Procedure to reset OKI DBI table
198 Parameters:
199 errbuf: The error message to be passed back to caller
200 retcode: Oracle error number
201 ************************************************************************** */
202 PROCEDURE reset_base_tables (
203 errbuf OUT NOCOPY VARCHAR2,
204 retcode OUT NOCOPY VARCHAR2
205 ) IS
206
207 l_sql_string VARCHAR2(4000);
208 l_oki_schema VARCHAR2(30);
209 l_status VARCHAR2(30);
210 l_industry VARCHAR2(30);
211
212 BEGIN
213 retcode := 0;
214
215 IF (FND_INSTALLATION.GET_APP_INFO('OKI', l_status, l_industry, l_oki_schema)) THEN
216
217 l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema || '.OKI_DBI_CHR_STAGE_INC' ;
218 EXECUTE IMMEDIATE l_sql_string ;
219 rlog( 'Truncated Table OKI_DBI_CHR_STAGE_INC',2);
220
221 l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema || '.OKI_DBI_CHR_INC' ;
222 EXECUTE IMMEDIATE l_sql_string ;
223 rlog( 'Truncated Table OKI_DBI_CHR_INC',2);
224
225 l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema ||'.OKI_DBI_CURR_CONV';
226 EXECUTE IMMEDIATE l_sql_string;
227 rlog( 'Truncated table OKI_DBI_CURR_CONV' ,2);
228
229 l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema ||'.OKI_DBI_CLE_B_OLD';
230 EXECUTE IMMEDIATE l_sql_string;
231 rlog( 'Truncated Table OKI_DBI_CLE_B_OLD' ,2);
232
233 -- RSG is now dropping the MV log before doing the initial load
234 -- l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema ||'.MLOG$_OKI_DBI_CLE_B';
235 -- l_sql_string := 'TRUNCATE TABLE MLOG$_OKI_DBI_CLE_B';
236 -- EXECUTE IMMEDIATE l_sql_string;
237 -- rlog( 'Base table LOG MLOG_OKI_DBI_CLE_B was truncated ' ,2);
238
239 l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema ||'.OKI_DBI_CLE_B';
240 EXECUTE IMMEDIATE l_sql_string;
241 rlog( 'Truncated Table OKI_DBI_CLE_B' ,2);
242
243 BIS_COLLECTION_UTILITIES.DeleteLogForObject('OKIDBICLEB');
244 rlog( 'Completed resetting base tables and BIS log file ' || fnd_date.date_to_displayDT(sysdate),1);
245 END IF;
246
247 EXCEPTION
248 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
249 RAISE;
250 WHEN OTHERS
251 THEN
252 errbuf := sqlerrm;
253 retcode := sqlcode;
254 bis_collection_utilities.put_line(errbuf || '' || retcode ) ;
255 fnd_message.set_name( application => 'FND'
256 , name => 'CRM-DEBUG ERROR' ) ;
257 fnd_message.set_token(
258 token => 'ROUTINE'
259 , value => 'OKI_DBI_LOAD_CLEB_PVT.RESET_BASE_TABLES ' ) ;
260 bis_collection_utilities.put_line(fnd_message.get) ;
261 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
262 END reset_base_tables;
263
264 /* *****************************************************************************
265 Procedure:populate_base_tables
266 Description:This procedure calls different procedures based on the type of load
267 Parameters:
268 errbuf: The error message to be passed back to caller
269 retcode: Oracle error number
270 p_start_date: User entered from date
271 p_end_date: User entered to date
272 p_no_of_workers: User entered number of sub workers ( for incremental load )
273 ************************************************************************** */
274
275 PROCEDURE populate_base_tables (
276 errbuf OUT NOCOPY VARCHAR2,
277 retcode OUT NOCOPY VARCHAR2,
278 p_start_date IN VARCHAR2,
279 p_end_date IN VARCHAR2,
280 p_no_of_workers IN NUMBER
281 ) IS
282 l_start_date DATE;
283 l_end_date DATE;
284 l_run_date DATE;
285 l_setup_ok BOOLEAN;
286 l_count NUMBER ;
287 l_ren_count NUMBER;
288 l_max_ren_batch NUMBER;
289 l_recs_processed NUMBER;
290 l_missing_flag NUMBER;
291 l_exception exception;
292 BEGIN
293 l_setup_ok := FALSE;
294 l_count := 0 ;
295 l_ren_count := 0;
296 l_max_ren_batch := 0;
297 l_recs_processed := 0 ;
298 l_missing_flag := 0;
299
300 g_true_incr := 0;
301 g_4712_date := to_date('01/01/4712' , 'MM/DD/YYYY');
302 g_9999_date := to_date('12-31-9999' ,'MM-DD-YYYY');
303 g_0001_date := to_date('01-01-0001' ,'MM-DD-YYYY');
304 g_euro_start_date := to_date('01/01/1999','MM/DD/RRRR');
305
306 l_run_date := sysdate;
307 g_global_start_date := bis_common_parameters.get_global_start_date;
308 g_no_of_workers := p_no_of_workers;
309
310 IF( NVL(OKI_DBI_LOAD_CLEB_PVT.g_load_type,'INC LOAD') <> 'INITIAL LOAD')
311 THEN
312 l_setup_ok := BIS_COLLECTION_UTILITIES.setup('OKIDBICLEB');
313 IF (NOT l_setup_ok) THEN
314 errbuf := fnd_message.get;
315 rlog( 'BIS Setup Failure ',0);
316 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
317 END IF;
318
319 rlog( 'Service Contracts Intelligence - INCREMENTAL LOAD ' || fnd_date.date_to_displayDT(sysdate),0);
320 rlog( 'Number of workers requested : ' || g_no_of_workers,0);
321 END IF;
322
323 g_debug := BIS_COLLECTION_UTILITIES.G_DEBUG;
324
325 -- Check to see if User entered the start date for incremental load as param.
326 -- if not default to previous last refresh of load date.(from bis_refresh_log)
327
328 IF( NVL(OKI_DBI_LOAD_CLEB_PVT.g_load_type,'INC LOAD') <> 'INITIAL LOAD')
329 THEN
330 IF(p_start_date IS NULL )
331 THEN
332 l_start_date := fnd_date.displaydt_to_date(BIS_COLLECTION_UTILITIES.get_last_refresh_period('OKIDBICLEB'))
333 - 0.004;
334 g_true_incr :=1;
335 ELSE
336 l_start_date := to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS') - 0.004;
337 END IF;
338 ELSE
339 l_start_date := to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS') ;
340 END IF;
341
342 -- End date for load is defaulted to sysdate/ run_date
343 IF(p_end_date is NULL)
344 THEN
345 l_end_date := l_run_date;
346 ELSE
347 l_end_date := to_date(p_end_date,'YYYY/MM/DD HH24:MI:SS');
348 END IF;
349
350 OKI_DBI_LOAD_CLEB_PVT.g_start_date := l_start_date;
351 OKI_DBI_LOAD_CLEB_PVT.g_end_date := l_end_date;
352 OKI_DBI_LOAD_CLEB_PVT.g_run_date := l_run_date;
353
354 IF( NVL(OKI_DBI_LOAD_CLEB_PVT.g_load_type,'INC LOAD') <> 'INITIAL LOAD')
355 THEN
356 rlog( 'Start date - '|| fnd_date.date_to_displayDT(l_start_date),1);
357 rlog( 'End date - '|| fnd_date.date_to_displayDT(l_end_date),1);
358 rlog( 'Processing Deletes ' || fnd_date.date_to_displayDT(sysdate),1);
359 OKI_DBI_LOAD_CLEB_PVT.process_deletes;
360 OKI_DBI_LOAD_CLEB_PVT.populate_inc_table_inc;
361 ELSE
362 OKI_DBI_LOAD_CLEB_PVT.populate_inc_table_init;
363 END IF;
364
365 rlog( 'Populating Currencies Table OKI_DBI_CURR_CONV - ' || fnd_date.date_to_displayDT(sysdate),1);
366 OKI_DBI_LOAD_CLEB_PVT.load_currencies;
367 rlog( 'Load of Currencies Table OKI_DBI_CURR_CONV completed - ' || fnd_date.date_to_displayDT(sysdate),1);
368
369 rlog( 'Checking negative rates in Table OKI_DBI_CURR_CONV - ' ||
370 fnd_date.date_to_displayDT(sysdate),1);
371 SELECT COUNT(1) INTO l_missing_flag
372 FROM oki_dbi_curr_conv
373 --WHERE upper(rate_type) <> 'USER'
374 WHERE (rate_f <= 0 OR rate_g < 0 OR rate_sg < 0 OR rate_f is NULL);
375
376
377 IF(nvl(l_missing_flag,0) > 0) -- There are missing currencies
378 THEN
379 rlog( 'Reporting Missing Currencies ' || fnd_date.date_to_displayDT(sysdate),1);
380 OKI_DBI_LOAD_CLEB_PVT.report_missing_currencies;
381
382 errbuf := 'There are missing currencies';
383 rlog( 'ERROR : Missing Currencies, view output file for details',0);
384 retcode := 2;
385 RAISE_APPLICATION_ERROR (-20000, 'Error in INC table collection: ' || errbuf);
386 COMMIT ;
387 END IF ;
388
389 IF( OKI_DBI_LOAD_CLEB_PVT.g_load_type = 'INITIAL LOAD')
390 THEN
391 OKI_DBI_LOAD_CLEB_PVT.direct_load(l_recs_processed);
392 ELSE
393
394 IF ( g_contracts_count > 0 OR g_del_count > 0 ) THEN
395 OKI_DBI_LOAD_CLEB_PVT.incr_load(l_recs_processed);
396 ELSE
397 rlog ('No contracts are identified for update/delete',1);
398 END IF;
399
400 END IF;
401 l_count := l_count + l_recs_processed ;
402
403 IF l_count IS NULL THEN
404 l_count := 0;
405 END IF;
406
407 BIS_COLLECTION_UTILITIES.wrapup(TRUE,
408 l_count,
409 'OKI DBI COV LINES COLLECTION SUCCEEDED',
410 OKI_DBI_LOAD_CLEB_PVT.g_start_date,
411 OKI_DBI_LOAD_CLEB_PVT.g_end_date
412 );
413
414 IF( NVL(OKI_DBI_LOAD_CLEB_PVT.g_load_type,'INC LOAD') <> 'INITIAL LOAD') THEN
415 rlog('SUCCESS: Load Program Successfully completed ' || fnd_date.date_to_displayDT(SYSDATE),0);
416 DECLARE
417 l_days NUMBER;
418 l_hours NUMBER;
419 l_minutes NUMBER;
420 l_seconds NUMBER;
421 l_date TIMESTAMP;
422 l_str VARCHAR2(1000);
423 BEGIN
424 l_date := TO_TIMESTAMP(SYSDATE);
425 l_days := EXTRACT (day FROM l_date - l_run_date);
426 l_hours := EXTRACT (hour FROM l_date - l_run_date);
427 l_minutes := EXTRACT (minute FROM l_date - l_run_date);
428 l_seconds := EXTRACT (second FROM l_date - l_run_date);
429 l_str := 'Load Completed in ';
430 IF ( l_days <> 0 ) THEN
431 l_str := l_str || l_days || ' Days ';
432 ELSif l_days =1 then
433 l_str := l_str || l_days || ' Day ';
434 END IF;
435 IF ( l_hours <> 0 ) THEN
436 l_str := l_str || l_hours || ' Hours ';
437 elsIF ( l_hours =1 ) THEN
438 l_str := l_str || l_hours || ' Hour ';
439 END IF;
440 IF ( l_minutes <> 0 ) THEN
441 l_str := l_str || l_minutes || ' Minutes ';
442 ELSIF ( l_minutes = 1 ) THEN
443 l_str := l_str || l_minutes || ' Minute ';
444 END IF;
445 IF ( l_seconds <> 0 ) THEN
446 l_str := l_str || l_seconds || ' Seconds ';
447 ELSIF ( l_seconds =1 ) THEN
448 l_str := l_str || l_seconds || ' Second ';
449 END IF;
450
451 rlog (l_str,0);
452 EXCEPTION
453 WHEN OTHERS THEN
454 rlog('Unable to calculate load running time ', 0);
455 END;
456 END IF;
457
458 EXCEPTION
459 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
460 RAISE;
461 WHEN OTHERS
462 THEN
463 retcode := SQLCODE ;
464 errbuf := SQLERRM ;
465 BIS_COLLECTION_UTILITIES.wrapup(FALSE,
466 l_count,
467 errbuf || ': ' || retcode,
468 OKI_DBI_LOAD_CLEB_PVT.g_start_date,
469 OKI_DBI_LOAD_CLEB_PVT.g_end_date
470 ) ;
471 fnd_message.set_name( application => 'FND'
472 , name => 'CRM-DEBUG ERROR' ) ;
473 fnd_message.set_token(
474 token => 'ROUTINE'
475 , value => 'OKI_DBI_LOAD_CLEB_PVT.populate_base_tables ' ) ;
476 bis_collection_utilities.put_line(fnd_message.get) ;
477 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
478 END populate_base_tables;
479
480 /* *****************************************************************************
481 Procedure:populate_inc_table_init
482 Description:Procedure to populate incremental table for Initial load.
483 Check okc_k_vers_numbers table for new/modified contracts
484 ************************************************************************** */
485
486 PROCEDURE populate_inc_table_init IS
487 l_start_date DATE ;
488 l_end_date DATE ;
489 l_batch_size NUMBER ;
490 l_sql_string VARCHAR2(10000) ;
491
492 l_oki_schema VARCHAR2(30) ;
493 l_status VARCHAR2(30) ;
494 l_industry VARCHAR2(30) ;
495 l_count NUMBER ;
496 l_count2 NUMBER;
497 l_global_curr VARCHAR(30);
498 --Added by Arun for secondary global currency conversion
499 l_sglobal_curr VARCHAR(30);
500 l_conv_type VARCHAR2(40);
501 l_salesperson_code VARCHAR2(80) ;
502 l_sysdate DATE ;
503 l_rate_type VARCHAR2(1000);
504 l_srate_type VARCHAR2(1000);
505 l_annu_curr_code VARCHAR2(100);
506
507
508 BEGIN
509
510
511 l_start_date := OKI_DBI_LOAD_CLEB_PVT.G_Start_Date;
512 l_end_date := OKI_DBI_LOAD_CLEB_PVT.G_End_Date;
513 l_global_curr := BIS_COMMON_PARAMETERS.Get_Currency_Code;
514 l_sysdate := OKI_DBI_LOAD_CLEB_PVT.G_Run_Date;
515 l_rate_type := BIS_COMMON_PARAMETERS.Get_Rate_Type;
516 l_srate_type := bis_common_parameters.get_secondary_rate_type ;
517 l_annu_curr_code := CASE bis_common_parameters.get_annualized_currency_code
518 WHEN 'PRIMARY' THEN 'Primary'
519 WHEN 'SECONDARY' THEN 'Secondary'
520 ELSE 'Not Defined'
521 END;
522
523
524 -- Added by Arun for secondary global currency conversion changes
525 IF(POA_CURRENCY_PKG.display_secondary_currency_yn)
526 THEN
527 -- Check if Sec currency Display profile is set
528 IF BIS_COMMON_PARAMETERS.Get_Secondary_Currency_Code IS NOT NULL
529 THEN l_sglobal_curr := BIS_COMMON_PARAMETERS.Get_Secondary_Currency_Code;
530 END IF;
531 END IF;
532 l_conv_type := bis_common_parameters.get_treasury_rate_type;
533
534 rlog ('Treasury Rate Type is - '||NVL(l_conv_type, 'Not Defined'),1);
535
536 IF l_conv_type is NULL THEN
537 l_conv_type := bis_common_parameters.get_rate_type ;
538 END IF;
539
540 rlog ('Primary Rate Type is - '||NVL(l_rate_type,'Not Defined'),1);
541 rlog ('Secondary Rate Type is - '||NVL(l_srate_type,'Not Defined'),1);
542 rlog ('Primary Global Currency is - '||NVL(l_global_curr,'Not Defined'),1);
543 rlog ('Secondary Global Currency is - '||NVL(l_sglobal_curr,'Not Defined'),1);
544 rlog ('Annualized Global Currency is - '||l_annu_curr_code,1);
545
546
547 l_salesperson_code := fnd_profile.value('OKS_ENABLE_SALES_CREDIT');
548 IF l_salesperson_code IN ('YES', 'DRT') THEN /* Added 'DRT' filter condition, Please refer Bug#5978601 */
549 l_salesperson_code := fnd_profile.value('OKS_VENDOR_CONTACT_ROLE');
550 ELSE
551 l_salesperson_code := 'SALESPERSON';
552 END IF ;
553
554 rlog ('Sales Person Code - '||l_salesperson_code,1);
555 SELECT NVL(BIS_COMMON_PARAMETERS.get_batch_size(BIS_COMMON_PARAMETERS.HIGH)
556 , 1000)
557 INTO l_batch_size
558 FROM DUAL ;
559
560 rlog( 'Populating Incremental Staging Table OKI_DBI_CHR_STAGE_INC - ' || fnd_date.date_to_displayDT(sysdate),1);
561
562 SELECT MAX(renewal_id),MAX(ren_con_id)
563 INTO g_renewal_id,g_ren_con_id
564 from
565 (
566 SELECT decode(opn_code,'RENEWAL',id,null) Renewal_ID, decode(opn_code,'REN_CON',id,null) Ren_CON_ID
567 from okc_class_operations clsop
568 WHERE clsop.cls_code='SERVICE'
569 AND clsop.opn_code in ('RENEWAL','REN_CON')
570 );
571
572 INSERT /*+ append */ INTO OKI_DBI_CHR_STAGE_INC
573 ( id,
574 currency_code,
575 date_approved,
576 creation_date,
577 conversion_rate_date,
578 conversion_type,
579 conversion_rate,
580 end_date,
581 authoring_org_id,
582 application_id,
583 contract_number,
584 contract_number_modifier,
585 buy_or_sell,
586 scs_code,
587 trn_code,
588 date_signed,
589 start_date,
590 date_terminated,
591 renewal_type_code,
592 sts_code,
593 datetime_cancelled,
594 last_update_date,
595 Bill_to_site_use_id,
596 Ship_to_site_use_id,
597 inv_organization_id,
598 subsequent_renewal_type_code, /* for ER#5760744 */
599 hdr_term_cancel_source /* for ER 6684955 */
600 )
601 SELECT /*+ parallel(h) parallel(v) */ h.id
602 , h.currency_code
603 , h.date_approved
604 , h.creation_date
605 , h.conversion_rate_date
606 , h.conversion_type
607 , h.conversion_rate
608 , h.end_date
609 , h.authoring_org_id
610 , h.application_id
611 , h.contract_number
612 , h.contract_number_modifier
613 , h.buy_or_sell
614 , h.scs_code
615 , h.trn_code
616 , h.date_signed
617 , h.start_date
618 , h.date_terminated
619 , h.renewal_type_code
620 , h.sts_code
621 , h.datetime_cancelled
622 , h.last_update_date
623 , h.Bill_to_site_use_id
624 , h.Ship_to_site_use_id
625 , h.inv_organization_id
626 , decode(h.renewal_type_code, 'ERN', Decode(h.approval_type, 'M', 'ERN'),
627 'EVN', decode(h.approval_type, 'Y', 'EVN', 'N', 'EVN'),
628 'DNR', 'DNR',
629 'NSR', decode(h.approval_type, 'Y', 'NSR', 'N', 'NSR', 'NSR')
630 ) subsequent_renewal_type_code /* Added this colunm as a part of ER#5760744 */
631 ,h.term_cancel_source hdr_term_cancel_source /* Added for ER 6684955 */
632 FROM okc_k_headers_all_b h
633 , okc_k_vers_numbers v
634 WHERE 1=1
635 AND
636 (
637 (
638 v.last_update_date >= l_start_date
639 AND v.last_update_date+0 <= l_end_date
640 AND COALESCE(h.date_terminated,h.datetime_cancelled,h.end_date,g_4712_date) > g_global_start_date
641 )
642 OR
643 ( /* Retrieve open contracts that do not fall within the initial load date range, for disco */
644 v.last_update_date < l_start_date
645 AND h.datetime_cancelled is NULL
646 AND h.date_signed is NULL
647 AND h.date_terminated is NULL -- This check is for bad data which have date terminated without date signed
648 )
649 )
650 AND h.id = v.chr_id
651 AND h.template_yn = 'N'
652 AND h.application_id = 515
653 AND h.buy_or_sell ='S'
654 AND h.scs_code IN ('SERVICE','WARRANTY') ;
655
656 l_count := SQL%ROWCOUNT ;
657
658 rlog( 'Number of contracts inserted into OKI_DBI_CHR_STAGE_INC : '||to_char(l_count), 2) ;
659 COMMIT;
660
661 GATHER_TABLE_STATS(TABNAME => 'OKI_DBI_CHR_STAGE_INC');
662
663 rlog('Load of Incremental Staging Table OKI_DBI_CHR_STAGE_INC completed - ' ||
664 fnd_date.date_to_displayDT(SYSDATE),1) ;
665
666 rlog( 'Populating Incremental Table OKI_DBI_CHR_INC - ' || fnd_date.date_to_displayDT(sysdate),1);
667
668 INSERT /*+ append parallel(a) */ INTO oki_dbi_chr_inc a
669 (
670 chr_id
671 , conversion_date
672 , trx_rate_type
673 , trx_currency
674 , func_currency
675 , trx_func_rate
676 , batch_id
677 , grace_end_date
678 , salesrep_id
679 , resource_id
680 , resource_group_id
681 , gsd_flag
682 , authoring_org_id
683 , application_id
684 , contract_number
685 , contract_number_modifier
686 , buy_or_sell
687 , scs_code
688 , trn_code
689 , date_signed
690 , start_date
691 , end_date
692 , date_terminated
693 , renewal_type_code
694 , sts_code
695 , date_approved
696 , datetime_cancelled
697 , creation_Date
698 , last_update_date
699 , Bill_to_site_use_id
700 , Ship_to_site_use_id
701 , est_rev_percent
702 , est_rev_date
703 , Acct_rule_id
704 , master_organization_id
705 , customer_party_id
706 , order_number
707 , subsequent_renewal_type_code /* for ER#5760744 */
708 , negotiation_status /* for ER#5950128 */
709 , reminder /* for ER#5950128 */
710 , hdr_term_cancel_source /* Added for ER 6684955 */
711 ) SELECT k.id
712 , trunc(COALESCE(k.conversion_date, k.date_approved, k.creation_date)) CONVERSION_DATE
713 , (CASE WHEN k.currency_code = k.sob_currency_code
714 THEN l_rate_type
715 ELSE oki_dbi_currency_pvt.get_trx_rate_type_init(
716 k.id
717 , k.currency_code
718 , k.sob_currency_code
719 , NVL(k.date_approved, k.creation_date)
720 , k.conversion_date
721 , k.conversion_type
722 , k.trx_func_rate )
723 END ) TRX_RATE_TYPE
724 , k.currency_code
725 , k.sob_currency_code
726 , k.trx_func_rate
727 , 1 batch_id
728 , k.ged+1 AS GRACE_END_DATE
729 , k.salesrep_id
730 , k.resource_id
731 , k.resource_group_id
732 , k.gsd_flag
733 , k.authoring_org_id
734 , k.application_id
735 , k.contract_number
736 , k.contract_number_modifier
737 , k.buy_or_sell
738 , k.scs_code
739 , k.trn_code
740 , k.date_signed
741 , k.start_date
742 , k.end_date
743 , k.date_terminated
744 , k.renewal_type_code
745 , k.sts_code
746 , k.date_approved
747 , k.datetime_cancelled
748 , k.creation_Date
749 , k.last_update_date
750 , k.Bill_to_site_use_id
751 , k.Ship_to_site_use_id
752 , k.est_rev_percent
753 , k.est_rev_date
754 , k.Acct_rule_id
755 , k.master_organization_id
756 , k.customer_party_id
757 , k.order_number
758 , k.subsequent_renewal_type_code /* for ER#5760744 */
759 , k.negotiation_status /* for ER#5950128 */
760 , k.reminder /* for ER#5950128 */
761 , k.hdr_term_cancel_source /* Added as part of ER6684955 */
762 FROM (
763 SELECT /*+ ordered no_merge use_hash(fsp,sob,sh,tcu,srep,res)
764 parallel(fsp) parallel(sob) parallel(h) parallel(sh)
765 parallel(srep) parallel(res) swap_join_inputs(fsp)
766 swap_join_inputs(sob)
767 pq_distribute(fsp,none,broadcast)
768 pq_distribute(sob,none, broadcast) */
769 h.id
770 , h.currency_code
771 , sob.currency_code AS SOB_CURRENCY_CODE
772 , h.date_approved
773 , h.creation_date
774 , h.conversion_rate_date AS CONVERSION_DATE
775 , h.conversion_type
776 , decode(upper(h.conversion_type), 'USER',
777 decode(h.currency_code, sob.currency_code, 1, h.conversion_rate),
778 NULL) AS TRX_FUNC_RATE
779 -- , sh.est_rev_percent win_percent
780 -- , sh.est_rev_date expected_close_date
781 -- This CASE expression must handle all TCE_CODE values.
782 -- For HOUR and MINUTE values,
783 -- the (((24 * 60) - 1) / (24 * 60))
784 -- expr makes end_date 1 minute before midnight so that
785 -- adding the grace period always adds at least 1 day
786 -- since 11.5.10 grace_duration from OKS tables
787 , TRUNC(CASE
788 WHEN tcu.tce_code IN ('YEAR')
789 THEN ADD_MONTHS (h.end_date
790 , (12 * sh.grace_duration * tcu.quantity))
791 WHEN tcu.tce_code IN ('MONTH')
792 THEN ADD_MONTHS (h.end_date
793 , (sh.grace_duration * tcu.quantity))
794 WHEN tcu.tce_code IN ('DAY')
795 THEN h.end_date
796 + (sh.grace_duration * tcu.quantity)
797 WHEN tcu.tce_code IN ('HOUR')
798 THEN h.end_date
799 + (((24 * 60) - 1) / (24 * 60))
800 + ((sh.grace_duration * tcu.quantity) / 24)
801 WHEN tcu.tce_code IN ('MINUTE')
802 THEN h.end_date
803 + (((24 * 60) - 1) / (24 * 60))
804 + ((sh.grace_duration * tcu.quantity) / (24 * 60)) END) ged
805 , DECODE(srep.salesrep_id, NULL, -1, srep.salesrep_id) AS salesrep_id
806 , res.resource_id
807 , nvl(srep.SALES_GROUP_ID, -1) resource_group_id
808 , h.gsd_flag
809 , h.authoring_org_id
810 , h.application_id
811 , h.contract_number
812 , h.contract_number_modifier
813 , h.buy_or_sell
814 , h.scs_code
815 , h.trn_code
816 , h.date_signed
817 , h.start_date
818 , h.end_date
819 , h.date_terminated
820 , h.renewal_type_code
821 , h.sts_code
822 , h.datetime_cancelled
823 , h.last_update_date
824 , h.Bill_to_site_use_id
825 , h.Ship_to_site_use_id
826 , sh.est_rev_percent
827 , sh.est_rev_date
828 , sh.Acct_rule_id
829 , mprm.master_organization_id
830 , TO_NUMBER(c.object1_id1) customer_party_id
831 , oh.order_number order_number
832 , h.subsequent_renewal_type_code /* for ER#5760744 */
833 , sh.renewal_status negotiation_status /* for ER#5950128 */
834 , sh.rmndr_suppress_flag reminder /* for ER#5950128 */
835 , h.hdr_term_cancel_source /* for ER6684955 */
836 FROM (SELECT /*+ no_merge parallel(h) */
837 h.id
838 , h.currency_code
839 , h.date_approved
840 , h.creation_date
841 , h.conversion_rate_date
842 , h.conversion_type
843 , h.conversion_rate
844 , h.end_date
845 , h.authoring_org_id
846 , 1 gsd_flag
847 , h.application_id
848 , h.contract_number
849 , h.contract_number_modifier
850 , h.buy_or_sell
851 , h.scs_code
852 , h.trn_code
853 , h.date_signed
854 , h.start_date
855 , h.date_terminated
856 , h.renewal_type_code
857 , h.sts_code
858 , h.datetime_cancelled
859 , h.last_update_date
860 , h.Bill_to_site_use_id
861 , h.Ship_to_site_use_id
862 , h.inv_organization_id
863 , h.subsequent_renewal_type_code /* for ER#5760744 */
864 , h.hdr_term_cancel_source /* for ER6684955 */
865 FROM oki_dbi_chr_stage_inc h
866 UNION
867 SELECT /*+ leading(inc) use_hash(h,ren_rel,a)
868 parallel(inc) parallel(h) parallel(ren_rel) parallel(a) */
869 h.id
870 , h.currency_code
871 , h.date_approved
872 , h.creation_date
873 , h.conversion_rate_date
874 , h.conversion_type
875 , h.conversion_rate
876 , h.end_date
877 , h.authoring_org_id
878 , case when (COALESCE(h.date_terminated,h.end_date,g_4712_date) <= g_global_start_date) then -1
879 else 1 end gsd_flag
880 , h.application_id
881 , h.contract_number
882 , h.contract_number_modifier
883 , h.buy_or_sell
884 , h.scs_code
885 , h.trn_code
886 , h.date_signed
887 , h.start_date
888 , h.date_terminated
889 , h.renewal_type_code
890 , h.sts_code
891 , h.datetime_cancelled
892 , h.last_update_date
893 , h.Bill_to_site_use_id
894 , h.Ship_to_site_use_id
895 , h.inv_organization_id
896 , decode(h.renewal_type_code, 'ERN', Decode(h.approval_type, 'M', 'ERN'),
897 'EVN', decode(h.approval_type, 'Y', 'EVN', 'N', 'EVN'),
898 'DNR', 'DNR',
899 'NSR', decode(h.approval_type, 'Y', 'NSR', 'N', 'NSR', 'NSR')
900 ) subsequent_renewal_type_code /* Added this colunm as a part of ER#5760744 */
901 ,h.term_cancel_source hdr_term_cancel_source /* for ER6684955 */
902 FROM oki_dbi_chr_stage_inc inc
903 , okc_k_headers_all_b h
904 , okc_operation_lines ren_rel
905 , oki_dbi_chr_stage_inc inc2
906 , okc_operation_instances opins
907 WHERE 1=1
908 AND ren_rel.object_chr_id = h.ID
909 AND ren_rel.subject_chr_id = inc.id
910 AND ren_rel.subject_cle_id IS NULL
911 AND ren_rel.object_cle_id IS NULL
912 /* restricts relationships to renewals and renewal consolidations*/
913 AND ren_rel.oie_id=opins.id
914 AND opins.cop_id in (g_renewal_id,g_ren_con_id)
915 /* end of restricting relationship to renewals and renewal consolidations*/
916 AND inc2.ID(+) = H.ID
917 -- only get the ones that did not find a match
918 AND inc2.ID IS NULL
919 AND h.datetime_cancelled is null
920 AND h.template_yn = 'N'
921 AND h.application_id = 515
922 AND h.buy_or_sell ='S'
923 AND h.scs_code IN ('SERVICE','WARRANTY')
924 ) h
925 , financials_system_params_all fsp
926 , gl_sets_of_books sob
927 , OKS_K_HEADERS_B sh
928 , mtl_parameters mprm
929 , okc_k_party_roles_b c
930 , okc_k_rel_objs ro
931 , oe_order_headers_all oh
932 -- inline view to select one conversion only per UOM code
933 -- Chooses the conversion rule with the lowest quantity
934 , (SELECT /*+ no_merge parallel(tcui) */
935 tcui.uom_code
936 , max(tcui.tce_code)
937 keep (dense_rank first order by tcui.quantity) as tce_code
938 , max(tcui.quantity)
939 keep (dense_rank first order by tcui.quantity) as quantity
940 FROM okc_time_code_units_b tcui
941 WHERE tcui.active_flag = 'Y'
942 GROUP BY tcui.uom_code
943 ) tcu
944 -- salesrep
945 , (SELECT /*+ no_merge parallel(srep) parallel(h) */
946 srep.dnz_chr_id
947 , h.authoring_org_id
948 -- if multiple sales rep are in contract get the sales rep
949 -- with the closest date to current date.
950 -- for this sales rep identified get the sales group id.
951 , max(srep.object1_id1) keep (dense_rank first
952 ORDER BY CASE WHEN (l_sysdate
953 BETWEEN NVL(srep.start_date,l_sysdate)
954 AND NVL(srep.end_date,l_sysdate))
955 THEN 1
956 WHEN (NVL(srep.start_date, l_sysdate) >
957 l_sysdate)
958 THEN 2
959 ELSE 3
960 END ASC
961 , CASE WHEN (l_sysdate BETWEEN NVL(srep.start_date,l_sysdate)
962 AND NVL(srep.end_date,l_sysdate))
963 THEN g_9999_date -
964 NVL(srep.start_date, l_sysdate)
965 ELSE (CASE WHEN (NVL(srep.start_date,l_sysdate) > l_sysdate)
966 THEN g_9999_date -
967 NVL(srep.start_date,l_sysdate)
968 ELSE NVL(srep.end_date,l_sysdate) -
969 g_0001_date
970 END)
971 END ASC , srep.last_update_date DESC, srep.id ASC) salesrep_id
972 , max(srep.sales_group_id) keep (dense_rank first
973 ORDER BY CASE WHEN (sysdate
974 BETWEEN NVL(srep.start_date,sysdate)
975 AND NVL(srep.end_date,sysdate))
976 THEN 1
977 WHEN (NVL(srep.start_date, sysdate) >
978 sysdate)
979 THEN 2
980 ELSE 3
981 END ASC
982 , CASE WHEN (sysdate BETWEEN NVL(srep.start_date,sysdate)
983 AND NVL(srep.end_date,sysdate))
984 THEN g_9999_date -
985 NVL(srep.start_date, sysdate)
986 ELSE (CASE WHEN (NVL(srep.start_date,sysdate) > sysdate)
987 THEN g_9999_date -
988 NVL(srep.start_date,sysdate)
989 ELSE NVL(srep.end_date,sysdate) -
990 g_0001_date
991 END)
992 END ASC , srep.last_update_date DESC, srep.id ASC) sales_group_id
993 FROM okc_contacts srep
994 , okc_k_headers_all_b h
995 WHERE 1 = 1
996 AND h.id = srep.dnz_chr_id
997 AND srep.cro_code = l_salesperson_code
998 AND NVL (srep.primary_yn, 'Y') = 'Y'
999 AND h.template_yn = 'N'
1000 AND h.application_id = 515
1001 AND h.buy_or_sell ='S'
1002 AND h.scs_code IN ('SERVICE','WARRANTY')
1003 GROUP BY srep.dnz_chr_id, h.authoring_org_id
1004 )srep
1005 , jtf_rs_salesreps res
1006 WHERE 1=1
1007 AND fsp.org_id = h.authoring_org_id
1008 AND sob.set_of_books_id = fsp.set_of_books_id
1009 AND h.id = sh.chr_id
1010 AND h.inv_organization_id = mprm.organization_id
1011 AND c.dnz_chr_id = h.id
1012 AND c.cle_id IS NULL
1013 AND c.rle_code IN ('CUSTOMER','LICENSEE','BUYER')
1014 AND NVL(c.primary_yn,'Y') = 'Y'
1015 AND h.id = ro.chr_id (+)
1016 AND ro.jtot_object1_code(+) = 'OKX_ORDERHEAD'
1017 AND ro.object1_id1 = oh.header_id(+)
1018 AND tcu.uom_code(+) = sh.grace_period
1019 AND h.id = srep.dnz_chr_id(+)
1020 AND srep.salesrep_id = res.salesrep_id(+)
1021 AND srep.authoring_org_id = res.org_id(+)
1022 ) k ;
1023
1024 l_count := SQL%ROWCOUNT ;
1025
1026 rlog( 'Number of contracts inserted into OKI_DBI_CHR_INC : '||to_char(l_count),2) ;
1027 COMMIT;
1028
1029 GATHER_TABLE_STATS( TABNAME => 'OKI_DBI_CHR_INC') ;
1030
1031 rlog('Load of Incremental Table OKI_DBI_CHR_INC completed - ' ||
1032 fnd_date.date_to_displayDT(SYSDATE),1) ;
1033
1034 EXCEPTION
1035 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1036 RAISE;
1037 WHEN OTHERS THEN
1038 rlog( 'Error : While loading Incremental Table OKI_DBI_CHR_INC ',0);
1039 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
1040 fnd_message.set_name( application => 'FND'
1041 , name => 'CRM-DEBUG ERROR' ) ;
1042 fnd_message.set_token(
1043 token => 'ROUTINE'
1044 , value => 'OKI_DBI_LOAD_CLEB_PVT.POPULATE_INC_TABLE_INIT ' ) ;
1045 bis_collection_utilities.put_line(fnd_message.get) ;
1046 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR ;
1047 END populate_inc_table_init ;
1048
1049 /* *****************************************************************************
1050 Procedure:populate_inc_table_inc
1051 Descritpion:Procedure to populate incremental table for incremental load.
1052 Check okc_k_vers_numbers table for new/modified contracts
1053 ************************************************************************** */
1054
1055 PROCEDURE populate_inc_table_inc IS
1056 l_start_date DATE ;
1057 l_end_date DATE ;
1058 l_batch_size NUMBER ;
1059 l_sql_string VARCHAR2(10000) ;
1060
1061 l_oki_schema VARCHAR2(30) ;
1062 l_status VARCHAR2(30) ;
1063 l_industry VARCHAR2(30) ;
1064 l_count NUMBER ;
1065 l_global_curr VARCHAR(30);
1066 --Added by Arun for secondary global currency conversion
1067 l_sglobal_curr VARCHAR(30);
1068 l_salesperson_code VARCHAR2(80) ;
1069 l_sysdate DATE ;
1070 l_rate_type VARCHAR2(1000);
1071
1072 BEGIN
1073
1074
1075
1076 SELECT MAX(renewal_id),MAX(ren_con_id)
1077 INTO g_renewal_id,g_ren_con_id
1078 from
1079 (
1080 SELECT decode(opn_code,'RENEWAL',id,null) Renewal_ID, decode(opn_code,'REN_CON',id,null) Ren_CON_ID
1081 from okc_class_operations clsop
1082 WHERE clsop.cls_code='SERVICE'
1083 AND clsop.opn_code in ('RENEWAL','REN_CON')
1084 );
1085
1086
1087 l_start_date := OKI_DBI_LOAD_CLEB_PVT.g_start_date ;
1088 l_end_date := OKI_DBI_LOAD_CLEB_PVT.g_end_date ;
1089 l_global_curr := bis_common_parameters.get_currency_code ;
1090 l_sysdate := OKI_DBI_LOAD_CLEB_PVT.g_run_date ;
1091
1092 -- Added by Arun for secondary global currency conversion changes
1093 IF(POA_CURRENCY_PKG.display_secondary_currency_yn)
1094 THEN
1095 -- Check if Sec Currency display flag is set
1096 IF BIS_COMMON_PARAMETERS.Get_Secondary_Currency_Code IS NOT NULL
1097 THEN l_sglobal_curr := BIS_COMMON_PARAMETERS.Get_Secondary_Currency_Code;
1098 END IF;
1099 END IF;
1100
1101 l_salesperson_code := fnd_profile.value('OKS_ENABLE_SALES_CREDIT');
1102 IF l_salesperson_code IN ('YES', 'DRT') THEN /* Added 'DRT' filter condition, Please refer Bug#5978601 */
1103 l_salesperson_code := fnd_profile.value('OKS_VENDOR_CONTACT_ROLE');
1104 ELSE
1105 l_salesperson_code := 'SALESPERSON';
1106 END IF ;
1107
1108 SELECT NVL(BIS_COMMON_PARAMETERS.get_batch_size(BIS_COMMON_PARAMETERS.HIGH)
1109 , 1000)
1110 INTO l_batch_size
1111 FROM DUAL ;
1112
1113 g_batch_size := l_batch_size;
1114 IF (FND_INSTALLATION.GET_APP_INFO(
1115 application_short_name => 'OKI'
1116 , status => l_status
1117 , industry => l_industry
1118 , oracle_schema => l_oki_schema )) THEN
1119
1120 l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema || '.OKI_DBI_CHR_STAGE_INC';
1121 EXECUTE IMMEDIATE l_sql_string;
1122 rlog( 'Truncated Table ' || l_oki_schema || '.OKI_DBI_CHR_STAGE_INC',1);
1123
1124 l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema || '.OKI_DBI_CHR_INC';
1125 EXECUTE IMMEDIATE l_sql_string;
1126 rlog( 'Truncated Table ' || l_oki_schema || '.OKI_DBI_CHR_INC',1);
1127
1128 /* Commented as per mail communications - for Disco */
1129 /*
1130 l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema || '.OKI_DBI_WORKER_STATUS';
1131 EXECUTE IMMEDIATE l_sql_string;
1132 rlog( 'Truncated Table ' || l_oki_schema || '.OKI_DBI_WORKER_STATUS',1);
1133 */
1134
1135 DELETE FROM OKI_DBI_WORKER_STATUS WHERE OBJECT_NAME = 'OKI_DBI_CLE_B_OLD';
1136 COMMIT;
1137
1138 l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema || '.OKI_DBI_CLE_B_OLD';
1139 EXECUTE IMMEDIATE l_sql_string;
1140 rlog( 'Truncated Table ' || l_oki_schema || '.OKI_DBI_CLE_B_OLD',1);
1141
1142 l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema || '.OKI_DBI_CURR_CONV';
1143 EXECUTE IMMEDIATE l_sql_string;
1144 rlog( 'Truncated Table ' || l_oki_schema || '.OKI_DBI_CURR_CONV',1);
1145 l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema || '.OKI_DBI_REN_INC' ;
1146 EXECUTE IMMEDIATE l_sql_string ;
1147 rlog('Truncated Table OKI_DBI_REN_INC ',1);
1148 l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema || '.OKI_DBI_PREV_INC' ;
1149 EXECUTE IMMEDIATE l_sql_string ;
1150 rlog('Truncated Table OKI_DBI_PREV_INC ',1);
1151
1152 l_rate_type := bis_common_parameters.get_treasury_rate_type;
1153
1154 rlog ('Treasury Rate Type is '||NVL(l_rate_type,'Not Defined'),1);
1155
1156 IF l_rate_type is NULL THEN
1157 l_rate_type := bis_common_parameters.get_rate_type ;
1158 END IF;
1159
1160 rlog ('Primary Rate Type is '||NVL(bis_common_parameters.get_rate_type,'Not Defined'),1);
1161
1162 rlog( 'Populating Incremental Staging Table OKI_DBI_CHR_STAGE_INC - ' || fnd_date.date_to_displayDT(sysdate),1);
1163
1164
1165
1166 /* Added hint as per perf. teams recommendations*/
1167 INSERT INTO OKI_DBI_CHR_STAGE_INC
1168 ( id,
1169 currency_code,
1170 date_approved,
1171 creation_date,
1172 conversion_rate_date,
1173 conversion_type,
1174 conversion_rate,
1175 end_date,
1176 authoring_org_id,
1177 application_id,
1178 contract_number,
1179 contract_number_modifier,
1180 buy_or_sell,
1181 scs_code,
1182 trn_code,
1183 date_signed,
1184 start_date,
1185 date_terminated,
1186 renewal_type_code,
1187 sts_code,
1188 datetime_cancelled,
1189 last_update_date,
1190 Bill_to_site_use_id,
1191 Ship_to_site_use_id,
1192 inv_organization_id,
1193 subsequent_renewal_type_code,
1194 hdr_term_cancel_source
1195 )
1196 SELECT /*+ cardinality(v,1) index(V OKC_K_VERS_NUMBERS_N1) */ h.id
1197 , h.currency_code
1198 , h.date_approved
1199 , h.creation_date
1200 , h.conversion_rate_date
1201 , h.conversion_type
1202 , h.conversion_rate
1203 , h.end_date
1204 , h.authoring_org_id
1205 , h.application_id
1206 , h.contract_number
1207 , h.contract_number_modifier
1208 , h.buy_or_sell
1209 , h.scs_code
1210 , h.trn_code
1211 , h.date_signed
1212 , h.start_date
1213 , h.date_terminated
1214 , h.renewal_type_code
1215 , h.sts_code
1216 , h.datetime_cancelled
1217 , h.last_update_date
1218 , h.Bill_to_site_use_id
1219 , h.Ship_to_site_use_id
1220 , h.inv_organization_id
1221 , decode(h.renewal_type_code, 'ERN', Decode(h.approval_type, 'M', 'ERN'),
1222 'EVN', decode(h.approval_type, 'Y', 'EVN', 'N', 'EVN'),
1223 'DNR', 'DNR',
1224 'NSR', decode(h.approval_type, 'Y', 'NSR', 'N', 'NSR', 'NSR')
1225 ) subsequent_renewal_type_code /* for ER#5760744 */
1226 , h.term_cancel_source hdr_term_cancel_source /* for ER 6684955 */
1227 FROM okc_k_headers_all_b h
1228 , okc_k_vers_numbers v
1229 WHERE 1=1
1230 AND v.last_update_date >= l_start_date
1231 AND v.last_update_date+0 <= l_end_date
1232 AND h.id = v.chr_id
1233 AND COALESCE(h.date_terminated,h.datetime_cancelled,h.end_date,g_4712_date) > g_global_start_date
1234 AND h.template_yn = 'N'
1235 AND h.application_id = 515
1236 AND h.buy_or_sell ='S'
1237 AND h.scs_code IN ('SERVICE','WARRANTY') ;
1238
1239 l_count := SQL%ROWCOUNT ;
1240
1241 rlog( 'Number of contracts inserted into OKI_DBI_CHR_STAGE_INC : '||to_char(l_count), 2) ;
1242 COMMIT;
1243
1244 GATHER_TABLE_STATS(TABNAME => 'OKI_DBI_CHR_STAGE_INC');
1245
1246 rlog('Load of Incremental Staging Table OKI_DBI_CHR_STAGE_INC completed - ' ||
1247 fnd_date.date_to_displayDT(SYSDATE),1) ;
1248
1249 rlog( 'Populating Incremental Table OKI_DBI_CHR_INC - ' || fnd_date.date_to_displayDT(sysdate),1);
1250
1251 INSERT INTO oki_dbi_chr_inc
1252 (
1253 chr_id
1254 , conversion_date
1255 , trx_rate_type
1256 , trx_currency
1257 , func_currency
1258 , trx_func_rate
1259 , batch_id
1260 , grace_end_date
1261 , salesrep_id
1262 , resource_id
1263 , resource_group_id
1264 , worker_number
1265 , gsd_flag
1266 , authoring_org_id
1267 , application_id
1268 , contract_number
1269 , contract_number_modifier
1270 , buy_or_sell
1271 , scs_code
1272 , trn_code
1273 , date_signed
1274 , start_date
1275 , end_date
1276 , date_terminated
1277 , renewal_type_code
1278 , sts_code
1279 , date_approved
1280 , datetime_cancelled
1281 , creation_Date
1282 , last_update_date
1283 , Bill_to_site_use_id
1284 , Ship_to_site_use_id
1285 , est_rev_percent
1286 , est_rev_date
1287 , Acct_rule_id
1288 , master_organization_id
1289 , customer_party_id
1290 , order_number
1291 , subsequent_renewal_type_code /* Added this colunm as a part of ER#5760744 */
1292 , negotiation_status /* Added this colunm as a part of ER#5950128 */
1293 , reminder /* Added this colunm as a part of ER#5950128 */
1294 , hdr_term_cancel_source /* Added for ER 6684955 */
1295 )
1296 select chr_id
1297 , conversion_date
1298 , trx_rate_type
1299 , trx_currency
1300 , func_currency
1301 , trx_func_rate
1302 , batch_id
1303 , grace_end_date
1304 , salesrep_id
1305 , CASE WHEN SALESREP_ID <> -1 THEN
1306 (select resource_id from jtf_rs_salesreps s where s.salesrep_id = new.salesrep_id
1307 and s.org_id = new.authoring_org_id)
1308 END resource_id
1309 , resource_group_id
1310 , -1 worker_number
1311 , gsd_flag
1312 , authoring_org_id
1313 , application_id
1314 , contract_number
1315 , contract_number_modifier
1316 , buy_or_sell
1317 , scs_code
1318 , trn_code
1319 , date_signed
1320 , start_date
1321 , end_date
1322 , date_terminated
1323 , renewal_type_code
1324 , sts_code
1325 , date_approved
1326 , datetime_cancelled
1327 , creation_Date
1328 , last_update_date
1329 , Bill_to_site_use_id
1330 , Ship_to_site_use_id
1331 , est_rev_percent
1332 , est_rev_date
1333 , Acct_rule_id
1334 , master_organization_id
1335 , customer_party_id
1336 , order_number
1337 , subsequent_renewal_type_code /* for ER#5760744 */
1338 , negotiation_status /* for ER#5950128 */
1339 , reminder /* for ER#5950128 */
1340 , hdr_term_cancel_source /* for ER 6684955 */
1341 from (
1342 SELECT chr_id
1343 , conversion_date
1344 , trx_rate_type
1345 , trx_currency
1346 , func_currency
1347 , trx_func_rate
1348 , batch_id
1349 , grace_end_date
1350 , NVL((SELECT to_number(substr(salesrep_attribs,1,instr(salesrep_attribs,'#')-1)) from dual),-1) salesrep_id
1351 , NVL((SELECT to_number(substr(salesrep_attribs,instr(salesrep_attribs,'#')+1)) from dual),-1) resource_group_id
1352 , gsd_flag
1353 , authoring_org_id
1354 , application_id
1355 , contract_number
1356 , contract_number_modifier
1357 , buy_or_sell
1358 , scs_code
1359 , trn_code
1360 , date_signed
1361 , start_date
1362 , end_date
1363 , date_terminated
1364 , renewal_type_code
1365 , sts_code
1366 , date_approved
1367 , datetime_cancelled
1368 , creation_Date
1369 , last_update_date
1370 , Bill_to_site_use_id
1371 , Ship_to_site_use_id
1372 , est_rev_percent
1373 , est_rev_date
1374 , Acct_rule_id
1375 , master_organization_id
1376 , customer_party_id
1377 , order_number
1378 , subsequent_renewal_type_code /* for ER#5760744 */
1379 , negotiation_status /* for ER#5950128 */
1380 , reminder /* Added this colunm as a part of ER#5950128 */
1381 , hdr_term_cancel_source /* Added for ER 6684955 */
1382 FROM (SELECT /*+ use_hash(fsp,mprm) swap_join_inputs(fsp) use_nl(sob,sh,c,ro)*/
1383 h.id chr_id
1384 , trunc(COALESCE(h.conversion_rate_date, h.date_approved, h.creation_date)) AS CONVERSION_DATE
1385 , (CASE WHEN h.currency_code = sob.currency_code
1386 THEN l_rate_type
1387 ELSE oki_dbi_currency_pvt.get_trx_rate_type(
1388 h.id
1389 , h.currency_code
1390 , sob.currency_code
1391 , h.creation_date
1392 , h.conversion_rate_date
1393 , h.conversion_type
1394 , h.conversion_rate)
1395 END ) AS TRX_RATE_TYPE
1396 , h.currency_code trx_currency
1397 , sob.currency_code func_currency
1398 , decode(upper(h.conversion_type), 'USER',
1399 decode(h.currency_code, sob.currency_code, 1, h.conversion_rate),
1400 NULL) AS TRX_FUNC_RATE
1401 , 1 BATCH_ID
1402 -- This CASE expression must handle all TCE_CODE values.
1403 -- For HOUR and MINUTE values,
1404 -- the (((24 * 60) - 1) / (24 * 60))
1405 -- expr makes end_date 1 minute before midnight so that
1406 -- adding the grace period always adds at least 1 day
1407 , (SELECT TRUNC(CASE
1408 WHEN tcu.tce_code IN ('YEAR')
1409 THEN ADD_MONTHS (h.end_date
1410 , (12 * sh.grace_duration * tcu.quantity))
1411 WHEN tcu.tce_code IN ('MONTH')
1412 THEN ADD_MONTHS (h.end_date
1413 , (sh.grace_duration * tcu.quantity))
1414 WHEN tcu.tce_code IN ('DAY')
1415 THEN h.end_date
1416 + (sh.grace_duration * tcu.quantity)
1417 WHEN tcu.tce_code IN ('HOUR')
1418 THEN h.end_date
1419 + (((24 * 60) - 1) / (24 * 60))
1420 + ((sh.grace_duration * tcu.quantity) / 24)
1421 WHEN tcu.tce_code IN ('MINUTE')
1422 THEN h.end_date
1423 + (((24 * 60) - 1) / (24 * 60))
1424 + ((sh.grace_duration * tcu.quantity) / (24 * 60))
1425 END)+1 FROM DUAL) AS GRACE_END_DATE
1426 ,( SELECT nvl(max(srep.object1_id1) keep (dense_rank first
1427 ORDER BY CASE WHEN (l_sysdate
1428 BETWEEN NVL(srep.start_date,l_sysdate)
1429 AND NVL(srep.end_date,l_sysdate))
1430 THEN 1
1431 WHEN (NVL(srep.start_date, l_sysdate) >
1432 l_sysdate)
1433 THEN 2
1434 ELSE 3
1435 END ASC
1436 , CASE WHEN (l_sysdate BETWEEN NVL(srep.start_date,l_sysdate)
1437 AND NVL(srep.end_date,l_sysdate))
1438 THEN g_9999_date -
1439 NVL(srep.start_date, l_sysdate)
1440 ELSE (CASE WHEN (NVL(srep.start_date,l_sysdate) > l_sysdate)
1441 THEN g_9999_date -
1442 NVL(srep.start_date,l_sysdate)
1443 ELSE NVL(srep.end_date,l_sysdate) -
1444 g_0001_date
1445 END)
1446 END ASC , srep.last_update_date DESC, srep.id ASC),-1) || '#' ||
1447 nvl(max(srep.sales_group_id) keep (dense_rank first
1448 ORDER BY CASE WHEN (l_sysdate
1449 BETWEEN NVL(srep.start_date,l_sysdate)
1450 AND NVL(srep.end_date,l_sysdate))
1451 THEN 1
1452 WHEN (NVL(srep.start_date, l_sysdate) >
1453 l_sysdate)
1454 THEN 2
1455 ELSE 3
1456 END ASC
1457 , CASE WHEN (l_sysdate BETWEEN NVL(srep.start_date,l_sysdate)
1458 AND NVL(srep.end_date,l_sysdate))
1459 THEN g_9999_date -
1460 NVL(srep.start_date, l_sysdate)
1461 ELSE (CASE WHEN (NVL(srep.start_date,l_sysdate) > l_sysdate)
1462 THEN g_9999_date -
1463 NVL(srep.start_date,l_sysdate)
1464 ELSE NVL(srep.end_date,l_sysdate) -
1465 g_0001_date
1466 END)
1467 END ASC , srep.last_update_date DESC , srep.id ASC),-1)
1468 FROM okc_contacts srep
1469 WHERE 1 = 1
1470 AND srep.cro_code = l_salesperson_code
1471 AND NVL (srep.primary_yn, 'Y') = 'Y'
1472 AND h.id = srep.dnz_chr_id
1473 GROUP BY srep.dnz_chr_id) Salesrep_attribs
1474 , h.gsd_flag
1475 , h.authoring_org_id
1476 , h.application_id
1477 , h.contract_number
1478 , h.contract_number_modifier
1479 , h.buy_or_sell
1480 , h.scs_code
1481 , h.trn_code
1482 , h.date_signed
1483 , h.start_date
1484 , h.end_date
1485 , h.date_terminated
1486 , h.renewal_type_code
1487 , h.sts_code
1488 , h.date_approved
1489 , h.datetime_cancelled
1490 , h.creation_Date
1491 , h.last_update_date
1492 , h.Bill_to_site_use_id
1493 , h.Ship_to_site_use_id
1494 , sh.est_rev_percent
1495 , sh.est_rev_date
1496 , sh.Acct_rule_id
1497 , mprm.master_organization_id
1498 , TO_NUMBER(c.object1_id1) customer_party_id
1499 , CASE WHEN ro.object1_id1 is not null then
1500 (SELECT order_number from oe_order_headers_all where header_id = ro.object1_id1)
1501 END order_number
1502 , h.subsequent_renewal_type_code /* Added this colunm as a part of ER#5760744 */
1503 , sh.renewal_status negotiation_status /* Added this colunm as a part of ER#5950128 */
1504 , sh.rmndr_suppress_flag reminder /* Added this colunm as a part of ER#5950128 */
1505 , h.hdr_term_cancel_source /* Added for ER 6684955 */
1506 FROM (
1507 SELECT id
1508 , currency_code
1509 , date_approved
1510 , creation_date
1511 , conversion_rate_date
1512 , conversion_type
1513 , conversion_rate
1514 , end_date
1515 , authoring_org_id
1516 , gsd_flag
1517 , application_id
1518 , contract_number
1519 , contract_number_modifier
1520 , buy_or_sell
1521 , scs_code
1522 , trn_code
1523 , date_signed
1524 , start_date
1525 , date_terminated
1526 , renewal_type_code
1527 , sts_code
1528 , datetime_cancelled
1529 , last_update_date
1530 , Bill_to_site_use_id
1531 , Ship_to_site_use_id
1532 , inv_organization_id
1533 , subsequent_renewal_type_code /* Added this colunm as a part of ER#5760744 */
1534 , hdr_term_cancel_source /* Added for ER 6684955 */
1535 FROM (
1536 SELECT id
1537 , currency_code
1538 , date_approved
1539 , creation_date
1540 , conversion_rate_date
1541 , conversion_type
1542 , conversion_rate
1543 , end_date
1544 , authoring_org_id
1545 , gsd_flag
1546 , application_id
1547 , contract_number
1548 , contract_number_modifier
1549 , buy_or_sell
1550 , scs_code
1551 , trn_code
1552 , date_signed
1553 , start_date
1554 , date_terminated
1555 , renewal_type_code
1556 , sts_code
1557 , datetime_cancelled
1558 , last_update_date
1559 , Bill_to_site_use_id
1560 , Ship_to_site_use_id
1561 , inv_organization_id
1562 , subsequent_renewal_type_code /* Added this colunm as a part of ER#5760744 */
1563 , hdr_term_cancel_source /* Added for ER 6684955 */
1564 ,ROW_NUMBER() OVER (PARTITION BY id ORDER BY gsd_flag DESC) r
1565 FROM (
1566 SELECT /*+ cardinality(inc,10) */
1567 inc.id
1568 , inc.currency_code
1569 , inc.date_approved
1570 , inc.creation_date
1571 , inc.conversion_rate_date
1572 , inc.conversion_type
1573 , inc.conversion_rate
1574 , inc.end_date
1575 , inc.authoring_org_id
1576 , inc.application_id
1577 , inc.contract_number
1578 , inc.contract_number_modifier
1579 , inc.buy_or_sell
1580 , inc.scs_code
1581 , inc.trn_code
1582 , inc.date_signed
1583 , inc.start_date
1584 , inc.date_terminated
1585 , inc.renewal_type_code
1586 , inc.sts_code
1587 , inc.datetime_cancelled
1588 , inc.last_update_date
1589 , inc.Bill_to_site_use_id
1590 , inc.Ship_to_site_use_id
1591 , inc.inv_organization_id
1592 , 1 gsd_flag
1593 , subsequent_renewal_type_code /* Added this colunm as a part of ER#5760744 */
1594 , inc.hdr_term_cancel_source /* Added for ER 6684955 */
1595 FROM oki_dbi_chr_stage_inc inc
1596 UNION ALL
1597 SELECT /*+ ordered cardinality(inc,10) */
1598 h.id
1599 , h.currency_code
1600 , h.date_approved
1601 , h.creation_date
1602 , h.conversion_rate_date
1603 , h.conversion_type
1604 , h.conversion_rate
1605 , h.end_date
1606 , h.authoring_org_id
1607 , h.application_id
1608 , h.contract_number
1609 , h.contract_number_modifier
1610 , h.buy_or_sell
1611 , h.scs_code
1612 , h.trn_code
1613 , h.date_signed
1614 , h.start_date
1615 , h.date_terminated
1616 , h.renewal_type_code
1617 , h.sts_code
1618 , h.datetime_cancelled
1619 , h.last_update_date
1620 , h.Bill_to_site_use_id
1621 , h.Ship_to_site_use_id
1622 , h.inv_organization_id
1623 , CASE WHEN (COALESCE(h.date_terminated,h.end_date,g_4712_date)<= g_global_start_date)
1624 THEN -1
1625 ELSE 1
1626 END gsd_flag
1627 , decode(h.renewal_type_code, 'ERN', Decode(h.approval_type, 'M', 'ERN'),
1628 'EVN', decode(h.approval_type, 'Y', 'EVN', 'N', 'EVN'),
1629 'DNR', 'DNR',
1630 'NSR', decode(h.approval_type, 'Y', 'NSR', 'N', 'NSR', 'NSR')
1631 ) subsequent_renewal_type_code /* Added this colunm as a part of ER#5760744 */
1632 , h.term_cancel_source hdr_term_cancel_source /* Added for ER 6684955 */
1633 FROM oki_dbi_chr_stage_inc inc ,
1634 okc_operation_lines ren_rel ,
1635 okc_operation_instances opins,
1636 okc_k_headers_all_b h
1637 WHERE 1=1
1638 AND ren_rel.object_chr_id = h.id
1639 AND ren_rel.subject_chr_id = inc.id
1640 AND ren_rel.subject_cle_id IS NULL
1641 AND ren_rel.object_cle_id IS NULL
1642 /* restricts relationships to renewals and renewal consolidations*/
1643 AND ren_rel.oie_id=opins.id
1644 AND opins.cop_id in (g_renewal_id,g_ren_con_id)
1645 /* end of restricting relationship to renewals and renewal consolidations*/
1646 /*AND COALESCE(h.date_terminated,h.end_date,g_4712_date)<= g_global_start_date*/
1647 AND h.datetime_cancelled IS NULL
1648 AND h.template_yn = 'N'
1649 AND h.application_id = 515
1650 AND h.buy_or_sell ='S'
1651 AND h.scs_code IN ('SERVICE','WARRANTY')
1652 )
1653 )
1654 WHERE r = 1 ) h
1655 , oks_k_headers_b sh
1656 , mtl_parameters mprm
1657 , okc_k_party_roles_b c
1658 , okc_k_rel_objs ro
1659 , financials_system_params_all fsp
1660 , gl_sets_of_books sob
1661 -- inline view to select one conversion only per UOM code
1662 -- Chooses the conversion rule with the lowest quantity
1663 , (SELECT tcui.uom_code
1664 , max(tcui.tce_code)
1665 keep (dense_rank first order by tcui.quantity) as tce_code
1666 , max(tcui.quantity)
1667 keep (dense_rank first order by tcui.quantity) as quantity
1668 FROM okc_time_code_units_b tcui
1669 WHERE tcui.active_flag = 'Y'
1670 GROUP BY tcui.uom_code
1671 ) tcu
1672 WHERE 1=1
1673 AND fsp.org_id = h.authoring_org_id
1674 AND h.inv_organization_id = mprm.organization_id
1675 AND c.dnz_chr_id = h.id
1676 AND c.cle_id IS NULL
1677 AND c.rle_code IN ('CUSTOMER','LICENSEE','BUYER')
1678 /* Removed this conditions after confirming from Ramesh Shankar*/
1679 -- AND NVL(c.primary_yn,'Y') = 'Y'
1680 AND h.id = ro.chr_id (+)
1681 AND ro.jtot_object1_code(+) = 'OKX_ORDERHEAD'
1682 AND sob.set_of_books_id = fsp.set_of_books_id
1683 AND h.ID = sh.chr_id
1684 AND tcu.uom_code(+) = sh.grace_period
1685 ) ilv1
1686 ) new ;
1687
1688 g_contracts_count := SQL%ROWCOUNT ;
1689
1690 rlog( 'Number of contracts identified for merge: ' || to_char(g_contracts_count), 2) ;
1691 COMMIT ;
1692 rlog( 'Load of Incremental Table OKI_DBI_CHR_INC completed - ' || fnd_date.date_to_displayDT(sysdate),1);
1693
1694 GATHER_TABLE_STATS(TABNAME => 'OKI_DBI_CHR_INC') ;
1695 END IF ;
1696 EXCEPTION
1697 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1698 RAISE;
1699 WHEN OTHERS THEN
1700 rlog( 'Error : while loading Incremental Table OKI_DBI_CHR_INC ',0);
1701 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
1702 fnd_message.set_name( application => 'FND'
1703 , name => 'CRM-DEBUG ERROR' ) ;
1704 fnd_message.set_token(
1705 token => 'ROUTINE'
1706 , value => 'OKI_DBI_LOAD_CLEB_PVT.POPULATE_INC_TABLE_INC ' ) ;
1707 bis_collection_utilities.put_line(fnd_message.get) ;
1708 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR ;
1709 END populate_inc_table_inc ;
1710
1711 /* *****************************************************************************
1712 Procedure:load_currencies
1713 Description:loads the currencies Staging table
1714 ************************************************************************** */
1715
1716 PROCEDURE load_currencies IS
1717 l_count NUMBER ;
1718 l_sql_string VARCHAR2(10000) ;
1719
1720 BEGIN
1721
1722 INSERT /*+ append */ INTO oki_dbi_curr_conv
1723 (chr_id,
1724 from_currency,
1725 to_currency,
1726 curr_conv_date,
1727 rate_type,
1728 rate_f,
1729 rate_g,
1730 rate_sg)
1731 SELECT
1732 chr_id,
1733 inc.trx_currency FROM_CURRENCY,
1734 inc.func_currency TO_CURRENCY,
1735 inc.conv_date CURR_CONV_DATE,
1736 inc.conv_type RATE_TYPE
1737 , CASE WHEN (UPPER(inc.conv_type) = 'USER' )
1738 THEN inc.rate
1739 ELSE
1740 FII_CURRENCY.Get_Rate(inc.trx_currency
1741 ,inc.func_currency
1742 ,inc.conv_date,inc.conv_type)
1743 END rate_f,
1744 FII_CURRENCY.Get_TC_To_PGC_Rate(inc.trx_currency
1745 ,inc.conv_date
1746 ,inc.conv_type
1747 ,inc.func_currency
1748 ,inc.conv_date,inc.rate) RATE_G,
1749 FII_CURRENCY.Get_TC_To_SGC_Rate(inc.trx_currency
1750 ,inc.conv_date
1751 ,inc.conv_type
1752 ,inc.func_currency
1753 ,inc.conv_date,inc.rate) RATE_SG
1754 FROM (SELECT
1755 DISTINCT trx_currency
1756 , func_currency
1757 , conversion_date conv_date
1758 , trx_rate_type conv_type
1759 , DECODE (UPPER(trx_rate_type),'USER', trx_func_rate, NULL) rate
1760 , DECODE(upper(trx_rate_type), 'USER', chr.chr_id, NULL) chr_id
1761 FROM oki_dbi_chr_inc chr
1762 ORDER BY func_currency
1763 , conversion_date ) inc;
1764 -- selecting all the distinct curr conv , "USER" will come as well but FII will return NULL, these rows will be handled when inserting into _old table
1765
1766 l_count := SQL%ROWCOUNT ;
1767 rlog( 'Number of lines inserted into OKI_DBI_CURR_CONV : ' || to_char(l_count),2);
1768
1769 COMMIT;
1770
1771 GATHER_TABLE_STATS(tabname=>'OKI_DBI_CURR_CONV');
1772
1773
1774
1775
1776 EXCEPTION
1777 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
1778 RAISE;
1779 WHEN OTHERS THEN
1780 rlog('Error during load_currencies: Insert into OKI_DBI_CURR_CONV Failed' , 0);
1781 rlog(sqlerrm ||' '||sqlcode, 0);
1782 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
1783 fnd_message.set_name( application => 'FND'
1784 , name => 'CRM-DEBUG ERROR' ) ;
1785 fnd_message.set_token( token => 'ROUTINE'
1786 , value => 'OKI_DBI_LOAD_CLEB_PVT.load_currencies' ) ;
1787 bis_collection_utilities.put_line(fnd_message.get) ;
1788 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
1789
1790 END load_currencies;
1791
1792 /* *****************************************************************************
1793 Procedure:report_missing_currencies
1794 Description:Identifies missing Currencies and reports in a tabular format
1795 ************************************************************************** */
1796
1797 PROCEDURE report_missing_currencies IS
1798 l_rate_type VARCHAR2(1000) ;
1799 --Added by Arun for secondary global currency changes
1800 l_srate_type VARCHAR2(1000) ;
1801 l_global_currency VARCHAR2(100) ;
1802 --Added by Arun for secondary global currency changes
1803 l_sglobal_currency VARCHAR2(100) ;
1804 l_indenting VARCHAR2(10) ;
1805 l_length_contract_number NUMBER ;
1806 l_length_sts_code NUMBER ;
1807 l_length_rate_type NUMBER ;
1808 l_length_from_currency NUMBER ;
1809 l_length_to_currency NUMBER ;
1810 l_length_date NUMBER ;
1811 l_length_contract_id NUMBER ;
1812
1813 BEGIN
1814 l_rate_type := bis_common_parameters.get_rate_type ;
1815 l_srate_type := bis_common_parameters.get_secondary_rate_type ;
1816 l_global_currency := bis_common_parameters.get_currency_code;
1817 l_indenting := ' ' ;
1818 l_length_contract_number := 35 ;
1819 l_length_sts_code := 30 ;
1820 l_length_rate_type := 12 ;
1821 l_length_from_currency := 17 ;
1822 l_length_to_currency := 15 ;
1823 l_length_date := 20 ;
1824 l_length_contract_id := 50 ;
1825
1826
1827 IF(POA_CURRENCY_PKG.display_secondary_currency_yn)
1828 THEN
1829 l_sglobal_currency := BIS_COMMON_PARAMETERS.Get_Secondary_Currency_Code;
1830 END IF;
1831
1832 -- Section 1 Missing functional to global conversion rates
1833 rout(rpad('*' ,80 ,'*'), 0 ) ;
1834 rout(' Missing Functional To Global Conversion Rates', 0 ) ;
1835 rout(rpad('*' ,80 ,'*'), 0 ) ;
1836 rout(' ', 0 ) ;
1837
1838 BIS_COLLECTION_UTILITIES.writemissingrateheader ;
1839
1840 -- Missing currency summary for converting from functional to
1841 -- global currency
1842 FOR r_cur IN (SELECT distinct to_currency func_currency,
1843 decode(rate_g, -3, g_euro_start_date , curr_conv_date) conversion_date
1844 FROM oki_dbi_curr_conv
1845 WHERE curr_conv_date IS NOT NULL
1846 --AND rate_type <> 'USER' --fix for 4102597
1847 AND rate_g < 0
1848 AND rate_g NOT IN (-2,-3,-5)
1849 ORDER BY conversion_date ASC)
1850 LOOP
1851 BIS_COLLECTION_UTILITIES.writemissingrate(
1852 l_rate_type
1853 , r_cur.func_currency
1854 , l_global_currency
1855 , r_cur.conversion_date ) ;
1856 END LOOP ;
1857
1858 /********************Added by Arun for secondary global currency conversion changes****/
1859 -- Section 1a Missing functional to secondary global conversion rates
1860 IF(l_sglobal_currency IS NOT NULL)
1861 THEN
1862 rout(rpad('*' ,80 ,'*'), 0 ) ;
1863 rout(' Missing Functional To Secondary Global Conversion Rates', 0 ) ;
1864 rout(rpad('*' ,80 ,'*'), 0 ) ;
1865 rout(' ', 0 ) ;
1866
1867 BIS_COLLECTION_UTILITIES.writemissingrateheader ;
1868
1869 -- Missing currency summary for converting from functional to
1870 -- secondary global currency
1871 FOR r_cur IN (SELECT DISTINCT to_currency func_currency
1872 ,decode(rate_sg, -3, g_euro_start_date, curr_conv_date) conversion_date
1873 FROM oki_dbi_curr_conv
1874 WHERE curr_conv_date IS NOT NULL
1875 --AND rate_type <> 'USER' --fix for 4102597
1876 AND rate_sg < 0
1877 AND rate_sg NOT IN (-2,-3,-5)
1878 ORDER BY conversion_date ASC)
1879 LOOP
1880 BIS_COLLECTION_UTILITIES.writemissingrate(
1881 l_srate_type
1882 ,r_cur.func_currency
1883 ,l_sglobal_currency
1884 ,r_cur.conversion_date ) ;
1885 END LOOP ;
1886 END IF;
1887
1888 /**************changes for secondary global currency conversion ends here*******/
1889
1890 -- Section 2 Missing transactional to functional conversion rates
1891 rout(rpad(' ' ,80 ,' '), 0 ) ;
1892 rout(rpad('*' ,80 ,'*'), 0 ) ;
1893 rout(' Missing Transactional To Functional Conversion Rates'
1894 , 0 ) ;
1895 rout(rpad('*' ,80 ,'*'), 0 ) ;
1896 rout(' ', 0 ) ;
1897
1898 BIS_COLLECTION_UTILITIES.writemissingrateheader ;
1899
1900 -- Missing currency summary for converting from transactional to
1901 -- functional currency
1902 FOR r_cur IN (SELECT DISTINCT rate_type AS TRX_RATE_TYPE
1903 , from_currency as TRX_CURRENCY
1904 , to_currency as FUNC_CURRENCY
1905 , decode(rate_f, -3, g_euro_start_date, curr_conv_date) AS CONVERSION_DATE
1906 FROM oki_dbi_curr_conv
1907 WHERE curr_conv_date IS NOT NULL
1908 --AND rate_type <> 'USER' --fix for 4102597
1909 AND ( rate_f <= 0 OR rate_f IS NULL)
1910 ORDER BY conversion_date ASC )
1911 LOOP
1912 BIS_COLLECTION_UTILITIES.writemissingrate(
1913 r_cur.trx_rate_type
1914 , r_cur.trx_currency
1915 , r_cur.func_currency
1916 , r_cur.conversion_date ) ;
1917 END LOOP ;
1918
1919 rout(rpad(' ' ,110 ,' '), 0 ) ;
1920 rout(rpad('*' ,110 ,'*'), 0 ) ;
1921 rout(' Contracts With Missing Functional to Global Conversion Rates', 0 ) ;
1922 rout(rpad('*' ,110 ,'*'), 0 ) ;
1923 rout(' ', 0 ) ;
1924
1925 bis_collection_utilities.writeMissingContractHeader ;
1926
1927 FOR r_cur IN (
1928 SELECT /*+ ordered use_hash(chr) use_nl(h)
1929 parallel(inc) parallel(chr) parallel(h) */
1930 distinct h.contract_number || ' ' ||
1931 h.contract_number_modifier AS complete_contract_number
1932 , h.sts_code AS sts_code
1933 , h.id AS chr_id
1934 , inc.to_currency AS func_currency
1935 , decode(inc.rate_g, -3, g_euro_start_date, inc.curr_conv_date) conversion_date
1936 FROM oki_dbi_curr_conv inc
1937 , oki_dbi_chr_inc chr
1938 , okc_k_headers_all_b h
1939 WHERE 1 = 1
1940 --AND inc.rate_type <> 'USER'
1941 AND inc.rate_g < 0
1942 AND inc.rate_g NOT IN (-2,-3,-5)
1943 AND decode(upper(inc.rate_type),'USER',inc.chr_id,chr.chr_id) = chr.chr_id
1944 AND h.id = chr.chr_id
1945 AND chr.conversion_date = inc.curr_conv_date
1946 AND chr.trx_currency = inc.from_currency
1947 AND chr.func_currency = inc.to_currency
1948 AND chr.trx_rate_type = inc.rate_type
1949 ORDER BY conversion_date asc)
1950
1951 LOOP
1952 bis_collection_utilities.writeMissingContract(
1953 r_cur.complete_contract_number
1954 , r_cur.sts_code
1955 , r_cur.chr_id
1956 , l_rate_type
1957 , r_cur.func_currency
1958 , l_global_currency
1959 , r_cur.conversion_date );
1960 END LOOP;
1961
1962 /**************Added By Arun for secondary global currency conversion*******/
1963 IF(l_sglobal_currency IS NOT NULL)
1964 THEN
1965 rout(rpad(' ' ,110 ,' '), 0 ) ;
1966 rout(rpad('*' ,110 ,'*'), 0 ) ;
1967 rout(' Contracts With Missing Functional to Secondary Global Conversion Rates', 0 ) ;
1968 rout(rpad('*' ,110 ,'*'), 0 ) ;
1969 rout(' ', 0 ) ;
1970
1971 bis_collection_utilities.writeMissingContractHeader ;
1972
1973 FOR r_cur IN (
1974 SELECT /*+ leading(inc) use_hash(chr) use_nl(h) */
1975 distinct h.contract_number || ' ' ||
1976 h.contract_number_modifier AS complete_contract_number
1977 , h.sts_code AS sts_code
1978 , h.id AS chr_id
1979 , inc.to_currency AS func_currency
1980 , decode(inc.rate_sg,-3,g_euro_start_date,inc.curr_conv_date) AS CONVERSION_DATE
1981 FROM oki_dbi_curr_conv inc
1982 , oki_dbi_chr_inc chr
1983 , okc_k_headers_all_b h
1984 WHERE inc.rate_sg < 0
1985 AND inc.rate_sg NOT IN (-2,-3,-5)
1986 --AND inc.rate_type <> 'USER'
1987 AND decode(upper(inc.rate_type),'USER',inc.chr_id,chr.chr_id) = chr.chr_id
1988 AND h.id = chr.chr_id
1989 AND chr.conversion_date = inc.curr_conv_date
1990 AND chr.trx_currency = inc.from_currency
1991 AND chr.func_currency = inc.to_currency
1992 AND chr.trx_rate_type = inc.rate_type
1993 ORDER BY conversion_date ASC )
1994 LOOP
1995 bis_collection_utilities.writeMissingContract(
1996 r_cur.complete_contract_number
1997 ,r_cur.sts_code
1998 ,r_cur.chr_id
1999 ,l_rate_type
2000 ,r_cur.func_currency
2001 ,l_sglobal_currency
2002 ,r_cur.conversion_date);
2003 END LOOP;
2004 END IF;
2005 /**************changes for secondary global currency conversion ends here*******/
2006
2007
2008 rout(rpad(' ' ,110 ,' '), 0 ) ;
2009 rout(rpad('*' ,110 ,'*') ,0) ;
2010 rout(' Contracts With Missing Transactional To Functional Conversion Rates',0) ;
2011 rout(rpad('*' ,110 ,'*') ,0) ;
2012 rout(' ' ,0) ;
2013
2014 bis_collection_utilities.writeMissingContractHeader ;
2015
2016 -- Error occured when converting from transactional to function currency
2017 -- Write the details to the error log
2018 FOR r_cur IN (
2019 SELECT distinct /*+ leading(inc) use_hash(chr) use_nl(h) */
2020 h.contract_number || ' ' ||
2021 h.contract_number_modifier AS complete_contract_number
2022 , h.sts_code AS sts_code
2023 , chr.chr_id AS chr_id
2024 , inc.rate_type AS trx_rate_type
2025 , inc.from_currency AS trx_currency
2026 , inc.to_currency AS func_currency
2027 , decode(inc.rate_f,-3,g_euro_start_date,inc.curr_conv_date) AS conversion_date
2028 FROM oki_dbi_curr_conv inc
2029 , oki_dbi_chr_inc chr
2030 , okc_k_headers_all_b h
2031 WHERE 1 = 1
2032 --AND inc.rate_type <> 'USER'
2033 AND decode(upper(inc.rate_type),'USER',inc.chr_id,chr.chr_id) = chr.chr_id
2034 AND (inc.rate_f <= 0 OR inc.rate_f is null)
2035 AND h.id = chr.chr_id
2036 AND chr.conversion_date = inc.curr_conv_date
2037 AND chr.trx_currency = inc.from_currency
2038 AND chr.func_currency = inc.to_currency
2039 AND chr.trx_rate_type = inc.rate_type
2040 ORDER BY conversion_date)
2041 LOOP
2042 bis_collection_utilities.writeMissingContract(
2043 r_cur.complete_contract_number
2044 , r_cur.sts_code
2045 , r_cur.chr_id
2046 , r_cur.trx_rate_type
2047 , r_cur.trx_currency
2048 , r_cur.func_currency
2049 , r_cur.conversion_date);
2050 END LOOP ;
2051
2052 /**************Added By Arun for secondary global currency conversion*******/
2053 IF(l_sglobal_currency IS NOT NULL)
2054 THEN
2055 rout(rpad(' ' ,80 ,' '), 0 ) ;
2056 rout(rpad('*' ,80 ,'*'), 0 ) ;
2057 rout(' Other Errors in Functional To Secondary Global Conversion Rates', 0 ) ;
2058 rout(' Contact System Administrator with the list of Contracts', 0 ) ;
2059 rout(' Missing Functional To Secondary Global Conversion Rates',0);
2060 rout(rpad('*' ,80 ,'*'), 0 ) ;
2061 rout(' ', 0 ) ;
2062
2063
2064 FOR r_cur IN (
2065 SELECT /*+ leading(inc) use_hash(chr) use_nl(h) */
2066 distinct h.contract_number || ' ' ||
2067 h.contract_number_modifier AS complete_contract_number
2068 , h.sts_code AS sts_code
2069 , h.id AS chr_id
2070 , inc.to_currency AS func_currency
2071 , inc.curr_conv_date AS conversion_date
2072 , inc.rate_sg func_sglobal_rate
2073 FROM oki_dbi_curr_conv inc
2074 , oki_dbi_chr_inc chr
2075 , okc_k_headers_all_b h
2076 WHERE inc.rate_sg < 0
2077 --AND inc.rate_type <> 'USER'
2078 AND decode(upper(inc.rate_type),'USER',inc.chr_id,chr.chr_id) = chr.chr_id
2079 AND h.id = chr.chr_id
2080 AND chr.conversion_date = inc.curr_conv_date
2081 AND chr.trx_currency = inc.from_currency
2082 AND chr.func_currency = inc.to_currency
2083 AND chr.trx_rate_type = inc.rate_type
2084 ORDER BY conversion_date ASC )
2085 LOOP
2086
2087 bis_collection_utilities.writeMissingContract(
2088 r_cur.complete_contract_number
2089 ,r_cur.sts_code
2090 ,r_cur.chr_id
2091 ,l_rate_type
2092 ,r_cur.func_currency
2093 ,l_sglobal_currency
2094 ,r_cur.conversion_date);
2095
2096 END LOOP;
2097 END IF;
2098 /**************changes for secondary global currency conversion ends here*******/
2099
2100 EXCEPTION
2101 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2102 RAISE;
2103 WHEN OTHERS THEN
2104 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
2105 fnd_message.set_name( application => 'FND'
2106 , name => 'CRM-DEBUG ERROR' ) ;
2107 fnd_message.set_token(
2108 token => 'ROUTINE'
2109 , value => 'OKI_DBI_LOAD_CLEB_PVT.report_missing_currencies ' ) ;
2110 bis_collection_utilities.put_line(fnd_message.get) ;
2111 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR ;
2112 END report_missing_currencies ;
2113
2114 /* *****************************************************************************
2115 Procedure:process_deletes
2116 Description:Identifies deleted lines from OLTP table and deletes
2117 such records from OKI_DBI base tables
2118 ************************************************************************** */
2119 PROCEDURE process_deletes IS
2120
2121 l_oki_schema VARCHAR2(30);
2122 l_status VARCHAR2(30);
2123 l_industry VARCHAR2(30);
2124 l_sql_string VARCHAR2(4000) ;
2125 l_count NUMBER;
2126
2127 BEGIN
2128
2129 -- Delete the records from the last load from the delete table;
2130 IF (FND_INSTALLATION.GET_APP_INFO(
2131 application_short_name => 'OKI'
2132 , status => l_status
2133 , industry => l_industry
2134 , oracle_schema => l_oki_schema)) THEN
2135
2136 l_sql_string := 'TRUNCATE TABLE ' || l_oki_schema ||'.OKI_DBI_CLE_DEL' ;
2137 EXECUTE IMMEDIATE l_sql_string ;
2138
2139 INSERT /*+ APPEND */ INTO oki_dbi_cle_del
2140 (cle_id)
2141 SELECT /*+ index_ffs(f OKI_DBI_CLE_B_U1) parallel_index(f OKI_DBI_CLE_B_U1) */
2142 f.cle_id
2143 FROM oki_dbi_cle_b f
2144 WHERE cle_id not in (SELECT /*+ index_ffs(okc OKC_K_LINES_B_U1) parallel_index(okc OKC_K_LINES_B_U1) */
2145 id
2146 FROM okc_k_lines_b okc );
2147
2148 l_count := SQL%ROWCOUNT;
2149 g_del_count := l_count;
2150 rlog('Number of lines identified for delete: ' ||
2151 TO_CHAR(l_count), 2);
2152 COMMIT;
2153
2154 IF ( g_del_count > 0 ) THEN
2155 DELETE FROM oki_dbi_cle_b
2156 WHERE cle_id IN ( SELECT cle_id
2157 FROM oki_dbi_cle_del
2158 );
2159 l_count := SQL%ROWCOUNT;
2160 END IF;
2161 rlog('Number of lines deleted from oki_dbi_cle_b: ' ||
2162 TO_CHAR(l_count), 2);
2163 COMMIT;
2164 END IF ;
2165
2166 EXCEPTION
2167 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2168 RAISE;
2169 WHEN OTHERS THEN
2170 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
2171 fnd_message.set_name( application => 'FND'
2172 , name => 'CRM-DEBUG ERROR' ) ;
2173 fnd_message.set_token(
2174 token => 'ROUTINE'
2175 , value => 'OKI_DBI_LOAD_CLEB_PVT.PROCESS_DELETES ' ) ;
2176 bis_collection_utilities.put_line(fnd_message.get) ;
2177 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
2178 END process_deletes;
2179
2180 /* *****************************************************************************
2181 Procedure:load_staging
2182 Description:Procedure to merge new/modified records from OLTP into OKI DBI Staging table
2183 p_worker - current worker number
2184 p_recs_processed - number or records processed by the current worker
2185 ************************************************************************** */
2186 PROCEDURE load_staging(
2187 p_worker IN NUMBER
2188 , p_recs_processed OUT NOCOPY NUMBER
2189 ) IS
2190 l_run_date DATE ;
2191 l_location VARCHAR2(1000) ;
2192 l_count NUMBER ;
2193 l_login_id NUMBER;
2194
2195 l_annu_curr_code VARCHAR2(20);
2196 l_glob_curr_code VARCHAR2(20);
2197 l_sglob_curr_code VARCHAR2(20);
2198 l_renewal_logic VARCHAR2(10);
2199 l_balance_logic VARCHAR2(10);
2200 l_service_code number;
2201 l_warranty_code number;
2202 l_ext_warr_code number;
2203
2204 BEGIN
2205
2206
2207
2208 g_global_start_date := bis_common_parameters.get_global_start_date;
2209 g_4712_date := to_date('01/01/4712' , 'MM/DD/YYYY');
2210 l_login_id := FND_GLOBAL.login_id ;
2211 l_run_date := sysdate;
2212
2213 l_annu_curr_code := bis_common_parameters.get_annualized_currency_code;
2214 l_glob_curr_code := 'PRIMARY';/* BUg 4015406 bis_common_parameters.get_currency_code; */
2215 l_sglob_curr_code := 'SECONDARY';/*bis_common_parameters.get_secondary_currency_code;*/
2216
2217 l_location := ' Inserting modified contract lines into OKI_DBI_CLE_B_OLD
2218 table with worker '|| p_worker||' ';
2219 rlog('Populating Staging Table OKI_DBI_CLE_B_OLD with updated/created Contracts : '
2220 ||fnd_date.date_to_displayDT(sysdate), 1) ;
2221
2222 -- l_balance_logic:='EVENTDATE';
2223 -- l_balance_logic:=nvl(l_balance_logic,'CONTRDATE');
2224
2225 /* Balance logic for OI */
2226 l_balance_logic := nvl(fnd_profile.value('OKI_BAL_IDENT'),'CONTRDATE');
2227
2228 /*Renewal logic for OI */
2229 l_renewal_logic := nvl(fnd_profile.value('OKI_REN_IDENT'),'STANDARD');
2230
2231 rlog( 'Profile value for balance logic is '|| l_balance_logic , 1 );
2232 rlog( 'Profile value for renewal logic is '|| l_renewal_logic , 1 );
2233
2234 /* Effective_expire_date is populated only if the date signed is not null. Prior to 8.0,
2235 there was no scenario wherein a renewal could exist without thhe original contract was signed and so
2236 the expirations mv used r_date_signed and r_date_cancelled internally to ensure that date signed
2237 was present. Because of transfers scenarios in R12, a renewal relationship can exist without the original
2238 contract being signed and hence, to avoid unsigned contracts showing up in expirations, effective_expire_date
2239 is populated only when date signed is not null */
2240 SELECT Max(service_code),Max(warranty_code),Max(ext_warr_code)
2241 INTO l_service_code,l_warranty_code,l_ext_warr_code
2242 FROM
2243 (
2244 SELECT
2245 Decode(lty_code,'SERVICE',Id) service_code,
2246 Decode(lty_code,'WARRANTY',Id) warranty_code,
2247 Decode(lty_code,'EXT_WARRANTY',Id) ext_warr_code
2248 FROM okc_line_styles_b
2249 WHERE lty_code IN ('SERVICE','WARRANTY','EXT_WARRANTY')
2250 AND lse_parent_id IS NULL
2251 );
2252
2253 SELECT MAX(renewal_id),MAX(ren_con_id)
2254 INTO g_renewal_id,g_ren_con_id
2255 from
2256 (
2257 SELECT decode(opn_code,'RENEWAL',id,null) Renewal_ID, decode(opn_code,'REN_CON',id,null) Ren_CON_ID
2258 from okc_class_operations clsop
2259 WHERE clsop.cls_code='SERVICE'
2260 AND clsop.opn_code in ('RENEWAL','REN_CON')
2261 );
2262
2263
2264 INSERT /*+ append */ INTO oki_dbi_cle_b_old(
2265 chr_id
2266 , cle_id
2267 , cle_creation_date
2268 , inv_organization_id
2269 , authoring_org_id
2270 , application_id
2271 , Customer_party_id
2272 , salesrep_id
2273 , price_negotiated
2274 , price_negotiated_f
2275 , price_negotiated_g
2276 , price_negotiated_sg
2277 , contract_number
2278 , contract_number_modifier
2279 , buy_or_sell
2280 , scs_code
2281 , sts_code
2282 , trn_code
2283 , root_lty_code
2284 , renewal_flag
2285 , date_signed
2286 , date_cancelled
2287 , start_date
2288 , end_date
2289 , date_terminated
2290 , effective_start_date
2291 , effective_end_date
2292 , trx_func_curr_rate
2293 , func_global_curr_rate
2294 , func_sglobal_curr_rate
2295 , resource_group_id
2296 , resource_id
2297 , sle_id
2298 , service_item_id
2299 , covered_item_id
2300 , covered_item_org_id
2301 , quantity
2302 , uom_code
2303 , grace_end_date
2304 , expected_close_date
2305 , win_percent
2306 , ubt_amt
2307 , ubt_amt_f
2308 , ubt_amt_g
2309 , ubt_amt_sg
2310 , credit_amt
2311 , credit_amt_f
2312 , credit_amt_g
2313 , credit_amt_sg
2314 , override_amt
2315 , override_amt_f
2316 , override_amt_g
2317 , override_amt_sg
2318 , supp_credit
2319 , supp_credit_f
2320 , supp_credit_g
2321 , supp_credit_sg
2322 , renewal_type
2323 , term_flag
2324 , hstart_date
2325 , hend_date
2326 , annualization_factor
2327 , ubt_amt_a
2328 , credit_amt_a
2329 , override_amt_a
2330 , supp_credit_a
2331 , price_negotiated_a
2332 , worker_number
2333 , created_by
2334 , creation_Date
2335 , last_update_Date
2336 , gsd_flag
2337 -- add the four extra columns
2338 , effective_active_date
2339 , effective_term_date
2340 , effective_expire_date
2341 , termination_entry_date
2342 , falsernwlyn
2343 , curr_code
2344 , curr_code_f
2345 , hdr_order_number
2346 , hdr_sts_code
2347 , hdr_trn_code
2348 , hdr_renewal_type
2349 , hdr_date_approved
2350 , hdr_date_cancelled
2351 , hdr_date_terminated
2352 , hdr_creation_date
2353 , hdr_last_update_date
2354 , service_item_org_id
2355 , sl_line_number
2356 , sl_sts_code
2357 , sl_trn_code
2358 , sl_renewal_type
2359 , sl_start_date
2360 , sl_end_Date
2361 , sl_date_cancelled
2362 , sl_date_terminated
2363 , sl_creation_date
2364 , sl_last_update_date
2365 , order_number
2366 , unit_price_percent
2367 , unit_price
2368 , unit_price_f
2369 , unit_price_g
2370 , unit_price_sg
2371 , list_price
2372 , list_price_f
2373 , list_price_g
2374 , list_price_sg
2375 , duration_uom
2376 , duration_qty
2377 , cl_last_update_date
2378 , cov_prod_id
2379 , cov_prod_system_id
2380 , line_number
2381 , line_type
2382 , hdr_bill_site_id
2383 , hdr_ship_site_id
2384 , hdr_acct_rule_id
2385 , hdr_grace_end_date
2386 , hdr_date_signed
2387 , hdr_subsequent_renewal_type /* Added this colunm as a part of ER#5760744 */
2388 , agreement_type_code /* for ER 6062516 */
2389 , agreement_name /* for ER 6062516 */
2390 , negotiation_status /* Added this colunm as a part of ER#5950128 */
2391 , reminder /* Added this colunm as a part of ER#5950128 */
2392 , HDR_TERM_CANCEL_SOURCE /* Added as part of ER6684955 */
2393 , SL_TERM_CANCEL_SOURCE /* Added as part of ER6684955 */
2394 )
2395 ( select
2396 new.chr_id
2397 , new.cle_id
2398 , new.cle_creation_date
2399 , new.inv_organization_id
2400 , new.authoring_org_id
2401 , new.application_id
2402 , new.Customer_party_id
2403 , new.salesrep_id
2404 , new.price_negotiated
2405 , new.price_negotiated_f
2406 , new.price_negotiated_g
2407 , new.price_negotiated_sg
2408 , new.contract_number
2409 , new.contract_number_modifier
2410 , new.buy_or_sell
2411 , new.scs_code
2412 , new.sts_code
2413 , new.trn_code
2414 , new.root_lty_code
2415 , new.renewal_flag
2416 , new.date_signed
2417 , new.date_cancelled
2418 , new.start_date
2419 , new.end_date
2420 , new.date_terminated
2421 , new.effective_start_date
2422 , case when l_balance_logic='CONTRDATE' THEN new.effective_end_date
2423 ELSE NVL2(new.date_terminated, new.effective_term_date,new.effective_expire_date)
2424 end effective_end_date
2425 , new.trx_func_curr_rate
2426 , new.func_global_curr_rate
2427 , new.func_sglobal_curr_rate
2428 , new.resource_group_id
2429 , new.resource_id
2430 , new.sle_id
2431 , new.service_item_id
2432 , new.covered_item_id
2433 , case new.covered_item_id when -1 then -99 else new.inv_organization_id end covered_item_org_id
2434 , new.quantity
2435 , new.uom_code
2436 , new.grace_end_date
2437 , new.expected_close_date
2438 , new.win_percent
2439 , new.ubt_amt
2440 , new.ubt_amt_f
2441 , new.ubt_amt_g
2442 , new.ubt_amt_sg
2443 , new.credit_amt
2444 , new.credit_amt_f
2445 , new.credit_amt_g
2446 , new.credit_amt_sg
2447 , new.override_amt
2448 , new.override_amt_f
2449 , new.override_amt_g
2450 , new.override_amt_sg
2451 , new.supp_credit
2452 , new.supp_credit_f
2453 , new.supp_credit_g
2454 , new.supp_credit_sg
2455 , new.renewal_type
2456 , new.term_flag
2457 , new.hstart_date
2458 , new.hend_date
2459 , new.annualization_factor
2460 , case l_annu_curr_code when l_glob_curr_code then new.ubt_amt_g
2461 when l_sglob_curr_code then new.ubt_amt_sg
2462 else new.ubt_amt_g end * new.annualization_factor ubt_amt_a
2463 , case l_annu_curr_code when l_glob_curr_code then new.credit_amt_g
2464 when l_sglob_curr_code then new.credit_amt_sg
2465 else new.credit_amt_g end * new.annualization_factor credit_amt_a
2466 , case l_annu_curr_code when l_glob_curr_code then new.override_amt_g
2467 when l_sglob_curr_code then new.override_amt_sg
2468 else new.override_amt_g end * new.annualization_factor override_amt_a
2469 , case l_annu_curr_code when l_glob_curr_code then new.supp_credit_g
2470 when l_sglob_curr_code then new.supp_credit_sg
2471 else new.supp_credit_g end * new.annualization_factor supp_credit_a
2472 , case l_annu_curr_code when l_glob_curr_code then new.price_negotiated_g
2473 when l_sglob_curr_code then new.price_negotiated_sg
2474 else new.price_negotiated_g end * new.annualization_factor price_negotiated_a
2475 , p_worker
2476 , l_login_id
2477 , l_run_Date
2478 , l_run_date
2479 , new.gsd_flag
2480 -- add the four extra columns
2481 , new.effective_active_date
2482 , new.effective_term_date
2483 , new.effective_expire_date
2484 , new.termination_entry_date
2485 , new.falsernwlyn
2486 , new.curr_code
2487 , new.curr_code_f
2488 , new.hdr_order_number
2489 , new.hdr_sts_code
2490 , new.hdr_trn_code
2491 , new.hdr_renewal_type
2492 , new.hdr_date_approved
2493 , new.hdr_date_cancelled
2494 , new.hdr_date_terminated
2495 , new.hdr_creation_date
2496 , new.hdr_last_update_date
2497 , new.service_item_org_id
2498 , new.sl_line_number
2499 , new.sl_sts_code
2500 , new.sl_trn_code
2501 , new.sl_renewal_type
2502 , new.sl_start_date
2503 , new.sl_end_Date
2504 , new.sl_date_cancelled
2505 , new.sl_date_terminated
2506 , new.sl_creation_date
2507 , new.sl_last_update_date
2508 , new.order_number
2509 , new.unit_price_percent
2510 , new.unit_price
2511 , new.unit_price_f
2512 , new.unit_price_g
2513 , new.unit_price_sg
2514 , new.list_price
2515 , new.list_price_f
2516 , new.list_price_g
2517 , new.list_price_sg
2518 , new.duration_uom
2519 , new.duration_qty
2520 , new.cl_last_update_date
2521 , new.cov_prod_id
2522 , new.cov_prod_system_id
2523 , new.line_number
2524 , new.line_type
2525 , new.hdr_bill_site_id
2526 , new.hdr_ship_site_id
2527 , new.hdr_acct_rule_id
2528 , new.hdr_grace_end_date
2529 , new.hdr_date_signed
2530 , new.hdr_subsequent_renewal_type /* Added this colunm as a part of ER#5760744 */
2531 , new.agreement_type_code /* for ER 6062516 */
2532 , new.agreement_name /* for ER 6062516 */
2533 , new.negotiation_status /* Added this colunm as a part of ER#5950128 */
2534 , new.reminder /* Added this colunm as a part of ER#5950128 */
2535 , new.HDR_TERM_CANCEL_SOURCE /* Added as part of ER6684955 */
2536 , new.SL_TERM_CANCEL_SOURCE /* Added as part of ER6684955 */
2537 from (SELECT
2538 ilv1.chr_id
2539 , ilv1.cle_id
2540 , ilv1.cle_creation_date
2541 , ilv1.inv_organization_id
2542 , ilv1.authoring_org_id
2543 , ilv1.application_id
2544 , ilv1.Customer_party_id
2545 , ilv1.salesrep_id
2546 , ilv1.price_negotiated
2547 , ilv1.price_negotiated_f
2548 , ilv1.price_negotiated_g
2549 , ilv1.price_negotiated_sg
2550 , ilv1.contract_number
2551 , ilv1.contract_number_modifier
2552 , ilv1.buy_or_sell
2553 , ilv1.scs_code
2554 , ilv1.sts_code
2555 , ilv1.trn_code
2556 , ilv1.root_lty_code
2557 , ilv1.date_signed
2558 , ilv1.date_cancelled
2559 , ilv1.start_date
2560 , ilv1.end_date
2561 , ilv1.date_terminated
2562 , ilv1.trx_func_curr_rate
2563 , ilv1.func_global_curr_rate
2564 , ilv1.func_sglobal_curr_rate
2565 , ilv1.resource_group_id
2566 , ilv1.resource_id
2567 , ilv1.sle_id
2568 , ilv1.quantity
2569 , ilv1.uom_code
2570 /*, msi.primary_uom_code
2571 , poa_dbi_uom_pkg.convert_to_item_base_uom (
2572 ilv1.covered_item_id
2573 , ilv1.inv_organization_id
2574 , NULL
2575 , msi.primary_uom_code
2576 , ilv1.uom_code) AS trx_mst_uom_conv_rate
2577 */
2578 , ilv1.grace_end_date
2579 , ilv1.expected_close_date
2580 , ilv1.win_percent
2581 , ilv1.ubt_amt
2582 , ilv1.ubt_amt * ilv1.trx_func_curr_rate ubt_amt_f
2583 , ilv1.ubt_amt * ilv1.trx_func_curr_rate * ilv1.func_global_curr_rate ubt_amt_g
2584 , ilv1.ubt_amt * ilv1.trx_func_curr_rate * ilv1.func_sglobal_curr_rate ubt_amt_sg
2585 , ilv1.credit_amt
2586 , ilv1.credit_amt * ilv1.trx_func_curr_rate credit_amt_f
2587 , ilv1.credit_amt * ilv1.trx_func_curr_rate * ilv1.func_global_curr_rate credit_amt_g
2588 , ilv1.credit_amt * ilv1.trx_func_curr_rate * ilv1.func_sglobal_curr_rate credit_amt_sg
2589 , ilv1.override_amt
2590 , ilv1.override_amt * ilv1.trx_func_curr_rate override_amt_f
2591 , ilv1.override_amt * ilv1.trx_func_curr_rate * ilv1.func_global_curr_rate override_amt_g
2592 , ilv1.override_amt * ilv1.trx_func_curr_rate * ilv1.func_sglobal_curr_rate override_amt_sg
2593 , ilv1.supp_credit
2594 , ilv1.supp_credit * ilv1.trx_func_curr_rate supp_credit_f
2595 , ilv1.supp_credit * ilv1.trx_func_curr_rate * ilv1.func_global_curr_rate supp_credit_g
2596 , ilv1.supp_credit * ilv1.trx_func_curr_rate * ilv1.func_sglobal_curr_rate supp_credit_sg
2597 , ilv1.renewal_type
2598 , CASE WHEN ilv1.date_terminated < ilv1.start_date THEN -1 ELSE 1 END term_flag
2599 , ilv1.hstart_date
2600 , ilv1.hend_date
2601 , ilv1.annualization_factor annualization_factor
2602 , ilv1.gsd_flag
2603 , case when l_balance_logic='CONTRDATE' THEN date_terminated
2604 ELSE least(greatest(date_terminated,
2605 termination_entry_date,
2606 nvl(date_signed,date_terminated)),
2607 greatest(ilv1.date_signed,ilv1.end_date))
2608 --Usually date_terminated cannot be prsent without date signed.
2609 --This is a check for bad data available in the volume environments
2610 END effective_term_date
2611 , case when l_balance_logic='CONTRDATE' and ilv1.date_signed is not null THEN ilv1.end_date
2612 ELSE greatest(ilv1.date_signed,ilv1.end_date)
2613 END effective_expire_date
2614 , case when l_balance_logic='CONTRDATE' and ilv1.date_signed is not null THEN ilv1.start_date
2615 ELSE greatest(ilv1.date_signed,ilv1.start_date)
2616 END effective_active_date
2617 , NVL2(ilv1.date_terminated,termination_entry_date,NULL) termination_entry_date
2618 /* We change the definition given in the inner query if the value of balance logic is event dates */
2619 , case when l_balance_logic='CONTRDATE' THEN ilv1.effective_start_date
2620 else greatest(ilv1.date_signed,ilv1.start_date)
2621 end effective_start_date
2622 , ilv1.effective_end_date
2623 , ilv1.curr_code
2624 , ilv1.curr_code_f
2625 , ilv1.hdr_order_number
2626 , ilv1.hdr_sts_code
2627 , ilv1.hdr_trn_code
2628 , ilv1.hdr_renewal_type
2629 , ilv1.hdr_date_approved
2630 , ilv1.hdr_date_cancelled
2631 , ilv1.hdr_date_terminated
2632 , ilv1.hdr_creation_date
2633 , ilv1.hdr_last_update_date
2634 , (select to_number(substr(ilv1.service_item_attribs,1,instr(service_item_attribs,'#')-1)) from dual) service_item_id
2635 , (select to_number(substr(ilv1.service_item_attribs,instr(service_item_attribs,'#')+1)) from dual) service_item_org_id
2636 , ilv1.sl_line_number
2637 , ilv1.sl_sts_code
2638 , ilv1.sl_trn_code
2639 , ilv1.sl_renewal_type
2640 , ilv1.sl_start_date
2641 , ilv1.sl_end_Date
2642 , ilv1.sl_date_cancelled
2643 , ilv1.sl_date_terminated
2644 , ilv1.sl_creation_date
2645 , ilv1.sl_last_update_date
2646 , CASE l_renewal_logic when 'STANDARD' THEN ilv1.okc_renewal_flag
2647 else NVL2(ilv1.order_number,0,1) END renewal_flag
2648 , CASE WHEN l_renewal_logic= 'ORDERNO' AND ilv1.order_number IS NULL
2649 AND ilv1.okc_renewal_flag = 0 THEN 'Y'
2650 END falsernwlyn
2651 , ilv1.order_number
2652 , ilv1.unit_price_percent
2653 , ilv1.unit_price
2654 , nvl(ilv1.unit_price,0) * ilv1.trx_func_curr_rate unit_price_f
2655 , nvl(ilv1.unit_price,0) * ilv1.trx_func_curr_rate * ilv1.func_global_curr_rate unit_price_g
2656 , nvl(ilv1.unit_price,0) * ilv1.trx_func_curr_rate * ilv1.func_sglobal_curr_rate unit_price_sg
2657 , ilv1.list_price
2658 , nvl(ilv1.list_price,0) * ilv1.trx_func_curr_rate list_price_f
2659 , nvl(ilv1.list_price,0) * ilv1.trx_func_curr_rate * ilv1.func_global_curr_rate list_price_g
2660 , nvl(ilv1.list_price,0) * ilv1.trx_func_curr_rate * ilv1.func_sglobal_curr_rate list_price_sg
2661 , ilv1.duration_uom
2662 , ilv1.duration_qty
2663 , ilv1.cl_last_update_date
2664 , ilv1.line_number
2665 , ilv1.line_type
2666 , ilv1.hdr_bill_site_id
2667 , ilv1.hdr_ship_site_id
2668 , ilv1.hdr_acct_rule_id
2669 , ilv1.hdr_grace_end_date
2670 , ilv1.hdr_date_signed
2671 , CASE line_type when 'COVER_PROD' THEN
2672 to_number(substr(csi_attribs,1,instr(csi_attribs,'#')-1))
2673 ELSE -999 END cov_prod_id
2674 , CASE line_type when 'COVER_PROD' THEN
2675 to_number(substr(csi_attribs,instr(csi_attribs,'#')+1,instr(csi_attribs,'#',1,2)-instr(csi_attribs,'#')-1))
2676 ELSE -999 END cov_prod_system_id
2677 , CASE line_type when 'COVER_ITEM' THEN covered_item_id
2678 when 'COVER_PROD' then to_number(substr(csi_attribs,instr(csi_attribs,'#',-1)+1))
2679 ELSE -1 END covered_item_id
2680 , ilv1.hdr_subsequent_renewal_type /* Added this colunm as a part of ER#5760744 */
2681 , ilv1.agreement_type_code /* for ER 6062516 */
2682 , ilv1.agreement_name /* for ER 6062516 */
2683 , ilv1.negotiation_status /* Added this colunm as a part of ER#5950128 */
2684 , ilv1.reminder /* Added this colunm as a part of ER#5950128 */
2685 , ilv1.HDR_TERM_CANCEL_SOURCE /* Added as part of ER6684955 */
2686 , ilv1.SL_TERM_CANCEL_SOURCE /* Added as part of ER6684955 */
2687 FROM (SELECT /*+ ordered use_nl(root_temp,agmt) cardinality(h,10)*/
2688 h.chr_id AS chr_id
2689 , l.id AS cle_id
2690 , l.creation_date AS cle_creation_date
2691 , h.master_organization_id inv_organization_id
2692 , h.authoring_org_id
2693 , h.application_id
2694 , h.customer_party_id
2695 , h.salesrep_id
2696 , nvl(l.price_negotiated,0) price_negotiated
2697 , nvl(l.price_negotiated,0) * cur.rate_f AS price_negotiated_f
2698 , nvl(l.price_negotiated,0) * cur.rate_g AS price_negotiated_g
2699 , nvl(l.price_negotiated,0) * cur.rate_sg AS price_negotiated_sg
2700 , h.contract_number
2701 , h.contract_number_modifier
2702 , h.buy_or_sell
2703 , h.scs_code
2704 , l.sts_code
2705 , NVL(l.trn_code,h.trn_code) AS trn_code
2706 , root_temp.root_lty_code
2707 , l.annualized_factor annualization_factor
2708 , NVL2(l.date_cancelled,null,date_signed) date_signed
2709 , h.datetime_cancelled hdr_date_cancelled
2710 , sl.date_cancelled sl_date_cancelled
2711 , l.date_cancelled AS date_cancelled
2712 , h.sts_code hdr_sts_code
2713 , sl.sts_code sl_sts_code
2714 , h.start_date hstart_date
2715 , h.end_date hend_date
2716 , l.start_date AS start_date
2717 , COALESCE(l.end_date,h.end_date,g_4712_date)+1 AS end_date
2718 , NVL2(h.date_signed, l.date_terminated,NULL ) AS date_terminated
2719 , NVL2(h.date_signed, l.start_date, NULL) effective_start_date
2720 , NVL2(h.date_signed, LEAST( COALESCE(l.end_date
2721 , h.end_date
2722 , g_4712_date) +1
2723 ,COALESCE(l.date_terminated
2724 , h.date_terminated
2725 , g_4712_date))
2726 , NULL) AS effective_end_date
2727 , cur.rate_f AS trx_func_curr_rate
2728 , cur.rate_g / decode(cur.rate_f,0,-1,cur.rate_f) AS func_global_curr_rate
2729 , cur.rate_sg / decode(cur.rate_f,0,-1,cur.rate_f) AS func_sglobal_curr_rate
2730 , h.resource_group_id resource_group_id
2731 , h.resource_id resource_id
2732 , sl.id AS sle_id
2733 , CASE root_temp.lty_code when 'COVER_ITEM' then TO_NUMBER (itm2.object1_id1) END covered_item_id
2734 , itm2.number_of_items quantity
2735 , itm2.uom_code uom_code
2736 , CASE WHEN h.end_date = l.end_date
2737 THEN h.grace_end_date
2738 ELSE NULL
2739 END grace_end_date
2740 , h.est_rev_date AS expected_close_date
2741 , h.est_rev_percent AS win_percent
2742 -- terminated amounts
2743 , nvl(oksl.ubt_amount,0) ubt_amt
2744 , nvl(oksl.credit_amount,0) credit_amt
2745 , nvl(oksl.override_amount,0) override_amt
2746 , nvl(oksl.suppressed_credit,0) supp_credit
2747 , h.renewal_type_code hdr_renewal_type
2748 , CASE NVL(h.renewal_type_code,sl.line_renewal_Type_code) when 'DNR' THEN 'DNR'
2749 ELSE l.line_renewal_type_code
2750 END renewal_type
2751 , h.gsd_flag
2752 -- we take last update date as the candidate for termination entry date
2753 , l.last_update_date termination_entry_date
2754 , h.trx_currency curr_code
2755 , h.func_currency curr_code_f
2756 , h.order_number hdr_order_number
2757 , ( select object1_id1||'#' || object1_id2 from okc_k_items
2758 where cle_id = sl.id and rownum=1) service_item_attribs
2759 /* For line lelvel order number */
2760 ,CASE WHEN root_lty_code <> 'WARRANTY' then (
2761 /* rel objs has multiple entries for the same order number */
2762 Select oehdr.order_number order_number
2763 from oe_order_headers_all oehdr
2764 , oe_order_lines_all oelin
2765 , okc_k_rel_objs okcrel
2766 WHERE okcrel.object1_id1 = oelin.line_id
2767 AND oehdr.header_id = oelin.header_id
2768 AND okcrel.cle_id = l.id
2769 AND rownum=1 )
2770 /* End of for line level order number */
2771 ELSE h.order_number END order_number
2772 , NVL(( SELECT 1
2773 FROM okc_operation_lines okl
2774 , okc_operation_instances opins
2775 WHERE okl.oie_id=opins.id
2776 AND opins.cop_id in (g_renewal_id,g_ren_con_id)
2777 AND object_cle_id IS NOT NULL
2778 AND subject_cle_id = l.id
2779 AND rownum = 1),0) okc_renewal_flag
2780 , CASE root_temp.lty_code WHEN 'COVER_PROD' THEN
2781 ( select instance_id || '#' || system_id || '#' || inventory_item_id
2782 from csi_item_instances where instance_id = itm2.object1_id1)
2783 END csi_attribs
2784 , h.trn_code hdr_trn_code
2785 , h.date_approved hdr_date_approved
2786 , h.date_terminated hdr_date_terminated
2787 , h.creation_Date hdr_creation_date
2788 , h.last_update_date hdr_last_update_date
2789 , sl.line_number sl_line_number
2790 , NVL(sl.trn_code,h.trn_code) sl_trn_code
2791 , CASE WHEN h.renewal_type_code = 'DNR' THEN 'DNR'
2792 ELSE sl.line_renewal_type_code
2793 END sl_renewal_type
2794 , sl.start_date sl_start_date
2795 , sl.end_date sl_end_Date
2796 , sl.date_terminated sl_date_terminated
2797 , sl.creation_date sl_creation_date
2798 , sl.last_update_date sl_last_update_date
2799 , case oksl.toplvl_operand_code when 'PERCENT_PRICE'
2800 then oksl.toplvl_operand_val
2801 end unit_price_percent
2802 , nvl(l.price_unit,0) unit_price
2803 , nvl(l.line_list_price,0) list_price
2804 , oksl.toplvl_uom_code duration_uom
2805 , oksl.toplvl_price_qty duration_qty
2806 , l.last_update_date cl_last_update_date
2807 , sl.line_number ||'.' || l.line_number line_number
2808 , root_temp.lty_code line_type
2809 , h.Bill_to_site_use_id hdr_bill_site_id
2810 , h.Ship_to_site_use_id hdr_ship_site_id
2811 , h.Acct_rule_id hdr_acct_rule_id
2812 , h.grace_end_date hdr_grace_end_date
2813 , h.date_signed hdr_date_signed
2814 , h.renewal_type_code hdr_subsequent_renewal_type /* Added this colunm as a part of ER#5760744 */
2815 , agmt.agreement_type_code /* for ER 6062516 */
2816 , agmt.agreement_name /* for ER 6062516 */
2817 , h.negotiation_status negotiation_status /* Added this colunm as a part of ER#5950128 */
2818 , decode(h.reminder, 'Y', 'Enable', 'N', 'Disable', h.reminder) reminder /* Added this colunm as a part of ER#5950128 */
2819 , h.HDR_TERM_CANCEL_SOURCE HDR_TERM_CANCEL_SOURCE
2820 , NVL(sl.term_cancel_source, h.HDR_TERM_CANCEL_SOURCE) SL_TERM_CANCEL_SOURCE
2821 FROM
2822 oki_dbi_chr_inc h
2823 , oki_dbi_curr_conv cur
2824 , okc_k_lines_b sl
2825 , okc_k_lines_b l
2826 , ( select /*+ no_merge */ cii.instance_id
2827 , qpl.meaning agreement_type_code
2828 , oat.name agreement_name
2829 from oe_agreements_tl oat,
2830 qp_lookups qpl,
2831 oe_agreements_b oab,
2832 csi_item_instances cii
2833 where oab.agreement_id = oat.agreement_id
2834 and cii.last_oe_agreement_id = oab.agreement_id(+)
2835 and oat.language = userenv('LANG')
2836 and qpl.lookup_type(+) = 'QP_AGREEMENT_TYPE'
2837 and qpl.lookup_code(+) = oab.agreement_type_code
2838 ) agmt /* for ER 6062516 */
2839 , (Select id,lty_code,
2840 case lse_parent_id when l_service_code then 'SERVICE'
2841 when l_warranty_code then 'WARRANTY'
2842 when l_ext_warr_code then 'EXT_WARRANTY'
2843 END root_lty_code
2844 FROM okc_line_styles_b n
2845 where lse_parent_id = l_service_code
2846 or lse_parent_id = l_warranty_code
2847 or lse_parent_id = l_ext_warr_code ) root_temp
2848 , okc_k_items itm2
2849 , oks_k_lines_b oksl
2850 WHERE 1 = 1
2851 AND h.worker_number = p_worker
2852 AND (h.chr_id = cur.chr_id OR upper(cur.rate_type) <> 'USER')
2853 AND h.conversion_date = cur.curr_conv_date
2854 AND h.trx_currency = cur.from_currency
2855 AND h.func_currency = cur.to_currency
2856 AND h.trx_rate_type = cur.rate_type
2857 AND h.chr_id = sl.chr_id
2858 AND sl.id = l.cle_id
2859 AND l.price_level_ind = 'Y'
2860 AND l.lse_id = root_temp.id
2861 AND l.id = itm2.cle_id
2862 AND l.id = oksl.cle_id
2863 AND itm2.object1_id1 = agmt.instance_id(+)
2864 ) ilv1
2865 )new
2866 WHERE NOT EXISTS
2867 (
2868 SELECT 1 FROM oki_dbi_cle_b old
2869 WHERE new.cle_id = old.cle_id
2870 AND new.cl_last_update_date = old.cl_last_update_date
2871 AND new.sl_last_update_date = old.sl_last_update_date
2872 AND new.hdr_last_update_date = old.hdr_last_update_date
2873 AND NVL(new.salesrep_id,-9999) = NVL(old.salesrep_id,-9999)
2874 AND new.renewal_flag = old.renewal_flag
2875 AND new.term_flag = old.term_flag
2876 AND NVL(new.ubt_amt,0) = NVL(old.ubt_amt,0)
2877 AND NVL(new.credit_amt,0) = NVL(old.credit_amt,0)
2878 AND NVL(new.override_amt,0) = NVL(old.override_amt,0)
2879 AND NVL(new.trx_func_curr_rate,-1) = NVL(old.trx_func_curr_rate,-1)
2880 AND NVL(new.func_global_curr_rate,-1) = NVL(old.func_global_curr_rate,-1)
2881 AND NVL(new.func_sglobal_curr_rate,-1) = NVL(old.func_sglobal_curr_rate,-1)
2882 AND NVL(new.resource_group_id, -9999) = NVL(old.resource_group_id, -9999)
2883 AND NVL(new.resource_id, -1) = NVL(old.resource_id, -1)
2884 AND NVL(new.grace_end_date, l_run_date) = NVL(old.grace_end_date, l_run_date)
2885 AND NVL(new.expected_close_date, l_run_date) = NVL(old.expected_close_date, l_run_date)
2886 AND new.curr_code_f = old.curr_code_f
2887 AND NVL(new.hdr_order_number,-99999) = NVL(old.hdr_order_number,-99999)
2888 AND NVL(new.order_number,-99999) = NVL(old.order_number,-99999)
2889 AND NVL(new.unit_price_percent,-99999) = NVL(old.unit_price_percent,-99999)
2890 AND NVL(new.list_price,-99999) = NVL(old.list_price,-99999)
2891 AND NVL(new.duration_uom,'ABC') = NVL(old.duration_uom,'ABC')
2892 AND NVL(new.duration_qty,-99999) = NVL(old.duration_qty,-99999)
2893 AND NVL(new.cov_prod_id,-99999) = NVL(old.cov_prod_id,-99999)
2894 AND NVL(new.cov_prod_system_id,-99999) = NVL(old.cov_prod_system_id,-99999)
2895 AND nvl(new.hdr_acct_rule_id,-99999) = old.hdr_acct_rule_id
2896 AND new.service_item_org_id = old.service_item_org_id
2897 -- AND new.sts_code = old.sts_code
2898 -- AND new.hstart_date = old.hstart_date
2899 -- AND new.hend_date = old.hend_date
2900 -- AND NVL(new.hdr_date_approved,l_run_date) = NVL(old.hdr_date_approved,l_run_date)
2901 -- AND NVL(new.hdr_date_cancelled,l_run_date) = NVL(old.hdr_date_cancelled,l_run_date)
2902 -- AND NVL(new.hdr_date_terminated,l_run_date) = NVL(old.hdr_date_terminated,l_run_date)
2903 -- AND NVL(new.sl_start_date,l_run_date) = NVL(old.sl_start_date,l_run_date)
2904 -- AND NVL(new.sl_end_Date,l_run_date) = NVL(old.sl_end_Date,l_run_date)
2905 -- AND NVL(new.sl_date_cancelled,l_run_date) = NVL(old.sl_date_cancelled,l_run_date)
2906 -- AND NVL(new.sl_date_terminated,l_run_date) = NVL(old.sl_date_terminated,l_run_date)
2907 -- AND new.start_date = old.start_date
2908 -- AND new.end_date = old.end_date
2909 -- AND NVL(new.date_signed,l_run_date) = NVL(old.date_signed,l_run_date)
2910 -- AND NVL(new.date_cancelled,l_run_date) = NVL(old.date_cancelled,l_run_date)
2911 -- AND NVL(new.date_terminated,l_run_date) = NVL(old.date_terminated,l_run_date)
2912 -- AND NVL(new.price_negotiated,0) = NVL(old.price_negotiated,0)
2913 -- AND NVL(new.trn_code,'X') = NVL(old.trn_code,'X')
2914 -- AND new.renewal_type = old.renewal_type
2915 -- AND new.inv_organization_id = old.inv_organization_id
2916 -- AND NVL(new.supp_credit,0) = NVL(old.supp_credit,0)
2917 -- AND NVL(new.root_lty_code,'X') = NVL(old.root_lty_code,'X')
2918 -- AND new.customer_party_id = old.customer_party_id
2919 -- AND NVL(new.service_item_id, -1) = NVL(old.service_item_id, -1)
2920 -- AND NVL(new.covered_item_id, -1) = NVL(old.covered_item_id, -1)
2921 -- AND NVL(new.win_percent, -1) = NVL(old.win_percent, -1)
2922 -- AND new.curr_code = old.curr_code
2923 -- AND new.hdr_sts_code = old.hdr_sts_code
2924 -- AND NVL(new.hdr_trn_code,'ABC') = NVL(old.hdr_trn_code,'ABC')
2925 -- AND NVL(new.hdr_renewal_type,'ABC') = NVL(old.hdr_renewal_type,'ABC')
2926 -- AND new.sl_line_number = old.sl_line_number
2927 -- AND new.sl_sts_code = old.sl_sts_code
2928 -- AND NVL(new.sl_trn_code,'ABC') = NVL(old.sl_trn_code,'ABC')
2929 -- AND NVL(new.sl_renewal_type,'ABC') = NVL(old.sl_renewal_type,'ABC')
2930 -- AND NVL(new.unit_price,-99999) = NVL(old.unit_price,-99999)
2931 -- AND nvl(new.hdr_bill_site_id,-99999) = old.hdr_bill_site_id
2932 -- AND nvl(new.hdr_ship_site_id,-99999) = old.hdr_ship_site_id
2933 -- AND new.line_number = old.line_number
2934 -- AND new.line_type = old.line_type
2935 -- AND NVL(new.effective_start_date,l_run_date) = NVL(old.effective_start_date,l_run_date)
2936 -- AND NVL(new.effective_end_date,l_run_date) = NVL(old.effective_end_date,l_run_date)
2937 -- AND NVL(new.price_negotiated_f,0) = NVL(old.price_negotiated_f,0)
2938 -- AND NVL(new.price_negotiated_g,0) = NVL(old.price_negotiated_g,0)
2939 -- AND NVL(new.price_negotiated_sg,0) = NVL(old.price_negotiated_sg,0)
2940 -- AND NVL(new.ubt_amt_f,0) = NVL(old.ubt_amt_f,0)
2941 -- AND NVL(new.ubt_amt_g,0) = NVL(old.ubt_amt_g,0)
2942 -- AND NVL(new.ubt_amt_sg,0) = NVL(old.ubt_amt_sg,0)
2943 -- AND NVL(new.credit_amt_f,0) = NVL(old.credit_amt_f,0)
2944 -- AND NVL(new.credit_amt_g,0) = NVL(old.credit_amt_g,0)
2945 -- AND NVL(new.credit_amt_sg,0) = NVL(old.credit_amt_sg,0)
2946 -- AND NVL(new.override_amt_f,0) = NVL(old.override_amt_f,0)
2947 -- AND NVL(new.override_amt_g,0) = NVL(old.override_amt_g,0)
2948 -- AND NVL(new.override_amt_sg,0) = NVL(old.override_amt_sg,0)
2949 -- AND NVL(new.supp_credit_f,0) = NVL(old.supp_credit_f,0)
2950 -- AND NVL(new.supp_credit_g,0) = NVL(old.supp_credit_g,0)
2951 -- AND NVL(new.supp_credit_sg,0) = NVL(old.supp_credit_sg,0)
2952 -- AND NVL(new.sle_id, -1) = NVL(old.sle_id, -1)
2953 -- AND NVL(new.quantity, -1) = NVL(old.quantity, -1)
2954 -- AND NVL(new.uom_code, 'X') = NVL(old.uom_code, 'X')
2955 -- AND NVL(new.unit_price_f,-99999) = NVL(old.unit_price_f,-99999)
2956 -- AND NVL(new.unit_price_g,-99999) = NVL(old.unit_price_g,-99999)
2957 -- AND NVL(new.unit_price_sg,-99999) = NVL(old.unit_price_sg,-99999)
2958 -- AND NVL(new.list_price_f,-99999) = NVL(old.list_price_f,-99999)
2959 -- AND NVL(new.list_price_g,-99999) = NVL(old.list_price_g,-99999)
2960 -- AND NVL(new.list_price_sg,-99999) = NVL(old.list_price_sg,-99999)
2961 -- AND NVL(new.effective_term_date,l_run_date) = NVL(old.effective_term_date,l_run_date)
2962 -- AND NVL(new.effective_expire_date,l_run_date) = NVL(old.effective_expire_date,l_run_date)
2963 -- AND NVL(new.effective_active_date,l_run_date) = NVL(old.effective_active_date,l_run_date)
2964 -- AND NVL(new.falsernwlyn,'ABC') = NVL(old.falsernwlyn,'ABC')
2965 -- AND NVL(new.hdr_grace_end_date, l_run_date) = NVL(old.hdr_grace_end_date, l_run_date)
2966 -- AND NVL(new.hdr_date_signed,l_run_date) = NVL(old.hdr_date_signed,l_run_date)
2967 ));
2968 p_recs_processed := SQL%ROWCOUNT ;
2969 rlog( 'Number of lines inserted into OKI_DBI_CLE_B_OLD is '||p_recs_processed,2);
2970 rlog('Load of Staging Table OKI_DBI_CLE_B_OLD for updated/created Contracts completed: '
2971 ||fnd_date.date_to_displayDT(sysdate), 1) ;
2972 COMMIT;
2973
2974 EXCEPTION
2975 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
2976 RAISE;
2977 WHEN OTHERS THEN
2978 rlog('Error during load_staging: ' || l_location || ' Failed' , 0);
2979 rlog(sqlerrm ||' '||sqlcode, 0) ;
2980 rlog(l_location , 0) ;
2981 fnd_message.set_name( application => 'FND'
2982 , name => 'CRM-DEBUG ERROR' ) ;
2983 fnd_message.set_token( token => 'ROUTINE'
2984 , value => 'OKI_DBI_LOAD_CLEB_PVT.LOAD_STAGING ' ) ;
2985 bis_collection_utilities.put_line(fnd_message.get) ;
2986 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR ;
2987 END load_staging;
2988
2989 /*******************************************************************************
2990 Procedure: populate_ren_rel
2991 Description: populate renewal relationship needs to be done in three phases
2992 and in the order of delta changes (1), latest relations (2),
2993 and updates due to deletes(3). This needs to be done in the same
2994 order to avoid any overriding of the relathip information.
2995 1. Identify lines that got affected due to delta changes
2996 2. Apply on top of this latest relations
2997 3. Identify new relation ships to lines affected due to deletes.
2998 Changing the order may override the actual relationships
2999 Parameters: p_no_of_workers Number of sub workers entered by the user
3000 *******************************************************************************/
3001 PROCEDURE populate_ren_rel(p_no_of_workers IN NUMBER) IS
3002
3003 l_start_date DATE;
3004 l_end_date DATE;
3005 l_run_date DATE;
3006 l_batch_size NUMBER;
3007 l_sql_string VARCHAR2(4000);
3008 l_oki_schema VARCHAR2(30);
3009 l_status VARCHAR2(30);
3010 l_industry VARCHAR2(30);
3011 l_count NUMBER;
3012 l_location VARCHAR2(1000);
3013
3014 BEGIN
3015 l_start_date := OKI_DBI_LOAD_CLEB_PVT.g_start_date ;
3016 l_end_date := OKI_DBI_LOAD_CLEB_PVT.g_end_date ;
3017 l_run_date := OKI_DBI_LOAD_CLEB_PVT.g_run_date ;
3018
3019 rlog('Populating table OKI_DBI_REN_INC due to ''Operation Lines Changes'': '
3020 ||fnd_date.date_to_displayDT(sysdate), 1) ;
3021 /* Changes to operation_lines */
3022 /* Changed for appsperf bug 4913262 */
3023 MERGE /*+ INDEX(B OKI_DBI_REN_INC_U1) */ INTO OKI_DBI_REN_INC b
3024 USING
3025 (SELECT s1.cle_id
3026 , s1.r_cle_id
3027 , case when g_true_incr = 1 then mod(rownum-1,p_no_of_workers)+1 else -99 end worker_number
3028 FROM (SELECT /*+ ordered use_nl(OPINS,CLSOP)*/
3029 rel.object_cle_id cle_id
3030 , MAX(rel.subject_cle_id) KEEP (DENSE_RANK LAST
3031 ORDER BY rel.last_update_date) r_cle_id
3032 FROM OKC_OPERATION_LINES REL ,
3033 OKC_OPERATION_INSTANCES OPINS,
3034 OKC_CLASS_OPERATIONS CLSOP
3035 WHERE 1 = 1
3036 AND rel.oie_id=opins.id
3037 AND opins.cop_id=clsop.id
3038 AND clsop.cls_code='SERVICE'
3039 AND clsop.opn_code in ('RENEWAL','REN_CON')
3040 AND
3041 ( EXISTS
3042 (select null
3043 FROM oki_dbi_cle_b b
3044 where b.cle_id = rel.object_cle_id)
3045 OR
3046 EXISTS
3047 (select null
3048 FROM oki_dbi_cle_b_old o
3049 where o.cle_Id = rel.object_cle_id)
3050 )
3051 AND rel.object_cle_id IS NOT NULL
3052 AND rel.subject_cle_id IS NOT NULL
3053 AND rel.last_update_date BETWEEN l_start_date AND l_end_date
3054 GROUP BY rel.object_cle_id) s1
3055 ) s
3056 ON (b.cle_id = s.cle_id)
3057 WHEN MATCHED THEN
3058 UPDATE SET
3059 r_cle_id = s.r_cle_id
3060 , worker_number = s.worker_number
3061 WHEN NOT MATCHED THEN
3062 INSERT(
3063 cle_id
3064 , r_cle_id
3065 , worker_number
3066 ) VALUES (
3067 s.cle_id
3068 , s.r_cle_id
3069 , s.worker_number
3070 );
3071
3072 l_count := SQL%ROWCOUNT;
3073
3074 rlog( 'Number of lines inserted into OKI_DBI_REN_INC : '
3075 ||TO_CHAR(l_count),2);
3076 rlog('Load of table OKI_DBI_REN_INC due to ''Operation Lines Changes'' completed: '
3077 ||fnd_date.date_to_displayDT(sysdate), 1) ;
3078 COMMIT;
3079
3080 IF g_true_incr = 0 THEN
3081
3082 -- GATHER_TABLE_STATS(TABNAME=>'OKI_DBI_REN_INC');
3083
3084 rlog('Updating table OKI_DBI_REN_INC due to ''Operation Lines Changes'': '
3085 ||fnd_date.date_to_displayDT(sysdate), 1) ;
3086
3087 /* Changes to operation_lines */
3088 MERGE /*+ INDEX(B OKI_DBI_REN_INC_U1) */ INTO OKI_DBI_REN_INC b
3089 USING
3090 (SELECT s1.cle_id
3091 , s1.r_cle_id
3092 , mod(rownum-1,p_no_of_workers)+1 worker_number
3093 FROM (SELECT /*+ ordered index(b) use_nl(rel,opins) */
3094 rel.object_cle_id cle_id
3095 , MAX (rel.subject_cle_id)KEEP (DENSE_RANK LAST ORDER BY rel.last_update_date) r_cle_id
3096 FROM oki_dbi_ren_inc b,
3097 okc_operation_lines rel
3098 ,okc_operation_instances opins,
3099 okc_class_operations clsop
3100 WHERE 1 = 1
3101 AND rel.oie_id=opins.id
3102 AND opins.cop_id=clsop.id
3103 AND clsop.cls_code='SERVICE'
3104 AND clsop.opn_code in ('RENEWAL','REN_CON')
3105 AND b.worker_number = -99
3106 AND b.cle_id = rel.object_cle_id
3107 AND rel.object_cle_id IS NOT NULL
3108 AND rel.subject_cle_id IS NOT NULL
3109 GROUP BY rel.object_cle_id
3110 ) s1
3111 ) s
3112 ON (b.cle_id = s.cle_id)
3113 WHEN MATCHED THEN
3114 UPDATE SET
3115 r_cle_id = s.r_cle_id
3116 , worker_number = s.worker_number
3117 WHEN NOT MATCHED THEN
3118 INSERT(
3119 cle_id
3120 , r_cle_id
3121 , worker_number
3122 ) VALUES (
3123 s.cle_id
3124 , s.r_cle_id
3125 , s.worker_number
3126 );
3127 l_count := SQL%ROWCOUNT;
3128
3129 rlog('Number of lines updated into OKI_DBI_REN_INC : '
3130 ||TO_CHAR(l_count),2);
3131 rlog('Updation of table OKI_DBI_REN_INC due to ''Operation Lines Changes'' completed: '
3132 ||fnd_date.date_to_displayDT(sysdate), 1) ;
3133 COMMIT;
3134 END IF;
3135
3136 /* Lines affected due to deletes */
3137 IF (g_del_count > 0 ) then
3138
3139 rlog('Populating table OKI_DBI_REN_INC due to ''Deletes'': '
3140 ||fnd_date.date_to_displayDT(sysdate), 1) ;
3141
3142 MERGE INTO OKI_DBI_REN_INC b
3143 USING
3144 (SELECT cle_id
3145 , r_cle_id
3146 , mod(rownum-1,p_no_of_workers)+1 worker_number
3147 FROM (SELECT /*+ ordered use_nl(del,rl,opins,clsop) */
3148 del.cle_id
3149 , MAX(rl.subject_cle_id) KEEP (DENSE_RANK LAST
3150 ORDER BY rl.last_update_date) r_cle_id
3151 FROM oki_dbi_cle_del del1
3152 , oki_dbi_cle_b del
3153 , okc_operation_lines rl
3154 , okc_operation_instances opins
3155 , okc_class_operations clsop
3156 WHERE 1 = 1
3157 AND rl.oie_id=opins.id
3158 AND opins.cop_id=clsop.id
3159 AND clsop.cls_code='SERVICE'
3160 AND clsop.opn_code in ('RENEWAL','REN_CON')
3161 AND del.r_cle_id = del1.cle_id
3162 AND rl.object_cle_id(+) = del.cle_id
3163 AND ( ( rl.subject_cle_id IS NOT NULL
3164 AND rl.object_cle_id IS NOT NULL)
3165 OR( rl.subject_cle_id IS NULL
3166 AND rl.object_cle_id IS NULL
3167 AND rl.object_chr_id IS NULL
3168 AND rl.subject_chr_id IS NULL))
3169 GROUP BY del.cle_id) s1
3170 ) s
3171 ON (b.cle_id = s.cle_id)
3172 WHEN MATCHED THEN
3173 UPDATE SET
3174 r_cle_id = s.r_cle_id
3175 , worker_number = s.worker_number
3176 WHEN NOT MATCHED THEN
3177 INSERT(
3178 cle_id
3179 , r_cle_id
3180 , worker_number
3181 ) VALUES (
3182 s.cle_id
3183 , s.r_cle_id
3184 , s.worker_number
3185 );
3186
3187 l_count := SQL%ROWCOUNT;
3188 rlog( 'Number of lines inserted into OKI_DBI_REN_INC : '
3189 ||TO_CHAR(l_count),2);
3190 rlog('Load of table OKI_DBI_REN_INC due to ''Deletes'' completed: '
3191 ||fnd_date.date_to_displayDT(sysdate), 1) ;
3192 COMMIT;
3193 END IF;
3194 GATHER_TABLE_STATS( TABNAME=>'OKI_DBI_REN_INC' );
3195
3196
3197 EXCEPTION
3198 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3199 RAISE;
3200 WHEN OTHERS THEN
3201 rlog('Error in populate_ren_rel: Insert into OKI_DBI_REN_INC FAILED', 0);
3202 rlog(sqlerrm ||' '||sqlcode, 0);
3203 rlog(l_location , 0);
3204 fnd_message.set_name( application => 'FND'
3205 , name => 'CRM-DEBUG ERROR' ) ;
3206 fnd_message.set_token(
3207 token => 'ROUTINE'
3208 , value => 'OKI_DBI_LOAD_CLEB_PVT.POPULATE_REN_REL' ) ;
3209 bis_collection_utilities.put_line(fnd_message.get) ;
3210 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
3211 END populate_ren_rel;
3212
3213 /*******************************************************************************
3214 Procedure: UPdate_RHS
3215 Description: Update the right hand side (renewal information) by copying
3216 the left hand side (original information).
3217 Parameters:
3218 p_worker : current worker number
3219 p_no_of_workers: total number of workers requested by the user
3220 p_recs_processed: total number of records processed by the current woker
3221 *******************************************************************************/
3222 PROCEDURE Update_RHS
3223 ( p_worker IN NUMBER
3224 , p_no_of_workers IN NUMBER
3225 , p_recs_processed OUT NOCOPY NUMBER
3226 ) IS
3227 l_batch_size NUMBER;
3228 l_sql_string VARCHAR2(4000);
3229 l_oki_schema VARCHAR2(30);
3230 l_status VARCHAR2(30);
3231 l_industry VARCHAR2(30);
3232 l_count NUMBER;
3233 l_location VARCHAR2(1000) ;
3234 l_start_date date;
3235 l_end_date date;
3236 l_temp exception;
3237 l_sts_count number;
3238
3239 BEGIN
3240
3241 l_location := ' Updating Covered Lines in OKI_DBI_CLE_B_OLD with Renewals: ' ;
3242
3243 rlog('Updating Staging Table OKI_DBI_CLE_B_OLD with renewal information : '
3244 ||fnd_date.date_to_displayDT(sysdate), 1) ;
3245 UPDATE oki_dbi_cle_b_old b set (
3246 r_chr_id
3247 , r_cle_id
3248 , r_date_signed
3249 , r_date_cancelled
3250 )=
3251 (SELECT /*+ ordered index(ren_inc) use_nl(old) */
3252 chr_id
3253 , old.cle_id
3254 , date_signed
3255 , date_cancelled
3256 FROM oki_dbi_ren_inc ren_inc,
3257 oki_dbi_cle_b_old old
3258 WHERE ren_inc.r_cle_id = old.cle_id (+)
3259 AND ren_inc.worker_number = p_worker
3260 AND ren_inc.cle_id = b.cle_id)
3261 WHERE EXISTS (SELECT/*+ ordered index(ren_inc) use_nl(old) */ 1
3262 FROM oki_dbi_cle_b_old old,
3263 oki_dbi_ren_inc ren_inc
3264 WHERE ren_inc.r_cle_id = old.cle_id (+)
3265 AND ren_inc.worker_number = p_worker
3266 AND ren_inc.cle_id = b.cle_id);
3267
3268 p_recs_processed := SQL%ROWCOUNT ;
3269
3270 rlog( 'Number of lines updated into OKI_DBI_CLE_B_OLD with renewal information is '
3271 || TO_CHAR(p_recs_processed),2) ;
3272 rlog('Updation of Staging Table OKI_DBI_CLE_B_OLD with renewal information completed: '
3273 ||fnd_date.date_to_displayDT(sysdate), 1) ;
3274
3275 COMMIT;
3276 EXCEPTION
3277 WHEN OTHERS THEN
3278 rlog('Error in update_RHS: ',0);
3279 rlog(SQLERRM ||' '||SQLCODE, 0) ;
3280 rlog(l_location || ' Failed', 0) ;
3281 fnd_message.set_name( application => 'FND'
3282 , name => 'CRM-DEBUG ERROR' ) ;
3283 fnd_message.set_token(
3284 token => 'ROUTINE'
3285 , value => 'OKI_DBI_LOAD_CLEB_PVT.Update_RHS ' ) ;
3286 bis_collection_utilities.put_line(fnd_message.get) ;
3287 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR ;
3288 END update_RHS ;
3289
3290 /*******************************************************************************
3291 Procedure: UPdate_LHS
3292 Description: Update the left hand side (original information) by copying
3293 the right hand side (renewal information).
3294 Parameters: p_worker: The worker number of the current worker that calls this procedure
3295 p_no_of_workers: The total number of workers requested by the user
3296 p_recs_processed:The number of records processed by the current worker
3297 *******************************************************************************/
3298 PROCEDURE Update_LHS(
3299 p_worker IN NUMBER
3300 , p_no_of_workers IN NUMBER
3301 , p_recs_processed OUT NOCOPY NUMBER
3302 )
3303 IS
3304
3305 l_sql_string VARCHAR2(4000);
3306 l_count NUMBER;
3307 l_location VARCHAR2(1000);
3308 l_sts_count NUMBER;
3309
3310 BEGIN
3311
3312 l_location := 'Updating Covered Lines in OKI_DBI_CLE_B_OLD with Original Information: ' ;
3313
3314 rlog('Updating Staging Table OKI_DBI_CLE_B_OLD with original information : '
3315 ||fnd_date.date_to_displayDT(sysdate), 1) ;
3316
3317 UPDATE oki_dbi_cle_b_old b SET
3318 ( p_chr_id
3319 , p_cle_id
3320 , p_price_negotiated
3321 , p_price_negotiated_f
3322 , p_price_negotiated_g
3323 , p_price_negotiated_sg
3324 , p_grace_end_date
3325 , p_ubt_amt
3326 , p_ubt_amt_f
3327 , p_ubt_amt_g
3328 , p_ubt_amt_sg
3329 , p_credit_amt
3330 , p_credit_amt_f
3331 , p_credit_amt_g
3332 , p_credit_amt_sg
3333 , p_override_amt
3334 , p_override_amt_f
3335 , p_override_amt_g
3336 , p_override_amt_sg
3337 , p_supp_credit
3338 , p_supp_credit_f
3339 , p_supp_credit_g
3340 , p_supp_credit_sg
3341 , p_end_date
3342 , p_term_flag
3343 , p_price_negotiated_a
3344 , p_ubt_amt_a
3345 , p_credit_amt_a
3346 , p_override_amt_a
3347 , p_supp_credit_a) =
3348 (SELECT /*+ ordered index(prev_inc) use_nl(cle) */
3349 chr_id
3350 , cle.cle_id
3351 , price_negotiated
3352 , price_negotiated_f
3353 , price_negotiated_g
3354 , price_negotiated_sg
3355 , grace_end_date
3356 , ubt_amt
3357 , ubt_amt_f
3358 , ubt_amt_g
3359 , ubt_amt_sg
3360 , credit_amt
3361 , credit_amt_f
3362 , credit_amt_g
3363 , credit_amt_sg
3364 , override_amt
3365 , override_amt_f
3366 , override_amt_g
3367 , override_amt_sg
3368 , supp_credit
3369 , supp_credit_f
3370 , supp_credit_g
3371 , supp_credit_sg
3372 , end_date
3373 , term_flag
3374 , price_negotiated_a
3375 , ubt_amt_a
3376 , credit_amt_a
3377 , override_amt_a
3378 , supp_credit_a
3379 FROM oki_dbi_prev_inc prev_inc
3380 ,oki_dbi_cle_b_old cle
3381 WHERE prev_inc.p_cle_id = cle.cle_id (+)
3382 AND prev_inc.worker_number = p_worker
3383 AND prev_inc.cle_id = b.cle_id)
3384 WHERE EXISTS (SELECT /*+ ordered index(prev_inc) use_nl(cle) */ 1
3385 FROM oki_dbi_cle_b_old cle
3386 , oki_dbi_prev_inc prev_inc
3387 WHERE prev_inc.p_cle_id = cle.cle_id (+)
3388 AND prev_inc.worker_number = p_worker
3389 AND prev_inc.cle_id = b.cle_id);
3390
3391 p_recs_processed := SQL%ROWCOUNT ;
3392
3393 rlog( 'Number of lines updated into OKI_DBI_CLE_B_OLD with original information is '
3394 || TO_CHAR(p_recs_processed),2) ;
3395 rlog('Updation of Staging Table OKI_DBI_CLE_B_OLD with original information completed: '
3396 ||fnd_date.date_to_displayDT(sysdate), 1) ;
3397
3398 COMMIT;
3399
3400 EXCEPTION
3401 WHEN OTHERS THEN
3402 rlog('Error in update_LHS: Updating LHS information in OKI_DBI_CLE_B_OLD FAILED',0);
3403 rlog(sqlerrm ||' '||sqlcode, 0) ;
3404 rlog(l_location , 0) ;
3405 fnd_message.set_name( application => 'FND'
3406 , name => 'CRM-DEBUG ERROR' ) ;
3407 fnd_message.set_token(
3408 token => 'ROUTINE'
3409 , value => 'OKI_DBI_LOAD_CLEB_PVT.Update_LHS ' ) ;
3410 bis_collection_utilities.put_line(fnd_message.get) ;
3411 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR ;
3412 END Update_LHS;
3413
3414 /*******************************************************************************
3415 Procedure: incr_load
3416 Description: Load OKI_DBI_CLE_B_OLD with original, LHS and RHS information.
3417 Update OKI_DBI_CLE_B with this information.
3418 Parameters: p_recs_processed:The number of records processed
3419 *******************************************************************************/
3420 PROCEDURE incr_Load ( p_recs_processed OUT NOCOPY NUMBER )
3421 IS
3422 l_login_id NUMBER;
3423 l_user_id NUMBER;
3424 l_request_id NUMBER;
3425 l_program_application_id NUMBER;
3426 l_program_id NUMBER;
3427 l_program_login_id NUMBER;
3428 l_run_date DATE;
3429 l_count NUMBER;
3430 l_no_of_workers NUMBER;
3431 l_batch_size number;
3432
3433 l_oki_schema VARCHAR2(30) ;
3434 l_status VARCHAR2(30) ;
3435 l_industry VARCHAR2(30) ;
3436 l_sql_string VARCHAR2(1000) ;
3437 l_start_date DATE;
3438 l_end_date DATE;
3439 l_location VARCHAR2(1000);
3440
3441 BEGIN
3442
3443
3444
3445 l_user_id := FND_GLOBAL.user_id ;
3446 l_request_id := FND_GLOBAL.conc_request_id;
3447 l_program_application_id := FND_GLOBAL.prog_appl_id;
3448 l_program_id := FND_GLOBAL.conc_program_id;
3449 l_program_login_id := FND_GLOBAL.conc_login_id;
3450 l_run_date := OKI_DBI_LOAD_CLEB_PVT.g_run_date ;
3451 l_oki_schema :='OKI';
3452 l_start_date := OKI_DBI_LOAD_CLEB_PVT.g_start_date ;
3453 l_end_date := OKI_DBI_LOAD_CLEB_PVT.g_end_date ;
3454
3455
3456 /* Contracts count may be less than the no of workers. */
3457 l_location := 'Deriving No of workers';
3458 l_no_of_workers := least(CEIL(g_contracts_count/g_batch_size), g_no_of_workers);
3459 rlog( 'Number of workers to be started : ' || l_no_of_workers,0);
3460 FOR worker_no IN 1 .. l_no_of_workers LOOP
3461 UPDATE oki_dbi_chr_inc
3462 SET worker_number = worker_no
3463 WHERE worker_number = -1
3464 AND ROWNUM <= CEIL(g_contracts_count/l_no_of_workers);
3465
3466 IF ( SQL%ROWCOUNT > 0 ) THEN
3467 INSERT INTO OKI_DBI_WORKER_STATUS (
3468 object_name
3469 , worker_number
3470 , status
3471 , phase) VALUES(
3472 'OKI_DBI_CLE_B_OLD'
3473 , worker_no
3474 , 'UNASSIGNED'
3475 , 0);
3476 END IF;
3477 COMMIT;
3478 END LOOP;
3479
3480 FOR l_phase IN 1 .. 7
3481 LOOP
3482 IF l_phase = 1 THEN
3483 rlog ('Inserting modified/created contracts into OKI_DBI_CLE_B_OLD : '||fnd_date.date_to_displayDT(sysdate), 1);
3484 ELSIF l_phase = 2 THEN
3485 rlog ('Finding new renewal relationship information Phase I : '||fnd_date.date_to_displayDT(sysdate),1);
3486 ELSIF l_phase = 3 THEN
3487 rlog ('Finding new renewal relationship information Phase II : '||fnd_date.date_to_displayDT(sysdate),1);
3488 ELSIF l_phase = 4 THEN
3489 rlog ('Finding new renewal relationship information Phase III : '||fnd_date.date_to_displayDT(sysdate),1);
3490 ELSIF l_phase = 5 THEN
3491 rlog ('Inserting covered lines into OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B : '||fnd_date.date_to_displayDT(sysdate),1);
3492 ELSIF l_phase = 6 THEN
3493 rlog ('Updating renewal information into OKI_DBI_CLE_B_OLD : '||fnd_date.date_to_displayDT(sysdate),1);
3494 ELSIF l_phase = 7 THEN
3495 rlog ('Updating original information into OKI_DBI_CLE_B_OLD : '||fnd_date.date_to_displayDT(sysdate),1);
3496 END IF;
3497 FOR i IN 1 .. l_no_of_workers
3498 LOOP
3499 g_worker(i) := LAUNCH_WORKER( i
3500 , l_phase
3501 , l_no_of_workers);
3502 END LOOP;
3503 COMMIT;
3504
3505 DECLARE
3506 l_unassigned_cnt NUMBER := 0;
3507 l_completed_cnt NUMBER := 0;
3508 l_wip_cnt NUMBER := 0;
3509 l_failed_cnt NUMBER := 0;
3510 l_tot_cnt NUMBER := 0;
3511 BEGIN
3512 LOOP
3513 SELECT NVL(sum(decode(status,'UNASSIGNED',1,0)),0),
3514 NVL(sum(decode(status,'COMPLETED',1,0)),0),
3515 NVL(sum(decode(status,'IN PROCESS',1,0)),0),
3516 NVL(sum(decode(status,'FAILED',1,0)),0),
3517 count(*)
3518 INTO l_unassigned_cnt,
3519 l_completed_cnt,
3520 l_wip_cnt,
3521 l_failed_cnt,
3522 l_tot_cnt
3523 FROM OKI_DBI_WORKER_STATUS
3524 WHERE object_name = 'OKI_DBI_CLE_B_OLD';
3525
3526 IF ( l_failed_cnt > 0 ) THEN
3527 rlog('One of the sub-workers errored out..Exiting',1);
3528 RAISE G_CHILD_PROCESS_ISSUE;
3529 END IF;
3530
3531 IF ( l_tot_cnt = l_completed_cnt ) THEN
3532 IF l_phase = 1 THEN
3533 rlog ('Inserting modified/created contracts into OKI_DBI_CLE_B_OLD completed : '||fnd_date.date_to_displayDT(sysdate), 1);
3534 ELSIF l_phase = 2 THEN
3535 rlog ('Finding new renewal relationship information Phase I completed : '||fnd_date.date_to_displayDT(sysdate),1);
3536 ELSIF l_phase = 3 THEN
3537 rlog ('Finding new renewal relationship information Phase II completed : '||fnd_date.date_to_displayDT(sysdate),1);
3538 ELSIF l_phase = 4 THEN
3539 rlog ('Finding new renewal relationship information Phase III completed : '||fnd_date.date_to_displayDT(sysdate),1);
3540 ELSIF l_phase = 5 THEN
3541 rlog ('Inserting covered lines into OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B completed : '||fnd_date.date_to_displayDT(sysdate),1);
3542 ELSIF l_phase = 6 THEN
3543 rlog ('Updating renewal information into OKI_DBI_CLE_B_OLD completed : '||fnd_date.date_to_displayDT(sysdate),1);
3544 ELSIF l_phase = 7 THEN
3545 rlog ('Updating original information into OKI_DBI_CLE_B_OLD completed : '||fnd_date.date_to_displayDT(sysdate),1);
3546 END IF;
3547 EXIT;
3548 END IF;
3549 DBMS_LOCK.sleep(5);
3550 END LOOP;
3551
3552 END; -- Monitor child process Ends here.
3553
3554 IF ( l_phase = 2 ) THEN
3555
3556 GATHER_TABLE_STATS(TABNAME=>'OKI_DBI_PREV_INC');
3557
3558 rlog('Populating table OKI_DBI_REN_INC from OKI_DBI_PREV_INC: '
3559 ||fnd_date.date_to_displayDT(sysdate), 1) ;
3560
3561 MERGE INTO OKI_DBI_REN_INC b
3562 USING
3563 (SELECT rel.p_cle_id cle_id
3564 , rel.cle_id r_cle_id
3565 , MOD(ROWNUM-1,l_no_of_workers)+1 worker_number
3566 FROM oki_dbi_prev_inc rel
3567 )s
3568 ON (b.cle_id = s.cle_id)
3569 WHEN MATCHED THEN
3570 UPDATE SET
3571 r_cle_id = s.r_cle_id
3572 , worker_number = s.worker_number
3573 WHEN NOT MATCHED THEN
3574 INSERT ( cle_id
3575 , r_cle_id
3576 , worker_number
3577 ) VALUES(
3578 s.cle_id
3579 , s.r_cle_id
3580 , s.worker_number
3581 );
3582
3583 l_count := SQL%ROWCOUNT;
3584 rlog( 'Number of lines inserted into OKI_DBI_REN_INC : '
3585 ||TO_CHAR(l_count),2);
3586 rlog('Load of table OKI_DBI_REN_INC from OKI_DBI_PREV_INC completed: '
3587 ||fnd_date.date_to_displayDT(sysdate), 1) ;
3588 COMMIT;
3589 OKI_DBI_LOAD_CLEB_PVT.populate_ren_rel(l_no_of_workers);
3590
3591 ELSIF( l_phase = 3 ) THEN
3592
3593 GATHER_TABLE_STATS(TABNAME=>'OKI_DBI_PREV_INC');
3594
3595 ELSIF (l_phase = 4 ) THEN
3596
3597 GATHER_TABLE_STATS(TABNAME=>'OKI_DBI_PREV_INC');
3598
3599 l_sql_string := 'TRUNCATE TABLE '||l_oki_schema||'.oki_dbi_Cle_del';
3600 EXECUTE IMMEDIATE l_sql_string;
3601 rlog('Truncated table OKI_DBI_CLE_DEL ',2);
3602
3603 rlog('Populating table OKI_DBI_CLE_DEL from OKI_DBI_REN_INC,OKI_DBI_PREV_INC: '
3604 ||fnd_date.date_to_displayDT(sysdate), 1) ;
3605 INSERT INTO OKI_DBI_CLE_DEL
3606 ( cle_id
3607 ,worker_number)
3608 (SELECT
3609 cle_id
3610 , worker_number
3611 FROM (
3612 SELECT cle_id
3613 , MOD(ROWNUM-1,l_no_of_workers)+1 worker_number
3614 FROM (
3615 SELECT /*+ index_ffs(OKI_DBI_REN_INC OKI_DBI_REN_INC_U1) */
3616 cle_id FROM oki_dbi_ren_inc
3617 UNION
3618 SELECT r_cle_id FROM oki_dbi_ren_inc
3619 where r_cle_id is not null
3620 UNION
3621 SELECT /*+ index_ffs(OKI_DBI_PREV_INC OKI_DBI_PREV_INC_U1) */
3622 cle_id FROM oki_dbi_prev_inc
3623 where cle_id is not null
3624 )
3625 )
3626 );
3627
3628 COMMIT;
3629
3630 rlog('Load of table OKI_DBI_CLE_DEL from OKI_DBI_REN_INC,OKI_DBI_PREV_INC completed: '
3631 ||fnd_date.date_to_displayDT(sysdate), 1) ;
3632
3633 GATHER_TABLE_STATS(TABNAME=>'OKI_DBI_CLE_DEL');
3634
3635 END IF;
3636
3637 IF ( l_phase < 7 ) THEN
3638 UPDATE OKI_DBI_WORKER_STATUS SET STATUS='UNASSIGNED', phase = l_phase + 1;
3639 END IF;
3640 COMMIT;
3641 END LOOP;
3642
3643 GATHER_TABLE_STATS(TABNAME=>'OKI_DBI_CLE_B_OLD');
3644
3645 rlog('Updating summary table OKI_DBI_CLE_B from OKI_DBI_CLE_B_OLD: '
3646 ||fnd_date.date_to_displayDT(sysdate), 1) ;
3647
3648 UPDATE
3649 (
3650 SELECT /* + ordered use_nl(b) */
3651 b.chr_id new_chr_id
3652 , b.cle_creation_date new_cle_creation_date
3653 , b.inv_organization_id new_inv_organization_id
3654 , b.authoring_org_id new_authoring_org_id
3655 , b.application_id new_application_id
3656 , b.customer_party_id new_customer_party_id
3657 , b.salesrep_id new_salesrep_id
3658 , b.price_negotiated new_price_negotiated
3659 , b.price_negotiated_f new_price_negotiated_f
3660 , b.price_negotiated_g new_price_negotiated_g
3661 , b.price_negotiated_sg new_price_negotiated_sg
3662 , b.contract_number new_contract_number
3663 , b.contract_number_modifier new_contract_number_modifier
3664 , b.buy_or_sell new_buy_or_sell
3665 , b.scs_code new_scs_code
3666 , b.sts_code new_sts_code
3667 , b.trn_code new_trn_code
3668 , b.root_lty_code new_root_lty_code
3669 , b.renewal_flag new_renewal_flag
3670 , b.date_signed new_date_signed
3671 , b.date_cancelled new_date_cancelled
3672 , b.hstart_date new_hstart_date
3673 , b.hend_date new_hend_date
3674 , b.start_date new_start_date
3675 , b.end_date new_end_date
3676 , b.date_terminated new_date_terminated
3677 , b.effective_start_date new_effective_start_date
3678 , b.effective_end_date new_effective_end_date
3679 , b.trx_func_curr_rate new_trx_func_curr_rate
3680 , b.func_global_curr_rate new_func_global_curr_rate
3681 , b.func_sglobal_curr_rate new_func_sglobal_curr_rate
3682 , b.last_update_login new_last_update_login
3683 , b.last_updated_by new_last_updated_by
3684 , b.last_update_date new_last_update_date
3685 , b.request_id new_request_id
3686 , b.program_application_id new_program_application_id
3687 , b.program_id new_program_id
3688 , b.program_login_id new_program_login_id
3689 , b.resource_group_id new_resource_group_id
3690 , b.resource_id new_resource_id
3691 , b.sle_id new_sle_id
3692 , b.service_item_id new_service_item_id
3693 , b.covered_item_id new_covered_item_id
3694 , b.covered_item_org_id new_covered_item_org_id
3695 , b.quantity new_quantity
3696 , b.uom_code new_uom_code
3697 , b.grace_end_date new_grace_end_date
3698 , b.expected_close_date new_expected_close_date
3699 , b.win_percent new_win_percent
3700 , b.ubt_amt new_ubt_amt
3701 , b.ubt_amt_f new_ubt_amt_f
3702 , b.ubt_amt_g new_ubt_amt_g
3703 , b.ubt_amt_sg new_ubt_amt_sg
3704 , b.credit_amt new_credit_amt
3705 , b.credit_amt_f new_credit_amt_f
3706 , b.credit_amt_g new_credit_amt_g
3707 , b.credit_amt_sg new_credit_amt_sg
3708 , b.override_amt new_override_amt
3709 , b.override_amt_f new_override_amt_f
3710 , b.override_amt_g new_override_amt_g
3711 , b.override_amt_sg new_override_amt_sg
3712 , b.supp_credit new_supp_credit
3713 , b.supp_credit_f new_supp_credit_f
3714 , b.supp_credit_g new_supp_credit_g
3715 , b.supp_credit_sg new_supp_credit_sg
3716 , b.renewal_type new_renewal_type
3717 , b.term_flag new_term_flag
3718 , b.r_chr_id new_r_chr_id
3719 , b.r_cle_id new_r_cle_id
3720 , b.r_date_signed new_r_date_signed
3721 , b.r_date_cancelled new_r_date_cancelled
3722 , b.annualization_factor new_annualization_factor
3723 , b.p_chr_id new_p_chr_id
3724 , b.p_cle_id new_p_cle_id
3725 , b.p_price_negotiated new_p_price_negotiated
3726 , b.p_price_negotiated_f new_p_price_negotiated_f
3727 , b.p_price_negotiated_g new_p_price_negotiated_g
3728 , b.p_price_negotiated_sg new_p_price_negotiated_sg
3729 , b.p_grace_end_date new_p_grace_end_date
3730 , b.p_ubt_amt new_p_ubt_amt
3731 , b.p_ubt_amt_f new_p_ubt_amt_f
3732 , b.p_ubt_amt_g new_p_ubt_amt_g
3733 , b.p_ubt_amt_sg new_p_ubt_amt_sg
3734 , b.p_credit_amt new_p_credit_amt
3735 , b.p_credit_amt_f new_p_credit_amt_f
3736 , b.p_credit_amt_g new_p_credit_amt_g
3737 , b.p_credit_amt_sg new_p_credit_amt_sg
3738 , b.p_override_amt new_p_override_amt
3739 , b.p_override_amt_f new_p_override_amt_f
3740 , b.p_override_amt_g new_p_override_amt_g
3741 , b.p_override_amt_sg new_p_override_amt_sg
3742 , b.p_supp_credit new_p_supp_credit
3743 , b.p_supp_credit_f new_p_supp_credit_f
3744 , b.p_supp_credit_g new_p_supp_credit_g
3745 , b.p_supp_credit_sg new_p_supp_credit_sg
3746 , b.p_end_date new_p_end_date
3747 , b.p_term_flag new_p_term_flag
3748 , b.price_negotiated_a new_price_negotiated_a
3749 , b.ubt_amt_a new_ubt_amt_a
3750 , b.credit_amt_a new_credit_amt_a
3751 , b.override_amt_a new_override_amt_a
3752 , b.supp_credit_a new_supp_credit_a
3753 , b.p_price_negotiated_a new_p_price_negotiated_a
3754 , b.p_ubt_amt_a new_p_ubt_amt_a
3755 , b.p_credit_amt_a new_p_credit_amt_a
3756 , b.p_override_amt_a new_p_override_amt_a
3757 , b.p_supp_credit_a new_p_supp_credit_a
3758 , b.gsd_flag new_gsd_flag
3759 , b.falsernwlyn new_falsernwlyn
3760 , b.effective_active_date new_effective_active_date
3761 , b.effective_term_date new_effective_term_date
3762 , b.effective_expire_date new_effective_expire_date
3763 , b.termination_entry_date new_termination_entry_date
3764 , b.curr_code new_curr_code
3765 , b.curr_code_f new_curr_code_f
3766 , b.hdr_order_number new_hdr_order_number
3767 , b.hdr_sts_code new_hdr_sts_code
3768 , b.hdr_trn_code new_hdr_trn_code
3769 , b.hdr_renewal_type new_hdr_renewal_type
3770 , b.hdr_date_approved new_hdr_date_approved
3771 , b.hdr_date_cancelled new_hdr_date_cancelled
3772 , b.hdr_date_terminated new_hdr_date_terminated
3773 , b.hdr_creation_date new_hdr_creation_date
3774 , b.hdr_last_update_date new_hdr_last_update_date
3775 , b.service_item_org_id new_service_item_org_id
3776 , b.sl_line_number new_sl_line_number
3777 , b.sl_sts_code new_sl_sts_code
3778 , b.sl_trn_code new_sl_trn_code
3779 , b.sl_renewal_type new_sl_renewal_type
3780 , b.sl_start_date new_sl_start_date
3781 , b.sl_end_Date new_sl_end_Date
3782 , b.sl_date_cancelled new_sl_date_cancelled
3783 , b.sl_date_terminated new_sl_date_terminated
3784 , b.sl_creation_date new_sl_creation_date
3785 , b.sl_last_update_date new_sl_last_update_date
3786 , b.order_number new_order_number
3787 , b.unit_price_percent new_unit_price_percent
3788 , b.unit_price new_unit_price
3789 , b.unit_price_f new_unit_price_f
3790 , b.unit_price_g new_unit_price_g
3791 , b.unit_price_sg new_unit_price_sg
3792 , b.list_price new_list_price
3793 , b.list_price_f new_list_price_f
3794 , b.list_price_g new_list_price_g
3795 , b.list_price_sg new_list_price_sg
3796 , b.duration_uom new_duration_uom
3797 , b.duration_qty new_duration_qty
3798 , b.cl_last_update_date new_cl_last_update_date
3799 , b.cov_prod_id new_cov_prod_id
3800 , b.cov_prod_system_id new_cov_prod_system_id
3801 , b.line_number new_line_number
3802 , b.line_type new_line_type
3803 , b.hdr_bill_site_id new_hdr_bill_site_id
3804 , b.hdr_ship_site_id new_hdr_ship_site_id
3805 , b.hdr_acct_rule_id new_hdr_acct_rule_id
3806 , b.hdr_grace_end_Date new_hdr_grace_end_Date
3807 , b.hdr_date_signed new_hdr_date_signed
3808 , b.hdr_subsequent_renewal_type N_HDR_SUBSEQUENT_RENEWAL_TYPE /* Added this colunm as a part of ER#5760744 */
3809 , b.agreement_type_code new_agreement_type_code /* for ER 6062516 */
3810 , b.agreement_name new_agreement_name /* for ER 6062516 */
3811 , b.negotiation_status new_negotiation_status /* Added this colunm as a part of ER#5950128 */
3812 , b.reminder new_reminder /* Added this colunm as a part of ER#5950128 */
3813 , b.hdr_term_cancel_source new_hdr_term_cancel_source /* Added for ER 6684955 */
3814 , b.sl_term_cancel_source new_sl_term_cancel_source /* Added for ER 6684955 */
3815 , s.chr_id old_chr_id
3816 , s.cle_creation_date old_cle_creation_date
3817 , s.inv_organization_id old_inv_organization_id
3818 , s.authoring_org_id old_authoring_org_id
3819 , s.application_id old_application_id
3820 , s.customer_party_id old_customer_party_id
3821 , s.salesrep_id old_salesrep_id
3822 , s.price_negotiated old_price_negotiated
3823 , s.price_negotiated_f old_price_negotiated_f
3824 , s.price_negotiated_g old_price_negotiated_g
3825 , s.price_negotiated_sg old_price_negotiated_sg
3826 , s.contract_number old_contract_number
3827 , s.contract_number_modifier old_contract_number_modifier
3828 , s.buy_or_sell old_buy_or_sell
3829 , s.scs_code old_scs_code
3830 , s.sts_code old_sts_code
3831 , s.trn_code old_trn_code
3832 , s.root_lty_code old_root_lty_code
3833 , s.renewal_flag old_renewal_flag
3834 , s.date_signed old_date_signed
3835 , s.date_cancelled old_date_cancelled
3836 , s.hstart_date old_hstart_date
3837 , s.hend_date old_hend_date
3838 , s.start_date old_start_date
3839 , s.end_date old_end_date
3840 , s.date_terminated old_date_terminated
3841 , s.effective_start_date old_effective_start_date
3842 , s.effective_end_date old_effective_end_date
3843 , s.trx_func_curr_rate old_trx_func_curr_rate
3844 , s.func_global_curr_rate old_func_global_curr_rate
3845 , s.func_sglobal_curr_rate old_func_sglobal_curr_rate
3846 , s.resource_group_id old_resource_group_id
3847 , s.resource_id old_resource_id
3848 , s.sle_id old_sle_id
3849 , s.service_item_id old_service_item_id
3850 , s.covered_item_id old_covered_item_id
3851 , s.covered_item_org_id old_covered_item_org_id
3852 , s.quantity old_quantity
3853 , s.uom_code old_uom_code
3854 , s.grace_end_date old_grace_end_date
3855 , s.expected_close_date old_expected_close_date
3856 , s.win_percent old_win_percent
3857 , s.ubt_amt old_ubt_amt
3858 , s.ubt_amt_f old_ubt_amt_f
3859 , s.ubt_amt_g old_ubt_amt_g
3860 , s.ubt_amt_sg old_ubt_amt_sg
3861 , s.credit_amt old_credit_amt
3862 , s.credit_amt_f old_credit_amt_f
3863 , s.credit_amt_g old_credit_amt_g
3864 , s.credit_amt_sg old_credit_amt_sg
3865 , s.override_amt old_override_amt
3866 , s.override_amt_f old_override_amt_f
3867 , s.override_amt_g old_override_amt_g
3868 , s.override_amt_sg old_override_amt_sg
3869 , s.supp_credit old_supp_credit
3870 , s.supp_credit_f old_supp_credit_f
3871 , s.supp_credit_g old_supp_credit_g
3872 , s.supp_credit_sg old_supp_credit_sg
3873 , s.renewal_type old_renewal_type
3874 , s.term_flag old_term_flag
3875 , s.r_chr_id old_r_chr_id
3876 , s.r_cle_id old_r_cle_id
3877 , s.r_date_signed old_r_date_signed
3878 , s.r_date_cancelled old_r_date_cancelled
3879 , s.annualization_factor old_annualization_factor
3880 , s.p_chr_id old_p_chr_id
3881 , s.p_cle_id old_p_cle_id
3882 , s.p_price_negotiated old_p_price_negotiated
3883 , s.p_price_negotiated_f old_p_price_negotiated_f
3884 , s.p_price_negotiated_g old_p_price_negotiated_g
3885 , s.p_price_negotiated_sg old_p_price_negotiated_sg
3886 , s.p_grace_end_date old_p_grace_end_date
3887 , s.p_ubt_amt old_p_ubt_amt
3888 , s.p_ubt_amt_f old_p_ubt_amt_f
3889 , s.p_ubt_amt_g old_p_ubt_amt_g
3890 , s.p_ubt_amt_sg old_p_ubt_amt_sg
3891 , s.p_credit_amt old_p_credit_amt
3892 , s.p_credit_amt_f old_p_credit_amt_f
3893 , s.p_credit_amt_g old_p_credit_amt_g
3894 , s.p_credit_amt_sg old_p_credit_amt_sg
3895 , s.p_override_amt old_p_override_amt
3896 , s.p_override_amt_f old_p_override_amt_f
3897 , s.p_override_amt_g old_p_override_amt_g
3898 , s.p_override_amt_sg old_p_override_amt_sg
3899 , s.p_supp_credit old_p_supp_credit
3900 , s.p_supp_credit_f old_p_supp_credit_f
3901 , s.p_supp_credit_g old_p_supp_credit_g
3902 , s.p_supp_credit_sg old_p_supp_credit_sg
3903 , s.p_end_date old_p_end_date
3904 , s.p_term_flag old_p_term_flag
3905 , s.price_negotiated_a old_price_negotiated_a
3906 , s.ubt_amt_a old_ubt_amt_a
3907 , s.credit_amt_a old_credit_amt_a
3908 , s.override_amt_a old_override_amt_a
3909 , s.supp_credit_a old_supp_credit_a
3910 , s.p_price_negotiated_a old_p_price_negotiated_a
3911 , s.p_ubt_amt_a old_p_ubt_amt_a
3912 , s.p_credit_amt_a old_p_credit_amt_a
3913 , s.p_override_amt_a old_p_override_amt_a
3914 , s.p_supp_credit_a old_p_supp_credit_a
3915 , s.gsd_flag old_gsd_flag
3916 , s.falsernwlyn old_falsernwlyn
3917 , s.effective_active_date old_effective_active_date
3918 , s.effective_term_date old_effective_term_date
3919 , s.effective_expire_date old_effective_expire_date
3920 , s.termination_entry_date old_termination_entry_date
3921 , s.curr_code old_curr_code
3922 , s.curr_code_f old_curr_code_f
3923 , s.hdr_order_number old_hdr_order_number
3924 , s.hdr_sts_code old_hdr_sts_code
3925 , s.hdr_trn_code old_hdr_trn_code
3926 , s.hdr_renewal_type old_hdr_renewal_type
3927 , s.hdr_date_approved old_hdr_date_approved
3928 , s.hdr_date_cancelled old_hdr_date_cancelled
3929 , s.hdr_date_terminated old_hdr_date_terminated
3930 , s.hdr_creation_date old_hdr_creation_date
3931 , s.hdr_last_update_date old_hdr_last_update_date
3932 , s.service_item_org_id old_service_item_org_id
3933 , s.sl_line_number old_sl_line_number
3934 , s.sl_sts_code old_sl_sts_code
3935 , s.sl_trn_code old_sl_trn_code
3936 , s.sl_renewal_type old_sl_renewal_type
3937 , s.sl_start_date old_sl_start_date
3938 , s.sl_end_Date old_sl_end_Date
3939 , s.sl_date_cancelled old_sl_date_cancelled
3940 , s.sl_date_terminated old_sl_date_terminated
3941 , s.sl_creation_date old_sl_creation_date
3942 , s.sl_last_update_date old_sl_last_update_date
3943 , s.order_number old_order_number
3944 , s.unit_price_percent old_unit_price_percent
3945 , s.unit_price old_unit_price
3946 , s.unit_price_f old_unit_price_f
3947 , s.unit_price_g old_unit_price_g
3948 , s.unit_price_sg old_unit_price_sg
3949 , s.list_price old_list_price
3950 , s.list_price_f old_list_price_f
3951 , s.list_price_g old_list_price_g
3952 , s.list_price_sg old_list_price_sg
3953 , s.duration_uom old_duration_uom
3954 , s.duration_qty old_duration_qty
3955 , s.cl_last_update_date old_cl_last_update_date
3956 , s.cov_prod_id old_cov_prod_id
3957 , s.cov_prod_system_id old_cov_prod_system_id
3958 , s.line_number old_line_number
3959 , s.line_type old_line_type
3960 , s.hdr_bill_site_id old_hdr_bill_site_id
3961 , s.hdr_ship_site_id old_hdr_ship_site_id
3962 , s.hdr_acct_rule_id old_hdr_acct_rule_id
3963 , s.hdr_grace_end_date old_hdr_grace_end_date
3964 , s.hdr_date_signed old_hdr_date_signed
3965 , s.hdr_subsequent_renewal_type o_hdr_subsequent_renewal_type /* Added this colunm as a part of ER#5760744 */
3966 , s.agreement_type_code old_agreement_type_code /* Added for ER 6062516 */
3967 , s.agreement_name old_agreement_name /* Added for ER 6062516 */
3968 , s.negotiation_status old_negotiation_status /* Added this colunm as a part of ER#5950128 */
3969 , s.reminder old_reminder /* Added this colunm as a part of ER#5950128 */
3970 , s.hdr_term_cancel_source old_hdr_term_cancel_source /* Added for ER 6684955 */
3971 , s.sl_term_cancel_source old_sl_term_cancel_source /* Added for ER 6684955 */
3972
3973 FROM OKI_DBI_CLE_B_OLD S ,
3974 OKI_DBI_CLE_B B
3975 WHERE B.CLE_ID = S.CLE_ID )
3976 SET
3977 new_chr_id = old_chr_id,
3978 new_cle_creation_date = old_cle_creation_date ,
3979 new_inv_organization_id = old_inv_organization_id ,
3980 new_authoring_org_id = old_authoring_org_id ,
3981 new_application_id = old_application_id ,
3982 new_customer_party_id = old_customer_party_id ,
3983 new_salesrep_id = old_salesrep_id ,
3984 new_price_negotiated = old_price_negotiated ,
3985 new_price_negotiated_f = old_price_negotiated_f ,
3986 new_price_negotiated_g = old_price_negotiated_g ,
3987 new_price_negotiated_sg = old_price_negotiated_sg ,
3988 new_contract_number = old_contract_number ,
3989 new_contract_number_modifier = old_contract_number_modifier ,
3990 new_buy_or_sell = old_buy_or_sell ,
3991 new_scs_code = old_scs_code ,
3992 new_sts_code = old_sts_code ,
3993 new_trn_code = old_trn_code ,
3994 new_root_lty_code = old_root_lty_code ,
3995 new_renewal_flag = old_renewal_flag ,
3996 new_date_signed = old_date_signed ,
3997 new_date_cancelled = old_date_cancelled ,
3998 new_hstart_date = old_hstart_date ,
3999 new_hend_date = old_hend_date ,
4000 new_start_date = old_start_date ,
4001 new_end_date = old_end_date ,
4002 new_date_terminated = old_date_terminated ,
4003 new_effective_start_date = old_effective_start_date ,
4004 new_effective_end_date = old_effective_end_date ,
4005 new_trx_func_curr_rate = old_trx_func_curr_rate ,
4006 new_func_global_curr_rate = old_func_global_curr_rate ,
4007 new_func_sglobal_curr_rate = old_func_sglobal_curr_rate ,
4008 new_last_update_login = l_login_id ,
4009 new_last_updated_by = l_user_id ,
4010 new_last_update_date = l_run_date ,
4011 new_request_id = l_request_id ,
4012 new_program_application_id = l_program_application_id ,
4013 new_program_id = l_program_id ,
4014 new_program_login_id = l_program_login_id ,
4015 new_resource_group_id = old_resource_group_id ,
4016 new_resource_id = old_resource_id ,
4017 new_sle_id = old_sle_id ,
4018 new_service_item_id = old_service_item_id ,
4019 new_covered_item_id = old_covered_item_id ,
4020 new_covered_item_org_id = old_covered_item_org_id ,
4021 new_quantity = old_quantity ,
4022 new_uom_code = old_uom_code ,
4023 new_grace_end_date = old_grace_end_date ,
4024 new_expected_close_date = old_expected_close_date ,
4025 new_win_percent = old_win_percent ,
4026 new_ubt_amt = old_ubt_amt ,
4027 new_ubt_amt_f = old_ubt_amt_f ,
4028 new_ubt_amt_g = old_ubt_amt_g ,
4029 new_ubt_amt_sg = old_ubt_amt_sg ,
4030 new_credit_amt = old_credit_amt ,
4031 new_credit_amt_f = old_credit_amt_f ,
4032 new_credit_amt_g = old_credit_amt_g ,
4033 new_credit_amt_sg = old_credit_amt_sg ,
4034 new_override_amt = old_override_amt ,
4035 new_override_amt_f = old_override_amt_f ,
4036 new_override_amt_g = old_override_amt_g ,
4037 new_override_amt_sg = old_override_amt_sg ,
4038 new_supp_credit = old_supp_credit ,
4039 new_supp_credit_f = old_supp_credit_f ,
4040 new_supp_credit_g = old_supp_credit_g ,
4041 new_supp_credit_sg = old_supp_credit_sg ,
4042 new_renewal_type = old_renewal_type ,
4043 new_term_flag = old_term_flag ,
4044 new_r_chr_id = old_r_chr_id ,
4045 new_r_cle_id = old_r_cle_id ,
4046 new_r_date_signed = old_r_date_signed ,
4047 new_r_date_cancelled = old_r_date_cancelled ,
4048 new_annualization_factor = old_annualization_factor ,
4049 new_p_chr_id = old_p_chr_id ,
4050 new_p_cle_id = old_p_cle_id ,
4051 new_p_price_negotiated = old_p_price_negotiated ,
4052 new_p_price_negotiated_f = old_p_price_negotiated_f ,
4053 new_p_price_negotiated_g = old_p_price_negotiated_g ,
4054 new_p_price_negotiated_sg = old_p_price_negotiated_sg ,
4055 new_p_grace_end_date = old_p_grace_end_date ,
4056 new_p_ubt_amt = old_p_ubt_amt ,
4057 new_p_ubt_amt_f = old_p_ubt_amt_f ,
4058 new_p_ubt_amt_g = old_p_ubt_amt_g ,
4059 new_p_ubt_amt_sg = old_p_ubt_amt_sg ,
4060 new_p_credit_amt = old_p_credit_amt ,
4061 new_p_credit_amt_f = old_p_credit_amt_f ,
4062 new_p_credit_amt_g = old_p_credit_amt_g ,
4063 new_p_credit_amt_sg = old_p_credit_amt_sg ,
4064 new_p_override_amt = old_p_override_amt ,
4065 new_p_override_amt_f = old_p_override_amt_f ,
4066 new_p_override_amt_g = old_p_override_amt_g ,
4067 new_p_override_amt_sg = old_p_override_amt_sg ,
4068 new_p_supp_credit = old_p_supp_credit ,
4069 new_p_supp_credit_f = old_p_supp_credit_f ,
4070 new_p_supp_credit_g = old_p_supp_credit_g ,
4071 new_p_supp_credit_sg = old_p_supp_credit_sg ,
4072 new_p_end_date = old_p_end_date ,
4073 new_p_term_flag = old_p_term_flag ,
4074 new_price_negotiated_a = old_price_negotiated_a ,
4075 new_ubt_amt_a = old_ubt_amt_a ,
4076 new_credit_amt_a = old_credit_amt_a ,
4077 new_override_amt_a = old_override_amt_a ,
4078 new_supp_credit_a = old_supp_credit_a ,
4079 new_p_price_negotiated_a = old_p_price_negotiated_a ,
4080 new_p_ubt_amt_a = old_p_ubt_amt_a ,
4081 new_p_credit_amt_a = old_p_credit_amt_a ,
4082 new_p_override_amt_a = old_p_override_amt_a ,
4083 new_p_supp_credit_a = old_p_supp_credit_a ,
4084 new_gsd_flag = old_gsd_flag ,
4085 new_falsernwlyn = old_falsernwlyn ,
4086 new_effective_active_date = old_effective_active_date ,
4087 new_effective_term_date = old_effective_term_date ,
4088 new_effective_expire_date = old_effective_expire_date ,
4089 new_termination_entry_date = old_termination_entry_date ,
4090 new_curr_code = old_curr_code ,
4091 new_curr_code_f = old_curr_code_f ,
4092 new_hdr_order_number = old_hdr_order_number ,
4093 new_hdr_sts_code = old_hdr_sts_code ,
4094 new_hdr_trn_code = old_hdr_trn_code ,
4095 new_hdr_renewal_type = old_hdr_renewal_type ,
4096 new_hdr_date_approved = old_hdr_date_approved ,
4097 new_hdr_date_cancelled = old_hdr_date_cancelled ,
4098 new_hdr_date_terminated = old_hdr_date_terminated ,
4099 new_hdr_creation_date = old_hdr_creation_date ,
4100 new_hdr_last_update_date = old_hdr_last_update_date ,
4101 new_service_item_org_id = old_service_item_org_id ,
4102 new_sl_line_number = old_sl_line_number ,
4103 new_sl_sts_code = old_sl_sts_code ,
4104 new_sl_trn_code = old_sl_trn_code ,
4105 new_sl_renewal_type = old_sl_renewal_type ,
4106 new_sl_start_date = old_sl_start_date ,
4107 new_sl_end_Date = old_sl_end_Date ,
4108 new_sl_date_cancelled = old_sl_date_cancelled ,
4109 new_sl_date_terminated = old_sl_date_terminated ,
4110 new_sl_creation_date = old_sl_creation_date ,
4111 new_sl_last_update_date = old_sl_last_update_date ,
4112 new_order_number = old_order_number ,
4113 new_unit_price_percent = old_unit_price_percent ,
4114 new_unit_price = old_unit_price ,
4115 new_unit_price_f = old_unit_price_f ,
4116 new_unit_price_g = old_unit_price_g ,
4117 new_unit_price_sg = old_unit_price_sg ,
4118 new_list_price = old_list_price ,
4119 new_list_price_f = old_list_price_f ,
4120 new_list_price_g = old_list_price_g ,
4121 new_list_price_sg = old_list_price_sg ,
4122 new_duration_uom = old_duration_uom ,
4123 new_duration_qty = old_duration_qty ,
4124 new_cl_last_update_date = old_cl_last_update_date ,
4125 new_cov_prod_id = old_cov_prod_id ,
4126 new_cov_prod_system_id = old_cov_prod_system_id ,
4127 new_line_number = old_line_number ,
4128 new_line_type = old_line_type ,
4129 new_hdr_bill_site_id = old_hdr_bill_site_id ,
4130 new_hdr_ship_site_id = old_hdr_ship_site_id ,
4131 new_hdr_acct_rule_id = old_hdr_acct_rule_id ,
4132 new_hdr_grace_end_Date = old_hdr_grace_end_date ,
4133 new_hdr_date_signed = old_hdr_date_signed ,
4134 n_hdr_subsequent_renewal_type = o_hdr_subsequent_renewal_type , /* Added this colunm as a part of ER#5760744 */
4135 new_agreement_type_code = old_agreement_type_code , /* for ER 6062516 */
4136 new_agreement_name = old_agreement_name , /* for ER 6062516 */
4137 new_negotiation_status = old_negotiation_status , /* Added this colunm as a part of ER#5950128 */
4138 new_reminder = old_reminder , /* Added this colunm as a part of ER#5950128 */
4139 new_hdr_term_cancel_source = old_hdr_term_cancel_source , /* Added for ER 6684955 */ /* Added for ER 6684955 */
4140 new_sl_term_cancel_source = old_sl_term_cancel_source; /* Added for ER 6684955 */ /* Added for ER 6684955 */
4141
4142
4143 l_count := SQL%ROWCOUNT;
4144
4145 rlog('Number of lines updated into OKI_DBI_CLE_B : '|| l_count,2);
4146 COMMIT ;
4147
4148 INSERT INTO OKI_DBI_CLE_B
4149 (
4150 chr_id
4151 , cle_id
4152 , cle_creation_date
4153 , inv_organization_id
4154 , authoring_org_id
4155 , application_id
4156 , customer_party_id
4157 , salesrep_id
4158 , price_negotiated
4159 , price_negotiated_f
4160 , price_negotiated_g
4161 , price_negotiated_sg
4162 , contract_number
4163 , contract_number_modifier
4164 , buy_or_sell
4165 , scs_code
4166 , sts_code
4167 , trn_code
4168 , root_lty_code
4169 , renewal_flag
4170 , date_signed
4171 , date_cancelled
4172 , hstart_date
4173 , hend_date
4174 , start_date
4175 , end_date
4176 , date_terminated
4177 , effective_start_date
4178 , effective_end_date
4179 , trx_func_curr_rate
4180 , func_global_curr_rate
4181 , func_sglobal_curr_rate
4182 , created_by
4183 , last_update_login
4184 , creation_date
4185 , last_updated_by
4186 , last_update_date
4187 , request_id
4188 , program_application_id
4189 , program_id
4190 , program_login_id
4191 , resource_group_id
4192 , resource_id
4193 , sle_id
4194 , service_item_id
4195 , covered_item_id
4196 , covered_item_org_id
4197 , quantity
4198 , uom_code
4199 , grace_end_date
4200 , expected_close_date
4201 , win_percent
4202 , ubt_amt
4203 , ubt_amt_f
4204 , ubt_amt_g
4205 , ubt_amt_sg
4206 , credit_amt
4207 , credit_amt_f
4208 , credit_amt_g
4209 , credit_amt_sg
4210 , override_amt
4211 , override_amt_f
4212 , override_amt_g
4213 , override_amt_sg
4214 , supp_credit
4215 , supp_credit_f
4216 , supp_credit_g
4217 , supp_credit_sg
4218 , renewal_type
4219 , term_flag
4220 , r_chr_id
4221 , r_cle_id
4222 , r_date_signed
4223 , r_date_cancelled
4224 , annualization_factor
4225 , p_chr_id
4226 , p_cle_id
4227 , p_price_negotiated
4228 , p_price_negotiated_f
4229 , p_price_negotiated_g
4230 , p_price_negotiated_sg
4231 , p_grace_end_date
4232 , p_ubt_amt
4233 , p_ubt_amt_f
4234 , p_ubt_amt_g
4235 , p_ubt_amt_sg
4236 , p_credit_amt
4237 , p_credit_amt_f
4238 , p_credit_amt_g
4239 , p_credit_amt_sg
4240 , p_override_amt
4241 , p_override_amt_f
4242 , p_override_amt_g
4243 , p_override_amt_sg
4244 , p_supp_credit
4245 , p_supp_credit_f
4246 , p_supp_credit_g
4247 , p_supp_credit_sg
4248 , p_end_date
4249 , p_term_flag
4250 , price_negotiated_a
4251 , ubt_amt_a
4252 , credit_amt_a
4253 , override_amt_a
4254 , supp_credit_a
4255 , p_price_negotiated_a
4256 , p_ubt_amt_a
4257 , p_credit_amt_a
4258 , p_override_amt_a
4259 , p_supp_credit_a
4260 , gsd_flag
4261 , falsernwlyn
4262 , effective_active_date
4263 , effective_term_date
4264 , effective_expire_date
4265 , termination_entry_date
4266 , curr_code
4267 , curr_code_f
4268 , hdr_order_number
4269 , hdr_sts_code
4270 , hdr_trn_code
4271 , hdr_renewal_type
4272 , hdr_date_approved
4273 , hdr_date_cancelled
4274 , hdr_date_terminated
4275 , hdr_creation_date
4276 , hdr_last_update_date
4277 , service_item_org_id
4278 , sl_line_number
4279 , sl_sts_code
4280 , sl_trn_code
4281 , sl_renewal_type
4282 , sl_start_date
4283 , sl_end_Date
4284 , sl_date_cancelled
4285 , sl_date_terminated
4286 , sl_creation_date
4287 , sl_last_update_date
4288 , order_number
4289 , unit_price_percent
4290 , unit_price
4291 , unit_price_f
4292 , unit_price_g
4293 , unit_price_sg
4294 , list_price
4295 , list_price_f
4296 , list_price_g
4297 , list_price_sg
4298 , duration_uom
4299 , duration_qty
4300 , cl_last_update_date
4301 , cov_prod_id
4302 , cov_prod_system_id
4303 , line_number
4304 , line_type
4305 , hdr_bill_site_id
4306 , hdr_ship_site_id
4307 , hdr_acct_rule_id
4308 , hdr_grace_end_date
4309 , hdr_date_signed
4310 , hdr_subsequent_renewal_type /* Added this colunm as a part of ER#5760744 */
4311 , agreement_type_code /* for ER 6062516 */
4312 , agreement_name /* for ER 6062516 */
4313 , negotiation_status /* Added this colunm as a part of ER#5950128 */
4314 , reminder /* Added this colunm as a part of ER#5950128 */
4315 , hdr_term_cancel_source /* Added for ER 6684955 */
4316 , sl_term_cancel_source /* Added for ER 6684955 */
4317 )
4318 ( SELECT
4319 s.chr_id
4320 , s.cle_id
4321 , s.cle_creation_date
4322 , s.inv_organization_id
4323 , s.authoring_org_id
4324 , s.application_id
4325 , s.customer_party_id
4326 , s.salesrep_id
4327 , s.price_negotiated
4328 , s.price_negotiated_f
4329 , s.price_negotiated_g
4330 , s.price_negotiated_sg
4331 , s.contract_number
4332 , s.contract_number_modifier
4333 , s.buy_or_sell
4334 , s.scs_code
4335 , s.sts_code
4336 , s.trn_code
4337 , s.root_lty_code
4338 , s.renewal_flag
4339 , s.date_signed
4340 , s.date_cancelled
4341 , s.hstart_date
4342 , s.hend_date
4343 , s.start_date
4344 , s.end_date
4345 , s.date_terminated
4346 , s.effective_start_date
4347 , s.effective_end_date
4348 , s.trx_func_curr_rate
4349 , s.func_global_curr_rate
4350 , s.func_sglobal_curr_rate
4351 , s.created_by
4352 , l_login_id
4353 , s.creation_date
4354 , l_user_id
4355 , l_run_date
4356 , l_request_id
4357 , l_program_application_id
4358 , l_program_id
4359 , l_program_login_id
4360 , s.resource_group_id
4361 , s.resource_id
4362 , s.sle_id
4363 , s.service_item_id
4364 , s.covered_item_id
4365 , s.covered_item_org_id
4366 , s.quantity
4367 , s.uom_code
4368 , s.grace_end_date
4369 , s.expected_close_date
4370 , s.win_percent
4371 , s.ubt_amt
4372 , s.ubt_amt_f
4373 , s.ubt_amt_g
4374 , s.ubt_amt_sg
4375 , s.credit_amt
4376 , s.credit_amt_f
4377 , s.credit_amt_g
4378 , s.credit_amt_sg
4379 , s.override_amt
4380 , s.override_amt_f
4381 , s.override_amt_g
4382 , s.override_amt_sg
4383 , s.supp_credit
4384 , s.supp_credit_f
4385 , s.supp_credit_g
4386 , s.supp_credit_sg
4387 , s.renewal_type
4388 , s.term_flag
4389 , s.r_chr_id
4390 , s.r_cle_id
4391 , s.r_date_signed
4392 , s.r_date_cancelled
4393 , s.annualization_factor
4394 , s.p_chr_id
4395 , s.p_cle_id
4396 , s.p_price_negotiated
4397 , s.p_price_negotiated_f
4398 , s.p_price_negotiated_g
4399 , s.p_price_negotiated_sg
4400 , s.p_grace_end_date
4401 , s.p_ubt_amt
4402 , s.p_ubt_amt_f
4403 , s.p_ubt_amt_g
4404 , s.p_ubt_amt_sg
4405 , s.p_credit_amt
4406 , s.p_credit_amt_f
4407 , s.p_credit_amt_g
4408 , s.p_credit_amt_sg
4409 , s.p_override_amt
4410 , s.p_override_amt_f
4411 , s.p_override_amt_g
4412 , s.p_override_amt_sg
4413 , s.p_supp_credit
4414 , s.p_supp_credit_f
4415 , s.p_supp_credit_g
4416 , s.p_supp_credit_sg
4417 , s.p_end_date
4418 , s.p_term_flag
4419 , s.price_negotiated_a
4420 , s.ubt_amt_a
4421 , s.credit_amt_a
4422 , s.override_amt_a
4423 , s.supp_credit_a
4424 , s.p_price_negotiated_a
4425 , s.p_ubt_amt_a
4426 , s.p_credit_amt_a
4427 , s.p_override_amt_a
4428 , s.p_supp_credit_a
4429 , s.gsd_flag
4430 , s.falsernwlyn
4431 , s.effective_active_date
4432 , s.effective_term_date
4433 , s.effective_expire_date
4434 , s.termination_entry_date
4435 , s.curr_code
4436 , s.curr_code_f
4437 , s.hdr_order_number
4438 , s.hdr_sts_code
4439 , s.hdr_trn_code
4440 , s.hdr_renewal_type
4441 , s.hdr_date_approved
4442 , s.hdr_date_cancelled
4443 , s.hdr_date_terminated
4444 , s.hdr_creation_date
4445 , s.hdr_last_update_date
4446 , s.service_item_org_id
4447 , s.sl_line_number
4448 , s.sl_sts_code
4449 , s.sl_trn_code
4450 , s.sl_renewal_type
4451 , s.sl_start_date
4452 , s.sl_end_Date
4453 , s.sl_date_cancelled
4454 , s.sl_date_terminated
4455 , s.sl_creation_date
4456 , s.sl_last_update_date
4457 , s.order_number
4458 , s.unit_price_percent
4459 , s.unit_price
4460 , s.unit_price_f
4461 , s.unit_price_g
4462 , s.unit_price_sg
4463 , s.list_price
4464 , s.list_price_f
4465 , s.list_price_g
4466 , s.list_price_sg
4467 , s.duration_uom
4468 , s.duration_qty
4469 , s.cl_last_update_date
4470 , s.cov_prod_id
4471 , s.cov_prod_system_id
4472 , s.line_number
4473 , s.line_type
4474 , s.hdr_bill_site_id
4475 , s.hdr_ship_site_id
4476 , s.hdr_acct_rule_id
4477 , s.hdr_grace_end_date
4478 , s.hdr_date_signed
4479 , s.hdr_subsequent_renewal_type /* for ER#5760744 */
4480 , s.agreement_type_code /* for ER 6062516 */
4481 , s.agreement_name /* for ER 6062516 */
4482 , s.negotiation_status /* Added this colunm as a part of ER#5950128 */
4483 , s.reminder /* Added this colunm as a part of ER#5950128 */
4484 , s.hdr_term_cancel_source /* Added for ER 6684955 */
4485 , s.sl_term_cancel_source /* Added for ER 6684955 */
4486 FROM oki_dbi_cle_b_old s
4487 WHERE NOT EXISTS( SELECT NULL
4488 FROM oki_dbi_cle_b b
4489 WHERE b.cle_id = s.cle_id ) );
4490 p_recs_processed := SQL%ROWCOUNT;
4491
4492 rlog('Number of lines inserted into OKI_DBI_CLE_B : '|| p_recs_processed,2);
4493 p_recs_processed := l_count + p_recs_processed;
4494 COMMIT ;
4495
4496 rlog('Updation of summary table OKI_DBI_CLE_B from OKI_DBI_CLE_B_OLD completed: '
4497 ||fnd_date.date_to_displayDT(sysdate), 1) ;
4498
4499 EXCEPTION
4500 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
4501 RAISE;
4502 WHEN OTHERS THEN
4503 rlog (sqlerrm || ' ' || sqlcode,0);
4504 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
4505 fnd_message.set_name( application => 'FND'
4506 , name => 'CRM-DEBUG ERROR' ) ;
4507 fnd_message.set_token(
4508 token => 'ROUTINE'
4509 , value => 'OKI_DBI_LOAD_CLEB_PVT.INCR_LOAD' ) ;
4510 bis_collection_utilities.put_line(fnd_message.get) ;
4511 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR ;
4512 END incr_load;
4513
4514 /* *****************************************************************************
4515 Procedure : direct_load
4516 Description : Procedure to insert covered lines and renewal relationships
4517 Parameters : p_recs_processed:The number of records processed
4518 ************************************************************************** */
4519 PROCEDURE direct_load
4520 ( p_recs_processed OUT NOCOPY NUMBER
4521 ) IS
4522 l_login_id NUMBER;
4523 l_user_id NUMBER;
4524 l_request_id NUMBER;
4525 l_program_application_id NUMBER;
4526 l_program_id NUMBER;
4527 l_program_login_id NUMBER;
4528
4529 l_annu_curr_code VARCHAR2(20);
4530
4531 l_start_date DATE ;
4532 l_end_date DATE ;
4533 l_run_date DATE ;
4534 l_dop NUMBER;
4535 l_location VARCHAR2(1000);
4536 l_sysdate DATE;
4537
4538 l_sql_string VARCHAR2(1000) ;
4539 l_count NUMBER ;
4540
4541 l_oki_schema VARCHAR2(30) ;
4542 l_status VARCHAR2(30) ;
4543 l_industry VARCHAR2(30) ;
4544 l_salesperson_code VARCHAR2(80) ;
4545 l_glob_curr_code VARCHAR2(20);
4546 l_sglob_curr_code VARCHAR2(20);
4547 l_renewal_logic VARCHAR2(10);
4548 l_balance_logic VARCHAR2(10);
4549 l_service_code number;
4550 l_warranty_code number;
4551 l_ext_warr_code number;
4552
4553 BEGIN
4554
4555
4556
4557 l_login_id := FND_GLOBAL.login_id ;
4558 l_user_id := FND_GLOBAL.user_id ;
4559 l_request_id := fnd_global.CONC_REQUEST_ID;
4560 l_program_application_id := fnd_global.PROG_APPL_ID;
4561 l_program_id := fnd_global.CONC_PROGRAM_ID;
4562 l_program_login_id := fnd_global.conc_login_id;
4563
4564 l_sysdate := OKI_DBI_LOAD_CLEB_PVT.g_run_date ;
4565 l_annu_curr_code := bis_common_parameters. get_annualized_currency_code;
4566 l_glob_curr_code := 'PRIMARY';/* BUg 4015406 bis_common_parameters.get_currency_code; */
4567 l_sglob_curr_code := 'SECONDARY';/*bis_common_parameters.get_secondary_currency_code;*/
4568
4569 l_start_date := OKI_DBI_LOAD_CLEB_PVT.g_start_date ;
4570 l_end_date := OKI_DBI_LOAD_CLEB_PVT.g_end_date ;
4571 l_run_date := OKI_DBI_LOAD_CLEB_PVT.g_run_date ;
4572 l_dop := 1 ;
4573
4574 l_salesperson_code := fnd_profile.value('OKS_ENABLE_SALES_CREDIT');
4575 IF l_salesperson_code IN ('YES', 'DRT') THEN /* Added 'DRT' filter condition, Please refer Bug#5978601 */
4576 l_salesperson_code := fnd_profile.value('OKS_VENDOR_CONTACT_ROLE');
4577 ELSE
4578 l_salesperson_code := 'SALESPERSON';
4579 END IF ;
4580
4581 l_location := 'Insert Covered Lines ';
4582
4583 -- Lesters Feedback 5/19/04 force parallel qry
4584
4585 EXECUTE IMMEDIATE 'alter session force parallel query';
4586
4587 rlog('Populating Staging Table OKI_DBI_CLE_B_OLD - ' ||fnd_date.date_to_displayDT(sysdate),1) ;
4588 /* Balance logic for OI */
4589 l_balance_logic := nvl(fnd_profile.value('OKI_BAL_IDENT'),'CONTRDATE');
4590 /* Renewal logic for OI */
4591 l_renewal_logic := nvl(fnd_profile.value('OKI_REN_IDENT'),'STANDARD');
4592
4593 rlog( 'Profile value for balance logic is '|| l_balance_logic , 2 );
4594 rlog( 'Profile value for renewal logic is '|| l_renewal_logic , 2 );
4595
4596
4597
4598 SELECT Max(service_code),Max(warranty_code),Max(ext_warr_code)
4599 INTO l_service_code,l_warranty_code,l_ext_warr_code
4600 FROM
4601 (
4602 SELECT
4603 Decode(lty_code,'SERVICE',Id) service_code,
4604 Decode(lty_code,'WARRANTY',Id) warranty_code,
4605 Decode(lty_code,'EXT_WARRANTY',Id) ext_warr_code
4606 FROM okc_line_styles_b
4607 WHERE lty_code IN ('SERVICE','WARRANTY','EXT_WARRANTY')
4608 AND lse_parent_id IS NULL
4609 );
4610
4611 /* Effective_expire_date is populated only if the date signed is not null. Prior to 8.0,
4612 there was no scenario wherein a renewal could exist without thhe original contract was signed and so
4613 the expirations mv used r_date_signed and r_date_cancelled internally to ensure that date signed
4614 was present. Because of transfers scenarios in R12, a renewal relationship can exist without the original
4615 contract being signed and hence, to avoid unsigned contracts showing up in expirations, effective_expire_date
4616 is populated only when date signed is not null */
4617
4618 --++++++++++++++++++++++
4619 INSERT /*+ append parallel(old) */ into oki_dbi_cle_b_old old
4620 ( chr_id
4621 ,cle_id
4622 ,cle_creation_date
4623 ,inv_organization_id
4624 ,authoring_org_id
4625 ,application_id
4626 ,customer_party_id
4627 ,resource_group_id
4628 ,resource_id
4629 ,salesrep_id
4630 ,price_negotiated
4631 ,price_negotiated_f
4632 ,price_negotiated_g
4633 ,price_negotiated_sg
4634 ,contract_number
4635 ,contract_number_modifier
4636 ,buy_or_sell
4637 ,scs_code
4638 ,sts_code
4639 ,trn_code
4640 ,root_lty_code
4641 ,renewal_flag
4642 ,date_signed
4643 ,date_cancelled
4644 ,hstart_date
4645 ,hend_date
4646 ,start_date
4647 ,end_date
4648 ,date_terminated
4649 ,effective_start_date
4650 ,effective_end_date
4651 ,trx_func_curr_rate
4652 ,func_global_curr_rate
4653 ,func_sglobal_curr_rate
4654 ,sle_id
4655 ,service_item_id
4656 ,covered_item_id
4657 ,covered_item_org_id
4658 ,quantity
4659 ,uom_code
4660 ,grace_end_date
4661 ,expected_close_date
4662 ,win_percent
4663 ,ubt_amt
4664 ,ubt_amt_f
4665 ,ubt_amt_g
4666 ,ubt_amt_sg
4667 ,credit_amt
4668 ,credit_amt_f
4669 ,credit_amt_g
4670 ,credit_amt_sg
4671 ,override_amt
4672 ,override_amt_f
4673 ,override_amt_g
4674 ,override_amt_sg
4675 ,supp_credit
4676 ,supp_credit_f
4677 ,supp_credit_g
4678 ,supp_credit_sg
4679 ,renewal_type
4680 ,term_flag
4681 ,annualization_factor
4682 ,ubt_amt_a
4683 ,credit_amt_a
4684 ,override_amt_a
4685 ,supp_credit_a
4686 ,price_negotiated_a
4687 ,created_by
4688 ,creation_date
4689 ,last_update_date
4690 ,gsd_flag
4691 -- add the four extra columns
4692 , effective_active_date
4693 , effective_term_date
4694 , effective_expire_date
4695 , termination_entry_date
4696 , falsernwlyn
4697 ,curr_code
4698 ,curr_code_f
4699 ,hdr_order_number
4700 ,hdr_sts_code
4701 ,hdr_trn_code
4702 ,hdr_renewal_type
4703 ,hdr_date_approved
4704 ,hdr_date_cancelled
4705 ,hdr_date_terminated
4706 ,hdr_creation_date
4707 ,hdr_last_update_date
4708 ,service_item_org_id
4709 ,sl_line_number
4710 ,sl_sts_code
4711 ,sl_trn_code
4712 ,sl_renewal_type
4713 ,sl_start_date
4714 ,sl_end_Date
4715 ,sl_date_cancelled
4716 ,sl_date_terminated
4717 ,sl_creation_date
4718 ,sl_last_update_date
4719 ,order_number
4720 ,unit_price_percent
4721 ,unit_price
4722 ,unit_price_f
4723 ,unit_price_g
4724 ,unit_price_sg
4725 ,list_price
4726 ,list_price_f
4727 ,list_price_g
4728 ,list_price_sg
4729 ,duration_uom
4730 ,duration_qty
4731 ,cl_last_update_date
4732 ,cov_prod_id
4733 ,cov_prod_system_id
4734 ,line_number
4735 ,line_type
4736 ,hdr_bill_site_id
4737 ,hdr_ship_site_id
4738 ,hdr_acct_rule_id
4739 ,hdr_grace_end_date
4740 ,hdr_date_signed
4741 ,p_cle_id
4742 ,r_cle_id
4743 ,hdr_subsequent_renewal_type /* for ER#5760744 */
4744 ,agreement_type_code /* for ER 6062516 */
4745 ,agreement_name /* for ER 6062516 */
4746 ,negotiation_status /* for ER#5950128 */
4747 ,reminder /* for ER#5950128 */
4748 ,HDR_TERM_CANCEL_SOURCE /* Added as part of ER 6684955 */
4749 ,SL_TERM_CANCEL_SOURCE /* Added as part of ER 6684955 */
4750 )
4751 ( SELECT chr_id
4752 ,cle_id
4753 ,cle_creation_date
4754 ,inv_organization_id
4755 ,authoring_org_id
4756 ,application_id
4757 ,customer_party_id
4758 ,resource_group_id
4759 ,resource_id
4760 ,salesrep_id
4761 ,price_negotiated
4762 ,price_negotiated_f
4763 ,price_negotiated_g
4764 ,price_negotiated_sg
4765 ,contract_number
4766 ,contract_number_modifier
4767 ,buy_or_sell
4768 ,scs_code
4769 ,sts_code
4770 ,trn_code
4771 ,root_lty_code
4772 ,renewal_flag
4773 ,date_signed
4774 ,date_cancelled
4775 ,hstart_date
4776 ,hend_date
4777 ,start_date
4778 ,end_date
4779 ,date_terminated
4780 ,effective_start_date
4781 ,effective_end_date
4782 ,trx_func_curr_rate
4783 ,func_global_curr_rate
4784 ,func_sglobal_curr_rate
4785 ,sle_id
4786 ,service_item_id
4787 ,covered_item_id
4788 ,covered_item_org_id
4789 ,quantity
4790 ,uom_code
4791 ,grace_end_date
4792 ,expected_close_date
4793 ,win_percent
4794 ,ubt_amt
4795 ,ubt_amt_f
4796 ,ubt_amt_g
4797 ,ubt_amt_sg
4798 ,credit_amt
4799 ,credit_amt_f
4800 ,credit_amt_g
4801 ,credit_amt_sg
4802 ,override_amt
4803 ,override_amt_f
4804 ,override_amt_g
4805 ,override_amt_sg
4806 ,supp_credit
4807 ,supp_credit_f
4808 ,supp_credit_g
4809 ,supp_credit_sg
4810 ,renewal_type
4811 ,term_flag
4812 ,annualization_factor
4813 ,DECODE(l_annu_curr_code, l_glob_curr_code, ubt_amt_g
4814 , l_sglob_curr_code, ubt_amt_sg
4815 , ubt_amt_g) * annualization_factor ubt_amt_a
4816 ,DECODE(l_annu_curr_code, l_glob_curr_code, credit_amt_g
4817 , l_sglob_curr_code, credit_amt_sg
4818 , credit_amt_g) * annualization_factor credit_amt_a
4819 ,DECODE(l_annu_curr_code, l_glob_curr_code, override_amt_g
4820 , l_sglob_curr_code, override_amt_sg
4821 , override_amt_g) * annualization_factor override_amt_a
4822 ,DECODE(l_annu_curr_code, l_glob_curr_code, supp_credit_g
4823 , l_sglob_curr_code, supp_credit_sg
4824 , supp_credit_g) * annualization_factor supp_credit_a
4825 ,DECODE(l_annu_curr_code, l_glob_curr_code, price_negotiated_g
4826 , l_sglob_curr_code, price_negotiated_sg
4827 , price_negotiated_g) * annualization_factor price_negotiated_a
4828 , l_login_id
4829 , l_run_date
4830 , l_run_date
4831 , gsd_flag
4832 -- add the four extra columns
4833 , effective_active_date
4834 , effective_term_date
4835 , effective_expire_date
4836 , termination_entry_date
4837 , falsernwlyn
4838 , curr_code
4839 , curr_code_f
4840 , hdr_order_number
4841 , hdr_sts_code
4842 , hdr_trn_code
4843 , hdr_renewal_type
4844 , hdr_date_approved
4845 , hdr_date_cancelled
4846 , hdr_date_terminated
4847 , hdr_creation_date
4848 , hdr_last_update_date
4849 , service_item_org_id
4850 , sl_line_number
4851 , sl_sts_code
4852 , sl_trn_code
4853 , sl_renewal_type
4854 , sl_start_date
4855 , sl_end_Date
4856 , sl_date_cancelled
4857 , sl_date_terminated
4858 , sl_creation_date
4859 , sl_last_update_date
4860 , order_number
4861 , unit_price_percent
4862 , unit_price
4863 , unit_price_f
4864 , unit_price_g
4865 , unit_price_sg
4866 , list_price
4867 , list_price_f
4868 , list_price_g
4869 , list_price_sg
4870 , duration_uom
4871 , duration_qty
4872 , cl_last_update_date
4873 , cov_prod_id
4874 , cov_prod_system_id
4875 , line_number
4876 , line_type
4877 , hdr_bill_site_id
4878 , hdr_ship_site_id
4879 , hdr_acct_rule_id
4880 , hdr_grace_end_date
4881 , hdr_date_signed
4882 , to_number(p_cle_id)
4883 , to_number(r_cle_id)
4884 , hdr_subsequent_renewal_type /* for ER#5760744 */
4885 , agreement_type_code /* for ER 6062516 */
4886 , agreement_name /* for ER 6062516 */
4887 , negotiation_status /* Added this colunm as a part of ER#5950128 */
4888 , reminder /* Added this colunm as a part of ER#5950128 */
4889 , HDR_TERM_CANCEL_SOURCE /* Added as part of ER 6684955 */
4890 , SL_TERM_CANCEL_SOURCE /* Added as part of ER 6684955 */
4891 FROM
4892 ( SELECT
4893 ilv1.chr_id
4894 , ilv1.cle_id
4895 , ilv1.cle_creation_date
4896 , ilv1.inv_organization_id
4897 , ilv1.authoring_org_id
4898 , ilv1.application_id
4899 , ilv1.customer_party_id
4900 , ilv1.resource_group_id
4901 , ilv1.resource_id
4902 , ilv1.salesrep_id
4903 , ilv1.price_negotiated
4904 , ilv1.price_negotiated_f
4905 , ilv1.price_negotiated_g
4906 , ilv1.price_negotiated_sg
4907 , ilv1.contract_number
4908 , ilv1.contract_number_modifier
4909 , ilv1.buy_or_sell
4910 , ilv1.scs_code
4911 , ilv1.sts_code -- line level cancellation code
4912 , ilv1.trn_code
4913 , ilv1.root_lty_code
4914 , CASE when l_renewal_logic = 'STANDARD' THEN
4915 NVL (ilv1.renewal_flag, 0)
4916 ELSE
4917 DECODE(ilv1.order_number,NULL,1,0)
4918 END AS renewal_flag
4919 , p_cle_id
4920 , r_cle_id
4921 , ilv1.date_signed
4922 , ilv1.date_cancelled -- line level cancelled date
4923 , ilv1.hstart_date -- header start_date
4924 , ilv1.hend_date -- header end_date
4925 , ilv1.start_date
4926 , ilv1.end_date
4927 , ilv1.date_terminated
4928 , ilv1.trx_func_curr_rate
4929 , ilv1.func_global_curr_rate
4930 , ilv1.func_sglobal_curr_rate
4931 , ilv1.sle_id
4932 , ilv1.service_item_id
4933 , ilv1.covered_item_id
4934 , decode (ilv1.covered_item_id, -1,-99,ilv1.inv_organization_id) covered_item_org_id
4935 , ilv1.quantity
4936 , ilv1.uom_code
4937 , ilv1.grace_end_date
4938 , ilv1.expected_close_date
4939 , ilv1.win_percent
4940 , ilv1.ubt_amt
4941 , ilv1.ubt_amt * ilv1.trx_func_curr_rate ubt_amt_f
4942 , ilv1.ubt_amt * ilv1.trx_func_curr_rate * ilv1.func_global_curr_rate ubt_amt_g
4943 , ilv1.ubt_amt * ilv1.trx_func_curr_rate * ilv1.func_sglobal_curr_rate ubt_amt_sg
4944 , ilv1.credit_amt
4945 , ilv1.credit_amt * ilv1.trx_func_curr_rate credit_amt_f
4946 , ilv1.credit_amt * ilv1.trx_func_curr_rate * ilv1.func_global_curr_rate credit_amt_g
4947 , ilv1.credit_amt * ilv1.trx_func_curr_rate * ilv1.func_sglobal_curr_rate credit_amt_sg
4948 , ilv1.override_amt
4949 , ilv1.override_amt * ilv1.trx_func_curr_rate override_amt_f
4950 , ilv1.override_amt * ilv1.trx_func_curr_rate * ilv1.func_global_curr_rate override_amt_g
4951 , ilv1.override_amt * ilv1.trx_func_curr_rate * ilv1.func_sglobal_curr_rate override_amt_sg
4952 , ilv1.supp_credit
4953 , ilv1.supp_credit * ilv1.trx_func_curr_rate supp_credit_f
4954 , ilv1.supp_credit * ilv1.trx_func_curr_rate * ilv1.func_global_curr_rate supp_credit_g
4955 , ilv1.supp_credit * ilv1.trx_func_curr_rate * ilv1.func_sglobal_curr_rate supp_credit_sg
4956 , ilv1.renewal_type
4957 , (CASE WHEN ilv1.date_terminated < ilv1.start_date
4958 THEN -1
4959 ELSE 1
4960 END ) term_flag
4961 , ilv1.annualization_factor
4962 , ilv1.gsd_flag
4963 , CASE WHEN l_balance_logic='CONTRDATE' THEN date_terminated
4964 ELSE
4965 least(greatest(date_terminated,termination_entry_date,nvl(date_signed,date_terminated)),
4966 greatest(ilv1.date_signed,ilv1.end_date))
4967 --Usually date_terminated cannot be prsent without date signed. This is a check for bad data available in the volume environments
4968 END effective_term_date
4969 , CASE WHEN l_balance_logic='CONTRDATE' and ilv1.date_signed is not null THEN
4970 ilv1.end_date
4971 ELSE
4972 greatest(ilv1.date_signed,ilv1.end_date)
4973 END effective_expire_date
4974 , CASE WHEN l_balance_logic='CONTRDATE' and ilv1.date_signed is not null THEN
4975 ilv1.start_date
4976 ELSE
4977 greatest(ilv1.date_signed,ilv1.start_date)
4978 END effective_active_date
4979 , NVL2(ilv1.date_terminated,termination_entry_date,NULL) termination_entry_date
4980 /* We change the definition given in the inner query if the value of balance logic is event dates */
4981 , CASE WHEN l_balance_logic='CONTRDATE' THEN
4982 ilv1.effective_start_date
4983 ELSE
4984 greatest(ilv1.date_signed,ilv1.start_date)
4985 END effective_start_date
4986 , CASE WHEN l_balance_logic='CONTRDATE' THEN
4987 ilv1.effective_end_date
4988 ELSE
4989 NVL2(ilv1.date_terminated,
4990 least(greatest(date_terminated,termination_entry_date,nvl(date_signed,date_terminated)),
4991 greatest(ilv1.date_signed,ilv1.end_date))
4992 ,greatest(ilv1.end_date,ilv1.date_signed))
4993 END effective_end_date
4994 , CASE WHEN l_renewal_logic= 'ORDERNO' AND ilv1.order_number IS NULL
4995 AND ilv1.renewal_flag =0 THEN 'Y'
4996 END falsernwlyn
4997 , ilv1.curr_code
4998 , ilv1.curr_code_f
4999 , ilv1.hdr_order_number
5000 , ilv1.hdr_sts_code
5001 , ilv1.hdr_trn_code
5002 , ilv1.hdr_renewal_type
5003 , ilv1.hdr_date_approved
5004 , ilv1.hdr_date_cancelled
5005 , ilv1.hdr_date_terminated
5006 , ilv1.hdr_creation_date
5007 , ilv1.hdr_last_update_date
5008 , ilv1.service_item_org_id
5009 , ilv1.sl_line_number
5010 , ilv1.sl_sts_code
5011 , ilv1.sl_trn_code
5012 , ilv1.sl_renewal_type
5013 , ilv1.sl_start_date
5014 , ilv1.sl_end_Date
5015 , ilv1.sl_date_cancelled
5016 , ilv1.sl_date_terminated
5017 , ilv1.sl_creation_date
5018 , ilv1.sl_last_update_date
5019 , ilv1.order_number
5020 , ilv1.unit_price_percent
5021 , ilv1.unit_price
5022 , nvl(ilv1.unit_price,0) * ilv1.trx_func_curr_rate unit_price_f
5023 , nvl(ilv1.unit_price,0) * ilv1.trx_func_curr_rate * ilv1.func_global_curr_rate unit_price_g
5024 , nvl(ilv1.unit_price,0) * ilv1.trx_func_curr_rate * ilv1.func_sglobal_curr_rate unit_price_sg
5025 , ilv1.list_price
5026 , nvl(ilv1.list_price,0) * ilv1.trx_func_curr_rate list_price_f
5027 , nvl(ilv1.list_price,0) * ilv1.trx_func_curr_rate * ilv1.func_global_curr_rate list_price_g
5028 , nvl(ilv1.list_price,0) * ilv1.trx_func_curr_rate * ilv1.func_sglobal_curr_rate list_price_sg
5029 , ilv1.duration_uom
5030 , ilv1.duration_qty
5031 , ilv1.cl_last_update_date
5032 , ilv1.cov_prod_id
5033 , ilv1.cov_prod_system_id
5034 , ilv1.line_number
5035 , ilv1.line_type
5036 , ilv1.hdr_bill_site_id
5037 , ilv1.hdr_ship_site_id
5038 , ilv1.hdr_acct_rule_id
5039 , ilv1.hdr_grace_end_date
5040 , ilv1.hdr_date_signed
5041 , ilv1.hdr_subsequent_renewal_type /* Added ER#5760744 */
5042 , ilv1.agreement_type_code /* for ER 6062516 */
5043 , ilv1.agreement_name /* for ER 6062516 */
5044 , ilv1.negotiation_status /* Added for ER#5950128 */
5045 , ilv1.reminder /* Added ER#5950128 */
5046 , ilv1.HDR_TERM_CANCEL_SOURCE /* ER 6684955 */
5047 , ilv1.SL_TERM_CANCEL_SOURCE /* ER 6684955 */
5048 -- modified hints as feedback from performance team 27-OCT-2005.
5049 FROM
5050 (
5051 SELECT
5052 newilv.*,
5053 (SELECT DECODE(COUNT(1), 0, 0, 1) renewal_flag
5054 FROM okc_operation_lines okl
5055 , okc_operation_instances opins
5056 WHERE rownum=1
5057 AND okl.object_cle_id IS NOT NULL
5058 AND okl.subject_cle_id IS NOT NULL
5059 AND okl.oie_id=opins.id
5060 AND opins.cop_id in (g_renewal_id,g_ren_con_id)
5061 AND newilv.cle_id=okl.subject_cle_id
5062 ) RENEWAL_FLAG
5063 FROM
5064 (SELECT /*+ ordered use_hash(h,sl,l,root_temp,itm,itm2,cii,oksl) full(l)
5065 parallel(cur) parallel(h) parallel(l) parallel(sl)
5066 parallel(oksl) parallel(itm) parallel(itm2) parallel(cii)
5067 swap_join_inputs(root_temp)
5068 pq_distribute(h hash,hash)
5069 pq_distribute(oksl hash,hash)
5070 pq_distribute(itm,hash,hash)
5071 pq_distribute(l,hash,hash) pq_distribute(itm2,hash,hash) */
5072 h.chr_id chr_id
5073 , l.id cle_id
5074 , l.creation_date cle_creation_date
5075 , l.annualized_factor annualization_factor
5076 , h.master_organization_id AS inv_organization_id
5077 , h.authoring_org_id
5078 , h.application_id
5079 , TO_NUMBER(h.customer_party_id) customer_party_id
5080 , h.salesrep_id
5081 , h.resource_id
5082 , h.resource_group_id
5083 , nvl(l.price_negotiated,0) price_negotiated
5084 , nvl(l.price_negotiated,0) * cur.rate_f price_negotiated_f
5085 , nvl(l.price_negotiated,0) * cur.rate_g price_negotiated_g
5086 , nvl(l.price_negotiated,0) * cur.rate_sg price_negotiated_sg
5087 , h.contract_number
5088 , h.contract_number_modifier
5089 , h.buy_or_sell
5090 , h.scs_code
5091 , l.sts_code
5092 , NVL(l.trn_code,h.trn_code) trn_code
5093 , root_temp.root_lty_code
5094 /* , row_number() over (partition by l.id order by okl.last_update_date desc) rnum */
5095 , to_number(null) p_cle_id
5096 , r1.subject_cle_id r_cle_id
5097 , CASE WHEN l.date_cancelled is null
5098 THEN h.date_signed
5099 ELSE null
5100 END date_signed
5101 , l.date_cancelled date_cancelled
5102 , h.start_date hstart_date
5103 , h.end_date hend_date
5104 , l.start_date start_date
5105 , COALESCE((l.end_date + 1)
5106 , (h.end_date + 1)
5107 , g_4712_date) end_date
5108 ,trunc(months_between((COALESCE((l.end_date)
5109 , (h.end_date)
5110 , g_4712_date)), l.start_date)/12) nyears
5111 , NVL2(h.date_signed,l.date_terminated,NULL ) AS date_terminated
5112 , NVL2(h.date_signed, l.start_date,NULL) effective_start_date
5113 , NVL2(h.date_signed
5114 , LEAST(COALESCE(l.end_date
5115 , h.end_date
5116 , g_4712_date) +1
5117 , COALESCE(l.date_terminated
5118 , h.date_terminated
5119 , g_4712_date))
5120 , NULL) effective_end_date
5121 , cur.rate_f trx_func_curr_rate
5122 , cur.rate_g / decode(cur.rate_f,0,-1,cur.rate_f) func_global_curr_rate
5123 , cur.rate_sg / decode(cur.rate_f,0,-1,cur.rate_f) func_sglobal_curr_rate
5124 , CASE WHEN h.end_date = l.end_date
5125 THEN h.grace_end_date
5126 ELSE NULL
5127 END AS grace_end_date
5128 -- service item, covered item
5129 , sl.id AS sle_id
5130 , itm.object1_id1 AS service_item_id
5131 , NVL((CASE WHEN root_temp.lty_code IN ('COVER_ITEM')
5132 -- if to_number is removed get ORA-00932 inconsistent datatypes
5133 THEN TO_NUMBER (itm2.object1_id1)
5134 WHEN root_temp.lty_code IN ('COVER_PROD')
5135 THEN cii.inventory_item_id
5136 END),-1) AS covered_item_id
5137 , itm2.number_of_items quantity
5138 , itm2.uom_code AS uom_code
5139 -- Forecast
5140 , h.est_rev_percent win_percent
5141 , h.est_rev_date expected_close_date
5142 -- terminated amounts
5143 , nvl(oksl.ubt_amount,0) ubt_amt
5144 , nvl(oksl.credit_amount,0) credit_amt
5145 , nvl(oksl.override_amount,0) override_amt
5146 , nvl(oksl.suppressed_credit,0) supp_credit
5147 , CASE WHEN nvl(h.renewal_type_code,'X') = 'DNR'
5148 OR nvl(sl.line_renewal_type_code,'X') ='DNR' THEN
5149 'DNR'
5150 ELSE
5151 l.line_renewal_type_code
5152 END renewal_type
5153 , h.gsd_flag
5154 , l.last_update_date termination_entry_date
5155 , h.trx_currency curr_code
5156 , h.func_currency curr_code_f
5157 , h.order_number hdr_order_number
5158 , h.sts_code hdr_sts_code
5159 , h.trn_code hdr_trn_code
5160 , h.renewal_type_code hdr_renewal_type
5161 , h.date_approved hdr_date_approved
5162 , h.datetime_cancelled hdr_date_cancelled
5163 , h.date_terminated hdr_date_terminated
5164 , h.creation_Date hdr_creation_date
5165 , h.last_update_date hdr_last_update_date
5166 , itm.object1_id2 service_item_org_id
5167 , sl.line_number sl_line_number
5168 , sl.sts_code sl_sts_code
5169 , NVL(sl.trn_code,h.trn_code) sl_trn_code
5170 , ( CASE WHEN h.renewal_type_code = 'DNR'
5171 THEN h.renewal_type_code
5172 ELSE sl.line_renewal_type_code
5173 END ) sl_renewal_type
5174 , sl.start_date sl_start_date
5175 , sl.end_date sl_end_Date
5176 , sl.date_cancelled sl_date_cancelled
5177 , sl.date_terminated sl_date_terminated
5178 , sl.creation_date sl_creation_date
5179 , sl.last_update_date sl_last_update_date
5180 , decode(root_temp.root_lty_code,'WARRANTY',h.order_number,oehdr.order_number) order_number
5181 , decode(oksl.toplvl_operand_code,
5182 'PERCENT_PRICE',oksl.toplvl_operand_val,
5183 NULL) unit_price_percent
5184 , nvl(l.price_unit,0) unit_price
5185 , nvl(l.line_list_price,0) list_price
5186 , oksl.toplvl_uom_code duration_uom
5187 , oksl.toplvl_price_qty duration_qty
5188 , l.last_update_date cl_last_update_date
5189 , CASE WHEN root_temp.lty_code IN ('COVER_PROD')
5190 THEN cii.instance_id
5191 ELSE -999
5192 END cov_prod_id
5193 , CASE WHEN root_temp.lty_code IN ('COVER_PROD')
5194 THEN NVL(cii.system_id,-1)
5195 ELSE -999
5196 END cov_prod_system_id
5197 , sl.line_number ||'.' || l.line_number line_number
5198 , root_temp.lty_code line_type
5199 , h.Bill_to_site_use_id hdr_bill_site_id
5200 , h.Ship_to_site_use_id hdr_ship_site_id
5201 , h.Acct_rule_id hdr_acct_rule_id
5202 , h.grace_end_date hdr_grace_end_date
5203 , h.date_signed hdr_date_signed
5204 , h.subsequent_renewal_type_code hdr_subsequent_renewal_type /* ER#5760744 */
5205 , agmt.agreement_type_code /* for ER 6062516 */
5206 , agmt.agreement_name /* for ER 6062516 */
5207 , h.negotiation_status negotiation_status /* ER#5950128 */
5208 , decode(h.reminder, 'Y', 'Enable', 'N', 'Disable', h.reminder) reminder /* ER#5950128 */
5209 , h.HDR_TERM_CANCEL_SOURCE HDR_TERM_CANCEL_SOURCE /* ER 6684955 */
5210 , NVL(sl.term_cancel_source, h.HDR_TERM_CANCEL_SOURCE ) SL_TERM_CANCEL_SOURCE /* ER 6684955 */
5211 FROM
5212 oki_dbi_chr_inc h
5213 , oki_dbi_curr_conv cur
5214 , okc_k_lines_b sl
5215 , okc_k_items itm
5216 , okc_k_lines_b l
5217 , ( select /*+ no_merge */ oab.agreement_id
5218 , qpl.meaning agreement_type_code
5219 , oat.name agreement_name
5220 from oe_agreements_tl oat,
5221 qp_lookups qpl,
5222 oe_agreements_b oab
5223 where oab.agreement_id = oat.agreement_id
5224 and oat.language = userenv('LANG')
5225 and qpl.lookup_type(+) = 'QP_AGREEMENT_TYPE'
5226 and qpl.lookup_code(+) = oab.agreement_type_code
5227 ) agmt /* for ER 6062516 */
5228 , ( Select /*+ no_merge */ id,lty_code,decode(lse_parent_id,l_service_code,'SERVICE',l_warranty_code,'WARRANTY',l_ext_warr_code,'EXT_WARRANTY') root_lty_code
5229 FROM okc_line_styles_b n
5230 where lse_parent_id in (l_service_code,l_warranty_code,l_ext_warr_code)
5231 ) root_temp
5232 , oks_k_lines_b oksl
5233 /*, okc_operation_lines okl
5234 , okc_operation_instances OPINS */
5235 , okc_k_items itm2
5236 , csi_item_instances cii
5237 , ( /* rel objs has multiple entries for the same order number */
5238 Select /*+ no_merge parallel(oehdr) parallel(oelin) parallel(okcrel) */ okcrel.cle_id,max(oehdr.order_number) order_number
5239 from
5240 oe_order_headers_all oehdr
5241 , oe_order_lines_all oelin
5242 , okc_k_rel_objs okcrel
5243 WHERE okcrel.object1_id1 = oelin.line_id
5244 AND oehdr.header_id = oelin.header_id
5245 group by okcrel.cle_id
5246 ) oehdr
5247 , ( SELECT object_cle_id ,
5248 MAX(subject_cle_id) KEEP (DENSE_RANK LAST ORDER BY okl.last_update_date) subject_cle_id
5249 FROM okc_operation_lines okl
5250 , okc_operation_instances opins
5251 WHERE
5252 okl.object_cle_id IS NOT NULL
5253 AND okl.subject_cle_id IS NOT NULL
5254 AND okl.oie_id=opins.id
5255 AND opins.cop_id in (g_renewal_id,g_ren_con_id)
5256 group by okl.object_cle_id
5257 ) r1
5258 WHERE 1 = 1
5259 AND l.id = oehdr.cle_id(+)
5260 AND (h.chr_id = cur.chr_id OR upper(cur.rate_type) <> 'USER')
5261 AND h.conversion_date = cur.curr_conv_date
5262 AND h.trx_currency = cur.from_currency
5263 AND h.func_currency = cur.to_currency
5264 AND h.trx_rate_type = cur.rate_type
5265 AND h.chr_id = sl.chr_id
5266 AND sl.ID = l.cle_id
5267 AND l.price_level_ind = 'Y'
5268 AND l.lse_id = root_temp.id
5269 AND sl.ID = itm.cle_id
5270 AND l.id = itm2.cle_id
5271 AND itm2.object1_id1 = cii.instance_id(+)
5272 AND l.id = oksl.cle_id
5273 AND l.id = r1.object_cle_id(+)
5274 AND cii.last_oe_agreement_id = agmt.agreement_id(+)
5275 /* AND l.id = okl.object_cle_id(+)
5276 AND okl.object_cle_id(+) IS NOT NULL
5277 AND okl.subject_cle_id(+) IS NOT NULL
5278 AND okl.oie_id=opins.id(+)
5279 AND opins.cop_id(+) = decode(opins.cop_id(+),g_renewal_id,g_renewal_id,g_ren_con_id,g_ren_con_id) */
5280 ) newilv
5281 ) ilv1
5282 WHERE 1=1
5283 ));
5284 p_recs_processed := SQL%ROWCOUNT ;
5285
5286 --++++++++++++++++++++++++
5287
5288 --Lesters Feedback 5/19/04 disable force parallel qry
5289 EXECUTE IMMEDIATE 'alter session enable parallel query';
5290 l_count := p_recs_processed;
5291 rlog( 'Number of lines inserted into OKI_DBI_CLE_B_OLD : ' ||
5292 to_char(l_count),2);
5293 COMMIT;
5294 rlog('Load of Staging Table OKI_DBI_CLE_B_OLD completed - ' ||
5295 fnd_date.date_to_displayDT(SYSDATE),1) ;
5296
5297 GATHER_TABLE_STATS(TABNAME=>'OKI_DBI_CLE_B_OLD');
5298
5299 rlog('Populating Base Summary Table OKI_DBI_CLE_B - ' || fnd_date.date_to_displayDT(SYSDATE),1) ;
5300
5301
5302 -- Added enable parallel dml since change in dbms_stats implementation disables
5303 -- the parellel dml call which results in the subsequent insert being serialized.
5304 -- No longer need since it is now done in the gather_table_stats procedure
5305 -- EXECUTE IMMEDIATE 'alter session enable parallel dml';
5306
5307 INSERT /*+ APPEND parallel(f)*/ INTO OKI_DBI_CLE_B f
5308 (
5309 chr_id
5310 , cle_id
5311 , cle_creation_date
5312 , inv_organization_id
5313 , authoring_org_id
5314 , application_id
5315 , customer_party_id
5316 , salesrep_id
5317 , price_negotiated
5318 , price_negotiated_f
5319 , price_negotiated_g
5320 , price_negotiated_sg
5321 , contract_number
5322 , contract_number_modifier
5323 , buy_or_sell
5324 , scs_code
5325 , sts_code
5326 , trn_code
5327 , root_lty_code
5328 , renewal_flag
5329 , date_signed
5330 , date_cancelled
5331 , hstart_date
5332 , hend_date
5333 , start_date
5334 , end_date
5335 , date_terminated
5336 , effective_start_date
5337 , effective_end_date
5338 , trx_func_curr_rate
5339 , func_global_curr_rate
5340 , func_sglobal_curr_rate
5341 , created_by
5342 , last_update_login
5343 , creation_date
5344 , last_updated_by
5345 , last_update_date
5346 , request_id
5347 , program_application_id
5348 , program_id
5349 , program_login_id
5350 , resource_group_id
5351 , resource_id
5352 , sle_id
5353 , service_item_id
5354 , covered_item_id
5355 , covered_item_org_id
5356 , quantity
5357 , uom_code
5358 , grace_end_date
5359 , expected_close_date
5360 , win_percent
5361 , ubt_amt
5362 , ubt_amt_f
5363 , ubt_amt_g
5364 , ubt_amt_sg
5365 , credit_amt
5366 , credit_amt_f
5367 , credit_amt_g
5368 , credit_amt_sg
5369 , override_amt
5370 , override_amt_f
5371 , override_amt_g
5372 , override_amt_sg
5373 , supp_credit
5374 , supp_credit_f
5375 , supp_credit_g
5376 , supp_credit_sg
5377 , renewal_type
5378 , term_flag
5379 , r_chr_id
5380 , r_cle_id
5381 , r_date_signed
5382 , r_date_cancelled
5383 , annualization_factor
5384 , p_chr_id
5385 , p_cle_id
5386 , p_price_negotiated
5387 , p_price_negotiated_f
5388 , p_price_negotiated_g
5389 , p_price_negotiated_sg
5390 , p_grace_end_date
5391 , p_ubt_amt
5392 , p_ubt_amt_f
5393 , p_ubt_amt_g
5394 , p_ubt_amt_sg
5395 , p_credit_amt
5396 , p_credit_amt_f
5397 , p_credit_amt_g
5398 , p_credit_amt_sg
5399 , p_override_amt
5400 , p_override_amt_f
5401 , p_override_amt_g
5402 , p_override_amt_sg
5403 , p_supp_credit
5404 , p_supp_credit_f
5405 , p_supp_credit_g
5406 , p_supp_credit_sg
5407 , p_end_date
5408 , p_term_flag
5409 , price_negotiated_a
5410 , ubt_amt_a
5411 , credit_amt_a
5412 , override_amt_a
5413 , supp_credit_a
5414 , p_price_negotiated_a
5415 , p_ubt_amt_a
5416 , p_credit_amt_a
5417 , p_override_amt_a
5418 , p_supp_credit_a
5419 , gsd_flag
5420 , falsernwlyn
5421 , effective_term_date
5422 , effective_expire_date
5423 , effective_active_date
5424 , termination_entry_date
5425 , curr_code
5426 , curr_code_f
5427 , hdr_order_number
5428 , hdr_sts_code
5429 , hdr_trn_code
5430 , hdr_renewal_type
5431 , hdr_date_approved
5432 , hdr_date_cancelled
5433 , hdr_date_terminated
5434 , hdr_creation_date
5435 , hdr_last_update_date
5436 , service_item_org_id
5437 , sl_line_number
5438 , sl_sts_code
5439 , sl_trn_code
5440 , sl_renewal_type
5441 , sl_start_date
5442 , sl_end_Date
5443 , sl_date_cancelled
5444 , sl_date_terminated
5445 , sl_creation_date
5446 , sl_last_update_date
5447 , order_number
5448 , unit_price_percent
5449 , unit_price
5450 , unit_price_f
5451 , unit_price_g
5452 , unit_price_sg
5453 , list_price
5454 , list_price_f
5455 , list_price_g
5456 , list_price_sg
5457 , duration_uom
5458 , duration_qty
5459 , cl_last_update_date
5460 , cov_prod_id
5461 , cov_prod_system_id
5462 , line_number
5463 , line_type
5464 , hdr_bill_site_id
5465 , hdr_ship_site_id
5466 , hdr_acct_rule_id
5467 , hdr_grace_end_date
5468 , hdr_date_signed
5469 , hdr_subsequent_renewal_type /* ER#5760744 */
5470 , agreement_type_code /* ER 6062516 */
5471 , agreement_name /* ER 6062516 */
5472 , negotiation_status /* ER#5950128 */
5473 , reminder /* ER#5950128 */
5474 , hdr_term_cancel_source /* ER 6684955 */
5475 , sl_term_cancel_source /* ER 6684955 */
5476 )
5477 SELECT /*+ ordered use_hash(rl) parallel(ren_rel) parallel(rl)
5478 pq_distribute(rl,hash,hash) */
5479 ren_rel.chr_id
5480 , ren_rel.cle_id
5481 , ren_rel.cle_creation_date
5482 , ren_rel.inv_organization_id
5483 , ren_rel.authoring_org_id
5484 , ren_rel.application_id
5485 , ren_rel.customer_party_id
5486 , ren_rel.salesrep_id
5487 , ren_rel.price_negotiated
5488 , ren_rel.price_negotiated_f
5489 , ren_rel.price_negotiated_g
5490 , ren_rel.price_negotiated_sg
5491 , ren_rel.contract_number
5492 , ren_rel.contract_number_modifier
5493 , ren_rel.buy_or_sell
5494 , ren_rel.scs_code
5495 , ren_rel.sts_code
5496 , ren_rel.trn_code
5497 , ren_rel.root_lty_code
5498 , ren_rel.renewal_flag
5499 , ren_rel.date_signed
5500 , ren_rel.date_cancelled
5501 , ren_rel.hstart_date
5502 , ren_rel.hend_date
5503 , ren_rel.start_date
5504 , ren_rel.end_date
5505 , ren_rel.date_terminated
5506 , ren_rel.effective_start_date
5507 , ren_rel.effective_end_date
5508 , ren_rel.trx_func_curr_rate
5509 , ren_rel.func_global_curr_rate
5510 , ren_rel.func_sglobal_curr_rate
5511 , l_user_id
5512 , l_login_id
5513 , l_run_date
5514 , l_user_id
5515 , l_run_date
5516 -- CM request ID columns here
5517 , l_request_id
5518 , l_program_application_id
5519 , l_program_id
5520 , l_program_login_id
5521 -- Resource, resource group
5522 , ren_rel.resource_group_id
5523 , ren_rel.resource_id
5524 -- service item, covered item
5525 , ren_rel.sle_id
5526 , ren_rel.service_item_id
5527 , ren_rel.covered_item_id
5528 , ren_rel.covered_item_org_id
5529 , ren_rel.quantity
5530 , ren_rel.uom_code
5531 , ren_rel.grace_end_date
5532 -- Forecast
5533 , ren_rel.expected_close_date
5534 , ren_rel.win_percent
5535 , ren_rel.ubt_amt
5536 , ren_rel.ubt_amt_f
5537 , ren_rel.ubt_amt_g
5538 , ren_rel.ubt_amt_sg
5539 , ren_rel.credit_amt
5540 , ren_rel.credit_amt_f
5541 , ren_rel.credit_amt_g
5542 , ren_rel.credit_amt_sg
5543 , ren_rel.override_amt
5544 , ren_rel.override_amt_f
5545 , ren_rel.override_amt_g
5546 , ren_rel.override_amt_sg
5547 , ren_rel.supp_credit
5548 , ren_rel.supp_credit_f
5549 , ren_rel.supp_credit_g
5550 , ren_rel.supp_credit_sg
5551 , ren_rel.renewal_type
5552 , ren_rel.term_flag
5553 , rl.chr_id
5554 , rl.cle_id
5555 , rl.date_signed
5556 , rl.date_cancelled
5557 , ren_rel.annualization_factor
5558 , p.chr_id
5559 , p.cle_id
5560 , p.price_negotiated
5561 , p.price_negotiated_f
5562 , p.price_negotiated_g
5563 , p.price_negotiated_sg
5564 , p.grace_end_date
5565 , p.ubt_amt
5566 , p.ubt_amt_f
5567 , p.ubt_amt_g
5568 , p.ubt_amt_sg
5569 , p.credit_amt
5570 , p.credit_amt_f
5571 , p.credit_amt_g
5572 , p.credit_amt_sg
5573 , p.override_amt
5574 , p.override_amt_f
5575 , p.override_amt_g
5576 , p.override_amt_sg
5577 , p.supp_credit
5578 , p.supp_credit_f
5579 , p.supp_credit_g
5580 , p.supp_credit_sg
5581 , p.end_date
5582 , p.term_flag
5583 , ren_rel.price_negotiated_a
5584 , ren_rel.ubt_amt_a
5585 , ren_rel.credit_amt_a
5586 , ren_rel.override_amt_a
5587 , ren_rel.supp_credit_a
5588 , p.price_negotiated_a
5589 , p.ubt_amt_a
5590 , p.credit_amt_a
5591 , p.override_amt_a
5592 , p.supp_credit_a
5593 , ren_rel.gsd_flag
5594 , ren_rel.falsernwlyn
5595 , ren_rel.effective_term_date
5596 , ren_rel.effective_expire_date
5597 , ren_rel.effective_active_date
5598 , ren_rel.termination_entry_date
5599 , ren_rel.curr_code
5600 , ren_rel.curr_code_f
5601 , ren_rel.hdr_order_number
5602 , ren_rel.hdr_sts_code
5603 , ren_rel.hdr_trn_code
5604 , ren_rel.hdr_renewal_type
5605 , ren_rel.hdr_date_approved
5606 , ren_rel.hdr_date_cancelled
5607 , ren_rel.hdr_date_terminated
5608 , ren_rel.hdr_creation_date
5609 , ren_rel.hdr_last_update_date
5610 , ren_rel.service_item_org_id
5611 , ren_rel.sl_line_number
5612 , ren_rel.sl_sts_code
5613 , ren_rel.sl_trn_code
5614 , ren_rel.sl_renewal_type
5615 , ren_rel.sl_start_date
5616 , ren_rel.sl_end_Date
5617 , ren_rel.sl_date_cancelled
5618 , ren_rel.sl_date_terminated
5619 , ren_rel.sl_creation_date
5620 , ren_rel.sl_last_update_date
5621 , ren_rel.order_number
5622 , ren_rel.unit_price_percent
5623 , ren_rel.unit_price
5624 , ren_rel.unit_price_f
5625 , ren_rel.unit_price_g
5626 , ren_rel.unit_price_sg
5627 , ren_rel.list_price
5628 , ren_rel.list_price_f
5629 , ren_rel.list_price_g
5630 , ren_rel.list_price_sg
5631 , ren_rel.duration_uom
5632 , ren_rel.duration_qty
5633 , ren_rel.cl_last_update_date
5634 , ren_rel.cov_prod_id
5635 , ren_rel.cov_prod_system_id
5636 , ren_rel.line_number
5637 , ren_rel.line_type
5638 , ren_rel.hdr_bill_site_id
5639 , ren_rel.hdr_ship_site_id
5640 , ren_rel.hdr_acct_rule_id
5641 , ren_rel.hdr_grace_end_date
5642 , ren_rel.hdr_date_signed
5643 , ren_rel.hdr_subsequent_renewal_type /* ER#5760744 */
5644 , ren_rel.agreement_type_code /* ER 6062516 */
5645 , ren_rel.agreement_name /* ER 6062516 */
5646 , ren_rel.negotiation_status /* ER#5950128 */
5647 , ren_rel.reminder /* ER#5950128 */
5648 , ren_rel.hdr_term_cancel_source /* ER 6684955 */
5649 , ren_rel.sl_term_cancel_source /* ER 6684955 */
5650 FROM
5651 oki_dbi_cle_b_old ren_rel
5652 , oki_dbi_cle_b_old P
5653 , oki_dbi_cle_b_old rl
5654 WHERE 1=1
5655 AND ren_rel.r_cle_id=rl.cle_id(+)
5656 AND ren_rel.cle_id=P.r_cle_id(+);
5657
5658 p_recs_processed := SQL%ROWCOUNT ;
5659
5660 l_count := p_recs_processed;
5661 rlog('Number of lines inserted into OKI_DBI_CLE_B : ' || to_char(l_count),2);
5662 COMMIT;
5663 rlog('Load of Base Summary Table OKI_DBI_CLE_B Completed - ' ||
5664 fnd_date.date_to_displayDT(SYSDATE),1) ;
5665
5666 EXCEPTION
5667 WHEN OKC_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
5668 RAISE;
5669 WHEN OTHERS THEN
5670 rlog('Error during direct_load: Insert into OKI_DBI_CLE_B Table Failed' , 0);
5671 rlog(sqlerrm ||' '||sqlcode, 0);
5672 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
5673 fnd_message.set_name( application => 'FND'
5674 , name => 'CRM-DEBUG ERROR' ) ;
5675 fnd_message.set_token( token => 'ROUTINE'
5676 , value => 'OKI_DBI_LOAD_CLEB_PVT.direct_load ' ) ;
5677 bis_collection_utilities.put_line(fnd_message.get) ;
5678 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
5679 END direct_load;
5680
5681 /*******************************************************************************
5682 Procedure: worker
5683 Description: This Procedure will be called by spawned request.
5684 Hence value of global variables will be lost.
5685 Here we will decide which stage it should load.
5686 Stage 1) Load Staging table oki_dbi_cle_b_old with contracts info
5687 Stage 2) Load RHS of oki_dbi_cle_b_old with its renewal information
5688 Stage 3) Load LHS of oki_dbi_cle_b_old with its original information
5689 Parameters:
5690 errbuf -Mandatory out parameter containing error message to be passed back to the concurrent manager
5691 retcode-Mandatory out parameter containing the Oracle error number to be passed back to the concurrent manager
5692 p_worker_no- current worker number
5693 p_phase - identifier of the sub-stage
5694 p_no_of_workers- total number of workers requested by the user
5695 *******************************************************************************/
5696
5697 PROCEDURE worker(errbuf OUT NOCOPY VARCHAR2,
5698 retcode OUT NOCOPY VARCHAR2,
5699 p_worker_no IN NUMBER,
5700 p_phase IN NUMBER,
5701 p_no_of_workers IN NUMBER) IS
5702
5703 l_unassigned_cnt NUMBER := 0;
5704 l_failed_cnt NUMBER := 0;
5705 l_wip_cnt NUMBER := 0;
5706 l_completed_cnt NUMBER := 0;
5707 l_total_cnt NUMBER := 0;
5708 l_count NUMBER := 0;
5709 l_recs_processed NUMBER := 0;
5710
5711 BEGIN
5712
5713 rlog('Start of Worker '|| p_worker_no ||' : ' ||fnd_date.date_to_displayDT(SYSDATE),1);
5714 errbuf := NULL;
5715 retcode := 0;
5716 l_count := 0;
5717
5718 SELECT NVL(sum(decode(status,'UNASSIGNED', 1, 0)),0),
5719 NVL(sum(decode(status,'FAILED', 1, 0)),0),
5720 NVL(sum(decode(status,'IN PROCESS', 1, 0)),0),
5721 NVL(sum(decode(status,'COMPLETED',1 , 0)),0),
5722 count(*)
5723 INTO l_unassigned_cnt,
5724 l_failed_cnt,
5725 l_wip_cnt,
5726 l_completed_cnt,
5727 l_total_cnt
5728 FROM OKI_DBI_WORKER_STATUS
5729 WHERE 1=1
5730 AND object_name = 'OKI_DBI_CLE_B_OLD';
5731
5732 IF (l_failed_cnt > 0) THEN
5733 rlog('Another worker have errored out. Stop processing.',2);
5734 ELSIF (l_unassigned_cnt = 0) THEN
5735 rlog('No more jobs left. Terminating.',2);
5736 ELSIF (l_completed_cnt = l_total_cnt) THEN
5737 rlog('All jobs completed, no more job. Terminating',2);
5738 ELSIF (l_unassigned_cnt > 0) THEN
5739
5740 UPDATE OKI_DBI_WORKER_STATUS
5741 SET status = 'IN PROCESS'
5742 ,phase = p_phase
5743 WHERE object_name = 'OKI_DBI_CLE_B_OLD'
5744 AND worker_number = p_worker_no
5745 AND STATUS ='UNASSIGNED';
5746
5747 COMMIT;
5748
5749 DECLARE
5750 BEGIN
5751 IF p_phase = 1 THEN
5752 load_staging(p_worker_no, l_recs_processed);
5753 ELSIF p_phase = 2 THEN
5754 delta_changes ( p_worker_no, p_no_of_workers, l_recs_processed );
5755 ELSIF p_phase = 3 THEN
5756 populate_prev_inc( p_worker_no, p_no_of_workers,1,l_recs_processed );
5757 ELSIF p_phase = 4 THEN
5758 populate_prev_inc( p_worker_no, p_no_of_workers,2,l_recs_processed );
5759 ELSIF p_phase = 5 THEN
5760 update_staging ( p_worker_no, p_no_of_workers, l_recs_processed );
5761 ELSIF p_phase = 6 THEN
5762 update_RHS(p_worker_no, p_no_of_workers, l_recs_processed);
5763 ELSIF p_phase = 7 THEN
5764 update_LHS(p_worker_no, p_no_of_workers,l_recs_processed);
5765 ELSE
5766 RAISE G_CHILD_PROCESS_ISSUE;
5767 END IF;
5768
5769 -- rlog('Total No of Records updated in OKI_DBI_CLE_B_OLD using Worker '||
5770 -- p_worker_no || ' is '|| l_recs_processed,3);
5771 COMMIT;
5772
5773 UPDATE OKI_DBI_WORKER_STATUS
5774 SET status = 'COMPLETED'
5775 WHERE object_name = 'OKI_DBI_CLE_B_OLD'
5776 AND status = 'IN PROCESS'
5777 AND worker_number = p_worker_no;
5778 COMMIT;
5779
5780 EXCEPTION
5781 WHEN OTHERS THEN
5782 retcode := -1;
5783
5784 UPDATE OKI_DBI_WORKER_STATUS
5785 SET status = 'FAILED'
5786 WHERE object_name = 'OKI_DBI_CLE_B_OLD'
5787 AND status = 'IN PROCESS'
5788 AND worker_number = p_worker_no;
5789
5790 COMMIT;
5791 rlog('An Error occurred in worker : '|| p_worker_no ,1);
5792 RAISE G_CHILD_PROCESS_ISSUE;
5793 END;
5794 END IF;
5795
5796 rlog('Finished Worker ' || p_worker_no || ' : ' ||fnd_date.date_to_displayDT(SYSDATE),1);
5797
5798 EXCEPTION
5799 WHEN OTHERS THEN
5800 rlog('Error in procedure worker : Error : ' || SQLERRM,2);
5801 RAISE;
5802 END WORKER;
5803
5804 /*******************************************************************************
5805 Procedure: launch_worker
5806 Description: This Function is used to spawn requests . It returns the spawned
5807 request id
5808 Parameters:
5809 p_worker - current worker number
5810 p_phase - identifier of the sub-stage
5811 p_recs_processed - number or records processed by the current worker
5812 *******************************************************************************/
5813
5814 FUNCTION launch_worker(p_worker_no IN NUMBER,
5815 p_phase IN NUMBER,
5816 p_no_of_workers IN NUMBER) RETURN NUMBER IS
5817
5818 l_request_id NUMBER;
5819
5820 BEGIN
5821
5822 --rlog('Start of the procedure launch_worker for worker ' ||p_worker_no||' at : ' ||
5823 -- fnd_date.date_to_displayDT(SYSDATE),2);
5824
5825 fnd_profile.put('CONC_SINGLE_THREAD','N');
5826
5827 DECLARE
5828 l_oki_schema VARCHAR2(30);
5829 l_status VARCHAR2(30);
5830 l_industry VARCHAR2(30);
5831 BEGIN
5832 IF (FND_INSTALLATION.GET_APP_INFO(
5833 application_short_name => 'OKI'
5834 , status => l_status
5835 , industry => l_industry
5836 , oracle_schema => l_oki_schema)) THEN
5837
5838 l_request_id := FND_REQUEST.SUBMIT_REQUEST(l_oki_schema,
5839 'OKI_DBI_SUB_WORKER',
5840 NULL,
5841 NULL,
5842 FALSE,
5843 p_worker_no,
5844 p_phase,
5845 p_no_of_workers);
5846 END IF;
5847
5848 EXCEPTION
5849 WHEN OTHERS THEN
5850 rlog('Worker exception is ' || SQLERRM,2);
5851 END;
5852
5853
5854 -- if the submission of the request fails , abort the program
5855 IF (l_request_id = 0) THEN
5856 rlog('Error in launching child workers',2);
5857 RAISE G_CHILD_PROCESS_ISSUE;
5858 END IF;
5859 rlog('Request ID of the sub-worker launched : ' || l_request_id,2);
5860 RETURN l_request_id;
5861
5862 EXCEPTION
5863 WHEN OTHERS THEN
5864 rlog('Error in launch_worker : Error : ' || sqlerrm,2);
5865 RAISE;
5866 END LAUNCH_WORKER;
5867
5868
5869 /*******************************************************************************
5870 Procedure: delta_changes
5871 Description: This procedure is used to find out renewal and original
5872 delta changes
5873 Parameters:
5874 p_worker - current worker number
5875 p_no_of_workers - the total number of workers requested by the user
5876 p_recs_processed - number or records processed by the current worker
5877 *******************************************************************************/
5878 PROCEDURE delta_changes(
5879 p_worker IN NUMBER
5880 , p_no_of_workers IN NUMBER
5881 , p_recs_processed OUT NOCOPY NUMBER
5882 )
5883 IS
5884 l_location VARCHAR2(1000);
5885 l_count NUMBER;
5886
5887 BEGIN
5888 l_location := ' populating renewal increments table ' ;
5889 rlog('Populating renewal incremental table OKI_DBI_REN_INC due to ''Delta Changes'': '
5890 ||fnd_date.date_to_displayDT(sysdate), 1) ;
5891
5892 /* Delta Changes */
5893 INSERT INTO OKI_DBI_REN_INC
5894 (
5895 cle_id
5896 , r_cle_id
5897 , worker_number
5898 )
5899 (SELECT /*+ ordered index(ch) use_nl(al) */
5900 al.cle_id
5901 , al.r_cle_id
5902 , MOD(ROWNUM-1,p_no_of_workers)+1 worker_number
5903 FROM oki_dbi_cle_b_old ch
5904 , oki_dbi_cle_b al
5905 WHERE ch.worker_number = p_worker
5906 AND ch.cle_id = al.r_cle_id);
5907
5908 l_count := SQL%ROWCOUNT;
5909 rlog( 'Number of lines inserted into OKI_DBI_REN_INC due to ''Delta Changes'' is '
5910 ||TO_CHAR(l_count),2);
5911 rlog('Load of renewal incremental table OKI_DBI_REN_INC due to ''Delta Changes'' completed: '
5912 ||fnd_date.date_to_displayDT(sysdate), 1) ;
5913 COMMIT;
5914
5915 l_location := ' populating original increments table ' ;
5916 rlog('Populating original incremental table OKI_DBI_PREV_INC due to ''Delta Changes'': '
5917 ||fnd_date.date_to_displayDT(sysdate), 1) ;
5918
5919 /* Delta Changes */
5920 INSERT INTO OKI_DBI_PREV_INC
5921 (
5922 p_cle_id
5923 , cle_id
5924 , worker_number
5925 )
5926 (SELECT s.p_cle_id,
5927 s.cle_id ,
5928 s.worker_number
5929 FROM
5930 (SELECT /*+ ordered index(ch) use_nl(al) */
5931 al.cle_id p_cle_id
5932 , al.r_cle_id cle_id
5933 , MOD(ROWNUM-1,p_no_of_workers)+1 worker_number
5934 FROM oki_dbi_cle_b_old ch
5935 , oki_dbi_cle_b al
5936 WHERE ch.cle_id = al.cle_id
5937 AND al.r_cle_id IS NOT NULL
5938 AND ch.worker_number = p_worker)S);
5939
5940 l_count := SQL%ROWCOUNT;
5941 rlog( 'Number of lines inserted into OKI_DBI_PREV_INC due to ''Delta Changes'' is ' ||TO_CHAR(l_count),2);
5942 rlog('Load of original incremental table OKI_DBI_PREV_INC due to ''Delta Changes'' completed: '
5943 ||fnd_date.date_to_displayDT(sysdate), 1) ;
5944 COMMIT;
5945
5946
5947
5948 EXCEPTION
5949 WHEN OTHERS THEN
5950 rlog('Error in delta_changes: ',0);
5951 rlog(SQLERRM ||' '||SQLCODE, 0) ;
5952 rlog(l_location || ' Failed', 0) ;
5953 fnd_message.set_name( application => 'FND'
5954 , name => 'CRM-DEBUG ERROR' ) ;
5955 fnd_message.set_token(
5956 token => 'ROUTINE'
5957 , value => 'OKI_DBI_LOAD_CLEB_PVT.delta_changes ' ) ;
5958 bis_collection_utilities.put_line(fnd_message.get) ;
5959 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR ;
5960
5961 END;
5962
5963 /*******************************************************************************
5964 Procedure: update_staging
5965 Description: This Function is used to insert covered lines into
5966 oki_dbi_cle_b_old which have been identified for update
5967 in identifying renewal relationship change
5968 Parameters:
5969 p_worker - current worker number
5970 p_no_of_workers - the total number of workers requested by the user
5971 p_recs_processed - number or records processed by the current worker
5972 *******************************************************************************/
5973 PROCEDURE update_staging( p_worker IN NUMBER
5974 , p_no_of_workers IN NUMBER
5975 , p_recs_processed OUT NOCOPY NUMBER
5976 )
5977 IS
5978 l_location VARCHAR2(1000);
5979 l_count NUMBER;
5980
5981 BEGIN
5982
5983
5984 rlog('Populating staging table OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B: '
5985 ||fnd_date.date_to_displayDT(sysdate), 1) ;
5986
5987 INSERT INTO oki_dbi_cle_b_old
5988 ( chr_id
5989 , cle_id
5990 , cle_creation_date
5991 , inv_organization_id
5992 , authoring_org_id
5993 , application_id
5994 , customer_party_id
5995 , salesrep_id
5996 , price_negotiated
5997 , price_negotiated_f
5998 , price_negotiated_g
5999 , contract_number
6000 , contract_number_modifier
6001 , buy_or_sell
6002 , sts_code
6003 , trn_code
6004 , renewal_flag
6005 , date_signed
6006 , date_cancelled
6007 , start_date
6008 , end_date
6009 , date_terminated
6010 , effective_start_date
6011 , effective_end_date
6012 , trx_func_curr_rate
6013 , func_global_curr_rate
6014 , created_by
6015 , last_update_login
6016 , creation_date
6017 , last_updated_by
6018 , last_update_date
6019 , security_group_id
6020 , root_lty_code
6021 , resource_group_id
6022 , resource_id
6023 , sle_id
6024 , service_item_id
6025 , covered_item_id
6026 , quantity
6027 , uom_code
6028 , grace_end_date
6029 , expected_close_date
6030 , win_percent
6031 , price_negotiated_sg
6032 , scs_code
6033 , hstart_date
6034 , hend_date
6035 , func_sglobal_curr_rate
6036 , request_id
6037 , program_login_id
6038 , program_application_id
6039 , program_id
6040 , covered_item_org_id
6041 , ubt_amt
6042 , credit_amt
6043 , credit_amt_f
6044 , credit_amt_g
6045 , ubt_amt_f
6046 , credit_amt_sg
6047 , override_amt
6048 , ubt_amt_g
6049 , ubt_amt_sg
6050 , override_amt_f
6051 , override_amt_g
6052 , override_amt_sg
6053 , supp_credit
6054 , supp_credit_f
6055 , supp_credit_g
6056 , supp_credit_sg
6057 , term_flag
6058 , renewal_type
6059 , annualization_factor
6060 , price_negotiated_a
6061 , ubt_amt_a
6062 , credit_amt_a
6063 , override_amt_a
6064 , supp_credit_a
6065 , worker_number
6066 , gsd_flag
6067 /* Added the following columns for bug 4227245 */
6068 , p_chr_id
6069 , p_cle_id
6070 , p_price_negotiated
6071 , p_price_negotiated_f
6072 , p_price_negotiated_g
6073 , p_price_negotiated_sg
6074 , p_grace_end_date
6075 , p_ubt_amt
6076 , p_ubt_amt_f
6077 , p_ubt_amt_g
6078 , p_ubt_amt_sg
6079 , p_credit_amt
6080 , p_credit_amt_f
6081 , p_credit_amt_g
6082 , p_credit_amt_sg
6083 , p_override_amt
6084 , p_override_amt_f
6085 , p_override_amt_g
6086 , p_override_amt_sg
6087 , p_supp_credit
6088 , p_supp_credit_f
6089 , p_supp_credit_g
6090 , p_supp_credit_sg
6091 , p_end_date
6092 , p_term_flag
6093 , p_price_negotiated_a
6094 , p_ubt_amt_a
6095 , p_credit_amt_a
6096 , p_override_amt_a
6097 , p_supp_credit_a
6098 , r_chr_id
6099 , r_cle_id
6100 , r_date_signed
6101 , r_date_cancelled
6102 , falsernwlyn
6103 /* End of Additions for bug 4227245 */
6104 , effective_active_date
6105 , effective_term_date
6106 , effective_expire_date
6107 , termination_entry_date
6108 , curr_code
6109 , curr_code_f
6110 , hdr_order_number
6111 , hdr_sts_code
6112 , hdr_trn_code
6113 , hdr_renewal_type
6114 , hdr_date_approved
6115 , hdr_date_cancelled
6116 , hdr_date_terminated
6117 , hdr_creation_date
6118 , hdr_last_update_date
6119 , service_item_org_id
6120 , sl_line_number
6121 , sl_sts_code
6122 , sl_trn_code
6123 , sl_renewal_type
6124 , sl_start_date
6125 , sl_end_Date
6126 , sl_date_cancelled
6127 , sl_date_terminated
6128 , sl_creation_date
6129 , sl_last_update_date
6130 , order_number
6131 , unit_price_percent
6132 , unit_price
6133 , unit_price_f
6134 , unit_price_g
6135 , unit_price_sg
6136 , list_price
6137 , list_price_f
6138 , list_price_g
6139 , list_price_sg
6140 , duration_uom
6141 , duration_qty
6142 , cl_last_update_date
6143 , cov_prod_id
6144 , cov_prod_system_id
6145 , line_number
6146 , line_type
6147 , hdr_bill_site_id
6148 , hdr_ship_site_id
6149 , hdr_acct_rule_id
6150 , hdr_grace_end_Date
6151 , hdr_date_Signed
6152 , hdr_subsequent_renewal_type /* Added ER#5760744 */
6153 , agreement_type_code /* ER 6062516 */
6154 , agreement_name /* ER 6062516 */
6155 , negotiation_status /* ER#5950128 */
6156 , reminder /* ER#5950128 */
6157 , HDR_TERM_CANCEL_SOURCE /* of ER 6684955 */
6158 , SL_TERM_CANCEL_SOURCE /* ER 6684955 */
6159 )
6160 (SELECT /*+ ordered index(ren_inc) use_nl(b) cardinality(ren_inc,10) */
6161 b.chr_id
6162 , b.cle_id
6163 , b.cle_creation_date
6164 , b.inv_organization_id
6165 , b.authoring_org_id
6166 , b.application_id
6167 , b.customer_party_id
6168 , b.salesrep_id
6169 , b.price_negotiated
6170 , b.price_negotiated_f
6171 , b.price_negotiated_g
6172 , b.contract_number
6173 , b.contract_number_modifier
6174 , b.buy_or_sell
6175 , b.sts_code
6176 , b.trn_code
6177 , b.renewal_flag
6178 , b.date_signed
6179 , b.date_cancelled
6180 , b.start_date
6181 , b.end_date
6182 , b.date_terminated
6183 , b.effective_start_date
6184 , b.effective_end_date
6185 , b.trx_func_curr_rate
6186 , b.func_global_curr_rate
6187 , b.created_by
6188 , b.last_update_login
6189 , b.creation_date
6190 , b.last_updated_by
6191 , b.last_update_date
6192 , b.security_group_id
6193 , b.root_lty_code
6194 , b.resource_group_id
6195 , b.resource_id
6196 , b.sle_id
6197 , b.service_item_id
6198 , b.covered_item_id
6199 , b.quantity
6200 , b.uom_code
6201 , b.grace_end_date
6202 , b.expected_close_date
6203 , b.win_percent
6204 , b.price_negotiated_sg
6205 , b.scs_code
6206 , b.hstart_date
6207 , b.hend_date
6208 , b.func_sglobal_curr_rate
6209 , b.request_id
6210 , b.program_login_id
6211 , b.program_application_id
6212 , b.program_id
6213 , b.covered_item_org_id
6214 , b.ubt_amt
6215 , b.credit_amt
6216 , b.credit_amt_f
6217 , b.credit_amt_g
6218 , b.ubt_amt_f
6219 , b.credit_amt_sg
6220 , b.override_amt
6221 , b.ubt_amt_g
6222 , b.ubt_amt_sg
6223 , b.override_amt_f
6224 , b.override_amt_g
6225 , b.override_amt_sg
6226 , b.supp_credit
6227 , b.supp_credit_f
6228 , b.supp_credit_g
6229 , b.supp_credit_sg
6230 , b.term_flag
6231 , b.renewal_type
6232 , b.annualization_factor
6233 , b.price_negotiated_a
6234 , b.ubt_amt_a
6235 , b.credit_amt_a
6236 , b.override_amt_a
6237 , b.supp_credit_a
6238 , MOD(ROWNUM-1,p_no_of_workers)+1 worker_number
6239 , b.gsd_flag
6240 /* Added the following columns for bug 4227245 */
6241 , b.p_chr_id
6242 , b.p_cle_id
6243 , b.p_price_negotiated
6244 , b.p_price_negotiated_f
6245 , b.p_price_negotiated_g
6246 , b.p_price_negotiated_sg
6247 , b.p_grace_end_date
6248 , b.p_ubt_amt
6249 , b.p_ubt_amt_f
6250 , b.p_ubt_amt_g
6251 , b.p_ubt_amt_sg
6252 , b.p_credit_amt
6253 , b.p_credit_amt_f
6254 , b.p_credit_amt_g
6255 , b.p_credit_amt_sg
6256 , b.p_override_amt
6257 , b.p_override_amt_f
6258 , b.p_override_amt_g
6259 , b.p_override_amt_sg
6260 , b.p_supp_credit
6261 , b.p_supp_credit_f
6262 , b.p_supp_credit_g
6263 , b.p_supp_credit_sg
6264 , b.p_end_date
6265 , b.p_term_flag
6266 , b.p_price_negotiated_a
6267 , b.p_ubt_amt_a
6268 , b.p_credit_amt_a
6269 , b.p_override_amt_a
6270 , b.p_supp_credit_a
6271 , b.r_chr_id
6272 , b.r_cle_id
6273 , b.r_date_signed
6274 , b.r_date_cancelled
6275 , b.falsernwlyn
6276 /* End of Additions for bug 4227245 */
6277 , b.effective_active_date
6278 , b.effective_term_date
6279 , b.effective_expire_date
6280 , b.termination_entry_date
6281 , b.curr_code
6282 , b.curr_code_f
6283 , b.hdr_order_number
6284 , b.hdr_sts_code
6285 , b.hdr_trn_code
6286 , b.hdr_renewal_type
6287 , b.hdr_date_approved
6288 , b.hdr_date_cancelled
6289 , b.hdr_date_terminated
6290 , b.hdr_creation_date
6291 , b.hdr_last_update_date
6292 , b.service_item_org_id
6293 , b.sl_line_number
6294 , b.sl_sts_code
6295 , b.sl_trn_code
6296 , b.sl_renewal_type
6297 , b.sl_start_date
6298 , b.sl_end_Date
6299 , b.sl_date_cancelled
6300 , b.sl_date_terminated
6301 , b.sl_creation_date
6302 , b.sl_last_update_date
6303 , b.order_number
6304 , b.unit_price_percent
6305 , b.unit_price
6306 , b.unit_price_f
6307 , b.unit_price_g
6308 , b.unit_price_sg
6309 , b.list_price
6310 , b.list_price_f
6311 , b.list_price_g
6312 , b.list_price_sg
6313 , b.duration_uom
6314 , b.duration_qty
6315 , b.cl_last_update_date
6316 , b.cov_prod_id
6317 , b.cov_prod_system_id
6318 , b.line_number
6319 , b.line_type
6320 , b.hdr_bill_site_id
6321 , b.hdr_ship_site_id
6322 , b.hdr_acct_rule_id
6323 , b.hdr_grace_end_Date
6324 , b.hdr_date_Signed
6325 , b.hdr_subsequent_renewal_type /* ER#5760744 */
6326 , b.agreement_type_code /* ER 6062516 */
6327 , b.agreement_name /* ER 6062516 */
6328 , b.negotiation_status /* Added part of ER#5950128 */
6329 , b.reminder /* Added part of ER#5950128 */
6330 , b.HDR_TERM_CANCEL_SOURCE /* ER 6684955 */
6331 , b.SL_TERM_CANCEL_SOURCE /* ER 6684955 */
6332 FROM oki_dbi_cle_del ren_inc ,
6333 oki_dbi_cle_b b
6334 WHERE b.cle_id = ren_inc.cle_id
6335 AND ren_inc.worker_number = p_worker
6336 AND NOT EXISTS
6337 (SELECT NULL
6338 FROM oki_dbi_cle_b_old old
6339 WHERE old.cle_id = b.cle_id)
6340 );
6341
6342 l_count := SQL%ROWCOUNT;
6343 rlog( 'Number of lines inserted into OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B: '
6344 ||TO_CHAR(l_count),2);
6345 rlog('Load of staging table OKI_DBI_CLE_B_OLD from OKI_DBI_CLE_B complete: '
6346 ||fnd_date.date_to_displayDT(sysdate), 1) ;
6347 COMMIT;
6348
6349 p_recs_processed := NVL(l_count,0);
6350
6351 EXCEPTION
6352 WHEN OTHERS THEN
6353 rlog('Error in update_staging : ',0);
6354 rlog(SQLERRM ||' '||SQLCODE, 0) ;
6355 rlog(l_location || ' Failed', 0) ;
6356 fnd_message.set_name( application => 'FND'
6357 , name => 'CRM-DEBUG ERROR' ) ;
6358 fnd_message.set_token(
6359 token => 'ROUTINE'
6360 , value => 'OKI_DBI_LOAD_CLEB_PVT.update_staging ' ) ;
6361 bis_collection_utilities.put_line(fnd_message.get) ;
6362 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR ;
6363 END ;
6364
6365 /*******************************************************************************
6366 Procedure: populate_prev_inc
6367 Description: Find renewal relationship changes for original contract lines.
6368 and update oki_dbi_prev_inc table
6369 Parameters:
6370 p_worker - current worker number
6371 p_no_of_workers - the total number of workers requested by the user
6372 p_stage - identifier of the stage
6373 p_recs_processed - number or records processed by the current worker
6374 *******************************************************************************/
6375 PROCEDURE populate_prev_inc(
6376 p_worker IN NUMBER
6377 , p_no_of_workers IN NUMBER
6378 , p_stage IN NUMBER
6379 , p_recs_processed OUT NOCOPY NUMBER
6380
6381 )
6382 IS
6383 l_location VARCHAR2(1000);
6384 l_count NUMBER;
6385
6386 BEGIN
6387 /* Confirmed from OKS that renewed contracts cannot be deleted).
6388 Hence there is no need of processing deletes here*/
6389
6390 IF( p_stage = 1 ) THEN
6391
6392 rlog('Populating original incremental table OKI_DBI_PREV_INC from OKI_DBI_REN_INC: '
6393 ||fnd_date.date_to_displayDT(sysdate), 1) ;
6394
6395 /* Changes to Operation Lines...Use the information Already found in
6396 the previous merge to get p_cle_id*/
6397 MERGE INTO OKI_DBI_PREV_INC b
6398 USING
6399 (SELECT rel.r_cle_id cle_id
6400 , rel.cle_id p_cle_id
6401 , MOD(ROWNUM-1,p_no_of_workers)+1 worker_number
6402 FROM oki_dbi_ren_inc rel
6403 WHERE rel.worker_number = p_worker
6404 )s
6405 ON (b.cle_id = s.cle_id)
6406 WHEN MATCHED THEN
6407 UPDATE SET
6408 p_cle_id = s.p_cle_id
6409 , worker_number = s.worker_number
6410 WHEN NOT MATCHED THEN
6411 INSERT ( cle_id
6412 , p_cle_id
6413 , worker_number
6414 ) VALUES(
6415 s.cle_id
6416 , s.p_cle_id
6417 , s.worker_number
6418 );
6419
6420 l_count := SQL%ROWCOUNT;
6421
6422 rlog( 'Number of lines inserted into OKI_DBI_PREV_INC from OKI_DBI_REN_INC: '
6423 ||TO_CHAR(l_count),2);
6424
6425 rlog('Load of original incremental table OKI_DBI_PREV_INC from OKI_DBI_REN_INC: '
6426 ||fnd_date.date_to_displayDT(sysdate), 1) ;
6427
6428 COMMIT;
6429
6430 ELSIF ( p_stage = 2 ) THEN
6431
6432 rlog('Populating original incremental table OKI_DBI_PREV_INC for ''Intermediate Cancellations'': '
6433 ||fnd_date.date_to_displayDT(sysdate), 1) ;
6434
6435 /* Check for intermediate Cancellations*/
6436 MERGE INTO OKI_DBI_PREV_INC b
6437 USING
6438 (SELECT cle_id
6439 , p_cle_id
6440 , worker_number
6441 FROM (SELECT /*+ ordered index(del) use_nl(cle) */
6442 cle.cle_id,
6443 NULL p_cle_id,
6444 MOD(ROWNUM-1,p_no_of_workers)+1 worker_number
6445 FROM oki_dbi_prev_inc del,
6446 oki_dbi_cle_b cle
6447 WHERE cle.p_cle_id = del.p_cle_id
6448 AND cle.cle_id <> del.cle_id
6449 AND cle.p_cle_id IS NOT NULL
6450 AND del.worker_number = p_worker) s1
6451 ) s
6452 ON (b.cle_id = s.cle_id)
6453 WHEN MATCHED THEN
6454 UPDATE SET
6455 worker_number = s.worker_number
6456 , p_cle_id = s.p_cle_id
6457 WHEN NOT MATCHED THEN
6458 INSERT(
6459 cle_id
6460 , p_cle_id
6461 , worker_number
6462 ) VALUES (
6463 s.cle_id
6464 , s.p_cle_id
6465 , s.worker_number
6466 );
6467
6468 l_count := SQL%ROWCOUNT;
6469 rlog( 'Number of lines inserted into OKI_DBI_REN_INC due to ''Intermediate Cancellations'' is '
6470 ||TO_CHAR(l_count),2);
6471
6472 rlog('Load of original incremental table OKI_DBI_PREV_INC for ''Intermediate Cancellations'' completed : '
6473 ||fnd_date.date_to_displayDT(sysdate), 1) ;
6474 COMMIT;
6475
6476 END IF;
6477 p_recs_processed := l_count;
6478
6479 EXCEPTION
6480 WHEN OTHERS THEN
6481 rlog('Error in populate_prev_inc: ',0);
6482 rlog(SQLERRM ||' '||SQLCODE, 0) ;
6483 rlog(l_location || ' Failed', 0) ;
6484 fnd_message.set_name( application => 'FND'
6485 , name => 'CRM-DEBUG ERROR' ) ;
6486 fnd_message.set_token(
6487 token => 'ROUTINE'
6488 , value => 'OKI_DBI_LOAD_CLEB_PVT.populate_prev_inc ' ) ;
6489 bis_collection_utilities.put_line(fnd_message.get) ;
6490 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR ;
6491 END;
6492
6493
6494
6495 END OKI_DBI_LOAD_CLEB_PVT;