Skip to main content
May 31, 2023 8:48 AM UTC
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.

 

 

Jun 6, 2023 10:50 AM UTC

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

4 replies

1-Visitor
May 31, 2023 8:55 AM UTC

You can start looking at this article

 

[Knowledge Hub] Windchill PLM - Workflows Business Reporting

 

 

May 31, 2023 11:29 AM UTC

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.

May 31, 2023 5:31 PM UTC

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

MN_9999219AuthorAnswer
Jun 6, 2023 10:50 AM UTC

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