Insert Into dbo.Scripts Values('Hotfix 21060712 General Payment Summary Incorrect CAC on transitioned job', GetDate(), '|~|') Go If exists (select * from sysobjects where id = object_id(N'[dbo].[repsp_EmployeePaymentSummary]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].repsp_EmployeePaymentSummary GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE PROCEDURE dbo.repsp_EmployeePaymentSummary @AdminID Int ,@DateFrom Datetime ,@DateTo Datetime ,@EmployeeInClause Text = Null ,@ShowEntry Bit = 0 -- depends on report ,@ETP Bit = 0 ,@AdminTypeID Int = Null ,@IsIndividual Bit = 1 ,@ElectronicOutput Bit = 0 ,@SelectEmployees Bit = 0 ,@LodgementID Int = Null ,@UpdatedBy Varchar(75) = Null ,@UserID Int = Null -- NOT NULL --,@IsAmendment Bit = 0 --Indicator Varchar(1) = 'O' -- 'A' = amend AS SET NOCOUNT ON SET ANSI_WARNINGS OFF /* Author: Stewart McLeod Date: 20 Apr 2007 Purpose: Notes: Revision History: 7/4/09 SM - Add the output to a table via flag @ReturnTable Called by proc repsp_EmployeePaymentSummaryElectronic 10/4/09 SM - Address was using practice 26/5/09 SM - Add Is Individual 10/06/09 JO - Add Select Employees 30/10/09 SM - Integrate Electronic Payment Summary Output 25/11/09 SM - updated the ATO plain paper reference SM - updated the Lump Sum Payment type 13/07/10 SM - clean the data remove exponential function for zero values SM - usually exponential functions are 20 char cater for a 19 digit exponential function take out clean the data remove the 'A0' replace with 'A|0' 21/07/10 SM - updated for v10, payer's address, payee's address replace double instances of 'ƒ' SM - v10 update 21/07/10 SM - updated for v10, payer's address, payee's address replace double instances of 'ƒ' SM - v10 update 02/12/10 SM - add @EmpPS - include employees receieving patments for the period (contained in @EmpPaymentsSUM) - exclude emps already having a PS for the period 01/03/11 SM - add NonTaxableAllowances 16/03/11 SM - add Reportable Employer Super Contribs 04/08/11 SM - Salary sacrifice changes (add Flag for sal sac account) 08/08/11 SM - Add Signatory1 field 24/10/2011 SM - incorrect join for Signatory1 14/11/2011 SM - now inserts a line feed after the FILE-TOTAL line in all electronic outputs not just v8 12/03/2012 SM - change 'T' test to 'P' production sm - add financial year function 15/06/2012 SM - duplic abn / wnp 03/07/2012 SM - FEMPA011.0 added for the financial year 2012. 02/11/2012 SM - FEMPA011.0 ETP Code 28/3/2013 SM - Salary sacrifice was included in gross pay TO DO.add column on detail report 24/5/2013 AmendmentIndicator ETPCode 16/07/2013 SM ACN FEMPA011.0 ETP Code updated 20/08/2014 SM strip spaces out of emp dup 20/08/2014 SM Re-issue payment after account is locked; creates new entry and reversal of the existing entry. 30/01/2015 SM replace 'NAT 72711B-12.2008' with 'NAT0046' 19/05/2015 SM allowed for extra comma in the Reference2 subentry (when the pay entry has three splits) 04/06/2015 SM allowed for extra comma in the Reference2 subentry (when the pay entry has six splits) 04/06/2015 SM Separate ETP R/S and O/T 18/06/2015 SM Rounding on all PS outputs, tax not rounded down any more, 02/07/2015 SM convert ETP to ETP R 02/07/2015 SM convert implement ETP codes S and P 02/10/2015 SM Add lump sum D tax, even though it should be nil, if someone adds tax manually it will not report. 14/06/16 SM exclude FEG payments without bank accounts 12/07/16 JO Pick up correct CAC based on job type Exec repsp_EmployeePaymentSUmmary 1069,'20140701','20150630' ,@ETP = 1 ,@ElectronicOutput = 1 */ SET NOCOUNT ON --IF @ElectronicOutput = 1 -- Debug /* CREATE TABLE [dbo].[#EmpPaySum] ( EmployeeID Int ,EntryDate Datetime ,EmployeeName Varchar(200) ,EmployeeFirstName nVarchar(75) ,EmployeeLastName nVarchar(75) ,EmployeeAddress Varchar(1000) ,EmployeeAdd Varchar(500) ,EmployeeCity Varchar(50) ,EmployeeState Varchar(50) ,EmployeePostCode Varchar(50) ,EmployeeCountry Varchar(1000) ,EmployeeTFN Varchar(50) ,TFN Varchar(50) ,Payee Varchar(200) ,DateOfBirth DateTime ,Dateofpayment DateTime ,Wages Money ,GrossPayment Money ,LumpSumA Money ,LumpSumB Money ,LumpSumD Money ,LumpSumE Money ,OtherIncome Money ,FBT Money ,Tax Money ,ETPTaxable Money ,ETPNonTaxable Money ,ETPTax Money ,ABNPayerNumber Varchar(2000) ,BranchNumber Varchar(2000) ,PayerName Varchar(2000) ) */ -- ******************************** /* IF @ElectronicOutput = 0 */ -- ******************************** DECLARE @SubEntry Table ( EntryID Int ,SubEntryID Int ,EntryDate DateTime ,Payee nvarchar(200) ,Memo Varchar(2000) ,TransTotal Money ,EmployeeID Int ,Reference2 Varchar(200) ,AccountID Int -- used for direct entry ,Account_AccountID int -- used to detemine prior year payments ,IsHeader Bit ) DECLARE @Entry Table ( EntryID Int ,TransTotal Money ) DECLARE @TaxTable Table ( EntryID Int ,SubEntryID Int ,Account_AccountID int ,EntryDate DateTime ,Direction Int ,EmployeeID Int ,TaxString Varchar(500) ,TaxString2 Varchar(100) ,TaxString3 Varchar(100) ,TaxString4 Varchar(100) ,TaxString5 Varchar(100) ,CommaPos1 Int ,CommaPos2 Int ,CommaPos3 Int ,CommaPos4 Int ,CommaPos5 Int ) DECLARE @TaxTable2 Table ( EntryID Int ,SubEntryID Int ,Account_AccountID int ,EntryDate DateTime ,Direction Int ,EmployeeID Int ,TaxString Varchar(500) ,PipePos1 Int ,PipePos2 Int ,PipePos3 Int ) DECLARE @EmpPSAmended TABLE (EmployeeID INT ,IsETPAmended Int ,IsIndividualAmended Int ) DECLARE @EmpPS Table ( EmployeeID Int ,FromDate DateTime ,ToDate DateTime ,EmployeeClaimType_LookupID Int ) DECLARE @EmpDivPayments TABLE (EntryID Int primary key) DECLARE @EmpPayments TABLE (EmployeeID INT ,EntryID Int ,EntryDate DateTime ,Payee nvarchar(200) ,EmployeeAddressID Int ,Memo Varchar(2000) ,Wages Money ,GrossPayment Money ,LumpSumA Money ,LumpSumB Money ,LumpSumD Money ,LumpSumE Money ,ReportableSuper Money ,NonTaxableAllowances Money ,TAX Money ,ETPTaxable Money ,ETPNonTaxable Money ,ETPTax Money ,ETPCode nvarchar(5) ) DECLARE @EmpPaymentsSUM TABLE (EmployeeID INT -- ,EntryID Int ,EntryDate DateTime ,Payee nvarchar(200) ,EmployeeAddressID Int ,Wages Money ,GrossPayment Money ,LumpSumA Money ,LumpSumB Money ,LumpSumD Money ,LumpSumE Money ,ReportableSuper Money ,NonTaxableAllowances Money ,TAX Money ,ETPTaxable Money ,ETPNonTaxable Money ,ETPTax Money ,HasLodgementETP Int ,HasLodgementIndividual Int ,HasLodgementETPThisYear Int ,HasLodgementIndividualThisYear Int ,HasAllowanceEntries Int ,ETPCode nvarchar(5) ) DECLARE @Results Table ( EntryID Int ,SubEntryID Int ,Account_AccountID int ,EntryDate DateTime ,EmployeeID Int ,IsETP Bit Not Null ,Code Char(1) Null ,Gross Money Not Null ,Tax Money Not Null ) DECLARE @MaxDateofPayment Table ( MaxEntryDate DateTime ,EmployeeID Int primary key ) IF @ElectronicOutput = 0 BEGIN DECLARE @Signatory1 VARCHAR(150) END IF @ElectronicOutput = 1 --DECLARE @AmendmentIndicator Varchar(1) DECLARE @OUTPUT TABLE ( OutputOrder Int ,Outputfield Varchar(8000) ) -- Supplier data record 1 DECLARE @PracticeABN NCHAR(11) DECLARE @RunType NCHAR(1) DECLARE @ReportEndDatedd NCHAR(2) DECLARE @ReportEndDatemm NCHAR(2) DECLARE @ReportEndDateyyyy NCHAR(4) DECLARE @ReportEndDate NCHAR(8) DECLARE @ReportYear Datetime DECLARE @Datatype NCHAR(1) DECLARE @Typeofreport NCHAR(1) DECLARE @Formatofreturnmedia NCHAR(1) DECLARE @ATOReportSpecificationVersionNumber NCHAR(10) DECLARE @Filler578 NCHAR(578) -- Supplier data record 2 DECLARE @Suppliername NCHAR(200) DECLARE @SupplierContactName NCHAR(38) DECLARE @SupplierContactTelephoneNumber NCHAR(15) -- FORMAT 02b1234b5678 or 0412b123b456 DECLARE @SupplierFacsimileNumber NCHAR(15) -- FORMAT 02b1234b5678 or 0412b123b456 DECLARE @SupplierFileReference NCHAR(16) DECLARE @Filler327 NCHAR(327) -- Supplier data record 3 DECLARE @PracticeID Int DECLARE @PracticeStreetAddressID Int DECLARE @PracticePostalAddressID Int DECLARE @SupplierStreetAddress NCHAR(76) -- Mandatory (line1 38) Optional (line 2 38) DECLARE @SupplierStreetSuburb NCHAR(27) -- Mandatory DECLARE @SupplierStreetState NCHAR(3) -- Mandatory DECLARE @SupplierStreetPostCode NCHAR(4) -- Mandatory DECLARE @SupplierStreetCountry NCHAR(20) DECLARE @SupplierPostalAddress NCHAR(76) DECLARE @SupplierPostalSuburb NCHAR(27) DECLARE @SupplierPostalState NCHAR(3) DECLARE @SupplierPostalPostCode NCHAR(4) DECLARE @SupplierPostalCountry NCHAR(20) DECLARE @SupplierEmail NCHAR(76) DECLARE @Filler275 NCHAR(275) -- Payer identity record DECLARE @Admins TABLE (AdminID Int ,AdminTypeID Int ,ABN NCHAR(11) ,WPN NCHAR(11) ,ABN_WPN NCHAR(11) -- Mandatory ,CAC NCHAR(3) -- Mandatory ,AdminFullName NCHAR(200) ,PayerName NCHAR(200) -- Mandatory ,PayerTradingName NCHAR(200) ,PayerAddress VARCHAR(500) ,PayerAddress1 NCHAR(38) -- Mandatory ,PayerAddress2 NCHAR(38) ,PayerSuburb NCHAR(27) -- Mandatory ,PayerState NCHAR(3) -- Mandatory ,PayerPostCode NCHAR(4) -- Mandatory ,PayerCountry NCHAR(20) ,ReportingUserID Int ,ContactName NCHAR(38) ,ContactTelephoneNumber NCHAR(15) ,ContactFacsimileNumber NCHAR(15) ,ETPPayerType NCHAR(1) ) CREATE TABLE dbo.#Data ( ID Int Not Null ) -- Software record DECLARE @Softwareproducttype NCHAR(80) DECLARE @Filler536 NCHAR(536) -- PAYGW Payment Summary Individual Non-business data record -- Temp table for data -- DROP Table [dbo].[#EmpPaySum] CREATE TABLE [dbo].[#EmpPaySum] ( EmployeeID Int ,EntryDate Datetime ,EmployeeName Varchar(200) ,EmployeeFirstName nVarchar(75) ,EmployeeLastName nVarchar(75) ,EmployeeAddress Varchar(1000) ,EmployeeAdd Varchar(500) ,EmployeeCity Varchar(50) ,EmployeeState Varchar(50) ,EmployeePostCode Varchar(50) ,EmployeeCountry Varchar(1000) -- ,EmployeeStartDate Datetime ,EmployeeTFN Varchar(50) ,TFN Varchar(50) ,Payee Varchar(200) ,DateOfBirth DateTime ,Dateofpayment DateTime ,Wages Money ,GrossPayment Money ,LumpSumA Money ,LumpSumB Money ,LumpSumD Money ,LumpSumE Money ,ReportableSuper Money ,NonTaxableAllowances Money ,OtherIncome Money ,FBT Money ,Tax Money ,ETPTaxable Money ,ETPNonTaxable Money ,ETPTax Money ,ABNPayerNumber Varchar(2000) ,BranchNumber Varchar(2000) ,PayerName Varchar(2000) ,PaymentTypeCode Varchar(1) ,AmendmentIndicator Varchar(1) ,AmendmentIndicatorETP Varchar(1) ) DECLARE @Emps TABLE ( PayeeTFN NCHAR(9) -- Mandatory ,PayeeSurname NCHAR(30) -- Mandatory ,PayeeFirstGivenName NCHAR(15) -- Mandatory ,PayeeSecondGivenName NCHAR(15) -- Mandatory ,PayeeAddress VARCHAR(500) ,PayeeAddress1 NCHAR(38) -- Mandatory ,PayeeAddress2 NCHAR(38) ,PayeeSuburb NCHAR(27) -- Mandatory ,PayeeState NCHAR(3) -- Mandatory ,PayeePostCode NCHAR(4) -- Mandatory ,PayeeCountry NCHAR(20) ,PeriodPaymentStart NCHAR(8) -- Mandatory ,PeriodPaymentEnd NCHAR(8) -- Mandatory ,TotalTaxWithheld NCHAR(8) -- Mandatory ,GrossPayment NCHAR(8) -- Mandatory ,TotalAllowances NCHAR(8) -- Mandatory ,LumpSumPaymentA NCHAR(8) -- Mandatory ,LumpSumPaymentB NCHAR(8) -- Mandatory ,LumpSumPaymentD NCHAR(8) -- Mandatory ,LumpSumPaymentE NCHAR(8) -- Mandatory ,PayeeDateOfBirth NCHAR(8) -- Mandatory ,PayeeETPDateofpayment NCHAR(8) -- Mandatory ETP -- ,PayeeDateStarted NCHAR(8) ,ETPTaxable NCHAR(8) ,ETPNonTaxable NCHAR(8) ,ETPTax NCHAR(8) ,CommunityDevelopmentEmploymentProject NCHAR(8) -- Mandatory ,OtherIncome NCHAR(8) -- Mandatory ,ReportableFringeBenefits NCHAR(8) -- Mandatory ,PaymentTypeCode VARCHAR(1) ,AmendmentIndicator VARCHAR(1) ,AmendmentIndicatorETP VARCHAR(1) ,ReportableSuper NCHAR(8) -- Mandatory ) DECLARE @Filler380 NCHAR(380) DECLARE @Filler377 NCHAR(377) -- Eligible Termination Payment (ETP) data record DECLARE @Filler316 NCHAR(316) DECLARE @Filler283 NCHAR(283) DECLARE @ReportableEmployerSuperannuationContribs NCHAR(8) DECLARE @LumpSumPaymentAType NCHAR(1) DECLARE @WorkplaceGiving NCHAR(8) DECLARE @UnionProfFees NCHAR(8) DECLARE @ExemptForeignEmploymentIncome NCHAR(8) DECLARE @DeductibleAmountPurchasePriceofAnnuity NCHAR(8) --File total record DECLARE @FileTotal NCHAR(10) DECLARE @NumberOfRecords NCHAR(10) DECLARE @Filler607 NCHAR(607) DECLARE @PayerName Varchar(1105) DECLARE @PayerBranch Varchar(10) DECLARE @AdminAddressID Int DECLARE @AdminAddress Varchar(760) DECLARE @AdminAdd Varchar(500) DECLARE @AdminCity Varchar(50) DECLARE @AdminState Varchar(50) DECLARE @AdminPostCode Varchar(50) DECLARE @AdminCountry Varchar(100) DECLARE @ABN_WPN TABLE (AdminID Int ,ABN Varchar(2000) ,WPN Varchar(2000) ,ABN_WPN Varchar(2000) ) -- ***** POPULATE TMP Tables ***** INSERT INTO @EmpPS SELECT ec.EmployeeID, min(ec.FromDate), min(ec.ToDate),ec.EmployeeClaimType_LookupID FROM EmployeeClaim ec INNER JOIN dbo.Employee e ON e.EmployeeID = ec.EmployeeID WHERE ec.FromDate BETWEEN @DateFrom AND @DateTO AND ec.ToDate BETWEEN @DateFrom AND @DateTO AND ((@ETP = 1) OR (ec.EmployeeClaimType_LookupID = 300011)) AND ((@ETP = 0) OR (ec.EmployeeClaimType_LookupID = 300010)) AND e.AdminID = @AdminID GROUP BY ec.EmployeeID,ec.EmployeeClaimType_LookupID SELECT @PayerName = cpPN.PropertyValue FROM admin_customproperty acp INNER JOIN CustomProperty cpPN ON cpPN.CustomPropertyID = acp.CustomPropertyID AND cpPN.CustomPropertyTypeID in (607) WHERE acp.AdminID = @AdminID SELECT @PayerBranch = cpBranch.PropertyValue FROM admin_customproperty acp INNER JOIN CustomProperty cpBranch ON cpBranch.CustomPropertyID = acp.CustomPropertyID AND cpBranch.CustomPropertyTypeID in (610) WHERE acp.AdminID = @AdminID AND ((cpBranch.AdminTypeID = @AdminTypeID) OR (@AdminTypeID IS Null)) -- IF @IsAmendment = 1 -- BEGIN -- Set @AmendmentIndicator = 'A' -- amend -- END -- ELSE -- BEGIN -- Set @AmendmentIndicator = 'O' -- END -- SELECT @AdminAddressID = Min(aa.AddressID) FROM Admin_Address aa INNER JOIN Address ad ON aa.AddressID = ad.AddressID AND ad.IsMailing = 1 WHERE aa.AdminID = @AdminID GROUP BY aa.AddressID Set @AdminAddress = (Select ad.Address + CHAR(10) + CHAR(13) + ISNULL(ad.City + ' ','') + ISNULL(ad.State + ' ','') + ISNULL(ad.PostCode + ' ','') + CHAR(13) + ISNULL(ad.Country + ' ','') FROM Address ad WHERE ad.AddressID = @AdminAddressID) Set @AdminAdd = (Select ad.Address + CHAR(10) + CHAR(13) FROM Address ad WHERE ad.AddressID = @AdminAddressID) Set @AdminCity = (Select ad.City FROM Address ad WHERE ad.AddressID = @AdminAddressID) Set @AdminState = (Select ad.State FROM Address ad WHERE ad.AddressID = @AdminAddressID) Set @AdminPostCode = (Select ad.PostCode FROM Address ad WHERE ad.AddressID = @AdminAddressID) Set @AdminCountry = (Select ad.Country FROM Address ad WHERE ad.AddressID = @AdminAddressID) --Signatory1 SELECT @Signatory1 = ISNULL(u.Name,'') FROM dbo.Appointment apt INNER JOIN dbo.Appointee_Appointment aa ON apt.AppointmentID = aa.AppointmentID INNER JOIN dbo.[User] u ON u.UserID = aa.UserID WHERE apt.IsSignatory = 1 AND apt.Number = 1 AND apt.AdminID = @AdminID -- CREATE TABLE dbo.#Data -- ( -- ID Int Not Null -- ) -- Employees to include IF NOT @EmployeeInClause IS NULL EXEC dbo.usp_Split @EmployeeInClause ELSE INSERT INTO dbo.#Data SELECT EmployeeID FROM Employee WHERE AdminID = @AdminID -- Tmp DATA INSERT INTO @SubEntry SELECT se.EntryID ,se.SubEntryID ,se.EntryDate ,se.Payee ,se.Memo ,TransTotal = Round((se.Total * se.Direction ),2) ,c.EmployeeID ,se.Reference2 ,AccountID = CASE WHEN se.Reference2 IS NULL THEN se.Account_AccountID ELSE NULL END ,Account_AccountID = se.Account_AccountID ,IsHeader = se.IsHeader FROM dbo.SubEntry se INNER JOIN dbo.Creditor c ON se.CreditorID = c.CreditorID INNER JOIN dbo.Employee e ON c.EmployeeID = e.EmployeeID INNER JOIN dbo.#Data d ON d.ID = e.EmployeeID INNER JOIN dbo.Dividend div ON div.DividendID = se.CreditorDividendID WHERE se.EntryDate >= @DateFrom AND se.EntryDate <= @DateTO AND se.ContextID = @AdminID AND ((@AdminTypeID IS NULL) OR (se.AdminTypeID = @AdminTypeID)) AND ISNULL(se.ISBatchPosting,0) = 0 AND se.Entrytype not in (100,101) AND se.ContextID = e.AdminID AND div.BankAccount_AccountID IS NOT NULL INSERT INTO @SubEntry SELECT se.EntryID ,se.SubEntryID ,se.EntryDate ,se.Payee ,se.Memo ,TransTotal = Round((se.Total * se.Direction ),2) ,se.EmployeeID ,se.Reference2 ,AccountID = CASE WHEN se.Reference2 IS NULL THEN se.Account_AccountID ELSE NULL END ,Account_AccountID = se.Account_AccountID ,IsHeader = se.IsHeader FROM dbo.SubEntry se LEFT JOIN dbo.Dividend div ON div.DividendID = se.CreditorDividendID WHERE se.EntryDate >= @DateFrom AND se.EntryDate <= @DateTO AND se.ContextID = @AdminID AND ((@AdminTypeID IS NULL) OR (se.AdminTypeID = @AdminTypeID)) AND se.EntryID in (Select EntryID from dbo.SubEntry WHERE EmployeeID IN (Select ID FROM dbo.#Data)) AND NOT se.SubEntryID in (Select subEntryID from @SubEntry) AND ISNULL(se.ISBatchPosting,0) = 0 AND div.DividendID IS NULL /* Select * from @SubEntry order by payee return 0 FROM dbo.SubEntry se WHERE se.EntryDate >= @DateFrom AND se.EntryDate <= @DateTO AND se.ContextID = @AdminID AND ((@AdminTypeID IS NULL) OR (se.AdminTypeID = @AdminTypeID)) AND se.EntryID in (Select EntryID from dbo.SubEntry WHERE EmployeeID IN (Select ID FROM dbo.#Data)) AND ISNULL(se.ISBatchPosting,0) = 0 --AND se.EmployeeID Is Not Null */ -- Calc Transaction totals INSERT INTO @Entry SELECT EntryID ,TransTotal = -TransTotal --Sum(TransTotal) FROM @SubEntry WHERE IsHeader = 1 --GROUP BY EntryID INSERT INTO @TaxTable ( EntryID ,SubEntryID ,Account_AccountID ,EntryDate ,Direction ,EmployeeID ,TaxString --,CommaPos1 ) SELECT EntryID = se.EntryID ,SubEntryID = se.SubEntryID ,Account_AccountID = se.Account_AccountID ,EntryDate = se.EntryDate ,Direction = CASE WHEN se.TransTotal >= 0 THEN 1 ELSE -1 END ,EmployeeID = se.EmployeeID ,TaxString = se.Reference2 --,CommaPos1 = Charindex(',',se.Reference2) FROM @SubEntry se WHERE se.Reference2 Is Not Null Order By EmployeeID,EntryID,SubEntryID -- ********************************************** -- CLEAN UP THE FRONT END DATA TO REMOVE ERRORS -- ********************************************** UPDATE @TaxTable SET TaxString = Replace(TaxString,'A0','A|0') UPDATE @TaxTable SET TaxString = Replace(TaxString,'ETP|','ETP=R|') UPDATE @TaxTable SET TaxString = Replace ( TaxString ,Replace ( Substring( TaxString,charindex('E-',TaxString) - 16,20) , '|' , '' ) ,0 ) WHERE charindex('E-',TaxString) > 1 UPDATE @TaxTable SET TaxString = Replace ( TaxString ,Substring( TaxString,charindex('E-',TaxString) - 17,21) ,'' ) WHERE charindex('E-',TaxString) > 1 -- ********************************************** -- END CLEAN UP THE FRONT END DATA TO REMOVE ERRORS -- ********************************************** UPDATE @TaxTable SET CommaPos1 = Charindex(',',TaxString) -- ********************************************** -- SEPARATE THE TAX STRING INTO ITS COMPONENTS -- ********************************************** Update @TaxTable SET TaxString2 = SUBSTRING (TaxString -- string ,Charindex(',',TaxString) + 1 -- start ,Len(TaxString) - Charindex(',',TaxString) -- length ) ,CommaPos2 = Charindex(',',SUBSTRING (TaxString -- string ,Charindex(',',TaxString) + 1 -- start ,Len(TaxString) - Charindex(',',TaxString) -- length ) ) WHERE CommaPos1 > 0 Update @TaxTable SET TaxString3 = SUBSTRING (TaxString2 -- string ,Charindex(',',TaxString2) + 1 -- start ,Len(TaxString2) - Charindex(',',TaxString2) -- length ) ,CommaPos3 = Charindex(',',SUBSTRING (TaxString2 -- string ,Charindex(',',TaxString2) + 1 -- start ,Len(TaxString2) - Charindex(',',TaxString2) -- length ) ) WHERE CommaPos2 > 0 Update @TaxTable SET TaxString4 = SUBSTRING (TaxString3 -- string ,Charindex(',',TaxString3) + 1 -- start ,Len(TaxString3) - Charindex(',',TaxString3) -- length ) ,CommaPos4 = Charindex(',',SUBSTRING (TaxString3 -- string ,Charindex(',',TaxString3) + 1 -- start ,Len(TaxString3) - Charindex(',',TaxString3) -- length ) ) WHERE CommaPos3 > 0 Update @TaxTable SET TaxString5 = SUBSTRING (TaxString4 -- string ,Charindex(',',TaxString4) + 1 -- start ,Len(TaxString4) - Charindex(',',TaxString4) -- length ) ,CommaPos5 = Charindex(',',SUBSTRING (TaxString4 -- string ,Charindex(',',TaxString4) + 1 -- start ,Len(TaxString4) - Charindex(',',TaxString4) -- length ) ) WHERE CommaPos4 > 0 -- ********************************************** -- END SEPARATE THE TAX STRING INTO ITS COMPONENTS -- ********************************************** INSERT INTO @TaxTable2 SELECT EntryID ,SubEntryID ,Account_AccountID ,EntryDate ,Direction ,EmployeeID ,TaxString = CASE WHEN CommaPos1 = 0 THEN TaxString ELSE SubString(TaxString, 1, CommaPos1 - 1) END ,0,0,0 FROM @TaxTable INSERT INTO @TaxTable2 SELECT EntryID ,SubEntryID ,Account_AccountID ,EntryDate ,Direction ,EmployeeID ,TaxString = CASE WHEN CommaPos2 = 0 THEN TaxString2 ELSE SubString(TaxString2, 1, CommaPos2 - 1) END ,0,0,0 FROM @TaxTable WHERE CommaPos1 > 0 INSERT INTO @TaxTable2 SELECT EntryID ,SubEntryID ,Account_AccountID ,EntryDate ,Direction ,EmployeeID ,TaxString = CASE WHEN CommaPos3 = 0 THEN TaxString3 ELSE SubString(TaxString3, 1, CommaPos3 - 1) END ,0,0,0 FROM @TaxTable WHERE CommaPos2 > 0 INSERT INTO @TaxTable2 SELECT EntryID ,SubEntryID ,Account_AccountID ,EntryDate ,Direction ,EmployeeID ,TaxString = CASE WHEN CommaPos4 = 0 THEN TaxString4 ELSE SubString(TaxString4, 1, CommaPos4 - 1) END ,0,0,0 FROM @TaxTable WHERE CommaPos3 > 0 INSERT INTO @TaxTable2 SELECT EntryID ,SubEntryID ,Account_AccountID ,EntryDate ,Direction ,EmployeeID ,TaxString = CASE WHEN CommaPos5 = 0 THEN TaxString5 ELSE SubString(TaxString5, 1, CommaPos5 - 1) END ,0,0,0 FROM @TaxTable WHERE CommaPos4 > 0 UPDATE @TaxTable2 SET PipePos1 = Charindex('|',TaxString) UPDATE @TaxTable2 SET PipePos2 = Charindex('|',TaxString, PipePos1 + 1) UPDATE @TaxTable2 SET PipePos3 = Charindex('|',TaxString, PipePos2 + 1) INSERT INTO @Results SELECT EntryID ,SubEntryID ,Account_AccountID ,EntryDate ,EmployeeID ,IsETP = CASE WHEN Charindex('ETP=R',TaxString) > 0 THEN Convert(Bit, Charindex('ETP=R',SubString(TaxString, PipePos1 + 1, PipePos2 - (PipePos1 + 1)))) WHEN Charindex('ETP=O',TaxString) > 0 THEN Convert(Bit, Charindex('ETP=O',SubString(TaxString, PipePos1 + 1, PipePos2 - (PipePos1 + 1)))) WHEN Charindex('ETP',TaxString) > 0 THEN Convert(Bit, Charindex('ETP',SubString(TaxString, PipePos1 + 1, PipePos2 - (PipePos1 + 1)))) ELSE 0 END ,Code = CASE WHEN Len(SubString(TaxString, PipePos1 + 1, PipePos2 - (PipePos1 + 1))) = 3 THEN Null ELSE Right(SubString(TaxString, PipePos1 + 1, PipePos2 - (PipePos1 + 1)),1) END ,Gross = Convert(Money, SubString(TaxString, PipePos2 + 1, PipePos3 - (PipePos2 + 1))) * Direction ,Tax = Convert(Money, SubString(TaxString, PipePos3 + 1, Len(TaxString) - (PipePos3))) * Direction FROM @TaxTable2 /* Select * FROM @TaxTable Select * FROM @TaxTable2 Select * FROM @Results return 0 */ -- Detemine if there's prior year entries for the employee DECLARE @PriorYearPayments TABLE ( EmployeeID INT ,Account_AccountID INT ,SubEntryID INT ,EntryDate DATETIME ,Reference2 varchar(250) ,Ref2HasR int ,Ref2HasO int ) INSERT INTO @PriorYearPayments SELECT EmployeeID = se.EmployeeID ,Account_AccountID = se.Account_AccountID ,SubEntryID = se.SubEntryID ,EntryDate = se.EntryDate ,Reference2 = se.Reference2 ,Ref2HasR = CASE WHEN Charindex('ETP=R|',se.Reference2) > 0 OR Charindex('ETP=|',se.Reference2) > 0 THEN 1 ELSE 0 END ,Ref2HasO = CASE WHEN Charindex('ETP=O|',se.Reference2) > 0 THEN 1 ELSE 0 END FROM SubEntry se INNER JOIN @SubEntry se1 ON se.EmployeeID = se1.EmployeeID AND se.Account_AccountID = se1.Account_AccountID AND se.EntryDate < dbo.uFnFinancialYearStartDate(@DateFrom) AND ISNULL(se.IsBatchPosting,0) = 0 UPDATE @Results SET Code = Replace(r.Code, 'R','S') FROM @Results r INNER JOIN @PriorYearPayments pyp ON pyp.EmployeeID = r.EmployeeID AND pyp.Account_AccountID = r.Account_AccountID AND r.Code = 'R' UPDATE @Results SET Code = Replace(r.Code, 'O','P') FROM @Results r INNER JOIN @PriorYearPayments pyp ON pyp.EmployeeID = r.EmployeeID AND pyp.Account_AccountID = r.Account_AccountID AND r.Code = 'O' /* Select dbo.uFnFinancialYearStartDate(@DateFrom), * FROM @Results Select * FROM @PriorYearPayments return 0 */ --- ABN WPN --- ABN WPN DECLARE @MaxWpn Int DECLARE @MaxWpnVal varchar(2000) DECLARE @MaxABN Int DECLARE @MaxABNVal varchar(2000) SELECT @MaxWpn = cpWPN.CustomPropertyID FROM admin_customproperty acp INNER JOIN CustomProperty cpWPN ON cpWPN.CustomPropertyID = acp.CustomPropertyID AND cpWPN.CustomPropertyTypeID in (4) WHERE acp.AdminID = @AdminID SELECT @MaxWpnVal = Replace(cpWPN.PropertyValue,' ','') FROM CustomProperty cpWPN WHERE cpWPN.CustomPropertyID = @MaxWpn SELECT @MaxABN = cpABN.CustomPropertyID FROM admin_customproperty acp INNER JOIN CustomProperty cpABN ON cpABN.CustomPropertyID = acp.CustomPropertyID AND cpABN.CustomPropertyTypeID in (2) WHERE acp.AdminID = @AdminID SELECT @MaxABNVal = Replace(cpABN.PropertyValue,' ','') FROM CustomProperty cpABN WHERE cpABN.CustomPropertyID = @MaxABN INSERT INTO @ABN_WPN (AdminID,WPN,ABN,ABN_WPN) SELECT @AdminID,@MaxWpnVal,@MaxABNVal,ISNULL(@MaxWpnVal,@MaxABNVal) -- *** INSERT SPLIT DIVIDEND PAYMENTS *** INSERT INTO @EmpPayments SELECT EmployeeID = r.EmployeeID ,EntryID = CASE WHEN @ShowEntry = 1 THEN r.EntryID ELSE NULL END ,EntryDate = CASE WHEN @ShowEntry = 1 THEN r.EntryDate ELSE NULL END ,Payee = (ISNULL(e.FirstName + ' ','') + e.LastName) ,EmployeeAddressID = CASE WHEN e.AddressID IS NULL THEN c.AddressID ELSE e.AddressID END ,Memo = Null ,Wages = Sum(CASE Code WHEN 'G' THEN Gross WHEN 'A' THEN Gross WHEN 'D' THEN Gross ELSE 0 END) ,GrossPayment = Sum(CASE Code WHEN 'G' THEN Gross ELSE 0 END) ,LumpSumA = Sum(CASE Code WHEN 'A' THEN Gross ELSE 0 END) ,LumpSumB = Sum(CASE Code WHEN 'B' THEN Gross ELSE 0 END) ,LumpSumD = Sum(CASE Code WHEN 'D' THEN Gross ELSE 0 END) ,LumpSumE = Sum(CASE Code WHEN 'E' THEN Gross ELSE 0 END) ,ReportableSuper = 0 ,NonTaxableAllowances = 0 ,Tax = Sum(CASE Code WHEN 'G' THEN Tax WHEN 'A' THEN Tax WHEN 'B' THEN Tax WHEN 'D' THEN Tax ELSE 0 END) ,ETPTaxable = Sum(CASE WHEN IsETP = 1 AND Tax <> 0 THEN Gross ELSE 0 END) ,ETPNonTaxable = Sum(CASE WHEN IsETP = 1 AND Tax = 0 THEN Gross ELSE 0 END) ,ETPTax = Sum(CASE IsETP WHEN 1 THEN Tax ELSE 0 END) ,ETPCode = CASE IsETP WHEN 1 THEN r.Code ELSE '' END FROM @Results r INNER JOIN dbo.Employee e ON e.EmployeeID = r.EmployeeID LEFT OUTER JOIN dbo.Creditor c ON c.EmployeeID = e.EmployeeID GROUP BY r.EmployeeID ,CASE WHEN @ShowEntry = 1 THEN r.EntryID ELSE NULL END ,CASE WHEN @ShowEntry = 1 THEN r.EntryDate ELSE NULL END ,r.Code ,r.IsETP ,CASE WHEN e.AddressID IS NULL THEN c.AddressID ELSE e.AddressID END ,(ISNULL(e.FirstName + ' ','') + e.LastName) -- *** INSERT DIRECT ENTRY (NON-DIVIDEND) PAYMENTS *** -- WAGES INSERT INTO @EmpPayments SELECT EmployeeID = se.EmployeeID ,EntryID = CASE WHEN @ShowEntry = 1 THEN se.EntryID ELSE NULL END ,EntryDate = CASE WHEN @ShowEntry = 1 THEN se.EntryDate ELSE null END ,Payee = (ISNULL(e.FirstName + ' ','') + e.LastName) --se.Payee ,EmployeeAddressID = CASE WHEN e.AddressID IS NULL THEN c.AddressID ELSE e.AddressID END ,Memo = CASE WHEN @ShowEntry = 1 THEN se.Memo ELSE null END ,Wages = Sum(CASE WHEN ISNULL(acc.Flags,0) & 4194304 = 4194304 THEN 0 WHEN ISNULL(acc.Flags,0) & 2097152 = 2097152 THEN 0 WHEN acc.AccountGroupID in (6020) THEN TransTotal WHEN acc.AccountGroupID in (2030,2040) and acc.CreditorClaimTypeID IN (250,270) THEN TransTotal ELSE 0 END) ,GrossPayment = Sum(CASE WHEN ISNULL(acc.Flags,0) & 4194304 = 4194304 THEN 0 WHEN ISNULL(acc.Flags,0) & 2097152 = 2097152 THEN 0 WHEN acc.AccountGroupID in (6020) THEN TransTotal WHEN acc.AccountGroupID in (2030,2040) and acc.CreditorClaimTypeID IN (250,270) THEN TransTotal ELSE 0 END) ,LumpSumA = 0 ,LumpSumB = 0 ,LumpSumD = 0 ,LumpSumE = 0 ,ReportableSuper = SUM( CASE WHEN ISNULL(acc.Flags,0) & 4194304 = 4194304 --acc.AccountUID = '9E974D57-2A9C-461E-998D-768E818F05EC' THEN TransTotal ELSE 0 END ) ,NonTaxableAllowances = Sum(CASE WHEN acc.AccountGroupID in (6020) AND ISNULL(acc.Flags,0) & 2097152 = 2097152 THEN TransTotal ELSE 0 END ) ,Tax = Sum(CASE WHEN acc.AccountGroupID in (4012,4013) THEN -TransTotal ELSE 0 END) ,ETPTaxable = Sum(CASE WHEN acc.AccountGroupID in (2030,2040) and acc.CreditorClaimTypeID IN (280) THEN TransTotal ELSE 0 END) ,ETPNonTaxable = 0 ,ETPTax = 0 ,ETPCode = '' FROM @SubEntry se INNER JOIN dbo.Employee e ON e.EmployeeID = se.EmployeeID INNER JOIN dbo.Account acc ON acc.AccountID = se.AccountID INNER JOIN dbo.AccountGroup ag ON ag.AccountGroupID = acc.AccountGroupID LEFT OUTER JOIN dbo.Creditor c ON c.EmployeeID = e.EmployeeID WHERE ag.AccountGroupID in (6020,2030,2040,4012,4013) AND se.EntryID NOT IN (select EntryID FROM @Results) -- AND e.AdminID = acc.AdminID GROUP BY (ISNULL(e.FirstName + ' ','') + e.LastName) ,se.EmployeeID ,CASE WHEN @ShowEntry = 1 THEN se.EntryID ELSE NULL END ,CASE WHEN @ShowEntry = 1 THEN se.EntryDate ELSE null END ,se.Payee ,CASE WHEN e.AddressID IS NULL THEN c.AddressID ELSE e.AddressID END , CASE WHEN @ShowEntry = 1 THEN se.Memo ELSE null END -- *** INSERT DIRECT ENTRY (NON-DIVIDEND) PAYMENTS *** -- PREFERRED CREDITOR PAYMENTS -- MAX Date of Payment for ETP IF (@ShowEntry = 0) AND (@ETP = 1) BEGIN INSERT INTO @MaxDateofPayment SELECT Max(EntryDate) ,EmployeeID FROM @subentry WHERE NOT EmployeeID IS NULL GROUP BY EmployeeID END INSERT INTO @EmpPaymentsSUM SELECT EmployeeID = ep.EmployeeID -- ,EntryID = ep.EntryID ,EntryDate = MAX(ep.EntryDate) ,Payee = ep.Payee ,EmployeeAddressID = ep.EmployeeAddressID ,Wages = Sum(ep.Wages) ,GrossPayment = Sum(ep.GrossPayment) ,LumpSumA = Sum(ep.LumpSumA) ,LumpSumB = Sum(ep.LumpSumB) ,LumpSumD = Sum(ep.LumpSumD) ,LumpSumE = Sum(ep.LumpSumE) ,ReportableSuper = Sum(ep.ReportableSuper) ,NonTaxableAllowances = Sum(ep.NonTaxableAllowances) ,Tax = Sum(ep.Tax) ,ETPTaxable = Sum(ep.ETPTaxable) ,ETPNonTaxable = Sum(ep.ETPNonTaxable) ,ETPTax = Sum(ep.ETPTax) ,0 -- } ,0 -- ,0 -- history records ,0 -- } ,HasAllowanceEntries = CASE WHEN Sum(ep.NonTaxableAllowances) > 0 THEN 1 ELSE 0 END ,ep.ETPCode FROM @EmpPayments ep GROUP BY ep.EmployeeID, ep.Payee,ep.EmployeeAddressID--, ep.EntryDate ,ep.EntryID, ep.Memo ,ep.ETPCode -- Update Employee Address UPDATE @EmpPaymentsSUM SET EmployeeAddressID = c.AddressID FROM Creditor c INNER JOIN Employee e ON e.EmployeeId = c.EmployeeId INNER JOIN @EmpPaymentsSUM eps ON e.EmployeeId = eps.EmployeeId WHERE eps.EmployeeAddressID IS NULL -- UPDATE ETP Date of payment -- PayeeETPDateofpayment IF (@ShowEntry = 0) AND (@ETP = 1) BEGIN UPDATE @EmpPaymentsSUM Set EntryDate = mdop.MaxEntryDate FROM @EmpPaymentsSUM eps INNER JOIN @MaxDateofPayment mdop ON mdop.EmployeeId = eps.EmployeeID END /* Select * from @Subentry return 0 Select * from @MaxDateofPayment --Select * from @TaxTable Select * from @TaxTable2 Select * from @EmpPayments Select * from @EmpPaymentsSUM return 0 */ -- Work out whether the emp had amended ETP/Individual lodgements for the period or not Update @EmpPaymentsSUM SET HasLodgementETPThisYear = 1 FROM @EmpPaymentsSUM eps INNER JOIN EmployeeClaim ec ON eps.EmployeeID = ec.EmployeeID WHERE ec.EmployeeClaimType_LookupID = 300010 AND @ETP = 1 AND ec.ToDate BETWEEN @DateFROM AND @DateTo Update @EmpPaymentsSUM SET HasLodgementETP = 1 FROM @EmpPaymentsSUM eps INNER JOIN EmployeeClaim ec ON eps.EmployeeID = ec.EmployeeID WHERE ec.EmployeeClaimType_LookupID = 300010 AND @ETP = 1 Update @EmpPaymentsSUM SET HasLodgementIndividualThisYear = 1 FROM @EmpPaymentsSUM eps INNER JOIN EmployeeClaim ec ON eps.EmployeeID = ec.EmployeeID WHERE ec.EmployeeClaimType_LookupID = 300011 AND @IsIndividual = 1 AND ec.ToDate BETWEEN @DateFROM AND @DateTo Update @EmpPaymentsSUM SET HasLodgementIndividual = 1 FROM @EmpPaymentsSUM eps INNER JOIN EmployeeClaim ec ON eps.EmployeeID = ec.EmployeeID WHERE ec.EmployeeClaimType_LookupID = 300011 AND @IsIndividual = 1 --Select * FROM @EmpPaymentsSUM -- ********************************************************************************************************** -- ******* REPORT DATA ************************************************************************************** -- ********************************************************************************************************** -- THERE MAY BE DIFFERENT OUTPUTS: -- RETURN THE LIST FOR THE REPORT FILTER -- RETURN THE DATA FOR THE PAYG SUMMARY REPORT -- RETURN THE DATA FOR THE ELECTRONIC OUTPUT -- REPORT FILTER -- Used for the filter form to return the list of possible employees to select from -- This should include employees that receieved payments for the period (contained in @EmpPaymentsSUM) -- and exclude emps already having a PS for the period IF @SelectEmployees = 1 BEGIN /* Select * FROM @EmpPaymentsSUM ep LEFT OUTER JOIN @EmpPS ec ON ep.EmployeeID = ec.EmployeeID --WHERE ((ec.FromDate Is NOT Null) OR (ec.FromDate >= @DateFrom)) --AND ((ec.ToDate Is NOT Null) OR (ec.ToDate >= @DateTo)) Select * FROM @EmpPS REturn 0 */ SELECT e.EmployeeID ,e.LastName ,e.FirstName ,e.Title ,IssueDate = ec.ToDate FROM dbo.Employee e INNER JOIN @EmpPaymentsSUM ep ON ep.EmployeeID = e.EmployeeID LEFT OUTER JOIN @EmpPS ec --dbo.EmployeeClaim ON e.EmployeeID = ec.EmployeeID WHERE e.AdminID = @AdminID --AND ec.EmployeeID IS NULL --we want to exclude emps that already have a PS for the period GROUP BY e.EmployeeID ,e.LastName ,e.FirstName ,e.Title ,ec.ToDate,ec.EmployeeID ORDER BY isnull(ec.ToDate,'30000101') DESC END ELSE --- **** REPORT DATA **** BEGIN -- PAYG SUMMARY REPORT IF @ElectronicOutput = 0 BEGIN SELECT EmployeeID = e.EmployeeID ,EntryDate = eps.EntryDate ,EmployeeName = ISNULL(e.Title + ' ','') + ISNULL(e.FirstName + ' ','') + e.LastName ,EmployeeFirstName = e.FirstName ,EmployeeLastName = e.LastName ,EmployeeAddress = ad.Address + Char(10) + ISNULL(ad.City+ ' ','') + ISNULL(ad.State + ' ','') + ISNULL(ad.PostCode,'') + ISNULL(CHAR(13) + ad.Country,'') ,EmployeeAdd = ad.Address ,EmployeeCity = ad.City ,EmployeeState = ad.State ,EmployeePostCode = ad.PostCode ,EmployeeCountry = ad.Country -- ,EmployeeStartDate = e.StartDate ,EmployeeTFN = CASE WHEN dbo.uFnIsTaxFileNumberValid(e.TFN) = 1 THEN e.TFN ELSE CASE WHEN e.TFN IS NULL THEN '000000000' WHEN NOT (ISNUMERIC(e.TFN) = 1) THEN '*** TFN ' + e.TFN + ' NOT Numeric' WHEN NOT LEN(e.TFN) = 9 THEN '*** TFN ' + e.TFN + ' NOT 9 digit number' WHEN NOT dbo.UfnIsTaxFileNumberValid(e.TFN) = 1 THEN '*** TFN ' + e.TFN + ' DOESN''T pass algorithm' ELSE e.TFN END END ,TFN = ISNULL(e.TFN,'000000000') ,Payee = ISNULL(eps.Payee,ISNULL(e.FirstName + ' ','') + e.LastName) ,DateOfBirth = e.DateOfBirth ,Dateofpayment = eps.EntryDate -- ,Memo = ep.Memo ,Wages = Round(eps.Wages,0) ,GrossPayment = Round(eps.GrossPayment,0) ,LumpSumA = Round(eps.LumpSumA,0) ,LumpSumAType = CASE WHEN eps.LumpSumA > 0 THEN 'R' ELSE '' END ,LumpSumB = Round(eps.LumpSumB,0) ,LumpSumD = Round(eps.LumpSumD,0) ,LumpSumE = Round(eps.LumpSumE,0) ,CDEP = 0 ,ReportableSuper = Round(eps.ReportableSuper,0) ,Allowances = Round(eps.NonTaxableAllowances,0) ,OtherIncome = 0 ,FBT = 0 ,Tax = Round(eps.Tax,0) ,ETPTaxable = Round(eps.ETPTaxable,0) ,ETPNonTaxable = Round(eps.ETPNonTaxable,0) ,ETPTax = Round(eps.ETPTax,0) ,ABNPayerNumber = ISNULL(abnwpn.ABN_WPN,'*** ABN or Payer No. NOT Entered') ,BranchNumber = ISNULL(@PayerBranch,'001') ,PayerName = @PayerName ,HasLodgementETP = eps.HasLodgementETPThisYear ,HasLodgementIndividual = eps.HasLodgementIndividualThisYear ,FormIDETP = '' --CASE --WHEN ep.HasLodgementETP = 1 --THEN -- CASE -- WHEN @DateTo > '20120630' -- THEN 'NAT70996-07.2012' -- ELSE 'NAT72709B-12.2008' -- END --ELSE -- CASE -- WHEN @DateTo > '20120630' -- THEN 'NAT70868-06.2012' -- ELSE 'NAT72708B-12.2008' -- END --END ,FormIDIndividual = '' --CASE --WHEN ep.HasLodgementIndividual = 1 --THEN -- CASE -- WHEN @DateTo > '20120630' -- THEN '' -- ELSE 'NAT72710B-12.2008' -- END --ELSE -- CASE -- WHEN @DateTo > '20120630' -- THEN '' -- ELSE 'NAT0046-02.2014' --'NAT 72711B-12.2008' -- END --END ,HasAllowanceEntries = eps.HasAllowanceEntries ,DateFrom = @DateFrom ,DateTo = @DateTo ,Signatory1 = @Signatory1 ,AmendmentIndicator = CASE WHEN eps.HasLodgementETPThisYear = 1 THEN 'A' ELSE 'O' END ,ETPCode = eps.ETPCode FROM @EmpPaymentsSUM eps INNER JOIN Employee e ON eps.EmployeeID = e.EmployeeID INNER JOIN dbo.#Data #d ON #d.ID = e.EmployeeID LEFT OUTER JOIN @ABN_WPN abnwpn ON abnwpn.AdminID = e.AdminID LEFT OUTER JOIN Address ad ON ad.AddressID = eps.EmployeeAddressID WHERE ((@ETP = 0) OR ((eps.ETPTaxable + eps.ETPNonTaxable + eps.ETPTax) > 0)) AND ((@ETP = 1) OR ((eps.ETPTaxable + eps.ETPNonTaxable + eps.ETPTax) = 0)) AND e.AdminID = @AdminID ORDER BY e.LastName --EntryDate END IF @ElectronicOutput = 1 BEGIN IF @LodgementID Is Not Null BEGIN -- DELETE dbo.EmployeeClaim -- FROM dbo.EmployeeClaim ec -- INNER -- JOIN dbo.#Data d -- ON d.ID = ec.EmployeeID -- WHERE IsNull(ec.FromDate, '30001231') >= @DateFrom -- AND IsNull(ec.ToDate, '18990101') <= @DateTo -- INSERT INTO dbo.EmployeeClaim (EmployeeID,EmployeeClaimType_LookupID,FromDate,ToDate,Period,Rate,TotalClaim,EmpPrefClaim,EmpUnsecClaim,ERAPrefClaim,ERAUnsecClaim,Deductions,NotionalTax,Tax,ERATax,NI,ERANI,IsTaxable,IsNI,IsManualCalc,IsManualClaim,IsFinalised,CreatedDate,CreatedBy,UpdatedDate,UpdatedBy,TS) SELECT d.ID ,EmployeeClaimType_LookupID = CASE WHEN @ETP = 1 THEN 300010 ELSE 300011 END ,@DateFrom ,@DateTo ,@LodgementID ,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 ,GetDate() ,@UpdatedBy ,GetDate() ,@UpdatedBy ,NewID() FROM dbo.#Data d Group BY d.ID END -- POPULATE VARIABLES FOR The Report SELECT @PRACTICEID = PracticeID FROM dbo.Admin WHERE AdminID = @AdminID -- Supplier data record 1 SELECT @PracticeABN = Replace(cp.PropertyValue,' ','') FROM dbo.Admin p INNER JOIN dbo.Admin_CustomProperty acp ON p.AdminID = acp.AdminID INNER JOIN dbo.CustomProperty cp ON cp.CustomPropertyID = acp.CustomPropertyID AND cp.CustomPropertyTypeID = 2 --ABN AND p.AdminID = @PracticeID Set @RunType = 'P' -- t = test; p = production Set @ReportEndDatedd = datepart(dd,@DateTo) Set @ReportEndDatemm = datepart(mm,@DateTo) Set @ReportEndDateyyyy = datepart(yyyy,@DateTo) Set @ReportEndDate = Replace( str(datepart(d,@DateTo),2) + str(datepart(m,@DateTo),2) + str(datepart(yyyy,@DateTo),4) ,' ','0') -- Mandatory Set @Datatype = 'E' Set @Typeofreport = 'A' Set @Formatofreturnmedia = 'P' IF @DateTo <= '20080630' Set @ATOReportSpecificationVersionNumber = 'FEMPA008.0' IF @DateTo <= '20090630' Set @ATOReportSpecificationVersionNumber = 'FEMPA009.0' IF @DateTo <= '20120630' Set @ATOReportSpecificationVersionNumber = 'FEMPA010.0' IF @DateTo > '20120630' Set @ATOReportSpecificationVersionNumber = 'FEMPA011.0' Set @Filler578 = '' -- Supplier data record 2 Select @Suppliername = PracticeName FROM dbo.PRACTICE Where PracticeID = @PRACTICEID Select @SupplierContactName = u.[Name] FROM [User] u WHERE UserID = ISNULL(@UserID,1) IF @SupplierContactName IS NULL Select @SupplierContactName = u.[Name] FROM [User] u INNER JOIN Admin a ON a.ReportingUserID = u.UserID WHERE a.ReportingUserID = @UserID AND a.AdminID = @AdminID SELECT @SupplierContactTelephoneNumber = n.Number FROM dbo.Practice p INNER JOIN dbo.Practice_Number pn ON p.PracticeID = pn.PracticeID INNER JOIN dbo.Number n ON n.NumberID = pn.NumberID AND n.NumberTypeID = 1 --PHONE AND pn.PracticeID = @PracticeID SELECT @SupplierFacsimileNumber = n.Number FROM dbo.Practice p INNER JOIN dbo.Practice_Number pn ON p.PracticeID = pn.PracticeID INNER JOIN dbo.Number n ON n.NumberID = pn.NumberID AND n.NumberTypeID = 2 --FAX AND pn.PracticeID = @PracticeID Select @SupplierFileReference = ShortName FROM dbo.Admin WHERE AdminID = @AdminID Set @Filler327 = '' -- Supplier data record 3 SELECT @PracticeStreetAddressID = a.AddressID FROM dbo.Practice_Address pa INNER JOIN dbo.Address a ON a.AddressID = pa.AddressID WHERE a.AddressTypeId = 2 -- 2 Street AND pa.PracticeID = @PracticeID SELECT @PracticePostalAddressID = a.AddressID FROM dbo.Practice_Address pa INNER JOIN dbo.Address a ON a.AddressID = pa.AddressID WHERE a.AddressTypeId = 1 -- 1 Postal AND pa.PracticeId = @PracticeID SELECT @SupplierStreetaddress = Replace(Replace(Address,char(13),''),char(10),' ') FROM dbo.Address WHERE AddressID = @PracticeStreetAddressID SELECT @SupplierPostalAddress = Replace(Replace(Address,char(13),''),char(10),' ') FROM dbo.Address WHERE AddressID = @PracticePostalAddressID SELECT @SupplierStreetSuburb = CASE WHEN City is not null THEN City ELSE '' END FROM dbo.Address WHERE AddressID = @PracticeStreetAddressID SELECT @SupplierPostalSuburb = CASE WHEN City is not null THEN City ELSE '' END FROM dbo.Address WHERE AddressID = @PracticePostalAddressID SELECT @SupplierStreetState = CASE WHEN State is not null THEN State ELSE '' END FROM dbo.Address WHERE AddressID = @PracticeStreetAddressID SELECT @SupplierPostalState = CASE WHEN State is not null THEN State ELSE '' END FROM dbo.Address WHERE AddressID = @PracticePostalAddressID SELECT @SupplierStreetPostCode = CASE WHEN PostCode is not null THEN PostCode ELSE '' END FROM dbo.Address WHERE AddressID = @PracticeStreetAddressID SELECT @SupplierPostalPostCode = CASE WHEN PostCode is not null THEN PostCode ELSE '' END FROM dbo.Address WHERE AddressID = @PracticePostalAddressID SELECT @SupplierStreetCountry = CASE WHEN Country is not null THEN Country ELSE '' END FROM dbo.Address WHERE AddressID = @PracticeStreetAddressID SELECT @SupplierPostalCountry = CASE WHEN Country is not null THEN Country ELSE '' END FROM dbo.Address WHERE AddressID = @PracticePostalAddressID SELECT @SupplierEmail = ISNULL(n.Number,'Enter email') FROM dbo.Practice_Number pn INNER JOIN dbo.Number n ON pn.NumberID = n.NumberID WHERE n.NumberTypeId = 4 -- 4 Email AND pn.PracticeID = @PracticeID IF @SupplierEmail IS NULL SELECT @SupplierEmail = '' Set @Filler275 = '' -- Payer identity record -- ****************************************** -- POPULATE THE DATA FOR THE TEXT FILE -- ****************************************** /* this part of the proc was in the original verison of repsp_EmployeePaymentSummaryElectronic which is now combined as part of repsp_EmployeePaymentSummary EXECUTE dbo.repsp_EmployeePaymentSummary @AdminID ,@DateFrom ,@DateTo ,NULL -- Employee Inclause ,0 -- Show entry ,@ETP -- Return ETP bit ,1 -- ElectronicOutput */ INSERT INTO #EmpPaySum SELECT EmployeeID = e.EmployeeID ,EntryDate = ep.EntryDate ,EmployeeName = ISNULL(e.Title + ' ','') + ISNULL(e.FirstName + ' ','') + e.LastName ,EmployeeFirstName = e.FirstName ,EmployeeLastName = e.LastName ,EmployeeAddress = ad.Address + Char(10) + Char(13) + ISNULL(ad.City+ ' ','') + ISNULL(ad.State + ' ','') + ISNULL(ad.PostCode,'') + ISNULL(CHAR(13) + ad.Country,'') ,EmployeeAdd = ad.Address + Char(10) + Char(13) ,EmployeeCity = ad.City ,EmployeeState = ad.State ,EmployeePostCode = ad.PostCode ,EmployeeCountry = ad.Country -- ,EmployeeStartDate = e.StartDate ,EmployeeTFN = CASE WHEN e.TFN IS NULL THEN '000000000' WHEN NOT (ISNUMERIC(replace(e.TFN,' ','')) = 1) THEN '*** TFN ' + e.TFN + ' NOT Numeric' WHEN NOT LEN(replace(e.TFN,' ','')) = 9 THEN '*** TFN ' + e.TFN + ' NOT 9 digit number' WHEN NOT dbo.UfnIsTaxFileNumberValid(e.TFN) = 1 THEN '*** TFN ' + e.TFN + ' DOESN''T pass algorithm' ELSE e.TFN END ,TFN = ISNULL(e.TFN,'000000000') ,Payee = ISNULL(ep.Payee,ISNULL(e.FirstName + ' ','') + e.LastName) ,DateOfBirth = e.DateOfBirth ,Dateofpayment = ep.EntryDate -- ,Memo = ep.Memo ,Wages = Round(ep.Wages,0) ,GrossPayment = Round(ep.GrossPayment,0) ,LumpSumA = Round(ep.LumpSumA,0) ,LumpSumB = Round(ep.LumpSumB,0) ,LumpSumD = Round(ep.LumpSumD,0) ,LumpSumE = Round(ep.LumpSumE,0) ,ReportableSuper = ep.ReportableSuper ,NonTaxableAllowances = ep.NonTaxableAllowances ,OtherIncome = Null ,FBT = Null ,Tax = Round(ep.Tax,0) ,ETPTaxable = Round(ep.ETPTaxable,0) ,ETPNonTaxable = Round(ep.ETPNonTaxable,0) ,ETPTax = Round(ep.ETPTax,0) ,ABNPayerNumber = ISNULL(abnwpn.ABN_WPN,'*** ABN or Payer No. NOT Entered') ,BranchNumber = ISNULL(@PayerBranch,'001') ,PayerName = @PayerName ,PaymentTypeCode = ep.ETPCode ,AmendmentIndicator = CASE WHEN ep.HasLodgementIndividualThisYear = 1 THEN 'A' ELSE 'O' END -- Amended or Original ,AmendmentIndicatorETP = CASE WHEN ep.HasLodgementETPThisYear = 1 THEN 'A' ELSE 'O' END -- Amended or Original FROM @EmpPaymentsSUM ep INNER JOIN Employee e ON ep.EmployeeID = e.EmployeeID INNER JOIN dbo.#Data #d ON #d.ID = e.EmployeeID LEFT OUTER JOIN @ABN_WPN abnwpn ON abnwpn.AdminID = e.AdminID LEFT OUTER JOIN Address ad ON ad.AddressID = ep.EmployeeAddressID WHERE ((@ETP = 0) OR ((ep.ETPTaxable + ep.ETPNonTaxable + ep.ETPTax) > 0)) AND ((@ETP = 1) OR ((ep.ETPTaxable + ep.ETPNonTaxable + ep.ETPTax) = 0)) AND e.AdminID = @AdminID ORDER BY e.LastName --EntryDate -- This bit was for multiple jobs (admins). -- At the monent it only outputs job-by-job -- IF NOT @AdminInClause IS NULL -- BEGIN -- EXEC dbo.usp_Split @AdminInClause -- END -- ELSE -- BEGIN -- INSERT -- INTO dbo.#Data -- SELECT AdminID -- FROM dbo.Admin -- WHERE IsNotTaxReg = 0 -- This is where you select the jobs for inclusion -- AND PracticeID = @PracticeID -- AND AdminID = @AdminID -- AND AdminStatusID in (1,2) -- AND AdminTypeID > 1000 ---- END INSERT INTO @Admins SELECT AdminID = a.AdminID ,AdminTypeID = a.AdminTypeID ,ABN = NULL ,WPN = NULL ,ABN_WPN = NULL ,CAC = '001' -- Branch Number Mandatory *** UPDATED BELOW IF SET *** ,AdminFullName = convert(nchar(200),a.Name ) ,PayerName = NULL -- ,PayerTradingName = NULL ,PayerAddress = NULL ,PayerAddress1 = Null ,PayerAddress2 = Null ,PayerSuburb = Null -- Mandatory ,PayerState = Null -- Mandatory ,PayerPostCode = Null -- Mandatory ,PayerCountry = Null ,ReportingUserID = a.ReportingUserID ,ContactName = '' ,ContactTelephoneNumber = @SupplierContactTelephoneNumber -- FORMAT 02b1234b5678 or 0412b123b456 ,ContactFacsimileNumber = @SupplierFacsimileNumber -- FORMAT 02b1234b5678 ,ETPPayerType = 'P' FROM dbo.Admin a WHERE a.AdminID = @AdminID -- INNER -- JOIN dbo.#Data #d -- ON #d.ID = a.AdminID -- ORDER BY a.AdminID -- -- ADD JOB SUFFIX UPDATE @Admins SET AdminFullName = rtrim(AdminFullName) + isnull(' ' + cpSuffix.PropertyValue,'') FROM admin_customproperty acp INNER JOIN @Admins a ON a.AdminID = acp.AdminID INNER JOIN CustomProperty cpSuffix ON cpSuffix.CustomPropertyID = acp.CustomPropertyID AND cpSuffix.CustomPropertyTypeID in (12) -- ADMINS FOR HISTORY -- UPDATE DATA -- Payer Name UPDATE @Admins SET PayerName = cpPN.PropertyValue FROM admin_customproperty acp INNER JOIN @Admins a ON a.AdminID = acp.AdminID INNER JOIN CustomProperty cpPN ON cpPN.CustomPropertyID = acp.CustomPropertyID AND cpPN.CustomPropertyTypeID in (607) WHERE NOT cpPN.PropertyValue IS NULL UPDATE @Admins SET PayerName = Isnull(PayerName,isNull(AdminFullName,'')) -- CAC Branch Number /* */ UPDATE @Admins SET CAC = Isnull(cpBranch.PropertyValue,a.CAC) FROM admin_customproperty acp INNER JOIN @Admins a ON a.AdminID = acp.AdminID INNER JOIN CustomProperty cpBranch ON cpBranch.CustomPropertyID = acp.CustomPropertyID AND cpBranch.CustomPropertyTypeID in (610) AND ISNULL(cpBranch.AdminTypeID,@AdminTypeID) = @AdminTypeID UPDATE @Admins SET PayerTradingName = '' -- SPLIT OUT ADDRESS LINES UPDATE @Admins SET PayerAddress = Replace(Replace(ad.Address,char(13),'ƒ'),char(10),'ƒ') ,PayerSuburb = convert(nchar(27),ad.City) -- Mandatory ,PayerState = convert(nchar(3),ad.State) -- Mandatory ,PayerPostCode = convert(nchar(4),ad.PostCode) -- Mandatory ,PayerCountry = convert(nchar(20),Isnull(ad.Country,'')) FROM dbo.Address ad INNER JOIN dbo.Admin_Address aa ON aa.AddressID = ad.AddressID INNER JOIN @Admins a ON a.AdminID = aa.AdminID WHERE ad.IsMailing = 1 UPDATE @Admins SET PayerAddress = Replace(PayerAddress,'ƒƒ','ƒ') UPDATE @Admins SET PayerAddress1 = CASE WHEN Charindex(PayerAddress,'ƒ') > 0 THEN convert(nchar(38),left(PayerAddress, Charindex(PayerAddress,'ƒ'))) ELSE convert(nchar(38),PayerAddress) END ,PayerAddress2 = convert(nchar(38),Right(PayerAddress, Charindex(PayerAddress,'ƒ'))) FROM @Admins UPDATE @Admins SET PayerAddress1 = REPLACE (PayerAddress1,'ƒ',' ') ,PayerAddress2 = REPLACE (PayerAddress2,'ƒ',' ') -- CONTACT NAME UPDATE @Admins SET ContactName = u.Name FROM @Admins a INNER JOIN dbo.[User] u ON u.UserID = a.ReportingUserID -- ABN WPN UPDATE @Admins SET ABN = convert(nchar(11),REPLACE(cpABN.PropertyValue,' ','')) FROM admin_customproperty acp INNER JOIN @Admins a ON a.AdminID = acp.AdminID INNER JOIN CustomProperty cpABN ON cpABN.CustomPropertyID = acp.CustomPropertyID AND cpABN.CustomPropertyTypeID in (2) UPDATE @Admins SET WPN = convert(nchar(11),REPLACE(cpWPN.PropertyValue,' ','')) FROM admin_customproperty acp INNER JOIN @Admins a ON a.AdminID = acp.AdminID INNER JOIN CustomProperty cpWPN ON cpWPN.CustomPropertyID = acp.CustomPropertyID AND cpWPN.CustomPropertyTypeID in (4) UPDATE @Admins SET ABN_WPN = ISNULL(WPN,ABN) -- Software record SELECT @Softwareproducttype = 'COMMERCIAL Exalt Technologies, MYOB Insolvency ' + Setting FROM dbo.ApplicationSetting WHERE SettingId = 1 SET @Filler536 = '' -- PAYGW Payment Summary Individual Non-business data record INSERT INTO @Emps SELECT PayeeTFN = convert(nchar(9),replace(eps.TFN,' ','')) -- Mandatory ,PayeeSurname = convert(nchar(30),eps.EmployeeLastName) -- Mandatory ,PayeeFirstGivenName = CASE WHEN CharIndex(' ',eps.EmployeeFirstName) = 0 THEN Convert(nchar(15),eps.EmployeeFirstName) ELSE Convert(nchar(15),LEFT(eps.EmployeeFirstName,CharIndex(' ',eps.EmployeeFirstName))) -- Mandatory END ,PayeeSecondGivenName = CASE WHEN CharIndex(' ',eps.EmployeeFirstName) = 0 THEN '' ELSE ISNULL(convert(nchar(15),RIGHT(eps.EmployeeFirstName,LEN(eps.EmployeeFirstName) - CharIndex(' ',eps.EmployeeFirstName))),'') END --,PayeeAddress = Replace(eps.EmployeeAdd,char(13) + char(10),'ƒ') ,PayeeAddress = Replace(Replace(eps.EmployeeAdd,char(13),'ƒ'),char(10),'ƒ') ,PayeeAddress1 = '' ,PayeeAddress2 = '' ,PayeeSuburb = convert(nchar(27),eps.EmployeeCity) -- Mandatory ,PayeeState = convert(nchar(3),eps.EmployeeState) -- Mandatory ,PayeePostCode = convert(nchar(4),eps.EmployeePostCode) -- Mandatory ,PayeeCountry = convert(nchar(20),Isnull(eps.EmployeeCountry,'')) ,PeriodPaymentStart = Replace( str(datepart(d,@DateFrom),2) + str(datepart(m,@DateFrom),2) + str(datepart(yyyy,@DateFrom),4) ,' ','0') ,PeriodPaymentEnd = Replace( str(datepart(d,@DateTo),2) + str(datepart(m,@DateTo),2) + str(datepart(yyyy,@DateTo),4) ,' ','0') ,TotalTaxWithheld = REPLACE(STR(FLOOR(eps.Tax),8),' ',0) --REPLICATE('0', 8 - Len(eps.Tax)) + ,STR(eps.Tax) -- Mandatory ,GrossPayment = REPLACE(STR(FLOOR(eps.GrossPayment),8),' ',0) -- Mandatory ,TotalAllowances = REPLACE(STR(FLOOR(eps.NonTaxableAllowances),8),' ',0) -- ,LumpSumPaymentA = REPLACE(STR(FLOOR(eps.LumpSumA),8),' ',0) -- Mandatory ,LumpSumPaymentB = REPLACE(STR(FLOOR(eps.LumpSumB),8),' ',0) -- Mandatory ,LumpSumPaymentD = REPLACE(STR(FLOOR(eps.LumpSumD),8),' ',0) -- Mandatory ,LumpSumPaymentE = REPLACE(STR(FLOOR(eps.LumpSumE),8),' ',0) -- Mandatory ,PayeeDateOfBirth = ISNULL( Replace( str(datepart(d,eps.DateOfBirth),2) -- If only year is recorded then return as 00 + str(datepart(m,eps.DateOfBirth),2) -- If only year is recorded then return as 00 + str(datepart(yyyy,eps.DateOfBirth),4) ,' ','0') ,'00000000' ) -- Mandatory ,PayeeETPDateofpayment = ISNULL( Replace( str(datepart(d,eps.Dateofpayment),2) + str(datepart(m,eps.Dateofpayment),2) + str(datepart(yyyy,eps.Dateofpayment),4) ,' ','0') ,'00000000' ) -- Mandatory -- ,PayeeDateStarted = -- Replace( -- str(datepart(d,eps.PayeeDateStarted),2) -- + str(datepart(m,eps.PayeeDateStarted),2) -- + str(datepart(yyyy,eps.PayeeDateStarted),4) -- ,' ','0') -- Mandatory -- ,ETPTaxable = REPLACE(STR(FLOOR(eps.ETPTaxable),8),' ',0) ,ETPNonTaxable = REPLACE(STR(FLOOR(eps.ETPNonTaxable),8),' ',0) ,ETPTax = REPLACE(STR(FLOOR(eps.ETPTax),8),' ',0) ,CommunityDevelopmentEmploymentProject = '00000000' -- Mandatory ,OtherIncome = '00000000' -- Mandatory ,ReportableFringeBenefits = '00000000' -- Mandatory ,PaymentTypeCode ,AmendmentIndicator ,AmendmentIndicatorETP ,ReportableSuper = REPLACE(STR(FLOOR(eps.ReportableSuper),8),' ',0) FROM #EmpPaySum eps -- SPLIT OUT ADDRESS LINES UPDATE @Emps SET PayeeAddress = Replace(PayeeAddress,'ƒƒ','ƒ') /* UPDATE @Emps SET PayeeAddress = Replace(PayeeAddress,'ƒ',' ') */ UPDATE @Emps SET PayeeAddress1 = CASE WHEN Charindex('ƒ',PayeeAddress) = 0 THEN convert(nchar(38),PayeeAddress) ELSE REPLACE(Convert(nchar(38),LEFT(PayeeAddress,CharIndex('ƒ',PayeeAddress))),'ƒ',' ') -- Mandatory END ,PayeeAddress2 = CASE WHEN CharIndex('ƒ',PayeeAddress) = 0 THEN '' ELSE ISNULL(convert(nchar(38),RIGHT(PayeeAddress,LEN(PayeeAddress) - CharIndex('ƒ',PayeeAddress))),'') END FROM @Emps UPDATE @Emps SET PayeeAddress1 = Replace(PayeeAddress1,'ƒ',' ') ,PayeeAddress2 = Replace(PayeeAddress2,'ƒ',' ') --SELECT PayeeAddress1 , PayeeAddress2 ,REPLACE(PayeeAddress,'ƒ',' '),Charindex('ƒ',PayeeAddress),* FROM @Emps SET @Filler316 = '' SET @Filler283 = '' --SET @Filler275 = '' SET @ReportableEmployerSuperannuationContribs = '00000000' SET @LumpSumPaymentAType = 'R' -- R – where payment was for a genuine redundancy; T – where payment was not a payment for a genuine redundancy SET @WorkplaceGiving = '00000000' SET @UnionProfFees = '00000000' SET @ExemptForeignEmploymentIncome = '00000000' SET @DeductibleAmountPurchasePriceofAnnuity = '00000000' -- Eligible Termination Payment (ETP) data record Set @Filler380 = '' Set @Filler377 = '' --File total record Set @FileTotal = 'FILE-TOTAL' Set @Filler607 = '' --Select * FROM #EmpPaySum --Select * FROM @Emps --return 0 -- ******************************************************** -- REPORT OUTPUT INSERT INTO @Output SELECT -- Supplier data record 1 --Character position Field length Field format Field type Field name Data item Definitions --& edit rules --1-3 3 N M Record length (=628) SR01 7.1 --4-17 14 AN M Record identifier (=IDENTREGISTER1) SR02 7.2 --18-28 11 N M Supplier Australian Business Number SR03 7.3 --29-29 1 A M Run type (T=Test, P=Production) SR04 7.4 --30-37 8 DT M Report end date (DDMMCCYY) SR05 7.5 --38-38 1 A M Data type (=E) SR06 7.6 --39-39 1 A M Type of report (=A) SR07 7.7 --40-40 1 A M Format of return media (=P) SR08 7.8 --41-50 10 AN M ATO Report specification version number (=FEMPA007.4) SR09 7.9 --51-60 10 AN O ATO Corporate External Gateway User ID SR10 7.10 --61-628 568 A S Filler SR11 7.11 1 -- order ,'628' + 'IDENTREGISTER1' + @PracticeABN + @RunType + @ReportEndDate + @Datatype + @Typeofreport + @Formatofreturnmedia + @ATOReportSpecificationVersionNumber + @Filler578 -- + CHAR(10) + CHAR(13) -- Supplier data record 2 --Character position Field length Field format Field type Field name Data item Definitions & edit rules --1-3 3 N M Record length (=628) SR12 7.1 --4-17 14 AN M Record identifier (=IDENTREGISTER2) SR13 7.12 --18-217 200 AN M Supplier name SR14 7.13 --218-255 38 AN M Supplier contact name SR15 7.14 --256-270 15 AN M Supplier contact telephone number SR16 7.15 --271-285 15 AN O Supplier facsimile number SR17 7.16 --286-301 16 AN O Supplier file reference SR18 7.17 --302-628 327 A S Filler SR19 7.11 INSERT INTO @Output SELECT 2 -- order ,'628' + 'IDENTREGISTER2' + @Suppliername + @SupplierContactName + @SupplierContactTelephoneNumber + @SupplierFacsimileNumber + @SupplierFileReference + @Filler327 -- Supplier data record 3 --1-3 3 N M Record length (=628) SR20 7.1 --4-17 14 AN M Record identifier (=IDENTREGISTER3) SR21 7.18 --18-55 38 AN M Street address - line 1 SR22 7.19 --56-93 38 AN O Street address - line 2 SR23 7.19 --94-120 27 AN M Suburb, town or city SR24 7.20 --121-123 3 A M State or territory SR25 7.21 --124-127 4 N M Postcode SR26 7.22 --128-147 20 AN O Country SR27 7.23 --148-185 38 AN O Postal address - line 1 SR28 7.24 --186-223 38 AN C Postal address - line 2 SR29 7.24 --224-250 27 AN C Suburb, town or city SR30 7.25 --251-253 3 A C State or territory SR31 7.26 --254-257 4 N C Postcode SR32 7.27 --258-277 20 AN O Country SR33 7.28 --278-353 76 AN O Supplier e-mail address SR34 7.29 --354-628 275 A S Filler SR35 7.11 INSERT INTO @Output SELECT 3 -- order ,'628' + 'IDENTREGISTER3' + @SupplierStreetaddress + @SupplierStreetSuburb + @SupplierStreetState + @SupplierStreetPostCode + @SupplierStreetCountry + @SupplierPostaladdress + @SupplierPostalSuburb + @SupplierPostalState + @SupplierPostalPostCode + @SupplierPostalCountry + @SupplierEmail + @Filler275 -- Payer identity record --Character position Field length Field format Field type Field name Data item Definitions & edit rules --1-3 3 N M Record length (=628) PR01 7.1 --4-11 8 A M Record identifier (=IDENTITY) PR02 7.30 --12-22 11 N M Payer Australian Business Number or Withholding Payer Number PR03 7.31 --23-25 3 N M Branch Number PR04 7.32 --26-29 4 N M Financial year (CCYY) PR05 7.33 --30-229 200 AN M Payer name PR06 7.34 --230-429 200 AN O Payer trading name PR07 7.35 --430-467 38 AN M Payer address - line 1 PR08 7.36 --468-505 38 AN O Payer address - line 2 PR09 7.36 --506-532 27 AN M Suburb, town or city PR10 7.37 --533-535 3 A M State or territory PR11 7.38 --536-539 4 N M Postcode PR12 7.39 --540-559 20 AN O Country PR13 7.40 --560-597 38 AN O Contact name PR14 7.41 --598-612 15 AN O Contact telephone number PR15 7.42 --613-627 15 AN O Contact facsimile number PR16 7.43 --628-628 1 A C ETP payer type PR17 7.44 INSERT INTO @Output SELECT 4 -- order , '628' + 'IDENTITY' + isnull(ABN_WPN,'') -- Mandatory + CAC + Convert(nchar(4),YEAR(dbo.uFnFinancialYearEndDate(@DateTo))) -- Mandatory + PayerName + PayerTradingName + PayerAddress1 + PayerAddress2 + PayerSuburb + PayerState + PayerPostCode + PayerCountry + ContactName + ContactTelephoneNumber + ContactFacsimileNumber + ' ' --filler 1 -- ETPPayerType FROM @Admins -- Software record --Character --position Field length Field format Field type Field name Data item Definitions & edit rules --1-3 3 N M Record length (=628) SW01 7.1 --4-11 8 A M Record identifier (=SOFTWARE) SW02 7.45 --12-91 80 AN M Software product type SW03 7.46 --92-92 1 A M ECI tested (=Y or N) SW05 7.70 --93-628 536 A S Filler SW04 7.11 INSERT INTO @Output SELECT 5 -- order , '628' + 'SOFTWARE' + @Softwareproducttype + 'Y' + @Filler536 -- INDIVIDUAL NON-BUSINESS PAYMENT SUMMARY DATA RECORD --Character position Field length Field format Field type Field name Definitions & edit rules --1-3 3 N M Record length (=628) 7.1 --4-7 4 A M Record identifier (=DINB) 7.46 --8-8 1 A M Income type (= P or S) 7.95 --9-17 9 N M Payee tax file number 7.47 --18-25 8 DT O Payee date of birth (DDMMCCYY) 7.48 --26-55 30 AN M Payee surname 7.49 --56-70 15 AN C Payee first given name 7.50 --71-85 15 AN O Payee second given name 7.51 --86-123 38 AN M Payee address - line 1 7.52 --124-161 38 AN O Payee address - line 2 7.52 --162-188 27 AN M Suburb, town or locality 7.53 --189-191 3 A M State or territory 7.54 --192-195 4 N M Postcode 7.55 --196-215 20 AN C Country 7.56 --216-223 8 DT M Period of payment start date (DDMMCCYY) 7.57 --224-231 8 DT M Period of payment end date (DDMMCCYY) 7.58 --232-239 8 N M Total tax withheld (dollars) 7.59 --240-247 8 N M Gross payments (dollars) 7.61 --248-255 8 N M Total allowances (dollars) 7.62 --256-263 8 N M Lump sum payment A (dollars) 7.63 --264-271 8 N M Lump sum payment B (dollars) 7.63 --272-279 8 N M Lump sum payment D (dollars) 7.63 --280-287 8 N M Lump sum payment E (dollars) 7.63 --288-295 8 N M Community development employment project (dollars) 7.64 --296-303 8 N M Other income (dollars) 7.65 --304-311 8 N M Reportable fringe benefits (dollars) 7.66 --312-312 1 A M Amendment indicator (=A or O) 7.92 --313-628 316 A S Filler 7.10 --DEBUG /* SELECT 6 -- order ,'628' + 'DINB' + 'S' --S - for salary or wages , PayeeTFN -- Mandatory , PayeeDateOfBirth -- Mandatory , PayeeSurname -- Mandatory , PayeeFirstGivenName -- Mandatory , PayeeSecondGivenName , PayeeAddress1 -- Mandatory , PayeeAddress2 , PayeeSuburb -- Mandatory , PayeeState -- Mandatory , PayeePostCode -- Mandatory , PayeeCountry , PeriodPaymentStart -- Mandatory , PeriodPaymentEnd -- Mandatory , TotalTaxWithheld -- Mandatory , GrossPayment -- Mandatory , TotalAllowances -- Mandatory , LumpSumPaymentA -- Mandatory , LumpSumPaymentB -- Mandatory , LumpSumPaymentD -- Mandatory , LumpSumPaymentE -- Mandatory , CommunityDevelopmentEmploymentProject -- Mandatory ,@ATOReportSpecificationVersionNumber , CASE WHEN @ATOReportSpecificationVersionNumber = 'FEMPA008.0' THEN OtherIncome -- Mandatory + ReportableFringeBenefits -- Mandatory + AmendmentIndicator + @Filler316 WHEN @ATOReportSpecificationVersionNumber = 'FEMPA009.0' THEN ' ' --OtherIncome left blank + ReportableFringeBenefits -- Mandatory + AmendmentIndicator + @ReportableEmployerSuperannuationContribs + @LumpSumPaymentAType + @WorkplaceGiving + @UnionProfFees + @ExemptForeignEmploymentIncome + @Filler283 ELSE -- 'FEMPA010.0' ' ' --OtherIncome left blank (Filler) + ReportableFringeBenefits -- Mandatory + AmendmentIndicator + @ReportableEmployerSuperannuationContribs + @LumpSumPaymentAType + @WorkplaceGiving + @UnionProfFees + @ExemptForeignEmploymentIncome + @DeductibleAmountPurchasePriceofAnnuity + @Filler275 END FROM @EMPS WHERE (@ETP = 0) */ INSERT INTO @Output SELECT 6 -- order ,'628' + 'DINB' + 'S' --S - for salary or wages + PayeeTFN -- Mandatory + PayeeDateOfBirth -- Mandatory + PayeeSurname -- Mandatory + PayeeFirstGivenName -- Mandatory + PayeeSecondGivenName + PayeeAddress1 -- Mandatory + PayeeAddress2 + PayeeSuburb -- Mandatory + PayeeState -- Mandatory + PayeePostCode -- Mandatory + PayeeCountry + PeriodPaymentStart -- Mandatory + PeriodPaymentEnd -- Mandatory + TotalTaxWithheld -- Mandatory + GrossPayment -- Mandatory + TotalAllowances -- Mandatory + LumpSumPaymentA -- Mandatory + LumpSumPaymentB -- Mandatory + LumpSumPaymentD -- Mandatory + LumpSumPaymentE -- Mandatory + CommunityDevelopmentEmploymentProject -- Mandatory + CASE WHEN @ATOReportSpecificationVersionNumber = 'FEMPA008.0' THEN OtherIncome -- Mandatory + ReportableFringeBenefits -- Mandatory + AmendmentIndicator + @Filler316 WHEN @ATOReportSpecificationVersionNumber = 'FEMPA009.0' THEN ' ' --OtherIncome left blank + ReportableFringeBenefits -- Mandatory + AmendmentIndicator + ReportableSuper + CASE WHEN LumpSumPaymentA = '00000000' THEN ' ' ELSE @LumpSumPaymentAType END + @WorkplaceGiving + @UnionProfFees + @ExemptForeignEmploymentIncome + @Filler283 WHEN @ATOReportSpecificationVersionNumber = 'FEMPA010.0' THEN '00000000' --OtherIncome left blank + ReportableFringeBenefits -- Mandatory + AmendmentIndicator + ReportableSuper + ' ' -- @LumpSumPaymentAType + @WorkplaceGiving + @UnionProfFees + @ExemptForeignEmploymentIncome + @DeductibleAmountPurchasePriceofAnnuity + @Filler275 ELSE -- 'FEMPA011.0' '00000000' --OtherIncome left blank + ReportableFringeBenefits -- Mandatory + AmendmentIndicator + ReportableSuper + CASE WHEN LumpSumPaymentA = '00000000' THEN ' ' ELSE @LumpSumPaymentAType END + @WorkplaceGiving + @UnionProfFees + @ExemptForeignEmploymentIncome + @DeductibleAmountPurchasePriceofAnnuity + @Filler275 END FROM @EMPS WHERE (@ETP = 0) -- PAYGW Payment Summary Voluntary Agreement data record -- NOT SUPPORTED -- 7 -- order --Character position Field length Field format Field type Field name Data item Definitions & edit rules --1-3 3 N M Record length (=628) VA01 7.1 --4-7 4 A M Record identifier (=DBUS) VA02 7.69 --8-18 11 N M Payee Australian Business Number VA03 7.71 --19-26 8 DT O Payee date of birth (DDMMCCYY) VA04 7.50 --27-56 30 AN M Payee surname VA05 7.51 --57-71 15 AN M Payee first given name VA06 7.52 --72-86 15 AN O Payee second given name VA07 7.53 --87-124 38 AN M Payee address - line 1 VA08 7.54 --125-162 38 AN O Payee address - line 2 VA09 7.54 --163-189 27 AN M Suburb, town or city VA10 7.55 --190-192 3 A M State or territory VA11 7.56 --193-196 4 N M Postcode VA12 7.57 --197-216 20 AN O Country VA13 7.58 --217-224 8 DT M Period of payment start date (DDMMCCYY) VA14 7.59 --225-232 8 DT M Period of payment end date (DDMMCCYY) VA15 7.60 --233-240 8 N M Total tax withheld (dollars) VA16 7.61 --241-245 5 N M Withholding rate VA17 7.62 --246-253 8 N M Gross payments, including the market value of non-cash benefits (dollars) VA18 7.72 --254-628 375 A S Filler VA19 7.11 -- PAYGW Payment Summary Labour Hire & Other Specified Payments data record -- NOT SUPPORTED -- 8 -- order -- PAYGW Payment Summary Personal Services Attributed Income data record -- NOT SUPPORTED -- 9 -- order -- Eligible Termination Payment (ETP) data record --Character position Field length Field format Field type Field name Data item Definitions & edit rules --1-3 3 N M Record length (=628) ET01 7.1 --4-7 4 A M Record identifier (=DETP) ET02 7.73 --8-16 9 N M Payee Tax File Number ET03 7.49 --17-46 30 AN M Payee surname ET04 7.51 --47-61 15 AN M Payee first given name ET05 7.52 --62-76 15 AN O Payee second given name ET06 7.53 --77-114 38 AN M Payee address - line 1 ET07 7.54 --115-152 38 AN O Payee address - line 2 ET08 7.54 --153-179 27 AN M Suburb, town or city ET09 7.55 --180-182 3 A M State or territory ET10 7.56 --183-186 4 N M Postcode ET11 7.57 --187-206 20 AN O Country ET12 7.58 --207-214 8 DT C Payee date of birth (DDMMCCYY) ET13 7.74 --215-222 8 DT M Date of payment (DDMMCCYY) ET14 7.75 --223-230 8 DT M Date started (DDMMCCYY) ET15 7.76 --231-235 5 N C Number of days before 1 July 1983 ET16 7.77 --236-240 5 N M Number of days after 30 June 1983 ET17 7.78 --241-248 8 N C CGT exempt component (dollars) ET18 7.79 --249-256 8 N C Non-qualifying component (dollars) ET19 7.80 --257-264 8 N C Undeducted contributions (dollars) ET20 7.81 --265-272 8 N C Concessional component (dollars) ET21 7.82 --273-280 8 N C Pre July 1983 component (dollars) ET22 7.83 --281-288 8 N C Post June 1983 untaxed element (dollars) ET23 7.84 --289-296 8 N C Post June 1983 taxed element (dollars) ET24 7.85 --297-304 8 N C Post June 1994 invalidity component (dollars) ET25 7.86 --305-312 8 N M Gross amount of payment (dollars) ET26 7.87 --313-320 8 N M Total tax withheld (dollars) ET27 7.88 --321-328 8 N M Assessable amount (dollars) ET28 7.89 --329-329 1 A M Death benefit (=Y or N) ET29 7.90 --330-330 1 A C Type of death benefit (=T or O) ET30 7.91 --331-628 298 A S Filler ET31 7.11 -- v11.0.1 /* Character position Field length Field format Field type Field name Reference number 1-3 3 N M Record length (=628) 7.1 4-7 4 A M Record identifier (=DEMP) 7.82 8-16 9 N M Payee tax file number 7.48 17-46 30 AN M Payee surname or family name 7.50 47-61 15 AN C Payee first given name 7.51 62-76 15 AN O Payee second given name 7.52 77-114 38 AN M Payee address line 1 7.53 115-152 38 AN O Payee address line 2 7.53 153-179 27 AN M Payee suburb, town or locality 7.54 180-182 3 A M Payee state or territory 7.55 183-186 4 N M Payee postcode 7.56 187-206 20 AN C Payee country 7.57 207-214 8 DT M Payee date of birth (DDMMCCYY) 7.49 215-222 8 DT M Date of payment (DDMMCCYY) 7.83 223-230 8 N M Total tax withheld (dollars) 7.60 231-238 8 N M Taxable component (dollars) 7.84 239-246 8 N M Tax free component (dollars) 7.85 247-247 1 A M Payment type code (=R, O, S, P, D, B, N or T) 7.86 248-248 1 A M Amendment indicator (=A or O) 7.67 249-628 380 A S Filler 7.10 */ INSERT INTO @Output SELECT 10 -- order , '628' + 'DEMP' + PayeeTFN -- Mandatory + PayeeSurname -- Mandatory + PayeeFirstGivenName -- Mandatory + PayeeSecondGivenName + PayeeAddress1 -- Mandatory + PayeeAddress2 + PayeeSuburb -- Mandatory + PayeeState -- Mandatory + PayeePostCode -- Mandatory + PayeeCountry + PayeeDateOfBirth + PayeeETPDateofpayment + ETPTax --223-230 8 N M Total tax withheld (dollars) 7.60 + ETPTaxable --231-238 8 N M Taxable component (dollars) 7.79 + ETPNonTaxable -- 239-246 8 N M Tax free component (dollars) 7.80 + CASE WHEN @ATOReportSpecificationVersionNumber = 'FEMPA011.0' THEN + PaymentTypeCode + AmendmentIndicatorETP --251-251 1 A M Amendment indicator (=A or O) 7.67 + @Filler380 ELSE 'N' -- Death benefit (=Y or N) Mandatory + ' ' --C Type of death benefit (=T or O) ET30 7.91 + 'N' --249-249 1 A M Transitional termination payment (=Y or N) 7.83 + 'N' --250-250 1 A M Is this payment part of a payment made in an earlier income year for the same termination? (=Y or N) 7.84 + AmendmentIndicatorETP --251-251 1 A M Amendment indicator (=A or O) 7.67 + @Filler377 END FROM @EMPS WHERE (@ETP = 1) --331-628 298 A S Filler ET31 7.11 --File total record -- calc no of records Select @NumberOfRecords = Count(Outputfield) + 1 from @Output --Character position Field length Field format Field type Field name Definitions & edit rules --1-3 3 N M Record length (=628) 7.1 --4-13 10 AN M Record identifier (=FILE-TOTAL) 7.90 --14-21 8 N M Number of records 7.91 --22-628 607 A S Filler 7.10 INSERT INTO @Output SELECT 11 -- order , '628' + @FileTotal + REPLACE(STR(FLOOR(@NumberOfRecords),8),' ',0) -- including this one + @Filler607 -- INSERTS A LINE FEED /* IF @ATOReportSpecificationVersionNumber = 'FEMPA008.0' BEGIN INSERT INTO @Output SELECT 12 -- order ,'' END */ SELECT * FROM @Output Order By OutputOrder END END -- Debug -- Select * fROM #EmpPaySum SET NOCOUNT OFF SET ANSI_WARNINGS ON GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO GRANT EXECUTE ON dbo.repsp_EmployeePaymentSummary TO [insol2_users] GO