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 318: (lot_number mtl_serial_numbers.lot_number%TYPE,

314: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
315: l_lpn_id NUMBER;
316:
317: TYPE lot_record_type IS RECORD
318: (lot_number mtl_serial_numbers.lot_number%TYPE,
319: primary_quantity NUMBER,
320: delta_primary_quantity NUMBER,
321: secondary_quantity NUMBER,
322: delta_secondary_quantity NUMBER,

Line 329: (serial_number mtl_serial_numbers.serial_number%TYPE,

325: update_mtlt BOOLEAN,
326: delete_mtlt BOOLEAN);
327:
328: TYPE serial_record_type IS RECORD
329: (serial_number mtl_serial_numbers.serial_number%TYPE,
330: lot_number mtl_serial_numbers.lot_number%TYPE,
331: transaction_temp_id NUMBER,
332: delete_msnt BOOLEAN);
333:

Line 330: lot_number mtl_serial_numbers.lot_number%TYPE,

326: delete_mtlt BOOLEAN);
327:
328: TYPE serial_record_type IS RECORD
329: (serial_number mtl_serial_numbers.serial_number%TYPE,
330: lot_number mtl_serial_numbers.lot_number%TYPE,
331: transaction_temp_id NUMBER,
332: delete_msnt BOOLEAN);
333:
334: TYPE lot_table_type IS TABLE OF lot_record_type INDEX BY BINARY_INTEGER;

Line 358: FROM mtl_serial_numbers_temp

354: FOR UPDATE;
355:
356: CURSOR msnt_cursor(v_transaction_temp_id NUMBER) IS
357: SELECT fm_serial_number
358: FROM mtl_serial_numbers_temp
359: WHERE transaction_temp_id = v_transaction_temp_id
360: ORDER BY fm_serial_number
361: FOR UPDATE;
362:

Line 709: UPDATE mtl_serial_numbers_temp

705: IF (l_debug = 1) THEN
706: mydebug(j || 'Transferring Serial Number: ' || l_serial_table1(j).serial_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
707: END IF;
708:
709: UPDATE mtl_serial_numbers_temp
710: SET transaction_temp_id = p_transaction_temp_id2,
711: last_update_date = Sysdate,
712: last_updated_by = p_user_id
713: WHERE transaction_temp_id = p_transaction_temp_id1

Line 721: DELETE FROM mtl_serial_numbers_temp

717: IF (l_debug = 1) THEN
718: mydebug(j || 'Deleting Serial Number: ' || l_serial_table1(j).serial_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
719: END IF;
720:
721: DELETE FROM mtl_serial_numbers_temp
722: WHERE transaction_temp_id = p_transaction_temp_id1
723: AND fm_serial_number = l_serial_table1(j).serial_number;
724:
725: -- unmark serial

Line 726: UPDATE mtl_serial_numbers

722: WHERE transaction_temp_id = p_transaction_temp_id1
723: AND fm_serial_number = l_serial_table1(j).serial_number;
724:
725: -- unmark serial
726: UPDATE mtl_serial_numbers
727: SET group_mark_id = NULL,
728: last_update_date = Sysdate,
729: last_updated_by = p_user_id
730: WHERE serial_number = l_serial_table1(j).serial_number

Line 918: UPDATE mtl_serial_numbers_temp

914: IF (l_debug = 1) THEN
915: mydebug(j || 'Transferring Serial Number: ' || l_serial_table1(j).serial_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
916: END IF;
917:
918: UPDATE mtl_serial_numbers_temp
919: SET transaction_temp_id = l_serial_transaction_temp_id,
920: last_update_date = Sysdate,
921: last_updated_by = p_user_id
922: WHERE transaction_temp_id = l_serial_table1(j).transaction_temp_id

Line 930: DELETE FROM mtl_serial_numbers_temp

926: IF (l_debug = 1) THEN
927: mydebug(j || 'Deleting Serial Number: ' || l_serial_table1(j).serial_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
928: END IF;
929:
930: DELETE FROM mtl_serial_numbers_temp
931: WHERE transaction_temp_id = l_serial_table1(j).transaction_temp_id
932: AND fm_serial_number = l_serial_table1(j).serial_number;
933:
934: -- unmark serial

Line 935: UPDATE mtl_serial_numbers

931: WHERE transaction_temp_id = l_serial_table1(j).transaction_temp_id
932: AND fm_serial_number = l_serial_table1(j).serial_number;
933:
934: -- unmark serial
935: UPDATE mtl_serial_numbers
936: SET group_mark_id = NULL,
937: last_update_date = Sysdate,
938: last_updated_by = p_user_id
939: WHERE serial_number = l_serial_table1(j).serial_number

Line 3158: l_msnt_record MTL_SERIAL_NUMBERS_TEMP%ROWTYPE;

3154: l_delimiter VARCHAR2(30) := ':';
3155: l_serial_number VARCHAR2(30) := NULL;
3156: l_n_msnt_transaction_temp_id NUMBER;
3157: l_o_msnt_transaction_temp_id NUMBER;
3158: l_msnt_record MTL_SERIAL_NUMBERS_TEMP%ROWTYPE;
3159: m NUMBER := 1; -- position of delimiter
3160: n NUMBER := 1; -- Start position for substr or search for delimiter
3161: CURSOR cur_confirmed_serials IS
3162: SELECT transaction_temp_id

Line 3259: UPDATE mtl_serial_numbers_temp

3255: identified by l_n_msnt_transaction_temp_id */
3256: l_progress := '110';
3257: mydebug('l_progress: ' || l_progress );
3258: -- update the existing msnt record and set its transaction-temp_id = new_transaction-temp_id
3259: UPDATE mtl_serial_numbers_temp
3260: SET transaction_temp_id = l_n_msnt_transaction_temp_id
3261: , last_update_date = SYSDATE
3262: , last_updated_by = p_user_id
3263: WHERE transaction_temp_id = l_o_msnt_transaction_temp_id

Line 3275: UPDATE MTL_SERIAL_NUMBERS

3271: RAISE fnd_api.G_EXC_ERROR;
3272: ELSE
3273: l_progress := '140';
3274: mydebug('l_progress: ' || l_progress );
3275: UPDATE MTL_SERIAL_NUMBERS
3276: SET group_mark_id = l_n_msnt_transaction_temp_id
3277: ,last_update_date= SYSDATE
3278: ,last_updated_by = p_user_id
3279: WHERE current_organization_id = p_organization_id

Line 3335: UPDATE mtl_serial_numbers_temp

3331: l_progress := '200';
3332: mydebug('l_progress: ' || l_progress );
3333:
3334: -- update the existing msnt record and set its transaction-temp_id = new_transaction-temp_id
3335: UPDATE mtl_serial_numbers_temp
3336: SET transaction_temp_id = l_n_msnt_transaction_temp_id
3337: , last_update_date = SYSDATE
3338: , last_updated_by = p_user_id
3339: WHERE transaction_temp_id = l_o_msnt_transaction_temp_id

Line 3342: FROM mtl_serial_numbers msn

3338: , last_updated_by = p_user_id
3339: WHERE transaction_temp_id = l_o_msnt_transaction_temp_id
3340: AND fm_serial_number IN
3341: (SELECT serial_number
3342: FROM mtl_serial_numbers msn
3343: ,mtl_serial_numbers_temp msnt
3344: WHERE msn.serial_number = msnt.fm_serial_number
3345: AND msnt.transaction_temp_id = l_o_msnt_transaction_temp_id
3346: AND NVL(msn.lot_number,'@@') = nvl(p_serial_lot_number,'@@')

Line 3343: ,mtl_serial_numbers_temp msnt

3339: WHERE transaction_temp_id = l_o_msnt_transaction_temp_id
3340: AND fm_serial_number IN
3341: (SELECT serial_number
3342: FROM mtl_serial_numbers msn
3343: ,mtl_serial_numbers_temp msnt
3344: WHERE msn.serial_number = msnt.fm_serial_number
3345: AND msnt.transaction_temp_id = l_o_msnt_transaction_temp_id
3346: AND NVL(msn.lot_number,'@@') = nvl(p_serial_lot_number,'@@')
3347: AND msn.lpn_id = p_lpn_match_lpn_id);

Line 3355: UPDATE MTL_SERIAL_NUMBERS

3351: RAISE fnd_api.G_EXC_ERROR;
3352: ELSE
3353: l_progress := '300';
3354: mydebug('l_progress: ' || l_progress );
3355: UPDATE MTL_SERIAL_NUMBERS
3356: SET group_mark_id = l_n_msnt_transaction_temp_id
3357: ,last_update_date= SYSDATE
3358: ,last_updated_by = p_user_id
3359: WHERE current_organization_id = p_organization_id

Line 3363: FROM mtl_serial_numbers msn

3359: WHERE current_organization_id = p_organization_id
3360: AND inventory_item_id = p_inventory_item_id
3361: AND serial_number IN
3362: (SELECT serial_number
3363: FROM mtl_serial_numbers msn
3364: ,mtl_serial_numbers_temp msnt
3365: WHERE msn.serial_number = msnt.fm_serial_number
3366: AND msnt.transaction_temp_id = l_n_msnt_transaction_temp_id
3367: AND NVL(msn.lot_number,'@@') = nvl(p_serial_lot_number,'@@')

Line 3364: ,mtl_serial_numbers_temp msnt

3360: AND inventory_item_id = p_inventory_item_id
3361: AND serial_number IN
3362: (SELECT serial_number
3363: FROM mtl_serial_numbers msn
3364: ,mtl_serial_numbers_temp msnt
3365: WHERE msn.serial_number = msnt.fm_serial_number
3366: AND msnt.transaction_temp_id = l_n_msnt_transaction_temp_id
3367: AND NVL(msn.lot_number,'@@') = nvl(p_serial_lot_number,'@@')
3368: AND msn.lpn_id = p_lpn_match_lpn_id);

Line 3714: INSERT INTO mtl_serial_numbers_temp

3710: -- 1. using LPN_ID passed in or serial_lot_number passed in (p-confirmed_serials is NULL)
3711: -- OR 2. using the data from the gtmp table (when p_confirmed_serials is nOT NULL)
3712: IF p_serial_number IS NULL
3713: THEN
3714: INSERT INTO mtl_serial_numbers_temp
3715: (transaction_temp_id
3716: ,last_update_date
3717: ,last_updated_by
3718: ,creation_date

Line 3868: FROM mtl_serial_numbers msn

3864: ,msn.cycles_since_mark
3865: ,msn.number_of_repairs
3866: ,NULL --product_code
3867: ,NULL --product_transaction_id
3868: FROM mtl_serial_numbers msn
3869: WHERE msn.current_organization_id = p_organization_id
3870: AND msn.inventory_item_id = p_inventory_item_id
3871: AND lpn_id = p_lpn_id
3872: AND NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@'));

Line 3900: INSERT INTO mtl_serial_numbers_temp

3896: END IF;
3897:
3898: ELSE -- confirmed_serials are no null so get the serials from mtl-allocations_gtmp table
3899:
3900: INSERT INTO mtl_serial_numbers_temp
3901: (transaction_temp_id
3902: ,last_update_date
3903: ,last_updated_by
3904: ,creation_date

Line 4054: FROM mtl_serial_numbers msn

4050: ,msn.cycles_since_mark
4051: ,msn.number_of_repairs
4052: ,NULL --product_code
4053: ,NULL --product_transaction_id
4054: FROM mtl_serial_numbers msn
4055: WHERE msn.current_organization_id = p_organization_id
4056: AND msn.inventory_item_id = p_inventory_item_id
4057: AND nvl(lpn_id,0) = nvl(p_lpn_id,0)
4058: AND NVL(lot_number,'@@') = nvl(p_serial_lot_number,'@@')

Line 4148: UPDATE MTL_SERIAL_NUMBERS

4144: IF p_serial_number IS NULL
4145: THEN
4146: l_progress := '200';
4147: mydebug('l_progress: ' || l_progress );
4148: UPDATE MTL_SERIAL_NUMBERS
4149: SET group_mark_id = p_group_mark_id
4150: , last_updated_by = p_user_id
4151: WHERE current_organization_id = p_organization_id
4152: AND inventory_item_id = p_inventory_item_id

Line 4165: UPDATE MTL_SERIAL_NUMBERS msn

4161: END IF;
4162: ELSE
4163: l_progress := '300';
4164: mydebug('l_progress: ' || l_progress );
4165: UPDATE MTL_SERIAL_NUMBERS msn
4166: SET group_mark_id = p_group_mark_id
4167: , last_updated_by = p_user_id
4168: WHERE msn.current_organization_id = p_organization_id
4169: AND msn.inventory_item_id = p_inventory_item_id

Line 4239: UPDATE MTL_SERIAL_NUMBERS

4235:
4236: x_return_status := l_g_ret_sts_success;
4237: l_progress := '110';
4238: mydebug('l_progress: ' || l_progress );
4239: UPDATE MTL_SERIAL_NUMBERS
4240: SET group_mark_id = p_group_mark_id
4241: , last_updated_by = p_user_id
4242: WHERE current_organization_id = p_organization_id
4243: AND inventory_item_id = p_inventory_item_id

Line 4387: FROM mtl_serial_numbers_temp msnt

4383: ,msnt.fm_serial_number
4384: ,mmtt.organization_id
4385: ,mmtt.inventory_item_id
4386: ,msnt.creation_date
4387: FROM mtl_serial_numbers_temp msnt
4388: ,mtl_material_transactions_temp mmtt
4389: WHERE mmtt.transaction_header_id = p_transaction_header_id
4390: AND mmtt.transaction_temp_id = msnt.transaction_temp_id
4391: AND mmtt.subinventory_code = p_rec_mmtt1_subinventory_code

Line 4403: FROM mtl_serial_numbers_temp msnt

4399: IS
4400: SELECT msnt.transaction_temp_id
4401: ,msnt.fm_serial_number
4402: ,msnt.creation_date
4403: FROM mtl_serial_numbers_temp msnt
4404: WHERE msnt.transaction_temp_id = p_serial_transaction_temp_id
4405: ORDER BY msnt.creation_date DESC;
4406: BEGIN
4407: mydebug('In .. ' || l_proc_name);

Line 4661: UPDATE mtl_serial_numbers_temp

4657: IF p_serial_allocated_flag = 'Y' THEN
4658:
4659: l_progress := 1100;
4660: mydebug('l_progress .. ' || l_progress);
4661: UPDATE mtl_serial_numbers_temp
4662: SET transaction_temp_id = l_serial_transaction_temp_id
4663: , last_update_date = SYSDATE
4664: , last_updated_by = p_user_id
4665: WHERE transaction_temp_id IN

Line 4683: UPDATE MTL_SERIAL_NUMBERS

4679: RAISE fnd_api.G_EXC_ERROR;
4680: END IF;
4681: l_progress := 1200;
4682: mydebug('l_progress .. ' || l_progress);
4683: UPDATE MTL_SERIAL_NUMBERS
4684: SET group_mark_id = l_serial_transaction_temp_id
4685: , last_updated_by = p_user_id
4686: , last_update_date = SYSDATE
4687: WHERE current_organization_id = p_organization_id

Line 4691: FROM mtl_serial_numbers_temp msnt

4687: WHERE current_organization_id = p_organization_id
4688: AND inventory_item_id = rec_mmtt1.inventory_item_id
4689: AND serial_number IN
4690: (SELECT fm_serial_number
4691: FROM mtl_serial_numbers_temp msnt
4692: WHERE msnt.transaction_temp_id = l_serial_transaction_temp_id);
4693:
4694: IF SQL%NOTFOUND THEN
4695: RAISE fnd_api.G_EXC_ERROR;

Line 4701: UPDATE MTL_SERIAL_NUMBERS

4697: ELSE --IF p_serial_allocated_flag = 'N' THEN
4698: l_progress := 1300;
4699: mydebug('l_progress .. ' || l_progress);
4700: -- First unmark serials in all MSNTS
4701: UPDATE MTL_SERIAL_NUMBERS
4702: SET group_mark_id = NULL
4703: , last_updated_by = p_user_id
4704: , last_update_date = SYSDATE
4705: WHERE (current_organization_id

Line 4712: ,mtl_serial_numbers_temp msnt

4708: IN (SELECT mmtt.organization_id
4709: ,mmtt.inventory_item_id
4710: ,msnt.fm_serial_number
4711: FROM mtl_transaction_lots_temp mtlt
4712: ,mtl_serial_numbers_temp msnt
4713: ,mtl_material_transactions_temp mmtt
4714: WHERE mmtt.transaction_header_id = p_transaction_header_id
4715: AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
4716: AND mtlt.serial_transaction_temp_id = msnt.transaction_temp_id

Line 4731: DELETE mtl_serial_numbers_temp

4727: ELSE
4728: -- Now delete MSNTs
4729: l_progress := 1400;
4730: mydebug('l_progress .. ' || l_progress);
4731: DELETE mtl_serial_numbers_temp
4732: WHERE transaction_temp_id
4733: IN
4734: (SELECT mtlt.serial_transaction_temp_id
4735: FROM mtl_transaction_lots_temp mtlt

Line 4756: FROM mtl_serial_numbers_temp msnt

4752: l_progress := 4000;
4753: mydebug('l_progress : ' || l_progress);
4754: SELECT count(*)
4755: INTO l_op_msnt_to_delete
4756: FROM mtl_serial_numbers_temp msnt
4757: WHERE msnt.transaction_temp_id = l_serial_transaction_temp_id;
4758: mydebug('l_op_msnt_to_delete : ' || l_op_msnt_to_delete
4759: || ':' || rec_mtlt1.group_lot_primary_quantity);
4760:

Line 4784: UPDATE MTL_SERIAL_NUMBERS

4780: END IF;
4781:
4782: l_progress := 4300;
4783: mydebug('l_progress : ' || l_progress);
4784: UPDATE MTL_SERIAL_NUMBERS
4785: SET group_mark_id = NULL
4786: ,last_updated_by = p_user_id
4787: ,last_update_date = SYSDATE
4788: WHERE current_organization_id = p_organization_id

Line 4797: DELETE mtl_serial_numbers_temp

4793: RAISE fnd_api.G_EXC_ERROR;
4794: ELSE
4795: l_progress := 4400;
4796: mydebug('l_progress : ' || l_progress);
4797: DELETE mtl_serial_numbers_temp
4798: WHERE transaction_temp_id = rec_msnt_to_delete_ls.transaction_temp_id
4799: AND fm_serial_number = rec_msnt_to_delete_ls.fm_serial_number;
4800:
4801: IF SQL%NOTFOUND THEN

Line 4876: UPDATE mtl_serial_numbers_temp

4872: -- Now update MSNT
4873: l_progress := 1900;
4874: mydebug('l_progress : ' || l_progress);
4875:
4876: UPDATE mtl_serial_numbers_temp
4877: SET transaction_temp_id = rec_mmtt1.group_temp_id
4878: , last_update_date = SYSDATE
4879: , last_updated_by = p_user_id
4880: WHERE transaction_temp_id

Line 4883: FROM mtl_serial_numbers_temp msnt,

4879: , last_updated_by = p_user_id
4880: WHERE transaction_temp_id
4881: IN
4882: (SELECT msnt.transaction_temp_id
4883: FROM mtl_serial_numbers_temp msnt,
4884: mtl_material_transactions_temp mmtt
4885: WHERE mmtt.transaction_header_id = p_transaction_header_id
4886: AND mmtt.transaction_temp_id <> rec_mmtt1.group_temp_id
4887: AND mmtt.transaction_temp_id = msnt.transaction_temp_id

Line 4901: UPDATE MTL_SERIAL_NUMBERS

4897: -- REMARK MSN with new temp_id
4898:
4899: l_progress := 2000;
4900: mydebug('l_progress : ' || l_progress);
4901: UPDATE MTL_SERIAL_NUMBERS
4902: SET group_mark_id = rec_mmtt1.group_temp_id
4903: , last_updated_by = p_user_id
4904: , last_update_date = SYSDATE
4905: WHERE current_organization_id = p_organization_id

Line 4910: FROM mtl_serial_numbers_temp

4906: AND inventory_item_id = rec_mmtt1.inventory_item_id
4907: AND serial_number
4908: IN
4909: (SELECT fm_serial_number
4910: FROM mtl_serial_numbers_temp
4911: WHERE transaction_temp_id = rec_mmtt1.group_temp_id);
4912: --l_serial_transaction_temp_id);
4913:
4914: IF SQL%NOTFOUND THEN

Line 4922: UPDATE MTL_SERIAL_NUMBERS

4918: ELSE -- (p_serial_allocated_flag = 'N' ,
4919: -- delete all msnts and unmark all these serials in MSN.
4920: l_progress := 2100;
4921: mydebug('l_progress : ' || l_progress);
4922: UPDATE MTL_SERIAL_NUMBERS
4923: SET group_mark_id = NULL
4924: , last_updated_by = p_user_id
4925: , last_update_date = SYSDATE
4926: WHERE (current_organization_id

Line 4932: FROM mtl_serial_numbers_temp msnt

4928: ,serial_number)
4929: IN (SELECT mmtt.organization_id
4930: ,mmtt.inventory_item_id
4931: ,msnt.fm_serial_number
4932: FROM mtl_serial_numbers_temp msnt
4933: ,mtl_material_transactions_temp mmtt
4934: WHERE mmtt.transaction_header_id = p_transaction_header_id
4935: AND mmtt.transaction_temp_id = msnt.transaction_temp_id
4936: AND mmtt.subinventory_code = rec_mmtt1.subinventory_code

Line 4949: DELETE mtl_serial_numbers_temp

4945: ELSE
4946: l_progress := 2200;
4947: mydebug('l_progress : ' || l_progress);
4948:
4949: DELETE mtl_serial_numbers_temp
4950: WHERE transaction_temp_id IN
4951: (SELECT msnt.transaction_temp_id
4952: FROM mtl_serial_numbers_temp msnt,
4953: mtl_material_transactions_temp mmtt

Line 4952: FROM mtl_serial_numbers_temp msnt,

4948:
4949: DELETE mtl_serial_numbers_temp
4950: WHERE transaction_temp_id IN
4951: (SELECT msnt.transaction_temp_id
4952: FROM mtl_serial_numbers_temp msnt,
4953: mtl_material_transactions_temp mmtt
4954: WHERE mmtt.transaction_header_id = p_transaction_header_id
4955: AND mmtt.transaction_temp_id = msnt.transaction_temp_id
4956: AND mmtt.subinventory_code = rec_mmtt1.subinventory_code

Line 4973: FROM mtl_serial_numbers_temp

4969: l_progress := 3000;
4970: mydebug('l_progress : ' || l_progress);
4971: SELECT count(*)
4972: INTO l_op_msnt_to_delete
4973: FROM mtl_serial_numbers_temp
4974: WHERE (transaction_temp_id ,
4975: fm_serial_number)
4976: IN
4977: (SELECT msnt.transaction_temp_id

Line 4979: FROM mtl_serial_numbers_temp msnt

4975: fm_serial_number)
4976: IN
4977: (SELECT msnt.transaction_temp_id
4978: ,msnt.fm_serial_number
4979: FROM mtl_serial_numbers_temp msnt
4980: ,mtl_material_transactions_temp mmtt
4981: WHERE mmtt.transaction_header_id = p_transaction_header_id
4982: AND mmtt.transaction_temp_id = msnt.transaction_temp_id
4983: AND mmtt.subinventory_code = rec_mmtt1.subinventory_code

Line 5023: UPDATE MTL_SERIAL_NUMBERS

5019: mydebug('l_op_msnt_to_delete : ' || l_op_msnt_to_delete);
5020: END IF;
5021: l_progress := 3300;
5022: mydebug('l_progress : ' || l_progress);
5023: UPDATE MTL_SERIAL_NUMBERS
5024: SET group_mark_id = NULL
5025: ,last_updated_by = p_user_id
5026: ,last_update_date = SYSDATE
5027: WHERE current_organization_id = p_organization_id

Line 5036: DELETE mtl_serial_numbers_temp

5032: RAISE fnd_api.G_EXC_ERROR;
5033: ELSE
5034: l_progress := 2200;
5035: mydebug('l_progress : ' || l_progress);
5036: DELETE mtl_serial_numbers_temp
5037: WHERE transaction_temp_id = rec_msnt_to_delete.transaction_temp_id
5038: AND fm_serial_number = rec_msnt_to_delete.fm_serial_number;
5039:
5040: IF SQL%NOTFOUND THEN

Line 6274: FROM mtl_serial_numbers msn

6270: WHERE transaction_temp_id = p_temp_id;
6271:
6272: CURSOR insert_serial_allocated_csr (p_serial_lot_number VARCHAR2) IS
6273: SELECT serial_number
6274: FROM mtl_serial_numbers msn
6275: WHERE msn.current_organization_id = p_organization_id
6276: AND msn.inventory_item_id = p_item_id
6277: AND lpn_id = p_lpn_match_lpn_id
6278: AND NVL(msn.lot_number,'@@') = NVL(p_serial_lot_number, '@@')

Line 6281: from mtl_serial_numbers_temp msnt,

6277: AND lpn_id = p_lpn_match_lpn_id
6278: AND NVL(msn.lot_number,'@@') = NVL(p_serial_lot_number, '@@')
6279: AND msn.serial_number NOT IN
6280: ( select msnt.fm_serial_number
6281: from mtl_serial_numbers_temp msnt,
6282: mtl_transaction_lots_temp mtlt,
6283: mtl_material_transactions_temp mmtt
6284: where mmtt.inventory_item_id = p_item_id
6285: AND mmtt.organization_id = p_organization_id

Line 7010: FROM mtl_serial_numbers

7006: --Bug5649056
7007: l_value VARCHAR2(3); --bug 6651517
7008: CURSOR ser_csr IS
7009: SELECT serial_number
7010: FROM mtl_serial_numbers
7011: WHERE lpn_id = p_fromlpn_id
7012: AND inventory_item_id = p_item_id
7013: AND NVL(lot_number, -999) = NVL(p_lot, -999);
7014:

Line 7530: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt

7526: END IF;
7527:
7528: SELECT COUNT(fm_serial_number)
7529: INTO l_serial_exist_cnt
7530: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
7531: WHERE mtlt.transaction_temp_id = p_temp_id
7532: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
7533: AND msnt.fm_serial_number IN(
7534: SELECT serial_number

Line 7535: FROM mtl_serial_numbers

7531: WHERE mtlt.transaction_temp_id = p_temp_id
7532: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
7533: AND msnt.fm_serial_number IN(
7534: SELECT serial_number
7535: FROM mtl_serial_numbers
7536: WHERE lpn_id = p_fromlpn_id
7537: AND inventory_item_id = p_item_id
7538: AND NVL(revision, '-999') = NVL(p_rev, '-999'));
7539:

Line 7888: FROM mtl_serial_numbers_temp msnt

7884: END IF;
7885:
7886: SELECT COUNT(fm_serial_number)
7887: INTO l_serial_exist_cnt
7888: FROM mtl_serial_numbers_temp msnt
7889: WHERE msnt.transaction_temp_id = p_temp_id
7890: AND msnt.fm_serial_number IN(
7891: SELECT serial_number
7892: FROM mtl_serial_numbers

Line 7892: FROM mtl_serial_numbers

7888: FROM mtl_serial_numbers_temp msnt
7889: WHERE msnt.transaction_temp_id = p_temp_id
7890: AND msnt.fm_serial_number IN(
7891: SELECT serial_number
7892: FROM mtl_serial_numbers
7893: WHERE lpn_id = p_fromlpn_id
7894: AND inventory_item_id = p_item_id
7895: AND NVL(revision, '-999') = NVL(p_rev, '-999'));
7896:

Line 7915: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt

7911: END IF;
7912:
7913: SELECT COUNT(fm_serial_number)
7914: INTO l_total_serial_cnt
7915: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
7916: WHERE mtlt.transaction_temp_id = p_temp_id
7917: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
7918:
7919: IF (l_debug = 1) THEN

Line 7958: FROM mtl_serial_numbers_temp msnt

7954: END IF;
7955:
7956: SELECT COUNT(fm_serial_number)
7957: INTO l_serial_exist_cnt
7958: FROM mtl_serial_numbers_temp msnt
7959: WHERE msnt.transaction_temp_id = p_temp_id
7960: AND msnt.fm_serial_number IN(
7961: SELECT serial_number
7962: FROM mtl_serial_numbers

Line 7962: FROM mtl_serial_numbers

7958: FROM mtl_serial_numbers_temp msnt
7959: WHERE msnt.transaction_temp_id = p_temp_id
7960: AND msnt.fm_serial_number IN(
7961: SELECT serial_number
7962: FROM mtl_serial_numbers
7963: WHERE lpn_id = p_fromlpn_id
7964: AND inventory_item_id = p_item_id
7965: AND NVL(revision, '-999') = NVL(p_rev, '-999'));
7966:

Line 8300: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt

8296: END IF;
8297:
8298: SELECT COUNT(fm_serial_number)
8299: INTO l_serial_exist_cnt
8300: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
8301: WHERE mtlt.transaction_temp_id = p_temp_id
8302: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
8303: AND msnt.fm_serial_number IN(
8304: SELECT serial_number

Line 8305: FROM mtl_serial_numbers

8301: WHERE mtlt.transaction_temp_id = p_temp_id
8302: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
8303: AND msnt.fm_serial_number IN(
8304: SELECT serial_number
8305: FROM mtl_serial_numbers
8306: WHERE lpn_id = p_fromlpn_id
8307: AND inventory_item_id = p_item_id
8308: AND NVL(revision, '-999') = NVL(p_rev, '-999'));
8309:

Line 8806: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt

8802: AND p_action = 4 THEN
8803:
8804: SELECT COUNT(fm_serial_number)
8805: INTO l_total_serial_cnt
8806: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt
8807: WHERE mtlt.transaction_temp_id = p_temp_id
8808: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
8809:
8810: IF (l_debug = 1) THEN

Line 8869: FROM mtl_serial_numbers_temp msnt

8865: END IF;
8866:
8867: SELECT COUNT(fm_serial_number)
8868: INTO l_serial_exist_cnt
8869: FROM mtl_serial_numbers_temp msnt
8870: WHERE msnt.transaction_temp_id = p_temp_id
8871: AND msnt.fm_serial_number IN(
8872: SELECT serial_number
8873: FROM mtl_serial_numbers

Line 8873: FROM mtl_serial_numbers

8869: FROM mtl_serial_numbers_temp msnt
8870: WHERE msnt.transaction_temp_id = p_temp_id
8871: AND msnt.fm_serial_number IN(
8872: SELECT serial_number
8873: FROM mtl_serial_numbers
8874: WHERE lpn_id = p_fromlpn_id
8875: AND inventory_item_id = p_item_id
8876: AND NVL(revision, '-999') = NVL(p_rev, '-999'));
8877:

Line 9232: FROM mtl_serial_numbers_temp msnt

9228: AND p_action = 4 THEN
9229:
9230: SELECT COUNT(fm_serial_number)
9231: INTO l_total_serial_cnt
9232: FROM mtl_serial_numbers_temp msnt
9233: WHERE msnt.transaction_temp_id = p_temp_id;
9234:
9235: IF (l_debug = 1) THEN
9236: mydebug('SN tot count' || l_total_serial_cnt);

Line 9454: FROM mtl_serial_numbers_temp msnt,

9450: serial_number,
9451: transaction_quantity,
9452: primary_quantity)
9453: SELECT mtlt.lot_number,fm_serial_number,1,1
9454: FROM mtl_serial_numbers_temp msnt,
9455: mtl_transaction_lots_temp mtlt,
9456: mtl_serial_numbers msn
9457: WHERE mtlt.transaction_temp_id = p_temp_id
9458: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id

Line 9456: mtl_serial_numbers msn

9452: primary_quantity)
9453: SELECT mtlt.lot_number,fm_serial_number,1,1
9454: FROM mtl_serial_numbers_temp msnt,
9455: mtl_transaction_lots_temp mtlt,
9456: mtl_serial_numbers msn
9457: WHERE mtlt.transaction_temp_id = p_temp_id
9458: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
9459: AND msnt.fm_serial_number = msn.serial_number
9460: AND msn.lpn_id = p_fromlpn_id

Line 9503: FROM mtl_serial_numbers_temp msnt,

9499: (serial_number,
9500: transaction_quantity,
9501: primary_quantity)
9502: SELECT fm_serial_number,1,1
9503: FROM mtl_serial_numbers_temp msnt,
9504: mtl_serial_numbers msn
9505: WHERE msnt.transaction_temp_id = p_temp_id
9506: AND msnt.fm_serial_number = msn.serial_number
9507: AND msn.lpn_id = p_fromlpn_id

Line 9504: mtl_serial_numbers msn

9500: transaction_quantity,
9501: primary_quantity)
9502: SELECT fm_serial_number,1,1
9503: FROM mtl_serial_numbers_temp msnt,
9504: mtl_serial_numbers msn
9505: WHERE msnt.transaction_temp_id = p_temp_id
9506: AND msnt.fm_serial_number = msn.serial_number
9507: AND msn.lpn_id = p_fromlpn_id
9508: AND msn.inventory_item_id = p_item_id;

Line 9540: mtl_serial_numbers msn

9536: transaction_quantity,
9537: primary_quantity)
9538: SELECT mtlt.lot_number, serial_number, 1, 1
9539: FROM mtl_transaction_lots_temp mtlt,
9540: mtl_serial_numbers msn
9541: WHERE mtlt.transaction_temp_id = p_temp_id
9542: AND msn.lpn_id = p_fromlpn_id
9543: AND mtlt.lot_number = msn.lot_number
9544: AND msn.inventory_item_id = p_item_id

Line 9552: FROM mtl_serial_numbers msn

9548: (serial_number,
9549: transaction_quantity,
9550: primary_quantity)
9551: SELECT serial_number,1,1
9552: FROM mtl_serial_numbers msn
9553: WHERE msn.lpn_id = p_fromlpn_id
9554: AND msn.inventory_item_id = p_item_id
9555: AND Nvl(msn.group_mark_id, -1) = -1;
9556: END IF;

Line 9980: FROM mtl_serial_numbers_temp msnt,

9976: serial_number,
9977: transaction_quantity,
9978: primary_quantity)
9979: SELECT mtlt.lot_number,fm_serial_number,1,1
9980: FROM mtl_serial_numbers_temp msnt,
9981: mtl_transaction_lots_temp mtlt,
9982: mtl_serial_numbers msn
9983: WHERE mtlt.transaction_temp_id = p_temp_id
9984: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id

Line 9982: mtl_serial_numbers msn

9978: primary_quantity)
9979: SELECT mtlt.lot_number,fm_serial_number,1,1
9980: FROM mtl_serial_numbers_temp msnt,
9981: mtl_transaction_lots_temp mtlt,
9982: mtl_serial_numbers msn
9983: WHERE mtlt.transaction_temp_id = p_temp_id
9984: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
9985: AND msnt.fm_serial_number = msn.serial_number
9986: AND msn.lpn_id is null -- make sure it is loose pick

Line 10010: FROM mtl_serial_numbers_temp msnt,

10006: (serial_number,
10007: transaction_quantity,
10008: primary_quantity)
10009: SELECT fm_serial_number,1,1
10010: FROM mtl_serial_numbers_temp msnt,
10011: mtl_serial_numbers msn
10012: WHERE msnt.transaction_temp_id = p_temp_id
10013: AND msnt.fm_serial_number = msn.serial_number
10014: AND msn.lpn_id is null

Line 10011: mtl_serial_numbers msn

10007: transaction_quantity,
10008: primary_quantity)
10009: SELECT fm_serial_number,1,1
10010: FROM mtl_serial_numbers_temp msnt,
10011: mtl_serial_numbers msn
10012: WHERE msnt.transaction_temp_id = p_temp_id
10013: AND msnt.fm_serial_number = msn.serial_number
10014: AND msn.lpn_id is null
10015: AND msn.inventory_item_id = p_item_id;

Line 11961: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,

11957: l_count := 0;
11958: BEGIN
11959: SELECT 1
11960: INTO l_count
11961: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,
11962: mtl_material_transactions_temp mmtt
11963: WHERE (p_from_serial BETWEEN msnt.fm_serial_number AND msnt.to_serial_number
11964: OR p_to_serial BETWEEN msnt.fm_serial_number AND msnt.to_serial_number)
11965: AND mmtt.inventory_item_id = p_item_id

Line 12020: UPDATE mtl_serial_numbers

12016: );
12017: l_progress := '70';
12018:
12019: BEGIN
12020: UPDATE mtl_serial_numbers
12021: SET group_mark_id = p_serial_transaction_temp_id
12022: WHERE inventory_item_id = p_item_id
12023: AND serial_number BETWEEN p_from_serial AND p_to_serial
12024: AND LENGTH(serial_number) = LENGTH(p_from_serial);