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

Query related to promotion request in Windchill

  • May 31, 2023
  • 2 replies
  • 1405 views

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.

 

 

Best answer by MN_9999219

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

2 replies

Marco Tosin
21-Topaz I
Marco Tosin21-Topaz I
21-Topaz I
May 31, 2023

You can start looking at this article

 

[Knowledge Hub] Windchill PLM - Workflows Business Reporting

 

 

Marco
avillanueva
23-Emerald I
avillanueva23-Emerald I
23-Emerald I
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.

    M
    MN_999921910-MarbleAuthor
    10-Marble
    May 31, 2023

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

    M
    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

      Terms & ConditionsCookie settingsAccessibility statement