Full Text Indexing Disabled.
jhereg66
Posts: 2
This was the update code generated by SQLCompare. It disables Full Text Indexing, but never restores it. Is this an existing issue?
/*
Script created by SQL Compare from Red Gate Software Ltd at 10/22/2004 10:08:44 PM
Run this script on USATLOSIPRODDB4.BPIDBBase to make it the same as ATLNRSQADB1.BPIDB2QA
Please back up your database before running this script
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT N'Disabling full text indexing'
GO
sp_fulltext_database N'disable'
GO
BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[bpinet_sp_ViolationTableSelectValue]'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
Create Procedure dbo.bpinet_sp_ViolationTableSelectValue
@ViolationTableId int,
@FieldName varchar(100),
@SelectValue varchar(50) Output
AS
BEGIN
--First, we need to derrive the violation table name based
--on the violationId
/*declare @ViolationTableId int
declare @FieldName varchar(100)
declare @HasField bit
select @ViolationTableId = 28
select @FieldName = 'LinkedCode'
*/
declare @HasField bit
declare @strSql nvarchar(1000)
declare @GrpCode int
declare @GrpItem int
SELECT @strSql = N'Select @GrpCode = ComplChkGrpCode, @GrpItem = ComplChkGrpItem FROM obpt_ComplChkGrpItemInfo WHERE ComplChkId = ' + convert(varchar(10), @ViolationTableId)
EXEC sp_executesql @strSql,N'@GrpCode int OUTPUT, @GrpItem int OUTPUT',@GrpCode OUTPUT, @GrpItem OUTPUT
--Now that we know which table we're looking at, we can hit the systables
--to find out if that table has the field we're looking for.
declare @strTableName varchar(50)
Select @strTableName = 'obpt_HospComplViolationListGrp' + convert(nvarchar,@GrpCode) + 'Item' + Convert(nvarchar,@GrpItem)
select @strSql = N'select @HasField = count(*) from syscolumns ' +
'join sysobjects on sysobjects.id = syscolumns.id ' +
'where sysobjects.name = ''' + @strTableName + '''' +
'AND sysColumns.name = ''' + @fieldName + ''''
EXEC sp_executesql @strSql,N'@HasField bit OUTPUT',@HasField OUTPUT
--print @FieldName + ' - ' + convert(varchar(50),@HasField)
if @HasField = 1
begin
Select @SelectValue = @FieldName
end
ELSE
Begin
Select @SelectValue = 'null AS ' + @FieldName
End
--select @strSql = N'Select * from obpt_ComplViolationListGrp' + convert(nvarchar,@GrpCode) + 'Item' + Convert(nvarchar,@GrpItem)
--print @HasField
--print @strSql
RETURN @HasField
END
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BPINet_sp_DropTempTable]'
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE Procedure BPINet_sp_DropTempTable(@TableName VarChar(50)) AS
declare @sql nvarchar(200)
if object_id('tempdb..' + @TableName) > 0
begin
print 'dropping table ' + @TableName
select @sql = 'drop table ' + @TableName
Exec sp_executesql @sql
end
/*
bpinet_sp_droptemptable '#Temp1'
bpinet_sp_droptemptable '#temp2'
Create Table #Temp1 (Code VarChar(10))
Create Table #Temp2 (Code VarChar(10), StdDept Numeric(10,0), Status SmallInt)
*/
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_CIUpdateHistory]'
GO
CREATE procedure bpinet_sp_CIUpdateHistory
@TransNo Numeric(10),
@IsItCorp SmallInt,
@LinkNo numeric(10) ,
@HospID VarChar(10),
@DeptNum VarChar(10),
@ProcCode VarChar(20),
@IsItActive smallint,
@HospDesc VarChar(50),
@TechnicalDesc Varchar(50),
@UserComments VarChar(256),
@CorpStdDept VarChar(18),
@CorpStdCode VarChar(20),
@SimDept VarChar(3),
@SimCode VarChar(10)
as
--**********************************************************************************************************************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Update UB Table History
if isnumeric(@DeptNum)=0 and upper(@DeptNum) <> 'NULL' Set @DeptNum='0'
if isnumeric(@CorpStdDept)=0 and upper(@CorpStdDept) <> 'NULL' Set @CorpStdDept='0'
IF @IsITCorp = 0
Begin
Insert Into osit_wkCdmHistory
(TransNo, LinkNo, HospID, DeptNum, ProcCode,
IsItActive,HospDesc, TechnicalDesc, UserComments,
CorpStdDept, CorpStdCode, SimDept, SimCode)
Values
(@TransNo, @LinkNo, @HospID, @DeptNum, @ProcCode,
@IsItActive,@HospDesc, @TechnicalDesc, @UserComments,
@CorpStdDept, @CorpStdCode, @SimDept, @SimCode)
End
Else
Begin
Insert Into osit_wkcorpCdmHistory
(TransNo, LinkNo, CorpID,
IsItActive,HospDesc, TechnicalDesc, UserComments,
CorpStdDept, CorpStdCode, SimDept, SimCode)
Values
(@TransNo, @LinkNo, @HospID,
@IsItActive,@HospDesc, @TechnicalDesc, @UserComments,
@CorpStdDept, @CorpStdCode, @SimDept, @SimCode)
End
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_GetViolationTableName]'
GO
SET QUOTED_IDENTIFIER OFF
GO
/****** Object: Stored Procedure dbo.SP_GetCounterValue Script Date: 7/25/99 1:46:35 AM ******/
Create PROCEDURE bpinet_sp_GetViolationTableName
@ViolationTableId int,
@strTableName varchar(50) Output
AS
begin
declare @strSql nvarchar(1000)
declare @GrpCode int
declare @GrpItem int
SELECT @strSql = N'Select @GrpCode = ComplChkGrpCode, @GrpItem = ComplChkGrpItem FROM obpt_ComplChkGrpItemInfo WHERE ComplChkId = ' + convert(varchar(10), @ViolationTableId)
EXEC sp_executesql @strSql,N'@GrpCode int OUTPUT, @GrpItem int OUTPUT',@GrpCode OUTPUT, @GrpItem OUTPUT
Select @strTableName = 'obpt_HospComplViolationListGrp' + convert(nvarchar,@GrpCode) + 'Item' + Convert(nvarchar,@GrpItem)
end
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_RptPCComplianceSummary]'
GO
CREATE PROCEDURE [bpinet_sp_RptPCComplianceSummary]
@HospID varchar(10),
@Accepted smallint,
@HCPCSPayor varchar(5),
@UBPayor varchar(5),
@MedHCPCSPayor varchar(5),
@MedUBPayor varchar(5),
@DefFiscInt int,
@OSIPayor smallint,
@DeptNums varchar(256) ,
@OSIDateStamp int,
@HCPCSTOUBVersion int,
@IsItCorp smallint,
@UserID int,
@ViolationOnly smallint,
@FirstRun smallint,
@Future smallint
/*
@OSIPayors varchar(256),
*/
AS
--select @HospID='BH31',@UserID=9138,@HCPCSPayor='C',@UBPayor='U',@MedHCPCSPayor='C',@MedUBPayor='U',@DefFiscInt=45,@OSIPayor=45,@DeptNums='',@OSIDateStamp=20040130,@HCPCSTOUBVersion=20040101,@ViolationOnly=1,@IsItCorp=0,@Accepted=0,@FirstRun=1,@Future=0
/*Select @HospID='BH31',@UserID=9138,@HCPCSPayor='C',
@UBPayor='U',@MedHCPCSPayor='C',@MedUBPayor='U',@DefFiscInt=45,@OSIPayor=45,@DeptNums='',
@OSIDateStamp=20040130,@HCPCSTOUBVersion=20040101,@ViolationOnly=1,@IsItCorp=0,
@Accepted=0,@FirstRun=1,@Future=0
*/
DECLARE
@OsiPayorCode varchar (5)
/*First, parse the list of reportTypes and iterate through a cursor
to add data to the temp table for each type of violation
*/
--Note that reports 23,24,25,26,27,28 and 32 are not used
DECLARE ReportTypeCursor Cursor FOR
SELECT * from func_splitArray('1,2,4,5,6,7,8,10,11,12,13,14,15,16,17,18,19,20,21,22,29,30,31,33,34')
DECLARE @CurrentReport varchar(100)
DECLARE @sql varchar (5000)
DECLARE @SELECTValue varchar(100)
DECLARE @CurrentTable varchar(100)
CREATE TABLE #ComplianceSummary (
[HospId] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeptNum] [varchar](18) NULL ,
[ProcCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OSIPayorCode] [smallint] NULL,
[CPTHCPCSPayor] [varchar] (5) NULL,
[UBPayor] [varchar] (5) NULL,
[PayorOrder] [smallint] NOT NULL,
[IsItClientOverwrite] [int] NULL ,
[UserId] [numeric](18, 0),
[ViolationType] [smallint] NULL,
[ViolationGroup] [smallint] NULL,
[ViolationGroupItem] [smallint] NULL,
[ViolationId] [numeric] (18,0) NOT NULL
)
OPEN ReportTypeCursor
FETCH NEXT FROM ReportTypeCursor
INTO @CurrentReport
@FETCH_STATUS to see if there are any more rows to fetch.
--Declare @LogStart datetime
@FETCH_STATUS = 0
BEGIN
--Set @LogStart = getdate()
--If the current table doesn't have the field we're looking for
--dummy it up, otherwise SELECT the actual value
--PRINT 'getting data for violationtype ' + @CurrentReport
exec bpinet_sp_GetViolationTableName @CurrentReport, @CurrentTable Output
SELECT @sql = 'INSERT INTO #ComplianceSummary'
SELECT @sql = @sql + '(ViolationType,ViolationId,HospId,DeptNum,ProcCode,OSIPayorCode,CPTHCPCSPayor,UBPayor,PayorOrder,IsItClientOverwrite,
UserId,ViolationGroup, ViolationGroupItem)'
--sp_ViolationTableSELECTValue determines if column exists in current table
SELECT @sql = @sql + ' SELECT ' + convert(varchar(3),@CurrentReport) + ' AS ViolationType, '
SELECT @sql = @sql + 'ViolationId, '
exec bpinet_sp_ViolationTableSelectValue @CurrentReport,'HospId',@SELECTValue Output
SELECT @sql = @sql + @SELECTValue + ','
exec bpinet_sp_ViolationTableSelectValue @CurrentReport,'DeptNum',@SELECTValue Output
SELECT @sql = @sql + @CurrentTable + '.' + @SELECTValue + ','
exec bpinet_sp_ViolationTableSelectValue @CurrentReport,'ProcCode',@SELECTValue Output
SELECT @sql = @sql + @SELECTValue + ','
exec bpinet_sp_ViolationTableSelectValue @CurrentReport,'OSIPayorCode',@SELECTValue Output
SELECT @sql = @sql + @SELECTValue + ','
If @CurrentReport in ( 1,4,5,7,8,9,11)
BEGIN
exec bpinet_sp_ViolationTableSelectValue @CurrentReport,'HospHCPCSPayorCode',@SELECTValue Output
SELECT @sql = @sql + @SELECTValue + ','
SELECT @sql = @sql + 'NULL,1,'
END
If @CurrentReport in (2,3,6) -- = 2 OR @CurrentReport = 3 OR @CurrentReport = 6
BEGIN
exec bpinet_sp_ViolationTableSelectValue @CurrentReport,'HospHCPCSPayorCode',@SELECTValue Output
SELECT @sql = @sql + @SELECTValue + ','
exec bpinet_sp_ViolationTableSelectValue @CurrentReport,'HospUBPayorCode',@SELECTValue Output
SELECT @sql = @sql + @SELECTValue + ','
SELECT @sql = @sql + '3,'
END
If @CurrentReport = 10 OR @CurrentReport > 11
BEGIN
SELECT @sql = @sql + 'NULL,NULL,0,'
END
exec bpinet_sp_ViolationTableSelectValue @CurrentReport,'IsItClientOverwrite',@SELECTValue Output
SELECT @sql = @sql + @SELECTValue + ','
exec bpinet_sp_ViolationTableSelectValue @CurrentReport,'UserId',@SELECTValue Output
SELECT @sql = @sql + @CurrentTable + '.' + @SELECTValue + ','
--obtain the group number for the current violationType
DECLARE @GrpCode int
DECLARE @GrpItem int
DECLARE @strGroupNumSql nvarchar(1000)
SELECT @strGroupNumSql = N'SELECT @GrpCode = ComplChkGrpCode, @GrpItem = ComplChkGrpItem FROM obpt_ComplChkGrpItemInfo WHERE ComplChkId = ' + convert(varchar(10), @CurrentReport)
EXEC sp_executesql @strGroupNumSql,N'@GrpCode int OUTPUT, @GrpItem int OUTPUT',@GrpCode OUTPUT, @GrpItem OUTPUT
Select @sql = @sql + convert(varchar(3),@GrpCode) + ' AS ViolationGroup, '
Select @sql = @sql + convert(varchar(3),@GrpItem) + ' AS ViolationGroupItem'
--If the table we're hitting currently doesn't have a deptnum field
--or if a dept filter was not specified, don't add one
--we'll check for "null" in @selectvalue AS a cheezy way of seeing if the deptnum
--field exists
exec bpinet_sp_ViolationTableSelectValue @CurrentReport,'DeptNum',@SelectValue Output
select @sql = @sql + ' FROM ' + @CurrentTable + ' WHERE HospId = ''' + @HospId + ''''
if charindex('null',@SelectValue) = 0 AND @DeptNums <> ''
begin
Select @sql = @sql + ' AND DeptNum IN (' + @DeptNums + ') '
end
SELECT @sql = @sql + ' AND CONVERT(varchar(3), IsItClientOverWrite) = ''' + CONVERT(varchar(3), @Accepted) + ''''
--print @sql
-- Exec sp_executeSql @sql
Exec(@sql)
-- log times for each pass
-- Insert Into osit_PerfMonitor (LogTime, StartTime, EndTime, ElapsedTime, UserID, Type, AddInfo, UserLogin)
-- Values(getdate(),'','',datediff(second,@LogStart,getdate()),@UserID,'BPINet Test LoadTemp1',@sql,'n/a')
-- This is executed AS long AS the previous fetch succeeds.
FETCH NEXT FROM ReportTypeCursor
INTO @CurrentReport
END
CLOSE ReportTypeCursor
DEALLOCATE ReportTypeCursor
UPDATE #ComplianceSummary
SET OSIPayorCode = 1, PayorOrder = 0
WHERE OSIPayorCode Is Null
--finally, now that we have all of the data we need in the temp table.
--we can query the temp table and sort it and move it to another temp table that has
--the rownum field.
--print '#2'
--Set @LogStart = getdate()
CREATE TABLE #ComplianceSummary2(
[RowNum] [int] IDENTITY (1, 1) NOT NULL ,
[ViolationId] [numeric] (18,0) NOT NULL,
[HospId] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeptNum] [varchar](18) NULL ,
[ProcCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OSIPayorCode] [smallint] NULL,
[CPTHCPCSPayor] [varchar] (5) NULL,
[UBPayor] [varchar] (5) NULL,
[PayorOrder] [smallint] NOT NULL,
[HOSPDescription] [varchar] (50) Null,
[IsItClientOverwrite] [int] NULL ,
[UserId] [numeric](18, 0),
[ViolationType] [smallint] NULL,
[ViolationGroup] [smallint] NULL,
[ViolationGroupItem] [smallint] NULL,
[GroupDescription] [varchar] (100) NULL,
[ITEMDescription] [varchar] (100) NULL
PRIMARY KEY CLUSTERED
(
[RowNum]
) ON [PRIMARY]
) ON [PRIMARY]
SELECT @sql = 'Insert into #ComplianceSummary2' +
'(ViolationId,HospId, DeptNum, ProcCode, OSIPayorCode, CPTHCPCSPayor, UBPayor, PayorOrder, IsItClientOverwrite, UserId, ViolationType,ViolationGroup,ViolationGroupItem,GroupDescription, ItemDescription, HospDescription)' +
' SELECT v.ViolationId,v.HospId, v.DeptNum, v.ProcCode, v.OSIPayorCode, v.CPTHCPCSPayor, v.UBPayor, v.PayorOrder, v.IsItClientOverwrite, v.UserId, v.ViolationType, v.ViolationGroup,v.ViolationGroupItem,
grpInfo.DisplayDescription AS GroupDescription, itemInfo.DisplayDescription AS ItemDescription, cdm.HospDesc AS HospDescription
from #ComplianceSummary' + ' v Left JOIN
obpt_ComplChkGrpInfo grpInfo ON grpInfo.ComplChkGrpCode = v.ViolationGroup
Left JOIN obpt_ComplChkGrpItemInfo itemInfo ON itemInfo.ComplChkId = v.ViolationType
Left JOIN '
if @IsItCorp = 1
BEGIN
SELECT @sql = @sql + 'osit_wkcorpcdm cdm ON cdm.CorpId = v.HospId AND cdm.CorpStdDept = v.DeptNum AND cdm.CorpStdCode = v.ProcCode '
END
else
BEGIN
SELECT @sql = @sql + 'osit_wkcdm cdm ON cdm.HospId = v.HospId AND cdm.deptNum = v.DeptNum AND cdm.ProcCode = v.ProcCode 'END
SELECT @sql = @sql + ' Order by v.DeptNum, v.ProcCode'
PRINT @sql
--execute sp_executesql @sql
exec (@sql)
-- log times for each pass
-- Insert Into osit_PerfMonitor (LogTime, StartTime, EndTime, ElapsedTime, UserID, Type, AddInfo, UserLogin)
-- Values(getdate(),'','',datediff(second,@LogStart,getdate()),@UserID,'BPINet Test LoadTemp2',@sql,'n/a')
-- Set @LogStart = getdate() --reset for next log
--select * from #compliancesummary2 ORDER BY DeptNum, ProcCode
/*
list of aliases:
cdm: osit_wkCDM
CVList: #compliancesummary2
CCGII: compliancecheckgrpiteminfo
hlist: osiv_wkHCPCS
ulist: osiv_wkUB
hMedlist: osit_wkHCPCS
uMedlist: osit_wkUB
osi: stdcatactive
ohlist: stdcatHCPCSactive
osi1: stdcatUBactive
dept: osit_deptinfo
p1: osit_wkcdmpricelist
HCPCSPayor: osit_hosppayorlist
UBPayor: osit_hosppayorlist
OSIPayor: osit_osipayorlist
*/
--print '#3'
IF @FirstRun = 1
BEGIN
declare @UserTable varchar(30)
select @UserTable = 'ComplianceSummary' + convert(varchar(10),@UserId)
if object_id(@UserTable) > 0
begin
--print 'dropping existing unsorted temp table'
select @sql = 'drop table ' + @UserTable
--Exec sp_executesql @sql
exec(@sql)
end
select @sql = 'CREATE TABLE ' + @UserTable + ' ('
SELECT @sql = @sql + '[DeptNum] [varchar](18) NULL ,'
SELECT @sql = @sql + ' [ProcCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,'
SELECT @sql = @sql + ' [ItemDesc] [varchar] (50) Null,'
SELECT @sql = @sql + ' [ItemViolated] [varchar] (100) NULL,'
SELECT @sql = @sql + ' [Department] [varchar] (50),'
SELECT @sql = @sql + ' [Volume] [int],'
SELECT @sql = @sql + ' [PrimaryPrice] [money],'
SELECT @sql = @sql + ' [DefRevCode] [varchar] (10), '
SELECT @sql = @sql + ' [MedRevCode] [varchar] (10), '
SELECT @sql = @sql + ' [DefCPTHCPCSCode] [varchar] (10), '
SELECT @sql = @sql + ' [MedCPTHCPCSCode] [varchar] (10), '
SELECT @sql = @sql + ' [BPIRevCode] [varchar] (10), '
SELECT @sql = @sql + ' [BPICPTCode] [varchar] (7), '
-- SELECT @sql = @sql + ' [BPIHCPCSCode] [varchar] (7), '
SELECT @sql = @sql + ' [UserComments] [varchar] (255), '
SELECT @sql = @sql + ' [BPIComments] [varchar] (3000), '
SELECT @sql = @sql + ' [ComplNum] [varchar] (20), '
SELECT @sql = @sql + ' [OSIPayorDesc] [varchar] (80), '
SELECT @sql = @sql + ' [HCPCSPayorDesc] [varchar] (80), '
SELECT @sql = @sql + ' [UBPayorDesc] [varchar] (80), '
SELECT @sql = @sql + ' [PayorOrder] [smallint],'
SELECT @sql = @sql + ' [OSIPayorCode] [smallint],'
SELECT @sql = @sql + ' [SugRevCodes] [varchar] (2000) )'
END
--execute sp_executesql @sql
exec (@sql)
-- log times for each pass
-- Insert Into osit_PerfMonitor (LogTime, StartTime, EndTime, ElapsedTime, UserID, Type, AddInfo, UserLogin)
-- Values(getdate(),'','',datediff(second,@LogStart,getdate()),@UserID,'BPINet Test LoadTemp3',@sql,'n/a')
-- Set @LogStart = getdate() --reset for next log
SELECT @sql = 'Insert into ' + @UserTable + ' '
SELECT @sql = @sql + 'SELECT CONVERT(varchar(10), CVList.DeptNum) AS [DeptNum], '
SELECT @sql = @sql + 'CVList.ProcCode AS [ProcCode], '
SELECT @sql = @sql + 'CVList.HOSPDescription AS [ItemDesc], '
SELECT @sql = @sql + 'CVList.ITEMDescription AS [ItemViolated], '
SELECT @sql = @sql + 'dept.stddeptdesc AS [Department], '
SELECT @sql = @sql + 'CONVERT(int, cdm.annualizedVolume) AS Volume, '
SELECT @sql = @sql + 'p1.price AS [PrimaryPrice], '
SELECT @sql = @sql + 'CONVERT(varchar(10), ulist.ubcode) AS [DefRevCode], '
SELECT @sql = @sql + 'CONVERT(varchar(10), uMedlist.ubcode) AS [MedRevCode], '
SELECT @sql = @sql + '(CASE WHEN hlist.HCPCSCode IS NULL THEN " " ELSE hlist.HCPCSCode END) AS [DefCPTHCPCSCode], '
SELECT @sql = @sql + '(CASE WHEN hMedlist.HCPCSCode IS NULL THEN " " ELSE hMedlist.HCPCSCode END) AS [MedCPTHCPCSCode], '
SELECT @sql = @sql + 'CONVERT(varchar(10), osi1.UBCode) AS [BPIRevCode], '
IF @Future = 1
BEGIN
SELECT @sql = @sql + '(CASE WHEN ohlist.CPTReviewCPTCodeFuture IS NOT NULL THEN ohlist.CPTReviewCPTCodeFuture '
SELECT @sql = @sql + 'ELSE ohlist.CPTReviewHCPCSCodeFuture END) AS [BPICPTCode], '
END
ELSE
BEGIN
SELECT @sql = @sql + '(CASE WHEN ohlist.CPTReviewCPTCode IS NOT NULL THEN ohlist.CPTReviewCPTCode '
SELECT @sql = @sql + 'ELSE ohlist.CPTReviewHCPCSCode END) AS [BPICPTCode], '
END
--SELECT @sql = @sql + '(CASE WHEN ' + @Future + '= 1 THEN '
--SELECT @sql = @sql + '(CASE WHEN ohlist.CPTReviewCPTCodeFuture IS NOT NULL THEN ohlist.CPTReviewCPTCodeFuture '
--SELECT @sql = @sql + 'ELSE ohlist.CPTReviewHCPCSCodeFuture END) ELSE '
--SELECT @sql = @sql + '(CASE WHEN ohlist.CPTReviewCPTCode IS NOT NULL THEN ohlist.CPTReviewCPTCode '
--SELECT @sql = @sql + 'ELSE ohlist.CPTReviewHCPCSCode END) END) AS [BPICPTCode], '
SELECT @sql = @sql + 'cdm.UserComments AS [UserComments], '
SELECT @sql = @sql + 'ohlist.Comments AS [BPIComments], '
SELECT @sql = @sql + 'convert(varchar(7), CCGII.ComplChkGrpCode) + " " + convert(varchar(7), CCGII.ComplChkGrpItem) AS ComplNum, '
SELECT @sql = @sql + '(CASE WHEN OSIPayor.osipayordesc IS NULL THEN "Payor Non-Applicable" ELSE OSIPayor.osipayordesc END) AS OSIPayorDesc, '
SELECT @sql = @sql + '(CASE WHEN HCPCSPayor.HospPayorDesc IS NULL THEN " " ELSE HCPCSPayor.HospPayorDesc END) AS HCPCSPayorDesc, '
SELECT @sql = @sql + '(CASE WHEN UBPayor.HospPayorDesc IS NULL THEN " " ELSE UBPayor.HospPayorDesc END) AS UBPayorDesc, '
SELECT @sql = @sql + 'CVList.PayorOrder, '
SELECT @sql = @sql + 'CVList.OSIPayorCode, '
SELECT @sql = @sql + '" "'
IF @IsItCorp = 0
BEGIN
SELECT @sql = @sql + ' FROM osit_wkCDM cdm WITH(NOLOCK) '
END
ELSE
BEGIN
SELECT @sql = @sql + ' FROM osit_wkCorpCDM cdm WITH(NOLOCK) '
END
IF @ViolationOnly = 0
BEGIN
SELECT @sql = @sql + ' LEFT JOIN #ComplianceSummary2 CVList WITH(NOLOCK) '
END
ELSE
BEGIN
SELECT @sql = @sql + ' INNER JOIN #ComplianceSummary2 CVList WITH(NOLOCK) '
END
IF @IsItCorp = 0
BEGIN
SELECT @sql = @sql + ' ON cdm.ProcCode = CVList.ProcCode '
SELECT @sql = @sql + ' AND cdm.DeptNum = CVList.DeptNum '
SELECT @sql = @sql + ' AND cdm.HospID = CVList.HospID '
END
ELSE
BEGIN
SELECT @sql = @sql + ' ON cdm.CorpStdCode = CVList.Proccode '
SELECT @sql = @sql + ' AND cdm.CorpStdDept = CVList.deptNum '
SELECT @sql = @sql + ' AND cdm.CorpID = CVList.hospid '
END
--added zero to IN in order to capture info from violation tables w/o OSIPayorCodes
--SELECT @sql = @sql + ' AND CONVERT(varchar(10), cvlist.OSIPayorCode) IN (0, ' + @OSIPayors + ') ) '
SELECT @sql = @sql + ' AND CONVERT(varchar(10), cvlist.OSIPayorCode) = ''' + CONVERT(varchar(10), @OSIPayor) + ''''
SELECT @sql = @sql + ' LEFT JOIN obpt_ComplChkGrpItemInfo CCGII WITH(NOLOCK) '
SELECT @sql = @sql + ' ON CCGII.ComplChkGrpCode = CVList.ViolationGroup '
SELECT @sql = @sql + ' AND CCGII.ComplChkGrpItem = CVList.ViolationGroupItem '
IF @IsItCorp = 0
BEGIN
SELECT @sql = @sql + ' LEFT JOIN osit_wkCDMHcpcsList hlist WITH(NOLOCK) '
SELECT @sql = @sql + ' ON cdm.linkno = hlist.linkno '
SELECT @sql = @sql + ' AND hlist.hosppayorcode = dbo.FUNC_GetPCCPTPayorv2Wk(cdm.LinkNo, ''' + @HCPCSPayor + ''', ''' + @HospID + ''')'
SELECT @sql = @sql + ' AND HCPCSEffDate = (SELECT MAX(HCPCSEffDate) FROM osit_WkCDMHcpcsList WHERE Linkno = CDM.LinkNo AND hosppayorcode = hlist.HospPayorCode AND HCPCSEffDate <= GetDate())'
END
ELSE
BEGIN
SELECT @sql = @sql + ' LEFT JOIN osit_wkCorpCDMHcpcsList hlist WITH(NOLOCK) '
SELECT @sql = @sql + ' ON cdm.linkno = hlist.linkno '
SELECT @sql = @sql + ' AND hlist.hosppayorcode = dbo.FUNC_GetPCCPTPayorv2Corp(cdm.LinkNo, ''' + @HCPCSPayor + ''', ''' + @HospID + ''')'
SELECT @sql = @sql + ' AND HCPCSEffDate = (SELECT MAX(HCPCSEffDate) FROM osit_WkCorpCDMHcpcsList WHERE Linkno = CDM.LinkNo AND HospPayorCode = hlist.HospPayorCode AND HCPCSEffDate <= GetDate())'
END
IF @IsItCorp = 0
BEGIN
SELECT @sql = @sql + ' LEFT JOIN osit_wkCDMUBList ulist WITH(NOLOCK) '
SELECT @sql = @sql + ' ON cdm.linkno = ulist.linkno '
SELECT @sql = @sql + ' AND ulist.hosppayorcode = dbo.FUNC_GetPCUBPayorv2Wk(cdm.LinkNo, ''' + @UBPayor + ''', ''' + @HospID + ''')'
SELECT @sql = @sql + ' AND UBEffDate = (SELECT MAX(UBEffDate) FROM osit_WkCDMUBList WHERE Linkno = CDM.LinkNo AND HospPayorCode = ulist.HospPayorCode AND UBEffDate <= GetDate())'
END
ELSE
BEGIN
SELECT @sql = @sql + ' LEFT JOIN osit_wkCorpCDMUBList ulist WITH(NOLOCK) '
SELECT @sql = @sql + ' ON cdm.linkno = ulist.linkno '
SELECT @sql = @sql + ' AND ulist.hosppayorcode = dbo.FUNC_GetPCUBPayorv2Corp(cdm.LinkNo, ''' +@UBPayor + ''', ''' + @HospID + ''')'
SELECT @sql = @sql + ' AND UBEffDate = (SELECT MAX(UBEffDate) FROM osit_WkCorpCDMUBList WHERE Linkno = CDM.LinkNo AND HospPayorCode = ulist.HospPayorCode AND UBEffDate <= GetDate())'
END
IF @IsItCorp = 0
BEGIN
SELECT @sql = @sql + ' LEFT JOIN osit_wkCDMHCPCSList hMedlist WITH(NOLOCK) '
SELECT @sql = @sql + ' ON cdm.linkno = hMedlist.linkno '
SELECT @sql = @sql + ' AND hMedlist.hosppayorcode = ''' + @MedHCPCSPayor + ''''
SELECT @sql = @sql + ' AND hMedList.HCPCSEffDate = (SELECT MAX(HCPCSEffDate) FROM osit_WkCDMHcpcsList WHERE Linkno = CDM.LinkNo AND HCPCSEffDate <= GetDate() AND hMedlist.hosppayorcode = ''' + @MedHCPCSPayor + ''' )'
END
ELSE
BEGIN
SELECT @sql = @sql + ' LEFT JOIN osit_wkCorpCDMHCPCSList hMedlist WITH(NOLOCK) '
SELECT @sql = @sql + ' ON cdm.linkno = hMedlist.linkno '
SELECT @sql = @sql + ' AND hMedlist.hosppayorcode = ''' + @MedHCPCSPayor + ''''
SELECT @sql = @sql + ' AND hMedList.HCPCSEffDate = (SELECT MAX(HCPCSEffDate) FROM osit_WkCorpCDMHcpcsList WHERE Linkno = CDM.LinkNo AND HCPCSEffDate <= GetDate() AND hMedlist.hosppayorcode = ''' + @MedHCPCSPayor + ''')'
END
IF @IsItCorp = 0
BEGIN
SELECT @sql = @sql + ' LEFT JOIN osit_wkCDMUBList uMedlist WITH(NOLOCK) '
SELECT @sql = @sql + ' ON cdm.linkno = uMedlist.linkno '
SELECT @sql = @sql + ' AND uMedlist.hosppayorcode = ''' + @MedUBPayor + ''''
SELECT @sql = @sql + ' AND uMedList.UBEffDate = (SELECT MAX(UBEffDate) FROM osit_WkCDMUBList WHERE Linkno = CDM.LinkNo AND UBEffDate <= GetDate() AND uMedlist.hosppayorcode = ''' + @MedUBPayor + ''')'
END
ELSE
BEGIN
SELECT @sql = @sql + ' LEFT JOIN osit_wkCorpCDMUBList uMedlist WITH(NOLOCK) '
SELECT @sql = @sql + ' ON cdm.linkno = uMedlist.linkno '
SELECT @sql = @sql + ' AND uMedlist.hosppayorcode = ''' + @MedUBPayor + ''''
SELECT @sql = @sql + ' AND uMedList.UBEffDate = (SELECT MAX(UBEffDate) FROM osit_WkCorpCDMUBList WHERE Linkno = CDM.LinkNo AND UBEffDate <= GetDate() AND uMedlist.hosppayorcode = ''' + @MedUBPayor + ''')'
END
SELECT @sql = @sql + ' LEFT JOIN osit_StdCatalogActive osi WITH(NOLOCK) '
SELECT @sql = @sql + ' ON cdm.OsiStdCode = osi.StdCode '
SELECT @sql = @sql + ' AND osi.IsItCDMMaster = 1 '
SELECT @sql = @sql + ' LEFT JOIN osit_StdCatalogHcpcsListActive ohlist WITH(NOLOCK) '
SELECT @sql = @sql + ' ON osi.StdCode = ohlist.stdCode '
SELECT @sql = @sql + ' AND CONVERT(varchar(10), ohlist.OsiPayorCode) = '
SELECT @sql = @sql + ' dbo.FUNC_GetOSICPTPayorv2(ohlist.stdCode, ''' + CONVERT(varchar(10), @DefFiscInt) + ''''
SELECT @sql = @sql + ') LEFT JOIN osit_StdCatalogUBListActive osi1 WITH(NOLOCK) '
SELECT @sql = @sql + ' ON osi.StdCode = osi1.stdCode '
SELECT @sql = @sql + ' AND osi1.Priority = 1 '
SELECT @sql = @sql + ' AND CONVERT(varchar(10), osi1.OsiPayorCode) = '
SELECT @sql = @sql + ' dbo.FUNC_GetOSIUBPayorv2(osi1.stdCode, ''' + CONVERT(varchar(10), @DefFiscInt) + ''''
SELECT @sql = @sql + ') LEFT JOIN osit_DeptInfo dept WITH(NOLOCK) '
IF @IsItCorp = 0
BEGIN
SELECT @sql = @sql + ' ON cdm.DeptNum = dept.DeptNum '
SELECT @sql = @sql + ' AND cdm.HospID = dept.HospID '
SELECT @sql = @sql + ' LEFT JOIN osit_wkCDMPriceList p1 WITH(NOLOCK) '
END
ELSE
BEGIN
SELECT @sql = @sql + ' ON cdm.CorpStdDept = dept.DeptNum '
SELECT @sql = @sql + ' AND cdm.CorpID = dept.HospID '
SELECT @sql = @sql + ' LEFT JOIN osit_wkCorpCDMPriceList p1 WITH(NOLOCK) '
END
SELECT @sql = @sql + ' ON cdm.linkno = p1.linkno '
SELECT @sql = @sql + ' AND p1.priceid = 1 '
SELECT @sql = @sql + ' LEFT JOIN osit_HospPayorList HCPCSPayor WITH(NOLOCK) '
SELECT @sql = @sql + ' ON HCPCSPayor.Hosppayorcode = cvlist.CPTHCPCSPayor '
SELECT @sql = @sql + ' AND HCPCSPayor.payortype = 1 '
SELECT @sql = @sql + ' AND HCPCSPayor.isitactive = 1 '
SELECT @sql = @sql + ' AND hcpcspayor.hospid = CVList.hospid '
SELECT @sql = @sql + ' LEFT JOIN osit_HospPayorList UBPayor WITH(NOLOCK) '
SELECT @sql = @sql + ' ON UBPayor.Hosppayorcode = cvlist.UBPayor '
SELECT @sql = @sql + ' AND UBpayor.payortype = 2 '
SELECT @sql = @sql + ' AND UBpayor.isitactive = 1 '
SELECT @sql = @sql + ' AND UBpayor.hospid = CVList.hospid '
SELECT @sql = @sql + ' LEFT JOIN osit_OSIPayorList OSIPayor With(nolock) '
SELECT @sql = @sql + ' ON OSIPayor.OSIPayorCode = cvlist.OSIPayorCode '
SELECT @sql = @sql + ' WHERE CONVERT(varchar(10), cdm.OSIDateStamp) = ''' + CONVERT(varchar(10), @OSIDateStamp) + ''''
SELECT @sql = @sql + ' AND cdm.IsItActive = 1 '
print @sql
--insert for #ComplianceSummary is executed here
--execute sp_executesql @sql
exec (@sql)
-- log times for each pass
-- Insert Into osit_PerfMonitor (LogTime, StartTime, EndTime, ElapsedTime, UserID, Type, AddInfo, UserLogin)
-- Values(getdate(),'','',datediff(second,@LogStart,getdate()),@UserID,'BPINet Test LoadTemp4',@sql,'n/a')
-- Set @LogStart = getdate() --reset for next log
--add suggested revenue code column
/*
SET CONCAT_NULL_YIELDS_NULL OFF
DECLARE
@UBCode varchar(20),
@HCPCSCode varchar(7)
DECLARE
sugrevcodes_cursor
CURSOR FOR
SELECT DISTINCT
CONVERT(varchar(15), umap.UBCode),
umap.HCPCSCode
FROM
osit_HCPCS2ubmap umap WITH(NOLOCK)
INNER JOIN
#ComplianceSummary3
ON
#ComplianceSummary3.[BPICPTCode] = umap.HCPCSCode
LEFT JOIN
osit_UBCodeCatalog ucat WITH(NOLOCK)
ON
ucat.UBCode = umap.UBCode
WHERE
umap.Hcpcs2UBMapVersion = @UBVersion
AND
umap.OSIPayorCode = @OSIPayor
OPEN sugrevcodes_cursor
-- Perform the first fetch.
FETCH NEXT FROM sugrevcodes_cursor
INTO @UBCode, @HCPCSCode
@FETCH_STATUS to see if there are any more rows to fetch.
@FETCH_STATUS = 0
BEGIN
UPDATE #ComplianceSummary3
SET
[SugRevCodes] = [SugRevCodes] + ', ' + @UBCode
WHERE
#ComplianceSummary3.[BPICPTCode] = @HCPCSCode
AND
[SugRevCodes] Is Not Null
UPDATE #ComplianceSummary3
SET
[SugRevCodes] = @UBCode
WHERE
#ComplianceSummary3.[BPICPTCode] = @HCPCSCode
AND
[SugRevCodes] Is Null
FETCH NEXT FROM sugrevcodes_cursor
INTO @UBCode, @HCPCSCode
END
CLOSE sugrevcodes_cursor
DEALLOCATE sugrevcodes_cursor
*/
SELECT @sql = 'SELECT * FROM ' + @UserTable + ' '
SELECT @sql = @sql + 'ORDER BY [DeptNum], [ProcCode] '
--print @sql
--execute sp_executesql @sql
exec (@sql)
-- log times for each pass
-- Insert Into osit_PerfMonitor (LogTime, StartTime, EndTime, ElapsedTime, UserID, Type, AddInfo, UserLogin)
-- Values(getdate(),'','',datediff(second,@LogStart,getdate()),@UserID,'BPINet Test LoadTemp5',@sql,'n/a')
-- Set @LogStart = getdate() --reset for next log
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_RptPCCPTLegalPrint]'
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [bpinet_sp_RptPCCPTLegalPrint]
AS
SELECT
MedAssetsRegister,
CPTFARSFooter,
CPTCopyrightFooter,
CPTLegalPrint
FROM
osit_LegalInfo
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_UBUpdateWk]'
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure bpinet_sp_UBUpdateWk
@IsItCorp SmallInt,
@ID uniqueidentifier,
@LinkNo numeric(10) ,
@PayorCode VarChar(10),
@EffDate datetime,
@Code VarChar(10)
as
--**********************************************************************************************************************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Update UB Table
IF @IsITCorp = 0
Begin
IF (Select Count(LinkNO) From osit_wkCdmUbList Where UBID = @ID) > 0
Begin
update osit_wkCdmUbList set
UBEffDate = @EffDate,
UBCode = @Code
Where UBID = @ID
End
Else
Begin
--Priority is a dead field that does not allow nulls
Insert Into osit_wkCdmUbList
(UBID, LinkNo, HospPayorCode, UBEffDate, UBCode, Priority)
Values
(@ID, @LinkNo, @PayorCode, @EffDate, @Code,0)
End
End
Else
Begin
IF (Select Count(LinkNO) From osit_wkcorpCdmUbList Where UBID = @ID) > 0
Begin
update osit_wkcorpCdmUbList set
UBEffDate = @EffDate,
UBCode = @Code
Where UBID = @ID
End
Else
Begin
Insert Into osit_wkcorpCdmUbList
(UBID, LinkNo, HospPayorCode, UBEffDate, UBCode,Priority)
Values
(@ID, @LinkNo, @PayorCode, @EffDate, @Code,0)
End
End
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_RptPCDuplicateItemsByDesc]'
GO
CREATE PROCEDURE [bpinet_sp_RptPCDuplicateItemsByDesc]
@HospID varchar(10),
@Accepted smallint,
@OSIDateStamp int,
@IsItCorp smallint,
@UserID int
AS
--select @HospId='CAR58',@Accepted=0,@OSIDateStamp=20020920,@IsItCorp=1,@USerId=-1
CREATE TABLE #DiffPriceDescriptions (
[ViolationID] numeric (18,0),
[DeptNum] varchar(10),
[ProcCode] varchar(20),
[ItemDesc] varchar(50),
[Volume] int,
[PrimaryPrice] money,
[PriceType] smallint,
[Accepted] smallint )
IF @UserID <> -1 --Run report on specific departments, in osit_BPISelectedDept
AND @IsItCorp = 1 --Corporate is true
BEGIN
INSERT INTO #DiffPriceDescriptions
SELECT
lst.ViolationID,
cdm.CorpStdDept,
cdm.CorpStdCode,
cdm.HospDesc,
CONVERT(int, (CASE WHEN cdm.AnnualizedVolume IS NULL THEN 0 ELSE cdm.AnnualizedVolume END)) ,
ipp.Price ,
ipp.PriceType,
lst.IsItClientOverwrite
FROM obpt_HospComplViolationListGrp2Item2 lst WITH(NOLOCK)
INNER JOIN osit_wkCorpCDM cdm WITH(NOLOCK)
ON cdm.CorpID = lst.HospID
AND cdm.CorpStdCode = lst.Proccode
AND cdm.CorpStdDept = lst.DeptNum
AND cdm.OSIDateStamp = @OSIDateStamp
AND cdm.IsItActive = 1
LEFT JOIN osit_wkCorpCDMPriceList ipp WITH(NOLOCK)
ON ipp.LinkNo = cdm.LinkNo
AND ipp.PriceID = 1
AND ipp.PriceEffDate = (SELECT MAX(PriceEffDate) FROM osit_wkCorpCDMPriceList WHERE linkno = cdm.linkno AND PriceID = 1 AND PriceEffDate <= getdate())
WHERE lst.HospID = @HospID
AND lst.IsItClientOverwrite = @Accepted
AND lst.DeptNum IN (Select DeptNum from osit_BPISelectedDept WHERE UserID = @UserID)
END
IF @UserID <> -1 --Run report on specific departments, in osit_BPISelectedDept
AND @IsItCorp = 0 --Corporate is false
BEGIN
INSERT INTO #DiffPriceDescriptions
SELECT
lst.ViolationID,
cdm.DeptNum,
cdm.ProcCode,
cdm.HospDesc,
CONVERT(int, (CASE WHEN cdm.AnnualizedVolume IS NULL THEN 0 ELSE cdm.AnnualizedVolume END)) ,
ipp.Price ,
ipp.PriceType,
lst.IsItClientOverwrite
FROM obpt_HospComplViolationListGrp2Item2 lst WITH(NOLOCK)
INNER JOIN osit_wkCDM cdm WITH(NOLOCK)
ON cdm.HospID = lst.HospID
AND cdm.ProcCode = lst.Proccode
AND cdm.DeptNum = lst.DeptNum
AND cdm.OSIDateStamp = @OSIDateStamp
AND cdm.IsItActive = 1
LEFT JOIN osit_wkCDMPriceList ipp WITH(NOLOCK)
ON ipp.LinkNo = cdm.LinkNo
AND ipp.PriceID = 1
AND ipp.PriceEffDate = (SELECT MAX(PriceEffDate) FROM osit_wkCDMPriceList WHERE linkno = cdm.linkno AND PriceID = 1 AND PriceEffDate <= getdate())
WHERE lst.HospID = @HospID
AND lst.IsItClientOverwrite = @Accepted
AND lst.DeptNum IN (Select DeptNum from osit_BPISelectedDept WHERE UserID = @UserID)
END
IF @UserID = -1 --Run report on all departments
AND @IsItCorp = 1 --Corporate is true
BEGIN
INSERT INTO #DiffPriceDescriptions
SELECT
lst.ViolationID,
cdm.CorpStdDept,
cdm.CorpStdCode,
cdm.HospDesc,
CONVERT(int, (CASE WHEN cdm.AnnualizedVolume IS NULL THEN 0 ELSE cdm.AnnualizedVolume END)) ,
ipp.Price ,
ipp.PriceType,
lst.IsItClientOverwrite
FROM obpt_HospComplViolationListGrp2Item2 lst WITH(NOLOCK)
INNER JOIN osit_wkCorpCDM cdm WITH(NOLOCK)
ON cdm.CorpID = lst.HospID
AND cdm.CorpStdCode = lst.Proccode
AND cdm.CorpStdDept = lst.DeptNum
AND cdm.OSIDateStamp = @OSIDateStamp
AND cdm.IsItActive = 1
LEFT JOIN osit_wkCorpCDMPriceList ipp WITH(NOLOCK)
ON ipp.LinkNo = cdm.LinkNo
AND ipp.PriceID = 1
AND ipp.PriceEffDate = (SELECT MAX(PriceEffDate) FROM osit_wkCorpCDMPriceList WHERE linkno = cdm.linkno AND PriceID = 1 AND PriceEffDate <= getdate())
WHERE lst.HospID = @HospID
AND lst.IsItClientOverwrite = @Accepted
END
IF @UserID = -1 --Run report on all departments
AND @IsItCorp = 0 --Corporate is false
BEGIN
INSERT INTO #DiffPriceDescriptions
SELECT
lst.ViolationID,
cdm.DeptNum,
cdm.ProcCode,
cdm.HospDesc,
CONVERT(int, (CASE WHEN cdm.AnnualizedVolume IS NULL THEN 0 ELSE cdm.AnnualizedVolume END)) ,
ipp.Price ,
ipp.PriceType,
lst.IsItClientOverwrite
FROM obpt_HospComplViolationListGrp2Item2 lst WITH(NOLOCK)
INNER JOIN osit_wkCDM cdm WITH(NOLOCK)
ON cdm.HospID = lst.HospID
AND cdm.ProcCode = lst.Proccode
AND cdm.DeptNum = lst.DeptNum
AND cdm.OSIDateStamp = @OSIDateStamp
AND cdm.IsItActive = 1
LEFT JOIN osit_wkCDMPriceList ipp WITH(NOLOCK)
ON ipp.LinkNo = cdm.LinkNo
AND ipp.PriceID = 1
AND ipp.PriceEffDate = (SELECT MAX(PriceEffDate) FROM osit_wkCDMPriceList WHERE linkno = cdm.linkno AND PriceID = 1 AND PriceEffDate <= getdate())
WHERE lst.HospID = @HospID
AND lst.IsItClientOverwrite = @Accepted
END
SELECT
ViolationID,
[ItemDesc] AS [DuplicateItem],
[DeptNum],
[ProcCode],
[ItemDesc],
[Volume],
[PrimaryPrice],
[PriceType],
[Accepted]
FROM
#DiffPriceDescriptions
WHERE
((([ItemDesc]) IN
(SELECT [ItemDesc]
FROM [#DiffPriceDescriptions] AS Tmp
GROUP BY [ItemDesc]
-- HAVING Count(*)>1 remmed by rich not pulling violations when only one left
)))
ORDER BY
[ItemDesc],
[DeptNum],
[ProcCode]
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_RptPCLinkedCodesNotCDMtest]'
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [bpinet_sp_RptPCLinkedCodesNotCDMtest]
@HospID varchar(10),
@Accepted smallint,
@OSIPayor smallint,
@HCPCSVersion smallint,
@CPTVersion smallint,
@UserId int,
@IsItCorp tinyint
AS
--use any for testing
--ready for butch
--this report does not have departments
--and is not tied to corporate columns/tables
--ergo, no IF clauses
SELECT DISTINCT
vList.LinkedCode AS [MissingCPTHCPCSCode], ccat.ShortDesc AS [MissingCodeDesc],
vList.CPTCode AS [CPTHCPCSCodeLinked], lcat.ShortDesc AS [CodeLinkDesc] ,
vList.IsItClientOverwrite AS [Accepted],
vList.ViolationId
FROM obpt_HospComplViolationListGrp5Item3 vList WITH(NOLOCK)
LEFT JOIN osit_CodeCatalog ccat WITH(NOLOCK)
ON ccat.HCPCSCode = vList.CPTCode
AND ccat.Version = @CPTVersion
AND ccat.OsiPayorCode = @OSIPayor
LEFT JOIN osit_CodeCatalog lcat WITH(NOLOCK)
ON lcat.HCPCSCode = vList.CPTCode
AND lcat.Version = @HCPCSVersion
AND lcat.OsiPayorCode = @OSIPayor
WHERE vList.HospID = @HospID
AND vList.IsItClientOverwrite = @Accepted
ORDER BY
vList.LinkedCode,
vList.CPTCode
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_HCPCSUpdateWk]'
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure bpinet_sp_HCPCSUpdateWk
@IsItCorp SmallInt,
@ID uniqueidentifier,
@LinkNo numeric(10) ,
@PayorCode VarChar(10),
@EffDate datetime,
@Code VarChar(10),
@Mod1 VarChar(10),
@Mod2 VarChar(10)
as
--**********************************************************************************************************************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Update Table
IF @IsITCorp = 0
Begin
IF (Select Count(LinkNo) From osit_wkCdmHCPCSList Where HCPCSID = @ID) > 0
Begin
update osit_wkCdmHcpcsList set
HCPCSEffDate = @EffDate,
HCPCSCode = @Code,
ModifierCode1 = @Mod1,
ModifierCode2 = @Mod2
Where HCPCSID = @ID
End
Else
Begin
--Priority is a dead field that does not allow nulls
--IsItCPT is also dead
Insert Into osit_wkCdmHcpcsList
(HCPCSID, LinkNo, HospPayorCode, HCPCSEffDate, HCPCSCode, ModifierCode1, ModifierCode2 , Priority, IsItCPTCode)
Values
(@ID, @LinkNo, @PayorCode, @EffDate, @Code, @Mod1, @Mod2 ,0, 0)
End
End
Else
Begin
IF (Select Count(LinkNo) From osit_wkcorpCdmHCPCSList Where HCPCSID = @ID) > 0
Begin
update osit_wkcorpCdmHcpcsList set
HCPCSEffDate = @EffDate,
HCPCSCode = @Code,
ModifierCode1 = @Mod1,
ModifierCode2 = @Mod2
Where HCPCSID = @ID
End
Else
Begin
--Priority is a dead field that does not allow nulls
Insert Into osit_wkcorpCdmHcpcsList
(HCPCSID, LinkNo, HospPayorCode, HCPCSEffDate, HCPCSCode, ModifierCode1, ModifierCode2 , Priority)
Values
(@ID, @LinkNo, @PayorCode, @EffDate, @Code, @Mod1, @Mod2 ,0)
End
End
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_RptPCAPCLegend]'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [bpinet_sp_RptPCAPCLegend]
AS
SELECT
APCStatusCode + ': ' + APCStatusDesc AS APCLegend
FROM
osit_APCStatusCodeList
ORDER BY
APCStatusCode
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_CIGetLinkNo]'
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure bpinet_sp_CIGetLinkNo
@IsItCorp SmallInt,
@HospID varchar(10),
@DeptNum varchar(10),
@ProcCode varchar(20)
as
--**********************************************************************************************************************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Declare @LinkNo numeric(10)
IF @IsItCorp = 0
Begin
Set @LinkNo =
(Select LinkNo From osit_WkCDM
Where HospID = @HospID AND DeptNum = @DeptNum AND ProcCode = @ProcCode)
End
ELSE
Begin
Set @LinkNo =
(Select LinkNo From osit_WkCorpCDM
Where CorpID = @HospID AND CorpStdDept = @DeptNum AND CorpStdCode = @ProcCode)
End
Select @LinkNo
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_GetOSIDeptList]'
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE bpinet_sp_GetOSIDeptList
@HospId as varchar(10)
AS
SELECT distinct osi.StdDept AS DeptNum,
dept.StdDeptDesc,
CASE WHEN (sel.Deptnum is not null)
THEN 1
ELSE 0
END AS IsSelected
FROM osit_StdCatalogActive osi
JOIN osit_StdDeptCatalog dept
ON osi.StdDept = dept.StdDeptCode
LEFT JOIN osit_BPISelectedOSIDept sel
ON osi.StdDept= sel.DeptNum
AND sel.HospId = @HospId
ORDER BY osi.StdDept
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_PriceUpdate]'
GO
SET ANSI_NULLS ON
GO
CREATE procedure bpinet_sp_PriceUpdate
@IsItCorp SmallInt,
@LinkNo numeric(10) ,
@PriceID VarChar(10),
@OldEffDate datetime,
@EffDate datetime,
@Price Money
as
--**********************************************************************************************************************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Update Table
IF @IsITCorp = 0
Begin
IF (Select Count(LinkNo) From osit_wkCdmPriceList Where LinkNo = @LinkNo AND PriceID = @PriceID AND PriceEffDate = @OldEffDate) > 0
Begin
update osit_wkCdmPriceList set
PriceEffDate = @EffDate,
Price = @Price
Where LinkNo = @LinkNo AND PriceID = @PriceID AND PriceEffDate = @OldEffDate
End
Else
Begin
Insert Into osit_wkCdmPriceList
(LinkNo, PriceID, PriceEffDate, Price, PriceType)
Values
(@LinkNo, @PriceID, @EffDate, @Price, 1)
End
End
Else
Begin
IF (Select Count(LinkNo) From osit_wkCorpCdmPriceList Where LinkNo = @LinkNo AND PriceID = @PriceID AND PriceEffDate = @OldEffDate) > 0
Begin
update osit_wkcorpCdmPriceList set
PriceEffDate = @EffDate,
Price = @Price
Where LinkNo = @LinkNo AND PriceID = @PriceID AND PriceEffDate = @OldEffDate
End
Else
Begin
Insert Into osit_wkcorpCdmPriceList
(LinkNo, PriceID, PriceEffDate, Price, PriceType)
Values
(@LinkNo, @PriceID, @EffDate, @Price, 1)
End
End
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_CIInsert]'
GO
Create procedure bpinet_sp_CIInsert
@IsItCorp SmallInt,
@HospID VarChar(10),
@DeptNum VarChar(10) ,
@ProcCode VarChar(20),
@OSIDateStamp Numeric(8)
as
--**********************************************************************************************************************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Update UB Table History
IF @IsITCorp = 0
Begin
Insert Into osit_wkCdm
(HospID, DeptNum, ProcCode, OSIDateStamp )
Values
(@HospID, @DeptNum, @ProcCode, @OSIDateStamp)
-- now get the linkno as a result set
Select LinkNo From osit_wkCDM
Where HospID = @HospID and DeptNum = @DeptNum and ProcCode = @ProcCode and OSIDateStamp = @OSIDateStamp
End
Else
Begin
Insert Into osit_wkcorpCdm
(CorpID, CorpStdDept, CorpStdCode, OSIDateStamp )
Values
(@HospID, @DeptNum, @ProcCode, @OSIDateStamp)
-- now get the linkno as a result set
Select LinkNo From osit_wkcorpCDM
Where CorpID = @HospID and CorpStdDept = @DeptNum and CorpStdCode = @ProcCode and OSIDateStamp = @OSIDateStamp
End
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_KMRevGet]'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE bpinet_sp_KMRevGet
@Code as varchar(10)
AS
--Priority is expected by the object, but not needed for this query
select UBCode,UBCodeDesc,Comments, Priority=0
from osit_UBCodeCatalog
Where UBCode = @Code
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_CIUpdate]'
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure bpinet_sp_CIUpdate
@IsItCorp SmallInt,
@LinkNo numeric(10) ,
@HospID VarChar(10),
@DeptNum VarChar(10),
@ProcCode VarChar(20),
@IsItActive smallint,
@HospDesc VarChar(50),
@TechnicalDesc Varchar(50),
@UserComments VarChar(256),
@CorpStdDept VarChar(18),
@CorpStdCode VarChar(20),
@SimDept VarChar(3),
@SimCode VarChar(10)
as
--**********************************************************************************************************************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Update UB Table
-- Convert Dept Numbers to SmallInt until we change the datatypes in the table.
if isnumeric(@DeptNum)=0 Set @DeptNum='0'
if isnumeric(@CorpStdDept)=0 Set @CorpStdDept='0'
IF @IsITCorp = 0
Begin
Update osit_wkCdm Set
HospID = @HospID,
DeptNum = @DeptNum,
ProcCode = @ProcCode,
IsItActive = @IsItActive,
HospDesc = @HospDesc,
TechnicalDesc = @TechnicalDesc,
UserComments = @UserComments,
CorpStdDept = @CorpStdDept,
CorpStdCode = @CorpStdCode,
SimDept = @SimDept,
SimCode = @SimCode
Where LinkNo = @LinkNo
End
Else
Begin
Update osit_wkcorpCdm Set
CorpID = @HospID,
IsItActive = @IsItActive,
HospDesc = @HospDesc,
TechnicalDesc = @TechnicalDesc,
UserComments = @UserComments,
CorpStdDept = @DeptNum,
CorpStdCode = @ProcCode,
SimDept = @SimDept,
SimCode = @SimCode
Where LinkNo = @LinkNo
End
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_PCGetMissingItemDetail]'
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE procedure bpinet_sp_PCGetMissingItemDetail
--This part of the missing items query will be used by both getMissingItems and GetLinkedCode violations
@MissingItemType smallint, --HCPCS = 1; APC = 2
@HospID varchar(10),
@UserId int ,
@OSIPayorCode int,
@APCPassCodeVersion numeric (8,0),
@UseFutureCode int,
@State varchar(3),
@HospLocalityCode varchar (2),
@HospIsSoleProvider smallint,
@IsForLinkedItems smallint
AS
declare @sql nvarchar(4000)
BEGIN
SELECT tmp.Accepted, tmp.Code, tmp.ShortDesc, apc.APCPassCodeStatusCode, tmp.ViolationID,
(CASE WHEN @IsForLinkedItems = 1 THEN tmp.CDMCode ELSE NULL END) AS CDMCode,
(CASE WHEN @MissingItemType = 1 THEN
(CASE WHEN @HospIsSoleProvider = 1 THEN rbs.SoleProviderPrice ELSE rbs.CPTPrice END)
ELSE apcCat.APCNatPaymentRate END) AS CPTPrice
FROM ##TempMissingTable tmp
LEFT JOIN osit_APCPassCodeList apc ON apc.HCPCSCode = tmp.Code
AND apc.APCPassCodeVersion = @APCPassCodeVersion
LEFT JOIN osit_CPTRatesByState rbs ON rbs.CPTCode = tmp.Code
AND rbs.State = @State
AND rbs.MedicareLocCode = @HospLocalityCode
LEFT JOIN osit_APCCatalog apcCat ON apcCat.APCCode = tmp.Code
AND apcCat.APCVersion = @APCPassCodeVersion
ORDER BY (CASE WHEN @IsForLinkedItems = 1 THEN tmp.CDMCode ELSE tmp.code END)
END
--Matthew Share - the payorcode is presently hard-coded to 1 until Tracy gets back
--to me to let me know how they want to handle the overlays when we're doing the backwards
--lookup - we have a CPT and we're trying to find catalog items that are related to it.
BEGIN
SELECT tmp.Code, sca.StdCode, sca.StdDept, sca.BillingDesc30
FROM ##TempMissingTable tmp
INNER JOIN osit_StdCatalogHCPCSListActive hcpcsla ON
tmp.Code = (CASE WHEN @UseFutureCode = 1 THEN hcpcsla.CPTReviewHCPCSCodeFuture ELSE hcpcsla.CPTReviewHCPCSCode END)
AND hcpcsla.OSIPayorCode = 1
LEFT JOIN osit_StdCatalogActive sca ON hcpcsla.StdCode = sca.StdCode
UNION
SELECT tmp.Code, sca.StdCode, sca.StdDept, sca.BillingDesc30
FROM ##TempMissingTable tmp
INNER JOIN osit_StdCatalogHCPCSListActive hcpcsla ON
(CASE WHEN @UseFutureCode = 1 THEN hcpcsla.CPTReviewCPTCodeFuture ELSE hcpcsla.CPTReviewCPTCode END) = tmp.Code
AND hcpcsla.OSIPayorCode = 1
LEFT JOIN osit_StdCatalogActive sca ON hcpcsla.StdCode = sca.StdCode
ORDER BY tmp.code
END
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_compl_AcceptViolation]'
GO
SET ANSI_NULLS OFF
GO
CREATE procedure bpinet_sp_compl_AcceptViolation
@ViolationType smallint,
@HospID varchar(10),
@DeptNum varchar(10),
@ProcCode varchar(10),
@OSIPayorCode smallint,
@IsItCorp smallint,
--@CorpPushDownHosps varchar(256), --A list of corp hosps that you want included
@UserID Numeric(10),
--in the cascading update
@NewValue smallint
AS
Declare @sql nvarchar(1000)
Declare @ViolationTableName varchar(50)
--First, we get the appropriate violation table name from the violationTypeId
exec bpinet_sp_GetViolationTableName @ViolationType, @ViolationTableName Output
--If we're dealing with a corp, accept the related corp items
/*IF (@IsItCorp = 1 AND @CorpPushDownHosps <> '')
BEGIN
select @sql = 'UPDATE ' + @ViolationTableName + '
set IsItClientOverWrite = ' + convert(varchar(5),@NewValue) +
',UserID = ' + convert(varchar(10),@UserID) + ', LastChanged = GetDate()
from ' + @ViolationTableName + ' v ' +
'JOIN osit_wkcdm cdm
ON v.hospid = cdm.hospid
AND v.DeptNum = cdm.DeptNum
and v.ProcCode = cdm.ProcCode
JOIN osit_hospperiodinfo hpi ON
hpi.hospid = cdm.hospid
And hpi.IsItActive = 1
WHERE cdm.CorpStdCatalogId = ''' + @HospId + '''
AND cdm.CorpStdDept = ' + @DeptNum +
' AND cdm.corpStdCode = ''' + @ProcCode + '''
and cdm.isitactive = 1
and cdm.hospid in (' + @CorpPushDownHosps + ')
and cdm.OsiDateStamp = hpi.OsiDateStamp'
print @sql
Exec sp_executeSql @sql
END*/
--This is the standard (non-corp) update statement which we'll want to execute
--whether we're dealing with a corp or not; so it's not included in the @IsItCorp
--if statement scope. On corps, we'll update the related records above and then
--call this. On non-corps, we'll simple call this and skip all that mess above.
select @sql = 'UPDATE ' + @ViolationTableName + '
set IsItClientOverWrite = ' + convert(varchar(5),@NewValue) +
',UserID = ' + convert(varchar(10),@UserID) + ', LastChanged = GetDate()
WHERE HospId = ''' + @HospId + '''
AND DeptNum = ' + @DeptNum +
' AND ProcCode = ''' + @ProcCode + ''''
Declare @HasField tinyint
declare @SelectValue varchar(100)
Execute @HasField = bpinet_sp_ViolationTableSelectValue @ViolationType, 'OSIPayorCode', @SelectValue Output
If (@HasField = 1)
begin
select @sql = @sql + ' AND OSIPayorCode = ' + convert(varchar(5),@OSIPayorCode)
end
--print @sql
Exec sp_executeSql @sql
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_RptPCDuplicateItemsByStdCode]'
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [bpinet_sp_RptPCDuplicateItemsByStdCode]
@HospID varchar(10),
@Accepted smallint,
@OSIDateStamp int,
@IsItCorp smallint,
@UserID int
AS
--select @HospId='CAR62',@Accepted=0,@OSIDateStamp=20040107,@IsItCorp=0,@USerId=-1
CREATE TABLE #DiffPriceStdCodes (
[ViolationID] numeric(18,0),
[LongDesc] varchar(30),
[DeptNum] varchar(10),
[ProcCode] varchar(20),
[ItemDesc] varchar(50),
[Volume] int,
[PrimaryPrice] money,
[PriceType] smallint,
[Accepted] smallint )
IF @UserID <> -1 --Run report on specific departments, in osit_BPISelectedDept
AND @IsItCorp = 1 --Corporate is true
BEGIN
INSERT INTO #DiffPriceStdCodes
SELECT
lst.ViolationID,
osi.BillingDesc30,
cdm.CorpStdDept,
cdm.CorpStdCode,
cdm.HospDesc,
CONVERT(int, (CASE WHEN cdm.AnnualizedVolume IS NULL THEN 0 ELSE cdm.AnnualizedVolume END)) ,
ipp.Price ,
ipp.PriceType,
lst.IsItClientOverwrite
FROM obpt_HospComplViolationListGrp2Item7 lst WITH(NOLOCK)
INNER JOIN osit_wkCorpCDM cdm WITH(NOLOCK)
ON cdm.CorpID = lst.HospID
AND cdm.CorpStdCode = lst.Proccode
AND cdm.CorpStdDept = lst.DeptNum
AND cdm.OSIDateStamp = @OSIDateStamp
AND cdm.IsItActive = 1
LEFT JOIN osit_wkCorpCDMPriceList ipp WITH(NOLOCK)
ON ipp.LinkNo = cdm.LinkNo
AND ipp.PriceID = 1
AND ipp.PriceEffDate = (SELECT MAX(PriceEffDate) FROM osit_wkCorpCDMPriceList WHERE linkno = cdm.linkno AND PriceID = 1 AND PriceEffDate <= getdate())
INNER JOIN osit_StdCatalogActive osi WITH(NOLOCK)
ON cdm.OsiStdCode = osi.StdCode
AND osi.IsItCDMMaster = 1
WHERE lst.HospID = @HospID
AND lst.IsItClientOverwrite = @Accepted
AND lst.DeptNum IN (Select DeptNum from osit_BPISelectedDept WHERE UserID = @UserID)
END
IF @UserID <> -1 --Run report on specific departments, in osit_BPISelectedDept
AND @IsItCorp = 0 --Corporate is false
BEGIN
INSERT INTO #DiffPriceStdCodes
SELECT
lst.ViolationID,
osi.BillingDesc30,
cdm.DeptNum,
cdm.ProcCode,
cdm.HospDesc,
CONVERT(int, (CASE WHEN cdm.AnnualizedVolume IS NULL THEN 0 ELSE cdm.AnnualizedVolume END)) ,
ipp.Price ,
ipp.PriceType,
lst.IsItClientOverwrite
FROM obpt_HospComplViolationListGrp2Item7 lst WITH(NOLOCK)
INNER JOIN osit_wkCDM cdm WITH(NOLOCK)
ON cdm.HospID = lst.HospID
AND cdm.ProcCode = lst.Proccode
AND cdm.DeptNum = lst.DeptNum
AND cdm.OSIDateStamp = @OSIDateStamp
AND cdm.IsItActive = 1
LEFT JOIN osit_wkCDMPriceList ipp WITH(NOLOCK)
ON ipp.LinkNo = cdm.LinkNo
AND ipp.PriceID = 1
AND ipp.PriceEffDate = (SELECT MAX(PriceEffDate) FROM osit_wkCDMPriceList WHERE linkno = cdm.linkno AND PriceID = 1 AND PriceEffDate <= getdate())
INNER JOIN osit_StdCatalogActive osi WITH(NOLOCK)
ON cdm.OsiStdCode = osi.StdCode
AND osi.IsItCDMMaster = 1
WHERE lst.HospID = @HospID
AND lst.IsItClientOverwrite = @Accepted
AND lst.DeptNum IN (Select DeptNum from osit_BPISelectedDept WHERE UserID = @UserID)
END
IF @UserID = -1 --Run report on all departments
AND @IsItCorp = 1 --Corporate is true
BEGIN
INSERT INTO #DiffPriceStdCodes
SELECT
lst.ViolationID,
osi.BillingDesc30,
cdm.CorpStdDept,
cdm.CorpStdCode,
cdm.HospDesc,
CONVERT(int, (CASE WHEN cdm.AnnualizedVolume IS NULL THEN 0 ELSE cdm.AnnualizedVolume END)) ,
ipp.Price ,
ipp.PriceType,
lst.IsItClientOverwrite
FROM obpt_HospComplViolationListGrp2Item7 lst WITH(NOLOCK)
INNER JOIN osit_wkCorpCDM cdm WITH(NOLOCK)
ON cdm.CorpID = lst.HospID
AND cdm.CorpStdCode = lst.Proccode
AND cdm.CorpStdDept = lst.DeptNum
AND cdm.OSIDateStamp = @OSIDateStamp
AND cdm.IsItActive = 1
LEFT JOIN osit_wkCorpCDMPriceList ipp WITH(NOLOCK)
ON ipp.LinkNo = cdm.LinkNo
AND ipp.PriceID = 1
AND ipp.PriceEffDate = (SELECT MAX(PriceEffDate) FROM osit_wkCorpCDMPriceList WHERE linkno = cdm.linkno AND PriceID = 1 AND PriceEffDate <= getdate())
INNER JOIN osit_StdCatalogActive osi WITH(NOLOCK)
ON cdm.OsiStdCode = osi.StdCode
AND osi.IsItCDMMaster = 1
WHERE lst.HospID = @HospID
AND lst.IsItClientOverwrite = @Accepted
END
IF @UserID = -1 --Run report on all departments
AND @IsItCorp = 0 --Corporate is false
BEGIN
INSERT INTO #DiffPriceStdCodes
SELECT
lst.ViolationID,
osi.BillingDesc30,
cdm.DeptNum,
cdm.ProcCode,
cdm.HospDesc,
CONVERT(int, (CASE WHEN cdm.AnnualizedVolume IS NULL THEN 0 ELSE cdm.AnnualizedVolume END)) ,
ipp.Price ,
ipp.PriceType,
lst.IsItClientOverwrite
FROM obpt_HospComplViolationListGrp2Item7 lst WITH(NOLOCK)
INNER JOIN osit_wkCDM cdm WITH(NOLOCK)
ON cdm.HospID = lst.HospID
AND cdm.ProcCode = lst.Proccode
AND cdm.DeptNum = lst.DeptNum
AND cdm.OSIDateStamp = @OSIDateStamp
AND cdm.IsItActive = 1
LEFT JOIN osit_wkCDMPriceList ipp WITH(NOLOCK)
ON ipp.LinkNo = cdm.LinkNo
AND ipp.PriceID = 1
AND ipp.PriceEffDate = (SELECT MAX(PriceEffDate) FROM osit_wkCDMPriceList WHERE linkno = cdm.linkno AND PriceID = 1 AND PriceEffDate <= getdate())
INNER JOIN osit_StdCatalogActive osi WITH(NOLOCK)
ON cdm.OsiStdCode = osi.StdCode
AND osi.IsItCDMMaster = 1
WHERE lst.HospID = @HospID
AND lst.IsItClientOverwrite = @Accepted
END
SELECT
ViolationID,
[LongDesc] AS [DuplicateItem],
[DeptNum],
[ProcCode],
[ItemDesc],
[Volume],
[PrimaryPrice],
[PriceType],
[Accepted]
FROM
#DiffPriceStdCodes
WHERE
((([LongDesc]) IN
(SELECT [LongDesc]
FROM [#DiffPriceStdCodes] AS Tmp
GROUP BY [LongDesc]
HAVING Count(*)>1 )))
ORDER BY
[LongDesc],
[DeptNum],
[ProcCode]
DROP TABLE #DiffPriceStdCodes
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_UBUpdateHistoryWk]'
GO
create procedure bpinet_sp_UBUpdateHistoryWk
@TransNo Numeric(10),
@IsItCorp SmallInt,
@ID uniqueidentifier,
@LinkNo numeric(10) ,
@PayorCode VarChar(10),
@EffDate datetime,
@Code VarChar(10),
@DeleteFlag SmallInt
as
--**********************************************************************************************************************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Update UB Table History
IF @IsITCorp = 0
Begin
Insert Into osit_wkCdmUbListHistory
(TransNo, UBID, LinkNo, HospPayorCode, UBEffDate, UBCode, DeleteFlag)
Values
(@TransNo, @ID, @LinkNo, @PayorCode, @EffDate, @Code, @DeleteFlag)
End
Else
Begin
Insert Into osit_wkcorpCdmUbListHistory
(TransNo, UBID, LinkNo, HospPayorCode, UBEffDate, UBCode, DeleteFlag)
Values
(@TransNo, @ID, @LinkNo, @PayorCode, @EffDate, @Code, @DeleteFlag)
End
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_UBUpdateHistory]'
GO
create procedure bpinet_sp_UBUpdateHistory
@TransNo Numeric(10),
@IsItCorp SmallInt,
@ID uniqueidentifier,
@LinkNo numeric(10) ,
@PayorCode VarChar(10),
@EffDate datetime,
@Code VarChar(10),
@DeleteFlag SmallInt
as
--**********************************************************************************************************************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Update UB Table History
IF @IsITCorp = 0
Begin
Insert Into osit_wkCdmUbListHistory
(TransNo, UBID, LinkNo, HospPayorCode, UBEffDate, UBCode, DeleteFlag)
Values
(@TransNo, @ID, @LinkNo, @PayorCode, @EffDate, @Code, @DeleteFlag)
End
Else
Begin
Insert Into osit_wkcorpCdmUbListHistory
(TransNo, UBID, LinkNo, HospPayorCode, UBEffDate, UBCode, DeleteFlag)
Values
(@TransNo, @ID, @LinkNo, @PayorCode, @EffDate, @Code, @DeleteFlag)
End
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BPINet_sp_ValPCMedNonCovered]'
GO
Potential Medicare Non-Covered and Non-Billable Items Found
CREATE procedure BPINet_sp_ValPCMedNonCovered
@HospID varchar(10),
@OSIDateStamp varchar(8),
@MedCode smallint,
@GroupItem numeric(10,0),
@OSIPayor smallint,
@UserId numeric(9,0),
@IsItCorp smallint,
@numRowAffected numeric(8,0) output
as
--**********************************************************************************************************************
--
--**********************************************************************************************************************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
DECLARE @Err NUMERIC(8,0)
-- This procedure had ComplChkGrpCode = 3 and ComplChkGrpItem = @GroupItem
--DECLARE @HospID varchar(10)
--DECLARE @OSIDateStamp varchar(8)
--DECLARE @MedCode smallint
--DECLARE @GroupItem numeric(10,0)
--DECLARE @OSIPayor smallint
--DECLARE @UserId numeric(9,0)
--DECLARE @IsItCorp smallint
--DECLARE @numRowAffected numeric(8,0)
--SET @HospID = 'CAR58'
--SET @OSIDateStamp = 20020920
--SET @MedCode = 1
--SET @GroupItem = 1
--SET @OSIPayor = 1
--SET @UserId = 9030
--SET @IsItCorp = 1
--SET @numRowAffected = 0
DECLARE @Sql varchar(500)
DECLARE @SqlCond varchar(500)
--delete the existing records
SET @SQL = 'DELETE FROM obpt_HospComplViolationListGrp3Item' + cast(@GroupItem as varchar(2)) +
' WHERE HospID = ''' + @HospID + ''' AND IsItClientOverwrite = 0
/*
Script created by SQL Compare from Red Gate Software Ltd at 10/22/2004 10:08:44 PM
Run this script on USATLOSIPRODDB4.BPIDBBase to make it the same as ATLNRSQADB1.BPIDB2QA
Please back up your database before running this script
*/
SET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
PRINT N'Disabling full text indexing'
GO
sp_fulltext_database N'disable'
GO
BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[bpinet_sp_ViolationTableSelectValue]'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
Create Procedure dbo.bpinet_sp_ViolationTableSelectValue
@ViolationTableId int,
@FieldName varchar(100),
@SelectValue varchar(50) Output
AS
BEGIN
--First, we need to derrive the violation table name based
--on the violationId
/*declare @ViolationTableId int
declare @FieldName varchar(100)
declare @HasField bit
select @ViolationTableId = 28
select @FieldName = 'LinkedCode'
*/
declare @HasField bit
declare @strSql nvarchar(1000)
declare @GrpCode int
declare @GrpItem int
SELECT @strSql = N'Select @GrpCode = ComplChkGrpCode, @GrpItem = ComplChkGrpItem FROM obpt_ComplChkGrpItemInfo WHERE ComplChkId = ' + convert(varchar(10), @ViolationTableId)
EXEC sp_executesql @strSql,N'@GrpCode int OUTPUT, @GrpItem int OUTPUT',@GrpCode OUTPUT, @GrpItem OUTPUT
--Now that we know which table we're looking at, we can hit the systables
--to find out if that table has the field we're looking for.
declare @strTableName varchar(50)
Select @strTableName = 'obpt_HospComplViolationListGrp' + convert(nvarchar,@GrpCode) + 'Item' + Convert(nvarchar,@GrpItem)
select @strSql = N'select @HasField = count(*) from syscolumns ' +
'join sysobjects on sysobjects.id = syscolumns.id ' +
'where sysobjects.name = ''' + @strTableName + '''' +
'AND sysColumns.name = ''' + @fieldName + ''''
EXEC sp_executesql @strSql,N'@HasField bit OUTPUT',@HasField OUTPUT
--print @FieldName + ' - ' + convert(varchar(50),@HasField)
if @HasField = 1
begin
Select @SelectValue = @FieldName
end
ELSE
Begin
Select @SelectValue = 'null AS ' + @FieldName
End
--select @strSql = N'Select * from obpt_ComplViolationListGrp' + convert(nvarchar,@GrpCode) + 'Item' + Convert(nvarchar,@GrpItem)
--print @HasField
--print @strSql
RETURN @HasField
END
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BPINet_sp_DropTempTable]'
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE Procedure BPINet_sp_DropTempTable(@TableName VarChar(50)) AS
declare @sql nvarchar(200)
if object_id('tempdb..' + @TableName) > 0
begin
print 'dropping table ' + @TableName
select @sql = 'drop table ' + @TableName
Exec sp_executesql @sql
end
/*
bpinet_sp_droptemptable '#Temp1'
bpinet_sp_droptemptable '#temp2'
Create Table #Temp1 (Code VarChar(10))
Create Table #Temp2 (Code VarChar(10), StdDept Numeric(10,0), Status SmallInt)
*/
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_CIUpdateHistory]'
GO
CREATE procedure bpinet_sp_CIUpdateHistory
@TransNo Numeric(10),
@IsItCorp SmallInt,
@LinkNo numeric(10) ,
@HospID VarChar(10),
@DeptNum VarChar(10),
@ProcCode VarChar(20),
@IsItActive smallint,
@HospDesc VarChar(50),
@TechnicalDesc Varchar(50),
@UserComments VarChar(256),
@CorpStdDept VarChar(18),
@CorpStdCode VarChar(20),
@SimDept VarChar(3),
@SimCode VarChar(10)
as
--**********************************************************************************************************************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Update UB Table History
if isnumeric(@DeptNum)=0 and upper(@DeptNum) <> 'NULL' Set @DeptNum='0'
if isnumeric(@CorpStdDept)=0 and upper(@CorpStdDept) <> 'NULL' Set @CorpStdDept='0'
IF @IsITCorp = 0
Begin
Insert Into osit_wkCdmHistory
(TransNo, LinkNo, HospID, DeptNum, ProcCode,
IsItActive,HospDesc, TechnicalDesc, UserComments,
CorpStdDept, CorpStdCode, SimDept, SimCode)
Values
(@TransNo, @LinkNo, @HospID, @DeptNum, @ProcCode,
@IsItActive,@HospDesc, @TechnicalDesc, @UserComments,
@CorpStdDept, @CorpStdCode, @SimDept, @SimCode)
End
Else
Begin
Insert Into osit_wkcorpCdmHistory
(TransNo, LinkNo, CorpID,
IsItActive,HospDesc, TechnicalDesc, UserComments,
CorpStdDept, CorpStdCode, SimDept, SimCode)
Values
(@TransNo, @LinkNo, @HospID,
@IsItActive,@HospDesc, @TechnicalDesc, @UserComments,
@CorpStdDept, @CorpStdCode, @SimDept, @SimCode)
End
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_GetViolationTableName]'
GO
SET QUOTED_IDENTIFIER OFF
GO
/****** Object: Stored Procedure dbo.SP_GetCounterValue Script Date: 7/25/99 1:46:35 AM ******/
Create PROCEDURE bpinet_sp_GetViolationTableName
@ViolationTableId int,
@strTableName varchar(50) Output
AS
begin
declare @strSql nvarchar(1000)
declare @GrpCode int
declare @GrpItem int
SELECT @strSql = N'Select @GrpCode = ComplChkGrpCode, @GrpItem = ComplChkGrpItem FROM obpt_ComplChkGrpItemInfo WHERE ComplChkId = ' + convert(varchar(10), @ViolationTableId)
EXEC sp_executesql @strSql,N'@GrpCode int OUTPUT, @GrpItem int OUTPUT',@GrpCode OUTPUT, @GrpItem OUTPUT
Select @strTableName = 'obpt_HospComplViolationListGrp' + convert(nvarchar,@GrpCode) + 'Item' + Convert(nvarchar,@GrpItem)
end
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_RptPCComplianceSummary]'
GO
CREATE PROCEDURE [bpinet_sp_RptPCComplianceSummary]
@HospID varchar(10),
@Accepted smallint,
@HCPCSPayor varchar(5),
@UBPayor varchar(5),
@MedHCPCSPayor varchar(5),
@MedUBPayor varchar(5),
@DefFiscInt int,
@OSIPayor smallint,
@DeptNums varchar(256) ,
@OSIDateStamp int,
@HCPCSTOUBVersion int,
@IsItCorp smallint,
@UserID int,
@ViolationOnly smallint,
@FirstRun smallint,
@Future smallint
/*
@OSIPayors varchar(256),
*/
AS
--select @HospID='BH31',@UserID=9138,@HCPCSPayor='C',@UBPayor='U',@MedHCPCSPayor='C',@MedUBPayor='U',@DefFiscInt=45,@OSIPayor=45,@DeptNums='',@OSIDateStamp=20040130,@HCPCSTOUBVersion=20040101,@ViolationOnly=1,@IsItCorp=0,@Accepted=0,@FirstRun=1,@Future=0
/*Select @HospID='BH31',@UserID=9138,@HCPCSPayor='C',
@UBPayor='U',@MedHCPCSPayor='C',@MedUBPayor='U',@DefFiscInt=45,@OSIPayor=45,@DeptNums='',
@OSIDateStamp=20040130,@HCPCSTOUBVersion=20040101,@ViolationOnly=1,@IsItCorp=0,
@Accepted=0,@FirstRun=1,@Future=0
*/
DECLARE
@OsiPayorCode varchar (5)
/*First, parse the list of reportTypes and iterate through a cursor
to add data to the temp table for each type of violation
*/
--Note that reports 23,24,25,26,27,28 and 32 are not used
DECLARE ReportTypeCursor Cursor FOR
SELECT * from func_splitArray('1,2,4,5,6,7,8,10,11,12,13,14,15,16,17,18,19,20,21,22,29,30,31,33,34')
DECLARE @CurrentReport varchar(100)
DECLARE @sql varchar (5000)
DECLARE @SELECTValue varchar(100)
DECLARE @CurrentTable varchar(100)
CREATE TABLE #ComplianceSummary (
[HospId] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeptNum] [varchar](18) NULL ,
[ProcCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OSIPayorCode] [smallint] NULL,
[CPTHCPCSPayor] [varchar] (5) NULL,
[UBPayor] [varchar] (5) NULL,
[PayorOrder] [smallint] NOT NULL,
[IsItClientOverwrite] [int] NULL ,
[UserId] [numeric](18, 0),
[ViolationType] [smallint] NULL,
[ViolationGroup] [smallint] NULL,
[ViolationGroupItem] [smallint] NULL,
[ViolationId] [numeric] (18,0) NOT NULL
)
OPEN ReportTypeCursor
FETCH NEXT FROM ReportTypeCursor
INTO @CurrentReport
@FETCH_STATUS to see if there are any more rows to fetch.
--Declare @LogStart datetime
@FETCH_STATUS = 0
BEGIN
--Set @LogStart = getdate()
--If the current table doesn't have the field we're looking for
--dummy it up, otherwise SELECT the actual value
--PRINT 'getting data for violationtype ' + @CurrentReport
exec bpinet_sp_GetViolationTableName @CurrentReport, @CurrentTable Output
SELECT @sql = 'INSERT INTO #ComplianceSummary'
SELECT @sql = @sql + '(ViolationType,ViolationId,HospId,DeptNum,ProcCode,OSIPayorCode,CPTHCPCSPayor,UBPayor,PayorOrder,IsItClientOverwrite,
UserId,ViolationGroup, ViolationGroupItem)'
--sp_ViolationTableSELECTValue determines if column exists in current table
SELECT @sql = @sql + ' SELECT ' + convert(varchar(3),@CurrentReport) + ' AS ViolationType, '
SELECT @sql = @sql + 'ViolationId, '
exec bpinet_sp_ViolationTableSelectValue @CurrentReport,'HospId',@SELECTValue Output
SELECT @sql = @sql + @SELECTValue + ','
exec bpinet_sp_ViolationTableSelectValue @CurrentReport,'DeptNum',@SELECTValue Output
SELECT @sql = @sql + @CurrentTable + '.' + @SELECTValue + ','
exec bpinet_sp_ViolationTableSelectValue @CurrentReport,'ProcCode',@SELECTValue Output
SELECT @sql = @sql + @SELECTValue + ','
exec bpinet_sp_ViolationTableSelectValue @CurrentReport,'OSIPayorCode',@SELECTValue Output
SELECT @sql = @sql + @SELECTValue + ','
If @CurrentReport in ( 1,4,5,7,8,9,11)
BEGIN
exec bpinet_sp_ViolationTableSelectValue @CurrentReport,'HospHCPCSPayorCode',@SELECTValue Output
SELECT @sql = @sql + @SELECTValue + ','
SELECT @sql = @sql + 'NULL,1,'
END
If @CurrentReport in (2,3,6) -- = 2 OR @CurrentReport = 3 OR @CurrentReport = 6
BEGIN
exec bpinet_sp_ViolationTableSelectValue @CurrentReport,'HospHCPCSPayorCode',@SELECTValue Output
SELECT @sql = @sql + @SELECTValue + ','
exec bpinet_sp_ViolationTableSelectValue @CurrentReport,'HospUBPayorCode',@SELECTValue Output
SELECT @sql = @sql + @SELECTValue + ','
SELECT @sql = @sql + '3,'
END
If @CurrentReport = 10 OR @CurrentReport > 11
BEGIN
SELECT @sql = @sql + 'NULL,NULL,0,'
END
exec bpinet_sp_ViolationTableSelectValue @CurrentReport,'IsItClientOverwrite',@SELECTValue Output
SELECT @sql = @sql + @SELECTValue + ','
exec bpinet_sp_ViolationTableSelectValue @CurrentReport,'UserId',@SELECTValue Output
SELECT @sql = @sql + @CurrentTable + '.' + @SELECTValue + ','
--obtain the group number for the current violationType
DECLARE @GrpCode int
DECLARE @GrpItem int
DECLARE @strGroupNumSql nvarchar(1000)
SELECT @strGroupNumSql = N'SELECT @GrpCode = ComplChkGrpCode, @GrpItem = ComplChkGrpItem FROM obpt_ComplChkGrpItemInfo WHERE ComplChkId = ' + convert(varchar(10), @CurrentReport)
EXEC sp_executesql @strGroupNumSql,N'@GrpCode int OUTPUT, @GrpItem int OUTPUT',@GrpCode OUTPUT, @GrpItem OUTPUT
Select @sql = @sql + convert(varchar(3),@GrpCode) + ' AS ViolationGroup, '
Select @sql = @sql + convert(varchar(3),@GrpItem) + ' AS ViolationGroupItem'
--If the table we're hitting currently doesn't have a deptnum field
--or if a dept filter was not specified, don't add one
--we'll check for "null" in @selectvalue AS a cheezy way of seeing if the deptnum
--field exists
exec bpinet_sp_ViolationTableSelectValue @CurrentReport,'DeptNum',@SelectValue Output
select @sql = @sql + ' FROM ' + @CurrentTable + ' WHERE HospId = ''' + @HospId + ''''
if charindex('null',@SelectValue) = 0 AND @DeptNums <> ''
begin
Select @sql = @sql + ' AND DeptNum IN (' + @DeptNums + ') '
end
SELECT @sql = @sql + ' AND CONVERT(varchar(3), IsItClientOverWrite) = ''' + CONVERT(varchar(3), @Accepted) + ''''
--print @sql
-- Exec sp_executeSql @sql
Exec(@sql)
-- log times for each pass
-- Insert Into osit_PerfMonitor (LogTime, StartTime, EndTime, ElapsedTime, UserID, Type, AddInfo, UserLogin)
-- Values(getdate(),'','',datediff(second,@LogStart,getdate()),@UserID,'BPINet Test LoadTemp1',@sql,'n/a')
-- This is executed AS long AS the previous fetch succeeds.
FETCH NEXT FROM ReportTypeCursor
INTO @CurrentReport
END
CLOSE ReportTypeCursor
DEALLOCATE ReportTypeCursor
UPDATE #ComplianceSummary
SET OSIPayorCode = 1, PayorOrder = 0
WHERE OSIPayorCode Is Null
--finally, now that we have all of the data we need in the temp table.
--we can query the temp table and sort it and move it to another temp table that has
--the rownum field.
--print '#2'
--Set @LogStart = getdate()
CREATE TABLE #ComplianceSummary2(
[RowNum] [int] IDENTITY (1, 1) NOT NULL ,
[ViolationId] [numeric] (18,0) NOT NULL,
[HospId] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DeptNum] [varchar](18) NULL ,
[ProcCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[OSIPayorCode] [smallint] NULL,
[CPTHCPCSPayor] [varchar] (5) NULL,
[UBPayor] [varchar] (5) NULL,
[PayorOrder] [smallint] NOT NULL,
[HOSPDescription] [varchar] (50) Null,
[IsItClientOverwrite] [int] NULL ,
[UserId] [numeric](18, 0),
[ViolationType] [smallint] NULL,
[ViolationGroup] [smallint] NULL,
[ViolationGroupItem] [smallint] NULL,
[GroupDescription] [varchar] (100) NULL,
[ITEMDescription] [varchar] (100) NULL
PRIMARY KEY CLUSTERED
(
[RowNum]
) ON [PRIMARY]
) ON [PRIMARY]
SELECT @sql = 'Insert into #ComplianceSummary2' +
'(ViolationId,HospId, DeptNum, ProcCode, OSIPayorCode, CPTHCPCSPayor, UBPayor, PayorOrder, IsItClientOverwrite, UserId, ViolationType,ViolationGroup,ViolationGroupItem,GroupDescription, ItemDescription, HospDescription)' +
' SELECT v.ViolationId,v.HospId, v.DeptNum, v.ProcCode, v.OSIPayorCode, v.CPTHCPCSPayor, v.UBPayor, v.PayorOrder, v.IsItClientOverwrite, v.UserId, v.ViolationType, v.ViolationGroup,v.ViolationGroupItem,
grpInfo.DisplayDescription AS GroupDescription, itemInfo.DisplayDescription AS ItemDescription, cdm.HospDesc AS HospDescription
from #ComplianceSummary' + ' v Left JOIN
obpt_ComplChkGrpInfo grpInfo ON grpInfo.ComplChkGrpCode = v.ViolationGroup
Left JOIN obpt_ComplChkGrpItemInfo itemInfo ON itemInfo.ComplChkId = v.ViolationType
Left JOIN '
if @IsItCorp = 1
BEGIN
SELECT @sql = @sql + 'osit_wkcorpcdm cdm ON cdm.CorpId = v.HospId AND cdm.CorpStdDept = v.DeptNum AND cdm.CorpStdCode = v.ProcCode '
END
else
BEGIN
SELECT @sql = @sql + 'osit_wkcdm cdm ON cdm.HospId = v.HospId AND cdm.deptNum = v.DeptNum AND cdm.ProcCode = v.ProcCode 'END
SELECT @sql = @sql + ' Order by v.DeptNum, v.ProcCode'
PRINT @sql
--execute sp_executesql @sql
exec (@sql)
-- log times for each pass
-- Insert Into osit_PerfMonitor (LogTime, StartTime, EndTime, ElapsedTime, UserID, Type, AddInfo, UserLogin)
-- Values(getdate(),'','',datediff(second,@LogStart,getdate()),@UserID,'BPINet Test LoadTemp2',@sql,'n/a')
-- Set @LogStart = getdate() --reset for next log
--select * from #compliancesummary2 ORDER BY DeptNum, ProcCode
/*
list of aliases:
cdm: osit_wkCDM
CVList: #compliancesummary2
CCGII: compliancecheckgrpiteminfo
hlist: osiv_wkHCPCS
ulist: osiv_wkUB
hMedlist: osit_wkHCPCS
uMedlist: osit_wkUB
osi: stdcatactive
ohlist: stdcatHCPCSactive
osi1: stdcatUBactive
dept: osit_deptinfo
p1: osit_wkcdmpricelist
HCPCSPayor: osit_hosppayorlist
UBPayor: osit_hosppayorlist
OSIPayor: osit_osipayorlist
*/
--print '#3'
IF @FirstRun = 1
BEGIN
declare @UserTable varchar(30)
select @UserTable = 'ComplianceSummary' + convert(varchar(10),@UserId)
if object_id(@UserTable) > 0
begin
--print 'dropping existing unsorted temp table'
select @sql = 'drop table ' + @UserTable
--Exec sp_executesql @sql
exec(@sql)
end
select @sql = 'CREATE TABLE ' + @UserTable + ' ('
SELECT @sql = @sql + '[DeptNum] [varchar](18) NULL ,'
SELECT @sql = @sql + ' [ProcCode] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,'
SELECT @sql = @sql + ' [ItemDesc] [varchar] (50) Null,'
SELECT @sql = @sql + ' [ItemViolated] [varchar] (100) NULL,'
SELECT @sql = @sql + ' [Department] [varchar] (50),'
SELECT @sql = @sql + ' [Volume] [int],'
SELECT @sql = @sql + ' [PrimaryPrice] [money],'
SELECT @sql = @sql + ' [DefRevCode] [varchar] (10), '
SELECT @sql = @sql + ' [MedRevCode] [varchar] (10), '
SELECT @sql = @sql + ' [DefCPTHCPCSCode] [varchar] (10), '
SELECT @sql = @sql + ' [MedCPTHCPCSCode] [varchar] (10), '
SELECT @sql = @sql + ' [BPIRevCode] [varchar] (10), '
SELECT @sql = @sql + ' [BPICPTCode] [varchar] (7), '
-- SELECT @sql = @sql + ' [BPIHCPCSCode] [varchar] (7), '
SELECT @sql = @sql + ' [UserComments] [varchar] (255), '
SELECT @sql = @sql + ' [BPIComments] [varchar] (3000), '
SELECT @sql = @sql + ' [ComplNum] [varchar] (20), '
SELECT @sql = @sql + ' [OSIPayorDesc] [varchar] (80), '
SELECT @sql = @sql + ' [HCPCSPayorDesc] [varchar] (80), '
SELECT @sql = @sql + ' [UBPayorDesc] [varchar] (80), '
SELECT @sql = @sql + ' [PayorOrder] [smallint],'
SELECT @sql = @sql + ' [OSIPayorCode] [smallint],'
SELECT @sql = @sql + ' [SugRevCodes] [varchar] (2000) )'
END
--execute sp_executesql @sql
exec (@sql)
-- log times for each pass
-- Insert Into osit_PerfMonitor (LogTime, StartTime, EndTime, ElapsedTime, UserID, Type, AddInfo, UserLogin)
-- Values(getdate(),'','',datediff(second,@LogStart,getdate()),@UserID,'BPINet Test LoadTemp3',@sql,'n/a')
-- Set @LogStart = getdate() --reset for next log
SELECT @sql = 'Insert into ' + @UserTable + ' '
SELECT @sql = @sql + 'SELECT CONVERT(varchar(10), CVList.DeptNum) AS [DeptNum], '
SELECT @sql = @sql + 'CVList.ProcCode AS [ProcCode], '
SELECT @sql = @sql + 'CVList.HOSPDescription AS [ItemDesc], '
SELECT @sql = @sql + 'CVList.ITEMDescription AS [ItemViolated], '
SELECT @sql = @sql + 'dept.stddeptdesc AS [Department], '
SELECT @sql = @sql + 'CONVERT(int, cdm.annualizedVolume) AS Volume, '
SELECT @sql = @sql + 'p1.price AS [PrimaryPrice], '
SELECT @sql = @sql + 'CONVERT(varchar(10), ulist.ubcode) AS [DefRevCode], '
SELECT @sql = @sql + 'CONVERT(varchar(10), uMedlist.ubcode) AS [MedRevCode], '
SELECT @sql = @sql + '(CASE WHEN hlist.HCPCSCode IS NULL THEN " " ELSE hlist.HCPCSCode END) AS [DefCPTHCPCSCode], '
SELECT @sql = @sql + '(CASE WHEN hMedlist.HCPCSCode IS NULL THEN " " ELSE hMedlist.HCPCSCode END) AS [MedCPTHCPCSCode], '
SELECT @sql = @sql + 'CONVERT(varchar(10), osi1.UBCode) AS [BPIRevCode], '
IF @Future = 1
BEGIN
SELECT @sql = @sql + '(CASE WHEN ohlist.CPTReviewCPTCodeFuture IS NOT NULL THEN ohlist.CPTReviewCPTCodeFuture '
SELECT @sql = @sql + 'ELSE ohlist.CPTReviewHCPCSCodeFuture END) AS [BPICPTCode], '
END
ELSE
BEGIN
SELECT @sql = @sql + '(CASE WHEN ohlist.CPTReviewCPTCode IS NOT NULL THEN ohlist.CPTReviewCPTCode '
SELECT @sql = @sql + 'ELSE ohlist.CPTReviewHCPCSCode END) AS [BPICPTCode], '
END
--SELECT @sql = @sql + '(CASE WHEN ' + @Future + '= 1 THEN '
--SELECT @sql = @sql + '(CASE WHEN ohlist.CPTReviewCPTCodeFuture IS NOT NULL THEN ohlist.CPTReviewCPTCodeFuture '
--SELECT @sql = @sql + 'ELSE ohlist.CPTReviewHCPCSCodeFuture END) ELSE '
--SELECT @sql = @sql + '(CASE WHEN ohlist.CPTReviewCPTCode IS NOT NULL THEN ohlist.CPTReviewCPTCode '
--SELECT @sql = @sql + 'ELSE ohlist.CPTReviewHCPCSCode END) END) AS [BPICPTCode], '
SELECT @sql = @sql + 'cdm.UserComments AS [UserComments], '
SELECT @sql = @sql + 'ohlist.Comments AS [BPIComments], '
SELECT @sql = @sql + 'convert(varchar(7), CCGII.ComplChkGrpCode) + " " + convert(varchar(7), CCGII.ComplChkGrpItem) AS ComplNum, '
SELECT @sql = @sql + '(CASE WHEN OSIPayor.osipayordesc IS NULL THEN "Payor Non-Applicable" ELSE OSIPayor.osipayordesc END) AS OSIPayorDesc, '
SELECT @sql = @sql + '(CASE WHEN HCPCSPayor.HospPayorDesc IS NULL THEN " " ELSE HCPCSPayor.HospPayorDesc END) AS HCPCSPayorDesc, '
SELECT @sql = @sql + '(CASE WHEN UBPayor.HospPayorDesc IS NULL THEN " " ELSE UBPayor.HospPayorDesc END) AS UBPayorDesc, '
SELECT @sql = @sql + 'CVList.PayorOrder, '
SELECT @sql = @sql + 'CVList.OSIPayorCode, '
SELECT @sql = @sql + '" "'
IF @IsItCorp = 0
BEGIN
SELECT @sql = @sql + ' FROM osit_wkCDM cdm WITH(NOLOCK) '
END
ELSE
BEGIN
SELECT @sql = @sql + ' FROM osit_wkCorpCDM cdm WITH(NOLOCK) '
END
IF @ViolationOnly = 0
BEGIN
SELECT @sql = @sql + ' LEFT JOIN #ComplianceSummary2 CVList WITH(NOLOCK) '
END
ELSE
BEGIN
SELECT @sql = @sql + ' INNER JOIN #ComplianceSummary2 CVList WITH(NOLOCK) '
END
IF @IsItCorp = 0
BEGIN
SELECT @sql = @sql + ' ON cdm.ProcCode = CVList.ProcCode '
SELECT @sql = @sql + ' AND cdm.DeptNum = CVList.DeptNum '
SELECT @sql = @sql + ' AND cdm.HospID = CVList.HospID '
END
ELSE
BEGIN
SELECT @sql = @sql + ' ON cdm.CorpStdCode = CVList.Proccode '
SELECT @sql = @sql + ' AND cdm.CorpStdDept = CVList.deptNum '
SELECT @sql = @sql + ' AND cdm.CorpID = CVList.hospid '
END
--added zero to IN in order to capture info from violation tables w/o OSIPayorCodes
--SELECT @sql = @sql + ' AND CONVERT(varchar(10), cvlist.OSIPayorCode) IN (0, ' + @OSIPayors + ') ) '
SELECT @sql = @sql + ' AND CONVERT(varchar(10), cvlist.OSIPayorCode) = ''' + CONVERT(varchar(10), @OSIPayor) + ''''
SELECT @sql = @sql + ' LEFT JOIN obpt_ComplChkGrpItemInfo CCGII WITH(NOLOCK) '
SELECT @sql = @sql + ' ON CCGII.ComplChkGrpCode = CVList.ViolationGroup '
SELECT @sql = @sql + ' AND CCGII.ComplChkGrpItem = CVList.ViolationGroupItem '
IF @IsItCorp = 0
BEGIN
SELECT @sql = @sql + ' LEFT JOIN osit_wkCDMHcpcsList hlist WITH(NOLOCK) '
SELECT @sql = @sql + ' ON cdm.linkno = hlist.linkno '
SELECT @sql = @sql + ' AND hlist.hosppayorcode = dbo.FUNC_GetPCCPTPayorv2Wk(cdm.LinkNo, ''' + @HCPCSPayor + ''', ''' + @HospID + ''')'
SELECT @sql = @sql + ' AND HCPCSEffDate = (SELECT MAX(HCPCSEffDate) FROM osit_WkCDMHcpcsList WHERE Linkno = CDM.LinkNo AND hosppayorcode = hlist.HospPayorCode AND HCPCSEffDate <= GetDate())'
END
ELSE
BEGIN
SELECT @sql = @sql + ' LEFT JOIN osit_wkCorpCDMHcpcsList hlist WITH(NOLOCK) '
SELECT @sql = @sql + ' ON cdm.linkno = hlist.linkno '
SELECT @sql = @sql + ' AND hlist.hosppayorcode = dbo.FUNC_GetPCCPTPayorv2Corp(cdm.LinkNo, ''' + @HCPCSPayor + ''', ''' + @HospID + ''')'
SELECT @sql = @sql + ' AND HCPCSEffDate = (SELECT MAX(HCPCSEffDate) FROM osit_WkCorpCDMHcpcsList WHERE Linkno = CDM.LinkNo AND HospPayorCode = hlist.HospPayorCode AND HCPCSEffDate <= GetDate())'
END
IF @IsItCorp = 0
BEGIN
SELECT @sql = @sql + ' LEFT JOIN osit_wkCDMUBList ulist WITH(NOLOCK) '
SELECT @sql = @sql + ' ON cdm.linkno = ulist.linkno '
SELECT @sql = @sql + ' AND ulist.hosppayorcode = dbo.FUNC_GetPCUBPayorv2Wk(cdm.LinkNo, ''' + @UBPayor + ''', ''' + @HospID + ''')'
SELECT @sql = @sql + ' AND UBEffDate = (SELECT MAX(UBEffDate) FROM osit_WkCDMUBList WHERE Linkno = CDM.LinkNo AND HospPayorCode = ulist.HospPayorCode AND UBEffDate <= GetDate())'
END
ELSE
BEGIN
SELECT @sql = @sql + ' LEFT JOIN osit_wkCorpCDMUBList ulist WITH(NOLOCK) '
SELECT @sql = @sql + ' ON cdm.linkno = ulist.linkno '
SELECT @sql = @sql + ' AND ulist.hosppayorcode = dbo.FUNC_GetPCUBPayorv2Corp(cdm.LinkNo, ''' +@UBPayor + ''', ''' + @HospID + ''')'
SELECT @sql = @sql + ' AND UBEffDate = (SELECT MAX(UBEffDate) FROM osit_WkCorpCDMUBList WHERE Linkno = CDM.LinkNo AND HospPayorCode = ulist.HospPayorCode AND UBEffDate <= GetDate())'
END
IF @IsItCorp = 0
BEGIN
SELECT @sql = @sql + ' LEFT JOIN osit_wkCDMHCPCSList hMedlist WITH(NOLOCK) '
SELECT @sql = @sql + ' ON cdm.linkno = hMedlist.linkno '
SELECT @sql = @sql + ' AND hMedlist.hosppayorcode = ''' + @MedHCPCSPayor + ''''
SELECT @sql = @sql + ' AND hMedList.HCPCSEffDate = (SELECT MAX(HCPCSEffDate) FROM osit_WkCDMHcpcsList WHERE Linkno = CDM.LinkNo AND HCPCSEffDate <= GetDate() AND hMedlist.hosppayorcode = ''' + @MedHCPCSPayor + ''' )'
END
ELSE
BEGIN
SELECT @sql = @sql + ' LEFT JOIN osit_wkCorpCDMHCPCSList hMedlist WITH(NOLOCK) '
SELECT @sql = @sql + ' ON cdm.linkno = hMedlist.linkno '
SELECT @sql = @sql + ' AND hMedlist.hosppayorcode = ''' + @MedHCPCSPayor + ''''
SELECT @sql = @sql + ' AND hMedList.HCPCSEffDate = (SELECT MAX(HCPCSEffDate) FROM osit_WkCorpCDMHcpcsList WHERE Linkno = CDM.LinkNo AND HCPCSEffDate <= GetDate() AND hMedlist.hosppayorcode = ''' + @MedHCPCSPayor + ''')'
END
IF @IsItCorp = 0
BEGIN
SELECT @sql = @sql + ' LEFT JOIN osit_wkCDMUBList uMedlist WITH(NOLOCK) '
SELECT @sql = @sql + ' ON cdm.linkno = uMedlist.linkno '
SELECT @sql = @sql + ' AND uMedlist.hosppayorcode = ''' + @MedUBPayor + ''''
SELECT @sql = @sql + ' AND uMedList.UBEffDate = (SELECT MAX(UBEffDate) FROM osit_WkCDMUBList WHERE Linkno = CDM.LinkNo AND UBEffDate <= GetDate() AND uMedlist.hosppayorcode = ''' + @MedUBPayor + ''')'
END
ELSE
BEGIN
SELECT @sql = @sql + ' LEFT JOIN osit_wkCorpCDMUBList uMedlist WITH(NOLOCK) '
SELECT @sql = @sql + ' ON cdm.linkno = uMedlist.linkno '
SELECT @sql = @sql + ' AND uMedlist.hosppayorcode = ''' + @MedUBPayor + ''''
SELECT @sql = @sql + ' AND uMedList.UBEffDate = (SELECT MAX(UBEffDate) FROM osit_WkCorpCDMUBList WHERE Linkno = CDM.LinkNo AND UBEffDate <= GetDate() AND uMedlist.hosppayorcode = ''' + @MedUBPayor + ''')'
END
SELECT @sql = @sql + ' LEFT JOIN osit_StdCatalogActive osi WITH(NOLOCK) '
SELECT @sql = @sql + ' ON cdm.OsiStdCode = osi.StdCode '
SELECT @sql = @sql + ' AND osi.IsItCDMMaster = 1 '
SELECT @sql = @sql + ' LEFT JOIN osit_StdCatalogHcpcsListActive ohlist WITH(NOLOCK) '
SELECT @sql = @sql + ' ON osi.StdCode = ohlist.stdCode '
SELECT @sql = @sql + ' AND CONVERT(varchar(10), ohlist.OsiPayorCode) = '
SELECT @sql = @sql + ' dbo.FUNC_GetOSICPTPayorv2(ohlist.stdCode, ''' + CONVERT(varchar(10), @DefFiscInt) + ''''
SELECT @sql = @sql + ') LEFT JOIN osit_StdCatalogUBListActive osi1 WITH(NOLOCK) '
SELECT @sql = @sql + ' ON osi.StdCode = osi1.stdCode '
SELECT @sql = @sql + ' AND osi1.Priority = 1 '
SELECT @sql = @sql + ' AND CONVERT(varchar(10), osi1.OsiPayorCode) = '
SELECT @sql = @sql + ' dbo.FUNC_GetOSIUBPayorv2(osi1.stdCode, ''' + CONVERT(varchar(10), @DefFiscInt) + ''''
SELECT @sql = @sql + ') LEFT JOIN osit_DeptInfo dept WITH(NOLOCK) '
IF @IsItCorp = 0
BEGIN
SELECT @sql = @sql + ' ON cdm.DeptNum = dept.DeptNum '
SELECT @sql = @sql + ' AND cdm.HospID = dept.HospID '
SELECT @sql = @sql + ' LEFT JOIN osit_wkCDMPriceList p1 WITH(NOLOCK) '
END
ELSE
BEGIN
SELECT @sql = @sql + ' ON cdm.CorpStdDept = dept.DeptNum '
SELECT @sql = @sql + ' AND cdm.CorpID = dept.HospID '
SELECT @sql = @sql + ' LEFT JOIN osit_wkCorpCDMPriceList p1 WITH(NOLOCK) '
END
SELECT @sql = @sql + ' ON cdm.linkno = p1.linkno '
SELECT @sql = @sql + ' AND p1.priceid = 1 '
SELECT @sql = @sql + ' LEFT JOIN osit_HospPayorList HCPCSPayor WITH(NOLOCK) '
SELECT @sql = @sql + ' ON HCPCSPayor.Hosppayorcode = cvlist.CPTHCPCSPayor '
SELECT @sql = @sql + ' AND HCPCSPayor.payortype = 1 '
SELECT @sql = @sql + ' AND HCPCSPayor.isitactive = 1 '
SELECT @sql = @sql + ' AND hcpcspayor.hospid = CVList.hospid '
SELECT @sql = @sql + ' LEFT JOIN osit_HospPayorList UBPayor WITH(NOLOCK) '
SELECT @sql = @sql + ' ON UBPayor.Hosppayorcode = cvlist.UBPayor '
SELECT @sql = @sql + ' AND UBpayor.payortype = 2 '
SELECT @sql = @sql + ' AND UBpayor.isitactive = 1 '
SELECT @sql = @sql + ' AND UBpayor.hospid = CVList.hospid '
SELECT @sql = @sql + ' LEFT JOIN osit_OSIPayorList OSIPayor With(nolock) '
SELECT @sql = @sql + ' ON OSIPayor.OSIPayorCode = cvlist.OSIPayorCode '
SELECT @sql = @sql + ' WHERE CONVERT(varchar(10), cdm.OSIDateStamp) = ''' + CONVERT(varchar(10), @OSIDateStamp) + ''''
SELECT @sql = @sql + ' AND cdm.IsItActive = 1 '
print @sql
--insert for #ComplianceSummary is executed here
--execute sp_executesql @sql
exec (@sql)
-- log times for each pass
-- Insert Into osit_PerfMonitor (LogTime, StartTime, EndTime, ElapsedTime, UserID, Type, AddInfo, UserLogin)
-- Values(getdate(),'','',datediff(second,@LogStart,getdate()),@UserID,'BPINet Test LoadTemp4',@sql,'n/a')
-- Set @LogStart = getdate() --reset for next log
--add suggested revenue code column
/*
SET CONCAT_NULL_YIELDS_NULL OFF
DECLARE
@UBCode varchar(20),
@HCPCSCode varchar(7)
DECLARE
sugrevcodes_cursor
CURSOR FOR
SELECT DISTINCT
CONVERT(varchar(15), umap.UBCode),
umap.HCPCSCode
FROM
osit_HCPCS2ubmap umap WITH(NOLOCK)
INNER JOIN
#ComplianceSummary3
ON
#ComplianceSummary3.[BPICPTCode] = umap.HCPCSCode
LEFT JOIN
osit_UBCodeCatalog ucat WITH(NOLOCK)
ON
ucat.UBCode = umap.UBCode
WHERE
umap.Hcpcs2UBMapVersion = @UBVersion
AND
umap.OSIPayorCode = @OSIPayor
OPEN sugrevcodes_cursor
-- Perform the first fetch.
FETCH NEXT FROM sugrevcodes_cursor
INTO @UBCode, @HCPCSCode
@FETCH_STATUS to see if there are any more rows to fetch.
@FETCH_STATUS = 0
BEGIN
UPDATE #ComplianceSummary3
SET
[SugRevCodes] = [SugRevCodes] + ', ' + @UBCode
WHERE
#ComplianceSummary3.[BPICPTCode] = @HCPCSCode
AND
[SugRevCodes] Is Not Null
UPDATE #ComplianceSummary3
SET
[SugRevCodes] = @UBCode
WHERE
#ComplianceSummary3.[BPICPTCode] = @HCPCSCode
AND
[SugRevCodes] Is Null
FETCH NEXT FROM sugrevcodes_cursor
INTO @UBCode, @HCPCSCode
END
CLOSE sugrevcodes_cursor
DEALLOCATE sugrevcodes_cursor
*/
SELECT @sql = 'SELECT * FROM ' + @UserTable + ' '
SELECT @sql = @sql + 'ORDER BY [DeptNum], [ProcCode] '
--print @sql
--execute sp_executesql @sql
exec (@sql)
-- log times for each pass
-- Insert Into osit_PerfMonitor (LogTime, StartTime, EndTime, ElapsedTime, UserID, Type, AddInfo, UserLogin)
-- Values(getdate(),'','',datediff(second,@LogStart,getdate()),@UserID,'BPINet Test LoadTemp5',@sql,'n/a')
-- Set @LogStart = getdate() --reset for next log
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_RptPCCPTLegalPrint]'
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [bpinet_sp_RptPCCPTLegalPrint]
AS
SELECT
MedAssetsRegister,
CPTFARSFooter,
CPTCopyrightFooter,
CPTLegalPrint
FROM
osit_LegalInfo
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_UBUpdateWk]'
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure bpinet_sp_UBUpdateWk
@IsItCorp SmallInt,
@ID uniqueidentifier,
@LinkNo numeric(10) ,
@PayorCode VarChar(10),
@EffDate datetime,
@Code VarChar(10)
as
--**********************************************************************************************************************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Update UB Table
IF @IsITCorp = 0
Begin
IF (Select Count(LinkNO) From osit_wkCdmUbList Where UBID = @ID) > 0
Begin
update osit_wkCdmUbList set
UBEffDate = @EffDate,
UBCode = @Code
Where UBID = @ID
End
Else
Begin
--Priority is a dead field that does not allow nulls
Insert Into osit_wkCdmUbList
(UBID, LinkNo, HospPayorCode, UBEffDate, UBCode, Priority)
Values
(@ID, @LinkNo, @PayorCode, @EffDate, @Code,0)
End
End
Else
Begin
IF (Select Count(LinkNO) From osit_wkcorpCdmUbList Where UBID = @ID) > 0
Begin
update osit_wkcorpCdmUbList set
UBEffDate = @EffDate,
UBCode = @Code
Where UBID = @ID
End
Else
Begin
Insert Into osit_wkcorpCdmUbList
(UBID, LinkNo, HospPayorCode, UBEffDate, UBCode,Priority)
Values
(@ID, @LinkNo, @PayorCode, @EffDate, @Code,0)
End
End
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_RptPCDuplicateItemsByDesc]'
GO
CREATE PROCEDURE [bpinet_sp_RptPCDuplicateItemsByDesc]
@HospID varchar(10),
@Accepted smallint,
@OSIDateStamp int,
@IsItCorp smallint,
@UserID int
AS
--select @HospId='CAR58',@Accepted=0,@OSIDateStamp=20020920,@IsItCorp=1,@USerId=-1
CREATE TABLE #DiffPriceDescriptions (
[ViolationID] numeric (18,0),
[DeptNum] varchar(10),
[ProcCode] varchar(20),
[ItemDesc] varchar(50),
[Volume] int,
[PrimaryPrice] money,
[PriceType] smallint,
[Accepted] smallint )
IF @UserID <> -1 --Run report on specific departments, in osit_BPISelectedDept
AND @IsItCorp = 1 --Corporate is true
BEGIN
INSERT INTO #DiffPriceDescriptions
SELECT
lst.ViolationID,
cdm.CorpStdDept,
cdm.CorpStdCode,
cdm.HospDesc,
CONVERT(int, (CASE WHEN cdm.AnnualizedVolume IS NULL THEN 0 ELSE cdm.AnnualizedVolume END)) ,
ipp.Price ,
ipp.PriceType,
lst.IsItClientOverwrite
FROM obpt_HospComplViolationListGrp2Item2 lst WITH(NOLOCK)
INNER JOIN osit_wkCorpCDM cdm WITH(NOLOCK)
ON cdm.CorpID = lst.HospID
AND cdm.CorpStdCode = lst.Proccode
AND cdm.CorpStdDept = lst.DeptNum
AND cdm.OSIDateStamp = @OSIDateStamp
AND cdm.IsItActive = 1
LEFT JOIN osit_wkCorpCDMPriceList ipp WITH(NOLOCK)
ON ipp.LinkNo = cdm.LinkNo
AND ipp.PriceID = 1
AND ipp.PriceEffDate = (SELECT MAX(PriceEffDate) FROM osit_wkCorpCDMPriceList WHERE linkno = cdm.linkno AND PriceID = 1 AND PriceEffDate <= getdate())
WHERE lst.HospID = @HospID
AND lst.IsItClientOverwrite = @Accepted
AND lst.DeptNum IN (Select DeptNum from osit_BPISelectedDept WHERE UserID = @UserID)
END
IF @UserID <> -1 --Run report on specific departments, in osit_BPISelectedDept
AND @IsItCorp = 0 --Corporate is false
BEGIN
INSERT INTO #DiffPriceDescriptions
SELECT
lst.ViolationID,
cdm.DeptNum,
cdm.ProcCode,
cdm.HospDesc,
CONVERT(int, (CASE WHEN cdm.AnnualizedVolume IS NULL THEN 0 ELSE cdm.AnnualizedVolume END)) ,
ipp.Price ,
ipp.PriceType,
lst.IsItClientOverwrite
FROM obpt_HospComplViolationListGrp2Item2 lst WITH(NOLOCK)
INNER JOIN osit_wkCDM cdm WITH(NOLOCK)
ON cdm.HospID = lst.HospID
AND cdm.ProcCode = lst.Proccode
AND cdm.DeptNum = lst.DeptNum
AND cdm.OSIDateStamp = @OSIDateStamp
AND cdm.IsItActive = 1
LEFT JOIN osit_wkCDMPriceList ipp WITH(NOLOCK)
ON ipp.LinkNo = cdm.LinkNo
AND ipp.PriceID = 1
AND ipp.PriceEffDate = (SELECT MAX(PriceEffDate) FROM osit_wkCDMPriceList WHERE linkno = cdm.linkno AND PriceID = 1 AND PriceEffDate <= getdate())
WHERE lst.HospID = @HospID
AND lst.IsItClientOverwrite = @Accepted
AND lst.DeptNum IN (Select DeptNum from osit_BPISelectedDept WHERE UserID = @UserID)
END
IF @UserID = -1 --Run report on all departments
AND @IsItCorp = 1 --Corporate is true
BEGIN
INSERT INTO #DiffPriceDescriptions
SELECT
lst.ViolationID,
cdm.CorpStdDept,
cdm.CorpStdCode,
cdm.HospDesc,
CONVERT(int, (CASE WHEN cdm.AnnualizedVolume IS NULL THEN 0 ELSE cdm.AnnualizedVolume END)) ,
ipp.Price ,
ipp.PriceType,
lst.IsItClientOverwrite
FROM obpt_HospComplViolationListGrp2Item2 lst WITH(NOLOCK)
INNER JOIN osit_wkCorpCDM cdm WITH(NOLOCK)
ON cdm.CorpID = lst.HospID
AND cdm.CorpStdCode = lst.Proccode
AND cdm.CorpStdDept = lst.DeptNum
AND cdm.OSIDateStamp = @OSIDateStamp
AND cdm.IsItActive = 1
LEFT JOIN osit_wkCorpCDMPriceList ipp WITH(NOLOCK)
ON ipp.LinkNo = cdm.LinkNo
AND ipp.PriceID = 1
AND ipp.PriceEffDate = (SELECT MAX(PriceEffDate) FROM osit_wkCorpCDMPriceList WHERE linkno = cdm.linkno AND PriceID = 1 AND PriceEffDate <= getdate())
WHERE lst.HospID = @HospID
AND lst.IsItClientOverwrite = @Accepted
END
IF @UserID = -1 --Run report on all departments
AND @IsItCorp = 0 --Corporate is false
BEGIN
INSERT INTO #DiffPriceDescriptions
SELECT
lst.ViolationID,
cdm.DeptNum,
cdm.ProcCode,
cdm.HospDesc,
CONVERT(int, (CASE WHEN cdm.AnnualizedVolume IS NULL THEN 0 ELSE cdm.AnnualizedVolume END)) ,
ipp.Price ,
ipp.PriceType,
lst.IsItClientOverwrite
FROM obpt_HospComplViolationListGrp2Item2 lst WITH(NOLOCK)
INNER JOIN osit_wkCDM cdm WITH(NOLOCK)
ON cdm.HospID = lst.HospID
AND cdm.ProcCode = lst.Proccode
AND cdm.DeptNum = lst.DeptNum
AND cdm.OSIDateStamp = @OSIDateStamp
AND cdm.IsItActive = 1
LEFT JOIN osit_wkCDMPriceList ipp WITH(NOLOCK)
ON ipp.LinkNo = cdm.LinkNo
AND ipp.PriceID = 1
AND ipp.PriceEffDate = (SELECT MAX(PriceEffDate) FROM osit_wkCDMPriceList WHERE linkno = cdm.linkno AND PriceID = 1 AND PriceEffDate <= getdate())
WHERE lst.HospID = @HospID
AND lst.IsItClientOverwrite = @Accepted
END
SELECT
ViolationID,
[ItemDesc] AS [DuplicateItem],
[DeptNum],
[ProcCode],
[ItemDesc],
[Volume],
[PrimaryPrice],
[PriceType],
[Accepted]
FROM
#DiffPriceDescriptions
WHERE
((([ItemDesc]) IN
(SELECT [ItemDesc]
FROM [#DiffPriceDescriptions] AS Tmp
GROUP BY [ItemDesc]
-- HAVING Count(*)>1 remmed by rich not pulling violations when only one left
)))
ORDER BY
[ItemDesc],
[DeptNum],
[ProcCode]
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_RptPCLinkedCodesNotCDMtest]'
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE PROCEDURE [bpinet_sp_RptPCLinkedCodesNotCDMtest]
@HospID varchar(10),
@Accepted smallint,
@OSIPayor smallint,
@HCPCSVersion smallint,
@CPTVersion smallint,
@UserId int,
@IsItCorp tinyint
AS
--use any for testing
--ready for butch
--this report does not have departments
--and is not tied to corporate columns/tables
--ergo, no IF clauses
SELECT DISTINCT
vList.LinkedCode AS [MissingCPTHCPCSCode], ccat.ShortDesc AS [MissingCodeDesc],
vList.CPTCode AS [CPTHCPCSCodeLinked], lcat.ShortDesc AS [CodeLinkDesc] ,
vList.IsItClientOverwrite AS [Accepted],
vList.ViolationId
FROM obpt_HospComplViolationListGrp5Item3 vList WITH(NOLOCK)
LEFT JOIN osit_CodeCatalog ccat WITH(NOLOCK)
ON ccat.HCPCSCode = vList.CPTCode
AND ccat.Version = @CPTVersion
AND ccat.OsiPayorCode = @OSIPayor
LEFT JOIN osit_CodeCatalog lcat WITH(NOLOCK)
ON lcat.HCPCSCode = vList.CPTCode
AND lcat.Version = @HCPCSVersion
AND lcat.OsiPayorCode = @OSIPayor
WHERE vList.HospID = @HospID
AND vList.IsItClientOverwrite = @Accepted
ORDER BY
vList.LinkedCode,
vList.CPTCode
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_HCPCSUpdateWk]'
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure bpinet_sp_HCPCSUpdateWk
@IsItCorp SmallInt,
@ID uniqueidentifier,
@LinkNo numeric(10) ,
@PayorCode VarChar(10),
@EffDate datetime,
@Code VarChar(10),
@Mod1 VarChar(10),
@Mod2 VarChar(10)
as
--**********************************************************************************************************************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Update Table
IF @IsITCorp = 0
Begin
IF (Select Count(LinkNo) From osit_wkCdmHCPCSList Where HCPCSID = @ID) > 0
Begin
update osit_wkCdmHcpcsList set
HCPCSEffDate = @EffDate,
HCPCSCode = @Code,
ModifierCode1 = @Mod1,
ModifierCode2 = @Mod2
Where HCPCSID = @ID
End
Else
Begin
--Priority is a dead field that does not allow nulls
--IsItCPT is also dead
Insert Into osit_wkCdmHcpcsList
(HCPCSID, LinkNo, HospPayorCode, HCPCSEffDate, HCPCSCode, ModifierCode1, ModifierCode2 , Priority, IsItCPTCode)
Values
(@ID, @LinkNo, @PayorCode, @EffDate, @Code, @Mod1, @Mod2 ,0, 0)
End
End
Else
Begin
IF (Select Count(LinkNo) From osit_wkcorpCdmHCPCSList Where HCPCSID = @ID) > 0
Begin
update osit_wkcorpCdmHcpcsList set
HCPCSEffDate = @EffDate,
HCPCSCode = @Code,
ModifierCode1 = @Mod1,
ModifierCode2 = @Mod2
Where HCPCSID = @ID
End
Else
Begin
--Priority is a dead field that does not allow nulls
Insert Into osit_wkcorpCdmHcpcsList
(HCPCSID, LinkNo, HospPayorCode, HCPCSEffDate, HCPCSCode, ModifierCode1, ModifierCode2 , Priority)
Values
(@ID, @LinkNo, @PayorCode, @EffDate, @Code, @Mod1, @Mod2 ,0)
End
End
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_RptPCAPCLegend]'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE [bpinet_sp_RptPCAPCLegend]
AS
SELECT
APCStatusCode + ': ' + APCStatusDesc AS APCLegend
FROM
osit_APCStatusCodeList
ORDER BY
APCStatusCode
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_CIGetLinkNo]'
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure bpinet_sp_CIGetLinkNo
@IsItCorp SmallInt,
@HospID varchar(10),
@DeptNum varchar(10),
@ProcCode varchar(20)
as
--**********************************************************************************************************************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Declare @LinkNo numeric(10)
IF @IsItCorp = 0
Begin
Set @LinkNo =
(Select LinkNo From osit_WkCDM
Where HospID = @HospID AND DeptNum = @DeptNum AND ProcCode = @ProcCode)
End
ELSE
Begin
Set @LinkNo =
(Select LinkNo From osit_WkCorpCDM
Where CorpID = @HospID AND CorpStdDept = @DeptNum AND CorpStdCode = @ProcCode)
End
Select @LinkNo
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_GetOSIDeptList]'
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE bpinet_sp_GetOSIDeptList
@HospId as varchar(10)
AS
SELECT distinct osi.StdDept AS DeptNum,
dept.StdDeptDesc,
CASE WHEN (sel.Deptnum is not null)
THEN 1
ELSE 0
END AS IsSelected
FROM osit_StdCatalogActive osi
JOIN osit_StdDeptCatalog dept
ON osi.StdDept = dept.StdDeptCode
LEFT JOIN osit_BPISelectedOSIDept sel
ON osi.StdDept= sel.DeptNum
AND sel.HospId = @HospId
ORDER BY osi.StdDept
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_PriceUpdate]'
GO
SET ANSI_NULLS ON
GO
CREATE procedure bpinet_sp_PriceUpdate
@IsItCorp SmallInt,
@LinkNo numeric(10) ,
@PriceID VarChar(10),
@OldEffDate datetime,
@EffDate datetime,
@Price Money
as
--**********************************************************************************************************************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Update Table
IF @IsITCorp = 0
Begin
IF (Select Count(LinkNo) From osit_wkCdmPriceList Where LinkNo = @LinkNo AND PriceID = @PriceID AND PriceEffDate = @OldEffDate) > 0
Begin
update osit_wkCdmPriceList set
PriceEffDate = @EffDate,
Price = @Price
Where LinkNo = @LinkNo AND PriceID = @PriceID AND PriceEffDate = @OldEffDate
End
Else
Begin
Insert Into osit_wkCdmPriceList
(LinkNo, PriceID, PriceEffDate, Price, PriceType)
Values
(@LinkNo, @PriceID, @EffDate, @Price, 1)
End
End
Else
Begin
IF (Select Count(LinkNo) From osit_wkCorpCdmPriceList Where LinkNo = @LinkNo AND PriceID = @PriceID AND PriceEffDate = @OldEffDate) > 0
Begin
update osit_wkcorpCdmPriceList set
PriceEffDate = @EffDate,
Price = @Price
Where LinkNo = @LinkNo AND PriceID = @PriceID AND PriceEffDate = @OldEffDate
End
Else
Begin
Insert Into osit_wkcorpCdmPriceList
(LinkNo, PriceID, PriceEffDate, Price, PriceType)
Values
(@LinkNo, @PriceID, @EffDate, @Price, 1)
End
End
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_CIInsert]'
GO
Create procedure bpinet_sp_CIInsert
@IsItCorp SmallInt,
@HospID VarChar(10),
@DeptNum VarChar(10) ,
@ProcCode VarChar(20),
@OSIDateStamp Numeric(8)
as
--**********************************************************************************************************************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Update UB Table History
IF @IsITCorp = 0
Begin
Insert Into osit_wkCdm
(HospID, DeptNum, ProcCode, OSIDateStamp )
Values
(@HospID, @DeptNum, @ProcCode, @OSIDateStamp)
-- now get the linkno as a result set
Select LinkNo From osit_wkCDM
Where HospID = @HospID and DeptNum = @DeptNum and ProcCode = @ProcCode and OSIDateStamp = @OSIDateStamp
End
Else
Begin
Insert Into osit_wkcorpCdm
(CorpID, CorpStdDept, CorpStdCode, OSIDateStamp )
Values
(@HospID, @DeptNum, @ProcCode, @OSIDateStamp)
-- now get the linkno as a result set
Select LinkNo From osit_wkcorpCDM
Where CorpID = @HospID and CorpStdDept = @DeptNum and CorpStdCode = @ProcCode and OSIDateStamp = @OSIDateStamp
End
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_KMRevGet]'
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE bpinet_sp_KMRevGet
@Code as varchar(10)
AS
--Priority is expected by the object, but not needed for this query
select UBCode,UBCodeDesc,Comments, Priority=0
from osit_UBCodeCatalog
Where UBCode = @Code
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_CIUpdate]'
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE procedure bpinet_sp_CIUpdate
@IsItCorp SmallInt,
@LinkNo numeric(10) ,
@HospID VarChar(10),
@DeptNum VarChar(10),
@ProcCode VarChar(20),
@IsItActive smallint,
@HospDesc VarChar(50),
@TechnicalDesc Varchar(50),
@UserComments VarChar(256),
@CorpStdDept VarChar(18),
@CorpStdCode VarChar(20),
@SimDept VarChar(3),
@SimCode VarChar(10)
as
--**********************************************************************************************************************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Update UB Table
-- Convert Dept Numbers to SmallInt until we change the datatypes in the table.
if isnumeric(@DeptNum)=0 Set @DeptNum='0'
if isnumeric(@CorpStdDept)=0 Set @CorpStdDept='0'
IF @IsITCorp = 0
Begin
Update osit_wkCdm Set
HospID = @HospID,
DeptNum = @DeptNum,
ProcCode = @ProcCode,
IsItActive = @IsItActive,
HospDesc = @HospDesc,
TechnicalDesc = @TechnicalDesc,
UserComments = @UserComments,
CorpStdDept = @CorpStdDept,
CorpStdCode = @CorpStdCode,
SimDept = @SimDept,
SimCode = @SimCode
Where LinkNo = @LinkNo
End
Else
Begin
Update osit_wkcorpCdm Set
CorpID = @HospID,
IsItActive = @IsItActive,
HospDesc = @HospDesc,
TechnicalDesc = @TechnicalDesc,
UserComments = @UserComments,
CorpStdDept = @DeptNum,
CorpStdCode = @ProcCode,
SimDept = @SimDept,
SimCode = @SimCode
Where LinkNo = @LinkNo
End
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_PCGetMissingItemDetail]'
GO
SET QUOTED_IDENTIFIER OFF
GO
CREATE procedure bpinet_sp_PCGetMissingItemDetail
--This part of the missing items query will be used by both getMissingItems and GetLinkedCode violations
@MissingItemType smallint, --HCPCS = 1; APC = 2
@HospID varchar(10),
@UserId int ,
@OSIPayorCode int,
@APCPassCodeVersion numeric (8,0),
@UseFutureCode int,
@State varchar(3),
@HospLocalityCode varchar (2),
@HospIsSoleProvider smallint,
@IsForLinkedItems smallint
AS
declare @sql nvarchar(4000)
BEGIN
SELECT tmp.Accepted, tmp.Code, tmp.ShortDesc, apc.APCPassCodeStatusCode, tmp.ViolationID,
(CASE WHEN @IsForLinkedItems = 1 THEN tmp.CDMCode ELSE NULL END) AS CDMCode,
(CASE WHEN @MissingItemType = 1 THEN
(CASE WHEN @HospIsSoleProvider = 1 THEN rbs.SoleProviderPrice ELSE rbs.CPTPrice END)
ELSE apcCat.APCNatPaymentRate END) AS CPTPrice
FROM ##TempMissingTable tmp
LEFT JOIN osit_APCPassCodeList apc ON apc.HCPCSCode = tmp.Code
AND apc.APCPassCodeVersion = @APCPassCodeVersion
LEFT JOIN osit_CPTRatesByState rbs ON rbs.CPTCode = tmp.Code
AND rbs.State = @State
AND rbs.MedicareLocCode = @HospLocalityCode
LEFT JOIN osit_APCCatalog apcCat ON apcCat.APCCode = tmp.Code
AND apcCat.APCVersion = @APCPassCodeVersion
ORDER BY (CASE WHEN @IsForLinkedItems = 1 THEN tmp.CDMCode ELSE tmp.code END)
END
--Matthew Share - the payorcode is presently hard-coded to 1 until Tracy gets back
--to me to let me know how they want to handle the overlays when we're doing the backwards
--lookup - we have a CPT and we're trying to find catalog items that are related to it.
BEGIN
SELECT tmp.Code, sca.StdCode, sca.StdDept, sca.BillingDesc30
FROM ##TempMissingTable tmp
INNER JOIN osit_StdCatalogHCPCSListActive hcpcsla ON
tmp.Code = (CASE WHEN @UseFutureCode = 1 THEN hcpcsla.CPTReviewHCPCSCodeFuture ELSE hcpcsla.CPTReviewHCPCSCode END)
AND hcpcsla.OSIPayorCode = 1
LEFT JOIN osit_StdCatalogActive sca ON hcpcsla.StdCode = sca.StdCode
UNION
SELECT tmp.Code, sca.StdCode, sca.StdDept, sca.BillingDesc30
FROM ##TempMissingTable tmp
INNER JOIN osit_StdCatalogHCPCSListActive hcpcsla ON
(CASE WHEN @UseFutureCode = 1 THEN hcpcsla.CPTReviewCPTCodeFuture ELSE hcpcsla.CPTReviewCPTCode END) = tmp.Code
AND hcpcsla.OSIPayorCode = 1
LEFT JOIN osit_StdCatalogActive sca ON hcpcsla.StdCode = sca.StdCode
ORDER BY tmp.code
END
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_compl_AcceptViolation]'
GO
SET ANSI_NULLS OFF
GO
CREATE procedure bpinet_sp_compl_AcceptViolation
@ViolationType smallint,
@HospID varchar(10),
@DeptNum varchar(10),
@ProcCode varchar(10),
@OSIPayorCode smallint,
@IsItCorp smallint,
--@CorpPushDownHosps varchar(256), --A list of corp hosps that you want included
@UserID Numeric(10),
--in the cascading update
@NewValue smallint
AS
Declare @sql nvarchar(1000)
Declare @ViolationTableName varchar(50)
--First, we get the appropriate violation table name from the violationTypeId
exec bpinet_sp_GetViolationTableName @ViolationType, @ViolationTableName Output
--If we're dealing with a corp, accept the related corp items
/*IF (@IsItCorp = 1 AND @CorpPushDownHosps <> '')
BEGIN
select @sql = 'UPDATE ' + @ViolationTableName + '
set IsItClientOverWrite = ' + convert(varchar(5),@NewValue) +
',UserID = ' + convert(varchar(10),@UserID) + ', LastChanged = GetDate()
from ' + @ViolationTableName + ' v ' +
'JOIN osit_wkcdm cdm
ON v.hospid = cdm.hospid
AND v.DeptNum = cdm.DeptNum
and v.ProcCode = cdm.ProcCode
JOIN osit_hospperiodinfo hpi ON
hpi.hospid = cdm.hospid
And hpi.IsItActive = 1
WHERE cdm.CorpStdCatalogId = ''' + @HospId + '''
AND cdm.CorpStdDept = ' + @DeptNum +
' AND cdm.corpStdCode = ''' + @ProcCode + '''
and cdm.isitactive = 1
and cdm.hospid in (' + @CorpPushDownHosps + ')
and cdm.OsiDateStamp = hpi.OsiDateStamp'
print @sql
Exec sp_executeSql @sql
END*/
--This is the standard (non-corp) update statement which we'll want to execute
--whether we're dealing with a corp or not; so it's not included in the @IsItCorp
--if statement scope. On corps, we'll update the related records above and then
--call this. On non-corps, we'll simple call this and skip all that mess above.
select @sql = 'UPDATE ' + @ViolationTableName + '
set IsItClientOverWrite = ' + convert(varchar(5),@NewValue) +
',UserID = ' + convert(varchar(10),@UserID) + ', LastChanged = GetDate()
WHERE HospId = ''' + @HospId + '''
AND DeptNum = ' + @DeptNum +
' AND ProcCode = ''' + @ProcCode + ''''
Declare @HasField tinyint
declare @SelectValue varchar(100)
Execute @HasField = bpinet_sp_ViolationTableSelectValue @ViolationType, 'OSIPayorCode', @SelectValue Output
If (@HasField = 1)
begin
select @sql = @sql + ' AND OSIPayorCode = ' + convert(varchar(5),@OSIPayorCode)
end
--print @sql
Exec sp_executeSql @sql
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_RptPCDuplicateItemsByStdCode]'
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE [bpinet_sp_RptPCDuplicateItemsByStdCode]
@HospID varchar(10),
@Accepted smallint,
@OSIDateStamp int,
@IsItCorp smallint,
@UserID int
AS
--select @HospId='CAR62',@Accepted=0,@OSIDateStamp=20040107,@IsItCorp=0,@USerId=-1
CREATE TABLE #DiffPriceStdCodes (
[ViolationID] numeric(18,0),
[LongDesc] varchar(30),
[DeptNum] varchar(10),
[ProcCode] varchar(20),
[ItemDesc] varchar(50),
[Volume] int,
[PrimaryPrice] money,
[PriceType] smallint,
[Accepted] smallint )
IF @UserID <> -1 --Run report on specific departments, in osit_BPISelectedDept
AND @IsItCorp = 1 --Corporate is true
BEGIN
INSERT INTO #DiffPriceStdCodes
SELECT
lst.ViolationID,
osi.BillingDesc30,
cdm.CorpStdDept,
cdm.CorpStdCode,
cdm.HospDesc,
CONVERT(int, (CASE WHEN cdm.AnnualizedVolume IS NULL THEN 0 ELSE cdm.AnnualizedVolume END)) ,
ipp.Price ,
ipp.PriceType,
lst.IsItClientOverwrite
FROM obpt_HospComplViolationListGrp2Item7 lst WITH(NOLOCK)
INNER JOIN osit_wkCorpCDM cdm WITH(NOLOCK)
ON cdm.CorpID = lst.HospID
AND cdm.CorpStdCode = lst.Proccode
AND cdm.CorpStdDept = lst.DeptNum
AND cdm.OSIDateStamp = @OSIDateStamp
AND cdm.IsItActive = 1
LEFT JOIN osit_wkCorpCDMPriceList ipp WITH(NOLOCK)
ON ipp.LinkNo = cdm.LinkNo
AND ipp.PriceID = 1
AND ipp.PriceEffDate = (SELECT MAX(PriceEffDate) FROM osit_wkCorpCDMPriceList WHERE linkno = cdm.linkno AND PriceID = 1 AND PriceEffDate <= getdate())
INNER JOIN osit_StdCatalogActive osi WITH(NOLOCK)
ON cdm.OsiStdCode = osi.StdCode
AND osi.IsItCDMMaster = 1
WHERE lst.HospID = @HospID
AND lst.IsItClientOverwrite = @Accepted
AND lst.DeptNum IN (Select DeptNum from osit_BPISelectedDept WHERE UserID = @UserID)
END
IF @UserID <> -1 --Run report on specific departments, in osit_BPISelectedDept
AND @IsItCorp = 0 --Corporate is false
BEGIN
INSERT INTO #DiffPriceStdCodes
SELECT
lst.ViolationID,
osi.BillingDesc30,
cdm.DeptNum,
cdm.ProcCode,
cdm.HospDesc,
CONVERT(int, (CASE WHEN cdm.AnnualizedVolume IS NULL THEN 0 ELSE cdm.AnnualizedVolume END)) ,
ipp.Price ,
ipp.PriceType,
lst.IsItClientOverwrite
FROM obpt_HospComplViolationListGrp2Item7 lst WITH(NOLOCK)
INNER JOIN osit_wkCDM cdm WITH(NOLOCK)
ON cdm.HospID = lst.HospID
AND cdm.ProcCode = lst.Proccode
AND cdm.DeptNum = lst.DeptNum
AND cdm.OSIDateStamp = @OSIDateStamp
AND cdm.IsItActive = 1
LEFT JOIN osit_wkCDMPriceList ipp WITH(NOLOCK)
ON ipp.LinkNo = cdm.LinkNo
AND ipp.PriceID = 1
AND ipp.PriceEffDate = (SELECT MAX(PriceEffDate) FROM osit_wkCDMPriceList WHERE linkno = cdm.linkno AND PriceID = 1 AND PriceEffDate <= getdate())
INNER JOIN osit_StdCatalogActive osi WITH(NOLOCK)
ON cdm.OsiStdCode = osi.StdCode
AND osi.IsItCDMMaster = 1
WHERE lst.HospID = @HospID
AND lst.IsItClientOverwrite = @Accepted
AND lst.DeptNum IN (Select DeptNum from osit_BPISelectedDept WHERE UserID = @UserID)
END
IF @UserID = -1 --Run report on all departments
AND @IsItCorp = 1 --Corporate is true
BEGIN
INSERT INTO #DiffPriceStdCodes
SELECT
lst.ViolationID,
osi.BillingDesc30,
cdm.CorpStdDept,
cdm.CorpStdCode,
cdm.HospDesc,
CONVERT(int, (CASE WHEN cdm.AnnualizedVolume IS NULL THEN 0 ELSE cdm.AnnualizedVolume END)) ,
ipp.Price ,
ipp.PriceType,
lst.IsItClientOverwrite
FROM obpt_HospComplViolationListGrp2Item7 lst WITH(NOLOCK)
INNER JOIN osit_wkCorpCDM cdm WITH(NOLOCK)
ON cdm.CorpID = lst.HospID
AND cdm.CorpStdCode = lst.Proccode
AND cdm.CorpStdDept = lst.DeptNum
AND cdm.OSIDateStamp = @OSIDateStamp
AND cdm.IsItActive = 1
LEFT JOIN osit_wkCorpCDMPriceList ipp WITH(NOLOCK)
ON ipp.LinkNo = cdm.LinkNo
AND ipp.PriceID = 1
AND ipp.PriceEffDate = (SELECT MAX(PriceEffDate) FROM osit_wkCorpCDMPriceList WHERE linkno = cdm.linkno AND PriceID = 1 AND PriceEffDate <= getdate())
INNER JOIN osit_StdCatalogActive osi WITH(NOLOCK)
ON cdm.OsiStdCode = osi.StdCode
AND osi.IsItCDMMaster = 1
WHERE lst.HospID = @HospID
AND lst.IsItClientOverwrite = @Accepted
END
IF @UserID = -1 --Run report on all departments
AND @IsItCorp = 0 --Corporate is false
BEGIN
INSERT INTO #DiffPriceStdCodes
SELECT
lst.ViolationID,
osi.BillingDesc30,
cdm.DeptNum,
cdm.ProcCode,
cdm.HospDesc,
CONVERT(int, (CASE WHEN cdm.AnnualizedVolume IS NULL THEN 0 ELSE cdm.AnnualizedVolume END)) ,
ipp.Price ,
ipp.PriceType,
lst.IsItClientOverwrite
FROM obpt_HospComplViolationListGrp2Item7 lst WITH(NOLOCK)
INNER JOIN osit_wkCDM cdm WITH(NOLOCK)
ON cdm.HospID = lst.HospID
AND cdm.ProcCode = lst.Proccode
AND cdm.DeptNum = lst.DeptNum
AND cdm.OSIDateStamp = @OSIDateStamp
AND cdm.IsItActive = 1
LEFT JOIN osit_wkCDMPriceList ipp WITH(NOLOCK)
ON ipp.LinkNo = cdm.LinkNo
AND ipp.PriceID = 1
AND ipp.PriceEffDate = (SELECT MAX(PriceEffDate) FROM osit_wkCDMPriceList WHERE linkno = cdm.linkno AND PriceID = 1 AND PriceEffDate <= getdate())
INNER JOIN osit_StdCatalogActive osi WITH(NOLOCK)
ON cdm.OsiStdCode = osi.StdCode
AND osi.IsItCDMMaster = 1
WHERE lst.HospID = @HospID
AND lst.IsItClientOverwrite = @Accepted
END
SELECT
ViolationID,
[LongDesc] AS [DuplicateItem],
[DeptNum],
[ProcCode],
[ItemDesc],
[Volume],
[PrimaryPrice],
[PriceType],
[Accepted]
FROM
#DiffPriceStdCodes
WHERE
((([LongDesc]) IN
(SELECT [LongDesc]
FROM [#DiffPriceStdCodes] AS Tmp
GROUP BY [LongDesc]
HAVING Count(*)>1 )))
ORDER BY
[LongDesc],
[DeptNum],
[ProcCode]
DROP TABLE #DiffPriceStdCodes
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_UBUpdateHistoryWk]'
GO
create procedure bpinet_sp_UBUpdateHistoryWk
@TransNo Numeric(10),
@IsItCorp SmallInt,
@ID uniqueidentifier,
@LinkNo numeric(10) ,
@PayorCode VarChar(10),
@EffDate datetime,
@Code VarChar(10),
@DeleteFlag SmallInt
as
--**********************************************************************************************************************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Update UB Table History
IF @IsITCorp = 0
Begin
Insert Into osit_wkCdmUbListHistory
(TransNo, UBID, LinkNo, HospPayorCode, UBEffDate, UBCode, DeleteFlag)
Values
(@TransNo, @ID, @LinkNo, @PayorCode, @EffDate, @Code, @DeleteFlag)
End
Else
Begin
Insert Into osit_wkcorpCdmUbListHistory
(TransNo, UBID, LinkNo, HospPayorCode, UBEffDate, UBCode, DeleteFlag)
Values
(@TransNo, @ID, @LinkNo, @PayorCode, @EffDate, @Code, @DeleteFlag)
End
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[bpinet_sp_UBUpdateHistory]'
GO
create procedure bpinet_sp_UBUpdateHistory
@TransNo Numeric(10),
@IsItCorp SmallInt,
@ID uniqueidentifier,
@LinkNo numeric(10) ,
@PayorCode VarChar(10),
@EffDate datetime,
@Code VarChar(10),
@DeleteFlag SmallInt
as
--**********************************************************************************************************************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
-- Update UB Table History
IF @IsITCorp = 0
Begin
Insert Into osit_wkCdmUbListHistory
(TransNo, UBID, LinkNo, HospPayorCode, UBEffDate, UBCode, DeleteFlag)
Values
(@TransNo, @ID, @LinkNo, @PayorCode, @EffDate, @Code, @DeleteFlag)
End
Else
Begin
Insert Into osit_wkcorpCdmUbListHistory
(TransNo, UBID, LinkNo, HospPayorCode, UBEffDate, UBCode, DeleteFlag)
Values
(@TransNo, @ID, @LinkNo, @PayorCode, @EffDate, @Code, @DeleteFlag)
End
GO
@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating [dbo].[BPINet_sp_ValPCMedNonCovered]'
GO
Potential Medicare Non-Covered and Non-Billable Items Found
CREATE procedure BPINet_sp_ValPCMedNonCovered
@HospID varchar(10),
@OSIDateStamp varchar(8),
@MedCode smallint,
@GroupItem numeric(10,0),
@OSIPayor smallint,
@UserId numeric(9,0),
@IsItCorp smallint,
@numRowAffected numeric(8,0) output
as
--**********************************************************************************************************************
--
--**********************************************************************************************************************
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON
DECLARE @Err NUMERIC(8,0)
-- This procedure had ComplChkGrpCode = 3 and ComplChkGrpItem = @GroupItem
--DECLARE @HospID varchar(10)
--DECLARE @OSIDateStamp varchar(8)
--DECLARE @MedCode smallint
--DECLARE @GroupItem numeric(10,0)
--DECLARE @OSIPayor smallint
--DECLARE @UserId numeric(9,0)
--DECLARE @IsItCorp smallint
--DECLARE @numRowAffected numeric(8,0)
--SET @HospID = 'CAR58'
--SET @OSIDateStamp = 20020920
--SET @MedCode = 1
--SET @GroupItem = 1
--SET @OSIPayor = 1
--SET @UserId = 9030
--SET @IsItCorp = 1
--SET @numRowAffected = 0
DECLARE @Sql varchar(500)
DECLARE @SqlCond varchar(500)
--delete the existing records
SET @SQL = 'DELETE FROM obpt_HospComplViolationListGrp3Item' + cast(@GroupItem as varchar(2)) +
' WHERE HospID = ''' + @HospID + ''' AND IsItClientOverwrite = 0
This discussion has been closed.
Comments
Thanks for your post. This is a known issue and we are working on a fix for it.