[Home] [Help]
PACKAGE BODY: APPS.GME_BATCH_STEP_RESOURCES_DBL
Source
1 PACKAGE BODY gme_batch_step_resources_dbl AS
2 /* $Header: GMEVGSRB.pls 120.1 2005/06/03 11:00:34 appldev $ */
3
4 /* Global Variables */
5 g_table_name VARCHAR2 (80) DEFAULT 'GME_BATCH_STEP_RESOURCES';
6 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
7 g_pkg_name VARCHAR2 (30) := 'GME_BATCH_STEP_RESOURCES_DBL';
8
9 /*============================================================================
10 | Copyright (c) 2001 Oracle Corporation
11 | TVP, Reading
12 | All rights reserved
13 =============================================================================
14 | FILENAME
15 | GMEVGSRB.pls
16 |
17 | DESCRIPTION
18 |
19 |
20 |
21 |
22 | NOTES
23 |
24 | HISTORY
25 | 12-MAR-01 Thomas Daniel Created
26 | 24-OCT-01 Pawan Kumar Added sequence_dependent_id and sequence_dependent_usage
27 | 16-JAN-02 Sanrda Dulyk Added capacity_tolerance
28 11-MAY-04 Rishi Varma 3307549
29 Added firm_type,group_sequence_id and group_sequence_number
30 | - insert_row
31 | - fetch_row
32 | - update_row
33 | - lock_row
34 |
35 |
36 =============================================================================
37 */
38
39 /* Api start of comments
40 +============================================================================
41 | FUNCTION NAME
42 | insert_row
43 |
44 | TYPE
45 | Private
46 | USAGE
47 | Insert_Row will insert a row in gme_batch_step_resources
48 |
49 |
50 | DESCRIPTION
51 | Insert_Row will insert a row in gme_batch_step_resources
52 |
53 |
54 |
55 | PARAMETERS
56 | p_batch_step_resources IN gme_batch_step_resources%ROWTYPE
57 | x_batch_step_resources IN OUT NOCOPY gme_batch_step_resources%ROWTYPE
58 |
59 | RETURNS
60 | BOOLEAN
61 | HISTORY
62 | 12-MAR-01 Thomas Daniel Created
63 |
64 |
65 |
66 +=============================================================================
67 Api end of comments
68 */
69 FUNCTION insert_row (
70 p_batch_step_resources IN gme_batch_step_resources%ROWTYPE
71 ,x_batch_step_resources IN OUT NOCOPY gme_batch_step_resources%ROWTYPE)
72 RETURN BOOLEAN
73 IS
74 l_api_name CONSTANT VARCHAR2 (30) := 'insert_row';
75 BEGIN
76 IF g_debug <= gme_debug.g_log_procedure THEN
77 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
78 || l_api_name);
79 END IF;
80
81 x_batch_step_resources := p_batch_step_resources;
82
83 INSERT INTO gme_batch_step_resources
84 (batchstep_resource_id
85 ,batchstep_activity_id
86 ,resources
87 ,cost_analysis_code
88 ,cost_cmpntcls_id
89 ,prim_rsrc_ind
90 ,scale_type
91 ,plan_rsrc_count
92 ,actual_rsrc_count
93 --,RESOURCE_QTY_UOM
94 , plan_rsrc_usage
95 ,actual_rsrc_usage
96 --,USAGE_UOM
97 , plan_start_date
98 ,actual_start_date
99 ,plan_cmplt_date
100 ,actual_cmplt_date
101 ,offset_interval
102 ,min_capacity
103 ,max_capacity
104 ,process_parameter_1
105 ,process_parameter_2
106 ,process_parameter_3
107 ,process_parameter_4
108 ,process_parameter_5
109 ,sequence_dependent_id
110 ,sequence_dependent_usage
111 ,capacity_tolerance
112 ,attribute_category
113 ,attribute1
114 ,attribute2
115 ,attribute3
116 ,attribute4
117 ,attribute5
118 ,attribute6
119 ,attribute7
120 ,attribute8
121 ,attribute9
122 ,attribute10
123 ,attribute11
124 ,attribute12
125 ,attribute13
126 ,attribute14
127 ,attribute15
128 ,attribute16
129 ,attribute17
130 ,attribute18
131 ,attribute19
132 ,attribute20
133 ,attribute21
134 ,attribute22
135 ,attribute23
136 ,attribute24
137 ,attribute25
138 ,attribute26
139 ,attribute27
140 ,attribute28
141 ,attribute29
142 ,attribute30
143 ,last_update_date, last_updated_by
144 ,created_by, creation_date
145 ,text_code
146 ,batch_id
147 ,batchstep_id
148 --,CAPACITY_UOM
149 , actual_rsrc_qty
150 ,plan_rsrc_qty
151 ,last_update_login
152 ,calculate_charges
153 ,original_rsrc_qty
154 ,original_rsrc_usage
155 ,firm_type
156 ,group_sequence_id
157 ,group_sequence_number
158 ,capacity_um
159 ,usage_um
160 ,resource_qty_um
161 ,organization_id)
162 VALUES (gem5_batchstepline_id_s.NEXTVAL
163 ,x_batch_step_resources.batchstep_activity_id
164 ,x_batch_step_resources.resources
165 ,x_batch_step_resources.cost_analysis_code
166 ,x_batch_step_resources.cost_cmpntcls_id
167 ,x_batch_step_resources.prim_rsrc_ind
168 ,x_batch_step_resources.scale_type
169 ,x_batch_step_resources.plan_rsrc_count
170 ,x_batch_step_resources.actual_rsrc_count
171 --,x_batch_step_resources.RESOURCE_QTY_UOM
172 , x_batch_step_resources.plan_rsrc_usage
173 ,x_batch_step_resources.actual_rsrc_usage
174 --,x_batch_step_resources.USAGE_UOM
175 , x_batch_step_resources.plan_start_date
176 ,x_batch_step_resources.actual_start_date
177 ,x_batch_step_resources.plan_cmplt_date
178 ,x_batch_step_resources.actual_cmplt_date
179 ,x_batch_step_resources.offset_interval
180 ,x_batch_step_resources.min_capacity
181 ,x_batch_step_resources.max_capacity
182 ,x_batch_step_resources.process_parameter_1
183 ,x_batch_step_resources.process_parameter_2
184 ,x_batch_step_resources.process_parameter_3
185 ,x_batch_step_resources.process_parameter_4
186 ,x_batch_step_resources.process_parameter_5
187 ,x_batch_step_resources.sequence_dependent_id
188 ,x_batch_step_resources.sequence_dependent_usage
189 ,x_batch_step_resources.capacity_tolerance
190 ,x_batch_step_resources.attribute_category
191 ,x_batch_step_resources.attribute1
192 ,x_batch_step_resources.attribute2
193 ,x_batch_step_resources.attribute3
194 ,x_batch_step_resources.attribute4
195 ,x_batch_step_resources.attribute5
196 ,x_batch_step_resources.attribute6
197 ,x_batch_step_resources.attribute7
198 ,x_batch_step_resources.attribute8
199 ,x_batch_step_resources.attribute9
200 ,x_batch_step_resources.attribute10
201 ,x_batch_step_resources.attribute11
202 ,x_batch_step_resources.attribute12
203 ,x_batch_step_resources.attribute13
204 ,x_batch_step_resources.attribute14
205 ,x_batch_step_resources.attribute15
206 ,x_batch_step_resources.attribute16
207 ,x_batch_step_resources.attribute17
208 ,x_batch_step_resources.attribute18
209 ,x_batch_step_resources.attribute19
210 ,x_batch_step_resources.attribute20
211 ,x_batch_step_resources.attribute21
212 ,x_batch_step_resources.attribute22
213 ,x_batch_step_resources.attribute23
214 ,x_batch_step_resources.attribute24
215 ,x_batch_step_resources.attribute25
216 ,x_batch_step_resources.attribute26
217 ,x_batch_step_resources.attribute27
218 ,x_batch_step_resources.attribute28
219 ,x_batch_step_resources.attribute29
220 ,x_batch_step_resources.attribute30
221 ,gme_common_pvt.g_timestamp, gme_common_pvt.g_user_ident
222 ,gme_common_pvt.g_user_ident, gme_common_pvt.g_timestamp
223 ,x_batch_step_resources.text_code
224 ,x_batch_step_resources.batch_id
225 ,x_batch_step_resources.batchstep_id
226 --,x_batch_step_resources.CAPACITY_UOM
227 , x_batch_step_resources.actual_rsrc_qty
228 ,x_batch_step_resources.plan_rsrc_qty
229 ,gme_common_pvt.g_login_id
230 ,x_batch_step_resources.calculate_charges
231 ,x_batch_step_resources.original_rsrc_qty
232 ,x_batch_step_resources.original_rsrc_usage
233 ,x_batch_step_resources.firm_type
234 ,x_batch_step_resources.group_sequence_id
235 ,x_batch_step_resources.group_sequence_number
236 ,x_batch_step_resources.capacity_um
237 ,x_batch_step_resources.usage_um
238 ,x_batch_step_resources.resource_qty_um
239 ,x_batch_step_resources.organization_id)
240 RETURNING batchstep_resource_id
241 INTO x_batch_step_resources.batchstep_resource_id;
242
243 IF SQL%FOUND THEN
244 x_batch_step_resources.created_by := gme_common_pvt.g_user_ident;
245 x_batch_step_resources.creation_date := gme_common_pvt.g_timestamp;
246 x_batch_step_resources.last_updated_by :=
247 gme_common_pvt.g_user_ident;
248 x_batch_step_resources.last_update_date :=
249 gme_common_pvt.g_timestamp;
250 RETURN TRUE;
251 ELSE
252 RETURN FALSE;
253 END IF;
254 EXCEPTION
255 WHEN OTHERS THEN
256 IF g_debug <= gme_debug.g_log_unexpected THEN
257 gme_debug.put_line ( 'When others exception in '
258 || g_pkg_name
259 || '.'
260 || l_api_name
261 || ' Error is '
262 || SQLERRM);
263 END IF;
264
265 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
266 RETURN FALSE;
267 END insert_row;
268
269 /* Api start of comments
270 +============================================================================
271 | FUNCTION NAME
272 | fetch_row
273 |
274 | TYPE
275 | Private
276 | USAGE
277 | Fetch_Row will fetch a row in gme_batch_step_resources
278 |
279 |
280 | DESCRIPTION
281 | Fetch_Row will fetch a row in gme_batch_step_resources
282 |
283 |
284 |
285 | PARAMETERS
286 | p_batch_step_resources IN gme_batch_step_resources%ROWTYPE
287 | x_batch_step_resources IN OUT NOCOPY gme_batch_step_resources%ROWTYPE
288 |
289 | RETURNS
290 | BOOLEAN
291 | HISTORY
292 | 12-MAR-01 Thomas Daniel Created
293 |
294 |
295 |
296 +=============================================================================
297 Api end of comments
298 */
299 FUNCTION fetch_row (
300 p_batch_step_resources IN gme_batch_step_resources%ROWTYPE
301 ,x_batch_step_resources IN OUT NOCOPY gme_batch_step_resources%ROWTYPE)
302 RETURN BOOLEAN
303 IS
304 l_api_name CONSTANT VARCHAR2 (30) := 'fetch_row';
305 BEGIN
306 IF g_debug <= gme_debug.g_log_procedure THEN
307 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
308 || l_api_name);
309 END IF;
310
311 IF p_batch_step_resources.batchstep_resource_id IS NOT NULL THEN
312 SELECT batchstep_resource_id
313 ,batchstep_activity_id
314 ,resources
315 ,cost_analysis_code
316 ,cost_cmpntcls_id
317 ,prim_rsrc_ind
318 ,scale_type
319 ,plan_rsrc_count
320 ,actual_rsrc_count
321 --,RESOURCE_QTY_UOM
322 , plan_rsrc_usage
323 ,actual_rsrc_usage
324 --,USAGE_UOM
325 , plan_start_date
326 ,actual_start_date
327 ,plan_cmplt_date
328 ,actual_cmplt_date
329 ,offset_interval
330 ,min_capacity
331 ,max_capacity
332 ,process_parameter_1
333 ,process_parameter_2
334 ,process_parameter_3
335 ,process_parameter_4
336 ,process_parameter_5
337 ,sequence_dependent_id
338 ,sequence_dependent_usage
339 ,capacity_tolerance
340 ,attribute_category
341 ,attribute1
342 ,attribute2
343 ,attribute3
344 ,attribute4
345 ,attribute5
346 ,attribute6
347 ,attribute7
348 ,attribute8
349 ,attribute9
350 ,attribute10
351 ,attribute11
352 ,attribute12
353 ,attribute13
354 ,attribute14
355 ,attribute15
356 ,attribute16
357 ,attribute17
358 ,attribute18
359 ,attribute19
360 ,attribute20
361 ,attribute21
362 ,attribute22
363 ,attribute23
364 ,attribute24
365 ,attribute25
366 ,attribute26
367 ,attribute27
368 ,attribute28
369 ,attribute29
370 ,attribute30
371 ,last_update_date
372 ,last_updated_by
373 ,created_by
374 ,creation_date
375 ,text_code
376 ,batch_id
377 ,batchstep_id
378 --,CAPACITY_UOM
379 , actual_rsrc_qty
380 ,plan_rsrc_qty
381 ,last_update_login
382 ,calculate_charges
383 ,original_rsrc_qty
384 ,original_rsrc_usage
385 ,firm_type
386 ,group_sequence_id
387 ,group_sequence_number
388 ,capacity_um
389 ,usage_um
390 ,resource_qty_um
391 ,organization_id
392 INTO x_batch_step_resources.batchstep_resource_id
393 ,x_batch_step_resources.batchstep_activity_id
394 ,x_batch_step_resources.resources
395 ,x_batch_step_resources.cost_analysis_code
399 ,x_batch_step_resources.plan_rsrc_count
396 ,x_batch_step_resources.cost_cmpntcls_id
397 ,x_batch_step_resources.prim_rsrc_ind
398 ,x_batch_step_resources.scale_type
400 ,x_batch_step_resources.actual_rsrc_count
401 --,x_batch_step_resources.RESOURCE_QTY_UOM
402 , x_batch_step_resources.plan_rsrc_usage
403 ,x_batch_step_resources.actual_rsrc_usage
404 --,x_batch_step_resources.USAGE_UOM
405 , x_batch_step_resources.plan_start_date
406 ,x_batch_step_resources.actual_start_date
407 ,x_batch_step_resources.plan_cmplt_date
408 ,x_batch_step_resources.actual_cmplt_date
409 ,x_batch_step_resources.offset_interval
410 ,x_batch_step_resources.min_capacity
411 ,x_batch_step_resources.max_capacity
412 ,x_batch_step_resources.process_parameter_1
413 ,x_batch_step_resources.process_parameter_2
414 ,x_batch_step_resources.process_parameter_3
415 ,x_batch_step_resources.process_parameter_4
416 ,x_batch_step_resources.process_parameter_5
417 ,x_batch_step_resources.sequence_dependent_id
418 ,x_batch_step_resources.sequence_dependent_usage
419 ,x_batch_step_resources.capacity_tolerance
420 ,x_batch_step_resources.attribute_category
421 ,x_batch_step_resources.attribute1
422 ,x_batch_step_resources.attribute2
423 ,x_batch_step_resources.attribute3
424 ,x_batch_step_resources.attribute4
425 ,x_batch_step_resources.attribute5
426 ,x_batch_step_resources.attribute6
427 ,x_batch_step_resources.attribute7
428 ,x_batch_step_resources.attribute8
429 ,x_batch_step_resources.attribute9
430 ,x_batch_step_resources.attribute10
431 ,x_batch_step_resources.attribute11
432 ,x_batch_step_resources.attribute12
433 ,x_batch_step_resources.attribute13
434 ,x_batch_step_resources.attribute14
435 ,x_batch_step_resources.attribute15
436 ,x_batch_step_resources.attribute16
437 ,x_batch_step_resources.attribute17
438 ,x_batch_step_resources.attribute18
439 ,x_batch_step_resources.attribute19
440 ,x_batch_step_resources.attribute20
441 ,x_batch_step_resources.attribute21
442 ,x_batch_step_resources.attribute22
443 ,x_batch_step_resources.attribute23
444 ,x_batch_step_resources.attribute24
445 ,x_batch_step_resources.attribute25
446 ,x_batch_step_resources.attribute26
447 ,x_batch_step_resources.attribute27
448 ,x_batch_step_resources.attribute28
449 ,x_batch_step_resources.attribute29
450 ,x_batch_step_resources.attribute30
451 ,x_batch_step_resources.last_update_date
452 ,x_batch_step_resources.last_updated_by
453 ,x_batch_step_resources.created_by
454 ,x_batch_step_resources.creation_date
455 ,x_batch_step_resources.text_code
456 ,x_batch_step_resources.batch_id
457 ,x_batch_step_resources.batchstep_id
458 --,x_batch_step_resources.CAPACITY_UOM
459 , x_batch_step_resources.actual_rsrc_qty
460 ,x_batch_step_resources.plan_rsrc_qty
461 ,x_batch_step_resources.last_update_login
462 ,x_batch_step_resources.calculate_charges
463 ,x_batch_step_resources.original_rsrc_qty
464 ,x_batch_step_resources.original_rsrc_usage
465 ,x_batch_step_resources.firm_type
466 ,x_batch_step_resources.group_sequence_id
467 ,x_batch_step_resources.group_sequence_number
468 ,x_batch_step_resources.capacity_um
469 ,x_batch_step_resources.usage_um
470 ,x_batch_step_resources.resource_qty_um
471 ,x_batch_step_resources.organization_id
472 FROM gme_batch_step_resources
473 WHERE batchstep_resource_id =
474 p_batch_step_resources.batchstep_resource_id;
475 ELSIF p_batch_step_resources.batchstep_activity_id IS NOT NULL
476 AND p_batch_step_resources.resources IS NOT NULL THEN
477 SELECT batchstep_resource_id
478 ,batchstep_activity_id
479 ,resources
480 ,cost_analysis_code
481 ,cost_cmpntcls_id
482 ,prim_rsrc_ind
483 ,scale_type
484 ,plan_rsrc_count
485 ,actual_rsrc_count
486 --,RESOURCE_QTY_UOM
487 , plan_rsrc_usage
488 ,actual_rsrc_usage
489 --,USAGE_UOM
490 , plan_start_date
491 ,actual_start_date
492 ,plan_cmplt_date
493 ,actual_cmplt_date
494 ,offset_interval
495 ,min_capacity
496 ,max_capacity
497 ,process_parameter_1
498 ,process_parameter_2
499 ,process_parameter_3
500 ,process_parameter_4
501 ,process_parameter_5
505 ,attribute_category
502 ,sequence_dependent_id
503 ,sequence_dependent_usage
504 ,capacity_tolerance
506 ,attribute1
507 ,attribute2
508 ,attribute3
509 ,attribute4
510 ,attribute5
511 ,attribute6
512 ,attribute7
513 ,attribute8
514 ,attribute9
515 ,attribute10
516 ,attribute11
517 ,attribute12
518 ,attribute13
519 ,attribute14
520 ,attribute15
521 ,attribute16
522 ,attribute17
523 ,attribute18
524 ,attribute19
525 ,attribute20
526 ,attribute21
527 ,attribute22
528 ,attribute23
529 ,attribute24
530 ,attribute25
531 ,attribute26
532 ,attribute27
533 ,attribute28
534 ,attribute29
535 ,attribute30
536 ,last_update_date
537 ,last_updated_by
538 ,created_by
539 ,creation_date
540 ,text_code
541 ,batch_id
542 ,batchstep_id
543 --,CAPACITY_UOM
544 , actual_rsrc_qty
545 ,plan_rsrc_qty
546 ,last_update_login
547 ,calculate_charges
548 ,original_rsrc_qty
549 ,original_rsrc_usage
550 ,firm_type
551 ,group_sequence_id
552 ,group_sequence_number
553 ,capacity_um
554 ,usage_um
555 ,resource_qty_um
556 ,organization_id
557 INTO x_batch_step_resources.batchstep_resource_id
558 ,x_batch_step_resources.batchstep_activity_id
559 ,x_batch_step_resources.resources
560 ,x_batch_step_resources.cost_analysis_code
561 ,x_batch_step_resources.cost_cmpntcls_id
562 ,x_batch_step_resources.prim_rsrc_ind
563 ,x_batch_step_resources.scale_type
564 ,x_batch_step_resources.plan_rsrc_count
565 ,x_batch_step_resources.actual_rsrc_count
566 --,x_batch_step_resources.RESOURCE_QTY_UOM
567 , x_batch_step_resources.plan_rsrc_usage
568 ,x_batch_step_resources.actual_rsrc_usage
569 --,x_batch_step_resources.USAGE_UOM
570 , x_batch_step_resources.plan_start_date
571 ,x_batch_step_resources.actual_start_date
572 ,x_batch_step_resources.plan_cmplt_date
573 ,x_batch_step_resources.actual_cmplt_date
574 ,x_batch_step_resources.offset_interval
575 ,x_batch_step_resources.min_capacity
576 ,x_batch_step_resources.max_capacity
577 ,x_batch_step_resources.process_parameter_1
578 ,x_batch_step_resources.process_parameter_2
579 ,x_batch_step_resources.process_parameter_3
580 ,x_batch_step_resources.process_parameter_4
581 ,x_batch_step_resources.process_parameter_5
582 ,x_batch_step_resources.sequence_dependent_id
583 ,x_batch_step_resources.sequence_dependent_usage
584 ,x_batch_step_resources.capacity_tolerance
585 ,x_batch_step_resources.attribute_category
586 ,x_batch_step_resources.attribute1
587 ,x_batch_step_resources.attribute2
588 ,x_batch_step_resources.attribute3
589 ,x_batch_step_resources.attribute4
590 ,x_batch_step_resources.attribute5
591 ,x_batch_step_resources.attribute6
592 ,x_batch_step_resources.attribute7
593 ,x_batch_step_resources.attribute8
594 ,x_batch_step_resources.attribute9
595 ,x_batch_step_resources.attribute10
596 ,x_batch_step_resources.attribute11
597 ,x_batch_step_resources.attribute12
598 ,x_batch_step_resources.attribute13
599 ,x_batch_step_resources.attribute14
600 ,x_batch_step_resources.attribute15
601 ,x_batch_step_resources.attribute16
602 ,x_batch_step_resources.attribute17
603 ,x_batch_step_resources.attribute18
604 ,x_batch_step_resources.attribute19
605 ,x_batch_step_resources.attribute20
606 ,x_batch_step_resources.attribute21
607 ,x_batch_step_resources.attribute22
608 ,x_batch_step_resources.attribute23
609 ,x_batch_step_resources.attribute24
610 ,x_batch_step_resources.attribute25
611 ,x_batch_step_resources.attribute26
612 ,x_batch_step_resources.attribute27
613 ,x_batch_step_resources.attribute28
614 ,x_batch_step_resources.attribute29
615 ,x_batch_step_resources.attribute30
616 ,x_batch_step_resources.last_update_date
617 ,x_batch_step_resources.last_updated_by
618 ,x_batch_step_resources.created_by
622 ,x_batch_step_resources.batchstep_id
619 ,x_batch_step_resources.creation_date
620 ,x_batch_step_resources.text_code
621 ,x_batch_step_resources.batch_id
623 --,x_batch_step_resources.CAPACITY_UOM
624 , x_batch_step_resources.actual_rsrc_qty
625 ,x_batch_step_resources.plan_rsrc_qty
626 ,x_batch_step_resources.last_update_login
627 ,x_batch_step_resources.calculate_charges
628 ,x_batch_step_resources.original_rsrc_qty
629 ,x_batch_step_resources.original_rsrc_usage
630 ,x_batch_step_resources.firm_type
631 ,x_batch_step_resources.group_sequence_id
632 ,x_batch_step_resources.group_sequence_number
633 ,x_batch_step_resources.capacity_um
634 ,x_batch_step_resources.usage_um
635 ,x_batch_step_resources.resource_qty_um
636 ,x_batch_step_resources.organization_id
637 FROM gme_batch_step_resources
638 WHERE batchstep_activity_id =
639 p_batch_step_resources.batchstep_activity_id
640 AND resources = p_batch_step_resources.resources;
641 ELSE
642 gme_common_pvt.log_message ('GME_NO_KEYS'
643 ,'TABLE_NAME'
644 ,g_table_name);
645 RETURN FALSE;
646 END IF;
647
648 RETURN TRUE;
649 EXCEPTION
650 WHEN NO_DATA_FOUND THEN
651 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
652 ,'TABLE_NAME'
653 ,g_table_name);
654 RETURN FALSE;
655 WHEN OTHERS THEN
656 IF g_debug <= gme_debug.g_log_unexpected THEN
657 gme_debug.put_line ( 'When others exception in '
658 || g_pkg_name
659 || '.'
660 || l_api_name
661 || ' Error is '
662 || SQLERRM);
663 END IF;
664
665 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
666 RETURN FALSE;
667 END fetch_row;
668
669 /* Api start of comments
670 +============================================================================
671 | FUNCTION NAME
672 | delete_row
673 |
674 | TYPE
675 | Private
676 | USAGE
677 | Delete_Row will delete a row in gme_batch_step_resources
678 |
679 |
680 | DESCRIPTION
681 | Delete_Row will delete a row in gme_batch_step_resources
682 |
683 |
684 |
685 | PARAMETERS
686 | p_batch_step_resources IN gme_batch_step_resources%ROWTYPE
687 |
688 | RETURNS
689 | BOOLEAN
690 | HISTORY
691 | 12-MAR-01 Thomas Daniel Created
692 | 26-AUG-02 Bharati Satpute Bug 2404126
693 | Added error message 'GME_RECORD_CHANGED'
694 |
695 |
696 +=============================================================================
697 Api end of comments
698 */
699 FUNCTION delete_row (
700 p_batch_step_resources IN gme_batch_step_resources%ROWTYPE)
701 RETURN BOOLEAN
702 IS
703 l_dummy NUMBER := 0;
704 locked_by_other_user EXCEPTION;
705 PRAGMA EXCEPTION_INIT (locked_by_other_user, -54);
706 l_api_name CONSTANT VARCHAR2 (30) := 'delete_row';
707 BEGIN
708 IF g_debug <= gme_debug.g_log_procedure THEN
709 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
710 || l_api_name);
711 END IF;
712
713 IF p_batch_step_resources.batchstep_resource_id IS NOT NULL THEN
714 SELECT 1
715 INTO l_dummy
716 FROM gme_batch_step_resources
717 WHERE batchstep_resource_id =
718 p_batch_step_resources.batchstep_resource_id
719 FOR UPDATE NOWAIT;
720
721 DELETE FROM gme_batch_step_resources
722 WHERE batchstep_resource_id =
723 p_batch_step_resources.batchstep_resource_id;
724 ELSIF p_batch_step_resources.batchstep_activity_id IS NOT NULL
725 AND p_batch_step_resources.resources IS NOT NULL THEN
726 SELECT 1
727 INTO l_dummy
728 FROM gme_batch_step_resources
729 WHERE batchstep_activity_id =
730 p_batch_step_resources.batchstep_activity_id
731 AND resources = p_batch_step_resources.resources
732 FOR UPDATE NOWAIT;
733
734 DELETE FROM gme_batch_step_resources
735 WHERE batchstep_activity_id =
736 p_batch_step_resources.batchstep_activity_id
737 AND resources = p_batch_step_resources.resources;
738 ELSE
739 gme_common_pvt.log_message ('GME_NO_KEYS'
740 ,'TABLE_NAME'
741 ,g_table_name);
742 RETURN FALSE;
743 END IF;
747 ELSE
744
745 IF SQL%FOUND THEN
746 RETURN TRUE;
748 IF l_dummy = 0 THEN
749 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
750 ,'TABLE_NAME'
751 ,g_table_name);
752 ELSE
753 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
754 ,'TABLE_NAME'
755 ,g_table_name);
756 END IF;
757
758 RETURN FALSE;
759 END IF;
760 EXCEPTION
761 WHEN locked_by_other_user THEN
762 gme_common_pvt.log_message
763 ('GME_RECORD_LOCKED'
764 ,'TABLE_NAME'
765 ,g_table_name
766 ,'RECORD'
767 ,'Resource'
768 ,'KEY'
769 ,TO_CHAR (p_batch_step_resources.batchstep_resource_id) );
770 RETURN FALSE;
771 WHEN OTHERS THEN
772 IF g_debug <= gme_debug.g_log_unexpected THEN
773 gme_debug.put_line ( 'When others exception in '
774 || g_pkg_name
775 || '.'
776 || l_api_name
777 || ' Error is '
778 || SQLERRM);
779 END IF;
780
781 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
782 RETURN FALSE;
783 END delete_row;
784
785 /* Api start of comments
786 +============================================================================
787 | FUNCTION NAME
788 | update_row
789 |
790 | TYPE
791 | Private
792 | USAGE
793 | Update_Row will update a row in gme_batch_step_resources
794 |
795 |
796 | DESCRIPTION
797 | Update_Row will update a row in gme_batch_step_resources
798 |
799 |
800 |
801 | PARAMETERS
802 | p_batch_step_resources IN gme_batch_step_resources%ROWTYPE
803 |
804 | RETURNS
805 | BOOLEAN
806 | HISTORY
807 | 12-MAR-01 Thomas Daniel Created
808 | 26-AUG-02 Bharati Satpute Bug 2404126
809 | Added error message 'GME_RECORD_CHANGED'
810 |
811 |
812 +=============================================================================
813 Api end of comments
814 */
815 FUNCTION update_row (
816 p_batch_step_resources IN gme_batch_step_resources%ROWTYPE)
817 RETURN BOOLEAN
818 IS
819 l_dummy NUMBER := 0;
820 locked_by_other_user EXCEPTION;
821 PRAGMA EXCEPTION_INIT (locked_by_other_user, -54);
822 l_api_name CONSTANT VARCHAR2 (30) := 'update_row';
823 BEGIN
824 IF g_debug <= gme_debug.g_log_procedure THEN
825 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
826 || l_api_name);
827 END IF;
828
829 IF p_batch_step_resources.batchstep_resource_id IS NOT NULL THEN
830 SELECT 1
831 INTO l_dummy
832 FROM gme_batch_step_resources
833 WHERE batchstep_resource_id =
834 p_batch_step_resources.batchstep_resource_id
835 FOR UPDATE NOWAIT;
836
837 UPDATE gme_batch_step_resources
838 SET batchstep_activity_id =
839 p_batch_step_resources.batchstep_activity_id
840 ,resources = p_batch_step_resources.resources
841 ,cost_analysis_code = p_batch_step_resources.cost_analysis_code
842 ,cost_cmpntcls_id = p_batch_step_resources.cost_cmpntcls_id
843 ,prim_rsrc_ind = p_batch_step_resources.prim_rsrc_ind
844 ,scale_type = p_batch_step_resources.scale_type
845 ,plan_rsrc_count = p_batch_step_resources.plan_rsrc_count
846 ,actual_rsrc_count = p_batch_step_resources.actual_rsrc_count
847 --,RESOURCE_QTY_UOM = p_batch_step_resources.RESOURCE_QTY_UOM
848 , plan_rsrc_usage = p_batch_step_resources.plan_rsrc_usage
849 ,actual_rsrc_usage = p_batch_step_resources.actual_rsrc_usage
850 --,USAGE_UOM = p_batch_step_resources.USAGE_UOM
851 , plan_start_date = p_batch_step_resources.plan_start_date
852 ,actual_start_date = p_batch_step_resources.actual_start_date
853 ,plan_cmplt_date = p_batch_step_resources.plan_cmplt_date
854 ,actual_cmplt_date = p_batch_step_resources.actual_cmplt_date
855 ,offset_interval = p_batch_step_resources.offset_interval
856 ,min_capacity = p_batch_step_resources.min_capacity
857 ,max_capacity = p_batch_step_resources.max_capacity
858 ,process_parameter_1 =
859 p_batch_step_resources.process_parameter_1
860 ,process_parameter_2 =
861 p_batch_step_resources.process_parameter_2
862 ,process_parameter_3 =
863 p_batch_step_resources.process_parameter_3
864 ,process_parameter_4 =
868 ,sequence_dependent_id =
865 p_batch_step_resources.process_parameter_4
866 ,process_parameter_5 =
867 p_batch_step_resources.process_parameter_5
869 p_batch_step_resources.sequence_dependent_id
870 ,sequence_dependent_usage =
871 p_batch_step_resources.sequence_dependent_usage
872 ,capacity_tolerance = p_batch_step_resources.capacity_tolerance
873 ,attribute_category = p_batch_step_resources.attribute_category
874 ,attribute1 = p_batch_step_resources.attribute1
875 ,attribute2 = p_batch_step_resources.attribute2
876 ,attribute3 = p_batch_step_resources.attribute3
877 ,attribute4 = p_batch_step_resources.attribute4
878 ,attribute5 = p_batch_step_resources.attribute5
879 ,attribute6 = p_batch_step_resources.attribute6
880 ,attribute7 = p_batch_step_resources.attribute7
881 ,attribute8 = p_batch_step_resources.attribute8
882 ,attribute9 = p_batch_step_resources.attribute9
883 ,attribute10 = p_batch_step_resources.attribute10
884 ,attribute11 = p_batch_step_resources.attribute11
885 ,attribute12 = p_batch_step_resources.attribute12
886 ,attribute13 = p_batch_step_resources.attribute13
887 ,attribute14 = p_batch_step_resources.attribute14
888 ,attribute15 = p_batch_step_resources.attribute15
889 ,attribute16 = p_batch_step_resources.attribute16
890 ,attribute17 = p_batch_step_resources.attribute17
891 ,attribute18 = p_batch_step_resources.attribute18
892 ,attribute19 = p_batch_step_resources.attribute19
893 ,attribute20 = p_batch_step_resources.attribute20
894 ,attribute21 = p_batch_step_resources.attribute21
895 ,attribute22 = p_batch_step_resources.attribute22
896 ,attribute23 = p_batch_step_resources.attribute23
897 ,attribute24 = p_batch_step_resources.attribute24
898 ,attribute25 = p_batch_step_resources.attribute25
899 ,attribute26 = p_batch_step_resources.attribute26
900 ,attribute27 = p_batch_step_resources.attribute27
901 ,attribute28 = p_batch_step_resources.attribute28
902 ,attribute29 = p_batch_step_resources.attribute29
903 ,attribute30 = p_batch_step_resources.attribute30
904 ,last_update_date = gme_common_pvt.g_timestamp
905 ,last_updated_by = gme_common_pvt.g_user_ident
906 ,text_code = p_batch_step_resources.text_code
907 ,batch_id = p_batch_step_resources.batch_id
908 ,batchstep_id = p_batch_step_resources.batchstep_id
909 --,CAPACITY_UOM = p_batch_step_resources.CAPACITY_UOM
910 , actual_rsrc_qty = p_batch_step_resources.actual_rsrc_qty
911 ,plan_rsrc_qty = p_batch_step_resources.plan_rsrc_qty
912 ,last_update_login = gme_common_pvt.g_login_id
913 ,calculate_charges = p_batch_step_resources.calculate_charges
914 ,original_rsrc_qty = p_batch_step_resources.original_rsrc_qty
915 ,original_rsrc_usage =
916 p_batch_step_resources.original_rsrc_usage
917 ,firm_type = p_batch_step_resources.firm_type
918 ,group_sequence_id = p_batch_step_resources.group_sequence_id
919 ,group_sequence_number =
920 p_batch_step_resources.group_sequence_number
921 ,capacity_um = p_batch_step_resources.capacity_um
922 ,usage_um = p_batch_step_resources.usage_um
923 ,resource_qty_um = p_batch_step_resources.resource_qty_um
924 WHERE batchstep_resource_id =
925 p_batch_step_resources.batchstep_resource_id
926 AND last_update_date = p_batch_step_resources.last_update_date;
927 ELSIF p_batch_step_resources.batchstep_activity_id IS NOT NULL
928 AND p_batch_step_resources.resources IS NOT NULL THEN
929 SELECT 1
930 INTO l_dummy
931 FROM gme_batch_step_resources
932 WHERE batchstep_activity_id =
933 p_batch_step_resources.batchstep_activity_id
934 AND resources = p_batch_step_resources.resources
935 FOR UPDATE NOWAIT;
936
937 UPDATE gme_batch_step_resources
938 SET batchstep_activity_id =
939 p_batch_step_resources.batchstep_activity_id
940 ,resources = p_batch_step_resources.resources
941 ,cost_analysis_code = p_batch_step_resources.cost_analysis_code
942 ,cost_cmpntcls_id = p_batch_step_resources.cost_cmpntcls_id
943 ,prim_rsrc_ind = p_batch_step_resources.prim_rsrc_ind
944 ,scale_type = p_batch_step_resources.scale_type
945 ,plan_rsrc_count = p_batch_step_resources.plan_rsrc_count
946 ,actual_rsrc_count = p_batch_step_resources.actual_rsrc_count
947 --,RESOURCE_QTY_UOM = p_batch_step_resources.RESOURCE_QTY_UOM
948 , plan_rsrc_usage = p_batch_step_resources.plan_rsrc_usage
949 ,actual_rsrc_usage = p_batch_step_resources.actual_rsrc_usage
950 --,USAGE_UOM = p_batch_step_resources.USAGE_UOM
951 , plan_start_date = p_batch_step_resources.plan_start_date
952 ,actual_start_date = p_batch_step_resources.actual_start_date
953 ,plan_cmplt_date = p_batch_step_resources.plan_cmplt_date
954 ,actual_cmplt_date = p_batch_step_resources.actual_cmplt_date
955 ,offset_interval = p_batch_step_resources.offset_interval
956 ,min_capacity = p_batch_step_resources.min_capacity
957 ,max_capacity = p_batch_step_resources.max_capacity
958 ,process_parameter_1 =
959 p_batch_step_resources.process_parameter_1
960 ,process_parameter_2 =
961 p_batch_step_resources.process_parameter_2
962 ,process_parameter_3 =
963 p_batch_step_resources.process_parameter_3
964 ,process_parameter_4 =
965 p_batch_step_resources.process_parameter_4
966 ,process_parameter_5 =
967 p_batch_step_resources.process_parameter_5
968 ,sequence_dependent_id =
969 p_batch_step_resources.sequence_dependent_id
970 ,sequence_dependent_usage =
971 p_batch_step_resources.sequence_dependent_usage
972 ,capacity_tolerance = p_batch_step_resources.capacity_tolerance
973 ,attribute_category = p_batch_step_resources.attribute_category
974 ,attribute1 = p_batch_step_resources.attribute1
975 ,attribute2 = p_batch_step_resources.attribute2
976 ,attribute3 = p_batch_step_resources.attribute3
977 ,attribute4 = p_batch_step_resources.attribute4
981 ,attribute8 = p_batch_step_resources.attribute8
978 ,attribute5 = p_batch_step_resources.attribute5
979 ,attribute6 = p_batch_step_resources.attribute6
980 ,attribute7 = p_batch_step_resources.attribute7
982 ,attribute9 = p_batch_step_resources.attribute9
983 ,attribute10 = p_batch_step_resources.attribute10
984 ,attribute11 = p_batch_step_resources.attribute11
985 ,attribute12 = p_batch_step_resources.attribute12
986 ,attribute13 = p_batch_step_resources.attribute13
987 ,attribute14 = p_batch_step_resources.attribute14
988 ,attribute15 = p_batch_step_resources.attribute15
989 ,attribute16 = p_batch_step_resources.attribute16
990 ,attribute17 = p_batch_step_resources.attribute17
991 ,attribute18 = p_batch_step_resources.attribute18
992 ,attribute19 = p_batch_step_resources.attribute19
993 ,attribute20 = p_batch_step_resources.attribute20
994 ,attribute21 = p_batch_step_resources.attribute21
995 ,attribute22 = p_batch_step_resources.attribute22
996 ,attribute23 = p_batch_step_resources.attribute23
997 ,attribute24 = p_batch_step_resources.attribute24
998 ,attribute25 = p_batch_step_resources.attribute25
999 ,attribute26 = p_batch_step_resources.attribute26
1000 ,attribute27 = p_batch_step_resources.attribute27
1001 ,attribute28 = p_batch_step_resources.attribute28
1002 ,attribute29 = p_batch_step_resources.attribute29
1003 ,attribute30 = p_batch_step_resources.attribute30
1004 ,last_update_date = gme_common_pvt.g_timestamp
1005 ,last_updated_by = gme_common_pvt.g_user_ident
1006 ,text_code = p_batch_step_resources.text_code
1007 ,batch_id = p_batch_step_resources.batch_id
1008 ,batchstep_id = p_batch_step_resources.batchstep_id
1009 --,CAPACITY_UOM = p_batch_step_resources.CAPACITY_UOM
1010 , actual_rsrc_qty = p_batch_step_resources.actual_rsrc_qty
1011 ,plan_rsrc_qty = p_batch_step_resources.plan_rsrc_qty
1012 ,last_update_login = gme_common_pvt.g_login_id
1013 ,calculate_charges = p_batch_step_resources.calculate_charges
1014 ,original_rsrc_qty = p_batch_step_resources.original_rsrc_qty
1015 ,original_rsrc_usage =
1016 p_batch_step_resources.original_rsrc_usage
1017 ,firm_type = p_batch_step_resources.firm_type
1018 ,group_sequence_id = p_batch_step_resources.group_sequence_id
1019 ,group_sequence_number =
1020 p_batch_step_resources.group_sequence_number
1021 ,capacity_um = p_batch_step_resources.capacity_um
1022 ,usage_um = p_batch_step_resources.usage_um
1023 ,resource_qty_um = p_batch_step_resources.resource_qty_um
1024 WHERE batchstep_activity_id =
1025 p_batch_step_resources.batchstep_activity_id
1026 AND resources = p_batch_step_resources.resources
1027 AND last_update_date = p_batch_step_resources.last_update_date;
1028 ELSE
1029 gme_common_pvt.log_message ('GME_NO_KEYS'
1030 ,'TABLE_NAME'
1031 ,g_table_name);
1032 RETURN FALSE;
1033 END IF;
1034
1035 IF SQL%FOUND THEN
1036 RETURN TRUE;
1037 ELSE
1038 IF l_dummy = 0 THEN
1039 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
1040 ,'TABLE_NAME'
1041 ,g_table_name);
1042 ELSE
1043 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
1044 ,'TABLE_NAME'
1045 ,g_table_name);
1046 END IF;
1047
1048 RETURN FALSE;
1049 END IF;
1050 EXCEPTION
1051 WHEN NO_DATA_FOUND THEN
1052 IF l_dummy = 0 THEN
1053 gme_common_pvt.log_message ('GME_NO_DATA_FOUND'
1054 ,'TABLE_NAME'
1055 ,g_table_name);
1056 ELSE
1057 gme_common_pvt.log_message ('GME_RECORD_CHANGED'
1058 ,'TABLE_NAME'
1059 ,g_table_name);
1060 END IF;
1061
1062 RETURN FALSE;
1063 WHEN locked_by_other_user THEN
1064 gme_common_pvt.log_message
1065 ('GME_RECORD_LOCKED'
1066 ,'TABLE_NAME'
1067 ,g_table_name
1068 ,'RECORD'
1069 ,'Resource'
1070 ,'KEY'
1071 ,TO_CHAR (p_batch_step_resources.batchstep_resource_id) );
1072 RETURN FALSE;
1073 WHEN OTHERS THEN
1074 IF g_debug <= gme_debug.g_log_unexpected THEN
1075 gme_debug.put_line ( 'When others exception in '
1076 || g_pkg_name
1077 || '.'
1078 || l_api_name
1079 || ' Error is '
1080 || SQLERRM);
1081 END IF;
1082
1083 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR', SQLERRM);
1084 RETURN FALSE;
1085 END update_row;
1086
1087 /* Api start of comments
1088 +============================================================================
1089 | FUNCTION NAME
1090 | lock_row
1091 |
1092 | TYPE
1093 | Private
1094 | USAGE
1098 | DESCRIPTION
1095 | Lock_Row will lock a row in gme_batch_step_resources
1096 |
1097 |
1099 | Lock_Row will lock a row in gme_batch_step_resources
1100 |
1101 |
1102 |
1103 | PARAMETERS
1104 | p_batch_step_resources IN gme_batch_step_resources%ROWTYPE
1105 |
1106 | RETURNS
1107 | BOOLEAN
1108 | HISTORY
1109 | 12-MAR-01 Thomas Daniel Created
1110 |
1111 |
1112 |
1113 +=============================================================================
1114 Api end of comments
1115 */
1116 FUNCTION lock_row (
1117 p_batch_step_resources IN gme_batch_step_resources%ROWTYPE)
1118 RETURN BOOLEAN
1119 IS
1120 l_dummy NUMBER;
1121 l_api_name CONSTANT VARCHAR2 (30) := 'lock_row';
1122 BEGIN
1123 IF g_debug <= gme_debug.g_log_procedure THEN
1124 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
1125 || l_api_name);
1126 END IF;
1127
1128 IF p_batch_step_resources.batchstep_resource_id IS NOT NULL THEN
1129 SELECT 1
1130 INTO l_dummy
1131 FROM gme_batch_step_resources
1132 WHERE batchstep_resource_id =
1133 p_batch_step_resources.batchstep_resource_id
1134 FOR UPDATE NOWAIT;
1135 ELSIF p_batch_step_resources.batchstep_activity_id IS NOT NULL
1136 AND p_batch_step_resources.resources IS NOT NULL THEN
1137 SELECT 1
1138 INTO l_dummy
1139 FROM gme_batch_step_resources
1140 WHERE batchstep_activity_id =
1141 p_batch_step_resources.batchstep_activity_id
1142 AND resources = p_batch_step_resources.resources
1143 FOR UPDATE NOWAIT;
1144 END IF;
1145
1146 RETURN TRUE;
1147 EXCEPTION
1148 WHEN app_exception.record_lock_exception THEN
1149 gme_common_pvt.log_message
1150 ('GME_RECORD_LOCKED'
1151 ,'TABLE_NAME'
1152 ,g_table_name
1153 ,'RECORD'
1154 ,'Resource'
1155 ,'KEY'
1156 ,TO_CHAR (p_batch_step_resources.batchstep_resource_id) );
1157 RETURN FALSE;
1158 WHEN OTHERS THEN
1159 IF g_debug <= gme_debug.g_log_unexpected THEN
1160 gme_debug.put_line ( 'When others exception in '
1161 || g_pkg_name
1162 || '.'
1163 || l_api_name
1164 || ' Error is '
1165 || SQLERRM);
1166 END IF;
1167
1168 RETURN FALSE;
1169 END lock_row;
1170 END gme_batch_step_resources_dbl;