eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.WMS_RETURN_SV dependencies on MTL_SERIAL_NUMBERS

Line 1184: FROM mtl_serial_numbers msn, wms_lpn_contents wlpnc, rcv_transactions rt

1180: msn.inventory_item_id, msn.revision, msn.lot_number,
1181: msn.serial_number, to_number(null) quantity, wlpnc.uom_code uom_code, rt.transaction_type,
1182: rt.interface_transaction_id, msn.COST_GROUP_ID cg_id,
1183: rt.destination_type_code, rt.quantity rt_quantity, rt.parent_transaction_id
1184: FROM mtl_serial_numbers msn, wms_lpn_contents wlpnc, rcv_transactions rt
1185: WHERE msn.last_txn_source_name = rt.transaction_type
1186: AND msn.last_txn_source_id = rt.interface_transaction_id
1187: AND rt.group_id = p_group_id
1188: AND (((( rt.transaction_type = 'RETURN TO VENDOR' -- 3603808

Line 1256: FROM mtl_serial_numbers msn, wms_lpn_contents wlpnc, rcv_transactions_interface rti, rcv_transactions rt

1252: , rti.interface_transaction_id
1253: , rti.destination_type_code
1254: , rti.quantity rti_quantity
1255: , rti.parent_transaction_id
1256: FROM mtl_serial_numbers msn, wms_lpn_contents wlpnc, rcv_transactions_interface rti, rcv_transactions rt
1257: WHERE msn.last_txn_source_name = rti.transaction_type
1258: AND msn.last_txn_source_id = rti.interface_transaction_id
1259: AND rti.group_id = p_group_id
1260: AND rti.transaction_type = 'CORRECT'

Line 1274: FROM mtl_serial_numbers msn, rcv_transactions rt, rcv_shipment_lines rsl

1270: msn.inventory_item_id, msn.revision, msn.lot_number,
1271: msn.serial_number, to_number(NULL) quantity, rt.transaction_type,
1272: rt.interface_transaction_id, msn.COST_GROUP_ID cg_id,
1273: rt.destination_type_code, rt.quantity rt_quantity, rt.parent_transaction_id
1274: FROM mtl_serial_numbers msn, rcv_transactions rt, rcv_shipment_lines rsl
1275: WHERE rt.group_id = p_group_id
1276: AND ((rt.transaction_type = 'CORRECT' AND Nvl(msn.lpn_id,-1) = Nvl(rt.lpn_id,-1))
1277: OR (rt.transaction_type = 'RETURN TO RECEIVING'
1278: AND Nvl(msn.lpn_id,-1) = Nvl(rt.transfer_lpn_id,-1)))

Line 1298: FROM mtl_serial_numbers msn, rcv_transactions rt, rcv_shipment_lines rsl

1294: msn.inventory_item_id, msn.revision, msn.lot_number,
1295: msn.serial_number, to_number(NULL) quantity, rt.transaction_type,
1296: rt.interface_transaction_id, msn.COST_GROUP_ID cg_id,
1297: rt.destination_type_code, rt.quantity rt_quantity, rt.parent_transaction_id
1298: FROM mtl_serial_numbers msn, rcv_transactions rt, rcv_shipment_lines rsl
1299: WHERE rt.transaction_date >= (Sysdate - 1)
1300: AND rt.lpn_group_id = p_group_id
1301: AND ((rt.transaction_type = 'CORRECT' AND Nvl(msn.lpn_id,-1) = Nvl(rt.lpn_id,-1))
1302: OR (rt.transaction_type = 'RETURN TO RECEIVING'

Line 1324: FROM mtl_serial_numbers msn, rcv_transactions rt, rcv_shipment_lines rsl

1320: msn.inventory_item_id, msn.revision, msn.lot_number,
1321: msn.serial_number, to_number(NULL) quantity, rt.transaction_type,
1322: rt.interface_transaction_id, msn.COST_GROUP_ID cg_id,
1323: rt.destination_type_code, rt.quantity rt_quantity, rt.parent_transaction_id
1324: FROM mtl_serial_numbers msn, rcv_transactions rt, rcv_shipment_lines rsl
1325: WHERE rt.group_id = p_group_id
1326: AND ((rt.transaction_type = 'CORRECT' AND Nvl(msn.lpn_id,-1) = Nvl(rt.lpn_id,-1))
1327: OR (rt.transaction_type = 'RETURN TO RECEIVING'
1328: AND Nvl(msn.lpn_id,-1) = Nvl(rt.transfer_lpn_id,-1)))

Line 1764: UPDATE mtl_serial_numbers

1760: END IF;
1761:
1762: -- updating status of the sn. to 5 for -ve correction of rtv
1763: -- since this means that the sn are to be packed in receiving.
1764: UPDATE mtl_serial_numbers
1765: SET current_status = l_status
1766: , inspection_status = l_insp_status
1767: , last_txn_source_name = NULL
1768: , last_txn_source_id = NULL

Line 1792: UPDATE mtl_serial_numbers

1788: END IF;
1789: -- Leave the current status of the serial number same
1790: -- as the serial number continues to stay in receiving
1791: -- need to change just the inspection status appropriately.
1792: UPDATE mtl_serial_numbers
1793: SET inspection_status = l_status
1794: , last_txn_source_name = NULL
1795: , last_txn_source_id = NULL
1796: , group_mark_id = NULL

Line 1811: UPDATE mtl_serial_numbers

1807: AND i.transaction_type in ('RETURN TO VENDOR','RETURN TO CUSTOMER')) THEN
1808:
1809: /* Not for Accept or Reject */
1810: l_status := 4;
1811: UPDATE mtl_serial_numbers
1812: SET current_status = l_status
1813: , last_txn_source_name = NULL
1814: , last_txn_source_id = NULL
1815: , group_mark_id = NULL

Line 1836: UPDATE mtl_serial_numbers

1832: ELSE
1833: l_status := 5;
1834: END IF;
1835:
1836: UPDATE mtl_serial_numbers
1837: SET current_status = l_status
1838: , last_txn_source_name = NULL
1839: , last_txn_source_id = NULL
1840: , group_mark_id = NULL

Line 1998: UPDATE mtl_serial_numbers

1994: FOR irec IN c_neg_deliver_ser_1159 LOOP
1995: IF ((irec.transaction_type = 'CORRECT' AND irec.rt_quantity < 0)
1996: OR (irec.transaction_type = 'RETURN TO RECEIVING')) THEN
1997:
1998: UPDATE mtl_serial_numbers
1999: SET
2000: current_status = 5
2001: , current_subinventory_code = NULL
2002: , current_locator_id = NULL

Line 2032: UPDATE mtl_serial_numbers

2028: l_status := 7;
2029: ELSE
2030: l_status := 5;
2031: END IF;
2032: UPDATE mtl_serial_numbers
2033: SET
2034: --current_status = 5
2035: current_status = l_status
2036: , current_subinventory_code = NULL

Line 2069: UPDATE mtl_serial_numbers

2065: l_status := 7;
2066: ELSE
2067: l_status := 5;
2068: END IF;
2069: UPDATE mtl_serial_numbers
2070: SET
2071: --current_status = 5
2072: current_status = l_status
2073: , current_subinventory_code = NULL

Line 2115: DELETE FROM MTL_SERIAL_NUMBERS_TEMP

2111: FETCH c_interface_txn_id INTO l_interface_txn_id;
2112: EXIT WHEN c_interface_txn_id%NOTFOUND;
2113:
2114: BEGIN
2115: DELETE FROM MTL_SERIAL_NUMBERS_TEMP
2116: WHERE TRANSACTION_TEMP_ID = l_interface_txn_id;
2117:
2118: DELETE FROM MTL_TRANSACTION_LOTS_TEMP
2119: WHERE TRANSACTION_TEMP_ID = l_interface_txn_id;

Line 2189: UPDATE mtl_serial_numbers

2185: -- Also go against the inventory_item_id in the MSN table
2186: -- otherwise an index is not used and a full table scan will occur.
2187: -- Only update MSN if an item ID exists on the RTI record.
2188: IF (i.item_id IS NOT NULL) THEN
2189: UPDATE mtl_serial_numbers
2190: SET last_txn_source_name = NULL,
2191: current_status = nvl(previous_status,current_status),
2192: lpn_txn_error_flag = 'Y'
2193: WHERE last_txn_source_name = i.transaction_type

Line 2272: FROM mtl_serial_numbers

2268:
2269: BEGIN
2270:
2271: SELECT '1' into v_dummy
2272: FROM mtl_serial_numbers
2273: WHERE lpn_id = p_lpn_id
2274: AND current_organization_id = p_org_id
2275: AND nvl(last_txn_source_name,'@@@') IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
2276: AND rownum <= 1;

Line 2313: FROM mtl_serial_numbers

2309:
2310: BEGIN
2311:
2312: SELECT '1' into v_dummy
2313: FROM mtl_serial_numbers
2314: WHERE lpn_id = p_lpn_id
2315: AND current_organization_id = p_org_id
2316: AND nvl(last_txn_source_name,'@@@') NOT IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
2317: AND rownum <= 1;

Line 2443: FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP MSNT, MTL_TRANSACTION_LOTS_TEMP MTLT

2439: l_position := '0020';
2440:
2441: open c_ref for SELECT msn.lot_number, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
2442: MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity, msn.COST_GROUP_ID, null sec_qty--13399743
2443: FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP MSNT, MTL_TRANSACTION_LOTS_TEMP MTLT
2444: WHERE MTLT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
2445: AND MSNT.TRANSACTION_TEMP_ID = MTLT.SERIAL_TRANSACTION_TEMP_ID
2446: AND MSN.INVENTORY_ITEM_ID = p_item_id
2447: AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')

Line 2456: FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_INTERFACE

2452: UNION --RTV Change 16197273
2453:
2454: SELECT msn.lot_number, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
2455: MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity, msn.COST_GROUP_ID, null sec_qty--13399743
2456: FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_INTERFACE
2457: MSNI, mtl_transaction_lots_interface MTLI
2458: WHERE MTLI.PRODUCT_TRANSACTION_ID = p_rcv_trx_interface_id
2459: AND MSNI.PRODUCT_TRANSACTION_ID = p_rcv_trx_interface_id
2460: AND MSN.INVENTORY_ITEM_ID = p_item_id

Line 2509: FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP msnt,

2505: /* Serial Controlled */
2506: l_position := '0040';
2507: open c_ref for SELECT NULL LOT_NUMBER, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
2508: MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity, msn.COST_GROUP_ID, null sec_qty --13399743
2509: FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP msnt,
2510: wms_lpn_contents wlpnc
2511: WHERE MSNT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
2512: AND MSN.INVENTORY_ITEM_ID = p_item_id
2513: AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')

Line 2526: FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_INTERFACE MSNI,

2522: UNION --RTV Change 16197273
2523:
2524: SELECT NULL LOT_NUMBER, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
2525: MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity, msn.COST_GROUP_ID, null sec_qty --13399743
2526: FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_INTERFACE MSNI,
2527: wms_lpn_contents wlpnc
2528: WHERE MSNI.PRODUCT_TRANSACTION_ID = p_rcv_trx_interface_id
2529: AND MSN.INVENTORY_ITEM_ID = p_item_id
2530: AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')

Line 3221: FROM mtl_serial_numbers

3217: AND ORGANIZATION_ID = p_organization_id ;
3218: CURSOR c_msnt(p_lpn_id NUMBER,p_organization_id NUMBER,p_item_id NUMBER )
3219: IS
3220: SELECT serial_number
3221: FROM mtl_serial_numbers
3222: WHERE lpn_id = p_lpn_id
3223: AND inventory_item_id = p_item_id
3224: AND CURRENT_ORGANIZATION_ID = p_organization_id ;
3225: l_mtlt_rec c_mtlt%ROWTYPE;

Line 3572: FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP MSNT, MTL_TRANSACTION_LOTS_TEMP MTLT

3568: IF p_lot_controlled = 2 AND p_serial_controlled in (2,5) THEN
3569: l_position := '0020';
3570: open c_ref for SELECT mtlt.lot_number, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
3571: MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity
3572: FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP MSNT, MTL_TRANSACTION_LOTS_TEMP MTLT
3573: WHERE MTLT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
3574: AND MSNT.TRANSACTION_TEMP_ID = MTLT.SERIAL_TRANSACTION_TEMP_ID
3575: AND MSN.INVENTORY_ITEM_ID = p_item_id
3576: AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')

Line 3596: FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP MSNT

3592: ELSIF p_lot_controlled <> 2 AND p_serial_controlled in (2,5) THEN
3593: l_position := '0040';
3594: open c_ref for SELECT NULL LOT_NUMBER, MSN.SERIAL_NUMBER FM_SERIAL_NUMBER,
3595: MSN.SERIAL_NUMBER TO_SERIAL_NUMBER, null quantity
3596: FROM MTL_SERIAL_NUMBERS MSN, MTL_SERIAL_NUMBERS_TEMP MSNT
3597: WHERE MSNT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
3598: AND MSN.INVENTORY_ITEM_ID = p_item_id
3599: AND Nvl(MSN.revision,'@@@') = Nvl(p_item_revision,'@@@')
3600: AND MSN.SERIAL_NUMBER >= MSNT.FM_SERIAL_NUMBER

Line 3628: UPDATE mtl_serial_numbers

3624: ** if the txn fails. Bringing back the status is done in txn_complete
3625: ** if txn fails.
3626: */
3627: IF l_from_serial_number IS NOT NULL THEN
3628: UPDATE mtl_serial_numbers
3629: SET previous_status = current_status
3630: WHERE serial_number = l_from_serial_number
3631: AND inventory_item_id = p_item_id;
3632: END IF;

Line 3762: FROM MTL_SERIAL_NUMBERS MSN

3758: print_debug('END OF LOOP PROCESS WHOLE LPN RETURN - Non Serial Contents Records');
3759: END IF;
3760:
3761: open c_ref_ser for SELECT LAST_TXN_SOURCE_ID
3762: FROM MTL_SERIAL_NUMBERS MSN
3763: WHERE MSN.LPN_ID = p_lpn_id
3764: AND MSN.LAST_TXN_SOURCE_NAME IN ('RETURN TO VENDOR',
3765: 'RETURN TO CUSTOMER',
3766: 'RETURN TO RECEIVING');

Line 3929: FROM MTL_SERIAL_NUMBERS MSN

3925: 'RETURN TO RECEIVING');
3926:
3927: ELSIF p_serial_controlled = 1 THEN -- Serial, Lot control doesn't matter
3928: open c_ref for SELECT LAST_TXN_SOURCE_ID
3929: FROM MTL_SERIAL_NUMBERS MSN
3930: WHERE MSN.LPN_ID = p_lpn_id
3931: AND MSN.INVENTORY_ITEM_ID = p_item_id
3932: AND ((MSN.REVISION = p_item_revision AND p_item_revision IS NOT NULL) OR
3933: (MSN.REVISION IS NULL AND p_item_revision IS NULL))