[Home] [Help]
PACKAGE BODY: APPS.ENG_BOM_PKG
Source
1 PACKAGE BODY ENG_BOM_PKG AS
2 /* $Header: ENGPBTRB.pls 120.10 2011/06/23 04:06:40 qyou ship $ */
3
4 /***************************************************************************
5 * Function : Get_GTIN_Structure_Type_Id
6 * Returns : StructureTypeId of 'Packaging Hierarchy' / NULL
7 * Parameters IN : None
8 * Parameters OUT: None
9 * Purpose : To get the structure type id of 'Packaging Hierarchy' if available
10 *****************************************************************************/
11 FUNCTION Get_GTIN_Structure_Type_Id RETURN NUMBER
12 IS
13 l_GTIN_Id NUMBER;
14 BEGIN
15 SELECT Structure_Type_Id
16 INTO l_GTIN_Id
17 FROM bom_structure_types_vl
18 WHERE Structure_Type_Name ='Packaging Hierarchy';
19
20 RETURN l_GTIN_Id;
21
22 EXCEPTION
23 WHEN NO_DATA_FOUND THEN
24 Return NULL;
25 END Get_GTIN_Structure_Type_Id;
26
27 -- +------------------------------ BOM_UPDATE --------------------------------+
28 -- NAME
29 -- BOM_UPDATE
30
31 -- DESCRIPTION
32 -- Update Bills: Flip assembly_type to 1 (manufacturing)
33
34 -- REQUIRES
35 -- org_id: organization id
36 -- eng_item_id: bill that requires assembly_type to bee set to 1
37 -- designator_option
38 -- 1. all
39 -- 2. primary only
40 -- 3. specific only
41 -- alt_bom_designator
42
43 -- OUTPUT
44
45 -- NOTES
46
47 -- +--------------------------------------------------------------------------+
48
49 PROCEDURE BOM_UPDATE
50 (
51 X_org_id IN NUMBER,
52 X_eng_item_id IN NUMBER,
53 X_designator_option IN NUMBER,
54 X_transfer_option IN NUMBER,
55 X_alt_bom_designator IN VARCHAR2,
56 X_effectivity_date IN DATE,
57 X_implemented_only IN NUMBER,
58 X_unit_number IN VARCHAR2 DEFAULT NULL
59 )
60 IS
61 X_stmt_num NUMBER;
62 X_GTIN_ST_TYPE_ID NUMBER;
63
64 --BOM ER 9946990 begin
65 l_parent_BIT NUMBER;
66 l_PTO_flag varchar2(1);
67 ATO_IN_KIT_EXCEPTION EXCEPTION;
68 ATO_IN_MODEL_EXCEPTION EXCEPTION;
69
70 cursor all_bills(X_org_id number, X_eng_item_id number, X_designator_option number , X_alt_bom_designator varchar2) is
71 select bill_sequence_id from bom_structures_b
72 WHERE ORGANIZATION_ID = X_org_id
73 AND ASSEMBLY_ITEM_ID = X_eng_item_id
74 AND ((X_designator_option = 2 AND
75 ALTERNATE_BOM_DESIGNATOR IS NULL)
76 OR
77 (X_designator_option = 3 AND
78 ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
79 OR
80 X_designator_option = 1)
81 AND nvl(effectivity_control , 1) <> 4;
82
83
84
85 cursor comp_rows(bill_id IN NUMBER) is
86 select msib.bom_item_type, msib.replenish_to_order_flag, bcb.optional
87 from bom_components_b bcb, mtl_system_items_b msib
88 where bcb.bill_sequence_id = bill_id
89 and msib.inventory_item_id = bcb.component_item_id
90 and msib.organization_id = bcb.pk2_value;
91
92 --BOM ER 9946990 end
93 BEGIN
94
95 --BOM ER 9946990 begin
96 X_stmt_num := 350;
97
98 select msib.bom_item_type, msib.pick_components_flag into l_parent_BIT, l_PTO_flag from mtl_system_items_b msib where inventory_item_id = X_eng_item_id and organization_id = X_org_id;
99
100 for bill in all_bills(X_org_id, X_eng_item_id, X_designator_option, X_alt_bom_designator)
101 loop
102
103 for comp in comp_rows(bill.bill_sequence_id)
104 loop
105 if l_parent_BIT = 4 and l_PTO_flag = 'Y'
106 and comp.bom_item_type = 4 and comp.replenish_to_order_flag = 'Y'
107 and nvl(FND_PROFILE.value('BOM:MANDATORY_ATO_IN_PTO'), 2) <> 1
108 then
109 raise ATO_IN_KIT_EXCEPTION;
110 end if;
111
112
113 if l_parent_BIT = 1 and l_PTO_flag = 'Y'
114 and comp.bom_item_type = 4 and comp.replenish_to_order_flag = 'Y' and nvl(comp.optional, 1) = 2
115 and nvl(FND_PROFILE.value('BOM:MANDATORY_ATO_IN_PTO'), 2) <> 1
116 then
117 raise ATO_IN_MODEL_EXCEPTION;
118 end if;
119
120 end loop;
121
122 end loop;
123
124 --BOM ER 9946990 end
125
126 X_stmt_num := 400;
127
128 X_GTIN_ST_TYPE_ID := Get_GTIN_Structure_Type_Id;
129
130 UPDATE BOM_BILL_OF_MATERIALS
131 SET ASSEMBLY_TYPE = 1,
132 LAST_UPDATE_DATE = sysdate,
133 LAST_UPDATED_BY = to_number(fnd_profile.value('USER_ID'))
134 WHERE ORGANIZATION_ID = X_org_id
135 AND ASSEMBLY_ITEM_ID = X_eng_item_id
136 AND ((X_designator_option = 2 AND
137 ALTERNATE_BOM_DESIGNATOR IS NULL)
138 OR
139 (X_designator_option = 3 AND
140 ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
141 OR
142 X_designator_option = 1)
143 AND nvl(effectivity_control , 1) <> 4 -- Bug 4210718
144 AND ((X_GTIN_ST_TYPE_ID IS NULL or STRUCTURE_TYPE_ID <> X_GTIN_ST_TYPE_ID)
145 OR STRUCTURE_TYPE_ID IS NULL); --added for bug 9436790
146 UPDATE BOM_EXPLOSIONS_ALL -- Update Sql added for bug#9260472
147 SET ASSEMBLY_TYPE = 1,
148 LAST_UPDATE_DATE = sysdate,
149 LAST_UPDATED_BY = to_number(fnd_profile.value('USER_ID'))
150 WHERE ORGANIZATION_ID = X_org_id
151 AND COMPONENT_ITEM_ID = X_eng_item_id
152 AND ((X_designator_option = 2 AND
153 ALTERNATE_BOM_DESIGNATOR IS NULL)
154 OR
155 (X_designator_option = 3 AND
156 ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
157 OR
158 X_designator_option = 1)
159 AND nvl(effectivity_control , 1) <> 4
160 AND ((X_GTIN_ST_TYPE_ID IS NULL or STRUCTURE_TYPE_ID <> X_GTIN_ST_TYPE_ID)
161 OR STRUCTURE_TYPE_ID IS NULL); --added for bug 9436790
162
163 X_stmt_num := 401;
164 If (PJM_UNIT_EFF.Enabled = 'Y' AND
165 PJM_UNIT_EFF.Unit_Effective_Item(
166 X_Item_ID => X_eng_item_id,
167 X_Organization_ID => X_org_id) = 'Y')
168 THEN
169 DELETE FROM BOM_INVENTORY_COMPONENTS BIC
170 WHERE ((X_implemented_only = 1 AND BIC.IMPLEMENTATION_DATE IS NULL)
171 OR (X_transfer_option = 2 AND
172 (BIC.FROM_END_ITEM_UNIT_NUMBER > X_unit_number
173 OR (BIC.FROM_END_ITEM_UNIT_NUMBER < X_unit_number
174 AND NVL(BIC.TO_END_ITEM_UNIT_NUMBER, X_unit_number)
175 < X_unit_number)))
176 OR (X_transfer_option = 3 AND
177 NVL(BIC.TO_END_ITEM_UNIT_NUMBER, X_unit_number) < X_unit_number))
178 AND BIC.BILL_SEQUENCE_ID in (SELECT BOM_T.BILL_SEQUENCE_ID
179 FROM BOM_BILL_OF_MATERIALS BOM_T
180 WHERE ORGANIZATION_ID = X_org_id
181 AND ASSEMBLY_ITEM_ID = X_eng_item_id
182 AND ASSEMBLY_TYPE = 1
183 AND ((X_designator_option = 2 AND ALTERNATE_BOM_DESIGNATOR IS NULL)
184 OR (X_designator_option = 3 AND ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
185 OR (X_designator_option = 1))
186 AND (X_GTIN_ST_TYPE_ID IS NULL or STRUCTURE_TYPE_ID <> X_GTIN_ST_TYPE_ID)
187 AND nvl(BOM_T.effectivity_control , 1) <> 4 -- Bug 4210718
188 );
189 ELSE
190 DELETE FROM BOM_INVENTORY_COMPONENTS BIC
191 WHERE ((X_implemented_only = 1 AND BIC.IMPLEMENTATION_DATE IS NULL)
192 OR (X_transfer_option = 2 AND
193 (BIC.EFFECTIVITY_DATE > X_effectivity_date
194 OR NVL(BIC.DISABLE_DATE, X_effectivity_date + 1)
195 <= X_effectivity_date))
196 OR (X_transfer_option = 3 AND NVL(BIC.DISABLE_DATE, X_effectivity_date +
197 1) <= X_effectivity_date))
198 AND BIC.BILL_SEQUENCE_ID in (SELECT BOM_T.BILL_SEQUENCE_ID
199 FROM BOM_BILL_OF_MATERIALS BOM_T
200 WHERE ORGANIZATION_ID = X_org_id
201 AND ASSEMBLY_ITEM_ID = X_eng_item_id
202 AND ASSEMBLY_TYPE = 1
203 AND ((X_designator_option = 2 AND ALTERNATE_BOM_DESIGNATOR IS NULL)
204 OR (X_designator_option = 3 AND ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
205 OR (X_designator_option = 1))
206 AND (X_GTIN_ST_TYPE_ID IS NULL or STRUCTURE_TYPE_ID <> X_GTIN_ST_TYPE_ID)
207 AND nvl(BOM_T.effectivity_control , 1) <> 4 -- Bug 4210718
208 );
209 END IF;
210 EXCEPTION
211 --BOM ER 9946990 begin
212 WHEN ATO_IN_KIT_EXCEPTION THEN
213 rollback;
214 FND_MESSAGE.SET_NAME('BOM', 'BOM_KIT_COMP_PRF_NOT_SET');
215 APP_EXCEPTION.RAISE_EXCEPTION;
216
217
218 WHEN ATO_IN_MODEL_EXCEPTION THEN
219 rollback;
220 FND_MESSAGE.SET_NAME('BOM', 'BOM_MODEL_COMP_PRF_NOT_SET');
221 APP_EXCEPTION.RAISE_EXCEPTION;
222
223 --BOM ER 9946990 end
224
225 WHEN OTHERS THEN
226 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'BOM_UPDATE',
227 stmt_num => X_stmt_num,
228 message_name => 'ENG_ENUBRT_ERROR',
229 token => SQLERRM);
230
231 END BOM_UPDATE;
232
233 -- +--------------------------- BOM_TRANSFER -----------------------------+
234 -- NAME
235 -- BOM_TRANSFER
236
237 -- DESCRIPTION
238 -- Transfer Bills
239
240 -- REQUIRES
241 -- org_id: organization id
242 -- eng_item_id
243 -- mfg_item_id
244 -- designator_option
245 -- 1. all
246 -- 2. primary only
247 -- 3. specific only
248 -- transfer option
249 -- 1. all rows
250 -- 2. current only
251 -- 3. current and pending
252 -- alt_bom_designator
253 -- effectivity_date
254 -- last_login_id not used internally
255 -- ecn_name
256
257 -- OUTPUT
258
259 -- NOTES
260
261 -- +--------------------------------------------------------------------------+
262
263 PROCEDURE BOM_TRANSFER
264 (
265 X_org_id IN NUMBER,
266 X_eng_item_id IN NUMBER,
267 X_mfg_item_id IN NUMBER,
268 X_designator_option IN NUMBER,
269 X_transfer_option IN NUMBER,
270 X_alt_bom_designator IN VARCHAR2,
271 X_effectivity_date IN DATE,
272 X_last_login_id IN NUMBER,
273 X_ecn_name IN VARCHAR2,
274 X_unit_number IN VARCHAR2 DEFAULT NULL
275 )
276 IS
277 X_stmt_num NUMBER;
278 X_from_bill_sequence_id NUMBER;
279 X_GTIN_ST_TYPE_ID NUMBER;
280 FLAG NUMBER;
281
282 l_parent_BIT NUMBER;
283 l_PTO_flag varchar2(1);
284 ATO_IN_KIT_EXCEPTION EXCEPTION;
285 ATO_IN_MODEL_EXCEPTION EXCEPTION;
286
287
288 cursor all_bills(X_org_id number, X_eng_item_id number,
289 X_designator_option number, X_alt_bom_designator varchar2) is
290 select bill_sequence_id from bom_structures_b
291 WHERE ORGANIZATION_ID = X_org_id
292 AND ASSEMBLY_ITEM_ID = X_eng_item_id
293 AND ((X_designator_option = 2 AND
294 ALTERNATE_BOM_DESIGNATOR IS NULL)
295 OR
296 (X_designator_option = 3 AND
297 ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
298 OR
299 X_designator_option = 1)
300 AND nvl(effectivity_control , 1) <> 4;
301
302
303
304 cursor comp_rows(bill_id IN NUMBER) is
305 select msib.bom_item_type, msib.replenish_to_order_flag, bcb.optional
306 from bom_components_b bcb, mtl_system_items_b msib
307 where bcb.bill_sequence_id = bill_id
308 and msib.inventory_item_id = bcb.component_item_id
309 and msib.organization_id = bcb.pk2_value;
310
311 --BOM ER 9946990 end
312
313 CURSOR BOM_CURSOR IS
314 SELECT BILL_SEQUENCE_ID, ALTERNATE_BOM_DESIGNATOR
315 FROM BOM_BILL_OF_MATERIALS
316 WHERE ORGANIZATION_ID = X_org_id
317 AND ASSEMBLY_ITEM_ID = X_mfg_item_id
318 AND nvl(effectivity_control, 1) <> 4 -- Bug 4210718
319 AND Source_BILL_SEQUENCE_ID = BILL_SEQUENCE_ID; --R12
320
321 -- BUG 3503220
322 CURSOR BOM_COPIES IS
323 SELECT BILL_SEQUENCE_ID, ALTERNATE_BOM_DESIGNATOR, assembly_item_id, organization_id, obj_name
324 FROM BOM_BILL_OF_MATERIALS BOM_T
325 WHERE ORGANIZATION_ID = X_org_id
326 AND ASSEMBLY_ITEM_ID = X_mfg_item_id
327 AND nvl(BOM_T.effectivity_control, 1) <> 4 -- Bug 4210718
328 AND ((X_designator_option = 2 AND
329 BOM_T.ALTERNATE_BOM_DESIGNATOR IS NULL)
330 OR
331 (X_designator_option = 3 AND
332 BOM_T.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
333 OR
334 (X_designator_option = 1));
335
336 BEGIN
337
338 --BOM ER 9946990 begin
339
340 X_stmt_num := 650;
341
342 BEGIN
343
344 select msib.bom_item_type, msib.pick_components_flag into l_parent_BIT, l_PTO_flag
345 from mtl_system_items_b msib where inventory_item_id = X_eng_item_id and organization_id = X_org_id;
346
347 for bill in all_bills(X_org_id, X_eng_item_id,
348 X_designator_option, X_alt_bom_designator) loop
349
350 for comp in comp_rows(bill.bill_sequence_id) loop
351 if l_parent_BIT = 4 and l_PTO_flag = 'Y'
352 and comp.bom_item_type = 4 and comp.replenish_to_order_flag = 'Y'
353 and nvl(FND_PROFILE.value('BOM:MANDATORY_ATO_IN_PTO'), 2) <> 1
354 then
355 raise ATO_IN_KIT_EXCEPTION;
356 end if;
357
358
359 if l_parent_BIT = 1 and l_PTO_flag = 'Y'
360 and comp.bom_item_type = 4 and comp.replenish_to_order_flag = 'Y' and nvl(comp.optional, 1) = 2
361 and nvl(FND_PROFILE.value('BOM:MANDATORY_ATO_IN_PTO'), 2) <> 1
362 then
363 raise ATO_IN_MODEL_EXCEPTION;
364 end if;
365
366 end loop;
367
368 end loop;
369
370 EXCEPTION
371
372
373 WHEN ATO_IN_KIT_EXCEPTION THEN
374 rollback;
375 FND_MESSAGE.SET_NAME('BOM', 'BOM_KIT_COMP_PRF_NOT_SET');
376 APP_EXCEPTION.RAISE_EXCEPTION;
377
378
379 WHEN ATO_IN_MODEL_EXCEPTION THEN
380 rollback;
381 FND_MESSAGE.SET_NAME('BOM', 'BOM_MODEL_COMP_PRF_NOT_SET');
382 APP_EXCEPTION.RAISE_EXCEPTION;
383
384
385
386 WHEN OTHERS THEN
387 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'COMPONENT_TRANSFER',
388 stmt_num => X_stmt_num,
389 message_name => 'ENG_ENUBRT_ERROR',
390 token => SQLERRM);
391
392 END;
393
394
395 --BOM ER 9946990 end
396
397 --- BOM_BILL_OF_MATERIALS
398
399 X_stmt_num := 700;
400
401
402 BEGIN
403
404 X_GTIN_ST_TYPE_ID := Get_GTIN_Structure_Type_Id;
405
406 -- Bug 3503263 While Specific Alternate of a Bill is being copied then Primary bill of Target Item is creaed.
407 IF (x_eng_item_id<>x_mfg_item_id)and (x_designator_option = 3) then
408 FLAG := 1;
409 ELSE
410 FLAG:= 0;
411 END IF;
412 -- Bug 3523263 Bug 4240131 inserted effectivity_control in table.
413 INSERT INTO BOM_BILL_OF_MATERIALS(
414 ASSEMBLY_ITEM_ID,
415 ORGANIZATION_ID,
416 ALTERNATE_BOM_DESIGNATOR,
417 LAST_UPDATE_DATE,
418 LAST_UPDATED_BY,
419 CREATION_DATE,
420 CREATED_BY,
421 LAST_UPDATE_LOGIN,
422 COMMON_ASSEMBLY_ITEM_ID,
423 SPECIFIC_ASSEMBLY_COMMENT,
424 PENDING_FROM_ECN,
425 ATTRIBUTE_CATEGORY,
426 ATTRIBUTE1,
427 ATTRIBUTE2,
428 ATTRIBUTE3,
429 ATTRIBUTE4,
430 ATTRIBUTE5,
431 ATTRIBUTE6,
432 ATTRIBUTE7,
433 ATTRIBUTE8,
434 ATTRIBUTE9,
435 ATTRIBUTE10,
436 ATTRIBUTE11,
437 ATTRIBUTE12,
438 ATTRIBUTE13,
439 ATTRIBUTE14,
440 ATTRIBUTE15,
441 ASSEMBLY_TYPE,
442 BILL_SEQUENCE_ID,
443 COMMON_BILL_SEQUENCE_ID,
444 REQUEST_ID,
445 PROGRAM_APPLICATION_ID,
446 PROGRAM_ID,
447 PROGRAM_UPDATE_DATE,
448 COMMON_ORGANIZATION_ID,
449 NEXT_EXPLODE_DATE,
450 EFFECTIVITY_CONTROL,
451 source_bill_sequence_id, --R12
452 STRUCTURE_TYPE_ID, -- Bug 12555524
453 pk1_value, --Bug 4707618
454 pk2_value) --Bug 4707618
455 SELECT
456 X_mfg_item_id,
457 ORGANIZATION_ID,
458 BOM_T.ALTERNATE_BOM_DESIGNATOR,
459 SYSDATE,
460 to_number(Fnd_Profile.Value('USER_ID')),
461 SYSDATE,
462 to_number(Fnd_Profile.Value('USER_ID')),
463 to_number(Fnd_Profile.Value('LOGIN_ID')),
464 BOM_T.COMMON_ASSEMBLY_ITEM_ID,
465 BOM_T.SPECIFIC_ASSEMBLY_COMMENT,
466 BOM_T.PENDING_FROM_ECN,
467 BOM_T.ATTRIBUTE_CATEGORY,
468 BOM_T.ATTRIBUTE1,
469 BOM_T.ATTRIBUTE2,
470 BOM_T.ATTRIBUTE3,
471 BOM_T.ATTRIBUTE4,
472 BOM_T.ATTRIBUTE5,
473 BOM_T.ATTRIBUTE6,
474 BOM_T.ATTRIBUTE7,
475 BOM_T.ATTRIBUTE8,
476 BOM_T.ATTRIBUTE9,
477 BOM_T.ATTRIBUTE10,
478 BOM_T.ATTRIBUTE11,
479 BOM_T.ATTRIBUTE12,
480 BOM_T.ATTRIBUTE13,
481 BOM_T.ATTRIBUTE14,
482 BOM_T.ATTRIBUTE15,
483 1,
484 BOM_INVENTORY_COMPONENTS_S.NEXTVAL,
485 DECODE(BOM_T.COMMON_BILL_SEQUENCE_ID,BOM_T.BILL_SEQUENCE_ID,BOM_INVENTORY_COMPONENTS_S.CURRVAL,BOM_T.COMMON_BILL_SEQUENCE_ID),
486 BOM_T.REQUEST_ID,
487 BOM_T.PROGRAM_APPLICATION_ID,
488 BOM_T.PROGRAM_ID,
489 BOM_T.PROGRAM_UPDATE_DATE,
490 BOM_T.COMMON_ORGANIZATION_ID,
491 BOM_T.NEXT_EXPLODE_DATE,
492 BOM_T.EFFECTIVITY_CONTROL,
493 DECODE(BOM_T.COMMON_BILL_SEQUENCE_ID,BOM_T.BILL_SEQUENCE_ID,BOM_INVENTORY_COMPONENTS_S.CURRVAL,BOM_T.COMMON_BILL_SEQUENCE_ID),
494 STRUCTURE_TYPE_ID, -- Bug 12555524
495 X_mfg_item_id,
496 ORGANIZATION_ID
497 FROM BOM_BILL_OF_MATERIALS BOM_T
498 WHERE ORGANIZATION_ID = X_org_id
499 AND ASSEMBLY_ITEM_ID = X_eng_item_id
500 AND ((X_designator_option = 2 AND
501 BOM_T.ALTERNATE_BOM_DESIGNATOR IS NULL)
502 OR
503 (X_designator_option = 3 AND
504 BOM_T.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator)
505 OR
506 (X_designator_option = 1))
507 AND nvl(BOM_T.effectivity_control , 1) <> 4 -- Bug 4210718
508 AND (X_GTIN_ST_TYPE_ID IS NULL or BOM_T.STRUCTURE_TYPE_ID <> X_GTIN_ST_TYPE_ID);
509
510
511 EXCEPTION
512
513 WHEN OTHERS THEN
514 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'BOM_TRANSFER',
515 stmt_num => X_stmt_num,
516 message_name => 'ENG_ENUBRT_ERROR',
517 token => SQLERRM);
518 END;
519 -- bug 3780577 : odaboval moved the ERES call after the LOOP
520 -- in order to see the inventory components:
521 /* THIS ERES CALL IS NOT MOVED TO A PLACE BELOW.
522 -- ERES BEGIN
523 -- If there is a parent eRecord, log a child record to accompany it
524 -- ================================================================
525 IF ENG_BOM_RTG_TRANSFER_PKG.G_PARENT_ERECORD_ID is NOT NULL THEN
526 FOR BILL IN BOM_COPIES LOOP -- BUG 3503220
527 -- Log an erecord for each new bill inserted above -- BUG 3503220
528
529 ENG_BOM_RTG_TRANSFER_PKG.Process_Erecord
530 ( p_event_name =>'oracle.apps.bom.billCreate'
531 , p_event_key =>to_char(BILL.bill_sequence_id)
532 , p_user_key =>ENG_BOM_RTG_TRANSFER_PKG.G_ITEM_NAME
533 ||'-'||ENG_BOM_RTG_TRANSFER_PKG.G_ORG_CODE||'-'||BILL.alternate_bom_designator
534 , p_parent_event_key =>to_char(X_eng_item_id)||'-'||to_char(X_org_id)
535 ||'-'||to_char(X_mfg_item_id)
536 );
537 END LOOP;
538 END IF;
539 NOT USED ANYMORE. PLEASE SEE CALL BELOW. */
540 -- ERES END
541 -- ========
542
543 FOR BOM1 IN BOM_CURSOR LOOP
544
545 X_stmt_num := 701;
546 BEGIN
547 SELECT BILL_SEQUENCE_ID
548 INTO X_from_bill_sequence_id
549 FROM BOM_BILL_OF_MATERIALS
550 WHERE ORGANIZATION_ID = X_org_id
551 AND ASSEMBLY_ITEM_ID = X_eng_item_id
552 AND NVL(ALTERNATE_BOM_DESIGNATOR,'NONE') = NVL(BOM1.ALTERNATE_BOM_DESIGNATOR,'NONE');
553 EXCEPTION
554 WHEN OTHERS THEN
555 ENG_BOM_RTG_TRANSFER_PKG.RAISE_ERROR(func_name => 'BOM_TRANSFER',
556 stmt_num => X_stmt_num,
557 message_name => 'ENG_ENUBRT_ERROR',
558 token => SQLERRM);
559 END;
560
561 ENG_ITEM_PKG.COMPONENT_TRANSFER(X_org_id => X_org_id,
562 X_eng_item_id => X_eng_item_id,
563 X_designator_option => X_designator_option,
564 X_alt_bom_designator => X_alt_bom_designator);
565
566 BOM_COPY_BILL.COPY_BILL(from_sequence_id => X_from_bill_sequence_id,
567 to_sequence_id => BOM1.BILL_SEQUENCE_ID,
568 from_org_id => X_org_id,
569 to_org_id => X_org_id,
570 display_option => X_transfer_option,
571 user_id => to_number(Fnd_Profile.Value('USER_ID')),
572 to_item_id => X_mfg_item_id,
573 direction => 4,
574 to_alternate => BOM1.ALTERNATE_BOM_DESIGNATOR,
575 rev_date => X_effectivity_date,
576 e_change_notice => X_ecn_name,
577 rev_item_seq_id => NULL,
578 bill_or_eco => 1,
579 eco_eff_date => X_effectivity_date,
580 unit_number => X_unit_number,
581 from_item_id =>X_eng_item_id);
582
583 END LOOP;
584 IF FLAG=1 THEN -- 3503263
585 UPDATE BOM_BILL_OF_MATERIALS BOM1
586 SET BOM1.ALTERNATE_BOM_DESIGNATOR =NULL
587 WHERE ORGANIZATION_ID = X_org_id
588 AND ASSEMBLY_ITEM_ID = X_mfg_item_id
589 AND (X_designator_option = 3 AND
590 BOM1.ALTERNATE_BOM_DESIGNATOR = X_alt_bom_designator);
591 END IF;
592
593 -- Bug 4584490: Changes for bom business events support
594 BEGIN
595 FOR BILL IN BOM_COPIES
596 LOOP
597 Bom_Business_Event_PKG.Raise_Bill_Event(
598 p_pk1_value => Bill.assembly_item_id
599 , p_pk2_value => Bill.organization_id
600 , p_obj_name => Bill.obj_name
601 , p_structure_name => Bill.alternate_bom_designator
602 , p_organization_id => Bill.organization_id
603 , p_structure_comment => NULL
604 , p_Event_Name => Bom_Business_Event_PKG.G_STRUCTURE_CREATION_EVENT
605 );
606 END LOOP;
607 EXCEPTION
608 WHEN OTHERS THEN
609 null;
610 -- nothing is required to be done, process continues
611 END;
612 -- End Changes for bug 4584490
613 -- bug 3780577 : odaboval moved the ERES BillCreate here
614 -- in order to get the routing revisions.
615 -- ERES BEGIN
616 -- If there is a parent eRecord, log a child record to accompany it
617 -- ================================================================
618 IF ENG_BOM_RTG_TRANSFER_PKG.G_PARENT_ERECORD_ID is NOT NULL THEN
619 FOR BILL IN BOM_COPIES LOOP -- BUG 3503220
620 -- Log an erecord for each new bill inserted above -- BUG 3503220
621
622 ENG_BOM_RTG_TRANSFER_PKG.Process_Erecord
623 ( p_event_name =>'oracle.apps.bom.billCreate'
624 , p_event_key =>to_char(BILL.bill_sequence_id)
625 , p_user_key =>ENG_BOM_RTG_TRANSFER_PKG.G_ITEM_NAME
626 ||'-'||ENG_BOM_RTG_TRANSFER_PKG.G_ORG_CODE||'-'||BILL.alternate_bom_designator
627 , p_parent_event_key =>to_char(X_eng_item_id)||'-'||to_char(X_org_id)
628 ||'-'||to_char(X_mfg_item_id)
629 );
630 END LOOP;
631 END IF;
632 -- ERES END
633 -- ========
634 END BOM_TRANSFER;
635
636 END ENG_BOM_PKG;