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