[Home] [Help]
PACKAGE BODY: APPS.BOM_INV_COMPS2_PKG
Source
1 PACKAGE BODY BOM_INV_COMPS2_PKG as
2 /* $Header: bompic2b.pls 120.6 2006/06/11 19:30:30 seradhak noship $ */
3
4 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
5 X_Operation_Seq_Num NUMBER,
6 X_Component_Item_Id NUMBER,
7 X_Item_Num NUMBER,
8 X_Component_Quantity NUMBER,
9 X_Component_Yield_Factor NUMBER,
10 X_Component_Remarks VARCHAR2,
11 X_Effectivity_Date DATE,
12 X_Change_Notice VARCHAR2,
13 X_Implementation_Date DATE,
14 X_Disable_Date DATE,
15 X_Attribute_Category VARCHAR2,
16 X_Attribute1 VARCHAR2,
17 X_Attribute2 VARCHAR2,
18 X_Attribute3 VARCHAR2,
19 X_Attribute4 VARCHAR2,
23 X_Attribute8 VARCHAR2,
20 X_Attribute5 VARCHAR2,
21 X_Attribute6 VARCHAR2,
22 X_Attribute7 VARCHAR2,
24 X_Attribute9 VARCHAR2,
25 X_Attribute10 VARCHAR2,
26 X_Attribute11 VARCHAR2,
27 X_Attribute12 VARCHAR2,
28 X_Attribute13 VARCHAR2,
29 X_Attribute14 VARCHAR2,
30 X_Attribute15 VARCHAR2,
31 X_Planning_Factor NUMBER,
32 X_Quantity_Related NUMBER,
33 X_So_Basis NUMBER,
34 X_Optional NUMBER,
35 X_Mutually_Exclusive_Options NUMBER,
36 X_Include_In_Cost_Rollup NUMBER,
37 X_Check_Atp NUMBER,
38 X_Required_To_Ship NUMBER,
39 X_Required_For_Revenue NUMBER,
40 X_Include_On_Ship_Docs NUMBER,
41 X_Include_On_Bill_Docs NUMBER,
42 X_Low_Quantity NUMBER,
43 X_High_Quantity NUMBER,
44 X_Acd_Type NUMBER,
45 X_Old_Component_Sequence_Id NUMBER,
46 X_Component_Sequence_Id NUMBER,
47 X_Bill_Sequence_Id NUMBER,
48 X_Wip_Supply_Type NUMBER,
49 X_Pick_Components NUMBER,
50 X_Supply_Subinventory VARCHAR2,
51 X_Supply_Locator_Id NUMBER,
52 X_Operation_Lead_Time_Percent NUMBER,
53 X_Revised_Item_Sequence_Id NUMBER,
54 X_Cost_Factor NUMBER,
55 X_Bom_Item_Type NUMBER,
56 X_From_Unit_Number VARCHAR2,
57 X_To_Unit_Number VARCHAR2,
58 X_Enforce_Int_Requirements NUMBER DEFAULT NULL,
59 X_Auto_Request_Material VARCHAR2 DEFAULT NULL
60 ,X_Suggested_Vendor_Name VARCHAR2 DEFAULT NULL
61 ,X_Vendor_Id NUMBER DEFAULT NULL
62 ,X_Unit_Price NUMBER DEFAULT NULL
63 , X_basis_type NUMBER DEFAULT NULL
64 ) IS
65 CURSOR C IS
66 SELECT *
67 FROM BOM_INVENTORY_COMPONENTS
68 WHERE rowid = X_Rowid
69 FOR UPDATE of Component_Sequence_Id NOWAIT;
70 Recinfo C%ROWTYPE;
71
72
73 BEGIN
74 OPEN C;
75 FETCH C INTO Recinfo;
76 if (C%NOTFOUND) then
77 CLOSE C;
78 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
79 APP_EXCEPTION.Raise_Exception;
80 end if;
81 CLOSE C;
82 if (
83 (Recinfo.operation_seq_num = X_Operation_Seq_Num)
84 AND (Recinfo.component_item_id = X_Component_Item_Id)
85 AND ( (Recinfo.item_num = X_Item_Num)
86 OR ( (Recinfo.item_num IS NULL)
87 AND (X_Item_Num IS NULL)))
88 AND (Recinfo.component_quantity = X_Component_Quantity)
89 AND (Recinfo.component_yield_factor = X_Component_Yield_Factor)
90 AND ( (Recinfo.component_remarks = X_Component_Remarks)
91 OR ( (Recinfo.component_remarks IS NULL)
92 AND (X_Component_Remarks IS NULL)))
93 AND (Recinfo.effectivity_date = X_Effectivity_Date)
94 AND ( (Recinfo.change_notice = X_Change_Notice)
95 OR ( (Recinfo.change_notice IS NULL)
96 AND (X_Change_Notice IS NULL)))
97 AND ( (Recinfo.implementation_date = X_Implementation_Date)
98 OR ( (Recinfo.implementation_date IS NULL)
99 AND (X_Implementation_Date IS NULL)))
100 AND ( (Recinfo.disable_date = X_Disable_Date)
101 OR ( (Recinfo.disable_date IS NULL)
102 AND (X_Disable_Date IS NULL)))
103 AND ( (Recinfo.attribute_category = X_Attribute_Category)
104 OR ( (Recinfo.attribute_category IS NULL)
105 AND (X_Attribute_Category IS NULL)))
106 AND ( (Recinfo.attribute1 = X_Attribute1)
107 OR ( (Recinfo.attribute1 IS NULL)
108 AND (X_Attribute1 IS NULL)))
109 AND ( (Recinfo.attribute2 = X_Attribute2)
110 OR ( (Recinfo.attribute2 IS NULL)
111 AND (X_Attribute2 IS NULL)))
112 AND ( (Recinfo.attribute3 = X_Attribute3)
113 OR ( (Recinfo.attribute3 IS NULL)
114 AND (X_Attribute3 IS NULL)))
115 AND ( (Recinfo.attribute4 = X_Attribute4)
116 OR ( (Recinfo.attribute4 IS NULL)
117 AND (X_Attribute4 IS NULL)))
118 AND ( (Recinfo.attribute5 = X_Attribute5)
119 OR ( (Recinfo.attribute5 IS NULL)
120 AND (X_Attribute5 IS NULL)))
121 AND ( (Recinfo.attribute6 = X_Attribute6)
122 OR ( (Recinfo.attribute6 IS NULL)
126 AND (X_Attribute7 IS NULL)))
123 AND (X_Attribute6 IS NULL)))
124 AND ( (Recinfo.attribute7 = X_Attribute7)
125 OR ( (Recinfo.attribute7 IS NULL)
127 AND ( (Recinfo.attribute8 = X_Attribute8)
128 OR ( (Recinfo.attribute8 IS NULL)
129 AND (X_Attribute8 IS NULL)))
130 AND ( (Recinfo.attribute9 = X_Attribute9)
131 OR ( (Recinfo.attribute9 IS NULL)
132 AND (X_Attribute9 IS NULL)))
133 AND ( (Recinfo.attribute10 = X_Attribute10)
134 OR ( (Recinfo.attribute10 IS NULL)
135 AND (X_Attribute10 IS NULL)))
136 AND ( (Recinfo.attribute11 = X_Attribute11)
137 OR ( (Recinfo.attribute11 IS NULL)
138 AND (X_Attribute11 IS NULL)))
139 AND ( (Recinfo.attribute12 = X_Attribute12)
140 OR ( (Recinfo.attribute12 IS NULL)
141 AND (X_Attribute12 IS NULL)))
142 AND ( (Recinfo.attribute13 = X_Attribute13)
143 OR ( (Recinfo.attribute13 IS NULL)
144 AND (X_Attribute13 IS NULL)))
145 AND ( (Recinfo.attribute14 = X_Attribute14)
146 OR ( (Recinfo.attribute14 IS NULL)
147 AND (X_Attribute14 IS NULL)))
148 AND ( (Recinfo.attribute15 = X_Attribute15)
149 OR ( (Recinfo.attribute15 IS NULL)
150 AND (X_Attribute15 IS NULL)))
151 AND (Recinfo.planning_factor = X_Planning_Factor)
152 AND (Recinfo.quantity_related = X_Quantity_Related)
153 AND ( (Recinfo.so_basis = X_So_Basis)
154 OR ( (Recinfo.so_basis IS NULL)
155 AND (X_So_Basis IS NULL)))
156 AND ( (Recinfo.optional = X_Optional)
157 OR ( (Recinfo.optional IS NULL)
158 AND (X_Optional IS NULL)))
159 AND ( (Recinfo.mutually_exclusive_options = X_Mutually_Exclusive_Options)
160 OR ( (Recinfo.mutually_exclusive_options IS NULL)
161 AND (X_Mutually_Exclusive_Options IS NULL)))
162 AND (Recinfo.include_in_cost_rollup = X_Include_In_Cost_Rollup)
163 AND (Recinfo.check_atp = X_Check_Atp)
164 AND ( (Recinfo.required_to_ship = X_Required_To_Ship)
165 OR ( (Recinfo.required_to_ship IS NULL)
166 AND (X_Required_To_Ship IS NULL)))
167 AND ( (Recinfo.required_for_revenue = X_Required_For_Revenue)
168 OR ( (Recinfo.required_for_revenue IS NULL)
169 AND (X_Required_For_Revenue IS NULL)))
170 AND ( (Recinfo.include_on_ship_docs = X_Include_On_Ship_Docs)
171 OR ( (Recinfo.include_on_ship_docs IS NULL)
172 AND (X_Include_On_Ship_Docs IS NULL)))
173 AND ( (Recinfo.include_on_bill_docs = X_Include_On_Bill_Docs)
174 OR ( (Recinfo.include_on_bill_docs IS NULL)
175 AND (X_Include_On_Bill_Docs IS NULL)))
176 AND ( (Recinfo.low_quantity = X_Low_Quantity)
177 OR ( (Recinfo.low_quantity IS NULL)
178 AND (X_Low_Quantity IS NULL)))
179 AND ( (Recinfo.high_quantity = X_High_Quantity)
180 OR ( (Recinfo.high_quantity IS NULL)
181 AND (X_High_Quantity IS NULL)))
182 AND ( (Recinfo.acd_type = X_Acd_Type)
183 OR ( (Recinfo.acd_type IS NULL)
184 AND (X_Acd_Type IS NULL)))
185 AND ( (Recinfo.old_component_sequence_id = X_Old_Component_Sequence_Id)
186 OR ( (Recinfo.old_component_sequence_id IS NULL)
187 AND (X_Old_Component_Sequence_Id IS NULL)))
188 AND (Recinfo.component_sequence_id = X_Component_Sequence_Id)
189 AND (Recinfo.bill_sequence_id = X_Bill_Sequence_Id)
190 AND ( (Recinfo.wip_supply_type = X_Wip_Supply_Type)
191 OR ( (Recinfo.wip_supply_type IS NULL)
192 AND (X_Wip_Supply_Type IS NULL)))
193 AND ( (Recinfo.pick_components = X_Pick_Components)
194 OR ( (Recinfo.pick_components IS NULL)
195 AND (X_Pick_Components IS NULL)))
196 AND ( (Recinfo.supply_subinventory = X_Supply_Subinventory)
197 OR ( (Recinfo.supply_subinventory IS NULL)
198 AND (X_Supply_Subinventory IS NULL)))
199 AND ( (Recinfo.supply_locator_id = X_Supply_Locator_Id)
200 OR ( (Recinfo.supply_locator_id IS NULL)
201 AND (X_Supply_Locator_Id IS NULL)))
202 AND ( (Recinfo.basis_type = X_basis_type)
203 OR ( (Recinfo.basis_type IS NULL)
204 AND (X_basis_type IS NULL)))
205 /* Fixed bug 666081. Operation_Lead_Time_Percent (OLTP) is not part of the
206 view BOM_INVENTORY_COMPONENTS_V and is not selected from the base table in the
207 form. So if the user copies a bill which has some values for OLTP, then this
208 causes a lock_row error.
209
210 AND ( (Recinfo.operation_lead_time_percent = X_Operation_Lead_Time_Percent)
211 OR ( (Recinfo.operation_lead_time_percent IS NULL)
212 AND (X_Operation_Lead_Time_Percent IS NULL)))
213 */
214 AND ( (Recinfo.revised_item_sequence_id = X_Revised_Item_Sequence_Id)
215 OR ( (Recinfo.revised_item_sequence_id IS NULL)
216 AND (X_Revised_Item_Sequence_Id IS NULL)))
217 AND ( (Recinfo.cost_factor = X_Cost_Factor)
218 OR ( (Recinfo.cost_factor IS NULL)
222 AND (X_Bom_Item_Type IS NULL)))
219 AND (X_Cost_Factor IS NULL)))
220 AND ( (Recinfo.bom_item_type = X_Bom_Item_Type)
221 OR ( (Recinfo.bom_item_type IS NULL)
223 AND ( (Recinfo.from_end_item_unit_number = X_From_Unit_Number)
224 OR ( (Recinfo.from_end_item_unit_number IS NULL)
225 AND (X_From_Unit_Number IS NULL)))
226 AND ( (Recinfo.to_end_item_unit_number = X_To_Unit_Number)
227 OR ( (Recinfo.to_end_item_unit_number IS NULL)
228 AND (X_To_Unit_Number IS NULL)))
229 AND ( (Recinfo.enforce_int_requirements = X_Enforce_Int_Requirements)
230 OR ( (Recinfo.Enforce_Int_Requirements IS NULL)
231 AND (X_Enforce_Int_Requirements IS NULL)))
232 AND ( (Recinfo.auto_request_material = X_Auto_Request_Material)
233 OR ( (Recinfo.Auto_Request_Material IS NULL)
234 AND (X_Auto_Request_Material IS NULL)))
235 AND ( (Recinfo.suggested_vendor_name = X_Suggested_Vendor_Name)
236 OR ( (Recinfo.suggested_vendor_name IS NULL)
237 AND (X_Suggested_Vendor_Name IS NULL)))
238 AND ( (Recinfo.vendor_id = X_Vendor_Id)
239 OR ( (Recinfo.vendor_id IS NULL)
240 AND (X_Vendor_Id IS NULL)))
241 AND ( (Recinfo.Unit_Price = X_Unit_Price)
242 OR ( (Recinfo.Unit_Price IS NULL)
243 AND (X_Unit_Price IS NULL)))
244
245 ) then
246 return;
247 else
248 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
249 APP_EXCEPTION.Raise_Exception;
250 end if;
251 END Lock_Row;
252
253
254
255 PROCEDURE Update_Row(X_Rowid VARCHAR2,
256 X_Operation_Seq_Num NUMBER,
257 X_Component_Item_Id NUMBER,
258 X_Last_Update_Date DATE,
259 X_Last_Updated_By NUMBER,
260 X_Last_Update_Login NUMBER,
261 X_Item_Num NUMBER,
262 X_Component_Quantity NUMBER,
263 X_Component_Yield_Factor NUMBER,
264 X_Component_Remarks VARCHAR2,
265 X_Effectivity_Date DATE,
266 X_Change_Notice VARCHAR2,
267 X_Implementation_Date DATE,
268 X_Disable_Date DATE,
269 X_Attribute_Category VARCHAR2,
270 X_Attribute1 VARCHAR2,
271 X_Attribute2 VARCHAR2,
272 X_Attribute3 VARCHAR2,
273 X_Attribute4 VARCHAR2,
274 X_Attribute5 VARCHAR2,
275 X_Attribute6 VARCHAR2,
276 X_Attribute7 VARCHAR2,
277 X_Attribute8 VARCHAR2,
278 X_Attribute9 VARCHAR2,
279 X_Attribute10 VARCHAR2,
280 X_Attribute11 VARCHAR2,
281 X_Attribute12 VARCHAR2,
282 X_Attribute13 VARCHAR2,
283 X_Attribute14 VARCHAR2,
284 X_Attribute15 VARCHAR2,
285 X_Planning_Factor NUMBER,
286 X_Quantity_Related NUMBER,
287 X_So_Basis NUMBER,
288 X_Optional NUMBER,
289 X_Mutually_Exclusive_Options NUMBER,
290 X_Include_In_Cost_Rollup NUMBER,
291 X_Check_Atp NUMBER,
292 X_Required_To_Ship NUMBER,
293 X_Required_For_Revenue NUMBER,
294 X_Include_On_Ship_Docs NUMBER,
295 X_Include_On_Bill_Docs NUMBER,
296 X_Low_Quantity NUMBER,
297 X_High_Quantity NUMBER,
298 X_Acd_Type NUMBER,
299 X_Old_Component_Sequence_Id NUMBER,
300 X_Component_Sequence_Id NUMBER,
301 X_Bill_Sequence_Id NUMBER,
302 X_Wip_Supply_Type NUMBER,
303 X_Pick_Components NUMBER,
304 X_Supply_Subinventory VARCHAR2,
305 X_Supply_Locator_Id NUMBER,
306 X_Operation_Lead_Time_Percent NUMBER,
307 X_Revised_Item_Sequence_Id NUMBER,
308 X_Cost_Factor NUMBER,
309 X_Bom_Item_Type NUMBER,
310 X_From_Unit_Number VARCHAR2,
311 X_To_Unit_Number VARCHAR2,
312 X_Enforce_Int_Requirements NUMBER DEFAULT NULL,
313 X_Auto_Request_Material VARCHAR2 DEFAULT NULL
314 ,X_Suggested_Vendor_Name VARCHAR2 DEFAULT NULL
315 ,X_Vendor_Id NUMBER DEFAULT NULL
316 ,X_Unit_Price NUMBER DEFAULT NULL
320 l_err_tbl Error_Handler.Mesg_Token_Tbl_Type;
317 ,X_basis_type NUMBER DEFAULT NULL
318
319 ) IS
321 l_return_status VARCHAR2(10);
322 l_organization_id number; --4306013
323 l_component_item_name VARCHAR2(240); --4306013
324 l_bill_sequence_id number; --4306013
325
326 org_id number; --4306013
327 comp_item_name VARCHAR2(240); --4306013
328 alt_bom_code varchar2(240); --4306013
329 ass_item_id NUMBER; --4306013
330 s_ass_comment varchar2(240); --4306013
331 l_created_by NUMBER;
332 l_creation_date DATE;
333
334 BEGIN
335 UPDATE BOM_INVENTORY_COMPONENTS
336 SET
337 operation_seq_num = X_Operation_Seq_Num,
338 component_item_id = X_Component_Item_Id,
339 last_update_date = X_Last_Update_Date,
340 last_updated_by = X_Last_Updated_By,
341 last_update_login = X_Last_Update_Login,
342 item_num = X_Item_Num,
343 component_quantity = X_Component_Quantity,
344 component_yield_factor = X_Component_Yield_Factor,
345 component_remarks = X_Component_Remarks,
346 effectivity_date = X_Effectivity_Date,
347 change_notice = X_Change_Notice,
348 implementation_date = X_Implementation_Date,
349 disable_date = X_Disable_Date,
350 attribute_category = X_Attribute_Category,
351 attribute1 = X_Attribute1,
352 attribute2 = X_Attribute2,
353 attribute3 = X_Attribute3,
354 attribute4 = X_Attribute4,
355 attribute5 = X_Attribute5,
356 attribute6 = X_Attribute6,
357 attribute7 = X_Attribute7,
358 attribute8 = X_Attribute8,
359 attribute9 = X_Attribute9,
360 attribute10 = X_Attribute10,
361 attribute11 = X_Attribute11,
362 attribute12 = X_Attribute12,
363 attribute13 = X_Attribute13,
364 attribute14 = X_Attribute14,
365 attribute15 = X_Attribute15,
366 planning_factor = X_Planning_Factor,
367 quantity_related = X_Quantity_Related,
368 so_basis = X_So_Basis,
369 optional = X_Optional,
370 mutually_exclusive_options = X_Mutually_Exclusive_Options,
371 include_in_cost_rollup = X_Include_In_Cost_Rollup,
372 check_atp = X_Check_Atp,
373 required_to_ship = X_Required_To_Ship,
374 required_for_revenue = X_Required_For_Revenue,
375 include_on_ship_docs = X_Include_On_Ship_Docs,
376 include_on_bill_docs = X_Include_On_Bill_Docs,
377 low_quantity = X_Low_Quantity,
378 high_quantity = X_High_Quantity,
379 acd_type = X_Acd_Type,
380 old_component_sequence_id = X_Old_Component_Sequence_Id,
381 component_sequence_id = X_Component_Sequence_Id,
382 bill_sequence_id = X_Bill_Sequence_Id,
383 wip_supply_type = X_Wip_Supply_Type,
384 pick_components = X_Pick_Components,
385 supply_subinventory = X_Supply_Subinventory,
386 supply_locator_id = X_Supply_Locator_Id,
387 operation_lead_time_percent = X_Operation_Lead_Time_Percent,
388 revised_item_sequence_id = X_Revised_Item_Sequence_Id,
389 cost_factor = X_Cost_Factor,
390 bom_item_type = X_Bom_Item_Type,
391 from_end_item_unit_number = X_from_unit_number,
392 to_end_item_unit_number = X_to_unit_number,
393 enforce_int_requirements = X_Enforce_Int_Requirements,
394 auto_request_material = X_Auto_Request_Material
395 ,suggested_vendor_name = X_Suggested_Vendor_Name
396 ,vendor_id = X_Vendor_Id
397 ,unit_price = X_Unit_Price
398 ,basis_type = X_basis_type
399 WHERE rowid = X_Rowid;
400
401 if (SQL%NOTFOUND) then
402 Raise NO_DATA_FOUND;
403 end if;
404
405 BOMPCMBM.Update_Related_Components( p_src_comp_seq_id => X_Component_Sequence_Id
406 , x_Mesg_Token_Tbl => l_err_tbl
407 , x_Return_Status => l_return_status
408 );
409 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
410 THEN
411 app_exception.raise_exception;
412 END IF;
413
414 -- Raising Business event
415
416 SELECT bbm.Organization_Id, bbm.alternate_bom_designator, bbm.assembly_item_id,
417 bbm.specific_assembly_comment,creation_date,created_by
418 INTO org_id, alt_bom_code, ass_item_id, s_ass_comment,l_creation_date,l_created_by
419 FROM Bom_Bill_Of_Materials bbm
420 WHERE bbm.Bill_Sequence_Id = X_Bill_Sequence_Id;
421
422 Bom_Business_Event_PKG.Raise_Bill_Event
423 (p_pk1_value => ass_item_id
424 , p_pk2_value => org_id
428 , p_structure_comment => s_ass_comment
425 , p_obj_name => NULL
426 , p_structure_name => alt_bom_code
427 , p_organization_id => org_id
429 , p_Event_Load_Type => 'Single'
430 , p_Event_Entity_Name => 'Component'
431 , p_Event_Entity_Parent_Id => X_Bill_Sequence_Id
432 , p_Event_Name => Bom_Business_Event_PKG.G_STRUCTURE_MODIFIED_EVENT
433 , p_last_update_date => X_Last_Update_Date
434 , p_last_updated_by => X_Last_Updated_By
435 , p_creation_date => l_creation_date
436 , p_created_by => l_created_by
437 , p_last_update_login=> X_Last_Update_Login
438 , p_component_seq_id => X_Component_Sequence_Id
439 );
440 END Update_Row;
441
442 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
443 BEGIN
444 DELETE FROM BOM_INVENTORY_COMPONENTS
445 WHERE rowid = X_Rowid;
446
447 if (SQL%NOTFOUND) then
448 Raise NO_DATA_FOUND;
449 end if;
450
451
452 END Delete_Row;
453
454 FUNCTION POPULATE_INV_COMPS (X_Group_Id NUMBER,
455 X_Bill_Sequence_Id NUMBER,
456 X_Err_Text IN OUT NOCOPY VARCHAR2
457 ) RETURN NUMBER
458 IS
459 BEGIN
460 INSERT INTO BOM_INVENTORY_COMPONENTS(
461 operation_seq_num,
462 component_item_id,
463 last_update_date,
464 last_updated_by,
465 creation_date,
466 created_by,
467 item_num,
468 component_quantity,
469 component_yield_factor,
470 effectivity_date,
471 implementation_date,
472 planning_factor,
473 quantity_related,
474 so_basis,
475 optional,
476 mutually_exclusive_options,
477 include_in_cost_rollup,
478 check_atp,
479 required_to_ship,
480 required_for_revenue,
481 include_on_ship_docs,
482 include_on_bill_docs,
483 low_quantity,
484 high_quantity,
485 component_sequence_id,
486 bill_sequence_id,
487 wip_supply_type,
488 supply_subinventory,
489 supply_locator_id,
490 pick_components,
491 bom_item_type)
492 SELECT bce.OPERATION_SEQ_NUM,
493 bce.COMPONENT_ITEM_ID,
494 bce.LAST_UPDATE_DATE,
495 bce.LAST_UPDATED_BY,
496 bce.CREATION_DATE,
497 bce.CREATED_BY,
498 decode(bce.PLAN_LEVEL,0,10,bce.ITEM_NUM),
499 bce.COMPONENT_QUANTITY,
500 1, /* component_yield_factor */
501 bce.EFFECTIVITY_DATE,
502 sysdate,
503 100, /* planning_factor */
504 2, /* quantity_related */
505 NVL(bce.SO_BASIS,2),
506 NVL(bce.OPTIONAL,2),
507 NVL(bce.MUTUALLY_EXCLUSIVE_OPTIONS,2),
508 decode(msi.DEFAULT_INCLUDE_IN_ROLLUP_FLAG,
509 'Y', 1,
510 'N', 2,
511 2), /* include_in_cost_rollup */
512 bce.CHECK_ATP,
513 NVL(bce.REQUIRED_TO_SHIP,2),
514 NVL(bce.REQUIRED_FOR_REVENUE,2),
515 NVL(bce.INCLUDE_ON_SHIP_DOCS,2),
516 NVL(bce.INCLUDE_ON_BILL_DOCS,2),
517 bce.LOW_QUANTITY,
518 bce.HIGH_QUANTITY,
519 BOM_INVENTORY_COMPONENTS_S.nextval,
520 X_BILL_SEQUENCE_ID,
521 decode(bce.bom_item_type, 2, 6, 1, 6, 1, nvl(msi.wip_supply_type,1)), /* wip_supply_type */
522 decode(bce.bom_item_type, 4, msi.wip_supply_subinventory,null),
523 decode(bce.bom_item_type, 4, msi.wip_supply_locator_id,null),
524 bce.PICK_COMPONENTS,
525 bce.BOM_ITEM_TYPE
526 FROM MTL_SYSTEM_ITEMS msi,
527 BOM_CONFIG_EXPLOSIONS bce
528 WHERE bce.group_id = X_group_id
529 AND bce.component_item_id = msi.inventory_item_id
530 AND bce.organization_id = msi.organization_id;
531
532 -- Clean up table after a successful insert
533 Delete from BOM_CONFIG_EXPLOSIONS
534 where group_id = X_group_id;
535
536 -- Returns success code
537 Return 0;
538
539 EXCEPTION
540 WHEN OTHERS THEN
541 x_err_text := 'BOM_INV_COMPS2_PKG.POPULATE_INV_COMPS():'||substr(SQLERRM,1,60);
542 FND_MESSAGE.SET_NAME('BOM', 'BOM_SQL_ERR');
543 FND_MESSAGE.SET_TOKEN('ENTITY', x_err_text);
544 APP_EXCEPTION.RAISE_EXCEPTION;
545 return 1; -- 1 indicates failure to calling package
546 END POPULATE_INV_COMPS;
547
548 END BOM_INV_COMPS2_PKG;