Summary Table

Categories Total Count
PII 0
URL 0
DNS 0
EKL 0
IP 0
PORT 0
VsID 0
CF 0
AI 0
VPD 0
PL 0
Other 0

File Content

?-- =============================================
-- Description: Returns Patient Detail Data.
--
-- Maintenance Log:
--
-- Update By Update Date Description
-- ----------- --------- ----------------------------
-- =============================================
CREATE PROCEDURE [dbo].[_DEMOuspRptPiiPatients]
-- Add the parameters for the stored procedure here
@visnIds varchar(max),
@stationIds varchar(max),
@moduleIds varchar(max),
@userId int

AS
DECLARE @stationsTable TABLE
(
RowID int,
ModuleID tinyint,
RegionID tinyint,
CCNRegionID tinyint,
VisnID tinyint,
StationID smallint,
ParentStationID smallint,
Station3N varchar(10),
Station6A varchar(10),
StationDisplayName varchar(100),
StationName varchar(100),
IsParent bit,
Inactive bit
)

BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
BEGIN TRY

--populate what stations the user has permission to see
INSERT INTO @stationsTable (RowID, ModuleID, RegionID, CCNRegionID, VisnID, StationID, ParentStationID, Station3N, Station6A
, StationDisplayName, StationName, IsParent, Inactive)
EXEC seclyr.uspGetUserParentStations @userId, @moduleIds;

--Insert statements for procedure here
SELECT DISTINCT
sx.VisnID
, VisnCode
, sx.StationID
, Station3N
, StationName
, Patients.PatientID
, Patients.PatientName
, Patients.SSN
, IntegrationControlNumber
, PatientIEN
, BatchLogID
, DateSynchronized
FROM Patients
INNER JOIN @stationsTable sx on Patients.StationID = sx.StationID
INNER JOIN Visns on sx.VisnID = Visns.VisnID

WHERE (sx.VisnID In(SELECT [Value] FROM ufnSplit(',', @visnIds)) OR @visnIds IS NULL)
AND (Patients.StationID In(SELECT [Value] FROM ufnSplit(',', @stationIds)) OR @stationIds IS NULL)
--if need to filter by a field with nulls that are doing multi-select + null handler, use this format:
--AND (ISNULL(sx.VisnID, 0) In(SELECT [Value] FROM ufnSplit(',', @visnIds)) OR @visnIds IS NULL)

ORDER By sx.VisnID, Station3N, PatientName
;

END TRY

BEGIN CATCH
PRINT 'There was an error processing the patient details report.' + CHAR(13);
SELECT * FROM dbo.ufnGetErrorInfo();
EXEC uspRaiseErrorInfo;
END CATCH
END