[Home] [Help]
PACKAGE BODY: APPS.BOM_BOM_COMPONENT_UTIL
Source
1 PACKAGE BODY Bom_Bom_Component_Util AS
2 /* $Header: BOMUCMPB.pls 120.17.12020000.2 2012/07/16 12:05:20 anagubad ship $ */
3 /****************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- ENGUCMPB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package Bom_Bom_Component_Util
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 12-JUL-99 Rahul Chitko Initial Creation
21 -- 24-OCT-00 Masanori Kimizuka Modified Insert_Row to add Eco_For_Production
22 --
23 -- 31-AUG-01 Refai Farook One To Many support changes
24 --
25 -- 25-SEP-01 Refai Farook Mass changes for unit effectivity changes(Update_Row procedure changed)
26 --
27 -- 15-NOV-02 Anirban Dey Added Auto_Request_Material Support in 11.5.9
28 --
29 -- 29-APR-05 Abhishek Rudresh Common BOM attrs Update
30 ****************************************************************************/
31 G_PKG_NAME CONSTANT VARCHAR2(30) := 'Bom_Bom_Component_Util';
32
33 -- FUNCTION Get_Operation_Leadtime
34 /********************************************************************
35 * Function : Get_Operation_Leadtime
36 * Parameters IN : p_assembly_item_id IN NUMBER
37 * p_organization_id IN NUMBER
38 * p_alternate_bom_code IN VARCHAR2
39 * p_operation_seq_num IN NUMBER
40 * Returns: Lead Time percent corresponding to the operation
41 * Purpose : This function gives the lead time percent of the operation
42 * as defined in the routing.
43 **********************************************************************/
44 FUNCTION Get_Operation_Leadtime (
45 p_assembly_item_id IN NUMBER,
46 p_organization_id IN NUMBER,
47 p_alternate_bom_code IN VARCHAR2,
48 p_operation_seq_num IN NUMBER) RETURN NUMBER;
49
50
51 -- PROCEDURE Convert_Miss_To_Null
52 /********************************************************************
53 * Procedure : Convert_Miss_To_Null
54 * Parameters IN : Bom Component Exposed column record
55 * Bom Component Unexposed Column record
56 * Parameters OUT: Bom Component exposed column record
57 * Bom Component unexposed column record
58 * Purpose : This procedure will convert the missing values of
59 * some attributes that the user wishes to NULL.
60 **********************************************************************/
61 PROCEDURE Convert_Miss_To_Null
62 ( p_bom_component_rec IN Bom_Bo_Pub.Bom_Comps_Rec_Type
63 , p_bom_Comp_Unexp_Rec IN Bom_Bo_Pub.Bom_Comps_Unexposed_Rec_Type
64 , x_bom_Component_Rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Comps_Rec_Type
65 , x_bom_Comp_Unexp_Rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Comps_Unexposed_Rec_Type
66 )
67 IS
68 l_rev_component_rec Bom_Bo_Pub.Rev_Component_Rec_Type;
69 l_rev_comp_unexp_rec Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type;
70 BEGIN
71
72 -- Convert the BOM Record to ECO Record
73 Bom_Bo_Pub.Convert_BomComp_To_EcoComp
74 ( p_bom_component_rec => p_bom_component_rec
75 , p_bom_comp_unexp_rec => p_bom_comp_unexp_rec
76 , x_rev_component_rec => l_rev_component_rec
77 , x_rev_comp_unexp_rec => l_rev_comp_unexp_rec
78 );
79
80 -- Call the Convert Missing to Null procedure
81
82 Bom_Bom_Component_Util.Convert_Miss_To_Null
83 ( p_rev_component_rec => l_rev_component_rec
84 , p_rev_comp_unexp_rec => l_rev_comp_unexp_rec
85 , x_rev_component_rec => l_rev_component_rec
86 , x_rev_comp_unexp_rec => l_rev_comp_unexp_rec
87 );
88
89 -- Convert the ECO Record back to BOM for return
90
91 Bom_Bo_Pub.Convert_EcoComp_To_BomComp
92 ( p_rev_component_rec => l_rev_component_rec
93 , p_rev_comp_unexp_rec => l_rev_comp_unexp_rec
94 , x_bom_component_rec => x_bom_component_rec
95 , x_bom_comp_unexp_rec => x_bom_comp_unexp_rec
96 );
97
98 END Convert_Miss_To_Null;
99
100
101 /*****************************************************************
102 * Procedure : Query_Row
103 * Parameters IN : Bom Component Key
104 * Parameters OUT: Bom component Exposed column Record
105 * Bom component Unexposed column Record
106 * Returns : None
107 * Purpose : Query will query the database record and seperate
108 * the unexposed and exposed attributes before returning
109 * the records.
110 ********************************************************************/
111 PROCEDURE Query_Row
112 ( p_Component_Item_Id IN NUMBER
113 , p_Operation_Sequence_Number IN NUMBER
114 , p_Effectivity_Date IN DATE
115 , p_Bill_Sequence_Id IN NUMBER
116 , p_from_end_item_number IN VARCHAR2 := NULL
117 , x_Bom_Component_Rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Comps_Rec_Type
118 , x_Bom_Comp_Unexp_Rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Comps_Unexposed_Rec_Type
119 , x_Return_Status IN OUT NOCOPY VARCHAR2
120 , p_Mesg_Token_Tbl IN Error_Handler.Mesg_Token_Tbl_Type
121 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
122
123 )
124 IS
125 l_rev_component_rec Bom_Bo_Pub.Rev_component_Rec_Type;
126 l_rev_comp_unexp_rec Bom_Bo_pub.Rev_Comp_unexposed_Rec_Type;
127
128 BEGIN
129
130 x_mesg_token_tbl := p_mesg_token_tbl;
131
132 Bom_Bom_Component_Util.Query_Row
133 ( p_component_item_id => p_component_item_id
134 , p_Operation_Sequence_Number => p_Operation_Sequence_Number
135 , p_Effectivity_Date => p_Effectivity_Date
136 , p_Bill_Sequence_Id => p_Bill_Sequence_Id
137 , p_from_end_item_number => p_from_end_item_number
138 , x_rev_component_rec => l_rev_component_rec
139 , x_rev_comp_unexp_rec => l_rev_comp_unexp_rec
140 , x_return_status => x_return_status
141 , p_Mesg_Token_Tbl => p_Mesg_Token_Tbl
142 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
143 );
144
145 -- Convert the ECO record to BOM Record
146
147 Bom_Bo_Pub.Convert_EcoComp_To_BomComp
148 ( p_rev_component_rec => l_rev_component_rec
149 , p_rev_comp_unexp_rec => l_rev_comp_unexp_rec
150 , x_bom_component_rec => x_bom_component_rec
151 , x_bom_comp_unexp_rec => x_bom_comp_unexp_rec
152 );
153
154 END Query_Row;
155
156
157 /*********************************************************************
158 * Procedure : Perform_Writes
159 * Parameters IN : Bom Component exposed column record
160 * Bom component unexposed column record
161 * Parameters OUT: Return Status
162 * Message Token Table
163 * Purpose : Perform Writes is the only exposed procedure when the
164 * user has to perform any insert/update/deletes to the
165 * Inventory Components table.
166 *********************************************************************/
167 PROCEDURE Perform_Writes
168 ( p_bom_component_rec IN Bom_Bo_Pub.Bom_Comps_Rec_Type
169 , p_bom_comp_unexp_rec IN Bom_Bo_Pub.Bom_Comps_Unexposed_Rec_Type
170 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
171 , x_Return_Status IN OUT NOCOPY VARCHAR2
172 )
173 IS
174 l_rev_component_rec Bom_Bo_Pub.Rev_Component_Rec_Type;
175 l_rev_comp_unexp_rec Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type;
176 BEGIN
177
178 -- Convert the BOM Record to ECO Record
179 Bom_Bo_Pub.Convert_BomComp_To_EcoComp
180 ( p_bom_component_rec => p_bom_component_rec
181 , p_bom_comp_unexp_rec => p_bom_comp_unexp_rec
182 , x_rev_component_rec => l_rev_component_rec
183 , x_rev_comp_unexp_rec => l_rev_comp_unexp_rec
184 );
185
186 -- Call Perform Writes Procedure
187
188 Bom_Bom_Component_Util.Perform_Writes
189 ( p_rev_component_rec => l_rev_component_rec
190 , p_rev_comp_unexp_rec => l_rev_comp_unexp_rec
191 , x_mesg_token_tbl => x_mesg_token_tbl
192 , x_return_status => x_return_status
193 );
194
195 END Perform_Writes;
196
197
198 /*******************************************************************/
199 --
200 -- ECO BO routines
201 --
202 /******************************************************************/
203
204
205 PROCEDURE Query_Row
206 ( p_Component_Item_Id IN NUMBER
207 , p_Operation_Sequence_Number IN NUMBER
208 , p_Effectivity_Date IN DATE
209 , p_Bill_Sequence_Id IN NUMBER
210 , p_from_end_item_number IN VARCHAR2 := NULL
211 , x_Rev_Component_Rec IN OUT NOCOPY Bom_Bo_Pub.Rev_Component_Rec_Type
212 , x_Rev_Comp_Unexp_Rec IN OUT NOCOPY Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
213 , x_Return_Status IN OUT NOCOPY VARCHAR2
214 , p_Mesg_Token_Tbl IN Error_Handler.Mesg_Token_Tbl_Type
215 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
216
217 )
218 IS
219 l_rev_component_rec Bom_Bo_Pub.Rev_Component_Rec_Type;
220 l_Rev_Comp_Unexp_Rec Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type;
221 l_err_text VARCHAR2(2000);
222 BEGIN
223
224 -- dbms_output.put_line('Querying component record . . .');
225 -- dbms_output.put_line('Component: ' ||
226 -- to_char(p_Component_Item_Id));
227 -- dbms_output.put_line('Op Seq : ' ||
228 -- to_char(p_Operation_Sequence_Number));
229 -- dbms_output.put_line('Effective: ' || to_char(p_Effectivity_Date));
230 -- dbms_output.put_line('Bill Seq : ' || to_char(p_Bill_Sequence_Id));
231
232
233 x_mesg_token_tbl := p_mesg_token_tbl;
234
235 SELECT ROWID
236 , SUPPLY_SUBINVENTORY
237 , REVISED_ITEM_SEQUENCE_ID
238 , REQUIRED_FOR_REVENUE
239 , HIGH_QUANTITY
240 , COMPONENT_SEQUENCE_ID
241 , WIP_SUPPLY_TYPE
242 , SUPPLY_LOCATOR_ID
243 , BOM_ITEM_TYPE
244 , OPERATION_SEQ_NUM
245 , COMPONENT_ITEM_ID
246 , ITEM_NUM
247 , BASIS_TYPE
248 , COMPONENT_QUANTITY
249 , COMPONENT_YIELD_FACTOR
250 , COMPONENT_REMARKS
251 , EFFECTIVITY_DATE
252 , CHANGE_NOTICE
253 , DISABLE_DATE
254 , ATTRIBUTE_CATEGORY
255 , ATTRIBUTE1
256 , ATTRIBUTE2
257 , ATTRIBUTE3
258 , ATTRIBUTE4
259 , ATTRIBUTE5
260 , ATTRIBUTE6
261 , ATTRIBUTE7
262 , ATTRIBUTE8
263 , ATTRIBUTE9
264 , ATTRIBUTE10
265 , ATTRIBUTE11
266 , ATTRIBUTE12
267 , ATTRIBUTE13
268 , ATTRIBUTE14
269 , ATTRIBUTE15
270 , PLANNING_FACTOR
271 , QUANTITY_RELATED
272 , SO_BASIS
273 , OPTIONAL
274 , MUTUALLY_EXCLUSIVE_OPTIONS
275 , INCLUDE_IN_COST_ROLLUP
276 , CHECK_ATP
277 , SHIPPING_ALLOWED
278 , REQUIRED_TO_SHIP
279 , INCLUDE_ON_SHIP_DOCS
280 , LOW_QUANTITY
281 , ACD_TYPE
282 , OLD_COMPONENT_SEQUENCE_ID
283 , BILL_SEQUENCE_ID
284 , PICK_COMPONENTS
285 , FROM_END_ITEM_UNIT_NUMBER
286 , TO_END_ITEM_UNIT_NUMBER
287 , ENFORCE_INT_REQUIREMENTS
288 , AUTO_REQUEST_MATERIAL -- Added in 11.5.9 by ADEY
289 , SUGGESTED_VENDOR_NAME --- Deepu
290 , VENDOR_ID --- Deepu
291 -- , PURCHASING_CATEGORY_ID --- Deepu
292 , UNIT_PRICE --- Deepu
293 INTO l_rev_comp_Unexp_rec.rowid
294 , l_rev_component_rec.supply_subinventory
295 , l_rev_comp_Unexp_rec.revised_item_sequence_id
296 , l_rev_component_rec.required_for_revenue
297 , l_rev_component_rec.maximum_allowed_quantity
298 , l_rev_comp_Unexp_rec.component_sequence_id
299 , l_rev_component_rec.wip_supply_type
300 , l_rev_comp_Unexp_rec.supply_locator_id
301 , l_rev_comp_Unexp_rec.bom_item_type
302 , l_rev_component_rec.operation_sequence_number
303 , l_rev_comp_Unexp_rec.component_item_id
304 , l_rev_component_rec.item_sequence_number
305 , l_rev_component_rec.basis_type
306 , l_rev_component_rec.quantity_per_assembly
307 , l_rev_component_rec.projected_yield
308 , l_rev_component_rec.comments
309 , l_rev_component_rec.start_effective_date
310 , l_rev_component_rec.Eco_Name
311 , l_rev_component_rec.disable_date
312 , l_rev_component_rec.attribute_category
313 , l_rev_component_rec.attribute1
314 , l_rev_component_rec.attribute2
315 , l_rev_component_rec.attribute3
316 , l_rev_component_rec.attribute4
317 , l_rev_component_rec.attribute5
318 , l_rev_component_rec.attribute6
319 , l_rev_component_rec.attribute7
320 , l_rev_component_rec.attribute8
321 , l_rev_component_rec.attribute9
322 , l_rev_component_rec.attribute10
323 , l_rev_component_rec.attribute11
324 , l_rev_component_rec.attribute12
325 , l_rev_component_rec.attribute13
326 , l_rev_component_rec.attribute14
327 , l_rev_component_rec.attribute15
328 , l_rev_component_rec.planning_percent
329 , l_rev_component_rec.quantity_related
330 , l_rev_component_rec.so_basis
331 , l_rev_component_rec.optional
332 , l_rev_component_rec.mutually_exclusive
333 , l_rev_component_rec.include_in_cost_rollup
334 , l_rev_component_rec.check_atp
335 , l_rev_component_rec.shipping_allowed
336 , l_rev_component_rec.required_to_ship
337 , l_rev_component_rec.include_on_ship_docs
338 , l_rev_component_rec.minimum_allowed_quantity
339 , l_rev_component_rec.acd_type
340 , l_rev_comp_unexp_rec.old_component_sequence_id
341 , l_rev_comp_unexp_rec.bill_sequence_id
342 , l_rev_comp_unexp_rec.pick_components
343 , l_rev_component_rec.from_end_item_unit_number
344 , l_rev_component_rec.to_end_item_unit_number
345 , l_rev_comp_unexp_rec.enforce_int_requirements_code
346 , l_rev_component_rec.auto_request_material -- Added in 11.5.9 by ADEY
347 , l_rev_component_rec.Suggested_Vendor_Name --- Deepu
348 -- , l_rev_component_rec.purchasing_category_id --- Deepu
349 , l_rev_comp_unexp_rec.Vendor_Id --- Deepu
350 , l_rev_component_rec.Unit_Price --- Deepu
351 FROM BOM_INVENTORY_COMPONENTS
352 WHERE component_item_id = p_component_item_id
353 AND effectivity_date = p_effectivity_date
354 AND operation_seq_num = nvl(p_operation_sequence_number,1) --Bug 5856042
355 AND bill_sequence_id = p_bill_sequence_id
356 AND NVL(from_end_item_unit_number, FND_API.G_MISS_CHAR) =
357 NVL(p_from_end_item_number, FND_API.G_MISS_CHAR);
358
359 x_Return_Status := BOM_Globals.G_RECORD_FOUND;
360 x_Rev_Component_Rec := l_rev_component_rec;
361 x_Rev_Comp_Unexp_Rec := l_Rev_Comp_Unexp_Rec;
362
363 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Finished querying and assigning component record . . .'); END IF;
364
365 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Selecting the lookup meaning for enforce int requirements code . . .'); END IF;
366
367 IF l_rev_comp_unexp_rec.enforce_int_requirements_code IS NOT NULL AND
368 l_rev_comp_unexp_rec.enforce_int_requirements_code <> FND_API.G_MISS_NUM THEN
369 Begin
370 SELECT meaning INTO l_rev_component_rec.enforce_int_requirements FROM mfg_lookups
371 WHERE lookup_type = 'BOM_ENFORCE_INT_REQUIREMENTS' AND
372 lookup_code = l_rev_comp_unexp_rec.enforce_int_requirements_code;
373 exception
374 when others then
375 l_err_text := G_PKG_NAME ||
376 ' Utility (Component Query Row) '
377 || substrb(SQLERRM,1,200);
378
379 Error_Handler.Add_Error_Token
380 ( p_message_name => NULL
381 , p_message_text => l_err_text
382 , p_mesg_token_tbl => p_mesg_token_tbl
383 , x_mesg_token_tbl => x_mesg_token_tbl
384 );
385
386 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
387 End;
388 END IF;
389
390
391 EXCEPTION
392
393 WHEN NO_DATA_FOUND THEN
394 x_return_status := BOM_Globals.G_RECORD_NOT_FOUND;
395 x_rev_component_rec := l_rev_component_rec;
396 x_Rev_Comp_Unexp_Rec := l_Rev_Comp_Unexp_Rec;
397
398 WHEN OTHERS THEN
399 l_err_text := G_PKG_NAME ||
400 ' Utility (Component Query Row) '
401 || substrb(SQLERRM,1,200);
402 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
403
404 Error_Handler.Add_Error_Token
405 ( p_message_name => NULL
406 , p_message_text => l_err_text
407 , p_mesg_token_tbl => p_mesg_token_tbl
408 , x_mesg_token_tbl => x_mesg_token_tbl
409 );
410
411 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
412
413 END Query_Row;
414
415 /***************************************************************************
416 * Procedure : Update_Row
417 * Parameters IN : Revised Component exposed column record
418 * Revised Component unexposed column record
419 * Parameters OUT: Mesg_Token_Tbl
420 * Return_Status
421 * Purpose : Update_Row procedure will update the production record with
422 * the user given values. Any errors will be returned by filling
423 * the Mesg_Token_Tbl and setting the return_status.
424 ****************************************************************************/
425 PROCEDURE Update_Row
426 ( p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
427 , p_Rev_Comp_Unexp_Rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
428 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
429 , x_Return_Status IN OUT NOCOPY VARCHAR2
430 )
431 IS
432 l_return_status varchar2(80);
433 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
434 l_rev_component_rec Bom_Bo_Pub.Rev_Component_Rec_Type;
435 l_err_text VARCHAR2(2000);
436 l_operation_leadtime NUMBER := NULL;
437 l_operation_seq_num NUMBER;
438 BEGIN
439
440 /* need to populate Operation Lead Time percent corresponding to the operation
441 -vhymavat bug3537394 */
442 IF((p_rev_component_rec.new_operation_sequence_number IS NULL) OR
443 (p_rev_component_rec.new_operation_sequence_number =FND_API.G_MISS_NUM) ) THEN
444 l_operation_seq_num := p_rev_component_rec.operation_sequence_number;
445
446 ELSE
447 l_operation_seq_num :=p_rev_component_rec.new_operation_sequence_number;
448 END IF;
449
450 IF(l_operation_seq_num <>1 and p_rev_component_rec.acd_type is null) THEN
451 l_operation_leadtime :=
452 Get_Operation_Leadtime (
453 p_assembly_item_id =>p_rev_comp_Unexp_rec.revised_item_id
454 ,p_organization_id =>p_rev_comp_Unexp_rec.organization_id
455 ,p_alternate_bom_code =>p_rev_component_rec.alternate_bom_code
456 ,p_operation_seq_num => l_operation_seq_num
457 );
458
459 END IF;
460
461
462 UPDATE BOM_INVENTORY_COMPONENTS
463 SET SUPPLY_SUBINVENTORY = p_rev_component_rec.supply_subinventory
464 , REQUIRED_FOR_REVENUE = p_rev_component_rec.required_for_revenue
465 , HIGH_QUANTITY = p_rev_component_rec.maximum_allowed_quantity
466 , WIP_SUPPLY_TYPE = p_rev_component_rec.wip_supply_type
467 , SUPPLY_LOCATOR_ID =
468 DECODE(p_rev_comp_Unexp_rec.supply_locator_id, FND_API.G_MISS_NUM,
469 NULL, p_rev_comp_Unexp_rec.supply_locator_id)
470 , OPERATION_SEQ_NUM = l_operation_seq_num
471 , EFFECTIVITY_DATE =
472 DECODE( p_rev_component_rec.new_effectivity_date
473 , FND_API.G_MISS_DATE
474 , p_rev_component_rec.start_effective_date
475 , NULL
476 , p_rev_component_rec.start_effective_date
477 , p_rev_component_rec.new_effectivity_date
478 )
479 , LAST_UPDATE_DATE = SYSDATE
480 , LAST_UPDATED_BY = BOM_Globals.Get_User_Id
481 , LAST_UPDATE_LOGIN = BOM_Globals.Get_User_Id
482 , ITEM_NUM = p_rev_component_rec.item_sequence_number
483 , BASIS_TYPE = decode(p_rev_component_rec.basis_type,
484 FND_API.G_MISS_NUM, null,p_rev_component_rec.basis_type)
485 , COMPONENT_QUANTITY = p_rev_component_rec.quantity_per_assembly
486 , COMPONENT_YIELD_FACTOR = p_rev_component_rec.projected_yield
487 , COMPONENT_REMARKS =
488 DECODE( p_rev_component_rec.comments, --bug:4178604 Replace FND_API.G_MISS_CHAR by NULL
489 FND_API.G_MISS_CHAR,NULL,
490 p_rev_component_rec.comments)
491 , DISABLE_DATE = p_rev_component_rec.disable_date
492 , ATTRIBUTE_CATEGORY = p_rev_component_rec.attribute_category
493 , ATTRIBUTE1 = p_rev_component_rec.attribute1
494 , ATTRIBUTE2 = p_rev_component_rec.attribute2
495 , ATTRIBUTE3 = p_rev_component_rec.attribute3
496 , ATTRIBUTE4 = p_rev_component_rec.attribute4
497 , ATTRIBUTE5 = p_rev_component_rec.attribute5
498 , ATTRIBUTE6 = p_rev_component_rec.attribute6
499 , ATTRIBUTE7 = p_rev_component_rec.attribute7
500 , ATTRIBUTE8 = p_rev_component_rec.attribute8
501 , ATTRIBUTE9 = p_rev_component_rec.attribute9
502 , ATTRIBUTE10 = p_rev_component_rec.attribute10
503 , ATTRIBUTE11 = p_rev_component_rec.attribute11
504 , ATTRIBUTE12 = p_rev_component_rec.attribute12
505 , ATTRIBUTE13 = p_rev_component_rec.attribute13
506 , ATTRIBUTE14 = p_rev_component_rec.attribute14
507 , ATTRIBUTE15 = p_rev_component_rec.attribute15
508 , PLANNING_FACTOR = p_rev_component_rec.planning_percent
509 , QUANTITY_RELATED = p_rev_component_rec.quantity_related
510 , SO_BASIS = p_rev_component_rec.so_basis
511 , OPTIONAL = p_rev_component_rec.optional
512 , MUTUALLY_EXCLUSIVE_OPTIONS = p_rev_component_rec.mutually_exclusive
513 , INCLUDE_IN_COST_ROLLUP = p_rev_component_rec.include_in_cost_rollup
514 , CHECK_ATP = p_rev_component_rec.check_atp
515 , SHIPPING_ALLOWED = p_rev_component_rec.shipping_allowed
516 , REQUIRED_TO_SHIP = p_rev_component_rec.required_to_ship
517 , INCLUDE_ON_SHIP_DOCS = p_rev_component_rec.include_on_ship_docs
518 , LOW_QUANTITY = p_rev_component_rec.minimum_allowed_quantity
519 , ACD_TYPE = p_rev_component_rec.acd_type
520 , PROGRAM_UPDATE_DATE = SYSDATE
521 , PROGRAM_ID = BOM_Globals.Get_Prog_Id
522 , OPERATION_LEAD_TIME_PERCENT = l_operation_leadtime
523 , Original_System_Reference =
524 p_rev_component_rec.original_system_reference
525 , From_End_Item_Unit_Number =
526 DECODE(p_rev_component_rec.new_from_end_item_unit_number
527 ,FND_API.G_MISS_CHAR
528 ,p_rev_component_rec.from_end_item_unit_number
529 ,NULL
530 ,p_rev_component_rec.from_end_item_unit_number
531 ,p_rev_component_rec.new_from_end_item_unit_number
532 )
533 , To_End_Item_Unit_Number =
534 DECODE( p_rev_component_rec.to_end_item_unit_number
535 , FND_API.G_MISS_CHAR
536 , NULL
537 , p_rev_component_rec.to_end_item_unit_number
538 )
539 , Enforce_Int_Requirements = p_rev_comp_Unexp_rec.Enforce_Int_Requirements_code
540 , Auto_Request_Material = p_rev_component_rec.auto_request_material -- Added in 11.5.9 by ADEY
541 , Suggested_Vendor_Name = p_rev_component_rec.Suggested_Vendor_Name --- Deepu
542 , Vendor_Id = p_rev_comp_Unexp_rec.Vendor_Id --- Deepu
543 -- , Purchasing_Category_id = p_rev_component_rec.purchasing_category_id --- Deepu
544 , Unit_Price = p_rev_component_rec.Unit_Price --- Deepu
545 , REQUEST_ID = Fnd_Global.Conc_Request_Id
546 , PROGRAM_APPLICATION_ID = Fnd_Global.Prog_Appl_Id
547 , COMPONENT_ITEM_ID = p_rev_comp_Unexp_rec.COMPONENT_ITEM_ID /* bug 8412156 */
548 WHERE COMPONENT_SEQUENCE_ID = p_Rev_Comp_Unexp_Rec.component_sequence_id
549 ;
550 --For non-referencing common boms.
551 BOMPCMBM.Update_Related_Components( p_src_comp_seq_id => p_Rev_Comp_Unexp_Rec.component_sequence_id
552 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
553 , x_Return_Status => x_Return_Status
554 );
555 -- x_Return_Status := FND_API.G_RET_STS_SUCCESS;
556 EXCEPTION
557
558 WHEN OTHERS THEN
559
560 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
561 THEN
562 l_err_text := G_PKG_NAME ||
563 ' : Utility (Component Update) ' ||
564 SUBSTR(SQLERRM, 1, 200);
565 Error_Handler.Add_Error_Token
566 ( p_Message_Name => NULL
567 , p_Message_Text => l_err_text
568 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
569 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
570 );
571 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
572 END IF;
573
574 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
575 END Update_Row;
576
577 --following function has been added for bug 7713832
578 FUNCTION Get_Src_Comp_Seq_Id(p_component_item_id IN NUMBER
579 , p_start_effective_date IN DATE
580 , p_op_seq_num IN NUMBER
581 , p_bill_sequence_id IN NUMBER
582 )
583 RETURN NUMBER
584 IS
585 l_src_bill_seq_id NUMBER;
586 l_src_comp_seq_id NUMBER;
587
588 BEGIN
589
590 SELECT source_bill_sequence_id
591 INTO l_src_bill_seq_id
592 FROM bom_structures_b
593 WHERE bill_sequence_id = p_bill_sequence_id
594 and bill_sequence_id <> source_bill_sequence_id;
595
596 select component_sequence_id into l_src_comp_seq_id from bom_components_b
597 where component_item_id = p_component_item_id
598 and bill_sequence_id = l_src_bill_seq_id
599 and operation_seq_num = p_op_seq_num
600 and effectivity_date = p_start_effective_date;
601
602 RETURN l_src_comp_seq_id;
603
604 EXCEPTION
605 WHEN NO_DATA_FOUND THEN
606 RETURN NULL;
607
608 WHEN OTHERS THEN
609 RETURN NULL;
610 END;
611
612
613 /*****************************************************************************
614 * Procedure : Insert_Row
615 * Parameters IN : Revised Component exposed column record
616 * Revised Component unexposed column record
617 * Parameters OUT: Mesg_Token_Tbl
618 * Return_Status
619 * Purpose : This procedure will insert a record in the bom_inventory-
620 * component table. Any errors will be filled in the Mesg_Token
621 * Tbl and returned with a return_status of U
622 *****************************************************************************/
623 PROCEDURE Insert_Row
624 ( p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
625 , p_Rev_Comp_Unexp_Rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
626 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
627 , x_Return_Status IN OUT NOCOPY VARCHAR2
628 )
629 IS
630
631 l_err_text VARCHAR2(2000);
632 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
633 l_Bo_Id VARCHAR2(3);
634
635 l_old_component_sequence_id NUMBER; -- Bug 2820641
636
637 l_object_revision_id NUMBER;
638 l_minor_revision_id NUMBER;
639 l_comp_revision_id NUMBER;
640 l_comp_minor_revision_id NUMBER;
641 l_operation_leadtime NUMBER := NULL;
642 l_operation_seq_num NUMBER;
643
644 l_src_comp_seq_id NUMBER := NULL;
645 l1_src_bill_seq_id NUMBER;
646 l1_com_bill_seq_id NUMBER;
647
648 BEGIN
649
650 l_Bo_Id := Bom_Globals.Get_Bo_Identifier;
651
652
653 -- bug 2820641
654 -- BOM form : BOMFDBOM.fmb won't insert the Old_component_sequence_id.
655 -- ENG form : ENGFDECN.fmb will always inserts Old_component_sequence_id.
656
657 -- moved the select from bom_inventory_components in this block for bug 7713832
658 -- This was originally in the insert statement
659 -- It was removed so that while migrating common bill components
660 --common_component_sequence_id is populated correctly
661
662 if l_Bo_Id = BOM_Globals.G_ECO_BO THEN
663 if (p_rev_comp_Unexp_rec.old_component_sequence_id = FND_API.G_MISS_NUM)
664 or (p_rev_comp_Unexp_rec.old_component_sequence_id is NULL) then
665 --l_old_component_sequence_id := p_rev_comp_Unexp_rec.component_sequence_id;
666 l_src_comp_seq_id := null;
667 else
668 l_old_component_sequence_id := p_rev_comp_Unexp_rec.old_component_sequence_id;
669 --these 2 values will always be the same for acd_type 1
670 if l_old_component_sequence_id <> p_rev_comp_Unexp_rec.component_sequence_id then
671 Select common_component_sequence_id into l_src_comp_seq_id from bom_inventory_components where
672 component_sequence_id = l_old_component_sequence_id;
673 end if;
674 end if;
675 else
676 if (p_rev_comp_Unexp_rec.old_component_sequence_id = FND_API.G_MISS_NUM) then
677 l_old_component_sequence_id := NULL;
678 else
679 l_old_component_sequence_id := p_rev_comp_Unexp_rec.old_component_sequence_id;
680 if l_old_component_sequence_id is not null then
681 Select common_component_sequence_id into l_src_comp_seq_id from bom_inventory_components where
682 component_sequence_id = l_old_component_sequence_id;
683 end if;
684
685 end if;
686
687 end if;
688 -- bug 2820641
689
690 --/* added for BOM Defaulting for WEB-ADI Open Interface calls */
691
692
693 BOM_GLOBALS.GET_DEF_REV_ATTRS
694 ( p_bill_sequence_id => p_rev_comp_Unexp_rec.bill_sequence_id
695 , p_comp_item_id => p_rev_comp_Unexp_rec.component_item_id
696 , p_effectivity_date => nvl(p_rev_component_rec.start_effective_date,SYSDATE)
697 , x_object_revision_id => l_object_revision_id
698 , x_minor_revision_id => l_minor_revision_id
699 , x_comp_revision_id => l_comp_revision_id
700 , x_comp_minor_revision_id => l_comp_minor_revision_id
701 );
702
703 /* need to populate Operation Lead Time percent corresponding to the operation
704 -vhymavat bug3537394 */
705 IF((p_rev_component_rec.new_operation_sequence_number IS NULL) OR
706 (p_rev_component_rec.new_operation_sequence_number =FND_API.G_MISS_NUM) ) THEN
707
708 IF (( p_rev_component_rec.operation_sequence_number IS NULL) OR
709 ( p_rev_component_rec.operation_sequence_number =FND_API.G_MISS_NUM)) THEN
710 l_operation_seq_num := 1;
711 ELSE
712 l_operation_seq_num := p_rev_component_rec.operation_sequence_number;
713 END IF;
714 ELSE
715 l_operation_seq_num :=p_rev_component_rec.new_operation_sequence_number;
716 END IF;
717
718 IF(l_operation_seq_num <>1 and p_rev_component_rec.acd_type is null) THEN
719 l_operation_leadtime :=
720 Get_Operation_Leadtime (
721 p_assembly_item_id =>p_rev_comp_Unexp_rec.revised_item_id
722 ,p_organization_id =>p_rev_comp_Unexp_rec.organization_id
723 ,p_alternate_bom_code =>p_rev_component_rec.alternate_bom_code
724 ,p_operation_seq_num => l_operation_seq_num
725 );
726
727 END IF;
728
729 --Bug 7712832 changes start
730 IF Bom_Globals.Get_Caller_Type = 'MIGRATION' THEN
731
732 l_src_comp_seq_id := Get_Src_Comp_Seq_Id(p_component_item_id => p_rev_comp_Unexp_rec.component_item_id,
733 p_bill_sequence_id => p_rev_comp_Unexp_rec.bill_sequence_id,
734 p_op_seq_num => p_rev_component_rec.operation_sequence_number,
735 p_start_effective_date => p_rev_component_rec.start_effective_date);
736 END IF;
737
738 --Bug 7712832 changes end
739 --bug:3254815 Update request id, prog id, prog appl id and prog update date.
740 INSERT INTO BOM_INVENTORY_COMPONENTS
741 ( SUPPLY_SUBINVENTORY
742 , OPERATION_LEAD_TIME_PERCENT
743 , REVISED_ITEM_SEQUENCE_ID
744 , COST_FACTOR
745 , REQUIRED_FOR_REVENUE
746 , HIGH_QUANTITY
747 , COMPONENT_SEQUENCE_ID
748 , PROGRAM_APPLICATION_ID
749 , WIP_SUPPLY_TYPE
750 , SUPPLY_LOCATOR_ID
751 , BOM_ITEM_TYPE
752 , OPERATION_SEQ_NUM
753 , COMPONENT_ITEM_ID
754 , LAST_UPDATE_DATE
755 , LAST_UPDATED_BY
756 , CREATION_DATE
757 , CREATED_BY
758 , LAST_UPDATE_LOGIN
759 , ITEM_NUM
760 , BASIS_TYPE
761 , COMPONENT_QUANTITY
762 , COMPONENT_YIELD_FACTOR
763 , COMPONENT_REMARKS
764 , EFFECTIVITY_DATE
765 , CHANGE_NOTICE
766 , IMPLEMENTATION_DATE
767 , DISABLE_DATE
768 , ATTRIBUTE_CATEGORY
769 , ATTRIBUTE1
770 , ATTRIBUTE2
771 , ATTRIBUTE3
772 , ATTRIBUTE4
773 , ATTRIBUTE5
774 , ATTRIBUTE6
775 , ATTRIBUTE7
776 , ATTRIBUTE8
777 , ATTRIBUTE9
778 , ATTRIBUTE10
779 , ATTRIBUTE11
780 , ATTRIBUTE12
781 , ATTRIBUTE13
782 , ATTRIBUTE14
783 , ATTRIBUTE15
784 , PLANNING_FACTOR
785 , QUANTITY_RELATED
786 , SO_BASIS
787 , OPTIONAL
788 , MUTUALLY_EXCLUSIVE_OPTIONS
789 , INCLUDE_IN_COST_ROLLUP
790 , CHECK_ATP
791 , SHIPPING_ALLOWED
792 , REQUIRED_TO_SHIP
793 , INCLUDE_ON_SHIP_DOCS
794 , INCLUDE_ON_BILL_DOCS
795 , LOW_QUANTITY
796 , ACD_TYPE
797 , OLD_COMPONENT_SEQUENCE_ID
798 , BILL_SEQUENCE_ID
799 , REQUEST_ID
800 , PROGRAM_ID
801 , PROGRAM_UPDATE_DATE
802 , PICK_COMPONENTS
803 , Original_System_Reference
804 , From_End_Item_Unit_Number
805 , To_End_Item_Unit_Number
806 , Eco_For_Production -- Added by MK
807 , Enforce_Int_Requirements
808 , Auto_Request_Material -- Added in 11.5.9 by ADEY
809 , Obj_Name -- Added by hgelli.
810 , pk1_value
811 , pk2_value
812 , Suggested_Vendor_Name --- Deepu
813 , Vendor_Id --- Deepu
814 -- , Purchasing_Category_id --- Deepu
815 , Unit_Price --- Deepu
816 ,from_object_revision_id
817 , from_minor_revision_id
818 --,component_item_revision_id
819 --,component_minor_revision_id
820 , common_component_sequence_id
821 )
822 VALUES
823 ( p_rev_component_rec.supply_subinventory
824 , l_operation_leadtime
825 , p_rev_comp_unexp_rec.revised_item_sequence_id
826 , NULL /* Cost Factor */
827 , p_rev_component_rec.required_for_revenue
828 , p_rev_component_rec.maximum_allowed_quantity
829 , p_rev_comp_Unexp_rec.component_sequence_id
830 , BOM_Globals.Get_Prog_AppId
831 , p_rev_component_rec.wip_supply_type
832 , DECODE(p_rev_comp_Unexp_rec.supply_locator_id, FND_API.G_MISS_NUM,
833 NULL, p_rev_comp_Unexp_rec.supply_locator_id)
834 , p_rev_comp_Unexp_rec.bom_item_type
835 , l_operation_seq_num
836 , p_rev_comp_Unexp_rec.component_item_id
837 , SYSDATE /* Last Update Date */
838 , BOM_Globals.Get_User_Id /* Last Updated By */
839 , SYSDATE /* Creation Date */
840 , BOM_Globals.Get_User_Id /* Created By */
841 , BOM_Globals.Get_User_Id /* Last Update Login */
842 , DECODE(p_rev_component_rec.item_sequence_number, FND_API.G_MISS_NUM,
843 1, NULL,1,p_rev_component_rec.item_sequence_number)
844 , DECODE(p_rev_component_rec.basis_type,FND_API.G_MISS_NUM,
845 NULL,p_rev_component_rec.basis_type)
846 , p_rev_component_rec.quantity_per_assembly
847 , p_rev_component_rec.projected_yield
848 , p_rev_component_rec.comments
849 , nvl(p_rev_component_rec.start_effective_date,SYSDATE) --2169237
850 , p_rev_component_rec.Eco_Name
851 , DECODE(l_Bo_Id,
852 Bom_Globals.G_BOM_BO,
853 Decode( p_rev_comp_Unexp_rec.bom_implementation_date,
854 null,
855 null,
856 SYSDATE),
857 NULL
858 ) /* Implementation Date */
859 /*
860 , DECODE(l_Bo_Id,
861 Bom_Globals.G_BOM_BO,
862 SYSDATE,
863 NULL
864 ) -- Implementation Date
865 */
866 , p_rev_component_rec.disable_date
867 , p_rev_component_rec.attribute_category
868 , p_rev_component_rec.attribute1
869 , p_rev_component_rec.attribute2
870 , p_rev_component_rec.attribute3
871 , p_rev_component_rec.attribute4
872 , p_rev_component_rec.attribute5
873 , p_rev_component_rec.attribute6
874 , p_rev_component_rec.attribute7
875 , p_rev_component_rec.attribute8
876 , p_rev_component_rec.attribute9
877 , p_rev_component_rec.attribute10
878 , p_rev_component_rec.attribute11
879 , p_rev_component_rec.attribute12
880 , p_rev_component_rec.attribute13
881 , p_rev_component_rec.attribute14
882 , p_rev_component_rec.attribute15
883 , p_rev_component_rec.planning_percent
884 , p_rev_component_rec.quantity_related
885 , p_rev_component_rec.so_basis
886 , p_rev_component_rec.optional
887 , p_rev_component_rec.mutually_exclusive
888 , p_rev_component_rec.include_in_cost_rollup
889 , p_rev_component_rec.check_atp
890 , p_rev_component_rec.shipping_allowed
891 , p_rev_component_rec.required_to_ship
892 , p_rev_component_rec.include_on_ship_docs
893 , NULL /* Include On Bill Docs */
894 , p_rev_component_rec.minimum_allowed_quantity
895 , p_rev_component_rec.acd_type
896 -- , DECODE( p_rev_comp_Unexp_rec.old_component_sequence_id
897 -- , FND_API.G_MISS_NUM
898 -- , NULL
899 -- ,p_rev_comp_Unexp_rec.old_component_sequence_id
900 -- )
901 , l_old_component_sequence_id
902 , p_rev_comp_Unexp_rec.bill_sequence_id
903 , Fnd_Global.Conc_Request_Id /* Request Id */
904 , BOM_Globals.Get_Prog_Id
905 , SYSDATE /* program_update_date */
906 , p_rev_comp_Unexp_rec.pick_components
907 , p_rev_component_rec.original_system_reference
908 , DECODE( p_rev_component_rec.from_end_item_unit_number
909 , FND_API.G_MISS_CHAR
910 , null
911 , p_rev_component_rec.from_end_item_unit_number
912 )
913 , DECODE( p_rev_component_rec.to_end_item_unit_number
914 , FND_API.G_MISS_CHAR
915 , null
916 , p_rev_component_rec.to_end_item_unit_number
917 )
918 , BOM_Globals.Get_Eco_For_Production
919 -- DECODE( l_Bo_Id, BOM_Globals.G_ECO_BO, l_Eco_For_Production, 2) /* Eco for Production flag */
920 , p_rev_comp_Unexp_rec.Enforce_Int_Requirements_Code
921 , p_rev_component_rec.auto_request_material -- Added in 11.5.9 by ADEY
922 , NULL-- Added by hgelli. Identifies this record as Bom Component.
923 , p_rev_comp_Unexp_rec.component_item_id
924 , p_rev_comp_Unexp_rec.organization_id
925 , p_rev_component_rec.Suggested_Vendor_Name --- Deepu
926 , p_rev_comp_Unexp_rec.Vendor_Id --- Deepu
927 -- , p_rev_component_rec.purchasing_category_id --- Deepu
928 , p_rev_component_rec.Unit_Price --- Deepu
929 ,l_object_revision_id
930 ,l_minor_revision_id
931 --,l_comp_revision_id
932 --,l_comp_minor_revision_id
933 ,l_src_comp_seq_id -- changed for bug 7713832
934 );
935
936 --Bug 7713832 begin
937 -- the purpose of this code block is to set the bill_sequence_id and common_bill_sequence_id
938 -- of updatable common boms to be the same
939 -- this is required since migrator data cannot use 'enable_attrs_update' since
940 -- it contains data corresponding to updatable common bom from the source instance
941 -- and does not expect the program to automatically create the record
942
943 IF Bom_Globals.Get_Caller_Type = 'MIGRATION' THEN
944 select source_bill_sequence_id, common_bill_sequence_id into
945 l1_src_bill_seq_id, l1_com_bill_seq_id from bom_structures_b
946 where bill_sequence_id = p_rev_comp_Unexp_rec.bill_sequence_id;
947 --if the following condition is true and you are here it means you have an updatable common bill
948 IF p_rev_comp_Unexp_rec.bill_sequence_id <> l1_src_bill_seq_id THEN
949 --you may already have assigned bill sequence id to common bill sequence id
950 -- in that case no update is needed, otherwise it is required
951 IF p_rev_comp_Unexp_rec.bill_sequence_id <> l1_com_bill_seq_id THEN
952 update bom_structures_b set common_bill_sequence_id = p_rev_comp_Unexp_rec.bill_sequence_id
953 where bill_sequence_id = p_rev_comp_Unexp_rec.bill_sequence_id;
954 END IF;
955 END IF;
956 END IF;
957 --Bug 7713832 end
958 --For non-referencing common boms.
959 --should only be visited if the caller is not migrator
960 --since extract is going to contain corresponding data
961
962 IF Bom_Globals.Get_Caller_Type <> 'MIGRATION' THEN --Bug 7713832
963 BOMPCMBM.Insert_Related_Components( p_src_bill_seq_id => p_rev_comp_Unexp_rec.bill_sequence_id
964 , p_src_comp_seq_id => p_rev_comp_Unexp_rec.component_sequence_id
965 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
966 , x_Return_Status => x_Return_Status
967 );
968 END IF;
969 --x_Return_Status := FND_API.G_RET_STS_SUCCESS;
970
971 EXCEPTION
972 --included for Bug 9076970
973 -- if bom is imported w/o routing same components menat to be imported at different operation_seq_num will now
974 -- be imported at same operation_seq_num and this would violate the unique key constraints on
975 -- effectivity_date, operation_seq_num, component_item_id and bill_sequence_id. hence, this exception is included.
976 WHEN DUP_VAL_ON_INDEX THEN
977 FND_MESSAGE.SET_NAME('BOM', 'BOM_COMPONENT_DUPLICATE');
978 APP_EXCEPTION.RAISE_EXCEPTION;
979 --end changes Bug 9076970
980 WHEN OTHERS THEN
981 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Unexpected Error occured in Insert . . .' || SQLERRM); END IF;
982
983 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
984 THEN
985 l_err_text := G_PKG_NAME ||
986 ' : Utility (Component Insert) ' ||
987 SUBSTR(SQLERRM, 1, 200);
988 Error_Handler.Add_Error_Token
989 ( p_Message_Name => NULL
990 , p_Message_Text => l_err_text
991 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
992 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
993 );
994 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
995 END IF;
996
997 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
998
999 END Insert_Row;
1000
1001 /****************************************************************************
1002 * Procedure : Delete_Row
1003 * Parameters IN : Revised Component Key
1004 * Parameters OUT: Mesg_Token_Tbl
1005 * Return_Status
1006 * Purpose : Will delete a revised component record for a ECO.
1007 * Delete operation will not delete a record in production which
1008 * is already implemented.
1009 *****************************************************************************/
1010 /* Comment out by MK to support delet
1011 PROCEDURE Delete_Row
1012 ( p_component_sequence_id IN NUMBER
1013 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1014 , x_Return_Status IN OUT NOCOPY VARCHAR2
1015 )
1016 */
1017
1018 PROCEDURE Delete_Row
1019 ( p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
1020 , p_rev_comp_unexp_rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
1021 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
1022 , x_Return_Status IN OUT NOCOPY VARCHAR2
1023 )
1024
1025 IS
1026
1027 l_dummy number;
1028 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1029
1030 -- added by MK on 06/04/2001
1031 Cursor CheckGroup is
1032 SELECT description,
1033 delete_group_sequence_id,
1034 delete_type
1035 FROM bom_delete_groups
1036 WHERE delete_group_name = p_rev_comp_unexp_rec.delete_group_name
1037 AND organization_id = p_rev_comp_unexp_rec.organization_id;
1038
1039 l_dg_sequence_id NUMBER;
1040 l_rev_component_rec Bom_Bo_Pub.Rev_Component_Rec_Type ;
1041 l_rev_comp_unexp_rec Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type ;
1042 l_assembly_type NUMBER;
1043
1044
1045
1046
1047 BEGIN
1048
1049
1050 --
1051 -- Initialize Common Record and Status
1052 --
1053 l_rev_component_rec := p_rev_component_rec ;
1054 l_rev_comp_unexp_rec := p_rev_comp_unexp_rec ;
1055 x_return_status := FND_API.G_RET_STS_SUCCESS;
1056
1057 IF Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_ECO_BO
1058 THEN
1059
1060 DELETE FROM BOM_INVENTORY_COMPONENTS
1061 WHERE COMPONENT_SEQUENCE_ID = p_rev_comp_unexp_rec.component_sequence_id;
1062 -- p_component_sequence_id ;
1063
1064 /******************************************************************
1065 -- Also delete the Substitute components and Reference designators
1066 -- by first logging a warning notifying the user of the cascaded
1067 -- Delete.
1068 *******************************************************************/
1069
1070 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
1071 -- This is a warning.
1072 THEN
1073 Error_Handler.Add_Error_Token
1074 ( p_Message_Name => 'BOM_COMP_DEL_CHILDREN'
1075 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1076 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1077 , p_message_type => 'W' -- Added by MK on 11/13/00
1078 );
1079 END IF;
1080
1081 DELETE from bom_reference_designators
1082 WHERE component_sequence_id = p_rev_comp_unexp_rec.component_sequence_id ;
1083 -- p_component_sequence_id ;
1084
1085 DELETE from bom_substitute_components
1086 WHERE component_Sequence_id = p_rev_comp_unexp_rec.component_sequence_id ;
1087 -- p_component_sequence_id ;
1088
1089 --Bug 9356298 start
1090 --For non-referencing common boms.
1091 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Deleting componenets for non-referencing bom . . .' ); END IF;
1092 BOMPCMBM.Delete_Related_Components(p_src_comp_seq => p_rev_comp_unexp_rec.component_sequence_id);
1093 --Bug 9356298 end
1094
1095 -- In Bom BO, the user is not allowed to delete components directly.
1096 -- The user can use delete group functionality for deleting components.
1097 ELSIF Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_BOM_BO
1098 THEN
1099
1100
1101 FOR DG IN CheckGroup
1102 LOOP
1103 IF DG.delete_type <> 4 /* Component */ then
1104
1105 Error_Handler.Add_Error_Token
1106 ( p_message_name => 'BOM_DUPLICATE_DELETE_GROUP'
1107 , p_mesg_token_tbl => l_mesg_token_Tbl
1108 , x_mesg_token_tbl => l_mesg_token_tbl
1109 );
1110
1111 x_return_status := FND_API.G_RET_STS_ERROR;
1112 x_mesg_token_tbl := l_mesg_token_tbl;
1113 RETURN;
1114 END IF;
1115
1116 l_rev_comp_unexp_rec.DG_Sequence_Id :=
1117 DG.delete_group_sequence_id;
1118 l_rev_comp_unexp_rec.DG_Description := DG.description;
1119
1120 END LOOP;
1121
1122 IF l_rev_comp_unexp_rec.DG_Sequence_Id <> FND_API.G_MISS_NUM
1123 THEN
1124 l_dg_sequence_id := l_rev_comp_unexp_rec.DG_Sequence_Id;
1125 ELSE
1126 l_dg_sequence_id := NULL;
1127 Error_Handler.Add_Error_Token
1128 ( p_message_name => 'NEW_DELETE_GROUP'
1129 , p_mesg_token_tbl => l_mesg_token_Tbl
1130 , x_mesg_token_tbl => l_mesg_token_tbl
1131 , p_message_type => 'W' /* Warning */
1132 );
1133 END IF;
1134
1135 -- bug 5199643
1136 select assembly_type into l_assembly_type
1137 from bom_structures_b
1138 where bill_sequence_id = l_rev_comp_unexp_rec.bill_sequence_id;
1139
1140 l_dg_sequence_id :=
1141 MODAL_DELETE.DELETE_MANAGER
1142 ( new_group_seq_id => l_dg_sequence_id,
1143 name => l_rev_comp_unexp_rec.Delete_Group_Name,
1144 group_desc => l_rev_comp_unexp_rec.dg_description,
1145 org_id => l_rev_comp_unexp_rec.organization_id,
1146 bom_or_eng => l_assembly_type, /*dg type must be same as that of bill */
1147 del_type => 4 /* Component */,
1148 ent_bill_seq_id => l_rev_comp_unexp_rec.bill_sequence_id,
1149 ent_rtg_seq_id => NULL,
1150 ent_inv_item_id => l_rev_comp_unexp_rec.revised_item_id,
1151 ent_alt_designator => l_rev_component_rec.alternate_bom_code,
1152 ent_comp_seq_id => l_rev_comp_unexp_rec.component_sequence_id,
1153 ent_op_seq_id => NULL,
1154 user_id => BOM_Globals.Get_User_Id
1155 );
1156
1157 BOMPCMBM.Delete_Related_Pending_Comps(p_src_comp_seq_id => p_rev_comp_unexp_rec.component_sequence_id
1158 , x_Return_Status => x_Return_Status);
1159
1160 END IF ;
1161
1162 --x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1163 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1164
1165 EXCEPTION
1166
1167 WHEN OTHERS THEN
1168 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1169 THEN
1170 Error_Handler.Add_Error_Token
1171 ( p_Message_Name => NULL
1172 , p_Message_Text => 'Error Rev. Comp Delete Row ' ||
1173 SUBSTR(SQLERRM, 1, 100)
1174 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1175 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1176 );
1177 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1178 END IF;
1179 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1180 END Delete_Row;
1181
1182 /*******************************************************
1183 * This is copy of the procedure that is currently used by
1184 * the ECO.
1185 *********************************************************/
1186 Procedure Cancel_Revised_Component (
1187 comp_seq_id number,
1188 user_id number,
1189 login number,
1190 comment varchar2
1191 ) IS
1192 err_text varchar2(2000);
1193 stmt_num number;
1194 Begin
1195 /*
1196 ** insert the cancelled rev components into eng_revised_components
1197 */
1198 stmt_num := 10;
1199 INSERT INTO ENG_REVISED_COMPONENTS (
1200 COMPONENT_SEQUENCE_ID,
1201 COMPONENT_ITEM_ID,
1202 OPERATION_SEQUENCE_NUM,
1203 BILL_SEQUENCE_ID,
1204 CHANGE_NOTICE,
1205 EFFECTIVITY_DATE,
1206 BASIS_TYPE,
1207 COMPONENT_QUANTITY,
1208 COMPONENT_YIELD_FACTOR,
1209 LAST_UPDATE_DATE,
1210 LAST_UPDATED_BY,
1211 CREATION_DATE,
1212 CREATED_BY,
1213 LAST_UPDATE_LOGIN,
1214 CANCELLATION_DATE,
1215 CANCEL_COMMENTS,
1216 OLD_COMPONENT_SEQUENCE_ID,
1217 ITEM_NUM,
1218 WIP_SUPPLY_TYPE,
1219 COMPONENT_REMARKS,
1220 SUPPLY_SUBINVENTORY,
1221 SUPPLY_LOCATOR_ID,
1222 DISABLE_DATE,
1223 ACD_TYPE,
1224 PLANNING_FACTOR,
1225 QUANTITY_RELATED,
1226 SO_BASIS,
1227 OPTIONAL,
1228 MUTUALLY_EXCLUSIVE_OPTIONS,
1229 INCLUDE_IN_COST_ROLLUP,
1230 CHECK_ATP,
1231 SHIPPING_ALLOWED,
1232 REQUIRED_TO_SHIP,
1233 REQUIRED_FOR_REVENUE,
1234 INCLUDE_ON_SHIP_DOCS,
1235 LOW_QUANTITY,
1236 HIGH_QUANTITY,
1237 REVISED_ITEM_SEQUENCE_ID,
1238 ATTRIBUTE_CATEGORY,
1239 ATTRIBUTE1,
1240 ATTRIBUTE2,
1241 ATTRIBUTE3,
1242 ATTRIBUTE4,
1243 ATTRIBUTE5,
1244 ATTRIBUTE6,
1245 ATTRIBUTE7,
1246 ATTRIBUTE8,
1247 ATTRIBUTE9,
1248 ATTRIBUTE10,
1249 ATTRIBUTE11,
1250 ATTRIBUTE12,
1251 ATTRIBUTE13,
1252 ATTRIBUTE14,
1253 ATTRIBUTE15)
1254 SELECT
1255 IC.COMPONENT_SEQUENCE_ID,
1256 IC.COMPONENT_ITEM_ID,
1257 IC.OPERATION_SEQ_NUM,
1258 IC.BILL_SEQUENCE_ID,
1259 IC.CHANGE_NOTICE,
1260 IC.EFFECTIVITY_DATE,
1261 IC.BASIS_TYPE,
1262 IC.COMPONENT_QUANTITY,
1263 IC. COMPONENT_YIELD_FACTOR,
1264 SYSDATE,
1265 user_id,
1266 SYSDATE,
1267 user_id,
1268 login,
1269 sysdate,
1270 comment,
1271 IC.OLD_COMPONENT_SEQUENCE_ID,
1272 IC.ITEM_NUM,
1273 IC.WIP_SUPPLY_TYPE,
1274 IC.COMPONENT_REMARKS,
1275 IC.SUPPLY_SUBINVENTORY,
1276 IC.SUPPLY_LOCATOR_ID,
1277 IC.DISABLE_DATE,
1278 IC.ACD_TYPE,
1279 IC.PLANNING_FACTOR,
1280 IC.QUANTITY_RELATED,
1281 IC.SO_BASIS,
1282 IC.OPTIONAL,
1283 IC.MUTUALLY_EXCLUSIVE_OPTIONS,
1284 IC.INCLUDE_IN_COST_ROLLUP,
1285 IC.CHECK_ATP,
1286 IC.SHIPPING_ALLOWED,
1287 IC.REQUIRED_TO_SHIP,
1288 IC.REQUIRED_FOR_REVENUE,
1289 IC.INCLUDE_ON_SHIP_DOCS,
1290 IC.LOW_QUANTITY,
1291 IC.HIGH_QUANTITY,
1292 IC.REVISED_ITEM_SEQUENCE_ID,
1293 IC.ATTRIBUTE_CATEGORY,
1294 IC.ATTRIBUTE1,
1295 IC.ATTRIBUTE2,
1296 IC.ATTRIBUTE3,
1297 IC.ATTRIBUTE4,
1298 IC.ATTRIBUTE5,
1299 IC.ATTRIBUTE6,
1300 IC.ATTRIBUTE7,
1301 IC.ATTRIBUTE8,
1302 IC.ATTRIBUTE9,
1303 IC.ATTRIBUTE10,
1304 IC.ATTRIBUTE11,
1305 IC.ATTRIBUTE12,
1306 IC.ATTRIBUTE13,
1307 IC.ATTRIBUTE14,
1308 IC.ATTRIBUTE15
1309 FROM BOM_INVENTORY_COMPONENTS IC
1310 WHERE IC.COMPONENT_SEQUENCE_ID = comp_seq_id;
1311 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows inserted into erc');
1312
1313 /*
1314 ** delete from bom_inventory_comps
1315 */
1316 DELETE FROM BOM_INVENTORY_COMPONENTS
1317 WHERE COMPONENT_SEQUENCE_ID = comp_seq_id;
1318 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows delete from bic');
1319
1320 -- Fixed bug 618781.
1321 -- Cancelling of Revised component must also cancel the
1322 -- Subs. components and the reference designators.
1323
1324 /*
1325 ** Delete the Substitute Components and also the Reference Designators
1326 */
1327 DELETE FROM BOM_SUBSTITUTE_COMPONENTS SC
1328 WHERE SC.COMPONENT_SEQUENCE_ID = comp_seq_id;
1329
1330 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows deleted from bsc');
1331
1332 /*
1333 ** delete reference designators of all pending revised items on ECO
1334 */
1335 stmt_num := 30;
1336 DELETE FROM BOM_REFERENCE_DESIGNATORS RD
1337 WHERE RD.COMPONENT_SEQUENCE_ID = comp_seq_id;
1338 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows deleted from rfd');
1339
1340 EXCEPTION
1341 WHEN OTHERS THEN
1342 rollback;
1343 err_text := 'Cancel_Revised_Component' || '(' || stmt_num || ')' ||
1344 SQLERRM;
1345 FND_MESSAGE.SET_NAME('BOM', 'BOM_SQL_ERR');
1346 FND_MESSAGE.SET_TOKEN('ENTITY', err_text);
1347 APP_EXCEPTION.RAISE_EXCEPTION;
1348 END Cancel_Revised_Component;
1349
1350 PROCEDURE Cancel_Component( p_component_sequence_id IN NUMBER
1351 , p_cancel_comments IN VARCHAR2
1352 , p_user_id IN NUMBER
1353 , p_login_id IN NUMBER
1354 )
1355 IS
1356 BEGIN
1357 Cancel_Revised_Component
1358 ( comp_seq_id => p_component_sequence_id,
1359 user_id => p_user_id,
1360 login => p_login_id,
1361 comment => p_cancel_comments
1362 );
1363
1364 END Cancel_Component;
1365
1366 PROCEDURE Perform_Writes( p_rev_component_rec IN
1367 Bom_Bo_Pub.Rev_Component_Rec_Type
1368 , p_rev_comp_unexp_rec IN
1369 Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
1370 , p_control_rec IN
1371 Bom_Bo_Pub.Control_Rec_Type
1372 := BOM_BO_PUB.G_DEFAULT_CONTROL_REC
1373 , x_Mesg_Token_Tbl IN OUT NOCOPY
1374 Error_Handler.Mesg_Token_Tbl_Type
1375 , x_Return_Status IN OUT NOCOPY VARCHAR2
1376 )
1377 IS
1378 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1379 l_Rev_component_Rec Bom_Bo_Pub.Rev_Component_rec_Type;
1380 l_rev_comp_unexp_rec Bom_Bo_Pub.rev_comp_unexposed_rec_type;
1381 l_return_status VARCHAR2(1);
1382 l_assembly_type NUMBER;
1383 l_Comp_Seq_Id NUMBER;
1384 l_Token_Tbl Error_Handler.Token_Tbl_Type; -- Added by MK on 11/13/00
1385 l_bom_item_type NUMBER;
1386 l_Structure_Type_Name VARCHAR2(30);
1387 l_Assembly_Item_Id NUMBER;
1388 l_Organization_Id NUMBER;
1389 l_Structure_Name VARCHAR2(30);
1390 l_error_message VARCHAR2(512);
1391
1392
1393 CURSOR c_CheckBillExists IS
1394 SELECT 1
1395 FROM sys.dual
1396 WHERE NOT EXISTS
1397 ( SELECT bill_sequence_id
1398 FROM bom_bill_of_materials
1399 WHERE assembly_item_id =
1400 l_rev_comp_unexp_rec.revised_item_id
1401 AND organization_id =
1402 l_rev_comp_unexp_rec.organization_id
1403 AND NVL(alternate_bom_designator, 'NONE') =
1404 NVL(l_rev_component_rec.alternate_bom_code,
1405 'NONE')
1406 );
1407 l_bill_sequence_id NUMBER;
1408 CURSOR GetBillSeqId IS
1409 SELECT bom_inventory_components_s.nextval bill_sequence_id
1410 FROM sys.dual;
1411
1412 err_text varchar2(2000);
1413 err_code varchar2(100);
1414 BEGIN
1415 l_rev_component_rec := p_rev_component_rec;
1416 l_rev_comp_unexp_rec := p_rev_comp_unexp_rec;
1417 l_return_status := FND_API.G_RET_STS_SUCCESS;
1418
1419 l_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
1420 l_Token_Tbl(1).Token_Value := l_rev_component_rec.component_item_name;
1421
1422
1423 IF l_Rev_Component_Rec.Transaction_Type = BOM_GLOBALS.G_OPR_CREATE THEN
1424
1425 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Test Harness: Executing Insert Row. . . '); END IF;
1426
1427 IF Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_ECO_BO
1428 THEN
1429 FOR CheckBillExists IN c_CheckBillExists LOOP
1430 -- Loop executes then the bill does not exist.
1431 -- Procedure Create_New_Bill
1432 /* Bug 1742811
1433 ECO BO is not in Sync with Form with respect to the BOM Type
1434 being created .ECO Form Creates BOM based on Change Order Type.
1435 Below fix made to get the assembly tupe of BOM based on Change Order
1436 Type of ECO
1437 */
1438 select assembly_type
1439 INTO l_assembly_type
1440 --from eng_change_order_types
1441 from eng_change_order_types_vl
1442 where change_order_type_id =
1443 (select change_order_type_id
1444 from eng_engineering_changes
1445 where change_notice =
1446 l_rev_component_rec.eco_name
1447 and organization_id =
1448 l_rev_comp_unexp_rec.organization_id);
1449
1450 /*
1451 SELECT decode(eng_item_flag, 'N', 1, 2)
1452 INTO l_assembly_type
1453 FROM mtl_system_items
1454 WHERE inventory_item_id = l_rev_comp_unexp_rec.revised_item_id
1455 AND organization_id = l_rev_comp_unexp_rec.organization_id;
1456 */
1457 IF p_control_rec.caller_type = 'FORM'
1458 THEN
1459 FOR X_id IN GetBillSeqId LOOP
1460 l_rev_comp_unexp_rec.bill_sequence_id :=
1461 X_id.bill_sequence_id;
1462 END LOOP;
1463
1464
1465 -- Message Name is changed by MK on 11/02/00
1466 Error_Handler.Add_Error_Token
1467 ( p_Message_Name => 'ENG_NEW_PRIMARY_CREATED' -- 'BOM_ECO_CREATE_BOM'
1468 , p_Message_Text => NULL
1469 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1470 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1471 );
1472 ELSE
1473
1474 --
1475 -- Log a warning indicating that a new bill has been created
1476 -- as a result of the component being added.
1477 --
1478 -- Message Name is changed by MK on 11/02/00
1479 Error_Handler.Add_Error_Token
1480 ( p_Message_Name => 'ENG_NEW_PRIMARY_CREATED' -- 'BOM_NEW_PRIMARY_CREATED'
1481 , p_Message_Text => NULL
1482 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1483 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1484 , p_message_type => 'W' -- Parameter added as fix for Bug - 3267190
1485 );
1486 END IF;
1487
1488 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('BOM_Component_Util: Creating New Bill. . . ');
1489 END IF;
1490 Bom_Bom_Component_Util.Create_New_Bill
1491 ( p_assembly_item_id =>
1492 l_rev_comp_unexp_rec.revised_item_id
1493 , p_organization_id =>
1494 l_rev_comp_unexp_rec.organization_id
1495 , p_pending_from_ecn =>
1496 l_rev_component_rec.eco_name
1497 , p_bill_sequence_id =>
1498 l_rev_comp_unexp_rec.bill_sequence_id
1499 , p_common_bill_sequence_id =>
1500 l_rev_comp_unexp_rec.bill_sequence_id
1501 , p_assembly_type => l_assembly_type
1502 , p_last_update_date => SYSDATE
1503 , p_last_updated_by => BOM_Globals.Get_User_Id
1504 , p_creation_date => SYSDATE
1505 , p_created_by => BOM_Globals.Get_User_Id
1506 , p_revised_item_seq_id =>
1507 l_rev_comp_unexp_rec.revised_item_sequence_id
1508 , p_original_system_reference =>
1509 l_rev_component_rec.original_system_reference);
1510 END LOOP;
1511 END IF;
1512
1513 Insert_Row
1514 ( p_Rev_component_rec => l_Rev_Component_Rec
1515 , p_Rev_Comp_Unexp_Rec => l_Rev_Comp_Unexp_Rec
1516 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1517 , x_return_status => l_Return_Status
1518 );
1519
1520 If (x_return_Status = FND_API.G_RET_STS_SUCCESS) Then
1521
1522 Begin
1523 SELECT BOM_ITEM_TYPE
1524 INTO l_bom_item_type
1525 FROM MTL_SYSTEM_ITEMS_B
1526 WHERE INVENTORY_ITEM_ID = l_rev_comp_unexp_rec.revised_item_id
1527 AND ORGANIZATION_ID = l_rev_comp_unexp_rec.organization_id;
1528
1529 If l_bom_item_type = BOM_Globals.G_PRODUCT_FAMILY Then
1530 Product_Family_PKG.Update_PF_Item_Id
1531 (X_Inventory_Item_Id => l_Rev_Comp_Unexp_Rec.component_item_id,
1532 X_Organization_Id => l_rev_comp_unexp_rec.organization_id,
1533 X_PF_Item_Id => l_rev_comp_unexp_rec.revised_item_id,
1534 X_Trans_Type => NULL,
1535 X_Error_Msg => err_text,
1536 X_Error_Code => err_code);
1537 End if;
1538
1539 EXCEPTION
1540 WHEN OTHERS THEN
1541 err_text := 'Update product family Item id error' || SQLERRM;
1542 FND_MESSAGE.SET_NAME('BOM', 'BOM_SQL_ERR');
1543 FND_MESSAGE.SET_TOKEN('ENTITY', err_text);
1544 APP_EXCEPTION.RAISE_EXCEPTION;
1545 End;
1546 End if;
1547 ELSIF l_Rev_Component_Rec.Transaction_Type = BOM_GLOBALS.G_OPR_UPDATE
1548 THEN
1549
1550 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Test Harness: Executing Update Row. . . '); END IF;
1551
1552 Update_Row
1553 ( p_Rev_component_rec => l_Rev_Component_Rec
1554 , p_Rev_Comp_Unexp_Rec => l_Rev_Comp_Unexp_Rec
1555 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1556 , x_return_status => l_Return_Status
1557 );
1558 ELSIF l_Rev_Component_Rec.Transaction_Type = BOM_GLOBALS.G_OPR_DELETE
1559 THEN
1560
1561 -- dbms_output.put_line('Test Harness: Executing Delete Row. . . ');
1562
1563 /* Commented out by MK on 06/01/2001
1564 -- to support deleting thr DeleteGroup
1565 Delete_Row
1566 ( p_component_sequence_id =>
1567 l_Rev_Comp_Unexp_Rec.Component_Sequence_Id
1568 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1569 , x_return_status => l_Return_Status
1570 );
1571 */
1572
1573 Delete_Row
1574 ( p_Rev_component_rec => l_Rev_Component_Rec
1575 , p_Rev_Comp_Unexp_Rec => l_Rev_Comp_Unexp_Rec
1576 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1577 , x_return_status => l_Return_Status
1578 );
1579
1580 ELSIF l_Rev_Component_Rec.Transaction_Type = BOM_GLOBALS.G_OPR_CANCEL
1581 THEN
1582 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Perform Cancel Component . . .'); END IF;
1583
1584 --
1585 -- Fetch Component Sequence Id
1586 --
1587 SELECT component_sequence_id
1588 INTO l_comp_seq_id
1589 FROM bom_inventory_components
1590 WHERE component_item_id =
1591 l_rev_comp_unexp_rec.component_item_id
1592 AND bill_sequence_id = l_rev_comp_unexp_rec.bill_sequence_id
1593 AND operation_seq_num =
1594 l_rev_component_rec.operation_sequence_number
1595 AND effectivity_date =
1596 l_rev_component_rec.start_Effective_date;
1597
1598 --
1599 -- Log a warning indicating reference designators and
1600 -- substitute components will also get deleted.
1601 --
1602 Error_Handler.Add_Error_Token
1603 ( p_Message_Name => 'BOM_COMP_CANCEL_DEL_CHILDREN'
1604 , p_Message_Text => NULL
1605 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1606 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1607 , p_Token_Tbl => l_Token_Tbl -- Added by MK on 11/13/00
1608 , p_message_type => 'W' -- Added by MK on 11/13/00
1609 );
1610
1611 Bom_Bom_Component_Util.Cancel_Component
1612 ( p_component_sequence_id =>
1613 l_comp_seq_id
1614 , p_cancel_comments =>
1615 l_rev_component_rec.cancel_comments
1616 , p_user_id =>
1617 BOM_Globals.Get_User_ID
1618 , p_login_id =>
1619 BOM_Globals.Get_Login_ID
1620 );
1621
1622 END IF;
1623
1624 /********************************************************************
1625 -- If the structure type is Packaging Hierarchy the we will do the
1626 -- following operations.
1627 ********************************************************************/
1628 IF l_Rev_Component_Rec.Transaction_Type = BOM_GLOBALS.G_OPR_CREATE
1629 OR l_Rev_Component_Rec.Transaction_Type = BOM_GLOBALS.G_OPR_UPDATE
1630 OR l_Rev_Component_Rec.Transaction_Type = BOM_GLOBALS.G_OPR_DELETE
1631 THEN
1632 SELECT STRUCTURE_TYPE_NAME,
1633 ASSEMBLY_ITEM_ID,
1634 ORGANIZATION_ID,
1635 ALTERNATE_BOM_DESIGNATOR
1636 INTO
1637 l_Structure_Type_Name,
1638 l_Assembly_Item_Id,
1639 l_Organization_Id,
1640 l_Structure_Name
1641 FROM BOM_STRUCTURE_TYPES_B STRUCT_TYPE,
1642 BOM_STRUCTURES_B BOM_STRUCT
1643 -- Added the below NVL condition for bug 14202819
1644 WHERE NVL(BOM_STRUCT.STRUCTURE_TYPE_ID, 1) = STRUCT_TYPE.STRUCTURE_TYPE_ID
1645 AND BOM_STRUCT.BILL_SEQUENCE_ID = l_Rev_Comp_Unexp_Rec.BILL_SEQUENCE_ID;
1646
1647 IF (l_Structure_Type_Name ='Packaging Hierarchy') THEN
1648 l_error_message := NULL;
1649 BOM_GTIN_RULES.Perform_Rollup (
1650 p_item_id => l_Rev_Comp_Unexp_Rec.component_item_id
1651 ,p_organization_id => l_Organization_Id
1652 ,p_parent_item_id => l_Assembly_Item_Id
1653 ,p_structure_type_name => l_Structure_Type_Name
1654 ,p_transaction_type => l_Rev_Component_Rec.Transaction_Type
1655 ,p_structure_name => l_Structure_Name
1656 ,x_error_message => l_error_message
1657 );
1658 IF l_error_message IS NOT NULL AND l_error_message <> '' THEN
1659 l_Token_Tbl(1).Token_Name := 'ERROR_MESSAGE';
1660 l_Token_Tbl(1).Token_Value := l_error_message;
1661
1662 Error_Handler.Add_Error_Token
1663 ( p_message_name => 'BOM_VALIDATION_FAILURE'
1664 , p_Mesg_Token_Tbl=> l_Mesg_Token_Tbl
1665 , x_Mesg_Token_Tbl=> l_Mesg_Token_Tbl
1666 , p_Token_Tbl => l_Token_Tbl
1667 );
1668 l_return_status := FND_API.G_RET_STS_ERROR;
1669 RETURN;
1670 END IF;
1671
1672 IF l_Rev_Component_Rec.Transaction_Type = BOM_GLOBALS.G_OPR_CREATE THEN
1673
1674 BOM_GTIN_RULES.Update_Top_GTIN (
1675 p_organization_id => l_Organization_Id
1676 ,p_component_item_id => l_Rev_Comp_Unexp_Rec.component_item_id
1677 ,p_parent_item_id => l_Assembly_Item_Id
1678 ,p_structure_name => l_Structure_Name
1679 );
1680 END IF;
1681
1682 BOM_GTIN_RULES.Check_GTIN_Attributes (
1683 p_bill_sequence_id => l_rev_comp_unexp_rec.bill_sequence_id
1684 ,p_assembly_item_id => l_Assembly_Item_Id
1685 ,p_organization_id => l_Organization_Id
1686 ,p_alternate_bom_code => l_Structure_Name
1687 ,p_component_item_id => l_Rev_Comp_Unexp_Rec.component_item_id
1688 ,x_return_status => l_return_status
1689 ,x_error_message => l_error_message
1690 );
1691 IF l_return_status <> 'S' THEN
1692 l_Token_Tbl(1).Token_Name := 'ERROR_MESSAGE';
1693 l_Token_Tbl(1).Token_Value := l_error_message;
1694
1695 Error_Handler.Add_Error_Token
1696 ( p_message_name => 'BOM_VALIDATION_FAILURE'
1697 , p_Mesg_Token_Tbl=> l_Mesg_Token_Tbl
1698 , x_Mesg_Token_Tbl=> l_Mesg_Token_Tbl
1699 , p_Token_Tbl => l_Token_Tbl
1700 );
1701 l_return_status := FND_API.G_RET_STS_ERROR;
1702 END IF;
1703 END IF;
1704 END IF;
1705
1706
1707 x_return_status := l_return_status;
1708 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1709 END Perform_Writes;
1710
1711
1712 /******************************************************************************
1713 * Procedure : Create_New_Bill
1714 * Parameters IN : Assembly Item ID
1715 * Organization ID
1716 * Pending from ECN
1717 * common_bill_sequence_id
1718 * assembly_type
1719 * WHO columns
1720 * revised_item_sequence_id
1721 * Purpose : This procedure will be called when a revised component is
1722 * the first component being added on a revised item. This
1723 * procedure will create a Bill and update the revised item
1724 * information indicating that bill for this revised item now
1725 * exists.
1726 ******************************************************************************/
1727 PROCEDURE Create_New_Bill( p_assembly_item_id IN NUMBER
1728 , p_organization_id IN NUMBER
1729 , p_pending_from_ecn IN VARCHAR2
1730 , p_bill_sequence_id IN NUMBER
1731 , p_common_bill_sequence_id IN NUMBER
1732 , p_assembly_type IN NUMBER
1733 , p_last_update_date IN DATE
1734 , p_last_updated_by IN NUMBER
1735 , p_creation_date IN DATE
1736 , p_created_by IN NUMBER
1737 , p_revised_item_seq_id IN NUMBER
1738 , p_original_system_reference IN VARCHAR2
1739 , p_alternate_bom_code IN VARCHAR2 := NULL)
1740 IS
1741 CURSOR c_structure_type( p_alternate_bom_code IN VARCHAR2
1742 , p_organization_id IN NUMBER
1743 )
1744 IS
1745 SELECT structure_type_id
1746 FROM bom_alternate_designators
1747 WHERE nvl(alternate_designator_code,'XXXXXXXXXXX') =
1748 nvl(p_alternate_bom_code, 'XXXXXXXXXXX' )
1749 and organization_id = p_organization_id;
1750
1751 l_structure_type_id number;
1752 -- Added for bug 4550996
1753 CURSOR c_effectivity_control IS
1754 SELECT effectivity_control
1755 FROM mtl_system_items
1756 WHERE inventory_item_id = p_assembly_item_id
1757 AND organization_id = p_organization_id;
1758
1759 l_effectivity_control NUMBER;
1760 -- End bug 4550996
1761 BEGIN
1762
1763 if bom_globals.get_debug = 'Y'
1764 then
1765 error_handler.write_debug('Rev_Comps: default structure type_id for alt: ' || p_alternate_bom_code);
1766 end if;
1767
1768 if(p_alternate_bom_code is null)
1769 then
1770 for l_structure_type in c_structure_type( p_alternate_bom_code => p_alternate_bom_code
1771 , p_organization_id => -1
1772 )
1773 loop
1774 l_structure_type_id := l_structure_type.structure_type_id;
1775
1776 end loop;
1777 else
1778 for l_structure_type in c_structure_type( p_alternate_bom_code => p_alternate_bom_code
1779 , p_organization_id => p_organization_id
1780 )
1781 loop
1782 l_structure_type_id := l_structure_type.structure_type_id;
1783
1784 end loop;
1785 end if;
1786
1787 if bom_globals.get_debug = 'Y'
1788 then
1789 error_handler.write_debug('Rev_Comps: defaulted structure type id: ' || l_structure_type_id);
1790 end if;
1791
1792 -- Added for fix of bug 4550996
1793 OPEN c_effectivity_control;
1794 FETCH c_effectivity_control INTO l_effectivity_control;
1795 CLOSE c_effectivity_control;
1796 IF bom_globals.get_debug = 'Y' THEN
1797 Error_handler.Write_debug('Rev_Comps: defaulted effectivity control: ' || l_effectivity_control);
1798 END IF;
1799 -- End fix of bug 4550996
1800
1801 INSERT INTO Bom_Bill_Of_Materials
1802 ( assembly_item_id
1803 , organization_id
1804 , pending_from_ecn
1805 , bill_sequence_id
1806 , common_bill_sequence_id
1807 , assembly_type
1808 , last_update_date
1809 , last_updated_by
1810 , creation_date
1811 , created_by
1812 , original_system_reference
1813 , structure_type_id
1814 , effectivity_control -- bug 4550996
1815 , implementation_date -- bug 4550996
1816 , alternate_bom_designator
1817 , source_bill_sequence_id --Bug 4550996
1818 , pk1_value --Bug 4550996
1819 , pk2_value --Bug 4550996
1820 )
1821 VALUES ( p_assembly_item_id
1822 , p_organization_id
1823 , p_pending_from_ecn
1824 , p_bill_sequence_id
1825 , p_common_bill_sequence_id
1826 , p_assembly_type
1827 , p_last_update_date
1828 , p_last_updated_by
1829 , p_creation_date
1830 , p_created_by
1831 , p_original_system_reference
1832 , l_structure_type_id
1833 , l_effectivity_control -- bug 4550996
1834 , sysdate -- bug 4550996
1835 , p_alternate_bom_code
1836 , p_bill_sequence_id
1837 , p_assembly_item_id
1838 , p_organization_id
1839 );
1840
1841 UPDATE eng_revised_items
1842 SET bill_sequence_id = p_bill_sequence_id
1843 WHERE revised_item_sequence_id = p_revised_item_seq_id;
1844
1845 END Create_New_Bill;
1846
1847 /***************************************************************************
1848 * Procedure : Convert_Miss_To_Null
1849 * Parameters IN : Revised component exposed column record
1850 * Revised component unexposed column record
1851 * Parameters OUT: Revised Component exposed column record
1852 * Revised component unexposed column record.
1853 * Purpose : This procedure will convert all missing columns to NULL.
1854 ****************************************************************************/
1855 PROCEDURE Convert_Miss_To_Null
1856 ( p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
1857 , p_Rev_Comp_Unexp_Rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
1858 , x_Rev_Component_Rec IN OUT NOCOPY Bom_Bo_Pub.Rev_Component_Rec_Type
1859 , x_Rev_Comp_Unexp_Rec IN OUT NOCOPY Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
1860 )
1861 IS
1862 l_rev_component_rec Bom_Bo_Pub.Rev_Component_Rec_Type :=
1863 p_rev_component_rec;
1864 l_Rev_Comp_Unexp_Rec Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type :=
1865 p_Rev_Comp_Unexp_Rec;
1866 BEGIN
1867
1868 IF l_rev_component_rec.supply_subinventory = FND_API.G_MISS_CHAR THEN
1869 l_rev_component_rec.supply_subinventory := NULL;
1870 END IF;
1871
1872 IF l_rev_component_rec.required_for_revenue = FND_API.G_MISS_NUM THEN
1873 l_rev_component_rec.required_for_revenue := NULL;
1874 END IF;
1875
1876 IF l_rev_component_rec.maximum_allowed_quantity = FND_API.G_MISS_NUM THEN
1877 l_rev_component_rec.maximum_allowed_quantity := NULL;
1878 END IF;
1879
1880
1881 IF l_rev_component_rec.wip_supply_type = FND_API.G_MISS_NUM THEN
1882 l_rev_component_rec.wip_supply_type := NULL;
1883 END IF;
1884
1885 IF l_rev_component_rec.location_name = FND_API.G_MISS_NUM THEN
1886 l_rev_comp_unexp_rec.supply_locator_id := NULL;
1887 END IF;
1888
1889 IF l_rev_component_rec.operation_sequence_number = FND_API.G_MISS_NUM THEN
1890 l_rev_component_rec.operation_sequence_number := NULL;
1891 END IF;
1892
1893 IF l_rev_component_rec.item_sequence_number = FND_API.G_MISS_NUM THEN
1894 l_rev_component_rec.item_sequence_number := NULL;
1895 END IF;
1896
1897 IF l_rev_component_rec.quantity_per_assembly = FND_API.G_MISS_NUM THEN
1898 l_rev_component_rec.quantity_per_assembly := NULL;
1899 END IF;
1900
1901 IF l_rev_component_rec.projected_yield = FND_API.G_MISS_NUM THEN
1902 l_rev_component_rec.projected_yield := NULL;
1903 END IF;
1904
1905 IF l_rev_component_rec.comments = FND_API.G_MISS_CHAR THEN
1906 l_rev_component_rec.comments := NULL;
1907 END IF;
1908
1909 IF l_rev_component_rec.start_effective_date = FND_API.G_MISS_DATE THEN
1910 l_rev_component_rec.start_effective_date := NULL;
1911 END IF;
1912
1913 IF l_rev_component_rec.disable_date = FND_API.G_MISS_DATE THEN
1914 l_rev_component_rec.disable_date := NULL;
1915 END IF;
1916
1917 IF l_rev_component_rec.attribute_category = FND_API.G_MISS_CHAR THEN
1918 l_rev_component_rec.attribute_category := NULL;
1919 END IF;
1920
1921 IF l_rev_component_rec.attribute1 = FND_API.G_MISS_CHAR THEN
1922 l_rev_component_rec.attribute1 := NULL;
1923 END IF;
1924
1925 IF l_rev_component_rec.attribute2 = FND_API.G_MISS_CHAR THEN
1926 l_rev_component_rec.attribute2 := NULL;
1927 END IF;
1928
1929 IF l_rev_component_rec.attribute3 = FND_API.G_MISS_CHAR THEN
1930 l_rev_component_rec.attribute3 := NULL;
1931 END IF;
1932
1933 IF l_rev_component_rec.attribute4 = FND_API.G_MISS_CHAR THEN
1934 l_rev_component_rec.attribute4 := NULL;
1935 END IF;
1936
1937 IF l_rev_component_rec.attribute5 = FND_API.G_MISS_CHAR THEN
1938 l_rev_component_rec.attribute5 := NULL;
1939 END IF;
1940
1941 IF l_rev_component_rec.attribute6 = FND_API.G_MISS_CHAR THEN
1942 l_rev_component_rec.attribute6 := NULL;
1943 END IF;
1944
1945 IF l_rev_component_rec.attribute7 = FND_API.G_MISS_CHAR THEN
1946 l_rev_component_rec.attribute7 := NULL;
1947 END IF;
1948
1949 IF l_rev_component_rec.attribute8 = FND_API.G_MISS_CHAR THEN
1950 l_rev_component_rec.attribute8 := NULL;
1951 END IF;
1952
1953 IF l_rev_component_rec.attribute9 = FND_API.G_MISS_CHAR THEN
1954 l_rev_component_rec.attribute9 := NULL;
1955 END IF;
1956
1957 IF l_rev_component_rec.attribute10 = FND_API.G_MISS_CHAR THEN
1958 l_rev_component_rec.attribute10 := NULL;
1959 END IF;
1960
1961 IF l_rev_component_rec.attribute11 = FND_API.G_MISS_CHAR THEN
1962 l_rev_component_rec.attribute11 := NULL;
1963 END IF;
1964
1965 IF l_rev_component_rec.attribute12 = FND_API.G_MISS_CHAR THEN
1966 l_rev_component_rec.attribute12 := NULL;
1967 END IF;
1968
1969 IF l_rev_component_rec.attribute13 = FND_API.G_MISS_CHAR THEN
1970 l_rev_component_rec.attribute13 := NULL;
1971 END IF;
1972
1973 IF l_rev_component_rec.attribute14 = FND_API.G_MISS_CHAR THEN
1974 l_rev_component_rec.attribute14 := NULL;
1975 END IF;
1976
1977 IF l_rev_component_rec.attribute15 = FND_API.G_MISS_CHAR THEN
1978 l_rev_component_rec.attribute15 := NULL;
1979 END IF;
1980
1981 IF l_rev_component_rec.planning_percent = FND_API.G_MISS_NUM THEN
1982 l_rev_component_rec.planning_percent := NULL;
1983 END IF;
1984
1985 IF l_rev_component_rec.quantity_related = FND_API.G_MISS_NUM THEN
1986 l_rev_component_rec.quantity_related := NULL;
1987 END IF;
1988
1989 IF l_rev_component_rec.so_basis = FND_API.G_MISS_NUM THEN
1990 l_rev_component_rec.so_basis := NULL;
1991 END IF;
1992
1993 IF l_rev_component_rec.optional = FND_API.G_MISS_NUM THEN
1994 l_rev_component_rec.optional := NULL;
1995 END IF;
1996
1997 IF l_rev_component_rec.mutually_exclusive = FND_API.G_MISS_NUM THEN
1998 l_rev_component_rec.mutually_exclusive := NULL;
1999 END IF;
2000
2001 IF l_rev_component_rec.include_in_cost_rollup = FND_API.G_MISS_NUM THEN
2002 l_rev_component_rec.include_in_cost_rollup := NULL;
2003 END IF;
2004
2005 IF l_rev_component_rec.check_atp = FND_API.G_MISS_NUM THEN
2006 l_rev_component_rec.check_atp := NULL;
2007 END IF;
2008
2009 IF l_rev_component_rec.shipping_allowed = FND_API.G_MISS_NUM THEN
2010 l_rev_component_rec.shipping_allowed := NULL;
2011 END IF;
2012
2013 IF l_rev_component_rec.required_to_ship = FND_API.G_MISS_NUM THEN
2014 l_rev_component_rec.required_to_ship := NULL;
2015 END IF;
2016
2017 IF l_rev_component_rec.include_on_ship_docs = FND_API.G_MISS_NUM THEN
2018 l_rev_component_rec.include_on_ship_docs := NULL;
2019 END IF;
2020
2021 IF l_rev_component_rec.minimum_allowed_quantity = FND_API.G_MISS_NUM THEN
2022 l_rev_component_rec.minimum_allowed_quantity := NULL;
2023 END IF;
2024
2025 IF l_rev_component_rec.acd_type = FND_API.G_MISS_NUM THEN
2026 l_rev_component_rec.acd_type := NULL;
2027 END IF;
2028
2029 -- Added in 11.5.9 by ADEY
2030 IF l_rev_component_rec.auto_request_material = FND_API.G_MISS_CHAR THEN
2031 l_rev_component_rec.auto_request_material := NULL;
2032 END IF;
2033
2034 IF l_rev_component_rec.Suggested_Vendor_Name = FND_API.G_MISS_CHAR THEN --- Deepu
2035 l_rev_component_rec.Suggested_Vendor_Name := NULL;
2036 l_Rev_Comp_Unexp_Rec.Vendor_Id := NULL;
2037 END IF;
2038
2039 /*
2040 IF l_rev_component_rec.purchasing_category_id = FND_API.G_MISS_NUM THEN --- Deepu
2041 l_rev_component_rec.purchasing_category_id := NULL;
2042 END IF;
2043 */
2044 IF l_rev_component_rec.Unit_Price = FND_API.G_MISS_NUM THEN --- Deepu
2045 l_rev_component_rec.Unit_Price := NULL;
2046 END IF;
2047
2048 x_Rev_Component_Rec := l_rev_component_rec;
2049 x_Rev_Comp_Unexp_Rec := l_Rev_Comp_Unexp_Rec;
2050
2051 END Convert_Miss_To_Null;
2052
2053
2054 FUNCTION Get_Operation_Leadtime (
2055 p_assembly_item_id IN NUMBER,
2056 p_organization_id IN NUMBER,
2057 p_alternate_bom_code IN VARCHAR2,
2058 p_operation_seq_num IN NUMBER) RETURN NUMBER
2059 IS
2060
2061 l_leadtime_percent NUMBER;
2062
2063 BEGIN
2064
2065 SELECT OPERATION_LEAD_TIME_PERCENT
2066 into
2067 l_leadtime_percent
2068 FROM
2069 bom_operation_sequences bos
2070 WHERE
2071 bos.routing_sequence_id =
2072 (
2073 select common_routing_sequence_id
2074 from bom_operational_routings
2075 where assembly_item_id = p_assembly_item_id
2076 and organization_id = p_organization_id
2077 and nvl(alternate_routing_designator,
2078 nvl(p_alternate_bom_code, 'NONE')) =
2079 nvl(p_alternate_bom_code, 'NONE')
2080 and (p_alternate_bom_code is null
2081 or (p_alternate_bom_code is not null
2082 and (alternate_routing_designator =
2083 p_alternate_bom_code
2084 or not exists
2085 (select null
2086 from bom_operational_routings bor2
2087 where bor2.assembly_item_id =
2088 p_assembly_item_id
2089 and bor2.organization_id = p_organization_id
2090 and bor2.alternate_routing_designator = p_alternate_bom_code
2091 )
2092 )
2093 )
2094 )
2095 )
2096 AND bos.operation_type = 1 --bug: 4161149
2097 AND bos.operation_seq_num = p_operation_seq_num
2098 and bos.implementation_date is not null
2099 and bos.EFFECTIVITY_DATE <= sysdate
2100 AND nvl(disable_date, sysdate+1)
2101 > sysdate;
2102
2103 return l_leadtime_percent;
2104 EXCEPTION
2105 when no_data_found then
2106 return null; -- BUG : 4559089
2107
2108
2109 END;
2110
2111
2112 END Bom_Bom_Component_Util;