[Home] [Help]
PACKAGE BODY: APPS.IMC_REPORTS_SUMMARY_PKG
Source
1 PACKAGE BODY imc_reports_summary_pkg AS
2 /* $Header: imcrsumb.pls 120.10.12000000.4 2007/10/19 12:12:20 nshinde ship $ */
3 -- IMC reports summary extraction program
4
5 --- declarations
6
7 -- g_proc_name is set to the procedure name in a procedure
8 g_proc_name varchar2(50);
9
10 g_party_exists number := 0;
11 g_select_str varchar2(3000);
12
13 TYPE pregrowth_rec_type IS RECORD(
14 month_name VARCHAR2(80),
15 exist_flag VARCHAR2(1)
16 );
17
18 TYPE pregrowth_tbl_type IS TABLE OF pregrowth_rec_type INDEX BY BINARY_INTEGER;
19
20 PROCEDURE get_counts;
21 PROCEDURE write_log(p_msg varchar2);
22
23 --------------- get_counts -----------------
24
25
26 -- This PROCEDURE will count the total number of customers and
27 -- number of customers FOR each party_type and assigns these values
28 -- to the appropriate global variables
29
30 PROCEDURE get_counts IS
31
32 BEGIN
33
34 g_proc_name := 'get_counts';
35
36 -- Single SELECT for all counts, as suggested by Lester Gutierrez
37
38 SELECT
39 SUM(DECODE(party_type,'PERSON',
40 DECODE(LEAST(creation_date,add_months(sysdate,-23)),
41 add_months(sysdate,-23),count(*),0),
42 0)),
43 SUM(DECODE(party_type,'ORGANIZATION',
44 DECODE(LEAST(creation_date,add_months(sysdate,-23)),
45 add_months(sysdate,-23),count(*),0),
46 0)),
47 SUM(DECODE(party_type,'PARTY_RELATIONSHIP',
48 DECODE(LEAST(creation_date,add_months(sysdate,-23)),
49 add_months(sysdate,-23),count(*),0),
50 0)),
51 SUM(DECODE(LEAST(creation_date,add_months(sysdate,-23)),
52 add_months(sysdate,-23),count(*),0)),
53 SUM(count(*))
54 INTO rp_grth_per_cnt, rp_grth_org_cnt, rp_grth_rel_cnt,
55 rp_grth_total_cnt, rp_total_cnt
56 FROM hz_parties
57 GROUP BY party_type,creation_date;
58
59 -- for total person, organization and relationship count
60 -- the party must be active. Don't include inactive,
61 -- deleted or merged parties.
62 SELECT
63 SUM(DECODE(party_type,'PERSON', count(*),0)),
64 SUM(DECODE(party_type,'ORGANIZATION', count(*),0)),
65 SUM(DECODE(party_type,'PARTY_RELATIONSHIP',count(*),0))
66 INTO rp_per_cnt, rp_org_cnt, rp_rel_cnt
67 FROM hz_parties
68 WHERE status = 'A'
69 GROUP BY party_type;
70
71 g_party_exists := 2;
72
73 EXCEPTION
74 WHEN NO_DATA_FOUND THEN
75 g_party_exists := 1;
76 write_log('No data found in HZ_PARTIES:' || sqlerrm);
77
78 WHEN OTHERS THEN
79 write_log('Error:' || sqlerrm);
80
81 END get_counts;
82
83 PROCEDURE write_log (p_msg varchar2) IS
84
85 -- This procedure logs errors and information messages
86 -- The variable g_log_flag is used as a flag whether to use fnd_file.put_line
87 -- or not.
88 -- If it is set to null, error messages are logged to fnd_file.
89 -- If it set to some value, the message can be printed using dbms_output
90 -- instead of fnd_file.put_line.
91 -- This is used only during developement and testing as fnd_file
92 -- can not be used from the SQL prompt.
93
94 BEGIN
95 IF g_log_output IS NULL THEN
96 fnd_file.put_line
97 (fnd_file.log,substr(p_msg || ': ' || g_proc_name,1,350) ||
98 to_char(sysdate,'dd-mon-yy hh24:mi:ss'));
99 END IF;
100
101 END write_log;
102
103
104 -------- Loads data related to Industry chart and report --------
105 --- Report Name : INDUSTRY ---
106 --- Party Types: ORGANIZATION ---
107 --- Report Types: R and G ---
108
109 PROCEDURE load_industry IS
110
111 l_nd_ind NUMBER;
112
113 BEGIN
114
115 g_proc_name := 'load_industry';
116
117 -- INSERT all the categories for ORGANIZATION
118
119 -- fix bug 3296096, get CUSTOMER_CATEGORY from
120 -- HZ_CODE_ASSIGNMENTS table
121
122 -- fix perf bug 3638775, here are the steps
123 -- Run sql statement twice is much faster than having outer join to HZ_PARTIES
124 -- 1) get all industry with customer_category
125 -- 2) count the total number of parties with customer_category
126 -- 3) deduct the total number of organization by total number calculated at step 2
127 -- the result will be those undefined industry
128
129 -- Step 1
130 INSERT INTO imc_reports_tempinfo(report_name,
131 report_type,
132 category,
133 parent_category,
134 org_cnt,
135 time_stamp)
136 SELECT 'INDUSTRY','R',
137 industry, 'YES' industry_code, sum(org_count), sysdate
138 FROM (SELECT lkp.meaning industry,
139 decode(pty.party_type, 'ORGANIZATION', count(*), 0) org_count
140 FROM hz_parties pty, hz_code_assignments look, ar_lookups lkp
141 WHERE look.class_category = 'CUSTOMER_CATEGORY'
142 AND look.owner_table_name = 'HZ_PARTIES'
143 AND pty.party_id = look.owner_table_id
144 AND pty.party_type = 'ORGANIZATION'
145 AND pty.status = 'A'
146 AND look.class_code = lkp.lookup_code
147 AND sysdate between look.start_date_active and nvl(look.end_date_active,sysdate)
148 AND lkp.lookup_type = 'CUSTOMER_CATEGORY'
149 GROUP BY lkp.meaning, pty.party_type)
150 GROUP BY industry;
151
152 -- Step 2
153 SELECT count(1) INTO l_nd_ind
154 FROM (SELECT 1
155 FROM hz_parties pty, hz_code_assignments look, ar_lookups lkp
156 WHERE look.class_category = 'CUSTOMER_CATEGORY'
157 AND look.owner_table_name = 'HZ_PARTIES'
158 AND pty.party_id = look.owner_table_id
159 AND pty.party_type = 'ORGANIZATION'
160 AND pty.status = 'A'
161 AND look.class_code = lkp.lookup_code
162 AND sysdate between look.start_date_active and nvl(look.end_date_active,sysdate)
163 AND lkp.lookup_type = 'CUSTOMER_CATEGORY'
164 GROUP BY pty.party_id);
165
166 -- Step 3
167 INSERT INTO imc_reports_tempinfo(report_name,
168 report_type,
169 category,
170 parent_category,
171 org_cnt,
172 time_stamp)
173 VALUES
174 ('INDUSTRY','R', rp_msg_undefined, 'YES', rp_org_cnt-l_nd_ind, sysdate);
175
176 SELECT nvl(sum(org_cnt),0)
177 INTO rp_ind_org_cnt
178 FROM imc_reports_tempinfo
179 WHERE report_name = 'INDUSTRY'
180 AND report_type = 'R';
181
182 -- UPDATE percentage for ORGANIZATION by industry
183
184 IF rp_ind_org_cnt > 0 THEN
185
186 UPDATE imc_reports_tempinfo
187 SET org_pct = round((org_cnt/rp_ind_org_cnt)*100,2)
188 WHERE report_name = 'INDUSTRY'
189 AND report_type = 'R';
190
191 END IF;
192
193 /* Bug 3296096, total does not match with the sum of all category
194 by count. So, comment out this total count after talking to PM
195 as this total # does not make a good meaning on the number
196 the total # of organizations are shown on graph
197 -- INSERT 'Total' row for ORGANIZATION by industry
198
199 INSERT INTO imc_reports_tempinfo(report_name,
200 report_type,
201 category,
202 org_cnt,
203 org_pct,
204 time_stamp)
205 SELECT 'INDUSTRY','R',rp_msg_total, nvl(rp_org_cnt,0),'100.00',sysdate
206 FROM dual;
207 */
208
209 -- INSERT 'Top 5' counts for ORGANIZATION by industry
210
211 INSERT INTO imc_reports_tempinfo(report_name,
212 report_type,
213 category,
214 parent_category,
215 org_cnt,
216 org_pct,
217 time_stamp)
218 SELECT 'INDUSTRY','G', category, parent_category,
219 org_cnt, org_pct, sysdate
220 FROM (SELECT category, parent_category, org_cnt, org_pct
221 FROM imc_reports_tempinfo
222 WHERE report_name = 'INDUSTRY'
223 AND report_type = 'R'
224 AND org_cnt IS NOT NULL
225 AND NOT (org_pct = 100 AND parent_category IS NULL)
226 ORDER BY org_cnt DESC)
227 WHERE rownum < 6;
228
229
230 -- INSERT 'All Others' row for ORGANIZATION by industry
231
232 IF rp_org_cnt > 0 THEN
233
234 INSERT INTO imc_reports_tempinfo(report_name,
235 report_type,
236 category,
237 org_cnt,
238 org_pct,
239 time_stamp)
240 SELECT 'INDUSTRY','G',rp_msg_all_others,
241 (rp_ind_org_cnt - sum(imc_tmp.org_cnt)),
242 round(((rp_ind_org_cnt - sum(imc_tmp.org_cnt))/rp_ind_org_cnt) * 100,2),
243 sysdate
244 FROM imc_reports_tempinfo imc_tmp
245 WHERE imc_tmp.report_name = 'INDUSTRY'
246 AND imc_tmp.report_type = 'G';
247
248 END IF;
249
250 COMMIT;
251
252 END load_industry;
253
254
255 -------- Loads data related to Country chart and report --------
256 --- Report Name : COUNTRY ---
257 --- Party Types: ORGANIZATION and PERSON ---
258 --- Report Types: R and G ---
259
260 PROCEDURE load_country IS
261
262 l_nd_org NUMBER;
263 l_nd_per NUMBER;
264
265 BEGIN
266
267 g_proc_name := 'load_country';
268
269 -- INSERT all the countries for ORGANIZATION and PERSON
270
271 -- Fix perf bug 3659367.
272 -- 1) Use FND_TERRITORIES_TL instead of VL
273 -- 2) Count the number of parties which has country set
274 -- 3) By deducting the total number of parties by the total number of parites
275 -- having country information, we will get the number of parties which do
276 -- not have country information
277
278 -- Step 1
279 -- Fix perf bug 4915034, use parallel hint on HZ_PARTIES table
280 INSERT INTO imc_reports_tempinfo(report_name,
281 report_type,
282 category,
283 parent_category,
284 org_cnt,
285 per_cnt,
286 time_stamp)
287 SELECT 'COUNTRY','R',
288 terr.territory_short_name country,
289 terr.territory_code country_code,
290 pty.org_count, pty.per_count, sysdate
291 FROM (SELECT country, sum(org_count) org_count, sum(per_count) per_count
292 FROM (SELECT /*+ parallel(pty) */ pty.country,
293 DECODE(pty.party_type,'ORGANIZATION',count(*),0) org_count,
294 DECODE(pty.party_type,'PERSON',count(*),0) per_count
295 FROM hz_parties pty
296 WHERE pty.party_type IN ('ORGANIZATION','PERSON')
297 AND pty.status = 'A'
298 GROUP BY pty.country, pty.party_type )
299 GROUP BY country ) pty ,
300 fnd_territories_tl terr
301 WHERE pty.country = terr.territory_code
302 AND terr.language = userenv('LANG')
303 ORDER BY country, country_code;
304
305 -- Step 2
306 SELECT sum(org_cnt), sum(per_cnt) INTO l_nd_org, l_nd_per
307 FROM IMC_REPORTS_TEMPINFO
308 WHERE report_name = 'COUNTRY'
309 AND report_type = 'R';
310
311 -- Step 3
312 INSERT INTO imc_reports_tempinfo(report_name,
313 report_type,
314 category,
315 parent_category,
316 org_cnt,
317 per_cnt,
318 time_stamp)
319 VALUES
320 ('COUNTRY','R', rp_msg_undefined, NULL,
321 rp_org_cnt-l_nd_org, rp_per_cnt-l_nd_per, sysdate);
322
323 -- UPDATE percentage for ORGANIZATION and PERSON by country
324
325 IF rp_org_cnt > 0 AND rp_per_cnt > 0 THEN
326
327 UPDATE imc_reports_tempinfo
328 SET org_pct = round((org_cnt/rp_org_cnt)*100,2),
329 per_pct = round((per_cnt/rp_per_cnt)*100,2)
330 WHERE report_name = 'COUNTRY'
331 AND report_type = 'R';
332
333 ELSIF rp_org_cnt > 0 AND rp_per_cnt = 0 THEN
334
335 UPDATE imc_reports_tempinfo
336 SET org_pct = round((org_cnt/rp_org_cnt)*100,2)
337 WHERE report_name = 'COUNTRY'
338 AND report_type = 'R';
339
340 ELSIF rp_org_cnt = 0 AND rp_per_cnt > 0 THEN
341
342 UPDATE imc_reports_tempinfo
343 SET per_pct = round((per_cnt/rp_per_cnt)*100,2)
344 WHERE report_name = 'COUNTRY'
345 AND report_type = 'R';
346
347 END IF;
348
349 -- INSERT 'Total' row for ORGANIZATION and PERSON by country
350
351 INSERT INTO imc_reports_tempinfo(report_name,
352 report_type,
353 category,
354 org_cnt,
355 org_pct,
356 per_cnt,
357 per_pct,
358 time_stamp)
359 SELECT 'COUNTRY','R',rp_msg_total,
360 nvl(rp_org_cnt,0),'100.00',nvl(rp_per_cnt,0),'100.00',sysdate
361 FROM dual;
362
363
364 -- INSERT 'Top 5' counts for ORGANIZATION and PERSON by country
365
366 INSERT INTO imc_reports_tempinfo(report_name,
367 report_type,
368 category,
369 parent_category,
370 org_cnt,
371 org_pct,
372 time_stamp)
373 SELECT 'COUNTRY','G', category, parent_category,
374 org_cnt, org_pct, sysdate
375 FROM (SELECT category, parent_category, org_cnt, org_pct
376 FROM imc_reports_tempinfo
377 WHERE report_name = 'COUNTRY'
378 AND report_type = 'R'
379 AND org_cnt IS NOT NULL
380 AND NOT (org_pct = 100 AND parent_category IS NULL)
381 ORDER BY org_cnt DESC)
382 WHERE rownum < 6;
383
384
385 INSERT INTO imc_reports_tempinfo(report_name,
386 report_type,
387 category,
388 parent_category,
389 per_cnt,
390 per_pct,
391 time_stamp)
392 SELECT 'COUNTRY','G', category, parent_category,
393 per_cnt, per_pct, sysdate
394 FROM (SELECT category, parent_category, per_cnt, per_pct
395 FROM imc_reports_tempinfo
396 WHERE report_name = 'COUNTRY'
397 AND report_type = 'R'
398 AND per_cnt IS NOT NULL
399 AND NOT (per_pct = 100 AND parent_category IS NULL)
400 ORDER BY per_cnt DESC)
401 WHERE rownum < 6;
402
403
404 -- INSERT 'All Others' row for ORGANIZATION and PERSON by country
405
406 IF rp_org_cnt > 0 AND rp_per_cnt > 0 THEN
407
408 INSERT INTO imc_reports_tempinfo(report_name,
409 report_type,
410 category,
411 org_cnt,
412 org_pct,
413 per_cnt,
414 per_pct,
415 time_stamp)
416 SELECT 'COUNTRY','G',rp_msg_all_others,
417 (rp_org_cnt - sum(imc_tmp.org_cnt)),
418 round(((rp_org_cnt - sum(imc_tmp.org_cnt))/rp_org_cnt) * 100,2),
419 (rp_per_cnt - sum(imc_tmp.per_cnt)),
420 round(((rp_per_cnt - sum(imc_tmp.per_cnt))/rp_per_cnt) * 100,2),
421 sysdate
422 FROM imc_reports_tempinfo imc_tmp
423 WHERE imc_tmp.report_name = 'COUNTRY'
424 AND imc_tmp.report_type = 'G';
425
426 ELSIF rp_org_cnt = 0 AND rp_per_cnt > 0 THEN
427
428 INSERT INTO imc_reports_tempinfo(report_name,
429 report_type,
430 category,
431 org_cnt,
432 org_pct,
433 per_cnt,
434 per_pct,
435 time_stamp)
436 SELECT 'COUNTRY','G',rp_msg_all_others,
437 (rp_org_cnt - sum(imc_tmp.org_cnt)), 0,
438 (rp_per_cnt - sum(imc_tmp.per_cnt)),
439 round(((rp_per_cnt - sum(imc_tmp.per_cnt))/rp_per_cnt) * 100,2),
440 sysdate
441 FROM imc_reports_tempinfo imc_tmp
442 WHERE imc_tmp.report_name = 'COUNTRY'
443 AND imc_tmp.report_type = 'G';
444
445 ELSIF rp_org_cnt > 0 AND rp_per_cnt = 0 THEN
446
447 INSERT INTO imc_reports_tempinfo(report_name,
448 report_type,
449 category,
450 org_cnt,
451 org_pct,
452 per_cnt,
453 per_pct,
454 time_stamp)
455 SELECT 'COUNTRY','G',rp_msg_all_others,
456 (rp_org_cnt - sum(imc_tmp.org_cnt)),
457 round(((rp_org_cnt - sum(imc_tmp.org_cnt))/rp_org_cnt) * 100,2),
458 (rp_per_cnt - sum(imc_tmp.per_cnt)), 0,
459 sysdate
460 FROM imc_reports_tempinfo imc_tmp
461 WHERE imc_tmp.report_name = 'COUNTRY'
462 AND imc_tmp.report_type = 'G';
463
464 END IF;
465
466 COMMIT;
467
468 END load_country;
469
470
471 -------- Loads data related to State chart and report --------
472 --- Report Name : STATE ---
473 --- Party Types: ORGANIZATION and PERSON ---
474 --- Report Types: R and G ---
475
476 PROCEDURE load_state IS
477
478 CURSOR state_country IS
479 SELECT parent_category,
480 sum(org_cnt) org_total,
481 sum(per_cnt) per_total
482 FROM imc_reports_tempinfo
483 WHERE report_name = 'STATE'
484 AND report_type = 'R'
485 AND parent_category <> rp_msg_total
486 GROUP BY parent_category;
487
488 BEGIN
489
490 g_proc_name := 'load_state';
491
492 -- INSERT all the states for ORGANIZATION and PERSON
493
494 INSERT INTO imc_reports_tempinfo(report_name,
495 report_type,
496 category,
497 parent_category,
498 org_cnt,
499 per_cnt,
500 time_stamp)
501 SELECT 'STATE','R',
502 state, country, sum(org_count), sum(per_count), sysdate
503 FROM (SELECT nvl(pty.state,rp_msg_undefined) state,
504 pty.country,
505 decode(pty.party_type,'ORGANIZATION',count(*),0) org_count,
506 decode(pty.party_type,'PERSON',count(*),0) per_count
507 FROM hz_parties pty
508 WHERE pty.party_type IN ('ORGANIZATION','PERSON')
509 AND pty.status = 'A'
510 GROUP BY pty.country, pty.state, pty.party_type)
511 GROUP BY country, state;
512
513
514 FOR i in state_country LOOP
515
516 -- UPDATE percentage for ORGANIZATION and PERSON by state
517
518 IF i.org_total > 0 AND i.per_total > 0 THEN
519
520 UPDATE imc_reports_tempinfo
521 SET org_pct = round((org_cnt/i.org_total)*100,2),
522 per_pct = round((per_cnt/i.per_total)*100,2)
523 WHERE report_name = 'STATE'
524 AND report_type = 'R'
525 AND parent_category = i.parent_category;
526
527 ELSIF i.org_total > 0 AND i.per_total = 0 THEN
528
529 UPDATE imc_reports_tempinfo
530 SET org_pct = round((org_cnt/i.org_total)*100,2)
531 WHERE report_name = 'STATE'
532 AND report_type = 'R'
533 AND parent_category = i.parent_category;
534
535 ELSIF i.org_total = 0 AND i.per_total > 0 THEN
536
537 UPDATE imc_reports_tempinfo
538 SET per_pct = round((per_cnt/i.per_total)*100,2)
539 WHERE report_name = 'STATE'
540 AND report_type = 'R'
541 AND parent_category = i.parent_category;
542
543 END IF;
544
545 -- INSERT 'Top 5' counts for ORGANIZATION and PERSON by state
546
547 INSERT INTO imc_reports_tempinfo(report_name,
548 report_type,
549 category,
550 parent_category,
551 org_cnt,
552 org_pct,
553 time_stamp)
554 SELECT 'STATE','G', category, parent_category,
555 org_cnt, org_pct, sysdate
556 FROM (SELECT category, parent_category, org_cnt, org_pct
557 FROM imc_reports_tempinfo
558 WHERE report_name = 'STATE'
559 AND report_type = 'R'
560 AND org_cnt IS NOT NULL
561 AND NOT (org_pct = 100 AND parent_category IS NULL)
562 AND category <> rp_msg_total
563 AND parent_category = i.parent_category
564 ORDER BY org_cnt DESC)
565 WHERE rownum < 6;
566
567
568 INSERT INTO imc_reports_tempinfo(report_name,
569 report_type,
570 category,
571 parent_category,
572 per_cnt,
573 per_pct,
574 time_stamp)
575 SELECT 'STATE','G', category, parent_category,
576 per_cnt, per_pct, sysdate
577 FROM (SELECT category, parent_category, per_cnt, per_pct
578 FROM imc_reports_tempinfo
579 WHERE report_name = 'STATE'
580 AND report_type = 'R'
581 AND category <> rp_msg_total
582 AND per_cnt IS NOT NULL
583 AND NOT (per_pct = 100 AND parent_category IS NULL)
584 AND parent_category = i.parent_category
585 ORDER BY per_cnt DESC)
586 WHERE rownum < 6;
587
588
589 -- INSERT 'Total' row for ORGANIZATION and PERSON by state
590
591 INSERT INTO imc_reports_tempinfo(report_name,
592 report_type,
593 parent_category,
594 category,
595 org_cnt,
596 org_pct,
597 per_cnt,
598 per_pct,
599 time_stamp)
600 SELECT 'STATE','R', i.parent_category, rp_msg_total,
601 nvl(i.org_total,0),'100.00',nvl(i.per_total,0),'100.00',sysdate
602 FROM dual;
603
604
605 -- INSERT 'All Others' row for ORGANIZATION and PERSON by state
606
607 IF i.org_total > 0 AND i.per_total > 0 THEN
608
609 INSERT INTO imc_reports_tempinfo(report_name,
610 report_type,
611 parent_category,
612 category,
613 org_cnt,
614 org_pct,
615 per_cnt,
616 per_pct,
617 time_stamp)
618 SELECT 'STATE','G',i.parent_category,rp_msg_all_others,
619 (i.org_total - sum(imc_tmp.org_cnt)),
620 round(((i.org_total - sum(imc_tmp.org_cnt))/i.org_total) * 100,2),
621 (i.per_total - sum(imc_tmp.per_cnt)),
622 round(((i.per_total - sum(imc_tmp.per_cnt))/i.per_total) * 100,2),
623 sysdate
624 FROM imc_reports_tempinfo imc_tmp
625 WHERE imc_tmp.report_name = 'STATE'
626 AND imc_tmp.report_type = 'G'
627 AND imc_tmp.parent_category = i.parent_category;
628
629 ELSIF i.org_total = 0 AND i.per_total > 0 THEN
630
631 INSERT INTO imc_reports_tempinfo(report_name,
632 report_type,
633 parent_category,
634 category,
635 org_cnt,
636 org_pct,
637 per_cnt,
638 per_pct,
639 time_stamp)
640 SELECT 'STATE','G',i.parent_category,rp_msg_all_others, 0, 0,
641 (i.per_total - sum(imc_tmp.per_cnt)),
642 round(((i.per_total - sum(imc_tmp.per_cnt))/i.per_total) * 100,2),
643 sysdate
644 FROM imc_reports_tempinfo imc_tmp
645 WHERE imc_tmp.report_name = 'STATE'
646 AND imc_tmp.report_type = 'G'
647 AND imc_tmp.parent_category = i.parent_category;
648
649 ELSIF i.org_total > 0 AND i.per_total = 0 THEN
650
651 INSERT INTO imc_reports_tempinfo(report_name,
652 report_type,
653 parent_category,
654 category,
655 org_cnt,
656 org_pct,
657 per_cnt,
658 per_pct,
659 time_stamp)
660 SELECT 'STATE','G',i.parent_category,rp_msg_all_others,
661 (i.org_total - sum(imc_tmp.org_cnt)),
662 round(((i.org_total - sum(imc_tmp.org_cnt))/i.org_total) * 100,2),
663 0, 0, sysdate
664 FROM imc_reports_tempinfo imc_tmp
665 WHERE imc_tmp.report_name = 'STATE'
666 AND imc_tmp.report_type = 'G'
667 AND imc_tmp.parent_category = i.parent_category;
668
669 END IF;
670
671 END LOOP;
672
673 COMMIT;
674
675 EXCEPTION
676 WHEN OTHERS THEN
677 write_log('Error:' || sqlerrm);
678
679 END load_state;
680
681
682 -------- Loads data related to Duplicates chart and report --------
683 --- Report Name : DUPLICATE ---
684 --- Party Types: ORGANIZATION and PERSON ---
685 --- Report Types: R and G ---
686
687 PROCEDURE load_duplicates IS
688
689 BEGIN
690
691 g_proc_name := 'load_duplicates';
692
693 -- INSERT all records for ORGANIZATION and PERSON by duplicate
694 /*
695 -- fix bug 3296241 and 3296206
696 INSERT INTO imc_reports_tempinfo(report_name,
697 report_type,
698 category,
699 parent_category,
700 org_cnt,
701 per_cnt,
702 time_stamp)
703 SELECT 'DUPLICATE', 'R',
704 decode(allcount.dn, 0, rp_msg_no_dupl,
705 1, allcount.dr || ' ' || rp_msg_dupl,
706 allcount.dr || ' ' || rp_msg_dupls) category, allcount.dn,
707 sum(decode(allcount.ptype,'ORGANIZATION',allcount.dc,0)) ocount
708 , sum(decode(allcount.ptype,'PERSON',allcount.dc,0)) pcount
709 , sysdate
710 FROM
711 (SELECT x.ptype, rng.rng_no dn, rng.dupl_rng dr, count(1) dc
712 FROM (SELECT hp.customer_key || hl.address_key key_comb,
713 decode(hp.party_type,'ORGANIZATION',count(*),0) org_count,
714 decode(hp.party_type,'PERSON',count(*),0) per_count,
715 hp.party_type ptype
716 FROM hz_parties hp, hz_locations hl, hz_party_sites hs
717 WHERE hp.party_id = hs.party_id (+)
718 AND hp.party_type in ('ORGANIZATION','PERSON')
719 AND hp.status = 'A'
720 AND hs.identifying_address_flag (+) = 'Y'
721 AND hs.location_id = hl.location_id (+)
722 GROUP BY hp.customer_key || hl.address_key, hp.party_type) x,
723 imc_dupl_range_v rng
724 WHERE ((x.org_count between rng.min and rng.max)
725 OR (x.per_count between rng.min and rng.max))
726 GROUP BY rng.dupl_rng, rng.rng_no, ptype
727 ) allcount
728 GROUP BY allcount.dn, allcount.dr;
729 */
730 -- perf bug fix 3638757
731 INSERT INTO imc_reports_tempinfo(report_name,
732 report_type,
733 category,
734 parent_category,
735 org_cnt,
736 per_cnt,
737 time_stamp)
738 SELECT /*+ parallel(v3) */ 'DUPLICATE', 'R'
739 , decode(val,'0',rp_msg_no_dupl,'1',val||' '||rp_msg_dupl, val||' '||rp_msg_dupls) category
740 , decode(val,'0','0','1','1','2','2','3','3','4-10','4','11-100','5','101-10000','6','10001-9999999999','7') dn
741 , sum(decode(pt, 'ORGANIZATION', tpc, 0)) otpc
742 , sum(decode(pt, 'PERSON', tpc, 0)) ptpc
743 , sysdate
744 FROM
745 ( select /*+ parallel(v2) */
746 val, pt, sum(occurence) occur, sum(totalptycount) tpc
747 from
748 ( select /*+ parallel(v1) */
749 decode(least(col1,4),col1,to_char(col1-1)
750 , decode(least(col1,11),col1 ,'4-10'
751 , decode(least(col1,101),col1 ,'11-100'
752 , decode(least(col1,10001),col1 ,'101-10000'
753 , '10001-9999999999')))) val
754 , pt, count(*) occurence, col1*count(*) totalptycount
755 from
756 ( select /*+ parallel(hp) parallel(hs) parallel(hl) use_hash(hs,hl) */
757 count(*) col1, hp.party_type pt
758 from hz_parties hp, hz_party_sites hs, hz_locations hl
759 where hp.party_type in ('ORGANIZATION','PERSON')
760 and hp.status = 'A'
761 and hp.party_id = hs.party_id(+)
762 and hs.identifying_address_flag(+) = 'Y'
763 and hs.location_id = hl.location_id(+)
764 group by hp.customer_key || hl.address_key, hp.party_type ) v1
765 group by decode(least(col1,4),col1,to_char(col1-1),
766 decode(least(col1,11),col1 ,'4-10',
767 decode(least(col1,101),col1 ,'11-100',
768 decode(least(col1,10001),col1 ,'101-10000',
769 '10001-9999999999')))), pt, col1 ) v2
770 group by val, pt
771 ) v3
772 GROUP BY val;
773
774 SELECT nvl(sum(org_cnt),0), nvl(sum(per_cnt),0)
775 INTO rp_dupl_org_cnt, rp_dupl_per_cnt
776 FROM imc_reports_tempinfo
777 WHERE report_name = 'DUPLICATE'
778 AND report_type = 'R';
779
780 -- INSERT the rest of the ranges from imc_dupl_rang_v that do not
781 -- have any duplicate entries for either ORGANIZATION or PERSON party types
782
783 INSERT INTO imc_reports_tempinfo(report_name,
784 report_type,
785 category,
786 parent_category,
787 org_cnt,
788 per_cnt,
789 time_stamp)
790 SELECT 'DUPLICATE', 'R',
791 decode(rng.rng_no, 0, rp_msg_no_dupl,
792 1, rng.dupl_rng || ' ' || rp_msg_dupl,
793 rng.dupl_rng || ' ' || rp_msg_dupls),
794 rng.rng_no, 0, 0, sysdate
795 FROM imc_dupl_range_v rng
796 WHERE NOT EXISTS (SELECT '1' FROM imc_reports_tempinfo tmp
797 WHERE tmp.report_name = 'DUPLICATE'
798 AND tmp.report_type = 'R'
799 AND tmp.parent_category = rng.rng_no);
800
801
802 -- UPDATE percentage for ORGANIZATION and PERSON by duplicate
803
804 IF rp_dupl_org_cnt > 0 AND rp_dupl_per_cnt > 0 THEN
805
806 UPDATE imc_reports_tempinfo
807 SET org_pct = round((org_cnt/rp_dupl_org_cnt)*100,2),
808 per_pct = round((per_cnt/rp_dupl_per_cnt)*100,2)
809 WHERE report_name = 'DUPLICATE'
810 AND report_type = 'R';
811
812 ELSIF rp_dupl_org_cnt > 0 AND rp_dupl_per_cnt = 0 THEN
813
814 UPDATE imc_reports_tempinfo
815 SET org_pct = round((org_cnt/rp_dupl_org_cnt)*100,2),
816 per_pct = 0
817 WHERE report_name = 'DUPLICATE'
818 AND report_type = 'R';
819
820 ELSIF rp_dupl_org_cnt = 0 AND rp_dupl_per_cnt > 0 THEN
821
822 UPDATE imc_reports_tempinfo
823 SET org_pct = 0,
824 per_pct = round((per_cnt/rp_dupl_per_cnt)*100,2)
825 WHERE report_name = 'DUPLICATE'
826 AND report_type = 'R';
827
828 END IF;
829
830 -- INSERT 'Total' row for ORGANIZATION and PERSON by duplicate
831
832 INSERT INTO imc_reports_tempinfo(report_name,
833 report_type,
834 category,
835 org_cnt,
836 org_pct,
837 per_cnt,
838 per_pct,
839 time_stamp)
840 SELECT 'DUPLICATE','R',rp_msg_total, nvl(rp_dupl_org_cnt,0),'100.00',
841 nvl(rp_dupl_per_cnt,0),'100.00', sysdate
842 FROM dual;
843
844 COMMIT;
845
846 EXCEPTION
847 WHEN OTHERS THEN
848 write_log('Error:' || sqlerrm);
849
850 END load_duplicates;
851
852
853 -------- Loads data related to Country chart and report --------
854 --- Report Name : COUNTRY ---
855 --- Party Types: ORGANIZATION and PERSON ---
856 --- Report Types: R and G ---
857
858 PROCEDURE load_growth IS
859
860 CURSOR get_pregrowth_months IS
861 SELECT category, to_number(parent_category)
862 FROM IMC_REPORTS_TEMPINFO
863 WHERE report_name = 'PRE-GROWTH'
864 AND report_type = 'R'
865 ORDER BY parent_category;
866
867 l_month_name VARCHAR2(80);
868 l_month_no NUMBER;
869 l_pregrowth_tbl pregrowth_tbl_type;
870
871 BEGIN
872
873 g_proc_name := 'load_growth';
874
875 -- INSERT all records ORGANIZATION,PERSON,PARTY_RELATIONSHIP,Total by growth
876
877 /* Fix perf bug 4915034 - don't join with imc_growth_time_v
878 Query IMC_REPORTS_TEMPINFO to find out which month did not write into
879 TEMPINFO table.
880
881 INSERT INTO imc_reports_tempinfo(report_name,
882 report_type,
883 category,
884 parent_category,
885 org_cnt,
886 per_cnt,
887 rel_cnt,
888 total_cnt,
889 time_stamp)
890 SELECT 'PRE-GROWTH', 'R', month, month_no,
891 sum(org_count), sum(per_count),
892 sum(rel_count), sum(tot_count), sysdate
893 FROM (SELECT decode(party_type,'ORGANIZATION',count(*),0) org_count,
894 decode(party_type,'PERSON',count(*),0) per_count,
895 decode(party_type,'PARTY_RELATIONSHIP',count(*),0) rel_count,
896 count(*) tot_count,
897 to_char(creation_date,'Mon-YY') month_name,
898 to_number (to_char(creation_date,'MM')) month_num
899 FROM hz_parties
900 WHERE creation_date >= add_months(sysdate,-23)
901 GROUP BY to_char(creation_date,'Mon-YY'),
902 to_number(to_char(creation_date,'MM')), party_type),
903 imc_growth_time_v gro
904 WHERE gro.month = month_name (+)
905 GROUP BY month_no, month;
906 */
907 INSERT INTO imc_reports_tempinfo(report_name,
908 report_type,
909 category,
910 parent_category,
911 org_cnt,
912 per_cnt,
913 rel_cnt,
914 total_cnt,
915 time_stamp)
916 SELECT 'PRE-GROWTH', 'R', month_name,
917 decode(month_name,
918 to_char(add_months(sysdate, -23), 'Mon-YY'), 1,
919 to_char(add_months(sysdate, -22), 'Mon-YY'), 2,
920 to_char(add_months(sysdate, -21), 'Mon-YY'), 3,
921 to_char(add_months(sysdate, -20), 'Mon-YY'), 4,
922 to_char(add_months(sysdate, -19), 'Mon-YY'), 5,
923 to_char(add_months(sysdate, -18), 'Mon-YY'), 6,
924 to_char(add_months(sysdate, -17), 'Mon-YY'), 7,
925 to_char(add_months(sysdate, -16), 'Mon-YY'), 8,
926 to_char(add_months(sysdate, -15), 'Mon-YY'), 9,
927 to_char(add_months(sysdate, -14), 'Mon-YY'), 10,
928 to_char(add_months(sysdate, -13), 'Mon-YY'), 11,
929 to_char(add_months(sysdate, -12), 'Mon-YY'), 12,
930 to_char(add_months(sysdate, -11), 'Mon-YY'), 13,
931 to_char(add_months(sysdate, -10), 'Mon-YY'), 14,
932 to_char(add_months(sysdate, -9), 'Mon-YY'), 15,
933 to_char(add_months(sysdate, -8), 'Mon-YY'), 16,
934 to_char(add_months(sysdate, -7), 'Mon-YY'), 17,
935 to_char(add_months(sysdate, -6), 'Mon-YY'), 18,
936 to_char(add_months(sysdate, -5), 'Mon-YY'), 19,
937 to_char(add_months(sysdate, -4), 'Mon-YY'), 20,
938 to_char(add_months(sysdate, -3), 'Mon-YY'), 21,
939 to_char(add_months(sysdate, -2), 'Mon-YY'), 22,
940 to_char(add_months(sysdate, -1), 'Mon-YY'), 23,
941 to_char(sysdate, 'Mon-YY'), 24
942 ) month_no,
943 sum(org_count), sum(per_count),
944 sum(rel_count), sum(tot_count), sysdate
945 FROM (SELECT decode(party_type,'ORGANIZATION',count(*),0) org_count,
946 decode(party_type,'PERSON',count(*),0) per_count,
947 decode(party_type,'PARTY_RELATIONSHIP',count(*),0) rel_count,
948 count(*) tot_count,
949 to_char(creation_date,'Mon-YY') month_name,
950 to_number (to_char(creation_date,'MM')) month_num
951 FROM hz_parties
952 WHERE creation_date >= add_months(sysdate,-23)
953 GROUP BY to_char(creation_date,'Mon-YY'),
954 to_number(to_char(creation_date,'MM')), party_type)
955 GROUP BY month_name;
956
957 -- create a table of month_name and month_nos
958 FOR i IN 1..24 LOOP
959 l_pregrowth_tbl(i).month_name := to_char(add_months(sysdate, i-24), 'Mon-YY');
960 l_pregrowth_tbl(i).exist_flag := 'N';
961 END LOOP;
962
963 -- fetch all months and month_nos which have been created into IMC_REPORTS_TEMPINFO
964 OPEN get_pregrowth_months;
965 LOOP
966 FETCH get_pregrowth_months INTO l_month_name, l_month_no;
967 EXIT WHEN get_pregrowth_months%NOTFOUND;
968 IF(l_pregrowth_tbl(l_month_no).month_name = l_month_name) THEN
969 l_pregrowth_tbl(l_month_no).exist_flag := 'Y';
970 END IF;
971 END LOOP;
972 CLOSE get_pregrowth_months;
973
974 -- insert missing months
975 FOR i IN 1..24 LOOP
976 IF(l_pregrowth_tbl(i).exist_flag = 'N') THEN
977 INSERT INTO imc_reports_tempinfo(report_name, report_type, category, parent_category,
978 org_cnt, per_cnt, rel_cnt, total_cnt, time_stamp)
979 VALUES ('PRE-GROWTH', 'R', l_pregrowth_tbl(i).month_name, i, null, null, null, null, sysdate);
980 END IF;
981 END LOOP;
982
983 -- To get a cumulative count, doing a self join and inserting rows
984 -- for GROWTH now, using the PRE-GROWTH rows
985
986 INSERT INTO imc_reports_tempinfo(report_name,
987 report_type,
988 category,
989 parent_category,
990 org_cnt,
991 per_cnt,
992 rel_cnt,
993 total_cnt,
994 time_stamp)
995 SELECT 'GROWTH', 'R', category, parent_category,
996 org_cnt, per_cnt, rel_cnt, total_cnt, sysdate
997 FROM (SELECT a.category,
998 to_number(a.parent_category) parent_category,
999 nvl(sum(d.org_cnt),0) org_cnt,
1000 nvl(sum(d.per_cnt),0) per_cnt,
1001 nvl(sum(d.rel_cnt),0) rel_cnt,
1002 nvl(sum(d.total_cnt),0) total_cnt
1003 FROM imc_reports_tempinfo a, imc_reports_tempinfo d
1004 WHERE a.report_name = 'PRE-GROWTH'
1005 AND d.report_name = 'PRE-GROWTH'
1006 AND to_number(d.parent_category) <= to_number(a.parent_category)
1007 AND UPPER(d.parent_category) = LOWER(d.parent_category)
1008 AND UPPER(a.parent_category) = LOWER(a.parent_category)
1009 GROUP BY a.parent_category,a.category
1010 ORDER BY to_number(a.parent_category)) ;
1011
1012 -- Cumulative rows for Growth report loaded. We can knock off
1013 -- PRE-GROWTH records
1014
1015 DELETE imc_reports_tempinfo
1016 WHERE report_name = 'PRE-GROWTH';
1017
1018 -- UPDATE percentage ORGANIZATION,PERSON,PARTY_RELATIONSHIP,Total by growth
1019
1020 IF rp_grth_org_cnt > 0 AND rp_grth_per_cnt > 0 AND rp_grth_rel_cnt > 0 AND
1021 rp_grth_total_cnt > 0 THEN
1022
1023 UPDATE imc_reports_tempinfo
1024 SET org_pct = round((org_cnt/rp_grth_org_cnt)*100,2),
1025 per_pct = round((per_cnt/rp_grth_per_cnt)*100,2),
1026 rel_pct = round((rel_cnt/rp_grth_rel_cnt)*100,2),
1027 total_pct = round((total_cnt/rp_grth_total_cnt)*100,2)
1028 WHERE report_name = 'GROWTH'
1029 AND report_type = 'R';
1030
1031 ELSIF rp_grth_org_cnt = 0 AND rp_grth_per_cnt > 0 AND rp_grth_rel_cnt > 0 THEN
1032
1033 UPDATE imc_reports_tempinfo
1034 SET org_pct = 0,
1035 per_pct = round((per_cnt/rp_grth_per_cnt)*100,2),
1036 rel_pct = round((rel_cnt/rp_grth_rel_cnt)*100,2),
1037 total_pct = round((total_cnt/rp_grth_total_cnt)*100,2)
1038 WHERE report_name = 'GROWTH'
1039 AND report_type = 'R';
1040
1041 ELSIF rp_grth_org_cnt > 0 AND rp_grth_per_cnt = 0 AND rp_grth_rel_cnt > 0 THEN
1042
1043 UPDATE imc_reports_tempinfo
1044 SET org_pct = round((org_cnt/rp_grth_org_cnt)*100,2),
1045 per_pct = 0,
1046 rel_pct = round((rel_cnt/rp_grth_rel_cnt)*100,2),
1047 total_pct = round((total_cnt/rp_grth_total_cnt)*100,2)
1048 WHERE report_name = 'GROWTH'
1049 AND report_type = 'R';
1050
1051 ELSIF rp_grth_org_cnt > 0 AND rp_grth_per_cnt > 0 AND rp_grth_rel_cnt = 0 THEN
1052
1053 UPDATE imc_reports_tempinfo
1054 SET org_pct = round((org_cnt/rp_grth_org_cnt)*100,2),
1055 per_pct = round((per_cnt/rp_grth_per_cnt)*100,2),
1056 rel_pct = 0,
1057 total_pct = round((total_cnt/rp_grth_total_cnt)*100,2)
1058 WHERE report_name = 'GROWTH'
1059 AND report_type = 'R';
1060
1061 ELSIF rp_grth_org_cnt = 0 AND rp_grth_per_cnt = 0 AND rp_grth_rel_cnt > 0 THEN
1062
1063 UPDATE imc_reports_tempinfo
1064 SET org_pct = 0,
1065 per_pct = 0,
1066 rel_pct = round((rel_cnt/rp_grth_rel_cnt)*100,2),
1067 total_pct = round((total_cnt/rp_grth_total_cnt)*100,2)
1068 WHERE report_name = 'GROWTH'
1069 AND report_type = 'R';
1070
1071 ELSIF rp_grth_org_cnt > 0 AND rp_grth_per_cnt = 0 AND rp_grth_rel_cnt = 0 THEN
1072
1073 UPDATE imc_reports_tempinfo
1074 SET org_pct = round((org_cnt/rp_grth_org_cnt)*100,2),
1075 per_pct = 0,
1076 rel_pct = 0,
1077 total_pct = round((total_cnt/rp_grth_total_cnt)*100,2)
1078 WHERE report_name = 'GROWTH'
1079 AND report_type = 'R';
1080
1081 ELSIF rp_grth_org_cnt = 0 AND rp_grth_per_cnt > 0 AND rp_grth_rel_cnt = 0 THEN
1082
1083 UPDATE imc_reports_tempinfo
1084 SET org_pct = 0,
1085 per_pct = round((per_cnt/rp_grth_per_cnt)*100,2),
1086 rel_pct = 0,
1087 total_pct = round((total_cnt/rp_grth_total_cnt)*100,2)
1088 WHERE report_name = 'GROWTH'
1089 AND report_type = 'R';
1090
1091 END IF;
1092
1093 -- INSERT 'Total' row ORGANIZATION,PERSON,PARTY_RELATIONSHIP,Total by growth
1094
1095 INSERT INTO imc_reports_tempinfo(report_name,
1096 report_type,
1097 category,
1098 org_cnt,
1099 org_pct,
1100 per_cnt,
1101 per_pct,
1102 rel_cnt,
1103 rel_pct,
1104 total_cnt,
1105 total_pct,
1106 time_stamp)
1107 SELECT 'GROWTH','R',rp_msg_total,
1108 nvl(rp_grth_org_cnt,0),'100.00',
1109 nvl(rp_grth_per_cnt,0),'100.00',
1110 nvl(rp_grth_rel_cnt,0),'100.00',
1111 nvl(rp_grth_total_cnt,0),'100.00',
1112 sysdate
1113 FROM dual;
1114
1115 COMMIT;
1116
1117 EXCEPTION
1118 WHEN OTHERS THEN
1119 write_log('Error:' || sqlerrm);
1120
1121 END load_growth;
1122
1123 -- main PROCEDURE that calls the PROCEDUREs FOR each type of report/chart
1124
1125 PROCEDURE extract_main IS
1126
1127 BEGIN
1128
1129 g_proc_name := 'extract_main';
1130
1131 get_counts;
1132
1133 IF g_party_exists = 2 THEN
1134
1135 load_country;
1136 load_growth;
1137 load_duplicates;
1138 load_state;
1139 load_industry;
1140
1141 END IF;
1142
1143 EXCEPTION
1144 WHEN OTHERS THEN
1145 write_log('Error:' || sqlerrm);
1146
1147 END extract_main;
1148
1149 FUNCTION get_party_count(
1150 p_party_type IN VARCHAR2,
1151 p_date IN DATE,
1152 x_return_status IN OUT NOCOPY VARCHAR2)
1153 RETURN NUMBER;
1154
1155 FUNCTION get_enrich_party_count (
1156 p_party_type IN VARCHAR2,
1157 p_date IN DATE,
1158 x_return_status IN OUT NOCOPY VARCHAR2)
1159 RETURN NUMBER;
1160
1161 FUNCTION get_party_clause(
1162 p_table_name IN VARCHAR2,
1163 p_party_type IN VARCHAR2,
1164 p_system_date IN DATE,
1165 x_return_status IN OUT NOCOPY VARCHAR2)
1166 RETURN VARCHAR2;
1167
1168 FUNCTION get_profile_clause(
1169 p_table_name IN VARCHAR2,
1170 p_party_type IN VARCHAR2,
1171 p_system_date IN DATE,
1172 x_return_status IN OUT NOCOPY VARCHAR2)
1173 RETURN VARCHAR2;
1174
1175 FUNCTION get_contactpoint_clause(
1176 p_table_name IN VARCHAR2,
1177 p_party_type IN VARCHAR2,
1178 p_attribute IN VARCHAR2,
1179 p_system_date IN DATE,
1180 x_return_status IN OUT NOCOPY VARCHAR2)
1181 RETURN VARCHAR2;
1182
1183 FUNCTION get_org_contact_clause(
1184 p_table_name IN VARCHAR2,
1185 p_party_type IN VARCHAR2,
1186 p_system_date IN DATE,
1187 x_return_status IN OUT NOCOPY VARCHAR2)
1188 RETURN VARCHAR2;
1189
1190 FUNCTION get_org_contact_role_clause(
1191 p_table_name IN VARCHAR2,
1192 p_party_type IN VARCHAR2,
1193 p_attribute IN VARCHAR2,
1194 p_system_date IN DATE,
1195 x_return_status IN OUT NOCOPY VARCHAR2)
1196 RETURN VARCHAR2;
1197
1198 FUNCTION get_code_assign_clause(
1199 p_table_name IN VARCHAR2,
1200 p_party_type IN VARCHAR2,
1201 p_attribute IN VARCHAR2,
1202 p_system_date IN DATE,
1203 x_return_status IN OUT NOCOPY VARCHAR2)
1204 RETURN VARCHAR2;
1205
1206 PROCEDURE get_compl_count(
1207 x_return_status OUT NOCOPY VARCHAR2,
1208 x_msg_count OUT NOCOPY NUMBER,
1209 x_msg_data OUT NOCOPY VARCHAR2
1210 ) IS
1211
1212 l_report_name VARCHAR2(30);
1213 l_party_type VARCHAR2(240);
1214 l_attribute VARCHAR2(80);
1215 l_select_stmt VARCHAR2(2000);
1216 l_attribute_count NUMBER;
1217 l_attr_code VARCHAR2(30);
1218 l_score NUMBER;
1219 l_total_party NUMBER;
1220 l_table_name VARCHAR2(30);
1221 l_system_date DATE;
1222 l_quarter_start NUMBER;
1223 l_month_start NUMBER;
1224 l_parent_cat VARCHAR2(30);
1225 l_return_status VARCHAR2(30);
1226 l_msg_data VARCHAR2(2000);
1227 l_msg_count NUMBER;
1228 l_dummy VARCHAR2(1);
1229 l_month_exist VARCHAR2(1);
1230 l_quarter_exist VARCHAR2(1);
1231 l_daily_exist VARCHAR2(1);
1232 l_attr_type VARCHAR2(30);
1233
1234 l_org_count NUMBER;
1235 l_per_count NUMBER;
1236 l_cnt_count NUMBER;
1237 l_temp_type VARCHAR2(30);
1238 l_temp_count NUMBER;
1239
1240 -- get all reports and type of reports (i.e. ORGNAIZATION, PERSON or CONTACT)
1241 cursor get_all_reports(l_system_date DATE) is
1242 SELECT rpt.lookup_code, substrb(rs.category,1,30)
1243 FROM imc_lookups rpt, imc_reports_summary rs
1244 WHERE rpt.lookup_type = 'COMPLETENESS_REPORTS'
1245 AND rpt.enabled_flag = 'Y'
1246 AND rpt.lookup_code = rs.parent_category
1247 AND rs.report_name = 'COMPLRPT_STATUS'
1248 AND rs.report_type = 'A';
1249
1250 -- get all attributes and decode the description to table name and column name
1251 -- description like HZ_PARTIES.DUNS_NUMBER
1252 cursor get_all_attributes(l_report_name VARCHAR2, l_system_date DATE, l_type VARCHAR2) is
1253 SELECT substrb(t.description,instr(t.description,'.')+1), substrb(t.description,1,instr(t.description,'.')-1)
1254 , t.lookup_code
1255 FROM imc_lookups r, imc_lookups t, imc_lookups a
1256 WHERE r.lookup_type = l_report_name
1257 AND r.enabled_flag = 'Y'
1258 AND r.lookup_code = t.lookup_code
1259 and t.lookup_type = a.lookup_code
1260 and a.lookup_type = l_type
1261 AND l_system_date BETWEEN r.start_date_active AND nvl(r.end_date_active, l_system_date);
1262
1263 -- get date and check if the date is start of month and quarter
1264 cursor get_system_month_day(l_system_date DATE) is
1265 SELECT decode(to_char(l_system_date,'DD'),'01',1,0),
1266 decode(to_char(l_system_date,'MM'),'01',1,'04',1,'07',1,'10',1,0),
1267 to_char(l_system_date, 'YYYY-MM')
1268 FROM dual;
1269
1270 -- check if there is any completeness reports in IMC reports summary table
1271 cursor is_compl_record_exist is
1272 SELECT 'X'
1273 FROM IMC_REPORTS_SUMMARY a, IMC_LOOKUPS b
1274 WHERE a.report_name = b.lookup_code
1275 AND b.lookup_type = 'COMPLETENESS_REPORTS'
1276 AND a.report_type = 'M'
1277 AND rownum = 1;
1278
1279 -- check if there exist any month record
1280 cursor is_month_record_exist(l_report_name VARCHAR2, l_date DATE) is
1281 SELECT 'X'
1282 FROM IMC_REPORTS_SUMMARY
1283 WHERE report_name = l_report_name
1284 AND report_type = 'M'
1285 AND parent_category = to_char(l_date,'YYYY-MM')
1286 AND rownum = 1;
1287
1288 -- check if there exist any quarter record
1289 cursor is_quarter_record_exist(l_report_name VARCHAR2, l_date DATE) is
1290 SELECT 'X'
1291 FROM IMC_REPORTS_SUMMARY
1292 WHERE report_name = l_report_name
1293 AND report_type = 'Q'
1294 AND parent_category = to_char(l_date,'YYYY-')||'Q'||to_char(l_date,'Q')
1295 AND rownum = 1;
1296
1297 -- check if there exist any daily record
1298 cursor is_daily_record_exist(l_report_name VARCHAR2, l_date DATE) is
1299 SELECT 'X'
1300 FROM IMC_REPORTS_SUMMARY
1301 WHERE report_name = l_report_name
1302 AND report_type = 'D'
1303 AND parent_category = to_char(l_date,'YYYY-MM')
1304 AND rownum = 1;
1305
1306 -- get party count of organization
1307 cursor get_org_count is
1308 SELECT count(1)
1309 FROM HZ_PARTIES p, HZ_ORGANIZATION_PROFILES op
1310 WHERE p.status in ('A','I')
1311 AND p.party_type = 'ORGANIZATION'
1312 AND p.party_id = op.party_id
1313 AND sysdate between op.effective_start_date and nvl(op.effective_end_date, sysdate);
1314
1315 -- get party count of person
1316 cursor get_per_count is
1317 SELECT count(1)
1318 FROM HZ_PARTIES p, HZ_PERSON_PROFILES pp
1319 WHERE p.status in ('A','I')
1320 AND p.party_type = 'PERSON'
1321 AND p.party_id = pp.party_id
1322 AND sysdate between pp.effective_start_date and nvl(pp.effective_end_date, sysdate);
1323
1324 -- get contact count
1325 cursor get_cnt_count is
1326 SELECT count(1)
1327 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r, HZ_PARTIES p
1328 , HZ_CODE_ASSIGNMENTS ca, HZ_RELATIONSHIP_TYPES rt
1329 WHERE oc.party_relationship_id = r.relationship_id
1330 AND r.subject_type = 'PERSON'
1331 AND r.subject_id = p.party_id
1332 AND ca.class_category = 'RELATIONSHIP_TYPE_GROUP'
1333 AND ca.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
1334 AND ca.class_code = 'PARTY_REL_GRP_CONTACTS'
1335 AND rt.relationship_type_id = ca.owner_table_id
1336 AND rt.subject_type = 'PERSON'
1337 AND rt.forward_rel_code = r.relationship_code
1338 AND rt.relationship_type = r.relationship_type
1339 AND p.status in ('A','I');
1340
1341 BEGIN
1342
1343 savepoint get_compl_count_pvt;
1344 FND_MSG_PUB.initialize;
1345
1346 l_return_status := FND_API.G_RET_STS_SUCCESS;
1347 l_system_date := sysdate;
1348
1349 write_log('Start collecting data for completeness report at: '||l_system_date);
1350
1351 -- check if sysdate is first day of month and calendar quarter
1352 OPEN get_system_month_day(l_system_date);
1353 FETCH get_system_month_day INTO l_month_start, l_quarter_start, l_parent_cat;
1354 CLOSE get_system_month_day;
1355
1356 -- check if this is the first run of completeness report
1357 OPEN is_compl_record_exist;
1358 FETCH is_compl_record_exist INTO l_dummy;
1359 CLOSE is_compl_record_exist;
1360
1361 -- get count of organization
1362 OPEN get_org_count;
1363 FETCH get_org_count INTO l_org_count;
1364 CLOSE get_org_count;
1365
1366 -- get count of person
1367 OPEN get_per_count;
1368 FETCH get_per_count INTO l_per_count;
1369 CLOSE get_per_count;
1370
1371 -- get count of contact
1372 OPEN get_cnt_count;
1373 FETCH get_cnt_count INTO l_cnt_count;
1374 CLOSE get_cnt_count;
1375
1376 -- getting all reports that is active
1377 OPEN get_all_reports(l_system_date);
1378 LOOP
1379 FETCH get_all_reports INTO l_report_name, l_party_type;
1380 EXIT WHEN get_all_reports%NOTFOUND;
1381
1382 IF(l_party_type = 'ORGANIZATION') THEN
1383 l_attr_type := 'COMPL_ORG_ATTRIBUTES';
1384 ELSIF(l_party_type = 'PERSON') THEN
1385 l_attr_type := 'COMPL_PER_ATTRIBUTES';
1386 ELSIF(l_party_type = 'CONTACT') THEN
1387 l_attr_type := 'COMPL_CNT_ATTRIBUTES';
1388 END IF;
1389
1390 -- check if current month data exist
1391 OPEN is_month_record_exist(l_report_name, l_system_date);
1392 FETCH is_month_record_exist INTO l_month_exist;
1393 CLOSE is_month_record_exist;
1394
1395 -- check if current quarter data exist
1396 OPEN is_quarter_record_exist(l_report_name, l_system_date);
1397 FETCH is_quarter_record_exist INTO l_quarter_exist;
1398 CLOSE is_quarter_record_exist;
1399
1400 -- get total number of active and inactive parties
1401 /* Fix bug 3638782
1402 count the number of party out of the loop */
1403 --l_total_party := get_party_count(l_party_type, l_system_date, l_return_status);
1404 IF(l_party_type = 'ORGANIZATION') THEN
1405 l_total_party := l_org_count;
1406 ELSIF(l_party_type = 'PERSON') THEN
1407 l_total_party := l_per_count;
1408 ELSIF(l_party_type = 'CONTACT') THEN
1409 l_total_party := l_cnt_count;
1410 END IF;
1411 l_attribute := NULL;
1412 l_attribute_count := 0;
1413
1414 delete_daily_score(l_report_name, l_system_date, l_return_status, l_msg_count, l_msg_data);
1415
1416 -- get all attributes of a report which are used to calculate completeness
1417 OPEN get_all_attributes(l_report_name, l_system_date, l_attr_type);
1418 LOOP
1419 FETCH get_all_attributes INTO l_attribute, l_table_name, l_attr_code;
1420 EXIT WHEN get_all_attributes%NOTFOUND;
1421 -- get completeness score for each attribute of sysdate
1422 insert_daily_score(l_report_name, l_total_party, l_party_type
1423 , l_attribute, l_attr_code, l_table_name
1424 , l_system_date, l_parent_cat
1425 , l_return_status, l_msg_count, l_msg_data);
1426 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
1427 l_attribute_count := l_attribute_count + 1;
1428 END IF;
1429 END LOOP;
1430 CLOSE get_all_attributes;
1431
1432 -- check if at least one daily record successfully created for a report
1433 OPEN is_daily_record_exist(l_report_name, l_system_date);
1434 FETCH is_daily_record_exist INTO l_daily_exist;
1435 CLOSE is_daily_record_exist;
1436 IF(l_daily_exist IS NOT NULL) THEN
1437
1438 -- for first day of a month or no "Month" record
1439 IF (l_dummy IS NULL) THEN
1440 insert_monthly_score(l_report_name, l_total_party, l_attribute_count, l_system_date
1441 , l_return_status, l_msg_count, l_msg_data);
1442 insert_quarterly_score(l_report_name, l_total_party, l_attribute_count, l_system_date
1443 , l_return_status, l_msg_count, l_msg_data);
1444 ELSE
1445 IF (l_month_exist IS NULL) THEN
1446 -- if current month data not exist
1447 insert_monthly_score(l_report_name, l_total_party, l_attribute_count, l_system_date
1448 , l_return_status, l_msg_count, l_msg_data);
1449 IF (l_quarter_exist IS NULL) THEN
1450 insert_quarterly_score(l_report_name, l_total_party, l_attribute_count, l_system_date
1451 , l_return_status, l_msg_count, l_msg_data);
1452 ELSE
1453 update_quarterly_score(l_report_name, l_total_party, l_attribute_count, l_system_date
1454 , l_return_status, l_msg_count, l_msg_data);
1455 END IF;
1456 ELSE
1457 -- if current month and quarter data exist
1458 update_monthly_score(l_report_name, l_total_party, l_attribute_count, l_system_date
1459 , l_return_status, l_msg_count, l_msg_data);
1460 update_quarterly_score(l_report_name, l_total_party, l_attribute_count, l_system_date
1461 , l_return_status, l_msg_count, l_msg_data);
1462 END IF;
1463 END IF;
1464
1465 ELSE
1466 write_log('No daily record exist for completeness report: '||l_report_name);
1467 END IF;
1468 l_month_exist := null;
1469 l_quarter_exist := null;
1470 END LOOP;
1471 CLOSE get_all_reports;
1472
1473 write_log('Finish collecting data for completeness report');
1474
1475 EXCEPTION
1476
1477 WHEN FND_API.G_EXC_ERROR THEN
1478 ROLLBACK TO get_compl_count_pvt;
1479 x_return_status := FND_API.G_RET_STS_ERROR;
1480 FND_MSG_PUB.Count_And_Get(
1481 p_encoded => FND_API.G_FALSE,
1482 p_count => x_msg_count,
1483 p_data => x_msg_data);
1484
1485 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1486 ROLLBACK TO get_compl_count_pvt;
1487 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1488 FND_MSG_PUB.Count_And_Get(
1489 p_encoded => FND_API.G_FALSE,
1490 p_count => x_msg_count,
1491 p_data => x_msg_data);
1492
1493 WHEN OTHERS THEN
1494 ROLLBACK TO get_compl_count_pvt;
1495 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1496 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1497 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1498 FND_MSG_PUB.ADD;
1499 FND_MSG_PUB.Count_And_Get(
1500 p_encoded => FND_API.G_FALSE,
1501 p_count => x_msg_count,
1502 p_data => x_msg_data);
1503
1504 END get_compl_count;
1505
1506 PROCEDURE delete_daily_score (
1507 p_report_name IN VARCHAR2,
1508 p_system_date IN DATE,
1509 x_return_status OUT NOCOPY VARCHAR2,
1510 x_msg_count OUT NOCOPY NUMBER,
1511 x_msg_data OUT NOCOPY VARCHAR2
1512 ) IS
1513 str VARCHAR2(2000);
1514 l_return_status VARCHAR2(30);
1515 BEGIN
1516
1517 savepoint delete_daily_score_pvt;
1518 l_return_status := FND_API.G_RET_STS_SUCCESS;
1519
1520 write_log('Start removing daily record for completeness report: '||p_report_name);
1521
1522 -- remove day record
1523 str := 'delete from imc_reports_summary '||
1524 ' where report_name = '''||p_report_name||''''||
1525 ' and parent_category = to_char(:p_date,''YYYY-MM'')'||
1526 ' and report_type = ''D''';
1527
1528 execute immediate str using p_system_date;
1529
1530 x_return_status := l_return_status;
1531
1532 EXCEPTION
1533
1534 WHEN OTHERS THEN
1535 ROLLBACK TO delete_daily_score_pvt;
1536 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1537 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1538 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1539 FND_MSG_PUB.ADD;
1540 FND_MSG_PUB.Count_And_Get(
1541 p_encoded => FND_API.G_FALSE,
1542 p_count => x_msg_count,
1543 p_data => x_msg_data);
1544
1545 END delete_daily_score;
1546
1547 PROCEDURE insert_daily_score (
1548 p_report_name IN VARCHAR2,
1549 p_total_party IN NUMBER,
1550 p_party_type IN VARCHAR2,
1551 p_attribute IN VARCHAR2,
1552 p_attr_code IN VARCHAR2,
1553 p_table_name IN VARCHAR2,
1554 p_system_date IN DATE,
1555 p_parent_cat IN VARCHAR2,
1556 x_return_status OUT NOCOPY VARCHAR2,
1557 x_msg_count OUT NOCOPY NUMBER,
1558 x_msg_data OUT NOCOPY VARCHAR2
1559 ) IS
1560 table_prefix VARCHAR2(10);
1561 str VARCHAR2(2000);
1562 fromandwhere_str VARCHAR2(4000);
1563 l_return_status VARCHAR2(30);
1564 l_perf_hint VARCHAR2(60); -- Perf Bug 6322629
1565 BEGIN
1566
1567 savepoint insert_daily_score_pvt;
1568 l_return_status := FND_API.G_RET_STS_SUCCESS;
1569 l_perf_hint := '';
1570
1571 write_log('Start inserting daily score for completeness report: '||p_report_name);
1572 write_log('>> p_total_party:'||p_total_party||' p_party_type:'||p_party_type||' p_attribute: '||p_attribute);
1573 write_log('>> p_attr_code: '||p_attr_code||' p_table_name: '||p_table_name||' p_parent_cat: '||p_parent_cat);
1574
1575 IF(p_table_name = 'HZ_PARTIES') THEN
1576 table_prefix := 'pty.';
1577 fromandwhere_str := get_party_clause(p_table_name, p_party_type, p_system_date, x_return_status);
1578 -- Perf Bug 6322629 (Add parallel hint for contact tables as volume of data is too high)
1579 IF (p_party_type = 'CONTACT') THEN
1580 l_perf_hint := ' /*+ PARALLEL(rt) PARALLEL(ca) PARALLEL(pty) PARALLEL(r) PARALLEL(oc) */ ';
1581 ELSE
1582 l_perf_hint := ' /*+ PARALLEL(pty) */ ';
1583 END IF;
1584 ELSIF((p_table_name = 'HZ_ORGANIZATION_PROFILES') OR (p_table_name = 'HZ_PERSON_PROFILES')) THEN
1585 table_prefix := 'prof.';
1586 fromandwhere_str := get_profile_clause(p_table_name, p_party_type, p_system_date, x_return_status);
1587 -- Perf Bug 6322629 (Add parallel hint for profile tables as volume of data is too high)
1588 l_perf_hint := ' /*+ PARALLEL(prof) PARALLEL(pty) */ ';
1589 ELSIF(p_table_name = 'HZ_CONTACT_POINTS') THEN
1590 table_prefix := 'contpt.';
1591 fromandwhere_str := get_contactpoint_clause(p_table_name, p_party_type, p_attribute, p_system_date, x_return_status);
1592 -- Perf Bug 6322629 (Add parallel hint for contact tables as volume of data is too high)
1593 IF (p_party_type = 'CONTACT') THEN
1594 l_perf_hint := ' /*+ PARALLEL(rt) PARALLEL(ca) PARALLEL(r) PARALLEL(oc) PARALLEL(contpt) PARALLEL(pty) */ ';
1595 ELSE
1596 l_perf_hint := ' /*+ PARALLEL(contpt) PARALLEL(pty) */ ';
1597 END IF;
1598 ELSIF(p_table_name = 'HZ_ORG_CONTACTS') THEN
1599 table_prefix := 'orgcnt.';
1600 fromandwhere_str := get_org_contact_clause(p_table_name, p_party_type, p_system_date, x_return_status);
1601 -- Perf Bug 6322629 (Add parallel hint for contact tables as volume of data is too high)
1602 l_perf_hint := ' /*+ PARALLEL(rt) PARALLEL(ca) PARALLEL(pty) PARALLEL(r) PARALLEL(orgcnt) */ ';
1603 ELSIF(p_table_name = 'HZ_ORG_CONTACT_ROLES') THEN
1604 table_prefix := 'ocrole.';
1605 fromandwhere_str := get_org_contact_role_clause(p_table_name, p_party_type, p_attribute, p_system_date, x_return_status);
1606 ELSIF(p_table_name = 'HZ_CODE_ASSIGNMENTS') THEN
1607 table_prefix := 'ca.';
1608 fromandwhere_str := get_code_assign_clause(p_table_name, p_party_type, p_attribute, p_system_date, x_return_status);
1609 END IF;
1610
1611 -- add today record
1612 str := 'insert into imc_reports_summary('||
1613 ' report_name, report_type,'||
1614 ' category, parent_category,'||
1615 ' total_cnt, total_pct,'||
1616 ' time_stamp )'||
1617 ' select '||l_perf_hint||
1618 ''''||p_report_name||''''||','||
1619 '''D'''||','||
1620 ''''||p_attr_code||''''||','||
1621 ''''||p_parent_cat||''''||','||
1622 'nvl(sum(decode('||table_prefix||p_attribute||', NULL, 0, 1)),0),'||
1623 p_total_party ||','||
1624 ':p_date '||fromandwhere_str;
1625
1626 write_log('>> sql string: '||str);
1627
1628 IF((p_table_name = 'HZ_ORGANIZATION_PROFILES') OR (p_table_name = 'HZ_PERSON_PROFILES') OR (p_table_name = 'HZ_CODE_ASSIGNMENTS')) THEN
1629 execute immediate str using p_system_date, p_system_date, p_system_date;
1630 ELSE
1631 execute immediate str using p_system_date;
1632 END IF;
1633
1634 x_return_status := l_return_status;
1635
1636 EXCEPTION
1637
1638 WHEN OTHERS THEN
1639 ROLLBACK TO insert_daily_score_pvt;
1640 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1641 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1642 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1643 FND_MSG_PUB.ADD;
1644 FND_MSG_PUB.Count_And_Get(
1645 p_encoded => FND_API.G_FALSE,
1646 p_count => x_msg_count,
1647 p_data => x_msg_data);
1648 write_log('Error: '||sqlerrm);
1649
1650 END insert_daily_score;
1651
1652 PROCEDURE insert_monthly_score (
1653 p_report_name IN VARCHAR2,
1654 p_total_party IN NUMBER,
1655 p_total_attribute IN NUMBER,
1656 p_system_date IN DATE,
1657 x_return_status OUT NOCOPY VARCHAR2,
1658 x_msg_count OUT NOCOPY NUMBER,
1659 x_msg_data OUT NOCOPY VARCHAR2
1660 ) IS
1661
1662 l_return_status VARCHAR2(30);
1663
1664 BEGIN
1665
1666 savepoint insert_monthly_score_pvt;
1667 l_return_status := FND_API.G_RET_STS_SUCCESS;
1668
1669 write_log('Start inserting monthly score for completeness report: '||p_report_name);
1670 write_log('>> p_total_party:'||p_total_party||' p_total_attribute: '||p_total_attribute);
1671
1672 insert into IMC_REPORTS_SUMMARY (
1673 report_name,
1674 report_type,
1675 category,
1676 parent_category,
1677 org_cnt,
1678 total_cnt,
1679 total_pct,
1680 time_stamp
1681 ) select
1682 p_report_name,
1683 'M',
1684 NULL,
1685 to_char(p_system_date, 'YYYY-MM'),
1686 p_total_attribute,
1687 (sum(total_cnt)),
1688 (p_total_party*p_total_attribute),
1689 -- (sum(total_cnt)/(p_total_attribute*p_total_party))*100,
1690 -- p_total_party,
1691 p_system_date
1692 from IMC_REPORTS_SUMMARY
1693 where report_name = p_report_name
1694 and report_type = 'D'
1695 and parent_category = to_char(p_system_date,'YYYY-MM');
1696
1697 x_return_status := l_return_status;
1698
1699 EXCEPTION
1700
1701 WHEN OTHERS THEN
1702 ROLLBACK TO insert_monthly_score_pvt;
1703 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1704 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1705 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1706 FND_MSG_PUB.ADD;
1707 FND_MSG_PUB.Count_And_Get(
1708 p_encoded => FND_API.G_FALSE,
1709 p_count => x_msg_count,
1710 p_data => x_msg_data);
1711 write_log('Error: '||sqlerrm);
1712
1713 END insert_monthly_score;
1714
1715 PROCEDURE update_monthly_score (
1716 p_report_name IN VARCHAR2,
1717 p_total_party IN NUMBER,
1718 p_total_attribute IN NUMBER,
1719 p_system_date IN DATE,
1720 x_return_status OUT NOCOPY VARCHAR2,
1721 x_msg_count OUT NOCOPY NUMBER,
1722 x_msg_data OUT NOCOPY VARCHAR2
1723 ) IS
1724
1725 l_return_status VARCHAR2(30);
1726
1727 BEGIN
1728
1729 savepoint update_monthly_score_pvt;
1730 l_return_status := FND_API.G_RET_STS_SUCCESS;
1731
1732 write_log('Start updating monthly score for completeness report: '||p_report_name);
1733 write_log('>> p_total_party:'||p_total_party||' p_total_attribute: '||p_total_attribute);
1734
1735 update IMC_REPORTS_SUMMARY
1736 set total_cnt =
1737 (select (sum(total_cnt))
1738 from IMC_REPORTS_SUMMARY
1739 where report_name = p_report_name
1740 and parent_category = to_char(p_system_date,'YYYY-MM')
1741 and report_type = 'D'),
1742 total_pct = (p_total_party*p_total_attribute),
1743 org_cnt = p_total_attribute,
1744 time_stamp = p_system_date
1745 where report_name = p_report_name
1746 and report_type = 'M'
1747 and parent_category = to_char(p_system_date,'YYYY-MM');
1748
1749 x_return_status := l_return_status;
1750
1751 EXCEPTION
1752
1753 WHEN OTHERS THEN
1754 ROLLBACK TO update_monthly_score_pvt;
1755 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1756 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1757 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1758 FND_MSG_PUB.ADD;
1759 FND_MSG_PUB.Count_And_Get(
1760 p_encoded => FND_API.G_FALSE,
1761 p_count => x_msg_count,
1762 p_data => x_msg_data);
1763 write_log('Error: '||sqlerrm);
1764
1765 END update_monthly_score;
1766
1767 PROCEDURE insert_quarterly_score (
1768 p_report_name IN VARCHAR2,
1769 p_total_party IN NUMBER,
1770 p_total_attribute IN NUMBER,
1771 p_system_date IN DATE,
1772 x_return_status OUT NOCOPY VARCHAR2,
1773 x_msg_count OUT NOCOPY NUMBER,
1774 x_msg_data OUT NOCOPY VARCHAR2
1775 ) IS
1776
1777 l_return_status VARCHAR2(30);
1778
1779 BEGIN
1780
1781 savepoint insert_quarterly_score_pvt;
1782 l_return_status := FND_API.G_RET_STS_SUCCESS;
1783
1784 write_log('Start inserting quarterly score for completeness report: '||p_report_name);
1785 write_log('>> p_total_party:'||p_total_party||' p_total_attribute: '||p_total_attribute);
1786
1787 insert into IMC_REPORTS_SUMMARY (
1788 report_name,
1789 report_type,
1790 category,
1791 parent_category,
1792 org_cnt,
1793 total_cnt,
1794 total_pct,
1795 time_stamp
1796 ) select
1797 p_report_name,
1798 'Q',
1799 NULL,
1800 to_char(p_system_date, 'YYYY-')||'Q'||to_char(p_system_date,'Q'),
1801 org_cnt,
1802 total_cnt,
1803 total_pct,
1804 time_stamp
1805 from IMC_REPORTS_SUMMARY
1806 where report_name = p_report_name
1807 and report_type = 'M'
1808 and parent_category = to_char(p_system_date, 'YYYY-MM');
1809
1810 x_return_status := l_return_status;
1811
1812 EXCEPTION
1813
1814 WHEN OTHERS THEN
1815 ROLLBACK TO insert_quarterly_score_pvt;
1816 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1817 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1818 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1819 FND_MSG_PUB.ADD;
1820 FND_MSG_PUB.Count_And_Get(
1821 p_encoded => FND_API.G_FALSE,
1822 p_count => x_msg_count,
1823 p_data => x_msg_data);
1824 write_log('Error: '||sqlerrm);
1825
1826 END insert_quarterly_score;
1827
1828 PROCEDURE update_quarterly_score (
1829 p_report_name IN VARCHAR2,
1830 p_total_party IN NUMBER,
1831 p_total_attribute IN NUMBER,
1832 p_system_date IN DATE,
1833 x_return_status OUT NOCOPY VARCHAR2,
1834 x_msg_count OUT NOCOPY NUMBER,
1835 x_msg_data OUT NOCOPY VARCHAR2
1836 ) IS
1837
1838 l_return_status VARCHAR2(30);
1839
1840 BEGIN
1841
1842 savepoint update_quarterly_score_pvt;
1843 l_return_status := FND_API.G_RET_STS_SUCCESS;
1844
1845 write_log('Start updating quarterly score for completeness report: '||p_report_name);
1846 write_log('>> p_total_party:'||p_total_party||' p_total_attribute: '||p_total_attribute);
1847
1848 update IMC_REPORTS_SUMMARY
1849 set (total_cnt, total_pct, org_cnt, time_stamp) =
1850 (select total_cnt, total_pct, org_cnt, time_stamp
1851 from IMC_REPORTS_SUMMARY
1852 where report_name = p_report_name
1853 and report_type = 'M'
1854 and parent_category = to_char(p_system_date,'YYYY-MM'))
1855 where report_name = p_report_name
1856 and report_type = 'Q'
1857 and parent_category = to_char(p_system_date,'YYYY-')||'Q'||to_char(p_system_date,'Q');
1858
1859 x_return_status := l_return_status;
1860
1861 EXCEPTION
1862
1863 WHEN OTHERS THEN
1864 ROLLBACK TO update_quarterly_score_pvt;
1865 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1866 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
1867 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1868 FND_MSG_PUB.ADD;
1869 FND_MSG_PUB.Count_And_Get(
1870 p_encoded => FND_API.G_FALSE,
1871 p_count => x_msg_count,
1872 p_data => x_msg_data);
1873 write_log('Error: '||sqlerrm);
1874
1875 END update_quarterly_score;
1876
1877 PROCEDURE get_enrich_count(
1878 x_return_status OUT NOCOPY VARCHAR2,
1879 x_msg_count OUT NOCOPY NUMBER,
1880 x_msg_data OUT NOCOPY VARCHAR2
1881 ) IS
1882
1883 CURSOR is_party_enrich_exist IS
1884 SELECT 'X'
1885 FROM IMC_REPORTS_SUMMARY
1886 WHERE report_name = 'PARTY_ENRICH'
1887 AND rownum = 1;
1888
1889 -- change here (Nishant)
1890 /*
1891 Bug 5593223 : Added DNB Bulk Import statistics to
1892 DNB Online Purchase statistics for Enrichment Report
1893 26-Dec-2006 (Nishant Singhai)
1894 */
1895 /*
1896 CURSOR min_max_year_month(l_system_date DATE) IS
1897 SELECT nvl(to_number(to_char(min(last_update_date), 'YYYY')),0) iy
1898 , nvl(to_number(to_char(min(last_update_date), 'MM')),0) im
1899 , to_number(to_char(l_system_date, 'YYYY')) xy
1900 , to_number(to_char(l_system_date, 'MM')) xm
1901 FROM HZ_PARTY_INTERFACE
1902 WHERE content_source_type = 'DNB'
1903 AND status = 'P2';
1904 */
1905 CURSOR min_max_year_month(l_system_date DATE) IS
1906 SELECT MIN(iy), MIN(im),
1907 TO_NUMBER(to_char(l_system_date, 'YYYY')) xy,
1908 TO_NUMBER(to_char(l_system_date, 'MM')) xm
1909 FROM (
1910 SELECT nvl(to_number(to_char(min(last_update_date), 'YYYY')),0) iy
1911 , nvl(to_number(to_char(min(last_update_date), 'MM')),0) im
1912 FROM HZ_PARTY_INTERFACE
1913 WHERE content_source_type = 'DNB'
1914 AND status = 'P2'
1915 UNION
1916 SELECT nvl(to_number(to_char(min(last_update_date), 'YYYY')),0) iy
1917 , nvl(to_number(to_char(min(last_update_date), 'MM')),0) im
1918 FROM hz_imp_batch_summary
1919 WHERE ORIGINAL_SYSTEM = 'DNB'
1920 AND IMPORT_STATUS <> 'PENDING'
1921 );
1922
1923 CURSOR get_system_month_day(l_system_date DATE) IS
1924 SELECT decode(to_char(l_system_date,'DD'),'01',1,0),
1925 decode(to_char(l_system_date,'MM'),'01',1,'04',1,'07',1,'10',1,0)
1926 FROM dual;
1927
1928 CURSOR get_all_period IS
1929 SELECT add_months(to_date(parent_category,'YYYY-MM'),1)-1
1930 , decode(substrb(parent_category,6,2),'03',1,'06',1,'09',1,'12',1,0)
1931 , decode(parent_category, to_char(sysdate,'YYYY-MM'), 1, 0)
1932 FROM IMC_REPORTS_SUMMARY
1933 WHERE report_name = 'PARTY_ENRICH'
1934 AND report_type = 'M';
1935
1936 CURSOR is_month_record_exist(l_date DATE) IS
1937 SELECT 'X'
1938 FROM IMC_REPORTS_SUMMARY
1939 WHERE report_name = 'PARTY_ENRICH'
1940 AND report_type = 'M'
1941 AND parent_category = to_char(l_date,'YYYY-MM')
1942 AND rownum = 1;
1943
1944 CURSOR is_quarter_record_exist(l_date DATE) IS
1945 SELECT 'X'
1946 FROM IMC_REPORTS_SUMMARY
1947 WHERE report_name = 'PARTY_ENRICH'
1948 AND report_type = 'Q'
1949 AND parent_category = to_char(l_date,'YYYY-')||'Q'||to_char(l_date,'Q')
1950 AND rownum = 1;
1951
1952 l_dummy VARCHAR2(1);
1953 l_min_year NUMBER := 0;
1954 l_min_month NUMBER := 0;
1955 l_max_year NUMBER := 0;
1956 l_max_month NUMBER := 0;
1957 l_system_date DATE;
1958 l_quarter_start NUMBER;
1959 l_month_start NUMBER;
1960 l_parent_cat VARCHAR2(30);
1961 l_return_status VARCHAR2(30);
1962 l_msg_data VARCHAR2(2000);
1963 l_msg_count NUMBER;
1964 l_period_end DATE;
1965 l_last_date NUMBER;
1966 l_month_exist VARCHAR2(1);
1967 l_quarter_exist VARCHAR2(1);
1968
1969 BEGIN
1970
1971 savepoint get_enrich_count_pvt;
1972
1973 l_system_date := sysdate;
1974
1975 write_log('Start collecting data for enrichment report at: '||l_system_date);
1976
1977 -- check if first time to run this program
1978 OPEN is_party_enrich_exist;
1979 FETCH is_party_enrich_exist INTO l_dummy;
1980 CLOSE is_party_enrich_exist;
1981
1982 IF (l_dummy IS NULL) THEN
1983 -- first time running enrichment report
1984
1985 write_log('Removing data for enrichment report');
1986
1987 DELETE FROM IMC_REPORTS_SUMMARY
1988 WHERE REPORT_NAME = 'PARTY_ENRICH';
1989
1990 OPEN min_max_year_month(l_system_date);
1991 FETCH min_max_year_month INTO l_min_year, l_min_month, l_max_year, l_max_month;
1992 CLOSE min_max_year_month;
1993
1994 IF(l_min_year = 0) THEN
1995 write_log('No enrichment data exist in party interface table');
1996 ELSE
1997 write_log('Adding month/year combination for enrichment report');
1998
1999 FOR I IN l_min_year..l_max_year LOOP
2000 INSERT INTO IMC_REPORTS_SUMMARY (
2001 REPORT_NAME
2002 ,REPORT_TYPE
2003 ,CATEGORY
2004 ,PARENT_CATEGORY
2005 ,TIME_STAMP )
2006 SELECT
2007 'PARTY_ENRICH'
2008 ,'M'
2009 ,NULL
2010 ,to_char(I)||'-'||lookup_code
2011 ,sysdate
2012 FROM FND_LOOKUP_VALUES
2013 WHERE LOOKUP_TYPE = 'MONTH'
2014 AND ENABLED_FLAG = 'Y'
2015 GROUP BY lookup_code;
2016 END LOOP;
2017
2018 write_log('Removing out of range month/year combination for enrichment report');
2019
2020 -- remove rows that are out of min year-month and max year-month range
2021 DELETE IMC_REPORTS_SUMMARY
2022 WHERE report_name = 'PARTY_ENRICH'
2023 AND (parent_category < to_char(l_min_year)||'-'||lpad(to_char(l_min_month),2,'0')
2024 OR parent_category > to_char(l_max_year)||'-'||lpad(to_char(l_max_month),2,'0'));
2025
2026 -- count the score of number of enriched data
2027 OPEN get_all_period;
2028 LOOP
2029 FETCH get_all_period INTO l_period_end, l_quarter_start, l_last_date;
2030 EXIT WHEN get_all_period%NOTFOUND;
2031 -- if l_period_end is same as l_system_date, then put l_system_date as l_period_end
2032 IF(l_last_date = 1) THEN
2033 update_menrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
2034 ELSE
2035 update_menrich_score(l_period_end, l_return_status, l_msg_count, l_msg_data);
2036 END IF;
2037 IF(l_quarter_start = 1) THEN
2038 IF(l_last_date = 1) THEN
2039 insert_qenrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
2040 ELSE
2041 insert_qenrich_score(l_period_end, l_return_status, l_msg_count, l_msg_data);
2042 END IF;
2043 ELSE
2044 IF((to_number(to_char(l_period_end,'YYYY')) = l_max_year) AND
2045 (to_number(to_char(l_period_end,'MM')) = l_max_month)) THEN
2046 IF(l_last_date = 1) THEN
2047 insert_qenrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
2048 ELSE
2049 insert_qenrich_score(l_period_end, l_return_status, l_msg_count, l_msg_data);
2050 END IF;
2051 END IF;
2052 END IF;
2053 END LOOP;
2054 CLOSE get_all_period;
2055 END IF;
2056
2057 ELSE
2058 /*
2059 OPEN get_system_month_day(l_system_date);
2060 FETCH get_system_month_day INTO l_month_start, l_quarter_start;
2061 CLOSE get_system_month_day;
2062 */
2063 -- check if current month data exist
2064 OPEN is_month_record_exist(l_system_date);
2065 FETCH is_month_record_exist INTO l_month_exist;
2066 CLOSE is_month_record_exist;
2067
2068 -- check if current quarter data exist
2069 OPEN is_quarter_record_exist(l_system_date);
2070 FETCH is_quarter_record_exist INTO l_quarter_exist;
2071 CLOSE is_quarter_record_exist;
2072
2073 -- IF(l_month_start = 1) THEN
2074 IF (l_month_exist IS NULL) THEN
2075 insert_menrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
2076 -- IF(l_quarter_start = 1) THEN
2077 IF (l_quarter_exist IS NULL) THEN
2078 insert_qenrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
2079 ELSE
2080 update_qenrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
2081 END IF;
2082 ELSE
2083 update_menrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
2084 update_qenrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
2085 END IF;
2086
2087 END IF;
2088
2089 write_log('Finish collecting data for enrichment report at: '||l_system_date);
2090
2091 EXCEPTION
2092
2093 WHEN FND_API.G_EXC_ERROR THEN
2094 ROLLBACK TO get_enrich_count_pvt;
2095 x_return_status := FND_API.G_RET_STS_ERROR;
2096 FND_MSG_PUB.Count_And_Get(
2097 p_encoded => FND_API.G_FALSE,
2098 p_count => x_msg_count,
2099 p_data => x_msg_data);
2100
2101 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2102 ROLLBACK TO get_enrich_count_pvt;
2103 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2104 FND_MSG_PUB.Count_And_Get(
2105 p_encoded => FND_API.G_FALSE,
2106 p_count => x_msg_count,
2107 p_data => x_msg_data);
2108
2109 WHEN OTHERS THEN
2110 ROLLBACK TO get_enrich_count_pvt;
2111 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2112 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2113 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2114 FND_MSG_PUB.ADD;
2115 FND_MSG_PUB.Count_And_Get(
2116 p_encoded => FND_API.G_FALSE,
2117 p_count => x_msg_count,
2118 p_data => x_msg_data);
2119 write_log('Error: '||sqlerrm);
2120
2121 END get_enrich_count;
2122
2123 PROCEDURE insert_menrich_score (
2124 p_system_date IN DATE,
2125 x_return_status OUT NOCOPY VARCHAR2,
2126 x_msg_count OUT NOCOPY NUMBER,
2127 x_msg_data OUT NOCOPY VARCHAR2
2128 ) IS
2129
2130 l_return_status VARCHAR2(30);
2131 l_party_count NUMBER;
2132 l_enpty_count NUMBER;
2133
2134 BEGIN
2135
2136 savepoint insert_menrich_score_pvt;
2137 l_return_status := FND_API.G_RET_STS_SUCCESS;
2138
2139 write_log('Start inserting monthly data for enrichment report: '||p_system_date);
2140
2141 l_party_count := get_party_count('ORGANIZATION', p_system_date, l_return_status);
2142 l_enpty_count := get_enrich_party_count('ORGANIZATION', p_system_date, l_return_status);
2143
2144 write_log('>> l_party_count: '||l_party_count);
2145 write_log('>> l_enpty_count: '||l_enpty_count);
2146
2147 -- change here (Nishant)
2148 /*
2149 Bug 5593223 : Added DNB Bulk Import statistics to
2150 DNB Online Purchase statistics for Enrichment Report
2151 26-Dec-2006 (Nishant Singhai)
2152 */
2153 /*
2154 INSERT INTO IMC_REPORTS_SUMMARY (
2155 report_name
2156 ,report_type
2157 ,category
2158 ,parent_category
2159 ,org_cnt
2160 ,total_cnt
2161 ,total_pct
2162 ,time_stamp
2163 ) SELECT
2164 'PARTY_ENRICH'
2165 ,'M'
2166 ,NULL
2167 ,to_char(p_system_date,'YYYY-MM')
2168 ,nvl(sum(decode(count(1),0,0,1)),0)
2169 ,l_enpty_count
2170 ,l_party_count
2171 ,p_system_date
2172 FROM HZ_PARTY_INTERFACE
2173 WHERE status = 'P2'
2174 AND to_char(last_update_date,'YYYY-MM') = to_char(p_system_date,'YYYY-MM')
2175 GROUP BY party_id;
2176 */
2177 INSERT INTO IMC_REPORTS_SUMMARY (
2178 report_name
2179 ,report_type
2180 ,category
2181 ,parent_category
2182 ,org_cnt
2183 ,total_cnt
2184 ,total_pct
2185 ,time_stamp
2186 ) SELECT
2187 'PARTY_ENRICH'
2188 ,'M'
2189 ,NULL
2190 ,to_char(p_system_date,'YYYY-MM')
2191 ,SUM(org_enriched_for_period)
2192 ,l_enpty_count
2193 ,l_party_count
2194 ,p_system_date
2195 FROM (
2196 SELECT nvl(sum(decode(count(1),0,0,1)),0) org_enriched_for_period
2197 FROM HZ_PARTY_INTERFACE
2198 WHERE status = 'P2'
2199 AND content_source_type = 'DNB'
2200 AND TO_CHAR(last_update_date,'YYYY-MM')= TO_CHAR(p_system_date,'YYYY-MM')
2201 GROUP BY party_id
2202 UNION ALL
2203 SELECT nvl(SUM(parties_imported),0) org_enriched_for_period
2204 FROM hz_imp_batch_summary
2205 WHERE ORIGINAL_SYSTEM = 'DNB'
2206 AND IMPORT_STATUS <> 'PENDING'
2207 AND TO_CHAR(last_update_date,'YYYY-MM')= TO_CHAR(p_system_date,'YYYY-MM')
2208 ) ;
2209
2210 EXCEPTION
2211
2212 WHEN OTHERS THEN
2213 ROLLBACK TO insert_menrich_score_pvt;
2214 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2215 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2216 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2217 FND_MSG_PUB.ADD;
2218 FND_MSG_PUB.Count_And_Get(
2219 p_encoded => FND_API.G_FALSE,
2220 p_count => x_msg_count,
2221 p_data => x_msg_data);
2222 write_log('Error: '||sqlerrm);
2223
2224 END insert_menrich_score;
2225
2226 PROCEDURE update_menrich_score (
2227 p_system_date IN DATE,
2228 x_return_status OUT NOCOPY VARCHAR2,
2229 x_msg_count OUT NOCOPY NUMBER,
2230 x_msg_data OUT NOCOPY VARCHAR2
2231 ) IS
2232
2233 l_return_status VARCHAR2(30);
2234 l_party_count NUMBER;
2235 l_enpty_count NUMBER;
2236
2237 BEGIN
2238
2239 savepoint update_menrich_score_pvt;
2240 l_return_status := FND_API.G_RET_STS_SUCCESS;
2241
2242 write_log('Start updating monthly data for enrichment report: '||p_system_date);
2243
2244 l_party_count := get_party_count('ORGANIZATION', p_system_date, l_return_status);
2245 l_enpty_count := get_enrich_party_count('ORGANIZATION', p_system_date, l_return_status);
2246
2247 write_log('>> l_party_count: '||l_party_count);
2248 write_log('>> l_enpty_count: '||l_enpty_count);
2249
2250 -- change here (Nishant)
2251 /*
2252 Bug 5593223 : Added DNB Bulk Import statistics to
2253 DNB Online Purchase statistics for Enrichment Report
2254 26-Dec-2006 (Nishant Singhai)
2255 */
2256 /*
2257 UPDATE IMC_REPORTS_SUMMARY
2258 SET (org_cnt, total_cnt,total_pct, time_stamp) =
2259 (SELECT nvl(sum(decode(count(1),0,0,1)),0), l_enpty_count, l_party_count, p_system_date
2260 FROM HZ_PARTY_INTERFACE
2261 WHERE status = 'P2'
2262 AND to_char(last_update_date,'YYYY-MM') = to_char(p_system_date,'YYYY-MM')
2263 GROUP BY party_id)
2264 WHERE report_name = 'PARTY_ENRICH'
2265 AND report_type = 'M'
2266 AND parent_category = to_char(p_system_date,'YYYY-MM');
2267 */
2268
2269 UPDATE IMC_REPORTS_SUMMARY
2270 SET (org_cnt, total_cnt,total_pct, time_stamp) =
2271 (SELECT SUM(org_enriched_for_period), l_enpty_count, l_party_count, p_system_date
2272 FROM (
2273 SELECT nvl(sum(decode(count(1),0,0,1)),0) org_enriched_for_period
2274 FROM HZ_PARTY_INTERFACE
2275 WHERE status = 'P2'
2276 AND content_source_type = 'DNB'
2277 AND TO_CHAR(last_update_date,'YYYY-MM')= TO_CHAR(p_system_date,'YYYY-MM')
2278 GROUP BY party_id
2279 UNION ALL
2280 SELECT nvl(SUM(parties_imported),0) org_enriched_for_period
2281 FROM hz_imp_batch_summary
2282 WHERE ORIGINAL_SYSTEM = 'DNB'
2283 AND IMPORT_STATUS <> 'PENDING'
2284 AND TO_CHAR(last_update_date,'YYYY-MM')= TO_CHAR(p_system_date,'YYYY-MM')
2285 )
2286 )
2287 WHERE report_name = 'PARTY_ENRICH'
2288 AND report_type = 'M'
2289 AND parent_category = to_char(p_system_date,'YYYY-MM');
2290
2291 EXCEPTION
2292
2293 WHEN OTHERS THEN
2294 ROLLBACK TO update_menrich_score_pvt;
2295 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2296 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2297 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2298 FND_MSG_PUB.ADD;
2299 FND_MSG_PUB.Count_And_Get(
2300 p_encoded => FND_API.G_FALSE,
2301 p_count => x_msg_count,
2302 p_data => x_msg_data);
2303 write_log('Error: '||sqlerrm);
2304
2305 END update_menrich_score;
2306
2307 PROCEDURE insert_qenrich_score (
2308 p_system_date IN DATE,
2309 x_return_status OUT NOCOPY VARCHAR2,
2310 x_msg_count OUT NOCOPY NUMBER,
2311 x_msg_data OUT NOCOPY VARCHAR2
2312 ) IS
2313
2314 l_return_status VARCHAR2(30);
2315 l_party_count NUMBER;
2316 l_enpty_count NUMBER;
2317
2318 BEGIN
2319
2320 savepoint insert_qenrich_score_pvt;
2321 l_return_status := FND_API.G_RET_STS_SUCCESS;
2322
2323 write_log('Start inserting quarterly data for enrichment report: '||p_system_date);
2324
2325 l_party_count := get_party_count('ORGANIZATION', p_system_date, l_return_status);
2326 l_enpty_count := get_enrich_party_count('ORGANIZATION', p_system_date, l_return_status);
2327
2328 write_log('>> l_party_count: '||l_party_count);
2329 write_log('>> l_enpty_count: '||l_enpty_count);
2330
2331 -- change here (Nishant)
2332 /*
2333 Bug 5593223 : Added DNB Bulk Import statistics to
2334 DNB Online Purchase statistics for Enrichment Report
2335 26-Dec-2006 (Nishant Singhai)
2336 */
2337 /*
2338 INSERT INTO IMC_REPORTS_SUMMARY (
2339 report_name
2340 ,report_type
2341 ,category
2342 ,parent_category
2343 ,org_cnt
2344 ,total_cnt
2345 ,total_pct
2346 ,time_stamp
2347 ) SELECT
2348 'PARTY_ENRICH'
2349 ,'Q'
2350 ,NULL
2351 ,to_char(p_system_date,'YYYY-')||'Q'||to_char(p_system_date,'Q')
2352 ,nvl(sum(decode(count(1),0,0,1)),0)
2353 ,l_enpty_count
2354 ,l_party_count
2355 ,p_system_date
2356 FROM HZ_PARTY_INTERFACE
2357 WHERE status = 'P2'
2358 AND to_char(last_update_date,'YYYY-Q') = to_char(p_system_date,'YYYY-Q')
2359 GROUP BY party_id;
2360 */
2361 INSERT INTO IMC_REPORTS_SUMMARY (
2362 report_name
2363 ,report_type
2364 ,category
2365 ,parent_category
2366 ,org_cnt
2367 ,total_cnt
2368 ,total_pct
2369 ,time_stamp
2370 ) SELECT
2371 'PARTY_ENRICH'
2372 ,'Q'
2373 ,NULL
2374 ,to_char(p_system_date,'YYYY-')||'Q'||to_char(p_system_date,'Q')
2375 ,SUM(org_enriched_for_period)
2376 ,l_enpty_count
2377 ,l_party_count
2378 ,p_system_date
2379 FROM (
2380 SELECT nvl(SUM(decode(count(1),0,0,1)),0) org_enriched_for_period
2381 FROM HZ_PARTY_INTERFACE
2382 WHERE status = 'P2'
2383 AND content_source_type = 'DNB'
2384 AND to_char(last_update_date,'YYYY-Q') = to_char(p_system_date,'YYYY-Q')
2385 GROUP BY party_id
2386 UNION ALL
2387 SELECT nvl(SUM(parties_imported),0) org_enriched_for_period
2388 FROM hz_imp_batch_summary
2389 WHERE ORIGINAL_SYSTEM = 'DNB'
2390 AND IMPORT_STATUS <> 'PENDING'
2391 AND TO_CHAR(last_update_date,'YYYY-Q')= TO_CHAR(p_system_date,'YYYY-Q')
2392 );
2393
2394 EXCEPTION
2395
2396 WHEN OTHERS THEN
2397 ROLLBACK TO insert_qenrich_score_pvt;
2398 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2399 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2400 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2401 FND_MSG_PUB.ADD;
2402 FND_MSG_PUB.Count_And_Get(
2403 p_encoded => FND_API.G_FALSE,
2404 p_count => x_msg_count,
2405 p_data => x_msg_data);
2406 write_log('Error: '||sqlerrm);
2407
2408 END insert_qenrich_score;
2409
2410 PROCEDURE update_qenrich_score (
2411 p_system_date IN DATE,
2412 x_return_status OUT NOCOPY VARCHAR2,
2413 x_msg_count OUT NOCOPY NUMBER,
2414 x_msg_data OUT NOCOPY VARCHAR2
2415 ) IS
2416
2417 l_return_status VARCHAR2(30);
2418 l_party_count NUMBER;
2419 l_enpty_count NUMBER;
2420
2421 BEGIN
2422
2423 savepoint update_qenrich_score_pvt;
2424 l_return_status := FND_API.G_RET_STS_SUCCESS;
2425
2426 write_log('Start updating quarterly data for enrichment report: '||p_system_date);
2427
2428 l_party_count := get_party_count('ORGANIZATION', p_system_date, l_return_status);
2429 l_enpty_count := get_enrich_party_count('ORGANIZATION', p_system_date, l_return_status);
2430
2431 write_log('>> l_party_count: '||l_party_count);
2432 write_log('>> l_enpty_count: '||l_enpty_count);
2433
2434 -- change here (Nishant)
2435 /*
2436 Bug 5593223 : Added DNB Bulk Import statistics to
2437 DNB Online Purchase statistics for Enrichment Report
2438 26-Dec-2006 (Nishant Singhai)
2439 */
2440 /*
2441 UPDATE IMC_REPORTS_SUMMARY
2442 SET (org_cnt, total_cnt,total_pct, time_stamp) =
2443 (SELECT nvl(sum(decode(count(1),0,0,1)),0), l_enpty_count, l_party_count, p_system_date
2444 FROM HZ_PARTY_INTERFACE
2445 WHERE status = 'P2'
2446 AND to_char(last_update_date,'YYYY-Q') = to_char(p_system_date,'YYYY-Q')
2447 GROUP BY party_id)
2448 WHERE report_name = 'PARTY_ENRICH'
2449 AND report_type = 'Q'
2450 AND parent_category = to_char(p_system_date,'YYYY-')||'Q'||to_char(p_system_date,'Q');
2451 */
2452 UPDATE IMC_REPORTS_SUMMARY
2453 SET (org_cnt, total_cnt,total_pct, time_stamp) =
2454 (SELECT SUM(org_enriched_for_period), l_enpty_count, l_party_count, p_system_date
2455 FROM (
2456 SELECT nvl(sum(decode(count(1),0,0,1)),0) org_enriched_for_period
2457 FROM HZ_PARTY_INTERFACE
2458 WHERE status = 'P2'
2459 AND content_source_type = 'DNB'
2460 AND TO_CHAR(last_update_date,'YYYY-Q')= TO_CHAR(p_system_date,'YYYY-Q')
2461 GROUP BY party_id
2462 UNION ALL
2463 SELECT nvl(SUM(parties_imported),0) org_enriched_for_period
2464 FROM hz_imp_batch_summary
2465 WHERE ORIGINAL_SYSTEM = 'DNB'
2466 AND IMPORT_STATUS <> 'PENDING'
2467 AND TO_CHAR(last_update_date,'YYYY-Q')= TO_CHAR(p_system_date,'YYYY-Q')
2468 )
2469 )
2470 WHERE report_name = 'PARTY_ENRICH'
2471 AND report_type = 'Q'
2472 AND parent_category = to_char(p_system_date,'YYYY-')||'Q'||to_char(p_system_date,'Q');
2473
2474 EXCEPTION
2475
2476 WHEN OTHERS THEN
2477 ROLLBACK TO update_qenrich_score_pvt;
2478 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2479 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2480 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2481 FND_MSG_PUB.ADD;
2482 FND_MSG_PUB.Count_And_Get(
2483 p_encoded => FND_API.G_FALSE,
2484 p_count => x_msg_count,
2485 p_data => x_msg_data);
2486 write_log('Error: '||sqlerrm);
2487
2488 END update_qenrich_score;
2489
2490 -- get total number of parties
2491 FUNCTION get_party_count(
2492 p_party_type IN VARCHAR2,
2493 p_date IN DATE,
2494 x_return_status IN OUT NOCOPY VARCHAR2
2495 ) RETURN NUMBER IS
2496
2497 -- count ORGANIZATION or PERSON parties
2498 cursor get_pty_count(l_party_type VARCHAR2, l_date DATE) is
2499 SELECT count(1)
2500 FROM HZ_PARTIES
2501 WHERE status in ('A','I')
2502 AND party_type = l_party_type
2503 AND trunc(creation_date) <= trunc(l_date);
2504
2505 -- count CONTACT
2506 cursor get_contact_count(l_date DATE) is
2507 SELECT count(1)
2508 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r, HZ_PARTIES p
2509 , HZ_CODE_ASSIGNMENTS ca, HZ_RELATIONSHIP_TYPES rt
2510 WHERE oc.party_relationship_id = r.relationship_id
2511 AND r.subject_type = 'PERSON'
2512 AND r.subject_id = p.party_id
2513 AND ca.class_category = 'RELATIONSHIP_TYPE_GROUP'
2514 AND ca.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
2515 AND ca.class_code = 'PARTY_REL_GRP_CONTACTS'
2516 AND rt.relationship_type_id = ca.owner_table_id
2517 AND rt.subject_type = 'PERSON'
2518 AND rt.forward_rel_code = r.relationship_code
2519 AND rt.relationship_type = r.relationship_type
2520 AND p.status in ('A','I')
2521 AND trunc(p.creation_date) <= trunc(l_date);
2522
2523 l_party_count NUMBER;
2524
2525 BEGIN
2526
2527 IF(p_party_type = 'CONTACT') THEN
2528 OPEN get_contact_count(p_date);
2529 FETCH get_contact_count INTO l_party_count;
2530 CLOSE get_contact_count;
2531 ELSE
2532 OPEN get_pty_count(p_party_type, p_date);
2533 FETCH get_pty_count INTO l_party_count;
2534 CLOSE get_pty_count;
2535 END IF;
2536
2537 RETURN l_party_count;
2538
2539 EXCEPTION
2540 WHEN OTHERS THEN
2541 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2542 RETURN NULL;
2543 END get_party_count;
2544
2545 FUNCTION get_enrich_party_count (
2546 p_party_type IN VARCHAR2,
2547 p_date IN DATE,
2548 x_return_status IN OUT NOCOPY VARCHAR2
2549 ) RETURN NUMBER IS
2550
2551 -- change here (Nishant)
2552 /*
2553 Bug 5593223 : Added DNB Bulk Import statistics to
2554 DNB Online Purchase statistics for Enrichment Report
2555 26-Dec-2006 (Nishant Singhai)
2556 */
2557 /*
2558 cursor get_enpty_count(l_party_type VARCHAR2, l_date DATE) is
2559 SELECT nvl(sum(decode(count(1),0,0,1)),0)
2560 FROM HZ_PARTY_INTERFACE
2561 WHERE status = 'P2'
2562 AND content_source_type = 'DNB'
2563 AND trunc(last_update_date) <= trunc(l_date)
2564 GROUP BY party_id;
2565 */
2566
2567 cursor get_enpty_count(l_party_type VARCHAR2, l_date DATE) is
2568 SELECT SUM(total_org_enriched) FROM (
2569 SELECT nvl(sum(decode(count(1),0,0,1)),0) total_org_enriched
2570 FROM HZ_PARTY_INTERFACE
2571 WHERE status = 'P2'
2572 AND content_source_type = 'DNB'
2573 AND TRUNC(last_update_date) <= TRUNC(l_date)
2574 GROUP BY party_id
2575 UNION ALL
2576 SELECT nvl(SUM(parties_imported),0) total_org_enriched
2577 FROM hz_imp_batch_summary
2578 WHERE ORIGINAL_SYSTEM = 'DNB'
2579 AND IMPORT_STATUS <> 'PENDING'
2580 AND TRUNC(last_update_date) <= TRUNC(l_date)
2581 );
2582
2583 l_enpty_count NUMBER;
2584
2585 BEGIN
2586
2587 OPEN get_enpty_count(p_party_type, p_date);
2588 FETCH get_enpty_count INTO l_enpty_count;
2589 CLOSE get_enpty_count;
2590
2591 RETURN l_enpty_count;
2592
2593 EXCEPTION
2594 WHEN OTHERS THEN
2595 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2596 RETURN NULL;
2597 END get_enrich_party_count;
2598
2599 FUNCTION get_party_clause(
2600 p_table_name IN VARCHAR2,
2601 p_party_type IN VARCHAR2,
2602 p_system_date IN DATE,
2603 x_return_status IN OUT NOCOPY VARCHAR2)
2604 RETURN VARCHAR2 IS
2605
2606 str VARCHAR2(4000);
2607
2608 BEGIN
2609
2610 IF(p_party_type = 'CONTACT') THEN
2611 str := ' from HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r, HZ_PARTIES pty'||
2612 ' , HZ_CODE_ASSIGNMENTS ca, HZ_RELATIONSHIP_TYPES rt'||
2613 ' where pty.status in (''A'',''I'')'||
2614 ' and oc.party_relationship_id = r.relationship_id' ||
2615 ' and r.subject_type = ''PERSON'''||
2616 ' and r.party_id = pty.party_id'||
2617 ' and ca.class_category = ''RELATIONSHIP_TYPE_GROUP'''||
2618 ' and ca.owner_table_name = ''HZ_RELATIONSHIP_TYPES'''||
2619 ' and ca.class_code = ''PARTY_REL_GRP_CONTACTS'''||
2620 ' and rt.relationship_type_id = ca.owner_table_id'||
2621 ' and rt.subject_type = ''PERSON'''||
2622 ' and rt.forward_rel_code = r.relationship_code'||
2623 ' and rt.relationship_type = r.relationship_type';
2624 ELSE
2625 str := ' from HZ_PARTIES pty'||
2626 ' where pty.status in (''A'',''I'')'||
2627 ' and pty.party_type = '''||p_party_type||'''';
2628 END IF;
2629
2630 RETURN str;
2631
2632 EXCEPTION
2633 WHEN OTHERS THEN
2634 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2635 RETURN NULL;
2636 END get_party_clause;
2637
2638 FUNCTION get_code_assign_clause(
2639 p_table_name IN VARCHAR2,
2640 p_party_type IN VARCHAR2,
2641 p_attribute IN VARCHAR2,
2642 p_system_date IN DATE,
2643 x_return_status IN OUT NOCOPY VARCHAR2)
2644 RETURN VARCHAR2 IS
2645
2646 str VARCHAR2(4000);
2647
2648 BEGIN
2649
2650 -- code assignment is only for organization report
2651 str := ' from (select owner_table_id '||p_attribute||
2652 ' from HZ_CODE_ASSIGNMENTS c'||
2653 ' where owner_table_name = ''HZ_PARTIES'''||
2654 ' and status = ''A'''||
2655 ' and :p_date between c.start_date_active and nvl(c.end_date_active,:p_date)'||
2656 ' group by owner_table_id) ca';
2657
2658 RETURN str;
2659
2660 EXCEPTION
2661 WHEN OTHERS THEN
2662 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2663 RETURN NULL;
2664 END get_code_assign_clause;
2665
2666 FUNCTION get_profile_clause(
2667 p_table_name IN VARCHAR2,
2668 p_party_type IN VARCHAR2,
2669 p_system_date IN DATE,
2670 x_return_status IN OUT NOCOPY VARCHAR2)
2671 RETURN VARCHAR2 IS
2672
2673 str VARCHAR2(4000);
2674
2675 BEGIN
2676
2677 -- contact reports should NOT have profile checking
2678 -- the following if may not be valid now
2679 IF(p_party_type = 'CONTACT') THEN
2680 NULL;
2681 ELSE
2682 str := ' from HZ_PARTIES pty, '||p_table_name||' prof'||
2683 ' where pty.status in (''A'',''I'')'||
2684 ' and pty.party_type = '''||p_party_type||''''||
2685 ' and pty.party_id = prof.party_id'||
2686 ' and :p_date between prof.effective_start_date and nvl(prof.effective_end_date,:p_date)';
2687 END IF;
2688
2689 RETURN str;
2690
2691 EXCEPTION
2692 WHEN OTHERS THEN
2693 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2694 RETURN NULL;
2695 END get_profile_clause;
2696
2697 FUNCTION get_contactpoint_clause(
2698 p_table_name IN VARCHAR2,
2699 p_party_type IN VARCHAR2,
2700 p_attribute IN VARCHAR2,
2701 p_system_date IN DATE,
2702 x_return_status IN OUT NOCOPY VARCHAR2)
2703 RETURN VARCHAR2 IS
2704
2705 str VARCHAR2(4000);
2706 l_contact_point_type VARCHAR2(30);
2707
2708 BEGIN
2709
2710 IF(p_attribute = 'URL') THEN
2711 l_contact_point_type := 'WEB';
2712 ELSIF(p_attribute = 'EMAIL_ADDRESS') THEN
2713 l_contact_point_type := 'EMAIL';
2714 ELSE
2715 l_contact_point_type := 'PHONE';
2716 END IF;
2717
2718 -- if the reports is used for contact, then we need to use HZ_RELATIONSHIPS
2719 -- and HZ_ORG_CONTACTS table to find out that person
2720
2721 IF(p_party_type = 'CONTACT') THEN
2722 str := ' from HZ_PARTIES pty, HZ_CONTACT_POINTS contpt,'||
2723 ' HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r,'||
2724 ' HZ_CODE_ASSIGNMENTS ca, HZ_RELATIONSHIP_TYPES rt'||
2725 ' where pty.status in (''A'',''I'')'||
2726 ' and oc.party_relationship_id = r.relationship_id' ||
2727 ' and r.subject_type = ''PERSON'''||
2728 ' and r.party_id = pty.party_id'||
2729 ' and ca.class_category = ''RELATIONSHIP_TYPE_GROUP'''||
2730 ' and ca.owner_table_name = ''HZ_RELATIONSHIP_TYPES'''||
2731 ' and ca.class_code = ''PARTY_REL_GRP_CONTACTS'''||
2732 ' and rt.relationship_type_id = ca.owner_table_id'||
2733 ' and rt.subject_type = ''PERSON'''||
2734 ' and rt.forward_rel_code = r.relationship_code'||
2735 ' and rt.relationship_type = r.relationship_type'||
2736 ' and pty.party_id = contpt.owner_table_id'||
2737 ' and contpt.owner_table_name = ''HZ_PARTIES'''||
2738 ' and contpt.status in (''A'',''I'')'||
2739 ' and contpt.contact_point_type = '''||l_contact_point_type||''''||
2740 ' and contpt.primary_flag = ''Y''';
2741 ELSE
2742 str := ' from HZ_PARTIES pty, HZ_CONTACT_POINTS contpt'||
2743 ' where pty.status in (''A'',''I'')'||
2744 ' and pty.party_type = '''||p_party_type||''''||
2745 ' and pty.party_id = contpt.owner_table_id'||
2746 ' and contpt.owner_table_name = ''HZ_PARTIES'''||
2747 ' and contpt.status in (''A'',''I'')'||
2748 ' and contpt.contact_point_type = '''||l_contact_point_type||''''||
2749 ' and contpt.primary_flag = ''Y''';
2750 END IF;
2751
2752 RETURN str;
2753
2754 EXCEPTION
2755 WHEN OTHERS THEN
2756 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2757 RETURN NULL;
2758 END get_contactpoint_clause;
2759
2760 FUNCTION get_org_contact_clause(
2761 p_table_name IN VARCHAR2,
2762 p_party_type IN VARCHAR2,
2763 p_system_date IN DATE,
2764 x_return_status IN OUT NOCOPY VARCHAR2)
2765 RETURN VARCHAR2 IS
2766
2767 str VARCHAR2(4000);
2768
2769 BEGIN
2770
2771 -- org_contact is only used for contact report
2772 str := ' from HZ_ORG_CONTACTS orgcnt, HZ_RELATIONSHIPS r, HZ_PARTIES pty'||
2773 ' , HZ_CODE_ASSIGNMENTS ca, HZ_RELATIONSHIP_TYPES rt'||
2774 ' where pty.status in (''A'',''I'')'||
2775 ' and orgcnt.party_relationship_id = r.relationship_id' ||
2776 ' and r.subject_type = ''PERSON'''||
2777 ' and r.party_id = pty.party_id'||
2778 ' and ca.class_category = ''RELATIONSHIP_TYPE_GROUP'''||
2779 ' and ca.owner_table_name = ''HZ_RELATIONSHIP_TYPES'''||
2780 ' and ca.class_code = ''PARTY_REL_GRP_CONTACTS'''||
2781 ' and rt.relationship_type_id = ca.owner_table_id'||
2782 ' and rt.subject_type = ''PERSON'''||
2783 ' and rt.forward_rel_code = r.relationship_code'||
2784 ' and rt.relationship_type = r.relationship_type';
2785
2786 RETURN str;
2787
2788 EXCEPTION
2789 WHEN OTHERS THEN
2790 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2791 RETURN NULL;
2792 END get_org_contact_clause;
2793
2794 FUNCTION get_org_contact_role_clause(
2795 p_table_name IN VARCHAR2,
2796 p_party_type IN VARCHAR2,
2797 p_attribute IN VARCHAR2,
2798 p_system_date IN DATE,
2799 x_return_status IN OUT NOCOPY VARCHAR2)
2800 RETURN VARCHAR2 IS
2801
2802 str VARCHAR2(4000);
2803
2804 BEGIN
2805
2806 str := ' from (select r.party_id '||p_attribute||
2807 ' from HZ_ORG_CONTACTS orgcnt, HZ_RELATIONSHIPS r, HZ_PARTIES pty'||
2808 ' , HZ_CODE_ASSIGNMENTS ca, HZ_RELATIONSHIP_TYPES rt'||
2809 ' where pty.status in (''A'',''I'')'||
2810 ' and orgcnt.party_relationship_id = r.relationship_id' ||
2811 ' and r.subject_type = ''PERSON'''||
2812 ' and r.party_id = pty.party_id'||
2813 ' and ca.class_category = ''RELATIONSHIP_TYPE_GROUP'''||
2814 ' and ca.owner_table_name = ''HZ_RELATIONSHIP_TYPES'''||
2815 ' and ca.class_code = ''PARTY_REL_GRP_CONTACTS'''||
2816 ' and rt.relationship_type_id = ca.owner_table_id'||
2817 ' and rt.subject_type = ''PERSON'''||
2818 ' and rt.forward_rel_code = r.relationship_code'||
2819 ' and rt.relationship_type = r.relationship_type'||
2820 ' and exists (select 1 from HZ_ORG_CONTACT_ROLES ocr'||
2821 ' where ocr.status = ''A'''||
2822 ' and ocr.org_contact_id = orgcnt.org_contact_id)) ocrole';
2823
2824 RETURN str;
2825
2826 EXCEPTION
2827 WHEN OTHERS THEN
2828 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2829 RETURN NULL;
2830 END get_org_contact_role_clause;
2831
2832 -- main PROCEDURE that collect data for quality reports
2833
2834 PROCEDURE extract_quality IS
2835
2836 l_return_status VARCHAR2(30);
2837 l_msg_count NUMBER;
2838 l_msg_data VARCHAR2(2000);
2839
2840 BEGIN
2841
2842 g_proc_name := 'extract_quality';
2843
2844 get_compl_count(l_return_status, l_msg_count, l_msg_data);
2845 get_enrich_count(l_return_status, l_msg_count, l_msg_data);
2846
2847 EXCEPTION
2848 WHEN OTHERS THEN
2849 write_log('Error:' || sqlerrm);
2850
2851 END extract_quality;
2852
2853 PROCEDURE archive_compl_report (
2854 p_report_code IN VARCHAR2,
2855 x_return_status OUT NOCOPY VARCHAR2
2856 ) IS
2857
2858 CURSOR get_meaning IS
2859 SELECT meaning, description, start_date_active
2860 FROM IMC_LOOKUPS
2861 WHERE lookup_type = 'COMPLETENESS_REPORTS'
2862 AND lookup_code = p_report_code;
2863
2864 l_mean VARCHAR2(80);
2865 l_desc VARCHAR2(240);
2866 l_start_date DATE;
2867 BEGIN
2868 x_return_status := FND_API.G_RET_STS_SUCCESS;
2869
2870 OPEN get_meaning;
2871 FETCh get_meaning INTO l_mean, l_desc, l_start_date;
2872 CLOSE get_meaning;
2873
2874 FND_LOOKUP_VALUES_PKG.update_row(
2875 x_lookup_type => 'COMPLETENESS_REPORTS',
2876 x_security_group_id => NULL,
2877 x_view_application_id => 879,
2878 x_lookup_code => p_report_code,
2879 x_tag => NULL,
2880 x_attribute_category => NULL,
2881 x_attribute1 => NULL,
2882 x_attribute2 => NULL,
2883 x_attribute3 => NULL,
2884 x_attribute4 => NULL,
2885 x_enabled_flag => 'Y',
2886 x_start_date_active => l_start_date,
2887 x_end_date_active => sysdate,
2888 x_territory_code => NULL,
2889 x_attribute5 => NULL,
2890 x_attribute6 => NULL,
2891 x_attribute7 => NULL,
2892 x_attribute8 => NULL,
2893 x_attribute9 => NULL,
2894 x_attribute10 => NULL,
2895 x_attribute11 => NULL,
2896 x_attribute12 => NULL,
2897 x_attribute13 => NULL,
2898 x_attribute14 => NULL,
2899 x_attribute15 => NULL,
2900 x_meaning => l_mean,
2901 x_description => l_desc,
2902 x_last_update_date => sysdate,
2903 x_last_updated_by => fnd_global.user_id,
2904 x_last_update_login => fnd_global.login_id);
2905 EXCEPTION
2906 WHEN OTHERS THEN
2907 x_return_status := FND_API.G_RET_STS_ERROR;
2908 RAISE;
2909 END archive_compl_report;
2910
2911 END imc_reports_summary_pkg;