[Home] [Help]
51: mmt.revision mtl_revision,
52: to_char(null) lot_number,
53: to_char(mmt.transaction_date,'dd-mm-yy hh24:mi:ss') mtl_txn_char_date
54: FROM mtl_unit_transactions mut,
55: mtl_material_transactions mmt,
56: mtl_transaction_types mtt
57: WHERE mut.serial_number = p_serial_number
58: AND mut.inventory_item_id = p_item_id
59: AND mmt.transaction_id = mut.transaction_id
77: mtln.lot_number lot_number,
78: to_char(mmt.transaction_date,'dd-mm-yy hh24:mi:ss') mtl_txn_char_date
79: FROM mtl_unit_transactions mut,
80: mtl_transaction_lot_numbers mtln,
81: mtl_material_transactions mmt,
82: mtl_transaction_types mtt
83: WHERE mut.serial_number = p_serial_number
84: AND mut.inventory_item_id = p_item_id
85: AND mtln.organization_id = mut.organization_id
103: mmt.trx_source_line_id trx_source_line_id,
104: mmt.transaction_source_id mtl_source_id,
105: mmt.source_project_id source_project_id,
106: mmt.source_task_id source_task_id
107: FROM mtl_material_transactions mmt
108: WHERE mmt.transaction_id = p_mtl_txn_id;
109:
110: CURSOR from_sixfer_cur(
111: p_mtl_txn_id in number,
120: abs(mmt.primary_quantity) quantity,
121: mmt.transaction_date mtl_txn_date,
122: mmt.transaction_id mtl_txn_id,
123: mmt.trx_source_line_id trx_source_line_id
124: FROM mtl_material_transactions mmt
125: WHERE (mmt.transaction_id = p_mtl_txn_id
126: OR
127: mmt.transaction_id = p_mtl_xfer_txn_id)
128: AND mmt.transaction_quantity < 0
136: abs(mtln.primary_quantity) quantity,
137: mmt.transaction_date mtl_txn_date,
138: mmt.transaction_id mtl_txn_id,
139: mmt.trx_source_line_id trx_source_line_id
140: FROM mtl_material_transactions mmt,
141: mtl_transaction_lot_numbers mtln
142: WHERE (mmt.transaction_id = p_mtl_txn_id
143: OR
144: mmt.transaction_id = p_mtl_xfer_txn_id)
3336: l_mtl_action_id,
3337: l_mtl_source_type_id,
3338: l_mtl_type_class,
3339: l_mtl_txn_qty
3340: FROM mtl_material_transactions mmt,
3341: mtl_transaction_types mtt
3342: WHERE mmt.transaction_id = q_rec.source_id
3343: AND mtt.transaction_type_id = mmt.transaction_type_id;
3344:
3386: mtt.type_class mtl_type_class,
3387: mmt.transaction_quantity mtl_txn_qty
3388: FROM mtl_system_items msi,
3389: mtl_transaction_types mtt,
3390: mtl_material_transactions mmt
3391: WHERE mmt.transaction_source_type_id = 5
3392: AND mmt.transaction_action_id IN (1, 27, 31, 32, 33, 34)
3393: AND mmt.transaction_date > p_freeze_date
3394: AND mmt.transaction_source_id = pc_wip_job_id
3839: l_wip_job_id,
3840: l_order_line_id,
3841: l_err_txn_date,
3842: l_mtl_creation_date
3843: FROM mtl_material_transactions
3844: WHERE transaction_id = l_mtl_txn_id_tab(ind);
3845: EXCEPTION
3846: WHEN no_data_found THEN
3847: RAISE skip_error;
4101: l_inv_org_id,
4102: l_err_txn_source_type_id,
4103: l_err_txn_action_id,
4104: l_err_txn_date
4105: FROM mtl_material_transactions
4106: WHERE transaction_id = l_mtl_txn_id_tab(ind);
4107: EXCEPTION
4108: WHEN no_data_found THEN
4109: RAISE skip_error;
6478: abs(mmt.primary_quantity) quantity,
6479: mmt.transaction_date mtl_txn_date,
6480: mmt.transaction_id mtl_txn_id,
6481: mmt.trx_source_line_id trx_source_line_id
6482: FROM mtl_material_transactions mmt
6483: WHERE mmt.transaction_id = p_mtl_txn_id
6484: AND p_lot_code = 1
6485: UNION
6486: SELECT mmt.inventory_item_id item_id,
6492: abs(mtln.primary_quantity) quantity,
6493: mmt.transaction_date mtl_txn_date,
6494: mmt.transaction_id mtl_txn_id,
6495: mmt.trx_source_line_id trx_source_line_id
6496: FROM mtl_material_transactions mmt,
6497: mtl_transaction_lot_numbers mtln
6498: WHERE mmt.transaction_id = p_mtl_txn_id
6499: AND mtln.transaction_id = mmt.transaction_id
6500: AND p_lot_code <> 1;
6539: SELECT transaction_date,
6540: trx_source_line_id
6541: INTO l_mtl_txn_date,
6542: l_order_line_id
6543: FROM mtl_material_transactions
6544: WHERE transaction_id = txn_rec.mtl_txn_id;
6545:
6546: l_txn_rec.transaction_id := fnd_api.g_miss_num;
6547: l_txn_rec.transaction_type_id := correction_txn_type_id;
7420: x_return_status := fnd_api.g_ret_sts_success;
7421:
7422: SELECT transfer_transaction_id
7423: INTO l_mtl_xfer_txn_id
7424: FROM mtl_material_transactions
7425: WHERE transaction_id = p_diag_txn_rec.mtl_txn_id;
7426:
7427: FOR inv_rec IN from_sixfer_cur(
7428: p_diag_txn_rec.mtl_txn_id,
7957: -- and stamp it in in_transit_order_line_id
7958: BEGIN
7959: SELECT trx_source_line_id
7960: INTO l_instance_rec.in_transit_order_line_id
7961: FROM mtl_material_transactions
7962: WHERE transaction_action_id = 21
7963: AND transaction_source_type_id = 8
7964: AND (shipment_number, inventory_item_id) IN (
7965: SELECT shipment_number, inventory_item_id
7962: WHERE transaction_action_id = 21
7963: AND transaction_source_type_id = 8
7964: AND (shipment_number, inventory_item_id) IN (
7965: SELECT shipment_number, inventory_item_id
7966: FROM mtl_material_transactions
7967: WHERE transaction_id = inv_rec.mtl_txn_id)
7968: AND rownum = 1;
7969: EXCEPTION
7970: WHEN no_data_found THEN
8040:
8041: BEGIN
8042: SELECT trx_source_line_id
8043: INTO l_instance_rec.in_transit_order_line_id
8044: FROM mtl_material_transactions
8045: WHERE transaction_action_id = 21
8046: AND transaction_source_type_id = 8
8047: AND (shipment_number, inventory_item_id) IN (
8048: SELECT shipment_number, inventory_item_id
8045: WHERE transaction_action_id = 21
8046: AND transaction_source_type_id = 8
8047: AND (shipment_number, inventory_item_id) IN (
8048: SELECT shipment_number, inventory_item_id
8049: FROM mtl_material_transactions
8050: WHERE transaction_id = inv_rec.mtl_txn_id)
8051: AND rownum = 1;
8052: EXCEPTION
8053: WHEN no_data_found THEN
8142: abs(mmt.primary_quantity) quantity,
8143: mmt.transaction_date mtl_txn_date,
8144: mmt.transaction_id mtl_txn_id,
8145: mmt.trx_source_line_id trx_source_line_id
8146: FROM mtl_material_transactions mmt
8147: WHERE mmt.transaction_id = p_mtl_txn_id
8148: AND p_lot_code = 1
8149: UNION
8150: SELECT mmt.inventory_item_id item_id,
8156: abs(mtln.primary_quantity) quantity,
8157: mmt.transaction_date mtl_txn_date,
8158: mmt.transaction_id mtl_txn_id,
8159: mmt.trx_source_line_id trx_source_line_id
8160: FROM mtl_material_transactions mmt,
8161: mtl_transaction_lot_numbers mtln
8162: WHERE mmt.transaction_id = p_mtl_txn_id
8163: AND mtln.transaction_id = mmt.transaction_id
8164: AND p_lot_code <> 1;
8716: mut.transaction_id mtl_txn_id,
8717: mut.transaction_date mtl_txn_date,
8718: mmt.trx_source_line_id rma_line_id
8719: FROM mtl_unit_transactions mut,
8720: mtl_material_transactions mmt
8721: WHERE mut.serial_number = p_serial
8722: AND mut.inventory_item_id = p_item_id
8723: AND mut.transaction_date > p_freeze_date
8724: AND mmt.transaction_id = mut.transaction_id
8729: mtln.transaction_date mtl_txn_date,
8730: mmt.trx_source_line_id rma_line_id
8731: FROM mtl_unit_transactions mut,
8732: mtl_transaction_lot_numbers mtln,
8733: mtl_material_transactions mmt
8734: WHERE mut.serial_number = p_serial
8735: AND mut.inventory_item_id = p_item_id
8736: AND mtln.serial_transaction_id = mut.transaction_id
8737: AND mtln.transaction_date > p_freeze_date
9359: l_mtl_txn_id number;
9360:
9361: CURSOR mmt_cur(p_line_id in number, p_item_id in number, p_organization_id in number) IS
9362: SELECT transaction_id
9363: FROM mtl_material_transactions
9364: WHERE transaction_type_id = 15 -- RMA Transaction
9365: AND inventory_item_id = p_item_id
9366: AND organization_id = p_organization_id
9367: AND trx_source_line_id = p_line_id
9539: Begin
9540: Begin
9541: select mmt.subinventory_code,mmt.locator_id,mmt.organization_id,mmt.revision
9542: into v_subinv,v_locator_id,v_org_id,v_revision
9543: from MTL_MATERIAL_TRANSACTIONS mmt
9544: where mmt.transaction_id = (select max(transaction_id)
9545: from MTL_UNIT_TRANSACTIONS
9546: where serial_number = csi_rec.serial_number
9547: and inventory_item_id = csi_rec.inventory_item_id
10028: IF revision_mig(i) is null and order_line_id_mig(i) is not null THEN
10029: Begin
10030: select revision
10031: into revision_mig(i)
10032: from MTL_MATERIAL_TRANSACTIONS
10033: where transaction_type_id = 33
10034: and transaction_action_id = 1
10035: and trx_source_line_id = order_line_id_mig(i)
10036: and rownum < 2;
11246: CURSOR CSI_MAT_ERROR IS
11247: select cii.transaction_error_id,
11248: mmt.inventory_item_id,mmt.organization_id
11249: from CSI_TXN_ERRORS cii,
11250: MTL_MATERIAL_TRANSACTIONS mmt
11251: where cii.processed_flag in ('E', 'R')
11252: and cii.inv_material_transaction_id is not null
11253: and mmt.transaction_id = cii.inv_material_transaction_id;
11254: --
12289: select inventory_item_id,organization_id,revision,
12290: subinventory_code,locator_id,abs(primary_quantity) transaction_quantity
12291: into inventory_item_id_mig(i),organization_id_mig(i),revision_mig(i),
12292: subinv_mig(i),locator_id_mig(i),quantity_mig(i)
12293: from MTL_MATERIAL_TRANSACTIONS
12294: where transaction_id = mat_txn_id_mig(i)
12295: and ( (transaction_type_id = 33) OR
12296: (transaction_source_type_id = 5 AND transaction_quantity < 0) );
12297: Exception
13758: Begin
13759: select count(*)
13760: into l_error_count
13761: from CSI_TXN_ERRORS cii,
13762: MTL_MATERIAL_TRANSACTIONS mmt
13763: where cii.inv_material_transaction_id is not null
13764: and cii.inv_material_transaction_id = mmt.transaction_id
13765: and cii.processed_flag in ('E','R')
13766: and mmt.inventory_item_id = inventory_item_id_mig(i)
14398: mmt.transaction_action_id,
14399: mmt.transaction_source_type_id,
14400: mmt.transaction_id
14401: FROM csi_txn_errors cii,
14402: mtl_material_transactions mmt
14403: WHERE cii.processed_flag in ('E', 'R')
14404: AND cii.inv_material_transaction_id is not null
14405: AND mmt.transaction_id = cii.inv_material_transaction_id;
14406:
14618: Begin
14619: select count(*)
14620: into l_error_count
14621: from CSI_TXN_ERRORS cii,
14622: MTL_MATERIAL_TRANSACTIONS mmt
14623: where cii.inv_material_transaction_id is not null
14624: and cii.inv_material_transaction_id = mmt.transaction_id
14625: and cii.processed_flag in ('E','R')
14626: and mmt.inventory_item_id = inventory_item_id_mig(i)
14981: mmt.transaction_quantity,
14982: mmt.trx_source_line_id,
14983: mmt.revision
14984: FROM csi_txn_errors cii,
14985: mtl_material_transactions mmt
14986: WHERE cii.processed_flag = 'E'
14987: AND cii.inv_material_transaction_id is not null
14988: AND mmt.transaction_id = cii.inv_material_transaction_id
14989: AND mmt.transaction_type_id = 15; -- RMA
15382: oel.sold_to_org_id owner_acct,
15383: oel.ordered_quantity ordered_qty,
15384: oel.order_quantity_uom ordered_uom,
15385: hca.party_id party_id
15386: FROM csi_txn_errors cte,mtl_material_transactions mmt,mtl_system_items msi,oe_order_lines_all oel,hz_cust_accounts hca
15387: WHERE cte.processed_flag in ('E', 'R')
15388: AND cte.transaction_type_id = 53
15389: AND cte.inv_material_transaction_id = mmt.transaction_id
15390: AND msi.inventory_item_id = mmt.inventory_item_id
15697: l_mtl_action_id,
15698: l_mtl_source_type_id,
15699: l_mtl_type_class,
15700: l_mtl_txn_qty
15701: FROM mtl_material_transactions mmt,
15702: mtl_transaction_types mtt
15703: WHERE mmt.transaction_id = err_rec.inv_material_transaction_id
15704: AND mtt.transaction_type_id = mmt.transaction_type_id;
15705:
16349: FOR fs_rec IN fs_cur
16350: LOOP
16351: SELECT creation_date
16352: INTO l_creation_date
16353: FROM mtl_material_transactions
16354: WHERE transaction_id = fs_rec.mtl_txn_id;
16355:
16356: UPDATE csi_ii_forward_sync_temp
16357: SET mtl_txn_creation_date = l_creation_date
16368: log(date_time_stamp||'creating mmt trigger csi_block_mat_txn_trg');
16369:
16370: EXECUTE IMMEDIATE
16371: 'CREATE OR REPLACE TRIGGER CSI_BLOCK_MAT_TXN_TRG
16372: BEFORE INSERT ON MTL_MATERIAL_TRANSACTIONS
16373: REFERENCING NEW AS NEW OLD AS OLD
16374: FOR EACH ROW
16375: DECLARE
16376: v_nl_trackable VARCHAR2(1);