/*
 FN_InvoiceList
 Function used to produce invoices
*/

--  NP Adding SupportContract_ID to returned result, needed for ContractManager
--  NP Adding Description to returned result, needed for ContractManager
--  NP Adding ProductCode, TAS_ID, CutomerRef needed for Contract Manager
--  NP Adding InvoicedToTAS to checnk if the invoice has been sent to TAS


CREATE         FUNCTION FN_InvoiceList (@unused INTEGER = NULL)
RETURNS @InvoiceTable TABLE
(
	SupportContract_ID INTEGER,
	Customer VARCHAR(50),
	ContractNum VARCHAR(20),
	ContractStart DATETIME,
	ContractEnd DATETIME,
	ContractValue DECIMAL(18,2),
	ContractInvoiceDays INTEGER,
	ContractNumberOfPayments INTEGER,
	InvoiceValue DECIMAL(18,2),
	InvoiceDate DATETIME,
	InvoicePeriodStart DATETIME,
	InvoicePeriodEnd DATETIME,
	InvoiceDuration INTEGER,
	PaymentNumber INTEGER,
	PaymentTerms VARCHAR(255),
	AccountMgr VARCHAR(255),
	TASCode VARCHAR(20),
	Description VARCHAR(1024),
	ProductCode VARCHAR(50),
	TAS_ID INTEGER,
	CustomerRef VARCHAR(20),
	InvoicedToTAS BIT	
)
AS
BEGIN 

	DECLARE @cont_SupportContract_ID AS INTEGER
	DECLARE @cont_Customer AS VARCHAR(50)
	DECLARE @cont_ContractNum AS VARCHAR(20)
	DECLARE @cont_StartDate AS DATETIME
	DECLARE @cont_EndDate AS DATETIME
	DECLARE @cont_Price AS DECIMAL(18,2)
	DECLARE @cont_InvoiceDays AS INTEGER
	DECLARE @cont_NumberOfPayments AS INTEGER
	DECLARE @cont_PaymentTerms AS VARCHAR(255)
	DECLARE @cont_AccountMgr AS VARCHAR(255)
	DECLARE @cont_TASCode AS VARCHAR(20)
	DECLARE @cont_Description AS VARCHAR(1024)
	DECLARE @cont_ProductCode AS VARCHAR(50)
	DECLARE @cont_TAS_ID as INTEGER
	DECLARE @cont_CustomerRef AS VARCHAR(20)
	DECLARE @cont_Invoice_ID AS INTEGER
	DECLARE @cont_InvoicedToTAS AS BIT
	
	DECLARE @temp_ContMonths AS INTEGER
	DECLARE @temp_MonthsBetweenPayments AS INTEGER
	DECLARE @temp_InvoicePeriodStart AS DATETIME
	DECLARE @temp_InvoicePeriodEnd AS DATETIME
	DECLARE @temp_InvoiceDate AS DATETIME
	DECLARE @loop_var AS INTEGER
	
	----------------------------------------------------------------------------------------------------------------------------
	-- Get a list of contracts and open a cursor for the result set

	-- NP Changing Price to ContractValue
	-- NP Changing PaymentPeriod_ID to NoOfPayments
	-- NP Changing TASCode to TAS_CODE
	DECLARE contract_cur CURSOR FOR
		
		SELECT 
			SupportContract_ID, 
			Customer, 
			ContractNumber, 
			StartDate, 
			EndDate, 
			ContractValue, 
			InvoiceDays, 
			NoOfPayments, 
			PaymentTerms, 
			AccountMgr, 
			TAS_CODE,
			Description,
			ProductCode,
			TAS_ID,
			CustomerRef
		FROM V_Contracts 
	
	OPEN contract_cur
	FETCH NEXT FROM contract_cur INTO 
					@cont_SupportContract_ID,
					@cont_Customer, 
					@cont_ContractNum, 
					@cont_StartDate, 
					@cont_EndDate, 
					@cont_Price,
					@cont_InvoiceDays, 
					@cont_NumberOfPayments, 
					@cont_PaymentTerms, 
					@cont_AccountMgr, 
					@cont_TASCode,
					@cont_Description,
					@cont_ProductCode,
					@cont_TAS_ID,
					@cont_CustomerRef
	
	WHILE (@@FETCH_STATUS = 0)
	BEGIN
		-- Check that a charge will be made
		IF @cont_NumberOfPayments > 0
		BEGIN
			
			-- Determine the duration, in months, rounded down of the contract (rounded down is ok since all contracts run a whole number of months)
			SET @temp_ContMonths = DATEDIFF(month, @cont_StartDate, DATEADD(day, 1, @cont_EndDate))

			-- Determine how often they have to pay
			SET @temp_MonthsBetweenPayments = @temp_ContMonths / @cont_NumberOfPayments
	
			-- for each payment, add a row to our output table
			SET @loop_var = 0
			WHILE (@loop_var < @cont_NumberOfPayments)
			BEGIN
				
				SET @temp_InvoicePeriodStart = DATEADD(Month, @loop_var * @temp_MonthsBetweenPayments, @cont_StartDate)
				SET @temp_InvoicePeriodEnd = DATEADD(Month, @temp_MonthsBetweenPayments, @temp_InvoicePeriodStart)
				SET @temp_InvoiceDate = DATEADD(Day, @cont_InvoiceDays * -1, @temp_InvoicePeriodStart)

				-- NP Checking to see if record as being invoiced to TAS		
				SET @cont_InvoicedToTAS = 0
				SET @cont_Invoice_ID =0
				DECLARE InvoicedToTAS_cur CURSOR FOR

					SELECT
						Invoice_ID
					FROM 
						TB_Invoice
					WHERE
						SupportContract_ID = @cont_SupportContract_ID AND
						PaymentNumber = @loop_var +1
				OPEN InvoicedToTAS_cur
				FETCH NEXT FROM InvoicedToTAS_cur INTO 
						@cont_Invoice_ID
				IF @cont_Invoice_ID>0
				BEGIN
					SET @cont_InvoicedToTAS = 1
				END

				-- Close the cursor	
				CLOSE InvoicedToTAS_cur
				DEALLOCATE InvoicedToTAS_cur	
				-- NP End of checking process				


				INSERT INTO @InvoiceTable VALUES
							(
								@cont_SupportContract_ID, 
								@cont_Customer, 
								@cont_ContractNum, 
								@cont_StartDate, 
								@cont_EndDate, 
								@cont_Price, 
								@cont_InvoiceDays, 
								@cont_NumberOfPayments,
								@cont_Price / @cont_NumberOfPayments, 
								@temp_InvoiceDate, 
								@temp_InvoicePeriodStart, 
								@temp_InvoicePeriodEnd,
								@temp_MonthsBetweenPayments, 
								@loop_var + 1, 
								@cont_PaymentTerms, 
								@cont_AccountMgr, 
								@cont_TASCode, 
								@cont_Description,
								@cont_ProductCode,
								@cont_TAS_ID,
								@cont_CustomerRef,
								@cont_InvoicedToTAS
							)
				
				SET @loop_var = @loop_var + 1
			END		
		END	
	
		FETCH NEXT FROM contract_cur INTO 
						@cont_SupportContract_ID, 
						@cont_Customer, 
						@cont_ContractNum, 
						@cont_StartDate, 
						@cont_EndDate, 
						@cont_Price,
						@cont_InvoiceDays, 
						@cont_NumberOfPayments, 
						@cont_PaymentTerms, 
						@cont_AccountMgr, 
						@cont_TASCode,
						@cont_Description,
						@cont_ProductCode,
						@cont_TAS_ID,
						@cont_CustomerRef
	END

	-- Close the cursor	
	CLOSE contract_cur
	DEALLOCATE contract_cur
	
	RETURN

END

§þk¥

• Database / DRW help Check out the Tutorials for Frontpage Databases
  
• Forum FAQ' s
  
• Developers Edition Spooky Login User Registration and Management tool
  
• Personalized Frontpage Hosting
  

§þk¥

• Database / DRW help Check out the Tutorials for Frontpage Databases
  
• Forum FAQ' s
  
• Developers Edition Spooky Login User Registration and Management tool
  
• Personalized Frontpage Hosting