[Home] [Help]
PACKAGE BODY: APPS.EAM_SCHED_BOTTOM_UP_PVT
Source
1 PACKAGE BODY EAM_SCHED_BOTTOM_UP_PVT AS
2 /* $Header: EAMVSBUB.pls 120.13.12010000.3 2008/10/17 07:57:01 smrsharm ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVSBUB.pls
11 --
12 -- DESCRIPTION
13 --
14 --
15 -- NOTES
16 --
17 -- HISTORY
18 -- 120.0 - Initial Creation
19 -- 120.1 - Modified call to various procedures wherein IN and OUT params were being passed as the same variable.
20 -- Added code to write into eAM debug file . Added review comment for updating WHO columns during DML
21 -- 120.2 - Added procedure update_instance_usage to update instance usage records when updating instance dates.
22 -- Also added code to check if record exists in woru when inserting an instance record in woru.
23 -- Added code to update usage record in WORU for a resource having multiple rows only when expanding the resource dates.
24 -- 120.3 - Changes for accounting period cursor check.
25 -- 120.4 - Accounting period check rectified.
26 -- 120.5 - Changes for instance usage records. removed commented and debug statements.
27 -- 120.6 - Changes to support shift work order functionality. Remove all usage records to be updated from database
28 -- and re-insert these records again.
29 -- Populate first_unit_completion_date and last_unit_start_date in WO table too. Needed for forms lock_row.
30 -- Moved code to sync up wori and wor with woru at the end of update_resource_usage procedure.
31 -- 3/8/2005 Prashant Kathotia Initial Creation
32 -- 08/15/2006 - Changes for Bug 5408720 - Anju Gupta
33 ***************************************************************************/
34
35 /*************************************************************************************************************************
36 * Procedure : update_resource
37 * Parameters IN :
38 p_curr_inst_rec
39 p_eam_res_tbl
40
41 * Parameters OUT NOCOPY:
42 x_eam_res_tbl
43 x_return_status
44 * Purpose : Procedure will propagate changes from instance level to resource level during
45 Bottom Up Scheduling.
46 ************************************************************************************************************************/
47 procedure update_resource( p_curr_inst_rec IN EAM_PROCESS_WO_PUB.eam_res_inst_rec_type,
48 p_eam_res_tbl IN EAM_PROCESS_WO_PUB.eam_res_tbl_type,
49 x_eam_res_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_tbl_type,
50 x_return_status OUT NOCOPY VARCHAR2 ) IS
51
52
53 l_resource_rec EAM_PROCESS_WO_PUB.eam_res_rec_type;
54 l_wip_id NUMBER ;
55 l_op_seq_num NUMBER ;
56 l_org_id NUMBER ;
57 l_res_seq_num NUMBER ;
58 l_inst_start_date DATE ;
59 l_inst_end_date DATE ;
60 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type ;
61 l_res_rec_found VARCHAR2(1) ;
62 l_change_date VARCHAR2(1) ;
63 l_res_start_date DATE;
64 l_res_end_date DATE;
65 l_return_status VARCHAR2(1) ;
66 l_eam_res_tbl_index NUMBER;
67 BEGIN
68
69 -- Initialize variables
70 l_wip_id := p_curr_inst_rec.wip_entity_id ;
71 l_op_seq_num := p_curr_inst_rec.operation_seq_num ;
72 l_org_id := p_curr_inst_rec.organization_id ;
73 l_res_seq_num := p_curr_inst_rec.resource_seq_num ;
74 l_inst_start_date := p_curr_inst_rec.start_date ;
75 l_inst_end_date := p_curr_inst_rec.completion_date ;
76 l_eam_res_tbl := p_eam_res_tbl ;
77 l_return_status := FND_API.G_RET_STS_SUCCESS;
78
79 x_return_status := l_return_status ;
80
81 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Inside update_resource ') ; END IF ;
82
83 l_eam_res_tbl_index := l_eam_res_tbl.FIRST;
84
85 WHILE l_eam_res_tbl_index IS NOT NULL LOOP
86
87 IF ( l_eam_res_tbl(l_eam_res_tbl_index).wip_entity_id = l_wip_id AND l_eam_res_tbl(l_eam_res_tbl_index).operation_seq_num = l_op_seq_num AND
88 l_eam_res_tbl(l_eam_res_tbl_index).resource_seq_num = l_res_seq_num) THEN
89
90 l_res_rec_found := 'Y';
91
92
93 IF ( l_inst_start_date < l_eam_res_tbl(l_eam_res_tbl_index).start_date ) THEN
94 l_eam_res_tbl(l_eam_res_tbl_index).start_date := l_inst_start_date ;
95 l_res_start_date := l_inst_start_date;
96 l_res_end_date := l_eam_res_tbl(l_eam_res_tbl_index).completion_date;
97 l_change_date := 'Y' ;
98 END IF;
99
100 IF ( l_inst_end_date > l_eam_res_tbl(l_eam_res_tbl_index).completion_date ) THEN
101 l_eam_res_tbl(l_eam_res_tbl_index).completion_date := l_inst_end_date ;
102 l_res_end_date := l_inst_end_date;
103 l_res_start_date := l_eam_res_tbl(l_eam_res_tbl_index).start_date;
104 l_change_date := 'Y' ;
105 END IF;
106
107 END IF;
108
109 l_eam_res_tbl_index := l_eam_res_tbl.NEXT(l_eam_res_tbl_index);
110
111 END LOOP; -- end loop through l_eam_res_tbl
112
113 IF ( NVL( l_res_rec_found, 'N') = 'N' )THEN
114
115 EAM_RES_UTILITY_PVT.Query_Row ( l_wip_id ,
116 l_org_id,
117 l_op_seq_num,
118 l_res_seq_num,
119 l_resource_rec,
120 l_return_status) ;
121
122 l_resource_rec.wip_entity_id := l_wip_id ;
123 l_resource_rec.organization_id := l_org_id ;
124 l_resource_rec.operation_seq_num := l_op_seq_num ;
125 l_resource_rec.resource_seq_num := l_res_seq_num ;
126
127 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
128 RAISE FND_API.G_EXC_ERROR ;
129 END IF;
130
131 l_resource_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
132 l_eam_res_tbl( l_eam_res_tbl.COUNT+1) := l_resource_rec ;
133 l_res_end_date := l_resource_rec.completion_date;
134 l_res_start_date := l_resource_rec.start_date;
135
136 IF ( l_res_start_date > l_inst_start_date) THEN
137 l_eam_res_tbl( l_eam_res_tbl.LAST).start_date := l_inst_start_date ;
138 l_res_start_date := l_inst_start_date;
139 l_change_date := 'Y' ;
140 END IF;
141
142 IF ( l_res_end_date < l_inst_end_date ) THEN
143 l_eam_res_tbl( l_eam_res_tbl.LAST).completion_date := l_inst_end_date;
144 l_res_end_date := l_inst_end_date;
145 l_change_date := 'Y';
146 END IF;
147
148 END IF;
149
150 IF ( NVL( l_change_date, 'N' ) = 'Y' ) THEN
151
152 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Updating WOR ') ; END IF ;
153
154 UPDATE wip_operation_resources
155 SET start_date = l_res_start_date ,
156 completion_date = l_res_end_date ,
157 last_update_date = sysdate ,
158 last_updated_by = FND_GLOBAL.user_id ,
159 creation_date = sysdate ,
160 created_by = FND_GLOBAL.user_id ,
161 last_update_login = FND_GLOBAL.login_id
162 WHERE wip_entity_id = l_wip_id
163 AND operation_seq_num = l_op_seq_num
164 AND resource_seq_num = l_res_seq_num;
165
166 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Updating WORU start date for resource') ; END IF ;
167
168 UPDATE wip_operation_resource_usage
169 SET start_date = l_res_start_date ,
170 last_update_date = sysdate ,
171 last_updated_by = FND_GLOBAL.user_id ,
172 creation_date = sysdate ,
173 created_by = FND_GLOBAL.user_id ,
174 last_update_login = FND_GLOBAL.login_id
175 WHERE wip_entity_id = l_wip_id
176 AND operation_seq_num = l_op_seq_num
177 AND resource_seq_num = l_res_seq_num
178 AND start_date = ( SELECT MIN(start_date)
179 FROM wip_operation_resource_usage
180 WHERE wip_entity_id = l_wip_id
181 AND operation_seq_num = l_op_seq_num
182 AND resource_seq_num = l_res_seq_num
183 AND instance_id IS NULL
184 AND serial_number IS NULL)
185 AND instance_id IS NULL
186 AND serial_number IS NULL;
187
188 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Updating WORU end date for resource') ; END IF ;
189
190 UPDATE wip_operation_resource_usage
191 SET completion_date = l_res_end_date ,
192 last_update_date = sysdate ,
193 last_updated_by = FND_GLOBAL.user_id ,
194 creation_date = sysdate ,
195 created_by = FND_GLOBAL.user_id ,
196 last_update_login = FND_GLOBAL.login_id
197 WHERE wip_entity_id = l_wip_id
198 AND operation_seq_num = l_op_seq_num
199 AND resource_seq_num = l_res_seq_num
200 AND completion_date = ( SELECT MAX(completion_date)
201 FROM wip_operation_resource_usage
202 WHERE wip_entity_id = l_wip_id
203 AND operation_seq_num = l_op_seq_num
204 AND resource_seq_num = l_res_seq_num
205 AND instance_id IS NULL
206 AND serial_number IS NULL)
207 AND instance_id IS NULL
208 AND serial_number IS NULL;
209
210 END IF ;
211
212 x_eam_res_tbl := l_eam_res_tbl;
213 x_return_status := l_return_status ;
214
215 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Exiting update_resource ') ; END IF ;
216 EXCEPTION
217 WHEN FND_API.G_EXC_ERROR THEN
218
219 x_return_status := l_return_status ;
220 END update_resource;
221
222
223 /*************************************************************************************************************************
224 * Procedure : update_operations
225 * Parameters IN :
226 p_curr_res_rec
227 p_eam_op_tbl
228
229 * Parameters OUT NOCOPY:
230 x_eam_op_tbl
231 x_return_status
232
233 * Purpose : Procedure will propagate changes from resource level to operations level during
234 Bottom Up Scheduling.
235 ************************************************************************************************************************/
236
237
238 procedure update_operations ( p_curr_res_rec IN EAM_PROCESS_WO_PUB.eam_res_rec_type,
239 p_eam_op_tbl IN EAM_PROCESS_WO_PUB.eam_op_tbl_type,
240 x_eam_op_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_op_tbl_type,
241 x_return_status OUT NOCOPY VARCHAR2 ) IS
242
243 l_operation_rec EAM_PROCESS_WO_PUB.eam_op_rec_type ;
244 l_wip_id NUMBER ;
245 l_org_id NUMBER ;
246 l_op_seq_num NUMBER ;
247 l_eam_op_tbl EAM_PROCESS_WO_PUB.eam_op_tbl_type ;
248 l_op_rec_found VARCHAR2(1);
249 l_res_start_date DATE ;
250 l_res_end_date DATE ;
251 l_op_start_date DATE;
252 l_op_end_date DATE;
253 l_change_date VARCHAR2(1);
254 l_return_status VARCHAR2(1) ;
255 l_eam_op_tbl_index NUMBER;
256
257 BEGIN
258
259 -- Initialize variables
260 l_wip_id := p_curr_res_rec.wip_entity_id ;
261 l_org_id := p_curr_res_rec.organization_id ;
262 l_op_seq_num := p_curr_res_rec.operation_seq_num ;
263 l_eam_op_tbl := p_eam_op_tbl ;
264
265 l_res_start_date := p_curr_res_rec.start_date;
266 l_res_end_date := p_curr_res_rec.completion_date;
267 l_return_status := FND_API.G_RET_STS_SUCCESS;
268
269
270 x_return_status := l_return_status;
271 l_eam_op_tbl_index := l_eam_op_tbl.FIRST;
272
273 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Inside update_operations ') ; END IF ;
274
275 WHILE l_eam_op_tbl_index IS NOT NULL LOOP
276
277 IF ( l_eam_op_tbl(l_eam_op_tbl_index).wip_entity_id = l_wip_id AND l_eam_op_tbl(l_eam_op_tbl_index).operation_seq_num = l_op_seq_num ) THEN
278
279 l_op_rec_found := 'Y';
280
281 IF ( l_res_start_date < l_eam_op_tbl(l_eam_op_tbl_index).start_date ) THEN
282 l_eam_op_tbl(l_eam_op_tbl_index).start_date := l_res_start_date ;
283 l_op_start_date := l_res_start_date;
284 l_op_end_date := l_eam_op_tbl(l_eam_op_tbl_index).completion_date ;
285 l_change_date := 'Y';
286 END IF;
287
288 IF ( l_res_end_date > l_eam_op_tbl(l_eam_op_tbl_index).completion_date ) THEN
289 l_eam_op_tbl(l_eam_op_tbl_index).completion_date := l_res_end_date ;
290 l_op_start_date := l_eam_op_tbl(l_eam_op_tbl_index).start_date ;
291 l_op_end_date := l_res_end_date ;
292 l_change_date := 'Y';
293 END IF;
294
295 END IF;
296
297 l_eam_op_tbl_index := l_eam_op_tbl.NEXT(l_eam_op_tbl_index);
298
299 END LOOP; -- end loop through l_eam_op_tbl
300
301 IF ( NVL( l_op_rec_found, 'N' ) = 'N' ) THEN
302
303 EAM_OP_UTILITY_PVT.query_row( l_wip_id ,
304 l_org_id ,
305 l_op_seq_num ,
306 l_operation_rec ,
307 l_return_status );
308
309 l_operation_rec.wip_entity_id := l_wip_id ;
310 l_operation_rec.organization_id := l_org_id ;
311 l_operation_rec.operation_seq_num := l_op_seq_num ;
312
313
314 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
315 RAISE FND_API.G_EXC_ERROR ;
316 END IF;
317
318 l_operation_rec.transaction_type := EAM_PROCESS_WO_PUB.G_OPR_UPDATE;
319 l_eam_op_tbl( l_eam_op_tbl.COUNT+1) := l_operation_rec;
320 l_op_start_date := l_operation_rec.start_date ;
321 l_op_end_date := l_operation_rec.completion_date ;
322
323 IF ( l_op_start_date > l_res_start_date) THEN
324 l_eam_op_tbl( l_eam_op_tbl.LAST).start_date := l_res_start_date ;
325 l_op_start_date := l_res_start_date;
326 l_change_date := 'Y' ;
327 END IF;
328
329 IF ( l_op_end_date < l_res_end_date ) THEN
330 l_eam_op_tbl( l_eam_op_tbl.LAST).completion_date := l_res_end_date;
331 l_op_end_date := l_res_end_date;
332 l_change_date := 'Y';
333 END IF;
334
335 END IF;
336
337 IF ( NVL( l_change_date, 'N' ) = 'Y' ) THEN
338
339 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Updating Wip_operations ') ; END IF ;
340
341 UPDATE wip_operations
342 SET first_unit_start_date = l_op_start_date,
343 first_unit_completion_date = l_op_end_date,
344 last_unit_start_date = l_op_start_date,
345 last_unit_completion_date = l_op_end_date ,
346 last_update_date = sysdate ,
347 last_updated_by = FND_GLOBAL.user_id ,
348 creation_date = sysdate ,
349 created_by = FND_GLOBAL.user_id ,
350 last_update_login = FND_GLOBAL.login_id
351 WHERE wip_entity_id = l_wip_id
352 AND operation_seq_num = l_op_seq_num ;
353 END IF ;
354
355 x_eam_op_tbl := l_eam_op_tbl;
356 x_return_status := l_return_status ;
357
358 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Exiting update_operations ') ; END IF ;
359 EXCEPTION
360 WHEN FND_API.G_EXC_ERROR THEN
361 x_return_status := l_return_status ;
362 END update_operations;
363
364
365 /*************************************************************************************************************************
366 * Procedure : update_workorder
367 * Parameters IN :
368 p_curr_op_rec
369 p_eam_wo_rec
370
371 * Parameters OUT NOCOPY:
372 x_eam_wo_rec
373 x_return_status
374
375 * Purpose : Procedure will propagate changes from operations level to work order level during
376 Bottom Up Scheduling.
377 ************************************************************************************************************************/
378
379
380
381 procedure update_workorder( p_curr_op_rec IN EAM_PROCESS_WO_PUB.eam_op_rec_type,
382 p_eam_wo_rec IN EAM_PROCESS_WO_PUB.eam_wo_rec_type,
383 x_eam_wo_rec OUT NOCOPY EAM_PROCESS_WO_PUB.eam_wo_rec_type,
384 x_return_status OUT NOCOPY VARCHAR2 ) IS
385
386 l_op_start_date DATE ;
387 l_op_end_date DATE ;
388 l_wo_start_date DATE ;
389 l_wo_end_date DATE ;
390 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type ;
391 l_wo_date_change VARCHAR2(1) ;
392 l_return_status VARCHAR2(1);
393 --l_wo_req_start_date DATE ;
394 --l_wo_due_date DATE ;
395
396 BEGIN
397 -- Initialize variables
398 l_op_start_date := p_curr_op_rec.start_date;
399 l_op_end_date := p_curr_op_rec.completion_date;
400 l_eam_wo_rec := p_eam_wo_rec;
401 l_return_status := FND_API.G_RET_STS_SUCCESS;
402
403 x_return_status := l_return_status ;
404
405 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Inside update_workorder ') ; END IF ;
406
407 IF ( l_eam_wo_rec.transaction_type IS NULL ) THEN -- query up from DB
408 EAM_WO_UTILITY_PVT.Query_Row ( p_curr_op_rec.wip_entity_id ,
409 p_curr_op_rec.organization_id ,
410 l_eam_wo_rec ,
411 l_return_status );
412
413 l_eam_wo_rec.wip_entity_id := p_curr_op_rec.wip_entity_id ;
414 l_eam_wo_rec.organization_id := p_curr_op_rec.organization_id ;
415
416 END IF;
417
418 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
419 RAISE FND_API.G_EXC_ERROR ;
420 END IF;
421
422 l_wo_start_date := l_eam_wo_rec.scheduled_start_date ;
423 l_wo_end_date := l_eam_wo_rec.scheduled_completion_date ;
424
425 IF ( l_eam_wo_rec.scheduled_start_date > l_op_start_date ) THEN
426 l_wo_start_date := l_op_start_date;
427 l_wo_date_change := 'Y';
428 END IF ;
429
430 IF ( l_eam_wo_rec.scheduled_completion_date < l_op_end_date ) THEN
431 l_wo_end_date := l_op_end_date;
432 l_wo_date_change := 'Y';
433 END IF ;
434
435 IF ( NVL( l_wo_date_change , 'N' ) = 'Y' ) THEN
436
437 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Updating WDJ') ; END IF ;
438
439 UPDATE wip_discrete_jobs
440 SET scheduled_start_date = l_wo_start_date,
441 scheduled_completion_date = l_wo_end_date ,
442 last_update_date = sysdate ,
443 last_updated_by = FND_GLOBAL.user_id ,
444 creation_date = sysdate ,
445 created_by = FND_GLOBAL.user_id ,
446 last_update_login = FND_GLOBAL.login_id
447 WHERE wip_entity_id = p_curr_op_rec.wip_entity_id
448 AND organization_id = p_curr_op_rec.organization_id ;
449
450
451 l_eam_wo_rec.scheduled_start_date := l_wo_start_date;
452 l_eam_wo_rec.scheduled_completion_date := l_wo_end_date;
453 END IF;
454
455 x_eam_wo_rec := l_eam_wo_rec;
456 x_return_status := l_return_status ;
457 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Exiting update_workorder') ; END IF ;
458
459 EXCEPTION
460 WHEN FND_API.G_EXC_ERROR THEN
461 x_return_status := l_return_status ;
462
463 END update_workorder;
464
465
466 /*************************************************************************************************************************
467 * Procedure : schedule_bottom_up_pvt
468 * Parameters IN : p_commit
469 p_wip_entity_id
470 p_org_id
471 p_woru_modified
472
473 * Parameters OUT NOCOPY:
474
475 x_return_status
476 x_message_name
477
478 * Purpose : Procedure will do Bottom Up Scheduling for a firm work order and for
479 DS Scheduled work orders
480
481 * History - Anju Gupta -
482 Modified for bug 5408720 - The procedure now only takes in the wip_entity_id and schedules the entire
483 structure of the work order, adjusting each level in a bottom-up fashion
484 ************************************************************************************************************************/
485
486
487 procedure schedule_bottom_up_pvt (
488 p_api_version_number IN NUMBER
489 , p_commit IN VARCHAR2
490 , p_wip_entity_id IN NUMBER
491 , p_org_id IN NUMBER
492 , p_woru_modified IN VARCHAR2
493 , x_return_status OUT NOCOPY VARCHAR2
494 , x_message_name OUT NOCOPY VARCHAR2
495 ) IS
496
497 CURSOR get_opresource_csr( c_wip_entity_id NUMBER) IS
498 SELECT start_date,
499 completion_date,
500 operation_seq_num,
501 resource_seq_num
502 FROM wip_operation_resources
503 WHERE wip_entity_id = c_wip_entity_id;
504
505 CURSOR get_inst_usage_min_date( c_wip_entity_id NUMBER, c_op_seq_num NUMBER , c_res_seq_num NUMBER ) IS
506 SELECT MIN(start_date) as min_inst_usg_date
507 FROM wip_operation_resource_usage
508 WHERE wip_entity_id = c_wip_entity_id
509 AND operation_seq_num = c_op_seq_num
510 AND resource_seq_num = c_res_seq_num
511 AND (instance_id IS NOT NULL OR serial_number IS not NULL) ;
512
513 CURSOR get_inst_usage_max_date( c_wip_entity_id NUMBER, c_op_seq_num NUMBER , c_res_seq_num NUMBER ) IS
514 SELECT MAX(completion_date) as max_inst_usg_date
515 FROM wip_operation_resource_usage
516 WHERE wip_entity_id = c_wip_entity_id
517 AND operation_seq_num = c_op_seq_num
518 AND resource_seq_num = c_res_seq_num
519 AND (instance_id is not null OR serial_number IS not NULL) ;
520
521 CURSOR get_woru_min_date( c_wip_entity_id NUMBER, c_op_seq_num NUMBER , c_res_seq_num NUMBER ,
522 c_instance_id NUMBER , c_serial_num VARCHAR2 ) IS
523 SELECT MIN(start_date) as min_inst_usg_date
524 FROM wip_operation_resource_usage
525 WHERE wip_entity_id = c_wip_entity_id
526 AND operation_seq_num = c_op_seq_num
527 AND resource_seq_num = c_res_seq_num
528 AND instance_id = c_instance_id
529 AND ( serial_number IS NULL OR serial_number = c_serial_num);
530
531 CURSOR get_woru_max_date( c_wip_entity_id NUMBER, c_op_seq_num NUMBER , c_res_seq_num NUMBER ,
532 c_instance_id NUMBER , c_serial_num VARCHAR2 ) IS
533 SELECT MAX(completion_date) as max_inst_usg_date
534 FROM wip_operation_resource_usage
535 WHERE wip_entity_id = c_wip_entity_id
536 AND operation_seq_num = c_op_seq_num
537 AND resource_seq_num = c_res_seq_num
538 AND instance_id = c_instance_id
539 AND ( serial_number IS NULL OR serial_number = c_serial_num);
540
541 CURSOR get_res_usage_min_date( c_wip_entity_id NUMBER, c_op_seq_num NUMBER , c_res_seq_num NUMBER ) IS
542 SELECT MIN(start_date) as min_res_usg_date
543 FROM wip_operation_resource_usage
544 WHERE wip_entity_id = c_wip_entity_id
545 AND operation_seq_num = c_op_seq_num
546 AND resource_seq_num = c_res_seq_num
547 AND instance_id IS NULL
548 AND serial_number IS NULL ;
549
550 CURSOR get_res_usage_max_date( c_wip_entity_id NUMBER, c_op_seq_num NUMBER , c_res_seq_num NUMBER ) IS
551 SELECT MAX(completion_date) as max_inst_usg_date
552 FROM wip_operation_resource_usage
553 WHERE wip_entity_id = c_wip_entity_id
554 AND operation_seq_num = c_op_seq_num
555 AND resource_seq_num = c_res_seq_num
556 AND instance_id IS NULL
557 AND serial_number IS NULL ;
558
559 CURSOR get_instdates( c_wip_entity_id NUMBER, c_op_seq_num NUMBER , c_res_seq_num NUMBER ) IS
560 SELECT start_date, completion_date, instance_id, serial_number
561 FROM wip_operation_resource_usage
562 WHERE wip_entity_id = c_wip_entity_id
563 AND operation_seq_num = c_op_seq_num
564 AND resource_seq_num = c_res_seq_num;
565
566 CURSOR get_op_dates_csr( p_wip_entity_id NUMBER) IS
567 SELECT operation_seq_num, first_unit_start_date,
568 last_unit_completion_date
569 FROM wip_operations
570 WHERE wip_entity_id = p_wip_entity_id;
571
572 CURSOR get_wo_dates_csr( p_wip_entity_id NUMBER ) IS
573 SELECT scheduled_start_date,
574 scheduled_completion_date
575 FROM wip_discrete_jobs
576 WHERE wip_entity_id = p_wip_entity_id;
577
578 /* Define local variables */
579 l_return_status varchar2(1);
580 c_resusagemin_date Date;
581 c_resusagemax_date Date;
582 c_instusage_min_date Date;
583 c_instusage_max_date Date;
584 c_instusagemin_date Date;
585 c_instusagemax_date Date;
586 l_min_res_date Date;
587 l_max_res_date Date;
588 l_min_date Date;
589 l_max_date Date;
590 l_scheduled_start_date Date;
591 l_scheduled_completion_date Date;
592
593
594 BEGIN
595 -- Initialize variables
596
597 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Entering schedule_bottom_up_pvt ') ; END IF ;
598
599 SAVEPOINT SCHED_BOTTOM_UP;
600
601 l_return_status := FND_API.G_RET_STS_SUCCESS;
602 x_return_status := l_return_status ;
603
604 --/* Get the various operations and associated resources in the WO
605 FOR c_opresource_rec IN get_opresource_csr(p_wip_entity_id) LOOP
606
607 --/* Get WORU Dates for the Resource Usage
608 open get_res_usage_min_date( p_wip_entity_id, c_opresource_rec.operation_seq_num , c_opresource_rec.resource_seq_num );
609 FETCH get_res_usage_min_date INTO c_resusagemin_date;
610 close get_res_usage_min_date;
611
612 open get_res_usage_max_date( p_wip_entity_id, c_opresource_rec.operation_seq_num , c_opresource_rec.resource_seq_num );
613 FETCH get_res_usage_max_date INTO c_resusagemax_date;
614 close get_res_usage_max_date;
615
616 --/* Get WORU Dates for the Instance Usage
617 open get_inst_usage_min_date( p_wip_entity_id, c_opresource_rec.operation_seq_num , c_opresource_rec.resource_seq_num );
618 FETCH get_inst_usage_min_date INTO c_instusage_min_date;
619 close get_inst_usage_min_date;
620
621 open get_inst_usage_max_date( p_wip_entity_id, c_opresource_rec.operation_seq_num , c_opresource_rec.resource_seq_num );
622 FETCH get_inst_usage_max_date INTO c_instusage_max_date;
623 close get_inst_usage_max_date;
624
625 IF p_woru_modified = 'Y' THEN
626
627 --/* Adjust WORU and WORU' dates
628 IF c_instusage_min_date < c_resusagemin_date THEN
629 UPDATE wip_operation_resource_usage
630 SET start_date = c_instusage_min_date
631 WHERE wip_entity_id = p_wip_entity_id
632 AND operation_seq_num = c_opresource_rec.operation_seq_num
633 AND resource_seq_num = c_opresource_rec.resource_seq_num
634 AND instance_id IS NULL
635 AND serial_number IS NULL
636 AND rownum = 1;
637
638 c_resusagemin_date := c_instusage_min_date;
639 --l_resourcemin_expanded = 1;
640 END IF;
641
642 IF c_instusage_max_date > c_resusagemax_date THEN
643 UPDATE wip_operation_resource_usage
644 SET completion_date = c_instusage_max_date
645 WHERE wip_entity_id = p_wip_entity_id
646 AND operation_seq_num = c_opresource_rec.operation_seq_num
647 AND resource_seq_num = c_opresource_rec.resource_seq_num
648 AND instance_id IS NULL
649 AND serial_number IS NULL
650 AND rownum = 1;
651
652 c_resusagemax_date := c_instusage_max_date;
653 --l_resourcemax_expanded = 1;
654 END IF;
655
656 --/* Adjust WORU' and WORI dates
657 FOR c_instdates_rec IN get_instdates(p_wip_entity_id , c_opresource_rec.operation_seq_num, c_opresource_rec.resource_seq_num )
658 LOOP
659 open get_woru_min_date(p_wip_entity_id, c_opresource_rec.operation_seq_num , c_opresource_rec.resource_seq_num,
660 c_instdates_rec.instance_id, c_instdates_rec.serial_number );
661 FETCH get_woru_min_date INTO c_instusagemin_date;
662 close get_woru_min_date;
663
664 open get_woru_max_date(p_wip_entity_id, c_opresource_rec.operation_seq_num , c_opresource_rec.resource_seq_num,
665 c_instdates_rec.instance_id, c_instdates_rec.serial_number );
666 FETCH get_woru_max_date INTO c_instusagemax_date;
667 close get_woru_max_date;
668
669 IF c_instusagemin_date < c_instdates_rec.start_date THEN
670 UPDATE wip_op_resource_instances
671 SET start_date = c_instusagemin_date,
672 last_update_date = sysdate ,
673 last_updated_by = FND_GLOBAL.user_id ,
674 last_update_login = FND_GLOBAL.login_id
675 WHERE wip_entity_id = p_wip_entity_id
676 AND operation_seq_num = c_opresource_rec.operation_seq_num
677 AND resource_seq_num = c_opresource_rec.resource_seq_num
678 AND instance_id = c_instdates_rec.instance_id
679 AND (serial_number IS NULL OR (serial_number = c_instdates_rec.serial_number));
680 END IF;
681
682 IF c_instusagemax_date > c_instdates_rec.completion_date THEN
683 UPDATE wip_op_resource_instances
684 SET completion_date = c_instusagemax_date,
685 last_update_date = sysdate ,
686 last_updated_by = FND_GLOBAL.user_id ,
687 last_update_login = FND_GLOBAL.login_id
688 WHERE wip_entity_id = p_wip_entity_id
689 AND operation_seq_num = c_opresource_rec.operation_seq_num
690 AND resource_seq_num = c_opresource_rec.resource_seq_num
691 AND instance_id = c_instdates_rec.instance_id
692 AND (serial_number IS NULL OR (serial_number = c_instdates_rec.serial_number));
693 END IF;
694
695 END LOOP;
696
697
698 --/* Adjust WORU and WOR dates
699 IF c_resusagemin_date < c_opresource_rec.start_date THEN
700 UPDATE wip_operation_resources
701 SET start_date = c_resusagemin_date,
702 last_update_date = sysdate ,
703 last_updated_by = FND_GLOBAL.user_id ,
704 last_update_login = FND_GLOBAL.login_id
705 WHERE wip_entity_id = p_wip_entity_id
706 AND operation_seq_num = c_opresource_rec.operation_seq_num
707 AND organization_id = p_org_id
708 AND resource_seq_num = c_opresource_rec.resource_seq_num ;
709
710
711 END IF;
712
713 IF c_resusagemax_date > c_opresource_rec.completion_date THEN
714 UPDATE wip_operation_resources
715 SET completion_date = c_resusagemax_date,
716 last_update_date = sysdate ,
717 last_updated_by = FND_GLOBAL.user_id ,
718 last_update_login = FND_GLOBAL.login_id
719 WHERE wip_entity_id = p_wip_entity_id
720 AND operation_seq_num = c_opresource_rec.operation_seq_num
721 AND organization_id = p_org_id
722 AND resource_seq_num = c_opresource_rec.resource_seq_num ;
723
724 END IF;
725
726 END IF;
727
728 END LOOP;
729
730 --/* Adjust WOR and WO dates
731
732 FOR c_operation_rec IN get_op_dates_csr( p_wip_entity_id) LOOP
733 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
734 EAM_ERROR_MESSAGE_PVT.Write_Debug(' Adjusting operation dates ') ;
735 EAM_ERROR_MESSAGE_PVT.Write_Debug(' op date ' || c_operation_rec.last_unit_completion_date) ;
736 END IF ;
737
738 select min(start_date), max(completion_date)
739 into l_min_res_date, l_max_res_date
740 from wip_operation_resources
741 where wip_entity_id = p_wip_entity_id
742 and operation_seq_num = c_operation_rec.operation_seq_num;
743
744 IF l_min_res_date <> c_operation_rec.first_unit_start_date THEN /*Bug 7336817*/
745 UPDATE wip_operations
746 SET first_unit_start_date = l_min_res_date,
747 last_unit_start_date = l_min_res_date,
748 last_update_date = sysdate ,
749 last_updated_by = FND_GLOBAL.user_id ,
750 last_update_login = FND_GLOBAL.login_id
751 WHERE wip_entity_id = p_wip_entity_id
752 AND operation_seq_num = c_operation_rec.operation_seq_num ;
753
754 END IF;
755
756 IF l_max_res_date <> c_operation_rec.last_unit_completion_date THEN /*Bug 7336817*/
757 UPDATE wip_operations
758 SET first_unit_completion_date = l_max_res_date ,
759 last_unit_completion_date = l_max_res_date ,
760 last_update_date = sysdate ,
761 last_updated_by = FND_GLOBAL.user_id ,
762 last_update_login = FND_GLOBAL.login_id
763 WHERE wip_entity_id = p_wip_entity_id
764 AND operation_seq_num = c_operation_rec.operation_seq_num ;
765
766 END IF;
767
768 END LOOP;
769
770 --/* Adjust WO and WDJ dates
771 OPEN get_wo_dates_csr( p_wip_entity_id);
772 FETCH get_wo_dates_csr into l_scheduled_start_date, l_scheduled_completion_date;
773 CLOSE get_wo_dates_csr;
774
775 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
776 EAM_ERROR_MESSAGE_PVT.Write_Debug(' Adjusting WO dates ') ;
777 END IF ;
778
779 select min(first_unit_start_date), max(last_unit_completion_date)
780 into l_min_date, l_max_date
781 from wip_operations
782 where wip_entity_id = p_wip_entity_id;
783
784 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
785 EAM_ERROR_MESSAGE_PVT.Write_Debug(' op start dates ' || l_min_date) ;
786 EAM_ERROR_MESSAGE_PVT.Write_Debug(' op end dates ' || l_max_date) ;
787 EAM_ERROR_MESSAGE_PVT.Write_Debug(' wo end dates ' || l_scheduled_completion_date) ;
788 END IF ;
789
790 IF l_min_date < l_scheduled_start_date THEN
791 UPDATE wip_discrete_jobs
792 SET scheduled_start_date = l_min_date,
793 last_update_date = sysdate ,
794 last_updated_by = FND_GLOBAL.user_id ,
795 last_update_login = FND_GLOBAL.login_id
796 WHERE wip_entity_id = p_wip_entity_id
797 AND organization_id = p_org_id ;
798
799 END IF;
800
801 IF l_max_date > l_scheduled_completion_date THEN
802 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN
803 EAM_ERROR_MESSAGE_PVT.Write_Debug(' Updating WO completion date ') ;
804 END IF ;
805
806 UPDATE wip_discrete_jobs
807 SET scheduled_completion_date = l_max_date,
808 last_update_date = sysdate ,
809 last_updated_by = FND_GLOBAL.user_id ,
810 last_update_login = FND_GLOBAL.login_id
811 WHERE wip_entity_id = p_wip_entity_id
812 AND organization_id = p_org_id ;
813
814 END IF;
815
816
817 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Exiting schedule_bottom_up_pvt ') ; END IF ;
818
819 EXCEPTION
820
821 WHEN FND_API.G_EXC_ERROR THEN
822 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Error occured in schedule_bottom_up_pvt API ') ; END IF ;
823 x_return_status := FND_API.G_RET_STS_ERROR ;
824 x_message_name := ' EAM_SCHED_BOTTOMUP_ERR';
825
826 END schedule_bottom_up_pvt ;
827
828
829 /*************************************************************************************************************************
830 * Procedure : insert_into_woru
831 * Parameters IN : p_eam_res_usage_rec
832 * Parameters OUT : x_return_status
833 * Purpose : Inserts a resource usage record in wip_operation_resource_usage table
834 ************************************************************************************************************************/
835
836 procedure insert_into_woru ( p_eam_res_usage_rec IN EAM_PROCESS_WO_PUB.eam_res_usage_rec_type,
837 x_return_status OUT NOCOPY VARCHAR2 ) IS
838
839 CURSOR res_usage_rec_check_csr IS
840 SELECT 1
841 FROM WIP_OPERATION_RESOURCE_USAGE
842 WHERE wip_entity_id = p_eam_res_usage_rec.wip_entity_id
843 AND operation_seq_num = p_eam_res_usage_rec.operation_seq_num
844 AND resource_seq_num = p_eam_res_usage_rec.resource_seq_num
845 AND start_date = p_eam_res_usage_rec.start_date
846 AND completion_date = p_eam_res_usage_rec.completion_date
847 AND instance_id IS NULL
848 AND serial_number IS NULL ;
849
850 CURSOR inst_usage_rec_check_csr IS
851 SELECT 1
852 FROM WIP_OPERATION_RESOURCE_USAGE
853 WHERE wip_entity_id = p_eam_res_usage_rec.wip_entity_id
854 AND operation_seq_num = p_eam_res_usage_rec.operation_seq_num
855 AND resource_seq_num = p_eam_res_usage_rec.resource_seq_num
856 AND start_date = p_eam_res_usage_rec.start_date
857 AND completion_date = p_eam_res_usage_rec.completion_date
858 AND instance_id = p_eam_res_usage_rec.instance_id
859 AND ( serial_number IS NULL OR serial_number = p_eam_res_usage_rec.serial_number );
860
861 l_rec_exists NUMBER;
862 l_return_status VARCHAR2(1) ;
863
864 BEGIN
865
866 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Inside insert_into_woru' ) ; END IF ;
867
868 l_return_status := FND_API.G_RET_STS_SUCCESS ;
869 x_return_status := l_return_status ;
870 l_rec_exists := 2 ;
871
872 IF ( p_eam_res_usage_rec.instance_id IS NULL ) THEN -- ckeck if recource record has already been inserted
873 OPEN res_usage_rec_check_csr;
874 FETCH res_usage_rec_check_csr INTO l_rec_exists;
875 IF (res_usage_rec_check_csr%NOTFOUND) THEN
876 l_rec_exists := 0;
877 END IF;
878 CLOSE res_usage_rec_check_csr;
879 ELSE
880 OPEN inst_usage_rec_check_csr;
881 FETCH inst_usage_rec_check_csr INTO l_rec_exists;
882 IF (inst_usage_rec_check_csr%NOTFOUND) THEN
883 l_rec_exists := 0;
884 END IF;
885 CLOSE inst_usage_rec_check_csr;
886 END IF ;
887
888 IF ( l_rec_exists = 0 ) THEN
889
890 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Insert record in WORU ' ) ; END IF ;
891
892 BEGIN
893 INSERT INTO WIP_OPERATION_RESOURCE_USAGE
894 ( wip_entity_id
895 , operation_seq_num
896 , resource_seq_num
897 , organization_id
898 , start_date
899 , completion_date
900 , assigned_units
901 , instance_id
902 , serial_number
903 , last_update_date
904 , last_updated_by
905 , creation_date
906 , created_by
907 , last_update_login
908 , request_id
909 , program_application_id
910 , program_id
911 , program_update_date)
912 VALUES
913 ( p_eam_res_usage_rec.wip_entity_id
914 , p_eam_res_usage_rec.operation_seq_num
915 , p_eam_res_usage_rec.resource_seq_num
916 , p_eam_res_usage_rec.organization_id
917 , p_eam_res_usage_rec.start_date
918 , p_eam_res_usage_rec.completion_date
919 , NVL( p_eam_res_usage_rec.assigned_units , 1 )
920 , p_eam_res_usage_rec.instance_id
921 , p_eam_res_usage_rec.serial_number
922 , SYSDATE
923 , FND_GLOBAL.user_id
924 , SYSDATE
925 , FND_GLOBAL.user_id
926 , FND_GLOBAL.login_id
927 , p_eam_res_usage_rec.request_id
928 , p_eam_res_usage_rec.program_application_id
929 , p_eam_res_usage_rec.program_id
930 , SYSDATE);
931
932 EXCEPTION WHEN OTHERS THEN
933 l_return_status := FND_API.G_RET_STS_ERROR ;
934 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Error occurred ' ||SQLERRM ) ; END IF ;
935 END ;
936
937 END IF; -- end of l_rec_exists
938
939 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Exiting insert_into_woru with status ' || l_return_status ) ; END IF ;
940 x_return_status := l_return_status ;
941
942 END insert_into_woru;
943
944 /*************************************************************************************************************************
945 * Procedure : update_woru
946 * Parameters IN : p_eam_res_usage_rec
947 * Purpose : Table Handler :- Updates a resource usage record in wip_operation_resource_usage table
948 ************************************************************************************************************************/
949
950
951 procedure update_woru( p_eam_res_usage_rec IN EAM_PROCESS_WO_PUB.eam_res_usage_rec_type) IS
952
953 BEGIN
954 IF p_eam_res_usage_rec.instance_id IS NULL THEN
955
956 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Inside update_woru: Updating resource record ' ) ; END IF ;
957
958 UPDATE WIP_OPERATION_RESOURCE_USAGE
959 SET start_date = p_eam_res_usage_rec.start_date ,
960 completion_date = p_eam_res_usage_rec.completion_date ,
961 last_update_date = sysdate ,
962 last_updated_by = FND_GLOBAL.user_id ,
963 creation_date = sysdate ,
964 created_by = FND_GLOBAL.user_id ,
965 last_update_login = FND_GLOBAL.login_id
966 WHERE wip_entity_id = p_eam_res_usage_rec.wip_entity_id
967 AND operation_seq_num = p_eam_res_usage_rec.operation_seq_num
968 AND organization_id = p_eam_res_usage_rec.organization_id
969 AND resource_seq_num = p_eam_res_usage_rec.resource_seq_num
970 AND start_date = p_eam_res_usage_rec.old_start_date
971 AND completion_date = p_eam_res_usage_rec.old_completion_date
972 AND instance_id IS NULL ;
973 ELSE
974 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Inside update_woru: Updating resource Instance record ' ) ; END IF ;
975
976 UPDATE WIP_OPERATION_RESOURCE_USAGE
977 SET start_date = p_eam_res_usage_rec.start_date ,
978 completion_date = p_eam_res_usage_rec.completion_date ,
979 last_update_date = sysdate ,
980 last_updated_by = FND_GLOBAL.user_id ,
981 creation_date = sysdate ,
982 created_by = FND_GLOBAL.user_id ,
983 last_update_login = FND_GLOBAL.login_id
984 WHERE wip_entity_id = p_eam_res_usage_rec.wip_entity_id
985 AND operation_seq_num = p_eam_res_usage_rec.operation_seq_num
986 AND organization_id = p_eam_res_usage_rec.organization_id
987 AND resource_seq_num = p_eam_res_usage_rec.resource_seq_num
988 AND start_date = p_eam_res_usage_rec.old_start_date
989 AND completion_date = p_eam_res_usage_rec.old_completion_date
990 AND instance_id = p_eam_res_usage_rec.instance_id
991 AND (serial_number IS NULL OR (serial_number = p_eam_res_usage_rec.serial_number));
992 END IF;
993
994 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Exiting update_woru' ) ; END IF ;
995 END update_woru ;
996
997 /*************************************************************************************************************************
998 * Procedure : delete_from_woru
999 * Parameters IN : p_eam_res_usage_rec
1000 * Purpose : Table Handler :- Deletes a resource usage record in wip_operation_resource_usage table
1001 ************************************************************************************************************************/
1002
1003 procedure delete_from_woru( p_eam_res_usage_rec IN EAM_PROCESS_WO_PUB.eam_res_usage_rec_type ) IS
1004 l_count NUMBER;
1005 BEGIN
1006
1007 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Inside delete_from_woru' ) ; END IF ;
1008
1009 DELETE FROM WIP_OPERATION_RESOURCE_USAGE
1010 WHERE wip_entity_id = p_eam_res_usage_rec.wip_entity_id
1011 AND operation_seq_num = p_eam_res_usage_rec.operation_seq_num
1012 AND organization_id = p_eam_res_usage_rec.organization_id
1013 AND resource_seq_num = p_eam_res_usage_rec.resource_seq_num
1014 AND start_date = p_eam_res_usage_rec.start_date
1015 AND completion_date = p_eam_res_usage_rec.completion_date
1016 AND instance_id = p_eam_res_usage_rec.instance_id
1017 AND (serial_number IS NULL OR (serial_number = p_eam_res_usage_rec.serial_number));
1018 --check if no records in woru
1019
1020 SELECT count(*)
1021 INTO l_count
1022 FROM wip_operation_resource_usage
1023 WHERE wip_entity_id = p_eam_res_usage_rec.wip_entity_id
1024 AND operation_seq_num =p_eam_res_usage_rec.operation_seq_num
1025 AND organization_id = p_eam_res_usage_rec.organization_id
1026 AND resource_seq_num = p_eam_res_usage_rec.resource_seq_num
1027 AND instance_id = p_eam_res_usage_rec.instance_id
1028 AND (serial_number IS NULL OR (serial_number = p_eam_res_usage_rec.serial_number));
1029
1030
1031
1032 -- If there are no rows in WORU for an employee then delete from WORI also
1033
1034 IF (l_count=0 ) THEN
1035
1036 DELETE FROM wip_op_resource_instances
1037 WHERE wip_entity_id =p_eam_res_usage_rec.wip_entity_id
1038 AND operation_seq_num = p_eam_res_usage_rec.operation_seq_num
1039 AND organization_id = p_eam_res_usage_rec.organization_id
1040 AND resource_seq_num =p_eam_res_usage_rec.resource_seq_num
1041 AND instance_id = p_eam_res_usage_rec.instance_id
1042 AND (serial_number IS NULL OR (serial_number = p_eam_res_usage_rec.serial_number));
1043 END IF;
1044
1045 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Exiting delete_from_woru' ) ; END IF ;
1046 END delete_from_woru ;
1047
1048
1049 /*************************************************************************************************************************
1050 * Procedure : update_wori
1051 * Parameters IN : p_eam_res_usage_rec
1052 p_eam_res_inst_tbl
1053 * Parameters OUT : x_eam_res_inst_tbl
1054 x_return_status
1055 * Purpose : Updates/Deletes record from wip_op_resource_instances table . Returns changed
1056 instance records in x_eam_res_inst_tbl.
1057 ************************************************************************************************************************/
1058
1059 procedure update_wori ( p_eam_res_usage_rec IN EAM_PROCESS_WO_PUB.eam_res_usage_rec_type
1060 ,p_eam_res_inst_tbl IN EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type
1061 ,x_eam_res_inst_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type
1062 ,x_return_status OUT NOCOPY VARCHAR ) IS
1063
1064 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type ;
1065 l_eam_res_inst_rec EAM_PROCESS_WO_PUB.eam_res_inst_rec_type ;
1066 l_min_start_date DATE;
1067 l_max_completion_date DATE;
1068 l_wip_entity_id NUMBER ;
1069 l_operation_seq_num NUMBER ;
1070 l_res_seq_num NUMBER ;
1071 l_instance_id NUMBER ;
1072 l_org_id NUMBER ;
1073 l_serial_number VARCHAR2(80);
1074 l_found VARCHAR2(1) ;
1075 l_return_status VARCHAR2(1) ;
1076 l_update VARCHAR2(1) ;
1077 l_eam_res_inst_tbl_index NUMBER;
1078 l_count NUMBER;
1079
1080 BEGIN
1081 -- Initialize variables
1082 l_eam_res_inst_tbl := p_eam_res_inst_tbl;
1083 l_wip_entity_id := p_eam_res_usage_rec.wip_entity_id;
1084 l_operation_seq_num := p_eam_res_usage_rec.operation_seq_num;
1085 l_res_seq_num := p_eam_res_usage_rec.resource_seq_num;
1086 l_instance_id := p_eam_res_usage_rec.instance_id;
1087 l_serial_number := p_eam_res_usage_rec.serial_number;
1088 l_org_id := p_eam_res_usage_rec.organization_id;
1089 l_found := 'N';
1090 l_return_status := FND_API.G_RET_STS_SUCCESS;
1091
1092 x_return_status := l_return_status;
1093 x_eam_res_inst_tbl := l_eam_res_inst_tbl;
1094
1095 -- place check for no rows returned in SELECT .This may occur when last record of woru gets deleted !
1096
1097 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Inside update_wori ' ) ; END IF ;
1098
1099
1100 SELECT count(*)
1101 INTO l_count
1102 FROM wip_operation_resource_usage
1103 WHERE wip_entity_id = p_eam_res_usage_rec.wip_entity_id
1104 AND operation_seq_num =p_eam_res_usage_rec.operation_seq_num
1105 AND organization_id = p_eam_res_usage_rec.organization_id
1106 AND resource_seq_num = p_eam_res_usage_rec.resource_seq_num
1107 AND instance_id = p_eam_res_usage_rec.instance_id
1108 AND (serial_number IS NULL OR (serial_number = p_eam_res_usage_rec.serial_number));
1109
1110 IF (l_count=0) THEN
1111 return;
1112 END IF;
1113
1114 SELECT min(start_date), max(completion_date)
1115 INTO l_min_start_date,l_max_completion_date
1116 FROM wip_operation_resource_usage
1117 WHERE wip_entity_id = l_wip_entity_id
1118 AND operation_seq_num = l_operation_seq_num
1119 AND organization_id = l_org_id
1120 AND resource_seq_num = l_res_seq_num
1121 AND instance_id = l_instance_id
1122 AND (serial_number IS NULL OR (serial_number = l_serial_number));
1123
1124
1125 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Update WORI' ) ; END IF ;
1126
1127
1128 UPDATE wip_op_resource_instances
1129 SET start_date = l_min_start_date,
1130 completion_date = l_max_completion_date ,
1131 last_update_date = sysdate ,
1132 last_updated_by = FND_GLOBAL.user_id ,
1133 creation_date = sysdate ,
1134 created_by = FND_GLOBAL.user_id ,
1135 last_update_login = FND_GLOBAL.login_id
1136 WHERE wip_entity_id = l_wip_entity_id
1137 AND operation_seq_num = l_operation_seq_num
1138 AND organization_id = l_org_id
1139 AND resource_seq_num = l_res_seq_num
1140 AND instance_id = l_instance_id
1141 AND (serial_number IS NULL OR (serial_number = l_serial_number));
1142
1143 -- now update the pl/sql table for instances.
1144
1145 l_eam_res_inst_tbl_index := l_eam_res_inst_tbl.FIRST;
1146
1147
1148 WHILE l_eam_res_inst_tbl_index IS NOT NULL LOOP
1149
1150 IF ( l_eam_res_inst_tbl(l_eam_res_inst_tbl_index).wip_entity_id = l_wip_entity_id AND
1151 l_eam_res_inst_tbl(l_eam_res_inst_tbl_index).organization_id = l_org_id AND
1152 l_eam_res_inst_tbl(l_eam_res_inst_tbl_index).operation_seq_num = l_operation_seq_num AND
1153 l_eam_res_inst_tbl(l_eam_res_inst_tbl_index).resource_seq_num = l_res_seq_num AND
1154 l_eam_res_inst_tbl(l_eam_res_inst_tbl_index).instance_id = l_instance_id AND
1155 (l_eam_res_inst_tbl(l_eam_res_inst_tbl_index).serial_number IS NULL
1156 OR (l_eam_res_inst_tbl(l_eam_res_inst_tbl_index).serial_number = l_serial_number))
1157 ) THEN
1158
1159 l_found := 'Y';
1160 l_eam_res_inst_tbl(l_eam_res_inst_tbl_index).start_date := l_min_start_date ;
1161 l_eam_res_inst_tbl(l_eam_res_inst_tbl_index).completion_date := l_max_completion_date ;
1162 END IF;
1163
1164 l_eam_res_inst_tbl_index := l_eam_res_inst_tbl.NEXT( l_eam_res_inst_tbl_index);
1165
1166 END LOOP;
1167
1168 IF l_found = 'N' THEN -- query up from DB
1169
1170
1171 EAM_RES_INST_UTILITY_PVT.Query_Row( l_wip_entity_id
1172 , l_org_id
1173 , l_operation_seq_num
1174 , l_res_seq_num
1175 , l_instance_id
1176 , l_serial_number
1177 , l_eam_res_inst_rec
1178 , l_return_status );
1179
1180 l_eam_res_inst_rec.wip_entity_id := l_wip_entity_id ;
1181 l_eam_res_inst_rec.organization_id := l_org_id ;
1182 l_eam_res_inst_rec.operation_seq_num := l_operation_seq_num ;
1183 l_eam_res_inst_rec.resource_seq_num := l_res_seq_num ;
1184 l_eam_res_inst_rec.instance_id := l_instance_id ;
1185 l_eam_res_inst_rec.serial_number := l_serial_number;
1186
1187 IF ( l_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1188 l_eam_res_inst_rec.start_date := l_min_start_date;
1189 l_eam_res_inst_rec.completion_date := l_max_completion_date;
1190 l_eam_res_inst_tbl( l_eam_res_inst_tbl.COUNT + 1) := l_eam_res_inst_rec;
1191 ELSE
1192 RAISE FND_API.G_EXC_ERROR;
1193 END IF;
1194
1195 END IF; -- end of check for l_found
1196
1197
1198
1199 x_return_status := l_return_status;
1200 x_eam_res_inst_tbl := l_eam_res_inst_tbl;
1201
1202 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Exiting update_wori ' ) ; END IF ;
1203
1204 EXCEPTION WHEN FND_API.G_EXC_ERROR THEN
1205 x_return_status := l_return_status;
1206 END update_wori;
1207
1208 /*************************************************************************************************************************
1209 * Procedure : update_wor
1210 * Parameters IN : p_eam_res_usage_rec
1211 p_eam_res_tbl
1212 * Parameters OUT : x_eam_res_tbl
1213 x_return_status
1214 * Purpose : Updates/Deletes record from wip_op_resource_instances table . Returns changed
1215 instance records in x_eam_res_tbl.
1216 ************************************************************************************************************************/
1217
1218 procedure update_wor ( p_eam_res_usage_rec IN EAM_PROCESS_WO_PUB.eam_res_usage_rec_type
1219 ,p_eam_res_tbl IN EAM_PROCESS_WO_PUB.eam_res_tbl_type
1220 ,x_eam_res_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_tbl_type
1221 ,x_return_status OUT NOCOPY VARCHAR ) IS
1222
1223 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type ;
1224 l_resource_rec EAM_PROCESS_WO_PUB.eam_res_rec_type ;
1225 l_min_start_date DATE;
1226 l_max_completion_date DATE;
1227 l_wip_entity_id NUMBER ;
1228 l_operation_seq_num NUMBER ;
1229 l_res_seq_num NUMBER ;
1230 l_instance_id NUMBER ;
1231 l_org_id NUMBER ;
1232 --l_serial_number NUMBER := p_eam_res_usage_rec.serial_number;
1233 l_found VARCHAR2(1) ;
1234 l_return_status VARCHAR2(1) ;
1235 l_update VARCHAR2(1) ;
1236 l_eam_res_tbl_index NUMBER;
1237
1238 BEGIN
1239 -- Initialize variables
1240 l_eam_res_tbl := p_eam_res_tbl;
1241 l_wip_entity_id := p_eam_res_usage_rec.wip_entity_id;
1242 l_operation_seq_num := p_eam_res_usage_rec.operation_seq_num;
1243 l_res_seq_num := p_eam_res_usage_rec.resource_seq_num;
1244 l_org_id := p_eam_res_usage_rec.organization_id;
1245 l_found := 'N';
1246 l_return_status := FND_API.G_RET_STS_SUCCESS;
1247
1248 x_return_status := l_return_status;
1249 x_eam_res_tbl := l_eam_res_tbl;
1250
1251 -- place check for no rows returned in SELECT .This may occur when last record of woru gets deleted !
1252
1253 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Inside update_wor' ) ; END IF ;
1254
1255 BEGIN
1256
1257 SELECT min(start_date), max(completion_date)
1258 INTO l_min_start_date,l_max_completion_date
1259 FROM wip_operation_resource_usage
1260 WHERE wip_entity_id = l_wip_entity_id
1261 AND operation_seq_num = l_operation_seq_num
1262 AND organization_id = l_org_id
1263 AND resource_seq_num = l_res_seq_num
1264 AND instance_id IS NULL
1265 AND serial_number IS NULL;
1266
1267 EXCEPTION WHEN NO_DATA_FOUND THEN
1268 l_update := 'N' ;
1269 END ;
1270
1271 IF ( NVL(l_update,'Y') = 'Y' ) THEN
1272
1273 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Updating dates in from WOR ' ) ; END IF ;
1274
1275 UPDATE wip_operation_resources
1276 SET start_date = l_min_start_date,
1277 completion_date = l_max_completion_date ,
1278 last_update_date = sysdate ,
1279 last_updated_by = FND_GLOBAL.user_id ,
1280 creation_date = sysdate ,
1281 created_by = FND_GLOBAL.user_id ,
1282 last_update_login = FND_GLOBAL.login_id
1283 WHERE wip_entity_id = l_wip_entity_id
1284 AND operation_seq_num = l_operation_seq_num
1285 AND organization_id = l_org_id
1286 AND resource_seq_num = l_res_seq_num ;
1287
1288
1289 -- now update the pl/sql table for resources.
1290
1291 l_eam_res_tbl_index := l_eam_res_tbl.FIRST;
1292
1293 WHILE l_eam_res_tbl_index IS NOT NULL LOOP
1294
1295 IF ( l_eam_res_tbl(l_eam_res_tbl_index).wip_entity_id = l_wip_entity_id AND
1296 l_eam_res_tbl(l_eam_res_tbl_index).organization_id = l_org_id AND
1297 l_eam_res_tbl(l_eam_res_tbl_index).operation_seq_num = l_operation_seq_num AND
1298 l_eam_res_tbl(l_eam_res_tbl_index).resource_seq_num = l_res_seq_num ) THEN
1299
1300 l_found := 'Y';
1301 l_eam_res_tbl(l_eam_res_tbl_index).start_date := l_min_start_date ;
1302 l_eam_res_tbl(l_eam_res_tbl_index).completion_date := l_max_completion_date ;
1303 END IF;
1304
1305 l_eam_res_tbl_index := l_eam_res_tbl.NEXT( l_eam_res_tbl_index);
1306
1307 END LOOP;
1308
1309 IF l_found = 'N' THEN -- query up from DB
1310
1311 EAM_RES_UTILITY_PVT.Query_Row ( l_wip_entity_id ,
1312 l_org_id,
1313 l_operation_seq_num,
1314 l_res_seq_num,
1315 l_resource_rec,
1316 l_return_status) ;
1317
1318 l_resource_rec.wip_entity_id := l_wip_entity_id ;
1319 l_resource_rec.organization_id := l_org_id ;
1320 l_resource_rec.operation_seq_num := l_operation_seq_num ;
1321 l_resource_rec.resource_seq_num := l_res_seq_num ;
1322
1323 IF ( l_return_status = FND_API.G_RET_STS_SUCCESS ) THEN
1324 l_resource_rec.start_date := l_min_start_date;
1325 l_resource_rec.completion_date := l_max_completion_date;
1326 l_eam_res_tbl( l_eam_res_tbl.COUNT + 1) := l_resource_rec;
1327 ELSE
1328 RAISE FND_API.G_EXC_ERROR;
1329 END IF;
1330
1331 END IF; -- end of check for l_found
1332
1333 END IF ; -- end of l_update
1334
1335 x_return_status := l_return_status;
1336 x_eam_res_tbl := l_eam_res_tbl;
1337
1338 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' exiting update_wor ' ) ; END IF ;
1339
1340 EXCEPTION WHEN FND_API.G_EXC_ERROR THEN
1341 x_return_status := l_return_status;
1342 END update_wor;
1343
1344 /*************************************************************************************************************************
1345 * Procedure : update_resource_usage
1346 * Parameters IN :
1347 p_eam_res_tbl
1348 p_eam_res_inst_tbl
1349 p_eam_res_usage_tbl
1350
1351 * Parameters OUT NOCOPY:
1352 x_eam_res_tbl
1353 x_eam_res_usage_tbl
1354 x_eam_res_inst_tbl
1355 x_return_status
1356 x_message_name
1357
1358 * Purpose : Procedure will update Resource Usage table when a resource is added, its dates are changed
1359 or usage record is added/updated/deleted. Corresponding changes are also done to WORI
1360 ************************************************************************************************************************/
1361
1362 procedure update_resource_usage(
1363 p_eam_res_tbl IN EAM_PROCESS_WO_PUB.eam_res_tbl_type
1364 , p_eam_res_inst_tbl IN EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type
1365 , p_eam_res_usage_tbl IN EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type
1366 , x_eam_res_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_tbl_type
1367 , x_eam_res_usage_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type
1368 , x_eam_res_inst_tbl OUT NOCOPY EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type
1369 , x_return_status OUT NOCOPY VARCHAR2
1370 , x_message_name OUT NOCOPY VARCHAR2
1371 ) IS
1372
1373 CURSOR get_inst_usage_min_date( c_wip_entity_id NUMBER, c_op_seq_num NUMBER , c_res_seq_num NUMBER ,
1374 c_instance_id NUMBER , c_serial_num VARCHAR2 ) IS
1375 SELECT MIN(start_date) as min_inst_usg_date
1376 FROM wip_operation_resource_usage
1377 WHERE wip_entity_id = c_wip_entity_id
1378 AND operation_seq_num = c_op_seq_num
1379 AND resource_seq_num = c_res_seq_num
1380 AND instance_id = c_instance_id
1381 AND ( serial_number IS NULL OR serial_number = c_serial_num) ;
1382
1383 CURSOR get_inst_usage_max_date( c_wip_entity_id NUMBER, c_op_seq_num NUMBER , c_res_seq_num NUMBER ,
1384 c_instance_id NUMBER , c_serial_num VARCHAR2 ) IS
1385 SELECT MAX(completion_date) as max_inst_usg_date
1386 FROM wip_operation_resource_usage
1387 WHERE wip_entity_id = c_wip_entity_id
1388 AND operation_seq_num = c_op_seq_num
1389 AND resource_seq_num = c_res_seq_num
1390 AND instance_id = c_instance_id
1391 AND ( serial_number IS NULL OR serial_number = c_serial_num) ;
1392
1393 CURSOR get_res_usage_min_date( c_wip_entity_id NUMBER, c_op_seq_num NUMBER , c_res_seq_num NUMBER ) IS
1394 SELECT MIN(start_date) as min_res_usg_date
1395 FROM wip_operation_resource_usage
1396 WHERE wip_entity_id = c_wip_entity_id
1397 AND operation_seq_num = c_op_seq_num
1398 AND resource_seq_num = c_res_seq_num
1399 AND instance_id IS NULL
1400 AND serial_number IS NULL ;
1401
1402 CURSOR get_res_usage_max_date( c_wip_entity_id NUMBER, c_op_seq_num NUMBER , c_res_seq_num NUMBER ) IS
1403 SELECT MAX(completion_date) as max_inst_usg_date
1404 FROM wip_operation_resource_usage
1405 WHERE wip_entity_id = c_wip_entity_id
1406 AND operation_seq_num = c_op_seq_num
1407 AND resource_seq_num = c_res_seq_num
1408 AND instance_id IS NULL
1409 AND serial_number IS NULL ;
1410
1411 CURSOR get_instdates( c_wip_entity_id NUMBER, c_op_seq_num NUMBER , c_res_seq_num NUMBER ) IS
1412 SELECT start_date, completion_date, instance_id, serial_number
1413 FROM wip_operation_resource_usage
1414 WHERE wip_entity_id = c_wip_entity_id
1415 AND operation_seq_num = c_op_seq_num
1416 AND resource_seq_num = c_res_seq_num;
1417
1418
1419 l_mesg_token_tbl EAM_ERROR_MESSAGE_PVT.mesg_token_tbl_type;
1420 l_return_status VARCHAR2(1) ;
1421 l_eam_res_usage_rec EAM_PROCESS_WO_PUB.eam_res_usage_rec_type;
1422 l_eam_res_inst_rec EAM_PROCESS_WO_PUB.eam_res_inst_rec_type;
1423 l_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type ;
1424 l_out_eam_res_tbl EAM_PROCESS_WO_PUB.eam_res_tbl_type ;
1425 l_eam_res_usage_tbl EAM_PROCESS_WO_PUB.eam_res_usage_tbl_type ;
1426 l_eam_res_inst_tbl EAM_PROCESS_WO_PUB.eam_res_inst_tbl_type ;
1427 l_min_found VARCHAR2(1) ;
1428 l_max_found VARCHAR2(1) ;
1429 l_res_tbl_index NUMBER;
1430 l_res_inst_tbl_index NUMBER;
1431 l_res_usage_tbl_index NUMBER;
1432 l_old_usage_start_date DATE ;
1433 l_old_usage_completion_date DATE ;
1434 l_wip_entity_id NUMBER;
1435 l_op_seq_num NUMBER;
1436 l_res_seq_num NUMBER;
1437 l_instance_id NUMBER;
1438 l_serial_num VARCHAR2(80);
1439 l_min_date DATE ;
1440 l_max_date DATE ;
1441 l_woru_count NUMBER;
1442
1443 BEGIN
1444 SAVEPOINT UPDATE_RES_USAGE ;
1445
1446 -- Initialize variables
1447 l_return_status := FND_API.G_RET_STS_SUCCESS;
1448 l_eam_res_tbl := p_eam_res_tbl;
1449 l_eam_res_usage_tbl := p_eam_res_usage_tbl;
1450 l_eam_res_inst_tbl := p_eam_res_inst_tbl;
1451 l_min_found :='N';
1452 l_max_found :='N';
1453
1454
1455 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Entering update_resource_usage ') ; END IF ;
1456
1457 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' l_eam_res_tbl.count = '|| l_eam_res_tbl.count) ; END IF ;
1458 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' l_eam_res_inst_tbl.count = '|| l_eam_res_inst_tbl.count) ; END IF ;
1459 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' l_eam_res_usage_tbl.count = '|| l_eam_res_usage_tbl.count) ; END IF ;
1460
1461
1462 l_res_inst_tbl_index := l_eam_res_inst_tbl.FIRST ;
1463
1464 WHILE l_res_inst_tbl_index IS NOT NULL LOOP
1465
1466
1467 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Processing instance record ' || l_res_inst_tbl_index) ; END IF ;
1468
1469 IF ( l_eam_res_inst_tbl(l_res_inst_tbl_index).transaction_type = EAM_PROCESS_WO_PUB.G_OPR_CREATE )THEN
1470
1471 -- when adding a resource add one record into WORU too .
1472
1473 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Creating usage record for instance ') ; END IF ;
1474
1475 l_eam_res_usage_rec.wip_entity_id := l_eam_res_inst_tbl(l_res_inst_tbl_index).wip_entity_id ;
1476 l_eam_res_usage_rec.organization_id := l_eam_res_inst_tbl(l_res_inst_tbl_index).organization_id ;
1477 l_eam_res_usage_rec.operation_seq_num := l_eam_res_inst_tbl(l_res_inst_tbl_index).operation_seq_num ;
1478 l_eam_res_usage_rec.resource_seq_num := l_eam_res_inst_tbl(l_res_inst_tbl_index).resource_seq_num ;
1479 l_eam_res_usage_rec.start_date := l_eam_res_inst_tbl(l_res_inst_tbl_index).start_date ;
1480 l_eam_res_usage_rec.completion_date := l_eam_res_inst_tbl(l_res_inst_tbl_index).completion_date ;
1481 l_eam_res_usage_rec.instance_id := l_eam_res_inst_tbl(l_res_inst_tbl_index).instance_id ;
1482 l_eam_res_usage_rec.serial_number := l_eam_res_inst_tbl(l_res_inst_tbl_index).serial_number ;
1483 l_eam_res_usage_rec.transaction_type := l_eam_res_inst_tbl(l_res_inst_tbl_index).transaction_type ;
1484
1485
1486 -- insert record into the resource usage pl/sql table
1487 l_eam_res_usage_tbl( l_eam_res_usage_tbl.COUNT + 1) := l_eam_res_usage_rec ;
1488
1489 ELSIF ( l_eam_res_inst_tbl(l_res_inst_tbl_index).transaction_type = EAM_PROCESS_WO_PUB.G_OPR_UPDATE )THEN
1490
1491 l_wip_entity_id := l_eam_res_inst_tbl(l_res_inst_tbl_index).wip_entity_id ;
1492 l_op_seq_num := l_eam_res_inst_tbl(l_res_inst_tbl_index).operation_seq_num ;
1493 l_res_seq_num := l_eam_res_inst_tbl(l_res_inst_tbl_index).resource_seq_num ;
1494 l_instance_id := l_eam_res_inst_tbl(l_res_inst_tbl_index).instance_id ;
1495 l_serial_num := l_eam_res_inst_tbl(l_res_inst_tbl_index).serial_number ;
1496
1497 BEGIN
1498
1499 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Updating WORU with start date for instance') ; END IF ;
1500
1501 OPEN get_inst_usage_min_date( l_wip_entity_id ,l_op_seq_num , l_res_seq_num , l_instance_id , l_serial_num) ;
1502 FETCH get_inst_usage_min_date INTO l_min_date ;
1503 CLOSE get_inst_usage_min_date ;
1504
1505 OPEN get_inst_usage_max_date( l_wip_entity_id ,l_op_seq_num , l_res_seq_num , l_instance_id , l_serial_num) ;
1506 FETCH get_inst_usage_max_date INTO l_max_date ;
1507 CLOSE get_inst_usage_max_date ;
1508
1509 l_res_usage_tbl_index := l_eam_res_usage_tbl.FIRST ;
1510
1511
1512 IF l_eam_res_usage_tbl.count >0 THEN
1513
1514 WHILE l_res_usage_tbl_index IS NOT NULL LOOP
1515
1516 IF ( l_eam_res_usage_tbl( l_res_usage_tbl_index ).wip_entity_id = l_wip_entity_id AND
1517 l_eam_res_usage_tbl( l_res_usage_tbl_index ).operation_seq_num = l_op_seq_num AND
1518 l_eam_res_usage_tbl( l_res_usage_tbl_index ).resource_seq_num = l_res_seq_num AND
1519 l_eam_res_usage_tbl( l_res_usage_tbl_index ).instance_id = l_instance_id AND
1520 ( l_eam_res_usage_tbl( l_res_usage_tbl_index ).serial_number IS NULL OR
1521 l_eam_res_usage_tbl( l_res_usage_tbl_index ).serial_number = l_serial_num ) AND
1522 l_eam_res_usage_tbl( l_res_usage_tbl_index ).old_start_date = l_min_date ) THEN
1523
1524 l_min_found := 'Y' ;
1525 END IF ;
1526
1527 IF ( l_eam_res_usage_tbl( l_res_usage_tbl_index ).wip_entity_id = l_wip_entity_id AND
1528 l_eam_res_usage_tbl( l_res_usage_tbl_index ).operation_seq_num = l_op_seq_num AND
1529 l_eam_res_usage_tbl( l_res_usage_tbl_index ).resource_seq_num = l_res_seq_num AND
1530 l_eam_res_usage_tbl( l_res_usage_tbl_index ).instance_id = l_instance_id AND
1531 ( l_eam_res_usage_tbl( l_res_usage_tbl_index ).serial_number IS NULL OR
1532 l_eam_res_usage_tbl( l_res_usage_tbl_index ).serial_number = l_serial_num ) AND
1533 l_eam_res_usage_tbl( l_res_usage_tbl_index ).old_completion_date = l_max_date ) THEN
1534
1535 l_max_found := 'Y' ;
1536 END IF ;
1537
1538 l_res_usage_tbl_index := l_eam_res_usage_tbl.NEXT(l_res_usage_tbl_index);
1539
1540 END LOOP;
1541
1542 IF ( l_min_found = 'N' ) THEN
1543
1544 UPDATE wip_operation_resource_usage
1545 SET start_date = l_eam_res_inst_tbl(l_res_inst_tbl_index).start_date ,
1546 last_update_date = sysdate ,
1547 last_updated_by = FND_GLOBAL.user_id ,
1548 creation_date = sysdate ,
1549 created_by = FND_GLOBAL.user_id ,
1550 last_update_login = FND_GLOBAL.login_id
1551 WHERE wip_entity_id = l_wip_entity_id
1552 AND operation_seq_num = l_op_seq_num
1553 AND resource_seq_num = l_res_seq_num
1554 AND start_date = l_min_date
1555 AND instance_id = l_instance_id
1556 AND ( serial_number IS NULL OR serial_number = l_serial_num);
1557 END IF ;
1558
1559 IF ( l_max_found = 'N' ) THEN
1560
1561 UPDATE wip_operation_resource_usage
1562 SET completion_date = l_eam_res_inst_tbl(l_res_inst_tbl_index).completion_date ,
1563 last_update_date = sysdate ,
1564 last_updated_by = FND_GLOBAL.user_id ,
1565 creation_date = sysdate ,
1566 created_by = FND_GLOBAL.user_id ,
1567 last_update_login = FND_GLOBAL.login_id
1568 WHERE wip_entity_id = l_wip_entity_id
1569 AND operation_seq_num = l_op_seq_num
1570 AND resource_seq_num = l_res_seq_num
1571 AND completion_date = l_max_date
1572 AND instance_id = l_instance_id
1573 AND ( serial_number IS NULL OR serial_number = l_serial_num);
1574 END IF ;
1575
1576 END IF;
1577
1578 EXCEPTION WHEN NO_DATA_FOUND THEN
1579 l_return_status := FND_API.G_RET_STS_ERROR ;
1580 RAISE FND_API.G_EXC_ERROR ;
1581 END ;
1582
1583 END IF; -- end of check for l_eam_res_inst_tbl(l_res_inst_tbl_index).transaction_type
1584
1585 l_res_inst_tbl_index := l_eam_res_inst_tbl.NEXT(l_res_inst_tbl_index);
1586
1587 END LOOP; -- end looping through l_eam_res_inst_tbl
1588
1589
1590 l_res_tbl_index := l_eam_res_tbl.FIRST ;
1591
1592 WHILE l_res_tbl_index IS NOT NULL LOOP
1593
1594 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Processing resource record ' || l_res_tbl_index) ; END IF ;
1595
1596 IF ( l_eam_res_tbl(l_res_tbl_index).transaction_type = EAM_PROCESS_WO_PUB.G_OPR_CREATE )THEN
1597
1598 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Creating usage record for resource') ; END IF ;
1599
1600 -- when adding a resource add one record into WORU too .
1601 l_eam_res_usage_rec.wip_entity_id := l_eam_res_tbl(l_res_tbl_index).wip_entity_id ;
1602 l_eam_res_usage_rec.organization_id := l_eam_res_tbl(l_res_tbl_index).organization_id ;
1603 l_eam_res_usage_rec.operation_seq_num := l_eam_res_tbl(l_res_tbl_index).operation_seq_num ;
1604 l_eam_res_usage_rec.resource_seq_num := l_eam_res_tbl(l_res_tbl_index).resource_seq_num ;
1605 l_eam_res_usage_rec.start_date := l_eam_res_tbl(l_res_tbl_index).start_date ;
1606 l_eam_res_usage_rec.completion_date := l_eam_res_tbl(l_res_tbl_index).completion_date ;
1607 l_eam_res_usage_rec.instance_id := NULL ;
1608 l_eam_res_usage_rec.serial_number := NULL ;
1609 l_eam_res_usage_rec.assigned_units := l_eam_res_tbl(l_res_tbl_index).assigned_units ;
1610 l_eam_res_usage_rec.transaction_type := l_eam_res_tbl(l_res_tbl_index).transaction_type ;
1611 l_eam_res_usage_rec.request_id := l_eam_res_tbl(l_res_tbl_index).request_id ;
1612 l_eam_res_usage_rec.program_application_id := l_eam_res_tbl(l_res_tbl_index).program_application_id ;
1613 l_eam_res_usage_rec.program_id := l_eam_res_tbl(l_res_tbl_index).program_id ;
1614
1615
1616 -- insert record into the resource usage pl/sql table
1617 l_eam_res_usage_tbl( l_eam_res_usage_tbl.COUNT + 1) := l_eam_res_usage_rec ;
1618
1619 ELSIF ( l_eam_res_tbl(l_res_tbl_index).transaction_type = EAM_PROCESS_WO_PUB.G_OPR_UPDATE )THEN
1620
1621 l_wip_entity_id := l_eam_res_tbl(l_res_tbl_index).wip_entity_id ;
1622 l_op_seq_num := l_eam_res_tbl(l_res_tbl_index).operation_seq_num ;
1623 l_res_seq_num := l_eam_res_tbl(l_res_tbl_index).resource_seq_num ;
1624
1625 select count(*) into l_woru_count
1626 from wip_operation_resource_usage where
1627 wip_entity_id = l_wip_entity_id
1628 AND operation_seq_num = l_op_seq_num
1629 AND resource_seq_num = l_res_seq_num
1630 AND instance_id IS NULL
1631 AND serial_number IS NULL ;
1632
1633 BEGIN
1634
1635 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Updating WORU with start date for resource') ; END IF ;
1636
1637 OPEN get_res_usage_min_date( l_wip_entity_id ,l_op_seq_num , l_res_seq_num );
1638 FETCH get_res_usage_min_date INTO l_min_date ;
1639 CLOSE get_res_usage_min_date ;
1640
1641 OPEN get_res_usage_max_date( l_wip_entity_id ,l_op_seq_num , l_res_seq_num ) ;
1642 FETCH get_res_usage_max_date INTO l_max_date ;
1643 CLOSE get_res_usage_max_date ;
1644
1645 l_res_usage_tbl_index := l_eam_res_usage_tbl.FIRST ;
1646
1647 IF l_eam_res_usage_tbl.count =0 AND l_woru_count=1 THEN -- Added for Summary tab.Only for 24 hr resources.
1648
1649 UPDATE wip_operation_resource_usage
1650 SET start_date = l_eam_res_tbl(l_res_tbl_index).start_date ,
1651 last_update_date = sysdate ,
1652 last_updated_by = FND_GLOBAL.user_id ,
1653 creation_date = sysdate ,
1654 created_by = FND_GLOBAL.user_id ,
1655 last_update_login = FND_GLOBAL.login_id
1656 WHERE wip_entity_id = l_wip_entity_id
1657 AND operation_seq_num = l_op_seq_num
1658 AND resource_seq_num = l_res_seq_num
1659 AND instance_id IS NULL
1660 AND serial_number IS NULL ;
1661
1662
1663 UPDATE wip_operation_resource_usage
1664 SET completion_date = l_eam_res_tbl(l_res_tbl_index).completion_date ,
1665 last_update_date = sysdate ,
1666 last_updated_by = FND_GLOBAL.user_id ,
1667 creation_date = sysdate ,
1668 created_by = FND_GLOBAL.user_id ,
1669 last_update_login = FND_GLOBAL.login_id
1670 WHERE wip_entity_id = l_wip_entity_id
1671 AND operation_seq_num = l_op_seq_num
1672 AND resource_seq_num = l_res_seq_num
1673 AND instance_id IS NULL
1674 AND serial_number IS NULL ;
1675
1676 FOR c_instdates_rec IN get_instdates(l_wip_entity_id , l_op_seq_num, l_res_seq_num )
1677 LOOP
1678 select count(*) into l_woru_count
1679 from wip_operation_resource_usage
1680 where
1681 wip_entity_id = l_wip_entity_id
1682 AND operation_seq_num = l_op_seq_num
1683 AND resource_seq_num = l_res_seq_num
1684 AND instance_id = c_instdates_rec.instance_id
1685 AND ( serial_number IS NULL OR serial_number = c_instdates_rec.serial_number);
1686
1687 IF l_woru_count=1 THEN -- Update WORU rows for instances.Only for 24 hr resources
1688
1689 UPDATE wip_operation_resource_usage
1690 SET start_date = l_eam_res_tbl(l_res_tbl_index).start_date ,
1691 last_update_date = sysdate ,
1692 last_updated_by = FND_GLOBAL.user_id ,
1693 creation_date = sysdate ,
1694 created_by = FND_GLOBAL.user_id ,
1695 last_update_login = FND_GLOBAL.login_id
1696 WHERE wip_entity_id = l_wip_entity_id
1697 AND operation_seq_num = l_op_seq_num
1698 AND resource_seq_num = l_res_seq_num
1699 AND instance_id = c_instdates_rec.instance_id
1700 AND ( serial_number IS NULL OR serial_number = c_instdates_rec.serial_number);
1701
1702 UPDATE wip_operation_resource_usage
1703 SET completion_date =l_eam_res_tbl(l_res_tbl_index).completion_date ,
1704 last_update_date = sysdate ,
1705 last_updated_by = FND_GLOBAL.user_id ,
1706 creation_date = sysdate ,
1707 created_by = FND_GLOBAL.user_id ,
1708 last_update_login = FND_GLOBAL.login_id
1709 WHERE wip_entity_id = l_wip_entity_id
1710 AND operation_seq_num = l_op_seq_num
1711 AND resource_seq_num = l_res_seq_num
1712 AND instance_id = c_instdates_rec.instance_id
1713 AND ( serial_number IS NULL OR serial_number = c_instdates_rec.serial_number);
1714 END IF;
1715
1716 END LOOP;
1717
1718 END IF;
1719
1720 IF l_eam_res_usage_tbl.count >0 THEN
1721
1722 WHILE l_res_usage_tbl_index IS NOT NULL LOOP
1723
1724 IF ( l_eam_res_usage_tbl( l_res_usage_tbl_index ).wip_entity_id = l_wip_entity_id AND
1725 l_eam_res_usage_tbl( l_res_usage_tbl_index ).operation_seq_num = l_op_seq_num AND
1726 l_eam_res_usage_tbl( l_res_usage_tbl_index ).resource_seq_num = l_res_seq_num AND
1727 l_eam_res_usage_tbl( l_res_usage_tbl_index ).instance_id IS NULL AND
1728 l_eam_res_usage_tbl( l_res_usage_tbl_index ).serial_number IS NULL AND
1729 l_eam_res_usage_tbl( l_res_usage_tbl_index ).old_start_date = l_min_date ) THEN
1730
1731 l_min_found := 'Y' ;
1732 END IF ;
1733
1734 IF ( l_eam_res_usage_tbl( l_res_usage_tbl_index ).wip_entity_id = l_wip_entity_id AND
1735 l_eam_res_usage_tbl( l_res_usage_tbl_index ).operation_seq_num = l_op_seq_num AND
1736 l_eam_res_usage_tbl( l_res_usage_tbl_index ).resource_seq_num = l_res_seq_num AND
1737 l_eam_res_usage_tbl( l_res_usage_tbl_index ).instance_id IS NULL AND
1738 l_eam_res_usage_tbl( l_res_usage_tbl_index ).serial_number IS NULL AND
1739 l_eam_res_usage_tbl( l_res_usage_tbl_index ).old_completion_date = l_max_date ) THEN
1740
1741 l_max_found := 'Y' ;
1742 END IF ;
1743
1744 l_res_usage_tbl_index := l_eam_res_usage_tbl.NEXT(l_res_usage_tbl_index);
1745 END LOOP;
1746
1747
1748 IF ( l_min_found = 'N' ) THEN
1749
1750 UPDATE wip_operation_resource_usage
1751 SET start_date = l_eam_res_tbl(l_res_tbl_index).start_date ,
1752 last_update_date = sysdate ,
1753 last_updated_by = FND_GLOBAL.user_id ,
1754 creation_date = sysdate ,
1755 created_by = FND_GLOBAL.user_id ,
1756 last_update_login = FND_GLOBAL.login_id
1757 WHERE wip_entity_id = l_wip_entity_id
1758 AND operation_seq_num = l_op_seq_num
1759 AND resource_seq_num = l_res_seq_num
1760 AND start_date = l_min_date
1761 AND instance_id IS NULL
1762 AND serial_number IS NULL ;
1763 END IF ;
1764
1765 IF ( l_max_found = 'N' ) THEN
1766
1767 UPDATE wip_operation_resource_usage
1768 SET completion_date = l_eam_res_tbl(l_res_tbl_index).completion_date ,
1769 last_update_date = sysdate ,
1770 last_updated_by = FND_GLOBAL.user_id ,
1771 creation_date = sysdate ,
1772 created_by = FND_GLOBAL.user_id ,
1773 last_update_login = FND_GLOBAL.login_id
1774 WHERE wip_entity_id = l_wip_entity_id
1775 AND operation_seq_num = l_op_seq_num
1776 AND resource_seq_num = l_res_seq_num
1777 AND completion_date = l_max_date
1778 AND instance_id IS NULL
1779 AND serial_number IS NULL ;
1780 END IF ;
1781
1782 END IF;
1783 EXCEPTION WHEN NO_DATA_FOUND THEN
1784 l_return_status := FND_API.G_RET_STS_ERROR ;
1785 RAISE FND_API.G_EXC_ERROR ;
1786 END ;
1787
1788 END IF; -- end of check for l_eam_res_tbl(l_res_tbl_index).transaction_type
1789
1790 l_res_tbl_index := l_eam_res_tbl.NEXT(l_res_tbl_index);
1791
1792 END LOOP; -- end looping through l_eam_res_tbl
1793
1794 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Purging resource and instance records for update from WORU ' ) ; END IF ;
1795
1796 IF ( l_eam_res_usage_tbl.count > 0 ) THEN
1797 FOR i IN l_eam_res_usage_tbl.FIRST..l_eam_res_usage_tbl.LAST LOOP
1798 IF ( l_eam_res_usage_tbl(i).transaction_type = EAM_PROCESS_WO_PUB.G_OPR_UPDATE ) THEN
1799 IF ( l_eam_res_usage_tbl(i).instance_id IS NULL ) THEN
1800 DELETE FROM wip_operation_resource_usage
1801 WHERE wip_entity_id = l_eam_res_usage_tbl(i).wip_entity_id
1802 AND operation_seq_num = l_eam_res_usage_tbl(i).operation_seq_num
1803 AND resource_seq_num = l_eam_res_usage_tbl(i).resource_seq_num
1804 AND instance_id IS NULL
1805 AND start_date = l_eam_res_usage_tbl(i).old_start_date
1806 AND completion_date = l_eam_res_usage_tbl(i).old_completion_date ;
1807 ELSE
1808 DELETE FROM wip_operation_resource_usage
1809 WHERE wip_entity_id = l_eam_res_usage_tbl(i).wip_entity_id
1810 AND operation_seq_num = l_eam_res_usage_tbl(i).operation_seq_num
1811 AND resource_seq_num = l_eam_res_usage_tbl(i).resource_seq_num
1812 AND instance_id = l_eam_res_usage_tbl(i).instance_id
1813 AND ( serial_number IS NULL OR serial_number = l_eam_res_usage_tbl(i).serial_number )
1814 AND start_date = l_eam_res_usage_tbl(i).old_start_date
1815 AND completion_date = l_eam_res_usage_tbl(i).old_completion_date ;
1816 END IF ;
1817 END IF ;
1818 END LOOP ;
1819 END IF ;
1820
1821 l_res_usage_tbl_index := l_eam_res_usage_tbl.FIRST ;
1822
1823 WHILE l_res_usage_tbl_index IS NOT NULL LOOP
1824
1825 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Processing resource usage record ' || l_res_usage_tbl_index) ; END IF ;
1826
1827 l_eam_res_usage_rec := l_eam_res_usage_tbl(l_res_usage_tbl_index);
1828
1829 IF ( l_eam_res_usage_rec.transaction_type IN ( EAM_PROCESS_WO_PUB.G_OPR_CREATE , EAM_PROCESS_WO_PUB.G_OPR_UPDATE) ) THEN
1830 -- call insert usage method
1831 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Txn: Create resource usage ') ; END IF ;
1832
1833 insert_into_woru( l_eam_res_usage_rec , l_return_status ) ;
1834
1835 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1836 RAISE FND_API.G_EXC_ERROR;
1837 END IF;
1838
1839 ELSIF ( l_eam_res_usage_rec.transaction_type = EAM_PROCESS_WO_PUB.G_OPR_DELETE ) THEN
1840 -- call method delete usage and update inst table.
1841
1842 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Txn:Delete resource usage ') ; END IF ;
1843
1844 delete_from_woru( l_eam_res_usage_rec ) ;
1845
1846 END IF; -- end of checking l_eam_res_usage_tbl(i).transaction_type
1847
1848 l_res_usage_tbl_index := l_eam_res_usage_tbl.NEXT(l_res_usage_tbl_index);
1849
1850 END LOOP; -- end of looping through l_eam_res_usage_tbl
1851
1852 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Sync up resource and instance records with usage records ') ; END IF ;
1853
1854 IF ( l_eam_res_usage_tbl.count > 0 ) THEN
1855 FOR i IN l_eam_res_usage_tbl.FIRST..l_eam_res_usage_tbl.LAST LOOP
1856
1857 l_eam_res_usage_rec := l_eam_res_usage_tbl(i) ;
1858
1859 IF ( l_eam_res_usage_rec.instance_id IS NOT NULL ) THEN
1860
1861 update_wori ( l_eam_res_usage_rec
1862 ,p_eam_res_inst_tbl
1863 ,l_eam_res_inst_tbl
1864 ,l_return_status );
1865
1866 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1867 RAISE FND_API.G_EXC_ERROR;
1868 END IF;
1869
1870 ELSE
1871 update_wor ( l_eam_res_usage_rec
1872 , l_eam_res_tbl
1873 , l_out_eam_res_tbl
1874 , l_return_status );
1875
1876 l_eam_res_tbl := l_out_eam_res_tbl;
1877
1878 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1879 RAISE FND_API.G_EXC_ERROR;
1880 END IF;
1881 END IF ;
1882 END LOOP ;
1883 END IF ;
1884
1885 x_eam_res_usage_tbl := l_eam_res_usage_tbl ;
1886 x_eam_res_inst_tbl := l_eam_res_inst_tbl ;
1887 x_eam_res_tbl := l_eam_res_tbl ;
1888 x_return_status := l_return_status ;
1889
1890 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Exiting update_resource_usage ') ; END IF ;
1891
1892 EXCEPTION
1893 WHEN FND_API.G_EXC_ERROR THEN
1894 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug(' Error occured in update_resource_usage API ') ; END IF ;
1895 ROLLBACK TO UPDATE_RES_USAGE ;
1896 x_return_status := l_return_status ;
1897 x_message_name := ' ' ;
1898
1899 END update_resource_usage;
1900
1901 END EAM_SCHED_BOTTOM_UP_PVT ;
1902
1903