SQL-LIFE EFT FileExtract q

This stored procedure is used to render all of the structure / content for a debit order file for submission to Netcash. The file comprises

• a header line with fields for
  • username
  • password
  • pin
  • instruction (V=validate only (no debits done), C=update master file, B=two day debit, F=same day debit, G=NAEDO run, A=update beneficiary, D=?Payment batch upload?)
  • automatic date forward (0=do not use AutoForward, 1=Use AutoForward)
  • jump errors (set to 1, to have the file jump over errors, and process valid transaction, set to 0 to fail the file on error)
• a second header line, with fields for
  • batch identifier (unique batch file number/name - varchar(50))
  • action date (yyyy/mm/dd - if AutoForward is true, then this will be moved to the next available date if the date in question is not available)
• detail lines for each debit order to be processed. NOTE: You can only have one line per debit account, so if a person has two coverages (or a double deduction), you must sum the total deduction for the client and then create one debit order line for that person.
  • field names are detailed in the stored proc code
• a footer line, with fields for
  • End identifier - static text = "##END##"
  • Batch Total (The total sum of the transactions in the batch, in cents, with no commas and no full stops)

/*  * * * * * * * * * * * * * * * * * * 
Created  : Marc Gammon, Feb 2013
Purpouse : Process a Life Insurance billing run request to extract the contents of the file
		to be sent for processing for a given run ID and @mode
		Possible @mode values:
			V : Validate file
			C : Update debit master file
			B : Two day debit batch run
			F : Same day debit batch run
			G : NAEDO batch run
			A : Update beneficiaries
			D : Payment batch upload
		@fileSubModifier allows the same file to be sent through multiple times, but with a
		different "batch number" - batch number is @LifeEFTRunID + @fileSubModifier, so the 
		same file details can be generated, from the same @LifeEFTRunID, but with the modifier 
		incremented to create a different "batch" number.
		@NAEDOCode is the NAEDO tracking indicator.  Possible values are
			1 : no tracking
			2 : 1  day tracking
			3 : 3  day tracking
			4 : 7  day tracking
			5 : 14 day tracking
			6 : 21 day tracking
			7 : 32 day tracking

* * * * * * * * * * * * * * * * * * * */
CREATE PROCEDURE LIFE_EFT_FileExtract_q 
	@LifeEFTRunID int, 
	@mode varchar(1) = 'V', 
	@fileSubModifier varchar(2) = '00',
	@NAEDOCode varchar(1) = '1'
AS
BEGIN
  SET NOCOUNT ON
  DECLARE @TransactionDate varchar(15), @headerLine varchar(50)
  DECLARE @outTableVar table(
	ID int IDENTITY(1,1) NOT NULL,
    fileContent varchar(8000) NOT NULL);

    
	SELECT	@TransactionDate = convert(varchar,TransactionDate, 106)
	FROM	t_Life_EFT_Run
	WHERE	ID = @LifeEFTRunID
	
	INSERT	INTO @outTableVar (fileContent)
	SELECT  value + ',"'+@mode+'","1","1"' 
	FROM	LookupAfA..t_Settings 
	WHERE	[Path] = 'Configuration.Life.EFT.Header'

	INSERT	INTO @outTableVar (fileContent)
	SELECT  '"' + 'EFT_' + right('00000' + convert(varchar, @LifeEFTRunID),5) + '_' + @fileSubModifier + '","' + @TransactionDate + '"'

	SELECT	LBJ.ApplicationID, sum(RandValue) * 100 as TotalValue, BankName, BranchName,
			BranchCode, AccType, AccNumber, AccHolderName
	INTO	#oneRecordPerPatient
	FROM	t_Life_Billing_Journal LBJ 
	inner	join t_Life_Banking LB on LBJ.LifeBankingID = LB.ID
	inner	join t_Application A on LBJ.ApplicationID = A.ID
	WHERE	LifeEFTRunID = @LifeEFTRunID
	AND		RandValue > 0
	GROUP	By LBJ.ApplicationID, BankName, BranchName, BranchCode, 
			AccType, AccNumber, AccHolderName, DeductDay


	INSERT	INTO @outTableVar (fileContent)
	SELECT	'"' + convert(varchar, ApplicationID)	-- 1. Unique account reference number for client
			+ '","' + AccHolderName					-- 2. Name of client at Netcash
			+ '","' + AccHolderName					-- 3. Bank account holder's name
			+ '","' + case AccType when 'Current' then '1'
						when 'Savings' then '2'		-- 4. Account type: Current/Checking=1, Savings=2, Transmission=3
						else '3' end
			+ '","' + BranchCode					-- 5. Branch code
			+ '","' + AccNumber						-- 6. Account number
			+ '","0'								-- 7. Contract amount (regular monthly amount if using fixed debits)
			+ '","' + convert(varchar,convert(int,TotalValue))	-- 8. Batch Amount - value of this debit, in cents
			+ '","'									-- 9. eMail address of client - leave blank to suppress email from clearing house
			+ '","'									--10. Name on CC (for credit card debits only : i.e. unused by us)
			+ '","'									--11. Credit card number
			+ '","'									--12. Expiry month
			+ '","'									--13. Expiry year
			+ '","'									--14. Card type (Mastercard=1, Visa=2)
			+ '","0'								--15. Is-Credit-Card (bank account=0, credit card=1)
			+ '","'									--16. Extra field 1
			+ '","'									--17. Extra field 2
			+ '","'									--18. Extra field 3
			+ '","'									--19. Statement-Ref - EFT payments only, not used for debits
			+ '","' + @NAEDOCode					--20. NAEDO Code (see comments at top of proc)
			+ '"'
	FROM	#oneRecordPerPatient


	-- Footer line : ##END## and total sum of the vale of the batch in cents.
	INSERT	INTO @outTableVar (fileContent)
	SELECT '"##END##", "' + convert(varchar,convert(int,sum(TotalValue))) + '"'
	FROM	#oneRecordPerPatient

	SELECT fileContent from @outTableVar ORDER BY ID

	
END
GO