[Home] [Help]
PACKAGE BODY: APPS.EAM_OP_UTILITY_PVT
Source
1 PACKAGE BODY EAM_OP_UTILITY_PVT AS
2 /* $Header: EAMVOPUB.pls 120.7 2006/03/14 20:46:17 pkathoti noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVOPUB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EAM_OP_UTILITY_PVT
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 30-JUN-2002 Kenichi Nagumo Initial Creation
21 -- 27-OCT-2004 Girish Enhancements Bug 3852846
22 ***************************************************************************/
23
24 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_OP_UTILITY_PVT';
25
26 /*********************************************************************
27 * Procedure : Query_Row
28 * Parameters IN : wip entity id
29 * organization Id
30 * operation_seq_num
31 * Parameters OUT NOCOPY: EAM OP column record
32 * Mesg token Table
33 * Return Status
34 * Purpose : Procedure will query the database record
35 * and return with those records.
36 ***********************************************************************/
37
38 PROCEDURE Query_Row
39 ( p_wip_entity_id IN NUMBER
40 , p_organization_id IN NUMBER
41 , p_operation_seq_num IN NUMBER
42 , x_eam_op_rec OUT NOCOPY EAM_PROCESS_WO_PUB.eam_op_rec_type
43 , x_Return_status OUT NOCOPY VARCHAR2
44 )
45 IS
46 l_eam_op_rec EAM_PROCESS_WO_PUB.eam_op_rec_type;
47 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
48 l_dummy varchar2(10);
49 BEGIN
50
51 SELECT
52 wip_entity_id
53 , organization_id
54 , operation_sequence_id
55 , operation_seq_num
56 , standard_operation_id
57 , department_id
58 , description
59 , minimum_transfer_quantity
60 , count_point_type
61 , backflush_flag
62 , shutdown_type
63 , first_unit_start_date
64 , first_unit_completion_date
65 , attribute_category
66 , attribute1
67 , attribute2
68 , attribute3
69 , attribute4
70 , attribute5
71 , attribute6
72 , attribute7
73 , attribute8
74 , attribute9
75 , attribute10
76 , attribute11
77 , attribute12
78 , attribute13
79 , attribute14
80 , attribute15
81 , long_description
82 INTO
83 l_eam_op_rec.wip_entity_id
84 , l_eam_op_rec.organization_id
85 , l_eam_op_rec.operation_sequence_id
86 , l_eam_op_rec.operation_seq_num
87 , l_eam_op_rec.standard_operation_id
88 , l_eam_op_rec.department_id
89 , l_eam_op_rec.description
90 , l_eam_op_rec.minimum_transfer_quantity
91 , l_eam_op_rec.count_point_type
92 , l_eam_op_rec.backflush_flag
93 , l_eam_op_rec.shutdown_type
94 , l_eam_op_rec.start_date
95 , l_eam_op_rec.completion_date
96 , l_eam_op_rec.attribute_category
97 , l_eam_op_rec.attribute1
98 , l_eam_op_rec.attribute2
99 , l_eam_op_rec.attribute3
100 , l_eam_op_rec.attribute4
101 , l_eam_op_rec.attribute5
102 , l_eam_op_rec.attribute6
103 , l_eam_op_rec.attribute7
104 , l_eam_op_rec.attribute8
105 , l_eam_op_rec.attribute9
106 , l_eam_op_rec.attribute10
107 , l_eam_op_rec.attribute11
108 , l_eam_op_rec.attribute12
109 , l_eam_op_rec.attribute13
110 , l_eam_op_rec.attribute14
111 , l_eam_op_rec.attribute15
112 , l_eam_op_rec.long_description
113 FROM wip_operations wo
114 WHERE wo.wip_entity_id = p_wip_entity_id
115 AND wo.organization_id = p_organization_id
116 AND wo.operation_seq_num = p_operation_seq_num;
117
118 x_return_status := EAM_PROCESS_WO_PVT.G_RECORD_FOUND;
119 x_eam_op_rec := l_eam_op_rec;
120
121 EXCEPTION
122 WHEN NO_DATA_FOUND THEN
123 x_return_status := EAM_PROCESS_WO_PVT.G_RECORD_NOT_FOUND;
124 x_eam_op_rec := l_eam_op_rec;
125
126 WHEN OTHERS THEN
127 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
128 x_eam_op_rec := l_eam_op_rec;
129
130 END Query_Row;
131
132
133 /********************************************************************
134 * Procedure : Insert_Row
135 * Parameters IN : EAM OP column record
136 * Parameters OUT NOCOPY: Message Token Table
137 * Return Status
138 * Purpose : Procedure will perfrom an insert into the
139 * wip_operations table.
140 *********************************************************************/
141
142 PROCEDURE Insert_Row
143 ( p_eam_op_rec IN EAM_PROCESS_WO_PUB.eam_op_rec_type
144 , x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
145 , x_return_Status OUT NOCOPY VARCHAR2
146 )
147 IS
148 l_count number;
149 l_min_op_seq_num number;
150 l_department_id number;
151 BEGIN
152
153 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Writing OP rec for ' || p_eam_op_rec.operation_seq_num); END IF;
154
155 -- bug no 3444091
156 if p_eam_op_rec.start_date > p_eam_op_rec.completion_date then
157 x_return_status := fnd_api.g_ret_sts_error;
158 fnd_message.set_name('EAM','EAM_WO_OP_DT_ERR');
159 return;
160 end if;
161
162 INSERT INTO WIP_OPERATIONS
163 ( wip_entity_id
164 , operation_seq_num
165 , organization_id
166 , operation_sequence_id
167 , standard_operation_id
168 , department_id
169 , description
170 , scheduled_quantity
171 , quantity_in_queue
172 , quantity_running
173 , quantity_waiting_to_move
174 , quantity_rejected
175 , quantity_scrapped
176 , quantity_completed
177 , first_unit_start_date
178 , first_unit_completion_date
179 , last_unit_start_date
180 , last_unit_completion_date
181 , count_point_type
182 , backflush_flag
183 , minimum_transfer_quantity
184 , shutdown_type
185 , attribute_category
186 , attribute1
187 , attribute2
188 , attribute3
189 , attribute4
190 , attribute5
191 , attribute6
192 , attribute7
193 , attribute8
194 , attribute9
195 , attribute10
196 , attribute11
197 , attribute12
198 , attribute13
199 , attribute14
200 , attribute15
201 , long_description
202 , last_update_date
203 , last_updated_by
204 , creation_date
205 , created_by
206 , last_update_login
207 , request_id
208 , program_application_id
209 , program_id
210 , program_update_date
211 , x_pos
212 , y_pos)
213 VALUES
214 ( p_eam_op_rec.wip_entity_id
215 , p_eam_op_rec.operation_seq_num
216 , p_eam_op_rec.organization_id
217 , p_eam_op_rec.operation_sequence_id
218 , p_eam_op_rec.standard_operation_id
219 , p_eam_op_rec.department_id
220 , p_eam_op_rec.description
221 , 1
222 , 0
223 , 0,0,0,0,0
224 , p_eam_op_rec.start_date
225 , p_eam_op_rec.completion_date
226 , p_eam_op_rec.start_date
227 , p_eam_op_rec.completion_date
228 , p_eam_op_rec.count_point_type
229 , p_eam_op_rec.backflush_flag
230 , p_eam_op_rec.minimum_transfer_quantity
231 , p_eam_op_rec.shutdown_type
232 , p_eam_op_rec.attribute_category
233 , p_eam_op_rec.attribute1
234 , p_eam_op_rec.attribute2
235 , p_eam_op_rec.attribute3
236 , p_eam_op_rec.attribute4
237 , p_eam_op_rec.attribute5
238 , p_eam_op_rec.attribute6
239 , p_eam_op_rec.attribute7
240 , p_eam_op_rec.attribute8
241 , p_eam_op_rec.attribute9
242 , p_eam_op_rec.attribute10
243 , p_eam_op_rec.attribute11
244 , p_eam_op_rec.attribute12
245 , p_eam_op_rec.attribute13
246 , p_eam_op_rec.attribute14
247 , p_eam_op_rec.attribute15
248 , p_eam_op_rec.long_description
249 , SYSDATE
250 , FND_GLOBAL.user_id
251 , SYSDATE
252 , FND_GLOBAL.user_id
253 , FND_GLOBAL.login_id
254 , p_eam_op_rec.request_id
255 , p_eam_op_rec.program_application_id
256 , p_eam_op_rec.program_id
257 , SYSDATE
258 , p_eam_op_rec.x_pos
259 , p_eam_op_rec.y_pos); --Added for bug#4615678
260
261 IF p_eam_op_rec.standard_operation_id IS NOT NULL THEN
262
263 FND_ATTACHED_DOCUMENTS2_PKG.copy_attachments(
264 X_from_entity_name => 'BOM_STANDARD_OPERATIONS',
265 X_from_pk1_value => to_char(p_eam_op_rec.standard_operation_id),
266 X_from_pk2_value => '',
267 X_from_pk3_value => '',
268 X_from_pk4_value => '',
269 X_from_pk5_value => '',
270 X_to_entity_name => 'EAM_DISCRETE_OPERATIONS',
271 X_to_pk1_value => p_eam_op_rec.wip_entity_id,
272 X_to_pk2_value => p_eam_op_rec.operation_seq_num,
273 X_to_pk3_value => p_eam_op_rec.organization_id,
274 X_to_pk4_value => '',
275 X_to_pk5_value => '',
276 X_created_by => fnd_global.user_id,
277 X_last_update_login => '',
278 X_program_application_id=> '',
279 X_program_id => '',
280 X_request_id => ''
281 );
282
283 END IF;
284
285 SELECT count(*) INTO l_count
286 FROM wip_requirement_operations
287 WHERE organization_id = p_eam_op_rec.organization_id
288 AND wip_entity_id = p_eam_op_rec.wip_entity_id
289 AND operation_seq_num = 1
290 AND rownum <=1;
291
292 IF l_count <> 0 THEN
293 select min(operation_seq_num) into l_min_op_seq_num
294 from wip_operations
295 where organization_id = p_eam_op_rec.organization_id
296 and wip_entity_id = p_eam_op_rec.wip_entity_id ;
297
298 IF (l_min_op_seq_num is not null) THEN
299 select department_id into l_department_id
300 from wip_operations
301 where organization_id = p_eam_op_rec.organization_id
302 and wip_entity_id = p_eam_op_rec.wip_entity_id
303 and operation_seq_num = l_min_op_seq_num;
304 END IF;
305
306
307 update wip_requirement_operations
308 set operation_seq_num = l_min_op_seq_num,
309 department_id = l_department_id
310 where operation_seq_num = 1
311 and organization_id = p_eam_op_rec.organization_id
312 and wip_entity_id = p_eam_op_rec.wip_entity_id ;
313 END IF;
314
315 SELECT count(*) INTO l_count
316 FROM wip_eam_direct_items
317 WHERE organization_id = p_eam_op_rec.organization_id
318 AND wip_entity_id = p_eam_op_rec.wip_entity_id
319 AND operation_seq_num = 1
320 AND rownum <=1;
321
322 IF l_count <> 0 THEN
323 select min(operation_seq_num) into l_min_op_seq_num
324 from wip_operations
325 where organization_id = p_eam_op_rec.organization_id
326 and wip_entity_id = p_eam_op_rec.wip_entity_id ;
327
328
329 IF (l_min_op_seq_num is not null) THEN
330 begin
331 select department_id into l_department_id
332 from wip_eam_direct_items
333 where organization_id = p_eam_op_rec.organization_id
334 and wip_entity_id = p_eam_op_rec.wip_entity_id
335 and operation_seq_num = l_min_op_seq_num;
336 exception when no_data_found then
337 null;
338 end;
339 END IF;
340
341 update wip_eam_direct_items
342 set operation_seq_num = l_min_op_seq_num,
343 department_id = l_department_id
344 where operation_seq_num = 1
345 and organization_id = p_eam_op_rec.organization_id
346 and wip_entity_id = p_eam_op_rec.wip_entity_id ;
347 END IF;
348
349 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug ('Creating new operation') ; END IF;
350
351 x_return_status := FND_API.G_RET_STS_SUCCESS;
352
353 EXCEPTION
354 WHEN OTHERS THEN
355 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
356 ( p_message_name => NULL
357 , p_message_text => G_PKG_NAME ||' :Inserting Record ' || SQLERRM
358 , x_mesg_token_Tbl => x_mesg_token_tbl
359 );
360
361 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
362
363 END Insert_Row;
364
365 /********************************************************************
366 * Procedure : Update_Row
367 * Parameters IN : EAM OP column record
368 * Parameters OUT NOCOPY: Message Token Table
369 * Return Status
373
370 * Purpose : Procedure will perfrom an Update on the
371 * wip_operations table.
372 *********************************************************************/
374 PROCEDURE Update_Row
375 ( p_eam_op_rec IN EAM_PROCESS_WO_PUB.eam_op_rec_type
376 , x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
377 , x_return_Status OUT NOCOPY VARCHAR2
378 )
379 IS
380
381 l_old_dept_id NUMBER;
382 BEGIN
383
384 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Updating OP '|| p_eam_op_rec.operation_seq_num); END IF;
385
386 -- bug no 3444091
387 if p_eam_op_rec.start_date > p_eam_op_rec.completion_date then
388 x_return_status := fnd_api.g_ret_sts_error;
389 fnd_message.set_name('EAM','EAM_WO_OP_DT_ERR');
390 return;
391 end if;
392
393 select department_id into l_old_dept_id
394 from wip_operations
395 WHERE organization_id = p_eam_op_rec.organization_id
396 AND wip_entity_id = p_eam_op_rec.wip_entity_id
397 AND operation_seq_num = p_eam_op_rec.operation_seq_num;
398
399
400 UPDATE WIP_OPERATIONS
401 SET operation_sequence_id = p_eam_op_rec.operation_sequence_id
402 , standard_operation_id = p_eam_op_rec.standard_operation_id
403 , department_id = p_eam_op_rec.department_id
404 , description = p_eam_op_rec.description
405 , first_unit_start_date = p_eam_op_rec.start_date
406 , first_unit_completion_date = p_eam_op_rec.completion_date
407 , last_unit_start_date = p_eam_op_rec.start_date
408 , last_unit_completion_date = p_eam_op_rec.completion_date
409 , count_point_type = p_eam_op_rec.count_point_type
410 , backflush_flag = p_eam_op_rec.backflush_flag
411 , minimum_transfer_quantity = p_eam_op_rec.minimum_transfer_quantity
412 , shutdown_type = p_eam_op_rec.shutdown_type
413 , attribute_category = p_eam_op_rec.attribute_category
414 , attribute1 = p_eam_op_rec.attribute1
415 , attribute2 = p_eam_op_rec.attribute2
416 , attribute3 = p_eam_op_rec.attribute3
417 , attribute4 = p_eam_op_rec.attribute4
418 , attribute5 = p_eam_op_rec.attribute5
419 , attribute6 = p_eam_op_rec.attribute6
420 , attribute7 = p_eam_op_rec.attribute7
421 , attribute8 = p_eam_op_rec.attribute8
422 , attribute9 = p_eam_op_rec.attribute9
423 , attribute10 = p_eam_op_rec.attribute10
424 , attribute11 = p_eam_op_rec.attribute11
425 , attribute12 = p_eam_op_rec.attribute12
426 , attribute13 = p_eam_op_rec.attribute13
427 , attribute14 = p_eam_op_rec.attribute14
428 , attribute15 = p_eam_op_rec.attribute15
429 , long_description = p_eam_op_rec.long_description
430 , last_update_date = SYSDATE
431 , last_updated_by = FND_GLOBAL.user_id
432 , last_update_login = FND_GLOBAL.login_id
433 , request_id = p_eam_op_rec.request_id
434 , program_application_id = p_eam_op_rec.program_application_id
435 , program_id = p_eam_op_rec.program_id
436 , program_update_date = SYSDATE
437 , x_pos = p_eam_op_rec.x_pos --Added for bug#4615678
438 , y_pos = p_eam_op_rec.y_pos --Added for bug#4615678
439 WHERE organization_id = p_eam_op_rec.organization_id
440 AND wip_entity_id = p_eam_op_rec.wip_entity_id
441 AND operation_seq_num = p_eam_op_rec.operation_seq_num;
442
443 -- If Department of operation is updated then correponding departemnt of materials should also get updated
444 IF l_old_dept_id <> p_eam_op_rec.department_id THEN
445 UPDATE WIP_REQUIREMENT_OPERATIONS
446 set department_id = p_eam_op_rec.department_id
447 WHERE organization_id = p_eam_op_rec.organization_id
448 AND wip_entity_id = p_eam_op_rec.wip_entity_id
449 AND operation_seq_num = p_eam_op_rec.operation_seq_num ;
450
451 UPDATE WIP_EAM_DIRECT_ITEMS
452 SET Department_id = p_eam_op_rec.department_id
453 WHERE organization_id = p_eam_op_rec.organization_id
454 AND wip_entity_id = p_eam_op_rec.wip_entity_id
455 AND operation_seq_num = p_eam_op_rec.operation_seq_num ;
456 END IF;
457 x_return_status := FND_API.G_RET_STS_SUCCESS;
458
459 END Update_Row;
460
461
462
463
464 PROCEDURE Delete_Row
468 )
465 ( p_eam_op_rec IN EAM_PROCESS_WO_PUB.eam_op_rec_type
466 , x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
467 , x_return_Status OUT NOCOPY VARCHAR2
469 IS
470 BEGIN
471
472 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Deleting Operation: '|| p_eam_op_rec.operation_seq_num); END IF;
473
474 DELETE FROM WIP_OPERATIONS
475 WHERE wip_entity_id = p_eam_op_rec.wip_entity_id
476 AND organization_id = p_eam_op_rec.organization_id
477 AND operation_seq_num = p_eam_op_rec.operation_seq_num;
478
479 -- Enhancement Bug 3852846
480 UPDATE eam_asset_status_history
481 SET enable_flag = 'N'
482 , last_update_date = SYSDATE
483 , last_updated_by = FND_GLOBAL.user_id
484 , last_update_login = FND_GLOBAL.login_id
485 WHERE wip_entity_id = p_eam_op_rec.wip_entity_id
486 AND organization_id = p_eam_op_rec.organization_id
487 AND operation_seq_num = p_eam_op_rec.operation_seq_num
488 AND (enable_flag = 'Y' OR enable_flag IS NULL);
489
490 x_return_status := FND_API.G_RET_STS_SUCCESS;
491
492 END Delete_Row;
493
494
495
496 /*********************************************************************
497 * Procedure : Perform_Writes
498 * Parameters IN : Operation Record
499 * Parameters OUT NOCOPY: Messgae Token Table
500 * Return Status
501 * Purpose : This is the only procedure that the user will have
502 * access to when he/she needs to perform any kind of
503 * writes to the wip_operations.
504 *********************************************************************/
505
506 PROCEDURE Perform_Writes
507 ( p_eam_op_rec IN EAM_PROCESS_WO_PUB.eam_op_rec_type
508 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
509 , x_return_status OUT NOCOPY VARCHAR2
510 )
511 IS
512 l_Mesg_Token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
513 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
514 BEGIN
515
516 IF p_eam_op_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE
517 THEN
518 Insert_Row
519 ( p_eam_op_rec => p_eam_op_rec
520 , x_mesg_token_Tbl => l_mesg_token_tbl
521 , x_return_Status => l_return_status
522 );
523 ELSIF p_eam_op_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
524 THEN
525 Update_Row
526 ( p_eam_op_rec => p_eam_op_rec
527 , x_mesg_token_Tbl => l_mesg_token_tbl
528 , x_return_Status => l_return_status
529 );
530 ELSIF p_eam_op_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_DELETE
531 THEN
532 Delete_Row
533 ( p_eam_op_rec => p_eam_op_rec
534 , x_mesg_token_Tbl => l_mesg_token_tbl
535 , x_return_Status => l_return_status
536 );
537
538 END IF;
539
540 x_return_status := l_return_status;
541 x_mesg_token_tbl := l_mesg_token_tbl;
542
543 END Perform_Writes;
544
545
546 FUNCTION NUM_OF_ROW
547 ( p_eam_op_tbl IN EAM_PROCESS_WO_PUB.eam_op_tbl_type
548 , p_wip_entity_id IN NUMBER
549 , p_organization_id IN NUMBER
550 ) RETURN BOOLEAN
551 IS
552
553 l_count NUMBER := 0;
554
555 BEGIN
556
557 begin
558 IF p_wip_entity_id is not null
559 THEN
560 select count(*)
561 into l_count
562 from wip_operations
563 where wip_entity_id = p_wip_entity_id
564 and organization_id = p_organization_id
565 AND rownum <=1;
566
567 END IF;
568 end;
569
570 l_count := l_count + p_eam_op_tbl.COUNT;
571
572 IF (l_count > 0) THEN
573 RETURN FALSE;
574 ELSE
575 RETURN TRUE;
576 END IF;
577
578 END NUM_OF_ROW;
579
580
581 END EAM_OP_UTILITY_PVT;