6 Replies Latest reply: Mar 3, 2013 6:38 PM by PranitSaha RSS

    Misc receipt ..

    964818
      Can u please tell me query for this columns i want only unique records....


      Item     ,Qty     ,Subinv,     Locator,     Reference     ,Reason,     Account Alias,     SNs if Serialized.
        • 1. Re: Misc receipt ..
          Hussein Sawwan-Oracle
          961815 wrote:
          Can u please tell me query for this columns i want only unique records....


          Item     ,Qty     ,Subinv,     Locator,     Reference     ,Reason,     Account Alias,     SNs if Serialized.
          Please do not post duplicates -- Misc Issue report
          • 2. Re: Misc receipt ..
            964818
            Hi,

            it's not duplicate please reply ...
            • 3. Re: Misc receipt ..
              Mahendra
              Post your query here..we can correct it if required
              • 4. Re: Misc receipt ..
                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
                • 5. Re: Misc receipt ..
                  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
                  • 6. Re: Misc receipt ..
                    PranitSaha
                    Please try below SQL.

                    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=MMT.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