Precisely Enterworks

 View Only

  • 1.  SQL code for determining if a product record is locked in a workflow

    Posted 04-26-2022 15:32
    Hi,
    does anyone have SQL code (that can be used in an EPX workflow) for determining if a product record is locked in a workflow already?

    I'm processing several Product records through a Mass Data Change workflow and would like to verify that none of the products are already locked in a different workflow.
    The Product records are "linked" to a MDC record which is the record that is sent to the Mass Data Change workflow.

    thanks!

    ------------------------------
    Ruthy Sleeth | Business Process Analyst
    New Pig | 814-686-2254
    ------------------------------


  • 2.  RE: SQL code for determining if a product record is locked in a workflow

    Employee
    Posted 06-17-2022 03:31
    Ruthy,

    If I understand you correctly (and in generic terms), you have a parent repository that is what is anchored to the workflow and you want to know if the child records are linked to a parent record that is in workflow?

    The "locked in workflow" flag is in the B_MASTER_REPOSITORY_ITEM table and can be accessed (for your scenario) with the following SQL.  Of course, you will need to substitute all of the generic snapshot columns with the appropriate ones for your environment:

    select p.<parentKey>, p.<parentDescriptor>, c.<childKey>, c.<childDescriptor>
    from <childRepository> c
    join <parentRepository> p on p.<parentKey> = c.<parentKey>
    join B_MASTER_REPOSITORY_ITEM mri on mri.ITEM_ID = p.InternalRecordId
    where mri.WORKFLOW_STATE = 1
    order by p.Product_ID, c.Item_ID

    Where:

    <parentkey> - primary key for parent repository
    <parentDescriptor> - one or more parent attributes to identify the parent record
    <childKey> - primary key for the child repository
    <childDescriptor> - one or more child attributes to identify the child record
    <parentRepository> - snapshot table view for the parent repository
    <childRepository> - snapshot table view for the child repository

    The above query finds the records that are locked in workflow.  Changing the WORKFLOW_STATE = 0 will find the records NOT locked in workflow.

    -Brian

    ------------------------------
    Brian Zupke | Senior Technical Support Engineer
    Winshuttle North America | 909-900-9179
    ------------------------------



  • 3.  RE: SQL code for determining if a product record is locked in a workflow

    Posted 06-17-2022 07:59
    Awesome!  this is great info.  I will try it out.
    thank you so much!!
    : )

    ------------------------------
    Ruthy Sleeth | Business Process Analyst
    New Pig | 814-686-2254
    ------------------------------

    Original Message