[Home] [Help]
PACKAGE BODY: APPS.EAM_JOBCLOSE_PRIV
Source
1 PACKAGE BODY EAM_JOBCLOSE_PRIV AS
2 /* $Header: EAMJCLPB.pls 120.2.12010000.2 2008/11/06 23:48:26 mashah 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
170 TYPE WORKORDER_REC IS RECORD
171 (wip_entity_id NUMBER,
172 organization_id NUMBER,
173 wip_entity_name VARCHAR2(240),
174 old_system_status NUMBER,
175 old_wo_status NUMBER,
176 workflow_type NUMBER
177 );
178
179 TYPE WORKORDER_TAB is TABLE OF workorder_rec
180 INDEX BY BINARY_INTEGER;
181 l_workorder_tbl WORKORDER_TAB;
182 l_wo_count NUMBER;
183 l_new_status_type NUMBER;
184 l_return_status VARCHAR2(1);
185
186 CURSOR workorders
187 IS
188 SELECT wdj.wip_entity_id, we.wip_entity_name,
189 wdj.status_type,wdj.organization_id,ewod.user_defined_status_id,ewod.workflow_type
190 FROM wip_discrete_jobs wdj, wip_dj_close_temp wdct,eam_work_order_details ewod,wip_entities we
191 WHERE wdct.group_id = p_group_id
192 and wdct.wip_entity_id = wdj.wip_entity_id
193 and wdct.organization_id = wdj.organization_id
194 and wdj.wip_entity_id = ewod.wip_entity_id
195 and wdj.organization_id = ewod.organization_id
196 and wdj.wip_entity_id = we.wip_entity_id
197 and wdj.organization_id = we.organization_id;
198
199 BEGIN
200
201 RETCODE := 0 ; -- success
202 ----Commenting code as this is now being called from WIP. See bug 6718091
203 /* l_wo_count := 0;
204 FOR l_workorders_rec IN workorders LOOP
205 l_wo_count := l_wo_count +1;
206 l_workorder_tbl(l_wo_count).wip_entity_id := l_workorders_rec.wip_entity_id;
207 l_workorder_tbl(l_wo_count).wip_entity_name := l_workorders_rec.wip_entity_name;
208 l_workorder_tbl(l_wo_count).organization_id := l_workorders_rec.organization_id;
209 l_workorder_tbl(l_wo_count).old_system_status := l_workorders_rec.status_type;
210 l_workorder_tbl(l_wo_count).old_wo_status := l_workorders_rec.user_defined_status_id;
211 l_workorder_tbl(l_wo_count).workflow_type := l_workorders_rec.workflow_type;
212
213 RAISE_WORKFLOW_STATUS_CHANGED
214 (p_wip_entity_id => l_workorder_tbl(l_wo_count).wip_entity_id,
215 p_wip_entity_name => l_workorder_tbl(l_wo_count).wip_entity_name,
216 p_organization_id => l_workorder_tbl(l_wo_count).organization_id,
217 p_new_status => 14,
218 p_old_system_status => l_workorder_tbl(l_wo_count).old_system_status,
219 p_old_wo_status => l_workorder_tbl(l_wo_count).old_wo_status,
220 p_workflow_type => l_workorder_tbl(l_wo_count).workflow_type,
221 x_return_status => l_return_status
222 );
223
224 IF (NVL(l_return_status,'S') <> 'S') THEN
225 RETCODE := 2;
226 errbuf := SQLERRM;
227 RETURN;
228 END IF;
229
230 EAM_TEXT_UTIL.PROCESS_WO_EVENT
231 (
232 p_event => 'UPDATE',
233 p_wip_entity_id =>l_workorder_tbl(l_wo_count).wip_entity_id,
234 p_organization_id =>l_workorder_tbl(l_wo_count).organization_id,
235 p_last_update_date => SYSDATE,
236 p_last_updated_by => FND_GLOBAL.user_id,
237 p_last_update_login =>FND_GLOBAL.login_id
238 );
239
240
241 END LOOP;
242
243 COMMIT;*/
244
245 --Launch concurrent program to close work orders
246
247 l_user_id := fnd_global.user_id;
248 l_resp_id := fnd_global.resp_id;
249
250 IF (l_user_id IS NOT NULL AND l_resp_id IS NOT NULL) THEN
251 FND_GLOBAL.APPS_INITIALIZE(l_user_id, l_resp_id,426,0);
252 END IF;
253
254 fnd_file.put_line(FND_FILE.LOG,'Before invoking WIP conc. program');
255
256 l_req_id := fnd_request.submit_request('WIP', 'WICDCL', NULL,
257 NULL,
258 FALSE,
259 p_organization_id,'','','','','','','','','',
260 '','','',to_char(p_group_id),'2','','','',p_report_type,'',
261 chr(0),'','','','','','','','','',
262 '','','','','','','','','','',
263 '','','','','','','','','','',
264 '','','','','','','','','','',
265 '','','','','','','','','','',
266 '','','','','','','','','','',
267 '','','','','','','','','','',
268 '','','','','','','','','','');
269
270
271 COMMIT ;
272
273 IF (l_req_id = 0) THEN
274 RETCODE := 2;
275 errbuf := SQLERRM;
276 RETURN;
277 END IF;
278
279 WAIT_CONC_PROGRAM(l_req_id,ERRBUF,RETCODE);
280
281 FND_FILE.PUT_LINE(FND_FILE.LOG,'WIP Close concurrent program : '||retcode);
282
283 ----Commenting code as this is now being called from WIP. See bug 6718091
284
285 /* FOR i IN l_workorder_tbl.FIRST..l_workorder_tbl.LAST LOOP
286 BEGIN
287 SELECT status_type
288 INTO l_new_status_type
289 FROM WIP_DISCRETE_JOBS
290 WHERE wip_entity_id = l_workorder_tbl(i).wip_entity_id
291 AND organization_id = l_workorder_tbl(i).organization_id;
292
293 IF(l_new_status_type <> 14) THEN
294
295 UPDATE EAM_WORK_ORDER_DETAILS
296 SET USER_DEFINED_STATUS_ID = l_new_status_type,
297 last_update_date = SYSDATE,
298 last_updated_by = FND_GLOBAL.user_id,
299 last_update_login = FND_GLOBAL.login_id
300 WHERE wip_entity_id = l_workorder_tbl(i).wip_entity_id
301 AND organization_id = l_workorder_tbl(i).organization_id;
302
303
304
305 COMMIT;
306
307
308 RAISE_WORKFLOW_STATUS_CHANGED
309 (p_wip_entity_id => l_workorder_tbl(i).wip_entity_id,
310 p_wip_entity_name => l_workorder_tbl(i).wip_entity_name,
311 p_organization_id => l_workorder_tbl(i).organization_id,
312 p_new_status => l_new_status_type,
313 p_old_system_status => 14,
314 p_old_wo_status => 14,
315 p_workflow_type => l_workorder_tbl(i).workflow_type,
316 x_return_status => l_return_status
317 );
318
319 IF (NVL(l_return_status,'S') <> 'S') THEN
320 FND_FILE.PUT_LINE(FND_FILE.LOG,'Raising of workflow has failed with an error');
321 END IF;
322
323 EAM_TEXT_UTIL.PROCESS_WO_EVENT
324 (
325 p_event => 'UPDATE',
326 p_wip_entity_id =>l_workorder_tbl(i).wip_entity_id,
327 p_organization_id =>l_workorder_tbl(i).organization_id,
328 p_last_update_date => SYSDATE,
329 p_last_updated_by => FND_GLOBAL.user_id,
330 p_last_update_login =>FND_GLOBAL.login_id
331 );
332 END IF; --end of check for status changed
333
334 EXCEPTION
335 WHEN NO_DATA_FOUND THEN
336 NULL;
337 END;
338 END LOOP;
339
340 COMMIT;*/
341
342 if (RETCODE <> -1 ) then
343 FND_FILE.PUT_LINE(FND_FILE.LOG,'Wip close concurrent program has errored or has a warning');
344 errbuf := fnd_message.get;
345 RETURN;
346 ELSE
347 retcode:= 0;
348 end if;
349
350
351 EXCEPTION
352 WHEN others THEN
353 retcode := 2; -- error
354 errbuf := SQLERRM;
355
356 END EAM_CLOSE_MGR ;
357
358
359
360 PROCEDURE EAM_CLOSE_WO
361 (
362 p_submission_date IN DATE,
363 p_organization_id IN NUMBER,
364 p_group_id IN NUMBER,
365 p_select_jobs IN NUMBER,
366 p_report_type IN VARCHAR2,
367 x_request_id OUT NOCOPY NUMBER
368 )
369 IS
370 BEGIN
371
372 x_request_id := fnd_request.submit_request('EAM', 'EAMCDCL', '',
373 to_char(p_submission_date, 'YYYY/MM/DD HH24:MI'),
374 FALSE,
375 to_char(p_organization_id),'','','','','','','','','',
376 '','','',to_char(p_group_id),to_char(p_select_jobs),'','','',p_report_type,'',
377 chr(0),'','','','','','','','','',
378 '','','','','','','','','','',
379 '','','','','','','','','','',
380 '','','','','','','','','','',
381 '','','','','','','','','','',
382 '','','','','','','','','','',
383 '','','','','','','','','','',
384 '','','','','','','','','','');
385
386 END EAM_CLOSE_WO;
387
388 END EAM_JOBCLOSE_PRIV ;