[Home] [Help]
1161: cii.operational_status_code
1162: FROM csi_item_instances cii,
1163: csi_item_instances_h ciih,
1164: csi_transactions ct,
1165: mtl_material_transactions mmt,
1166: csi_txn_types ctt
1167: WHERE ct.transaction_id = p_transaction_id
1168: AND ct.inv_material_transaction_id = mmt.transaction_id(+)
1169: AND ct.transaction_type_id = ctt.transaction_type_id
1545: mmt.organization_id,
1546: mmt.primary_quantity,
1547: msi.serial_number_control_code,
1548: msi.primary_unit_of_measure
1549: FROM mtl_material_transactions mmt,
1550: mtl_system_items msi
1551: WHERE mmt.transaction_id = p_mtl_txn_id
1552: AND msi.inventory_item_id = mmt.inventory_item_id
1553: AND msi.organization_id = mmt.organization_id;
3165: CURSOR get_srl_code_from_org
3166: IS
3167: SELECT DECODE (msib.serial_number_control_code,1,'NON-SERIAL',
3168: 2, 'SERIAL', 5 ,'SERIAL',6, 'NON-SERIAL','NON-SERIAL') serial_control_code
3169: FROM mtl_material_transactions mmt
3170: ,mtl_system_items_b msib
3171: WHERE mmt.transaction_id = p_mtl_transaction_id
3172: AND mmt.inventory_item_id = msib.inventory_item_id
3173: AND mmt.organization_id = msib.organization_id ;
3175: CURSOR get_srl_code_to_org
3176: IS
3177: SELECT DECODE (msib.serial_number_control_code,1,'NON-SERIAL',
3178: 2, 'SERIAL', 5 ,'SERIAL',6, 'NON-SERIAL','NON-SERIAL') serial_control_code
3179: FROM mtl_material_transactions mmt
3180: ,mtl_system_items_b msib
3181: WHERE mmt.transaction_id = p_mtl_transaction_id
3182: AND mmt.inventory_item_id = msib.inventory_item_id
3183: AND mmt.transfer_organization_id = msib.organization_id ;
3240: AND cii.serial_number is NULL
3241: AND EXISTS (
3242: SELECT 'x'
3243: FROM csi_transactions ct1,
3244: mtl_material_transactions mmt
3245: WHERE ct1.transaction_type_id in (131, 142, 143, 144)
3246: AND ct1.transaction_status_code = 'PENDING'
3247: AND mmt.transaction_id = ct1.inv_material_transaction_id
3248: AND mmt.inventory_item_id = mmt.inventory_item_id
3275: ,citdv.inventory_item_id
3276: ,citdv.transaction_quantity
3277: ,citdv.source_transaction_type
3278: FROM csi_inst_txn_details_v citdv,
3279: mtl_material_transactions mmt,
3280: csi_item_instances_h ciih
3281: WHERE mmt.inventory_item_id = c_inv_item_id
3282: AND mmt.organization_id = c_inv_org_id
3283: AND mmt.shipment_number = c_shipment_number
3317: AND cii.serial_number is NOT NULL
3318: AND EXISTS (
3319: SELECT 'x'
3320: FROM csi_transactions ct1,
3321: mtl_material_transactions mmt
3322: WHERE ct1.transaction_type_id in (131, 142, 143, 144)
3323: AND ct1.transaction_status_code = 'PENDING'
3324: AND mmt.transaction_id = ct1.inv_material_transaction_id
3325: AND mmt.inventory_item_id = mmt.inventory_item_id
3348: ,citdv.source_transaction_type
3349: ,citdv.inventory_item_id
3350: FROM csi_inst_txn_details_v citdv,
3351: csi_item_instances_h ciih,
3352: mtl_material_transactions mmt
3353: WHERE mmt.inventory_item_id = c_inv_item_id
3354: AND citdv.inv_material_transaction_id = mmt.transaction_id
3355: AND mmt.organization_id = c_inv_org_id
3356: AND mmt.shipment_number = c_shipment_number
3405: transfer_organization_id
3406: INTO l_inventory_item_id,
3407: l_shipment_number,
3408: l_xfer_organization_id
3409: FROM mtl_material_transactions
3410: WHERE transaction_id = p_material_transaction_id;
3411:
3412: SELECT source_transaction_type
3413: INTO l_src_transaction_type
3711: l_mtl_txn_date,
3712: l_inventory_item_id,
3713: l_organization_id,
3714: l_mtl_xfer_txn_id
3715: FROM mtl_material_transactions
3716: WHERE transaction_id = p_csi_txn_rec.inv_material_transaction_id;
3717:
3718: debug(' mtl_txn_type_id : '||l_mtl_type_id);
3719: debug(' mtl_src_type_id : '||l_mtl_src_type_id);