[Home] [Help]
PACKAGE BODY: APPS.MST_LAUNCH_PLAN_PKG
Source
1 PACKAGE BODY MST_LAUNCH_PLAN_PKG AS
2 /* $Header: MSTPLAPB.pls 120.0 2005/05/26 17:44:13 appldev noship $ */
3
4 PROCEDURE mst_launch_plan (
5 errbuf OUT NOCOPY VARCHAR2,
6 retcode OUT NOCOPY NUMBER,
7 arg_plan_id IN NUMBER,
8 arg_reuse_files IN NUMBER,
9 arg_audit_mode IN NUMBER ,
10 arg_launch_planner IN NUMBER,
11 arg_plan_start_date IN VARCHAR2 ,
12 arg_plan_cutoff_date IN VARCHAR2,
13 arg_netchange_mode IN NUMBER DEFAULT NULL
14 )
15 IS
16 var_snapshot_req_id INTEGER;
17 var_audit_req_id INTEGER;
18 var_planner_req_id INTEGER;
19 var_user_id INTEGER;
20 v_plan_id NUMBER;
21 v_desig_id NUMBER;
22 v_completion_date date;
23 l_platform_type NUMBER := 0;
24 l_call_status boolean;
25 l_phase varchar2(80);
26 l_status varchar2(80);
27 l_dev_phase varchar2(80);
28 l_dev_status varchar2(80);
29 l_message varchar2(2048);
30 l_industry VARCHAR2(30);
31 l_schema VARCHAR2(30);
32 v_lookup_name varchar2(100);
33 v_req_data number;
34 v_ex_error_plan_launch EXCEPTION;
35 lLaunchPlanner NUMBER ;
36 lNetchange NUMBER ;
37 lAuditMode NUMBER ;
38 lReUseFiles NUMBER ;
39 lStartDate VARCHAR2(30) ;
40 lCutoffDate VARCHAR2(30) ;
41 CURSOR C1(p_plan_id in number) IS
42 SELECT
43 Plan_Type, request_id, plan_completion_date,
44 compile_designator,audit_request_id
45 FROM mst_plans
46 WHERE plan_id = p_plan_id;
47
48 v_rec_c1 c1%rowtype;
49
50 BEGIN
51 lLaunchPlanner := arg_launch_planner;
52 lNetchange := arg_netchange_mode;
53 lAuditMode := arg_audit_mode;
54 lReUseFiles := arg_reuse_files;
55 lStartDate := arg_plan_start_date;
56 lCutoffDate := arg_plan_cutoff_date;
57
58 v_plan_id := arg_plan_id;
59
60
61 v_req_data := fnd_conc_global.request_data;
62 IF v_req_data is null then
63 open c1(v_plan_id);
64 fetch c1 into v_rec_c1;
65 Close c1;
66
67 IF ( v_rec_c1.request_id is not null ) Then
68 -- -------------------------------------
69 -- Check if previous plan output exists.
70 -- if existing, check for the status of
71 -- of the plan output.
72 -- -------------------------------------
73 l_call_status:= FND_CONCURRENT.GET_REQUEST_STATUS
74 ( v_rec_c1.request_id,
75 NULL,
76 NULL,
77 l_phase,
78 l_status,
79 l_dev_phase,
80 l_dev_status,
81 l_message);
82
83 IF v_rec_c1.plan_completion_date is not null then
84 IF upper(l_dev_phase) <>'COMPLETE' then
85 v_lookup_name := 'MST_POST_PROCESSING_RUNNING';
86 raise v_ex_error_plan_launch;
87 END IF;
88 ELSE
89 IF upper(l_dev_phase) <>'COMPLETE' then
90 v_lookup_name := 'MST_PLAN_RUNNING';
91 raise v_ex_error_plan_launch;
92 END IF;
93 END IF;
94
95 END IF; --v_rec_c1.request_id
96
97 IF ( v_rec_c1.audit_request_id is not null ) Then
98 -- -------------------------------------
99 -- check for the status of
100 -- of the audit request
101 -- -------------------------------------
102 l_call_status:= FND_CONCURRENT.GET_REQUEST_STATUS
103 ( v_rec_c1.audit_request_id,
104 NULL,
105 NULL,
106 l_phase,
107 l_status,
108 l_dev_phase,
109 l_dev_status,
110 l_message);
111
112 IF upper(l_dev_phase) <> 'COMPLETE' then
113 v_lookup_name := 'MST_AUDIT_RUNNING';
114 raise v_ex_error_plan_launch;
115 END IF;
116
117 END IF; --v_rec_c1.audit_request_id
118
119 END IF;
120
121 -- if net change snapshot has to be launched
122 IF arg_netchange_mode = SYS_YES THEN
123 lReUseFiles := SYS_NO;
124 ELSE
125 lNetchange := SYS_NO;
126
127 END IF;
128
129
130
131 var_snapshot_req_id := NULL;
132
133 var_snapshot_req_id := FND_REQUEST.SUBMIT_REQUEST(
134 'MST', -- application
135 'MSTSNPMPRC', -- program
136 NULL, -- description
137 NULL, -- start time
138 FALSE, -- sub_request
139 v_plan_id,
140 lStartDate, -- plan start date
141 lCutoffDate, -- plan cutoff date
142 lLaunchPlanner,
143 0, -- snapshot_worker
144 0, -- monitor_pipe
145 0, -- monitor_request_id
146 lNetchange, -- Netchange_mode
147 lReUseFiles, -- Reuse set up files
148 lAuditMode -- audit mode
149 );
150
151 UPDATE mst_plans
152 SET request_id = var_snapshot_req_id
153 WHERE plan_id = v_plan_id;
154
155 COMMIT;
156 MSC_UTIL.msc_Debug('Launched Snapshot:'||
157 to_char(var_snapshot_req_id));
158
159 retcode := 0;
160 errbuf := NULL;
161 return;
162
163 EXCEPTION
164 when v_ex_error_plan_launch then
165 retcode := 2;
166 if v_lookup_name is not null then
167 fnd_message.set_name('MST',v_lookup_name);
168 fnd_message.set_token('PLAN_NAME',v_rec_c1.compile_designator);
169 errbuf := fnd_message.get;
170 end if;
171 when OTHERS THEN
172 retcode := 2;
173 errbuf := sqlerrm;
174 END mst_launch_plan;
175
176
177 PROCEDURE refresh_snapshot(
178 ERRBUF OUT NOCOPY VARCHAR2,
179 RETCODE OUT NOCOPY NUMBER,
180 pSNAPName in VARCHAR2,
181 pDEGREE in NUMBER DEFAULT 0)
182 IS
183 lv_DEGREE NUMBER;
184 lv_base_table_name VARCHAR2(30);
185 lv_snap_schema VARCHAR2(30);
186 lv_snapshot_str VARCHAR2(2000); -- list of snapshots to be refreshed
187 lv_refresh_param VARCHAR2(10);
188 lv_setup_source_objs NUMBER;
189
190 l_retval boolean;
191 l_dummy1 varchar2(32);
192 l_dummy2 varchar2(32);
193 l_wsh_schema varchar2(32);
194
195 SOURCE_SETUP_ERROR EXCEPTION;
196
197 BEGIN
198 lv_DEGREE := nvl(pDEGREE,0);
199 lv_DEGREE := LEAST(lv_DEGREE,10);
200
201 RETCODE := G_ERROR;
202
203 IF FOUND_EXIST_OBJECTS = FALSE THEN
204 lv_setup_source_objs := 1;
205 ELSE
206 SELECT DECODE(NVL(fnd_profile.value('MST_SOURCE_SETUP') ,'1'), '1',1 ,2)
207 INTO lv_setup_source_objs
208 FROM DUAL;
209 END IF;
210
211 IF (lv_setup_source_objs = 1) THEN
212 IF SETUP_SOURCE_OBJECTS = FALSE THEN
213 RAISE SOURCE_SETUP_ERROR;
214 END IF;
215
216 ELSE
217
218 l_retval := FND_INSTALLATION.GET_APP_INFO
219 ( 'WSH',
220 l_dummy1,
221 l_dummy2,
222 l_wsh_schema);
223
224 SELECT owner,master
225 INTO lv_snap_schema,lv_base_table_name
226 FROM ALL_SNAPSHOTS
227 WHERE name = pSNAPName
228 AND OWNER = l_wsh_schema ;
229
230
231 lv_snapshot_str := lv_snap_schema||'.'||pSNAPName;
232 lv_refresh_param := 'F';
233 DBMS_SNAPSHOT.REFRESH ( lv_snapshot_str,
234 lv_refresh_param,
235 parallelism =>lv_DEGREE);
236 END IF;
237
238 COMMIT;
239
240 RETCODE := G_SUCCESS;
241 EXCEPTION
242 WHEN SOURCE_SETUP_ERROR THEN
243 MSC_UTIL.MSC_LOG('Error Setting Up Source Objects');
244 RETCODE:= G_ERROR;
245
246 ERRBUF:= SQLERRM;
247
248 WHEN OTHERS THEN ROLLBACK;
249 RETCODE := G_ERROR;
250 ERRBUF := SQLERRM;
251 END ;
252
253 /* This function determines whether some necessary object for snapshot exists */
254 FUNCTION FOUND_EXIST_OBJECTS RETURN BOOLEAN
255 IS
256 l_objs_count NUMBER;
257
258 BEGIN
259
260 SELECT COUNT(object_name)
261 INTO l_objs_count
262 FROM ALL_OBJECTS
263 WHERE UPPER(object_name) = 'MST_DELIVERY_DETAILS_SN_V' AND
264 UPPER(object_type) = 'VIEW' AND
265 UPPER(owner) = 'APPS';
266
267 IF ( l_objs_count < 1 ) THEN
268 return FALSE;
269 END IF;
270
271 return TRUE;
272
273 EXCEPTION
274
275 WHEN OTHERS THEN
276
277 MSC_UTIL.MSC_LOG( SQLERRM);
278 return FALSE;
279 END FOUND_EXIST_OBJECTS;
280
281
282 /* This function will be called based on the profile option MST_SOURCE_SETUP*/
283 FUNCTION SETUP_SOURCE_OBJECTS RETURN BOOLEAN
284 IS
285 l_user_id NUMBER;
286 l_application_id NUMBER;
287 l_resp_id NUMBER;
288
289 lv_request_id_wsh NUMBER;
290 lv_request_id_objs NUMBER;
291 lv_success boolean:= TRUE;
292
293 lv_out number;
294
295 BEGIN
296 /* Submit the request to drop and create snapshot*/
297 lv_request_id_wsh := FND_REQUEST.SUBMIT_REQUEST(
298 'MST',
299 'MSTWSHSN',
300 NULL,
301 NULL,
302 FALSE); -- sub request
303 commit;
304 MSC_UTIL.MSC_LOG( 'Request : ' ||
305 lv_request_id_wsh||' : WSH SNAPSHOT TABLE Creation request submitted');
306
307 wait_for_request(lv_request_id_wsh, 10, lv_out);
308
309 if lv_out = 2 THEN
310 lv_success := FALSE ;
311 else
312 lv_success := TRUE;
313 end if;
314
315 /* Only if the Snapshot Creation Process is successfull then call the
316 create triggers/view/synonyms */
317
318 if lv_success THEN --create snapshots success
319 lv_request_id_objs := FND_REQUEST.SUBMIT_REQUEST(
320 'MST',
321 'MSTSNETC',
322 NULL,
323 NULL,
324 FALSE); -- sub request
325 commit;
326 MSC_UTIL.MSC_LOG( 'Request : '||lv_request_id_objs||' :Creates Triggesr/Views Submitted');
327
328 end if;
329
330 IF lv_success THEN
331 begin
332 MSC_UTIL.MSC_LOG('Updating Profile Option MSC_SOURCE_SETUP to No ');
333
334 UPDATE FND_PROFILE_OPTION_VALUES
335 SET PROFILE_OPTION_VALUE = '2'
336 WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
337 FROM FND_PROFILE_OPTIONS
338 WHERE PROFILE_OPTION_NAME = 'MST_SOURCE_SETUP');
339 MSC_UTIL.MSC_LOG('Profile Option MSC_SOURCE_SETUP has been updated No ');
340
341 COMMIT;
342 return TRUE;
343
344 EXCEPTION
345 WHEN OTHERS THEN
346 MSC_UTIL.MSC_LOG ('Error Updating Profile MSC_SOURCE_SETUP: '||SQLERRM);
347 end;
348 ELSE
349 MSC_UTIL.MSC_LOG ('Source Setup Objects Creation Requests did not complete Successfully');
350 MSC_UTIL.MSC_LOG(lv_request_id_wsh);
351 MSC_UTIL.MSC_LOG(lv_request_id_objs);
352 return false;
353 END IF;
354
355 return true;
356
357 EXCEPTION
358
359 WHEN OTHERS THEN
360
361 MSC_UTIL.MSC_LOG( SQLERRM);
362 return FALSE;
363 END SETUP_SOURCE_OBJECTS;
364
365
366 PROCEDURE WAIT_FOR_REQUEST(
367 p_request_id in number,
368 p_timeout IN NUMBER,
369 o_retcode OUT NOCOPY NUMBER)
370 IS
371
372 l_refreshed_flag NUMBER;
373 l_pending_timeout_flag NUMBER;
374 l_start_time DATE;
375
376 ---------------- used for fnd_concurrent ---------
377 l_call_status boolean;
378 l_phase varchar2(80);
379 l_status varchar2(80);
380 l_dev_phase varchar2(80);
381 l_dev_status varchar2(80);
382 l_message varchar2(240);
383 l_request_id number;
384
385 BEGIN
386 l_request_id := p_request_id;
387 l_start_time := SYSDATE;
388
389 LOOP
390 << begin_loop >>
391
392 l_pending_timeout_flag := SIGN( SYSDATE - l_start_time - p_timeout/1440.0);
393
394 l_call_status:= FND_CONCURRENT.WAIT_FOR_REQUEST
395 ( l_request_id,
396 10,
397 10,
398 l_phase,
399 l_status,
400 l_dev_phase,
401 l_dev_status,
402 l_message);
403
404 EXIT WHEN l_call_status=FALSE;
405 IF l_dev_phase='PENDING' THEN
406 EXIT WHEN l_pending_timeout_flag= 1;
407
408 ELSIF l_dev_phase='RUNNING' THEN
409 GOTO begin_loop;
410
411 ELSIF l_dev_phase='COMPLETE' THEN
412 IF l_dev_status = 'NORMAL' THEN
413 o_retcode:= SYS_YES;
414 RETURN;
415 END IF;
416 EXIT;
417
418 ELSIF l_dev_phase='INACTIVE' THEN
419 EXIT WHEN l_pending_timeout_flag= 1;
420 END IF;
421
422 DBMS_LOCK.SLEEP( 10);
423
424 END LOOP;
425
426 o_retcode:= SYS_NO;
427 RETURN;
428 END WAIT_FOR_REQUEST;
429
430 END mst_launch_plan_pkg; -- package