1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39
| SELECT T.id, N.name, W.procedure_id, I.name, R.name,
DECODE(TV.variable,'VAR1',TV.variable,NULL), DECODE(TV.variable,'VAR1',TV.type,NULL), DECODE(TV.variable,'VAR1',TV.value,NULL),
DECODE(TV.variable,'VAR2',TV.variable,NULL), DECODE(TV.variable,'VAR2',TV.type,NULL), DECODE(TV.variable,'VAR2',TV.value,NULL),
DECODE(TV.variable,'VAR3',TV.variable,NULL), DECODE(TV.variable,'VAR3',TV.type,NULL), DECODE(TV.variable,'VAR3',TV.value,NULL),
DECODE(TV.variable,'VAR4',TV.variable,NULL), DECODE(TV.variable,'VAR4',TV.type,NULL), DECODE(TV.variable,'VAR4',TV.value,NULL),
'NOT_USED', 0, 'NOT_USED',
DECODE(CV.variable,'VAR5',CV.variable,NULL), DECODE(CV.variable,'VAR5',CV.type,NULL), DECODE(CV.variable,'VAR5',CV.value,NULL),
DECODE(CV.variable,'VAR6',CV.variable,NULL), DECODE(CV.variable,'VAR6',CV.type,NULL), DECODE(CV.variable,'VAR6',CV.value,NULL),
'NOT_USED', 0, 'NOT_USED', 'NOT_USED', 0, 'NOT_USED',
'NOT_USED', 0, 'NOT_USED'
FROM node N,
Actor I,
CATEGORY R,
( SELECT tcid, variable, value, type FROM case_variable WHERE variable IN ( 'VAR1','VAR2' ,'VAR3','VAR4' ) ) TV,
( SELECT tcid, variable, type, value FROM case_variable WHERE variable IN ( 'VAR5' , 'VAR6' ) ) CV,
workcase W,
task T
WHERE ( T.actor_id = 2863
OR ( ( T.role_id IN ( SELECT actor_role.role_id FROM actor_role WHERE actor_role.actor_id = 2863
UNION
SELECT role_hierarchy.sub_role_id FROM role_hierarchy, CATEGORY, actor_role
WHERE role_hierarchy.sub_role_id = CATEGORY .id
AND CATEGORY.name LIKE 'role%'
AND role_hierarchy.upper_role_id = actor_role.role_id
AND actor_role.actor_id = 2863
)
)
AND actor_id IS NULL
)
)
AND T.state < 7
AND T.proc_name LIKE 'proc%'
AND TV.tcid ( + ) = T.id
AND CV.tcid ( + ) = T.workcase_id
AND N.id = T.node_id
AND W.id = T.workcase_id
AND I.id = W.initiator_id
AND R.id = W.resp_role_id
ORDER BY T.priority DESC, T.creation_date ASC |
Partager