[Home] [Help]
PACKAGE BODY: APPS.GME_UNRELEASE_STEP_PVT
Source
1 PACKAGE BODY gme_unrelease_step_pvt AS
2 /* $Header: GMEVURSB.pls 120.6 2006/06/14 17:23:11 pxkumar noship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'gme_unrelease_step_pvt';
5
6 /*=============================================================================
7 Procedure
8 unrelease_step
9 Description
10 This procedure call unreleases the batch step.
11 Parameters
12 p_batch_step_rec The batch step row to unrelease
13 p_create_resv_pend_lots Indicates whether to re-create reservations/pending product lots
14 p_from_unrelease_batch Passed as 1 from unrelease batch and 0 from gme_api_main (which means it's an unreleae step)
15 x_batch_step_rec Updated batch step record
16 x_return_status Outcome of the API call
17 S - Success
18 E - Error
19 U - Unexpected error
20 =============================================================================*/
21 PROCEDURE unrelease_step (
22 p_batch_step_rec IN gme_batch_steps%ROWTYPE
23 ,p_update_inventory_ind IN VARCHAR2
24 ,p_create_resv_pend_lots IN NUMBER
25 ,p_from_unrelease_batch IN NUMBER
26 ,x_batch_step_rec OUT NOCOPY gme_batch_steps%ROWTYPE
27 ,x_return_status OUT NOCOPY VARCHAR2)
28 IS
29 CURSOR cur_get_step_materials (
30 v_batch_id gme_batch_header.batch_id%TYPE
31 ,v_batchstep_id gme_batch_steps.batchstep_id%TYPE)
32 IS
33 SELECT d.*
34 FROM gme_batch_step_items i, gme_material_details d
35 WHERE d.batch_id = v_batch_id
36 AND d.material_detail_id = i.material_detail_id
37 AND i.batchstep_id = v_batchstep_id
38 AND d.release_type = gme_common_pvt.g_mtl_autobystep_release;
39
40 l_api_name CONSTANT VARCHAR2 (30) := 'unrelease_step';
41 l_material_detail_tbl gme_common_pvt.material_details_tab;
42 l_material_detail_rec gme_material_details%ROWTYPE;
43 l_in_batch_step_rec gme_batch_steps%ROWTYPE;
44 l_msg_count NUMBER;
45 l_msg_stack VARCHAR2 (2000);
46 l_lock_status VARCHAR2(1);
47 l_locked_by_status VARCHAR2(1);
48 l_lock_allowed VARCHAR2(1);
49 l_return_status VARCHAR2(1);
50 error_update_row EXCEPTION;
51 error_unrelease_matl EXCEPTION;
52 update_step_qty_error EXCEPTION;
53 gmo_lock_error EXCEPTION;
54 BEGIN
55 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
56 gme_debug.g_log_procedure THEN
57 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
58 || l_api_name);
59 gme_debug.put_line ( g_pkg_name
60 || '.'
61 || l_api_name
62 || ' unreleasing batchstep_id='
63 || p_batch_step_rec.batchstep_id);
64 END IF;
65
66 /* Set the return status to success initially */
67 x_return_status := fnd_api.g_ret_sts_success;
68 -- set output structure
69 x_batch_step_rec := p_batch_step_rec;
70 -- Pawan Kumar added for bug 5034336
71 -- check for batch step lock status from gmo
72 gmo_vbatch_grp.GET_ENTITY_LOCK_STATUS (
73 P_API_VERSION => 1.0,
74 P_INIT_MSG_LIST => FND_API.G_FALSE,
75 P_COMMIT => FND_API.G_FALSE,
76 P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
77 X_RETURN_STATUS => x_return_status ,
78 X_MSG_COUNT => l_msg_count,
79 X_MSG_DATA => l_msg_stack,
80 P_ENTITY_NAME => 'OPERATION',
81 P_ENTITY_KEY => x_batch_step_rec.batchstep_id,
82 P_REQUESTER => gme_common_pvt.g_user_ident,
83 X_LOCK_STATUS => l_lock_status,
84 X_LOCKED_BY_STATUS => l_locked_by_status,
85 X_LOCK_ALLOWED => l_lock_allowed);
86 gme_debug.put_line ( g_pkg_name
87 || '.'
88 || l_api_name
89 || ':'
90 || 'from gmo the lock_status='
91 || l_lock_status);
92 IF l_return_status <> fnd_api.g_ret_sts_success THEN
93 RAISE gmo_lock_error;
94 END IF;
95 IF (g_debug <= gme_debug.g_log_procedure) THEN
96 gme_debug.put_line ( g_pkg_name
97 || '.'
98 || l_api_name
99 || ':'
100 || 'from gmo the lock_status='
101 || l_lock_status);
102 END IF;
103 IF l_lock_status = 'Y' THEN
104 gme_common_pvt.log_message ('GME_STEP_LOCK_ERROR');
105 RAISE gmo_lock_error;
106 END IF;
107
108
109 -- set step status
110 x_batch_step_rec.step_status := gme_common_pvt.g_step_pending;
111 gme_common_pvt.g_batch_status_check := fnd_api.g_false;
112 -- set actual start date to NULL...
113 x_batch_step_rec.actual_start_date := NULL;
114
115 -- reset quality status
116 IF (x_batch_step_rec.quality_status <> 1) THEN
117 x_batch_step_rec.quality_status := 2;
118 END IF;
119
120 -- Update the step
121 IF NOT (gme_batch_steps_dbl.update_row (p_batch_step => x_batch_step_rec) ) THEN
122 RAISE error_update_row;
123 END IF;
124
125 -- Update WHO columns for output structure
126 x_batch_step_rec.last_updated_by := gme_common_pvt.g_user_ident;
127 x_batch_step_rec.last_update_date := gme_common_pvt.g_timestamp;
128 x_batch_step_rec.last_update_login := gme_common_pvt.g_login_id;
129
130 -- Fetch all the material lines associated to the step if not from unrelease batch
131 IF p_from_unrelease_batch = 0 THEN
132 OPEN cur_get_step_materials (x_batch_step_rec.batch_id
133 ,x_batch_step_rec.batchstep_id);
134
135 FETCH cur_get_step_materials
136 BULK COLLECT INTO l_material_detail_tbl;
137
138 CLOSE cur_get_step_materials;
139
140 FOR i IN 1 .. l_material_detail_tbl.COUNT LOOP
141 l_material_detail_rec := l_material_detail_tbl (i);
142 gme_unrelease_batch_pvt.unrelease_material
143 (p_material_detail_rec => l_material_detail_rec
144 ,p_update_inventory_ind => p_update_inventory_ind
145 ,p_create_resv_pend_lots => p_create_resv_pend_lots
146 ,p_from_batch => FALSE
147 ,x_return_status => x_return_status);
148
149 IF x_return_status <> fnd_api.g_ret_sts_success THEN
150 RAISE error_unrelease_matl;
151 END IF;
152 END LOOP;
153 END IF; -- IF p_from_unrelease_batch = 0 THEN
154
155 /* Invoke the update step qty API to update the step quantities and the */
156 /* quantities of the succeeding steps */
157 l_in_batch_step_rec := x_batch_step_rec;
158 gme_update_step_qty_pvt.update_step_qty
159 (p_batch_step_rec => l_in_batch_step_rec
160 ,x_message_count => l_msg_count
161 ,x_message_list => l_msg_stack
162 ,x_return_status => x_return_status
163 ,x_batch_step_rec => x_batch_step_rec);
164
165 IF x_return_status <> fnd_api.g_ret_sts_success THEN
166 RAISE update_step_qty_error;
167 END IF;
168
169 -- Remove the actual start date of the activities... set defaults; set WHO columns
170 UPDATE gme_batch_step_activities
171 SET actual_start_date = NULL
172 ,plan_activity_factor = NVL (plan_activity_factor, 0)
173 ,last_updated_by = gme_common_pvt.g_user_ident
174 ,last_update_date = gme_common_pvt.g_timestamp
175 ,last_update_login = gme_common_pvt.g_login_id
176 WHERE batchstep_id = x_batch_step_rec.batchstep_id
177 AND batch_id = x_batch_step_rec.batch_id;
178
179 -- Remove the actual start date of the resources... set defaults; set WHO columns
180 UPDATE gme_batch_step_resources
181 SET actual_start_date = NULL
182 ,plan_rsrc_count = NVL (plan_rsrc_count, 1)
183 ,plan_rsrc_qty = NVL (plan_rsrc_qty, 0)
184 ,plan_rsrc_usage = NVL (plan_rsrc_usage, 0)
185 ,last_updated_by = gme_common_pvt.g_user_ident
186 ,last_update_date = gme_common_pvt.g_timestamp
187 ,last_update_login = gme_common_pvt.g_login_id
188 WHERE batchstep_id = x_batch_step_rec.batchstep_id
189 AND batch_id = x_batch_step_rec.batch_id;
190
191 -- Update plan start date and plan completion date on activity and resources
192 -- to that on the step if they are NULL; who columns set above, no need again...
193 UPDATE gme_batch_step_activities
194 SET plan_start_date = x_batch_step_rec.plan_start_date
195 WHERE batchstep_id = x_batch_step_rec.batchstep_id
196 AND batch_id = x_batch_step_rec.batch_id
197 AND plan_start_date IS NULL;
198
199 UPDATE gme_batch_step_resources
200 SET plan_start_date = x_batch_step_rec.plan_start_date
201 WHERE batchstep_id = x_batch_step_rec.batchstep_id
202 AND batch_id = x_batch_step_rec.batch_id
203 AND plan_start_date IS NULL;
204
205 UPDATE gme_batch_step_activities
206 SET plan_cmplt_date = x_batch_step_rec.plan_cmplt_date
207 WHERE batchstep_id = x_batch_step_rec.batchstep_id
208 AND batch_id = x_batch_step_rec.batch_id
209 AND plan_cmplt_date IS NULL;
210
211 UPDATE gme_batch_step_resources
212 SET plan_cmplt_date = x_batch_step_rec.plan_cmplt_date
213 WHERE batchstep_id = x_batch_step_rec.batchstep_id
214 AND batch_id = x_batch_step_rec.batch_id
215 AND plan_cmplt_date IS NULL;
216
217 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
218 gme_debug.g_log_procedure THEN
219 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
220 END IF;
221 EXCEPTION
222 WHEN error_update_row THEN
223 gme_common_pvt.log_message ('GME_UNEXPECTED_ERROR', 'ERROR'
224 ,SQLERRM);
225 x_return_status := fnd_api.g_ret_sts_unexp_error;
226 WHEN error_unrelease_matl OR update_step_qty_error THEN
227 NULL;
228 WHEN gmo_lock_error THEN
229 IF (g_debug <= gme_debug.g_log_procedure) THEN
230 gme_debug.put_line ( g_pkg_name
231 || '.'
232 || l_api_name
233 || ':'
234 || 'GMO_LOCK_ERROR.');
235
236 END IF;
237 x_return_status := fnd_api.g_ret_sts_error;
238 WHEN OTHERS THEN
239 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
240
241 IF g_debug <= gme_debug.g_log_procedure THEN
242 gme_debug.put_line ( 'Unexpected error: '
243 || g_pkg_name
244 || '.'
245 || l_api_name
246 || ': '
247 || SQLERRM);
248 END IF;
249
250 x_return_status := fnd_api.g_ret_sts_unexp_error;
251 END unrelease_step;
252
253 PROCEDURE validate_step_for_unrelease
254 (p_batch_hdr_rec IN gme_batch_header%ROWTYPE
255 ,p_batch_step_rec IN gme_batch_steps%ROWTYPE
256 ,x_return_status OUT NOCOPY VARCHAR2) IS
257
258 l_api_name CONSTANT VARCHAR2 (30) := 'validate_step_for_unrelease';
259
260 CURSOR cur_dep_step (v_batchstep_id gme_batch_steps.batchstep_id%TYPE, v_batch_id NUMBER) IS
261 SELECT 1
262 FROM gme_batch_step_dependencies d, gme_batch_steps s
263 WHERE d.batchstep_id = s.batchstep_id AND
264 d.batch_id = s.batch_id AND
265 d.dep_step_id = v_batchstep_id AND
266 s.batch_id = v_batch_id AND
267 s.step_status <> gme_common_pvt.g_step_pending;
268
269 l_is_dep_step_valid NUMBER;
270
271 error_batch_type EXCEPTION;
272 error_batch_status EXCEPTION;
273 error_step_status EXCEPTION;
274 error_dep_step_status EXCEPTION;
275
276 BEGIN
277 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
278 gme_debug.g_log_procedure THEN
279 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
280 || l_api_name);
281 END IF;
282
283 IF p_batch_hdr_rec.batch_type = gme_common_pvt.g_doc_type_fpo THEN
284 RAISE error_batch_type;
285 END IF;
286
287 IF p_batch_hdr_rec.batch_status <> gme_common_pvt.g_batch_wip THEN
288 RAISE error_batch_status;
289 END IF;
290
291 IF p_batch_step_rec.step_status <> gme_common_pvt.g_step_wip THEN
292 RAISE error_step_status;
293 END IF;
294
295 IF p_batch_hdr_rec.automatic_step_calculation = 1 OR
296 p_batch_step_rec.steprelease_type = gme_common_pvt.g_auto_step_release OR
297 p_batch_hdr_rec.enforce_step_dependency = 1 THEN
298 -- return error if any immediate succeeding step is not Pending
299 OPEN cur_dep_step(p_batch_step_rec.batchstep_id, p_batch_step_rec.batch_id);
300 FETCH cur_dep_step INTO l_is_dep_step_valid;
301 IF cur_dep_step%FOUND THEN
302 CLOSE cur_dep_step;
303 RAISE error_dep_step_status;
304 END IF;
305 CLOSE cur_dep_step;
306 END IF; -- IF p_batch_hdr_rec.automatic_step_calculation = 1 OR
307
308 IF NVL (g_debug, gme_debug.g_log_procedure + 1) <=
309 gme_debug.g_log_procedure THEN
310 gme_debug.put_line ('Exiting api ' || g_pkg_name || '.' || l_api_name);
311 END IF;
312 EXCEPTION
313 WHEN error_step_status THEN
314 gme_common_pvt.log_message('GME_API_INV_STEP_STAT_UNRELE');
315 x_return_status := fnd_api.g_ret_sts_error;
316 WHEN error_batch_type OR error_batch_status THEN
317 gme_common_pvt.log_message('GME_API_INV_BATCH_UNRELE_STEP');
318 x_return_status := fnd_api.g_ret_sts_error;
319 WHEN error_dep_step_status THEN
320 gme_common_pvt.log_message ('GME_SUCC_DEP_PENDING');
321 x_return_status := FND_API.G_RET_STS_ERROR;
322 WHEN OTHERS THEN
323 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
324
325 IF g_debug <= gme_debug.g_log_procedure THEN
326 gme_debug.put_line ( 'Unexpected error: '
327 || g_pkg_name
328 || '.'
329 || l_api_name
330 || ': '
331 || SQLERRM);
332 END IF;
333
334 x_return_status := fnd_api.g_ret_sts_unexp_error;
335 END validate_step_for_unrelease;
336
337 END gme_unrelease_step_pvt;