retrieve the master created during a workflow without jumping into the SD tables : Portal

L

Lucas SPINA

started a topic 5 months ago

Hi,

We need to setup a dashboard tracking the workflow activity.

As our SA tables are purged regularly, we would like to retrieve the MD records created during the workflow.

we are thinking about using the MTA_INTEG_LOAD_ID and MTA_INTEG_BATCH_ID tables but for now we were not able to retrieve our data.

How should we proceed ?

Thanks & regards,

Lucas

Best Answer

L

Lucas SPINA said 5 months ago

Hello,

We cannot proceed that way as we will not keep any data in the SA tables.

we were thinking about this solution. can you confirm it's the good way to proceed ?

select md.* from WF_WORKFLOW_INSTANCE wfi

INNER join semarchy_repository.dbo.MTA_INTEG_LOAD il ON wfi.ID = il.WORKFLOW_INSTANCE_ID

INNER JOIN semarchy_repository.dbo.MTA_INTEG_BATCH ib on ib.R_INTEGLOAD = il.UUID

INNER JOIN md_site md ON md.b_batchid = ib.BATCHID

T

Toshish Chauhan

said 5 months ago

You can try this query .

L

Lucas SPINA

said 5 months ago

Answer

Hello,

We cannot proceed that way as we will not keep any data in the SA tables.

we were thinking about this solution. can you confirm it's the good way to proceed ?

select md.* from WF_WORKFLOW_INSTANCE wfi

INNER join semarchy_repository.dbo.MTA_INTEG_LOAD il ON wfi.ID = il.WORKFLOW_INSTANCE_ID

INNER JOIN semarchy_repository.dbo.MTA_INTEG_BATCH ib on ib.R_INTEGLOAD = il.UUID

INNER JOIN md_site md ON md.b_batchid = ib.BATCHID

L

Lucas SPINA

said 5 months ago

Hello,

This solution will not work for us as our retention policy is very short on the SA tables.

We were looking for something like this :

select md.* from WF_WORKFLOW_INSTANCE wfi

LEFT join semarchy_repository.dbo.MTA_INTEG_LOAD il ON wfi.ID = il.WORKFLOW_INSTANCE_ID

LEFT JOIN semarchy_repository.dbo.MTA_INTEG_BATCH ib on ib.R_INTEGLOAD = il.UUID

LEFT JOIN md_site md ON md.b_batchid = ib.BATCHID

could you confirm it's is a viable option to retrieve the data and if we should change something in our WHERE clause ?

Best regards

Lucas

T

Toshish Chauhan

said 5 months ago

Hello Lucas,

As you are purging Sa_table regularly. So, We can use Sa table loadid to find the respective Batch id in Md table via looking in MTA_INTEG_LOAD and MTA_INTEG_BATCH table.

Here is my query by which we are able to find the respective data in MD_table.

select * from semarchy_emp_tutorial4.md_person md where  md.b_batchid in 
(select mib.batchid from semarchy_repository9.mta_integ_batch mib
where mib.r_integload in (select mil.uuid from semarchy_repository9.mta_integ_load mil join semarchy_emp_tutorial4.sa_person sa on 
mil.loadid =sa.b_loadid))

Create query in Dashboard and Enable drill down feature while creating query in dashboard. It will help you to see detailed view of data.

Here we create a chart for the above query. Here we show total records created with respective of batch id (You can choose data to display according to your choice ).

Here is the view of chart data after drill down in dashboard.