[Home] [Help]
PACKAGE BODY: APPS.EAM_JOBCLOSE_PRIV
Source
1 PACKAGE BODY EAM_JOBCLOSE_PRIV AS
2 /* $Header: EAMJCLPB.pls 120.7 2011/06/04 20:38:26 somitra ship $ */
3
4
5 PROCEDURE RAISE_WORKFLOW_STATUS_CHANGED
6 (p_wip_entity_id IN NUMBER,
7 p_wip_entity_name IN VARCHAR2,
8 p_organization_id IN NUMBER,
9 p_new_status IN NUMBER,
10 p_old_system_status IN NUMBER,
11 p_old_wo_status IN NUMBER,
12 p_workflow_type IN NUMBER,
13 x_return_status OUT NOCOPY VARCHAR2
14 )
15 IS
16 l_status_changed_event VARCHAR2(240);
17 l_event_name VARCHAR2(240);
18 l_parameter_list wf_parameter_list_t;
19 l_event_key VARCHAR2(200);
20 l_wf_event_seq NUMBER;
21 l_estimation_status NUMBER;
22 l_cost_estimate NUMBER;
23 l_return_status VARCHAR2(1);
24 l_err_text VARCHAR2(2000);
25 l_msg_count NUMBER;
26
27 BEGIN
28
29 l_status_changed_event := 'oracle.apps.eam.workorder.status.changed';
30
31 IF (WF_EVENT.TEST(l_status_changed_event) <> 'NONE' --if status change event enabled
32 ) THEN
33 SELECT EAM_WORKFLOW_EVENT_S.NEXTVAL
34 INTO l_wf_event_seq
35 FROM DUAL;
36
37 l_parameter_list := wf_parameter_list_t();
38 l_event_name := l_status_changed_event;
39
40 l_event_key := TO_CHAR(l_wf_event_seq);
41 WF_CORE.CONTEXT('Enterprise Asset Management...','Work Order Status change event','Building parameter list');
42 -- Add Parameters
43 Wf_Event.AddParameterToList(p_name =>'WIP_ENTITY_ID',
44 p_value => TO_CHAR(p_wip_entity_id),
45 p_parameterlist => l_parameter_list);
46 Wf_Event.AddParameterToList(p_name =>'WIP_ENTITY_NAME',
47 p_value =>p_wip_entity_name,
48 p_parameterlist => l_parameter_list);
49 Wf_Event.AddParameterToList(p_name =>'ORGANIZATION_ID',
50 p_value => TO_CHAR(p_organization_id),
51 p_parameterlist => l_parameter_list);
52 Wf_Event.AddParameterToList(p_name =>'NEW_SYSTEM_STATUS',
53 p_value => TO_CHAR(p_new_status),
54 p_parameterlist => l_parameter_list);
55 Wf_Event.AddParameterToList(p_name =>'NEW_WO_STATUS',
56 p_value => TO_CHAR(p_new_status),
57 p_parameterlist => l_parameter_list);
58 Wf_Event.AddParameterToList(p_name =>'OLD_SYSTEM_STATUS',
59 p_value => TO_CHAR(p_old_system_status),
60 p_parameterlist => l_parameter_list);
61 Wf_Event.AddParameterToList(p_name =>'OLD_WO_STATUS',
62 p_value => TO_CHAR(p_old_wo_status),
63 p_parameterlist => l_parameter_list);
64 Wf_Event.AddParameterToList(p_name =>'WORKFLOW_TYPE',
65 p_value => TO_CHAR(p_workflow_type),
66 p_parameterlist => l_parameter_list);
67 Wf_Event.AddParameterToList(p_name =>'REQUESTOR',
68 p_value =>FND_GLOBAL.USER_NAME ,
69 p_parameterlist => l_parameter_list);
70 Wf_Core.Context('Enterprise Asset Management...','Work Order Staus Changed Event','Raising event');
71
72 Wf_Event.Raise( p_event_name => l_event_name,
73 p_event_key => l_event_key,
74 p_parameters => l_parameter_list);
75 l_parameter_list.DELETE;
76 WF_CORE.CONTEXT('Enterprise Asset Management..','Work Order Status Changed Event','After raising event');
77 END IF; --end of check for status change event
78
79
80 END RAISE_WORKFLOW_STATUS_CHANGED;
81
82
83 /**************************************************************************
84 * PROCEDURE TO WAIT FOR CONC. PROGRAM.
85 * IT WILL RETURN ONLY AFTER THE CONC. PROGRAM COMPLETES
86 /**************************************************************************/
87
88 PROCEDURE WAIT_CONC_PROGRAM(p_request_id in number,
89 errbuf out NOCOPY varchar2,
90 retcode out NOCOPY number) is
91 l_call_status boolean;
92 l_phase varchar2(80);
93 l_status varchar2(80);
94 l_dev_phase varchar2(80);
95 l_dev_status varchar2(80);
96 l_message varchar2(240);
97
98 l_counter number := 0;
99 BEGIN
100 LOOP
101 l_call_status:= FND_CONCURRENT.WAIT_FOR_REQUEST
102 ( p_request_id,
103 10,
104 -1,
105 l_phase,
106 l_status,
107 l_dev_phase,
108 l_dev_status,
109 l_message);
110 exit when l_call_status=false;
111
112 if (l_dev_phase='COMPLETE') then
113 if (l_dev_status = 'NORMAL') then
114 retcode := -1;
115 elsif (l_dev_status = 'WARNING') then
116 retcode := 1;
117 else
118 retcode := 2;
119 end if;
120 errbuf := l_message;
121 return;
122 end if;
123
124 l_counter := l_counter + 1;
125 exit when l_counter >= 2;
126
127 end loop;
128
129 retcode := 2;
130 return ;
131 END WAIT_CONC_PROGRAM;
132
133
134
135 /* Wrapper function which will be called by the concurrent manager */
136 procedure EAM_CLOSE_MGR
137 (
138 ERRBUF OUT NOCOPY VARCHAR2 ,
139 RETCODE OUT NOCOPY VARCHAR2 ,
140 p_organization_id IN NUMBER ,
141 p_class_type IN VARCHAR2 ,
142 p_from_class IN VARCHAR2 ,
143 p_to_class IN VARCHAR2 ,
144 p_from_job IN VARCHAR2 ,
145 p_to_job IN VARCHAR2 ,
146 p_from_release_date IN VARCHAR2 ,
147 p_to_release_date IN VARCHAR2 ,
148 p_from_start_date IN VARCHAR2 ,
149 p_to_start_date IN VARCHAR2 ,
150 p_from_completion_date IN VARCHAR2 ,
151 p_to_completion_date IN VARCHAR2 ,
152 p_status IN VARCHAR2 ,
153 p_group_id IN NUMBER ,
154 p_select_jobs IN NUMBER ,
155 p_exclude_reserved_jobs IN VARCHAR2 ,
156 p_uncompleted_jobs IN VARCHAR2,
157 p_exclude_pending_txn_jobs IN VARCHAR2 ,
158 p_report_type IN VARCHAR2 ,
159 p_act_close_date IN VARCHAR2
160 )
161 IS
162 l_returnstatus VARCHAR2(1) ;
163 l_msg_count NUMBER;
164 l_msg_data VARCHAR2(200);
165 l_warning NUMBER;
166 l_req_id NUMBER;
167 l_user_id NUMBER;
168 l_resp_id NUMBER;
169 l_resp_appl_id NUMBER;
170
171 TYPE WORKORDER_REC IS RECORD
172 (wip_entity_id NUMBER,
173 organization_id NUMBER,
174 wip_entity_name VARCHAR2(240),
175 old_system_status NUMBER,
176 old_wo_status NUMBER,
177 workflow_type NUMBER
178 );
179
180 TYPE WORKORDER_TAB is TABLE OF workorder_rec
181 INDEX BY BINARY_INTEGER;
182 l_workorder_tbl WORKORDER_TAB;
183 l_wo_count NUMBER;
184 l_new_status_type NUMBER;
185 l_return_status VARCHAR2(1);
186
187 CURSOR workorders
188 IS
189 SELECT wdj.wip_entity_id, we.wip_entity_name,
190 wdj.status_type,wdj.organization_id,ewod.user_defined_status_id,ewod.workflow_type
191 FROM wip_discrete_jobs wdj, wip_dj_close_temp wdct,eam_work_order_details ewod,wip_entities we
192 WHERE wdct.group_id = p_group_id
193 and wdct.wip_entity_id = wdj.wip_entity_id
194 and wdct.organization_id = wdj.organization_id
195 and wdj.wip_entity_id = ewod.wip_entity_id
196 and wdj.organization_id = ewod.organization_id
197 and wdj.wip_entity_id = we.wip_entity_id
198 and wdj.organization_id = we.organization_id;
199
200 BEGIN
201
202 RETCODE := 0 ; -- success
203 ----Commenting code as this is now being called from WIP. See bug 6718091
204 /* l_wo_count := 0;
205 FOR l_workorders_rec IN workorders LOOP
206 l_wo_count := l_wo_count +1;
207 l_workorder_tbl(l_wo_count).wip_entity_id := l_workorders_rec.wip_entity_id;
208 l_workorder_tbl(l_wo_count).wip_entity_name := l_workorders_rec.wip_entity_name;
209 l_workorder_tbl(l_wo_count).organization_id := l_workorders_rec.organization_id;
210 l_workorder_tbl(l_wo_count).old_system_status := l_workorders_rec.status_type;
211 l_workorder_tbl(l_wo_count).old_wo_status := l_workorders_rec.user_defined_status_id;
212 l_workorder_tbl(l_wo_count).workflow_type := l_workorders_rec.workflow_type;
213
214 RAISE_WORKFLOW_STATUS_CHANGED
215 (p_wip_entity_id => l_workorder_tbl(l_wo_count).wip_entity_id,
216 p_wip_entity_name => l_workorder_tbl(l_wo_count).wip_entity_name,
217 p_organization_id => l_workorder_tbl(l_wo_count).organization_id,
218 p_new_status => 14,
219 p_old_system_status => l_workorder_tbl(l_wo_count).old_system_status,
220 p_old_wo_status => l_workorder_tbl(l_wo_count).old_wo_status,
221 p_workflow_type => l_workorder_tbl(l_wo_count).workflow_type,
222 x_return_status => l_return_status
223 );
224
225 IF (NVL(l_return_status,'S') <> 'S') THEN
226 RETCODE := 2;
227 errbuf := SQLERRM;
228 RETURN;
229 END IF;
230
231 EAM_TEXT_UTIL.PROCESS_WO_EVENT
232 (
233 p_event => 'UPDATE',
234 p_wip_entity_id =>l_workorder_tbl(l_wo_count).wip_entity_id,
235 p_organization_id =>l_workorder_tbl(l_wo_count).organization_id,
236 p_last_update_date => SYSDATE,
237 p_last_updated_by => FND_GLOBAL.user_id,
238 p_last_update_login =>FND_GLOBAL.login_id
239 );
240
241
242 END LOOP;
243
244 COMMIT;*/
245
246 --Launch concurrent program to close work orders
247
248 l_user_id := fnd_global.user_id;
249 l_resp_id := fnd_global.resp_id;
250 l_resp_appl_id := fnd_global.resp_appl_id;
251
252 IF (l_user_id IS NOT NULL AND l_resp_id IS NOT NULL) THEN
253 FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id,l_resp_appl_id,0);
254 END IF;
255
256 fnd_file.put_line(FND_FILE.LOG,'Before invoking WIP conc. program');
257
258 l_req_id := fnd_request.submit_request('WIP', 'WICDCL', NULL,
259 NULL,
260 FALSE,
261 p_organization_id,p_class_type,p_from_class,p_to_class,p_from_job,p_to_job,p_from_release_date,
262 p_to_release_date,p_from_start_date,p_to_start_date,p_from_completion_date,p_to_completion_date,
263 p_status,to_char(p_group_id),to_char(p_select_jobs),p_exclude_reserved_jobs,p_uncompleted_jobs,
264 p_exclude_pending_txn_jobs,p_report_type,p_act_close_date,
265 chr(0),'','','','','','','','','',
266 '','','','','','','','','','',
267 '','','','','','','','','','',
268 '','','','','','','','','','',
269 '','','','','','','','','','',
270 '','','','','','','','','','',
271 '','','','','','','','','','',
272 '','','','','','','','','','');
273
274
275 COMMIT ;
276
277 fnd_file.put_line(FND_FILE.LOG,'After invoking WIP conc. program..request_id.'||l_req_id);
278
279 IF (l_req_id = 0) THEN
280 RETCODE := 2;
281 errbuf := SQLERRM;
282 RETURN;
283 END IF;
284
285 WAIT_CONC_PROGRAM(l_req_id,ERRBUF,RETCODE);
286
287 FND_FILE.PUT_LINE(FND_FILE.LOG,'WIP Close concurrent program : '||retcode);
288
289 ----Commenting code as this is now being called from WIP. See bug 6718091
290
291 /* FOR i IN l_workorder_tbl.FIRST..l_workorder_tbl.LAST LOOP
292 BEGIN
293 SELECT status_type
294 INTO l_new_status_type
295 FROM WIP_DISCRETE_JOBS
296 WHERE wip_entity_id = l_workorder_tbl(i).wip_entity_id
297 AND organization_id = l_workorder_tbl(i).organization_id;
298
299 IF(l_new_status_type <> 14) THEN
300
301 UPDATE EAM_WORK_ORDER_DETAILS
302 SET USER_DEFINED_STATUS_ID = l_new_status_type,
303 last_update_date = SYSDATE,
307 AND organization_id = l_workorder_tbl(i).organization_id;
304 last_updated_by = FND_GLOBAL.user_id,
305 last_update_login = FND_GLOBAL.login_id
306 WHERE wip_entity_id = l_workorder_tbl(i).wip_entity_id
308
309
310
311 COMMIT;
312
313
314 RAISE_WORKFLOW_STATUS_CHANGED
315 (p_wip_entity_id => l_workorder_tbl(i).wip_entity_id,
316 p_wip_entity_name => l_workorder_tbl(i).wip_entity_name,
317 p_organization_id => l_workorder_tbl(i).organization_id,
318 p_new_status => l_new_status_type,
319 p_old_system_status => 14,
320 p_old_wo_status => 14,
321 p_workflow_type => l_workorder_tbl(i).workflow_type,
322 x_return_status => l_return_status
323 );
324
325 IF (NVL(l_return_status,'S') <> 'S') THEN
326 FND_FILE.PUT_LINE(FND_FILE.LOG,'Raising of workflow has failed with an error');
327 END IF;
328
329 EAM_TEXT_UTIL.PROCESS_WO_EVENT
330 (
331 p_event => 'UPDATE',
332 p_wip_entity_id =>l_workorder_tbl(i).wip_entity_id,
333 p_organization_id =>l_workorder_tbl(i).organization_id,
334 p_last_update_date => SYSDATE,
335 p_last_updated_by => FND_GLOBAL.user_id,
336 p_last_update_login =>FND_GLOBAL.login_id
337 );
338 END IF; --end of check for status changed
339
340 EXCEPTION
341 WHEN NO_DATA_FOUND THEN
342 NULL;
343 END;
344 END LOOP;
345
346 COMMIT;*/
347
348 if (RETCODE <> -1 ) then
349 FND_FILE.PUT_LINE(FND_FILE.LOG,'Wip close concurrent program has errored or has a warning');
350 errbuf := fnd_message.get;
351 RETURN;
352 ELSE
353 retcode:= 0;
354 end if;
355
356
357 EXCEPTION
358 WHEN others THEN
359 retcode := 2; -- error
360 errbuf := SQLERRM;
361
362 END EAM_CLOSE_MGR ;
363
364
365
366 PROCEDURE EAM_CLOSE_WO
367 (
368 p_submission_date IN DATE,
369 p_organization_id IN NUMBER,
370 p_group_id IN NUMBER,
371 p_select_jobs IN NUMBER,
372 p_report_type IN VARCHAR2,
373 x_request_id OUT NOCOPY NUMBER
374 )
375 IS
376 BEGIN
377
378 x_request_id := fnd_request.submit_request('EAM', 'EAMCDCL', '',
379 to_char(p_submission_date, 'YYYY/MM/DD HH24:MI'),
380 FALSE,
381 to_char(p_organization_id),'','','','','','','','','',
382 '','','',to_char(p_group_id),to_char(p_select_jobs),'','','',p_report_type,'',
383 chr(0),'','','','','','','','','',
384 '','','','','','','','','','',
385 '','','','','','','','','','',
386 '','','','','','','','','','',
387 '','','','','','','','','','',
388 '','','','','','','','','','',
389 '','','','','','','','','','',
390 '','','','','','','','','','');
391
392 END EAM_CLOSE_WO;
393
394 END EAM_JOBCLOSE_PRIV ;