1 PACKAGE BODY ENG_Default_Revised_Item AS
2 /* $Header: ENGDRITB.pls 120.9 2008/01/24 02:17:56 atjen ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ENG_Default_Revised_Item';
7
8 -- Package global used within the package.
9
10 g_revised_item_rec ENG_Eco_PUB.Revised_Item_Rec_Type;
11 g_rev_item_unexp_rec Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type;
12
13 -- Local Function used for defualting.
14
15 /***************************************************************************
16 * Function : Get_Status_Type
17 * Returns : NUMBER
18 * Purpose : Function will look at the ECO and will return the status
19 * type of the ECO as the default status type for the revised
20 * item.
21 *****************************************************************************/
22 FUNCTION Get_Status_Type
23 RETURN NUMBER
24 IS
25 l_status_type NUMBER := NULL;
26 BEGIN
27
28 SELECT status_type
29 INTO l_status_type
30 FROM eng_engineering_changes
31 WHERE change_notice = g_revised_item_rec.eco_name
32 AND organization_id = g_rev_item_unexp_rec.organization_id;
33
34 RETURN l_status_type;
35
36 EXCEPTION
37
38 WHEN OTHERS THEN
39
40 RETURN NULL;
41
42 END Get_Status_Type;
43
44 /*****************************************************************************
45 * Function : Get_Bill_Sequence
46 * Return : NUMBER
47 * Purpose : Function will query the bill record for the current revised
48 * item and return the bill_sequence_id.
49 ******************************************************************************/
50 FUNCTION Get_Bill_Sequence
51 RETURN NUMBER
52 IS
53 l_bill_sequence_id NUMBER := NULL;
54 BEGIN
55
56 SELECT bill_sequence_id
57 INTO l_bill_sequence_id
58 FROM bom_bill_of_materials
59 WHERE assembly_item_id = g_rev_item_unexp_rec.revised_item_id
60 AND NVL(alternate_bom_designator, 'NONE') =
61 NVL(g_revised_item_rec.alternate_bom_code, 'NONE')
62 AND organization_id = g_rev_item_unexp_rec.organization_id;
63
64 RETURN l_bill_sequence_id;
65
66 EXCEPTION
67
68 WHEN OTHERS THEN
69
70 RETURN NULL;
71
72 END Get_Bill_Sequence;
73
74 /*****************************************************************************
75 * Function : Get_Routing_Sequence
76 * Return : NUMBER
77 * Purpose : Function will query the rtg record for the current revised
78 * item and return the rtg_sequence_id.
79 ******************************************************************************/
80 FUNCTION Get_Routing_Sequence
81 RETURN NUMBER
82 IS
83 l_rtg_sequence_id NUMBER := NULL;
84 BEGIN
85
86 SELECT routing_sequence_id
87 INTO l_rtg_sequence_id
88 FROM bom_operational_routings
89 WHERE assembly_item_id = g_rev_item_unexp_rec.revised_item_id
90 AND NVL(alternate_routing_designator, 'NONE') =
91 NVL(g_revised_item_rec.alternate_bom_code, 'NONE')
92 AND organization_id = g_rev_item_unexp_rec.organization_id;
93
94 IF Bom_Globals.Get_Debug = 'Y' THEN
95 Error_Handler.Write_Debug('FUNCTION Get_Routing_Sequence, RTG Seq Id is: ' || to_char(l_rtg_sequence_id));
96 END IF;
97
98 RETURN l_rtg_sequence_id;
99
100
101 EXCEPTION
102
103 WHEN OTHERS THEN
104
105 RETURN NULL;
106
107 END Get_Routing_Sequence;
108
109
110 /*****************************************************************************
111 * Function : Get_Update_WIP
112 * Returns : Number
113 * Purpose : Function will look at the item attribute build_in_wip for the
114 * the current revised item and will return that as the default
115 * value for the column.
116 *****************************************************************************/
117 FUNCTION Get_Update_Wip
118 RETURN NUMBER
119 IS
120 l_build_in_wip VARCHAR2(1) := NULL;
121 l_update_wip NUMBER := NULL;
122 BEGIN
123
124 SELECT build_in_wip_flag
125 INTO l_build_in_wip
126 FROM mtl_system_items
127 WHERE inventory_item_id = g_rev_item_unexp_rec.revised_item_id
128 AND organization_id = g_rev_item_unexp_rec.organization_id;
129
130 IF (l_build_in_wip = 'Y')
131 THEN
132 l_update_wip := 1;
133 ELSIF (l_build_in_wip = 'N')
134 THEN
135 l_update_wip := 2;
136 END IF;
137
138 RETURN l_update_wip;
139
140 EXCEPTION
141
142 WHEN OTHERS THEN
143
144 RETURN NULL;
145
146 END Get_Update_Wip;
147
148 FUNCTION Get_Revised_Item_Sequence
149 RETURN NUMBER
150 IS
151 l_revised_item_seq_id NUMBER := NULL;
152 BEGIN
153
154 SELECT eng_revised_items_s.NEXTVAL
155 INTO l_revised_item_seq_id
156 FROM DUAL;
157
158 RETURN l_revised_item_seq_id;
159
160 EXCEPTION
161
162 WHEN OTHERS THEN
163 RETURN NULL;
164
165 END Get_Revised_Item_Sequence;
166
167 PROCEDURE Get_Flex_Revised_Item
168 IS
169 BEGIN
170
171 -- In the future call Flex APIs for defaults
172
173 IF g_revised_item_rec.attribute_category = FND_API.G_MISS_CHAR THEN
174 g_revised_item_rec.attribute_category := NULL;
175 END IF;
176
177 IF g_revised_item_rec.attribute2 = FND_API.G_MISS_CHAR THEN
178 g_revised_item_rec.attribute2 := NULL;
179 END IF;
180
181 IF g_revised_item_rec.attribute3 = FND_API.G_MISS_CHAR THEN
182 g_revised_item_rec.attribute3 := NULL;
183 END IF;
184
185 IF g_revised_item_rec.attribute4 = FND_API.G_MISS_CHAR THEN
186 g_revised_item_rec.attribute4 := NULL;
187 END IF;
188
189 IF g_revised_item_rec.attribute5 = FND_API.G_MISS_CHAR THEN
190 g_revised_item_rec.attribute5 := NULL;
191 END IF;
192
193 IF g_revised_item_rec.attribute7 = FND_API.G_MISS_CHAR THEN
194 g_revised_item_rec.attribute7 := NULL;
195 END IF;
196
197 IF g_revised_item_rec.attribute8 = FND_API.G_MISS_CHAR THEN
198 g_revised_item_rec.attribute8 := NULL;
199 END IF;
200
201 IF g_revised_item_rec.attribute9 = FND_API.G_MISS_CHAR THEN
202 g_revised_item_rec.attribute9 := NULL;
203 END IF;
204
205 IF g_revised_item_rec.attribute11 = FND_API.G_MISS_CHAR THEN
206 g_revised_item_rec.attribute11 := NULL;
207 END IF;
208
209 IF g_revised_item_rec.attribute12 = FND_API.G_MISS_CHAR THEN
210 g_revised_item_rec.attribute12 := NULL;
211 END IF;
212
213 IF g_revised_item_rec.attribute13 = FND_API.G_MISS_CHAR THEN
214 g_revised_item_rec.attribute13 := NULL;
215 END IF;
216
217 IF g_revised_item_rec.attribute14 = FND_API.G_MISS_CHAR THEN
218 g_revised_item_rec.attribute14 := NULL;
219 END IF;
220
221 IF g_revised_item_rec.attribute15 = FND_API.G_MISS_CHAR THEN
222 g_revised_item_rec.attribute15 := NULL;
223 END IF;
224
225 IF g_revised_item_rec.attribute1 = FND_API.G_MISS_CHAR THEN
226 g_revised_item_rec.attribute1 := NULL;
227 END IF;
228
229 IF g_revised_item_rec.attribute6 = FND_API.G_MISS_CHAR THEN
230 g_revised_item_rec.attribute6 := NULL;
231 END IF;
232
233 IF g_revised_item_rec.attribute10 = FND_API.G_MISS_CHAR THEN
234 g_revised_item_rec.attribute10 := NULL;
235 END IF;
236
237 END Get_Flex_Revised_Item;
238
239
240 -- Eco For Production added by MK on 10/06/2000
241 FUNCTION Get_Eco_For_Production
242 RETURN NUMBER
243 IS
244 BEGIN
245 IF ( g_revised_item_rec.lot_number IS NOT NULL
246 AND g_revised_item_rec.lot_number <> FND_API.G_MISS_CHAR ) OR
247 ( g_rev_item_unexp_rec.from_wip_entity_id IS NOT NULL
248 AND g_rev_item_unexp_rec.from_wip_entity_id <> FND_API.G_MISS_NUM)
249 THEN
250 RETURN 1 ; -- Return 1 : Yes
251 ELSE
252 RETURN 2 ; -- Return 2 : No
253 END IF ;
254
255 END Get_Eco_For_Production ;
256
257 --11.5.10 to get current life_cycle_id
258 FUNCTION Get_Current_LifeCycle_Id
259 ( p_rev_item_id IN NUMBER
260 , p_org_id IN NUMBER
261 , p_current_revision IN VARCHAR2
262 )
263 RETURN NUMBER
264 IS
265 l_id NUMBER;
266 BEGIN
267
268 /*SELECT LP.PROJ_ELEMENT_ID
269 into l_id
270 FROM PA_EGO_LIFECYCLES_PHASES_V LP, MTL_ITEM_REVISIONS MIR
271 WHERE
272 LP.PROJ_ELEMENT_ID =MIR.CURRENT_PHASE_ID
273 AND MIR.INVENTORY_ITEM_ID = p_rev_item_id
274 AND MIR.ORGANIZATION_ID = p_org_id
275 AND MIR.REVISION = p_current_revision; */ -- Commented By LKASTURI
276
277 -- Bug 3311072: Change the query to select item phase
278 -- Added By LKASTURI
279 SELECT CURRENT_PHASE_ID
280 INTO l_id
281 FROM MTL_System_items_vl
282 WHERE INVENTORY_ITEM_ID = p_rev_item_id
283 AND ORGANIZATION_ID = p_org_id;
284 -- End Changes 3311072
285
286 RETURN l_id;
287
288 EXCEPTION
289
290 WHEN NO_DATA_FOUND THEN
291 RETURN NULL;
292
293 WHEN OTHERS THEN
294 RETURN FND_API.G_MISS_NUM;
295
296
297 END Get_Current_LifeCycle_Id;
298
299 --11.5.10 to get current life_cycle_id
300 FUNCTION Get_Current_Structure_Rev_Id
301 ( p_bill_seq_id IN NUMBER
302 , p_item_rev_id IN NUMBER
303 , p_current_revision IN VARCHAR2
304 )
305 RETURN NUMBER
306 IS
307 l_id NUMBER;
308 BEGIN
309
310 /* not supported for 11.5.10
311 select STRUCTURE_REVISION_ID into l_id
312 from should use minor revision
313 where
314 BILL_SEQUENCE_ID = p_bill_seq_id
315 and REVISION = p_current_revision
316 and OBJECT_REVISION_ID = p_item_rev_id;
317 */
318
319 RETURN null;
320
321 EXCEPTION
322
323 WHEN NO_DATA_FOUND THEN
324 RETURN NULL;
325
326 WHEN OTHERS THEN
327 RETURN FND_API.G_MISS_NUM;
328
329
330 END Get_Current_Structure_Rev_Id;
331
332
333
334 /****************************************************************************
335 * Function : Get_Current_Item_Revision
336 * Paramters IN : Revised itemid
337 * Organization ID
338 * Revision Date
339 * Purpose : Function will return the current item revision bu looking
340 * at the mtl_item_revisions table and return the revision that
341 * has implementation date NOT NULL and is currently effective.
342 ******************************************************************************/
343 FUNCTION Get_Current_Item_Revision
344 ( p_revised_item_id IN NUMBER
345 , p_organization_id IN NUMBER
346 , p_revision_date IN DATE
347 ) RETURN VARCHAR2
348 IS
349 l_current_revision VARCHAR2(3) := NULL;
350
351 CURSOR NO_ECO_ITEM_REV IS
352 SELECT REVISION
353 FROM MTL_ITEM_REVISIONS
354 WHERE INVENTORY_ITEM_ID = p_revised_item_id
355 AND ORGANIZATION_ID = p_organization_id
356 AND EFFECTIVITY_DATE <= p_revision_date
357 AND IMPLEMENTATION_DATE IS NOT NULL
358 ORDER BY EFFECTIVITY_DATE DESC, REVISION DESC;
359 BEGIN
360 OPEN NO_ECO_ITEM_REV;
361 FETCH NO_ECO_ITEM_REV INTO l_current_revision;
362 CLOSE NO_ECO_ITEM_REV;
363
364 RETURN l_current_revision;
365
366 END Get_Current_Item_Revision;
367
368
369
370
371 /*******************************************************************************
372 * Procedure : Attribute_Defaulting
373 * Parameters IN : Revised item exposed record
374 * Revised item unexposed record
375 * Parameters OUT: Revised item exposed record after defaulting
376 * Revised item unexposed record after defaulting
377 * Mesg_Token_Table
378 * Return_Status
379 * Purpose : Attribute Defaulting will default the necessary null attribute
380 * with appropriate values.
381 *******************************************************************************/
382 PROCEDURE Attribute_Defaulting
383 ( p_revised_item_rec IN ENG_Eco_PUB.Revised_Item_Rec_Type
384 , p_rev_item_unexp_rec IN Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type
385 , x_revised_item_rec IN OUT NOCOPY ENG_Eco_PUB.Revised_Item_Rec_Type
386 , x_rev_item_unexp_rec IN OUT NOCOPY Eng_Eco_Pub.Rev_Item_Unexposed_rec_type
387 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
388 , x_return_status OUT NOCOPY VARCHAR2
389 )
390 IS
391 l_revision VARCHAR2(3);
392 l_default_lifecycle NUMBER := 0;
393
394 CURSOR c_status (cp_change_id IN NUMBER)
395 IS
396 SELECT ecsv.status_code , ecsv.status_type
397 FROM eng_change_statuses_vl ecsv
398 WHERE ecsv.status_code IN (SELECT els1.status_code
399 FROM eng_lifecycle_statuses els1
400 WHERE els1.entity_name='ENG_CHANGE'
401 AND els1.entity_id1 = cp_change_id
402 AND els1.active_flag = 'Y'
403 AND els1.sequence_number = (SELECT min(els2.sequence_number)
404 FROM eng_lifecycle_statuses els2
405 WHERE els2.entity_name='ENG_CHANGE'
406 AND els2.entity_id1 = cp_change_id
407 AND els2.active_flag = 'Y'));
408
409 CURSOR c_rev_item_status (cp_rev_item_seq_id IN NUMBER)
410 IS
411 SELECT status_code ,status_type
412 FROM eng_revised_items
413 WHERE revised_item_sequence_id = cp_rev_item_seq_id;
414
415 BEGIN
416
417 -- Initialize g_revised_item_rec
418
419 g_revised_item_rec := p_revised_item_rec;
420 g_rev_item_unexp_rec := p_rev_item_unexp_rec;
421
422 -- Default NULL attributes.
423
424 IF g_revised_item_rec.disposition_type IS NULL THEN
425
426 g_revised_item_rec.disposition_type := 1; -- 'No Change Required'
427
428 END IF;
429
430 IF g_revised_item_rec.earliest_effective_date IS NULL
431 OR g_revised_item_rec.earliest_effective_date = FND_API.G_MISS_DATE
432 THEN
433 -- commenting the defaulting of earliest_effective_date to sysdate for bug 3575375
434 g_revised_item_rec.earliest_effective_date := NULL;--SYSDATE;
435
436 END IF;
437
438 /***********************************************************************************
439 --
440 -- Defaulting Logic for status_code and status_type.
441 -- If Change Order is a PLM change, then the revised item.
442 -- status_type and status_code are defaulted to the First lifecycle phase of the change.
443 -- This is the behaviour when the revised item is added from the SSWA UI to the change order.
444 -- Default to the current_status_type and status_code if transaction type is not create.
445 -- Added For bug 3618676
446 --
447 ***********************************************************************************/
448
449 IF (Eng_Globals.Get_PLM_Or_ERP_Change(g_revised_item_rec.eco_name, g_rev_item_unexp_rec.organization_id) = 'PLM'
450 AND g_rev_item_unexp_rec.status_code IS NULL)
451 THEN
452 IF g_revised_item_rec.transaction_type = 'CREATE'
453 THEN
454 OPEN c_status(g_rev_item_unexp_rec.change_id);
455 FETCH c_status INTO g_rev_item_unexp_rec.status_code, g_revised_item_rec.status_type;
456 CLOSE c_status;
457 ELSE
458 -- The following has to be commented out once Status code promotion logic is added to the BO for PLM ECOs
459 -- and the status code being populated in Populate_Null_Columns for PLM
460 -- As no exposed field is available for status code. This is being done here.
461 OPEN c_rev_item_status(g_rev_item_unexp_rec.revised_item_sequence_id);
462 FETCH c_rev_item_status INTO g_rev_item_unexp_rec.status_code, g_revised_item_rec.status_type;
463 CLOSE c_rev_item_status;
464
465 END IF;
466 END IF;
467
468 /***********************************************************************************
469 --
470 -- For Other case, i.e ERP change set status type as the header status_type
471 -- And status_code as the status_type .
472 --
473 ***********************************************************************************/
474
475 IF g_revised_item_rec.status_type IS NULL THEN
476
477 g_revised_item_rec.status_type := Get_Status_Type;
478
479 END IF;
480
481 -- Added for bug 3618676
482 IF g_rev_item_unexp_rec.status_code IS NULL THEN
483
484 g_rev_item_unexp_rec.status_code := g_revised_item_rec.status_type;
485
486 END IF;
487
488
489 IF g_revised_item_rec.start_effective_date IS NULL AND
490 g_revised_item_rec.use_up_plan_name IS NULL THEN
491
492 g_revised_item_rec.start_effective_date := SYSDATE;
493
494 END IF;
495
496 /***********************************************************************
497 -- Copied this defaulting logic to ENG_Val_To_Id.Revised_Item_VID
498 -- by MK on 02/15/2001. BO doest not need this logic
499 -- but ECO Form is still using it. Hence not comment out.
500 ***********************************************************************/
501 IF g_rev_item_unexp_rec.bill_sequence_id IS NULL OR
502 g_rev_item_unexp_rec.bill_sequence_id = FND_API.G_MISS_NUM
503 THEN
504 g_rev_item_unexp_rec.bill_sequence_id := Get_Bill_Sequence;
505
506 IF Bom_Globals.Get_Debug = 'Y' THEN
507 Error_Handler.Write_Debug('Getting Bill Seq Id . . . : ' ||
508 to_char(g_rev_item_unexp_rec.bill_sequence_id));
509 END IF;
510
511 END IF;
512
513 IF g_revised_item_rec.mrp_active IS NULL THEN
514
515 g_revised_item_rec.mrp_active := 1;
516
517 END IF;
518
519 IF g_revised_item_rec.update_wip IS NULL THEN
520
521 -- Added IF condition because update_wip will be set to No
522 -- for Unit Controlled items.
523 -- Added by AS on 07/06/99
524
525 IF NOT BOM_Globals.Get_Unit_Controlled_Item
526 THEN
527 g_revised_item_rec.update_wip := Get_Update_Wip;
528 ELSE
529 g_revised_item_rec.update_wip := 2;
530 END IF;
531 END IF;
532
533 IF g_rev_item_unexp_rec.use_up IS NULL OR
534 g_rev_item_unexp_rec.use_up = FND_API.G_MISS_NUM
535 THEN
536 g_rev_item_unexp_rec.use_up := 2;
537 END IF;
538
539 IF g_rev_item_unexp_rec.use_up_item_id IS NULL OR
540 g_revised_item_rec.use_up_item_name = FND_API.G_MISS_CHAR
541 THEN
542 g_rev_item_unexp_rec.use_up := 2;
543 END IF;
544
545 IF g_rev_item_unexp_rec.requestor_id = FND_API.G_MISS_NUM
546 THEN
547 g_rev_item_unexp_rec.requestor_id := NULL;
548 END IF;
549
550 g_rev_item_unexp_rec.revised_item_sequence_id := Get_Revised_Item_Sequence;
551
552 IF g_revised_item_rec.attribute_category = FND_API.G_MISS_CHAR
553 OR g_revised_item_rec.attribute2 = FND_API.G_MISS_CHAR
554 OR g_revised_item_rec.attribute3 = FND_API.G_MISS_CHAR
555 OR g_revised_item_rec.attribute4 = FND_API.G_MISS_CHAR
556 OR g_revised_item_rec.attribute5 = FND_API.G_MISS_CHAR
557 OR g_revised_item_rec.attribute7 = FND_API.G_MISS_CHAR
558 OR g_revised_item_rec.attribute8 = FND_API.G_MISS_CHAR
559 OR g_revised_item_rec.attribute9 = FND_API.G_MISS_CHAR
560 OR g_revised_item_rec.attribute11 = FND_API.G_MISS_CHAR
561 OR g_revised_item_rec.attribute12 = FND_API.G_MISS_CHAR
562 OR g_revised_item_rec.attribute13 = FND_API.G_MISS_CHAR
563 OR g_revised_item_rec.attribute14 = FND_API.G_MISS_CHAR
564 OR g_revised_item_rec.attribute15 = FND_API.G_MISS_CHAR
565 OR g_revised_item_rec.attribute1 = FND_API.G_MISS_CHAR
566 OR g_revised_item_rec.attribute6 = FND_API.G_MISS_CHAR
567 OR g_revised_item_rec.attribute10 = FND_API.G_MISS_CHAR
568 THEN
569
570 Get_Flex_Revised_Item;
571
572 END IF;
573
574 /***********************************************************************
575 -- Added by MK on 09/01/2000
576 -- For New ECO Effectivities and ECO Routing
577 ***********************************************************************/
578
579 /***********************************************************************
580 -- Copied this defaulting logic to ENG_Val_To_Id.Revised_Item_VID
581 -- by MK on 02/15/2001. BO doest not need this logic
582 -- but ECO Form is still using it. Hence not comment out.
583 ***********************************************************************/
584 IF g_rev_item_unexp_rec.routing_sequence_id IS NULL OR
585 g_rev_item_unexp_rec.routing_sequence_id = FND_API.G_MISS_NUM
586 THEN
587 g_rev_item_unexp_rec.routing_sequence_id := Get_Routing_Sequence;
588
589 IF Bom_Globals.Get_Debug = 'Y' THEN
590 Error_Handler.Write_Debug('Getting Routing Seq Id . . . : ' ||
591 to_char(g_rev_item_unexp_rec.routing_sequence_id));
592 END IF;
593
594 END IF;
595
596 IF g_rev_item_unexp_rec.routing_sequence_id is NOT NULL THEN
597
598 Select CFM_ROUTING_FLAG,CTP_FLAG
599 into g_rev_item_unexp_rec.cfm_routing_flag,g_revised_item_rec.ctp_flag
600 from BOM_OPERATIONAL_ROUTINGS
601 where ROUTING_SEQUENCE_ID = g_rev_item_unexp_rec.routing_sequence_id;
602
603 END IF;
604
605 IF g_rev_item_unexp_rec.cfm_routing_flag IS NULL OR
606 g_rev_item_unexp_rec.cfm_routing_flag = FND_API.G_MISS_NUM
607 THEN
608 g_rev_item_unexp_rec.cfm_routing_flag := Bom_Default_Rtg_Header.Get_Cfm_Routing_Flag ;
609 END IF ; -- to Suppport Flow Routing, This should be exposed column.
610
611
612 IF g_revised_item_rec.ctp_flag IS NULL OR
613 g_revised_item_rec.ctp_flag = FND_API.G_MISS_NUM THEN
614 g_revised_item_rec.ctp_flag
615 := Bom_Default_Rtg_Header.Get_Ctp_Flag ;
616 END IF;
617
618 /* Comment out. For Future Release
619 IF g_revised_item_rec.mixed_model_map_flag IS NULL OR
620 g_revised_item_rec.mixed_model_map_flag = FND_API.G_MISS_NUM THEN
621 g_revised_item_rec.mixed_model_map_flag
622 := Bom_Default_Rtg_Header.Get_Get_Mixed_Model_Map_Flag ;
623 END IF;
624 */
625 -- Added by MK on 09/01/2000
626
627 -- Eco For Production Added by MK on 10/06/2000
628 IF g_revised_item_rec.eco_for_production IS NULL OR
629 g_revised_item_rec.eco_for_production = FND_API.G_MISS_NUM
630 THEN
631 g_revised_item_rec.eco_for_production := Get_Eco_For_Production ;
632 END IF;
633 --11.5.10 Defaulting current_item_revision_id and current_lifecycle_state_id
634
635 IF g_rev_item_unexp_rec.current_item_revision_id IS NULL OR
636 g_rev_item_unexp_rec.current_item_revision_id = FND_API.G_MISS_NUM
637 THEN
638 g_rev_item_unexp_rec.current_item_revision_id :=
639 BOM_REVISIONS.get_item_revision_id_fn(
640 'ALL',
641 'IMPL_ONLY',
642 g_rev_item_unexp_rec.organization_id,
643 g_rev_item_unexp_rec.revised_item_id, SYSDATE);
644 END IF;
645
646 IF g_rev_item_unexp_rec.current_lifecycle_state_id IS NULL OR
647 g_rev_item_unexp_rec.current_lifecycle_state_id = FND_API.G_MISS_NUM
648 THEN
649 /* l_revision :=
650 BOM_REVISIONS.GET_ITEM_REVISION_FN (
651 'ALL',
652 'ALL',
653 g_rev_item_unexp_rec.organization_id,
654 g_rev_item_unexp_rec.revised_item_id, SYSDATE); */ -- Commented By LKASTURI
655
656 g_rev_item_unexp_rec.current_lifecycle_state_id :=
657 Get_Current_LifeCycle_Id (
658 p_rev_item_id =>g_rev_item_unexp_rec.revised_item_id,
659 p_org_id =>g_rev_item_unexp_rec.organization_id,
660 p_current_revision =>l_revision);
661
662 END IF;
663
664
665 -- Done defaulting attributes
666 IF g_revised_item_rec.current_structure_rev_name is not NULL THEN
667
668 g_rev_item_unexp_rec.current_structure_rev_id:=
669 Get_Current_Structure_Rev_Id
670 ( p_bill_seq_id => g_rev_item_unexp_rec.bill_sequence_id
671 , p_item_rev_id => g_rev_item_unexp_rec.current_item_revision_id
672 , p_current_revision =>g_revised_item_rec.current_structure_rev_name);
673
674 END IF;
675
676 --end of 11.5.10 changes
677
678 -- 11.5.10E
679 -- Setting the values of new_rev_label, new_rev_description, new_rev_reason as null
680 -- for ERP case or when the new revision is not specified.
681 IF (Eng_Globals.Get_PLM_Or_ERP_Change(g_revised_item_rec.eco_name,
682 g_rev_item_unexp_rec.organization_id) <> 'PLM')
683 THEN
684 g_revised_item_rec.from_item_revision := FND_API.G_MISS_CHAR;
685
686 -- Commented for bug fix 4517503
687 -- g_rev_item_unexp_rec.from_item_revision_id := FND_API.G_MISS_NUM;
688 g_rev_item_unexp_rec.new_revision_reason_code := FND_API.G_MISS_CHAR;
689 ELSE
690 g_revised_item_rec.from_item_revision
691 := Get_Current_Item_Revision
692 ( p_revised_item_id => g_rev_item_unexp_rec.revised_item_id
693 , p_organization_id => g_rev_item_unexp_rec.organization_id
694 , p_revision_date => SYSDATE
695 );
696 END IF;
697 --
698 -- Added for bug fix 4517503
699 -- Setting the from item revision id to current item revision id
700 -- In ENGURITB.pls this value is being used to populate eng_revised_items.
701 -- current_item_revision_id in procedure insert_row.
702 -- Moved from ENGURITB.pls => Eng_revised_item_util.Insert_Row
703 IF g_rev_item_unexp_rec.from_item_revision_id IS NULL OR
704 g_rev_item_unexp_rec.from_item_revision_id = FND_API.G_MISS_NUM
705 THEN
706 g_rev_item_unexp_rec.from_item_revision_id := g_rev_item_unexp_rec.current_item_revision_id;
707 END IF;
708 -- End of bug fix 4517503
709
710 -- The new revision related fields are being nulled out if the new
711 -- revision is not provided.
712 IF (g_revised_item_rec.new_revised_item_revision IS NULL OR
713 g_revised_item_rec.new_revised_item_revision = FND_API.G_MISS_CHAR)
714 THEN
715 g_rev_item_unexp_rec.new_revision_reason_code := FND_API.G_MISS_CHAR;
716 END IF;
717
718 -- Validation for the scheduled_date
719
720 x_revised_item_rec := g_revised_item_rec;
721 x_rev_item_unexp_rec := g_rev_item_unexp_rec;
722 x_return_status := FND_API.G_RET_STS_SUCCESS;
723
724 END Attribute_Defaulting;
725
726 /******************************************************************************
727 * Procedure : Populate_Null_Columns
728 * Parameters IN : Revised component exposed column record
729 * Revised component unexposed column record
730 * Old Revised component record
731 * Old Revised component unexposed record
732 * Parameters OUT: Revised component exposed column record
733 * Revised component unexposed column record
734 * Purpose : The procedure will look at the columns that the user has not
735 * filled in for an update record and will copy values for those
736 * columns from the old record. If the columns that the user has
737 * given are having a missing or any other then those columns
738 * are not copied.
739 ********************************************************************************/
740 PROCEDURE Populate_Null_Columns
741 ( p_revised_item_rec IN ENG_Eco_PUB.Revised_item_Rec_Type
742 , p_old_revised_item_rec IN Eng_Eco_Pub.Revised_item_Rec_Type
743 , p_rev_item_unexp_Rec IN Eng_Eco_Pub.Rev_item_Unexposed_Rec_Type
744 , p_old_rev_item_unexp_Rec IN Eng_Eco_Pub.Rev_item_Unexposed_Rec_Type
745 , x_revised_item_Rec IN OUT NOCOPY Eng_Eco_Pub.Revised_Item_Rec_Type
746 , x_rev_item_unexp_Rec IN OUT NOCOPY Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type
747 )
748 IS
749 l_revised_item_rec ENG_Eco_PUB.Revised_Item_Rec_Type :=
750 p_revised_item_rec;
751 l_rev_item_unexp_rec Eng_Eco_Pub.Rev_item_Unexposed_Rec_Type :=
752 p_rev_item_unexp_rec;
753 BEGIN
754
755 IF l_revised_item_rec.disposition_type IS NULL THEN
756 l_revised_item_rec.disposition_type :=
757 p_old_revised_item_rec.disposition_type;
758 END IF;
759
760 IF l_revised_item_rec.earliest_Effective_date IS NULL THEN
761 l_revised_item_rec.earliest_effective_date :=
762 p_old_revised_item_rec.earliest_effective_date;
763 END IF;
764
765 IF l_revised_item_rec.attribute_category IS NULL THEN
766 l_revised_item_rec.attribute_category :=
767 p_old_revised_item_rec.attribute_category;
768 END IF;
769
770 IF l_revised_item_rec.attribute2 IS NULL THEN
771 l_revised_item_rec.attribute2 := p_old_revised_item_rec.attribute2;
772 END IF;
773
774 IF l_revised_item_rec.attribute3 IS NULL THEN
775 l_revised_item_rec.attribute3 := p_old_revised_item_rec.attribute3;
776 END IF;
777
778 IF l_revised_item_rec.attribute4 IS NULL THEN
779 l_revised_item_rec.attribute4 := p_old_revised_item_rec.attribute4;
780 END IF;
781
782 IF l_revised_item_rec.attribute5 IS NULL THEN
783 l_revised_item_rec.attribute5 := p_old_revised_item_rec.attribute5;
784 END IF;
785
786 IF l_revised_item_rec.attribute7 IS NULL THEN
787 l_revised_item_rec.attribute7 := p_old_revised_item_rec.attribute7;
788 END IF;
789
790 IF l_revised_item_rec.attribute8 IS NULL THEN
791 l_revised_item_rec.attribute8 := p_old_revised_item_rec.attribute8;
792 END IF;
793
794 IF l_revised_item_rec.attribute9 IS NULL THEN
795 l_revised_item_rec.attribute9 := p_old_revised_item_rec.attribute9;
796 END IF;
797
798 IF l_revised_item_rec.attribute11 IS NULL THEN
799 l_revised_item_rec.attribute11 := p_old_revised_item_rec.attribute11;
800 END IF;
801
802 IF l_revised_item_rec.attribute12 IS NULL THEN
803 l_revised_item_rec.attribute12 := p_old_revised_item_rec.attribute12;
804 END IF;
805
806 IF l_revised_item_rec.attribute13 IS NULL THEN
807 l_revised_item_rec.attribute13 := p_old_revised_item_rec.attribute13;
808 END IF;
809
810 IF l_revised_item_rec.attribute14 IS NULL THEN
811 l_revised_item_rec.attribute14 := p_old_revised_item_rec.attribute14;
812 END IF;
813
814 IF l_revised_item_rec.attribute15 IS NULL THEN
815 l_revised_item_rec.attribute15 := p_old_revised_item_rec.attribute15;
816 END IF;
817
818 IF l_revised_item_rec.status_type IS NULL THEN
819 l_revised_item_rec.status_type := p_old_revised_item_rec.status_type;
820 l_rev_item_unexp_rec.status_code := p_old_rev_item_unexp_rec.status_code; -- Added for bug 3618676
821 END IF;
822
823 IF l_revised_item_rec.start_effective_date IS NULL THEN
824 l_revised_item_rec.start_effective_date :=
825 p_old_revised_item_rec.start_effective_date;
826 END IF;
827
828 IF l_rev_item_unexp_rec.bill_sequence_id IS NULL THEN
829 l_rev_item_unexp_rec.bill_sequence_id :=
830 p_old_rev_item_unexp_rec.bill_sequence_id;
831 END IF;
832
833 IF l_revised_item_rec.mrp_active IS NULL THEN
834 l_revised_item_rec.mrp_active := p_old_revised_item_rec.mrp_active;
835 END IF;
836
837 IF l_revised_item_rec.update_wip IS NULL THEN
838 l_revised_item_rec.update_wip := p_old_revised_item_rec.update_wip;
839 END IF;
840
841
842
843 --
844 -- Simply copy the unexposed columns from the old record to the new record
845 -- so that no values are lost in the return process.
846 --
847 IF l_rev_item_unexp_rec.use_up_item_id IS NULL
848 THEN
849 l_rev_item_unexp_rec.use_up_item_id :=
850 p_old_rev_item_unexp_rec.use_up_item_id;
851 END IF;
852
853
854 l_rev_item_unexp_rec.revised_item_sequence_id :=
855 p_old_rev_item_unexp_rec.revised_item_sequence_id;
856
857 l_rev_item_unexp_rec.auto_implement_date :=
858 p_old_rev_item_unexp_rec.auto_implement_date;
859
860 l_rev_item_unexp_rec.cancellation_date :=
861 p_old_rev_item_unexp_rec.cancellation_date;
862
863 -- Added the 'null' condition by MK on 02/15/2001
864 IF l_rev_item_unexp_rec.bill_sequence_id IS NULL
865 THEN
866 l_rev_item_unexp_rec.bill_sequence_id :=
867 p_old_rev_item_unexp_rec.bill_sequence_id;
868 END IF ;
869
870 l_rev_item_unexp_rec.requestor_id :=
871 p_old_rev_item_unexp_rec.requestor_id;
872
873 l_rev_item_unexp_rec.use_up := p_old_rev_item_unexp_rec.use_up;
874
875 IF l_revised_item_rec.use_up_plan_name IS NULL THEN
876 l_revised_item_rec.use_up_plan_name :=
877 p_old_revised_item_rec.use_up_plan_name;
878 END IF;
879
880 IF l_revised_item_rec.attribute1 IS NULL THEN
881 l_revised_item_rec.attribute1 := p_old_revised_item_rec.attribute1;
882 END IF;
883
884 IF l_revised_item_rec.attribute6 IS NULL THEN
885 l_revised_item_rec.attribute6 := p_old_revised_item_rec.attribute6;
886 END IF;
887
888 IF l_revised_item_rec.attribute10 IS NULL THEN
889 l_revised_item_rec.attribute10 := p_old_revised_item_rec.attribute10;
890 END IF;
891
892 -- Added by MK on 10/24/2000
893 IF l_revised_item_rec.updated_revised_item_revision IS NULL THEN
894 l_revised_item_rec.updated_revised_item_revision := p_old_revised_item_rec.new_revised_item_revision ;
895 END IF;
896
897 /*********************************************************************
898 -- Added by MK on 09/01/2000
899 -- Enhancement for New ECO Effectivities and ECO Routing
900 --
901 *********************************************************************/
902
903 IF l_revised_item_rec.from_cumulative_quantity IS NULL THEN
904 l_revised_item_rec.from_cumulative_quantity := p_old_revised_item_rec.from_cumulative_quantity;
905 END IF;
906
907 IF l_revised_item_rec.lot_number IS NULL THEN
908 l_revised_item_rec.lot_number := p_old_revised_item_rec.lot_number ;
909 END IF;
910
911 IF l_revised_item_rec.completion_subinventory IS NULL THEN
912 l_revised_item_rec.completion_subinventory := p_old_revised_item_rec.completion_subinventory;
913 END IF;
914
915 IF l_revised_item_rec.priority IS NULL THEN
916 l_revised_item_rec.priority := p_old_revised_item_rec.priority;
917 END IF;
918
919 IF l_revised_item_rec.ctp_flag IS NULL THEN
920 l_revised_item_rec.ctp_flag := p_old_revised_item_rec.ctp_flag ;
921 END IF;
922
923 -- Added by MK on 10/24/2000
924 IF l_revised_item_rec.updated_routing_revision IS NULL THEN
925 l_revised_item_rec.updated_routing_revision := p_old_revised_item_rec.new_routing_revision ;
926 END IF;
927
928 IF l_revised_item_rec.routing_comment IS NULL THEN
929 l_revised_item_rec.routing_comment := p_old_revised_item_rec.routing_comment ;
930 END IF;
931
932 -- Added by MK on 10/06/2000
933 IF l_revised_item_rec.eco_for_production IS NULL THEN
934 l_revised_item_rec.eco_for_production :=
935 p_old_revised_item_rec.eco_for_production ;
936 END IF;
937
938 --
939 -- Simply copy the unexposed columns from the old record to the new record
940 -- so that no values are lost in the return process.
941 --
942 IF l_rev_item_unexp_rec.from_wip_entity_id IS NULL
943 THEN
944 l_rev_item_unexp_rec.from_wip_entity_id :=
945 p_old_rev_item_unexp_rec.from_wip_entity_id ;
946 END IF;
947
948 IF l_rev_item_unexp_rec.to_wip_entity_id IS NULL
949 THEN
950 l_rev_item_unexp_rec.to_wip_entity_id :=
951 p_old_rev_item_unexp_rec.to_wip_entity_id ;
952 END IF;
953
954 IF l_rev_item_unexp_rec.completion_locator_id IS NULL
955 THEN
956 l_rev_item_unexp_rec.completion_locator_id :=
957 p_old_rev_item_unexp_rec.completion_locator_id ;
958 END IF;
959
960 -- Added the 'null' condition by MK on 02/15/2001
961 IF l_rev_item_unexp_rec.routing_sequence_id IS NULL
962 THEN
963 l_rev_item_unexp_rec.routing_sequence_id :=
964 p_old_rev_item_unexp_rec.routing_sequence_id;
965 END IF ;
966
967 l_rev_item_unexp_rec.cfm_routing_flag :=
968 p_old_rev_item_unexp_rec.cfm_routing_flag ;
969
970 -- Added by MK on 09/01/2000
971
972
973 x_revised_item_rec := l_revised_item_rec;
974 x_rev_item_unexp_rec := l_rev_item_unexp_rec;
975
976 END Populate_Null_Columns;
977
978
979 /*****************************************************************************
980 * Function : Check_Alternate_Already_Exists
981 * Parameters IN : Revised item
982 * Alternate_Bom_Designator
983 * Organization Id
984 * Returns : True if the Alternate bill exists otherwise False.
985 * Purpose : Function will check if the a bill for given revised item with
986 * the given alternate designator exists. If it does then the
987 * function returns TRUE otherwise FALSE.
988 *******************************************************************************/
989 FUNCTION Check_Alternate_Already_Exists
990 ( p_revised_item_id IN NUMBER
991 , p_alternate_bom_designator IN VARCHAR2
992 , p_organization_id IN NUMBER
993 ) RETURN BOOLEAN
994 IS
995 cursor c_CheckAlternate IS
996 SELECT 1
997 FROM BOM_BILL_OF_MATERIALS
998 WHERE assembly_item_id = p_revised_item_id
999 AND organization_id = p_organization_id
1000 AND ((alternate_bom_designator IS NULL
1001 AND p_alternate_bom_designator IS NULL)
1002 OR alternate_bom_designator = p_alternate_bom_designator);
1003 BEGIN
1004
1005 FOR l_Count IN c_CheckAlternate LOOP
1006 RETURN TRUE;
1007
1008 END LOOP;
1009
1010 RETURN FALSE;
1011
1012 END Check_Alternate_Already_Exists;
1013
1014 /*****************************************************************************
1015 * Function : Compatible_Primary_Bill_Exists
1016 * Parameters IN : Revised item Id
1017 * Change Notice
1018 * Organization Id
1019 * Returns : True if the process succeed, False otherwise
1020 * Purpose : Function will check if the primary bill exists with a
1021 * compatible type.
1022 ******************************************************************************/
1023 FUNCTION Compatible_Primary_Bill_Exists
1024 ( p_revised_item_id IN NUMBER
1025 , p_change_notice IN VARCHAR2
1026 , p_organization_id IN NUMBER
1027 ) RETURN BOOLEAN
1028 IS
1029 l_assembly_type NUMBER := 0;
1030
1031 cursor c_CheckBillType IS
1032 SELECT 1
1033 FROM BOM_BILL_OF_MATERIALS
1034 WHERE assembly_item_id = p_revised_item_id
1035 AND organization_id = p_organization_id
1036 AND alternate_bom_designator is null
1037 AND ((assembly_type = 1 and l_assembly_type = 1)
1038 or l_assembly_type = 2);
1039 BEGIN
1040
1041 l_assembly_type := ENG_Globals.Get_ECO_Assembly_Type
1042 ( p_change_notice => p_change_notice
1043 , p_organization_id => p_organization_id
1044 );
1045
1046 FOR l_Count IN c_CheckBillType LOOP
1047
1048 RETURN TRUE;
1049 END LOOP;
1050
1051 RETURN FALSE;
1052
1053 END Compatible_Primary_Bill_Exists;
1054
1055
1056
1057 /*****************************************************************************
1058 * Added by MK on 09/01/2000 for New ECO Effectivity and ECO Routing
1059 *
1060 * Function : Check_Alt_Rtg_Already_Exists
1061 * Parameters IN : Revised item
1062 * Alternate_Bom_Designator
1063 * Organization Id
1064 * Returns : True if the Alternate bill exists otherwise False.
1065 * Purpose : Function will check if the a bill for given revised item with
1066 * the given alternate designator exists. If it does then the
1067 * function returns TRUE otherwise FALSE.
1068 *******************************************************************************/
1069 FUNCTION Check_Alt_Rtg_Already_Exists
1070 ( p_revised_item_id IN NUMBER
1071 , p_alternate_bom_designator IN VARCHAR2
1072 , p_organization_id IN NUMBER
1073 ) RETURN BOOLEAN
1074 IS
1075 cursor c_CheckAlternate IS
1076 SELECT 1
1077 FROM BOM_OPERATIONAL_ROUTINGS
1078 WHERE assembly_item_id = p_revised_item_id
1079 AND organization_id = p_organization_id
1080 AND alternate_routing_designator = p_alternate_bom_designator;
1081 BEGIN
1082
1083 FOR l_Count IN c_CheckAlternate LOOP
1084 RETURN TRUE;
1085 END LOOP;
1086
1087 RETURN FALSE;
1088
1089 END Check_Alt_Rtg_Already_Exists;
1090
1091 /*****************************************************************************
1092 * Function : Compatible_Primary_Rtg_Exists
1093 * Parameters IN : Revised item Id
1094 * Change Notice
1095 * Organization Id
1096 * Returns : True if the process succeed, False otherwise
1097 * Purpose : Function will check if the primary routing exists with a
1098 * compatible type.
1099 ******************************************************************************/
1100 FUNCTION Compatible_Primary_Rtg_Exists
1101 ( p_revised_item_id IN NUMBER
1102 , p_change_notice IN VARCHAR2
1103 , p_organization_id IN NUMBER
1104 ) RETURN BOOLEAN
1105 IS
1106 l_routing_type NUMBER := 0;
1107
1108 cursor c_CheckRtgType IS
1109 SELECT 1
1110 FROM BOM_OPERATIONAL_ROUTINGS
1111 WHERE assembly_item_id = p_revised_item_id
1112 AND organization_id = p_organization_id
1113 AND alternate_routing_designator is null
1114 AND ((routing_type = 1 and l_routing_type = 1)
1115 or l_routing_type = 2);
1116 BEGIN
1117
1118 l_routing_type := ENG_Globals.Get_ECO_Assembly_Type
1119 ( p_change_notice => p_change_notice
1120 , p_organization_id => p_organization_id
1121 );
1122
1123 FOR l_Count IN c_CheckRtgType LOOP
1124 RETURN TRUE;
1125 END LOOP;
1126
1127 RETURN FALSE;
1128
1129 END Compatible_Primary_Rtg_Exists;
1130 -- Added by MK on 09/01/2000
1131
1132 /****************************************************************************
1133 * Function : Initialize Bill Sequence Id
1134 * Returns : Number
1135 * Purpose : Will generate a new bill sequence id and return
1136 *
1137 *****************************************************************************/
1138 FUNCTION Initialize_Bill_Sequence_Id
1139 RETURN NUMBER
1140 IS
1141 l_bill_sequence_id NUMBER;
1142 cursor bill_seq_id is select bom_inventory_components_s.nextval from sys.dual;
1143 BEGIN
1144 open bill_seq_id;
1145 fetch bill_seq_id into l_bill_sequence_id;
1146 close bill_seq_id;
1147
1148 RETURN l_bill_sequence_id;
1149 END Initialize_Bill_Sequence_Id;
1150
1151
1152 /****************************************************************************
1153 * Added by MK on 09/01/2000 for New ECO Effectivity and ECO Routing
1154 *
1155 * Function : Get_Current_Rtg_Revision
1156 * Paramters IN : Revised itemid
1157 * Organization ID
1158 * Revision Date
1159 * Purpose : Function will return the current item revision by looking
1160 * at the mtl_rtg_item_revisions table and return the revision that
1161 * has implementation date NOT NULL and is currently effective.
1162 ******************************************************************************/
1163 FUNCTION Get_Current_Rtg_Revision
1164 ( p_revised_item_id IN NUMBER
1165 , p_organization_id IN NUMBER
1166 , p_revision_date IN DATE
1167 ) RETURN VARCHAR2
1168 IS
1169 l_current_revision VARCHAR2(3) := NULL;
1170
1171 CURSOR NO_ECO_ROUTING_REV IS
1172 SELECT process_revision
1173 FROM MTL_RTG_ITEM_REVISIONS
1174 WHERE INVENTORY_ITEM_ID = p_revised_item_id
1175 AND ORGANIZATION_ID = p_organization_id
1176 AND EFFECTIVITY_DATE <= p_revision_date
1177 AND IMPLEMENTATION_DATE IS NOT NULL
1178 ORDER BY EFFECTIVITY_DATE DESC, PROCESS_REVISION DESC;
1179 BEGIN
1180 OPEN NO_ECO_ROUTING_REV;
1181 FETCH NO_ECO_ROUTING_REV INTO l_current_revision;
1182
1183 -- Added by MK on 11/27/00
1184 IF NO_ECO_ROUTING_REV%NOTFOUND THEN
1185 SELECT mp.starting_revision
1186 INTO l_current_revision
1187 FROM MTL_PARAMETERS mp
1188 WHERE mp.organization_id = p_organization_id
1189 AND NOT EXISTS( SELECT NULL
1190 FROM MTL_RTG_ITEM_REVISIONS
1191 WHERE implementation_date IS NOT NULL
1192 AND organization_id = p_organization_id
1193 AND inventory_item_id = p_revised_item_id
1194 ) ;
1195 END IF ;
1196
1197
1198
1199 CLOSE NO_ECO_ROUTING_REV;
1200
1201 RETURN l_current_revision;
1202
1203 END Get_Current_Rtg_Revision;
1204 -- Added by MK on 09/02/2000
1205
1206
1207 /******************************************************************************
1208 * Function : Get_Schedule_Date
1209 * Parameters IN : Use_UP_Item_Id
1210 * Plan Name
1211 * Organization Id
1212 * Revised Item ID
1213 * Bill Sequence ID
1214 * Returns : Date
1215 * Purpose : If the user has update the use up plan or the use up item
1216 * then the schedule date also needs to be changed. This schedule
1217 * is has to be such that it is not greater than any of the
1218 * components on the ECO and should be within the range of the
1219 * given mrp plan.
1220 ******************************************************************************/
1221 FUNCTION Get_Scheduled_Date
1222 ( p_use_up_item_id IN NUMBER
1223 , p_use_up_plan_name IN VARCHAR2
1224 , p_revised_item_id IN NUMBER
1225 , p_organization_id IN NUMBER
1226 , p_bill_sequence_id IN NUMBER
1227 ) RETURN DATE
1228 IS
1229 l_scheduled_date DATE := NULL;
1230 l_sequence_id NUMBER := NULL;
1231 l_assembly_item_id NUMBER := NULL;
1232 l_inventory_use_up_date DATE := NULL;
1233 CURSOR SCHED_DATE IS
1234 SELECT si.inventory_use_up_date, bl.assembly_item_id
1235 FROM mrp_system_items si,
1236 bom_lists bl
1237 WHERE bl.sequence_id = l_sequence_id
1238 AND ( bl.assembly_item_id = p_revised_item_id
1239 OR bl.assembly_item_id IN
1240 ( SELECT component_item_id
1241 FROM bom_inventory_components
1242 WHERE bill_sequence_id = p_bill_sequence_id
1243 AND nvl(acd_type,1) <> 3
1244 AND effectivity_date <= si.inventory_use_up_date
1245 AND nvl(disable_date, si.inventory_use_up_date + 1) >
1246 si.inventory_use_up_date
1247 )
1248 )
1249 AND si.organization_id = p_organization_id
1250 AND si.compile_designator = p_use_up_plan_name
1251 AND si.inventory_item_id = bl.assembly_item_id
1252 AND si.inventory_use_up_date >= trunc(sysdate)
1253 AND (EXISTS ( SELECT 'valid'
1254 FROM mrp_plans pl2
1255 WHERE pl2.organization_id = p_organization_id
1256 AND pl2.explosion_completion_date <=
1257 pl2.data_completion_date
1258 AND pl2.data_completion_date <=
1259 pl2.plan_completion_date
1260 AND pl2.plan_type in (1,2)
1261 AND pl2.compile_designator = p_use_up_plan_name
1262 )
1263 OR
1264 EXISTS ( --added by arudresh, bug: 3725067
1265 SELECT plan_completion_date
1266 FROM mrp_plan_organizations_v
1267 WHERE compile_designator = p_use_up_plan_name
1268 AND planned_organization = p_organization_id
1269 )
1270 );
1271 BEGIN
1272 l_sequence_id := ENG_REVISED_ITEMS_PKG.Get_BOM_Lists_Seq_Id;
1273
1274 ENG_REVISED_ITEMS_PKG.Insert_BOM_Lists
1275 ( X_Revised_Item_Id => p_revised_item_id
1276 , X_Sequence_Id => l_sequence_id
1277 , X_Bill_Sequence_Id => p_bill_sequence_id
1278 );
1279
1280 FOR l_sched_date IN SCHED_DATE LOOP
1281 IF l_sched_date.assembly_item_id = p_use_up_item_id
1282 THEN
1283 l_scheduled_date := l_sched_date.inventory_use_up_date;
1284 END IF;
1285 END LOOP;
1286
1287 ENG_REVISED_ITEMS_PKG.Delete_BOM_Lists (X_Sequence_Id => l_sequence_id);
1288
1289 RETURN l_scheduled_date;
1290
1291 END Get_Scheduled_Date;
1292
1293 /***************************************************************************
1294 * Function : Get_Requestor
1295 * Pramaeters IN : Change notice
1296 * Organization Id
1297 * Returns : Requestor Id
1298 * Purpose : The function will query the requestor from the change notice
1299 * and return it as the requestor for cancellation, if the user
1300 * has not given a requestor name.
1301 ******************************************************************************/
1302 FUNCTION Get_Requestor ( p_Change_Notice IN VARCHAR2
1303 , p_organization_id IN NUMBER
1304 )
1305 RETURN NUMBER
1306 IS
1307 l_requestor NUMBER;
1308 BEGIN
1309 SELECT requestor_id
1310 INTO l_requestor
1311 FROM eng_engineering_changes
1312 WHERE change_notice = p_change_notice
1313 AND organization_id = p_organization_id;
1314
1315 RETURN l_requestor;
1316
1317 EXCEPTION
1318 WHEN OTHERS THEN
1319 RETURN NULL;
1320
1321 END Get_Requestor;
1322
1323
1324 /*******************************************************************************
1325 * Procedure : Entity Defaulting
1326 * Parameters IN : Revised item exposed column record
1327 * Revised item unexposed column record
1328 * Parameters OUT: Revised item exposed column record
1329 * Revised item unexposed column record
1330 * Returns : None
1331 * Purpose : Entity defaulting will default any values remaining values that
1332 * need conditional defaulting i.e defaulting which is based on
1333 * one or more columns from the entity column values.
1334 ******************************************************************************/
1335 PROCEDURE Entity_Defaulting
1336 ( p_revised_item_rec IN ENG_Eco_PUB.Revised_Item_Rec_Type
1337 , p_rev_item_unexp_rec IN ENG_Eco_PUB.Rev_Item_Unexposed_Rec_Type
1338 , p_old_revised_item_rec IN Eng_Eco_Pub.Revised_Item_Rec_Type
1339 , p_old_rev_item_unexp_rec IN Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type
1340 , p_control_rec IN BOM_BO_Pub.Control_Rec_Type
1341 := BOM_BO_PUB.G_DEFAULT_CONTROL_REC
1342 , x_revised_item_rec IN OUT NOCOPY ENG_Eco_PUB.Revised_Item_Rec_Type
1343 , x_rev_item_unexp_rec IN OUT NOCOPY Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type
1344 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1345 , x_return_status OUT NOCOPY VARCHAR2
1346 )
1347 IS
1348 l_schedule_id NUMBER := NULL;
1349 l_revised_item_rec ENG_Eco_PUB.Revised_Item_Rec_Type :=
1350 p_revised_item_rec;
1351 l_rev_item_unexp_rec Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type :=
1352 p_rev_item_unexp_rec;
1353 l_processed BOOLEAN := FALSE;
1354 l_current_revision VARCHAR2(3) := 999;
1355 l_err_text VARCHAR2(2000);
1356
1357 l_ECO_approved NUMBER := 0;
1358
1359 CURSOR c_status (cp_change_id IN NUMBER)
1360 IS
1361 SELECT ecsv.status_code
1362 FROM eng_change_statuses_vl ecsv
1363 WHERE ecsv.status_code IN (SELECT els1.status_code
1364 FROM eng_lifecycle_statuses els1
1365 WHERE els1.entity_name='ENG_CHANGE'
1366 AND els1.entity_id1 = cp_change_id
1367 AND els1.active_flag = 'Y'
1368 AND els1.sequence_number = (SELECT min(els2.sequence_number)
1369 FROM eng_lifecycle_statuses els2
1370 WHERE els2.entity_name='ENG_CHANGE'
1371 AND els2.entity_id1 = cp_change_id
1372 AND els2.active_flag = 'Y'));
1373 CURSOR c_CheckApproval IS SELECT 1
1374 FROM ENG_ENGINEERING_CHANGES
1375 WHERE change_notice = p_revised_item_rec.eco_name
1376 AND organization_id =
1377 p_rev_item_unexp_rec.organization_id
1378 AND approval_status_type = 5;
1379
1380 l_rev_already_exists NUMBER := 0;
1381 CURSOR c_CheckRevision ( p_revision VARCHAR2) IS
1382 SELECT 1
1383 FROM MTL_ITEM_REVISIONS
1384 WHERE inventory_item_id = p_rev_item_unexp_rec.revised_item_id
1385 AND organization_id = p_rev_item_unexp_rec.organization_id
1386 AND revision = p_revision;
1387
1388 CURSOR c_Get_Bill_Seq
1389 IS
1390 SELECT bill_sequence_id
1391 FROM bom_bill_of_materials
1392 WHERE assembly_item_id = p_rev_item_unexp_rec.revised_item_id
1393 AND organization_id = p_rev_item_unexp_rec.organization_id
1394 AND NVL(alternate_bom_designator, 'none') =
1395 NVL(p_revised_item_rec.alternate_bom_code, 'none');
1396
1397
1398 /************************************************************************
1399 -- Followings are added for New ECO Effectivity and ECO Routing
1400 -- by MK 09/01/2000
1401 ************************************************************************/
1402 l_current_rtg_revision VARCHAR2(3) := 999;
1403
1404 CURSOR c_Rtg_CheckRevision ( p_revision VARCHAR2) IS
1405 SELECT 1
1406 FROM MTL_RTG_ITEM_REVISIONS
1407 WHERE inventory_item_id = p_rev_item_unexp_rec.revised_item_id
1408 AND organization_id = p_rev_item_unexp_rec.organization_id
1409 AND process_revision = p_revision;
1410
1411 l_rtg_rev_already_exists NUMBER := 0;
1412
1413 CURSOR c_Get_Routing_Seq
1414 IS
1415 SELECT routing_sequence_id
1416 FROM BOM_OPERATIONAL_ROUTINGS
1417 WHERE assembly_item_id = p_rev_item_unexp_rec.revised_item_id
1418 AND organization_id = p_rev_item_unexp_rec.organization_id
1419 AND NVL(alternate_routing_designator, 'none') =
1420 NVL(p_revised_item_rec.alternate_bom_code, 'none');
1421
1422 -- Added by MK on 09/01/2000
1423
1424
1425 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1426 l_Token_Tbl Error_Handler.Token_Tbl_Type;
1427 -- Added for bug 4210718
1428 l_revEffStrc_exists NUMBER;
1429 l_structure_type_id NUMBER;
1430 l_cp_not_allowed NUMBER;
1431
1432 -- R12: OPM Convergence Project
1433 CURSOR c_Get_Item_Details
1434 IS
1435 SELECT msi.bom_item_type , msi.tracking_quantity_ind
1436 FROM mtl_system_items msi
1437 WHERE msi.inventory_item_id = p_rev_item_unexp_rec.revised_item_id
1438 AND msi.organization_id = p_rev_item_unexp_rec.organization_id;
1439
1440 CURSOR c_Get_Org_Details
1441 IS
1442 SELECT process_enabled_flag
1443 FROM mtl_parameters
1444 WHERE organization_id = p_rev_item_unexp_rec.organization_id;
1445
1446 l_Item_Details_Rec c_Get_Item_Details%ROWTYPE;
1447 l_Org_Details_Rec c_Get_Org_Details%ROWTYPE;
1448 BEGIN
1449
1450 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Performing Entity Level Defaulting . . .'); END IF;
1451
1452 -- Initialize flags
1453
1454 G_SCHED_DATE_CHANGED := FALSE;
1455
1456 G_DEL_UPD_INS_ITEM_REV := 0;
1457
1458 G_CREATE_ALTERNATE := FALSE;
1459
1460 G_ECO_FOR_PROD_CHANGED := FALSE ; -- Added by MK on 10/24/2000
1461
1462 G_OLD_SCHED_DATE := NULL; -- Bug 6657209
1463
1464 l_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
1465 l_Token_Tbl(1).Token_Value := p_revised_item_rec.revised_item_name;
1466
1467 ENG_Globals.Check_Approved_For_Process
1468 ( p_change_notice => l_revised_item_rec.eco_name
1469 , p_organization_id => l_rev_item_unexp_rec.organization_id
1470 , x_processed => l_processed
1471 , x_err_text => l_err_text
1472 );
1473
1474 IF l_processed
1475 THEN
1476 IF p_old_revised_item_rec.status_type = 4
1477 THEN
1478 l_revised_item_rec.status_type := 1; -- Open
1479 END IF;
1480
1481 l_Token_Tbl(1).Token_Name := 'ECO_NAME';
1482 l_Token_Tbl(1).Token_Value := p_revised_item_rec.eco_name;
1483
1484 Error_Handler.Add_Error_Token
1485 ( p_Message_Name => 'ENG_APPROVE_WARNING'
1486 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1487 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1488 , p_Token_Tbl => l_Token_Tbl
1489 , p_message_type => 'W'
1490 );
1491
1492 l_Token_Tbl(1).Token_Name := 'REVISED_ITEM_NAME';
1493 l_Token_Tbl(1).Token_Value := p_revised_item_rec.revised_item_name;
1494
1495 END IF;
1496
1497 -- Syalaman - Added for bug 6371493.
1498 IF (Eng_Globals.Get_PLM_Or_ERP_Change(l_revised_item_rec.eco_name, l_rev_item_unexp_rec.organization_id) = 'PLM'
1499 AND l_rev_item_unexp_rec.status_code IS NULL)
1500 THEN
1501 IF l_revised_item_rec.transaction_type = 'CREATE'
1502 THEN
1503 OPEN c_status(l_rev_item_unexp_rec.change_id);
1504 FETCH c_status INTO l_rev_item_unexp_rec.status_code;
1505 CLOSE c_status;
1506 ELSE
1507 l_rev_item_unexp_rec.status_code := p_old_rev_item_unexp_rec.status_code;
1508 END IF;
1509 END IF;
1510
1511 -- Added by MK on 01/03/01
1512 -- If eco for production flag is Y ,
1513 -- Revisions should be null. Because this eco is only
1514 -- applied to work orders.
1515 --
1516 IF l_revised_item_rec.eco_for_production = 1 THEN
1517
1518 IF l_revised_item_rec.new_revised_item_revision IS NOT NULL OR
1519 l_revised_item_rec.new_revised_item_revision <> FND_API.G_MISS_CHAR OR
1520 l_revised_item_rec.updated_revised_item_revision IS NOT NULL OR
1521 l_revised_item_rec.updated_revised_item_revision <> FND_API.G_MISS_CHAR OR
1522 l_revised_item_rec.new_routing_revision IS NOT NULL OR
1523 l_revised_item_rec.new_routing_revision <> FND_API.G_MISS_CHAR OR
1524 l_revised_item_rec.updated_routing_revision IS NOT NULL OR
1525 l_revised_item_rec.updated_routing_revision <> FND_API.G_MISS_CHAR
1526 THEN
1527 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1528 THEN
1529 Error_Handler.Add_Error_Token
1530 ( p_Message_Name => 'ENG_RIT_SET_REV_NULL'
1531 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1532 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1533 , p_Token_Tbl => l_Token_Tbl
1534 , p_message_type => 'W');
1535 END IF;
1536 END IF ;
1537 l_revised_item_rec.new_revised_item_revision := NULL ;
1538 l_revised_item_rec.updated_revised_item_revision := NULL ;
1539 l_revised_item_rec.new_routing_revision := NULL ;
1540 l_revised_item_rec.updated_routing_revision := NULL ;
1541 END IF ;
1542
1543
1544 -- Modified by MK on 10/24/00
1545 IF (( (l_revised_item_rec.updated_revised_item_revision <>
1546 l_revised_item_rec.new_revised_item_revision )
1547 OR
1548 ( l_revised_item_rec.updated_revised_item_revision IS NULL AND
1549 l_revised_item_rec.new_revised_item_revision IS NOT NULL )
1550 OR
1551 ( l_revised_item_rec.updated_revised_item_revision IS NOT NULL AND
1552 l_revised_item_rec.new_revised_item_revision IS NULL )
1553 )
1554 AND
1555 l_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
1556 )
1557 OR
1558 l_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE
1559 THEN
1560
1561
1562 IF l_revised_item_rec.transaction_type =
1563 ENG_Globals.G_OPR_CREATE
1564 THEN
1565
1566 IF Bom_Globals.Get_Debug = 'Y' THEN
1567 Error_Handler.Write_Debug('In transaction type = Create, Checking for revised item revision: ' ||
1568 p_revised_item_rec.new_revised_item_revision);
1569 END IF;
1570 FOR x_count IN
1571 c_CheckRevision
1572 ( p_revision =>
1573 l_revised_item_rec.new_revised_item_revision
1574 )
1575 LOOP
1576
1577 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Current Revision Found . . .'); END IF;
1578
1579 l_rev_already_exists := 1;
1580 END LOOP;
1581
1582 l_current_revision :=
1583 Get_Current_Item_Revision
1584 ( p_revised_item_id =>
1585 l_rev_item_unexp_rec.revised_item_id
1586 , p_organization_id =>
1587 l_rev_item_unexp_rec.organization_id
1588 , p_revision_date => SYSDATE
1589 );
1590
1591 -- Can insert a revision into MTL_ITEM_REVISIONS only
1592 -- if it is not the same as the Current Revision
1593
1594 IF l_revised_item_rec.new_revised_item_revision <>
1595 l_current_revision AND
1596 l_rev_already_exists = 0
1597 THEN
1598 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Item Revision set for Insert . . .'); END IF;
1599
1600 G_DEL_UPD_INS_ITEM_REV := 3;
1601 ELSIF l_rev_already_exists = 1 THEN
1602
1603 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Item Revision set for Update . . .'); END IF;
1604
1605 G_DEL_UPD_INS_ITEM_REV := 2;
1606 END IF;
1607
1608 ELSIF l_revised_item_rec.transaction_type =
1609 ENG_Globals.G_OPR_UPDATE
1610 THEN
1611
1612 IF Bom_Globals.Get_Debug = 'Y' THEN
1613 Error_Handler.Write_Debug('In transaction type = Update , Checking for revised item revision: ' ||
1614 l_revised_item_rec.updated_revised_item_revision );
1615 END IF;
1616
1617 l_current_revision :=
1618 Get_Current_Item_Revision
1619 ( p_revised_item_id =>
1620 l_rev_item_unexp_rec.revised_item_id
1621 , p_organization_id =>
1622 l_rev_item_unexp_rec.organization_id
1623 , p_revision_date => SYSDATE
1624 );
1625
1626 FOR x_count IN
1627 c_CheckRevision
1628 ( p_revision =>
1629 l_revised_item_rec.updated_revised_item_revision
1630 )
1631 LOOP
1632 l_rev_already_exists := 1;
1633 END LOOP;
1634
1635 IF l_rev_already_exists = 0 AND
1636 l_revised_item_rec.updated_revised_item_revision <>
1637 FND_API.G_MISS_CHAR
1638 THEN
1639 --
1640 -- Insert new revision information into
1641 -- MTL_ITEM_REVISIONS
1642 --
1643 IF l_revised_item_rec.new_revised_item_revision IS NULL OR
1644 l_revised_item_rec.new_revised_item_revision
1645 = l_current_revision -- Added by MK on 02/13/2001
1646 THEN
1647 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Item Revision set for Insert . . .'); END IF;
1648
1649 G_DEL_UPD_INS_ITEM_REV := 3;
1650 ELSE
1651 --
1652 -- Modified by MK on 10/24/00
1653 -- Update new revision information into
1654 -- MTL_ITEM_REVISIONS
1655 --
1656 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Item Revision set for Update. . .'); END IF;
1657 G_DEL_UPD_INS_ITEM_REV := 2 ;
1658 END IF ;
1659
1660 ELSIF ( ( l_rev_already_exists = 0
1661 AND
1662 (
1663 l_revised_item_rec.updated_revised_item_revision =
1664 FND_API.G_MISS_CHAR OR
1665 l_revised_item_rec.updated_revised_item_revision
1666 IS NULL )
1667 )
1668 OR -- Added by MK on 02/13/2001 for Bug 1641488
1669 ( l_rev_already_exists = 1 AND
1670 l_revised_item_rec.updated_revised_item_revision
1671 = l_current_revision
1672 ))
1673 -- and l_revised_item_rec.transaction_type = ENG_Globals.G_OPR_DELETE
1674 -- Bug 3629755
1675 -- Commented as it is within the
1676 -- l_revised_item_rec.transaction_type = ENG_Globals.G_OPR_UPDATE Condition
1677 THEN
1678
1679 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Item Revision set for Delete . . .'); END IF;
1680
1681 G_DEL_UPD_INS_ITEM_REV := 1;
1682
1683 ELSIF l_rev_already_exists = 1 AND
1684 l_revised_item_rec.updated_revised_item_revision
1685 <> l_current_revision
1686 THEN
1687
1688 -- Update new item revision information in
1689 -- MTL_ITEM_REVISIONS
1690 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Item Revision set for Update. . .'); END IF;
1691 G_DEL_UPD_INS_ITEM_REV := 2;
1692
1693 END IF; /* If Update Ends */
1694
1695 END IF; /* If Create or Update Ends */
1696
1697 END IF; /* Main If ends */
1698 --
1699 -- Set the global flag g_create_alternate to TRUE if the user is
1700 -- trying to create an alternate
1701 --
1702 IF ((Eng_Globals.Get_PLM_Or_ERP_Change(l_revised_item_rec.eco_name, l_rev_item_unexp_rec.organization_id) = 'PLM' AND
1703 (l_revised_item_rec.alternate_bom_code IS NOT NULL OR
1704 l_rev_item_unexp_rec.structure_type_id IS NOT NULL)
1705 )
1706 OR
1707 (l_revised_item_rec.alternate_bom_code IS NOT NULL AND
1708 Compatible_Primary_Bill_Exists
1709 ( p_revised_item_id => l_rev_item_unexp_rec.revised_item_id
1710 , p_change_notice => l_revised_item_rec.eco_name
1711 , p_organization_id => l_rev_item_unexp_rec.organization_id
1712 ) AND
1713 l_revised_item_rec.alternate_bom_code <> fnd_api.G_MISS_CHAR)
1714 ) AND
1715 NOT Check_Alternate_Already_Exists
1716 ( p_revised_item_id => l_rev_item_unexp_rec.revised_item_id
1717 , p_alternate_bom_designator => l_revised_item_rec.alternate_bom_code
1718 , p_organization_id => l_rev_item_unexp_rec.organization_id
1719 )
1720 THEN
1721 -- Bug : 4210718
1722 -- Before creating the alternate check if there is change policy
1723 -- associated with it or a revision eff structure of the structure type
1724 -- has been created. Skip the alternate bom creation in these cases.
1725 l_cp_not_allowed := 2;
1726 l_structure_type_id := l_rev_item_unexp_rec.structure_type_id;
1727 Eng_Validate_Revised_Item.Check_Structure_Type_Policy
1728 ( p_inventory_item_id => l_rev_item_unexp_rec.revised_item_id
1729 , p_organization_id => l_rev_item_unexp_rec.organization_id
1730 , p_alternate_bom_code => l_revised_item_rec.alternate_bom_code
1731 , x_structure_type_id => l_structure_type_id
1732 , x_strc_cp_not_allowed => l_cp_not_allowed
1733 );
1734 IF Bom_Globals.Get_Debug = 'Y' THEN
1735 Error_Handler.Write_Debug('After Check if structure change policy is existing...'||to_char(l_cp_not_allowed)) ;
1736 Error_Handler.Write_Debug('structure change policy Structure Type Id...'||to_char(l_structure_type_id)) ;
1737 END IF;
1738 IF l_cp_not_allowed = 2
1739 THEN
1740 BEGIN
1741 l_revEffStrc_exists := 2;
1742 SELECT 1
1743 INTO l_revEffStrc_exists
1744 FROM bom_structures_b
1745 WHERE effectivity_control = 4
1746 AND assembly_item_id = l_rev_item_unexp_rec.revised_item_id
1747 AND organization_id = l_rev_item_unexp_rec.organization_id
1748 AND structure_type_id = l_structure_type_id
1749 AND ROWNUM = 1;
1750 EXCEPTION
1751 WHEN NO_DATA_FOUND THEN
1752 NULL;
1753 END;
1754
1755 OPEN c_Get_Item_Details;
1756 FETCH c_Get_Item_Details INTO l_Item_Details_Rec;
1757 CLOSE c_Get_Item_Details;
1758 OPEN c_Get_Org_Details;
1759 FETCH c_Get_Org_Details INTO l_Org_Details_Rec;
1760 CLOSE c_Get_Org_Details;
1761 IF Bom_Globals.Get_Debug = 'Y' THEN
1762 Error_Handler.Write_Debug('After Check if revision eff structure is existing...'||to_char(l_revEffStrc_exists)) ;
1763 END IF;
1764 -- Added check for OPM Convergence
1765 -- Bills shouldnt get created when the organization is a process enabled org
1766 -- and the assembly item is model or optional or it is dual UOM contralled
1767 -- then cannot create the bill
1768 IF (l_revEffStrc_exists = 2
1769 AND (l_Org_Details_Rec.process_enabled_flag = 'N'
1770 OR (l_Org_Details_Rec.process_enabled_flag = 'Y' AND l_Item_Details_Rec.bom_item_type NOT IN (1,2)))
1771 AND l_Item_Details_Rec.tracking_quantity_ind = 'P')
1772 THEN
1773 l_rev_item_unexp_rec.bill_sequence_id := Initialize_Bill_Sequence_Id;
1774 IF Bom_Globals.Get_Debug = 'Y' THEN
1775 Error_Handler.Write_Debug('Bill seq id is generated for an alternate BOM . . . : ' ||
1776 to_char(l_rev_item_unexp_rec.bill_sequence_id ));
1777 END IF;
1778 IF Bom_Globals.Get_Debug = 'Y' THEN
1779 Error_Handler.Write_Debug('Setting creat alternate bill flag to True. . . ' ) ;
1780 END IF;
1781 G_CREATE_ALTERNATE := TRUE;
1782 END IF;
1783 END IF;
1784 -- End changes for Bug : 4210718
1785 END IF;
1786
1787 /***********************************************************************
1788 -- Comment out by MK on 02/15/2001
1789 -- Because this code is duplicate the logic in Attribute Defaulting and
1790 -- ENG_Val_To_Id.Revised_Item_VID
1791 IF (l_rev_item_unexp_rec.bill_sequence_id IS NULL OR
1792 l_rev_item_unexp_rec.bill_sequence_id = FND_API.G_MISS_NUM)
1793 AND l_revised_item_rec.alternate_bom_code IS NULL -- Added by MK on 10/31/00
1794 THEN
1795 --
1796 -- If the user is simply trying to add a revised item that
1797 -- already exists on another ECO and has a bill for it
1798 -- then get the bill sequence and default it.
1799 --
1800 FOR bill_seq IN c_Get_Bill_Seq LOOP
1801 l_rev_item_unexp_rec.bill_sequence_id :=
1802 bill_seq.bill_sequence_id;
1803 END LOOP;
1804
1805 IF Bom_Globals.Get_Debug = 'Y'
1806 THEN
1807 Error_Handler.Write_Debug('BillSeq Defaulted to: ' ||
1808 to_char(l_rev_item_unexp_rec.bill_sequence_id));
1809 END IF;
1810 END IF;
1811 ************************************************************************/
1812
1813
1814 -- If either Use Up Plan or Use Up Item has changed, get new scheduled date
1815
1816 IF Bom_Globals.Get_Debug = 'Y' THEN
1817 Error_Handler.Write_Debug('Before Getting New Scheduled Date, Start Effective Date : '
1818 ||to_char(l_revised_item_rec.start_effective_date) );
1819 END IF;
1820
1821 IF p_control_rec.caller_type <> 'FORM' -- Bug1906633
1822 THEN
1823
1824
1825 IF ( l_revised_item_rec.use_up_plan_name <>
1826 p_old_revised_item_rec.use_up_plan_name OR
1827 ( l_revised_item_rec.use_up_plan_name IS NULL AND
1828 p_old_revised_item_rec.use_up_plan_name IS NOT NULL
1829 ) OR
1830 ( p_old_revised_item_rec.use_up_plan_name IS NULL AND
1831 l_revised_item_rec.use_up_plan_name IS NOT NULL
1832 )
1833 )
1834 OR
1835 ( l_rev_item_unexp_rec.use_up_item_id <>
1836 p_old_rev_item_unexp_rec.use_up_item_id OR
1837 ( l_rev_item_unexp_rec.use_up_item_id IS NULL AND
1838 p_old_rev_item_unexp_rec.use_up_item_id IS NOT NULL
1839 ) OR
1840 ( p_old_rev_item_unexp_rec.use_up_item_id IS NULL AND
1841 l_rev_item_unexp_rec.use_up_item_id IS NOT NULL
1842 )
1843 )
1844 THEN
1845
1846 IF Bom_Globals.Get_Debug = 'Y' THEN
1847 Error_Handler.Write_Debug('If either Use Up Plan or Use Up Item has changed, get new scheduled date');
1848 Error_Handler.Write_Debug('Use Up Item Id : ' ||to_char(l_rev_item_unexp_rec.use_up_item_id ) );
1849 Error_Handler.Write_Debug('Use Up Plan Name : ' ||l_revised_item_rec.use_up_plan_name );
1850 END IF;
1851
1852 IF l_rev_item_unexp_rec.use_up_item_id IS NOT NULL AND
1853 l_rev_item_unexp_rec.use_up_item_id <> FND_API.G_MISS_NUM AND -- Added by MK on 10/31/00
1854 l_revised_item_rec.use_up_plan_name IS NOT NULL
1855 THEN
1856 G_OLD_SCHED_DATE := l_revised_item_rec.start_effective_date; -- 6657209
1857 l_revised_item_rec.start_effective_date :=
1858 Get_Scheduled_Date( p_use_up_item_id =>
1859 l_rev_item_unexp_rec.use_up_item_id
1860 , p_use_up_plan_name =>
1861 l_revised_item_rec.use_up_plan_name
1862 , p_revised_item_id =>
1863 l_rev_item_unexp_rec.revised_item_id
1864 , p_organization_id =>
1865 l_rev_item_unexp_rec.organization_id
1866 , p_bill_sequence_id =>
1867 l_rev_item_unexp_rec.bill_sequence_id
1868 );
1869 -- Also set Use_Up to 1
1870 l_rev_item_unexp_rec.use_up := 1;
1871
1872 ELSIF l_revised_item_rec.start_effective_date IS NULL
1873 THEN
1874 l_revised_item_rec.start_effective_date := SYSDATE;
1875 l_rev_item_unexp_rec.use_up := 2;
1876 G_OLD_SCHED_DATE := null; -- 6657209
1877 END IF;
1878 END IF;
1879
1880 END IF; -- Bug 1906633
1881 IF Bom_Globals.Get_Debug = 'Y' THEN
1882 Error_Handler.Write_Debug('After getting new schedule date, Start Effective Date : '
1883 ||to_char(l_revised_item_rec.start_effective_date) );
1884 END IF;
1885
1886 /* -- Moved to ENGLRITB.pls where this flag is set based on
1887 -- validation in Check_Reschedule
1888 -- By AS on 10/12/99
1889
1890 IF l_revised_item_rec.new_effective_date IS NOT NULL AND
1891 l_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
1892 THEN
1893 G_SCHED_DATE_CHANGED := TRUE;
1894 END IF;
1895 */
1896
1897 -- Added by MK on 11/13/00
1898 -- If user is tring to reschedule revised item , set
1899 -- G_SCHED_DATE_CHANGED to True
1900 -- For the Eco form, this flag should be set to true in Entity Defaulting
1901 -- once, this flag is overwritten based on validation in ENGLRITB.pls
1902 -- set this flag, if from unit number is changed, we need to update
1903 -- components in this case also.
1904 IF p_revised_item_rec.Transaction_Type = ENG_Globals.G_OPR_UPDATE
1905 AND (NVL( p_revised_item_rec.new_effective_date,
1906 p_revised_item_rec.start_effective_date )
1907 <> p_old_revised_item_rec.start_effective_date)
1908 THEN
1909
1910 IF Bom_Globals.Get_Debug = 'Y' THEN
1911 Error_Handler.Write_Debug('Scheduled Date is been trying to udpate. . .');
1912 END IF;
1913 G_SCHED_DATE_CHANGED := TRUE;
1914 END IF;
1915
1916
1917 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Defualting based on Status Type . . .'); END IF;
1918
1919 IF l_revised_item_rec.status_type <>
1920 p_old_revised_item_rec.status_type
1921 THEN
1922
1923 -- Scheduled
1924
1925 IF l_revised_item_rec.status_type = 4
1926 THEN
1927
1928 l_ECO_Approved := 0;
1929 FOR x_count IN c_CheckApproval LOOP
1930 l_ECO_Approved := 1;
1931 END LOOP;
1932
1933 IF l_ECO_approved = 1
1934 THEN
1935 l_rev_item_unexp_rec.auto_implement_date :=
1936 SYSDATE;
1937 ELSE
1938 l_rev_item_unexp_rec.auto_implement_date :=
1939 NULL;
1940 END IF;
1941
1942 ELSE
1943 l_rev_item_unexp_rec.auto_implement_date := NULL;
1944 END IF;
1945
1946 -- Hold
1947
1948 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Checking if status type = HOLD . . .'); END IF;
1949
1950 /* Changed upon ITI's request. Earlier warnings weren't being
1951 logged and mrp_active blindly being overwritten
1952 By AS on 11/10/99
1953 */
1954 IF l_revised_item_rec.transaction_type = ENG_Globals.G_OPR_UPDATE AND
1955 l_revised_item_rec.status_type = 2 AND
1956 l_revised_item_rec.status_type <>
1957 p_old_revised_item_rec.status_type AND
1958 l_revised_item_rec.mrp_active <> 2
1959 THEN
1960 l_revised_item_rec.mrp_active := 2;
1961 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1962 THEN
1963 Error_Handler.Add_Error_Token
1964 ( p_Message_Name => 'ENG_SET_MRP_ACTIVE_NO'
1965 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1966 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1967 , p_Token_Tbl => l_Token_Tbl
1968 , p_message_type => 'W');
1969 END IF;
1970 ELSIF l_revised_item_rec.transaction_type = ENG_Globals.G_OPR_UPDATE AND
1971 l_revised_item_rec.status_type <> 2 AND
1972 l_revised_item_rec.status_type <>
1973 p_old_revised_item_rec.status_type AND
1974 l_revised_item_rec.mrp_active <> 1
1975 -- add the next line for fixing BUG 1577957
1976 AND nvl(l_revised_item_rec.eco_for_production,2) = 2
1977 -- add the next line for fixing BUG 2218574
1978 AND fnd_profile.value('ENG:DEFAULT_MRP_ACTIVE') = '1'
1979 THEN
1980 l_revised_item_rec.mrp_active := 1;
1981 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
1982 THEN
1983 Error_Handler.Add_Error_Token
1984 ( p_Message_Name => 'ENG_SET_MRP_ACTIVE_YES'
1985 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1986 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1987 , p_Token_Tbl => l_Token_Tbl
1988 , p_message_type => 'W');
1989 END IF;
1990 END IF;
1991
1992 -- Cancelled
1993
1994 IF l_revised_item_rec.status_type = 5
1995 THEN
1996 -- get the requestor id.
1997 IF l_rev_item_unexp_rec.requestor_id IS NULL
1998 THEN
1999 l_rev_item_unexp_rec.requestor_id :=
2000 Get_Requestor
2001 ( p_change_notice =>
2002 l_revised_item_rec.eco_name
2003 , p_organization_id =>
2004 l_rev_item_unexp_rec.organization_id
2005 );
2006 END IF;
2007 l_rev_item_unexp_rec.cancellation_date := SYSDATE;
2008 END IF;
2009
2010 END IF; /* Status Type updation Ends */
2011
2012
2013 IF l_revised_item_rec.updated_revised_item_revision = FND_API.G_MISS_CHAR
2014 THEN
2015 l_revised_item_rec.updated_revised_item_revision := NULL;
2016 END IF;
2017
2018 IF l_revised_item_rec.new_effective_date = FND_API.G_MISS_DATE
2019 THEN
2020 l_revised_item_rec.new_effective_date := NULL;
2021 END IF;
2022
2023 IF l_rev_item_unexp_rec.use_up_item_id = FND_API.G_MISS_NUM
2024 THEN
2025 l_rev_item_unexp_rec.use_up_item_id := NULL;
2026 END IF;
2027
2028 -- Code section from From End Item Unit Number and
2029 -- New From End ITem Unit Number added by As on 07/06/99
2030
2031 IF l_revised_item_rec.from_end_item_unit_number = FND_API.G_MISS_CHAR
2032 THEN
2033 l_revised_item_rec.from_end_item_unit_number := NULL;
2034 END IF;
2035
2036 IF l_revised_item_rec.new_from_end_item_unit_number = FND_API.G_MISS_CHAR
2037 THEN
2038 l_revised_item_rec.new_from_end_item_unit_number := NULL;
2039 END IF;
2040
2041 -- Added by MK on 11/15/00
2042 IF l_revised_item_rec.new_revised_item_revision = FND_API.G_MISS_CHAR
2043 THEN
2044 l_revised_item_rec.new_revised_item_revision := NULL;
2045 END IF;
2046
2047 IF l_revised_item_rec.new_routing_revision = FND_API.G_MISS_CHAR
2048 THEN
2049 l_revised_item_rec.new_routing_revision := NULL;
2050 END IF;
2051
2052 IF l_revised_item_rec.updated_routing_revision = FND_API.G_MISS_CHAR
2053 THEN
2054 l_revised_item_rec.updated_routing_revision := NULL;
2055 END IF;
2056
2057
2058 /***********************************************************************
2059 -- Added by MK on 09/01/2000
2060 -- For New ECO Effectivities and ECO Routing
2061 ***********************************************************************/
2062 -- Initialize flags
2063 G_CREATE_RTG_ALTERNATE := FALSE;
2064 G_DEL_UPD_INS_RTG_REV := 0;
2065
2066 /***********************************************************************
2067 -- Routing Revision Defaulting
2068 -- Added by MK on 09/01/2000
2069 ***********************************************************************/
2070 -- Modified by MK on 10/24/00
2071 IF (( (l_revised_item_rec.updated_routing_revision <>
2072 l_revised_item_rec.new_routing_revision )
2073 OR
2074 ( l_revised_item_rec.updated_routing_revision IS NULL AND
2075 l_revised_item_rec.new_routing_revision IS NOT NULL )
2076 OR
2077 ( l_revised_item_rec.updated_routing_revision IS NOT NULL AND
2078 l_revised_item_rec.new_routing_revision IS NULL )
2079 )
2080 AND l_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
2081 )
2082 OR
2083 l_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_CREATE
2084 THEN
2085
2086 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
2087 ( 'Checking for routing revision: ' || p_revised_item_rec.new_routing_revision );
2088 END IF;
2089
2090 IF l_revised_item_rec.transaction_type =
2091 ENG_Globals.G_OPR_CREATE
2092 THEN
2093 FOR x_count IN c_Rtg_CheckRevision
2094 ( p_revision => l_revised_item_rec.new_routing_revision)
2095 LOOP
2096
2097 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Current Routing Revision Found . . .'); END IF;
2098
2099 l_rtg_rev_already_exists := 1;
2100 END LOOP;
2101
2102 l_current_rtg_revision :=
2103 Get_Current_Rtg_Revision
2104 ( p_revised_item_id => l_rev_item_unexp_rec.revised_item_id
2105 , p_organization_id => l_rev_item_unexp_rec.organization_id
2106 , p_revision_date => SYSDATE
2107 );
2108
2109 -- Can insert a revision into MTL_RTG_ITEM_REVISIONS only
2110 -- if it is not the same as the Current Revision
2111 -- Bug 4311691: Modified the if clause to check for l_rtg_rev_already_exists
2112 IF l_revised_item_rec.new_routing_revision <> l_current_rtg_revision AND
2113 l_rtg_rev_already_exists = 0
2114 THEN
2115
2116 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Rtg Revision set for Insert . . .'); END IF;
2117
2118 G_DEL_UPD_INS_RTG_REV := 3;
2119 ELSIF l_rtg_rev_already_exists = 1 THEN
2120
2121 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Rtg Revision set for Update . . .'); END IF;
2122
2123 G_DEL_UPD_INS_RTG_REV := 2;
2124 END IF;
2125
2126 ELSIF l_revised_item_rec.transaction_type = ENG_Globals.G_OPR_UPDATE
2127 THEN
2128
2129 l_current_rtg_revision :=
2130 Get_Current_Rtg_Revision
2131 ( p_revised_item_id => l_rev_item_unexp_rec.revised_item_id
2132 , p_organization_id => l_rev_item_unexp_rec.organization_id
2133 , p_revision_date => SYSDATE
2134 );
2135
2136 FOR x_count IN c_Rtg_CheckRevision
2137 ( p_revision =>
2138 l_revised_item_rec.updated_routing_revision
2139 )
2140 LOOP
2141 l_rtg_rev_already_exists := 1;
2142 END LOOP;
2143
2144 IF l_rtg_rev_already_exists = 0 AND
2145 l_revised_item_rec.updated_routing_revision <>
2146 FND_API.G_MISS_CHAR
2147 THEN
2148 --
2149 -- Insert updated routing revision information into
2150 -- MTL_RTG_ITEM_REVISIONS
2151 IF l_revised_item_rec.new_routing_revision IS NULL OR
2152 l_revised_item_rec.new_routing_revision
2153 = l_current_rtg_revision -- Added by MK on 02/13/2001
2154 THEN
2155 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Rtg Revision set for Insert . . .'); END IF;
2156
2157 G_DEL_UPD_INS_RTG_REV := 3;
2158 ELSE
2159 --
2160 -- Modified by MK on 10/24/00
2161 -- Update new routing revision information into
2162 -- MTL_RTG_ITEM_REVISIONS
2163 --
2164 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Rtg Revision set for Update. . .'); END IF;
2165
2166 G_DEL_UPD_INS_RTG_REV := 2 ;
2167
2168 END IF ;
2169
2170 ELSIF ( l_rtg_rev_already_exists = 0 AND
2171 ( l_revised_item_rec.updated_routing_revision
2172 = FND_API.G_MISS_CHAR OR
2173 l_revised_item_rec.updated_routing_revision
2174 IS NULL )
2175 )
2176 OR -- Added by MK on 02/13/2001 for Bug 1641488
2177 ( l_rtg_rev_already_exists = 1 AND
2178 l_revised_item_rec.updated_routing_revision
2179 = l_current_rtg_revision
2180 )
2181 THEN
2182
2183 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Rtg Revision set for Delete. . .'); END IF;
2184
2185 G_DEL_UPD_INS_RTG_REV := 1;
2186
2187 ELSIF l_rtg_rev_already_exists = 1 AND
2188 l_revised_item_rec.updated_routing_revision <> l_current_revision
2189 THEN
2190 -- Update new routing revision information in
2191 -- MTL_RTG_ITEM_REVISIONS
2192
2193 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Rtg Revision set for Update. . .'); END IF;
2194 G_DEL_UPD_INS_RTG_REV := 2;
2195
2196 END IF; /* If Update Ends */
2197 END IF; /* If Create or Update Ends */
2198
2199 END IF; /* Main If ends */
2200
2201 /***********************************************************************
2202 -- Routing Sequence Id Defaulting
2203 -- Added by MK on 09/01/2000
2204 -- Set the global flag G_CREATE_RTG_ALTERNATE to TRUE if the user is
2205 -- trying to create an alternate
2206 -- if Primary Routing has already existed.
2207 ***********************************************************************/
2208
2209 IF l_revised_item_rec.alternate_bom_code IS NOT NULL AND
2210 NOT Check_Alt_Rtg_Already_Exists
2211 ( p_revised_item_id => l_rev_item_unexp_rec.revised_item_id
2212 , p_alternate_bom_designator => l_revised_item_rec.alternate_bom_code
2213 , p_organization_id => l_rev_item_unexp_rec.organization_id
2214 )
2215 AND
2216 Compatible_Primary_Rtg_Exists
2217 ( p_revised_item_id => l_rev_item_unexp_rec.revised_item_id
2218 , p_change_notice => l_revised_item_rec.eco_name
2219 , p_organization_id => l_rev_item_unexp_rec.organization_id
2220 )
2221 AND (Bom_globals.Get_Caller_Type <> BOM_GLOBALS.G_MASS_CHANGE)
2222 THEN
2223 l_rev_item_unexp_rec.routing_sequence_id :=
2224 Bom_Default_Rtg_Header.Get_Routing_Sequence ;
2225 -- to generate new routing sequence id
2226
2227 IF Bom_Globals.Get_Debug = 'Y' THEN
2228 Error_Handler.Write_Debug('Routing seq id is generated for an alternate routing . . . : ' ||
2229 to_char(l_rev_item_unexp_rec.routing_sequence_id));
2230 END IF;
2231
2232 IF Bom_Globals.Get_Debug = 'Y' THEN
2233 Error_Handler.Write_Debug('Setting creat alternate bill flag to True. . . ' ) ;
2234 END IF;
2235
2236 G_CREATE_RTG_ALTERNATE := TRUE;
2237
2238
2239 END IF;
2240
2241 /***********************************************************************
2242 -- Comment out by MK on 02/15/2001
2243 -- Because this code is duplicate the logic in Attribute Defaulting and
2244 -- ENG_Val_To_Id.Revised_Item_VID
2245
2246 IF ( l_rev_item_unexp_rec.routing_sequence_id IS NULL OR
2247 l_rev_item_unexp_rec.routing_sequence_id = FND_API.G_MISS_NUM )
2248 AND l_revised_item_rec.alternate_bom_code IS NULL -- Added by MK on 10/31/00
2249 THEN
2250 --
2251 -- If the user is simply trying to add a revised item that
2252 -- already exists on another ECO and has a routing for it
2253 -- then get the routing sequence and default it.
2254 --
2255 FOR routing_seq IN c_Get_Routing_Seq LOOP
2256 l_rev_item_unexp_rec.routing_sequence_id :=
2257 routing_seq.routing_sequence_id;
2258 END LOOP;
2259
2260 IF Bom_Globals.Get_Debug = 'Y' THEN
2261 Error_Handler.Write_Debug('Routing Seq Id Defaulted to: ' ||
2262 to_char(l_rev_item_unexp_rec.routing_sequence_id));
2263 END IF;
2264
2265 END IF;
2266 ***********************************************************************/
2267
2268 /***********************************************************************
2269 -- Set Missig Columns to Null
2270 -- Added by MK on 09/01/2000
2271 ***********************************************************************/
2272 -- Added by MK for ECO Routing
2273
2274 IF l_revised_item_rec.from_cumulative_quantity = FND_API.G_MISS_NUM
2275 THEN
2276 l_revised_item_rec.from_cumulative_quantity := NULL ;
2277 END IF ;
2278
2279 IF l_revised_item_rec.lot_number = FND_API.G_MISS_CHAR
2280 THEN
2281 l_revised_item_rec.lot_number := NULL ;
2282 END IF ;
2283
2284 IF l_revised_item_rec.completion_subinventory = FND_API.G_MISS_CHAR
2285 THEN
2286 l_revised_item_rec.completion_subinventory := NULL ;
2287 END IF ;
2288
2289 IF l_revised_item_rec.priority = FND_API.G_MISS_NUM
2290 THEN
2291 l_revised_item_rec.priority := NULL ;
2292 END IF ;
2293
2294 IF l_revised_item_rec.routing_comment = FND_API.G_MISS_CHAR
2295 THEN
2296 l_revised_item_rec.routing_comment := NULL ;
2297 END IF ;
2298
2299 IF l_rev_item_unexp_rec.from_wip_entity_id = FND_API.G_MISS_NUM
2300 THEN
2301 l_rev_item_unexp_rec.from_wip_entity_id := NULL ;
2302 END IF ;
2303
2304
2305 IF l_rev_item_unexp_rec.to_wip_entity_id = FND_API.G_MISS_NUM
2306 THEN
2307 l_rev_item_unexp_rec.to_wip_entity_id := NULL ;
2308 END IF ;
2309
2310 IF l_rev_item_unexp_rec.completion_locator_id = FND_API.G_MISS_NUM
2311 THEN
2312 l_rev_item_unexp_rec.completion_locator_id := NULL ;
2313 END IF ;
2314
2315
2316 -- Updated by MK on 09/01/2000
2317 /***********************************************************************
2318 -- MRP Active and WIP Update Defaulting
2319 -- Added by MK on 09/01/2000
2320 ***********************************************************************/
2321
2322 IF Bom_Globals.Get_Debug = 'Y' THEN
2323 Error_Handler.Write_Debug('Before MRP Active and WIP Update Defaulting') ;
2324 Error_Handler.Write_Debug('MRP Active : ' || to_char(l_revised_item_rec.mrp_active )) ;
2325 Error_Handler.Write_Debug('Update Wip: ' || to_char(l_revised_item_rec.update_wip )) ;
2326 Error_Handler.Write_Debug('Lot Num : ' || l_revised_item_rec.lot_number ) ;
2327 Error_Handler.Write_Debug('Cum Qty : ' || to_char(l_revised_item_rec.from_cumulative_quantity)) ;
2328 Error_Handler.Write_Debug('From Wo : ' || to_char(l_rev_item_unexp_rec.from_wip_entity_id )) ;
2329 Error_Handler.Write_Debug('To Wo : ' || to_char(l_rev_item_unexp_rec.to_wip_entity_id)) ;
2330 END IF ;
2331
2332 -- Modified by MK on 10/30/2000
2333 IF l_revised_item_rec.transaction_type = ENG_Globals.G_OPR_CREATE
2334 AND (l_revised_item_rec.mrp_active <> 2 OR
2335 l_revised_item_rec.update_wip <> 1 )
2336 AND
2337 ( l_revised_item_rec.lot_number IS NOT NULL OR
2338 l_revised_item_rec.from_cumulative_quantity IS NOT NULL OR
2339 l_rev_item_unexp_rec.to_wip_entity_id IS NOT NULL OR
2340 l_rev_item_unexp_rec.from_wip_entity_id IS NOT NULL
2341 )
2342 THEN
2343 -- Set MRP_Active to No and Update_Wip to Yes
2344 l_revised_item_rec.mrp_active := 2;
2345 l_revised_item_rec.update_wip := 1;
2346
2347 IF Bom_Globals.Get_Debug = 'Y' THEN
2348 Error_Handler.Write_Debug('Mrp Active and Update Wip are set to Yes') ;
2349 Error_Handler.Write_Debug('MRP Active : ' || to_char(l_revised_item_rec.mrp_active )) ;
2350 Error_Handler.Write_Debug('UPDATE Wip: ' || to_char(l_revised_item_rec.update_wip )) ;
2351 END IF ;
2352 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
2353 THEN
2354 Error_Handler.Add_Error_Token
2355 ( p_Message_Name => 'ENG_SET_WO_EFFECTIVITY_FLAG'
2356 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2357 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2358 , p_Token_Tbl => l_Token_Tbl
2359 , p_message_type => 'W');
2360 END IF;
2361 END IF ;
2362
2363 /***********************************************************************
2364 -- Set G_ECO_FOR_PROD_CHANGED to True if user is trying to
2365 -- update ECO_FOR_PRODUCTION
2366 -- Added by MK on 10/24/2000
2367 ***********************************************************************/
2368 IF l_revised_item_rec.transaction_type = ENG_GLOBALS.G_OPR_UPDATE
2369 AND ( l_revised_item_rec.eco_for_production
2370 <> p_old_revised_item_rec.eco_for_production OR
2371 ( l_revised_item_rec.eco_for_production IS NOT NULL AND
2372 p_old_revised_item_rec.eco_for_production IS NULL )
2373 )
2374 THEN
2375
2376 IF Bom_Globals.Get_Debug = 'Y' THEN
2377 Error_Handler.Write_Debug('Eco for Prod has been changed. . . Yes') ;
2378 END IF ;
2379 G_ECO_FOR_PROD_CHANGED := TRUE;
2380 END IF;
2381
2382 -- Load out record
2383 x_revised_item_rec := l_revised_item_rec;
2384 x_rev_item_unexp_rec := l_rev_item_unexp_rec;
2385 x_Mesg_Token_Tbl := l_MEsg_Token_Tbl;
2386 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
2387
2388 END Entity_Defaulting;
2389
2390
2391 END ENG_Default_Revised_Item;