USE [LABORATORY]
GO
/****** Object:  StoredProcedure [dbo].[Lab_Cancel_ForRefund]    Script Date: 07/03/2018 11:00:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Lab_Cancel_ForRefund]
	@Requestnum int,
	@UserId varchar(8)
AS

declare @Assessid varchar(20),
	@LabExamId varchar(10),
	@RefNum varchar(10),
	@ItemDesc varchar(50),
	@RevenueId varchar(2),
	@Amount float,
	@Quantity float,
	@ORNum varchar(10)
	
	select  @LabExamId = ISNULL(ItemCharged,'') ,
	@Refnum = Refnum
	from tbLABMaster where Requestnum = @Requestnum;
	
	Set @Assessid = (Select ISNULL(Assessid,'') as Assessid from Billing..tbCashAssessment where RefNum=@RefNum and ItemID=@LabexamId);
	Select @Labexamid = L.ItemID, 
		@RefNum = L.Refnum,
		@ItemDesc = E.LabExam,
		@RevenueId = 'LB',
		@ORNum = isnull(D.ORNumber, ''),
		@Amount = L.Amount, 
		@Quantity = L.Quantity
	from Billing..tbCashAssessment L
		left outer join BUILD_FILE..tbCoLabExam E on L.ItemID = E.Labexamid
		left outer join BILLING..tbCashAssessment D on L.RefNum = D.RefNum
	where L.AssessID = @AssessId;

Insert Into tbLabCreditMemo(UserId, CSNum, ItemId, ItemDesc, RevenueId, Amount, Quantity, ORNum)
Values(@UserId, @RefNum, @LabExamId, @ItemDesc, @RevenueId, @Amount, @Quantity, @ORNum);

--Delete BILLING..tbCashAssessment 
--Where RefNum = @RefNum and ItemId = @LabExamId;

Update BILLING..tbCashAssessment 
set RecordStatus ='R'
Where RefNum = @RefNum and ItemId = @LabExamId;

Update tbLabMaster
Set RequestStatus = 'R', RefundedBy = @UserId
Where RequestNum = @Requestnum;



---OLD--- comment by Verna 5/31/2016

--USE [LABORATORY]
--GO
--/****** Object:  StoredProcedure [dbo].[Lab_Cancel_ForRefund]    Script Date: 05/31/2016 10:18:14 ******/
--SET ANSI_NULLS ON
--GO
--SET QUOTED_IDENTIFIER ON
--GO
--ALTER PROCEDURE [dbo].[Lab_Cancel_ForRefund]
--	@AssessID int,
--	@UserId varchar(8)
--AS

--declare @RequestNum varchar(10),
--	@LabExamId varchar(4),
--	@RefNum varchar(10),
--	@ItemDesc varchar(50),
--	@RevenueId varchar(2),
--	@Amount float,
--	@Quantity float,
--	@ORNum varchar(10)

--Select @Labexamid = L.ItemID, 
--	@RefNum = L.Refnum,
--	@ItemDesc = E.LabExam,
--	@RevenueId = L.RevenueID,
--	@ORNum = L.ORNumber,
--	@Amount = L.Amount, 
--	@Quantity = L.Quantity
--from BILLING..tbCashAssessment L
--	left outer join BUILD_FILE..tbCoLabExam E on L.ItemID = E.Labexamid
--where L.AssessId = @AssessId;

--Set @requestNum = (Select top 1 requestNum from tbLabMaster where itemcharged = @Labexamid and RefNum = @RefNum);


---- by FredC 12/14/2004
--if @LabExamID is null
--begin
--	Select @Labexamid = L.ItemID, 
--		@RefNum = L.Refnum,
--		@ItemDesc = E.LabExam,
--		@RevenueId = 'LB',
--		@ORNum = isnull(L.ORNum, ''),
--		@Amount = L.Amount, 
--		@Quantity = L.Quantity
--	from LABORATORY..tbLabMaster L
--		left outer join BUILD_FILE..tbCoLabExam E on L.ItemID = E.Labexamid
--	where L.RequestNum = @AssessId;
--end

--Insert Into tbLabCreditMemo(UserId, CSNum, ItemId, ItemDesc, RevenueId, Amount, Quantity, ORNum)
--Values(@UserId, @RefNum, @LabExamId, @ItemDesc, @RevenueId, @Amount, @Quantity, @ORNum);

--Insert Into BILLING..tbCashAssessmentBackUp(AssessID, HospNum, ChargeSlip, ProfId1, ProfId2, 
--	ProfId3, ProfFee1, ProfFee2, ProfFee3, ItemDescription, WithSurcharge, OPService, IdNum, 
--	Name, TransDate, AssessNum, Indicator, DrCr, RecordStatus, ItemID, Quantity, RefNum, 
--	Amount, UserID, RevenueID, RequestDocID, Mark, ORNumber, DepartmentID, PackageCode, PackageAmount, 
--	SpecimenId, Rush, Transplant, Dialysis, DoctorName)
--Select AssessID, HospNum, ChargeSlip, ProfId1, ProfId2, 
--	ProfId3, ProfFee1, ProfFee2, ProfFee3, ItemDescription, WithSurcharge, OPService, IdNum, 
--	Name, TransDate, AssessNum, Indicator, DrCr, RecordStatus, ItemID, Quantity, RefNum, 
--	Amount, UserID, RevenueID, RequestDocID, Mark, ORNumber, DepartmentID, PackageCode, PackageAmount, 
--	SpecimenId, Rush, Transplant, Dialysis, DoctorName
--from BILLING..tbCashAssessment 
--where AssessId = @AssessId;

--Delete BILLING..tbCashAssessment 
--Where RefNum = @RefNum and ItemId = @LabExamId;

--Update tbLabMaster
--Set RequestStatus = 'R', RefundedBy = @UserId
--Where RequestNum = @RequestNum;