1 Package Body WIP_EAM_UTILS as
2 /* $Header: wipeamub.pls 120.3.12020000.2 2012/09/05 05:56:51 vboddapa 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 /*+ leading( ppv ) */ 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 -- start of bug fix : 13366445
487 if p_asset_activity_id is not null then
488 -- 5.1 Default from Rebuild Activity combination
489
490 select count(*)
491 into l_count from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
492 where meaa.asset_activity_id = l_asset_activity_id
493 and meaa.maintenance_object_type = l_maintenance_object_type
494 and meaa.maintenance_object_id = l_instance_id
495 and eomd.organization_id = p_org_id
496 and meaa.activity_association_id = eomd.object_id
497 and eomd.object_type in (40, 60)
498 and nvl(meaa.tmpl_flag, 'N') = 'N'
499 and eomd.accounting_class_code is not null ;
500
501 if l_count = 1 then
502 select accounting_class_code into x_class_code from mtl_eam_asset_activities meaa, eam_org_maint_defaults eomd
503 where meaa.asset_activity_id = l_asset_activity_id
504 and meaa.maintenance_object_type = l_maintenance_object_type
505 and meaa.maintenance_object_id = l_instance_id
506 and nvl(meaa.tmpl_flag, 'N') = 'N'
507 and eomd.organization_id = p_org_id
508 and meaa.activity_association_id = eomd.object_id
509 and eomd.object_type in (40, 60);
510 return;
511 end if;
512 end if;
513
514 -- Get it from the rebuild item definition
515 -- Serial controlled rebuild
516 if p_serial_number is not null then
517
518 select count(*)
519 into l_count
520 from eam_org_maint_defaults eomd
521 where eomd.organization_id = p_org_id
522 and eomd.object_type = 50
523 and eomd.object_id = l_instance_id
524 and eomd.accounting_class_code is not null;
525 if l_count = 1 then
526 select accounting_class_code into x_class_code from eam_org_maint_defaults eomd
527 where eomd.organization_id = p_org_id
528 and eomd.object_type = 50
529 and eomd.object_id = l_instance_id;
530 return;
531 end if;
532 end if;
533 end if;
534 -- end of bug fix : 13366445
535 -- For Non serial controlled
536 -- In case no other case has returned a WAC, then default it from
537 -- the 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 EXCEPTION
544
545 when others then
546 rollback to DEFAULT_WAC_START;
547 x_msg_data := SQLERRM;
548 x_return_status := 'E';
549 -- Default it from eam parameters.
550 select default_eam_class into x_class_code
551 from wip_eam_parameters where
552 organization_id = p_org_id;
553
554 END DEFAULT_ACC_CLASS;
555
556
557 -- This procedure copies over the asset attachments,
558 -- asset activity attachments, activity bom attachments
559 -- and activity routing attachments to the work order
560 -- created by the WIP Mass Load.
561 PROCEDURE copy_attachments(
562 copy_asset_attachments IN VARCHAR2, -- Copy Asset Attachments (Y/N).
563 copy_activity_attachments IN VARCHAR2, -- Copy Activity Attachments (Y/N).
564 copy_activity_bom_attachments IN VARCHAR2, -- Copy Activity BOM Attachments (Y/N).
565 copy_activity_rtng_attachments IN VARCHAR2, -- Copy Activity Routing Attachments (Y/N).
566 p_organization_id IN NUMBER, -- Org Id of the Work Order
567 p_wip_entity_id IN NUMBER, -- Wip Ent Id of WO (created thru WML).
568 p_primary_item_id IN NUMBER, -- Asset Activity Id of the activity.
569 p_common_bom_sequence_id IN NUMBER, -- BOM Sequence Id for the activity
570 p_common_routing_sequence_id IN NUMBER -- Routing Sequence Id for the Activity
571 ) IS
572
573 l_operation_sequence_id NUMBER;
574 l_operation_sequence_number NUMBER;
575 l_asset_number VARCHAR2(30);
576 l_inv_item_id NUMBER;
577
578 -- baroy - instead of the ref cursor, use collections for bulk binding.
579 -- TYPE CUR_TYP is ref cursor;
580 -- c_op_cur CUR_TYP;
581 TYPE op_rec_type is record (operation_sequence_id bom_operation_sequences.operation_sequence_id%type,
582 operation_seq_num bom_operation_sequences.operation_seq_num%type);
583
584 op_rec op_rec_type;
585 cursor op_table is select operation_sequence_id, operation_seq_num
586 from bom_operation_sequences
587 where routing_sequence_id = p_common_routing_sequence_id ;
588
589 begin
590
591 -- Standard Start of API savepoint
592 -- l_stmt_num := 10;
593 SAVEPOINT copy_attachments_pvt;
594
595 -- Copy Asset Attachments
596
597 if (copy_asset_attachments = 'Y') then
598
599 begin
600
601 select nvl(asset_number,rebuild_serial_number), nvl(asset_group_id,rebuild_item_id)
602 into l_asset_number, l_inv_item_id
603 from wip_discrete_jobs
604 where wip_entity_id = p_wip_entity_id
605 and organization_id = p_organization_id;
606
607
608
609 if (p_wip_entity_id is not null and l_asset_number is not
610 null and l_inv_item_id is not null ) then
611
612 fnd_attached_documents2_pkg.copy_attachments(
613 X_from_entity_name => 'MTL_SERIAL_NUMBERS',
614 X_from_pk1_value => to_char(p_organization_id),
615 X_from_pk2_value => to_char(l_inv_item_id),
616 X_from_pk3_value => l_asset_number,
617 X_from_pk4_value => '',
618 X_from_pk5_value => '',
619 X_to_entity_name => 'EAM_WORK_ORDERS',
620 X_to_pk1_value => to_char(p_organization_id),
621 X_to_pk2_value => to_char(p_wip_entity_id),
622 X_to_pk3_value => '',
623 X_to_pk4_value => '',
624 X_to_pk5_value => '',
625 X_created_by => fnd_global.user_id,
626 X_last_update_login => fnd_global.login_id
627 -- X_program_application_id=> '',
628 -- X_program_id => '',
629 -- X_request_id => ''
630 );
631
632 end if; -- end of check for p_wip_entity_id and l_asset_number
633
634 end;
635
636 end if ; -- End of Copy Asset Attachments
637
638 -- Copy Activity Attachments
639 if (copy_activity_attachments = 'Y') then
640
641 if p_primary_item_id is not null then
642
643 fnd_attached_documents2_pkg.copy_attachments(
644 X_from_entity_name => 'MTL_SYSTEM_ITEMS',
645 X_from_pk1_value => to_char(p_organization_id),
646 X_from_pk2_value => to_char(p_primary_item_id),
647 X_from_pk3_value => '',
648 X_from_pk4_value => '',
649 X_from_pk5_value => '',
650 X_to_entity_name => 'EAM_WORK_ORDERS',
651 X_to_pk1_value => to_char(p_organization_id),
652 X_to_pk2_value => to_char(p_wip_entity_id),
653 X_to_pk3_value => '',
654 X_to_pk4_value => '',
655 X_to_pk5_value => '',
656 X_created_by => fnd_global.user_id,
657 X_last_update_login => fnd_global.login_id
658 -- X_program_application_id=> '',
659 -- X_program_id => '',
660 -- X_request_id => ''
661 );
662
663 end if;
664
665 end if; -- End of Copy Activity Attachments
666
667 -- Copy Attachments from Activity BOM
668
669 if (copy_activity_bom_attachments = 'Y') then
670
671 if p_common_bom_sequence_id is not null then
672
673 fnd_attached_documents2_pkg.copy_attachments(
674 X_from_entity_name => 'BOM_BILL_OF_MATERIALS',
675 X_from_pk1_value => to_char(p_common_bom_sequence_id),
676 X_from_pk2_value => '',
677 X_from_pk3_value => '',
678 X_from_pk4_value => '',
679 X_from_pk5_value => '',
680 X_to_entity_name => 'EAM_WORK_ORDERS',
681 X_to_pk1_value => to_char(p_organization_id),
682 X_to_pk2_value => to_char(p_wip_entity_id),
683 X_to_pk3_value => '',
684 X_to_pk4_value => '',
685 X_to_pk5_value => '',
686 X_created_by => fnd_global.user_id,
687 X_last_update_login => fnd_global.login_id
688 -- X_program_application_id=> '',
689 -- X_program_id => '',
690 -- X_request_id => ''
691 );
692
693 end if; -- End of function
694
695 end if; -- end of copy bom attachments
696
697 if (copy_activity_rtng_attachments = 'Y') then
698
699 if (p_common_routing_sequence_id is not null) then
700
701 fnd_attached_documents2_pkg.copy_attachments(
702 X_from_entity_name => 'BOM_OPERATIONAL_ROUTINGS',
703 X_from_pk1_value => to_char(p_common_routing_sequence_id),
704 X_from_pk2_value => '',
705 X_from_pk3_value => '',
706 X_from_pk4_value => '',
707 X_from_pk5_value => '',
708 X_to_entity_name => 'EAM_WORK_ORDERS',
709 X_to_pk1_value => to_char(p_organization_id),
710 X_to_pk2_value => to_char(p_wip_entity_id),
711 X_to_pk3_value => '',
712 X_to_pk4_value => '',
713 X_to_pk5_value => '',
714 X_created_by => fnd_global.user_id,
715 X_last_update_login => fnd_global.login_id
716 -- X_program_application_id=> '',
717 -- X_program_id => '',
718 -- X_request_id => ''
719 );
720
721 -- Copy Attachments from Activity Routing
722 -- 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 ;
723
724 -- l_stmt_num := 75;
725
726 LOOP FETCH op_table into op_rec;
727
728 l_operation_sequence_id := op_rec.operation_sequence_id;
729 l_operation_sequence_number := op_rec.operation_seq_num;
730
731 -- l_stmt_num := 80;
732
733 if l_operation_sequence_id is not null then
734
735 fnd_attached_documents2_pkg.copy_attachments(
736 X_from_entity_name => 'BOM_OPERATION_SEQUENCES',
737 X_from_pk1_value => to_char(l_operation_sequence_id),
738 X_from_pk2_value => '',
739 X_from_pk3_value => '',
740 X_from_pk4_value => '',
741 X_from_pk5_value => '',
742 X_to_entity_name => 'EAM_DISCRETE_OPERATIONS',
743 X_to_pk1_value => to_char(p_wip_entity_id),
744 X_to_pk2_value => to_char(l_operation_sequence_number),
745 X_to_pk3_value => to_char(p_organization_id),
746 X_to_pk4_value => '',
747 X_to_pk5_value => '',
748 X_created_by => fnd_global.user_id,
749 X_last_update_login => fnd_global.login_id
750 -- X_program_application_id=> '',
751 -- X_program_id => '',
752 -- X_request_id => ''
753 );
754
755 end if;
756
757 exit when op_table%NOTFOUND;
758
759 end loop; -- for the op_table loop.
760
761 close op_table;
762
763 end if ; -- End of check for p_common_routing_sequence_id
764
765 end if; -- End of Copy Routing Attachments
766
767
768 EXCEPTION
769 WHEN OTHERS THEN
770 ROLLBACK TO copy_attachments_pvt;
771
772 END copy_attachments;
773
774
775
776
777 procedure create_default_operation
778 ( p_organization_id IN NUMBER
779 ,p_wip_entity_id IN NUMBER
780 ) IS
781
782 l_wip_entity_id NUMBER;
783 l_operation_exist NUMBER := 0;
784 l_description VARCHAR2(720);
785 l_organization_id NUMBER;
786 l_owning_department_id NUMBER;
787 l_start_date DATE;
788 l_completion_date DATE;
789 l_count number;
790 l_min_op_seq_num number;
791 l_department_id number;
792
793
794
795 BEGIN
796
797 fnd_message.set_name('EAM', 'EAM_WO_DEFAULT_OP');
798
799 l_description := SUBSTRB(fnd_message.get, 1, 240);
800 l_wip_entity_id := p_wip_entity_id;
801 l_organization_id := p_organization_id;
802
803
804 begin
805 SELECT nvl(COUNT(*),0)
806 into l_operation_exist
807 FROM WIP_OPERATIONS WO
808 WHERE WO.WIP_ENTITY_ID = l_wip_entity_id;
809
810 IF ((l_operation_exist=0)) then
811
812
813 select scheduled_start_date,
814 scheduled_completion_date,
815 owning_department
816 into l_start_date,
817 l_completion_date,
818 l_owning_department_id
819 from wip_discrete_jobs
820 where wip_entity_id = l_wip_entity_id
821 and organization_id = l_organization_id;
822
823 if (l_owning_department_id is null) then
824 /* Changes for IB convergence */
825 /* select distinct msn.owning_department_id
826 into l_owning_department_id
827 from wip_discrete_jobs wdj,mtl_serial_numbers msn
828 where wdj.asset_group_id = msn.inventory_item_id (+)
829 and wdj.organization_id = msn.current_organization_id (+)
830 and wdj.asset_number = msn.serial_number (+)
831 and wdj.wip_entity_id = l_wip_entity_id
832 and wdj.organization_id = l_organization_id;*/
833
834 select eomd.owning_department_id
835 into l_owning_department_id
836 from eam_org_maint_defaults eomd, wip_discrete_jobs wdj
837 where wdj.maintenance_object_type = 3
838 and wdj.organization_id = eomd.organization_id (+)
839 and eomd.object_type (+) = 50
840 and eomd.object_id (+) = wdj.maintenance_object_id
841 and wdj.wip_entity_id = l_wip_entity_id
842 and wdj.organization_id = l_organization_id;
843
844 end if;
845
846
847 -- insert
848 insert into wip_operations
849 (
850 wip_entity_id
851 ,operation_seq_num
852 ,organization_id
853 ,repetitive_schedule_id
854 ,last_update_date
855 ,last_updated_by
856 ,creation_date
857 ,created_by
858 ,last_update_login
859 ,operation_sequence_id
860 ,standard_operation_id
861 ,department_id
862 ,description
863 ,scheduled_quantity
864 ,quantity_in_queue
865 ,quantity_running
866 ,quantity_waiting_to_move
867 ,quantity_rejected
868 ,quantity_scrapped
869 ,quantity_completed
870 ,first_unit_start_date
871 ,first_unit_completion_date
872 ,last_unit_start_date
873 ,last_unit_completion_date
874 ,previous_operation_seq_num
875 ,next_operation_seq_num
876 ,count_point_type
877 ,backflush_flag
878 ,minimum_transfer_quantity
879 ,date_last_moved
880 ,wf_itemtype
881 ,wf_itemkey
882 ,operation_yield
883 ,operation_yield_enabled
884 ,pre_split_quantity
885 ,operation_completed
886 ,shutdown_type
887 ,x_pos
888 ,y_pos
889 )
890 values
891 (
892 l_wip_entity_id
893 ,10
894 ,l_organization_id
895 ,null -- repetitive schedule id
896 ,sysdate -- last_update_date
897 ,FND_GLOBAL.USER_ID
898 ,sysdate -- creation_date
899 ,FND_GLOBAL.USER_ID
900 ,FND_GLOBAL.LOGIN_ID
901 ,null -- operation_sequence_id
902 ,null -- standard_operation_id
903 ,l_owning_department_id
904 ,l_description
905 ,1 -- scheduled_quantity
906 ,1 -- quantity_in_queue
907 ,1 -- quantity_running
908 ,1 -- quantity_waiting_to_move
909 ,0 -- quantity_rejected
910 ,1 -- quantity_scrapped
911 ,1 -- quantity_completed
912 ,l_start_date
913 ,l_completion_date
914 ,l_start_date
915 ,l_completion_date
916 ,null -- previous_operation_seq_num
917 ,null -- next_operation_seq_num
918 ,1 -- count_point_type
919 ,1 -- backflush_flag
920 ,1 -- minimum_transfer_quantity
921 ,null -- date_last_moved
922 ,null -- wf_itemtype
923 ,null -- wf_itemkey
924 ,null -- operation_yield
925 ,null -- operation_yield_enabled
926 ,null -- pre_split_quantity
927 ,null -- operation_completed
928 ,null -- shutdown_type
929 ,null -- x_pos
930 ,null -- y_pos
931 );
932
933 else -- else for operation_exist check
934 null;
935
936 end if;
937
938 /* Code added for updating material operation */
939
940
941 begin
942 select count(*)
943 into l_count
944 from wip_requirement_operations_v
945 where organization_id = p_organization_id
946 and wip_entity_id = p_wip_entity_id
947 and operation_seq_num = 1;
948
949 if l_count <> 0 then
950 select min(operation_seq_num)
951 into l_min_op_seq_num
952 from wip_operations
953 where organization_id = p_organization_id and
954 wip_entity_id = p_wip_entity_id;
955
956 if (l_min_op_seq_num is not null) then
957 select department_id into l_department_id
958 from wip_operations
959 where organization_id = p_organization_id and
960 wip_entity_id = p_wip_entity_id
961 and operation_seq_num = l_min_op_seq_num;
962 end if;
963
964 update wip_requirement_operations
965 set operation_seq_num = l_min_op_seq_num,
966 department_id = l_department_id
967 where operation_seq_num = 1 and
968 organization_id = p_organization_id and
969 wip_entity_id = p_wip_entity_id;
970
971 end if;
972
973
974 select count(*)
975 into l_count
976 from wip_eam_direct_items
977 where organization_id = p_organization_id
978 and wip_entity_id = p_wip_entity_id
979 and operation_seq_num = 1;
980
981 if l_count <> 0 then
982 select min(operation_seq_num)
983 into l_min_op_seq_num
984 from wip_operations
985 where organization_id = p_organization_id and
986 wip_entity_id = p_wip_entity_id;
987
988 if (l_min_op_seq_num is not null) then
989 select department_id into l_department_id
990 from wip_operations
991 where organization_id = p_organization_id and
992 wip_entity_id = p_wip_entity_id
993 and operation_seq_num = l_min_op_seq_num;
994 end if;
995
996 update wip_eam_direct_items
997 set operation_seq_num = l_min_op_seq_num,
998 department_id = l_department_id
999 where operation_seq_num = 1 and
1000 organization_id = p_organization_id and
1001 wip_entity_id = p_wip_entity_id;
1002
1003 end if;
1004
1005 end;
1006
1007
1008 /* End of Check for Materials Operation */
1009
1010 end ; -- end for operation existence check
1011
1012 END create_default_operation; -- dml
1013
1014
1015
1016
1017
1018 END WIP_EAM_UTILS;
[Home] [Help]