[Home] [Help]
190: , NVL(wms_task_type, 1)
191: , task_priority
192: , operation_plan_id
193: , move_order_line_id
194: FROM mtl_material_transactions_temp
195: WHERE transaction_temp_id = l_temp_id);
196:
197: x_return_status := fnd_api.g_ret_sts_success;
198: EXCEPTION
358: )
359: OR (wdt.task_type IN (1, 4, 5, 6)
360: AND EXISTS
361: (SELECT Nvl(mmtt.transaction_temp_id, -1)
362: FROM mtl_material_transactions_temp mmtt
363: WHERE mmtt.transaction_temp_id = wdt.transaction_temp_id
364: AND mmtt.subinventory_code = nvl(p_sign_on_zone,mmtt.subinventory_code) --Added bug3771517
365: AND mmtt.organization_id = wdt.organization_id))
366: );
390: )
391: OR (wdt.task_type IN (1, 4, 5, 6)
392: AND EXISTS
393: (SELECT Nvl(mmtt.transaction_temp_id, -1)
394: FROM mtl_material_transactions_temp mmtt
395: WHERE mmtt.transaction_temp_id = wdt.transaction_temp_id
396: AND mmtt.subinventory_code = nvl(p_sign_on_zone,mmtt.subinventory_code) --Added bug3771517
397: AND mmtt.organization_id = wdt.organization_id))
398: );
421: , wdt.priority
422: , wdt.task_id task_id
423: , sub.picking_order sub_picking_order
424: , loc.picking_order loc_picking_order
425: FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt, mtl_item_locations loc, mtl_secondary_inventories sub
426: WHERE wdt.person_id = l_user_id
427: AND wdt.organization_id = l_org_id
428: AND wdt.status <= 3
429: AND wdt.task_type IN (1, 4, 5, 6)
468: , wdt.priority
469: , wdt.task_id task_id
470: , sub.picking_order sub_picking_order
471: , loc.picking_order loc_picking_order
472: FROM mtl_material_transactions_temp mmtt, wms_dispatched_tasks wdt, mtl_item_locations loc, mtl_secondary_inventories sub
473: WHERE wdt.person_id = l_user_id
474: AND wdt.organization_id = l_org_id
475: AND wdt.status <= 3
476: AND wdt.task_type IN (1, 4, 5, 6)
710: , l_wms_task_type
711: , l_std_op_id
712: , l_operation_plan_id
713: , l_move_order_line_id
714: FROM mtl_material_transactions_temp t, bom_std_op_resources bsor, bom_resources bremp
715: WHERE t.transaction_temp_id = task_rec.task_id
716: AND t.standard_operation_id = bsor.standard_operation_id
717: AND bsor.resource_id = bremp.resource_id
718: AND bremp.resource_type = 2
749: WHERE inventory_item_id = l_eqp_id
750: AND ROWNUM < 2;
751: --select breqp.resource_id equip_type_id
752: --INTO l_mac_res_id
753: --from mtl_material_transactions_temp t,
754: --bom_std_op_resources bsor,
755: --bom_resources breqp
756: --where t.transaction_temp_id = task_rec.task_id
757: --and t.standard_operation_id = bsor.standard_operation_id
764: SELECT mtl_material_transactions_s.NEXTVAL txnhdrid
765: INTO l_txn_hdr_id
766: FROM DUAL;
767:
768: UPDATE mtl_material_transactions_temp
769: SET transaction_header_id = l_txn_hdr_id
770: WHERE transaction_temp_id = task_rec.task_id;
771:
772: -- Insert into WMS_DISPATCHED_TASKS for this user
1276: , l_wms_task_type
1277: , l_std_op_id
1278: , l_operation_plan_id
1279: , l_move_order_line_id
1280: FROM mtl_material_transactions_temp t, bom_std_op_resources bsor, bom_resources bremp
1281: WHERE t.transaction_temp_id = task_rec.task_id
1282: AND t.standard_operation_id = bsor.standard_operation_id
1283: AND bsor.resource_id = bremp.resource_id
1284: AND bremp.resource_type = 2
1320: SELECT mtl_material_transactions_s.NEXTVAL txnhdrid
1321: INTO l_txn_hdr_id
1322: FROM DUAL;
1323:
1324: UPDATE mtl_material_transactions_temp
1325: SET transaction_header_id = l_txn_hdr_id
1326: WHERE transaction_temp_id = task_rec.task_id;
1327:
1328: -- Insert into WMS_DISPATCHED_TASKS for this user
1605: , l_orig_sub
1606: , l_orig_loc
1607: , l_tran_type_id
1608: , l_orig_txn_header_id
1609: FROM mtl_material_transactions_temp
1610: WHERE transaction_temp_id = p_temp_id;
1611:
1612: IF (l_loc <> l_orig_loc)
1613: OR(p_sub <> l_orig_sub) THEN
1614: IF (l_debug = 1) THEN
1615: mydebug('complete_pick: User entered a different sub');
1616: END IF;
1617:
1618: UPDATE mtl_material_transactions_temp
1619: SET transfer_subinventory = p_sub
1620: , transfer_to_location = l_loc
1621: WHERE transaction_temp_id = p_temp_id;
1622: END IF;
1644: , l_uom
1645: , l_from_sub
1646: , l_from_loc
1647: , l_rev
1648: FROM mtl_material_transactions_temp mmtt
1649: WHERE mmtt.transaction_temp_id = p_temp_id;
1650:
1651: SELECT msi.serial_number_control_code
1652: , msi.lot_control_code
1869: -- wms_pick_drop_pvt.pick_drop
1870: --
1871: IF (l_content_lpn_id IS NULL
1872: OR l_content_lpn_id <> l_transfer_lpn_id) THEN
1873: UPDATE mtl_material_transactions_temp mmtt
1874: SET transaction_status = 3
1875: , transaction_header_id = p_txn_hdr_id
1876: , last_update_date = SYSDATE
1877: , last_updated_by = p_user_id
1878: , transaction_batch_id = p_txn_hdr_id
1879: WHERE mmtt.transaction_temp_id = p_temp_id;
1880: ELSIF(l_content_lpn_id = l_transfer_lpn_id) THEN
1881: -- We are transferring the entire lpn
1882: UPDATE mtl_material_transactions_temp mmtt
1883: SET transaction_status = 3
1884: , transaction_header_id = p_txn_hdr_id
1885: , transfer_lpn_id = NULL
1886: , last_update_date = SYSDATE
1890: END IF;
1891: ELSE
1892: IF (l_content_lpn_id IS NULL
1893: OR l_content_lpn_id <> l_transfer_lpn_id) THEN
1894: UPDATE mtl_material_transactions_temp mmtt
1895: SET transaction_status = 3
1896: , transaction_header_id = p_txn_hdr_id
1897: , last_update_date = SYSDATE
1898: , last_updated_by = p_user_id
1900: , transaction_batch_seq = p_temp_id
1901: WHERE mmtt.transaction_temp_id = p_temp_id;
1902: ELSIF(l_content_lpn_id = l_transfer_lpn_id) THEN
1903: -- We are transferring the entire lpn
1904: UPDATE mtl_material_transactions_temp mmtt
1905: SET transaction_status = 3
1906: , transaction_header_id = p_txn_hdr_id
1907: , transfer_lpn_id = NULL
1908: , last_update_date = SYSDATE
1923: --
1924: IF (WMS_CONTROL.get_current_release_level >=
1925: INV_RELEASE.get_j_release_level)
1926: THEN
1927: UPDATE mtl_material_transactions_temp
1928: SET transaction_quantity = -1 * ABS(transaction_quantity)
1929: , primary_quantity = -1 * ABS(primary_quantity)
1930: WHERE transaction_temp_id = p_temp_id
1931: AND organization_id = p_org_id;
1929: , primary_quantity = -1 * ABS(primary_quantity)
1930: WHERE transaction_temp_id = p_temp_id
1931: AND organization_id = p_org_id;
1932: ELSE
1933: UPDATE mtl_material_transactions_temp
1934: SET transaction_quantity = -1 * ABS(transaction_quantity)
1935: , primary_quantity = -1 * ABS(primary_quantity)
1936: , process_flag = 'W'
1937: WHERE transaction_temp_id = p_temp_id
2790: SELECT 1
2791: INTO l_loaded
2792: FROM DUAL
2793: WHERE EXISTS(SELECT 1
2794: FROM mtl_material_transactions_temp
2795: WHERE (transfer_lpn_id = p_lpn
2796: OR content_lpn_id = p_lpn));
2797: EXCEPTION
2798: WHEN NO_DATA_FOUND THEN
2870: -- Begin fix for 2774506
2871:
2872:
2873: SELECT locator_id,organization_id INTO l_locator_id, l_organization_id
2874: from mtl_material_transactions_temp
2875: where transaction_temp_id = p_temp_id;
2876:
2877: select nvl(project_id ,-999) , nvl(task_id ,-999)
2878: into l_mmtt_proj_id , l_mmtt_task_id
2954: AND inventory_item_id = p_item_id;
2955:
2956: SELECT mmtt.transfer_subinventory
2957: INTO l_xfr_sub_code
2958: FROM mtl_material_transactions_temp mmtt
2959: WHERE mmtt.transaction_temp_id = p_temp_id;
2960:
2961: -- Check to see if the item is in the LPN
2962: IF (l_debug = 1) THEN
3060:
3061: BEGIN
3062: SELECT allocated_lpn_id
3063: INTO l_allocated_lpn_id
3064: FROM mtl_material_transactions_temp
3065: WHERE transaction_temp_id = p_temp_id;
3066: EXCEPTION
3067: WHEN NO_DATA_FOUND THEN
3068: IF (l_debug = 1) THEN
3749: SELECT primary_quantity
3750: , transaction_uom
3751: INTO l_mmtt_qty
3752: , l_txn_uom
3753: FROM mtl_material_transactions_temp
3754: WHERE transaction_temp_id = p_temp_id;
3755:
3756: -- If item is lot controlled then validate the lots
3757:
4772: mydebug('lpn_match: l_temp_serial_trans_temp is null');
4773: END IF;
4774: SELECT 1
4775: INTO l_allocate_serial_flag
4776: FROM mtl_material_transactions_temp mmtt,
4777: mtl_serial_numbers_temp msnt,
4778: mtl_serial_numbers msn
4779: WHERE mmtt.transaction_temp_id = p_temp_id
4780: AND mmtt.organization_id = p_org_id
5057: l_ret VARCHAR2(1) := 'X';
5058: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
5059: CURSOR c_cancelled_tasks IS
5060: SELECT decode(mmtt.transaction_type_id, 35,'N',51,'N','Y')
5061: FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mol
5062: WHERE (mmtt.transaction_temp_id = txn_temp_id OR mmtt.parent_line_id = txn_temp_id)
5063: AND mmtt.move_order_line_id = mol.line_id
5064: AND mol.line_status = inv_globals.g_to_status_cancel_by_source
5065: AND ROWNUM = 1;
5148: , mmtt1.transaction_uom
5149: , mmtt2.transaction_uom
5150: , mmtt2.transaction_quantity
5151: , mmtt2.primary_quantity
5152: FROM mtl_material_transactions_temp mmtt1, mtl_material_transactions_temp mmtt2
5153: WHERE mmtt1.transaction_temp_id = p_temp_id
5154: AND mmtt1.transaction_header_id = l_txn_hdr_id
5155: AND mmtt1.organization_id = p_org_id
5156: AND mmtt2.transaction_header_id = mmtt1.transaction_header_id
5177: l_loaded NUMBER := 0;
5178:
5179: CURSOR mmtt_csr2(p_transaction_header_id NUMBER) IS
5180: SELECT mmtt.transaction_temp_id
5181: FROM mtl_material_transactions_temp mmtt
5182: WHERE mmtt.transaction_header_id = p_transaction_header_id;
5183:
5184:
5185: l_move_order_line_id NUMBER; --Bug2924823 H to I
5209: SELECT transfer_subinventory
5210: , transfer_to_location
5211: INTO l_to_sub
5212: , l_to_loc
5213: FROM mtl_material_transactions_temp
5214: WHERE transaction_temp_id = p_temp_id;
5215:
5216: BEGIN
5217: SELECT 1
5305: SELECT 0
5306: INTO l_loaded
5307: FROM DUAL
5308: WHERE NOT EXISTS(SELECT 1
5309: FROM mtl_material_transactions_temp
5310: WHERE content_lpn_id = p_from_lpn_id
5311: AND transaction_temp_id<>p_temp_id);
5312:
5313: IF (l_debug = 1) THEN
5441: END IF;
5442:
5443: -- end bug fix 2769358
5444:
5445: UPDATE mtl_material_transactions_temp
5446: SET transfer_lpn_id = l_transfer_lpn_id
5447: , content_lpn_id = l_content_lpn_id
5448: , lpn_id = l_lpn_id
5449: WHERE transaction_temp_id = l_tabtype(l_counter);
5526: l_item_id
5527: , l_lot
5528: , l_tran_type_id
5529: , l_tran_source_type_id
5530: FROM mtl_material_transactions_temp
5531: WHERE transaction_temp_id = x_temp_id;
5532:
5533: IF (l_debug = 1) THEN
5534: mydebug('load_pick : Transaction_temp_id : ' || x_temp_id);
5761: SELECT 1
5762: INTO l_bulk_pick_flag
5763: FROM DUAL
5764: WHERE EXISTS(SELECT 1
5765: FROM mtl_material_transactions_temp mmtt
5766: WHERE mmtt.parent_line_id = p_temp_id);
5767: EXCEPTION
5768: WHEN NO_DATA_FOUND THEN
5769: l_bulk_pick_flag := 0;
5802: END IF;
5803:
5804: -- Merge MMTT, MTLT, MSNT
5805:
5806: UPDATE mtl_material_transactions_temp
5807: SET transaction_quantity = transaction_quantity + l_mmtt2_txn_qty
5808: , primary_quantity = primary_quantity + l_mmtt2_primary_qty
5809: WHERE transaction_temp_id = p_temp_id;
5810:
5817: SET transaction_temp_id = p_temp_id
5818: WHERE transaction_temp_id = l_mmtt2_txn_temp_id;
5819: END IF;
5820:
5821: DELETE FROM mtl_material_transactions_temp
5822: WHERE transaction_temp_id = l_mmtt2_txn_temp_id;
5823:
5824: DELETE FROM wms_dispatched_tasks
5825: WHERE transaction_temp_id = l_mmtt2_txn_temp_id;
5865: ,move_order_line_id
5866: INTO l_mmtt_transaction_uom
5867: ,l_transaction_qty
5868: , l_move_order_line_id
5869: FROM mtl_material_transactions_temp
5870: WHERE transaction_temp_id = p_temp_id;
5871:
5872: mydebug('load_pick:
5873: transaction_uom:'||l_mmtt_transaction_uom);
5923:
5924: mydebug('load_pick: Deleting MMTT');
5925: -- merging from H
5926:
5927: DELETE FROM mtl_material_transactions_temp
5928: WHERE transaction_temp_id = p_temp_id;
5929:
5930: mydebug('load_pick: Updating move order line with reduced quantity');
5931:
5942: WHERE transaction_temp_id = p_temp_id;
5943:
5944:
5945: ELSE
5946: DELETE FROM mtl_material_transactions_temp
5947: WHERE transaction_temp_id = p_temp_id;
5948:
5949: update mtl_material_transactions_temp
5950: set transaction_temp_id=p_temp_id
5945: ELSE
5946: DELETE FROM mtl_material_transactions_temp
5947: WHERE transaction_temp_id = p_temp_id;
5948:
5949: update mtl_material_transactions_temp
5950: set transaction_temp_id=p_temp_id
5951: where transaction_temp_id=x_temp_id; --Added bug 3765153
5952:
5953: bulk_pick(
6132: INTO l_cost_group_id
6133: , l_org_id
6134: , l_sub
6135: , l_loc
6136: FROM mtl_material_transactions_temp
6137: WHERE transaction_temp_id = l_temp_id;
6138:
6139: l_exist_lpn := 0;
6140:
6228: SELECT mtl_material_transactions_s.NEXTVAL
6229: INTO l_new_temp_id
6230: FROM DUAL;
6231:
6232: INSERT INTO mtl_material_transactions_temp
6233: (
6234: transaction_header_id
6235: , transaction_temp_id
6236: , source_code
6615: , l_lpn_id
6616: , operation_plan_id
6617: , transaction_header_id
6618: , l_new_temp_id
6619: FROM mtl_material_transactions_temp
6620: WHERE transaction_temp_id = l_temp_id);
6621:
6622: x_return_status := fnd_api.g_ret_sts_success;
6623: EXCEPTION
6965: , l_uom
6966: , l_item_id
6967: , l_user_id
6968: , l_mo_line_id
6969: FROM mtl_material_transactions_temp
6970: WHERE transaction_temp_id = l_temp_id;
6971:
6972: IF l_uom = p_uom_code THEN
6973: l_qty := p_lpn_qty;
7007: IF (l_debug = 1) THEN
7008: mydebug('multiple_lpn_pick: l_new_temp_id = ' || l_new_temp_id);
7009: END IF;
7010:
7011: INSERT INTO mtl_material_transactions_temp
7012: (
7013: transaction_header_id
7014: , transaction_temp_id
7015: , source_code
7400: , wms_task_type
7401: , task_priority
7402: , container_item_id
7403: , operation_plan_id
7404: FROM mtl_material_transactions_temp
7405: WHERE transaction_temp_id = l_temp_id);
7406:
7407: -- Update original MMTT with the remaining transaction and primary qty
7408:
7405: WHERE transaction_temp_id = l_temp_id);
7406:
7407: -- Update original MMTT with the remaining transaction and primary qty
7408:
7409: UPDATE mtl_material_transactions_temp
7410: SET transaction_quantity = transaction_quantity - l_qty
7411: , primary_quantity = primary_quantity - l_pr_qty
7412: WHERE transaction_temp_id = l_temp_id;
7413:
7977: CURSOR others_in_mmtt_delivery_cursor(l_lpn_id IN NUMBER) IS
7978: SELECT wda.delivery_id
7979: FROM wsh_delivery_assignments_v wda
7980: , wsh_delivery_details wdd
7981: , mtl_material_transactions_temp mmtt
7982: WHERE mmtt.transfer_lpn_id = l_lpn_id
7983: AND wda.delivery_detail_id = wdd.delivery_detail_id
7984: AND wdd.move_order_line_id = mmtt.move_order_line_id
7985: AND wdd.organization_id = mmtt.organization_id;
8002: CURSOR current_delivery_cursor IS
8003: SELECT wda.delivery_id
8004: FROM wsh_delivery_assignments_v wda
8005: , wsh_delivery_details wdd
8006: , mtl_material_transactions_temp mmtt
8007: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
8008: AND wdd.move_order_line_id = mmtt.move_order_line_id
8009: AND wdd.organization_id = mmtt.organization_id
8010: AND mmtt.transaction_temp_id = p_temp_id
8024: --
8025: CURSOR lpn_project_task_cursor IS
8026: SELECT NVL(mil.project_id, -1)
8027: , NVL(mil.task_id, -1)
8028: FROM mtl_item_locations mil, mtl_material_transactions_temp mmtt
8029: WHERE mil.inventory_location_id = mmtt.transfer_to_location
8030: AND mil.organization_id = mmtt.organization_id
8031: AND mmtt.transfer_lpn_id = p_pick_to_lpn
8032: AND mmtt.organization_id = p_organization_id;
8037: --
8038: CURSOR mtl_project_task_cursor IS
8039: SELECT NVL(mil.project_id, -1)
8040: , NVL(mil.task_id, -1)
8041: FROM mtl_item_locations mil, mtl_material_transactions_temp mmtt
8042: WHERE mil.inventory_location_id = mmtt.transfer_to_location
8043: AND mil.organization_id = mmtt.organization_id
8044: AND mmtt.organization_id = p_organization_id
8045: AND mmtt.transaction_temp_id = p_temp_id;
8045: AND mmtt.transaction_temp_id = p_temp_id;
8046:
8047: CURSOR current_carton_grouping_cursor IS
8048: SELECT mol.carton_grouping_id
8049: FROM mtl_txn_request_lines mol, mtl_material_transactions_temp mmtt
8050: WHERE mmtt.transaction_temp_id = p_temp_id
8051: AND mmtt.organization_id = mol.organization_id
8052: AND mmtt.move_order_line_id = mol.line_id;
8053:
8052: AND mmtt.move_order_line_id = mol.line_id;
8053:
8054: CURSOR others_carton_grouping_cursor(p_lpn_id IN NUMBER) IS
8055: SELECT DISTINCT mol.carton_grouping_id
8056: FROM mtl_txn_request_lines mol, mtl_material_transactions_temp mmtt
8057: WHERE mmtt.transfer_lpn_id = p_lpn_id
8058: AND mmtt.organization_id = mol.organization_id
8059: AND mmtt.move_order_line_id = mol.line_id;
8060: BEGIN
8159: , l_mmtt_txn_type_id
8160: , l_mmtt_wip_entity_type
8161: FROM mtl_txn_request_headers mtrh
8162: , mtl_txn_request_lines mtrl
8163: , mtl_material_transactions_temp mmtt
8164: WHERE mtrh.header_id = mtrl.header_id
8165: AND mtrl.line_id = mmtt.move_order_line_id
8166: AND mmtt.transaction_temp_id = p_temp_id;
8167:
8173: INTO l_mo_type_in_lpn
8174: , l_wip_entity_type_in_lpn
8175: FROM mtl_txn_request_headers mtrh
8176: , mtl_txn_request_lines mtrl
8177: , mtl_material_transactions_temp mmtt
8178: WHERE mtrh.header_id = mtrl.header_id
8179: AND mtrl.line_id = mmtt.move_order_line_id
8180: AND mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
8181: AND ROWNUM < 2;
8229: INTO l_xfr_sub
8230: , l_xfr_to_location
8231: , l_item_id
8232: , l_operation_plan_id
8233: FROM mtl_material_transactions_temp mmtt
8234: WHERE mmtt.transaction_temp_id = p_temp_id;
8235:
8236: l_lpn_controlled_flag := wms_globals.g_non_lpn_controlled_sub;
8237:
8256:
8257: BEGIN
8258: SELECT COUNT(*)
8259: INTO l_count
8260: FROM mtl_material_transactions_temp mmtt
8261: WHERE mmtt.transaction_temp_id <> p_temp_id
8262: AND mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
8263: AND ( NVL(mmtt.transfer_subinventory, 0) <> l_xfr_sub
8264: OR
8297: INTO l_count
8298: FROM DUAL
8299: WHERE EXISTS
8300: ( SELECT 'x'
8301: FROM mtl_material_transactions_temp mmtt
8302: , mtl_secondary_inventories msi
8303: WHERE mmtt.transaction_temp_id <> p_temp_id
8304: AND mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
8305: AND msi.organization_id = p_organization_id
8331: l_count := 0;
8332: BEGIN
8333: SELECT COUNT(1)
8334: INTO l_count
8335: FROM mtl_material_transactions_temp mmtt
8336: WHERE mmtt.transaction_temp_id <> p_temp_id
8337: AND mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
8338: AND mmtt.operation_plan_id <> l_operation_plan_id;
8339: EXCEPTION
8469: --Bug#4440585. Added this block
8470: BEGIN
8471: SELECT wdd.delivery_detail_id INTO l_line_rows(1)
8472: FROM wsh_delivery_details wdd
8473: , mtl_material_transactions_temp mmtt
8474: WHERE mmtt.transaction_temp_id = p_temp_id
8475: AND wdd.move_order_line_id = mmtt.move_order_line_id
8476: AND wdd.organization_id = mmtt.organization_id;
8477:
8476: AND wdd.organization_id = mmtt.organization_id;
8477:
8478: SELECT wdd.delivery_detail_id INTO l_line_rows(2)
8479: FROM wsh_delivery_details wdd
8480: , mtl_material_transactions_temp mmtt
8481: WHERE mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
8482: AND wdd.move_order_line_id = mmtt.move_order_line_id
8483: AND wdd.organization_id = mmtt.organization_id
8484: AND rownum<2;
8957: , l_item_id
8958: , l_user_id
8959: , l_mo_line_id
8960: , l_reservation_id
8961: FROM mtl_material_transactions_temp
8962: WHERE transaction_temp_id = l_temp_id;
8963:
8964: l_to_reservation_id := l_reservation_id;
8965: l_progress := '30';
9411: -- Create new MMTT line
9412:
9413: l_progress := '270';
9414:
9415: INSERT INTO mtl_material_transactions_temp
9416: (
9417: transaction_header_id
9418: , transaction_temp_id
9419: , source_code
9804: , wms_task_type
9805: , task_priority
9806: , container_item_id
9807: , operation_plan_id
9808: FROM mtl_material_transactions_temp
9809: WHERE transaction_temp_id = l_temp_id);
9810:
9811: l_progress := '280';
9812:
9811: l_progress := '280';
9812:
9813: -- Update original MMTT with the remaining transaction and primary qty
9814:
9815: UPDATE mtl_material_transactions_temp
9816: SET transaction_quantity = transaction_quantity - l_qty
9817: , primary_quantity = primary_quantity - l_pr_qty
9818: , lpn_id = null --bug 4046834
9819: , content_lpn_id = null
10069: END IF;
10070:
10071: -- for nested LPNs selected for picking items from:end
10072:
10073: UPDATE mtl_material_transactions_temp
10074: SET content_lpn_id = l_from_lpn_id
10075: , transfer_lpn_id = l_to_lpn_id
10076: , lpn_id = l_lpn_id
10077: , subinventory_code = l_act_sub
10080: , last_update_date = SYSDATE
10081: , last_updated_by = l_user_id
10082: WHERE transaction_temp_id = l_local_temp_id;
10083: ELSE
10084: UPDATE mtl_material_transactions_temp
10085: SET transfer_lpn_id = l_to_lpn_id
10086: , lpn_id = l_from_lpn_id
10087: , content_lpn_id = NULL
10088: , subinventory_code = l_act_sub
10272:
10273: l_local_temp_id := l_temp_id;
10274: END IF;
10275:
10276: UPDATE mtl_material_transactions_temp
10277: SET transfer_lpn_id = l_to_lpn_id
10278: , lpn_id = NULL
10279: , content_lpn_id = NULL
10280: , subinventory_code = l_act_sub
10666:
10667:
10668: CURSOR mmtt_csr IS
10669: SELECT mmtt.transaction_temp_id
10670: FROM mtl_material_transactions_temp mmtt
10671: WHERE mmtt.transaction_header_id = l_txn_header_id
10672: AND mmtt.organization_id = l_org_id;
10673:
10674: -- VARAJAGO for bug 5222498, added to fetch the group_mark_id details of MSN
11007:
11008: -- CURSOR TO GET ALL MMTT LINES ASSOCIATED WITH DROP LPN
11009: CURSOR C_DROP_LPN_MMTT_LINE_CSR IS
11010: SELECT organization_id, TRANSACTION_TEMP_ID, MOVE_ORDER_LINE_ID, INVENTORY_ITEM_ID, PRIMARY_QUANTITY
11011: FROM mtl_material_transactions_temp mmtt
11012: WHERE TRANSACTION_HEADER_ID = l_txn_header_id
11013: AND mmtt.organization_id = l_org_id
11014: ORDER BY move_order_line_id asc,primary_quantity desc ;
11015:
11119: , l_mo_line_id
11120: , l_tran_source_type_id
11121: , l_inventory_item_id
11122: , l_tran_action_id
11123: FROM mtl_material_transactions_temp
11124: WHERE transaction_temp_id = l_temp_id
11125: AND organization_id = l_org_id;
11126:
11127: BEGIN
11146: SELECT 1
11147: INTO l_cnt
11148: FROM DUAL
11149: WHERE EXISTS(SELECT 1
11150: FROM mtl_material_transactions_temp
11151: WHERE parent_line_id = l_temp_id);
11152: EXCEPTION
11153: WHEN NO_DATA_FOUND THEN
11154: l_cnt := 0;
11169: IF (l_debug = 1) THEN
11170: mydebug('pick_drop: User did not pick entire lpn');
11171: END IF;
11172:
11173: UPDATE mtl_material_transactions_temp
11174: SET transaction_header_id = l_txn_header_id
11175: , lpn_id = l_lpn_id
11176: , transfer_lpn_id = l_transfer_lpn_id
11177: , transaction_status = 3
11188: -- We have to do this as kind of a hack because we basically
11189: -- are picking one complete lpn but fulfilling multiple
11190: -- mmtt lines
11191:
11192: UPDATE mtl_material_transactions_temp
11193: SET transaction_header_id = l_txn_header_id
11194: , lpn_id = l_content_lpn_id
11195: , transfer_lpn_id = l_content_lpn_id
11196: , content_lpn_id = NULL
11215: END IF;
11216:
11217: -- Get rid of the original MMTT line (which was a bogus line created
11218: -- for bulk picking
11219: DELETE mtl_material_transactions_temp
11220: WHERE transaction_temp_id = l_temp_id;
11221: END IF;
11222: END IF;
11223:
11259: IF (l_debug = 1) THEN
11260: mydebug('pick_drop: Need to update the account period in MMTT');
11261: END IF;
11262:
11263: UPDATE mtl_material_transactions_temp
11264: SET acct_period_id = l_period_id
11265: WHERE transaction_header_id = l_txn_header_id
11266: AND organization_id = l_org_id;
11267: ELSE
11488: -- (as it was before drop)
11489:
11490: SELECT count(transaction_temp_id)
11491: INTO l_check_tasks
11492: FROM mtl_material_transactions_temp mmtt
11493: WHERE transfer_lpn_id = l_transfer_lpn_id
11494: AND transaction_temp_id <> l_temp_id ;
11495: IF l_check_tasks = 0 THEN
11496: l_lpn_context := wms_container_pub.LPN_CONTEXT_PREGENERATED;
11507: END IF;
11508:
11509: --Bug # 2275770
11510: --Update mmtt.transaction_date to sysdate
11511: UPDATE mtl_material_transactions_temp
11512: SET transaction_date = SYSDATE
11513: WHERE transaction_header_id = l_txn_header_id;
11514:
11515: -- Now call the txn processor...
12501: END IF;
12502:
12503: SELECT COUNT(*)
12504: INTO l_mmtt_rowcnt
12505: FROM mtl_material_transactions_temp m
12506: WHERE m.cartonization_id IS NOT NULL
12507: AND m.cartonization_id = l_lpn_id
12508: AND parent_line_id IS NULL;
12509:
12514: IF l_mmtt_rowcnt > 0 THEN
12515: -- There are MMTT tasks for this LPN
12516: SELECT COUNT(*)
12517: INTO l_wdt_rowcnt
12518: FROM mtl_material_transactions_temp m, wms_dispatched_tasks t
12519: WHERE m.cartonization_id = l_lpn_id
12520: AND t.transaction_temp_id = m.transaction_temp_id
12521: AND t.status = 4;
12522:
12592: , l_wms_task_type
12593: , l_std_op_id
12594: , l_operation_plan_id
12595: , l_move_order_line_id
12596: FROM mtl_material_transactions_temp t, bom_std_op_resources bsor, bom_resources bremp
12597: WHERE t.transaction_temp_id = task_rec.task_id
12598: AND t.standard_operation_id = bsor.standard_operation_id
12599: AND bsor.resource_id = bremp.resource_id
12600: AND bremp.resource_type = 2
12614: AND ROWNUM < 2;
12615: /*
12616: select breqp.resource_id equip_type_id
12617: INTO l_mac_res_id
12618: from mtl_material_transactions_temp t,
12619: bom_std_op_resources bsor,
12620: bom_resources breqp
12621: where t.transaction_temp_id = task_rec.task_id
12622: and t.standard_operation_id = bsor.standard_operation_id
12629: SELECT mtl_material_transactions_s.NEXTVAL txnhdrid
12630: INTO l_txn_hdr_id
12631: FROM DUAL;
12632:
12633: UPDATE mtl_material_transactions_temp
12634: SET transaction_header_id = l_txn_hdr_id
12635: WHERE transaction_temp_id = task_rec.task_id;
12636:
12637: -- Insert into WMS_DISPATCHED_TASKS for this user
12878: SELECT 1
12879: INTO l_mmtt_rowcnt
12880: FROM DUAL
12881: WHERE EXISTS(SELECT 1
12882: FROM mtl_material_transactions_temp
12883: WHERE transaction_temp_id = l_pick_slip_id
12884: AND parent_line_id is NULL);
12885: EXCEPTION
12886: WHEN NO_DATA_FOUND THEN
12939: , l_std_op_id
12940: , l_operation_plan_id
12941: , l_move_order_line_id
12942: , l_item_id --Bug6009436
12943: FROM mtl_material_transactions_temp t, bom_std_op_resources bsor, bom_resources bremp
12944: WHERE t.transaction_temp_id = l_pick_slip_id
12945: AND t.standard_operation_id = bsor.standard_operation_id
12946: AND bsor.resource_id = bremp.resource_id
12947: AND bremp.resource_type = 2
12971: AND ROWNUM < 2;
12972: /*
12973: select breqp.resource_id equip_type_id
12974: INTO l_mac_res_id
12975: from mtl_material_transactions_temp t,
12976: bom_std_op_resources bsor,
12977: bom_resources breqp
12978: where t.transaction_temp_id = task_rec.task_id
12979: and t.standard_operation_id = bsor.standard_operation_id
13003: SELECT mtl_material_transactions_s.NEXTVAL txnhdrid
13004: INTO l_txn_hdr_id
13005: FROM DUAL;
13006:
13007: UPDATE mtl_material_transactions_temp
13008: SET transaction_header_id = l_txn_hdr_id
13009: WHERE transaction_temp_id = l_pick_slip_id;
13010:
13011: --Bug6009436.Begin
13180: END IF;
13181:
13182: SELECT COUNT(*)
13183: INTO l_mmtt_rowcnt
13184: FROM mtl_material_transactions_temp m
13185: WHERE m.cartonization_id IS NOT NULL
13186: AND m.cartonization_id = l_lpn_id
13187: AND parent_line_id IS NULL;
13188:
13193: IF l_mmtt_rowcnt > 0 THEN
13194: -- There are MMTT tasks for this LPN
13195: SELECT COUNT(*)
13196: INTO l_wdt_rowcnt
13197: FROM mtl_material_transactions_temp m, wms_dispatched_tasks t
13198: WHERE m.cartonization_id = l_lpn_id
13199: AND t.transaction_temp_id = m.transaction_temp_id
13200: AND t.status = 4;
13201:
13444: WHERE mtlt.transaction_temp_id = p_temp_id
13445: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
13446:
13447: BEGIN
13448: DELETE FROM mtl_material_transactions_temp
13449: WHERE transaction_temp_id = p_temp_id;
13450:
13451: DELETE FROM wms_dispatched_tasks
13452: WHERE transaction_temp_id = p_temp_id;
13538: , mmtt.move_order_line_id
13539: , mmtt.transaction_quantity
13540: , mmtt.transaction_uom
13541: , mmtt.primary_quantity
13542: FROM mtl_material_transactions_temp mmtt
13543: WHERE mmtt.transaction_temp_id = p_temp_id
13544: AND NOT EXISTS(SELECT 1
13545: FROM mtl_material_transactions_temp t1
13546: WHERE t1.parent_line_id = mmtt.transaction_temp_id)
13541: , mmtt.primary_quantity
13542: FROM mtl_material_transactions_temp mmtt
13543: WHERE mmtt.transaction_temp_id = p_temp_id
13544: AND NOT EXISTS(SELECT 1
13545: FROM mtl_material_transactions_temp t1
13546: WHERE t1.parent_line_id = mmtt.transaction_temp_id)
13547: UNION ALL
13548: SELECT mmtt.transaction_header_id
13549: , mmtt.transaction_temp_id
13556: , mmtt.move_order_line_id
13557: , mmtt.transaction_quantity
13558: , mmtt.transaction_uom
13559: , mmtt.primary_quantity
13560: FROM mtl_material_transactions_temp mmtt
13561: WHERE mmtt.parent_line_id = p_temp_id;
13562:
13563: CURSOR c_mo_line_info IS
13564: SELECT mtrh.move_order_type
13574: AND mtrh.header_id = mtrl.header_id;
13575:
13576: CURSOR c_get_other_mmtt IS
13577: SELECT COUNT(*)
13578: FROM mtl_material_transactions_temp mmtt
13579: WHERE mmtt.move_order_line_id = l_mo_line_id
13580: AND mmtt.transaction_temp_id <> l_txn_temp_id
13581: AND NOT EXISTS(SELECT 1
13582: FROM mtl_material_transactions_temp t1
13578: FROM mtl_material_transactions_temp mmtt
13579: WHERE mmtt.move_order_line_id = l_mo_line_id
13580: AND mmtt.transaction_temp_id <> l_txn_temp_id
13581: AND NOT EXISTS(SELECT 1
13582: FROM mtl_material_transactions_temp t1
13583: WHERE t1.parent_line_id = mmtt.transaction_temp_id);
13584: BEGIN
13585: IF (l_debug = 1) THEN
13586: mydebug('CLEANUP_TASK: Cleaning up the Task with Temp ID = ' || p_temp_id);
14094: , mmtt.inventory_item_id
14095: INTO l_transaction_type_id
14096: , l_org_id
14097: , l_item_id
14098: FROM mtl_material_transactions_temp mmtt
14099: WHERE mmtt.transaction_temp_id = p_temp_id;
14100:
14101: IF inv_material_status_grp.is_status_applicable(
14102: p_wms_installed => p_wms_installed
14186:
14187: -- bug fix 2805229
14188: SELECT wms_task_type
14189: INTO l_wms_task_type
14190: FROM mtl_material_transactions_temp
14191: WHERE transaction_temp_id = p_temp_id;
14192:
14193: IF l_wms_task_type = 7 THEN -- staging move
14194: IF (l_debug = 1) THEN
14204:
14205: SELECT mtrh.move_order_type
14206: INTO l_mmtt_mo_type
14207: FROM mtl_txn_request_headers mtrh, -- mo header for the new task
14208: mtl_txn_request_lines mtrl, mtl_material_transactions_temp mmtt
14209: WHERE mtrh.header_id = mtrl.header_id
14210: AND mtrl.line_id = mmtt.move_order_line_id
14211: AND mmtt.transaction_temp_id = p_temp_id;
14212:
14219: , mmtt.transfer_subinventory
14220: INTO l_lpn_controlled_flag
14221: , l_xfr_lpn_id
14222: , l_orig_xfr_sub
14223: FROM mtl_material_transactions_temp mmtt, mtl_secondary_inventories msi
14224: WHERE mmtt.transaction_temp_id = p_temp_id
14225: AND mmtt.organization_id = msi.organization_id
14226: AND msi.secondary_inventory_name = p_confirmed_drop_sub;
14227:
14243: INTO l_count
14244: FROM DUAL
14245: WHERE EXISTS(
14246: SELECT 1
14247: FROM mtl_material_transactions_temp mmtt
14248: WHERE mmtt.transaction_temp_id <> p_temp_id
14249: AND mmtt.transfer_lpn_id = l_xfr_lpn_id
14250: AND mmtt.transfer_subinventory <> p_confirmed_drop_sub
14251: AND mmtt.transfer_subinventory <> l_orig_xfr_sub);
14358: , mmtt.transfer_lpn_id
14359: , mmtt.transaction_uom
14360: , mmtt.transaction_quantity
14361: , mmtt.primary_quantity
14362: FROM mtl_material_transactions_temp mmtt
14363: WHERE mmtt.transaction_header_id = p_txn_hdr_id
14364: AND mmtt.organization_id = p_org_id
14365: AND mmtt.transaction_quantity > 0
14366: AND mmtt.parent_line_id is NULL --Added bug3765153 to ensure only parent line are picked
14370: SELECT mmtt.transaction_temp_id
14371: , mmtt.transaction_uom
14372: , mmtt.transaction_quantity
14373: , mmtt.primary_quantity
14374: FROM mtl_material_transactions_temp mmtt
14375: WHERE mmtt.parent_line_id = p_temp_id
14376: AND mmtt.organization_id = p_org_id
14377: ORDER BY mmtt.transaction_quantity DESC;
14378:
14377: ORDER BY mmtt.transaction_quantity DESC;
14378:
14379: CURSOR unpicked_child_mmtt_lines(p_parent_line_id NUMBER,p_org_id NUMBER) IS
14380: SELECT mmtt.transaction_temp_id
14381: FROM mtl_material_transactions_temp mmtt
14382: WHERE mmtt.organization_id = p_org_id
14383: AND mmtt.parent_line_id = p_parent_line_id; --Added bug3765153 to determine and back order unpicked lines
14384:
14385: l_parent_txn_qty NUMBER;
14450: );
14451: END IF;
14452:
14453: IF l_parent_txn_qty >= l_child_txn_qty THEN
14454: UPDATE mtl_material_transactions_temp mmtt
14455: SET mmtt.transaction_header_id = x_new_txn_hdr_id
14456: , mmtt.transfer_lpn_id = l_transfer_lpn_id
14457: , mmtt.lpn_id = l_lpn_id
14458: , mmtt.parent_line_id = l_parent_txn_temp_id --Modified from NULL bug3765153
14473: select mtl_material_transactions_s.NEXTVAL
14474: into l_new_temp_id
14475: from dual; --Added bug3765153
14476:
14477: INSERT INTO mtl_material_transactions_temp
14478: (
14479: transaction_header_id
14480: , transaction_temp_id
14481: , source_code
14868: , task_priority
14869: , container_item_id
14870: , operation_plan_id
14871: , parent_line_id
14872: FROM mtl_material_transactions_temp
14873: WHERE transaction_temp_id = l_child_txn_temp_id);
14874:
14875: UPDATE mtl_material_transactions_temp mmtt
14876: SET mmtt.transaction_header_id = x_new_txn_hdr_id
14871: , parent_line_id
14872: FROM mtl_material_transactions_temp
14873: WHERE transaction_temp_id = l_child_txn_temp_id);
14874:
14875: UPDATE mtl_material_transactions_temp mmtt
14876: SET mmtt.transaction_header_id = x_new_txn_hdr_id
14877: , mmtt.transaction_quantity = l_parent_txn_qty
14878: , mmtt.primary_quantity = l_parent_pri_qty
14879: , mmtt.parent_line_id = NULL --l_parent_txn_temp_id --Modified from NULL bug3765153
14957: , wdt.task_type
14958: , SYSDATE
14959: , mmtt.operation_plan_id
14960: , mmtt.move_order_line_id
14961: FROM wms_dispatched_tasks wdt, mtl_material_transactions_temp mmtt
14962: WHERE wdt.transaction_temp_id = p_temp_id
14963: AND mmtt.transaction_header_id = x_new_txn_hdr_id);
14964:
14965: DELETE FROM wms_dispatched_tasks wdt
14963: AND mmtt.transaction_header_id = x_new_txn_hdr_id);
14964:
14965: DELETE FROM wms_dispatched_tasks wdt
14966: WHERE wdt.transaction_temp_id IN(SELECT mmtt.transaction_temp_id
14967: FROM mtl_material_transactions_temp mmtt
14968: WHERE mmtt.transaction_header_id = p_txn_hdr_id
14969: AND mmtt.organization_id = p_org_id);
14970:
14971: --Added bug 3765153 to clean up lines with remaining qty
14997: END IF;
14998:
14999: -- Deleting Parent Tasks. Once loaded, only Child Tasks are considered.
15000:
15001: DELETE FROM mtl_material_transactions_temp mmtt
15002: WHERE mmtt.transaction_header_id = p_txn_hdr_id
15003: AND mmtt.organization_id = p_org_id;
15004:
15005: -- nullify the parent_line_id for the children lines
15003: AND mmtt.organization_id = p_org_id;
15004:
15005: -- nullify the parent_line_id for the children lines
15006: -- since the parent lines are gone Bug3765153
15007: update mtl_material_transactions_temp
15008: set parent_line_id = null
15009: where transaction_header_id = x_new_txn_hdr_id;
15010:
15011: EXCEPTION
15142: mmtt.lpn_id,
15143: null,
15144: null
15145: INTO l_sub,l_loc,l_rev,l_lpn,l_ser,l_lot
15146: from mtl_material_Transactions_temp mmtt
15147: where mmtt.inventory_item_id = p_inventory_item_id
15148: and mmtt.organization_id = p_organization_id
15149: and mmtt.transfer_lpn_id = p_transfer_lpn_id
15150: and mmtt.content_lpn_id is null
15157: mmtt.lpn_id,
15158: null,
15159: mtlt.lot_number
15160: INTO l_sub,l_loc,l_rev,l_lpn,l_ser,l_lot
15161: from mtl_material_Transactions_temp mmtt,
15162: mtl_transaction_lots_temp mtlt
15163: where mmtt.inventory_item_id = p_inventory_item_id
15164: and mmtt.organization_id = p_organization_id
15165: and mmtt.transfer_lpn_id = p_transfer_lpn_id
15281: CURSOR pick_delivery_cursor IS
15282: SELECT wda.delivery_id
15283: FROM wsh_delivery_assignments wda,
15284: wsh_delivery_details wdd,
15285: mtl_material_transactions_temp temp
15286: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
15287: AND wdd.move_order_line_id = temp.move_order_line_id
15288: AND wdd.organization_id = temp.organization_id
15289: AND temp.transfer_lpn_id = p_pick_lpn_id
15409: AND rownum = 1 ;
15410:
15411: SELECT wdd.delivery_detail_id
15412: INTO l_line_rows(2)
15413: FROM wsh_delivery_details wdd, Mtl_material_transactions_temp mmtt
15414: WHERE mmtt.move_order_line_id = wdd.move_order_line_id
15415: AND wdd.organization_id = mmtt.organization_id
15416: AND mmtt.organization_id= p_organization_id
15417: AND mmtt.transfer_lpn_id= p_pick_lpn_id
15539: CURSOR pick_delivery_cursor IS
15540: SELECT wda.delivery_id
15541: FROM wsh_delivery_assignments wda,
15542: wsh_delivery_details wdd,
15543: mtl_material_transactions_temp temp
15544: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
15545: AND wdd.move_order_line_id = temp.move_order_line_id
15546: AND wdd.organization_id = temp.organization_id
15547: AND temp.transfer_lpn_id = p_pick_lpn_id
15595:
15596: BEGIN
15597: Select transfer_subinventory, transfer_to_location into l_drop_sub,
15598: l_drop_loc
15599: from mtl_material_transactions_temp
15600: where transfer_lpn_id = p_pick_lpn_id
15601: AND organization_id = p_organization_id;
15602:
15603: EXCEPTION