/* Insurance Balance Write-Off Update Query*/
BEGIN TRAN
SET NOCOUNT ON

DECLARE @PaymentMethodId int
DECLARE @VisitTransactionsId int
DECLARE @TransactionsId int
DECLARE @PayerName varchar(50)
DECLARE @BatchId int
DECLARE @AdjustmentTypeMId int
DECLARE @Amount money

SELECT @PayerName = ?PAYERNAME.TEXT?
SELECT @BatchId = ?BATCH.ITEMDATA.U?
SELECT @AdjustmentTypeMID = ?ADJUSTMENTTYPE.ITEMDATA.U?
SELECT @Amount = ?AMOUNT.TEXT.U?


DECLARE @Visit int
DECLARE @CarrierId int
DECLARE @PVPId int

DECLARE c_visit CURSOR STATIC FORWARD_ONLY LOCAL FOR
	SELECT pv.PatientVisitId, pv.CurrentInsuranceCarriersId,pvp.patientvisitprocsid
	FROM PatientProfile p 
	JOIN PatientVisit pv ON pv.PatientProfileId = p.PatientProfileId
	JOIN InsuranceCarriers ic ON pv.CurrentInsuranceCarriersId = ic.InsuranceCarriersId
                JOIN PatientVisitProcs pvp ON pv.patientvisitId = pvp.patientvisitid and pvp.voided is null
	JOIN PatientVisitProcsAgg pvpa ON pvp.PatientVisitProcsId = pvpa.PatientVisitProcsId
	AND pvpa.InsBalance > 0 AND pvpa.InsBalance < @amount AND pv.visit <?Date.Date?
                  AND  --Filter on doctor
	(
	(?DOCTOR.ITEMDATA? IS NOT NULL AND pv.DoctorID IN (?DOCTOR.ITEMDATA.U?)) OR
	(?DOCTOR.ITEMDATA? IS NULL)
	)
AND  --Filter on procedure
	(
	(?PROCEDURE.ITEMDATA? IS NOT NULL AND pvp.ProceduresId IN (?PROCEDURE.ITEMDATA.U?)) OR
	(?PROCEDURE.ITEMDATA? IS NULL)
	)
AND  --Filter on Insurance Carrier
	(
	(?INSURANCECARRIER.ITEMDATA? IS NOT NULL AND pv.PrimaryInsuranceCarriersId IN (?INSURANCECARRIER.ITEMDATA.U?)) OR
	(?INSURANCECARRIER.ITEMDATA? IS NULL)
	)
	AND  --Filter on Insurance Group
	(
	(?INSURANCEGROUP.ITEMDATA? IS NOT NULL AND ic.InsuranceGroupId IN (?INSURANCEGROUP.ITEMDATA.U?)) OR
	(?INSURANCEGROUP.ITEMDATA? IS NULL)
                )
	
OPEN c_visit
FETCH NEXT FROM c_visit INTO @Visit, @CarrierId,@PVPId
WHILE (@@fetch_status<>-1)
BEGIN

	INSERT	PaymentMethod(	Source, PayerName, PayerId, PayerType, BatchId, PaymentType, 
						Created, CreatedBy, LastModified, LastModifiedBy)
	VALUES	(2 /* Ins */, @PayerName, @CarrierId, 'Insurance', @BatchId, 6 /* Conveyance */, 
		 GetDate(), dbo.GetLogonID(), GetDate(), dbo.GetLogonID())

	SELECT	@PaymentMethodId = (SELECT MAX(PaymentMethodId) FROM PaymentMethod WHERE CreatedBy = dbo.GetLogonID())

	INSERT	VisitTransactions(	PatientVisitId, PaymentMethodId, 
								Payments, Adjustments, Transfers,
								Created, CreatedBy, LastModified, LastModifiedBy)
	VALUES					 (	@Visit, @PaymentMethodId,
								0, 0, 0,
								GetDate(), dbo.GetLogonID(), GetDate(), dbo.GetLogonID())
	
	SELECT	@VisitTransactionsId = @@IDENTITY

	INSERT	Transactions(	VisitTransactionsId, Type, Action, ActionTypeMId, Name,
							Created, CreatedBy, LastModified, LastModifiedBy)
	VALUES				(	@VisitTransactionsId, 'A', 'A', @AdjustmentTypeMId, 'Adjustment',
							GetDate(), dbo.GetLogonID(), GetDate(), dbo.GetLogonID())

	SELECT	@TransactionsId = (SELECT MAX(TransactionsId) FROM Transactions WHERE CreatedBy = dbo.GetLogonID())

	INSERT	TransactionDistributions(	PatientVisitProcsId, TransactionsId, Amount, 
					Created, CreatedBy, LastModified, LastModifiedBy)											
	SELECT	pvp.PatientVisitProcsId, @TransactionsId, pvpa.InsBalance,
					GetDate(), dbo.GetLogonID(), GetDate(), dbo.GetLogonID()
	FROM	PatientVisitProcs pvp 
	INNER JOIN PatientVisitProcsAgg pvpa ON pvp.PatientVisitProcsId = pvpa.PatientVisitProcsId
	WHERE	pvp.PatientVisitId = @Visit AND pvpa.InsBalance > 0
                 AND pvp.patientvisitprocsid = @pvpid
-- DONT NEED THIS HERE ->->->	FETCH NEXT FROM c_visit INTO @Visit, @CarrierId,@PVPId
--Set the correct bill status

EXEC SetBillStatus @Visit
Fetch next from c_visit INTO @Visit,@CarrierId,@PVPId
END

DEALLOCATE c_visit


COMMIT TRAN