Guest User

Untitled

a guest
Sep 25th, 2014
249
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. ALTER PROCEDURE [dbo].[Widget_PreApprovalForms_Search]
  2.     @FormStatusId INT = NULL ,
  3.     @CreatedBy INT = NULL ,
  4.     @FormEmployeeId INT = NULL ,
  5.     @ApproverId INT = NULL ,
  6.     @RegionId VARCHAR(50) = NULL
  7. AS
  8.     DECLARE @localFormStatusId INT
  9.     SET @localFormStatusId = @FormStatusId
  10.     DECLARE @localCreatedBy INT
  11.     SET @localCreatedBy = @CreatedBy
  12.     DECLARE @localFormEmployeeId INT
  13.     SET @localFormEmployeeId = @FormEmployeeId
  14.     DECLARE @localApproverId INT
  15.     SET @localApproverId = @ApproverId
  16.     DECLARE @localRegionId VARCHAR(50)
  17.     SET @localRegionId = @RegionId
  18.  
  19.  
  20.     SELECT DISTINCT
  21.             DT.* ,
  22.             ISNULL(NULLIF(CAST(DT.FormRegionCode AS VARCHAR(50)), ''),
  23.                    Region.short_name) AS Region
  24.     FROM    ( SELECT    FBD.FormId ,
  25.                         FBD.AlternateId ,
  26.                         FBD.VerificationValue ,
  27.                         FBD.TypeId AS FormTypeId ,
  28.                         FBD.FormTypeDescription AS Description ,
  29.                         FBD.DashboardIcon ,
  30.                         FBD.CreatedByName AS CreatedByUser ,
  31.                         CAST(NULL AS VARCHAR(20)) AS NonEmployeeName ,
  32.                         CAST(NULL AS VARCHAR(20)) AS ForUser ,
  33.                         FBD.EmployeeOrUserName AS ForDisplayName ,
  34.                         FBD.EmployeeIndex AS ForEmployeeIndex ,
  35.                         FBD.FormStatusId ,
  36.                         FBD.FormStatus AS FormStatusDescription ,
  37.                         FBD.CreatedDate ,
  38.                         FBD.ClosedDate ,
  39.                         DATEDIFF(d, FBD.CreatedDate,
  40.                                  ISNULL(FBD.ClosedDate, GETDATE())) AS DaysOpen ,
  41.                         CPA.CurrentApproverName AS PendingApproverName ,
  42.                         CPA.CurrentApproverId AS PendingApproverUserId ,
  43.                         CAST(NULL AS VARCHAR(20)) AS PendingApproverUserIdExpanded ,
  44.                         ISNULL(ForEmployeeRegion.region_id,
  45.                                CreatedEmployeeRegion.region_id) AS RegionId ,
  46.                         FBD.RegionCode AS FormRegionCode ,
  47.                         CAST(NULL AS VARCHAR(20)) AS RoutePlanExpanded
  48.               FROM      FormBaseData_View FBD
  49.                         INNER JOIN dbo.CurrentAndPriorApprovers_View AS CPA ON FBD.FormId = CPA.FormId
  50.                         LEFT OUTER JOIN WalshGroup_staff_data_syn AS ForEmployeeRegion ON FBD.EmployeeIndex = ForEmployeeRegion.empl_index
  51.                         LEFT OUTER JOIN WalshGroup_staff_data_syn AS CreatedEmployeeRegion ON FBD.CreatedById = CreatedEmployeeRegion.empl_index
  52.                         LEFT OUTER JOIN WalshGroup_group_membership_syn GM ON CPA.CurrentApproverId = GM.group_id
  53.               WHERE     ( ( FBD.FormStatusId = @localFormStatusId
  54.                             OR ( FBD.FormStatusId = 3
  55.                                  AND @localFormStatusId IS NOT NULL
  56.                                )
  57.                           )
  58.                           OR @localFormStatusId IS NULL
  59.                         )
  60.                         AND ( FBD.CreatedById = @localCreatedBy
  61.                               OR @localCreatedBy IS NULL
  62.                             )
  63.                         AND ( FBD.EmployeeIndex = @localFormEmployeeId
  64.                               OR @localFormEmployeeId IS NULL
  65.                             )
  66.                         AND ( @localApproverId IS NULL
  67.                               OR ( @localApproverId IS NOT NULL
  68.                                    AND ( ( @localFormStatusId = 1
  69.                                            AND ( CPA.CurrentApproverId = @localApproverId
  70.                                                  OR GM.login_id = @localApproverId
  71.                                                )
  72.                                          )
  73.                                          OR @localFormStatusId IS NULL
  74.                                          AND FBD.FormId IN (
  75.                                          SELECT FormID
  76.                                          FROM   dbo.RoutePlan
  77.                                          WHERE  Deleted = 0
  78.                                                 AND UserId = @localApproverId
  79.                                                 AND RoutePlanStatusID > 1 )
  80.                                        )
  81.                                  )
  82.                             )            
  83.     --((@localFormStatusId = 1 AND CPA.CurrentApproverId = @ApproverId)
  84.     --           OR
  85.     --          (@localFormStatusId IS NULL AND @ApproverId IN (SELECT 6))
  86.     --          ) OR
  87.              
  88.     --        )
  89.             ) AS DT
  90.             LEFT OUTER JOIN WalshGroup_regions_syn AS Region ON DT.RegionId = Region.region_id
  91.     WHERE   ( DT.RegionId = @localRegionId
  92.               OR @localRegionId IS NULL
  93.             )
RAW Paste Data