Wip Completion (intellinum/Telnet) Query to Fetch Work Order, Lot and LPN Numbers Details

 SELECT

    we.wip_entity_name,

    wlc.organization_id,

    wlc.wip_entity_id,

    wlc.inventory_item_id,

    msik.concatenated_segments item,

    mmt.transaction_id,

    mmt.transaction_quantity,

    mmt.transaction_date,

    lpn.attribute4 lot_number,

    lpn.license_plate_number

FROM

    wip_lpn_completions       wlc,

    wip_entities              we,

    mtl_material_transactions mmt,

    mtl_transaction_lot_numbers lot,

    wms_license_plate_numbers lpn,

    mtl_system_items_kfv      msik

WHERE

        we.wip_entity_id = wlc.wip_entity_id

    AND we.organization_id = wlc.organization_id

    AND mmt.completion_transaction_id = wlc.completion_transaction_id

    AND lot.transaction_id = mmt.transaction_id

    AND lpn.lpn_id = wlc.lpn_id

    AND msik.inventory_item_id = wlc.inventory_item_id

    AND msik.organization_id = wlc.organization_id

--    AND we.wip_entity_name = '129465'--'129502'

    AND we.organization_id IN (91, 93)

    AND msik.item_type = 'FG'

ORDER BY

    mmt.transaction_date DESC;

Overview of Oracle Warehouse System (WMS)

Oracle Warehouse Management System (WMS) is advanced inventory module with lot of unique features that improves productivity of distribution centers, manufacturing or inventory handling facilities. Oracle WMS supports manual data entry, bar code readers scanning for data entry as well as Radio Frequency Identification (RFID) scanning. WMS can be enabled at Inventory organization level.

Key business processes supported by Oracle WMS:

   1. Inbound Logistics (Receiving, supplier returns, corrections)
   2. Stocking and internal inventory movements
   3. WIP Job assembly completion, component issues
   4. Outbound Logistics (pickimg, packing, shipping)
   5. Reverse logistics (customer returns)

License Plate Numbers (LPN): LPN is an object carries the location where it is residing and items that are holding. LPN need not be a physical object, it could be logical grouping. LPN simpley a group of items represented systematically. The major advantage of LPN is less number of transactions. Entire LPN can be transacted at once instead of by each line that LPN holds.
Material Status: This is one of the beautiful features of WMS, you can assign status to an item, lot or serial number. Each status is defined with a list of permissble transactions. Eg. if you have status called ‘No Sale’ and set the rules such that shipping is not allowed but all other internal inventory movements are allowed. Material status can enforce this kind of business rules.
Label printing: WMS partially supports this fucntionality. You can define label formats and fields (data) needed, you can associate this with WMS rules to print the label automatically. WMS can generate xml output, but you need a 3rd party label printing/formatting tool to actually print.
Cost groups: This is another major functionality offered by WMS module. In standard Inventory module Inventory valuation accounts are derived by subinventory setups. In WMS based on cost group rules you can have different valuations independent of subinventories.
Putaway Rules: When a matrial is received you need to receive into stock. In a major distribution center it is often complex to know where to store the material. WMS offers putaway rules that can be setup based son business criteria. Eg. If all hazarad material categy must be received into hazardous area then putaway rules can be setup.
WMS Rules engine: WMS rules engine enforces the rules defined for putaway, cost group or label printing. You can simulate the results based on set of rules defined.


Thanks
Amar Alam