[Home] [Help]
PACKAGE BODY: APPS.ENG_ITEM_PKG
Source
1 PACKAGE BODY ENG_ITEM_PKG AS
2 /* $Header: ENGPITRB.pls 120.1 2006/03/06 08:07:43 prgopala noship $ */
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 BEGIN
549
550 X_stmt_num := 199;
551
552 X_GTIN_ST_TYPE_ID := Get_GTIN_Structure_Type_Id;
553
554 BEGIN
555 UPDATE MTL_SYSTEM_ITEMS
556 SET ENG_ITEM_FLAG = 'N'
557 WHERE ORGANIZATION_ID = X_org_id
558 AND ENG_ITEM_FLAG <> 'N'
559 AND INVENTORY_ITEM_ID IN
560 (SELECT BIC.COMPONENT_ITEM_ID
561 FROM BOM_INVENTORY_COMPONENTS BIC,
562 BOM_BILL_OF_MATERIALS BOM
563 WHERE BOM.ORGANIZATION_ID = X_org_id
564 AND BOM.ASSEMBLY_ITEM_ID = X_eng_item_id
565 AND ((X_designator_option = 2 AND
566 BOM.ALTERNATE_BOM_DESIGNATOR IS NULL)
567 OR (X_designator_option = 3 AND
568 BOM.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
569 OR (X_designator_option = 1))
570 AND (X_GTIN_ST_TYPE_ID IS NULL or STRUCTURE_TYPE_ID <> X_GTIN_ST_TYPE_ID)
571 AND nvl(bom.effectivity_control, 1) <> 4 -- Bug 4210718
572 AND BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID);
573
574
575 EXCEPTION
576 WHEN OTHERS THEN
577 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'COMPONENT_TRANSFER',
578 stmt_num => X_stmt_num,
579 message_name => 'ENG_ENUBRT_ERROR',
580 token => SQLERRM);
581 END;
582
583 /* The following SQL is added to fix Bug 1799242 */
584 -- Transfer Sub Components: Flip the eng_item_flag to 'N' for each
585 -- of the Substitute component for all the Components of
586 -- the bills.
587
588 X_stmt_num := 200;
589
590 BEGIN
591 UPDATE MTL_SYSTEM_ITEMS
592 SET ENG_ITEM_FLAG = 'N'
593 WHERE ORGANIZATION_ID = X_org_id
594 AND ENG_ITEM_FLAG <> 'N'
595 AND INVENTORY_ITEM_ID IN
596 (select BSC.SUBSTITUTE_COMPONENT_ID
597 from BOM_SUBSTITUTE_COMPONENTS BSC,
598 BOM_INVENTORY_COMPONENTS BIC,
599 BOM_BILL_OF_MATERIALS BOM
600 WHERE
601 BSC.COMPONENT_SEQUENCE_ID = BIC.COMPONENT_SEQUENCE_ID
602 AND BOM.ORGANIZATION_ID = X_org_id
603 AND BOM.ASSEMBLY_ITEM_ID = X_eng_item_id
604 AND ((X_designator_option = 2 AND
605 BOM.ALTERNATE_BOM_DESIGNATOR IS NULL)
606 OR (X_designator_option = 3 AND
607 BOM.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
608 OR (X_designator_option = 1))
609 AND (X_GTIN_ST_TYPE_ID IS NULL or STRUCTURE_TYPE_ID <> X_GTIN_ST_TYPE_ID)
610 AND nvl(bom.effectivity_control, 1) <> 4 -- Bug 4210718
611 AND BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID);
612
613
614 EXCEPTION
615 WHEN OTHERS THEN
616 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'COMPONENT_TRANSFER',
617 stmt_num => X_stmt_num,
618 message_name => 'ENG_ENUBRT_ERROR',
619 token => SQLERRM);
620 END;
621
622 /* Changes for 1799242 end here */
623
624 -- The following code included to trasfer master org item when
625 -- transferring an item from eng to mfg. Bug #709403.
626 X_stmt_num := 201;
627 BEGIN
628 SELECT MASTER_ORGANIZATION_ID
629 INTO X_master_org
630 FROM MTL_PARAMETERS
631 WHERE ORGANIZATION_ID = X_org_id;
632 EXCEPTION
633 WHEN NO_DATA_FOUND THEN
634 X_master_org := X_org_id;
635 WHEN OTHERS THEN
636 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'COMPONENT_TRANSFER',
637 stmt_num => X_stmt_num,
638 message_name => 'ENG_ENUBRT_ERROR',
639 token => SQLERRM);
640 END;
641
642 IF (X_master_org <> X_org_id) THEN
643 X_stmt_num := 200;
644 BEGIN
645 UPDATE MTL_SYSTEM_ITEMS
646 SET ENG_ITEM_FLAG = 'N'
647 WHERE ORGANIZATION_ID = X_master_org
648 AND ENG_ITEM_FLAG <> 'N'
649 AND INVENTORY_ITEM_ID IN
650 (SELECT BIC.COMPONENT_ITEM_ID
651 FROM BOM_INVENTORY_COMPONENTS BIC,
652 BOM_BILL_OF_MATERIALS BOM
653 WHERE BOM.ORGANIZATION_ID = X_org_id
654 AND BOM.ASSEMBLY_ITEM_ID = X_eng_item_id
655 AND ((X_designator_option = 2 AND
656 BOM.ALTERNATE_BOM_DESIGNATOR IS NULL)
657 OR (X_designator_option = 3 AND
658 BOM.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
659 OR (X_designator_option = 1))
660 AND (X_GTIN_ST_TYPE_ID IS NULL or STRUCTURE_TYPE_ID <> X_GTIN_ST_TYPE_ID)
661 AND nvl(bom.effectivity_control, 1) <> 4 -- Bug 4210718
662 AND BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID);
663
664
665 EXCEPTION
666 WHEN OTHERS THEN
667 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'COMPONENT_TRANSFER',
668 stmt_num => X_stmt_num,
669 message_name => 'ENG_ENUBRT_ERROR',
670 token => SQLERRM);
671 END;
672 END IF;
673 END COMPONENT_TRANSFER;
674
675 -- +--------------------------- SET_OP_SEQ -----------------------------------+
676 -- NAME
677 -- SET_OP_SEQ
678
679 -- DESCRIPTION
680 -- Set Operation Sequence: Set operation_seq_num to 1 in table
681 -- BOM_INVENTORY_COMPONENTS where there is no
682 -- corresponding manufacturing routing.
683
684 -- REQUIRES
685 -- org_id: organization id
686 -- item_id: item to be updated
687 -- designator_option
688 -- 1. all
689 -- 2. primary only
690 -- 3. specific only
691 -- alt_bom_designator
692
693 -- OUTPUT
694
695 -- NOTES
696
697 -- +--------------------------------------------------------------------------+
698
699 PROCEDURE SET_OP_SEQ
700 (
701 X_org_id IN NUMBER,
702 X_item_id IN NUMBER,
703 X_designator_option IN NUMBER,
704 X_alt_bom_designator IN VARCHAR2
705 )
706 IS
707 DUMMY NUMBER DEFAULT 0;
708 X_stmt_num NUMBER;
709 X_unit_assembly VARCHAR2(2) := 'N';
710
711 l_primary_rtg_sequence_id NUMBER;
712 l_primary_rtg_exists NUMBER;
713 l_routing_sequence_id NUMBER;
714 l_routing_type NUMBER;
715 l_primary_rtg_type NUMBER;
716
717 CURSOR c_transfer_bills IS
718 SELECT *
719 FROM BOM_BILL_OF_MATERIALS BOM
720 WHERE BOM.ORGANIZATION_ID = X_org_id
721 AND BOM.ASSEMBLY_ITEM_ID = X_item_id
722 AND nvl(bom.effectivity_control, 1) <> 4 -- Bug 4210718
723 AND ((X_designator_option = 2 AND BOM.ALTERNATE_BOM_DESIGNATOR IS NULL)
724 OR (X_designator_option = 3 AND BOM.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
725 OR (X_designator_option = 1));
726
727
728 BEGIN
729
730 X_stmt_num := 300;
731 -- Changes for bug 3801212
732 -- Need to update the operation_seq_num in bom_inventory_components conditionally
733 -- Case A: Primary bill is transferred
734 -- 1) Primary routing_type = 1 (mfg): No update required
735 -- 2) Primary routing_type = 2 (eng): update required
736 -- Case B: Alternate bill is transferred
737 -- Alternate routing exists
738 -- 1) Alternate routing_type = 1 (mfg): No update required
739 -- 2) Alternate routing_type = 2 (eng): update required
740 -- Alternate routing does not exist
741 -- 1) Primary routing_type = 1 (mfg): No update required
742 -- 2) Primary routing_type = 2 (eng): update required
743 l_primary_rtg_sequence_id := -1;
744 l_primary_rtg_type := -1;
745 l_primary_rtg_exists := 1;
746
747 FOR ctb IN c_transfer_bills
748 LOOP
749 l_routing_sequence_id := -1;
750 l_routing_type := -1;
751 -- Step 1: Fetch the routing sequence id
752 BEGIN
753 IF ctb.ALTERNATE_BOM_DESIGNATOR IS NOT NULL AND l_primary_rtg_exists = 1
754 THEN
755 BEGIN
756 X_stmt_num := 301;
757 SELECT BOR.COMMON_ROUTING_SEQUENCE_ID, BOR.ROUTING_TYPE
758 INTO l_routing_sequence_id, l_routing_type
759 FROM BOM_OPERATIONAL_ROUTINGS BOR
760 WHERE BOR.ASSEMBLY_ITEM_ID = ctb.ASSEMBLY_ITEM_ID
761 AND BOR.ORGANIZATION_ID = ctb.ORGANIZATION_ID
762 AND BOR.ALTERNATE_ROUTING_DESIGNATOR = ctb.ALTERNATE_BOM_DESIGNATOR;
763 EXCEPTION
764 WHEN NO_DATA_FOUND THEN
765 null;
766 END;
767 END IF;
768 IF (l_routing_sequence_id = -1
769 AND l_primary_rtg_exists = 1
770 AND (l_primary_rtg_sequence_id = -1 OR l_primary_rtg_type <> 1))
771 THEN
772 BEGIN
773 X_stmt_num := 302;
774 SELECT BOR.COMMON_ROUTING_SEQUENCE_ID, BOR.ROUTING_TYPE
775 INTO l_routing_sequence_id, l_routing_type
776 FROM BOM_OPERATIONAL_ROUTINGS BOR
777 WHERE BOR.ASSEMBLY_ITEM_ID = ctb.ASSEMBLY_ITEM_ID
778 AND BOR.ORGANIZATION_ID = ctb.ORGANIZATION_ID
779 AND BOR.ALTERNATE_ROUTING_DESIGNATOR IS NULL;
780
781 l_primary_rtg_sequence_id := l_routing_sequence_id;
782 l_primary_rtg_type := l_routing_type;
783 EXCEPTION
784 WHEN NO_DATA_FOUND THEN
785 l_primary_rtg_exists := 2;
786 END;
787 ELSIF (l_routing_sequence_id = -1 AND l_primary_rtg_sequence_id <> -1 AND l_primary_rtg_type = 1)
788 THEN
789 l_routing_sequence_id := l_primary_rtg_sequence_id;
790 l_routing_type := l_primary_rtg_type;
791 END IF;
792 IF (l_routing_type <> 1 AND l_routing_sequence_id <> -1)
793 THEN
794 l_routing_sequence_id := -1;
795 END IF;
796
797 EXCEPTION
798 WHEN OTHERS THEN
799 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'SET_OP_SEQ',
800 stmt_num => X_stmt_num,
801 message_name => 'ENG_ENUBRT_ERROR',
802 token => SQLERRM);
803 END;
804 -- Step 2: Update OPERATION_SEQ_NUM Accordingly
805 BEGIN
806 IF ( l_routing_sequence_id = -1 )
807 THEN
808 X_stmt_num := 303;
809 UPDATE BOM_INVENTORY_COMPONENTS
810 SET OPERATION_SEQ_NUM = 1
811 WHERE BILL_SEQUENCE_ID = ctb.BILL_SEQUENCE_ID;
812 ELSE
813 X_stmt_num := 304;
814 UPDATE BOM_INVENTORY_COMPONENTS BIC
815 SET BIC.OPERATION_SEQ_NUM = 1
816 WHERE BIC.BILL_SEQUENCE_ID = ctb.BILL_SEQUENCE_ID
817 AND NOT EXISTS (SELECT NULL
818 FROM BOM_OPERATION_SEQUENCES BOS
819 WHERE ROUTING_SEQUENCE_ID = l_routing_sequence_id
820 AND BOS.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM);
821 END IF;
822 EXCEPTION
823 WHEN DUP_VAL_ON_INDEX THEN
824 FND_MESSAGE.SET_NAME('ENG', 'ENG_COMP_OP_COMBINATION');
825 APP_EXCEPTION.RAISE_EXCEPTION;
826 WHEN OTHERS THEN
827 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'SET_OP_SEQ',
828 stmt_num => X_stmt_num,
829 message_name => 'ENG_ENUBRT_ERROR',
830 token => SQLERRM);
831 END;
832
833 IF (PJM_UNIT_EFF.Enabled = 'Y' AND
834 PJM_UNIT_EFF.Unit_Effective_Item(
835 X_Item_ID => X_item_id,
836 X_Organization_ID => X_org_id) = 'Y')
837 THEN
838 X_unit_assembly := 'Y';
839 ELSE
840 X_unit_assembly := 'N';
841 END IF;
842 -- Step 3: Validate that there are no overlapping components created by result of the above update
843 BEGIN
844 X_stmt_num := 305;
845 SELECT count(*)
846 INTO DUMMY
847 FROM BOM_INVENTORY_COMPONENTS BIC
848 WHERE BIC.BILL_SEQUENCE_ID = ctb.BILL_SEQUENCE_ID
849 AND EXISTS
850 (SELECT NULL
851 FROM BOM_INVENTORY_COMPONENTS BIC2
852 WHERE BIC2.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
853 AND BIC2.COMPONENT_ITEM_ID = BIC.COMPONENT_ITEM_ID
854 AND BIC2.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM
855 AND NVL(BIC2.ECO_FOR_PRODUCTION,2) = 2
856 AND BIC2.COMPONENT_SEQUENCE_ID <> BIC.COMPONENT_SEQUENCE_ID
857 AND NVL(BIC2.OLD_COMPONENT_SEQUENCE_ID, BIC2.COMPONENT_SEQUENCE_ID)
858 <> BIC.COMPONENT_SEQUENCE_ID
859 AND ((X_unit_assembly = 'Y'
860 AND BIC2.DISABLE_DATE IS NULL
861 AND (BIC.TO_END_ITEM_UNIT_NUMBER IS NULL
862 OR BIC.TO_END_ITEM_UNIT_NUMBER >= BIC2.FROM_END_ITEM_UNIT_NUMBER)
863 AND (BIC2.TO_END_ITEM_UNIT_NUMBER IS NULL
864 OR BIC.FROM_END_ITEM_UNIT_NUMBER <= BIC2.TO_END_ITEM_UNIT_NUMBER))
865 OR (X_unit_assembly = 'N'
866 AND BIC2.EFFECTIVITY_DATE BETWEEN BIC.EFFECTIVITY_DATE
867 AND NVL(BIC.DISABLE_DATE - 1, BIC2.EFFECTIVITY_DATE + 1)))
868 );
869 EXCEPTION
870 WHEN OTHERS THEN
871 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'SET_OP_SEQ',
872 stmt_num => X_stmt_num,
873 message_name => 'ENG_ENUBRT_ERROR',
874 token => SQLERRM);
875 END;
876 IF (DUMMY <> 0)
877 THEN
878 IF (X_unit_assembly = 'Y')
879 THEN
880 FND_MESSAGE.SET_NAME('BOM', 'BOM_UNIT_OVERLAP');
881 ELSE
882 FND_MESSAGE.SET_NAME('ENG', 'ENG_COMP_OP_COMBINATION');
883 END IF;
884 APP_EXCEPTION.RAISE_EXCEPTION;
885 END IF;
886 END LOOP;
887 /*
888 BEGIN
889 UPDATE BOM_INVENTORY_COMPONENTS BIC
890 SET OPERATION_SEQ_NUM = 1
891 WHERE NOT EXISTS
892 (SELECT 'X'
893 FROM BOM_OPERATIONAL_ROUTINGS BOR,
894 BOM_BILL_OF_MATERIALS BOM,
895 BOM_OPERATION_SEQUENCES BOS
896 WHERE BIC.BILL_SEQUENCE_ID = BOM.BILL_SEQUENCE_ID
897 AND BOR.ROUTING_TYPE = 1
898 AND BOM.ASSEMBLY_ITEM_ID = BOR.ASSEMBLY_ITEM_ID
899 AND BOM.ORGANIZATION_ID = BOR.ORGANIZATION_ID
900 AND NVL(BOM.ALTERNATE_BOM_DESIGNATOR, 'NONE') =
901 NVL(BOR.ALTERNATE_ROUTING_DESIGNATOR, 'NONE')
902 AND BOR.COMMON_ROUTING_SEQUENCE_ID = BOS.ROUTING_SEQUENCE_ID
903 AND BIC.OPERATION_SEQ_NUM = BOS.OPERATION_SEQ_NUM)
904 AND BIC.BILL_SEQUENCE_ID IN
905 (SELECT BOM2.BILL_SEQUENCE_ID
906 FROM BOM_BILL_OF_MATERIALS BOM2
907 WHERE BOM2.ORGANIZATION_ID = X_org_id
908 AND BOM2.ASSEMBLY_ITEM_ID = X_item_id
909 AND ((X_designator_option = 2 AND
910 BOM2.ALTERNATE_BOM_DESIGNATOR IS NULL)
911 OR
912 (X_designator_option = 3 AND
913 BOM2.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
914 OR
915 (X_designator_option = 1)));
916 EXCEPTION
917 WHEN OTHERS THEN
918 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'SET_OP_SEQ',
919 stmt_num => X_stmt_num,
920 message_name => 'ENG_ENUBRT_ERROR',
921 token => SQLERRM);
922 END;
923
924 -- if found means the update caused an overlapping component effectivity
925 -- date, then transfer will fail
926
927 X_stmt_num := 301;
928
929 IF (PJM_UNIT_EFF.Enabled = 'Y' AND
930 PJM_UNIT_EFF.Unit_Effective_Item(
931 X_Item_ID => X_item_id,
932 X_Organization_ID => X_org_id) = 'Y')
933 THEN
934 X_unit_assembly := 'Y';
935 ELSE
936 X_unit_assembly := 'N';
937 END IF;
938
939 SELECT count(*)
940 INTO DUMMY
941 FROM BOM_INVENTORY_COMPONENTS BIC
942 WHERE BIC.BILL_SEQUENCE_ID IN
943 (SELECT BOM.BILL_SEQUENCE_ID
944 FROM BOM_BILL_OF_MATERIALS BOM
945 WHERE BOM.ORGANIZATION_ID = X_org_id
946 AND BOM.ASSEMBLY_ITEM_ID = X_item_id
947 AND ((X_designator_option = 2 AND
948 BOM.ALTERNATE_BOM_DESIGNATOR IS NULL)
949 OR
950 (X_designator_option = 3 AND
951 BOM.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
952 OR
953 (X_designator_option = 1)))
954 AND EXISTS
955 (SELECT NULL
956 FROM BOM_INVENTORY_COMPONENTS BIC2
957 WHERE BIC2.BILL_SEQUENCE_ID = BIC.BILL_SEQUENCE_ID
958 AND BIC2.COMPONENT_ITEM_ID = BIC.COMPONENT_ITEM_ID
959 AND BIC2.OPERATION_SEQ_NUM = BIC.OPERATION_SEQ_NUM
960 AND NVL(BIC2.ECO_FOR_PRODUCTION,2) = 2
961 AND BIC2.COMPONENT_SEQUENCE_ID <> BIC.COMPONENT_SEQUENCE_ID
962 AND NVL(BIC2.OLD_COMPONENT_SEQUENCE_ID, BIC2.COMPONENT_SEQUENCE_ID) <> BIC.COMPONENT_SEQUENCE_ID
963 AND ((X_unit_assembly = 'Y'
964 AND BIC2.DISABLE_DATE IS NULL
965 AND (BIC.TO_END_ITEM_UNIT_NUMBER IS NULL
966 OR BIC.TO_END_ITEM_UNIT_NUMBER >= BIC2.FROM_END_ITEM_UNIT_NUMBER)
967 AND (BIC2.TO_END_ITEM_UNIT_NUMBER IS NULL
968 OR BIC.FROM_END_ITEM_UNIT_NUMBER <= BIC2.TO_END_ITEM_UNIT_NUMBER))
969 OR (X_unit_assembly = 'N'
970 AND BIC2.EFFECTIVITY_DATE BETWEEN BIC.EFFECTIVITY_DATE AND
971 NVL(BIC.DISABLE_DATE - 1, BIC2.EFFECTIVITY_DATE + 1))));
972
973 IF (DUMMY <> 0) THEN
974 IF (X_unit_assembly = 'Y') THEN
975 FND_MESSAGE.SET_NAME('BOM', 'BOM_UNIT_OVERLAP');
976 ELSE
977 FND_MESSAGE.SET_NAME('ENG', 'ENG_COMP_OP_COMBINATION');
978 END IF;
979 APP_EXCEPTION.RAISE_EXCEPTION;
980 END IF;
981 */
982 END SET_OP_SEQ;
983
984 END ENG_ITEM_PKG;