[Home] [Help]
135: ,x_msg_count OUT NOCOPY NUMBER
136: ,x_msg_data OUT NOCOPY VARCHAR2);
137:
138: PROCEDURE proc_insert_mtlt
139: (p_lot_record IN mtl_transaction_lots_temp%ROWTYPE
140: ,x_return_status OUT NOCOPY VARCHAR2
141: ,x_msg_count OUT NOCOPY NUMBER
142: ,x_msg_data OUT NOCOPY VARCHAR2);
143:
306: l_transaction_uom2 VARCHAR2(3);
307: l_conversion_factor NUMBER;
308: l_conversion_factor1 NUMBER;
309: l_conversion_factor2 NUMBER;
310: l_mtlt_rec mtl_transaction_lots_temp%ROWTYPE;
311: l_row_exists NUMBER;
312: i NUMBER;
313: j NUMBER;
314: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
347:
348: CURSOR mtlt_cursor(v_transaction_temp_id NUMBER) IS
349: SELECT lot_number, primary_quantity, transaction_quantity, secondary_quantity
350: , serial_transaction_temp_id
351: FROM mtl_transaction_lots_temp
352: WHERE transaction_temp_id = v_transaction_temp_id
353: ORDER BY lot_number
354: FOR UPDATE;
355:
527:
528: BEGIN
529: SELECT serial_transaction_temp_id
530: INTO l_serial_transaction_temp_id
531: FROM mtl_transaction_lots_temp
532: WHERE transaction_temp_id = p_transaction_temp_id2
533: AND lot_number = l_lot_table1(i).lot_number;
534:
535: l_row_exists := 1;
559: IF (l_debug = 1) THEN
560: mydebug(i || 'Deleting Lot Number: ' || l_lot_table1(i).lot_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
561: END IF;
562:
563: UPDATE mtl_transaction_lots_temp
564: SET primary_quantity = primary_quantity + l_lot_table1(i).delta_primary_quantity,
565: transaction_quantity = transaction_quantity +
566: Round(l_lot_table1(i).delta_primary_quantity * l_conversion_factor2,
567: l_g_decimal_precision),
570: last_updated_by = p_user_id
571: WHERE transaction_temp_id = p_transaction_temp_id2
572: AND lot_number = l_lot_table1(i).lot_number;
573:
574: DELETE FROM mtl_transaction_lots_temp
575: WHERE transaction_temp_id = p_transaction_temp_id1
576: AND lot_number = l_lot_table1(i).lot_number;
577:
578: ELSE -- row does not exist in 2
580: IF (l_debug = 1) THEN
581: mydebug(i || 'Transferring Lot Number: ' || l_lot_table1(i).lot_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
582: END IF;
583:
584: UPDATE mtl_transaction_lots_temp
585: SET transaction_temp_id = p_transaction_temp_id2,
586: transaction_quantity = Round(primary_quantity * l_conversion_factor2, l_g_decimal_precision),
587: last_update_date = Sysdate,
588: last_updated_by = p_user_id
592: END IF;
593:
594: ELSIF l_lot_table1(i).update_mtlt THEN
595:
596: UPDATE mtl_transaction_lots_temp
597: SET primary_quantity = l_lot_table1(i).primary_quantity,
598: transaction_quantity = Round(l_lot_table1(i).primary_quantity * l_conversion_factor, l_g_decimal_precision),
599: secondary_quantity = l_lot_table1(i).secondary_quantity,
600: last_update_date = Sysdate,
607: IF (l_debug = 1) THEN
608: mydebug(i || 'Updating Lot Number: ' || l_lot_table1(i).lot_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
609: END IF;
610:
611: UPDATE mtl_transaction_lots_temp
612: SET primary_quantity = primary_quantity + l_lot_table1(i).delta_primary_quantity,
613: transaction_quantity = transaction_quantity + Round(l_lot_table1(i).delta_primary_quantity * l_conversion_factor2,
614: l_g_decimal_precision),
615: secondary_quantity = secondary_quantity + l_lot_table1(i).delta_secondary_quantity,
624: END IF;
625:
626: SELECT *
627: INTO l_mtlt_rec
628: FROM mtl_transaction_lots_temp
629: WHERE transaction_temp_id = p_transaction_temp_id1
630: AND lot_number = l_lot_table1(i).lot_number;
631:
632: l_mtlt_rec.transaction_temp_id := p_transaction_temp_id2;
880:
881: BEGIN
882: SELECT serial_transaction_temp_id
883: INTO l_serial_transaction_temp_id
884: FROM mtl_transaction_lots_temp
885: WHERE transaction_temp_id = p_transaction_temp_id2
886: AND lot_number = l_lot_table1(i).lot_number;
887:
888: l_row_exists := 1;
957: IF (l_debug = 1) THEN
958: mydebug(i || 'Deleting Lot Number: ' || l_lot_table1(i).lot_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
959: END IF;
960:
961: UPDATE mtl_transaction_lots_temp
962: SET primary_quantity = primary_quantity + l_lot_table1(i).delta_primary_quantity,
963: transaction_quantity = transaction_quantity +
964: Round(l_lot_table1(i).delta_primary_quantity * l_conversion_factor2,
965: l_g_decimal_precision),
967: last_updated_by = p_user_id
968: WHERE transaction_temp_id = p_transaction_temp_id2
969: AND lot_number = l_lot_table1(i).lot_number;
970:
971: DELETE FROM mtl_transaction_lots_temp
972: WHERE transaction_temp_id = p_transaction_temp_id1
973: AND lot_number = l_lot_table1(i).lot_number;
974:
975: ELSE -- row does not exist in 2
977: IF (l_debug = 1) THEN
978: mydebug(i || 'Transferring Lot Number: ' || l_lot_table1(i).lot_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
979: END IF;
980:
981: UPDATE mtl_transaction_lots_temp
982: SET transaction_temp_id = p_transaction_temp_id2,
983: serial_transaction_temp_id = l_serial_transaction_temp_id,
984: transaction_quantity = Round(primary_quantity * l_conversion_factor2, l_g_decimal_precision),
985: last_update_date = Sysdate,
990: END IF;
991:
992: ELSIF l_lot_table1(i).update_mtlt THEN
993:
994: UPDATE mtl_transaction_lots_temp
995: SET primary_quantity = l_lot_table1(i).primary_quantity,
996: transaction_quantity = Round(l_lot_table1(i).primary_quantity * l_conversion_factor, l_g_decimal_precision),
997: secondary_quantity = l_lot_table1(i).secondary_quantity,
998: last_update_date = Sysdate,
1005: IF (l_debug = 1) THEN
1006: mydebug(i || 'Updating Lot Number: ' || l_lot_table1(i).lot_number, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
1007: END IF;
1008:
1009: UPDATE mtl_transaction_lots_temp
1010: SET primary_quantity = primary_quantity + l_lot_table1(i).delta_primary_quantity,
1011: transaction_quantity = transaction_quantity + Round(l_lot_table1(i).delta_primary_quantity * l_conversion_factor2,
1012: l_g_decimal_precision),
1013: last_update_date = Sysdate,
1021: END IF;
1022:
1023: SELECT *
1024: INTO l_mtlt_rec
1025: FROM mtl_transaction_lots_temp
1026: WHERE transaction_temp_id = p_transaction_temp_id1
1027: AND lot_number = l_lot_table1(i).lot_number;
1028:
1029: l_mtlt_rec.transaction_temp_id := p_transaction_temp_id2;
2503: mydebug('l_progress : ' || l_progress);
2504: IF p_exception='OVER' and p_lot_controlled='Y' THEN
2505: select sum(primary_quantity),sum(transaction_quantity),sum(SECONDARY_QUANTITY)
2506: into l_rem_lot_pri_qty,l_rem_lot_trx_qty,l_rem_lot_sec_qty
2507: from mtl_transaction_lots_temp
2508: where transaction_temp_id = p_transaction_temp_id
2509: group by transaction_temp_id;
2510:
2511:
2675:
2676: CURSOR cur_mtlt_to_copy_from (p_lot_number VARCHAR2
2677: ,p_lot_transaction_temp_id NUMBER ) IS
2678: SELECT mtlt.*
2679: FROM mtl_transaction_lots_temp mtlt
2680: WHERE transaction_temp_id = p_lot_transaction_temp_id
2681: AND lot_number = p_lot_number;
2682:
2683: l_rec_mtlt_to_copy_from mtl_transaction_lots_temp%ROWTYPE;
2679: FROM mtl_transaction_lots_temp mtlt
2680: WHERE transaction_temp_id = p_lot_transaction_temp_id
2681: AND lot_number = p_lot_number;
2682:
2683: l_rec_mtlt_to_copy_from mtl_transaction_lots_temp%ROWTYPE;
2684:
2685: CURSOR cur_confirmed_lots_serials IS
2686: SELECT DISTINCT -- so that we get only lot records in case of lot+Serial item control
2687: lot_number
2808: -- lot qty in the selected, in the above cursor, MTLT equals qty that is needed for the new MTLT
2809: -- update the MTLT with new temp id instead of inserting a new and then deleting the old one
2810: l_progress := '200';
2811: mydebug('l_progress: ' || l_progress );
2812: UPDATE mtl_transaction_lots_temp
2813: SET transaction_temp_id = p_new_transaction_temp_id
2814: , transaction_quantity = rec_confirmed_lots_serials.transaction_Quantity
2815: , primary_quantity = rec_confirmed_lots_serials.primary_quantity
2816: , secondary_quantity = rec_confirmed_lots_serials.secondary_quantity
2849: -- If new MTLT is inserted for p_new_transaction_temp_id this means
2850: -- the original MTLT still has some qty remaining...so update it.
2851: -- the original MTLT need not be adjusted for secondary quantity.
2852: -- It is not expected to be populated
2853: UPDATE mtl_transaction_lots_temp
2854: SET transaction_quantity = transaction_quantity - rec_confirmed_lots_serials.suggested_quantity
2855: ,primary_quantity = primary_quantity - rec_confirmed_lots_serials.primary_quantity
2856: ,secondary_quantity = NVL(secondary_quantity, rec_confirmed_lots_serials.secondary_quantity) - rec_confirmed_lots_serials.secondary_quantity
2857: ,last_update_date = SYSDATE
2874: IF p_update = 'Y2'
2875: THEN
2876: l_progress := '240';
2877: mydebug('l_progress: ' || l_progress );
2878: UPDATE mtl_transaction_lots_temp
2879: SET transaction_quantity = transaction_quantity +
2880: rec_confirmed_lots_serials.transaction_Quantity
2881: ,primary_quantity = primary_quantity + rec_confirmed_lots_serials.primary_quantity
2882: ,secondary_quantity = secondary_quantity+ rec_confirmed_lots_serials.secondary_quantity
2893: -- lot qty in the selected MTLT = qty that is needed for the new MTLT
2894: --update the MTLT with new temp id instead of inserting a new and then deleting the old one
2895: l_progress := '250';
2896: mydebug('l_progress: ' || l_progress );
2897: UPDATE mtl_transaction_lots_temp
2898: SET transaction_temp_id = p_transaction_temp_id_to_merge --p_new_transaction_temp_id
2899: , secondary_quantity = rec_confirmed_lots_serials.secondary_quantity
2900: , secondary_unit_of_measure = p_confirmed_sec_uom
2901: -- For lot + serial controlled items
2929: L_progress := '270';
2930: mydebug('l_progress: ' || l_progress );
2931: -- If new MTLT is inserted for p_transaction_temp_id_to_merge this means
2932: -- the original MTLT still has some qty remaining...so update it.
2933: UPDATE mtl_transaction_lots_temp
2934: SET transaction_quantity = transaction_quantity -
2935: rec_confirmed_lots_serials.suggested_Quantity
2936: ,primary_quantity = primary_quantity -
2937: rec_confirmed_lots_serials.primary_quantity
2954: L_progress := '290';
2955: mydebug('l_progress: ' || l_progress );
2956: -- If all the qty from the original MTLT is consumed and merged to the
2957: -- p_transaction_temp_id_to_merge we do not need the original MTLT ..delete it
2958: DELETE mtl_transaction_lots_temp
2959: WHERE transaction_temp_id = p_transaction_temp_id -- l_rec_mtlt_to_copy_from.transaction_temp_id
2960: AND lot_number = rec_confirmed_lots_serials.lot_number;
2961: IF SQL%NOTFOUND THEN
2962: RAISE fnd_api.G_EXC_ERROR;
2965: -- If all the qty from the original MTLT is not consumed then update the original MTLT
2966: -- attached to p_transaction_temp_id .. as selected in the cursor above
2967: L_progress := '300';
2968: mydebug('l_progress: ' || l_progress );
2969: UPDATE mtl_transaction_lots_temp
2970: SET transaction_quantity = transaction_quantity - rec_confirmed_lots_serials.suggested_Quantity
2971: ,primary_quantity = primary_quantity - rec_confirmed_lots_serials.primary_quantity
2972: ,secondary_quantity = NVL(secondary_quantity, rec_confirmed_lots_serials.secondary_quantity) -
2973: rec_confirmed_lots_serials.secondary_quantity
2993: p_lpn_match IN (1,3) )
2994: THEN
2995: l_progress := '350';
2996: mydebug('l_progress: ' || l_progress );
2997: UPDATE mtl_transaction_lots_temp
2998: SET
2999: transaction_quantity = rec_confirmed_lots_serials.transaction_quantity --jxlu
3000: ,primary_quantity = rec_confirmed_lots_serials.primary_quantity --jxlu
3001: ,secondary_quantity = rec_confirmed_lots_serials.secondary_quantity
3023: p_exception = 'OVER')
3024: THEN
3025: l_progress := '360';
3026: mydebug('l_progress: ' || l_progress );
3027: UPDATE mtl_transaction_lots_temp
3028: SET
3029: transaction_quantity = rec_confirmed_lots_serials.transaction_quantity
3030: ,primary_quantity = rec_confirmed_lots_serials.primary_quantity
3031: ,secondary_quantity = rec_confirmed_lots_serials.secondary_quantity
3400:
3401: END proc_process_confirmed_serials;
3402:
3403: PROCEDURE proc_insert_mtlt
3404: ( p_lot_record IN mtl_transaction_lots_temp%ROWTYPE
3405: ,x_return_status OUT NOCOPY VARCHAR2
3406: ,x_msg_count OUT NOCOPY NUMBER
3407: ,x_msg_data OUT NOCOPY VARCHAR2)
3408: IS
3410: l_progress VARCHAR2(30) := '100';
3411: BEGIN
3412: mydebug('In.. ' || l_proc_name);
3413: x_return_status := l_g_ret_sts_success;
3414: INSERT INTO mtl_transaction_lots_temp
3415: (transaction_temp_id
3416: ,last_update_date
3417: ,last_updated_by
3418: ,creation_date
4308: l_new_transaction_temp_id NUMBER := NULL;
4309: l_serial_transaction_temp_id NUMBER := NULL;
4310: l_transaction_temp_id NUMBER := NULL;
4311: l_original_sub_loc VARCHAR2(1) := 'N';
4312: l_rec_mtlt_to_copy_from mtl_transaction_lots_temp%ROWTYPE;
4313: l_suggested_mmtt_qty NUMBER := 0;
4314: l_suggested_mtlt_qty NUMBER := 0;
4315: -- bug #4141928 INV CONV
4316: l_suggested_mmtt_sec_qty NUMBER := 0;
4360: , sum(mtlt.secondary_quantity) group_lot_secondary_quantity
4361: ,COUNT(*) group_lot_count
4362: ,MIN(mtlt.transaction_temp_id) group_lot_temp_id
4363: ,mtlt.lot_number
4364: FROM mtl_transaction_lots_temp mtlt
4365: , mtl_material_transactions_temp mmtt
4366: WHERE mmtt.transaction_header_id = p_transaction_header_id
4367: AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
4368: AND mmtt.subinventory_code = p_subinventory_code
4663: , last_update_date = SYSDATE
4664: , last_updated_by = p_user_id
4665: WHERE transaction_temp_id IN
4666: (SELECT mtlt.serial_transaction_temp_id
4667: FROM mtl_transaction_lots_temp mtlt
4668: , mtl_material_transactions_temp mmtt
4669: WHERE mmtt.transaction_header_id = p_transaction_header_id
4670: AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
4671: AND mmtt.subinventory_code = rec_mmtt1.subinventory_code
4707: ,serial_number)
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
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
4736: , mtl_material_transactions_temp mmtt
4737: WHERE mmtt.transaction_header_id = p_transaction_header_id
4738: AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
4739: AND mmtt.subinventory_code = rec_mmtt1.subinventory_code
4811: with serial-transaction-temp_id and user_id, sysdate.
4812: So, there is no harm is updating qty too */
4813: l_progress := 1500;
4814: mydebug('l_progress .. ' || l_progress);
4815: UPDATE MTL_transaction_lots_temp mtlt
4816: SET transaction_temp_id = rec_mmtt1.group_temp_id
4817: , primary_quantity = rec_mtlt1.group_lot_primary_quantity
4818: , transaction_quantity = l_suggested_mtlt_qty
4819: , secondary_quantity = decode (secondary_quantity, null, null, l_suggested_mtlt_sec_qty)
4830: mydebug('l_progress .. ' || l_progress);
4831: IF rec_mtlt1.group_lot_count > 1 THEN
4832: l_progress := 230;
4833: mydebug('l_progress .. ' || l_progress);
4834: DELETE mtl_transaction_lots_temp
4835: WHERE lot_number = rec_mtlt1.lot_number
4836: AND transaction_temp_id
4837: IN
4838: (SELECT mtlt.transaction_temp_id
4836: AND transaction_temp_id
4837: IN
4838: (SELECT mtlt.transaction_temp_id
4839: FROM mtl_material_transactions_temp mmtt
4840: ,mtl_transaction_lots_temp mtlt
4841: WHERE mmtt.transaction_header_id = p_transaction_header_id
4842: AND mmtt.transaction_temp_id <> rec_mtlt1.group_lot_temp_id
4843: AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
4844: AND mtlt.lot_number = rec_mtlt1.lot_number
6265: WHERE mmtt.transaction_header_id = p_transaction_header_id;
6266:
6267: CURSOR lot_csr IS
6268: SELECT lot_number, serial_transaction_temp_id
6269: FROM mtl_transaction_lots_temp
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
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
6286: and mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
7015: CURSOR lot_csr IS
7016: SELECT mtlt.primary_quantity
7017: , NVL(mtlt.secondary_quantity, 0) -- Bug #4141928
7018: , mtlt.lot_number
7019: FROM mtl_transaction_lots_temp mtlt
7020: WHERE mtlt.transaction_temp_id = p_temp_id
7021: ORDER BY LOT_NUMBER;
7022:
7023: --jxlu 10/12/04
7390: INTO l_item_cnt
7391: FROM DUAL
7392: WHERE EXISTS(
7393: SELECT 1
7394: FROM wms_lpn_contents wlc, mtl_transaction_lots_temp mtlt
7395: WHERE wlc.parent_lpn_id = p_fromlpn_id
7396: AND wlc.organization_id = p_org_id
7397: AND wlc.inventory_item_id = p_item_id
7398: AND NVL(wlc.revision, '-999') = NVL(p_rev, '-999')
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
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
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
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
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
9459: AND msnt.fm_serial_number = msn.serial_number
9535: serial_number,
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
9922: SELECT mtlt.primary_quantity
9923: , mtlt.transaction_quantity
9924: , NVL(mtlt.secondary_quantity, 0) -- Bug #4141928
9925: , mtlt.lot_number
9926: FROM mtl_transaction_lots_temp mtlt
9927: WHERE mtlt.transaction_temp_id = p_temp_id
9928: ORDER BY LOT_NUMBER;
9929:
9930: CURSOR lot_att IS
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
9985: AND msnt.fm_serial_number = msn.serial_number
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
11983: l_progress := '40';
11984:
11985: SELECT serial_transaction_temp_id
11986: INTO p_serial_transaction_temp_id
11987: FROM mtl_transaction_lots_temp
11988: WHERE transaction_temp_id = p_transaction_temp_id
11989: AND lot_number= p_lot;
11990:
11991: IF p_serial_transaction_temp_id IS NULL THEN
11995: FROM DUAL;
11996:
11997: l_progress := '50';
11998:
11999: UPDATE mtl_transaction_lots_temp
12000: SET serial_transaction_temp_id = p_serial_transaction_temp_id
12001: WHERE transaction_temp_id = p_transaction_temp_id
12002: AND lot_number= p_lot;
12003:
12164: l_lot,
12165: l_transaction_action_id, -- Bug 4632519
12166: l_transaction_type_id -- Bug 4632519
12167: from mtl_material_Transactions_temp mmtt,
12168: mtl_transaction_lots_temp mtlt
12169: where mmtt.inventory_item_id = p_inventory_item_id
12170: and mmtt.organization_id = p_organization_id
12171: and mmtt.transfer_lpn_id = p_transfer_lpn_id
12172: and mmtt.content_lpn_id is null