[Home] [Help]
PACKAGE BODY: APPS.BOM_OP_RES_UTIL
Source
1 PACKAGE BODY BOM_Op_Res_UTIL AS
2 /* $Header: BOMURESB.pls 120.3.12000000.2 2007/09/13 07:09:31 pgandhik ship $ */
3
4 /****************************************************************************
5 --
6 -- Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA
7 -- All rights reserved.
8 --
9 -- FILENAME
10 --
11 -- BOMURESS.pls
12 --
13 -- DESCRIPTION
14 --
15 -- Body of package BOM_Op_Res_UTIL
16 --
17 -- NOTES
18 --
19 -- HISTORY
20 --
21 -- 18-AUG-00 Masanori Kimizuka Initial Creation
22 --
23 ****************************************************************************/
24
25 G_Pkg_Name CONSTANT VARCHAR2(30) := 'BOM_Op_Res_UTIL' ;
26
27
28
29 /*****************************************************************
30 * Procedure : Query_Row
31 * Parameters IN : Rtg Operation Resource Key
32 * Parameters OUT : Rtg Operation Resource Exposed column Record
33 * Rtg Operation Resource Unexposed column Record
34 * Returns : None
35 * Purpose : Convert Record and Call Query_Row used by ECO.
36 * Query will query the database record and seperate
37 * the unexposed and exposed attributes before returning
38 * the records.
39 ********************************************************************/
40 PROCEDURE Query_Row
41 ( p_resource_sequence_number IN NUMBER
42 , p_operation_sequence_id IN NUMBER
43 , p_acd_type IN NUMBER
44 , p_mesg_token_tbl IN Error_Handler.Mesg_Token_Tbl_Type
45 , x_op_resource_rec IN OUT NOCOPY Bom_Rtg_Pub.Op_Resource_Rec_Type
46 , x_op_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Op_Res_Unexposed_Rec_Type
47 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
48 , x_return_status IN OUT NOCOPY VARCHAR2
49 )
50
51 IS
52
53 l_rev_op_resource_rec Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type ;
54 l_rev_op_res_unexp_rec Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type;
55
56 BEGIN
57
58 x_mesg_token_tbl := p_mesg_token_tbl;
59
60 BOM_Op_Res_UTIL.Query_Row
61 ( p_resource_sequence_number => p_resource_sequence_number
62 , p_operation_sequence_id => p_operation_sequence_id
63 , p_acd_type => p_acd_type
64 , p_mesg_token_tbl => p_mesg_Token_tbl
65 , x_rev_op_resource_rec => l_rev_op_resource_rec
66 , x_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
67 , x_mesg_token_tbl => x_mesg_token_tbl
68 , x_return_status => x_return_status
69 ) ;
70
71 -- Convert the ECO record to Routing Record
72
73 Bom_Rtg_Pub.Convert_EcoRes_To_RtgRes
74 ( p_rev_op_resource_rec => l_rev_op_resource_rec
75 , p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
76 , x_rtg_op_resource_rec => x_op_resource_rec
77 , x_rtg_op_res_unexp_rec => x_op_res_unexp_rec
78 ) ;
79
80
81
82 END Query_Row;
83
84
85 /*****************************************************************
86 * Procedure : Query_Row used by ECO BO and internally called by RTG BO
87 * Parameters IN : Revised Operation Resource Key
88 * Parameters OUT: Revised Operation Resource Exposed column Record
89 * Revised Operation Resource Unexposed column Record
90 * Returns : None
91 * Purpose : Revised Operation Resource Query Row
92 * will query the database record and seperate
93 * the unexposed and exposed attributes before returning
94 * the records.
95 ********************************************************************/
96 PROCEDURE Query_Row
97 ( p_resource_sequence_number IN NUMBER
98 , p_operation_sequence_id IN NUMBER
99 , p_acd_type IN NUMBER
100 , p_mesg_token_tbl IN Error_Handler.Mesg_Token_Tbl_Type
101 , x_rev_op_resource_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
102 , x_rev_op_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
103 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
104 , x_return_status IN OUT NOCOPY VARCHAR2
105 )
106 IS
107
108
109 /* Define Variable */
110 l_rev_op_resource_rec Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type ;
111 l_rev_op_res_unexp_rec Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type;
112 l_err_text VARCHAR2(2000) ;
113 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type ;
114 l_bo_id VARCHAR2(3) ;
115 l_operation_sequence_id NUMBER := p_operation_sequence_id ;
116
117 /* Define Cursor */
118 Cursor op_res_cur( p_resource_sequence_number NUMBER
119 , p_operation_sequence_id NUMBER
120 , l_bo_id VARCHAR2
121 , p_acd_type NUMBER )
122 IS
123
124 SELECT * FROM BOM_OPERATION_RESOURCES
125 WHERE (( l_bo_id = BOM_Rtg_Globals.G_ECO_BO
126 AND NVL(ACD_TYPE, FND_API.G_MISS_NUM)
127 = NVL(p_acd_type,FND_API.G_MISS_NUM))
128 OR
129 ( l_bo_id = BOM_Rtg_Globals.G_RTG_BO
130 /* AND ACD_TYPE IS NULL
131 Bug 6378493 Commenting out the condition on the parameter ACD_type */
132 )
133 )
134 AND RESOURCE_SEQ_NUM = p_resource_sequence_number
135 AND OPERATION_SEQUENCE_ID = p_operation_sequence_id
136 ;
137
138 op_res_rec BOM_OPERATION_RESOURCES%ROWTYPE ;
139
140
141 BEGIN
142
143 x_mesg_token_tbl := p_mesg_token_tbl;
144 l_bo_id := BOM_Rtg_Globals.Get_Bo_Identifier ;
145
146 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
147 ('Querying an operation resource record : Res Seq Number ' || to_char(p_resource_sequence_number) || '. . . ' ) ;
148 END IF ;
149
150 -- Calling from revised operation resource with
151 -- transaction type : Create, Acd_Type: Change or Disable
152 -- to get the original value and defaulting
153 IF l_bo_id = BOM_Rtg_Globals.G_ECO_BO AND
154 p_acd_type = FND_API.G_MISS_NUM
155 THEN
156 l_bo_id := BOM_Rtg_Globals.G_RTG_BO ;
157
158 begin
159 SELECT old_operation_sequence_id
160 INTO l_operation_sequence_id
161 FROM BOM_OPERATION_SEQUENCES
162 WHERE operation_sequence_id = p_operation_sequence_id ;
163 end ;
164
165 END IF ;
166
167 IF NOT op_res_cur%ISOPEN
168 THEN
169 OPEN op_res_cur( p_resource_sequence_number
170 , l_operation_sequence_id
171 , l_bo_id
172 , p_acd_type ) ;
173 END IF ;
174
175 FETCH op_res_cur INTO op_res_rec ;
176
177 IF op_res_cur%FOUND
178 THEN
179
180
181 -- Unexposed Column
182 l_rev_op_res_unexp_rec.Operation_Sequence_Id := op_res_rec.OPERATION_SEQUENCE_ID ;
183 l_rev_op_resource_rec.Substitute_Group_Number := op_res_rec.SUBSTITUTE_GROUP_NUM ;
184 l_rev_op_res_unexp_rec.Substitute_Group_Number := l_rev_op_resource_rec.Substitute_Group_Number;
185 l_rev_op_res_unexp_rec.Resource_Id := op_res_rec.RESOURCE_ID ;
186 l_rev_op_res_unexp_rec.Activity_Id := op_res_rec.ACTIVITY_ID ;
187 l_rev_op_res_unexp_rec.Setup_Id := op_res_rec.SETUP_ID ;
188
189 -- Exposed Column
190 l_rev_op_resource_rec.Eco_Name := op_res_rec.CHANGE_NOTICE ;
191 l_rev_op_resource_rec.ACD_Type := op_res_rec.ACD_TYPE ;
192 l_rev_op_resource_rec.Resource_Sequence_Number := op_res_rec.RESOURCE_SEQ_NUM ;
193 l_rev_op_resource_rec.Standard_Rate_Flag := op_res_rec.STANDARD_RATE_FLAG ;
194 l_rev_op_resource_rec.Assigned_Units := op_res_rec.Assigned_Units ;
195 l_rev_op_resource_rec.Usage_Rate_Or_Amount := op_res_rec.USAGE_RATE_OR_AMOUNT ;
196 l_rev_op_resource_rec.Usage_Rate_Or_Amount_Inverse := op_res_rec.USAGE_RATE_OR_AMOUNT_INVERSE ;
197 l_rev_op_resource_rec.Basis_Type := op_res_rec.BASIS_TYPE ;
198 l_rev_op_resource_rec.Schedule_Flag := op_res_rec.SCHEDULE_FLAG ;
199 l_rev_op_resource_rec.Resource_Offset_Percent := op_res_rec.RESOURCE_OFFSET_PERCENT ;
200 l_rev_op_resource_rec.Autocharge_Type := op_res_rec.AUTOCHARGE_TYPE ;
201 l_rev_op_resource_rec.Schedule_Sequence_Number := op_res_rec.SCHEDULE_SEQ_NUM ;
202 l_rev_op_resource_rec.Principle_Flag := op_res_rec.PRINCIPLE_FLAG ;
203 l_rev_op_resource_rec.Attribute_category := op_res_rec.ATTRIBUTE_CATEGORY ;
204 l_rev_op_resource_rec.Attribute1 := op_res_rec.ATTRIBUTE1 ;
205 l_rev_op_resource_rec.Attribute2 := op_res_rec.ATTRIBUTE2 ;
206 l_rev_op_resource_rec.Attribute3 := op_res_rec.ATTRIBUTE3 ;
207 l_rev_op_resource_rec.Attribute4 := op_res_rec.ATTRIBUTE4 ;
208 l_rev_op_resource_rec.Attribute5 := op_res_rec.ATTRIBUTE5 ;
209 l_rev_op_resource_rec.Attribute6 := op_res_rec.ATTRIBUTE6 ;
210 l_rev_op_resource_rec.Attribute7 := op_res_rec.ATTRIBUTE7 ;
211 l_rev_op_resource_rec.Attribute8 := op_res_rec.ATTRIBUTE8 ;
212 l_rev_op_resource_rec.Attribute9 := op_res_rec.ATTRIBUTE9 ;
213 l_rev_op_resource_rec.Attribute10 := op_res_rec.ATTRIBUTE10 ;
214 l_rev_op_resource_rec.Attribute11 := op_res_rec.ATTRIBUTE11 ;
215 l_rev_op_resource_rec.Attribute12 := op_res_rec.ATTRIBUTE12 ;
216 l_rev_op_resource_rec.Attribute13 := op_res_rec.ATTRIBUTE13 ;
217 l_rev_op_resource_rec.Attribute14 := op_res_rec.ATTRIBUTE14 ;
218 l_rev_op_resource_rec.Attribute15 := op_res_rec.ATTRIBUTE15 ;
219 l_rev_op_resource_rec.Original_System_Reference := op_res_rec.ORIGINAL_SYSTEM_REFERENCE ;
220
221 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Finished querying and assigning operation record . . .') ;
222 END IF ;
223
224 x_return_status := BOM_Rtg_Globals.G_RECORD_FOUND ;
225 x_rev_op_resource_rec := l_rev_op_resource_rec ;
226 x_rev_op_res_unexp_rec := l_rev_op_res_unexp_rec ;
227
228 ELSE
229 x_return_status := BOM_Rtg_Globals.G_RECORD_NOT_FOUND ;
230 x_rev_op_resource_rec := l_rev_op_resource_rec ;
231 x_rev_op_res_unexp_rec := l_rev_op_res_unexp_rec ;
232
233 END IF ;
234
235 IF op_res_cur%ISOPEN
236 THEN
237 CLOSE op_res_cur ;
238 END IF ;
239
240 EXCEPTION
241 WHEN OTHERS THEN
242 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
243 ('Some unknown error in Query Row. . .' || SQLERRM );
244 END IF ;
245
246 l_err_text := G_PKG_NAME || ' Utility (Op Resource Query Row) '
247 || substrb(SQLERRM,1,200);
248
249 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
250
251 Error_Handler.Add_Error_Token
252 ( p_message_name => NULL
253 , p_message_text => l_err_text
254 , p_mesg_token_tbl => l_mesg_token_tbl
255 , x_mesg_token_tbl => l_mesg_token_tbl
256 ) ;
257
258 -- Return the status and message table.
259 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
260 x_mesg_token_tbl := l_mesg_token_tbl ;
261
262
263 END Query_Row;
264
265
266 /*********************************************************************
267 * Procedure : Perform_Writes used by RTG BO
268 * Parameters IN : Operation Resource exposed column record
269 * Operation Resource unexposed column record
270 * Parameters OUT: Return Status
271 * Message Token Table
272 * Purpose : Convert Rtg Op Resource to ECO Op Resource and
273 * Call Check_Entity for ECO BO.
274 * Perform Writes is the only exposed procedure when the
275 * user has to perform any insert/update/deletes to the
276 * Operation Resources table.
277 *********************************************************************/
278
279 PROCEDURE Perform_Writes
280 ( p_op_resource_rec IN Bom_Rtg_Pub.Op_Resource_Rec_Type
281 , p_op_res_unexp_rec IN Bom_Rtg_Pub.Op_Res_Unexposed_Rec_Type
282 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
283 , x_return_status IN OUT NOCOPY VARCHAR2
284 )
285 IS
286 l_rev_op_resource_rec Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type ;
287 l_rev_op_res_unexp_rec Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type ;
288
289 BEGIN
290 -- Convert Routing Operation to Common Operation
291 Bom_Rtg_Pub.Convert_RtgRes_To_EcoRes
292 ( p_rtg_op_resource_rec => p_op_resource_rec
293 , p_rtg_op_res_unexp_rec => p_op_res_unexp_rec
294 , x_rev_op_resource_rec => l_rev_op_resource_rec
295 , x_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
296 ) ;
297
298 -- Call Perform Writes Procedure
299 Bom_Op_Res_UTIL.Perform_Writes
300 ( p_rev_op_resource_rec => l_rev_op_resource_rec
301 , p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
302 , p_control_rec => Bom_Rtg_Pub.G_DEFAULT_CONTROL_REC
303 , x_mesg_token_tbl => x_mesg_token_tbl
304 , x_return_status => x_return_status
305 ) ;
306
307 END Perform_Writes ;
308
309
310
311 /*********************************************************************
312 * Procedure : Perform_Writes used by ECO BO and internally called by RTG BO
313 * Parameters IN : Revised Op Resource exposed column record
314 * Revised Op Resource unexposed column record
315 * Parameters OUT: Return Status
316 * Message Token Table
317 * Purpose : Perform Writes is the only exposed procedure when the
318 * user has to perform any insert/update/deletes to the
319 * Operation Resources table.
320 *********************************************************************/
321 PROCEDURE Perform_Writes
322 ( p_rev_op_resource_rec IN Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
323 , p_rev_op_res_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
324 , p_control_rec IN Bom_Rtg_Pub.Control_Rec_Type
325 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
326 , x_return_status IN OUT NOCOPY VARCHAR2
327 )
328 IS
329
330 l_rev_op_resource_rec Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type ;
331 l_rev_op_res_unexp_rec Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type ;
332
333
334 -- Error Handlig Variables
335 l_return_status VARCHAR2(1);
336 l_err_text VARCHAR2(2000) ;
337 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type;
338
339
340 BEGIN
341 --
342 -- Initialize Record and Status
343 --
344 l_rev_op_resource_rec := p_rev_op_resource_rec ;
345 l_rev_op_res_unexp_rec := p_rev_op_res_unexp_rec ;
346 l_return_status := FND_API.G_RET_STS_SUCCESS ;
347 x_return_status := FND_API.G_RET_STS_SUCCESS ;
348
349 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
350 ('Performing Database Writes . . .') ;
351 END IF ;
352
353
354 IF l_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_CREATE THEN
355 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
356 ('Operatin Resource : Executing Insert Row. . . ') ;
357 END IF;
361 , p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
358
359 Insert_Row
360 ( p_rev_op_resource_rec => l_rev_op_resource_rec
362 , x_return_status => l_return_status
363 , x_mesg_token_tbl => l_mesg_token_tbl
364 ) ;
365
366
367 ELSIF l_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_UPDATE
368 THEN
369 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
370 ('Operatin Resource : Executing Update Row. . . ') ;
371 END IF ;
372
373 Update_Row
374 ( p_rev_op_resource_rec => l_rev_op_resource_rec
375 , p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
376 , x_return_status => l_return_status
377 , x_mesg_token_tbl => l_mesg_token_tbl
378 ) ;
379
380 ELSIF l_rev_op_resource_rec.transaction_type = BOM_Rtg_Globals.G_OPR_DELETE
381 THEN
382
383 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
384 ('Operatin Resource : Executing Delete Row. . . ') ;
385 END IF ;
386
387 Delete_Row
388 ( p_rev_op_resource_rec => l_rev_op_resource_rec
389 , p_rev_op_res_unexp_rec => l_rev_op_res_unexp_rec
390 , x_return_status => l_return_status
391 , x_mesg_token_tbl => l_mesg_token_tbl
392 ) ;
393
394 END IF ;
395
396 --
397 -- Return Status
398 --
399 x_return_status := l_return_status ;
400 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl ;
401
402 EXCEPTION
403 WHEN OTHERS THEN
404 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
405 ('Some unknown error in Perform Writes . . .' || SQLERRM );
406 END IF ;
407
408 l_err_text := G_PKG_NAME || ' Utility (Perform Writes) '
409 || substrb(SQLERRM,1,200);
410
411 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
412
413 Error_Handler.Add_Error_Token
414 ( p_message_name => NULL
415 , p_message_text => l_err_text
416 , p_mesg_token_tbl => l_mesg_token_tbl
417 , x_mesg_token_tbl => l_mesg_token_tbl
418 ) ;
419
420 -- Return the status and message table.
421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
422 x_mesg_token_tbl := l_mesg_token_tbl ;
423
424 END Perform_Writes;
425
426
427 /*****************************************************************************
428 * Procedure : Insert_Row
429 * Parameters IN : Revised Operation Resource exposed column record
430 * Revised Operation Resource unexposed column record
431 * Parameters OUT: Return Status
432 * Message Token Table
433 * Purpose : This procedure will insert a record in the Operation Resource
434 * table; BOM_OPERATION_RESOURCES
435 *
436 *****************************************************************************/
437 PROCEDURE Insert_Row
438 ( p_rev_op_resource_rec IN Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
439 , p_rev_op_res_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
440 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
441 , x_return_status IN OUT NOCOPY VARCHAR2
442 )
443 IS
444
445 -- Error Handlig Variables
446 l_err_text VARCHAR2(2000) ;
447 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
448
449 BEGIN
450
451 x_return_status := FND_API.G_RET_STS_SUCCESS ;
452
453 --bug:3254815 Update request id, prog id, prog appl id and prog update date.
454 INSERT INTO BOM_OPERATION_RESOURCES
455 (
456 operation_sequence_id
457 , resource_seq_num
458 , resource_id
459 , activity_id
460 , standard_rate_flag
461 , assigned_units
462 , usage_rate_or_amount
463 , usage_rate_or_amount_inverse
464 , basis_type
465 , schedule_flag
466 , last_update_date
467 , last_updated_by
468 , creation_date
469 , created_by
470 , last_update_login
471 , resource_offset_percent
472 , autocharge_type
473 , attribute_category
474 , attribute1
475 , attribute2
476 , attribute3
477 , attribute4
478 , attribute5
479 , attribute6
480 , attribute7
481 , attribute8
482 , attribute9
483 , attribute10
484 , attribute11
485 , attribute12
486 , attribute13
487 , attribute14
488 , attribute15
489 , request_id
490 , program_application_id
491 , program_id
492 , program_update_date
493 , schedule_seq_num
494 , substitute_group_num
495 , principle_flag
496 , change_notice
497 , acd_type
498 , original_system_reference
499 , setup_id
500 )
504 , p_rev_op_res_unexp_rec.resource_id
501 VALUES (
502 p_rev_op_res_unexp_rec.operation_sequence_id
503 , p_rev_op_resource_rec.resource_sequence_number
505 , p_rev_op_res_unexp_rec.activity_id
506 , p_rev_op_resource_rec.standard_rate_flag
507 , p_rev_op_resource_rec.assigned_units
508 , p_rev_op_resource_rec.usage_rate_or_amount
509 , p_rev_op_resource_rec.usage_rate_or_amount_inverse
510 , p_rev_op_resource_rec.basis_type
511 , p_rev_op_resource_rec.schedule_flag
512 , SYSDATE -- Last Update Date
513 , BOM_Rtg_Globals.Get_User_Id -- Last Updated By
514 , SYSDATE -- Creation Date
515 , BOM_Rtg_Globals.Get_User_Id -- Created By
516 , BOM_Rtg_Globals.Get_Login_Id -- Last Update Login
517 , p_rev_op_resource_rec.resource_offset_percent
518 , p_rev_op_resource_rec.autocharge_type
519 , p_rev_op_resource_rec.attribute_category
520 , p_rev_op_resource_rec.attribute1
521 , p_rev_op_resource_rec.attribute2
522 , p_rev_op_resource_rec.attribute3
523 , p_rev_op_resource_rec.attribute4
524 , p_rev_op_resource_rec.attribute5
525 , p_rev_op_resource_rec.attribute6
526 , p_rev_op_resource_rec.attribute7
527 , p_rev_op_resource_rec.attribute8
528 , p_rev_op_resource_rec.attribute9
529 , p_rev_op_resource_rec.attribute10
530 , p_rev_op_resource_rec.attribute11
531 , p_rev_op_resource_rec.attribute12
532 , p_rev_op_resource_rec.attribute13
533 , p_rev_op_resource_rec.attribute14
534 , p_rev_op_resource_rec.attribute15
535 , Fnd_Global.Conc_Request_Id -- Request Id
536 , BOM_Rtg_Globals.Get_Prog_AppId -- Application Id
537 , BOM_Rtg_Globals.Get_Prog_Id -- Program Id
538 , SYSDATE -- program_update_date
539 , p_rev_op_resource_rec.schedule_sequence_number
540 , nvl(p_rev_op_resource_rec.substitute_group_number, p_rev_op_res_unexp_rec.substitute_group_number)
541 , p_rev_op_resource_rec.principle_flag
542 , p_rev_op_resource_rec.eco_name
543 , p_rev_op_resource_rec.acd_type
544 , p_rev_op_resource_rec.original_system_reference
545 , p_rev_op_res_unexp_rec.setup_id
546 ) ;
547
548
549 EXCEPTION
550
551 WHEN OTHERS THEN
552 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
553 ('Unexpected Error occured in Insert . . .' || SQLERRM);
554 END IF;
555
556 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
557 THEN
558 l_err_text := G_PKG_NAME || ' : Utility (Op Resource Insert) ' ||
559 SUBSTR(SQLERRM, 1, 200);
560
561 Error_Handler.Add_Error_Token
562 ( p_message_name => NULL
563 , p_message_text => l_err_text
564 , p_mesg_token_tbl => l_mesg_token_tbl
565 , x_mesg_token_tbl => l_mesg_token_tbl
566 ) ;
567 END IF ;
568
569 -- Return the status and message table.
570 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
571 x_mesg_token_tbl := l_mesg_token_tbl ;
572
573 END Insert_Row ;
574
575
576 /***************************************************************************
577 * Procedure : Update_Row
578 * Parameters IN : Revised Operation Resource exposed column record
579 * Revised Operation Resource unexposed column record
580 * Parameters OUT: Return Status
581 * Message Token Table
582 * Purpose : Update_Row procedure will update the production record with
583 * the user given values. Any errors will be returned by filling
584 * the Mesg_Token_Tbl and setting the return_status.
585 ****************************************************************************/
586 PROCEDURE Update_Row
587 ( p_rev_op_resource_rec IN Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
588 , p_rev_op_res_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
589 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
590 , x_return_status IN OUT NOCOPY VARCHAR2
591 )
592 IS
593
594 -- Error Handlig Variables
595 l_err_text VARCHAR2(2000) ;
596 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
597
598 BEGIN
599
600 x_return_status := FND_API.G_RET_STS_SUCCESS ;
601
602 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Performing update operation . . .') ;
603 END IF ;
604
605 UPDATE BOM_OPERATION_RESOURCES
606 SET
607 resource_id = p_rev_op_res_unexp_rec.resource_id
608 , activity_id = p_rev_op_res_unexp_rec.activity_id
609 , standard_rate_flag = p_rev_op_resource_rec.standard_rate_flag
610 , assigned_units = p_rev_op_resource_rec.assigned_units
611 , usage_rate_or_amount = p_rev_op_resource_rec.usage_rate_or_amount
612 , usage_rate_or_amount_inverse = p_rev_op_resource_rec.usage_rate_or_amount_inverse
613 , basis_type = p_rev_op_resource_rec.basis_type
617 , last_update_login = BOM_Rtg_Globals.Get_Login_Id /* Last Update Login */
614 , schedule_flag = p_rev_op_resource_rec.schedule_flag
615 , last_update_date = SYSDATE /* Last Update Date */
616 , last_updated_by = BOM_Rtg_Globals.Get_User_Id /* Last Updated By */
618 , resource_offset_percent = p_rev_op_resource_rec.resource_offset_percent
619 , autocharge_type = p_rev_op_resource_rec.autocharge_type
620 , attribute_category = p_rev_op_resource_rec.attribute_category
621 , attribute1 = p_rev_op_resource_rec.attribute1
622 , attribute2 = p_rev_op_resource_rec.attribute2
623 , attribute3 = p_rev_op_resource_rec.attribute3
624 , attribute4 = p_rev_op_resource_rec.attribute4
625 , attribute5 = p_rev_op_resource_rec.attribute5
626 , attribute6 = p_rev_op_resource_rec.attribute6
627 , attribute7 = p_rev_op_resource_rec.attribute7
628 , attribute8 = p_rev_op_resource_rec.attribute8
629 , attribute9 = p_rev_op_resource_rec.attribute9
630 , attribute10 = p_rev_op_resource_rec.attribute10
631 , attribute11 = p_rev_op_resource_rec.attribute11
632 , attribute12 = p_rev_op_resource_rec.attribute12
633 , attribute13 = p_rev_op_resource_rec.attribute13
634 , attribute14 = p_rev_op_resource_rec.attribute14
635 , attribute15 = p_rev_op_resource_rec.attribute15
636 , program_application_id = BOM_Rtg_Globals.Get_Prog_AppId /* Application Id */
637 , program_id = BOM_Rtg_Globals.Get_Prog_Id /* Program Id */
638 , program_update_date = SYSDATE /* program_update_date */
639 , schedule_seq_num = p_rev_op_resource_rec.schedule_sequence_number
640 , substitute_group_num = nvl(p_rev_op_resource_rec.substitute_group_number, p_rev_op_res_unexp_rec.substitute_group_number)
641 , principle_flag = p_rev_op_resource_rec.principle_flag
642 , original_system_reference = p_rev_op_resource_rec.original_system_reference
643 , setup_id = p_rev_op_res_unexp_rec.setup_id
644 , request_id = Fnd_Global.Conc_Request_Id
645 WHERE operation_sequence_id = p_rev_op_res_unexp_rec.operation_sequence_id
646 AND resource_seq_num = p_rev_op_resource_rec.resource_sequence_number
647 AND NVL(acd_type, 0) = NVL(p_rev_op_resource_rec.acd_type,0) ;
648
649
650
651 EXCEPTION
652 WHEN OTHERS THEN
653 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
654 ('Unexpected Error occured in Update . . .' || SQLERRM);
655 END IF;
656
657 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
658 THEN
659 l_err_text := G_PKG_NAME || ' : Utility (Op Resource Update) ' ||
660 SUBSTR(SQLERRM, 1, 200);
661 Error_Handler.Add_Error_Token
662 ( p_message_name => NULL
663 , p_message_text => l_err_text
664 , p_mesg_token_tbl => l_mesg_token_tbl
665 , x_mesg_token_tbl => l_mesg_token_tbl
666 ) ;
667 END IF ;
668
669 -- Return the status and message table.
670 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
671 x_mesg_token_tbl := l_mesg_token_tbl ;
672
673 END Update_Row ;
674
675
676
677 /********************************************************************
678 * Procedure : Delete_Row
679 * Parameters IN : Revised Operation Resource exposed column record
680 * Revised Operation Resource unexposed column record
681 * Parameters OUT: Return Status
682 * Message Token Table
683 * Purpose : Delete_Row procedure will delete the production record with
684 * the user given values. Any errors will be returned by filling
685 * the Mesg_Token_Tbl and setting the return_status.
686 *
687 *********************************************************************/
688 PROCEDURE Delete_Row
689 ( p_rev_op_resource_rec IN Bom_Rtg_Pub.Rev_Op_Resource_Rec_Type
690 , p_rev_op_res_unexp_rec IN Bom_Rtg_Pub.Rev_Op_Res_Unexposed_Rec_Type
691 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
692 , x_return_status IN OUT NOCOPY VARCHAR2
693 )
694 IS
695
696 -- Error Handlig Variables
697 l_err_text VARCHAR2(2000) ;
698 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
699 l_token_tbl Error_Handler.Token_Tbl_Type;
700
701 BEGIN
702
703 x_return_status := FND_API.G_RET_STS_SUCCESS ;
704
705
706 DELETE FROM BOM_OPERATION_RESOURCES
707 WHERE operation_sequence_id = p_rev_op_res_unexp_rec.operation_sequence_id
708 AND resource_seq_num = p_rev_op_resource_rec.resource_sequence_number
709 AND NVL(acd_type, 1) = NVL(p_rev_op_resource_rec.acd_type,1) ;
710
711
712
713 /******************************************************************
714 -- Also delete substitute resources
715 -- by first logging a warning notifying the user of the cascaded
716 -- Delete.
720 WHERE NOT EXISTS ( SELECT 'AnOther Res not exist'
717 *******************************************************************/
718
719 DELETE FROM BOM_SUB_OPERATION_RESOURCES sor
721 FROM BOM_OPERATION_RESOURCES bor
722 WHERE bor.substitute_group_num = sor.substitute_group_num
723 AND bor.operation_sequence_id = sor.operation_sequence_id
724 )
725 AND sor.substitute_group_num = nvl(p_rev_op_resource_rec.substitute_group_number, p_rev_op_res_unexp_rec.substitute_group_number)
726 AND sor.operation_sequence_id = p_rev_op_res_unexp_rec.operation_sequence_id ;
727
728 IF SQL%FOUND THEN
729 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_SUCCESS)
730 -- This is a warning.
731 THEN
732 l_token_tbl(1).token_name := 'RES_SEQ_NUMBER';
733 l_token_tbl(1).token_value := p_rev_op_resource_rec.resource_sequence_number ;
734
735 Error_Handler.Add_Error_Token
736 ( p_Message_Name => 'BOM_RES_DELETE_SUB_RES'
737 , p_Mesg_Token_Tbl => l_Mesg_Token_Tbl
738 , x_Mesg_Token_Tbl => l_Mesg_Token_Tbl
739 , p_Message_Type => 'W'
740 , p_token_tbl => l_token_tbl
741 ) ;
742 END IF;
743
744 END IF ;
745
746 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
747
748 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Finished deleting revised operation record . . .') ;
749 END IF ;
750
751
752 EXCEPTION
753 WHEN OTHERS THEN
754 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
755 ('Unexpected Error occured in Delete . . .' || SQLERRM);
756 END IF;
757
758 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
759 THEN
760 l_err_text := G_PKG_NAME || ' : Utility (Op Resource Delete) ' ||
761 SUBSTR(SQLERRM, 1, 200);
762 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
763
764 Error_Handler.Add_Error_Token
765 ( p_message_name => NULL
766 , p_message_text => l_err_text
767 , p_mesg_token_tbl => l_mesg_token_tbl
768 , x_mesg_token_tbl => l_mesg_token_tbl
769 ) ;
770 END IF ;
771
772 -- Return the status and message table.
773 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
774 x_mesg_token_tbl := l_mesg_token_tbl ;
775
776 END Delete_Row ;
777
778
779 END BOM_Op_Res_UTIL ;