USE [Medicare]
GO

/****** Object:  StoredProcedure [dbo].[MedicRep_PaymentDenied]    Script Date: 11/14/2012 11:29:06 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

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



ALTER PROCEDURE [dbo].[MedicRep_PaymentDenied] 

   @StartDate as varchar(10),
   @EndDate as varchar(10),
   @MedType as varchar(1)

AS

--set @startDate = '01/01/2000'
--set @EndDate = '01/10/2007'
--set @MedType = '2'

select RTrim(IsNull(C.LastName,'') + ', ' +
             IsNull(C.FirstName,'') + ' ' +
             IsNull(C.MiddleName,'')) [Patient Name],
isnull(a.transdate,'') [Transmittal Date],
       Convert(varchar(10),B.AdmDate,101) + '-' + 
       Convert(varchar(10),B.DcrDate,101) [Confinement],
        Cast(IsNull((Select Sum(IsNull(Amount,0))
		      from tbMedPayment
		      Where IdNum = A.IdNum
		      Group by IdNum),0) -
          IsNull( ( Select Cast( Sum( IsNull( MedRoomBoard, 0 ) +
		                       IsNull( MedDrug, 0 ) +
			           IsNull( MedOthers, 0 ) +
			           IsNull( MedOR, 0 ) ) as Decimal( 12, 2 ) )
		             from tbmedclaim
			     Where IdNum = A.IdNum ), 0 )as money) [Amount],
       A.IdNum [IdNum], isnull(A.ReasonDenied, '') as ReasonDenied,
       isnull(DateDenied, '') as DateDenied, isnull(DateApproved,'') as DateApproved,
       isnull(DateDeniedMain, '') as DateDeniedMain, isnull(ReasonDeniedMain,'') as ReasonDeniedMain,
       isnull(DateAppealMain, '') as DateAppealMain, isnull(A.MemberNumber,'') as MemberNumber, isnull(E.Name,'') as MemberType, 
	Convert(varchar(10),B.DcrDate,101) as DcrDate
from tbMedPatient A Inner Join Patient_data..tbPatient B
On A.IdNum = B.IdNum Inner Join Patient_data..tbMaster C
On B.HospNum = C.HospNum Left Outer Join tbMedActual D
On A.IdNum = D.IdNum Left Outer Join tbMedType E
on A.MemberType = E.Code
Where A.PaymentDenied = 1 and
      B.DcrDate Between @StartDate and @EndDate + ' 23:59:59.99' and
      A.MemberType = @MedType

Union All

Select RTrim(IsNull(C.LastName,'') + ', ' +
             IsNull(C.FirstName,'') + ' ' +
             IsNull(C.MiddleName,'')) [Patient Name],
isnull(a.transdate,'') [Transmittal Date],
       Convert(varchar(10),B.AdmDate,101) + '-' + 
       Convert(varchar(10),B.DcrDate,101) [Confinement],
          Cast(IsNull((Select Sum(IsNull(Amount,0))
		      from tbMedPayment
		      Where IdNum = A.IdNum
		      Group by IdNum),0) -
          IsNull( ( Select Cast( Sum( IsNull( MedRoomBoard, 0 ) +
		                       IsNull( MedDrug, 0 ) +
			           IsNull( MedOthers, 0 ) +
			           IsNull( MedOR, 0 ) ) as Decimal( 12, 2 ) )
		             from tbmedclaim
			     Where IdNum = A.IdNum ), 0 )as money) [Amount],

       A.IdNum [IdNum], isnull(A.ReasonDenied, '') as ReasonDenied,
       isnull(DateDenied, '') as DateDenied, isnull(DateApproved,'') as DateApproved,
       isnull(DateDeniedMain, '') as DateDeniedMain, isnull(ReasonDeniedMain,'') as ReasonDeniedMain,
       isnull(DateAppealMain, '') as DateAppealMain, isnull(A.MemberNumber,'') as MemberNumber, isnull(E.Name,'') as MemberType,
	Convert(varchar(10),B.DcrDate,101) as DcrDate
from tbMedPatient A Inner Join Patient_data..tbOutPatient B
On A.IdNum = B.IdNum Inner Join Patient_data..tbMaster C
On B.HospNum = C.HospNum Left Outer Join tbMedActual D
On A.IdNum = D.IdNum Left Outer Join tbMedType E
on A.MemberType = E.Code
Where A.PaymentDenied = 1 and
      B.DcrDate Between @StartDate and @EndDate + ' 23:59:59.99' and
      A.MemberType = @MedType

Order by [Patient Name]


GO


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



USE [Medicare]
GO
/****** Object:  StoredProcedure [dbo].[Medic_GetLoadRev]    Script Date: 11/26/2012 10:03:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER PROCEDURE [dbo].[Medic_GetLoadRev] --'567643','jem','LB'
	@IDNum as varchar(10),
	@UserID as varchar(15),
	@RevenueID as varchar(5)
	
AS

Select a.Code,a.Description,
 [Phic Amount    ]=(select isnull(SUM(cast(PhicAmount as money)),0) from medicare..tbmedsoa b where b.idnum = max(a.idnum)
	and b.RevenueID = (a.revenueid) and isnull(b.itemid,'') = isnull(a.code,'')),
a.LocationID,
 Sum(cast(A.amount as money)) [Amount    ],
 [temp] = (select isnull(SUM(cast(PhicAmount as money)),0) from medicare..tbmedsoa b where b.idnum = max(a.idnum)
	and b.RevenueID = (a.revenueid) and isnull(b.itemid,'') = isnull(a.code,''))   from 
Medicare..Medic_DumpTable a 
left outer join medicare..tbmedsoa b on a.idnum = b.idnum and isnull(a.code,'') = isnull(b.itemid,'') and a.RevenueID = b.RevenueID
where a.UserID = @UserID and a.IdNum = @IDNum and a.RevenueID = @RevenueID and amount > 0
group by a.code, a.description, a.revenueid, a.locationid
order by a.code


GO


USE [Medicare]
GO

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


/****** Object:  StoredProcedure [dbo].[MedicRep_PaymentRegister]    Script Date: 11/14/2012 14:55:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO





ALTER PROCEDURE [dbo].[MedicRep_PaymentRegister] --'1','or0638020'

   @MemberType as varchar(1),
   @OrNumber as varchar(20)

AS

/*Select IsNull(A.IndexNumber,'') [Index Number],
       [Adm. #] =
       Case IsNull(C.IdNum,'')
          When '' Then IsNull(B.IdNum,'')
	  Else IsNull(C.IdNum,'')
       End,
       [Confinement] =
       Case IsNull(C.IdNum,'')
          When '' Then 
            Convert(varchar(10),B.AdmDate,101) + '-' +
            Convert(varchar(10),isnull(B.DcrDate,B.AdmDate),101)
          Else
	    Convert(varchar(10),C.AdmDate,101)  + '-' +
                    Convert(varchar(10),isnull(C.DcrDate,C.AdmDate),101)
       End,
       [Patient Name] =
       Case IsNull(C.IdNum,'')
          When '' Then
            IsNull(D.LastName,'') + ', ' +
            IsNull(D.FirstName,'') + ' ' +
            IsNull(D.MiddleName,'')
          Else
            IsNull(E.LastName,'') + ', ' +
            IsNull(E.FirstName,'') + ' ' +
            IsNull(E.MiddleName,'')
       End,
       IsNull(H.LastName,'') + ', ' +
       IsNull(H.FirstName,'') + ' ' +
       IsNull(H.MiddleName,'') [Member],
       [Receivable] =
	(Case When (Select Count(Idnum) from Medicare..tbMedPayment  Where IdNum = A.IdNum and Denied = 0) > 1
		Then Cast(  IsNull( ( Select Sum(IsNull(Amount,0))  from Medicare..tbMedPayment    Where IdNum = A.IdNum and
			                   Denied = 0 and OrNumber = @ORnumber), 0 ) as money )
			+
			(Cast(
			        ( Case  
			            When A.PTB = 1 Or F.MR = 1 Then --0
					Isnull((Select Cast(MedRoomBoard  + MedDrug + MedOthers + MedOR as Money)   --->> Corrected for MR
					From tbmedclaim where idnum = A.Idnum),0) -
				              ( IsNull(F.RefundRoomBoard,0) + IsNull(F.RefundDrug,0) +
				                   IsNull(F.RefundOthers,0) + IsNull(F.RefundOR,0) ) 
			            Else
				               ( ( IsNull(F.MedRoomBoard,0) + IsNull(F.MedDrug,0) +
				                   IsNull(F.MedOthers,0) + IsNull(F.MedOR,0) ) -
				                 ( IsNull(F.RefundRoomBoard,0) + IsNull(F.RefundDrug,0) +
				                   IsNull(F.RefundOthers,0) + IsNull(F.RefundOR,0) ) )
			-
			Cast(
			       IsNull( ( Select Sum(IsNull(Amount,0))	
			                    from Medicare..tbMedPayment
				       Where IdNum = A.IdNum and
			                   Denied = 0 ), 0 ) as money )
			          End ) as money ) )
		Else
        Cast(
        ( Case  
            When A.PTB = 1 Or F.MR = 1 Then --0
		Isnull((Select Cast(MedRoomBoard  + MedDrug + MedOthers + MedOR as Money)   --->> Corrected for MR
		From tbmedclaim where idnum = A.Idnum),0) -
                 ( IsNull(F.RefundRoomBoard,0) + IsNull(F.RefundDrug,0) +
                   IsNull(F.RefundOthers,0) + IsNull(F.RefundOR,0) ) 
            Else
               ( ( IsNull(F.MedRoomBoard,0) + IsNull(F.MedDrug,0) +
                   IsNull(F.MedOthers,0) + IsNull(F.MedOR,0) ) -
                 ( IsNull(F.RefundRoomBoard,0) + IsNull(F.RefundDrug,0) +
                   IsNull(F.RefundOthers,0) + IsNull(F.RefundOR,0) ) )
          End ) as money )
	End),
       [Payment] =
       Cast(
       IsNull( ( Select Sum(IsNull(Amount,0))
                    from Medicare..tbMedPayment
	       Where IdNum = A.IdNum and
	                   Denied = 0 and OrNumber = @ORnumber), 0 ) as money ),
       IsNull(G.OrNumber,'') [O.R. #],
       [Date Paid] =
       Case IsNull(G.DatePaid,'')
          When '' Then ''
	  Else Convert(varchar(10),G.DatePaid,101)
       End,
       [TotalPayment] =
       Cast(
       IsNull( ( Select Sum(IsNull(Amount,0))
                    from Medicare..tbMedPayment
	       Where IdNum = A.IdNum and
	                   Denied = 0 and Ornumber =  @OrNumber), 0 ) as money  ),
[Tax] = (select sum(Tax) from medicare..tbmedpayment where idnum = a.idnum)
     
from Medicare..tbMedPatient A Left Outer Join Patient_Data..tbPatient B
On A.IdNum = B.IdNum Left Outer Join Patient_Data..tbOutPatient C
On A.IdNum = C.IdNum Left Outer Join Patient_Data..tbMaster D
On B.HospNum = D.HospNum Left Outer Join Patient_Data..tbMaster E
On C.HospNum = E.HospNum Left Outer Join Medicare..tbMedActual F
On A.IdNum = F.IdNum Left Outer Join Medicare..tbMedPayment G
On A.IdNum = G.IdNum Left Outer Join Medicare..tbMedMember H
On A.MemberNumber = H.MemberNumber
Where A.MemberType = @MemberType and
            G.OrNumber = @OrNumber
Order by [Patient Name] */


