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