≡ Menu

An SQL Query for Inventorying Teamcenter Objects

Share the knowledge

As part of an upgrade/data-model conversion we have going on where I work, the application engineers from Siemens asked us if we had a way of inventorying Teamcenter so we could know how many items of each item type we have. So I came up with the following SQL query:

SQL Query for Inventorying Teamcenter

SELECT
  w.pobject_type, COUNT(*)
 
FROM
  pitem i, pworkspaceobject w
 
WHERE
  i.puid = w.puid
 
GROUP BY
  w.pobject_type
 
ORDER BY
  w.pobject_type
;

I ran this against a Teamcenter Engineering 2007 system running on Oracle. I can’t promise that it’ll work for Teamcenter 8, but I expect it will — if you try it, please let me know your results in the comments below. If you’re not on Oracle you may need to adjust the SQL a bit.

It should be easy to modify the query to inventory other types of objects, for example dataset types, by selecting from a different table besides pitem.

By the way, if you are using Oracle I recommend SQL Developer, which is a free download from Oracle.

  • Adam Chandler

    Tried it on a TC8 SQL install and it works fine ( apart from having to chaneg the table names to uppercase ):

    AllocationMap    149
    ArcWeld    1
    CAEAnalysis    19
    CAEModel    18
    DMTemplate    1
    DatumPoint    2
    Design    2
    Document    11
    ExcelTemplate    5
    GCS_CP_Plug    1
    GCS_CP_Socket    1
    Item    596
    MENCMachining    71
    ObjectTemplate    1
    SpecTemplate    2
    T4_Item    108069
    T4_Material_Data    5
    T4_Project    260
    WeldPoint    32

    • http://plmdojo.com Scott Pigman

      Good to know, thanks for the feedback!

      • Rahul

        What is the difference between Publish … , Publish To Default ODS and Publish … To Default ODS..?

        • http://plmdojo.com/ Scott Pigman

          Where are you seeing this?

  • http://profile.yahoo.com/SDNB67TEBPB6JRO5PQCC4HM4ZQ Kittu

    Hi scott I have just started to work on teamcenter . I was wondering if the following requirement is possible in teamcenter.i am usinng 8.3.
    There are some properties on my  custom data revision when we click on the field is there any way to show some description and can we validate  the data entered in the field

    • http://plmdojo.com/ Scott Pigman

      If it’s a text field you could apply a naming rule to it. Additionally, I believe you can add a pre-condition to any property’s set() operation — I’ve seen the option in the BMIDE but I’ve not implemented it myself.

      • Teamcenter Heretic

        Just tried something really simple….
        Create a naming rule (regex) that allows upper case alpha numeric, spaces and dashes.
        Add it to the name field of an Item. Good.
        Add it to the name field of an Item Revision. Bad. Flat doesn’t work!
        Why is this restriction in place? Who knows!???

        • http://plmdojo.com/ Scott Pigman

          ugh, this sort of of thing annoys the crap out of me. So it lets you attach the naming rule but it doesn’t validate your input?

          Did you file a PR? What are the chances the answer is that it’s working “as designed”?

          • Teamcenter Heretic

            Bwahhhhahah

            Won’t even let you attach it 😉

            Your recent call to the Global Technical Access Center, Incident Report (IR)
            Number 6864789 opened on 20-MAR-2013, has been researched by our GTAC
            software engineers. They have determined that this issue should be
            reviewed for possible enhancement to the current software functionality.

            The response form PD was:

            Naming rules are restricted to a few properties on a few BOs.

            The reason is that the solution for the Id validation and auto-generation is not implemented generically for all the BOs.

          • http://plmdojo.com/ Scott Pigman

            double ugh. I suppose this list of the valid properties and BOs for naming rules is kept in a ancient, booby-trapped aztec temple.

  • http://hooverm.pip.verisignlabs.com/ Teamcenter Heretic

    Here is some interesting SQL for you Oracle folks (sorry, haven’t ported to MicroshaftSql) :

    set linesize 1024;
    set colsep “~”
    set pagesize 0
    set newpage none
    set feedback off
    set termout off
    set recsep off
    set heading off
    set echo off
    set trimout on
    set trimspool on

    select  distinct
       trim(a.pitem_id),
       trim(b.pitem_revision_id),
       trim(m.pobject_type),
       trim(m.pobject_name),
       trim(m.prevision_number),
       trim(f.puid),
       trim(decode( f.pmachine_type, 1, f.pvms_path_name,
                                2, f.punix_path_name,
                                3, f.pwnt_path_name )
                   || ‘/’ || g.psd_path_name || ‘/’ || g.pfile_name )
    from
        pitem a,
        pitemrevision b,
        pdataset c,
        pimanrelation d,
        pimanvolume f,
        pimanfile g,
        previsions j,
        pdataset k,
        pom_backpointer l,
        pworkspaceobject m,
        pimantype n,
           ppom_application_object p
    where
           f.puid is not null and
        a.puid = b.ritems_tagu and
        d.rprimary_objectu = b.puid and
        d.rsecondary_objectu = c.puid and
        d.rrelation_typeu = n.puid and
        c.rrev_chain_anchoru = j.puid and
           k.puid in ( select pvalu_0 from previsions where puid = j.puid )  and
        k.puid = m.puid and
        k.puid = l.from_uid and
        l.to_uid = g.puid (+) and
        f.puid (+) = g.rvolume_tagu and
           k.puid = p.puid
    order by trim(a.pitem_id),trim(b.pitem_revision_id),trim(m.pobject_type);

    This should generate a file looking roughly similar to this:

    000001                          ~A                               ~UGMASTER                        ~000001-A                        ~   2~w8HtJ1C8GUZyNB ~/dba_4b9542d2/000001__ug__26o035438r7ql.qaf
    000001                          ~A                               ~UGMASTER                        ~000001-A                        ~   2~w8HtJ1C8GUZyNB ~/dba_4b9542d2/000001__ug__9q2035438r7qm.qaf
    000001                          ~A                               ~UGMASTER                        ~000001-A                        ~   2~w8HtJ1C8GUZyNB ~/dba_4b9542d2/000001__ugp_om0035438r7qk.prt
    000002                          ~A                               ~UGMASTER                        ~000002-A                        ~   2~w8HtJ1C8GUZyNB ~/dba_4b9542d2/000002__ug__4t5035438r7xd.qaf
    000002                          ~A                               ~UGMASTER                        ~000002-A                        ~   2~w8HtJ1C8GUZyNB ~/dba_4b9542d2/000002__ug__nap035438r7xe.qaf
    000002                          ~A                               ~UGMASTER                        ~000002-A                        ~   2~w8HtJ1C8GUZyNB ~/dba_4b9542d2/000002__ugp_20u035438r7xc.prt
    000002                          ~A                               ~UGPART                          ~000002-A-dwg1                   ~   2~w8HtJ1C8GUZyNB ~/dba_4b9542d2/000002__ug__czh035438ranh.qaf
    000002                          ~A                               ~UGPART                          ~000002-A-dwg1                   ~   2~w8HtJ1C8GUZyNB ~/dba_4b9542d2/000002__ug__hrw035438rang.qaf
    0000

    Now, if you do this before your tape backup each night you end up with a back reference from the file name to the TC object.

  • Magellan K

    how to learn  TC Database structure?It is a nightmare to me…
    TC is so  bureaucratism and poor efficiency,even a item type search I must rely on google.
    tragedy!

    • http://plmdojo.com/ Scott Pigman

      It’s an object oriented data model implemented in a relational database and that’s not trivial to implement.
      Study the class hierarchy in BMIDE’s class view. Tables follow the same hierarchy. Tables for class start with the letter P and are followed by the name of the class. Columns are prefixed with P, presumably for property, or R, presumably for reference. R’s are foreign key references, P’s are attributes of the table. Primary key for the tables is PUID (property – Unique Identifier). Inheritance is implemented by using the same PUID in every table in the type hierarchy. So a particular item will have a row in PITEM, PWORKSPACEOBJECT, PPOM_APPLICATION_OBJECT, and PPOM_OBJECT, each with the same PUID value.

      • Priti

        its really useful…

      • feifel

        really good…

  • Priti

    Hello,
    Its working on Teamcenter 8.3 also

    Thanks

  • Vinay

    Hi Scott,
    i am trying to fetch project_ids for an ItemRevision in database. From BMIDE, i have learnt that Project_ids is a runtime attribute, so is not stored in database. Now i am trying to figure out the logic and database to reach project_ids values starting from PITEMREVISION table. Could you please suggest something..

  • mohit

    Hi
    I am new in Teamcenter and I am trying to build a query in query builder for Showing me “Orphan Dataset”
    means which is not attached with any itemrevision.
    So could you plz tell me the attributes and procedure for it.’

    Thanks

  • Semen Neitour

    Hello, I am new in TeamCenter also. I have the following question. Is there available documentation about the TeamCenter database schema ? At least a list of tables used and the descriptions to each table.

  • Kiran

    Hi,
    May I know,How to delete the datasets(pdf,Word,excel) from the Teamcenter.It should be thru Database.Is it possible to write a query to get delete these objects from back end.If yes I request please provide soultion.
    TIA

Optimization WordPress Plugins & Solutions by W3 EDGE