{"text_format":"1","workbook":"xlsx","n_sheets":7,"sheets":["Summary","Script","Sheet2","Modifications","Detail","PickListDetails","Duplicates"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":2,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Summary","index":0,"n_rows":9,"n_cols":6,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
[]
[null,null,null,null,"Version 1.0"]
[]
["SQLServerName\\Instance","MYOB6BGYH72\\SQL2017DEV",null,null,"The SQL server name can be found on the title bar of MYOB AE. E.g. MYOB6BGYH72"]
[]
["MYOB AE Database Name","VPMDPMMEL",null,null,"The MYOB AE database name can be found on the title bar of MYOB AE. E.g.VPMSER"]
[]
[]
["SQL Admin User Password",null,null,null,"Enter your SQL Admin (sa) password.","You may need to contact your IT personnel for this password."]

{"sheet":"Script","index":1,"n_rows":215,"n_cols":2,"rows_kept":215,"cut":false,"hidden":true,"merged":[]}
[]
["Declare @SQL nvarchar(4000), @Year int, @RETURN_TABLE_PREFIX CHAR(4), @RETURN_TABLE_SUFFIX CHAR(2), @RETURN_TABLE_SUFFIX_PRIOR CHAR(2)"]
["Declare @AETaxDatabase varchar(50),  @AETaxLedger varchar(50), @TaxYear varchar(4), @PriorYear varchar(4) "]
["Set @AETaxDatabase = (Select KeyValue From AETAX_Configuration where KeyName = 'Connection_Database' )  "]
["Set @AETaxLedger = (Select KeyValue From AETAX_Configuration where KeyName = 'SystemServices_Client' ) "]
["Set @TaxYear = (Select KeyValue From AETAX_Configuration where KeyName = 'Tax_Year' )   "]
["Set @PriorYear = Convert(int, @TaxYear) - 1 "]
[]
["Set @SQL = 'Select @RETURN_TABLE_SUFFIX = ' + @AETaxDatabase + N'.[dbo].[fn_GetTableSuffixForYear] (' + Convert(varchar(4), @TaxYear) + N') '  "]
["exec sp_executesql @sql, N'@RETURN_TABLE_SUFFIX varchar(255) out', @RETURN_TABLE_SUFFIX out   "]
["Set @SQL = 'Select @RETURN_TABLE_SUFFIX_PRIOR = ' + @AETaxDatabase + N'.[dbo].[fn_GetTableSuffixForYear] (' + Convert(varchar(4), @PriorYear) + N') '  "]
["exec sp_executesql @sql, N'@RETURN_TABLE_SUFFIX_PRIOR varchar(255) out', @RETURN_TABLE_SUFFIX_PRIOR out   "]
[]
["Set @SQL = N'Select CS.ClientCode, CS.ClientName, CS.ClientPartner, IsNull(CM.EmployeeName, '''') as Manager, CS.Email, CS.Tel, T.ReturnCode, T.Surname, T.GivenNames, T.TaxFileNumber, T.FormType, '"]
["Set @SQL = @SQL + N'T.STP_Curr as [Total Salary and Wage Expense ' + @TaxYear + N'], T.Lodged_Curr as Lodged_' + @TaxYear + N', '"]
["Set @SQL = @SQL + N'T.STP_Prior as [Total Salary and Wage Expense ' + @PriorYear + N'], T.Lodged_Prior as Lodged_' + @PriorYear + N' '"]
["Set @SQL = @SQL + N'From vRpt_ContactFull CS '"]
["Set @SQL = @SQL + N'Left Join vw_ClientManager CM on CS.ContactID = CM.ContactID '"]
["Set @SQL = @SQL + N'Inner Join ( '"]
["Set @SQL = @SQL + N'Select T.ClientCode, T.ReturnCode, T.Surname, T.GivenNames, T.TaxFileNumber, T.FormType, MAx(T.Lodged_Curr) as Lodged_Curr, MAx(T.Lodged_Prior) as Lodged_Prior, Sum(T.STP_Curr) as STP_Curr, Sum(T.STP_Prior) as STP_Prior '"]
["Set @SQL = @SQL + N'From ( '"]
["Set @SQL = @SQL + N'Select T.ClientCode, T.ReturnCode, T.Surname, T.GivenNames, T.TaxFileNumber, S.FormType, S.Num58 as STP_Curr, T.IsLodged as Lodged_Curr, null as STP_Prior , null as Lodged_Prior '"]
["Set @SQL = @SQL + N'FRom vw_DS6TA_' + @TaxYear + N'_11 T '"]
["Set @SQL = @SQL + N'Inner Join vw_TAX_DZZTA_' + @TaxYear + N'_52 S on T.ReturnCode = S.ReturnCode '"]
["Set @SQL = @SQL + N'Where S.Num58 IS NOT NULL and S.FormType in (''I'', ''C'', ''P'', ''T'', ''F'') '"]
["Set @SQL = @SQL + N'and T.BackupOfReturn is null '"]
["Set @SQL = @SQL + N'Union '"]
["Set @SQL = @SQL + N'Select T.ClientCode, T.ReturnCode, T.Surname, T.GivenNames, T.TaxFileNumber, S.FormType, Null as Lodged_Curr, Null as STP_Curr, S.Num58 as STP_Prior, T.IsLodged as Lodged_Prior '"]
["Set @SQL = @SQL + N'FRom vw_DS6TA_' + @PriorYear + N'_11 T '"]
["Set @SQL = @SQL + N'Inner Join vw_TAX_DZZTA_' + @PriorYear + N'_52 S on T.ReturnCode = S.ReturnCode '"]
["Set @SQL = @SQL + N'Where S.Num58 IS NOT NULL and S.FormType in (''I'', ''C'', ''P'', ''T'', ''F'') '"]
["Set @SQL = @SQL + N'and T.BackupOfReturn is null '"]
["Set @SQL = @SQL + N') T '"]
["Set @SQL = @SQL + N'Group by T.ClientCode, T.ReturnCode, T.Surname, T.GivenNames, T.TaxFileNumber, T.FormType '"]
["Set @SQL = @SQL + N') T on CS.ClientCode collate database_default = T.ClientCode '"]
["Set @SQL = @SQL + N'Where T.STP_Curr is not null or T.STP_Prior is not null '"]
["Set @SQL = @SQL + N'Order By CS.ClientCode, T.ReturnCode '"]
["exec sp_executesql @sql"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,"  "]

{"sheet":"Sheet2","index":2,"n_rows":1,"n_cols":1,"rows_kept":1,"cut":false,"hidden":true,"merged":[]}
["Declare @SQL nvarchar(4000), @Year int, @RETURN_TABLE_PREFIX CHAR(4), @RETURN_TABLE_SUFFIX CHAR(2), @RETURN_TABLE_SUFFIX_PRIOR CHAR(2) Declare @AETaxDatabase varchar(50),  @AETaxLedger varchar(50), @TaxYear varchar(4), @PriorYear varchar(4)  Set @AETaxDatabase = (Select KeyValue From AETAX_Configuration where KeyName = 'Connection_Database' )   Set @AETaxLedger = (Select KeyValue From AETAX_Configuration where KeyName = 'SystemServices_Client' )  Set @TaxYear = (Select KeyValue From AETAX_Configuration where KeyName = 'Tax_Year' )    Set @PriorYear = Convert(int, @TaxYear) - 1   Set @SQL = 'Select @RETURN_TABLE_SUFFIX = ' + @AETaxDatabase + N'.[dbo].[fn_GetTableSuffixForYear] (' + Convert(varchar(4), @TaxYear) + N') '   exec sp_executesql @sql, N'@RETURN_TABLE_SUFFIX varchar(255) out', @RETURN_TABLE_SUFFIX out    Set @SQL = 'Select @RETURN_TABLE_SUFFIX_PRIOR = ' + @AETaxDatabase + N'.[dbo].[fn_GetTableSuffixForYear] (' + Convert(varchar(4), @PriorYear) + N') '   exec sp_executesql @sql, N'@RETURN_TABLE_SUFFIX_PRIOR varchar(255) out', @RETURN_TABLE_SUFFIX_PRIOR out     Set @SQL = N'Select CS.ClientCode, CS.ClientName, CS.ClientPartner, IsNull(CM.EmployeeName, '''') as Manager, CS.Email, CS.Tel, T.ReturnCode, T.Surname, T.GivenNames, T.TaxFileNumber, T.FormType, ' Set @SQL = @SQL + N'T.STP_Curr as [Total Salary and Wage Expense ' + @TaxYear + N'], T.Lodged_Curr as Lodged_' + @TaxYear + N', ' Set @SQL = @SQL + N'T.STP_Prior as [Total Salary and Wage Expense ' + @PriorYear + N'], T.Lodged_Prior as Lodged_' + @PriorYear + N' ' Set @SQL = @SQL + N'From vRpt_ContactFull CS ' Set @SQL = @SQL + N'Left Join vw_ClientManager CM on CS.ContactID = CM.ContactID ' Set @SQL = @SQL + N'Inner Join ( ' Set @SQL = @SQL + N'Select T.ClientCode, T.ReturnCode, T.Surname, T.GivenNames, T.TaxFileNumber, T.FormType, MAx(T.Lodged_Curr) as Lodged_Curr, MAx(T.Lodged_Prior) as Lodged_Prior, Sum(T.STP_Curr) as STP_Curr, Sum(T.STP_Prior) as STP_Prior ' Set @SQL = @SQL + N'From ( ' Set @SQL = @SQL + N'Select T.ClientCode, T.ReturnCode, T.Surname, T.GivenNames, T.TaxFileNumber, S.FormType, S.Num58 as STP_Curr, T.IsLodged as Lodged_Curr, null as STP_Prior , null as Lodged_Prior ' Set @SQL = @SQL + N'FRom vw_DS6TA_' + @TaxYear + N'_11 T ' Set @SQL = @SQL + N'Inner Join vw_TAX_DZZTA_' + @TaxYear + N'_52 S on T.ReturnCode = S.ReturnCode ' Set @SQL = @SQL + N'Where S.Num58 IS NOT NULL and S.FormType in (''I'', ''C'', ''P'', ''T'', ''F'') ' Set @SQL = @SQL + N'and T.BackupOfReturn is null ' Set @SQL = @SQL + N'Union ' Set @SQL = @SQL + N'Select T.ClientCode, T.ReturnCode, T.Surname, T.GivenNames, T.TaxFileNumber, S.FormType, Null as Lodged_Curr, Null as STP_Curr, S.Num58 as STP_Prior, T.IsLodged as Lodged_Prior ' Set @SQL = @SQL + N'FRom vw_DS6TA_' + @PriorYear + N'_11 T ' Set @SQL = @SQL + N'Inner Join vw_TAX_DZZTA_' + @PriorYear + N'_52 S on T.ReturnCode = S.ReturnCode ' Set @SQL = @SQL + N'Where S.Num58 IS NOT NULL and S.FormType in (''I'', ''C'', ''P'', ''T'', ''F'') ' Set @SQL = @SQL + N'and T.BackupOfReturn is null ' Set @SQL = @SQL + N') T ' Set @SQL = @SQL + N'Group by T.ClientCode, T.ReturnCode, T.Surname, T.GivenNames, T.TaxFileNumber, T.FormType ' Set @SQL = @SQL + N') T on CS.ClientCode collate database_default = T.ClientCode ' Set @SQL = @SQL + N'Where T.STP_Curr is not null or T.STP_Prior is not null ' Set @SQL = @SQL + N'Order By CS.ClientCode, T.ReturnCode ' exec sp_executesql @sql "]

{"sheet":"Modifications","index":3,"n_rows":13,"n_cols":4,"rows_kept":13,"cut":false,"hidden":true,"merged":[]}
["Modifications"]
[]
["Date","Author","Modification","Version"]
["2015-05-14","S Gaff","Add LastUsed to Overhead assignments In Detail",1.1000000000000001]
[null,null,"Add M-Powered Payee Number to Details > Company",1.1000000000000001]
[null,null,"Add ContactTypes to Summary and Detail",1.1000000000000001]
[null,null,"Add Team Security usage to Summary",1.1000000000000001]
[null,null,"Add Assignment Type to Assignment Templates in Detail ",1.1000000000000001]
[null,null,"Change Clients usage in Summary to split between Open and Closed Clients",1.1000000000000001]
[null,null,"Add Closed/Inactive status to Duplicates",1.1000000000000001]
[]
["2015-06-15","S.Gaff","Add Closed clients to Partner usage in Detail",1.2]
[null,null,"Add Partner and Closed status to LongCodes",1.2]

{"sheet":"Detail","index":4,"n_rows":3,"n_cols":15,"rows_kept":3,"cut":false,"hidden":false,"merged":[]}
[null,null,null,null,"STP - Salary and Wages Expense  Report",null,null,null,null,null,null,"Current Year",null,"Prior Year"]
[null,null,null,null,null,null,null,null,null,null,null,"2018",null,{"$f":"L2-1","v":2017}]
["ClientCode","ClientName","ClientPartner","ClientManager","Email","Tel","ReturnCode","Surname","GivenNames","TaxFileNumber","FormType","Total Salary and Wage Expense","Lodged","Total Salary and Wage Expense","Lodged"]

{"sheet":"PickListDetails","index":5,"n_rows":27,"n_cols":1,"rows_kept":27,"cut":false,"hidden":true,"merged":[]}
["ClientPartner"]
["-UNSPEC -"]
["Banerjee Shumit"]
["Bookless Matthew"]
["Carrafa Michael"]
["Cauchi Richard"]
["Cronan Jason"]
["David Williams ZZ"]
["Goodyer Brett"]
["Gountzos Peter"]
["Joe Atkinson ZZ"]
["Lofthouse David"]
["Meagher Anne"]
["Moretti Richard"]
["Mottershead Ross"]
["Norbert Calabro ZZ"]
["Paul Sweeney ZZ"]
["Porter Jason"]
["Purchas Ian"]
["Quinn Daniel"]
["Richard Croaker ZZ"]
["Rose Terry"]
["Stephen Hathway ZZ"]
["Stephen McMahon ZZ"]
["Stimpson David"]
["van der Velde Terry"]
["Vardy Darren"]

{"sheet":"Duplicates","index":6,"n_rows":3,"n_cols":5,"rows_kept":3,"cut":false,"hidden":true,"merged":[]}
["Worksheet contains:","Codes that exist in both databases in the ClientSupplier and Employee tables."]
["Database 1",null,null,null,"Database 2"]
[{"$f":"Summary!A7","v":0},null,null,null,{"$f":"Summary!E7","v":0}]