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_SUMMARY

Line 1: PACKAGE BODY imc_reports_summary_pkg AS

1: PACKAGE BODY imc_reports_summary_pkg AS
2: /* $Header: imcrsumb.pls 120.10.12000000.4 2007/10/19 12:12:20 nshinde ship $ */
3: -- IMC reports summary extraction program
4:
5: --- declarations

Line 3: -- IMC reports summary extraction program

1: PACKAGE BODY imc_reports_summary_pkg AS
2: /* $Header: imcrsumb.pls 120.10.12000000.4 2007/10/19 12:12:20 nshinde ship $ */
3: -- IMC reports summary extraction program
4:
5: --- declarations
6:
7: -- g_proc_name is set to the procedure name in a procedure

Line 1243: FROM imc_lookups rpt, imc_reports_summary rs

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'

Line 1270: -- check if there is any completeness reports in IMC reports summary table

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

Line 1273: FROM IMC_REPORTS_SUMMARY a, IMC_LOOKUPS b

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;

Line 1282: FROM IMC_REPORTS_SUMMARY

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;

Line 1291: FROM IMC_REPORTS_SUMMARY

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;

Line 1300: FROM IMC_REPORTS_SUMMARY

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;

Line 1523: str := 'delete from imc_reports_summary '||

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:

Line 1612: str := 'insert into imc_reports_summary('||

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

Line 1672: insert into IMC_REPORTS_SUMMARY (

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,

Line 1692: from IMC_REPORTS_SUMMARY

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:

Line 1735: update IMC_REPORTS_SUMMARY

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

Line 1738: from IMC_REPORTS_SUMMARY

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),

Line 1787: insert into IMC_REPORTS_SUMMARY (

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,

Line 1805: from IMC_REPORTS_SUMMARY

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:

Line 1848: update IMC_REPORTS_SUMMARY

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

Line 1851: from IMC_REPORTS_SUMMARY

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

Line 1885: FROM IMC_REPORTS_SUMMARY

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)

Line 1932: FROM IMC_REPORTS_SUMMARY

1928: CURSOR get_all_period IS
1929: SELECT add_months(to_date(parent_category,'YYYY-MM'),1)-1
1930: , decode(substrb(parent_category,6,2),'03',1,'06',1,'09',1,'12',1,0)
1931: , decode(parent_category, to_char(sysdate,'YYYY-MM'), 1, 0)
1932: FROM IMC_REPORTS_SUMMARY
1933: WHERE report_name = 'PARTY_ENRICH'
1934: AND report_type = 'M';
1935:
1936: CURSOR is_month_record_exist(l_date DATE) IS

Line 1938: FROM IMC_REPORTS_SUMMARY

1934: AND report_type = 'M';
1935:
1936: CURSOR is_month_record_exist(l_date DATE) IS
1937: SELECT 'X'
1938: FROM IMC_REPORTS_SUMMARY
1939: WHERE report_name = 'PARTY_ENRICH'
1940: AND report_type = 'M'
1941: AND parent_category = to_char(l_date,'YYYY-MM')
1942: AND rownum = 1;

Line 1946: FROM IMC_REPORTS_SUMMARY

1942: AND rownum = 1;
1943:
1944: CURSOR is_quarter_record_exist(l_date DATE) IS
1945: SELECT 'X'
1946: FROM IMC_REPORTS_SUMMARY
1947: WHERE report_name = 'PARTY_ENRICH'
1948: AND report_type = 'Q'
1949: AND parent_category = to_char(l_date,'YYYY-')||'Q'||to_char(l_date,'Q')
1950: AND rownum = 1;

Line 1987: DELETE FROM IMC_REPORTS_SUMMARY

1983: -- first time running enrichment report
1984:
1985: write_log('Removing data for enrichment report');
1986:
1987: DELETE FROM IMC_REPORTS_SUMMARY
1988: WHERE REPORT_NAME = 'PARTY_ENRICH';
1989:
1990: OPEN min_max_year_month(l_system_date);
1991: FETCH min_max_year_month INTO l_min_year, l_min_month, l_max_year, l_max_month;

