[Home] [Help]
319: primary_quantity NUMBER,
320: delta_primary_quantity NUMBER,
321: secondary_quantity NUMBER,
322: delta_secondary_quantity NUMBER,
323: secondary_uom mtl_material_transactions_temp.secondary_uom_code%TYPE,
324: serial_transaction_temp_id NUMBER,
325: update_mtlt BOOLEAN,
326: delete_mtlt BOOLEAN);
327:
340: CURSOR mmtt_cursor(v_transaction_temp_id1 NUMBER, v_transaction_temp_id2 NUMBER) IS
341: SELECT inventory_item_id, transaction_temp_id, primary_quantity
342: , item_primary_uom_code, transaction_quantity, transaction_uom
343: , secondary_transaction_quantity, secondary_uom_code
344: FROM mtl_material_transactions_temp
345: WHERE transaction_temp_id IN (v_transaction_temp_id1, v_transaction_temp_id2)
346: FOR UPDATE;
347:
348: CURSOR mtlt_cursor(v_transaction_temp_id NUMBER) IS
1058: mydebug('Updating MMTT with delta qty: ' || l_delta_primary_quantity, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
1059: mydebug('Updating MMTT with delta sec qty: ' || l_delta_secondary_quantity, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
1060: END IF;
1061:
1062: UPDATE mtl_material_transactions_temp
1063: SET transaction_quantity = Round(l_primary_quantity*l_conversion_factor, l_g_decimal_precision),
1064: transaction_uom = p_transaction_uom,
1065: primary_quantity = l_primary_quantity,
1066: secondary_transaction_quantity = l_secondary_quantity,
1080: , last_updated_by = fnd_global.user_id
1081: WHERE lpn_id = l_lpn_id;
1082:
1083: -- Update remaining MMTT record
1084: UPDATE mtl_material_transactions_temp
1085: SET transaction_quantity = transaction_quantity + Round(l_delta_primary_quantity*l_conversion_factor2, l_g_decimal_precision),
1086: primary_quantity = primary_quantity + l_delta_primary_quantity,
1087: secondary_transaction_quantity = secondary_transaction_quantity+l_delta_secondary_quantity,
1088: last_update_date = Sysdate,
1171:
1172: CURSOR cur_split_mmtts
1173: IS
1174: SELECT transaction_temp_id
1175: FROM mtl_material_transactions_temp
1176: WHERE transaction_header_id = p_transaction_header_id
1177: AND transaction_temp_id <> p_transaction_temp_id
1178: AND inventory_item_id = p_inventory_item_id
1179: AND nvl(revision,'@@') = nvl(p_revision,'@@')
1885: x_new_transaction_temp_id := l_new_transaction_temp_id;
1886:
1887: mydebug(l_proc_name || ': l_new_transaction_temp_id = ' || l_new_transaction_temp_id);
1888:
1889: INSERT INTO mtl_material_transactions_temp
1890: ( TRANSACTION_HEADER_ID
1891: ,TRANSACTION_TEMP_ID
1892: ,SOURCE_CODE
1893: ,SOURCE_LINE_ID
2354: ,INTRANSIT_ACCOUNT
2355: ,FOB_POINT
2356: ,MOVE_ORDER_HEADER_ID
2357: ,SERIAL_ALLOCATED_FLAG
2358: FROM mtl_material_transactions_temp
2359: WHERE transaction_temp_id = p_transaction_temp_id);
2360: IF SQL%NOTFOUND THEN
2361: mydebug (' p_transaction_temp_id: NOT found : ' || p_transaction_temp_id);
2362: fnd_message.set_name('WMS', 'WMS_INSERT_ALLOCATION'); -- NEWMSG
2508: where transaction_temp_id = p_transaction_temp_id
2509: group by transaction_temp_id;
2510:
2511:
2512: UPDATE mtl_material_transactions_temp
2513: SET transaction_quantity = l_rem_lot_trx_qty
2514: , primary_quantity = l_rem_lot_pri_qty
2515: , secondary_transaction_quantity = l_rem_lot_sec_qty
2516: , last_update_date = SYSDATE
2516: , last_update_date = SYSDATE
2517: , last_updated_by = p_user_id
2518: WHERE transaction_temp_id = p_transaction_temp_id;
2519: ELSE
2520: UPDATE mtl_material_transactions_temp
2521: SET transaction_quantity = transaction_quantity - l_confirmed_sugg_qty
2522: , primary_quantity = primary_quantity - l_confirmed_prim_qty
2523: , secondary_transaction_quantity = secondary_transaction_quantity - l_confirmed_sec_qty
2524: , last_update_date = SYSDATE
2531: END IF;
2532: ELSE -- LOAD
2533: l_progress := '200';
2534: mydebug('l_progress : ' || l_progress);
2535: UPDATE mtl_material_transactions_temp
2536: SET transaction_quantity = p_confirmed_trx_qty
2537: , primary_quantity = l_confirmed_prim_qty
2538: , secondary_transaction_quantity = decode( p_confirmed_sec_uom,
2539: null,
2566: IF p_update = 'Y2' -- -- and update p_transaction_temp_id_to_merge to add qty)
2567: THEN
2568: l_progress := '190';
2569: mydebug ('l_progress ' || l_progress);
2570: UPDATE mtl_material_transactions_temp
2571: SET transaction_quantity = transaction_quantity + p_confirmed_trx_qty
2572: , primary_quantity = primary_quantity + nvl(l_confirmed_prim_qty ,0)
2573: , secondary_transaction_quantity = secondary_transaction_quantity + nvl(l_confirmed_sec_qty, 0)
2574: , secondary_uom_code = p_confirmed_sec_uom
2580: END IF;
2581: IF p_action = l_g_action_load_multiple THEN
2582: l_progress := '190'; -- Delete the original MMTT, if merging into another MMTT
2583: mydebug ('l_progress ' || l_progress);
2584: DELETE mtl_material_transactions_temp
2585: WHERE transaction_temp_id = p_transaction_temp_id;
2586: IF SQL%NOTFOUND THEN
2587: RAISE fnd_api.G_EXC_ERROR;
2588: END IF;
2588: END IF;
2589: ELSE -- 'SPLIT'
2590: l_progress := '200';
2591: mydebug('l_progress : ' || l_progress);
2592: UPDATE mtl_material_transactions_temp
2593: SET transaction_quantity = transaction_quantity - l_confirmed_sugg_qty
2594: , primary_quantity = primary_quantity - l_confirmed_prim_qty
2595: , secondary_transaction_quantity = secondary_transaction_quantity - l_confirmed_sec_qty
2596: , last_update_date = SYSDATE
4338: , revision
4339: , subinventory_code
4340: , locator_id
4341: , item_primary_uom_code
4342: FROM mtl_material_transactions_temp mmtt
4343: WHERE mmtt.transaction_header_id = p_transaction_header_id
4344: GROUP BY
4345: inventory_item_id
4346: ,revision
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
4369: AND mmtt.locator_id = p_locator_id
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
4392: AND mmtt.locator_id = p_rec_mmtt1_locator_id
4569:
4570: /* Update the MMTT record with transacttion-temp_id = group_temp_id.
4571: Updating transaction_quantity same as primary_quantity since
4572: the transaction_qty should be IN same uom AS primary qty FOR this mmtt */
4573: UPDATE mtl_material_transactions_temp
4574: SET primary_quantity = rec_mmtt1.mmtt_primary_quantity
4575: , transaction_quantity = l_suggested_mmtt_qty
4576: , secondary_transaction_quantity = DECODE(secondary_transaction_quantity, NULL, NULL, l_suggested_mmtt_sec_qty)
4577: , transaction_uom = p_suggested_uom
4588: END IF;
4589:
4590: -- Bug# 4185621: update child line posting flag back to 'Y' for bulk picking
4591: IF (l_parent_posting_flag = 'N') THEN
4592: UPDATE mtl_material_transactions_temp mmtt
4593: SET posting_flag = 'Y'
4594: WHERE parent_line_id = rec_mmtt1.group_temp_id
4595: AND parent_line_id <> transaction_temp_id;
4596: END IF;
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
4672: AND mmtt.locator_id = rec_mmtt1.locator_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
4717: AND mmtt.subinventory_code = rec_mmtt1.subinventory_code
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
4740: AND mmtt.locator_id = rec_mmtt1.locator_id
4835: WHERE lot_number = rec_mtlt1.lot_number
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
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
4888: AND mmtt.subinventory_code = rec_mmtt1.subinventory_code
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
4937: AND mmtt.locator_id = rec_mmtt1.locator_id
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
4957: AND mmtt.locator_id = rec_mmtt1.locator_id
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
4984: AND mmtt.locator_id = rec_mmtt1.locator_id
5113:
5114: DELETE wms_dispatched_tasks
5115: WHERE transaction_temp_id IN
5116: (SELECT transaction_temp_id
5117: FROM mtl_material_transactions_temp
5118: WHERE transaction_header_id = p_transaction_header_id
5119: AND transaction_temp_id NOT IN
5120: (SELECT transaction_temp_id
5121: FROM mtl_allocations_gtmp));
5147: END IF;
5148:
5149: l_progress := 2500;
5150: mydebug('l_progress .. ' || l_progress);
5151: DELETE mtl_material_transactions_temp
5152: WHERE transaction_header_id = p_transaction_header_id
5153: AND transaction_temp_id NOT IN
5154: (SELECT transaction_temp_id
5155: FROM mtl_allocations_gtmp);
5334: -- Select all MOLs that are cancelled , so that cancelled tasks can be reduced
5335: CURSOR cur_cancelled_MOLs IS
5336: SELECT mtrl.line_id
5337: , mtrl.uom_code
5338: FROM mtl_material_transactions_temp mmtt
5339: , mtl_txn_request_lines mtrl
5340: WHERE (mmtt.transaction_temp_id = p_transaction_temp_id
5341: -- shld add : and mmtt.mmtt.transaction_temp_id <> mmtt.parent_line_id
5342: OR mmtt.parent_line_id = p_transaction_temp_id)
5352: , mmtt.inventory_item_id
5353: , mmtt.primary_quantity
5354: , mmtt.item_primary_uom_code
5355: , NVL(mmtt.secondary_transaction_quantity, 0) secondary_transaction_quantity
5356: FROM mtl_material_transactions_temp mmtt
5357: WHERE mmtt.move_order_line_id = p_mo_line_id
5358: AND NOT EXISTS(SELECT 1
5359: FROM mtl_material_transactions_temp t1
5360: WHERE t1.parent_line_id = mmtt.transaction_temp_id)
5355: , NVL(mmtt.secondary_transaction_quantity, 0) secondary_transaction_quantity
5356: FROM mtl_material_transactions_temp mmtt
5357: WHERE mmtt.move_order_line_id = p_mo_line_id
5358: AND NOT EXISTS(SELECT 1
5359: FROM mtl_material_transactions_temp t1
5360: WHERE t1.parent_line_id = mmtt.transaction_temp_id)
5361: AND NOT EXISTS(SELECT 1
5362: FROM wms_dispatched_tasks wdt
5363: WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id);
5408: -- all MMTTs for the given MOL, this MMTT should not have any child records
5409:
5410: SELECT count(*)
5411: INTO l_mmtt_count
5412: FROM mtl_material_transactions_temp mmtt
5413: WHERE mmtt.move_order_line_id = rec_cancelled_mols.line_id
5414: AND NOT EXISTS ( SELECT 1
5415: FROM mtl_material_transactions_temp t1
5416: WHERE t1.parent_line_id = mmtt.transaction_temp_id);
5411: INTO l_mmtt_count
5412: FROM mtl_material_transactions_temp mmtt
5413: WHERE mmtt.move_order_line_id = rec_cancelled_mols.line_id
5414: AND NOT EXISTS ( SELECT 1
5415: FROM mtl_material_transactions_temp t1
5416: WHERE t1.parent_line_id = mmtt.transaction_temp_id);
5417:
5418: UPDATE mtl_txn_request_lines
5419: SET quantity_detailed =(quantity_detailed - l_deleted_mmtt_qty)
5475: l_lpn_context_packing CONSTANT NUMBER := WMS_Container_PUB.LPN_CONTEXT_PACKING ;
5476:
5477: CURSOR cur_from_lpns IS
5478: SELECT DISTINCT lpn_id
5479: FROM mtl_material_transactions_temp
5480: WHERE transaction_header_id = p_transaction_header_id
5481: AND lpn_id IS NOT NULL;
5482:
5483: CURSOR cur_content_lpns IS
5481: AND lpn_id IS NOT NULL;
5482:
5483: CURSOR cur_content_lpns IS
5484: SELECT DISTINCT content_lpn_id
5485: FROM mtl_material_transactions_temp
5486: WHERE transaction_header_id = p_transaction_header_id
5487: AND content_lpn_id IS NOT NULL;
5488:
5489: --modified for bug 6642448
5488:
5489: --modified for bug 6642448
5490: CURSOR cur_transfer_lpns IS
5491: SELECT DISTINCT transfer_lpn_id
5492: FROM mtl_material_transactions_temp
5493: WHERE transaction_header_id = p_transaction_header_id
5494: AND nvl(content_lpn_id , nvl(lpn_id,-999)) <> transfer_lpn_id;
5495:
5496: BEGIN
5510: SELECT 1
5511: INTO l_other_tasks
5512: FROM DUAL
5513: WHERE EXISTS(SELECT 1
5514: FROM mtl_material_transactions_temp
5515: WHERE transaction_header_id <> p_transaction_header_id
5516: AND transfer_lpn_id = rec_transfer_lpns.transfer_lpn_id);
5517: EXCEPTION
5518: WHEN NO_DATA_FOUND THEN
5568: SELECT 1
5569: INTO l_other_tasks
5570: FROM DUAL
5571: WHERE EXISTS(SELECT 1
5572: FROM mtl_material_transactions_temp
5573: WHERE transaction_header_id <> p_transaction_header_id
5574: AND content_lpn_id = rec_content_lpns.content_lpn_id);
5575: EXCEPTION
5576: WHEN NO_DATA_FOUND THEN
5657:
5658: DELETE wms_dispatched_tasks
5659: WHERE transaction_temp_id IN
5660: (SELECT transaction_temp_id
5661: FROM mtl_material_transactions_temp
5662: WHERE transaction_header_id = p_transaction_header_id);
5663: IF SQL%NOTFOUND THEN
5664: mydebug('no WDTs to delete for this header:' );
5665: ELSE
5697: WHERE person_id = p_employee_id
5698: AND ( status = l_g_task_dispatched OR
5699: status = l_g_task_active) -- IN (3,9 ) ;
5700: AND transaction_temp_id in (SELECT transaction_temp_id
5701: FROM mtl_material_transactions_temp
5702: WHERE transaction_header_id = p_transaction_header_id);
5703:
5704: IF SQL%NOTFOUND THEN
5705: mydebug('no WDT to update for this employee id for this header id with stat in ( 3,9) ' );
6260: l_mmtt_qty_in_mol_uom NUMBER; --Total MMTT qty in MOL UOM
6261:
6262: CURSOR mmtt_csr2(p_transaction_header_id NUMBER) IS
6263: SELECT mmtt.transaction_temp_id
6264: FROM mtl_material_transactions_temp mmtt
6265: WHERE mmtt.transaction_header_id = p_transaction_header_id;
6266:
6267: CURSOR lot_csr IS
6268: SELECT lot_number, serial_transaction_temp_id
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
6287: AND msnt.transaction_temp_id = NVL(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
6364: --Get the move_order_line_id for the current task
6365: BEGIN
6366: SELECT move_order_line_id
6367: INTO l_mo_line_id
6368: FROM mtl_material_transactions_temp
6369: WHERE transaction_temp_id = p_temp_id;
6370: EXCEPTION
6371: WHEN OTHERS THEN
6372: l_mo_line_id := NULL;
6749:
6750: --Fetch the primary quantity for all MMTTs for this MO line
6751: SELECT ABS(SUM(primary_quantity))
6752: INTO l_sum_mmtt_qty
6753: FROM mtl_material_transactions_temp
6754: WHERE move_order_line_id = l_mo_line_id;
6755:
6756: --Convert the MMTT primary quantity into MOL UOM
6757: IF p_primary_uom = l_mol_uom THEN
7165: SELECT 1
7166: INTO l_loaded
7167: FROM DUAL
7168: WHERE EXISTS(SELECT 1
7169: FROM mtl_material_transactions_temp
7170: WHERE (transfer_lpn_id = p_fromlpn_id
7171: OR content_lpn_id = p_fromlpn_id));
7172: EXCEPTION
7173: WHEN NO_DATA_FOUND THEN
7252: INTO l_locator_id, l_organization_id,
7253: l_transaction_header_id,
7254: l_transaction_uom,
7255: l_sec_transaction_uom -- Bug #4141928
7256: from mtl_material_transactions_temp
7257: where transaction_temp_id = p_temp_id;
7258:
7259: select nvl(project_id ,-999) , nvl(task_id ,-999)
7260: into l_mmtt_proj_id , l_mmtt_task_id
7341: , mmtt.locator_id
7342: INTO l_xfr_sub_code
7343: , l_mmtt_sub
7344: , l_mmtt_loc
7345: FROM mtl_material_transactions_temp mmtt
7346: WHERE mmtt.transaction_temp_id = p_temp_id;
7347:
7348: -- Check to see if the item is in the LPN
7349: IF (l_debug = 1) THEN
7451:
7452: BEGIN
7453: SELECT allocated_lpn_id
7454: INTO l_allocated_lpn_id
7455: FROM mtl_material_transactions_temp
7456: WHERE transaction_temp_id = p_temp_id;
7457: EXCEPTION
7458: WHEN NO_DATA_FOUND THEN
7459: IF (l_debug = 1) THEN
8271: END IF;
8272:
8273: SELECT primary_quantity, NVL(secondary_transaction_quantity, 0) -- Bug #4141928
8274: INTO l_mmtt_qty, l_mmtt_sec_qty -- Bug #4141928
8275: FROM mtl_material_transactions_temp
8276: WHERE transaction_temp_id = p_temp_id;
8277:
8278: -- If item is lot controlled then validate the lots
8279:
9934: ORDER BY LOT_NUMBER;
9935:
9936: CURSOR mmtt_csr IS
9937: SELECT transfer_subinventory
9938: FROM mtl_material_transactions_temp
9939: WHERE transaction_temp_id = p_temp_id; -- Bug #7257709
9940:
9941: BEGIN
9942:
10038:
10039: end if;
10040:
10041: IF (p_suggested_locator = p_confirmed_locator) THEN
10042: UPDATE mtl_material_transactions_temp mmtt
10043: SET posting_flag = 'N'
10044: WHERE transaction_temp_id = p_temp_id;
10045: END IF;
10046:
10074:
10075: -- Start change - Bug 4185621: restore posting flag in mmtt
10076:
10077: IF (p_suggested_locator = p_confirmed_locator) THEN
10078: UPDATE mtl_material_transactions_temp mmtt
10079: SET posting_flag = 'Y'
10080: WHERE transaction_temp_id = p_temp_id;
10081: END IF;
10082:
10312: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
10313: CURSOR c_cancelled_tasks IS
10314: --SELECT decode(mmtt.transaction_type_id, 35,'N',51,'N','Y')
10315: SELECT 'FAIL'
10316: FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mol
10317: WHERE mmtt.transaction_temp_id = p_transaction_temp_id
10318: AND mmtt.move_order_line_id = mol.line_id
10319: AND mol.line_status = inv_globals.g_to_status_cancel_by_source
10320: AND ROWNUM = 1;
10411: END IF;
10412:
10413: --Bug7120019
10414: IF wms_control.g_current_release_level >= 120001 THEN
10415: l_CursorStmt := 'SELECT count (*) FROM mtl_material_transactions_temp mmtt, wms_user_task_type_attributes wutta '||
10416: 'WHERE mmtt.transaction_temp_id = :x_temp_id ' ||
10417: ' AND mmtt.standard_operation_id = wutta.user_task_type_id '||
10418: ' AND mmtt.organization_id = wutta.organization_id '||
10419: ' AND wutta.honor_case_pick_flag = ''Y'' '; --Added for Bug#7584906
10667: CURSOR others_in_mmtt_delivery_cursor(l_lpn_id IN NUMBER) IS
10668: SELECT wda.delivery_id
10669: FROM wsh_delivery_assignments_v wda
10670: , wsh_delivery_details_ob_grp_v wdd
10671: , mtl_material_transactions_temp mmtt
10672: WHERE mmtt.transfer_lpn_id = l_lpn_id
10673: AND wda.delivery_detail_id = wdd.delivery_detail_id
10674: AND wdd.move_order_line_id = mmtt.move_order_line_id
10675: AND wdd.organization_id = mmtt.organization_id;
10686: CURSOR current_delivery_cursor IS
10687: SELECT wda.delivery_id
10688: FROM wsh_delivery_assignments_v wda
10689: , wsh_delivery_details_ob_grp_v wdd
10690: , mtl_material_transactions_temp mmtt
10691: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
10692: AND wdd.move_order_line_id = mmtt.move_order_line_id
10693: AND wdd.organization_id = mmtt.organization_id
10694: AND mmtt.transaction_temp_id = p_temp_id
10709: --
10710: CURSOR lpn_project_task_cursor(p_pick_to_lpn_id NUMBER) IS
10711: SELECT NVL(mil.project_id, -1)
10712: , NVL(mil.task_id, -1)
10713: FROM mtl_item_locations mil, mtl_material_transactions_temp mmtt
10714: WHERE mil.inventory_location_id = mmtt.transfer_to_location
10715: AND mil.organization_id = mmtt.organization_id
10716: AND mmtt.transfer_lpn_id = p_pick_to_lpn_id
10717: AND mmtt.organization_id = p_organization_id;
10722: --
10723: CURSOR mtl_project_task_cursor IS
10724: SELECT NVL(mil.project_id, -1)
10725: , NVL(mil.task_id, -1)
10726: FROM mtl_item_locations mil, mtl_material_transactions_temp mmtt
10727: WHERE mil.inventory_location_id = mmtt.transfer_to_location
10728: AND mil.organization_id = mmtt.organization_id
10729: AND mmtt.organization_id = p_organization_id
10730: AND mmtt.transaction_temp_id = p_temp_id;
10730: AND mmtt.transaction_temp_id = p_temp_id;
10731:
10732: CURSOR current_carton_grouping_cursor IS
10733: SELECT mol.carton_grouping_id
10734: FROM mtl_txn_request_lines mol, mtl_material_transactions_temp mmtt
10735: WHERE mmtt.transaction_temp_id = p_temp_id
10736: AND mmtt.organization_id = mol.organization_id
10737: AND mmtt.move_order_line_id = mol.line_id;
10738:
10737: AND mmtt.move_order_line_id = mol.line_id;
10738:
10739: CURSOR others_carton_grouping_cursor(p_lpn_id IN NUMBER) IS
10740: SELECT DISTINCT mol.carton_grouping_id
10741: FROM mtl_txn_request_lines mol, mtl_material_transactions_temp mmtt
10742: WHERE mmtt.transfer_lpn_id = p_lpn_id
10743: AND mmtt.organization_id = mol.organization_id
10744: AND mmtt.move_order_line_id = mol.line_id;
10745:
11098: , l_item_id
11099: , l_operation_plan_id
11100: , l_parent_line_id
11101: , l_transaction_header_id
11102: FROM mtl_material_transactions_temp mmtt
11103: WHERE mmtt.transaction_temp_id = p_temp_id;
11104:
11105: IF (l_debug = 1) THEN
11106: mydebug('validate_pick_to_lpn: parent line id:'||l_parent_line_id);
11115: select 'N'
11116: into l_multiple_pick
11117: from dual
11118: where exists (select 1
11119: from mtl_material_transactions_temp
11120: where transfer_lpn_id = pick_to_lpn_rec.lpn_id
11121: and transaction_header_id <>l_transaction_header_id);
11122:
11123: fnd_message.set_name('WMS', 'WMS_INVLD_PICKTO_LPN_NOT_NEW'); -- new message
11132: select 'Y'
11133: into l_bulk_task_exist
11134: from dual
11135: where exists (select 1
11136: from mtl_material_transactions_temp
11137: where transfer_lpn_id = pick_to_lpn_rec.lpn_id
11138: and transaction_temp_id = parent_line_id -- bulk task
11139: );
11140: fnd_message.set_name('WMS', 'WMS_INVLD_PICKTO_LPN_BULK');
11164: , l_mmtt_txn_type_id
11165: , l_mmtt_wip_entity_type
11166: FROM mtl_txn_request_headers mtrh
11167: , mtl_txn_request_lines mtrl
11168: , mtl_material_transactions_temp mmtt
11169: WHERE mtrh.header_id = mtrl.header_id
11170: AND mtrl.line_id = mmtt.move_order_line_id
11171: AND mmtt.transaction_temp_id = p_temp_id;
11172:
11176: INTO l_mo_type_in_lpn
11177: , l_wip_entity_type_in_lpn
11178: FROM mtl_txn_request_headers mtrh
11179: , mtl_txn_request_lines mtrl
11180: , mtl_material_transactions_temp mmtt
11181: WHERE mtrh.header_id = mtrl.header_id
11182: AND mtrl.line_id = mmtt.move_order_line_id
11183: AND mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
11184: AND ROWNUM < 2;
11231: INTO l_xfr_sub
11232: , l_xfr_to_location
11233: , l_item_id
11234: , l_operation_plan_id
11235: FROM mtl_material_transactions_temp mmtt
11236: WHERE mmtt.transaction_temp_id = p_temp_id; */
11237:
11238: l_lpn_controlled_flag := wms_globals.g_non_lpn_controlled_sub;
11239:
11258:
11259: BEGIN
11260: SELECT COUNT(*)
11261: INTO l_count
11262: FROM mtl_material_transactions_temp mmtt
11263: WHERE mmtt.transaction_temp_id <> p_temp_id
11264: AND mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
11265: AND ( NVL(mmtt.transfer_subinventory, 0) <> l_xfr_sub
11266: OR
11299: INTO l_count
11300: FROM DUAL
11301: WHERE EXISTS
11302: ( SELECT 'x'
11303: FROM mtl_material_transactions_temp mmtt
11304: , mtl_secondary_inventories msi
11305: WHERE mmtt.transaction_temp_id <> p_temp_id
11306: AND mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
11307: AND msi.organization_id = p_organization_id
11333: l_count := 0;
11334: BEGIN
11335: SELECT COUNT(1)
11336: INTO l_count
11337: FROM mtl_material_transactions_temp mmtt
11338: WHERE mmtt.transaction_temp_id <> p_temp_id
11339: AND mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
11340: AND mmtt.operation_plan_id <> l_operation_plan_id;
11341: EXCEPTION
11476: BEGIN
11477: --{
11478: SELECT wdd.delivery_detail_id INTO l_line_rows(1)
11479: FROM wsh_delivery_details wdd
11480: , mtl_material_transactions_temp mmtt
11481: WHERE mmtt.transaction_temp_id = p_temp_id
11482: AND wdd.move_order_line_id = mmtt.move_order_line_id
11483: AND wdd.organization_id = mmtt.organization_id;
11484: --
11483: AND wdd.organization_id = mmtt.organization_id;
11484: --
11485: SELECT wdd.delivery_detail_id INTO l_line_rows(2)
11486: FROM wsh_delivery_details wdd
11487: , mtl_material_transactions_temp mmtt
11488: WHERE mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
11489: AND wdd.move_order_line_id = mmtt.move_order_line_id
11490: AND wdd.organization_id = mmtt.organization_id
11491: AND rownum<2;
11850: , mmtt.inventory_item_id
11851: INTO l_transaction_type_id
11852: , l_org_id
11853: , l_item_id
11854: FROM mtl_material_transactions_temp mmtt
11855: WHERE mmtt.transaction_temp_id = p_temp_id;
11856:
11857: IF inv_material_status_grp.is_status_applicable(
11858: p_wms_installed => p_wms_installed
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
11966: AND mmtt.organization_id = p_organization_id
12139: l_lpn,
12140: l_ser,l_lot,
12141: l_transaction_action_id, -- Bug 4632519
12142: l_transaction_type_id -- Bug 4632519
12143: from mtl_material_Transactions_temp mmtt
12144: where mmtt.inventory_item_id = p_inventory_item_id
12145: and mmtt.organization_id = p_organization_id
12146: and mmtt.transfer_lpn_id = p_transfer_lpn_id
12147: and mmtt.content_lpn_id is null
12163: l_ser,
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