1 PACKAGE BODY gme_terminate_step_pvt AS
2 /* $Header: GMEVTRSB.pls 120.1 2005/06/03 12:25:21 appldev $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name VARCHAR2 (30) := 'GME_TERMINATE_STEP_PVT';
5
6 /*
7 REM *********************************************************************
8 REM *
9 REM * FILE: GMEVTRSB.pls
10 REM * PURPOSE: Package Body for the GME step terminate api
11 REM * AUTHOR: Pawan Kumar
12 REM * DATE: 2 May 2005
13 REM * HISTORY:
14 REM * ========
15 REM *
16 REM **********************************************************************
17 */
18
19 /*======================================================================================
20 Procedure
21 Terminate_Step
22 Description
23 This procedure call terminates WIP batch steps.
24 Parameters
25 x_batch_step_rec The batch step row to identify the step.
26 x_return_status outcome of the API call
27 S - Success
28 E - Error
29 U - Unexpected error
30 ======================================================================================*/
31 PROCEDURE terminate_step (
32 p_batch_step_rec IN gme_batch_steps%ROWTYPE
33 ,p_update_inventory_ind IN VARCHAR2
34 ,p_actual_cmplt_date IN DATE
35 ,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE
36 ,x_return_status OUT NOCOPY VARCHAR2)
37 IS
38 /* Variable Declarations */
39 l_resource_txns gme_resource_txns_gtmp%ROWTYPE;
40 l_resource_txns_tab gme_common_pvt.resource_transactions_tab;
41 l_resources_tab gme_common_pvt.resources_tab;
42 l_activities_tab gme_common_pvt.activities_tab;
43 l_api_name VARCHAR2 (20) := 'Terminate_step';
44 /* Exception declarations */
45 batch_step_upd_err EXCEPTION;
46 resource_txns_gtmp_del_err EXCEPTION;
47 resource_upd_err EXCEPTION;
48 activity_upd_err EXCEPTION;
49
50 /* Cursor declarations */
51 CURSOR cur_get_resources (v_batchstep_id NUMBER)
52 IS
53 SELECT *
54 FROM gme_batch_step_resources
55 WHERE batchstep_id = v_batchstep_id;
56
57 CURSOR cur_get_activities (v_batchstep_id NUMBER)
58 IS
59 SELECT *
60 FROM gme_batch_step_activities
61 WHERE batchstep_id = v_batchstep_id;
62 BEGIN
63 /* Set the save point before processing */
64 SAVEPOINT terminate_batch_step;
65
66 IF (g_debug <= gme_debug.g_log_procedure) THEN
67 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
68 || 'Entering');
69 END IF;
70
71 -- Set the return status to success initially
72 x_return_status := fnd_api.g_ret_sts_success;
73 x_batch_step_rec := p_batch_step_rec;
74
75 -- Current step status must be WIP to terminate step
76
77 /* IF x_batch_step_rec.step_status <> 2
78 THEN
79 RAISE invalid_step_status;
80 END IF;
81 Bug# 2841929(back port 2836788) We need to remove the
82 resource information for the
83 gme_batch_step_rsrc_summary table, as this table
84 should only hold data of the resources which are in
85 PENDING or WIP */
86 DELETE FROM gme_batch_step_rsrc_summary
87 WHERE batchstep_id = x_batch_step_rec.batchstep_id;
88
89 /* Get all the activities associated with the step */
90 /* OPEN cur_get_activities (x_batch_step.batchstep_id);
91 FETCH cur_get_activities BULK COLLECT INTO l_activity_ids;
92 CLOSE cur_get_activities;
93 */
94 -- FOR i IN 1 .. l_activity_ids.COUNT
95 /* Pawan Kumar - bug 3328047- removed bulk collect */
96 OPEN cur_get_activities (x_batch_step_rec.batchstep_id);
97
98 FETCH cur_get_activities
99 BULK COLLECT INTO l_activities_tab;
100
101 CLOSE cur_get_activities;
102
103 FOR i IN 1 .. l_activities_tab.COUNT LOOP
104 -- Update actual completion date for activities
105 l_activities_tab (i).actual_cmplt_date := p_actual_cmplt_date;
106
107 IF (l_activities_tab (i).actual_activity_factor IS NULL) THEN
108 l_activities_tab (i).actual_activity_factor := 0;
109 END IF;
110
111 IF NOT (gme_batch_step_activities_dbl.update_row
112 (l_activities_tab (i) ) ) THEN
113 RAISE activity_upd_err;
114 END IF;
115 END LOOP; /*end for l_activity_tab */
116
117 /* Get all the resources associated with the step */
118 OPEN cur_get_resources (x_batch_step_rec.batchstep_id);
119
120 FETCH cur_get_resources
121 BULK COLLECT INTO l_resources_tab;
122
123 CLOSE cur_get_resources;
124
125 FOR i IN 1 .. l_resources_tab.COUNT LOOP
126 -- Update actual completion date for resources
127 l_resources_tab (i).actual_cmplt_date := p_actual_cmplt_date;
128
129 IF l_resources_tab (i).actual_rsrc_count IS NULL THEN
130 l_resources_tab (i).actual_rsrc_count := 0;
131 END IF;
132
133 IF l_resources_tab (i).actual_rsrc_usage IS NULL THEN
134 l_resources_tab (i).actual_rsrc_usage := 0;
135 END IF;
136
137 IF l_resources_tab (i).actual_rsrc_qty IS NULL THEN
138 l_resources_tab (i).actual_rsrc_qty := 0;
139 END IF;
140
141 IF NOT (gme_batch_step_resources_dbl.update_row (l_resources_tab (i) ) ) THEN
142 RAISE resource_upd_err;
143 END IF;
144
145 IF (p_update_inventory_ind = 'Y') THEN
146 l_resource_txns.line_id :=
147 l_resources_tab (i).batchstep_resource_id;
148 gme_resource_engine_pvt.fetch_active_resources
149 (p_resource_rec => l_resource_txns
150 ,x_resource_tbl => l_resource_txns_tab
151 ,x_return_status => x_return_status);
152
153 -- Delete the pending resource transactions
154 FOR j IN 1 .. l_resource_txns_tab.COUNT LOOP
155 IF l_resource_txns_tab (j).completed_ind = 0 THEN
156 l_resource_txns_tab (j).action_code := 'DEL';
157
158 IF (g_debug <= gme_debug.g_log_procedure) THEN
159 gme_debug.put_line
160 ( g_pkg_name
161 || '.'
162 || l_api_name
163 || ':'
164 || 'Calling resource txn update)delete_row');
165 END IF;
166
167 IF NOT (gme_resource_txns_gtmp_dbl.update_row
168 (p_resource_txns => l_resource_txns_tab
169 (j) ) ) THEN
170 RAISE resource_txns_gtmp_del_err;
171 END IF;
172 END IF; /* completed_ind = 0 */
173 END LOOP; /*end for l_resource_txns_tab */
174 END IF; /* update_inventory = 'Y' */
175 END LOOP; /*end for l_resources_tab */
176
177 -- Update the Batch Step Status to Completed
178 x_batch_step_rec.step_status := 3;
179 x_batch_step_rec.terminated_ind := 1;
180 x_batch_step_rec.actual_cmplt_date := p_actual_cmplt_date;
181
182 IF x_batch_step_rec.actual_step_qty IS NULL THEN
183 x_batch_step_rec.actual_step_qty := 0;
184 END IF;
185
186 IF x_batch_step_rec.actual_charges IS NULL THEN
187 x_batch_step_rec.actual_charges := 0;
188 END IF;
189
190 IF x_batch_step_rec.actual_mass_qty IS NULL THEN
191 x_batch_step_rec.actual_mass_qty := 0;
192 END IF;
193
194 IF x_batch_step_rec.actual_volume_qty IS NULL THEN
195 x_batch_step_rec.actual_volume_qty := 0;
196 END IF;
197
198 IF (g_debug <= gme_debug.g_log_procedure) THEN
199 gme_debug.put_line ( g_pkg_name
200 || '.'
201 || l_api_name
202 || ':'
203 || ' Calling batch step update_row');
204 END IF;
205
206 IF NOT (gme_batch_steps_dbl.update_row (p_batch_step => x_batch_step_rec) ) THEN
207 RAISE batch_step_upd_err;
208 END IF;
209
210 IF (g_debug <= gme_debug.g_log_procedure) THEN
211 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
212 || 'Exiting');
213 END IF;
214 EXCEPTION
215 WHEN resource_txns_gtmp_del_err THEN
216 IF (g_debug <= gme_debug.g_log_procedure) THEN
217 gme_debug.put_line ( g_pkg_name
218 || '.'
219 || l_api_name
220 || ':'
221 || 'RESOURCE_TXNS_GTMP_DEL_ERR.');
222 END IF;
223
224 ROLLBACK TO SAVEPOINT terminate_batch_step;
225 x_return_status := fnd_api.g_ret_sts_error;
226 WHEN resource_upd_err THEN
227 IF (g_debug <= gme_debug.g_log_procedure) THEN
228 gme_debug.put_line ( g_pkg_name
229 || '.'
230 || l_api_name
231 || ':'
232 || 'RESOURCE_UPD_ERR.');
233 END IF;
234
235 ROLLBACK TO SAVEPOINT terminate_batch_step;
236 x_return_status := fnd_api.g_ret_sts_error;
237 WHEN activity_upd_err THEN
238 IF (g_debug <= gme_debug.g_log_procedure) THEN
239 gme_debug.put_line ( g_pkg_name
240 || '.'
241 || l_api_name
242 || ':'
243 || 'ACTIVITY_UPD_ERR.');
244 END IF;
245
246 ROLLBACK TO SAVEPOINT terminate_batch_step;
247 x_return_status := fnd_api.g_ret_sts_error;
248 WHEN batch_step_upd_err THEN
249 IF (g_debug <= gme_debug.g_log_procedure) THEN
250 gme_debug.put_line
251 ( g_pkg_name
252 || '.'
253 || l_api_name
254 || ':'
255 || ' terminate_step, error : BATCH_STEP_UPD_ERR.'
256 ,gme_debug.g_log_error
257 ,'terminate_step');
258 END IF;
259
260 ROLLBACK TO SAVEPOINT terminate_batch_step;
261 x_return_status := fnd_api.g_ret_sts_error;
262 WHEN OTHERS THEN
263 IF g_debug <= gme_debug.g_log_unexpected THEN
264 gme_debug.put_line ( 'When others exception in '
265 || g_pkg_name
266 || '.'
267 || l_api_name
268 || ' Error is '
269 || SQLERRM);
270 END IF;
271
272 ROLLBACK TO SAVEPOINT terminate_batch_step;
273 x_return_status := fnd_api.g_ret_sts_unexp_error;
274 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
275 END terminate_step;
276 END gme_terminate_step_pvt;
[Home] [Help]