1 PACKAGE BODY gme_close_batch_pvt AS
2 /* $Header: GMEVCLBB.pls 120.14.12020000.2 2012/07/26 15:28:58 gmurator ship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_CLOSE_BATCH_PVT';
5
6 /*================================================================================
7 Procedure
8 Close_Batch
9 Description
10 This particular procedure call close the batch.
11 Parameters
12 p_batch_header The batch header row to identify the batch
13 x_return_status outcome of the API call
14 S - Success
15 E - Error
16 U - Unexpected error
17 History
18 13JUN02 Chandrashekar Tiruvidula Bug 2377670
19 Added call to function check_close_date to validate close date.
20 16JUL02 Bharati Satpute Bug2395188
21 Added check for enforce step dependency
22 08-AUG-2002 Shrikant Nene Bug# 2495557
23 Close the "not yet closed" phantom batches.
24
25 02-JUL-2009 G. Muratore Bug 8636368
26 Enforce step dependency validation is not required when closing steps.
27
28 04-OCT-2010 G. Muratore Bug 10100973
29 Remove orphan move order line records if they exist.
30
31 15-FEB-2011 G. Muratore Bug 10161288/11067065
32 Remove any remaining open move order line records if they exist.
33
34 06-JUN-2011 G. Muratore Bug 12546780 rework of 10161288
35 Do not remove invisible move order line records if they exist.
36 This will be done in GMF code at the time the batch is final posted.
37 ================================================================================*/
38 PROCEDURE close_batch (
39 p_batch_header_rec IN gme_batch_header%ROWTYPE
40 ,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE
41 ,x_return_status OUT NOCOPY VARCHAR2)
42 IS
43 /* Miscellaneous */
44 l_api_name CONSTANT VARCHAR2 (30) := 'close_batch';
45 l_batch_status NUMBER;
46 l_back_flush NUMBER;
47 l_error_count NUMBER;
48 l_row_count NUMBER;
49 l_return_status VARCHAR2 (1);
50 l_ins_history gme_batch_history%ROWTYPE;
51 l_batch_step gme_batch_steps%ROWTYPE;
52 l_batch_steps_tab gme_close_batch_pvt.step_details_tab;
53 l_batch_header gme_batch_header%ROWTYPE;
54 l_in_batch_header gme_batch_header%ROWTYPE;
55 l_material_detail_ids gme_common_pvt.number_tab;
56 l_message_count NUMBER;
57 l_message_list VARCHAR2 (2000);
58 l_step_count NUMBER;
59 l_batch_header_rec gme_batch_header%ROWTYPE;
60
61 /*siva FPbug#4684029*/
62 l_gmf_cost_alloc_calc NUMBER;
63 l_total_plan_output NUMBER;
64 l_total_wip_plan_output NUMBER;
65 l_total_actual_output NUMBER;
66 l_uom VARCHAR2(3);
67 l_qty NUMBER;
68
69 marked_for_deletion EXCEPTION;
70 batch_header_upd_err EXCEPTION;
71 batch_lines_locked EXCEPTION;
72 batch_step_fetch_error EXCEPTION;
73 batch_step_close_error EXCEPTION;
74 invalid_step_status EXCEPTION;
75 invalid_batch_status EXCEPTION;
76 batch_header_fetch_error EXCEPTION;
77 dep_batch_step_fetch_err EXCEPTION;
78 batch_status_invalid EXCEPTION;
79 batch_status_closed EXCEPTION;
80 expected_error EXCEPTION;
81 unexpected_error EXCEPTION;
82 batch_hist_insert_err EXCEPTION;
83 close_phant_fail EXCEPTION;
84 batch_close_date_err EXCEPTION;
85 steps_not_closed EXCEPTION;
86 error_processing EXCEPTION; -- 4944024
87
88 /*siva FPbug#4684029*/
89 ERROR_IN_GET_TOTAL_QTY EXCEPTION;
90 MATERIAL_DETAIL_UPD_ERR EXCEPTION;
91
92 -- Bug 5903208
93 gmf_cost_failure EXCEPTION;
94
95 -- Bug 10100973
96 l_material_details_tab gme_common_pvt.material_details_tab;
97 l_material_details_rec gme_material_details%ROWTYPE;
98 material_details_fetch_err EXCEPTION;
99
100 -- Bug 10161288
101 purge_exception_err EXCEPTION;
102
103 /* Get only the phantom ingredients which are not close already */
104 CURSOR cur_get_phant
105 IS
106 SELECT phantom_id
107 FROM gme_material_details d, gme_batch_header h
108 WHERE d.batch_id = x_batch_header_rec.batch_id
109 AND phantom_id IS NOT NULL
110 AND h.batch_id = d.phantom_id
111 AND h.batch_status <> 4;
112
113 CURSOR cur_lock_material_lines (v_batch_id NUMBER)
114 IS
115 SELECT material_detail_id
116 FROM gme_material_details
117 WHERE batch_id = v_batch_id
118 FOR UPDATE OF actual_qty NOWAIT;
119
120 CURSOR cur_gme_batch_steps
121 IS
122 SELECT COUNT (*)
123 FROM gme_batch_steps
124 WHERE step_status < 4 AND batch_id = x_batch_header_rec.batch_id;
125
126 /* siva FPbug#4684029 */
127 CURSOR cur_get_prod (v_batch_id NUMBER)
128 IS
129 SELECT *
130 FROM gme_material_details
131 WHERE batch_id = v_batch_id
132 AND line_type = 1;
133 BEGIN
134 IF g_debug <= gme_debug.g_log_procedure THEN
135 gme_debug.put_line ('Entering api ' || g_pkg_name || '.'
136 || l_api_name);
137 END IF;
138
139 /* Set the success staus to success inititally*/
140 x_return_status := fnd_api.g_ret_sts_success;
141
142 /* Initialize output batch header*/
143 IF NOT (gme_batch_header_dbl.fetch_row (p_batch_header_rec
144 ,x_batch_header_rec) ) THEN
145 RAISE batch_header_fetch_error;
146 END IF;
147
148 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
149 gme_debug.put_line ( 'Closing batch '
150 || x_batch_header_rec.batch_no
151 || ' at '
152 || TO_CHAR (p_batch_header_rec.batch_close_date
153 ,'DD-MON-YYYY HH24:MI:SS') );
154 END IF;
155
156 /* Validate batch status, report error if batch is not certified */
157 IF x_batch_header_rec.batch_status <> 3 THEN
158 RAISE invalid_batch_status;
159 END IF;
160
161 /* Report error that batch is marked for deletion */
162 IF p_batch_header_rec.delete_mark = 1 THEN
163 RAISE marked_for_deletion;
164 END IF;
165
166 -- 4944024 BEGIN
167 -- Delete any reservations against this batch as a supply source
168 IF nvl(g_debug, gme_debug.g_log_procedure + 1) <= gme_debug.g_log_procedure THEN
169 gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Invoking delete_prod_supply_resv for batch header' );
170 END IF;
171
172 l_batch_header_rec := x_batch_header_rec;
173 gme_supply_res_pvt.delete_batch_prod_supply_resv (
174 p_batch_header_rec => l_batch_header_rec
175 ,x_msg_count => l_message_count
176 ,x_msg_data => l_message_list
177 ,x_return_status => x_return_status);
178
179 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
180 RAISE error_processing;
181 END IF;
182 -- 4944024 END
183
184
185 -- Bug 8636368 - This following validation is not required when closing steps.
186 /* Bharati Satpute Bug2395188 Added check for enforce step dependency */
187 /* IF x_batch_header_rec.poc_ind = 'Y' THEN
188 IF x_batch_header_rec.enforce_step_dependency = 1 THEN
189 OPEN cur_gme_batch_steps;
190
191 FETCH cur_gme_batch_steps
192 INTO l_step_count;
193
194 CLOSE cur_gme_batch_steps;
195
196 IF l_step_count > 0 THEN
197 RAISE steps_not_closed; */
198 -- END IF; /*l_step_count > 0*/
199 -- END IF; /*enforce_step_dependency = 1*/
200 -- END IF; /*poc_ind = 'Y'*/
201
202 /* Let us check if we have the lock for all the material lines */
203 OPEN cur_lock_material_lines (x_batch_header_rec.batch_id);
204
205 FETCH cur_lock_material_lines
206 BULK COLLECT INTO l_material_detail_ids;
207
208 IF SQLCODE = -54 THEN
209 RAISE batch_lines_locked;
210 END IF;
211
212 CLOSE cur_lock_material_lines;
213
214 -- Bug 10100973 - Let's remove Open orphan Move order records if they exist.
215 l_material_details_rec.batch_id := x_batch_header_rec.batch_id;
216 IF NOT (gme_material_details_dbl.fetch_tab
217 (p_material_detail => l_material_details_rec
218 ,x_material_detail => l_material_details_tab) ) THEN
219 RAISE material_details_fetch_err;
220 END IF;
221
222 FOR i IN 1 .. l_material_details_tab.COUNT LOOP
223 DELETE FROM mtl_txn_request_lines l
224 WHERE organization_id = x_batch_header_rec.organization_id
225 AND transaction_source_type_id = gme_common_pvt.g_txn_source_type
226 AND txn_source_id = x_batch_header_rec.batch_id
227 AND txn_source_line_id = l_material_details_tab(i).material_detail_id
228 AND line_status = 7
229 AND NOT EXISTS (SELECT 1
230 FROM mtl_txn_request_headers mtrh
231 WHERE mtrh.header_id = l.header_id)
232 AND NOT EXISTS (SELECT 1
233 FROM mtl_material_transactions_temp mmtt
234 where l.line_id = mmtt.move_order_line_id);
235 END LOOP;
236 -- End Bug 10100973
237
238 -- Bug 10161288 - Added following block to remove any open MO line.
239 IF x_batch_header_rec.update_inventory_ind = 'Y' THEN
240 IF (g_debug <= gme_debug.g_log_procedure) THEN
241 gme_debug.put_line ( g_pkg_name
242 || '.'
243 || l_api_name
244 || ':'
245 || 'calling purge exceptions for batch_id='
246 || x_batch_header_rec.batch_id);
247 END IF;
248
249 -- Bug 12546780 - Do not delete invisible move orders.
250 -- Delete all move-orders excluding invisible move orders
251 gme_cancel_batch_pvt.purge_batch_exceptions
252 (p_batch_header_rec => x_batch_header_rec
253 ,p_delete_reservations => 'T'
254 ,x_return_status => x_return_status);
255
256 IF x_return_status <> fnd_api.g_ret_sts_success THEN
257 RAISE purge_exception_err;
258 END IF;
259 END IF;
260
261 -- BUG 11067065 - Let's close any remaining open putaway MO line.
262 UPDATE MTL_TXN_REQUEST_Lines
263 SET line_status = 5
264 WHERE line_id in
265 (SELECT mtrl.line_id
266 FROM MTL_TXN_REQUEST_Lines mtrl, mtl_txn_request_headers mtrh
267 WHERE mtrl.TRANSACTION_SOURCE_TYPE_ID = 5
268 AND mtrl.LINE_STATUS = 7
269 AND mtrl.LPN_ID IS NOT NULL
270 AND mtrh.header_id = mtrl.header_id
271 AND mtrh.ORGANIZATION_ID = mtrl.organization_id
272 AND mtrl.txn_source_id = x_batch_header_rec.batch_id
273 AND mtrl.ORGANIZATION_ID = x_batch_header_rec.organization_id
274 AND mtrh.MOVE_ORDER_TYPE = 6);
275
276 /*siva FPbug#4684029 getting profile option value */
277 l_gmf_cost_alloc_calc := FND_PROFILE.VALUE('GMF_COST_ALLOC_CALC');
278
279 /* If batch close date is not provided use sysdate */
280 IF p_batch_header_rec.batch_close_date IS NULL THEN
281 x_batch_header_rec.batch_close_date := gme_common_pvt.g_timestamp;
282 ELSE
283 x_batch_header_rec.batch_close_date :=
284 p_batch_header_rec.batch_close_date;
285 END IF;
286
287 /* Check batch close date is not less than step close or transaction dates */
288 IF NOT (gme_close_batch_pvt.check_close_date (x_batch_header_rec) ) THEN
289 RAISE batch_close_date_err;
290 END IF;
291
292 IF g_debug <= gme_debug.g_log_procedure THEN
293 gme_debug.put_line ('Calling Batch Header DBL.Update Row ');
294 END IF;
295
296 /* Set up the fields in output structure. */
297 x_batch_header_rec.batch_status := 4;
298
299 /* Update the batch step to the database */
300 IF NOT (gme_batch_header_dbl.update_row (x_batch_header_rec) ) THEN
301 RAISE batch_header_upd_err;
302 END IF;
303
304 /* Insert the event into the batch history table */
305 IF x_batch_header_rec.update_inventory_ind = 'Y' THEN
306 IF g_debug <= gme_debug.g_log_procedure THEN
307 gme_debug.put_line ('Calling Create History.');
308 END IF;
309
310 IF NOT gme_common_pvt.create_history
311 (p_batch_header_rec => x_batch_header_rec
312 ,p_original_status => 3) THEN
313 RAISE batch_hist_insert_err;
314 END IF;
315 /* IF NOT create_history(x_batch_header_rec) THEN
316 RAISE BATCH_HIST_INSERT_ERR;
317 END IF;
318 */
319 END IF;
320
321 /* Now we have to close any associated batches */
322 FOR l_rec IN cur_get_phant LOOP
323 l_batch_header.batch_id := l_rec.phantom_id;
324
325 /* Initialize batch header*/
326 IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header
327 ,l_in_batch_header) ) THEN
328 RAISE batch_header_fetch_error;
329 END IF;
330
331 /* Close only completed phantom batches; can have cancelled phantom batches if the batch was terminated */
332 IF l_in_batch_header.batch_status = 3 THEN
333 /* x_batch_header_rec has either sysdate or the user passed-in date */
334 l_in_batch_header.batch_close_date :=
335 x_batch_header_rec.batch_close_date;
336 gme_close_batch_pvt.close_batch
337 (p_batch_header_rec => l_in_batch_header
338 ,x_batch_header_rec => l_batch_header
339 ,x_return_status => l_return_status);
340
341 IF l_return_status <> x_return_status THEN
342 RAISE close_phant_fail;
343 END IF;
344 END IF; /* batch_status = 3 */
345 END LOOP;
346
347 IF x_batch_header_rec.poc_ind = 'Y' THEN
348 /* Now update the batch step status to close */
349 gme_close_batch_pvt.fetch_batch_steps (x_batch_header_rec.batch_id
350 ,NULL
351 ,l_batch_steps_tab
352 ,l_return_status);
353
354 IF l_return_status <> x_return_status THEN
355 RAISE dep_batch_step_fetch_err;
356 END IF;
357
358 FOR i IN 1 .. l_batch_steps_tab.COUNT LOOP
359 /* Call Close step api to close the all steps */
360 IF (l_batch_steps_tab (i).step_status = 3) THEN
361 /* x_batch_header_rec has either sysdate or the user passed-in date */
362 l_batch_steps_tab (i).step_close_date :=
363 x_batch_header_rec.batch_close_date;
364 gme_close_step_pvt.close_step
365 (p_batch_step_rec => l_batch_steps_tab
366 (i)
367 ,x_batch_step_rec => l_batch_step
368 ,x_return_status => l_return_status);
369
370 IF l_return_status <> x_return_status THEN
371 RAISE batch_step_close_error;
372 END IF;
373 END IF;
374 END LOOP;
375 END IF;
376
377 /* Update the row who columns */
378 x_batch_header_rec.last_update_date := gme_common_pvt.g_timestamp;
379 x_batch_header_rec.last_updated_by := gme_common_pvt.g_user_ident;
380 x_batch_header_rec.last_update_login := gme_common_pvt.g_login_id;
381
382 /*siva FPBug#4684029 BEGIN
383 calculate costs if dynamic allocation of cost is enabled through profile*/
384 IF l_gmf_cost_alloc_calc = 1 THEN
385 /* get total plan and actual quantities if cost allocation is dynamic */
386 gme_api_grp.get_total_qty(
387 x_batch_header_rec.batch_id,
388 1,
389 NULL,
390 l_total_plan_output,
391 l_total_wip_plan_output, --Bug#5111078 get_total_qty signature is changed
392 l_total_actual_output,
393 l_uom,
394 l_return_status
395 );
396 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
397 RAISE ERROR_IN_GET_TOTAL_QTY;
398 ELSE
399 -- Pawan Added for bug 5256361
400 IF l_total_actual_output > 0 THEN
401 /* fetch product lines */
402 FOR l_product_rec IN cur_get_prod (x_batch_header_rec.batch_id) LOOP
403 IF l_product_rec.dtl_um <> l_uom THEN
404 l_qty := inv_convert.inv_um_convert(
405 l_product_rec.inventory_item_id,
406 5,
407 l_product_rec.actual_qty,
408 l_product_rec.dtl_um,
409 l_uom,
410 NULL,
411 NULL);
412 ELSE
413 l_qty := l_product_rec.actual_qty;
414 END IF;
415 /* dynamic cost factor is ratio of actual qty to total product qty */
416 l_product_rec.cost_alloc := l_qty/l_total_actual_output;
417
418 /* updating the material detail record with new cost factor*/
419 IF NOT gme_material_details_dbl.update_row(l_product_rec) THEN
420 RAISE MATERIAL_DETAIL_UPD_ERR;
421 END IF;
422 END LOOP;
423 END IF;
424 END IF;
425 END IF; /* profile condition IF*/
426
427 /* FPBug#4684029 END */
428
429
430 --
431 -- Bug 5903208 - call to GMF
432 --
433 GMF_VIB.Finalize_VIB_Details
434 ( p_api_version => 1.0,
435 p_init_msg_list => FND_API.G_FALSE,
436 p_batch_id => x_batch_header_rec.batch_id,
437 x_return_status => x_return_status,
438 x_msg_count => l_message_count,
439 x_msg_data => l_message_list);
440
441 IF x_return_status <> FND_API.G_RET_STS_SUCCESS
442 THEN
443 RAISE gmf_cost_failure;
444 END IF;
445
446 EXCEPTION
447 WHEN gmf_cost_failure THEN
448 -- Bug 5903208
449 x_return_status := FND_API.G_RET_STS_ERROR;
450
451 WHEN batch_header_fetch_error THEN
452 x_return_status := fnd_api.g_ret_sts_error;
453 WHEN invalid_batch_status THEN
454 x_return_status := fnd_api.g_ret_sts_error;
455 gme_common_pvt.log_message ('GME_INVALID_BATCH_STATUS'
456 ,'PROCESS'
457 ,'Close');
458 WHEN batch_lines_locked OR app_exception.record_lock_exception THEN
459 x_return_status := fnd_api.g_ret_sts_error;
460 gme_common_pvt.log_message ('GME_API_BATCH_LINES_LOCKED');
461 WHEN marked_for_deletion THEN
462 x_return_status := fnd_api.g_ret_sts_error;
463 gme_common_pvt.log_message ('GME_API_MARKED_FOR_DELETION');
464 WHEN steps_not_closed THEN
465 x_return_status := fnd_api.g_ret_sts_error;
466 gme_common_pvt.log_message ('GME_API_STEPS_NOT_CLOSE');
467 --FPBug#4684029 added MATERIAL_DETAIL_UPD_ERR exception
468 WHEN batch_header_upd_err OR batch_close_date_err OR material_detail_upd_err THEN
469 x_return_status := fnd_api.g_ret_sts_error;
470 WHEN batch_hist_insert_err THEN
471 x_return_status := fnd_api.g_ret_sts_error;
472 WHEN dep_batch_step_fetch_err THEN
473 x_return_status := fnd_api.g_ret_sts_error;
474 WHEN batch_step_close_error THEN
475 x_return_status := fnd_api.g_ret_sts_error;
476 --FPBug#4684029
477 WHEN error_in_get_total_qty THEN
478 x_return_status := l_return_status;
479 WHEN close_phant_fail THEN
480 x_return_status := l_return_status;
481 WHEN error_processing THEN -- 4944024 BEGIN
482 IF g_debug <= gme_debug.g_log_procedure THEN
483 gme_debug.put_line ('Terminating due to error deleteing reservations against this batch supply.');
484 END IF;
485 -- 4944024 END
486 WHEN material_details_fetch_err THEN
487 IF (g_debug <= gme_debug.g_log_procedure) THEN
488 gme_debug.put_line ( g_pkg_name
489 || '.'
490 || l_api_name
491 || ':'
492 || 'MATERIAL_FETCH_ERROR');
493 END IF;
494
495 x_return_status := fnd_api.g_ret_sts_error;
496 WHEN purge_exception_err THEN
497 IF (g_debug <= gme_debug.g_log_procedure) THEN
498 gme_debug.put_line ( g_pkg_name
499 || '.'
500 || l_api_name
501 || ':'
502 || 'purge_exception_err');
503 END IF;
504 WHEN OTHERS THEN
505 x_return_status := fnd_api.g_ret_sts_unexp_error;
506 fnd_msg_pub.add_exc_msg ('GME_CLOSE_BATCH_PVT', 'CLOSE_BATCH');
507 END close_batch;
508
509 /*===============================================================================
510 Function
511 Create_History
512 Description
513 This procedure is used to record an entry into the batch history table.
514
515 Parameters
516 p_batch_header_rec Batch header row
517 ==================================================================================*/
518 FUNCTION create_history (p_batch_header_rec IN gme_batch_header%ROWTYPE)
519 RETURN BOOLEAN
520 IS
521 /* Local variable definitions */
522 l_return_status VARCHAR2 (1);
523 /* Buffers for database reads/writes */
524 l_ins_history gme_batch_history%ROWTYPE;
525 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_HISTORY';
526 BEGIN
527 l_ins_history.batch_id := p_batch_header_rec.batch_id;
528 l_ins_history.orig_status := 3;
529 l_ins_history.new_status := 4;
530 /*
531 l_ins_history.orig_wip_whse := p_batch_header_rec.wip_whse_code;
532 l_ins_history.new_wip_whse := p_batch_header_rec.wip_whse_code;
533 */
534 l_ins_history.gl_posted_ind := 0;
535
536 IF NOT (gme_batch_history_dbl.insert_row (l_ins_history, l_ins_history) ) THEN
537 RETURN FALSE;
538 ELSE
539 RETURN TRUE;
540 END IF;
541 --Bug2804440
542 EXCEPTION
543 WHEN OTHERS THEN
544 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
545 RETURN FALSE;
546 --End Bug2804440
547 END create_history;
548
549 /*===============================================================================
550 Function
551 check_close_date
552 Description
553 Created for BUG 2377670.
554 This procedure is used to check the batch close date against all steps
555 and transactions.
556
557 Parameters
558 p_batch_header_rec Batch header row
559 History
560 ==================================================================================*/
561 FUNCTION check_close_date (p_batch_header_rec IN gme_batch_header%ROWTYPE)
562 RETURN BOOLEAN
563 IS
564 CURSOR cur_batch_steps (v_batch_id NUMBER)
565 IS
566 SELECT step_close_date
567 FROM gme_batch_steps
568 WHERE batch_id IN (
569 SELECT DISTINCT batch_id
570 FROM gme_material_details
571 START WITH batch_id =
572 p_batch_header_rec.batch_id
573 CONNECT BY batch_id = PRIOR phantom_id)
574 AND NVL (step_close_date, p_batch_header_rec.batch_close_date) >
575 p_batch_header_rec.batch_close_date;
576
577 CURSOR cur_get_batches (v_batch_id NUMBER)
578 IS
579 SELECT DISTINCT batch_id
580 FROM gme_material_details
581 START WITH batch_id = v_batch_id
582 CONNECT BY batch_id = PRIOR phantom_id;
583
584 x_batch_tbl gme_common_pvt.number_tab;
585 x_batch_row gme_batch_header%ROWTYPE;
586 x_mat_cnt NUMBER;
587 x_rsrc_cnt NUMBER;
588 x_status VARCHAR2 (10);
589 x_date DATE;
590 l_api_name CONSTANT VARCHAR2 (30) := 'CHECK_CLOSE_DATE';
591
592
593 x_material_tbl gme_common_pvt.number_tab;
594
595 CURSOR cur_rsrc_txns (v_batch_id NUMBER)
596 IS
597 SELECT trans_date
598 FROM gme_resource_txns_gtmp
599 WHERE doc_type = 'PROD'
600 AND doc_id = v_batch_id
601 AND completed_ind = 1
602 AND delete_mark = 0
603 AND action_code NOT IN ('DEL', 'REVS', 'REVL')
604 AND NVL (trans_date, p_batch_header_rec.batch_close_date) >
605 p_batch_header_rec.batch_close_date
606 AND ROWNUM = 1;
607
608 /* CURSOR cur_inventory_txns (v_batch_id NUMBER)
609 IS
610 SELECT trans_date
611 FROM gme_inventory_txns_gtmp
612 WHERE doc_type = 'PROD'
613 AND doc_id = v_batch_id
614 AND completed_ind = 1
615 AND transaction_no <> 2
616 AND action_code NOT IN ('DELP', 'DELC')
617 AND NVL (trans_date, p_batch_header_rec.batch_close_date) >
618 p_batch_header_rec.batch_close_date; */
619 /* Bug#7208400 The below two cursors are added to validate the batch close date
620 against the transactions of the materials in a batch */
621 CURSOR cur_get_materials(v_batch_id NUMBER)
622 IS
623 SELECT material_detail_id
624 from gme_material_details
625 WHERE batch_id = v_batch_id;
626
627
628 CURSOR cur_inventory_txns (v_mat_det_id NUMBER, v_batch_id NUMBER,
629 v_txn_source_type NUMBER, v_pairs_reversal_type NUMBER)
630 IS
631 SELECT transaction_date
632 FROM mtl_material_transactions mmt
633 WHERE trx_source_line_id = v_mat_det_id
634 AND transaction_source_id = v_batch_id
635 AND transaction_source_type_id = v_txn_source_type
636 AND NOT EXISTS ( SELECT /*+ no_unnest */
637 transaction_id1
638 FROM gme_transaction_pairs
639 WHERE transaction_id1 = mmt.transaction_id
640 AND pair_type = v_pairs_reversal_type)
641 AND NVL (transaction_date, p_batch_header_rec.batch_close_date) >
642 p_batch_header_rec.batch_close_date
643 AND ROWNUM = 1;
644
645
646 CURSOR cur_batch_cmplt_date (v_batch_id NUMBER)
647 IS
648 SELECT actual_cmplt_date
649 FROM gme_batch_header
650 WHERE batch_id IN (
651 SELECT DISTINCT batch_id
652 FROM gme_material_details
653 START WITH batch_id =
654 p_batch_header_rec.batch_id
655 CONNECT BY batch_id = PRIOR phantom_id)
656 AND NVL (actual_cmplt_date, p_batch_header_rec.batch_close_date) >
657 p_batch_header_rec.batch_close_date;
658 BEGIN
659 /* Check if batch close date is greater than all batch step close dates */
660 OPEN cur_batch_steps (p_batch_header_rec.batch_id);
661
662 FETCH cur_batch_steps
663 INTO x_date;
664
665 IF (cur_batch_steps%FOUND) THEN
666 CLOSE cur_batch_steps;
667
668 gme_common_pvt.log_message ('GME_CLOSE_STEP_DATE_ERR'
669 ,'D1'
670 ,fnd_date.date_to_displaydt (x_date) );
671 RETURN FALSE;
672 END IF;
673
674 CLOSE cur_batch_steps;
675
676 IF (p_batch_header_rec.update_inventory_ind = 'Y') THEN
677 OPEN cur_get_batches (p_batch_header_rec.batch_id);
678
679 FETCH cur_get_batches
680 BULK COLLECT INTO x_batch_tbl;
681
682 CLOSE cur_get_batches;
683
684 FOR i IN 1 .. x_batch_tbl.COUNT LOOP
685 x_batch_row.batch_id := x_batch_tbl (i);
686
687 IF NOT gme_batch_header_dbl.fetch_row (x_batch_row, x_batch_row) THEN
688 RETURN FALSE;
689 END IF;
690
691 gme_trans_engine_util.load_rsrc_trans
692 (p_batch_row => x_batch_row
693 ,x_rsc_row_count => x_rsrc_cnt
694 ,x_return_status => x_status);
695
696 IF (NVL (x_rsrc_cnt, 0) > 0) THEN
697 /* Check if the batch close date is greater than all completed resource transaction dates */
698 OPEN cur_rsrc_txns (x_batch_tbl (i) );
699
700 FETCH cur_rsrc_txns
701 INTO x_date;
702
703 IF (cur_rsrc_txns%FOUND) THEN
704 CLOSE cur_rsrc_txns;
705
706 gme_common_pvt.log_message
707 ('GME_CLOSE_RSRC_DATE_ERR'
708 ,'D1'
709 ,fnd_date.date_to_displaydt (x_date) );
710 RETURN FALSE;
711 END IF;
712
713 CLOSE cur_rsrc_txns;
714 END IF;
715 /*bug#7208400 start */
716 OPEN cur_get_materials (x_batch_row.batch_id);
717
718 FETCH cur_get_materials
719 BULK COLLECT INTO x_material_tbl;
720
721 CLOSE cur_get_materials;
722
723 FOR j IN 1 .. x_material_tbl.COUNT LOOP
724 /* Check if the batch close date is greater than all completed inventory transaction dates */
725 OPEN cur_inventory_txns (x_material_tbl (j),x_batch_tbl (i),gme_common_pvt.g_txn_source_type, gme_common_pvt.g_pairs_reversal_type );
726
727 FETCH cur_inventory_txns
728 INTO x_date;
729 IF (cur_inventory_txns%FOUND) THEN
730 CLOSE cur_inventory_txns;
731 gme_common_pvt.log_message('GME_CLOSE_INVEN_DATE_ERR', 'D1', fnd_date.date_to_displayDT(X_date));
732 RETURN FALSE;
733 END IF;
734
735 CLOSE cur_inventory_txns;
736 END LOOP;
737 /*bug#7208400 End */
738 END LOOP;
739 END IF;
740
741
742 /* Check if batch close date is greater than all batch complete dates */
743 OPEN cur_batch_cmplt_date (p_batch_header_rec.batch_id);
744
745 FETCH cur_batch_cmplt_date
746 INTO x_date;
747
748 IF (cur_batch_cmplt_date%FOUND) THEN
749 CLOSE cur_batch_cmplt_date;
750
751 gme_common_pvt.log_message ('GME_CLOSE_CMPLT_DATE_ERR'
752 ,'D1'
753 ,fnd_date.date_to_displaydt (x_date) );
754 RETURN FALSE;
755 END IF;
756
757 CLOSE cur_batch_cmplt_date;
758
759 RETURN TRUE;
760 EXCEPTION
761 WHEN OTHERS THEN
762 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
763 RETURN FALSE;
764 END check_close_date;
765
766 PROCEDURE fetch_batch_steps (
767 p_batch_id IN NUMBER
768 ,p_batchstep_id IN NUMBER
769 ,x_step_tbl OUT NOCOPY step_details_tab
770 ,x_return_status OUT NOCOPY VARCHAR2)
771 IS
772 /* Local variables.
773 ==================*/
774 l_num_steps NUMBER := 0;
775 l_routing_id NUMBER;
776 l_step_id NUMBER;
777
778 /* Cursor Definitions.
779 =====================*/
780 CURSOR cur_get_routing (v_batch_id NUMBER)
781 IS
782 SELECT routing_id
783 FROM gme_batch_header
784 WHERE batch_id = v_batch_id;
785
786 CURSOR cur_get_steps (v_batch_id NUMBER, v_batchstep_id NUMBER)
787 IS
788 SELECT d.dep_step_id
789 FROM gme_batch_step_dependencies d
790 WHERE d.batch_id = v_batch_id
791 START WITH ( (d.batch_id = v_batch_id)
792 AND ( (v_batchstep_id IS NULL)
793 OR (batchstep_id = v_batchstep_id) ) )
794 CONNECT BY d.batch_id = PRIOR d.batch_id
795 AND d.batchstep_id = PRIOR d.dep_step_id
796 GROUP BY d.dep_step_id
797 ORDER BY MAX (LEVEL) DESC;
798
799 CURSOR cur_get_step_rec (v_batch_id NUMBER, v_step_id NUMBER)
800 IS
801 SELECT *
802 FROM gme_batch_steps
803 WHERE batch_id = v_batch_id AND batchstep_id = v_step_id;
804
805 CURSOR cur_get_final_steps (v_batch_id NUMBER)
806 IS
807 SELECT *
808 FROM gme_batch_steps s
809 WHERE s.batch_id = v_batch_id
810 AND s.batchstep_id NOT IN (SELECT dep_step_id
811 FROM gme_batch_step_dependencies
812 WHERE batch_id = v_batch_id)
813 ORDER BY batchstep_no;
814
815 /* Buffer records for database read.
816 ===================================*/
817 l_step_rec gme_batch_steps%ROWTYPE;
818 /* Exceptions.
819 =====================*/
820 no_routing_associated EXCEPTION;
821 step_details_missing EXCEPTION;
822 circular_reference EXCEPTION;
823 PRAGMA EXCEPTION_INIT (circular_reference, -01436);
824 BEGIN
825 x_return_status := fnd_api.g_ret_sts_success;
826
827 /* Fetch the routing for the batch passed in */
828 OPEN cur_get_routing (p_batch_id);
829
830 FETCH cur_get_routing
831 INTO l_routing_id;
832
833 IF cur_get_routing%NOTFOUND THEN
834 CLOSE cur_get_routing;
835
836 RAISE no_routing_associated;
837 END IF;
838
839 CLOSE cur_get_routing;
840
841 /* Get the routing steps from the PM dependency table */
842 OPEN cur_get_steps (p_batch_id, p_batchstep_id);
843
844 FETCH cur_get_steps
845 INTO l_step_id;
846
847 /* Add the steps to the pl/sql table */
848 WHILE cur_get_steps%FOUND LOOP
849 l_num_steps := l_num_steps + 1;
850
851 /* Get the step details */
852 OPEN cur_get_step_rec (p_batch_id, l_step_id);
853
854 FETCH cur_get_step_rec
855 INTO l_step_rec;
856
857 CLOSE cur_get_step_rec;
858
859 x_step_tbl (l_num_steps) := l_step_rec;
860
861 FETCH cur_get_steps
862 INTO l_step_id;
863 END LOOP; /* WHILE Cur_get_steps%FOUND */
864
865 CLOSE cur_get_steps;
866
867 -- Bug 14123348 - This block is here to fetch any steps which have no dependencies.
868 -- If there aren't any it's ok... It is not a failure.
869
870 /* Populate the pl/sql table with the final steps based on the dependencies */
871 /* only if it is being called for the entire batch */
872 IF p_batchstep_id IS NULL THEN
873 OPEN cur_get_final_steps (p_batch_id);
874
875 FETCH cur_get_final_steps
876 INTO l_step_rec;
877
878 IF cur_get_final_steps%FOUND THEN
879 WHILE cur_get_final_steps%FOUND LOOP
880 l_num_steps := l_num_steps + 1;
881 x_step_tbl (l_num_steps) := l_step_rec;
882
883 FETCH cur_get_final_steps
884 INTO l_step_rec;
885 END LOOP;
886 -- Bug 14123348 - Comment out else path as this is not really a failure.
887 -- ELSE
888 -- CLOSE cur_get_final_steps;
889
890 -- RAISE step_details_missing;
891 END IF;
892
893 CLOSE cur_get_final_steps;
894 END IF; /* IF p_batchstep_id IS NULL */
895 EXCEPTION
896 WHEN no_routing_associated THEN
897 x_return_status := fnd_api.g_ret_sts_error;
898 fnd_message.set_name ('GMD', 'GMD_NO_ROUTING_ASSOCIATED');
899 fnd_msg_pub.ADD;
900 WHEN step_details_missing THEN
901 x_return_status := fnd_api.g_ret_sts_error;
902 fnd_message.set_name ('GMD', 'GME_STEP_DETAILS_MISSING');
903 fnd_msg_pub.ADD;
904 WHEN circular_reference THEN
905 fnd_message.set_name ('GMD', 'GMD_CIRCULAR_DEPEN_DETECT');
906 fnd_msg_pub.ADD;
907 x_return_status := fnd_api.g_ret_sts_error;
908 WHEN OTHERS THEN
909 x_return_status := fnd_api.g_ret_sts_unexp_error;
910 fnd_message.set_name ('GMD', 'GMD_UNEXPECTED_ERROR');
911 fnd_message.set_token ('ERROR', SQLERRM);
912 fnd_msg_pub.ADD;
913 END fetch_batch_steps;
914 END gme_close_batch_pvt;
[Home] [Help]