[Home] [Help]
1146: , wlpnc.COST_GROUP_ID cg_id
1147: , rt.destination_type_code
1148: , rt.quantity rt_quantity
1149: , rt.parent_transaction_id
1150: FROM wms_license_plate_numbers wlpn, wms_lpn_contents wlpnc, rcv_transactions rt
1151: WHERE rt.group_id = p_group_id
1152: AND (((( rt.transaction_type = 'RETURN TO VENDOR'
1153: AND rt.lpn_id IS NOT NULL -- 3603808
1154: )
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
1228: , rti.interface_transaction_id
1229: , rti.destination_type_code
1230: , rti.quantity rti_quantity
1231: , rti.parent_transaction_id
1232: FROM wms_lpn_contents wlpnc, rcv_transactions_interface rti, rcv_transactions rt
1233: WHERE rti.group_id = p_group_id
1234: AND rti.transaction_type = 'CORRECT'
1235: AND rt.transaction_id = rti.parent_transaction_id
1236: AND ((rt.transaction_type in ('RETURN TO VENDOR','RETURN TO CUSTOMER') AND rti.quantity < 0) OR
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'
2175: */
2176:
2177: FOR i IN c_failure LOOP
2178:
2179: UPDATE wms_lpn_contents
2180: SET source_name = NULL
2181: WHERE source_name = i.transaction_type
2182: AND source_header_id = i.interface_transaction_id;
2183:
2237:
2238: BEGIN
2239:
2240: SELECT '1' INTO v_dummy FROM
2241: rcv_transactions_interface rti,wms_lpn_contents wlc
2242: WHERE rti.interface_transaction_id = wlc.source_header_id
2243: AND rti.processing_status_code = 'WSH_INTERFACED'
2244: AND rti.to_organization_id = wlc.organization_id
2245: AND wlc.parent_lpn_id = p_lpn_id
2286:
2287: BEGIN
2288:
2289: SELECT '1' INTO v_dummy
2290: FROM wms_lpn_contents
2291: WHERE nvl(serial_summary_entry,2) <> 1
2292: AND parent_lpn_id = p_lpn_id
2293: AND organization_id = p_org_id
2294: AND nvl(source_name,'@@@') IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
2328:
2329: BEGIN
2330:
2331: SELECT '1' INTO v_dummy
2332: FROM wms_lpn_contents
2333: WHERE nvl(serial_summary_entry,2) <> 1
2334: AND parent_lpn_id = p_lpn_id
2335: AND ORGANIZATION_ID = p_org_id
2336: AND nvl(source_name,'@@@') NOT IN ('RETURN TO VENDOR', 'RETURN TO RECEIVING', 'RETURN TO CUSTOMER')
2474: /* Lot Controlled */
2475: l_position := '0030';
2476:
2477: /* For a given LPN, Lot and Item combination there could be more
2478: ** than one record in wms_lpn_contents, but we need
2479: ** only one record per LPN, Lot and Item combination as an output of this cursor
2480: */
2481:
2482: open c_ref for SELECT DISTINCT MTLT.LOT_NUMBER, NULL FM_SERIAL_NUMBER,
2480: */
2481:
2482: open c_ref for SELECT DISTINCT MTLT.LOT_NUMBER, NULL FM_SERIAL_NUMBER,
2483: NULL TO_SERIAL_NUMBER, MTLT.transaction_quantity quantity,wlpnc.cost_group_id, MTLT.secondary_quantity sec_qty--13399743
2484: FROM WMS_LPN_CONTENTS WLPNC, MTL_TRANSACTION_LOTS_TEMP MTLT
2485: WHERE MTLT.TRANSACTION_TEMP_ID = p_rcv_trx_interface_id
2486: AND WLPNC.LOT_NUMBER = MTLT.LOT_NUMBER
2487: AND WLPNC.PARENT_LPN_ID = p_lpn_id
2488: AND WLPNC.INVENTORY_ITEM_ID = P_ITEM_ID
2491: UNION -- RTV Change 16197273
2492:
2493: SELECT DISTINCT MTLI.LOT_NUMBER, NULL FM_SERIAL_NUMBER,
2494: NULL TO_SERIAL_NUMBER, MTLI.transaction_quantity quantity,wlpnc.cost_group_id, MTLI.SECONDARY_TRANSACTION_QUANTITY sec_qty--13399743
2495: FROM WMS_LPN_CONTENTS WLPNC, mtl_transaction_lots_interface MTLI
2496: WHERE MTLI.PRODUCT_TRANSACTION_ID = p_rcv_trx_interface_id
2497: AND WLPNC.LOT_NUMBER = MTLI.LOT_NUMBER
2498: AND WLPNC.PARENT_LPN_ID = p_lpn_id
2499: AND WLPNC.INVENTORY_ITEM_ID = P_ITEM_ID
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,'@@@')
2514: AND MSN.SERIAL_NUMBER >= MSNT.FM_SERIAL_NUMBER
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,'@@@')
2531: AND MSN.SERIAL_NUMBER >= MSNI.FM_SERIAL_NUMBER
2541: ELSE
2542:
2543: l_position := '0050';
2544: open c_ref for SELECT NULL , NULL , NULL , rti.quantity, wlpnc.cost_group_id, RTI.secondary_quantity sec_qty--13399743
2545: FROM WMS_LPN_CONTENTS WLPNC, RCV_TRANSACTIONS_INTERFACE RTI
2546: WHERE RTI.INTERFACE_TRANSACTION_ID = p_rcv_trx_interface_id
2547: AND WLPNC.PARENT_LPN_ID = p_lpn_id
2548: AND WLPNC.INVENTORY_ITEM_ID = RTI.ITEM_ID
2549: AND nvl(WLPNC.SOURCE_NAME,'@@@') not in ('RETURN TO RECEIVING','RETURN TO VENDOR', 'RETURN TO CUSTOMER')
2549: AND nvl(WLPNC.SOURCE_NAME,'@@@') not in ('RETURN TO RECEIVING','RETURN TO VENDOR', 'RETURN TO CUSTOMER')
2550: AND rownum <= 1;
2551:
2552: /* For a given LPN and Item combination there could be more
2553: ** than one record in wms_lpn_contents, but we need
2554: ** only one record as an output of this cursor
2555: */
2556: l_uom := p_uom;
2557: END IF;
2688: END MARK_RETURNS;
2689:
2690: /*
2691: --16197273
2692: --Description:API to unmark the wms_lpn_contents table at the time of processing new rti/mti.
2693: --This api will be called from RTV specific package :RCVWSHIB.pls
2694: */
2695:
2696: PROCEDURE unmark_returns (
2713:
2714: CURSOR c_unmark_wlc
2715: IS
2716: SELECT LPN_CONTENT_ID
2717: FROM wms_lpn_contents
2718: WHERE parent_lpn_id = p_lpn_id
2719: AND inventory_item_id = p_item_id
2720: AND organization_id = p_org_id
2721: AND NVL(lot_number, '@@@') = NVL(p_lot_number,'@@@')
2748:
2749: FOR i IN 1..l_lpn_content_id.COUNT LOOP
2750:
2751:
2752: UPDATE wms_lpn_contents
2753: SET source_header_id = NULL,
2754: source_name = NULL
2755: WHERE LPN_CONTENT_ID = l_lpn_content_id(i).LPN_CONTENT_ID ;
2756:
3094: print_debug('Master LPN is shipped.' );
3095: END IF;
3096:
3097:
3098: SELECT DISTINCT parent_lpn_id INTO l_lpn_id FROM wms_lpn_contents WHERE
3099: source_header_id = p_rcv_trx_interface_id
3100: AND source_name = 'RETURN TO VENDOR'
3101: AND ROWNUM < 2 ;
3102:
3201: wlc.PRIMARY_QUANTITY primary_qty ,
3202: wlc.SECONDARY_QUANTITY sec_qty ,
3203: wlc.SECONDARY_UOM_CODE sec_uom ,
3204: wlc.SERIAL_SUMMARY_ENTRY serial_control
3205: FROM wms_lpn_contents wlc,
3206: wms_license_plate_numbers wlpn
3207: WHERE wlc.parent_lpn_id = wlpn.lpn_id
3208: AND wlpn.lpn_id = p_lpn_id
3209: AND wlc.source_header_id IS NULL ;
3407: GROUP BY transfer_lpn_id;
3408: CURSOR get_unpack_lpn(p_lpn_id NUMBER, p_rti_qty NUMBER)
3409: IS
3410: SELECT parent_lpn_id
3411: FROM wms_lpn_contents
3412: WHERE parent_lpn_id = p_lpn_id
3413: --AND source_header_is is null
3414: GROUP BY parent_lpn_id
3415: HAVING SUM(quantity) <> p_rti_qty;
3730:
3731: x_return_status := fnd_api.g_ret_sts_success;
3732:
3733: open c_ref for SELECT SOURCE_HEADER_ID
3734: FROM WMS_LPN_CONTENTS WLPNC
3735: WHERE WLPNC.ORGANIZATION_ID = p_org_id
3736: AND WLPNC.PARENT_LPN_ID = p_lpn_id
3737: AND NVL(SERIAL_SUMMARY_ENTRY,2) <> 1 -- Non Serial Contents Records(value=2)
3738: AND WLPNC.SOURCE_NAME IN ('RETURN TO VENDOR',
3913: END IF;
3914:
3915: IF p_lot_controlled = 2 AND p_serial_controlled = 2 THEN -- NonSerial and NonLot
3916: open c_ref for SELECT SOURCE_HEADER_ID
3917: FROM WMS_LPN_CONTENTS WLPNC
3918: WHERE WLPNC.ORGANIZATION_ID = p_org_id
3919: AND WLPNC.PARENT_LPN_ID = p_lpn_id
3920: AND WLPNC.INVENTORY_ITEM_ID = p_item_id
3921: AND ((WLPNC.revision = p_item_revision AND p_item_revision IS NOT NULL) OR
3937: AND MSN.SERIAL_NUMBER = p_serial_code;
3938:
3939: ELSIF p_lot_controlled = 1 AND p_serial_controlled = 2 THEN -- Lot Only
3940: open c_ref for SELECT SOURCE_HEADER_ID
3941: FROM WMS_LPN_CONTENTS WLPNC
3942: WHERE WLPNC.ORGANIZATION_ID = p_org_id
3943: AND WLPNC.PARENT_LPN_ID = p_lpn_id
3944: AND WLPNC.INVENTORY_ITEM_ID = p_item_id
3945: AND ((WLPNC.REVISION = p_item_revision AND p_item_revision IS NOT NULL) OR
4234:
4235: -- BEGIN
4236: -- SELECT wlc.lot_number
4237: -- INTO l_lot_number
4238: --FROM wms_lpn_contents wlc,
4239: -- mtl_system_items msi
4240: --WHERE wlc.parent_lpn_id = p_lpn_id
4241: --AND wlc.organization_id = p_org_id
4242: --AND wlc.inventory_item_id = p_item_id