[Home] [Help]
42: REM * This will aid in handling Product Yield reversals first for layer sequencing for GMF.
43: REM * Procedure: process_transactions
44:
45: REM * Apeksha Mishra 21-Sep-2009 Bug 8605909
46: REM * Added the call to function gme_common_pvt.check_close_period to check whether
47: REM * the period is closed or not.
48: REM * Procedure: delete_material_txn
49:
50: REM * G. Muratore 05-AUG-2009 Bug 8639523 (rework of 7385309 for ingreds)
145: |
146: +==========================================================================+ */
147: PROCEDURE create_material_txn (
148: p_mmti_rec IN mtl_transactions_interface%ROWTYPE
149: ,p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl
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';
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;
159: x_mmti_rec mtl_transactions_interface%ROWTYPE;
160: x_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
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;
159: x_mmti_rec mtl_transactions_interface%ROWTYPE;
160: x_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
161: l_mat_dtl_rec gme_material_details%ROWTYPE;
162: m_mat_dtl_rec gme_material_details%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;
159: x_mmti_rec mtl_transactions_interface%ROWTYPE;
160: x_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
161: l_mat_dtl_rec gme_material_details%ROWTYPE;
162: m_mat_dtl_rec gme_material_details%ROWTYPE;
163: l_ret NUMBER;
164: l_api_version NUMBER;
210:
211: -- Start bug 9832061
212: IF (g_debug <= gme_debug.g_log_statement) THEN
213: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
214: || 'Before calling gme_common_pvt.check_close_period');
215: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
216: || 'l_mmti_rec.transaction_date = ' || to_char(l_mmti_rec.transaction_date, 'DD-MON-YYYY HH24:MI:SS'));
217:
218: END IF;
219:
220: l_trans_date := l_mmti_rec.transaction_date;
221:
222: -- Bug 8605909 Check to see if original transaction date is in a closed period
223: IF NOT gme_common_pvt.check_close_period(p_org_id => l_mmti_rec.organization_id
224: ,p_trans_date => l_mmti_rec.transaction_date) THEN
225:
226: -- Let's default to timestamp and overwrite if the user entered a different date.
227: IF (g_debug <= gme_debug.g_log_statement) THEN
224: ,p_trans_date => l_mmti_rec.transaction_date) THEN
225:
226: -- Let's default to timestamp and overwrite if the user entered a different date.
227: IF (g_debug <= gme_debug.g_log_statement) THEN
228: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Assigning gme_common_pvt.g_timestamp to transaction date');
229: END IF;
230:
231: l_mmti_rec.transaction_date := gme_common_pvt.g_timestamp;
232:
227: IF (g_debug <= gme_debug.g_log_statement) THEN
228: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Assigning gme_common_pvt.g_timestamp to transaction date');
229: END IF;
230:
231: l_mmti_rec.transaction_date := gme_common_pvt.g_timestamp;
232:
233: -- bug 9832061
234: --IF l_trans_date IS NOT NULL AND l_trans_date <> l_mmt_rec.transaction_date THEN
235: IF l_trans_date IS NOT NULL AND l_trans_date > l_mmti_rec.transaction_date THEN
319: END IF;
320:
321: m_mmti_rec.transaction_source_id := m_mat_dtl_rec.batch_id;
322:
323: IF l_mmti_rec.transaction_type_id = gme_common_pvt.g_ing_issue THEN
324: --(35)
325: m_mmti_rec.transaction_type_id :=
326: gme_common_pvt.g_prod_completion;
327: -- (44)
322:
323: IF l_mmti_rec.transaction_type_id = gme_common_pvt.g_ing_issue THEN
324: --(35)
325: m_mmti_rec.transaction_type_id :=
326: gme_common_pvt.g_prod_completion;
327: -- (44)
328: m_mmti_rec.transaction_action_id :=
329: gme_common_pvt.g_prod_comp_txn_action;
330: --(31)
325: m_mmti_rec.transaction_type_id :=
326: gme_common_pvt.g_prod_completion;
327: -- (44)
328: m_mmti_rec.transaction_action_id :=
329: gme_common_pvt.g_prod_comp_txn_action;
330: --(31)
331: ELSIF l_mmti_rec.transaction_type_id = gme_common_pvt.g_ing_return THEN
332: --(43)
333: m_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_return;
327: -- (44)
328: m_mmti_rec.transaction_action_id :=
329: gme_common_pvt.g_prod_comp_txn_action;
330: --(31)
331: ELSIF l_mmti_rec.transaction_type_id = gme_common_pvt.g_ing_return THEN
332: --(43)
333: m_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_return;
334: -- (17)
335: m_mmti_rec.transaction_action_id :=
329: gme_common_pvt.g_prod_comp_txn_action;
330: --(31)
331: ELSIF l_mmti_rec.transaction_type_id = gme_common_pvt.g_ing_return THEN
332: --(43)
333: m_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_return;
334: -- (17)
335: m_mmti_rec.transaction_action_id :=
336: gme_common_pvt.g_prod_ret_txn_action;
337: --(27)
332: --(43)
333: m_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_return;
334: -- (17)
335: m_mmti_rec.transaction_action_id :=
336: gme_common_pvt.g_prod_ret_txn_action;
337: --(27)
338: ELSIF l_mmti_rec.transaction_type_id =
339: gme_common_pvt.g_prod_completion THEN
340: -- (44)
335: m_mmti_rec.transaction_action_id :=
336: gme_common_pvt.g_prod_ret_txn_action;
337: --(27)
338: ELSIF l_mmti_rec.transaction_type_id =
339: gme_common_pvt.g_prod_completion THEN
340: -- (44)
341: m_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_issue;
342: -- (35)
343: m_mmti_rec.transaction_action_id :=
337: --(27)
338: ELSIF l_mmti_rec.transaction_type_id =
339: gme_common_pvt.g_prod_completion THEN
340: -- (44)
341: m_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_issue;
342: -- (35)
343: m_mmti_rec.transaction_action_id :=
344: gme_common_pvt.g_ing_issue_txn_action;
345: --(1)
340: -- (44)
341: m_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_issue;
342: -- (35)
343: m_mmti_rec.transaction_action_id :=
344: gme_common_pvt.g_ing_issue_txn_action;
345: --(1)
346: ELSIF l_mmti_rec.transaction_type_id =
347: gme_common_pvt.g_prod_return THEN
348: m_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_return;
343: m_mmti_rec.transaction_action_id :=
344: gme_common_pvt.g_ing_issue_txn_action;
345: --(1)
346: ELSIF l_mmti_rec.transaction_type_id =
347: gme_common_pvt.g_prod_return THEN
348: m_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_return;
349: -- (17)
350: m_mmti_rec.transaction_action_id :=
351: gme_common_pvt.g_ing_ret_txn_action;
344: gme_common_pvt.g_ing_issue_txn_action;
345: --(1)
346: ELSIF l_mmti_rec.transaction_type_id =
347: gme_common_pvt.g_prod_return THEN
348: m_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_return;
349: -- (17)
350: m_mmti_rec.transaction_action_id :=
351: gme_common_pvt.g_ing_ret_txn_action;
352: --(27)
347: gme_common_pvt.g_prod_return THEN
348: m_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_return;
349: -- (17)
350: m_mmti_rec.transaction_action_id :=
351: gme_common_pvt.g_ing_ret_txn_action;
352: --(27)
353: ELSIF l_mmti_rec.transaction_type_id =
354: gme_common_pvt.g_byprod_completion THEN
355: -- add for byprod type_id
350: m_mmti_rec.transaction_action_id :=
351: gme_common_pvt.g_ing_ret_txn_action;
352: --(27)
353: ELSIF l_mmti_rec.transaction_type_id =
354: gme_common_pvt.g_byprod_completion THEN
355: -- add for byprod type_id
356: m_mmti_rec.transaction_action_id :=
357: gme_common_pvt.g_byprod_ret_txn_action;
358: --(32)
353: ELSIF l_mmti_rec.transaction_type_id =
354: gme_common_pvt.g_byprod_completion THEN
355: -- add for byprod type_id
356: m_mmti_rec.transaction_action_id :=
357: gme_common_pvt.g_byprod_ret_txn_action;
358: --(32)
359: ELSE
360: --l_mmti_rec.transaction_type_id = gme_common_pvt.g_byprod_return THEN
361: -- add for byprod type_id
356: m_mmti_rec.transaction_action_id :=
357: gme_common_pvt.g_byprod_ret_txn_action;
358: --(32)
359: ELSE
360: --l_mmti_rec.transaction_type_id = gme_common_pvt.g_byprod_return THEN
361: -- add for byprod type_id
362: m_mmti_rec.transaction_action_id :=
363: gme_common_pvt.g_byprod_comp_txn_action;
364: --(31)
359: ELSE
360: --l_mmti_rec.transaction_type_id = gme_common_pvt.g_byprod_return THEN
361: -- add for byprod type_id
362: m_mmti_rec.transaction_action_id :=
363: gme_common_pvt.g_byprod_comp_txn_action;
364: --(31)
365: END IF;
366:
367: 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
440: || '.'
441: || l_api_name
442: || ':'
443: || 'calling validate transactions:'
444: || gme_common_pvt.g_transaction_header_id);
445:
446:
447: select count(*)
448: into l_cnt_int
446:
447: select count(*)
448: into l_cnt_int
449: from mtl_transactions_interface
450: where transaction_header_id= gme_common_pvt.g_transaction_header_id;
451: gme_debug.put_line ( g_pkg_name
452: || '.'
453: || l_api_name
454: || ':'
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
475: ,x_trans_count => l_txn_count
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
481: from mtl_material_transactions_temp
482: where transaction_header_id= gme_common_pvt.g_transaction_header_id;
483: gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'count from temp table:'|| l_cnt_temp);
484: END IF;
485: IF l_ret < 0 THEN
486: IF (g_debug <= gme_debug.g_log_statement) THEN
490: IF (l_msg_data IS NOT NULL) THEN
491: IF (g_debug <= gme_debug.g_log_statement) THEN
492: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'l_msg_data is: '||l_msg_data);
493: END IF;
494: gme_common_pvt.log_message(p_message_code => 'FND_GENERIC_MESSAGE'
495: ,p_product_code => 'FND'
496: ,p_token1_name => 'MESSAGE'
497: ,p_token1_value => l_msg_data);
498: ELSE
497: ,p_token1_value => l_msg_data);
498: ELSE
499: /* Bug 5256543 Get messages from interface table and put on stack */
500: FOR get_msgs IN (SELECT error_explanation FROM mtl_transactions_interface
501: WHERE transaction_header_id = gme_common_pvt.g_transaction_header_id
502: AND error_explanation IS NOT NULL) LOOP
503: IF (g_debug <= gme_debug.g_log_statement) THEN
504: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'error_explanation is: '||get_msgs.error_explanation);
505: END IF;
502: AND error_explanation IS NOT NULL) LOOP
503: IF (g_debug <= gme_debug.g_log_statement) THEN
504: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'error_explanation is: '||get_msgs.error_explanation);
505: END IF;
506: gme_common_pvt.log_message(p_message_code => 'FND_GENERIC_MESSAGE'
507: ,p_product_code => 'FND'
508: ,p_token1_name => 'MESSAGE'
509: ,p_token1_value => get_msgs.error_explanation);
510: END LOOP;
659: +==========================================================================+ */
660: PROCEDURE update_material_txn (
661: p_transaction_id IN NUMBER
662: ,p_mmti_rec IN mtl_transactions_interface%ROWTYPE
663: ,p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl
664: ,x_return_status OUT NOCOPY VARCHAR2)
665: IS
666: l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_MATERIAL_TXN';
667: l_mmt_rec mtl_material_transactions%ROWTYPE;
664: ,x_return_status OUT NOCOPY VARCHAR2)
665: IS
666: l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_MATERIAL_TXN';
667: l_mmt_rec mtl_material_transactions%ROWTYPE;
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;
666: l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_MATERIAL_TXN';
667: l_mmt_rec mtl_material_transactions%ROWTYPE;
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;
722: -- the inventory is available as we do not get erroneous shortage messages.
723:
724: -- Bug 9770408 - Evaluate non divisible flag also. This is needed for Yield corrections.
725: l_delete_done := 0;
726: IF l_line_type = gme_common_pvt.g_line_type_ing OR l_lot_divisible = 'N' THEN
727: -- call to delete all the transactions for this transaction_id
728: IF (g_debug <= gme_debug.g_log_statement) THEN
729: gme_debug.put_line
730: ( g_pkg_name
773: RAISE create_material_txn_err;
774: END IF;
775:
776: -- Bug 9301755 - Call delete for non ingredient lines.
777: -- IF l_line_type <> gme_common_pvt.g_line_type_ing THEN
778: IF l_delete_done = 0 THEN
779: -- call to delete all the transactions for this transaction_id
780: IF (g_debug <= gme_debug.g_log_statement) THEN
781: gme_debug.put_line
866: | in place for products and byproducts.
867: +==========================================================================+ */
868: PROCEDURE update_material_txn (
869: p_mmt_rec IN mtl_material_transactions%ROWTYPE
870: ,p_mmln_tbl IN gme_common_pvt.mtl_trans_lots_num_tbl
871: ,x_return_status OUT NOCOPY VARCHAR2)
872: IS
873: l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_MATERIAL_TXN-2';
874: l_transaction_id NUMBER;
872: IS
873: l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_MATERIAL_TXN-2';
874: l_transaction_id NUMBER;
875: l_mmt_rec mtl_material_transactions%ROWTYPE;
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;
874: l_transaction_id NUMBER;
875: l_mmt_rec mtl_material_transactions%ROWTYPE;
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;
952: -- the inventory is available as we do not get erroneous shortage messages.
953:
954: -- Bug 9770408 - Evaluate non divisible flag also. This is needed for Yield corrections.
955: l_delete_done := 0;
956: IF l_line_type = gme_common_pvt.g_line_type_ing OR l_lot_divisible = 'N' THEN
957: -- call to delete all the transactions for this transaction_id
958: IF (g_debug <= gme_debug.g_log_statement) THEN
959: gme_debug.put_line
960: ( g_pkg_name
1012: RAISE create_material_txn_err;
1013: END IF;
1014:
1015: -- Bug 8639523 - Call delete for non ingredient lines.
1016: -- IF l_line_type <> gme_common_pvt.g_line_type_ing THEN
1017: IF l_delete_done = 0 THEN
1018: -- call to delete all the transactions for this transaction_id
1019: IF (g_debug <= gme_debug.g_log_statement) THEN
1020: gme_debug.put_line
1108: IS
1109: SELECT transaction_id2
1110: FROM gme_transaction_pairs
1111: WHERE transaction_id1 = v_transaction_id
1112: AND pair_type = gme_common_pvt.g_pairs_phantom_type;
1113:
1114: l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_MATERIAL_TXN';
1115: l_transaction_id NUMBER;
1116: m_transaction_id NUMBER;
1114: l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_MATERIAL_TXN';
1115: l_transaction_id NUMBER;
1116: m_transaction_id NUMBER;
1117: l_mmt_rec mtl_material_transactions%ROWTYPE;
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;
1116: m_transaction_id NUMBER;
1117: l_mmt_rec mtl_material_transactions%ROWTYPE;
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;
1161:
1162: -- bug 9832061
1163: IF (g_debug <= gme_debug.g_log_statement) THEN
1164: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1165: || 'Before calling gme_common_pvt.check_close_period');
1166: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1167: || 'l_mmt_rec.transaction_date = ' || to_char(l_mmt_rec.transaction_date, 'DD-MON-YYYY HH24:MI:SS'));
1168:
1169: IF l_trans_date IS NOT NULL THEN
1172: END IF;
1173: END IF;
1174:
1175: -- Bug 8605909 Check to see if original transaction date is in a closed period
1176: IF NOT gme_common_pvt.check_close_period(p_org_id => l_mmt_rec.organization_id
1177: ,p_trans_date => l_mmt_rec.transaction_date) THEN
1178:
1179: -- Let's default to timestamp and overwrite if the user entered a different date.
1180: IF (g_debug <= gme_debug.g_log_statement) THEN
1177: ,p_trans_date => l_mmt_rec.transaction_date) THEN
1178:
1179: -- Let's default to timestamp and overwrite if the user entered a different date.
1180: IF (g_debug <= gme_debug.g_log_statement) THEN
1181: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Assigning gme_common_pvt.g_timestamp to transaction date');
1182: END IF;
1183:
1184: l_mmt_rec.transaction_date := gme_common_pvt.g_timestamp;
1185:
1180: IF (g_debug <= gme_debug.g_log_statement) THEN
1181: gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Assigning gme_common_pvt.g_timestamp to transaction date');
1182: END IF;
1183:
1184: l_mmt_rec.transaction_date := gme_common_pvt.g_timestamp;
1185:
1186: -- bug 9832061
1187: --IF l_trans_date IS NOT NULL AND l_trans_date <> l_mmt_rec.transaction_date THEN
1188: IF l_trans_date IS NOT NULL AND l_trans_date > l_mmt_rec.transaction_date 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
1262: --(35)
1263: l_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_return;
1264: --(43)
1265: l_mmti_rec.transaction_action_id :=
1259:
1260: -- Bug 6437252 LPN Support
1261: IF l_mmti_rec.transaction_type_id = gme_common_pvt.g_ing_issue THEN
1262: --(35)
1263: l_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_return;
1264: --(43)
1265: l_mmti_rec.transaction_action_id :=
1266: gme_common_pvt.g_ing_ret_txn_action;
1267: --(27)
1262: --(35)
1263: l_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_return;
1264: --(43)
1265: l_mmti_rec.transaction_action_id :=
1266: gme_common_pvt.g_ing_ret_txn_action;
1267: --(27)
1268: l_mmti_rec.transfer_lpn_id := l_mmt_rec.lpn_id;
1269: ELSIF l_mmti_rec.transaction_type_id = gme_common_pvt.g_ing_return THEN
1270: --(43)
1265: l_mmti_rec.transaction_action_id :=
1266: gme_common_pvt.g_ing_ret_txn_action;
1267: --(27)
1268: l_mmti_rec.transfer_lpn_id := l_mmt_rec.lpn_id;
1269: ELSIF l_mmti_rec.transaction_type_id = gme_common_pvt.g_ing_return THEN
1270: --(43)
1271: l_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_issue;
1272: -- (35)
1273: l_mmti_rec.transaction_action_id :=
1267: --(27)
1268: l_mmti_rec.transfer_lpn_id := l_mmt_rec.lpn_id;
1269: ELSIF l_mmti_rec.transaction_type_id = gme_common_pvt.g_ing_return THEN
1270: --(43)
1271: l_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_issue;
1272: -- (35)
1273: l_mmti_rec.transaction_action_id :=
1274: gme_common_pvt.g_ing_issue_txn_action;
1275: --(1)
1270: --(43)
1271: l_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_issue;
1272: -- (35)
1273: l_mmti_rec.transaction_action_id :=
1274: gme_common_pvt.g_ing_issue_txn_action;
1275: --(1)
1276: l_mmti_rec.lpn_id := l_mmt_rec.transfer_lpn_id;
1277: ELSIF l_mmti_rec.transaction_type_id = gme_common_pvt.g_prod_completion THEN
1278: -- (44)
1273: l_mmti_rec.transaction_action_id :=
1274: gme_common_pvt.g_ing_issue_txn_action;
1275: --(1)
1276: l_mmti_rec.lpn_id := l_mmt_rec.transfer_lpn_id;
1277: ELSIF l_mmti_rec.transaction_type_id = gme_common_pvt.g_prod_completion THEN
1278: -- (44)
1279: l_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_return;
1280: --(17)
1281: l_mmti_rec.transaction_action_id :=
1275: --(1)
1276: l_mmti_rec.lpn_id := l_mmt_rec.transfer_lpn_id;
1277: ELSIF l_mmti_rec.transaction_type_id = gme_common_pvt.g_prod_completion THEN
1278: -- (44)
1279: l_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_return;
1280: --(17)
1281: l_mmti_rec.transaction_action_id :=
1282: gme_common_pvt.g_prod_ret_txn_action;
1283: --(27)
1278: -- (44)
1279: l_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_return;
1280: --(17)
1281: l_mmti_rec.transaction_action_id :=
1282: gme_common_pvt.g_prod_ret_txn_action;
1283: --(27)
1284: l_mmti_rec.lpn_id := l_mmt_rec.transfer_lpn_id;
1285: ELSIF l_mmti_rec.transaction_type_id = gme_common_pvt.g_prod_return THEN
1286: l_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_completion;
1281: l_mmti_rec.transaction_action_id :=
1282: gme_common_pvt.g_prod_ret_txn_action;
1283: --(27)
1284: l_mmti_rec.lpn_id := l_mmt_rec.transfer_lpn_id;
1285: ELSIF l_mmti_rec.transaction_type_id = gme_common_pvt.g_prod_return THEN
1286: l_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_completion;
1287: l_mmti_rec.transaction_action_id :=
1288: gme_common_pvt.g_prod_comp_txn_action;
1289: --(31)
1282: gme_common_pvt.g_prod_ret_txn_action;
1283: --(27)
1284: l_mmti_rec.lpn_id := l_mmt_rec.transfer_lpn_id;
1285: ELSIF l_mmti_rec.transaction_type_id = gme_common_pvt.g_prod_return THEN
1286: l_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_completion;
1287: l_mmti_rec.transaction_action_id :=
1288: gme_common_pvt.g_prod_comp_txn_action;
1289: --(31)
1290: l_mmti_rec.transfer_lpn_id := l_mmt_rec.lpn_id;
1284: l_mmti_rec.lpn_id := l_mmt_rec.transfer_lpn_id;
1285: ELSIF l_mmti_rec.transaction_type_id = gme_common_pvt.g_prod_return THEN
1286: l_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_completion;
1287: l_mmti_rec.transaction_action_id :=
1288: gme_common_pvt.g_prod_comp_txn_action;
1289: --(31)
1290: l_mmti_rec.transfer_lpn_id := l_mmt_rec.lpn_id;
1291: ELSIF l_mmti_rec.transaction_type_id =
1292: gme_common_pvt.g_byprod_completion THEN
1288: gme_common_pvt.g_prod_comp_txn_action;
1289: --(31)
1290: l_mmti_rec.transfer_lpn_id := l_mmt_rec.lpn_id;
1291: ELSIF l_mmti_rec.transaction_type_id =
1292: gme_common_pvt.g_byprod_completion THEN
1293: l_mmti_rec.transaction_type_id := gme_common_pvt.g_byprod_return;
1294: l_mmti_rec.transaction_action_id :=
1295: gme_common_pvt.g_byprod_ret_txn_action;
1296: --(32)
1289: --(31)
1290: l_mmti_rec.transfer_lpn_id := l_mmt_rec.lpn_id;
1291: ELSIF l_mmti_rec.transaction_type_id =
1292: gme_common_pvt.g_byprod_completion THEN
1293: l_mmti_rec.transaction_type_id := gme_common_pvt.g_byprod_return;
1294: l_mmti_rec.transaction_action_id :=
1295: gme_common_pvt.g_byprod_ret_txn_action;
1296: --(32)
1297: l_mmti_rec.lpn_id := l_mmt_rec.transfer_lpn_id;
1291: ELSIF l_mmti_rec.transaction_type_id =
1292: gme_common_pvt.g_byprod_completion THEN
1293: l_mmti_rec.transaction_type_id := gme_common_pvt.g_byprod_return;
1294: l_mmti_rec.transaction_action_id :=
1295: gme_common_pvt.g_byprod_ret_txn_action;
1296: --(32)
1297: l_mmti_rec.lpn_id := l_mmt_rec.transfer_lpn_id;
1298: ELSE
1299: --l_mmti_rec.transaction_type_id = gme_common_pvt.g_byprod_return THEN
1295: gme_common_pvt.g_byprod_ret_txn_action;
1296: --(32)
1297: l_mmti_rec.lpn_id := l_mmt_rec.transfer_lpn_id;
1298: ELSE
1299: --l_mmti_rec.transaction_type_id = gme_common_pvt.g_byprod_return THEN
1300: l_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_completion;
1301: l_mmti_rec.transaction_action_id :=
1302: gme_common_pvt.g_byprod_comp_txn_action;
1303: --(31)
1296: --(32)
1297: l_mmti_rec.lpn_id := l_mmt_rec.transfer_lpn_id;
1298: ELSE
1299: --l_mmti_rec.transaction_type_id = gme_common_pvt.g_byprod_return THEN
1300: l_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_completion;
1301: l_mmti_rec.transaction_action_id :=
1302: gme_common_pvt.g_byprod_comp_txn_action;
1303: --(31)
1304: l_mmti_rec.transfer_lpn_id := l_mmt_rec.lpn_id;
1298: ELSE
1299: --l_mmti_rec.transaction_type_id = gme_common_pvt.g_byprod_return THEN
1300: l_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_completion;
1301: l_mmti_rec.transaction_action_id :=
1302: gme_common_pvt.g_byprod_comp_txn_action;
1303: --(31)
1304: l_mmti_rec.transfer_lpn_id := l_mmt_rec.lpn_id;
1305: END IF;
1306:
1332: --Bug#8453427 Start
1333: /*Bug#8453427 Added the delete call for the phantom transactions for the product return transactions, as the
1334: corresponsing phantom transctions would be of the production completion types and always +ve sign transactions should be
1335: created first */
1336: IF (l_mmti_rec.transaction_type_id = gme_common_pvt.g_prod_return OR
1337: l_mmti_rec.transaction_type_id = gme_common_pvt.g_byprod_return ) THEN
1338: IF l_mat_dtl_rec.phantom_line_id IS NOT NULL AND p_txns_pair IS NULL THEN
1339: IF (g_debug <= gme_debug.g_log_statement) THEN
1340: gme_debug.put_line ( g_pkg_name
1333: /*Bug#8453427 Added the delete call for the phantom transactions for the product return transactions, as the
1334: corresponsing phantom transctions would be of the production completion types and always +ve sign transactions should be
1335: created first */
1336: IF (l_mmti_rec.transaction_type_id = gme_common_pvt.g_prod_return OR
1337: l_mmti_rec.transaction_type_id = gme_common_pvt.g_byprod_return ) THEN
1338: IF l_mat_dtl_rec.phantom_line_id IS NOT NULL AND p_txns_pair IS NULL THEN
1339: IF (g_debug <= gme_debug.g_log_statement) THEN
1340: gme_debug.put_line ( g_pkg_name
1341: || '.'
1414: || '.'
1415: || l_api_name
1416: || ':'
1417: || 'inserting into pairs table pair_type:'
1418: || gme_common_pvt.g_pairs_reversal_type);
1419: END IF;
1420:
1421: INSERT INTO gme_transaction_pairs
1422: (batch_id, material_detail_id
1423: ,transaction_id1, transaction_id2
1424: ,pair_type)
1425: VALUES (l_mat_dtl_rec.batch_id, l_mat_dtl_rec.material_detail_id
1426: ,l_mmt_rec.transaction_id, NULL
1427: ,gme_common_pvt.g_pairs_reversal_type);
1428:
1429:
1430: -- with the new rec- call the create txn
1431: create_material_txn (p_mmti_rec => l_mmti_rec
1451:
1452:
1453: /*Bug#8453427 Added the below if condition as the phantom transactions already created
1454: above for the product and by product return transaction types. */
1455: IF (l_mmti_rec.transaction_type_id <> gme_common_pvt.g_prod_return AND
1456: l_mmti_rec.transaction_type_id <> gme_common_pvt.g_byprod_return ) THEN
1457: -- changes for phantom
1458: IF l_mat_dtl_rec.phantom_line_id IS NOT NULL AND p_txns_pair IS NULL THEN
1459: IF (g_debug <= gme_debug.g_log_statement) THEN
1452:
1453: /*Bug#8453427 Added the below if condition as the phantom transactions already created
1454: above for the product and by product return transaction types. */
1455: IF (l_mmti_rec.transaction_type_id <> gme_common_pvt.g_prod_return AND
1456: l_mmti_rec.transaction_type_id <> gme_common_pvt.g_byprod_return ) THEN
1457: -- changes for phantom
1458: IF l_mat_dtl_rec.phantom_line_id IS NOT NULL AND p_txns_pair IS NULL THEN
1459: IF (g_debug <= gme_debug.g_log_statement) THEN
1460: gme_debug.put_line ( g_pkg_name
1576: | Initialize secondary_quantity when necessary.
1577: +==========================================================================+ */
1578: PROCEDURE build_txn_inter (
1579: p_mmti_rec IN mtl_transactions_interface%ROWTYPE
1580: ,p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl
1581: ,p_assign_phantom IN NUMBER DEFAULT 0
1582: ,x_mmti_rec OUT NOCOPY mtl_transactions_interface%ROWTYPE
1583: ,x_mmli_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_inter_tbl
1584: ,x_return_status OUT NOCOPY VARCHAR2)
1579: p_mmti_rec IN mtl_transactions_interface%ROWTYPE
1580: ,p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl
1581: ,p_assign_phantom IN NUMBER DEFAULT 0
1582: ,x_mmti_rec OUT NOCOPY mtl_transactions_interface%ROWTYPE
1583: ,x_mmli_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_inter_tbl
1584: ,x_return_status OUT NOCOPY VARCHAR2)
1585: IS
1586: l_mmti_rec mtl_transactions_interface%ROWTYPE;
1587: l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
1583: ,x_mmli_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_inter_tbl
1584: ,x_return_status OUT NOCOPY VARCHAR2)
1585: IS
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;
1687: || 'transaction_quantity '
1688: || l_mmli_tbl (i).transaction_quantity);
1689: END IF;
1690: /* Bug 4929610 added lot expiry check */
1691: IF (x_mmti_rec.transaction_type_id = gme_common_pvt.g_ing_issue) THEN
1692: IF (gme_transactions_pvt.is_lot_expired (p_organization_id => x_mmti_rec.organization_id,
1693: p_inventory_item_id => x_mmti_rec.inventory_item_id,
1694: p_lot_number => l_mmli_tbl(i).lot_number,
1695: p_date => x_mmti_rec.transaction_date)) THEN
1710: l_prim_qty := inv_convert.inv_um_convert
1711: (item_id => x_mmti_rec.inventory_item_id
1712: ,lot_number => l_mmli_tbl(i).lot_number
1713: ,organization_id => x_mmti_rec.organization_id
1714: ,precision => gme_common_pvt.g_precision
1715: ,from_quantity => l_mmli_tbl(i).transaction_quantity
1716: ,from_unit => x_mmti_rec.transaction_uom
1717: ,to_unit => l_primary_uom
1718: ,from_name => NULL
1741: l_secondary_qty := inv_convert.inv_um_convert
1742: (item_id => x_mmti_rec.inventory_item_id
1743: ,lot_number => l_mmli_tbl(i).lot_number
1744: ,organization_id => x_mmti_rec.organization_id
1745: ,precision => gme_common_pvt.g_precision
1746: ,from_quantity => l_mmli_tbl(i).transaction_quantity
1747: ,from_unit => x_mmti_rec.transaction_uom
1748: ,to_unit => l_secondary_uom
1749: ,from_name => NULL
1783: RAISE build_txn_inter_lot_err;
1784: END IF;
1785:
1786: -- Bug 13925279 - Assign the user entered expiration date.
1787: IF (x_mmti_rec.transaction_type_id <> gme_common_pvt.g_ing_issue AND
1788: l_mmli_tbl(i).lot_expiration_date IS NOT NULL) THEN
1789: UPDATE mtl_transaction_lots_interface
1790: SET lot_expiration_date = l_mmli_tbl(i).lot_expiration_date
1791: WHERE transaction_interface_id = x_mmti_rec.transaction_interface_id;
1909: END IF;
1910:
1911: l_mmti_rec := p_mmti_rec;
1912:
1913: IF gme_common_pvt.g_transaction_header_id IS NULL THEN
1914: SELECT mtl_material_transactions_s.NEXTVAL
1915: INTO gme_common_pvt.g_transaction_header_id
1916: FROM DUAL;
1917:
1911: l_mmti_rec := p_mmti_rec;
1912:
1913: IF gme_common_pvt.g_transaction_header_id IS NULL THEN
1914: SELECT mtl_material_transactions_s.NEXTVAL
1915: INTO gme_common_pvt.g_transaction_header_id
1916: FROM DUAL;
1917:
1918: IF (g_debug <= gme_debug.g_log_statement) THEN
1919: gme_debug.put_line ( g_pkg_name
1919: gme_debug.put_line ( g_pkg_name
1920: || '.'
1921: || l_api_name
1922: || ':'
1923: || 'GME_COMMON_PVT.g_transaction_header_id '
1924: || gme_common_pvt.g_transaction_header_id);
1925: END IF;
1926: END IF;
1927:
1920: || '.'
1921: || l_api_name
1922: || ':'
1923: || 'GME_COMMON_PVT.g_transaction_header_id '
1924: || gme_common_pvt.g_transaction_header_id);
1925: END IF;
1926: END IF;
1927:
1928: l_mmti_rec.transaction_header_id :=
1925: END IF;
1926: END IF;
1927:
1928: l_mmti_rec.transaction_header_id :=
1929: gme_common_pvt.g_transaction_header_id;
1930:
1931: IF (g_debug <= gme_debug.g_log_statement) THEN
1932: gme_debug.put_line ( g_pkg_name
1933: || '.'
1941: INTO l_mmti_rec.transaction_interface_id
1942: FROM DUAL;
1943:
1944: IF l_mmti_rec.transaction_type_id IN
1945: (gme_common_pvt.g_ing_return
1946: ,gme_common_pvt.g_prod_completion
1947: ,gme_common_pvt.g_byprod_completion) THEN
1948:
1949: -- Bug 9770408 - Do not overwrite transaction_batch_seq value if it is passed in.
1942: FROM DUAL;
1943:
1944: IF l_mmti_rec.transaction_type_id IN
1945: (gme_common_pvt.g_ing_return
1946: ,gme_common_pvt.g_prod_completion
1947: ,gme_common_pvt.g_byprod_completion) THEN
1948:
1949: -- Bug 9770408 - Do not overwrite transaction_batch_seq value if it is passed in.
1950: IF l_mmti_rec.transaction_batch_seq IS NULL THEN
1943:
1944: IF l_mmti_rec.transaction_type_id IN
1945: (gme_common_pvt.g_ing_return
1946: ,gme_common_pvt.g_prod_completion
1947: ,gme_common_pvt.g_byprod_completion) THEN
1948:
1949: -- Bug 9770408 - Do not overwrite transaction_batch_seq value if it is passed in.
1950: IF l_mmti_rec.transaction_batch_seq IS NULL THEN
1951: l_mmti_rec.transaction_batch_seq := 1;
2139: || '.'
2140: || l_api_name
2141: || ':'
2142: || 'last_updated_by: '
2143: || gme_common_pvt.g_user_ident);
2144: gme_debug.put_line ( g_pkg_name
2145: || '.'
2146: || l_api_name
2147: || ':'
2224: INTO l_type
2225: FROM mtl_transaction_types
2226: WHERE transaction_type_id = l_mmti_rec.transaction_type_id;
2227: IF l_locator IS NOT NULL THEN
2228: gme_common_pvt.log_message ('GME_MATERIAL_STS_INV_SUB_LOC'
2229: ,'TRANSTYPE',l_type,'ITEM',l_item
2230: ,'SUBINV',l_mmti_rec.subinventory_code
2231: ,'LOCN',l_locator);
2232: ELSE
2229: ,'TRANSTYPE',l_type,'ITEM',l_item
2230: ,'SUBINV',l_mmti_rec.subinventory_code
2231: ,'LOCN',l_locator);
2232: ELSE
2233: gme_common_pvt.log_message ('GME_MATERIAL_STS_INV_SUB'
2234: ,'TRANSTYPE',l_type,'ITEM',l_item
2235: ,'SUBINV',l_mmti_rec.subinventory_code);
2236: END IF;
2237: gme_debug.put_line ( g_pkg_name
2329: l_mmli_rec.transaction_interface_id := p_trans_inter_id;
2330: l_transaction_type_id := p_transaction_type_id;
2331: l_inventory_item_id := p_inventory_item_id ;
2332: IF l_transaction_type_id IN
2333: (gme_common_pvt.g_ing_return
2334: ,gme_common_pvt.g_prod_completion
2335: ,gme_common_pvt.g_byprod_completion) THEN
2336: l_mmli_rec.transaction_quantity :=
2337: ABS(l_mmli_rec.transaction_quantity);
2330: l_transaction_type_id := p_transaction_type_id;
2331: l_inventory_item_id := p_inventory_item_id ;
2332: IF l_transaction_type_id IN
2333: (gme_common_pvt.g_ing_return
2334: ,gme_common_pvt.g_prod_completion
2335: ,gme_common_pvt.g_byprod_completion) THEN
2336: l_mmli_rec.transaction_quantity :=
2337: ABS(l_mmli_rec.transaction_quantity);
2338: l_mmli_rec.secondary_transaction_quantity :=
2331: l_inventory_item_id := p_inventory_item_id ;
2332: IF l_transaction_type_id IN
2333: (gme_common_pvt.g_ing_return
2334: ,gme_common_pvt.g_prod_completion
2335: ,gme_common_pvt.g_byprod_completion) THEN
2336: l_mmli_rec.transaction_quantity :=
2337: ABS(l_mmli_rec.transaction_quantity);
2338: l_mmli_rec.secondary_transaction_quantity :=
2339: ABS(l_mmli_rec.secondary_transaction_quantity);
2351: ,p_trx_status_enabled => NULL
2352: ,p_trx_type_id => l_transaction_type_id
2353: ,p_lot_status_enabled => NULL
2354: ,p_serial_status_enabled => NULL
2355: ,p_organization_id => gme_common_pvt.g_organization_id
2356: ,p_inventory_item_id => l_inventory_item_id
2357: ,p_sub_code => p_subinventory_code
2358: ,p_locator_id => p_locator_id
2359: ,p_lot_number => l_mmli_rec.lot_number
2453: ,n_attribute8
2454: ,n_attribute9
2455: ,n_attribute10)
2456: VALUES ( p_trans_inter_id --transaction_interface_id
2457: ,gme_common_pvt.g_timestamp --last_update_date
2458: ,gme_common_pvt.g_user_ident --last_updated_by
2459: ,gme_common_pvt.g_user_ident --last_update_login
2460: ,gme_common_pvt.g_timestamp --creation_date
2461: ,gme_common_pvt.g_user_ident --created_by
2454: ,n_attribute9
2455: ,n_attribute10)
2456: VALUES ( p_trans_inter_id --transaction_interface_id
2457: ,gme_common_pvt.g_timestamp --last_update_date
2458: ,gme_common_pvt.g_user_ident --last_updated_by
2459: ,gme_common_pvt.g_user_ident --last_update_login
2460: ,gme_common_pvt.g_timestamp --creation_date
2461: ,gme_common_pvt.g_user_ident --created_by
2462: ,l_mmli_rec.parent_lot_number --parent lot_number
2455: ,n_attribute10)
2456: VALUES ( p_trans_inter_id --transaction_interface_id
2457: ,gme_common_pvt.g_timestamp --last_update_date
2458: ,gme_common_pvt.g_user_ident --last_updated_by
2459: ,gme_common_pvt.g_user_ident --last_update_login
2460: ,gme_common_pvt.g_timestamp --creation_date
2461: ,gme_common_pvt.g_user_ident --created_by
2462: ,l_mmli_rec.parent_lot_number --parent lot_number
2463: /*Bug#7372673*/
2456: VALUES ( p_trans_inter_id --transaction_interface_id
2457: ,gme_common_pvt.g_timestamp --last_update_date
2458: ,gme_common_pvt.g_user_ident --last_updated_by
2459: ,gme_common_pvt.g_user_ident --last_update_login
2460: ,gme_common_pvt.g_timestamp --creation_date
2461: ,gme_common_pvt.g_user_ident --created_by
2462: ,l_mmli_rec.parent_lot_number --parent lot_number
2463: /*Bug#7372673*/
2464: ,l_mmli_rec.lot_number --lot_number
2457: ,gme_common_pvt.g_timestamp --last_update_date
2458: ,gme_common_pvt.g_user_ident --last_updated_by
2459: ,gme_common_pvt.g_user_ident --last_update_login
2460: ,gme_common_pvt.g_timestamp --creation_date
2461: ,gme_common_pvt.g_user_ident --created_by
2462: ,l_mmli_rec.parent_lot_number --parent lot_number
2463: /*Bug#7372673*/
2464: ,l_mmli_rec.lot_number --lot_number
2465: ,l_mmli_rec.transaction_quantity --lot_quantity
2539: WHEN material_status_err THEN
2540: SELECT substr(concatenated_segments,1,100)
2541: INTO l_item
2542: FROM mtl_system_items_kfv
2543: WHERE organization_id = gme_common_pvt.g_organization_id
2544: AND inventory_item_id = l_inventory_item_id;
2545: SELECT transaction_type_name
2546: INTO l_type
2547: FROM mtl_transaction_types
2545: SELECT transaction_type_name
2546: INTO l_type
2547: FROM mtl_transaction_types
2548: WHERE transaction_type_id = p_transaction_type_id;
2549: gme_common_pvt.log_message ('GME_MATERIAL_STS_INV_LOT'
2550: ,'TRANSTYPE',l_type,'ITEM',l_item
2551: ,'LOT',l_mmli_rec.lot_number);
2552: gme_debug.put_line ( g_pkg_name
2553: || '.'
2607: +==========================================================================+ */
2608: PROCEDURE get_transactions (
2609: p_transaction_id IN NUMBER
2610: ,x_mmt_rec OUT NOCOPY mtl_material_transactions%ROWTYPE
2611: ,x_mmln_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_num_tbl
2612: ,x_return_status OUT NOCOPY VARCHAR2)
2613: IS
2614:
2615: -- Namit S. Bug4917213. Changed query to add hint to improve sharable memory.
2648: || 'Entering with transaction '||p_transaction_id);
2649: END IF;
2650:
2651: IF p_transaction_id IS NULL THEN
2652: gme_common_pvt.log_message ('GME_NO_KEYS', 'TABLE_NAME', l_api_name);
2653:
2654: IF (g_debug <= gme_debug.g_log_statement) THEN
2655: gme_debug.put_line ( g_pkg_name
2656: || '.'
2661: END IF;
2662:
2663: l_transaction_id := p_transaction_id;
2664: -- Namit S. Bug4917213.
2665: OPEN cur_get_transaction (l_transaction_id, gme_common_pvt.g_pairs_reversal_type);
2666: FETCH cur_get_transaction
2667: INTO x_mmt_rec;
2668: IF cur_get_transaction%FOUND THEN
2669: IF (g_debug <= gme_debug.g_log_statement) THEN
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');
2691: RAISE fnd_api.g_exc_error;
2692: END IF;
2693: CLOSE cur_get_transaction;
2694:
2748: |
2749: +==========================================================================+ */
2750: PROCEDURE construct_mmti (
2751: p_mmt_rec IN mtl_material_transactions%ROWTYPE
2752: ,p_mmln_tbl IN gme_common_pvt.mtl_trans_lots_num_tbl
2753: ,x_mmti_rec OUT NOCOPY mtl_transactions_interface%ROWTYPE
2754: ,x_mmli_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_inter_tbl
2755: ,x_return_status OUT NOCOPY VARCHAR2)
2756: IS
2750: PROCEDURE construct_mmti (
2751: p_mmt_rec IN mtl_material_transactions%ROWTYPE
2752: ,p_mmln_tbl IN gme_common_pvt.mtl_trans_lots_num_tbl
2753: ,x_mmti_rec OUT NOCOPY mtl_transactions_interface%ROWTYPE
2754: ,x_mmli_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_inter_tbl
2755: ,x_return_status OUT NOCOPY VARCHAR2)
2756: IS
2757: l_mmt_rec mtl_material_transactions%ROWTYPE;
2758: l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
2754: ,x_mmli_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_inter_tbl
2755: ,x_return_status OUT NOCOPY VARCHAR2)
2756: IS
2757: l_mmt_rec mtl_material_transactions%ROWTYPE;
2758: l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
2759: l_mmti_rec mtl_transactions_interface%ROWTYPE;
2760: l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
2761: l_api_name CONSTANT VARCHAR2 (30) := 'CONSTRUCT_MMTI';
2762: BEGIN
2756: IS
2757: l_mmt_rec mtl_material_transactions%ROWTYPE;
2758: l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
2759: l_mmti_rec mtl_transactions_interface%ROWTYPE;
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;
2877: p_mat_det_id IN NUMBER
2878: ,p_batch_id IN NUMBER
2879: ,p_phantom_line_id IN NUMBER DEFAULT NULL
2880: ,p_order_by IN NUMBER DEFAULT 1
2881: ,x_mmt_tbl OUT NOCOPY gme_common_pvt.mtl_mat_tran_tbl
2882: ,x_return_status OUT NOCOPY VARCHAR2)
2883: IS
2884:
2885: -- Namit S. Bug4917213. Changed query to add hint to improve sharable memory.
2958: SELECT batch_id INTO p_phantom_batch_id
2959: FROM gme_material_details
2960: WHERE material_detail_id = p_phantom_line_id;
2961: OPEN cur_get_all_trans (p_mat_det_id, p_batch_id,p_phantom_line_id,p_phantom_batch_id,
2962: gme_common_pvt.g_txn_source_type, gme_common_pvt.g_pairs_reversal_type);
2963: FETCH cur_get_all_trans
2964: BULK COLLECT INTO x_mmt_tbl;
2965: CLOSE cur_get_all_trans;
2966: ELSE
2965: CLOSE cur_get_all_trans;
2966: ELSE
2967: -- Namit S. Bug4917213.
2968: OPEN cur_get_trans (p_mat_det_id, p_batch_id,
2969: gme_common_pvt.g_txn_source_type, gme_common_pvt.g_pairs_reversal_type);
2970: FETCH cur_get_trans
2971: BULK COLLECT INTO x_mmt_tbl;
2972: CLOSE cur_get_trans;
2973: END IF;
3018: |
3019: +==========================================================================+ */
3020: PROCEDURE get_lot_trans (
3021: p_transaction_id IN NUMBER
3022: ,x_mmln_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_num_tbl
3023: ,x_return_status OUT NOCOPY VARCHAR2)
3024: IS
3025: CURSOR cur_get_lot_trans (v_transaction_id NUMBER)
3026: IS
3106: IS
3107: SELECT transaction_interface_id
3108: FROM mtl_transactions_interface
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;
3107: SELECT transaction_interface_id
3108: FROM mtl_transactions_interface
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;
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
3118: -- Initially let us assign the return status to success
3126: IF p_transaction_hdr_id IS NOT NULL THEN
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;
3218: ) IS
3219: SELECT SUM (DECODE (v_trans_uom,
3220: t.transaction_uom, transaction_quantity,
3221: inv_convert.inv_um_convert (d.inventory_item_id,
3222: gme_common_pvt.g_precision,
3223: t.transaction_quantity,
3224: t.transaction_uom,
3225: v_trans_uom,
3226: NULL,
3232: WHERE t.organization_id = v_organization_id
3233: AND t.inventory_item_id = v_item_id
3234: AND t.transaction_source_id = v_batch_id
3235: AND t.trx_source_line_id = v_mat_det_id
3236: AND t.transaction_source_type_id = gme_common_pvt.g_txn_source_type
3237: AND t.trx_source_line_id = d.material_detail_id
3238: AND (t.revision IS NULL OR t.revision = v_revision)
3239: GROUP BY t.revision, t.inventory_item_id;
3240:
3252: m.transaction_uom, m.transaction_quantity,
3253: inv_convert.inv_um_convert (d.inventory_item_id,
3254: v_lot_number,
3255: v_organization_id,
3256: gme_common_pvt.g_precision,
3257: m.transaction_quantity,
3258: m.transaction_uom,
3259: v_trans_uom,
3260: NULL,
3269: AND l.inventory_item_id = v_item_id
3270: AND l.organization_id = v_organization_id
3271: AND l.transaction_source_id = v_batch_id
3272: AND m.trx_source_line_id = v_mat_det_id
3273: AND m.transaction_source_type_id = gme_common_pvt.g_txn_source_type
3274: -- Pawan Kumar added for checking of revision bug 5451006- 5493370
3275: AND (m.revision IS NULL OR m.revision = v_revision)
3276: GROUP BY l.lot_number, l.inventory_item_id;
3277:
3403: WHERE transaction_interface_id = v_trans_inter_id
3404: GROUP BY lot_number;
3405:
3406: l_mmti_rec mtl_transactions_interface%ROWTYPE;
3407: l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
3408: l_mat_dtl_rec gme_material_details%ROWTYPE;
3409: l_batch_hdr_rec gme_batch_header%ROWTYPE;
3410: l_item_rec mtl_system_items_b%ROWTYPE;
3411: l_available_qty NUMBER;
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;
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;
3478: -- Check for step status in case the item is associated to a step.
3479: l_rel_type :=
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
3481: (l_mat_dtl_rec.material_detail_id);
3482: IF ( l_rel_type = gme_common_pvt.g_mtl_autobystep_release ) THEN -- /*3*/
3483: OPEN Cur_associated_step(l_mat_dtl_rec.material_detail_id);
3484: FETCH Cur_associated_step INTO l_step_status;
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
3481: (l_mat_dtl_rec.material_detail_id);
3482: IF ( l_rel_type = gme_common_pvt.g_mtl_autobystep_release ) THEN -- /*3*/
3483: OPEN Cur_associated_step(l_mat_dtl_rec.material_detail_id);
3484: FETCH Cur_associated_step INTO l_step_status;
3485: CLOSE Cur_associated_step;
3486: IF l_step_status NOT IN (2,3) THEN
3483: OPEN Cur_associated_step(l_mat_dtl_rec.material_detail_id);
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:
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
3493: IF (l_rel_type = gme_common_pvt.g_mtl_auto_release )
3494: AND l_mat_dtl_rec.line_type IN (1,2)
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
3493: IF (l_rel_type = gme_common_pvt.g_mtl_auto_release )
3494: AND l_mat_dtl_rec.line_type IN (1,2)
3495: AND l_mat_dtl_rec.phantom_line_id IS NULL THEN
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
3495: AND l_mat_dtl_rec.phantom_line_id IS NULL THEN
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
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 */
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;
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
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
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');
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
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;
3512: (gme_common_pvt.g_prod_completion
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
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.
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');
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:
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;
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:
3540: OPEN cur_get_item_rec (l_mmti_rec.inventory_item_id, l_mmti_rec.organization_id);
3541: FETCH cur_get_item_rec INTO l_item_rec;
3542: IF cur_get_item_rec%NOTFOUND THEN
3543: CLOSE cur_get_item_rec;
3544: gme_common_pvt.log_message ('PM_INVALID_ITEM');
3545: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3546: gme_debug.put_line('Item cursor fetch no record in mtl_system_items_b: ');
3547: gme_debug.put_line('inventory_item_id = '|| TO_CHAR (l_mmti_rec.inventory_item_id));
3548: gme_debug.put_line('organization_id = '|| TO_CHAR (l_mmti_rec.organization_id));
3555: gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Item loct_control Code: '|| l_item_rec.location_control_code);
3556: END IF;
3557:
3558: /* Bug 5358129 for ingredients lots should exist */
3559: IF (l_mat_dtl_rec.line_type = gme_common_pvt.g_line_type_ing AND l_mat_dtl_rec.phantom_type = 0 AND l_item_rec.lot_control_code = 2) THEN
3560: FOR get_lots IN (SELECT DISTINCT lot_number FROM mtl_transaction_lots_interface WHERE transaction_interface_id = p_transaction_interface_id) LOOP
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,
3569: END IF;
3570:
3571: -- if return transaction then check qty was issued and return not more than issued qty
3572: IF l_mmti_rec.transaction_type_id IN
3573: (gme_common_pvt.g_byprod_return
3574: ,gme_common_pvt.g_prod_return
3575: ,gme_common_pvt.g_ing_return) THEN
3576: IF (g_debug <= gme_debug.g_log_statement) THEN
3577: gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||
3570:
3571: -- if return transaction then check qty was issued and return not more than issued qty
3572: IF l_mmti_rec.transaction_type_id IN
3573: (gme_common_pvt.g_byprod_return
3574: ,gme_common_pvt.g_prod_return
3575: ,gme_common_pvt.g_ing_return) THEN
3576: IF (g_debug <= gme_debug.g_log_statement) THEN
3577: gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||
3578: 'Return transaction for : '||l_mmti_rec.transaction_type_id);
3571: -- if return transaction then check qty was issued and return not more than issued qty
3572: IF l_mmti_rec.transaction_type_id IN
3573: (gme_common_pvt.g_byprod_return
3574: ,gme_common_pvt.g_prod_return
3575: ,gme_common_pvt.g_ing_return) THEN
3576: IF (g_debug <= gme_debug.g_log_statement) THEN
3577: gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||
3578: 'Return transaction for : '||l_mmti_rec.transaction_type_id);
3579:
3612: || l_available_qty);
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
3652: || l_available_qty);
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 */
3739: || 'Entering');
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.
3791: |
3792: | HISTORY
3793: | Created 02-Feb-05 Pawan Kumar
3794: | 5176319 20-Jun-06 Namit S. Added call to gme_unrelease_batch_pvt.create_matl_resv_pplot
3795: | and gme_common_pvt.reset_txn_hdr_tbl.
3796: | Bug 5763818 28-Feb-2007 Archana Mundhe Do not update actual qty if
3797: | the material detail line has been deleted.
3798: | Bug 8300015 Changed the logic of updating the phantom transactions.
3799: | Bug back port 6997483 Srinivasulu Puri Added parameter transaction_id
3825: FROM mtl_material_transactions t , gme_material_details d, mtl_transaction_lot_numbers tl
3826: WHERE t.organization_id = v_organization_id
3827: AND t.transaction_source_id = v_batch_id
3828: AND t.trx_source_line_id = v_mat_det_id
3829: AND t.transaction_source_type_id = gme_common_pvt.g_txn_source_type
3830: AND t.trx_source_line_id = d.material_detail_id
3831: AND tl.transaction_id(+) = t.transaction_id) a;
3832:
3833: CURSOR cur_lot_qty (
3845: AND l.organization_id = v_organization_id
3846: AND l.transaction_source_id = v_batch_id
3847: AND m.trx_source_line_id = v_mat_det_id
3848: AND m.transaction_source_type_id =
3849: gme_common_pvt.g_txn_source_type
3850: GROUP BY l.lot_number, l.inventory_item_id;
3851:
3852: -- Bug 5763818
3853: CURSOR check_event_batchmtl_removed (
3973: SET transaction_id2 = l_transaction_id
3974: WHERE batch_id = l_transaction_source_id
3975: AND material_detail_id = l_trx_source_line_id
3976: AND transaction_id1 = l_source_line_id
3977: AND pair_type = gme_common_pvt.g_pairs_reversal_type;
3978: -- Now insert a reverse record
3979: INSERT INTO gme_transaction_pairs
3980: (batch_id, material_detail_id
3981: ,transaction_id1, transaction_id2
3981: ,transaction_id1, transaction_id2
3982: ,pair_type)
3983: VALUES (l_transaction_source_id, l_trx_source_line_id
3984: ,l_transaction_id, l_source_line_id
3985: ,gme_common_pvt.g_pairs_reversal_type);
3986:
3987: IF (g_debug <= gme_debug.g_log_statement) THEN
3988: gme_debug.put_line ( g_pkg_name
3989: || '.'
4048: (l_mat_dtl_rec.phantom_line_id = l_gme_pairs_rec_upd.material_detail_id) THEN
4049: UPDATE gme_transaction_pairs
4050: SET transaction_id2 = l_transaction_id
4051: WHERE transaction_id1 = l_gme_pairs_rec_upd.transaction_id1
4052: AND pair_type = gme_common_pvt.g_pairs_phantom_type;
4053: END IF;
4054: END LOOP;
4055: BEGIN
4056: SELECT *
4056: SELECT *
4057: INTO l_gme_pairs_rec
4058: FROM gme_transaction_pairs
4059: WHERE transaction_id2 = l_transaction_id
4060: AND pair_type = gme_common_pvt.g_pairs_phantom_type;
4061:
4062: IF (g_debug <= gme_debug.g_log_statement) THEN
4063: gme_debug.put_line
4064: ( g_pkg_name
4106: ,transaction_id1, transaction_id2
4107: ,pair_type)
4108: VALUES (l_transaction_source_id, l_trx_source_line_id
4109: ,l_transaction_id, l_gme_pairs_rec.transaction_id1
4110: ,gme_common_pvt.g_pairs_phantom_type);
4111:
4112:
4113: EXCEPTION
4114: WHEN NO_DATA_FOUND THEN
4150: ,transaction_id1, transaction_id2
4151: ,pair_type)
4152: VALUES (l_transaction_source_id, l_trx_source_line_id
4153: ,l_transaction_id, l_transaction_reference
4154: ,gme_common_pvt.g_pairs_phantom_type);
4155: END;
4156: ELSE
4157: l_dispense_id := l_transaction_reference ;
4158: -- make a call to GMO for informing about dispense_id
4156: ELSE
4157: l_dispense_id := l_transaction_reference ;
4158: -- make a call to GMO for informing about dispense_id
4159: IF l_mat_dtl_rec.dispense_ind = 'Y' THEN
4160: IF l_transaction_type_id = gme_common_pvt.g_ing_issue THEN
4161: -- For consume
4162: GMO_DISPENSE_GRP.CHANGE_DISPENSE_STATUS
4163: (p_api_version => 1.0,
4164: p_init_msg_list => 'F',
4261: -- Pawan Kumar bug 5483071 added check for transaction refernce and source_line_id
4262: -- THis done so that we donot try to invoke this for reversal of wip return
4263: -- dispense is not a issue as it is only for ingredients
4264: IF l_mat_dtl_rec.line_type <> -1 AND
4265: l_transaction_type_id = gme_common_pvt.g_prod_completion
4266: AND l_transaction_reference IS NULL
4267: -- Pawan Kumar add bug 5709186
4268: -- in case transaction added from transaction form, l_source_line_id is null.
4269: AND nvl(l_source_line_id, -99) < 0 THEN
4361: || 'Entering');
4362: END IF;
4363:
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;
4442: | x_msg_count Returns number of error message in the error message stack in case of failure
4443: | x_msg_data Returns the error message in case of failure
4444: | x_trans_count The count of material transaction interface records processed.
4445: | p_table Source of transaction records with value 1 of material transaction interface table and value 2 of material transaction temp table
4446: | p_header_id Transaction header id DEFAULT gme_common_pvt.get_txn_header_id
4447: |
4448: | RETURNS
4449: | returns via x_ OUT parameters
4450: |
4452: | Created 07-Mar-05 Jalaj Srivastava
4453: | 26-JUL-2007 Swapna Bug#6266714
4454: | Added condition to check the transaction source type in the for loop query.
4455: | 26-JUL-2007 Swapna Bug#6685680
4456: | Added call to gme_common_pvt.log message to log the actual error message which we can
4457: | retrieve using gme_common_pvt.count_and_get from the wraper apis.
4458: |
4459: | 10-MAR-2009 Hari Luthra BUG # 6335682
4460: | Added condition gtp.pair_type(+) = 1 to avoid duplicate rows in the for loop while creation
4453: | 26-JUL-2007 Swapna Bug#6266714
4454: | Added condition to check the transaction source type in the for loop query.
4455: | 26-JUL-2007 Swapna Bug#6685680
4456: | Added call to gme_common_pvt.log message to log the actual error message which we can
4457: | retrieve using gme_common_pvt.count_and_get from the wraper apis.
4458: |
4459: | 10-MAR-2009 Hari Luthra BUG # 6335682
4460: | Added condition gtp.pair_type(+) = 1 to avoid duplicate rows in the for loop while creation
4461: | of layers so as to handle the transactions for phantom batches.
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
4496: CURSOR get_error_int
4497: IS
4498: SELECT ERROR_CODE, error_explanation
4499: FROM mtl_transactions_interface
4500: WHERE transaction_header_id =gme_common_pvt.g_transaction_header_id
4501: AND error_explanation IS NOT NULL;
4502:
4503: CURSOR get_error_temp
4504: IS
4503: CURSOR get_error_temp
4504: IS
4505: SELECT ERROR_CODE, error_explanation
4506: FROM mtl_material_transactions_temp
4507: WHERE transaction_header_id =gme_common_pvt.g_transaction_header_id
4508: AND error_explanation IS NOT NULL;
4509:
4510: l_api_name CONSTANT VARCHAR2 (30) := 'PROCESS_TRANSACTIONS';
4511: l_return NUMBER;
4654: IF (l_return = 0) THEN
4655: x_return_status := 'S';
4656: END IF;
4657: IF (x_msg_data IS NOT NULL) THEN
4658: gme_common_pvt.log_message(p_message_code => 'FND_GENERIC_MESSAGE'
4659: ,p_product_code => 'FND'
4660: ,p_token1_name => 'MESSAGE'
4661: ,p_token1_value => x_msg_data);
4662: END IF;
4665: IF p_table = 1 THEN
4666: x_msg_count := 0;
4667: FOR rec IN get_error_int LOOP
4668: /*Bug#6685680 Add the below call to log the actual error message*/
4669: gme_common_pvt.log_message(p_message_code => 'FND_GENERIC_MESSAGE'
4670: ,p_product_code => 'FND'
4671: ,p_token1_name => 'MESSAGE'
4672: ,p_token1_value => rec.error_explanation);
4673: --fnd_message.set_encoded (rec.error_explanation);
4681: ELSE
4682: x_msg_count := 0;
4683: FOR rec IN get_error_temp LOOP
4684: /*Bug#6685680 Add the below call to log the actual error message*/
4685: gme_common_pvt.log_message(p_message_code => 'FND_GENERIC_MESSAGE'
4686: ,p_product_code => 'FND'
4687: ,p_token1_name => 'MESSAGE'
4688: ,p_token1_value => rec.error_explanation);
4689: --fnd_message.set_encoded (rec.error_explanation);
4747: mtl_system_items_b msi,
4748: gme_material_details md,
4749: gme_transaction_pairs gtp
4750: WHERE
4751: mmt.transaction_set_id = gme_common_pvt.g_transaction_header_id
4752: --AND mtln.transaction_id(+) = mmt.transaction_id /*Commented for Bug 8347011*/
4753: AND msi.organization_id = mmt.organization_id
4754: AND msi.inventory_item_id = mmt.inventory_item_id
4755: AND md.material_detail_id = mmt.trx_source_line_id
4755: AND md.material_detail_id = mmt.trx_source_line_id
4756: AND gtp.transaction_id1(+) = mmt.transaction_id
4757: AND gtp.batch_id(+) = mmt.transaction_source_id
4758: AND gtp.material_detail_id(+) = mmt.trx_source_line_id
4759: AND mmt.transaction_source_type_id = gme_common_pvt.g_txn_source_type /*Bug#6266714*/
4760: AND gtp.pair_type (+) = 1 /*BUG 6335682 */
4761: ORDER BY mmt.transaction_date,
4762: case md.line_type
4763: when -1 then 0
4841: p_qty_tbl.delete();
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:
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
4860:
4861: inv_quantity_tree_pub.clear_quantity_cache;
4862: END IF;
4863:
4864: gme_common_pvt.reset_txn_hdr_tbl; -- nsinghi bug#5176319
4865:
4866: -- Bug 8751983 - Reset global IB timestamp to NULL.
4867: gme_common_pvt.g_ib_timestamp_set := 0;
4868:
4863:
4864: gme_common_pvt.reset_txn_hdr_tbl; -- nsinghi bug#5176319
4865:
4866: -- Bug 8751983 - Reset global IB timestamp to NULL.
4867: gme_common_pvt.g_ib_timestamp_set := 0;
4868:
4869: IF (NVL (g_debug, 0) IN
4870: (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4871: gme_debug.put_line ( g_pkg_name
4948: ,p_tree_mode IN INTEGER
4949: ,p_is_serial_control IN BOOLEAN DEFAULT FALSE
4950: ,p_grade_code IN VARCHAR2
4951: ,p_demand_source_type_id IN NUMBER
4952: DEFAULT gme_common_pvt.g_txn_source_type
4953: ,p_demand_source_header_id IN NUMBER DEFAULT -9999
4954: ,p_demand_source_line_id IN NUMBER DEFAULT -9999
4955: ,p_demand_source_name IN VARCHAR2 DEFAULT NULL
4956: ,p_lot_expiration_date IN DATE DEFAULT NULL
5180: ,p_inventory_item_id IN NUMBER
5181: ,p_tree_mode IN INTEGER
5182: ,p_is_serial_control IN BOOLEAN := FALSE
5183: ,p_demand_source_type_id IN NUMBER
5184: DEFAULT gme_common_pvt.g_txn_source_type
5185: ,p_demand_source_header_id IN NUMBER DEFAULT -9999
5186: ,p_demand_source_line_id IN NUMBER DEFAULT -9999
5187: ,p_demand_source_name IN VARCHAR2 DEFAULT NULL
5188: ,p_lot_expiration_date IN DATE DEFAULT NULL
5442: IF l_expire_date IS NULL THEN
5443: RETURN FALSE;
5444: ELSE
5445: IF l_expire_date < NVL(p_date, SYSDATE) THEN
5446: gme_common_pvt.log_message(p_product_code => 'INV', p_message_code => 'INV_LOT_EXPIRED');
5447: RETURN TRUE;
5448: END IF;
5449: END IF;
5450: RETURN FALSE;
5521: ,attribute_category -- Bug 12881196 Add missing attribute columns so that they get saved
5522: ,transfer_lpn_id
5523: ,lpn_id) -- Bug 6437252 LPN support
5524: VALUES (p_mmti_rec.transaction_interface_id
5525: ,gme_common_pvt.g_transaction_header_id
5526: ,'OPM' -- source_code
5527: ,p_mmti_rec.transaction_source_id --source_header_id
5528: ,1 -- lock_flag
5529: ,2 -- transaction_mode
5531: ,2 -- validation_required
5532: , NVL (p_mmti_rec.source_line_id, -99)-- transaction_id for reversal
5533: ,p_mmti_rec.transaction_source_id -- batch id
5534: ,p_mmti_rec.trx_source_line_id -- material detail id
5535: ,gme_common_pvt.g_user_ident --last_updated_by
5536: ,gme_common_pvt.g_user_ident -- last_update_login
5537: ,gme_common_pvt.g_timestamp --last_update_date
5538: ,gme_common_pvt.g_timestamp --creation_date
5539: ,gme_common_pvt.g_user_ident --created_by
5532: , NVL (p_mmti_rec.source_line_id, -99)-- transaction_id for reversal
5533: ,p_mmti_rec.transaction_source_id -- batch id
5534: ,p_mmti_rec.trx_source_line_id -- material detail id
5535: ,gme_common_pvt.g_user_ident --last_updated_by
5536: ,gme_common_pvt.g_user_ident -- last_update_login
5537: ,gme_common_pvt.g_timestamp --last_update_date
5538: ,gme_common_pvt.g_timestamp --creation_date
5539: ,gme_common_pvt.g_user_ident --created_by
5540: ,p_mmti_rec.inventory_item_id -- inventory_item_id
5533: ,p_mmti_rec.transaction_source_id -- batch id
5534: ,p_mmti_rec.trx_source_line_id -- material detail id
5535: ,gme_common_pvt.g_user_ident --last_updated_by
5536: ,gme_common_pvt.g_user_ident -- last_update_login
5537: ,gme_common_pvt.g_timestamp --last_update_date
5538: ,gme_common_pvt.g_timestamp --creation_date
5539: ,gme_common_pvt.g_user_ident --created_by
5540: ,p_mmti_rec.inventory_item_id -- inventory_item_id
5541: ,p_mmti_rec.revision
5534: ,p_mmti_rec.trx_source_line_id -- material detail id
5535: ,gme_common_pvt.g_user_ident --last_updated_by
5536: ,gme_common_pvt.g_user_ident -- last_update_login
5537: ,gme_common_pvt.g_timestamp --last_update_date
5538: ,gme_common_pvt.g_timestamp --creation_date
5539: ,gme_common_pvt.g_user_ident --created_by
5540: ,p_mmti_rec.inventory_item_id -- inventory_item_id
5541: ,p_mmti_rec.revision
5542: ,p_mmti_rec.organization_id --organization_id
5535: ,gme_common_pvt.g_user_ident --last_updated_by
5536: ,gme_common_pvt.g_user_ident -- last_update_login
5537: ,gme_common_pvt.g_timestamp --last_update_date
5538: ,gme_common_pvt.g_timestamp --creation_date
5539: ,gme_common_pvt.g_user_ident --created_by
5540: ,p_mmti_rec.inventory_item_id -- inventory_item_id
5541: ,p_mmti_rec.revision
5542: ,p_mmti_rec.organization_id --organization_id
5543: /* FPBug#4543872 rework
5558: p_mmti_rec.transaction_uom, --transaction_uom
5559: p_mmti_rec.subinventory_code
5560: , --subinventory_code
5561: p_mmti_rec.locator_id, --locator_id
5562: gme_common_pvt.g_txn_source_type
5563: , -- (Batch) transaction_source_type_id
5564: gme_common_pvt.g_wip_entity_type_batch -- (for batch) wip_entity_type
5565: ,p_mmti_rec.transaction_source_name -- transaction_source_name
5566: ,p_mmti_rec.transaction_reference
5560: , --subinventory_code
5561: p_mmti_rec.locator_id, --locator_id
5562: gme_common_pvt.g_txn_source_type
5563: , -- (Batch) transaction_source_type_id
5564: gme_common_pvt.g_wip_entity_type_batch -- (for batch) wip_entity_type
5565: ,p_mmti_rec.transaction_source_name -- transaction_source_name
5566: ,p_mmti_rec.transaction_reference
5567: ,p_mmti_rec.reason_id
5568: ,p_mmti_rec.transaction_batch_id -- must populate for seq
5621: +==========================================================================+ */
5622: PROCEDURE get_mmt_transactions (
5623: p_transaction_id IN NUMBER
5624: ,x_mmt_rec OUT NOCOPY mtl_material_transactions%ROWTYPE
5625: ,x_mmln_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_num_tbl
5626: ,x_return_status OUT NOCOPY VARCHAR2)
5627: IS
5628:
5629: CURSOR cur_get_transaction (v_transaction_id NUMBER)
5651: || 'Entering with transaction '||p_transaction_id);
5652: END IF;
5653:
5654: IF p_transaction_id IS NULL THEN
5655: gme_common_pvt.log_message ('GME_NO_KEYS', 'TABLE_NAME', l_api_name);
5656:
5657: IF (g_debug <= gme_debug.g_log_statement) THEN
5658: gme_debug.put_line ( g_pkg_name
5659: || '.'
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');
5693: RAISE fnd_api.g_exc_error;
5694: END IF;
5695: CLOSE cur_get_transaction;
5696:
5769: gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'|| 'Normal Exiting');
5770: END IF;
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;
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;
5782: fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
5786: END validate_lot_for_ing;
5787:
5788: /* Added for bug 5597385 */
5789: PROCEDURE gmo_pre_process_val(p_mmti_rec IN mtl_transactions_interface%ROWTYPE,
5790: p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl,
5791: p_mode IN VARCHAR2,
5792: x_return_status OUT NOCOPY VARCHAR2) IS
5793: CURSOR cur_get_item_rec (v_item_id NUMBER, v_org_id NUMBER) IS
5794: SELECT *
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;
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;
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);
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
5856: OPEN Cur_associated_step(l_mat_dtl_rec.material_detail_id);
5857: FETCH Cur_associated_step INTO l_step_status;
5858: CLOSE Cur_associated_step;
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
5856: OPEN Cur_associated_step(l_mat_dtl_rec.material_detail_id);
5857: FETCH Cur_associated_step INTO l_step_status;
5858: CLOSE Cur_associated_step;
5859: IF l_step_status NOT IN (2,3) THEN
5856: OPEN Cur_associated_step(l_mat_dtl_rec.material_detail_id);
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
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
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');
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
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))
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');
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))
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;
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))
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;
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))
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
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))
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);
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;
5883: IF cur_get_item_rec%NOTFOUND THEN
5884: CLOSE cur_get_item_rec;
5885: gme_common_pvt.log_message ('PM_INVALID_ITEM');
5886: IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
5887: gme_debug.put_line('Item cursor fetch no record in mtl_system_items_b: ');
5888: gme_debug.put_line('inventory_item_id = '|| TO_CHAR (p_mmti_rec.inventory_item_id));
5889: gme_debug.put_line('organization_id = '|| TO_CHAR (p_mmti_rec.organization_id));
5896: gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Item loct_control Code: '|| l_item_rec.location_control_code);
5897: END IF;
5898:
5899: /* Bug 5358129 for ingredients lots should exist */
5900: IF (l_mat_dtl_rec.line_type = gme_common_pvt.g_line_type_ing AND l_mat_dtl_rec.phantom_type = 0 AND l_item_rec.lot_control_code = 2) THEN
5901: FOR i IN 1..p_mmli_tbl.COUNT LOOP
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,
5908: END IF;
5909: END LOOP;
5910: END IF;
5911: -- if return transaction then check qty was issued and return not more than issued qty
5912: IF p_mmti_rec.transaction_type_id IN (gme_common_pvt.g_byprod_return, gme_common_pvt.g_prod_return, gme_common_pvt.g_ing_return) THEN
5913: IF (g_debug <= gme_debug.g_log_statement) THEN
5914: gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'Return transaction for : '||p_mmti_rec.transaction_type_id);
5915: END IF;
5916: IF l_item_rec.lot_control_code = 1 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);
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
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);
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 */