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