[Home] [Help]
150: ,p_phantom_trans IN NUMBER DEFAULT 0
151: ,x_return_status OUT NOCOPY VARCHAR2)
152: IS
153: l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_MATERIAL_TXN';
154: l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
155: l_mmti_rec mtl_transactions_interface%ROWTYPE;
156: l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
157: m_mmti_rec mtl_transactions_interface%ROWTYPE;
158: m_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
174: -- bug 9832061
175: l_trans_date DATE;
176: BEGIN
177: /* Initially let us assign the return status to success */
178: x_return_status := fnd_api.g_ret_sts_success;
179:
180: IF (g_debug <= gme_debug.g_log_statement) THEN
181: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
182: || 'Entering');
189: -- Now fetch the material details for the material
190: IF NOT gme_material_details_dbl.fetch_row
191: (p_material_detail => l_mat_dtl_rec
192: ,x_material_detail => l_mat_dtl_rec) THEN
193: RAISE fnd_api.g_exc_error;
194: END IF;
195:
196: IF (g_debug <= gme_debug.g_log_statement) THEN
197: gme_debug.put_line ( g_pkg_name
260: ,p_assign_phantom => l_assign_phantom
261: ,x_mmti_rec => x_mmti_rec
262: ,x_mmli_tbl => x_mmli_tbl
263: ,x_return_status => l_return_status);
264: IF l_return_status <> fnd_api.g_ret_sts_success THEN
265: RAISE build_txn_inter_err;
266: END IF;
267: l_assign_phantom := 0;
268:
314: -- Now fetch the material details for the phantom line
315: IF NOT gme_material_details_dbl.fetch_row
316: (p_material_detail => m_mat_dtl_rec
317: ,x_material_detail => m_mat_dtl_rec) THEN
318: RAISE fnd_api.g_exc_error;
319: END IF;
320:
321: m_mmti_rec.transaction_source_id := m_mat_dtl_rec.batch_id;
322:
417: ,x_mmti_rec => x_mmti_rec
418: ,x_mmli_tbl => x_mmli_tbl
419: ,x_return_status => l_return_status);
420:
421: IF l_return_status <> fnd_api.g_ret_sts_success THEN
422: RAISE build_txn_inter_err;
423: END IF;
424:
425: IF (g_debug <= gme_debug.g_log_statement) THEN
433: END IF; -- for phantom
434: END IF; --p_phantom_trans <> 1 THEN
435:
436: -- code for moving the data to temp
437: IF gme_common_pvt.g_move_to_temp = fnd_api.g_true THEN
438: IF (g_debug <= gme_debug.g_log_statement) THEN
439: gme_debug.put_line ( g_pkg_name
440: || '.'
441: || l_api_name
463:
464: -- Bug 14461780 - pass validation level as full again so exp and orig dates get computed correctly.
465: l_ret := inv_txn_manager_grp.validate_transactions
466: (p_api_version => l_api_version
467: ,p_init_msg_list => fnd_api.g_true
468: ,p_validation_level => fnd_api.g_valid_level_full -- do not change this !!!
469: -- bug 12836004
470: --,p_validation_level => fnd_api.g_valid_level_none
471: ,p_header_id => gme_common_pvt.g_transaction_header_id
464: -- Bug 14461780 - pass validation level as full again so exp and orig dates get computed correctly.
465: l_ret := inv_txn_manager_grp.validate_transactions
466: (p_api_version => l_api_version
467: ,p_init_msg_list => fnd_api.g_true
468: ,p_validation_level => fnd_api.g_valid_level_full -- do not change this !!!
469: -- bug 12836004
470: --,p_validation_level => fnd_api.g_valid_level_none
471: ,p_header_id => gme_common_pvt.g_transaction_header_id
472: ,x_return_status => l_return_status
466: (p_api_version => l_api_version
467: ,p_init_msg_list => fnd_api.g_true
468: ,p_validation_level => fnd_api.g_valid_level_full -- do not change this !!!
469: -- bug 12836004
470: --,p_validation_level => fnd_api.g_valid_level_none
471: ,p_header_id => gme_common_pvt.g_transaction_header_id
472: ,x_return_status => l_return_status
473: ,x_msg_count => l_msg_count
474: ,x_msg_data => l_msg_data
472: ,x_return_status => l_return_status
473: ,x_msg_count => l_msg_count
474: ,x_msg_data => l_msg_data
475: ,x_trans_count => l_txn_count
476: ,p_free_tree => fnd_api.g_false);
477: IF (g_debug <= gme_debug.g_log_statement) THEN
478: gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'after validate transactions:'|| l_ret);
479: gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'x_trans_count:'|| l_txn_count);
480: select count(*) into l_cnt_temp
508: ,p_token1_name => 'MESSAGE'
509: ,p_token1_value => get_msgs.error_explanation);
510: END LOOP;
511: END IF;
512: RAISE fnd_api.g_exc_error;
513: END IF;
514: END IF;
515: IF (g_debug <= gme_debug.g_log_statement) THEN
516: gme_debug.put_line ( g_pkg_name
524: WHEN build_txn_inter_err THEN
525: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'
526: ||' error from build_txn_inter');
527: x_return_status := l_return_status;
528: WHEN fnd_api.g_exc_error THEN
529: x_return_status := fnd_api.g_ret_sts_error;
530: WHEN fnd_api.g_exc_unexpected_error THEN
531: x_return_status := fnd_api.g_ret_sts_unexp_error;
532: WHEN OTHERS THEN
525: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'
526: ||' error from build_txn_inter');
527: x_return_status := l_return_status;
528: WHEN fnd_api.g_exc_error THEN
529: x_return_status := fnd_api.g_ret_sts_error;
530: WHEN fnd_api.g_exc_unexpected_error THEN
531: x_return_status := fnd_api.g_ret_sts_unexp_error;
532: WHEN OTHERS THEN
533: x_return_status := fnd_api.g_ret_sts_unexp_error;
526: ||' error from build_txn_inter');
527: x_return_status := l_return_status;
528: WHEN fnd_api.g_exc_error THEN
529: x_return_status := fnd_api.g_ret_sts_error;
530: WHEN fnd_api.g_exc_unexpected_error THEN
531: x_return_status := fnd_api.g_ret_sts_unexp_error;
532: WHEN OTHERS THEN
533: x_return_status := fnd_api.g_ret_sts_unexp_error;
534: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
527: x_return_status := l_return_status;
528: WHEN fnd_api.g_exc_error THEN
529: x_return_status := fnd_api.g_ret_sts_error;
530: WHEN fnd_api.g_exc_unexpected_error THEN
531: x_return_status := fnd_api.g_ret_sts_unexp_error;
532: WHEN OTHERS THEN
533: x_return_status := fnd_api.g_ret_sts_unexp_error;
534: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
535:
529: x_return_status := fnd_api.g_ret_sts_error;
530: WHEN fnd_api.g_exc_unexpected_error THEN
531: x_return_status := fnd_api.g_ret_sts_unexp_error;
532: WHEN OTHERS THEN
533: x_return_status := fnd_api.g_ret_sts_unexp_error;
534: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
535:
536: IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
537: gme_debug.put_line ( g_pkg_name
668: l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
669: l_mmti_rec mtl_transactions_interface%ROWTYPE;
670: l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
671: l_mat_dtl_rec gme_material_details%ROWTYPE;
672: l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
673:
674: create_material_txn_err EXCEPTION;
675: delete_material_txn_err EXCEPTION;
676:
692:
693: invalid_line_type EXCEPTION;
694: BEGIN
695: --Initially let us assign the return status to success
696: x_return_status := fnd_api.g_ret_sts_success;
697:
698: IF (g_debug <= gme_debug.g_log_statement) THEN
699: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
700: || 'Entering');
739: -- 8605909 updated the delete material transaction with the trans date parameter
740: ,p_trans_date => l_mmti_rec.transaction_date
741: ,x_return_status => l_return_status);
742:
743: IF l_return_status <> fnd_api.g_ret_sts_success THEN
744: IF (g_debug <= gme_debug.g_log_statement) THEN
745: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
746: END IF;
747: RAISE delete_material_txn_err;
768: create_material_txn (p_mmti_rec => l_mmti_rec
769: ,p_mmli_tbl => l_mmli_tbl
770: ,x_return_status => l_return_status);
771:
772: IF l_return_status <> fnd_api.g_ret_sts_success THEN
773: RAISE create_material_txn_err;
774: END IF;
775:
776: -- Bug 9301755 - Call delete for non ingredient lines.
791: -- 8605909 updated the delete material transaction with the trans date parameter
792: ,p_trans_date => l_mmti_rec.transaction_date
793: ,x_return_status => l_return_status);
794:
795: IF l_return_status <> fnd_api.g_ret_sts_success THEN
796: IF (g_debug <= gme_debug.g_log_statement) THEN
797: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
798: END IF;
799: RAISE delete_material_txn_err;
810: END IF;
811: EXCEPTION
812: WHEN invalid_line_type THEN
813: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error fetching line_type.');
814: x_return_status := fnd_api.g_ret_sts_unexp_error ;
815:
816: WHEN delete_material_txn_err THEN
817: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
818: x_return_status := l_return_status ;
819:
820: WHEN create_material_txn_err THEN
821: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create material txn');
822: x_return_status := l_return_status ;
823: WHEN fnd_api.g_exc_error THEN
824: x_return_status := fnd_api.g_ret_sts_error;
825: WHEN fnd_api.g_exc_unexpected_error THEN
826: x_return_status := fnd_api.g_ret_sts_unexp_error;
827: WHEN OTHERS THEN
820: WHEN create_material_txn_err THEN
821: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create material txn');
822: x_return_status := l_return_status ;
823: WHEN fnd_api.g_exc_error THEN
824: x_return_status := fnd_api.g_ret_sts_error;
825: WHEN fnd_api.g_exc_unexpected_error THEN
826: x_return_status := fnd_api.g_ret_sts_unexp_error;
827: WHEN OTHERS THEN
828: x_return_status := fnd_api.g_ret_sts_unexp_error;
821: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create material txn');
822: x_return_status := l_return_status ;
823: WHEN fnd_api.g_exc_error THEN
824: x_return_status := fnd_api.g_ret_sts_error;
825: WHEN fnd_api.g_exc_unexpected_error THEN
826: x_return_status := fnd_api.g_ret_sts_unexp_error;
827: WHEN OTHERS THEN
828: x_return_status := fnd_api.g_ret_sts_unexp_error;
829: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
822: x_return_status := l_return_status ;
823: WHEN fnd_api.g_exc_error THEN
824: x_return_status := fnd_api.g_ret_sts_error;
825: WHEN fnd_api.g_exc_unexpected_error THEN
826: x_return_status := fnd_api.g_ret_sts_unexp_error;
827: WHEN OTHERS THEN
828: x_return_status := fnd_api.g_ret_sts_unexp_error;
829: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
830:
824: x_return_status := fnd_api.g_ret_sts_error;
825: WHEN fnd_api.g_exc_unexpected_error THEN
826: x_return_status := fnd_api.g_ret_sts_unexp_error;
827: WHEN OTHERS THEN
828: x_return_status := fnd_api.g_ret_sts_unexp_error;
829: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
830:
831: IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
832: gme_debug.put_line ( g_pkg_name
876: l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
877: l_mmti_rec mtl_transactions_interface%ROWTYPE;
878: l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
879: l_mat_dtl_rec gme_material_details%ROWTYPE;
880: l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
881: create_material_txn_err EXCEPTION;
882: delete_material_txn_err EXCEPTION;
883:
884: -- Bug 8639523 - Introduce cursor to get line_type and variables.
898:
899: invalid_line_type EXCEPTION;
900: BEGIN
901: -- Initially let us assign the return status to success
902: x_return_status := fnd_api.g_ret_sts_success;
903:
904: IF (g_debug <= gme_debug.g_log_statement) THEN
905: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
906: || 'Entering');
926: ,x_mmti_rec => l_mmti_rec
927: ,x_mmli_tbl => l_mmli_tbl
928: ,x_return_status => l_return_status);
929:
930: IF l_return_status <> fnd_api.g_ret_sts_success THEN
931: IF (g_debug <= gme_debug.g_log_statement) THEN
932: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from construct mmti');
933: END IF;
934: RAISE fnd_api.g_exc_error;
930: IF l_return_status <> fnd_api.g_ret_sts_success THEN
931: IF (g_debug <= gme_debug.g_log_statement) THEN
932: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from construct mmti');
933: END IF;
934: RAISE fnd_api.g_exc_error;
935: END IF;
936:
937: -- Bug 8639523 - Fetch the line_type so we can decide whether to call delete first or create.
938: OPEN Cur_get_material_line_type (l_transaction_id);
969: --8605909 updated the delete material transaction with the trans date parameter
970: ,p_trans_date => l_mmt_rec.transaction_date
971: ,x_return_status => l_return_status);
972:
973: IF l_return_status <> fnd_api.g_ret_sts_success THEN
974: IF (g_debug <= gme_debug.g_log_statement) THEN
975: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
976: END IF;
977: RAISE delete_material_txn_err;
1004: create_material_txn (p_mmti_rec => l_mmti_rec
1005: ,p_mmli_tbl => l_mmli_tbl
1006: ,x_return_status => l_return_status);
1007:
1008: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1009: IF (g_debug <= gme_debug.g_log_statement) THEN
1010: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create material txn');
1011: END IF;
1012: RAISE create_material_txn_err;
1031: --8605909 updated the delete material transaction with the trans date parameter
1032: ,p_trans_date => l_mmt_rec.transaction_date
1033: ,x_return_status => l_return_status);
1034:
1035: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1036: IF (g_debug <= gme_debug.g_log_statement) THEN
1037: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
1038: END IF;
1039: RAISE delete_material_txn_err;
1051:
1052: EXCEPTION
1053: WHEN invalid_line_type THEN
1054: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error fetching line_type.');
1055: x_return_status := fnd_api.g_ret_sts_unexp_error ;
1056: WHEN delete_material_txn_err THEN
1057: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
1058: x_return_status := l_return_status ;
1059: WHEN create_material_txn_err THEN
1058: x_return_status := l_return_status ;
1059: WHEN create_material_txn_err THEN
1060: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create material txn');
1061: x_return_status := l_return_status ;
1062: WHEN fnd_api.g_exc_error THEN
1063: x_return_status := fnd_api.g_ret_sts_error;
1064: WHEN fnd_api.g_exc_unexpected_error THEN
1065: x_return_status := fnd_api.g_ret_sts_unexp_error;
1066: WHEN OTHERS THEN
1059: WHEN create_material_txn_err THEN
1060: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create material txn');
1061: x_return_status := l_return_status ;
1062: WHEN fnd_api.g_exc_error THEN
1063: x_return_status := fnd_api.g_ret_sts_error;
1064: WHEN fnd_api.g_exc_unexpected_error THEN
1065: x_return_status := fnd_api.g_ret_sts_unexp_error;
1066: WHEN OTHERS THEN
1067: x_return_status := fnd_api.g_ret_sts_unexp_error;
1060: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create material txn');
1061: x_return_status := l_return_status ;
1062: WHEN fnd_api.g_exc_error THEN
1063: x_return_status := fnd_api.g_ret_sts_error;
1064: WHEN fnd_api.g_exc_unexpected_error THEN
1065: x_return_status := fnd_api.g_ret_sts_unexp_error;
1066: WHEN OTHERS THEN
1067: x_return_status := fnd_api.g_ret_sts_unexp_error;
1068: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1061: x_return_status := l_return_status ;
1062: WHEN fnd_api.g_exc_error THEN
1063: x_return_status := fnd_api.g_ret_sts_error;
1064: WHEN fnd_api.g_exc_unexpected_error THEN
1065: x_return_status := fnd_api.g_ret_sts_unexp_error;
1066: WHEN OTHERS THEN
1067: x_return_status := fnd_api.g_ret_sts_unexp_error;
1068: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1069:
1063: x_return_status := fnd_api.g_ret_sts_error;
1064: WHEN fnd_api.g_exc_unexpected_error THEN
1065: x_return_status := fnd_api.g_ret_sts_unexp_error;
1066: WHEN OTHERS THEN
1067: x_return_status := fnd_api.g_ret_sts_unexp_error;
1068: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1069:
1070: IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
1071: gme_debug.put_line ( g_pkg_name
1118: l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
1119: l_mmti_rec mtl_transactions_interface%ROWTYPE;
1120: l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
1121: l_mat_dtl_rec gme_material_details%ROWTYPE;
1122: l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
1123: create_material_txn_err EXCEPTION;
1124: delete_material_txn_err EXCEPTION;
1125: get_trans_err EXCEPTION;
1126: l_trans_date DATE;
1125: get_trans_err EXCEPTION;
1126: l_trans_date DATE;
1127: BEGIN
1128: -- Initially let us assign the return status to success
1129: x_return_status := fnd_api.g_ret_sts_success;
1130:
1131: IF (g_debug <= gme_debug.g_log_statement) THEN
1132: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1133: || 'Entering');
1151: ,x_mmt_rec => l_mmt_rec
1152: ,x_mmln_tbl => l_mmln_tbl
1153: ,x_return_status => l_return_status);
1154:
1155: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1156: IF (g_debug <= gme_debug.g_log_statement) THEN
1157: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from get transactions');
1158: END IF;
1159: RAISE get_trans_err;
1221: ,x_mmti_rec => l_mmti_rec
1222: ,x_mmli_tbl => l_mmli_tbl
1223: ,x_return_status => l_return_status);
1224:
1225: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1226: IF (g_debug <= gme_debug.g_log_statement) THEN
1227: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from construct mmti');
1228: END IF;
1229: RAISE fnd_api.g_exc_error;
1225: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1226: IF (g_debug <= gme_debug.g_log_statement) THEN
1227: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from construct mmti');
1228: END IF;
1229: RAISE fnd_api.g_exc_error;
1230: END IF;
1231:
1232: IF (g_debug <= gme_debug.g_log_statement) THEN
1233: gme_debug.put_line ( g_pkg_name
1253:
1254: IF NOT gme_material_details_dbl.fetch_row
1255: (p_material_detail => l_mat_dtl_rec
1256: ,x_material_detail => l_mat_dtl_rec) THEN
1257: RAISE fnd_api.g_exc_error;
1258: END IF;
1259:
1260: -- Bug 6437252 LPN Support
1261: IF l_mmti_rec.transaction_type_id = gme_common_pvt.g_ing_issue THEN
1365: delete_material_txn (p_transaction_id => m_transaction_id
1366: ,p_txns_pair => 1
1367: ,p_trans_date => l_trans_date
1368: ,x_return_status => l_return_status);
1369: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1370: IF (g_debug <= gme_debug.g_log_statement) THEN
1371: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create mat txn');
1372: END IF;
1373: RAISE delete_material_txn_err;
1432: ,p_mmli_tbl => l_mmli_tbl
1433: ,p_phantom_trans => 1
1434: ,x_return_status => l_return_status);
1435:
1436: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1437: IF (g_debug <= gme_debug.g_log_statement) THEN
1438: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create mat txn');
1439: END IF;
1440: RAISE create_material_txn_err;
1485: delete_material_txn (p_transaction_id => m_transaction_id
1486: ,p_txns_pair => 1
1487: ,p_trans_date => l_trans_date
1488: ,x_return_status => l_return_status);
1489: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1490: IF (g_debug <= gme_debug.g_log_statement) THEN
1491: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create mat txn');
1492: END IF;
1493: RAISE delete_material_txn_err;
1521: x_return_status := l_return_status ;
1522: WHEN create_material_txn_err THEN
1523: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create material txn');
1524: x_return_status := l_return_status ;
1525: WHEN fnd_api.g_exc_error THEN
1526: x_return_status := fnd_api.g_ret_sts_error;
1527: WHEN fnd_api.g_exc_unexpected_error THEN
1528: IF (g_debug <= gme_debug.g_log_statement) THEN
1529: gme_debug.put_line ( g_pkg_name
1522: WHEN create_material_txn_err THEN
1523: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create material txn');
1524: x_return_status := l_return_status ;
1525: WHEN fnd_api.g_exc_error THEN
1526: x_return_status := fnd_api.g_ret_sts_error;
1527: WHEN fnd_api.g_exc_unexpected_error THEN
1528: IF (g_debug <= gme_debug.g_log_statement) THEN
1529: gme_debug.put_line ( g_pkg_name
1530: || '.'
1523: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create material txn');
1524: x_return_status := l_return_status ;
1525: WHEN fnd_api.g_exc_error THEN
1526: x_return_status := fnd_api.g_ret_sts_error;
1527: WHEN fnd_api.g_exc_unexpected_error THEN
1528: IF (g_debug <= gme_debug.g_log_statement) THEN
1529: gme_debug.put_line ( g_pkg_name
1530: || '.'
1531: || l_api_name
1532: || ':'
1533: || 'Unexpected');
1534: END IF;
1535:
1536: x_return_status := fnd_api.g_ret_sts_unexp_error;
1537: WHEN OTHERS THEN
1538: x_return_status := fnd_api.g_ret_sts_unexp_error;
1539: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1540:
1534: END IF;
1535:
1536: x_return_status := fnd_api.g_ret_sts_unexp_error;
1537: WHEN OTHERS THEN
1538: x_return_status := fnd_api.g_ret_sts_unexp_error;
1539: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1540:
1541: IF (g_debug <= gme_debug.g_log_statement) THEN
1542: gme_debug.put_line ( g_pkg_name
1586: l_mmti_rec mtl_transactions_interface%ROWTYPE;
1587: l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
1588: l_api_name CONSTANT VARCHAR2 (30) := 'BUILD_TXN_INTER';
1589: x_header_id NUMBER;
1590: l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
1591: l_insert_hdr BOOLEAN;
1592: build_txn_inter_err EXCEPTION;
1593: build_txn_inter_lot_err EXCEPTION;
1594: lot_expired_err EXCEPTION;
1616: um_convert_error EXCEPTION;
1617:
1618: BEGIN
1619: -- Initially let us assign the return status to success
1620: x_return_status := fnd_api.g_ret_sts_success;
1621:
1622: IF (g_debug <= gme_debug.g_log_statement) THEN
1623: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1624: || 'Entering');
1637: ,x_mmti_rec => x_mmti_rec
1638: ,x_return_status => l_return_status
1639: ,p_insert_hdr => l_insert_hdr);
1640:
1641: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1642: IF (g_debug <= gme_debug.g_log_statement) THEN
1643: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from build inter hdr');
1644: END IF;
1645: RAISE build_txn_inter_err;
1775: ,p_mmli_rec => l_mmli_tbl (i)
1776: ,x_mmli_rec => x_mmli_tbl (i)
1777: ,x_return_status => l_return_status);
1778:
1779: IF l_return_status <> fnd_api.g_ret_sts_success THEN
1780: IF (g_debug <= gme_debug.g_log_statement) THEN
1781: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from build lot inter');
1782: END IF;
1783: RAISE build_txn_inter_lot_err;
1797: /* Bug 4929610 Added code to insert if not inserted originally */
1798: IF NOT(l_insert_hdr) THEN
1799: insert_txn_inter_hdr(p_mmti_rec => x_mmti_rec,
1800: x_return_status => l_return_status);
1801: IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1802: RAISE insert_hdr_err;
1803: END IF;
1804: END IF;
1805:
1820: fnd_message.set_token ('ITEM_NO', l_item_no);
1821: fnd_message.set_token ('FROM_UOM',x_mmti_rec.transaction_uom);
1822: fnd_message.set_token ('TO_UOM', l_primary_uom);
1823: fnd_msg_pub.ADD;
1824: x_return_status := FND_API.g_ret_sts_error;
1825: WHEN lot_expired_err THEN
1826: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'lot_expired_err');
1827: x_return_status := 'T';
1828: WHEN build_txn_inter_lot_err THEN
1830: x_return_status := l_return_status;
1831: WHEN build_txn_inter_err THEN
1832: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'build_txn_inter_err');
1833: x_return_status := l_return_status;
1834: WHEN fnd_api.g_exc_error THEN
1835: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'user defined error');
1836: x_return_status := fnd_api.g_ret_sts_error;
1837: WHEN fnd_api.g_exc_unexpected_error THEN
1838: gme_debug.put_line ( g_pkg_name
1832: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'build_txn_inter_err');
1833: x_return_status := l_return_status;
1834: WHEN fnd_api.g_exc_error THEN
1835: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'user defined error');
1836: x_return_status := fnd_api.g_ret_sts_error;
1837: WHEN fnd_api.g_exc_unexpected_error THEN
1838: gme_debug.put_line ( g_pkg_name
1839: || '.'
1840: || l_api_name
1833: x_return_status := l_return_status;
1834: WHEN fnd_api.g_exc_error THEN
1835: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'user defined error');
1836: x_return_status := fnd_api.g_ret_sts_error;
1837: WHEN fnd_api.g_exc_unexpected_error THEN
1838: gme_debug.put_line ( g_pkg_name
1839: || '.'
1840: || l_api_name
1841: || ':'
1840: || l_api_name
1841: || ':'
1842: || 'unexp'
1843: || SQLERRM);
1844: x_return_status := fnd_api.g_ret_sts_unexp_error;
1845: WHEN OTHERS THEN
1846: x_return_status := fnd_api.g_ret_sts_unexp_error;
1847: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1848:
1842: || 'unexp'
1843: || SQLERRM);
1844: x_return_status := fnd_api.g_ret_sts_unexp_error;
1845: WHEN OTHERS THEN
1846: x_return_status := fnd_api.g_ret_sts_unexp_error;
1847: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1848:
1849: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1850: gme_debug.put_line ( g_pkg_name
1892: AND inventory_location_id (+) = v_loc_id;
1893: l_mmti_rec mtl_transactions_interface%ROWTYPE;
1894: l_api_name CONSTANT VARCHAR2 (30) := 'BUILD_TXN_INTER_hdr';
1895: x_header_id NUMBER;
1896: l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
1897: l_item VARCHAR2(100);
1898: l_type VARCHAR2(100);
1899: l_locator VARCHAR2(100);
1900: material_status_err EXCEPTION ;
1900: material_status_err EXCEPTION ;
1901: insert_hdr_err EXCEPTION ;
1902: BEGIN
1903: -- Initially let us assign the return status to success
1904: x_return_status := fnd_api.g_ret_sts_success;
1905:
1906: IF (g_debug <= gme_debug.g_log_statement) THEN
1907: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1908: || 'Entering');
2193: /* Bug 4929610 fixed */
2194: IF (p_insert_hdr) THEN
2195: insert_txn_inter_hdr(p_mmti_rec => l_mmti_rec,
2196: x_return_status => l_return_status);
2197: IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2198: RAISE insert_hdr_err;
2199: END IF;
2200: END IF;
2201: x_mmti_rec := l_mmti_rec;
2240: || ':'
2241: || 'material status invalid for item, subinventory, locator etc'
2242: );
2243: x_return_status := 'T';
2244: WHEN fnd_api.g_exc_unexpected_error THEN
2245: gme_debug.put_line ( g_pkg_name
2246: || '.'
2247: || l_api_name
2248: || ':'
2247: || l_api_name
2248: || ':'
2249: || 'unexp'
2250: || SQLERRM);
2251: x_return_status := fnd_api.g_ret_sts_unexp_error;
2252: WHEN OTHERS THEN
2253: x_return_status := fnd_api.g_ret_sts_unexp_error;
2254: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2255:
2249: || 'unexp'
2250: || SQLERRM);
2251: x_return_status := fnd_api.g_ret_sts_unexp_error;
2252: WHEN OTHERS THEN
2253: x_return_status := fnd_api.g_ret_sts_unexp_error;
2254: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2255:
2256: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2257: gme_debug.put_line ( g_pkg_name
2303: ,x_mmli_rec OUT NOCOPY mtl_transaction_lots_interface%ROWTYPE
2304: ,x_return_status OUT NOCOPY VARCHAR2)
2305: IS
2306: l_api_name CONSTANT VARCHAR2 (30) := 'BUILD_TXN_INTER_LOT';
2307: l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
2308: l_mmli_rec mtl_transaction_lots_interface%ROWTYPE;
2309: l_transaction_type_id NUMBER;
2310: l_inventory_item_id NUMBER;
2311: l_item VARCHAR2(100);
2312: l_type VARCHAR2(100);
2313:
2314: material_status_err EXCEPTION;
2315: BEGIN
2316: x_return_status := fnd_api.g_ret_sts_success;
2317:
2318: IF (g_debug <= gme_debug.g_log_statement) THEN
2319: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
2320: || 'Entering');
2554: || l_api_name
2555: || ':'
2556: || 'material status invalid for item, subinventory, locator etc');
2557: x_return_status := 'T';
2558: WHEN fnd_api.g_exc_error THEN
2559: gme_debug.put_line ( g_pkg_name
2560: || '.'
2561: || l_api_name
2562: || ':'
2561: || l_api_name
2562: || ':'
2563: || 'WHEN exe'
2564: || SQLERRM);
2565: x_return_status := fnd_api.g_ret_sts_error;
2566: WHEN fnd_api.g_exc_unexpected_error THEN
2567: gme_debug.put_line ( g_pkg_name
2568: || '.'
2569: || l_api_name
2562: || ':'
2563: || 'WHEN exe'
2564: || SQLERRM);
2565: x_return_status := fnd_api.g_ret_sts_error;
2566: WHEN fnd_api.g_exc_unexpected_error THEN
2567: gme_debug.put_line ( g_pkg_name
2568: || '.'
2569: || l_api_name
2570: || ':'
2569: || l_api_name
2570: || ':'
2571: || 'unexp'
2572: || SQLERRM);
2573: x_return_status := fnd_api.g_ret_sts_unexp_error;
2574: WHEN OTHERS THEN
2575: x_return_status := fnd_api.g_ret_sts_unexp_error;
2576: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2577:
2571: || 'unexp'
2572: || SQLERRM);
2573: x_return_status := fnd_api.g_ret_sts_unexp_error;
2574: WHEN OTHERS THEN
2575: x_return_status := fnd_api.g_ret_sts_unexp_error;
2576: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2577:
2578: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2579: gme_debug.put_line ( g_pkg_name
2635: FROM mtl_transaction_lot_numbers
2636: WHERE transaction_id = v_transaction_id;
2637:
2638: l_api_name CONSTANT VARCHAR2 (30) := 'GET_TRANSACTIONS';
2639: l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
2640: l_transaction_id NUMBER;
2641: no_transaction_found EXCEPTION;
2642:
2643: BEGIN
2640: l_transaction_id NUMBER;
2641: no_transaction_found EXCEPTION;
2642:
2643: BEGIN
2644: x_return_status := fnd_api.g_ret_sts_success;
2645:
2646: IF (g_debug <= gme_debug.g_log_statement) THEN
2647: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
2648: || 'Entering with transaction '||p_transaction_id);
2677: get_lot_trans (p_transaction_id => l_transaction_id
2678: ,x_mmln_tbl => x_mmln_tbl
2679: ,x_return_status => l_return_status);
2680:
2681: IF l_return_status <> fnd_api.g_ret_sts_success THEN
2682: IF (g_debug <= gme_debug.g_log_statement) THEN
2683: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
2684: || 'error from get lot trans');
2685: END IF;
2682: IF (g_debug <= gme_debug.g_log_statement) THEN
2683: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
2684: || 'error from get lot trans');
2685: END IF;
2686: RAISE fnd_api.g_exc_error;
2687: END IF;
2688: ELSE /* IF cur_get_transaction%FOUND THEN */
2689: CLOSE cur_get_transaction;
2690: gme_common_pvt.log_message ('GME_NO_TRANS_FOUND');
2687: END IF;
2688: ELSE /* IF cur_get_transaction%FOUND THEN */
2689: CLOSE cur_get_transaction;
2690: gme_common_pvt.log_message ('GME_NO_TRANS_FOUND');
2691: RAISE fnd_api.g_exc_error;
2692: END IF;
2693: CLOSE cur_get_transaction;
2694:
2695: IF (g_debug <= gme_debug.g_log_statement) THEN
2709: || 'Exiting with '
2710: || x_return_status);
2711: END IF;
2712: EXCEPTION
2713: WHEN fnd_api.g_exc_error THEN
2714: x_return_status := fnd_api.g_ret_sts_error;
2715: WHEN fnd_api.g_exc_unexpected_error THEN
2716: x_return_status := fnd_api.g_ret_sts_unexp_error;
2717: WHEN OTHERS THEN
2710: || x_return_status);
2711: END IF;
2712: EXCEPTION
2713: WHEN fnd_api.g_exc_error THEN
2714: x_return_status := fnd_api.g_ret_sts_error;
2715: WHEN fnd_api.g_exc_unexpected_error THEN
2716: x_return_status := fnd_api.g_ret_sts_unexp_error;
2717: WHEN OTHERS THEN
2718: x_return_status := fnd_api.g_ret_sts_unexp_error;
2711: END IF;
2712: EXCEPTION
2713: WHEN fnd_api.g_exc_error THEN
2714: x_return_status := fnd_api.g_ret_sts_error;
2715: WHEN fnd_api.g_exc_unexpected_error THEN
2716: x_return_status := fnd_api.g_ret_sts_unexp_error;
2717: WHEN OTHERS THEN
2718: x_return_status := fnd_api.g_ret_sts_unexp_error;
2719: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2712: EXCEPTION
2713: WHEN fnd_api.g_exc_error THEN
2714: x_return_status := fnd_api.g_ret_sts_error;
2715: WHEN fnd_api.g_exc_unexpected_error THEN
2716: x_return_status := fnd_api.g_ret_sts_unexp_error;
2717: WHEN OTHERS THEN
2718: x_return_status := fnd_api.g_ret_sts_unexp_error;
2719: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2720:
2714: x_return_status := fnd_api.g_ret_sts_error;
2715: WHEN fnd_api.g_exc_unexpected_error THEN
2716: x_return_status := fnd_api.g_ret_sts_unexp_error;
2717: WHEN OTHERS THEN
2718: x_return_status := fnd_api.g_ret_sts_unexp_error;
2719: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2720:
2721: IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2722: gme_debug.put_line ( g_pkg_name
2760: l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
2761: l_api_name CONSTANT VARCHAR2 (30) := 'CONSTRUCT_MMTI';
2762: BEGIN
2763: -- Initially let us assign the return status to success
2764: x_return_status := fnd_api.g_ret_sts_success;
2765:
2766: IF (g_debug <= gme_debug.g_log_statement) THEN
2767: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
2768: || 'Entering');
2831: || 'Exiting with '
2832: || x_return_status);
2833: END IF;
2834: EXCEPTION
2835: WHEN fnd_api.g_exc_error THEN
2836: x_return_status := fnd_api.g_ret_sts_error;
2837: WHEN fnd_api.g_exc_unexpected_error THEN
2838: x_return_status := fnd_api.g_ret_sts_unexp_error;
2839: WHEN OTHERS THEN
2832: || x_return_status);
2833: END IF;
2834: EXCEPTION
2835: WHEN fnd_api.g_exc_error THEN
2836: x_return_status := fnd_api.g_ret_sts_error;
2837: WHEN fnd_api.g_exc_unexpected_error THEN
2838: x_return_status := fnd_api.g_ret_sts_unexp_error;
2839: WHEN OTHERS THEN
2840: x_return_status := fnd_api.g_ret_sts_unexp_error;
2833: END IF;
2834: EXCEPTION
2835: WHEN fnd_api.g_exc_error THEN
2836: x_return_status := fnd_api.g_ret_sts_error;
2837: WHEN fnd_api.g_exc_unexpected_error THEN
2838: x_return_status := fnd_api.g_ret_sts_unexp_error;
2839: WHEN OTHERS THEN
2840: x_return_status := fnd_api.g_ret_sts_unexp_error;
2841: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2834: EXCEPTION
2835: WHEN fnd_api.g_exc_error THEN
2836: x_return_status := fnd_api.g_ret_sts_error;
2837: WHEN fnd_api.g_exc_unexpected_error THEN
2838: x_return_status := fnd_api.g_ret_sts_unexp_error;
2839: WHEN OTHERS THEN
2840: x_return_status := fnd_api.g_ret_sts_unexp_error;
2841: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2842:
2836: x_return_status := fnd_api.g_ret_sts_error;
2837: WHEN fnd_api.g_exc_unexpected_error THEN
2838: x_return_status := fnd_api.g_ret_sts_unexp_error;
2839: WHEN OTHERS THEN
2840: x_return_status := fnd_api.g_ret_sts_unexp_error;
2841: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2842:
2843: IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2844: gme_debug.put_line ( g_pkg_name
2944: l_api_name CONSTANT VARCHAR2 (30) := 'GET_MAT_TRANS';
2945: p_phantom_batch_id NUMBER;
2946: BEGIN
2947: -- Initially let us assign the return status to success
2948: x_return_status := fnd_api.g_ret_sts_success;
2949:
2950: IF (g_debug <= gme_debug.g_log_statement) THEN
2951: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
2952: || 'Entering with mat/batch '||p_mat_det_id||'/'||p_batch_id);
2980: || 'Exiting with '
2981: || x_return_status);
2982: END IF;
2983: EXCEPTION
2984: WHEN fnd_api.g_exc_error THEN
2985: x_return_status := fnd_api.g_ret_sts_error;
2986: WHEN fnd_api.g_exc_unexpected_error THEN
2987: x_return_status := fnd_api.g_ret_sts_unexp_error;
2988: WHEN OTHERS THEN
2981: || x_return_status);
2982: END IF;
2983: EXCEPTION
2984: WHEN fnd_api.g_exc_error THEN
2985: x_return_status := fnd_api.g_ret_sts_error;
2986: WHEN fnd_api.g_exc_unexpected_error THEN
2987: x_return_status := fnd_api.g_ret_sts_unexp_error;
2988: WHEN OTHERS THEN
2989: x_return_status := fnd_api.g_ret_sts_unexp_error;
2982: END IF;
2983: EXCEPTION
2984: WHEN fnd_api.g_exc_error THEN
2985: x_return_status := fnd_api.g_ret_sts_error;
2986: WHEN fnd_api.g_exc_unexpected_error THEN
2987: x_return_status := fnd_api.g_ret_sts_unexp_error;
2988: WHEN OTHERS THEN
2989: x_return_status := fnd_api.g_ret_sts_unexp_error;
2990: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2983: EXCEPTION
2984: WHEN fnd_api.g_exc_error THEN
2985: x_return_status := fnd_api.g_ret_sts_error;
2986: WHEN fnd_api.g_exc_unexpected_error THEN
2987: x_return_status := fnd_api.g_ret_sts_unexp_error;
2988: WHEN OTHERS THEN
2989: x_return_status := fnd_api.g_ret_sts_unexp_error;
2990: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2991:
2985: x_return_status := fnd_api.g_ret_sts_error;
2986: WHEN fnd_api.g_exc_unexpected_error THEN
2987: x_return_status := fnd_api.g_ret_sts_unexp_error;
2988: WHEN OTHERS THEN
2989: x_return_status := fnd_api.g_ret_sts_unexp_error;
2990: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2991:
2992: IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2993: gme_debug.put_line ( g_pkg_name
3030:
3031: l_api_name CONSTANT VARCHAR2 (30) := 'GET_LOT_TRANS';
3032: BEGIN
3033: -- Initially let us assign the return status to success
3034: x_return_status := fnd_api.g_ret_sts_success;
3035:
3036: IF (g_debug <= gme_debug.g_log_statement) THEN
3037: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
3038: || 'Entering with transaction id '||p_transaction_id);
3060: || 'Exiting with '
3061: || x_return_status);
3062: END IF;
3063: EXCEPTION
3064: WHEN fnd_api.g_exc_error THEN
3065: x_return_status := fnd_api.g_ret_sts_error;
3066: WHEN fnd_api.g_exc_unexpected_error THEN
3067: x_return_status := fnd_api.g_ret_sts_unexp_error;
3068: WHEN OTHERS THEN
3061: || x_return_status);
3062: END IF;
3063: EXCEPTION
3064: WHEN fnd_api.g_exc_error THEN
3065: x_return_status := fnd_api.g_ret_sts_error;
3066: WHEN fnd_api.g_exc_unexpected_error THEN
3067: x_return_status := fnd_api.g_ret_sts_unexp_error;
3068: WHEN OTHERS THEN
3069: x_return_status := fnd_api.g_ret_sts_unexp_error;
3062: END IF;
3063: EXCEPTION
3064: WHEN fnd_api.g_exc_error THEN
3065: x_return_status := fnd_api.g_ret_sts_error;
3066: WHEN fnd_api.g_exc_unexpected_error THEN
3067: x_return_status := fnd_api.g_ret_sts_unexp_error;
3068: WHEN OTHERS THEN
3069: x_return_status := fnd_api.g_ret_sts_unexp_error;
3070:
3063: EXCEPTION
3064: WHEN fnd_api.g_exc_error THEN
3065: x_return_status := fnd_api.g_ret_sts_error;
3066: WHEN fnd_api.g_exc_unexpected_error THEN
3067: x_return_status := fnd_api.g_ret_sts_unexp_error;
3068: WHEN OTHERS THEN
3069: x_return_status := fnd_api.g_ret_sts_unexp_error;
3070:
3071: IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
3065: x_return_status := fnd_api.g_ret_sts_error;
3066: WHEN fnd_api.g_exc_unexpected_error THEN
3067: x_return_status := fnd_api.g_ret_sts_unexp_error;
3068: WHEN OTHERS THEN
3069: x_return_status := fnd_api.g_ret_sts_unexp_error;
3070:
3071: IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
3072: gme_debug.put_line ( g_pkg_name
3073: || '.'
3109: WHERE transaction_header_id = v_hdr_id
3110: AND transaction_source_type_id = gme_common_pvt.g_txn_source_type
3111: AND wip_entity_type = gme_common_pvt.g_wip_entity_type_batch;
3112:
3113: l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
3114: l_number_tab gme_common_pvt.number_tab;
3115: no_transaction_found EXCEPTION;
3116: l_api_name CONSTANT VARCHAR2 (30) := 'GME_PRE_PROCESS';
3117: BEGIN
3115: no_transaction_found EXCEPTION;
3116: l_api_name CONSTANT VARCHAR2 (30) := 'GME_PRE_PROCESS';
3117: BEGIN
3118: -- Initially let us assign the return status to success
3119: x_return_status := fnd_api.g_ret_sts_success;
3120:
3121: IF (g_debug <= gme_debug.g_log_statement) THEN
3122: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
3123: || 'Entering with p_transaction_hdr_id '||p_transaction_hdr_id);
3127: OPEN cur_get_trans (p_transaction_hdr_id);
3128: IF cur_get_trans%NOTFOUND THEN
3129: CLOSE cur_get_trans;
3130: gme_common_pvt.log_message ('GME_NO_TRANS_FOUND');
3131: RAISE fnd_api.g_exc_error;
3132: END IF;
3133: FETCH cur_get_trans
3134: BULK COLLECT INTO l_number_tab;
3135: CLOSE cur_get_trans;
3142: END IF;
3143: pre_process_val (p_transaction_interface_id => l_number_tab (i)
3144: ,x_return_status => l_return_status);
3145:
3146: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3147: RAISE fnd_api.g_exc_error;
3148: END IF;
3149: END LOOP;
3150:
3143: pre_process_val (p_transaction_interface_id => l_number_tab (i)
3144: ,x_return_status => l_return_status);
3145:
3146: IF l_return_status <> fnd_api.g_ret_sts_success THEN
3147: RAISE fnd_api.g_exc_error;
3148: END IF;
3149: END LOOP;
3150:
3151: IF (g_debug <= gme_debug.g_log_statement) THEN
3161: set error_code = 'wip_mtlInterfaceProc_pub.processInterface()',
3162: error_explanation = l_errMessage,
3163: process_flag = wip_constants.mti_error
3164: where transaction_header_id = p_txnHdrID; */
3165: WHEN fnd_api.g_exc_error THEN
3166: x_return_status := fnd_api.g_ret_sts_error;
3167: WHEN fnd_api.g_exc_unexpected_error THEN
3168: x_return_status := fnd_api.g_ret_sts_unexp_error;
3169: WHEN OTHERS THEN
3162: error_explanation = l_errMessage,
3163: process_flag = wip_constants.mti_error
3164: where transaction_header_id = p_txnHdrID; */
3165: WHEN fnd_api.g_exc_error THEN
3166: x_return_status := fnd_api.g_ret_sts_error;
3167: WHEN fnd_api.g_exc_unexpected_error THEN
3168: x_return_status := fnd_api.g_ret_sts_unexp_error;
3169: WHEN OTHERS THEN
3170: x_return_status := fnd_api.g_ret_sts_unexp_error;
3163: process_flag = wip_constants.mti_error
3164: where transaction_header_id = p_txnHdrID; */
3165: WHEN fnd_api.g_exc_error THEN
3166: x_return_status := fnd_api.g_ret_sts_error;
3167: WHEN fnd_api.g_exc_unexpected_error THEN
3168: x_return_status := fnd_api.g_ret_sts_unexp_error;
3169: WHEN OTHERS THEN
3170: x_return_status := fnd_api.g_ret_sts_unexp_error;
3171:
3164: where transaction_header_id = p_txnHdrID; */
3165: WHEN fnd_api.g_exc_error THEN
3166: x_return_status := fnd_api.g_ret_sts_error;
3167: WHEN fnd_api.g_exc_unexpected_error THEN
3168: x_return_status := fnd_api.g_ret_sts_unexp_error;
3169: WHEN OTHERS THEN
3170: x_return_status := fnd_api.g_ret_sts_unexp_error;
3171:
3172: IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
3166: x_return_status := fnd_api.g_ret_sts_error;
3167: WHEN fnd_api.g_exc_unexpected_error THEN
3168: x_return_status := fnd_api.g_ret_sts_unexp_error;
3169: WHEN OTHERS THEN
3170: x_return_status := fnd_api.g_ret_sts_unexp_error;
3171:
3172: IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
3173: gme_debug.put_line ( g_pkg_name
3174: || '.'
3279: l_api_name CONSTANT VARCHAR2 (30) := 'GET_RETURNABLE_QTY';
3280:
3281: BEGIN
3282: -- Initially let us assign the return status to success
3283: x_return_status := fnd_api.g_ret_sts_success;
3284:
3285: IF (g_debug <= gme_debug.g_log_statement) THEN
3286: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':' || 'Entering');
3287: END IF;
3341: || x_available_qty);
3342: END IF;
3343: EXCEPTION
3344: WHEN OTHERS THEN
3345: x_return_status := fnd_api.g_ret_sts_unexp_error;
3346: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3347:
3348: IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
3349: gme_debug.put_line ( g_pkg_name
3417: lot_val_err EXCEPTION;
3418: l_api_name CONSTANT VARCHAR2 (30) := 'PRE_PROCESS_VAL';
3419: BEGIN
3420: -- Initially let us assign the return status to success
3421: x_return_status := fnd_api.g_ret_sts_success;
3422:
3423: IF (g_debug <= gme_debug.g_log_statement) THEN
3424: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
3425: || 'Entering');
3450:
3451: IF NOT gme_batch_header_dbl.fetch_row
3452: (p_batch_header => l_batch_hdr_rec
3453: ,x_batch_header => l_batch_hdr_rec) THEN
3454: RAISE fnd_api.g_exc_error;
3455: END IF;-- batch fetch
3456: ELSE
3457: RAISE fnd_api.g_exc_error;
3458: END IF; -- transaction_source_id IS NOT NULL
3453: ,x_batch_header => l_batch_hdr_rec) THEN
3454: RAISE fnd_api.g_exc_error;
3455: END IF;-- batch fetch
3456: ELSE
3457: RAISE fnd_api.g_exc_error;
3458: END IF; -- transaction_source_id IS NOT NULL
3459:
3460: IF l_batch_hdr_rec.update_inventory_ind = 'Y' THEN
3461: IF l_mmti_rec.trx_source_line_id IS NOT NULL THEN
3463:
3464: IF NOT gme_material_details_dbl.fetch_row
3465: (p_material_detail => l_mat_dtl_rec
3466: ,x_material_detail => l_mat_dtl_rec) THEN
3467: RAISE fnd_api.g_exc_error;
3468: END IF; -- material fetch
3469:
3470: ELSE
3471: RAISE fnd_api.g_exc_error;
3467: RAISE fnd_api.g_exc_error;
3468: END IF; -- material fetch
3469:
3470: ELSE
3471: RAISE fnd_api.g_exc_error;
3472: END IF; -- trx_source_line_id IS NOT NULL
3473: IF gme_common_pvt.g_batch_status_check = fnd_api.g_true THEN
3474: IF l_batch_hdr_rec.batch_status NOT IN (2, 3) THEN
3475: gme_common_pvt.log_message ('GME_INVALID_BATCH_STATUS');
3469:
3470: ELSE
3471: RAISE fnd_api.g_exc_error;
3472: END IF; -- trx_source_line_id IS NOT NULL
3473: IF gme_common_pvt.g_batch_status_check = fnd_api.g_true THEN
3474: IF l_batch_hdr_rec.batch_status NOT IN (2, 3) THEN
3475: gme_common_pvt.log_message ('GME_INVALID_BATCH_STATUS');
3476: RAISE fnd_api.g_exc_error;
3477: END IF;
3472: END IF; -- trx_source_line_id IS NOT NULL
3473: IF gme_common_pvt.g_batch_status_check = fnd_api.g_true THEN
3474: IF l_batch_hdr_rec.batch_status NOT IN (2, 3) THEN
3475: gme_common_pvt.log_message ('GME_INVALID_BATCH_STATUS');
3476: RAISE fnd_api.g_exc_error;
3477: END IF;
3478: -- Check for step status in case the item is associated to a step.
3479: l_rel_type :=
3480: gme_common_pvt.is_material_auto_release
3484: FETCH Cur_associated_step INTO l_step_status;
3485: CLOSE Cur_associated_step;
3486: IF l_step_status NOT IN (2,3) THEN
3487: gme_common_pvt.log_message ('GME_API_INVALID_STEP_STATUS');
3488: RAISE fnd_api.g_exc_error;
3489: END IF;
3490: END IF; -- IF ( l_rel_type = gme_common_pvt.g_mtl_autobystep_release ) THEN
3491:
3492: -- check for item release type for products
3496: -- Bug 9626176 -- Allow transactions in wip batch for auto release prods.
3497: IF l_batch_hdr_rec.batch_status NOT IN (2, 3) THEN
3498: -- IF l_batch_hdr_rec.batch_status <> 3 THEN
3499: gme_common_pvt.log_message('GME_INVALID_BATCH_STATUS');
3500: RAISE fnd_api.g_exc_error;
3501: END IF;
3502: END IF;
3503: END IF; -- gme_common_pvt.g_batch_status_check
3504: IF l_mmti_rec.transaction_type_id IN
3505: (gme_common_pvt.g_ing_issue, gme_common_pvt.g_ing_return) THEN
3506: IF l_mat_dtl_rec.line_type <> -1 THEN
3507: /* Bug 5141394 Changed message */
3508: gme_common_pvt.log_message ('GME_LINE_TYPE_TXN_TYPE_DIFF');
3509: RAISE fnd_api.g_exc_error;
3510: END IF;
3511: ELSIF l_mmti_rec.transaction_type_id IN
3512: (gme_common_pvt.g_prod_completion
3513: ,gme_common_pvt.g_prod_return) THEN
3513: ,gme_common_pvt.g_prod_return) THEN
3514: IF l_mat_dtl_rec.line_type <> 1 THEN
3515: /* Bug 5141394 Changed message */
3516: gme_common_pvt.log_message ('GME_LINE_TYPE_TXN_TYPE_DIFF');
3517: RAISE fnd_api.g_exc_error;
3518: END IF;
3519: ELSIF l_mmti_rec.transaction_type_id IN
3520: (gme_common_pvt.g_byprod_completion
3521: ,gme_common_pvt.g_byprod_return) THEN
3522: IF l_mat_dtl_rec.line_type <> 2 THEN
3523: /* Bug 5141394 Changed message */
3524: --RLNAGARA Bug6873185 Moved below line which was setting message name inside the IF condition.
3525: gme_common_pvt.log_message ('GME_LINE_TYPE_TXN_TYPE_DIFF');
3526: RAISE fnd_api.g_exc_error;
3527: END IF;
3528: END IF;
3529:
3530: -- check for phantom
3531: IF l_mat_dtl_rec.phantom_line_id IS NOT NULL THEN
3532: IF l_mmti_rec.transaction_header_id <>
3533: gme_common_pvt.g_transaction_header_id THEN
3534: gme_common_pvt.log_message ('GME_PHANTOM_NO_RETURN');
3535: RAISE fnd_api.g_exc_error;
3536: END IF;
3537: END IF;
3538:
3539: -- get the item propertites
3561: gme_transactions_pvt.validate_lot_for_ing(p_organization_id => l_mmti_rec.organization_id,
3562: p_inventory_item_id => l_mmti_rec.inventory_item_id,
3563: p_lot_number => get_lots.lot_number,
3564: x_return_status => l_return_status);
3565: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3566: RAISE lot_val_err;
3567: END IF;
3568: END LOOP;
3569: END IF;
3593: ,p_lot_control => l_item_rec.lot_control_code
3594: ,x_available_qty => l_available_qty
3595: ,x_return_status => x_return_status);
3596:
3597: IF x_return_Status <> fnd_api.g_ret_sts_success THEN
3598: RAISE fnd_api.g_exc_unexpected_error;
3599: END IF;
3600: IF (g_debug <= gme_debug.g_log_statement) THEN
3601: gme_debug.put_line ( g_pkg_name
3594: ,x_available_qty => l_available_qty
3595: ,x_return_status => x_return_status);
3596:
3597: IF x_return_Status <> fnd_api.g_ret_sts_success THEN
3598: RAISE fnd_api.g_exc_unexpected_error;
3599: END IF;
3600: IF (g_debug <= gme_debug.g_log_statement) THEN
3601: gme_debug.put_line ( g_pkg_name
3602: || '.'
3613: END IF;
3614:
3615: IF ABS (l_available_qty) < ABS (l_mmti_rec.transaction_quantity) THEN
3616: gme_common_pvt.log_message ('GME_QTY_LESS_THEN_ISSUED');
3617: RAISE fnd_api.g_exc_error;
3618: END IF;
3619: ELSE /* Lot Control */
3620: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3621: gme_debug.put_line ( g_pkg_name
3634: ,p_lot_control => l_item_rec.lot_control_code
3635: ,x_available_qty => l_available_qty
3636: ,x_return_status => x_return_status);
3637:
3638: IF x_return_Status <> fnd_api.g_ret_sts_success THEN
3639: RAISE fnd_api.g_exc_unexpected_error;
3640: END IF;
3641: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3642: gme_debug.put_line ( g_pkg_name
3635: ,x_available_qty => l_available_qty
3636: ,x_return_status => x_return_status);
3637:
3638: IF x_return_Status <> fnd_api.g_ret_sts_success THEN
3639: RAISE fnd_api.g_exc_unexpected_error;
3640: END IF;
3641: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3642: gme_debug.put_line ( g_pkg_name
3643: || '.'
3653: END IF;
3654:
3655: IF ABS (l_available_qty) < ABS (get_rec.l_mtli_lot_qty) THEN
3656: gme_common_pvt.log_message ('GME_QTY_LESS_THEN_ISSUED');
3657: RAISE fnd_api.g_exc_error;
3658: END IF;
3659: END LOOP;
3660: END IF; /* IF l_item_rec.lot_control_code = 1 THEN */
3661: END IF; /* IF transaction_type_id in RETURNS */
3671: END IF;
3672: EXCEPTION
3673: WHEN lot_val_err THEN
3674: x_return_status := l_return_status;
3675: WHEN fnd_api.g_exc_error THEN
3676: x_return_status := fnd_api.g_ret_sts_error;
3677: gme_transactions_pvt.gme_txn_message
3678: (p_api_name => l_api_name
3679: ,p_transaction_interface_id => p_transaction_interface_id
3672: EXCEPTION
3673: WHEN lot_val_err THEN
3674: x_return_status := l_return_status;
3675: WHEN fnd_api.g_exc_error THEN
3676: x_return_status := fnd_api.g_ret_sts_error;
3677: gme_transactions_pvt.gme_txn_message
3678: (p_api_name => l_api_name
3679: ,p_transaction_interface_id => p_transaction_interface_id
3680: );
3677: gme_transactions_pvt.gme_txn_message
3678: (p_api_name => l_api_name
3679: ,p_transaction_interface_id => p_transaction_interface_id
3680: );
3681: WHEN fnd_api.g_exc_unexpected_error THEN
3682: x_return_status := fnd_api.g_ret_sts_unexp_error;
3683: WHEN OTHERS THEN
3684: x_return_status := fnd_api.g_ret_sts_unexp_error;
3685: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3678: (p_api_name => l_api_name
3679: ,p_transaction_interface_id => p_transaction_interface_id
3680: );
3681: WHEN fnd_api.g_exc_unexpected_error THEN
3682: x_return_status := fnd_api.g_ret_sts_unexp_error;
3683: WHEN OTHERS THEN
3684: x_return_status := fnd_api.g_ret_sts_unexp_error;
3685: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3686:
3680: );
3681: WHEN fnd_api.g_exc_unexpected_error THEN
3682: x_return_status := fnd_api.g_ret_sts_unexp_error;
3683: WHEN OTHERS THEN
3684: x_return_status := fnd_api.g_ret_sts_unexp_error;
3685: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3686:
3687: IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
3688: gme_debug.put_line ( g_pkg_name
3740: END IF;
3741:
3742: -- based on message call count and get
3743: gme_common_pvt.count_and_get (x_count => x_message_count
3744: ,p_encoded => fnd_api.g_false
3745: ,x_data => x_message_list);
3746:
3747: -- Bug 14685438 - limit error string to size of the field.
3748: UPDATE mtl_transactions_interface
3886:
3887:
3888: BEGIN
3889: -- Initially let us assign the return status to success
3890: x_return_status := fnd_api.g_ret_sts_success;
3891:
3892: IF (g_debug <= gme_debug.g_log_statement) THEN
3893: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
3894: || 'Entering with trans id: '||p_transaction_id);
4005: l_mat_dtl_rec.material_detail_id := l_trx_source_line_id ;
4006: IF NOT gme_material_details_dbl.fetch_row
4007: (p_material_detail => l_mat_dtl_rec
4008: ,x_material_detail => l_mat_dtl_rec) THEN
4009: RAISE fnd_api.g_exc_error;
4010: END IF;
4011: IF (g_debug <= gme_debug.g_log_statement) THEN
4012: gme_debug.put_line
4013: ( g_pkg_name
4169: p_dispense_id => l_dispense_id,
4170: p_status_code => 'CNSUMED',
4171: p_transaction_id => l_transaction_id
4172: ) ;
4173: IF l_return_status <> fnd_api.g_ret_sts_success THEN
4174: RAISE dispense_error;
4175: END IF;
4176: ELSE
4177: -- unconsume
4186: p_dispense_id => l_dispense_id,
4187: p_status_code => 'REVRDISP' ,
4188: p_transaction_id => l_transaction_id
4189: ) ;
4190: IF l_return_status <> fnd_api.g_ret_sts_success THEN
4191: RAISE dispense_error;
4192: END IF;
4193: END IF;
4194: END IF ; -- dispense_ind
4217:
4218: IF NOT gme_material_details_dbl.fetch_row
4219: (p_material_detail => l_mat_dtl_rec
4220: ,x_material_detail => l_mat_dtl_rec) THEN
4221: RAISE fnd_api.g_exc_error;
4222: END IF;
4223:
4224: IF (g_debug <= gme_debug.g_log_statement) THEN
4225: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||' mat_id '|| l_mat_dtl_rec.material_detail_id );
4250: l_mat_dtl_rec.actual_qty := ABS (l_actual_qty);
4251:
4252: IF NOT gme_material_details_dbl.update_row
4253: (p_material_detail => l_mat_dtl_rec) THEN
4254: RAISE fnd_api.g_exc_error;
4255: END IF;
4256:
4257: -- B4944024 BEGIN
4258: -- At yield, any reservations against PROD must transfer to a supply source of Inventory
4287: || l_api_name
4288: || ' transfer_reservation_to_inv returns '
4289: || x_return_status);
4290: END IF;
4291: IF x_return_status <> fnd_api.g_ret_sts_success THEN
4292: RAISE transfer_error; -- B4944024
4293: END IF;
4294: END IF;
4295: -- B4944024 END
4306: END IF; -- l_exists = 0
4307: EXCEPTION
4308: WHEN dispense_error THEN
4309:
4310: x_return_status := fnd_api.g_ret_sts_error;
4311: WHEN fnd_api.g_exc_error THEN
4312: x_return_status := fnd_api.g_ret_sts_error;
4313: WHEN fnd_api.g_exc_unexpected_error THEN
4314: x_return_status := fnd_api.g_ret_sts_unexp_error;
4307: EXCEPTION
4308: WHEN dispense_error THEN
4309:
4310: x_return_status := fnd_api.g_ret_sts_error;
4311: WHEN fnd_api.g_exc_error THEN
4312: x_return_status := fnd_api.g_ret_sts_error;
4313: WHEN fnd_api.g_exc_unexpected_error THEN
4314: x_return_status := fnd_api.g_ret_sts_unexp_error;
4315: WHEN OTHERS THEN
4308: WHEN dispense_error THEN
4309:
4310: x_return_status := fnd_api.g_ret_sts_error;
4311: WHEN fnd_api.g_exc_error THEN
4312: x_return_status := fnd_api.g_ret_sts_error;
4313: WHEN fnd_api.g_exc_unexpected_error THEN
4314: x_return_status := fnd_api.g_ret_sts_unexp_error;
4315: WHEN OTHERS THEN
4316: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4309:
4310: x_return_status := fnd_api.g_ret_sts_error;
4311: WHEN fnd_api.g_exc_error THEN
4312: x_return_status := fnd_api.g_ret_sts_error;
4313: WHEN fnd_api.g_exc_unexpected_error THEN
4314: x_return_status := fnd_api.g_ret_sts_unexp_error;
4315: WHEN OTHERS THEN
4316: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4317:
4310: x_return_status := fnd_api.g_ret_sts_error;
4311: WHEN fnd_api.g_exc_error THEN
4312: x_return_status := fnd_api.g_ret_sts_error;
4313: WHEN fnd_api.g_exc_unexpected_error THEN
4314: x_return_status := fnd_api.g_ret_sts_unexp_error;
4315: WHEN OTHERS THEN
4316: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4317:
4318: IF g_debug <= gme_debug.g_log_unexpected THEN
4353: l_material_detail_id NUMBER;
4354: l_api_name CONSTANT VARCHAR2 (30) := 'purge_trans_pairs';
4355: BEGIN
4356: -- Initially let us assign the return status to success
4357: x_return_status := fnd_api.g_ret_sts_success;
4358:
4359: IF (g_debug <= gme_debug.g_log_statement) THEN
4360: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
4361: || 'Entering');
4364: IF p_batch_id IS NULL AND p_material_detail_id IS NULL THEN
4365: gme_common_pvt.log_message ('GME_INVALID_FIELD'
4366: ,'FIELD'
4367: ,'p_batch_id');
4368: RAISE fnd_api.g_exc_error;
4369: END IF;
4370:
4371: l_batch_id := p_batch_id;
4372: l_material_detail_id := p_material_detail_id;
4406: || 'Exiting with '
4407: || x_return_status);
4408: END IF;
4409: EXCEPTION
4410: WHEN fnd_api.g_exc_error THEN
4411: x_return_status := fnd_api.g_ret_sts_error;
4412: WHEN fnd_api.g_exc_unexpected_error THEN
4413: x_return_status := fnd_api.g_ret_sts_unexp_error;
4414: WHEN OTHERS THEN
4407: || x_return_status);
4408: END IF;
4409: EXCEPTION
4410: WHEN fnd_api.g_exc_error THEN
4411: x_return_status := fnd_api.g_ret_sts_error;
4412: WHEN fnd_api.g_exc_unexpected_error THEN
4413: x_return_status := fnd_api.g_ret_sts_unexp_error;
4414: WHEN OTHERS THEN
4415: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4408: END IF;
4409: EXCEPTION
4410: WHEN fnd_api.g_exc_error THEN
4411: x_return_status := fnd_api.g_ret_sts_error;
4412: WHEN fnd_api.g_exc_unexpected_error THEN
4413: x_return_status := fnd_api.g_ret_sts_unexp_error;
4414: WHEN OTHERS THEN
4415: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4416:
4409: EXCEPTION
4410: WHEN fnd_api.g_exc_error THEN
4411: x_return_status := fnd_api.g_ret_sts_error;
4412: WHEN fnd_api.g_exc_unexpected_error THEN
4413: x_return_status := fnd_api.g_ret_sts_unexp_error;
4414: WHEN OTHERS THEN
4415: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4416:
4417: IF g_debug <= gme_debug.g_log_unexpected THEN
4474: +==========================================================================+ */
4475: /* Bug 5255959 added p_clear_qty_cache parameter */
4476: PROCEDURE process_transactions (
4477: p_api_version IN NUMBER := 1
4478: ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
4479: ,p_commit IN VARCHAR2 := fnd_api.g_false
4480: ,p_validation_level IN NUMBER
4481: := fnd_api.g_valid_level_full
4482: ,p_table IN NUMBER := 2
4475: /* Bug 5255959 added p_clear_qty_cache parameter */
4476: PROCEDURE process_transactions (
4477: p_api_version IN NUMBER := 1
4478: ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
4479: ,p_commit IN VARCHAR2 := fnd_api.g_false
4480: ,p_validation_level IN NUMBER
4481: := fnd_api.g_valid_level_full
4482: ,p_table IN NUMBER := 2
4483: ,p_header_id IN NUMBER
4477: p_api_version IN NUMBER := 1
4478: ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
4479: ,p_commit IN VARCHAR2 := fnd_api.g_false
4480: ,p_validation_level IN NUMBER
4481: := fnd_api.g_valid_level_full
4482: ,p_table IN NUMBER := 2
4483: ,p_header_id IN NUMBER
4484: := gme_common_pvt.get_txn_header_id
4485: ,x_return_status OUT NOCOPY VARCHAR2
4486: ,x_msg_count OUT NOCOPY NUMBER
4487: ,x_msg_data OUT NOCOPY VARCHAR2
4488: ,x_trans_count OUT NOCOPY NUMBER
4489: --Bug#5584699 Changed variable from boolean to varchar2
4490: ,p_clear_qty_cache IN VARCHAR2 := fnd_api.g_true)
4491: --,p_clear_qty_cache IN BOOLEAN DEFAULT TRUE)
4492: IS
4493:
4494: --bug 7720970 kbanddyo added join error_explanation IS NOT NULL for both the cursors below
4556: if p_table is MMTT then
4557: free the quantity tree */
4558: /* Bug 5255959 added p_clear_qty_cache condition */
4559: --Bug#5584699
4560: IF (p_table = 2 AND p_clear_qty_cache = fnd_api.g_true) THEN
4561: IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
4562: gme_debug.put_line
4563: ( g_pkg_name
4564: || '.'
4696: END LOOP;
4697: END IF; -- IF p_table
4698:
4699: IF (x_return_status IS NULL OR x_return_status = 'S') THEN
4700: RAISE fnd_api.g_exc_unexpected_error;
4701: END IF;
4702: END IF;
4703: /* Bug 5903208 Start GMF code */
4704: /* BUG 6335682 HALUTHRA : Adding gtp.pair_type (+) = 1 to avoid duplicate generation of layers to handle phantom batches*/
4801: l_trans_rec.doc_qty := -1 * l_trans_rec.doc_qty;
4802:
4803: gmf_layers.Create_outgoing_Layers
4804: ( p_api_version => 1.0,
4805: p_init_msg_list => FND_API.G_FALSE,
4806: p_tran_rec => l_trans_rec,
4807: x_return_status => x_return_status,
4808: x_msg_count => x_msg_count,
4809: x_msg_data => x_msg_data);
4815: --Bug#6322202 Added the below condition for byproducts
4816: IF trans_rec.line_type <> 1 THEN
4817: gmf_layers.Create_outgoing_Layers
4818: ( p_api_version => 1.0,
4819: p_init_msg_list => FND_API.G_FALSE,
4820: p_tran_rec => l_trans_rec,
4821: x_return_status => x_return_status,
4822: x_msg_count => x_msg_count,
4823: x_msg_data => x_msg_data);
4823: x_msg_data => x_msg_data);
4824: ELSE
4825: gmf_layers.Create_Incoming_Layers
4826: ( p_api_version => 1.0,
4827: p_init_msg_list => FND_API.G_FALSE,
4828: p_tran_rec => l_trans_rec,
4829: x_return_status => x_return_status,
4830: x_msg_count => x_msg_count,
4831: x_msg_data => x_msg_data);
4842: /* Bug 5903208 End GMF code */
4843:
4844: IF x_return_status = 'S' THEN
4845: gme_common_pvt.g_transaction_header_id := NULL;
4846: gme_common_pvt.g_batch_status_check := fnd_api.g_true;
4847: END IF;
4848: /* end temporary */
4849:
4850: IF (l_return = 0) AND (fnd_api.to_boolean (p_commit) ) THEN
4846: gme_common_pvt.g_batch_status_check := fnd_api.g_true;
4847: END IF;
4848: /* end temporary */
4849:
4850: IF (l_return = 0) AND (fnd_api.to_boolean (p_commit) ) THEN
4851: --empty the quantity tree cache
4852: IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
4853: gme_debug.put_line
4854: ( g_pkg_name
4876: || x_return_status);
4877: END IF;
4878:
4879: EXCEPTION
4880: WHEN fnd_api.g_exc_error THEN
4881: x_return_status := fnd_api.g_ret_sts_error;
4882: fnd_msg_pub.count_and_get (p_count => x_msg_count
4883: ,p_data => x_msg_data);
4884: WHEN fnd_api.g_exc_unexpected_error THEN
4877: END IF;
4878:
4879: EXCEPTION
4880: WHEN fnd_api.g_exc_error THEN
4881: x_return_status := fnd_api.g_ret_sts_error;
4882: fnd_msg_pub.count_and_get (p_count => x_msg_count
4883: ,p_data => x_msg_data);
4884: WHEN fnd_api.g_exc_unexpected_error THEN
4885: x_return_status := fnd_api.g_ret_sts_unexp_error;
4880: WHEN fnd_api.g_exc_error THEN
4881: x_return_status := fnd_api.g_ret_sts_error;
4882: fnd_msg_pub.count_and_get (p_count => x_msg_count
4883: ,p_data => x_msg_data);
4884: WHEN fnd_api.g_exc_unexpected_error THEN
4885: x_return_status := fnd_api.g_ret_sts_unexp_error;
4886:
4887: IF (NVL (g_debug, 0) > 0) THEN
4888: gme_debug.put_line ( g_pkg_name
4881: x_return_status := fnd_api.g_ret_sts_error;
4882: fnd_msg_pub.count_and_get (p_count => x_msg_count
4883: ,p_data => x_msg_data);
4884: WHEN fnd_api.g_exc_unexpected_error THEN
4885: x_return_status := fnd_api.g_ret_sts_unexp_error;
4886:
4887: IF (NVL (g_debug, 0) > 0) THEN
4888: gme_debug.put_line ( g_pkg_name
4889: || '.'
4895:
4896: fnd_msg_pub.count_and_get (p_count => x_msg_count
4897: ,p_data => x_msg_data);
4898: WHEN OTHERS THEN
4899: x_return_status := fnd_api.g_ret_sts_unexp_error;
4900:
4901: IF (NVL (g_debug, 0) > 0) THEN
4902: gme_debug.put_line ( g_pkg_name
4903: || '.'
4919: | Query quantities at a level specified by the input
4920: |
4921: | ARGUMENTS
4922: | p_api_version API Version of this procedure. Current version is 1.0
4923: | p_init_msg_list fnd_api.g_false or fnd_api.g_true is passed as input to determine whether to Initialize message list or not |
4924: | x_return_status Returns the status to indicate success or failure of execution
4925: | x_msg_count Returns number of error message in the error message stack in case of failure
4926: | x_msg_data Returns the error message in case of failure
4927: |
4938: +==========================================================================+ */
4939: PROCEDURE query_quantities (
4940: p_api_version_number IN NUMBER := 1
4941: ,p_init_msg_lst IN VARCHAR2
4942: DEFAULT fnd_api.g_false
4943: ,x_return_status OUT NOCOPY VARCHAR2
4944: ,x_msg_count OUT NOCOPY NUMBER
4945: ,x_msg_data OUT NOCOPY VARCHAR2
4946: ,p_organization_id IN NUMBER
5112: || 'Exiting with '
5113: || x_return_status);
5114: END IF;
5115: EXCEPTION
5116: WHEN fnd_api.g_exc_error THEN
5117: x_return_status := fnd_api.g_ret_sts_error;
5118: fnd_msg_pub.count_and_get (p_count => x_msg_count
5119: ,p_data => x_msg_data);
5120: WHEN fnd_api.g_exc_unexpected_error THEN
5113: || x_return_status);
5114: END IF;
5115: EXCEPTION
5116: WHEN fnd_api.g_exc_error THEN
5117: x_return_status := fnd_api.g_ret_sts_error;
5118: fnd_msg_pub.count_and_get (p_count => x_msg_count
5119: ,p_data => x_msg_data);
5120: WHEN fnd_api.g_exc_unexpected_error THEN
5121: x_return_status := fnd_api.g_ret_sts_unexp_error;
5116: WHEN fnd_api.g_exc_error THEN
5117: x_return_status := fnd_api.g_ret_sts_error;
5118: fnd_msg_pub.count_and_get (p_count => x_msg_count
5119: ,p_data => x_msg_data);
5120: WHEN fnd_api.g_exc_unexpected_error THEN
5121: x_return_status := fnd_api.g_ret_sts_unexp_error;
5122:
5123: IF (NVL (g_debug, 0) > 0) THEN
5124: gme_debug.put_line ( g_pkg_name
5117: x_return_status := fnd_api.g_ret_sts_error;
5118: fnd_msg_pub.count_and_get (p_count => x_msg_count
5119: ,p_data => x_msg_data);
5120: WHEN fnd_api.g_exc_unexpected_error THEN
5121: x_return_status := fnd_api.g_ret_sts_unexp_error;
5122:
5123: IF (NVL (g_debug, 0) > 0) THEN
5124: gme_debug.put_line ( g_pkg_name
5125: || '.'
5131:
5132: fnd_msg_pub.count_and_get (p_count => x_msg_count
5133: ,p_data => x_msg_data);
5134: WHEN OTHERS THEN
5135: x_return_status := fnd_api.g_ret_sts_unexp_error;
5136:
5137: IF (NVL (g_debug, 0) > 0) THEN
5138: gme_debug.put_line ( g_pkg_name
5139: || '.'
5156: | return the quantities at the level after the update
5157: |
5158: | ARGUMENTS
5159: | p_api_version API Version of this procedure. Current version is 1.0
5160: | p_init_msg_list fnd_api.g_false or fnd_api.g_true is passed as input to determine whether to Initialize message list or not |
5161: | x_return_status Returns the status to indicate success or failure of execution
5162: | x_msg_count Returns number of error message in the error message stack in case of failure
5163: | x_msg_data Returns the error message in case of failure
5164: |
5171: +==========================================================================+ */
5172: PROCEDURE update_quantities (
5173: p_api_version_number IN NUMBER := 1
5174: ,p_init_msg_lst IN VARCHAR2
5175: DEFAULT fnd_api.g_false
5176: ,x_return_status OUT NOCOPY VARCHAR2
5177: ,x_msg_count OUT NOCOPY NUMBER
5178: ,x_msg_data OUT NOCOPY VARCHAR2
5179: ,p_organization_id IN NUMBER
5368: || 'Exiting with '
5369: || x_return_status);
5370: END IF;
5371: EXCEPTION
5372: WHEN fnd_api.g_exc_error THEN
5373: x_return_status := fnd_api.g_ret_sts_error;
5374: fnd_msg_pub.count_and_get (p_count => x_msg_count
5375: ,p_data => x_msg_data);
5376: WHEN fnd_api.g_exc_unexpected_error THEN
5369: || x_return_status);
5370: END IF;
5371: EXCEPTION
5372: WHEN fnd_api.g_exc_error THEN
5373: x_return_status := fnd_api.g_ret_sts_error;
5374: fnd_msg_pub.count_and_get (p_count => x_msg_count
5375: ,p_data => x_msg_data);
5376: WHEN fnd_api.g_exc_unexpected_error THEN
5377: x_return_status := fnd_api.g_ret_sts_unexp_error;
5372: WHEN fnd_api.g_exc_error THEN
5373: x_return_status := fnd_api.g_ret_sts_error;
5374: fnd_msg_pub.count_and_get (p_count => x_msg_count
5375: ,p_data => x_msg_data);
5376: WHEN fnd_api.g_exc_unexpected_error THEN
5377: x_return_status := fnd_api.g_ret_sts_unexp_error;
5378:
5379: IF (NVL (g_debug, 0) > 0) THEN
5380: gme_debug.put_line ( g_pkg_name
5373: x_return_status := fnd_api.g_ret_sts_error;
5374: fnd_msg_pub.count_and_get (p_count => x_msg_count
5375: ,p_data => x_msg_data);
5376: WHEN fnd_api.g_exc_unexpected_error THEN
5377: x_return_status := fnd_api.g_ret_sts_unexp_error;
5378:
5379: IF (NVL (g_debug, 0) > 0) THEN
5380: gme_debug.put_line ( g_pkg_name
5381: || '.'
5387:
5388: fnd_msg_pub.count_and_get (p_count => x_msg_count
5389: ,p_data => x_msg_data);
5390: WHEN OTHERS THEN
5391: x_return_status := fnd_api.g_ret_sts_unexp_error;
5392:
5393: IF (NVL (g_debug, 0) > 0) THEN
5394: gme_debug.put_line ( g_pkg_name
5395: || '.'
5462: BEGIN
5463: IF (NVL (g_debug, 0) IN (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
5464: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':' || 'Entering');
5465: END IF;
5466: x_return_status := fnd_api.g_ret_sts_success;
5467: INSERT INTO mtl_transactions_interface
5468: (transaction_interface_id
5469: ,transaction_header_id
5470: ,source_code
5591: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':' || 'Exiting');
5592: END IF;
5593: EXCEPTION
5594: WHEN OTHERS THEN
5595: x_return_status := fnd_api.g_ret_sts_unexp_error;
5596: IF (NVL (g_debug, 0) > 0) THEN
5597: gme_debug.put_line (g_pkg_name|| '.'|| l_api_name|| ':'|| 'WHEN OTHERS:'|| SQLERRM);
5598: END IF;
5599: END insert_txn_inter_hdr;
5638: FROM mtl_transaction_lot_numbers
5639: WHERE transaction_id = v_transaction_id;
5640:
5641: l_api_name CONSTANT VARCHAR2 (30) := 'GET_MMT_TRANSACTIONS';
5642: l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
5643: l_transaction_id NUMBER;
5644: no_transaction_found EXCEPTION;
5645:
5646: BEGIN
5643: l_transaction_id NUMBER;
5644: no_transaction_found EXCEPTION;
5645:
5646: BEGIN
5647: x_return_status := fnd_api.g_ret_sts_success;
5648:
5649: IF (g_debug <= gme_debug.g_log_statement) THEN
5650: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
5651: || 'Entering with transaction '||p_transaction_id);
5679: get_lot_trans (p_transaction_id => l_transaction_id
5680: ,x_mmln_tbl => x_mmln_tbl
5681: ,x_return_status => l_return_status);
5682:
5683: IF l_return_status <> fnd_api.g_ret_sts_success THEN
5684: IF (g_debug <= gme_debug.g_log_statement) THEN
5685: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
5686: || 'error from get lot trans');
5687: END IF;
5684: IF (g_debug <= gme_debug.g_log_statement) THEN
5685: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
5686: || 'error from get lot trans');
5687: END IF;
5688: RAISE fnd_api.g_exc_error;
5689: END IF;
5690: ELSE /* IF cur_get_transaction%FOUND THEN */
5691: CLOSE cur_get_transaction;
5692: gme_common_pvt.log_message ('GME_NO_TRANS_FOUND');
5689: END IF;
5690: ELSE /* IF cur_get_transaction%FOUND THEN */
5691: CLOSE cur_get_transaction;
5692: gme_common_pvt.log_message ('GME_NO_TRANS_FOUND');
5693: RAISE fnd_api.g_exc_error;
5694: END IF;
5695: CLOSE cur_get_transaction;
5696:
5697: IF (g_debug <= gme_debug.g_log_statement) THEN
5711: || 'Exiting with '
5712: || x_return_status);
5713: END IF;
5714: EXCEPTION
5715: WHEN fnd_api.g_exc_error THEN
5716: x_return_status := fnd_api.g_ret_sts_error;
5717: WHEN fnd_api.g_exc_unexpected_error THEN
5718: x_return_status := fnd_api.g_ret_sts_unexp_error;
5719: WHEN OTHERS THEN
5712: || x_return_status);
5713: END IF;
5714: EXCEPTION
5715: WHEN fnd_api.g_exc_error THEN
5716: x_return_status := fnd_api.g_ret_sts_error;
5717: WHEN fnd_api.g_exc_unexpected_error THEN
5718: x_return_status := fnd_api.g_ret_sts_unexp_error;
5719: WHEN OTHERS THEN
5720: x_return_status := fnd_api.g_ret_sts_unexp_error;
5713: END IF;
5714: EXCEPTION
5715: WHEN fnd_api.g_exc_error THEN
5716: x_return_status := fnd_api.g_ret_sts_error;
5717: WHEN fnd_api.g_exc_unexpected_error THEN
5718: x_return_status := fnd_api.g_ret_sts_unexp_error;
5719: WHEN OTHERS THEN
5720: x_return_status := fnd_api.g_ret_sts_unexp_error;
5721: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
5714: EXCEPTION
5715: WHEN fnd_api.g_exc_error THEN
5716: x_return_status := fnd_api.g_ret_sts_error;
5717: WHEN fnd_api.g_exc_unexpected_error THEN
5718: x_return_status := fnd_api.g_ret_sts_unexp_error;
5719: WHEN OTHERS THEN
5720: x_return_status := fnd_api.g_ret_sts_unexp_error;
5721: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
5722:
5716: x_return_status := fnd_api.g_ret_sts_error;
5717: WHEN fnd_api.g_exc_unexpected_error THEN
5718: x_return_status := fnd_api.g_ret_sts_unexp_error;
5719: WHEN OTHERS THEN
5720: x_return_status := fnd_api.g_ret_sts_unexp_error;
5721: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
5722:
5723: IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
5724: gme_debug.put_line ( g_pkg_name
5750: IF (g_debug <= gme_debug.g_log_procedure) THEN
5751: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'|| 'Entering with organization_id = '||p_organization_id
5752: ||' inventory_item_id = '||p_inventory_item_id||' lot_number = '||p_lot_number);
5753: END IF;
5754: x_return_status := FND_API.G_RET_STS_SUCCESS;
5755: OPEN Cur_get_lot;
5756: FETCH Cur_get_lot INTO l_date;
5757: /* Bug 15879394 Removed the erroring out */
5758: /*
5771: EXCEPTION
5772: WHEN expired_lot THEN
5773: gme_common_pvt.log_message(p_message_code => 'INV_LOT_EXPIRED',
5774: p_product_code => 'INV');
5775: x_return_status := fnd_api.g_ret_sts_error;
5776: WHEN invalid_lot THEN
5777: x_return_status := fnd_api.g_ret_sts_unexp_error;
5778: gme_common_pvt.log_message(p_message_code => 'INV_INVALID_LOT',
5779: p_product_code => 'INV');
5773: gme_common_pvt.log_message(p_message_code => 'INV_LOT_EXPIRED',
5774: p_product_code => 'INV');
5775: x_return_status := fnd_api.g_ret_sts_error;
5776: WHEN invalid_lot THEN
5777: x_return_status := fnd_api.g_ret_sts_unexp_error;
5778: gme_common_pvt.log_message(p_message_code => 'INV_INVALID_LOT',
5779: p_product_code => 'INV');
5780: WHEN OTHERS THEN
5781: x_return_status := fnd_api.g_ret_sts_unexp_error;
5777: x_return_status := fnd_api.g_ret_sts_unexp_error;
5778: gme_common_pvt.log_message(p_message_code => 'INV_INVALID_LOT',
5779: p_product_code => 'INV');
5780: WHEN OTHERS THEN
5781: x_return_status := fnd_api.g_ret_sts_unexp_error;
5782: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
5783: IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
5784: gme_debug.put_line ( g_pkg_name|| '.'|| l_api_name|| ':'|| 'WHEN OTHERS:'|| SQLERRM);
5785: END IF;
5811: lot_val_err EXCEPTION;
5812: l_api_name CONSTANT VARCHAR2(30) := 'GMO_PRE_PROCESS_VAL';
5813: BEGIN
5814: -- Initially let us assign the return status to success
5815: x_return_status := fnd_api.g_ret_sts_success;
5816: IF (g_debug <= gme_debug.g_log_statement) THEN
5817: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'|| 'Entering');
5818: END IF;
5819: IF (g_debug <= gme_debug.g_log_statement) THEN
5829: END IF;
5830: IF p_mmti_rec.transaction_source_id IS NOT NULL THEN
5831: l_batch_hdr_rec.batch_id := p_mmti_rec.transaction_source_id;
5832: IF NOT gme_batch_header_dbl.fetch_row(l_batch_hdr_rec, l_batch_hdr_rec) THEN
5833: RAISE fnd_api.g_exc_error;
5834: END IF;
5835: ELSE
5836: RAISE fnd_api.g_exc_error;
5837: END IF; -- transaction_source_id IS NOT NULL
5832: IF NOT gme_batch_header_dbl.fetch_row(l_batch_hdr_rec, l_batch_hdr_rec) THEN
5833: RAISE fnd_api.g_exc_error;
5834: END IF;
5835: ELSE
5836: RAISE fnd_api.g_exc_error;
5837: END IF; -- transaction_source_id IS NOT NULL
5838:
5839: IF l_batch_hdr_rec.update_inventory_ind = 'Y' THEN
5840: IF p_mmti_rec.trx_source_line_id IS NOT NULL THEN
5839: IF l_batch_hdr_rec.update_inventory_ind = 'Y' THEN
5840: IF p_mmti_rec.trx_source_line_id IS NOT NULL THEN
5841: l_mat_dtl_rec.material_detail_id := p_mmti_rec.trx_source_line_id;
5842: IF NOT gme_material_details_dbl.fetch_row(l_mat_dtl_rec, l_mat_dtl_rec) THEN
5843: RAISE fnd_api.g_exc_error;
5844: END IF; -- material fetch
5845: ELSE
5846: RAISE fnd_api.g_exc_error;
5847: END IF; -- trx_source_line_id IS NOT NULL
5842: IF NOT gme_material_details_dbl.fetch_row(l_mat_dtl_rec, l_mat_dtl_rec) THEN
5843: RAISE fnd_api.g_exc_error;
5844: END IF; -- material fetch
5845: ELSE
5846: RAISE fnd_api.g_exc_error;
5847: END IF; -- trx_source_line_id IS NOT NULL
5848: IF gme_common_pvt.g_batch_status_check = fnd_api.g_true THEN
5849: IF l_batch_hdr_rec.batch_status NOT IN (2, 3) THEN
5850: gme_common_pvt.log_message ('GME_INVALID_BATCH_STATUS');
5844: END IF; -- material fetch
5845: ELSE
5846: RAISE fnd_api.g_exc_error;
5847: END IF; -- trx_source_line_id IS NOT NULL
5848: IF gme_common_pvt.g_batch_status_check = fnd_api.g_true THEN
5849: IF l_batch_hdr_rec.batch_status NOT IN (2, 3) THEN
5850: gme_common_pvt.log_message ('GME_INVALID_BATCH_STATUS');
5851: RAISE fnd_api.g_exc_error;
5852: END IF;
5847: END IF; -- trx_source_line_id IS NOT NULL
5848: IF gme_common_pvt.g_batch_status_check = fnd_api.g_true THEN
5849: IF l_batch_hdr_rec.batch_status NOT IN (2, 3) THEN
5850: gme_common_pvt.log_message ('GME_INVALID_BATCH_STATUS');
5851: RAISE fnd_api.g_exc_error;
5852: END IF;
5853: -- Check for step status in case the item is associated to a step.
5854: l_rel_type := gme_common_pvt.is_material_auto_release(l_mat_dtl_rec.material_detail_id);
5855: IF (l_rel_type = gme_common_pvt.g_mtl_autobystep_release) THEN
5857: FETCH Cur_associated_step INTO l_step_status;
5858: CLOSE Cur_associated_step;
5859: IF l_step_status NOT IN (2,3) THEN
5860: gme_common_pvt.log_message ('GME_API_INVALID_STEP_STATUS');
5861: RAISE fnd_api.g_exc_error;
5862: END IF;
5863: END IF; -- IF ( l_rel_type = gme_common_pvt.g_mtl_autobystep_release ) THEN
5864: -- check for item release type for products
5865: IF (l_rel_type = gme_common_pvt.g_mtl_auto_release AND l_mat_dtl_rec.line_type IN (1,2) AND l_mat_dtl_rec.phantom_line_id IS NULL) THEN
5866: -- Bug 9626176 -- Allow transactions in wip batch for auto release prods.
5867: IF l_batch_hdr_rec.batch_status NOT IN (2, 3) THEN
5868: -- IF l_batch_hdr_rec.batch_status <> 3 THEN
5869: gme_common_pvt.log_message('GME_INVALID_BATCH_STATUS');
5870: RAISE fnd_api.g_exc_error;
5871: END IF;
5872: END IF;
5873: END IF; -- gme_common_pvt.g_batch_status_check
5874: IF (l_mat_dtl_rec.line_type <> -1 AND p_mmti_rec.transaction_type_id IN (gme_common_pvt.g_ing_issue, gme_common_pvt.g_ing_return))
5874: IF (l_mat_dtl_rec.line_type <> -1 AND p_mmti_rec.transaction_type_id IN (gme_common_pvt.g_ing_issue, gme_common_pvt.g_ing_return))
5875: OR (l_mat_dtl_rec.line_type <> 1 AND p_mmti_rec.transaction_type_id IN (gme_common_pvt.g_prod_completion, gme_common_pvt.g_prod_return))
5876: OR (l_mat_dtl_rec.line_type <> 2 AND p_mmti_rec.transaction_type_id IN (gme_common_pvt.g_byprod_completion, gme_common_pvt.g_byprod_return)) THEN
5877: gme_common_pvt.log_message ('GME_LINE_TYPE_TXN_TYPE_DIFF');
5878: RAISE fnd_api.g_exc_error;
5879: END IF;
5880: -- get the item propertites
5881: OPEN cur_get_item_rec (p_mmti_rec.inventory_item_id, p_mmti_rec.organization_id);
5882: FETCH cur_get_item_rec INTO l_item_rec;
5902: gme_transactions_pvt.validate_lot_for_ing(p_organization_id => p_mmti_rec.organization_id,
5903: p_inventory_item_id => p_mmti_rec.inventory_item_id,
5904: p_lot_number => p_mmli_tbl(i).lot_number,
5905: x_return_status => l_return_status);
5906: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5907: RAISE lot_val_err;
5908: END IF;
5909: END LOOP;
5910: END IF;
5921: ,p_lot_number => NULL
5922: ,p_lot_control => l_item_rec.lot_control_code
5923: ,x_available_qty => l_available_qty
5924: ,x_return_status => x_return_status);
5925: IF x_return_status <> fnd_api.g_ret_sts_success THEN
5926: RAISE fnd_api.g_exc_unexpected_error;
5927: END IF;
5928: IF (g_debug <= gme_debug.g_log_statement) THEN
5929: gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Returning Qty '|| p_mmti_rec.transaction_quantity);
5922: ,p_lot_control => l_item_rec.lot_control_code
5923: ,x_available_qty => l_available_qty
5924: ,x_return_status => x_return_status);
5925: IF x_return_status <> fnd_api.g_ret_sts_success THEN
5926: RAISE fnd_api.g_exc_unexpected_error;
5927: END IF;
5928: IF (g_debug <= gme_debug.g_log_statement) THEN
5929: gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Returning Qty '|| p_mmti_rec.transaction_quantity);
5930: gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Available to Return '|| l_available_qty);
5930: gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Available to Return '|| l_available_qty);
5931: END IF;
5932: IF ABS (l_available_qty) < ABS (p_mmti_rec.transaction_quantity) THEN
5933: gme_common_pvt.log_message ('GME_QTY_LESS_THEN_ISSUED');
5934: RAISE fnd_api.g_exc_error;
5935: END IF;
5936: ELSE /* Lot Control */
5937: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
5938: gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Item is lot control: '|| l_item_rec.lot_control_code);
5942: ,p_lot_number => p_mmli_tbl(i).lot_number
5943: ,p_lot_control => l_item_rec.lot_control_code
5944: ,x_available_qty => l_available_qty
5945: ,x_return_status => x_return_status);
5946: IF x_return_Status <> fnd_api.g_ret_sts_success THEN
5947: RAISE fnd_api.g_exc_unexpected_error;
5948: END IF;
5949: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
5950: gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Returning qty: '|| p_mmli_tbl(i).transaction_quantity);
5943: ,p_lot_control => l_item_rec.lot_control_code
5944: ,x_available_qty => l_available_qty
5945: ,x_return_status => x_return_status);
5946: IF x_return_Status <> fnd_api.g_ret_sts_success THEN
5947: RAISE fnd_api.g_exc_unexpected_error;
5948: END IF;
5949: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
5950: gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Returning qty: '|| p_mmli_tbl(i).transaction_quantity);
5951: gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Available to Return '|| l_available_qty);
5951: gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Available to Return '|| l_available_qty);
5952: END IF;
5953: IF ABS(l_available_qty) < ABS(p_mmli_tbl(i).transaction_quantity) THEN
5954: gme_common_pvt.log_message ('GME_QTY_LESS_THEN_ISSUED');
5955: RAISE fnd_api.g_exc_error;
5956: END IF;
5957: END LOOP;
5958: END IF; /* IF l_item_rec.lot_control_code = 1 THEN */
5959: END IF; /* IF transaction_type_id in RETURNS */
5963: END IF;
5964: EXCEPTION
5965: WHEN lot_val_err THEN
5966: x_return_status := l_return_status;
5967: WHEN fnd_api.g_exc_error THEN
5968: x_return_status := fnd_api.g_ret_sts_error;
5969: WHEN fnd_api.g_exc_unexpected_error THEN
5970: x_return_status := fnd_api.g_ret_sts_unexp_error;
5971: WHEN OTHERS THEN
5964: EXCEPTION
5965: WHEN lot_val_err THEN
5966: x_return_status := l_return_status;
5967: WHEN fnd_api.g_exc_error THEN
5968: x_return_status := fnd_api.g_ret_sts_error;
5969: WHEN fnd_api.g_exc_unexpected_error THEN
5970: x_return_status := fnd_api.g_ret_sts_unexp_error;
5971: WHEN OTHERS THEN
5972: x_return_status := fnd_api.g_ret_sts_unexp_error;
5965: WHEN lot_val_err THEN
5966: x_return_status := l_return_status;
5967: WHEN fnd_api.g_exc_error THEN
5968: x_return_status := fnd_api.g_ret_sts_error;
5969: WHEN fnd_api.g_exc_unexpected_error THEN
5970: x_return_status := fnd_api.g_ret_sts_unexp_error;
5971: WHEN OTHERS THEN
5972: x_return_status := fnd_api.g_ret_sts_unexp_error;
5973: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
5966: x_return_status := l_return_status;
5967: WHEN fnd_api.g_exc_error THEN
5968: x_return_status := fnd_api.g_ret_sts_error;
5969: WHEN fnd_api.g_exc_unexpected_error THEN
5970: x_return_status := fnd_api.g_ret_sts_unexp_error;
5971: WHEN OTHERS THEN
5972: x_return_status := fnd_api.g_ret_sts_unexp_error;
5973: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
5974: IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
5968: x_return_status := fnd_api.g_ret_sts_error;
5969: WHEN fnd_api.g_exc_unexpected_error THEN
5970: x_return_status := fnd_api.g_ret_sts_unexp_error;
5971: WHEN OTHERS THEN
5972: x_return_status := fnd_api.g_ret_sts_unexp_error;
5973: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
5974: IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
5975: gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'WHEN OTHERS:'|| SQLERRM);
5976: END IF;