eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.WMS_TASK_LOAD dependencies on MTL_SERIAL_NUMBERS

Line 383: (lot_number mtl_serial_numbers.lot_number%TYPE,

379: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
380: l_lpn_id NUMBER;
381:
382: TYPE lot_record_type IS RECORD
383: (lot_number mtl_serial_numbers.lot_number%TYPE,
384: primary_quantity NUMBER,
385: delta_primary_quantity NUMBER,
386: secondary_quantity NUMBER,
387: delta_secondary_quantity NUMBER,

Line 394: (serial_number mtl_serial_numbers.serial_number%TYPE,

390: update_mtlt BOOLEAN,
391: delete_mtlt BOOLEAN);
392:
393: TYPE serial_record_type IS RECORD
394: (serial_number mtl_serial_numbers.serial_number%TYPE,
395: lot_number mtl_serial_numbers.lot_number%TYPE,
396: transaction_temp_id NUMBER,
397: delete_msnt BOOLEAN);
398:

Line 395: lot_number mtl_serial_numbers.lot_number%TYPE,

391: delete_mtlt BOOLEAN);
392:
393: TYPE serial_record_type IS RECORD
394: (serial_number mtl_serial_numbers.serial_number%TYPE,
395: lot_number mtl_serial_numbers.lot_number%TYPE,
396: transaction_temp_id NUMBER,
397: delete_msnt BOOLEAN);
398:
399: TYPE lot_table_type IS TABLE OF lot_record_type INDEX BY BINARY_INTEGER;

Line 423: FROM mtl_serial_numbers_temp

419: FOR UPDATE;
420:
421: CURSOR msnt_cursor(v_transaction_temp_id NUMBER) IS
422: SELECT fm_serial_number
423: FROM mtl_serial_numbers_temp
424: WHERE transaction_temp_id = v_transaction_temp_id
425: ORDER BY fm_serial_number
426: FOR UPDATE;
427:

Line 779: UPDATE mtl_serial_numbers_temp

775: IF (l_debug = 1) THEN
776: mydebug(j || 'Transferring Serial Number: ' || l_serial_table1(j).serial_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
777: END IF;
778:
779: UPDATE mtl_serial_numbers_temp
780: SET transaction_temp_id = p_transaction_temp_id2,
781: last_update_date = Sysdate,
782: last_updated_by = p_user_id
783: WHERE transaction_temp_id = p_transaction_temp_id1

Line 791: DELETE FROM mtl_serial_numbers_temp

787: IF (l_debug = 1) THEN
788: mydebug(j || 'Deleting Serial Number: ' || l_serial_table1(j).serial_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
789: END IF;
790:
791: DELETE FROM mtl_serial_numbers_temp
792: WHERE transaction_temp_id = p_transaction_temp_id1
793: AND fm_serial_number = l_serial_table1(j).serial_number;
794:
795: -- unmark serial

Line 796: UPDATE mtl_serial_numbers

792: WHERE transaction_temp_id = p_transaction_temp_id1
793: AND fm_serial_number = l_serial_table1(j).serial_number;
794:
795: -- unmark serial
796: UPDATE mtl_serial_numbers
797: SET group_mark_id = NULL,
798: last_update_date = Sysdate,
799: last_updated_by = p_user_id
800: WHERE serial_number = l_serial_table1(j).serial_number

Line 988: UPDATE mtl_serial_numbers_temp

984: IF (l_debug = 1) THEN
985: mydebug(j || 'Transferring Serial Number: ' || l_serial_table1(j).serial_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
986: END IF;
987:
988: UPDATE mtl_serial_numbers_temp
989: SET transaction_temp_id = l_serial_transaction_temp_id,
990: last_update_date = Sysdate,
991: last_updated_by = p_user_id
992: WHERE transaction_temp_id = l_serial_table1(j).transaction_temp_id

Line 1000: DELETE FROM mtl_serial_numbers_temp

996: IF (l_debug = 1) THEN
997: mydebug(j || 'Deleting Serial Number: ' || l_serial_table1(j).serial_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
998: END IF;
999:
1000: DELETE FROM mtl_serial_numbers_temp
1001: WHERE transaction_temp_id = l_serial_table1(j).transaction_temp_id
1002: AND fm_serial_number = l_serial_table1(j).serial_number;
1003:
1004: -- unmark serial

Line 1005: UPDATE mtl_serial_numbers

1001: WHERE transaction_temp_id = l_serial_table1(j).transaction_temp_id
1002: AND fm_serial_number = l_serial_table1(j).serial_number;
1003:
1004: -- unmark serial
1005: UPDATE mtl_serial_numbers
1006: SET group_mark_id = NULL,
1007: last_update_date = Sysdate,
1008: last_updated_by = p_user_id
1009: WHERE serial_number = l_serial_table1(j).serial_number

Line 4175: l_msnt_record MTL_SERIAL_NUMBERS_TEMP%ROWTYPE;

4171: l_delimiter VARCHAR2(30) := ':';
4172: l_serial_number VARCHAR2(30) := NULL;
4173: l_n_msnt_transaction_temp_id NUMBER;
4174: l_o_msnt_transaction_temp_id NUMBER;
4175: l_msnt_record MTL_SERIAL_NUMBERS_TEMP%ROWTYPE;
4176: m NUMBER := 1; -- position of delimiter
4177: n NUMBER := 1; -- Start position for substr or search for delimiter
4178: CURSOR cur_confirmed_serials IS
4179: SELECT transaction_temp_id

Line 4279: UPDATE mtl_serial_numbers_temp

4275: identified by l_n_msnt_transaction_temp_id */
4276: l_progress := '110';
4277: mydebug('l_progress: ' || l_progress );
4278: -- update the existing msnt record and set its transaction-temp_id = new_transaction-temp_id
4279: UPDATE mtl_serial_numbers_temp
4280: SET transaction_temp_id = l_n_msnt_transaction_temp_id
4281: , last_update_date = SYSDATE
4282: , last_updated_by = p_user_id
4283: WHERE transaction_temp_id = l_o_msnt_transaction_temp_id

Line 4295: UPDATE MTL_SERIAL_NUMBERS

4291: RAISE fnd_api.G_EXC_ERROR;
4292: ELSE
4293: l_progress := '140';
4294: mydebug('l_progress: ' || l_progress );
4295: UPDATE MTL_SERIAL_NUMBERS
4296: SET group_mark_id = l_n_msnt_transaction_temp_id
4297: ,last_update_date= SYSDATE
4298: ,last_updated_by = p_user_id
4299: WHERE current_organization_id = p_organization_id

Line 4356: UPDATE mtl_serial_numbers_temp

4352: l_progress := '200';
4353: mydebug('l_progress: ' || l_progress );
4354:
4355: -- update the existing msnt record and set its transaction-temp_id = new_transaction-temp_id
4356: UPDATE mtl_serial_numbers_temp
4357: SET transaction_temp_id = l_n_msnt_transaction_temp_id
4358: , last_update_date = SYSDATE
4359: , last_updated_by = p_user_id
4360: WHERE transaction_temp_id = l_o_msnt_transaction_temp_id

Line 4363: FROM mtl_serial_numbers msn

4359: , last_updated_by = p_user_id
4360: WHERE transaction_temp_id = l_o_msnt_transaction_temp_id
4361: AND fm_serial_number IN
4362: (SELECT serial_number
4363: FROM mtl_serial_numbers msn
4364: ,mtl_serial_numbers_temp msnt
4365: WHERE msn.serial_number = msnt.fm_serial_number
4366: AND msnt.transaction_temp_id = l_o_msnt_transaction_temp_id
4367: AND NVL(msn.lot_number,'@@') = nvl(p_serial_lot_number,'@@')

Line 4364: ,mtl_serial_numbers_temp msnt

4360: WHERE transaction_temp_id = l_o_msnt_transaction_temp_id
4361: AND fm_serial_number IN
4362: (SELECT serial_number
4363: FROM mtl_serial_numbers msn
4364: ,mtl_serial_numbers_temp msnt
4365: WHERE msn.serial_number = msnt.fm_serial_number
4366: AND msnt.transaction_temp_id = l_o_msnt_transaction_temp_id
4367: AND NVL(msn.lot_number,'@@') = nvl(p_serial_lot_number,'@@')
4368: AND msn.lpn_id = p_lpn_match_lpn_id);

Line 4376: UPDATE MTL_SERIAL_NUMBERS

4372: RAISE fnd_api.G_EXC_ERROR;
4373: ELSE
4374: l_progress := '300';
4375: mydebug('l_progress: ' || l_progress );
4376: UPDATE MTL_SERIAL_NUMBERS
4377: SET group_mark_id = l_n_msnt_transaction_temp_id
4378: ,last_update_date= SYSDATE
4379: ,last_updated_by = p_user_id
4380: WHERE current_organization_id = p_organization_id

Line 4384: FROM mtl_serial_numbers msn

4380: WHERE current_organization_id = p_organization_id
4381: AND inventory_item_id = p_inventory_item_id
4382: AND serial_number IN
4383: (SELECT serial_number
4384: FROM mtl_serial_numbers msn
4385: ,mtl_serial_numbers_temp msnt
4386: WHERE msn.serial_number = msnt.fm_serial_number
4387: AND msnt.transaction_temp_id = l_n_msnt_transaction_temp_id
4388: AND NVL(msn.lot_number,'@@') = nvl(p_serial_lot_number,'@@')

Line 4385: ,mtl_serial_numbers_temp msnt

4381: AND inventory_item_id = p_inventory_item_id
4382: AND serial_number IN
4383: (SELECT serial_number
4384: FROM mtl_serial_numbers msn
4385: ,mtl_serial_numbers_temp msnt
4386: WHERE msn.serial_number = msnt.fm_serial_number
4387: AND msnt.transaction_temp_id = l_n_msnt_transaction_temp_id
4388: AND NVL(msn.lot_number,'@@') = nvl(p_serial_lot_number,'@@')
4389: AND msn.lpn_id = p_lpn_match_lpn_id);

Line 4415: UPDATE mtl_serial_numbers_temp

4411: mydebug('p_lpn_match: ' || p_lpn_match );
4412: /*12871057*/
4413:
4414: -- update the existing msnt record and set its transaction-temp_id = new_transaction-temp_id
4415: UPDATE mtl_serial_numbers_temp
4416: SET transaction_temp_id = l_n_msnt_transaction_temp_id
4417: , last_update_date = SYSDATE
4418: , last_updated_by = p_user_id
4419: WHERE transaction_temp_id = p_transaction_temp_id

Line 4422: FROM mtl_serial_numbers msn

4418: , last_updated_by = p_user_id
4419: WHERE transaction_temp_id = p_transaction_temp_id
4420: AND fm_serial_number IN
4421: (SELECT serial_number
4422: FROM mtl_serial_numbers msn
4423: ,mtl_serial_numbers_temp msnt
4424: WHERE msn.serial_number = msnt.fm_serial_number
4425: AND msnt.transaction_temp_id = p_transaction_temp_id
4426: AND msn.current_organization_id = p_organization_id --HWSNIssue 13860546(Performance Issue)

Line 4423: ,mtl_serial_numbers_temp msnt

4419: WHERE transaction_temp_id = p_transaction_temp_id
4420: AND fm_serial_number IN
4421: (SELECT serial_number
4422: FROM mtl_serial_numbers msn
4423: ,mtl_serial_numbers_temp msnt
4424: WHERE msn.serial_number = msnt.fm_serial_number
4425: AND msnt.transaction_temp_id = p_transaction_temp_id
4426: AND msn.current_organization_id = p_organization_id --HWSNIssue 13860546(Performance Issue)
4427: AND NVL(msn.lot_number,'@@') = nvl(p_serial_lot_number,'@@')

Line 4436: UPDATE MTL_SERIAL_NUMBERS

4432: RAISE fnd_api.G_EXC_ERROR;
4433: ELSE
4434: l_progress := '300111';
4435: mydebug('AMO coming here l_progress: ' || l_progress );
4436: UPDATE MTL_SERIAL_NUMBERS
4437: SET group_mark_id = l_n_msnt_transaction_temp_id
4438: ,last_update_date= SYSDATE
4439: ,last_updated_by = p_user_id
4440: WHERE current_organization_id = p_organization_id

Line 4444: FROM mtl_serial_numbers msn

4440: WHERE current_organization_id = p_organization_id
4441: AND inventory_item_id = p_inventory_item_id
4442: AND serial_number IN
4443: (SELECT serial_number
4444: FROM mtl_serial_numbers msn
4445: ,mtl_serial_numbers_temp msnt
4446: WHERE msn.serial_number = msnt.fm_serial_number
4447: AND msn.current_organization_id = p_organization_id --HWSNIssue 13860546(Performance Issue)
4448: AND msnt.transaction_temp_id = l_n_msnt_transaction_temp_id

Line 4445: ,mtl_serial_numbers_temp msnt

4441: AND inventory_item_id = p_inventory_item_id
4442: AND serial_number IN
4443: (SELECT serial_number
4444: FROM mtl_serial_numbers msn
4445: ,mtl_serial_numbers_temp msnt
4446: WHERE msn.serial_number = msnt.fm_serial_number
4447: AND msn.current_organization_id = p_organization_id --HWSNIssue 13860546(Performance Issue)
4448: AND msnt.transaction_temp_id = l_n_msnt_transaction_temp_id
4449: AND NVL(msn.lot_number,'@@') = nvl(p_serial_lot_number,'@@')

Line 4797: SELECT '1' INTO l_check FROM DUAL WHERE (SELECT Count(1) FROM mtl_serial_numbers

4793:
4794: -- Start of code fix for Bug 14089430
4795: IF p_serial_number IS NULL AND p_lpn_id IS NOT NULL THEN
4796: BEGIN
4797: SELECT '1' INTO l_check FROM DUAL WHERE (SELECT Count(1) FROM mtl_serial_numbers
4798: WHERE inventory_item_id = p_inventory_item_id
4799: AND current_organization_id = p_organization_id
4800: AND NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@')
4801: AND lpn_id = p_lpn_id AND current_status = 3) = (SELECT Sum(primary_transaction_quantity) FROM mtl_onhand_quantities_detail

Line 4828: INSERT INTO mtl_serial_numbers_temp

4824: -- 1. using LPN_ID passed in or serial_lot_number passed in (p-confirmed_serials is NULL)
4825: -- OR 2. using the data from the gtmp table (when p_confirmed_serials is nOT NULL)
4826: IF p_serial_number IS NULL
4827: THEN
4828: INSERT INTO mtl_serial_numbers_temp
4829: (transaction_temp_id
4830: ,last_update_date
4831: ,last_updated_by
4832: ,creation_date

Line 4982: FROM mtl_serial_numbers msn

4978: ,msn.cycles_since_mark
4979: ,msn.number_of_repairs
4980: ,NULL --product_code
4981: ,NULL --product_transaction_id
4982: FROM mtl_serial_numbers msn
4983: WHERE msn.current_organization_id = p_organization_id
4984: AND msn.inventory_item_id = p_inventory_item_id
4985: AND lpn_id = p_lpn_id
4986: AND NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@'));

Line 5014: INSERT INTO mtl_serial_numbers_temp

5010: END IF;
5011:
5012: ELSE -- confirmed_serials are no null so get the serials from mtl-allocations_gtmp table
5013:
5014: INSERT INTO mtl_serial_numbers_temp
5015: (transaction_temp_id
5016: ,last_update_date
5017: ,last_updated_by
5018: ,creation_date

Line 5168: FROM mtl_serial_numbers msn

5164: ,msn.cycles_since_mark
5165: ,msn.number_of_repairs
5166: ,NULL --product_code
5167: ,NULL --product_transaction_id
5168: FROM mtl_serial_numbers msn
5169: WHERE msn.current_organization_id = p_organization_id
5170: AND msn.inventory_item_id = p_inventory_item_id
5171: AND nvl(lpn_id,0) = nvl(p_lpn_id,0)
5172: AND NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@')

Line 5262: UPDATE MTL_SERIAL_NUMBERS

5258: IF p_serial_number IS NULL
5259: THEN
5260: l_progress := '200';
5261: mydebug('l_progress: ' || l_progress );
5262: UPDATE MTL_SERIAL_NUMBERS
5263: SET group_mark_id = p_group_mark_id
5264: , last_updated_by = p_user_id
5265: WHERE current_organization_id = p_organization_id
5266: AND inventory_item_id = p_inventory_item_id

Line 5279: UPDATE MTL_SERIAL_NUMBERS msn

5275: END IF;
5276: ELSE
5277: l_progress := '300';
5278: mydebug('l_progress: ' || l_progress );
5279: UPDATE MTL_SERIAL_NUMBERS msn
5280: SET group_mark_id = p_group_mark_id
5281: , last_updated_by = p_user_id
5282: WHERE msn.current_organization_id = p_organization_id
5283: AND msn.inventory_item_id = p_inventory_item_id

Line 5353: UPDATE MTL_SERIAL_NUMBERS

5349:
5350: x_return_status := l_g_ret_sts_success;
5351: l_progress := '110';
5352: mydebug('l_progress: ' || l_progress );
5353: UPDATE MTL_SERIAL_NUMBERS
5354: SET group_mark_id = p_group_mark_id
5355: , last_updated_by = p_user_id
5356: WHERE current_organization_id = p_organization_id
5357: AND inventory_item_id = p_inventory_item_id

Line 5510: FROM mtl_serial_numbers_temp msnt

5506: ,msnt.fm_serial_number
5507: ,mmtt.organization_id
5508: ,mmtt.inventory_item_id
5509: ,msnt.creation_date
5510: FROM mtl_serial_numbers_temp msnt
5511: ,mtl_material_transactions_temp mmtt
5512: WHERE mmtt.transaction_header_id = p_transaction_header_id
5513: AND mmtt.transaction_temp_id = msnt.transaction_temp_id
5514: AND mmtt.subinventory_code = p_rec_mmtt1_subinventory_code

Line 5526: FROM mtl_serial_numbers_temp msnt

5522: IS
5523: SELECT msnt.transaction_temp_id
5524: ,msnt.fm_serial_number
5525: ,msnt.creation_date
5526: FROM mtl_serial_numbers_temp msnt
5527: WHERE msnt.transaction_temp_id = p_serial_transaction_temp_id
5528: ORDER BY msnt.creation_date DESC;
5529: BEGIN
5530: mydebug('In .. ' || l_proc_name);

Line 5841: UPDATE mtl_serial_numbers_temp

5837: IF p_serial_allocated_flag = 'Y' THEN
5838:
5839: l_progress := 1100;
5840: mydebug('l_progress .. ' || l_progress);
5841: UPDATE mtl_serial_numbers_temp
5842: SET transaction_temp_id = l_serial_transaction_temp_id
5843: , last_update_date = SYSDATE
5844: , last_updated_by = p_user_id
5845: WHERE transaction_temp_id IN

Line 5863: UPDATE MTL_SERIAL_NUMBERS

5859: RAISE fnd_api.G_EXC_ERROR;
5860: END IF;
5861: l_progress := 1200;
5862: mydebug('l_progress .. ' || l_progress);
5863: UPDATE MTL_SERIAL_NUMBERS
5864: SET group_mark_id = l_serial_transaction_temp_id
5865: , last_updated_by = p_user_id
5866: , last_update_date = SYSDATE
5867: WHERE current_organization_id = p_organization_id

Line 5871: FROM mtl_serial_numbers_temp msnt

5867: WHERE current_organization_id = p_organization_id
5868: AND inventory_item_id = rec_mmtt1.inventory_item_id
5869: AND serial_number IN
5870: (SELECT fm_serial_number
5871: FROM mtl_serial_numbers_temp msnt
5872: WHERE msnt.transaction_temp_id = l_serial_transaction_temp_id);
5873:
5874: IF SQL%NOTFOUND THEN
5875: RAISE fnd_api.G_EXC_ERROR;

Line 5881: UPDATE MTL_SERIAL_NUMBERS

5877: ELSE --IF p_serial_allocated_flag = 'N' THEN
5878: l_progress := 1300;
5879: mydebug('l_progress .. ' || l_progress);
5880: -- First unmark serials in all MSNTS
5881: UPDATE MTL_SERIAL_NUMBERS
5882: SET group_mark_id = NULL
5883: , last_updated_by = p_user_id
5884: , last_update_date = SYSDATE
5885: WHERE (current_organization_id

Line 5892: ,mtl_serial_numbers_temp msnt

5888: IN (SELECT mmtt.organization_id
5889: ,mmtt.inventory_item_id
5890: ,msnt.fm_serial_number
5891: FROM mtl_transaction_lots_temp mtlt
5892: ,mtl_serial_numbers_temp msnt
5893: ,mtl_material_transactions_temp mmtt
5894: WHERE mmtt.transaction_header_id = p_transaction_header_id
5895: AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
5896: AND mtlt.serial_transaction_temp_id = msnt.transaction_temp_id

Line 5908: FROM mtl_serial_numbers_temp msnt

5904: --at that time their is no MTLT for substituted lot..
5905: (SELECT mmtt.organization_id
5906: ,mmtt.inventory_item_id
5907: ,msnt.fm_serial_number
5908: FROM mtl_serial_numbers_temp msnt
5909: ,mtl_material_transactions_temp mmtt
5910: WHERE mmtt.transaction_header_id = p_transaction_header_id
5911: AND mmtt.transaction_temp_id = msnt.transaction_temp_id
5912: AND mmtt.subinventory_code = rec_mmtt1.subinventory_code

Line 5926: DELETE mtl_serial_numbers_temp

5922: ELSE
5923: -- Now delete MSNTs
5924: l_progress := 1400;
5925: mydebug('l_progress .. ' || l_progress);
5926: DELETE mtl_serial_numbers_temp
5927: WHERE transaction_temp_id
5928: IN
5929: (SELECT mtlt.serial_transaction_temp_id
5930: FROM mtl_transaction_lots_temp mtlt

Line 5943: FROM mtl_serial_numbers_temp msnt

5939: AND nvl(mmtt.revision,'@@') = nvl(rec_mmtt1.revision,'@@')
5940: UNION --For lot substitution of lot and serial items when serial are confirmed we insert msnt with mmtt.transaction_temp_id
5941: --at that time their is no MTLT for substituted lot..
5942: (SELECT msnt.transaction_temp_id
5943: FROM mtl_serial_numbers_temp msnt
5944: ,mtl_material_transactions_temp mmtt
5945: WHERE mmtt.transaction_header_id = p_transaction_header_id
5946: AND mmtt.transaction_temp_id = msnt.transaction_temp_id
5947: AND mmtt.subinventory_code = rec_mmtt1.subinventory_code

Line 5964: FROM mtl_serial_numbers_temp msnt

5960: l_progress := 4000;
5961: mydebug('l_progress : ' || l_progress);
5962: SELECT count(*)
5963: INTO l_op_msnt_to_delete
5964: FROM mtl_serial_numbers_temp msnt
5965: WHERE msnt.transaction_temp_id = l_serial_transaction_temp_id;
5966: mydebug('l_op_msnt_to_delete : ' || l_op_msnt_to_delete
5967: || ':' || rec_mtlt1.group_lot_primary_quantity);
5968:

Line 5992: UPDATE MTL_SERIAL_NUMBERS

5988: END IF;
5989:
5990: l_progress := 4300;
5991: mydebug('l_progress : ' || l_progress);
5992: UPDATE MTL_SERIAL_NUMBERS
5993: SET group_mark_id = NULL
5994: ,last_updated_by = p_user_id
5995: ,last_update_date = SYSDATE
5996: WHERE current_organization_id = p_organization_id

Line 6005: DELETE mtl_serial_numbers_temp

6001: RAISE fnd_api.G_EXC_ERROR;
6002: ELSE
6003: l_progress := 4400;
6004: mydebug('l_progress : ' || l_progress);
6005: DELETE mtl_serial_numbers_temp
6006: WHERE transaction_temp_id = rec_msnt_to_delete_ls.transaction_temp_id
6007: AND fm_serial_number = rec_msnt_to_delete_ls.fm_serial_number;
6008:
6009: IF SQL%NOTFOUND THEN

Line 6086: UPDATE mtl_serial_numbers_temp

6082: -- Now update MSNT
6083: l_progress := 1900;
6084: mydebug('l_progress : ' || l_progress);
6085:
6086: UPDATE mtl_serial_numbers_temp
6087: SET transaction_temp_id = rec_mmtt1.group_temp_id
6088: , last_update_date = SYSDATE
6089: , last_updated_by = p_user_id
6090: WHERE transaction_temp_id

Line 6093: FROM mtl_serial_numbers_temp msnt,

6089: , last_updated_by = p_user_id
6090: WHERE transaction_temp_id
6091: IN
6092: (SELECT msnt.transaction_temp_id
6093: FROM mtl_serial_numbers_temp msnt,
6094: mtl_material_transactions_temp mmtt
6095: WHERE mmtt.transaction_header_id = p_transaction_header_id
6096: AND mmtt.transaction_temp_id <> rec_mmtt1.group_temp_id
6097: AND mmtt.transaction_temp_id = msnt.transaction_temp_id

Line 6111: UPDATE MTL_SERIAL_NUMBERS

6107: -- REMARK MSN with new temp_id
6108:
6109: l_progress := 2000;
6110: mydebug('l_progress : ' || l_progress);
6111: UPDATE MTL_SERIAL_NUMBERS
6112: SET group_mark_id = rec_mmtt1.group_temp_id
6113: , last_updated_by = p_user_id
6114: , last_update_date = SYSDATE
6115: WHERE current_organization_id = p_organization_id

Line 6120: FROM mtl_serial_numbers_temp

6116: AND inventory_item_id = rec_mmtt1.inventory_item_id
6117: AND serial_number
6118: IN
6119: (SELECT fm_serial_number
6120: FROM mtl_serial_numbers_temp
6121: WHERE transaction_temp_id = rec_mmtt1.group_temp_id);
6122: --l_serial_transaction_temp_id);
6123:
6124: IF SQL%NOTFOUND THEN

Line 6132: UPDATE MTL_SERIAL_NUMBERS

6128: ELSE -- (p_serial_allocated_flag = 'N' ,
6129: -- delete all msnts and unmark all these serials in MSN.
6130: l_progress := 2100;
6131: mydebug('l_progress : ' || l_progress);
6132: UPDATE MTL_SERIAL_NUMBERS
6133: SET group_mark_id = NULL
6134: , last_updated_by = p_user_id
6135: , last_update_date = SYSDATE
6136: WHERE (current_organization_id

Line 6142: FROM mtl_serial_numbers_temp msnt

6138: ,serial_number)
6139: IN (SELECT mmtt.organization_id
6140: ,mmtt.inventory_item_id
6141: ,msnt.fm_serial_number
6142: FROM mtl_serial_numbers_temp msnt
6143: ,mtl_material_transactions_temp mmtt
6144: WHERE mmtt.transaction_header_id = p_transaction_header_id
6145: AND mmtt.transaction_temp_id = msnt.transaction_temp_id
6146: AND mmtt.subinventory_code = rec_mmtt1.subinventory_code

Line 6159: DELETE mtl_serial_numbers_temp

6155: ELSE
6156: l_progress := 2200;
6157: mydebug('l_progress : ' || l_progress);
6158:
6159: DELETE mtl_serial_numbers_temp
6160: WHERE transaction_temp_id IN
6161: (SELECT msnt.transaction_temp_id
6162: FROM mtl_serial_numbers_temp msnt,
6163: mtl_material_transactions_temp mmtt

Line 6162: FROM mtl_serial_numbers_temp msnt,

6158:
6159: DELETE mtl_serial_numbers_temp
6160: WHERE transaction_temp_id IN
6161: (SELECT msnt.transaction_temp_id
6162: FROM mtl_serial_numbers_temp msnt,
6163: mtl_material_transactions_temp mmtt
6164: WHERE mmtt.transaction_header_id = p_transaction_header_id
6165: AND mmtt.transaction_temp_id = msnt.transaction_temp_id
6166: AND mmtt.subinventory_code = rec_mmtt1.subinventory_code

Line 6183: FROM mtl_serial_numbers_temp

6179: l_progress := 3000;
6180: mydebug('l_progress : ' || l_progress);
6181: SELECT count(*)
6182: INTO l_op_msnt_to_delete
6183: FROM mtl_serial_numbers_temp
6184: WHERE (transaction_temp_id ,
6185: fm_serial_number)
6186: IN
6187: (SELECT msnt.transaction_temp_id

Line 6189: FROM mtl_serial_numbers_temp msnt

6185: fm_serial_number)
6186: IN
6187: (SELECT msnt.transaction_temp_id
6188: ,msnt.fm_serial_number
6189: FROM mtl_serial_numbers_temp msnt
6190: ,mtl_material_transactions_temp mmtt
6191: WHERE mmtt.transaction_header_id = p_transaction_header_id
6192: AND mmtt.transaction_temp_id = msnt.transaction_temp_id
6193: AND mmtt.subinventory_code = rec_mmtt1.subinventory_code

Line 6233: UPDATE MTL_SERIAL_NUMBERS

6229: mydebug('l_op_msnt_to_delete : ' || l_op_msnt_to_delete);
6230: END IF;
6231: l_progress := 3300;
6232: mydebug('l_progress : ' || l_progress);
6233: UPDATE MTL_SERIAL_NUMBERS
6234: SET group_mark_id = NULL
6235: ,last_updated_by = p_user_id
6236: ,last_update_date = SYSDATE
6237: WHERE current_organization_id = p_organization_id

Line 6246: DELETE mtl_serial_numbers_temp

6242: RAISE fnd_api.G_EXC_ERROR;
6243: ELSE
6244: l_progress := 2200;
6245: mydebug('l_progress : ' || l_progress);
6246: DELETE mtl_serial_numbers_temp
6247: WHERE transaction_temp_id = rec_msnt_to_delete.transaction_temp_id
6248: AND fm_serial_number = rec_msnt_to_delete.fm_serial_number;
6249:
6250: IF SQL%NOTFOUND THEN

Line 7547: FROM mtl_serial_numbers msn

7543: WHERE transaction_temp_id = p_temp_id;
7544:
7545: CURSOR insert_serial_allocated_csr (p_serial_lot_number VARCHAR2) IS
7546: SELECT serial_number
7547: FROM mtl_serial_numbers msn
7548: WHERE msn.current_organization_id = p_organization_id
7549: AND msn.inventory_item_id = p_item_id
7550: AND lpn_id = p_lpn_match_lpn_id
7551: AND NVL(msn.lot_number,'@@') = NVL(p_serial_lot_number, '@@')

Line 7554: from mtl_serial_numbers_temp msnt,

7550: AND lpn_id = p_lpn_match_lpn_id
7551: AND NVL(msn.lot_number,'@@') = NVL(p_serial_lot_number, '@@')
7552: AND msn.serial_number NOT IN
7553: ( select msnt.fm_serial_number
7554: from mtl_serial_numbers_temp msnt,
7555: mtl_transaction_lots_temp mtlt,
7556: mtl_material_transactions_temp mmtt
7557: where mmtt.inventory_item_id = p_item_id
7558: AND mmtt.organization_id = p_organization_id

Line 8422: FROM mtl_serial_numbers

8418: l_fulfillment_base VARCHAR2(1) := p_fulfillment_base;
8419:
8420: CURSOR ser_csr IS
8421: SELECT serial_number
8422: FROM mtl_serial_numbers
8423: WHERE lpn_id = p_fromlpn_id
8424: AND inventory_item_id = p_item_id
8425: AND NVL(lot_number, -999) = NVL(p_lot, -999);
8426:

Line 9061: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt

9057: END IF;
9058:
9059: SELECT COUNT(fm_serial_number)
9060: INTO l_serial_exist_cnt
9061: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
9062: WHERE mtlt.transaction_temp_id = p_temp_id
9063: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
9064: AND msnt.fm_serial_number IN(
9065: SELECT serial_number

Line 9066: FROM mtl_serial_numbers

9062: WHERE mtlt.transaction_temp_id = p_temp_id
9063: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
9064: AND msnt.fm_serial_number IN(
9065: SELECT serial_number
9066: FROM mtl_serial_numbers
9067: WHERE lpn_id = p_fromlpn_id
9068: AND inventory_item_id = p_item_id
9069: AND NVL(revision, '-999') = NVL(p_rev, '-999'));
9070:

Line 9452: FROM mtl_serial_numbers_temp msnt

9448: END IF;
9449:
9450: SELECT COUNT(fm_serial_number)
9451: INTO l_serial_exist_cnt
9452: FROM mtl_serial_numbers_temp msnt
9453: WHERE msnt.transaction_temp_id = p_temp_id
9454: AND msnt.fm_serial_number IN(
9455: SELECT serial_number
9456: FROM mtl_serial_numbers

Line 9456: FROM mtl_serial_numbers

9452: FROM mtl_serial_numbers_temp msnt
9453: WHERE msnt.transaction_temp_id = p_temp_id
9454: AND msnt.fm_serial_number IN(
9455: SELECT serial_number
9456: FROM mtl_serial_numbers
9457: WHERE lpn_id = p_fromlpn_id
9458: AND inventory_item_id = p_item_id
9459: AND NVL(revision, '-999') = NVL(p_rev, '-999'));
9460:

Line 9809: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt

9805: END IF;
9806:
9807: SELECT COUNT(fm_serial_number)
9808: INTO l_serial_exist_cnt
9809: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
9810: WHERE mtlt.transaction_temp_id = p_temp_id
9811: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
9812: AND msnt.fm_serial_number IN(
9813: SELECT serial_number

Line 9814: FROM mtl_serial_numbers

9810: WHERE mtlt.transaction_temp_id = p_temp_id
9811: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
9812: AND msnt.fm_serial_number IN(
9813: SELECT serial_number
9814: FROM mtl_serial_numbers
9815: WHERE lpn_id = p_fromlpn_id
9816: AND inventory_item_id = p_item_id
9817: AND NVL(revision, '-999') = NVL(p_rev, '-999'));
9818:

Line 10344: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt

10340: AND p_action = 4 THEN
10341:
10342: SELECT COUNT(fm_serial_number)
10343: INTO l_total_serial_cnt
10344: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
10345: WHERE mtlt.transaction_temp_id = p_temp_id
10346: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
10347:
10348: IF (l_debug = 1) THEN

Line 10409: FROM mtl_serial_numbers_temp msnt

10405: END IF;
10406:
10407: SELECT COUNT(fm_serial_number)
10408: INTO l_serial_exist_cnt
10409: FROM mtl_serial_numbers_temp msnt
10410: WHERE msnt.transaction_temp_id = p_temp_id
10411: AND msnt.fm_serial_number IN(
10412: SELECT serial_number
10413: FROM mtl_serial_numbers

Line 10413: FROM mtl_serial_numbers

10409: FROM mtl_serial_numbers_temp msnt
10410: WHERE msnt.transaction_temp_id = p_temp_id
10411: AND msnt.fm_serial_number IN(
10412: SELECT serial_number
10413: FROM mtl_serial_numbers
10414: WHERE lpn_id = p_fromlpn_id
10415: AND inventory_item_id = p_item_id
10416: AND NVL(revision, '-999') = NVL(p_rev, '-999'));
10417:

Line 10772: FROM mtl_serial_numbers_temp msnt

10768: IF p_is_sn_alloc = 'Y' AND p_action = 4 THEN --{
10769:
10770: SELECT COUNT(fm_serial_number)
10771: INTO l_total_serial_cnt
10772: FROM mtl_serial_numbers_temp msnt
10773: WHERE msnt.transaction_temp_id = p_temp_id;
10774:
10775: IF (l_debug = 1) THEN
10776: mydebug('SN tot count' || l_total_serial_cnt);

Line 11521: FROM mtl_serial_numbers_temp msnt,

11517: serial_number,
11518: transaction_quantity,
11519: primary_quantity)
11520: SELECT mtlt.lot_number,fm_serial_number,1,1
11521: FROM mtl_serial_numbers_temp msnt,
11522: mtl_transaction_lots_temp mtlt,
11523: mtl_serial_numbers msn
11524: WHERE mtlt.transaction_temp_id = p_temp_id
11525: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id

Line 11523: mtl_serial_numbers msn

11519: primary_quantity)
11520: SELECT mtlt.lot_number,fm_serial_number,1,1
11521: FROM mtl_serial_numbers_temp msnt,
11522: mtl_transaction_lots_temp mtlt,
11523: mtl_serial_numbers msn
11524: WHERE mtlt.transaction_temp_id = p_temp_id
11525: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
11526: AND msnt.fm_serial_number = msn.serial_number
11527: AND msn.lpn_id = p_fromlpn_id

Line 11573: FROM mtl_serial_numbers_temp msnt,

11569: (serial_number,
11570: transaction_quantity,
11571: primary_quantity)
11572: SELECT fm_serial_number,1,1
11573: FROM mtl_serial_numbers_temp msnt,
11574: mtl_serial_numbers msn
11575: WHERE msnt.transaction_temp_id = p_temp_id
11576: AND msnt.fm_serial_number = msn.serial_number
11577: AND msn.lpn_id = p_fromlpn_id

Line 11574: mtl_serial_numbers msn

11570: transaction_quantity,
11571: primary_quantity)
11572: SELECT fm_serial_number,1,1
11573: FROM mtl_serial_numbers_temp msnt,
11574: mtl_serial_numbers msn
11575: WHERE msnt.transaction_temp_id = p_temp_id
11576: AND msnt.fm_serial_number = msn.serial_number
11577: AND msn.lpn_id = p_fromlpn_id
11578: AND msn.inventory_item_id = p_item_id;

Line 11610: mtl_serial_numbers msn

11606: transaction_quantity,
11607: primary_quantity)
11608: SELECT mtlt.lot_number, serial_number, 1, 1
11609: FROM mtl_transaction_lots_temp mtlt,
11610: mtl_serial_numbers msn
11611: WHERE mtlt.transaction_temp_id = p_temp_id
11612: AND msn.lpn_id = p_fromlpn_id
11613: AND mtlt.lot_number = msn.lot_number
11614: AND msn.inventory_item_id = p_item_id

Line 11622: FROM mtl_serial_numbers msn

11618: (serial_number,
11619: transaction_quantity,
11620: primary_quantity)
11621: SELECT serial_number,1,1
11622: FROM mtl_serial_numbers msn
11623: WHERE msn.lpn_id = p_fromlpn_id
11624: AND msn.inventory_item_id = p_item_id
11625: AND Nvl(msn.group_mark_id, -1) = -1;
11626: END IF;

Line 12168: FROM mtl_serial_numbers_temp msnt,

12164: serial_number,
12165: transaction_quantity,
12166: primary_quantity)
12167: SELECT mtlt.lot_number,fm_serial_number,1,1
12168: FROM mtl_serial_numbers_temp msnt,
12169: mtl_transaction_lots_temp mtlt,
12170: mtl_serial_numbers msn
12171: WHERE mtlt.transaction_temp_id = p_temp_id
12172: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id

Line 12170: mtl_serial_numbers msn

12166: primary_quantity)
12167: SELECT mtlt.lot_number,fm_serial_number,1,1
12168: FROM mtl_serial_numbers_temp msnt,
12169: mtl_transaction_lots_temp mtlt,
12170: mtl_serial_numbers msn
12171: WHERE mtlt.transaction_temp_id = p_temp_id
12172: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
12173: AND msnt.fm_serial_number = msn.serial_number
12174: AND msn.lpn_id is null -- make sure it is loose pick

Line 12194: FROM mtl_serial_numbers_temp msnt,

12190: (serial_number,
12191: transaction_quantity,
12192: primary_quantity)
12193: SELECT fm_serial_number,1,1
12194: FROM mtl_serial_numbers_temp msnt,
12195: mtl_serial_numbers msn
12196: WHERE msnt.transaction_temp_id = p_temp_id
12197: AND msnt.fm_serial_number = msn.serial_number
12198: AND msn.lpn_id is null

Line 12195: mtl_serial_numbers msn

12191: transaction_quantity,
12192: primary_quantity)
12193: SELECT fm_serial_number,1,1
12194: FROM mtl_serial_numbers_temp msnt,
12195: mtl_serial_numbers msn
12196: WHERE msnt.transaction_temp_id = p_temp_id
12197: AND msnt.fm_serial_number = msn.serial_number
12198: AND msn.lpn_id is null
12199: AND msn.inventory_item_id = p_item_id;

Line 14268: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,

14264: l_count := 0;
14265: BEGIN
14266: SELECT 1
14267: INTO l_count
14268: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,
14269: mtl_material_transactions_temp mmtt
14270: WHERE (p_from_serial BETWEEN msnt.fm_serial_number AND msnt.to_serial_number
14271: OR p_to_serial BETWEEN msnt.fm_serial_number AND msnt.to_serial_number)
14272: AND mmtt.inventory_item_id = p_item_id

Line 14335: UPDATE mtl_serial_numbers

14331: );
14332: l_progress := '70';
14333:
14334: BEGIN
14335: UPDATE mtl_serial_numbers
14336: SET group_mark_id = p_serial_transaction_temp_id
14337: WHERE inventory_item_id = p_item_id
14338: AND serial_number BETWEEN p_from_serial AND p_to_serial
14339: AND LENGTH(serial_number) = LENGTH(p_from_serial);

Line 15489: FROM mtl_serial_numbers

15485:
15486: CURSOR c_serials_in_lpn (v_lpn_id NUMBER, v_lot_number VARCHAR2)
15487: IS
15488: SELECT serial_number
15489: FROM mtl_serial_numbers
15490: WHERE lpn_id = v_lpn_id
15491: AND lot_number = v_lot_number
15492: AND inventory_item_id = p_item_id
15493: AND NVL (revision, '-999') = NVL (p_rev, '-999')

Line 15638: UPDATE MTL_SERIAL_NUMBERS

15634:
15635: IF (p_serial_controlled = 'Y') THEN
15636: mydebug (p_api_name||' Item is Serial Controlled');
15637: -- Unmark the Serials
15638: UPDATE MTL_SERIAL_NUMBERS
15639: SET group_mark_id = NULL
15640: , last_updated_by = fnd_global.user_id
15641: , last_update_date = SYSDATE
15642: WHERE (current_organization_id

Line 15649: ,mtl_serial_numbers_temp msnt

15645: IN (SELECT mmtt.organization_id
15646: ,mmtt.inventory_item_id
15647: ,msnt.fm_serial_number
15648: FROM mtl_transaction_lots_temp mtlt
15649: ,mtl_serial_numbers_temp msnt
15650: ,mtl_material_transactions_temp mmtt
15651: WHERE mmtt.transaction_header_id = p_transaction_header_id
15652: AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
15653: AND mtlt.serial_transaction_temp_id = msnt.transaction_temp_id

Line 15659: FROM mtl_serial_numbers_temp msnt

15655: --at that time their is no MTLT for substituted lot..
15656: SELECT mmtt.organization_id
15657: ,mmtt.inventory_item_id
15658: ,msnt.fm_serial_number
15659: FROM mtl_serial_numbers_temp msnt
15660: ,mtl_material_transactions_temp mmtt
15661: WHERE mmtt.transaction_header_id = p_transaction_header_id
15662: AND mmtt.transaction_temp_id = msnt.transaction_temp_id
15663: );

Line 15666: DELETE FROM mtl_serial_numbers_temp

15662: AND mmtt.transaction_temp_id = msnt.transaction_temp_id
15663: );
15664: mydebug (p_api_name||' Updated '||SQL%ROWCOUNT||' rows in MSN');
15665:
15666: DELETE FROM mtl_serial_numbers_temp
15667: WHERE transaction_temp_id IN ((SELECT serial_transaction_temp_id
15668: FROM mtl_transaction_lots_temp
15669: WHERE transaction_temp_id IN (SELECT transaction_temp_id
15670: FROM mtl_material_transactions_temp