[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
10159: -- Bug 14261335
10160: SELECT COUNT(msnt.transaction_temp_id)
10161: INTO l_serial_count
10162: FROM mtl_serial_numbers_temp msnt,
10163: mtl_material_transactions_temp mmtt,
10164: mtl_transaction_lots_temp mtlt
10165: WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id
10166: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
10167: AND mmtt.inventory_item_id = l_item_id
10248: -- Bug 14261335
10249: SELECT COUNT(msnt.transaction_temp_id)
10250: INTO l_serial_count
10251: FROM mtl_serial_numbers_temp msnt,
10252: mtl_material_transactions_temp mmtt
10253: WHERE mmtt.transaction_temp_id = msnt.transaction_temp_id
10254: AND mmtt.inventory_item_id = l_item_id
10255: AND mmtt.organization_id = p_org_id
10256: AND msnt.fm_serial_number = l_fm_serial;
10285:
10286: l_local_temp_id := l_temp_id;
10287: END IF;
10288:
10289: UPDATE mtl_material_transactions_temp
10290: SET transfer_lpn_id = l_to_lpn_id
10291: , lpn_id = NULL
10292: , content_lpn_id = NULL
10293: , subinventory_code = l_act_sub
10353: -- Bug 14261335
10354: SELECT COUNT(msnt.transaction_temp_id)
10355: INTO l_serial_count
10356: FROM mtl_serial_numbers_temp msnt,
10357: mtl_material_transactions_temp mmtt,
10358: mtl_transaction_lots_temp mtlt
10359: WHERE mmtt.transaction_temp_id = mtlt.transaction_temp_id
10360: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id
10361: AND mmtt.inventory_item_id = l_item_id
10434: -- Bug 14261335
10435: SELECT COUNT(msnt.transaction_temp_id)
10436: INTO l_serial_count
10437: FROM mtl_serial_numbers_temp msnt,
10438: mtl_material_transactions_temp mmtt
10439: WHERE mmtt.transaction_temp_id = msnt.transaction_temp_id
10440: AND mmtt.inventory_item_id = l_item_id
10441: AND mmtt.organization_id = p_org_id
10442: AND msnt.fm_serial_number = l_fm_serial;
10695:
10696:
10697: CURSOR mmtt_csr IS
10698: SELECT mmtt.transaction_temp_id
10699: FROM mtl_material_transactions_temp mmtt
10700: WHERE mmtt.transaction_header_id = l_txn_header_id
10701: AND mmtt.organization_id = l_org_id;
10702:
10703: -- VARAJAGO for bug 5222498, added to fetch the group_mark_id details of MSN
11045:
11046: -- CURSOR TO GET ALL MMTT LINES ASSOCIATED WITH DROP LPN
11047: CURSOR C_DROP_LPN_MMTT_LINE_CSR IS
11048: SELECT organization_id, TRANSACTION_TEMP_ID, MOVE_ORDER_LINE_ID, INVENTORY_ITEM_ID, PRIMARY_QUANTITY
11049: FROM mtl_material_transactions_temp mmtt
11050: WHERE TRANSACTION_HEADER_ID = l_txn_header_id
11051: AND mmtt.organization_id = l_org_id
11052: ORDER BY move_order_line_id asc,primary_quantity desc ;
11053:
11113: , l_mo_line_id
11114: , l_tran_source_type_id
11115: , l_inventory_item_id
11116: , l_tran_action_id
11117: FROM mtl_material_transactions_temp
11118: WHERE transaction_temp_id = l_temp_id
11119: AND organization_id = l_org_id;
11120:
11121: IF (p_drop_lpn = ''
11190: , l_mo_line_id
11191: , l_tran_source_type_id
11192: , l_inventory_item_id
11193: , l_tran_action_id
11194: FROM mtl_material_transactions_temp
11195: WHERE transaction_temp_id = l_temp_id
11196: AND organization_id = l_org_id; */
11197:
11198: BEGIN
11217: SELECT 1
11218: INTO l_cnt
11219: FROM DUAL
11220: WHERE EXISTS(SELECT 1
11221: FROM mtl_material_transactions_temp
11222: WHERE parent_line_id = l_temp_id);
11223: EXCEPTION
11224: WHEN NO_DATA_FOUND THEN
11225: l_cnt := 0;
11240: IF (l_debug = 1) THEN
11241: mydebug('pick_drop: User did not pick entire lpn');
11242: END IF;
11243:
11244: UPDATE mtl_material_transactions_temp
11245: SET transaction_header_id = l_txn_header_id
11246: , lpn_id = l_lpn_id
11247: , transfer_lpn_id = l_transfer_lpn_id
11248: , transaction_status = 3
11259: -- We have to do this as kind of a hack because we basically
11260: -- are picking one complete lpn but fulfilling multiple
11261: -- mmtt lines
11262:
11263: UPDATE mtl_material_transactions_temp
11264: SET transaction_header_id = l_txn_header_id
11265: , lpn_id = l_content_lpn_id
11266: , transfer_lpn_id = l_content_lpn_id
11267: , content_lpn_id = NULL
11286: END IF;
11287:
11288: -- Get rid of the original MMTT line (which was a bogus line created
11289: -- for bulk picking
11290: DELETE mtl_material_transactions_temp
11291: WHERE transaction_temp_id = l_temp_id;
11292: END IF;
11293: END IF;
11294:
11330: IF (l_debug = 1) THEN
11331: mydebug('pick_drop: Need to update the account period in MMTT');
11332: END IF;
11333:
11334: UPDATE mtl_material_transactions_temp
11335: SET acct_period_id = l_period_id
11336: WHERE transaction_header_id = l_txn_header_id
11337: AND organization_id = l_org_id;
11338: ELSE
11540: -- Modified for bug 12853197
11541:
11542: SELECT count(transaction_temp_id)
11543: INTO l_check_tasks
11544: FROM mtl_material_transactions_temp mmtt
11545: WHERE transfer_lpn_id = l_transfer_lpn_id
11546: AND transaction_header_id <> l_txn_header_id;
11547:
11548:
11561: ELSE
11562: l_lpn_context := wms_container_pub.LPN_CONTEXT_PACKING;
11563: END IF;
11564:
11565: UPDATE mtl_material_transactions_temp
11566: SET transfer_lpn_id = NULL
11567: WHERE transaction_header_id = l_txn_header_id;
11568:
11569: ELSE
11571: IF l_xfrlpnid<>l_transfer_lpn_id THEN
11572:
11573: l_lpn_context := wms_container_pub.lpn_context_inv;
11574:
11575: UPDATE mtl_material_transactions_temp
11576: SET transfer_lpn_id = l_xfrlpnid
11577: WHERE transaction_header_id = l_txn_header_id;
11578:
11579: IF (l_check_tasks=0) THEN
11589: IF l_check_tasks>0 THEN
11590:
11591: l_lpn_context := wms_container_pub.LPN_CONTEXT_PACKING;
11592:
11593: UPDATE mtl_material_transactions_temp
11594: SET transfer_lpn_id = null
11595: WHERE transaction_header_id = l_txn_header_id;
11596:
11597: ELSE
11624: -- (as it was before drop)
11625:
11626: SELECT count(transaction_temp_id)
11627: INTO l_check_tasks
11628: FROM mtl_material_transactions_temp mmtt
11629: WHERE transfer_lpn_id = l_transfer_lpn_id
11630: AND transaction_temp_id <> l_temp_id ;
11631: IF l_check_tasks = 0 THEN
11632: l_lpn_context := wms_container_pub.LPN_CONTEXT_PREGENERATED;
11643: END IF;
11644:
11645: --Bug # 2275770
11646: --Update mmtt.transaction_date to sysdate
11647: UPDATE mtl_material_transactions_temp
11648: SET transaction_date = SYSDATE
11649: WHERE transaction_header_id = l_txn_header_id;
11650:
11651: -- Now call the txn processor...
12409: --12595055 adding for LPN context remains packing context when dropped to non lpn controlled sub after lot substitution..
12410: IF (l_is_transfer_sub_lpn = 2 AND l_tran_action_id = 2 AND l_tran_source_type_id IN(4, 13)) THEN
12411: SELECT count(transaction_temp_id)
12412: INTO l_check_tasks
12413: FROM mtl_material_transactions_temp mmtt
12414: WHERE transfer_lpn_id = l_transfer_lpn_id
12415: AND transaction_temp_id <> l_temp_id ;
12416: IF l_check_tasks = 0 THEN
12417: l_lpn_context := wms_container_pub.LPN_CONTEXT_PREGENERATED;
12753: END IF;
12754:
12755: SELECT COUNT(*)
12756: INTO l_mmtt_rowcnt
12757: FROM mtl_material_transactions_temp m
12758: WHERE m.cartonization_id IS NOT NULL
12759: AND m.cartonization_id = l_lpn_id
12760: AND parent_line_id IS NULL;
12761:
12766: IF l_mmtt_rowcnt > 0 THEN
12767: -- There are MMTT tasks for this LPN
12768: SELECT COUNT(*)
12769: INTO l_wdt_rowcnt
12770: FROM mtl_material_transactions_temp m, wms_dispatched_tasks t
12771: WHERE m.cartonization_id = l_lpn_id
12772: AND t.transaction_temp_id = m.transaction_temp_id
12773: AND t.status = 4;
12774:
12844: , l_wms_task_type
12845: , l_std_op_id
12846: , l_operation_plan_id
12847: , l_move_order_line_id
12848: FROM mtl_material_transactions_temp t, bom_std_op_resources bsor, bom_resources bremp
12849: WHERE t.transaction_temp_id = task_rec.task_id
12850: AND t.standard_operation_id = bsor.standard_operation_id
12851: AND bsor.resource_id = bremp.resource_id
12852: AND bremp.resource_type = 2
12866: AND ROWNUM < 2;
12867: /*
12868: select breqp.resource_id equip_type_id
12869: INTO l_mac_res_id
12870: from mtl_material_transactions_temp t,
12871: bom_std_op_resources bsor,
12872: bom_resources breqp
12873: where t.transaction_temp_id = task_rec.task_id
12874: and t.standard_operation_id = bsor.standard_operation_id
12881: SELECT mtl_material_transactions_s.NEXTVAL txnhdrid
12882: INTO l_txn_hdr_id
12883: FROM DUAL;
12884:
12885: UPDATE mtl_material_transactions_temp
12886: SET transaction_header_id = l_txn_hdr_id
12887: WHERE transaction_temp_id = task_rec.task_id;
12888:
12889: -- Insert into WMS_DISPATCHED_TASKS for this user
13130: SELECT 1
13131: INTO l_mmtt_rowcnt
13132: FROM DUAL
13133: WHERE EXISTS(SELECT 1
13134: FROM mtl_material_transactions_temp
13135: WHERE transaction_temp_id = l_pick_slip_id
13136: AND parent_line_id is NULL);
13137: EXCEPTION
13138: WHEN NO_DATA_FOUND THEN
13191: , l_std_op_id
13192: , l_operation_plan_id
13193: , l_move_order_line_id
13194: , l_item_id --Bug6009436
13195: FROM mtl_material_transactions_temp t, bom_std_op_resources bsor, bom_resources bremp
13196: WHERE t.transaction_temp_id = l_pick_slip_id
13197: AND t.standard_operation_id = bsor.standard_operation_id
13198: AND bsor.resource_id = bremp.resource_id
13199: AND bremp.resource_type = 2
13223: AND ROWNUM < 2;
13224: /*
13225: select breqp.resource_id equip_type_id
13226: INTO l_mac_res_id
13227: from mtl_material_transactions_temp t,
13228: bom_std_op_resources bsor,
13229: bom_resources breqp
13230: where t.transaction_temp_id = task_rec.task_id
13231: and t.standard_operation_id = bsor.standard_operation_id
13255: SELECT mtl_material_transactions_s.NEXTVAL txnhdrid
13256: INTO l_txn_hdr_id
13257: FROM DUAL;
13258:
13259: UPDATE mtl_material_transactions_temp
13260: SET transaction_header_id = l_txn_hdr_id
13261: WHERE transaction_temp_id = l_pick_slip_id;
13262:
13263: --Bug6009436.Begin
13432: END IF;
13433:
13434: SELECT COUNT(*)
13435: INTO l_mmtt_rowcnt
13436: FROM mtl_material_transactions_temp m
13437: WHERE m.cartonization_id IS NOT NULL
13438: AND m.cartonization_id = l_lpn_id
13439: AND parent_line_id IS NULL;
13440:
13445: IF l_mmtt_rowcnt > 0 THEN
13446: -- There are MMTT tasks for this LPN
13447: SELECT COUNT(*)
13448: INTO l_wdt_rowcnt
13449: FROM mtl_material_transactions_temp m, wms_dispatched_tasks t
13450: WHERE m.cartonization_id = l_lpn_id
13451: AND t.transaction_temp_id = m.transaction_temp_id
13452: AND t.status = 4;
13453:
13697: WHERE mtlt.transaction_temp_id = p_temp_id
13698: AND msnt.transaction_temp_id = mtlt.serial_transaction_temp_id;
13699:
13700: BEGIN
13701: DELETE FROM mtl_material_transactions_temp
13702: WHERE transaction_temp_id = p_temp_id;
13703:
13704: DELETE FROM wms_dispatched_tasks
13705: WHERE transaction_temp_id = p_temp_id;
13791: , mmtt.move_order_line_id
13792: , mmtt.transaction_quantity
13793: , mmtt.transaction_uom
13794: , mmtt.primary_quantity
13795: FROM mtl_material_transactions_temp mmtt
13796: WHERE mmtt.transaction_temp_id = p_temp_id
13797: AND NOT EXISTS(SELECT 1
13798: FROM mtl_material_transactions_temp t1
13799: WHERE t1.parent_line_id = mmtt.transaction_temp_id)
13794: , mmtt.primary_quantity
13795: FROM mtl_material_transactions_temp mmtt
13796: WHERE mmtt.transaction_temp_id = p_temp_id
13797: AND NOT EXISTS(SELECT 1
13798: FROM mtl_material_transactions_temp t1
13799: WHERE t1.parent_line_id = mmtt.transaction_temp_id)
13800: UNION ALL
13801: SELECT mmtt.transaction_header_id
13802: , mmtt.transaction_temp_id
13809: , mmtt.move_order_line_id
13810: , mmtt.transaction_quantity
13811: , mmtt.transaction_uom
13812: , mmtt.primary_quantity
13813: FROM mtl_material_transactions_temp mmtt
13814: WHERE mmtt.parent_line_id = p_temp_id;
13815:
13816: CURSOR c_mo_line_info IS
13817: SELECT mtrh.move_order_type
13827: AND mtrh.header_id = mtrl.header_id;
13828:
13829: CURSOR c_get_other_mmtt IS
13830: SELECT COUNT(*)
13831: FROM mtl_material_transactions_temp mmtt
13832: WHERE mmtt.move_order_line_id = l_mo_line_id
13833: AND mmtt.transaction_temp_id <> l_txn_temp_id
13834: AND NOT EXISTS(SELECT 1
13835: FROM mtl_material_transactions_temp t1
13831: FROM mtl_material_transactions_temp mmtt
13832: WHERE mmtt.move_order_line_id = l_mo_line_id
13833: AND mmtt.transaction_temp_id <> l_txn_temp_id
13834: AND NOT EXISTS(SELECT 1
13835: FROM mtl_material_transactions_temp t1
13836: WHERE t1.parent_line_id = mmtt.transaction_temp_id);
13837: BEGIN
13838: IF (l_debug = 1) THEN
13839: mydebug('CLEANUP_TASK: Cleaning up the Task with Temp ID = ' || p_temp_id);
14347: , mmtt.inventory_item_id
14348: INTO l_transaction_type_id
14349: , l_org_id
14350: , l_item_id
14351: FROM mtl_material_transactions_temp mmtt
14352: WHERE mmtt.transaction_temp_id = p_temp_id;
14353:
14354: IF inv_material_status_grp.is_status_applicable(
14355: p_wms_installed => p_wms_installed
14447:
14448: -- bug fix 2805229
14449: SELECT wms_task_type
14450: INTO l_wms_task_type
14451: FROM mtl_material_transactions_temp
14452: WHERE transaction_temp_id = p_temp_id;
14453:
14454: IF l_wms_task_type = 7 THEN -- staging move
14455: IF (l_debug = 1) THEN
14467: mtrl.txn_source_id
14468: INTO l_mmtt_mo_type,
14469: l_txn_source_id --Bug#9659710
14470: FROM mtl_txn_request_headers mtrh, -- mo header for the new task
14471: mtl_txn_request_lines mtrl, mtl_material_transactions_temp mmtt
14472: WHERE mtrh.header_id = mtrl.header_id
14473: AND mtrl.line_id = mmtt.move_order_line_id
14474: AND mmtt.transaction_temp_id = p_temp_id;
14475:
14482: , mmtt.transfer_subinventory
14483: INTO l_lpn_controlled_flag
14484: , l_xfr_lpn_id
14485: , l_orig_xfr_sub
14486: FROM mtl_material_transactions_temp mmtt, mtl_secondary_inventories msi
14487: WHERE mmtt.transaction_temp_id = p_temp_id
14488: AND mmtt.organization_id = msi.organization_id
14489: AND msi.secondary_inventory_name = p_confirmed_drop_sub;
14490:
14513: INTO l_count
14514: FROM DUAL
14515: WHERE EXISTS(
14516: SELECT 1
14517: FROM mtl_material_transactions_temp mmtt
14518: WHERE mmtt.transaction_temp_id <> p_temp_id
14519: AND mmtt.transfer_lpn_id = l_xfr_lpn_id
14520: AND mmtt.transfer_subinventory <> p_confirmed_drop_sub
14521: AND mmtt.transfer_subinventory <> l_orig_xfr_sub);
14584:
14585: -- bug fix 2805229
14586: SELECT wms_task_type
14587: INTO l_wms_task_type
14588: FROM mtl_material_transactions_temp
14589: WHERE transaction_temp_id = p_temp_id;
14590:
14591: IF l_wms_task_type = 7 THEN -- staging move
14592: IF (l_debug = 1) THEN
14602:
14603: SELECT mtrh.move_order_type
14604: INTO l_mmtt_mo_type
14605: FROM mtl_txn_request_headers mtrh, -- mo header for the new task
14606: mtl_txn_request_lines mtrl, mtl_material_transactions_temp mmtt
14607: WHERE mtrh.header_id = mtrl.header_id
14608: AND mtrl.line_id = mmtt.move_order_line_id
14609: AND mmtt.transaction_temp_id = p_temp_id;
14610:
14617: , mmtt.transfer_subinventory
14618: INTO l_lpn_controlled_flag
14619: , l_xfr_lpn_id
14620: , l_orig_xfr_sub
14621: FROM mtl_material_transactions_temp mmtt, mtl_secondary_inventories msi
14622: WHERE mmtt.transaction_temp_id = p_temp_id
14623: AND mmtt.organization_id = msi.organization_id
14624: AND msi.secondary_inventory_name = p_confirmed_drop_sub;
14625:
14643: INTO l_count
14644: FROM DUAL
14645: WHERE EXISTS(
14646: SELECT 1
14647: FROM mtl_material_transactions_temp mmtt
14648: WHERE mmtt.transaction_temp_id <> p_temp_id
14649: AND mmtt.transfer_lpn_id = l_xfr_lpn_id
14650: AND mmtt.transfer_subinventory <> p_confirmed_drop_sub
14651: AND mmtt.transfer_subinventory <> l_orig_xfr_sub);
14758: , mmtt.transfer_lpn_id
14759: , mmtt.transaction_uom
14760: , mmtt.transaction_quantity
14761: , mmtt.primary_quantity
14762: FROM mtl_material_transactions_temp mmtt
14763: WHERE mmtt.transaction_header_id = p_txn_hdr_id
14764: AND mmtt.organization_id = p_org_id
14765: AND mmtt.transaction_quantity > 0
14766: AND mmtt.parent_line_id is NULL --Added bug3765153 to ensure only parent line are picked
14770: SELECT mmtt.transaction_temp_id
14771: , mmtt.transaction_uom
14772: , mmtt.transaction_quantity
14773: , mmtt.primary_quantity
14774: FROM mtl_material_transactions_temp mmtt
14775: WHERE mmtt.parent_line_id = p_temp_id
14776: AND mmtt.organization_id = p_org_id
14777: ORDER BY mmtt.transaction_quantity DESC;
14778:
14777: ORDER BY mmtt.transaction_quantity DESC;
14778:
14779: CURSOR unpicked_child_mmtt_lines(p_parent_line_id NUMBER,p_org_id NUMBER) IS
14780: SELECT mmtt.transaction_temp_id
14781: FROM mtl_material_transactions_temp mmtt
14782: WHERE mmtt.organization_id = p_org_id
14783: AND mmtt.parent_line_id = p_parent_line_id; --Added bug3765153 to determine and back order unpicked lines
14784:
14785: l_parent_txn_qty NUMBER;
14850: );
14851: END IF;
14852:
14853: IF l_parent_txn_qty >= l_child_txn_qty THEN
14854: UPDATE mtl_material_transactions_temp mmtt
14855: SET mmtt.transaction_header_id = x_new_txn_hdr_id
14856: , mmtt.transfer_lpn_id = l_transfer_lpn_id
14857: , mmtt.lpn_id = l_lpn_id
14858: , mmtt.parent_line_id = l_parent_txn_temp_id --Modified from NULL bug3765153
14873: select mtl_material_transactions_s.NEXTVAL
14874: into l_new_temp_id
14875: from dual; --Added bug3765153
14876:
14877: INSERT INTO mtl_material_transactions_temp
14878: (
14879: transaction_header_id
14880: , transaction_temp_id
14881: , source_code
15268: , task_priority
15269: , container_item_id
15270: , operation_plan_id
15271: , parent_line_id
15272: FROM mtl_material_transactions_temp
15273: WHERE transaction_temp_id = l_child_txn_temp_id);
15274:
15275: UPDATE mtl_material_transactions_temp mmtt
15276: SET mmtt.transaction_header_id = x_new_txn_hdr_id
15271: , parent_line_id
15272: FROM mtl_material_transactions_temp
15273: WHERE transaction_temp_id = l_child_txn_temp_id);
15274:
15275: UPDATE mtl_material_transactions_temp mmtt
15276: SET mmtt.transaction_header_id = x_new_txn_hdr_id
15277: , mmtt.transaction_quantity = l_parent_txn_qty
15278: , mmtt.primary_quantity = l_parent_pri_qty
15279: , mmtt.parent_line_id = NULL --l_parent_txn_temp_id --Modified from NULL bug3765153
15357: , wdt.task_type
15358: , SYSDATE
15359: , mmtt.operation_plan_id
15360: , mmtt.move_order_line_id
15361: FROM wms_dispatched_tasks wdt, mtl_material_transactions_temp mmtt
15362: WHERE wdt.transaction_temp_id = p_temp_id
15363: AND mmtt.transaction_header_id = x_new_txn_hdr_id);
15364:
15365: DELETE FROM wms_dispatched_tasks wdt
15363: AND mmtt.transaction_header_id = x_new_txn_hdr_id);
15364:
15365: DELETE FROM wms_dispatched_tasks wdt
15366: WHERE wdt.transaction_temp_id IN(SELECT mmtt.transaction_temp_id
15367: FROM mtl_material_transactions_temp mmtt
15368: WHERE mmtt.transaction_header_id = p_txn_hdr_id
15369: AND mmtt.organization_id = p_org_id);
15370:
15371: --Added bug 3765153 to clean up lines with remaining qty
15397: END IF;
15398:
15399: -- Deleting Parent Tasks. Once loaded, only Child Tasks are considered.
15400:
15401: DELETE FROM mtl_material_transactions_temp mmtt
15402: WHERE mmtt.transaction_header_id = p_txn_hdr_id
15403: AND mmtt.organization_id = p_org_id;
15404:
15405: -- nullify the parent_line_id for the children lines
15403: AND mmtt.organization_id = p_org_id;
15404:
15405: -- nullify the parent_line_id for the children lines
15406: -- since the parent lines are gone Bug3765153
15407: update mtl_material_transactions_temp
15408: set parent_line_id = null
15409: where transaction_header_id = x_new_txn_hdr_id;
15410:
15411: EXCEPTION
15542: mmtt.lpn_id,
15543: null,
15544: null
15545: INTO l_sub,l_loc,l_rev,l_lpn,l_ser,l_lot
15546: from mtl_material_Transactions_temp mmtt
15547: where mmtt.inventory_item_id = p_inventory_item_id
15548: and mmtt.organization_id = p_organization_id
15549: and mmtt.transfer_lpn_id = p_transfer_lpn_id
15550: and mmtt.content_lpn_id is null
15557: mmtt.lpn_id,
15558: null,
15559: mtlt.lot_number
15560: INTO l_sub,l_loc,l_rev,l_lpn,l_ser,l_lot
15561: from mtl_material_Transactions_temp mmtt,
15562: mtl_transaction_lots_temp mtlt
15563: where mmtt.inventory_item_id = p_inventory_item_id
15564: and mmtt.organization_id = p_organization_id
15565: and mmtt.transfer_lpn_id = p_transfer_lpn_id
15681: CURSOR pick_delivery_cursor IS
15682: SELECT wda.delivery_id
15683: FROM wsh_delivery_assignments wda,
15684: wsh_delivery_details wdd,
15685: mtl_material_transactions_temp temp
15686: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
15687: AND wdd.move_order_line_id = temp.move_order_line_id
15688: AND wdd.organization_id = temp.organization_id
15689: AND temp.transfer_lpn_id = p_pick_lpn_id
15809: AND rownum = 1 ;
15810:
15811: SELECT wdd.delivery_detail_id
15812: INTO l_line_rows(2)
15813: FROM wsh_delivery_details wdd, Mtl_material_transactions_temp mmtt
15814: WHERE mmtt.move_order_line_id = wdd.move_order_line_id
15815: AND wdd.organization_id = mmtt.organization_id
15816: AND mmtt.organization_id= p_organization_id
15817: AND mmtt.transfer_lpn_id= p_pick_lpn_id
15939: CURSOR pick_delivery_cursor IS
15940: SELECT wda.delivery_id
15941: FROM wsh_delivery_assignments wda,
15942: wsh_delivery_details wdd,
15943: mtl_material_transactions_temp temp
15944: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
15945: AND wdd.move_order_line_id = temp.move_order_line_id
15946: AND wdd.organization_id = temp.organization_id
15947: AND temp.transfer_lpn_id = p_pick_lpn_id
15995:
15996: BEGIN
15997: Select transfer_subinventory, transfer_to_location into l_drop_sub,
15998: l_drop_loc
15999: from mtl_material_transactions_temp
16000: where transfer_lpn_id = p_pick_lpn_id
16001: AND organization_id = p_organization_id;
16002:
16003: EXCEPTION
16085: mydebug('Inside l_mo_line_status not equal to 5 -- ');
16086: END IF;
16087: BEGIN
16088: SELECT COUNT(1) INTO l_mmtt_exists
16089: FROM mtl_material_transactions_temp
16090: WHERE move_order_line_id = p_mo_line_id;
16091: EXCEPTION
16092: WHEN no_data_found THEN
16093: l_mmtt_exists := 0;