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
------------------------------
Original Message:
Sent: 04-26-2022 15:31
From: Ruthy Sleeth
Subject: SQL code for determining if a product record is locked in a workflow
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
------------------------------