[Home] [Help]
PACKAGE BODY: APPS.BOM_SUB_OP_RES_UTIL
Source
1 PACKAGE BODY BOM_Sub_Op_Res_UTIL AS
2 /* $Header: BOMUSORB.pls 120.2 2005/12/08 22:02:09 bbpatel noship $ */
3
4 /****************************************************************************
5 --
6 -- Copyright (c) 2000 Oracle Corporation, Redwood Shores, CA, USA
7 -- All rights reserved.
8 --
9 -- FILENAME
10 --
11 -- BOMUSORB.pls
12 --
13 -- DESCRIPTION
14 --
15 -- Body of package BOM_Sub_Op_Res_UTIL
16 --
17 -- NOTES
18 --
19 -- HISTORY
20 --
21 -- 22-AUG-00 Masanori Kimizuka Initial Creation
22 -- 08-DEC-2005 Bhavnesh Patel 4689856:Added basis type column to identify
23 -- a sub resource
24 ****************************************************************************/
25
26 G_Pkg_Name CONSTANT VARCHAR2(30) := 'BOM_Sub_Op_Res_UTIL' ;
27
28 /*****************************************************************
29 * Procedure : Query_Row
30 * Parameters IN : Sub Operation Resource Key
31 * Parameters out: Sub Operation Resource Exposed column Record
32 * Sub Operation Resource Unexposed column Record
33 * Returns : None
34 * Purpose : Convert Record and Call Query_Row used by ECO.
35 * Query will query the database record and seperate
36 * the unexposed and exposed attributes before returning
37 * the records.
38 ********************************************************************/
39 PROCEDURE Query_Row
40 ( p_resource_id IN NUMBER
41 , p_substitute_group_number IN NUMBER
42 , p_operation_sequence_id IN NUMBER
43 , p_acd_type IN NUMBER
44 , p_replacement_group_number IN NUMBER --bug 2489765
45 , p_basis_type IN NUMBER
46 , p_mesg_token_tbl IN Error_Handler.Mesg_Token_Tbl_Type
47 , x_sub_resource_rec IN OUT NOCOPY Bom_Rtg_Pub.Sub_Resource_Rec_Type
48 , x_sub_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Sub_Res_Unexposed_Rec_Type
49 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
50 , x_return_status IN OUT NOCOPY VARCHAR2
51 )
52
53 IS
54
55 l_rev_sub_resource_rec Bom_Rtg_Pub.Rev_Sub_Resource_Rec_Type ;
56 l_rev_sub_res_unexp_rec Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type;
57
58 BEGIN
59
60 x_mesg_token_tbl := p_mesg_token_tbl;
61
62 BOM_Sub_Op_Res_UTIL.Query_Row
63 ( p_resource_id => p_resource_id
64 , p_substitute_group_number => p_substitute_group_number
65 , p_operation_sequence_id => p_operation_sequence_id
66 , p_acd_type => p_acd_type
67 , p_replacement_group_number => p_replacement_group_number --bug 2489765
68 , p_basis_type => p_basis_type
69 , p_mesg_token_tbl => p_mesg_Token_tbl
70 , x_rev_sub_resource_rec => l_rev_sub_resource_rec
71 , x_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
72 , x_mesg_token_tbl => x_mesg_token_tbl
73 , x_return_status => x_return_status
74 ) ;
75
76 -- Convert the ECO record to Routing Record
77
78 Bom_Rtg_Pub.Convert_EcoSubRes_To_RtgSubRes
79 ( p_rev_sub_resource_rec => l_rev_sub_resource_rec
80 , p_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
81 , x_rtg_sub_resource_rec => x_sub_resource_rec
82 , x_rtg_sub_res_unexp_rec => x_sub_res_unexp_rec
83 ) ;
84
85
86
87 END Query_Row;
88
89
90 /*****************************************************************
91 * Procedure : Query_Row used by ECO BO and internally called by RTG BO
92 * Parameters IN : Revised Sub Operation Resource Key
93 * Parameters out: Revised Sub Operation Resource Exposed column Record
94 * Revised Sub Operation Resource Unexposed column Record
95 * Returns : None
96 * Purpose : Sub Revised Operation Resource Query Row
97 * will query the database record and seperate
98 * the unexposed and exposed attributes before returning
99 * the records.
100 ********************************************************************/
101 PROCEDURE Query_Row
102 ( p_resource_id IN NUMBER
103 , p_substitute_group_number IN NUMBER
104 , p_operation_sequence_id IN NUMBER
105 , p_acd_type IN NUMBER
106 , p_replacement_group_number IN NUMBER --bug 2489765
107 , p_basis_type IN NUMBER
108 , p_mesg_token_tbl IN Error_Handler.Mesg_Token_Tbl_Type
109 , x_rev_sub_resource_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Sub_Resource_Rec_Type
110 , x_rev_sub_res_unexp_rec IN OUT NOCOPY Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type
111 , x_mesg_token_tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
112 , x_return_status IN OUT NOCOPY VARCHAR2
113 )
114 IS
115
116
117 /* Define Variable */
118 l_rev_sub_resource_rec Bom_Rtg_Pub.Rev_Sub_Resource_Rec_Type ;
119 l_rev_sub_res_unexp_rec Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type;
120 l_err_text VARCHAR2(2000) ;
121 l_bo_id VARCHAR2(3) ;
122 l_mesg_token_tbl Error_Handler.Mesg_Token_Tbl_Type ;
123
124
125 /* Define Cursor */
126 Cursor sub_res_csr ( p_sub_resource_id NUMBER
127 , p_substiute_group_number NUMBER
128 , p_operation_sequence_id NUMBER
129 , l_bo_id VARCHAR2
130 , p_acd_type NUMBER
131 , p_replacement_group_number NUMBER -- bug 2489765
132 , p_basis_type NUMBER
133 )
134 IS
135
136 SELECT * FROM BOM_SUB_OPERATION_RESOURCES
137 WHERE (( l_bo_id = BOM_Rtg_Globals.G_ECO_BO
138 AND ACD_TYPE = p_acd_type )
139 OR
140 ( l_bo_id = BOM_Rtg_Globals.G_RTG_BO
141 AND ACD_TYPE IS NULL )
142 )
143 AND BASIS_TYPE = p_basis_type
144 AND RESOURCE_ID = p_resource_id
145 AND SUBSTITUTE_GROUP_NUM = p_substiute_group_number
146 AND OPERATION_SEQUENCE_ID = p_operation_sequence_id
147 AND REPLACEMENT_GROUP_NUM = p_replacement_group_number ; --bug 2489765
148
149 sub_res_rec BOM_SUB_OPERATION_RESOURCES%ROWTYPE ;
150
151
152 BEGIN
153
154 x_mesg_token_tbl := p_mesg_token_tbl;
155 l_bo_id := BOM_Rtg_Globals.Get_Bo_Identifier ;
156
157 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
158 ('Querying a sub operation resource record : Sub Res Id ' || to_char(p_resource_id)
159 || ' - Schedule Seq Num ' || to_char(p_substitute_group_number) || '. ' ) ;
160 END IF ;
161
162 IF NOT sub_res_csr%ISOPEN
163 THEN
164 OPEN sub_res_csr( p_resource_id
165 , p_substitute_group_number
166 , p_operation_sequence_id
167 , l_bo_id
168 , p_acd_type
169 , p_replacement_group_number -- bug 2489765
170 , p_basis_type
171 ) ;
172 END IF ;
173
174 FETCH sub_res_csr INTO sub_res_rec ;
175
176 IF sub_res_csr%FOUND
177 THEN
178
179
180 -- Unexposed Column
181 l_rev_sub_res_unexp_rec.Operation_Sequence_Id := sub_res_rec.OPERATION_SEQUENCE_ID ;
182 l_rev_sub_resource_rec.Substitute_Group_Number := sub_res_rec.SUBSTITUTE_GROUP_NUM ;
183 l_rev_sub_res_unexp_rec.Substitute_Group_Number := l_rev_sub_resource_rec.Substitute_Group_Number;
184 l_rev_sub_res_unexp_rec.Resource_Id := sub_res_rec.RESOURCE_ID ;
185 l_rev_sub_res_unexp_rec.Activity_Id := sub_res_rec.ACTIVITY_ID ;
186 l_rev_sub_res_unexp_rec.Setup_Id := sub_res_rec.SETUP_ID ;
187
188 -- Exposed Column
189 l_rev_sub_resource_rec.Eco_Name := sub_res_rec.CHANGE_NOTICE ;
190 l_rev_sub_resource_rec.ACD_Type := sub_res_rec.ACD_TYPE ;
191 l_rev_sub_resource_rec.Schedule_Sequence_Number := sub_res_rec.SCHEDULE_SEQ_NUM ;
192 l_rev_sub_resource_rec.Replacement_Group_Number := sub_res_rec.REPLACEMENT_GROUP_NUM ;
193 l_rev_sub_resource_rec.Standard_Rate_Flag := sub_res_rec.STANDARD_RATE_FLAG ;
194 l_rev_sub_resource_rec.Assigned_Units := sub_res_rec.Assigned_Units ;
195 l_rev_sub_resource_rec.Usage_Rate_Or_Amount := sub_res_rec.USAGE_RATE_OR_AMOUNT ;
196 l_rev_sub_resource_rec.Usage_Rate_Or_Amount_Inverse := sub_res_rec.USAGE_RATE_OR_AMOUNT_INVERSE ;
197 l_rev_sub_resource_rec.Basis_Type := sub_res_rec.BASIS_TYPE ;
198 l_rev_sub_resource_rec.Schedule_Flag := sub_res_rec.SCHEDULE_FLAG ;
199 l_rev_sub_resource_rec.Resource_Offset_Percent := sub_res_rec.RESOURCE_OFFSET_PERCENT ;
200 l_rev_sub_resource_rec.Autocharge_Type := sub_res_rec.AUTOCHARGE_TYPE ;
201 l_rev_sub_resource_rec.Schedule_Sequence_Number := sub_res_rec.SCHEDULE_SEQ_NUM ;
202 l_rev_sub_resource_rec.Principle_Flag := sub_res_rec.PRINCIPLE_FLAG ;
203 l_rev_sub_resource_rec.Attribute_category := sub_res_rec.ATTRIBUTE_CATEGORY ;
204 l_rev_sub_resource_rec.Attribute1 := sub_res_rec.ATTRIBUTE1 ;
205 l_rev_sub_resource_rec.Attribute2 := sub_res_rec.ATTRIBUTE2 ;
206 l_rev_sub_resource_rec.Attribute3 := sub_res_rec.ATTRIBUTE3 ;
207 l_rev_sub_resource_rec.Attribute4 := sub_res_rec.ATTRIBUTE4 ;
208 l_rev_sub_resource_rec.Attribute5 := sub_res_rec.ATTRIBUTE5 ;
209 l_rev_sub_resource_rec.Attribute6 := sub_res_rec.ATTRIBUTE6 ;
210 l_rev_sub_resource_rec.Attribute7 := sub_res_rec.ATTRIBUTE7 ;
211 l_rev_sub_resource_rec.Attribute8 := sub_res_rec.ATTRIBUTE8 ;
212 l_rev_sub_resource_rec.Attribute9 := sub_res_rec.ATTRIBUTE9 ;
213 l_rev_sub_resource_rec.Attribute10 := sub_res_rec.ATTRIBUTE10 ;
214 l_rev_sub_resource_rec.Attribute11 := sub_res_rec.ATTRIBUTE11 ;
215 l_rev_sub_resource_rec.Attribute12 := sub_res_rec.ATTRIBUTE12 ;
216 l_rev_sub_resource_rec.Attribute13 := sub_res_rec.ATTRIBUTE13 ;
217 l_rev_sub_resource_rec.Attribute14 := sub_res_rec.ATTRIBUTE14 ;
218 l_rev_sub_resource_rec.Attribute15 := sub_res_rec.ATTRIBUTE15 ;
219 l_rev_sub_resource_rec.Original_System_Reference := sub_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_sub_resource_rec := l_rev_sub_resource_rec ;
226 x_rev_sub_res_unexp_rec := l_rev_sub_res_unexp_rec ;
227
228 ELSE
229 x_return_status := BOM_Rtg_Globals.G_RECORD_NOT_FOUND ;
230 x_rev_sub_resource_rec := l_rev_sub_resource_rec ;
231 x_rev_sub_res_unexp_rec := l_rev_sub_res_unexp_rec ;
232
233 END IF ;
234
235 IF sub_res_csr%ISOPEN
236 THEN
237 CLOSE sub_res_csr ;
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 Perform Writes . . .' || SQLERRM );
244 END IF ;
245
246 l_err_text := G_PKG_NAME || ' Utility (Sub 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 : Sub Operation Resource exposed column record
269 * Sub Operation Resource unexposed column record
270 * Parameters out: Return Status
271 * Message Token Table
272 * Purpose : Convert Rtg Sub Op Resource to ECO Sub 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 * Sub Operation Resources table.
277 *********************************************************************/
278
279 PROCEDURE Perform_Writes
280 ( p_sub_resource_rec IN Bom_Rtg_Pub.Sub_Resource_Rec_Type
281 , p_sub_res_unexp_rec IN Bom_Rtg_Pub.Sub_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_sub_resource_rec Bom_Rtg_Pub.Rev_Sub_Resource_Rec_Type ;
287 l_rev_sub_res_unexp_rec Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type ;
288
289 BEGIN
290 -- Convert Routing Operation to Common Operation
291 Bom_Rtg_Pub.Convert_RtgSubRes_To_EcoSubRes
292 ( p_rtg_sub_resource_rec => p_sub_resource_rec
293 , p_rtg_sub_res_unexp_rec => p_sub_res_unexp_rec
294 , x_rev_sub_resource_rec => l_rev_sub_resource_rec
295 , x_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
296 ) ;
297
298 -- Call Perform Writes Procedure
299 Bom_Sub_Op_Res_UTIL.Perform_Writes
300 ( p_rev_sub_resource_rec => l_rev_sub_resource_rec
301 , p_rev_sub_res_unexp_rec => l_rev_sub_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 Sub Op Resource exposed column record
314 * Revised Sub 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_sub_resource_rec IN Bom_Rtg_Pub.Rev_Sub_Resource_Rec_Type
323 , p_rev_sub_res_unexp_rec IN Bom_Rtg_Pub.Rev_Sub_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_sub_resource_rec Bom_Rtg_Pub.Rev_Sub_Resource_Rec_Type ;
331 l_rev_sub_res_unexp_rec Bom_Rtg_Pub.Rev_Sub_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_sub_resource_rec := p_rev_sub_resource_rec ;
345 l_rev_sub_res_unexp_rec := p_rev_sub_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_sub_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 ('Sub Operatin Sequence: Executing Insert Row. . . ') ;
357 END IF;
358
359 Insert_Row
360 ( p_rev_sub_resource_rec => l_rev_sub_resource_rec
361 , p_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
362 , x_return_status => l_return_status
363 , x_mesg_token_tbl => x_mesg_token_tbl
364 ) ;
365
366
367 ELSIF l_rev_sub_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 ('Sub Operatin Sequence: Executing Update Row. . . ') ;
371 END IF ;
372
373 Update_Row
374 ( p_rev_sub_resource_rec => l_rev_sub_resource_rec
375 , p_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
376 , x_return_status => l_return_status
377 , x_mesg_token_tbl => x_mesg_token_tbl
378 ) ;
379
380 ELSIF l_rev_sub_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 ('Sub Operatin Sequence: Executing Delete Row. . . ') ;
385 END IF ;
386
387 Delete_Row
388 ( p_rev_sub_resource_rec => l_rev_sub_resource_rec
389 , p_rev_sub_res_unexp_rec => l_rev_sub_res_unexp_rec
390 , x_return_status => l_return_status
391 , x_mesg_token_tbl => x_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 Sub Operation Resource exposed column record
430 * Revised Sub Operation Resource unexposed column record
431 * Parameters out: Return Status
432 * Message Token Table
433 * Purpose : This procedure will insert a record in the Sub Operation Resource
434 * table; BOM_SUB_OPERATION_RESOURCES
435 *
436 *****************************************************************************/
437 PROCEDURE Insert_Row
438 ( p_rev_sub_resource_rec IN Bom_Rtg_Pub.Rev_Sub_Resource_Rec_Type
439 , p_rev_sub_res_unexp_rec IN Bom_Rtg_Pub.Rev_Sub_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.
454 INSERT INTO BOM_SUB_OPERATION_RESOURCES
455 (
456 operation_sequence_id
457 , substitute_group_num
458 , resource_id
459 , replacement_group_num
460 , activity_id
461 , standard_rate_flag
462 , assigned_units
463 , usage_rate_or_amount
464 , usage_rate_or_amount_inverse
465 , basis_type
466 , schedule_flag
467 , last_update_date
468 , last_updated_by
469 , creation_date
470 , created_by
471 , last_update_login
472 , resource_offset_percent
473 , autocharge_type
474 , principle_flag
475 , attribute_category
476 , attribute1
477 , attribute2
478 , attribute3
479 , attribute4
480 , attribute5
481 , attribute6
482 , attribute7
483 , attribute8
484 , attribute9
485 , attribute10
486 , attribute11
487 , attribute12
488 , attribute13
489 , attribute14
490 , attribute15
491 , request_id
492 , program_application_id
493 , program_id
494 , program_update_date
495 , schedule_seq_num
496 , change_notice
497 , acd_type
498 , original_system_reference
499 , setup_id
500 )
501 VALUES (
502 p_rev_sub_res_unexp_rec.operation_sequence_id
503 , nvl(p_rev_sub_resource_rec.substitute_group_number, p_rev_sub_res_unexp_rec.substitute_group_number)
504 , p_rev_sub_res_unexp_rec.resource_id
505 , p_rev_sub_resource_rec.replacement_group_number
506 , p_rev_sub_res_unexp_rec.activity_id
507 , p_rev_sub_resource_rec.standard_rate_flag
508 , p_rev_sub_resource_rec.assigned_units
509 , p_rev_sub_resource_rec.usage_rate_or_amount
510 , p_rev_sub_resource_rec.usage_rate_or_amount_inverse
511 , p_rev_sub_resource_rec.basis_type
512 , p_rev_sub_resource_rec.schedule_flag
513 , SYSDATE -- Last Update Date
514 , BOM_Rtg_Globals.Get_User_Id -- Last Updated By
515 , SYSDATE -- Creation Date
516 , BOM_Rtg_Globals.Get_User_Id -- Created By
517 , BOM_Rtg_Globals.Get_Login_Id -- Last Update Login
518 , p_rev_sub_resource_rec.resource_offset_percent
519 , p_rev_sub_resource_rec.autocharge_type
520 , p_rev_sub_resource_rec.principle_flag
521 , p_rev_sub_resource_rec.attribute_category
522 , p_rev_sub_resource_rec.attribute1
523 , p_rev_sub_resource_rec.attribute2
524 , p_rev_sub_resource_rec.attribute3
525 , p_rev_sub_resource_rec.attribute4
526 , p_rev_sub_resource_rec.attribute5
527 , p_rev_sub_resource_rec.attribute6
528 , p_rev_sub_resource_rec.attribute7
529 , p_rev_sub_resource_rec.attribute8
530 , p_rev_sub_resource_rec.attribute9
531 , p_rev_sub_resource_rec.attribute10
532 , p_rev_sub_resource_rec.attribute11
533 , p_rev_sub_resource_rec.attribute12
534 , p_rev_sub_resource_rec.attribute13
535 , p_rev_sub_resource_rec.attribute14
536 , p_rev_sub_resource_rec.attribute15
537 , Fnd_Global.Conc_Request_Id -- Request Id
538 , BOM_Rtg_Globals.Get_Prog_AppId -- Application Id
539 , BOM_Rtg_Globals.Get_Prog_Id -- Program Id
540 , SYSDATE -- program_update_date
541 , p_rev_sub_resource_rec.schedule_sequence_number
542 , p_rev_sub_resource_rec.eco_name
543 , p_rev_sub_resource_rec.acd_type
544 , p_rev_sub_resource_rec.original_system_reference
545 , p_rev_sub_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 (Sub 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 Sub Operation Resource exposed column record
579 * Revised Sub 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_sub_resource_rec IN Bom_Rtg_Pub.Rev_Sub_Resource_Rec_Type
588 , p_rev_sub_res_unexp_rec IN Bom_Rtg_Pub.Rev_Sub_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
606 UPDATE BOM_SUB_OPERATION_RESOURCES
607 SET
608 replacement_group_num = NVL(p_rev_sub_resource_rec.new_replacement_group_number, p_rev_sub_resource_rec.replacement_group_number) -- bug 3741570
609 , resource_id = NVL(p_rev_sub_res_unexp_rec.new_resource_id, p_rev_sub_res_unexp_rec.resource_id)
610 , schedule_seq_num = p_rev_sub_resource_rec.schedule_sequence_number
611 , activity_id = p_rev_sub_res_unexp_rec.activity_id
612 , standard_rate_flag = p_rev_sub_resource_rec.standard_rate_flag
613 , assigned_units = p_rev_sub_resource_rec.assigned_units
614 , usage_rate_or_amount = p_rev_sub_resource_rec.usage_rate_or_amount
615 , usage_rate_or_amount_inverse = p_rev_sub_resource_rec.usage_rate_or_amount_inverse
616 , basis_type = NVL(p_rev_sub_resource_rec.new_basis_type,p_rev_sub_resource_rec.basis_type)
617 , schedule_flag = p_rev_sub_resource_rec.schedule_flag
618 , last_update_date = SYSDATE /* Last Update Date */
619 , last_updated_by = BOM_Rtg_Globals.Get_User_Id /* Last Updated By */
620 , last_update_login = BOM_Rtg_Globals.Get_Login_Id /* Last Update Login */
621 , resource_offset_percent = p_rev_sub_resource_rec.resource_offset_percent
622 , autocharge_type = p_rev_sub_resource_rec.autocharge_type
623 , principle_flag = p_rev_sub_resource_rec.principle_flag
624 , attribute_category = p_rev_sub_resource_rec.attribute_category
625 , attribute1 = p_rev_sub_resource_rec.attribute1
626 , attribute2 = p_rev_sub_resource_rec.attribute2
627 , attribute3 = p_rev_sub_resource_rec.attribute3
628 , attribute4 = p_rev_sub_resource_rec.attribute4
629 , attribute5 = p_rev_sub_resource_rec.attribute5
630 , attribute6 = p_rev_sub_resource_rec.attribute6
631 , attribute7 = p_rev_sub_resource_rec.attribute7
632 , attribute8 = p_rev_sub_resource_rec.attribute8
633 , attribute9 = p_rev_sub_resource_rec.attribute9
634 , attribute10 = p_rev_sub_resource_rec.attribute10
635 , attribute11 = p_rev_sub_resource_rec.attribute11
636 , attribute12 = p_rev_sub_resource_rec.attribute12
637 , attribute13 = p_rev_sub_resource_rec.attribute13
638 , attribute14 = p_rev_sub_resource_rec.attribute14
639 , attribute15 = p_rev_sub_resource_rec.attribute15
640 , program_application_id = BOM_Rtg_Globals.Get_Prog_AppId /* Application Id */
641 , program_id = BOM_Rtg_Globals.Get_Prog_Id /* Program Id */
642 , program_update_date = SYSDATE /* program_update_date */
643 , original_system_reference = p_rev_sub_resource_rec.original_system_reference
644 , setup_Id = p_rev_sub_res_unexp_rec.setup_id
645 , request_id = Fnd_Global.Conc_Request_Id
646 WHERE NVL(acd_type, 0) = NVL(p_rev_sub_resource_rec.acd_type,0)
647 AND basis_type = p_rev_sub_resource_rec.basis_type
648 AND substitute_group_num = nvl(p_rev_sub_resource_rec.substitute_group_number, p_rev_sub_res_unexp_rec.substitute_group_number)
649 AND resource_id = p_rev_sub_res_unexp_rec.resource_id
650 AND replacement_group_num = p_rev_sub_resource_rec.replacement_group_number -- bug 3741570
651 AND operation_sequence_id = p_rev_sub_res_unexp_rec.operation_sequence_id ;
652
653
654
655 EXCEPTION
656 WHEN OTHERS THEN
657 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
658 ('Unexpected Error occured in Update . . .' || SQLERRM);
659 END IF;
660
661 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
662 THEN
663 l_err_text := G_PKG_NAME || ' : Utility (Sub Op Resource Update) ' ||
664 SUBSTR(SQLERRM, 1, 200);
665 Error_Handler.Add_Error_Token
666 ( p_message_name => NULL
667 , p_message_text => l_err_text
668 , p_mesg_token_tbl => l_mesg_token_tbl
669 , x_mesg_token_tbl => l_mesg_token_tbl
670 ) ;
671 END IF ;
672
673 -- Return the status and message table.
674 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
675 x_mesg_token_tbl := l_mesg_token_tbl ;
676
677 END Update_Row ;
678
679
680
681 /********************************************************************
682 * Procedure : Delete_Row
683 * Parameters IN : Revised Sub Operation Resource exposed column record
684 * Revised Sub Operation Resource unexposed column record
685 * Parameters out: Return Status
686 * Message Token Table
687 * Purpose : Delete_Row procedure will delete the production record with
688 * the user given values. Any errors will be returned by filling
689 * the Mesg_Token_Tbl and setting the return_status.
690 *
691 *********************************************************************/
692 PROCEDURE Delete_Row
693 ( p_rev_sub_resource_rec IN Bom_Rtg_Pub.Rev_Sub_Resource_Rec_Type
694 , p_rev_sub_res_unexp_rec IN Bom_Rtg_Pub.Rev_Sub_Res_Unexposed_Rec_Type
695 , x_Mesg_Token_Tbl IN OUT NOCOPY Error_Handler.Mesg_Token_Tbl_Type
696 , x_return_status IN OUT NOCOPY VARCHAR2
697 )
698 IS
699
700 -- Error Handlig Variables
701 l_err_text VARCHAR2(2000) ;
702 l_Mesg_Token_Tbl Error_Handler.Mesg_Token_Tbl_Type ;
703
704
705 BEGIN
706
707 x_return_status := FND_API.G_RET_STS_SUCCESS ;
708
709 DELETE FROM BOM_SUB_OPERATION_RESOURCES
710 WHERE NVL(acd_type, 0) = NVL(p_rev_sub_resource_rec.acd_type,0)
711 AND basis_type = p_rev_sub_resource_rec.basis_type
712 AND substitute_group_num = nvl(p_rev_sub_resource_rec.substitute_group_number, p_rev_sub_res_unexp_rec.substitute_group_number)
713 AND resource_id = p_rev_sub_res_unexp_rec.resource_id
714 AND replacement_group_num = p_rev_sub_resource_rec.replacement_group_number -- bug 3741570
715 AND operation_sequence_id = p_rev_sub_res_unexp_rec.operation_sequence_id
716 ;
717
718 x_Mesg_Token_Tbl := l_Mesg_Token_Tbl;
719
720 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug('Finished deleting revised sub operation resource record . . .') ;
721 END IF ;
722
723
724 EXCEPTION
725 WHEN OTHERS THEN
726 IF BOM_Rtg_Globals.Get_Debug = 'Y' THEN Error_Handler.Write_Debug
727 ('Unexpected Error occured in Delete . . .' || SQLERRM);
728 END IF;
729
730 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
731 THEN
732 l_err_text := G_PKG_NAME || ' : Utility (Sub Op Resource Delete) ' ||
733 SUBSTR(SQLERRM, 1, 200);
734 -- dbms_output.put_line('Unexpected Error: '||l_err_text);
735
736 Error_Handler.Add_Error_Token
737 ( p_message_name => NULL
738 , p_message_text => l_err_text
739 , p_mesg_token_tbl => l_mesg_token_tbl
740 , x_mesg_token_tbl => l_mesg_token_tbl
741 ) ;
742 END IF ;
743
744 -- Return the status and message table.
745 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
746 x_mesg_token_tbl := l_mesg_token_tbl ;
747
748 END Delete_Row ;
749
750
751 END BOM_Sub_Op_Res_UTIL ;