[Home] [Help]
PACKAGE BODY: APPS.ENG_REVISED_ITEM_UTIL
Source
1 PACKAGE BODY ENG_Revised_Item_Util AS
2 /* $Header: ENGURITB.pls 120.7.12010000.2 2008/11/04 18:26:40 sanmani ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ENG_Revised_Item_Util';
7
8
9
10 /*****************************************************************************
11 * Added by MK on 09/01/2000
12 * Procedure : Delete_Routing_Details
13 * Parameters IN : Revised Item and Routing Header Key Column.
14 * Purpose : Delete Routing Details Routing from Tables.
15 *****************************************************************************/
16 PROCEDURE Delete_Routing_Details
17 ( p_organization_id IN NUMBER
18 , p_revised_item_id IN NUMBER
19 , p_revised_item_sequence_id IN NUMBER
20 , p_routing_sequence_id IN NUMBER
21 , p_change_notice IN VARCHAR2 )
22 IS
23
24
25 BEGIN
26 DELETE FROM MTL_RTG_ITEM_REVISIONS
27 where organization_id = p_organization_id
28 and inventory_item_id = p_revised_item_id
29 and revised_item_sequence_Id = p_revised_item_sequence_id
30 and change_notice = p_change_notice
31 and implementation_date is null;
32
33 DELETE FROM ENG_CURRENT_SCHEDULED_DATES
34 where organization_id = p_organization_id
35 and revised_item_id = p_revised_item_id
36 and revised_item_sequence_Id = p_revised_item_sequence_id
37 and change_notice = p_change_notice ;
38
39 DELETE FROM BOM_OPERATIONAL_ROUTINGS bor
40 where bor.routing_sequence_id = p_routing_sequence_id
41 and bor.pending_from_ecn = p_change_notice
42 and not exists (select null
43 from BOM_OPERATION_SEQUENCES bos
44 where bos.routing_sequence_id = bor.routing_sequence_id
45 and (bos.change_notice is null
46 or
47 bos.change_notice <> p_change_notice
48 or
49 (bos.change_notice = p_change_notice
50 and bos.revised_item_sequence_id <> p_revised_item_sequence_id)))
51 and ((bor.alternate_routing_designator is null
52 and not exists (select null
53 from BOM_OPERATIONAL_ROUTINGS bor2
54 where bor2.organization_id = bor.organization_id
55 and bor2.assembly_item_id = bor.assembly_item_id
56 and bor2.alternate_routing_designator is not null))
57 or
58 (bor.alternate_routing_designator is not null))
59 and not exists (select null
60 from ENG_REVISED_ITEMS eri
61 where eri.organization_id = bor.organization_id
62 and eri.bill_sequence_id = bor.routing_sequence_id
63 and eri.change_notice <> p_change_notice
64 );
65
66 END Delete_Routing_Details;
67 -- Added by MK on 09/01/2000
68
69
70 /*****************************************************************************
71 * Added by MK on 09/01/2000
72 * Procedure : Insert_Routing_Revisions
73 * Parameters IN : Routing Revision Column.
74 * Purpose : Insert the New Routing Revision Record into MTL_RTG_ITEM_REVISIONS
75 ****************************************************************************/
76 PROCEDURE Insert_Routing_Revisions
77 ( p_inventory_item_id IN NUMBER
78 , p_organization_id IN NUMBER
79 , p_revision IN VARCHAR2
80 , p_user_id IN NUMBER
81 , p_login_id IN NUMBER
82 , p_change_notice IN VARCHAR2
83 , p_effectivity_date IN DATE
84 , p_revised_item_sequence_id IN NUMBER
85 )
86 IS
87 BEGIN
88
89 INSERT INTO MTL_RTG_ITEM_REVISIONS
90 ( inventory_item_id
91 , organization_id
92 , process_revision
93 , last_update_date
94 , last_updated_by
95 , creation_date
96 , created_by
97 , last_update_login
98 , change_notice
99 , ecn_initiation_date
100 , effectivity_date
101 , revised_item_sequence_id
102 )
103 VALUES
104 ( p_inventory_item_id
105 , p_organization_id
106 , p_revision
107 , SYSDATE
108 , p_user_id
109 , SYSDATE
110 , p_user_id
111 , p_login_id
112 , p_change_notice
113 , SYSDATE
114 , DECODE(p_effectivity_date
115 , TRUNC(SYSDATE), SYSDATE
116 , p_effectivity_date)
117 , p_revised_item_sequence_id
118 ) ;
119
120 END Insert_Routing_Revisions ;
121 -- Added by MK on 09/01/2000
122
123
124
125
126 /*****************************************************************************
127 * Procedure : Cancel_ECO
128 * Parameters IN : Organization_id
129 * Change notice
130 * Mesg Token Table
131 * Parameters OUT: Mesg Token Table
132 * Return Status
133 * Purpose : If revised item is being cancelled, AND the revised item is
134 * the last revised item on the ECO, AND all the existing
135 * revised items have been implemented or cancelled, do one of
136 * the following:
137 * 1) Set ECO status to IMPLEMENTED if there are any implemented
138 * revised items on the ECO
139 * 2) Set ECO status to CANCELLED if there are no implemented
140 * revised items on the ECO
141 * History : Added by AS on 09/22/99 to include bug fix for 980294.
142 ******************************************************************************/
143 Procedure Cancel_ECO
144 ( p_organization_id IN NUMBER
145 , p_change_notice IN VARCHAR2
146 , p_user_id IN NUMBER
147 , p_login IN NUMBER
148 , p_Mesg_Token_Tbl IN Error_Handler.Mesg_Token_Tbl_Type
149 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
150 , x_return_status OUT NOCOPY VARCHAR2
151 )
152 IS
153 l_err_text VARCHAR2(2000) := NULL;
154 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type := p_Mesg_Token_Tbl;
155 l_token_tbl Error_Handler.Token_Tbl_Type;
156 X_EcoStatus number;
157 Cursor CheckEco is
158 Select 'x' dummy
159 From dual
160 Where not exists (
161 Select null
162 From eng_revised_items eri
163 Where eri.change_notice = p_change_notice
164 And eri.organization_id = p_organization_id
165 And eri.status_type not in (6, 5));
166
167 Cursor CheckStatusEco is
168 Select 'x' dummy
169 From dual
170 Where exists (
171 Select null
172 From eng_revised_items eri
173 Where eri.change_notice = p_change_notice
174 And eri.organization_id = p_organization_id
175 And eri.status_type = 6);
176 BEGIN
177 X_EcoStatus := 5;
178 For X_NewStatus in CheckEco loop
179 l_token_tbl.delete;
180 l_token_tbl(1).token_name := 'ECO_NAME';
181 l_token_tbl(1).token_value := p_change_notice;
182 For X_NewStatus in CheckStatusEco loop
183
184 -- Change ECO status to implemented.
185
186 UPDATE ENG_ENGINEERING_CHANGES
187 SET IMPLEMENTATION_DATE = SYSDATE,
188 STATUS_TYPE = 6,
189 LAST_UPDATED_BY = p_user_id,
190 LAST_UPDATE_LOGIN = p_login
191 WHERE ORGANIZATION_ID = p_organization_id
192 AND CHANGE_NOTICE = p_change_notice;
193 X_EcoStatus := 6;
194
195 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
196 THEN
197 Error_Handler.Add_Error_Token
198 ( p_Message_Name => 'ENG_LAST_ITEM_CANCL_ECO_IMPL'
199 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
200 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
201 , p_Token_Tbl => l_Token_Tbl
202 , p_message_type => 'W');
203 END IF;
204 End loop;
205
206 if (X_EcoStatus = 5) then
207
208 -- Change ECO status to canceled.
209
210 UPDATE ENG_ENGINEERING_CHANGES
211 SET CANCELLATION_DATE = SYSDATE,
212 STATUS_TYPE = 5,
213 LAST_UPDATED_BY = p_user_id,
214 LAST_UPDATE_LOGIN = p_login
215 WHERE ORGANIZATION_ID = p_organization_id
216 AND CHANGE_NOTICE = p_change_notice;
217
218 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
219 THEN
220 Error_Handler.Add_Error_Token
221 ( p_Message_Name => 'ENG_LAST_ITEM_CANCL_ECO_CANCL'
222 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
223 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
224 , p_Token_Tbl => l_Token_Tbl
225 , p_message_type => 'W');
226 END IF;
227 end if;
228 End loop;
229 x_return_status := FND_API.G_RET_STS_SUCCESS;
230
231 EXCEPTION
232 WHEN OTHERS THEN
233 l_err_text := G_PKG_NAME || ' :(Cancel_ECO)-Revised Item '
234 || substrb(SQLERRM,1,200);
235 Error_Handler.Add_Error_Token
236 ( p_Message_Name => NULL
237 , p_Message_Text => l_Err_Text
238 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
239 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
240 );
241 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
242 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
243
244 END Cancel_ECO;
245
246 /*****************************************************************************
247 * Procedure : Cancel_Revised_Item
248 * Parameters IN : Revised item sequence Id
249 * Bill Sequence Id
250 * User Id
251 * Login Id
252 * Change notice
253 * Parameters OUT: Mesg Token Table
254 * Return Status
255 * Purpose : Procedure will perform the cancellation of a revised item.
256 * In doing so the procedure will delete the corresponding
257 * revisions and will also make sure that the underlying
258 * entities also get cancelled.
259 *
260 * History : 09/01/2000 MK ECO for Routing.
261 ******************************************************************************/
262 Procedure Cancel_Revised_Item
263 ( rev_item_seq IN NUMBER
264 , bill_seq_id IN NUMBER
265 , routing_seq_id IN NUMBER -- Added by MK on 09/01/2000
266 , user_id IN NUMBER
267 , login IN NUMBER
268 , change_order IN VARCHAR2
269 , cancel_comments IN VARCHAR2
270 , p_Mesg_Token_Tbl IN Error_Handler.Mesg_Token_Tbl_Type
271 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
272 , x_return_status OUT NOCOPY VARCHAR2
273 )
274 IS
275 l_err_text VARCHAR2(2000) := NULL;
276 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type := p_Mesg_Token_Tbl;
277
278 CURSOR c_getRevisedComps IS
279 SELECT component_sequence_id
280 FROM bom_inventory_components
281 WHERE revised_item_sequence_id = rev_item_seq;
282
283
284 CURSOR c_getRevisedOps IS
285 SELECT operation_sequence_id
286 , operation_seq_num
287 FROM BOM_OPERATION_SEQUENCES
288 WHERE revised_item_sequence_id = rev_item_seq ;
289
290 -- Changes for Bug 3668603
291 -- Cursor to check if the routing header used in the revised item has any references
292 Cursor c_check_rtg_header_del is
293 select routing_sequence_id
294 from bom_operational_routings bor
295 where bor.routing_sequence_id = routing_seq_id
296 and bor.pending_from_ecn = change_order
297 and not exists (select null
298 from BOM_OPERATION_SEQUENCES bos
299 where bos.routing_sequence_id = bor.routing_sequence_id
300 and (bos.change_notice is null
301 or
302 bos.change_notice <> change_order
303 or
304 (bos.change_notice = change_order
305 and bos.revised_item_sequence_id <> rev_item_seq)))
306 and ((bor.alternate_routing_designator is null
307 and not exists (select null
308 from BOM_OPERATIONAL_ROUTINGS bor2
309 where bor2.organization_id = bor.organization_id
310 and bor2.assembly_item_id = bor.assembly_item_id
311 and bor2.alternate_routing_designator is not null)
312 and not exists (select null
313 from MTL_RTG_ITEM_REVISIONS mriv
314 where mriv.organization_id = bor.organization_id
315 and mriv.inventory_item_id = bor.assembly_item_id
316 and mriv.implementation_date is not null
317 and mriv.change_notice is null))
318 or
319 (bor.alternate_routing_designator is not null))
320 and not exists (select null
321 from ENG_REVISED_ITEMS eri
322 where eri.organization_id = bor.organization_id
323 and eri.routing_sequence_id = bor.routing_sequence_id
324 and eri.revised_item_sequence_id <> rev_item_seq
325 and eri.status_type <> 5);
326
327 l_del_rtg_header NUMBER;
328 -- End changes for bug 3668603
329
330 BEGIN
331
332 x_return_status := FND_API.G_RET_STS_SUCCESS;
333
334 FOR rev_comp IN c_getRevisedComps
335 LOOP
336 Bom_Bom_Component_Util.Cancel_Component
337 ( p_component_sequence_id => rev_comp.component_sequence_id
338 , p_cancel_comments => cancel_comments
339 , p_user_id => user_id
340 , p_login_id => login
341 );
342 END LOOP;
343
344 -- Delete the rows from bom_inventory_components
345
346 DELETE FROM bom_components_b --BOM_INVENTORY_COMPONENTS IC -- R12: Modified for common bom changes
347 WHERE REVISED_ITEM_SEQUENCE_ID = rev_item_seq;
348
349 -- Delete item revisions created by revised items on ECO
350 /* delete from MTL_ITEM_REVISIONS_TL
351 where revision_id IN (select revision_id
352 from MTL_ITEM_REVISIONS_B
353 where REVISED_ITEM_SEQUENCE_ID = rev_item_seq);*/
354 -- Added revision_id to where clause for performance bug 4251776
355 delete from MTL_ITEM_REVISIONS_TL
356 where revision_id IN (select new_item_revision_id
357 from eng_revised_items I
358 WHERE REVISED_ITEM_SEQUENCE_ID = rev_item_seq);
359 DELETE FROM MTL_ITEM_REVISIONS_B I
360 where revision_id IN (select new_item_revision_id
361 from eng_revised_items I
362 WHERE REVISED_ITEM_SEQUENCE_ID = rev_item_seq);
363 /*WHERE REVISED_ITEM_SEQUENCE_ID = rev_item_seq;*/
364
365
366 -- Delete the bom header if bill was created by this revised item and
367 -- nothing else references this
368
369 DELETE FROM BOM_BILL_OF_MATERIALS B
370 WHERE B.BILL_SEQUENCE_ID = bill_seq_id
371 AND B.PENDING_FROM_ECN = change_order
372 AND NOT EXISTS (SELECT NULL
373 FROM BOM_INVENTORY_COMPONENTS C
374 WHERE C.BILL_SEQUENCE_ID = B.BILL_SEQUENCE_ID
375 AND (C.REVISED_ITEM_SEQUENCE_ID IS NULL
376 OR C.REVISED_ITEM_SEQUENCE_ID <> rev_item_seq))
377 AND ( (B.ALTERNATE_BOM_DESIGNATOR IS NULL
378 AND NOT EXISTS (SELECT NULL
379 FROM BOM_BILL_OF_MATERIALS B2
380 WHERE B2.ORGANIZATION_ID = B.ORGANIZATION_ID
381 AND B2.ASSEMBLY_ITEM_ID = B.ASSEMBLY_ITEM_ID
382 AND B2.ALTERNATE_BOM_DESIGNATOR IS NOT NULL))
383 OR
384 (NOT EXISTS (SELECT NULL
385 FROM ENG_REVISED_ITEMS R
386 WHERE R.ORGANIZATION_ID = B.ORGANIZATION_ID
387 AND R.BILL_SEQUENCE_ID = B.BILL_SEQUENCE_ID
388 AND R.REVISED_ITEM_SEQUENCE_ID <> rev_item_seq
389 AND R.STATUS_TYPE <> 5)));
390
391
392
393 -- If bill was deleted, then unset the bill_sequence_id on the revised item
394
395 if (SQL%ROWCOUNT > 0) then
396 UPDATE ENG_REVISED_ITEMS R
397 SET BILL_SEQUENCE_ID = ''
398 , cancel_comments = cancel_comments
399 , cancellation_date = SYSDATE
400 WHERE R.REVISED_ITEM_SEQUENCE_ID = rev_item_seq;
401 end if;
402
403
404 /***********************************************************************
405 -- Added by MK on 09/01/2000
406 -- Cancel Revised Item for ECO Routing
407 ***********************************************************************/
408
409 FOR rev_opseq IN c_getRevisedOps
410 LOOP
411 ENG_Globals.Cancel_Operation
412 ( p_operation_sequence_id => rev_opseq.operation_sequence_id
413 , p_cancel_comments => cancel_comments
414 , p_op_seq_num => rev_opseq.operation_seq_num -- Added by MK on 11/27/00
415 , p_user_id => user_id
416 , p_login_id => login
417 , p_prog_id => Bom_Rtg_Globals.Get_Prog_Id
418 , p_prog_appid => Bom_Rtg_Globals.Get_Prog_AppId
419 , x_return_status => x_return_status
420 , x_mesg_token_tbl => x_mesg_token_tbl
421 ) ;
422
423 END LOOP;
424
425 -- Delete the rows from bom_operation_sequences
426
427 DELETE FROM BOM_OPERATION_SEQUENCES
428 WHERE REVISED_ITEM_SEQUENCE_ID = rev_item_seq;
429
430 -- Delete item revisions created by revised items on ECO
431
432 DELETE FROM MTL_RTG_ITEM_REVISIONS I
433 WHERE REVISED_ITEM_SEQUENCE_ID = rev_item_seq
434 AND implementation_date IS NULL; -- bug 3668603: delete only unimplemented revisions
435
436 -- Delete the routing header if routing was created by this revised item and
437 -- nothing else references this
438 -- Bug 3668603
439 -- Before deleting the routing header, Check using cursor c_check_rtg_header_del, if it is referenced.
440 -- If referenced, the header is not deleted and bom_operational_routings.pending_for_ecn is
441 -- set to null for the routing header header if pending_form_eco =change_order
442 -- If not referenced, then delete the routing revisions if the header is a primary routing
443 -- Delete the header and unset the routing_sequence_id on the revised items
444 -- using the routing_sequence_id.
445 --
446 l_del_rtg_header := 0;
447 FOR crh IN c_check_rtg_header_del
448 LOOP
449 l_del_rtg_header := 1;
450 END LOOP;
451
452 IF (l_del_rtg_header = 1)
453 THEN
454 DELETE FROM MTL_RTG_ITEM_REVISIONS rev
455 WHERE EXISTS (SELECT 1
456 FROM BOM_OPERATIONAL_ROUTINGS bor
457 WHERE bor.routing_sequence_id = routing_seq_id
458 AND bor.alternate_routing_designator IS NULL
459 AND bor.assembly_item_id = rev.INVENTORY_ITEM_ID
460 AND bor.organization_id = rev.organization_id);
461
462 DELETE FROM BOM_OPERATIONAL_ROUTINGS
463 WHERE routing_sequence_id = routing_seq_id;
464 ELSE
465 UPDATE BOM_OPERATIONAL_ROUTINGS
466 SET last_update_date = SYSDATE,
467 last_updated_by = user_id,
468 last_update_login = login,
469 pending_from_ecn = null
470 WHERE routing_sequence_id = routing_seq_id
471 AND pending_from_ecn = change_order;
472 END IF;
473
474
475 /* DELETE FROM BOM_OPERATIONAL_ROUTINGS bor1
476 WHERE bor1.routing_sequence_id = routing_seq_id
477 AND bor1.pending_from_ecn = change_order
478 AND NOT EXISTS (SELECT NULL
479 FROM BOM_OPERATION_SEQUENCES bos
480 WHERE bos.ROUTING_SEQUENCE_ID = bor1.ROUTING_SEQUENCE_ID
481 AND (bos.CHANGE_NOTICE IS NULL
482 OR bos.CHANGE_NOTICE <> change_order)
483 )
484 AND ((bor1.ALTERNATE_ROUTING_DESIGNATOR IS NULL
485 AND NOT EXISTS (SELECT NULL
486 FROM BOM_OPERATIONAL_ROUTINGS bor2
487 WHERE bor2.ORGANIZATION_ID = bor1.ORGANIZATION_ID
488 AND bor2.ASSEMBLY_ITEM_ID = bor1.ASSEMBLY_ITEM_ID
489 AND bor2.ALTERNATE_ROUTING_DESIGNATOR IS NOT NULL))
490 OR
491 (bor1.ALTERNATE_ROUTING_DESIGNATOR IS NOT NULL
492 AND NOT EXISTS (SELECT NULL
493 FROM ENG_REVISED_ITEMS eri
494 WHERE eri.ORGANIZATION_ID = bor1.ORGANIZATION_ID
495 AND eri.ROUTING_SEQUENCE_ID = bor1.ROUTING_SEQUENCE_ID
496 AND eri.CHANGE_NOTICE <> change_order)));*/
497
498
499
500 -- If routing was deleted, then unset the routing_sequence_id on the revised item
501
502 --if (SQL%ROWCOUNT > 0) then
503 IF (l_del_rtg_header = 1) THEN -- Bug 3668603
504 UPDATE ENG_REVISED_ITEMS eri
505 SET routing_sequence_id = ''
506 , cancel_comments = cancel_comments
507 , cancellation_date = SYSDATE
508 WHERE eri.REVISED_ITEM_SEQUENCE_ID = rev_item_seq;
509 end if;
510 -- Added by MK on 09/01/2000
511
512 -- End Changes for Bug 3668603
513
514
515
516
517 EXCEPTION
518 WHEN NO_DATA_FOUND THEN
519 NULL;
520 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
521 WHEN OTHERS THEN
522 l_err_text := G_PKG_NAME || ' :(Cancel_Revised_Item)-Revised Item '
523 || substrb(SQLERRM,1,200);
524 Error_Handler.Add_Error_Token
525 ( p_Message_Name => NULL
526 , p_Message_Text => l_Err_Text
527 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
528 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
529 );
530 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
531 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
532
533 END Cancel_Revised_Item;
534
535
536 -- Insert a record into the scheduled dates history table
537 -- when a revised item is rescheduled.
538
539 PROCEDURE Insert_Current_Scheduled_Dates (x_change_notice VARCHAR2,
540 x_organization_id NUMBER,
541 x_revised_item_id NUMBER,
542 x_scheduled_date DATE,
543 x_revised_item_sequence_id NUMBER,
544 x_requestor_id NUMBER,
545 x_userid NUMBER,
546 x_original_system_reference VARCHAR2,
547 x_comments VARCHAR2) -- Bug 3589974
548 IS
549 x_schedule_id NUMBER;
550 BEGIN
551 select ENG_CURRENT_SCHEDULED_DATES_S.nextval
552 into x_schedule_id
553 from sys.dual;
554 insert into ENG_CURRENT_SCHEDULED_DATES (
555 change_notice,
556 organization_id,
557 revised_item_id,
558 scheduled_date,
559 last_update_date,
560 last_updated_by,
561 schedule_id,
562 creation_date,
563 created_by,
564 last_update_login,
565 employee_id,
566 revised_item_sequence_id,
567 original_system_reference,
568 comments -- Bug 3589974
569 )
570 values (x_change_notice,
571 x_organization_id,
572 x_revised_item_id,
573 x_scheduled_date,
574 sysdate,
575 x_userid,
576 x_schedule_id,
577 sysdate,
578 x_userid,
579 x_userid,
580 x_requestor_id,
581 x_revised_item_sequence_id,
582 x_original_system_reference,
583 x_comments -- Bug 3589974
584 );
585 END Insert_Current_Scheduled_Dates;
586
587 PROCEDURE Update_Component_Unit_Number
588 ( p_new_from_end_item_number VARCHAR2
589 , p_revised_item_sequence_id NUMBER
590 )
591 IS
592 BEGIN
593 UPDATE bom_inventory_components
594 SET from_end_item_unit_number = p_new_from_end_item_number
595 WHERE revised_item_sequence_id = p_revised_item_sequence_id
596 AND implementation_date IS NOT NULL;
597 END Update_Component_Unit_Number;
598
599
600
601 /*****************************************************************************
602 * Procedure : Update_Rev_Operations
603 * Parameters IN : Revised item sequence Id
604 * Routing Sequence Id
605 * Scheduled Date(Effectivity Date)
606 * Change notice
607 * Purpose : Procedure will perform the update of effectivity date
608 * and disable date in revised operations when user trying
609 * to reschedule parent revised item.
610 * History : 11/13/2000 MK Added in ECO for Routing.
611 ******************************************************************************/
612
613 PROCEDURE Update_Rev_Operations (x_change_notice VARCHAR2,
614 x_routing_sequence_id NUMBER,
615 x_revised_item_sequence_id NUMBER,
616 x_scheduled_date DATE,
617 x_from_end_item_unit_number VARCHAR2 DEFAULT NULL)
618 IS
619 BEGIN
620 UPDATE BOM_OPERATION_SEQUENCES
621 SET effectivity_date = x_scheduled_date
622 -- , from_end_item_unit_number = x_from_end_item_unit_number
623 WHERE implementation_date IS NULL
624 AND change_notice = x_change_notice
625 AND revised_item_sequence_id = x_revised_item_sequence_id
626 AND routing_sequence_id = x_routing_sequence_id ;
627
628 UPDATE BOM_OPERATION_SEQUENCES
629 SET disable_date = x_scheduled_date
630 WHERE implementation_date IS NULL
631 AND acd_type = 3
632 AND change_notice = x_change_notice
633 AND revised_item_sequence_id = x_revised_item_sequence_id
634 AND routing_sequence_id = x_routing_sequence_id ;
635
636 END Update_Rev_Operations ;
637
638
639
640 /*********************************************************************
641 * Procedure : Updating new_item_revision_id and new_lifecycle_state_id
642 * Parameters IN :Revised_item_sequence_id
643 Revised_item_id
644 organization_id
645 new_item_revision
646 new_lifecycle_phase_name
647 ********************************************************************* */
648 PROCEDURE Update_New_Rev_Lifecycle(
649 p_revised_item_seq_id IN NUMBER
650 , p_revised_item_id IN NUMBER
651 , p_org_id IN NUMBER
652 ,p_lifecycle_name IN VARCHAR2
653 ,p_new_item_revision IN VARCHAR2
654 ,p_change_notice IN VARCHAR2
655 , x_Return_Status OUT NOCOPY VARCHAR2
656 )
657
658 is
659 l_new_life_cycle_state_id NUMBER;
660 l_new_item_rev_id NUMBER;
661 l_err_text VARCHAR2(2000) := NULL;
662 l_fetch_lifecycle NUMBER := 0;
663 BEGIN
664
665 l_new_item_rev_id := ENG_Val_To_Id.Revised_Item_Code (
666 p_revised_item_num => p_revised_item_id,
667 p_organization_id => p_org_id,
668 p_revison_code => p_new_item_revision );
669 --
670 -- Bug 3311072: Added check if lifecycle name is not null and if plm or erp record
671 -- Modified by LKASTURI
672 IF (p_lifecycle_name IS NOT NULL)
673 THEN
674 BEGIN
675
676 SELECT 1
677 INTO l_fetch_lifecycle
678 FROM eng_engineering_changes
679 WHERE nvl(plm_or_erp_change , 'PLM') = 'PLM'
680 AND change_notice = p_change_notice
681 AND organization_id = p_org_id;
682
683 EXCEPTION
684 WHEN NO_DATA_FOUND THEN
685 l_new_life_cycle_state_id := null;
686 END;
687
688 IF (l_fetch_lifecycle = 1)
689 THEN
690
691 l_new_life_cycle_state_id :=ENG_Val_To_Id.Lifecycle_id (
692 p_lifecycle_name => p_lifecycle_name,
693 p_inventory_item_id => p_revised_item_id,
694 p_org_id => p_org_id,
695 x_err_text => l_err_text);
696 END IF;
697 END IF;
698
699 UPDATE ENG_REVISED_ITEMS
700 SET new_item_revision_id = l_new_item_rev_id,
701 new_lifecycle_state_id = l_new_life_cycle_state_id
702 WHERE REVISED_ITEM_SEQUENCE_ID = p_revised_item_seq_id;
703
704 x_return_status := FND_API.G_RET_STS_SUCCESS;
705
706 EXCEPTION
707 WHEN NO_DATA_FOUND THEN
708 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
709
710 WHEN OTHERS THEN
711 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
712
713 end Update_New_Rev_Lifecycle;
714
715
716
717 /*****************************************************************************
718 * Procedure : Update_Row
719 * Parameters IN : Revised item exposed column record.
720 * Revised item unexposed column record
721 * Parameters OUT: Mesg Token Table
722 * Return Status
723 * Purpose : Update row procedure will update the revised item record. It
724 * will check if the user has tried to update the schedule date
725 * and if the date has been updated then it will update the
726 * dates on it revision and will also update the dates on all
727 * revised components on that revised item. If the user has
728 * updated the use up plan name or the item, then a new schedule
729 * must be fetched and updated in all the corresponding entities.
730 ******************************************************************************/
731 PROCEDURE Update_Row
732 ( p_revised_item_rec IN ENG_Eco_PUB.Revised_Item_Rec_Type
733 , p_rev_item_unexp_rec IN Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type
734 , p_control_rec IN BOM_BO_Pub.Control_Rec_Type
735 := BOM_BO_PUB.G_DEFAULT_CONTROL_REC
736 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
737 , x_Return_Status OUT NOCOPY VARCHAR2
738 )
739 IS
740 l_err_text VARCHAR2(2000);
741 l_stmt_num NUMBER := 0;
742 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
743 l_user_id NUMBER;
744 l_login_id NUMBER;
745 l_prog_appid NUMBER;
746 l_prog_id NUMBER;
747 l_request_id NUMBER;
748 req_id NUMBER;
749 l_language_code VARCHAR2(3);
750 l_revision_id NUMBER;
751
752 BEGIN
753
754 l_user_id := Eng_Globals.Get_User_Id;
755 l_login_id := Eng_Globals.Get_Login_Id;
756 l_request_id := ENG_GLOBALS.Get_request_id;
757 l_prog_appid := ENG_GLOBALS.Get_prog_appid;
758 l_prog_id := ENG_GLOBALS.Get_prog_id;
759
760
761
762 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Updating revised item . . . seq id : ' ||
763 to_char(p_rev_item_unexp_rec.revised_item_sequence_id));
764 END IF;
765
766 IF (p_control_rec.caller_type = 'FORM' AND
767 p_control_rec.validation_controller = 'MAIN_EFFECTS')
768 OR
769 p_control_rec.caller_type <> 'FORM'
770 THEN
771 BEGIN
772 l_stmt_num := 1;
773 UPDATE ENG_REVISED_ITEMS
774 SET CHANGE_NOTICE = p_revised_item_rec.eco_name
775 , ORGANIZATION_ID = p_rev_item_unexp_rec.organization_id
776 , REVISED_ITEM_ID = p_rev_item_unexp_rec.revised_item_id
777 , LAST_UPDATE_DATE = SYSDATE
778 , LAST_UPDATED_BY = l_User_Id
779 , LAST_UPDATE_LOGIN = l_Login_Id
780 , IMPLEMENTATION_DATE =
781 DECODE( p_rev_item_unexp_rec.implementation_date,
782 FND_API.G_MISS_DATE,
783 to_date(NULL),
784 p_rev_item_unexp_rec.implementation_date
785 )
786 , CANCELLATION_DATE =
787 DECODE(p_rev_item_unexp_rec.cancellation_date,
788 FND_API.G_MISS_DATE,
789 to_date(NULL),
790 p_rev_item_unexp_rec.cancellation_date
791 )
792 , CANCEL_COMMENTS = p_revised_item_rec.cancel_comments
793 , DISPOSITION_TYPE = p_revised_item_rec.disposition_type
794
795 , NEW_ITEM_REVISION = --p_revised_item_rec.updated_revised_item_revision -- Added by MK
796 -- Comment Out by MK on 10/24/00 --Bug 2953132
797 DECODE(p_revised_item_rec.updated_revised_item_revision,
798 NULL,
799 p_revised_item_rec.new_revised_item_revision,
800 p_revised_item_rec.updated_revised_item_revision
801 )
802 , EARLY_SCHEDULE_DATE =
803 DECODE(p_revised_item_rec.earliest_effective_date,
804 FND_API.G_MISS_DATE,
805 to_date(NULL),
806 p_revised_item_rec.earliest_effective_date
807 )
808 , ATTRIBUTE_CATEGORY = p_revised_item_rec.attribute_category
809 , ATTRIBUTE2 = p_revised_item_rec.attribute2
810 , ATTRIBUTE3 = p_revised_item_rec.attribute3
811 , ATTRIBUTE4 = p_revised_item_rec.attribute4
812 , ATTRIBUTE5 = p_revised_item_rec.attribute5
813 , ATTRIBUTE7 = p_revised_item_rec.attribute7
814 , ATTRIBUTE8 = p_revised_item_rec.attribute8
815 , ATTRIBUTE9 = p_revised_item_rec.attribute9
816 , ATTRIBUTE11 = p_revised_item_rec.attribute11
817 , ATTRIBUTE12 = p_revised_item_rec.attribute12
818 , ATTRIBUTE13 = p_revised_item_rec.attribute13
819 , ATTRIBUTE14 = p_revised_item_rec.attribute14
820 , ATTRIBUTE15 = p_revised_item_rec.attribute15
821 , STATUS_TYPE = p_revised_item_rec.status_type
822 , SCHEDULED_DATE =
823 DECODE(p_revised_item_rec.new_effective_date, to_date(NULL),
824 p_revised_item_rec.start_effective_date,
825 p_revised_item_rec.new_effective_date
826 )
827 , BILL_SEQUENCE_ID = p_rev_item_unexp_rec.bill_sequence_id
828 , MRP_ACTIVE = p_revised_item_rec.mrp_active
829 , PROGRAM_ID = l_Prog_Id
830 , PROGRAM_UPDATE_DATE = SYSDATE
831 , UPDATE_WIP = p_revised_item_rec.update_wip
832 , USE_UP = p_rev_item_unexp_rec.use_up
833 , USE_UP_ITEM_ID = p_rev_item_unexp_rec.use_up_item_id
834 , REVISED_ITEM_SEQUENCE_ID=p_rev_item_unexp_rec.revised_item_sequence_id
835 , USE_UP_PLAN_NAME = p_revised_item_rec.use_up_plan_name
836 , DESCRIPTIVE_TEXT = p_revised_item_rec.change_description
837 , AUTO_IMPLEMENT_DATE = trunc(p_rev_item_unexp_rec.auto_implement_date)
838 , FROM_END_ITEM_UNIT_NUMBER= p_revised_item_rec.from_end_item_unit_number
839 , ATTRIBUTE1 = p_revised_item_rec.attribute1
840 , ATTRIBUTE6 = p_revised_item_rec.attribute6
841 , ATTRIBUTE10 = p_revised_item_rec.attribute10
842 , Original_System_Reference =
843 p_revised_item_rec.original_system_reference
844 -- Added by MK on 08/26/2000 ECO for Routing
845 , FROM_WIP_ENTITY_ID = p_rev_item_unexp_rec.from_wip_entity_id
846 , TO_WIP_ENTITY_ID = p_rev_item_unexp_rec.to_wip_entity_id
847 , FROM_CUM_QTY = p_revised_item_rec.from_cumulative_quantity
848 , LOT_NUMBER = p_revised_item_rec.lot_number
849 , CFM_ROUTING_FLAG = p_rev_item_unexp_rec.cfm_routing_flag
850 , COMPLETION_SUBINVENTORY = p_revised_item_rec.completion_subinventory
851 , COMPLETION_LOCATOR_ID = p_rev_item_unexp_rec.completion_locator_id
852 -- , MIXED_MODEL_MAP_FLAG = p_rev_item_unexp_rec.mixed_model_map_flag
853 , PRIORITY = p_revised_item_rec.priority
854 , CTP_FLAG = p_revised_item_rec.ctp_flag
855 , ROUTING_SEQUENCE_ID = p_rev_item_unexp_rec.routing_sequence_id
856 , NEW_ROUTING_REVISION = p_revised_item_rec.updated_routing_revision -- Added by MK
857 -- Comment out by MK on 10/24/00
858 -- DECODE(p_revised_item_rec.updated_routing_revision ,
859 -- NULL,
860 -- p_revised_item_rec.new_routing_revision,
861 -- p_revised_item_rec.updated_routing_revision
862 -- )
863 , ROUTING_COMMENT = p_revised_item_rec.routing_comment
864 , ECO_FOR_PRODUCTION = p_revised_item_rec.eco_for_production -- Added by MK on 10/06/00
865 , CHANGE_ID = p_rev_item_unexp_rec.change_id --Added on 12/12/02
866 , Transfer_Or_Copy = p_revised_item_rec.Transfer_Or_Copy
867 , Transfer_OR_Copy_Item = p_revised_item_rec.Transfer_OR_Copy_Item
868 , Transfer_OR_Copy_Bill = p_revised_item_rec.Transfer_OR_Copy_Bill
869 , Transfer_OR_Copy_Routing = p_revised_item_rec.Transfer_OR_Copy_Routing
870 , Copy_To_Item = p_revised_item_rec.Copy_To_Item
871 , Copy_To_Item_Desc = p_revised_item_rec.Copy_To_Item_Desc
872 , selection_option= p_revised_item_rec.selection_option
873 , selection_date = p_revised_item_rec.selection_date
874 , selection_unit_number= p_revised_item_rec.selection_unit_number
875 , STATUS_code = nvl(p_rev_item_unexp_rec.status_code, p_revised_item_rec.status_type) -- Bug 3424007
876 WHERE REVISED_ITEM_SEQUENCE_ID = p_rev_item_unexp_rec.revised_item_sequence_id
877 ;
878
879 x_return_status := FND_API.G_RET_STS_SUCCESS;
880
881
882 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Updating revised item is completed'); END IF;
883
884 EXCEPTION
885 WHEN NO_DATA_FOUND THEN
886 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('no data stmt_num '|| l_stmt_num); END IF;
887 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
888 THEN
889 Error_Handler.Add_Error_Token
890 ( p_Message_Name => 'ENG_REV_ITEM_REC_DELETED'
891 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
892 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
893 );
894 END IF;
895 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
896
897 WHEN OTHERS THEN
898 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('un exp stmt_num '|| l_stmt_num); END IF;
899 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
900 THEN
901 l_err_text := G_PKG_NAME || ' : Utility (Revised Item Update) '
902 || SUBSTR(SQLERRM, 1, 200);
903 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug(l_err_text); END IF;
904 IF FND_MSG_PUB.Check_Msg_Level
905 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
906 THEN
907 Error_Handler.Add_Error_Token
908 ( p_Message_Name => NULL
909 , p_Message_Text => l_Err_Text
910 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
911 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
912 );
913 END IF;
914 END IF;
915 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
916 END;
917 END IF; -- if call is from form, and side effects processing not requested.
918
919 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Validation controller is '||p_control_rec.validation_controller); END IF;
920
921 -- If call if from form, execute this block of code only if side effects
922 -- processing has been requested
923 -- By AS on 10/13/99
924 IF (p_control_rec.caller_type = 'FORM' AND
925 p_control_rec.validation_controller = 'SIDE_EFFECTS')
926 OR
927 p_control_rec.caller_type <> 'FORM'
928 THEN
929
930 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Performing SIDE_EFFECTS'); END IF;
931
932 /*********************************************************************
933 --
934 -- If the user has updated the status to 5 i.e. cancel, then
935 -- Call the cancel_revised_item procedure that will do all
936 -- that needs to be done for cancellation
937 --
938 **********************************************************************/
939 IF p_revised_item_rec.status_type = 5
940 THEN
941 -- Mark revised item as 'Cancelled' and process children accordingly
942
943 l_stmt_num := 2;
944 Cancel_Revised_Item
945 ( rev_item_seq => p_rev_item_unexp_rec.revised_item_sequence_id
946 , bill_seq_id => p_rev_item_unexp_rec.bill_sequence_id
947 , routing_seq_id => p_rev_item_unexp_rec.routing_sequence_id
948 , user_id => l_User_ID
949 , login => l_Login_ID
950 , change_order => p_revised_item_rec.eco_name
951 , cancel_comments =>p_revised_item_rec.cancel_comments
952 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
953 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
954 , x_return_status => x_return_status
955 );
956
957 IF p_control_rec.caller_type <> 'FORM'
958 THEN
959 Cancel_ECO
960 ( p_organization_id => p_rev_item_unexp_rec.organization_id
961 , p_change_notice => p_revised_item_rec.eco_name
962 , p_user_id => l_User_ID
963 , p_login => l_Login_ID
964 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
965 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
966 , x_return_status => x_return_status
967 );
968 END IF;
969
970 /************************************************************
971 --
972 -- irrespective of the return type from the procedures, if the
973 -- user has tried to cancel a revised item, then procedure
974 -- should not do any further processing, b'coz if the revised
975 -- item cancellation succeeds then, the revised item should not
976 -- be operated on and if the procedure to cancel the revised
977 -- item failed then there is some unexpected error.
978 --
979 **************************************************************/
980 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
981 END IF;
982
983 /* Start of new block to check if new revision needs to be created or
984 if any existing need to be deleted ro modified.
985 */
986
987 BEGIN
988 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Action on Item Revision is :'||to_char(Eng_Default_Revised_Item.G_DEL_UPD_INS_ITEM_REV) ); END IF;
989 IF Eng_Default_Revised_Item.G_DEL_UPD_INS_ITEM_REV = 1
990 THEN
991 -- Delete record from MTL_ITEM_REVISIONS if it already
992 -- exists
993 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Deleting Item Revisions . . .'); END IF;
994
995 l_stmt_num := 3;
996 ENG_REVISED_ITEMS_PKG.Delete_Item_Revisions
997 ( x_change_notice =>
998 p_revised_item_rec.eco_name
999 , x_organization_id =>
1000 p_rev_item_unexp_rec.organization_id
1001 , x_inventory_item_id =>
1002 p_rev_item_unexp_rec.revised_item_id
1003 , x_revised_item_sequence_id =>
1004 p_rev_item_unexp_rec.revised_item_sequence_id
1005 );
1006 ELSIF Eng_Default_Revised_Item.G_DEL_UPD_INS_ITEM_REV = 2
1007 THEN
1008 -- Update new item revision information in
1009 -- MTL_ITEM_REVISIONS
1010
1011 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Updating Item Revisions . . .');
1012 END IF;
1013
1014 /*
1015 IF Bom_Globals.Get_Debug = 'Y' THEN
1016 Error_Handler.Write_Debug(p_revised_item_rec.updated_revised_item_revision
1017 || p_revised_item_rec.start_effective_date
1018 ||p_revised_item_rec.new_effective_date
1019 || p_revised_item_rec.eco_name
1020 || p_rev_item_unexp_rec.organization_id
1021 || p_rev_item_unexp_rec.revised_item_id
1022 || p_rev_item_unexp_rec.revised_item_sequence_id);
1023 END IF;
1024 */
1025
1026
1027 l_stmt_num := 4;
1028
1029 /* ENG_REVISED_ITEMS_PKG.Update_Item_Revisions
1030 ( x_revision =>
1031 p_revised_item_rec.updated_revised_item_revision
1032 -- , x_scheduled_date => p_revised_item_rec.new_effective_date
1033 , x_scheduled_date =>
1034 DECODE(
1035 DECODE(p_revised_item_rec.new_effective_date,
1036 to_date(NULL), p_revised_item_rec.start_effective_date,
1037 p_revised_item_rec.new_effective_date),
1038 TRUNC(SYSDATE), SYSDATE,
1039 DECODE(p_revised_item_rec.new_effective_date,
1040 to_date(NULL), p_revised_item_rec.start_effective_date,
1041 p_revised_item_rec.new_effective_date)
1042 )
1043 , x_change_notice =>
1044 p_revised_item_rec.eco_name
1045 , x_organization_id =>
1046 p_rev_item_unexp_rec.organization_id
1047 , x_inventory_item_id =>
1048 p_rev_item_unexp_rec.revised_item_id
1049 , x_revised_item_sequence_id =>
1050 p_rev_item_unexp_rec.revised_item_sequence_id
1051 );
1052 */
1053
1054
1055 UPDATE MTL_ITEM_REVISIONS_B
1056 SET revision =
1057 DECODE( p_revised_item_rec.updated_revised_item_revision
1058 , FND_API.G_MISS_CHAR
1059 , p_revised_item_rec.new_revised_item_revision
1060 , NULL
1061 , p_revised_item_rec.new_revised_item_revision
1062 , p_revised_item_rec.updated_revised_item_revision
1063 )
1064 --Bug No:3612330 added by sseraphi to update the rev label also with rev code.
1065 , revision_label =
1066 DECODE( p_revised_item_rec.updated_revised_item_revision
1067 , FND_API.G_MISS_CHAR
1068 , p_revised_item_rec.new_revised_item_revision
1069 , NULL
1070 , p_revised_item_rec.new_revised_item_revision
1071 , p_revised_item_rec.updated_revised_item_revision
1072 )
1073 , effectivity_date =
1074 DECODE( DECODE( p_revised_item_rec.new_effective_date
1075 , to_date(NULL)
1076 , p_revised_item_rec.start_effective_date
1077 , p_revised_item_rec.new_effective_date
1078 ),
1079 TRUNC(SYSDATE), SYSDATE,
1080 DECODE( p_revised_item_rec.new_effective_date
1081 , NULL
1082 , p_revised_item_rec.start_effective_date
1083 , p_revised_item_rec.new_effective_date
1084 )
1085 )
1086 , description = Decode(p_revised_item_rec.new_revised_item_rev_desc,
1087 FND_API.G_MISS_CHAR,
1088 description,
1089 p_revised_item_rec.new_revised_item_rev_desc)
1090 , last_update_date = SYSDATE
1091 , last_update_login = l_login_id
1092 , last_updated_by = l_user_id
1093
1094 WHERE change_notice = p_revised_item_rec.eco_name
1095 AND organization_id = p_rev_item_unexp_rec.organization_id
1096 AND inventory_item_id = p_rev_item_unexp_rec.revised_item_id
1097 AND revised_item_sequence_id =
1098 p_rev_item_unexp_rec.revised_item_sequence_id
1099 AND revision = nvl( p_revised_item_rec.new_revised_item_revision,'NULL')
1100 RETURNING revision_id INTO l_revision_id;
1101
1102
1103 SELECT userenv('LANG') INTO l_language_code FROM dual;
1104 update MTL_ITEM_REVISIONS_TL
1105 set
1106 last_update_date = SYSDATE, --who column
1107 last_update_login = l_login_id, --who column
1108 last_updated_by = l_user_id, --who column
1109 /* Item revision description support Bug: 1667419*/
1110 description = Decode(p_revised_item_rec.new_revised_item_rev_desc,
1111 FND_API.G_MISS_CHAR,
1112 description,
1113 p_revised_item_rec.new_revised_item_rev_desc),
1114 source_lang = l_language_code
1115 where revision_id = l_revision_id
1116 AND LANGUAGE = l_language_code;
1117
1118
1119
1120 ELSIF Eng_Default_Revised_Item.G_DEL_UPD_INS_ITEM_REV = 3
1121 THEN
1122 -- Insert new record if revision record doesn't already
1123 -- exist
1124
1125 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Inserting Item Revisions . . .'); END IF;
1126 l_stmt_num := 5;
1127 IF (p_revised_item_rec.new_effective_date is NULL) THEN
1128 ENG_REVISED_ITEMS_PKG.Insert_Item_Revisions
1129 ( x_inventory_item_id =>
1130 p_rev_item_unexp_rec.revised_item_id
1131 , x_organization_id =>
1132 p_rev_item_unexp_rec.organization_id
1133 , x_revision =>
1134 p_revised_item_rec.updated_revised_item_revision
1135 , x_userid =>
1136 l_User_Id
1137 , x_change_notice =>
1138 p_revised_item_rec.eco_name
1139 , x_scheduled_date => p_revised_item_rec.start_effective_date
1140 , x_revised_item_sequence_id =>
1141 p_rev_item_unexp_rec.revised_item_sequence_id
1142 /* Item revision description support Bug: 1667419*/
1143 , x_revision_description =>
1144 p_revised_item_rec.new_revised_item_rev_desc
1145 );
1146 ELSE
1147 ENG_REVISED_ITEMS_PKG.Insert_Item_Revisions
1148 ( x_inventory_item_id =>
1149 p_rev_item_unexp_rec.revised_item_id
1150 , x_organization_id =>
1151 p_rev_item_unexp_rec.organization_id
1152 , x_revision =>
1153 p_revised_item_rec.updated_revised_item_revision
1154 , x_userid =>
1155 l_User_Id
1156 , x_change_notice =>
1157 p_revised_item_rec.eco_name
1158 , x_scheduled_date => p_revised_item_rec.new_effective_date
1159 , x_revised_item_sequence_id =>
1160 p_rev_item_unexp_rec.revised_item_sequence_id
1161 /* Item revision description support Bug: 1667419*/
1162 , x_revision_description =>
1163 p_revised_item_rec.new_revised_item_rev_desc
1164 );
1165 END IF ;
1166 /* Item revision description support Bug: 1667419*/
1167 ELSIF Eng_Default_Revised_Item.G_DEL_UPD_INS_ITEM_REV = 0
1168 THEN
1169
1170 UPDATE MTL_ITEM_REVISIONS_B
1171 SET
1172 description = Decode(p_revised_item_rec.new_revised_item_rev_desc,
1173 FND_API.G_MISS_CHAR,
1174 description,
1175 p_revised_item_rec.new_revised_item_rev_desc)
1176 /* Bug no :2905537
1177 Revised item effectivity date updation doesnt update in item revisions table
1178 adding effectivity_date to the update statement */
1179
1180 , effectivity_date =
1181 DECODE( DECODE( p_revised_item_rec.new_effective_date
1182 , to_date(NULL)
1183 , p_revised_item_rec.start_effective_date
1184 , p_revised_item_rec.new_effective_date
1185 ),
1186 TRUNC(SYSDATE), SYSDATE,
1187 DECODE( p_revised_item_rec.new_effective_date
1188 , to_date(NULL)
1189 , p_revised_item_rec.start_effective_date
1190 , p_revised_item_rec.new_effective_date
1191 )
1192 )
1193 /* End of bug 2905537 */
1194 , last_update_date = SYSDATE
1195 , last_update_login = l_login_id
1196 , last_updated_by = l_user_id
1197 WHERE change_notice = p_revised_item_rec.eco_name
1198 AND organization_id = p_rev_item_unexp_rec.organization_id
1199 AND inventory_item_id = p_rev_item_unexp_rec.revised_item_id
1200 AND revised_item_sequence_id =
1201 p_rev_item_unexp_rec.revised_item_sequence_id
1202 AND revision = nvl( p_revised_item_rec.new_revised_item_revision,'NULL')
1203 RETURNING revision_id INTO l_revision_id;
1204
1205 SELECT userenv('LANG') INTO l_language_code FROM dual;
1206 update MTL_ITEM_REVISIONS_TL
1207 set
1208 last_update_date = SYSDATE, --who column
1209 last_update_login = l_login_id, --who column
1210 last_updated_by = l_user_id, --who column
1211 description = Decode(p_revised_item_rec.new_revised_item_rev_desc,
1212 FND_API.G_MISS_CHAR,
1213 description,
1214 p_revised_item_rec.new_revised_item_rev_desc),
1215 source_lang = l_language_code
1216 where revision_id = l_revision_id
1217 AND LANGUAGE = l_language_code;
1218
1219
1220 END IF; /* If G_DEL_UPD_INS_ITEM_REV Check Ends */
1221
1222
1223 /****************************************************************
1224 -- Added by MK on 08/26/2000
1225 -- ECO for Routing
1226 ****************************************************************/
1227
1228 IF Eng_Default_Revised_Item.G_DEL_UPD_INS_RTG_REV = 1
1229 THEN
1230 -- Delete record from MTL_RTG_ITEM_REVISIONS if it already
1231 -- exists
1232 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Deleting Routing Revisions . . .'); END IF;
1233
1234 DELETE FROM MTL_RTG_ITEM_REVISIONS
1235 WHERE implementation_date IS NULL
1236 AND change_notice = p_revised_item_rec.eco_name
1237 AND revised_item_sequence_id = p_rev_item_unexp_rec.revised_item_sequence_id
1238 AND organization_id = p_rev_item_unexp_rec.organization_id
1239 AND inventory_item_id = p_rev_item_unexp_rec.revised_item_id ;
1240 -- AND process_revision = p_revised_item_rec.new_routing_revision ;
1241 -- Modified by MK on 02/13/2001 for Bug 1641488
1242
1243 ELSIF Eng_Default_Revised_Item.G_DEL_UPD_INS_RTG_REV = 2
1244 THEN
1245 -- Update new item revision information in
1246 -- MTL_ITEM_REVISIONS
1247
1248 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Updating Routing Revisions . . .'); END IF;
1249
1250 UPDATE MTL_RTG_ITEM_REVISIONS
1251 SET process_revision = p_revised_item_rec.updated_routing_revision
1252 , effectivity_date = DECODE( DECODE(p_revised_item_rec.new_effective_date,
1253 to_date(NULL), p_revised_item_rec.start_effective_date,
1254 p_revised_item_rec.new_effective_date)
1255 , TRUNC(SYSDATE), SYSDATE
1256 , DECODE(p_revised_item_rec.new_effective_date,
1257 to_date(NULL), p_revised_item_rec.start_effective_date,
1258 p_revised_item_rec.new_effective_date)
1259 )
1260 , last_update_date = SYSDATE
1261 , last_updated_by = l_user_id
1262 , last_update_login = l_login_id
1263 WHERE change_notice = p_revised_item_rec.eco_name
1264 AND revised_item_sequence_id = p_rev_item_unexp_rec.revised_item_sequence_id
1265 AND organization_id = p_rev_item_unexp_rec.organization_id
1266 AND inventory_item_id = p_rev_item_unexp_rec.revised_item_id
1267 AND process_revision = nvl(p_revised_item_rec.new_routing_revision, 'NULL') ;
1268
1269
1270
1271 ELSIF Eng_Default_Revised_Item.G_DEL_UPD_INS_RTG_REV = 3
1272 THEN
1273 -- Insert new record if revision record doesn't already
1274 -- exist
1275 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Inserting Routing Revisions . . . '); END IF;
1276
1277 IF ( p_revised_item_rec.new_effective_date is NULL) THEN
1278 Insert_Routing_Revisions
1279 ( p_inventory_item_id => p_rev_item_unexp_rec.revised_item_id
1280 , p_organization_id => p_rev_item_unexp_rec.organization_id
1281 , p_revision => p_revised_item_rec.updated_routing_revision
1282 , p_user_id => l_user_id
1283 , p_login_id => l_login_id
1284 , p_change_notice => p_revised_item_rec.eco_name
1285 , p_effectivity_date => p_revised_item_rec.start_effective_date
1286 , p_revised_item_sequence_id => p_rev_item_unexp_rec.revised_item_sequence_id
1287 );
1288 ELSE
1289 Insert_Routing_Revisions
1290 ( p_inventory_item_id => p_rev_item_unexp_rec.revised_item_id
1291 , p_organization_id => p_rev_item_unexp_rec.organization_id
1292 , p_revision => p_revised_item_rec.updated_routing_revision
1293 , p_user_id => l_user_id
1294 , p_login_id => l_login_id
1295 , p_change_notice => p_revised_item_rec.eco_name
1296 , p_effectivity_date => p_revised_item_rec.new_effective_date
1297 , p_revised_item_sequence_id => p_rev_item_unexp_rec.revised_item_sequence_id
1298 );
1299 END IF ;
1300
1301
1302 END IF;
1303
1304 -- If G_DEL_UPD_INS_RTG_REV Check Ends. Added by MK on 08/26/2000
1305
1306
1307 /************************************************************
1308 --
1309 -- If the user has tried to reschedule a revised item, then
1310 -- this flag is set during the entity defaulting phase.
1311 --
1312 ************************************************************/
1313 IF Eng_Default_Revised_Item.G_SCHED_DATE_CHANGED
1314 THEN
1315 l_stmt_num := 6;
1316
1317 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Updating effective daste for child rev comps . . .'); END IF;
1318
1319 ENG_REVISED_ITEMS_PKG.Update_Inventory_Components
1320 ( x_change_notice =>
1321 p_revised_item_rec.eco_name
1322 , x_bill_sequence_id =>
1323 p_rev_item_unexp_rec.bill_sequence_id
1324 , x_revised_item_sequence_id =>
1325 p_rev_item_unexp_rec.revised_item_sequence_id
1326 , x_scheduled_date =>
1327 -- p_revised_item_rec.start_effective_date
1328 p_revised_item_rec.new_effective_date -- Added by MK on 11/13/00
1329 , x_from_end_item_unit_number =>
1330 p_revised_item_rec.from_end_item_unit_number
1331 );
1332
1333
1334
1335 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Updating effective date for child rev operations . . . '); END IF;
1336
1337 Update_Rev_Operations
1338 ( x_change_notice => p_revised_item_rec.eco_name
1339 , x_routing_sequence_id => p_rev_item_unexp_rec.routing_sequence_id
1340 , x_revised_item_sequence_id => p_rev_item_unexp_rec.revised_item_sequence_id
1341 , x_scheduled_date => p_revised_item_rec.new_effective_date
1342 ) ;
1343
1344
1345 l_stmt_num := 7;
1346 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Inserting current schedule date . . .'); END IF;
1347 if (p_revised_item_rec.status_type = 4) then
1348 if (p_rev_item_unexp_rec.requestor_id is null ) then
1349 -- req_id := l_User_Id;
1350 -- Bug 3589974 : Fetching the party_id for the current user_id
1351 BEGIN
1352 /*SELECT person_id
1353 INTO req_id
1354 FROM eng_security_people_v
1355 WHERE user_id = l_User_Id;*/
1356 -- Commented the above query as eng_security_people_v in engestd.odf
1357 -- will not be available in DMF patchset
1358 /*SELECT party.PARTY_ID
1359 INTO req_id
1360 FROM HZ_PARTIES party, fnd_user fu
1361 WHERE fu.user_id = l_User_Id
1362 AND to_char(fu.employee_id) = party.person_identifier
1363 AND ROWNUM = 1;*/
1364 -- Modified query for performance bug 4240438
1365 SELECT ppf.party_id INTO req_id
1366 FROM per_people_f ppf, fnd_user fu
1367 WHERE fu.user_id = l_User_Id
1368 AND fu.employee_id = ppf.person_id
1369 AND trunc(sysdate) between ppf.effective_start_date and ppf.effective_end_date; -- Fix for 4293553
1370 EXCEPTION
1371 WHEN OTHERS THEN
1372 req_id := NULL;
1373 END;
1374
1375 else
1376 req_id := p_rev_item_unexp_rec.requestor_id;
1377 end if;
1378 Insert_Current_Scheduled_Dates
1379 ( x_change_notice =>
1380 p_revised_item_rec.eco_name
1381 , x_organization_id =>
1382 p_rev_item_unexp_rec.organization_id
1383 , x_revised_item_id =>
1384 p_rev_item_unexp_rec.revised_item_id
1385 , x_scheduled_date =>
1386 p_revised_item_rec.new_effective_date -- p_revised_item_rec.start_effective_date
1387 -- Bug 3589974 : Using the new effectivity date
1388 , x_revised_item_sequence_id =>
1389 p_rev_item_unexp_rec.revised_item_sequence_id
1390 , x_requestor_id =>
1391 req_id
1392 , x_userid =>
1393 l_User_Id
1394 , x_original_system_reference =>
1395 p_revised_item_rec.original_system_reference
1396 , x_comments => p_revised_item_rec.reschedule_comments -- Bug 3589974
1397 );
1398 end if;
1399
1400 IF Bom_Globals.Get_Debug = 'Y' THEN
1401 Error_Handler.Write_Debug('Updating effective dates of pending item/rtg rev in this revised item record . . . ');
1402 END IF;
1403
1404 UPDATE MTL_RTG_ITEM_REVISIONS
1405 SET effectivity_date =
1406 DECODE( DECODE( p_revised_item_rec.new_effective_date
1407 , to_date(NULL)
1408 , p_revised_item_rec.start_effective_date
1409 , p_revised_item_rec.new_effective_date
1410 )
1411 , TRUNC(SYSDATE), SYSDATE
1412 , DECODE( p_revised_item_rec.new_effective_date
1413 , to_date(NULL)
1414 , p_revised_item_rec.start_effective_date
1415 , p_revised_item_rec.new_effective_date
1416 )
1417 )
1418 , last_update_date = SYSDATE
1419 , last_updated_by = l_user_id
1420 , last_update_login = l_login_id
1421 WHERE change_notice = p_revised_item_rec.eco_name
1422 AND organization_id = p_rev_item_unexp_rec.organization_id
1423 AND inventory_item_id = p_rev_item_unexp_rec.revised_item_id
1424 AND revised_item_sequence_id = p_rev_item_unexp_rec.revised_item_sequence_id ;
1425
1426
1427 UPDATE MTL_ITEM_REVISIONS_B
1428 SET effectivity_date =
1429 DECODE( DECODE( p_revised_item_rec.new_effective_date
1430 , to_date(NULL)
1431 , p_revised_item_rec.start_effective_date
1432 , p_revised_item_rec.new_effective_date
1433 ),
1434 TRUNC(SYSDATE), SYSDATE,
1435 DECODE( p_revised_item_rec.new_effective_date
1436 , to_date(NULL)
1437 , p_revised_item_rec.start_effective_date
1438 , p_revised_item_rec.new_effective_date
1439 )
1440 )
1441 , description = Decode(p_revised_item_rec.new_revised_item_rev_desc,
1442 FND_API.G_MISS_CHAR,
1443 description,
1444 p_revised_item_rec.new_revised_item_rev_desc)
1445 , last_update_date = SYSDATE
1446 , last_updated_by = l_user_id
1447 , last_update_login = l_login_id
1448 WHERE change_notice = p_revised_item_rec.eco_name
1449 AND organization_id = p_rev_item_unexp_rec.organization_id
1450 AND inventory_item_id = p_rev_item_unexp_rec.revised_item_id
1451 AND revised_item_sequence_id =
1452 p_rev_item_unexp_rec.revised_item_sequence_id
1453 RETURNING revision_id INTO l_revision_id;
1454
1455 /* Item revision description support Bug: 1667419*/
1456
1457 SELECT userenv('LANG') INTO l_language_code FROM dual;
1458 update MTL_ITEM_REVISIONS_TL
1459 set
1460 last_update_date = SYSDATE, --who column
1461 last_update_login = l_login_id, --who column
1462 last_updated_by = l_user_id, --who column
1463 description = Decode(p_revised_item_rec.new_revised_item_rev_desc,
1464 FND_API.G_MISS_CHAR,
1465 description,
1466 p_revised_item_rec.new_revised_item_rev_desc),
1467 source_lang = l_language_code
1468 where revision_id = l_revision_id
1469 AND LANGUAGE = l_language_code;
1470
1471
1472
1473 END IF; /* Reschedule Ends */
1474
1475 IF p_revised_item_rec.new_from_end_item_unit_number IS NOT NULL
1476 THEN
1477 Update_Component_Unit_Number
1478 ( p_new_from_end_item_number =>
1479 p_revised_item_rec.new_from_end_item_unit_number
1480 , p_revised_item_sequence_id =>
1481 p_rev_item_unexp_rec.revised_item_sequence_id
1482 );
1483 END IF;
1484
1485
1486
1487 /************************************************************
1488 -- If the user has tried to update Eco For Production, then
1489 -- this flag is set during the entity defaulting phase.
1490 -- Added by MK on 24-OCT-00
1491 ************************************************************/
1492 IF Eng_Default_Revised_Item.G_ECO_FOR_PROD_CHANGED
1493 THEN
1494
1495 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Before Updating Eco_For_Production in Rev Comps and Rev Ops'); END IF;
1496
1497 UPDATE BOM_OPERATION_SEQUENCES
1498 SET eco_for_production = p_revised_item_rec.eco_for_production
1499 WHERE revised_item_sequence_id = p_rev_item_unexp_rec.revised_item_sequence_id ;
1500
1501 UPDATE BOM_INVENTORY_COMPONENTS
1502 SET eco_for_production = p_revised_item_rec.eco_for_production
1503 WHERE revised_item_sequence_id = p_rev_item_unexp_rec.revised_item_sequence_id ;
1504
1505 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('After Updating Eco_For_Production in Rev Comps and Rev Ops'); END IF;
1506
1507 END IF; /* Eco For Production */
1508
1509 /* 11.5.10 chnages */
1510 if p_revised_item_rec.New_Revised_Item_Revision is not null
1511 or p_revised_item_rec.new_lifecycle_phase_name is not null then
1512 Update_New_Rev_Lifecycle(
1513 p_revised_item_seq_id => p_rev_item_unexp_rec.revised_item_sequence_id
1514 , p_revised_item_id => p_rev_item_unexp_rec.revised_item_id
1515 , p_org_id => p_rev_item_unexp_rec.organization_id
1516 , p_lifecycle_name => p_revised_item_rec.new_lifecycle_phase_name
1517 , p_new_item_revision => p_revised_item_rec.New_Revised_Item_Revision
1518 , p_change_notice => p_revised_item_rec.eco_name
1519 , x_Return_Status => x_return_status);
1520
1521 end if;
1522 x_return_status := FND_API.G_RET_STS_SUCCESS;
1523 EXCEPTION
1524 WHEN OTHERS THEN
1525 l_err_text := G_PKG_NAME ||
1526 ' : (Updating Record) ' ||
1527 SUBSTRB(SQLERRM,1,200);
1528 Error_Handler.Add_Error_Token
1529 ( p_Message_Name => NULL
1530 , p_Message_Text => l_Err_Text
1531 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1532 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1533 );
1534 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1535 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1536
1537 END; /* Inser/Update/Delete revision and check reschedule block Ends */
1538 END IF;
1539
1540 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('returning from update_row'); END IF;
1541
1542 END Update_Row;
1543
1544 /*****************************************************************************
1545 * Procedure : Insert_Row
1546 * Paramaters IN : Revised item exposed column record
1547 * Revised item unexposed column record
1548 * Parameters OUT: Mesg Token Table
1549 * Return Status
1550 * Purpose : Procedure will insert a new revised item record. It will also
1551 * add any new revision if the user has added a revision that
1552 * does not exist. Also an entry into the table eng_current_
1553 * effective dates is also made for the new item.
1554 *****************************************************************************/
1555 PROCEDURE Insert_Row
1556 ( p_revised_item_rec IN ENG_Eco_PUB.Revised_Item_Rec_Type
1557 , p_rev_item_unexp_rec IN Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type
1558 , p_control_rec IN BOM_BO_Pub.Control_Rec_Type
1559 := BOM_BO_PUB.G_DEFAULT_CONTROL_REC
1560 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1561 , x_Return_Status OUT NOCOPY VARCHAR2
1562 )
1563 IS
1564 l_assembly_type NUMBER := NULL;
1565 l_err_text VARCHAR2(2000) := NULL;
1566 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1567 l_user_id NUMBER;
1568 l_login_id NUMBER;
1569 l_prog_appid NUMBER;
1570 l_prog_id NUMBER;
1571 l_request_id NUMBER;
1572 l_current_item_revision_id NUMBER;
1573 --added for 3972225
1574 CURSOR c_revised_phase_id (cp_revised_item_id NUMBER,cp_organization_id NUMBER) IS
1575 SELECT current_phase_id
1576 FROM mtl_system_items
1577 WHERE inventory_item_id = cp_revised_item_id
1578 and organization_id = cp_organization_id ;
1579
1580 l_current_lifecycle_phase_id NUMBER;
1581 BEGIN
1582
1583 x_return_status := FND_API.G_RET_STS_SUCCESS;
1584
1585 l_user_id := Eng_Globals.Get_User_Id;
1586 l_login_id := Eng_Globals.Get_Login_Id;
1587 l_request_id := ENG_GLOBALS.Get_request_id;
1588 l_prog_appid := ENG_GLOBALS.Get_prog_appid;
1589 l_prog_id := ENG_GLOBALS.Get_prog_id;
1590
1591 -- 11.5.10E
1592 -- populating the current_item_revision_id to from_revision_id
1593 l_current_item_revision_id := p_rev_item_unexp_rec.from_item_revision_id;
1594
1595 IF l_current_item_revision_id is null
1596 THEN
1597 l_current_item_revision_id := p_rev_item_unexp_rec.current_item_revision_id;
1598 END IF;
1599
1600 IF l_current_item_revision_id is null
1601 THEN
1602 l_current_item_revision_id := BOM_REVISIONS.get_item_revision_id_fn(
1603 'ALL',
1604 'IMPL_ONLY',
1605 p_rev_item_unexp_rec.organization_id,
1606 p_rev_item_unexp_rec.revised_item_id,
1607 SYSDATE);
1608 END IF;
1609 --added for 3972225
1610 OPEN c_revised_phase_id(cp_revised_item_id => p_rev_item_unexp_rec.revised_item_id,cp_organization_id => p_rev_item_unexp_rec.organization_id);
1611 FETCH c_revised_phase_id INTO l_current_lifecycle_phase_id;
1612 CLOSE c_revised_phase_id;
1613 IF (p_control_rec.caller_type = 'FORM' AND
1614 p_control_rec.validation_controller = 'MAIN_EFFECTS')
1615 OR
1616 p_control_rec.caller_type <> 'FORM'
1617 THEN
1618 INSERT INTO ENG_REVISED_ITEMS
1619 (
1620 CHANGE_NOTICE
1621 , ORGANIZATION_ID
1622 , REVISED_ITEM_ID
1623 , LAST_UPDATE_DATE
1624 , LAST_UPDATED_BY
1625 , CREATION_DATE
1626 , CREATED_BY
1627 , LAST_UPDATE_LOGIN
1628 , IMPLEMENTATION_DATE
1629 , CANCELLATION_DATE
1630 , CANCEL_COMMENTS
1631 , DISPOSITION_TYPE
1632 , NEW_ITEM_REVISION
1633 , EARLY_SCHEDULE_DATE
1634 , ATTRIBUTE_CATEGORY
1635 , ATTRIBUTE2
1636 , ATTRIBUTE3
1637 , ATTRIBUTE4
1638 , ATTRIBUTE5
1639 , ATTRIBUTE7
1640 , ATTRIBUTE8
1641 , ATTRIBUTE9
1642 , ATTRIBUTE11
1643 , ATTRIBUTE12
1644 , ATTRIBUTE13
1645 , ATTRIBUTE14
1646 , ATTRIBUTE15
1647 , STATUS_TYPE
1648 , SCHEDULED_DATE
1649 , BILL_SEQUENCE_ID
1650 , MRP_ACTIVE
1651 , REQUEST_ID
1652 , PROGRAM_ID
1653 , PROGRAM_UPDATE_DATE
1654 , UPDATE_WIP
1655 , USE_UP
1656 , USE_UP_ITEM_ID
1657 , REVISED_ITEM_SEQUENCE_ID
1658 , USE_UP_PLAN_NAME
1659 , DESCRIPTIVE_TEXT
1660 , AUTO_IMPLEMENT_DATE
1661 , FROM_END_ITEM_UNIT_NUMBER
1662 , ATTRIBUTE1
1663 , ATTRIBUTE6
1664 , ATTRIBUTE10
1665 , Original_System_Reference
1666
1667 /* Added by MK on 08/26/2000 ECO for Routing */
1668 , FROM_WIP_ENTITY_ID
1669 , TO_WIP_ENTITY_ID
1670 , FROM_CUM_QTY
1671 , LOT_NUMBER
1672 , CFM_ROUTING_FLAG
1673 , COMPLETION_SUBINVENTORY
1674 , COMPLETION_LOCATOR_ID
1675 -- , MIXED_MODEL_MAP_FLAG
1676 , PRIORITY
1677 , CTP_FLAG
1678 , ROUTING_SEQUENCE_ID
1679 , NEW_ROUTING_REVISION
1680 , ROUTING_COMMENT
1681 , ECO_FOR_PRODUCTION -- Added by MK on 10/06/00
1682 , CHANGE_ID --Added on 12/12/02
1683 , ALTERNATE_BOM_DESIGNATOR -- Added by Maloy so that ALTERNATE_BOM_DESIGNATOR Get saved and 2871651 works fine
1684 --11.5.10 Changes
1685 ,TRANSFER_OR_COPY
1686 ,TRANSFER_OR_COPY_ITEM
1687 ,TRANSFER_OR_COPY_BILL
1688 ,TRANSFER_OR_COPY_ROUTING
1689 ,COPY_TO_ITEM
1690 ,COPY_TO_ITEM_DESC
1691 ,STATUS_CODE
1692 --end of 11.5.10 changes
1693 ,parent_revised_item_seq_id
1694 ,selection_option
1695 ,selection_date
1696 ,selection_unit_number
1697 ,new_item_revision_id
1698 ,current_item_revision_id
1699 ,current_lifecycle_state_id
1700 ,new_lifecycle_state_id
1701 ,enable_item_in_local_org
1702 ,create_bom_in_local_org )
1703 VALUES
1704 (
1705 p_revised_item_rec.eco_name
1706 , p_rev_item_unexp_rec.organization_id
1707 , p_rev_item_unexp_rec.revised_item_id
1708 , SYSDATE
1709 , l_User_Id
1710 , SYSDATE
1711 , l_User_Id
1712 , l_Login_Id
1713 , DECODE(p_rev_item_unexp_rec.implementation_date,
1714 FND_API.G_MISS_DATE,
1715 to_date(NULL),
1716 p_rev_item_unexp_rec.implementation_date
1717 )
1718 , DECODE(p_rev_item_unexp_rec.cancellation_date,
1719 FND_API.G_MISS_DATE,
1720 to_date(NULL),
1721 p_rev_item_unexp_rec.cancellation_date
1722 )
1723 , p_revised_item_rec.cancel_comments
1724 , p_revised_item_rec.disposition_type
1725 , p_revised_item_rec.new_revised_item_revision
1726 , p_revised_item_rec.earliest_effective_date
1727 , p_revised_item_rec.attribute_category
1728 , p_revised_item_rec.attribute2
1729 , p_revised_item_rec.attribute3
1730 , p_revised_item_rec.attribute4
1731 , p_revised_item_rec.attribute5
1732 , p_revised_item_rec.attribute7
1733 , p_revised_item_rec.attribute8
1734 , p_revised_item_rec.attribute9
1735 , p_revised_item_rec.attribute11
1736 , p_revised_item_rec.attribute12
1737 , p_revised_item_rec.attribute13
1738 , p_revised_item_rec.attribute14
1739 , p_revised_item_rec.attribute15
1740 , p_revised_item_rec.status_type
1741 , p_revised_item_rec.start_effective_date
1742 , DECODE(p_rev_item_unexp_rec.bill_sequence_id, FND_API.G_MISS_NUM,
1743 NULL, p_rev_item_unexp_rec.bill_sequence_id)
1744 , p_revised_item_rec.mrp_active
1745 , NULL /* Request ID */
1746 , l_prog_id
1747 , SYSDATE
1748 , p_revised_item_rec.update_wip
1749 , p_rev_item_unexp_rec.use_up
1750 , DECODE(p_rev_item_unexp_rec.use_up_item_id, FND_API.G_MISS_NUM,
1751 NULL, p_rev_item_unexp_rec.use_up_item_id)
1752 , p_rev_item_unexp_rec.revised_item_sequence_id
1753 , p_revised_item_rec.use_up_plan_name
1754 , p_revised_item_rec.change_description
1755 , trunc(p_rev_item_unexp_rec.auto_implement_date)
1756 , p_revised_item_rec.from_end_item_unit_number
1757 , p_revised_item_rec.attribute1
1758 , p_revised_item_rec.attribute6
1759 , p_revised_item_rec.attribute10
1760 , p_revised_item_rec.original_system_reference
1761
1762 /* Added by MK on 08/26/2000 ECO for Routing */
1763 , p_rev_item_unexp_rec.from_wip_entity_id
1764 , p_rev_item_unexp_rec.to_wip_entity_id
1765 , p_revised_item_rec.from_cumulative_quantity
1766 , p_revised_item_rec.lot_number
1767 , p_rev_item_unexp_rec.cfm_routing_flag
1768 , p_revised_item_rec.completion_subinventory
1769 , p_rev_item_unexp_rec.completion_locator_id
1770 -- , p_rev_item_unexp_rec.mixed_model_map_flag
1771 , p_revised_item_rec.priority
1772 , p_revised_item_rec.ctp_flag
1773 , DECODE(p_rev_item_unexp_rec.routing_sequence_id, FND_API.G_MISS_NUM,
1774 NULL, p_rev_item_unexp_rec.routing_sequence_id )
1775 , p_revised_item_rec.new_routing_revision
1776 , p_revised_item_rec.routing_comment
1777 , p_revised_item_rec.eco_for_production
1778 , p_rev_item_unexp_rec.change_id
1779 , p_revised_item_rec.alternate_bom_code -- Added by Maloy so that ALTERNATE_BOM_DESIGNATOR Get saved and 2871651 works fine
1780 --Start of 11.5.10 changes
1781 , p_revised_item_rec.Transfer_Or_Copy
1782 , p_revised_item_rec.Transfer_OR_Copy_Item
1783 , p_revised_item_rec.Transfer_OR_Copy_Bill
1784 , p_revised_item_rec.Transfer_OR_Copy_Routing
1785 , p_revised_item_rec.Copy_To_Item
1786 , p_revised_item_rec.Copy_To_Item_Desc
1787 , nvl(p_rev_item_unexp_rec.status_code,p_revised_item_rec.status_type)
1788 , p_rev_item_unexp_rec.parent_revised_item_seq_id
1789 , p_revised_item_rec.selection_option
1790 , p_revised_item_rec.selection_date
1791 , p_revised_item_rec.selection_unit_number
1792 , p_rev_item_unexp_rec.new_item_revision_id
1793 , l_current_item_revision_id
1794 -- , p_rev_item_unexp_rec.current_item_revision_id
1795 , nvl(p_rev_item_unexp_rec.current_lifecycle_state_id ,l_current_lifecycle_phase_id)
1796 , p_rev_item_unexp_rec.new_lifecycle_state_id
1797 , p_revised_item_rec.enable_item_in_local_org
1798 , p_revised_item_rec.create_bom_in_local_org --End of 11.5.10 changes
1799 );
1800 END IF;
1801
1802 -- If call if from form, execute this block of code only if side effects
1803 -- processing has been requested
1804 -- By AS on 10/13/99
1805
1806 IF (p_control_rec.caller_type = 'FORM' AND
1807 p_control_rec.validation_controller = 'SIDE_EFFECTS')
1808 OR
1809 p_control_rec.caller_type <> 'FORM'
1810 THEN
1811 IF Eng_Default_Revised_Item.G_DEL_UPD_INS_ITEM_REV = 3 AND
1812 ( p_revised_item_rec.new_revised_item_revision IS NOT NULL OR
1813 p_revised_item_rec.new_revised_item_revision <> FND_API.G_MISS_CHAR
1814 )
1815 THEN
1816 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Inserting item revisions . . . '); END IF;
1817 ENG_REVISED_ITEMS_PKG.Insert_Item_Revisions
1818 ( x_inventory_item_id =>
1819 p_rev_item_unexp_rec.revised_item_id
1820 , x_organization_id =>
1821 p_rev_item_unexp_rec.organization_id
1822 , x_revision =>
1823 p_revised_item_rec.new_revised_item_revision
1824 , x_userid =>
1825 l_User_Id
1826 , x_change_notice =>
1827 p_revised_item_rec.eco_name
1828 , x_scheduled_date =>
1829 p_revised_item_rec.start_effective_date
1830 , x_revised_item_sequence_id =>
1831 p_rev_item_unexp_rec.revised_item_sequence_id
1832 /* Item revision description support Bug: 1667419*/
1833 , x_revision_description =>
1834 p_revised_item_rec.new_revised_item_rev_desc
1835 , p_new_revision_label =>
1836 p_revised_item_rec.new_revision_label
1837 , p_new_revision_reason_code =>
1838 p_rev_item_unexp_rec.new_revision_reason_code
1839 , p_from_revision_id =>
1840 p_rev_item_unexp_rec.from_item_revision_id
1841 );
1842 END IF;
1843
1844 IF Eng_Default_Revised_Item.G_DEL_UPD_INS_RTG_REV = 3 AND
1845 ( p_revised_item_rec.new_routing_revision IS NOT NULL OR
1846 p_revised_item_rec.new_routing_revision <> FND_API.G_MISS_CHAR
1847 )
1848 THEN
1849
1850 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Inserting routing revisions . . .'); END IF;
1851
1852
1853 Insert_Routing_Revisions
1854 ( p_inventory_item_id => p_rev_item_unexp_rec.revised_item_id
1855 , p_organization_id => p_rev_item_unexp_rec.organization_id
1856 , p_revision => p_revised_item_rec.new_routing_revision
1857 , p_user_id => l_user_id
1858 , p_login_id => l_login_id
1859 , p_change_notice => p_revised_item_rec.eco_name
1860 , p_effectivity_date => p_revised_item_rec.start_effective_date
1861 , p_revised_item_sequence_id => p_rev_item_unexp_rec.revised_item_sequence_id
1862 );
1863 END IF ;
1864
1865
1866 IF p_revised_item_rec.start_effective_date IS NOT NULL
1867 THEN
1868 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Inserting cur_sch_dates . . .'); END IF;
1869 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('ECO : ' || p_revised_item_rec.eco_name); END IF;
1870 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Organization: ' ||
1871 to_char(p_rev_item_unexp_rec.organization_id));
1872 END IF;
1873 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Revised Item: ' ||
1874 to_char(p_rev_item_unexp_rec.revised_item_id));
1875 END IF;
1876 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Effective Date: ' ||
1877 to_char(p_revised_item_rec.start_effective_date));
1878 END IF;
1879 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Revised Item Sequence: ' ||
1880 to_char(p_rev_item_unexp_rec.revised_item_sequence_id));
1881 END IF;
1882 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Requestor: ' ||
1883 to_char(p_rev_item_unexp_rec.requestor_id));
1884 END IF;
1885
1886 /*Insert_Current_Scheduled_Dates
1887 ( x_change_notice =>
1888 p_revised_item_rec.eco_name
1889 , x_organization_id =>
1890 p_rev_item_unexp_rec.organization_id
1891 , x_revised_item_id =>
1892 p_rev_item_unexp_rec.revised_item_id
1893 , x_scheduled_date =>
1894 p_revised_item_rec.start_effective_date
1895 , x_revised_item_sequence_id =>
1896 p_rev_item_unexp_rec.revised_item_sequence_id
1897 , x_requestor_id =>
1898 p_rev_item_unexp_rec.requestor_id
1899 , x_userid =>
1900 l_User_Id
1901 , x_original_system_reference =>
1902 p_revised_item_rec.original_system_reference);*/
1903
1904 END IF;
1905
1906 IF Eng_Default_Revised_Item.G_CREATE_ALTERNATE
1907 THEN
1908
1909 l_assembly_type := ENG_Globals.Get_ECO_Assembly_Type
1910 ( p_change_notice =>
1911 p_revised_item_rec.eco_name
1912 , p_organization_id =>
1913 p_rev_item_unexp_rec.organization_id
1914 );
1915
1916 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Creating Altenate BOM . . .'); END IF;
1917
1918 ENG_REVISED_ITEMS_PKG.Create_BOM
1919 ( x_assembly_item_id =>
1920 p_rev_item_unexp_rec.revised_item_id
1921 , x_organization_id =>
1922 p_rev_item_unexp_rec.organization_id
1923 , x_alternate_BOM_designator =>
1924 p_revised_item_rec.alternate_bom_code
1925 , x_userid =>
1926 l_User_Id
1927 , x_change_notice =>
1928 p_revised_item_rec.eco_name
1929 , x_revised_item_sequence_id =>
1930 p_rev_item_unexp_rec.revised_item_sequence_id
1931 , x_bill_sequence_id =>
1932 p_rev_item_unexp_rec.bill_sequence_id
1933 , x_assembly_type =>
1934 l_assembly_type
1935 , x_structure_type_id =>
1936 p_rev_item_unexp_rec.structure_type_id
1937 );
1938
1939 -- Added by MK on 02/15/2001 for Bug#1647352
1940 -- Set Bill Sequence Id to Revised Item table
1941 --
1942 UPDATE ENG_REVISED_ITEMS
1943 SET bill_sequence_id = p_rev_item_unexp_rec.bill_sequence_id
1944 , last_update_date = SYSDATE -- Last Update Date
1945 , last_updated_by = l_user_id -- Last Updated By
1946 , last_update_login = l_login_id -- Last Update Login
1947 WHERE revised_item_sequence_id = p_rev_item_unexp_rec.revised_item_sequence_id ;
1948
1949 IF BOM_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
1950 ('Set created bill sequence id : ' || to_char(p_rev_item_unexp_rec.bill_sequence_id )
1951 || ' to the parenet revised item . . .') ;
1952 END IF ;
1953
1954 END IF;
1955
1956
1957 /******************************************************************
1958 -- Added by MK on 09/01/2000
1959 -- ECO for Routing
1960 -- Create Alternate Routing
1961 ******************************************************************/
1962
1963 IF Eng_Default_Revised_Item.G_CREATE_RTG_ALTERNATE
1964 THEN
1965 IF l_assembly_type IS NULL THEN
1966 l_assembly_type := ENG_Globals.Get_ECO_Assembly_Type
1967 ( p_change_notice => p_revised_item_rec.eco_name
1968 , p_organization_id => p_rev_item_unexp_rec.organization_id
1969 );
1970 END IF ;
1971
1972 IF Bom_Globals.Get_Debug = 'Y' THEN
1973 Error_Handler.Write_Debug('Creating Alternate Routing. . . ');
1974 /*
1975 Error_Handler.Write_Debug('Rtg Sequence Id : '|| to_char(p_rev_item_unexp_rec.routing_sequence_id));
1976 Error_Handler.Write_Debug('Assembly Item : '|| to_char(p_rev_item_unexp_rec.revised_item_id));
1977 Error_Handler.Write_Debug('Org Id: '|| to_char(p_rev_item_unexp_rec.organization_id));
1978 Error_Handler.Write_Debug('Alt Code : '|| p_revised_item_rec.alternate_bom_code );
1979 Error_Handler.Write_Debug('Routing Type : '|| to_char(l_assembly_type));
1980 Error_Handler.Write_Debug('User Id: '|| to_char(l_user_id));
1981 Error_Handler.Write_Debug('Login Id: '|| to_char(l_login_id));
1982 */
1983
1984 END IF;
1985
1986
1987 ENG_Globals.Create_New_Routing
1988 ( p_assembly_item_id => p_rev_item_unexp_rec.revised_item_id
1989 , p_organization_id => p_rev_item_unexp_rec.organization_id
1990 , p_alternate_routing_code => p_revised_item_rec.alternate_bom_code
1991 , p_pending_from_ecn => p_revised_item_rec.eco_name
1992 , p_routing_sequence_id => p_rev_item_unexp_rec.routing_sequence_id
1993 , p_common_routing_sequence_id => p_rev_item_unexp_rec.routing_sequence_id
1994 , p_routing_type => l_assembly_type
1995 , p_last_update_date => SYSDATE
1996 , p_last_updated_by => l_user_id
1997 , p_creation_date => SYSDATE
1998 , p_created_by => l_user_id
1999 , p_login_id => l_login_id
2000 , p_revised_item_sequence_id => p_rev_item_unexp_rec.revised_item_sequence_id
2001 , p_original_system_reference => p_revised_item_rec.original_system_reference
2002 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2003 , x_return_status => x_return_status
2004 ) ;
2005
2006 END IF ;
2007
2008 END IF;
2009
2010
2011 if p_revised_item_rec.New_Revised_Item_Revision is not null
2012 or p_revised_item_rec.new_lifecycle_phase_name is not null then
2013
2014 Update_New_Rev_Lifecycle(
2015 p_revised_item_seq_id => p_rev_item_unexp_rec.revised_item_sequence_id
2016 , p_revised_item_id => p_rev_item_unexp_rec.revised_item_id
2017 , p_org_id => p_rev_item_unexp_rec.organization_id
2018 ,p_lifecycle_name => p_revised_item_rec.new_lifecycle_phase_name
2019 ,p_new_item_revision => p_revised_item_rec.New_Revised_Item_Revision
2020 , p_change_notice => p_revised_item_rec.eco_name
2021 , x_Return_Status => x_return_status);
2022
2023 end if;
2024
2025
2026
2027 EXCEPTION
2028
2029 WHEN OTHERS THEN
2030
2031 l_err_text := G_PKG_NAME || ' : (Inserting Record - Revised Item) '
2032 || substrb(SQLERRM,1,200);
2033 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug(l_err_text); END IF;
2034 Error_Handler.Add_Error_Token
2035 ( p_Message_Name => NULL
2036 , p_Message_Text => l_Err_Text
2037 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2038 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2039 );
2040 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2041 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2042
2043 END Insert_Row;
2044
2045 /*****************************************************************************
2046 * Procedure : Delete_Row
2047 * Parameters IN : Revised item Key
2048 * Parameters OUT: Mesg Token Table
2049 * Return Status
2050 * Purpose : Procedure will perfrom the deletion of revised item and all
2051 * other entities depending on that revised item.
2052 *****************************************************************************/
2053 PROCEDURE Delete_Row
2054 ( p_revised_item_sequence_id IN NUMBER
2055 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2056 , x_return_status OUT NOCOPY VARCHAR2
2057 )
2058 IS
2059 l_organization_id NUMBER := NULL;
2060 l_revised_item_id NUMBER := NULL;
2061 l_revised_item_sequence_id NUMBER := NULL;
2062 l_bill_sequence_id NUMBER := NULL;
2063
2064 l_routing_sequence_id NUMBER := NULL ;
2065 -- Added by MK on 09/01/2000
2066
2067 l_change_notice VARCHAR2(10) := NULL;
2068 l_err_text VARCHAR2(2000) := NULL;
2069 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2070
2071 BEGIN
2072
2073 BEGIN
2074 SELECT change_notice, organization_id, revised_item_id,
2075 revised_item_sequence_id, bill_sequence_id
2076 , routing_sequence_id -- Added by MK
2077
2078 INTO l_change_notice, l_organization_id, l_revised_item_id,
2079 l_revised_item_sequence_id, l_bill_sequence_id
2080 , l_routing_sequence_id -- Added by MK
2081 FROM eng_revised_items
2082 WHERE revised_item_sequence_id = p_revised_item_sequence_id;
2083
2084 DELETE FROM ENG_REVISED_ITEMS
2085 WHERE REVISED_ITEM_SEQUENCE_ID = p_revised_item_sequence_id;
2086
2087 x_return_status := FND_API.G_RET_STS_SUCCESS;
2088
2089 EXCEPTION
2090 WHEN OTHERS THEN
2091 l_err_text := G_PKG_NAME ||
2092 ' : (Deleting Record) - Revised Item'
2093 || substrb(SQLERRM,1,200);
2094 Error_Handler.Add_Error_Token
2095 ( p_Message_Name => NULL
2096 , p_Message_Text => l_Err_Text
2097 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2098 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2099 );
2100 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2101 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
2102 RETURN;
2103 END;
2104
2105 BEGIN
2106 ENG_REVISED_ITEMS_PKG.Delete_Details
2107 ( x_organization_id => l_organization_id
2108 , x_revised_item_id => l_revised_item_id
2109 , x_revised_item_sequence_id => p_revised_item_sequence_id
2110 , x_bill_sequence_id => l_bill_sequence_id
2111 , x_change_notice => l_change_notice);
2112
2113
2114 -- Added by MK on 09/01/2000
2115 IF l_routing_sequence_id IS NOT NULL
2116 THEN
2117 Delete_Routing_Details
2118 ( p_organization_id => l_organization_id
2119 , p_revised_item_id => l_revised_item_id
2120 , p_revised_item_sequence_id => p_revised_item_sequence_id
2121 , p_routing_sequence_id => l_routing_sequence_id
2122 , p_change_notice => l_change_notice) ;
2123 END IF ;
2124
2125
2126 EXCEPTION
2127
2128 WHEN NO_DATA_FOUND THEN
2129 NULL;
2130
2131 WHEN OTHERS THEN
2132 IF FND_MSG_PUB.Check_Msg_Level
2133 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2134 THEN
2135 l_err_text := G_PKG_NAME ||
2136 ' : (Deleting Revised Item Details ' ||
2137 substrb(SQLERRM,1,200);
2138 Error_Handler.Add_Error_Token
2139 ( p_Message_Name => NULL
2140 , p_Message_Text => l_Err_Text
2141 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2142 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2143 );
2144 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2145 END IF;
2146 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2147 END;
2148 END Delete_Row;
2149
2150 /*****************************************************************************
2151 * Procedure : Query_Row
2152 * Parameters IN : Revised item id
2153 * Organization Id
2154 * Change Notice
2155 * New Revised item revision
2156 * Parameters OUT: Revised item exposed column record
2157 * Revised item unexposed column record
2158 * Mesg token Table
2159 * Return Status
2160 * Purpose : Procedure will query the database record, seperate the values
2161 * into exposed columns and unexposed columns are will return
2162 * with those records.
2163 ******************************************************************************/
2164 PROCEDURE Query_Row
2165 ( p_revised_item_id IN NUMBER
2166 , p_organization_id IN NUMBER
2167 , p_change_notice IN VARCHAR2
2168 , p_start_eff_date IN DATE := NULL
2169 , p_new_item_revision IN VARCHAR2
2170 , p_new_routing_revision IN VARCHAR2 -- Added by MK
2171 , p_from_end_item_number IN VARCHAR2 := NULL
2172 , p_alternate_designator IN VARCHAR2 := NULL -- To Fix 2869146
2173 , x_revised_item_rec OUT NOCOPY Eng_Eco_Pub.Revised_Item_Rec_Type
2174 , x_rev_item_unexp_rec OUT NOCOPY Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type
2175 , x_Return_status OUT NOCOPY VARCHAR2
2176 )
2177 IS
2178 l_revised_item_rec ENG_Eco_PUB.Revised_Item_Rec_Type;
2179 l_rev_item_unexp_rec Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type;
2180 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2181 l_err_text VARCHAR2(2000);
2182 BEGIN
2183
2184 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Revised Item: ' || to_char(p_revised_item_id)); END IF;
2185 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Organization: ' || to_char(p_organization_id)); END IF;
2186 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('ChangeNotice: ' || p_change_notice); END IF;
2187 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Revision: ' || p_new_item_revision); END IF;
2188 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Rtg Revision: ' || p_new_routing_revision); END IF;
2189 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Effective Date: ' || to_char(p_start_eff_date));
2190 END IF;
2191
2192 SELECT
2193 CHANGE_NOTICE
2194 , ORGANIZATION_ID
2195 , REVISED_ITEM_ID
2196 , IMPLEMENTATION_DATE
2197 , CANCELLATION_DATE
2198 , CANCEL_COMMENTS
2199 , DISPOSITION_TYPE
2200 , NEW_ITEM_REVISION
2201 , EARLY_SCHEDULE_DATE
2202 , ATTRIBUTE_CATEGORY
2203 , ATTRIBUTE2
2204 , ATTRIBUTE3
2205 , ATTRIBUTE4
2206 , ATTRIBUTE5
2207 , ATTRIBUTE7
2208 , ATTRIBUTE8
2209 , ATTRIBUTE9
2210 , ATTRIBUTE11
2211 , ATTRIBUTE12
2212 , ATTRIBUTE13
2213 , ATTRIBUTE14
2214 , ATTRIBUTE15
2215 , STATUS_TYPE
2216 , SCHEDULED_DATE
2217 , BILL_SEQUENCE_ID
2218 , MRP_ACTIVE
2219 , UPDATE_WIP
2220 , USE_UP
2221 , USE_UP_ITEM_ID
2222 , REVISED_ITEM_SEQUENCE_ID
2223 , USE_UP_PLAN_NAME
2224 , DESCRIPTIVE_TEXT
2225 , AUTO_IMPLEMENT_DATE
2226 , ATTRIBUTE1
2227 , ATTRIBUTE6
2228 , ATTRIBUTE10
2229
2230 -- Added by MK on 09/01/2000 ECO for ROUTINGS
2231 , FROM_WIP_ENTITY_ID
2232 , TO_WIP_ENTITY_ID
2233 , FROM_CUM_QTY
2234 , LOT_NUMBER
2235 , CFM_ROUTING_FLAG
2236 , COMPLETION_SUBINVENTORY
2237 , COMPLETION_LOCATOR_ID
2238 -- , MIXED_MODEL_MAP_FLAG
2239 , PRIORITY
2240 , CTP_FLAG
2241 , ROUTING_SEQUENCE_ID
2242 , NEW_ROUTING_REVISION
2243 , ROUTING_COMMENT -- End of ECO for Routing
2244 , ECO_FOR_PRODUCTION -- Added by MK 10/06/00
2245 , CHANGE_ID
2246 , STATUS_CODE -- Added for bug 3618676
2247 INTO
2248 l_revised_item_rec.eco_name
2249 , l_rev_item_unexp_rec.organization_id
2250 , l_rev_item_unexp_rec.revised_item_id
2251 , l_rev_item_unexp_rec.implementation_date
2252 , l_rev_item_unexp_rec.cancellation_date
2253 , l_revised_item_rec.cancel_comments
2254 , l_revised_item_rec.disposition_type
2255 , l_revised_item_rec.new_revised_item_revision
2256 , l_revised_item_rec.earliest_effective_date
2257 , l_revised_item_rec.attribute_category
2258 , l_revised_item_rec.attribute2
2259 , l_revised_item_rec.attribute3
2260 , l_revised_item_rec.attribute4
2261 , l_revised_item_rec.attribute5
2262 , l_revised_item_rec.attribute7
2263 , l_revised_item_rec.attribute8
2264 , l_revised_item_rec.attribute9
2265 , l_revised_item_rec.attribute11
2266 , l_revised_item_rec.attribute12
2267 , l_revised_item_rec.attribute13
2268 , l_revised_item_rec.attribute14
2269 , l_revised_item_rec.attribute15
2270 , l_revised_item_rec.status_type
2271 , l_revised_item_rec.start_effective_date
2272 , l_rev_item_unexp_rec.bill_sequence_id
2273 , l_revised_item_rec.mrp_active
2274 , l_revised_item_rec.update_wip
2275 , l_rev_item_unexp_rec.use_up
2276 , l_rev_item_unexp_rec.use_up_item_id
2277 , l_rev_item_unexp_rec.revised_item_sequence_id
2278 , l_revised_item_rec.use_up_plan_name
2279 , l_revised_item_rec.change_description
2280 , l_rev_item_unexp_rec.auto_implement_date
2281 , l_revised_item_rec.attribute1
2282 , l_revised_item_rec.attribute6
2283 , l_revised_item_rec.attribute10
2284
2285 /* Added by MK on 09/01/2000 ECO for Routing */
2286 , l_rev_item_unexp_rec.from_wip_entity_id
2287 , l_rev_item_unexp_rec.to_wip_entity_id
2288 , l_revised_item_rec.from_cumulative_quantity
2289 , l_revised_item_rec.lot_number
2290 , l_rev_item_unexp_rec.cfm_routing_flag
2291 , l_revised_item_rec.completion_subinventory
2292 , l_rev_item_unexp_rec.completion_locator_id
2293 -- , l_rev_item_unexp_rec.mixed_model_map_flag
2294 , l_revised_item_rec.priority
2295 , l_revised_item_rec.ctp_flag
2296 , l_rev_item_unexp_rec.routing_sequence_id
2297 , l_revised_item_rec.new_routing_revision
2298 , l_revised_item_rec.routing_comment
2299 , l_revised_item_rec.eco_for_production -- Added by MK on 10/06/00
2300 , l_rev_item_unexp_rec.CHANGE_ID --Added ON 12/12/02
2301 , l_rev_item_unexp_rec.status_code -- Added for bug 3618676
2302 FROM ENG_REVISED_ITEMS
2303 WHERE revised_item_id = p_revised_item_id
2304 AND organization_id = p_organization_id
2305 AND change_notice = p_change_notice
2306 AND NVL(new_item_revision, 'NONE') = NVL(p_new_item_revision, 'NONE')
2307 AND NVL(new_routing_revision, 'NONE') = NVL(p_new_routing_revision, 'NONE') -- Added by MK
2308 -- AND TRUNC(scheduled_date) = TRUNC(p_start_eff_date)
2309 AND scheduled_date = p_start_eff_date -- Bug 3593861: Scheduled date not truncated when querying for existing records.
2310 AND NVL(from_end_item_unit_number, 'NONE')
2311 = NVL(p_from_end_item_number, 'NONE')
2312 AND NVL(alternate_bom_designator,'-9999999999') = NVL(p_alternate_designator,'-9999999999');
2313
2314 x_return_status := ENG_Globals.G_RECORD_FOUND;
2315
2316 x_revised_item_Rec := l_revised_item_rec;
2317 x_rev_item_unexp_rec := l_rev_item_unexp_rec;
2318
2319 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Execution of Query row over . . .'); END IF;
2320
2321 EXCEPTION
2322
2323 WHEN NO_DATA_FOUND THEN
2324
2325 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Query Revised Item: ' || SQLERRM); END IF;
2326
2327 x_return_status := Eng_Globals.G_RECORD_NOT_FOUND;
2328 x_revised_item_Rec := l_revised_item_rec;
2329 x_rev_item_unexp_rec := l_rev_item_unexp_rec;
2330
2331 WHEN OTHERS THEN
2332 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Query Revised Item: ' || SQLERRM); END IF;
2333
2334 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2335 x_revised_item_Rec := l_revised_item_rec;
2336 x_rev_item_unexp_rec := l_rev_item_unexp_rec;
2337
2338 END Query_Row;
2339
2340
2341 /*****************************************************************************
2342 * Procedure : Perform_Writes
2343 * Parameters IN : Revised item exposed column record
2344 * Revised item unexposed column record
2345 * Parameters OUT: Mesg Token Table
2346 * Return Status
2347 * Purpose : This is the only procedure exposed for the user to perform
2348 * insert update and delete operations on a revised item.
2349 * So based on the transaction type this procedure will call
2350 * the internal insert, update and delete procedures.
2351 ******************************************************************************/
2352 PROCEDURE Perform_Writes( p_revised_item_rec IN
2353 Eng_Eco_Pub.Revised_Item_Rec_Type
2354 , p_rev_item_unexp_rec IN
2355 Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type
2356 , p_control_rec IN BOM_BO_Pub.Control_Rec_Type
2357 := BOM_BO_PUB.G_DEFAULT_CONTROL_REC
2358 , x_Mesg_Token_Tbl OUT NOCOPY
2359 Error_Handler.Mesg_Token_Tbl_Type
2360 , x_Return_Status OUT NOCOPY VARCHAR2
2361 )
2362 IS
2363 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2364 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2365 BEGIN
2366
2367 IF p_revised_item_rec.transaction_type = Eng_Globals.G_OPR_CREATE
2368 THEN
2369 Insert_Row( p_revised_item_rec => p_revised_item_rec
2370 , p_rev_item_unexp_rec => p_rev_item_unexp_rec
2371 , p_control_rec => p_control_rec
2372 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2373 , x_Return_Status => l_return_status
2374 );
2375 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2376 x_return_status := l_return_status;
2377
2378 ELSIF p_revised_item_rec.transaction_type = Eng_Globals.G_OPR_UPDATE
2379 THEN
2380 Update_Row( p_revised_item_rec => p_revised_item_rec
2381 , p_rev_item_unexp_rec => p_rev_item_unexp_rec
2382 , p_control_rec => p_control_rec
2383 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2384 , x_Return_Status => l_return_status
2385 );
2386 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2387 x_return_status := l_return_status;
2388
2389 ELSIF p_revised_item_rec.transaction_type = Eng_Globals.G_OPR_DELETE
2390 THEN
2391 l_return_status := FND_API.G_RET_STS_SUCCESS;
2392
2393 IF p_control_rec.caller_type <> 'FORM'
2394 THEN
2395 -- ENG_Validate_Revised_Item.Check_Entity_Delete
2396 ENG_Validate.Check_Entity_Delete
2397 ( x_return_status => l_return_status
2398 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2399 , p_revised_item_rec => p_revised_item_rec
2400 , p_rev_item_unexp_rec => p_rev_item_unexp_rec
2401 );
2402 END IF;
2403
2404 IF l_return_status = FND_API.G_RET_STS_SUCCESS THEN
2405 -- Okay to Delete the item.
2406 Delete_Row
2407 ( p_revised_item_sequence_id =>
2408 p_rev_item_unexp_rec.revised_item_sequence_id
2409 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2410 , x_return_status => l_Return_Status
2411 );
2412 END IF;
2413
2414 x_return_status := l_return_status;
2415 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
2416 END IF;
2417
2418 END Perform_Writes;
2419
2420
2421 /********************************************************************
2422 * API Name : Reschedule_Revised_Item
2423 * API Type : Public PROCEDURE
2424 * Purpose : API to reschedule the revised item.
2425 * This API is called from the JAVA layer.
2426 * Input : p_revised_item_sequence_id , p_effectivity_date
2427 * Output : x_return_status
2428 * Modifications :
2429 * For R12 changes have been made to this API for
2430 * special handling of component changes created for
2431 * destination bill.
2432 * a. Acd_type 1,3 will not exist for this case
2433 * b. When Acd_type = 2 , effectivity date should not be
2434 * updated for the components on destination bill ECOs.
2435 *
2436 * For source bill ECO changes,
2437 * these changes in effectivity should be propagated to the
2438 * related replicated components.
2439 *********************************************************************/
2440 PROCEDURE Reschedule_Revised_Item
2441 ( p_api_version IN NUMBER := 1.0 --
2442 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE --
2443 , p_commit IN VARCHAR2 := FND_API.G_FALSE --
2444 , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL --
2445 , p_debug IN VARCHAR2 := 'N' --
2446 , p_output_dir IN VARCHAR2 := NULL --
2447 , p_debug_filename IN VARCHAR2 := 'Resch_RevItem.log' --
2448 , x_return_status OUT NOCOPY VARCHAR2 --
2449 , x_msg_count OUT NOCOPY NUMBER --
2450 , x_msg_data OUT NOCOPY VARCHAR2 --
2451 , p_revised_item_sequence_id IN NUMBER
2452 , p_effectivity_date IN DATE
2453 ) IS
2454
2455 l_api_name CONSTANT VARCHAR2(30) := 'Change_Effectivity_Date';
2456 l_api_version CONSTANT NUMBER := 1.0;
2457 l_user_id NUMBER;
2458 l_login_id NUMBER;
2459 l_error_mesg VARCHAR2(2000);
2460
2461 CURSOR c_revised_item (cp_revised_item_sequence_id NUMBER) IS
2462 SELECT revised_item_id, bill_sequence_id, routing_sequence_id,
2463 change_notice, organization_id
2464 FROM eng_revised_items
2465 WHERE revised_item_sequence_id = cp_revised_item_sequence_id;
2466
2467 l_rev_item c_revised_item%ROWTYPE;
2468 -- R12 Changes for common BOM
2469 l_return_status varchar2(80);
2470 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2471 -- Cursor to Fetch all source bill's component changes that are being updated
2472 -- by reschedule
2473 CURSOR c_source_components(
2474 cp_change_notice eng_engineering_changes.change_notice%TYPE
2475 , cp_revised_item_seq_id eng_revised_items.revised_item_sequence_id%TYPE
2476 , cp_bill_sequence_id bom_structures_b.bill_sequence_id%TYPE) IS
2477 SELECT bcb.component_sequence_id
2478 FROM bom_components_b bcb
2479 WHERE bcb.CHANGE_NOTICE = cp_change_notice
2480 AND bcb.revised_item_sequence_id = cp_revised_item_seq_id
2481 AND bcb.bill_sequence_id = cp_bill_sequence_id
2482 AND (bcb.common_component_sequence_id IS NULL
2483 OR bcb.common_component_sequence_id = bcb.component_sequence_id)
2484 AND bcb.IMPLEMENTATION_DATE IS NULL;
2485
2486 BEGIN
2487
2488 l_user_id := to_number(Fnd_Profile.Value('USER_ID'));
2489 l_login_id := to_number(Fnd_Profile.Value('LOGIN_ID'));
2490
2491 IF (p_debug = 'Y')
2492 THEN
2493 BOM_Globals.Set_Debug(p_debug);
2494 Error_Handler.Open_Debug_Session
2495 ( p_debug_filename => p_debug_filename
2496 , p_output_dir => p_output_dir
2497 , x_return_status => x_return_status
2498 , x_error_mesg => l_error_mesg
2499 );
2500 END IF;
2501
2502 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('-***-Start API Reschedule_Revised_Item-***-'); END IF;
2503 SAVEPOINT Reschedule_Revised_Item_SP;
2504 x_return_status := FND_API.G_RET_STS_SUCCESS;
2505
2506 -- Standard call to check for call compatibility
2507 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
2508 THEN
2509 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2510 END IF;
2511
2512 -- Initialize message list if p_init_msg_list is set to TRUE.
2513 IF FND_API.to_Boolean(p_init_msg_list) THEN
2514 FND_MSG_PUB.initialize;
2515 END IF;
2516
2517 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Fetch the revised Item Details'); END IF;
2518 OPEN c_revised_item(cp_revised_item_sequence_id => p_revised_item_sequence_id);
2519 FETCH c_revised_item INTO l_rev_item;
2520
2521 -- update item revision
2522 UPDATE MTL_ITEM_REVISIONS_B
2523 SET effectivity_date = p_effectivity_date,
2524 last_update_date = sysdate,
2525 last_updated_by = l_user_id,
2526 last_update_login = l_login_id
2527 WHERE change_notice = l_rev_item.change_notice
2528 AND organization_id = l_rev_item.organization_id
2529 AND implementation_date is NULL
2530 AND inventory_item_id = l_rev_item.revised_item_id
2531 AND revised_item_sequence_id = p_revised_item_sequence_id;
2532 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Updated '|| SQL%ROWCOUNT ||'rows for item revision effectivity'); END IF;
2533
2534 -- update revised components EFFECTIVITY_DATE
2535 UPDATE BOM_INVENTORY_COMPONENTS bic
2536 SET bic.EFFECTIVITY_DATE = p_effectivity_date
2537 WHERE bic.CHANGE_NOTICE = l_rev_item.change_notice
2538 AND bic.revised_item_sequence_id = p_revised_item_sequence_id
2539 AND bic.bill_sequence_id = l_rev_item.bill_sequence_id
2540 AND (bic.common_component_sequence_id IS NULL
2541 OR bic.common_component_sequence_id = bic.component_sequence_id)
2542 -- This is to ensure that the destination bill's revised item
2543 -- reschedule doesnt affect its components effectivity date
2544 AND bic.IMPLEMENTATION_DATE IS NULL;
2545 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Updated '|| SQL%ROWCOUNT ||'rows for component effectivity' ); END IF;
2546
2547 -- update revised components DISABLE_DATE
2548 UPDATE BOM_INVENTORY_COMPONENTS bic1
2549 SET bic1.DISABLE_DATE = p_effectivity_date
2550 WHERE bic1.CHANGE_NOTICE = l_rev_item.change_notice
2551 AND bic1.ACD_TYPE = 3 -- ACD Type: Disable
2552 AND revised_item_sequence_id = p_revised_item_sequence_id
2553 AND bill_sequence_id = l_rev_item.bill_sequence_id
2554 AND bic1.IMPLEMENTATION_DATE IS NULL;
2555 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Updated '|| SQL%ROWCOUNT ||'rows for component disable date' ); END IF;
2556 -- R12 : Common BOM changes
2557 -- updating the replicated components for the pending changes
2558 FOR c_sc IN c_source_components(l_rev_item.change_notice, p_revised_item_sequence_id, l_rev_item.bill_sequence_id)
2559 LOOP
2560 BOMPCMBM.Update_Related_Components(
2561 p_src_comp_seq_id => c_sc.component_sequence_id
2562 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2563 , x_Return_Status => l_return_status);
2564 END LOOP;
2565 -- End changes for R12
2566
2567 -- update revised operation details
2568 Update_Rev_Operations
2569 ( x_change_notice => l_rev_item.change_notice
2570 , x_routing_sequence_id => l_rev_item.routing_sequence_id
2571 , x_revised_item_sequence_id => p_revised_item_sequence_id
2572 , x_scheduled_date => p_effectivity_date);
2573 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Updated operation sequences effectivity'); END IF;
2574
2575 -- update routing revision details
2576 UPDATE MTL_RTG_ITEM_REVISIONS
2577 SET effectivity_date = p_effectivity_date,
2578 last_update_date = sysdate,
2579 last_updated_by = l_user_id,
2580 last_update_login = l_login_id
2581 WHERE change_notice = l_rev_item.change_notice
2582 AND organization_id = l_rev_item.organization_id
2583 AND implementation_date is NULL
2584 AND inventory_item_id = l_rev_item.revised_item_id
2585 AND revised_item_sequence_id = p_revised_item_sequence_id;
2586 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Updated '|| SQL%ROWCOUNT ||'rows for routing revision effectivity'); END IF;
2587
2588 CLOSE c_revised_item;
2589 IF FND_API.To_Boolean (p_commit)
2590 THEN
2591 COMMIT WORK;
2592 END IF;
2593 FND_MSG_PUB.Count_And_Get
2594 ( p_count => x_msg_count
2595 , p_data => x_msg_data );
2596 IF Bom_Globals.Get_Debug = 'Y'
2597 THEN
2598 Error_Handler.Write_Debug('-***-End API Reschedule_Revised_Item-***-');
2599 Error_Handler.Close_Debug_Session;
2600 END IF;
2601
2602 EXCEPTION
2603 WHEN OTHERS THEN
2604 ROLLBACK TO Reschedule_Revised_Item_SP;
2605 CLOSE c_revised_item;
2606 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2607 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2608 THEN
2609 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_api_name);
2610 END IF;
2611 FND_MSG_PUB.Count_And_Get
2612 ( p_count => x_msg_count
2613 , p_data => x_msg_data );
2614 IF Bom_Globals.Get_Debug = 'Y'
2615 THEN
2616 Error_Handler.Write_Debug('Unexpected Error in API Reschedule_Revised_Item');
2617 Error_Handler.Close_Debug_Session;
2618 END IF;
2619
2620 END Reschedule_Revised_Item;
2621
2622 ------------------------------------------------------------------------
2623 -- API name : Copy_Revised_Item --
2624 -- Type : Private --
2625 -- Pre-reqs : None. --
2626 -- Procedure : Propagates the specified ECO --
2627 -- Parameters : --
2628 -- IN : p_old_revised_item_seq_id NUMBER Required --
2629 -- p_effectivity_date DATE Required --
2630 -- OUT : x_new_revised_item_seq_id VARCHAR2(1) --
2631 -- x_return_status VARCHAR2(30) --
2632 -- Version : Current version 1.0 --
2633 -- Initial version 1.0 --
2634 -- --
2635 -- Notes : This API is invoked only when a common bill has --
2636 -- pending changes associated for its WIP supply type --
2637 -- attributes and the common component in the source --
2638 -- bill is being implemented. --
2639 -- This API will create a revised item in the same --
2640 -- status as the old revised item being passed as an --
2641 -- input parameter. --
2642 -- A copy of all the destination changes are then made --
2643 -- to this revised item with the effectivity range of --
2644 -- the component being implemented. --
2645 ------------------------------------------------------------------------
2646 PROCEDURE Copy_Revised_Item (
2647 p_old_revised_item_seq_id IN NUMBER
2648 , p_effectivity_date IN DATE
2649 , x_new_revised_item_seq_id OUT NOCOPY NUMBER
2650 -- , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
2651 , x_return_status OUT NOCOPY VARCHAR2
2652 ) IS
2653
2654 CURSOR c_revised_item (cp_revised_item_sequence_id NUMBER) IS
2655 SELECT change_notice, organization_id, revised_item_id, disposition_type
2656 , early_schedule_date, status_type, bill_sequence_id, mrp_active
2657 , DESCRIPTIVE_TEXT, change_id, ALTERNATE_BOM_DESIGNATOR, status_code
2658 FROM eng_revised_items
2659 WHERE revised_item_sequence_id = cp_revised_item_sequence_id;
2660
2661 l_old_revised_item_rec c_revised_item%ROWTYPE;
2662 l_revised_item_rec Eng_Eco_Pub.Revised_Item_Rec_Type;
2663 l_rev_item_unexp_rec Eng_Eco_Pub.Rev_Item_Unexposed_Rec_Type;
2664 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
2665 BEGIN
2666 --
2667 -- Initialize OUT variables
2668 --
2669 x_new_revised_item_seq_id := NULL;
2670 x_return_status := FND_API.G_RET_STS_SUCCESS;
2671 --
2672 -- Processing Begins
2673 --
2674 OPEN c_revised_item(p_old_revised_item_seq_id);
2675 FETCH c_revised_item INTO l_old_revised_item_rec;
2676 CLOSE c_revised_item;
2677 --
2678 -- Generate a sequence_number for the new revised item
2679 -- Copy only attributes required for creation of revised item context
2680 --
2681 SELECT eng_revised_items_s.NEXTVAL INTO x_new_revised_item_seq_id FROM dual;
2682 l_revised_item_rec.eco_name := l_old_revised_item_rec.change_notice;
2683 l_rev_item_unexp_rec.organization_id := l_old_revised_item_rec.organization_id;
2684 l_rev_item_unexp_rec.revised_item_id := l_old_revised_item_rec.revised_item_id;
2685 l_revised_item_rec.disposition_type := l_old_revised_item_rec.disposition_type;
2686 l_revised_item_rec.earliest_effective_date := l_old_revised_item_rec.early_schedule_date;
2687 l_revised_item_rec.status_type := l_old_revised_item_rec.status_type;
2688 l_revised_item_rec.start_effective_date := p_effectivity_date;
2689 l_rev_item_unexp_rec.bill_sequence_id := l_old_revised_item_rec.bill_sequence_id;
2690 l_revised_item_rec.mrp_active := l_old_revised_item_rec.mrp_active;
2691 l_rev_item_unexp_rec.revised_item_sequence_id := x_new_revised_item_seq_id;
2692 l_revised_item_rec.change_description := l_old_revised_item_rec.DESCRIPTIVE_TEXT;
2693 l_rev_item_unexp_rec.cfm_routing_flag := Bom_Default_Rtg_Header.Get_Cfm_Routing_Flag;
2694 l_revised_item_rec.eco_for_production := 2;
2695 l_rev_item_unexp_rec.change_id := l_old_revised_item_rec.change_id;
2696 l_revised_item_rec.alternate_bom_code := l_old_revised_item_rec.ALTERNATE_BOM_DESIGNATOR;
2697 l_rev_item_unexp_rec.status_code := l_old_revised_item_rec.status_code;
2698 l_revised_item_rec.transaction_type := Eng_Globals.G_OPR_CREATE;
2699 --
2700 -- Call attribute defaulting
2701 --
2702 Eng_Default_Revised_Item.Attribute_Defaulting(
2703 p_revised_item_rec => l_revised_item_rec
2704 , p_rev_item_unexp_rec => l_rev_item_unexp_rec
2705 , x_revised_item_rec => l_revised_item_rec
2706 , x_rev_item_unexp_rec => l_rev_item_unexp_rec
2707 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2708 , x_return_status => x_Return_Status
2709 );
2710 --
2711 -- Call Perform writes
2712 --
2713 Eng_Revised_Item_Util.Perform_Writes(
2714 p_revised_item_rec => l_revised_item_rec
2715 , p_rev_item_unexp_rec => l_rev_item_unexp_rec
2716 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
2717 , x_Return_Status => x_Return_Status
2718 );
2719 --
2720 -- Begin Exception handling
2721 --
2722 EXCEPTION
2723 WHEN OTHERS THEN
2724 IF c_revised_item%ISOPEN THEN
2725 CLOSE c_revised_item;
2726 END IF;
2727 x_new_revised_item_seq_id := NULL;
2728 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2729
2730 END Copy_Revised_Item;
2731
2732 END ENG_Revised_Item_Util;