You cannot select more than 25 topics Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.

193 lines
3.5 KiB
Plaintext

/****** 对象: View WF_EmpWorks 脚本日期: 03/12/2011 21:42:50 ******/;
/* WF_EmpWorks */;
CREATE VIEW WF_EmpWorks
(
PRI,
WorkID,
IsRead,
Starter,
StarterName,
WFState,
FK_Dept,
DeptName,
FK_Flow,
FlowName,
PWorkID,
PFlowNo,
FK_Node,
NodeName,
Title,
RDT,
ADT,
SDT,
FK_Emp,
FID,
FK_FlowSort,
SysType,
SDTOfNode,
PressTimes,
GuestNo,
GuestName,
BillNo,
TodoEmps,
TodoEmpsNum,
TodoSta,
TaskSta,
FlowNote,
ListType,
Sender,
AtPara,
Domain,
OrgNo,
FlowIdx,
FlowSortIdx
)
AS
SELECT A.PRI,A.WorkID,B.IsRead, A.Starter,
A.StarterName,
A.WFState,
A.FK_Dept,A.DeptName, A.FK_Flow, A.FlowName,A.PWorkID,
A.PFlowNo,
B.FK_Node, B.FK_NodeText AS NodeName, A.Title, A.RDT, B.RDT AS ADT,
B.SDT, B.FK_Emp,B.FID ,A.FK_FlowSort,A.SysType,A.SDTOfNode,B.PressTimes,
A.GuestNo,
A.GuestName,
A.BillNo,
A.TodoEmps,
A.TodoEmpsNum,
A.TodoSta,
A.TaskSta,
A.FlowNote,
0 as ListType,
A.Sender,
A.AtPara,
A.Domain,A.OrgNo,
C.Idx AS FlowIdx, D.Idx AS FlowSortIdx
FROM WF_GenerWorkFlow A, WF_GenerWorkerlist B,WF_Flow C,WF_FlowSort D
WHERE (B.IsEnable = 1) AND (B.IsPass = 0)
AND A.WorkID = B.WorkID AND A.FK_Node = B.FK_Node AND A.WFState!=0 AND WhoExeIt!=1 AND A.FK_Flow=C.No AND A.FK_FlowSort=D.No AND C.FK_FlowSort=D.No
UNION
SELECT A.PRI,A.WorkID,B.Sta AS IsRead, A.Starter,
A.StarterName,
2 AS WFState,
A.FK_Dept,A.DeptName, A.FK_Flow, A.FlowName,A.PWorkID,
A.PFlowNo,
B.FK_Node, B.NodeName, A.Title, A.RDT, B.RDT AS ADT,
B.RDT AS SDT, B.CCTo as FK_Emp,B.FID ,A.FK_FlowSort,A.SysType,A.SDTOfNode, 0 as PressTimes,
A.GuestNo,
A.GuestName,
A.BillNo,
A.TodoEmps,
A.TodoEmpsNum,
0 as TodoSta,
0 AS TaskSta,
A.FlowNote,
1 as ListType,
B.Rec as Sender,
'@IsCC=1'||A.AtPara as AtPara,
A.Domain,A.OrgNo,
C.Idx AS FlowIdx, D.Idx AS FlowSortIdx
FROM WF_GenerWorkFlow A, WF_CCList B,WF_Flow C,WF_FlowSort D WHERE A.WorkID=B.WorkID AND B.Sta <=1 AND B.InEmpWorks = 1 AND A.WFState!=0 AND A.FK_Flow=C.No AND A.FK_FlowSort=D.No AND C.FK_FlowSort=D.No;
/****** <20><><EFBFBD><EFBFBD>: View V_FlowStarterBPM <20>ű<EFBFBD><C5B1><EFBFBD><EFBFBD><EFBFBD>: 2015-04-10 ******/;
CREATE VIEW V_FlowStarterBPM (FK_Flow,FlowName,FK_Emp,OrgNo)
AS
SELECT
A.FK_Flow,
a.FlowName,
C.FK_Emp,
C.OrgNo
FROM
WF_Node a,
WF_NodeStation b,
Port_DeptEmpStation c
WHERE
a.NodePosType= 0
AND a.NodeID= b.FK_Node
AND B.FK_Station= C.FK_Station
AND ( A.DeliveryWay= 0 OR A.DeliveryWay= 14 ) UNION
SELECT
A.FK_Flow,
a.FlowName,
C.FK_Emp,
C.OrgNo
FROM
WF_Node a,
WF_NodeDept b,
Port_DeptEmp c
WHERE
a.NodePosType= 0
AND a.NodeID= b.FK_Node
AND B.FK_Dept= C.FK_Dept
AND A.DeliveryWay= 1 UNION
SELECT
A.FK_Flow,
a.FlowName,
C.UserID AS FK_Emp,
C.OrgNo
FROM
WF_Node A,
WF_NodeEmp B,
Port_Emp C
WHERE
A.NodePosType= 0
AND A.NodeID= B.FK_Node
AND A.DeliveryWay= 3
AND B.FK_Emp=C.No
UNION
SELECT
A.FK_Flow,
A.FlowName,
B.UserID AS FK_Emp,
B.OrgNo
FROM
WF_Node A,
Port_Emp B,
WF_Flow C
WHERE
A.NodePosType= 0 AND A.DeliveryWay= 4
AND A.FK_Flow= C.No
AND ((B.OrgNo = C.OrgNo) OR ((B.OrgNo IS NULL) AND (C.OrgNo IS NULL)))
UNION
SELECT
A.FK_Flow,
a.FlowName,
E.FK_Emp,
E.OrgNo
FROM
WF_Node A,
WF_NodeDept B,
WF_NodeStation C,
Port_DeptEmpStation E
WHERE
a.NodePosType= 0
AND A.NodeID= B.FK_Node
AND A.NodeID= C.FK_Node
AND B.FK_Dept= E.FK_Dept
AND C.FK_Station= E.FK_Station
AND A.DeliveryWay= 9 UNION
SELECT
A.FK_Flow,
A.FlowName,
C.UserID AS FK_Emp,
B.OrgNo
FROM
WF_Node A,
WF_FlowOrg B,
Port_Emp C
WHERE
A.FK_Flow= B.FlowNo
AND ((B.OrgNo = C.OrgNo) OR ((B.OrgNo IS NULL) AND (C.OrgNo IS NULL)))
AND (
A.DeliveryWay= 22
OR A.DeliveryWay= 51);