[Home] [Help]
PACKAGE BODY: APPS.MRP_CL_REFRESH_SNAPSHOT
Source
1 PACKAGE BODY MRP_CL_REFRESH_SNAPSHOT AS
2 /* $Header: MRPCLEAB.pls 120.26.12010000.2 2008/10/06 08:17:45 sbyerram ship $ */
3
4 V_STMT_NO NUMBER:= 0;
5 V_REMOTE_CALL BOOLEAN :=FALSE; -- for 2351297
6 TRUNCATE_LOG_ERROR EXCEPTION;
7 TYPE NumTblTyp IS TABLE OF NUMBER;
8
9 --- PREPLACE CHANGE START ---
10
11 NULL_DBLINK CONSTANT VARCHAR2(1):= ' ';
12 v_a2m_dblink VARCHAR2(128);
13
14 lv_snapshot_str VARCHAR2(4000):= ''; -- list of snapshots to be refreshed
15 lv_refresh_param VARCHAR2(80):= '';
16 -- combinations of 'C', 'F', ... for refreshment
17 lv_num_of_snap NUMBER := 0;
18
19 v_database_version number;
20 lv_db_version varchar2(100);
21 lv_db_cmpt_version varchar2(100);
22
23 g_REFRESH_TYPE VARCHAR2(30);
24 g_CALLING_MODULE NUMBER;
25 g_INSTANCE_ID NUMBER;
26 g_INSTANCE_CODE VARCHAR2(150);
27 g_A2M_DBLINK VARCHAR2(150);
28
29 --- PREPLACE CHANGE END ---
30 /* this procedure create index for SRP project */
31 PROCEDURE CREATE_INDEX (lv_status IN OUT NOCOPY NUMBER) IS
32 lv_sql_stmt varchar2(2000);
33 lv_csd_schema VARCHAR2(32);
34 lv_csp_schema VARCHAR2(32);
35 lv_tablespace VARCHAR2(30);
36 lv_index_tablespace VARCHAR2(30);
37 lv_storage_clause VARCHAR2(200);
38 BEGIN
39
40 lv_csd_schema := MSC_UTIL.GET_SCHEMA_NAME(512);
41 MSC_UTIL.GET_STORAGE_PARAMETERS('CSD_REPAIRS',
42 lv_csd_schema,
43 lv_tablespace,
44 lv_index_tablespace,
45 lv_storage_clause);
46
47 lv_sql_stmt:= 'CREATE INDEX CSD_REPAIRS_N11 ON CSD_REPAIRS (inventory_org_id ,'
48 || 'last_update_date ,REPAIR_MODE ) TABLESPACE '
49 ||lv_index_tablespace;
50 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_sql_stmt);
51 BEGIN
52 Csd_Repairs_Util.create_csd_index(lv_sql_stmt,'CSD_REPAIRS_N11');
53 lv_status :=1 ;
54 EXCEPTION
55 WHEN OTHERS THEN
56 IF SQLCODE IN (-01408) THEN
57 /*Index on same column already exists*/
58 lv_status :=1 ;
59 ELSIF SQLCODE IN (-00955) THEN
60 /*Index already exists*/
61 lv_status :=1 ;
62 ELSE
63 lv_status := 2 ;
64 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
65 raise_application_error(-20001, 'Index Creation failed: ' || sqlerrm);
66 END IF;
67 END ;
68 lv_csp_schema := MSC_UTIL.GET_SCHEMA_NAME(523);
69 MSC_UTIL.GET_STORAGE_PARAMETERS('CSP_REPAIR_PO_HEADERS',
70 lv_csp_schema,
71 lv_tablespace,
72 lv_index_tablespace,
73 lv_storage_clause);
74
75 lv_sql_stmt:= 'CREATE INDEX CSP_REPAIR_PO_HEADERS_N1 ON CSP_REPAIR_PO_HEADERS'
76 || '(WIP_id, REPAIR_PO_HEADER_ID, '
77 || 'INVENTORY_ITEM_ID) TABLESPACE '
78 ||lv_index_tablespace;
79 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_sql_stmt);
80 BEGIN
81 CSP_REPAIR_PO_GRP.create_csp_index(lv_sql_stmt,'CSP_REPAIR_PO_HEADERS_N1');
82 lv_status :=1 ;
83 EXCEPTION
84 WHEN OTHERS THEN
85 IF SQLCODE IN (-01408) THEN
86 /*Index on same column already exists*/
87 lv_status :=1 ;
88 ELSIF SQLCODE IN (-00955) THEN
89 /*Index already exists*/
90 lv_status :=1 ;
91 ELSE
92 lv_status := 2 ;
93 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
94 raise_application_error(-20001, 'Index Creation failed: ' || sqlerrm);
95 END IF;
96 END ;
97
98 lv_sql_stmt:= 'CREATE INDEX CSP_REPAIR_PO_HEADERS_N2 ON CSP_REPAIR_PO_HEADERS'
99 || '( purchase_order_header_id) TABLESPACE '
100 ||lv_index_tablespace;
101 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_sql_stmt);
102 BEGIN
103 CSP_REPAIR_PO_GRP.create_csp_index(lv_sql_stmt,'CSP_REPAIR_PO_HEADERS_N2');
104 lv_status :=1 ;
105 EXCEPTION
106 WHEN OTHERS THEN
107 IF SQLCODE IN (-01408) THEN
108 /*Index on same column already exists*/
109 lv_status :=1 ;
110 ELSIF SQLCODE IN (-00955) THEN
111 /*Index already exists*/
112 lv_status :=1 ;
113 ELSE
114 lv_status := 2 ;
115 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
116 raise_application_error(-20001, 'Index Creation failed: ' || sqlerrm);
117 END IF;
118 END ;
119
120 lv_sql_stmt:= 'CREATE INDEX CSP_REPAIR_PO_HEADERS_N3 ON CSP_REPAIR_PO_HEADERS'
121 || '( requisition_line_id ) TABLESPACE '
122 ||lv_index_tablespace;
123 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_sql_stmt);
124 BEGIN
125 CSP_REPAIR_PO_GRP.create_csp_index(lv_sql_stmt,'CSP_REPAIR_PO_HEADERS_N3');
126 lv_status :=1 ;
127 EXCEPTION
128 WHEN OTHERS THEN
129 IF SQLCODE IN (-01408) THEN
130 /*Index on same column already exists*/
131 lv_status :=1 ;
132 ELSIF SQLCODE IN (-00955) THEN
133 /*Index already exists*/
134 lv_status :=1 ;
135 ELSE
136 lv_status := 2 ;
137 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
138 raise_application_error(-20001, 'Index Creation failed: ' || sqlerrm);
139 END IF;
140 END ;
141 lv_status :=1 ;
142 END CREATE_INDEX;
143
144
145 /* NEW Patching Strategy */
146 /* This procedure will be called based on the profile option MSC_SOURCE_SETUP*/
147 FUNCTION SETUP_SOURCE_OBJECTS RETURN BOOLEAN
148 IS
149 l_user_id NUMBER;
150 l_application_id NUMBER;
151 l_resp_id NUMBER;
152 lv_request_id_drop NUMBER;
153 lv_request_id_wip NUMBER;
154 lv_request_id_wsm NUMBER;
155 lv_request_id_wsh NUMBER;
156 lv_request_id_bom NUMBER;
157 lv_request_id_inv NUMBER;
158 lv_request_id_mrp NUMBER;
159 lv_request_id_ont NUMBER;
160 lv_request_id_pox NUMBER;
161 lv_request_id_ahl NUMBER;
162 lv_request_id_view NUMBER;
163 lv_request_id_syn NUMBER;
164 lv_request_id_trig NUMBER;
165 lv_success boolean:= TRUE;
166 lv_out number;
167 lv_request_id_eam NUMBER; /* ds change: change */
168 lv_sql_stmt varchar2(2000);
169 lv_srp_enabled_flag VARCHAR2(1);
170
171 BEGIN
172 /* Submit the request to look for changed snapshots and drop these snapshots */
173 lv_request_id_drop := FND_REQUEST.SUBMIT_REQUEST(
174 'MSC',
175 'MSCDROPS',
176 NULL,
177 NULL,
178 FALSE); -- sub request
179
180 commit;
181 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '
182 ||lv_request_id_drop||' :Checks for Snapshots which have changed and Drops them');
183 wait_for_request(lv_request_id_drop, 10, lv_out);
184
185 if lv_success THEN
186 if lv_out = 2 THEN lv_success := FALSE ; end if;
187 end if;
188
189 if lv_success THEN
190 lv_sql_stmt:= 'select NVL(FND_PROFILE.VALUE'||v_a2m_dblink||'(''MSC_SRP_ENABLED''),''N'')'
191 || ' from dual ';
192 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_sql_stmt);
193 execute immediate lv_sql_stmt into lv_srp_enabled_flag ;
194 IF lv_srp_enabled_flag='Y' THEN
195 CREATE_INDEX(lv_out);
196 if lv_out = 2 THEN
197 lv_success := FALSE ;
198 end if;
199 END IF ;
200
201 END IF ;
202
203 /* Only if the Drop Snapshot Process is successfull then call the create snapshots */
204
205 if lv_success THEN --drop snapshots success
206 lv_request_id_wsm := FND_REQUEST.SUBMIT_REQUEST(
207 'MSC',
208 'MSCWSMSN',
209 NULL,
210 NULL,
211 FALSE); -- sub request
212 commit;
213 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_wsm||' :Creates WSM Snapshot Logs and Snapshots');
214
215 lv_request_id_bom := FND_REQUEST.SUBMIT_REQUEST(
216 'MSC',
217 'MSCBOMSN',
218 NULL,
219 NULL,
220 FALSE); -- sub request
221 commit;
222 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_bom||' :Creates BOM Snapshot Logs and Snapshots');
223
224 lv_request_id_inv := FND_REQUEST.SUBMIT_REQUEST(
225 'MSC',
226 'MSCINVSN',
227 NULL,
228 NULL,
229 FALSE); -- sub request
230 commit;
231
232 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_bom||' :Creates CSP Snapshot Logs and Snapshots');
233 lv_request_id_inv := FND_REQUEST.SUBMIT_REQUEST(
234 'MSC',
235 'MSCCSPSN',
236 NULL,
237 NULL,
238 FALSE); -- sub request
239 commit;
240 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_inv||' :Creates INV Snapshot Logs and Snapshots');
241
242 lv_request_id_mrp := FND_REQUEST.SUBMIT_REQUEST(
243 'MSC',
244 'MSCMRPSN',
245 NULL,
246 NULL,
247 FALSE); -- sub request
248 commit;
249 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_mrp||' :Creates MRP Snapshot Logs and Snapshots');
250
251 lv_request_id_pox := FND_REQUEST.SUBMIT_REQUEST(
252 'MSC',
253 'MSCPOXSN',
254 NULL,
255 NULL,
256 FALSE); -- sub request
257 commit;
258 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_pox||' :Creates PO Snapshot Logs and Snapshots');
259
260 lv_request_id_ont := FND_REQUEST.SUBMIT_REQUEST(
261 'MSC',
262 'MSCONTSN',
263 NULL,
264 NULL,
265 FALSE); -- sub request
266 commit;
267 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_ont||' :Creates OE Snapshot Logs and Snapshots');
268
269 lv_request_id_wsh := FND_REQUEST.SUBMIT_REQUEST(
270 'MSC',
271 'MSCWSHSN',
272 NULL,
273 NULL,
274 FALSE); -- sub request
275 commit;
276 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_wsh||' :Creates WSH Snapshot Logs and Snapshots');
277
278 IF MRP_CL_FUNCTION.CHECK_AHL_VER = 1 THEN
279
280 lv_request_id_ahl := FND_REQUEST.SUBMIT_REQUEST(
281 'MSC',
282 'MSCAHLSN',
283 NULL,
284 NULL,
285 FALSE); -- sub request
286 commit;
287 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_ahl||' :Creates AHL Snapshot Logs and Snapshots');
288
289 /* ds change: change start */
290 lv_request_id_eam := FND_REQUEST.SUBMIT_REQUEST(
291 'MSC',
292 'MSCEAMSN',
293 NULL,
294 NULL,
295 FALSE); -- sub request
296 commit;
297 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_eam||' :Creates EAM Snapshot Logs and Snapshots');
298
299 /* ds change: change end */
300
301
302 END IF;
303
304
305 /* BUG 3019053
306 * Create WIP snapshot only when the MRP snapshots are created
307 * successfully.
308 * This is done since the WIP snapshots need the snapshot log and Grants
309 * on the new MRP table - mrp_ap_open_wip_status.
310 */
311
312 wait_for_request(lv_request_id_mrp, 10, lv_out);
313 if lv_success THEN
314
315 lv_request_id_wip := FND_REQUEST.SUBMIT_REQUEST(
316 'MSC',
317 'MSCWIPSN',
318 NULL,
319 NULL,
320 FALSE); -- sub request
321 commit;
322 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_wip||' :Creates WIP Snapshot Logs and Snapshots');
323
324 if lv_out = 2 THEN lv_success := FALSE ; end if;
325 end if;
326
327 wait_for_request(lv_request_id_wsm, 10, lv_out);
328 if lv_success THEN
329 if lv_out = 2 THEN lv_success := FALSE ; end if;
330 end if;
331 wait_for_request(lv_request_id_bom, 10, lv_out);
332 if lv_success THEN
333 if lv_out = 2 THEN lv_success := FALSE ; end if;
334 end if;
335 wait_for_request(lv_request_id_inv, 10, lv_out);
336 if lv_success THEN
337 if lv_out = 2 THEN lv_success := FALSE ; end if;
338 end if;
339 wait_for_request(lv_request_id_pox, 10, lv_out);
340 if lv_success THEN
341 if lv_out = 2 THEN lv_success := FALSE ; end if;
342 end if;
343 wait_for_request(lv_request_id_ont, 10, lv_out);
344 if lv_success THEN
345 if lv_out = 2 THEN lv_success := FALSE ; end if;
346 end if;
347 wait_for_request(lv_request_id_wip, 10, lv_out);
348 if lv_success THEN
349 if lv_out = 2 THEN lv_success := FALSE ; end if;
350 end if;
351 wait_for_request(lv_request_id_wsh, 10, lv_out);
352 if lv_success THEN
353 if lv_out = 2 THEN lv_success := FALSE ; end if;
354 end if;
355 if MRP_CL_FUNCTION.CHECK_AHL_VER = 1 THEN
356 wait_for_request(lv_request_id_ahl, 10, lv_out);
357 if lv_success THEN
358 if lv_out = 2 THEN lv_success := FALSE ; end if;
359 end if;
360 /* ds change: change start */
361 wait_for_request(lv_request_id_eam, 10, lv_out);
362 if lv_success THEN
363 if lv_out = 2 THEN lv_success := FALSE ; end if;
364 end if;
365 /* ds change: change end */
366 end if;
367 /* Only if the Snapshot creation Process is successfull then create trigs, views,synms */
368
369 if lv_success THEN --create snapshots success
370 lv_request_id_syn := FND_REQUEST.SUBMIT_REQUEST(
371 'MSC',
372 'MSCSYNMS',
373 NULL,
374 NULL,
375 FALSE); -- sub request
376 commit;
377 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_syn||' :Creates Synonyms used by Collections Process');
378
379 wait_for_request(lv_request_id_syn, 10, lv_out);
380 if lv_success THEN
381 if lv_out = 2 THEN lv_success := FALSE ; end if;
382 end if;
383
384 IF lv_success THEN -- Only when Synonyms creation succcess
385 lv_request_id_view := FND_REQUEST.SUBMIT_REQUEST(
386 'MSC',
387 'MSCVIEWS',
388 NULL,
389 NULL,
390 FALSE); -- sub request
391 commit;
392 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_view||' :Creates Views used by Collections Process');
393
394 lv_request_id_trig := FND_REQUEST.SUBMIT_REQUEST(
395 'MSC',
396 'MSCTRIGS',
397 NULL,
398 NULL,
399 FALSE); -- sub request
400 commit;
401 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trig||' :Creates Triggers used by Collections Process');
402 wait_for_request(lv_request_id_view, 10, lv_out);
403 if lv_success THEN
404 if lv_out = 2 THEN lv_success := FALSE ; end if;
405 end if;
406 wait_for_request(lv_request_id_trig, 10, lv_out);
407 if lv_success THEN
408 if lv_out = 2 THEN lv_success := FALSE ; end if;
409 end if;
410
411
412 END IF; -- Synonyms creation succcess
413 end if; -- create snapshots success
414 end if; --drop snapshots success
415
416
417 COMMIT;
418 /* CALLING MAP_REGION_TO_SITE FOR MAPPING VENDOR SITES TO REGIONS */
419
420 /* UPDATE THE PROFILE OPTION MSC_SOURCE_SETUP TO NO */
421
422 IF lv_success AND ( MRP_CL_FUNCTION.MAP_REGION_TO_SITE(null) = 1) THEN
423
424 begin
425 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Updating Profile Option MSC_SOURCE_SETUP to No ');
426
427 UPDATE FND_PROFILE_OPTION_VALUES
428 SET PROFILE_OPTION_VALUE = 'N'
429 WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
430 FROM FND_PROFILE_OPTIONS
431 WHERE PROFILE_OPTION_NAME = 'MSC_SOURCE_SETUP');
432 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Profile Option MSC_SOURCE_SETUP has been updated No ');
433 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Value No indicates that the Collection Setup Objects have been applied');
434 COMMIT;
435 return TRUE;
436
437 EXCEPTION
438
439 WHEN OTHERS THEN
440 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Updating Profile MSC_SOURCE_SETUP: '||SQLERRM);
441 end;
442 ELSE
443 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Source Setup Objects Creation Requests did not complete Successfully');
444 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please check the Log files for the appropriate message:');
445 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_drop);
446 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wip);
447 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_eam); /* ds change: change */
448 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsm);
449 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_bom);
450 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_inv);
451 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_mrp);
452 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_pox);
453 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ont);
454 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_wsh);
455 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_ahl);
456 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_view);
457 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_syn);
458 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_request_id_trig);
459
460 return false;
461
462 END IF;
463 return true;
464
465 EXCEPTION
466
467 WHEN OTHERS THEN
468
469 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
470 return FALSE;
471 END SETUP_SOURCE_OBJECTS;
472 /* NEW Patching Strategy */
473 --3967634 added function CHECK_INSTALL
474 FUNCTION CHECK_INSTALL (app_name IN VARCHAR2)RETURN BOOLEAN
475 IS
476 l_status varchar2(1);
477 l_industry varchar2(1);
478 l_ora_schema varchar2(30);
479 l_return_code boolean;
480 BEGIN
481 --
482 -- Call FND routine to figure out installation status
483 --
484 -- If the license status is not 'I', Project Manufacturing is
485 -- not installed.
486 --
487 l_return_code := fnd_installation.get_app_info(app_name,
488 l_status,
489 l_industry,
490 l_ora_schema);
491
492 IF (l_return_code = FALSE) THEN
493 return FALSE;
494 END IF;
495 IF (l_status <> 'I') THEN
496 return FALSE;
497 END IF;
498 return TRUE;
499 END CHECK_INSTALL;
500 --3967634
501
502 /* -- Removed logic from below function, and moved it to the overloaded function */
503
504 PROCEDURE REFRESH_SNAPSHOT(
505 ERRBUF OUT NOCOPY VARCHAR2,
506 RETCODE OUT NOCOPY NUMBER,
507 p_user_name IN VARCHAR2,
508 p_resp_name IN VARCHAR2,
509 p_application_name IN VARCHAR2,
510 p_refresh_type IN VARCHAR2,
511 o_request_id OUT NOCOPY NUMBER,
512 pInstance_ID IN NUMBER,
513 pInstance_Code IN VARCHAR2,
514 pa2m_dblink IN VARCHAR2)
515 IS
516
517 l_application_id NUMBER;
518 lv_application_name VARCHAR2(240);
519
520 BEGIN
521
522 lv_application_name := p_application_name;
523
524 BEGIN
525
526 SELECT APPLICATION_ID
527 INTO l_application_id
528 FROM FND_APPLICATION_VL
529 WHERE APPLICATION_NAME = lv_application_name;
530
531 EXCEPTION
532
533 WHEN NO_DATA_FOUND THEN
534 RETCODE:= G_ERROR;
535 ERRBUF := 'NO_USER_DEFINED';
536 RETURN;
537 WHEN OTHERS THEN RAISE;
538 END;
539
540 REFRESH_SNAPSHOT(
541 ERRBUF,
542 RETCODE,
543 p_user_name,
544 p_resp_name,
545 p_application_name,
546 p_refresh_type,
547 o_request_id,
548 pInstance_ID,
549 pInstance_Code,
550 pa2m_dblink,
551 l_application_id
552 );
553
554 END REFRESH_SNAPSHOT;
555
556 /* -- Added this procedure to accept application_id instead of application_name */
557
558 PROCEDURE REFRESH_SNAPSHOT(
559 ERRBUF OUT NOCOPY VARCHAR2,
560 RETCODE OUT NOCOPY NUMBER,
561 p_user_name IN VARCHAR2,
562 p_resp_name IN VARCHAR2,
563 p_application_name IN VARCHAR2,
564 p_refresh_type IN VARCHAR2,
565 o_request_id OUT NOCOPY NUMBER,
566 pInstance_ID IN NUMBER,
567 pInstance_Code IN VARCHAR2,
568 pa2m_dblink IN VARCHAR2,
569 p_application_id IN NUMBER)
570 IS
571
572 l_user_id NUMBER;
573 l_application_id NUMBER;
574 l_resp_id NUMBER;
575
576 lv_user_name VARCHAR2(100);
577 lv_resp_name VARCHAR2(100);
578 lv_ref_type VARCHAR2(1);
579
580 result BOOLEAN;
581
582 lv_log_msg varchar2(500);
583
584 BEGIN
585
586 lv_ref_type := p_refresh_type;
587
588 /* if user_id = -1, it means this procedure is called from a
589 remote database */
590 IF FND_GLOBAL.USER_ID = -1 THEN
591 V_REMOTE_CALL := TRUE; -- for 2351297
592 lv_user_name := p_user_name;
593 lv_resp_name := p_resp_name;
594
595 BEGIN
596 SELECT USER_ID
597 INTO l_user_id
598 FROM FND_USER
599 WHERE USER_NAME = lv_user_name;
600 EXCEPTION
601 WHEN NO_DATA_FOUND THEN
602 RETCODE:= G_ERROR;
603 ERRBUF := 'NO_USER_DEFINED';
604 RETURN;
605 END;
606
607 IF MRP_CL_FUNCTION.validateUser(l_user_id,MSC_UTIL.TASK_COLL,lv_log_msg) THEN
608 MRP_CL_FUNCTION.MSC_Initialize(MSC_UTIL.TASK_COLL,
609 l_user_id,
610 -1, --l_resp_id,
611 -1 --l_application_id
612 );
613 ELSE
614 RETCODE:= MSC_UTIL.G_ERROR;
615 ERRBUF := lv_log_msg;
616 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_log_msg);
617 RETURN;
618 END IF;
619
620 END IF;
621
622 IF V_REMOTE_CALL THEN
623 result := FND_REQUEST.SET_MODE(TRUE);
624 v_request_id := FND_REQUEST.SUBMIT_REQUEST(
625 'MRP',
626 'MSRFWOR',
627 NULL,
628 NULL,
629 FALSE, -- not a sub request,code fix for 2351297
630 1, -- Fast refresh
631 'COLL SNAPSHOTS', --
632 0, -- threshold not used
633 0, -- degree of parallel
634 SYS_YES, -- Conc. progr enabled
635 lv_ref_type,
636 G_COLLECTIONS,
637 pInstance_ID,
638 pInstance_Code,
639 pa2m_dblink);
640 ELSE
641 v_request_id := FND_REQUEST.SUBMIT_REQUEST(
642 'MRP',
643 'MSRFWOR',
644 NULL,
645 NULL,
646 TRUE, -- sub request,code fix for 2351297
647 1, -- Fast refresh
648 'COLL SNAPSHOTS', -- IN VARCHAR2,
649 0, -- threshold not used
650 0, -- degree of parallel
651 SYS_YES, -- Conc. progr enabled
652 lv_ref_type,
653 G_COLLECTIONS,
654 pInstance_ID,
655 pInstance_Code,
656 pa2m_dblink);
657 COMMIT;
658 END IF;
659
660 o_request_id := v_request_id;
661 IF v_request_id = 0 THEN
662 ERRBUF:= FND_MESSAGE.GET;
663 END IF;
664
665 RETCODE:= G_SUCCESS;
666
667 EXCEPTION
668
669 WHEN OTHERS THEN
670
671 RETCODE:= G_ERROR;
672
673 ERRBUF:= SQLERRM;
674
675 END REFRESH_SNAPSHOT;
676
677
678 PROCEDURE LOG_ERROR( pBUFF IN VARCHAR2)
679 IS
680 BEGIN
681
682 IF v_cp_enabled= SYS_YES THEN
683
684 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
685 null;
686
687 ELSE
688
689 null;
690 --DBMS_OUTPUT.PUT_LINE( pBUFF);
691
692 END IF;
693
694 END LOG_ERROR;
695
696
697 PROCEDURE LOG_DEBUG( pBUFF IN VARCHAR2)
698 IS
699 BEGIN
700 IF (G_MSC_DEBUG <> 'Y') THEN
701 return;
702 END IF;
703 -- add a line of text to the log file and
704
705 FND_FILE.PUT_LINE(FND_FILE.LOG,pBUFF);
706 --DBMS_OUTPUT.PUT_LINE( pBUFF);
707 null;
708 return;
709
710 EXCEPTION
711 WHEN OTHERS THEN
712 return;
713 END LOG_DEBUG;
714
715 PROCEDURE PURGE_OBSOLETE_DATA
716 IS
717 lv_mrp_schema VARCHAR2(30);
718 lv_sql_stmt VARCHAR2(100);
719 lv_retval boolean;
720 lv_dummy1 varchar2(32);
721 lv_dummy2 varchar2(32);
722
723 CURSOR c_query_tables(lv_owner VARCHAR2) is
724 SELECT table_name
725 FROM ALL_TABLES
726 WHERE TABLE_NAME like 'MRP_AD%'
727 AND owner = lv_owner;
728
729 BEGIN
730
731 lv_mrp_schema := MSC_UTIL.G_MRP_SCHEMA;
732
733 FOR c1 in c_query_tables(lv_mrp_schema)
734 LOOP
735 BEGIN
736
737 lv_sql_stmt := 'TRUNCATE TABLE '||lv_mrp_schema||'.'||c1.table_name;
738 EXECUTE IMMEDIATE lv_sql_stmt;
739
740 EXCEPTION
741 WHEN OTHERS THEN
742 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
743 END;
744 END LOOP;
745
746 EXCEPTION
747 WHEN OTHERS THEN
748 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
749
750 END PURGE_OBSOLETE_DATA;
751
752 /*NEW PATCHING STRATEGY */
753 PROCEDURE WAIT_FOR_REQUEST(
754 p_request_id in number,
755 p_timeout IN NUMBER,
756 o_retcode OUT NOCOPY NUMBER)
757 IS
758
759 l_refreshed_flag NUMBER;
760 l_pending_timeout_flag NUMBER;
761 l_start_time DATE;
762
763 ---------------- used for fnd_concurrent ---------
764 l_call_status boolean;
765 l_phase varchar2(80);
766 l_status varchar2(80);
767 l_dev_phase varchar2(80);
768 l_dev_status varchar2(80);
769 l_message varchar2(240);
770 l_request_id number;
771
772 BEGIN
773 l_request_id := p_request_id;
774 l_start_time := SYSDATE;
775
776 LOOP
777 << begin_loop >>
778
779 l_pending_timeout_flag := SIGN( SYSDATE - l_start_time - p_timeout/1440.0);
780
781 l_call_status:= FND_CONCURRENT.WAIT_FOR_REQUEST
782 ( l_request_id,
783 60,
784 10,
785 l_phase,
786 l_status,
787 l_dev_phase,
788 l_dev_status,
789 l_message);
790
791 EXIT WHEN l_call_status=FALSE;
792
793 IF l_dev_phase='PENDING' THEN
794 EXIT WHEN l_pending_timeout_flag= 1;
795
796 ELSIF l_dev_phase='RUNNING' THEN
797 GOTO begin_loop;
798
799 ELSIF l_dev_phase='COMPLETE' THEN
800 IF l_dev_status = 'NORMAL' THEN
801 o_retcode:= SYS_YES;
802 RETURN;
803 END IF;
804 EXIT;
805
806 ELSIF l_dev_phase='INACTIVE' THEN
807 EXIT WHEN l_pending_timeout_flag= 1;
808 END IF;
809
810 DBMS_LOCK.SLEEP( 10);
811
812 END LOOP;
813
814 o_retcode:= SYS_NO;
815 RETURN;
816 END WAIT_FOR_REQUEST;
817 /*NEW PATCHING STRATEGY */
818
819
820 PROCEDURE WAIT_FOR_REQUEST(
821 p_timeout IN NUMBER,
822 o_retcode OUT NOCOPY NUMBER)
823 IS
824
825 l_refreshed_flag NUMBER;
826 l_pending_timeout_flag NUMBER;
827 l_start_time DATE;
828
829 ---------------- used for fnd_concurrent ---------
830 l_call_status boolean;
831 l_phase varchar2(80);
832 l_status varchar2(80);
833 l_dev_phase varchar2(80);
834 l_dev_status varchar2(80);
835 l_message varchar2(240);
836
837 BEGIN
838
839 l_start_time := SYSDATE;
840
841 LOOP
842
843 l_pending_timeout_flag := SIGN( SYSDATE - l_start_time - p_timeout/1440.0);
844
845 l_call_status:= FND_CONCURRENT.WAIT_FOR_REQUEST
846 ( v_request_id,
847 10,
848 10,
849 l_phase,
850 l_status,
851 l_dev_phase,
852 l_dev_status,
853 l_message);
854
855 EXIT WHEN l_call_status=FALSE;
856
857 IF l_dev_phase='PENDING' OR l_dev_phase='INACTIVE' THEN
858 IF l_pending_timeout_flag= 1 THEN
859 o_retcode:= G_PENDING_INACTIVE;
860 RETURN;
861 END IF;
862 ELSIF l_dev_phase='COMPLETE' THEN
863 IF l_dev_status = 'NORMAL' OR l_dev_status = 'WARNING' THEN
864 o_retcode:= G_NORMAL_COMPLETION;
865 RETURN;
866 END IF;
867 EXIT;
868 END IF;
869
870 DBMS_LOCK.SLEEP( 10);
871
872 END LOOP;
873
874 o_retcode:= G_OTHERS;
875 RETURN;
876
877 END WAIT_FOR_REQUEST;
878
879
880 /* added this private function to check the number of rows in snapshot log and whether to truncate it
881 for bug: 2507837 The snapshot having 0 rows will also be completely refreshed*/
882 FUNCTION TRUNC_SNAP_LOG( pNUM_OF_ROWS IN NUMBER,
883 pSCHEMA_NAME IN VARCHAR2,
884 pTABLE_NAME IN VARCHAR2,
885 pSNAP_NAME IN VARCHAR2,
886 pDEGREE IN NUMBER)
887 RETURN boolean
888 IS
889
890 lv_num_of_log_rows NUMBER := 0;
891 lv_num_snp_rows NUMBER := 0;
892 lv_sel_sql_stmt VARCHAR2(200);
893 lv_sel_snp_stmt VARCHAR2(200);
894 lv_trnc_sql_stmt VARCHAR2(200);
895 lv_mlog_tab_name VARCHAR2(30);
896 lv_prod_id NUMBER;
897 lv_base_schema VARCHAR2(48) := pSCHEMA_NAME;
898 lv_status BOOLEAN := TRUE;
899 BEGIN
900 v_cp_enabled := SYS_YES;
901
902 begin
903
904 SELECT LOG_TABLE
905 INTO lv_mlog_tab_name
906 FROM ALL_SNAPSHOT_LOGS
907 WHERE MASTER = upper(pTABLE_NAME)
908 AND LOG_OWNER = upper(lv_base_schema)
909 AND ROWNUM = 1;
910
911 IF pNUM_OF_ROWS > 0 THEN
912
913 lv_sel_snp_stmt := ' select count(*) from '||MSC_UTIL.G_APPS_SCHEMA|| '.' ||pSNAP_NAME
914 || ' where rownum < 2 ';
915 EXECUTE IMMEDIATE lv_sel_snp_stmt INTO lv_num_snp_rows;
916 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' The number of rows in Snapshot: '|| pSNAP_NAME ||' more than .. '||lv_num_snp_rows);
917
918 IF lv_num_snp_rows > 0 THEN
919 lv_sel_sql_stmt := ' select count(*) from '||lv_base_schema|| '.'||lv_mlog_tab_name
920 || ' where rownum <= :p1 ';
921 EXECUTE IMMEDIATE lv_sel_sql_stmt
922 INTO lv_num_of_log_rows
923 USING pNUM_OF_ROWS;
924
925 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The number of rows in Snapshot log: '||lv_mlog_tab_name
926 ||' more than .. '||lv_num_of_log_rows);
927 END IF;
928 END IF;
929
930 exception
931 when others then
932 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, sqlerrm);
933 raise;
934 end;
935
936 IF (pNUM_OF_ROWS <= lv_num_of_log_rows) OR (lv_num_snp_rows = 0) THEN
937
938 begin
939 lv_trnc_sql_stmt := 'TRUNCATE TABLE '||lv_base_schema|| '.' ||lv_mlog_tab_name;
940 EXECUTE IMMEDIATE lv_trnc_sql_stmt;
941 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Successfully Truncated the Snapshot Log : '||lv_mlog_tab_name);
942 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Successfully Truncated the Snapshot Log on Table: '||pTABLE_NAME);
943 exception
944 when others then
945 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_trnc_sql_stmt);
946 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error while truncating Snapshot Log : '||lv_mlog_tab_name);
947 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
948 RETURN TRUE; --so that the MView can do a complete refresh.
949 end;
950
951 -- refresh the snapshot
952 -- this condition will return TRUE , indicating that MView log is truncated
953 lv_status := TRUE;
954
955 ELSE
956 -- this condition will return FALSE , indicating no need to truncate the log and refresh the snapshot
957 lv_status := FALSE;
958 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Snapshot : '|| pSNAP_NAME ||' was not refreshed .');
959 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The number of rows in Snapshot log: '||lv_mlog_tab_name
960 ||' = '||lv_num_of_log_rows);
961 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'This number('||lv_num_of_log_rows
962 ||') is less than the thresold entered for truncating Logs: '
963 || pNUM_OF_ROWS);
964 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' The number of rows in Snapshot: '|| pSNAP_NAME ||' was = '||lv_num_snp_rows);
965 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '-------------------------------------------------------------------');
966
967 END IF; -- If the truncate condition matches
968
969 RETURN lv_status;
970
971 EXCEPTION
972 WHEN OTHERS THEN
973 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
974 RETURN FALSE;
975 END TRUNC_SNAP_LOG;
976
977 /*
978 -- This functions checks for the appropriate refresh type for a given snapshot.
979
980 -- The function will recommend a complete refresh of the snapshot under the foll conditions:
981 -- i) If the base table has one or more rows, and the snapshot has 0 rows
982 -- ii) If the ratio of the no. of the rows in the snapshot log to the total no. of rows in the
983 snapshot is greater than the value in profile MSC_SNAPSHOT_THRESHOLD
984
985 -- For all other conditions it will recommend force refresh
986 */
987 FUNCTION SNAPSHOT_DATA_CORRECT( p_base_schema IN VARCHAR2,
988 p_table IN VARCHAR2,
989 p_snapshot IN VARCHAR2)
990 RETURN boolean
991 IS
992 lv_tab_count NUMBER := 0;
993 lv_snap_count NUMBER := 0;
994 lv_log_count NUMBER := 0;
995 lv_where_clause VARCHAR2(2000) := NULL;
996 lv_mlog_tab_name VARCHAR2(48);
997 lv_master_tbl VARCHAR2(48);
998 lv_base_schema VARCHAR2(48);
999 lv_snapshot_threshold NUMBER := NVL(FND_PROFILE.VALUE('MSC_SNAPSHOT_THRESHOLD'),40);
1000
1001 BEGIN
1002 /*
1003 IF (p_snapshot = 'MTL_MTRX_TMP_SN') OR (p_snapshot = 'WIP_FLOW_SCHDS_SN') OR
1004 (p_snapshot = 'WIP_WREQ_OPRS_SN') OR (p_snapshot = 'WIP_WOPRS_SN') OR
1005 (p_snapshot = 'WIP_WOPR_RESS_SN') OR (p_snapshot = 'AHL_SCH_MTLS_SN') OR
1006 (p_snapshot = 'WIP_OPR_RES_INSTS_SN') OR (p_snapshot = 'WIP_WOPR_NETWORKS_SN') OR
1007 (p_snapshot = 'BOM_RES_INST_CHNGS_SN') OR (p_snapshot = 'EAM_WO_RELATIONSHIPS_SN') OR
1008 (p_snapshot = 'WSM_LJ_OPR_RESS_INSTS_SN')
1009 THEN
1010 RETURN TRUE;
1011 END IF;
1012 */
1013 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'start - snapshot_data_correct');
1014 -- Add the where clause which are used in snapshot definition
1015 IF (p_snapshot = 'MTL_SUPPLY_SN') THEN
1016 lv_where_clause := ' ITEM_ID IS NOT NULL ';
1017 ELSIF (p_snapshot = 'MTL_SYS_ITEMS_SN') THEN
1018 lv_where_clause := ' (mrp_planning_code IS NOT NULL AND mrp_planning_code <> 6 AND ( inventory_item_flag = ''Y'' OR eng_item_flag = ''Y'') '
1019 ||' AND bom_item_type <> 3 AND planning_make_buy_code IN (1,2) AND primary_uom_code IS NOT NULL) OR ATP_FLAG <> ''N'' OR ATP_COMPONENTS_FLAG <> ''N'' ';
1020 ELSIF (p_snapshot = 'MRP_SCHD_DATES_SN') THEN
1021 lv_where_clause := ' Schedule_Level= 2 ';
1022 ELSIF (p_snapshot = 'PO_ACCEPTANCES_SN') THEN
1023 lv_where_clause := ' accepted_flag IN (''Y'',''N'') ';
1024 ELSIF (p_snapshot = 'PO_CHANGE_REQUESTS_SN') THEN
1025 lv_where_clause := ' document_type IN (''PO'',''RELEASE'') ';
1026 ELSIF (p_snapshot = 'WIP_DSCR_JOBS_SN') THEN
1027 lv_where_clause := ' status_type IN (1, 3, 4, 6) ';
1028 ELSIF (p_snapshot = 'WIP_WREQ_OPRS_SN') THEN
1029 lv_where_clause := ' wip_supply_type <> 6 ' ||
1030 ' AND wip_entity_id = ' ||
1031 ' (select wip_entity_id ' ||
1032 ' from wip_discrete_jobs ' ||
1033 ' where ' ||
1034 ' status_type in (1,3,4,6) '||
1035 ' and wip_entity_id = WIP_REQUIREMENT_OPERATIONS.wip_entity_id ) ' ;
1036 ELSIF (p_snapshot = 'WIP_WOPRS_SN') THEN
1037 lv_where_clause := ' wip_entity_id = ' ||
1038 ' (select wip_entity_id ' ||
1039 ' from wip_discrete_jobs ' ||
1040 ' where ' ||
1041 ' status_type in (1,3,4,6) '||
1042 ' and wip_entity_id = WIP_OPERATIONS.wip_entity_id ) ' ;
1043 ELSIF (p_snapshot = 'WIP_WOPR_RESS_SN') THEN
1044 lv_where_clause := ' wip_entity_id = ' ||
1045 ' (select wip_entity_id ' ||
1046 ' from wip_discrete_jobs ' ||
1047 ' where ' ||
1048 ' status_type in (1,3,4,6) '||
1049 ' and wip_entity_id = WIP_OPERATION_RESOURCES.wip_entity_id ) ' ;
1050 ELSIF (p_snapshot = 'WIP_REPT_SCHDS_SN') THEN
1051 lv_where_clause := ' Status_Type in (1,3,4,6) ';
1052 ELSIF (p_snapshot = 'OE_ODR_LINES_SN') THEN
1053 lv_where_clause := ' visible_demand_flag=''Y'' AND ordered_quantity <>0 AND ship_from_org_id IS NOT NULL ';
1054 ELSIF(p_snapshot = 'WSM_LJ_OPR_RESS_SN') THEN
1055 lv_where_clause := ' nvl(PHANTOM_FLAG,2) <> 1 ';
1056 ELSIF (p_snapshot = 'MRP_FORECAST_DATES_SN') THEN
1057 lv_where_clause := ' ORIGINATION_TYPE <> 10 ';
1058 ELSIF (p_snapshot = 'MTL_TXN_REQUEST_LINES_SN') THEN
1059 lv_where_clause := ' TRANSACTION_SOURCE_TYPE_ID = 5 ' ||
1060 ' AND LINE_STATUS = 7 ' ||
1061 ' AND LPN_ID IS NOT NULL ';
1062 END IF;
1063
1064 IF(lv_where_clause is not null) THEN
1065 lv_where_clause := ' WHERE '|| lv_where_clause;
1066 END IF;
1067
1068 lv_base_schema := p_base_schema;
1069 lv_master_tbl := p_table;
1070 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_master_tbl - ' || p_base_schema || '.' || lv_master_tbl);
1071
1072 EXECUTE IMMEDIATE
1073 ' SELECT LOG_TABLE FROM ALL_SNAPSHOT_LOGS '
1074 || ' WHERE MASTER = :lv_master_tbl AND '
1075 || ' LOG_OWNER = :p_schema AND '
1076 || ' ROWNUM = 1'
1077 INTO lv_mlog_tab_name
1078 USING upper(lv_master_tbl), upper(lv_base_schema);
1079 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_mlog_tab_name - ' || lv_mlog_tab_name);
1080
1081 EXECUTE IMMEDIATE
1082 ' SELECT count(*) FROM ' || lv_master_tbl || lv_where_clause
1083 INTO lv_tab_count;
1084 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_tab_count - ' || lv_tab_count);
1085
1086 EXECUTE IMMEDIATE
1087 ' SELECT count(1) FROM '||MSC_UTIL.G_APPS_SCHEMA||'.'||p_snapshot
1088 INTO lv_snap_count;
1089 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_snap_count - ' || lv_snap_count);
1090
1091 EXECUTE IMMEDIATE
1092 ' SELECT count(*) FROM ' || lv_base_schema || '.' || lv_mlog_tab_name || ' WHERE nvl(snaptime$$, sysdate+1) > sysdate '
1093 INTO lv_log_count;
1094 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'lv_log_count - ' || lv_log_count);
1095
1096 IF ((lv_tab_count <> 0) AND (lv_snap_count = 0))
1097 OR
1098 (lv_log_count > (lv_snapshot_threshold/100)*lv_tab_count) THEN
1099 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'returning false');
1100 RETURN FALSE; -- Error out since the snapshot is not having any rows
1101 ELSE
1102 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'returning true');
1103 RETURN TRUE; -- snapshot data is correct - so continue the refresh
1104 END IF;
1105
1106 EXCEPTION
1107 WHEN OTHERS THEN
1108 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1109 RETURN FALSE; -- Error out
1110 END SNAPSHOT_DATA_CORRECT;
1111
1112 /*------------------------------------------------------------
1113 Procedure name : Log_Snap_Ref_status
1114 Parameters :
1115 IN : pSnapshot_Name varchar2
1116 : pRefresh_Mode varchar2
1117 : pStatus varchar2
1118 : pElapsed_Time number
1119
1120 Description : This procedure prints the input parameters
1121 in the log file.
1122
1123 ------------------------------------------------------------ */
1124
1125 PROCEDURE Log_Snap_Ref_status
1126 (
1127 pSnapshot_Name IN VARCHAR2
1128 , pRefresh_Mode IN VARCHAR2
1129 , pStatus IN VARCHAR2
1130 , pElapsed_Time IN NUMBER
1131 )
1132 IS
1133
1134 lv_message VARCHAR2(200);
1135 BEGIN
1136 lv_message := RPAD (pSnapshot_Name, 40 , ' ');
1137 lv_message := lv_message || RPAD (pRefresh_Mode, 3, ' ');
1138 lv_message := lv_message || RPAD (pStatus, 10, ' ');
1139 lv_message := lv_message || RPAD (to_char(pElapsed_Time,'99990.9'),
1140 10, ' ');
1141
1142 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_message);
1143
1144 END Log_Snap_Ref_status;
1145
1146 /*------------------------------------------------------------
1147 Procedure name : handle_ORA_12034
1148 Parameters :
1149 IN : pSnapshot_List varchar2
1150 : pRefresh_Param varchar2
1151 : pDegre number
1152 OUT : ERRBUF varchar2
1153 : RETCODE number
1154
1155 Description : This procedure will be called when
1156 we encounter ORA-12034 when performing
1157 fast refresh of snapshot(s).
1158 In this procedure we will refresh the
1159 snapshots one-by-one in the mode (fast or
1160 complete) they were being refreshed originally.
1161 If we encounter the ORA-12034 again, we will do a
1162 complete refresh of that snapshot alone.
1163
1164 After all snapshots are refreshed, we will
1165 perform refresh of all snapshots together to
1166 guarantee the atomicity of transactions.
1167
1168 ------------------------------------------------------------ */
1169
1170 PROCEDURE handle_ORA_12034
1171 (
1172 ERRBUF OUT NOCOPY VARCHAR2
1173 , RETCODE OUT NOCOPY NUMBER
1174 , pSnapshot_List IN VARCHAR2
1175 , pRefresh_Param IN VARCHAR2
1176 , pDegree IN NUMBER)
1177 IS
1178 lv_snapshot_name VARCHAR2(60);
1179 lv_refresh_mode VARCHAR2(1);
1180 lv_total_snapshots NUMBER;
1181 lv_snap_length NUMBER;
1182 lv_task_start_time DATE;
1183 lv_elapsed_mins NUMBER;
1184
1185 BEGIN
1186
1187 -- First Copy the input snapshot strings into local variables.
1188
1189 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'In Procedure: handle_ORA_12034.');
1190 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Refreshing Snapshots One By One');
1191
1192 lv_total_snapshots := length(lv_refresh_param);
1193
1194 FOR i IN 1..lv_total_snapshots LOOP
1195
1196 -- Locate the first ',' in the snapshot string.
1197
1198 lv_snap_length := instr(lv_snapshot_str, ',');
1199
1200 IF (lv_snap_length = 0) THEN
1201 -- Last Snapshot in the list
1202
1203 lv_snapshot_name := lv_snapshot_str;
1204 lv_refresh_mode := lv_refresh_param;
1205 ELSE
1206 lv_snapshot_name := SUBSTR(lv_snapshot_str, 1, lv_snap_length -1);
1207 lv_refresh_mode := SUBSTR(lv_refresh_param,1,1);
1208 lv_snapshot_str := SUBSTR(lv_snapshot_str,lv_snap_length + 1,
1209 LENGTH(lv_snapshot_str));
1210 lv_refresh_param := SUBSTR(lv_refresh_param, 2,
1211 LENGTH(lv_refresh_param));
1212 END IF;
1213
1214 -- Now Refresh the single snapshot.
1215 BEGIN
1216
1217 lv_task_start_time := SYSDATE;
1218 if (v_database_version >= 10) then
1219 DBMS_MVIEW.REFRESH(lv_snapshot_name,
1220 lv_refresh_mode,
1221 atomic_refresh => FALSE,
1222 parallelism => pDegree);
1223 else
1224 DBMS_MVIEW.REFRESH(lv_snapshot_name,
1225 lv_refresh_mode,
1226 parallelism => pDegree);
1227 end if;
1228 lv_elapsed_mins := CEIL((SYSDATE- lv_task_start_time)*14400.0)/10;
1229 COMMIT;
1230 Log_Snap_Ref_status (lv_snapshot_name,
1231 lv_refresh_mode,
1232 'SUCCESS',
1233 lv_elapsed_mins);
1234 EXCEPTION
1235 WHEN OTHERS THEN
1236
1237 lv_elapsed_mins := CEIL((SYSDATE- lv_task_start_time)*14400.0)/10;
1238 IF SQLCODE IN (-12034) THEN
1239
1240 Log_Snap_Ref_status (lv_snapshot_name,
1241 lv_refresh_mode,
1242 'ORA'||to_char(SQLCODE),
1243 lv_elapsed_mins);
1244
1245 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Doing a Complete Refresh of the snapshot.');
1246 BEGIN
1247
1248 lv_task_start_time := SYSDATE;
1249 if (v_database_version >= 10) then
1250 DBMS_MVIEW.REFRESH(lv_snapshot_name,
1251 'C',
1252 atomic_refresh => FALSE,
1253 parallelism => pDegree);
1254 else
1255 DBMS_MVIEW.REFRESH(lv_snapshot_name,
1256 'C',
1257 parallelism => pDegree);
1258 end if;
1259 COMMIT;
1260
1261 lv_elapsed_mins := CEIL((SYSDATE- lv_task_start_time)*14400.0)/10;
1262
1263 Log_Snap_Ref_status (lv_snapshot_name,
1264 'C*',
1265 'SUCCESS',
1266 lv_elapsed_mins);
1267
1268 EXCEPTION
1269 WHEN OTHERS THEN
1270
1271 lv_elapsed_mins := CEIL((SYSDATE- lv_task_start_time)*14400.0)/10;
1272
1273 Log_Snap_Ref_status (lv_snapshot_name,
1274 'C',
1275 'ORA'||to_char(SQLCODE),
1276 lv_elapsed_mins);
1277
1278 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in Complete Refresh of: '||
1279 lv_snapshot_name);
1280 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
1281 ROLLBACK;
1282 RETCODE := G_ERROR;
1283 ERRBUF := SQLERRM;
1284 RETURN;
1285 END;
1286 ELSE
1287 ROLLBACK;
1288 RETCODE := G_ERROR;
1289 ERRBUF := SQLERRM;
1290 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Refreshing Snapshot : '||
1291 lv_snapshot_name || ' , Mode : ' ||
1292 lv_refresh_mode);
1293 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
1294 RETURN;
1295 END IF;
1296 END;
1297 END LOOP;
1298
1299 -- Now Perform a Refresh of ALL snapshots together to
1300 -- guarantee the atomicity of transactions
1301
1302 BEGIN
1303
1304 lv_task_start_time := SYSDATE;
1305
1306 DBMS_MVIEW.REFRESH (pSnapshot_List,
1307 pRefresh_Param,
1308 atomic_refresh => TRUE,
1309 parallelism => pDegree);
1310 COMMIT;
1311
1312 lv_elapsed_mins :=
1313 CEIL((SYSDATE- lv_task_start_time)*14400.0)/10;
1314
1315 EXCEPTION
1316 WHEN OTHERS THEN
1317
1318 ROLLBACK;
1319 RETCODE := G_ERROR;
1320 ERRBUF := SQLERRM;
1321 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Refreshing Snapshots : '||
1322 pSnapshot_List || ' , Mode : ' ||
1323 pRefresh_Param);
1324 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
1325 RETURN;
1326 END;
1327
1328 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'All Snapshots Refreshed Successfully');
1329 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, to_char(lv_elapsed_mins)|| ' Minutes Elapsed.');
1330
1331 RETCODE := G_SUCCESS;
1332 RETURN;
1333
1334
1335 END handle_ORA_12034;
1336
1337
1338 /*-----------------------------------------------------------------------------
1339 Procedure : cancel_submitted_requests
1340
1341 Parameters : p_req_id (IN) - table type which holds the request ids of all
1342 concurrent requests launched
1343
1344 Description : for all request ids in p_req_id, we check the status of the
1345 request, and cancel it, if not already completed
1346 -----------------------------------------------------------------------------*/
1347 PROCEDURE cancel_submitted_requests (p_req_id IN NumTblTyp)
1348 IS
1349 l_call_status boolean;
1350 l_phase varchar2(80);
1351 l_status varchar2(80);
1352 l_dev_phase varchar2(80);
1353 l_dev_status varchar2(80);
1354 l_message varchar2(240);
1355 l_request_id number;
1356 l_canc_req_retval number;
1357
1358 BEGIN
1359 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Cancelling pending/running snapshots');
1360 FOR j IN 1..p_req_id.COUNT LOOP
1361 l_request_id:=p_req_id(j);
1362
1363 l_call_status := FND_CONCURRENT.GET_REQUEST_STATUS
1364 (l_request_id,
1365 '',
1366 '',
1367 l_phase,
1368 l_status,
1369 l_dev_phase,
1370 l_dev_status,
1371 l_message);
1372
1373 IF l_dev_phase <> 'COMPLETE' THEN
1374 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Cancelling request - ' || l_request_id);
1375 l_canc_req_retval := FND_AMP_PRIVATE.cancel_request (l_request_id, l_message);
1376 COMMIT;
1377
1378 IF l_canc_req_retval = 0 THEN
1379 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in cancelling request, ' || l_request_id);
1380 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error - ' || l_message);
1381 END IF;
1382
1383 END IF;
1384
1385 END LOOP;
1386 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Cancelled pending/running snapshots');
1387
1388 EXCEPTION
1389 WHEN OTHERS THEN
1390 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in cancelling requests - ' || SQLERRM);
1391 END cancel_submitted_requests;
1392
1393
1394
1395 FUNCTION CREATE_SNAPSHOT_STRING( pSnapshot_str IN VARCHAR2)
1396 RETURN VARCHAR2
1397 IS
1398
1399 v_dblink VARCHAR2(128);
1400
1401 lv_item_flag NUMBER;
1402 lv_item_subs_falg NUMBER; --SRP Adddition
1403 lv_vendor_flag NUMBER;
1404 lv_customer_flag NUMBER;
1405 lv_bom_flag NUMBER;
1406 lv_reserves_flag NUMBER;
1407 lv_sourcing_flag NUMBER;
1408 lv_wip_flag NUMBER;
1409 lv_iro_flag NUMBER;
1410 lv_ero_flag NUMBER;
1411 lv_saf_stock_flag NUMBER;
1412 lv_po_flag NUMBER;
1413 lv_oh_flag NUMBER;
1414 lv_supplier_cap_flag NUMBER;
1415 lv_supplier_resp_flag NUMBER;
1416 lv_uom_flag NUMBER;
1417 lv_mds_flag NUMBER;
1418 lv_forecast_flag NUMBER;
1419 lv_mps_flag NUMBER;
1420 lv_sales_order_flag NUMBER;
1421 lv_u_sup_dem_flag NUMBER;
1422 lv_nra_flag NUMBER;
1423 lv_src_hist_flag NUMBER;
1424 lv_trip_flag NUMBER;
1425
1426
1427 lv_snapshot_grp_str VARCHAR2(150);
1428 lv_sql_stmt VARCHAR2(2000);
1429
1430 BEGIN
1431
1432 IF pSnapshot_str = 'ALL SNAPSHOTS' THEN
1433
1434 lv_snapshot_grp_str := ' ( ''SCAP'', ''FCST'',''ITEM'',''MRP'',''MRP'',''OH'''
1435 ||',''RES'',''PO'',''ONT'',''SRSP'',''BOM'',''WSH'',''USUD'''
1436 ||',''ERO'',''WIP'',''ISUB'',''EAM'')' ;
1437
1438 ELSIF pSnapshot_str = 'COLL SNAPSHOTS' THEN
1439
1440 SELECT DECODE( A2M_DBLINK,
1441 NULL, NULL_DBLINK,
1442 '@'||A2M_DBLINK)
1443 INTO v_dblink
1444 FROM MRP_AP_APPS_INSTANCES_ALL
1445 WHERE INSTANCE_ID = g_INSTANCE_ID
1446 AND INSTANCE_CODE= g_INSTANCE_CODE
1447 AND nvl(A2M_DBLINK,NULL_DBLINK) = nvl(g_A2M_DBLINK,NULL_DBLINK) ;
1448
1449 lv_sql_stmt := ' SELECT item,ITEM_SUBSTITUTES,supplier, customer, bom, '
1450 ||' reservations, sourcing, wip,internal_repair,external_repair, safety_stock, '
1451 ||' po, oh, supplier_capacity, supplier_response, uom, mds, '
1452 ||' forecast, mps, sales_order,USER_SUPPLY_DEMAND,trip '
1453 ||' FROM msc_coll_parameters'||v_dblink
1454 ||' WHERE instance_id = '||g_INSTANCE_ID;
1455
1456 EXECUTE IMMEDIATE lv_sql_stmt
1457 INTO lv_item_flag,
1458 lv_item_subs_falg,
1459 lv_vendor_flag,
1460 lv_customer_flag,
1461 lv_bom_flag,
1462 lv_reserves_flag,
1463 lv_sourcing_flag,
1464 lv_wip_flag,
1465 lv_iro_flag, -- For Bug 5909379
1466 lv_ero_flag, -- For Bug 5935273
1467 lv_saf_stock_flag,
1468 lv_po_flag,
1469 lv_oh_flag,
1470 lv_supplier_cap_flag,
1471 lv_supplier_resp_flag,
1472 lv_uom_flag,
1473 lv_mds_flag,
1474 lv_forecast_flag,
1475 lv_mps_flag,
1476 lv_sales_order_flag,
1477 lv_u_sup_dem_flag,
1478 lv_trip_flag;
1479
1480
1481 lv_snapshot_grp_str := '';
1482
1483 IF (lv_po_flag = MSC_UTIL.SYS_YES) THEN /* Added lv_reserves_flag for Bug 6144734 */
1484 lv_snapshot_grp_str := lv_snapshot_grp_str || '''PO'',' ;
1485 END IF; -- lv_po_flag
1486
1487 IF (lv_u_sup_dem_flag = MSC_UTIL.SYS_YES) THEN
1488 lv_snapshot_grp_str := lv_snapshot_grp_str || '''USUD'',' ;
1489 END IF; -- lv_po_flag
1490
1491 IF (lv_item_flag = MSC_UTIL.SYS_YES) THEN
1492 lv_snapshot_grp_str := lv_snapshot_grp_str || '''ITEM'',' ;
1493 END IF; -- lv_item_flag
1494
1495 -- SRP Changes
1496 IF (lv_item_subs_falg = MSC_UTIL.SYS_YES) THEN
1497 lv_snapshot_grp_str := lv_snapshot_grp_str || '''ISUB'',' ;
1498 END IF;
1499
1500
1501 IF (lv_oh_flag = MSC_UTIL.SYS_YES) THEN
1502 lv_snapshot_grp_str := lv_snapshot_grp_str || '''OH'',' ;
1503 END IF; -- lv_oh_flag
1504
1505 /* IF (lv_reserves_flag = MSC_UTIL.SYS_YES) THEN
1506 lv_snapshot_grp_str := lv_snapshot_grp_str || '''RES'',' ;
1507 END IF; -- lv_reserves_flag
1508 */
1509 IF (lv_bom_flag = MSC_UTIL.SYS_YES) THEN
1510 lv_snapshot_grp_str := lv_snapshot_grp_str || '''BOM'',' ;
1511 END IF; -- lv_bom_flag
1512
1513 IF ((lv_mps_flag = MSC_UTIL.SYS_YES) or (lv_mds_flag = MSC_UTIL.SYS_YES)) THEN
1514 lv_snapshot_grp_str := lv_snapshot_grp_str || '''MRP'',' ;
1515 END IF; -- lv_mps_flag or lv_mds_flag
1516
1517 IF (lv_forecast_flag = MSC_UTIL.SYS_YES) THEN
1518 lv_snapshot_grp_str := lv_snapshot_grp_str || '''FCST'',' ;
1519 END IF; -- lv_forecast_flag
1520
1521 IF (lv_wip_flag = MSC_UTIL.SYS_YES OR lv_iro_flag = MSC_UTIL.SYS_YES OR lv_ero_flag = MSC_UTIL.SYS_YES OR lv_reserves_flag = MSC_UTIL.SYS_YES) THEN -- Changed For Bug 5909379 SRP Internal Repairs
1522 lv_snapshot_grp_str := lv_snapshot_grp_str || '''WIP'',''EAM'',' ;
1523
1524 IF (lv_ero_flag = MSC_UTIL.SYS_YES OR lv_reserves_flag = MSC_UTIL.SYS_YES) THEN /* For Bug 5937835 */
1525 lv_snapshot_grp_str := lv_snapshot_grp_str || '''ERO'',' ;
1526 END IF ;
1527
1528 END IF; -- lv_wip_flag
1529
1530 IF (lv_supplier_cap_flag = MSC_UTIL.SYS_YES or lv_supplier_cap_flag = ASL_YES_RETAIN_CP) THEN
1531 lv_snapshot_grp_str := lv_snapshot_grp_str || '''SCAP'',' ;
1532 END IF; -- lv_supplier_cap_flag
1533
1534 IF (lv_supplier_resp_flag = MSC_UTIL.SYS_YES) THEN
1535 lv_snapshot_grp_str := lv_snapshot_grp_str || '''SRSP'',' ;
1536 END IF; -- lv_supplier_resp_flag
1537
1538 IF ((lv_sales_order_flag = MSC_UTIL.SYS_YES) OR (g_REFRESH_TYPE = 'I') OR (lv_reserves_flag = MSC_UTIL.SYS_YES)) THEN /* added lv_sales_order_flag for bug 6144734 */
1539 lv_snapshot_grp_str := lv_snapshot_grp_str || '''ONT'',' ;
1540 END IF; -- lv_reserves_flag or lv_sales_order_flag -- or Incremental
1541
1542 IF (lv_trip_flag = MSC_UTIL.SYS_YES) THEN
1543 lv_snapshot_grp_str := lv_snapshot_grp_str || '''WSH'',' ;
1544 END IF; -- lv_trip_flag
1545
1546 IF lv_snapshot_grp_str = '' or lv_snapshot_grp_str is NULL THEN
1547 NULL;
1548 ELSE
1549 lv_snapshot_grp_str := '(' || substr(lv_snapshot_grp_str,1,length(lv_snapshot_grp_str) -1 ) || ' ) ' ;
1550 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_snapshot_grp_str);
1551 END IF;
1552 ELSE
1553
1554 lv_snapshot_grp_str := '(''' ||pSnapshot_str || ''')';
1555
1556 END IF;
1557
1558 RETURN lv_snapshot_grp_str;
1559
1560 END CREATE_SNAPSHOT_STRING;
1561
1562 function eval(exp varchar2)
1563 return boolean as
1564 val number;
1565 begin
1566 if exp = '1' then
1567 val := 1;
1568 else
1569 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Evaluating -- ' || exp);
1570 begin
1571 execute immediate ' select ' || exp || ' from dual ' into val;
1572 exception when others then
1573 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error while evaluating :' || exp);
1574 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
1575 val :=0;
1576 end;
1577 end if;
1578 if val =1 then
1579 return true;
1580 else
1581 return false;
1582 end if;
1583 end;
1584
1585 /*-----------------------------------------------------------------------------
1586 Function : split_refresh
1587
1588 Parameters : p_refresh_param (IN) - string which holds the refresh type
1589 of snapshots to be refreshed
1590
1591 p_snapshot_str (IN) - string which consists of the list of
1592 snapshots which need to be refreshed
1593
1594 Description : this function will launch a standalone refresh snapshot conc
1595 program for every snapshot in p_snapshot_str
1596 -----------------------------------------------------------------------------*/
1597 FUNCTION split_refresh (p_refresh_mode IN NUMBER, p_snapshot_str IN VARCHAR2)
1598 RETURN BOOLEAN IS
1599
1600 lv_total_snapshots NUMBER;
1601 lv_snap_length NUMBER;
1602 lv_p_refresh_mode NUMBER := p_refresh_mode;
1603
1604 lv_snapshot_groups_str VARCHAR2(500);
1605
1606 lv_req_id NumTblTyp := NumTblTyp();
1607 lv_out NUMBER;
1608 lv_failed_req_id NUMBER;
1609
1610 lv_retval boolean;
1611 lv_dummy1 varchar2(32);
1612 lv_dummy2 varchar2(32);
1613 lv_mrp_schema varchar2(30);
1614 lv_prod_short_name varchar2(30);
1615
1616 lv_snapshot_name varchar2(50);
1617 lv_existance_check varchar2(200);
1618
1619 i NUMBER := 1;
1620
1621 TYPE CurTyp is ref cursor;
1622
1623 c_snap CurTyp;
1624
1625 lv_cusros_str varchar2(500);
1626
1627 BEGIN
1628
1629 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Start: split_refresh');
1630
1631 lv_snapshot_groups_str := CREATE_SNAPSHOT_STRING(p_snapshot_str);
1632
1633 IF lv_snapshot_groups_str = '' or lv_snapshot_groups_str is NULL THEN
1634 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'No Snapshots selected');
1635 ELSE
1636
1637 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_snapshot_groups_str);
1638
1639 lv_prod_short_name := AD_TSPACE_UTIL.get_product_short_name(704);
1640 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Product short name - ' || lv_prod_short_name);
1641
1642 lv_mrp_schema := MSC_UTIL.G_MRP_SCHEMA;
1643 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'MRP schema - ' || lv_mrp_schema);
1644
1645 lv_cusros_str := ' select mview_name, existance_check
1646 from msc_coll_snapshots_v
1647 where mview_name in ' || lv_snapshot_groups_str || '
1648 or snapshot_group_string in ' || lv_snapshot_groups_str ;
1649
1650 OPEN c_snap for lv_cusros_str;
1651
1652
1653 LOOP
1654 fetch c_snap into lv_snapshot_name,lv_existance_check;
1655 exit when c_snap%notfound;
1656 if eval(lv_existance_check) then
1657 lv_snapshot_str := lv_snapshot_str || lv_snapshot_name || ',' ;
1658 lv_refresh_param := lv_refresh_param || '?';
1659 lv_num_of_snap := lv_num_of_snap + 1;
1660
1661 lv_req_id.EXTEND(1);
1662 v_request_id := FND_REQUEST.SUBMIT_REQUEST(
1663 'MSC',
1664 'MSCCLRFS',
1665 NULL,
1666 NULL,
1667 FALSE, -- sub request
1668 lv_p_refresh_mode,
1669 lv_snapshot_name,
1670 2, -- degree of parallel
1671 v_refresh_number,
1672 0 -- threshold not used
1673 );
1674
1675 COMMIT;
1676
1677 IF v_request_id = 0 THEN
1678 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in launching program to refresh snapshot, ' || lv_snapshot_name);
1679 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error message - ' || SQLERRM);
1680 cancel_submitted_requests(lv_req_id);
1681 close c_snap;
1682 RETURN FALSE;
1683 ELSE
1684 lv_req_id(lv_num_of_snap) := v_request_id;
1685 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Submitted request ' || lv_req_id(i) || ', to refresh snapshot: ' || lv_snapshot_name);
1686 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '------------------------------------------------');
1687 END IF;
1688
1689 EXIT WHEN v_request_id = 0;
1690 end if; --eval (lv_existance_check)
1691 END LOOP;
1692 close c_snap;
1693
1694 --removing the additional coma(,) at the end
1695 if lv_num_of_snap > 0 then
1696 lv_snapshot_str := substr(lv_snapshot_str,1,length(lv_snapshot_str) -1 ) ;
1697 end if;
1698
1699 FOR j IN 1..lv_req_id.COUNT LOOP
1700 wait_for_request(lv_req_id(j), 30, lv_out);
1701
1702 IF lv_out = 2 THEN
1703 lv_failed_req_id := lv_req_id(j);
1704
1705 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ERROR : Please see the log files of request, ' || lv_failed_req_id || ', for details');
1706 cancel_submitted_requests(lv_req_id);
1707
1708 RETURN FALSE;
1709 END IF;
1710
1711 END LOOP;
1712
1713 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Successfully refreshed all snapshots');
1714 END IF;
1715 RETURN TRUE;
1716
1717 EXCEPTION
1718 WHEN OTHERS THEN
1719 ROLLBACK;
1720 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Refreshing Snapshots : ' || SQLERRM);
1721
1722 IF lv_req_id.COUNT > 0 THEN
1723 cancel_submitted_requests(lv_req_id);
1724 END IF;
1725
1726 RETURN FALSE;
1727
1728 END split_refresh;
1729
1730 PROCEDURE REFRESH_SNAPSHOT(
1731 ERRBUF OUT NOCOPY VARCHAR2,
1732 RETCODE OUT NOCOPY NUMBER,
1733 pREFRESH_MODE IN NUMBER,
1734 pSNAPSHOT_NAME IN VARCHAR2,
1735 pNUMBER_OF_ROWS IN NUMBER,
1736 pDEGREE IN NUMBER,
1737 pCP_ENABLED IN NUMBER,
1738 pREFRESH_TYPE IN VARCHAR2,
1739 pCALLING_MODULE IN NUMBER,
1740 pINSTANCE_ID IN NUMBER,
1741 pINSTANCE_CODE IN VARCHAR2,
1742 pA2M_DBLINK IN VARCHAR2)
1743 IS
1744
1745 v_lrn NUMBER;
1746 v_old_lrn NUMBER;
1747 v_apps_lrn NUMBER;
1748 --lv_refresh_number NUMBER;
1749 lv_complete_ref_flow NUMBER:= 2;
1750 lv_initialization_flag NUMBER:= 2;
1751 lv_task_start_time DATE;
1752 lv_elapsed_mins NUMBER;
1753
1754 lv_standard_ret NUMBER;
1755 lv_wfd_ret_code NUMBER;
1756 lv_wfd_err_msg VARCHAR2(400);
1757 lv_last_ref_type VARCHAR2(8);
1758
1759 lv_refresh_mode NUMBER := pREFRESH_MODE;
1760 lv_snapshot_name VARCHAR2(30) := pSNAPSHOT_NAME;
1761 lv_NUMBER_OF_ROWS NUMBER := nvl(pNUMBER_OF_ROWS,0);
1762 lv_DEGREE NUMBER := nvl(pDEGREE,0);
1763
1764 lv_flm_appl_short_name VARCHAR2(50);
1765 CONFIG_BOM_NOT_FOUND EXCEPTION;
1766 INDIVIDUAL_REFRESH_ERROR EXCEPTION;
1767
1768 lv_base_table_name VARCHAR2(30);
1769 lv_base_schema VARCHAR2(30);
1770 lv_snap_log_schema VARCHAR2(30);
1771 lv_mlog_tab_name VARCHAR2(30);
1772
1773 lv_so_sn_flag NUMBER;
1774 lv_wip_sn_flag NUMBER;
1775
1776
1777 lv_wip_flag NUMBER;
1778 lv_sales_order_flag NUMBER;
1779
1780
1781
1782 CURSOR c_item_name_seg IS
1783 select APPLICATION_COLUMN_NAME
1784 from FND_ID_FLEX_SEGMENTS
1785 where ID_FLEX_CODE = 'MSTK'
1786 and ENABLED_FLAG = 'Y'
1787 and DISPLAY_FLAG = 'Y'
1788 and APPLICATION_ID = 401
1789 and ID_FLEX_NUM = 101
1790 order by SEGMENT_NUM;
1791
1792 lv_item_name_kfv varchar2(2000) := NULL;
1793 delimiter varchar2(10);
1794
1795 ----- New variables for PREPLACE ----
1796 v_dblink VARCHAR2(128);
1797
1798 lv_sql_stmt VARCHAR2(15000);
1799 lv_sql_stmt1 VARCHAR2(1500);
1800
1801 dest_cursor INTEGER;
1802 ignore INTEGER;
1803
1804 lv_setup_source_objs NUMBER;
1805 SOURCE_SETUP_ERROR EXCEPTION;
1806 lv_cursor_stmt varchar2(1000);
1807
1808 -- LRD for doing an incremental refresh of region to site mapping.
1809 max_lrd DATE;
1810
1811
1812 BEGIN
1813
1814 -- setting the global variables
1815 g_REFRESH_TYPE := pREFRESH_TYPE;
1816 g_CALLING_MODULE := pCALLING_MODULE;
1817 g_INSTANCE_ID := pINSTANCE_ID ;
1818 g_INSTANCE_CODE := pINSTANCE_CODE;
1819 g_A2M_DBLINK := pA2M_DBLINK;
1820
1821 BEGIN
1822 DBMS_UTILITY.DB_VERSION (lv_db_version,lv_db_cmpt_version);
1823 v_database_version := to_number(substr(lv_db_version,1,instrb(lv_db_version,'.')-1) );
1824 EXCEPTION
1825 WHEN OTHERS THEN
1826 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error getting DataBase version : ' || SQLERRM);
1827 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Assuming 9i to continue...');
1828 v_database_version := 9;
1829 --lv_db_version := v_version_9i;
1830 --RAISE;
1831 END;
1832
1833 if pA2M_DBLINK is null then
1834 v_a2m_dblink := pA2M_DBLINK ;
1835 else
1836 v_a2m_dblink := '@'||pA2M_DBLINK;
1837 end if ;
1838 v_cp_enabled := SYS_YES;
1839 v_refresh_type := pREFRESH_TYPE;
1840 lv_DEGREE := LEAST(lv_DEGREE,10);
1841
1842 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'ref mode - ' || pREFRESH_MODE);
1843 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'snp name - ' || pSNAPSHOT_NAME);
1844
1845 dbms_output.put_line('The degree of parallelism for Refreshing snapshots is set to: '||lv_DEGREE);
1846
1847 SELECT MRP_AP_REFRESH_S.NEXTVAL
1848 INTO v_refresh_number
1849 FROM DUAL;
1850
1851
1852 IF pCALLING_MODULE = G_COLLECTIONS THEN /* Forward Port Bug 2904050 */
1853
1854 BEGIN
1855 SELECT max(LRD)
1856 INTO max_lrd
1857 FROM MRP_AP_APPS_INSTANCES_ALL;
1858 EXCEPTION
1859 WHEN NO_DATA_FOUND THEN
1860 RETCODE:= G_ERROR;
1861 ERRBUF := 'NO_INSTANCE_FOUND';
1862
1863 WHEN OTHERS THEN
1864 RAISE;
1865 END;
1866
1867
1868 BEGIN
1869 SELECT LRN, DECODE( A2M_DBLINK,
1870 NULL, NULL_DBLINK,
1871 '@'||A2M_DBLINK)
1872 INTO v_old_lrn, v_dblink
1873 FROM MRP_AP_APPS_INSTANCES_ALL
1874 WHERE INSTANCE_ID = pINSTANCE_ID
1875 AND INSTANCE_CODE= pINSTANCE_CODE
1876 AND nvl(A2M_DBLINK,NULL_DBLINK) = nvl(pA2M_DBLINK,NULL_DBLINK) ;
1877 EXCEPTION
1878 WHEN NO_DATA_FOUND THEN
1879 RETCODE:= G_ERROR;
1880 ERRBUF := 'NO_INSTANCE_FOUND';
1881 WHEN OTHERS THEN
1882 RAISE;
1883 END;
1884
1885 /* Frontport Bug 2904050 - We will pass the SO_LRN from msc_coll_parameters, and if it is null, we will pass APPS_LRN from msc_apps_instances for the explosion of
1886 SMCs */
1887
1888 lv_sql_stmt := ' SELECT apps_lrn '
1889 ||' FROM msc_apps_instances'||v_dblink
1890 ||' WHERE instance_id = '||pINSTANCE_ID;
1891
1892
1893 EXECUTE IMMEDIATE lv_sql_stmt
1894 INTO v_apps_lrn;
1895
1896 lv_sql_stmt := ' SELECT nvl(min(so_lrn),'||to_char(v_apps_lrn)||')'
1897 ||' FROM msc_instance_orgs'||v_dblink
1898 ||' WHERE sr_instance_id = '||pINSTANCE_ID;
1899
1900 EXECUTE IMMEDIATE lv_sql_stmt
1901 INTO v_lrn;
1902
1903 END IF;
1904
1905
1906 /* NEW Patching Strategy */
1907 /* Based on the profile option setting MSC_SOURCE_SETUP Setup the Source Objects */
1908
1909 SELECT DECODE(NVL(fnd_profile.value('MSC_SOURCE_SETUP') ,'Y'), 'Y',1 ,2)
1910 INTO lv_setup_source_objs
1911 FROM DUAL;
1912
1913 IF (lv_setup_source_objs = 1) THEN
1914 IF SETUP_SOURCE_OBJECTS = FALSE THEN
1915 RAISE SOURCE_SETUP_ERROR;
1916 ELSE
1917 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Source set up completed successfully');
1918 END IF;
1919 END IF;
1920
1921 BEGIN
1922 -- after mtl_supply_sn is created the transaction_id will be zero,
1923 -- we need to do a complete refresh on mtl_supply_sn.
1924 -- if transaction_id = 0 exists, it means mtl_supply_sn is just created.
1925 lv_cursor_stmt :=
1926 ' select 1'
1927 ||' from mrp_sn_supply'
1928 ||' where transaction_id= 0'
1929 ||' and rownum=1';
1930
1931 EXECUTE IMMEDIATE lv_cursor_stmt INTO lv_initialization_flag;
1932
1933 EXCEPTION
1934 WHEN OTHERS THEN NULL;
1935
1936 END;
1937
1938 BEGIN
1939
1940 IF split_refresh (lv_refresh_mode, pSNAPSHOT_NAME) = TRUE THEN
1941
1942 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Successfully refreshed these Snapshots :');
1943 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,1,100) );
1944 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,101,100) );
1945 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,201,100) );
1946 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,301,100) );
1947 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,401,100) );
1948 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, substr(lv_snapshot_str,501) );
1949
1950 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Type of Refresh done: '||lv_refresh_param);
1951
1952 IF pCALLING_MODULE = G_COLLECTIONS and lv_num_of_snap > 1 THEN
1953
1954 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Doing a fast refresh of all snapshots...');
1955 BEGIN
1956
1957 lv_refresh_param := replace(lv_refresh_param, 'C', 'F');
1958 lv_refresh_param := replace(lv_refresh_param, '?', 'F');
1959 DBMS_MVIEW.REFRESH ( lv_snapshot_str,lv_refresh_param,parallelism =>lv_DEGREE,atomic_refresh => TRUE);
1960 COMMIT;
1961
1962 RETCODE := G_SUCCESS;
1963
1964 EXCEPTION
1965 WHEN OTHERS THEN
1966 MSC_UTIL.G_ERROR_STACK:= DBMS_UTILITY.FORMAT_ERROR_STACK;
1967 IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
1968 OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
1969 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
1970 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
1971 RAISE;
1972 END IF;
1973 IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12034') > 0 THEN
1974 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || SQLERRM);
1975 handle_ORA_12034 (ERRBUF,
1976 RETCODE,
1977 lv_snapshot_str,
1978 lv_refresh_param,
1979 lv_DEGREE);
1980
1981 IF (RETCODE = G_ERROR) THEN
1982 RAISE;
1983 END IF;
1984 ELSE
1985 ROLLBACK;
1986 RETCODE:= G_ERROR;
1987 ERRBUF:= SQLERRM;
1988 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, MSC_UTIL.G_ERROR_STACK);
1989 RAISE;
1990 END IF;
1991 END ;
1992
1993 END IF; --readconsistency
1994 ELSE
1995 ROLLBACK;
1996 RETCODE:= G_ERROR;
1997 ERRBUF:= SQLERRM;
1998 RAISE INDIVIDUAL_REFRESH_ERROR;
1999 END IF;
2000
2001 EXCEPTION
2002 WHEN OTHERS THEN
2003 ROLLBACK;
2004 RETCODE:= G_ERROR;
2005 ERRBUF:= SQLERRM;
2006 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
2007 RAISE;
2008 END;
2009
2010
2011 IF lv_initialization_flag = 1 THEN
2012
2013 lv_sql_stmt:= 'TRUNCATE TABLE '|| MSC_UTIL.G_MRP_SCHEMA||'.MRP_AD_SUPPLY';
2014 EXECUTE IMMEDIATE lv_sql_stmt;
2015
2016 END IF;
2017
2018
2019 IF pCALLING_MODULE = G_COLLECTIONS THEN
2020 BEGIN
2021
2022 IF (pREFRESH_TYPE = 'T' ) THEN
2023
2024 lv_sql_stmt:=
2025 'BEGIN MSC_CL_PULL.SALES_ORDER_REFRESH_TYPE'||v_dblink||'('
2026 ||' :pINSTANCE_ID, '
2027 ||' :lv_so_sn_flag );'
2028 ||'END;';
2029
2030 EXECUTE IMMEDIATE lv_sql_stmt
2031 USING IN pINSTANCE_ID,
2032 OUT lv_so_sn_flag;
2033
2034 END IF;
2035
2036 lv_sql_stmt := ' SELECT sales_order '
2037 ||' , wip, wip_sn_flag '
2038 ||' FROM msc_coll_parameters'||v_dblink
2039 ||' WHERE instance_id = '||pINSTANCE_ID ;
2040
2041
2042 EXECUTE IMMEDIATE lv_sql_stmt
2043 INTO lv_sales_order_flag,
2044 lv_wip_flag,
2045 lv_wip_sn_flag;
2046
2047 EXCEPTION
2048 WHEN NO_DATA_FOUND THEN
2049 RETCODE:= G_ERROR;
2050 ERRBUF := 'Please verify Setup DBLinks setup in Source Database';
2051 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
2052 WHEN OTHERS THEN
2053 RETCODE:= G_ERROR;
2054 ERRBUF:= SQLERRM;
2055 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
2056 RAISE;
2057 END;
2058
2059 IF (pREFRESH_TYPE = 'T' ) THEN
2060 IF (lv_sales_order_flag = 1) AND (lv_so_sn_flag = 4) THEN
2061 /* If sales order is to be Targeted mode in Continuous collections */
2062 v_lrn := -1;
2063 END IF;
2064 ELSE
2065 /* For all other collections except continuous collections */
2066 IF (lv_sales_order_flag = 1) AND (pREFRESH_TYPE <> 'I' )THEN
2067 v_lrn := -1;
2068 END IF;
2069 END IF;
2070
2071 IF (pREFRESH_TYPE = 'T' ) THEN
2072 IF (lv_wip_flag = 1) AND (lv_wip_sn_flag = 4) THEN
2073 /* If wip is to be Targeted mode in Continuous collections */
2074 lv_complete_ref_flow := 1;
2075 END IF;
2076 ELSE
2077 /* For all other collections except continuous collections */
2078 IF (lv_wip_flag = 1 ) AND (pREFRESH_TYPE <> 'I') THEN
2079 lv_complete_ref_flow := 1;
2080 END IF;
2081 END IF;
2082
2083 SELECT application_short_name
2084 INTO lv_flm_appl_short_name
2085 FROM fnd_application
2086 WHERE application_id=714;
2087
2088 IF (CHECK_INSTALL(lv_flm_appl_short_name)) THEN
2089 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Populating Flow Demand, Complete Refresh Flow: ' ||
2090 to_char (lv_complete_ref_flow));
2091
2092 lv_task_start_time := SYSDATE;
2093
2094 BEGIN
2095 IF lv_complete_ref_flow = 1 THEN
2096 MRP_FLOW_DEMAND.Main_Flow_Demand( -1,
2097 lv_wfd_ret_code,
2098 lv_wfd_err_msg);
2099 ELSE
2100 MRP_FLOW_DEMAND.Main_Flow_Demand( v_refresh_number,
2101 lv_wfd_ret_code,
2102 lv_wfd_err_msg);
2103 END IF;
2104
2105 lv_elapsed_mins := CEIL((SYSDATE- lv_task_start_time)*14400.0)/10;
2106 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, to_char(lv_elapsed_mins)|| ' Minutes Elapsed.');
2107
2108 EXCEPTION
2109 WHEN OTHERS THEN
2110 NULL;
2111 END;
2112 COMMIT;
2113 END IF;
2114
2115 BEGIN
2116 IF (pREFRESH_TYPE = 'P' or pREFRESH_TYPE = 'T')
2117 and (lv_sales_order_flag =2) THEN
2118 /* if Sales order is NO (for targeted and Continuous
2119 * collections) , dont explode ATO */
2120 NULL;
2121 ELSE
2122 IF (v_explode_ato = 'Y') THEN
2123 -- explode ATO only if the profile is YES
2124 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Exploding SMC demand, LRN: ' || to_char (v_lrn));
2125
2126 lv_task_start_time := SYSDATE;
2127
2128 lv_standard_ret :=
2129 MRP_EXPL_STD_MANDATORY.Explode_ATO_SM_COMPS(v_lrn);
2130
2131 lv_elapsed_mins :=
2132 CEIL((SYSDATE- lv_task_start_time)*14400.0)/10;
2133
2134 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, to_char(lv_elapsed_mins)|| ' Minutes Elapsed.');
2135
2136 IF lv_standard_ret = 2 THEN
2137 RAISE CONFIG_BOM_NOT_FOUND;
2138 END IF;
2139
2140 END IF;
2141 END IF;
2142
2143 EXCEPTION
2144 WHEN CONFIG_BOM_NOT_FOUND THEN
2145 RETCODE:= G_WARNING;
2146 ERRBUF := 'Please check the warning message in the logfile';
2147 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ERRBUF);
2148 WHEN OTHERS THEN
2149 NULL;
2150 END;
2151 COMMIT;
2152
2153 BEGIN
2154 select '||'''||CONCATENATED_SEGMENT_DELIMITER||'''||'
2155 into delimiter
2156 from fnd_id_flex_structures
2157 where ID_FLEX_CODE = 'MSTK'
2158 and APPLICATION_ID = 401
2159 and ID_FLEX_NUM = 101;
2160
2161 for c_rec in c_item_name_seg loop
2162 if (lv_item_name_kfv is null) then
2163 lv_item_name_kfv := 'x.'||c_rec.APPLICATION_COLUMN_NAME;
2164 else
2165 lv_item_name_kfv := lv_item_name_kfv || delimiter ||'x.'||c_rec.APPLICATION_COLUMN_NAME;
2166 end if;
2167 end loop;
2168 EXCEPTION
2169 WHEN OTHERS THEN
2170 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' An error occured in building the item name from KFV');
2171 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2172 lv_item_name_kfv := 'x.SEGMENT1';
2173 END;
2174
2175
2176 lv_sql_stmt := 'UPDATE MRP_AP_APPS_INSTANCES_ALL '
2177 ||' SET LRN= MRP_AP_REFRESH_S.CURRVAL, '
2178 -- Resource Start Time. This time will be updated before the snapshot refresh.
2179 -- LRD= SYSDATE,
2180 ||' LAST_UPDATE_DATE= SYSDATE,'
2181 ||' LAST_UPDATED_BY= FND_GLOBAL.USER_ID,'
2182 ||' BOM_HOUR_UOM_CODE =FND_PROFILE.VALUE(''BOM:HOUR_UOM_CODE''),'
2183 ||' MRP_MPS_CONSUMPTION =DECODE( FND_PROFILE.VALUE(''MRP_MPS_CONSUMPTION''),'
2184 ||' ''Y'', 1,'
2185 ||' ''1'', 1,'
2186 ||' 2),'
2187 ||' MRP_SHIP_ARRIVE_FLAG =DECODE( FND_PROFILE.VALUE(''MRP_SHIP_ARRIVE_FLAG''),'
2188 ||' ''Y'', 1,'
2189 ||' ''1'', 1,'
2190 ||' 2),'
2191 ||' CRP_SPREAD_LOAD =DECODE( FND_PROFILE.VALUE(''CRP_SPREAD_LOAD''),'
2192 ||' ''Y'', 1,'
2193 ||' ''1'', 1,'
2194 ||' 2),'
2195 ||' MSO_ITEM_DMD_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_ITEM_DMD_PENALTY'')),'
2196 ||' MSO_ITEM_CAP_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_ITEM_CAP_PENALTY'')),'
2197 ||' MSO_ORG_DMD_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_ORG_DMD_PENALTY'')),'
2198 ||' MSO_ORG_ITEM_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_ORG_ITEM_PENALTY'')),'
2199 ||' MSO_ORG_RES_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_ORG_RES_PENALTY'')),'
2200 ||' MSO_ORG_TRSP_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_ORG_TRSP_PENALTY'')),'
2201 ||' MSC_AGGREG_RES_NAME =TO_NUMBER( FND_PROFILE.VALUE(''MSC_AGGREG_RES_NAME'')),'
2202 ||' MSO_RES_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_RES_PENALTY'')),'
2203 ||' MSO_SUP_CAP_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_SUP_CAP_PENALTY'')),'
2204 ||' MSC_BOM_SUBST_PRIORITY =TO_NUMBER( FND_PROFILE.VALUE(''MSC_BOM_SUBST_PRIORITY'')),'
2205 ||' MSO_TRSP_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_TRSP_PENALTY'')),'
2206 ||' MSC_ALT_BOM_COST =TO_NUMBER( FND_PROFILE.VALUE(''MSC_ALT_BOM_COST'')),'
2207 ||' MSO_FCST_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_FCST_PENALTY'')),'
2208 ||' MSO_SO_PENALTY =TO_NUMBER( FND_PROFILE.VALUE(''MSO_SO_PENALTY'')),'
2209 -- MSC_ALT_OP_RES =TO_NUMBER( FND_PROFILE.VALUE('MSC_RESOURCE_TYPE')),
2210 ||' MSC_ALT_RES_PRIORITY =TO_NUMBER( FND_PROFILE.VALUE(''MSC_ALT_RES_PRIORITY'')),'
2211 ||' MSC_SIMUL_RES_SEQ =TO_NUMBER( FND_PROFILE.VALUE(''MSC_SIMUL_RES_SEQ'')),'
2212 ||' MRP_BIS_AV_DISCOUNT =NVL(TO_NUMBER(FND_PROFILE.VALUE(''MRP_BIS_AV_DISCOUNT'')),0),'
2213 ||' MRP_BIS_PRICE_LIST =TO_NUMBER( FND_PROFILE.VALUE(''MRP_BIS_PRICE_LIST'')),'
2214 ||' MSC_DMD_PRIORITY_FLEX_NUM=NVL(TO_NUMBER( FND_PROFILE.VALUE(''MSC_DMD_PRIORITY_FLEX_NUM'')),0),'
2215 ||' MSC_BATCHABLE_FLAG =TO_NUMBER( FND_PROFILE.VALUE(''MSC_BATCHABLE_FLAG'')),'
2216 ||' MSC_BATCHING_WINDOW =TO_NUMBER( FND_PROFILE.VALUE(''MSC_BATCHING_WINDOW'')),'
2217 ||' MSC_MIN_CAPACITY =TO_NUMBER( FND_PROFILE.VALUE(''MSC_MIN_CAPACITY'')),'
2218 ||' MSC_MAX_CAPACITY =TO_NUMBER( FND_PROFILE.VALUE(''MSC_MAX_CAPACITY'')),'
2219 ||' MSC_UNIT_OF_MEASURE =TO_NUMBER( FND_PROFILE.VALUE(''MSC_UNIT_OF_MEASURE'')),'
2220 ||' VALIDATION_ORG_ID =NVL(TO_NUMBER( FND_PROFILE.VALUE(''MSC_ORG_FOR_BOM_EXPLOSION'')),to_number(null)),'
2221 ||' MSC_SO_OFFSET_DAYS =TO_NUMBER( NVL(FND_PROFILE.VALUE'||v_dblink||'(''MSC_SO_OFFSET_DAYS''),99999)),'
2222 ||' ITEM_NAME_FROM_KFV = :lv_item_name_kfv '
2223 ||' WHERE INSTANCE_ID = :pINSTANCE_ID'
2224 ||' AND INSTANCE_CODE= :pINSTANCE_CODE'
2225 ||' AND nvl(A2M_DBLINK,'||''''||NULL_DBLINK ||''''||') = nvl(:pA2M_DBLINK,'||''''||NULL_DBLINK||''''||') ';
2226
2227 Execute immediate lv_sql_stmt
2228 USING lv_item_name_kfv,
2229 pINSTANCE_ID,
2230 pINSTANCE_CODE,
2231 pA2M_DBLINK;
2232 COMMIT;
2233
2234 IF lv_standard_ret = 2 AND RETCODE = G_WARNING THEN
2235 RETCODE := G_WARNING;
2236 ELSE
2237 RETCODE:= G_SUCCESS;
2238 END IF;
2239
2240 END IF; --pCALLING_MODULE = G_COLLECTIONS
2241
2242
2243 /* SITE TO REGION MAPPING */
2244 IF (pCALLING_MODULE = G_COLLECTIONS ) AND ( pREFRESH_TYPE='C' OR pREFRESH_TYPE='P') THEN
2245 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'before calling MRP_CL_FUNCTION.MAP_REGION_TO_SITE');
2246 IF MRP_CL_FUNCTION.MAP_REGION_TO_SITE(max_lrd)=1 THEN NULL; END IF;
2247 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'after calling MRP_CL_FUNCTION.MAP_REGION_TO_SITE');
2248 END IF;
2249
2250 RETCODE:= G_SUCCESS;
2251 ERRBUF:= null;
2252 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Refresh Snapshot process completed successfully');
2253 return;
2254
2255 EXCEPTION
2256 WHEN SOURCE_SETUP_ERROR THEN
2257 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Setting Up Source Objects');
2258 RETCODE:= G_ERROR;
2259
2260 ERRBUF:= SQLERRM;
2261
2262 WHEN INDIVIDUAL_REFRESH_ERROR THEN
2263 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2264 RETCODE:= G_ERROR;
2265 ERRBUF:= SQLERRM;
2266
2267 WHEN TRUNCATE_LOG_ERROR THEN
2268 RETCODE:= G_ERROR;
2269 ERRBUF:= SQLERRM;
2270 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2271
2272 WHEN OTHERS THEN
2273
2274 ROLLBACK;
2275
2276 RETCODE:= G_ERROR;
2277
2278 ERRBUF:= SQLERRM;
2279
2280 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2281
2282 END REFRESH_SNAPSHOT;
2283
2284 FUNCTION GET_REFRESH_TYPE ( p_base_schema IN VARCHAR2,
2285 p_base_table_name IN VARCHAR2,
2286 p_snapshot_name IN VARCHAR2)
2287 RETURN varchar2
2288 IS
2289 lv_refresh_type varchar2(1);
2290 lv_initialization_flag NUMBER :=2;
2291 BEGIN
2292
2293
2294
2295 IF SNAPSHOT_DATA_CORRECT(p_base_schema, p_base_table_name,p_snapshot_name) THEN
2296
2297 lv_refresh_type := '?';
2298
2299 --override rules...
2300
2301 IF ( (p_snapshot_name = 'MTL_SUPPLY_SN') OR
2302 (p_snapshot_name = 'MTL_U_SUPPLY_SN') OR
2303 (p_snapshot_name = 'MTL_U_DEMAND_SN')
2304 ) THEN
2305
2306 BEGIN
2307 EXECUTE IMMEDIATE
2308 ' select 1'
2309 ||' from mrp_sn_supply'
2310 ||' where transaction_id= 0'
2311 ||' and rownum=1'
2312 INTO lv_initialization_flag;
2313 EXCEPTION
2314 WHEN OTHERS THEN NULL;
2315 END;
2316 IF (lv_initialization_flag = 1 ) THEN
2317 lv_refresh_type := 'C';
2318 END IF;
2319 END IF;
2320
2321
2322 ELSE -- SNAPSHOT_DATA_CORRECT
2323 /* launch the snapshot in complete mode without erroring out.*/
2324 lv_refresh_type := 'C';
2325 END IF;
2326
2327 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Optimal refresh type found:' ||lv_refresh_type );
2328 return lv_refresh_type;
2329
2330 END GET_REFRESH_TYPE;
2331
2332 PROCEDURE REFRESH_SINGLE_SNAPSHOT(
2333 ERRBUF OUT NOCOPY VARCHAR2,
2334 RETCODE OUT NOCOPY NUMBER,
2335 pREFRESH_MODE IN NUMBER,
2336 pSNAPSHOT_NAME IN VARCHAR2,
2337 pDEGREE IN NUMBER,
2338 pCURRENT_LRN IN NUMBER,
2339 p_NUMBER_OF_ROWS IN NUMBER
2340 ) IS
2341 lv_erp_product_code number;
2342 lv_last_refresh_date NUMBER;
2343 lv_last_ref_type VARCHAR2(8);
2344
2345 lv_base_table_name VARCHAR2(30);
2346 lv_base_schema VARCHAR2(30);
2347
2348 lv_refresh_param VARCHAR2(1);
2349 lv_snapshot_name VARCHAR2(100) := pSNAPSHOT_NAME;
2350 lv_snap_str VARCHAR2(150) := MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name;
2351 BEGIN
2352
2353 BEGIN
2354 DBMS_UTILITY.DB_VERSION (lv_db_version,lv_db_cmpt_version);
2355 v_database_version := to_number(substr(lv_db_version,1,instrb(lv_db_version,'.')-1) );
2356 EXCEPTION
2357 WHEN OTHERS THEN
2358 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error getting DataBase version : ' || SQLERRM);
2359 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Assuming 9i to continue...');
2360 v_database_version := 9;
2361 --lv_db_version := v_version_9i;
2362 --RAISE;
2363 END;
2364
2365 --Set the global variable for triggers.
2366 IF pCURRENT_LRN = -1 THEN
2367 SELECT MRP_AP_REFRESH_S.NEXTVAL
2368 INTO v_refresh_number
2369 FROM DUAL;
2370 ELSE
2371 v_refresh_number := pCURRENT_LRN;
2372 END IF;
2373
2374 IF (pREFRESH_MODE = 1) THEN -- fast
2375 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'single refresh, fast');
2376 lv_refresh_param := 'F';
2377 ELSIF (pREFRESH_MODE = 2) THEN --complete
2378 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'single refresh, complete');
2379 lv_refresh_param := 'C';
2380 ELSE -- automatic or force
2381 SELECT master_table, erp_product_code
2382 INTO lv_base_table_name,lv_erp_product_code
2383 FROM MSC_COLL_SNAPSHOTS_V
2384 WHERE mview_name = lv_snapshot_name;
2385
2386 lv_base_schema := MSC_UTIL.GET_SCHEMA_NAME(lv_erp_product_code);
2387
2388 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Master-Table = '|| lv_base_schema || '.'|| lv_base_table_name);
2389
2390 --If logs are truncated, do Complete refresh, else let system decide best refresh method
2391 IF pREFRESH_MODE = 3 AND TRUNC_SNAP_LOG(p_NUMBER_OF_ROWS,lv_base_schema,
2392 lv_base_table_name,lv_snapshot_name,pDEGREE) THEN
2393 lv_refresh_param := 'C';
2394 ELSE
2395 lv_refresh_param :=GET_REFRESH_TYPE(lv_base_schema, lv_base_table_name,lv_snapshot_name);
2396 END IF;
2397 END IF; -- pREFRESH_MODE
2398
2399 --Refreshing the snapshot
2400 BEGIN
2401 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'Refreshing the snapshot : ' || lv_snap_str || ' in ' || lv_refresh_param || ' mode with degree ' || pDEGREE);
2402 if (v_database_version >= 10) then
2403 DBMS_MVIEW.REFRESH(LIST => lv_snap_str,
2404 METHOD => lv_refresh_param,
2405 atomic_refresh => FALSE,
2406 parallelism => pDEGREE);
2407 else
2408 DBMS_MVIEW.REFRESH(LIST => lv_snap_str,
2409 METHOD => lv_refresh_param,
2410 parallelism => pDEGREE);
2411 end if;
2412 EXCEPTION
2413 WHEN OTHERS THEN
2414 MSC_UTIL.G_ERROR_STACK := DBMS_UTILITY.FORMAT_ERROR_STACK;
2415 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2416 IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-1578') > 0
2417 OR instr(MSC_UTIL.G_ERROR_STACK ,'ORA-36040') > 0 THEN
2418 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error in refresh snapshot program : ' || MSC_UTIL.G_ERROR_STACK);
2419 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Please Launch CP "DROP Collection SnapShots" with option "ALL SNAPSHOTS"');
2420 RAISE;
2421 END IF;
2422
2423
2424 IF instr(MSC_UTIL.G_ERROR_STACK ,'ORA-12034') > 0 THEN
2425 handle_ORA_12034 (ERRBUF,
2426 RETCODE,
2427 lv_snap_str,
2428 lv_refresh_param,
2429 pDEGREE);
2430
2431 IF (RETCODE = G_ERROR) THEN
2432 RAISE;
2433 END IF;
2434 lv_refresh_param := '?';
2435 ELSE
2436 RAISE;
2437 END IF;
2438 END;
2439
2440 COMMIT;
2441
2442 /* updating the msc_coll_snapshots lookup with mode in which snapshot was refreshed */
2443 IF lv_refresh_param = '?' THEN
2444
2445 EXECUTE IMMEDIATE ' SELECT DECODE(last_refresh_type,''COMPLETE'',''C'', ''F'') '
2446 || ' FROM all_mviews WHERE mview_name = :lv_snapshot_name '
2447 || ' AND owner = :lv_snap_schema '
2448 INTO lv_refresh_param -- overwrite existing value with the actual refresh done...
2449 USING lv_snapshot_name, MSC_UTIL.G_APPS_SCHEMA;
2450
2451 END IF;
2452
2453 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Refreshed the Snapshot: ' || lv_snap_str );
2454 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Type of Refresh done : ' || lv_refresh_param);
2455 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, '---------------------------------------------');
2456
2457
2458 IF lv_refresh_param = 'C' THEN
2459 UPDATE MSC_COLL_SNAPSHOTS_V
2460 SET complete_refresh_timestamp = to_char(sysdate,'YYYY-MM-DD HH:MI:SS')
2461 WHERE MVIEW_NAME = lv_snapshot_name ;
2462
2463 COMMIT;
2464 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'successfully updated the complete refresh time in fnd_lookup_values');
2465 END IF;
2466
2467 EXCEPTION
2468 WHEN OTHERS THEN
2469 ROLLBACK;
2470 RETCODE:= G_ERROR;
2471 ERRBUF:= SQLERRM;
2472 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error refreshing individual snapshot : ' || lv_snap_str);
2473 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error : '|| ERRBUF);
2474 RAISE;
2475 END REFRESH_SINGLE_SNAPSHOT;
2476
2477 PROCEDURE DROP_SNAPSHOT(
2478 ERRBUF OUT NOCOPY VARCHAR2,
2479 RETCODE OUT NOCOPY NUMBER,
2480 p_snapshot_str IN VARCHAR2)
2481 IS
2482
2483 lv_sql_stmt VARCHAR2(2000);
2484 lv_snapshot_groups_str VARCHAR2(500);
2485 lv_snapshot_name varchar2(50);
2486 lv_existance_check varchar2(200);
2487 drop_count number :=0;
2488
2489 TYPE CurTyp is ref cursor;
2490 c_snap CurTyp;
2491 lv_cusror_str varchar2(500);
2492
2493 BEGIN
2494
2495 v_cp_enabled := SYS_YES;
2496
2497 lv_snapshot_groups_str := CREATE_SNAPSHOT_STRING(p_snapshot_str);
2498
2499 IF lv_snapshot_groups_str = '' or lv_snapshot_groups_str is NULL THEN
2500 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'No Snapshots selected');
2501 ELSE
2502
2503 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_snapshot_groups_str);
2504
2505 lv_cusror_str := ' select mview_name, existance_check
2506 from msc_coll_snapshots_v
2507 where mview_name in ' || lv_snapshot_groups_str || '
2508 or snapshot_group_string in ' || lv_snapshot_groups_str ;
2509
2510 OPEN c_snap for lv_cusror_str;
2511
2512 LOOP
2513 fetch c_snap into lv_snapshot_name,lv_existance_check;
2514 exit when c_snap%notfound;
2515 if eval(lv_existance_check) then
2516
2517 lv_sql_stmt := 'DROP SNAPSHOT '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name;
2518
2519 EXECUTE IMMEDIATE lv_sql_stmt;
2520 --Droping the Synonym and Trigger on this snapshot
2521 MSC_UTIL.DROP_MVIEW_TRIGGERS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
2522 MSC_UTIL.DROP_MVIEW_SYNONYMS(MSC_UTIL.G_APPS_SCHEMA, lv_snapshot_name);
2523 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Successfully dropped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
2524 drop_count := drop_count + 1;
2525 else
2526 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' Skipped the Snapshot : '||MSC_UTIL.G_APPS_SCHEMA||'.'||lv_snapshot_name);
2527 end if;
2528 END LOOP;
2529
2530 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Number of snapshots dropped :' || to_char(drop_count));
2531 END IF;
2532 if drop_count > 0 then
2533 begin
2534 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Updating Profile Option MSC_SOURCE_SETUP to Yes ');
2535
2536 UPDATE FND_PROFILE_OPTION_VALUES
2537 SET PROFILE_OPTION_VALUE = 'Y'
2538 WHERE PROFILE_OPTION_ID = (SELECT PROFILE_OPTION_ID
2539 FROM FND_PROFILE_OPTIONS
2540 WHERE PROFILE_OPTION_NAME = 'MSC_SOURCE_SETUP');
2541 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Profile Option MSC_SOURCE_SETUP has been updated Yes ');
2542 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'The Value Yes indicates that the Collection Setup Objects need to be recreated');
2543 COMMIT;
2544
2545 EXCEPTION
2546
2547 WHEN OTHERS THEN
2548 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error Updating Profile MSC_SOURCE_SETUP: '||SQLERRM);
2549 end;
2550 end if;
2551 RETCODE:= G_SUCCESS;
2552
2553 EXCEPTION
2554 WHEN OTHERS THEN
2555 ROLLBACK;
2556 RETCODE:= G_ERROR;
2557 ERRBUF:= SQLERRM;
2558 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
2559 END DROP_SNAPSHOT;
2560
2561
2562 PROCEDURE check_MV_cont_ref_type(p_MV_name in varchar2,
2563 p_entity_lrn in number,
2564 entity_flag OUT NOCOPY number,
2565 p_ad_table_name in varchar2,
2566 p_org_str in varchar2,
2567 p_coll_thresh in number,
2568 p_last_tgt_cont_coll_time in date,
2569 p_ret_code OUT NOCOPY number,
2570 p_err_buf OUT NOCOPY varchar2)
2571 IS
2572 lv_MV_complete_refresh number :=0 ;
2573
2574 lv_Num_del number :=0 ;
2575 lv_Num_new number :=0 ;
2576 lv_Num_snap number :=0 ;
2577
2578 v_sql_stmt Varchar2(2000);
2579
2580 BEGIN
2581
2582 IF p_last_tgt_cont_coll_time is NOT NULL THEN
2583 BEGIN
2584 select 1
2585 into lv_MV_complete_refresh
2586 from msc_coll_snapshots_v
2587 where mview_name = p_MV_name
2588 and existance_check = MSC_UTIL.SYS_YES
2589 and nvl(complete_refresh_timestamp,p_last_tgt_cont_coll_time) > p_last_tgt_cont_coll_time;
2590 EXCEPTION
2591 WHEN NO_DATA_FOUND THEN
2592 lv_MV_complete_refresh := 0;
2593 END;
2594 IF lv_MV_complete_refresh = 1 THEN
2595 entity_flag := MSC_UTIL.SYS_TGT;
2596 RETURN;
2597 END IF;
2598 END IF;
2599
2600 IF p_ad_table_name is not null THEN
2601 v_sql_stmt := 'select count(*)
2602 from ' || p_ad_table_name || '
2603 where organization_id ' || p_org_str ;
2604 EXECUTE IMMEDIATE v_sql_stmt INTO lv_Num_del;
2605 ELSE
2606 lv_Num_del := 0;
2607 END IF;
2608
2609 v_sql_stmt := 'select count(*)
2610 from ' || p_MV_name || '
2611 where rn > ' || p_entity_lrn || '
2612 and organization_id ' || p_org_str ;
2613 EXECUTE IMMEDIATE v_sql_stmt INTO lv_Num_new;
2614
2615 v_sql_stmt := 'select count(*)
2616 from ' || p_MV_name || '
2617 where rn <= ' || p_entity_lrn || '
2618 and organization_id ' || p_org_str || '
2619 and rownum < :num_thr' ;
2620 EXECUTE IMMEDIATE v_sql_stmt INTO lv_Num_snap USING 1+((lv_Num_del + lv_Num_new)/(p_coll_thresh/100));
2621
2622 IF lv_Num_new = 0 and lv_Num_del = 0 THEN
2623 entity_flag := MSC_UTIL.SYS_NO;
2624 ELSIF lv_Num_snap =0 THEN
2625 entity_flag := MSC_UTIL.SYS_TGT;
2626 ELSIF (lv_Num_del + lv_Num_new) >= (p_coll_thresh * lv_Num_snap)/100 THEN
2627 entity_flag := MSC_UTIL.SYS_TGT;
2628 ELSE
2629 entity_flag := MSC_UTIL.SYS_INCR;
2630 END IF;
2631
2632
2633
2634 EXCEPTION WHEN OTHERS THEN
2635 entity_flag := MSC_UTIL.SYS_INCR;
2636 p_ret_code := MSC_UTIL.G_ERROR;
2637 p_err_buf := p_err_buf || ' ' ||p_MV_name ;
2638 END check_MV_cont_ref_type;
2639
2640
2641 PROCEDURE check_entity_cont_ref_type(p_entity_name in varchar2,
2642 p_entity_lrn in number,
2643 entity_flag OUT NOCOPY number,
2644 p_org_str in varchar2,
2645 p_coll_thresh in number,
2646 p_last_tgt_cont_coll_time in date,
2647 p_ret_code OUT NOCOPY number,
2648 p_err_buf OUT NOCOPY varchar2)
2649 IS
2650 lv_snapshot_name varchar2(50);
2651 lv_existance_check varchar2(200);
2652 lv_ad_table_name varchar2(50);
2653
2654 i NUMBER := 1;
2655
2656 TYPE CurTyp is ref cursor;
2657
2658 c_snap CurTyp;
2659
2660 lv_cusros_str varchar2(500);
2661
2662 lv_MV_complete_refresh number :=0 ;
2663 lv_entity_decided number := msc_util.sys_no;
2664 lv_entity_incr number := msc_util.sys_no;
2665 BEGIN
2666
2667 /*Check if any MV for this entity has undergone a complete refresh after last coll*/
2668 IF p_last_tgt_cont_coll_time is NOT NULL THEN
2669 BEGIN
2670 Execute immediate '
2671 select 1
2672 from msc_coll_snapshots_v
2673 where snapshot_group_string = ''' || p_entity_name || '''
2674 and existance_check = ' || MSC_UTIL.SYS_YES || '
2675 and check_for_cont_refresh = ' || MSC_UTIL.SYS_YES || '
2676 and nvl(complete_refresh_timestamp,:vDate) > :vDate
2677 and rownum < 2'
2678 into lv_MV_complete_refresh
2679 using p_last_tgt_cont_coll_time, p_last_tgt_cont_coll_time; -- tobe enhanced
2680 EXCEPTION
2681 WHEN NO_DATA_FOUND THEN
2682 lv_MV_complete_refresh := 0;
2683 END;
2684 IF lv_MV_complete_refresh = 1 THEN
2685 entity_flag := MSC_UTIL.SYS_TGT;
2686 RETURN;
2687 END IF;
2688 END IF;
2689
2690 lv_cusros_str := ' select mview_name, ad_table_name, existance_check
2691 from msc_coll_snapshots_v
2692 where check_for_cont_refresh = ' || MSC_UTIL.SYS_YES || '
2693 and snapshot_group_string = ''' || p_entity_name || '''' ;
2694
2695 OPEN c_snap for lv_cusros_str;
2696
2697
2698 LOOP
2699 fetch c_snap into lv_snapshot_name, lv_ad_table_name, lv_existance_check;
2700 exit when c_snap%notfound;
2701 if eval(lv_existance_check) then
2702 check_MV_cont_ref_type(lv_snapshot_name,
2703 p_entity_lrn,
2704 entity_flag,
2705 lv_ad_table_name,
2706 p_org_str,
2707 p_coll_thresh,
2708 p_last_tgt_cont_coll_time,
2709 p_ret_code,
2710 p_err_buf);
2711
2712 IF entity_flag = MSC_UTIL.SYS_TGT THEN
2713 lv_entity_decided := msc_util.sys_yes;
2714 ELSIF entity_flag = MSC_UTIL.SYS_INCR THEN
2715 lv_entity_incr := msc_util.sys_yes;
2716 END IF;
2717
2718 /* if the last MV suggested no collection,
2719 this need to be updated with previous result. */
2720 IF entity_flag = MSC_UTIL.SYS_NO AND lv_entity_incr = msc_util.sys_yes THEN
2721 entity_flag := MSC_UTIL.SYS_INCR;
2722 END IF;
2723
2724 EXIT WHEN lv_entity_decided = msc_util.sys_yes;
2725 end if; --eval (lv_existance_check)
2726 END LOOP;
2727
2728 EXCEPTION
2729 WHEN OTHERS THEN
2730 p_ret_code := MSC_UTIL.G_ERROR;
2731 p_err_buf := SQLERRM;
2732 END check_entity_cont_ref_type;
2733
2734
2735
2736 PROCEDURE CREATE_SOURCE_VIEWS(
2737 ERRBUF OUT NOCOPY VARCHAR2,
2738 RETCODE OUT NOCOPY NUMBER)
2739 IS
2740 lv_request_id NUMBER;
2741 type request_id_list_type is table of NUMBER index by pls_integer;
2742 lv_request_id_views request_id_list_type;
2743 lv_out number;
2744 indx number := 0;
2745 BEGIN
2746 -- setup
2747 indx := indx + 1;
2748 lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
2749 'MSC',
2750 'MSCVWSTP',
2751 NULL,
2752 NULL,
2753 FALSE);
2754 commit;
2755 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates Setup Views used by Collections Process');
2756 --
2757 -- Item
2758 indx := indx + 1;
2759 lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
2760 'MSC',
2761 'MSCVWITM',
2762 NULL,
2763 NULL,
2764 FALSE);
2765 commit;
2766 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates Item Views used by Collections Process');
2767 --
2768 -- BOM
2769 indx := indx + 1;
2770 lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
2771 'MSC',
2772 'MSCVWBOM',
2773 NULL,
2774 NULL,
2775 FALSE);
2776 commit;
2777 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates BOM Views used by Collections Process');
2778 --
2779 -- Routing
2780 indx := indx + 1;
2781 lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
2782 'MSC',
2783 'MSCVWRTG',
2784 NULL,
2785 NULL,
2786 FALSE);
2787 commit;
2788 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates Routing Views used by Collections Process');
2789 --
2790 -- WIP
2791 indx := indx + 1;
2792 lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
2793 'MSC',
2794 'MSCVWWIP',
2795 NULL,
2796 NULL,
2797 FALSE);
2798 commit;
2799 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates WIP Views used by Collections Process');
2800 --
2801 -- Demand
2802 indx := indx + 1;
2803 lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
2804 'MSC',
2805 'MSCVWDEM',
2806 NULL,
2807 NULL,
2808 FALSE);
2809 commit;
2810 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates Demand Views used by Collections Process');
2811 --
2812
2813 -- Supply
2814 indx := indx + 1;
2815 lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
2816 'MSC',
2817 'MSCVWSUP',
2818 NULL,
2819 NULL,
2820 FALSE);
2821 commit;
2822 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates Supply Views used by Collections Process');
2823 --
2824 -- Other
2825 indx := indx + 1;
2826 lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
2827 'MSC',
2828 'MSCVWOTH',
2829 NULL,
2830 NULL,
2831 FALSE);
2832 commit;
2833 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates Other Views used by Collections Process');
2834 --
2835 -- Repair Order
2836 indx := indx + 1;
2837 lv_request_id_views(indx) := FND_REQUEST.SUBMIT_REQUEST(
2838 'MSC',
2839 'MSCVWRPO',
2840 NULL,
2841 NULL,
2842 FALSE);
2843 commit;
2844 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_views(indx)||' :Creates Repair Order Views used by Collections Process');
2845 --
2846 for i in 1 .. lv_request_id_views.last
2847 loop
2848 wait_for_request(lv_request_id_views(i), 10, lv_out);
2849 if lv_out = 2 THEN
2850 ERRBUF := 'Error in creating Source Views';
2851 RETCODE := MSC_UTIL.G_ERROR;
2852 EXIT;
2853 end if;
2854 -- MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,'id-'||lv_request_id_views(i));
2855 end loop;
2856
2857 EXCEPTION
2858 WHEN OTHERS THEN
2859 RETCODE := MSC_UTIL.G_ERROR;
2860 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
2861 RAISE;
2862
2863 END CREATE_SOURCE_VIEWS;
2864
2865 PROCEDURE CREATE_SOURCE_TRIGGERS(
2866 ERRBUF OUT NOCOPY VARCHAR2,
2867 RETCODE OUT NOCOPY NUMBER)
2868 IS
2869 lv_request_id NUMBER;
2870 type request_id_list_type is table of NUMBER index by pls_integer;
2871 lv_request_id_trigs request_id_list_type;
2872 lv_out number;
2873 indx number := 0;
2874 BEGIN
2875 -- Item
2876 indx := indx + 1;
2877 lv_request_id_trigs(indx) := FND_REQUEST.SUBMIT_REQUEST(
2878 'MSC',
2879 'MSCTRITM',
2880 NULL,
2881 NULL,
2882 FALSE);
2883 commit;
2884 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates Item Triggers used by Collections Process');
2885 --
2886 -- BOM
2887 indx := indx + 1;
2888 lv_request_id_trigs(indx) := FND_REQUEST.SUBMIT_REQUEST(
2889 'MSC',
2890 'MSCTRBOM',
2891 NULL,
2892 NULL,
2893 FALSE);
2894 commit;
2895 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates BOM Triggers used by Collections Process');
2896 --
2897 -- Routing
2898 indx := indx + 1;
2899 lv_request_id_trigs(indx) := FND_REQUEST.SUBMIT_REQUEST(
2900 'MSC',
2901 'MSCTRRTG',
2902 NULL,
2903 NULL,
2904 FALSE);
2905 commit;
2906 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates Routing Triggers used by Collections Process');
2907 --
2908 -- WIP
2909 indx := indx + 1;
2910 lv_request_id_trigs(indx) := FND_REQUEST.SUBMIT_REQUEST(
2911 'MSC',
2912 'MSCTRWIP',
2913 NULL,
2914 NULL,
2915 FALSE);
2916 commit;
2917 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates WIP Triggers used by Collections Process');
2918 --
2919 -- Demand
2920 indx := indx + 1;
2921 lv_request_id_trigs(indx) := FND_REQUEST.SUBMIT_REQUEST(
2922 'MSC',
2923 'MSCTRDEM',
2924 NULL,
2925 NULL,
2926 FALSE);
2927 commit;
2928 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates Demand Triggers used by Collections Process');
2929 --
2930
2931 -- Supply
2932 indx := indx + 1;
2933 lv_request_id_trigs(indx) := FND_REQUEST.SUBMIT_REQUEST(
2934 'MSC',
2935 'MSCTRSUP',
2936 NULL,
2937 NULL,
2938 FALSE);
2939 commit;
2940 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates Supply Triggers used by Collections Process');
2941 --
2942 -- Other
2943 indx := indx + 1;
2944 lv_request_id_trigs(indx) := FND_REQUEST.SUBMIT_REQUEST(
2945 'MSC',
2946 'MSCTROTH',
2947 NULL,
2948 NULL,
2949 FALSE);
2950 commit;
2951 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates Other Triggers used by Collections Process');
2952 --
2953 -- Repair Order
2954 indx := indx + 1;
2955 lv_request_id_trigs(indx) := FND_REQUEST.SUBMIT_REQUEST(
2956 'MSC',
2957 'MSCTRRPO',
2958 NULL,
2959 NULL,
2960 FALSE);
2961 commit;
2962 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Request : '||lv_request_id_trigs(indx)||' :Creates Repair Order Triggers used by Collections Process');
2963 --
2964 for i in 1 .. lv_request_id_trigs.last
2965 loop
2966 wait_for_request(lv_request_id_trigs(i), 10, lv_out);
2967 if lv_out = 2 THEN
2968 ERRBUF := 'Error in creating Source Triggers';
2969 RETCODE := MSC_UTIL.G_ERROR;
2970 EXIT;
2971 end if;
2972 end loop;
2973
2974 EXCEPTION
2975 WHEN OTHERS THEN
2976 RETCODE := MSC_UTIL.G_ERROR;
2977 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS,SQLERRM);
2978 RAISE;
2979
2980 END CREATE_SOURCE_TRIGGERS;
2981
2982 END MRP_CL_REFRESH_SNAPSHOT;