gistfile1.sql

USE [RentTS]
GO
/****** Object:  StoredProcedure [RTS].[GenerateAnnualPenaltyForProperty]    Script Date: 3/21/2013 7:17:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER
PROCEDURE [RTS].[GenerateAnnualPenaltyForProperty]
@FISCAL_YEAR numeric(4,0),
@DUE_DATE varchar(50) OUTPUT,
@PENALTY_NO int,
@PROPERTY_ID numeric(18,0),
@CURR_FEE numeric(18,2),
@CURR_PRIOR_FEE numeric(18,2),
@CURRENT_USER varchar(50),
@REF_FORM varchar(50),
@REF_FORM_NO varchar(50),
@NEW_PENALTY_FEE numeric(18,2) OUTPUT,
@NET_PAYABLE numeric(18,2) OUTPUT,
@PENALTY_BALANCE NUMERIC(18,2),
@WAIVER_TYPE varchar(50) OUTPUT,
@TOTAL_PENALTY_BEFORE_WAIVER numeric(18,2) =0 OUTPUT,
@WAIVER_PERCENTAGE numeric(18,2) =0 OUTPUT,
@ApplyPenalty int = 1

AS

insert into ErrorLog (ErrorMessage, ErrorSource, ErrorDump, ReferenceForm, InputData, SuccessCheckPoint, SourceSystem)
values
('GAPFP: Inputs received for Property ID:', @PROPERTY_ID, 'Apply Penalty:', @ApplyPenalty, 'Current & Prior Fees:', @CURR_FEE, @CURR_PRIOR_FEE)

DECLARE @CURR_YEAR_PENALTY_TOTAL NUMERIC(18,2)
DECLARE @PRIOR_PENALTY_TOTAL NUMERIC(18,2)
DECLARE @TEMP_DATE date= NULL
--DECLARE @PENALTY_BALANCE NUMERIC(18,2)

SET @NEW_PENALTY_FEE = @CURR_FEE + @CURR_PRIOR_FEE

DECLARE @TRANSACTION_NAME varchar(50)
SET @TRANSACTION_NAME = 'GenerateAnnualPenaltyForProperty'

insert into ErrorLog (ErrorMessage, ErrorSource, ErrorDump, ReferenceForm, InputData, SuccessCheckPoint)
values
('GAPFP: About to BEGIN TRANSACTION for Property ID:', @PROPERTY_ID, 'Transaction Name:', @TRANSACTION_NAME, 'Transcount:', @@TRANCOUNT)


BEGIN
TRANSACTION @TRANSACTION_NAME

insert into ErrorLog (ErrorMessage, ErrorSource, ErrorDump, ReferenceForm, InputData, SuccessCheckPoint)
values
('GAPFP: Transaction Started. About to Begin Try. For Property ID:', @PROPERTY_ID, 'Transaction Name:', @TRANSACTION_NAME, 'Transcount:', @@TRANCOUNT)


BEGIN
TRY

insert into ErrorLog (ErrorMessage, ErrorSource, ErrorDump, ReferenceForm, InputData, SuccessCheckPoint)
values
('GAPFP: About to test for Apply Penalty IF for Property ID:', @PROPERTY_ID, 'Apply Penalty:', @ApplyPenalty, 'Combined Penalty Fee:', @NEW_PENALTY_FEE)


IF @ApplyPenalty = 1
BEGIN

insert into ErrorLog (ErrorMessage, ErrorSource, ErrorDump, ReferenceForm)
values
('GAPFP: Inside Apply Penalty IF for Property ID:', @PROPERTY_ID, 'Apply Penalty:', @ApplyPenalty)

insert into ErrorLog (ErrorMessage, ErrorSource, ErrorDump, ReferenceForm)
values
('GAPFP: About to update Property for Property ID:', @PROPERTY_ID, 'Apply Penalty:', @ApplyPenalty)

-- UPDATE Property
UPDATE RTS.Property
SET @CURR_YEAR_PENALTY_TOTAL = CurrentYearPenalty = (CurrentYearPenalty + @CURR_FEE),   --FeeBalance
@PRIOR_PENALTY_TOTAL= PenaltyBalance = (PenaltyBalance + @CURR_PRIOR_FEE),--PriorFeeBalance
ModifiedBy = @CURRENT_USER,
ModifiedOn = SYSDATETIME(),
ReferenceForm = @REF_FORM,
ReferenceFormNo = @REF_FORM_NO
WHERE
PropertyId = @PROPERTY_ID

-- Account Recievale
DECLARE @ArID numeric(18,0)--uncomment here
EXEC
@ArID = [RTS].GetNewSequenceValue
@SeqName='AccountReceivableSeq'


DECLARE @PENALTY_DUE_DATE date = NULL
BEGIN TRY
  SET @PENALTY_DUE_DATE  = CONVERT(date, @DUE_DATE, 101)
END TRY
BEGIN CATCH
	SET @PENALTY_DUE_DATE = NULL
END CATCH


insert into ErrorLog (ErrorMessage, ErrorSource, ErrorDump, ReferenceForm)
values
('GAPFP: About to insert AR entry for Property ID:', @PROPERTY_ID, 'Apply Penalty:', @ApplyPenalty)

INSERT INTO [RTS].[AccountReceivable]
	([AccountReceivableId]
	,[PropertyId]
	,[TransactionType]
	,[TransactionDate]
	,[FeeAmount]
	,[PriorFees]
	,[PenaltyAmount]
	,[UnallocatedAmount]
	,[FiscalYear]
	,[Description]
	,[DueDate]
	,[OwnerId]
	,[CreatedBy]
	,[CreatedOn]
	,[ReferenceForm]
	,[ReferenceFormNo])
VALUES
	(@ArID
	,@PROPERTY_ID
	,('Penalty' + CONVERT(varchar,@PENALTY_NO))
	,SYSDATETIME()
	,0
	,0
	,@NEW_PENALTY_FEE
	,0
	,@FISCAL_YEAR
	,NULL--'Unallocated Adjusted If Any After Fee Calculation'--<Description, varchar(2000),>
	,@PENALTY_DUE_DATE
	,RTS.GetPropertyOwner(@PROPERTY_ID)--<OwnerId, numeric(18,0),>
	,@CURRENT_USER
	,SYSDATETIME()
	,@REF_FORM
	,@REF_FORM_NO)
	
--Checking if admin waiver can be applied
IF @PENALTY_NO =1
BEGIN
	DECLARE @STATUS VARCHAR(50)
	DECLARE @ERROR_CODE VARCHAR(50)
	DECLARE @ERROR_DESCRIPTION VARCHAR(50)
	DECLARE @CURRENT_PENALTY_BALANCE NUMERIC(18,2)
	--DECLARE @PENALTY_BALANCE NUMERIC(18,2)
	DECLARE @UNALLOCATED_BALANCE NUMERIC(18,2)
	DECLARE @TOTAL_PAYABLE NUMERIC(18,2)
	DECLARE @CURRENT_YEAR_PENALTY NUMERIC(18,2)
	DECLARE @ADMIN_WAIVER_PERCENTAGE NUMERIC(18,2)
	DECLARE @ADMIN_WAIVER_AMOUNT NUMERIC(18,2)
	DECLARE @WEEK_DAY numeric(1)
	
	EXEC [RTS].[CalculateAdminWaiver]
	@PROPERTY_ID = @PROPERTY_ID,
	@STATUS = @STATUS OUTPUT,
	@ERROR_CODE = @ERROR_CODE OUTPUT,
	@ERROR_DESCRIPTION = @ERROR_DESCRIPTION OUTPUT,
	@FEE_BALANCE = @CURR_FEE OUTPUT,
	@PRIOR_BALANCE = @CURR_PRIOR_FEE OUTPUT,
	@PENALTY_BALANCE = @PENALTY_BALANCE OUTPUT,
	@UNALLOCATED_BALANCE = @UNALLOCATED_BALANCE OUTPUT,
	@CURRENT_PENALTY_BALANCE = @CURRENT_PENALTY_BALANCE OUTPUT,
	@TOTAL_PAYABLE = @TOTAL_PAYABLE OUTPUT,
	@CURRENT_YEAR_PENALTY = @CURRENT_YEAR_PENALTY OUTPUT,
	@ADMIN_WAIVER_PERCENTAGE = @ADMIN_WAIVER_PERCENTAGE OUTPUT,
	@ADMIN_WAIVER_AMOUNT = @ADMIN_WAIVER_AMOUNT OUTPUT,
	@NET_PAYABLE = @NET_PAYABLE OUTPUT
	
	--select @NET_PAYABLE as 'netpayable'
	--select @CURRENT_YEAR_PENALTY as 'cyp'
	--select @ADMIN_WAIVER_AMOUNT as 'waiveramount'
	--select @ADMIN_WAIVER_PERCENTAGE as 'waiverpercentage'


	SET @TOTAL_PENALTY_BEFORE_WAIVER = @PENALTY_BALANCE + @CURRENT_PENALTY_BALANCE
	SET @WAIVER_PERCENTAGE = @ADMIN_WAIVER_PERCENTAGE


	IF @ADMIN_WAIVER_AMOUNT > 0
	BEGIN
		IF(@ADMIN_WAIVER_PERCENTAGE = 100)
		BEGIN
			SET @WAIVER_TYPE = 'FULLWAIVER'
		END
		ELSE
		BEGIN
			SET @WAIVER_TYPE = 'PARTIALWAIVER'
		END
		SET @NEW_PENALTY_FEE = (@PRIOR_PENALTY_TOTAL + @CURR_YEAR_PENALTY_TOTAL) - @ADMIN_WAIVER_AMOUNT
		SET @DUE_DATE= '08/30/'+convert(varchar,YEAR(ISNULL(@TEMP_DATE,GETDATE())))--GETDATE()
	END
	ELSE
	BEGIN
		SET @WAIVER_TYPE = 'NOWAIVER'
		SET @DUE_DATE= 'PAST DUE'
		SET @NEW_PENALTY_FEE= @CURR_YEAR_PENALTY_TOTAL + @PRIOR_PENALTY_TOTAL--@CURR_YEAR_PENALTY_TOTAL
	END
	
END
ELSE -- @PENALTY_NO =2
BEGIN
	SET @NEW_PENALTY_FEE= @CURR_YEAR_PENALTY_TOTAL + @PRIOR_PENALTY_TOTAL -- edited @PENALTY_BALANCE--@CURR_YEAR_PENALTY_TOTAL
	--SET @DUE_DATE= 'PAST DUE' -- Do not set Due date as PAST DUE. Rather patch tht date which is coming from GUI
	SET @WAIVER_TYPE = 'NOWAIVER'
	SET @NET_PAYABLE = @CURR_FEE + @CURR_PRIOR_FEE + @NEW_PENALTY_FEE
END

END
ELSE
BEGIN -- No Penalty Applies
	  -- Only bill need to be generated
	  
	-- SET output variables
	SET @NEW_PENALTY_FEE= @PENALTY_BALANCE -- No Penalty applied
	
	IF(@CURR_FEE + @CURR_PRIOR_FEE) = 0 -- If Penalty is not Applied but bill is generated, (In case of Bundle for Penalty only)
	BEGIN
		SET @DUE_DATE= 'PAST DUE'
	END
	
	--SET @DUE_DATE= 'PAST DUE'
	SET @WAIVER_TYPE = 'NOWAIVER'
	SET @NET_PAYABLE = @CURR_FEE + @CURR_PRIOR_FEE + @NEW_PENALTY_FEE
END

insert into ErrorLog (ErrorMessage, ErrorSource, ErrorDump, ReferenceForm)
values
('GAPFP: About to insert Property Bill entry for Property ID:', @PROPERTY_ID, 'Apply Penalty:', @ApplyPenalty)

-- Insert Property Bill
INSERT
	INTO [RTS].[PropertyBill]--uncomment here
		([PropertyId]
		,[FeeAmount]
		,[PriorAmount]
		,[PenaltyAmount]
		,[DueDate]
		,[Status]
		,[Description]
		,[CreatedBy]
		,[CreatedOn]
		,[ModifiedBy]
		,[ModifiedOn]
		,[ReferenceForm]
		,[ReferenceFormNo])
	VALUES
		(@PROPERTY_ID
		,@CURR_FEE
		,@CURR_PRIOR_FEE
		,@NEW_PENALTY_FEE
		,ISNULL(@TEMP_DATE,GETDATE()) 
		,('Penalty' + CONVERT(varchar,@PENALTY_NO))
		,NULL--<Description, varchar(200),>
		,@CURRENT_USER
		,SYSDATETIME()
		,NULL--<ModifiedBy, varchar(50),>
		,NULL--<ModifiedOn, datetime,>
		,@REF_FORM
		,@REF_FORM_NO)
COMMIT TRANSACTION @TRANSACTION_NAME

insert into ErrorLog (ErrorMessage, ErrorSource, ErrorDump, ReferenceForm, InputData, SuccessCheckPoint)
values
('GAPFP: Transaction Commited. About to Return 0. For Property ID:', @PROPERTY_ID, 'Transaction Name:', @TRANSACTION_NAME, 'Transcount:', @@TRANCOUNT)


RETURN
0

insert into ErrorLog (ErrorMessage, ErrorSource, ErrorDump, ReferenceForm, InputData, SuccessCheckPoint)
values
('GAPFP: Returned 0. About to End Try Block For Property ID:', @PROPERTY_ID, 'Transaction Name:', @TRANSACTION_NAME, 'Transcount:', @@TRANCOUNT)


END
TRY
BEGIN
CATCH
ROLLBACK TRANSACTION @TRANSACTION_NAME
RETURN -1
END
CATCH