use MEDICARE
go


alter table medicare..tbmedhospital
add ActivatePhase1 bit null

go

alter table medicare..tbmedhospital
add ActivatePhase2 bit null


go

alter table medicare..tbmedhospital
add ActivatePhase1_2 bit null

go

update medicare..tbmedhospital set ActivatePhase1 = 1
update medicare..tbmedhospital set ActivatePhase1_2 = 0
update medicare..tbmedhospital set ActivatePhase2 = 0


go


alter table build_file..tbcoBuildFileSetting
add isAllowUserVerification bit

go


update build_file..tbcoBuildFileSetting set isAllowUserVerification = 0

go







USE [Medicare]
GO

-------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'Medic_AutoComputeCharges')
EXEC ('CREATE PROC dbo.Medic_AutoComputeCharges AS SELECT 1')
GO
	

/****** Object:  StoredProcedure [dbo].[Medic_AutoComputeCharges]    Script Date: 05/21/2012 15:26:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


/****** Object:  Stored Procedure dbo.Medic_AutoComputeCharges    Script Date: 07/01/2000 1:19:49 AM ******/
ALTER PROCEDURE [dbo].[Medic_AutoComputeCharges]

   @IdNum as varchar(10),
   @strUserID as varchar(10)

AS
   /******/
Declare Patient_Charges CURSOR FOR    
Select Amount, RevenueID, ItemID, Quantity, Description, TransDate, MedicareType, MedicareCategory, LocationID
from Billing..BillingDrugsSOA
Where IDNum = @IDNum --and  (TransDate < = BillingDate  or BillingDate is null)

UNION ALL

Select Amount, RevenueID, ItemID, Quantity, Description, TransDate, MedicareType, MedicareCategory, LocationID
from Billing..vwHistoryInDrugs
Where IDNum = @IDNum --and  (TransDate < = BillingDate  or BillingDate is null)

UNION ALL 

Select Amount, RevenueID, ItemID, Quantity, Description, TransDate, MedicareType, MedicareCategory, LocationID
from Billing..BillingOthersSOA
Where IDNum = @IDNum --and (TransDate < = BillingDate  or BillingDate is null) 
          and DrCr <> 'P'
          and RevenueID not in ('PH','PC','CS','CC')

UNION ALL

Select Amount, RevenueID, ItemID, Quantity, Description, TransDate, MedicareType, MedicareCategory, LocationID
from Billing..vwHistoryIn
Where IDNum = @IDNum --and (TransDate < = BillingDate  or BillingDate is null) 
          and DrCr <> 'P'
          and RevenueID not in ('PH','PC','CS','CC')  

