[Home] [Help]
PACKAGE BODY: APPS.BOM_INV_COMPS1_PKG
Source
1 PACKAGE BODY BOM_INV_COMPS1_PKG as
2 /* $Header: bompic1b.pls 120.6 2006/06/11 19:30:04 seradhak noship $ */
3
4 PROCEDURE Check_Overlap(X_Rowid VARCHAR2,
5 X_Bill_Sequence_Id NUMBER,
6 X_Component_Item_Id NUMBER,
7 X_Operation_Seq_Num NUMBER,
8 X_Disable_Date DATE,
9 X_Effectivity_Date DATE) IS
10 dummy NUMBER;
11 BEGIN
12 SELECT 1 INTO dummy FROM sys.dual
13 WHERE NOT EXISTS
14 (SELECT 1 FROM bom_inventory_components
15 WHERE bill_sequence_id = X_Bill_Sequence_Id
16 AND component_item_id = X_Component_Item_Id
17 AND operation_seq_num = X_Operation_Seq_Num
18 AND (X_Disable_Date IS NULL
19 OR (to_char(X_Disable_Date,'YYYY/MM/DD HH24:MI:SS') > to_char(effectivity_date,'YYYY/MM/DD HH24:MI:SS')))
20 AND ((to_char(X_Effectivity_Date,'YYYY/MM/DD HH24:MI:SS') < to_char(disable_date,'YYYY/MM/DD HH24:MI:SS'))
21 OR disable_date IS NULL)
22 AND implementation_date IS NOT NULL
23 AND NVL(ECO_FOR_PRODUCTION,2) = 2
24 AND ((rowid <> X_Rowid) OR (X_Rowid IS NULL)));
25 EXCEPTION
26 WHEN NO_DATA_FOUND THEN
27 fnd_message.set_name('BOM','BOM_COMP_OP_COMBINATION');
28 app_exception.raise_exception;
29 END Check_Overlap;
30
31 PROCEDURE Check_Unit_Number_Overlap(X_Rowid VARCHAR2,
32 X_Bill_Sequence_Id NUMBER,
33 X_Component_Item_Id NUMBER,
34 X_Operation_Seq_Num NUMBER,
35 X_From_Unit_Number VARCHAR2,
36 X_To_Unit_Number VARCHAR2) IS
37 dummy NUMBER;
38 BEGIN
39 SELECT 1 INTO dummy FROM sys.dual
40 WHERE NOT EXISTS
41 (SELECT 1 FROM bom_inventory_components
42 WHERE bill_sequence_id = X_Bill_Sequence_Id
43 AND component_item_id = X_Component_Item_Id
44 AND operation_seq_num = X_Operation_Seq_Num
45 AND (X_To_Unit_Number IS NULL
46 OR (X_To_Unit_Number >= from_end_item_unit_number))
47 AND ((X_From_Unit_Number <= to_end_item_unit_number)
48 OR to_end_item_unit_number IS NULL)
49 AND implementation_date IS NOT NULL
50 AND NVL(ECO_FOR_PRODUCTION,2) = 2
51 AND disable_date is NULL
52 AND ((rowid <> X_Rowid) OR (X_Rowid IS NULL)));
53 EXCEPTION
54 WHEN NO_DATA_FOUND THEN
55 fnd_message.set_name('BOM','BOM_UNIT_OVERLAP');
56 app_exception.raise_exception;
57 END Check_Unit_Number_Overlap;
58
59 PROCEDURE Check_Commons(X_Bill_Sequence_Id NUMBER,
60 X_Organization_Id NUMBER,
61 X_Component_Item_Id NUMBER,
62 X_Bill_or_Eco NUMBER DEFAULT 2) IS --bug1517975
63 dummy NUMBER;
64 eng_items_allowed number := 1; -- bug 1517975
65 eng_items_for_mfg_ecos_flag varchar(3) := 'NO'; -- bug 1517975
66
67 BEGIN
68 -- Check if bill has cross-org commons
69 -- If so, make sure component exists in those other orgs
70 -- and the component does not violate the bill/comp matrix
71
72 -- bug 1517975
73 FND_PROFILE.GET('ENG:ALLOW_ENG_COMPS',eng_items_allowed);
74 if ((eng_items_allowed = 1) and (X_Bill_or_Eco = 2)) then
75 eng_items_for_mfg_ecos_flag := 'YES';
76 end if;
77
78 SELECT 1 INTO dummy
79 FROM bom_bill_of_materials bbom,
80 mtl_system_items msi1
81 WHERE bbom.source_bill_sequence_id = X_Bill_Sequence_Id
82 AND bbom.organization_id <> X_Organization_Id
83 AND msi1.inventory_item_id = bbom.assembly_item_id
84 AND msi1.organization_id = bbom.organization_id
85 AND NOT EXISTS (SELECT null
86 FROM mtl_system_items msi2
87 WHERE msi2.organization_id = bbom.organization_id
88 AND msi2.inventory_item_id = X_Component_Item_Id
89 --AND msi2.bom_enabled_flag = 'Y'
90 --Not a required condition.
91 AND ((bbom.assembly_type = 1 AND
92 msi2.eng_item_flag='N')
93 OR (bbom.assembly_type = 2)
94 OR (eng_items_for_mfg_ecos_flag = 'YES')) --bug1517975
95 AND msi2.inventory_item_id <> bbom.assembly_item_id
96 AND ((msi1.bom_item_type = 1
97 AND msi2.bom_item_type <> 3)
98 OR (msi1.bom_item_type = 2
99 AND msi2.bom_item_type <> 3)
100 OR (msi1.bom_item_type = 3)
101 OR (msi1.bom_item_type = 4
102 AND (msi2.bom_item_type = 4
103 OR (msi2.bom_item_type in (1,2)
104 AND msi2.replenish_to_order_flag = 'Y'
105 AND msi1.base_item_id is NOT NULL
106 AND msi1.replenish_to_order_flag = 'Y'
107 ))))
108 AND (msi1.bom_item_type = 3
109 OR msi1.pick_components_flag = 'Y'
110 OR msi2.pick_components_flag = 'N')
111 AND (msi1.bom_item_type = 3
112 OR nvl(msi2.bom_item_type, 4) <> 2
113 OR (msi2.bom_item_type = 2
114 AND ((msi1.pick_components_flag = 'Y'
115 AND msi2.pick_components_flag = 'Y')
116 OR (msi1.replenish_to_order_flag = 'Y'
117 AND msi2.replenish_to_order_flag = 'Y'
118 ))))
119 AND NOT (msi1.bom_item_type = 4
120 AND msi1.pick_components_flag = 'Y'
121 AND msi2.bom_item_type = 4
122 AND msi2.replenish_to_order_flag = 'Y')
123 );
124 fnd_message.set_name('INV','INV_NOT_VALID');
125 fnd_message.set_token('ENTITY','Component item_CAP',TRUE);
126 fnd_message.set_name('BOM','BOM_COMMON_OTHER_ORGS');
127 app_exception.raise_exception;
128 EXCEPTION
129 WHEN NO_DATA_FOUND THEN
130 null;
131 WHEN TOO_MANY_ROWS THEN
132 fnd_message.set_name('INV','INV_NOT_VALID');
133 fnd_message.set_token('ENTITY','Component item_CAP',TRUE);
134 fnd_message.set_name('BOM','BOM_COMMON_OTHER_ORGS');
135 app_exception.raise_exception;
136 END Check_Commons;
137
138
139 PROCEDURE Check_ATP(X_Organization_Id NUMBER,
140 X_Component_Item_Id NUMBER,
141 X_ATP_Comps_Flag VARCHAR2,
142 X_WIP_Supply_Type NUMBER,
143 X_Replenish_To_Order_Flag VARCHAR2,
144 X_Pick_Components_Flag VARCHAR2) IS
145 dummy NUMBER;
146 l_atp_comps_flag VARCHAR2(1);
147 l_atp_flag VARCHAR2(1);
148 BEGIN
149
150 -- Starting with R11, the ATP_Flag can have additional values R and C
151 -- apart from Y and N
152
153 -- Starting with 11i, even ATP Components flag has additional values which are
154 -- similar to ATP flag. To incorporate these values for multi-level ATP we also
155 -- release the update allowed constraint on Check_ATP
156
157 -- ATP Components flag for an item indicates whether an item's child components should be
158 -- ATP checked. A component c1 (ATP Check = Material) can be on a subassembly that does not
159 -- need to do atp check for components and hence has ATP Components of subassy is set to No. In
160 -- current validation c1 cannot be added onto the subassy because we restrict that.
161
162 -- We will now release the restriction on the ATP Check and ATP Components flag. This will allow the
163 -- users to control what can and cannot be structured on a bill. If the item level attribute for a
164 -- component is ATP Check = Yes, BOM will allow the user to turn it off at the component level.
165 -- The default value will be copied from the item.
166
167 null;
168 /*
169 SELECT atp_components_flag,
170 atp_flag
171 INTO l_atp_comps_flag,
172 l_atp_flag
173 FROM mtl_system_items msi
174 WHERE inventory_item_id = X_Component_Item_Id
175 AND organization_id = X_Organization_Id;
176
177 IF(( X_Atp_Comps_Flag = 'N' AND
178 ( NVL(X_Wip_Supply_Type,1) = 6 OR
179 X_Replenish_To_Order_Flag = 'Y' OR
180 X_Pick_Components_Flag = 'Y'
181 )
182 ) AND
183 ( l_atp_comps_flag IN ('Y','C', 'R', 'N') OR l_atp_flag IN ('Y', 'R', 'C','N' )
184 )
185 ) OR
186 X_Atp_Comps_flag IN ('Y','R','C')
187 THEN
188 -- Do nothing since this is permitted
189 -- If the Assembly item is Phantom or an ATO or PTO and has ATP Components as 'N'
190 -- Even then we will allow ATP components
191 NULL;
192 ELSIF (x_atp_comps_flag = 'N' AND
193 ( l_atp_comps_flag = 'N' AND l_atp_flag = 'N')
194 )
195 THEN
196 -- Even in this case do nothing since both the flag are N and hence is
197 -- is a valid combination
198
199 NULL;
200 ELSE
201 fnd_message.set_name('BOM','BOM_INVALID_ATP');
202 app_exception.raise_exception;
203 END IF;
204 */
205
206 END Check_ATP;
207
208
209 PROCEDURE Check_Unique(X_Rowid VARCHAR2,
210 X_Bill_Sequence_Id NUMBER,
211 X_Component_Item_id NUMBER,
212 X_Operation_Seq_Num NUMBER,
213 X_Effectivity_Date DATE,
214 X_bill_or_eco NUMBER) IS
215 dummy NUMBER;
216 BEGIN
217 SELECT 1 INTO dummy FROM dual WHERE NOT EXISTS
218 (SELECT 1 from bom_inventory_components
219 WHERE bill_sequence_id = X_Bill_Sequence_Id
220 AND component_item_id = X_Component_Item_Id
221 AND operation_seq_num = X_Operation_Seq_Num
222 AND effectivity_date = X_Effectivity_Date
223 AND NVL(ECO_FOR_PRODUCTION,2) = 2
224 AND ((X_Rowid is null) OR (rowid <> X_Rowid))
225 );
226
227 EXCEPTION
228 WHEN NO_DATA_FOUND THEN
229 IF (X_bill_or_eco = 1) THEN
230 fnd_message.set_name('BOM','BOM_COMPONENT_DUPLICATE');
231 ELSE
232 fnd_message.set_name('ENG','ENG_HAS_BEEN_MODIFIED');
233 END IF;
234 app_exception.raise_exception;
235 END Check_Unique;
236
237
238 PROCEDURE Check_Unique_From_Unit_Number(X_Rowid VARCHAR2,
239 X_Bill_Sequence_Id NUMBER,
240 X_Component_Item_id NUMBER,
241 X_Operation_Seq_Num NUMBER,
242 X_From_Unit_Number VARCHAR2,
243 X_bill_or_eco NUMBER) IS
244 dummy NUMBER;
245 BEGIN
246 SELECT 1 INTO dummy FROM dual WHERE NOT EXISTS
247 (SELECT 1 from bom_inventory_components
248 WHERE bill_sequence_id = X_Bill_Sequence_Id
249 AND component_item_id = X_Component_Item_Id
250 AND operation_seq_num = X_Operation_Seq_Num
251 AND from_end_item_unit_number = X_From_Unit_Number
252 AND ((X_Rowid is null) OR (rowid <> X_Rowid))
253 AND disable_date is NULL
254 AND NVL(ECO_FOR_PRODUCTION,2) = 2
255 AND ((X_bill_or_eco = 1) OR (X_bill_or_eco <> 1
256 AND implementation_date is null))
257 );
258
259 EXCEPTION
260 WHEN NO_DATA_FOUND THEN
261 IF (X_bill_or_eco = 1) THEN
262 fnd_message.set_name('BOM','BOM_COMP_DUP_UNIT');
263 ELSE
264 fnd_message.set_name('ENG','ENG_UNIT_NUMBER_MODIFIED');
265 END IF;
266 app_exception.raise_exception;
267 END Check_Unique_From_Unit_Number;
268
269
270 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
271 X_Operation_Seq_Num NUMBER,
272 X_Component_Item_Id NUMBER,
273 X_Last_Update_Date DATE,
274 X_Last_Updated_By NUMBER,
275 X_Creation_Date DATE,
276 X_Created_By NUMBER,
277 X_Last_Update_Login NUMBER,
278 X_Item_Num NUMBER,
279 X_Component_Quantity NUMBER,
280 X_Component_Yield_Factor NUMBER,
281 X_Component_Remarks VARCHAR2,
282 X_Effectivity_Date DATE,
283 X_Change_Notice VARCHAR2,
284 X_Implementation_Date DATE,
285 X_Disable_Date DATE,
286 X_Attribute_Category VARCHAR2,
287 X_Attribute1 VARCHAR2,
288 X_Attribute2 VARCHAR2,
289 X_Attribute3 VARCHAR2,
290 X_Attribute4 VARCHAR2,
291 X_Attribute5 VARCHAR2,
292 X_Attribute6 VARCHAR2,
293 X_Attribute7 VARCHAR2,
294 X_Attribute8 VARCHAR2,
295 X_Attribute9 VARCHAR2,
296 X_Attribute10 VARCHAR2,
297 X_Attribute11 VARCHAR2,
298 X_Attribute12 VARCHAR2,
299 X_Attribute13 VARCHAR2,
300 X_Attribute14 VARCHAR2,
301 X_Attribute15 VARCHAR2,
302 X_Planning_Factor NUMBER,
303 X_Quantity_Related NUMBER,
304 X_So_Basis NUMBER,
305 X_Optional NUMBER,
306 X_Mutually_Exclusive_Options NUMBER,
307 X_Include_In_Cost_Rollup NUMBER,
308 X_Check_Atp NUMBER,
309 X_Required_To_Ship NUMBER,
310 X_Required_For_Revenue NUMBER,
311 X_Include_On_Ship_Docs NUMBER,
312 X_Include_On_Bill_Docs NUMBER,
313 X_Low_Quantity NUMBER,
314 X_High_Quantity NUMBER,
315 X_Acd_Type NUMBER,
316 X_Old_Component_Sequence_Id NUMBER,
317 X_Component_Sequence_Id IN OUT NOCOPY NUMBER,
318 X_Bill_Sequence_Id NUMBER,
319 X_Wip_Supply_Type NUMBER,
320 X_Pick_Components NUMBER,
321 X_Supply_Subinventory VARCHAR2,
322 X_Supply_Locator_Id NUMBER,
323 X_Operation_Lead_Time_Percent NUMBER,
324 X_Revised_Item_Sequence_Id NUMBER,
325 X_Cost_Factor NUMBER,
326 X_Bom_Item_Type NUMBER,
327 X_From_Unit_Number VARCHAR2,
328 X_To_Unit_Number VARCHAR2,
329 X_Enforce_Int_Requirements NUMBER DEFAULT NULL,
330 X_auto_Request_Material VARCHAR2 DEFAULT NULL
331 ,X_Suggested_Vendor_Name VARCHAR2 DEFAULT NULL
332 ,X_Vendor_Id NUMBER DEFAULT NULL
333 ,X_Unit_Price NUMBER DEFAULT NULL
334 , X_basis_type NUMBER
335 ) IS
336 CURSOR C IS SELECT rowid FROM BOM_INVENTORY_COMPONENTS
337 WHERE component_sequence_id = X_Component_Sequence_Id;
338 CURSOR C2 IS SELECT bom_inventory_components_s.nextval FROM sys.dual;
339
340 l_object_revision_id NUMBER;
341 l_minor_revision_id NUMBER;
342 l_comp_revision_id NUMBER;
343 l_comp_minor_revision_id NUMBER;
344 l_err_tbl Error_Handler.Mesg_Token_Tbl_Type;
345 l_return_status VARCHAR2(10);
346 org_id number; --4306013
347 alt_bom_code varchar2(240); --4306013
348 ass_item_id NUMBER; --4306013
349 s_ass_comment varchar2(240); --4306013
350 BEGIN
351 if (X_Component_Sequence_Id is NULL) then
352 OPEN C2;
353 FETCH C2 INTO X_Component_Sequence_Id;
354 CLOSE C2;
355 end if;
356
357
358 --
359 -- With the enhancement to BOM functionality for supporting various PLM
360 -- requirements, BOM/Structures can have revisions and the components
361 -- can maintain effectivity with respect to these revisions.
362 -- We therefore now stamp the component with from_bill_revision_id
363 -- and from_structure_revision_code values.
364 -- These values are crucial for the explosion of BOM to work correctly for
365 -- a particular structure revision.
366 --
367
368 --
369 -- 1. Based on Component's Effectivity, get the Item Revision
370 -- 2. Get the max structure revsion id/code for the current bill
371 -- object_revision_id is null or object is 'EGO_ITEM' and
372 -- object_revision_id = item_revsion_id
373 -- 3. Use the values returned in 2 as the From_Bill_Rev_Id and
374 -- From_Structure_Revision_Code values for the component.
375 --
376
377
378
379 BOM_GLOBALS.GET_DEF_REV_ATTRS
380 ( p_bill_sequence_id => x_bill_sequence_id
381 , p_comp_item_id => x_component_item_id
382 , p_effectivity_date => x_effectivity_date
383 , x_object_revision_id => l_object_revision_id
384 , x_minor_revision_id => l_minor_revision_id
385 , x_comp_revision_id => l_comp_revision_id
386 , x_comp_minor_revision_id => l_comp_minor_revision_id
387 );
388
389
390 INSERT INTO BOM_INVENTORY_COMPONENTS(
391 operation_seq_num,
392 component_item_id,
393 last_update_date,
394 last_updated_by,
395 creation_date,
396 created_by,
397 last_update_login,
398 item_num,
399 component_quantity,
400 component_yield_factor,
401 component_remarks,
402 effectivity_date,
403 change_notice,
404 implementation_date,
405 disable_date,
406 attribute_category,
407 attribute1,
408 attribute2,
409 attribute3,
410 attribute4,
411 attribute5,
412 attribute6,
413 attribute7,
414 attribute8,
415 attribute9,
416 attribute10,
417 attribute11,
418 attribute12,
419 attribute13,
420 attribute14,
421 attribute15,
422 planning_factor,
423 quantity_related,
424 so_basis,
425 optional,
426 mutually_exclusive_options,
427 include_in_cost_rollup,
428 check_atp,
429 required_to_ship,
430 required_for_revenue,
431 include_on_ship_docs,
432 include_on_bill_docs,
433 low_quantity,
434 high_quantity,
435 acd_type,
436 old_component_sequence_id,
437 component_sequence_id,
438 bill_sequence_id,
439 wip_supply_type,
440 pick_components,
441 supply_subinventory,
442 supply_locator_id,
443 operation_lead_time_percent,
444 revised_item_sequence_id,
445 cost_factor,
446 bom_item_type,
447 from_end_item_unit_number,
448 to_end_item_unit_number,
449 enforce_int_requirements,
450 auto_request_material
451 ,suggested_vendor_name
452 ,vendor_id
453 ,unit_price
454 ,FROM_OBJECT_REVISION_ID
455 ,FROM_MINOR_REVISION_ID
456 --,COMPONENT_ITEM_REVISION_ID
457 --,COMPONENT_MINOR_REVISION_ID
458 ,basis_type
459 ) VALUES (
460 X_Operation_Seq_Num,
461 X_Component_Item_Id,
462 X_Last_Update_Date,
463 X_Last_Updated_By,
464 X_Creation_Date,
465 X_Created_By,
466 X_Last_Update_Login,
467 X_Item_Num,
468 X_Component_Quantity,
469 X_Component_Yield_Factor,
470 X_Component_Remarks,
471 X_Effectivity_Date,
472 X_Change_Notice,
473 X_Implementation_Date,
474 X_Disable_Date,
475 X_Attribute_Category,
476 X_Attribute1,
477 X_Attribute2,
478 X_Attribute3,
479 X_Attribute4,
480 X_Attribute5,
481 X_Attribute6,
482 X_Attribute7,
483 X_Attribute8,
484 X_Attribute9,
485 X_Attribute10,
486 X_Attribute11,
487 X_Attribute12,
488 X_Attribute13,
489 X_Attribute14,
490 X_Attribute15,
491 X_Planning_Factor,
492 X_Quantity_Related,
493 X_So_Basis,
494 X_Optional,
495 X_Mutually_Exclusive_Options,
496 X_Include_In_Cost_Rollup,
497 X_Check_Atp,
498 X_Required_To_Ship,
499 X_Required_For_Revenue,
500 X_Include_On_Ship_Docs,
501 X_Include_On_Bill_Docs,
502 X_Low_Quantity,
503 X_High_Quantity,
504 X_Acd_Type,
505 X_Old_Component_Sequence_Id,
506 X_Component_Sequence_Id,
507 X_Bill_Sequence_Id,
508 X_Wip_Supply_Type,
509 X_Pick_Components,
510 X_Supply_Subinventory,
511 X_Supply_Locator_Id,
512 X_Operation_Lead_Time_Percent,
513 X_Revised_Item_Sequence_Id,
514 X_Cost_Factor,
515 X_Bom_Item_Type,
516 X_From_Unit_Number,
517 X_To_Unit_Number,
518 X_Enforce_Int_Requirements,
519 X_Auto_Request_Material
520 ,X_Suggested_Vendor_Name
521 ,X_Vendor_Id
522 ,X_Unit_Price
523 , l_object_revision_id
524 , l_minor_revision_id
525 --, l_comp_revision_id
526 --, l_comp_minor_revision_id
527 ,X_basis_type
528 );
529 --Update referencing bills
530 BOMPCMBM.Insert_Related_Components(p_src_bill_seq_id => X_Bill_Sequence_Id
531 , p_src_comp_seq_id => X_Component_Sequence_Id
532 , x_Mesg_Token_Tbl => l_err_tbl
533 , x_Return_Status => l_return_status);
534 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
535 THEN
536 app_exception.raise_exception;
537 END IF;
538
539 OPEN C;
540 FETCH C INTO X_Rowid;
541 if (C%NOTFOUND) then
542 CLOSE C;
543 Raise NO_DATA_FOUND;
544 end if;
545 CLOSE C;
546
547
548 -- Raising Business event
549
550 SELECT bbm.Organization_Id, bbm.alternate_bom_designator, bbm.assembly_item_id, bbm.specific_assembly_comment
551 INTO org_id, alt_bom_code, ass_item_id, s_ass_comment
552 FROM Bom_Bill_Of_Materials bbm
553 WHERE bbm.Bill_Sequence_Id = X_Bill_Sequence_Id;
554
555
556 Bom_Business_Event_PKG.Raise_Bill_Event
557 (p_pk1_value => ass_item_id
558 , p_pk2_value => org_id
559 , p_obj_name => NULL
560 , p_structure_name => alt_bom_code
561 , p_organization_id => org_id
562 , p_structure_comment => s_ass_comment
563 , p_Event_Load_Type => 'Single'
564 , p_Event_Entity_Name => 'Component'
565 , p_Event_Entity_Parent_Id => X_Bill_Sequence_Id
566 , p_Event_Name => Bom_Business_Event_PKG.G_STRUCTURE_MODIFIED_EVENT
567 , p_last_update_date => X_Last_Update_Date
568 , p_last_updated_by => X_Last_Updated_By
569 , p_creation_date => X_Creation_Date
570 , p_created_by => X_Created_By
571 , p_last_update_login=> X_Last_Update_Login
572 , p_component_seq_id => X_Component_Sequence_Id
573 );
574 END Insert_Row;
575
576 END BOM_INV_COMPS1_PKG;