1 PACKAGE BODY gme_delete_batch_step_pvt AS
2 /* $Header: GMEVDBSB.pls 120.3.12010000.1 2008/07/25 10:30:00 appldev ship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'gme_delete_batch_step_pvt';
5
6 /*===========================================================================================
7 Procedure
8 delete_step
9 Description
10 This particular procedure is used to delete the step and its details in a batch.
11 Parameters
12 p_api_version For version specific processing - Default 1
13 p_validation_level Errors to skip before returning - Default 100
14 p_init_msg_list Signals wether the message stack should be initialised
15 p_commit Indicator to commit the changes made
16 p_batch_step The batch step record.
17 x_message_count The number of messages in the message stack
18 x_message_list message stack where the api writes its messages
19 x_return_status outcome of the API call
20 S - Success
21 E - Error
22 U - Unexpected error
23 =============================================================================================*/
24 PROCEDURE delete_step (
25 p_batch_step_rec IN gme_batch_steps%ROWTYPE
26 ,x_return_status OUT NOCOPY VARCHAR2
27 ,p_reroute_flag IN BOOLEAN := FALSE)
28 IS
29 l_api_name CONSTANT VARCHAR2 (30) := 'delete_step';
30 /* Collections for details etc */
31 l_gme_batchstep_activities gme_batch_step_activities%ROWTYPE;
32 l_material_ids gme_common_pvt.number_tab;
33 l_batch_header gme_batch_header%ROWTYPE;
34 l_batch_step gme_batch_steps%ROWTYPE;
35 l_batchstep_dependency gme_batch_step_dependencies%ROWTYPE;
36 l_batchstep_items gme_batch_step_items%ROWTYPE;
37 /* Local variables */
38 l_return_status VARCHAR2 (1);
39 l_inv_trans_count NUMBER;
40 l_rsrc_trans_count NUMBER;
41 l_count NUMBER;
42 l_step_tbl gmd_auto_step_calc.step_rec_tbl;
43 l_msg_count NUMBER;
44 l_msg_stack VARCHAR2 (2000);
45
46 CURSOR cur_get_count (v_batch_id NUMBER, v_batchstep_id NUMBER)
47 IS
48 SELECT COUNT (1)
49 FROM gme_batch_steps
50 WHERE batch_id = v_batch_id AND batchstep_id <> v_batchstep_id;
51
52 CURSOR cur_get_dependency (v_batchstep_id NUMBER)
53 IS
54 SELECT batchstep_id, dep_step_id
55 FROM gme_batch_step_dependencies
56 WHERE (batch_id = l_batch_header.batch_id)
57 AND (batchstep_id = v_batchstep_id OR dep_step_id = v_batchstep_id);
58
59 CURSOR cur_get_item_assoc (v_batchstep_id NUMBER)
60 IS
61 SELECT material_detail_id
62 FROM gme_batch_step_items
63 WHERE batchstep_id = v_batchstep_id;
64
65 /* Exceptions */
66 step_activity_delete_error EXCEPTION;
67 invalid_step EXCEPTION;
68 error_load_trans EXCEPTION;
69 batch_step_fetch_error EXCEPTION;
70 batch_header_fetch_error EXCEPTION;
71 step_dependency_delete_error EXCEPTION;
72 step_item_delete_error EXCEPTION;
73 step_delete_error EXCEPTION;
74 one_step_reqd EXCEPTION;
75 error_calc_step_qty EXCEPTION;
76 BEGIN
77 IF g_debug <= gme_debug.g_log_procedure THEN
78 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
79 || l_api_name);
80 END IF;
81
82 /* Set the savepoint before proceeding */
83 SAVEPOINT delete_batch_step;
84 /* Set the return status to success initially */
85 x_return_status := fnd_api.g_ret_sts_success;
86
87 /* Initialize local batch step array */
88 IF NOT (gme_batch_steps_dbl.fetch_row (p_batch_step_rec, l_batch_step) ) THEN
89 RAISE batch_step_fetch_error;
90 END IF;
91
92 /* Load the batch header associated with the step */
93 l_batch_header.batch_id := l_batch_step.batch_id;
94
95 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header, l_batch_header) ) THEN
96 RAISE batch_header_fetch_error;
97 END IF;
98
99 /* Don't allow the Batch Step to be deleted if the Batch Status */
100 /* is Cancelled or Closed or Certified or WIP */
101 IF (l_batch_step.step_status <> 1) THEN
102 RAISE invalid_step;
103 END IF;
104
105 /* Don't allow all the steps in the batch to be deleted */
106 /* Load all the transactions and resources to the temporary table */
107 /* for the current batch if the update inventory ind is set for the batch */
108 IF l_batch_header.update_inventory_ind = 'Y' THEN
109 gme_trans_engine_util.load_rsrc_trans (p_batch_row => l_batch_header
110 ,x_rsc_row_count => l_rsrc_trans_count
111 ,x_return_status => l_return_status);
112
113 IF l_return_status <> x_return_status THEN
114 RAISE error_load_trans;
115 END IF;
116 END IF; /* IF x_batch_header.update_inventory_ind = 'Y' */
117
118 /* Delete all the activities attached to the step and the subsequent child nodes */
119 IF NOT p_reroute_flag THEN
120 OPEN cur_get_count (l_batch_step.batch_id
121 ,l_batch_step.batchstep_id);
122
123 FETCH cur_get_count
124 INTO l_count;
125
126 CLOSE cur_get_count;
127
128 IF l_count = 0 THEN
129 RAISE one_step_reqd;
130 END IF;
131 END IF;
132
133 -- Navin: START Changed the logic --
134 -- 1. First delete all the resources transactions associated with the resource
135 -- gme_resource_engine_pvt.delete_resource_trans
136 UPDATE gme_resource_txns_gtmp
137 SET action_code = 'DEL'
138 WHERE line_id IN (
139 SELECT batchstep_resource_id
140 FROM gme_batch_step_resources
141 WHERE batch_id = l_batch_step.batch_id
142 AND batchstep_id = l_batch_step.batchstep_id)
143 AND action_code NOT IN ('REVS', 'REVL', 'DEL')
144 AND delete_mark <> 1;
145
146 -- 2. Delete all the process parameters
147 DELETE gme_process_parameters
148 WHERE batch_id = l_batch_step.batch_id
149 AND batchstep_id = l_batch_step.batchstep_id;
150
151 -- 3. Delete all the resources
152 DELETE gme_batch_step_resources
153 WHERE batch_id = l_batch_step.batch_id
154 AND batchstep_id = l_batch_step.batchstep_id;
155
156 -- 4. Delete all the activities
157 DELETE gme_batch_step_activities
158 WHERE batch_id = l_batch_step.batch_id
159 AND batchstep_id = l_batch_step.batchstep_id;
160
161 -- 5. Delete all the chanrges
162 DELETE gme_batch_step_charges
163 WHERE batch_id = l_batch_step.batch_id
164 AND batchstep_id = l_batch_step.batchstep_id;
165
166 -- Navin: END Changed the logic --
167 FOR l_rec IN cur_get_dependency (l_batch_step.batchstep_id) LOOP
168 l_batchstep_dependency.batch_id := l_batch_header.batch_id;
169 l_batchstep_dependency.batchstep_id := l_rec.batchstep_id;
170 l_batchstep_dependency.dep_step_id := l_rec.dep_step_id;
171
172 IF NOT (gme_batch_step_depend_dbl.delete_row (l_batchstep_dependency) ) THEN
173 RAISE step_dependency_delete_error;
174 END IF;
175 END LOOP;
176 /* FOR l_rec IN Cur_get_dependency(l_batch_step.batchstep_id) */
177
178 /* Fetch all the items associated with the step */
179 OPEN cur_get_item_assoc (l_batch_step.batchstep_id);
180
181 FETCH cur_get_item_assoc
182 BULK COLLECT INTO l_material_ids;
183
184 CLOSE cur_get_item_assoc;
185
186 /* Fetch all the item association details */
187 FOR i IN 1 .. l_material_ids.COUNT LOOP
188 l_batchstep_items.material_detail_id := l_material_ids (i);
189
190 IF NOT (gme_batch_step_items_dbl.delete_row (l_batchstep_items) ) THEN
191 RAISE step_item_delete_error;
192 END IF;
193 END LOOP;
194
195 IF NOT (gme_batch_steps_dbl.delete_row (l_batch_step) ) THEN
196 RAISE step_delete_error;
197 END IF;
198
199 --Begin Bug#3109673 P.Raghu
200 --While Rerouting a batch, the call 'gmd_auto_step_calc.calc_step_qty' is not required.
201 --It is called when the dependent batch step is deleted.
202 IF NOT p_reroute_flag THEN
203 IF l_batch_header.automatic_step_calculation = 1 THEN
204 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
205 gme_debug.put_line ('l_batch_step' || l_batch_step.batchstep_id);
206 gme_debug.put_line ('auto step calc...');
207 END IF;
208
209 gmd_auto_step_calc.calc_step_qty
210 (p_parent_id => l_batch_header.batch_id
211 ,p_step_tbl => l_step_tbl
212 ,p_msg_count => l_msg_count
213 ,p_msg_stack => l_msg_stack
214 ,p_return_status => l_return_status
215 ,p_called_from_batch => 1
216 ,p_organization_id => l_batch_header.organization_id);
217 END IF;
218
219 IF l_return_status <> x_return_status THEN
220 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
221 gme_debug.put_line
222 (fnd_msg_pub.get (p_encoded => fnd_api.g_false) );
223 END IF;
224
225 RAISE error_calc_step_qty;
226 END IF;
227
228 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
229 gme_debug.put_line ('done step calc...');
230 END IF;
231
232 /*Bug 2395188 Bharati Satpute Replaced first,last with count */
233 -- Update the plan_step_qty in GME_BATCH_STEPS
234 FOR i IN 1 .. l_step_tbl.COUNT LOOP
235 UPDATE gme_batch_steps
236 SET plan_step_qty = l_step_tbl (i).step_qty
237 ,plan_mass_qty = l_step_tbl (i).step_mass_qty
238 ,mass_ref_um = l_step_tbl (i).step_mass_uom
239 ,plan_volume_qty = l_step_tbl (i).step_vol_qty
240 ,volume_ref_um = l_step_tbl (i).step_vol_uom
241 WHERE batch_id = l_batch_header.batch_id
242 AND batchstep_no = l_step_tbl (i).step_no;
243 END LOOP; /* FOR i IN l_step_tbl.FIRST .. l_step_tbl.LAST */
244 END IF;
245
246 --End Bug#3109673
247 IF g_debug <= gme_debug.g_log_procedure THEN
248 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
249 END IF;
250 EXCEPTION
251 WHEN invalid_step THEN
252 ROLLBACK TO SAVEPOINT delete_batch_step;
253 x_return_status := fnd_api.g_ret_sts_error;
254 gme_common_pvt.log_message ('GME_STEP_NOT_PENDING');
255 /* FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
256 P_data => x_message_list); */
257 WHEN error_calc_step_qty THEN
258 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
259 gme_debug.put_line ('calc step qty error');
260 END IF;
261
262 x_return_status := fnd_api.g_ret_sts_error;
263 WHEN one_step_reqd THEN
264 ROLLBACK TO SAVEPOINT delete_batch_step;
265 x_return_status := fnd_api.g_ret_sts_error;
266 gme_common_pvt.log_message ('GME_ONE_STEP_REQD');
267 /* FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
268 P_data => x_message_list); */
269 WHEN step_activity_delete_error OR error_load_trans OR batch_step_fetch_error OR batch_header_fetch_error OR step_dependency_delete_error OR step_item_delete_error OR step_delete_error THEN
270 x_return_status := fnd_api.g_ret_sts_error;
271 ROLLBACK TO SAVEPOINT delete_batch_step;
272 /* FND_MSG_PUB.COUNT_AND_GET (P_count => x_message_count,
273 P_data => x_message_list); */
274 WHEN OTHERS THEN
275 IF g_debug <= gme_debug.g_log_unexpected THEN
276 gme_debug.put_line ( 'When others exception in '
277 || g_pkg_name
278 || '.'
279 || l_api_name
280 || ' Error is '
281 || SQLERRM);
282 END IF;
283
284 x_return_status := fnd_api.g_ret_sts_unexp_error;
285 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
286 END delete_step;
287
288 /*===========================================================================================
289 Procedure
290 delete_activity
291 Description
292 This particular procedure is used to delete the activity and its details in a batch.
293 Parameters
294 p_batch_step_activities_rec The batch step activity record.
295 x_return_status outcome of the API call
296 S - Success
297 E - Error
298 U - Unexpected error
299 =============================================================================================*/
300 PROCEDURE delete_activity (
301 p_batch_step_activities_rec IN gme_batch_step_activities%ROWTYPE
302 ,x_return_status OUT NOCOPY VARCHAR2)
303 IS
304 l_api_name CONSTANT VARCHAR2 (30) := 'delete_activity';
305 /* Collections for details etc */
306 l_batchstep_resource_ids gme_common_pvt.number_tab;
307 l_gme_batchstep_resources gme_batch_step_resources%ROWTYPE;
308
309 CURSOR cur_step_resource_ids (v_batchstep_activity_id NUMBER)
310 IS
311 SELECT batchstep_resource_id
312 FROM gme_batch_step_resources
313 WHERE batchstep_activity_id = v_batchstep_activity_id;
314
315 --Rishi Varma bug 3307549 13-May-2004 start
316 CURSOR cur_get_batch_id (v_batchstep_activity_id NUMBER)
317 IS
318 SELECT batch_id
319 FROM gme_batch_step_activities
320 WHERE batchstep_activity_id = v_batchstep_activity_id;
321
322 l_batch_id NUMBER;
323 --Rishi Varma bug 3307549 13-May-2004 end
324
325 /* Local variables */
326 l_return_status VARCHAR2 (1);
327 /* Exception */
328 activity_resource_delete_error EXCEPTION;
329 step_activity_delete_error EXCEPTION;
330
331 -- Bug 5903208
332 gmf_cost_failure EXCEPTION;
333 l_message_count NUMBER;
334 l_message_list VARCHAR2(2000);
335
336 BEGIN
337 IF g_debug <= gme_debug.g_log_procedure THEN
338 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
339 || l_api_name);
340 END IF;
341
342 /* Set the return status to success initially */
343 x_return_status := fnd_api.g_ret_sts_success;
344
345 /* Fetch all the resources associated with the activity */
346 OPEN cur_step_resource_ids
347 (p_batch_step_activities_rec.batchstep_activity_id);
348
349 FETCH cur_step_resource_ids
350 BULK COLLECT INTO l_batchstep_resource_ids;
351
352 CLOSE cur_step_resource_ids;
353
354 /* Fetch all the activity resources details */
355 FOR i IN 1 .. l_batchstep_resource_ids.COUNT LOOP
356 l_gme_batchstep_resources.batchstep_resource_id :=
357 l_batchstep_resource_ids (i);
358 /* Delete all the resources associated with the activity */
359 delete_resource (l_gme_batchstep_resources, l_return_status);
360
361 IF l_return_status <> x_return_status THEN
362 RAISE activity_resource_delete_error;
363 END IF;
364 END LOOP;
365
366 --Rishi Varma bug 3307549 13-05-2004 start
367 OPEN cur_get_batch_id (p_batch_step_activities_rec.batchstep_activity_id);
368 FETCH cur_get_batch_id INTO l_batch_id;
369 CLOSE cur_get_batch_id;
370
371
372 --
373 -- Bug 5903208 -- call to GMF
374 --
375 IF l_batchstep_resource_ids.COUNT > 0 THEN
376 GMF_VIB.Update_Batch_Requirements
377 ( p_api_version => 1.0,
378 p_init_msg_list => FND_API.G_FALSE,
379 p_batch_id => l_batch_id,
380 x_return_status => l_return_status,
381 x_msg_count => l_message_count,
382 x_msg_data => l_message_list);
383
384 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
385 THEN
386 RAISE gmf_cost_failure;
387 END IF;
388 END IF;
389
390 IF NOT (gme_batch_step_activities_dbl.delete_row
391 (p_batch_step_activities_rec) ) THEN
392 RAISE step_activity_delete_error;
393 END IF;
394
395 gme_batch_step_chg_pvt.set_activity_sequence_num (l_batch_id);
396
397 --Rishi Varma bug 3307549 13-05-2004 end.
398 IF g_debug <= gme_debug.g_log_procedure THEN
399 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
400 END IF;
401 EXCEPTION
402 WHEN gmf_cost_failure THEN
403 -- Bug 5903208
404 x_return_status := FND_API.G_RET_STS_ERROR;
405
406 WHEN step_activity_delete_error OR activity_resource_delete_error THEN
407 x_return_status := fnd_api.g_ret_sts_error;
408 WHEN OTHERS THEN
409 IF g_debug <= gme_debug.g_log_unexpected THEN
410 gme_debug.put_line ( 'When others exception in '
411 || g_pkg_name
412 || '.'
413 || l_api_name
414 || ' Error is '
415 || SQLERRM);
416 END IF;
417
418 x_return_status := fnd_api.g_ret_sts_unexp_error;
419 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
420 END delete_activity;
421
422 /*===========================================================================================
423 Procedure
424 delete_resource
425 Description
426 This particular procedure is used to delete the resource and its details in a batch.
427 Parameters
428 p_batch_step_resources_rec The batch step resources record.
429 x_return_status outcome of the API call
430 S - Success
431 E - Error
432 U - Unexpected error
433 Pawan kumar Added the process parameters deletion procedure for bug 2509572
434 Rishi Varma bug 3307549 18-05-2004
435 Added code to remove charges associated with a chargeable resource.
436 Rishi Varma 3682311 11-06-2004
437 Removed the explicit commit after the clear_charge procedure.
438 Rishi Varma 3718176 20-07-2004
439 Added code to remove the charges assocaited with a chargeable resource only if its the
440 last instance of the resource present in the step.
441 =============================================================================================*/
442 PROCEDURE delete_resource (
443 p_batch_step_resources_rec gme_batch_step_resources%ROWTYPE
444 ,x_return_status OUT NOCOPY VARCHAR2)
445 IS
446 /* 2841929: Added parameters to the cursor and passing proper parameters */
447 CURSOR cur_get_update_inventory (v_batch_id IN NUMBER)
448 IS
449 SELECT update_inventory_ind
450 FROM gme_batch_header
451 WHERE batch_id = v_batch_id;
452
453 --Rishi Varma bug# 3307549 10-05-2004
454 CURSOR cur_is_charge_associated (
455 v_resources gme_batch_step_resources.resources%TYPE
456 ,v_batchstep_id NUMBER)
457 IS
458 SELECT 1
459 FROM DUAL
460 WHERE EXISTS (
461 SELECT 1
462 FROM gme_batch_step_charges
463 WHERE resources = v_resources
464 AND batchstep_id = v_batchstep_id);
465
466 l_cur_is_charge_associated cur_is_charge_associated%ROWTYPE;
467 resource_charge_delete_error EXCEPTION;
468
469 --Rishi Varma B3718176 20-07-2004.
470 CURSOR cur_check_last_rsrc_instance (
471 v_resources gme_batch_step_resources.resources%TYPE
472 ,v_batchstep_id NUMBER)
473 IS
474 SELECT COUNT (1)
475 FROM gme_batch_step_resources
476 WHERE resources = v_resources AND batchstep_id = v_batchstep_id;
477
478 l_rsrc_count NUMBER := -1;
479 l_api_name CONSTANT VARCHAR2 (30) := 'Delete_resource';
480 /* Local variables */
481 l_return_status VARCHAR2 (1);
482 l_update_inventory_ind VARCHAR2 (1);
483 l_rsrc_parameters gme_process_parameters%ROWTYPE;
484 l_batch_step_resource gme_batch_step_resources%ROWTYPE;
485 /* Exceptions */
486 trans_delete_error EXCEPTION;
487 activity_resource_delete_error EXCEPTION;
488 rsrc_param_delete_error EXCEPTION;
489 BEGIN
490 IF g_debug <= gme_debug.g_log_procedure THEN
491 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
492 || l_api_name);
493 END IF;
494
495 /* Set the return status to success initially */
496 x_return_status := fnd_api.g_ret_sts_success;
497
498 /* 2841929: populate local variable with the current batchstep record */
499 IF NOT (gme_batch_step_resources_dbl.fetch_row
500 (p_batch_step_resources_rec
501 ,l_batch_step_resource) ) THEN
502 RAISE activity_resource_delete_error;
503 END IF;
504
505 --Pawan kumar Added the process parameters deletion procedure for bug 2509572
506 /* First delete all the process parameters associated with the resource */
507 l_rsrc_parameters.batchstep_resource_id :=
508 p_batch_step_resources_rec.batchstep_resource_id;
509
510 --Rishi Varma bug 3307549 10/05/2004 start
511 OPEN cur_is_charge_associated (l_batch_step_resource.resources
512 ,l_batch_step_resource.batchstep_id);
513
514 FETCH cur_is_charge_associated
515 INTO l_cur_is_charge_associated;
516
517 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
518 gme_debug.put_line ( l_api_name
519 || 'batch,step idno. are '
520 || l_batch_step_resource.batch_id
521 || l_batch_step_resource.batchstep_id);
522 END IF;
523
524 IF cur_is_charge_associated%FOUND THEN
525 CLOSE cur_is_charge_associated;
526
527 --Rishi Varma B3718716 20-07-2004
528 /*Deleting the charges associated to the resources only if this is the only instance of
529 this resouces in the step which is being deleted*/
530 OPEN cur_check_last_rsrc_instance
531 (l_batch_step_resource.resources
532 ,l_batch_step_resource.batchstep_id);
533
534 FETCH cur_check_last_rsrc_instance
535 INTO l_rsrc_count;
536
537 IF l_rsrc_count = 1 THEN
538 CLOSE cur_check_last_rsrc_instance;
539
540 gme_batch_step_chg_pvt.clear_charges
541 (p_batch_id => l_batch_step_resource.batch_id
542 ,p_batchstep_id => l_batch_step_resource.batchstep_id
543 ,x_return_status => l_return_status);
544
545 IF l_return_status <> x_return_status THEN
546 RAISE resource_charge_delete_error;
547 --Rishi Varma 3682311 11-06-2004
548 /*Commented the explicit "COMMIT" as the commit will propogate either from the
549 forms or from the public,main api's*/
550 --ELSE
551 COMMIT;
552 END IF;
553 ELSE
554 CLOSE cur_check_last_rsrc_instance;
555 END IF;
556 ELSE
557 CLOSE cur_is_charge_associated;
558 END IF;
559
560 --Rishi Varma bug 3307549 10/05/2004 end
561 IF NOT (gme_process_parameters_dbl.delete_all
562 (p_process_parameters => l_rsrc_parameters) ) THEN
563 RAISE rsrc_param_delete_error;
564 END IF;
565
566 /* 2841929: pass proper parameter value */
567 OPEN cur_get_update_inventory (l_batch_step_resource.batch_id);
568
569 FETCH cur_get_update_inventory
570 INTO l_update_inventory_ind;
571
572 CLOSE cur_get_update_inventory;
573
574 IF l_update_inventory_ind = 'Y' THEN
575 /* First delete all the resources transactions associated with the resource */
576 gme_delete_batch_step_pvt.delete_resource_transactions
577 (p_batch_step_resources_rec => p_batch_step_resources_rec
578 ,x_return_status => l_return_status);
579
580 IF l_return_status <> x_return_status THEN
581 RAISE trans_delete_error;
582 END IF;
583 END IF;
584
585 IF NOT (gme_batch_step_resources_dbl.delete_row
586 (p_batch_step_resources_rec) ) THEN
587 RAISE activity_resource_delete_error;
588 END IF;
589
590 --Rishi Varma bug 3307549 13/05/2004
591 --Re sequencing the sequence dependent ids after the delete.
592 gme_batch_step_chg_pvt.set_sequence_dependent_id
593 (l_batch_step_resource.batch_id);
594
595 IF g_debug <= gme_debug.g_log_procedure THEN
596 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
597 END IF;
598 EXCEPTION
599 WHEN trans_delete_error OR activity_resource_delete_error OR rsrc_param_delete_error OR resource_charge_delete_error THEN
600 x_return_status := fnd_api.g_ret_sts_error;
601 WHEN OTHERS THEN
602 IF g_debug <= gme_debug.g_log_unexpected THEN
603 gme_debug.put_line ( 'When others exception in '
604 || g_pkg_name
605 || '.'
606 || l_api_name
607 || ' Error is '
608 || SQLERRM);
609 END IF;
610
611 x_return_status := fnd_api.g_ret_sts_unexp_error;
612 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
613 END delete_resource;
614
615 /*===========================================================================================
616 Procedure
617 delete_resource_transactions
618 Description
619 This particular procedure is used to delete the resource transactions in a batch.
620 Parameters
621 p_batch_step_resources_rec The batch step resources record.
622 x_return_status outcome of the API call
623 S - Success
624 E - Error
625 U - Unexpected error
626 =============================================================================================*/
627 PROCEDURE delete_resource_transactions (
628 p_batch_step_resources_rec gme_batch_step_resources%ROWTYPE
629 ,x_return_status OUT NOCOPY VARCHAR2)
630 IS
631 l_resource_txns gme_resource_txns_gtmp%ROWTYPE;
632 l_resource_tab gme_common_pvt.resource_transactions_tab;
633 l_api_name CONSTANT VARCHAR2 (30) := 'delete_resource_transactions';
634 l_return_status VARCHAR2 (1);
635 BEGIN
636 IF g_debug <= gme_debug.g_log_procedure THEN
637 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
638 || l_api_name);
639 END IF;
640
641 /* Set the return status to success initially */
642 x_return_status := fnd_api.g_ret_sts_success;
643 /* Lets fetch all the active resource transactions for the current resource */
644 l_resource_txns.poc_trans_id := NULL;
645 l_resource_txns.doc_id := NULL;
646 l_resource_txns.line_id :=
647 p_batch_step_resources_rec.batchstep_resource_id;
648 gme_resource_engine_pvt.fetch_active_resources
649 (p_resource_rec => l_resource_txns
650 ,x_resource_tbl => l_resource_tab
651 ,x_return_status => l_return_status);
652
653 IF l_return_status <> x_return_status THEN
654 RAISE fnd_api.g_exc_error;
655 END IF; /* IF l_return_status <> x_return_status */
656
657 FOR i IN 1 .. l_resource_tab.COUNT LOOP
658 l_resource_txns := l_resource_tab (i);
659 gme_resource_engine_pvt.delete_resource_trans
660 (p_tran_rec => l_resource_txns
661 ,x_return_status => l_return_status);
662
663 IF l_return_status <> x_return_status THEN
664 RAISE fnd_api.g_exc_error;
665 END IF;
666 END LOOP; /* FOR i IN 1..l_resource_tab.COUNT */
667
668 IF g_debug <= gme_debug.g_log_procedure THEN
669 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
670 END IF;
671 EXCEPTION
672 WHEN fnd_api.g_exc_error THEN
673 x_return_status := fnd_api.g_ret_sts_error;
674 WHEN OTHERS THEN
675 IF g_debug <= gme_debug.g_log_unexpected THEN
676 gme_debug.put_line ( 'When others exception in '
677 || g_pkg_name
678 || '.'
679 || l_api_name
680 || ' Error is '
681 || SQLERRM);
682 END IF;
683
684 x_return_status := fnd_api.g_ret_sts_unexp_error;
685 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
686 END delete_resource_transactions;
687 END gme_delete_batch_step_pvt;
[Home] [Help]