eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IMC_REPORTS_SUMMARY_PKG dependencies on IMC_REPORTS_TEMPINFO

Line 130: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 167: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 178: FROM imc_reports_tempinfo

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

Line 186: UPDATE imc_reports_tempinfo

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:

Line 199: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 211: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 221: FROM imc_reports_tempinfo

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)

Line 234: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 244: FROM imc_reports_tempinfo imc_tmp

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;

Line 280: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 307: FROM IMC_REPORTS_TEMPINFO

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

Line 312: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 327: UPDATE imc_reports_tempinfo

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';

Line 335: UPDATE imc_reports_tempinfo

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:

Line 342: UPDATE imc_reports_tempinfo

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:

Line 351: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 366: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 376: FROM imc_reports_tempinfo

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)

Line 385: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 395: FROM imc_reports_tempinfo

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)

Line 408: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 422: FROM imc_reports_tempinfo imc_tmp

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

Line 428: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 441: FROM imc_reports_tempinfo imc_tmp

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

Line 447: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 460: FROM imc_reports_tempinfo imc_tmp

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;

Line 482: FROM imc_reports_tempinfo

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;

Line 494: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 520: UPDATE imc_reports_tempinfo

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'

Line 529: UPDATE imc_reports_tempinfo

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;

Line 537: UPDATE imc_reports_tempinfo

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;

Line 547: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 557: FROM imc_reports_tempinfo

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)

Line 568: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 578: FROM imc_reports_tempinfo

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

Line 591: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 609: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 624: FROM imc_reports_tempinfo imc_tmp

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:

Line 631: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 644: FROM imc_reports_tempinfo imc_tmp

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:

Line 651: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 664: FROM imc_reports_tempinfo imc_tmp

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:

Line 696: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 731: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 776: FROM imc_reports_tempinfo

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

Line 783: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 796: WHERE NOT EXISTS (SELECT '1' FROM imc_reports_tempinfo tmp

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:

Line 806: UPDATE imc_reports_tempinfo

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';

Line 814: UPDATE imc_reports_tempinfo

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';

Line 822: UPDATE imc_reports_tempinfo

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';

Line 832: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 862: FROM IMC_REPORTS_TEMPINFO

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:

Line 878: Query IMC_REPORTS_TEMPINFO to find out which month did not write into

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,

Line 881: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 907: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 963: -- fetch all months and month_nos which have been created into IMC_REPORTS_TEMPINFO

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;

Line 977: INSERT INTO imc_reports_tempinfo(report_name, report_type, category, parent_category,

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;

Line 986: INSERT INTO imc_reports_tempinfo(report_name,

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,

Line 1003: FROM imc_reports_tempinfo a, imc_reports_tempinfo d

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)

Line 1015: DELETE imc_reports_tempinfo

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:

Line 1023: UPDATE imc_reports_tempinfo

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)

Line 1033: UPDATE imc_reports_tempinfo

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)

Line 1043: UPDATE imc_reports_tempinfo

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)

Line 1053: UPDATE imc_reports_tempinfo

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)

Line 1063: UPDATE imc_reports_tempinfo

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)

Line 1073: UPDATE imc_reports_tempinfo

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)

Line 1083: UPDATE imc_reports_tempinfo

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)

Line 1095: INSERT INTO imc_reports_tempinfo(report_name,

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,