[Home] [Help]
PACKAGE BODY: APPS.ENG_ITEM_PKG
Source
1 PACKAGE BODY ENG_ITEM_PKG AS
2 /* $Header: ENGPITRB.pls 120.3 2010/10/07 06:58:26 ybabulal ship $ */
3
4 -- +-------------------------- ITEM_TRANSFER ---------------------------------+
5 -- NAME
6 -- ITEM_TRANSFER
7
8 -- DESCRIPTION
9 -- Transfer the Engineering Item to Manufacturing, and set revisions.
10
11 -- REQUIRES
12 -- org_id: organization id
13 -- eng_item_id: original item id
14 -- mfg_item_id: new id for manufacturing item
15 -- lastloginid not used internally just kept to support already existing usage
16 -- mfg_description: new description for manufacturing item
17 -- ecn_name: associated change order
18 -- bom_rev_starting: new revision
19 -- segment1
20 -- segment2
21 -- segment3
22 -- segment4
23 -- segment5
24 -- segment6
25 -- segment7
26 -- segment8
27 -- segment9
28 -- segment10
29 -- segment11
30 -- segment12
31 -- segment13
32 -- segment14
33 -- segment15
34 -- segment16
35 -- segment17
36 -- segment18
37 -- segment19
38 -- segment20
39
40 -- OUTPUT
41
42 -- NOTES
43
44 -- +--------------------------------------------------------------------------+
45
46 PROCEDURE ITEM_TRANSFER
47 (
48 X_org_id IN NUMBER,
49 X_eng_item_id IN NUMBER,
50 X_mfg_item_id IN NUMBER,
51 X_last_login_id IN NUMBER,
52 X_mfg_description IN VARCHAR2,
53 X_ecn_name IN VARCHAR2,
54 X_bom_rev_starting IN VARCHAR2,
55 X_segment1 IN VARCHAR2,
56 X_segment2 IN VARCHAR2,
57 X_segment3 IN VARCHAR2,
58 X_segment4 IN VARCHAR2,
59 X_segment5 IN VARCHAR2,
60 X_segment6 IN VARCHAR2,
61 X_segment7 IN VARCHAR2,
62 X_segment8 IN VARCHAR2,
63 X_segment9 IN VARCHAR2,
64 X_segment10 IN VARCHAR2,
65 X_segment11 IN VARCHAR2,
66 X_segment12 IN VARCHAR2,
67 X_segment13 IN VARCHAR2,
68 X_segment14 IN VARCHAR2,
69 X_segment15 IN VARCHAR2,
70 X_segment16 IN VARCHAR2,
71 X_segment17 IN VARCHAR2,
72 X_segment18 IN VARCHAR2,
73 X_segment19 IN VARCHAR2,
74 X_segment20 IN VARCHAR2
75 )
76 IS
77 X_master_org NUMBER;
78 X_stmt_num NUMBER;
79 BEGIN
80
81 IF (X_eng_item_id = X_mfg_item_id) THEN
82
83 X_stmt_num := 100;
84 BEGIN
85 UPDATE MTL_SYSTEM_ITEMS
86 SET ENG_ITEM_FLAG = 'N',
87 --DESCRIPTION = X_mfg_description,
88 LAST_UPDATE_LOGIN = to_number(Fnd_Profile.Value('LOGIN_ID')),
89
90 -----------------------------------
91 -- Commented out by AS on 04/14/98
92 -- See bug 647693.
93
94 -- CREATED_BY = to_number(Fnd_Profile.Value('USER_ID')),
95 -- CREATION_DATE = SYSDATE,
96 -----------------------------------
97
98 LAST_UPDATE_DATE = SYSDATE,
99 LAST_UPDATED_BY = to_number(Fnd_Profile.Value('USER_ID')),
100 ENGINEERING_DATE = SYSDATE,
101 ENGINEERING_ECN_CODE = X_ecn_name
102 WHERE INVENTORY_ITEM_ID = X_eng_item_id
103 AND ORGANIZATION_ID = X_org_id;
104 EXCEPTION
105 WHEN OTHERS THEN
106 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'ITEM_TRANSFER',
107 stmt_num => X_stmt_num,
108 message_name => 'ENG_ENUBRT_ERROR',
109 token => SQLERRM);
110 END;
111
112 -- The following code included to trasfer master org item when
113 -- transferring an item from eng to mfg. Bug #709403.
114 X_stmt_num := 101;
115 BEGIN
116 SELECT MASTER_ORGANIZATION_ID
117 INTO X_master_org
118 FROM MTL_PARAMETERS
119 WHERE ORGANIZATION_ID = X_org_id;
120 EXCEPTION
121 WHEN NO_DATA_FOUND THEN
122 X_master_org := X_org_id;
123 WHEN OTHERS THEN
124 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'ITEM_TRANSFER',
125 stmt_num => X_stmt_num,
126 message_name => 'ENG_ENUBRT_ERROR',
127 token => SQLERRM);
128 END;
129
130 IF (X_master_org <> X_org_id) THEN
131 X_stmt_num := 102;
132 BEGIN
133 UPDATE MTL_SYSTEM_ITEMS
134 SET ENG_ITEM_FLAG = 'N',
135 --DESCRIPTION = X_mfg_description,
136 LAST_UPDATE_LOGIN = to_number(Fnd_Profile.Value('LOGIN_ID')),
137
138 -----------------------------------
139 -- Commented out by AS on 04/14/98
140 -- See bug 647693.
141
142 -- CREATED_BY = to_number(Fnd_Profile.Value('USER_ID')),
143 -- CREATION_DATE = SYSDATE,
144 -----------------------------------
145
146 LAST_UPDATE_DATE = SYSDATE,
147 LAST_UPDATED_BY = to_number(Fnd_Profile.Value('USER_ID')),
148 ENGINEERING_DATE = SYSDATE,
149 ENGINEERING_ECN_CODE = X_ecn_name
150 WHERE INVENTORY_ITEM_ID = X_eng_item_id
151 AND ORGANIZATION_ID = X_master_org
152 AND ENG_ITEM_FLAG <> 'N';
153 EXCEPTION
154 WHEN OTHERS THEN
155 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'ITEM_TRANSFER',
156 stmt_num => X_stmt_num,
157 message_name => 'ENG_ENUBRT_ERROR',
158 token => SQLERRM);
159 END;
160 END IF;
161 ELSE
162
163 X_stmt_num := 101;
164 BEGIN
165 SELECT MASTER_ORGANIZATION_ID
166 INTO X_master_org
167 FROM MTL_PARAMETERS
168 WHERE ORGANIZATION_ID = X_org_id;
169 EXCEPTION
170 WHEN NO_DATA_FOUND THEN
171 X_master_org := X_org_id;
172 WHEN OTHERS THEN
173 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'ITEM_TRANSFER',
174 stmt_num => X_stmt_num,
175 message_name => 'ENG_ENUBRT_ERROR',
176 token => SQLERRM);
177 END;
178
179 END IF;
180
181 -- Note: We do not need to worry about costs and cross references since the
182 -- item id has not changed
183
184 IF (X_eng_item_id <> X_mfg_item_id) THEN
185
186 -- Copy rows for MTL_SYSTEM_ITEMS
187
188 ENG_COPY_TABLE_ROWS_PKG.C_MTL_SYSTEM_ITEMS(X_org_id,
189 X_org_id,
190 X_eng_item_id,
191 X_mfg_item_id,
192 -1,
193 X_mfg_description,
194 X_ecn_name,
195 X_segment1,
196 X_segment2,
197 X_segment3,
198 X_segment4,
199 X_segment5,
200 X_segment6,
201 X_segment7,
202 X_segment8,
203 X_segment9,
204 X_segment10,
205 X_segment11,
206 X_segment12,
207 X_segment13,
208 X_segment14,
209 X_segment15,
210 X_segment16,
211 X_segment17,
212 X_segment18,
213 X_segment19,
214 X_segment20);
215
216 -- Copy rows for MTL_ITEM_CATEGORIES
217
218 ENG_COPY_TABLE_ROWS_PKG.C_MTL_ITEM_CATEGORIES(X_org_id,
219 X_org_id,
220 X_eng_item_id,
221 X_mfg_item_id,
222 -1,
223 X_mfg_description,
224 X_ecn_name);
225
226 -- Copy rows for MTL_DESCR_ELEMENT_VALUES
227
228 ENG_COPY_TABLE_ROWS_PKG.C_MTL_DESCR_ELEMENT_VALUES(X_org_id,
229 X_org_id,
230 X_eng_item_id,
231 X_mfg_item_id,
232 -1,
233 X_mfg_description,
234 X_ecn_name);
235
236 -- Copy rows for MTL_RELATED_ITEMS
237
238 ENG_COPY_TABLE_ROWS_PKG.C_MTL_RELATED_ITEMS(X_org_id,
239 X_org_id,
240 X_eng_item_id,
241 X_mfg_item_id,
242 -1,
243 X_mfg_description,
244 X_ecn_name);
245
246 -- Copy rows for CST_ITEM_COSTS
247
248 ENG_COPY_TABLE_ROWS_PKG.C_CST_ITEM_COSTS(X_org_id,
249 X_org_id,
250 X_eng_item_id,
251 X_mfg_item_id,
252 -1,
253 X_mfg_description,
254 X_ecn_name);
255
256 -- Copy rows for CST_ITEM_COST_DETAILS
257
258 ENG_COPY_TABLE_ROWS_PKG.C_CST_ITEM_COST_DETAILS(X_org_id,
259 X_org_id,
260 X_eng_item_id,
261 X_mfg_item_id,
262 -1,
263 X_mfg_description,
264 X_ecn_name);
265
266 -- Copy rows for MTL_ITEM_SUB_INVENTORIES
267
268 ENG_COPY_TABLE_ROWS_PKG.C_MTL_ITEM_SUB_INVENTORIES(X_org_id,
269 X_org_id,
270 X_eng_item_id,
271 X_mfg_item_id,
272 -1,
273 X_mfg_description,
274 X_ecn_name);
275
276 -- Copy rows for MTL_SECONDARY_LOCATORS
277
278 ENG_COPY_TABLE_ROWS_PKG.C_MTL_SECONDARY_LOCATORS(X_org_id,
279 X_org_id,
280 X_eng_item_id,
281 X_mfg_item_id,
282 -1,
283 X_mfg_description,
284 X_ecn_name);
285
286 -- Copy rows for MTL_CROSS_REFERENCES
287
288 ENG_COPY_TABLE_ROWS_PKG.C_MTL_CROSS_REFERENCES(X_org_id,
289 X_org_id,
290 X_eng_item_id,
291 X_mfg_item_id,
292 -1,
293 X_mfg_description,
294 X_ecn_name);
295
296 -- Copy rows for MTL_PENDING_ITEM_STATUS
297
298 ENG_COPY_TABLE_ROWS_PKG.C_MTL_PENDING_ITEM_STATUS(X_org_id,
299 X_org_id,
300 X_eng_item_id,
301 X_mfg_item_id,
302 -1,
303 X_mfg_description,
304 X_ecn_name);
305
306 -- Copy rows for CST_STANDARD_COSTS
307
308 ENG_COPY_TABLE_ROWS_PKG.C_CST_STANDARD_COSTS(X_org_id,
309 X_org_id,
310 X_eng_item_id,
311 X_mfg_item_id,
312 -1,
313 X_mfg_description,
314 X_ecn_name);
315
316 -- Copy rows for CST_ELEMENTAL_COSTS
317
318 ENG_COPY_TABLE_ROWS_PKG.C_CST_ELEMENTAL_COSTS(X_org_id,
319 X_org_id,
320 X_eng_item_id,
321 X_mfg_item_id,
322 -1,
323 X_mfg_description,
324 X_ecn_name);
325
326 -- If item name has been changed, check if item exists in master
327 -- organization. If not, it needs to be created.
328
329 IF (X_master_org <> X_org_id) THEN
330
331 -- Copy rows for MTL_SYSTEM_ITEMS for the master org
332
333 ENG_COPY_TABLE_ROWS_PKG.C_MTL_SYSTEM_ITEMS(X_org_id,
334 X_master_org,
335 X_eng_item_id,
336 X_mfg_item_id,
337 -1,
338 X_mfg_description,
339 X_ecn_name,
340 X_segment1,
341 X_segment2,
342 X_segment3,
343 X_segment4,
344 X_segment5,
345 X_segment6,
346 X_segment7,
347 X_segment8,
348 X_segment9,
349 X_segment10,
350 X_segment11,
351 X_segment12,
352 X_segment13,
353 X_segment14,
354 X_segment15,
355 X_segment16,
356 X_segment17,
357 X_segment18,
358 X_segment19,
359 X_segment20);
360
361 -- Copy rows for each table that is org dependent for the item
362 -- in the master org
363
364 -- Copy rows for MTL_ITEM_CATEGORIES
365
366 ENG_COPY_TABLE_ROWS_PKG.C_MTL_ITEM_CATEGORIES(X_org_id,
367 X_master_org,
368 X_eng_item_id,
369 X_mfg_item_id,
370 -1,
371 X_mfg_description,
372 X_ecn_name);
373
374 -- CST_ITEM_COSTS
375
376 ENG_COPY_TABLE_ROWS_PKG.C_CST_ITEM_COSTS(X_org_id,
377 X_master_org,
378 X_eng_item_id,
379 X_mfg_item_id,
380 -1,
381 X_mfg_description,
382 X_ecn_name);
383 -- CST_ITEM_COST_DETAILS
384
385 ENG_COPY_TABLE_ROWS_PKG.C_CST_ITEM_COST_DETAILS(X_org_id,
386 X_master_org,
387 X_eng_item_id,
388 X_mfg_item_id,
389 -1,
390 X_mfg_description,
391 X_ecn_name);
392
393 -- MTL_ITEM_SUB_INVENTORIES
394
395 ENG_COPY_TABLE_ROWS_PKG.C_MTL_ITEM_SUB_INVENTORIES(X_org_id,
396 X_master_org,
397 X_eng_item_id,
398 X_mfg_item_id,
399 -1,
400 X_mfg_description,
401 X_ecn_name);
402
403 -- MTL_SECONDARY_LOCATORS
404
405 ENG_COPY_TABLE_ROWS_PKG.C_MTL_SECONDARY_LOCATORS(X_org_id,
406 X_master_org,
407 X_eng_item_id,
408 X_mfg_item_id,
409 -1,
410 X_mfg_description,
411 X_ecn_name);
412
413 -- MTL_CROSS_REFERENCES
414
415 ENG_COPY_TABLE_ROWS_PKG.C_MTL_CROSS_REFERENCES(X_org_id,
416 X_master_org,
417 X_eng_item_id,
418 X_mfg_item_id,
419 -1,
420 X_mfg_description,
421 X_ecn_name);
422
423 -- MTL_PENDING_ITEM_STATUS
424
425 ENG_COPY_TABLE_ROWS_PKG.C_MTL_PENDING_ITEM_STATUS(X_org_id,
426 X_master_org,
427 X_eng_item_id,
428 X_mfg_item_id,
429 -1,
430 X_mfg_description,
431 X_ecn_name);
432
433 -- CST_STANDARD_COSTS
434
435 ENG_COPY_TABLE_ROWS_PKG.C_CST_STANDARD_COSTS(X_org_id,
436 X_master_org,
437 X_eng_item_id,
438 X_mfg_item_id,
439 -1,
440 X_mfg_description,
441 X_ecn_name);
442
443 -- CST_ELEMENTAL_COSTS
444
445 ENG_COPY_TABLE_ROWS_PKG.C_CST_ELEMENTAL_COSTS(X_org_id,
446 X_master_org,
447 X_eng_item_id,
448 X_mfg_item_id,
449 -1,
450 X_mfg_description,
451 X_ecn_name);
452
453 IF (X_bom_rev_starting IS NOT NULL) THEN
454 ENG_COPY_TABLE_ROWS_PKG.C_MTL_ITEM_REVISIONS(X_inventory_item_id => X_mfg_item_id,
455 X_organization_id => X_master_org,
456 X_revision => X_bom_rev_starting,
457 X_last_update_date => SYSDATE,
458 X_last_updated_by => to_number(Fnd_Profile.Value('USER_ID')),
459 X_creation_date => SYSDATE,
460 X_created_by => to_number(Fnd_Profile.Value('USER_ID')),
461 X_last_update_login => to_number(Fnd_Profile.Value('LOGIN_ID')),
462 X_effectivity_date => SYSDATE,
463 X_change_notice => X_ecn_name,
464 X_implementation_date => SYSDATE);
465 END IF;
466
467 END IF; -- end of IF (X_master_org <> X_org_id) THEN
468
469 END IF; -- end of IF (X_eng_item_id <> X_mfg_item_id) THEN
470
471 -- Inserting the new revision in MTL_ITEM_REVISIONS.
472
473 IF (X_bom_rev_starting IS NOT NULL) THEN
474
475 ENG_COPY_TABLE_ROWS_PKG.C_MTL_ITEM_REVISIONS(X_inventory_item_id => X_mfg_item_id,
476 X_organization_id => X_org_id,
477 X_revision => X_bom_rev_starting,
478 X_last_update_date => SYSDATE,
479 X_last_updated_by => to_number(Fnd_Profile.Value('USER_ID')),
480 X_creation_date => SYSDATE,
481 X_created_by => to_number(Fnd_Profile.Value('USER_ID')),
482 X_last_update_login => to_number(Fnd_Profile.Value('LOGIN_ID')),
483 X_effectivity_date => SYSDATE,
484 X_change_notice => X_ecn_name,
485 X_implementation_date => SYSDATE);
486
487 END IF;
488
489 END ITEM_TRANSFER;
490
491 /***************************************************************************
492 * Function : Get_GTIN_Structure_Type_Id
493 * Returns : StructureTypeId of 'Packaging Hierarchy' / NULL
494 * Parameters IN : None
495 * Parameters OUT: None
496 * Purpose : To get the StructureTypeId of 'Packaging Hierarchy' if available
497 *****************************************************************************/
498 FUNCTION Get_GTIN_Structure_Type_Id RETURN NUMBER
499 IS
500 l_GTIN_Id NUMBER;
501 BEGIN
502 SELECT Structure_Type_Id
503 INTO l_GTIN_Id
504 FROM bom_structure_types_vl
505 WHERE Structure_Type_Name ='Packaging Hierarchy';
506
507 RETURN l_GTIN_Id;
508
509 EXCEPTION
510 WHEN NO_DATA_FOUND THEN
511 Return NULL;
512 END Get_GTIN_Structure_Type_Id;
513
514 -- +--------------------------- COMPONENT_TRANSFER ----------------------------+
515 -- NAME
516 -- COMPONENT_TRANSFER
517
518 -- DESCRIPTION
519 -- Transfer Components: Flip the eng_item_flag to 'N' for each component of
520 -- the bills.
521
522 -- REQUIRES
523 -- org_id: organization id
524 -- eng_item_id
525 -- designator_option
526 -- 1. all
527 -- 2. primary only
528 -- 3. specific only
529 -- alt_bom_designator
530
531 -- OUTPUT
532
533 -- NOTES
534
535 -- +--------------------------------------------------------------------------+
536
537 PROCEDURE COMPONENT_TRANSFER
538 (
539 X_org_id IN NUMBER,
540 X_eng_item_id IN NUMBER,
541 X_designator_option IN NUMBER,
542 X_alt_bom_designator IN VARCHAR2
543 )
544 IS
545 X_master_org NUMBER;
546 X_stmt_num NUMBER;
547 X_GTIN_ST_TYPE_ID NUMBER;
548
549 --BOM ER 9946990 begin
550
551 l_parent_BIT NUMBER;
552 l_PTO_flag varchar2(1);
553 ATO_IN_KIT_EXCEPTION EXCEPTION;
554 ATO_IN_MODEL_EXCEPTION EXCEPTION;
555
556 cursor all_bills(X_org_id number, X_eng_item_id number,
557 X_designator_option number, X_alt_bom_designator varchar2) is
558 select bill_sequence_id from bom_structures_b
559 WHERE ORGANIZATION_ID = X_org_id
560 AND ASSEMBLY_ITEM_ID = X_eng_item_id
561 AND ((X_designator_option = 2 AND
562 ALTERNATE_BOM_DESIGNATOR IS NULL)
563 OR
564 (X_designator_option = 3 AND
565 ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
566 OR
567 X_designator_option = 1)
568 AND nvl(effectivity_control , 1) <> 4;
569
570
571
572 cursor comp_rows(bill_id IN NUMBER) is
573 select msib.bom_item_type, msib.replenish_to_order_flag, bcb.optional
574 from bom_components_b bcb, mtl_system_items_b msib
575 where bcb.bill_sequence_id = bill_id
576 and msib.inventory_item_id = bcb.component_item_id
577 and msib.organization_id = bcb.pk2_value;
578
579 --BOM ER 9946990 end
580
581 BEGIN
582
583 --BOM ER 9946990 begin
584
585 X_stmt_num := 198;
586
587 BEGIN
588
589 select msib.bom_item_type, msib.pick_components_flag into l_parent_BIT, l_PTO_flag
590 from mtl_system_items_b msib where inventory_item_id = X_eng_item_id and organization_id = X_org_id;
591
592 for bill in all_bills(X_org_id, X_eng_item_id,
593 X_designator_option, X_alt_bom_designator) loop
594
595 for comp in comp_rows(bill.bill_sequence_id) loop
596 if l_parent_BIT = 4 and l_PTO_flag = 'Y'
597 and comp.bom_item_type = 4 and comp.replenish_to_order_flag = 'Y'
598 and nvl(FND_PROFILE.value('BOM:MANDATORY_ATO_IN_PTO'), 2) <> 1
599 then
600 raise ATO_IN_KIT_EXCEPTION;
601 end if;
602
603
604 if l_parent_BIT = 1 and l_PTO_flag = 'Y'
605 and comp.bom_item_type = 4 and comp.replenish_to_order_flag = 'Y' and nvl(comp.optional, 1) = 2
606 and nvl(FND_PROFILE.value('BOM:MANDATORY_ATO_IN_PTO'), 2) <> 1
607 then
608 raise ATO_IN_MODEL_EXCEPTION;
609 end if;
610
611 end loop;
612
613 end loop;
614
615
616 EXCEPTION
617
618 --BOM ER 9946990 begin
619 WHEN ATO_IN_KIT_EXCEPTION THEN
620 rollback;
621 FND_MESSAGE.SET_NAME('BOM', 'BOM_KIT_COMP_PRF_NOT_SET');
622 APP_EXCEPTION.RAISE_EXCEPTION;
623
624
625 WHEN ATO_IN_MODEL_EXCEPTION THEN
626 rollback;
627 FND_MESSAGE.SET_NAME('BOM', 'BOM_MODEL_COMP_PRF_NOT_SET');
628 APP_EXCEPTION.RAISE_EXCEPTION;
629
630 WHEN OTHERS THEN
631 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'COMPONENT_TRANSFER',
632 stmt_num => X_stmt_num,
633 message_name => 'ENG_ENUBRT_ERROR',
634 token => SQLERRM);
635
636
637 END;
638
639 --BOM ER 9946990 end
640
641 X_stmt_num := 199;
642
643 X_GTIN_ST_TYPE_ID := Get_GTIN_Structure_Type_Id;
644
645 BEGIN
646 UPDATE MTL_SYSTEM_ITEMS
647 SET ENG_ITEM_FLAG = 'N',
648 LAST_UPDATE_LOGIN = to_number(Fnd_Profile.Value('LOGIN_ID')), /* for bug 9868364 */
649 LAST_UPDATED_BY = to_number(Fnd_Profile.Value('USER_ID')), /* for bug 9868364 */
650 LAST_UPDATE_DATE = SYSDATE /* for bug 9701210 */
651 WHERE ORGANIZATION_ID = X_org_id
652 AND ENG_ITEM_FLAG <> 'N'
653 AND INVENTORY_ITEM_ID IN
654 (SELECT BIC.COMPONENT_ITEM_ID
655 FROM BOM_INVENTORY_COMPONENTS BIC,
656 BOM_BILL_OF_MATERIALS BOM
657 WHERE BOM.ORGANIZATION_ID = X_org_id
658 AND BOM.ASSEMBLY_ITEM_ID = X_eng_item_id
659 AND ((X_designator_option = 2 AND
660 BOM.ALTERNATE_BOM_DESIGNATOR IS NULL)
661 OR (X_designator_option = 3 AND
662 BOM.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
663 OR (X_designator_option = 1))
664 AND (X_GTIN_ST_TYPE_ID IS NULL or STRUCTURE_TYPE_ID <> X_GTIN_ST_TYPE_ID)
665 AND nvl(bom.effectivity_control, 1) <> 4 -- Bug 4210718
666 AND BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID);
667
668
669 EXCEPTION
670 WHEN OTHERS THEN
671 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'COMPONENT_TRANSFER',
672 stmt_num => X_stmt_num,
673 message_name => 'ENG_ENUBRT_ERROR',
674 token => SQLERRM);
675 END;
676
677 /* The following SQL is added to fix Bug 1799242 */
678 -- Transfer Sub Components: Flip the eng_item_flag to 'N' for each
679 -- of the Substitute component for all the Components of
680 -- the bills.
681
682 X_stmt_num := 200;
683
684 BEGIN
685 UPDATE MTL_SYSTEM_ITEMS
686 SET ENG_ITEM_FLAG = 'N',
687 LAST_UPDATE_LOGIN = to_number(Fnd_Profile.Value('LOGIN_ID')), /* for bug 9868364 */
688 LAST_UPDATED_BY = to_number(Fnd_Profile.Value('USER_ID')), /* for bug 9868364 */
689 LAST_UPDATE_DATE = SYSDATE /* for bug 9701210 */
690 WHERE ORGANIZATION_ID = X_org_id
691 AND ENG_ITEM_FLAG <> 'N'
692 AND INVENTORY_ITEM_ID IN
693 (select BSC.SUBSTITUTE_COMPONENT_ID
694 from BOM_SUBSTITUTE_COMPONENTS BSC,
695 BOM_INVENTORY_COMPONENTS BIC,
696 BOM_BILL_OF_MATERIALS BOM
697 WHERE
698 BSC.COMPONENT_SEQUENCE_ID = BIC.COMPONENT_SEQUENCE_ID
699 AND BOM.ORGANIZATION_ID = X_org_id
700 AND BOM.ASSEMBLY_ITEM_ID = X_eng_item_id
701 AND ((X_designator_option = 2 AND
702 BOM.ALTERNATE_BOM_DESIGNATOR IS NULL)
703 OR (X_designator_option = 3 AND
704 BOM.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
705 OR (X_designator_option = 1))
706 AND (X_GTIN_ST_TYPE_ID IS NULL or STRUCTURE_TYPE_ID <> X_GTIN_ST_TYPE_ID)
707 AND nvl(bom.effectivity_control, 1) <> 4 -- Bug 4210718
708 AND BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID);
709
710
711 EXCEPTION
712
713 WHEN OTHERS THEN
714 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'COMPONENT_TRANSFER',
715 stmt_num => X_stmt_num,
716 message_name => 'ENG_ENUBRT_ERROR',
717 token => SQLERRM);
718 END;
719
720 /* Changes for 1799242 end here */
721
722 -- The following code included to trasfer master org item when
723 -- transferring an item from eng to mfg. Bug #709403.
724 X_stmt_num := 201;
725 BEGIN
726 SELECT MASTER_ORGANIZATION_ID
727 INTO X_master_org
728 FROM MTL_PARAMETERS
729 WHERE ORGANIZATION_ID = X_org_id;
730 EXCEPTION
731 WHEN NO_DATA_FOUND THEN
732 X_master_org := X_org_id;
733 WHEN OTHERS THEN
734 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'COMPONENT_TRANSFER',
735 stmt_num => X_stmt_num,
736 message_name => 'ENG_ENUBRT_ERROR',
737 token => SQLERRM);
738 END;
739
740 IF (X_master_org <> X_org_id) THEN
741 X_stmt_num := 200;
742 BEGIN
743 UPDATE MTL_SYSTEM_ITEMS
744 SET ENG_ITEM_FLAG = 'N',
745 LAST_UPDATE_LOGIN = to_number(Fnd_Profile.Value('LOGIN_ID')), /* for bug 9868364 */
746 LAST_UPDATED_BY = to_number(Fnd_Profile.Value('USER_ID')), /* for bug 9868364 */
747 LAST_UPDATE_DATE = SYSDATE /* for bug 9701210 */
748 WHERE ORGANIZATION_ID = X_master_org
749 AND ENG_ITEM_FLAG <> 'N'
750 AND INVENTORY_ITEM_ID IN
751 (SELECT BIC.COMPONENT_ITEM_ID
752 FROM BOM_INVENTORY_COMPONENTS BIC,
753 BOM_BILL_OF_MATERIALS BOM
754 WHERE BOM.ORGANIZATION_ID = X_org_id
755 AND BOM.ASSEMBLY_ITEM_ID = X_eng_item_id
756 AND ((X_designator_option = 2 AND
757 BOM.ALTERNATE_BOM_DESIGNATOR IS NULL)
758 OR (X_designator_option = 3 AND
759 BOM.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
760 OR (X_designator_option = 1))
761 AND (X_GTIN_ST_TYPE_ID IS NULL or STRUCTURE_TYPE_ID <> X_GTIN_ST_TYPE_ID)
762 AND nvl(bom.effectivity_control, 1) <> 4 -- Bug 4210718
763 AND BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID);
764
765
766 EXCEPTION
767 WHEN OTHERS THEN
768 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'COMPONENT_TRANSFER',
769 stmt_num => X_stmt_num,
770 message_name => 'ENG_ENUBRT_ERROR',
771 token => SQLERRM);
772 END;
773 END IF;
774 END COMPONENT_TRANSFER;
775
776 -- +--------------------------- SET_OP_SEQ -----------------------------------+
777 -- NAME
778 -- SET_OP_SEQ
779
780 -- DESCRIPTION
781 -- Set Operation Sequence: Set operation_seq_num to 1 in table
782 -- BOM_INVENTORY_COMPONENTS where there is no
783 -- corresponding manufacturing routing.
784
785 -- REQUIRES
786 -- org_id: organization id
787 -- item_id: item to be updated
788 -- designator_option
789 -- 1. all
790 -- 2. primary only
791 -- 3. specific only
792 -- alt_bom_designator
793
794 -- OUTPUT
795
796 -- NOTES
797
798 -- +--------------------------------------------------------------------------+
799
800 PROCEDURE SET_OP_SEQ
801 (
802 X_org_id IN NUMBER,
803 X_item_id IN NUMBER,
804 X_designator_option IN NUMBER,
805 X_alt_bom_designator IN VARCHAR2
806 )
807 IS
808 DUMMY NUMBER DEFAULT 0;
809 X_stmt_num NUMBER;
810 X_unit_assembly VARCHAR2(2) := 'N';
811
812 l_primary_rtg_sequence_id NUMBER;
813 l_primary_rtg_exists NUMBER;
814 l_routing_sequence_id NUMBER;
815 l_routing_type NUMBER;
816 l_primary_rtg_type NUMBER;
817
818 CURSOR c_transfer_bills IS
819 SELECT *
820 FROM BOM_BILL_OF_MATERIALS BOM
821 WHERE BOM.ORGANIZATION_ID = X_org_id
822 AND BOM.ASSEMBLY_ITEM_ID = X_item_id
823 AND nvl(bom.effectivity_control, 1) <> 4 -- Bug 4210718
824 AND ((X_designator_option = 2 AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL)
825 OR (X_designator_option = 3 AND BOM.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
826 OR (X_designator_option = 1));
827
828
829 BEGIN
830
831 X_stmt_num := 300;
832 -- Changes for bug 3801212
833 -- Need to update the operation_seq_num in bom_inventory_components conditionally
834 -- Case A: Primary bill is transferred
835 -- 1) Primary routing_type = 1 (mfg): No update required
836 -- 2) Primary routing_type = 2 (eng): update required
837 -- Case B: Alternate bill is transferred
838 -- Alternate routing exists
839 -- 1) Alternate routing_type = 1 (mfg): No update required
840 -- 2) Alternate routing_type = 2 (eng): update required
841 -- Alternate routing does not exist
842 -- 1) Primary routing_type = 1 (mfg): No update required
843 -- 2) Primary routing_type = 2 (eng): update required
844 l_primary_rtg_sequence_id := -1;
845 l_primary_rtg_type := -1;
846 l_primary_rtg_exists := 1;
847
848 FOR ctb IN c_transfer_bills
849 LOOP
850 l_routing_sequence_id := -1;
851 l_routing_type := -1;
852 -- Step 1: Fetch the routing sequence id
853 BEGIN
854 IF ctb.ALTERNATE_BOM_DESIGNATOR IS NOT NULL AND l_primary_rtg_exists = 1
855 THEN
856 BEGIN
857 X_stmt_num := 301;
858 SELECT BOR.COMMON_ROUTING_SEQUENCE_ID, BOR.ROUTING_TYPE
859 INTO l_routing_sequence_id, l_routing_type
860 FROM BOM_OPERATIONAL_ROUTINGS BOR
861 WHERE BOR.ASSEMBLY_ITEM_ID = ctb.ASSEMBLY_ITEM_ID
862 AND BOR.ORGANIZATION_ID = ctb.ORGANIZATION_ID
863 AND BOR.ALTERNATE_ROUTING_DESIGNATOR = ctb.ALTERNATE_BOM_DESIGNATOR;
864 EXCEPTION
865 WHEN NO_DATA_FOUND THEN
866 null;
867 END;
868 END IF;
869 IF (l_routing_sequence_id = -1
870 AND l_primary_rtg_exists = 1
871 AND (l_primary_rtg_sequence_id = -1 OR l_primary_rtg_type <> 1))
872 THEN
873 BEGIN
874 X_stmt_num := 302;
875 SELECT BOR.COMMON_ROUTING_SEQUENCE_ID, BOR.ROUTING_TYPE
876 INTO l_routing_sequence_id, l_routing_type
877 FROM BOM_OPERATIONAL_ROUTINGS BOR
878 WHERE BOR.ASSEMBLY_ITEM_ID = ctb.ASSEMBLY_ITEM_ID
879 AND BOR.ORGANIZATION_ID = ctb.ORGANIZATION_ID
880 AND BOR.ALTERNATE_ROUTING_DESIGNATOR IS NULL;
881
882 l_primary_rtg_sequence_id := l_routing_sequence_id;
883 l_primary_rtg_type := l_routing_type;
884 EXCEPTION
885 WHEN NO_DATA_FOUND THEN
886 l_primary_rtg_exists := 2;
887 END;
888 ELSIF (l_routing_sequence_id = -1 AND l_primary_rtg_sequence_id <> -1 AND l_primary_rtg_type = 1)
889 THEN
890 l_routing_sequence_id := l_primary_rtg_sequence_id;
891 l_routing_type := l_primary_rtg_type;
892 END IF;
893 IF (l_routing_type <> 1 AND l_routing_sequence_id <> -1)
894 THEN
895 l_routing_sequence_id := -1;
896 END IF;
897
898 EXCEPTION
899 WHEN OTHERS THEN
900 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'SET_OP_SEQ',
901 stmt_num => X_stmt_num,
902 message_name => 'ENG_ENUBRT_ERROR',
903 token => SQLERRM);
904 END;
905 -- Step 2: Update OPERATION_SEQ_NUM Accordingly
906 BEGIN
907 IF ( l_routing_sequence_id = -1 )
908 THEN
909 X_stmt_num := 303;
910 UPDATE BOM_INVENTORY_COMPONENTS
911 SET OPERATION_SEQ_NUM = 1
912 WHERE BILL_SEQUENCE_ID = ctb.BILL_SEQUENCE_ID;
913 ELSE
914 X_stmt_num := 304;
915 UPDATE BOM_INVENTORY_COMPONENTS BIC
916 SET BIC.OPERATION_SEQ_NUM = 1
917 WHERE BIC.BILL_SEQUENCE_ID = ctb.BILL_SEQUENCE_ID
918 AND NOT EXISTS (SELECT NULL
919 FROM BOM_OPERATION_SEQUENCES BOS
920 WHERE ROUTING_SEQUENCE_ID = l_routing_sequence_id
921 AND BOS.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM);
922 END IF;
923 EXCEPTION
924 WHEN DUP_VAL_ON_INDEX THEN
925 FND_MESSAGE.SET_NAME('ENG', 'ENG_COMP_OP_COMBINATION');
926 APP_EXCEPTION.RAISE_EXCEPTION;
927 WHEN OTHERS THEN
928 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'SET_OP_SEQ',
929 stmt_num => X_stmt_num,
930 message_name => 'ENG_ENUBRT_ERROR',
931 token => SQLERRM);
932 END;
933
934 IF (PJM_UNIT_EFF.Enabled = 'Y' AND
935 PJM_UNIT_EFF.Unit_Effective_Item(
936 X_Item_ID => X_item_id,
937 X_Organization_ID => X_org_id) = 'Y')
938 THEN
939 X_unit_assembly := 'Y';
940 ELSE
941 X_unit_assembly := 'N';
942 END IF;
943 -- Step 3: Validate that there are no overlapping components created by result of the above update
944 BEGIN
945 X_stmt_num := 305;
946 SELECT count(*)
947 INTO DUMMY
948 FROM BOM_INVENTORY_COMPONENTS BIC
949 WHERE BIC.BILL_SEQUENCE_ID = ctb.BILL_SEQUENCE_ID
950 AND EXISTS
951 (SELECT NULL
952 FROM BOM_INVENTORY_COMPONENTS BIC2
953 WHERE BIC2.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
954 AND BIC2.COMPONENT_ITEM_ID = BIC.COMPONENT_ITEM_ID
955 AND BIC2.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM
956 AND NVL(BIC2.ECO_FOR_PRODUCTION,2) = 2
957 AND BIC2.COMPONENT_SEQUENCE_ID <> BIC.COMPONENT_SEQUENCE_ID
958 AND NVL(BIC2.OLD_COMPONENT_SEQUENCE_ID, BIC2.COMPONENT_SEQUENCE_ID)
959 <> BIC.COMPONENT_SEQUENCE_ID
960 AND ((X_unit_assembly = 'Y'
961 AND BIC2.DISABLE_DATE IS NULL
962 AND (BIC.TO_END_ITEM_UNIT_NUMBER IS NULL
963 OR BIC.TO_END_ITEM_UNIT_NUMBER >= BIC2.FROM_END_ITEM_UNIT_NUMBER)
964 AND (BIC2.TO_END_ITEM_UNIT_NUMBER IS NULL
965 OR BIC.FROM_END_ITEM_UNIT_NUMBER <= BIC2.TO_END_ITEM_UNIT_NUMBER))
966 OR (X_unit_assembly = 'N'
967 AND BIC2.EFFECTIVITY_DATE BETWEEN BIC.EFFECTIVITY_DATE
968 AND NVL(BIC.DISABLE_DATE - 1, BIC2.EFFECTIVITY_DATE + 1)))
969 );
970 EXCEPTION
971 WHEN OTHERS THEN
972 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'SET_OP_SEQ',
973 stmt_num => X_stmt_num,
974 message_name => 'ENG_ENUBRT_ERROR',
975 token => SQLERRM);
976 END;
977 IF (DUMMY <> 0)
978 THEN
979 IF (X_unit_assembly = 'Y')
980 THEN
981 FND_MESSAGE.SET_NAME('BOM', 'BOM_UNIT_OVERLAP');
982 ELSE
983 FND_MESSAGE.SET_NAME('ENG', 'ENG_COMP_OP_COMBINATION');
984 END IF;
985 APP_EXCEPTION.RAISE_EXCEPTION;
986 END IF;
987 END LOOP;
988 /*
989 BEGIN
990 UPDATE BOM_INVENTORY_COMPONENTS BIC
991 SET OPERATION_SEQ_NUM = 1
992 WHERE NOT EXISTS
993 (SELECT 'X'
994 FROM BOM_OPERATIONAL_ROUTINGS BOR,
995 BOM_BILL_OF_MATERIALS BOM,
996 BOM_OPERATION_SEQUENCES BOS
997 WHERE BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
998 AND BOR.ROUTING_TYPE = 1
999 AND BOM.ASSEMBLY_ITEM_ID = BOR.ASSEMBLY_ITEM_ID
1000 AND BOM.ORGANIZATION_ID = BOR.ORGANIZATION_ID
1001 AND NVL(BOM.ALTERNATE_BOM_DESIGNATOR, 'NONE') =
1002 NVL(BOR.ALTERNATE_ROUTING_DESIGNATOR, 'NONE')
1003 AND BOR.COMMON_ROUTING_SEQUENCE_ID = BOS.ROUTING_SEQUENCE_ID
1004 AND BIC.OPERATION_SEQ_NUM = BOS.OPERATION_SEQ_NUM)
1005 AND BIC.BILL_SEQUENCE_ID IN
1006 (SELECT BOM2.BILL_SEQUENCE_ID
1007 FROM BOM_BILL_OF_MATERIALS BOM2
1008 WHERE BOM2.ORGANIZATION_ID = X_org_id
1009 AND BOM2.ASSEMBLY_ITEM_ID = X_item_id
1010 AND ((X_designator_option = 2 AND
1011 BOM2.ALTERNATE_BOM_DESIGNATOR IS NULL)
1012 OR
1013 (X_designator_option = 3 AND
1014 BOM2.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
1015 OR
1016 (X_designator_option = 1)));
1017 EXCEPTION
1018 WHEN OTHERS THEN
1019 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'SET_OP_SEQ',
1020 stmt_num => X_stmt_num,
1021 message_name => 'ENG_ENUBRT_ERROR',
1022 token => SQLERRM);
1023 END;
1024
1025 -- if found means the update caused an overlapping component effectivity
1026 -- date, then transfer will fail
1027
1028 X_stmt_num := 301;
1029
1030 IF (PJM_UNIT_EFF.Enabled = 'Y' AND
1031 PJM_UNIT_EFF.Unit_Effective_Item(
1032 X_Item_ID => X_item_id,
1033 X_Organization_ID => X_org_id) = 'Y')
1034 THEN
1035 X_unit_assembly := 'Y';
1036 ELSE
1037 X_unit_assembly := 'N';
1038 END IF;
1039
1040 SELECT count(*)
1041 INTO DUMMY
1042 FROM BOM_INVENTORY_COMPONENTS BIC
1043 WHERE BIC.BILL_SEQUENCE_ID IN
1044 (SELECT BOM.BILL_SEQUENCE_ID
1045 FROM BOM_BILL_OF_MATERIALS BOM
1046 WHERE BOM.ORGANIZATION_ID = X_org_id
1047 AND BOM.ASSEMBLY_ITEM_ID = X_item_id
1048 AND ((X_designator_option = 2 AND
1049 BOM.ALTERNATE_BOM_DESIGNATOR IS NULL)
1050 OR
1051 (X_designator_option = 3 AND
1052 BOM.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
1053 OR
1054 (X_designator_option = 1)))
1055 AND EXISTS
1056 (SELECT NULL
1057 FROM BOM_INVENTORY_COMPONENTS BIC2
1058 WHERE BIC2.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
1059 AND BIC2.COMPONENT_ITEM_ID = BIC.COMPONENT_ITEM_ID
1060 AND BIC2.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM
1061 AND NVL(BIC2.ECO_FOR_PRODUCTION,2) = 2
1062 AND BIC2.COMPONENT_SEQUENCE_ID <> BIC.COMPONENT_SEQUENCE_ID
1063 AND NVL(BIC2.OLD_COMPONENT_SEQUENCE_ID, BIC2.COMPONENT_SEQUENCE_ID) <> BIC.COMPONENT_SEQUENCE_ID
1064 AND ((X_unit_assembly = 'Y'
1065 AND BIC2.DISABLE_DATE IS NULL
1066 AND (BIC.TO_END_ITEM_UNIT_NUMBER IS NULL
1067 OR BIC.TO_END_ITEM_UNIT_NUMBER >= BIC2.FROM_END_ITEM_UNIT_NUMBER)
1068 AND (BIC2.TO_END_ITEM_UNIT_NUMBER IS NULL
1069 OR BIC.FROM_END_ITEM_UNIT_NUMBER <= BIC2.TO_END_ITEM_UNIT_NUMBER))
1070 OR (X_unit_assembly = 'N'
1071 AND BIC2.EFFECTIVITY_DATE BETWEEN BIC.EFFECTIVITY_DATE AND
1072 NVL(BIC.DISABLE_DATE - 1, BIC2.EFFECTIVITY_DATE + 1))));
1073
1074 IF (DUMMY <> 0) THEN
1075 IF (X_unit_assembly = 'Y') THEN
1076 FND_MESSAGE.SET_NAME('BOM', 'BOM_UNIT_OVERLAP');
1077 ELSE
1078 FND_MESSAGE.SET_NAME('ENG', 'ENG_COMP_OP_COMBINATION');
1079 END IF;
1080 APP_EXCEPTION.RAISE_EXCEPTION;
1081 END IF;
1082 */
1083 END SET_OP_SEQ;
1084
1085 END ENG_ITEM_PKG;