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.16.12010000.3 2010/03/02 07:17:19 rgokavar 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.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

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 1937: FROM IMC_REPORTS_SUMMARY

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

Line 1943: FROM IMC_REPORTS_SUMMARY

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;

Line 1951: FROM IMC_REPORTS_SUMMARY

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;

Line 1992: DELETE FROM IMC_REPORTS_SUMMARY

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.

Line 2012: INSERT INTO IMC_REPORTS_SUMMARY (

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

Line 2033: DELETE IMC_REPORTS_SUMMARY

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:

Line 2166: INSERT INTO IMC_REPORTS_SUMMARY (

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

Line 2189: INSERT INTO IMC_REPORTS_SUMMARY (

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

Line 2269: UPDATE IMC_REPORTS_SUMMARY

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'

Line 2281: UPDATE IMC_REPORTS_SUMMARY

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

Line 2350: INSERT INTO IMC_REPORTS_SUMMARY (

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

Line 2373: INSERT INTO IMC_REPORTS_SUMMARY (

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

Line 2453: UPDATE IMC_REPORTS_SUMMARY

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'

Line 2464: UPDATE IMC_REPORTS_SUMMARY

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

Line 2923: END imc_reports_summary_pkg;

2919: x_return_status := FND_API.G_RET_STS_ERROR;
2920: RAISE;
2921: END archive_compl_report;
2922:
2923: END imc_reports_summary_pkg;