[Home] [Help]
PACKAGE BODY: APPS.OKI_LOAD_TERR_PVT
Source
1 PACKAGE BODY OKI_LOAD_TERR_PVT
2 /* $Header: OKITERRB.pls 120.3 2005/10/04 17:15:09 ngmishra noship $ */
3 AS
4
5 g_global_Start_date DATE;
6 g_start_date DATE;
7 g_end_date DATE;
8 g_4712_date DATE;
9 g_oki_schema VARCHAR2(30);
10 g_sysdate DATE;
11 g_user_id NUMBER;
12 g_login_id NUMBER;
13 g_status VARCHAR2(100);
14 g_industry VARCHAR2(100);
15 g_user_name VARCHAR2(100);
16 g_table_owner VARCHAR2(100);
17 g_load_type VARCHAR2(100);
18 G_CHILD_PROCESS_ISSUE EXCEPTION;
19
20 /* This API is used to print the log messages.
21 It has two paramters.
22 p_string : IN parameter, the string to be printed
23 p_indent : IN parameter, gives the information about number of
24 indentation */
25
26 PROCEDURE rlog ( p_string IN VARCHAR2
27 , p_indent IN NUMBER )
28 IS
29 l_message varchar2(2000);
30 BEGIN
31 l_message := NULL;
32 FOR i IN 1..p_indent
33 LOOP
34 l_message:=' '||l_message;
35 END LOOP;
36
37 l_message:=l_message||p_string;
38
39 COMMIT;
40 fnd_file.put_line( which => fnd_file.log,
41 buff => l_message);
42 EXCEPTION
43 WHEN OTHERS THEN
44 fnd_file.put_line( which => fnd_file.log,
45 buff => sqlerrm || '' || sqlcode);
46 fnd_message.set_name( application => 'FND'
47 , name => 'CRM-DEBUG ERROR' ) ;
48 fnd_message.set_token( token => 'ROUTINE'
49 , value => 'OKI_LOAD_TERR_PVT.log' ) ;
50 fnd_file.put_line( which => fnd_file.log,
51 buff => fnd_message.get);
52 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
53 END rlog;
54
55 /* This API is used to launch the worker.
56 It has only one parameter.
57 p_worker_no : IN parameter, gives information about no of worker to spawn */
58
59 PROCEDURE launch_worker(p_worker_no IN NUMBER)
60 IS
61 l_request_id NUMBER;
62
63 BEGIN
64
65 fnd_profile.put('CONC_SINGLE_THREAD','N');
66
67 l_request_id := FND_REQUEST.SUBMIT_REQUEST(g_oki_schema,
68 'OKI_TERR_WORKER',
69 NULL,
70 NULL,
71 FALSE,
72 p_worker_no,
73 g_load_type);
74
75 rlog('Request ID of the concurrent request launched : ' || l_request_id,2);
76
77 -- if the submission of the request fails , abort the program
78 IF (l_request_id = 0) THEN
79 rlog('Error in launching child workers',2);
80 RAISE G_CHILD_PROCESS_ISSUE;
81 END IF;
82
83 EXCEPTION
84 WHEN OTHERS THEN
85 rlog('Error in launch_worker : Error : ' || SQLERRM,2);
86 RAISE;
87 END launch_worker;
88
89 /* This API does not take any paramters.
90 It either truncates/deletes unwanted old records from
91 either staging table or Fact table or Worker Status table.
92 */
93 PROCEDURE reset_base_tables
94 IS
95 l_tab_name VARCHAR2(100);
96 l_ind_name VARCHAR2(100);
97 l_sql_string VARCHAR2(1000);
98
99 BEGIN
100
101 IF g_load_type = 'INIT' THEN
102 -- truncate tables
103 l_sql_string := 'TRUNCATE TABLE '|| g_oki_schema ||'.OKI_JTF_TERRITORIES';
104 EXECUTE IMMEDIATE l_sql_string;
105 rlog( 'Truncated Table OKI_JTF_TERRITORIES',2);
106 END IF;
107
108 l_sql_string := 'TRUNCATE TABLE '|| g_oki_schema ||'.OKI_JTF_TERRITORIES_STG';
109 EXECUTE IMMEDIATE l_sql_string;
110 rlog( 'Truncated Table OKI_JTF_TERRITORIES_STG',2);
111
112 DELETE FROM OKI_DBI_WORKER_STATUS
113 WHERE OBJECT_NAME = 'OKI_JTF_TERRITORIES';
114 COMMIT;
115
116 If g_load_type ='INIT' THEN
117 BIS_COLLECTION_UTILITIES.DeleteLogForObject('OKIJTFTERR');
118 rlog( 'Completed resetting base tables and BIS log file ' || fnd_date.date_to_displayDT(sysdate),1);
119 ELSE
120 rlog( 'Completed resetting base tables ' || fnd_date.date_to_displayDT(sysdate),1);
121 END IF;
122
123 EXCEPTION
124 WHEN OTHERS
125 THEN
126 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
127 fnd_message.set_name( application => 'FND'
128 , name => 'CRM-DEBUG ERROR' ) ;
129 fnd_message.set_token(
130 token => 'ROUTINE'
131 , value => 'OKI_LOAD_TERR_PVT.RESET_BASE_TABLES ' ) ;
132 bis_collection_utilities.put_line(fnd_message.get) ;
133 RAISE;
134 END reset_base_tables;
135
136 /* This API calculates and prints the load duration. This is called by both
137 initial ,incremntal load and worker.
138 It has 3 parameters.
139 p_start_date : IN parameter, start date of the load
140 p_end_date : IN parameter, end date of the load
141 p_string : IN parameter, this is to differentiate the call between
142 worker or by main programs. If it is not null, it is called
143 by worker else it is called by main programs*/
144 PROCEDURE find_time_diff (p_start_date IN DATE,
145 p_end_date IN DATE,
146 p_string IN VARCHAR2)
147 IS
148 l_days NUMBER;
149 l_hours NUMBER;
150 l_minutes NUMBER;
151 l_seconds NUMBER;
152 l_date TIMESTAMP;
153 l_str VARCHAR2(1000);
154 l_temp_str VARCHAR2(1000);
155 BEGIN
156 l_date := TO_TIMESTAMP(p_end_date);
157 l_days := EXTRACT (day FROM l_date - p_start_date);
158 l_hours := EXTRACT (hour FROM l_date - p_start_date);
159 l_minutes := EXTRACT (minute FROM l_date - p_start_date);
160 l_seconds := EXTRACT (second FROM l_date - p_start_date);
161
162 IF p_string IS NULL THEN
163 IF g_load_type ='INIT' THEN
164 l_str := 'Initial Load Completed in ';
165 ELSE
166 l_str := 'Incremental Load Completed in ';
167 END IF;
168 ELSE
169 l_str := p_string;
170 END IF;
171
172 l_temp_str := l_str;
173
174 IF ( l_days > 0 ) THEN
175 IF l_days = 1 THEN
176 l_str := l_str || l_days || ' Day ';
177 ELSE
178 l_str := l_str || l_days || ' Days ';
179 END IF;
180 END IF;
181 IF ( l_hours > 0 ) THEN
182 IF l_hours = 1 THEN
183 l_str := l_str || l_hours || ' Hour ';
184 ELSE
185 l_str := l_str || l_hours || ' Hours ';
186 END IF;
187 END IF;
188 IF ( l_minutes > 0 ) THEN
189 IF l_minutes = 1 THEN
190 l_str := l_str || l_minutes || ' Minute ';
191 ELSE
192 l_str := l_str || l_minutes || ' Minutes ';
193 END IF;
194 END IF;
195 IF ( l_seconds <> 0 ) THEN
196 IF l_seconds = 1 THEN
197 l_str := l_str || l_seconds || ' Second ';
198 ELSE
199 l_str := l_str || l_seconds || ' Seconds ';
200 END IF;
201 END IF;
202
203 IF l_str = l_temp_str THEN
204 l_str := l_str || 'less than a second ';
205 END IF;
206
207 IF p_string IS NULL THEN
208 rlog (l_str,0);
209 ELSE
210 rlog (l_str,1);
211 END IF;
212 EXCEPTION
213 WHEN OTHERS THEN
214 rlog('Unable to calculate load running time ', 0);
215 END;
216
217 /* This API is used to spawn the workers.
218 It has two paramters.
219 p_no_of_workers : IN parameter, gives information about
220 no of worker to spawn
221 p_terr_count : IN parameter, gives territory count. Based on
222 this parameter only workers will be distriubted. */
223
224 PROCEDURE spawn_workers( p_no_of_workers IN NUMBER
225 , p_terr_count IN NUMBER)
226 IS
227 l_no_of_workers NUMBER;
228 l_terr_count NUMBER;
229 l_sql_string VARCHAR2(1000);
230
231 BEGIN
232
233 l_no_of_workers := p_no_of_workers;
234 l_terr_count := p_terr_count;
235
236
237 FOR worker_no IN 1 .. l_no_of_workers LOOP
238
239 UPDATE OKI_JTF_TERRITORIES_STG
240 SET worker_number = worker_no
241 WHERE worker_number IS NULL
242 AND ROWNUM <= CEIL(l_terr_count/l_no_of_workers);
243
244 IF ( SQL%ROWCOUNT > 0 ) THEN
245 INSERT INTO OKI_DBI_WORKER_STATUS (
246 object_name
247 , worker_number
248 , status
249 , c_rows
250 )
251 VALUES(
252 'OKI_JTF_TERRITORIES'
253 , worker_no
254 ,'UNASSIGNED'
255 , -1
256 );
257 END IF;
258 COMMIT;
259
260 END LOOP;
261
262 FOR worker_no IN 1 .. l_no_of_workers
263 LOOP
264 launch_worker(worker_no);
265 END LOOP;
266
267 -- To check if the child workers have completed the requests sucessfully.
268
269 DECLARE
270 l_unassigned_cnt NUMBER := 0;
271 l_completed_cnt NUMBER := 0;
272 l_wip_cnt NUMBER := 0;
273 l_failed_cnt NUMBER := 0;
274 l_tot_cnt NUMBER := 0;
275
276 BEGIN
277 LOOP
278 SELECT NVL(sum(decode(status,'UNASSIGNED',1,0)),0),
279 NVL(sum(decode(status,'COMPLETED',1,0)),0),
280 NVL(sum(decode(status,'IN PROCESS',1,0)),0),
281 NVL(sum(decode(status,'FAILED',1,0)),0),
282 count(*)
283 INTO l_unassigned_cnt,
284 l_completed_cnt,
285 l_wip_cnt,
286 l_failed_cnt,
287 l_tot_cnt
288 FROM OKI_DBI_WORKER_STATUS
289 WHERE object_name = 'OKI_JTF_TERRITORIES';
290
291 IF ( l_failed_cnt > 0 ) THEN
292 rlog('One of the child workers Errored Out',2);
293 RAISE G_CHILD_PROCESS_ISSUE;
294 END IF;
295
296 IF ( l_tot_cnt = l_completed_cnt ) THEN
297 rlog('All Workers Finished Successfully at '||fnd_date.date_to_displayDT(sysdate),2);
298 EXIT;
299 END IF;
300 DBMS_LOCK.sleep(5);
301 END LOOP;
302
303 EXCEPTION
304
305 WHEN G_CHILD_PROCESS_ISSUE then
306 IF g_load_type ='INIT' THEN
307 l_sql_string := 'TRUNCATE TABLE '|| g_oki_schema ||'.OKI_JTF_TERRITORIES';
308 EXECUTE IMMEDIATE l_sql_string;
309 END IF;
310 RAISE;
311
312 WHEN OTHERS THEN
313 rlog( 'Error while loading table OKI_JTF_TERRITORIES table : ' || SQLERRM, 2 );
314 RAISE;
315
316 END; -- Monitor child process Ends here.
317
318 EXCEPTION
319 WHEN OTHERS THEN
320 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
321 fnd_message.set_name( application => 'FND'
322 , name => 'CRM-DEBUG ERROR' ) ;
323 fnd_message.set_token(
324 token => 'ROUTINE'
325 , value => 'OKI_LOAD_TERR_PVT.spawn_workers ' ) ;
326 bis_collection_utilities.put_line(fnd_message.get) ;
327 RAISE;
328 END spawn_workers;
329
330 /* This API is used to load the staging table.
331 It has only one paramter.
332 p_terr_count : OUT parameter, gives territory count.*/
333
334 PROCEDURE load_staging ( p_terr_count OUT NOCOPY NUMBER)
335 IS
336 l_tab_name VARCHAR2(1000);
337 BEGIN
338 rlog('Loading of Staging Table Started - ' || fnd_date.date_to_displayDT(sysdate),1);
339
340 IF g_load_type ='INIT' THEN
341
342 INSERT /* + APPEND */ INTO OKI_JTF_TERRITORIES_STG
343 ( authoring_org_id
344 , party_id
345 , party_name
346 , country_code
347 , state_code
348 , record_id
349 )
350 SELECT
351 authoring_org_id
352 , party_id
353 , party_name
354 , country_code
355 , state_code
356 , ROWNUM
357 FROM
358 (
359 SELECT /* + parallel(b) parallel(sts) parallel(c) parallel(p) parallel(v)
360 parallel(h) parallel(ro) use_hash(b,c,p,v,h,ro,sts) */
361 DISTINCT
362 h.authoring_org_id,
363 p.party_id,
364 p.party_Name,
365 c.country country_code,
366 c.region_2 state_code
367 FROM hr_all_organization_units b
368 , hr_locations c
369 , hz_parties p
370 , okc_k_headers_all_b h
371 , okc_k_vers_numbers v
372 , okc_statuses_b sts
373 , okc_k_party_roles_b ro
374 WHERE h.authoring_org_id = b.organization_id
375 AND b.location_id = c.location_id
376 AND p.party_id = ro.object1_id1
377 AND ( sts.ste_code = 'ACTIVE'
378 OR (sts.ste_code = 'EXPIRED' AND h.end_date >= g_sysdate - 120)
379 OR (sts.ste_code = 'ENTERED' AND h.start_date >= g_sysdate - 365)
380 )
381 AND v.last_update_date >= g_start_date
382 AND v.last_update_date+0 <= g_end_date
383 AND h.id = v.chr_id
384 AND COALESCE(h.date_terminated,h.datetime_cancelled,h.end_date,g_4712_date) > g_global_start_date
385 AND h.template_yn = 'N'
386 AND h.application_id = 515
387 AND h.buy_or_sell ='S'
388 AND h.scs_code IN ('SERVICE','WARRANTY')
389 AND ro.dnz_chr_id = h.id
390 AND ro.cle_id IS NULL
391 AND ro.rle_code IN ('CUSTOMER','LICENSEE','BUYER')
392 AND NVL(ro.primary_yn,'Y') = 'Y'
393 );
394 ELSE
395 INSERT /* + APPEND */ INTO OKI_JTF_TERRITORIES_STG
396 ( authoring_org_id
397 , party_id
398 , party_name
399 , country_code
400 , state_code
401 , record_id
402 )
403 SELECT
404 authoring_org_id
405 , party_id
406 , party_name
407 , country_code
408 , state_code
409 , ROWNUM
410 FROM
411 (
412 SELECT
413 DISTINCT
414 h.authoring_org_id,
415 p.party_id,
416 p.party_Name,
417 c.country country_code,
418 c.region_2 state_code
419 FROM hr_all_organization_units b
420 , hr_locations c
421 , hz_parties p
422 , okc_k_headers_all_b h
423 , okc_k_vers_numbers v
424 , okc_statuses_b sts
425 , okc_k_party_roles_b ro
426 WHERE h.authoring_org_id = b.organization_id
427 AND b.location_id = c.location_id
428 AND p.party_id = ro.object1_id1
429 AND ( sts.ste_code = 'ACTIVE'
430 OR (sts.ste_code = 'EXPIRED' AND h.end_date >= g_sysdate - 120)
431 OR (sts.ste_code = 'ENTERED' AND h.start_date >= g_sysdate - 365)
432 )
433 AND v.last_update_date >= g_start_date
434 AND v.last_update_date+0 <= g_end_date
435 AND h.id = v.chr_id
436 AND COALESCE(h.date_terminated,h.datetime_cancelled,h.end_date,g_4712_date) > g_global_start_date
437 AND h.template_yn = 'N'
438 AND h.application_id = 515
439 AND h.buy_or_sell ='S'
440 AND h.scs_code IN ('SERVICE','WARRANTY')
441 AND ro.dnz_chr_id = h.id
442 AND ro.cle_id IS NULL
443 AND ro.rle_code IN ('CUSTOMER','LICENSEE','BUYER')
444 AND NVL(ro.primary_yn,'Y') = 'Y'
445 );
446 END IF;
447 p_terr_count := SQL%ROWCOUNT;
448 COMMIT;
449 rlog('No of records inserted into Staging table - ' || p_terr_count,1);
450
451 l_tab_name := 'OKI_JTF_TERRITORIES_STG';
452 fnd_stats.gather_table_stats( ownname=> g_table_owner
453 , tabname=> l_tab_name
454 , percent=> 10);
455 rlog('Loading of Staging Table Completed - ' || fnd_date.date_to_displayDT(sysdate),1);
456
457 EXCEPTION
458 WHEN OTHERS THEN
459 bis_collection_utilities.put_line(sqlerrm || '' || sqlcode ) ;
460 fnd_message.set_name( application => 'FND'
461 , name => 'CRM-DEBUG ERROR' ) ;
462 fnd_message.set_token(
463 token => 'ROUTINE'
464 , value => 'OKI_LOAD_TERR_PVT.load_staging ' ) ;
465 bis_collection_utilities.put_line(fnd_message.get) ;
466 RAISE;
467 END load_staging;
468
469 /* This API will be called by SubWorker Concurrent Program.
470 This is the Driving Procedure for Workers
471 It has four Parameters
472 errbuf : OUT parameter, Used to store the Error Information if
473 this API fails.
474 retcode : OUT parameter, Used to store the Error Code if
475 this API fails.
476 p_worker_no : IN parameter, gives information about
477 the worker number
478 p_load_type : IN parameter, gives information about load type.
479 possible values are 'INIT','INCR'*/
480
481 PROCEDURE worker( errbuf OUT NOCOPY VARCHAR2,
482 retcode OUT NOCOPY VARCHAR2,
483 p_worker_no IN NUMBER,
484 p_load_type IN VARCHAR2
485 ) IS
486
487 l_unassigned_cnt NUMBER;
488 l_failed_cnt NUMBER;
489 l_wip_cnt NUMBER;
490 l_completed_cnt NUMBER;
491 l_total_cnt NUMBER;
492 l_count NUMBER;
493 l_recs_processed NUMBER;
494 l_start_date DATE;
495 l_string VARCHAR2(1000);
496 BEGIN
497 g_load_type := p_load_type;
498 l_start_date := SYSDATE;
499 l_unassigned_cnt := 0;
500 l_failed_cnt := 0;
501 l_wip_cnt := 0;
502 l_completed_cnt := 0;
503 l_total_cnt := 0;
504 l_count := 0;
505 l_recs_processed := 0;
506 errbuf := NULL;
507 retcode := 0;
508 l_count := 0;
509
510 SELECT NVL(sum(decode(status,'UNASSIGNED', 1, 0)),0),
511 NVL(sum(decode(status,'FAILED', 1, 0)),0),
512 NVL(sum(decode(status,'IN PROCESS', 1, 0)),0),
513 NVL(sum(decode(status,'COMPLETED',1 , 0)),0),
514 count(*)
515 INTO l_unassigned_cnt,
516 l_failed_cnt,
517 l_wip_cnt,
518 l_completed_cnt,
519 l_total_cnt
520 FROM OKI_DBI_WORKER_STATUS
521 WHERE 1=1
522 AND object_name = 'OKI_JTF_TERRITORIES';
523
524 IF (l_failed_cnt > 0) THEN
525 rlog('Another subworker have errored out. Stop processing.',1);
526 ELSIF (l_unassigned_cnt = 0) THEN
527 rlog('No more jobs left. Terminating.',1);
528 ELSIF (l_completed_cnt = l_total_cnt) THEN
529 rlog('All jobs completed, no more job. Terminating',1);
530 ELSIF (l_unassigned_cnt > 0) THEN
531 rlog('Subworker ' || p_worker_no ||' Starts: - ' ||fnd_date.date_to_displayDT(SYSDATE),1);
532
533 UPDATE OKI_DBI_WORKER_STATUS
534 SET status = 'IN PROCESS'
535 WHERE object_name = 'OKI_JTF_TERRITORIES'
536 AND worker_number = p_worker_no
537 AND STATUS ='UNASSIGNED';
538
539 COMMIT;
540 -- Calling the procedure for loading the jtf table!
541
542 load_jtf_terr (p_worker_no, l_count);
543
544 UPDATE OKI_DBI_WORKER_STATUS
545 SET status = 'COMPLETED'
546 , c_rows = l_count
547 WHERE object_name = 'OKI_JTF_TERRITORIES'
548 AND status = 'IN PROCESS'
549 AND worker_number = p_worker_no;
550 COMMIT;
551
552 END IF;
553
554 rlog('Subworker ' || p_worker_no || ' Finishes: - ' ||fnd_date.date_to_displayDT(SYSDATE),1);
555
556 l_string := 'Subworker ' || p_worker_no || ' Completed in ' ;
557
558 find_time_diff( l_start_date
559 , sysdate
560 , l_string
561 );
562 EXCEPTION
563 WHEN OTHERS THEN
564 retcode := 2;
565 rlog('Error in procedure worker : Error : ' || SQLERRM,1);
566 RAISE;
567 END WORKER;
568
569 /* This API will be used in both initial and incremental load.
570 This is the Driving Procedure.
571 It has three Parameters
572 errbuf : OUT parameter, Used to store the Error Information if
573 this API fails.
574 retcode : OUT parameter, Used to store the Error Code if
575 this API fails.
576 p_number_of_workers : IN parameter, gives information about
577 Number of workers to spawn.*/
578 PROCEDURE refresh_jtf_terr ( errbuf OUT NOCOPY VARCHAR2
579 , retcode OUT NOCOPY VARCHAR2
580 , p_number_of_workers IN NUMBER
581 ) IS
582
583 l_errpos NUMBER;
584 l_tab_name VARCHAR2(100);
585 l_count NUMBER;
586 l_setup_ok BOOLEAN;
587 l_string VARCHAR2(1000);
588 BEGIN
589
590 l_setup_ok := BIS_COLLECTION_UTILITIES.setup('OKIJTFTERR');
591 IF (NOT l_setup_ok) THEN
592 errbuf := fnd_message.get;
593 rlog( 'BIS Setup Failure ',0);
594 RAISE_APPLICATION_ERROR (-20000, 'Error in SETUP: ' || errbuf);
595 END IF;
596
597 l_errpos := 1;
598 l_tab_name := 'OKI_JTF_TERRITORIES';
599
600 IF g_load_type ='INIT' THEN
601 l_string := 'INITIAL';
602 ELSE
603 l_string := 'INCREMENTAL';
604 END IF;
605
606 rlog( 'Service Contracts Intelligence - TERRITORY '||l_string ||' LOAD: ' ||
607 fnd_date.date_to_displayDT(SYSDATE),0);
608 rlog( 'Parameter : Start date '|| fnd_date.date_to_displayDT(g_start_date), 1);
609 rlog( 'Parameter : End date '|| fnd_date.date_to_displayDT(g_end_date), 1);
610 rlog( 'Parameter : Number of Workers '|| p_number_of_workers, 1);
611
612
613 l_errpos := 2;
614
615 reset_base_tables;
616
617 l_errpos := 3;
618
619 load_staging(l_count);
620
621 l_errpos := 4;
622 IF l_count > 0 THEN
623 rlog('Launching Sub-Workers to load/update OKI_JTF_TERRITORIES table ' || fnd_date.date_to_displayDT(SYSDATE),1);
624 spawn_workers( p_number_of_workers
625 , l_count);
626 END IF;
627 l_errpos := 5;
628
629 l_errpos := 6;
630 -- analyze table
631 fnd_stats.gather_table_stats( ownname => g_table_owner
632 , tabname => l_tab_name
633 , percent => 10);
634 l_errpos := 7;
635
636 SELECT NVL(SUM(c_rows),0) INTO l_count
637 FROM OKI_DBI_WORKER_STATUS
638 WHERE object_name ='OKI_JTF_TERRITORIES';
639
640 rlog('No of records inserted/updated in OKI_JTF_TERRITORIES table - ' || l_count , 1);
641
642 BIS_COLLECTION_UTILITIES.wrapup( TRUE
643 , l_count
644 , 'OKI TERRITORY ' || l_string || ' LOAD COMPLETED SUCCESSFULLY'
645 , g_start_date
646 , g_end_date
647 );
648
649 rlog('SUCCESS: Load Program Successfully completed ' || fnd_date.date_to_displayDT(SYSDATE),0);
650
651 find_time_diff( g_sysdate
652 , sysdate
653 , NULL);
654
655 l_errpos := 7;
656 retcode :='0';
657
658 EXCEPTION
659 WHEN OTHERS THEN
660 retcode := sqlcode;
661 errbuf := sqlerrm;
662 ROLLBACK;
663
664 retcode := '2';
665 BIS_COLLECTION_UTILITIES.wrapup( FALSE
666 , l_count
667 , errbuf || ': ' || retcode
668 , g_start_date
669 , g_end_date
670 );
671 fnd_message.set_name( application => 'FND'
672 , name => 'CRM-DEBUG ERROR' ) ;
673 fnd_message.set_token( token => 'ROUTINE'
674 , value => 'OKI_LOAD_TERR_PVT.refresh_jtf_terr' ) ;
675 bis_collection_utilities.put_line(fnd_message.get) ;
676
677 RAISE;
678 END refresh_jtf_terr;
679
680 /* This API will be called by Initial Load Concurrent Program.
681 It has three Parameters
682 errbuf : OUT parameter, Used to store the Error Information if
683 this API fails.
684 retcode : OUT parameter, Used to store the Error Code if
685 this API fails.
686 p_worker_number : IN parameter, gives information about
687 Number of workers to spawn.*/
688 PROCEDURE initial_load ( errbuf OUT NOCOPY VARCHAR2
689 , retcode OUT NOCOPY VARCHAR2
690 , p_number_of_workers IN NUMBER)
691 IS
692 BEGIN
693
694 g_load_type :='INIT';
695 g_start_date := g_global_start_date;
696 g_end_date := g_sysdate;
697
698 refresh_jtf_terr( errbuf
699 , retcode
700 , p_number_of_workers
701 );
702 EXCEPTION
703 WHEN OTHERS THEN
704 bis_collection_utilities.put_line(errbuf || '' || retcode ) ;
705 fnd_message.set_name( application => 'FND'
706 , name => 'CRM-DEBUG ERROR' ) ;
707 fnd_message.set_token(
708 token => 'ROUTINE'
709 , value => 'OKI_LOAD_TERR_PVT.initial_load ' ) ;
710 bis_collection_utilities.put_line(fnd_message.get) ;
711 END initial_load;
712
713 /* This API will be called by Incremental Load Concurrent Program.
714 It has three Parameters
715 errbuf : OUT parameter, Used to store the Error Information if
716 this API fails.
717 retcode : OUT parameter, Used to store the Error Code if
718 this API fails.
719 p_worker_number : IN parameter, gives information about
720 Number of workers to spawn. */
721 PROCEDURE incr_load ( errbuf OUT NOCOPY VARCHAR2
722 , retcode OUT NOCOPY VARCHAR2
723 , p_number_of_workers IN NUMBER)
724 IS
725 BEGIN
726
727 g_load_type := 'INCR';
728
729 g_start_date := fnd_date.displaydt_to_date(BIS_COLLECTION_UTILITIES.get_last_refresh_period('OKIJTFTERR'))
730 - 0.004;
731
732 g_end_date := g_sysdate;
733
734 refresh_jtf_terr( errbuf
735 , retcode
736 , p_number_of_workers
737 );
738 EXCEPTION
739 WHEN OTHERS THEN
740 bis_collection_utilities.put_line(errbuf || '' || retcode ) ;
741 fnd_message.set_name( application => 'FND'
742 , name => 'CRM-DEBUG ERROR' ) ;
743 fnd_message.set_token(
744 token => 'ROUTINE'
745 , value => 'OKI_LOAD_TERR_PVT.initial_load ' ) ;
746 bis_collection_utilities.put_line(fnd_message.get) ;
747 RAISE;
748 END incr_load;
749
750 /* This API is used to load Territory fact.
751 This will be called by SubWorkers and not be Main Request.
752 It has two Parameters
753 p_worker_number : IN parameter, gives information about worker_number
754 x_rec_count : OUT parameter, gives no of records updated in
755 territory fact table by this worker */
756 PROCEDURE load_jtf_terr ( p_worker_number IN NUMBER
757 , x_rec_count OUT NOCOPY NUMBER) IS
758
759 l_errpos NUMBER;
760 l_sql_string VARCHAR2(4000);
761 l_counter NUMBER :=0;
762 l_rec_count NUMBER;
763 l_use_type VARCHAR2(30);
764 l_msg_count NUMBER;
765 l_msg_data NUMBER;
766 x_winning_res_id NUMBER;
767 x_winning_ter_id NUMBER;
768 retcode NUMBER;
769 l_sqlcode VARCHAR2(100);
770 l_sqlerrm VARCHAR2(1000);
771 l_return_status VARCHAR2(1);
772 l_gen_bulk_Rec JTF_TERR_ASSIGN_PUB.bulk_trans_rec_type;
773 l_gen_return_Rec JTF_TERR_ASSIGN_PUB.bulk_winners_rec_type;
774
775 l_no_update_refresh EXCEPTION;
776 l_sysdate DATE;
777
778 -- Select Distinct customer and authoring org id combinations from oki sales contracts headers table
779
780 CURSOR get_org_customer_codes (l_worker_number NUMBER) IS
781 select
782 authoring_org_id
783 , party_id
784 , party_name
785 , country_code
786 , state_code
787 , record_id
788 from OKI_JTF_TERRITORIES_STG t
789 where worker_number = l_worker_number
790 ;
791
792 get_org_customer_codes_rec get_org_customer_codes%ROWTYPE;
793
794 BEGIN
795 l_errpos := 1;
796 l_counter := 0;
797 l_sysdate := sysdate;
798
799 FOR get_org_customer_codes_rec IN get_org_customer_codes(p_worker_number)
800 LOOP
801
802 --
803 --For each combination of authoring_org and customer combination populate
804 --JTF_TERR_ASSIGN_PUB.bulk_trans_rec_type plsql table with the list of
805 --party_name, state_code, country_code and party_id
806 --
807
808 l_gen_bulk_rec.trans_object_id.EXTEND;
809 l_gen_bulk_rec.trans_detail_object_id.EXTEND;
810 l_gen_bulk_rec.SQUAL_CHAR01.EXTEND;
811 l_gen_bulk_rec.SQUAL_CHAR04.EXTEND;
812 l_gen_bulk_rec.SQUAL_CHAR07.EXTEND;
813 l_gen_bulk_rec.SQUAL_NUM01.EXTEND;
814
815 l_counter := l_counter + 1;
816
817 l_gen_bulk_rec.trans_object_id(l_counter) := get_org_customer_codes_rec.record_id ;
818 l_gen_bulk_rec.trans_detail_object_id(l_counter) := get_org_customer_codes_rec.authoring_org_id;
819 l_gen_bulk_rec.SQUAL_CHAR01(l_counter) := get_org_customer_codes_rec.party_name;
820 l_gen_bulk_rec.SQUAL_CHAR04(l_counter) := get_org_customer_codes_rec.state_code;
821 l_gen_bulk_rec.SQUAL_CHAR07(l_counter) := get_org_customer_codes_rec.country_code;
822 l_gen_bulk_rec.SQUAL_NUM01(l_counter) := get_org_customer_codes_rec.party_id;
823 l_use_type := 'RESOURCE';
824
825 END LOOP;
826
827 --
828 -- Now, the l_gen_bulk_rec has been initialized with all the customer name, country code, state code
829 -- combinations. We call the JTF API with this.
830 --
831 BEGIN
832 rlog('Calling JTF get_winners - ' || fnd_date.date_to_displayDT(SYSDATE),2);
833
834 JTF_TERR_ASSIGN_PUB.get_winners
835 ( p_api_version_number => 1.0
836 , p_init_msg_list => OKC_API.G_FALSE
837 , p_use_type => l_use_type
838 , p_source_id => -1500
839 , p_trans_id => -1501
840 , p_trans_rec => l_gen_bulk_rec
841 , p_resource_type => FND_API.G_MISS_CHAR
842 , p_role => FND_API.G_MISS_CHAR
843 , p_top_level_terr_id => FND_API.G_MISS_NUM
844 , p_num_winners => FND_API.G_MISS_NUM
845 , x_return_status => l_return_status
846 , x_msg_count => l_msg_count
847 , x_msg_data => l_msg_data
848 , x_winners_rec => l_gen_return_rec
849 );
850
851 rlog ('Get winners :: x_return_status : '|| l_return_status, 2);
852 rlog ('Get Winners :: x_msg_count : '|| l_msg_count, 2);
853 rlog ('Get Winners :: x_msg_data : '|| l_msg_data, 2);
854 rlog ('Returned From JTF get_winners - ' || fnd_date.date_to_displayDT(SYSDATE),2);
855
856 EXCEPTION
857 WHEN OTHERS THEN
858 rlog('Error in JTF_TERR_ASSIGN_PUB call ',1);
859 RAISE;
860 END;
861
862 --FOR i IN l_gen_return_rec.trans_object_id.FIRST .. l_gen_return_rec.trans_object_id.LAST
863 FOR i IN 1 .. l_gen_return_rec.trans_object_id.COUNT
864 LOOP
865 BEGIN
866 UPDATE OKI_JTF_TERRITORIES_STG
867 SET terr_id = l_gen_return_rec.terr_id(i),
868 resource_id = l_gen_return_rec.resource_id(i)
869 WHERE authoring_org_id = l_gen_return_rec.trans_detail_object_id(i)
870 AND record_id = l_gen_return_rec.trans_object_id(i);
871 EXCEPTION
872 WHEN OTHERS THEN
873 NULL;
874 END;
875 END LOOP;
876
877 rlog('Loading of table OKI_JTF_TERRITORIES using subworker '|| p_worker_number ||' started - '
878 || fnd_date.date_to_displayDT(SYSDATE),2);
879
880 IF(l_gen_return_rec.trans_object_id.FIRST IS NULL) THEN
881 rlog('Empty output from jtf function call' , 2);
882 ELSE
883 IF g_load_type ='INIT' THEN
884
885 INSERT INTO oki_jtf_territories
886 (
887 authoring_org_id
888 , customer_party_id
889 , terr_id
890 , resource_id
891 , creation_date
892 , created_by
893 , last_update_date
894 , last_updated_by
895 , last_update_login
896 )
897 SELECT
898 authoring_org_id
899 , party_id
900 , terr_id
901 , resource_id
902 , l_sysdate
903 , g_user_id
904 , l_sysdate
905 , g_user_id
906 , g_login_id
907 FROM OKI_JTF_TERRITORIES_STG
908 WHERE worker_number = p_worker_number
909 AND terr_id IS NOT NULL;
910
911 ELSE
912
913 MERGE INTO oki_jtf_territories b
914 USING
915 ( SELECT authoring_org_id
916 , party_id
917 , terr_id
918 , resource_id
919 FROM OKI_JTF_TERRITORIES_STG
920 WHERE worker_number = p_worker_number
921 AND terr_id IS NOT NULL
922 ) s
923 ON
924 ( b.authoring_org_id = s.authoring_org_id
925 AND b.customer_party_id = s.party_id
926 )
927 WHEN MATCHED THEN UPDATE SET
928 terr_id = s.terr_id
929 , resource_id = s.resource_id
930 , last_update_date = l_sysdate
931 , last_updated_by = g_user_id
932 , last_update_login = g_user_id
933 WHEN NOT MATCHED THEN
934 INSERT
935 (
936 authoring_org_id
937 , customer_party_id
938 , terr_id
939 , resource_id
940 , creation_date
941 , created_by
942 , last_update_date
943 , last_updated_by
944 , last_update_login
945 )
946 VALUES
947 (
948 s.authoring_org_id
949 , s.party_id
950 , s.terr_id
951 , s.resource_id
952 , l_sysdate
953 , g_user_id
954 , l_sysdate
955 , g_user_id
956 , g_login_id
957 );
958 END IF;
959 l_rec_count := SQL%ROWCOUNT;
960 IF l_rec_count >= 0 THEN
961 x_rec_count := NVL(l_rec_count,0);
962 ELSE
963 RAISE l_no_update_refresh;
964 END IF;
965 rlog('No of records inserted/updated in OKI_JTF_TERRITORIES table using subworker '
966 || p_worker_number ||' - ' || x_rec_count,2);
967 rlog('Loading of table OKI_JTF_TERRITORIES using subworker '|| p_worker_number ||' completed - '
968 || fnd_date.date_to_displayDT(SYSDATE),2);
969 END IF;
970
971 COMMIT;
972
973 EXCEPTION
974 WHEN OTHERS THEN
975 retcode := 2;
976
977 UPDATE OKI_DBI_WORKER_STATUS
978 SET status = 'FAILED'
979 WHERE object_name = 'OKI_JTF_TERRITORIES'
980 AND status = 'IN PROCESS'
981 AND worker_number = p_worker_number;
982 COMMIT;
983
984 rlog('Error in procedure load_jtf_worker : Error : ' || SQLERRM,2);
985 RAISE;
986 END load_jtf_terr;
987
988
989 BEGIN
990 g_global_start_date := bis_common_parameters.get_global_start_date;
991 g_4712_date := fnd_conc_date.string_to_date('4712/01/01');
992 g_sysdate := SYSDATE ;
993 g_user_id := NVL(fnd_global.user_id, -1);
994 g_login_id := NVL(fnd_global.login_id, -1);
995 g_user_name := 'OKI';
996 g_table_owner := 'OKI';
997 IF NOT (FND_INSTALLATION.GET_APP_INFO('OKI', g_status, g_industry, g_oki_schema)) THEN
998 fnd_file.put_line( which => fnd_file.log
999 , buff => 'Error while retrieving schema name for product OKI');
1000 RAISE_APPLICATION_ERROR(-20000,'Stack Dump Follows =>', true);
1001 END IF;
1002 END OKI_LOAD_TERR_PVT;