3 Replies Latest reply on May 14, 2009 12:05 PM by Sandeep Gandhi, Consultant

    Inventory Cost Currency  And UserId

    AHS
      Hi,

      In Oracle Apps 11i I have developed a customized report for the onhand inventory in the different subinventories from the following tables:
      mtl_material_transactions                     
      MTL_SYSTEM_ITEMS_B                     
      MTL_ITEM_LOCATIONS                     
      org_organization_definitions                    
      CST_ITEM_COST_TYPE_V                         
      I have taken the item cost from the last table mentioned above.
      I was looking for the currency code of the item cost and I'm not sure what to do.
      Can someone tell me where can I get the currency code from ...

      Another question concerns the userid of the person generating (running) the report from the application(Inventory repsonsibility) .
      How can I add that to the customized report?

      Thank you ...
        • 1. Re: Inventory Cost Currency  And UserId
          Sandeep Gandhi, Consultant
          You will have to join with gl_sets_of_Books and get the currency code from there.

          And you can get the userid by using the following function fnd_global.userid
          1 person found this helpful
          • 2. Re: Inventory Cost Currency  And UserId
            AHS
            Hi,

            using fnd_global.userid gave me -1.

            I need to know how to print the user id of the application user who requested of the customized report .

            what do i need to add to the report?

            my query is as follows and i have no customized PLSQL programs in the report:

            SELECT
            i.SEGMENT1                              AS "ITEM ID"
            ,i.DESCRIPTION                         AS "ITEM DESCRIPTION"
            ,o.ORGANIZATION_CODE                    AS "ORGANIZATION"
            ,m.SUBINVENTORY_CODE                    AS "SUBINVENTORY"
                           ,round(c.ITEM_COST,2)               As ITEM_COST
                                ,s.CURRENCY_CODE
                           ,SUM(m.TRANSACTION_QUANTITY)          AS "QUANTITY"
                           ,round(((SUM(m.TRANSACTION_QUANTITY))*c.ITEM_COST),2) AS "Total COST"
            FROM
                 mtl_material_transactions                m
            ,MTL_SYSTEM_ITEMS_B           i
            ,MTL_ITEM_LOCATIONS                l
                 ,org_organization_definitions     o
                 ,CST_ITEM_COST_TYPE_V          c
                 ,GL_SETS_OF_BOOKS           s     
            WHERE
            m.ORGANIZATION_ID=i.ORGANIZATION_ID
            and s.SET_OF_BOOKS_ID=o.SET_OF_BOOKS_ID
            AND m.INVENTORY_ITEM_ID=i.INVENTORY_ITEM_ID
            AND o.ORGANIZATION_ID=m.ORGANIZATION_ID
            AND m.ORGANIZATION_ID=l.ORGANIZATION_ID(+)
            AND m.TRANSFER_LOCATOR_ID=l.INVENTORY_LOCATION_ID(+)
            AND c.INVENTORY_ITEM_ID= i.INVENTORY_ITEM_ID
            AND c.ITEM_COST > 0
            and m.SUBINVENTORY_CODE     in ('MWarehouse','MWAREHOUSE')
            AND o.ORGANIZATION_ID = NVL(:P_ORG, o.ORGANIZATION_ID)
            AND m.SUBINVENTORY_CODE = NVL(:P_SUBINV,m.SUBINVENTORY_CODE)
            AND i.SEGMENT1=NVL(:P_ITEM,i.SEGMENT1)
            AND TRUNC(m.TRANSACTION_DATE) BETWEEN NVL(:P_DATE_FROM, TRUNC(m.TRANSACTION_DATE ))
            AND NVL(:P_DATE_TO, TRUNC(m.TRANSACTION_DATE ))                                                  
            GROUP BY
            i.SEGMENT1                              
            ,i.DESCRIPTION                         
            ,o.ORGANIZATION_CODE                    
            ,m.SUBINVENTORY_CODE     
                           ,c.ITEM_COST     
                           ,s.CURRENCY_CODE                    
            having SUM(m.TRANSACTION_QUANTITY)>0
            ORDER BY m.SUBINVENTORY_CODE;


            I will appreciate any help
            • 3. Re: Inventory Cost Currency  And UserId
              Sandeep Gandhi, Consultant
              If you run this from sql and you have not initialized your environment, then
              fnd_global.user_id will give you -1.

              However, if you are logged into EBS and running this report, fnd_global.user_id will work.

              Hope this answers your question.

              Edited by: Sandeep Gandhi, Independent Consultant on May 14, 2009 8:05 AM