Set ansi_nulls on
Set quoted_identifier on
Go
Alter proc [DBO]. [usp_srv_getallrelativeproject]
@ Serviceorderid varchar (50)
As
/*
Page: Get the list information associated with the service ticket by the service ticket ID
Action: Service Order Processing page
Createdby: XXX
Createddate: 20101021
Modifiedhistory:
Test scripts:
Exec usp_srv_getallrelativeproject 'srv000000014'
*/
Begin
Select D. [type],
D. typeid,
D. [DESC],
D. variationorderid,
Case
When D. workorderstatus is null then D. srvorderstatus
Else D. workorderstatus
End as status,
Case
When D. workorderhname is null then D. srvorderhname
Else D. workorderhname
End as hname
From (
Select R. [type],
R. typeid,
R. variationorderid,
R. [DESC],
A. dictname as srvorderstatus,
Hand. hname as srvorderhname,
Case Wo. Status
When 1 then 'processing'
When 2 then 'complete'
End as workorderstatus,
Wo. handlername as workorderhname
From DBO. tbl_variationorderrelativeproject R
Inner join tbl_serviceorder s
On R. variationorderid = @ serviceorderid
And S. serviceorderid = @ serviceorderid
Left join (
Select * From DBO. tbl_dictionary
Where dicttype = 'serviceorderstatus'
)
On S. statuscode = A. dictcode
Left join tbl_servicehandlerinfo hand
On R. variationorderid = hand. serviceorderid
Left join DBO. tbl_serviceworkorder wo
On R. typeid = Wo. serviceworkorderid
) D
End