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