[Home] [Help]
2279:
2280: -- Bug 5186993, if recount unmarking the serials and re-setting serials in MCSN.
2281: if (l_entry_status_code = 3) then
2282: unmark(l_cc_entry_id);
2283: UPDATE MTL_CC_SERIAL_NUMBERS
2284: SET
2285: UNIT_STATUS_CURRENT = DECODE((NVL(POS_ADJUSTMENT_QTY,0) -
2286: NVL(NEG_ADJUSTMENT_QTY,0)), 1, 2, -1, 1, UNIT_STATUS_CURRENT),
2287: POS_ADJUSTMENT_QTY = 0,
2611: END IF;
2612:
2613: SELECT SUM ( DECODE ( UNIT_STATUS_CURRENT, 1, 1, 0 ) )
2614: INTO l_total_serial_num_cnt
2615: FROM MTL_CC_SERIAL_NUMBERS
2616: WHERE CYCLE_COUNT_ENTRY_ID = l_cc_entry_id;
2617:
2618: IF ( l_total_serial_num_cnt <> l_count_quantity ) THEN
2619: FND_MESSAGE.SET_NAME ( 'INV',
2701: END IF;
2702: -- Bug 5186993, if recount unmarking the serials and re-setting serials in MCSN.
2703: if (l_entry_status_code = 3) then
2704: unmark(l_cc_entry_id);
2705: UPDATE MTL_CC_SERIAL_NUMBERS
2706: SET
2707: UNIT_STATUS_CURRENT = DECODE((NVL(POS_ADJUSTMENT_QTY,0) -
2708: NVL(NEG_ADJUSTMENT_QTY,0)), 1, 2, -1, 1, UNIT_STATUS_CURRENT),
2709: POS_ADJUSTMENT_QTY = 0,
4020: WHERE inventory_item_id = l_inventory_item_id
4021: AND current_organization_id = l_org_id
4022: AND serial_number IN
4023: (SELECT serial_number
4024: FROM mtl_cc_serial_numbers
4025: WHERE cycle_count_entry_id = l_cc_entry_id);
4026: END IF;
4027: END IF;
4028: END LOOP;
4451: g_cc_entry.cycle_count_entry_id := l_cycle_count_entry_id;
4452: END IF;
4453:
4454: -- Every time you calculate system quantity make sure that we update
4455: -- MTL_CC_SERIAL_NUMBERS table. So that change in system quantity is reflected
4456: -- in SERIAL_NUMBERS also
4457: -- Bug# 2386909
4458: -- Match against the LPN ID also when performing this insert statement
4459: INSERT INTO MTL_CC_SERIAL_NUMBERS
4455: -- MTL_CC_SERIAL_NUMBERS table. So that change in system quantity is reflected
4456: -- in SERIAL_NUMBERS also
4457: -- Bug# 2386909
4458: -- Match against the LPN ID also when performing this insert statement
4459: INSERT INTO MTL_CC_SERIAL_NUMBERS
4460: ( CYCLE_COUNT_ENTRY_ID,
4461: SERIAL_NUMBER,
4462: LAST_UPDATE_DATE,
4463: LAST_UPDATED_BY,
4482: AND msn.current_status = 3
4483: AND NVL ( msn.lpn_id, -99999 ) = NVL ( l_lpn_id, -99999 )
4484: AND NOT EXISTS (
4485: SELECT 'x'
4486: FROM MTL_CC_SERIAL_NUMBERS
4487: WHERE CYCLE_COUNT_ENTRY_ID = l_cycle_count_entry_id
4488: AND SERIAL_NUMBER = msn.SERIAL_NUMBER );
4489: END IF;
4490: END IF;
4787: g_cc_entry.cycle_count_entry_id := l_cycle_count_entry_id;
4788: END IF;
4789:
4790: -- Every time you calculate system quantity make sure that we update
4791: -- MTL_CC_SERIAL_NUMBERS table. So that change in system quantity is reflected
4792: -- in SERIAL_NUMBERS also
4793: -- Bug# 2386909
4794: -- Match against the LPN ID also when performing this insert statement
4795: INSERT INTO MTL_CC_SERIAL_NUMBERS
4791: -- MTL_CC_SERIAL_NUMBERS table. So that change in system quantity is reflected
4792: -- in SERIAL_NUMBERS also
4793: -- Bug# 2386909
4794: -- Match against the LPN ID also when performing this insert statement
4795: INSERT INTO MTL_CC_SERIAL_NUMBERS
4796: ( CYCLE_COUNT_ENTRY_ID,
4797: SERIAL_NUMBER,
4798: LAST_UPDATE_DATE,
4799: LAST_UPDATED_BY,
4818: AND msn.current_status = 3
4819: AND NVL ( msn.lpn_id, -99999 ) = NVL ( l_lpn_id, -99999 )
4820: AND NOT EXISTS (
4821: SELECT 'x'
4822: FROM MTL_CC_SERIAL_NUMBERS
4823: WHERE CYCLE_COUNT_ENTRY_ID = l_cycle_count_entry_id
4824: AND SERIAL_NUMBER = msn.SERIAL_NUMBER );
4825: END IF;
4826: END IF;
5213: ) THEN
5214: BEGIN
5215: SELECT MIN ( NVL ( number_of_counts, 0 ) )
5216: INTO l_number_of_counts
5217: FROM mtl_cc_serial_numbers
5218: WHERE cycle_count_entry_id = l_cycle_count_entry_id
5219: GROUP BY cycle_count_entry_id;
5220: EXCEPTION
5221: WHEN NO_DATA_FOUND THEN
5250: ) THEN
5251: BEGIN
5252: SELECT MIN ( number_of_counts )
5253: INTO l_number_of_counts
5254: FROM mtl_cc_serial_numbers
5255: WHERE cycle_count_entry_id = l_cycle_count_entry_id
5256: GROUP BY cycle_count_entry_id;
5257: EXCEPTION
5258: WHEN NO_DATA_FOUND THEN
5326: l_serial_number := g_cc_serial_entry.serial_number;
5327:
5328: SELECT COUNT ( * )
5329: INTO l_count
5330: FROM MTL_CC_SERIAL_NUMBERS
5331: WHERE serial_number = l_serial_number
5332: AND cycle_count_entry_id = l_cycle_count_entry_id;
5333:
5334: IF ( l_count > 0 ) THEN
5494: END IF;
5495: END IF;
5496: END new_serial_number;
5497:
5498: /* Deletes the serial info from mtl_cc_Serial_numbers in case of an Issue transaction */
5499: PROCEDURE delete_Serial_entry(p_serial_number IN VARCHAR2, p_cc_header_id IN NUMBER, p_cycle_count_entry_id IN NUMBER) IS
5500: BEGIN
5501:
5502: DELETE FROM mtl_cc_Serial_numbers
5498: /* Deletes the serial info from mtl_cc_Serial_numbers in case of an Issue transaction */
5499: PROCEDURE delete_Serial_entry(p_serial_number IN VARCHAR2, p_cc_header_id IN NUMBER, p_cycle_count_entry_id IN NUMBER) IS
5500: BEGIN
5501:
5502: DELETE FROM mtl_cc_Serial_numbers
5503: WHERE serial_number = p_serial_number
5504: AND cycle_count_entry_id IN
5505: (SELECT cycle_count_entry_id
5506: FROM mtl_cycle_count_entries
6756: print_debug ( 'success_flag: ' || l_success_flag );
6757: print_debug('Calling delete_Serial_entry 1');
6758: END IF;
6759:
6760: 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
6761:
6762:
6763: --If success flag is 2 or 3 then set the message for invalid
6764: --material status for the lot/serial and the item combination
6798: l_temp_locator_id := l_msn_locator_id;
6799: IF ( l_debug = 1 ) THEN
6800: print_debug('Calling delete_Serial_entry 2');
6801: END IF;
6802: 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
6803: END IF;
6804:
6805: IF ( l_debug = 1 ) THEN
6806: print_debug ( 'Calling cc_transact with the following parameters: '
6932: g_cc_serial_entry.last_update_date := SYSDATE;
6933: g_cc_serial_entry.last_updated_by := g_user_id;
6934: g_cc_serial_entry.last_update_login := g_login_id;
6935:
6936: UPDATE MTL_CC_SERIAL_NUMBERS
6937: SET last_update_date = g_cc_serial_entry.last_update_date,
6938: last_updated_by = g_cc_serial_entry.last_updated_by,
6939: last_update_login = g_cc_serial_entry.last_update_login,
6940: number_of_counts = g_cc_serial_entry.number_of_counts,
7816:
7817: CURSOR cc_multiple_entry_cursor
7818: IS
7819: SELECT *
7820: FROM mtl_cc_serial_numbers
7821: WHERE cycle_count_entry_id = g_cc_entry.cycle_count_entry_id;
7822:
7823: l_group_mark_id NUMBER;
7824: l_serial_adjustment_option NUMBER;