[Home] [Help]
PACKAGE BODY: APPS.EAM_DOWNTIME_PUB
Source
1 PACKAGE BODY EAM_Downtime_PUB as
2 /* $Header: EAMPEQDB.pls 120.2 2006/01/19 18:48:17 hkarmach noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_Downtime_PUB';
5 G_DEBUG VARCHAR2(1) := NVL(fnd_profile.value('EAM_DEBUG'), 'N');
6 g_reason_code varchar2(80):=null;
7
8
9 PROCEDURE WriteLog (p_api_version IN NUMBER,
10 p_msg_count IN NUMBER,
11 p_msg_data IN VARCHAR2,
12 x_return_status OUT NOCOPY VARCHAR2);
13
14 PROCEDURE Load_Downtime(
15 p_api_version IN NUMBER,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2,
19 p_downtime_group_id IN NUMBER,
20 p_org_id IN NUMBER,
21 p_simulation_set IN VARCHAR2,
22 p_include_unreleased IN NUMBER,
23 p_firm_order_only IN NUMBER,
24 p_department_id IN NUMBER,
25 p_resource_id IN NUMBER,
26 p_calendar_code IN VARCHAR2,
27 p_exception_set_id IN NUMBER,
28 p_user_id IN NUMBER,
29 p_request_id IN NUMBER,
30 p_prog_id IN NUMBER,
31 p_prog_app_id IN NUMBER,
32 p_login_id IN NUMBER);
33
34 PROCEDURE Purge_Downtime(
35 p_api_version IN NUMBER,
36 x_return_status OUT NOCOPY VARCHAR2,
37 x_msg_count OUT NOCOPY NUMBER,
38 x_msg_data OUT NOCOPY VARCHAR2,
39 p_org_id IN NUMBER,
40 p_simulation_set IN VARCHAR2);
41 --A global cursor that has one row for a downtime caused by wo or operations
42
43 cursor downtime_csr(p_org_id number,
44 p_department_id number,
45 p_resource_id number,
46 p_include_unreleased number, p_firm_order_only
47 number)
48 is
49
50 SELECT distinct wdj.organization_id maint_org_id, wdj.wip_entity_id wip_entity_id,
51 cii.inventory_item_id asset_group_id, cii.instance_number asset_number,
52 decode (wdj.shutdown_type, 2, to_number(NULL), 3, to_number(NULL), wo.operation_seq_num ) op_seq,
53 decode (wdj.shutdown_type, 2, wdj.scheduled_start_date, 3, wdj.scheduled_start_date, wo.first_unit_start_date ) from_date,
54 decode (wdj.shutdown_type, 2, wdj.scheduled_completion_date, 3, wdj.scheduled_completion_date, wo.last_unit_completion_date ) to_date,
55 decode (wdj.shutdown_type, 2, wdj.shutdown_type, 3, wdj.shutdown_type, wo.shutdown_type ) shutdown_type,
56 wdj.firm_planned_flag, msn.current_organization_id prod_org_id, msn.inventory_item_id equipment_item_id,
57 msn.serial_number eqp_serial_number, bdri.department_id, bre.resource_id, bre.instance_id,
58 1 as downtime_source_code
59 FROM wip_discrete_jobs wdj,
60 wip_entities we,
61 mtl_serial_numbers msn,
62 csi_item_instances cii,
63 mtl_parameters mp,
64 bom_resource_equipments bre,
65 bom_dept_res_instances bdri,
66 wip_operations wo
67 WHERE
68 ( ( p_resource_id IS NULL) OR
69 ( p_resource_id IS NOT NULL AND bre.resource_id = p_resource_id) )
70 AND bre.resource_id = bdri.resource_id
71 AND ( ( p_department_id IS NULL) OR
72 ( p_department_id IS NOT NULL AND bdri.department_id = p_department_id) )
73 AND bre.organization_id = msn.current_organization_id
74 AND cii.network_asset_flag = 'N'
75 and msn.current_organization_id = p_org_id
76 and cii.last_vld_organization_id = mp.organization_id
77 AND bre.inventory_item_id = msn.inventory_item_id
78 AND bdri.serial_number = msn.serial_number
79 AND cii.equipment_gen_object_id is not null
80 AND cii.equipment_gen_object_id = msn.gen_object_id
81 and wdj.maintenance_object_id = cii.instance_id
82 AND wdj.maintenance_object_type = 3
83 AND wdj.organization_id = mp.maint_organization_id
84 AND we.organization_id = wdj.organization_id
85 AND we.wip_entity_id = wdj.wip_entity_id
86 AND we.entity_type = 6
87 AND WDJ.wip_entity_id = WO.wip_entity_id(+)
88 AND ( ( wdj.shutdown_type in (2,3))
89 OR (NVL(wdj.shutdown_type,0) NOT IN (2,3) AND WO.shutdown_type IN (2,3) AND WDJ.wip_entity_id = WO.wip_entity_id )
90 )
91 AND ( ( p_include_unreleased = 1 AND wdj.status_type in (1,3))
92 OR ( p_include_unreleased <> 1 AND wdj.status_type = 3))
93 AND ( ( p_firm_order_only = 1 AND wdj.firm_planned_flag = 1)
94 OR (p_firm_order_only = 2) )
95 ORDER BY maint_org_id, asset_group_id, asset_number, from_date, wip_entity_id, op_seq;
96
97
98 type downtime_tbl_type is table of downtime_csr%rowtype index by binary_integer;
99
100
101 /* ========================================================================== */
102 -- PROCEDURE
103 -- Process_Production_Downtime
104 --
105 -- Description
106 -- This procedure is called by the concurrent program Load Production
107 -- Maintenance Downtime. The following parameters are passed by the
108 -- concurrent program:
109 -- . p_org_id
110 -- production organization to load maintenance downtime
111 -- . p_simulation_set
112 -- simulation set to load capacity reduction caused by downtime
113 -- . p_run_option
114 -- 1 = load downtime
115 -- 2 = purge all capacity change entries loaded by this process
116 -- . p_include_unreleased
117 -- 1 (yes) = consider both released and unreleased work orders
118 -- 2 (no) = consider only released work orders
119 -- . p_firm_order_only
120 -- 1 = consider only firm work orders
121 -- 2 = consider both firm and non-firm work orders
122 -- . p_department_id
123 -- Compute downtime only for equipment instances associated to
124 -- resources owned by the specified department.
125 -- . p_resource_id
126 -- Compute downtime only for equipment instances associated to
127 -- the specified resource.
128
129 /* ========================================================================== */
130
131 PROCEDURE Process_Production_Downtime(
132 errbuf OUT NOCOPY VARCHAR2,
133 retcode OUT NOCOPY NUMBER,
134 p_org_id IN NUMBER,
135 p_simulation_set IN VARCHAR2,
136 p_run_option IN NUMBER,
137 p_include_unreleased IN NUMBER,
138 p_firm_order_only IN NUMBER,
139 p_department_id IN NUMBER DEFAULT NULL,
140 p_resource_id IN NUMBER DEFAULT NULL
141 )
142 IS
143
144 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
145 l_msg_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
146 l_msg_count NUMBER := 0;
147 l_msg_data VARCHAR2(8000) := '';
148
149 l_err_num NUMBER := 0;
150 l_err_code VARCHAR2(240) := '';
151 l_err_msg VARCHAR2(240) := '';
152
153 l_stmt_num NUMBER := 0;
154 l_request_id NUMBER := 0;
155 l_user_id NUMBER := 0;
156 l_prog_id NUMBER := 0;
157 l_prog_app_id NUMBER := 0;
158 l_login_id NUMBER := 0;
159
160 l_conc_program_id NUMBER := 0;
161 conc_status BOOLEAN;
162 process_error EXCEPTION;
163
164 l_downtime_group_id NUMBER := 0;
165 l_exception_set_id NUMBER;
166 l_calendar_code VARCHAR2(10);
167
168 BEGIN
169 -- standard start of API savepoint
170 SAVEPOINT ProcessProductionDowntime_PUB;
171
172 ------------------------------------------------------------------
173 -- retrieving concurrent program information
174 ------------------------------------------------------------------
175 l_stmt_num := 5;
176
177 l_request_id := FND_GLOBAL.conc_request_id;
178 l_user_id := FND_GLOBAL.user_id;
179 l_prog_id := FND_GLOBAL.conc_program_id;
180 l_prog_app_id := FND_GLOBAL.prog_appl_id;
181 l_login_id := FND_GLOBAL.conc_login_id;
182 l_conc_program_id := FND_GLOBAL.conc_program_id;
183
184 l_stmt_num := 10;
185
186 FND_FILE.PUT_LINE(FND_FILE.LOG, 'request_id: '
187 ||to_char(l_request_id));
188 FND_FILE.PUT_LINE(FND_FILE.LOG, 'prog_appl_id: '
189 ||to_char(l_prog_app_id));
190 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_user_id: '
191 ||to_char(l_user_id));
192 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_program_id: '
193 ||to_char(l_prog_id));
194 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_login_id: '
195 ||to_char(l_login_id));
196 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_conc_program_id: '
197 ||to_char(l_conc_program_id));
198
199
200 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ');
201
202 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Organization: '
203 ||TO_CHAR(p_org_id));
204 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Simulation Set: '
205 ||p_simulation_set);
206 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Run Option: '
207 ||TO_CHAR(p_run_option));
208 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Include Unreleased Order: '
209 ||TO_CHAR(p_include_unreleased));
210 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Firm MaintenanceOder Only: '
211 ||TO_CHAR(p_firm_order_only));
212 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Department: '
213 ||TO_CHAR(p_department_id));
214 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Resource: '
215 ||TO_CHAR(p_resource_id));
216
217
218
219 select calendar_code, calendar_exception_set_id into l_calendar_code, l_exception_set_id
220 from mtl_parameters where organization_id=p_org_id;
221
222 l_stmt_num := 15;
223 SELECT bom_resource_downtime_group_s.nextval
224 INTO l_downtime_group_id
225 FROM DUAL;
226 if G_DEBUG = 'Y' then
227 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Downtime Group Id: '
228 ||TO_CHAR(l_downtime_group_id));
229 end if;
230
231 IF p_run_option = 1 THEN
232 l_stmt_num := 50;
233 Load_Downtime(
234 p_api_version => 1.0,
235 x_return_status => l_return_status,
236 x_msg_count => l_msg_count,
237 x_msg_data => l_msg_data,
238 p_downtime_group_id => l_downtime_group_id,
239 p_org_id => p_org_id,
240 p_simulation_set => p_simulation_set,
241 p_include_unreleased => p_include_unreleased,
242 p_firm_order_only => p_firm_order_only,
243 p_department_id => p_department_id,
244 p_resource_id => p_resource_id,
245 p_calendar_code => l_calendar_code,
246 p_exception_set_id => l_exception_set_id,
247 p_user_id => l_user_id,
248 p_request_id => l_request_id,
249 p_prog_id => l_prog_id,
250 p_prog_app_id => l_prog_app_id,
251 p_login_id => l_login_id
252 );
253 ELSE
254 l_stmt_num := 60;
255 Purge_Downtime(
256 p_api_version => 1.0,
257 x_return_status => l_return_status,
258 x_msg_count => l_msg_count,
259 x_msg_data => l_msg_data,
260 p_org_id => p_org_id,
261 p_simulation_set => p_simulation_set
262 );
263 END IF;
264
265 IF l_return_status <> FND_API.g_ret_sts_success THEN
266 WriteLog(
267 p_api_version => 1.0,
268 p_msg_count => l_msg_count,
269 p_msg_data => l_msg_data,
270 x_return_status => l_msg_return_status);
271 IF p_run_option = 1 THEN
272 l_err_code := 'Error: EAM_Downtime_Pub.Load_Downtime()';
273 ELSE
274 l_err_code := 'Error: EAM_Downtime_Pub.Purge_Downtime()';
275 END IF;
276 RAISE process_error;
277 END IF;
278
279 EXCEPTION
280 WHEN process_error THEN
281 ROLLBACK to ProcessProductionDowntime_PUB;
282 l_err_msg :='EAM_Downtime_PUB.Process_Production_Downtime ('
283 || TO_CHAR(l_stmt_num)
284 || '):'
285 || l_err_code;
286 FND_FILE.put_line(FND_FILE.log,l_err_msg);
287
288 conc_status := FND_CONCURRENT.set_completion_status('ERROR', l_err_msg);
289
290 WHEN OTHERS THEN
291 ROLLBACK to ProcessProductionDowntime_PUB;
292 l_err_num := SQLCODE;
293 l_err_code := NULL;
294 l_err_msg := SUBSTR('EAM_Downtime_PUB.Process_Production_Downtime ('
295 || TO_CHAR(l_stmt_num)
296 || '):'
297 || SQLERRM,1,240);
298 FND_FILE.put_line(FND_FILE.log,l_err_msg);
299
300 conc_status := FND_CONCURRENT.set_completion_status('ERROR', l_err_msg);
301
302 END Process_Production_Downtime;
303
304 procedure print_downtime(p_prompt in varchar2,
305 p_downtime in downtime_csr%rowtype) is
306 begin
307 IF G_DEBUG = 'Y' THEN
308 fnd_file.put_line(fnd_file.log, p_prompt||p_downtime.maint_org_id
309 || 'wip_entity_id:' ||p_downtime.wip_entity_id
310 || 'asset_group_id:' || p_downtime.asset_group_id
311 ||'asset_number:'||p_downtime.asset_number
312 ||'op_seq:'||p_downtime.op_seq
313 ||'from_date:'||p_downtime.from_date
314 ||'to_date:'||p_downtime.to_date);
315 end if;
316
317 end print_downtime;
318 function is_overlap(p_from_date in date,
319 p_from_time in number,
320 p_to_date in date,
321 p_to_time in number,
322 p_calendar_code in varchar2,
323 p_shift_num in number) return boolean is
324 l_count number;
325 l_from_time number;
326 l_to_time number;
327 i number;
328
329 begin
330 --logic to see if p_from_tim, p_to_time overlaps with bom_shift_times
331 /* i := 0;
332 for a_shift_time in (select from_time, to_time from bom_shift_times
333 where calendar_code=p_calendar_code and shift_num=p_shift_num) loop
334 i := i+1;
335 if (i = 1) then
336 l_from_time := a_shift_time.from_time;
337 end if;
338 l_to_time := a_shift_time.to_time;
339 end loop; */
340 -- a shift could have multiple stretches of time
341
342 select min(from_time), max(to_time) into l_from_time, l_to_time
343 from bom_shift_times
344 where calendar_code = p_calendar_code and shift_num = p_shift_num;
345
346 IF G_DEBUG = 'Y' THEN
347 fnd_file.put_line(fnd_file.log, 'Check if downtime overlaps shifts:p_from_date'||p_from_date||'p_from_time'||p_from_time||
348 'p_to_date:'||p_to_date||'p_to_time:'||p_to_time||'p_shift_num:'||p_shift_num||'l_from_time:'||l_from_time||
349 'lto_time:'||l_to_time);
350 end if;
351
352 -- if downtime spans across days
353 if p_to_date > p_from_date then
354 select count(shift_date) into l_count
355 from bom_shift_dates
356 where
357 trunc(shift_date) > trunc(p_from_date)
358 and trunc(shift_date) < trunc(p_to_date)
359 and seq_num is not null
360 and calendar_code = p_calendar_code and shift_num = p_shift_num;
361
362 -- if there are shifts for sure (more than 24 hr.) in between the down time start and to dates
363 if ((l_count > 0) or (p_to_time > p_from_time)) then
364 return true;
365 else
366 --p_to_time := p_to_time+86400; -- add one day to p_to_time
367
368 -- As long as the shift starts or ends in between the downtime
369 -- start and to dates, there is overlap.
370 if (l_from_time > p_from_time) or (l_from_time < p_to_time) or
371 (l_to_time > p_from_time) or (l_to_time < p_to_time) then
372 return true;
373 end if;
374 end if;
375
376 -- if downtime starts and ends on the same day
377 elsif p_to_date = p_from_date then
378
379 if (l_to_time < l_from_time) then
380 if (l_from_time < p_to_time) or (l_to_time > p_from_time) then
381 return true;
382 end if;
383 else
384 -- Bug # 4190920 : Need to corret the logic for overlap
385 if (p_from_time < l_to_time and l_from_time < p_to_time) then
386 return true;
387 end if;
388 end if;
389 end if;
390
391 return false;
392 end is_overlap;
393
394 -- Assume in_table is sorted on from_date, and out_table is empty
395 procedure merge_table(in_table IN downtime_tbl_type,
396 num_recs IN NUMBER,
397 out_table OUT NOCOPY downtime_tbl_type
398 ) IS
399 i number;
400 j number;
401 BEGIN
402 i := 0;
403 j := 0;
404
405 while i < num_recs loop
406 -- base case
407
408 if (i = 0) then
409 out_table(0) := in_table(0);
410 else
411
412 if(in_table(i).from_date > out_table(j).to_date) then
413 -- non overlapping interval
414 --dbms_output.put_line('new interval');
415 j := j+1;
416 out_table(j) := in_table(i);
417 elsif(in_table(i).to_date > out_table(j).to_date) then
418 -- need to stretch the current interval
419 -- dbms_output.put_line('stretch');
420 out_table(j).op_seq := null;
421
422 -- if the two rows are from different wo
423 if in_table(i).wip_entity_id <> out_table(j).wip_entity_id then
424 out_table(j).downtime_source_code := 2;
425 -- else if it wasn't already 2, set it to 3
426 -- 3 means operations within the same wo
427 elsif out_table(j).downtime_source_code <> 2 then
428 out_table(j).downtime_source_code := 3;
429 end if;
430
431 out_table(j).to_date := in_table(i).to_date;
432 else
433 -- new interval contained in the current interval, ignore
434 out_table(j).op_seq := null;
435
436 -- if the two rows are from different wo
437 if in_table(i).wip_entity_id <> out_table(j).wip_entity_id then
438 out_table(j).downtime_source_code := 2;
439 -- else if it wasn't already 2, set it to 3
440 -- 3 means operations within the same wo
441 elsif out_table(j).downtime_source_code <> 2 then
442 out_table(j).downtime_source_code := 3;
443 end if;
444 end if;
445 end if;
446
447 i := i+1;
448 end loop;
449 end merge_table;
450
451
452 /* Added for bug 3787120
453 -> This procedure is called before inserting into the table BOM_RES_INSTANCE_CHANGES
454 -> Check for the date/time range overlap with the existing record in BOM_RES_INSTANCE_CHANGES
455 -> If overlapping occurs then TRUE is returned. And if there is need for updating the record
456 then parameter p_out_to_update is updated to true.
457 -> If no overlapping, then FALSE is returned.
458 */
459
460 Function check_existence(p_downtime_row IN downtime_csr%ROWTYPE,
461 p_shift_num IN number,
462 p_simulation_set IN varchar2,
463 p_from_date IN date,
464 p_from_time IN number,
465 p_to_date IN date,
466 p_to_time IN number,
467 p_action_type IN number,
468 p_out_from_date OUT NOCOPY date,
469 p_out_from_time OUT NOCOPY number,
470 p_out_to_date OUT NOCOPY date,
471 p_out_to_time OUT NOCOPY number,
472 p_out_from_date_old OUT NOCOPY date,
473 p_out_from_time_old OUT NOCOPY number,
474 p_out_to_date_old OUT NOCOPY date,
475 p_out_to_time_old OUT NOCOPY number,
476 p_out_to_update OUT NOCOPY boolean) return boolean is
477 l_from_date_old date;
478 l_to_date_old date;
479 l_from_date_new date;
480 l_to_date_new date;
481 begin
482 IF G_DEBUG = 'Y' THEN
483 fnd_file.put_line(fnd_file.log,'In check_existence');
484 end if;
485
486 p_out_to_update := FALSE;
487
488 -- Assign the New Date time range values
489 l_from_date_new := trunc(p_from_date) + p_from_time / 86400 ;
490 l_to_date_new := trunc(p_to_date) + p_to_time / 86400;
491
492 --Check if new record's date/time range clashes with that of the old record
493 select from_date, from_time, to_date, to_time
494 into p_out_from_date_old, p_out_from_time_old, p_out_to_date_old, p_out_to_time_old
495 from bom_res_instance_changes
496 where department_id = p_downtime_row.department_id
497 and resource_id = p_downtime_row.resource_id
498 and shift_num = p_shift_num
499 and simulation_set = p_simulation_set
500 and instance_id = p_downtime_row.instance_id
501 and serial_number = p_downtime_row.eqp_serial_number
502 and action_type = p_action_type
503 and ( ( (trunc(from_date)+(from_time/86400))
504 between l_from_date_new and l_to_date_new)
505 or
506 (( trunc(to_date)+(to_time/86400))
507 between l_from_date_new and l_to_date_new)
508 or
509 ( l_from_date_new between
510 (trunc(from_date)+(from_time/86400))
511 and
512 (trunc(to_date)+(to_time/86400)))
513 or
514 (l_to_date_new between
515 (trunc(from_date)+(from_time/86400))
516 and
517 (trunc(to_date)+(to_time/86400))));
518
519 IF G_DEBUG = 'Y' THEN
520 fnd_file.put_line(fnd_file.log,'EXISTS');
521 end if;
522
523 p_out_from_date := p_out_from_date_old;
524 p_out_from_time := p_out_from_time_old;
525 p_out_to_date := p_out_to_date_old;
526 p_out_to_time := p_out_to_time_old;
527
528 -- Assign data time range values as stored in database.
529 l_from_date_old := trunc(p_out_from_date_old)+(p_out_from_time_old/86400);
530 l_to_date_old := trunc(p_out_to_date_old)+(p_out_to_time_old/86400);
531
532 -- Check if new date time range is same as that stored in database.
533 if (l_from_date_new = l_from_date_old and l_to_date_new = l_to_date_old) then
534 -- Both data time range is same.
535 -- hence no update and no insert is required.
536 p_out_to_update := FALSE; --to be skipped
537 IF G_DEBUG = 'Y' THEN
538 fnd_file.put_line(fnd_file.log,'to be SKIPPED');
539 end if;
540 return TRUE;
541 end if;
542
543 -- Find new values for time range to be updated in database.
544
545 if ( l_from_date_new < l_from_date_old ) then
546 p_out_from_date := p_from_date;
547 p_out_from_time := p_from_time;
548 p_out_to_update := TRUE;
549 IF G_DEBUG = 'Y' THEN
550 fnd_file.put_line(fnd_file.log,'Old from date : '||to_char(l_from_date_old,'DD-MON-YYYY HH24:MI:SS'));
551 fnd_file.put_line(fnd_file.log,'New from date : '||to_char(l_from_date_new,'DD-MON-YYYY HH24:MI:SS'));
552 end if;
553
554 end if;
555 if ( l_to_date_new > l_to_date_old ) then
556 p_out_to_date := p_to_date;
557 p_out_to_time := p_to_time;
558 p_out_to_update := TRUE;
559 IF G_DEBUG = 'Y' THEN
560 fnd_file.put_line(fnd_file.log,'Old to date : '||to_char(l_to_date_old,'DD-MON-YYYY HH24:MI:SS'));
561 fnd_file.put_line(fnd_file.log,'New to date : '||to_char(l_to_date_new,'DD-MON-YYYY HH24:MI:SS'));
562 end if;
563
564 end if;
565 return TRUE;
566 exception
567 when no_data_found then
568 IF G_DEBUG = 'Y' THEN
569 fnd_file.put_line(fnd_file.log,'In check_existence : DOES NOT EXIST');
570 end if;
571 p_out_to_update := FALSE;
572 return FALSE;
573 end check_existence;
574
575
576
577 /*
578 * Added the following procedure as a part of bug#3577299
579 * Given a from date/time and a to date/time, this procedure breaks
580 * up the interval into start and end times based on shifts for the
581 * resource/department and inserts into bom_res_instance_changes
582 */
583 procedure break_and_insert (p_from_date IN DATE,
584 p_from_time IN NUMBER,
585 p_to_date IN DATE,
586 p_to_time IN NUMBER,
587 p_shift_num IN NUMBER,
588 p_calendar_code IN VARCHAR2,
589 p_exception_set_id IN NUMBER,
590 p_simulation_set IN VARCHAR2,
591 p_downtime_group_id IN NUMBER,
592 p_downtime_row IN downtime_csr%ROWTYPE) is
593
594 -- Bug # 3787120 Modified cursor query
595 cursor c_wdays is
596 select bd.shift_date,bt.from_time,bt.to_time
597 from bom_shift_dates bd,bom_shift_times bt
598 where trunc(bd.shift_date) >= trunc(p_from_date)
599 and trunc(bd.shift_date) <= trunc(p_to_date)
600 and bd.calendar_code = p_calendar_code
601 and bd.shift_num = p_shift_num
602 and bd.exception_set_id= p_exception_set_id
603 and bd.seq_num is not null
604 and bt.calendar_code = bd.calendar_code
605 and bt.shift_num = bd.shift_num
606 order by bd.shift_date,bt.from_time;
607
608 cursor c_prior_date(p_curr_date date) is
609 select decode(nvl(bd.seq_num,-999),-999,bd.prior_date,bd.shift_date)
610 from bom_shift_dates bd
611 where trunc(bd.shift_date) = trunc(p_curr_date)-1
612 and bd.calendar_code = p_calendar_code
613 and bd.shift_num = p_shift_num
614 and bd.exception_set_id = p_exception_set_id;
615
616 l_shift_from_date date;
617 l_shift_to_date date;
618 l_shift_from_time number;
619 l_shift_to_time number;
620 l_from_date date;
621 l_to_date date;
622 l_from_time number;
623 l_to_time number;
624
625 -- Bug # 3787120 Added new variables.
626 l_to_insert boolean;
627 l_out_to_update boolean;
628
629 l_out_from_date date;
630 l_out_from_time number;
631 l_out_to_date date;
632 l_out_to_time number;
633
634 l_out_from_date_old date;
635 l_out_from_time_old number;
636 l_out_to_date_old date;
637 l_out_to_time_old number;
638
639 begin
640 IF G_DEBUG = 'Y' THEN
641 fnd_file.put_line(fnd_file.log,'In break_and_insert ');
642 fnd_file.put_line(fnd_file.log,'p_from_date '||p_from_date);
643 fnd_file.put_line(fnd_file.log,'p_from_time '||p_from_time);
644 fnd_file.put_line(fnd_file.log,'p_to_date '||p_to_date);
645 fnd_file.put_line(fnd_file.log,'p_to_time '||p_to_time);
646 fnd_file.put_line(fnd_file.log,'p_shift_num '||p_shift_num);
647 fnd_file.put_line(fnd_file.log,'p_calendar_code '||p_calendar_code);
648 fnd_file.put_line(fnd_file.log,'p_exception_set_id '||p_exception_set_id);
649 END IF;
650
651 for wd in c_wdays loop
652
653 -- Bug # 3787120
654 if (wd.from_time <= wd.to_time) then
655 l_shift_from_date := wd.shift_date;
656 else
657 -- Shift starts from previous day.
658 open c_prior_date(wd.shift_date);
659 fetch c_prior_date into l_shift_from_date;
660 close c_prior_date;
661 end if;
662
663 l_shift_to_date := wd.shift_date;
664 l_shift_from_time := wd.from_time;
665 l_shift_to_time := wd.to_time;
666
667 IF G_DEBUG = 'Y' THEN
668 fnd_file.put_line(fnd_file.log,'p_from: '||to_char(p_from_date,'DD-MM-YYYY ')||to_char(to_date(p_from_time,'SSSSS'),'HH24:MI:SS'));
669 fnd_file.put_line(fnd_file.log,'l_from: '||to_char(l_shift_from_date,'DD-MM-YYYY ')||to_char(to_date(l_shift_from_time,'SSSSS'),'HH24:MI:SS'));
670 fnd_file.put_line(fnd_file.log,'p_to: '||to_char(p_to_date,'DD-MM-YYYY ')||to_char(to_date(p_to_time,'SSSSS'),'HH24:MI:SS'));
671 fnd_file.put_line(fnd_file.log,'l_to: '||to_char(l_shift_to_date,'DD-MM-YYYY ')||to_char(to_date(l_shift_to_time,'SSSSS'),'HH24:MI:SS'));
672 end if;
673
674 l_to_insert := FALSE;
675 l_out_to_update := FALSE;
676
677 -- Check if the date time range is inside the shift of the resource
678 if ((to_date(to_char(p_to_date,'DD-MM-YYYY ')||to_char(p_to_time),'DD-MM-YYYY SSSSS') >=
679 to_date(to_char(l_shift_from_date,'DD-MM-YYYY ')||to_char(l_shift_from_time),'DD-MM-YYYY SSSSS')) AND
680 (to_date(to_char(p_from_date,'DD-MM-YYYY ')||to_char(p_from_time),'DD-MM-YYYY SSSSS') <=
681 to_date(to_char(l_shift_to_date,'DD-MM-YYYY ')||to_char(l_shift_to_time),'DD-MM-YYYY SSSSS')))
682 then
683 -- Need to insert / update the record
684
685 -- Update the date time value to fit in the shift timing of the resource
686 if (to_date(to_char(p_from_date,'DD-MM-YYYY ')||to_char(p_from_time),'DD-MM-YYYY SSSSS') >
687 to_date(to_char(l_shift_from_date,'DD-MM-YYYY ')||to_char(l_shift_from_time),'DD-MM-YYYY SSSSS'))
688 then
689 l_from_date := p_from_date;
690 l_from_time := p_from_time;
691 else
692 l_from_date := l_shift_from_date;
693 l_from_time := l_shift_from_time;
694 end if;
695
696 if (to_date(to_char(p_to_date,'DD-MM-YYYY ')||to_char(p_to_time),'DD-MM-YYYY SSSSS') <
697 to_date(to_char(l_shift_to_date,'DD-MM-YYYY ')||to_char(l_shift_to_time),'DD-MM-YYYY SSSSS'))
698 then
699 l_to_date := p_to_date;
700 l_to_time := p_to_time;
701 else
702 l_to_date := l_shift_to_date;
703 l_to_time := l_shift_to_time;
704 end if;
705
706
707 -- Bug # 3787120 : Check if the record exists alread
708 IF G_DEBUG = 'Y' THEN
709 fnd_file.put_line(fnd_file.log,'Checking existence-'||'dept:'||p_downtime_row.department_id||', resc:'||p_downtime_row.resource_id||
710 ', shift num:'||p_shift_num||', sim set:'||p_simulation_set||', from:'||l_from_date||to_char(to_date(l_from_time,'SSSSS'),' HH24:MI:SS')||
711 ',to '||l_to_date||to_char(to_date(l_to_time,'SSSSS'),' HH24:MI:SS')||', instance id:'||p_downtime_row.instance_id||', serial no:'||p_downtime_row.eqp_serial_number);
712 END IF;
713
714 if ( check_existence(p_downtime_row,
715 p_shift_num,
716 p_simulation_set,
717 l_from_date,
718 l_from_time,
719 l_to_date,
720 l_to_time,
721 2,
722 l_out_from_date,
723 l_out_from_time,
724 l_out_to_date,
725 l_out_to_time,
726 l_out_from_date_old,
727 l_out_from_time_old,
728 l_out_to_date_old,
729 l_out_to_time_old,
730 l_out_to_update) = TRUE )
731 then
732 l_to_insert := FALSE;
733 else
734 -- Record does not exist so insert
735 l_to_insert := TRUE;
736 end if;
737
738 if G_DEBUG = 'Y' THEN
739 if l_to_insert = TRUE then
740 fnd_file.put_line(fnd_file.log,'To be inserted');
741 end if;
742 if l_out_to_update = TRUE then
743 fnd_file.put_line(fnd_file.log,'To be updated');
744 end if;
745 end if;
746
747 if ( l_to_insert = TRUE and l_out_to_update = FALSE ) then
748 if G_DEBUG = 'Y' THEN
749 fnd_file.put_line(fnd_file.log,'Inserting from '||l_from_date||to_char(to_date(l_from_time,'SSSSS'),' HH24:MI:SS')||
750 ' to '||l_to_date||to_char(to_date(l_to_time,'SSSSS'),' HH24:MI:SS'));
751 end if;
752
753 insert into bom_res_instance_changes(
754 department_id,
755 resource_id,
756 shift_num,
757 simulation_set,
758 from_date,
759 from_time,
760 to_date,
761 to_time,
762 instance_id,
763 serial_number,
764 action_type,
765 LAST_UPDATE_DATE,
766 LAST_UPDATED_BY,
767 CREATION_DATE,
768 CREATED_BY,
769 LAST_UPDATE_LOGIN,
770 ATTRIBUTE_CATEGORY,
771 ATTRIBUTE1,
772 ATTRIBUTE2,
773 ATTRIBUTE3,
774 ATTRIBUTE4,
775 ATTRIBUTE5,
776 ATTRIBUTE6,
777 ATTRIBUTE7,
778 ATTRIBUTE8,
779 ATTRIBUTE9,
780 ATTRIBUTE10,
781 ATTRIBUTE11,
782 ATTRIBUTE12,
783 ATTRIBUTE13,
784 ATTRIBUTE14,
785 ATTRIBUTE15,
786 capacity_change,
787 reason_code,
788 downtime_source_code,
789 maintenance_organization_id,
790 wip_entity_id,
791 operation_seq_num,
792 downtime_group_id,
793 downtime_negotiable_flag)
794 values(
795 p_downtime_row.department_id,
796 p_downtime_row.resource_id,
797 p_shift_num,
798 p_simulation_set,
799 l_from_date,
800 l_from_time,
801 l_to_date,
802 l_to_time,
803 p_downtime_row.instance_id,
804 p_downtime_row.eqp_serial_number,
805 2, --reduce capacity
806 sysdate, --standard who
807 fnd_global.user_id,
808 sysdate,
809 fnd_global.user_id,
810 fnd_global.login_id,
811 null, null, --descp flex
812 null, null,
813 null, null,
814 null, null,
815 null, null,
816 null, null,
817 null, null,
818 null, null,
819 -1, --capacity change
820 g_reason_code,
821 p_downtime_row.downtime_source_code,
822 p_downtime_row.maint_org_id,
823 p_downtime_row.wip_entity_id,
824 p_downtime_row.op_seq,
825 p_downtime_group_id,
826 p_downtime_row.firm_planned_flag);
827
828 end if;
829
830 if ( l_out_to_update = TRUE and l_to_insert = FALSE ) then
831 if G_DEBUG = 'Y' THEN
832 fnd_file.put_line(fnd_file.log,'Updating...');
833 end if;
834
835 update bom_res_instance_changes
836 set from_date = l_out_from_date,
837 from_time = l_out_from_time,
838 to_date = l_out_to_date,
839 to_time = l_out_to_time
840 where department_id = p_downtime_row.department_id
841 and resource_id = p_downtime_row.resource_id
842 and shift_num = p_shift_num
843 and simulation_set = p_simulation_set
844 and instance_id = p_downtime_row.instance_id
845 and serial_number = p_downtime_row.eqp_serial_number
846 and action_type = 2
847 and from_date = l_out_from_date_old
848 and from_time = l_out_from_time_old
849 and to_date = l_out_to_date_old
850 and to_time = l_out_to_time_old;
851 end if;
852
853 end if;
854 end loop;
855 end break_and_insert;
856
857 procedure process_one_table(p_in_downtime_tbl in downtime_tbl_type,
858 p_in_len in number,
859 p_department_id in number,
860 p_resource_id in number,
861 p_calendar_code in varchar2,
862 p_exception_set_id in number,
863 p_downtime_group_id in number,
864 p_simulation_set in varchar2) is
865 j number;
866 l_out_downtime_tbl downtime_tbl_type;
867 l_out_len number;
868 l_from_date DATE;
869 l_to_date DATE;
870 l_from_time NUMBER;
871 l_to_time NUMBER;
872
873 cursor shift_csr(pp_department_id number,
874 pp_resource_id number,
875 pp_from_date date,
876 pp_to_date date) is
877 select bsd1.shift_num, bsd1.seq_num start_seq, bsd1.next_date next_date,
878 bsd2.seq_num end_seq, bsd2.prior_date prior_date
879 from bom_shift_dates bsd1, bom_shift_dates bsd2, bom_resource_shifts brs where
880 trunc(bsd1.shift_date) =trunc(pp_from_date)
881 and bsd1.calendar_code=p_calendar_code
882 and bsd1.exception_set_id=p_exception_set_id
883 and bsd1.shift_num=bsd2.shift_num
884 and trunc(bsd2.shift_date)=trunc(pp_to_date)
885 and bsd2.calendar_code=p_calendar_code
886 and bsd2.exception_set_id=p_exception_set_id
887 and brs.department_id= pp_department_id
888 and brs.resource_id = pp_resource_id
889 and brs.shift_num=bsd1.shift_num;
890
891 begin
892
893 merge_table(p_in_downtime_tbl, p_in_len, l_out_downtime_tbl);
894 --now, processing data
895 l_out_len := l_out_downtime_tbl.count;
896 IF G_DEBUG = 'Y' THEN
897 fnd_file.put_line(fnd_file.log, 'Now:table in_len:'||p_in_len||'table out_len:'||l_out_len);
898 end if;
899 j := 0;
900 while j < l_out_len loop
901 print_downtime('out_table row', l_out_downtime_tbl(j));
902 IF G_DEBUG = 'Y' THEN
903 fnd_file.put_line(fnd_file.log,'p_calendar_code: '||p_calendar_code);
904 fnd_file.put_line(fnd_file.log,'p_exception_set_id: '||p_exception_set_id);
905 fnd_file.put_line(fnd_file.log,'department_id: '||l_out_downtime_tbl(j).department_id);
906 fnd_file.put_line(fnd_file.log,'resource_id: '||l_out_downtime_tbl(j).resource_id);
907 fnd_file.put_line(fnd_file.log,'from_date: '||l_out_downtime_tbl(j).from_date);
908 fnd_file.put_line(fnd_file.log,'to_date: '||l_out_downtime_tbl(j).to_date);
909 end if;
910 for a_shift in shift_csr(l_out_downtime_tbl(j).department_id, l_out_downtime_tbl(j).resource_id,
911 l_out_downtime_tbl(j).from_date, l_out_downtime_tbl(j).to_date) loop
912 -- start_date
913 -- start_seq is null->off date->move to next date, time=60
914 IF G_DEBUG = 'Y' THEN
915 fnd_file.put_line(fnd_file.log, 'shift_num:'||a_shift.shift_num);
916 end if;
917 if a_shift.start_seq is null then
918 l_from_date := a_shift.next_date;
919 l_from_time := 60;
920 -- on date
921 else
922 l_from_date := l_out_downtime_tbl(j).from_date;
923 l_from_time := to_number(to_char(round(l_out_downtime_tbl(j).from_date, 'MI'), 'SSSSS'));
924 end if;
925 -- end_date
926 -- end_seq is null->off date->move to prev date, time=86340
927 if a_shift.end_seq is null then
928 l_to_date := a_shift.prior_date;
929 l_to_time := 86340;
930 else
931 l_to_date := l_out_downtime_tbl(j).to_date;
932 l_to_time := to_number(to_char(round(l_out_downtime_tbl(j).to_date, 'MI'), 'SSSSS'));
933 end if;
934 --Now insert into bric
935 l_from_date := trunc(l_from_date);
936 l_to_date := trunc(l_to_date);
937 if is_overlap(l_from_date,l_from_time, l_to_date, l_to_time, p_calendar_code, a_shift.shift_num)
938 then
939 /* Moved the insert stmt on bom_res_instance_changes
940 * to procedure break_and_insert as part of fix for
941 * bug#3577299
942 */
943 break_and_insert(l_from_date,l_from_time,l_to_date,l_to_time,
944 a_shift.shift_num,p_calendar_code,
945 p_exception_set_id,p_simulation_set,
946 p_downtime_group_id,l_out_downtime_tbl(j));
947 end if; -- if time overlap
948
949 end loop; --cursor shift_csr
950 j := j+1;
951 end loop; --out table loop
952 end process_one_table;
953
954
955 ----------------------------------------------------------------------------
956 -- PROCEDURE
957 -- WriteLog
958 --
959 -- DESCRIPTION
960 -- This API retrieves messages from message stack and write them to log file --
961 ---------------------------------------------------------------------------
962 PROCEDURE WriteLog (p_api_version IN NUMBER,
963 p_msg_count IN NUMBER,
964 p_msg_data IN VARCHAR2,
965 x_return_status OUT NOCOPY VARCHAR2) IS
966
967 l_api_name CONSTANT VARCHAR2(30) := 'WriteLog';
968 l_api_version CONSTANT NUMBER := 1.0;
969
970 l_msg_count NUMBER :=0;
971 l_msg_data VARCHAR2(8000):= '';
972
973 l_stmt_num NUMBER := 0;
974
975 BEGIN
976 -- standard start of API savepoint
977 SAVEPOINT WriteLog_PUB;
978
979 -- standard call to check for call compatibility
980 IF NOT FND_API.compatible_api_call (
981 l_api_version,
982 p_api_version,
983 l_api_name,
984 G_PKG_NAME ) then
985 RAISE FND_API.g_exc_unexpected_error;
986 END IF;
987
988 -- initialize api return status to success
989 x_return_status := FND_API.g_ret_sts_success;
990
991 -- assign to local variables
992 l_msg_count := p_msg_count;
993 l_msg_data := p_msg_data;
994
995 /* obtain messages from the message list */
996 l_stmt_num := 5;
997 FND_MSG_PUB.count_and_get(
998 p_encoded => FND_API.g_false,
999 p_count => l_msg_count,
1000 p_data => l_msg_data
1001 );
1002
1003 /* write all messages in the concurrent manager log */
1004 l_stmt_num := 10;
1005 IF(l_msg_count > 0) THEN
1006 FOR i in 1 ..l_msg_count
1007 LOOP
1008 l_msg_data := FND_MSG_PUB.get(i, FND_API.g_false);
1009 FND_FILE.put_line(FND_FILE.log, i ||'-'||l_msg_data);
1010 END LOOP;
1011 END IF;
1012
1013 EXCEPTION
1014 WHEN FND_API.g_exc_error THEN
1015 x_return_status := FND_API.g_ret_sts_error;
1016 FND_FILE.put_line(FND_FILE.log,'EAM_Downtime_PUB.WriteLog('
1017 || l_stmt_num
1018 || '): '
1019 || x_return_status
1020 || substr(SQLERRM,1,200));
1021 WHEN FND_API.g_exc_unexpected_error THEN
1022 x_return_status := FND_API.g_ret_sts_unexp_error;
1023 FND_FILE.put_line(FND_FILE.log,'EAM_Downtime_PUB.WriteLog('
1024 || l_stmt_num
1025 || '): '
1026 || x_return_status
1027 || substr(SQLERRM,1,200));
1028 WHEN others THEN
1029 x_return_status := FND_API.g_ret_sts_unexp_error;
1030 FND_FILE.put_line(FND_FILE.log,'EAM_Downtime_PUB.WriteLog('
1031 || l_stmt_num
1032 || '): '
1033 || x_return_status
1034 || substr(SQLERRM,1,200));
1035
1036 END WriteLog;
1037 ---------------------------------------------------------------------------
1038 PROCEDURE Load_Downtime(
1039 p_api_version IN NUMBER,
1040 x_return_status OUT NOCOPY VARCHAR2,
1041 x_msg_count OUT NOCOPY NUMBER,
1042 x_msg_data OUT NOCOPY VARCHAR2,
1043 p_downtime_group_id IN NUMBER,
1044 p_org_id IN NUMBER,
1045 p_simulation_set IN VARCHAR2,
1046 p_include_unreleased IN NUMBER,
1047 p_firm_order_only IN NUMBER,
1048 p_department_id IN NUMBER,
1049 p_resource_id IN NUMBER,
1050 p_calendar_code IN VARCHAR2,
1051 p_exception_set_id IN NUMBER,
1052 p_user_id IN NUMBER,
1053 p_request_id IN NUMBER,
1054 p_prog_id IN NUMBER,
1055 p_prog_app_id IN NUMBER,
1056 p_login_id IN NUMBER) IS
1057
1058 l_api_name CONSTANT VARCHAR2(30) := 'LoadDowntime';
1059 l_api_version CONSTANT NUMBER := 1.0;
1060
1061 l_msg_count NUMBER :=0;
1062 l_msg_data VARCHAR2(8000):= '';
1063
1064 l_stmt_num NUMBER := 0;
1065 l_old_maint_org_id NUMBER;
1066 l_old_asset_group_id NUMBER;
1067 l_old_asset_number varchar2(30);
1068 i NUMBER;
1069 j NUMBER;
1070 l_in_downtime_tbl downtime_tbl_type;
1071
1072 BEGIN
1073 -- standard start of API savepoint
1074 SAVEPOINT LoadDowntime_PUB;
1075
1076 -- standard call to check for call compatibility
1077 IF NOT FND_API.compatible_api_call (
1078 l_api_version,
1079 p_api_version,
1080 l_api_name,
1081 G_PKG_NAME ) then
1082 RAISE FND_API.g_exc_unexpected_error;
1083 END IF;
1084
1085 -- initialize api return status to success
1086 x_return_status := FND_API.g_ret_sts_success;
1087
1088 --API Body
1089 --Delete BRIC rows
1090 delete from bom_res_instance_changes
1091 where
1092 downtime_group_id is not null
1093 and simulation_set=p_simulation_set
1094 and ((p_department_id is not null and department_id=p_department_id)
1095 or (p_department_id is null and department_id in
1096 (select department_id from bom_departments
1097 where organization_id=p_org_id)))
1098 and ((p_resource_id is not null and resource_id=p_resource_id)
1099 or (p_resource_id is null));
1100
1101 --Delete BRC rows
1102 delete from bom_resource_changes
1103 where
1104 downtime_group_id is not null
1105 and simulation_set=p_simulation_set
1106 and ((p_department_id is not null and department_id=p_department_id)
1107 or (p_department_id is null and department_id in
1108 (select department_id from bom_departments
1109 where organization_id=p_org_id)))
1110 and ((p_resource_id is not null and resource_id=p_resource_id)
1111 or (p_resource_id is null));
1112
1113
1114
1115
1116 i := 0;
1117 IF G_DEBUG = 'Y' THEN
1118 fnd_file.put_line(fnd_file.log, 'about to enter, p_department_id:'||p_department_id||'resource_id:'||p_resource_id||'p_group_id:'||p_downtime_group_id
1119 ||'calendar_code:'||p_calendar_code||'exception_set_id:'||p_exception_set_id);
1120 fnd_file.put_line(fnd_file.log, 'organization' ||p_org_id);
1121 end if;
1122
1123 for a_downtime in downtime_csr(p_org_id, p_department_id, p_resource_id,
1124 p_include_unreleased, p_firm_order_only) loop
1125
1126 -- do nothing
1127 -- Only called the first time entering the loop
1128 IF G_DEBUG = 'Y' THEN
1129 fnd_file.put_line(fnd_file.log,'wip_entity_id:' ||a_downtime.wip_entity_id
1130 || 'asset_group_id:' || a_downtime.asset_group_id
1131 ||'asset_number:'||a_downtime.asset_number
1132 ||'op_seq:'||a_downtime.op_seq
1133 ||'from_date:'||a_downtime.from_date
1134 ||'to_date:'||a_downtime.to_date);
1135 -- ||'shutdown_type:'||a_downtime.shutdown_type
1136 -- ||'prod_org_id:'||a_downtime.prod_org_id
1137 -- ||'equipment_item_id:'||a_downtime.equipment_item_id
1138 -- ||'eqp_serial_number:'||a_downtime.eqp_serial_number
1139 -- ||'department_id:'||a_downtime.department_id
1140 -- ||'resource_id:'||a_downtime.resource_id
1141 -- ||'instance_id:'||a_downtime.instance_id);
1142 end if;
1143 if i = 0 then
1144 l_old_maint_org_id := a_downtime.maint_org_id;
1145 l_old_asset_group_id := a_downtime.asset_group_id;
1146 l_old_asset_number := a_downtime.asset_number;
1147
1148 elsif a_downtime.maint_org_id <> l_old_maint_org_id
1149 or a_downtime.asset_group_id <> l_old_asset_group_id
1150 or a_downtime.asset_number <> l_old_asset_number
1151 then
1152 process_one_table(l_in_downtime_tbl, i,
1153 p_department_id, p_resource_id,
1154 p_calendar_code, p_exception_set_id,
1155 p_downtime_group_id, p_simulation_set);
1156 i := 0;
1157 l_old_maint_org_id := a_downtime.maint_org_id;
1158 l_old_asset_group_id := a_downtime.asset_group_id;
1159 l_old_asset_number := a_downtime.asset_number;
1160 end if; -- if condition for chaning asset
1161
1162 l_in_downtime_tbl(i) := a_downtime;
1163
1164 i := i+1;
1165
1166 end loop; --cursor downtime_csr
1167 --process the last in_date table
1168 process_one_table(l_in_downtime_tbl, i,
1169 p_department_id, p_resource_id,
1170 p_calendar_code, p_exception_set_id,
1171 p_downtime_group_id, p_simulation_set);
1172
1173 --Now insert into brc
1174
1175 insert into bom_resource_changes(
1176 DEPARTMENT_ID,
1177 RESOURCE_ID,
1178 SHIFT_NUM,
1179 LAST_UPDATE_DATE,
1180 LAST_UPDATED_BY,
1181 CREATION_DATE,
1182 CREATED_BY,
1183 LAST_UPDATE_LOGIN,
1184 FROM_DATE,
1185 TO_DATE,
1186 FROM_TIME,
1187 TO_TIME,
1188 CAPACITY_CHANGE,
1189 SIMULATION_SET,
1190 ATTRIBUTE_CATEGORY,
1191 ATTRIBUTE1,
1192 ATTRIBUTE2,
1193 ATTRIBUTE3,
1194 ATTRIBUTE4,
1195 ATTRIBUTE5,
1196 ATTRIBUTE6,
1197 ATTRIBUTE7,
1198 ATTRIBUTE8,
1199 ATTRIBUTE9,
1200 ATTRIBUTE10,
1201 ATTRIBUTE11,
1202 ATTRIBUTE12,
1203 ATTRIBUTE13,
1204 ATTRIBUTE14,
1205 ATTRIBUTE15,
1206 REQUEST_ID ,
1207 PROGRAM_APPLICATION_ID,
1208 PROGRAM_ID,
1209 PROGRAM_UPDATE_DATE,
1210 ACTION_TYPE,
1211 REASON_CODE,
1212 downtime_group_id)
1213 select
1214 DEPARTMENT_ID,
1215 RESOURCE_ID,
1216 SHIFT_NUM,
1217 sysdate,
1218 fnd_global.user_id,
1219 sysdate,
1220 fnd_global.user_id,
1221 fnd_global.login_id,
1222 FROM_DATE,
1223 TO_DATE,
1224 FROM_TIME,
1225 TO_TIME,
1226 sum(CAPACITY_CHANGE),
1227 SIMULATION_SET,
1228 null, null, --descp flex
1229 null, null,
1230 null, null,
1231 null, null,
1232 null, null,
1233 null, null,
1234 null, null,
1235 null, null,
1236 p_request_id ,
1237 p_prog_app_id,
1238 p_prog_id,
1239 sysdate,
1240 ACTION_TYPE,
1241 g_reason_code,
1242 p_downtime_group_id
1243 from bom_res_instance_changes
1244 where
1245 downtime_group_id=p_downtime_group_id
1246 group by DEPARTMENT_ID,
1247 RESOURCE_ID,
1248 SHIFT_NUM,
1249 FROM_DATE,
1250 TO_DATE,
1251 FROM_TIME,
1252 TO_TIME,
1253 ACTION_TYPE,
1254 SIMULATION_SET;
1255 commit;
1256 EXCEPTION
1257 WHEN FND_API.g_exc_error THEN
1258 x_return_status := FND_API.g_ret_sts_error;
1259 FND_FILE.put_line(FND_FILE.log,'EAM_Downtime_PUB.LoadDowntime('
1260 || l_stmt_num
1261 || '): '
1262 || x_return_status
1263 || substr(SQLERRM,1,200));
1264 WHEN FND_API.g_exc_unexpected_error THEN
1265 Rollback to LoadDowntime_PUB; -- roll back data when unexpected errors happens
1266 x_return_status := FND_API.g_ret_sts_unexp_error;
1267 FND_FILE.put_line(FND_FILE.log,'EAM_Downtime_PUB.LoadDowntime('
1268 || l_stmt_num
1269 || '): '
1270 || x_return_status
1271 || substr(SQLERRM,1,200));
1272 WHEN others THEN
1273 Rollback to LoadDowntime_PUB; -- roll back data when unexpected errors happens
1274 x_return_status := FND_API.g_ret_sts_unexp_error;
1275 FND_FILE.put_line(FND_FILE.log,'EAM_Downtime_PUB.LoadDowntime('
1276 -- dbms_output.put_line('EAM_Downtime_PUB.LoadDowntime('
1277 || l_stmt_num
1278 || '): '
1279 || x_return_status
1280 || substr(SQLERRM,1,200));
1281
1282 END Load_Downtime;
1283
1284 ---------------------------------------------------------------------------
1285 PROCEDURE Purge_Downtime(
1286 p_api_version IN NUMBER,
1287 x_return_status OUT NOCOPY VARCHAR2,
1288 x_msg_count OUT NOCOPY NUMBER,
1289 x_msg_data OUT NOCOPY VARCHAR2,
1290 p_org_id IN NUMBER,
1291 p_simulation_set IN VARCHAR2) IS
1292
1293 l_api_name CONSTANT VARCHAR2(30) := 'PurgeDowntime';
1294 l_api_version CONSTANT NUMBER := 1.0;
1295
1296 l_msg_count NUMBER :=0;
1297 l_msg_data VARCHAR2(8000):= '';
1298
1299 l_stmt_num NUMBER := 0;
1300 l_old_maint_org_id NUMBER;
1301 l_old_asset_group_id NUMBER;
1302 l_old_asset_number NUMBER;
1303 i NUMBER;
1304 j NUMBER;
1305
1306
1307 BEGIN
1308 -- standard start of API savepoint
1309 SAVEPOINT PurgeDowntime_PUB;
1310
1311 -- standard call to check for call compatibility
1312 IF NOT FND_API.compatible_api_call (
1313 l_api_version,
1314 p_api_version,
1315 l_api_name,
1316 G_PKG_NAME ) then
1317 RAISE FND_API.g_exc_unexpected_error;
1318 END IF;
1319
1320 -- initialize api return status to success
1321 x_return_status := FND_API.g_ret_sts_success;
1322
1323 --API Body
1324 --Delete all previous equipment downtime rows in BRIC and BRC
1325 delete from bom_res_instance_changes
1326 where
1327 downtime_group_id is not null
1328 and simulation_set=p_simulation_set
1329 and department_id in
1330 (select department_id from bom_departments
1331 where
1332 organization_id=p_org_id);
1333
1334 delete from bom_resource_changes
1335 where
1336 downtime_group_id is not null
1337 and simulation_set=p_simulation_set
1338 and department_id in
1339 (select department_id from bom_departments
1340 where
1341 organization_id=p_org_id);
1342
1343
1344
1345 commit;
1346 EXCEPTION
1347 WHEN FND_API.g_exc_error THEN
1348 ROLLBACK to purgedowntime_pub;
1349 x_return_status := FND_API.g_ret_sts_error;
1350 FND_FILE.put_line(FND_FILE.log,'EAM_Downtime_PUB.PurgeDowntime('
1351 || l_stmt_num
1352 || '): '
1353 || x_return_status
1354 || substr(SQLERRM,1,200));
1355 WHEN FND_API.g_exc_unexpected_error THEN
1356 ROLLBACK to purgedowntime_pub;
1357 x_return_status := FND_API.g_ret_sts_unexp_error;
1358 FND_FILE.put_line(FND_FILE.log,'EAM_Downtime_PUB.PurgeDowntime('
1359 || l_stmt_num
1360 || '): '
1361 || x_return_status
1362 || substr(SQLERRM,1,200));
1363 WHEN others THEN
1364 ROLLBACK to purgedowntime_pub;
1365 x_return_status := FND_API.g_ret_sts_unexp_error;
1366 FND_FILE.put_line(FND_FILE.log,'EAM_Downtime_PUB.PurgeDowntime('
1367 || l_stmt_num
1368 || '): '
1369 || x_return_status
1370 || substr(SQLERRM,1,200));
1371
1372 END Purge_Downtime;
1373
1374 END EAM_Downtime_PUB;