Order by TransDate;
/*   SELECT (CASE WHEN tbinvstockcard.amount is NULL then 0
                        WHEN tbinvstockcard.revenueid = 'PC' or tbinvstockcard.revenueid = 'CC' then amount * -1 
                 ELSE TBINVSTOCKCARD.AMOUNT END)
             as AMOUNT,
              ltrim(rtrim(isnull(TBcoRevenueCode.ServiceId,''))) as RevenueID,
              isnull(TBINVSTOCKCARD.itemid,'') as ItemId,
              tbinvstockcard.quantity,
             ltrim(rtrim(isnull(tbinvmaster.itemname,'') + ' ' +  isnull(tbinvmaster.itemdesc,''))) as  ItemDesc,
             TBINVSTOCKCARD.TransDate              
    FROM Inventory..TBINVSTOCKCARD tbinvstockcard
      	  LEFT OUTER JOIN Patient_Data..tbPatient tbPatient
              ON TBINVSTOCKCARD.IdNum = tbPatient.IdNum
              LEFT OUTER JOIN Inventory..TBINVMASTER tbInvMaster
              ON tbinvstockcard.itemid = tbinvmaster.itemid
    	  LEFT OUTER JOIN Billing..tbbillInvCode tbcoRevenueCode
             on tbinvstockcard.revenueid = tbcorevenuecode.revenueid and tbinvstockcard.locationID = tbcorevenuecode.locationID
              
   WHERE TBINVSTOCKCARD.IDNUM= @IDNUM 

   Union All

   SELECT (CASE WHEN tbbilldailybill.drcr = NULL then 0
                         WHEN tbbilldailybill.drcr = 'D' then tbbilldailybill.amount
                         WHEN tbbilldailybill.drcr = 'C'  then (tbbilldailybill.amount * -1)
                         WHEN TBbilldailybill.drcr = 'P' and TBbilldailybill.revenueid = 'MD' then TBbilldailybill.amount
                         WHEN tbbilldailybill.drcr = 'P' and tbbilldailybill.revenueid <> 'MD'  then (tbbilldailybill.amount * -1)
                 END)
               as AMOUNT,
               isnull(tbbilldailybill.RevenueId,'') as RevenueID,
               isnull(tbbilldailybill.itemid,'') as ItemId,
               tbbilldailybill.quantity,
          (CASE WHEN rtrim(ltrim(isnull(tbbilldailybill.ItemID,''))) = '' then tbcorevenuecode.itemname
                WHEN TbBillDailyBill.drcr =  'P' then Tbcorevenuecode.ItemName + ' - ' + 'DR. ' + isnull(TbcoDoctor.LastName,'') + ', ' + isnull(TbcoDoctor.Firstname,'') +' ' +  isnull(TbcoDoctor.MiddleName,'')
                WHEN tbBillDailyBill.RevenueID = 'CP'  then 'COMPANY PAYMENT - ' + tbCoCompany.Company
                Else tbBillExamListing.Description
               End) as ItemDesc,
               Tbbilldailybill.TransDate
    FROM billing..tbBillDailyBill tbBillDailyBill
	  LEFT OUTER JOIN Patient_Data..tbPatient tbPatient
           	  ON tbBillDailyBill.IdNum = tbPatient.IdNum	
	  LEFT OUTER JOIN Billing..tbBillExamListing tbBillExamListing
              ON tbBillDailyBill.RevenueId = tbBillExamListing.RevenueId and tbBillDailyBill.ItemId = tbBillExamListing.ItemId
	  LEFT OUTER JOIN Billing..tbCoCompany tbCoCompany
              ON tbBillDailyBill.ItemID = tbCoCompany.CompanyId
	  LEFT OUTER JOIN Billing..tbCoDoctor tbcoDoctor
              ON tbBillDailyBill.ItemID = tbCoDoctor.DoctorID
              LEFT OUTER JOIN Billing..tbCoRevenueCode tbCoRevenueCode
              ON tbBillDailyBill.RevenueID = tbCoRevenueCode.RevenueID
                 
     WHERE TBBILLDAILYBILL.IDNUM= @IDNUM 

Order by Transdate, RevenueId, ItemDesc;*/
   /******/

   declare @strCode as varchar(10)
   declare @strDescription as varchar(100)
   declare @fltQuantity as float
   declare @fltAmount as float
   declare @strRevenueID as varchar(2)
   declare @strIdNum as varchar(10)
   declare @datTransDate as datetime
   declare @Position as varchar(2)
   declare @Category as varchar(2)
   declare @LocationID as varchar(5)

   declare @strPosition as varchar(1)
   declare @fltTempQty as float

   declare @intRecordCount as int
   declare @intCtr as int

Set @strIdNum = @IdNum

Open Patient_Charges
Fetch Next from Patient_Charges 
      Into @fltAmount, 
           @strRevenueID,
           @strCode,
           @fltQuantity,
           @strDescription,
           @datTransDate,
	@Position,
	@Category,
	@LocationID

Set @intRecordCount = @@Cursor_Rows
Set @intCtr = 1

Exec Medicare.dbo.Medic_InsertTempTable @strCode, 
                           @strDescription, 
                           @fltQuantity, 
                           @fltAmount, 
                           @strRevenueID, 
                           @strIdNum, 
                           @strUserID, @Position, @Category, @LocationID

Begin Tran

While @intCtr < @intRecordCount
Begin
   Fetch Next from Patient_Charges 
      Into @fltAmount, 
           @strRevenueID,
           @strCode,
           @fltQuantity,
           @strDescription,
           @datTransDate, @Position, @Category, @LocationID
   Exec Medic_InsertTempTable @strCode, 
                           @strDescription, 
                           @fltQuantity, 
                           @fltAmount, 
                           @strRevenueID, 
                           @strIdNum, 
                           @strUserID, @Position, @Category, @LocationID
   Set @intCtr = @intCtr + 1
End

Commit Tran
CLOSE Patient_Charges
DEALLOCATE Patient_Charges
Return 0

Abort_Insert:
   Rollback Tran
   Return -100001



go

-----------------------

