DBA Data[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 ;