[Home] [Help]
PACKAGE BODY: APPS.ENG_REV_COMPONENT_UTIL
Source
1 PACKAGE BODY ENG_Rev_Component_Util AS
2 /* $Header: ENGUCMPB.pls 115.20 2002/12/12 18:09:21 akumar ship $ */
3
4 -- Global constant holding the package name
5
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ENG_Rev_Component_Util';
7
8 /***************************************************************************
9 * Procedure : Convert_Miss_To_Null
10 * Parameters IN : Revised component exposed column record
11 * Revised component unexposed column record
12 * Parameters OUT: Revised Component exposed column record
13 * Revised component unexposed column record.
14 * Purpose : This procedure will convert all missing columns to NULL.
15 ****************************************************************************/
16 PROCEDURE Convert_Miss_To_Null
17 ( p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
18 , p_Rev_Comp_Unexp_Rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
19 , x_Rev_Component_Rec IN OUT NOCOPY Bom_Bo_Pub.Rev_Component_Rec_Type
20 , x_Rev_Comp_Unexp_Rec IN OUT NOCOPY Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
21 )
22 IS
23 l_rev_component_rec Bom_Bo_Pub.Rev_Component_Rec_Type :=
24 p_rev_component_rec;
25 l_Rev_Comp_Unexp_Rec Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type :=
26 p_Rev_Comp_Unexp_Rec;
27 BEGIN
28
29 IF l_rev_component_rec.supply_subinventory = FND_API.G_MISS_CHAR THEN
30 l_rev_component_rec.supply_subinventory := NULL;
31 END IF;
32
33 IF l_rev_component_rec.required_for_revenue = FND_API.G_MISS_NUM THEN
34 l_rev_component_rec.required_for_revenue := NULL;
35 END IF;
36
37 IF l_rev_component_rec.maximum_allowed_quantity = FND_API.G_MISS_NUM THEN
38 l_rev_component_rec.maximum_allowed_quantity := NULL;
39 END IF;
40
41
42 IF l_rev_component_rec.wip_supply_type = FND_API.G_MISS_NUM THEN
43 l_rev_component_rec.wip_supply_type := NULL;
44 END IF;
45
46 IF l_rev_component_rec.location_name = FND_API.G_MISS_NUM THEN
47 l_rev_comp_unexp_rec.supply_locator_id := NULL;
48 END IF;
49
50 IF l_rev_component_rec.operation_sequence_number = FND_API.G_MISS_NUM THEN
51 l_rev_component_rec.operation_sequence_number := NULL;
52 END IF;
53
54 IF l_rev_component_rec.item_sequence_number = FND_API.G_MISS_NUM THEN
55 l_rev_component_rec.item_sequence_number := NULL;
56 END IF;
57
58 IF l_rev_component_rec.quantity_per_assembly = FND_API.G_MISS_NUM THEN
59 l_rev_component_rec.quantity_per_assembly := NULL;
60 END IF;
61
62 IF l_rev_component_rec.projected_yield = FND_API.G_MISS_NUM THEN
63 l_rev_component_rec.projected_yield := NULL;
64 END IF;
65
66 IF l_rev_component_rec.comments = FND_API.G_MISS_CHAR THEN
67 l_rev_component_rec.comments := NULL;
68 END IF;
69
70 IF l_rev_component_rec.start_effective_date = FND_API.G_MISS_DATE THEN
71 l_rev_component_rec.start_effective_date := NULL;
72 END IF;
73
74 IF l_rev_component_rec.disable_date = FND_API.G_MISS_DATE THEN
75 l_rev_component_rec.disable_date := NULL;
76 END IF;
77
78 IF l_rev_component_rec.attribute_category = FND_API.G_MISS_CHAR THEN
79 l_rev_component_rec.attribute_category := NULL;
80 END IF;
81
82 IF l_rev_component_rec.attribute1 = FND_API.G_MISS_CHAR THEN
83 l_rev_component_rec.attribute1 := NULL;
84 END IF;
85
86 IF l_rev_component_rec.attribute2 = FND_API.G_MISS_CHAR THEN
87 l_rev_component_rec.attribute2 := NULL;
88 END IF;
89
90 IF l_rev_component_rec.attribute3 = FND_API.G_MISS_CHAR THEN
91 l_rev_component_rec.attribute3 := NULL;
92 END IF;
93
94 IF l_rev_component_rec.attribute4 = FND_API.G_MISS_CHAR THEN
95 l_rev_component_rec.attribute4 := NULL;
96 END IF;
97
98 IF l_rev_component_rec.attribute5 = FND_API.G_MISS_CHAR THEN
99 l_rev_component_rec.attribute5 := NULL;
100 END IF;
101
102 IF l_rev_component_rec.attribute6 = FND_API.G_MISS_CHAR THEN
103 l_rev_component_rec.attribute6 := NULL;
104 END IF;
105
106 IF l_rev_component_rec.attribute7 = FND_API.G_MISS_CHAR THEN
107 l_rev_component_rec.attribute7 := NULL;
108 END IF;
109
110 IF l_rev_component_rec.attribute8 = FND_API.G_MISS_CHAR THEN
111 l_rev_component_rec.attribute8 := NULL;
112 END IF;
113
114 IF l_rev_component_rec.attribute9 = FND_API.G_MISS_CHAR THEN
115 l_rev_component_rec.attribute9 := NULL;
116 END IF;
117
118 IF l_rev_component_rec.attribute10 = FND_API.G_MISS_CHAR THEN
119 l_rev_component_rec.attribute10 := NULL;
120 END IF;
121
122 IF l_rev_component_rec.attribute11 = FND_API.G_MISS_CHAR THEN
123 l_rev_component_rec.attribute11 := NULL;
124 END IF;
125
126 IF l_rev_component_rec.attribute12 = FND_API.G_MISS_CHAR THEN
127 l_rev_component_rec.attribute12 := NULL;
128 END IF;
129
130 IF l_rev_component_rec.attribute13 = FND_API.G_MISS_CHAR THEN
131 l_rev_component_rec.attribute13 := NULL;
132 END IF;
133
134 IF l_rev_component_rec.attribute14 = FND_API.G_MISS_CHAR THEN
135 l_rev_component_rec.attribute14 := NULL;
136 END IF;
137
138 IF l_rev_component_rec.attribute15 = FND_API.G_MISS_CHAR THEN
139 l_rev_component_rec.attribute15 := NULL;
140 END IF;
141
142 IF l_rev_component_rec.planning_percent = FND_API.G_MISS_NUM THEN
143 l_rev_component_rec.planning_percent := NULL;
144 END IF;
145
146 IF l_rev_component_rec.quantity_related = FND_API.G_MISS_NUM THEN
147 l_rev_component_rec.quantity_related := NULL;
148 END IF;
149
150 IF l_rev_component_rec.so_basis = FND_API.G_MISS_NUM THEN
151 l_rev_component_rec.so_basis := NULL;
152 END IF;
153
154 IF l_rev_component_rec.optional = FND_API.G_MISS_NUM THEN
155 l_rev_component_rec.optional := NULL;
156 END IF;
157
158 IF l_rev_component_rec.mutually_exclusive = FND_API.G_MISS_NUM THEN
159 l_rev_component_rec.mutually_exclusive := NULL;
160 END IF;
161
162 IF l_rev_component_rec.include_in_cost_rollup = FND_API.G_MISS_NUM THEN
163 l_rev_component_rec.include_in_cost_rollup := NULL;
164 END IF;
165
166 IF l_rev_component_rec.check_atp = FND_API.G_MISS_NUM THEN
167 l_rev_component_rec.check_atp := NULL;
168 END IF;
169
170 IF l_rev_component_rec.shipping_allowed = FND_API.G_MISS_NUM THEN
171 l_rev_component_rec.shipping_allowed := NULL;
172 END IF;
173
174 IF l_rev_component_rec.required_to_ship = FND_API.G_MISS_NUM THEN
175 l_rev_component_rec.required_to_ship := NULL;
176 END IF;
177
178 IF l_rev_component_rec.include_on_ship_docs = FND_API.G_MISS_NUM THEN
179 l_rev_component_rec.include_on_ship_docs := NULL;
180 END IF;
181
182 IF l_rev_component_rec.minimum_allowed_quantity = FND_API.G_MISS_NUM THEN
183 l_rev_component_rec.minimum_allowed_quantity := NULL;
184 END IF;
185
186 x_Rev_Component_Rec := l_rev_component_rec;
187 x_Rev_Comp_Unexp_Rec := l_Rev_Comp_Unexp_Rec;
188
189 END Convert_Miss_To_Null;
190
191 /***************************************************************************
192 * Procedure : Update_Row
193 * Parameters IN : Revised Component exposed column record
194 * Revised Component unexposed column record
195 * Parameters OUT: Mesg_Token_Tbl
196 * Return_Status
197 * Purpose : Update_Row procedure will update the production record with
198 * the user given values. Any errors will be returned by filling
199 * the Mesg_Token_Tbl and setting the return_status.
200 ****************************************************************************/
201 PROCEDURE Update_Row
202 ( p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
203 , p_Rev_Comp_Unexp_Rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
204 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
205 , x_Return_Status OUT NOCOPY VARCHAR2
206 )
207 IS
208 l_return_status varchar2(80);
209 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
210 l_rev_component_rec Bom_Bo_Pub.Rev_Component_Rec_Type;
211 l_err_text VARCHAR2(2000);
212 BEGIN
213
214 UPDATE BOM_INVENTORY_COMPONENTS
215 SET SUPPLY_SUBINVENTORY = p_rev_component_rec.supply_subinventory
216 , REQUIRED_FOR_REVENUE = p_rev_component_rec.required_for_revenue
217 , HIGH_QUANTITY = p_rev_component_rec.maximum_allowed_quantity
218 , WIP_SUPPLY_TYPE = p_rev_component_rec.wip_supply_type
219 , SUPPLY_LOCATOR_ID =
220 DECODE(p_rev_comp_Unexp_rec.supply_locator_id, FND_API.G_MISS_NUM,
221 NULL, p_rev_comp_Unexp_rec.supply_locator_id)
222 , OPERATION_SEQ_NUM =
223 DECODE(p_rev_component_rec.new_operation_sequence_number,
224 NULL,p_rev_component_rec.operation_sequence_number,
225 p_rev_component_Rec.new_operation_sequence_number
226 )
227 , LAST_UPDATE_DATE = SYSDATE
228 , LAST_UPDATED_BY = Bom_Globals.Get_User_Id
229 , LAST_UPDATE_LOGIN = Bom_Globals.Get_User_Id
230 , ITEM_NUM = p_rev_component_rec.item_sequence_number
231 , COMPONENT_QUANTITY = p_rev_component_rec.quantity_per_assembly
232 , COMPONENT_YIELD_FACTOR = p_rev_component_rec.projected_yield
233 , COMPONENT_REMARKS = p_rev_component_rec.comments
234 , DISABLE_DATE = p_rev_component_rec.disable_date
235 , ATTRIBUTE_CATEGORY = p_rev_component_rec.attribute_category
236 , ATTRIBUTE1 = p_rev_component_rec.attribute1
237 , ATTRIBUTE2 = p_rev_component_rec.attribute2
238 , ATTRIBUTE3 = p_rev_component_rec.attribute3
239 , ATTRIBUTE4 = p_rev_component_rec.attribute4
240 , ATTRIBUTE5 = p_rev_component_rec.attribute5
241 , ATTRIBUTE6 = p_rev_component_rec.attribute6
242 , ATTRIBUTE7 = p_rev_component_rec.attribute7
243 , ATTRIBUTE8 = p_rev_component_rec.attribute8
244 , ATTRIBUTE9 = p_rev_component_rec.attribute9
245 , ATTRIBUTE10 = p_rev_component_rec.attribute10
246 , ATTRIBUTE11 = p_rev_component_rec.attribute11
247 , ATTRIBUTE12 = p_rev_component_rec.attribute12
248 , ATTRIBUTE13 = p_rev_component_rec.attribute13
249 , ATTRIBUTE14 = p_rev_component_rec.attribute14
250 , ATTRIBUTE15 = p_rev_component_rec.attribute15
251 , PLANNING_FACTOR = p_rev_component_rec.planning_percent
252 , QUANTITY_RELATED = p_rev_component_rec.quantity_related
253 , SO_BASIS = p_rev_component_rec.so_basis
254 , OPTIONAL = p_rev_component_rec.optional
255 , MUTUALLY_EXCLUSIVE_OPTIONS = p_rev_component_rec.mutually_exclusive
256 , INCLUDE_IN_COST_ROLLUP = p_rev_component_rec.include_in_cost_rollup
257 , CHECK_ATP = p_rev_component_rec.check_atp
258 , SHIPPING_ALLOWED = p_rev_component_rec.shipping_allowed
259 , REQUIRED_TO_SHIP = p_rev_component_rec.required_to_ship
260 , INCLUDE_ON_SHIP_DOCS = p_rev_component_rec.include_on_ship_docs
261 , LOW_QUANTITY = p_rev_component_rec.minimum_allowed_quantity
262 , ACD_TYPE = p_rev_component_rec.acd_type
263 , PROGRAM_UPDATE_DATE = SYSDATE
264 , PROGRAM_ID = Bom_Globals.Get_Prog_Id
265 , Original_System_Reference =
266 p_rev_component_rec.original_system_reference
267 , From_End_Item_Unit_Number =
268 p_rev_component_rec.from_end_item_unit_number
269 , To_End_Item_Unit_Number =
270 p_rev_component_rec.to_end_item_unit_number
271 WHERE COMPONENT_SEQUENCE_ID = p_Rev_Comp_Unexp_Rec.component_sequence_id
272 ;
273
274 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
275
276 EXCEPTION
277
278 WHEN OTHERS THEN
279
280 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
281 THEN
282 l_err_text := G_PKG_NAME ||
283 ' : Utility (Component Update) ' ||
284 SUBSTR(SQLERRM, 1, 200);
285 Error_Handler.Add_Error_Token
286 ( p_Message_Name => NULL
287 , p_Message_Text => l_err_text
288 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
289 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
290 );
291 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
292 END IF;
293
294 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
295
296 END Update_Row;
297
298 /*****************************************************************************
299 * Procedure : Insert_Row
300 * Parameters IN : Revised Component exposed column record
301 * Revised Component unexposed column record
302 * Parameters OUT: Mesg_Token_Tbl
303 * Return_Status
304 * Purpose : This procedure will insert a record in the bom_inventory-
305 * component table. Any errors will be filled in the Mesg_Token
306 * Tbl and returned with a return_status of U
307 *****************************************************************************/
308 PROCEDURE Insert_Row
309 ( p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
310 , p_Rev_Comp_Unexp_Rec IN Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
311 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
312 , x_Return_Status OUT NOCOPY VARCHAR2
313 )
314 IS
315 l_err_text VARCHAR2(2000);
316 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
317 BEGIN
318
319 INSERT INTO BOM_INVENTORY_COMPONENTS
320 ( SUPPLY_SUBINVENTORY
321 , OPERATION_LEAD_TIME_PERCENT
322 , REVISED_ITEM_SEQUENCE_ID
323 , COST_FACTOR
324 , REQUIRED_FOR_REVENUE
325 , HIGH_QUANTITY
326 , COMPONENT_SEQUENCE_ID
327 , PROGRAM_APPLICATION_ID
328 , WIP_SUPPLY_TYPE
329 , SUPPLY_LOCATOR_ID
330 , BOM_ITEM_TYPE
331 , OPERATION_SEQ_NUM
332 , COMPONENT_ITEM_ID
333 , LAST_UPDATE_DATE
334 , LAST_UPDATED_BY
335 , CREATION_DATE
336 , CREATED_BY
337 , LAST_UPDATE_LOGIN
338 , ITEM_NUM
339 , COMPONENT_QUANTITY
340 , COMPONENT_YIELD_FACTOR
341 , COMPONENT_REMARKS
342 , EFFECTIVITY_DATE
343 , CHANGE_NOTICE
344 , IMPLEMENTATION_DATE
345 , DISABLE_DATE
346 , ATTRIBUTE_CATEGORY
347 , ATTRIBUTE1
348 , ATTRIBUTE2
349 , ATTRIBUTE3
350 , ATTRIBUTE4
351 , ATTRIBUTE5
352 , ATTRIBUTE6
353 , ATTRIBUTE7
354 , ATTRIBUTE8
355 , ATTRIBUTE9
356 , ATTRIBUTE10
357 , ATTRIBUTE11
358 , ATTRIBUTE12
359 , ATTRIBUTE13
360 , ATTRIBUTE14
361 , ATTRIBUTE15
362 , PLANNING_FACTOR
363 , QUANTITY_RELATED
364 , SO_BASIS
365 , OPTIONAL
366 , MUTUALLY_EXCLUSIVE_OPTIONS
367 , INCLUDE_IN_COST_ROLLUP
368 , CHECK_ATP
369 , SHIPPING_ALLOWED
370 , REQUIRED_TO_SHIP
371 , INCLUDE_ON_SHIP_DOCS
372 , INCLUDE_ON_BILL_DOCS
373 , LOW_QUANTITY
374 , ACD_TYPE
375 , OLD_COMPONENT_SEQUENCE_ID
376 , BILL_SEQUENCE_ID
377 , REQUEST_ID
378 , PROGRAM_ID
379 , PROGRAM_UPDATE_DATE
380 , PICK_COMPONENTS
381 , Original_System_Reference
382 , From_End_Item_Unit_Number
383 , To_End_Item_Unit_Number
384 )
385 VALUES
386 ( p_rev_component_rec.supply_subinventory
387 , NULL /* Operation Lead Time Percent */
388 , p_rev_comp_unexp_rec.revised_item_sequence_id
389 , NULL /* Cost Factor */
390 , p_rev_component_rec.required_for_revenue
391 , p_rev_component_rec.maximum_allowed_quantity
392 , p_rev_comp_Unexp_rec.component_sequence_id
393 , Bom_Globals.Get_Prog_AppId
394 , p_rev_component_rec.wip_supply_type
395 , DECODE(p_rev_comp_Unexp_rec.supply_locator_id, FND_API.G_MISS_NUM,
396 NULL, p_rev_comp_Unexp_rec.supply_locator_id)
397 , p_rev_comp_Unexp_rec.bom_item_type
398 , p_rev_component_rec.operation_sequence_number
399 , p_rev_comp_Unexp_rec.component_item_id
400 , SYSDATE /* Last Update Date */
401 , Bom_Globals.Get_User_Id /* Last Updated By */
402 , SYSDATE /* Creation Date */
403 , Bom_Globals.Get_User_Id /* Created By */
404 , Bom_Globals.Get_User_Id /* Last Update Login */
405 , p_rev_component_rec.item_sequence_number
406 , p_rev_component_rec.quantity_per_assembly
407 , p_rev_component_rec.projected_yield
408 , p_rev_component_rec.comments
409 , p_rev_component_rec.start_effective_date
410 , p_rev_component_rec.Eco_Name
411 , NULL /* Implementation Date */
412 , p_rev_component_rec.disable_date
413 , p_rev_component_rec.attribute_category
414 , p_rev_component_rec.attribute1
415 , p_rev_component_rec.attribute2
416 , p_rev_component_rec.attribute3
417 , p_rev_component_rec.attribute4
418 , p_rev_component_rec.attribute5
419 , p_rev_component_rec.attribute6
420 , p_rev_component_rec.attribute7
421 , p_rev_component_rec.attribute8
422 , p_rev_component_rec.attribute9
423 , p_rev_component_rec.attribute10
424 , p_rev_component_rec.attribute11
425 , p_rev_component_rec.attribute12
426 , p_rev_component_rec.attribute13
427 , p_rev_component_rec.attribute14
428 , p_rev_component_rec.attribute15
429 , p_rev_component_rec.planning_percent
430 , p_rev_component_rec.quantity_related
431 , p_rev_component_rec.so_basis
432 , p_rev_component_rec.optional
433 , p_rev_component_rec.mutually_exclusive
434 , p_rev_component_rec.include_in_cost_rollup
435 , p_rev_component_rec.check_atp
436 , p_rev_component_rec.shipping_allowed
437 , p_rev_component_rec.required_to_ship
438 , p_rev_component_rec.include_on_ship_docs
439 , NULL /* Include On Bill Docs */
440 , p_rev_component_rec.minimum_allowed_quantity
441 , p_rev_component_rec.acd_type
442 , p_rev_comp_Unexp_rec.old_component_sequence_id
443 , p_rev_comp_Unexp_rec.bill_sequence_id
444 , NULL /* Request Id */
445 , Bom_Globals.Get_Prog_Id
446 , SYSDATE /* program_update_date */
447 , p_rev_comp_Unexp_rec.pick_components
448 , p_rev_component_rec.original_system_reference
449 , p_rev_component_rec.from_end_item_unit_number
450 , p_rev_component_rec.to_end_item_unit_number
451 );
452
453 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
454
455 EXCEPTION
456
457 WHEN OTHERS THEN
458 --dbms_output.put_line('Unexpected Error occured in Insert . . .' || SQLERRM);
459
460 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
461 THEN
462 l_err_text := G_PKG_NAME ||
463 ' : Utility (Component Insert) ' ||
464 SUBSTR(SQLERRM, 1, 200);
465 Error_Handler.Add_Error_Token
466 ( p_Message_Name => NULL
467 , p_Message_Text => l_err_text
468 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
469 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
470 );
471 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
472 END IF;
473
474 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
475
476 END Insert_Row;
477
478 /****************************************************************************
479 * Procedure : Delete_Row
480 * Parameters IN : Revised Component Key
481 * Parameters OUT: Mesg_Token_Tbl
482 * Return_Status
483 * Purpose : Will delete a revised component record for a ECO.
484 * Delete operation will not delete a record in production which
485 * is already implemented.
486 *****************************************************************************/
487 PROCEDURE Delete_Row
488 ( p_component_sequence_id IN NUMBER
489 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
490 , x_Return_Status OUT NOCOPY VARCHAR2
491 )
492 IS
493 l_dummy number;
494 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
495 BEGIN
496
497 DELETE FROM BOM_INVENTORY_COMPONENTS
498 WHERE COMPONENT_SEQUENCE_ID = p_component_sequence_id;
499
500 /******************************************************************
501 -- Also delete the Substitute components and Reference designators
502 -- by first logging a warning notifying the user of the cascaded
503 -- Delete.
504 *******************************************************************/
505
506 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
507 -- This is a warning.
508 THEN
509 Error_Handler.Add_Error_Token
510 ( p_Message_Name => 'ENG_COMP_DEL_CHILDREN'
511 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
512 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
513 );
514 END IF;
515
516 DELETE from bom_reference_designators
517 WHERE component_sequence_id = p_component_sequence_id;
518
519 DELETE from bom_substitute_components
520 WHERE component_Sequence_id = p_component_sequence_id;
521
522 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
523 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
524
525 EXCEPTION
526
527 WHEN OTHERS THEN
528 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
529 THEN
530 Error_Handler.Add_Error_Token
531 ( p_Message_Name => NULL
532 , p_Message_Text => 'Error Rev. Comp Delete Row ' ||
533 SUBSTR(SQLERRM, 1, 100)
534 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
535 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
536 );
537 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
538 END IF;
539 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
540 END Delete_Row;
541
542 -- Function Query_Row
543
544 PROCEDURE Query_Row
545 ( p_Component_Item_Id IN NUMBER
546 , p_Operation_Sequence_Number IN NUMBER
547 , p_Effectivity_Date IN DATE
548 , p_Bill_Sequence_Id IN NUMBER
549 , p_from_end_item_number IN VARCHAR2 := NULL
550 , x_Rev_Component_Rec OUT NOCOPY Bom_Bo_Pub.Rev_Component_Rec_Type
551 , x_Rev_Comp_Unexp_Rec OUT NOCOPY Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
552 , x_Return_Status OUT NOCOPY VARCHAR2
553 )
554 IS
555 l_rev_component_rec Bom_Bo_Pub.Rev_Component_Rec_Type;
556 l_Rev_Comp_Unexp_Rec Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type;
557 l_err_text VARCHAR2(2000);
558 BEGIN
559
560 --dbms_output.put_line('Querying component record . . .');
561 --dbms_output.put_line('Component: ' || to_char(p_Component_Item_Id));
562 --dbms_output.put_line('Op Seq : ' || to_char(p_Operation_Sequence_Number));
563 --dbms_output.put_line('Effective: ' || to_char(p_Effectivity_Date));
564 --dbms_output.put_line('Bill Seq : ' || to_char(p_Bill_Sequence_Id));
565
566 SELECT SUPPLY_SUBINVENTORY
567 , REVISED_ITEM_SEQUENCE_ID
568 , REQUIRED_FOR_REVENUE
569 , HIGH_QUANTITY
570 , COMPONENT_SEQUENCE_ID
571 , WIP_SUPPLY_TYPE
572 , SUPPLY_LOCATOR_ID
573 , BOM_ITEM_TYPE
574 , OPERATION_SEQ_NUM
575 , COMPONENT_ITEM_ID
576 , ITEM_NUM
577 , COMPONENT_QUANTITY
578 , COMPONENT_YIELD_FACTOR
579 , COMPONENT_REMARKS
580 , EFFECTIVITY_DATE
581 , CHANGE_NOTICE
582 , DISABLE_DATE
583 , ATTRIBUTE_CATEGORY
584 , ATTRIBUTE1
585 , ATTRIBUTE2
586 , ATTRIBUTE3
587 , ATTRIBUTE4
588 , ATTRIBUTE5
589 , ATTRIBUTE6
590 , ATTRIBUTE7
591 , ATTRIBUTE8
592 , ATTRIBUTE9
593 , ATTRIBUTE10
594 , ATTRIBUTE11
595 , ATTRIBUTE12
596 , ATTRIBUTE13
597 , ATTRIBUTE14
598 , ATTRIBUTE15
599 , PLANNING_FACTOR
600 , QUANTITY_RELATED
601 , SO_BASIS
602 , OPTIONAL
603 , MUTUALLY_EXCLUSIVE_OPTIONS
604 , INCLUDE_IN_COST_ROLLUP
605 , CHECK_ATP
606 , SHIPPING_ALLOWED
607 , REQUIRED_TO_SHIP
608 , INCLUDE_ON_SHIP_DOCS
609 , LOW_QUANTITY
610 , ACD_TYPE
611 , OLD_COMPONENT_SEQUENCE_ID
612 , BILL_SEQUENCE_ID
613 , PICK_COMPONENTS
614 , FROM_END_ITEM_UNIT_NUMBER
615 , TO_END_ITEM_UNIT_NUMBER
616 INTO l_rev_component_rec.supply_subinventory
617 , l_rev_comp_Unexp_rec.revised_item_sequence_id
618 , l_rev_component_rec.required_for_revenue
619 , l_rev_component_rec.maximum_allowed_quantity
620 , l_rev_comp_Unexp_rec.component_sequence_id
621 , l_rev_component_rec.wip_supply_type
622 , l_rev_comp_Unexp_rec.supply_locator_id
623 , l_rev_comp_Unexp_rec.bom_item_type
624 , l_rev_component_rec.operation_sequence_number
625 , l_rev_comp_Unexp_rec.component_item_id
626 , l_rev_component_rec.item_sequence_number
627 , l_rev_component_rec.quantity_per_assembly
628 , l_rev_component_rec.projected_yield
629 , l_rev_component_rec.comments
630 , l_rev_component_rec.start_effective_date
631 , l_rev_component_rec.Eco_Name
632 , l_rev_component_rec.disable_date
633 , l_rev_component_rec.attribute_category
634 , l_rev_component_rec.attribute1
635 , l_rev_component_rec.attribute2
636 , l_rev_component_rec.attribute3
637 , l_rev_component_rec.attribute4
638 , l_rev_component_rec.attribute5
639 , l_rev_component_rec.attribute6
640 , l_rev_component_rec.attribute7
641 , l_rev_component_rec.attribute8
642 , l_rev_component_rec.attribute9
643 , l_rev_component_rec.attribute10
644 , l_rev_component_rec.attribute11
645 , l_rev_component_rec.attribute12
646 , l_rev_component_rec.attribute13
647 , l_rev_component_rec.attribute14
648 , l_rev_component_rec.attribute15
649 , l_rev_component_rec.planning_percent
650 , l_rev_component_rec.quantity_related
651 , l_rev_component_rec.so_basis
652 , l_rev_component_rec.optional
653 , l_rev_component_rec.mutually_exclusive
654 , l_rev_component_rec.include_in_cost_rollup
655 , l_rev_component_rec.check_atp
656 , l_rev_component_rec.shipping_allowed
657 , l_rev_component_rec.required_to_ship
658 , l_rev_component_rec.include_on_ship_docs
659 , l_rev_component_rec.minimum_allowed_quantity
660 , l_rev_component_rec.acd_type
661 , l_rev_comp_unexp_rec.old_component_sequence_id
662 , l_rev_comp_unexp_rec.bill_sequence_id
663 , l_rev_comp_unexp_rec.pick_components
664 , l_rev_component_rec.from_end_item_unit_number
665 , l_rev_component_rec.to_end_item_unit_number
666 FROM BOM_INVENTORY_COMPONENTS
667 WHERE component_item_id = p_component_item_id
668 AND effectivity_date = p_effectivity_date
669 AND operation_seq_num = p_operation_sequence_number
670 AND bill_sequence_id = p_bill_sequence_id
671 AND NVL(from_end_item_unit_number, FND_API.G_MISS_CHAR) =
672 NVL(p_from_end_item_number, FND_API.G_MISS_CHAR);
673 --dbms_output.put_line('Finished querying and assigning component record . . .');
674
675 x_Return_Status := Bom_Globals.G_RECORD_FOUND;
676 x_Rev_Component_Rec := l_rev_component_rec;
677 x_Rev_Comp_Unexp_Rec := l_Rev_Comp_Unexp_Rec;
678
679 EXCEPTION
680
681 WHEN NO_DATA_FOUND THEN
682 x_return_status := Bom_Globals.G_RECORD_NOT_FOUND;
683 x_rev_component_rec := l_rev_component_rec;
684 x_Rev_Comp_Unexp_Rec := l_Rev_Comp_Unexp_Rec;
685
686 WHEN OTHERS THEN
687 l_err_text := G_PKG_NAME || ' Utility (Component Query Row) '
688 || substrb(SQLERRM,1,200);
689 --dbms_output.put_line('Unexpected Error: ' || l_err_text);
690
691 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
692
693 END Query_Row;
694
695 PROCEDURE Cancel_Component( p_component_sequence_id IN NUMBER
696 , p_cancel_comments IN VARCHAR2
697 , p_user_id IN NUMBER
698 , p_login_id IN NUMBER
699 )
700 IS
701 BEGIN
702 ENG_Cancel_ECO.Cancel_Revised_Component
703 ( comp_seq_id => p_component_sequence_id,
704 user_id => p_user_id,
705 login => p_login_id,
706 comment => p_cancel_comments
707 );
708 END Cancel_Component;
709
710 PROCEDURE Perform_Writes( p_rev_component_rec IN
711 Bom_Bo_Pub.Rev_Component_Rec_Type
712 , p_rev_comp_unexp_rec IN
713 Bom_Bo_Pub.Rev_Comp_Unexposed_Rec_Type
714 , x_Mesg_Token_Tbl OUT NOCOPY
715 Error_Handler.Mesg_Token_Tbl_Type
716 , x_Return_Status OUT NOCOPY VARCHAR2
717 )
718 IS
719 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
720 l_Rev_component_Rec Bom_Bo_Pub.Rev_Component_rec_Type;
721 l_rev_comp_unexp_rec Bom_Bo_Pub.rev_comp_unexposed_rec_type;
722 l_return_status VARCHAR2(1);
723 l_assembly_type NUMBER;
724 l_Comp_Seq_Id NUMBER;
725 CURSOR c_CheckBillExists IS
726 SELECT 1
727 FROM sys.dual
728 WHERE NOT EXISTS
729 ( SELECT bill_sequence_id
730 FROM bom_bill_of_materials
731 WHERE assembly_item_id =
732 l_rev_comp_unexp_rec.revised_item_id
733 AND organization_id =
734 l_rev_comp_unexp_rec.organization_id
735 AND NVL(alternate_bom_designator, 'NONE') =
736 NVL(l_rev_component_rec.alternate_bom_code,
737 'NONE')
738 );
739
740 BEGIN
741 l_rev_component_rec := p_rev_component_rec;
742 l_rev_comp_unexp_rec := p_rev_comp_unexp_rec;
743 l_return_status := FND_API.G_RET_STS_SUCCESS;
744
745 IF l_Rev_Component_Rec.Transaction_Type = Bom_GLOBALS.G_OPR_CREATE THEN
746
747 --dbms_output.put_line('Test Harness: Executing Insert Row. . . ');
748
749 FOR CheckBillExists IN c_CheckBillExists LOOP
750 -- Loop executes then the bill does not exist.
751 -- Procedure Create_New_Bill
752 SELECT decode(eng_item_flag, 'N', 1, 2)
753 INTO l_assembly_type
754 FROM mtl_system_items
755 WHERE inventory_item_id = l_rev_comp_unexp_rec.revised_item_id
756 AND organization_id = l_rev_comp_unexp_rec.organization_id;
757
758
759 --
760 -- Log a warning indicating that a new bill has been created
761 -- as a result of the component being added.
762 --
763 Error_Handler.Add_Error_Token
764 ( p_Message_Name => 'ENG_NEW_PRIMARY_CREATED'
765 , p_Message_Text => NULL
766 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
767 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
768 );
769
770 Eng_Rev_Component_Util.Create_New_Bill
771 ( p_assembly_item_id =>
772 l_rev_comp_unexp_rec.revised_item_id
773 , p_organization_id =>
774 l_rev_comp_unexp_rec.organization_id
775 , p_pending_from_ecn =>
776 l_rev_component_rec.eco_name
777 , p_bill_sequence_id =>
778 l_rev_comp_unexp_rec.bill_sequence_id
779 , p_common_bill_sequence_id =>
780 l_rev_comp_unexp_rec.bill_sequence_id
781 , p_assembly_type => l_assembly_type
782 , p_last_update_date => SYSDATE
783 , p_last_updated_by => Bom_Globals.Get_User_Id
784 , p_creation_date => SYSDATE
785 , p_created_by => Bom_Globals.Get_User_Id
786 , p_revised_item_seq_id =>
787 l_rev_comp_unexp_rec.revised_item_sequence_id
788 , p_original_system_reference =>
789 l_rev_component_rec.original_system_reference);
790 END LOOP;
791
792 Insert_Row
793 ( p_Rev_component_rec => l_Rev_Component_Rec
794 , p_Rev_Comp_Unexp_Rec => l_Rev_Comp_Unexp_Rec
795 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
796 , x_return_status => l_Return_Status
797 );
798 ELSIF l_Rev_Component_Rec.Transaction_Type = Bom_GLOBALS.G_OPR_UPDATE
799 THEN
800
801 --dbms_output.put_line('Test Harness: Executing Update Row. . . ');
802
803 Update_Row
804 ( p_Rev_component_rec => l_Rev_Component_Rec
805 , p_Rev_Comp_Unexp_Rec => l_Rev_Comp_Unexp_Rec
806 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
807 , x_return_status => l_Return_Status
808 );
809 ELSIF l_Rev_Component_Rec.Transaction_Type = Bom_GLOBALS.G_OPR_DELETE
810 THEN
811
812 --dbms_output.put_line('Test Harness: Executing Delete Row. . . ');
813
814 Delete_Row
815 ( p_component_sequence_id =>
816 l_Rev_Comp_Unexp_Rec.Component_Sequence_Id
817 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
818 , x_return_status => l_Return_Status
819 );
820 ELSIF l_Rev_Component_Rec.Transaction_Type = Bom_GLOBALS.G_OPR_CANCEL
821 THEN
822 --dbms_output.put_line('Perform Cancel Component . . .');
823
824 --
825 -- Fetch Component Sequence Id
826 --
827 SELECT component_sequence_id
828 INTO l_comp_seq_id
829 FROM bom_inventory_components
830 WHERE component_item_id =
831 l_rev_comp_unexp_rec.component_item_id
832 AND bill_sequence_id = l_rev_comp_unexp_rec.bill_sequence_id
833 AND operation_seq_num =
834 l_rev_component_rec.operation_sequence_number
835 AND effectivity_date =
836 l_rev_component_rec.start_Effective_date;
837
838 --
839 -- Log a warning indicating reference designators and
840 -- substitute components will also get deleted.
841 --
842 Error_Handler.Add_Error_Token
843 ( p_Message_Name => 'ENG_COMP_CANCEL_DEL_CHILDREN'
844 , p_Message_Text => NULL
845 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
846 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
847 );
848
849 Eng_Rev_Component_Util.Cancel_Component
850 ( p_component_sequence_id =>
851 l_comp_seq_id
852 , p_cancel_comments =>
853 l_rev_component_rec.cancel_comments
854 , p_user_id =>
855 Bom_Globals.Get_User_ID
856 , p_login_id =>
857 Bom_Globals.Get_Login_ID
858 );
859
860 END IF;
861
862 END Perform_Writes;
863
864 /************ PROCEDURE NOT USED *************************************/
865 PROCEDURE Lock_Row
866 ( x_return_status OUT NOCOPY VARCHAR2
867 , p_rev_component_rec IN Bom_Bo_Pub.Rev_Component_Rec_Type
868 , x_rev_component_rec IN OUT NOCOPY Bom_Bo_Pub.Rev_Component_Rec_Type
869 , x_err_text OUT NOCOPY VARCHAR2
870 )
871 IS
872 l_rev_component_rec Bom_Bo_Pub.Rev_Component_Rec_Type;
873 BEGIN
874 null;
875 END Lock_Row;
876
877
878 /******************************************************************************
879 * Procedure : Create_New_Bill
880 * Parameters IN : Assembly Item ID
881 * Organization ID
882 * Pending from ECN
883 * common_bill_sequence_id
884 * assembly_type
885 * WHO columns
886 * revised_item_sequence_id
887 * Purpose : This procedure will be called when a revised component is
888 * the first component being added on a revised item. This
889 * procedure will create a Bill and update the revised item
890 * information indicating that bill for this revised item now
891 * exists.
892 ******************************************************************************/
893 PROCEDURE Create_New_Bill( p_assembly_item_id IN NUMBER
894 , p_organization_id IN NUMBER
895 , p_pending_from_ecn IN VARCHAR2
896 , p_bill_sequence_id IN NUMBER
897 , p_common_bill_sequence_id IN NUMBER
898 , p_assembly_type IN NUMBER
899 , p_last_update_date IN DATE
900 , p_last_updated_by IN NUMBER
901 , p_creation_date IN DATE
902 , p_created_by IN NUMBER
903 , p_revised_item_seq_id IN NUMBER
904 , p_original_system_reference IN VARCHAR2)
905 IS
906 BEGIN
907 INSERT INTO Bom_Bill_Of_Materials
908 ( assembly_item_id
909 , organization_id
910 , pending_from_ecn
911 , bill_sequence_id
912 , common_bill_sequence_id
913 , assembly_type
914 , last_update_date
915 , last_updated_by
916 , creation_date
917 , created_by
918 , original_system_reference)
919 VALUES ( p_assembly_item_id
920 , p_organization_id
921 , p_pending_from_ecn
922 , p_bill_sequence_id
923 , p_common_bill_sequence_id
924 , p_assembly_type
925 , p_last_update_date
926 , p_last_updated_by
927 , p_creation_date
928 , p_created_by
929 , p_original_system_reference);
930
931 UPDATE eng_revised_items
932 SET bill_sequence_id = p_bill_sequence_id
933 WHERE revised_item_sequence_id = p_revised_item_seq_id;
934
935 END Create_New_Bill;
936
937
938 END ENG_Rev_Component_Util;