[Home] [Help]
PACKAGE BODY: APPS.EAM_WO_UTILITY_PVT
Source
1 PACKAGE BODY EAM_WO_UTILITY_PVT AS
2 /* $Header: EAMVWOUB.pls 120.11.12020000.2 2013/01/17 10:11:13 fqu ship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 2002 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- EAMVWOUB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package EAM_WO_UTILITY_PVT
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 30-JUN-2002 Kenichi Nagumo Initial Creation
21 ***************************************************************************/
22
23 G_PKG_NAME CONSTANT VARCHAR2(30) := 'EAM_WO_UTILITY_PVT';
24
25 /*********************************************************************
26 * Procedure : Query_Row
27 * Parameters IN : wip entity id
28 * organization Id
29 * Parameters OUT NOCOPY: EAM WO column record
30 * Mesg token Table
31 * Return Status
32 * Purpose : Procedure will query the database record
33 * and return with those records.
34 ***********************************************************************/
35 PROCEDURE Query_Row
36 ( p_wip_entity_id IN NUMBER
37 , p_organization_id IN NUMBER
38 , x_eam_wo_rec OUT NOCOPY EAM_PROCESS_WO_PUB.eam_wo_rec_type
39 , x_Return_status OUT NOCOPY VARCHAR2
40 )
41 IS
42 l_eam_wo_rec EAM_PROCESS_WO_PUB.eam_wo_rec_type;
43 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
44 l_dummy varchar2(10);
45 BEGIN
46
47 SELECT we.wip_entity_name
48 , wdj.wip_entity_id
49 , wdj.organization_id
50 , wdj.description
51 , wdj.asset_number
52 , wdj.asset_group_id
53 , wdj.rebuild_item_id
54 , wdj.rebuild_serial_number
55 , we.gen_object_id
56 , wdj.maintenance_object_id
57 , wdj.maintenance_object_type
58 , wdj.maintenance_object_source
59 , wdj.eam_linear_location_id
60 , wdj.class_code
61 , wdj.primary_item_id
62 , wdj.activity_type
63 , wdj.activity_cause
64 , wdj.activity_source
65 , wdj.work_order_type
66 , wdj.status_type
67 , wdj.start_quantity
68 , wdj.date_released
69 , wdj.owning_department
70 , wdj.priority
71 , wdj.requested_start_date
72 , wdj.due_date
73 , wdj.shutdown_type
74 , wdj.firm_planned_flag
75 , wdj.notification_required
76 , wdj.tagout_required
77 , wdj.plan_maintenance
78 , wdj.project_id
79 , wdj.task_id
80 --, wdj.project_costed
81 , wdj.end_item_unit_number
82 , wdj.schedule_group_id
83 , wdj.bom_revision_date
84 , wdj.routing_revision_date
85 , wdj.alternate_routing_designator
86 , wdj.alternate_bom_designator
87 , wdj.routing_revision
88 , wdj.bom_revision
89 , wdj.parent_wip_entity_id
90 , wdj.manual_rebuild_flag
91 , wdj.pm_schedule_id
92 , wdj.material_account
93 , wdj.material_overhead_account
94 , wdj.resource_account
95 , wdj.outside_processing_account
96 , wdj.material_variance_account
97 , wdj.resource_variance_account
98 , wdj.outside_proc_variance_account
99 , wdj.std_cost_adjustment_account
100 , wdj.overhead_account
101 , wdj.overhead_variance_account
102 , wdj.scheduled_start_date
103 , wdj.scheduled_completion_date
104 , wdj.common_bom_sequence_id
105 , wdj.common_routing_sequence_id
106 , wdj.po_creation_time
107 , wdj.attribute_category
108 , wdj.attribute1
109 , wdj.attribute2
110 , wdj.attribute3
111 , wdj.attribute4
112 , wdj.attribute5
113 , wdj.attribute6
114 , wdj.attribute7
115 , wdj.attribute8
116 , wdj.attribute9
117 , wdj.attribute10
118 , wdj.attribute11
119 , wdj.attribute12
120 , wdj.attribute13
121 , wdj.attribute14
122 , wdj.attribute15
123 , wdj.material_issue_by_mo
124 , wdj.source_line_id
125 , wdj.source_code
126 , wdj.issue_zero_cost_flag
127 , ewod.user_defined_status_id
128 , ewod.pending_flag
129 , ewod.material_shortage_check_date
130 , ewod.material_shortage_flag
131 , ewod.workflow_type
132 , ewod.warranty_claim_status
133 , ewod.cycle_id
134 , ewod.seq_id
135 , ewod.ds_scheduled_flag
136 , ewod.assignment_complete
137 , ewod.warranty_active
138 , ewod.pm_suggested_start_date
139 , ewod.pm_suggested_end_date
140 , ewod.pm_base_meter_reading
141 , ewod.pm_base_meter
142 , ewod.failure_code_required
143 , ewod.planner_maintenance
144 INTO
145 l_eam_wo_rec.wip_entity_name
146 , l_eam_wo_rec.wip_entity_id
147 , l_eam_wo_rec.organization_id
148 , l_eam_wo_rec.description
149 , l_eam_wo_rec.asset_number
150 , l_eam_wo_rec.asset_group_id
151 , l_eam_wo_rec.rebuild_item_id
152 , l_eam_wo_rec.rebuild_serial_number
153 , l_eam_wo_rec.gen_object_id
154 , l_eam_wo_rec.maintenance_object_id
155 , l_eam_wo_rec.maintenance_object_type
156 , l_eam_wo_rec.maintenance_object_source
157 , l_eam_wo_rec.eam_linear_location_id
158 , l_eam_wo_rec.class_code
159 , l_eam_wo_rec.asset_activity_id
160 , l_eam_wo_rec.activity_type
161 , l_eam_wo_rec.activity_cause
162 , l_eam_wo_rec.activity_source
163 , l_eam_wo_rec.work_order_type
164 , l_eam_wo_rec.status_type
165 , l_eam_wo_rec.job_quantity
166 , l_eam_wo_rec.date_released
167 , l_eam_wo_rec.owning_department
168 , l_eam_wo_rec.priority
169 , l_eam_wo_rec.requested_start_date
170 , l_eam_wo_rec.due_date
171 , l_eam_wo_rec.shutdown_type
172 , l_eam_wo_rec.firm_planned_flag
173 , l_eam_wo_rec.notification_required
174 , l_eam_wo_rec.tagout_required
175 , l_eam_wo_rec.plan_maintenance
176 , l_eam_wo_rec.project_id
177 , l_eam_wo_rec.task_id
178 --, l_eam_wo_rec.project_costed
179 , l_eam_wo_rec.end_item_unit_number
180 , l_eam_wo_rec.schedule_group_id
181 , l_eam_wo_rec.bom_revision_date
182 , l_eam_wo_rec.routing_revision_date
183 , l_eam_wo_rec.alternate_routing_designator
184 , l_eam_wo_rec.alternate_bom_designator
185 , l_eam_wo_rec.routing_revision
186 , l_eam_wo_rec.bom_revision
187 , l_eam_wo_rec.parent_wip_entity_id
188 , l_eam_wo_rec.manual_rebuild_flag
189 , l_eam_wo_rec.pm_schedule_id
190 , l_eam_wo_rec.material_account
191 , l_eam_wo_rec.material_overhead_account
192 , l_eam_wo_rec.resource_account
193 , l_eam_wo_rec.outside_processing_account
194 , l_eam_wo_rec.material_variance_account
195 , l_eam_wo_rec.resource_variance_account
196 , l_eam_wo_rec.outside_proc_variance_account
197 , l_eam_wo_rec.std_cost_adjustment_account
198 , l_eam_wo_rec.overhead_account
199 , l_eam_wo_rec.overhead_variance_account
200 , l_eam_wo_rec.scheduled_start_date
201 , l_eam_wo_rec.scheduled_completion_date
202 , l_eam_wo_rec.common_bom_sequence_id
203 , l_eam_wo_rec.common_routing_sequence_id
204 , l_eam_wo_rec.po_creation_time
205 , l_eam_wo_rec.attribute_category
206 , l_eam_wo_rec.attribute1
207 , l_eam_wo_rec.attribute2
208 , l_eam_wo_rec.attribute3
209 , l_eam_wo_rec.attribute4
210 , l_eam_wo_rec.attribute5
211 , l_eam_wo_rec.attribute6
212 , l_eam_wo_rec.attribute7
213 , l_eam_wo_rec.attribute8
214 , l_eam_wo_rec.attribute9
215 , l_eam_wo_rec.attribute10
216 , l_eam_wo_rec.attribute11
217 , l_eam_wo_rec.attribute12
218 , l_eam_wo_rec.attribute13
219 , l_eam_wo_rec.attribute14
220 , l_eam_wo_rec.attribute15
221 , l_eam_wo_rec.material_issue_by_mo
222 , l_eam_wo_rec.source_line_id
223 , l_eam_wo_rec.source_code
224 , l_eam_wo_rec.issue_zero_cost_flag
225 , l_eam_wo_rec.user_defined_status_id
226 , l_eam_wo_rec.pending_flag
227 , l_eam_wo_rec.material_shortage_check_date
228 , l_eam_wo_rec.material_shortage_flag
229 , l_eam_wo_rec.workflow_type
230 , l_eam_wo_rec.warranty_claim_status
231 , l_eam_wo_rec.cycle_id
232 , l_eam_wo_rec.seq_id
233 , l_eam_wo_rec.ds_scheduled_flag
234 , l_eam_wo_rec.assignment_complete
235 , l_eam_wo_rec.warranty_active
236 , l_eam_wo_rec.pm_suggested_start_date
237 , l_eam_wo_rec.pm_suggested_end_date
238 , l_eam_wo_rec.pm_base_meter_reading
239 , l_eam_wo_rec.pm_base_meter
240 , l_eam_wo_rec.failure_code_required
241 , l_eam_wo_rec.planner_type --vardha
242 FROM wip_discrete_jobs wdj, wip_entities we,eam_work_order_details ewod
243 WHERE wdj.wip_entity_id = we.wip_entity_id
244 AND wdj.organization_id = we.organization_id
245 AND wdj.wip_entity_id = p_wip_entity_id
246 AND wdj.organization_id = p_organization_id
247 AND wdj.wip_entity_id = ewod.wip_entity_id(+)
248 AND wdj.organization_id = ewod.organization_id(+);
249
250
251 x_return_status := EAM_PROCESS_WO_PVT.G_RECORD_FOUND;
252 x_eam_wo_rec := l_eam_wo_rec;
253
254 EXCEPTION
255 WHEN NO_DATA_FOUND THEN
256 x_return_status := EAM_PROCESS_WO_PVT.G_RECORD_NOT_FOUND;
257 x_eam_wo_rec := l_eam_wo_rec;
258
259 WHEN OTHERS THEN
260 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
261 x_eam_wo_rec := l_eam_wo_rec;
262
263 END Query_Row;
264
265
266 /********************************************************************
267 * Procedure : Insert_Row
268 * Parameters IN : Work Order column record
269 * Parameters OUT NOCOPY: Message Token Table
270 * Return Status
271 * Purpose : Procedure will perfrom an insert into the
272 * win_discrete_jobs and wip_entities table.
273 *********************************************************************/
274
275 PROCEDURE Insert_Row
276 ( p_eam_wo_rec IN EAM_PROCESS_WO_PUB.eam_wo_rec_type
277 , x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
278 , x_return_Status OUT NOCOPY VARCHAR2
279 )
280 IS
281
282 l_asset_ops_msg_count NUMBER;
283 l_asset_ops_msg_data VARCHAR2(2000);
284 l_asset_ops_return_status VARCHAR2(1);
285
286 BEGIN
287
288 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Writing EAM WO rec for ' || p_eam_wo_rec.wip_entity_name); END IF;
289
290 -- bug no 3444091
291 if p_eam_wo_rec.scheduled_start_date > p_eam_wo_rec.scheduled_completion_date then
292 x_return_status := fnd_api.g_ret_sts_error;
293 fnd_message.set_name('EAM','EAM_WO_WORKORDER_DT_ERR');
294 return;
295 end if;
296
297 INSERT INTO WIP_DISCRETE_JOBS
298 ( wip_entity_id
299 , organization_id
300 , description
301 , asset_number
302 , asset_group_id
303 , rebuild_item_id
304 , rebuild_serial_number
305 , maintenance_object_id
306 , maintenance_object_type
307 , maintenance_object_source
308 , eam_linear_location_id
309 , class_code
310 , primary_item_id
311 , activity_type
312 , activity_cause
313 , activity_source
314 , work_order_type
315 , status_type
316 , date_released
317 , owning_department
318 , priority
319 , requested_start_date
320 , due_date
321 , shutdown_type
322 , firm_planned_flag
323 , notification_required
324 , tagout_required
325 , plan_maintenance
326 , project_id
327 , task_id
328 --, project_costed
329 , end_item_unit_number
330 , schedule_group_id
331 , bom_revision_date
332 , routing_revision_date
333 , alternate_routing_designator
334 , alternate_bom_designator
335 , routing_revision
336 , bom_revision
337 , parent_wip_entity_id
338 , manual_rebuild_flag
339 , pm_schedule_id
340 , job_type
341 , wip_supply_type
342 , material_account
343 , material_overhead_account
344 , resource_account
345 , outside_processing_account
346 , material_variance_account
347 , resource_variance_account
348 , outside_proc_variance_account
349 , std_cost_adjustment_account
350 , overhead_account
351 , overhead_variance_account
352 , scheduled_start_date
353 , scheduled_completion_date
354 , start_quantity
355 , quantity_completed
356 , quantity_scrapped
357 , net_quantity
358 , common_bom_sequence_id
359 , common_routing_sequence_id
360 , po_creation_time
361 , attribute_category
362 , attribute1
363 , attribute2
364 , attribute3
365 , attribute4
366 , attribute5
367 , attribute6
368 , attribute7
369 , attribute8
370 , attribute9
371 , attribute10
372 , attribute11
373 , attribute12
374 , attribute13
375 , attribute14
376 , attribute15
377 , material_issue_by_mo
378 , issue_zero_cost_flag
379 , last_update_date
380 , last_updated_by
381 , creation_date
382 , created_by
383 , last_update_login
384 , request_id
385 , program_application_id
386 , program_id
387 , program_update_date
388 , source_line_id
389 , source_code
390 )
391 VALUES
392 ( p_eam_wo_rec.wip_entity_id
393 , p_eam_wo_rec.organization_id
394 , p_eam_wo_rec.description
395 , p_eam_wo_rec.asset_number
396 , p_eam_wo_rec.asset_group_id
397 , p_eam_wo_rec.rebuild_item_id
398 , p_eam_wo_rec.rebuild_serial_number
399 , p_eam_wo_rec.maintenance_object_id
400 , p_eam_wo_rec.maintenance_object_type
401 , p_eam_wo_rec.maintenance_object_source
402 , p_eam_wo_rec.eam_linear_location_id
403 , p_eam_wo_rec.class_code
404 , p_eam_wo_rec.asset_activity_id
405 , p_eam_wo_rec.activity_type
406 , p_eam_wo_rec.activity_cause
407 , p_eam_wo_rec.activity_source
408 , p_eam_wo_rec.work_order_type
409 , 17 -- Always create WO in default status, then update to other statuses accordingly
410 , decode(p_eam_wo_rec.status_type,
411 WIP_CONSTANTS.RELEASED, decode(p_eam_wo_rec.date_released, NULL, SYSDATE, decode(sign(p_eam_wo_rec.date_released - sysdate),1,sysdate, p_eam_wo_rec.date_released)),
412 WIP_CONSTANTS.HOLD, NULL,
413 WIP_CONSTANTS.UNRELEASED, NULL,
414 NULL)
415 , p_eam_wo_rec.owning_department
416 , p_eam_wo_rec.priority
417 , p_eam_wo_rec.requested_start_date
418 , p_eam_wo_rec.due_date
419 , p_eam_wo_rec.shutdown_type
420 , p_eam_wo_rec.firm_planned_flag
421 , p_eam_wo_rec.notification_required
422 , p_eam_wo_rec.tagout_required
423 , p_eam_wo_rec.plan_maintenance
424 , p_eam_wo_rec.project_id
425 , p_eam_wo_rec.task_id
426 --, p_eam_wo_rec.project_costed
427 , p_eam_wo_rec.end_item_unit_number
428 , p_eam_wo_rec.schedule_group_id
429 , round(p_eam_wo_rec.bom_revision_date,'MI')
430 , round(p_eam_wo_rec.routing_revision_date,'MI')
431 , p_eam_wo_rec.alternate_routing_designator
432 , p_eam_wo_rec.alternate_bom_designator
433 , p_eam_wo_rec.routing_revision
434 , p_eam_wo_rec.bom_revision
435 , p_eam_wo_rec.parent_wip_entity_id
436 , p_eam_wo_rec.manual_rebuild_flag
437 , p_eam_wo_rec.pm_schedule_id
438 , 3
439 , 7
440 , p_eam_wo_rec.material_account
441 , p_eam_wo_rec.material_overhead_account
442 , p_eam_wo_rec.resource_account
443 , p_eam_wo_rec.outside_processing_account
444 , p_eam_wo_rec.material_variance_account
445 , p_eam_wo_rec.resource_variance_account
446 , p_eam_wo_rec.outside_proc_variance_account
447 , p_eam_wo_rec.std_cost_adjustment_account
448 , p_eam_wo_rec.overhead_account
449 , p_eam_wo_rec.overhead_variance_account
450 , p_eam_wo_rec.scheduled_start_date
451 , p_eam_wo_rec.scheduled_completion_date
452 , p_eam_wo_rec.job_quantity
453 , 0
454 , 0
455 , 1
456 , p_eam_wo_rec.common_bom_sequence_id
457 , p_eam_wo_rec.common_routing_sequence_id
458 , p_eam_wo_rec.po_creation_time
459 , p_eam_wo_rec.attribute_category
460 , p_eam_wo_rec.attribute1
461 , p_eam_wo_rec.attribute2
462 , p_eam_wo_rec.attribute3
463 , p_eam_wo_rec.attribute4
464 , p_eam_wo_rec.attribute5
465 , p_eam_wo_rec.attribute6
466 , p_eam_wo_rec.attribute7
467 , p_eam_wo_rec.attribute8
468 , p_eam_wo_rec.attribute9
469 , p_eam_wo_rec.attribute10
470 , p_eam_wo_rec.attribute11
471 , p_eam_wo_rec.attribute12
472 , p_eam_wo_rec.attribute13
473 , p_eam_wo_rec.attribute14
474 , p_eam_wo_rec.attribute15
475 , p_eam_wo_rec.material_issue_by_mo
476 , p_eam_wo_rec.issue_zero_cost_flag
477 , SYSDATE
478 , FND_GLOBAL.user_id
479 , SYSDATE
480 , FND_GLOBAL.user_id
481 , FND_GLOBAL.login_id
482 , p_eam_wo_rec.request_id
483 , p_eam_wo_rec.program_application_id
484 , p_eam_wo_rec.program_id
485 , SYSDATE
486 , p_eam_wo_rec.source_line_id
487 , p_eam_wo_rec.source_code
488 );
489
490 INSERT INTO WIP_ENTITIES
491 ( wip_entity_id
492 , organization_id
493 , last_update_date
494 , last_updated_by
495 , creation_date
496 , created_by
497 , last_update_login
498 , request_id
499 , program_application_id
500 , program_id
501 , program_update_date
502 , wip_entity_name
503 , entity_type
504 , description
505 , primary_item_id
506 , gen_object_id)
507 VALUES
508 ( p_eam_wo_rec.wip_entity_id
509 , p_eam_wo_rec.organization_id
510 , SYSDATE
511 , FND_GLOBAL.user_id
512 , SYSDATE
513 , FND_GLOBAL.user_id
514 , FND_GLOBAL.login_id
515 , p_eam_wo_rec.request_id
516 , p_eam_wo_rec.program_application_id
517 , p_eam_wo_rec.program_id
518 , SYSDATE
519 , p_eam_wo_rec.wip_entity_name
520 , 6
521 , p_eam_wo_rec.description
522 , p_eam_wo_rec.asset_activity_id
523 , MTL_GEN_OBJECT_ID_S.nextval);
524
525 INSERT INTO EAM_WORK_ORDER_DETAILS
526 (
527 wip_entity_id
528 , organization_id
529 , user_defined_status_id
530 , pending_flag
531 , material_shortage_check_date
532 , material_shortage_flag
533 , workflow_type
534 , warranty_claim_status
535 , cycle_id
536 , seq_id
537 , ds_scheduled_flag
538 , assignment_complete
539 , warranty_active
540 , pm_suggested_start_date
541 , pm_suggested_end_date
542 , pm_base_meter_reading
543 , pm_base_meter
544 , failure_code_required
545 , request_id
546 , program_id
547 , program_application_id
548 , program_update_date
549 , last_update_date
550 , last_updated_by
551 , creation_date
552 , created_by
553 , last_update_login
554 , planner_maintenance
555 )
556 VALUES
557 (
558 p_eam_wo_rec.wip_entity_id
559 , p_eam_wo_rec.organization_id
560 , p_eam_wo_rec.user_defined_status_id
561 , p_eam_wo_rec.pending_flag
562 , p_eam_wo_rec.material_shortage_check_date
563 , p_eam_wo_rec.material_shortage_flag
564 , p_eam_wo_rec.workflow_type
565 , p_eam_wo_rec.warranty_claim_status
566 , p_eam_wo_rec.cycle_id
567 , p_eam_wo_rec.seq_id
568 , p_eam_wo_rec.ds_scheduled_flag
569 , p_eam_wo_rec.assignment_complete
570 , p_eam_wo_rec.warranty_active
571 , p_eam_wo_rec.pm_suggested_start_date
572 , p_eam_wo_rec.pm_suggested_end_date
573 , p_eam_wo_rec.pm_base_meter_reading
574 , p_eam_wo_rec.pm_base_meter
575 , nvl(p_eam_wo_rec.failure_code_required,'N')
576 , p_eam_wo_rec.request_id
577 , p_eam_wo_rec.program_id
578 , p_eam_wo_rec.program_application_id
579 , SYSDATE
580 , SYSDATE
581 , FND_GLOBAL.user_id
582 , SYSDATE
583 , FND_GLOBAL.user_id
584 , FND_GLOBAL.login_id
585 , p_eam_wo_rec.planner_type --vardha
586 );
587
588 EAM_ASSET_LOG_PVT.INSERT_ROW
589 (
590 p_api_version => 1.0,
591 p_event_date => sysdate,
592 p_event_type => 'EAM_SYSTEM_EVENTS',
593 p_event_id => 5,
594 p_organization_id => p_eam_wo_rec.organization_id,
595 p_instance_id => p_eam_wo_rec.maintenance_object_id,
596 p_comments => null,
597 p_reference => p_eam_wo_rec.wip_entity_name,
598 p_ref_id => p_eam_wo_rec.wip_entity_id,
599 p_operable_flag => null,
600 p_reason_code => null,
601 x_return_status => l_asset_ops_return_status,
602 x_msg_count => l_asset_ops_msg_count,
603 x_msg_data => l_asset_ops_msg_data
604 );
605
606
607 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug ('Creating new Work Order') ; END IF;
608 x_return_status := FND_API.G_RET_STS_SUCCESS;
609
610
611 EXCEPTION
612 WHEN OTHERS THEN
613 EAM_ERROR_MESSAGE_PVT.Add_Error_Token
614 ( p_message_name => NULL
615 , p_message_text => G_PKG_NAME ||' :Inserting Record ' || SQLERRM
616 , x_mesg_token_Tbl => x_mesg_token_tbl
617 );
618
619 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
620
621 END Insert_Row;
622
623 /********************************************************************
624 * Procedure : Update_Row
625 * Parameters IN : Work Order column record
626 * Parameters OUT NOCOPY: Message Token Table
627 * Return Status
628 * Purpose : Procedure will perfrom an Update into the
629 * wip_discrete_jobs table.
630 *********************************************************************/
631
632 PROCEDURE Update_Row
633 ( p_eam_wo_rec IN EAM_PROCESS_WO_PUB.eam_wo_rec_type
634 , x_mesg_token_Tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
635 , x_return_Status OUT NOCOPY VARCHAR2
636 )
637 IS
638 BEGIN
639
640
641 IF EAM_PROCESS_WO_PVT.Get_Debug = 'Y' THEN EAM_ERROR_MESSAGE_PVT.Write_Debug('Updating EAM WO '|| p_eam_wo_rec.wip_entity_name); END IF;
642
643 -- bug no 3444091
644 if p_eam_wo_rec.scheduled_start_date > p_eam_wo_rec.scheduled_completion_date then
645 x_return_status := fnd_api.g_ret_sts_error;
646 fnd_message.set_name('EAM','EAM_WO_WORKORDER_DT_ERR');
647 return;
648 end if;
649
650
651 UPDATE WIP_DISCRETE_JOBS
652 SET description = p_eam_wo_rec.description
653 ,rebuild_serial_number = p_eam_wo_rec.rebuild_serial_number -- agaurav - Added the rebuild_serial_number column so that updation of serial number can happen
654 ,rebuild_item_id = p_eam_wo_rec.rebuild_item_id
655 ,asset_number = p_eam_wo_rec.asset_number
656 , asset_group_id = p_eam_wo_rec.asset_group_id
657 , class_code = p_eam_wo_rec.class_code
658 , primary_item_id = p_eam_wo_rec.asset_activity_id
659 , activity_type = p_eam_wo_rec.activity_type
660 , activity_cause = p_eam_wo_rec.activity_cause
661 , activity_source = p_eam_wo_rec.activity_source
662 -- , status_type = p_eam_wo_rec.status_type Status will be updated through status change api
663 , work_order_type = p_eam_wo_rec.work_order_type
664 , start_quantity = p_eam_wo_rec.job_quantity
665 , date_released = p_eam_wo_rec.date_released
666 , owning_department = p_eam_wo_rec.owning_department
667 , priority = p_eam_wo_rec.priority
668 , requested_start_date = p_eam_wo_rec.requested_start_date
669 , due_date = p_eam_wo_rec.due_date
670 , shutdown_type = p_eam_wo_rec.shutdown_type
671 , firm_planned_flag = p_eam_wo_rec.firm_planned_flag
672 , notification_required = p_eam_wo_rec.notification_required
673 , tagout_required = p_eam_wo_rec.tagout_required
674 , plan_maintenance = p_eam_wo_rec.plan_maintenance
675 , project_id = p_eam_wo_rec.project_id
676 , task_id = p_eam_wo_rec.task_id
677 , maintenance_object_id = p_eam_wo_rec.maintenance_object_id --added these 3 fields so that maintenance object id is updateable
678 , maintenance_object_type = p_eam_wo_rec.maintenance_object_type
679 , maintenance_object_source = p_eam_wo_rec.maintenance_object_source
680 , parent_wip_entity_id = p_eam_wo_rec.parent_wip_entity_id /* Added the column so that parent_wip_entity_id is updateable */
681 --, project_costed = p_eam_wo_rec.project_costed
682 , end_item_unit_number = p_eam_wo_rec.end_item_unit_number
683 , schedule_group_id = p_eam_wo_rec.schedule_group_id
684 , bom_revision_date = p_eam_wo_rec.bom_revision_date
685 , routing_revision_date = p_eam_wo_rec.routing_revision_date
686 , alternate_routing_designator = p_eam_wo_rec.alternate_routing_designator
687 , alternate_bom_designator = p_eam_wo_rec.alternate_bom_designator
688 , routing_revision = p_eam_wo_rec.routing_revision
689 , bom_revision = p_eam_wo_rec.bom_revision
690 , manual_rebuild_flag = p_eam_wo_rec.manual_rebuild_flag
691 , material_account = p_eam_wo_rec.material_account
692 , material_overhead_account = p_eam_wo_rec.material_overhead_account
693 , resource_account = p_eam_wo_rec.resource_account
694 , outside_processing_account = p_eam_wo_rec.outside_processing_account
695 , material_variance_account = p_eam_wo_rec.material_variance_account
696 , resource_variance_account = p_eam_wo_rec.resource_variance_account
697 , outside_proc_variance_account= p_eam_wo_rec.outside_proc_variance_account
698 , std_cost_adjustment_account = p_eam_wo_rec.std_cost_adjustment_account
699 , overhead_account = p_eam_wo_rec.overhead_account
700 , overhead_variance_account = p_eam_wo_rec.overhead_variance_account
701 , scheduled_start_date = p_eam_wo_rec.scheduled_start_date
702 , scheduled_completion_date = p_eam_wo_rec.scheduled_completion_date
703 , common_bom_sequence_id = p_eam_wo_rec.common_bom_sequence_id
704 , common_routing_sequence_id = p_eam_wo_rec.common_routing_sequence_id
705 , attribute_category = p_eam_wo_rec.attribute_category
706 , attribute1 = p_eam_wo_rec.attribute1
707 , attribute2 = p_eam_wo_rec.attribute2
708 , attribute3 = p_eam_wo_rec.attribute3
709 , attribute4 = p_eam_wo_rec.attribute4
710 , attribute5 = p_eam_wo_rec.attribute5
711 , attribute6 = p_eam_wo_rec.attribute6
712 , attribute7 = p_eam_wo_rec.attribute7
713 , attribute8 = p_eam_wo_rec.attribute8
714 , attribute9 = p_eam_wo_rec.attribute9
715 , attribute10 = p_eam_wo_rec.attribute10
716 , attribute11 = p_eam_wo_rec.attribute11
717 , attribute12 = p_eam_wo_rec.attribute12
718 , attribute13 = p_eam_wo_rec.attribute13
719 , attribute14 = p_eam_wo_rec.attribute14
720 , attribute15 = p_eam_wo_rec.attribute15
721 , material_issue_by_mo = p_eam_wo_rec.material_issue_by_mo
722 , issue_zero_cost_flag = p_eam_wo_rec.issue_zero_cost_flag
723 , source_line_id = p_eam_wo_rec.source_line_id
724 , source_code = p_eam_wo_rec.source_code
725 , last_update_date = SYSDATE
726 , last_updated_by = FND_GLOBAL.user_id
727 , last_update_login = FND_GLOBAL.login_id
728 , request_id = p_eam_wo_rec.request_id
729 , program_application_id = p_eam_wo_rec.program_application_id
730 , program_id = p_eam_wo_rec.program_id
731 , program_update_date = SYSDATE
732 WHERE wip_entity_id = p_eam_wo_rec.wip_entity_id
733 AND organization_id = p_eam_wo_rec.organization_id;
734
735
736 UPDATE WIP_ENTITIES
737 SET wip_entity_name = p_eam_wo_rec.wip_entity_name
738 , description = p_eam_wo_rec.description
739 , primary_item_id = p_eam_wo_rec.asset_activity_id
740 , last_update_date = SYSDATE
741 , last_updated_by = FND_GLOBAL.user_id
742 , last_update_login = FND_GLOBAL.login_id
743 , request_id = p_eam_wo_rec.request_id
744 , program_application_id = p_eam_wo_rec.program_application_id
745 , program_id = p_eam_wo_rec.program_id
746 , program_update_date = SYSDATE
747 WHERE wip_entity_id = p_eam_wo_rec.wip_entity_id
748 AND organization_id = p_eam_wo_rec.organization_id;
749
750
751 UPDATE EAM_WORK_ORDER_DETAILS
752 SET wip_entity_id = p_eam_wo_rec.wip_entity_id
753 , organization_id = p_eam_wo_rec.organization_id
754 , user_defined_status_id = p_eam_wo_rec.user_defined_status_id
755 , pending_flag = p_eam_wo_rec.pending_flag
756 , material_shortage_check_date = p_eam_wo_rec.material_shortage_check_date
757 , material_shortage_flag = p_eam_wo_rec.material_shortage_flag
758 , workflow_type = p_eam_wo_rec.workflow_type
759 , warranty_claim_status = p_eam_wo_rec.warranty_claim_status
760 , cycle_id = p_eam_wo_rec.cycle_id
761 , seq_id = p_eam_wo_rec.seq_id
762 , ds_scheduled_flag = p_eam_wo_rec.ds_scheduled_flag
763 , assignment_complete = p_eam_wo_rec.assignment_complete
764 , warranty_active = p_eam_wo_rec.warranty_active
765 , pm_suggested_start_date = p_eam_wo_rec.pm_suggested_start_date
766 , pm_suggested_end_date = p_eam_wo_rec.pm_suggested_end_date
767 , pm_base_meter_reading = p_eam_wo_rec.pm_base_meter_reading
768 , pm_base_meter = p_eam_wo_rec.pm_base_meter
769 , failure_code_required = nvl(p_eam_wo_rec.failure_code_required,'N')
770 , request_id = p_eam_wo_rec.request_id
771 , program_id = p_eam_wo_rec.program_id
772 , program_application_id = p_eam_wo_rec.program_application_id
773 , program_update_date = SYSDATE
774 , last_update_date = SYSDATE
775 , last_updated_by = FND_GLOBAL.user_id
776 , last_update_login = FND_GLOBAL.login_id
777 , planner_maintenance = p_eam_wo_rec.planner_type --vardha
778 WHERE wip_entity_id = p_eam_wo_rec.wip_entity_id
779 AND organization_id = p_eam_wo_rec.organization_id;
780
781 x_return_status := FND_API.G_RET_STS_SUCCESS;
782
783 END Update_Row;
784
785 /*********************************************************************
786 * Procedure : Perform_Writes
787 * Parameters IN : Work Order Column Record
788 * Parameters OUT NOCOPY: Messgae Token Table
789 * Return Status
790 * Purpose : This is the only procedure that the user will have
791 * access to when he/she needs to perform any kind of
792 * writes to the wip_discrete_jobs and wip_entities.
793 *********************************************************************/
794
795 PROCEDURE Perform_Writes
796 ( p_eam_wo_rec IN EAM_PROCESS_WO_PUB.eam_wo_rec_type
797 , x_mesg_token_tbl OUT NOCOPY EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type
798 , x_return_status OUT NOCOPY VARCHAR2
799 )
800 IS
801 l_Mesg_Token_tbl EAM_ERROR_MESSAGE_PVT.Mesg_Token_Tbl_Type;
802 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
803 BEGIN
804
805 IF p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_CREATE
806 THEN
807 Insert_Row
808 ( p_eam_wo_rec => p_eam_wo_rec
809 , x_mesg_token_Tbl => l_mesg_token_tbl
810 , x_return_Status => l_return_status
811 );
812 ELSIF p_eam_wo_rec.transaction_type = EAM_PROCESS_WO_PVT.G_OPR_UPDATE
813 THEN
814 Update_Row
815 ( p_eam_wo_rec => p_eam_wo_rec
816 , x_mesg_token_Tbl => l_mesg_token_tbl
817 , x_return_Status => l_return_status
818 );
819
820 END IF;
821
822 x_return_status := l_return_status;
823 x_mesg_token_tbl := l_mesg_token_tbl;
824
825 END Perform_Writes;
826
827 END EAM_WO_UTILITY_PVT;