[Home] [Help]
PACKAGE BODY: APPS.BOM_SUB_COMPS_PKG
Source
1 PACKAGE BODY BOM_SUB_COMPS_PKG as
2 /* $Header: bompiscb.pls 120.7.12000000.2 2007/06/18 06:31:21 pgandhik ship $ */
3
4
5 PROCEDURE Raise_Business_Event( p_Component_Sequence_Id NUMBER,
6 p_last_update_date DATE,
7 p_last_updated_by NUMBER,
8 p_creation_date DATE,
9 p_created_by NUMBER,
10 p_last_update_login NUMBER
11 );
12
13
14 PROCEDURE Get_Uom(X_uom_code IN OUT NOCOPY VARCHAR2,
15 X_sub_comp_id NUMBER,
16 X_org_id NUMBER) IS
17
18 BEGIN
19 SELECT primary_uom_code
20 INTO X_uom_code
21 FROM mtl_system_items
22 WHERE inventory_item_id = X_sub_comp_id
23 AND organization_id = X_org_id;
24
25 END Get_Uom;
26
27 PROCEDURE Check_Unique(X_acd_type NUMBER,
28 X_sub_comp_id NUMBER,
29 X_comp_seq_id NUMBER,
30 X_row_id VARCHAR2) IS
31 dummy NUMBER;
32
36 FROM dual
33 BEGIN
34 SELECT 1
35 INTO dummy
37 WHERE not exists
38 (SELECT 'x' FROM bom_substitute_components
39 WHERE nvl(acd_type, 1) = nvl(X_acd_type, 1)
40 AND substitute_component_id = X_sub_comp_id
41 AND component_sequence_id = X_comp_seq_id
42 AND ((X_row_id is NULL) OR (rowid <> X_row_id))
43 );
44 EXCEPTION
45 WHEN NO_DATA_FOUND THEN
46 fnd_message.set_name('BOM','BOM_DUPLICATE_SUB_COMP');
47 app_exception.raise_exception;
48
49 END Check_Unique;
50
51 PROCEDURE Check_Commons(X_bill_seq_id NUMBER,
52 X_org_id NUMBER,
53 X_sub_comp_id NUMBER) IS
54 counter NUMBER;
55
56 BEGIN
57 SELECT 1
58 INTO counter
59 FROM bom_bill_of_materials bbom
60 WHERE bbom.common_bill_sequence_id = X_bill_seq_id
61 AND bbom.organization_id <> X_org_id
62 AND not exists
63 (SELECT null
64 FROM mtl_system_items msi
65 WHERE msi.organization_id = bbom.organization_id
66 AND msi.inventory_item_id = X_sub_comp_id
67 AND msi.bom_enabled_flag = 'Y'
68 AND ((bbom.assembly_type = 1
69 AND msi.eng_item_flag = 'N')
70 OR (bbom.assembly_type = 2)))
71 AND ROWNUM=1; /* Bug 6134795 To insert a value 1 into counter
72 if one or more common bills exist if substitute component
73 does not exist in Other organizations */
74 fnd_message.set_name('INV','INV_NOT_VALID');
75 fnd_message.set_token('ENTITY','Substitute item', TRUE);
76 app_exception.raise_exception;
77
78 EXCEPTION
79 WHEN NO_DATA_FOUND THEN
80 null;
81
82 END Check_Commons;
83
84 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
85 X_Substitute_Component_Id NUMBER,
86 X_Last_Update_Date DATE,
87 X_Last_Updated_By NUMBER,
88 X_Creation_Date DATE,
89 X_Created_By NUMBER,
90 X_Last_Update_Login NUMBER,
91 X_Substitute_Item_Quantity NUMBER,
92 X_Component_Sequence_Id NUMBER,
93 X_Acd_Type NUMBER,
94 X_Change_Notice VARCHAR2,
95 X_Attribute_Category VARCHAR2,
96 X_Attribute1 VARCHAR2,
97 X_Attribute2 VARCHAR2,
98 X_Attribute3 VARCHAR2,
99 X_Attribute4 VARCHAR2,
100 X_Attribute5 VARCHAR2,
101 X_Attribute6 VARCHAR2,
102 X_Attribute7 VARCHAR2,
103 X_Attribute8 VARCHAR2,
104 X_Attribute9 VARCHAR2,
105 X_Attribute10 VARCHAR2,
106 X_Attribute11 VARCHAR2,
107 X_Attribute12 VARCHAR2,
108 X_Attribute13 VARCHAR2,
109 X_Attribute14 VARCHAR2,
110 X_Attribute15 VARCHAR2,
111 X_Enforce_Int_Requirements NUMBER DEFAULT NULL
112 ) IS
113 CURSOR C IS SELECT rowid FROM BOM_SUBSTITUTE_COMPONENTS
114 WHERE component_sequence_id = X_Component_Sequence_Id
115 AND ( (acd_type = X_Acd_Type)
116 or (acd_type is NULL and X_Acd_Type is NULL));
117
118 l_err_tbl Error_Handler.Mesg_Token_Tbl_Type;
119 l_return_status VARCHAR2(10);
120
121 BEGIN
122
123
124 INSERT INTO BOM_SUBSTITUTE_COMPONENTS(
125 substitute_component_id,
126 last_update_date,
127 last_updated_by,
128 creation_date,
129 created_by,
130 last_update_login,
131 substitute_item_quantity,
132 component_sequence_id,
133 acd_type,
134 change_notice,
135 enforce_int_requirements,
136 attribute_category,
137 attribute1,
138 attribute2,
139 attribute3,
140 attribute4,
141 attribute5,
142 attribute6,
143 attribute7,
144 attribute8,
145 attribute9,
146 attribute10,
147 attribute11,
148 attribute12,
149 attribute13,
150 attribute14,
151 attribute15
152 ) VALUES (
153 X_Substitute_Component_Id,
154 X_Last_Update_Date,
155 X_Last_Updated_By,
156 X_Creation_Date,
157 X_Created_By,
158 X_Last_Update_Login,
159 X_Substitute_Item_Quantity,
160 X_Component_Sequence_Id,
161 X_Acd_Type,
162 X_Change_Notice,
163 X_Enforce_Int_Requirements,
164 X_Attribute_Category,
165 X_Attribute1,
169 X_Attribute5,
166 X_Attribute2,
167 X_Attribute3,
168 X_Attribute4,
170 X_Attribute6,
171 X_Attribute7,
172 X_Attribute8,
173 X_Attribute9,
174 X_Attribute10,
175 X_Attribute11,
176 X_Attribute12,
177 X_Attribute13,
178 X_Attribute14,
179 X_Attribute15
180 );
181
182 OPEN C;
183 FETCH C INTO X_Rowid;
184 if (C%NOTFOUND) then
185 CLOSE C;
186 Raise NO_DATA_FOUND;
187 end if;
188 CLOSE C;
189 BOMPCMBM.Insert_Related_Sub_Comp(p_component_sequence_id => X_Component_Sequence_Id
190 , p_sub_comp_item_id => X_Substitute_Component_Id
191 , x_Mesg_Token_Tbl => l_err_tbl
192 , x_Return_Status => l_return_status);
193 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
194 THEN
195 app_exception.raise_exception;
196 END IF;
197
198 -- Calling Raise_Business_Event to raise business event
199 Raise_Business_Event(X_Component_Sequence_Id,X_Last_Update_Date,X_Last_Updated_By,
200 X_Creation_Date,X_Created_By,X_Last_Update_Login);
201
202 END Insert_Row;
203
204
205 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
206 X_Substitute_Component_Id NUMBER,
207 X_Substitute_Item_Quantity NUMBER,
208 X_Component_Sequence_Id NUMBER,
209 X_Acd_Type NUMBER,
210 X_Change_Notice VARCHAR2,
211 X_Attribute_Category VARCHAR2,
212 X_Attribute1 VARCHAR2,
213 X_Attribute2 VARCHAR2,
214 X_Attribute3 VARCHAR2,
215 X_Attribute4 VARCHAR2,
216 X_Attribute5 VARCHAR2,
217 X_Attribute6 VARCHAR2,
218 X_Attribute7 VARCHAR2,
219 X_Attribute8 VARCHAR2,
220 X_Attribute9 VARCHAR2,
221 X_Attribute10 VARCHAR2,
222 X_Attribute11 VARCHAR2,
223 X_Attribute12 VARCHAR2,
224 X_Attribute13 VARCHAR2,
225 X_Attribute14 VARCHAR2,
226 X_Attribute15 VARCHAR2,
227 X_Enforce_Int_Requirements NUMBER DEFAULT NULL
228 ) IS
229 CURSOR C IS
230 SELECT *
231 FROM BOM_SUBSTITUTE_COMPONENTS
232 WHERE rowid = X_Rowid
233 FOR UPDATE of Component_Sequence_Id NOWAIT;
234 Recinfo C%ROWTYPE;
235
236
237 BEGIN
238 OPEN C;
239 FETCH C INTO Recinfo;
240 if (C%NOTFOUND) then
241 CLOSE C;
242 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
243 APP_EXCEPTION.Raise_Exception;
244 end if;
245 CLOSE C;
246 if (
247 (Recinfo.substitute_component_id = X_Substitute_Component_Id)
248 AND (Recinfo.substitute_item_quantity = X_Substitute_Item_Quantity)
249 AND (Recinfo.component_sequence_id = X_Component_Sequence_Id)
250 AND ( (Recinfo.acd_type = X_Acd_Type)
251 OR ( (Recinfo.acd_type IS NULL)
252 AND (X_Acd_Type IS NULL)))
253 AND ( (Recinfo.change_notice = X_Change_Notice)
254 OR ( (Recinfo.change_notice IS NULL)
255 AND (X_Change_Notice IS NULL)))
256 AND ( (Recinfo.enforce_int_requirements = X_Enforce_Int_Requirements)
257 OR ( (Recinfo.enforce_int_requirements IS NULL)
258 AND (X_Enforce_Int_Requirements IS NULL)))
259 AND ( (Recinfo.attribute_category = X_Attribute_Category)
260 OR ( (Recinfo.attribute_category IS NULL)
261 AND (X_Attribute_Category IS NULL)))
262 AND ( (Recinfo.attribute1 = X_Attribute1)
263 OR ( (Recinfo.attribute1 IS NULL)
264 AND (X_Attribute1 IS NULL)))
265 AND ( (Recinfo.attribute2 = X_Attribute2)
266 OR ( (Recinfo.attribute2 IS NULL)
267 AND (X_Attribute2 IS NULL)))
268 AND ( (Recinfo.attribute3 = X_Attribute3)
269 OR ( (Recinfo.attribute3 IS NULL)
270 AND (X_Attribute3 IS NULL)))
271 AND ( (Recinfo.attribute4 = X_Attribute4)
272 OR ( (Recinfo.attribute4 IS NULL)
273 AND (X_Attribute4 IS NULL)))
274 AND ( (Recinfo.attribute5 = X_Attribute5)
275 OR ( (Recinfo.attribute5 IS NULL)
276 AND (X_Attribute5 IS NULL)))
277 AND ( (Recinfo.attribute6 = X_Attribute6)
278 OR ( (Recinfo.attribute6 IS NULL)
279 AND (X_Attribute6 IS NULL)))
280 AND ( (Recinfo.attribute7 = X_Attribute7)
281 OR ( (Recinfo.attribute7 IS NULL)
282 AND (X_Attribute7 IS NULL)))
283 AND ( (Recinfo.attribute8 = X_Attribute8)
284 OR ( (Recinfo.attribute8 IS NULL)
288 AND (X_Attribute9 IS NULL)))
285 AND (X_Attribute8 IS NULL)))
286 AND ( (Recinfo.attribute9 = X_Attribute9)
287 OR ( (Recinfo.attribute9 IS NULL)
289 AND ( (Recinfo.attribute10 = X_Attribute10)
290 OR ( (Recinfo.attribute10 IS NULL)
291 AND (X_Attribute10 IS NULL)))
292 AND ( (Recinfo.attribute11 = X_Attribute11)
293 OR ( (Recinfo.attribute11 IS NULL)
294 AND (X_Attribute11 IS NULL)))
295 AND ( (Recinfo.attribute12 = X_Attribute12)
296 OR ( (Recinfo.attribute12 IS NULL)
297 AND (X_Attribute12 IS NULL)))
298 AND ( (Recinfo.attribute13 = X_Attribute13)
299 OR ( (Recinfo.attribute13 IS NULL)
300 AND (X_Attribute13 IS NULL)))
301 AND ( (Recinfo.attribute14 = X_Attribute14)
302 OR ( (Recinfo.attribute14 IS NULL)
303 AND (X_Attribute14 IS NULL)))
304 AND ( (Recinfo.attribute15 = X_Attribute15)
305 OR ( (Recinfo.attribute15 IS NULL)
306 AND (X_Attribute15 IS NULL)))
307 ) then
308 return;
309 else
310 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
311 APP_EXCEPTION.Raise_Exception;
312 end if;
313 END Lock_Row;
314
315
316
317 PROCEDURE Update_Row(X_Rowid VARCHAR2,
318 X_Substitute_Component_Id NUMBER,
319 X_Last_Update_Date DATE,
320 X_Last_Updated_By NUMBER,
321 X_Last_Update_Login NUMBER,
322 X_Substitute_Item_Quantity NUMBER,
323 X_Component_Sequence_Id NUMBER,
324 X_Acd_Type NUMBER,
325 X_Change_Notice VARCHAR2,
326 X_Attribute_Category VARCHAR2,
327 X_Attribute1 VARCHAR2,
328 X_Attribute2 VARCHAR2,
329 X_Attribute3 VARCHAR2,
330 X_Attribute4 VARCHAR2,
331 X_Attribute5 VARCHAR2,
332 X_Attribute6 VARCHAR2,
333 X_Attribute7 VARCHAR2,
334 X_Attribute8 VARCHAR2,
335 X_Attribute9 VARCHAR2,
336 X_Attribute10 VARCHAR2,
337 X_Attribute11 VARCHAR2,
338 X_Attribute12 VARCHAR2,
339 X_Attribute13 VARCHAR2,
340 X_Attribute14 VARCHAR2,
341 X_Attribute15 VARCHAR2,
342 X_Enforce_Int_Requirements NUMBER DEFAULT NULL
343
344 ) IS
345 l_err_tbl Error_Handler.Mesg_Token_Tbl_Type;
346 l_return_status VARCHAR2(10);
347 l_old_sub_comp_id NUMBER;
348 l_acd_type NUMBER;
349 BEGIN
350 SELECT substitute_component_id, ACD_TYPE
351 INTO l_old_sub_comp_id, l_acd_type
352 FROM BOM_SUBSTITUTE_COMPONENTS
353 WHERE rowid = X_Rowid;
354
355 UPDATE BOM_SUBSTITUTE_COMPONENTS
356 SET
357 substitute_component_id = X_Substitute_Component_Id,
358 last_update_date = X_Last_Update_Date,
359 last_updated_by = X_Last_Updated_By,
360 last_update_login = X_Last_Update_Login,
361 substitute_item_quantity = X_Substitute_Item_Quantity,
362 component_sequence_id = X_Component_Sequence_Id,
363 acd_type = X_Acd_Type,
364 change_notice = X_Change_Notice,
365 enforce_int_requirements = X_Enforce_Int_Requirements,
366 attribute_category = X_Attribute_Category,
367 attribute1 = X_Attribute1,
368 attribute2 = X_Attribute2,
369 attribute3 = X_Attribute3,
370 attribute4 = X_Attribute4,
371 attribute5 = X_Attribute5,
372 attribute6 = X_Attribute6,
373 attribute7 = X_Attribute7,
374 attribute8 = X_Attribute8,
375 attribute9 = X_Attribute9,
376 attribute10 = X_Attribute10,
377 attribute11 = X_Attribute11,
378 attribute12 = X_Attribute12,
379 attribute13 = X_Attribute13,
380 attribute14 = X_Attribute14,
381 attribute15 = X_Attribute15
382 WHERE rowid = X_Rowid;
383
384 if (SQL%NOTFOUND) then
385 Raise NO_DATA_FOUND;
386 end if;
387 BOMPCMBM.Update_Related_Sub_Comp(p_component_sequence_id => X_Component_Sequence_Id
388 , p_old_sub_comp_item_id => l_old_sub_comp_id
389 , p_new_sub_comp_item_id=> X_Substitute_Component_ID
390 , p_acd_type => l_acd_type
391 , x_Mesg_Token_Tbl => l_err_tbl
392 , x_Return_Status => l_return_status);
393
394 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
395 THEN
396 app_exception.raise_exception;
397 END IF;
398
399 -- Calling Raise_Business_Event to raise business event
400 Raise_Business_Event(X_Component_Sequence_Id,X_Last_Update_Date,X_Last_Updated_By,
401 NULL,NULL,X_Last_Update_Login);
402
403 END Update_Row;
404
405 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
406 l_Component_Sequence_Id NUMBER;
407 l_common_component_sequence_id NUMBER;
408 l_sub_comp_id NUMBER;
409 l_return_status VARCHAR2(1);
410 BEGIN
411 Select component_sequence_id, substitute_component_id
412 into l_common_component_sequence_id, l_sub_comp_id
413 From BOM_SUBSTITUTE_COMPONENTS
414 WHERE rowid = X_Rowid;
415
416
417 DELETE FROM BOM_SUBSTITUTE_COMPONENTS
418 WHERE rowid = X_Rowid;
419
420 if (SQL%NOTFOUND) then
421 Raise NO_DATA_FOUND;
422 end if;
423
424 BOMPCMBM.Delete_Related_Sub_Comp(p_src_comp_seq => l_common_component_sequence_id
425 ,p_sub_comp_item_id => l_sub_comp_id
426 ,x_return_status => l_return_status);
427 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
428 THEN
429 app_exception.raise_exception;
430 END IF;
431
432 -- Calling Raise_Business_Event to raise business event
433 Raise_Business_Event(l_common_component_sequence_Id,sysdate,fnd_global.user_id,NULL,NULL,NULL);
434
435 Exception
436 WHEN NO_DATA_FOUND THEN
437 Raise NO_DATA_FOUND;
438
439
440 END Delete_Row;
441
442
443 PROCEDURE Raise_Business_Event( p_Component_Sequence_Id NUMBER,
444 p_last_update_date DATE,
445 p_last_updated_by NUMBER,
446 p_creation_date DATE,
447 p_created_by NUMBER,
448 p_last_update_login NUMBER
449 ) IS --4306013
450 l_Component_Item_Name VARCHAR2(512);
451 l_Component_Item_Id NUMBER;
452 l_Bill_Sequence_Id NUMBER;
453 l_Organization_Id NUMBER;
454 l_Component_Remarks VARCHAR2(240);
455
456 BEGIN
457
458 SELECT bic.Bill_Sequence_Id, bbm.Organization_Id, bic.Component_Item_Id,
459 bic.Component_Remarks, msi.Concatenated_Segments
460 INTO l_Bill_Sequence_Id, l_Organization_Id, l_Component_Item_Id,
461 l_Component_Remarks, l_Component_Item_Name
462 FROM Bom_Bill_Of_Materials bbm, Bom_Inventory_Components bic, Mtl_System_Items_Kfv msi
463 WHERE bbm.Bill_Sequence_Id = bic.Bill_Sequence_Id
464 And msi.Inventory_Item_Id = bic.Component_Item_Id
465 And msi.Organization_Id = bbm.Organization_Id
466 And bic.Component_Sequence_Id = p_Component_Sequence_Id;
467
468 Bom_Business_Event_PKG.Raise_Component_Event
469 ( p_bill_sequence_Id => l_Bill_Sequence_Id
470 , p_pk1_value => l_Component_Item_Id
471 , p_pk2_value => l_Organization_Id
472 , p_obj_name => NULL
473 , p_organization_id => l_Organization_Id
474 , p_comp_item_name => l_Component_Item_Name
475 , p_comp_description => l_Component_Remarks
476 , p_Event_Load_Type => 'Single'
477 , p_Event_Entity_Name => 'Substitute Component'
478 , p_Event_Entity_Parent_Id => p_Component_Sequence_Id
479 , p_Event_Name => Bom_Business_Event_PKG.G_COMPONENT_MODIFIED_EVENT
480 , p_last_update_date => p_last_update_date
481 , p_last_updated_by => p_last_updated_by
482 , p_creation_date => p_creation_date
483 , p_created_by => p_created_by
484 , p_last_update_login => p_last_update_login
485 );
486 END;
487
488 END BOM_SUB_COMPS_PKG;