[Home] [Help]
PACKAGE BODY: APPS.MSC_E1APS_DEMCL
Source
1 PACKAGE BODY MSC_E1APS_DEMCL AS -- body
2 --# $Header: MSCE1DMB.pls 120.2 2011/01/14 09:43:28 sravinoo noship $
3 /* Function to call MSC_E1APS_ODIScenarioExecute*/
4 FUNCTION CALL_ODIEXE( scenario_name IN VARCHAR2,
5 scenario_version IN VARCHAR2,
6 scenario_param IN VARCHAR2,
7 wsurl IN VARCHAR2)
8 RETURN BOOLEAN
9 IS
10 err_message VARCHAR2(1900);
11 start_index INTEGER;
12 ebd_index INTEGER;
13 return_str VARCHAR2(2000);
14 session_num VARCHAR2(10);
15 err_msg1 varchar2(4000);
16 BEGIN
17 /*execute ODI scenario*/
18 BEGIN
19 IF wsurl IS NULL THEN
20 RETURN TRUE;
21 END IF;
22 SELECT MSC_E1APS_UTIL.MSC_E1APS_ODIScenarioExecute(scenario_name,scenario_version,scenario_param,wsurl)
23 INTO return_str
24 FROM dual;
25
26 EXCEPTION
27 WHEN OTHERS THEN
28 SELECT instr(return_str,'#')
29 INTO start_index
30 FROM dual;
31
32 SELECT SUBSTR(return_str,start_index+1,1800)
33 INTO err_message
34 FROM dual;
35
36 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Scenario'
37 || scenario_name
38 || ' execution failed.'
39 || err_message);
40 RETURN FALSE;
41 END;
42 SELECT instr(return_str,'#')
43 INTO start_index
44 FROM dual;
45
46 SELECT SUBSTR(return_str,0,start_index-1)
47 INTO session_num
48 FROM dual;
49
50 SELECT SUBSTR(return_str,start_index+1,1800)
51 INTO err_message
52 FROM dual;
53
54 IF session_num = '-1' THEN
55 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Scenario '
56 || scenario_name
57 || ' executed with errors. Session #: '
58 || session_num
59 || ' , Error Message: '
60 || err_message);
61 RETURN FALSE;
62 END IF;
63 IF session_num <>'-1' AND LENGTH(err_message) > 0 THEN
64 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Scenario '
65 || scenario_name
66 || ' executed with errors. Session #: '
67 || session_num
68 || ' , Error Message: '
69 || err_message);
70 RETURN FALSE;
71 END IF;
72
73 -- Changes for 11G
74 IF ( (session_num <> '-1') AND (LENGTH(err_message) IS NULL) ) THEN
75 err_msg1 := MSC_E1APS_UTIL.GetODIErrorMessage(session_num);
76 IF ( LENGTH(err_msg1) IS NOT NULL ) THEN
77 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Scenario '
78 || scenario_name
79 || ' FAILED. Session # : ' || session_num);
80 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error : ' || err_msg1);
81 RETURN FALSE;
82 END IF;
83 END IF;
84
85 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Scenario '
86 || scenario_name
87 || ' execution is successful.');
88 RETURN TRUE;
89 END CALL_ODIEXE;
90 /* PROCEDURE For Collect Price List And UOM */
91 PROCEDURE DEM_PL_UOM(ERRBUF OUT NOCOPY VARCHAR2,
92 RETCODE OUT NOCOPY VARCHAR2,
93 p_instance_id IN NUMBER,
94 p_price_list IN NUMBER,
95 p_uom IN NUMBER)
96 IS
97
98 /* Variables to Call PL CM */
99 v_request_id1 NUMBER(20) DEFAULT 0;
100 p_start_date VARCHAR2(20);
101 p_end_date VARCHAR2(20);
102 p_include_all NUMBER(20);
103 p_include_prl_list VARCHAR2(1000);
104 p_exclude_prl_list VARCHAR2(1000);
105 total_rows NUMBER(10);
106 /* Variables to Call UOM CM */
107 v_request_id2 NUMBER(20) DEFAULT 0;
108 p_include_uom_list VARCHAR2(1000);
109 p_exclude_uom_list VARCHAR2(1000);
110 l_instance_code VARCHAR2(3);
111 scenario_name VARCHAR2(200);
112 scenario_version VARCHAR2(100);
113 scenario_param VARCHAR2(200);
114 pre_process_odi BOOLEAN;
115 ret_value1 BOOLEAN;
116 odi_url VARCHAR2(1000);
117
118
119 BEGIN
120
121 /* Launching Price List and UOM Pre-Proces Custom Hook*/
122 MSC_E1APS_HOOK.COL_PRC_UOM_PRE_PROCESS(ERRBUF,RETCODE);
123
124 IF RETCODE = MSC_UTIL.G_ERROR THEN
125 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error Message:' || ERRBUF);
126 RETCODE := MSC_UTIL.G_ERROR;
127 RETURN;
128 END IF;
129
130 /* Launching ODI Pre-Process Custom Hook Scenarios */
131 odi_url := fnd_profile.value('MSC_E1APS_ODIURL'); --Checking ODI Profile
132 IF odi_url IS NOT NULL THEN
133
134 select instance_code into l_instance_code
135 from msc_apps_instances
136 where instance_id = p_instance_id;
137
138 scenario_name := 'PREPROCESSHOOKPKG';
139 scenario_version := '001';
140 scenario_param := 'E1TOAPSPROJECT.PVV_PRE_PROCESS_VAR=';
141 scenario_param := scenario_param
142 ||l_instance_code
143 || ':'
144 || MSC_E1APS_UTIL.COL_PRC_UOM;
145 pre_process_odi :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
146
147 IF pre_process_odi = FALSE THEN
148 /* Executing Mail Scenario */
149 scenario_name := 'MAIL';
150 scenario_version := '001';
151 scenario_param := '';
152 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
153 RETCODE := MSC_UTIL.G_ERROR;
154 RETURN;
155 END IF;
156 END IF;
157
158 /* Launching Price List Collections*/
159 IF p_price_list = 1 THEN
160 BEGIN
161 /* Step 1: Deleting all rows from MSD_DEM_PRICE_LISTS */
162 DELETE
163 FROM MSD_DEM_PRICE_LISTS;
164
165 COMMIT;
166 EXCEPTION
167 WHEN OTHERS THEN
168 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,'No Rows Deleted from MSD_DEM_PRICE_LISTS');
169 RETCODE := MSC_UTIL.G_ERROR;
170 RETURN;
171 END;
172 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,'Rows Deleted from MSD_DEM_PRICE_LISTS');
173
174 /* Step 1: Inserting rows into MSD_DEM_PRICE_LISTS from MSD_ST_PRICE_LIST */
175 BEGIN
176 INSERT
177 INTO MSD_DEM_PRICE_LISTS
178 ( PRICE_LIST_NAME ,
179 LAST_UPDATE_LOGIN,
180 LAST_UPDATE_DATE ,
181 LAST_UPDATED_BY ,
182 CREATION_DATE ,
183 CREATED_BY
184 )
185 SELECT DISTINCT PRICE_LIST_NAME,
186 -1 ,
187 SYSDATE ,
188 -1 ,
189 SYSDATE ,
190 -1
191 FROM MSC_ST_PRICE_LIST;
192
193 COMMIT;
194 EXCEPTION
195 WHEN OTHERS THEN
196 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,'No Rows Inserted into MSD_DEM_PRICE_LISTS from MSD_ST_PRICE_LIS');
197 RETCODE := MSC_UTIL.G_ERROR;
198 RETURN;
199 END;
200 SELECT COUNT(*)
201 INTO total_rows
202 FROM MSD_DEM_PRICE_LISTS;
203 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,'Inserted ' || total_rows || 'rows into MSD_DEM_PRICE_LISTS from MSD_ST_PRICE_LIS');
204
205
206 p_start_date := fnd_profile.value('MSC_E1APS_DEM_START_DATE_IN_MONTHS');
207 p_end_date := fnd_profile.value('MSC_E1APS_DEM_END_DATE_IN_MONTHS');
208
209 IF total_rows <>0 THEN
210 p_start_date := fnd_date.date_to_canonical(ADD_MONTHS(SYSDATE, - p_start_date));
211 p_end_date := fnd_date.date_to_canonical(ADD_MONTHS(SYSDATE, + p_end_date));
212 p_include_all := 1;
213 p_include_prl_list := NULL;
214 p_exclude_prl_list := NULL;
215
216 v_request_id1 := fnd_request.submit_request('MSD', -- appln short name
217 'MSDDEMPRL', -- short name of conc pgm
218 NULL, -- description
219 NULL, -- start date
220 FALSE, -- sub request
221 p_instance_id,
222 p_start_date,
223 p_end_date,
224 p_include_all,
225 p_include_prl_list,
226 p_exclude_prl_list);
227 IF v_request_id1 = 0 THEN
228 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error in submitting concurrent program for Demantra Price List Collection');
229 RETCODE := MSC_UTIL.G_ERROR;
230 RETURN;
231 ELSE
232 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,'Demantra Price List Launched. Request Id:'
233 || v_request_id1 );
234
235
236
237 END IF;
238 END IF;
239 END IF;
240 /* Launching UOM Collections */
241 IF p_uom = 1 THEN
242 p_include_all := 1;
243 p_include_uom_list:= NULL;
244 p_exclude_uom_list:= NULL;
245
246 v_request_id2 := fnd_request.submit_request('MSD', -- appln short name
247 'MSDDEMUOM', -- short name of conc pgm
248 NULL, -- description
249 NULL, -- start date
250 FALSE, -- sub request
251 p_instance_id,
252 p_include_all,
253 p_include_uom_list,
254 p_exclude_uom_list);
255 IF v_request_id2 = 0 THEN
256 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error in submitting concurrent program for Demantra UOM Collection');
257 RETCODE := MSC_UTIL.G_ERROR;
258 RETURN;
259 ELSE
260 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,'Demantra UOM Collection Launched. Request Id:'
261 || v_request_id2 );
262 END IF;
263 END IF;
264
265 /* Launching ODI Post-Process Custom Hook Scenarios */
266 odi_url := fnd_profile.value('MSC_E1APS_ODIURL'); --Checking ODI Profile
267 IF odi_url IS NOT NULL THEN
268
269 select instance_code into l_instance_code
270 from msc_apps_instances
271 where instance_id = p_instance_id;
272
273 scenario_name := 'POSTPROCESSHOOKPKG';
274 scenario_version := '001';
275 scenario_param := 'E1TOAPSPROJECT.PVV_POST_PROCESS_VAR=';
276 scenario_param := scenario_param
277 ||l_instance_code
278 || ':'
279 || MSC_E1APS_UTIL.COL_PRC_UOM;
280 pre_process_odi :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
281
282 /* Executing Mail Scenario */
283 scenario_name := 'MAIL';
284 scenario_version := '001';
285 scenario_param := '';
286 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
287
288 IF pre_process_odi = FALSE THEN
289 RETCODE := MSC_UTIL.G_ERROR;
290 RETURN;
291 END IF;
292 END IF;
293
294 /* Launching Price List and UOM Post-Proces Custom Hook*/
295 MSC_E1APS_HOOK.COL_PRC_UOM_POST_PROCESS(ERRBUF,RETCODE);
296
300 RETURN;
297 IF RETCODE = MSC_UTIL.G_ERROR THEN
298 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error Message:' || ERRBUF);
299 RETCODE := MSC_UTIL.G_ERROR;
301 END IF;
302
303 END DEM_PL_UOM;
304 /* Procedure for Loading Sales History*/
305 PROCEDURE DEM_SH(ERRBUF OUT NOCOPY VARCHAR2,
306 RETCODE OUT NOCOPY VARCHAR2,
307 p_instance_id IN NUMBER,
308 p_auto_run IN NUMBER )
309 IS
310 lv_request_id1 NUMBER(20);
311 p_file_seperator VARCHAR(1);
312 p_control_path VARCHAR2(100);
313 p_data_path VARCHAR2(100);
314 p_file_name VARCHAR2(20);
315 ReturnStr VARCHAR2(2000);
316 odi_url VARCHAR2(1000);
317 fc_url VARCHAR2(1000);
318 l_call_status BOOLEAN;
319 l_phase VARCHAR2(80);
320 l_status VARCHAR2(80);
321 l_dev_phase VARCHAR2(80);
322 l_dev_status VARCHAR2(80);
323 l_message VARCHAR2(2048);
324 ret_value BOOLEAN DEFAULT FALSE;
325 ret_value1 BOOLEAN DEFAULT FALSE;
326 source_file VARCHAR2(200);
327 destination_file VARCHAR2(200);
328 fc_ret_value BOOLEAN DEFAULT FALSE;
329 scenario_name VARCHAR2(200);
330 scenario_version VARCHAR2(100);
331 scenario_param VARCHAR2(200);
332 v_request_id NUMBER;
333 l_sql VARCHAR2(100);
334 ErrMessage VARCHAR2(1900);
335 STARTINDEX INTEGER;
336 pre_process_odi BOOLEAN;
337 post_process_odi BOOLEAN;
338 l_instance_code VARCHAR2(3);
339
340
341 BEGIN
342
343 /* Launching Sales History Pre-Proces Custom Hook*/
344 MSC_E1APS_HOOK.COL_PRC_UOM_PRE_PROCESS(ERRBUF,RETCODE);
345
346 IF RETCODE = MSC_UTIL.G_ERROR THEN
347 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error Message:' || ERRBUF);
348 RETCODE := MSC_UTIL.G_ERROR;
349 RETURN;
350 END IF;
351
352 /*Checking ODI Profile */
353 odi_url := fnd_profile.value('MSC_E1APS_ODIURL');
354 IF odi_url IS NOT NULL THEN
355 /* Launching Pre-Process Custom Hook ODI Scenario */
356 select instance_code into l_instance_code from msc_apps_instances
357 where instance_id = p_instance_id;
358
359 scenario_name := 'PREPROCESSHOOKPKG';
360 scenario_version := '001';
361 scenario_param := 'E1TOAPSPROJECT.PVV_PRE_PROCESS_VAR=';
362 scenario_param := scenario_param
363 ||l_instance_code
364 || ':'
365 || MSC_E1APS_UTIL.COL_SALES_HST;
366 pre_process_odi :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
367
368 IF pre_process_odi = FALSE THEN
369 /* Executing Mail Scenario */
370 scenario_name := 'MAIL';
371 scenario_version := '001';
372 scenario_param := '';
373 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
374 RETCODE := MSC_UTIL.G_ERROR;
375 RETURN;
376 END IF;
377 END IF;
378 /* Truncating T_SRC_SALES_TMPL table */
379 Begin
380 l_sql:= 'Truncate table '
381 || MSD_DEM_DEMANTRA_UTILITIES.GET_DEMANTRA_SCHEMA
382 || '.T_SRC_SALES_TMPL';
383 EXECUTE immediate l_sql;
384 EXCEPTION
385 WHEN OTHERS THEN
386 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,'No Rows Truncated from T_SRC_SALES_TMPL');
387 RETCODE := MSC_UTIL.G_ERROR;
388 RETURN;
389 END;
390 /* Accessing instance_id from MSC_APPS_INSTANCES*/
391 SELECT ATTRIBUTE13,
392 ATTRIBUTE12,
393 ATTRIBUTE13,
394 ATTRIBUTE14
395 INTO p_data_path ,
396 p_control_path ,
397 destination_file,
398 source_file
399 FROM MSC_APPS_INSTANCES
400 WHERE INSTANCE_ID = p_instance_id;
401
402 BEGIN
403 /* Step 1: Deleting all rows and extracting data from MSC_CALENDARS */
404 DELETE
405 FROM MSD_DEM_CALENDARS;
406
407 COMMIT;
408 EXCEPTION
409 WHEN OTHERS THEN
410 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,'No Rows Deleted from MSD_DEM_CALENDARS');
411 RETCODE := MSC_UTIL.G_ERROR;
412 RETURN;
413 END;
414 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,'Rows Deleted from MSD_DEM_CALENDARS');
415 BEGIN
416 INSERT
417 INTO MSD_DEM_CALENDARS
418 (
419 INSTANCE ,
420 CALENDAR_TYPE ,
421 CALENDAR_CODE ,
422 LAST_UPDATE_DATE,
423 LAST_UPDATED_BY ,
424 CREATION_DATE ,
425 CREATED_BY ,
426 LAST_UPDATE_LOGIN
427 )
428 SELECT MAI.INSTANCE_CODE ,
429 'Manufacturing' ,
430 MC.CALENDAR_CODE ,
434 MC.CREATED_BY ,
431 MC.LAST_UPDATE_DATE,
432 MC.LAST_UPDATED_BY ,
433 MC.CREATION_DATE ,
435 MC.LAST_UPDATE_LOGIN
436 FROM MSC_CALENDARS MC,
437 MSC_APPS_INSTANCES MAI
438 WHERE MC.SR_INSTANCE_ID = p_instance_id
439 AND MAI.INSTANCE_ID = MC.SR_INSTANCE_ID;
440
441 COMMIT;
442 EXCEPTION
443 WHEN OTHERS THEN
444 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,'No Rows Inserted into MSD_DEM_CALENDARS from MSC_CALENDARS');
445 RETCODE := MSC_UTIL.G_ERROR;
446 RETURN;
447 END;
448 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,'Rows Inserted into MSD_DEM_CALENDARS from MSC_CALENDARS');
449 /*ODI Initialize*/
450 odi_url := fnd_profile.value('MSC_E1APS_ODIURL');
451 IF odi_url IS NOT NULL THEN
452 BEGIN
453 SELECT MSC_E1APS_UTIL.MSC_E1APS_ODIInitialize(odi_url,2)
454 INTO ReturnStr
455 FROM dual;
456
457 EXCEPTION
458 WHEN OTHERS THEN
459 select instr(ReturnStr,'#') into StartIndex from dual;
460 select substr(ReturnStr,StartIndex+1,1800) into ErrMessage from dual;
461
462 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Intialization failed. Error message' || ErrMessage);
463 RETCODE := MSC_UTIL.G_ERROR;
464 RETURN;
465 END;
466 select instr(ReturnStr,'#') into StartIndex from dual;
467 select substr(ReturnStr,StartIndex+1,1800) into ErrMessage from dual;
468
469 IF(length(ErrMessage) > 0) THEN
470 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Intialization failed. Error message' || ErrMessage);
471 RETCODE := MSC_UTIL.G_ERROR;
472 RETURN;
473 END IF;
474 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Initializion is successful');
475 END IF;
476 /*Calling 'MSC_E1APS_ODIScenarioExecute' Function by using the 'CALL_ODIEXE' function */
477 scenario_name := 'LOADE1SALESORDERHISTORYDATATODMPKG';
478 scenario_version := '001';
479 scenario_param := '';
480 ret_value :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
481 /* Copies the file from source path to destination Path */
482 fc_url := fnd_profile.value('MSC_E1APS_FCURL');
483
484 IF fc_url IS NOT NULL AND ret_value THEN
485 scenario_name := 'IMPORTFILETOAPSSERVER';
486 scenario_version := '001';
487 scenario_param := '';
488 fc_ret_value :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, fc_url);
489
490 IF fc_ret_value THEN
491 /* Executing Mail Scenario */
492 scenario_name := 'MAIL';
493 scenario_version := '001';
494 scenario_param := '';
495 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
496 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'File copied Successfully .' );
497 fc_ret_value:=ret_value;
498
499 ELSE
500 /* Executing Mail Scenario */
501 scenario_name := 'MAIL';
502 scenario_version := '001';
503 scenario_param := '';
504 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
505
506 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'File copy failed.' );
507 RETCODE := MSC_UTIL.G_ERROR;
508 RETURN;
509 END IF;
510 ELSE
511
512 /* Executing Mail Scenario */
513 scenario_name := 'MAIL';
514 scenario_version := '001';
515 scenario_param := '';
516 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
517
518 fc_ret_value:=ret_value;
519 IF ret_value =FALSE then
520 RETCODE := MSC_UTIL.G_ERROR;
521 RETURN;
522 END IF;
523 END IF;
524
525 IF fc_ret_value THEN
526 p_file_seperator := '/';
527 p_file_name := 'DemHistory.dat';
528 /* Step 3: Launching Demantra Collections */
529 msd_dem_ssl_rs.run_rs(errbuf, retcode, p_instance_id, p_auto_run, p_file_seperator, p_control_path, p_data_path, p_file_name );
530 IF NVL(to_number(retcode),0) <>0 THEN
531 msd_dem_common_utilities.log_message('Error Loading msd_dem_ssl_rs.run_rs Concurrent Request'
532 || errbuf
533 || retcode);
534 RETCODE := MSC_UTIL.G_ERROR;
535 RETURN;
536 ELSE
537 msd_dem_common_utilities.log_message('Demantra Collections Launched successfully');
538 END IF;
539 /*Launching CP for Calender */
540 v_request_id := fnd_request.submit_request('MSD', -- appln short name
541 'MSDDEMDC', -- short name of conc pgm
542 NULL, -- description
543 NULL, -- start date
547 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error in submitting concurrent program for Demantra Calenders Collection');
544 FALSE, -- sub request
545 1 );
546 IF v_request_id = 0 THEN
548 RETCODE := MSC_UTIL.G_ERROR;
549 RETURN;
550 ELSE
551 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR,'Demantra Calenders Collections Launched. Request Id:'
552 || v_request_id );
553 END IF;
554 END IF;
555
556 /*Checking ODI Profile */
557 odi_url := fnd_profile.value('MSC_E1APS_ODIURL');
558 IF odi_url IS NOT NULL THEN
559
560 /* Launching Post-Process Custom Hook ODI Scenario */
561 scenario_name := 'POSTPROCESSHOOKPKG';
562 scenario_version := '001';
563 scenario_param := 'E1TOAPSPROJECT.PVV_POST_PROCESS_VAR=';
564 scenario_param := scenario_param
565 ||l_instance_code
566 || ':'
567 || MSC_E1APS_UTIL.COL_SALES_HST;
568 post_process_odi :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
569
570 IF post_process_odi = FALSE THEN
571 /* Executing Mail Scenario */
572 scenario_name := 'MAIL';
573 scenario_version := '001';
574 scenario_param := '';
575 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
576 RETCODE := MSC_UTIL.G_ERROR;
577 RETURN;
578 ELSE
579 /* Executing Mail Scenario */
580 scenario_name := 'MAIL';
581 scenario_version := '001';
582 scenario_param := '';
583 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
584 END IF;
585 END IF;
586
587 /* Launching Sales History Post-Proces Custom Hook*/
588 MSC_E1APS_HOOK.COL_PRC_UOM_POST_PROCESS(ERRBUF,RETCODE);
589
590 IF RETCODE = MSC_UTIL.G_ERROR THEN
591 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error Message:' || ERRBUF);
592 RETCODE := MSC_UTIL.G_ERROR;
593 RETURN;
594 ELSE
595 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Post Process Custom Hook Procedure Excecuted Successfully');
596 END IF;
597
598 END DEM_SH;
599 /*Procedure for PTP Collections*/
600 PROCEDURE DEM_PTP(ERRBUF OUT NOCOPY VARCHAR2,
601 RETCODE OUT NOCOPY VARCHAR2,
602 p_instance_id IN NUMBER,
603 p_list_price IN NUMBER,
604 p_item_cost IN NUMBER,
605 p_price_history IN NUMBER)
606 IS
607 ReturnStr VARCHAR2(2000);
608 WSURL VARCHAR2(1000);
609 l_call_status BOOLEAN;
610 l_phase VARCHAR2(80);
611 l_status VARCHAR2(80);
612 l_dev_phase VARCHAR2(80);
613 l_dev_status VARCHAR2(80);
614 l_message VARCHAR2(2048);
615 l_wf_lookup_code VARCHAR2(200);
616 l_user_id NUMBER;
617 odi_url VARCHAR2(300);
618 fc_url VARCHAR2(1000);
619 ret_value BOOLEAN;
620 ret_value1 BOOLEAN;
621 source_file VARCHAR2(200);
622 destination_file VARCHAR2(200);
623 fc_ret_value BOOLEAN;
624 scenario_name VARCHAR2(200);
625 scenario_version VARCHAR2(100);
626 scenario_param VARCHAR2(200);
627 process_id VARCHAR2(10);
628 ErrMessage VARCHAR2(1900);
629 STARTINDEX INTEGER;
630 l_instance_code VARCHAR2(3);
631 pre_process_odi BOOLEAN;
632 post_process_odi BOOLEAN;
633 BEGIN
634
635 /* Launching Collect PTP Pre-Proces Custom Hook*/
636 MSC_E1APS_HOOK.COL_PTP_DATA_PRE_PROCESS(ERRBUF,RETCODE);
637
638 IF RETCODE = MSC_UTIL.G_ERROR THEN
639 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error Message:' || ERRBUF);
640 RETCODE := MSC_UTIL.G_ERROR;
641 RETURN;
642 END IF;
643
644 /* Checking ODI Profile*/
645 odi_url := fnd_profile.value('MSC_E1APS_ODIURL');
646 IF odi_url IS NOT NULL THEN
647 /* Launching Pre-Process Custom Hook ODI Scenario */
648 select instance_code into l_instance_code from msc_apps_instances
649 where instance_id = p_instance_id;
650
651 scenario_name := 'PREPROCESSHOOKPKG';
652 scenario_version := '001';
653 scenario_param := 'E1TOAPSPROJECT.PVV_PRE_PROCESS_VAR=';
654 scenario_param := scenario_param
655 ||l_instance_code
656 || ':'
657 || MSC_E1APS_UTIL.COL_PTP_DATA;
658 pre_process_odi :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
659
660 IF pre_process_odi = FALSE THEN
661 /* Executing Mail Scenario */
662 scenario_name := 'MAIL';
663 scenario_version := '001';
664 scenario_param := '';
668 END IF;
665 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
666 RETCODE := MSC_UTIL.G_ERROR;
667 RETURN;
669 END IF;
670
671
672 /* Checking ODI Profile*/
673 odi_url := fnd_profile.value('MSC_E1APS_ODIURL');
674 /* Invoke ODISenario*/
675 IF odi_url IS NOT NULL THEN
676 BEGIN
677 SELECT MSC_E1APS_UTIL.MSC_E1APS_ODIInitialize(odi_url,2)
678 INTO ReturnStr
679 FROM dual;
680
681 EXCEPTION
682 WHEN OTHERS THEN
683 select instr(ReturnStr,'#') into StartIndex from dual;
684 select substr(ReturnStr,StartIndex+1,1800) into ErrMessage from dual;
685
686 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Intialization failed. Error message' || ErrMessage);
687 RETCODE := MSC_UTIL.G_ERROR;
688 RETURN;
689 END;
690 select instr(ReturnStr,'#') into StartIndex from dual;
691 select substr(ReturnStr,StartIndex+1,1800) into ErrMessage from dual;
692
693 IF(length(ErrMessage) > 0) THEN
694 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Intialization failed. Error message' || ErrMessage);
695 RETCODE := MSC_UTIL.G_ERROR;
696 RETURN;
697 END IF;
698 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Initializion is successful');
699
700 END IF;
701 SELECT ATTRIBUTE14,
702 ATTRIBUTE13
703 INTO source_file,
704 destination_file
705 FROM MSC_APPS_INSTANCES
706 WHERE INSTANCE_ID = p_instance_id;
707
708 /* Bug#8224935 - APP ID */
709 l_user_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID', 'COMP_PTP', 1, 'user_id'));
710 /*Launching LIST PRICE ODI Senario*/
711 IF p_list_price = 1 THEN
712 /*Calling 'MSC_E1APS_ODIScenarioExecute' Function by using the 'CALL_ODIEXE' function
713 by passing the Senario name, version,parameter,odiurl */
714 scenario_name := 'LOADE1LISTPRICEDATATODMPKG';
715 scenario_version := '001';
716 scenario_param := '';
717 ret_value :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
718
719 /* Copies the file from source path to destination Path */
720 fc_url := fnd_profile.value('MSC_E1APS_FCURL');
721 fc_ret_value:=TRUE;
722 IF fc_url IS NOT NULL AND ret_value THEN
723 scenario_name := 'IMPORTFILESTODEMANTRASERVER';
724 scenario_version := '001';
725 scenario_param := '';
726 fc_ret_value :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, fc_url);
727
728 IF fc_ret_value = FALSE THEN
729 /* Executing Mail Scenario */
730 scenario_name := 'MAIL';
731 scenario_version := '001';
732 scenario_param := '';
733 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
734
735 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'File copy failed.' );
736 RETCODE := MSC_UTIL.G_ERROR;
737 RETURN;
738 END IF;
739 ELSE
740
741 /* Executing Mail Scenario */
742 scenario_name := 'MAIL';
743 scenario_version := '001';
744 scenario_param := '';
745 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
746
747 fc_ret_value:=ret_value;
748 IF ret_value =FALSE then
749 RETCODE := MSC_UTIL.G_ERROR;
750 RETURN;
751 END IF;
752 END IF;
753
754 IF fc_ret_value THEN
755 /* Calling LIST PRICE DEM WorkFlow*/
756 l_wf_lookup_code := 'WF_AIA_E1_PTP_PROMOPRICE_DWNLD';
757 MSC_E1APS_UTIL.DEM_WORKFLOW(errbuf, RETCODE,l_wf_lookup_code , process_id, l_user_id);
758 IF retcode= -1 OR process_id= -1 THEN
759 msd_dem_common_utilities.log_message('LIST PRICE DEM WORKFLOW NOT LAUNCHED. Process ID: ' || process_id);
760 RETCODE := MSC_UTIL.G_ERROR;
761 RETURN;
762 ELSE
763 msd_dem_common_utilities.log_message('LIST PRICE DEM WORKFLOW LAUNCHED. Process ID: ' || process_id);
764 END IF;
765 END IF;
766 END IF;
767 /*Launching ITEM COST ODI Senario*/
768 IF p_item_cost = 1 THEN
769 /*Calling 'MSC_E1APS_ODIScenarioExecute' Function by using the 'CALL_ODIEXE' function
770 by passing the Senario name, version,parameter,odiurl */
771 scenario_name := 'LOADE1ITEMCOSTDATATODMPKG';
772 scenario_version := '001';
773 scenario_param := '';
774 ret_value :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
775
776 /* Copies the file from source path to destination Path */
777
778 fc_url := fnd_profile.value('MSC_E1APS_FCURL');
779 fc_ret_value:=TRUE;
783 scenario_param := '';
780 IF fc_url IS NOT NULL AND ret_value THEN
781 scenario_name := 'IMPORTFILESTODEMANTRASERVER';
782 scenario_version := '001';
784 fc_ret_value :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, fc_url);
785
786 IF fc_ret_value = FALSE THEN
787 /* Executing Mail Scenario */
788 scenario_name := 'MAIL';
789 scenario_version := '001';
790 scenario_param := '';
791 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
792
793 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'File copy failed.' );
794 RETCODE := MSC_UTIL.G_ERROR;
795 RETURN;
796 END IF;
797 ELSE
798 /* Executing Mail Scenario*/
799 scenario_name := 'MAIL';
800 scenario_version := '001';
801 scenario_param := '';
802 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
803
804 fc_ret_value:=ret_value;
805 IF ret_value =FALSE then
806 RETCODE := MSC_UTIL.G_ERROR;
807 RETURN;
808 END IF;
809 END IF;
810
811 IF fc_ret_value THEN
812 /* Calling DEM WorkFlow*/
813 l_wf_lookup_code := 'WF_AIA_E1_PTP_PROMOCOST_DWNLD';
814 MSC_E1APS_UTIL.DEM_WORKFLOW(errbuf, RETCODE,l_wf_lookup_code , process_id, l_user_id);
815 IF retcode= -1 OR process_id= -1 THEN
816 msd_dem_common_utilities.log_message('ITEM COST DEM WORKFLOW NOT LAUNCHED.' || process_id);
817 RETCODE := MSC_UTIL.G_ERROR;
818 RETURN;
819 ELSE
820 msd_dem_common_utilities.log_message('ITEM COST DEM WORKFLOW LAUNCHED. Process ID: ' || process_id);
821 END IF;
822 END IF;
823 END IF;
824 /*Launching PRICE HISTORY ODI Senario*/
825 IF p_price_history = 1 THEN
826 /*Calling 'MSC_E1APS_ODIScenarioExecute' Function by using the 'CALL_ODIEXE' function */
827 scenario_name := 'LOADE1PRICEHISTORYDATATODMPKG';
828 scenario_version := '001';
829 scenario_param := '';
830 ret_value :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
831
832 /* Copies the file from source path to destination Path */
833 fc_url := fnd_profile.value('MSC_E1APS_FCURL');
834 fc_ret_value:=TRUE;
835 IF fc_url IS NOT NULL AND ret_value THEN
836 scenario_name := 'IMPORTFILESTODEMANTRASERVER';
837 scenario_version := '001';
838 scenario_param := '';
839 fc_ret_value :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, fc_url);
840 IF fc_ret_value = FALSE THEN
841 /* Executing Mail Scenario */
842 scenario_name := 'MAIL';
843 scenario_version := '001';
844 scenario_param := '';
845 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
846
847 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'File copy failed.' );
848 RETCODE := MSC_UTIL.G_ERROR;
849 RETURN;
850 END IF;
851 ELSE
852 /* Executing Mail Scenario*/
853 scenario_name := 'MAIL';
854 scenario_version := '001';
855 scenario_param := '';
856 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
857
858 fc_ret_value:=ret_value;
859 IF ret_value =FALSE then
860 RETCODE := MSC_UTIL.G_ERROR;
861 RETURN;
862 END IF;
863 END IF;
864
865 IF fc_ret_value THEN
866 /* Calling DEM WorkFlow*/
867 l_wf_lookup_code := 'WF_AIA_E1_PTP_PRICEHIST_DWNLD';
868 MSC_E1APS_UTIL.DEM_WORKFLOW(errbuf, RETCODE,l_wf_lookup_code , process_id, l_user_id);
869 IF retcode= -1 OR process_id= -1 THEN
870 msd_dem_common_utilities.log_message('PRICE HISTORY DEM WORKFLOW NOT LAUNCHED.Process ID: ' || process_id);
871 RETCODE := MSC_UTIL.G_ERROR;
872 RETURN;
873 ELSE
874 msd_dem_common_utilities.log_message('PRICE HISTORY DEM WORKFLOW LAUNCHED. Process ID: ' || process_id);
875 END IF;
876 END IF;
877 END IF;
878
879 /* Launching Post-Process Custom Hook ODI Scenario */
880 scenario_name := 'POSTPROCESSHOOKPKG';
881 scenario_version := '001';
882 scenario_param := 'E1TOAPSPROJECT.PVV_POST_PROCESS_VAR=';
883 scenario_param := scenario_param
887
884 ||l_instance_code
885 || ':'
886 || MSC_E1APS_UTIL.COL_PTP_DATA;
888 post_process_odi :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
889
890 IF post_process_odi = FALSE THEN
891 /* Executing Mail Scenario */
892 scenario_name := 'MAIL';
893 scenario_version := '001';
894 scenario_param := '';
895 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
896 RETCODE := MSC_UTIL.G_ERROR;
897 RETURN;
898 ELSE
899 /* Executing Mail Scenario */
900 scenario_name := 'MAIL';
901 scenario_version := '001';
902 scenario_param := '';
903 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
904 END IF;
905 /* Launching Collect PTP Post-Proces Custom Hook*/
906 MSC_E1APS_HOOK.COL_PTP_DATA_POST_PROCESS(ERRBUF,RETCODE);
907
908 IF RETCODE = MSC_UTIL.G_ERROR THEN
909 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error Message:' || ERRBUF);
910 RETCODE := MSC_UTIL.G_ERROR;
911 RETURN;
912 END IF;
913
914 END DEM_PTP;
915 /* Publish Progrmas */
916 /*Publish Planiing Results*/
917 PROCEDURE PUB_PPR(ERRBUF OUT NOCOPY VARCHAR2
918 ,RETCODE OUT NOCOPY VARCHAR2
919 ,p_instance_id IN NUMBER
920 ,p_plan_id IN VARCHAR2
921 ,p_purchase_plan IN NUMBER
922 ,p_deployment_plan IN NUMBER
923 ,p_detailed_production_plan IN NUMBER ) AS
924
925 /* Variables To Launch the ODI */
926 scenario_name VARCHAR2(200);
927 scenario_version VARCHAR2(100);
928 scenario_param VARCHAR2(100);
929 ReturnStr VARCHAR2(2000);
930 ret_value1 BOOLEAN ;
931 ret_value2 BOOLEAN ;
932 ret_value3 BOOLEAN ;
933 ret_value4 BOOLEAN ;
934 odi_url VARCHAR2(300);
935 ErrMessage VARCHAR2(1900);
936 STARTINDEX INTEGER;
937 l_instance_code VARCHAR2(3);
938 pre_process_odi BOOLEAN;
939 post_process_odi BOOLEAN;
940 BEGIN
941
942 /* Launching Publish Planning Results Pre-Proces Custom Hook*/
943 MSC_E1APS_HOOK.PUB_PLAN_RES_PRE_PROCESS(ERRBUF,RETCODE);
944
945 IF RETCODE = MSC_UTIL.G_ERROR THEN
946 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error Message:' || ERRBUF);
947 RETCODE := MSC_UTIL.G_ERROR;
948 RETURN;
949 END IF;
950
951 /* Checking ODI Profile*/
952 odi_url := fnd_profile.value('MSC_E1APS_ODIURL');
953
954 IF odi_url IS NOT NULL THEN
955 /* Launching Pre-Process Custom Hook ODI Scenario */
956 select instance_code into l_instance_code from msc_apps_instances
957 where instance_id = p_instance_id;
958
959 scenario_name := 'PREPROCESSHOOKPKG';
960 scenario_version := '001';
961 scenario_param := 'E1TOAPSPROJECT.PVV_PRE_PROCESS_VAR=';
962 scenario_param := scenario_param
963 ||l_instance_code
964 || ':'
965 || MSC_E1APS_UTIL.PUB_PLAN_RES ;
966 pre_process_odi :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
967
968 IF pre_process_odi = FALSE THEN
969 /* Executing Mail Scenario */
970 scenario_name := 'MAIL';
971 scenario_version := '001';
972 scenario_param := '';
973 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
974 RETCODE := MSC_UTIL.G_ERROR;
975 RETURN;
976 END IF;
977 END IF;
978
979 --Adding plan_id to scenario parameter
980 scenario_version := '001';
981 scenario_param := 'E1TOAPSPROJECT.PVV_PLAN_ID=';
982 scenario_param := scenario_param
983 || p_plan_id;
984
985 /* Checking ODI Profile*/
986 odi_url := fnd_profile.value('MSC_E1APS_ODIURL');
987 /* Invoke ODISenario*/
988 IF odi_url IS NOT NULL THEN
989 BEGIN
990 SELECT MSC_E1APS_UTIL.MSC_E1APS_ODIInitialize(odi_url,2)
991 INTO ReturnStr
992 FROM dual;
993
994 EXCEPTION
995 WHEN OTHERS THEN
996 select instr(ReturnStr,'#') into StartIndex from dual;
997 select substr(ReturnStr,StartIndex+1,1800) into ErrMessage from dual;
998
999 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Intialization failed. Error message' || ErrMessage);
1000 RETCODE := MSC_UTIL.G_ERROR;
1001 RETURN;
1002 END;
1003 select instr(ReturnStr,'#') into StartIndex from dual;
1004 select substr(ReturnStr,StartIndex+1,1800) into ErrMessage from dual;
1008 RETCODE := MSC_UTIL.G_ERROR;
1005
1006 IF(length(ErrMessage) > 0) THEN
1007 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Intialization failed. Error message' || ErrMessage);
1009 RETURN;
1010 END IF;
1011 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Initializion is successful');
1012
1013 END IF;
1014
1015 /* Executing PurchasePlan ODI Scenarios */
1016 IF p_purchase_plan = MSC_UTIL.SYS_YES THEN
1017 scenario_name:='LOADAPSPURCHASEPLANDATATOE1PKG';
1018 ret_value1 :=CALL_ODIEXE(scenario_name ,scenario_version ,scenario_param ,odi_url);
1019 END IF;
1020
1021 IF p_deployment_plan = MSC_UTIL.SYS_YES THEN
1022 scenario_name:='LOADAPSDEPLOYMENTPLANDATATOE1PKG';
1023 ret_value2 :=CALL_ODIEXE(scenario_name ,scenario_version ,scenario_param ,odi_url);
1024 END IF;
1025 IF p_detailed_production_plan = MSC_UTIL.SYS_YES THEN
1026 scenario_name:='LOADAPSDETAILEDPRODPLANDATATOE1PKG';
1027 ret_value3 :=CALL_ODIEXE(scenario_name ,scenario_version ,scenario_param ,odi_url);
1028 END IF;
1029
1030 IF ret_value3 = false OR ret_value2 = false OR ret_value1 = false THEN
1031 scenario_name :='MAIL';
1032 scenario_param:='';
1033 ret_value4 :=CALL_ODIEXE(scenario_name ,scenario_version ,scenario_param ,odi_url);
1034 RETCODE := MSC_UTIL.G_ERROR;
1035 RETURN;
1036 END IF;
1037
1038 IF ret_value3 OR ret_value2 OR ret_value1 THEN
1039 /* Launching Post-Process Custom Hook ODI Scenario */
1040 scenario_name := 'POSTPROCESSHOOKPKG';
1041 scenario_version := '001';
1042 scenario_param := 'E1TOAPSPROJECT.PVV_POST_PROCESS_VAR=';
1043 scenario_param := scenario_param
1044 ||l_instance_code
1045 || ':'
1046 || MSC_E1APS_UTIL.PUB_PLAN_RES ;
1047 post_process_odi :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
1048
1049 IF post_process_odi = FALSE THEN
1050 /* Executing Mail Scenario */
1051 scenario_name := 'MAIL';
1052 scenario_version := '001';
1053 scenario_param := '';
1054 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
1055 RETCODE := MSC_UTIL.G_ERROR;
1056 RETURN;
1057 ELSE
1058 /* Executing Mail Scenario */
1059 scenario_name := 'MAIL';
1060 scenario_version := '001';
1061 scenario_param := '';
1062 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
1063 END IF;
1064 ELSE
1065 scenario_name :='MAIL';
1066 scenario_param:='';
1067 ret_value4 :=CALL_ODIEXE(scenario_name ,scenario_version ,scenario_param ,odi_url);
1068 RETCODE := MSC_UTIL.G_ERROR;
1069 RETURN;
1070 END IF;
1071
1072 /* Launching Publish Planning Results Post-Proces Custom Hook*/
1073 MSC_E1APS_HOOK.PUB_PLAN_RES_POST_PROCESS(ERRBUF,RETCODE);
1074
1075 IF RETCODE = MSC_UTIL.G_ERROR THEN
1076 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error Message:' || ERRBUF);
1077 RETCODE := MSC_UTIL.G_ERROR;
1078 RETURN;
1079 END IF;
1080
1081 END PUB_PPR;
1082 /* Procedure for Publish Forecast Source Systems */
1083 PROCEDURE DEM_PUB_FSS(ERRBUF OUT NOCOPY VARCHAR2,
1084 RETCODE OUT NOCOPY VARCHAR2,
1085 p_instance_id IN NUMBER ) AS
1086 /* Variables To Launch the ODI */
1087 l_wf_lookup_code VARCHAR2(200);
1088 scenario_name VARCHAR2(200);
1089 scenario_param VARCHAR2(100);
1090 scenario_version VARCHAR2(100);
1091 l_user_id NUMBER;
1092 ret_value NUMBER;
1093 ret_value1 BOOLEAN;
1094 odi_url VARCHAR2(1000);
1095 l_instance_code VARCHAR2(3);
1096 pre_process_odi BOOLEAN;
1097 post_process_odi BOOLEAN;
1098 g_demantra_schema VARCHAR2(50):=null;
1099 g_data_profile_id number :=null;
1100 g_filter_id number :=null;
1101 g_member_id number :=null;
1102 x_sql varchar2(500) :=null;
1103 x_org_sql varchar2(500) :=null;
1104 TYPE REF_CURSOR_TYPE IS REF CURSOR;
1105 c_org REF_CURSOR_TYPE;
1106 ErrMessage VARCHAR2(1900);
1107 ReturnStr VARCHAR2(2000);
1108 STARTINDEX INTEGER;
1109
1110
1111 BEGIN
1112
1113 /* Launching Publish Forecast Pre-Proces Custom Hook*/
1114 MSC_E1APS_HOOK.PUB_FCST_PRE_PROCESS(ERRBUF,RETCODE);
1115
1116 IF RETCODE = MSC_UTIL.G_ERROR THEN
1117 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error Message:' || ERRBUF);
1118 RETCODE := MSC_UTIL.G_ERROR;
1119 RETURN;
1120 END IF;
1121
1122 /* Checking ODI Profile*/
1126 /* Launching Pre-Process Custom Hook ODI Scenario */
1123 odi_url := fnd_profile.value('MSC_E1APS_ODIURL');
1124
1125 IF odi_url IS NOT NULL THEN
1127 select instance_code into l_instance_code from msc_apps_instances
1128 where instance_id = p_instance_id;
1129
1130 scenario_name := 'PREPROCESSHOOKPKG';
1131 scenario_version := '001';
1132 scenario_param := 'E1TOAPSPROJECT.PVV_PRE_PROCESS_VAR=';
1133 scenario_param := scenario_param
1134 ||l_instance_code
1135 || ':'
1136 || MSC_E1APS_UTIL.PUB_FCST ;
1137 pre_process_odi :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
1138
1139 IF pre_process_odi = FALSE THEN
1140 /* Executing Mail Scenario */
1141 scenario_name := 'MAIL';
1142 scenario_version := '001';
1143 scenario_param := '';
1144 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
1145 RETCODE := MSC_UTIL.G_ERROR;
1146 RETURN;
1147 END IF;
1148 END IF;
1149 g_demantra_schema:=MSD_DEM_DEMANTRA_UTILITIES.GET_DEMANTRA_SCHEMA;
1150 x_org_sql := 'select t_ep_organization_ep_id from ' || g_demantra_schema
1151 ||'.t_ep_organization where organization IN ( select organization_code from msc_trading_partners '
1152 ||'where sr_instance_id = ' || p_instance_id|| ' and partner_type=3) order by t_ep_organization_ep_id' ;
1153
1154 x_sql := 'select id from '|| g_demantra_schema ||'.TRANSFER_QUERY where query_name like ''AIA-Forecast data''';
1155 execute immediate x_sql into g_data_profile_id ;
1156
1157 x_sql := 'select filter_id from '|| g_demantra_schema ||'.transfer_query_filters where id = ' || g_data_profile_id ;
1158 execute immediate x_sql into g_filter_id ;
1159
1160 x_sql := 'Delete from ' || g_demantra_schema ||'.transfer_query_filter_m where filter_id = ' || g_filter_id ;
1161 execute immediate x_sql ;
1162 commit;
1163
1164 open c_org for x_org_sql;
1165 loop
1166 FETCH c_org INTO g_member_id;
1167 exit when c_org%notfound;
1168 x_sql := 'INSERT INTO ' || g_demantra_schema ||'.transfer_query_filter_m VALUES('||g_filter_id ||','||g_member_id || ')';
1169 execute immediate x_sql ;
1170 end loop;
1171 close c_org;
1172 commit;
1173
1174 x_sql := 'Begin ' || g_demantra_schema || '.API_NOTIFY_APS_INTEGRATION(' ||g_data_profile_id|| '); End; ';
1175 execute immediate x_sql ;
1176 commit;
1177
1178 /* Bug#8224935 - APP ID */
1179 l_user_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID', 'COMP_DM', 1, 'user_id'));
1180 l_wf_lookup_code := 'WF_AIA_FORECAST_EXPORT';
1181 scenario_name := 'LOADDMFORECASTDATATOE1PKG';
1182
1183 --Bug8740081
1184 /*ODI Initializing */
1185 /* Checking ODI Profile*/
1186 odi_url := fnd_profile.value('MSC_E1APS_ODIURL');
1187
1188 /* Invoke ODISenario */
1189 IF odi_url IS NOT NULL THEN
1190 BEGIN
1191 SELECT MSC_E1APS_UTIL.MSC_E1APS_ODIInitialize(odi_url,2)
1192 INTO ReturnStr
1193 FROM dual;
1194
1195 EXCEPTION
1196 WHEN OTHERS THEN
1197 select instr(ReturnStr,'#') into StartIndex from dual;
1198 select substr(ReturnStr,StartIndex+1,1800) into ErrMessage from dual;
1199
1200 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Intialization failed. Error message' || ErrMessage);
1201 RETCODE := MSC_UTIL.G_ERROR;
1202 RETURN ;
1203 END;
1204
1205 IF(length(ErrMessage) > 0) THEN
1206 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Intialization failed. Error message' || ErrMessage);
1207 RETCODE := MSC_UTIL.G_ERROR;
1208 RETURN;
1209 END IF;
1210 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Initializion is successful');
1211
1212 END IF;
1213
1214 /* Launching Demantra Workflow using function PUBLISH_DEM_WORKFLOW*/
1215 ret_value:=MSC_E1APS_UTIL.PUBLISH_DEM_WORKFLOW(ERRBUF, RETCODE, p_instance_id, l_wf_lookup_code , scenario_name,l_user_id );
1216 IF ret_value = MSC_E1APS_UTIL.DEM_SUCCESS THEN
1217 /* Launching Post-Process Custom Hook ODI Scenario */
1218 scenario_name := 'POSTPROCESSHOOKPKG';
1219 scenario_version := '001';
1220 scenario_param := 'E1TOAPSPROJECT.PVV_POST_PROCESS_VAR=';
1221 scenario_param := scenario_param
1222 ||l_instance_code
1223 || ':'
1224 || MSC_E1APS_UTIL.PUB_PLAN_RES ;
1225 post_process_odi :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
1226
1230 scenario_version := '001';
1227 IF post_process_odi = FALSE THEN
1228 /* Executing Mail Scenario */
1229 scenario_name := 'MAIL';
1231 scenario_param := '';
1232 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
1233 RETCODE := MSC_UTIL.G_ERROR;
1234 RETURN;
1235 ELSE
1236 /* Executing Mail Scenario */
1237 scenario_name := 'MAIL';
1238 scenario_version := '001';
1239 scenario_param := '';
1240 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
1241 END IF;
1242 ELSIF ret_value = MSC_E1APS_UTIL.DEM_FAILURE THEN
1243 scenario_name :='MAIL';
1244 scenario_param:='';
1245 ret_value1 :=CALL_ODIEXE(scenario_name ,scenario_version ,scenario_param ,odi_url);
1246 RETCODE := MSC_UTIL.G_ERROR;
1247 RETURN;
1248
1249 ELSIF ret_value = MSC_E1APS_UTIL.DEM_WARNING THEN
1250 scenario_name :='MAIL';
1251 scenario_param:='';
1252 ret_value1 :=CALL_ODIEXE(scenario_name ,scenario_version ,scenario_param ,odi_url);
1253 RETCODE := MSC_UTIL.G_WARNING;
1254 RETURN;
1255 END IF;
1256
1257 /* Launching Publish Forecast Post-Proces Custom Hook*/
1258 MSC_E1APS_HOOK.PUB_FCST_POST_PROCESS(ERRBUF,RETCODE);
1259
1260 IF RETCODE = MSC_UTIL.G_ERROR THEN
1261 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error Message:' || ERRBUF);
1262 RETCODE := MSC_UTIL.G_ERROR;
1263 RETURN;
1264 END IF;
1265 END DEM_PUB_FSS;
1266
1267 /*Procedure for Publish PTP*/
1268 PROCEDURE DEM_PUB_PTP(ERRBUF OUT NOCOPY VARCHAR2 ,
1269 RETCODE OUT NOCOPY VARCHAR2 ,
1270 p_instance_id IN NUMBER ) AS
1271 /* Variables To Launch the ODI */
1272 l_user_id NUMBER;
1273 fc_ret_value BOOLEAN;
1274 ret_value BOOLEAN;
1275 ret_value1 BOOLEAN;
1276 ret_value2 Number;
1277 l_wf_lookup_code VARCHAR2(200);
1278 odi_url VARCHAR2(1000);
1279 fc_url VARCHAR2(1000);
1280 ReturnStr VARCHAR2(2000);
1281 scenario_name VARCHAR2(200);
1282 scenario_version VARCHAR2(100):='001';
1283 scenario_param VARCHAR2(100);
1284 ErrMessage VARCHAR2(1900);
1285 STARTINDEX INTEGER;
1286 l_instance_code VARCHAR2(3);
1287 pre_process_odi BOOLEAN;
1288 post_process_odi BOOLEAN;
1289
1290
1291 BEGIN
1292 /* Launching Publish PTP Pre-Proces Custom Hook*/
1293 MSC_E1APS_HOOK.PUB_PTP_RES_PRE_PROCESS(ERRBUF,RETCODE);
1294
1295 IF RETCODE = MSC_UTIL.G_ERROR THEN
1296 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error Message:' || ERRBUF);
1297 RETCODE := MSC_UTIL.G_ERROR;
1298 RETURN;
1299 END IF;
1300
1301 /* Checking ODI Profile*/
1302 odi_url := fnd_profile.value('MSC_E1APS_ODIURL');
1303 IF odi_url IS NOT NULL THEN
1304 /* Launching Pre-Process Custom Hook ODI Scenario */
1305 select instance_code into l_instance_code
1306 from msc_apps_instances
1307 where instance_id = p_instance_id;
1308
1309 scenario_param := 'E1TOAPSPROJECT.PVV_PRE_PROCESS_VAR=';
1310 scenario_param := scenario_param
1311 ||l_instance_code
1312 || ':'
1313 || MSC_E1APS_UTIL.PUB_PTP_RES;
1314
1315 pre_process_odi :=CALL_ODIEXE('PREPROCESSHOOKPKG',
1316 scenario_version,
1317 scenario_param,
1318 odi_url);
1319
1320 IF pre_process_odi = FALSE THEN
1321 /* Executing Mail Scenario */
1322 scenario_version := '001';
1323 ret_value1 :=CALL_ODIEXE('MAIL',scenario_version,'', odi_url);
1324 RETCODE := MSC_UTIL.G_ERROR;
1325 RETURN ;
1326 END IF;
1327 END IF;
1328
1329 /* Bug#8224935 - APP ID */
1330 l_user_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID', 'COMP_PTP', 1, 'user_id'));
1331 l_wf_lookup_code := 'WF_AIA_PTP_E1_UPLD_PROM_PRIC';
1332 scenario_name := 'LOADDMPROMOTIONPRICINGDATATOE1PKG';
1333
1334 --Bug8740081
1335 /* ODI Initialization */
1336
1337 /* Checking ODI Profile*/
1338 odi_url := fnd_profile.value('MSC_E1APS_ODIURL');
1339 /* Invoke ODISenario*/
1340 IF odi_url IS NOT NULL THEN
1341 BEGIN
1342 SELECT MSC_E1APS_UTIL.MSC_E1APS_ODIInitialize(odi_url,2)
1343 INTO ReturnStr
1344 FROM dual;
1345
1346 EXCEPTION
1347 WHEN OTHERS THEN
1351 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Intialization failed. Error message' || ErrMessage);
1348 select instr(ReturnStr,'#') into StartIndex from dual;
1349 select substr(ReturnStr,StartIndex+1,1800) into ErrMessage from dual;
1350
1352 RETCODE := MSC_UTIL.G_ERROR;
1353 RETURN;
1354 END;
1355 IF(length(ErrMessage) > 0) THEN
1356 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Intialization failed. Error message' || ErrMessage);
1357 RETCODE := MSC_UTIL.G_ERROR;
1358 RETURN;
1359 END IF;
1360 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Initializion is successful');
1361
1362 END IF;
1363
1364 /* Launching Demantra Workflow using function PUBLISH_DEM_WORKFLOW*/
1365 ret_value2:= MSC_E1APS_UTIL.PUBLISH_DEM_WORKFLOW(ERRBUF ,RETCODE ,p_instance_id,l_wf_lookup_code ,scenario_name,l_user_id );
1366
1367
1368 IF ret_value2 = MSC_E1APS_UTIL.DEM_FAILURE THEN
1369 scenario_name := 'MAIL';
1370 scenario_version := '001';
1371 scenario_param := '';
1372 ret_value1 :=CALL_ODIEXE(scenario_name ,scenario_version ,scenario_param ,odi_url);
1373 RETCODE := MSC_UTIL.G_ERROR;
1374 RETURN;
1375 END IF;
1376
1377 IF ret_value2 = MSC_E1APS_UTIL.DEM_WARNING THEN
1378 scenario_name := 'MAIL';
1379 scenario_version := '001';
1380 scenario_param := '';
1381 ret_value1 :=CALL_ODIEXE(scenario_name ,scenario_version ,scenario_param ,odi_url);
1382 RETCODE := MSC_UTIL.G_WARNING;
1383 RETURN;
1384 END IF;
1385
1386
1387 /* Copies the file from Demantra Server To E1_file_DS
1388 fc_url := fnd_profile.value('MSC_E1APS_FCURL');
1389 fc_ret_value:=FALSE;
1390 IF fc_url IS NOT NULL THEN
1391 scenario_version := '001';
1392 scenario_param := '';
1393 fc_ret_value :=CALL_ODIEXE('EXPORTFILESFROMDEMANTRASERVER' ,scenario_version ,scenario_param ,fc_url);
1394
1395 IF fc_ret_value = FALSE THEN
1396 scenario_name := 'MAIL';
1397 scenario_version := '001';
1398 scenario_param := '';
1399 ret_value1 :=CALL_ODIEXE(scenario_name ,scenario_version ,scenario_param ,odi_url);
1400 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'File copy failed.' );
1401 RETCODE := MSC_UTIL.G_ERROR;
1402 RETURN;
1403 END IF;
1404 END IF; */
1405 ret_value := FALSE;
1406 scenario_name := 'LOADDMDELETEPROMOPRICINGDATATOE1PKG';
1407 IF ret_value2 = MSC_E1APS_UTIL.DEM_SUCCESS THEN
1408 ret_value:=CALL_ODIEXE(scenario_name ,'001','',odi_url);
1409 END IF;
1410
1411 IF ret_value THEN
1412 /* Launching Post-Process Custom Hook ODI Scenario */
1413
1414 scenario_name := 'POSTPROCESSHOOKPKG';
1415 scenario_version := '001';
1416 scenario_param := 'E1TOAPSPROJECT.PVV_POST_PROCESS_VAR=';
1417 scenario_param := scenario_param
1418 ||l_instance_code
1419 || ':'
1420 || MSC_E1APS_UTIL.PUB_PTP_RES;
1421
1422 post_process_odi :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
1423
1424 IF post_process_odi = FALSE THEN
1425 /* Executing Mail Scenario */
1426 scenario_name := 'MAIL';
1427 scenario_version := '001';
1428 scenario_param := '';
1429 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
1430 RETCODE := MSC_UTIL.G_ERROR;
1431 RETURN;
1432 ELSE
1433 /* Executing Mail Scenario */
1434 scenario_name := 'MAIL';
1435 scenario_version := '001';
1436 scenario_param := '';
1437 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
1438 END IF;
1439 ELSE
1440 scenario_name := 'MAIL';
1441 scenario_param := '';
1442 scenario_version := '001';
1443 ret_value1 :=CALL_ODIEXE(scenario_name ,scenario_version,scenario_param ,odi_url);
1444 RETCODE := MSC_UTIL.G_ERROR;
1445 RETURN;
1446 END IF;
1447
1448 /* Launching Publish PTP Post-Proces Custom Hook*/
1449 MSC_E1APS_HOOK.PUB_PTP_RES_PRE_PROCESS(ERRBUF,RETCODE);
1450
1451 IF RETCODE = MSC_UTIL.G_ERROR THEN
1452 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error Message:' || ERRBUF);
1453 RETCODE := MSC_UTIL.G_ERROR;
1454 RETURN;
1455 END IF;
1456
1457 END DEM_PUB_PTP;
1458 /*Procedure for Publish DSM*/
1459 PROCEDURE DEM_PUB_DSM(ERRBUF OUT NOCOPY VARCHAR2 ,
1463 p_pb_dedu_dispos IN NUMBER) AS
1460 RETCODE OUT NOCOPY VARCHAR2 ,
1461 p_instance_id IN NUMBER ,
1462 p_pb_claims IN NUMBER ,
1464 /* Variables To Launch the ODI */
1465 l_user_id NUMBER;
1466 l_wf_lookup_code VARCHAR2(200);
1467 ret_value Number;
1468 ret_value1 Boolean;
1469 odi_url VARCHAR2(1000);
1470 scenario_name VARCHAR2(200);
1471 scenario_version VARCHAR2(100):='001';
1472 scenario_param VARCHAR2(100);
1473 l_instance_code VARCHAR2(3);
1474 pre_process_odi BOOLEAN;
1475 post_process_odi BOOLEAN;
1476 ErrMessage VARCHAR2(1900);
1477 ReturnStr VARCHAR2(2000);
1478 STARTINDEX INTEGER;
1479
1480 BEGIN
1481
1482 /* Launching Publish DSM Pre-Proces Custom Hook*/
1483 MSC_E1APS_HOOK.PUB_DSM_RES_PRE_PROCESS(ERRBUF,RETCODE);
1484
1485 IF RETCODE = MSC_UTIL.G_ERROR THEN
1486 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error Message:' || ERRBUF);
1487 RETCODE := MSC_UTIL.G_ERROR;
1488 RETURN;
1489 END IF;
1490
1491 /* Checking ODI Profile*/
1492 odi_url := fnd_profile.value('MSC_E1APS_ODIURL');
1493 IF odi_url IS NOT NULL THEN
1494 /* Updating ODI Pre-Process custom hook scenario_param */
1495 select instance_code into l_instance_code
1496 from msc_apps_instances
1497 where instance_id = p_instance_id;
1498
1499 scenario_param := 'E1TOAPSPROJECT.PVV_PRE_PROCESS_VAR=';
1500 scenario_param := scenario_param
1501 ||l_instance_code
1502 || ':'
1503 || MSC_E1APS_UTIL.PUB_DSM_RES;
1504 pre_process_odi :=CALL_ODIEXE('PREPROCESSHOOKPKG',
1505 scenario_version,
1506 scenario_param,
1507 odi_url);
1508
1509 IF pre_process_odi = FALSE THEN
1510 /* Executing Mail Scenario */
1511 scenario_version := '001';
1512 ret_value1 :=CALL_ODIEXE('MAIL',scenario_version,'', odi_url);
1513 RETCODE := MSC_UTIL.G_ERROR;
1514 RETURN ;
1515 END IF;
1516 END IF;
1517
1518 --Bug8740081
1519 --Bug8740081
1520 /*ODI Initialization*/
1521 /* Checking ODI Profile*/
1522 odi_url := fnd_profile.value('MSC_E1APS_ODIURL');
1523 /* Invoke ODISenario*/
1524 IF odi_url IS NOT NULL THEN
1525 BEGIN
1526 SELECT MSC_E1APS_UTIL.MSC_E1APS_ODIInitialize(odi_url,2)
1527 INTO ReturnStr
1528 FROM dual;
1529
1530 EXCEPTION
1531 WHEN OTHERS THEN
1532 select instr(ReturnStr,'#') into StartIndex from dual;
1533 select substr(ReturnStr,StartIndex+1,1800) into ErrMessage from dual;
1534
1535 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Intialization failed. Error message' || ErrMessage);
1536 RETCODE := MSC_UTIL.G_ERROR;
1537 RETURN;
1538 END;
1539 IF(length(ErrMessage) > 0) THEN
1540 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Intialization failed. Error message' || ErrMessage);
1541 RETCODE := MSC_UTIL.G_ERROR;
1542 RETURN;
1543 END IF;
1544 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Initializion is successful');
1545
1546 END IF;
1547
1548 /* Bug#8224935 - APP ID */
1549 l_user_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID', 'COMP_PTP', 1, 'user_id'));
1550 IF p_pb_claims = MSC_UTIL.SYS_YES THEN
1551 l_wf_lookup_code := 'WF_AIA_DSM_E1_CLAIM_EXP';
1552 scenario_name := 'LOADDMCLAIMDATATOE1PKG';
1553 ret_value:=MSC_E1APS_UTIL.PUBLISH_DEM_WORKFLOW(ERRBUF ,RETCODE ,p_instance_id ,l_wf_lookup_code ,scenario_name,l_user_id);
1554
1555 IF ret_value = MSC_E1APS_UTIL.DEM_FAILURE THEN
1556 RETCODE := MSC_UTIL.G_ERROR;
1557 RETURN;
1558 END IF;
1559
1560 IF ret_value = MSC_E1APS_UTIL.DEM_WARNING THEN
1561 RETCODE := MSC_UTIL.G_WARNING;
1562 RETURN;
1563 END IF;
1564 END IF;
1565
1566 IF p_pb_dedu_dispos = MSC_UTIL.SYS_YES THEN
1567 l_wf_lookup_code := 'WF_AIA_DSM_E1_DEDUCT_EXP';
1568 scenario_name := 'LOADDMDEDDISPOSITIONSDATATOE1PKG';
1569 ret_value:=MSC_E1APS_UTIL.PUBLISH_DEM_WORKFLOW(ERRBUF ,RETCODE ,p_instance_id ,l_wf_lookup_code ,scenario_name ,l_user_id);
1570
1571 IF ret_value = MSC_E1APS_UTIL.DEM_FAILURE THEN
1572 scenario_name := 'MAIL';
1573 scenario_version := '001';
1574 scenario_param := '';
1578 END IF;
1575 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
1576 RETCODE := MSC_UTIL.G_ERROR;
1577 RETURN;
1579
1580 IF ret_value = MSC_E1APS_UTIL.DEM_WARNING THEN
1581 scenario_name := 'MAIL';
1582 scenario_version := '001';
1583 scenario_param := '';
1584 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
1585 RETCODE := MSC_UTIL.G_WARNING;
1586 RETURN;
1587 END IF;
1588 END IF;
1589
1590 IF ret_value = MSC_E1APS_UTIL.DEM_SUCCESS THEN
1591 /* Launching Post-Process Custom Hook ODI Scenario */
1592 scenario_name := 'POSTPROCESSHOOKPKG';
1593 scenario_version := '001';
1594 scenario_param := 'E1TOAPSPROJECT.PVV_POST_PROCESS_VAR=';
1595 scenario_param := scenario_param
1596 ||l_instance_code
1597 || ':'
1598 || MSC_E1APS_UTIL.PUB_PLAN_RES ;
1599 post_process_odi :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
1600
1601 IF post_process_odi = FALSE THEN
1602 /* Executing Mail Scenario */
1603 scenario_name := 'MAIL';
1604 scenario_version := '001';
1605 scenario_param := '';
1606 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
1607 RETCODE := MSC_UTIL.G_ERROR;
1608 RETURN;
1609 ELSE
1610 /* Executing Mail Scenario */
1611 scenario_name := 'MAIL';
1612 scenario_version := '001';
1613 scenario_param := '';
1614 ret_value1 :=CALL_ODIEXE(scenario_name, scenario_version, scenario_param, odi_url);
1615 END IF;
1616 ELSE
1617 scenario_name :='MAIL';
1618 scenario_param:='';
1619 ret_value1 :=CALL_ODIEXE(scenario_name ,scenario_version ,scenario_param ,odi_url);
1620 RETCODE := MSC_UTIL.G_ERROR;
1621 RETURN;
1622 END IF;
1623
1624 /* Launching Publish DSM Post-Proces Custom Hook*/
1625 MSC_E1APS_HOOK.PUB_DSM_RES_PRE_PROCESS(ERRBUF,RETCODE);
1626
1627 IF RETCODE = MSC_UTIL.G_ERROR THEN
1628 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error Message:' || ERRBUF);
1629 RETCODE := MSC_UTIL.G_ERROR;
1630 RETURN;
1631 END IF;
1632
1633 END DEM_PUB_DSM;
1634 END MSC_E1APS_DEMCL;