[Home] [Help]
PACKAGE BODY: APPS.BOM_REF_DESIGNATOR_UTIL
Source
1 PACKAGE BODY BOM_Ref_Designator_Util AS
2 /* $Header: BOMURFDB.pls 120.4.12010000.3 2010/02/13 01:39:47 umajumde ship $ */
3 /****************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMURFDB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_Ref_Designator_Util
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 19-JUL-1999 Rahul Chitko Initial Creation
21 --
22 -- 06-May-05 Abhishek Rudresh Common BOM Attrs update
23 ****************************************************************************/
24 G_PKG_NAME CONSTANT VARCHAR2(30) := 'BOM_Ref_Designator_Util';
25 G_CONTROL_REC BOM_BO_PUB.Control_Rec_Type;
26
27 -- Procedure Clear_Dependent_Attr
28
29 PROCEDURE Clear_Dependent_Attr
30 ( p_attr_id IN NUMBER := FND_API.G_MISS_NUM
31 , p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
32 , p_old_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type :=
33 Bom_Bo_Pub.G_MISS_REF_DESIGNATOR_REC
34 , x_ref_designator_rec IN OUT NOCOPY Bom_Bo_Pub.Ref_Designator_Rec_Type
35 )
36 IS
37 BEGIN
38 -- Load out record
39
40 x_ref_designator_rec := p_ref_designator_rec;
41
42 END Clear_Dependent_Attr;
43
44 -- Procedure Apply_Attribute_Changes
45
46 PROCEDURE Apply_Attribute_Changes
47 ( p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
48 , p_old_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type :=
49 Bom_Bo_Pub.G_MISS_REF_DESIGNATOR_REC
50 , x_ref_designator_rec IN OUT NOCOPY Bom_Bo_Pub.Ref_Designator_Rec_Type
51 )
52 IS
53 BEGIN
54 -- Load out record
55
56 x_ref_designator_rec := p_ref_designator_rec;
57
58 END Apply_Attribute_Changes;
59
60 -- Function Convert_Miss_To_Null
61
62 FUNCTION Convert_Miss_To_Null
63 ( p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
64 ) RETURN Bom_Bo_Pub.Ref_Designator_Rec_Type
65 IS
66 l_ref_designator_rec Bom_Bo_Pub.Ref_Designator_Rec_Type :=
67 p_ref_designator_rec;
68 BEGIN
69
70 IF l_ref_designator_rec.Reference_Designator_Name = FND_API.G_MISS_CHAR THEN
71 l_ref_designator_rec.Reference_Designator_Name := NULL;
72 END IF;
73
74 IF l_ref_designator_rec.attribute_category = FND_API.G_MISS_CHAR THEN
75 l_ref_designator_rec.attribute_category := NULL;
76 END IF;
77
78 IF l_ref_designator_rec.attribute1 = FND_API.G_MISS_CHAR THEN
79 l_ref_designator_rec.attribute1 := NULL;
80 END IF;
81
82 IF l_ref_designator_rec.attribute2 = FND_API.G_MISS_CHAR THEN
83 l_ref_designator_rec.attribute2 := NULL;
84 END IF;
85
86 IF l_ref_designator_rec.attribute3 = FND_API.G_MISS_CHAR THEN
87 l_ref_designator_rec.attribute3 := NULL;
88 END IF;
89
90 IF l_ref_designator_rec.attribute4 = FND_API.G_MISS_CHAR THEN
91 l_ref_designator_rec.attribute4 := NULL;
92 END IF;
93
94 IF l_ref_designator_rec.attribute5 = FND_API.G_MISS_CHAR THEN
95 l_ref_designator_rec.attribute5 := NULL;
96 END IF;
97
98 IF l_ref_designator_rec.attribute6 = FND_API.G_MISS_CHAR THEN
99 l_ref_designator_rec.attribute6 := NULL;
100 END IF;
101
102 IF l_ref_designator_rec.attribute7 = FND_API.G_MISS_CHAR THEN
103 l_ref_designator_rec.attribute7 := NULL;
104 END IF;
105
106 IF l_ref_designator_rec.attribute8 = FND_API.G_MISS_CHAR THEN
107 l_ref_designator_rec.attribute8 := NULL;
108 END IF;
109
110 IF l_ref_designator_rec.attribute9 = FND_API.G_MISS_CHAR THEN
111 l_ref_designator_rec.attribute9 := NULL;
112 END IF;
113
114 IF l_ref_designator_rec.attribute10 = FND_API.G_MISS_CHAR THEN
115 l_ref_designator_rec.attribute10 := NULL;
116 END IF;
117
118 IF l_ref_designator_rec.attribute11 = FND_API.G_MISS_CHAR THEN
119 l_ref_designator_rec.attribute11 := NULL;
120 END IF;
121
122 IF l_ref_designator_rec.attribute12 = FND_API.G_MISS_CHAR THEN
123 l_ref_designator_rec.attribute12 := NULL;
124 END IF;
125
126 IF l_ref_designator_rec.attribute13 = FND_API.G_MISS_CHAR THEN
127 l_ref_designator_rec.attribute13 := NULL;
128 END IF;
129
130 IF l_ref_designator_rec.attribute14 = FND_API.G_MISS_CHAR THEN
131 l_ref_designator_rec.attribute14 := NULL;
132 END IF;
133
134 IF l_ref_designator_rec.attribute15 = FND_API.G_MISS_CHAR THEN
135 l_ref_designator_rec.attribute15 := NULL;
136 END IF;
137
138 RETURN l_ref_designator_rec;
139
140 END Convert_Miss_To_Null;
141
142 /********************************************************************
143 *
144 * Procedure : Update_Row
145 * Parameters IN : Reference Designator Record as given by the User
146 * Reference Designator Unexposed Record
147 * Parameters OUT: Return_Status - Indicating success or faliure
148 * Mesg_Token_Tbl - Filled with any errors or warnings
149 * Purpose : Will update the Reference Designator record and
150 * if unable to update then return with a status
151 * and Error Message table filled with the message
152 *
153 ********************************************************************/
154
155 PROCEDURE Update_Row
156 ( p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
157 , p_Ref_Desg_Unexp_Rec IN Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
158 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
159 , x_Return_Status IN OUT NOCOPY VARCHAR2
160 )
161 IS
162 l_return_status varchar2(80);
163 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
164 l_ref_designator_rec Bom_Bo_Pub.Ref_Designator_Rec_Type;
165 l_Token_Tbl Error_Handler.Token_Tbl_Type;
166 l_BO_Id VARCHAR2(3) := Bom_Globals.Get_Bo_Identifier;
167 BEGIN
168 --bug:3254815 Update request id, prog id, prog appl id and prog update date.
169 UPDATE BOM_REFERENCE_DESIGNATORS
170 SET COMPONENT_REFERENCE_DESIGNATOR =
171 DECODE(p_ref_designator_rec.new_reference_designator,
172 NULL,p_ref_designator_rec.Reference_Designator_Name,
173 FND_API.G_MISS_CHAR,
174 p_ref_designator_rec.Reference_Designator_Name,
175 p_ref_designator_rec.new_reference_designator
176 )
177 , LAST_UPDATE_DATE = SYSDATE
178 , LAST_UPDATED_BY = Bom_Globals.Get_User_Id
179 , LAST_UPDATE_LOGIN = Bom_Globals.Get_User_Id
180 , REF_DESIGNATOR_COMMENT =
181 DECODE( p_ref_designator_rec.ref_designator_comment
182 , FND_API.G_MISS_CHAR
183 , NULL
184 , p_ref_designator_rec.ref_designator_comment
185 )
186 , ATTRIBUTE_CATEGORY = p_ref_designator_rec.attribute_category
187 , ATTRIBUTE1 = p_ref_designator_rec.attribute1
188 , ATTRIBUTE2 = p_ref_designator_rec.attribute2
189 , ATTRIBUTE3 = p_ref_designator_rec.attribute3
190 , ATTRIBUTE4 = p_ref_designator_rec.attribute4
191 , ATTRIBUTE5 = p_ref_designator_rec.attribute5
192 , ATTRIBUTE6 = p_ref_designator_rec.attribute6
193 , ATTRIBUTE7 = p_ref_designator_rec.attribute7
194 , ATTRIBUTE8 = p_ref_designator_rec.attribute8
195 , ATTRIBUTE9 = p_ref_designator_rec.attribute9
196 , ATTRIBUTE10 = p_ref_designator_rec.attribute10
197 , ATTRIBUTE11 = p_ref_designator_rec.attribute11
198 , ATTRIBUTE12 = p_ref_designator_rec.attribute12
199 , ATTRIBUTE13 = p_ref_designator_rec.attribute13
200 , ATTRIBUTE14 = p_ref_designator_rec.attribute14
201 , ATTRIBUTE15 = p_ref_designator_rec.attribute15
202 , Original_System_Reference =
203 p_ref_designator_rec.Original_System_Reference
204 , REQUEST_ID = Fnd_Global.Conc_Request_Id
205 , PROGRAM_ID = Fnd_Global.Conc_Program_Id
206 , PROGRAM_APPLICATION_ID = Fnd_Global.Prog_Appl_Id
207 , PROGRAM_UPDATE_DATE = SYSDATE
208 WHERE COMPONENT_REFERENCE_DESIGNATOR =
209 p_ref_designator_rec.Reference_Designator_Name
210 AND COMPONENT_SEQUENCE_ID =
211 p_Ref_Desg_Unexp_Rec.component_sequence_id
212 AND ( ( l_BO_Id = Bom_Globals.G_ECO_BO AND
213 ACD_TYPE = p_Ref_Designator_Rec.acd_type
214 ) OR
215 ( l_BO_Id = Bom_Globals.G_BOM_BO AND
216 (acd_type IS NULL or acd_type = 1)
217 /* Bug 5726557; The code is modified to modify the refernce designators
218 when implemneted through an ECO */
219 )
220 );
221
222 BOMPCMBM.Update_Related_Ref_Desg(p_component_sequence_id => p_Ref_Desg_Unexp_Rec.component_sequence_id
223 , p_old_ref_desg => p_ref_designator_rec.Reference_Designator_Name
224 , p_new_ref_desg => nvl(p_ref_designator_rec.new_reference_designator,
225 p_ref_designator_rec.Reference_Designator_Name)
226 , p_acd_type => p_Ref_Designator_Rec.acd_type
227 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
228 , x_Return_Status => x_Return_Status);
229 --x_Return_Status := FND_API.G_RET_STS_SUCCESS;
230
231 EXCEPTION
232 WHEN OTHERS THEN
233
234 IF G_CONTROL_REC.caller_type = 'FORM'
235 THEN
236 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
237 RAISE;
238 END IF;
239
240 Error_Handler.Add_Error_Token
241 ( p_Message_Name => NULL
242 , p_Message_Text => 'ERROR in Update Row (Ref Desgs)' ||
243 substr(SQLERRM, 1, 100) || ' ' ||
244 to_char(SQLCODE)
245 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
246 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl);
247
248 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
249
250 END Update_Row;
251
252 --following function has been added for bug 7713832
253 FUNCTION Common_CompSeqIdRD( p_comp_seq_id NUMBER)
254 RETURN NUMBER
255 IS
256 l_src_comp_seq_id NUMBER;
257
258 BEGIN
259
260 SELECT common_component_sequence_id
261 INTO l_src_comp_seq_id
262 FROM bom_components_b
263 WHERE component_sequence_id = p_comp_seq_id
264 and component_sequence_id <> common_component_sequence_id;
265
266 RETURN l_src_comp_seq_id;
267
268 EXCEPTION
269 WHEN NO_DATA_FOUND THEN
270 RETURN NULL;
271
272 WHEN OTHERS THEN
273 RETURN NULL;
274 END;
275
276
277 /********************************************************************
278 *
279 * Procedure : Insert_Row
280 * Parameters IN : Reference Designator Record as given by the User
281 * Reference Designator Unexposed Record
282 * Parameters OUT: Return_Status - Indicating success or faliure
283 * Mesg_Token_Tbl - Filled with any errors or warnings
284 * Purpose : Insert Reference Designator record and if unable to
285 * to insert then return with an error_status and
286 * Error message filled in the Message Token Table.
287 *
288 ********************************************************************/
289
290 PROCEDURE Insert_Row
291 ( p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
292 , p_Ref_Desg_Unexp_Rec IN Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
293 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
294 , x_Return_Status IN OUT NOCOPY VARCHAR2
295 )
296 IS
297 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
298 l_src_comp_seq_id NUMBER := NULL; --bug 7713832
299 BEGIN
300
301 --Bug 7712832 changes start
302 IF Bom_Globals.Get_Caller_Type = 'MIGRATION' THEN
303 l_src_comp_seq_id := Common_CompSeqIdRD( p_comp_seq_id => p_Ref_Desg_Unexp_Rec.component_sequence_id
304 );
305 END IF;
306 --Bug 7712832 changes end
307 INSERT INTO BOM_REFERENCE_DESIGNATORS
308 ( COMPONENT_REFERENCE_DESIGNATOR
309 , LAST_UPDATE_DATE
310 , LAST_UPDATED_BY
311 , CREATION_DATE
312 , CREATED_BY
313 , LAST_UPDATE_LOGIN
314 , REF_DESIGNATOR_COMMENT
318 , REQUEST_ID
315 , CHANGE_NOTICE
316 , COMPONENT_SEQUENCE_ID
317 , ACD_TYPE
319 , PROGRAM_APPLICATION_ID
320 , PROGRAM_ID
321 , PROGRAM_UPDATE_DATE
322 , ATTRIBUTE_CATEGORY
323 , ATTRIBUTE1
324 , ATTRIBUTE2
325 , ATTRIBUTE3
326 , ATTRIBUTE4
327 , ATTRIBUTE5
328 , ATTRIBUTE6
329 , ATTRIBUTE7
330 , ATTRIBUTE8
331 , ATTRIBUTE9
332 , ATTRIBUTE10
333 , ATTRIBUTE11
334 , ATTRIBUTE12
335 , ATTRIBUTE13
336 , ATTRIBUTE14
337 , ATTRIBUTE15
338 , Original_System_Reference
339 , Common_Component_Sequence_Id --added for bug 7713832
340 )
341 VALUES
342 ( p_ref_designator_rec.Reference_Designator_Name
343 , SYSDATE
344 , Bom_Globals.Get_User_Id
345 , SYSDATE
346 , Bom_Globals.Get_User_Id
347 , Bom_Globals.Get_User_Id
348 , DECODE( p_ref_designator_rec.ref_designator_comment
349 , FND_API.G_MISS_CHAR
350 , NULL
351 , p_ref_designator_rec.ref_designator_comment )
352 , p_ref_designator_rec.Eco_Name
353 , p_Ref_Desg_Unexp_Rec.component_sequence_id
354 , p_ref_designator_rec.acd_type
355 , Fnd_Global.Conc_Request_Id /* Request Id */
356 , Bom_Globals.Get_Prog_AppId
357 , Bom_Globals.Get_Prog_Id
358 , SYSDATE
359 , p_ref_designator_rec.attribute_category
360 , p_ref_designator_rec.attribute1
361 , p_ref_designator_rec.attribute2
362 , p_ref_designator_rec.attribute3
363 , p_ref_designator_rec.attribute4
364 , p_ref_designator_rec.attribute5
365 , p_ref_designator_rec.attribute6
366 , p_ref_designator_rec.attribute7
367 , p_ref_designator_rec.attribute8
368 , p_ref_designator_rec.attribute9
369 , p_ref_designator_rec.attribute10
370 , p_ref_designator_rec.attribute11
371 , p_ref_designator_rec.attribute12
372 , p_ref_designator_rec.attribute13
373 , p_ref_designator_rec.attribute14
374 , p_ref_designator_rec.attribute15
375 , p_ref_designator_rec.Original_System_Reference
376 , l_src_comp_seq_id --bug 7713832
377 );
378 IF Bom_Globals.Get_Caller_Type <> 'MIGRATION' THEN --Bug 7713832
379 BOMPCMBM.Insert_Related_Ref_Desg(p_component_sequence_id => p_Ref_Desg_Unexp_Rec.component_sequence_id
380 , p_ref_desg => p_ref_designator_rec.Reference_Designator_Name
381 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
382 , x_Return_Status => x_return_status);
383 END IF;
384 --x_return_status := FND_API.G_RET_STS_SUCCESS;
385 EXCEPTION
386
387 WHEN OTHERS THEN
388 IF G_CONTROL_REC.caller_type = 'FORM'
389 THEN
390 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
391 RAISE;
392 END IF;
393
394 Error_Handler.Add_Error_Token
395 ( p_Message_Name => NULL
396 , p_Message_Text => 'ERROR in Insert Row (Ref Desgs)' ||
397 substr(SQLERRM, 1, 100) || ' ' ||
398 to_char(SQLCODE)
399 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
400 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
401 );
402
403 IF Bom_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('ERROR in Insert Row (Ref Desgs)' || substr(SQLERRM, 1, 100) || ' ' || to_char(SQLCODE)); END IF;
404
405 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
406
407 END Insert_Row;
408
409 /********************************************************************
410 *
411 * Procedure : Delete_Row
412 * Parameters IN : Reference Designator Key
413 * Parameters OUT: Return_Status - Indicating success or faliure
414 * Mesg_Token_Tbl - Filled with any errors or warnings
415 * Purpose : Insert Reference Designator record and if unable to
416 * to insert then return with an error_status and
417 * Error message filled in the Message Token Table.
418 *
419 ********************************************************************/
420
421 PROCEDURE Delete_Row
422 ( p_ref_designator IN VARCHAR2
423 , p_component_sequence_id IN NUMBER
424 , p_acd_type IN NUMBER
425 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
426 , x_Return_Status IN OUT NOCOPY VARCHAR2
427 )
428 IS
429 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
430 l_BO_Id VARCHAR2(3) := Bom_Globals.Get_Bo_Identifier;
431
432 BEGIN
433
434 DELETE FROM BOM_REFERENCE_DESIGNATORS
435 WHERE COMPONENT_REFERENCE_DESIGNATOR = p_ref_designator
436 AND COMPONENT_SEQUENCE_ID = p_component_sequence_id
437 AND ( ( l_BO_Id = Bom_Globals.G_ECO_BO AND
438 ACD_TYPE = p_acd_type
439 ) OR
440 ( l_BO_Id = Bom_Globals.G_BOM_BO AND
441 acd_type IS NULL
442 )
443 );
444 BOMPCMBM.Delete_Related_Ref_Desg(p_src_comp_seq => p_component_sequence_id
445 , p_ref_desg => p_ref_designator
446 , x_return_status => x_return_status);
447
448 --x_Return_Status := FND_API.G_RET_STS_SUCCESS;
449
450 EXCEPTION
451 WHEN OTHERS THEN
452
453 IF G_CONTROL_REC.caller_type = 'FORM'
454 THEN
458
455 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
456 RAISE;
457 END IF;
459 Error_Handler.Add_Error_Token
460 ( p_Message_Name => NULL
461 , p_Message_Text => 'ERROR in Delete Row (Ref Desgs)' ||
462 substr(SQLERRM, 1, 100) || ' ' ||
463 to_char(SQLCODE)
464 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
465 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
466 );
467 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
468
469 END Delete_Row;
470
471 /********************************************************************
472 *
473 * Procedure : Query_Row
474 * Parameters IN : Reference Designator Key
475 * Parameters OUT: Reference Designator Record of exposed columns
476 * Reference Designator Record of Unexposed Columns
477 * Return_Status - Indicating success or faliure
478 * Mesg_Token_Tbl - Filled with any errors or warnings
479 * Purpose : Query Row procedure will query a database record
480 * seperate the values into the exposed and the un-
481 * exposed record and return the values.
482 * If the query fails then the Error Token table will
483 * be filled in with the error message and returned with
484 * and error status otherwise a success
485 ********************************************************************/
486
487 PROCEDURE Query_Row
488 ( p_ref_designator IN VARCHAR2
489 , p_component_sequence_id IN NUMBER
490 , p_acd_type IN NUMBER
491 , x_Ref_Designator_Rec IN OUT NOCOPY Bom_Bo_Pub.Ref_Designator_Rec_Type
492 , x_Ref_Desg_Unexp_Rec IN OUT NOCOPY Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
493 , x_Return_Status IN OUT NOCOPY VARCHAR2
494 )
495 IS
496 l_ref_designator_rec Bom_Bo_Pub.Ref_Designator_Rec_Type;
497 l_Ref_Desg_Unexp_Rec Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type;
498 l_err_text VARCHAR2(2000);
499 BEGIN
500
501 SELECT COMPONENT_REFERENCE_DESIGNATOR
502 , REF_DESIGNATOR_COMMENT
503 , CHANGE_NOTICE
504 , COMPONENT_SEQUENCE_ID
505 , ACD_TYPE
506 , ATTRIBUTE_CATEGORY
507 , ATTRIBUTE1
508 , ATTRIBUTE2
509 , ATTRIBUTE3
510 , ATTRIBUTE4
511 , ATTRIBUTE5
512 , ATTRIBUTE6
513 , ATTRIBUTE7
514 , ATTRIBUTE8
515 , ATTRIBUTE9
516 , ATTRIBUTE10
517 , ATTRIBUTE11
518 , ATTRIBUTE12
519 , ATTRIBUTE13
520 , ATTRIBUTE14
521 , ATTRIBUTE15
522 INTO l_ref_designator_rec.Reference_Designator_Name
523 , l_ref_designator_rec.ref_designator_comment
524 , l_ref_designator_rec.Eco_Name
525 , l_Ref_Desg_Unexp_Rec.component_sequence_id
526 , l_ref_designator_rec.acd_type
527 , l_ref_designator_rec.attribute_category
528 , l_ref_designator_rec.attribute1
529 , l_ref_designator_rec.attribute2
530 , l_ref_designator_rec.attribute3
531 , l_ref_designator_rec.attribute4
532 , l_ref_designator_rec.attribute5
533 , l_ref_designator_rec.attribute6
534 , l_ref_designator_rec.attribute7
535 , l_ref_designator_rec.attribute8
536 , l_ref_designator_rec.attribute9
537 , l_ref_designator_rec.attribute10
538 , l_ref_designator_rec.attribute11
539 , l_ref_designator_rec.attribute12
540 , l_ref_designator_rec.attribute13
541 , l_ref_designator_rec.attribute14
542 , l_ref_designator_rec.attribute15
543 FROM BOM_REFERENCE_DESIGNATORS
544 WHERE COMPONENT_REFERENCE_DESIGNATOR = p_ref_designator
545 AND COMPONENT_SEQUENCE_ID = p_component_sequence_id
546 AND NVL(DECODE(ACD_TYPE, FND_API.G_MISS_NUM, null, acd_type), 1) =
547 NVL(DECODE(p_acd_type, FND_API.G_MISS_NUM, null, p_acd_type), 1)
548 /* Bug 5726557; The code is modified to modify the refernce designators
549 when implemneted through an ECO */
550 ;
551
552 x_Ref_Designator_Rec := l_ref_designator_rec;
553 x_Ref_Desg_Unexp_Rec := l_Ref_Desg_Unexp_Rec;
554 x_Return_Status := Bom_Globals.G_RECORD_FOUND;
555
556 EXCEPTION
557
558 WHEN NO_DATA_FOUND THEN
559 x_Return_Status := Bom_Globals.G_RECORD_NOT_FOUND;
560
561 WHEN OTHERS THEN
562 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
563
564 END Query_Row;
565
566 PROCEDURE Perform_Writes
567 ( p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_rec_Type
568 , p_ref_desg_unexp_rec IN Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
569 , p_control_rec IN BOM_BO_PUB.Control_Rec_Type
570 := BOM_BO_PUB.G_DEFAULT_CONTROL_REC
571 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
572 , x_return_status IN OUT NOCOPY VARCHAR2
573 )
574 IS
575 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
576 l_return_status VARCHAR2(1);
577 BEGIN
578 l_return_status := FND_API.G_RET_STS_SUCCESS;
579
580 IF p_ref_designator_rec.transaction_type = Bom_Globals.G_OPR_CREATE
581 THEN
582 Insert_Row( p_ref_designator_rec =>
583 p_ref_designator_rec
584 , p_ref_desg_unexp_rec =>
585 p_ref_desg_unexp_rec
586 , x_mesg_token_tbl => l_mesg_token_tbl
587 , x_return_status => l_return_status
588 );
589 ELSIF p_ref_designator_rec.transaction_type = Bom_Globals.G_OPR_UPDATE
590 THEN
591 Update_Row( p_ref_designator_rec =>
592 p_ref_designator_rec
593 , p_ref_desg_unexp_rec =>
594 p_ref_desg_unexp_rec
595 , x_mesg_token_tbl =>
596 l_mesg_token_tbl
600 ELSIF p_ref_designator_rec.transaction_type = Bom_Globals.G_OPR_DELETE
597 , x_return_status =>
598 l_return_status
599 );
601 THEN
602 Delete_Row
603 ( p_ref_designator =>
604 p_ref_designator_rec.reference_designator_name
605 , p_component_sequence_id =>
606 p_ref_desg_unexp_rec.component_sequence_id
607 , p_acd_type =>
608 p_ref_designator_rec.acd_type
609 , x_Mesg_Token_Tbl => l_mesg_token_tbl
610 , x_Return_Status => l_return_status
611 );
612 END IF;
613
614 x_return_status := l_return_status;
615 x_mesg_token_tbl := l_mesg_token_tbl;
616
617 END Perform_Writes;
618
619 /*
620 ** Procedure definitions for BOM Business Object
621 */
622 FUNCTION Convert_Miss_To_Null
623 ( p_bom_ref_designator_rec IN Bom_Bo_Pub.Bom_Ref_Designator_Rec_Type
624 ) RETURN Bom_Bo_Pub.Bom_Ref_Designator_Rec_Type
625 IS
626 l_ref_designator_rec Bom_Bo_Pub.Ref_Designator_Rec_Type;
627 l_ref_desg_unexp_rec Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type;
628 l_bom_ref_designator_rec Bom_Bo_Pub.Bom_Ref_Designator_Rec_Type;
629 l_bom_ref_desg_unexp_rec Bom_Bo_Pub.Bom_Ref_Desg_Unexp_Rec_Type;
630 BEGIN
631 --
632 -- Convert the BOM Record to ECO
633 --
634 Bom_Bo_Pub.Convert_BomDesg_To_EcoDesg
635 ( p_bom_ref_designator_rec => p_bom_ref_designator_rec
636 , x_ref_designator_rec => l_ref_designator_rec
637 , x_ref_desg_unexp_rec => l_ref_desg_unexp_rec
638 );
639
640 -- Call Conver Missing to Null
641
642 l_ref_designator_rec :=
643 Convert_Miss_To_Null
644 ( p_ref_designator_rec => l_ref_designator_rec);
645
646 --
647 -- Convert ECO record back to BOM
648 --
649 Bom_Bo_Pub.Convert_EcoDesg_To_BomDesg
650 ( p_ref_designator_rec => l_ref_designator_rec
651 , x_bom_ref_designator_rec => l_bom_ref_designator_rec
652 , x_bom_ref_desg_unexp_rec => l_bom_ref_desg_unexp_rec
653 );
654
655 RETURN l_bom_ref_designator_rec;
656
657 END Convert_Miss_To_Null;
658
659 -- Function Query_Row
660
661 PROCEDURE Query_Row
662 ( p_bom_ref_designator IN VARCHAR2
663 , p_component_sequence_id IN NUMBER
664 , p_acd_type IN NUMBER
665 , x_bom_Ref_Designator_Rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Ref_Designator_Rec_Type
666 , x_bom_Ref_Desg_Unexp_Rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Ref_Desg_Unexp_Rec_Type
667 , x_Return_Status IN OUT NOCOPY VARCHAR2
668 )
669 IS
670 l_ref_designator_rec Bom_Bo_Pub.Ref_Designator_Rec_Type;
671 l_ref_desg_unexp_rec Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type;
672 BEGIN
673
674 Bom_Ref_Designator_Util.Query_Row
675 ( p_ref_designator => p_bom_ref_designator
676 , p_component_sequence_id => p_component_sequence_id
677 , p_acd_type => p_acd_type
678 , x_ref_designator_rec => l_ref_designator_rec
679 , x_ref_desg_unexp_rec => l_ref_desg_unexp_rec
680 , x_return_status => x_return_status
681 );
682
683 -- Convert the ECO record to BOm for return
684
685 Bom_Bo_Pub.Convert_Ecodesg_To_BomDesg
686 ( p_ref_designator_rec => l_ref_designator_rec
687 , p_ref_desg_unexp_rec => l_ref_Desg_unexp_rec
688 , x_bom_ref_designator_rec => x_bom_ref_designator_rec
689 , x_bom_ref_desg_unexp_rec => x_bom_ref_desg_unexp_Rec
690 );
691
692 END Query_Row;
693
694 PROCEDURE Perform_Writes
695 ( p_bom_ref_designator_rec IN Bom_Bo_Pub.Bom_Ref_Designator_rec_Type
696 , p_bom_ref_desg_unexp_rec IN Bom_Bo_Pub.Bom_Ref_Desg_Unexp_Rec_Type
697 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
698 , x_return_status IN OUT NOCOPY VARCHAR2
699 )
700 IS
701 l_ref_designator_rec Bom_Bo_Pub.Ref_Designator_Rec_Type;
702 l_ref_desg_unexp_rec Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type;
703 BEGIN
704 --
705 -- Convert Bom Recor to ECO
706 --
707 Bom_Bo_Pub.Convert_BomDesg_To_EcoDesg
708 ( p_bom_ref_designator_rec => p_bom_ref_designator_rec
709 , p_bom_ref_desg_unexp_rec => p_bom_ref_desg_unexp_rec
710 , x_ref_designator_rec => l_ref_designator_rec
711 , x_ref_desg_unexp_rec => l_ref_desg_unexp_rec
712 );
713
714 -- Call Perform Writes
715 Bom_Ref_Designator_Util.Perform_Writes
716 ( p_ref_designator_rec => l_ref_designator_rec
717 , p_ref_desg_unexp_rec => l_ref_desg_unexp_rec
718 , x_return_status => x_return_status
719 , x_mesg_token_tbl => x_mesg_token_tbl
720 );
721
722 END Perform_Writes;
723
724
725 END BOM_Ref_Designator_Util;