1 package body BIS_RSG_MVLOG_MGT AS
2 /*$Header: BISSNLMB.pls 120.0 2005/06/01 14:24:20 appldev noship $*/
3 -- dbdrv: sql ~PROD ~PATH ~FILE none none none package &phase=plb \
4 -- dbdrv: checkfile(115.7=120.0):~PROD:~PATH:~FILE
5
6 Procedure create_snp_log(Errbuf in out NOCOPY varchar2,
7 Retcode in out NOCOPY varchar2,
8 p_object_name in varchar2,
9 p_object_type in varchar2,
10 p_called_by in varchar2) is
11 begin
12 null;
13 end;
14
15 /*
16 Procedure create_snp_log(Errbuf in out NOCOPY varchar2,
17 Retcode in out NOCOPY varchar2,
18 p_object_name in varchar2,
19 p_object_type in varchar2,
20 p_called_by in varchar2) is
21 cursor c_sql_stmt is
22 select
23 distinct
24 a.snapshot_log_sql,
25 a.FAST_REFRESH_FLAG,
26 a.object_name
27 from
28 bis_obj_properties a,
29 bis_obj_dependency e
30 where a.object_name=e.depend_object_name
31 and a.object_type=e.depend_object_type
32 and a.object_name=p_object_name
33 and a.object_type=p_object_type
34 and a.SNAPSHOT_LOG_SQL is not null;
35
36 cursor c_log_exist(p_master varchar2) is
37 select 'Y'
38 from dual
39 where exists (SELECT LOG_TABLE
40 from all_snapshot_logs
41 where master = p_master
42 AND log_owner IN (SELECT oracle_username
43 FROM fnd_oracle_userid
44 WHERE oracle_id BETWEEN 900 AND 999
45 AND read_only_flag = 'U'))
46 OR exists (SELECT logs.LOG_TABLE
47 from all_snapshot_logs logs, user_synonyms s
48 where logs.master = s.table_name
49 AND logs.log_owner = s.table_owner
50 AND s.synonym_name = p_master);
51
52
53
54
55 cursor c_mv is
56 SELECT MVIEW_NAME, OWNER
57 FROM all_mviews
58 WHERE MVIEW_NAME=p_object_name
59 AND owner IN (SELECT oracle_username
60 FROM fnd_oracle_userid
61 WHERE oracle_id BETWEEN 900 AND 999
62 AND read_only_flag = 'U')
63 UNION ALL
64 SELECT mview_name, owner
65 FROM all_mviews mvs, user_synonyms s
66 WHERE mvs.owner = s.table_owner
67 AND mvs.mview_name = s.table_name
68 AND mvs.mview_name = p_object_name;
69
70 l_sql_stmt_rec c_sql_stmt%rowtype;
71 l_sql varchar2(4000);
72 l_dummy varchar2(1);
73 l_mv_rec c_mv%rowtype;
74 l_log_created varchar2(1);
75
76 begin
77 if p_called_by='RSG' then
78 for l_sql_stmt_rec in c_sql_stmt loop
79 l_log_created:='N';
80 open c_log_exist(l_sql_stmt_rec.object_name);
81 fetch c_log_exist into l_dummy;
82 if c_log_exist%notfound then
83 write_log('Creating snapshot log because it doesn''t exist');
84 write_log('executing '||l_sql_stmt_rec.snapshot_log_sql);
85 if l_sql_stmt_rec.snapshot_log_sql is not null then
86 execute immediate l_sql_stmt_rec.snapshot_log_sql;
87 commit;
88 l_log_created:='Y';
89 write_log('Created snapshot log for object'||l_sql_stmt_rec.object_name);
90 end if;
91 else
92 write_log('Snapshot log for '||l_sql_stmt_rec.object_name||' already exists. Not going to recreate');
93 end if;---end if c_log_exist not found
94 close c_log_exist;
95
96 ---alter MV only when MV log is created by RSG
97 if p_object_type='MV' and l_sql_stmt_rec.FAST_REFRESH_FLAG='Y' and l_log_created='Y' then
98 for l_mv_rec in c_mv loop
99 l_sql:='alter materialized view '||l_mv_rec.owner||'.'||l_mv_rec.mview_name|| ' refresh fast ';
100 write_log('executing '||l_sql);
101 execute immediate l_sql;
102 commit;
103 write_log('Altered MV '||l_mv_rec.owner||'.'||l_mv_rec.mview_name||' to be fast refresh ');
104 end loop;
105 end if;
106 end loop;----end loop l_sql_stmt
107 */
108 /** The following code is commented out because full_refresh_complete is removed from design
109 l_sql:='update bis_obj_properties set full_refresh_complete=''Y'' where object_name='||''''||p_object_name||''''||' and object_type='||''''||p_object_type||'''';
110 write_log('executing '||l_sql);
111 execute immediate l_sql;
112 commit;
113 write_log('Updated full_refresh_complete flag to ''Y''');
114 **/
115 /*
116 end if;---end if p_called_by='RSG'
117 exception
118 when others then
119 Retcode:='2';
120 Errbuf:=sqlerrm;
121 write_log(sqlcode||' '||sqlerrm);
122 return;
123 end;
124 */
125
126 /** this concurrent program will not be delivered
127 ----full_refresh_complete is removed from design
128 Procedure reset_complete_flag(Errbuf in out NOCOPY varchar2,
129 Retcode in out NOCOPY varchar2,
130 p_set_name in varchar2,
131 p_set_app in varchar2,
132 p_called_by in varchar2) is
133 cursor c_objects is
134 select
135 distinct
136 a.object_name,
137 a.object_type
138 from
139 bis_obj_prog_linkages a,
140 fnd_concurrent_programs b,
141 fnd_request_set_programs c,
142 fnd_request_sets d,
143 fnd_application e
144 where
145 a.CONC_APP_ID=b.application_id
146 and a.CONC_PROGRAM_NAME=b.CONCURRENT_PROGRAM_NAME
147 and b.APPLICATION_ID=c.PROGRAM_APPLICATION_ID
148 and b.CONCURRENT_PROGRAM_ID=c.CONCURRENT_PROGRAM_ID
149 and c.SET_APPLICATION_ID=d.application_id
150 and c.REQUEST_SET_ID=d.REQUEST_SET_ID
151 and d.REQUEST_SET_NAME=p_set_name
152 and d.application_id=e.application_id
153 and e.application_short_name=p_set_app;
154 l_object_rec c_objects%rowtype;
155 l_sql varchar2(4000);
156 begin
157 if p_called_by='RSG' then
158 for l_object_rec in c_objects loop
159 l_sql:='update bis_obj_properties set full_refresh_complete=''N'' where object_name='||''''||l_object_rec.object_name||''''||' and object_type='||''''||l_object_rec.object_type||'''';
160 write_log('executing '||l_sql);
161 execute immediate l_sql;
162 commit;
163 write_log('updated full_refresh_complete for '||l_object_rec.object_name);
164 end loop;
165 end if;
166 exception
167 when others then
168 Retcode:='2';
169 Errbuf:=sqlerrm;
170 write_log(sqlcode||' '||sqlerrm);
171 return;
172 end;
173 **/
174
175 PROCEDURE write_log(p_text in VARCHAR2) is
176 l_len number;
177 l_start number:=1;
178 l_end number:=1;
179 last_reached boolean:=false;
180 BEGIN
181 if p_text is null or p_text='' then
182 return;
183 end if;
184 l_len:=nvl(length(p_text),0);
185 if l_len <=0 then
186 return;
187 end if;
188 while true loop
189 l_end:=l_start+250;
190 if l_end >= l_len then
191 l_end:=l_len;
192 last_reached:=true;
193 end if;
194 FND_FILE.PUT_LINE(FND_FILE.LOG,substr(p_text, l_start, 250));
195 l_start:=l_start+250;
196 if last_reached then
197 exit;
198 end if;
199 end loop;
200 END write_log;
201
202
203
204 END BIS_RSG_MVLOG_MGT;
[Home] [Help]