Select IsNull(A.IndexNumber,'') [Index Number],
       [Adm. #] =
       A.Idnum,
       [Confinement] =
            Convert(varchar(10),A.AdmDate,101) + '-' +
            Convert(varchar(10),isnull(A.DcrDate,B.AdmDate),101),
       [Patient Name] =
            IsNull(A.LastName,'') + ', ' +
            IsNull(A.FirstName,'') + ' ' +
            IsNull(A.MiddleName,''),
       IsNull(H.LastName,'') + ', ' +
       IsNull(H.FirstName,'') + ' ' +
       IsNull(H.MiddleName,'') [Member],
       [Receivable] =
	(Case When (Select Count(Idnum) from Medicare..tbMedPayment  Where IdNum = A.IdNum and Denied = 0) > 1
		Then Cast(  IsNull( ( Select Sum(IsNull(Amount,0))  from Medicare..tbMedPayment    Where IdNum = A.IdNum and
			                   Denied = 0 and OrNumber = @ORnumber), 0 ) as money )
			+
			(Cast(
			        ( Case  
			            When A.PTB = 1 Or F.MR = 1 Then --0
					Isnull((Select Cast(MedRoomBoard  + MedDrug + MedOthers + MedOR as Money)   --->> Corrected for MR
					From tbmedclaim where idnum = A.Idnum),0) -
				              ( IsNull(F.RefundRoomBoard,0) + IsNull(F.RefundDrug,0) +
				                   IsNull(F.RefundOthers,0) + IsNull(F.RefundOR,0) ) 
			            Else
				               ( ( IsNull(F.MedRoomBoard,0) + IsNull(F.MedDrug,0) +
				                   IsNull(F.MedOthers,0) + IsNull(F.MedOR,0) ) -
				                 ( IsNull(F.RefundRoomBoard,0) + IsNull(F.RefundDrug,0) +
				                   IsNull(F.RefundOthers,0) + IsNull(F.RefundOR,0) ) )
			-
			Cast(
			       IsNull( ( Select Sum(IsNull(Amount,0))	
			                    from Medicare..tbMedPayment
				       Where IdNum = A.IdNum and
			                   Denied = 0 ), 0 ) as money )
			          End ) as money ) )
		Else
        Cast(
        ( Case  
            When A.PTB = 1 Or F.MR = 1 OR Right(A.Idnum,1) = 'D'  Then --0
		Isnull((Select Cast(MedRoomBoard  + MedDrug + MedOthers + MedOR as Money)   --->> Corrected for MR
		From tbmedclaim where idnum = A.Idnum),0) -
                 ( IsNull(F.RefundRoomBoard,0) + IsNull(F.RefundDrug,0) +
                   IsNull(F.RefundOthers,0) + IsNull(F.RefundOR,0) ) 
            Else
               ( ( IsNull(F.MedRoomBoard,0) + IsNull(F.MedDrug,0) +
                   IsNull(F.MedOthers,0) + IsNull(F.MedOR,0) ) -
                 ( IsNull(F.RefundRoomBoard,0) + IsNull(F.RefundDrug,0) +
                   IsNull(F.RefundOthers,0) + IsNull(F.RefundOR,0) ) )
          End ) as money )
	End),
       [Payment] =
       Cast(
       IsNull( ( Select Sum(IsNull(Amount,0))
                    from Medicare..tbMedPayment
	       Where IdNum = A.IdNum and
	                   Denied = 0 and OrNumber = @ORnumber), 0 ) as money ),
       IsNull(G.OrNumber,'') [O.R. #],
       [Date Paid] =
       Case IsNull(G.DatePaid,'')
          When '' Then ''
	  Else Convert(varchar(10),G.DatePaid,101)
       End,
       [TotalPayment] =
       Cast(
       IsNull( ( Select Sum(IsNull(Amount,0))
                    from Medicare..tbMedPayment
	       Where IdNum = A.IdNum and
	                   Denied = 0 and Ornumber =  @OrNumber), 0 ) as money  ),
	   [PF Payment] = (select sum(payment) from medicare..tbmeddoctors where idnum = a.idnum and ornumber = g.ornumber),
     [Tax] = (select sum(Tax) from medicare..tbmedpayment where idnum = a.idnum)
from Medicare..tbMedPatient A Left Outer Join Patient_Data..tbPatient B
On A.IdNum = B.IdNum Left Outer Join Patient_Data..tbOutPatient C
On A.IdNum = C.IdNum Left Outer Join Patient_Data..tbMaster D
On B.HospNum = D.HospNum Left Outer Join Patient_Data..tbMaster E
On C.HospNum = E.HospNum Left Outer Join Medicare..tbMedActual F
On A.IdNum = F.IdNum Left Outer Join Medicare..tbMedPayment G
On A.IdNum = G.IdNum Left Outer Join Medicare..tbMedMember H
On A.MemberNumber = H.MemberNumber
Where A.MemberType = @MemberType and
            G.OrNumber = @OrNumber
Order by [Patient Name]


GO


USE [Medicare]
GO

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


/****** Object:  StoredProcedure [dbo].[MedicRep_GetNewUnderPaidAll]    Script Date: 11/26/2012 10:03:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON

GO




/****** Object:  Stored Procedure dbo.MedicRep_GetNewUnderPaid    Script Date: 10/11/2010 1:19:55 AM ******/
ALTER PROCEDURE [dbo].[MedicRep_GetNewUnderPaidAll] 

   @strStartDate as varchar(10),
   @strEndDate as varchar(10)

AS

Select IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,'') [Patient Name],
       IsNull(A.IDNum,'') IDNum,
        IsNull(Z.LastName,'') + ', ' + IsNull(Z.FirstName,'') + ' ' + IsNull(Z.MiddleName,'') [Member Name],
       IsNull(A.MemberNumber,'') MemberNum,
       Cast( IsNull( MedRoomBoard, 0)  As Decimal( 12, 2 ) ) MedRoomBoard,
	   Cast( IsNull( MedDrug, 0 ) As Decimal( 12, 2 ) ) MedDrug,
	   Cast( IsNull( MedOthers, 0 ) As Decimal( 12, 2 ) ) MedOthers,
	   Cast( IsNull( MedOR, 0 ) As Decimal( 12, 2 ) ) MedOR,
       IsNull( ( Select Cast( Sum( IsNull(RoomBoardAmt,0) ) as Decimal( 12, 2 ) )
		             from Medicare..tbMedPayment 
			     Where IdNum = A.IDNum ), 0 ) AS RoomBoardAmt,
	   IsNull( ( Select Cast( Sum( IsNull(DrugAmt,0) ) as Decimal( 12, 2 ) )
		             From Medicare..tbMedPayment 
			     Where IDNum = A.IDNum ), 0 ) AS DrugAmt,
	   IsNull( ( Select Cast( Sum( IsNull(OthersAmt,0) ) as Decimal( 12, 2 ) )
		             From Medicare..tbMedPayment 
			     Where IDNum = A.IDNum ), 0 ) AS OthersAmt,
	   IsNull( ( Select Cast( Sum( IsNull(ORAmt,0) ) as Decimal( 12, 2 ) )
		             From Medicare..tbMedPayment 
			     Where IDNum = A.IDNum ), 0 ) AS ORAmt,
	   Convert(Varchar(10), A.AdmDate, 101) + ' - ' + Convert(Varchar(10), A.DcrDate, 101) ConfinementDate,
	   dbo.fn_GetAPVNum(A.IDNum) APVNum, dbo.fn_GetPaymentDate(A.IDNum) ORDate,
	   dbo.fn_GetPaymentORNumber(A.IDNum) ORNum, 
       dbo.fn_GetMemberType(A.MemberType) MemberType
  From Medicare..tbMedPatient as A 
  Left Outer Join
	(Select IDNum, Sum(Amount) Amount, Sum(RoomBoardAmt) RoomBoardAmt, Sum(DrugAmt) DrugAmt, 
		Sum(OthersAmt) OthersAmt, Sum(ORAmt) ORAmt
	 From
		(Select IDNum, IsNull(Amount,0) Amount, IsNull(DrugAmt,0) DrugAmt, 
			IsNull(RoomBoardAmt,0) RoomBoardAmt, IsNull(OthersAmt,0) OthersAmt, 
			IsNull(ORAmt,0) ORAmt 
		 From Medicare..tbMedPayment 
		 Where DatePaid Between @strStartDate and @strEndDate + ' 23:59:59.99'
		 ) X 
		 Group By IDNum
	 ) As B On B.IdNum = A.IdNum 
  Left Outer Join Medicare..tbMedActual As C  
	On C.IdNum = A.IdNum left outer join Medicare..tbMedMember Z on z.MemberNumber = A.membernumber
  Where  ( ( Case IsNull(A.IdNum,'') 
	      When '' Then 0
              Else
                 Cast( IsNull( MedRoomBoard, 0 ) As Decimal( 12, 2 ) )  +
		  		 Cast(  IsNull( MedDrug, 0 ) As Decimal( 12, 2 ) )+
				 Cast(  IsNull( MedOthers, 0 ) As Decimal( 12, 2 ) ) +
				 Cast(  IsNull( MedOR, 0 ) As Decimal( 12, 2 ) )

              End ) - 
          ( Case IsNull(A.IdNum,'') 
	      When '' Then 0
              Else
                 IsNull( (Select IsNull( Cast( Sum(IsNull(Amount,0)) + Sum(IsNull(Tax,0)) as Decimal( 12, 2 ) ), 0 )
					From Medicare..tbMedPayment 
				  Where IDNum = A.IDNum ), 0)
           End ) ) > 0 And B.Amount > 0
  Order by [Patient Name]


GO

USE [Medicare]
GO

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


/****** Object:  StoredProcedure [dbo].[MedicRep_GetNewUnderPaid]    Script Date: 11/26/2012 10:02:58 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO








/****** Object:  Stored Procedure dbo.MedicRep_GetNewUnderPaid    Script Date: 10/11/2010 1:19:55 AM ******/
ALTER PROCEDURE [dbo].[MedicRep_GetNewUnderPaid] --'01/22/2012','10/01/2012','1'

   @strStartDate as varchar(10),
   @strEndDate as varchar(10),
   @strMedType as varchar(1)

As



Select IsNull(A.LastName,'') + ', ' + IsNull(A.FirstName,'') + ' ' + IsNull(A.MiddleName,'') [Patient Name],
       IsNull(A.IDNum,'') IDNum,
       IsNull(Z.LastName,'') + ', ' + IsNull(Z.FirstName,'') + ' ' + IsNull(Z.MiddleName,'') [Member Name],
       IsNull(A.MemberNumber,'') MemberNum,
       Cast( IsNull( MedRoomBoard, 0)  As Decimal( 12, 2 ) ) MedRoomBoard,
	   Cast( IsNull( MedDrug, 0 ) As Decimal( 12, 2 ) ) MedDrug,
	   Cast( IsNull( MedOthers, 0 ) As Decimal( 12, 2 ) ) MedOthers,
	   Cast( IsNull( MedOR, 0 ) As Decimal( 12, 2 ) ) MedOR,
       IsNull( ( Select Cast( Sum( IsNull(RoomBoardAmt,0) ) as Decimal( 12, 2 ) )
		             from Medicare..tbMedPayment 
			     Where IdNum = A.IDNum ), 0 ) AS RoomBoardAmt,
	   IsNull( ( Select Cast( Sum( IsNull(DrugAmt,0) ) as Decimal( 12, 2 ) )
		             From Medicare..tbMedPayment 
			     Where IDNum = A.IDNum ), 0 ) AS DrugAmt,
	   IsNull( ( Select Cast( Sum( IsNull(OthersAmt,0) ) as Decimal( 12, 2 ) )
		             From Medicare..tbMedPayment 
			     Where IDNum = A.IDNum ), 0 ) AS OthersAmt,
	   IsNull( ( Select Cast( Sum( IsNull(ORAmt,0) ) as Decimal( 12, 2 ) )
		             From Medicare..tbMedPayment 
			     Where IDNum = A.IDNum ), 0 ) AS ORAmt,
	   Convert(Varchar(10), A.AdmDate, 101) + ' - ' + Convert(Varchar(10), A.DcrDate, 101) ConfinementDate,
	   dbo.fn_GetAPVNum(A.IDNum) APVNum, dbo.fn_GetPaymentDate(A.IDNum) ORDate,
	   dbo.fn_GetPaymentORNumber(A.IDNum) ORNum
  From Medicare..tbMedPatient as A 
  Left Outer Join
	(Select IDNum, Sum(Amount) Amount, Sum(RoomBoardAmt) RoomBoardAmt, Sum(DrugAmt) DrugAmt, 
		Sum(OthersAmt) OthersAmt, Sum(ORAmt) ORAmt
	 From
		(Select IDNum, IsNull(Amount,0) Amount, IsNull(DrugAmt,0) DrugAmt, 
			IsNull(RoomBoardAmt,0) RoomBoardAmt, IsNull(OthersAmt,0) OthersAmt, 
			IsNull(ORAmt,0) ORAmt 
		 From Medicare..tbMedPayment 
		 Where DatePaid Between @strStartDate and @strEndDate + ' 23:59:59.99'
		 ) X 
		 Group By IDNum
	 ) As B On B.IdNum = A.IdNum 
	 left outer join Medicare..tbMedMember Z on A.MemberNumber = Z.membernumber
  Left Outer Join Medicare..tbMedActual As C  
	On C.IdNum = A.IdNum 
  Where  ( ( Case IsNull(A.IdNum,'') 
	      When '' Then 0
              Else
                 Cast( IsNull( MedRoomBoard, 0 ) As Decimal( 12, 2 ) )  +
		  		 Cast(  IsNull( MedDrug, 0 ) As Decimal( 12, 2 ) )+
				 Cast(  IsNull( MedOthers, 0 ) As Decimal( 12, 2 ) ) +
				 Cast(  IsNull( MedOR, 0 ) As Decimal( 12, 2 ) )

              End ) - 
          ( Case IsNull(A.IdNum,'') 
	      When '' Then 0
              Else
                 IsNull( (Select IsNull( Cast( Sum(IsNull(Amount,0)) + Sum(IsNull(Tax,0)) as Decimal( 12, 2 ) ), 0 )
					From Medicare..tbMedPayment 
				  Where IDNum = A.IDNum ), 0)
           End ) ) > 0 and
        A.MemberType = @strMedType And B.Amount > 0
  Order by [Patient Name]


GO

USE [Medicare]
GO
IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_SCHEMA = 'dbo' AND ROUTINE_NAME = 'MedicRep_GetUnPaidNEW')
EXEC ('CREATE PROC dbo.[MedicRep_GetUnPaidNEW] AS SELECT 1')
GO


