[Home] [Help]
194: WHERE move_order_line_id = p_line_id;
195:
196: CURSOR serial_number_csr IS
197: SELECT fm_serial_number, to_serial_number
198: FROM mtl_serial_numbers_temp
199: WHERE transaction_temp_id = l_transaction_temp_id;
200:
201: CURSOR c_mtrh IS
202: SELECT request_number, grouping_rule_id
1193: BEGIN
1194: p_success := TRUE;
1195:
1196: -- Delete predefined serial numbers
1197: DELETE mtl_serial_numbers
1198: WHERE group_mark_id = p_trx_header_id
1199: AND current_status = 6;
1200:
1201: -- Unmark serial numbers
1198: WHERE group_mark_id = p_trx_header_id
1199: AND current_status = 6;
1200:
1201: -- Unmark serial numbers
1202: UPDATE mtl_serial_numbers
1203: SET group_mark_id = NULL
1204: , line_mark_id = NULL
1205: , lot_line_mark_id = NULL
1206: WHERE group_mark_id = p_trx_header_id;
1205: , lot_line_mark_id = NULL
1206: WHERE group_mark_id = p_trx_header_id;
1207:
1208: -- Delete lot and serial records from temp tables
1209: DELETE mtl_serial_numbers_temp
1210: WHERE group_header_id = p_trx_header_id;
1211:
1212: DELETE mtl_transaction_lots_temp
1213: WHERE group_header_id = p_trx_header_id;
1237: CURSOR serial_temp_csr(trx_temp_id NUMBER) IS
1238: SELECT fm_serial_number
1239: , to_serial_number
1240: , group_header_id
1241: FROM mtl_serial_numbers_temp
1242: WHERE transaction_temp_id = trx_temp_id;
1243:
1244: l_fm_serial_number VARCHAR2(30);
1245: l_to_serial_number VARCHAR2(30);
1275: EXIT WHEN lot_temp_csr%NOTFOUND;
1276:
1277: SELECT COUNT(*)
1278: INTO l_serial_count
1279: FROM mtl_serial_numbers_temp
1280: WHERE transaction_temp_id = l_serial_temp_id;
1281:
1282: IF (l_serial_count > 0) THEN
1283: OPEN serial_temp_csr(l_serial_temp_id);
1286: FETCH serial_temp_csr INTO l_fm_serial_number, l_to_serial_number, l_header_id;
1287: EXIT WHEN serial_temp_csr%NOTFOUND;
1288:
1289: /* Bug #2798218
1290: * While allocating serials, the group_mark_id column in mtl_serial_numbers is always
1291: * populated with the value of MMTT.transaction_temp_id (for a serial controlled item)
1292: * or MTLT.serial_transaction_temp_id (for a lot and serial controlled item), whether
1293: * the serials are allocated by the system or the manually by the user in the serial entry block
1294: * While unmarking the serials, always use transaction_temp_id or serial_transaction_temp_id
1293: * the serials are allocated by the system or the manually by the user in the serial entry block
1294: * While unmarking the serials, always use transaction_temp_id or serial_transaction_temp_id
1295: * and do not use mmtt.transaction_header_id in the WHERE clause of the UPDATE statement
1296: */
1297: UPDATE mtl_serial_numbers
1298: SET line_mark_id = unmarked_value
1299: , group_mark_id = unmarked_value
1300: , lot_line_mark_id = unmarked_value
1301: WHERE (group_mark_id = l_serial_temp_id OR group_mark_id = l_header_id) --Bug#6009436.
1304: AND LENGTH(serial_number) = LENGTH(NVL(l_fm_serial_number, serial_number));
1305: END LOOP;
1306: END IF;
1307:
1308: DELETE mtl_serial_numbers_temp
1309: WHERE transaction_temp_id = l_serial_temp_id;
1310: END LOOP;
1311:
1312: CLOSE lot_temp_csr;
1316: ELSE
1317: --inv_debug.message('ssia', 'lot_count = ' || l_lot_count);
1318: SELECT COUNT(*)
1319: INTO l_serial_count
1320: FROM mtl_serial_numbers_temp
1321: WHERE transaction_temp_id = p_trx_tmp_id;
1322:
1323: print_debug('serial count: ' || l_serial_count);
1324:
1334: EXIT WHEN serial_temp_csr%NOTFOUND;
1335:
1336: --inv_debug.message('ssia', 'l_header_id is ' || l_header_id);
1337: --inv_debug.message('ssia', 'update mtl_serial_number');
1338: UPDATE mtl_serial_numbers
1339: SET line_mark_id = unmarked_value
1340: , group_mark_id = unmarked_value
1341: , lot_line_mark_id = unmarked_value
1342: WHERE ( group_mark_id = p_trx_tmp_id OR group_mark_id = l_header_id ) --Bug#6009436
1347:
1348: CLOSE serial_temp_csr;
1349: END IF;
1350:
1351: DELETE mtl_serial_numbers_temp
1352: WHERE transaction_temp_id = p_trx_tmp_id;
1353: END IF;
1354: EXCEPTION
1355: WHEN NO_DATA_FOUND THEN