[Home] [Help]
PACKAGE BODY: APPS.MSC_E1APS_DSMCOLL
Source
1 PACKAGE BODY MSC_E1APS_DSMCOLL AS --body
2 --# $Header: MSCE1DSB.pls 120.1 2010/05/14 10:31:12 nyellank noship $
3 /* Global variables */
4
5 ReturnStr varchar2(5000);
6 SessionNum varchar2(10);
7 ErrMessage varchar2(1900);
8 ErrLength integer;
9 StartIndex integer;
10 EndIndex integer;
11 WSURL varchar2(1000);
12 source_file varchar2(200);
13 destination_file varchar2(200);
14 fc_url varchar2(1000);
15 l_wf_lookup_code varchar2(1000);
16 errbuf VARCHAR2(1000);
17 retcode NUMBER;
18 process_id VARCHAR2(10);
19
20
21
22 FUNCTION MSC_E1APS_ODIExecute(scrName IN VARCHAR2,WSURL IN VARCHAR2)
23 RETURN BOOLEAN
24 AS
25
26 BEGIN
27
28 IF WSURL IS NOT NULL THEN
29 begin
30 /* Execute ODI scenarios */
31 select MSC_E1APS_UTIL.MSC_E1APS_ODIScenarioExecute(scrName,'001','',WSURL) into ReturnStr from dual;
32 EXCEPTION
33 WHEN OTHERS THEN
34 select instr(ReturnStr,'#') into StartIndex from dual;
35 select substr(ReturnStr,StartIndex+1,1800) into ErrMessage from dual;
36 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Scenario' || scrName|| ' execution failed.' || ErrMessage);
37 RETURN FALSE;
38 end;
39
40 select instr(ReturnStr,'#') into StartIndex from dual;
41 select substr(ReturnStr,0,StartIndex-1) into SessionNum from dual;
42 select substr(ReturnStr,StartIndex+1,1800) into ErrMessage from dual;
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
53 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Scenario ' || scrName || ' execution is successful.');
54 RETURN TRUE;
55 END IF;
56
57 RETURN TRUE;
58
59 END; --Procedure MSC_E1APS_DSMExecute
60
61
62 PROCEDURE MSC_DSM_COLLECTIONS(ERRBUF OUT NOCOPY VARCHAR2,
63 RETCODE OUT NOCOPY VARCHAR2,
64 parInstanceID IN VARCHAR2,
65 parLoadPayCnf IN NUMBER,
66 parLoadDed in NUMBER)
67 AS
68 ODILaunchFlag BOOLEAN;
69 mailstat BOOLEAN;
70 ret_value BOOLEAN;
71 l_user_id number;
72 fc_ret_value BOOLEAN;
73 scenario_name VARCHAR2(200);
74 scenario_version VARCHAR2(100);
75 scenario_param VARCHAR2(200);
76 l_instance_code VARCHAR2(3);
77 pre_process_odi BOOLEAN;
78 post_process_odi BOOLEAN;
79 ret_value1 BOOLEAN;
80
81 BEGIN
82
83 /* Launching Collect DSM Pre-Proces Custom Hook*/
84 MSC_E1APS_HOOK.COL_DSM_DATA_PRE_PROCESS(ERRBUF,RETCODE);
85 IF RETCODE = MSC_UTIL.G_ERROR THEN
86 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error Message:' || ERRBUF);
87 RETCODE := MSC_UTIL.G_ERROR;
88 RETURN;
89 END IF;
90
91 /* Checking ODI Profile*/
92 WSURL := fnd_profile.value('MSC_E1APS_ODIURL');
93 IF WSURL IS NOT NULL THEN
94 /* Launching Pre-Process Custom Hook ODI Scenario */
95 select instance_code into l_instance_code
96 from msc_apps_instances
97 where instance_id = parInstanceID;
98
99 scenario_name := 'PREPROCESSHOOKPKG';
100 scenario_version := '001';
101 scenario_param := 'E1TOAPSPROJECT.PVV_PRE_PROCESS_VAR=';
102 scenario_param := scenario_param
103 ||l_instance_code
104 || ':'
105 || MSC_E1APS_UTIL.COL_DSM_DATA;
106 pre_process_odi :=MSC_E1APS_DEMCL.CALL_ODIEXE(scenario_name, scenario_version, scenario_param, WSURL);
107
108 IF pre_process_odi = FALSE THEN
109 /* Executing Mail Scenario */
110 scenario_name := 'MAIL';
111 scenario_version := '001';
112 scenario_param := '';
113 ret_value1 :=MSC_E1APS_DEMCL.CALL_ODIEXE(scenario_name, scenario_version, scenario_param, WSURL);
114 RETCODE := MSC_UTIL.G_ERROR;
115 RETURN;
116 END IF;
117 END IF;
118
119 select ATTRIBUTE14,ATTRIBUTE13 into source_file,destination_file from MSC_APPS_INSTANCES where INSTANCE_ID = parInstanceID;
120
121 /* Bug#8224935 - APP ID */
122 l_user_id := to_number(msd_dem_common_utilities.get_app_id_text ('MSD_DEM_DEMANTRA_OBJECT_ID',
123 'COMP_PTP',
124 1,
125 'user_id'));
126 /*Initialize ODI*/
127 WSURL:= fnd_profile.value('MSC_E1APS_ODIURL');
128 IF WSURL IS NOT NULL THEN
129 BEGIN
130 MSC_UTIL.MSC_DEBUG('INITIALIZING ODI ....');
131 select MSC_E1APS_UTIL.MSC_E1APS_ODIInitialize(WSURL,2) into ReturnStr from dual;
132
133 EXCEPTION
134 WHEN OTHERS THEN
135 select instr(ReturnStr,'#') into StartIndex from dual;
136 select substr(ReturnStr,StartIndex+1,1800) into ErrMessage from dual;
137 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Intialization failed. Error message' || ErrMessage);
138 RETCODE := MSC_UTIL.G_ERROR;
139 RETURN;
140 END;
141 select instr(ReturnStr,'#') into StartIndex from dual;
142 select substr(ReturnStr,StartIndex+1,1800) into ErrMessage from dual;
143
144 IF(length(ErrMessage) > 0) THEN
145 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Intialization failed. Error message' || ErrMessage);
146 RETCODE := MSC_UTIL.G_ERROR;
147 return;
148 end if;
149
150 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'ODI Initializion is successful');
151
152
153 END IF;
154
155 IF parLoadPayCnf = MSC_UTIL.SYS_YES THEN
156
157 ODILaunchFlag := MSC_E1APS_ODIExecute('LOADE1APCONFIRMDATATODMPKG',WSURL);
158
159 fc_url := fnd_profile.value('MSC_E1APS_FCURL');
160 fc_ret_value:=TRUE;
161 if fc_url is not null and ODILaunchFlag then
162 fc_ret_value := MSC_E1APS_ODIExecute('IMPORTFILESTODEMANTRASERVER',fc_url);
163 if fc_ret_value = FALSE then
164 /* Launch Mail */
165 mailstat := MSC_E1APS_ODIExecute('MAIL',WSURL);
166
167 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'File copy failed.' );
168 RETCODE := MSC_UTIL.G_ERROR;
169 RETURN;
170 end if;
171 else
172 /* Launch Mail */
173 mailstat := MSC_E1APS_ODIExecute('MAIL',WSURL);
174 fc_ret_value:=ODILaunchFlag;
175
176 IF ODILaunchFlag = FALSE THEN
177 RETCODE := MSC_UTIL.G_ERROR;
178 RETURN;
179 END IF;
180
181 end if;
182
183 IF fc_ret_value THEN
184 /* Calling DEM WorkFlow*/
185 l_wf_lookup_code := 'WF_AIA_E1_PTP_APCONFIRM_IMPORT';
186 retcode := 0;
187
188 /* Lauch Demantra Work Flow */
189 MSC_E1APS_UTIL.DEM_WORKFLOW(errbuf, retcode, l_wf_lookup_code,process_id,l_user_id);
190
191 if retcode = -1 or process_id= -1 then
192 msd_dem_common_utilities.log_message('DEM WORKFLOW NOT LAUNCHED');
193 RETCODE := MSC_UTIL.G_ERROR;
194 RETURN;
195 else
196 msd_dem_common_utilities.log_message('DEM WORKFLOW LAUNCHED. Process ID: ' || process_id );
197 end if;
198 END IF;
199
200 END IF;
201
202 IF parLoadDed = MSC_UTIL.SYS_YES THEN
203
204 ODILaunchFlag := MSC_E1APS_ODIExecute('LOADE1DEDUCTIONSDATATODMPKG',WSURL);
205
206 fc_url := fnd_profile.value('MSC_E1APS_FCURL');
207 fc_ret_value:=TRUE;
208 if fc_url is not null and ODILaunchFlag then
209 fc_ret_value := MSC_E1APS_ODIExecute('IMPORTFILESTODEMANTRASERVER',fc_url);
210 if fc_ret_value = FALSE then
211 /* Launch Mail */
212 mailstat := MSC_E1APS_ODIExecute('MAIL',WSURL);
213
214 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'File copy failed.' );
215 RETCODE := MSC_UTIL.G_ERROR;
216 RETURN;
217 end if;
218 else
219 /* Launch Mail */
220 mailstat := MSC_E1APS_ODIExecute('MAIL',WSURL);
221 fc_ret_value:=ODILaunchFlag;
222
223 IF ODILaunchFlag = FALSE THEN
224 RETCODE := MSC_UTIL.G_ERROR;
225 -- RETURN;
226 END IF;
227
228 end if;
229
230 /* Launch Mail */
231 mailstat := MSC_E1APS_ODIExecute('MAIL',WSURL);
232 IF fc_ret_value THEN
233 /* Calling DEM WorkFlow*/
234 l_wf_lookup_code := 'WF_AIA_E1_DSM_NEWDEDUCT_DWNLD';
235 retcode := 0;
236
237 /* Lauch Demantra Work Flow */
238 MSC_E1APS_UTIL.DEM_WORKFLOW(errbuf, retcode, l_wf_lookup_code,process_id,l_user_id);
239
240 if retcode= -1 then
241 msd_dem_common_utilities.log_message('DEM WORKFLOW NOT LAUNCHED');
242 RETCODE := MSC_UTIL.G_ERROR;
243 RETURN;
244 else
245 msd_dem_common_utilities.log_message('DEM WORKFLOW LAUNCHED. Process ID: ' || process_id );
246 end if;
247
248 END IF;
249 END IF;
250
251 /* Launching Post-Process Custom Hook ODI Scenario */
252 select instance_code into l_instance_code
253 from msc_apps_instances
254 where instance_id = parInstanceID;
255
256 scenario_name := 'POSTPROCESSHOOKPKG';
257 scenario_version := '001';
258 scenario_param := 'E1TOAPSPROJECT.PVV_POST_PROCESS_VAR=';
259 scenario_param := scenario_param
260 ||l_instance_code
261 || ':'
262 || MSC_E1APS_UTIL.COL_DSM_DATA;
263
264 post_process_odi :=MSC_E1APS_DEMCL.CALL_ODIEXE(scenario_name, scenario_version, scenario_param, WSURL);
265
266 IF post_process_odi = FALSE THEN
267 /* Executing Mail Scenario */
268 scenario_name := 'MAIL';
269 scenario_version := '001';
270 scenario_param := '';
271 ret_value1 :=MSC_E1APS_DEMCL.CALL_ODIEXE(scenario_name, scenario_version, scenario_param, WSURL);
272 RETCODE := MSC_UTIL.G_ERROR;
273 RETURN;
274 ELSE
275 /* Executing Mail Scenario */
276 scenario_name := 'MAIL';
277 scenario_version := '001';
278 scenario_param := '';
279 ret_value1 :=MSC_E1APS_DEMCL.CALL_ODIEXE(scenario_name, scenario_version, scenario_param, WSURL);
280 END IF;
281
282 /* Launching Collect DSM Post-Proces Custom Hook*/
283 MSC_E1APS_HOOK.COL_DSM_DATA_PRE_PROCESS(ERRBUF,RETCODE);
284 IF RETCODE = MSC_UTIL.G_ERROR THEN
285 MSC_UTIL.LOG_MSG(MSC_UTIL.G_LVL_FATAL_ERR, 'Error Message:' || ERRBUF);
286 RETCODE := MSC_UTIL.G_ERROR;
287 RETURN;
288 END IF;
289
290 END; -- Procedure MSC_DSM_COLLECTIONS
291
292 END MSC_E1APS_DSMCOLL;
293