[Home] [Help]
PACKAGE BODY: APPS.ENG_REF_DESIGNATOR_UTIL
Source
1 PACKAGE BODY ENG_Ref_Designator_Util AS
2 /* $Header: ENGURFDB.pls 115.12 2002/12/14 00:44:17 bbontemp ship $ */
3
4 -- /* $Header: ENGURFDB.pls 115.12 2002/12/14 00:44:17 bbontemp ship $ */
5
6 -- Global constant holding the package name
7
8 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ENG_Ref_Designator_Util';
9
10 -- Procedure Clear_Dependent_Attr
11
12 PROCEDURE Clear_Dependent_Attr
13 ( p_attr_id IN NUMBER :=NULL-- FND_API.G_MISS_NUM
14 , p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
15 , p_old_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type :=
16 Bom_Bo_Pub.G_MISS_REF_DESIGNATOR_REC
17 , x_ref_designator_rec IN OUT NOCOPY Bom_Bo_Pub.Ref_Designator_Rec_Type
18 )
19 IS
20 BEGIN
21 -- Load out record
22
23 x_ref_designator_rec := p_ref_designator_rec;
24
25 END Clear_Dependent_Attr;
26
27 -- Procedure Apply_Attribute_Changes
28
29 PROCEDURE Apply_Attribute_Changes
30 ( p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
31 , p_old_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type :=
32 Bom_Bo_Pub.G_MISS_REF_DESIGNATOR_REC
33 , x_ref_designator_rec IN OUT NOCOPY Bom_Bo_Pub.Ref_Designator_Rec_Type
34 )
35 IS
36 BEGIN
37 -- Load out record
38
39 x_ref_designator_rec := p_ref_designator_rec;
40
41 END Apply_Attribute_Changes;
42
43 -- Function Convert_Miss_To_Null
44
45 FUNCTION Convert_Miss_To_Null
46 ( p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
47 ) RETURN Bom_Bo_Pub.Ref_Designator_Rec_Type
48 IS
49 l_ref_designator_rec Bom_Bo_Pub.Ref_Designator_Rec_Type :=
50 p_ref_designator_rec;
51 BEGIN
52
53 IF l_ref_designator_rec.Reference_Designator_Name = FND_API.G_MISS_CHAR THEN
54 l_ref_designator_rec.Reference_Designator_Name := NULL;
55 END IF;
56
57 IF l_ref_designator_rec.attribute_category = FND_API.G_MISS_CHAR THEN
58 l_ref_designator_rec.attribute_category := NULL;
59 END IF;
60
61 IF l_ref_designator_rec.attribute1 = FND_API.G_MISS_CHAR THEN
62 l_ref_designator_rec.attribute1 := NULL;
63 END IF;
64
65 IF l_ref_designator_rec.attribute2 = FND_API.G_MISS_CHAR THEN
66 l_ref_designator_rec.attribute2 := NULL;
67 END IF;
68
69 IF l_ref_designator_rec.attribute3 = FND_API.G_MISS_CHAR THEN
70 l_ref_designator_rec.attribute3 := NULL;
71 END IF;
72
73 IF l_ref_designator_rec.attribute4 = FND_API.G_MISS_CHAR THEN
74 l_ref_designator_rec.attribute4 := NULL;
75 END IF;
76
77 IF l_ref_designator_rec.attribute5 = FND_API.G_MISS_CHAR THEN
78 l_ref_designator_rec.attribute5 := NULL;
79 END IF;
80
81 IF l_ref_designator_rec.attribute6 = FND_API.G_MISS_CHAR THEN
82 l_ref_designator_rec.attribute6 := NULL;
83 END IF;
84
85 IF l_ref_designator_rec.attribute7 = FND_API.G_MISS_CHAR THEN
86 l_ref_designator_rec.attribute7 := NULL;
87 END IF;
88
89 IF l_ref_designator_rec.attribute8 = FND_API.G_MISS_CHAR THEN
90 l_ref_designator_rec.attribute8 := NULL;
91 END IF;
92
93 IF l_ref_designator_rec.attribute9 = FND_API.G_MISS_CHAR THEN
94 l_ref_designator_rec.attribute9 := NULL;
95 END IF;
96
97 IF l_ref_designator_rec.attribute10 = FND_API.G_MISS_CHAR THEN
98 l_ref_designator_rec.attribute10 := NULL;
99 END IF;
100
101 IF l_ref_designator_rec.attribute11 = FND_API.G_MISS_CHAR THEN
102 l_ref_designator_rec.attribute11 := NULL;
103 END IF;
104
105 IF l_ref_designator_rec.attribute12 = FND_API.G_MISS_CHAR THEN
106 l_ref_designator_rec.attribute12 := NULL;
107 END IF;
108
109 IF l_ref_designator_rec.attribute13 = FND_API.G_MISS_CHAR THEN
110 l_ref_designator_rec.attribute13 := NULL;
111 END IF;
112
113 IF l_ref_designator_rec.attribute14 = FND_API.G_MISS_CHAR THEN
114 l_ref_designator_rec.attribute14 := NULL;
115 END IF;
116
117 IF l_ref_designator_rec.attribute15 = FND_API.G_MISS_CHAR THEN
118 l_ref_designator_rec.attribute15 := NULL;
119 END IF;
120
121 RETURN l_ref_designator_rec;
122
123 END Convert_Miss_To_Null;
124
125 /********************************************************************
126 *
127 * Procedure : Update_Row
128 * Parameters IN : Reference Designator Record as given by the User
129 * Reference Designator Unexposed Record
130 * Parameters OUT: Return_Status - Indicating success or faliure
131 * Mesg_Token_Tbl - Filled with any errors or warnings
132 * Purpose : Will update the Reference Designator record and
133 * if unable to update then return with a status
134 * and Error Message table filled with the message
135 *
136 ********************************************************************/
137
138 PROCEDURE Update_Row
139 ( p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
140 , p_Ref_Desg_Unexp_Rec IN Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
141 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
142 , x_Return_Status OUT NOCOPY VARCHAR2
143 )
144 IS
145 l_return_status varchar2(80);
146 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
147 l_ref_designator_rec Bom_Bo_Pub.Ref_Designator_Rec_Type;
148 l_Token_Tbl Error_Handler.Token_Tbl_Type;
149 BEGIN
150
151 UPDATE BOM_REFERENCE_DESIGNATORS
152 SET LAST_UPDATE_DATE = SYSDATE
153 , LAST_UPDATED_BY = Bom_Globals.Get_User_Id
154 , LAST_UPDATE_LOGIN = Bom_Globals.Get_User_Id
155 , REF_DESIGNATOR_COMMENT =
156 p_ref_designator_rec.ref_designator_comment
157 , ATTRIBUTE_CATEGORY = p_ref_designator_rec.attribute_category
158 , ATTRIBUTE1 = p_ref_designator_rec.attribute1
159 , ATTRIBUTE2 = p_ref_designator_rec.attribute2
160 , ATTRIBUTE3 = p_ref_designator_rec.attribute3
161 , ATTRIBUTE4 = p_ref_designator_rec.attribute4
162 , ATTRIBUTE5 = p_ref_designator_rec.attribute5
163 , ATTRIBUTE6 = p_ref_designator_rec.attribute6
164 , ATTRIBUTE7 = p_ref_designator_rec.attribute7
165 , ATTRIBUTE8 = p_ref_designator_rec.attribute8
166 , ATTRIBUTE9 = p_ref_designator_rec.attribute9
167 , ATTRIBUTE10 = p_ref_designator_rec.attribute10
168 , ATTRIBUTE11 = p_ref_designator_rec.attribute11
169 , ATTRIBUTE12 = p_ref_designator_rec.attribute12
170 , ATTRIBUTE13 = p_ref_designator_rec.attribute13
171 , ATTRIBUTE14 = p_ref_designator_rec.attribute14
172 , ATTRIBUTE15 = p_ref_designator_rec.attribute15
173 , Original_System_Reference =
174 p_ref_designator_rec.Original_System_Reference
175 WHERE COMPONENT_REFERENCE_DESIGNATOR =
176 p_ref_designator_rec.Reference_Designator_Name
177 AND COMPONENT_SEQUENCE_ID =
178 p_Ref_Desg_Unexp_Rec.component_sequence_id
179 AND ACD_TYPE = p_Ref_Designator_Rec.acd_type
180 ;
181
182 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
183
184 EXCEPTION
185 WHEN OTHERS THEN
186 Error_Handler.Add_Error_Token
187 ( p_Message_Name => NULL
188 , p_Message_Text => 'ERROR in Update Row (Ref Desgs)' ||
189 substr(SQLERRM, 1, 100) || ' ' ||
190 to_char(SQLCODE)
191 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
192 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl);
193
194 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
195
196 END Update_Row;
197
198 /********************************************************************
199 *
200 * Procedure : Insert_Row
201 * Parameters IN : Reference Designator Record as given by the User
202 * Reference Designator Unexposed Record
203 * Parameters OUT: Return_Status - Indicating success or faliure
204 * Mesg_Token_Tbl - Filled with any errors or warnings
205 * Purpose : Insert Reference Designator record and if unable to
206 * to insert then return with an error_status and
207 * Error message filled in the Message Token Table.
208 *
209 ********************************************************************/
210
211 PROCEDURE Insert_Row
212 ( p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_Rec_Type
213 , p_Ref_Desg_Unexp_Rec IN Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
214 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
215 , x_Return_Status OUT NOCOPY VARCHAR2
216 )
217 IS
218 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
219 BEGIN
220
221 INSERT INTO BOM_REFERENCE_DESIGNATORS
222 ( COMPONENT_REFERENCE_DESIGNATOR
223 , LAST_UPDATE_DATE
224 , LAST_UPDATED_BY
225 , CREATION_DATE
226 , CREATED_BY
227 , LAST_UPDATE_LOGIN
228 , REF_DESIGNATOR_COMMENT
229 , CHANGE_NOTICE
230 , COMPONENT_SEQUENCE_ID
231 , ACD_TYPE
232 , REQUEST_ID
233 , PROGRAM_APPLICATION_ID
234 , PROGRAM_ID
235 , PROGRAM_UPDATE_DATE
236 , ATTRIBUTE_CATEGORY
237 , ATTRIBUTE1
238 , ATTRIBUTE2
239 , ATTRIBUTE3
240 , ATTRIBUTE4
241 , ATTRIBUTE5
242 , ATTRIBUTE6
243 , ATTRIBUTE7
244 , ATTRIBUTE8
245 , ATTRIBUTE9
246 , ATTRIBUTE10
247 , ATTRIBUTE11
248 , ATTRIBUTE12
249 , ATTRIBUTE13
250 , ATTRIBUTE14
251 , ATTRIBUTE15
252 , Original_System_Reference
253 )
254 VALUES
255 ( p_ref_designator_rec.Reference_Designator_Name
256 , SYSDATE
257 , Bom_Globals.Get_User_Id
258 , SYSDATE
259 , Bom_Globals.Get_User_Id
260 , Bom_Globals.Get_User_Id
261 , p_ref_designator_rec.ref_designator_comment
262 , p_ref_designator_rec.Eco_Name
263 , p_Ref_Desg_Unexp_Rec.component_sequence_id
264 , p_ref_designator_rec.acd_type
265 , NULL /* Request Id */
266 , Bom_Globals.Get_Prog_AppId
267 , Bom_Globals.Get_Prog_Id
268 , SYSDATE
269 , p_ref_designator_rec.attribute_category
270 , p_ref_designator_rec.attribute1
271 , p_ref_designator_rec.attribute2
272 , p_ref_designator_rec.attribute3
273 , p_ref_designator_rec.attribute4
274 , p_ref_designator_rec.attribute5
275 , p_ref_designator_rec.attribute6
276 , p_ref_designator_rec.attribute7
277 , p_ref_designator_rec.attribute8
278 , p_ref_designator_rec.attribute9
279 , p_ref_designator_rec.attribute10
280 , p_ref_designator_rec.attribute11
281 , p_ref_designator_rec.attribute12
282 , p_ref_designator_rec.attribute13
283 , p_ref_designator_rec.attribute14
284 , p_ref_designator_rec.attribute15
285 , p_ref_designator_rec.Original_System_Reference
286 );
287
288 x_return_status := FND_API.G_RET_STS_SUCCESS;
289 EXCEPTION
290
291 WHEN OTHERS THEN
292 Error_Handler.Add_Error_Token
293 ( p_Message_Name => NULL
294 , p_Message_Text => 'ERROR in Insert Row (Ref Desgs)' ||
295 substr(SQLERRM, 1, 100) || ' ' ||
296 to_char(SQLCODE)
297 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
298 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
299 );
300
301 --dbms_output.put_line('ERROR in Insert Row (Ref Desgs)' ||
302 -- substr(SQLERRM, 1, 100) || ' ' ||
303 -- to_char(SQLCODE)
304 -- );
305
306 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
307
308 END Insert_Row;
309
310 /********************************************************************
311 *
312 * Procedure : Delete_Row
313 * Parameters IN : Reference Designator Key
314 * Parameters OUT: Return_Status - Indicating success or faliure
315 * Mesg_Token_Tbl - Filled with any errors or warnings
316 * Purpose : Insert Reference Designator record and if unable to
317 * to insert then return with an error_status and
318 * Error message filled in the Message Token Table.
319 *
320 ********************************************************************/
321
322 PROCEDURE Delete_Row
323 ( p_ref_designator IN VARCHAR2
324 , p_component_sequence_id IN NUMBER
325 , p_acd_type IN NUMBER
326 , x_Mesg_Token_Tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
327 , x_Return_Status OUT NOCOPY VARCHAR2
328 )
329 IS
330 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
331
332 BEGIN
333
334 DELETE FROM BOM_REFERENCE_DESIGNATORS
335 WHERE COMPONENT_REFERENCE_DESIGNATOR = p_ref_designator
336 AND COMPONENT_SEQUENCE_ID = p_component_sequence_id
337 AND ACD_TYPE = p_acd_type
338 ;
339
340 x_Return_Status := FND_API.G_RET_STS_SUCCESS;
341
342 EXCEPTION
343 WHEN OTHERS THEN
344 Error_Handler.Add_Error_Token
345 ( p_Message_Name => NULL
346 , p_Message_Text => 'ERROR in Delete Row (Ref Desgs)' ||
347 substr(SQLERRM, 1, 100) || ' ' ||
348 to_char(SQLCODE)
349 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
350 , x_Mesg_Token_Tbl => x_Mesg_Token_Tbl
351 );
352 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
353
354 END Delete_Row;
355
356 /********************************************************************
357 *
358 * Procedure : Query_Row
359 * Parameters IN : Reference Designator Key
360 * Parameters OUT: Reference Designator Record of exposed columns
361 * Reference Designator Record of Unexposed Columns
362 * Return_Status - Indicating success or faliure
363 * Mesg_Token_Tbl - Filled with any errors or warnings
364 * Purpose : Query Row procedure will query a database record
365 * seperate the values into the exposed and the un-
366 * exposed record and return the values.
367 * If the query fails then the Error Token table will
368 * be filled in with the error message and returned with
369 * and error status otherwise a success
370 ********************************************************************/
371
372 PROCEDURE Query_Row
373 ( p_ref_designator IN VARCHAR2
374 , p_component_sequence_id IN NUMBER
375 , p_acd_type IN NUMBER
376 , x_Ref_Designator_Rec OUT NOCOPY Bom_Bo_Pub.Ref_Designator_Rec_Type
377 , x_Ref_Desg_Unexp_Rec OUT NOCOPY Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
378 , x_Return_Status OUT NOCOPY VARCHAR2
379 )
380 IS
381 l_ref_designator_rec Bom_Bo_Pub.Ref_Designator_Rec_Type;
382 l_Ref_Desg_Unexp_Rec Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type;
383 l_err_text VARCHAR2(2000);
384 BEGIN
385
386 SELECT COMPONENT_REFERENCE_DESIGNATOR
387 , REF_DESIGNATOR_COMMENT
388 , CHANGE_NOTICE
389 , COMPONENT_SEQUENCE_ID
390 , ACD_TYPE
391 , ATTRIBUTE_CATEGORY
392 , ATTRIBUTE1
393 , ATTRIBUTE2
394 , ATTRIBUTE3
395 , ATTRIBUTE4
396 , ATTRIBUTE5
397 , ATTRIBUTE6
398 , ATTRIBUTE7
399 , ATTRIBUTE8
400 , ATTRIBUTE9
401 , ATTRIBUTE10
402 , ATTRIBUTE11
403 , ATTRIBUTE12
404 , ATTRIBUTE13
405 , ATTRIBUTE14
406 , ATTRIBUTE15
407 INTO l_ref_designator_rec.Reference_Designator_Name
408 , l_ref_designator_rec.ref_designator_comment
409 , l_ref_designator_rec.Eco_Name
410 , l_Ref_Desg_Unexp_Rec.component_sequence_id
411 , l_ref_designator_rec.acd_type
412 , l_ref_designator_rec.attribute_category
413 , l_ref_designator_rec.attribute1
414 , l_ref_designator_rec.attribute2
415 , l_ref_designator_rec.attribute3
416 , l_ref_designator_rec.attribute4
417 , l_ref_designator_rec.attribute5
418 , l_ref_designator_rec.attribute6
419 , l_ref_designator_rec.attribute7
420 , l_ref_designator_rec.attribute8
421 , l_ref_designator_rec.attribute9
422 , l_ref_designator_rec.attribute10
423 , l_ref_designator_rec.attribute11
424 , l_ref_designator_rec.attribute12
425 , l_ref_designator_rec.attribute13
426 , l_ref_designator_rec.attribute14
427 , l_ref_designator_rec.attribute15
428 FROM BOM_REFERENCE_DESIGNATORS
429 WHERE COMPONENT_REFERENCE_DESIGNATOR = p_ref_designator
430 AND COMPONENT_SEQUENCE_ID = p_component_sequence_id
431 AND ACD_TYPE = p_acd_type
432 ;
433
434 x_Ref_Designator_Rec := l_ref_designator_rec;
435 x_Ref_Desg_Unexp_Rec := l_Ref_Desg_Unexp_Rec;
436 x_Return_Status := Bom_Globals.G_RECORD_FOUND;
437
438 EXCEPTION
439
440 WHEN NO_DATA_FOUND THEN
441 x_Return_Status := Bom_Globals.G_RECORD_NOT_FOUND;
442
443 WHEN OTHERS THEN
444 x_Return_Status := FND_API.G_RET_STS_UNEXP_ERROR;
445
446 END Query_Row;
447
448 PROCEDURE Perform_Writes
449 ( p_ref_designator_rec IN Bom_Bo_Pub.Ref_Designator_rec_Type
450 , p_ref_desg_unexp_rec IN Bom_Bo_Pub.Ref_Desg_Unexposed_Rec_Type
451 , x_mesg_token_tbl OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
452 , x_return_status OUT NOCOPY VARCHAR2
453 )
454 IS
455 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type;
456 l_return_status VARCHAR2(1);
457 BEGIN
458 l_return_status := FND_API.G_RET_STS_SUCCESS;
459
460 IF p_ref_designator_rec.transaction_type = Bom_Globals.G_OPR_CREATE
461 THEN
462 Insert_Row( p_ref_designator_rec =>
463 p_ref_designator_rec
464 , p_ref_desg_unexp_rec =>
465 p_ref_desg_unexp_rec
466 , x_mesg_token_tbl => l_mesg_token_tbl
467 , x_return_status => l_return_status
468 );
469 ELSIF p_ref_designator_rec.transaction_type = Bom_Globals.G_OPR_UPDATE
470 THEN
471 Update_Row( p_ref_designator_rec =>
472 p_ref_designator_rec
473 , p_ref_desg_unexp_rec =>
474 p_ref_desg_unexp_rec
475 , x_mesg_token_tbl =>
476 l_mesg_token_tbl
477 , x_return_status =>
478 l_return_status
479 );
480 ELSIF p_ref_designator_rec.transaction_type = Bom_Globals.G_OPR_DELETE
481 THEN
482 Delete_Row
483 ( p_ref_designator =>
484 p_ref_designator_rec.reference_designator_name
485 , p_component_sequence_id =>
486 p_ref_desg_unexp_rec.component_sequence_id
487 , p_acd_type =>
488 p_ref_designator_rec.acd_type
489 , x_Mesg_Token_Tbl => l_mesg_token_tbl
490 , x_Return_Status => l_return_status
491 );
492 END IF;
493
494 x_return_status := l_return_status;
495 x_mesg_token_tbl := l_mesg_token_tbl;
496
497 END Perform_Writes;
498
499 END ENG_Ref_Designator_Util;