[Home] [Help]
384: primary_quantity NUMBER,
385: delta_primary_quantity NUMBER,
386: secondary_quantity NUMBER,
387: delta_secondary_quantity NUMBER,
388: secondary_uom mtl_material_transactions_temp.secondary_uom_code%TYPE,
389: serial_transaction_temp_id NUMBER,
390: update_mtlt BOOLEAN,
391: delete_mtlt BOOLEAN);
392:
405: CURSOR mmtt_cursor(v_transaction_temp_id1 NUMBER, v_transaction_temp_id2 NUMBER) IS
406: SELECT inventory_item_id, transaction_temp_id, primary_quantity
407: , item_primary_uom_code, transaction_quantity, transaction_uom
408: , secondary_transaction_quantity, secondary_uom_code
409: FROM mtl_material_transactions_temp
410: WHERE transaction_temp_id IN (v_transaction_temp_id1, v_transaction_temp_id2)
411: FOR UPDATE;
412:
413: CURSOR mtlt_cursor(v_transaction_temp_id NUMBER) IS
1128: mydebug('Updating MMTT with delta qty: ' || l_delta_primary_quantity, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
1129: mydebug('Updating MMTT with delta sec qty: ' || l_delta_secondary_quantity, 'WMS_TASK_LOAD.UPDATE_LOADED_PART');
1130: END IF;
1131:
1132: UPDATE mtl_material_transactions_temp
1133: SET transaction_quantity = Round(l_primary_quantity*l_conversion_factor, l_g_decimal_precision),
1134: transaction_uom = p_transaction_uom,
1135: primary_quantity = l_primary_quantity,
1136: secondary_transaction_quantity = l_secondary_quantity,
1150: --, last_updated_by = fnd_global.user_id /* Bug 9448490 Lot Substitution Project */
1151: WHERE lpn_id = l_lpn_id;
1152:
1153: -- Update remaining MMTT record
1154: UPDATE mtl_material_transactions_temp
1155: SET transaction_quantity = transaction_quantity + Round(l_delta_primary_quantity*l_conversion_factor2, l_g_decimal_precision),
1156: primary_quantity = primary_quantity + l_delta_primary_quantity,
1157: secondary_transaction_quantity = secondary_transaction_quantity+l_delta_secondary_quantity,
1158: last_update_date = Sysdate,
1244:
1245: CURSOR cur_split_mmtts
1246: IS
1247: SELECT transaction_temp_id
1248: FROM mtl_material_transactions_temp
1249: WHERE transaction_header_id = p_transaction_header_id
1250: AND transaction_temp_id <> p_transaction_temp_id
1251: AND inventory_item_id = p_inventory_item_id
1252: AND nvl(revision,'@@') = nvl(p_revision,'@@')
2481: l_orig_mmtt_txn_uom,
2482: l_orig_mmtt_pri_qty,
2483: l_orig_mmtt_txn_qty,
2484: l_orig_mmtt_sec_qty
2485: FROM mtl_material_transactions_temp
2486: WHERE transaction_temp_id = p_transaction_temp_id;
2487: EXCEPTION
2488: WHEN OTHERS THEN
2489: l_fulfillment_base := 'P';
2612: x_new_transaction_temp_id := l_new_transaction_temp_id;
2613:
2614: mydebug(l_proc_name || ': l_new_transaction_temp_id = ' || l_new_transaction_temp_id);
2615:
2616: INSERT INTO mtl_material_transactions_temp
2617: ( TRANSACTION_HEADER_ID
2618: ,TRANSACTION_TEMP_ID
2619: ,SOURCE_CODE
2620: ,SOURCE_LINE_ID
3083: ,FOB_POINT
3084: ,MOVE_ORDER_HEADER_ID
3085: ,SERIAL_ALLOCATED_FLAG
3086: ,FULFILLMENT_BASE
3087: FROM mtl_material_transactions_temp
3088: WHERE transaction_temp_id = p_transaction_temp_id);
3089: IF SQL%NOTFOUND THEN
3090: mydebug (' p_transaction_temp_id: NOT found : ' || p_transaction_temp_id);
3091: fnd_message.set_name('WMS', 'WMS_INSERT_ALLOCATION'); -- NEWMSG
3307: FROM mtl_transaction_lots_temp
3308: WHERE transaction_temp_id = p_transaction_temp_id
3309: GROUP BY transaction_temp_id;
3310:
3311: UPDATE mtl_material_transactions_temp
3312: SET transaction_quantity = l_rem_lot_trx_qty
3313: , primary_quantity = l_rem_lot_pri_qty
3314: , secondary_transaction_quantity = l_rem_lot_sec_qty
3315: , last_update_date = SYSDATE
3316: , last_updated_by = p_user_id
3317: WHERE transaction_temp_id = p_transaction_temp_id;
3318:
3319: ELSE
3320: UPDATE mtl_material_transactions_temp
3321: SET transaction_quantity = l_calc_mmtt_txn_qty
3322: , primary_quantity = l_calc_mmtt_pri_qty
3323: , secondary_transaction_quantity = secondary_transaction_quantity - l_confirmed_sec_qty
3324: , last_update_date = SYSDATE
3331: END IF;
3332: ELSE -- LOAD
3333: l_progress := '200';
3334: mydebug('l_progress : ' || l_progress);
3335: UPDATE mtl_material_transactions_temp
3336: SET transaction_quantity = p_confirmed_trx_qty
3337: , primary_quantity = l_confirmed_prim_qty -- muom:sk added decode this is for full pick again.. SSK
3338: , secondary_transaction_quantity = DECODE(p_confirmed_sec_uom, NULL, NULL, l_confirmed_sec_qty)
3339: , secondary_uom_code = p_confirmed_sec_uom
3360: IF p_update = 'Y2' -- and update p_transaction_temp_id_to_merge to add qty)
3361: THEN
3362: l_progress := '190';
3363: mydebug ('l_progress in Y2 ' || l_progress);
3364: UPDATE mtl_material_transactions_temp
3365: SET transaction_quantity = transaction_quantity + p_confirmed_trx_qty
3366: , primary_quantity = primary_quantity + NVL(l_confirmed_prim_qty ,0)
3367: , secondary_transaction_quantity = secondary_transaction_quantity + NVL(l_confirmed_sec_qty, 0)
3368: , secondary_uom_code = p_confirmed_sec_uom
3375:
3376: IF p_action = l_g_action_load_multiple THEN
3377: l_progress := '190'; -- Delete the original MMTT, if merging into another MMTT
3378: mydebug ('l_progress for load multipe in Y2 ' || l_progress);
3379: DELETE mtl_material_transactions_temp
3380: WHERE transaction_temp_id = p_transaction_temp_id;
3381: IF SQL%NOTFOUND THEN
3382: RAISE fnd_api.G_EXC_ERROR;
3383: END IF;
3384:
3385: ELSE -- 'SPLIT'
3386: l_progress := '200';
3387: mydebug('l_progress for split case in p_action Y2 ' || l_progress);
3388: UPDATE mtl_material_transactions_temp
3389: SET transaction_quantity = l_calc_mmtt_txn_qty
3390: , primary_quantity = l_calc_mmtt_pri_qty
3391: , secondary_transaction_quantity = secondary_transaction_quantity - l_confirmed_sec_qty
3392: , last_update_date = SYSDATE
5459: , locator_id
5460: , item_primary_uom_code
5461: , SECONDARY_UOM_CODE --BUG12622871LSC
5462: , fulfillment_base
5463: FROM mtl_material_transactions_temp mmtt
5464: WHERE mmtt.transaction_header_id = p_transaction_header_id
5465: GROUP BY
5466: inventory_item_id
5467: ,revision
5484: ,COUNT(*) group_lot_count
5485: ,MIN(mtlt.transaction_temp_id) group_lot_temp_id
5486: ,mtlt.lot_number
5487: FROM mtl_transaction_lots_temp mtlt
5488: , mtl_material_transactions_temp mmtt
5489: WHERE mmtt.transaction_header_id = p_transaction_header_id
5490: AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
5491: AND mmtt.subinventory_code = p_subinventory_code
5492: AND mmtt.locator_id = p_locator_id
5507: ,mmtt.organization_id
5508: ,mmtt.inventory_item_id
5509: ,msnt.creation_date
5510: FROM mtl_serial_numbers_temp msnt
5511: ,mtl_material_transactions_temp mmtt
5512: WHERE mmtt.transaction_header_id = p_transaction_header_id
5513: AND mmtt.transaction_temp_id = msnt.transaction_temp_id
5514: AND mmtt.subinventory_code = p_rec_mmtt1_subinventory_code
5515: AND mmtt.locator_id = p_rec_mmtt1_locator_id
5709:
5710: /* Update the MMTT record with transacttion-temp_id = group_temp_id.
5711: Updating transaction_quantity same as primary_quantity since
5712: the transaction_qty should be IN same uom AS primary qty FOR this mmtt */
5713: UPDATE mtl_material_transactions_temp
5714: SET primary_quantity = rec_mmtt1.mmtt_primary_quantity
5715: , transaction_quantity = l_suggested_mmtt_qty
5716: , secondary_transaction_quantity = DECODE(secondary_transaction_quantity, NULL, NULL, l_suggested_mmtt_sec_qty)
5717: , transaction_uom = p_suggested_uom
5728: END IF;
5729:
5730: -- Bug# 4185621: update child line posting flag back to 'Y' for bulk picking
5731: IF (l_parent_posting_flag = 'N') THEN
5732: UPDATE mtl_material_transactions_temp mmtt
5733: SET posting_flag = 'Y'
5734: WHERE parent_line_id = rec_mmtt1.group_temp_id
5735: AND parent_line_id <> transaction_temp_id;
5736: END IF;
5844: , last_updated_by = p_user_id
5845: WHERE transaction_temp_id IN
5846: (SELECT mtlt.serial_transaction_temp_id
5847: FROM mtl_transaction_lots_temp mtlt
5848: , mtl_material_transactions_temp mmtt
5849: WHERE mmtt.transaction_header_id = p_transaction_header_id
5850: AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
5851: AND mmtt.subinventory_code = rec_mmtt1.subinventory_code
5852: AND mmtt.locator_id = rec_mmtt1.locator_id
5889: ,mmtt.inventory_item_id
5890: ,msnt.fm_serial_number
5891: FROM mtl_transaction_lots_temp mtlt
5892: ,mtl_serial_numbers_temp msnt
5893: ,mtl_material_transactions_temp mmtt
5894: WHERE mmtt.transaction_header_id = p_transaction_header_id
5895: AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
5896: AND mtlt.serial_transaction_temp_id = msnt.transaction_temp_id
5897: AND mmtt.subinventory_code = rec_mmtt1.subinventory_code
5905: (SELECT mmtt.organization_id
5906: ,mmtt.inventory_item_id
5907: ,msnt.fm_serial_number
5908: FROM mtl_serial_numbers_temp msnt
5909: ,mtl_material_transactions_temp mmtt
5910: WHERE mmtt.transaction_header_id = p_transaction_header_id
5911: AND mmtt.transaction_temp_id = msnt.transaction_temp_id
5912: AND mmtt.subinventory_code = rec_mmtt1.subinventory_code
5913: AND mmtt.locator_id = rec_mmtt1.locator_id
5927: WHERE transaction_temp_id
5928: IN
5929: (SELECT mtlt.serial_transaction_temp_id
5930: FROM mtl_transaction_lots_temp mtlt
5931: , mtl_material_transactions_temp mmtt
5932: WHERE mmtt.transaction_header_id = p_transaction_header_id
5933: AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
5934: AND mmtt.subinventory_code = rec_mmtt1.subinventory_code
5935: AND mmtt.locator_id = rec_mmtt1.locator_id
5940: UNION --For lot substitution of lot and serial items when serial are confirmed we insert msnt with mmtt.transaction_temp_id
5941: --at that time their is no MTLT for substituted lot..
5942: (SELECT msnt.transaction_temp_id
5943: FROM mtl_serial_numbers_temp msnt
5944: ,mtl_material_transactions_temp mmtt
5945: WHERE mmtt.transaction_header_id = p_transaction_header_id
5946: AND mmtt.transaction_temp_id = msnt.transaction_temp_id
5947: AND mmtt.subinventory_code = rec_mmtt1.subinventory_code
5948: AND mmtt.locator_id = rec_mmtt1.locator_id
6044: WHERE lot_number = rec_mtlt1.lot_number
6045: AND transaction_temp_id
6046: IN
6047: (SELECT mtlt.transaction_temp_id
6048: FROM mtl_material_transactions_temp mmtt
6049: ,mtl_transaction_lots_temp mtlt
6050: WHERE mmtt.transaction_header_id = p_transaction_header_id
6051: AND mmtt.transaction_temp_id <> rec_mmtt1.group_temp_id
6052: AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
6090: WHERE transaction_temp_id
6091: IN
6092: (SELECT msnt.transaction_temp_id
6093: FROM mtl_serial_numbers_temp msnt,
6094: mtl_material_transactions_temp mmtt
6095: WHERE mmtt.transaction_header_id = p_transaction_header_id
6096: AND mmtt.transaction_temp_id <> rec_mmtt1.group_temp_id
6097: AND mmtt.transaction_temp_id = msnt.transaction_temp_id
6098: AND mmtt.subinventory_code = rec_mmtt1.subinventory_code
6139: IN (SELECT mmtt.organization_id
6140: ,mmtt.inventory_item_id
6141: ,msnt.fm_serial_number
6142: FROM mtl_serial_numbers_temp msnt
6143: ,mtl_material_transactions_temp mmtt
6144: WHERE mmtt.transaction_header_id = p_transaction_header_id
6145: AND mmtt.transaction_temp_id = msnt.transaction_temp_id
6146: AND mmtt.subinventory_code = rec_mmtt1.subinventory_code
6147: AND mmtt.locator_id = rec_mmtt1.locator_id
6159: DELETE mtl_serial_numbers_temp
6160: WHERE transaction_temp_id IN
6161: (SELECT msnt.transaction_temp_id
6162: FROM mtl_serial_numbers_temp msnt,
6163: mtl_material_transactions_temp mmtt
6164: WHERE mmtt.transaction_header_id = p_transaction_header_id
6165: AND mmtt.transaction_temp_id = msnt.transaction_temp_id
6166: AND mmtt.subinventory_code = rec_mmtt1.subinventory_code
6167: AND mmtt.locator_id = rec_mmtt1.locator_id
6186: IN
6187: (SELECT msnt.transaction_temp_id
6188: ,msnt.fm_serial_number
6189: FROM mtl_serial_numbers_temp msnt
6190: ,mtl_material_transactions_temp mmtt
6191: WHERE mmtt.transaction_header_id = p_transaction_header_id
6192: AND mmtt.transaction_temp_id = msnt.transaction_temp_id
6193: AND mmtt.subinventory_code = rec_mmtt1.subinventory_code
6194: AND mmtt.locator_id = rec_mmtt1.locator_id
6332:
6333: DELETE wms_dispatched_tasks
6334: WHERE transaction_temp_id IN
6335: (SELECT transaction_temp_id
6336: FROM mtl_material_transactions_temp
6337: WHERE transaction_header_id = p_transaction_header_id
6338: AND transaction_temp_id NOT IN
6339: (SELECT transaction_temp_id
6340: FROM mtl_allocations_gtmp));
6366: END IF;
6367:
6368: l_progress := 2500;
6369: mydebug('l_progress .. ' || l_progress);
6370: DELETE mtl_material_transactions_temp
6371: WHERE transaction_header_id = p_transaction_header_id
6372: AND transaction_temp_id NOT IN
6373: (SELECT transaction_temp_id
6374: FROM mtl_allocations_gtmp);
6571: -- Select all MOLs that are cancelled , so that cancelled tasks can be reduced
6572: CURSOR cur_cancelled_MOLs IS
6573: SELECT mtrl.line_id
6574: , mtrl.uom_code
6575: FROM mtl_material_transactions_temp mmtt
6576: , mtl_txn_request_lines mtrl
6577: WHERE (mmtt.transaction_temp_id = p_transaction_temp_id
6578: -- shld add : and mmtt.mmtt.transaction_temp_id <> mmtt.parent_line_id
6579: OR mmtt.parent_line_id = p_transaction_temp_id)
6589: , mmtt.inventory_item_id
6590: , mmtt.primary_quantity
6591: , mmtt.item_primary_uom_code
6592: , NVL(mmtt.secondary_transaction_quantity, 0) secondary_transaction_quantity
6593: FROM mtl_material_transactions_temp mmtt
6594: WHERE mmtt.move_order_line_id = p_mo_line_id
6595: AND NOT EXISTS(SELECT 1
6596: FROM mtl_material_transactions_temp t1
6597: WHERE t1.parent_line_id = mmtt.transaction_temp_id)
6592: , NVL(mmtt.secondary_transaction_quantity, 0) secondary_transaction_quantity
6593: FROM mtl_material_transactions_temp mmtt
6594: WHERE mmtt.move_order_line_id = p_mo_line_id
6595: AND NOT EXISTS(SELECT 1
6596: FROM mtl_material_transactions_temp t1
6597: WHERE t1.parent_line_id = mmtt.transaction_temp_id)
6598: AND NOT EXISTS(SELECT 1
6599: FROM wms_dispatched_tasks wdt
6600: WHERE wdt.transaction_temp_id = mmtt.transaction_temp_id);
6645: -- all MMTTs for the given MOL, this MMTT should not have any child records
6646:
6647: SELECT count(*)
6648: INTO l_mmtt_count
6649: FROM mtl_material_transactions_temp mmtt
6650: WHERE mmtt.move_order_line_id = rec_cancelled_mols.line_id
6651: AND NOT EXISTS ( SELECT 1
6652: FROM mtl_material_transactions_temp t1
6653: WHERE t1.parent_line_id = mmtt.transaction_temp_id);
6648: INTO l_mmtt_count
6649: FROM mtl_material_transactions_temp mmtt
6650: WHERE mmtt.move_order_line_id = rec_cancelled_mols.line_id
6651: AND NOT EXISTS ( SELECT 1
6652: FROM mtl_material_transactions_temp t1
6653: WHERE t1.parent_line_id = mmtt.transaction_temp_id);
6654:
6655: UPDATE mtl_txn_request_lines
6656: SET quantity_detailed =(quantity_detailed - l_deleted_mmtt_qty)
6712: l_lpn_context_packing CONSTANT NUMBER := WMS_Container_PUB.LPN_CONTEXT_PACKING ;
6713:
6714: CURSOR cur_from_lpns IS
6715: SELECT DISTINCT lpn_id
6716: FROM mtl_material_transactions_temp
6717: WHERE transaction_header_id = p_transaction_header_id
6718: AND lpn_id IS NOT NULL;
6719:
6720: CURSOR cur_content_lpns IS
6718: AND lpn_id IS NOT NULL;
6719:
6720: CURSOR cur_content_lpns IS
6721: SELECT DISTINCT content_lpn_id
6722: FROM mtl_material_transactions_temp
6723: WHERE transaction_header_id = p_transaction_header_id
6724: AND content_lpn_id IS NOT NULL;
6725:
6726: --modified for bug 6642448
6725:
6726: --modified for bug 6642448
6727: CURSOR cur_transfer_lpns IS
6728: SELECT DISTINCT transfer_lpn_id
6729: FROM mtl_material_transactions_temp mmtt
6730: WHERE transaction_header_id = p_transaction_header_id
6731: AND nvl(content_lpn_id , nvl(lpn_id,-999)) <> transfer_lpn_id
6732: aND not exists ( select 1 from mtl_material_transactions_temp mmtt1
6733: where mmtt1.transaction_header_id = p_transaction_header_id
6728: SELECT DISTINCT transfer_lpn_id
6729: FROM mtl_material_transactions_temp mmtt
6730: WHERE transaction_header_id = p_transaction_header_id
6731: AND nvl(content_lpn_id , nvl(lpn_id,-999)) <> transfer_lpn_id
6732: aND not exists ( select 1 from mtl_material_transactions_temp mmtt1
6733: where mmtt1.transaction_header_id = p_transaction_header_id
6734: and mmtt.transfer_lpn_id=mmtt1.transfer_lpn_id
6735: and (mmtt1.lpn_id=mmtt1.transfer_lpn_id or
6736: mmtt1.content_lpn_id=mmtt1.transfer_lpn_id )); --BUG 12803567
6752: SELECT 1
6753: INTO l_other_tasks
6754: FROM DUAL
6755: WHERE EXISTS(SELECT 1
6756: FROM mtl_material_transactions_temp
6757: WHERE transaction_header_id <> p_transaction_header_id
6758: AND transfer_lpn_id = rec_transfer_lpns.transfer_lpn_id);
6759: EXCEPTION
6760: WHEN NO_DATA_FOUND THEN
6810: SELECT 1
6811: INTO l_other_tasks
6812: FROM DUAL
6813: WHERE EXISTS(SELECT 1
6814: FROM mtl_material_transactions_temp
6815: WHERE transaction_header_id <> p_transaction_header_id
6816: AND content_lpn_id = rec_content_lpns.content_lpn_id);
6817: EXCEPTION
6818: WHEN NO_DATA_FOUND THEN
6899:
6900: DELETE wms_dispatched_tasks
6901: WHERE transaction_temp_id IN
6902: (SELECT transaction_temp_id
6903: FROM mtl_material_transactions_temp
6904: WHERE transaction_header_id = p_transaction_header_id);
6905: IF SQL%NOTFOUND THEN
6906: mydebug('no WDTs to delete for this header:' );
6907: ELSE
6939: WHERE person_id = p_employee_id
6940: AND ( status = l_g_task_dispatched OR
6941: status = l_g_task_active) -- IN (3,9 ) ;
6942: AND transaction_temp_id in (SELECT transaction_temp_id
6943: FROM mtl_material_transactions_temp
6944: WHERE transaction_header_id = p_transaction_header_id);
6945:
6946: IF SQL%NOTFOUND THEN
6947: mydebug('no WDT to update for this employee id for this header id with stat in ( 3,9) ' );
7533: l_fulfillment_base VARCHAR2(1) := p_fulfillment_base; -- muom:16070349
7534:
7535: CURSOR mmtt_csr2(p_transaction_header_id NUMBER) IS
7536: SELECT mmtt.transaction_temp_id
7537: FROM mtl_material_transactions_temp mmtt
7538: WHERE mmtt.transaction_header_id = p_transaction_header_id;
7539:
7540: CURSOR lot_csr IS
7541: SELECT lot_number, serial_transaction_temp_id
7552: AND msn.serial_number NOT IN
7553: ( select msnt.fm_serial_number
7554: from mtl_serial_numbers_temp msnt,
7555: mtl_transaction_lots_temp mtlt,
7556: mtl_material_transactions_temp mmtt
7557: where mmtt.inventory_item_id = p_item_id
7558: AND mmtt.organization_id = p_organization_id
7559: and mtlt.transaction_temp_id(+) = mmtt.transaction_temp_id
7560: AND msnt.transaction_temp_id = NVL(mtlt.serial_transaction_temp_id, mmtt.transaction_temp_id)
7562: and mmtt.transaction_temp_id = p_temp_id);
7563:
7564: CURSOR get_mmtt_qty (p_reservation_id NUMBER) IS
7565: SELECT NVL(SUM(mtlt.primary_quantity) , SUM(mmtt.primary_quantity))
7566: FROM mtl_material_transactions_temp mmtt, mtl_transaction_lots_temp mtlt
7567: WHERE mmtt.reservation_id = p_reservation_id
7568: AND mmtt.transaction_temp_id = mtlt.transaction_temp_id(+);
7569:
7570: l_new_mmtt_qty NUMBER;
7652: --Get the move_order_line_id for the current task
7653: BEGIN
7654: SELECT move_order_line_id
7655: INTO l_mo_line_id
7656: FROM mtl_material_transactions_temp
7657: WHERE transaction_temp_id = p_temp_id;
7658: EXCEPTION
7659: WHEN OTHERS THEN
7660: l_mo_line_id := NULL;
8084: --Fetch the primary quantity for all MMTTs for this MO line
8085: --10120826 Fetch secondary_transaction_quantity for all MMTTs for this MO line
8086: SELECT ABS(SUM(primary_quantity)), ABS(SUM(secondary_transaction_quantity))
8087: INTO l_sum_mmtt_qty, l_sum_mmtt_sec_txn_qty
8088: FROM mtl_material_transactions_temp
8089: WHERE move_order_line_id = l_mo_line_id;
8090:
8091: --Convert the MMTT primary quantity into MOL UOM
8092: -- muom:sk
8140: BEGIN --16760116 Added exception Block
8141:
8142: SELECT reservation_id
8143: INTO l_reservation_id
8144: FROM mtl_material_transactions_temp
8145: WHERE transaction_temp_id = p_temp_id
8146: FOR UPDATE;
8147:
8148: OPEN get_mmtt_qty(l_reservation_id);
8689: SELECT 1
8690: INTO l_loaded
8691: FROM DUAL
8692: WHERE EXISTS(SELECT 1
8693: FROM mtl_material_transactions_temp
8694: WHERE (transfer_lpn_id = p_fromlpn_id
8695: OR content_lpn_id = p_fromlpn_id)
8696: AND organization_id = p_org_id); -- this AND organization_id condition is added for Bug# 12541060
8697: EXCEPTION
8778: l_transaction_header_id,
8779: l_transaction_uom,
8780: l_sec_transaction_uom, -- Bug #4141928
8781: l_transaction_source_type_id --11068325
8782: from mtl_material_transactions_temp
8783: where transaction_temp_id = p_temp_id;
8784:
8785: select nvl(project_id ,-999) , nvl(task_id ,-999)
8786: into l_mmtt_proj_id , l_mmtt_task_id
8867: , mmtt.locator_id
8868: INTO l_xfr_sub_code
8869: , l_mmtt_sub
8870: , l_mmtt_loc
8871: FROM mtl_material_transactions_temp mmtt
8872: WHERE mmtt.transaction_temp_id = p_temp_id;
8873:
8874: -- Check to see if the item is in the LPN
8875: IF (l_debug = 1) THEN
8983:
8984: BEGIN
8985: SELECT allocated_lpn_id
8986: INTO l_allocated_lpn_id
8987: FROM mtl_material_transactions_temp
8988: WHERE transaction_temp_id = p_temp_id;
8989: EXCEPTION
8990: WHEN NO_DATA_FOUND THEN
8991: IF (l_debug = 1) THEN
9778: END IF;
9779:
9780: SELECT primary_quantity, NVL(secondary_transaction_quantity, 0) -- Bug #4141928
9781: INTO l_mmtt_qty, l_mmtt_sec_qty -- Bug #4141928
9782: FROM mtl_material_transactions_temp
9783: WHERE transaction_temp_id = p_temp_id;
9784:
9785: -- If item is lot controlled then validate the lots
9786:
12086: ORDER BY LOT_NUMBER;
12087:
12088: CURSOR mmtt_csr IS
12089: SELECT transfer_subinventory
12090: FROM mtl_material_transactions_temp
12091: WHERE transaction_temp_id = p_temp_id; -- Bug #7257709
12092:
12093: --/* Bug 9448490 Lot Substitution Project */ start
12094: CURSOR lot_substitution_att IS
12258:
12259: END IF;
12260:
12261: IF (p_suggested_locator = p_confirmed_locator) THEN
12262: UPDATE mtl_material_transactions_temp mmtt
12263: SET posting_flag = 'N'
12264: WHERE transaction_temp_id = p_temp_id;
12265: END IF;
12266:
12292:
12293: --Bug 4185621: restore posting flag in mmtt
12294:
12295: IF (p_suggested_locator = p_confirmed_locator) THEN
12296: UPDATE mtl_material_transactions_temp mmtt
12297: SET posting_flag = 'Y'
12298: WHERE transaction_temp_id = p_temp_id;
12299: END IF;
12300: -- End change - Bug 4185621
12602: l_debug NUMBER := NVL(fnd_profile.VALUE('INV_DEBUG_TRACE'), 0);
12603: CURSOR c_cancelled_tasks IS
12604: --SELECT decode(mmtt.transaction_type_id, 35,'N',51,'N','Y')
12605: SELECT 'FAIL'
12606: FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mol
12607: WHERE mmtt.transaction_temp_id = p_transaction_temp_id
12608: AND mmtt.move_order_line_id = mol.line_id
12609: AND mol.line_status = inv_globals.g_to_status_cancel_by_source
12610: AND ROWNUM = 1;
12703:
12704:
12705: --Bug7120019
12706: IF wms_control.g_current_release_level >= 120001 THEN
12707: l_CursorStmt := 'SELECT count (*) FROM mtl_material_transactions_temp mmtt, wms_user_task_type_attributes wutta '||
12708: 'WHERE mmtt.transaction_temp_id = :x_temp_id ' ||
12709: ' AND mmtt.standard_operation_id = wutta.user_task_type_id '||
12710: ' AND mmtt.organization_id = wutta.organization_id '||
12711: ' AND wutta.honor_case_pick_flag = ''Y'' '; --Added for Bug#7584906
12963: CURSOR others_in_mmtt_delivery_cursor(l_lpn_id IN NUMBER) IS
12964: SELECT wda.delivery_id
12965: FROM wsh_delivery_assignments_v wda
12966: , wsh_delivery_details_ob_grp_v wdd
12967: , mtl_material_transactions_temp mmtt
12968: WHERE mmtt.transfer_lpn_id = l_lpn_id
12969: AND wda.delivery_detail_id = wdd.delivery_detail_id
12970: AND wdd.move_order_line_id = mmtt.move_order_line_id
12971: AND wdd.organization_id = mmtt.organization_id;
12982: CURSOR current_delivery_cursor IS
12983: SELECT wda.delivery_id
12984: FROM wsh_delivery_assignments_v wda
12985: , wsh_delivery_details_ob_grp_v wdd
12986: , mtl_material_transactions_temp mmtt
12987: WHERE wda.delivery_detail_id = wdd.delivery_detail_id
12988: AND wdd.move_order_line_id = mmtt.move_order_line_id
12989: AND wdd.organization_id = mmtt.organization_id
12990: AND mmtt.transaction_temp_id = p_temp_id
13005: --
13006: CURSOR lpn_project_task_cursor(p_pick_to_lpn_id NUMBER) IS
13007: SELECT NVL(mil.project_id, -1)
13008: , NVL(mil.task_id, -1)
13009: FROM mtl_item_locations mil, mtl_material_transactions_temp mmtt
13010: WHERE mil.inventory_location_id = mmtt.transfer_to_location
13011: AND mil.organization_id = mmtt.organization_id
13012: AND mmtt.transfer_lpn_id = p_pick_to_lpn_id
13013: AND mmtt.organization_id = p_organization_id;
13018: --
13019: CURSOR mtl_project_task_cursor IS
13020: SELECT NVL(mil.project_id, -1)
13021: , NVL(mil.task_id, -1)
13022: FROM mtl_item_locations mil, mtl_material_transactions_temp mmtt
13023: WHERE mil.inventory_location_id = mmtt.transfer_to_location
13024: AND mil.organization_id = mmtt.organization_id
13025: AND mmtt.organization_id = p_organization_id
13026: AND mmtt.transaction_temp_id = p_temp_id;
13026: AND mmtt.transaction_temp_id = p_temp_id;
13027:
13028: CURSOR current_carton_grouping_cursor IS
13029: SELECT mol.carton_grouping_id
13030: FROM mtl_txn_request_lines mol, mtl_material_transactions_temp mmtt
13031: WHERE mmtt.transaction_temp_id = p_temp_id
13032: AND mmtt.organization_id = mol.organization_id
13033: AND mmtt.move_order_line_id = mol.line_id;
13034:
13033: AND mmtt.move_order_line_id = mol.line_id;
13034:
13035: CURSOR others_carton_grouping_cursor(p_lpn_id IN NUMBER) IS
13036: SELECT DISTINCT mol.carton_grouping_id
13037: FROM mtl_txn_request_lines mol, mtl_material_transactions_temp mmtt
13038: WHERE mmtt.transfer_lpn_id = p_lpn_id
13039: AND mmtt.organization_id = mol.organization_id
13040: AND mmtt.move_order_line_id = mol.line_id;
13041:
13371: , l_item_id
13372: , l_operation_plan_id
13373: , l_parent_line_id
13374: , l_transaction_header_id
13375: FROM mtl_material_transactions_temp mmtt
13376: WHERE mmtt.transaction_temp_id = p_temp_id;
13377:
13378: IF (l_debug = 1) THEN
13379: mydebug('validate_pick_to_lpn: parent line id:'||l_parent_line_id);
13388: select 'N'
13389: into l_multiple_pick
13390: from dual
13391: where exists (select 1
13392: from mtl_material_transactions_temp
13393: where transfer_lpn_id = pick_to_lpn_rec.lpn_id
13394: and transaction_header_id <>l_transaction_header_id);
13395:
13396: fnd_message.set_name('WMS', 'WMS_INVLD_PICKTO_LPN_NOT_NEW'); -- new message
13405: select 'Y'
13406: into l_bulk_task_exist
13407: from dual
13408: where exists (select 1
13409: from mtl_material_transactions_temp
13410: where transfer_lpn_id = pick_to_lpn_rec.lpn_id
13411: and transaction_temp_id = parent_line_id -- bulk task
13412: );
13413: fnd_message.set_name('WMS', 'WMS_INVLD_PICKTO_LPN_BULK');
13437: , l_mmtt_txn_type_id
13438: , l_mmtt_wip_entity_type
13439: FROM mtl_txn_request_headers mtrh
13440: , mtl_txn_request_lines mtrl
13441: , mtl_material_transactions_temp mmtt
13442: WHERE mtrh.header_id = mtrl.header_id
13443: AND mtrl.line_id = mmtt.move_order_line_id
13444: AND mmtt.transaction_temp_id = p_temp_id;
13445:
13449: INTO l_mo_type_in_lpn
13450: , l_wip_entity_type_in_lpn
13451: FROM mtl_txn_request_headers mtrh
13452: , mtl_txn_request_lines mtrl
13453: , mtl_material_transactions_temp mmtt
13454: WHERE mtrh.header_id = mtrl.header_id
13455: AND mtrl.line_id = mmtt.move_order_line_id
13456: AND mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
13457: AND ROWNUM < 2;
13505: INTO l_xfr_sub
13506: , l_xfr_to_location
13507: , l_item_id
13508: , l_operation_plan_id
13509: FROM mtl_material_transactions_temp mmtt
13510: WHERE mmtt.transaction_temp_id = p_temp_id; */
13511:
13512: l_lpn_controlled_flag := wms_globals.g_non_lpn_controlled_sub;
13513:
13532:
13533: BEGIN
13534: SELECT COUNT(*)
13535: INTO l_count
13536: FROM mtl_material_transactions_temp mmtt
13537: WHERE mmtt.transaction_temp_id <> p_temp_id
13538: AND mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
13539: AND ( NVL(mmtt.transfer_subinventory, 0) <> l_xfr_sub
13540: OR
13573: INTO l_count
13574: FROM DUAL
13575: WHERE EXISTS
13576: ( SELECT 'x'
13577: FROM mtl_material_transactions_temp mmtt
13578: , mtl_secondary_inventories msi
13579: WHERE mmtt.transaction_temp_id <> p_temp_id
13580: AND mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
13581: AND msi.organization_id = p_organization_id
13603: BEGIN
13604:
13605: SELECT 'Y'
13606: INTO l_fb_comingle
13607: FROM mtl_material_transactions_temp mmtt
13608: WHERE mmtt.transaction_temp_id = p_temp_id
13609: AND mmtt.organization_id = p_organization_id
13610: AND EXISTS(SELECT 1
13611: FROM mtl_material_transactions_temp mmtt1
13607: FROM mtl_material_transactions_temp mmtt
13608: WHERE mmtt.transaction_temp_id = p_temp_id
13609: AND mmtt.organization_id = p_organization_id
13610: AND EXISTS(SELECT 1
13611: FROM mtl_material_transactions_temp mmtt1
13612: WHERE mmtt1.transaction_temp_id <> p_temp_id
13613: AND mmtt1.organization_id = p_organization_id
13614: AND mmtt1.inventory_item_id = p_inventory_item_id
13615: AND (mmtt1.transfer_lpn_id = pick_to_lpn_rec.lpn_id
13640: l_count := 0;
13641: BEGIN
13642: SELECT COUNT(1)
13643: INTO l_count
13644: FROM mtl_material_transactions_temp mmtt
13645: WHERE mmtt.transaction_temp_id <> p_temp_id
13646: AND mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
13647: AND mmtt.operation_plan_id <> l_operation_plan_id;
13648: EXCEPTION
13783: BEGIN
13784: --{
13785: SELECT wdd.delivery_detail_id INTO l_line_rows(1)
13786: FROM wsh_delivery_details wdd
13787: , mtl_material_transactions_temp mmtt
13788: WHERE mmtt.transaction_temp_id = p_temp_id
13789: AND wdd.move_order_line_id = mmtt.move_order_line_id
13790: AND wdd.organization_id = mmtt.organization_id;
13791: --
13790: AND wdd.organization_id = mmtt.organization_id;
13791: --
13792: SELECT wdd.delivery_detail_id INTO l_line_rows(2)
13793: FROM wsh_delivery_details wdd
13794: , mtl_material_transactions_temp mmtt
13795: WHERE mmtt.transfer_lpn_id = pick_to_lpn_rec.lpn_id
13796: AND wdd.move_order_line_id = mmtt.move_order_line_id
13797: AND wdd.organization_id = mmtt.organization_id
13798: AND rownum<2;
14157: , mmtt.inventory_item_id
14158: INTO l_transaction_type_id
14159: , l_org_id
14160: , l_item_id
14161: FROM mtl_material_transactions_temp mmtt
14162: WHERE mmtt.transaction_temp_id = p_temp_id;
14163:
14164: IF inv_material_status_grp.is_status_applicable(
14165: p_wms_installed => p_wms_installed
14265: BEGIN
14266: SELECT 1
14267: INTO l_count
14268: FROM mtl_serial_numbers_temp msnt, mtl_transaction_lots_temp mtlt,
14269: mtl_material_transactions_temp mmtt
14270: WHERE (p_from_serial BETWEEN msnt.fm_serial_number AND msnt.to_serial_number
14271: OR p_to_serial BETWEEN msnt.fm_serial_number AND msnt.to_serial_number)
14272: AND mmtt.inventory_item_id = p_item_id
14273: AND mmtt.organization_id = p_organization_id
14454: l_lpn,
14455: l_ser,l_lot,
14456: l_transaction_action_id, -- Bug 4632519
14457: l_transaction_type_id -- Bug 4632519
14458: from mtl_material_Transactions_temp mmtt
14459: where mmtt.inventory_item_id = p_inventory_item_id
14460: and mmtt.organization_id = p_organization_id
14461: and mmtt.transfer_lpn_id = p_transfer_lpn_id
14462: and mmtt.content_lpn_id is null
14478: l_ser,
14479: l_lot,
14480: l_transaction_action_id, -- Bug 4632519
14481: l_transaction_type_id -- Bug 4632519
14482: from mtl_material_Transactions_temp mmtt,
14483: mtl_transaction_lots_temp mtlt
14484: where mmtt.inventory_item_id = p_inventory_item_id
14485: and mmtt.organization_id = p_organization_id
14486: and mmtt.transfer_lpn_id = p_transfer_lpn_id
15264: IF l_subs_lot_qty >=0 THEN
15265: SELECT mmtt.ITEM_PRIMARY_UOM_CODE, mmtt.TRANSACTION_UOM, mmtt.SECONDARY_UOM_CODE, mmtt.INVENTORY_ITEM_ID,
15266: DECODE(mmtt.fulfillment_base, NULL, 'P', mmtt.fulfillment_base), mmtt.organization_id
15267: INTO l_puom, l_tuom, l_suom, l_item_id, l_fulfillment_base, l_organization_id
15268: FROM mtl_material_transactions_temp mmtt
15269: WHERE transaction_temp_id = p_temp_id;
15270:
15271: mydebug ('l_puom :' || l_puom);
15272: mydebug ('l_tuom :' || l_tuom);
15646: ,mmtt.inventory_item_id
15647: ,msnt.fm_serial_number
15648: FROM mtl_transaction_lots_temp mtlt
15649: ,mtl_serial_numbers_temp msnt
15650: ,mtl_material_transactions_temp mmtt
15651: WHERE mmtt.transaction_header_id = p_transaction_header_id
15652: AND mmtt.transaction_temp_id = mtlt.transaction_temp_id
15653: AND mtlt.serial_transaction_temp_id = msnt.transaction_temp_id
15654: UNION --For lot substitution of lot and serial items when serial are confirmed we insert msnt with mmtt.transaction_temp_id
15656: SELECT mmtt.organization_id
15657: ,mmtt.inventory_item_id
15658: ,msnt.fm_serial_number
15659: FROM mtl_serial_numbers_temp msnt
15660: ,mtl_material_transactions_temp mmtt
15661: WHERE mmtt.transaction_header_id = p_transaction_header_id
15662: AND mmtt.transaction_temp_id = msnt.transaction_temp_id
15663: );
15664: mydebug (p_api_name||' Updated '||SQL%ROWCOUNT||' rows in MSN');
15666: DELETE FROM mtl_serial_numbers_temp
15667: WHERE transaction_temp_id IN ((SELECT serial_transaction_temp_id
15668: FROM mtl_transaction_lots_temp
15669: WHERE transaction_temp_id IN (SELECT transaction_temp_id
15670: FROM mtl_material_transactions_temp
15671: WHERE transaction_header_id = p_transaction_header_id))
15672: UNION
15673: (SELECT transaction_temp_id
15674: FROM mtl_material_transactions_temp
15670: FROM mtl_material_transactions_temp
15671: WHERE transaction_header_id = p_transaction_header_id))
15672: UNION
15673: (SELECT transaction_temp_id
15674: FROM mtl_material_transactions_temp
15675: WHERE transaction_header_id = p_transaction_header_id));
15676:
15677: mydebug (p_api_name||' Deleted '||SQL%ROWCOUNT||' rows from MSNT');
15678: END IF;
15678: END IF;
15679:
15680: DELETE FROM mtl_transaction_lots_temp
15681: WHERE transaction_temp_id IN (SELECT transaction_temp_id
15682: FROM mtl_material_transactions_temp
15683: WHERE transaction_header_id = p_transaction_header_id);
15684:
15685: EXCEPTION
15686: WHEN OTHERS THEN
15816: ,allocated_lpn_id
15817: INTO l_mmtt_sub
15818: , l_mmtt_loc
15819: , l_allocated_lpn_id
15820: FROM mtl_material_transactions_temp mmtt
15821: WHERE mmtt.transaction_temp_id = p_temp_id;
15822:
15823: mydebug('p_fromlpn_id: '|| p_fromlpn_id);
15824: mydebug('p_org_id: '|| p_org_id);