1 PACKAGE BODY gme_batch_step_chg_pvt AS
2 /* $Header: GMEVCHGB.pls 120.1 2005/06/03 13:43:54 appldev $*/
3 g_pkg_name CONSTANT VARCHAR2 (30) := 'gme_batch_step_chg_pvt';
4 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
5
6 /*===========================================================================================
7 Procedure
8 Set_activity_sequence_num
9 Description
10 This procedure is used to assign the sequence numbers to all the activities of a batch.
11 Parameters
12 pbatch_id the batch whose activities are to be numbered.
13 =============================================================================================*/
14 PROCEDURE set_activity_sequence_num (pbatch_id IN NUMBER)
15 IS
16 l_api_name CONSTANT VARCHAR2 (30) := 'set_activity_sequence_num';
17
18 CURSOR get_activities (v_batch_id NUMBER)
19 IS
20 SELECT gsa.batchstep_id
21 ,DECODE (gsa.sequence_dependent_ind, 1, 1, 2) seq_dep_order
22 ,gsa.offset_interval, gsa.activity
23 ,gsa.batchstep_activity_id
24 ,NVL (gsa.sequence_dependent_ind, -1)
25 sequence_dependent_ind
26 FROM gme_batch_step_activities gsa
27 WHERE gsa.batch_id = v_batch_id
28 ORDER BY 1, 2, 3, 4, 5;
29
30 v_batch_id NUMBER := 0;
31
32 TYPE act_rec IS RECORD (
33 batchstep_id gme_batch_step_activities.batchstep_id%TYPE
34 ,seq_dep_order NUMBER
35 ,offset_interval gme_batch_step_activities.offset_interval%TYPE
36 ,activity gme_batch_step_activities.activity%TYPE
37 ,batchstep_activity_id gme_batch_step_activities.batchstep_activity_id%TYPE
38 ,seq_dep_ind gme_batch_step_activities.sequence_dependent_ind%TYPE
39 );
40
41 TYPE act_tbl IS TABLE OF act_rec
42 INDEX BY BINARY_INTEGER;
43
44 act_tab act_tbl;
45 act_cnt NUMBER := 1;
46 seq_num NUMBER := 0;
47 err_num NUMBER := 0;
48 found_seq NUMBER := 0;
49 old_step NUMBER := -1;
50 lower_seq NUMBER := 0;
51 upper_seq NUMBER := 0;
52 x NUMBER := 0;
53 y NUMBER := 0;
54 BEGIN
55 /* the seq num will always start at 10 for the first activity after the
56 seq dep activity. */
57 seq_num := 10;
58 old_step := -1;
59 v_batch_id := pbatch_id;
60
61 OPEN get_activities (v_batch_id);
62
63 /* select all of the activities for the batch into the table check on the way
64 if there was any numbering done already. */
65 LOOP
66 FETCH get_activities
67 INTO act_tab (act_cnt);
68
69 EXIT WHEN get_activities%NOTFOUND;
70
71 IF act_tab (act_cnt).seq_dep_ind > 1 THEN
72 found_seq := 1;
73 END IF;
74
75 act_cnt := act_cnt + 1;
76 END LOOP;
77
78 act_cnt := act_cnt - 1;
79
80 /* loop through the activities to number them if needed */
81 FOR i IN 1 .. act_cnt LOOP
82 /* if the step changes we can reset the seq num to 100 */
83 IF old_step <> act_tab (i).batchstep_id THEN
84 seq_num := 100;
85 END IF;
86
87 /* this batch has not been numbered so we can just simply number each
88 activity incrementing by 100 after each write */
89 IF found_seq = 0 THEN
90 /* if the row is the seq dep row it has to be first so we can skip it as the
91 number in seq dep ind is already correct */
92 IF act_tab (i).seq_dep_ind = 1 THEN
93 NULL;
94 ELSE
95 /* set the seq dep ind to the current seq num */
96 act_tab (i).seq_dep_ind := seq_num;
97
98 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
99 gme_debug.put_line
100 ( g_pkg_name
101 || '.'
102 || l_api_name
103 || 'found_seq = 0,batchstep_activity_id is'
104 || act_tab (i).batchstep_activity_id);
105 gme_debug.put_line ( g_pkg_name
106 || '.'
107 || l_api_name
108 || 'seq. dep. id is'
109 || act_tab (i).seq_dep_ind);
110 END IF;
111
112 UPDATE gme_batch_step_activities
113 SET sequence_dependent_ind = act_tab (i).seq_dep_ind
114 ,last_update_date = SYSDATE
115 ,last_updated_by = gme_common_pvt.g_user_ident
116 ,last_update_login = gme_common_pvt.g_login_id
117 WHERE batchstep_activity_id =
118 act_tab (i).batchstep_activity_id;
119
120 IF SQL%NOTFOUND THEN
121 err_num := -1;
122 EXIT;
123 END IF;
124
125 /* increment the seq num for each processed except the seq dep activity */
126 seq_num := seq_num + 100;
127 END IF;
128 ELSE
129 /* this batch has been numbered so we just need to number the rows that
130 have not been numbered yet */
131 /* if the row is the seq dep row it has to be first so we can skip it as the
132 number in seq dep ind is already correct */
133 IF act_tab (i).seq_dep_ind = 1 THEN
134 NULL;
135 ELSE
136 /* the activity needs to be numbered */
137 IF act_tab (i).seq_dep_ind = -1 THEN
138 /* first we have to located the previous activity in the step if one
139 exists to get its seq number. If not set the lower limit to 0 */
140 x := i - 1;
141
142 IF x < 1 THEN
143 lower_seq := 0;
144 ELSIF act_tab (x).batchstep_id <> act_tab (i).batchstep_id THEN
145 lower_seq := 0;
146 ELSE
147 lower_seq := act_tab (x).seq_dep_ind;
148 END IF;
149
150 /* get the next activity in the step for the seq num if one exists.
151 if one does not exist leave the value for upper as -1 */
152 y := i + 1;
153 upper_seq := -1;
154
155 FOR j IN y .. act_cnt LOOP
156 IF act_tab (j).batchstep_id <> act_tab (i).batchstep_id THEN
157 EXIT;
158 ELSE
159 IF act_tab (j).seq_dep_ind > -1 THEN
160 upper_seq := act_tab (j).seq_dep_ind;
161 EXIT;
162 END IF;
163 END IF;
164 END LOOP;
165
166 /* if the upper has been set the it means we have to put the activity
167 before an existing numbered activity. take the midpoint between for
168 the new seq num */
169 IF upper_seq > -1 THEN
170 act_tab (i).seq_dep_ind :=
171 ROUND ( ( (lower_seq + upper_seq) / 2) );
172 ELSE
173 /* if the upper does not exist the we just need to added a new seq num
174 at the normal increment method */
175 act_tab (i).seq_dep_ind := lower_seq + 100;
176 END IF;
177
178 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
179 gme_debug.put_line
180 ( g_pkg_name
181 || '.'
182 || l_api_name
183 || 'found_seq = 1,batchstep_activity_id is'
184 || act_tab (i).batchstep_activity_id);
185 gme_debug.put_line ( g_pkg_name
186 || '.'
187 || l_api_name
188 || 'seq. dep. id is'
189 || act_tab (i).seq_dep_ind);
190 END IF;
191
192 UPDATE gme_batch_step_activities
193 SET sequence_dependent_ind = act_tab (i).seq_dep_ind
194 ,last_update_date = SYSDATE
195 ,last_updated_by = gme_common_pvt.g_user_ident
196 ,last_update_login =
197 gme_common_pvt.g_login_id
198 ----- Punit Kumar
199 WHERE batchstep_activity_id =
200 act_tab (i).batchstep_activity_id;
201
202 IF SQL%NOTFOUND THEN
203 err_num := -1;
204 EXIT;
205 END IF;
206 END IF;
207 END IF;
208 END IF;
209 END LOOP;
210
211 CLOSE get_activities;
212 END set_activity_sequence_num;
213
214 /*===========================================================================================
215 Procedure
216 Set_all_batch_activities
217 Description
218 This procedure is used to assign the sequence numbers to the activities of pending,wip
219 batchses.
220 =============================================================================================*/
221 PROCEDURE set_all_batch_activities
222 IS
223 l_gme_batch_ids_tab gme_batch_step_chg_pvt.gme_batch_ids_tab;
224
225 /* select only pending or wip batches */
226 CURSOR get_batches
227 IS
228 SELECT gbh.batch_id
229 FROM gme_batch_header gbh
230 WHERE gbh.batch_status IN (1, 2);
231
232 v_batch_id NUMBER := 0;
233 BEGIN
234 OPEN get_batches;
235
236 FETCH get_batches
237 BULK COLLECT INTO l_gme_batch_ids_tab;
238
239 CLOSE get_batches;
240
241 FOR i IN 1 .. l_gme_batch_ids_tab.COUNT LOOP
242 set_activity_sequence_num (l_gme_batch_ids_tab (i) );
243 END LOOP;
244
245 COMMIT;
246 END set_all_batch_activities;
247
248 /*===========================================================================================
249 Procedure
250 Set_sequence_dependent_id
251 Description
252 This procedure is used to assign the sequence numbers to the resouces by populating the
253 sequence_dependent_id field in the gme_batch_Step_resouces table.
254 Parameters
255 pbatch_id - This identifies the batch whose resources are to be numbered.
256 =============================================================================================*/
257 PROCEDURE set_sequence_dependent_id (pbatch_id IN NUMBER)
258 IS
259 /* this query will select all the resources that need to have the setup applied
260 the step resource must be defined at the plant and be scheduled to an
261 instance There must be an activity defined in the step as sequence dependent.
262 All of the resources in the step will have the setup applied, hence there is
263 no join through activity. we just need to confirm that sequences dependencies
264 will be applied in the step. The sequence type will be found for operation
265 specific or global, hence there will be two outer joins */
266 l_api_name CONSTANT VARCHAR2 (30) := 'set_sequence_dependent_id';
267
268 CURSOR get_batch_details (v_batch_id NUMBER)
269 IS
270 SELECT gsr.batchstep_resource_id, 1 sds_type, gst.seq_dep_id
271 FROM gme_batch_header gbh
272 ,gmd_recipe_validity_rules gvr
273 ,gme_batch_steps gbs
274 ,gme_batch_step_activities gsa
275 ,gme_batch_step_resources gsr
276 ,cr_rsrc_dtl crd
277 ,ic_item_mst iim
278 ,gmp_sequence_types gst
279 WHERE gbh.batch_id = v_batch_id
280 AND gbh.recipe_validity_rule_id = gvr.recipe_validity_rule_id
281 AND gbh.batch_id = gbs.batch_id
282 AND gbs.batchstep_id = gsr.batchstep_id
283 AND gvr.item_id = iim.item_id
284 AND iim.seq_category_id IS NOT NULL
285 AND gbs.batchstep_id = gsa.batchstep_id
286 AND gsa.sequence_dependent_ind = 1
287 AND gbh.organization_id = crd.organization_id
288 AND gsr.resources = crd.resources
289 AND crd.schedule_ind = 2
290 AND iim.seq_category_id = gst.category_id
291 AND gbs.oprn_id = gst.oprn_id
292 UNION ALL
293 SELECT gsr2.batchstep_resource_id, 2 sds_type, gst2.seq_dep_id
294 FROM gme_batch_header gbh2
295 ,gmd_recipe_validity_rules gvr2
296 ,gme_batch_steps gbs2
297 ,gme_batch_step_activities gsa2
298 ,gme_batch_step_resources gsr2
299 ,cr_rsrc_dtl crd2
300 ,gmp_sequence_types gst2
301 ,ic_item_mst iim2
302 WHERE gbh2.batch_id = v_batch_id
303 AND gbh2.recipe_validity_rule_id = gvr2.recipe_validity_rule_id
304 AND gbh2.batch_id = gbs2.batch_id
305 AND gbs2.batchstep_id = gsr2.batchstep_id
306 AND gvr2.item_id = iim2.item_id
307 AND iim2.seq_category_id IS NOT NULL
308 AND gbs2.batchstep_id = gsa2.batchstep_id
309 AND gsa2.sequence_dependent_ind = 1
310 AND gbh2.organization_id = crd2.organization_id
311 AND gsr2.resources = crd2.resources
312 AND crd2.schedule_ind = 2
313 AND -1 = gst2.oprn_id
314 AND iim2.seq_category_id = gst2.category_id
315 ORDER BY 1, 2;
316
317 v_batch_id NUMBER := 0;
318 v_batchstep_resource_id NUMBER := 0;
319 v_sds_type NUMBER := 0;
320 v_seq_dep_id NUMBER := 0;
321 last_res NUMBER := 0;
322 BEGIN
323 v_batch_id := pbatch_id;
324 last_res := -1;
325
326 OPEN get_batch_details (v_batch_id);
327
328 LOOP
329 FETCH get_batch_details
330 INTO v_batchstep_resource_id, v_sds_type, v_seq_dep_id;
331
332 EXIT WHEN get_batch_details%NOTFOUND;
333
334 /* if the operation specific type exists use that one */
335 /* if the operation specific does not exist use the global */
336 /* the operation or global existed apply it to the resource otherwise
337 do not update the row and go one to the next row */
338 IF (v_sds_type = 1)
339 OR (v_sds_type = 2 AND last_res <> v_batchstep_resource_id) THEN
340 last_res := v_batchstep_resource_id;
341
342 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
343 gme_debug.put_line ( g_pkg_name
344 || '.'
345 || l_api_name
346 || 'v_sds_type ='
347 || v_sds_type);
348 gme_debug.put_line ( g_pkg_name
349 || '.'
350 || l_api_name
351 || 'seq_dep_id ='
352 || v_seq_dep_id);
353 gme_debug.put_line ( g_pkg_name
354 || '.'
355 || l_api_name
356 || 'batchstep resource id = '
357 || v_batchstep_resource_id);
358 END IF;
359
360 UPDATE gme_batch_step_resources
361 SET sequence_dependent_id = v_seq_dep_id
362 ,last_update_date = SYSDATE
363 ,last_updated_by = gme_common_pvt.g_user_ident
364 ,last_update_login = gme_common_pvt.g_login_id --- Punit
365 WHERE batchstep_resource_id = v_batchstep_resource_id;
366 END IF;
367 END LOOP;
368
369 CLOSE get_batch_details;
370 END set_sequence_dependent_id;
371
372 /*===========================================================================================
373 Procedure
374 set_all_batch_sequences
375 Description
376 This procedure is used to assign the sequence dependent ids to the resouces of pending,wip
377 batches
378 =============================================================================================*/
379 PROCEDURE set_all_batch_sequences
380 IS
381 l_gme_batch_ids_tab gme_batch_ids_tab;
382
383 /* this cursor will get just the batches where a sequence dependent activity exists.
384 the batch also has to be pending or WIP for this to happen */
385 CURSOR get_batches
386 IS
387 SELECT gbh.batch_id
388 FROM gme_batch_header gbh
389 WHERE gbh.batch_status IN (1, 2)
390 AND gbh.batch_id IN (
391 SELECT gbh1.batch_id
392 FROM gme_batch_header gbh1
393 ,gme_batch_step_activities gsa
394 WHERE gbh.batch_status IN (1, 2)
395 AND gbh.batch_id = gsa.batch_id
396 AND gsa.sequence_dependent_ind = 1);
397
398 v_batch_id NUMBER := 0;
399 BEGIN
400 OPEN get_batches;
401
402 FETCH get_batches
403 BULK COLLECT INTO l_gme_batch_ids_tab;
404
405 CLOSE get_batches;
406
407 FOR i IN 1 .. l_gme_batch_ids_tab.COUNT LOOP
408 set_sequence_dependent_id (l_gme_batch_ids_tab (i) );
409 END LOOP;
410
411 COMMIT;
412 END set_all_batch_sequences;
413
414 /*===========================================================================================
415 Procedure
416 Clear_charge_dates
417 Description
418 This procedure clears the charge dates.If the batchstep_id argument is passed then only the
419 charge dates associated to that step will be cleared.If the batchstep_id argument is null,
420 then the dates of all charges associated with the p_batch_id parameter will be cleared.
421
422 =============================================================================================*/
423 PROCEDURE clear_charge_dates (
424 p_batch_id IN NUMBER
425 ,p_batchstep_id IN NUMBER DEFAULT NULL
426 ,x_return_status OUT NOCOPY VARCHAR2)
427 IS
428 l_api_name CONSTANT VARCHAR2 (30) := 'CLEAR_CHARGE_DATES';
429
430 CURSOR cur_get_batchstep_ids (v_batch_id NUMBER)
431 IS
432 SELECT batchstep_id
433 FROM gme_batch_steps
434 WHERE batch_id = v_batch_id;
435
436 CURSOR cur_is_charge_associated (
437 v_batch_id NUMBER
438 ,v_batchstep_id NUMBER)
439 IS
440 SELECT 1
441 FROM gme_batch_step_charges
442 WHERE batch_id = v_batch_id
443 AND batchstep_id = v_batchstep_id
444 AND ROWNUM = 1;
445
446 l_gme_batchstep_ids_tab gme_batch_step_chg_pvt.gme_batchstep_ids_tab;
447 l_cur_is_charge_associated cur_is_charge_associated%ROWTYPE;
448 l_return_status VARCHAR2 (1);
449 BEGIN
450 /* Set the return status to success initially */
451 x_return_status := fnd_api.g_ret_sts_success;
452
453 IF p_batchstep_id IS NOT NULL THEN
454 --clear the dates of only charges associated with the step.
455 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
456 gme_debug.put_line ( g_pkg_name
457 || '.'
458 || l_api_name
459 || 'batch,step ids are'
460 || p_batch_id
461 || p_batchstep_id);
462 END IF;
463
464 OPEN cur_is_charge_associated (p_batch_id, p_batchstep_id);
465
466 FETCH cur_is_charge_associated
467 INTO l_cur_is_charge_associated;
468
469 IF cur_is_charge_associated%FOUND THEN
470 CLOSE cur_is_charge_associated;
471
472 UPDATE gme_batch_step_charges
473 SET plan_start_date = NULL
474 ,plan_cmplt_date = NULL
475 ,last_update_date = gme_common_pvt.g_timestamp
476 ,last_updated_by = gme_common_pvt.g_user_ident
477 ,last_update_login = gme_common_pvt.g_login_id --- Punit
478 WHERE batch_id = p_batch_id AND batchstep_id = p_batchstep_id;
479 ELSE
480 CLOSE cur_is_charge_associated;
481 END IF;
482 ELSE
483 --p_batchstep_id not passed.clear the dates for all charges associated with the batch.
484 OPEN cur_get_batchstep_ids (p_batch_id);
485
486 FETCH cur_get_batchstep_ids
487 BULK COLLECT INTO l_gme_batchstep_ids_tab;
488
489 CLOSE cur_get_batchstep_ids;
490
491 FOR i IN 1 .. l_gme_batchstep_ids_tab.COUNT LOOP
492 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
493 gme_debug.put_line ( g_pkg_name
494 || l_api_name
495 || 'batch,step ids are'
496 || p_batch_id
497 || l_gme_batchstep_ids_tab (i) );
498 END IF;
499
500 OPEN cur_is_charge_associated (p_batch_id
501 ,l_gme_batchstep_ids_tab (i) );
502
503 FETCH cur_is_charge_associated
504 INTO l_cur_is_charge_associated;
505
506 IF cur_is_charge_associated%FOUND THEN
507 CLOSE cur_is_charge_associated;
508
509 UPDATE gme_batch_step_charges
510 SET plan_start_date = NULL
511 ,plan_cmplt_date = NULL
512 ,last_update_date = gme_common_pvt.g_timestamp
513 ,last_updated_by = gme_common_pvt.g_user_ident
514 ,last_update_login = gme_common_pvt.g_login_id --- Punit
515 WHERE batch_id = p_batch_id
516 AND batchstep_id = l_gme_batchstep_ids_tab (i);
517 ELSE
518 CLOSE cur_is_charge_associated;
519 END IF;
520 END LOOP;
521 END IF;
522 EXCEPTION
523 WHEN OTHERS THEN
524 x_return_status := fnd_api.g_ret_sts_unexp_error;
525 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
526 gme_debug.put_line ( g_pkg_name
527 || '.'
528 || l_api_name
529 || ':'
530 || ' OTHERS:'
531 || SQLERRM);
532 END clear_charge_dates;
533
534 /*===========================================================================================
535 Procedure
536 Clear_charges
537 Description
538 This procedure clears the charges.If the batchstep_id argument is passed then only the
539 charges associated to that step will be cleared.If the batchstep_id argument is null,
540 then all charges associated with the p_batch_id parameter will be cleared.
541
542 =============================================================================================*/
543 PROCEDURE clear_charges (
544 p_batch_id IN NUMBER
545 ,p_batchstep_id IN NUMBER DEFAULT NULL
546 ,x_return_status OUT NOCOPY VARCHAR2)
547 IS
548 l_api_name CONSTANT VARCHAR2 (30) := 'CLEAR_CHARGES';
549
550 CURSOR cur_get_batchstep_ids (v_batch_id NUMBER)
551 IS
552 SELECT batchstep_id
553 FROM gme_batch_steps
554 WHERE batch_id = v_batch_id;
555
556 CURSOR cur_is_charge_associated (
557 v_batch_id NUMBER
558 ,v_batchstep_id NUMBER)
559 IS
560 SELECT 1
561 FROM gme_batch_step_charges
562 WHERE batch_id = v_batch_id
563 AND batchstep_id = v_batchstep_id
564 AND ROWNUM = 1;
565
566 l_gme_batchstep_ids_tab gme_batch_step_chg_pvt.gme_batchstep_ids_tab;
567 l_cur_is_charge_associated cur_is_charge_associated%ROWTYPE;
568 l_return_status VARCHAR2 (1);
569 l_batch_step_charges_in gme_batch_step_charges%ROWTYPE;
570 clear_chg_error EXCEPTION;
571 BEGIN
572 /* Set the return status to success initially */
573 x_return_status := fnd_api.g_ret_sts_success;
574
575 IF p_batchstep_id IS NOT NULL THEN
576 --clear only charges associated with the step.
577 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
578 gme_debug.put_line ( g_pkg_name
579 || '.'
580 || l_api_name
581 || ' batch step ids are'
582 || p_batchstep_id
583 || p_batch_id);
584 END IF;
585
586 OPEN cur_is_charge_associated (p_batch_id, p_batchstep_id);
587
588 FETCH cur_is_charge_associated
589 INTO l_cur_is_charge_associated;
590
591 IF cur_is_charge_associated%FOUND THEN
592 CLOSE cur_is_charge_associated;
593
594 l_batch_step_charges_in.batch_id := p_batch_id;
595 l_batch_step_charges_in.batchstep_id := p_batchstep_id;
596
597 IF NOT (gme_batch_step_charge_dbl.delete_row
598 (p_batch_step_charges_in => l_batch_step_charges_in) ) THEN
599 RAISE clear_chg_error;
600 END IF;
601 ELSE
602 CLOSE cur_is_charge_associated;
603 END IF;
604 ELSE
605 --p_batchstep_id not passed.clear the dates for all charges associated with the batch.
606 OPEN cur_get_batchstep_ids (p_batch_id);
607
608 FETCH cur_get_batchstep_ids
609 BULK COLLECT INTO l_gme_batchstep_ids_tab;
610
611 CLOSE cur_get_batchstep_ids;
612
613 FOR i IN 1 .. l_gme_batchstep_ids_tab.COUNT LOOP
614 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
615 gme_debug.put_line ( g_pkg_name
616 || '.'
617 || l_api_name
618 || 'batch,step ids are'
619 || p_batch_id
620 || l_gme_batchstep_ids_tab (i) );
621 END IF;
622
623 OPEN cur_is_charge_associated (p_batch_id
624 ,l_gme_batchstep_ids_tab (i) );
625
626 FETCH cur_is_charge_associated
627 INTO l_cur_is_charge_associated;
628
629 IF cur_is_charge_associated%FOUND THEN
630 CLOSE cur_is_charge_associated;
631
632 l_batch_step_charges_in.batch_id := p_batch_id;
633 l_batch_step_charges_in.batchstep_id :=
634 l_gme_batchstep_ids_tab (i);
635
636 IF NOT (gme_batch_step_charge_dbl.delete_row
637 (p_batch_step_charges_in => l_batch_step_charges_in) ) THEN
638 RAISE clear_chg_error;
639 END IF;
640 ELSE
641 CLOSE cur_is_charge_associated;
642 END IF;
643 END LOOP;
644 END IF;
645 EXCEPTION
646 WHEN clear_chg_error THEN
647 x_return_status := fnd_api.g_ret_sts_error;
648 WHEN OTHERS THEN
649 x_return_status := fnd_api.g_ret_sts_unexp_error;
650 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
651 gme_debug.put_line ( g_pkg_name
652 || '.'
653 || l_api_name
654 || ':'
655 || ' OTHERS:'
656 || SQLERRM);
657 END clear_charges;
658
659 /*===========================================================================================
660 Procedure
661 calc_activity_sequence_number
662 Description
663 This procedure calcualates the activity_sequence_number of gme_batch_step_charges table
664 Parameters
665 p_batchstep_id Batchstep id of the step for whose charge details,the activity
666 sequence is to be calculated.
667 p_resoures Resource associated with the charges for the step represented by
668 p_batchstep_id
669 x_act_seq_num The resultant activity_sequence_number
670 x_return_status outcome of the API call
671 S - Success
672 E - Error
673
674 HISTORY
675 Rishi Varma B3718176 14-07-2004
676 Created.
677 =============================================================================================*/
678 PROCEDURE calc_activity_sequence_number (
679 p_batchstep_id IN gme_batch_steps.batchstep_id%TYPE
680 ,p_resources IN gme_batch_step_resources.resources%TYPE
681 ,x_act_seq_num OUT NOCOPY NUMBER
682 ,x_return_status OUT NOCOPY VARCHAR2)
683 IS
684 CURSOR cur_get_seq_dep_ind (
685 v_resources gme_batch_step_resources.resources%TYPE
686 ,v_batchstep_id gme_batch_steps.batchstep_id%TYPE)
687 IS
688 SELECT MIN (a.sequence_dependent_ind)
689 FROM gme_batch_step_activities a, gme_batch_step_resources r
690 WHERE r.resources = v_resources
691 AND r.batchstep_activity_id = a.batchstep_activity_id
692 AND a.batchstep_id = v_batchstep_id
693 AND r.batchstep_id = v_batchstep_id
694 AND r.scale_type = 2;
695
696 l_api_name CONSTANT VARCHAR2 (30) := 'CALC_ACTIVITY_SEQUENCE_NUMBER';
697 l_activity VARCHAR2 (20);
698 BEGIN
699 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
700 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ' Entering');
701 END IF;
702
703 /* Initialise the return status to success*/
704 x_return_status := fnd_api.g_ret_sts_success;
705
706 OPEN cur_get_seq_dep_ind (p_resources, p_batchstep_id);
707
708 FETCH cur_get_seq_dep_ind
709 INTO x_act_seq_num;
710
711 IF cur_get_seq_dep_ind%NOTFOUND THEN
712 CLOSE cur_get_seq_dep_ind;
713 END IF;
714
715 CLOSE cur_get_seq_dep_ind;
716
717 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
718 gme_debug.put_line ( g_pkg_name
719 || '.'
720 || l_api_name
721 || ' In loop:seq dep ind,activity are'
722 || x_act_seq_num);
723 END IF;
724
725 IF x_act_seq_num IS NULL THEN
726 x_return_status := fnd_api.g_ret_sts_error;
727 END IF;
728 END calc_activity_sequence_number;
729
730 /*===========================================================================================
731 Procedure
732 populate_charges_table
733 Description
734 This procedure populates the charge details table gme_batch_step_charges.
735 Parameters
736 p_batchstep_charges_in The gme_batch_step_charges%ROWTYPE variable used to populate the
737 table.
738 p_no_of_charges The number of calculated charges.
739 p_remaining_quantity The quantity for the last charge.
740 x_return_status outcome of the API call
741 S - Success
742 E - Error
743 U - Unexpected error
744 The p_batchstep_charges parameter should have the following attributes populated;batch_id,
745 batchstep_id,charge_quantity,resources,
746 HISTORY
747 Rishi Varma B3718176 23-07-2004
748 Created.
749 =============================================================================================*/
750 PROCEDURE populate_charges_table (
751 p_batchstep_charges_in IN gme_batch_step_charges%ROWTYPE
752 ,p_no_of_charges IN NUMBER
753 ,p_remaining_quantity IN NUMBER
754 ,x_return_status OUT NOCOPY VARCHAR2)
755 IS
756 l_api_name CONSTANT VARCHAR2 (30) := 'POPULATE_CHARGES_TABLE';
757 l_batchstep_charges_in gme_batch_step_charges%ROWTYPE;
758 l_activity_sequence_number NUMBER;
759 l_return_status VARCHAR2 (1);
760 error_act_seq_num_calc EXCEPTION;
761 error_charge_insert EXCEPTION;
762 BEGIN
763 x_return_status := fnd_api.g_ret_sts_success;
764 l_batchstep_charges_in := p_batchstep_charges_in;
765 /*Calculating the activity sequence_number*/
766 gme_batch_step_chg_pvt.calc_activity_sequence_number
767 (p_batchstep_id => l_batchstep_charges_in.batchstep_id
768 ,p_resources => l_batchstep_charges_in.resources
769 ,x_act_seq_num => l_activity_sequence_number
770 ,x_return_status => l_return_status);
771
772 IF x_return_status <> fnd_api.g_ret_sts_success THEN
773 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
774 gme_debug.put_line
775 ('Error in calculating activity sequence number');
776 END IF;
777
778 RAISE error_act_seq_num_calc;
779 END IF;
780
781 l_batchstep_charges_in.activity_sequence_number :=
782 l_activity_sequence_number;
783
784 FOR i IN 1 .. (p_no_of_charges - 1) LOOP
785 l_batchstep_charges_in.charge_number := i;
786
787 IF (gme_batch_step_charge_dbl.insert_row
788 (p_batch_step_charges_in => l_batchstep_charges_in
789 ,x_batch_step_charges => l_batchstep_charges_in) ) THEN
790 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
791 gme_debug.put_line
792 ('sucessfully inserted into batchstep charges');
793 END IF;
794 ELSE
795 RAISE error_charge_insert;
796 END IF;
797 END LOOP;
798
799 --Populating the details of the last charge.
800 IF p_remaining_quantity <> -1 THEN
801 l_batchstep_charges_in.charge_quantity := p_remaining_quantity;
802 END IF;
803
804 l_batchstep_charges_in.charge_number := p_no_of_charges;
805
806 IF (gme_batch_step_charge_dbl.insert_row
807 (p_batch_step_charges_in => l_batchstep_charges_in
808 ,x_batch_step_charges => l_batchstep_charges_in) ) THEN
809 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
810 gme_debug.put_line ('sucessfully inserted into batchstep charges');
811 END IF;
812 ELSE
813 RAISE error_charge_insert;
814 END IF;
815 EXCEPTION
816 WHEN error_charge_insert OR error_act_seq_num_calc THEN
817 x_return_status := fnd_api.g_ret_sts_error;
818 WHEN OTHERS THEN
819 x_return_status := fnd_api.g_ret_sts_unexp_error;
820 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
821 gme_debug.put_line ( g_pkg_name
822 || '.'
823 || l_api_name
824 || ':'
825 || ' OTHERS:'
826 || SQLERRM);
827 END;
828 END gme_batch_step_chg_pvt;
[Home] [Help]