12:
13: CURSOR cur_msnt(cp_transaction_temp_id NUMBER) IS
14: SELECT msnt.* ,
15: msnt.ROWID msnt_rowid
16: FROM mtl_serial_numbers_temp msnt
17: WHERE transaction_temp_id = cp_transaction_temp_id;
18:
19: CURSOR cur_msn(cp_fm_serial_number VARCHAR2,
20: cp_to_serial_number VARCHAR2,
24: cp_length NUMBER)
25: IS
26: SELECT cost_group_id,
27: serial_number
28: FROM mtl_serial_numbers
29: WHERE serial_number
30: BETWEEN cp_fm_serial_number AND Nvl(cp_to_serial_number, cp_fm_serial_number)
31: AND Length(serial_number)=cp_length
32: AND serial_number LIKE (cp_prefix||'%')
164: END IF;
165:
166: x_return_status := fnd_api.g_ret_sts_success;
167:
168: UPDATE mtl_serial_numbers_temp
169: SET
170: transaction_temp_id = p_new_transaction_temp_id,
171: fm_serial_number = p_from_serial_number,
172: to_serial_number = p_to_serial_number
174:
175: IF (SQL%NOTFOUND) THEN
176: x_return_status := FND_API.G_RET_STS_ERROR ;
177: fnd_message.set_name('INV', 'INV_UPDATE_ERROR');
178: fnd_message.set_token('ENTITY1', 'MTL_SERIAL_NUMBERS_TEMP');
179: -- MESSAGE_TEXT = "Error Updating ENTITY1 "
180: fnd_msg_pub.add;
181: IF (l_debug = 1) THEN
182: print_debug('proc_update_msnt .. nodatafound OTHERS : ');
274: ELSE
275: SELECT 1 INTO l_onhand FROM dual
276: WHERE exists
277: (SELECT current_organization_id
278: FROM mtl_serial_numbers msn
279: WHERE (msn.lot_number = p_lot_number
280: OR (p_lot_number IS NULL AND msn.lot_number IS NULL))
281: AND (msn.revision = p_revision
282: OR (p_revision IS NULL AND msn.revision IS NULL))
308: ELSE
309: SELECT 1 INTO l_onhand FROM dual
310: WHERE exists
311: (SELECT current_organization_id
312: FROM mtl_serial_numbers msn
313: WHERE (msn.lot_number = p_lot_number
314: OR (p_lot_number IS NULL AND msn.lot_number IS NULL))
315: AND (msn.revision = p_revision
316: OR (p_revision IS NULL AND msn.revision IS NULL))
642: END IF;
643: Select cost_group_id INTO x_cost_group_id FROM (
644: SELECT mmtt.cost_group_id
645: FROM mtl_material_transactions_temp mmtt,
646: mtl_serial_numbers_temp msnt
647: WHERE mmtt.transfer_organization = p_organization_id
648: AND mmtt.transfer_subinventory = p_subinventory_code
649: AND Nvl(mmtt.transfer_to_location, -1) = Nvl(p_locator_id, -1)
650: AND mmtt.inventory_item_id = p_inventory_item_id
659: UNION
660:
661: SELECT mmtt.cost_group_id
662: FROM mtl_material_transactions_temp mmtt,
663: mtl_serial_numbers_temp msnt
664: WHERE mmtt.organization_id = p_organization_id
665: AND mmtt.subinventory_code = p_subinventory_code
666: AND Nvl(mmtt.locator_id, -1) = Nvl(p_locator_id, -1)
667: AND mmtt.inventory_item_id = p_inventory_item_id
688: Select cost_group_id INTO x_cost_group_id FROM (
689: SELECT mmtt.cost_group_id
690: FROM mtl_material_transactions_temp mmtt,
691: mtl_transaction_lots_temp mtlt,
692: mtl_serial_numbers_temp msnt
693: WHERE mmtt.transfer_organization = p_organization_id
694: AND mmtt.transfer_subinventory = p_subinventory_code
695: AND Nvl(mmtt.transfer_to_location, -1) = Nvl(p_locator_id, -1)
696: AND mmtt.inventory_item_id = p_inventory_item_id
708:
709: SELECT mmtt.cost_group_id
710: FROM mtl_material_transactions_temp mmtt,
711: mtl_transaction_lots_temp mtlt,
712: mtl_serial_numbers_temp msnt
713: WHERE mmtt.organization_id = p_organization_id
714: AND mmtt.subinventory_code = p_subinventory_code
715: AND Nvl(mmtt.locator_id, -1) = Nvl(p_locator_id, -1)
716: AND mmtt.inventory_item_id = p_inventory_item_id
838: AND ROWNUM = 1;
839: ELSE
840: SELECT msn.cost_group_id -- Loose material, serial control
841: INTO x_cost_group_id
842: FROM mtl_serial_numbers msn
843: WHERE (msn.lot_number = p_lot_number
844: OR (p_lot_number IS NULL AND msn.lot_number IS NULL))
845: AND (msn.revision = p_revision
846: OR (p_revision IS NULL AND msn.revision IS NULL))
892: WHERE ROWNUM < 2 ;
893: ELSE
894: SELECT msn.cost_group_id -- Packed material, serial control
895: INTO x_cost_group_id
896: FROM mtl_serial_numbers msn
897: WHERE (msn.lot_number = p_lot_number
898: OR (p_lot_number IS NULL AND msn.lot_number IS NULL))
899: AND (msn.revision = p_revision
900: OR (p_revision IS NULL AND msn.revision IS NULL))
1048: print_debug('proc_insert_msnt.. TSN: ' || p_to_serial_number);
1049: print_debug('proc_insert_msnt.. Txn temp id: ' || p_new_txn_temp_id);
1050: END IF;
1051:
1052: INSERT INTO mtl_serial_numbers_temp
1053: (TRANSACTION_TEMP_ID
1054: ,LAST_UPDATE_DATE
1055: ,LAST_UPDATED_BY
1056: ,CREATION_DATE
2623: l_cost_group_id NUMBER;
2624:
2625: -- For putting records in MSNT tables
2626: TYPE serial_record IS RECORD
2627: (from_serial_number mtl_serial_numbers.serial_number%TYPE,
2628: to_serial_number mtl_serial_numbers.serial_number%TYPE,
2629: cost_group_id NUMBER,
2630: update_msnt BOOLEAN);
2631: TYPE serial_table IS TABLE OF serial_record INDEX BY BINARY_INTEGER;
2624:
2625: -- For putting records in MSNT tables
2626: TYPE serial_record IS RECORD
2627: (from_serial_number mtl_serial_numbers.serial_number%TYPE,
2628: to_serial_number mtl_serial_numbers.serial_number%TYPE,
2629: cost_group_id NUMBER,
2630: update_msnt BOOLEAN);
2631: TYPE serial_table IS TABLE OF serial_record INDEX BY BINARY_INTEGER;
2632: l_serial_table serial_table;
3143: --the information in this table is copied to to lot_table
3144:
3145: -- For putting records in MTLT and MSNT tables
3146: TYPE serial_record IS RECORD
3147: (from_serial_number mtl_serial_numbers.serial_number%TYPE,
3148: to_serial_number mtl_serial_numbers.serial_number%TYPE,
3149: --lot_number mtl_serial_numbers.lot_number%TYPE,
3150: quantity NUMBER,
3151: cost_group_id NUMBER,
3144:
3145: -- For putting records in MTLT and MSNT tables
3146: TYPE serial_record IS RECORD
3147: (from_serial_number mtl_serial_numbers.serial_number%TYPE,
3148: to_serial_number mtl_serial_numbers.serial_number%TYPE,
3149: --lot_number mtl_serial_numbers.lot_number%TYPE,
3150: quantity NUMBER,
3151: cost_group_id NUMBER,
3152: new_serial_transaction_temp_id NUMBER,
3145: -- For putting records in MTLT and MSNT tables
3146: TYPE serial_record IS RECORD
3147: (from_serial_number mtl_serial_numbers.serial_number%TYPE,
3148: to_serial_number mtl_serial_numbers.serial_number%TYPE,
3149: --lot_number mtl_serial_numbers.lot_number%TYPE,
3150: quantity NUMBER,
3151: cost_group_id NUMBER,
3152: new_serial_transaction_temp_id NUMBER,
3153: --update_mtlt BOOLEAN,