USE [Medicare]
GO
/****** Object:  StoredProcedure [dbo].[Medic_InsertTempTable]    Script Date: 05/21/2012 15:29:22 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

alter table  Medicare..Medic_DumpTable add  [LocationID] [varchar](5) NULL
GO

	
-------------------------------------------------------------
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'Medic_InsertTempTable')
EXEC ('CREATE PROC dbo.Medic_InsertTempTable AS SELECT 1')
GO
	


/****** Object:  Stored Procedure dbo.Medic_InsertTempTable    Script Date: 07/01/2000 1:19:50 AM ******/
ALTER PROCEDURE [dbo].[Medic_InsertTempTable]

   @strCode as varchar(10),
   @strDescription as varchar(100),
   @fltQuantity as float,
   @fltAmount as float,
   @strRevenueID as varchar(2),
   @strIdNum as varchar(10),
   @strUserId as varchar(10),
   @Position as varchar(2),
   @Category as varchar(2),
   @LocationID as varchar(5)

AS

   Declare @strRWith as varchar(1);

   declare @strPosition as varchar(1);
   declare @strCategory as varchar(1);
   declare @fltTempQty as float;

--   Set @strCode = IsNull(@strCode,'');
    If @strRevenueID = 'RA'
	Begin
	   Set @strCode = null;
	End;
   Else
	Begin
	   Set @strCode = IsNull(@strCode,'');
	End;

   Set @strDescription = IsNull(@strDescription,'');
   Set @fltQuantity = IsNull(@fltQuantity,0);
   Set @fltAmount = IsNull(@fltAmount,0);
   Set @strRevenueID = IsNull(@strRevenueID,'');
   Set @strIdNum = IsNull(@strIdNum,'');
   Set @strUserId = IsNull(@strUserID,'');

   Begin Tran

   set @fltTempQty = @fltQuantity;

   If @fltQuantity <= 0 
      Begin
         If not (@strRevenueID = 'PC' or @strRevenueID = 'PH' or @strRevenueID = 'CC' or @strRevenueID = 'CS')
           Begin
             set @fltTempQty = 1;
           End
      End;

   Set @strRwith = IsNull((Select Max(IsNull(Rwith,'')) from Build_File..tbCoRevenueCode 
		           Where RevenueID = @strRevenueID
			     Group by RevenueID),'');   

   /* Get MedicareType of Item */
   Set @strPosition =
    ( Case @strRevenueID
         When 'SS' Then 'N'
         When 'RT' Then '1'
         When 'RA' Then '1'
         When 'PT' Then
            IsNull( ( Select IsNull(MedicareType,'')
                            from Build_File..tbCoPTExam
                             Where PTExamID = @strCode ), '' )
         When 'CT' Then
            IsNull( ( Select IsNull(MedicareType,'')
                            from Build_File..tbCoCTExam
                            Where CTExamID = @strCode ), '' )
         When 'AU' Then
           IsNull( ( Select IsNull(MedicareType,'')
                            from Build_File..tbCoAudExam
                            Where AudExamID = @strCode ), '' )
         When 'HS' Then
            IsNull( ( Select IsNull(MedicareType,'')
                            from Build_File..tbCoHSExam
                            Where HSExamID = @strCode ), '' )
          When 'CV' Then
            IsNull( ( Select IsNull(MedicareType,'')
                            from Build_File..tbCoHSExam
                            Where HSExamID = @strCode ), '' )
         When 'NU' Then
            IsNull( ( Select IsNull(MedicareType,'')
                            from Build_File..tbCoNucExam
                            Where NucExamID = @strCode ), '' )
         When 'PA' Then
            IsNull( ( Select IsNull(MedicareType,'')
                            from Build_File..tbCoRTSExam
                            Where RTSExamID = @strCode ), '' )
         When 'XR' Then
            IsNull( ( Select IsNull(MedicareType,'')
                            from Build_File..tbCoXrayExam
                            Where XrayExamID = @strCode ), '' )
         When 'LB' Then
            IsNull( ( Select IsNull(MedicareType,'')
                            from Build_File..tbCoLabExam
                            Where LabExamID = @strCode ), '' )
         When 'HI' Then
            IsNull( ( Select IsNull(MedicareType,'')
                            from Build_File..tbCoLabExam
                            Where LabExamID = @strCode ), '' )
         When 'BB' Then
            IsNull( ( Select IsNull(MedicareType,'')
                            from Build_File..tbCoLabExam
                            Where LabExamID = @strCode ), '' )
         When 'BA' Then
            IsNull( ( Select IsNull(MedicareType,'')
                            from Build_File..tbCoLabExam
                            Where LabExamID = @strCode ), '' )
         When 'US' Then
            IsNull( ( Select IsNull(MedicareType,'')
                            from Build_File..tbCoUltraExam
                            Where UltraExamID = @strCode ), '' )
         When 'PH' Then
            IsNull( ( Select IsNull(MedicareType,'')
                            from Inventory..tbInvMaster
                            Where ItemID = @strCode ), '' )
         When 'PC' Then '2'		/* Returned Medicine */
         When 'CS' Then
            IsNull( ( Select IsNull(MedicareType,'')
                            from Inventory..tbInvMaster
                            Where ItemID = @strCode ), '' )
        When 'HC' Then
            IsNull( ( Select IsNull(MedicareType,'')
                            from Inventory..tbInvMaster
                            Where ItemID = @strCode ), '' )
         When 'CC' Then '3'		/* Returned Supplies */
         When 'MD' then 'N'
         When 'FP' then 'N'

         Else
	    Case @strRWith
	       When 'Y' then
            	 IsNull( ( Select Max(IsNull(MedicareType,''))
                             from Build_File..tbCoOtherRevenue
 		             Where OtherRevenueID = @strCode
			     Group by OtherRevenueID ), '' )
	       Else
		 IsNull( ( Select Max(IsNull(MedicareType,''))
                             from Build_File..tbCoRevenueCode
 		             Where RevenueID = @strRevenueID
			     Group by RevenueID ), '' )
	    End
      End );

   Set @strCategory = 'A';

   /* Get MedicareCategory of Item */
   Set @strCategory =
    ( Case @strRevenueID
         When 'PT' Then
            IsNull( ( Select IsNull(MedicareCategory,'')
                            from Build_File..tbCoPTExam
                             Where PTExamID = @strCode ), '' )
         When 'CT' Then
            IsNull( ( Select IsNull(MedicareCategory,'')
                            from Build_File..tbCoCTExam
                            Where CTExamID = @strCode ), '' )
         When 'AU' Then
           IsNull( ( Select IsNull(MedicareCategory,'')
                            from Build_File..tbCoAudExam
                            Where AudExamID = @strCode ), '' )
         When 'HS' Then
            IsNull( ( Select IsNull(MedicareCategory,'')
                            from Build_File..tbCoHSExam
                            Where HSExamID = @strCode ), '' )
         When 'CV' Then
            IsNull( ( Select IsNull(MedicareCategory,'')
                            from Build_File..tbCoHSExam
                            Where HSExamID = @strCode ), '' )
         When 'NU' Then
            IsNull( ( Select IsNull(MedicareCategory,'')
                            from Build_File..tbCoNucExam
                            Where NucExamID = @strCode ), '' )
         When 'PA' Then
            IsNull( ( Select IsNull(MedicareCategory,'')
                            from Build_File..tbCoRTSExam
                            Where RTSExamID = @strCode ), '' )
         When 'XR' Then
            IsNull( ( Select IsNull(MedicareCategory,'')
                            from Build_File..tbCoXrayExam
                            Where XrayExamID = @strCode ), '' )
         When 'LB' Then
            IsNull( ( Select IsNull(MedicareCategory,'')
                            from Build_File..tbCoLabExam
                            Where LabExamID = @strCode ), '' )
         When 'HI' Then
            IsNull( ( Select IsNull(MedicareCategory,'')
                            from Build_File..tbCoLabExam
                            Where LabExamID = @strCode ), '' )
         When 'BB' Then
            IsNull( ( Select IsNull(MedicareCategory,'')
                            from Build_File..tbCoLabExam
                            Where LabExamID = @strCode ), '' )
         When 'BA' Then
            IsNull( ( Select IsNull(MedicareCategory,'')
                            from Build_File..tbCoLabExam
                            Where LabExamID = @strCode ), '' )
         When 'US' Then
            IsNull( ( Select IsNull(MedicareCategory,'')
                            from Build_File..tbCoUltraExam
                            Where UltraExamID = @strCode ), '' )
         When 'PH' Then
            IsNull( ( Select IsNull(MedicareCategory,'')
                            from Inventory..tbInvMaster
                            Where ItemID = @strCode ), '' )
         When 'CS' Then
            IsNull( ( Select IsNull(MedicareCategory,'')
                            from Inventory..tbInvMaster
                            Where ItemID = @strCode ), '' )
         When 'HC' Then
            IsNull( ( Select IsNull(MedicareCategory,'')
                            from Inventory..tbInvMaster
                            Where ItemID = @strCode ), '' )
         Else   
	    Case @strRWith
	       When 'Y' Then
                  IsNull( ( Select Max(IsNull(MedicareCategory,''))
                              from Build_File..tbCoOtherRevenue
 		              Where OtherRevenueID = @strCode
			      Group by OtherRevenueID ), '' )
	       Else
		  IsNull( ( Select Max(IsNull(MedicareCategory,''))
                              from Build_File..tbCoRevenueCode
 		              Where RevenueID = @strCode
			      Group by RevenueID ), '' )
	    End
      End );

   /* Default Position When code not found */
   If Len(@strPosition) = 0
      Begin
         Select @strPosition = ( Select MedicareType from Build_File..tbCoRevenueCode Where RevenueID = @strRevenueID );
      End;

   /* If Code is blank get Position from tbCoRevenueCode */
   If Len(@strCode) = 0 and (not @strRevenueID = 'RA' or not @strRevenueID = 'RT')
      Begin
         Set @strPosition =
            ( Select MedicareType
	    from Build_File..tbCoRevenueCode
                  Where RevenueID = @strRevenueID );
      End;

   If @fltTempQty = 0
     Begin
        Set @fltTempQty = 1;
     End;

