[Home] [Help]
1161: msn.inventory_item_id, msn.revision, msn.lot_number,
1162: msn.serial_number, to_number(null) quantity, wlpnc.uom_code uom_code, rt.transaction_type,
1163: rt.interface_transaction_id, msn.COST_GROUP_ID cg_id,
1164: rt.destination_type_code, rt.quantity rt_quantity, rt.parent_transaction_id
1165: FROM mtl_serial_numbers msn, wms_lpn_contents wlpnc, rcv_transactions rt
1166: WHERE msn.last_txn_source_name = rt.transaction_type
1167: AND msn.last_txn_source_id = rt.interface_transaction_id
1168: AND rt.group_id = p_group_id
1169: AND (((( rt.transaction_type = 'RETURN TO VENDOR' -- 3603808
1233: , rti.interface_transaction_id
1234: , rti.destination_type_code
1235: , rti.quantity rti_quantity
1236: , rti.parent_transaction_id
1237: FROM mtl_serial_numbers msn, wms_lpn_contents wlpnc, rcv_transactions_interface rti, rcv_transactions rt
1238: WHERE msn.last_txn_source_name = rti.transaction_type
1239: AND msn.last_txn_source_id = rti.interface_transaction_id
1240: AND rti.group_id = p_group_id
1241: AND rti.transaction_type = 'CORRECT'
1251: msn.inventory_item_id, msn.revision, msn.lot_number,
1252: msn.serial_number, to_number(NULL) quantity, rt.transaction_type,
1253: rt.interface_transaction_id, msn.COST_GROUP_ID cg_id,
1254: rt.destination_type_code, rt.quantity rt_quantity, rt.parent_transaction_id
1255: FROM mtl_serial_numbers msn, rcv_transactions rt, rcv_shipment_lines rsl
1256: WHERE rt.group_id = p_group_id
1257: AND ((rt.transaction_type = 'CORRECT' AND Nvl(msn.lpn_id,-1) = Nvl(rt.lpn_id,-1))
1258: OR (rt.transaction_type = 'RETURN TO RECEIVING'
1259: AND Nvl(msn.lpn_id,-1) = Nvl(rt.transfer_lpn_id,-1)))
1275: msn.inventory_item_id, msn.revision, msn.lot_number,
1276: msn.serial_number, to_number(NULL) quantity, rt.transaction_type,
1277: rt.interface_transaction_id, msn.COST_GROUP_ID cg_id,
1278: rt.destination_type_code, rt.quantity rt_quantity, rt.parent_transaction_id
1279: FROM mtl_serial_numbers msn, rcv_transactions rt, rcv_shipment_lines rsl
1280: WHERE rt.transaction_date >= (Sysdate - 1)
1281: AND rt.lpn_group_id = p_group_id
1282: AND ((rt.transaction_type = 'CORRECT' AND Nvl(msn.lpn_id,-1) = Nvl(rt.lpn_id,-1))
1283: OR (rt.transaction_type = 'RETURN TO RECEIVING'
1301: msn.inventory_item_id, msn.revision, msn.lot_number,
1302: msn.serial_number, to_number(NULL) quantity, rt.transaction_type,
1303: rt.interface_transaction_id, msn.COST_GROUP_ID cg_id,
1304: rt.destination_type_code, rt.quantity rt_quantity, rt.parent_transaction_id
1305: FROM mtl_serial_numbers msn, rcv_transactions rt, rcv_shipment_lines rsl
1306: WHERE rt.group_id = p_group_id
1307: AND ((rt.transaction_type = 'CORRECT' AND Nvl(msn.lpn_id,-1) = Nvl(rt.lpn_id,-1))
1308: OR (rt.transaction_type = 'RETURN TO RECEIVING'
1309: AND Nvl(msn.lpn_id,-1) = Nvl(rt.transfer_lpn_id,-1)))
1741: END IF;
1742:
1743: -- updating status of the sn. to 5 for -ve correction of rtv
1744: -- since this means that the sn are to be packed in receiving.
1745: UPDATE mtl_serial_numbers
1746: SET current_status = l_status
1747: , inspection_status = l_insp_status
1748: , last_txn_source_name = NULL
1749: , last_txn_source_id = NULL
1769: END IF;
1770: -- Leave the current status of the serial number same
1771: -- as the serial number continues to stay in receiving
1772: -- need to change just the inspection status appropriately.
1773: UPDATE mtl_serial_numbers
1774: SET inspection_status = l_status
1775: , last_txn_source_name = NULL
1776: , last_txn_source_id = NULL
1777: , group_mark_id = NULL
1788: AND i.transaction_type in ('RETURN TO VENDOR','RETURN TO CUSTOMER')) THEN
1789:
1790: /* Not for Accept or Reject */
1791: l_status := 4;
1792: UPDATE mtl_serial_numbers
1793: SET current_status = l_status
1794: , last_txn_source_name = NULL
1795: , last_txn_source_id = NULL
1796: , group_mark_id = NULL
1813: ELSE
1814: l_status := 5;
1815: END IF;
1816:
1817: UPDATE mtl_serial_numbers
1818: SET current_status = l_status
1819: , last_txn_source_name = NULL
1820: , last_txn_source_id = NULL
1821: , group_mark_id = NULL
1975: FOR irec IN c_neg_deliver_ser_1159 LOOP
1976: IF ((irec.transaction_type = 'CORRECT' AND irec.rt_quantity < 0)
1977: OR (irec.transaction_type = 'RETURN TO RECEIVING')) THEN
1978:
1979: UPDATE mtl_serial_numbers
1980: SET
1981: current_status = 5
1982: , current_subinventory_code = NULL
1983: , current_locator_id = NULL
2009: l_status := 7;
2010: ELSE
2011: l_status := 5;
2012: END IF;
2013: UPDATE mtl_serial_numbers
2014: SET
2015: --current_status = 5
2016: current_status = l_status
2017: , current_subinventory_code = NULL
2046: l_status := 7;
2047: ELSE
2048: l_status := 5;
2049: END IF;
2050: UPDATE mtl_serial_numbers
2051: SET
2052: --current_status = 5
2053: current_status = l_status
2054: , current_subinventory_code = NULL
2092: FETCH c_interface_txn_id INTO l_interface_txn_id;
2093: EXIT WHEN c_interface_txn_id%NOTFOUND;
2094:
2095: BEGIN
2096: DELETE FROM MTL_SERIAL_NUMBERS_TEMP
2097: WHERE TRANSACTION_TEMP_ID = l_interface_txn_id;
2098:
2099: DELETE FROM MTL_TRANSACTION_LOTS_TEMP
2100: WHERE TRANSACTION_TEMP_ID = l_interface_txn_id;
2166: -- Also go against the inventory_item_id in the MSN table
2167: -- otherwise an index is not used and a full table scan will occur.
2168: -- Only update MSN if an item ID exists on the RTI record.
2169: IF (i.item_id IS NOT NULL) THEN
2170: UPDATE mtl_serial_numbers
2171: SET last_txn_source_name = NULL,
2172: current_status = nvl(previous_status,current_status),
2173: lpn_txn_error_flag = 'Y'
2174: WHERE last_txn_source_name = i.transaction_type
2218:
2219: BEGIN
2220:
2221: SELECT '1' into v_dummy
2222: FROM mtl_serial_numbers
2223: WHERE lpn_id = p_lpn_id
2224: AND current_organization_id = p_org_id
2225: AND nvl(last_txn_source_name,'@@@') IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
2226: AND rownum <= 1;
2259:
2260: BEGIN
2261:
2262: SELECT '1' into v_dummy
2263: FROM mtl_serial_numbers
2264: WHERE lpn_id = p_lpn_id
2265: AND current_organization_id = p_org_id
2266: AND nvl(last_txn_source_name,'@@@') NOT IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
2267: AND rownum <= 1;
2386: l_position := '0020';
2387:
2388: open c_ref for SELECT msn.lot_number, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
2389: MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity, msn.COST_GROUP_ID
2390: FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP MSNT, MTL_TRANSACTION_LOTS_TEMP MTLT
2391: WHERE MTLT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
2392: AND MSNT.TRANSACTION_TEMP_ID = MTLT.SERIAL_TRANSACTION_TEMP_ID
2393: AND MSN.INVENTORY_ITEM_ID = p_item_id
2394: AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')
2426: /* Serial Controlled */
2427: l_position := '0040';
2428: open c_ref for SELECT NULL LOT_NUMBER, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
2429: MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity, msn.COST_GROUP_ID
2430: FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP msnt,
2431: wms_lpn_contents wlpnc
2432: WHERE MSNT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
2433: AND MSN.INVENTORY_ITEM_ID = p_item_id
2434: AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')
2656: IF p_lot_controlled = 2 AND p_serial_controlled in (2,5) THEN
2657: l_position := '0020';
2658: open c_ref for SELECT mtlt.lot_number, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
2659: MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity
2660: FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP MSNT, MTL_TRANSACTION_LOTS_TEMP MTLT
2661: WHERE MTLT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
2662: AND MSNT.TRANSACTION_TEMP_ID = MTLT.SERIAL_TRANSACTION_TEMP_ID
2663: AND MSN.INVENTORY_ITEM_ID = p_item_id
2664: AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')
2680: ELSIF p_lot_controlled <> 2 AND p_serial_controlled in (2,5) THEN
2681: l_position := '0040';
2682: open c_ref for SELECT NULL LOT_NUMBER, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
2683: MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity
2684: FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP MSNT
2685: WHERE MSNT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
2686: AND MSN.INVENTORY_ITEM_ID = p_item_id
2687: AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')
2688: AND MSN.SERIAL_NUMBER >= MSNT.FM_SERIAL_NUMBER
2712: ** if the txn fails. Bringing back the status is done in txn_complete
2713: ** if txn fails.
2714: */
2715: IF l_from_serial_number IS NOT NULL THEN
2716: UPDATE mtl_serial_numbers
2717: SET previous_status = current_status
2718: WHERE serial_number = l_from_serial_number
2719: AND inventory_item_id = p_item_id;
2720: END IF;
2846: print_debug('END OF LOOP PROCESS WHOLE LPN RETURN - Non Serial Contents Records');
2847: END IF;
2848:
2849: open c_ref_ser for SELECT LAST_TXN_SOURCE_ID
2850: FROM MTL_SERIAL_NUMBERS MSN
2851: WHERE MSN.LPN_ID = p_lpn_id
2852: AND MSN.LAST_TXN_SOURCE_NAME IN ('RETURN TO VENDOR',
2853: 'RETURN TO CUSTOMER',
2854: 'RETURN TO RECEIVING');
3013: 'RETURN TO RECEIVING');
3014:
3015: ELSIF p_serial_controlled = 1 THEN -- Serial, Lot control doesn't matter
3016: open c_ref for SELECT LAST_TXN_SOURCE_ID
3017: FROM MTL_SERIAL_NUMBERS MSN
3018: WHERE MSN.LPN_ID = p_lpn_id
3019: AND MSN.INVENTORY_ITEM_ID = p_item_id
3020: AND ((MSN.REVISION = p_item_revision AND p_item_revision IS NOT NULL) OR
3021: (MSN.REVISION IS NULL AND p_item_revision IS NULL))