This discussion is archived
6 Replies Latest reply: Mar 3, 2013 4:38 PM by PS RSS

Misc receipt ..

964818 Newbie
Currently Being Moderated
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 Employee ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi,

    it's not duplicate please reply ...
  • 3. Re: Misc receipt ..
    Mahendra Journeyer
    Currently Being Moderated
    Post your query here..we can correct it if required
  • 4. Re: Misc receipt ..
    964818 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 ..
    PS Pro
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points