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