[Home] [Help]
PACKAGE BODY: APPS.MSC_E1_APS_PDP
Source
1 PACKAGE BODY MSC_E1_APS_PDP AS
2 --# $Header: MSCE1PDB.pls 120.8 2011/09/09 07:26:00 sravinoo noship $
3 /* Array declaration */
4
5 TYPE scenarios_array IS VARRAY(100) OF VARCHAR2(200); -- declaring array for holding scenarios
6 arrScr scenarios_array := scenarios_array();
7
8 /* Global variables */
9
10 ReturnStr varchar2(2000):=NULL;
11 SessionNum varchar2(10) :=NULL;
12 ErrMessage varchar2(1900);
13 ErrLength integer;
14 StartIndex integer;
15 EndIndex integer;
16 WSURL varchar2(1000);
17
18 /* Local Procedure */
19
20 /* Function to Launch Mail after scnerios are done */
21 FUNCTION MSC_E1_Mail_ODIExecute(scrName IN VARCHAR2, WSURL IN VARCHAR2)
22 RETURN BOOLEAN
23 AS
24 -- NO local variables
25 BEGIN
26 IF WSURL IS NOT NULL THEN
27 begin
28 select MSC_E1APS_UTIL.MSC_E1APS_ODIScenarioExecute(scrName,'001','',WSURL) into ReturnStr from dual;
29
30
31 EXCEPTION
32 WHEN OTHERS THEN
33 select instr(ReturnStr,'#') into StartIndex from dual;
34 select substr(ReturnStr,StartIndex+1,1800) into ErrMessage from dual;
35 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Scenario' || scrName|| ' execution failed.' || ErrMessage);
36 return FALSE;
37 end;
38
39 select instr(ReturnStr,'#') into StartIndex from dual;
40 select substr(ReturnStr,0,StartIndex-1) into SessionNum from dual;
41 select substr(ReturnStr,StartIndex+1,1800) into ErrMessage from dual;
42
43 if (SessionNum = '-1') then
44 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Scenario ' || scrName|| ' executed with errors. Session #: ' || SessionNum || ' , Error Message: ' || ErrMessage);
45 RETURN FALSE;
46 end if;
47
48 if (SessionNum <> '-1' and length(ErrMessage) > 0) then
49 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Scenario ' || scrName|| ' executed with errors. Session #: ' || SessionNum || ' , Error Message: ' || ErrMessage);
50 RETURN FALSE;
51 end if;
52 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,' Mail Execution SUCCESS');
53 RETURN TRUE;
54 ELSE
55 RETURN TRUE;
56 END IF;
57 END; --MSC_E1_Mail_ODIExecute
58
59 /*execute ODI scenario*/
60 FUNCTION MSC_E1_APS_ODIEXECUTE(arrScr IN scenarios_array, WSURL IN VARCHAR2)
61 RETURN BOOLEAN
62 AS
63 scrStatus varchar2(10);
64 OdiStatus Boolean:= False;
65 err_msg1 varchar2(4000);
66
67 -- for bug # 12897298
68 ws_prf VARCHAR2(1000);
69 sql_cnd VARCHAR2(2000);
70 srch_str VARCHAR2(20) := 'MSC_E1APS_ODIPort';
71 chk_cnd INTEGER;
72
73 begin
74 FOR i IN arrScr.FIRST .. arrScr.LAST LOOP
75 IF WSURL IS NOT NULL THEN
76 begin
77 select MSC_E1APS_UTIL.MSC_E1APS_ODIScenarioExecute(arrScr(i),'001','',WSURL) into ReturnStr from dual;
78
79 EXCEPTION
80 WHEN OTHERS THEN
81 select instr(ReturnStr,'#') into StartIndex from dual;
82 select substr(ReturnStr,StartIndex+1,1800) into ErrMessage from dual;
83 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Scenario' || arrScr(i)|| ' execution failed.' || ErrMessage);
84 return FALSE;
85 end;
86
87 select instr(ReturnStr,'#') into StartIndex from dual;
88 select substr(ReturnStr,0,StartIndex-1) into SessionNum from dual;
89 select substr(ReturnStr,StartIndex+1,1800) into ErrMessage from dual;
90 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, ' ODI Scenario '||' RESULT');
91
92
93 if (SessionNum = '-1') then
94 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Scenario ' || arrScr(i)|| ' executed with errors. Session #: ' || SessionNum || ' , Error Message: ' || ErrMessage);
95 RETURN FALSE;
96 end if;
97
98 if (SessionNum <> '-1' and length(ErrMessage) > 0) then
99 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Scenario ' || arrScr(i)|| ' executed with errors. Session #: ' || SessionNum || ' , Error Message: ' || ErrMessage);
100 RETURN FALSE;
101 end if;
102
103 -- Changes for bug # 12897298
104 ws_prf := fnd_profile.value('MSC_E1APS_ODIURL');
105 sql_cnd := 'select INSTR('''||ws_prf||''','''||srch_str||''') from dual';
106 EXECUTE immediate sql_cnd into chk_cnd;
107 IF (chk_cnd > 0) THEN -- Execute only if its 11G
108 if (SessionNum <> '-1' and length(ErrMessage) IS NULL) then
109 err_msg1 := MSC_E1APS_UTIL.GetODIErrorMessage(SessionNum);
110 if (length(err_msg1) IS NOT NULL) then
111 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Scenario : ' || arrScr(i)|| ' FAILED. Session #: ' || SessionNum);
112 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error Message: ' || err_msg1);
113 RETURN FALSE;
114 end if;
115 end if;
116 END IF; -- for Bug # 12897298
117
118 if (SessionNum is NULL ) then
119 OdiStatus:= MSC_E1APS_UTIL.OdiSessionStatus(arrScr(i));
120 IF OdiStatus = FALSE THEN
121 RETURN OdiStatus;
122 END IF;
123
124 end if;
125 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,' ' || arrScr(i)||' SUCCESS');
126 ELSE
127 RETURN TRUE;
128 END IF;
129 END LOOP;
130
131 RETURN TRUE;
132
133 END ; -- MSC_E1_APS_ODIEXECUTE local procedure
134
135 /* Main procedure begins here */
136
137 /* Check parameters and add scenarios to above defined array */
138
139 PROCEDURE MSC_E1APS_SCR_LIST(ERRBUF OUT NOCOPY VARCHAR2,
140 RETCODE OUT NOCOPY VARCHAR2,
141 parInstanceID IN VARCHAR2,
142 parBaseDate IN INTEGER,
143 parCalendars IN NUMBER ,
144 parTradingPtnrs IN NUMBER ,
145 parPlanners IN NUMBER ,
146 parUOMs IN NUMBER ,
147 parItems IN NUMBER ,
148 parResrcs IN NUMBER ,
149 parRtng IN NUMBER ,
150 parOprns IN NUMBER ,
151 parBOMs IN NUMBER ,
152 parDmdClasses IN NUMBER ,
153 parSalesChannels IN NUMBER ,
154 parPriceLists IN NUMBER ,
155 parShippingMethods IN NUMBER ,
156 parItemSupp IN NUMBER ,
157 parItemSrcing IN NUMBER ,
158 parOnhandSupp IN NUMBER ,
159 parSS IN NUMBER ,
160 parPOSupp IN NUMBER ,
161 parReqSupp IN NUMBER,
162 parInstrSupp IN NUMBER ,
163 parExtFcst IN NUMBER ,
164 parSO IN NUMBER,
165 parWO IN NUMBER,
166 parATPRules IN NUMBER) -- GOP Integration bug # 10157479
167 is
168 scrCnt number(2) := 0;
169 setreq_success BOOLEAN;
170 req_submit_failed exception;
171 FlagODILaunch boolean;
172 set_req_id NUMBER;
173 disp_val varchar2(10);
174 fc_url varchar2(1000);
175 fc_ret_value BOOLEAN;
176 source_file varchar2(200);
177 destination_file varchar2(200);
178 mailstat BOOLEAN;
179 l_instance_code VARCHAR2(3);
180 scenario_name VARCHAR2(200);
181 scenario_version VARCHAR2(100);
182 scenario_param VARCHAR2(200);
183 pre_process_odi BOOLEAN;
184 post_process_odi BOOLEAN;
185 updateFlag NUMBER(2) ; /* Bug#9757888 To Update TP_SITE_CODE */
186
187
188 /* Array definition for file copy list */
189 TYPE FileList IS TABLE OF VARCHAR2(100);
190 arrFiles FileList;
191
192 /* Local Variables for pre-processor submit request */
193
194 varCtg NUMBER DEFAULT MSC_UTIL.SYS_NO;
195 varItemCtg NUMBER DEFAULT MSC_UTIL.SYS_NO;
196 varUomClass NUMBER DEFAULT MSC_UTIL.SYS_NO;
197 varDesig NUMBER DEFAULT MSC_UTIL.SYS_NO;
198 varProj NUMBER DEFAULT MSC_UTIL.SYS_NO;
199 varSuppCap NUMBER DEFAULT MSC_UTIL.SYS_NO;
200 varMatSup NUMBER DEFAULT MSC_UTIL.SYS_NO;
201 varMatDmd NUMBER DEFAULT MSC_UTIL.SYS_NO;
202 varResrv NUMBER DEFAULT MSC_UTIL.SYS_NO;
203 varResDmd NUMBER DEFAULT MSC_UTIL.SYS_NO;
204 varItemCst NUMBER DEFAULT MSC_UTIL.SYS_NO;
205 varParentReqId NUMBER DEFAULT -1;
206 varFisCal NUMBER DEFAULT MSC_UTIL.SYS_NO;
207 varSetup NUMBER DEFAULT MSC_UTIL.SYS_NO;
208 varLinkDummy VARCHAR2(1000) DEFAULT NULL;
209 varItemRollup NUMBER DEFAULT MSC_UTIL.SYS_NO;
210 varLvlvalue NUMBER DEFAULT MSC_UTIL.SYS_NO;
211 varLvlAssoc NUMBER DEFAULT MSC_UTIL.SYS_NO;
212 varBooking NUMBER DEFAULT MSC_UTIL.SYS_NO;
213 varShipment NUMBER DEFAULT MSC_UTIL.SYS_NO;
214 varMfgFct NUMBER DEFAULT MSC_UTIL.SYS_NO;
215 varCSData NUMBER DEFAULT MSC_UTIL.SYS_NO;
216 varCSDummy VARCHAR2(1000) DEFAULT NULL;
217 varCSRefresh NUMBER DEFAULT MSC_UTIL.SYS_NO;
218 varCurrConv NUMBER DEFAULT MSC_UTIL.SYS_NO;
219 varUomConv NUMBER DEFAULT MSC_UTIL.SYS_NO;
220 varCallingModule CONSTANT NUMBER := 1;
221 varCompUsers NUMBER DEFAULT MSC_UTIL.SYS_NO;
222 varItemSubs NUMBER DEFAULT MSC_UTIL.SYS_NO;
223 varCompCal NUMBER DEFAULT MSC_UTIL.SYS_NO;
224 varProfile NUMBER DEFAULT MSC_UTIL.SYS_NO;
225 varCalAssign NUMBER DEFAULT MSC_UTIL.SYS_NO;
226 varIRO NUMBER DEFAULT MSC_UTIL.SYS_NO;
227 varERO NUMBER DEFAULT MSC_UTIL.SYS_NO;
228 varSalesChannel NUMBER DEFAULT MSC_UTIL.SYS_NO;
229 varFiscalCalendar NUMBER DEFAULT MSC_UTIL.SYS_NO;
230
231 /* Local variables for ODS LOAD */
232 varRecalcResAvailability NUMBER DEFAULT MSC_UTIL.SYS_NO;
233 varSourcingHistoryEnabled NUMBER DEFAULT MSC_UTIL.SYS_YES;
234 varPurgeSourcingHistory NUMBER DEFAULT MSC_UTIL.SYS_NO;
235 varAPCCRefresh NUMBER DEFAULT MSC_UTIL.SYS_NO;
236
237 /*Additional Variables to Purge ODS Data */
238
239 varATPRules NUMBER DEFAULT MSC_UTIL.SYS_NO;
240 varBOR NUMBER DEFAULT MSC_UTIL.SYS_NO;
241 varKPIBIS NUMBER DEFAULT MSC_UTIL.SYS_NO;
242 varMDS NUMBER DEFAULT MSC_UTIL.SYS_NO;
243 varMPS NUMBER DEFAULT MSC_UTIL.SYS_NO;
244 varParameter NUMBER DEFAULT MSC_UTIL.SYS_NO;
245 varPOReceipts NUMBER DEFAULT MSC_UTIL.SYS_NO;
246 varProject NUMBER DEFAULT MSC_UTIL.SYS_NO;
247 varPURREQPO NUMBER DEFAULT MSC_UTIL.SYS_NO;
248 varReservesHard NUMBER DEFAULT MSC_UTIL.SYS_NO;
249 varResourceNRA NUMBER DEFAULT MSC_UTIL.SYS_NO;
250 varSH NUMBER DEFAULT MSC_UTIL.SYS_NO;
251 varSUBINV NUMBER DEFAULT MSC_UTIL.SYS_NO;
252 varSupplierResponse NUMBER DEFAULT MSC_UTIL.SYS_NO;
253 varTrip NUMBER DEFAULT MSC_UTIL.SYS_NO;
254 varUnitNO NUMBER DEFAULT MSC_UTIL.SYS_NO;
255 varUserCompany NUMBER DEFAULT MSC_UTIL.SYS_NO;
256 varUserSupplyDemand NUMBER DEFAULT MSC_UTIL.SYS_NO;
257 varPaybackDemandSupply NUMBER DEFAULT MSC_UTIL.SYS_NO;
258 varCurrencyConversion NUMBER DEFAULT MSC_UTIL.SYS_NO;
259 varDeliveryDetails NUMBER DEFAULT MSC_UTIL.SYS_NO;
260
261 /* Purge ODS Global Entities and Lid */
262 varPurgeGlobalODS NUMBER DEFAULT MSC_UTIL.SYS_NO;
263 varPurgeGlobalFlag NUMBER DEFAULT MSC_UTIL.SYS_NO;
264 varPurgeLocalId NUMBER DEFAULT MSC_UTIL.SYS_NO;
265
266 /*Loacal Parametes for Dependency Entites*/
267 locParCalendars NUMBER DEFAULT MSC_UTIL.SYS_NO;
268 locParResrcs NUMBER DEFAULT MSC_UTIL.SYS_NO;
269 locParOprns NUMBER DEFAULT MSC_UTIL.SYS_NO;
270 locParRtng NUMBER DEFAULT MSC_UTIL.SYS_NO;
271 locAtpRule NUMBER DEFAULT MSC_UTIL.SYS_NO; -- GOP Integration bug # 10157479
272
273 parInstanceCode VARCHAR2(3);
274
275 -- Local variables for profile values for bug # 11769639
276 varBatchSize NUMBER(5);
277 varNumWorkers NUMBER(5);
278 varTimeOut NUMBER(5);
279
280
281 BEGIN
282
283 locParCalendars := parCalendars;
284 locParResrcs := parResrcs;
285 locParOprns := parOprns ;
286 locParRtng := parRtng;
287 locAtpRule := parATPRules; -- GOP Integration bug # 10157479
288
289
290 /* Purge ODS Dependancy for parPOSupp and parReqSupp*/
291 IF parPOSupp = MSC_UTIL.SYS_YES AND parReqSupp = MSC_UTIL.SYS_YES AND parInstrSupp =MSC_UTIL.SYS_YES THEN
292 varPURREQPO := MSC_UTIL.SYS_YES;
293 END IF;
294
295 /* Launching Collections Pre-Process Custom Hook*/
296 MSC_E1APS_HOOK.COL_PLAN_DATA_PRE_PROCESS(ERRBUF,RETCODE);
297
298 IF RETCODE = MSC_UTIL.G_ERROR THEN
299 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error Message:' || ERRBUF);
300 RETCODE := MSC_UTIL.G_ERROR;
301 RETURN;
302 END IF;
303
304 WSURL := fnd_profile.value('MSC_E1APS_ODIURL');
305
306 IF WSURL IS NOT NULL THEN
307 /* Launching Pre-Process Custom Hook ODI Scenario */
308 select instance_code into l_instance_code from msc_apps_instances
309 where instance_id = parInstanceID;
310
311 scenario_name := 'PREPROCESSHOOKPKG';
312 scenario_version := '001';
313 scenario_param := 'E1TOAPSPROJECT.PVV_PRE_PROCESS_VAR=';
314 scenario_param := scenario_param ||l_instance_code || ':'
315 || MSC_E1APS_UTIL.COL_PLAN_DATA;
316 pre_process_odi :=MSC_E1APS_DEMCL.CALL_ODIEXE(scenario_name, scenario_version, scenario_param, WSURL);
317
318 IF pre_process_odi = FALSE THEN
319 mailstat := MSC_E1_Mail_ODIExecute('MAIL',WSURL);
320 RETCODE := MSC_UTIL.G_ERROR;
321 RETURN;
322 END IF;
323 END IF;
324
325 MSC_UTIL.MSC_DEBUG('STARTING E1 APS Planning Data Pull');
326
327 /*Dependency Check for Trading Partner and Calendars*/
328 IF parTradingPtnrs = MSC_UTIL.SYS_YES AND locParCalendars = MSC_UTIL.SYS_NO THEN
329 scrCnt := scrCnt + 1;
330 arrScr.EXTEND;
331 arrScr(scrCnt) := 'LOADE1CALENDARDATATOAPSPKG';
332 END IF;
333
334 /*Dependency Check for BOM and Resource*/
335 IF parBOMs = MSC_UTIL.SYS_YES THEN
336 locParOprns := MSC_UTIL.SYS_YES;
337 locParRtng := MSC_UTIL.SYS_YES;
338 locParResrcs := MSC_UTIL.SYS_YES;
339 END IF;
340
341 /*Dependency Check for Resource Requirments and WO */
342 IF parWO = MSC_UTIL.SYS_YES THEN
343 locParResrcs := MSC_UTIL.SYS_YES;
344 END IF;
345
346 /*Dependency Check for Routing Operations and WO */
347 IF parWO = MSC_UTIL.SYS_YES THEN
348 locParOprns := MSC_UTIL.SYS_YES;
349 END IF;
350
351 /*Dependency Check for Operations and Routing */
352 IF locParOprns = MSC_UTIL.SYS_YES THEN
353 locParRtng := MSC_UTIL.SYS_YES;
354 END IF;
355
356 -- Code changes for GOP Integration - bug # 10157479
357 -- Items will also be run after ATP Rules whenever ATP Rules is selected to update ATP Rule ID in system_items
358 -- set parameters for items to Yes
359
360 /*Dependency Check for ATP Rules */
361 IF parItems = MSC_UTIL.SYS_YES THEN
362 locAtpRule := MSC_UTIL.SYS_YES;
363 END IF;
364
365 /* Creating array to hold the scenarios to be launched for the input parameters */
366
367 MSC_UTIL.MSC_DEBUG('CHECKING INPUT PARAMETERS TO RUN SCENARIOS');
368
369 IF locParCalendars = MSC_UTIL.SYS_YES THEN
370 scrCnt := scrCnt + 1;
371 arrScr.EXTEND;
372 arrScr(scrCnt) := 'LOADE1CALENDARDATATOAPSPKG';
373 scrCnt := scrCnt + 1;
374 arrScr.EXTEND;
375 arrScr(scrCnt) := 'LOADE1WORKDAYPATTERNDATATOAPSPKG';
376 scrCnt := scrCnt + 1;
377 arrScr.EXTEND;
378 arrScr(scrCnt) := 'LOADE1SHIFTTIMEDATATOAPSPKG';
379 scrCnt := scrCnt + 1;
380 arrScr.EXTEND;
381 arrScr(scrCnt) := 'LOADE1CALENDAREXCEPTIONDATATOAPSPKG';
382 scrCnt := scrCnt + 1;
383 arrScr.EXTEND;
384 arrScr(scrCnt) := 'LOADE1SHIFTEXCEPTIONDATATOAPSPKG';
385 END IF;
386
387 IF parTradingPtnrs = MSC_UTIL.SYS_YES THEN
388 scrCnt := scrCnt + 1;
389 arrScr.EXTEND;
390 arrScr(scrCnt) := 'LOADE1TRADINGPARTNERSDATATOAPSPKG';
391 scrCnt := scrCnt + 1;
392 arrScr.EXTEND;
393 arrScr(scrCnt) := 'LOADE1TRADPARTNERSITEDATATOAPSPKG';
394 scrCnt := scrCnt + 1;
395 arrScr.EXTEND;
396 arrScr(scrCnt) := 'LOADE1LOCASSOCIATIONDATATOAPSPKG';
397 END IF;
398
399 IF parPlanners = MSC_UTIL.SYS_YES THEN
400 scrCnt := scrCnt + 1;
401 arrScr.EXTEND;
402 arrScr(scrCnt) := 'LOADE1PLANNERDATATOAPSPKG';
403 END IF;
404
405 IF parUOMs = MSC_UTIL.SYS_YES THEN
406 scrCnt := scrCnt + 1;
407 arrScr.EXTEND;
408 arrScr(scrCnt) := 'LOADE1UNITSOFMEASUREDATATOAPSPKG';
409 scrCnt := scrCnt + 1;
410 arrScr.EXTEND;
411 arrScr(scrCnt) := 'LOADE1UOMCONVERSIONDATATOAPSPKG';
412 scrCnt := scrCnt + 1;
413 arrScr.EXTEND;
414 arrScr(scrCnt) := 'LOADE1UOMCLASSCONVDATATOAPSPKG';
415 END IF;
416
417 -- Code changes for GOP Integration - bug # 10157479
418 IF locAtpRule= MSC_UTIL.SYS_YES THEN
419 scrCnt := scrCnt + 1;
420 arrScr.EXTEND;
421 arrScr(scrCnt) := 'LOADE1ATPRULESDATATOAPSPKG';
422 END IF;
423
424 IF parItems = MSC_UTIL.SYS_YES THEN
425 scrCnt := scrCnt + 1;
426 arrScr.EXTEND;
427 arrScr(scrCnt) := 'LOADE1ITEMDATATOAPSPKG';
428 scrCnt := scrCnt + 1;
429 arrScr.EXTEND;
430 arrScr(scrCnt) := 'LOADE1CATEGORYSETDATATOAPSPKG';
431 scrCnt := scrCnt + 1;
432 arrScr.EXTEND;
433 arrScr(scrCnt) := 'LOADE1ITEMCATEGORIESDATATOAPSPKG';
434 scrCnt := scrCnt + 1;
435 arrScr.EXTEND;
436 arrScr(scrCnt) := 'LOADE1ITEMSUBSTITUTEDATATOAPSPKG';
437 END IF;
438
439 IF locParResrcs = MSC_UTIL.SYS_YES THEN
440 scrCnt := scrCnt + 1;
441 arrScr.EXTEND;
442 arrScr(scrCnt) := 'LOADE1RESOURCEGROUPSDATATOAPSPKG';
443 scrCnt := scrCnt + 1;
444 arrScr.EXTEND;
445 arrScr(scrCnt) := 'LOADE1RESOURCESDATATOAPSPKG';
446 scrCnt := scrCnt + 1;
447 arrScr.EXTEND;
448 arrScr(scrCnt) := 'LOADE1RESOURCESHIFTDATATOAPSPKG';
449 scrCnt := scrCnt + 1;
450 arrScr.EXTEND;
451 arrScr(scrCnt) := 'LOADE1RESOURCESETUPSDATATOAPSPKG';
452 scrCnt := scrCnt + 1;
453 arrScr.EXTEND;
454 arrScr(scrCnt) := 'LOADE1STDOPERRESOURCESDATATOAPSPKG';
455 scrCnt := scrCnt + 1;
456 arrScr.EXTEND;
457 arrScr(scrCnt) := 'LOADE1SETUPTRANSITIONSDATATOAPSPKG';
458
459 END IF;
460
461 IF locParRtng = MSC_UTIL.SYS_YES THEN
462 scrCnt := scrCnt + 1;
463 arrScr.EXTEND;
464 arrScr(scrCnt) := 'LOADE1ROUTINGDATATOAPSPKG';
465 END IF;
466
467 IF locParOprns = MSC_UTIL.SYS_YES THEN
468 scrCnt := scrCnt + 1;
469 arrScr.EXTEND;
470 arrScr(scrCnt) := 'LOADE1ROUTINOPERATIONDATATOAPSPKG';
471 scrCnt := scrCnt + 1;
472 arrScr.EXTEND;
473 arrScr(scrCnt) := 'LOADE1ROUTINGOPRESOURCEDATATOAPSPKG';
474 END IF;
475
476 IF parBOMs = MSC_UTIL.SYS_YES THEN
477 scrCnt := scrCnt + 1;
478 arrScr.EXTEND;
479 arrScr(scrCnt) := 'LOADE1BOMHEADERDATATOAPSPKG';
480 scrCnt := scrCnt + 1;
481 arrScr.EXTEND;
482 arrScr(scrCnt) := 'LOADE1BOMCOMPONENTDATATOAPSPKG';
483 END IF;
484
485 IF parDmdClasses = MSC_UTIL.SYS_YES THEN
486 scrCnt := scrCnt + 1;
487 arrScr.EXTEND;
488 arrScr(scrCnt) := 'LOADE1DEMANDCLASSESDATATOAPSPKG';
489 END IF;
490
491 IF parSalesChannels = MSC_UTIL.SYS_YES THEN
492 scrCnt := scrCnt + 1;
493 arrScr.EXTEND;
494 arrScr(scrCnt) := 'LOADE1SALESCHANNELDATATOAPSPKG';
495 END IF;
496
497 IF parPriceLists = MSC_UTIL.SYS_YES THEN
498 scrCnt := scrCnt + 1;
499 arrScr.EXTEND;
500 arrScr(scrCnt) := 'LOADE1PRICELISTDATATOAPSPKG';
501 END IF;
502
503 IF parShippingMethods = MSC_UTIL.SYS_YES THEN
504 scrCnt := scrCnt + 1;
505 arrScr.EXTEND;
506 arrScr(scrCnt) := 'LOADE1SHIPPINGMETHODSDATATOAPSPKG';
507 END IF;
508
509 IF parItemSupp = MSC_UTIL.SYS_YES THEN
510 scrCnt := scrCnt + 1;
511 arrScr.EXTEND;
512 arrScr(scrCnt) := 'LOADE1ITEMSUPPLIERDATATOAPSPKG';
513 END IF;
514
515 IF parItemSrcing = MSC_UTIL.SYS_YES THEN
516 scrCnt := scrCnt + 1;
517 arrScr.EXTEND;
518 arrScr(scrCnt) := 'LOADE1ITEMSOURCINGDATATOAPSPKG';
519 END IF;
520
521 IF parOnhandSupp = MSC_UTIL.SYS_YES THEN
522 scrCnt := scrCnt + 1;
523 arrScr.EXTEND;
524 arrScr(scrCnt) := 'LOADE1ONHANDSUPPLIESDATATOAPSPKG';
525 END IF;
526
527 IF parSS = MSC_UTIL.SYS_YES THEN
528 scrCnt := scrCnt + 1;
529 arrScr.EXTEND;
530 arrScr(scrCnt) := 'LOADE1SAFETYSTOCKDATATOAPSPKG';
531 END IF;
532
533 IF parPOSupp = MSC_UTIL.SYS_YES THEN
534 scrCnt := scrCnt + 1;
535 arrScr.EXTEND;
536 arrScr(scrCnt) := 'LOADE1PURCHASEORDSUPPLYDATATOAPSPKG';
537 END IF;
538
539 IF parReqSupp = MSC_UTIL.SYS_YES THEN
540 scrCnt := scrCnt + 1;
541 arrScr.EXTEND;
542 arrScr(scrCnt) := 'LOADE1REQUISITIONSUPPLDATATOAPSPKG';
543 END IF;
544
545 IF parInstrSupp = MSC_UTIL.SYS_YES THEN
546 scrCnt := scrCnt + 1;
547 arrScr.EXTEND;
548 arrScr(scrCnt) := 'LOADE1INTRANSITSUPPLIESDATATOAPSPKG';
549 END IF;
550
551 IF parExtFcst = MSC_UTIL.SYS_YES THEN
552 scrCnt := scrCnt + 1;
553 arrScr.EXTEND;
554 arrScr(scrCnt) := 'LOADE1FORECASTDESDATATOAPSPKG';
555 scrCnt := scrCnt + 1;
556 arrScr.EXTEND;
557 arrScr(scrCnt) := 'LOADE1FORECASTDEMANDSDATATOAPSPKG';
558 END IF;
559
560 IF parSO = MSC_UTIL.SYS_YES THEN
561 scrCnt := scrCnt + 1;
562 arrScr.EXTEND;
563 arrScr(scrCnt) := 'LOADE1SALESORDERDATATOAPSPKG';
564 END IF;
565
566 IF parWO = MSC_UTIL.SYS_YES THEN
567 scrCnt := scrCnt + 1;
568 arrScr.EXTEND;
569 arrScr(scrCnt) := 'LOADE1WORKORDERSUPPLIESDATATOAPSPKG';
570 scrCnt := scrCnt + 1;
571 arrScr.EXTEND;
572 arrScr(scrCnt) := 'LOADE1WORKORDERCOMPDMNDDATATOAPSPKG';
573 scrCnt := scrCnt + 1;
574 arrScr.EXTEND;
575 arrScr(scrCnt) := 'LOADE1RESOURCEREQDATATOAPSPKG';
576 END IF;
577
578
579
580 /*Initialize ODI */
581
582 MSC_UTIL.MSC_DEBUG('INITIALIZING ODI ....');
583
584 WSURL := fnd_profile.value('MSC_E1APS_ODIURL');
585
586 IF WSURL IS NOT NULL THEN
587 BEGIN
588 select MSC_E1APS_UTIL.MSC_E1APS_ODIInitialize(WSURL,parBaseDate) into ReturnStr from dual;
589
590 EXCEPTION
591 WHEN OTHERS THEN
592 select instr(ReturnStr,'#') into StartIndex from dual;
593 select substr(ReturnStr,StartIndex+1,1800) into ErrMessage from dual;
594 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Intialization failed. Error Message' || ErrMessage );
595 RETCODE := MSC_UTIL.G_ERROR;
596 RETURN;
597 END;
598
599 select instr(ReturnStr,'#') into StartIndex from dual;
600 select substr(ReturnStr,StartIndex+1,1800) into ErrMessage from dual;
601
602 IF (length(ErrMessage) > 0) THEN
603 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Failed to Initialize. Error Message' || ErrMessage );
604 RETCODE := MSC_UTIL.G_ERROR;
605 return;
606 end if;
607
608 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Initializion is successful');
609
610 END IF;
611
612 /* Deleting all rows from MSC_ST_PRICE_LIST Before Loading the Data */
613 IF parPriceLists = MSC_UTIL.SYS_YES THEN
614 BEGIN
615 DELETE FROM MSC_ST_PRICE_LIST;
616 COMMIT;
617
618 EXCEPTION
619 WHEN OTHERS THEN
620 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,'No Rows Deleted from MSC_ST_PRICE_LIST');
621 RETCODE := MSC_UTIL.G_ERROR;
622 RETURN;
623 END;
624 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,'Rows Deleted from MSC_ST_PRICE_LIST');
625 END IF;
626
627 FlagODILaunch := MSC_E1_APS_ODIEXECUTE (arrScr, WSURL);
628
629 /* Launch Mail Scenario after all scenarions excution */
630 mailstat := MSC_E1_Mail_ODIExecute('MAIL',WSURL);
631
632 IF parTradingPtnrs = MSC_UTIL.SYS_YES AND locParCalendars = MSC_UTIL.SYS_NO THEN
633 BEGIN
634
635 SELECT INSTANCE_CODE into parInstanceCode FROM MSC_APPS_INSTANCES
636 WHERE INSTANCE_ID = parInstanceId;
637
638 EXCEPTION
639 WHEN OTHERS THEN
640 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,'No Rows fetched from MSC_APPS_INSTANCES');
641 RETCODE := MSC_UTIL.G_ERROR;
642 RETURN;
643 END;
644
645 BEGIN
646 DELETE FROM MSC_ST_CALENDARS
647 WHERE SR_INSTANCE_CODE like parInstanceCode AND PROCESS_FLAG =1;
648
649 EXCEPTION
650 WHEN OTHERS THEN
651 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,'No Rows deleted from MSC_ST_CALENDARS');
652 RETCODE := MSC_UTIL.G_ERROR;
653 RETURN;
654 END;
655 END IF;
656
657 IF FlagODILaunch = FALSE THEN
658 RETCODE := MSC_UTIL.G_ERROR;
659 RETURN;
660 END IF;
661
662
663 IF FlagODILaunch THEN
664
665 /* Variables initialization for Legacy Collections Pre-processor paramerters */
666
667 IF parItems = MSC_UTIL.SYS_YES THEN
668 varCtg := MSC_UTIL.SYS_YES;
669 varItemCtg := MSC_UTIL.SYS_YES;
670 varItemCst:= MSC_UTIL.SYS_YES;
671 varItemSubs:= MSC_UTIL.SYS_YES;
672 END IF;
673
674 IF parUOMs = MSC_UTIL.SYS_YES THEN
675 varUomClass := MSC_UTIL.SYS_YES;
676 varUomConv := MSC_UTIL.SYS_YES;
677 END IF;
678
679 IF parItemSupp = MSC_UTIL.SYS_YES THEN
680 varSuppCap :=MSC_UTIL.SYS_YES;
681 END IF;
682
683 If parExtFcst = MSC_UTIL.SYS_YES THEN
684 varDesig :=MSC_UTIL.SYS_YES;
685 varMatDmd :=MSC_UTIL.SYS_YES;
686 End IF;
687
688 IF parWO = MSC_UTIL.SYS_YES THEN
689 varMatSup :=MSC_UTIL.SYS_YES;
690 varMatDmd :=MSC_UTIL.SYS_YES;
691 varResDmd :=MSC_UTIL.SYS_YES;
692 END IF;
693
694 IF parOnhandSupp = MSC_UTIL.SYS_YES THEN
695 varMatSup :=MSC_UTIL.SYS_YES;
696 END IF;
697
698 IF parPOSupp = MSC_UTIL.SYS_YES THEN
699 varMatSup :=MSC_UTIL.SYS_YES;
700 END IF;
701
702 IF parInstrSupp = MSC_UTIL.SYS_YES THEN
703 varMatSup :=MSC_UTIL.SYS_YES;
704 END IF;
705
706 IF parReqSupp = MSC_UTIL.SYS_YES THEN
707 varMatSup :=MSC_UTIL.SYS_YES;
708 END IF;
709
710 IF parSO = MSC_UTIL.SYS_YES THEN
711 varMatDmd :=MSC_UTIL.SYS_YES;
712 End IF;
713
714 MSC_UTIL.MSC_DEBUG('LAUNCHING PRE-PROCESSOR');
715
716 MSC_UTIL.MSC_DEBUG('Setting Request Set');
717
718 setreq_success := fnd_submit.set_request_set('MSC','FNDRSSUB3385');
719
720 IF setreq_success THEN
721 disp_val := 'TRUE';
722 ELSE
723 disp_val := 'FALSE';
724 END IF;
725
726 IF NOT setreq_success THEN
727 MSC_UTIL.MSC_DEBUG('After Setting Request set'||disp_val);
728 raise req_submit_failed;
729 END IF;
730
731 BEGIN
732
733 -- Bug#9842448 Stage05 to Update TP_SITE_CODE
734 MSC_UTIL.MSC_DEBUG('Updating TP_SITE_CODE with LOCATION');
735 updateFlag:= MSC_UTIL.SYS_NO;
736 setreq_success:=fnd_submit.submit_program('MSC','MSCE1TPSITECODE','Stage05', parInstanceID,updateFlag);
737
738 IF setreq_success THEN
739 disp_val := 'TRUE';
740 ELSE
741 disp_val := 'FALSE';
742 END IF;
743
744 IF NOT setreq_success THEN
745 MSC_UTIL.MSC_DEBUG('After Updating TP_SITE_CODE TO LOCATION '||disp_val);
746 raise req_submit_failed;
747 END IF;
748
749 MSC_UTIL.MSC_DEBUG('Before Lauching Purge ODS Data');
750
751 IF locAtpRule= MSC_UTIL.SYS_YES THEN
752 varATPRules:=MSC_UTIL.SYS_NO;
753 END IF;
754
755 /* Purge ODS Data for Collected Entities (Stage10) */
756 setreq_success :=fnd_submit.submit_program('MSC','MSCPURLEGODSENT','Stage15',parInstanceID,varPurgeLocalId,varPurgeLocalId
757 ,varSuppCap, varATPRules, parBOMs,locParResrcs,locParRtng,locParOprns,varBOR, varPurgeGlobalODS,varPurgeGlobalODS, parDmdClasses,
758 varItemSubs,varPurgeGlobalODS, parExtFcst,parItems,varItemCtg,varPurgeGlobalODS,varKPIBIS, varMDS, varMPS, parOnhandSupp,
759 varParameter, parPlanners, varPOReceipts ,varProject, varPURREQPO, varReservesHard, varResourceNRA, parSS, parSO, varSH,
760 parShippingMethods,parItemSrcing ,varSUBINV, varSupplierResponse, varPurgeGlobalODS, varTrip, varUnitNO, varPurgeGlobalODS,varUomConv, varUserCompany
761 ,varUserSupplyDemand,varUserSupplyDemand, parWO, parSalesChannels, varFiscalCalendar, varIRO, varERO, varPaybackDemandSupply
762 ,varCurrencyConversion, varDeliveryDetails);
763
764 IF setreq_success THEN
765 disp_val := 'TRUE';
766 ELSE
767 disp_val := 'FALSE';
768 END IF;
769
770 IF NOT setreq_success THEN
771 MSC_UTIL.MSC_DEBUG('After Launching Purge ODS Data '||disp_val);
772 raise req_submit_failed;
773 END IF;
774
775 MSC_UTIL.MSC_DEBUG('Before Lauching Pre-processor');
776
777 -- Getting profile values set -- for bug # 11769639
778 varTimeOut := fnd_profile.value('MSC_E1APS_TIME_OUT');
779 varBatchSize := fnd_profile.value('MSC_E1APS_BATCH_SIZE');
780 varNumWorkers := fnd_profile.value('MSC_E1APS_NO_OF_WORKERS');
781
782 -- checking if the profile values are set
783 IF varTimeOut IS NULL THEN
784 varTimeOut := 180;
785 END IF;
786
787 IF varBatchSize IS NULL THEN
788 varBatchSize := 1000;
789 END IF;
790
791 IF varNumWorkers IS NULL THEN
792 varNumWorkers := 3;
793 END IF;
794
795 -- fix for bug # 11769639
796 -- replaced 3 default values with variables from profile values
797 -- for TimeOut, BatchSize, NumWorkers
798
799 setreq_success:=fnd_submit.submit_program('MSC','MSCPPM','Stage10',parInstanceID, varTimeOut, varBatchSize, varNumWorkers, locParCalendars, parDmdClasses
800 , parTradingPtnrs, varCtg, varItemCtg, parUOMs, varUomClass, varDesig, varProj
801 , parItems, varSuppCap, parSS, parShippingMethods, parItemSrcing, parBOMs, locParRtng
802 , locParResrcs, varMatSup, varMatDmd, varResrv, varResDmd, varItemCst, varParentReqId
803 , varFisCal, varSetup, varLinkDummy, varItemRollup, varLvlvalue, varLvlAssoc, varBooking
804 , varShipment, varMfgFct , parPriceLists, varCSData , varCSDummy,varCSRefresh, varCurrConv
805 , varUomConv, varCallingModule, varCompUsers , varItemSubs, parPlanners, varCompCal, varProfile
806 , varCalAssign, varIRO, varERO,parSalesChannels, varFiscalCalendar);
807
808 IF setreq_success THEN
809 disp_val := 'TRUE';
810 ELSE
811 disp_val := 'FALSE';
812 END IF;
813
814 IF NOT setreq_success THEN
815 MSC_UTIL.MSC_DEBUG('After Launching Pre-proccessor '||disp_val);
816 raise req_submit_failed;
817 END IF;
818
819 MSC_UTIL.MSC_DEBUG('Before Launching ODS LOAD');
820
821 -- fix for bug # 11769639
822 -- replaced 2 default values with variables from profile values
823 -- for TimeOut, NumWorkers
824
825 setreq_success:=fnd_submit.submit_program('MSC','MSCPDC','Stage20',parInstanceID, varTimeOut, varNumWorkers
826 , varRecalcResAvailability, varSourcingHistoryEnabled,varPurgeSourcingHistory, varAPCCRefresh);
827
828 IF setreq_success THEN
829 disp_val := 'TRUE';
830 ELSE
831 disp_val := 'FALSE';
832 END IF;
833
834 IF NOT setreq_success THEN
835 MSC_UTIL.MSC_DEBUG('After Launching ODS Load'||disp_val);
836 raise req_submit_failed;
837 END IF;
838
839 -- Bug#9842448 Stage90 to Update TP_SITE_CODE
840 MSC_UTIL.MSC_DEBUG('Updating TP_SITE_CODE with BILL_TO OR SHIP_TO');
841 updateFlag:= MSC_UTIL.SYS_YES;
842
843 setreq_success:=fnd_submit.submit_program('MSC','MSCE1TPSITECODE','Stage90', parInstanceID,updateFlag);
844
845 IF setreq_success THEN
846 disp_val := 'TRUE';
847 ELSE
848 disp_val := 'FALSE';
849 END IF;
850
851 IF NOT setreq_success THEN
852 MSC_UTIL.MSC_DEBUG('After Updating TP_SITE_CODE TO BILL_TO or SHIP_TO '||disp_val);
853 raise req_submit_failed;
854 END IF;
855
856 set_req_id := fnd_submit.submit_set(NULL,FALSE);
857
858 IF set_req_id = 0 THEN
859 FND_MESSAGE.SET_NAME('MSC','Request Set Submission Failed');
860 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, FND_MESSAGE.GET);
861 RETCODE := MSC_UTIL.G_ERROR;
862 RETURN;
863 END IF;
864
865
866 WSURL := fnd_profile.value('MSC_E1APS_ODIURL');
867 IF WSURL IS NOT NULL THEN
868 /* Launching Post-Process Custom Hook ODI Scenario */
869 scenario_name := 'POSTPROCESSHOOKPKG';
870 scenario_version := '001';
871 scenario_param := 'E1TOAPSPROJECT.PVV_POST_PROCESS_VAR=';
872 scenario_param := scenario_param
873 ||l_instance_code
874 || ':'
875 || MSC_E1APS_UTIL.COL_PLAN_DATA;
876 post_process_odi :=MSC_E1APS_DEMCL.CALL_ODIEXE(scenario_name, scenario_version, scenario_param, WSURL);
877
878 IF post_process_odi = FALSE THEN
879 mailstat := MSC_E1_Mail_ODIExecute('MAIL',WSURL);
880 RETCODE := MSC_UTIL.G_ERROR;
881 RETURN;
882 END IF;
883 END IF;
884
885 /* Launching Collections Post-Proces Custom Hook*/
886 MSC_E1APS_HOOK.COL_PLAN_DATA_POST_PROCESS(ERRBUF,RETCODE);
887 IF RETCODE = MSC_UTIL.G_ERROR THEN
888 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error Message:' || ERRBUF);
889 RETCODE := MSC_UTIL.G_ERROR;
890 RETURN;
891 END IF;
892 RETURN;
893
894 exception
895 when req_submit_failed then
896 FND_MESSAGE.SET_NAME('MSC','Request Set Failed');
897 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, FND_MESSAGE.GET);
898 RETCODE := MSC_UTIL.G_ERROR;
899 RETURN;
900 when others then
901 FND_MESSAGE.SET_NAME('MSC','Request Set Submission Failed');
902 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, FND_MESSAGE.GET);
903 RETCODE := MSC_UTIL.G_ERROR;
904 RETURN;
905 END;
906 END IF;
907
908 END; -- Procedure MSC_E1APS_SCR_LIST
909
910 END MSC_E1_APS_PDP;
911