/****** Object:  StoredProcedure [dbo].[MedicRep_GetUnPaidNEW]    Script Date: 11/26/2012 09:36:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO




ALTER PROCEDURE [dbo].[MedicRep_GetUnPaidNEW] 

   @StartDate as varchar(10),
   @EndDate as varchar(10)

As

Select distinct IsNull(D.LastName,'') [LastName], 
	   IsNull(D.FirstName,'') [FirstName],
	   IsNull(D.MiddleName,'') [Middle Name],
       case when right(D.lastname,1) = '.' then
			right(D.lastname,3)
		else ''
		end as [ExtPatient],
       IsNull(A.IdNum,'') [AdmNum],
       ISNULL(E.MemberNumber,'') [MemberNum],   
       IsNull(F.LastName,'') [MLastName], 
	   IsNull(F.FirstName,'') [MFirstName],
	   IsNull(F.MiddleName,'') [MMiddle Name],
	   case when right(F.lastname,1) = '.' then
			right(F.lastname,3)
		else ''
		end as [ExtMember],    
		convert(Varchar(10), C.AdmDate, 101) [AdmDate],
		convert(Varchar(10), C.DcrDate, 101) [DcrDate],
		convert(Varchar(10), F.BirthDate, 101) [BirthDate],
       IsNull( ( Select Cast( Sum( IsNull( MedRoomBoard, 0 ) +
		                       IsNull( MedDrug, 0 ) +
			           IsNull( MedOthers, 0 ) +
			           IsNull( MedOR, 0 ) ) as Decimal( 12, 2 ) )
		             from tbMedActual
			     Where IdNum = A.IdNum ), 0 ) [PHIC Benefit],
        IsNull( ( Select Cast( Sum( IsNull( ActualRoomBoard, 0 ) +
		                       IsNull( ActualDrug, 0 ) +
			           IsNull( ActualOthers, 0 ) +
			           IsNull( ActualOR, 0 ) ) as Decimal( 12, 2 ) )
		             from tbMedActual
			     Where IdNum = A.IdNum ), 0 ) [Total Charge]
		
  from tbMedActual A Inner Join Patient_Data..tbPatient C
  On A.IdNum = C.IdNum Inner Join Patient_Data..tbMaster D
  On C.HospNum = D.HospNum inner join Medicare..tbMedPatient E on A.IdNum = E.IdNum
  inner join Medicare..tbMedMember F on E.MemberNumber = F.MemberNumber
  inner join Medicare..tbMedPayment G on C.IdNum = G.IdNum
  Where  ( ( Case IsNull(A.IdNum,'') 
	      When '' Then 0
              Else
                 IsNull( ( Select Cast( Sum(      (  IsNull( MedRoomBoard, 0 ) - IsNull( RefundRoomBoard, 0) ) +
		  		          (  IsNull( MedDrug, 0 ) - IsNull( RefundDrug, 0) ) +
				          (  IsNull( MedOthers, 0 ) - IsNull( RefundOthers, 0) ) +
				          (  IsNull( MedOR, 0 ) - IsNull( RefundOR, 0) ) ) as Decimal( 12, 2 ) )
		             from Medicare..tbMedActual
			     Where IdNum = A.IdNum ), 0 )
              End ) - 
          ( Case IsNull(A.IdNum,'') 
	      When '' Then 0
              Else
                 IsNull( ( Select Cast( Sum( IsNull(Amount,0) ) as Decimal( 12, 2 ) )
		             from Medicare..tbMedPayment
			     Where IdNum = A.IdNum )+ ( Select Cast( Sum( IsNull(Tax,0) ) as Decimal( 12, 2 ) )
		             from Medicare..tbMedPayment
			     Where IdNum = A.IdNum ), 0 )
           End ) ) > 0 and
               IsNull( ( Select Cast( Sum( IsNull(Amount,0) ) as money )
		             from tbMedPayment
			     Where IdNum = A.IdNum ) + ( Select Cast( Sum( IsNull(Tax,0) ) as money )
		             from tbMedPayment
			     Where IdNum = A.IdNum ), 0 )  > 0 and
        (G.DatePaid Between @StartDate and @EndDate + ' 23:59:59.99')

Union All

Select distinct IsNull(D.LastName,'') [LastName], 
	   IsNull(D.FirstName,'') [FirstName],
	   IsNull(D.MiddleName,'') [Middle Name],
	    case when right(D.lastname,1) = '.' then
			right(D.lastname,3)
		else ''
		end as [ExtPatient],
       IsNull(A.IdNum,'') [AdmNum],
       ISNULL(E.MemberNumber,'') [MemberNum],   
       IsNull(F.LastName,'') [MLastName], 
	   IsNull(F.FirstName,'') [MFirstName],
	   IsNull(F.MiddleName,'') [MMiddle Name],   
	   case when right(F.lastname,1) = '.' then
			right(F.lastname,3)
		else ''
		end as [ExtMember],    
		convert(Varchar(10), C.AdmDate, 101) [AdmDate],
		convert(Varchar(10), C.DcrDate, 101) [DcrDate],
		convert(Varchar(10), F.BirthDate, 101) [BirthDate],     
       IsNull( ( Select Cast( Sum( IsNull( MedRoomBoard, 0 ) +
		                       IsNull( MedDrug, 0 ) +
			           IsNull( MedOthers, 0 ) +
			           IsNull( MedOR, 0 ) ) as Decimal( 12, 2 ) )
		             from tbMedActual
			     Where IdNum = A.IdNum ), 0 ) [PHIC Benefit],
        IsNull( ( Select Cast( Sum( IsNull( ActualRoomBoard, 0 ) +
		                       IsNull( ActualDrug, 0 ) +
			           IsNull( ActualOthers, 0 ) +
			           IsNull( ActualOR, 0 ) ) as Decimal( 12, 2 ) )
		             from tbMedActual
			     Where IdNum = A.IdNum ), 0 ) [Total Charge]
        
  from tbMedActual A Inner Join Patient_Data..tbOutPatient C
  On A.IdNum = C.IdNum Inner Join Patient_Data..tbMaster D
  On C.HospNum = D.HospNum inner join Medicare..tbMedPatient E on A.IdNum = E.IdNum
  inner join Medicare..tbMedMember F on E.MemberNumber = F.MemberNumber
  inner join Medicare..tbMedPayment G on C.IdNum = G.IdNum
Where  ( ( Case IsNull(A.IdNum,'') 
	      When '' Then 0
              Else
                 IsNull( ( Select Cast( Sum(      (  IsNull( MedRoomBoard, 0 ) - IsNull( RefundRoomBoard, 0) ) +
		  		          (  IsNull( MedDrug, 0 ) - IsNull( RefundDrug, 0) ) +
				          (  IsNull( MedOthers, 0 ) - IsNull( RefundOthers, 0) ) +
				          (  IsNull( MedOR, 0 ) - IsNull( RefundOR, 0) ) ) as Decimal( 12, 2 ) )
		             from Medicare..tbMedActual
			     Where IdNum = A.IdNum ), 0 )
              End ) - 
          ( Case IsNull(A.IdNum,'') 
	      When '' Then 0
              Else
                 IsNull( ( Select Cast( Sum( IsNull(Amount,0) ) as Decimal( 12, 2 ) )
		             from Medicare..tbMedPayment
			     Where IdNum = A.IdNum )+ ( Select Cast( Sum( IsNull(Tax,0) ) as Decimal( 12, 2 ) )
		             from Medicare..tbMedPayment
			     Where IdNum = A.IdNum ), 0 )
           End ) ) > 0 and
               IsNull( ( Select Cast( Sum( IsNull(Amount,0) ) as money )
		             from tbMedPayment
			     Where IdNum = A.IdNum ) + ( Select Cast( Sum( IsNull(Tax,0) ) as money )
		             from tbMedPayment
			     Where IdNum = A.IdNum ), 0 )  > 0 and
        (g.datepaid Between @StartDate and @EndDate + ' 23:59:59.99') 





GO