[Home] [Help]
PACKAGE BODY: APPS.BOM_BILL_OF_MATLS_PKG
Source
1 PACKAGE BODY BOM_BILL_OF_MATLS_PKG as
2 /* $Header: bompibmb.pls 120.9 2011/10/03 22:13:17 umajumde ship $ */
3
4 PROCEDURE Populate_Fields(
5 P_Bill_Sequence_Id in number,
6 P_Item_Seq_Increment in number,
7 P_Current_Rev IN OUT NOCOPY varchar2,
8 P_Base_Model IN OUT NOCOPY varchar2,
9 P_Base_Model_Desc IN OUT NOCOPY varchar2,
10 P_Common_Item IN OUT NOCOPY varchar2,
11 P_Common_Description IN OUT NOCOPY varchar2,
12 P_Item_Num_Default IN OUT NOCOPY number,
13 P_Common_Org_Code IN OUT NOCOPY varchar2,
14 P_Common_Org_Name IN OUT NOCOPY varchar2) IS
15
16 Cursor GetBillInfo is
17 Select bom.assembly_item_id,
18 bom.organization_id,
19 bom.common_bill_sequence_id,
20 bom.common_assembly_item_id,
21 bom.common_organization_id,
22 msi.base_item_id,
23 bom.source_bill_sequence_id
24 From bom_bill_of_materials bom,
25 mtl_system_items msi
26 Where bom.bill_sequence_id = P_Bill_Sequence_id
27 and msi.inventory_item_id = bom.assembly_item_id
28 and msi.organization_id = bom.organization_id;
29 Cursor GetCommon (P_Common_Assembly_Item_Id number,
30 P_Common_Organization_Id number) is
31 Select mif.item_number part,
32 mif.description description
33 From mtl_item_flexfields mif
34 Where mif.inventory_item_id = P_Common_Assembly_Item_Id
35 And mif.organization_id = P_Common_Organization_Id;
36 Cursor GetCommonOrg (P_Common_Organization_Id number) is
37 SELECT mp.organization_code ORGANIZATION_CODE, hou.name ORGANIZATION_NAME
38 FROM hr_all_organization_units_vl hou,
39 mtl_parameters mp
40 WHERE mp.organization_id = hou.organization_id
41 AND hou.organization_id = P_Common_Organization_Id;
42 Cursor GetBaseModel (P_Base_Item_Id number, P_Org_Id number) is
43 Select mif.item_number part,
44 mif.description description
45 From mtl_item_flexfields mif
46 Where mif.inventory_item_id = P_Base_Item_Id
47 And mif.organization_id = P_Org_Id;
48 CURSOR GetItemSeq IS
49 SELECT nvl(max(item_num), 0) + P_Item_Seq_Increment default_seq
50 FROM bom_inventory_components
51 WHERE bill_sequence_id = P_Bill_Sequence_Id;
52 No_Revision_Found EXCEPTION;
56 BEGIN
53 Pragma exception_init(no_revision_found, -20001);
54 BEGIN
55 For X_Bill in GetBillInfo loop
57 BOM_REVISIONS.Get_Revision(
58 type => 'PART',
59 eco_status => 'ALL',
60 examine_type => 'IMPL_ONLY',
61 org_id => X_Bill.Organization_Id,
62 item_id => X_Bill.Assembly_Item_Id,
63 rev_date => sysdate,
64 itm_rev => P_Current_Rev);
65 EXCEPTION
66 WHEN no_revision_found THEN
67 null;
68 END;
69 IF X_Bill.source_bill_sequence_id <> P_bill_sequence_id THEN
70 FOR X_CommonItem in GetCommon
71 (P_Common_Assembly_Item_Id => X_Bill.common_assembly_item_id,
72 P_Common_Organization_Id => X_Bill.common_organization_id) LOOP
73 P_Common_Item := X_CommonItem.Part;
74 P_Common_Description := X_CommonItem.Description;
75 END LOOP; -- common
76 FOR X_CommonOrg in GetCommonOrg
77 (P_Common_Organization_Id => X_Bill.common_organization_id) LOOP
78 P_Common_Org_Code := X_CommonOrg.ORGANIZATION_CODE;
79 P_Common_Org_Name := X_CommonOrg.ORGANIZATION_NAME;
80 END LOOP; -- Common Org
81 Else
82 P_Common_Item := null;
83 P_Common_Description := null;
84 P_Common_Org_Code := null;
85 P_Common_Org_Name := null;
86 END IF;
87
88 IF X_Bill.base_item_id is NOT NULL THEN
89 FOR X_BaseModel in GetBaseModel
90 (P_Base_Item_Id => X_Bill.base_item_id,
91 P_Org_Id => X_Bill.organization_id) LOOP
92 P_Base_Model := X_BaseModel.Part;
93 P_Base_Model_Desc := X_BaseModel.Description;
94 END LOOP; -- base model
95 Else
96 P_Base_Model := null;
97 P_Base_Model_Desc := null;
98 END IF;
99
100 -- Set Item Num default value
101 FOR X_Increment IN GetItemSeq LOOP
102 /*IF X_Increment.default_seq <= 9999 then
103 P_Item_Num_Default := X_increment.default_seq;
104 Else
105 P_Item_Num_Default := 9999;
106 End if;*/
107 P_Item_Num_Default := X_increment.default_seq;
108 END LOOP; -- increment
109 End loop; -- bill
110 END Populate_Fields;
111
112 PROCEDURE Check_Unique(X_Rowid VARCHAR2,
113 X_Assembly_Item_Id NUMBER,
114 X_Alternate_Bom_Designator VARCHAR2,
115 X_Organization_Id NUMBER) IS
116 dummy NUMBER;
117 BEGIN
118 SELECT 1 INTO dummy FROM dual
119 WHERE not exists
120 (SELECT 1 FROM bom_bill_of_materials
121 WHERE assembly_item_id = X_Assembly_item_id
122 AND nvl(alternate_bom_designator,'no alt') =
123 nvl(X_alternate_bom_designator,'no alt')
124 AND organization_id = X_organization_id
125 AND ((X_rowid is NULL) OR (rowid <> X_rowid))
126 );
127 EXCEPTION
128 WHEN no_data_found THEN
129 Fnd_Message.Set_Name('BOM','BOM_BILL_ALREADY_EXISTS');
130 Fnd_Message.Set_Token('ENTITY','BILL OF MATERIAL', TRUE);
131 App_Exception.Raise_Exception;
132 END Check_Unique;
133
134 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
135 X_Assembly_Item_Id NUMBER,
136 X_Organization_Id NUMBER,
137 X_Alternate_Bom_Designator VARCHAR2,
138 X_Last_Update_Date DATE,
139 X_Last_Updated_By NUMBER,
140 X_Creation_Date DATE,
141 X_Created_By NUMBER,
142 X_Last_Update_Login NUMBER,
143 X_Common_Assembly_Item_Id NUMBER,
144 X_Specific_Assembly_Comment VARCHAR2,
145 X_Pending_From_Ecn VARCHAR2,
146 X_Attribute_Category VARCHAR2,
147 X_Attribute1 VARCHAR2,
148 X_Attribute2 VARCHAR2,
149 X_Attribute3 VARCHAR2,
150 X_Attribute4 VARCHAR2,
151 X_Attribute5 VARCHAR2,
152 X_Attribute6 VARCHAR2,
153 X_Attribute7 VARCHAR2,
154 X_Attribute8 VARCHAR2,
155 X_Attribute9 VARCHAR2,
156 X_Attribute10 VARCHAR2,
157 X_Attribute11 VARCHAR2,
158 X_Attribute12 VARCHAR2,
159 X_Attribute13 VARCHAR2,
160 X_Attribute14 VARCHAR2,
161 X_Attribute15 VARCHAR2,
162 X_Assembly_Type NUMBER,
163 X_Common_Bill_Sequence_Id IN OUT NOCOPY NUMBER,
164 X_Bill_Sequence_Id IN OUT NOCOPY NUMBER,
165 X_Common_Organization_Id NUMBER,
166 X_Next_Explode_Date DATE,
167 X_structure_type_id NUMBER := NULL,
168 X_implementation_date DATE := NULL,
169 X_effectivity_control NUMBER := NULL
170
171 ) IS
172 l_preferred_flag Varchar2(1);
173 x_err_text varchar2(2000);
177 BEGIN
174 CURSOR C IS SELECT rowid FROM BOM_BILL_OF_MATERIALS
175 WHERE bill_sequence_id = X_Bill_Sequence_Id;
176 CURSOR C2 IS SELECT bom_inventory_components_s.nextval FROM sys.dual;
178 if (X_Bill_Sequence_Id is NULL) then
179 OPEN C2;
180 FETCH C2 INTO X_Bill_Sequence_Id;
181 CLOSE C2;
182 end if;
183 IF (X_Common_Assembly_Item_Id is NULL) THEN
184 X_Common_Bill_Sequence_Id := X_Bill_Sequence_Id;
185 ELSE
186 SELECT bill_sequence_id
187 INTO X_Common_Bill_Sequence_Id
188 FROM bom_bill_of_materials
189 WHERE organization_id = X_Common_Organization_Id
190 AND bill_sequence_id = common_bill_sequence_id
191 AND assembly_item_id = X_Common_Assembly_Item_Id
192 AND NVL(alternate_bom_designator, 'NONE') =
193 NVL(X_Alternate_Bom_Designator,'NONE');
194 END IF;
195 l_preferred_flag := BOM_Validate.Is_Preferred_Structure
196 (p_assembly_item_id => X_Assembly_item_Id,
197 p_organization_id => X_Organization_Id,
198 p_alternate_bom_code => X_Alternate_Bom_Designator,
199 x_err_text => x_err_text);
200
201 INSERT INTO BOM_BILL_OF_MATERIALS
202 ( assembly_item_id,
203 organization_id,
204 alternate_bom_designator,
205 last_update_date,
206 last_updated_by,
207 creation_date,
208 created_by,
209 last_update_login,
210 common_assembly_item_id,
211 specific_assembly_comment,
212 pending_from_ecn,
213 attribute_category,
214 attribute1,
215 attribute2,
216 attribute3,
217 attribute4,
218 attribute5,
219 attribute6,
220 attribute7,
221 attribute8,
222 attribute9,
223 attribute10,
224 attribute11,
225 attribute12,
226 attribute13,
227 attribute14,
228 attribute15,
229 assembly_type,
230 common_bill_sequence_id,
231 bill_sequence_id,
232 common_organization_id,
233 next_explode_date,
234 structure_type_id,
235 implementation_date,
236 effectivity_control,
237 is_preferred,
238 source_bill_sequence_id,
239 pk1_value,
240 pk2_value
241 ) VALUES (
242 X_Assembly_Item_Id,
243 X_Organization_Id,
244 X_Alternate_Bom_Designator,
245 X_Last_Update_Date,
246 X_Last_Updated_By,
247 X_Creation_Date,
248 X_Created_By,
249 X_Last_Update_Login,
250 X_Common_Assembly_Item_Id,
251 X_Specific_Assembly_Comment,
252 X_Pending_From_Ecn,
253 X_Attribute_Category,
254 X_Attribute1,
255 X_Attribute2,
256 X_Attribute3,
257 X_Attribute4,
258 X_Attribute5,
259 X_Attribute6,
260 X_Attribute7,
261 X_Attribute8,
262 X_Attribute9,
263 X_Attribute10,
264 X_Attribute11,
265 X_Attribute12,
266 X_Attribute13,
267 X_Attribute14,
268 X_Attribute15,
269 X_Assembly_Type,
270 X_Common_Bill_Sequence_Id,
271 X_Bill_Sequence_Id,
272 X_Common_Organization_Id,
273 X_Next_Explode_Date,
274 X_structure_type_id,
275 X_implementation_date,
276 X_effectivity_control,
277 decode ( l_preferred_flag, 'N',null,'Y'),
278 X_Common_Bill_Sequence_Id,
279 X_Assembly_Item_Id,
280 X_Organization_Id
281 );
282
283 OPEN C;
284 FETCH C INTO X_Rowid;
285 if (C%NOTFOUND) then
286 CLOSE C;
287 Raise NO_DATA_FOUND;
288 end if;
289 CLOSE C;
290
291
292 -- Raising Business event 4306013
293 Bom_Business_Event_PKG.Raise_Bill_Event
294 (p_pk1_value => X_Assembly_Item_Id
295 , p_pk2_value => X_Organization_Id
296 , p_obj_name => NULL
297 , p_structure_name => X_Alternate_Bom_Designator
298 , p_organization_id => X_Organization_Id
299 , p_structure_comment => X_Specific_Assembly_Comment
300 , p_Event_Load_Type => 'Single'
301 , p_Event_Entity_Name => 'Structure'
302 , p_Event_Entity_Parent_Id => X_Bill_Sequence_Id
303 , p_Event_Name => Bom_Business_Event_PKG.G_STRUCTURE_CREATION_EVENT
304 , p_last_update_date => X_Last_Update_Date
305 , p_last_updated_by => X_Last_Updated_By
306 , p_creation_date => X_Creation_Date
307 , p_created_by => X_Created_By
308 , p_last_update_login=> X_Last_Update_Login
309 );
310 END Insert_Row;
311
312
313 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
314 X_Assembly_Item_Id NUMBER,
315 X_Organization_Id NUMBER,
316 X_Alternate_Bom_Designator VARCHAR2,
317 X_Common_Assembly_Item_Id NUMBER,
321 X_Attribute1 VARCHAR2,
318 X_Specific_Assembly_Comment VARCHAR2,
319 X_Pending_From_Ecn VARCHAR2,
320 X_Attribute_Category VARCHAR2,
322 X_Attribute2 VARCHAR2,
323 X_Attribute3 VARCHAR2,
324 X_Attribute4 VARCHAR2,
325 X_Attribute5 VARCHAR2,
326 X_Attribute6 VARCHAR2,
327 X_Attribute7 VARCHAR2,
328 X_Attribute8 VARCHAR2,
329 X_Attribute9 VARCHAR2,
330 X_Attribute10 VARCHAR2,
331 X_Attribute11 VARCHAR2,
332 X_Attribute12 VARCHAR2,
333 X_Attribute13 VARCHAR2,
334 X_Attribute14 VARCHAR2,
335 X_Attribute15 VARCHAR2,
336 X_Assembly_Type NUMBER,
337 X_Common_Bill_Sequence_Id NUMBER,
338 X_Bill_Sequence_Id NUMBER,
339 X_Common_Organization_Id NUMBER,
340 X_Next_Explode_Date DATE,
341 X_structure_type_id NUMBER := NULL,
342 X_implementation_date DATE := NULL
343 ) IS
344 CURSOR C IS
345 SELECT *
346 FROM BOM_BILL_OF_MATERIALS
347 WHERE rowid = X_Rowid
348 FOR UPDATE of Bill_Sequence_Id NOWAIT;
349 Recinfo C%ROWTYPE;
350
351
352 BEGIN
353 OPEN C;
354 FETCH C INTO Recinfo;
355 if (C%NOTFOUND) then
356 CLOSE C;
357 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
358 APP_EXCEPTION.Raise_Exception;
359 end if;
360 CLOSE C;
361 if (
362 (Recinfo.assembly_item_id = X_Assembly_Item_Id)
363 AND (Recinfo.organization_id = X_Organization_Id)
364 AND ( (Recinfo.alternate_bom_designator = X_Alternate_Bom_Designator)
365 OR ( (Recinfo.alternate_bom_designator IS NULL)
366 AND (X_Alternate_Bom_Designator IS NULL)))
367 AND ( (Recinfo.common_assembly_item_id = X_Common_Assembly_Item_Id)
368 OR ( (Recinfo.common_assembly_item_id IS NULL)
369 AND (X_Common_Assembly_Item_Id IS NULL)))
370 AND ( (Recinfo.specific_assembly_comment = X_Specific_Assembly_Comment)
371 OR ( (Recinfo.specific_assembly_comment IS NULL)
372 AND (X_Specific_Assembly_Comment IS NULL)))
373 AND ( (Recinfo.pending_from_ecn = X_Pending_From_Ecn)
374 OR ( (Recinfo.pending_from_ecn IS NULL)
375 AND (X_Pending_From_Ecn IS NULL)))
376 AND ( (Recinfo.attribute_category = X_Attribute_Category)
377 OR ( (Recinfo.attribute_category IS NULL)
378 AND (X_Attribute_Category IS NULL)))
379 AND ( (Recinfo.attribute1 = X_Attribute1)
380 OR ( (Recinfo.attribute1 IS NULL)
381 AND (X_Attribute1 IS NULL)))
382 AND ( (Recinfo.attribute2 = X_Attribute2)
383 OR ( (Recinfo.attribute2 IS NULL)
384 AND (X_Attribute2 IS NULL)))
385 AND ( (Recinfo.attribute3 = X_Attribute3)
386 OR ( (Recinfo.attribute3 IS NULL)
387 AND (X_Attribute3 IS NULL)))
388 AND ( (Recinfo.attribute4 = X_Attribute4)
389 OR ( (Recinfo.attribute4 IS NULL)
390 AND (X_Attribute4 IS NULL)))
391 AND ( (Recinfo.attribute5 = X_Attribute5)
392 OR ( (Recinfo.attribute5 IS NULL)
393 AND (X_Attribute5 IS NULL)))
394 AND ( (Recinfo.attribute6 = X_Attribute6)
395 OR ( (Recinfo.attribute6 IS NULL)
396 AND (X_Attribute6 IS NULL)))
397 AND ( (Recinfo.attribute7 = X_Attribute7)
398 OR ( (Recinfo.attribute7 IS NULL)
399 AND (X_Attribute7 IS NULL)))
400 AND ( (Recinfo.attribute8 = X_Attribute8)
401 OR ( (Recinfo.attribute8 IS NULL)
402 AND (X_Attribute8 IS NULL)))
403 AND ( (Recinfo.attribute9 = X_Attribute9)
404 OR ( (Recinfo.attribute9 IS NULL)
405 AND (X_Attribute9 IS NULL)))
406 AND ( (Recinfo.attribute10 = X_Attribute10)
407 OR ( (Recinfo.attribute10 IS NULL)
408 AND (X_Attribute10 IS NULL)))
409 AND ( (Recinfo.attribute11 = X_Attribute11)
410 OR ( (Recinfo.attribute11 IS NULL)
411 AND (X_Attribute11 IS NULL)))
412 AND ( (Recinfo.attribute12 = X_Attribute12)
413 OR ( (Recinfo.attribute12 IS NULL)
414 AND (X_Attribute12 IS NULL)))
415 AND ( (Recinfo.attribute13 = X_Attribute13)
416 OR ( (Recinfo.attribute13 IS NULL)
417 AND (X_Attribute13 IS NULL)))
418 AND ( (Recinfo.attribute14 = X_Attribute14)
419 OR ( (Recinfo.attribute14 IS NULL)
420 AND (X_Attribute14 IS NULL)))
421 AND ( (Recinfo.attribute15 = X_Attribute15)
422 OR ( (Recinfo.attribute15 IS NULL)
423 AND (X_Attribute15 IS NULL)))
424 AND (Recinfo.assembly_type = X_Assembly_Type)
428 OR ( (Recinfo.common_organization_id IS NULL)
425 AND (Recinfo.common_bill_sequence_id = X_Common_Bill_Sequence_Id)
426 AND (Recinfo.bill_sequence_id = X_Bill_Sequence_Id)
427 AND ( (Recinfo.common_organization_id = X_Common_Organization_Id)
429 AND (X_Common_Organization_Id IS NULL)))
430 AND ( (Recinfo.next_explode_date = X_Next_Explode_Date)
431 OR ( (Recinfo.next_explode_date IS NULL)
432 AND (X_Next_Explode_Date IS NULL)))
433 AND ( (Recinfo.structure_type_id = X_Structure_Type_Id)
434 OR ( (Recinfo.structure_type_id IS NULL)
435 AND (X_Structure_Type_Id IS NULL)))
436 AND ( (Recinfo.implementation_date = X_Implementation_Date)
437 OR ( (Recinfo.implementation_date IS NULL)
438 AND (X_Implementation_Date IS NULL)))
439 ) then
440 return;
441 else
442 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
443 APP_EXCEPTION.Raise_Exception;
444 end if;
445 END Lock_Row;
446
447
448 PROCEDURE Update_Row(X_Rowid VARCHAR2,
449 X_Assembly_Item_Id NUMBER,
450 X_Organization_Id NUMBER,
451 X_Alternate_Bom_Designator VARCHAR2,
452 X_Last_Update_Date DATE,
453 X_Last_Updated_By NUMBER,
454 X_Last_Update_Login NUMBER,
455 X_Common_Assembly_Item_Id NUMBER,
456 X_Specific_Assembly_Comment VARCHAR2,
457 X_Pending_From_Ecn VARCHAR2,
458 X_Attribute_Category VARCHAR2,
459 X_Attribute1 VARCHAR2,
460 X_Attribute2 VARCHAR2,
461 X_Attribute3 VARCHAR2,
462 X_Attribute4 VARCHAR2,
463 X_Attribute5 VARCHAR2,
464 X_Attribute6 VARCHAR2,
465 X_Attribute7 VARCHAR2,
466 X_Attribute8 VARCHAR2,
467 X_Attribute9 VARCHAR2,
468 X_Attribute10 VARCHAR2,
469 X_Attribute11 VARCHAR2,
470 X_Attribute12 VARCHAR2,
471 X_Attribute13 VARCHAR2,
472 X_Attribute14 VARCHAR2,
473 X_Attribute15 VARCHAR2,
474 X_Assembly_Type NUMBER,
475 X_Common_Bill_Sequence_Id IN OUT NOCOPY NUMBER,
476 X_Bill_Sequence_Id NUMBER,
477 X_Common_Organization_Id NUMBER,
478 X_Next_Explode_Date DATE,
479 X_structure_type_id NUMBER := NULL,
480 X_implementation_date DATE := NULL,
481 X_effectivity_control NUMBER := NULL
482
483 ) IS
484 l_creation_date DATE;
485 l_created_by NUMBER;
486 BEGIN
487 --commented out the following code for bug 13035377
488 /* IF (X_Common_Assembly_Item_Id is NULL) THEN
489 X_Common_Bill_Sequence_Id := X_Bill_Sequence_Id;
490 ELSE
491 SELECT bill_sequence_id
492 INTO X_Common_Bill_Sequence_Id
493 FROM bom_bill_of_materials
494 WHERE organization_id = X_Common_Organization_Id
495 AND bill_sequence_id = common_bill_sequence_id
496 AND assembly_item_id = X_Common_Assembly_Item_Id
497 AND NVL(alternate_bom_designator, 'NONE') =
498 NVL(X_Alternate_Bom_Designator,'NONE');
499 END IF; */
500
501 UPDATE BOM_BILL_OF_MATERIALS
502 SET
503 assembly_item_id = X_Assembly_Item_Id,
504 organization_id = X_Organization_Id,
505 alternate_bom_designator = X_Alternate_Bom_Designator,
506 last_update_date = X_Last_Update_Date,
507 last_updated_by = X_Last_Updated_By,
508 last_update_login = X_Last_Update_Login,
509 common_assembly_item_id = X_Common_Assembly_Item_Id,
510 specific_assembly_comment = X_Specific_Assembly_Comment,
511 pending_from_ecn = X_Pending_From_Ecn,
512 attribute_category = X_Attribute_Category,
513 attribute1 = X_Attribute1,
514 attribute2 = X_Attribute2,
515 attribute3 = X_Attribute3,
516 attribute4 = X_Attribute4,
517 attribute5 = X_Attribute5,
518 attribute6 = X_Attribute6,
519 attribute7 = X_Attribute7,
520 attribute8 = X_Attribute8,
521 attribute9 = X_Attribute9,
522 attribute10 = X_Attribute10,
523 attribute11 = X_Attribute11,
524 attribute12 = X_Attribute12,
525 attribute13 = X_Attribute13,
526 attribute14 = X_Attribute14,
527 attribute15 = X_Attribute15,
528 assembly_type = X_Assembly_Type,
529 common_bill_sequence_id = X_Common_Bill_Sequence_Id,
530 bill_sequence_id = X_Bill_Sequence_Id,
531 common_organization_id = X_Common_Organization_Id,
532 next_explode_date = X_Next_Explode_Date,
533 structure_type_id = X_structure_type_id,
534 implementation_date = X_implementation_date,
535 effectivity_control = X_effectivity_control
536 WHERE rowid = X_Rowid;
537
538 if (SQL%NOTFOUND) then
539 Raise NO_DATA_FOUND;
540 end if;
541
542 SELECT creation_date,created_by INTO l_creation_date,l_created_by
543 FROM bom_bill_of_materials
544 WHERE organization_id = X_Organization_Id
545 AND bill_sequence_id = X_Bill_Sequence_Id
546 AND assembly_item_id = X_Assembly_Item_Id
547 AND NVL(alternate_bom_designator, 'NONE') =
548 NVL(X_Alternate_Bom_Designator,'NONE');
549
550 -- Raising Business event 4306013
551 Bom_Business_Event_PKG.Raise_Bill_Event
552 (p_pk1_value => X_Assembly_Item_Id
553 , p_pk2_value => X_Organization_Id
554 , p_obj_name => NULL
555 , p_structure_name => X_Alternate_Bom_Designator
556 , p_organization_id => X_Organization_Id
557 , p_structure_comment => X_Specific_Assembly_Comment
558 , p_Event_Load_Type => 'Single'
559 , p_Event_Entity_Name => 'Structure'
560 , p_Event_Entity_Parent_Id => X_Bill_Sequence_Id
561 , p_Event_Name => Bom_Business_Event_PKG.G_STRUCTURE_MODIFIED_EVENT
562 , p_last_update_date => X_Last_Update_Date
563 , p_last_updated_by => X_Last_Updated_By
564 , p_creation_date => l_creation_date
565 , p_created_by => l_created_by
566 , p_last_update_login=> X_Last_Update_Login
567 );
568 END Update_Row;
569
570 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
571
572 l_Structure_Name VARCHAR2(10);
573 l_Organization_Id NUMBER;
574 l_Assembly_Item_Id NUMBER;
575 l_specific_assembly_comment VARCHAR2(240);
576 -- bill_seq_id NUMBER; -- 4306013
577
578 BEGIN
579 DELETE FROM BOM_BILL_OF_MATERIALS
580 WHERE rowid = X_Rowid;
581
582 -- Getting the structure name and comments to raise the business event
583 -- SELECT bbm.Organization_Id, bbm.Assembly_Item_Id, bbm.Alternate_Bom_Designator, specific_assembly_comment
584 -- INTO l_Organization_Id, l_Assembly_Item_Id, l_Structure_Name, l_specific_assembly_comment
585 -- FROM Bom_Bill_Of_Materials bbm
586 -- WHERE rowid = X_Rowid;
587
588 -- SELECT bill_sequence_id into bill_seq_id
589 -- from bom_structures_b
590 -- where assembly_item_id = l_Assembly_Item_Id
591 -- and organization_id = l_Organization_Id
592 -- and Alternate_Bom_Designator = l_Structure_Name;
593
594 if (SQL%NOTFOUND) then
595 Raise NO_DATA_FOUND;
596 end if;
597
598 END Delete_Row;
599
600
601 END BOM_BILL_OF_MATLS_PKG;