/* Claim Status Rejected */
SET NOCOUNT ON
SELECT distinct	pp.Last + ', ' + pp.First as PatientName,
	pp.PatientId,
	pv.TicketNumber,
	pv.Visit,
	ic.ListName AS CarrierName,
	ic.InsuranceCarriersId,
	ic.ECSPayorId, 
	td.code,
	td.adjudicationcode,
	td.adjustmentgroupcode1,
	td.adjustmentreasoncode1,
	td.adjustmentamount1,
	td.adjustmentgroupcode2,
	td.adjustmentreasoncode2,
	td.adjustmentamount2,
	td.adjustmentgroupcode3,
	td.adjustmentreasoncode3,
	td.adjustmentamount3,
	td.adjustmentgroupcode4,
	td.adjustmentreasoncode4,
	td.adjustmentamount4,
	td.adjustmentgroupcode5,
	td.adjustmentreasoncode5,
	td.adjustmentamount5,
	td.adjustmentgroupcode6,
	td.adjustmentreasoncode6,
	td.adjustmentamount6,
	td.amount,
	ISNULL(ig.Name,'None') AS InsuranceGroup,
	ISNULL(ic.InsuranceGroupId, 0) AS InsuranceGroupId,
	Flag=convert(varchar(50),'Clearinghouse')

FROM PatientVisit pv
JOIN PatientProfile pp ON pv.PatientProfileId = pp.PatientProfileId
JOIN InsuranceCarriers ic ON pv.CurrentInsuranceCarriersId = ic.InsuranceCarriersId
LEFT JOIN InsuranceGroup ig ON ic.InsuranceGroupId = ig.InsuranceGroupId
right join patientvisitprocs pvp on pv.patientvisitid = pvp.patientvisitid
left join transactiondistributions td on pvp.patientvisitprocsid = td.patientvisitprocsid
inner  join visittransactions vt on pv.patientvisitid = vt.patientvisitid


WHERE	pv.BillStatus = 8 and td.adjustmentgroupcode1 is not null and
	pv.Entered >= ISNULL('03/01/2007','1/1/1900') and 
	pv.Entered < dateadd(d, 1,ISNULL('03/02/2007','1/1/3000')) and
	/*Create SecondDateRange Parameter */
	pv.Visit >= ISNULL(NULL,'1/1/1900') and 
	pv.Visit < dateadd(d, 1,ISNULL(NULL,'1/1/3000'))
	AND  --Filter on insurance carrier
	(
	('4' IS NOT NULL AND ic.InsuranceCarriersId IN (4)) OR
	('4' IS NULL)
	)
	AND  --Filter on insurance group
	(
	(NULL IS NOT NULL AND ig.InsuranceGroupId IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on doctor
	(
	(NULL IS NOT NULL AND pv.DoctorID IN (NULL)) OR
	(NULL IS NULL)
	)
	AND  --Filter on company
	(
	(NULL IS NOT NULL AND pv.CompanyID IN (NULL)) OR
	(NULL IS NULL)
	)