[Home] [Help]
PACKAGE BODY: APPS.IMC_REPORTS_SUMMARY_PKG
Source
1 PACKAGE BODY imc_reports_summary_pkg AS
2 /* $Header: imcrsumb.pls 120.16.12010000.3 2010/03/02 07:17:19 rgokavar 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 --Bug9281743
1906 --there are 2 rows fetched by internal query (2 table union) and if any of them is > 0,
1907 --then regular insert code needs to be executed
1908 --To implement this, Max of the min_year is fethed and if both MIN and MAX are = 0 then terminating the process.
1909 CURSOR min_max_year_month(l_system_date DATE) IS
1910 SELECT MIN(iy), MIN(im),
1911 TO_NUMBER(to_char(l_system_date, 'YYYY')) xy,
1912 TO_NUMBER(to_char(l_system_date, 'MM')) xm,
1913 MAX(iy)
1914 FROM (
1915 SELECT nvl(to_number(to_char(min(last_update_date), 'YYYY')),0) iy
1916 , nvl(to_number(to_char(min(last_update_date), 'MM')),0) im
1917 FROM HZ_PARTY_INTERFACE
1918 WHERE content_source_type = 'DNB'
1919 AND status = 'P2'
1920 UNION
1921 SELECT nvl(to_number(to_char(min(last_update_date), 'YYYY')),0) iy
1922 , nvl(to_number(to_char(min(last_update_date), 'MM')),0) im
1923 FROM hz_imp_batch_summary
1924 WHERE ORIGINAL_SYSTEM = 'DNB'
1925 AND IMPORT_STATUS <> 'PENDING'
1926 );
1927
1928 CURSOR get_system_month_day(l_system_date DATE) IS
1929 SELECT decode(to_char(l_system_date,'DD'),'01',1,0),
1930 decode(to_char(l_system_date,'MM'),'01',1,'04',1,'07',1,'10',1,0)
1931 FROM dual;
1932
1933 CURSOR get_all_period IS
1934 SELECT add_months(to_date(parent_category,'YYYY-MM'),1)-1
1935 , decode(substrb(parent_category,6,2),'03',1,'06',1,'09',1,'12',1,0)
1936 , decode(parent_category, to_char(sysdate,'YYYY-MM'), 1, 0)
1937 FROM IMC_REPORTS_SUMMARY
1938 WHERE report_name = 'PARTY_ENRICH'
1939 AND report_type = 'M';
1940
1941 CURSOR is_month_record_exist(l_date DATE) IS
1942 SELECT 'X'
1943 FROM IMC_REPORTS_SUMMARY
1944 WHERE report_name = 'PARTY_ENRICH'
1945 AND report_type = 'M'
1946 AND parent_category = to_char(l_date,'YYYY-MM')
1947 AND rownum = 1;
1948
1949 CURSOR is_quarter_record_exist(l_date DATE) IS
1950 SELECT 'X'
1951 FROM IMC_REPORTS_SUMMARY
1952 WHERE report_name = 'PARTY_ENRICH'
1953 AND report_type = 'Q'
1954 AND parent_category = to_char(l_date,'YYYY-')||'Q'||to_char(l_date,'Q')
1955 AND rownum = 1;
1956
1957 l_dummy VARCHAR2(1);
1958 l_min_year NUMBER := 0;
1959 l_min_month NUMBER := 0;
1960 l_max_year NUMBER := 0;
1961 l_max_month NUMBER := 0;
1962 l_system_date DATE;
1963 l_quarter_start NUMBER;
1964 l_month_start NUMBER;
1965 l_parent_cat VARCHAR2(30);
1966 l_return_status VARCHAR2(30);
1967 l_msg_data VARCHAR2(2000);
1968 l_msg_count NUMBER;
1969 l_period_end DATE;
1970 l_last_date NUMBER;
1971 l_month_exist VARCHAR2(1);
1972 l_quarter_exist VARCHAR2(1);
1973 l_max_of_min_year NUMBER := 0;
1974 BEGIN
1975
1976 savepoint get_enrich_count_pvt;
1977
1978 l_system_date := sysdate;
1979
1980 write_log('Start collecting data for enrichment report at: '||l_system_date);
1981
1982 -- check if first time to run this program
1983 OPEN is_party_enrich_exist;
1984 FETCH is_party_enrich_exist INTO l_dummy;
1985 CLOSE is_party_enrich_exist;
1986
1987 IF (l_dummy IS NULL) THEN
1988 -- first time running enrichment report
1989
1990 write_log('Removing data for enrichment report');
1991
1992 DELETE FROM IMC_REPORTS_SUMMARY
1993 WHERE REPORT_NAME = 'PARTY_ENRICH';
1994
1995 --Bug9281743
1996 --Max of the min_year is fetched and if is also zero then only process will be terminated.
1997 OPEN min_max_year_month(l_system_date);
1998 FETCH min_max_year_month INTO l_min_year, l_min_month, l_max_year, l_max_month,l_max_of_min_year;
1999 CLOSE min_max_year_month;
2000
2001 -- Assigning Max of Min Year value to Min Year when Min Year is zero and Max of Min year is not zero.
2002 IF l_min_year = 0 AND l_max_of_min_year <> 0 THEN
2003 l_min_year := l_max_of_min_year;
2004 End if;
2005
2006 IF (l_min_year = 0) AND (l_max_of_min_year = 0) THEN
2007 write_log('No enrichment data exist in party interface table');
2008 ELSE
2009 write_log('Adding month/year combination for enrichment report');
2010
2011 FOR I IN l_min_year..l_max_year LOOP
2012 INSERT INTO IMC_REPORTS_SUMMARY (
2013 REPORT_NAME
2014 ,REPORT_TYPE
2015 ,CATEGORY
2016 ,PARENT_CATEGORY
2017 ,TIME_STAMP )
2018 SELECT
2019 'PARTY_ENRICH'
2020 ,'M'
2021 ,NULL
2022 ,to_char(I)||'-'||lookup_code
2023 ,sysdate
2024 FROM FND_LOOKUP_VALUES
2025 WHERE LOOKUP_TYPE = 'MONTH'
2026 AND ENABLED_FLAG = 'Y'
2027 GROUP BY lookup_code;
2028 END LOOP;
2029
2030 write_log('Removing out of range month/year combination for enrichment report');
2031
2032 -- remove rows that are out of min year-month and max year-month range
2033 DELETE IMC_REPORTS_SUMMARY
2034 WHERE report_name = 'PARTY_ENRICH'
2035 AND (parent_category < to_char(l_min_year)||'-'||lpad(to_char(l_min_month),2,'0')
2036 OR parent_category > to_char(l_max_year)||'-'||lpad(to_char(l_max_month),2,'0'));
2037
2038 -- count the score of number of enriched data
2039 OPEN get_all_period;
2040 LOOP
2041 FETCH get_all_period INTO l_period_end, l_quarter_start, l_last_date;
2042 EXIT WHEN get_all_period%NOTFOUND;
2043 -- if l_period_end is same as l_system_date, then put l_system_date as l_period_end
2044 IF(l_last_date = 1) THEN
2045 update_menrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
2046 ELSE
2047 update_menrich_score(l_period_end, l_return_status, l_msg_count, l_msg_data);
2048 END IF;
2049 IF(l_quarter_start = 1) THEN
2050 IF(l_last_date = 1) THEN
2051 insert_qenrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
2052 ELSE
2053 insert_qenrich_score(l_period_end, l_return_status, l_msg_count, l_msg_data);
2054 END IF;
2055 ELSE
2056 IF((to_number(to_char(l_period_end,'YYYY')) = l_max_year) AND
2057 (to_number(to_char(l_period_end,'MM')) = l_max_month)) THEN
2058 IF(l_last_date = 1) THEN
2059 insert_qenrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
2060 ELSE
2061 insert_qenrich_score(l_period_end, l_return_status, l_msg_count, l_msg_data);
2062 END IF;
2063 END IF;
2064 END IF;
2065 END LOOP;
2066 CLOSE get_all_period;
2067 END IF;
2068
2069 ELSE
2070 /*
2071 OPEN get_system_month_day(l_system_date);
2072 FETCH get_system_month_day INTO l_month_start, l_quarter_start;
2073 CLOSE get_system_month_day;
2074 */
2075 -- check if current month data exist
2076 OPEN is_month_record_exist(l_system_date);
2077 FETCH is_month_record_exist INTO l_month_exist;
2078 CLOSE is_month_record_exist;
2079
2080 -- check if current quarter data exist
2081 OPEN is_quarter_record_exist(l_system_date);
2082 FETCH is_quarter_record_exist INTO l_quarter_exist;
2083 CLOSE is_quarter_record_exist;
2084
2085 -- IF(l_month_start = 1) THEN
2086 IF (l_month_exist IS NULL) THEN
2087 insert_menrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
2088 -- IF(l_quarter_start = 1) THEN
2089 IF (l_quarter_exist IS NULL) THEN
2090 insert_qenrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
2091 ELSE
2092 update_qenrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
2093 END IF;
2094 ELSE
2095 update_menrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
2096 update_qenrich_score(l_system_date, l_return_status, l_msg_count, l_msg_data);
2097 END IF;
2098
2099 END IF;
2100
2101 write_log('Finish collecting data for enrichment report at: '||l_system_date);
2102
2103 EXCEPTION
2104
2105 WHEN FND_API.G_EXC_ERROR THEN
2106 ROLLBACK TO get_enrich_count_pvt;
2107 x_return_status := FND_API.G_RET_STS_ERROR;
2108 FND_MSG_PUB.Count_And_Get(
2109 p_encoded => FND_API.G_FALSE,
2110 p_count => x_msg_count,
2111 p_data => x_msg_data);
2112
2113 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2114 ROLLBACK TO get_enrich_count_pvt;
2115 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2116 FND_MSG_PUB.Count_And_Get(
2117 p_encoded => FND_API.G_FALSE,
2118 p_count => x_msg_count,
2119 p_data => x_msg_data);
2120
2121 WHEN OTHERS THEN
2122 ROLLBACK TO get_enrich_count_pvt;
2123 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2124 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2125 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2126 FND_MSG_PUB.ADD;
2127 FND_MSG_PUB.Count_And_Get(
2128 p_encoded => FND_API.G_FALSE,
2129 p_count => x_msg_count,
2130 p_data => x_msg_data);
2131 write_log('Error: '||sqlerrm);
2132
2133 END get_enrich_count;
2134
2135 PROCEDURE insert_menrich_score (
2136 p_system_date IN DATE,
2137 x_return_status OUT NOCOPY VARCHAR2,
2138 x_msg_count OUT NOCOPY NUMBER,
2139 x_msg_data OUT NOCOPY VARCHAR2
2140 ) IS
2141
2142 l_return_status VARCHAR2(30);
2143 l_party_count NUMBER;
2144 l_enpty_count NUMBER;
2145
2146 BEGIN
2147
2148 savepoint insert_menrich_score_pvt;
2149 l_return_status := FND_API.G_RET_STS_SUCCESS;
2150
2151 write_log('Start inserting monthly data for enrichment report: '||p_system_date);
2152
2153 l_party_count := get_party_count('ORGANIZATION', p_system_date, l_return_status);
2154 l_enpty_count := get_enrich_party_count('ORGANIZATION', p_system_date, l_return_status);
2155
2156 write_log('>> l_party_count: '||l_party_count);
2157 write_log('>> l_enpty_count: '||l_enpty_count);
2158
2159 -- change here (Nishant)
2160 /*
2161 Bug 5593223 : Added DNB Bulk Import statistics to
2162 DNB Online Purchase statistics for Enrichment Report
2163 26-Dec-2006 (Nishant Singhai)
2164 */
2165 /*
2166 INSERT INTO IMC_REPORTS_SUMMARY (
2167 report_name
2168 ,report_type
2169 ,category
2170 ,parent_category
2171 ,org_cnt
2172 ,total_cnt
2173 ,total_pct
2174 ,time_stamp
2175 ) SELECT
2176 'PARTY_ENRICH'
2177 ,'M'
2178 ,NULL
2179 ,to_char(p_system_date,'YYYY-MM')
2180 ,nvl(sum(decode(count(1),0,0,1)),0)
2181 ,l_enpty_count
2182 ,l_party_count
2183 ,p_system_date
2184 FROM HZ_PARTY_INTERFACE
2185 WHERE status = 'P2'
2186 AND to_char(last_update_date,'YYYY-MM') = to_char(p_system_date,'YYYY-MM')
2187 GROUP BY party_id;
2188 */
2189 INSERT INTO IMC_REPORTS_SUMMARY (
2190 report_name
2191 ,report_type
2192 ,category
2193 ,parent_category
2194 ,org_cnt
2195 ,total_cnt
2196 ,total_pct
2197 ,time_stamp
2198 ) SELECT
2199 'PARTY_ENRICH'
2200 ,'M'
2201 ,NULL
2202 ,to_char(p_system_date,'YYYY-MM')
2203 ,SUM(org_enriched_for_period)
2204 ,l_enpty_count
2205 ,l_party_count
2206 ,p_system_date
2207 FROM (
2208 SELECT nvl(sum(decode(count(1),0,0,1)),0) org_enriched_for_period
2209 FROM HZ_PARTY_INTERFACE
2210 WHERE status = 'P2'
2211 AND content_source_type = 'DNB'
2212 AND TO_CHAR(last_update_date,'YYYY-MM')= TO_CHAR(p_system_date,'YYYY-MM')
2213 GROUP BY party_id
2214 UNION ALL
2215 SELECT nvl(SUM(parties_imported),0) org_enriched_for_period
2216 FROM hz_imp_batch_summary
2217 WHERE ORIGINAL_SYSTEM = 'DNB'
2218 AND IMPORT_STATUS <> 'PENDING'
2219 AND TO_CHAR(last_update_date,'YYYY-MM')= TO_CHAR(p_system_date,'YYYY-MM')
2220 ) ;
2221
2222 EXCEPTION
2223
2224 WHEN OTHERS THEN
2225 ROLLBACK TO insert_menrich_score_pvt;
2226 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2227 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2228 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2229 FND_MSG_PUB.ADD;
2230 FND_MSG_PUB.Count_And_Get(
2231 p_encoded => FND_API.G_FALSE,
2232 p_count => x_msg_count,
2233 p_data => x_msg_data);
2234 write_log('Error: '||sqlerrm);
2235
2236 END insert_menrich_score;
2237
2238 PROCEDURE update_menrich_score (
2239 p_system_date IN DATE,
2240 x_return_status OUT NOCOPY VARCHAR2,
2241 x_msg_count OUT NOCOPY NUMBER,
2242 x_msg_data OUT NOCOPY VARCHAR2
2243 ) IS
2244
2245 l_return_status VARCHAR2(30);
2246 l_party_count NUMBER;
2247 l_enpty_count NUMBER;
2248
2249 BEGIN
2250
2251 savepoint update_menrich_score_pvt;
2252 l_return_status := FND_API.G_RET_STS_SUCCESS;
2253
2254 write_log('Start updating monthly data for enrichment report: '||p_system_date);
2255
2256 l_party_count := get_party_count('ORGANIZATION', p_system_date, l_return_status);
2257 l_enpty_count := get_enrich_party_count('ORGANIZATION', p_system_date, l_return_status);
2258
2259 write_log('>> l_party_count: '||l_party_count);
2260 write_log('>> l_enpty_count: '||l_enpty_count);
2261
2262 -- change here (Nishant)
2263 /*
2264 Bug 5593223 : Added DNB Bulk Import statistics to
2265 DNB Online Purchase statistics for Enrichment Report
2266 26-Dec-2006 (Nishant Singhai)
2267 */
2268 /*
2269 UPDATE IMC_REPORTS_SUMMARY
2270 SET (org_cnt, total_cnt,total_pct, time_stamp) =
2271 (SELECT nvl(sum(decode(count(1),0,0,1)),0), l_enpty_count, l_party_count, p_system_date
2272 FROM HZ_PARTY_INTERFACE
2273 WHERE status = 'P2'
2274 AND to_char(last_update_date,'YYYY-MM') = to_char(p_system_date,'YYYY-MM')
2275 GROUP BY party_id)
2276 WHERE report_name = 'PARTY_ENRICH'
2277 AND report_type = 'M'
2278 AND parent_category = to_char(p_system_date,'YYYY-MM');
2279 */
2280
2281 UPDATE IMC_REPORTS_SUMMARY
2282 SET (org_cnt, total_cnt,total_pct, time_stamp) =
2283 (SELECT SUM(org_enriched_for_period), l_enpty_count, l_party_count, p_system_date
2284 FROM (
2285 SELECT nvl(sum(decode(count(1),0,0,1)),0) org_enriched_for_period
2286 FROM HZ_PARTY_INTERFACE
2287 WHERE status = 'P2'
2288 AND content_source_type = 'DNB'
2289 AND TO_CHAR(last_update_date,'YYYY-MM')= TO_CHAR(p_system_date,'YYYY-MM')
2290 GROUP BY party_id
2291 UNION ALL
2292 SELECT nvl(SUM(parties_imported),0) org_enriched_for_period
2293 FROM hz_imp_batch_summary
2294 WHERE ORIGINAL_SYSTEM = 'DNB'
2295 AND IMPORT_STATUS <> 'PENDING'
2296 AND TO_CHAR(last_update_date,'YYYY-MM')= TO_CHAR(p_system_date,'YYYY-MM')
2297 )
2298 )
2299 WHERE report_name = 'PARTY_ENRICH'
2300 AND report_type = 'M'
2301 AND parent_category = to_char(p_system_date,'YYYY-MM');
2302
2303 EXCEPTION
2304
2305 WHEN OTHERS THEN
2306 ROLLBACK TO update_menrich_score_pvt;
2307 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2308 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2309 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2310 FND_MSG_PUB.ADD;
2311 FND_MSG_PUB.Count_And_Get(
2312 p_encoded => FND_API.G_FALSE,
2313 p_count => x_msg_count,
2314 p_data => x_msg_data);
2315 write_log('Error: '||sqlerrm);
2316
2317 END update_menrich_score;
2318
2319 PROCEDURE insert_qenrich_score (
2320 p_system_date IN DATE,
2321 x_return_status OUT NOCOPY VARCHAR2,
2322 x_msg_count OUT NOCOPY NUMBER,
2323 x_msg_data OUT NOCOPY VARCHAR2
2324 ) IS
2325
2326 l_return_status VARCHAR2(30);
2327 l_party_count NUMBER;
2328 l_enpty_count NUMBER;
2329
2330 BEGIN
2331
2332 savepoint insert_qenrich_score_pvt;
2333 l_return_status := FND_API.G_RET_STS_SUCCESS;
2334
2335 write_log('Start inserting quarterly data for enrichment report: '||p_system_date);
2336
2337 l_party_count := get_party_count('ORGANIZATION', p_system_date, l_return_status);
2338 l_enpty_count := get_enrich_party_count('ORGANIZATION', p_system_date, l_return_status);
2339
2340 write_log('>> l_party_count: '||l_party_count);
2341 write_log('>> l_enpty_count: '||l_enpty_count);
2342
2343 -- change here (Nishant)
2344 /*
2345 Bug 5593223 : Added DNB Bulk Import statistics to
2346 DNB Online Purchase statistics for Enrichment Report
2347 26-Dec-2006 (Nishant Singhai)
2348 */
2349 /*
2350 INSERT INTO IMC_REPORTS_SUMMARY (
2351 report_name
2352 ,report_type
2353 ,category
2354 ,parent_category
2355 ,org_cnt
2356 ,total_cnt
2357 ,total_pct
2358 ,time_stamp
2359 ) SELECT
2360 'PARTY_ENRICH'
2361 ,'Q'
2362 ,NULL
2363 ,to_char(p_system_date,'YYYY-')||'Q'||to_char(p_system_date,'Q')
2364 ,nvl(sum(decode(count(1),0,0,1)),0)
2365 ,l_enpty_count
2366 ,l_party_count
2367 ,p_system_date
2368 FROM HZ_PARTY_INTERFACE
2369 WHERE status = 'P2'
2370 AND to_char(last_update_date,'YYYY-Q') = to_char(p_system_date,'YYYY-Q')
2371 GROUP BY party_id;
2372 */
2373 INSERT INTO IMC_REPORTS_SUMMARY (
2374 report_name
2375 ,report_type
2376 ,category
2377 ,parent_category
2378 ,org_cnt
2379 ,total_cnt
2380 ,total_pct
2381 ,time_stamp
2382 ) SELECT
2383 'PARTY_ENRICH'
2384 ,'Q'
2385 ,NULL
2386 ,to_char(p_system_date,'YYYY-')||'Q'||to_char(p_system_date,'Q')
2387 ,SUM(org_enriched_for_period)
2388 ,l_enpty_count
2389 ,l_party_count
2390 ,p_system_date
2391 FROM (
2392 SELECT nvl(SUM(decode(count(1),0,0,1)),0) org_enriched_for_period
2393 FROM HZ_PARTY_INTERFACE
2394 WHERE status = 'P2'
2395 AND content_source_type = 'DNB'
2396 AND to_char(last_update_date,'YYYY-Q') = to_char(p_system_date,'YYYY-Q')
2397 GROUP BY party_id
2398 UNION ALL
2399 SELECT nvl(SUM(parties_imported),0) org_enriched_for_period
2400 FROM hz_imp_batch_summary
2401 WHERE ORIGINAL_SYSTEM = 'DNB'
2402 AND IMPORT_STATUS <> 'PENDING'
2403 AND TO_CHAR(last_update_date,'YYYY-Q')= TO_CHAR(p_system_date,'YYYY-Q')
2404 );
2405
2406 EXCEPTION
2407
2408 WHEN OTHERS THEN
2409 ROLLBACK TO insert_qenrich_score_pvt;
2410 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2411 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2412 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2413 FND_MSG_PUB.ADD;
2414 FND_MSG_PUB.Count_And_Get(
2415 p_encoded => FND_API.G_FALSE,
2416 p_count => x_msg_count,
2417 p_data => x_msg_data);
2418 write_log('Error: '||sqlerrm);
2419
2420 END insert_qenrich_score;
2421
2422 PROCEDURE update_qenrich_score (
2423 p_system_date IN DATE,
2424 x_return_status OUT NOCOPY VARCHAR2,
2425 x_msg_count OUT NOCOPY NUMBER,
2426 x_msg_data OUT NOCOPY VARCHAR2
2427 ) IS
2428
2429 l_return_status VARCHAR2(30);
2430 l_party_count NUMBER;
2431 l_enpty_count NUMBER;
2432
2433 BEGIN
2434
2435 savepoint update_qenrich_score_pvt;
2436 l_return_status := FND_API.G_RET_STS_SUCCESS;
2437
2438 write_log('Start updating quarterly data for enrichment report: '||p_system_date);
2439
2440 l_party_count := get_party_count('ORGANIZATION', p_system_date, l_return_status);
2441 l_enpty_count := get_enrich_party_count('ORGANIZATION', p_system_date, l_return_status);
2442
2443 write_log('>> l_party_count: '||l_party_count);
2444 write_log('>> l_enpty_count: '||l_enpty_count);
2445
2446 -- change here (Nishant)
2447 /*
2448 Bug 5593223 : Added DNB Bulk Import statistics to
2449 DNB Online Purchase statistics for Enrichment Report
2450 26-Dec-2006 (Nishant Singhai)
2451 */
2452 /*
2453 UPDATE IMC_REPORTS_SUMMARY
2454 SET (org_cnt, total_cnt,total_pct, time_stamp) =
2455 (SELECT nvl(sum(decode(count(1),0,0,1)),0), l_enpty_count, l_party_count, p_system_date
2456 FROM HZ_PARTY_INTERFACE
2457 WHERE status = 'P2'
2458 AND to_char(last_update_date,'YYYY-Q') = to_char(p_system_date,'YYYY-Q')
2459 GROUP BY party_id)
2460 WHERE report_name = 'PARTY_ENRICH'
2461 AND report_type = 'Q'
2462 AND parent_category = to_char(p_system_date,'YYYY-')||'Q'||to_char(p_system_date,'Q');
2463 */
2464 UPDATE IMC_REPORTS_SUMMARY
2465 SET (org_cnt, total_cnt,total_pct, time_stamp) =
2466 (SELECT SUM(org_enriched_for_period), l_enpty_count, l_party_count, p_system_date
2467 FROM (
2468 SELECT nvl(sum(decode(count(1),0,0,1)),0) org_enriched_for_period
2469 FROM HZ_PARTY_INTERFACE
2470 WHERE status = 'P2'
2471 AND content_source_type = 'DNB'
2472 AND TO_CHAR(last_update_date,'YYYY-Q')= TO_CHAR(p_system_date,'YYYY-Q')
2473 GROUP BY party_id
2474 UNION ALL
2475 SELECT nvl(SUM(parties_imported),0) org_enriched_for_period
2476 FROM hz_imp_batch_summary
2477 WHERE ORIGINAL_SYSTEM = 'DNB'
2478 AND IMPORT_STATUS <> 'PENDING'
2479 AND TO_CHAR(last_update_date,'YYYY-Q')= TO_CHAR(p_system_date,'YYYY-Q')
2480 )
2481 )
2482 WHERE report_name = 'PARTY_ENRICH'
2483 AND report_type = 'Q'
2484 AND parent_category = to_char(p_system_date,'YYYY-')||'Q'||to_char(p_system_date,'Q');
2485
2486 EXCEPTION
2487
2488 WHEN OTHERS THEN
2489 ROLLBACK TO update_qenrich_score_pvt;
2490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2491 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
2492 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
2493 FND_MSG_PUB.ADD;
2494 FND_MSG_PUB.Count_And_Get(
2495 p_encoded => FND_API.G_FALSE,
2496 p_count => x_msg_count,
2497 p_data => x_msg_data);
2498 write_log('Error: '||sqlerrm);
2499
2500 END update_qenrich_score;
2501
2502 -- get total number of parties
2503 FUNCTION get_party_count(
2504 p_party_type IN VARCHAR2,
2505 p_date IN DATE,
2506 x_return_status IN OUT NOCOPY VARCHAR2
2507 ) RETURN NUMBER IS
2508
2509 -- count ORGANIZATION or PERSON parties
2510 cursor get_pty_count(l_party_type VARCHAR2, l_date DATE) is
2511 SELECT count(1)
2512 FROM HZ_PARTIES
2513 WHERE status in ('A','I')
2514 AND party_type = l_party_type
2515 AND trunc(creation_date) <= trunc(l_date);
2516
2517 -- count CONTACT
2518 cursor get_contact_count(l_date DATE) is
2519 SELECT count(1)
2520 FROM HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r, HZ_PARTIES p
2521 , HZ_CODE_ASSIGNMENTS ca, HZ_RELATIONSHIP_TYPES rt
2522 WHERE oc.party_relationship_id = r.relationship_id
2523 AND r.subject_type = 'PERSON'
2524 AND r.subject_id = p.party_id
2525 AND ca.class_category = 'RELATIONSHIP_TYPE_GROUP'
2526 AND ca.owner_table_name = 'HZ_RELATIONSHIP_TYPES'
2527 AND ca.class_code = 'PARTY_REL_GRP_CONTACTS'
2528 AND rt.relationship_type_id = ca.owner_table_id
2529 AND rt.subject_type = 'PERSON'
2530 AND rt.forward_rel_code = r.relationship_code
2531 AND rt.relationship_type = r.relationship_type
2532 AND p.status in ('A','I')
2533 AND trunc(p.creation_date) <= trunc(l_date);
2534
2535 l_party_count NUMBER;
2536
2537 BEGIN
2538
2539 IF(p_party_type = 'CONTACT') THEN
2540 OPEN get_contact_count(p_date);
2541 FETCH get_contact_count INTO l_party_count;
2542 CLOSE get_contact_count;
2543 ELSE
2544 OPEN get_pty_count(p_party_type, p_date);
2545 FETCH get_pty_count INTO l_party_count;
2546 CLOSE get_pty_count;
2547 END IF;
2548
2549 RETURN l_party_count;
2550
2551 EXCEPTION
2552 WHEN OTHERS THEN
2553 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2554 RETURN NULL;
2555 END get_party_count;
2556
2557 FUNCTION get_enrich_party_count (
2558 p_party_type IN VARCHAR2,
2559 p_date IN DATE,
2560 x_return_status IN OUT NOCOPY VARCHAR2
2561 ) RETURN NUMBER IS
2562
2563 -- change here (Nishant)
2564 /*
2565 Bug 5593223 : Added DNB Bulk Import statistics to
2566 DNB Online Purchase statistics for Enrichment Report
2567 26-Dec-2006 (Nishant Singhai)
2568 */
2569 /*
2570 cursor get_enpty_count(l_party_type VARCHAR2, l_date DATE) is
2571 SELECT nvl(sum(decode(count(1),0,0,1)),0)
2572 FROM HZ_PARTY_INTERFACE
2573 WHERE status = 'P2'
2574 AND content_source_type = 'DNB'
2575 AND trunc(last_update_date) <= trunc(l_date)
2576 GROUP BY party_id;
2577 */
2578
2579 cursor get_enpty_count(l_party_type VARCHAR2, l_date DATE) is
2580 SELECT SUM(total_org_enriched) FROM (
2581 SELECT nvl(sum(decode(count(1),0,0,1)),0) total_org_enriched
2582 FROM HZ_PARTY_INTERFACE
2583 WHERE status = 'P2'
2584 AND content_source_type = 'DNB'
2585 AND TRUNC(last_update_date) <= TRUNC(l_date)
2586 GROUP BY party_id
2587 UNION ALL
2588 SELECT nvl(SUM(parties_imported),0) total_org_enriched
2589 FROM hz_imp_batch_summary
2590 WHERE ORIGINAL_SYSTEM = 'DNB'
2591 AND IMPORT_STATUS <> 'PENDING'
2592 AND TRUNC(last_update_date) <= TRUNC(l_date)
2593 );
2594
2595 l_enpty_count NUMBER;
2596
2597 BEGIN
2598
2599 OPEN get_enpty_count(p_party_type, p_date);
2600 FETCH get_enpty_count INTO l_enpty_count;
2601 CLOSE get_enpty_count;
2602
2603 RETURN l_enpty_count;
2604
2605 EXCEPTION
2606 WHEN OTHERS THEN
2607 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2608 RETURN NULL;
2609 END get_enrich_party_count;
2610
2611 FUNCTION get_party_clause(
2612 p_table_name IN VARCHAR2,
2613 p_party_type IN VARCHAR2,
2614 p_system_date IN DATE,
2615 x_return_status IN OUT NOCOPY VARCHAR2)
2616 RETURN VARCHAR2 IS
2617
2618 str VARCHAR2(4000);
2619
2620 BEGIN
2621
2622 IF(p_party_type = 'CONTACT') THEN
2623 str := ' from HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r, HZ_PARTIES pty'||
2624 ' , HZ_CODE_ASSIGNMENTS ca, HZ_RELATIONSHIP_TYPES rt'||
2625 ' where pty.status in (''A'',''I'')'||
2626 ' and oc.party_relationship_id = r.relationship_id' ||
2627 ' and r.subject_type = ''PERSON'''||
2628 ' and r.party_id = pty.party_id'||
2629 ' and ca.class_category = ''RELATIONSHIP_TYPE_GROUP'''||
2630 ' and ca.owner_table_name = ''HZ_RELATIONSHIP_TYPES'''||
2631 ' and ca.class_code = ''PARTY_REL_GRP_CONTACTS'''||
2632 ' and rt.relationship_type_id = ca.owner_table_id'||
2633 ' and rt.subject_type = ''PERSON'''||
2634 ' and rt.forward_rel_code = r.relationship_code'||
2635 ' and rt.relationship_type = r.relationship_type';
2636 ELSE
2637 str := ' from HZ_PARTIES pty'||
2638 ' where pty.status in (''A'',''I'')'||
2639 ' and pty.party_type = '''||p_party_type||'''';
2640 END IF;
2641
2642 RETURN str;
2643
2644 EXCEPTION
2645 WHEN OTHERS THEN
2646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2647 RETURN NULL;
2648 END get_party_clause;
2649
2650 FUNCTION get_code_assign_clause(
2651 p_table_name IN VARCHAR2,
2652 p_party_type IN VARCHAR2,
2653 p_attribute IN VARCHAR2,
2654 p_system_date IN DATE,
2655 x_return_status IN OUT NOCOPY VARCHAR2)
2656 RETURN VARCHAR2 IS
2657
2658 str VARCHAR2(4000);
2659
2660 BEGIN
2661
2662 -- code assignment is only for organization report
2663 str := ' from (select owner_table_id '||p_attribute||
2664 ' from HZ_CODE_ASSIGNMENTS c'||
2665 ' where owner_table_name = ''HZ_PARTIES'''||
2666 ' and status = ''A'''||
2667 ' and :p_date between c.start_date_active and nvl(c.end_date_active,:p_date)'||
2668 ' group by owner_table_id) ca';
2669
2670 RETURN str;
2671
2672 EXCEPTION
2673 WHEN OTHERS THEN
2674 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2675 RETURN NULL;
2676 END get_code_assign_clause;
2677
2678 FUNCTION get_profile_clause(
2679 p_table_name IN VARCHAR2,
2680 p_party_type IN VARCHAR2,
2681 p_system_date IN DATE,
2682 x_return_status IN OUT NOCOPY VARCHAR2)
2683 RETURN VARCHAR2 IS
2684
2685 str VARCHAR2(4000);
2686
2687 BEGIN
2688
2689 -- contact reports should NOT have profile checking
2690 -- the following if may not be valid now
2691 IF(p_party_type = 'CONTACT') THEN
2692 NULL;
2693 ELSE
2694 str := ' from HZ_PARTIES pty, '||p_table_name||' prof'||
2695 ' where pty.status in (''A'',''I'')'||
2696 ' and pty.party_type = '''||p_party_type||''''||
2697 ' and pty.party_id = prof.party_id'||
2698 ' and :p_date between prof.effective_start_date and nvl(prof.effective_end_date,:p_date)';
2699 END IF;
2700
2701 RETURN str;
2702
2703 EXCEPTION
2704 WHEN OTHERS THEN
2705 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2706 RETURN NULL;
2707 END get_profile_clause;
2708
2709 FUNCTION get_contactpoint_clause(
2710 p_table_name IN VARCHAR2,
2711 p_party_type IN VARCHAR2,
2712 p_attribute IN VARCHAR2,
2713 p_system_date IN DATE,
2714 x_return_status IN OUT NOCOPY VARCHAR2)
2715 RETURN VARCHAR2 IS
2716
2717 str VARCHAR2(4000);
2718 l_contact_point_type VARCHAR2(30);
2719
2720 BEGIN
2721
2722 IF(p_attribute = 'URL') THEN
2723 l_contact_point_type := 'WEB';
2724 ELSIF(p_attribute = 'EMAIL_ADDRESS') THEN
2725 l_contact_point_type := 'EMAIL';
2726 ELSE
2727 l_contact_point_type := 'PHONE';
2728 END IF;
2729
2730 -- if the reports is used for contact, then we need to use HZ_RELATIONSHIPS
2731 -- and HZ_ORG_CONTACTS table to find out that person
2732
2733 IF(p_party_type = 'CONTACT') THEN
2734 str := ' from HZ_PARTIES pty, HZ_CONTACT_POINTS contpt,'||
2735 ' HZ_ORG_CONTACTS oc, HZ_RELATIONSHIPS r,'||
2736 ' HZ_CODE_ASSIGNMENTS ca, HZ_RELATIONSHIP_TYPES rt'||
2737 ' where pty.status in (''A'',''I'')'||
2738 ' and oc.party_relationship_id = r.relationship_id' ||
2739 ' and r.subject_type = ''PERSON'''||
2740 ' and r.party_id = pty.party_id'||
2741 ' and ca.class_category = ''RELATIONSHIP_TYPE_GROUP'''||
2742 ' and ca.owner_table_name = ''HZ_RELATIONSHIP_TYPES'''||
2743 ' and ca.class_code = ''PARTY_REL_GRP_CONTACTS'''||
2744 ' and rt.relationship_type_id = ca.owner_table_id'||
2745 ' and rt.subject_type = ''PERSON'''||
2746 ' and rt.forward_rel_code = r.relationship_code'||
2747 ' and rt.relationship_type = r.relationship_type'||
2748 ' and pty.party_id = contpt.owner_table_id'||
2749 ' and contpt.owner_table_name = ''HZ_PARTIES'''||
2750 ' and contpt.status in (''A'',''I'')'||
2751 ' and contpt.contact_point_type = '''||l_contact_point_type||''''||
2752 ' and contpt.primary_flag = ''Y''';
2753 ELSE
2754 str := ' from HZ_PARTIES pty, HZ_CONTACT_POINTS contpt'||
2755 ' where pty.status in (''A'',''I'')'||
2756 ' and pty.party_type = '''||p_party_type||''''||
2757 ' and pty.party_id = contpt.owner_table_id'||
2758 ' and contpt.owner_table_name = ''HZ_PARTIES'''||
2759 ' and contpt.status in (''A'',''I'')'||
2760 ' and contpt.contact_point_type = '''||l_contact_point_type||''''||
2761 ' and contpt.primary_flag = ''Y''';
2762 END IF;
2763
2764 RETURN str;
2765
2766 EXCEPTION
2767 WHEN OTHERS THEN
2768 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2769 RETURN NULL;
2770 END get_contactpoint_clause;
2771
2772 FUNCTION get_org_contact_clause(
2773 p_table_name IN VARCHAR2,
2774 p_party_type IN VARCHAR2,
2775 p_system_date IN DATE,
2776 x_return_status IN OUT NOCOPY VARCHAR2)
2777 RETURN VARCHAR2 IS
2778
2779 str VARCHAR2(4000);
2780
2781 BEGIN
2782
2783 -- org_contact is only used for contact report
2784 str := ' from HZ_ORG_CONTACTS orgcnt, HZ_RELATIONSHIPS r, HZ_PARTIES pty'||
2785 ' , HZ_CODE_ASSIGNMENTS ca, HZ_RELATIONSHIP_TYPES rt'||
2786 ' where pty.status in (''A'',''I'')'||
2787 ' and orgcnt.party_relationship_id = r.relationship_id' ||
2788 ' and r.subject_type = ''PERSON'''||
2789 ' and r.party_id = pty.party_id'||
2790 ' and ca.class_category = ''RELATIONSHIP_TYPE_GROUP'''||
2791 ' and ca.owner_table_name = ''HZ_RELATIONSHIP_TYPES'''||
2792 ' and ca.class_code = ''PARTY_REL_GRP_CONTACTS'''||
2793 ' and rt.relationship_type_id = ca.owner_table_id'||
2794 ' and rt.subject_type = ''PERSON'''||
2795 ' and rt.forward_rel_code = r.relationship_code'||
2796 ' and rt.relationship_type = r.relationship_type';
2797
2798 RETURN str;
2799
2800 EXCEPTION
2801 WHEN OTHERS THEN
2802 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2803 RETURN NULL;
2804 END get_org_contact_clause;
2805
2806 FUNCTION get_org_contact_role_clause(
2807 p_table_name IN VARCHAR2,
2808 p_party_type IN VARCHAR2,
2809 p_attribute IN VARCHAR2,
2810 p_system_date IN DATE,
2811 x_return_status IN OUT NOCOPY VARCHAR2)
2812 RETURN VARCHAR2 IS
2813
2814 str VARCHAR2(4000);
2815
2816 BEGIN
2817
2818 str := ' from (select r.party_id '||p_attribute||
2819 ' from HZ_ORG_CONTACTS orgcnt, HZ_RELATIONSHIPS r, HZ_PARTIES pty'||
2820 ' , HZ_CODE_ASSIGNMENTS ca, HZ_RELATIONSHIP_TYPES rt'||
2821 ' where pty.status in (''A'',''I'')'||
2822 ' and orgcnt.party_relationship_id = r.relationship_id' ||
2823 ' and r.subject_type = ''PERSON'''||
2824 ' and r.party_id = pty.party_id'||
2825 ' and ca.class_category = ''RELATIONSHIP_TYPE_GROUP'''||
2826 ' and ca.owner_table_name = ''HZ_RELATIONSHIP_TYPES'''||
2827 ' and ca.class_code = ''PARTY_REL_GRP_CONTACTS'''||
2828 ' and rt.relationship_type_id = ca.owner_table_id'||
2829 ' and rt.subject_type = ''PERSON'''||
2830 ' and rt.forward_rel_code = r.relationship_code'||
2831 ' and rt.relationship_type = r.relationship_type'||
2832 ' and exists (select 1 from HZ_ORG_CONTACT_ROLES ocr'||
2833 ' where ocr.status = ''A'''||
2834 ' and ocr.org_contact_id = orgcnt.org_contact_id)) ocrole';
2835
2836 RETURN str;
2837
2838 EXCEPTION
2839 WHEN OTHERS THEN
2840 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2841 RETURN NULL;
2842 END get_org_contact_role_clause;
2843
2844 -- main PROCEDURE that collect data for quality reports
2845
2846 PROCEDURE extract_quality IS
2847
2848 l_return_status VARCHAR2(30);
2849 l_msg_count NUMBER;
2850 l_msg_data VARCHAR2(2000);
2851
2852 BEGIN
2853
2854 g_proc_name := 'extract_quality';
2855
2856 get_compl_count(l_return_status, l_msg_count, l_msg_data);
2857 get_enrich_count(l_return_status, l_msg_count, l_msg_data);
2858
2859 EXCEPTION
2860 WHEN OTHERS THEN
2861 write_log('Error:' || sqlerrm);
2862
2863 END extract_quality;
2864
2865 PROCEDURE archive_compl_report (
2866 p_report_code IN VARCHAR2,
2867 x_return_status OUT NOCOPY VARCHAR2
2868 ) IS
2869
2870 CURSOR get_meaning IS
2871 SELECT meaning, description, start_date_active
2872 FROM IMC_LOOKUPS
2873 WHERE lookup_type = 'COMPLETENESS_REPORTS'
2874 AND lookup_code = p_report_code;
2875
2876 l_mean VARCHAR2(80);
2877 l_desc VARCHAR2(240);
2878 l_start_date DATE;
2879 BEGIN
2880 x_return_status := FND_API.G_RET_STS_SUCCESS;
2881
2882 OPEN get_meaning;
2883 FETCh get_meaning INTO l_mean, l_desc, l_start_date;
2884 CLOSE get_meaning;
2885
2886 FND_LOOKUP_VALUES_PKG.update_row(
2887 x_lookup_type => 'COMPLETENESS_REPORTS',
2888 x_security_group_id => NULL,
2889 x_view_application_id => 879,
2890 x_lookup_code => p_report_code,
2891 x_tag => NULL,
2892 x_attribute_category => NULL,
2893 x_attribute1 => NULL,
2894 x_attribute2 => NULL,
2895 x_attribute3 => NULL,
2896 x_attribute4 => NULL,
2897 x_enabled_flag => 'Y',
2898 x_start_date_active => l_start_date,
2899 x_end_date_active => sysdate,
2900 x_territory_code => NULL,
2901 x_attribute5 => NULL,
2902 x_attribute6 => NULL,
2903 x_attribute7 => NULL,
2904 x_attribute8 => NULL,
2905 x_attribute9 => NULL,
2906 x_attribute10 => NULL,
2907 x_attribute11 => NULL,
2908 x_attribute12 => NULL,
2909 x_attribute13 => NULL,
2910 x_attribute14 => NULL,
2911 x_attribute15 => NULL,
2912 x_meaning => l_mean,
2913 x_description => l_desc,
2914 x_last_update_date => sysdate,
2915 x_last_updated_by => fnd_global.user_id,
2916 x_last_update_login => fnd_global.login_id);
2917 EXCEPTION
2918 WHEN OTHERS THEN
2919 x_return_status := FND_API.G_RET_STS_ERROR;
2920 RAISE;
2921 END archive_compl_report;
2922
2923 END imc_reports_summary_pkg;