1 PACKAGE BODY gme_reopen_batch_pvt AS
2 /* $Header: GMEVROBB.pls 120.4.12010000.1 2008/07/25 10:31:36 appldev ship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_REOPEN_BATCH_PVT';
5
6 /*
7 REM **********************************************************************
8 REM * *
9 REM * FILE: GMEVROBB.pls *
10 REM * PURPOSE: Package Body for the GME batch reopen api *
11 REM * AUTHOR: Navin Sinha, OPM Development *
12 REM * DATE: May 19 2005 *
13 REM * *
14 REM * PROCEDURE reopen_batch *
15 REM * FUNCTION is_batch_posted *
16 REM * FUNCTION is_period_open *
17 REM * FUNCTION create_history *
18 REM * *
19 REM * *
20 REM * HISTORY: *
21 REM * ======== *
22 REM * Pawan Kumar Corrected the code. *
23 REM **********************************************************************
24 */
25 g_package_name CONSTANT VARCHAR2 (30) := 'GME_REOPEN_BATCH_PVT';
26
27 /*================================================================================
28 Procedure
29 reopen_batch
30 Description
31 This particular procedure call reopen the batch.
32 Parameters
33 p_batch_header_rec The batch header row to identify the batch
34 p_validation_level Errors to skip before returning - Default 100
35 x_batch_header_rec The batch header row to identify the batch
36 x_message_count The number of messages in the message stack
37 x_message_list message stack where the api writes its messages
38 x_return_status outcome of the API call
39 S - Success
40 E - Error
41 U - Unexpected error
42 ================================================================================*/
43 PROCEDURE reopen_batch (
44 p_batch_header_rec IN gme_batch_header%ROWTYPE
45 ,p_reopen_steps IN VARCHAR2 := 'F'
46 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
47 ,x_return_status OUT NOCOPY VARCHAR2)
48 IS
49 /* Cursor to get all the phantom batches except ones
50 which are release type of "Automatic by Step" and attached to steps */
51 CURSOR cur_matl_phant_ids (v_batch_id NUMBER)
52 IS
53 SELECT d.phantom_id
54 FROM gme_material_details d
55 WHERE d.batch_id = v_batch_id
56 AND NVL (d.phantom_id, 0) > 0
57 AND NOT EXISTS (
58 SELECT 1
59 FROM gme_batch_step_items
60 WHERE material_detail_id = d.material_detail_id
61 AND d.release_type = 3);
62
63 l_api_name CONSTANT VARCHAR2 (30) := 'REOPEN_BATCH';
64 /* Miscellaneous */
65 l_batch_status NUMBER;
66 l_batch_close_date DATE;
67 l_back_flush NUMBER;
68 l_error_count NUMBER;
69 l_row_count NUMBER;
70 l_ins_history gme_batch_history%ROWTYPE;
71 l_material_details gme_material_details%ROWTYPE;
72 l_material_details_tab gme_reopen_batch_pvt.material_details_tab;
73 l_phantom_ids gme_common_pvt.number_tab;
74 l_batch_header gme_batch_header%ROWTYPE;
75 l_in_batch_header gme_batch_header%ROWTYPE;
76 l_dummy NUMBER;
77 l_message_count NUMBER;
78 l_message_list VARCHAR2 (2000);
79 /* Added local variables */
80 l_mat_cnt NUMBER;
81 l_rsrc_cnt NUMBER;
82 l_status VARCHAR2 (1);
83 l_return_status VARCHAR2 (1);
84 l_msg_count NUMBER;
85 l_msg_data VARCHAR2 (100);
86 l_batch_cost BOOLEAN := TRUE;
87 batch_cost_err EXCEPTION;
88 -- batch_header_locked_err EXCEPTION;
89 batch_header_fetch_err EXCEPTION;
90 invalid_batch_type EXCEPTION;
91 invalid_batch_status EXCEPTION;
92 -- batch_already_purged EXCEPTION;
93 batch_already_posted EXCEPTION;
94 batch_costed_and_period_closed EXCEPTION;
95 -- marked_for_deletion EXCEPTION;
96 batch_step_reopen_err EXCEPTION;
97 batch_header_upd_err EXCEPTION;
98 batch_hist_insert_err EXCEPTION;
99 phantom_batch_reopen_err EXCEPTION;
100 cant_reopen_migrated_batch EXCEPTION;
101
102 -- Bug 5903208
103 gmf_cost_failure EXCEPTION;
104
105 BEGIN
106 /* Set the return staus to success inititally*/
107 x_return_status := fnd_api.g_ret_sts_success;
108
109 /* Initialize output batch header */
110 x_batch_header_rec := p_batch_header_rec ;
111 /* x_batch_header_rec gets the batch_id */
112
113 -- Validate batch status, report error if batch status is not Closed (4)
114 -- To reopen a batch, it has to be :
115 -- * not migrated => migrated_batch_ind <> 'Y'
116 -- * a batch => batch_type = 0
117 -- * closed => batch_status = 4
118 -- * not posted to GL => from GME_INVENTORY_TXNS_GTMP via material_detail_id
119 -- * not posted to GL => or directly from IC_TRAN_PND via batch_id
120 -- * not costed => actual_cost_ind = N
121
122 -- STEP-1: done at Public level.
123 IF (x_batch_header_rec.migrated_batch_ind = 'Y') THEN
124 RAISE cant_reopen_migrated_batch;
125 END IF;
126
127 -- STEP-2a: Validate Batch Type
128 IF (x_batch_header_rec.batch_type <> 0) THEN
129 RAISE invalid_batch_type;
130 END IF;
131
132 -- STEP-2b: Validate Batch Status
133 IF (x_batch_header_rec.batch_status <> 4) THEN
134 RAISE invalid_batch_status;
135 END IF;
136
137 /* Navin: need to uncomment.
138 Asked Srikanth to clarify the new logic of Is_batch_purged
139
140 IF x_batch_header_rec.update_inventory_ind = 'Y'
141 THEN
142 -- STEP-3: Check that transaction are not purged
143 IF gme_api_grp_pk2.Is_batch_purged(p_batch_id => x_batch_header_rec.batch_id) = TRUE
144 THEN
145 RAISE BATCH_ALREADY_PURGED;
146 END IF;
147 END IF;
148 */-- STEP-4: Validate GL Posted Indicator
149 /* Since we moved the gl_posted_ind from history to header
150 We should not call lines below */
151
152 IF x_batch_header_rec.gl_posted_ind = 1 THEN
153 RAISE batch_already_posted;
154 END IF;
155
156 -- STEP-5a: Validate the Actual Cost Indicator (1/2)
157 IF (NVL (x_batch_header_rec.actual_cost_ind, 'N') = 'Y') THEN
158 -- Return a WARNING if the period is still open.
159 -- Return an ERROR if the period is already closed
160
161 -- STEP-5b: Validate the Actual Cost Indicator (2/2)
162 IF (is_period_open (p_batch_id => x_batch_header_rec.batch_id) ) THEN
163 gme_common_pvt.log_message ('GME_API_ACTUAL_COST_DONE_ERROR');
164 ELSE
165 RAISE batch_costed_and_period_closed;
166 END IF;
167
168 l_batch_cost :=
169 gmf_cmcommon.is_batch_cost_frozen
170 (p_api_version => 2
171 ,p_init_msg_list => fnd_api.g_false
172 ,p_commit => fnd_api.g_false
173 ,x_return_status => l_return_status
174 ,x_msg_count => l_msg_count
175 ,x_msg_data => l_msg_data
176 ,p_batch_id => p_batch_header_rec.batch_id);
177
178 IF l_batch_cost = FALSE THEN
179 RAISE batch_cost_err;
180 END IF;
181 END IF;
182
183 /* Load transactions in temporary table
184 so that these can be displayed in the E-record */
185 gme_trans_engine_util.load_rsrc_trans
186 (p_batch_row => x_batch_header_rec
187 ,x_rsc_row_count => l_rsrc_cnt
188 ,x_return_status => l_status);
189
190 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
191 RAISE fnd_api.g_exc_error;
192 END IF;
193
194 /* Now we have to reopen any existing phantoms which are not
195 associated step and release type of "Automatic by step" */
196 OPEN cur_matl_phant_ids (x_batch_header_rec.batch_id);
197
198 FETCH cur_matl_phant_ids
199 BULK COLLECT INTO l_phantom_ids;
200
201 CLOSE cur_matl_phant_ids;
202
203 FOR i IN 1 .. l_phantom_ids.COUNT LOOP
204 l_batch_header.batch_id := l_phantom_ids (i);
205
206 /* Initialize batch header*/
207 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header
208 ,l_in_batch_header) ) THEN
209 RAISE batch_header_fetch_err;
210 END IF;
211
212 -- B3184949 Only reopen closed batches (can have cancelled batches when parent batch is terminated
213 IF l_in_batch_header.batch_status = 4 THEN
214 gme_reopen_batch_pvt.reopen_batch
215 (p_batch_header_rec => l_in_batch_header
216 ,p_reopen_steps => p_reopen_steps
217 ,x_batch_header_rec => l_batch_header
218 ,x_return_status => x_return_status);
219
220 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
221 RAISE phantom_batch_reopen_err;
222 END IF;
223 END IF; /* if batch_status = 4 */
224 END LOOP;
225
226 -- Reopen steps only if passed parameter is TRUE
227 IF p_reopen_steps = 'T' THEN
228 -- Now Examine the batch step(POC data) :
229 IF (x_batch_header_rec.poc_ind = 'Y') THEN
230 /* Call reopen step api to reopen the all steps */
231 gme_reopen_step_pvt.reopen_all_steps
232 (p_batch_header_rec => x_batch_header_rec
233 ,x_return_status => x_return_status);
234
235 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
236 RAISE batch_step_reopen_err;
237 END IF;
238 END IF; --delete only if we have steps in batch
239 END IF; -- reopen steps only if the parameter indicates so
240
241 -- STEP-6: Remove the batch close date from the batch header
242 -- STEP-7: Update the batch header status
243 -- STEP-8: Update the actual cost ind to 'N'
244 -- Set up the fields in output structure.
245 l_batch_close_date := x_batch_header_rec.batch_close_date;
246 x_batch_header_rec.batch_status := 3;
247 x_batch_header_rec.batch_close_date := NULL;
248 x_batch_header_rec.actual_cost_ind := 'N';
249
250 -- STEP-8: Update the batch step to the database
251
252 IF NOT (gme_batch_header_dbl.update_row (x_batch_header_rec) ) THEN
253 x_batch_header_rec.batch_status := 4;
254 x_batch_header_rec.batch_close_date := l_batch_close_date;
255 RAISE batch_header_upd_err;
256 END IF;
257
258 -- STEP-9: Insert the event into the batch history table
259 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
260 gme_debug.put_line (g_pkg_name
261 || '.'
262 || l_api_name
263 || ':'
264 || 'Create history'
265 );
266 END IF;
267
268 IF x_batch_header_rec.update_inventory_ind = 'Y' THEN
269 /* Insert the event into the batch history table */
270 IF NOT gme_common_pvt.create_history
271 (x_batch_header_rec
272 ,x_batch_header_rec.batch_status) THEN
273 RAISE batch_hist_insert_err;
274 END IF;
275 END IF;
276 /*IF NOT create_history (x_batch_header_rec) THEN
277 x_batch_header_rec.batch_status := 4;
278 x_batch_header_rec.batch_close_date := l_batch_close_date;
279 RAISE batch_hist_insert_err;
280 END IF;
281 END IF; */
282
283 /* Update the row who columns */
284 x_batch_header_rec.last_update_date := gme_common_pvt.g_timestamp;
285 x_batch_header_rec.last_updated_by := gme_common_pvt.g_user_ident;
286 x_batch_header_rec.last_update_login := gme_common_pvt.g_login_id;
287
288
289 --
290 -- Bug 5903208 - Make call to GMF to revert finalization layers for this batch
291 --
292 GMF_VIB.Revert_Finalization
293 ( p_api_version => 1.0,
294 p_init_msg_list => FND_API.G_FALSE,
295 p_batch_id => x_batch_header_rec.batch_id,
296 x_return_status => x_return_status,
297 x_msg_count => l_message_count,
298 x_msg_data => l_message_list);
299
300 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
301 THEN
302 RAISE gmf_cost_failure;
303 END IF;
304 -- End Bug 5903208
305
306 -- STEP-10: End of the process with S(uccess) or W(arning)
307 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
308 gme_debug.put_line ( g_pkg_name
309 || '.'
310 || l_api_name
311 || ':'
312 || 'NOrmal end to reopen');
313 END IF;
314 EXCEPTION
315 WHEN gmf_cost_failure THEN
316 -- Bug 5043868
317 x_return_status := FND_API.G_RET_STS_ERROR;
318
319 WHEN batch_cost_err THEN
320 x_return_status := fnd_api.g_ret_sts_error;
321 /* WHEN batch_header_locked_err THEN
322 x_return_status := fnd_api.g_ret_sts_error;
323 gme_common_pvt.log_message ('GME_BATCH_IN_USE');*/
324 WHEN batch_header_fetch_err OR fnd_api.g_exc_error THEN
325 x_return_status := fnd_api.g_ret_sts_error;
326 WHEN cant_reopen_migrated_batch THEN
327 x_return_status := fnd_api.g_ret_sts_error;
328 gme_common_pvt.log_message ('GME_API_MIG_BATCH_FOR_REOPEN');
329 WHEN invalid_batch_type THEN
330 x_return_status := fnd_api.g_ret_sts_error;
331 gme_common_pvt.log_message ('GME_API_INV_BATCH_TYPE');
332 WHEN invalid_batch_status THEN
333 x_return_status := fnd_api.g_ret_sts_error;
334 gme_common_pvt.log_message ('GME_INVALID_BATCH_STATUS'
335 ,'PROCESS'
336 ,'Reopen');
337 /* WHEN batch_already_purged THEN
338 x_return_status := fnd_api.g_ret_sts_error;
339 gme_common_pvt.log_message ('GME_API_TRANSACTIONS_PURGED');*/
340 WHEN batch_already_posted THEN
341 x_return_status := fnd_api.g_ret_sts_error;
342 gme_common_pvt.log_message ('GME_API_GL_POSTED');
343 WHEN batch_costed_and_period_closed THEN
344 x_return_status := fnd_api.g_ret_sts_error;
345 gme_common_pvt.log_message ('GME_API_COST_PERIOD_CLOSED');
346 /* WHEN marked_for_deletion THEN
347 x_return_status := fnd_api.g_ret_sts_error;
348 gme_common_pvt.log_message ('GME_API_MARKED_FOR_DELETION');*/
349 WHEN batch_step_reopen_err THEN
350 x_return_status := fnd_api.g_ret_sts_error;
351 gme_common_pvt.log_message ('GME_API_BATCH_STEP_REOPEN_ERR');
352 WHEN batch_header_upd_err THEN
353 x_return_status := fnd_api.g_ret_sts_error;
354 WHEN batch_hist_insert_err THEN
355 x_return_status := fnd_api.g_ret_sts_error;
356 WHEN phantom_batch_reopen_err THEN
357 NULL;
358 WHEN OTHERS THEN
359 x_return_status := fnd_api.g_ret_sts_unexp_error;
360 fnd_msg_pub.add_exc_msg (g_package_name, l_api_name);
361 END reopen_batch;
362
363 /*===============================================================================
364 Function
365 is_batch_posted
366 Description
367 This function return TRUE is one record is posted
368 for the batch_id or material_detail_id
369
370 Parameters
371 p_batch_id Batch header id
372 p_material_detail_id material_detail_id
373 ==================================================================================*/
374 FUNCTION is_batch_posted (
375 p_batch_id IN NUMBER DEFAULT NULL
376 ,p_material_detail_id IN NUMBER DEFAULT NULL)
377 RETURN BOOLEAN
378 IS
379 /* Local variable definitions */
380 l_is_posted NUMBER;
381 l_api_name CONSTANT VARCHAR2 (30) := 'is_batch_posted';
382 batch_already_posted EXCEPTION;
383 others_error EXCEPTION;
384
385 CURSOR c_posted_in_history (l_batch_id IN NUMBER)
386 IS
387 SELECT gl_posted_ind
388 FROM gme_batch_header
389 WHERE gl_posted_ind > 1
390 AND batch_status = 4
391 AND batch_id = l_batch_id;
392 BEGIN
393 IF (p_batch_id IS NOT NULL) THEN
394 OPEN c_posted_in_history (p_batch_id);
395
396 FETCH c_posted_in_history
397 INTO l_is_posted;
398
399 IF (c_posted_in_history%FOUND) THEN
400 CLOSE c_posted_in_history;
401
402 RAISE batch_already_posted;
403 END IF;
404
405 CLOSE c_posted_in_history;
406 ELSE
407 RAISE others_error;
408 END IF;
409
410 RETURN FALSE;
411 EXCEPTION
412 WHEN batch_already_posted THEN
413 RETURN TRUE;
414 WHEN others_error THEN
415 RETURN TRUE;
416 WHEN OTHERS THEN
417 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
418 RETURN FALSE;
419 END is_batch_posted;
420
421 /*===============================================================================
422 Function
423 is_period_open
424 Description
425 This function return TRUE if the relevant period is opened
426
427 Parameters
428 p_batch_id Batch ID of the batch to be reopened.
429 ==================================================================================*/
430 FUNCTION is_period_open (p_batch_id IN NUMBER)
431 RETURN BOOLEAN
432 IS
433 l_cnt_frozen_matls NUMBER := 0;
434 l_api_name CONSTANT VARCHAR2 (30) := 'is_period_open';
435 period_not_found EXCEPTION;
436 period_not_open EXCEPTION;
437
438 CURSOR c_get_period_info (l_batch_id IN gme_batch_header.batch_id%TYPE)
439 IS
440 -- See if any of the matl lines has been costed and frozen.
441 SELECT COUNT (1)
442 FROM cm_cmpt_dtl cst
443 ,cm_acst_led aled
444 ,gme_material_details md
445 ,gme_batch_header bh
446 WHERE bh.batch_id = l_batch_id
447 AND bh.batch_id = md.batch_id
448 AND md.material_detail_id = aled.transline_id
449 AND aled.source_ind = 0
450 AND aled.cmpntcost_id = cst.cmpntcost_id
451 AND cst.rollover_ind = 1;
452 BEGIN
453 OPEN c_get_period_info (p_batch_id);
454
455 FETCH c_get_period_info
456 INTO l_cnt_frozen_matls;
457
458 IF (c_get_period_info%NOTFOUND) THEN
459 CLOSE c_get_period_info;
460
461 RAISE period_not_found;
462 END IF;
463
464 CLOSE c_get_period_info;
465
466 IF (l_cnt_frozen_matls > 0) THEN
467 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
468 gme_debug.put_line ( g_pkg_name
469 || '.'
470 || l_api_name
471 || ':'
472 ||'Period not open');
473 END IF;
474
475 RAISE period_not_open;
476 END IF;
477
478 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
479 gme_debug.put_line ( g_pkg_name
480 || '.'
481 || l_api_name
482 || ':'
483 || 'period Open, so Warning if the batch has been costed' );
484 END IF;
485
486 gme_common_pvt.log_message ('GME_API_COST_PERIOD_OPEN');
487 RETURN TRUE;
488 EXCEPTION
489 WHEN period_not_found THEN
490 RETURN FALSE;
491 WHEN period_not_open THEN
492 RETURN FALSE;
493 WHEN OTHERS THEN
494 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
495 RETURN FALSE;
496 END is_period_open;
497
498 END gme_reopen_batch_pvt;
[Home] [Help]