1. 查询角色、用户

SELECT DISTINCT DomainName,

        u.FullName ,

        u.FirstName,

        u.InternalEMailAddress,

        r.Name,

        r.BusinessUnitIdName

FROM    SystemUser u

        JOIN systemuserroles ur ON u.SystemUserId = ur.SystemUserId

        JOIN Role r ON ur.RoleId = r.RoleId

        ORDER BY u.FullName

 

2. 审批流结点查询

        --审批模板

        SELECT  crm_default_flag ,

                crm_entity_name ,

                crm_approve_templeteId ,

                createdon ,

                crm_name ,

                crm_subject_field ,

                crm_wf_instanceid_field ,

                *

        FROM    crm_approve_templete

        WHERE   crm_approve_templeteId IN (

                '5B95D67C-A860-E411-9417-005056B01E9A',

                '5F95D67C-A860-E411-9417-005056B01E9A' )

--审批流程模板节点

        SELECT  crm_approver ,

                crm_wf_templete_nodeid ,

                crm_name ,

                a.crm_node_class ,

                b.Value ,

                crm_node_type ,

                crm_security_role '审批角色' ,

                crm_approver '审批人' ,

                CreatedOn ,

                crm_security_roleName ,

                crm_submit_busunitid ,

                *

        FROM    crm_wf_templete_node a ,

                StringMap b

        WHERE   a.crm_node_class = b.AttributeValue

                AND b.AttributeName = 'crm_node_class'

                AND crm_approve_templete_id IN (

                '5B95D67C-A860-E411-9417-005056B01E9A',

                '5F95D67C-A860-E411-9417-005056B01E9A' )

 

3. 报表

DECLARE @binUserGuid VARBINARY(128)

DECLARE @userGuid UNIQUEIDENTIFIER

SELECT @userGuid = ( SELECT TOP 1

                               SystemUserId

                     FROM      SystemUser

                     WHERE     DomainName = 'tct-hq\crmtadmin'

                   )

SET @binUserGuid = CAST(@userGuid AS VARBINARY(128))

SET context_info @binUserGuid

 

4. RC

SELECT  new_soldtoid, new_incotermid, u.SystemUserId, u.FullName FROM new_soldto s

JOIN new_rc r ON s.new_sap_code = r.new_sap_code

JOIN dbo.BusinessUnit b ON r.new_rcId = b.new_rc

JOIN dbo.SystemUser u ON b.BusinessUnitId = u.BusinessUnitId

WHERE u.SystemUserId ='424E63B8-1B16-E411-93F7-0050568F4CCE'