3 Replies Latest reply: Mar 1, 2013 7:54 AM by 964818 RSS

    Inventory

    964818
      I have a requirement in Inventory module develop a report like columns,     Quantity,     Destination Org,     Destination Subinventory,     Reason,     Reference,     Waybill,     Carrier
      am developing query but it showing duplicate records can u please tell me the query i want to display unique record



      The parameters are :Org, sub inventory,item


      i was developed like this but it showing duplicates can u please tell me exact query

      SELECT msib.segment1
      ,MOQD.TRANSACTION_QUANTITY
      ,MMT.TRANSFER_SUBINVENTORY
      ,MP.ORGANIZATION_CODE
      ,MTS.REASON_NAME
      ,MMT.TRANSACTION_REFERENCE
      ,MMT.WAYBILL_AIRBILL
      ,MMT.FREIGHT_CODE
      FROM mtl_system_items_b msib
      ,mtl_onhand_quantities_detail moqd
      ,mtl_material_transactions mmt
      ,mtl_transaction_reasons mts
      ,mtl_parameters mp
      ,mtl_secondary_inventories msi
      WHERE msib.segment1='10-40W Oil'
      AND MSIB.ORGANIZATION_ID=MOQD.ORGANIZATION_ID
      AND MSIB.INVENTORY_ITEM_ID=MOQD.INVENTORY_ITEM_ID
      AND MMT.ORGANIZATION_ID=MSIB.ORGANIZATION_ID
      AND MSIB.INVENTORY_ITEM_ID=MMT.iNVENTORY_ITEM_ID
      AND MP.ORGANIZATION_ID=MSIB.ORGANIZATION_ID
      AND MMT.REASON_ID=MTS.REASON_ID
      AND MSI.ORGANIZATION_ID=mmt.ORGANIZATION_ID

      Edited by: 961815 on Feb 27, 2013 10:45 PM

      Edited by: 961815 on Feb 28, 2013 12:10 AM
        • 1. Re: Inventory
          shreevat
          You are using mtl_onhand_quantities_detail table with joins inventory item id and organization_id. There can be multiple receipts/issues against an item, so mtl_onhand_quantities_detail would have more than 1 entry per item/org.

          Thanks
          Shree
          • 2. Re: Inventory
            PranitSaha
            Try with the modified one mentioned below.

            SELECT msib.segment1
            ,MMT.TRANSACTION_QUANTITY --MOQD.TRANSACTION_QUANTITY
            ,MMT.TRANSFER_SUBINVENTORY
            ,MP.ORGANIZATION_CODE
            ,MTS.REASON_NAME
            ,MMT.TRANSACTION_REFERENCE
            ,MMT.WAYBILL_AIRBILL
            ,MMT.FREIGHT_CODE
            FROM mtl_system_items_b msib
            ,mtl_onhand_quantities_detail moqd
            ,mtl_material_transactions mmt
            ,mtl_transaction_reasons mts
            ,mtl_parameters mp
            ,mtl_secondary_inventories msi
            WHERE msib.segment1='10-40W Oil'
            AND MSIB.ORGANIZATION_ID=MOQD.ORGANIZATION_ID
            AND MSIB.INVENTORY_ITEM_ID=MOQD.INVENTORY_ITEM_ID
            AND MMT.ORGANIZATION_ID=MSIB.ORGANIZATION_ID
            AND MSIB.INVENTORY_ITEM_ID=MMT.iNVENTORY_ITEM_ID
            AND MP.ORGANIZATION_ID=MSIB.ORGANIZATION_ID
            AND MMT.REASON_ID=MTS.REASON_ID
            AND MSI.ORGANIZATION_ID=mmt.ORGANIZATION_ID
            • 3. Re: Inventory
              964818
              Thanks


              I have one more query it's also showing duplicate record can u correct this.....it's help full for me.

              i have a requirement like miscellaneous issues report like columns.. Item     Qty     From Org     From Subinventory     Reference     Reason     Account Aliasname     SNs if Serialized


              i have developed report like below but am getting duplicate and also well as i don't where i want to take A/c alliasname ,SNs serialized...


              can u please send me total query whit out duplicates..


              SELECT msib.segment1 "Item"
              ,MOQD.PRIMARY_TRANSACTION_QUANTITY "Quantity"
              --,MOQD.TRANSACTION_QUANTITY
              ,MP.ORGANIZATION_CODE "From_Org"
              ,MSI.SOURCE_SUBINVENTORY "From_Suvinv"
              ,MMT.TRANSACTION_REFERENCE "Refernce"
              ,MTR.REASON_NAME "Reason"
              FROM mtl_system_items_b msib
              ,mtl_onhand_quantities_detail moqd
              ,mtl_parameters mp
              ,mtl_secondary_inventories msi
              ,mtl_material_transactions mmt
              ,mtl_transaction_reasons mtr
              WHERE segment1='10-40W Oil'
              AND MOQD.INVENTORY_ITEM_ID=MSIB.INVENTORY_ITEM_ID
              AND MOQD.ORGANIZATION_ID=MSIB.ORGANIZATION_ID
              AND MP.ORGANIZATION_ID=MSIB.ORGANIZATION_ID
              and MSI.ORGANIZATION_ID=MOQD.ORGANIZATION_ID
              and MMT.ORGANIZATION_ID=MSIB.ORGANIZATION_ID
              and MMT.REASON_ID=MTR.REASON_ID
              and MSIB.INVENTORY_ITEM_ID=MMT.INVENTORY_ITEM_ID