[Home] [Help]
PACKAGE BODY: APPS.ENG_REVISED_ITEMS_PKG
Source
1 PACKAGE BODY ENG_REVISED_ITEMS_PKG as
2 /* $Header: engprvib.pls 120.7 2006/09/15 10:57:54 pdutta noship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ENG_REVISED_ITEMS_PKG';
5
6 FUNCTION Get_High_Rev_ECO (x_organization_id NUMBER,
7 x_revised_Item_id NUMBER,
8 x_new_item_revision VARCHAR2) RETURN VARCHAR2 IS
9 ECO VARCHAR2(10);
10 cursor c1 is select change_notice from ENG_REVISED_ITEMS eri
11 where eri.Organization_Id = x_organization_id
12 and eri.Revised_Item_Id = x_revised_item_id
13 and eri.New_Item_Revision = x_new_item_revision
14 and eri.Cancellation_Date is null;
15 BEGIN
16 open c1;
17 fetch c1 into ECO;
18 close c1;
19 if ECO is null then
20 return(NULL);
21 else
22 return(ECO);
23 end if;
24 END Get_High_Rev_ECO;
25
26
27 Function Get_BOM_Lists_Seq_Id RETURN NUMBER IS
28 seq_id NUMBER;
29 BEGIN
30 select BOM_LISTS_S.nextval
31 into seq_id
32 from DUAL;
33 return(seq_id);
34 END Get_BOM_Lists_Seq_Id;
35
36
37 PROCEDURE Insert_BOM_Lists (x_revised_item_id NUMBER,
38 x_sequence_id NUMBER,
39 x_bill_sequence_id NUMBER) IS
40 BEGIN
41 insert into BOM_LISTS (sequence_id, assembly_item_id)
42 values (x_sequence_id, x_revised_item_id);
43
44 insert into BOM_LISTS (sequence_id, assembly_item_id)
45 select distinct(x_sequence_id), component_item_id
46 from BOM_INVENTORY_COMPONENTS
47 where bill_sequence_id = x_bill_sequence_id;
48 END Insert_BOM_Lists;
49
50
51 PROCEDURE Delete_BOM_Lists (x_sequence_id NUMBER) IS
52 BEGIN
53 delete from BOM_LISTS
54 where sequence_id = x_sequence_id;
55 END Delete_BOM_Lists;
56
57
58 PROCEDURE Delete_Details (x_organization_id NUMBER,
59 x_revised_item_id NUMBER,
60 x_revised_item_sequence_id NUMBER,
61 x_bill_sequence_id NUMBER,
62 x_change_notice VARCHAR2)
63 IS
64 BEGIN
65
66 delete from MTL_ITEM_REVISIONS_TL
67 where revision_id IN (SELECT revision_id
68 FROM MTL_ITEM_REVISIONS_B
69 WHERE organization_id = x_organization_id
70 and inventory_item_id = x_revised_item_id
71 and revised_item_sequence_Id = x_revised_item_sequence_id
72 and change_notice = x_change_notice
73 and implementation_date is null);
74
75 delete from MTL_ITEM_REVISIONS_B
76 where organization_id = x_organization_id
77 and inventory_item_id = x_revised_item_id
78 and revised_item_sequence_Id = x_revised_item_sequence_id
79 and change_notice = x_change_notice
80 and implementation_date is null;
81
82
83
84 delete from ENG_CURRENT_SCHEDULED_DATES
85 where organization_id = x_organization_id
86 and revised_item_id = x_revised_item_id
87 and revised_item_sequence_id = x_revised_item_sequence_id
88 and change_notice = x_change_notice;
89
90 /* Deletion from BOM_BILL_OF_MATERIALS is stopped from ENGFDECN form
91 This was done to fix the bug 1381912 as this causes orphan records
92 in bom_inventory_components,if an another session is open that is
93 using same bill header for entering the components.Now we change the
94 column value of pending_from_ecn to null,instead of deleting records
95 from bom_bill_of_mterials */
96 /*
97 delete from BOM_BILL_OF_MATERIALS bom
98 where bom.bill_sequence_id = x_bill_sequence_id
99 and bom.pending_from_ecn = x_change_notice
100 and not exists (select null
101 from BOM_INVENTORY_COMPONENTS bic
102 where bic.bill_sequence_id = bom.bill_sequence_id
103 and (bic.change_notice is null
104 or
105 bic.change_notice <> x_change_notice
106 or
107 (bic.change_notice = x_change_notice
108 and bic.revised_item_sequence_id <> x_revised_item_sequence_id)))
109 and ((bom.alternate_bom_designator is null
110 and not exists (select null
111 from BOM_BILL_OF_MATERIALS bom2
112 where bom2.organization_id = bom.organization_id
113 and bom2.assembly_item_id = bom.assembly_item_id
114 and bom2.alternate_bom_designator is not null))
115 or
116 (bom.alternate_bom_designator is not null
117 and not exists (select null
118 from ENG_REVISED_ITEMS eri
119 where eri.organization_id = bom.organization_id
120 and eri.bill_sequence_id = bom.bill_sequence_id
121 and eri.change_notice <> x_change_notice))
122 );
123 */
124 update BOM_BILL_OF_MATERIALS bom
125 set pending_from_ecn = null
126 where bom.bill_sequence_id = x_bill_sequence_id
127 and bom.pending_from_ecn = x_change_notice
128 and not exists (select null
129 from BOM_INVENTORY_COMPONENTS bic
130 where bic.bill_sequence_id = bom.bill_sequence_id
131 and (bic.change_notice is null
132 or
133 bic.change_notice <> x_change_notice
134 or
135 (bic.change_notice = x_change_notice
136 and bic.revised_item_sequence_id <> x_revised_item_sequence_id)))
137 and ((bom.alternate_bom_designator is null
138 and not exists (select null
139 from BOM_BILL_OF_MATERIALS bom2
140 where bom2.organization_id = bom.organization_id
141 and bom2.assembly_item_id = bom.assembly_item_id
142 and bom2.alternate_bom_designator is not null))
143 or
144 (bom.alternate_bom_designator is not null
145 and not exists (select null
146 from ENG_REVISED_ITEMS eri
147 where eri.organization_id = bom.organization_id
148 and eri.bill_sequence_id = bom.bill_sequence_id
149 and eri.change_notice <> x_change_notice))
150 );
151
152 update ENG_REVISED_ITEMS
153 set bill_sequence_id = ''
154 where bill_sequence_id = x_bill_sequence_id
155 and organization_id = x_organization_id
156 and implementation_date is null
157 and not exists (select null
158 from BOM_BILL_OF_MATERIALS bom
159 where bom.bill_sequence_id = x_bill_sequence_id);
160
161 END Delete_Details;
162
163
164 PROCEDURE Create_BOM (x_assembly_item_id NUMBER,
165 x_organization_id NUMBER,
166 x_alternate_BOM_designator VARCHAR2,
167 x_userid NUMBER,
168 x_change_notice VARCHAR2,
169 x_revised_item_sequence_id NUMBER,
170 x_bill_sequence_id NUMBER,
171 x_assembly_type NUMBER,
172 x_structure_type_id NUMBER) IS
173
174 l_structure_type_id NUMBER;
175 l_effectivity_control NUMBER;
176 l_login_id NUMBER;
177 BEGIN
178 l_login_id := Eng_Globals.Get_Login_Id;
179
180
181 IF x_structure_type_id IS NULL
182 THEN
183 SELECT structure_type_id
184 INTO l_structure_type_id
185 FROM bom_alternate_designators
186 WHERE
187 ((x_alternate_BOM_designator IS NULL
188 AND alternate_designator_code IS NULL
189 AND organization_id = -1)
190 OR
191 (x_alternate_BOM_designator IS NOT NULL
192 AND alternate_designator_code = x_alternate_BOM_designator
193 AND organization_id = x_organization_id));
194 ELSE
195 l_structure_type_id := x_structure_type_id;
196 END IF;
197
198 select effectivity_control
199 INTO l_effectivity_control
200 from mtl_system_items
201 where inventory_item_id = x_assembly_item_id
202 and organization_id = x_organization_id;
203
204 insert into BOM_BILL_OF_MATERIALS (
205 assembly_item_id,
206 organization_id,
207 alternate_BOM_designator,
208 last_update_date,
209 last_updated_by,
210 creation_date,
211 created_by,
212 last_update_login,
213 pending_from_ecn,
214 assembly_type,
215 common_bill_sequence_id,
216 bill_sequence_id,
217 structure_type_id,
218 implementation_date,
219 effectivity_control,
220 source_bill_sequence_id,
221 pk1_value, --Bug 4707618
222 pk2_value) --Bug 4707618
223 values (x_assembly_item_id,
224 x_organization_id,
225 x_alternate_BOM_designator,
226 sysdate,
227 x_userid,
228 sysdate,
229 x_userid,
230 x_userid,
231 x_change_notice,
232 x_assembly_type,
233 x_bill_sequence_id,
234 x_bill_sequence_id,
235 l_structure_type_id,
236 sysdate,
237 l_effectivity_control,
238 x_bill_sequence_id,
239 x_assembly_item_id, --Bug 4707618
240 x_organization_id); --Bug 4707618
241 END Create_BOM;
242
243
244 PROCEDURE Insert_Current_Scheduled_Dates (x_change_notice VARCHAR2,
245 x_organization_id NUMBER,
246 x_revised_item_id NUMBER,
247 x_scheduled_date DATE,
248 x_revised_item_sequence_id NUMBER,
249 x_requestor_id NUMBER,
250 x_userid NUMBER) IS
251 x_schedule_id NUMBER;
252 BEGIN
253 select ENG_CURRENT_SCHEDULED_DATES_S.nextval
254 into x_schedule_id
255 from sys.dual;
256 insert into ENG_CURRENT_SCHEDULED_DATES (
257 change_notice,
258 organization_id,
259 revised_item_id,
260 scheduled_date,
261 last_update_date,
262 last_updated_by,
263 schedule_id,
264 creation_date,
265 created_by,
266 last_update_login,
267 employee_id,
268 revised_item_sequence_id )
269 values (x_change_notice,
270 x_organization_id,
271 x_revised_item_id,
272 x_scheduled_date,
273 sysdate,
274 x_userid,
275 x_schedule_id,
276 sysdate,
277 x_userid,
278 x_userid,
279 x_requestor_id,
280 x_revised_item_sequence_id );
281 END Insert_Current_Scheduled_Dates;
282
283
284 PROCEDURE Delete_Item_Revisions (x_change_notice VARCHAR2,
285 x_organization_id NUMBER,
286 x_inventory_item_id NUMBER,
287 x_revised_item_sequence_id NUMBER)
288 IS
289 l_revision_id NUMBER;
290 BEGIN
291 -- Before deleting the revision, revision dependent changes should be deleted
292 -- 1. Item revision sepcific Attribute changes
293 -- 2. Item revision specific AML changes
294 -- 3. Item revision specific Attachment changes
295
296 --Bug No: 5530915
297 --Removing the attachment changes when deleting revision
298
299 delete from eng_attachment_changes
300 where
301 change_id IN (select change_id
302 from eng_engineering_changes
303 where change_notice = x_change_notice
304 and organization_id = x_organization_id) and --change_id is required for index
305 revised_item_sequence_id = x_revised_item_sequence_id and
306 entity_name = 'MTL_ITEM_REVISIONS' and
307 pk3_value IN (select revision_id
308 from MTL_ITEM_REVISIONS_B
309 where organization_id = x_organization_id
310 and inventory_item_id = x_inventory_item_id
311 and revised_item_sequence_Id = x_revised_item_sequence_id
312 and change_notice = x_change_notice
313 and implementation_date is null);
314
315
316
317 delete from MTL_ITEM_REVISIONS_TL
318 where revision_id IN (select revision_id
319 from MTL_ITEM_REVISIONS_B
320 where organization_id = x_organization_id
321 and inventory_item_id = x_inventory_item_id
322 and revised_item_sequence_Id = x_revised_item_sequence_id
323 and change_notice = x_change_notice
324 and implementation_date is null);
325
326 delete from MTL_ITEM_REVISIONS_B
327 where organization_id = x_organization_id
328 and inventory_item_id =x_inventory_item_id
329 and revised_item_sequence_Id = x_revised_item_sequence_id
330 and change_notice = x_change_notice
331 and implementation_date is null;
332
333
334 END Delete_Item_Revisions;
335
336 PROCEDURE Insert_Item_Revisions (x_inventory_item_id NUMBER,
337 x_organization_id NUMBER,
338 x_revision VARCHAR2,
339 x_userid NUMBER,
340 x_change_notice VARCHAR2,
341 x_scheduled_date DATE,
342 x_revised_item_sequence_id NUMBER,
343 x_revision_description VARCHAR2 := NULL,
344 p_new_revision_label VARCHAR2 DEFAULT NULL,
345 p_new_revision_reason_code VARCHAR2 DEFAULT NULL,
346 p_from_revision_id NUMBER DEFAULT NULL)
347 IS
348 l_revision_id NUMBER;
349
350 BEGIN
351 Insert_Item_Revisions (x_inventory_item_id => x_inventory_item_id,
352 x_organization_id => x_organization_id,
353 x_revision => x_revision,
354 x_userid => x_userid,
355 x_change_notice => x_change_notice,
356 x_scheduled_date => x_scheduled_date,
357 x_revised_item_sequence_id => x_revised_item_sequence_id,
358 x_revision_description => x_revision_description,
359 p_new_revision_label => p_new_revision_label,
360 p_new_revision_reason_code => p_new_revision_reason_code,
361 p_from_revision_id => p_from_revision_id,
362 x_new_revision_id => l_revision_id);
363 END;
364
365 PROCEDURE Insert_Item_Revisions (x_inventory_item_id NUMBER,
366 x_organization_id NUMBER,
367 x_revision VARCHAR2,
368 x_userid NUMBER,
369 x_change_notice VARCHAR2,
370 x_scheduled_date DATE,
371 x_revised_item_sequence_id NUMBER,
372 x_revision_description VARCHAR2 := NULL,
373 p_new_revision_label VARCHAR2 DEFAULT NULL,
374 p_new_revision_reason_code VARCHAR2 DEFAULT NULL,
375 p_from_revision_id NUMBER DEFAULT NULL,
376 x_new_revision_id IN OUT NOCOPY NUMBER)
377 IS
378 l_language_code VARCHAR2(3);
379 l_revision_id NUMBER;
380 l_Return_Status VARCHAR2(3);
381
382 l_att_return_status VARCHAR2(1);
383 l_msg_count NUMBER;
384 l_msg_data VARCHAR2(4000);
385 l_change_id NUMBER;
386 l_curr_rev_id NUMBER;
387
388
389 BEGIN
390 IF (Bom_globals.Get_Caller_Type <> BOM_GLOBALS.G_MASS_CHANGE) THEN -- added for bug 3534567
391 insert into MTL_ITEM_REVISIONS_B (
392 inventory_item_id,
393 organization_id,
394 revision,
395 revision_label,
396 last_update_date,
397 last_updated_by,
398 creation_date,
399 created_by,
400 last_update_login,
401 change_notice,
402 ecn_initiation_date,
403 effectivity_date,
404 revised_item_sequence_id,
405 revision_id,
406 object_version_number,
407 description,
408 revision_reason
409 )
410 values (x_inventory_item_id,
411 x_organization_id,
412 x_revision,
413 --x_revision,
414 decode( decode(p_new_revision_label, FND_API.G_MISS_CHAR, NULL, p_new_revision_label),
415 NULL, x_revision, p_new_revision_label),
416 sysdate,
417 x_userid,
418 sysdate,
419 x_userid,
420 x_userid,
421 x_change_notice,
422 sysdate,
423 decode(x_scheduled_date, trunc(sysdate), sysdate, x_scheduled_date),
424 x_revised_item_sequence_id,
425 mtl_item_revisions_b_s.NEXTVAL,
426 1,
427 decode(x_revision_description,FND_API.G_MISS_CHAR,NULL,x_revision_description),
428 decode(p_new_revision_reason_code, FND_API.G_MISS_CHAR, NULL, p_new_revision_reason_code)
429 )RETURNING revision_id INTO l_revision_id;
430
431 SELECT userenv('LANG') INTO l_language_code FROM dual;
432 -- description is stored in MTL_ITEM_REVISIONS_TL
433 insert into MTL_ITEM_REVISIONS_TL (
434 inventory_item_id,
435 organization_id,
436 revision_id,
437 language,
438 source_lang,
439 last_update_date,
440 last_updated_by,
441 creation_date,
442 created_by,
443 last_update_login,
444 description )
445 SELECT x_inventory_item_id,
446 x_organization_id,
447 l_revision_id,
448 lang.language_code,
449 l_language_code,
450 sysdate,
451 x_userid,
452 sysdate,
453 x_userid,
454 x_userid,
455 /* Item revision description support for ECO Bug: 1667419 */
456 decode(x_revision_description,FND_API.G_MISS_CHAR,NULL,x_revision_description)
457 FROM FND_LANGUAGES lang
458 where lang.INSTALLED_FLAG in ('I', 'B')
459 and not exists
460 (select NULL
461 from MTL_ITEM_REVISIONS_TL T
462 where T.INVENTORY_ITEM_ID = x_inventory_item_id
463 and T.ORGANIZATION_ID = x_organization_id
464 and T.REVISION_ID = l_revision_id
465 and T.LANGUAGE = lang.LANGUAGE_CODE);
466 x_new_revision_id := l_revision_id;
467
468 -- Bug : 5520086 Item Revision Level attribute values also have to be copied.
469 INV_ITEM_REVISION_PUB.copy_rev_UDA( p_organization_id => x_organization_id
470 ,p_inventory_item_id => x_inventory_item_id
471 ,p_revision_id => x_new_revision_id
472 ,p_revision => x_revision
473 ,p_source_revision_id => p_from_revision_id) ;
474
475
476 -- Bug 3886562
477 -- Item revision level attachments is a PLM functionality
478 -- Whenever a new revision is created, all the attachments of
479 -- the current revision must be copied to the new revision
480
481 BEGIN
482 -- Fetch the current revision
483
484 --11.5.10E
485 -- Fetching the from revision, if it is null, then fetching the
486 -- current revision
487 IF (p_from_revision_id is NULL OR
488 p_from_revision_id = FND_API.G_MISS_NUM)
489 THEN
490 l_curr_rev_id := bom_revisions.GET_ITEM_REVISION_ID_FN(
491 examine_type => 'IMPL_ONLY'
492 , org_id => x_organization_id
493 , item_id => x_inventory_item_id
494 , rev_date => SYSDATE);
495 ELSE
496 l_curr_rev_id := p_from_revision_id;
497 END IF;
498
499 -- Fetch the change id
500 SELECT change_id
501 INTO l_change_id
502 FROM eng_engineering_changes
503 WHERE change_notice = x_change_notice
504 AND organization_id = x_organization_id;
505
506 -- Calling API to copy attachments to the detination entity (new revision)
507 Eng_attachment_implementation.Copy_Attachments_And_Changes(
508 p_api_version => 1.0
509 , x_return_status => l_att_return_status
510 , x_msg_count => l_msg_count
511 , x_msg_data => l_msg_data
512 , p_change_id => l_change_id
513 , p_rev_item_seq_id => x_revised_item_sequence_id
514 , p_org_id => x_organization_id
515 , p_inv_item_id => x_inventory_item_id
516 , p_curr_rev_id => l_curr_rev_id
517 , p_new_rev_id => l_revision_id);
518
519 EXCEPTION
520 WHEN OTHERS THEN
521 -- Cannot copy attachments
522 -- **No error handling done
523 null;
524 END;
525 -- End Changes for bug 3886562
526 end if; -- bug3534567
527 END Insert_Item_Revisions;
528
529
530
531 PROCEDURE Update_Item_Revisions (x_revision VARCHAR2,
532 x_scheduled_date DATE,
533 x_change_notice VARCHAR2,
534 x_organization_id NUMBER,
535 x_inventory_item_id NUMBER,
536 x_revised_item_sequence_id NUMBER,
537 x_revision_description VARCHAR2 := NULL)
538 IS
539 l_language_code VARCHAR2(3);
540 l_revision_id NUMBER;
541 l_user_id NUMBER := FND_GLOBAL.User_Id;
542 l_login_id NUMBER := FND_GLOBAL.Login_Id;
543
544 BEGIN
545
546 update MTL_ITEM_REVISIONS_B
547 set revision = x_revision,
548 revision_label = x_revision, -- Bug No:3612330 added by sseraphi to update rev label along with rev code.
549 effectivity_date = decode(x_scheduled_date, trunc(sysdate), sysdate, x_scheduled_date),
550 last_update_date = SYSDATE,
551 last_update_login = l_login_id,
552 last_updated_by = l_user_id
553 where change_notice = x_change_notice
554 and organization_id = x_organization_id
555 and inventory_item_id = x_inventory_item_id
556 and revised_item_sequence_id = x_revised_item_sequence_id
557 RETURNING revision_id INTO l_revision_id;
558
559 SELECT userenv('LANG') INTO l_language_code FROM dual;
560
561 update MTL_ITEM_REVISIONS_TL
562 set
563 last_update_date = SYSDATE, --who column
564 last_update_login = l_login_id, --who column
565 last_updated_by = l_user_id, --who column
566 description = x_revision_description,
567 source_lang = l_language_code
568 where revision_id = l_revision_id
569 AND LANGUAGE = l_language_code;
570
571 END Update_Item_Revisions;
572
573
574 /* Modifications :
575 * For R12 changes have been made to this API for
576 * special handling of component changes created for
577 * destination bill.
578 * a. Acd_type 1,3 will not exist for this case
579 * b. When Acd_type = 2 , effectivity date should not be
580 * updated for the components on destination bill ECOs.
581 *
582 * For source bill ECO changes,
583 * these changes in effectivity should be propagated to the
584 * related replicated components.
585 *********************************************************************/
586
587 PROCEDURE Update_Inventory_Components (x_change_notice VARCHAR2,
588 x_bill_sequence_id NUMBER,
589 x_revised_item_sequence_id NUMBER,
590 x_scheduled_date DATE,
591 x_from_end_item_unit_number VARCHAR2 DEFAULT NULL) IS
592 -- R12 Changes for common BOM
593 l_return_status varchar2(80);
594 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
595 -- Cursor to Fetch all source bill's component changes that are being updated
596 -- by reschedule
597 CURSOR c_source_components(
598 cp_change_notice eng_engineering_changes.change_notice%TYPE
599 , cp_revised_item_seq_id eng_revised_items.revised_item_sequence_id%TYPE
600 , cp_bill_sequence_id bom_structures_b.bill_sequence_id%TYPE) IS
601 SELECT bcb.component_sequence_id
602 FROM bom_components_b bcb
603 WHERE bcb.CHANGE_NOTICE = cp_change_notice
604 AND bcb.revised_item_sequence_id = cp_revised_item_seq_id
605 AND bcb.bill_sequence_id = cp_bill_sequence_id
606 AND (bcb.common_component_sequence_id IS NULL
607 OR bcb.common_component_sequence_id = bcb.component_sequence_id)
608 AND bcb.IMPLEMENTATION_DATE IS NULL;
609
610 BEGIN
611 update BOM_INVENTORY_COMPONENTS
612 set effectivity_date = x_scheduled_date,
613 from_end_item_unit_number = x_from_end_item_unit_number
614 where change_notice = x_change_notice
615 and bill_sequence_id = x_bill_sequence_id
616 and revised_item_sequence_id = x_revised_item_sequence_id
617 AND (common_component_sequence_id IS NULL
618 OR common_component_sequence_id = component_sequence_id)
619 -- This is to ensure that the destination bill's revised item
620 -- reschedule doesnt affect its components effectivity date
621 and implementation_date is null;
622
623 update BOM_INVENTORY_COMPONENTS
624 set disable_date = x_scheduled_date
625 where change_notice = x_change_notice
626 and bill_sequence_id = x_bill_sequence_id
627 and revised_item_sequence_id = x_revised_item_sequence_id
628 and implementation_date is null
629 and acd_type = 3;
630
631 -- R12 : Common BOM changes
632 -- updating the replicated components for the pending changes
633 FOR c_sc IN c_source_components(x_change_notice, x_revised_item_sequence_id, x_bill_sequence_id)
634 LOOP
635 BOMPCMBM.Update_Related_Components(
636 p_src_comp_seq_id => c_sc.component_sequence_id
637 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
638 , x_Return_Status => l_return_status);
639 END LOOP;
640 -- End changes for R12
641 END Update_Inventory_Components;
642
643
644 -- Added for bug 3496165
645 /********************************************************************
646 * API Name : UPDATE_REVISION_CHANGE_NOTICE
647 * Parameters IN : p_revision_id, p_change_notice
648 * Parameters OUT: None
649 * Purpose : Updates the value of change_notice in the
650 * mtl_item_revisions_b/_tl table with the value passed as parameter
651 * for the row specified.
652 *********************************************************************/
653 PROCEDURE UPDATE_REVISION_CHANGE_NOTICE ( p_revision_id IN NUMBER
654 , p_change_notice IN VARCHAR2
655 ) IS
656 l_language_code VARCHAR2(3);
657 l_revision_id NUMBER;
658 l_user_id NUMBER := FND_GLOBAL.User_Id;
659 l_login_id NUMBER := FND_GLOBAL.Login_Id;
660 BEGIN
661
662 UPDATE MTL_ITEM_REVISIONS_B
663 SET change_notice = p_change_notice,
664 last_update_date = SYSDATE,
665 last_update_login = l_login_id,
666 last_updated_by = l_user_id
667 WHERE revision_id = p_revision_id;
668
669 SELECT userenv('LANG')
670 INTO l_language_code
671 FROM dual;
672
673 UPDATE MTL_ITEM_REVISIONS_TL
674 SET last_update_date = SYSDATE, --who column
675 last_update_login = l_login_id, --who column
676 last_updated_by = l_user_id, --who column
677 source_lang = l_language_code
678 where revision_id = l_revision_id
679 AND LANGUAGE = l_language_code;
680
681 END UPDATE_REVISION_CHANGE_NOTICE;
682
683 PROCEDURE Query_Target_Revised_Item (
684 p_api_version IN NUMBER := 1.0
685 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
686 -- , p_commit IN VARCHAR2 := FND_API.G_FALSE,
687 -- , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
688 , x_return_status OUT NOCOPY VARCHAR2
689 , x_msg_count OUT NOCOPY NUMBER
690 , x_msg_data OUT NOCOPY VARCHAR2
691 , p_change_id IN NUMBER
692 , p_organization_id IN NUMBER
693 , p_revised_item_id IN NUMBER
694 , p_revision_id IN NUMBER
695 , x_revised_item_seq_id OUT NOCOPY NUMBER
696 )
697 IS
698 CURSOR c_check_revision_id IS
699 SELECT 1
700 FROM mtl_item_revisions
701 WHERE revision_id = p_revision_id
702 AND inventory_item_id = p_revised_item_id
703 AND organization_id = p_organization_id;
704
705 CURSOR c_query_revised_item IS
706 SELECT eri.revised_item_sequence_id
707 FROM eng_revised_items eri , mtl_system_items_vl msiv
708 WHERE eri.change_id = p_change_id
709 AND eri.organization_id = p_organization_id
710 AND eri.revised_item_id = p_revised_item_id
711 AND eri.revised_item_id = msiv.inventory_item_id
712 AND eri.organization_id = msiv.organization_id
713 AND decode(msiv.bom_item_type ,
714 4 , nvl(FND_PROFILE.value('ENG:STANDARD_ITEM_ECN_ACCESS'), 1) ,
715 3 , nvl(FND_PROFILE.value('ENG:PLANNING_ITEM_ECN_ACCESS'), 1) ,
716 2 , nvl(FND_PROFILE.value('ENG:MODEL_ITEM_ECN_ACCESS'), 1) ,
717 1 , nvl(FND_PROFILE.value('ENG:MODEL_ITEM_ECN_ACCESS'), 1) , 1) = 1
718 AND (eri.status_type = 1
719 OR (eri.status_type = 10
720 AND EXISTS
721 (SELECT 1
722 FROM eng_change_statuses ecsb
723 WHERE ecsb.status_code = eri.status_code
724 AND ecsb.status_type = 1)))
725 AND nvl(eri.new_item_revision_id, eri.current_item_revision_id)
726 = nvl(p_revision_id, nvl(eri.new_item_revision_id, eri.current_item_revision_id))
727 AND eri.scheduled_date IN
728 (SELECT eri2.scheduled_date
729 FROM eng_revised_items eri2
730 WHERE eri2.change_id = eri.change_id
731 AND eri2.organization_id = eri.organization_id
732 AND eri2.revised_item_id = eri.revised_item_id)
733 ORDER BY eri.scheduled_date DESC;
734
735 l_dummy NUMBER;
736 l_return_status VARCHAR2(1);
737 l_api_name VARCHAR2(30);
738 l_api_version NUMBER;
739 BEGIN
740
741 l_api_name := 'QUERY_TARGET_REVISED_ITEM';
742 l_api_version := 1.0;
743
744 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
745 THEN
746 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
747 END IF;
748
749 -- Initialize message list if p_init_msg_list is set to TRUE.
750 IF FND_API.to_Boolean(p_init_msg_list) THEN
751 FND_MSG_PUB.Initialize;
752 END IF;
753 l_return_status := 'S';
754
755 -- Validate the revision id
756 IF p_revision_id IS NOT NULL
757 THEN
758 OPEN c_check_revision_id;
759 FETCH c_check_revision_id INTO l_dummy;
760 CLOSE c_check_revision_id;
761
762 IF l_dummy <> 1
763 THEN
764 l_return_status := FND_API.G_RET_STS_ERROR;
765 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
766 THEN
767 Fnd_message.set_name('ENG', 'ENG_REVISION_INVALID');
768 Fnd_msg_pub.Add;
769 END IF;
770 END IF;
771 END IF;
772 IF l_return_status = 'S'
773 THEN
774 OPEN c_query_revised_item;
775 FETCH c_query_revised_item INTO x_revised_item_seq_id;
776 CLOSE c_query_revised_item;
777 END IF;
778 -- Closing
779 x_return_status := l_return_status;
780
781 FND_MSG_PUB.Count_And_Get(
782 p_count => x_msg_count
783 , p_data => x_msg_data
784 );
785
786 EXCEPTION
787 WHEN OTHERS THEN
788 IF c_check_revision_id%ISOPEN THEN
789 CLOSE c_check_revision_id;
790 END IF;
791 IF c_query_revised_item%ISOPEN THEN
792 CLOSE c_query_revised_item;
793 END IF;
794 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
795 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
796 THEN
797 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
798 END IF;
799 FND_MSG_PUB.Count_And_Get(
800 p_count => x_msg_count
801 , p_data => x_msg_data
802 );
803
804 END Query_Target_Revised_Item;
805
806 PROCEDURE Get_Component_Intf_Change_Dtls (
807 p_api_version IN NUMBER := 1.0
808 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
809 -- , p_commit IN VARCHAR2 := FND_API.G_FALSE,
810 -- , p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
811 , x_return_status OUT NOCOPY VARCHAR2
812 , x_msg_count OUT NOCOPY NUMBER
813 , x_msg_data OUT NOCOPY VARCHAR2
814 , p_change_id IN NUMBER
815 , p_change_notice IN VARCHAR2
816 , p_organization_id IN NUMBER
817 , p_revised_item_id IN NUMBER
818 , p_bill_sequence_id IN NUMBER
819 , p_component_item_id IN NUMBER
820 , p_effectivity_date IN DATE := NULL
821 , p_from_end_item_unit_number IN NUMBER := NULL
822 , p_from_end_item_rev_id IN NUMBER := NULL
823 , p_old_component_sequence_id IN NUMBER := NULL
824 , p_transaction_type IN VARCHAR2
825 , x_revised_item_sequence_id OUT NOCOPY NUMBER
826 , x_component_sequence_id OUT NOCOPY NUMBER
827 , x_acd_type OUT NOCOPY NUMBER
828 , x_change_transaction_type OUT NOCOPY VARCHAR2
829 )
830 IS
831 l_return_status VARCHAR2(1);
832 l_api_name VARCHAR2(30);
833 l_api_version NUMBER;
834
835 CURSOR c_bill_details IS
836 SELECT alternate_bom_designator
837 FROM bom_structures_b
838 WHERE bill_sequence_id = p_bill_sequence_id;
839
840 CURSOR c_query_revised_item
841 IS
842 SELECT revised_item_sequence_id
843 FROM eng_revised_items
844 WHERE revised_item_id = p_revised_item_id
845 AND (p_effectivity_date IS NULL OR scheduled_date = p_effectivity_date)
846 AND bill_sequence_id = p_bill_sequence_id
847 AND NVL(from_end_item_unit_number, FND_API.G_MISS_CHAR)
848 = nvl(p_from_end_item_unit_number, FND_API.G_MISS_CHAR)
849 AND nvl(from_end_item_rev_id, '-1')
850 = nvl(p_from_end_item_rev_id, '-1')
851 AND change_id = p_change_id
852 AND status_type IN (1);
853
854 CURSOR c_query_revised_component
855 IS
856 SELECT revised_item_sequence_id, acd_type, component_sequence_id
857 FROM bom_components_b
858 WHERE component_item_id = p_component_item_id
859 AND (p_effectivity_date IS NULL OR effectivity_date = p_effectivity_date)
860 AND bill_sequence_id = p_bill_sequence_id
861 AND NVL(from_end_item_unit_number, FND_API.G_MISS_CHAR)
862 = nvl(p_from_end_item_unit_number, FND_API.G_MISS_CHAR)
863 AND nvl(from_end_item_rev_id, '-1')
864 = nvl(p_from_end_item_rev_id, '-1')
865 AND change_notice = p_change_notice
866 AND old_component_sequence_id = p_old_component_sequence_id
867 AND implementation_date IS NULL;
868
869 BEGIN
870
871 l_api_name := 'GET_COMPONENT_INTF_CHANGE_DTLS';
872 l_api_version := 1.0;
873
874 IF NOT FND_API.Compatible_API_Call(l_api_version, p_api_version, l_api_name, G_PKG_NAME)
875 THEN
876 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
877 END IF;
878
879 -- Initialize message list if p_init_msg_list is set to TRUE.
880 IF FND_API.to_Boolean(p_init_msg_list) THEN
881 FND_MSG_PUB.Initialize;
882 END IF;
883 l_return_status := 'S';
884
885 /* IF l_dummy <> 1
886 THEN
887 l_return_status := FND_API.G_RET_STS_ERROR;
888 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_ERROR)
889 THEN
890 Fnd_message.set_name('ENG', 'ENG_effectivity_details not provided INVALID');
891 Fnd_msg_pub.Add;
892 END IF;
893 END IF;
894 */
895
896 IF (p_transaction_type = 'CREATE')
897 THEN
898 x_change_transaction_type := 'CREATE';
899 x_acd_type := 1;
900 ELSIF (p_transaction_type = 'DISABLE')
901 THEN
902 x_change_transaction_type := 'CREATE';
903 x_acd_type := 3;
904 ELSIF (p_transaction_type = 'DELETE')
905 THEN
906 x_change_transaction_type := 'DELETE';
907 x_acd_type := 0;
908 ELSIF (p_transaction_type = 'UPDATE')
909 THEN
910 OPEN c_query_revised_component;
911 FETCH c_query_revised_component INTO x_revised_item_sequence_id, x_acd_type, x_component_sequence_id;
912 IF c_query_revised_component%NOTFOUND
913 THEN
914 x_change_transaction_type := 'CREATE';
915 x_acd_type := 2;
916 END IF;
917 CLOSE c_query_revised_component;
918 END IF;
919 -- Closing
920 x_return_status := l_return_status;
921
922 FND_MSG_PUB.Count_And_Get(
923 p_count => x_msg_count
924 , p_data => x_msg_data
925 );
926
927 EXCEPTION
928 WHEN OTHERS THEN
929 IF c_query_revised_component%ISOPEN
930 THEN
931 CLOSE c_query_revised_component;
932 END IF;
933
934 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
935 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
936 THEN
937 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
938 END IF;
939 FND_MSG_PUB.Count_And_Get(
940 p_count => x_msg_count
941 , p_data => x_msg_data
942 );
943 END Get_Component_Intf_Change_Dtls;
944
945 -- Bug 4290411
946 /********************************************************************
947 * API Name : Check_Rev_Comp_Editable
948 * Parameters IN : p_component_sequence_id
949 * Parameters OUT: x_rev_comp_editable_flag
950 * Purpose : The API is called from bom explosion to check if
951 * revised component is editable.
952 * This api does not check change header status/workflow
953 * and user access as PW already handles this.
954 *********************************************************************/
955 PROCEDURE Check_Rev_Comp_Editable (
956 p_component_sequence_id IN NUMBER
957 , x_rev_comp_editable_flag OUT NOCOPY VARCHAR2 -- FND_API.G_TRUE, FND_API.G_FALSE
958 ) IS
959 -- Cursor to check if revised component is editable
960 -- 1: revised item privilege based on profile access values
961 -- 2: revised item status check
962 -- 3: common bom for src pending changes
963 CURSOR c_chk_rev_comp_editable IS
964 SELECT eri.revised_item_sequence_id
965 FROM eng_revised_items eri , mtl_system_items_vl msiv , bom_components_b bcb
966 WHERE eri.revised_item_sequence_id = bcb.revised_item_sequence_id
967 and bcb.component_sequence_id = p_component_sequence_id
968 AND eri.revised_item_id = msiv.inventory_item_id
969 AND eri.organization_id = msiv.organization_id
970 -- 1: revised item privilege based on profile access values
971 AND decode(msiv.bom_item_type ,
972 4 , nvl(FND_PROFILE.value('ENG:STANDARD_ITEM_ECN_ACCESS'), 1) ,
973 3 , nvl(FND_PROFILE.value('ENG:PLANNING_ITEM_ECN_ACCESS'), 1) ,
974 2 , nvl(FND_PROFILE.value('ENG:MODEL_ITEM_ECN_ACCESS'), 1) ,
975 1 , nvl(FND_PROFILE.value('ENG:MODEL_ITEM_ECN_ACCESS'), 1) , 1) = 1
976 -- 2: revised item status check
977 AND (eri.status_type = 1
978 OR (eri.status_type = 10
979 AND EXISTS
980 (SELECT 1
981 FROM eng_change_statuses ecsb
982 WHERE ecsb.status_code = eri.status_code
983 AND ecsb.status_type = 1)))
984 -- 3: common bom for src pending changes
985 AND bcb.bill_sequence_id = eri.bill_sequence_id;
986 l_revised_item_seq_id NUMBER;
987 BEGIN
988 x_rev_comp_editable_flag := FND_API.G_FALSE;
989 OPEN c_chk_rev_comp_editable;
990 FETCH c_chk_rev_comp_editable INTO l_revised_item_seq_id;
991 IF (c_chk_rev_comp_editable%FOUND)
992 THEN
993 x_rev_comp_editable_flag := FND_API.G_TRUE;
994 END IF;
995 CLOSE c_chk_rev_comp_editable;
996 EXCEPTION
997 WHEN OTHERS THEN
998 IF (c_chk_rev_comp_editable%ISOPEN)
999 THEN
1000 CLOSE c_chk_rev_comp_editable;
1001 END IF;
1002 END Check_Rev_Comp_Editable;
1003
1004 END ENG_REVISED_ITEMS_PKG ;