/* Billing Status Report 11/22/03 CySolutions 
Revised 5/6/06 */
SET NOCOUNT ON

CREATE TABLE #Records
       (
         TicketNumber VARCHAR(20) ,
         Visit DATETIME ,
         LastDateFiled DATETIME ,
         FirstDateFiled DATETIME ,
         InsBalance MONEY ,
         PatBalance MONEY ,
         PrimaryInsuranceCarrier VARCHAR(50) ,
         CurrentInsuranceCarrier VARCHAR(50) ,
         CurrentCarrier NUMERIC(1 , 0) ,
         BillStatus VARCHAR(50) ,
         BillStatusID INT ,
         Entered DATETIME ,
         FilingType INT ,
         DaysSinceFiled INT ,
         PatientName VARCHAR(50) ,
         VisitOwner VARCHAR(50) ,
         Description VARCHAR(100) ,
         Doctor VARCHAR(100) ,
         Credentialed VARCHAR(1)
       )

-- Insert the New Records
IF '1' = '1'
   OR 1 IN ( NULL ) 
   INSERT INTO
    #Records
    SELECT
        ISNULL(a.TicketNumber , 'None') ,
        CONVERT(VARCHAR , a.ApptStart , 101) ,
        '' ,
        '' ,
        0 ,
        0 ,
        ISNULL(ic.ListName , 'None') ,
        ISNULL(ic.ListName , 'None') ,
        1 ,
        'New' ,
        1 ,
        '' ,
        0 ,
        0 ,
        pp.Last + ', ' + pp.First + ' - ' + pp.PatientID ,
        'Appointment' ,
        '' ,
        d.ListName ,
        CASE WHEN icd.InsuranceCarriersDoctorID IS NOT NULL THEN 'X'
             ELSE ' '
        END
    FROM
        dbo.InsuranceCarriers ic
        INNER JOIN dbo.PatientInsurance pi ON ic.InsuranceCarriersId = pi.InsuranceCarriersId
        RIGHT OUTER JOIN dbo.Appointments a ON pi.PatientProfileId = a.OwnerId
        INNER JOIN dbo.PatientProfile pp ON a.OwnerID = pp.PatientProfileID
        INNER JOIN DoctorFacility d ON ISNULL(a.DoctorID , a.ResourceID) = d.DoctorFacilityID
        LEFT JOIN InsuranceCarriersDoctor icd ON pi.InsuranceCarriersID = icd.InsuranceCarriersID
                                                 AND ISNULL(a.DoctorID , a.ResourceID) = icd.DoctorID
    WHERE
        ( pi.OrderForClaims = 1 )
        AND ( a.PatientVisitId IS NULL )
        AND ( a.ApptKind = 1 )
        AND (
              a.HideNewVisit IS NULL
              OR a.HideNewVisit = 0
            )
        AND (
              a.Canceled IS NULL
              OR a.Canceled = 0
            )
        AND ( a.ApptStart < GETDATE() )
        AND a.ApptStart >= ISNULL(NULL , '1/1/1900')
        AND a.ApptStart < DATEADD(day , 1 , ISNULL(NULL , '1/1/3000'))
        AND  --Filter on insurance carrier
        (
          (
            NULL IS NOT NULL
            AND ic.InsuranceCarriersId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on facility
        (
          (
            NULL IS NOT NULL
            AND a.FacilityID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on insurance group
        (
          (
            NULL IS NOT NULL
            AND ic.InsuranceGroupId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on Doctor
        (
          (
            NULL IS NOT NULL
            AND a.ResourceID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
                
-- Enter the Visit Records
INSERT INTO
    #Records
    SELECT
        pv.TicketNumber ,
        pv.Visit ,
        pv.LastFiledDate ,
        pv.FirstFiledDate ,
        pva.InsBalance ,
        pva.PatBalance ,
        ISNULL(ic.ListName , 'None') ,
        ISNULL(ic.ListName , 'None') ,
        pv.CurrentCarrier ,
        bs.Description ,
        pv.BillStatus ,
        pv.Entered ,
        ISNULL(pv.FilingType , 0) ,
        CASE WHEN pv.LastFiledDate IS NULL THEN 0
             ELSE DATEDIFF(d , pv.LastFiledDate , GETDATE())
        END ,
        pp.Last + ', ' + pp.First + ' - ' + pp.PatientID ,
        ISNULL(vo.Description , 'No Owner') ,
        ISNULL(LEFT(pv.Description , 100) , '') ,
        d.ListName ,
        CASE WHEN icd.InsuranceCarriersDoctorID IS NOT NULL THEN 'X'
             ELSE ' '
        END
    FROM
        dbo.PatientVisit pv
        INNER JOIN dbo.PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
        LEFT OUTER JOIN dbo.InsuranceCarriers ic ON pv.PrimaryInsuranceCarriersId = ic.InsuranceCarriersId
        LEFT OUTER JOIN dbo.PatientVisitAgg pva ON pv.PatientVisitId = pva.PatientVisitId
        LEFT OUTER JOIN dbo.InsuranceCarriers cic ON pv.CurrentInsuranceCarriersId = cic.InsuranceCarriersId
        LEFT OUTER JOIN dbo.MedLists bs ON bs.JoinId = pv.BillStatus
                                           AND bs.TableName = 'BillStatus'
        INNER JOIN DoctorFacility d ON pv.DoctorID = d.DoctorFacilityID
        LEFT OUTER JOIN MedLists vo ON pv.VisitOwnerMID = vo.MedListsID
        LEFT JOIN InsuranceCarriersDoctor icd ON pv.CurrentInsuranceCarriersId = icd.InsuranceCarriersID
                                                 AND pv.DoctorID = icd.DoctorID
    WHERE
        pv.Visit >= ISNULL(NULL , '1/1/1900')
        AND pv.Visit < DATEADD(day , 1 , ISNULL(NULL , '1/1/3000'))
        AND pv.BillStatus NOT IN ( 12 , 10 )
	--Filter on BillStatus
        AND (
              (
                NULL IS NULL
                AND '1' = '1'
              )
              OR (
                   '1' = '2'
                   AND NULL IS NULL
                 )
              OR pv.BillStatus IN ( NULL )
            )
        AND  --Filter on insurance carrier
        (
          (
            NULL IS NOT NULL
            AND ic.InsuranceCarriersId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on facility
        (
          (
            NULL IS NOT NULL
            AND pv.FacilityID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on insurance group
        (
          (
            NULL IS NOT NULL
            AND ic.InsuranceGroupId IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND -- Carrier priority to include
        (
          ( 1 = 1 )
          OR (
               1 = 2
               AND pv.CurrentCarrier = 1
             )
          OR (
               1 = 3
               AND pv.CurrentCarrier > 1
             )
        )
        AND  --Filter on visitowner
        (
          (
            NULL IS NOT NULL
            AND pv.VisitOwnerMID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )
        AND  --Filter on Doctor
        (
          (
            NULL IS NOT NULL
            AND pv.DoctorID IN ( NULL )
          )
          OR ( NULL IS NULL )
        )

IF 0 <> 1 
   SELECT
    *
   FROM
    #Records
ELSE 
   SELECT
    *
   FROM
    #Records
   WHERE
    BillStatus <> 'New'