[Home] [Help]
182: WHERE move_order_line_id = p_line_id;
183:
184: CURSOR serial_number_csr IS
185: SELECT fm_serial_number, to_serial_number
186: FROM mtl_serial_numbers_temp
187: WHERE transaction_temp_id = l_transaction_temp_id;
188:
189: CURSOR c_mtrh IS
190: SELECT request_number, grouping_rule_id
1061: BEGIN
1062: p_success := TRUE;
1063:
1064: -- Delete predefined serial numbers
1065: DELETE mtl_serial_numbers
1066: WHERE group_mark_id = p_trx_header_id
1067: AND current_status = 6;
1068:
1069: -- Unmark serial numbers
1066: WHERE group_mark_id = p_trx_header_id
1067: AND current_status = 6;
1068:
1069: -- Unmark serial numbers
1070: UPDATE mtl_serial_numbers
1071: SET group_mark_id = NULL
1072: , line_mark_id = NULL
1073: , lot_line_mark_id = NULL
1074: WHERE group_mark_id = p_trx_header_id;
1073: , lot_line_mark_id = NULL
1074: WHERE group_mark_id = p_trx_header_id;
1075:
1076: -- Delete lot and serial records from temp tables
1077: DELETE mtl_serial_numbers_temp
1078: WHERE group_header_id = p_trx_header_id;
1079:
1080: DELETE mtl_transaction_lots_temp
1081: WHERE group_header_id = p_trx_header_id;
1105: CURSOR serial_temp_csr(trx_temp_id NUMBER) IS
1106: SELECT fm_serial_number
1107: , to_serial_number
1108: , group_header_id
1109: FROM mtl_serial_numbers_temp
1110: WHERE transaction_temp_id = trx_temp_id;
1111:
1112: l_fm_serial_number VARCHAR2(30);
1113: l_to_serial_number VARCHAR2(30);
1143: EXIT WHEN lot_temp_csr%NOTFOUND;
1144:
1145: SELECT COUNT(*)
1146: INTO l_serial_count
1147: FROM mtl_serial_numbers_temp
1148: WHERE transaction_temp_id = l_serial_temp_id;
1149:
1150: IF (l_serial_count > 0) THEN
1151: OPEN serial_temp_csr(l_serial_temp_id);
1154: FETCH serial_temp_csr INTO l_fm_serial_number, l_to_serial_number, l_header_id;
1155: EXIT WHEN serial_temp_csr%NOTFOUND;
1156:
1157: /* Bug #2798218
1158: * While allocating serials, the group_mark_id column in mtl_serial_numbers is always
1159: * populated with the value of MMTT.transaction_temp_id (for a serial controlled item)
1160: * or MTLT.serial_transaction_temp_id (for a lot and serial controlled item), whether
1161: * the serials are allocated by the system or the manually by the user in the serial entry block
1162: * While unmarking the serials, always use transaction_temp_id or serial_transaction_temp_id
1161: * the serials are allocated by the system or the manually by the user in the serial entry block
1162: * While unmarking the serials, always use transaction_temp_id or serial_transaction_temp_id
1163: * and do not use mmtt.transaction_header_id in the WHERE clause of the UPDATE statement
1164: */
1165: UPDATE mtl_serial_numbers
1166: SET line_mark_id = unmarked_value
1167: , group_mark_id = unmarked_value
1168: , lot_line_mark_id = unmarked_value
1169: WHERE (group_mark_id = l_serial_temp_id OR group_mark_id = l_header_id) --Bug#6009436.
1172: AND LENGTH(serial_number) = LENGTH(NVL(l_fm_serial_number, serial_number));
1173: END LOOP;
1174: END IF;
1175:
1176: DELETE mtl_serial_numbers_temp
1177: WHERE transaction_temp_id = l_serial_temp_id;
1178: END LOOP;
1179:
1180: CLOSE lot_temp_csr;
1184: ELSE
1185: --inv_debug.message('ssia', 'lot_count = ' || l_lot_count);
1186: SELECT COUNT(*)
1187: INTO l_serial_count
1188: FROM mtl_serial_numbers_temp
1189: WHERE transaction_temp_id = p_trx_tmp_id;
1190:
1191: print_debug('serial count: ' || l_serial_count);
1192:
1202: EXIT WHEN serial_temp_csr%NOTFOUND;
1203:
1204: --inv_debug.message('ssia', 'l_header_id is ' || l_header_id);
1205: --inv_debug.message('ssia', 'update mtl_serial_number');
1206: UPDATE mtl_serial_numbers
1207: SET line_mark_id = unmarked_value
1208: , group_mark_id = unmarked_value
1209: , lot_line_mark_id = unmarked_value
1210: WHERE ( group_mark_id = p_trx_tmp_id OR group_mark_id = l_header_id ) --Bug#6009436
1215:
1216: CLOSE serial_temp_csr;
1217: END IF;
1218:
1219: DELETE mtl_serial_numbers_temp
1220: WHERE transaction_temp_id = p_trx_tmp_id;
1221: END IF;
1222: EXCEPTION
1223: WHEN NO_DATA_FOUND THEN