[Home] [Help]
PACKAGE BODY: APPS.BOM_REF_DESIG_PKG
Source
1 PACKAGE BODY BOM_REF_DESIG_PKG as
2 /* $Header: bompirdb.pls 120.8 2006/06/11 19:31:41 seradhak ship $ */
3
4 -- Business Event 4306013
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 PROCEDURE Check_Unique(X_rowid VARCHAR2,
14 X_component_sequence_id NUMBER,
15 X_designator VARCHAR2) IS
16 dummy NUMBER;
17 BEGIN
18 SELECT 1 into dummy from dual
19 WHERE NOT EXISTS
20 (SELECT 1 from bom_reference_designators
21 WHERE component_sequence_id = X_component_sequence_id
22 AND component_reference_designator = X_designator
23 AND ((acd_type is null) OR (acd_type <> 3))
24 AND ((X_rowid is NULL) OR (rowid <> X_rowid))
25 );
26 EXCEPTION
27 WHEN no_data_found THEN
28 Fnd_Message.Set_Name('INV','INV_ALREADY_EXISTS');
29 Fnd_Message.Set_Token('ENTITY','Reference designator', TRUE);
30 App_Exception.Raise_Exception;
31 END Check_Unique;
32
33 --* Procedure added for Bug 4247194
34 PROCEDURE Check_Add ( X_Component_Sequence_Id NUMBER,
35 X_Old_Component_Sequence_Id NUMBER,
36 X_Designator VARCHAR2,
37 X_Change_Notice VARCHAR2 ) IS
38
39 rec_exist NUMBER :=0 ;
40 disable_exist NUMBER;
41 BEGIN
42 --* Checking whether reference designator record exists in implemented
43 --* or unimplemented status. If the reference designator is being added
44 --* for the first time, furthur validations will be ignored.
45 SELECT Count(1) INTO rec_exist
46 FROM Bom_Inventory_Components bic,
47 bom_reference_designators brd
48 WHERE Nvl(bic.Old_Component_Sequence_Id,bic.Component_Sequence_Id) = X_Old_Component_Sequence_Id
49 AND Nvl(bic.Change_Notice,'*') <> X_Change_Notice
50 AND brd.component_sequence_id = bic.component_sequence_id
51 AND brd.component_reference_designator = X_Designator
52 AND ((brd.acd_type is NULL) or (brd.acd_type <> 3));
53
54 IF rec_exist > 0 THEN
55 rec_exist :=0;
56 BEGIN
57 --* Checking whether a DISABLE record exists for the reference designator
58 --* in any unimplemented ECO
59 --* Old Comp Seq Id is passed in the subquery to fetch the highest
60 --* unimplemented record. If this acd type for this record is 3
61 --* then no furthur validation is done, since a disable record exists
62 --* for this ADD record.
63 --* If through a previous ECO, a DISABLE and ADD record have been entered
64 --* then the following query will return 2 records. In this case we need
65 --* to fetch only ADD record's acd type (1). So added rownum condition and
66 --* included order by clause.
67
68 SELECT Acd_Type INTO rec_exist
69 FROM Bom_Reference_Designators
70 WHERE Component_Sequence_Id = ( SELECT Max(bic.Component_Sequence_Id)
71 FROM Bom_Inventory_Components bic,
72 bom_reference_designators brd
73 WHERE bic.Old_Component_Sequence_Id = X_Old_Component_Sequence_Id
74 AND bic.Change_Notice <> X_Change_Notice
75 AND bic.Implementation_Date IS NULL
76 AND brd.component_sequence_id = bic.component_sequence_id
77 AND brd.component_reference_designator = X_Designator )
78 AND Component_Reference_Designator = X_Designator
79 AND Rownum < 2
80 ORDER BY Acd_Type;
81 EXCEPTION
82 WHEN NO_DATA_FOUND THEN
83 NULL;
84 END;
85
86 IF rec_exist IN (0,1) THEN
87 --* If no_data_found for previous query or acd type is 1, checking current block
88 --* DISABLE record exists in the current block
89 SELECT 0 INTO disable_exist
90 FROM dual
91 WHERE NOT EXISTS
92 (SELECT 1 FROM bom_reference_designators
93 WHERE component_sequence_id = X_Component_Sequence_Id
94 AND component_reference_designator = X_Designator
95 AND acd_type = 3);
96
97 --* If DISABLE record does not exist in current block then fire
98 --* error message.
99 IF disable_exist = 0 THEN
100 Fnd_Message.Set_Name('INV','INV_ALREADY_EXISTS');
101 Fnd_Message.Set_Token('ENTITY','Reference designator', TRUE);
102 App_Exception.Raise_Exception;
103 END IF;
104 END IF;
105 END IF;
106 EXCEPTION
107 WHEN NO_DATA_FOUND THEN
108 NULL;
109 END Check_Add;
110 -- End of Bug 4247194
111
112 PROCEDURE Default_Row(X_Total_Records IN OUT NOCOPY NUMBER,
113 X_Component_Sequence_ID NUMBER
114 ) IS
115 BEGIN
116 -- Get defaults
117 select count(*)
118 into X_Total_Records
119 from bom_ref_designators_view
120 where component_sequence_id = X_Component_Sequence_Id
121 and nvl(acd_type,1) <> 3;
122 EXCEPTION
123 when no_data_found then
124 null;
125 END Default_Row;
126
127 PROCEDURE Insert_Row(X_Rowid IN OUT NOCOPY VARCHAR2,
128 X_Component_Ref_Desig VARCHAR2,
129 X_Last_Update_Date DATE,
130 X_Last_Updated_By NUMBER,
131 X_Creation_Date DATE,
132 X_Created_By NUMBER,
133 X_Last_Update_Login NUMBER,
134 X_Ref_Designator_Comment VARCHAR2,
135 X_Change_Notice VARCHAR2,
136 X_Component_Sequence_Id NUMBER,
137 X_Acd_Type NUMBER,
138 X_Attribute_Category VARCHAR2,
139 X_Attribute1 VARCHAR2,
140 X_Attribute2 VARCHAR2,
141 X_Attribute3 VARCHAR2,
142 X_Attribute4 VARCHAR2,
143 X_Attribute5 VARCHAR2,
144 X_Attribute6 VARCHAR2,
145 X_Attribute7 VARCHAR2,
146 X_Attribute8 VARCHAR2,
147 X_Attribute9 VARCHAR2,
148 X_Attribute10 VARCHAR2,
149 X_Attribute11 VARCHAR2,
150 X_Attribute12 VARCHAR2,
151 X_Attribute13 VARCHAR2,
152 X_Attribute14 VARCHAR2,
153 X_Attribute15 VARCHAR2
154 ) IS
155 CURSOR C IS SELECT rowid FROM BOM_REFERENCE_DESIGNATORS
156 WHERE component_sequence_id = X_Component_Sequence_Id
157 AND ( (acd_type = X_Acd_Type)
158 or (acd_type is NULL and X_Acd_Type is NULL));
159 l_err_tbl Error_Handler.Mesg_Token_Tbl_Type;
160 l_return_status VARCHAR2(10);
161
162 BEGIN
163
164
165 INSERT INTO BOM_REFERENCE_DESIGNATORS(
166 component_reference_designator,
167 last_update_date,
168 last_updated_by,
169 creation_date,
170 created_by,
171 last_update_login,
172 ref_designator_comment,
173 change_notice,
174 component_sequence_id,
175 acd_type,
176 attribute_category,
177 attribute1,
178 attribute2,
179 attribute3,
180 attribute4,
181 attribute5,
182 attribute6,
183 attribute7,
184 attribute8,
185 attribute9,
186 attribute10,
187 attribute11,
188 attribute12,
189 attribute13,
190 attribute14,
191 attribute15
192 ) VALUES (
193 X_Component_Ref_Desig,
194 X_Last_Update_Date,
195 X_Last_Updated_By,
196 X_Creation_Date,
197 X_Created_By,
198 X_Last_Update_Login,
199 X_Ref_Designator_Comment,
200 X_Change_Notice,
201 X_Component_Sequence_Id,
202 X_Acd_Type,
203 X_Attribute_Category,
204 X_Attribute1,
205 X_Attribute2,
206 X_Attribute3,
207 X_Attribute4,
208 X_Attribute5,
209 X_Attribute6,
210 X_Attribute7,
211 X_Attribute8,
212 X_Attribute9,
213 X_Attribute10,
214 X_Attribute11,
215 X_Attribute12,
216 X_Attribute13,
217 X_Attribute14,
218 X_Attribute15
219 );
220
221 OPEN C;
222 FETCH C INTO X_Rowid;
223 if (C%NOTFOUND) then
224 CLOSE C;
225 Raise NO_DATA_FOUND;
226 end if;
227 CLOSE C;
228 BOMPCMBM.Insert_Related_Ref_Desg(p_component_sequence_id => X_Component_Sequence_Id
229 , p_ref_desg => X_Component_Ref_Desig
230 , x_Mesg_Token_Tbl => l_err_tbl
231 , x_Return_Status => l_return_status);
232 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
233 THEN
234 app_exception.raise_exception;
235 END IF;
236
237
238 -- Calling Raise_Business_Event to raise business event
239 Raise_Business_Event(X_Component_Sequence_Id,X_Last_Update_Date,X_Last_Updated_By,
240 X_Creation_Date,X_Created_By,X_Last_Update_Login);
241
242 END Insert_Row;
243
244
245 PROCEDURE Lock_Row(X_Rowid VARCHAR2,
246 X_Component_Ref_Desig VARCHAR2,
247 X_Ref_Designator_Comment VARCHAR2,
248 X_Change_Notice VARCHAR2,
249 X_Component_Sequence_Id NUMBER,
250 X_Acd_Type NUMBER,
251 X_Attribute_Category VARCHAR2,
252 X_Attribute1 VARCHAR2,
253 X_Attribute2 VARCHAR2,
254 X_Attribute3 VARCHAR2,
255 X_Attribute4 VARCHAR2,
256 X_Attribute5 VARCHAR2,
257 X_Attribute6 VARCHAR2,
258 X_Attribute7 VARCHAR2,
259 X_Attribute8 VARCHAR2,
260 X_Attribute9 VARCHAR2,
261 X_Attribute10 VARCHAR2,
262 X_Attribute11 VARCHAR2,
263 X_Attribute12 VARCHAR2,
264 X_Attribute13 VARCHAR2,
265 X_Attribute14 VARCHAR2,
266 X_Attribute15 VARCHAR2
267 ) IS
268 CURSOR C IS
269 SELECT *
270 FROM BOM_REFERENCE_DESIGNATORS
271 WHERE rowid = X_Rowid
272 FOR UPDATE of Component_Sequence_Id NOWAIT;
273 Recinfo C%ROWTYPE;
274
275
276 BEGIN
277 OPEN C;
278 FETCH C INTO Recinfo;
279 if (C%NOTFOUND) then
280 CLOSE C;
281 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
282 APP_EXCEPTION.Raise_Exception;
283 end if;
284 CLOSE C;
285 if (
286 (Recinfo.component_reference_designator=X_Component_Ref_Desig)
287 AND ( (Recinfo.ref_designator_comment = X_Ref_Designator_Comment)
288 OR ( (Recinfo.ref_designator_comment IS NULL)
289 AND (X_Ref_Designator_Comment IS NULL)))
290 AND ( (Recinfo.change_notice = X_Change_Notice)
291 OR ( (Recinfo.change_notice IS NULL)
292 AND (X_Change_Notice IS NULL)))
293 AND (Recinfo.component_sequence_id = X_Component_Sequence_Id)
294 AND ( (Recinfo.acd_type = X_Acd_Type)
295 OR ( (Recinfo.acd_type IS NULL)
296 AND (X_Acd_Type IS NULL)))
297 AND ( (Recinfo.attribute_category = X_Attribute_Category)
298 OR ( (Recinfo.attribute_category IS NULL)
299 AND (X_Attribute_Category IS NULL)))
300 AND ( (Recinfo.attribute1 = X_Attribute1)
301 OR ( (Recinfo.attribute1 IS NULL)
302 AND (X_Attribute1 IS NULL)))
303 AND ( (Recinfo.attribute2 = X_Attribute2)
304 OR ( (Recinfo.attribute2 IS NULL)
305 AND (X_Attribute2 IS NULL)))
306 AND ( (Recinfo.attribute3 = X_Attribute3)
307 OR ( (Recinfo.attribute3 IS NULL)
308 AND (X_Attribute3 IS NULL)))
309 AND ( (Recinfo.attribute4 = X_Attribute4)
310 OR ( (Recinfo.attribute4 IS NULL)
311 AND (X_Attribute4 IS NULL)))
312 AND ( (Recinfo.attribute5 = X_Attribute5)
313 OR ( (Recinfo.attribute5 IS NULL)
314 AND (X_Attribute5 IS NULL)))
315 AND ( (Recinfo.attribute6 = X_Attribute6)
316 OR ( (Recinfo.attribute6 IS NULL)
317 AND (X_Attribute6 IS NULL)))
318 AND ( (Recinfo.attribute7 = X_Attribute7)
319 OR ( (Recinfo.attribute7 IS NULL)
320 AND (X_Attribute7 IS NULL)))
321 AND ( (Recinfo.attribute8 = X_Attribute8)
322 OR ( (Recinfo.attribute8 IS NULL)
323 AND (X_Attribute8 IS NULL)))
324 AND ( (Recinfo.attribute9 = X_Attribute9)
325 OR ( (Recinfo.attribute9 IS NULL)
326 AND (X_Attribute9 IS NULL)))
327 AND ( (Recinfo.attribute10 = X_Attribute10)
328 OR ( (Recinfo.attribute10 IS NULL)
329 AND (X_Attribute10 IS NULL)))
330 AND ( (Recinfo.attribute11 = X_Attribute11)
331 OR ( (Recinfo.attribute11 IS NULL)
332 AND (X_Attribute11 IS NULL)))
333 AND ( (Recinfo.attribute12 = X_Attribute12)
334 OR ( (Recinfo.attribute12 IS NULL)
335 AND (X_Attribute12 IS NULL)))
336 AND ( (Recinfo.attribute13 = X_Attribute13)
337 OR ( (Recinfo.attribute13 IS NULL)
338 AND (X_Attribute13 IS NULL)))
339 AND ( (Recinfo.attribute14 = X_Attribute14)
340 OR ( (Recinfo.attribute14 IS NULL)
341 AND (X_Attribute14 IS NULL)))
342 AND ( (Recinfo.attribute15 = X_Attribute15)
343 OR ( (Recinfo.attribute15 IS NULL)
344 AND (X_Attribute15 IS NULL)))
345 ) then
346 return;
347 else
348 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
349 APP_EXCEPTION.Raise_Exception;
350 end if;
351 END Lock_Row;
352
353
354
355 PROCEDURE Update_Row(X_Rowid VARCHAR2,
356 X_Component_Ref_Desig VARCHAR2,
357 X_Last_Update_Date DATE,
358 X_Last_Updated_By NUMBER,
359 X_Last_Update_Login NUMBER,
363 X_Acd_Type NUMBER,
360 X_Ref_Designator_Comment VARCHAR2,
361 X_Change_Notice VARCHAR2,
362 X_Component_Sequence_Id NUMBER,
364 X_Attribute_Category VARCHAR2,
365 X_Attribute1 VARCHAR2,
366 X_Attribute2 VARCHAR2,
367 X_Attribute3 VARCHAR2,
368 X_Attribute4 VARCHAR2,
369 X_Attribute5 VARCHAR2,
370 X_Attribute6 VARCHAR2,
371 X_Attribute7 VARCHAR2,
372 X_Attribute8 VARCHAR2,
373 X_Attribute9 VARCHAR2,
374 X_Attribute10 VARCHAR2,
375 X_Attribute11 VARCHAR2,
376 X_Attribute12 VARCHAR2,
377 X_Attribute13 VARCHAR2,
378 X_Attribute14 VARCHAR2,
379 X_Attribute15 VARCHAR2
380 ) IS
381 l_err_tbl Error_Handler.Mesg_Token_Tbl_Type;
382 l_return_status VARCHAR2(10);
383 l_old_ref_desg VARCHAR2(15);
384 l_acd_type NUMBER;
385
386 BEGIN
387
388 SELECT COMPONENT_REFERENCE_DESIGNATOR, ACD_TYPE
389 INTO l_old_ref_desg, l_acd_type
390 FROM BOM_REFERENCE_DESIGNATORS
391 WHERE rowid = X_Rowid;
392
393 UPDATE BOM_REFERENCE_DESIGNATORS
394 SET
395 component_reference_designator = X_Component_Ref_Desig,
396 last_update_date = X_Last_Update_Date,
397 last_updated_by = X_Last_Updated_By,
398 last_update_login = X_Last_Update_Login,
399 ref_designator_comment = X_Ref_Designator_Comment,
400 change_notice = X_Change_Notice,
401 component_sequence_id = X_Component_Sequence_Id,
402 acd_type = X_Acd_Type,
403 attribute_category = X_Attribute_Category,
404 attribute1 = X_Attribute1,
405 attribute2 = X_Attribute2,
406 attribute3 = X_Attribute3,
407 attribute4 = X_Attribute4,
408 attribute5 = X_Attribute5,
409 attribute6 = X_Attribute6,
410 attribute7 = X_Attribute7,
411 attribute8 = X_Attribute8,
412 attribute9 = X_Attribute9,
413 attribute10 = X_Attribute10,
414 attribute11 = X_Attribute11,
415 attribute12 = X_Attribute12,
416 attribute13 = X_Attribute13,
417 attribute14 = X_Attribute14,
418 attribute15 = X_Attribute15
419 WHERE rowid = X_Rowid;
420
421 if (SQL%NOTFOUND) then
422 Raise NO_DATA_FOUND;
423 end if;
424 BOMPCMBM.Update_Related_Ref_Desg(p_component_sequence_id => X_Component_Sequence_Id
425 , p_old_ref_desg => l_old_ref_desg
426 , p_new_ref_desg => X_Component_Ref_Desig
427 , p_acd_type => l_acd_type
428 , x_Mesg_Token_Tbl => l_err_tbl
429 , x_Return_Status => l_return_status);
430 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
431 THEN
432 app_exception.raise_exception;
433 END IF;
434
435
436 -- Calling Raise_Business_Event to raise business event
437 Raise_Business_Event(X_Component_Sequence_Id,X_Last_Update_Date,X_Last_Updated_By,
438 NULL,NULL,X_Last_Update_Login);
439
440 END Update_Row;
441
442 PROCEDURE Delete_Row(X_Rowid VARCHAR2) IS
443 l_common_component_sequence_id NUMBER;
444 l_ref_desg VARCHAR2(255);
445 l_return_status VARCHAR2(1);
446 l_Component_Sequence_Id NUMBER;
447 BEGIN
448
449 Select component_sequence_id, component_reference_designator
450 into l_common_component_sequence_id, l_ref_desg
451 From BOM_REFERENCE_DESIGNATORS
452 WHERE rowid = X_Rowid;
453
454
455 DELETE FROM BOM_REFERENCE_DESIGNATORS
456 WHERE rowid = X_Rowid;
457
458 if (SQL%NOTFOUND) then
459 Raise NO_DATA_FOUND;
460 end if;
461 BOMPCMBM.Delete_Related_Ref_Desg(p_src_comp_seq => l_common_component_sequence_id
462 , p_ref_desg => l_ref_desg
463 , x_return_status => l_return_status);
464 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
465 THEN
466 app_exception.raise_exception;
467 END IF;
468
469 -- Calling Raise_Business_Event to raise business event
470 Raise_Business_Event(l_common_component_sequence_Id,sysdate,fnd_global.user_id
471 ,NULL,NULL,fnd_global.user_id);
472
473 EXCEPTION
474 WHEN NO_DATA_FOUND THEN
475 Raise NO_DATA_FOUND;
476
477
478 END Delete_Row;
479
480 PROCEDURE Raise_Business_Event( p_Component_Sequence_Id NUMBER,
481 p_last_update_date DATE,
482 p_last_updated_by NUMBER,
483 p_creation_date DATE,
484 p_created_by NUMBER,
485 p_last_update_login NUMBER
486 ) IS --4306013
487 l_Component_Item_Name VARCHAR2(512);
488 l_Component_Item_Id NUMBER;
489 l_Bill_Sequence_Id NUMBER;
490 l_Organization_Id NUMBER;
491 l_Component_Remarks VARCHAR2(240);
492
493 BEGIN
494
495 SELECT bic.Bill_Sequence_Id, bbm.Organization_Id, bic.Component_Item_Id,
496 bic.Component_Remarks, msi.Concatenated_Segments
497 INTO l_Bill_Sequence_Id, l_Organization_Id, l_Component_Item_Id,
498 l_Component_Remarks, l_Component_Item_Name
499 FROM Bom_Bill_Of_Materials bbm, Bom_Inventory_Components bic, Mtl_System_Items_Kfv msi
500 WHERE bbm.Bill_Sequence_Id = bic.Bill_Sequence_Id
501 And msi.Inventory_Item_Id = bic.Component_Item_Id
502 And msi.Organization_Id = bbm.Organization_Id
503 And bic.Component_Sequence_Id = p_Component_Sequence_Id;
504
505 -- Raising Business event
506 Bom_Business_Event_PKG.Raise_Component_Event
507 (p_bill_sequence_Id => l_Bill_Sequence_Id
508 , p_pk1_value => l_Component_Item_Id
509 , p_pk2_value => l_Organization_Id
510 , p_obj_name => NULL
511 , p_organization_id => l_Organization_Id
512 , p_comp_item_name => l_Component_Item_Name
513 , p_comp_description => l_Component_Remarks
514 , p_Event_Load_Type => 'Single'
515 , p_Event_Entity_Name => 'Reference Designator'
516 , p_Event_Entity_Parent_Id => p_Component_Sequence_Id
517 , p_Event_Name => Bom_Business_Event_PKG.G_COMPONENT_MODIFIED_EVENT
518 , p_last_update_date => p_last_update_date
519 , p_last_updated_by => p_last_updated_by
520 , p_creation_date => p_creation_date
521 , p_created_by => p_created_by
522 , p_last_update_login => p_last_update_login
523 );
524
525 -- IF (SQL%NOTFOUND) THEN
526 -- Raise NO_DATA_FOUND;
527 -- END IF;
528 END;
529
530 END BOM_REF_DESIG_PKG;