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