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