[Home] [Help]
PACKAGE BODY: APPS.MSD_SRP_SSL_RS
Source
1 package body msd_srp_ssl_rs as
2 /* $Header: msdsrprunrsb.pls 120.3 2011/06/02 08:05:55 mpmurali ship $ */
3
4 procedure run_rs(errbuf out nocopy varchar2,
5 retcode out nocopy number,
6 instance number,
7 file_seperator varchar,
8 control_path varchar2,
9 data_path varchar2,
10 file_name varchar2,
11 auto_run NUMBER DEFAULT 2)
12
13 is
14
15 l_success boolean := false;
16 l_submit_failed exception;
17 l_req_id number;
18
19 CURSOR c_check_ui (p_unique_index_name VARCHAR2)
20 IS
21 select count(1) from ALL_INDEXES
22 where owner = owner
23 and index_name = p_unique_index_name;
24
25 CURSOR c_get_ui (p_unique_index_name VARCHAR2)
26 IS
27 select owner from ALL_INDEXES
28 where owner = owner
29 and index_name = p_unique_index_name;
30
31 C_DR_UI VARCHAR2(50) := 'MSD_DEM_DPT_REP_USG_HIST_U1';
32 C_FS_UI VARCHAR2(50) := 'MSD_DEM_FLD_SER_USG_HIST_U1';
33 C_SR_UI VARCHAR2(50) := 'MSD_DEM_SRP_RET_HIST_U1';
34
35 x_sql VARCHAR2(500):= NULL;
36 x_is_present NUMBER := 0;
37 x_owner VARCHAR2(50) := NULL;
38
39 begin
40
41 IF (instr(file_name, 'SPFUsageHist') = 0) --- SPP Inline Forecasting
42 THEN
43
44 /* 10224184 - Check and drop unique indexes before data load */
45 IF instr(file_name,'DptRepUsgHist')>0 THEN
46
47 msd_dem_common_utilities.log_debug ('Check for unique index MSD_DEM_DPT_REP_USG_HIST_U1');
48 OPEN c_check_ui(C_DR_UI);
49 FETCH c_check_ui INTO x_is_present;
50 CLOSE c_check_ui;
51
52 IF (x_is_present = 1) THEN
53
54 msd_dem_common_utilities.log_debug ('Found unique index MSD_DEM_DPT_REP_USG_HIST_U1');
55
56 OPEN c_get_ui(C_DR_UI);
57 FETCH c_get_ui INTO x_owner;
58 CLOSE c_get_ui;
59
60 x_sql := 'DROP INDEX ' || x_owner || '.' || C_DR_UI;
61 msd_dem_common_utilities.log_debug ('Executing sql - ' || x_sql);
62 EXECUTE IMMEDIATE x_sql;
63 msd_dem_common_utilities.log_message ('Dropped unique index ' || C_DR_UI);
64
65 END IF;
66
67 END IF;
68
69 IF instr(file_name,'FldSerUsgHist')>0 THEN
70
71 msd_dem_common_utilities.log_debug ('Check for unique index MSD_DEM_FLD_SER_USG_HIST_U1');
72 OPEN c_check_ui(C_FS_UI);
73 FETCH c_check_ui INTO x_is_present;
74 CLOSE c_check_ui;
75
76 IF (x_is_present = 1) THEN
77
78 msd_dem_common_utilities.log_debug ('Found unique index MSD_DEM_FLD_SER_USG_HIST_U1');
79
80 OPEN c_get_ui(C_FS_UI);
81 FETCH c_get_ui INTO x_owner;
82 CLOSE c_get_ui;
83
84 x_sql := 'DROP INDEX ' || x_owner || '.' || C_FS_UI;
85 msd_dem_common_utilities.log_debug ('Executing sql - ' || x_sql);
86 EXECUTE IMMEDIATE x_sql;
87 msd_dem_common_utilities.log_message ('Dropped unique index ' || C_FS_UI);
88
89 END IF;
90
91 END IF;
92
93 IF instr(file_name,'SerPartRetHist')>0 THEN
94
95 msd_dem_common_utilities.log_debug ('Check for unique index MSD_DEM_SRP_RET_HIST_U1');
96 OPEN c_check_ui(C_SR_UI);
97 FETCH c_check_ui INTO x_is_present;
98 CLOSE c_check_ui;
99
100 IF (x_is_present = 1) THEN
101
102 msd_dem_common_utilities.log_debug ('Found unique index MSD_DEM_SRP_RET_HIST_U1');
103
104 OPEN c_get_ui(C_SR_UI);
105 FETCH c_get_ui INTO x_owner;
106 CLOSE c_get_ui;
107
108 x_sql := 'DROP INDEX ' || x_owner || '.' || C_SR_UI;
109 msd_dem_common_utilities.log_debug ('Executing sql - ' || x_sql);
110 EXECUTE IMMEDIATE x_sql;
111 msd_dem_common_utilities.log_message ('Dropped unique index ' || C_SR_UI);
112
113 END IF;
114
115 END IF;
116
117 l_success := fnd_submit.set_request_set('MSD','MSDSRPFFLD');
118 if not l_success then
119 raise l_submit_failed;
120 end if;
121
122 if instr(file_name,'InstallBaseHistory') > 0 then
123 l_success := fnd_submit.submit_program('MSD','MSDSRPLD','STAGE10', instance, 1440, file_seperator, control_path, data_path, 3, null,null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null,
124 null, null, null, null, null, null, null, null, null, null,null,1,file_name);
125 if not l_success then
126 raise l_submit_failed;
127 end if;
128 l_success := fnd_submit.submit_program('MSD','MSDSRPPP','STAGE20',instance,1);
129 if not l_success then
130 raise l_submit_failed;
131 end if;
132 end if;
133
134 IF instr(file_name,'FldSerUsgHist')>0 THEN
135 l_success := fnd_submit.submit_program('MSD','MSDSRPLD','STAGE10', instance, 1440, file_seperator, control_path, data_path, 3, null,null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null,
136 null, null, null, null, null, null, null, null, null, null,null,1,null,file_name);
137 if not l_success then
138 raise l_submit_failed;
139 end if;
140 l_success := fnd_submit.submit_program('MSD','MSDSRPPP','STAGE20',instance,2);
141 if not l_success then
142 raise l_submit_failed;
143 end if;
144 end if;
145
146 IF instr(file_name,'DptRepUsgHist')>0 THEN
147 l_success := fnd_submit.submit_program('MSD','MSDSRPLD','STAGE10', instance, 1440, file_seperator, control_path, data_path, 3, null,null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null,
148 null, null, null, null, null, null, null, null, null, null,null,1,null,null,file_name);
149 if not l_success then
150 raise l_submit_failed;
151 end if;
152 l_success := fnd_submit.submit_program('MSD','MSDSRPPP','STAGE20',instance,3);
153 if not l_success then
154 raise l_submit_failed;
155 end if;
156 end if;
157
158 IF instr(file_name,'SerPartRetHist')>0 THEN
159 l_success := fnd_submit.submit_program('MSD','MSDSRPLD','STAGE10', instance, 1440, file_seperator, control_path, data_path, 3, null,null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null,
160 null, null, null, null, null, null, null, null, null, null,null,1,null,null,null,file_name);
161 if not l_success then
162 raise l_submit_failed;
163 end if;
164 l_success := fnd_submit.submit_program('MSD','MSDSRPPP','STAGE20',instance,4);
165 if not l_success then
166 raise l_submit_failed;
167 end if;
168 end if;
169
170 IF instr(file_name,'FailureRates')>0 THEN
171 l_success := fnd_submit.submit_program('MSD','MSDSRPLD','STAGE10', instance, 1440, file_seperator, control_path, data_path, 3, null,null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null,
172 null, null, null, null, null, null, null, null, null, null,null,1,null,null,null,null,file_name);
173 if not l_success then
174 raise l_submit_failed;
175 end if;
176 l_success := fnd_submit.submit_program('MSD','MSDSRPPP','STAGE20',instance,5);
177 if not l_success then
178 raise l_submit_failed;
179 end if;
180 end if;
181
182 IF instr(file_name,'PrdRetHist')>0 THEN
183 l_success := fnd_submit.submit_program('MSD','MSDSRPLD','STAGE10', instance, 1440, file_seperator, control_path, data_path, 3, null,null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null,
184 null, null, null, null, null, null, null, null, null, null,null,1,null,null,null,null,null,file_name);
185 if not l_success then
186 raise l_submit_failed;
187 end if;
188 l_success := fnd_submit.submit_program('MSD','MSDSRPPP','STAGE20',instance,6);
189 if not l_success then
190 raise l_submit_failed;
191 end if;
192 end if;
193
194 IF instr(file_name,'ForecastData')>0 THEN
195 l_success := fnd_submit.submit_program('MSD','MSDSRPLD','STAGE10', instance, 1440, file_seperator, control_path, data_path, 3, null,null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null,
196 null, null, null, null, null, null, null, null, null, null,null,1,null,null,null,null,null,null,file_name);
197 if not l_success then
198 raise l_submit_failed;
199 end if;
200 l_success := fnd_submit.submit_program('MSD','MSDSRPPP','STAGE20',instance,7);
201 if not l_success then
202 raise l_submit_failed;
203 end if;
204
205 end if;
206
207 l_req_id := fnd_submit.submit_set(NULL,FALSE);
208
209 ELSE --- Service Parts Forecasting (Demantra)
210
211 BEGIN
212 l_req_id := null;
213 l_req_id := fnd_request.submit_request('MSD', 'MSDSPFCUF', NULL, NULL, FALSE, instance, file_seperator, control_path,
214 data_path, file_name, auto_run);
215
216 EXCEPTION
217 WHEN OTHERS THEN
218 retcode := -1;
219 errbuf := substr(SQLERRM,1,150);
220 msd_dem_common_utilities.log_message ('msd_srp_ssl_rs.run_rs - ERROR ...' || TO_CHAR(systimestamp, 'DD-MON-YYYY HH24:MI:SS'));
221 msd_dem_common_utilities.log_message ('Error while submitting request for collecting usage history');
222 msd_dem_common_utilities.log_message (errbuf);
223 raise l_submit_failed;
224
225 END;
226
227 END IF;
228
229 commit;
230
231 retcode := 0;
232 exception
233 when l_submit_failed then
234 errbuf := 'Launching Request Set failed for SRP Streams';
235 msd_dem_common_utilities.log_message(errbuf);
236 msd_dem_common_utilities.log_debug(errbuf);
237 retcode := -1;
238 when others then
239 errbuf := substr(SQLERRM,1,150);
240 msd_dem_common_utilities.log_message(errbuf);
241 msd_dem_common_utilities.log_debug(errbuf);
242 retcode := -1;
243
244 end;
245
246 end msd_srp_ssl_rs;
247
248