--   If not @strPosition = 'N'		/* Do not include non-medicare items */
      Begin
      If Exists( Select *
	           from Medicare..Medic_DumpTable
	           Where Code = @strCode and
		         RevenueID = @strRevenueID and
                         IdNum = @strIdNum and
			 UserID = @strUserID )
         Begin
           If @fltAmount < 0
              Begin
	         Set @fltTempQty = Abs(@fltTempQty) * -1;
              End;
           If Len(@strCode) = 0
              Begin
	         Update Medicare..Medic_DumpTable
                    Set Quantity = Quantity + @fltTempQty,
                        Amount = Amount + @fltAmount
                    Where RevenueID = @strRevenueID and
                          IdNum = @strIdNum and
                          UserID = @strUserID;
              End
           Else
              Begin
                 Update Medicare..Medic_DumpTable
                    Set Quantity = Quantity + @fltTempQty,
                        Amount = Amount + @fltAmount
                    Where Code = @strCode and
	                  RevenueID = @strRevenueID and
                          IdNum = @strIdNum and
                          UserID = @strUserID;
              End;
         End;
      Else
         Begin	
            If @strRevenueID = 'PC'		/* Returned Medicines */
               Begin
                  Update Medicare..Medic_DumpTable
                     Set Quantity = Quantity - Abs(@fltTempQty),
                            Amount = Amount - Abs(@fltAmount) 
                     Where Code = @strCode and
		      RevenueID = 'PH' and
                      IdNum = @strIdNum and
                      UserID = @strUserID;
               End;
            Else
               If @strRevenueID = 'CC'		/* Returned Supplies */
                  Begin
                     Update Medicare..Medic_DumpTable
                        Set Quantity = Quantity - (Abs(@fltTempQty) ),
                               Amount = Amount - (Abs(@fltAmount) )
                        Where Code = @strCode and
    	                 RevenueID = 'CS'  and
                         IdNum = @strIdNum and
                         UserID = @strUserID;
                  End;
               Else 
                  Begin
                    
                     If @strRevenueID = 'PH' or @strRevenueID = 'HC'
                        Begin
                           Set @strDescription = ( Select IsNull(B.GenericName,'') + ' ' + IsNull(A.ItemDesc,'') + ' (' + A.ItemName + ')'
   				              from Inventory..tbInvMaster A Left Outer Join Inventory..tbForGeneric B
   				              On A.GenericID = B.GenericID
   				              Where A.ItemID = @strCode );
                        End;
                      
                     Insert Medicare..Medic_DumpTable( Code,
        		                     Description,
		                             Quantity,
		                             Amount,
		                             RevenueID,
             			             Position,
			                     IdNum,
                                             UserID,
			     MedicareCategory, LocationID )
	                             Values( @strCode,
		                             @strDescription,
		                             @fltTempQty,
                                             @fltAmount,
	   	                             @strRevenueID,
 			                     @Position, --@strPosition,
			                     @strIdNum,
                                             @strUserID,
			     @Category, @LocationID) --@strCategory );
               End;
      End;

   If @@error<>0 
      Begin
         goto Abort_Insert;
      End;

   End;					/* If not @strPosition = 'N' */

   Commit Tran;
   Return 0;

Abort_Insert:
   Rollback Tran;
   Return -100001;

--------------------------------
go