Skip to main content
MN_999921910-Marble
10-Marble
May 31, 2023
Solved

Query related to promotion request in Windchill

Hi All,

 

I'm trying to write a SQL to extract the objects linked to Promotion requests in Windchill. Any inputs or directions to useful articles is helpful.

 

 

MN_9999219Answer
10-Marble
June 6, 2023

This SQL worked to extract promotionnumber associated with EPMDOCUMENT

 

SELECT DISTINCT
        pn.name                    AS drw_pr_name,
        pn.promotionnoticenumber,
        epmm.documentnumber        AS epmdocumentnumber,
        epm.versionida2versioninfo AS epmdoc_revision,
        pn.promotiondate,
        pn.statestate              pr_state
    FROM
             promotionnotice pn
        JOIN promotiontarget   pt ON pn.ida2a2 = pt.ida3a5
        JOIN promotionseed     ps ON ps.ida3a5 = pn.ida2a2
        JOIN epmdocument       epm ON epm.ida2a2 = pt.ida3b5
        JOIN epmdocumentmaster epmm ON epmm.ida2a2 = epm.ida3masterreference
    ORDER BY
        pn.promotionnoticenumber

View in thread

4 replies

MarcoTosin
MarcoTosin21-Topaz II
21-Topaz II
May 31, 2023

You can start looking at this article

 

[Knowledge Hub] Windchill PLM - Workflows Business Reporting

 

 

Translate
Marco
avillanueva
avillanueva23-Emerald II
23-Emerald II
May 31, 2023

Are you looking to use Query Builder inside of Windchill or are you querying DB directly? Promotion Requests are stored in the PROMOTIONNOTICE table and related data in PROMOTIONSEED and PROMOTIONTARGET tables.

Translate
    MN_999921910-MarbleAuthor
    10-Marble
    May 31, 2023

    I'm querying the DB directly. Thanks for the inputs. Will try this out.

    Translate
    MN_999921910-MarbleAuthorAnswer
    10-Marble
    June 6, 2023

    This SQL worked to extract promotionnumber associated with EPMDOCUMENT

     

    SELECT DISTINCT
            pn.name                    AS drw_pr_name,
            pn.promotionnoticenumber,
            epmm.documentnumber        AS epmdocumentnumber,
            epm.versionida2versioninfo AS epmdoc_revision,
            pn.promotiondate,
            pn.statestate              pr_state
        FROM
                 promotionnotice pn
            JOIN promotiontarget   pt ON pn.ida2a2 = pt.ida3a5
            JOIN promotionseed     ps ON ps.ida3a5 = pn.ida2a2
            JOIN epmdocument       epm ON epm.ida2a2 = pt.ida3b5
            JOIN epmdocumentmaster epmm ON epmm.ida2a2 = epm.ida3masterreference
        ORDER BY
            pn.promotionnoticenumber

    Translate
      Terms & ConditionsAccessibility statement