1 Package Body WIP_EAM_UTILS as
2 /* $Header: wipeamub.pls 120.2 2005/09/06 16:35:44 anjgupta ship $ */
3
4 --------------------------------------------------------------------------
5 -- HISTORY: --
6 -- 04/25/05 Anju Gupta : IB Convergence Transactable Asset Changes --
7 -- Refer to TDD for Detailed Design --
8 --------------------------------------------------------------------------
9
10 -- Procedure to find the default wip accounting class for a work order
11 -- based on pre-defined criteria
12 PROCEDURE DEFAULT_ACC_CLASS(
13 p_org_id IN NUMBER, -- Organization Id
14 p_job_type IN NUMBER, -- Standard/Rebuild
15 p_serial_number IN VARCHAR2, -- Asset Number
16 p_asset_group IN VARCHAR2, -- Asset Group
17 p_parent_wo_name IN VARCHAR2, -- Parent Wip Entity Id
18 p_asset_activity IN VARCHAR2, -- Asset Activity
19 p_project_number IN VARCHAR2, -- Project Number
20 p_task_number IN VARCHAR2, -- Task Number
21 x_class_code OUT NOCOPY VARCHAR2, -- WAC (return value)
22 x_return_status OUT NOCOPY VARCHAR2, -- Return Status
23 x_msg_data OUT NOCOPY VARCHAR2 -- Error messages
24 )
25 IS
26 --Declare variables
27 l_inv_item_id NUMBER := NULL;
28 l_parent_weid NUMBER := NULL;
29 l_project_id NUMBER := NULL;
30 l_task_id NUMBER := NULL;
31 l_asset_activity_id NUMBER := NULL;
32 l_count NUMBER := 0;
33 l_prj_class_code VARCHAR2(10) := '';
34 l_instance_id NUMBER := NULL;
35 l_maintenance_object_type NUMBER :=0;
36
37 BEGIN
38
39 SAVEPOINT DEFAULT_WAC_START;
40
41 x_return_status := 'S';
42
43 -- Cases
44 -- 1. Maintenance Work Order with Project/Task
45 -- 2. Maintenance Work Order with no Project/Task
46 -- 2.1 Default from asset activity association
47 -- 2.2 Default from asset definition
48 -- 2.3 Default from EAM Parameters
49 -- 3. Rebuild WO with a parent.
50 -- 4. Rebuild WO without a parent but with Project/Task
51 -- 5. Rebuild WO without a parent with no Project/Task
52 -- 5.1 Default from Rebuild Activity combination
53 -- 5.2 Default from EAM Parameters
54
55
56
57 -- Get some of the commonly used variables for later use.
58 -- Essentially involves getting the id from the names
59 -- 1. Asset Group Id
60
61 if p_asset_group is not null then
62 /* IB Convergence changes */
63 select inventory_item_id into l_inv_item_id
64 from mtl_system_items_b_kfv msi, mtl_parameters mp
65 where msi.concatenated_segments = p_asset_group
66 and msi.organization_id = mp.organization_id
67 and mp.maint_organization_id = p_org_id
68 and rownum = 1;
69
70 --1.1 Derive Instance_id
71 if p_serial_number is not null then
72 select cii.instance_id
73 into l_instance_id
74 from csi_item_instances cii
75 where cii.inventory_item_id = l_inv_item_id
76 and cii.serial_number = p_serial_number;
77
78 l_maintenance_object_type := 3;
79
80 else
81 l_maintenance_object_type := 2;
82 l_instance_id := l_inv_item_id;
83
84 end if;
85
86 --1.2
87
88 end if;
89
90
91
92 -- 2. Parent Wip Entity Id
93 if p_parent_wo_name is not null then
94 select wip_entity_id into l_parent_weid from wip_entities
95 where wip_entity_name = p_parent_wo_name
96 and organization_id = p_org_id;
97 end if;
98 -- 3. Asset Activity Id
99 -- Activity should always be assigned to the work order organization
100 if p_asset_activity is not null then
101 select inventory_item_id into l_asset_activity_id from
102 mtl_system_items_b_kfv where
103 concatenated_segments = p_asset_activity
104 and organization_id = p_org_id;
105 end if;
106
107 -- 4. Project Id
108 if p_project_number is not null then
109 select ppv.project_id into l_project_id
110 from pjm_projects_v ppv,
111 pjm_project_parameters ppp
112 where ppv.project_id = ppp.project_id
113 and ppp.organization_id = p_org_id
114 and ppv.project_number = p_project_number;
115 end if;
116 -- 5. Task Id
117 if p_project_number is not null and p_task_number is not null then
118 select ppv.project_id, mtv.task_id into
119 l_project_id, l_task_id
120 from pjm_projects_v ppv,
121 pjm_project_parameters ppp,
122 mtl_task_v mtv
123 where ppv.project_id = ppp.project_id
124 and mtv.project_id(+) = ppp.project_id
125 and ppp.organization_id = p_org_id
126 and ppv.project_number = p_project_number
127 and task_number = p_task_number;
128 end if;
129
130 -- If WO is a maintenance work order.
131 if p_job_type = 1 then
132
133 -- Case 1. Maintenance Work Order with Project/Task
134 if p_project_number is not null then
135
136 -- Call the Projects Team's API to return the WAC for the project/task given
137 l_prj_class_code := PJM_UTILS.DEFAULT_WIP_ACCT_CLASS(
138 X_INVENTORY_ORG_ID => p_org_id,
139 X_PROJECT_ID => l_project_id,
140 X_TASK_ID => l_task_id,
141 X_CLASS_TYPE => 6);
142
143 if l_prj_class_code is not null then
144 x_class_code := l_prj_class_code;
145 return;
146 end if;
147 end if;
148
149 -- 2. Maintenance Work Order with no Project/Task; Or if the Project didn't
150 -- have an associated WAC
151
152 -- Case 2.1 From asset activity association
153 if p_asset_activity is not null then
154
155 select count(*)
156 into l_count from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
157 where meaa.asset_activity_id = l_asset_activity_id
158 and meaa.maintenance_object_type = 3
159 and meaa.maintenance_object_id = l_instance_id
160 and eomd.organization_id = p_org_id
161 and meaa.activity_association_id = eomd.object_id
162 and eomd.object_type = 60
163 and eomd.accounting_class_code is not null
164 and nvl(meaa.tmpl_flag, 'N') = 'N';
165
166 if l_count = 1 then
167 select accounting_class_code into x_class_code from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
168 where meaa.asset_activity_id = l_asset_activity_id
169 and meaa.maintenance_object_type = 3
170 and meaa.maintenance_object_id = l_instance_id
171 and eomd.organization_id = p_org_id
172 and meaa.activity_association_id = eomd.object_id
173 and eomd.object_type = 60
174 and nvl(meaa.tmpl_flag, 'N') = 'N';
175 return;
176 end if;
177 end if;
178
179 -- 2.2 From asset definition
180
181 select count(*)
182 into l_count
183 from eam_org_maint_defaults eomd
184 where eomd.organization_id = p_org_id
185 and eomd.object_type = 50
186 and eomd.object_id = l_instance_id
187 and eomd.accounting_class_code is not null;
188 if l_count = 1 then
189 select accounting_class_code into x_class_code from eam_org_maint_defaults eomd
190 where eomd.organization_id = p_org_id
191 and eomd.object_type = 50
192 and eomd.object_id = l_instance_id;
193 return;
194 end if;
195
196 -- 2.3 From EAM Parameters
197 select default_eam_class into x_class_code
198 from wip_eam_parameters where
199 organization_id = p_org_id;
200 return;
201
202 -- Rebuild Work Orders
203 elsif p_job_type = 2 then
204
205 -- Case 3. Rebuild WO with a parent.
206 if l_parent_weid is not null then
207 select class_code into x_class_code from
208 wip_discrete_jobs where
209 wip_entity_id = l_parent_weid
210 and organization_id = p_org_id;
211 return;
212
213 -- 4. Rebuild WO without a parent but with Project/Task
214 elsif (l_parent_weid is null and p_project_number is not null )then
215
216 -- Call the Projects Team's API to return the WAC for the project/task given
217 l_prj_class_code := PJM_UTILS.DEFAULT_WIP_ACCT_CLASS(
218 X_INVENTORY_ORG_ID => p_org_id,
219 X_PROJECT_ID => l_project_id,
220 X_TASK_ID => l_task_id,
221 X_CLASS_TYPE => 6);
222 if l_prj_class_code is not null then
223 x_class_code := l_prj_class_code;
224 return;
225 end if;
226
227 end if;
228
229 -- 5. Rebuild WO without a parent and with no Project/Task; Or if the
230 -- project did not have an associated WAC
231
232 if p_asset_activity is not null then
233 -- 5.1 Default from Rebuild Activity combination
234
235
236 select count(*)
237 into l_count from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
238 where meaa.asset_activity_id = l_asset_activity_id
239 and meaa.maintenance_object_type = l_maintenance_object_type
240 and meaa.maintenance_object_id = l_instance_id
241 and eomd.organization_id = p_org_id
242 and meaa.activity_association_id = eomd.object_id
243 and eomd.object_type = decode(l_maintenance_object_type, 3, 60, 2, 40 )
244 and nvl(meaa.tmpl_flag, 'N') = 'N'
245 and eomd.accounting_class_code is not null;
246
247 if l_count = 1 then
248 select accounting_class_code into x_class_code
249 from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
250 where meaa.asset_activity_id = l_asset_activity_id
251 and meaa.maintenance_object_type = l_maintenance_object_type
252 and meaa.maintenance_object_id = l_instance_id
253 and eomd.organization_id = p_org_id
254 and nvl(meaa.tmpl_flag, 'N') = 'N'
255 and meaa.activity_association_id = eomd.object_id
256 and eomd.object_type = decode(l_maintenance_object_type, 3, 60, 2, 40
257 );
258 return;
259 end if;
260
261 -- Get it from the rebuild item definition
262 else
263 -- Serial controlled rebuild
264 if p_serial_number is not null then
265
266 select count(*)
267 into l_count
268 from eam_org_maint_defaults eomd
269 where eomd.organization_id = p_org_id
270 and eomd.object_type = 50
271 and eomd.object_id = l_instance_id
272 and eomd.accounting_class_code is not null;
273
274 if l_count = 1 then
275 select accounting_class_code into x_class_code from eam_org_maint_defaults eomd
276 where eomd.organization_id = p_org_id
277 and eomd.object_type = 50
278 and eomd.object_id = l_instance_id;
279 return;
280 end if;
281 -- Non serial controlled
282 else
283
284 -- Default it from Eam Parameters
285 select default_eam_class into x_class_code
286 from wip_eam_parameters where
287 organization_id = p_org_id;
288 return;
289
290 end if;
291
292 end if;
293
294 end if;
295
296 -- In case no other case has returned a WAC, then default it from
297 -- the eam parameters.
298 select default_eam_class into x_class_code
299 from wip_eam_parameters where
300 organization_id = p_org_id;
301 return;
302
303 EXCEPTION
304
305 when others then
306 rollback to DEFAULT_WAC_START;
307 x_msg_data := SQLERRM;
308 x_return_status := 'E';
309 -- Default it from eam parameters.
310 select default_eam_class into x_class_code
311 from wip_eam_parameters where
312 organization_id = p_org_id;
313
314 END DEFAULT_ACC_CLASS;
315
316
317
318
319 -- This is a copy of the previous DEFAULT_ACC_CLASS procedure. The only
320 -- difference is that this procedure takes 'id's as input instead of names.
321 -- Procedure to find the default wip accounting class for a work order
322 -- based on pre-defined criteria
323 PROCEDURE DEFAULT_ACC_CLASS(
324 p_org_id IN NUMBER, -- Organization Id
325 p_job_type IN NUMBER, -- Standard/Rebuild
326 p_serial_number IN VARCHAR2, -- Asset Number
327 p_asset_group_id IN NUMBER, -- Asset Group
328 p_parent_wo_id IN NUMBER, -- Parent Wip Entity Id
329 p_asset_activity_id IN NUMBER, -- Asset Activity
330 p_project_id IN NUMBER, -- Project Number
331 p_task_id IN NUMBER, -- Task Number
332 x_class_code OUT NOCOPY VARCHAR2, -- WAC (return value)
333 x_return_status OUT NOCOPY VARCHAR2, -- Return Status
334 x_msg_data OUT NOCOPY VARCHAR2 -- Error messages
335 )
336 IS
337 --Declare variables
338 l_inv_item_id NUMBER := p_asset_group_id;--NULL;
339 l_parent_weid NUMBER := p_parent_wo_id;--NULL;
340 l_project_id NUMBER := p_project_id;--NULL;
341 l_task_id NUMBER := p_task_id;--NULL;
342 l_asset_activity_id NUMBER := p_asset_activity_id;--NULL;
343 l_count NUMBER := 0;
344 l_instance_id NUMBER := NULL;
345 l_prj_class_code VARCHAR2(10) := '';
346
347 l_maintenance_object_type NUMBER :=0;
348 BEGIN
349
350 SAVEPOINT DEFAULT_WAC_START;
351
352 x_return_status := 'S';
353
354 -- Cases
355 -- 1. Maintenance Work Order with Project/Task
356 -- 2. Maintenance Work Order with no Project/Task
357 -- 2.1 Default from asset activity association
358 -- 2.2 Default from asset definition
359 -- 2.3 Default from EAM Parameters
360 -- 3. Rebuild WO with a parent.
361 -- 4. Rebuild WO without a parent but with Project/Task
362 -- 5. Rebuild WO without a parent with no Project/Task
363 -- 5.1 Default from Rebuild Activity combination
364 -- 5.2 Default from EAM Parameters
365
366
367 --1 Derive Instance_id
368 if p_serial_number is not null then
369 select cii.instance_id
370 into l_instance_id
371 from csi_item_instances cii
372 where cii.inventory_item_id = p_asset_group_id
373 and cii.serial_number = p_serial_number;
374
375 l_maintenance_object_type := 3;
376
377 else
378 l_maintenance_object_type := 2;
379 l_instance_id := p_asset_group_id;
380 end if;
381
382 -- If WO is a maintenance work order.
383 if p_job_type = 1 then
384
385 -- Case 1. Maintenance Work Order with Project/Task
386 if p_project_id is not null then
387
388 -- Call the Projects Team's API to return the WAC for the project/task given
389 l_prj_class_code := PJM_UTILS.DEFAULT_WIP_ACCT_CLASS(
390 X_INVENTORY_ORG_ID => p_org_id,
391 X_PROJECT_ID => l_project_id,
392 X_TASK_ID => l_task_id,
393 X_CLASS_TYPE => 6);
394
395 if l_prj_class_code is not null then
396 x_class_code := l_prj_class_code;
397 return;
398 end if;
399 end if;
400
401 -- 2. Maintenance Work Order with no Project/Task; Or if the Project didn't
402 -- have an associated WAC
403
404 -- Case 2.1 From asset activity association
405 if p_asset_activity_id is not null then
406
407
408 select count(*)
409 into l_count from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
410 where meaa.asset_activity_id = l_asset_activity_id
411 and meaa.maintenance_object_type = 3
412 and meaa.maintenance_object_id = l_instance_id
413 and nvl(meaa.tmpl_flag, 'N') = 'N'
414 and eomd.organization_id = p_org_id
415 and meaa.activity_association_id = eomd.object_id
416 and eomd.object_type = 60
417 and eomd.accounting_class_code is not null ;
418
419 if l_count = 1 then
420 select accounting_class_code into x_class_code
421 from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
422 where meaa.asset_activity_id = l_asset_activity_id
423 and meaa.maintenance_object_type = 3
424 and meaa.maintenance_object_id = l_instance_id
425 and nvl(meaa.tmpl_flag, 'N') = 'N'
426 and eomd.organization_id = p_org_id
427 and meaa.activity_association_id = eomd.object_id
428 and eomd.object_type = 60;
429 return;
430 end if;
431 end if;
432
433 -- 2.2 From asset definition
434
435 select count(*)
436 into l_count
437 from eam_org_maint_defaults eomd
438 where eomd.organization_id = p_org_id
439 and eomd.object_type = 50
440 and eomd.object_id = l_instance_id
441 and eomd.accounting_class_code is not null;
442
443 if l_count = 1 then
444 select accounting_class_code into x_class_code from eam_org_maint_defaults eomd
445 where eomd.organization_id = p_org_id
446 and eomd.object_type = 50
447 and eomd.object_id = l_instance_id;
448 return;
449 end if;
450
451 -- 2.3 From EAM Parameters
452 select default_eam_class into x_class_code
453 from wip_eam_parameters where
454 organization_id = p_org_id;
455 return;
456
457 -- Rebuild Work Orders
458 elsif p_job_type = 2 then
459
460 -- Case 3. Rebuild WO with a parent.
461 if l_parent_weid is not null then
462 select class_code into x_class_code from
463 wip_discrete_jobs where
464 wip_entity_id = l_parent_weid
465 and organization_id = p_org_id;
466 return;
467
468 -- 4. Rebuild WO without a parent but with Project/Task
469 elsif l_parent_weid is null and p_project_id is not null then
470
471 -- Call the Projects Team's API to return the WAC for the project/task given
472 l_prj_class_code := PJM_UTILS.DEFAULT_WIP_ACCT_CLASS(
473 X_INVENTORY_ORG_ID => p_org_id,
474 X_PROJECT_ID => l_project_id,
475 X_TASK_ID => l_task_id,
476 X_CLASS_TYPE => 6);
477 if l_prj_class_code is not null then
478 x_class_code := l_prj_class_code;
479 return;
480 end if;
481
482 end if;
483
484 -- 5. Rebuild WO without a parent and with no Project/Task; Or if the
485 -- project did not have an associated WAC
486
487 if p_asset_activity_id is not null then
488 -- 5.1 Default from Rebuild Activity combination
489
490
491
492 select count(*)
493 into l_count from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
494 where meaa.asset_activity_id = l_asset_activity_id
495 and meaa.maintenance_object_type = l_maintenance_object_type
496 and meaa.maintenance_object_id = l_instance_id
497 and eomd.organization_id = p_org_id
498 and meaa.activity_association_id = eomd.object_id
499 and eomd.object_type in (40, 60)
500 and nvl(meaa.tmpl_flag, 'N') = 'N'
501 and eomd.accounting_class_code is not null ;
502
503 if l_count = 1 then
504 select accounting_class_code into x_class_code from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
505 where meaa.asset_activity_id = l_asset_activity_id
506 and meaa.maintenance_object_type = l_maintenance_object_type
507 and meaa.maintenance_object_id = l_instance_id
508 and nvl(meaa.tmpl_flag, 'N') = 'N'
509 and eomd.organization_id = p_org_id
510 and meaa.activity_association_id = eomd.object_id
511 and eomd.object_type in (40, 60);
512 return;
513 end if;
514
515 -- Get it from the rebuild item definition
516 else
517 -- Serial controlled rebuild
518 if p_serial_number is not null then
519
520 select count(*)
521 into l_count
522 from eam_org_maint_defaults eomd
523 where eomd.organization_id = p_org_id
524 and eomd.object_type = 50
525 and eomd.object_id = l_instance_id
526 and eomd.accounting_class_code is not null;
527 if l_count = 1 then
528 select accounting_class_code into x_class_code from eam_org_maint_defaults eomd
529 where eomd.organization_id = p_org_id
530 and eomd.object_type = 50
531 and eomd.object_id = l_instance_id;
532 return;
533 end if;
534 -- Non serial controlled
535 else
536
537 -- Default it from Eam Parameters
538 select default_eam_class into x_class_code
539 from wip_eam_parameters where
540 organization_id = p_org_id;
541 return;
542
543 end if;
544
545 end if;
546
547 end if;
548
549 -- In case no other case has returned a WAC, then default it from
550 -- the eam parameters.
551 select default_eam_class into x_class_code
552 from wip_eam_parameters where
553 organization_id = p_org_id;
554 return;
555
556 EXCEPTION
557
558 when others then
559 rollback to DEFAULT_WAC_START;
560 x_msg_data := SQLERRM;
561 x_return_status := 'E';
562 -- Default it from eam parameters.
563 select default_eam_class into x_class_code
564 from wip_eam_parameters where
565 organization_id = p_org_id;
566
567 END DEFAULT_ACC_CLASS;
568
569
570 -- This procedure copies over the asset attachments,
571 -- asset activity attachments, activity bom attachments
572 -- and activity routing attachments to the work order
573 -- created by the WIP Mass Load.
574 PROCEDURE copy_attachments(
575 copy_asset_attachments IN VARCHAR2, -- Copy Asset Attachments (Y/N).
576 copy_activity_attachments IN VARCHAR2, -- Copy Activity Attachments (Y/N).
577 copy_activity_bom_attachments IN VARCHAR2, -- Copy Activity BOM Attachments (Y/N).
578 copy_activity_rtng_attachments IN VARCHAR2, -- Copy Activity Routing Attachments (Y/N).
579 p_organization_id IN NUMBER, -- Org Id of the Work Order
580 p_wip_entity_id IN NUMBER, -- Wip Ent Id of WO (created thru WML).
581 p_primary_item_id IN NUMBER, -- Asset Activity Id of the activity.
582 p_common_bom_sequence_id IN NUMBER, -- BOM Sequence Id for the activity
583 p_common_routing_sequence_id IN NUMBER -- Routing Sequence Id for the Activity
584 ) IS
585
586 l_operation_sequence_id NUMBER;
587 l_operation_sequence_number NUMBER;
588 l_asset_number VARCHAR2(30);
589 l_inv_item_id NUMBER;
590
591 -- baroy - instead of the ref cursor, use collections for bulk binding.
592 -- TYPE CUR_TYP is ref cursor;
593 -- c_op_cur CUR_TYP;
594 TYPE op_rec_type is record (operation_sequence_id bom_operation_sequences.operation_sequence_id%type,
595 operation_seq_num bom_operation_sequences.operation_seq_num%type);
596
597 op_rec op_rec_type;
598 cursor op_table is select operation_sequence_id, operation_seq_num
599 from bom_operation_sequences
600 where routing_sequence_id = p_common_routing_sequence_id ;
601
602 begin
603
604 -- Standard Start of API savepoint
605 -- l_stmt_num := 10;
606 SAVEPOINT copy_attachments_pvt;
607
608 -- Copy Asset Attachments
609
610 if (copy_asset_attachments = 'Y') then
611
612 begin
613
614 select nvl(asset_number,rebuild_serial_number), nvl(asset_group_id,rebuild_item_id)
615 into l_asset_number, l_inv_item_id
616 from wip_discrete_jobs
617 where wip_entity_id = p_wip_entity_id
618 and organization_id = p_organization_id;
619
620
621
622 if (p_wip_entity_id is not null and l_asset_number is not
623 null and l_inv_item_id is not null ) then
624
625 fnd_attached_documents2_pkg.copy_attachments(
626 X_from_entity_name => 'MTL_SERIAL_NUMBERS',
627 X_from_pk1_value => to_char(p_organization_id),
628 X_from_pk2_value => to_char(l_inv_item_id),
629 X_from_pk3_value => l_asset_number,
630 X_from_pk4_value => '',
631 X_from_pk5_value => '',
632 X_to_entity_name => 'EAM_WORK_ORDERS',
633 X_to_pk1_value => to_char(p_organization_id),
634 X_to_pk2_value => to_char(p_wip_entity_id),
635 X_to_pk3_value => '',
636 X_to_pk4_value => '',
637 X_to_pk5_value => '',
638 X_created_by => fnd_global.user_id,
639 X_last_update_login => fnd_global.login_id
640 -- X_program_application_id=> '',
641 -- X_program_id => '',
642 -- X_request_id => ''
643 );
644
645 end if; -- end of check for p_wip_entity_id and l_asset_number
646
647 end;
648
649 end if ; -- End of Copy Asset Attachments
650
651 -- Copy Activity Attachments
652 if (copy_activity_attachments = 'Y') then
653
654 if p_primary_item_id is not null then
655
656 fnd_attached_documents2_pkg.copy_attachments(
657 X_from_entity_name => 'MTL_SYSTEM_ITEMS',
658 X_from_pk1_value => to_char(p_organization_id),
659 X_from_pk2_value => to_char(p_primary_item_id),
660 X_from_pk3_value => '',
661 X_from_pk4_value => '',
662 X_from_pk5_value => '',
663 X_to_entity_name => 'EAM_WORK_ORDERS',
664 X_to_pk1_value => to_char(p_organization_id),
665 X_to_pk2_value => to_char(p_wip_entity_id),
666 X_to_pk3_value => '',
667 X_to_pk4_value => '',
668 X_to_pk5_value => '',
669 X_created_by => fnd_global.user_id,
670 X_last_update_login => fnd_global.login_id
671 -- X_program_application_id=> '',
672 -- X_program_id => '',
673 -- X_request_id => ''
674 );
675
676 end if;
677
678 end if; -- End of Copy Activity Attachments
679
680 -- Copy Attachments from Activity BOM
681
682 if (copy_activity_bom_attachments = 'Y') then
683
684 if p_common_bom_sequence_id is not null then
685
686 fnd_attached_documents2_pkg.copy_attachments(
687 X_from_entity_name => 'BOM_BILL_OF_MATERIALS',
688 X_from_pk1_value => to_char(p_common_bom_sequence_id),
689 X_from_pk2_value => '',
690 X_from_pk3_value => '',
691 X_from_pk4_value => '',
692 X_from_pk5_value => '',
693 X_to_entity_name => 'EAM_WORK_ORDERS',
694 X_to_pk1_value => to_char(p_organization_id),
695 X_to_pk2_value => to_char(p_wip_entity_id),
696 X_to_pk3_value => '',
697 X_to_pk4_value => '',
698 X_to_pk5_value => '',
699 X_created_by => fnd_global.user_id,
700 X_last_update_login => fnd_global.login_id
701 -- X_program_application_id=> '',
702 -- X_program_id => '',
703 -- X_request_id => ''
704 );
705
706 end if; -- End of function
707
708 end if; -- end of copy bom attachments
709
710 if (copy_activity_rtng_attachments = 'Y') then
711
712 if (p_common_routing_sequence_id is not null) then
713
714 fnd_attached_documents2_pkg.copy_attachments(
715 X_from_entity_name => 'BOM_OPERATIONAL_ROUTINGS',
716 X_from_pk1_value => to_char(p_common_routing_sequence_id),
717 X_from_pk2_value => '',
718 X_from_pk3_value => '',
719 X_from_pk4_value => '',
720 X_from_pk5_value => '',
721 X_to_entity_name => 'EAM_WORK_ORDERS',
722 X_to_pk1_value => to_char(p_organization_id),
723 X_to_pk2_value => to_char(p_wip_entity_id),
724 X_to_pk3_value => '',
725 X_to_pk4_value => '',
726 X_to_pk5_value => '',
727 X_created_by => fnd_global.user_id,
728 X_last_update_login => fnd_global.login_id
729 -- X_program_application_id=> '',
730 -- X_program_id => '',
731 -- X_request_id => ''
732 );
733
734 -- Copy Attachments from Activity Routing
735 -- open c_op_cur for 'select operation_sequence_id, operation_seq_num from bom_operation_sequences where routing_sequence_id = ' || p_common_routing_sequence_id ;
736
737 -- l_stmt_num := 75;
738
739 LOOP FETCH op_table into op_rec;
740
741 l_operation_sequence_id := op_rec.operation_sequence_id;
742 l_operation_sequence_number := op_rec.operation_seq_num;
743
744 -- l_stmt_num := 80;
745
746 if l_operation_sequence_id is not null then
747
748 fnd_attached_documents2_pkg.copy_attachments(
749 X_from_entity_name => 'BOM_OPERATION_SEQUENCES',
750 X_from_pk1_value => to_char(l_operation_sequence_id),
751 X_from_pk2_value => '',
752 X_from_pk3_value => '',
753 X_from_pk4_value => '',
754 X_from_pk5_value => '',
755 X_to_entity_name => 'EAM_DISCRETE_OPERATIONS',
756 X_to_pk1_value => to_char(p_wip_entity_id),
757 X_to_pk2_value => to_char(l_operation_sequence_number),
758 X_to_pk3_value => to_char(p_organization_id),
759 X_to_pk4_value => '',
760 X_to_pk5_value => '',
761 X_created_by => fnd_global.user_id,
762 X_last_update_login => fnd_global.login_id
763 -- X_program_application_id=> '',
764 -- X_program_id => '',
765 -- X_request_id => ''
766 );
767
768 end if;
769
770 exit when op_table%NOTFOUND;
771
772 end loop; -- for the op_table loop.
773
774 close op_table;
775
776 end if ; -- End of check for p_common_routing_sequence_id
777
778 end if; -- End of Copy Routing Attachments
779
780
781 EXCEPTION
782 WHEN OTHERS THEN
783 ROLLBACK TO copy_attachments_pvt;
784
785 END copy_attachments;
786
787
788
789
790 procedure create_default_operation
791 ( p_organization_id IN NUMBER
792 ,p_wip_entity_id IN NUMBER
793 ) IS
794
795 l_wip_entity_id NUMBER;
796 l_operation_exist NUMBER := 0;
797 l_description VARCHAR2(720);
798 l_organization_id NUMBER;
799 l_owning_department_id NUMBER;
800 l_start_date DATE;
801 l_completion_date DATE;
802 l_count number;
803 l_min_op_seq_num number;
804 l_department_id number;
805
806
807
808 BEGIN
809
810 fnd_message.set_name('EAM', 'EAM_WO_DEFAULT_OP');
811
812 l_description := SUBSTRB(fnd_message.get, 1, 240);
813 l_wip_entity_id := p_wip_entity_id;
814 l_organization_id := p_organization_id;
815
816
817 begin
818 SELECT nvl(COUNT(*),0)
819 into l_operation_exist
820 FROM WIP_OPERATIONS WO
821 WHERE WO.WIP_ENTITY_ID = l_wip_entity_id;
822
823 IF ((l_operation_exist=0)) then
824
825
826 select scheduled_start_date,
827 scheduled_completion_date,
828 owning_department
829 into l_start_date,
830 l_completion_date,
831 l_owning_department_id
832 from wip_discrete_jobs
833 where wip_entity_id = l_wip_entity_id
834 and organization_id = l_organization_id;
835
836 if (l_owning_department_id is null) then
837 /* Changes for IB convergence */
838 /* select distinct msn.owning_department_id
839 into l_owning_department_id
840 from wip_discrete_jobs wdj,mtl_serial_numbers msn
841 where wdj.asset_group_id = msn.inventory_item_id (+)
842 and wdj.organization_id = msn.current_organization_id (+)
843 and wdj.asset_number = msn.serial_number (+)
844 and wdj.wip_entity_id = l_wip_entity_id
845 and wdj.organization_id = l_organization_id;*/
846
847 select eomd.owning_department_id
848 into l_owning_department_id
849 from eam_org_maint_defaults eomd, wip_discrete_jobs wdj
850 where wdj.maintenance_object_type = 3
851 and wdj.organization_id = eomd.organization_id (+)
852 and eomd.object_type (+) = 50
853 and eomd.object_id (+) = wdj.maintenance_object_id
854 and wdj.wip_entity_id = l_wip_entity_id
855 and wdj.organization_id = l_organization_id;
856
857 end if;
858
859
860 -- insert
861 insert into wip_operations
862 (
863 wip_entity_id
864 ,operation_seq_num
865 ,organization_id
866 ,repetitive_schedule_id
867 ,last_update_date
868 ,last_updated_by
869 ,creation_date
870 ,created_by
871 ,last_update_login
872 ,operation_sequence_id
873 ,standard_operation_id
874 ,department_id
875 ,description
876 ,scheduled_quantity
877 ,quantity_in_queue
878 ,quantity_running
879 ,quantity_waiting_to_move
880 ,quantity_rejected
881 ,quantity_scrapped
882 ,quantity_completed
883 ,first_unit_start_date
884 ,first_unit_completion_date
885 ,last_unit_start_date
886 ,last_unit_completion_date
887 ,previous_operation_seq_num
888 ,next_operation_seq_num
889 ,count_point_type
890 ,backflush_flag
891 ,minimum_transfer_quantity
892 ,date_last_moved
893 ,wf_itemtype
894 ,wf_itemkey
895 ,operation_yield
896 ,operation_yield_enabled
897 ,pre_split_quantity
898 ,operation_completed
899 ,shutdown_type
900 ,x_pos
901 ,y_pos
902 )
903 values
904 (
905 l_wip_entity_id
906 ,10
907 ,l_organization_id
908 ,null -- repetitive schedule id
909 ,sysdate -- last_update_date
910 ,FND_GLOBAL.USER_ID
911 ,sysdate -- creation_date
912 ,FND_GLOBAL.USER_ID
913 ,FND_GLOBAL.LOGIN_ID
914 ,null -- operation_sequence_id
915 ,null -- standard_operation_id
916 ,l_owning_department_id
917 ,l_description
918 ,1 -- scheduled_quantity
919 ,1 -- quantity_in_queue
920 ,1 -- quantity_running
921 ,1 -- quantity_waiting_to_move
922 ,0 -- quantity_rejected
923 ,1 -- quantity_scrapped
924 ,1 -- quantity_completed
925 ,l_start_date
926 ,l_completion_date
927 ,l_start_date
928 ,l_completion_date
929 ,null -- previous_operation_seq_num
930 ,null -- next_operation_seq_num
931 ,1 -- count_point_type
932 ,1 -- backflush_flag
933 ,1 -- minimum_transfer_quantity
934 ,null -- date_last_moved
935 ,null -- wf_itemtype
936 ,null -- wf_itemkey
937 ,null -- operation_yield
938 ,null -- operation_yield_enabled
939 ,null -- pre_split_quantity
940 ,null -- operation_completed
941 ,null -- shutdown_type
942 ,null -- x_pos
943 ,null -- y_pos
944 );
945
946 else -- else for operation_exist check
947 null;
948
949 end if;
950
951 /* Code added for updating material operation */
952
953
954 begin
955 select count(*)
956 into l_count
957 from wip_requirement_operations_v
958 where organization_id = p_organization_id
959 and wip_entity_id = p_wip_entity_id
960 and operation_seq_num = 1;
961
962 if l_count <> 0 then
963 select min(operation_seq_num)
964 into l_min_op_seq_num
965 from wip_operations
966 where organization_id = p_organization_id and
967 wip_entity_id = p_wip_entity_id;
968
969 if (l_min_op_seq_num is not null) then
970 select department_id into l_department_id
971 from wip_operations
972 where organization_id = p_organization_id and
973 wip_entity_id = p_wip_entity_id
974 and operation_seq_num = l_min_op_seq_num;
975 end if;
976
977 update wip_requirement_operations
978 set operation_seq_num = l_min_op_seq_num,
979 department_id = l_department_id
980 where operation_seq_num = 1 and
981 organization_id = p_organization_id and
982 wip_entity_id = p_wip_entity_id;
983
984 end if;
985
986
987 select count(*)
988 into l_count
989 from wip_eam_direct_items
990 where organization_id = p_organization_id
991 and wip_entity_id = p_wip_entity_id
992 and operation_seq_num = 1;
993
994 if l_count <> 0 then
995 select min(operation_seq_num)
996 into l_min_op_seq_num
997 from wip_operations
998 where organization_id = p_organization_id and
999 wip_entity_id = p_wip_entity_id;
1000
1001 if (l_min_op_seq_num is not null) then
1002 select department_id into l_department_id
1003 from wip_operations
1004 where organization_id = p_organization_id and
1005 wip_entity_id = p_wip_entity_id
1006 and operation_seq_num = l_min_op_seq_num;
1007 end if;
1008
1009 update wip_eam_direct_items
1010 set operation_seq_num = l_min_op_seq_num,
1011 department_id = l_department_id
1012 where operation_seq_num = 1 and
1013 organization_id = p_organization_id and
1014 wip_entity_id = p_wip_entity_id;
1015
1016 end if;
1017
1018 end;
1019
1020
1021 /* End of Check for Materials Operation */
1022
1023 end ; -- end for operation existence check
1024
1025 END create_default_operation; -- dml
1026
1027
1028
1029
1030
1031 END WIP_EAM_UTILS;
[Home] [Help]