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 get the number of organization items per organization

Discussion in 'General' started by nestor, Sep 16, 2010.

  1. nestor

    nestor Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    Please help me in creating an sql statements than counts an organization item per organization. Kindly explain also what is organization items.
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Organization Items are items that you have defined in your Item Master and which have been assigned to a particular Inventory Organization. To think more clearly about the SQL, open the mtl_system_items table.
     
    nestor likes this.
  3. nestor

    nestor Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    sir i am using R12 and when i select from that table. It says that it doesn't exist. Please help. Thanks.
     
  4. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Try it as apps.mtl_system_items or apps.mtl_system_items_b
     
    nestor likes this.
  5. nestor

    nestor Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    Thank you sir. It works but how will i get the names of these organizations?
     
  6. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    You have probably ORG_ID/ORGANIZATION_ID in the system items table. Look up the ID of your organization from either the front end help->diagnostics->>examine or from the organization table.

    A common good method to find tables you may be looking for is to search from tab by giving some table name characters, something like this

    Code (SQL):
    SELECT * FROM tab WHERE tname LIKE '%ORGANIZATION%'
    Then you can see the tables and see whose data is useful for you.
     
  7. nestor

    nestor Active Member

    Messages:
    28
    Likes Received:
    1
    Trophy Points:
    90
    Sir, is the column inventory_item_id of table apps.mtl_system_items_b the same as item_id column of the po.po_lines_all? Please see the query below and advise if my join condition between the two mentioned tables is correct.

    SELECT COUNT(b.inventory_item_id)
    FROM apps.mtl_system_items_b b
    , po.po_lines_all l, po.po_headers_all h
    WHERE b.inventory_item_id = l.item_id
    AND h.po_header_id = l.po_header_id
    AND h.org_id = 103 --hq

    thanks....