[Home] [Help]
1106: IS
1107: CURSOR cur_get_ph_txns (v_transaction_id NUMBER)
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';
1387: --Bug#8453427 End
1388:
1389: -- Bug 9717803 - Move transaction pair update to happen before create of mat txn.
1390: -- Insert into gme_transactions_pairs table
1391: -- code need to added for INSERT INTO GME_TRANSACTION_PAIRS tables
1392: -- which column will carry the material detail ld
1393: IF (g_debug <= gme_debug.g_log_statement) THEN
1394: gme_debug.put_line ( g_pkg_name
1395: || '.'
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
2623: FROM mtl_material_transactions mmt
2624: WHERE transaction_id = v_transaction_id
2625: AND NOT EXISTS ( SELECT /*+ no_unnest */
2626: transaction_id1
2627: FROM gme_transaction_pairs
2628: WHERE transaction_id1 = mmt.transaction_id
2629: AND pair_type = v_reversal_type)
2630: ORDER BY mmt.transaction_quantity;
2631:
2897: AND transaction_source_id = v_batch_id
2898: AND transaction_source_type_id = v_txn_source_type
2899: AND NOT EXISTS ( SELECT /*+ no_unnest */
2900: transaction_id1
2901: FROM gme_transaction_pairs
2902: WHERE transaction_id1 = mmt.transaction_id
2903: AND pair_type = v_pairs_reversal_type)
2904: ORDER BY CASE p_order_by
2905: when 1 then Row_Number() over(order by transaction_quantity)
2919: AND transaction_source_id = v_batch_id
2920: AND transaction_source_type_id = v_txn_source_type
2921: AND NOT EXISTS ( SELECT /*+ no_unnest */
2922: transaction_id1
2923: FROM gme_transaction_pairs
2924: WHERE transaction_id1 = mmt.transaction_id
2925: AND pair_type = v_pairs_reversal_type)
2926:
2927: UNION ALL
2932: AND transaction_source_id = v_phantom_batch_id
2933: AND transaction_source_type_id = v_txn_source_type
2934: AND NOT EXISTS ( SELECT /*+ no_unnest */
2935: transaction_id1
2936: FROM gme_transaction_pairs
2937: WHERE transaction_id1 = mmt.transaction_id
2938: AND pair_type = v_pairs_reversal_type))
2939: ORDER BY CASE p_order_by
2940: when 1 then Row_Number() over(order by transaction_quantity)
3872: l_source_line_id NUMBER;
3873: l_inventory_item_id NUMBER;
3874: l_organization_id NUMBER;
3875: l_actual_qty NUMBER;
3876: l_gme_pairs_rec gme_transaction_pairs%ROWTYPE;
3877: l_mat_dtl_rec gme_material_details%ROWTYPE;
3878: l_api_name CONSTANT VARCHAR2 (30) := 'gme_post_process';
3879: l_exists NUMBER; -- Bug 5763818
3880: transfer_error EXCEPTION; -- B4944024
3879: l_exists NUMBER; -- Bug 5763818
3880: transfer_error EXCEPTION; -- B4944024
3881: dispense_error EXCEPTION;
3882:
3883: l_gme_pairs_rec_upd gme_transaction_pairs%ROWTYPE;
3884: l_transaction_quantity NUMBER;
3885: l_transaction_quantity_upd NUMBER;
3886:
3887:
3968: || l_source_line_id);
3969: END IF;
3970:
3971: -- UPDATE transactions pair table for the reversal transaction
3972: UPDATE gme_transaction_pairs
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
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
3982: ,pair_type)
3983: VALUES (l_transaction_source_id, l_trx_source_line_id
4037: || l_transaction_id);
4038: END IF;
4039: /* Added the below loop to update the transaction only if it matches with the existing transactions
4040: and with the qty and opposite sign */
4041: FOR l_gme_pairs_rec_upd in (select * from gme_transaction_pairs where transaction_id2 = l_transaction_reference)
4042: LOOP
4043: SELECT transaction_quantity INTO l_transaction_quantity_upd
4044: FROM mtl_material_transactions t
4045: WHERE t.transaction_id = l_gme_pairs_rec_upd.transaction_id1;
4045: WHERE t.transaction_id = l_gme_pairs_rec_upd.transaction_id1;
4046:
4047: IF (l_transaction_quantity_upd = (-1) *l_transaction_quantity )AND
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 *
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
4100: || 'insert row- after update -mat_det_id: '
4101: || l_trx_source_line_id);
4102: END IF; -- for debug
4103:
4104: INSERT INTO gme_transaction_pairs
4105: (batch_id, material_detail_id
4106: ,transaction_id1, transaction_id2
4107: ,pair_type)
4108: VALUES (l_transaction_source_id, l_trx_source_line_id
4144: || l_trx_source_line_id);
4145: END IF;
4146:
4147: --INSERT a new row
4148: INSERT INTO gme_transaction_pairs
4149: (batch_id, material_detail_id
4150: ,transaction_id1, transaction_id2
4151: ,pair_type)
4152: VALUES (l_transaction_source_id, l_trx_source_line_id
4386: || l_material_detail_id);
4387: END IF;
4388:
4389: IF l_batch_id IS NOT NULL THEN
4390: DELETE FROM gme_transaction_pairs
4391: WHERE batch_id = l_batch_id;
4392: ELSIF l_material_detail_id IS NOT NULL THEN
4393: DELETE FROM gme_transaction_pairs
4394: WHERE material_detail_id = l_material_detail_id;
4389: IF l_batch_id IS NOT NULL THEN
4390: DELETE FROM gme_transaction_pairs
4391: WHERE batch_id = l_batch_id;
4392: ELSIF l_material_detail_id IS NOT NULL THEN
4393: DELETE FROM gme_transaction_pairs
4394: WHERE material_detail_id = l_material_detail_id;
4395: ELSE
4396: DELETE FROM gme_transaction_pairs
4397: WHERE batch_id = l_batch_id
4392: ELSIF l_material_detail_id IS NOT NULL THEN
4393: DELETE FROM gme_transaction_pairs
4394: WHERE material_detail_id = l_material_detail_id;
4395: ELSE
4396: DELETE FROM gme_transaction_pairs
4397: WHERE batch_id = l_batch_id
4398: AND material_detail_id = l_material_detail_id;
4399: END IF;
4400:
4745: --mtl_transaction_lot_numbers mtln,
4746: mtl_material_transactions mmt, /* Removed mtln for Bug 8347011 base bug 8219507 */
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
5604: | get_mmt_transactions
5605: |
5606: | USAGE
5607: | Gets all transactions from mmt based on transaction_id passed. Unlike get_transactions,
5608: | this procedure does not check for enteries in gme_transaction_pairs
5609: |
5610: | ARGUMENTS
5611: | p_transaction_id -- transaction_id from mmt for fetch
5612: |