Line 2000: INSERT INTO IMC_REPORTS_SUMMARY (

1996: ELSE
1997: write_log('Adding month/year combination for enrichment report');
1998:
1999: FOR I IN l_min_year..l_max_year LOOP
2000: INSERT INTO IMC_REPORTS_SUMMARY (
2001: REPORT_NAME
2002: ,REPORT_TYPE
2003: ,CATEGORY
2004: ,PARENT_CATEGORY

Line 2021: DELETE IMC_REPORTS_SUMMARY

2017:
2018: write_log('Removing out of range month/year combination for enrichment report');
2019:
2020: -- remove rows that are out of min year-month and max year-month range
2021: DELETE IMC_REPORTS_SUMMARY
2022: WHERE report_name = 'PARTY_ENRICH'
2023: AND (parent_category < to_char(l_min_year)||'-'||lpad(to_char(l_min_month),2,'0')
2024: OR parent_category > to_char(l_max_year)||'-'||lpad(to_char(l_max_month),2,'0'));
2025:

Line 2154: INSERT INTO IMC_REPORTS_SUMMARY (

2150: DNB Online Purchase statistics for Enrichment Report
2151: 26-Dec-2006 (Nishant Singhai)
2152: */
2153: /*
2154: INSERT INTO IMC_REPORTS_SUMMARY (
2155: report_name
2156: ,report_type
2157: ,category
2158: ,parent_category

Line 2177: INSERT INTO IMC_REPORTS_SUMMARY (

2173: WHERE status = 'P2'
2174: AND to_char(last_update_date,'YYYY-MM') = to_char(p_system_date,'YYYY-MM')
2175: GROUP BY party_id;
2176: */
2177: INSERT INTO IMC_REPORTS_SUMMARY (
2178: report_name
2179: ,report_type
2180: ,category
2181: ,parent_category

Line 2257: UPDATE IMC_REPORTS_SUMMARY

2253: DNB Online Purchase statistics for Enrichment Report
2254: 26-Dec-2006 (Nishant Singhai)
2255: */
2256: /*
2257: UPDATE IMC_REPORTS_SUMMARY
2258: SET (org_cnt, total_cnt,total_pct, time_stamp) =
2259: (SELECT nvl(sum(decode(count(1),0,0,1)),0), l_enpty_count, l_party_count, p_system_date
2260: FROM HZ_PARTY_INTERFACE
2261: WHERE status = 'P2'

Line 2269: UPDATE IMC_REPORTS_SUMMARY

2265: AND report_type = 'M'
2266: AND parent_category = to_char(p_system_date,'YYYY-MM');
2267: */
2268:
2269: UPDATE IMC_REPORTS_SUMMARY
2270: SET (org_cnt, total_cnt,total_pct, time_stamp) =
2271: (SELECT SUM(org_enriched_for_period), l_enpty_count, l_party_count, p_system_date
2272: FROM (
2273: SELECT nvl(sum(decode(count(1),0,0,1)),0) org_enriched_for_period

Line 2338: INSERT INTO IMC_REPORTS_SUMMARY (

2334: DNB Online Purchase statistics for Enrichment Report
2335: 26-Dec-2006 (Nishant Singhai)
2336: */
2337: /*
2338: INSERT INTO IMC_REPORTS_SUMMARY (
2339: report_name
2340: ,report_type
2341: ,category
2342: ,parent_category

Line 2361: INSERT INTO IMC_REPORTS_SUMMARY (

2357: WHERE status = 'P2'
2358: AND to_char(last_update_date,'YYYY-Q') = to_char(p_system_date,'YYYY-Q')
2359: GROUP BY party_id;
2360: */
2361: INSERT INTO IMC_REPORTS_SUMMARY (
2362: report_name
2363: ,report_type
2364: ,category
2365: ,parent_category

Line 2441: UPDATE IMC_REPORTS_SUMMARY

2437: DNB Online Purchase statistics for Enrichment Report
2438: 26-Dec-2006 (Nishant Singhai)
2439: */
2440: /*
2441: UPDATE IMC_REPORTS_SUMMARY
2442: SET (org_cnt, total_cnt,total_pct, time_stamp) =
2443: (SELECT nvl(sum(decode(count(1),0,0,1)),0), l_enpty_count, l_party_count, p_system_date
2444: FROM HZ_PARTY_INTERFACE
2445: WHERE status = 'P2'

Line 2452: UPDATE IMC_REPORTS_SUMMARY

2448: WHERE report_name = 'PARTY_ENRICH'
2449: AND report_type = 'Q'
2450: AND parent_category = to_char(p_system_date,'YYYY-')||'Q'||to_char(p_system_date,'Q');
2451: */
2452: UPDATE IMC_REPORTS_SUMMARY
2453: SET (org_cnt, total_cnt,total_pct, time_stamp) =
2454: (SELECT SUM(org_enriched_for_period), l_enpty_count, l_party_count, p_system_date
2455: FROM (
2456: SELECT nvl(sum(decode(count(1),0,0,1)),0) org_enriched_for_period

Line 2911: END imc_reports_summary_pkg;

2907: x_return_status := FND_API.G_RET_STS_ERROR;
2908: RAISE;
2909: END archive_compl_report;
2910:
2911: END imc_reports_summary_pkg;