[Home] [Help]
PACKAGE BODY: APPS.BOM_COMP_OPERATION_UTIL
Source
1 PACKAGE BODY BOM_Comp_Operation_Util AS
2 /* $Header: BOMUCOPB.pls 120.2.12010000.3 2010/02/13 01:37:02 umajumde ship $ */
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 --following function has been added for bug 7713832
154 FUNCTION Common_CompSeqIdCO( p_comp_seq_id NUMBER)
155 RETURN NUMBER
156 IS
157 l_src_comp_seq_id NUMBER;
158
159 BEGIN
160
161 SELECT common_component_sequence_id
162 INTO l_src_comp_seq_id
163 FROM bom_components_b
164 WHERE component_sequence_id = p_comp_seq_id
165 and component_sequence_id <> common_component_sequence_id;
166
167 RETURN l_src_comp_seq_id;
168
169 EXCEPTION
170 WHEN NO_DATA_FOUND THEN
171 RETURN NULL;
172
173 WHEN OTHERS THEN
174 RETURN NULL;
175 END;
176
177
178 /********************************************************************
179 *
180 * Procedure : Insert_Row
181 * Parameters IN : Component Ops.Record as given by the User
182 * Component Ops Unexposed Cols. Record
183 * Parameters OUT: Component Operations Record
184 * Return_Status - Indicating success or faliure
185 * Mesg_Token_Tbl - Filled with any errors or warnings
186 * Purpose : Will Insert a new component operatins record in
187 * Bom_Component_Operations table.
188 *
189 ********************************************************************/
190 PROCEDURE Insert_Row
191 ( p_bom_comp_ops_rec IN Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type
192 , p_bom_Comp_Ops_Unexp_Rec IN Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type
193 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
194 , x_return_status IN OUT NOCOPY VARCHAR2
195 )
196 IS
197 l_err_text VARCHAR2(255);
198 l_return_status VARCHAR2(200);
199 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
200 l_src_comp_seq_id NUMBER := NULL; --bug 7713832
201 BEGIN
202 --Bug 7712832 changes start
203 IF Bom_Globals.Get_Caller_Type = 'MIGRATION' THEN
204 l_src_comp_seq_id := Common_CompSeqIdCO( p_comp_seq_id => p_bom_comp_ops_unexp_rec.component_sequence_id
205 );
206 END IF;
207 --Bug 7712832 changes end
208
209 INSERT INTO BOM_COMPONENT_OPERATIONS
210 ( COMP_OPERATION_SEQ_ID
211 , OPERATION_SEQ_NUM
212 , OPERATION_SEQUENCE_ID
213 , BILL_SEQUENCE_ID
214 , LAST_UPDATE_DATE
215 , LAST_UPDATED_BY
216 , CREATION_DATE
217 , CREATED_BY
218 , LAST_UPDATE_LOGIN
219 , COMPONENT_SEQUENCE_ID
220 , ATTRIBUTE_CATEGORY
221 , ATTRIBUTE1
222 , ATTRIBUTE2
223 , ATTRIBUTE3
224 , ATTRIBUTE4
225 , ATTRIBUTE5
226 , ATTRIBUTE6
227 , ATTRIBUTE7
228 , ATTRIBUTE8
229 , ATTRIBUTE9
230 , ATTRIBUTE10
231 , ATTRIBUTE11
232 , ATTRIBUTE12
233 , ATTRIBUTE13
234 , ATTRIBUTE14
235 , ATTRIBUTE15
236 , REQUEST_ID
237 , PROGRAM_ID
238 , PROGRAM_APPLICATION_ID
239 , PROGRAM_UPDATE_DATE
240 , Common_Component_Sequence_Id --added for bug 7713832
241 )
242 VALUES
243 ( BOM_COMPONENT_OPERATIONS_S.NEXTVAL
244 , p_bom_comp_ops_rec.additional_operation_seq_num
245 , p_bom_comp_ops_unexp_rec.additional_operation_seq_id
246 , p_bom_comp_ops_unexp_rec.bill_sequence_id
247 , SYSDATE
248 , Bom_globals.Get_User_Id
249 , SYSDATE
250 , Bom_Globals.Get_User_Id
251 , Bom_Globals.Get_User_Id
252 , p_bom_comp_ops_unexp_rec.component_sequence_id
253 , p_bom_comp_ops_rec.attribute_category
254 , p_bom_comp_ops_rec.attribute1
255 , p_bom_comp_ops_rec.attribute2
256 , p_bom_comp_ops_rec.attribute3
257 , p_bom_comp_ops_rec.attribute4
258 , p_bom_comp_ops_rec.attribute5
259 , p_bom_comp_ops_rec.attribute6
260 , p_bom_comp_ops_rec.attribute7
261 , p_bom_comp_ops_rec.attribute8
262 , p_bom_comp_ops_rec.attribute9
263 , p_bom_comp_ops_rec.attribute10
264 , p_bom_comp_ops_rec.attribute11
265 , p_bom_comp_ops_rec.attribute12
266 , p_bom_comp_ops_rec.attribute13
267 , p_bom_comp_ops_rec.attribute14
268 , p_bom_comp_ops_rec.attribute15
269 , Fnd_Global.Conc_Request_Id
270 , Fnd_Global.Conc_Program_Id
271 , Fnd_Global.Prog_Appl_Id
272 , SYSDATE
273 , l_src_comp_seq_id --bug 7713832
274 );
275
276 --x_return_status := FND_API.G_RET_STS_SUCCESS;
277 IF Bom_Globals.Get_Caller_Type <> 'MIGRATION' THEN --Bug 7713832
278 BOMPCMBM.Insert_Related_Comp_Ops(p_component_sequence_id => p_bom_comp_ops_unexp_rec.component_sequence_id
279 , p_operation_seq_num => p_bom_comp_ops_rec.additional_operation_seq_num
280 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
281 , x_Return_Status => l_return_status);
282 END IF;
283 x_return_status := l_return_status;
284
285 EXCEPTION
286
287 WHEN OTHERS THEN
288 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
289 THEN
290 l_err_text := G_PKG_NAME ||
291 'Utility (Component Operations Insert)' ||
292 SUBSTR(SQLERRM, 1, 100);
293 Error_Handler.Add_Error_Token
294 ( p_Message_Name => NULL
295 , p_Message_text => l_err_text
296 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
297 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
298 );
299 END IF;
300 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
302
303 END Insert_Row;
304
305
306 /********************************************************************
307 *
308 * Procedure : Delete_Row
309 * Parameters IN : Primary Key of Component Operation Table
310 * Parameters OUT: Return_Status - Indicating success or faliure
311 * Mesg_Token_Tbl - Filled with any errors or warnings
312 * Purpose : Will delete a component operation record using the
313 * primary unique key.
314 ********************************************************************/
315 PROCEDURE Delete_Row
316 ( p_comp_operation_seq_id IN NUMBER
317 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
318 , x_return_status IN OUT NOCOPY VARCHAR2
319 )
320 IS
321 l_return_status VARCHAR2(200);
322 l_err_text VARCHAR2(255);
323 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
324 l_comp_seq_id NUMBER;
325 l_op_seq_num NUMBER;
326 BEGIN
327
328 l_return_status := FND_API.G_RET_STS_SUCCESS;
329
330 SELECT component_sequence_id, operation_seq_num
331 INTO l_comp_seq_id, l_op_seq_num
332 FROM BOM_COMPONENT_OPERATIONS
333 WHERE COMP_OPERATION_SEQ_ID = p_comp_operation_seq_id;
334
335 DELETE FROM BOM_COMPONENT_OPERATIONS
336 WHERE COMP_OPERATION_SEQ_ID = p_comp_operation_seq_id;
337
338 IF SQL%NOTFOUND THEN
339
340 Error_Handler.Add_Error_Token
341 ( p_Message_name => 'BOM_NOT_DELETE_ROW'
342 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
343 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
344 );
345
346 l_return_status := FND_API.G_RET_STS_ERROR;
347
348 ELSE
349 BOMPCMBM.Delete_Related_Comp_Ops(p_src_comp_seq_id => l_comp_seq_id,
350 p_operation_seq_num => l_op_seq_num,
354
351 x_return_status => l_return_status);
352
353 END IF;
355 x_return_status := l_return_status;
356
357 EXCEPTION
358
359 WHEN OTHERS THEN
360
361 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
362 THEN
363 l_err_text := G_PKG_NAME ||
364 'Utility (Component Operations Delete_Row)' ||
365 SUBSTR(SQLERRM, 1, 100);
366
367 Error_Handler.Add_Error_Token
368 ( p_Message_Name => NULL
369 , p_Message_Text => l_err_text
370 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
371 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
372 );
373 END IF;
374 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
375 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
376
377 END Delete_Row;
378
379 /********************************************************************
380 *
381 * Procedure : Query_Row
382 * Parameters IN : Component Operation primary key
383 * Parameters OUT: Component Operation Record of exposed colmuns
384 * Component Operation record of unexposed columns
385 * Return_Status - Indicating success or faliure
386 * Mesg_Token_Tbl - Filled with any errors or warnings
387 * Purpose : Complete Record will take the Database record and
388 * compare it with the user record and will complete
389 * the user record by filling in those values from the
390 * record that the user has left blank.
391 * Any user filled in columns will not be overwritten
392 * even if the values do not match.
393 ********************************************************************/
394 PROCEDURE Query_Row
395 ( p_component_sequence_id IN NUMBER
396 , p_additional_operation_seq_num IN NUMBER
397 , x_bom_comp_ops_rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type
398 , x_bom_comp_ops_Unexp_Rec IN OUT NOCOPY Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type
399 , x_return_status IN OUT NOCOPY VARCHAR2
400 )
401 IS
402 l_bom_comp_ops_rec Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type;
403 l_bom_comp_ops_unexp_rec Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type;
404 BEGIN
405
406 SELECT ROWID
407 , COMP_OPERATION_SEQ_ID
408 , OPERATION_SEQ_NUM
409 , OPERATION_SEQUENCE_ID
410 , COMPONENT_SEQUENCE_ID
411 , BILL_SEQUENCE_ID
412 , ATTRIBUTE_CATEGORY
413 , ATTRIBUTE1
414 , ATTRIBUTE2
415 , ATTRIBUTE3
416 , ATTRIBUTE4
417 , ATTRIBUTE5
418 , ATTRIBUTE6
419 , ATTRIBUTE7
420 , ATTRIBUTE8
421 , ATTRIBUTE9
422 , ATTRIBUTE10
423 , ATTRIBUTE11
424 , ATTRIBUTE12
425 , ATTRIBUTE13
426 , ATTRIBUTE14
427 , ATTRIBUTE15
428 INTO
429 l_bom_comp_ops_unexp_rec.rowid
430 , l_bom_comp_ops_unexp_rec.comp_operation_seq_id
431 , l_bom_comp_ops_rec.operation_sequence_number
432 , l_bom_comp_ops_unexp_rec.additional_operation_seq_id
433 , l_bom_comp_ops_unexp_rec.component_sequence_id
434 , l_bom_comp_ops_unexp_rec.bill_sequence_id
435 , l_bom_comp_ops_rec.attribute_category
436 , l_bom_comp_ops_rec.attribute1
437 , l_bom_comp_ops_rec.attribute2
438 , l_bom_comp_ops_rec.attribute3
439 , l_bom_comp_ops_rec.attribute4
440 , l_bom_comp_ops_rec.attribute5
441 , l_bom_comp_ops_rec.attribute6
442 , l_bom_comp_ops_rec.attribute7
443 , l_bom_comp_ops_rec.attribute8
444 , l_bom_comp_ops_rec.attribute9
445 , l_bom_comp_ops_rec.attribute10
446 , l_bom_comp_ops_rec.attribute11
447 , l_bom_comp_ops_rec.attribute12
448 , l_bom_comp_ops_rec.attribute13
449 , l_bom_comp_ops_rec.attribute14
450 , l_bom_comp_ops_rec.attribute15
451 FROM BOM_COMPONENT_OPERATIONS
452 WHERE COMPONENT_SEQUENCE_ID = p_component_sequence_id AND
453 OPERATION_SEQ_NUM = p_additional_operation_seq_num ;
454
455 x_bom_comp_ops_Rec := l_bom_comp_ops_rec;
456 x_bom_comp_ops_unexp_Rec := l_bom_comp_ops_unexp_rec;
457 x_return_status := Bom_Globals.G_RECORD_FOUND;
458
459 EXCEPTION
460
461 WHEN OTHERS THEN
462 x_bom_comp_ops_Rec := l_bom_comp_ops_rec;
463 x_bom_comp_ops_unexp_Rec := l_bom_comp_ops_unexp_rec;
464 x_return_status := Bom_Globals.G_RECORD_NOT_FOUND;
465
466 END Query_Row;
467
468 PROCEDURE Perform_Writes
469 ( p_bom_comp_ops_rec IN Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type
470 , p_bom_comp_ops_unexp_rec IN Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type
471 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
472 , x_Return_Status IN OUT NOCOPY VARCHAR2
473 )
474 IS
475 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
476 l_return_status VARCHAR2(1);
477 l_bom_comp_ops_rec Bom_Bo_Pub.Bom_Comp_Ops_Rec_Type;
478 l_bom_comp_ops_unexp_rec Bom_Bo_Pub.Bom_Comp_Ops_Unexp_Rec_Type;
479 BEGIN
480 l_bom_comp_ops_rec := p_bom_comp_ops_rec;
481 l_bom_comp_ops_unexp_rec := p_bom_comp_ops_unexp_rec;
482
483 IF l_bom_comp_ops_rec.transaction_type = Bom_Globals.G_OPR_CREATE
484 THEN
485 Insert_Row( p_bom_comp_ops_rec => l_bom_comp_ops_rec
486 , p_bom_comp_ops_Unexp_Rec => l_bom_comp_ops_unexp_rec
487 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
488 , x_return_status => l_return_status
489 );
493 , p_bom_comp_ops_Unexp_Rec => l_bom_comp_ops_unexp_rec
490 ELSIF l_bom_comp_ops_rec.transaction_type = Bom_Globals.G_OPR_UPDATE
491 THEN
492 Update_Row( p_bom_comp_ops_rec => l_bom_comp_ops_rec
494 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
495 , x_return_status => l_return_status
496 );
497
498 ELSIF l_bom_comp_ops_rec.transaction_type = Bom_Globals.G_OPR_DELETE
499 THEN
500 Delete_Row
501 ( p_comp_operation_seq_id => l_bom_comp_ops_unexp_rec.comp_operation_seq_id
502 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
503 , x_return_status => l_return_status
504 );
505
506 END IF;
507
508 x_return_status := l_return_status;
509 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
510
511 END Perform_Writes;
512
513
514 END BOM_Comp_Operation_Util;