1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how to capture issue value and receive value based on MMT table

Discussion in 'Oracle Financials' started by sharadwaty, Mar 14, 2013.

  1. sharadwaty

    sharadwaty Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    How to capture issue value and receive value based on MMT table for particular OPM organization item wise.
    Can anyone help me?
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Is it SQL/PL SQL specific question. If not move to the corresponding section.
     
  3. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi,

    Refer the below query It may give Idea for You.

    Code (SQL):

    SELECT      TRUNC (mmt.transaction_date) Doc_Date
             ,  NULL           Doc_No  
             , 'RECV' doc_type
             , msib.segment1 Doc_Item
             , mmt.subinventory_code Doc_whse
             , NVL (SUM (mmt.transaction_quantity), 0) Doc_qty
             , mmt.transaction_uom Doc_uom
             , msib.inventory_item_id item_id
        FROM mtl_material_transactions mmt,
             mtl_system_items_b msib,
             mtl_parameters mp
       WHERE mmt.inventory_item_id = msib.inventory_item_id
         AND mmt.organization_id = msib.organization_id
         AND mp.organization_id = mmt.organization_id
         AND mmt.subinventory_code =   :whse_code
         --AND msib.inventory_item_id =  :item_id
       --  AND TRUNC(mmt.transaction_date) between   :P_from_date  and  :P_to_date
         AND mmt.transaction_type_id IN (36, 18, 12, 71)   --  36 Return to Vendor , 18 PO Receipt , 12  Intransit Receipt ,71 PO Rcpt Adjust
    GROUP BY TRUNC (mmt.transaction_date),
             'RECV',
             msib.segment1,
             mmt.subinventory_code , --mp.organization_code,
             mmt.transaction_uom,
             msib.inventory_item_id  
     

    Regards
    Sambasiva Reddy
    samba.komma@gmail.com
     
  4. sharadwaty

    sharadwaty Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi Sambasiva,

    Thanks for your reply. Actually I am concern for issue cost or value(cost*qty) and receive cost or value(cost*qty). Not quantity.
    Can you please help me how to find out the cost for issuing transaction and receiving transaction for OPM organization? Any table reference you have?
     
  5. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
    Hi

    Refer the below query...
    Code (SQL):
    SELECT NVL (ROUND (SUM (NVL (ccd.cmpnt_cost, 0)), 2), 0)
            FROM cm_cmpt_dtl ccd,
                 gmf_period_statuses gps,
                 mtl_system_items_b msib
           WHERE msib.segment1 = p_item_no
             AND ccd.organization_id = p_destination_wh
             AND ccd.inventory_item_id = msib.inventory_item_id
             AND ccd.organization_id = msib.organization_id
             AND ccd.period_id = gps.period_id
             AND SYSDATE BETWEEN gps.start_date AND gps.end_date;
     
  6. sambuduk

    sambuduk Forum Advisor

    Messages:
    242
    Likes Received:
    73
    Trophy Points:
    455
    Location:
    Hyderabad , Telangana
  7. sharadwaty

    sharadwaty Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Hi Sambasiva,
    There hasn’t enough information. Discussion is incomplete.
    Actually I want to collect cost of particular ISO issue and receive transaction. For that type of transaction there is no transaction cost/actual cost/transfer price in MMT table. Where from I collect cost for this type of transactions? I have to show transaction wise cost.
    Can you help me …