On Error Rollback to all table

--Create Procedure CreateFee(
--@MonthNo				int				=	NULL
--, @FeeType				Varchar(100)	=	NULL
--, @StudentId			int				=	NULL
--, @ClassId				int				=	NULL
--, @FeeDiscount			float			=	NULL
--, @CampusId				int				=	NULL
--, @FiscalYearID			int				=	NULL
--, @LastUpdateUser		int				=	NULL
--, @LastUpdateDate		datetime		=	NULL
--)
--as

--select @LastUpdateDate = getdate()
--Declare @FiscalYearId int
--Set @FiscalYearId = (Select FiscalYearId from FiscalYear Where Active = '1')

Declare @MonthNo int
Declare @FeeType Varchar(100)
Declare @CampusId int
Declare @FiscalYearID int
Declare @LastUpdateUser int
Declare @LastUpdateDate DateTime
Declare	@ReceiptNo varchar(50)=NULL
Declare @YearNo	varchar(50) = NULL
Set @MonthNo='4'
Set @CampusId='1'
SET @FeeType = '1,4,5'
Set @FiscalYearID='1'
Set @LastUpdateUser='1'
Set @LastUpdateDate = getdate()
Set @YearNo='1314'

BEGIN TRY
   BEGIN TRAN

---Insert Select Fee Types in Temp Table
Begin
CREATE TABLE #FeeTypeTemp(ID Int Identity(1,1) Not NULL, FeeType INT)
INSERT Into #FeeTypeTemp(FeeType)
SELECT * FROM dbo.SplitIDs(@FeeType)
End


---Insert Into FeeMaster 
Begin
---Insert Into	FeeMaster (MonthNo, StudentId, FeeDiscount, FiscalYearId, CampusId, LastUpdateUser, LastUpdateDate)
Select MonthNo=@MonthNo, StudentId, FeeDiscount, FiscalYearID=@FiscalYearId, CampusId=@CampusId, LastUpdateUser=@LastUpdateUser, LastUpdateDate=getdate()   from Students  
Where IsActive='1'
End


---Insert Into FeeDetail
Begin
--Insert Into	FeeDetail(FeeMasterId, MonthNo, FeeTypeId, FeeAmount, StudentId, CampusId, LastUpdateUser, LastUpdateDate)
Select b.FeeMasterId 
	, b.MonthNo 
	, c.FeeTypeId 
	, FeeAmount=Case When @Campusid='1' and a.FeeTerm='0' then c.FeeAmount
					When @Campusid='1' and a.FeeTerm='1' then c.FeeAmount/2 
					When @Campusid='1' and a.FeeTerm='2' then '0'
					When @Campusid='1' and a.FeeTerm='3' then '0'
					When @Campusid='1' and a.FeeTerm='4' then a.FeeDiscount 
			        
			        When @Campusid='2' and a.FeeTerm='0' then c.FeeAmount 
					When @Campusid='2' and (a.FeeTerm='1' OR a.FeeTerm='3') and c.FeeTypeID='4' and @MonthNo='4' then c.FeeAmount 
					When @Campusid='2' and a.FeeTerm='1' and c.FeeTypeID='4' and @MonthNo<>'4' then c.FeeAmount/2
					When @Campusid='2' and a.FeeTerm='1' and c.FeeTypeID<>'4' and @MonthNo<>'4' then c.FeeAmount
					When @Campusid='2' and a.FeeTerm='2' then '0'
					When @Campusid='2' and a.FeeTerm='3' and c.FeeTypeID='4' and @MonthNo<>'4' then '0'
					When @Campusid='2' and a.FeeTerm='4' and c.FeeTypeID='4' then a.FeeDiscount
			        
			        When @Campusid='3' and a.FeeTerm='0' then c.FeeAmount 
			        When @Campusid='3' and a.FeeTerm='1' then c.FeeAmount/2 
			        When @Campusid='3' and a.FeeTerm='2' then '0' 
					When @Campusid='3' and a.FeeTerm='3' then '0'
					When @Campusid='3' and a.FeeTerm='4' and c.FeeTypeID='4' then a.FeeDiscount 
					When @Campusid='3' and a.FeeTerm='4' and c.FeeTypeID<>'4' then c.FeeAmount  
			   else 0 end 
	, a.StudentId 
	, CampusID=@CampusId
	, LastUpdateUser=@LastUpdateUser
	, LastUpdateDate=@LastUpdateDate
from Students a
Join FeeMaster b on a.StudentId=b.StudentId
Join ClassesFee c on a.PresentClassId=c.ClassId
Join #FeeTypeTemp d on c.FeeTypeId=d.FeeType  
where a.StudentId=b.StudentId 
and b.MonthNo=@MonthNo 
and c.CampusId=@CampusId 
and c.FiscalYearID=@FiscalYearId
End
if not exists (select 1 from FeeTransactions where monthNo = @MonthNo and FiscalYearId=@FiscalYearID and CampusId=@CampusId)
begin
--	Insert Into FeeTransactions
	Select	GetDate(),MonthNo,Debit=0,Credit=Sum(FeeAmount),FeeDiscount=isnull(s.FeeDiscount,0),Descriptions='Fee generated for the month of ' + convert(varchar,MonthNo),DiscountAmount=0,d.StudentId,p.ComputerNo,p.ParentId
				,ReceiptNo=@YearNo+Convert(Varchar,s.Studentid)+convert(varchar,MonthNo),FeeMasterId,FiscalYearId=@FiscalYearId,d.CampusId,LastUpdateUser=@LastUpdateUser,LastUpdateDate=getdate()
				,d.ClassId,d.SectionId,d.ShiftId
	From		FeeDetail d
	Join		Students s on (s.StudentId = d.StudentId)
	left Join	Parents p on (p.ParentId = s.ParentId)
	Where		(MonthNo = @MonthNo)
	and			(s.StudentId not in (Select StudentId from FeeTransactions ft Where ft.MonthNo = @MonthNo and FiscalYearId=@FiscalYearID and CampusId=@CampusId))
	Group by
	MonthNo,d.StudentId,s.FeeDiscount,d.ClassId,d.SectionId,d.ShiftId,s.StudentId,p.ParentId,d.CampusId,FeeMasterId,p.ComputerNo 

	order by studentid
end

Drop Table #FeeTypeTemp
COMMIT TRAN
END TRY
BEGIN CATCH
SELECT 'Error Number = ' + CAST(error_number() AS VARCHAR) + ' Error description ' + error_description()
ROLLBACK 
END CATCH

Select allOpen in new window