1 PACKAGE BODY gme_batch_step_chg_pvt AS
2 /* $Header: GMEVCHGB.pls 120.1.12010000.2 2009/07/24 16:25:26 gmurator ship $*/
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 -- Bug 8708957 - Correct cursor to use mtl_system_items and proper joins also.
269 CURSOR get_batch_details (v_batch_id NUMBER)
270 IS
271 SELECT gsr.batchstep_resource_id, 1 sds_type, gst.seq_dep_id
272 FROM gme_batch_header gbh
273 ,gmd_recipe_validity_rules gvr
274 ,gme_batch_steps gbs
275 ,gme_batch_step_activities gsa
276 ,gme_batch_step_resources gsr
277 ,cr_rsrc_dtl crd
278 ,mtl_system_items iim -- 8708957 - Change this from ic_item_mst to mtl_system_items
279 ,gmp_sequence_types gst
280 -- 8708957 - two more tables added
281 ,mtl_item_categories mic
282 ,mtl_default_category_sets dcs
283 WHERE gbh.batch_id = v_batch_id
284 AND gbh.recipe_validity_rule_id = gvr.recipe_validity_rule_id
285 AND gbh.batch_id = gbs.batch_id
286 AND gbs.batchstep_id = gsr.batchstep_id
287 AND gvr.inventory_item_id = iim.inventory_item_id -- 8708957 - Changed
288 AND gbh.organization_id = iim.organization_id -- 8708957 - Added
289 AND gbs.batchstep_id = gsa.batchstep_id
290 AND gsa.sequence_dependent_ind = 1
291 AND gbh.organization_id = crd.organization_id
292 AND gsr.resources = crd.resources
293 AND crd.schedule_ind = 2
294 AND gbs.oprn_id = gst.oprn_id
295 -- 8708957 - Following lines added
296 AND mic.CATEGORY_ID = gst.CATEGORY_ID
297 AND mic.inventory_item_id = iim.inventory_item_id
298 AND gbh.organization_id = mic.organization_id
299 AND dcs.CATEGORY_SET_ID = mic.CATEGORY_SET_ID
300 -- 8708957 - 14 is the seeded value for Sequence Dependency Class. DO NOT CHANGE !!
301 AND dcs.FUNCTIONAL_AREA_ID = 14
302 UNION ALL
303 SELECT gsr2.batchstep_resource_id, 2 sds_type, gst2.seq_dep_id
304 FROM gme_batch_header gbh2
305 ,gmd_recipe_validity_rules gvr2
306 ,gme_batch_steps gbs2
307 ,gme_batch_step_activities gsa2
308 ,gme_batch_step_resources gsr2
309 ,cr_rsrc_dtl crd2
310 ,gmp_sequence_types gst2
311 ,mtl_system_items iim2 -- 8708957 - Change this from ic_item_mst to mtl_system_items
312 -- 8708957 - two more tables added
313 ,mtl_item_categories mic2
314 ,mtl_default_category_sets dcs2
315 WHERE gbh2.batch_id = v_batch_id
316 AND gbh2.recipe_validity_rule_id = gvr2.recipe_validity_rule_id
317 AND gbh2.batch_id = gbs2.batch_id
318 AND gbs2.batchstep_id = gsr2.batchstep_id
319 AND gvr2.inventory_item_id = iim2.inventory_item_id -- 8708957 - Changed
320 AND gbh2.organization_id = iim2.organization_id -- 8708957 - Added
321 AND gbs2.batchstep_id = gsa2.batchstep_id
322 AND gsa2.sequence_dependent_ind = 1
323 AND gbh2.organization_id = crd2.organization_id
324 AND gsr2.resources = crd2.resources
325 AND crd2.schedule_ind = 2
326 AND -1 = gst2.oprn_id
327 -- 8708957 - Following lines added
328 AND mic2.CATEGORY_ID = gst2.CATEGORY_ID
329 AND mic2.inventory_item_id = iim2.inventory_item_id
330 AND gbh2.organization_id = mic2.organization_id
331 AND dcs2.CATEGORY_SET_ID = mic2.CATEGORY_SET_ID
332 -- 8708957 - 14 is the seeded value for Sequence Dependency Class. DO NOT CHANGE !! UNION ALL
333 AND dcs2.FUNCTIONAL_AREA_ID = 14
334 ORDER BY 1, 2;
335
336 -- Bug 8708957 - Comment out original cursor which was incorrectly using ic_item_mst
337 /*
338 CURSOR get_batch_details (v_batch_id NUMBER)
339 IS
340 SELECT gsr.batchstep_resource_id, 1 sds_type, gst.seq_dep_id
341 FROM gme_batch_header gbh
342 ,gmd_recipe_validity_rules gvr
343 ,gme_batch_steps gbs
344 ,gme_batch_step_activities gsa
345 ,gme_batch_step_resources gsr
346 ,cr_rsrc_dtl crd
347 ,ic_item_mst iim
348 ,gmp_sequence_types gst
349 WHERE gbh.batch_id = v_batch_id
350 AND gbh.recipe_validity_rule_id = gvr.recipe_validity_rule_id
351 AND gbh.batch_id = gbs.batch_id
352 AND gbs.batchstep_id = gsr.batchstep_id
353 AND gvr.item_id = iim.item_id
354 AND iim.seq_category_id IS NOT NULL
355 AND gbs.batchstep_id = gsa.batchstep_id
356 AND gsa.sequence_dependent_ind = 1
357 AND gbh.organization_id = crd.organization_id
358 AND gsr.resources = crd.resources
359 AND crd.schedule_ind = 2
360 AND iim.seq_category_id = gst.category_id
361 AND gbs.oprn_id = gst.oprn_id
362 UNION ALL
363 SELECT gsr2.batchstep_resource_id, 2 sds_type, gst2.seq_dep_id
364 FROM gme_batch_header gbh2
365 ,gmd_recipe_validity_rules gvr2
366 ,gme_batch_steps gbs2
367 ,gme_batch_step_activities gsa2
368 ,gme_batch_step_resources gsr2
369 ,cr_rsrc_dtl crd2
370 ,gmp_sequence_types gst2
371 ,ic_item_mst iim2
372 WHERE gbh2.batch_id = v_batch_id
373 AND gbh2.recipe_validity_rule_id = gvr2.recipe_validity_rule_id
374 AND gbh2.batch_id = gbs2.batch_id
375 AND gbs2.batchstep_id = gsr2.batchstep_id
376 AND gvr2.item_id = iim2.item_id
377 AND iim2.seq_category_id IS NOT NULL
378 AND gbs2.batchstep_id = gsa2.batchstep_id
379 AND gsa2.sequence_dependent_ind = 1
380 AND gbh2.organization_id = crd2.organization_id
381 AND gsr2.resources = crd2.resources
382 AND crd2.schedule_ind = 2
383 AND -1 = gst2.oprn_id
384 AND iim2.seq_category_id = gst2.category_id
385 ORDER BY 1, 2;
386 */
387 v_batch_id NUMBER := 0;
388 v_batchstep_resource_id NUMBER := 0;
389 v_sds_type NUMBER := 0;
390 v_seq_dep_id NUMBER := 0;
391 last_res NUMBER := 0;
392 BEGIN
393 v_batch_id := pbatch_id;
394 last_res := -1;
395
396 OPEN get_batch_details (v_batch_id);
397
398 LOOP
399 FETCH get_batch_details
400 INTO v_batchstep_resource_id, v_sds_type, v_seq_dep_id;
401
402 EXIT WHEN get_batch_details%NOTFOUND;
403
404 /* if the operation specific type exists use that one */
405 /* if the operation specific does not exist use the global */
406 /* the operation or global existed apply it to the resource otherwise
407 do not update the row and go one to the next row */
408 IF (v_sds_type = 1)
409 OR (v_sds_type = 2 AND last_res <> v_batchstep_resource_id) THEN
410 last_res := v_batchstep_resource_id;
411
412 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
413 gme_debug.put_line ( g_pkg_name
414 || '.'
415 || l_api_name
416 || 'v_sds_type ='
417 || v_sds_type);
418 gme_debug.put_line ( g_pkg_name
419 || '.'
420 || l_api_name
421 || 'seq_dep_id ='
422 || v_seq_dep_id);
423 gme_debug.put_line ( g_pkg_name
424 || '.'
425 || l_api_name
426 || 'batchstep resource id = '
427 || v_batchstep_resource_id);
428 END IF;
429
430 UPDATE gme_batch_step_resources
431 SET sequence_dependent_id = v_seq_dep_id
432 ,last_update_date = SYSDATE
433 ,last_updated_by = gme_common_pvt.g_user_ident
434 ,last_update_login = gme_common_pvt.g_login_id --- Punit
435 WHERE batchstep_resource_id = v_batchstep_resource_id;
436 END IF;
437 END LOOP;
438
439 CLOSE get_batch_details;
440 END set_sequence_dependent_id;
441
442 /*===========================================================================================
443 Procedure
444 set_all_batch_sequences
445 Description
446 This procedure is used to assign the sequence dependent ids to the resouces of pending,wip
447 batches
448 =============================================================================================*/
449 PROCEDURE set_all_batch_sequences
450 IS
451 l_gme_batch_ids_tab gme_batch_ids_tab;
452
453 /* this cursor will get just the batches where a sequence dependent activity exists.
454 the batch also has to be pending or WIP for this to happen */
455 CURSOR get_batches
456 IS
457 SELECT gbh.batch_id
458 FROM gme_batch_header gbh
459 WHERE gbh.batch_status IN (1, 2)
460 AND gbh.batch_id IN (
461 SELECT gbh1.batch_id
462 FROM gme_batch_header gbh1
463 ,gme_batch_step_activities gsa
464 WHERE gbh.batch_status IN (1, 2)
465 AND gbh.batch_id = gsa.batch_id
466 AND gsa.sequence_dependent_ind = 1);
467
468 v_batch_id NUMBER := 0;
469 BEGIN
470 OPEN get_batches;
471
472 FETCH get_batches
473 BULK COLLECT INTO l_gme_batch_ids_tab;
474
475 CLOSE get_batches;
476
477 FOR i IN 1 .. l_gme_batch_ids_tab.COUNT LOOP
478 set_sequence_dependent_id (l_gme_batch_ids_tab (i) );
479 END LOOP;
480
481 COMMIT;
482 END set_all_batch_sequences;
483
484 /*===========================================================================================
485 Procedure
486 Clear_charge_dates
487 Description
488 This procedure clears the charge dates.If the batchstep_id argument is passed then only the
489 charge dates associated to that step will be cleared.If the batchstep_id argument is null,
490 then the dates of all charges associated with the p_batch_id parameter will be cleared.
491
492 =============================================================================================*/
493 PROCEDURE clear_charge_dates (
494 p_batch_id IN NUMBER
495 ,p_batchstep_id IN NUMBER DEFAULT NULL
496 ,x_return_status OUT NOCOPY VARCHAR2)
497 IS
498 l_api_name CONSTANT VARCHAR2 (30) := 'CLEAR_CHARGE_DATES';
499
500 CURSOR cur_get_batchstep_ids (v_batch_id NUMBER)
501 IS
502 SELECT batchstep_id
503 FROM gme_batch_steps
504 WHERE batch_id = v_batch_id;
505
506 CURSOR cur_is_charge_associated (
507 v_batch_id NUMBER
508 ,v_batchstep_id NUMBER)
509 IS
510 SELECT 1
511 FROM gme_batch_step_charges
512 WHERE batch_id = v_batch_id
513 AND batchstep_id = v_batchstep_id
514 AND ROWNUM = 1;
515
516 l_gme_batchstep_ids_tab gme_batch_step_chg_pvt.gme_batchstep_ids_tab;
517 l_cur_is_charge_associated cur_is_charge_associated%ROWTYPE;
518 l_return_status VARCHAR2 (1);
519 BEGIN
520 /* Set the return status to success initially */
521 x_return_status := fnd_api.g_ret_sts_success;
522
523 IF p_batchstep_id IS NOT NULL THEN
524 --clear the dates of only charges associated with the step.
525 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
526 gme_debug.put_line ( g_pkg_name
527 || '.'
528 || l_api_name
529 || 'batch,step ids are'
530 || p_batch_id
531 || p_batchstep_id);
532 END IF;
533
534 OPEN cur_is_charge_associated (p_batch_id, p_batchstep_id);
535
536 FETCH cur_is_charge_associated
537 INTO l_cur_is_charge_associated;
538
539 IF cur_is_charge_associated%FOUND THEN
540 CLOSE cur_is_charge_associated;
541
542 UPDATE gme_batch_step_charges
543 SET plan_start_date = NULL
544 ,plan_cmplt_date = NULL
545 ,last_update_date = gme_common_pvt.g_timestamp
546 ,last_updated_by = gme_common_pvt.g_user_ident
547 ,last_update_login = gme_common_pvt.g_login_id --- Punit
548 WHERE batch_id = p_batch_id AND batchstep_id = p_batchstep_id;
549 ELSE
550 CLOSE cur_is_charge_associated;
551 END IF;
552 ELSE
553 --p_batchstep_id not passed.clear the dates for all charges associated with the batch.
554 OPEN cur_get_batchstep_ids (p_batch_id);
555
556 FETCH cur_get_batchstep_ids
557 BULK COLLECT INTO l_gme_batchstep_ids_tab;
558
559 CLOSE cur_get_batchstep_ids;
560
561 FOR i IN 1 .. l_gme_batchstep_ids_tab.COUNT LOOP
562 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
563 gme_debug.put_line ( g_pkg_name
564 || l_api_name
565 || 'batch,step ids are'
566 || p_batch_id
567 || l_gme_batchstep_ids_tab (i) );
568 END IF;
569
570 OPEN cur_is_charge_associated (p_batch_id
571 ,l_gme_batchstep_ids_tab (i) );
572
573 FETCH cur_is_charge_associated
574 INTO l_cur_is_charge_associated;
575
576 IF cur_is_charge_associated%FOUND THEN
577 CLOSE cur_is_charge_associated;
578
579 UPDATE gme_batch_step_charges
580 SET plan_start_date = NULL
581 ,plan_cmplt_date = NULL
582 ,last_update_date = gme_common_pvt.g_timestamp
583 ,last_updated_by = gme_common_pvt.g_user_ident
584 ,last_update_login = gme_common_pvt.g_login_id --- Punit
585 WHERE batch_id = p_batch_id
586 AND batchstep_id = l_gme_batchstep_ids_tab (i);
587 ELSE
588 CLOSE cur_is_charge_associated;
589 END IF;
590 END LOOP;
591 END IF;
592 EXCEPTION
593 WHEN OTHERS THEN
594 x_return_status := fnd_api.g_ret_sts_unexp_error;
595 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
596 gme_debug.put_line ( g_pkg_name
597 || '.'
598 || l_api_name
599 || ':'
600 || ' OTHERS:'
601 || SQLERRM);
602 END clear_charge_dates;
603
604 /*===========================================================================================
605 Procedure
606 Clear_charges
607 Description
608 This procedure clears the charges.If the batchstep_id argument is passed then only the
609 charges associated to that step will be cleared.If the batchstep_id argument is null,
610 then all charges associated with the p_batch_id parameter will be cleared.
611
612 =============================================================================================*/
613 PROCEDURE clear_charges (
614 p_batch_id IN NUMBER
615 ,p_batchstep_id IN NUMBER DEFAULT NULL
616 ,x_return_status OUT NOCOPY VARCHAR2)
617 IS
618 l_api_name CONSTANT VARCHAR2 (30) := 'CLEAR_CHARGES';
619
620 CURSOR cur_get_batchstep_ids (v_batch_id NUMBER)
621 IS
622 SELECT batchstep_id
623 FROM gme_batch_steps
624 WHERE batch_id = v_batch_id;
625
626 CURSOR cur_is_charge_associated (
627 v_batch_id NUMBER
628 ,v_batchstep_id NUMBER)
629 IS
630 SELECT 1
631 FROM gme_batch_step_charges
632 WHERE batch_id = v_batch_id
633 AND batchstep_id = v_batchstep_id
634 AND ROWNUM = 1;
635
636 l_gme_batchstep_ids_tab gme_batch_step_chg_pvt.gme_batchstep_ids_tab;
637 l_cur_is_charge_associated cur_is_charge_associated%ROWTYPE;
638 l_return_status VARCHAR2 (1);
639 l_batch_step_charges_in gme_batch_step_charges%ROWTYPE;
640 clear_chg_error EXCEPTION;
641 BEGIN
642 /* Set the return status to success initially */
643 x_return_status := fnd_api.g_ret_sts_success;
644
645 IF p_batchstep_id IS NOT NULL THEN
646 --clear only charges associated with the step.
647 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
648 gme_debug.put_line ( g_pkg_name
649 || '.'
650 || l_api_name
651 || ' batch step ids are'
652 || p_batchstep_id
653 || p_batch_id);
654 END IF;
655
656 OPEN cur_is_charge_associated (p_batch_id, p_batchstep_id);
657
658 FETCH cur_is_charge_associated
659 INTO l_cur_is_charge_associated;
660
661 IF cur_is_charge_associated%FOUND THEN
662 CLOSE cur_is_charge_associated;
663
664 l_batch_step_charges_in.batch_id := p_batch_id;
665 l_batch_step_charges_in.batchstep_id := p_batchstep_id;
666
667 IF NOT (gme_batch_step_charge_dbl.delete_row
668 (p_batch_step_charges_in => l_batch_step_charges_in) ) THEN
669 RAISE clear_chg_error;
670 END IF;
671 ELSE
672 CLOSE cur_is_charge_associated;
673 END IF;
674 ELSE
675 --p_batchstep_id not passed.clear the dates for all charges associated with the batch.
676 OPEN cur_get_batchstep_ids (p_batch_id);
677
678 FETCH cur_get_batchstep_ids
679 BULK COLLECT INTO l_gme_batchstep_ids_tab;
680
681 CLOSE cur_get_batchstep_ids;
682
683 FOR i IN 1 .. l_gme_batchstep_ids_tab.COUNT LOOP
684 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
685 gme_debug.put_line ( g_pkg_name
686 || '.'
687 || l_api_name
688 || 'batch,step ids are'
689 || p_batch_id
690 || l_gme_batchstep_ids_tab (i) );
691 END IF;
692
693 OPEN cur_is_charge_associated (p_batch_id
694 ,l_gme_batchstep_ids_tab (i) );
695
696 FETCH cur_is_charge_associated
697 INTO l_cur_is_charge_associated;
698
699 IF cur_is_charge_associated%FOUND THEN
700 CLOSE cur_is_charge_associated;
701
702 l_batch_step_charges_in.batch_id := p_batch_id;
703 l_batch_step_charges_in.batchstep_id :=
704 l_gme_batchstep_ids_tab (i);
705
706 IF NOT (gme_batch_step_charge_dbl.delete_row
707 (p_batch_step_charges_in => l_batch_step_charges_in) ) THEN
708 RAISE clear_chg_error;
709 END IF;
710 ELSE
711 CLOSE cur_is_charge_associated;
712 END IF;
713 END LOOP;
714 END IF;
715 EXCEPTION
716 WHEN clear_chg_error THEN
717 x_return_status := fnd_api.g_ret_sts_error;
718 WHEN OTHERS THEN
719 x_return_status := fnd_api.g_ret_sts_unexp_error;
720 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
721 gme_debug.put_line ( g_pkg_name
722 || '.'
723 || l_api_name
724 || ':'
725 || ' OTHERS:'
726 || SQLERRM);
727 END clear_charges;
728
729 /*===========================================================================================
730 Procedure
731 calc_activity_sequence_number
732 Description
733 This procedure calcualates the activity_sequence_number of gme_batch_step_charges table
734 Parameters
735 p_batchstep_id Batchstep id of the step for whose charge details,the activity
736 sequence is to be calculated.
737 p_resoures Resource associated with the charges for the step represented by
738 p_batchstep_id
739 x_act_seq_num The resultant activity_sequence_number
740 x_return_status outcome of the API call
741 S - Success
742 E - Error
743
744 HISTORY
745 Rishi Varma B3718176 14-07-2004
746 Created.
747 =============================================================================================*/
748 PROCEDURE calc_activity_sequence_number (
749 p_batchstep_id IN gme_batch_steps.batchstep_id%TYPE
750 ,p_resources IN gme_batch_step_resources.resources%TYPE
751 ,x_act_seq_num OUT NOCOPY NUMBER
752 ,x_return_status OUT NOCOPY VARCHAR2)
753 IS
754 CURSOR cur_get_seq_dep_ind (
755 v_resources gme_batch_step_resources.resources%TYPE
756 ,v_batchstep_id gme_batch_steps.batchstep_id%TYPE)
757 IS
758 SELECT MIN (a.sequence_dependent_ind)
759 FROM gme_batch_step_activities a, gme_batch_step_resources r
760 WHERE r.resources = v_resources
761 AND r.batchstep_activity_id = a.batchstep_activity_id
762 AND a.batchstep_id = v_batchstep_id
763 AND r.batchstep_id = v_batchstep_id
764 AND r.scale_type = 2;
765
766 l_api_name CONSTANT VARCHAR2 (30) := 'CALC_ACTIVITY_SEQUENCE_NUMBER';
767 l_activity VARCHAR2 (20);
768 BEGIN
769 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
770 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ' Entering');
771 END IF;
772
773 /* Initialise the return status to success*/
774 x_return_status := fnd_api.g_ret_sts_success;
775
776 OPEN cur_get_seq_dep_ind (p_resources, p_batchstep_id);
777
778 FETCH cur_get_seq_dep_ind
779 INTO x_act_seq_num;
780
781 IF cur_get_seq_dep_ind%NOTFOUND THEN
782 CLOSE cur_get_seq_dep_ind;
783 END IF;
784
785 CLOSE cur_get_seq_dep_ind;
786
787 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
788 gme_debug.put_line ( g_pkg_name
789 || '.'
790 || l_api_name
791 || ' In loop:seq dep ind,activity are'
792 || x_act_seq_num);
793 END IF;
794
795 IF x_act_seq_num IS NULL THEN
796 x_return_status := fnd_api.g_ret_sts_error;
797 END IF;
798 END calc_activity_sequence_number;
799
800 /*===========================================================================================
801 Procedure
802 populate_charges_table
803 Description
804 This procedure populates the charge details table gme_batch_step_charges.
805 Parameters
806 p_batchstep_charges_in The gme_batch_step_charges%ROWTYPE variable used to populate the
807 table.
808 p_no_of_charges The number of calculated charges.
809 p_remaining_quantity The quantity for the last charge.
810 x_return_status outcome of the API call
811 S - Success
812 E - Error
813 U - Unexpected error
814 The p_batchstep_charges parameter should have the following attributes populated;batch_id,
815 batchstep_id,charge_quantity,resources,
816 HISTORY
817 Rishi Varma B3718176 23-07-2004
818 Created.
819 =============================================================================================*/
820 PROCEDURE populate_charges_table (
821 p_batchstep_charges_in IN gme_batch_step_charges%ROWTYPE
822 ,p_no_of_charges IN NUMBER
823 ,p_remaining_quantity IN NUMBER
824 ,x_return_status OUT NOCOPY VARCHAR2)
825 IS
826 l_api_name CONSTANT VARCHAR2 (30) := 'POPULATE_CHARGES_TABLE';
827 l_batchstep_charges_in gme_batch_step_charges%ROWTYPE;
828 l_activity_sequence_number NUMBER;
829 l_return_status VARCHAR2 (1);
830 error_act_seq_num_calc EXCEPTION;
831 error_charge_insert EXCEPTION;
832 BEGIN
833 x_return_status := fnd_api.g_ret_sts_success;
834 l_batchstep_charges_in := p_batchstep_charges_in;
835 /*Calculating the activity sequence_number*/
836 gme_batch_step_chg_pvt.calc_activity_sequence_number
837 (p_batchstep_id => l_batchstep_charges_in.batchstep_id
838 ,p_resources => l_batchstep_charges_in.resources
839 ,x_act_seq_num => l_activity_sequence_number
840 ,x_return_status => l_return_status);
841
842 IF x_return_status <> fnd_api.g_ret_sts_success THEN
843 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
844 gme_debug.put_line
845 ('Error in calculating activity sequence number');
846 END IF;
847
848 RAISE error_act_seq_num_calc;
849 END IF;
850
851 l_batchstep_charges_in.activity_sequence_number :=
852 l_activity_sequence_number;
853
854 FOR i IN 1 .. (p_no_of_charges - 1) LOOP
855 l_batchstep_charges_in.charge_number := i;
856
857 IF (gme_batch_step_charge_dbl.insert_row
858 (p_batch_step_charges_in => l_batchstep_charges_in
859 ,x_batch_step_charges => l_batchstep_charges_in) ) THEN
860 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
861 gme_debug.put_line
862 ('sucessfully inserted into batchstep charges');
863 END IF;
864 ELSE
865 RAISE error_charge_insert;
866 END IF;
867 END LOOP;
868
869 --Populating the details of the last charge.
870 IF p_remaining_quantity <> -1 THEN
871 l_batchstep_charges_in.charge_quantity := p_remaining_quantity;
872 END IF;
873
874 l_batchstep_charges_in.charge_number := p_no_of_charges;
875
876 IF (gme_batch_step_charge_dbl.insert_row
877 (p_batch_step_charges_in => l_batchstep_charges_in
878 ,x_batch_step_charges => l_batchstep_charges_in) ) THEN
879 IF NVL (g_debug, -1) = gme_debug.g_log_statement THEN
880 gme_debug.put_line ('sucessfully inserted into batchstep charges');
881 END IF;
882 ELSE
883 RAISE error_charge_insert;
884 END IF;
885 EXCEPTION
886 WHEN error_charge_insert OR error_act_seq_num_calc THEN
887 x_return_status := fnd_api.g_ret_sts_error;
888 WHEN OTHERS THEN
889 x_return_status := fnd_api.g_ret_sts_unexp_error;
890 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
891 gme_debug.put_line ( g_pkg_name
892 || '.'
893 || l_api_name
894 || ':'
895 || ' OTHERS:'
896 || SQLERRM);
897 END;
898 END gme_batch_step_chg_pvt;
[Home] [Help]