[Home] [Help]
PACKAGE BODY: APPS.BOM_BOM_COMPONENT_UTIL
Source
1 PACKAGE BODY Bom_Bom_Component_Util AS
2 /* $Header: BOMUCMPB.pls 120.11 2007/02/23 08:55:30 vhymavat 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
326 , l_rev_component_rec.attribute14
323 , l_rev_component_rec.attribute11
324 , l_rev_component_rec.attribute12
325 , l_rev_component_rec.attribute13
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;
440 /* need to populate Operation Lead Time percent corresponding to the operation
437 l_operation_seq_num NUMBER;
438 BEGIN
439
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
533 , To_End_Item_Unit_Number =
530 ,p_rev_component_rec.from_end_item_unit_number
531 ,p_rev_component_rec.new_from_end_item_unit_number
532 )
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 WHERE COMPONENT_SEQUENCE_ID = p_Rev_Comp_Unexp_Rec.component_sequence_id
548 ;
549 --For non-referencing common boms.
550 BOMPCMBM.Update_Related_Components( p_src_comp_seq_id => p_Rev_Comp_Unexp_Rec.component_sequence_id
551 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
552 , x_Return_Status => x_Return_Status
553 );
554 -- x_Return_Status := FND_API.G_RET_STS_SUCCESS;
555 EXCEPTION
556
557 WHEN OTHERS THEN
558
559 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
560 THEN
561 l_err_text := G_PKG_NAME ||
562 ' : Utility (Component Update) ' ||
563 SUBSTR(SQLERRM, 1, 200);
564 Error_Handler.Add_Error_Token
565 ( p_Message_Name => NULL
566 , p_Message_Text => l_err_text
567 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
568 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
569 );
570 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
571 END IF;
572
573 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
574 END Update_Row;
575
576 /*****************************************************************************
577 * Procedure : Insert_Row
578 * Parameters IN : Revised Component exposed column record
579 * Revised Component unexposed column record
580 * Parameters OUT: Mesg_Token_Tbl
581 * Return_Status
582 * Purpose : This procedure will insert a record in the bom_inventory-
583 * component table. Any errors will be filled in the Mesg_Token
584 * Tbl and returned with a return_status of U
585 *****************************************************************************/
586 PROCEDURE Insert_Row
587 ( p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
588 , p_Rev_Comp_Unexp_Rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
589 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
590 , x_Return_Status IN OUT NOCOPY VARCHAR2
591 )
592 IS
593
594 l_err_text VARCHAR2(2000);
595 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
596 l_Bo_Id VARCHAR2(3);
597
598 l_old_component_sequence_id NUMBER; -- Bug 2820641
599
600 l_object_revision_id NUMBER;
601 l_minor_revision_id NUMBER;
602 l_comp_revision_id NUMBER;
603 l_comp_minor_revision_id NUMBER;
604 l_operation_leadtime NUMBER := NULL;
605 l_operation_seq_num NUMBER;
606
607 BEGIN
608
609 l_Bo_Id := Bom_Globals.Get_Bo_Identifier;
610
611
612 -- bug 2820641
613 -- BOM form : BOMFDBOM.fmb won't insert the Old_component_sequence_id.
614 -- ENG form : ENGFDECN.fmb will always inserts Old_component_sequence_id.
615
616 if l_Bo_Id = BOM_Globals.G_ECO_BO THEN
617 if (p_rev_comp_Unexp_rec.old_component_sequence_id = FND_API.G_MISS_NUM)
618 or (p_rev_comp_Unexp_rec.old_component_sequence_id is NULL) then
619 l_old_component_sequence_id := p_rev_comp_Unexp_rec.component_sequence_id;
620 else
621 l_old_component_sequence_id := p_rev_comp_Unexp_rec.old_component_sequence_id;
622 end if;
623 else
624 if (p_rev_comp_Unexp_rec.old_component_sequence_id = FND_API.G_MISS_NUM) then
625 l_old_component_sequence_id := NULL;
626 else
627 l_old_component_sequence_id := p_rev_comp_Unexp_rec.old_component_sequence_id;
628 end if;
629 end if;
630 -- bug 2820641
631
632 --/* added for BOM Defaulting for WEB-ADI Open Interface calls */
633
634
635 BOM_GLOBALS.GET_DEF_REV_ATTRS
636 ( p_bill_sequence_id => p_rev_comp_Unexp_rec.bill_sequence_id
637 , p_comp_item_id => p_rev_comp_Unexp_rec.component_item_id
638 , p_effectivity_date => nvl(p_rev_component_rec.start_effective_date,SYSDATE)
639 , x_object_revision_id => l_object_revision_id
640 , x_minor_revision_id => l_minor_revision_id
641 , x_comp_revision_id => l_comp_revision_id
642 , x_comp_minor_revision_id => l_comp_minor_revision_id
643 );
644
645 /* need to populate Operation Lead Time percent corresponding to the operation
646 -vhymavat bug3537394 */
647 IF((p_rev_component_rec.new_operation_sequence_number IS NULL) OR
648 (p_rev_component_rec.new_operation_sequence_number =FND_API.G_MISS_NUM) ) THEN
649
653 ELSE
650 IF (( p_rev_component_rec.operation_sequence_number IS NULL) OR
651 ( p_rev_component_rec.operation_sequence_number =FND_API.G_MISS_NUM)) THEN
652 l_operation_seq_num := 1;
654 l_operation_seq_num := p_rev_component_rec.operation_sequence_number;
655 END IF;
656 ELSE
657 l_operation_seq_num :=p_rev_component_rec.new_operation_sequence_number;
658 END IF;
659
660 IF(l_operation_seq_num <>1 and p_rev_component_rec.acd_type is null) THEN
661 l_operation_leadtime :=
662 Get_Operation_Leadtime (
663 p_assembly_item_id =>p_rev_comp_Unexp_rec.revised_item_id
664 ,p_organization_id =>p_rev_comp_Unexp_rec.organization_id
665 ,p_alternate_bom_code =>p_rev_component_rec.alternate_bom_code
666 ,p_operation_seq_num => l_operation_seq_num
667 );
668
669 END IF;
670
671 --bug:3254815 Update request id, prog id, prog appl id and prog update date.
672 INSERT INTO BOM_INVENTORY_COMPONENTS
673 ( SUPPLY_SUBINVENTORY
674 , OPERATION_LEAD_TIME_PERCENT
675 , REVISED_ITEM_SEQUENCE_ID
676 , COST_FACTOR
677 , REQUIRED_FOR_REVENUE
678 , HIGH_QUANTITY
679 , COMPONENT_SEQUENCE_ID
680 , PROGRAM_APPLICATION_ID
681 , WIP_SUPPLY_TYPE
682 , SUPPLY_LOCATOR_ID
683 , BOM_ITEM_TYPE
684 , OPERATION_SEQ_NUM
685 , COMPONENT_ITEM_ID
686 , LAST_UPDATE_DATE
687 , LAST_UPDATED_BY
688 , CREATION_DATE
689 , CREATED_BY
690 , LAST_UPDATE_LOGIN
691 , ITEM_NUM
692 , BASIS_TYPE
693 , COMPONENT_QUANTITY
694 , COMPONENT_YIELD_FACTOR
695 , COMPONENT_REMARKS
696 , EFFECTIVITY_DATE
697 , CHANGE_NOTICE
698 , IMPLEMENTATION_DATE
699 , DISABLE_DATE
700 , ATTRIBUTE_CATEGORY
701 , ATTRIBUTE1
702 , ATTRIBUTE2
703 , ATTRIBUTE3
704 , ATTRIBUTE4
705 , ATTRIBUTE5
706 , ATTRIBUTE6
707 , ATTRIBUTE7
708 , ATTRIBUTE8
709 , ATTRIBUTE9
710 , ATTRIBUTE10
711 , ATTRIBUTE11
712 , ATTRIBUTE12
713 , ATTRIBUTE13
714 , ATTRIBUTE14
715 , ATTRIBUTE15
716 , PLANNING_FACTOR
717 , QUANTITY_RELATED
718 , SO_BASIS
719 , OPTIONAL
720 , MUTUALLY_EXCLUSIVE_OPTIONS
721 , INCLUDE_IN_COST_ROLLUP
722 , CHECK_ATP
723 , SHIPPING_ALLOWED
724 , REQUIRED_TO_SHIP
725 , INCLUDE_ON_SHIP_DOCS
726 , INCLUDE_ON_BILL_DOCS
727 , LOW_QUANTITY
728 , ACD_TYPE
729 , OLD_COMPONENT_SEQUENCE_ID
730 , BILL_SEQUENCE_ID
731 , REQUEST_ID
732 , PROGRAM_ID
733 , PROGRAM_UPDATE_DATE
734 , PICK_COMPONENTS
735 , Original_System_Reference
736 , From_End_Item_Unit_Number
737 , To_End_Item_Unit_Number
738 , Eco_For_Production -- Added by MK
739 , Enforce_Int_Requirements
740 , Auto_Request_Material -- Added in 11.5.9 by ADEY
741 , Obj_Name -- Added by hgelli.
742 , pk1_value
743 , pk2_value
744 , Suggested_Vendor_Name --- Deepu
745 , Vendor_Id --- Deepu
746 -- , Purchasing_Category_id --- Deepu
747 , Unit_Price --- Deepu
748 ,from_object_revision_id
749 , from_minor_revision_id
750 --,component_item_revision_id
751 --,component_minor_revision_id
752 , common_component_sequence_id
753 )
754 VALUES
755 ( p_rev_component_rec.supply_subinventory
756 , l_operation_leadtime
757 , p_rev_comp_unexp_rec.revised_item_sequence_id
758 , NULL /* Cost Factor */
759 , p_rev_component_rec.required_for_revenue
760 , p_rev_component_rec.maximum_allowed_quantity
761 , p_rev_comp_Unexp_rec.component_sequence_id
762 , BOM_Globals.Get_Prog_AppId
763 , p_rev_component_rec.wip_supply_type
764 , DECODE(p_rev_comp_Unexp_rec.supply_locator_id, FND_API.G_MISS_NUM,
765 NULL, p_rev_comp_Unexp_rec.supply_locator_id)
766 , p_rev_comp_Unexp_rec.bom_item_type
767 , l_operation_seq_num
768 , p_rev_comp_Unexp_rec.component_item_id
769 , SYSDATE /* Last Update Date */
770 , BOM_Globals.Get_User_Id /* Last Updated By */
771 , SYSDATE /* Creation Date */
772 , BOM_Globals.Get_User_Id /* Created By */
773 , BOM_Globals.Get_User_Id /* Last Update Login */
774 , DECODE(p_rev_component_rec.item_sequence_number, FND_API.G_MISS_NUM,
775 1, NULL,1,p_rev_component_rec.item_sequence_number)
776 , DECODE(p_rev_component_rec.basis_type,FND_API.G_MISS_NUM,
777 NULL,p_rev_component_rec.basis_type)
778 , p_rev_component_rec.quantity_per_assembly
779 , p_rev_component_rec.projected_yield
783 , DECODE(l_Bo_Id,
780 , p_rev_component_rec.comments
781 , nvl(p_rev_component_rec.start_effective_date,SYSDATE) --2169237
782 , p_rev_component_rec.Eco_Name
784 Bom_Globals.G_BOM_BO,
785 Decode( p_rev_comp_Unexp_rec.bom_implementation_date,
786 null,
787 null,
788 SYSDATE),
789 NULL
790 ) /* Implementation Date */
791 /*
792 , DECODE(l_Bo_Id,
793 Bom_Globals.G_BOM_BO,
794 SYSDATE,
795 NULL
796 ) -- Implementation Date
797 */
798 , p_rev_component_rec.disable_date
799 , p_rev_component_rec.attribute_category
800 , p_rev_component_rec.attribute1
801 , p_rev_component_rec.attribute2
802 , p_rev_component_rec.attribute3
803 , p_rev_component_rec.attribute4
804 , p_rev_component_rec.attribute5
805 , p_rev_component_rec.attribute6
806 , p_rev_component_rec.attribute7
807 , p_rev_component_rec.attribute8
808 , p_rev_component_rec.attribute9
809 , p_rev_component_rec.attribute10
810 , p_rev_component_rec.attribute11
811 , p_rev_component_rec.attribute12
812 , p_rev_component_rec.attribute13
813 , p_rev_component_rec.attribute14
814 , p_rev_component_rec.attribute15
815 , p_rev_component_rec.planning_percent
816 , p_rev_component_rec.quantity_related
817 , p_rev_component_rec.so_basis
818 , p_rev_component_rec.optional
819 , p_rev_component_rec.mutually_exclusive
820 , p_rev_component_rec.include_in_cost_rollup
821 , p_rev_component_rec.check_atp
822 , p_rev_component_rec.shipping_allowed
823 , p_rev_component_rec.required_to_ship
824 , p_rev_component_rec.include_on_ship_docs
825 , NULL /* Include On Bill Docs */
826 , p_rev_component_rec.minimum_allowed_quantity
827 , p_rev_component_rec.acd_type
828 -- , DECODE( p_rev_comp_Unexp_rec.old_component_sequence_id
829 -- , FND_API.G_MISS_NUM
830 -- , NULL
831 -- ,p_rev_comp_Unexp_rec.old_component_sequence_id
832 -- )
833 , l_old_component_sequence_id
834 , p_rev_comp_Unexp_rec.bill_sequence_id
835 , Fnd_Global.Conc_Request_Id /* Request Id */
836 , BOM_Globals.Get_Prog_Id
837 , SYSDATE /* program_update_date */
838 , p_rev_comp_Unexp_rec.pick_components
839 , p_rev_component_rec.original_system_reference
840 , DECODE( p_rev_component_rec.from_end_item_unit_number
841 , FND_API.G_MISS_CHAR
842 , null
843 , p_rev_component_rec.from_end_item_unit_number
844 )
845 , DECODE( p_rev_component_rec.to_end_item_unit_number
846 , FND_API.G_MISS_CHAR
847 , null
848 , p_rev_component_rec.to_end_item_unit_number
849 )
850 , BOM_Globals.Get_Eco_For_Production
851 -- DECODE( l_Bo_Id, BOM_Globals.G_ECO_BO, l_Eco_For_Production, 2) /* Eco for Production flag */
852 , p_rev_comp_Unexp_rec.Enforce_Int_Requirements_Code
853 , p_rev_component_rec.auto_request_material -- Added in 11.5.9 by ADEY
854 , NULL-- Added by hgelli. Identifies this record as Bom Component.
855 , p_rev_comp_Unexp_rec.component_item_id
856 , p_rev_comp_Unexp_rec.organization_id
857 , p_rev_component_rec.Suggested_Vendor_Name --- Deepu
858 , p_rev_comp_Unexp_rec.Vendor_Id --- Deepu
859 -- , p_rev_component_rec.purchasing_category_id --- Deepu
860 , p_rev_component_rec.Unit_Price --- Deepu
861 ,l_object_revision_id
862 ,l_minor_revision_id
863 --,l_comp_revision_id
864 --,l_comp_minor_revision_id
865 ,(Select common_component_sequence_id from bom_inventory_components where
866 component_sequence_id = l_old_component_sequence_id)
867 );
868 --For non-referencing common boms.
869 BOMPCMBM.Insert_Related_Components( p_src_bill_seq_id => p_rev_comp_Unexp_rec.bill_sequence_id
870 , p_src_comp_seq_id => p_rev_comp_Unexp_rec.component_sequence_id
871 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
872 , x_Return_Status => x_Return_Status
873 );
874 --x_Return_Status := FND_API.G_RET_STS_SUCCESS;
875
876 EXCEPTION
877
878 WHEN OTHERS THEN
879 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Unexpected Error occured in Insert . . .' || SQLERRM); END IF;
880
881 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
882 THEN
883 l_err_text := G_PKG_NAME ||
884 ' : Utility (Component Insert) ' ||
885 SUBSTR(SQLERRM, 1, 200);
886 Error_Handler.Add_Error_Token
887 ( p_Message_Name => NULL
888 , p_Message_Text => l_err_text
889 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
890 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
891 );
892 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
893 END IF;
894
895 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
896
897 END Insert_Row;
898
902 * Parameters OUT: Mesg_Token_Tbl
899 /****************************************************************************
900 * Procedure : Delete_Row
901 * Parameters IN : Revised Component Key
903 * Return_Status
904 * Purpose : Will delete a revised component record for a ECO.
905 * Delete operation will not delete a record in production which
906 * is already implemented.
907 *****************************************************************************/
908 /* Comment out by MK to support delet
909 PROCEDURE Delete_Row
910 ( p_component_sequence_id IN NUMBER
911 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
912 , x_Return_Status IN OUT NOCOPY VARCHAR2
913 )
914 */
915
916 PROCEDURE Delete_Row
917 ( p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
918 , p_rev_comp_unexp_rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
919 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
920 , x_Return_Status IN OUT NOCOPY VARCHAR2
921 )
922
923 IS
924
925 l_dummy number;
926 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
927
928 -- added by MK on 06/04/2001
929 Cursor CheckGroup is
930 SELECT description,
931 delete_group_sequence_id,
932 delete_type
933 FROM bom_delete_groups
934 WHERE delete_group_name = p_rev_comp_unexp_rec.delete_group_name
935 AND organization_id = p_rev_comp_unexp_rec.organization_id;
936
937 l_dg_sequence_id NUMBER;
938 l_rev_component_rec Bom_Bo_Pub.Rev_Component_Rec_Type ;
939 l_rev_comp_unexp_rec Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type ;
940 l_assembly_type NUMBER;
941
942
943
944
945 BEGIN
946
947
948 --
949 -- Initialize Common Record and Status
950 --
951 l_rev_component_rec := p_rev_component_rec ;
952 l_rev_comp_unexp_rec := p_rev_comp_unexp_rec ;
953 x_return_status := FND_API.G_RET_STS_SUCCESS;
954
955 IF Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_ECO_BO
956 THEN
957
958 DELETE FROM BOM_INVENTORY_COMPONENTS
959 WHERE COMPONENT_SEQUENCE_ID = p_rev_comp_unexp_rec.component_sequence_id;
960 -- p_component_sequence_id ;
961
962 /******************************************************************
963 -- Also delete the Substitute components and Reference designators
964 -- by first logging a warning notifying the user of the cascaded
965 -- Delete.
966 *******************************************************************/
967
968 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
969 -- This is a warning.
970 THEN
971 Error_Handler.Add_Error_Token
972 ( p_Message_Name => 'BOM_COMP_DEL_CHILDREN'
973 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
974 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
975 , p_message_type => 'W' -- Added by MK on 11/13/00
976 );
977 END IF;
978
979 DELETE from bom_reference_designators
980 WHERE component_sequence_id = p_rev_comp_unexp_rec.component_sequence_id ;
981 -- p_component_sequence_id ;
982
983 DELETE from bom_substitute_components
984 WHERE component_Sequence_id = p_rev_comp_unexp_rec.component_sequence_id ;
985 -- p_component_sequence_id ;
986
987
988 -- In Bom BO, the user is not allowed to delete components directly.
989 -- The user can use delete group functionality for deleting components.
990 ELSIF Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_BOM_BO
991 THEN
992
993
994 FOR DG IN CheckGroup
995 LOOP
996 IF DG.delete_type <> 4 /* Component */ then
997
998 Error_Handler.Add_Error_Token
999 ( p_message_name => 'BOM_DUPLICATE_DELETE_GROUP'
1000 , p_mesg_token_tbl => l_mesg_token_Tbl
1001 , x_mesg_token_tbl => l_mesg_token_tbl
1002 );
1003
1004 x_return_status := FND_API.G_RET_STS_ERROR;
1005 x_mesg_token_tbl := l_mesg_token_tbl;
1006 RETURN;
1007 END IF;
1008
1009 l_rev_comp_unexp_rec.DG_Sequence_Id :=
1010 DG.delete_group_sequence_id;
1011 l_rev_comp_unexp_rec.DG_Description := DG.description;
1012
1013 END LOOP;
1014
1015 IF l_rev_comp_unexp_rec.DG_Sequence_Id <> FND_API.G_MISS_NUM
1016 THEN
1017 l_dg_sequence_id := l_rev_comp_unexp_rec.DG_Sequence_Id;
1018 ELSE
1019 l_dg_sequence_id := NULL;
1020 Error_Handler.Add_Error_Token
1021 ( p_message_name => 'NEW_DELETE_GROUP'
1022 , p_mesg_token_tbl => l_mesg_token_Tbl
1023 , x_mesg_token_tbl => l_mesg_token_tbl
1024 , p_message_type => 'W' /* Warning */
1025 );
1026 END IF;
1027
1028 -- bug 5199643
1029 select assembly_type into l_assembly_type
1030 from bom_structures_b
1034 MODAL_DELETE.DELETE_MANAGER
1031 where bill_sequence_id = l_rev_comp_unexp_rec.bill_sequence_id;
1032
1033 l_dg_sequence_id :=
1035 ( new_group_seq_id => l_dg_sequence_id,
1036 name => l_rev_comp_unexp_rec.Delete_Group_Name,
1037 group_desc => l_rev_comp_unexp_rec.dg_description,
1038 org_id => l_rev_comp_unexp_rec.organization_id,
1039 bom_or_eng => l_assembly_type, /*dg type must be same as that of bill */
1040 del_type => 4 /* Component */,
1041 ent_bill_seq_id => l_rev_comp_unexp_rec.bill_sequence_id,
1042 ent_rtg_seq_id => NULL,
1043 ent_inv_item_id => l_rev_comp_unexp_rec.revised_item_id,
1044 ent_alt_designator => l_rev_component_rec.alternate_bom_code,
1045 ent_comp_seq_id => l_rev_comp_unexp_rec.component_sequence_id,
1046 ent_op_seq_id => NULL,
1047 user_id => BOM_Globals.Get_User_Id
1048 );
1049
1050 BOMPCMBM.Delete_Related_Pending_Comps(p_src_comp_seq_id => p_rev_comp_unexp_rec.component_sequence_id
1051 , x_Return_Status => x_Return_Status);
1052
1053 END IF ;
1054
1055 --x_Return_Status := FND_API.G_RET_STS_SUCCESS;
1056 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1057
1058 EXCEPTION
1059
1060 WHEN OTHERS THEN
1061 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1062 THEN
1063 Error_Handler.Add_Error_Token
1064 ( p_Message_Name => NULL
1065 , p_Message_Text => 'Error Rev. Comp Delete Row ' ||
1066 SUBSTR(SQLERRM, 1, 100)
1067 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1068 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1069 );
1070 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1071 END IF;
1072 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
1073 END Delete_Row;
1074
1075 /*******************************************************
1076 * This is copy of the procedure that is currently used by
1077 * the ECO.
1078 *********************************************************/
1079 Procedure Cancel_Revised_Component (
1080 comp_seq_id number,
1081 user_id number,
1082 login number,
1083 comment varchar2
1084 ) IS
1085 err_text varchar2(2000);
1086 stmt_num number;
1087 Begin
1088 /*
1089 ** insert the cancelled rev components into eng_revised_components
1090 */
1091 stmt_num := 10;
1092 INSERT INTO ENG_REVISED_COMPONENTS (
1093 COMPONENT_SEQUENCE_ID,
1094 COMPONENT_ITEM_ID,
1095 OPERATION_SEQUENCE_NUM,
1096 BILL_SEQUENCE_ID,
1097 CHANGE_NOTICE,
1098 EFFECTIVITY_DATE,
1099 BASIS_TYPE,
1100 COMPONENT_QUANTITY,
1101 COMPONENT_YIELD_FACTOR,
1102 LAST_UPDATE_DATE,
1103 LAST_UPDATED_BY,
1104 CREATION_DATE,
1105 CREATED_BY,
1106 LAST_UPDATE_LOGIN,
1107 CANCELLATION_DATE,
1108 CANCEL_COMMENTS,
1109 OLD_COMPONENT_SEQUENCE_ID,
1110 ITEM_NUM,
1111 WIP_SUPPLY_TYPE,
1112 COMPONENT_REMARKS,
1113 SUPPLY_SUBINVENTORY,
1114 SUPPLY_LOCATOR_ID,
1115 DISABLE_DATE,
1116 ACD_TYPE,
1117 PLANNING_FACTOR,
1118 QUANTITY_RELATED,
1119 SO_BASIS,
1120 OPTIONAL,
1121 MUTUALLY_EXCLUSIVE_OPTIONS,
1122 INCLUDE_IN_COST_ROLLUP,
1123 CHECK_ATP,
1124 SHIPPING_ALLOWED,
1125 REQUIRED_TO_SHIP,
1126 REQUIRED_FOR_REVENUE,
1127 INCLUDE_ON_SHIP_DOCS,
1128 LOW_QUANTITY,
1129 HIGH_QUANTITY,
1130 REVISED_ITEM_SEQUENCE_ID,
1131 ATTRIBUTE_CATEGORY,
1132 ATTRIBUTE1,
1133 ATTRIBUTE2,
1134 ATTRIBUTE3,
1135 ATTRIBUTE4,
1136 ATTRIBUTE5,
1137 ATTRIBUTE6,
1138 ATTRIBUTE7,
1139 ATTRIBUTE8,
1140 ATTRIBUTE9,
1141 ATTRIBUTE10,
1142 ATTRIBUTE11,
1143 ATTRIBUTE12,
1144 ATTRIBUTE13,
1145 ATTRIBUTE14,
1146 ATTRIBUTE15)
1147 SELECT
1148 IC.COMPONENT_SEQUENCE_ID,
1149 IC.COMPONENT_ITEM_ID,
1150 IC.OPERATION_SEQ_NUM,
1151 IC.BILL_SEQUENCE_ID,
1152 IC.CHANGE_NOTICE,
1153 IC.EFFECTIVITY_DATE,
1154 IC.BASIS_TYPE,
1155 IC.COMPONENT_QUANTITY,
1156 IC. COMPONENT_YIELD_FACTOR,
1157 SYSDATE,
1158 user_id,
1159 SYSDATE,
1160 user_id,
1161 login,
1162 sysdate,
1163 comment,
1164 IC.OLD_COMPONENT_SEQUENCE_ID,
1165 IC.ITEM_NUM,
1166 IC.WIP_SUPPLY_TYPE,
1167 IC.COMPONENT_REMARKS,
1168 IC.SUPPLY_SUBINVENTORY,
1169 IC.SUPPLY_LOCATOR_ID,
1170 IC.DISABLE_DATE,
1171 IC.ACD_TYPE,
1172 IC.PLANNING_FACTOR,
1173 IC.QUANTITY_RELATED,
1174 IC.SO_BASIS,
1175 IC.OPTIONAL,
1179 IC.SHIPPING_ALLOWED,
1176 IC.MUTUALLY_EXCLUSIVE_OPTIONS,
1177 IC.INCLUDE_IN_COST_ROLLUP,
1178 IC.CHECK_ATP,
1180 IC.REQUIRED_TO_SHIP,
1181 IC.REQUIRED_FOR_REVENUE,
1182 IC.INCLUDE_ON_SHIP_DOCS,
1183 IC.LOW_QUANTITY,
1184 IC.HIGH_QUANTITY,
1185 IC.REVISED_ITEM_SEQUENCE_ID,
1186 IC.ATTRIBUTE_CATEGORY,
1187 IC.ATTRIBUTE1,
1188 IC.ATTRIBUTE2,
1189 IC.ATTRIBUTE3,
1190 IC.ATTRIBUTE4,
1191 IC.ATTRIBUTE5,
1192 IC.ATTRIBUTE6,
1193 IC.ATTRIBUTE7,
1194 IC.ATTRIBUTE8,
1195 IC.ATTRIBUTE9,
1196 IC.ATTRIBUTE10,
1197 IC.ATTRIBUTE11,
1198 IC.ATTRIBUTE12,
1199 IC.ATTRIBUTE13,
1200 IC.ATTRIBUTE14,
1201 IC.ATTRIBUTE15
1202 FROM BOM_INVENTORY_COMPONENTS IC
1203 WHERE IC.COMPONENT_SEQUENCE_ID = comp_seq_id;
1204 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows inserted into erc');
1205
1206 /*
1207 ** delete from bom_inventory_comps
1208 */
1209 DELETE FROM BOM_INVENTORY_COMPONENTS
1210 WHERE COMPONENT_SEQUENCE_ID = comp_seq_id;
1211 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows delete from bic');
1212
1213 -- Fixed bug 618781.
1214 -- Cancelling of Revised component must also cancel the
1215 -- Subs. components and the reference designators.
1216
1217 /*
1218 ** Delete the Substitute Components and also the Reference Designators
1219 */
1220 DELETE FROM BOM_SUBSTITUTE_COMPONENTS SC
1221 WHERE SC.COMPONENT_SEQUENCE_ID = comp_seq_id;
1222
1223 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows deleted from bsc');
1224
1225 /*
1226 ** delete reference designators of all pending revised items on ECO
1227 */
1228 stmt_num := 30;
1229 DELETE FROM BOM_REFERENCE_DESIGNATORS RD
1230 WHERE RD.COMPONENT_SEQUENCE_ID = comp_seq_id;
1231 -- dbms_output.put_line(SQL%ROWCOUNT || ' rows deleted from rfd');
1232
1233 EXCEPTION
1234 WHEN OTHERS THEN
1235 rollback;
1236 err_text := 'Cancel_Revised_Component' || '(' || stmt_num || ')' ||
1237 SQLERRM;
1238 FND_MESSAGE.SET_NAME('BOM', 'BOM_SQL_ERR');
1239 FND_MESSAGE.SET_TOKEN('ENTITY', err_text);
1240 APP_EXCEPTION.RAISE_EXCEPTION;
1241 END Cancel_Revised_Component;
1242
1243 PROCEDURE Cancel_Component( p_component_sequence_id IN NUMBER
1244 , p_cancel_comments IN VARCHAR2
1245 , p_user_id IN NUMBER
1246 , p_login_id IN NUMBER
1247 )
1248 IS
1249 BEGIN
1250 Cancel_Revised_Component
1251 ( comp_seq_id => p_component_sequence_id,
1252 user_id => p_user_id,
1253 login => p_login_id,
1254 comment => p_cancel_comments
1255 );
1256
1257 END Cancel_Component;
1258
1259 PROCEDURE Perform_Writes( p_rev_component_rec IN
1260 Bom_Bo_Pub.Rev_Component_Rec_Type
1261 , p_rev_comp_unexp_rec IN
1262 Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
1263 , p_control_rec IN
1264 Bom_Bo_Pub.Control_Rec_Type
1265 := BOM_BO_PUB.G_DEFAULT_CONTROL_REC
1266 , x_Mesg_Token_Tbl IN OUT NOCOPY
1267 Error_Handler.Mesg_Token_Tbl_Type
1268 , x_Return_Status IN OUT NOCOPY VARCHAR2
1269 )
1270 IS
1271 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
1272 l_Rev_component_Rec Bom_Bo_Pub.Rev_Component_rec_Type;
1273 l_rev_comp_unexp_rec Bom_Bo_Pub.rev_comp_unexposed_rec_type;
1274 l_return_status VARCHAR2(1);
1275 l_assembly_type NUMBER;
1276 l_Comp_Seq_Id NUMBER;
1277 l_Token_Tbl Error_Handler.Token_Tbl_Type; -- Added by MK on 11/13/00
1278 l_bom_item_type NUMBER;
1279 l_Structure_Type_Name VARCHAR2(30);
1280 l_Assembly_Item_Id NUMBER;
1281 l_Organization_Id NUMBER;
1282 l_Structure_Name VARCHAR2(30);
1283 l_error_message VARCHAR2(512);
1284
1285
1286 CURSOR c_CheckBillExists IS
1287 SELECT 1
1288 FROM sys.dual
1289 WHERE NOT EXISTS
1290 ( SELECT bill_sequence_id
1291 FROM bom_bill_of_materials
1292 WHERE assembly_item_id =
1293 l_rev_comp_unexp_rec.revised_item_id
1294 AND organization_id =
1295 l_rev_comp_unexp_rec.organization_id
1296 AND NVL(alternate_bom_designator, 'NONE') =
1297 NVL(l_rev_component_rec.alternate_bom_code,
1298 'NONE')
1299 );
1300 l_bill_sequence_id NUMBER;
1301 CURSOR GetBillSeqId IS
1302 SELECT bom_inventory_components_s.nextval bill_sequence_id
1303 FROM sys.dual;
1304
1305 err_text varchar2(2000);
1306 err_code varchar2(100);
1307 BEGIN
1308 l_rev_component_rec := p_rev_component_rec;
1309 l_rev_comp_unexp_rec := p_rev_comp_unexp_rec;
1310 l_return_status := FND_API.G_RET_STS_SUCCESS;
1311
1312 l_Token_Tbl(1).Token_Name := 'REVISED_COMPONENT_NAME';
1313 l_Token_Tbl(1).Token_Value := l_rev_component_rec.component_item_name;
1314
1315
1319
1316 IF l_Rev_Component_Rec.Transaction_Type = BOM_GLOBALS.G_OPR_CREATE THEN
1317
1318 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Test Harness: Executing Insert Row. . . '); END IF;
1320 IF Bom_Globals.Get_Bo_Identifier = Bom_Globals.G_ECO_BO
1321 THEN
1322 FOR CheckBillExists IN c_CheckBillExists LOOP
1323 -- Loop executes then the bill does not exist.
1324 -- Procedure Create_New_Bill
1325 /* Bug 1742811
1326 ECO BO is not in Sync with Form with respect to the BOM Type
1327 being created .ECO Form Creates BOM based on Change Order Type.
1328 Below fix made to get the assembly tupe of BOM based on Change Order
1329 Type of ECO
1330 */
1331 select assembly_type
1332 INTO l_assembly_type
1333 --from eng_change_order_types
1334 from eng_change_order_types_vl
1335 where change_order_type_id =
1336 (select change_order_type_id
1337 from eng_engineering_changes
1338 where change_notice =
1339 l_rev_component_rec.eco_name
1340 and organization_id =
1341 l_rev_comp_unexp_rec.organization_id);
1342
1343 /*
1344 SELECT decode(eng_item_flag, 'N', 1, 2)
1345 INTO l_assembly_type
1346 FROM mtl_system_items
1347 WHERE inventory_item_id = l_rev_comp_unexp_rec.revised_item_id
1348 AND organization_id = l_rev_comp_unexp_rec.organization_id;
1349 */
1350 IF p_control_rec.caller_type = 'FORM'
1351 THEN
1352 FOR X_id IN GetBillSeqId LOOP
1353 l_rev_comp_unexp_rec.bill_sequence_id :=
1354 X_id.bill_sequence_id;
1355 END LOOP;
1356
1357
1358 -- Message Name is changed by MK on 11/02/00
1359 Error_Handler.Add_Error_Token
1360 ( p_Message_Name => 'ENG_NEW_PRIMARY_CREATED' -- 'BOM_ECO_CREATE_BOM'
1361 , p_Message_Text => NULL
1362 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1363 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1364 );
1365 ELSE
1366
1367 --
1368 -- Log a warning indicating that a new bill has been created
1369 -- as a result of the component being added.
1370 --
1371 -- Message Name is changed by MK on 11/02/00
1372 Error_Handler.Add_Error_Token
1373 ( p_Message_Name => 'ENG_NEW_PRIMARY_CREATED' -- 'BOM_NEW_PRIMARY_CREATED'
1374 , p_Message_Text => NULL
1375 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1376 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1377 , p_message_type => 'W' -- Parameter added as fix for Bug - 3267190
1378 );
1379 END IF;
1380
1381 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('BOM_Component_Util: Creating New Bill. . . ');
1382 END IF;
1383 Bom_Bom_Component_Util.Create_New_Bill
1384 ( p_assembly_item_id =>
1385 l_rev_comp_unexp_rec.revised_item_id
1386 , p_organization_id =>
1387 l_rev_comp_unexp_rec.organization_id
1388 , p_pending_from_ecn =>
1389 l_rev_component_rec.eco_name
1390 , p_bill_sequence_id =>
1391 l_rev_comp_unexp_rec.bill_sequence_id
1392 , p_common_bill_sequence_id =>
1393 l_rev_comp_unexp_rec.bill_sequence_id
1394 , p_assembly_type => l_assembly_type
1395 , p_last_update_date => SYSDATE
1396 , p_last_updated_by => BOM_Globals.Get_User_Id
1397 , p_creation_date => SYSDATE
1398 , p_created_by => BOM_Globals.Get_User_Id
1399 , p_revised_item_seq_id =>
1400 l_rev_comp_unexp_rec.revised_item_sequence_id
1401 , p_original_system_reference =>
1402 l_rev_component_rec.original_system_reference);
1403 END LOOP;
1404 END IF;
1405
1406 Insert_Row
1407 ( p_Rev_component_rec => l_Rev_Component_Rec
1408 , p_Rev_Comp_Unexp_Rec => l_Rev_Comp_Unexp_Rec
1409 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1410 , x_return_status => l_Return_Status
1411 );
1412
1413 If (x_return_Status = FND_API.G_RET_STS_SUCCESS) Then
1414
1415 Begin
1416 SELECT BOM_ITEM_TYPE
1417 INTO l_bom_item_type
1418 FROM MTL_SYSTEM_ITEMS_B
1419 WHERE INVENTORY_ITEM_ID = l_rev_comp_unexp_rec.revised_item_id
1420 AND ORGANIZATION_ID = l_rev_comp_unexp_rec.organization_id;
1421
1422 If l_bom_item_type = BOM_Globals.G_PRODUCT_FAMILY Then
1423 Product_Family_PKG.Update_PF_Item_Id
1424 (X_Inventory_Item_Id => l_Rev_Comp_Unexp_Rec.component_item_id,
1425 X_Organization_Id => l_rev_comp_unexp_rec.organization_id,
1426 X_PF_Item_Id => l_rev_comp_unexp_rec.revised_item_id,
1427 X_Trans_Type => NULL,
1428 X_Error_Msg => err_text,
1429 X_Error_Code => err_code);
1430 End if;
1431
1432 EXCEPTION
1433 WHEN OTHERS THEN
1434 err_text := 'Update product family Item id error' || SQLERRM;
1438 End;
1435 FND_MESSAGE.SET_NAME('BOM', 'BOM_SQL_ERR');
1436 FND_MESSAGE.SET_TOKEN('ENTITY', err_text);
1437 APP_EXCEPTION.RAISE_EXCEPTION;
1439 End if;
1440 ELSIF l_Rev_Component_Rec.Transaction_Type = BOM_GLOBALS.G_OPR_UPDATE
1441 THEN
1442
1443 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Test Harness: Executing Update Row. . . '); END IF;
1444
1445 Update_Row
1446 ( p_Rev_component_rec => l_Rev_Component_Rec
1447 , p_Rev_Comp_Unexp_Rec => l_Rev_Comp_Unexp_Rec
1448 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1449 , x_return_status => l_Return_Status
1450 );
1451 ELSIF l_Rev_Component_Rec.Transaction_Type = BOM_GLOBALS.G_OPR_DELETE
1452 THEN
1453
1454 -- dbms_output.put_line('Test Harness: Executing Delete Row. . . ');
1455
1456 /* Commented out by MK on 06/01/2001
1457 -- to support deleting thr DeleteGroup
1458 Delete_Row
1459 ( p_component_sequence_id =>
1460 l_Rev_Comp_Unexp_Rec.Component_Sequence_Id
1461 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1462 , x_return_status => l_Return_Status
1463 );
1464 */
1465
1466 Delete_Row
1467 ( p_Rev_component_rec => l_Rev_Component_Rec
1468 , p_Rev_Comp_Unexp_Rec => l_Rev_Comp_Unexp_Rec
1469 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1470 , x_return_status => l_Return_Status
1471 );
1472
1473 ELSIF l_Rev_Component_Rec.Transaction_Type = BOM_GLOBALS.G_OPR_CANCEL
1474 THEN
1475 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Perform Cancel Component . . .'); END IF;
1476
1477 --
1478 -- Fetch Component Sequence Id
1479 --
1480 SELECT component_sequence_id
1481 INTO l_comp_seq_id
1482 FROM bom_inventory_components
1483 WHERE component_item_id =
1484 l_rev_comp_unexp_rec.component_item_id
1485 AND bill_sequence_id = l_rev_comp_unexp_rec.bill_sequence_id
1486 AND operation_seq_num =
1487 l_rev_component_rec.operation_sequence_number
1488 AND effectivity_date =
1489 l_rev_component_rec.start_Effective_date;
1490
1491 --
1492 -- Log a warning indicating reference designators and
1493 -- substitute components will also get deleted.
1494 --
1495 Error_Handler.Add_Error_Token
1496 ( p_Message_Name => 'BOM_COMP_CANCEL_DEL_CHILDREN'
1497 , p_Message_Text => NULL
1498 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1499 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
1500 , p_Token_Tbl => l_Token_Tbl -- Added by MK on 11/13/00
1501 , p_message_type => 'W' -- Added by MK on 11/13/00
1502 );
1503
1504 Bom_Bom_Component_Util.Cancel_Component
1505 ( p_component_sequence_id =>
1506 l_comp_seq_id
1507 , p_cancel_comments =>
1508 l_rev_component_rec.cancel_comments
1509 , p_user_id =>
1510 BOM_Globals.Get_User_ID
1511 , p_login_id =>
1512 BOM_Globals.Get_Login_ID
1513 );
1514
1515 END IF;
1516
1517 /********************************************************************
1518 -- If the structure type is Packaging Hierarchy the we will do the
1519 -- following operations.
1520 ********************************************************************/
1521 IF l_Rev_Component_Rec.Transaction_Type = BOM_GLOBALS.G_OPR_CREATE
1522 OR l_Rev_Component_Rec.Transaction_Type = BOM_GLOBALS.G_OPR_UPDATE
1523 OR l_Rev_Component_Rec.Transaction_Type = BOM_GLOBALS.G_OPR_DELETE
1524 THEN
1525 SELECT STRUCTURE_TYPE_NAME,
1526 ASSEMBLY_ITEM_ID,
1527 ORGANIZATION_ID,
1528 ALTERNATE_BOM_DESIGNATOR
1529 INTO
1530 l_Structure_Type_Name,
1531 l_Assembly_Item_Id,
1532 l_Organization_Id,
1533 l_Structure_Name
1534 FROM BOM_STRUCTURE_TYPES_B STRUCT_TYPE,
1535 BOM_STRUCTURES_B BOM_STRUCT
1536 WHERE BOM_STRUCT.STRUCTURE_TYPE_ID = STRUCT_TYPE.STRUCTURE_TYPE_ID
1537 AND BOM_STRUCT.BILL_SEQUENCE_ID = l_Rev_Comp_Unexp_Rec.BILL_SEQUENCE_ID;
1538
1539 IF (l_Structure_Type_Name ='Packaging Hierarchy') THEN
1540 l_error_message := NULL;
1541 BOM_GTIN_RULES.Perform_Rollup (
1542 p_item_id => l_Rev_Comp_Unexp_Rec.component_item_id
1543 ,p_organization_id => l_Organization_Id
1544 ,p_parent_item_id => l_Assembly_Item_Id
1545 ,p_structure_type_name => l_Structure_Type_Name
1546 ,p_transaction_type => l_Rev_Component_Rec.Transaction_Type
1547 ,p_structure_name => l_Structure_Name
1548 ,x_error_message => l_error_message
1549 );
1550 IF l_error_message IS NOT NULL AND l_error_message <> '' THEN
1551 l_Token_Tbl(1).Token_Name := 'ERROR_MESSAGE';
1552 l_Token_Tbl(1).Token_Value := l_error_message;
1553
1554 Error_Handler.Add_Error_Token
1555 ( p_message_name => 'BOM_VALIDATION_FAILURE'
1559 );
1556 , p_Mesg_Token_Tbl=> l_Mesg_Token_Tbl
1557 , x_Mesg_Token_Tbl=> l_Mesg_Token_Tbl
1558 , p_Token_Tbl => l_Token_Tbl
1560 l_return_status := FND_API.G_RET_STS_ERROR;
1561 RETURN;
1562 END IF;
1563
1564 IF l_Rev_Component_Rec.Transaction_Type = BOM_GLOBALS.G_OPR_CREATE THEN
1565
1566 BOM_GTIN_RULES.Update_Top_GTIN (
1567 p_organization_id => l_Organization_Id
1568 ,p_component_item_id => l_Rev_Comp_Unexp_Rec.component_item_id
1569 ,p_parent_item_id => l_Assembly_Item_Id
1570 ,p_structure_name => l_Structure_Name
1571 );
1572 END IF;
1573
1574 BOM_GTIN_RULES.Check_GTIN_Attributes (
1575 p_bill_sequence_id => l_rev_comp_unexp_rec.bill_sequence_id
1576 ,p_assembly_item_id => l_Assembly_Item_Id
1577 ,p_organization_id => l_Organization_Id
1578 ,p_alternate_bom_code => l_Structure_Name
1579 ,p_component_item_id => l_Rev_Comp_Unexp_Rec.component_item_id
1580 ,x_return_status => l_return_status
1581 ,x_error_message => l_error_message
1582 );
1583 IF l_return_status <> 'S' THEN
1584 l_Token_Tbl(1).Token_Name := 'ERROR_MESSAGE';
1585 l_Token_Tbl(1).Token_Value := l_error_message;
1586
1587 Error_Handler.Add_Error_Token
1588 ( p_message_name => 'BOM_VALIDATION_FAILURE'
1589 , p_Mesg_Token_Tbl=> l_Mesg_Token_Tbl
1590 , x_Mesg_Token_Tbl=> l_Mesg_Token_Tbl
1591 , p_Token_Tbl => l_Token_Tbl
1592 );
1593 l_return_status := FND_API.G_RET_STS_ERROR;
1594 END IF;
1595 END IF;
1596 END IF;
1597
1598
1599 x_return_status := l_return_status;
1600 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
1601 END Perform_Writes;
1602
1603
1604 /******************************************************************************
1605 * Procedure : Create_New_Bill
1606 * Parameters IN : Assembly Item ID
1607 * Organization ID
1608 * Pending from ECN
1609 * common_bill_sequence_id
1610 * assembly_type
1611 * WHO columns
1612 * revised_item_sequence_id
1613 * Purpose : This procedure will be called when a revised component is
1614 * the first component being added on a revised item. This
1615 * procedure will create a Bill and update the revised item
1616 * information indicating that bill for this revised item now
1617 * exists.
1618 ******************************************************************************/
1619 PROCEDURE Create_New_Bill( p_assembly_item_id IN NUMBER
1620 , p_organization_id IN NUMBER
1621 , p_pending_from_ecn IN VARCHAR2
1622 , p_bill_sequence_id IN NUMBER
1623 , p_common_bill_sequence_id IN NUMBER
1624 , p_assembly_type IN NUMBER
1625 , p_last_update_date IN DATE
1626 , p_last_updated_by IN NUMBER
1627 , p_creation_date IN DATE
1628 , p_created_by IN NUMBER
1629 , p_revised_item_seq_id IN NUMBER
1630 , p_original_system_reference IN VARCHAR2
1631 , p_alternate_bom_code IN VARCHAR2 := NULL)
1632 IS
1633 CURSOR c_structure_type( p_alternate_bom_code IN VARCHAR2
1634 , p_organization_id IN NUMBER
1635 )
1636 IS
1637 SELECT structure_type_id
1638 FROM bom_alternate_designators
1639 WHERE nvl(alternate_designator_code,'XXXXXXXXXXX') =
1640 nvl(p_alternate_bom_code, 'XXXXXXXXXXX' )
1641 and organization_id = p_organization_id;
1642
1643 l_structure_type_id number;
1644 -- Added for bug 4550996
1645 CURSOR c_effectivity_control IS
1646 SELECT effectivity_control
1647 FROM mtl_system_items
1648 WHERE inventory_item_id = p_assembly_item_id
1649 AND organization_id = p_organization_id;
1650
1651 l_effectivity_control NUMBER;
1652 -- End bug 4550996
1653 BEGIN
1654
1655 if bom_globals.get_debug = 'Y'
1656 then
1657 error_handler.write_debug('Rev_Comps: default structure type_id for alt: ' || p_alternate_bom_code);
1658 end if;
1659
1660 if(p_alternate_bom_code is null)
1661 then
1662 for l_structure_type in c_structure_type( p_alternate_bom_code => p_alternate_bom_code
1663 , p_organization_id => -1
1664 )
1665 loop
1666 l_structure_type_id := l_structure_type.structure_type_id;
1667
1668 end loop;
1669 else
1670 for l_structure_type in c_structure_type( p_alternate_bom_code => p_alternate_bom_code
1671 , p_organization_id => p_organization_id
1672 )
1673 loop
1674 l_structure_type_id := l_structure_type.structure_type_id;
1675
1676 end loop;
1677 end if;
1678
1679 if bom_globals.get_debug = 'Y'
1680 then
1684 -- Added for fix of bug 4550996
1681 error_handler.write_debug('Rev_Comps: defaulted structure type id: ' || l_structure_type_id);
1682 end if;
1683
1685 OPEN c_effectivity_control;
1686 FETCH c_effectivity_control INTO l_effectivity_control;
1687 CLOSE c_effectivity_control;
1688 IF bom_globals.get_debug = 'Y' THEN
1689 Error_handler.Write_debug('Rev_Comps: defaulted effectivity control: ' || l_effectivity_control);
1690 END IF;
1691 -- End fix of bug 4550996
1692
1693 INSERT INTO Bom_Bill_Of_Materials
1694 ( assembly_item_id
1695 , organization_id
1696 , pending_from_ecn
1697 , bill_sequence_id
1698 , common_bill_sequence_id
1699 , assembly_type
1700 , last_update_date
1701 , last_updated_by
1702 , creation_date
1703 , created_by
1704 , original_system_reference
1705 , structure_type_id
1706 , effectivity_control -- bug 4550996
1707 , implementation_date -- bug 4550996
1708 , alternate_bom_designator
1709 , source_bill_sequence_id --Bug 4550996
1710 , pk1_value --Bug 4550996
1711 , pk2_value --Bug 4550996
1712 )
1713 VALUES ( p_assembly_item_id
1714 , p_organization_id
1715 , p_pending_from_ecn
1716 , p_bill_sequence_id
1717 , p_common_bill_sequence_id
1718 , p_assembly_type
1719 , p_last_update_date
1720 , p_last_updated_by
1721 , p_creation_date
1722 , p_created_by
1723 , p_original_system_reference
1724 , l_structure_type_id
1725 , l_effectivity_control -- bug 4550996
1726 , sysdate -- bug 4550996
1727 , p_alternate_bom_code
1728 , p_bill_sequence_id
1729 , p_assembly_item_id
1730 , p_organization_id
1731 );
1732
1733 UPDATE eng_revised_items
1734 SET bill_sequence_id = p_bill_sequence_id
1735 WHERE revised_item_sequence_id = p_revised_item_seq_id;
1736
1737 END Create_New_Bill;
1738
1739 /***************************************************************************
1740 * Procedure : Convert_Miss_To_Null
1741 * Parameters IN : Revised component exposed column record
1742 * Revised component unexposed column record
1743 * Parameters OUT: Revised Component exposed column record
1744 * Revised component unexposed column record.
1745 * Purpose : This procedure will convert all missing columns to NULL.
1746 ****************************************************************************/
1747 PROCEDURE Convert_Miss_To_Null
1748 ( p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
1749 , p_Rev_Comp_Unexp_Rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
1750 , x_Rev_Component_Rec IN OUT NOCOPY Bom_Bo_Pub.Rev_Component_Rec_Type
1751 , x_Rev_Comp_Unexp_Rec IN OUT NOCOPY Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
1752 )
1753 IS
1754 l_rev_component_rec Bom_Bo_Pub.Rev_Component_Rec_Type :=
1755 p_rev_component_rec;
1756 l_Rev_Comp_Unexp_Rec Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type :=
1757 p_Rev_Comp_Unexp_Rec;
1758 BEGIN
1759
1760 IF l_rev_component_rec.supply_subinventory = FND_API.G_MISS_CHAR THEN
1761 l_rev_component_rec.supply_subinventory := NULL;
1762 END IF;
1763
1764 IF l_rev_component_rec.required_for_revenue = FND_API.G_MISS_NUM THEN
1765 l_rev_component_rec.required_for_revenue := NULL;
1766 END IF;
1767
1768 IF l_rev_component_rec.maximum_allowed_quantity = FND_API.G_MISS_NUM THEN
1769 l_rev_component_rec.maximum_allowed_quantity := NULL;
1770 END IF;
1771
1772
1773 IF l_rev_component_rec.wip_supply_type = FND_API.G_MISS_NUM THEN
1774 l_rev_component_rec.wip_supply_type := NULL;
1775 END IF;
1776
1777 IF l_rev_component_rec.location_name = FND_API.G_MISS_NUM THEN
1778 l_rev_comp_unexp_rec.supply_locator_id := NULL;
1779 END IF;
1780
1781 IF l_rev_component_rec.operation_sequence_number = FND_API.G_MISS_NUM THEN
1782 l_rev_component_rec.operation_sequence_number := NULL;
1783 END IF;
1784
1785 IF l_rev_component_rec.item_sequence_number = FND_API.G_MISS_NUM THEN
1786 l_rev_component_rec.item_sequence_number := NULL;
1787 END IF;
1788
1789 IF l_rev_component_rec.quantity_per_assembly = FND_API.G_MISS_NUM THEN
1790 l_rev_component_rec.quantity_per_assembly := NULL;
1791 END IF;
1792
1793 IF l_rev_component_rec.projected_yield = FND_API.G_MISS_NUM THEN
1794 l_rev_component_rec.projected_yield := NULL;
1795 END IF;
1796
1797 IF l_rev_component_rec.comments = FND_API.G_MISS_CHAR THEN
1798 l_rev_component_rec.comments := NULL;
1799 END IF;
1800
1801 IF l_rev_component_rec.start_effective_date = FND_API.G_MISS_DATE THEN
1802 l_rev_component_rec.start_effective_date := NULL;
1803 END IF;
1804
1808
1805 IF l_rev_component_rec.disable_date = FND_API.G_MISS_DATE THEN
1806 l_rev_component_rec.disable_date := NULL;
1807 END IF;
1809 IF l_rev_component_rec.attribute_category = FND_API.G_MISS_CHAR THEN
1810 l_rev_component_rec.attribute_category := NULL;
1811 END IF;
1812
1813 IF l_rev_component_rec.attribute1 = FND_API.G_MISS_CHAR THEN
1814 l_rev_component_rec.attribute1 := NULL;
1815 END IF;
1816
1817 IF l_rev_component_rec.attribute2 = FND_API.G_MISS_CHAR THEN
1818 l_rev_component_rec.attribute2 := NULL;
1819 END IF;
1820
1821 IF l_rev_component_rec.attribute3 = FND_API.G_MISS_CHAR THEN
1822 l_rev_component_rec.attribute3 := NULL;
1823 END IF;
1824
1825 IF l_rev_component_rec.attribute4 = FND_API.G_MISS_CHAR THEN
1826 l_rev_component_rec.attribute4 := NULL;
1827 END IF;
1828
1829 IF l_rev_component_rec.attribute5 = FND_API.G_MISS_CHAR THEN
1830 l_rev_component_rec.attribute5 := NULL;
1831 END IF;
1832
1833 IF l_rev_component_rec.attribute6 = FND_API.G_MISS_CHAR THEN
1834 l_rev_component_rec.attribute6 := NULL;
1835 END IF;
1836
1837 IF l_rev_component_rec.attribute7 = FND_API.G_MISS_CHAR THEN
1838 l_rev_component_rec.attribute7 := NULL;
1839 END IF;
1840
1841 IF l_rev_component_rec.attribute8 = FND_API.G_MISS_CHAR THEN
1842 l_rev_component_rec.attribute8 := NULL;
1843 END IF;
1844
1845 IF l_rev_component_rec.attribute9 = FND_API.G_MISS_CHAR THEN
1846 l_rev_component_rec.attribute9 := NULL;
1847 END IF;
1848
1849 IF l_rev_component_rec.attribute10 = FND_API.G_MISS_CHAR THEN
1850 l_rev_component_rec.attribute10 := NULL;
1851 END IF;
1852
1853 IF l_rev_component_rec.attribute11 = FND_API.G_MISS_CHAR THEN
1854 l_rev_component_rec.attribute11 := NULL;
1855 END IF;
1856
1857 IF l_rev_component_rec.attribute12 = FND_API.G_MISS_CHAR THEN
1858 l_rev_component_rec.attribute12 := NULL;
1859 END IF;
1860
1861 IF l_rev_component_rec.attribute13 = FND_API.G_MISS_CHAR THEN
1862 l_rev_component_rec.attribute13 := NULL;
1863 END IF;
1864
1865 IF l_rev_component_rec.attribute14 = FND_API.G_MISS_CHAR THEN
1866 l_rev_component_rec.attribute14 := NULL;
1867 END IF;
1868
1869 IF l_rev_component_rec.attribute15 = FND_API.G_MISS_CHAR THEN
1870 l_rev_component_rec.attribute15 := NULL;
1871 END IF;
1872
1873 IF l_rev_component_rec.planning_percent = FND_API.G_MISS_NUM THEN
1874 l_rev_component_rec.planning_percent := NULL;
1875 END IF;
1876
1877 IF l_rev_component_rec.quantity_related = FND_API.G_MISS_NUM THEN
1878 l_rev_component_rec.quantity_related := NULL;
1879 END IF;
1880
1881 IF l_rev_component_rec.so_basis = FND_API.G_MISS_NUM THEN
1882 l_rev_component_rec.so_basis := NULL;
1883 END IF;
1884
1885 IF l_rev_component_rec.optional = FND_API.G_MISS_NUM THEN
1886 l_rev_component_rec.optional := NULL;
1887 END IF;
1888
1889 IF l_rev_component_rec.mutually_exclusive = FND_API.G_MISS_NUM THEN
1890 l_rev_component_rec.mutually_exclusive := NULL;
1891 END IF;
1892
1893 IF l_rev_component_rec.include_in_cost_rollup = FND_API.G_MISS_NUM THEN
1894 l_rev_component_rec.include_in_cost_rollup := NULL;
1895 END IF;
1896
1897 IF l_rev_component_rec.check_atp = FND_API.G_MISS_NUM THEN
1898 l_rev_component_rec.check_atp := NULL;
1899 END IF;
1900
1901 IF l_rev_component_rec.shipping_allowed = FND_API.G_MISS_NUM THEN
1902 l_rev_component_rec.shipping_allowed := NULL;
1903 END IF;
1904
1905 IF l_rev_component_rec.required_to_ship = FND_API.G_MISS_NUM THEN
1906 l_rev_component_rec.required_to_ship := NULL;
1907 END IF;
1908
1909 IF l_rev_component_rec.include_on_ship_docs = FND_API.G_MISS_NUM THEN
1910 l_rev_component_rec.include_on_ship_docs := NULL;
1911 END IF;
1912
1913 IF l_rev_component_rec.minimum_allowed_quantity = FND_API.G_MISS_NUM THEN
1914 l_rev_component_rec.minimum_allowed_quantity := NULL;
1915 END IF;
1916
1917 IF l_rev_component_rec.acd_type = FND_API.G_MISS_NUM THEN
1918 l_rev_component_rec.acd_type := NULL;
1919 END IF;
1920
1921 -- Added in 11.5.9 by ADEY
1922 IF l_rev_component_rec.auto_request_material = FND_API.G_MISS_CHAR THEN
1923 l_rev_component_rec.auto_request_material := NULL;
1924 END IF;
1925
1926 IF l_rev_component_rec.Suggested_Vendor_Name = FND_API.G_MISS_CHAR THEN --- Deepu
1927 l_rev_component_rec.Suggested_Vendor_Name := NULL;
1928 l_Rev_Comp_Unexp_Rec.Vendor_Id := NULL;
1929 END IF;
1930
1931 /*
1932 IF l_rev_component_rec.purchasing_category_id = FND_API.G_MISS_NUM THEN --- Deepu
1933 l_rev_component_rec.purchasing_category_id := NULL;
1934 END IF;
1935 */
1936 IF l_rev_component_rec.Unit_Price = FND_API.G_MISS_NUM THEN --- Deepu
1937 l_rev_component_rec.Unit_Price := NULL;
1938 END IF;
1939
1940 x_Rev_Component_Rec := l_rev_component_rec;
1941 x_Rev_Comp_Unexp_Rec := l_Rev_Comp_Unexp_Rec;
1942
1943 END Convert_Miss_To_Null;
1944
1945
1946 FUNCTION Get_Operation_Leadtime (
1947 p_assembly_item_id IN NUMBER,
1948 p_organization_id IN NUMBER,
1949 p_alternate_bom_code IN VARCHAR2,
1950 p_operation_seq_num IN NUMBER) RETURN NUMBER
1951 IS
1952
1953 l_leadtime_percent NUMBER;
1954
1955 BEGIN
1956
1957 SELECT OPERATION_LEAD_TIME_PERCENT
1958 into
1959 l_leadtime_percent
1960 FROM
1961 bom_operation_sequences bos
1962 WHERE
1963 bos.routing_sequence_id =
1964 (
1965 select common_routing_sequence_id
1966 from bom_operational_routings
1967 where assembly_item_id = p_assembly_item_id
1968 and organization_id = p_organization_id
1969 and nvl(alternate_routing_designator,
1970 nvl(p_alternate_bom_code, 'NONE')) =
1971 nvl(p_alternate_bom_code, 'NONE')
1972 and (p_alternate_bom_code is null
1973 or (p_alternate_bom_code is not null
1974 and (alternate_routing_designator =
1975 p_alternate_bom_code
1976 or not exists
1977 (select null
1978 from bom_operational_routings bor2
1979 where bor2.assembly_item_id =
1980 p_assembly_item_id
1981 and bor2.organization_id = p_organization_id
1982 and bor2.alternate_routing_designator = p_alternate_bom_code
1983 )
1984 )
1985 )
1986 )
1987 )
1988 AND bos.operation_type = 1 --bug: 4161149
1989 AND bos.operation_seq_num = p_operation_seq_num
1990 and bos.implementation_date is not null
1991 and bos.EFFECTIVITY_DATE <= sysdate
1992 AND nvl(disable_date, sysdate+1)
1993 > sysdate;
1994
1995 return l_leadtime_percent;
1996 EXCEPTION
1997 when no_data_found then
1998 return null; -- BUG : 4559089
1999
2000
2001 END;
2002
2003
2004 END Bom_Bom_Component_Util;