1 PACKAGE BODY eam_wo_processor AS
2 /* $Header: EAMWOPRB.pls 115.16 2002/11/20 22:30:12 aan ship $ */
3
4 G_DEBUG boolean := FALSE;
5
6 type jobs_t is record (
7 wip_entity_id number,
8 status_type number
9 );
10 type jobs_table is table of jobs_t index by binary_integer ;
11
12
13 -- Private Function
14 procedure wo_schedule(
15 errbuf out NOCOPY varchar2,
16 retcode out NOCOPY number,
17 p_wip_entity_id in number,
18 p_status_type in number,
19 p_org_id in number);
20
21
22 procedure multi_wo_schedule(
23 errbuf out NOCOPY varchar2,
24 retcode out NOCOPY number,
25 p_group_id in number,
26 p_org_id in number);
27
28 function validate(p_wip_entity_id in number,
29 p_org_id in number,
30 p_status_type in number) return boolean;
31 function finite_scheduler(p_wip_entity_id in number,
32 p_org_id in number) return number;
33 function change_status_type(p_wip_entity_id in number,
34 p_org_id in number,
35 p_status_type in number) return boolean;
36 procedure error_status(p_wip_entity_id in number,
37 p_org_id in number);
38 procedure wait_conc_program(p_request_id in number,
39 errbuf out NOCOPY varchar2,
40 retcode out NOCOPY number);
41 function populate_jobs(p_group_id number) return jobs_table;
42 function mass_load(p_group_id in number,
43 p_org_id in number) return number;
44
45
46
47 -- The processor handles single and multiple work order processing
48 -- 1. Single Work Order processing
49 -- Takes argument p_wip_entity_id,p_status_type,p_org_id
50 -- It calls WPS engine for finite scheduling and change the job status
51 -- from 'Pending Scheduling' to 'Unreleased','Released', or 'Hold'.
52 -- The status will be set to 'Not Ready' if the processing fails.
53 -- 2. Multiple Work Order processing.
54 -- Takes argument p_group_id, p_org_id
55 -- p_group_id is the group id to be used for Mass Load to takes
56 -- rows for WIP_JOB_SCHEDULE_INTERFACE for processing.
57 -- It calls Mass Load for scheduling and change the job status
58 -- from 'Pending Scheduling' to 'Unreleased','Released', or 'Hold'.
59 -- The status will be set to 'Not Ready' if the processing fails.
60
61 procedure schedule(
62 errbuf out NOCOPY varchar2,
63 retcode out NOCOPY number,
64 p_wip_entity_id in number,
65 p_status_type in number,
66 p_group_id in number,
67 p_org_id in number) is
68 begin
69 G_DEBUG := FND_PROFILE.VALUE('MRP_DEBUG') = 'Y';
70 if G_DEBUG then
71 FND_FILE.PUT_LINE(FND_FILE.LOG,'Setting debug to Y');
72 else
73 FND_FILE.PUT_LINE(FND_FILE.LOG,'Setting debug to N');
74 end if;
75
76 if (p_wip_entity_id <> -1 and p_status_type <> -1) then
77 wo_schedule(errbuf,retcode,p_wip_entity_id,p_status_type,p_org_id);
78 elsif (p_group_id <> -1) then
79 multi_wo_schedule(errbuf,retcode,p_group_id,p_org_id);
80 else
81 retcode := G_ERROR;
82 fnd_message.set_name('EAM','EAM_WO_PROC_VALIDATE');
83 errbuf := fnd_message.get;
84 end if;
85 end;
86
87
88 -- Procedure to process single work order
89 procedure wo_schedule(
90 errbuf out NOCOPY varchar2,
91 retcode out NOCOPY number,
92 p_wip_entity_id in number,
93 p_status_type in number,
94 p_org_id in number) is
95 l_request_id number;
96 begin
97 if G_DEBUG then
98 FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing for work order : '||p_wip_entity_id);
99 end if;
100
101 -- Validate the work order status
102 if (not validate(p_wip_entity_id,p_org_id,p_status_type)) then
103 retcode := G_ERROR;
104 fnd_message.set_name('EAM','EAM_WO_PROC_VALIDATE');
105 errbuf := fnd_message.get;
106 return;
107 end if;
108 if G_DEBUG then
109 FND_FILE.PUT_LINE(FND_FILE.LOG,'Completes Validation');
110 end if;
111
112 -- Call WPS engine
113 l_request_id := finite_scheduler(p_wip_entity_id,p_org_id);
114 if (l_request_id = 0) then
115 retcode := G_ERROR;
116 fnd_message.set_name('EAM','EAM_WO_PROC_FAIL_WPS');
117 errbuf := fnd_message.get;
118 return;
119 end if;
120 if G_DEBUG then
121 FND_FILE.PUT_LINE(FND_FILE.LOG,'Request ID for WPS : '||l_request_id);
122 FND_FILE.PUT_LINE(FND_FILE.LOG,'committing...');
123 end if;
124
125
126
127
128 commit;
129
130 if G_DEBUG then
131 FND_FILE.PUT_LINE(FND_FILE.LOG,'committed.');
132 end if;
133
134 -- Wait until the WPS conc. program finishes
135 wait_conc_program(l_request_id,errbuf,retcode);
136
137 if G_DEBUG then
138 FND_FILE.PUT_LINE(FND_FILE.LOG,'concurrent program finished.');
139 end if;
140
141 if (retcode = G_ERROR) then
142 if G_DEBUG then
143 FND_FILE.PUT_LINE(FND_FILE.LOG,'error.');
144 end if;
145 error_status(p_wip_entity_id,p_org_id);
146 errbuf := fnd_message.get;
147 commit;
148 if G_DEBUG then
149 FND_FILE.PUT_LINE(FND_FILE.LOG,'error, committed.');
150 end if;
151 return;
152 end if;
153 if G_DEBUG then
154 FND_FILE.PUT_LINE(FND_FILE.LOG,'WPS finish up successfully');
155 end if;
156
157 -- Changing the job status after scheduling
158 if (not change_status_type(p_wip_entity_id,p_org_id,p_status_type)) then
159 retcode := G_ERROR;
160 fnd_message.set_name('EAM','EAM_WO_PROC_FAIL_STATUS');
161 errbuf := fnd_message.get;
162 return;
163 end if;
164 if G_DEBUG then
165 FND_FILE.PUT_LINE(FND_FILE.LOG,'Processor finishes up successfully');
166 end if;
167
168 commit;
169 end;
170
171 -- Procedure to process multiple work order
172 procedure multi_wo_schedule(
173 errbuf out NOCOPY varchar2,
174 retcode out NOCOPY number,
175 p_group_id in number,
176 p_org_id in number) is
177 l_jobs jobs_table;
178 l_index number;
179 l_request_id number;
180 l_err_cnt number;
181 l_dummy boolean;
182 begin
183 if G_DEBUG then
184 FND_FILE.PUT_LINE(FND_FILE.LOG,'Processing for multi work order with group id : '||p_group_id);
185 end if;
186
187 -- Put the job in the table
188 l_jobs := populate_jobs(p_group_id);
189 if G_DEBUG then
190 FND_FILE.PUT_LINE(FND_FILE.LOG,'Number of work order to be processed : '||l_jobs.count);
191 end if;
192
193 if (l_jobs.count = 0) then
194 return;
195 end if;
196
197 -- Validate the job
198 l_index := l_jobs.first;
199 loop
200 if G_DEBUG then
201 FND_FILE.PUT_LINE(FND_FILE.LOG,'Validating work order : '||l_jobs(l_index).wip_entity_id);
202 end if;
203 if (not validate(l_jobs(l_index).wip_entity_id,p_org_id,l_jobs(l_index).status_type)) then
204 retcode := G_ERROR;
205 fnd_message.set_name('EAM','EAM_WO_PROC_VALIDATE');
206 errbuf := fnd_message.get;
207 return;
208 end if;
209 exit when l_index = l_jobs.last;
210 l_index := l_jobs.next(l_index);
211 end loop;
212 if G_DEBUG then
213 FND_FILE.PUT_LINE(FND_FILE.LOG,'Completes validating all work orders');
214 end if;
215
216
217 -- Call WIP mass Load
218 l_request_id := mass_load(p_group_id,p_org_id);
219 if (l_request_id = 0) then
220 retcode := G_ERROR;
221 fnd_message.set_name('EAM','EAM_WO_PROC_FAIL_MASS_LOAD');
222 errbuf := fnd_message.get;
223 return;
224 end if;
225 if G_DEBUG then
226 FND_FILE.PUT_LINE(FND_FILE.LOG,'Calling Mass Load with request id : '||l_request_id);
227 end if;
228
229 -- Wait until the WIP mass Load conc. program finishes
230 wait_conc_program(l_request_id,errbuf,retcode);
231 if G_DEBUG then
232 FND_FILE.PUT_LINE(FND_FILE.LOG,'Concurrent Program returns with status : '||retcode);
233 end if;
234 if (retcode = G_ERROR) then
235 if G_DEBUG then
236 FND_FILE.PUT_LINE(FND_FILE.LOG,'Mass Load completes with error');
237 end if;
238 errbuf := fnd_message.get;
239 return;
240 end if;
241
242 if (retcode = G_SUCCESS) then
243 if G_DEBUG then
244 FND_FILE.PUT_LINE(FND_FILE.LOG,'Mass Load completes successfully');
245 end if;
246 return;
247 end if;
248
249
250 if G_DEBUG then
251 FND_FILE.PUT_LINE(FND_FILE.LOG,'Mass Load completes with warning');
252 end if;
253
254 -- Setting the error
255 l_index := l_jobs.first;
256 loop
257 select count(*)
258 into l_err_cnt
259 from wip_job_schedule_interface
260 where wip_entity_id = l_jobs(l_index).wip_entity_id
261 and group_id = p_group_id
262 and process_status <> WIP_CONSTANTS.COMPLETED;
263
264 if (l_err_cnt <> 0) then
265 error_status(l_jobs(l_index).wip_entity_id,p_org_id);
266 if G_DEBUG then
267 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in work order '||l_jobs(l_index).wip_entity_id);
268 end if;
269 end if;
270 exit when l_index = l_jobs.last;
271 l_index := l_jobs.next(l_index);
272 end loop;
273
274 commit;
275 end;
276
277
278 -- Function to validate :
279 -- 1. The current job status is 'Pending Scheduling'
280 -- 2. The destination status is either
281 -- 'Unreleased', 'Released', or 'Hold'
282 function validate(p_wip_entity_id in number,
283 p_org_id in number,
284 p_status_type in number) return boolean is
285 l_cnt number;
286 begin
287
288 select count(*)
289 into l_cnt
290 from wip_discrete_jobs
291 where wip_entity_id = p_wip_entity_id
292 and organization_id = p_org_id
293 and status_type = WIP_CONSTANTS.PEND_SCHED;
294
295 if (l_cnt = 0) then
296 return false;
297 end if;
298
299
300 if (p_status_type not in (WIP_CONSTANTS.UNRELEASED,WIP_CONSTANTS.RELEASED, WIP_CONSTANTS.HOLD) ) then
301 return false;
302 end if;
303
304 return true;
305 end;
306
307 -- Function to change the work order status and
308 -- perform release or hold process.
309 function change_status_type(p_wip_entity_id in number,
310 p_org_id in number,
311 p_status_type in number) return boolean is
312 l_class_code varchar2(10);
313 l_routing_exists number;
314 begin
315
316 -- Change the status to the UNRELEASED
317 update wip_discrete_jobs
318 set status_type = WIP_CONSTANTS.UNRELEASED
319 where wip_entity_id = p_wip_entity_id
320 and organization_id = p_org_id;
321
322 if (p_status_type = WIP_CONSTANTS.RELEASED) then
323 select class_code
324 into l_class_code
325 from wip_discrete_jobs
326 where wip_entity_id = p_wip_entity_id
327 and organization_id = p_org_id;
328
329 wip_change_status.release(p_wip_entity_id,p_org_id,NULL,NULL,l_class_code,
330 WIP_CONSTANTS.PEND_SCHED,WIP_CONSTANTS.RELEASED,
331 l_routing_exists);
332 elsif (p_status_type = WIP_CONSTANTS.HOLD) then
333 wip_change_status.put_job_on_hold(p_wip_entity_id,p_org_id);
334 end if;
335
336 -- Change the status to the new intended status
337 update wip_discrete_jobs
338 set status_type = p_status_type
339 where wip_entity_id = p_wip_entity_id
340 and organization_id = p_org_id;
341
342 return true;
343 end;
344
345 -- Procedure to set the Work Order status to 'Not ready'
346 procedure error_status(p_wip_entity_id in number,
347 p_org_id in number) is
348 begin
349 update wip_discrete_jobs
350 set status_type = WIP_CONSTANTS.DRAFT,
351 date_released = NULL
352 where wip_entity_id = p_wip_entity_id
353 and organization_id = p_org_id;
354 -- commit;
355 end;
356
357 -- Function to invoke WPS finite scheduler.
358 function finite_scheduler(p_wip_entity_id in number,
359 p_org_id in number) return number is
360 l_request_id number := 0;
361 l_direction number := NULL;
362 l_wip_entity_id number := NULL;
363 l_req_start_date date := NULL;
364 l_req_due_date date := NULL;
365 l_horizon_start date := NULL;
369
366 l_material_constrained number := NULL;
367 l_horizon_length number := NULL;
368 l_use_finite_scheduler number := NULL;
370 begin
371 wps_common.GetParameters(P_Org_Id => p_org_id,
372 X_Use_Finite_Scheduler => l_use_finite_scheduler,
373 X_Material_Constrained => l_material_constrained,
374 X_Horizon_Length => l_horizon_length);
375
376 select requested_start_date,due_date
377 into l_req_start_date,l_req_due_date
378 from wip_discrete_jobs
379 where wip_entity_id = p_wip_entity_id
380 and organization_id = p_org_id;
381
382 if (l_req_start_date is not null) then
383 l_horizon_start := l_req_start_date;
384 l_direction := WIP_CONSTANTS.WPS_FORWARD_SCHEDULE;
385 else
386 l_horizon_start := sysdate;
387 l_direction := WIP_CONSTANTS.WPS_BACKWARD_SCHEDULE;
388 end if;
389
390 l_request_id := FND_REQUEST.SUBMIT_REQUEST('WPS',
391 'WPCWFS',
392 '',
393 '',
394 FALSE,
395 to_char(p_org_id),
396 '1', -- scheduling mode
397 to_char(NVL(p_wip_entity_id,-1)),
398 to_char(l_direction),
399 '-1', -- midpoint_op
400 Nvl(fnd_number.number_to_canonical(wip_datetimes.dt_to_float(l_req_start_date)),
401 '-1'),
402 Nvl(fnd_number.number_to_canonical(wip_datetimes.dt_to_float(l_req_due_date)),
403 '-1'),
404 to_char(Nvl(wip_datetimes.dt_to_float(l_horizon_start),-1)),
405 to_char(l_horizon_length),
406 '1', -- res_constrained
407 to_char(l_material_constrained),
408 '0','','','','','1','-1','-1','6',
409 chr(0),
410 '','','','','','','','','','','','','','','','',
411 '','','','','','','','','','','','','','','','','','','','',
412 '','','','','','','','','','','','','','','','','','','','',
413 '','','','','','','','','','','','','','','','','','','','',
414 '','','');
415
416 return l_request_id;
417 end;
418
419
420 -- Procedure to wait conc. program.
421 -- It will return only after the conc. program completes
422 procedure wait_conc_program(p_request_id in number,
423 errbuf out NOCOPY varchar2,
424 retcode out NOCOPY number) is
425 l_call_status boolean;
426 l_phase varchar2(80);
427 l_status varchar2(80);
428 l_dev_phase varchar2(80);
429 l_dev_status varchar2(80);
430 l_message varchar2(240);
431
432 l_counter number := 0;
433 begin
434 loop
435 l_call_status:= FND_CONCURRENT.WAIT_FOR_REQUEST
436 ( p_request_id,
437 10,
438 300,
439 l_phase,
440 l_status,
441 l_dev_phase,
442 l_dev_status,
443 l_message);
444 exit when l_call_status=false;
445
446 if (l_dev_phase='COMPLETE') then
447 if (l_dev_status = 'NORMAL') then
448 retcode := G_SUCCESS;
449 elsif (l_dev_status = 'WARNING') then
450 retcode := G_WARNING;
451 else
452 retcode := G_ERROR;
453 end if;
454 errbuf := l_message;
455 return;
456 end if;
457
458 l_counter := l_counter + 1;
459 exit when l_counter >= 2;
460
461 end loop;
462
463 retcode := G_ERROR;
464 return ;
465 end;
466
467 -- Fucntion to populate PL/SQL table from WIP_JOB_SCHEDULE_INTERFACE
468 function populate_jobs(p_group_id number) return jobs_table is
469 l_jobs jobs_table;
470 cursor job_cursor is
471 select wip_entity_id,status_type
472 from wip_job_schedule_interface
473 where group_id = p_group_id;
474 l_index number := 0;
475 begin
476 for job_cursor_record in job_cursor loop
477 l_index := l_index + 1;
478 l_jobs(l_index).wip_entity_id := job_cursor_record.wip_entity_id;
479 l_jobs(l_index).status_type := job_cursor_record.status_type;
480 end loop;
481
482 return l_jobs;
483 end;
484
485 -- Function to invoke Mass Load
486 function mass_load(p_group_id in number,
487 p_org_id in number) return number is
488 l_req_id number;
489 begin
490
491 l_req_id := fnd_request.submit_request(
492 'WIP', 'WICMLP', NULL, NULL, FALSE,
493 to_char(p_group_id), '0', '2');
494 commit;
495 return l_req_id;
496
497 end;
498
499 END eam_wo_processor;
[Home] [Help]