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