PACKAGE BODY: APPS.HRI_OLTP_CONC_MV

Source

1 PACKAGE BODY hri_oltp_conc_mv  AS
2 /* $Header: hriocmvr.pkb 120.1 2005/12/02 03:27 cbridge noship $ */
3 
4 --
5 g_conc_request_id         NUMBER := fnd_global.conc_request_id;
6 g_schema                VARCHAR2(50);
7 
8 /*
9 **
10 */
11 PROCEDURE refresh_mv_sql
12         (p_mv_name         IN VARCHAR2
13         ,p_mv_refresh_mode IN VARCHAR2)
14 IS
15 
16 BEGIN
17 
18    IF (p_mv_name IS NOT NULL
19       AND p_mv_refresh_mode IN ('C') ) THEN
20         dbms_mview.refresh(p_mv_name, p_mv_refresh_mode);
21    END IF;
22 
23 
24    -- bug 4775190, disable query rewrite so view compiles on 8i database
25    IF p_mv_name = 'HRI_MDP_SUP_WRKFC_JX_MV' THEN
26 
27        EXECUTE IMMEDIATE 'ALTER MATERIALIZED VIEW  HRI_MDP_SUP_WRKFC_JX_MV DISABLE QUERY REWRITE';
28 
29    END IF;
30 
31 EXCEPTION WHEN OTHERS THEN
32    RAISE;
33 
34 END  refresh_mv_sql;
35 
36 -- -----------------------------------------------------------------------------
37 -- Inserts row into concurrent program log
38 -- -----------------------------------------------------------------------------
39 --
40 PROCEDURE output(p_text  VARCHAR2) IS
41   --
42 BEGIN
43   --
44   g_conc_request_id := fnd_global.conc_request_id;
45   --
46   IF g_conc_request_id IS NOT NULL THEN
47     --
48     -- Write to the concurrent request log
49     --
50     fnd_file.put_line(fnd_file.log, p_text);
51     --
52   ELSE
53     --
54     hr_utility.trace(p_text);
55     --
56   END IF;
57   --
58 END output;
59 
60 
61 
62 /*
63 ** Procedure wrapper for concurrent process to call
64 ** dbms_mview.refresh_mv() procedure
65 */
66 PROCEDURE refresh_mv
67         (errbuf          OUT NOCOPY  VARCHAR2
68         ,retcode         OUT NOCOPY VARCHAR2
69         ,p_mv_name         IN VARCHAR2
70         ,p_mv_refresh_mode IN VARCHAR2)
71 IS
72 
73   -- Variables required for table truncation.
74   --
75   l_dummy1        VARCHAR2(2000);
76   l_dummy2        VARCHAR2(2000);
77 
78 BEGIN
79 
80    output('Materialized View:  ' || p_mv_name);
81    output('Refresh Mode     :  ' || p_mv_refresh_mode);
82 
83   -- Find the schema we are running in.
84   --
85   IF NOT fnd_installation.get_app_info('APPS',l_dummy1, l_dummy2, g_schema) THEN
86     --
87     -- Could not find the schema raising exception.
88     --
89     output('Could not find schema to run in.');
90     --
91     RAISE NO_DATA_FOUND;
92     --
93   END IF;
94 
95 
96    IF (p_mv_name IS NOT NULL
97       AND p_mv_refresh_mode IN ('C') ) THEN
98 
99         output('Refreshing the Materialized View:  ' || p_mv_name);
100         output('Start time: ' || to_char(sysdate, 'DD-MON-YYYY HH.MI.SS'));
101 
102         refresh_mv_sql(p_mv_name, p_mv_refresh_mode);
103 
104         output('Gathering table statstics, start: '|| to_char(sysdate, 'DD-MON-YYYY HH.MI.SS'));
105         fnd_stats.gather_table_stats(g_schema, p_mv_name);
106         output('Gathering table statstics, end: '|| to_char(sysdate, 'DD-MON-YYYY HH.MI.SS'));
107 
108         output('End time: ' || to_char(sysdate, 'DD-MON-YYYY HH.MI.SS'));
109 
110    ELSE
111         output('Error: Invalid paramters');
112        errbuf  := 'ERROR';
113        retcode := '2';
114 
115    END IF;
116 
117 EXCEPTION WHEN OTHERS THEN
118   output('EXCEPTION: ' || substr(SQLERRM, 80) );
119   errbuf  := 'ERROR';
120   retcode := '2';
121 
122 END refresh_mv;
123 
124 
125 END HRI_OLTP_CONC_MV;