[Home] [Help]
PACKAGE BODY: APPS.BOM_COMP_OPERATION_UTIL
Source
1 PACKAGE BODY BOM_Comp_Operation_Util AS
2 /* $Header: BOMUCOPB.pls 120.2 2005/09/18 22:10:07 arudresh noship $ */
3 /***************************************************************************
4 --
5 -- Copyright (c) 1996 Oracle Corporation, Redwood Shores, CA, USA
6 -- All rights reserved.
7 --
8 -- FILENAME
9 --
10 -- BOMUCOPB.pls
11 --
12 -- DESCRIPTION
13 --
14 -- Body of package BOM_Comp_Operation_Util
15 --
16 -- NOTES
17 --
18 -- HISTORY
19 --
20 -- 21-AUG-2001 Refai Farook Initial Creation
21 ****************************************************************************/
22
23 -- Global constant holding the package name
24
25 G_PKG_NAME CONSTANT VARCHAR2(30) := 'BOM_Comp_Operation_Util';
26
27 /********************************************************************
28 *
29 * Procedure : Update_Row
30 * Parameter IN : Component Operation Record
31 * Component Operation Unexposed Cols. Record
32 * Parameter OUT : Return_Status - indicating success or failure
33 * Mesg_Token_Tbl - Filled with Errors or warnings
34 * Purpose : Update Row procedure will update the production rec
35 * to the new values as entered in the user record.
36 * Any errors are filled in the Mesg_Token_Tbl.
37 *
38 ********************************************************************/
39
40 PROCEDURE Update_Row
41 ( p_bom_comp_ops_rec IN Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type
42 , p_bom_comp_ops_unexp_Rec IN Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type
43 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
44 , x_return_status IN OUT NOCOPY VARCHAR2
45 )
46 IS
47 l_bom_comp_ops_rec Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type :=
48 p_bom_comp_ops_rec;
49 l_return_status VARCHAR2(200);
50 l_err_text VARCHAR2(255);
51 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
52 l_comp_seq_id NUMBER;
53 BEGIN
54
55 l_return_status := FND_API.G_RET_STS_SUCCESS;
56
57 --bug:3254815 Update request id, prog id, prog appl id and prog update date.
58 UPDATE BOM_COMPONENT_OPERATIONS
59 SET OPERATION_SEQ_NUM
60 = decode( p_bom_comp_ops_rec.new_additional_op_seq_num,
61 NULL,
62 p_bom_comp_ops_rec.additional_operation_seq_num,
63 FND_API.G_MISS_NUM,
64 p_bom_comp_ops_rec.additional_operation_seq_num,
65 p_bom_comp_ops_rec.new_additional_op_seq_num
66 )
67 , OPERATION_SEQUENCE_ID
68 = decode(p_bom_comp_ops_unexp_rec.new_additional_op_seq_id,
69 NULL,
70 p_bom_comp_ops_unexp_rec.additional_operation_seq_id,
71 FND_API.G_MISS_NUM,
72 p_bom_comp_ops_unexp_rec.additional_operation_seq_id,
73 p_bom_comp_ops_unexp_rec.new_additional_op_seq_id
74 )
75 , ATTRIBUTE_CATEGORY = p_bom_comp_ops_rec.attribute_category
76 , ATTRIBUTE1 = p_bom_comp_ops_rec.attribute1
77 , ATTRIBUTE2 = p_bom_comp_ops_rec.attribute2
78 , ATTRIBUTE3 = p_bom_comp_ops_rec.attribute3
79 , ATTRIBUTE4 = p_bom_comp_ops_rec.attribute4
80 , ATTRIBUTE5 = p_bom_comp_ops_rec.attribute5
81 , ATTRIBUTE6 = p_bom_comp_ops_rec.attribute6
82 , ATTRIBUTE7 = p_bom_comp_ops_rec.attribute7
83 , ATTRIBUTE8 = p_bom_comp_ops_rec.attribute8
84 , ATTRIBUTE9 = p_bom_comp_ops_rec.attribute9
85 , ATTRIBUTE10 = p_bom_comp_ops_rec.attribute10
86 , ATTRIBUTE11 = p_bom_comp_ops_rec.attribute11
87 , ATTRIBUTE12 = p_bom_comp_ops_rec.attribute12
88 , ATTRIBUTE13 = p_bom_comp_ops_rec.attribute13
89 , ATTRIBUTE14 = p_bom_comp_ops_rec.attribute14
90 , ATTRIBUTE15 = p_bom_comp_ops_rec.attribute15
91 , LAST_UPDATE_DATE = SYSDATE
92 , LAST_UPDATED_BY = BOM_Globals.Get_User_Id
93 , LAST_UPDATE_LOGIN = BOM_Globals.Get_Login_Id
94 , REQUEST_ID = Fnd_Global.Conc_Request_Id
95 , PROGRAM_ID = Fnd_Global.Conc_Program_Id
96 , PROGRAM_APPLICATION_ID = Fnd_Global.Prog_Appl_Id
97 , PROGRAM_UPDATE_DATE = SYSDATE
98 WHERE COMP_OPERATION_SEQ_ID = p_bom_comp_ops_unexp_rec.comp_operation_seq_id ;
99
100 SELECT component_sequence_id
101 INTO l_comp_seq_id
102 FROM BOM_COMPONENT_OPERATIONS
103 WHERE COMP_OPERATION_SEQ_ID = p_bom_comp_ops_unexp_rec.comp_operation_seq_id ;
104
105 IF SQL%NOTFOUND THEN
106
107 Error_Handler.Add_Error_Token
108 ( p_Message_name => 'BOM_NOT_UPDATE_ROW'
109 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
110 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
111 );
112
113 l_return_status := FND_API.G_RET_STS_ERROR;
114 ELSE
115 BOMPCMBM.Update_Related_Comp_Ops(p_component_sequence_id => l_comp_seq_id
116 , p_old_operation_seq_num => p_bom_comp_ops_rec.additional_operation_seq_num
117 , p_new_operation_seq_num => nvl(p_bom_comp_ops_rec.new_additional_op_seq_num,
118 p_bom_comp_ops_rec.additional_operation_seq_num)
119 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
120 , x_Return_Status => l_return_status);
121
122 END IF;
123
124 x_return_status := l_return_status;
125 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
126
127 EXCEPTION
128
129 WHEN OTHERS THEN
130 /* The following IF is to find out if the current level of the error
131 is greater than or equal to the message level threshold defined in the
132 profile 'FND_AS_MSG_LEVEL_THRESHOLD' */
133
134 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
135 THEN
136 l_err_text := G_PKG_NAME ||
137 'Utility (Component Operations Update)' ||
138 SUBSTR(SQLERRM, 1, 100);
139
140 Error_Handler.Add_Error_Token
141 ( p_Message_Name => NULL
142 , p_Message_Text => l_err_text
143 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
144 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
145 );
146 END IF;
147 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
148 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
149
150 END Update_Row;
151
152 /********************************************************************
153 *
154 * Procedure : Insert_Row
155 * Parameters IN : Component Ops.Record as given by the User
156 * Component Ops Unexposed Cols. Record
157 * Parameters OUT: Component Operations Record
158 * Return_Status - Indicating success or faliure
159 * Mesg_Token_Tbl - Filled with any errors or warnings
160 * Purpose : Will Insert a new component operatins record in
161 * Bom_Component_Operations table.
162 *
163 ********************************************************************/
164 PROCEDURE Insert_Row
165 ( p_bom_comp_ops_rec IN Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type
166 , p_bom_Comp_Ops_Unexp_Rec IN Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type
167 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
168 , x_return_status IN OUT NOCOPY VARCHAR2
169 )
170 IS
171 l_err_text VARCHAR2(255);
172 l_return_status VARCHAR2(200);
173 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
174 BEGIN
175
176
177 INSERT INTO BOM_COMPONENT_OPERATIONS
178 ( COMP_OPERATION_SEQ_ID
179 , OPERATION_SEQ_NUM
180 , OPERATION_SEQUENCE_ID
181 , BILL_SEQUENCE_ID
182 , LAST_UPDATE_DATE
183 , LAST_UPDATED_BY
184 , CREATION_DATE
185 , CREATED_BY
186 , LAST_UPDATE_LOGIN
187 , COMPONENT_SEQUENCE_ID
188 , ATTRIBUTE_CATEGORY
189 , ATTRIBUTE1
190 , ATTRIBUTE2
191 , ATTRIBUTE3
192 , ATTRIBUTE4
193 , ATTRIBUTE5
194 , ATTRIBUTE6
195 , ATTRIBUTE7
196 , ATTRIBUTE8
197 , ATTRIBUTE9
198 , ATTRIBUTE10
199 , ATTRIBUTE11
200 , ATTRIBUTE12
201 , ATTRIBUTE13
202 , ATTRIBUTE14
203 , ATTRIBUTE15
204 , REQUEST_ID
205 , PROGRAM_ID
206 , PROGRAM_APPLICATION_ID
207 , PROGRAM_UPDATE_DATE
208 )
209 VALUES
210 ( BOM_COMPONENT_OPERATIONS_S.NEXTVAL
211 , p_bom_comp_ops_rec.additional_operation_seq_num
212 , p_bom_comp_ops_unexp_rec.additional_operation_seq_id
213 , p_bom_comp_ops_unexp_rec.bill_sequence_id
214 , SYSDATE
215 , Bom_globals.Get_User_Id
216 , SYSDATE
217 , Bom_Globals.Get_User_Id
218 , Bom_Globals.Get_User_Id
219 , p_bom_comp_ops_unexp_rec.component_sequence_id
220 , p_bom_comp_ops_rec.attribute_category
221 , p_bom_comp_ops_rec.attribute1
222 , p_bom_comp_ops_rec.attribute2
223 , p_bom_comp_ops_rec.attribute3
224 , p_bom_comp_ops_rec.attribute4
225 , p_bom_comp_ops_rec.attribute5
226 , p_bom_comp_ops_rec.attribute6
227 , p_bom_comp_ops_rec.attribute7
228 , p_bom_comp_ops_rec.attribute8
229 , p_bom_comp_ops_rec.attribute9
230 , p_bom_comp_ops_rec.attribute10
231 , p_bom_comp_ops_rec.attribute11
232 , p_bom_comp_ops_rec.attribute12
233 , p_bom_comp_ops_rec.attribute13
234 , p_bom_comp_ops_rec.attribute14
235 , p_bom_comp_ops_rec.attribute15
236 , Fnd_Global.Conc_Request_Id
237 , Fnd_Global.Conc_Program_Id
238 , Fnd_Global.Prog_Appl_Id
239 , SYSDATE
240 );
241
242 --x_return_status := FND_API.G_RET_STS_SUCCESS;
243 BOMPCMBM.Insert_Related_Comp_Ops(p_component_sequence_id => p_bom_comp_ops_unexp_rec.component_sequence_id
244 , p_operation_seq_num => p_bom_comp_ops_rec.additional_operation_seq_num
245 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
246 , x_Return_Status => l_return_status);
247 x_return_status := l_return_status;
248
249 EXCEPTION
250
251 WHEN OTHERS THEN
252 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
253 THEN
254 l_err_text := G_PKG_NAME ||
255 'Utility (Component Operations Insert)' ||
256 SUBSTR(SQLERRM, 1, 100);
257 Error_Handler.Add_Error_Token
258 ( p_Message_Name => NULL
259 , p_Message_text => l_err_text
260 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
261 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
262 );
263 END IF;
264 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
265 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
266
267 END Insert_Row;
268
269
270 /********************************************************************
271 *
272 * Procedure : Delete_Row
273 * Parameters IN : Primary Key of Component Operation Table
274 * Parameters OUT: Return_Status - Indicating success or faliure
275 * Mesg_Token_Tbl - Filled with any errors or warnings
276 * Purpose : Will delete a component operation record using the
277 * primary unique key.
278 ********************************************************************/
279 PROCEDURE Delete_Row
280 ( p_comp_operation_seq_id IN NUMBER
281 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
282 , x_return_status IN OUT NOCOPY VARCHAR2
283 )
284 IS
285 l_return_status VARCHAR2(200);
286 l_err_text VARCHAR2(255);
287 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
288 l_comp_seq_id NUMBER;
289 l_op_seq_num NUMBER;
290 BEGIN
291
292 l_return_status := FND_API.G_RET_STS_SUCCESS;
293
294 SELECT component_sequence_id, operation_seq_num
295 INTO l_comp_seq_id, l_op_seq_num
296 FROM BOM_COMPONENT_OPERATIONS
297 WHERE COMP_OPERATION_SEQ_ID = p_comp_operation_seq_id;
298
299 DELETE FROM BOM_COMPONENT_OPERATIONS
300 WHERE COMP_OPERATION_SEQ_ID = p_comp_operation_seq_id;
301
302 IF SQL%NOTFOUND THEN
303
304 Error_Handler.Add_Error_Token
305 ( p_Message_name => 'BOM_NOT_DELETE_ROW'
306 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
307 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
308 );
309
310 l_return_status := FND_API.G_RET_STS_ERROR;
311
312 ELSE
313 BOMPCMBM.Delete_Related_Comp_Ops(p_src_comp_seq_id => l_comp_seq_id,
314 p_operation_seq_num => l_op_seq_num,
315 x_return_status => l_return_status);
316
317 END IF;
318
319 x_return_status := l_return_status;
320
321 EXCEPTION
322
323 WHEN OTHERS THEN
324
325 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
326 THEN
327 l_err_text := G_PKG_NAME ||
328 'Utility (Component Operations Delete_Row)' ||
329 SUBSTR(SQLERRM, 1, 100);
330
331 Error_Handler.Add_Error_Token
332 ( p_Message_Name => NULL
333 , p_Message_Text => l_err_text
334 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
335 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
336 );
337 END IF;
338 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
339 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
340
341 END Delete_Row;
342
343 /********************************************************************
344 *
345 * Procedure : Query_Row
346 * Parameters IN : Component Operation primary key
347 * Parameters OUT: Component Operation Record of exposed colmuns
348 * Component Operation record of unexposed columns
349 * Return_Status - Indicating success or faliure
350 * Mesg_Token_Tbl - Filled with any errors or warnings
351 * Purpose : Complete Record will take the Database record and
352 * compare it with the user record and will complete
353 * the user record by filling in those values from the
354 * record that the user has left blank.
355 * Any user filled in columns will not be overwritten
356 * even if the values do not match.
357 ********************************************************************/
358 PROCEDURE Query_Row
359 ( p_component_sequence_id IN NUMBER
360 , p_additional_operation_seq_num IN NUMBER
361 , x_bom_comp_ops_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type
362 , x_bom_comp_ops_Unexp_Rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type
363 , x_return_status IN OUT NOCOPY VARCHAR2
364 )
365 IS
366 l_bom_comp_ops_rec Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type;
367 l_bom_comp_ops_unexp_rec Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type;
368 BEGIN
369
370 SELECT ROWID
371 , COMP_OPERATION_SEQ_ID
372 , OPERATION_SEQ_NUM
373 , OPERATION_SEQUENCE_ID
374 , COMPONENT_SEQUENCE_ID
375 , BILL_SEQUENCE_ID
376 , ATTRIBUTE_CATEGORY
377 , ATTRIBUTE1
378 , ATTRIBUTE2
379 , ATTRIBUTE3
380 , ATTRIBUTE4
381 , ATTRIBUTE5
382 , ATTRIBUTE6
383 , ATTRIBUTE7
384 , ATTRIBUTE8
385 , ATTRIBUTE9
386 , ATTRIBUTE10
387 , ATTRIBUTE11
388 , ATTRIBUTE12
389 , ATTRIBUTE13
390 , ATTRIBUTE14
391 , ATTRIBUTE15
392 INTO
393 l_bom_comp_ops_unexp_rec.rowid
394 , l_bom_comp_ops_unexp_rec.comp_operation_seq_id
398 , l_bom_comp_ops_unexp_rec.bill_sequence_id
395 , l_bom_comp_ops_rec.operation_sequence_number
396 , l_bom_comp_ops_unexp_rec.additional_operation_seq_id
397 , l_bom_comp_ops_unexp_rec.component_sequence_id
399 , l_bom_comp_ops_rec.attribute_category
400 , l_bom_comp_ops_rec.attribute1
401 , l_bom_comp_ops_rec.attribute2
402 , l_bom_comp_ops_rec.attribute3
403 , l_bom_comp_ops_rec.attribute4
404 , l_bom_comp_ops_rec.attribute5
405 , l_bom_comp_ops_rec.attribute6
406 , l_bom_comp_ops_rec.attribute7
407 , l_bom_comp_ops_rec.attribute8
408 , l_bom_comp_ops_rec.attribute9
409 , l_bom_comp_ops_rec.attribute10
410 , l_bom_comp_ops_rec.attribute11
411 , l_bom_comp_ops_rec.attribute12
412 , l_bom_comp_ops_rec.attribute13
413 , l_bom_comp_ops_rec.attribute14
414 , l_bom_comp_ops_rec.attribute15
415 FROM BOM_COMPONENT_OPERATIONS
416 WHERE COMPONENT_SEQUENCE_ID = p_component_sequence_id AND
417 OPERATION_SEQ_NUM = p_additional_operation_seq_num ;
418
419 x_bom_comp_ops_Rec := l_bom_comp_ops_rec;
420 x_bom_comp_ops_unexp_Rec := l_bom_comp_ops_unexp_rec;
421 x_return_status := Bom_Globals.G_RECORD_FOUND;
422
423 EXCEPTION
424
425 WHEN OTHERS THEN
426 x_bom_comp_ops_Rec := l_bom_comp_ops_rec;
427 x_bom_comp_ops_unexp_Rec := l_bom_comp_ops_unexp_rec;
428 x_return_status := Bom_Globals.G_RECORD_NOT_FOUND;
429
430 END Query_Row;
431
432 PROCEDURE Perform_Writes
433 ( p_bom_comp_ops_rec IN Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type
434 , p_bom_comp_ops_unexp_rec IN Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type
435 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
436 , x_Return_Status IN OUT NOCOPY VARCHAR2
437 )
438 IS
439 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
440 l_return_status VARCHAR2(1);
441 l_bom_comp_ops_rec Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type;
442 l_bom_comp_ops_unexp_rec Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type;
443 BEGIN
444 l_bom_comp_ops_rec := p_bom_comp_ops_rec;
445 l_bom_comp_ops_unexp_rec := p_bom_comp_ops_unexp_rec;
446
447 IF l_bom_comp_ops_rec.transaction_type = Bom_Globals.G_OPR_CREATE
448 THEN
449 Insert_Row( p_bom_comp_ops_rec => l_bom_comp_ops_rec
450 , p_bom_comp_ops_Unexp_Rec => l_bom_comp_ops_unexp_rec
451 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
452 , x_return_status => l_return_status
453 );
454 ELSIF l_bom_comp_ops_rec.transaction_type = Bom_Globals.G_OPR_UPDATE
455 THEN
456 Update_Row( p_bom_comp_ops_rec => l_bom_comp_ops_rec
457 , p_bom_comp_ops_Unexp_Rec => l_bom_comp_ops_unexp_rec
458 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
459 , x_return_status => l_return_status
460 );
461
462 ELSIF l_bom_comp_ops_rec.transaction_type = Bom_Globals.G_OPR_DELETE
463 THEN
464 Delete_Row
465 ( p_comp_operation_seq_id => l_bom_comp_ops_unexp_rec.comp_operation_seq_id
466 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
467 , x_return_status => l_return_status
468 );
469
470 END IF;
471
472 x_return_status := l_return_status;
473 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
474
475 END Perform_Writes;
476
477
478 END BOM_Comp_Operation_Util;