[Home] [Help]
2526:
2527: -- Bug 5186993, if recount unmarking the serials and re-setting serials in MCSN.
2528: if (l_entry_status_code = 3) then
2529: unmark(l_cc_entry_id);
2530: UPDATE MTL_CC_SERIAL_NUMBERS
2531: SET
2532: UNIT_STATUS_CURRENT = DECODE((NVL(POS_ADJUSTMENT_QTY,0) -
2533: NVL(NEG_ADJUSTMENT_QTY,0)), 1, 2, -1, 1, UNIT_STATUS_CURRENT),
2534: POS_ADJUSTMENT_QTY = 0,
2858: END IF;
2859:
2860: SELECT SUM ( DECODE ( UNIT_STATUS_CURRENT, 1, 1, 0 ) )
2861: INTO l_total_serial_num_cnt
2862: FROM MTL_CC_SERIAL_NUMBERS
2863: WHERE CYCLE_COUNT_ENTRY_ID = l_cc_entry_id;
2864:
2865: IF ( l_total_serial_num_cnt <> l_count_quantity ) THEN
2866: FND_MESSAGE.SET_NAME ( 'INV',
2948: END IF;
2949: -- Bug 5186993, if recount unmarking the serials and re-setting serials in MCSN.
2950: if (l_entry_status_code = 3) then
2951: unmark(l_cc_entry_id);
2952: UPDATE MTL_CC_SERIAL_NUMBERS
2953: SET
2954: UNIT_STATUS_CURRENT = DECODE((NVL(POS_ADJUSTMENT_QTY,0) -
2955: NVL(NEG_ADJUSTMENT_QTY,0)), 1, 2, -1, 1, UNIT_STATUS_CURRENT),
2956: POS_ADJUSTMENT_QTY = 0,
4333: WHERE inventory_item_id = l_inventory_item_id
4334: AND current_organization_id = l_org_id
4335: AND serial_number IN
4336: (SELECT serial_number
4337: FROM mtl_cc_serial_numbers
4338: WHERE cycle_count_entry_id = l_cc_entry_id);
4339: END IF;
4340: END IF;
4341: END LOOP;
4786: g_cc_entry.cycle_count_entry_id := l_cycle_count_entry_id;
4787: END IF;
4788:
4789: -- Every time you calculate system quantity make sure that we update
4790: -- MTL_CC_SERIAL_NUMBERS table. So that change in system quantity is reflected
4791: -- in SERIAL_NUMBERS also
4792: -- Bug# 2386909
4793: -- Match against the LPN ID also when performing this insert statement
4794:
4792: -- Bug# 2386909
4793: -- Match against the LPN ID also when performing this insert statement
4794:
4795: -- added index hint /*+ index(MSN MTL_SERIAL_NUMBERS_N2) */ in below SQL query, for Bug 12608056
4796: INSERT INTO MTL_CC_SERIAL_NUMBERS
4797: ( CYCLE_COUNT_ENTRY_ID,
4798: SERIAL_NUMBER,
4799: LAST_UPDATE_DATE,
4800: LAST_UPDATED_BY,
4820: AND msn.current_status = 3
4821: AND NVL ( msn.lpn_id, -99999 ) = NVL ( l_lpn_id, -99999 )
4822: AND NOT EXISTS (
4823: SELECT 'x'
4824: FROM MTL_CC_SERIAL_NUMBERS
4825: WHERE CYCLE_COUNT_ENTRY_ID = l_cycle_count_entry_id
4826: AND SERIAL_NUMBER = msn.SERIAL_NUMBER );
4827: END IF;
4828: END IF;
5312: ) THEN
5313: BEGIN
5314: SELECT MIN ( NVL ( number_of_counts, 0 ) )
5315: INTO l_number_of_counts
5316: FROM mtl_cc_serial_numbers
5317: WHERE cycle_count_entry_id = l_cycle_count_entry_id
5318: GROUP BY cycle_count_entry_id;
5319: EXCEPTION
5320: WHEN NO_DATA_FOUND THEN
5349: ) THEN
5350: BEGIN
5351: SELECT MIN ( number_of_counts )
5352: INTO l_number_of_counts
5353: FROM mtl_cc_serial_numbers
5354: WHERE cycle_count_entry_id = l_cycle_count_entry_id
5355: GROUP BY cycle_count_entry_id;
5356: EXCEPTION
5357: WHEN NO_DATA_FOUND THEN
5425: l_serial_number := g_cc_serial_entry.serial_number;
5426:
5427: SELECT COUNT ( * )
5428: INTO l_count
5429: FROM MTL_CC_SERIAL_NUMBERS
5430: WHERE serial_number = l_serial_number
5431: AND cycle_count_entry_id = l_cycle_count_entry_id;
5432:
5433: IF ( l_count > 0 ) THEN
5593: END IF;
5594: END IF;
5595: END new_serial_number;
5596:
5597: /* Deletes the serial info from mtl_cc_Serial_numbers in case of an Issue transaction */
5598: PROCEDURE delete_Serial_entry(p_serial_number IN VARCHAR2, p_cc_header_id IN NUMBER, p_cycle_count_entry_id IN NUMBER) IS
5599: BEGIN
5600:
5601: DELETE FROM mtl_cc_Serial_numbers
5597: /* Deletes the serial info from mtl_cc_Serial_numbers in case of an Issue transaction */
5598: PROCEDURE delete_Serial_entry(p_serial_number IN VARCHAR2, p_cc_header_id IN NUMBER, p_cycle_count_entry_id IN NUMBER) IS
5599: BEGIN
5600:
5601: DELETE FROM mtl_cc_Serial_numbers
5602: WHERE serial_number = p_serial_number
5603: AND cycle_count_entry_id IN
5604: (SELECT cycle_count_entry_id
5605: FROM mtl_cycle_count_entries
6862: print_debug ( 'success_flag: ' || l_success_flag );
6863: print_debug('Calling delete_Serial_entry 1');
6864: END IF;
6865:
6866: delete_serial_entry(l_serial_number,l_cc_header_id,l_cycle_count_entry_id); --3595723 Delete the serial info from mtl_cc_Serial_numbers
6867:
6868:
6869: --If success flag is 2 or 3 then set the message for invalid
6870: --material status for the lot/serial and the item combination
6907: l_temp_locator_id := l_msn_locator_id;
6908: IF ( l_debug = 1 ) THEN
6909: print_debug('Calling delete_Serial_entry 2');
6910: END IF;
6911: delete_serial_entry(l_serial_number,l_cc_header_id,l_cycle_count_entry_id); --3595723 Delete the serial info from mtl_cc_Serial_numbers
6912: END IF;
6913:
6914: IF ( l_debug = 1 ) THEN
6915: print_debug ( 'Calling cc_transact with the following parameters: '
7041: g_cc_serial_entry.last_update_date := SYSDATE;
7042: g_cc_serial_entry.last_updated_by := g_user_id;
7043: g_cc_serial_entry.last_update_login := g_login_id;
7044:
7045: UPDATE MTL_CC_SERIAL_NUMBERS
7046: SET last_update_date = g_cc_serial_entry.last_update_date,
7047: last_updated_by = g_cc_serial_entry.last_updated_by,
7048: last_update_login = g_cc_serial_entry.last_update_login,
7049: number_of_counts = g_cc_serial_entry.number_of_counts,
8012:
8013: CURSOR cc_multiple_entry_cursor
8014: IS
8015: SELECT *
8016: FROM mtl_cc_serial_numbers
8017: WHERE cycle_count_entry_id = g_cc_entry.cycle_count_entry_id;
8018:
8019: l_group_mark_id NUMBER;
8020: l_serial_adjustment_option NUMBER;
9356: /*
9357: print_debug('Checking schduled serial CCs..' );
9358: SELECT COUNT(*)
9359: INTO x_scheduled_qty
9360: FROM mtl_serial_numbers msn, mtl_cc_serial_numbers mcsn,
9361: mtl_material_statuses_vl mms, mtl_cycle_count_entries mcce
9362: WHERE msn.inventory_item_id = p_inventory_item_id
9363: AND msn.current_organization_id = p_organization_id
9364: AND msn.current_status IN (1, 3)
10331:
10332: This procedure will do the processing for a cycle count with header details as "Multiple Per Request"
10333: Count and "Quantity Only" Detail in which the count quantity is equal to system quantity.
10334:
10335: If count quantity is equal to system quantity, the relevant tables like mtl_cc_serial_numbers,
10336: mtl_serial_numbers, mtl_cycle_count_entries will be updated and the out parameter x_return_result will
10337: have the value 1.
10338:
10339: In case count quantity is not equal to system quantity (or there are other discrepancies), this procedure
10387:
10388: SELECT COUNT(*)
10389: INTO l_count_present_qty
10390: FROM mtl_serial_numbers msn,
10391: mtl_cc_serial_numbers mccsn
10392: WHERE mccsn.cycle_count_entry_id = g_cc_entry.cycle_count_entry_id
10393: AND msn.serial_number = mccsn.serial_number
10394: AND msn.current_organization_id = g_cc_entry.organization_id
10395: AND msn.inventory_item_id = g_cc_entry.inventory_item_id
10439: END IF;
10440:
10441: BEGIN
10442: -- updating the serial records that have been counted as present
10443: UPDATE mtl_cc_serial_numbers
10444: SET last_update_date = SYSDATE,
10445: last_updated_by = g_user_id,
10446: last_update_login = g_login_id,
10447: number_of_counts = NVL(number_of_counts, 0) + 1,
10453: neg_adjustment_qty = 0
10454: WHERE cycle_count_entry_id = g_cc_entry.cycle_count_entry_id;
10455:
10456: IF l_debug = 1 THEN
10457: print_debug('Number of records updated in MTL_CC_SERIAL_NUMBERS table: ' || SQL%ROWCOUNT);
10458: END IF;
10459:
10460: -- Unmarking the serials that are marked as present
10461: UPDATE mtl_serial_numbers msn
10463: WHERE msn.current_organization_id = g_cc_entry.organization_id
10464: AND msn.inventory_item_id = g_cc_entry.inventory_item_id
10465: AND EXISTS
10466: (SELECT 1
10467: FROM mtl_cc_serial_numbers mccsn
10468: WHERE mccsn.cycle_count_entry_id = g_cc_entry.cycle_count_entry_id
10469: AND mccsn.serial_number = msn.serial_number
10470: )
10471: AND msn.group_mark_id = 1;