Wednesday, 30 July 2014

Query to List Active Serial Number for an Items

Below query can be used to display active serial number for Item, Organization combination

/* Formatted on 7/30/2014 6:55:59 PM (QP5 v5.115.810.9015) */
SELECT msi.segment1 item_number,
       msn.serial_number,
       msn.current_status_name,
       msn.status_code,
       msn.current_subinventory_code,
       ml.concatenated_segments
FROM mtl_serial_numbers_all_v msn,
     mtl_system_items_b msi,
     mtl_item_locations_kfv ml,
     mtl_parameters mp
WHERE     msi.inventory_item_id = msn.inventory_item_id
      AND mp.organization_code = msn.organization_code
      AND ml.inventory_location_id = msn.current_locator_id
      AND msn.current_status = 3
      AND mp.organization_id = msi.organization_id
      AND mp.organization_code = :org_code
      AND msi.segment1 = :item

1 comment:

  1. Regards
    Sridevi Koduru (Senior Oracle Apps Trainer Oracleappstechnical.com)
    LinkedIn profile - https://in.linkedin.com/in/sridevi-koduru-9b876a8b
    Please Contact for One to One Online Training on Oracle Apps Technical, Financials, SCM, Oracle Manufacturing, BI Publisher, OAF, ADF, SQL, PL/SQL, D2K at sridevikoduru@oracleappstechnical.com | +91 - 9581017828.

    ReplyDelete