Insert Into dbo.Scripts Values('General Hotfix v5.2.38 Cumulative',GetDate(), '|~|') Update ApplicationSetting Set Setting = '5.2.37' Where SettingID = 1 if exists (select * from sysobjects where id = object_id(N'[dbo].repsp_eLodge5602Assets') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure dbo.repsp_eLodge5602Assets GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE PROCEDURE dbo.repsp_eLodge5602Assets @AdminID Int ,@AdminTypeID Int = Null ,@FromDate DateTime ,@ToDate DateTime ,@ShowDetail Bit = 0 AS /* Author: JO Date: 24/07/17 Purpose: EAR gross asset realisations Updtes Si - 24 April 2019 - Exclude disclaimed leased assets -- exec dbo.repsp_eLodge5602Assets @AdminID=1429, @AdminTypeID=10,@FromDate='20181001',@ToDate='20181010' */ SET NOCOUNT ON CREATE TABLE dbo.#Position ( PositionGroup SmallInt ,SummaryGroup Int ,CategoryType Int ,Category VarChar(100) ,Description Varchar(100) ,AccountID Int ,ValueToDate Money ,Balance Money ,Total Money ,RATATotal Money ,IsTotal Bit ) EXECUTE dbo.repsp_CurrentPosition @AdminID=@AdminID,@AdminTypeID=@AdminTypeID,@AsAtDate=@ToDate,@AsAccrual=0,@AsTableInsert=1 DECLARE @Results Table (AccountID Int, Description Varchar(100), GrossAssets Money, FutureAssetsLo Money, FutureAssetsHi Money) DECLARE @Assets Table (AccountID Int, ERVHighAdj Money) DECLARE @ExRealisations Table (AccountID Int, TotalRealised Money) INSERT INTO @Results SELECT p.AccountID ,acc.Description ,ValueToDate ,Balance ,Balance FROM dbo.#Position p JOIN dbo.Account acc ON acc.AccountID = p.AccountID INNER JOIN dbo.AccountGroup ag ON ag.AccountGroupID = acc.AccountGroupID --WHERE Total <> 0 WHERE IsTotal = 0 AND IsNull(acc.Flags,0) & 8 = 0 AND NOT acc.AccountUID = '{4913F46E-7BAB-4B85-9CF8-678355F51788}' AND ag.AccountClassID In(3, 5, 7) AND Not ag.AccountGroupID = 7010 --Share capital --Leased assets INSERT INTO @Results SELECT p.AccountID ,acc.Description ,Sum(ValueToDate) ,Sum(Balance) ,Sum(Balance) FROM dbo.#Position p INNER JOIN dbo.Account acc ON acc.AccountID = p.AccountID --WHERE Total <> 0 WHERE IsTotal = 0 AND IsNull(acc.Flags,0) & 8 = 0 AND acc.AccountUID = '{4913F46E-7BAB-4B85-9CF8-678355F51788}' GROUP BY p.AccountID ,acc.Description INSERT INTO @Assets SELECT a.AccountID ,Sum(ERVHigh - ERVLow) FROM dbo.Asset a INNER JOIN @Results r ON r.AccountID = a.AccountID WHERE a.IsFullyRealised = 0 AND a.IsDisclaimed = 0 GROUP BY a.AccountID --Select * --from @Results --Where AccountId = 1129213 --Select * --from @assets --Where AccountId = 1129213 UPDATE @Results SET FutureAssetsHi = FutureAssetsHi + ERVHighAdj FROM @Results r INNER JOIN @Assets a ON a.AccountID = r.AccountID --Exec dbo.repsp_eLodge5602Assets @AdminID=7468,@AdminTypeID=10,@FromDate='20161220',@ToDate='20181219',@ShowDetail=1 IF @ShowDetail = 1 SELECT * ,Total = GrossAssets + FutureAssetsLo FROM @Results WHERE GrossAssets <> 0 OR FutureAssetsHi <> 0 OR FutureAssetsLo <> 0 ELSE SELECT RealisedToDate = IsNull(Sum(GrossAssets),0) ,FutureAssetsHi = IsNull(Sum(FutureAssetsHi),0) ,FutureAssetsLo = IsNull(Sum(FutureAssetsLo),0) ,TotalHi = IsNull(Sum(GrossAssets + FutureAssetsHi),0) ,TotalLo = IsNull(Sum(GrossAssets + FutureAssetsLo),0) FROM @Results WHERE GrossAssets <> 0 OR FutureAssetsHi <> 0 OR FutureAssetsLo <> 0 SET NOCOUNT OFF GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO GRANT EXECUTE ON dbo.repsp_eLodge5602Assets TO [insol2_users] GO if exists (select * from sysobjects where id = object_id(N'[dbo].[repsp_AdminDetailSheetJobInfo]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[repsp_AdminDetailSheetJobInfo] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[repsp_AdminDetailSheetJobInfo] /* 26 April 2019 - Si - Removed cost resolution records from results exec repsp_AdminDetailSheetJobInfo 1643 */ --parameters @AdminID Int ,@AdminTypeId Int = Null AS SET NOCOUNT ON SELECT CustomPropertyType = cpt.Description ,PropertyValue = CASE WHEN cpt.Format = 3 THEN CASE WHEN cp.PropertyValue = 'true' THEN 'Yes' WHEN cp.PropertyValue = 'false' THEN 'No' WHEN cp.PropertyValue = '1' THEN 'Yes' WHEN cp.PropertyValue = '2' THEN 'No' WHEN cp.PropertyValue = '0' THEN 'No' ELSE cp.PropertyValue END ELSE cp.PropertyValue END FROM dbo.CustomProperty cp INNER JOIN dbo.CustomPropertyType cpt ON cpt.CustomPropertyTypeID = cp.CustomPropertyTypeId INNER JOIN dbo.Admin_CustomProperty acp ON acp.CustomPropertyID = cp.CustomPropertyID LEFT OUTER JOIN dbo.Lookup lu ON lu.LookupID = cp.LookupID WHERE acp.AdminID = @AdminID AND ((@AdminTypeID IS NULL) OR (cp.AdminTypeID = @AdminTypeID)) AND NOT cpt.CustomPropertyTypeID in (998, 999) ORDER BY cpt.Description SET NOCOUNT OFF GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO GRANT EXECUTE ON [dbo].[repsp_AdminDetailSheetJobInfo] TO [insol2_users] GO --Insert Into dbo.Scripts --Values('General Hotfix 20190502 ROCAP update',GetDate(), '|~|') --Clean up and previously imported system ROCAP reports and mergetemplate DELETE FROM Report WHERE ReportID BETWEEN 325000 AND 352030 AND Description LIKE '%ROCAP%' DELETE FROM MergeTemplateGroup WHERE MergeTemplateID BETWEEN 325000 AND 352030 DELETE FROM MergeTemplate WHERE MergeTemplateID BETWEEN 325000 AND 352030 AND Description LIKE '%ROCAP%' --Import Form 507 - ROCAP Part A DECLARE @MTID INT = 325000 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTDesc = 'Form 507 - ROCAP Part A' ,@Extension = 'pdf' ,@SavePath = 'Directors' ,@MTCat = 50000 ,@SystemFlags = 53 ,@MTLocation = 1 ,@DeleteExisting = 1 ,@MTPrecDesc = 'pdfsp_Form_507 ' INSERT INTO MergeTemplate_ReplaceField (MergeTemplateId, ReplaceFieldId) VALUES (@MTID ,(select ReplaceFieldId from ReplaceField where CustomPropertyTypeID = 17)) Go --Import Form 507 - ROCAP Part B DECLARE @MTID INT = 325001 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTDesc = 'Form 507 - ROCAP Part B' ,@Extension = 'pdf' ,@SavePath = 'Directors' ,@MTCat = 50000 ,@SystemFlags = 53 ,@MTLocation = 1 ,@DeleteExisting = 1 ,@MTPrecDesc = Null Go --Import Form 507 - ROCAP Statement DECLARE @MTID INT = 325002 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTDesc = 'Form 507A - ROCAP Statement Verifying Report' ,@Extension = 'pdf' ,@SavePath = 'Directors' ,@MTCat = 50000 ,@SystemFlags = 53 ,@MTLocation = 1 ,@DeleteExisting = 1 ,@MTReportCategoryID = 52000 ,@ReportID = 325002 ,@MTPrecDesc = 'pdfsp_Form_507 ' INSERT INTO MergeTemplate_ReplaceField (MergeTemplateId, ReplaceFieldId) VALUES (@MTID ,(select ReplaceFieldId from ReplaceField where CustomPropertyTypeID = 17)) Go --Import Form 507 - ROCAP Q A4 Header DECLARE @MTID INT = 325003 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTDesc = 'Form 507 - ROCAP Q A4 Employees Header' ,@Extension = 'pdf' ,@SavePath = 'Directors' ,@MTCat = 50000 ,@SystemFlags = 53 ,@MTLocation = 1 ,@DeleteExisting = 1 ,@MTPrecDesc = 'pdfsp_Form_507 ' INSERT INTO MergeTemplate_ReplaceField (MergeTemplateId, ReplaceFieldId) VALUES (@MTID ,(select ReplaceFieldId from ReplaceField where CustomPropertyTypeID = 17)) Go --Import Form 507 - ROCAP Q A4 Schedule DECLARE @MTID INT = 325004 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTLocation = 1 ,@MTCat = 50000 ,@MTDesc = 'Form 507 - ROCAP Q A4 Employees Schedule' ,@SavePath = 'Directors' ,@DeleteExisting = 1 ,@MTPrecDesc = 'repsp_ROCAP_Annexure_A4 ' ,@MTFlags = Null ,@SystemFlags = 69 ,@Extension = 'xlsx' Go --Import Form 507 - ROCAP Q A4 Header DECLARE @MTID INT = 325005 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTDesc = 'Form 507 - ROCAP Q A5 Creditors Header' ,@Extension = 'pdf' ,@SavePath = 'Directors' ,@MTCat = 50000 ,@SystemFlags = 53 ,@MTLocation = 1 ,@DeleteExisting = 1 ,@MTPrecDesc = 'pdfsp_Form_507 ' INSERT INTO MergeTemplate_ReplaceField (MergeTemplateId, ReplaceFieldId) VALUES (@MTID ,(select ReplaceFieldId from ReplaceField where CustomPropertyTypeID = 17)) Go --Import Form 507 - ROCAP Q A5 Schedule DECLARE @MTID INT = 325006 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTLocation = 1 ,@MTCat = 50000 ,@MTDesc = 'Form 507 - ROCAP Q A5 Creditors Schedule' ,@SavePath = 'Directors' ,@DeleteExisting = 1 ,@MTPrecDesc = 'repsp_ROCAP_Annexure_A5 ' ,@MTFlags = Null ,@SystemFlags = 69 ,@Extension = 'xlsx' Go --Import Form 507 - ROCAP Q A6 DECLARE @MTID INT = 325007 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTLocation = 1 ,@MTCat = 50000 ,@MTDesc = 'Form 507 - ROCAP Q A6 Money Owed to Company Header' ,@SavePath = 'Directors' ,@DeleteExisting = 1 ,@MTPrecDesc = 'pdfsp_Form_507 ' ,@MTFlags = Null ,@SystemFlags = 53 ,@Extension = 'pdf' Go --Import Form 507 - ROCAP Q A6 Schedule DECLARE @MTID INT = 325008 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTLocation = 1 ,@MTCat = 50000 ,@MTDesc = 'Form 507 - ROCAP Q A6 Money Owed to Company Schedule' ,@SavePath = 'Directors' ,@DeleteExisting = 1 ,@MTPrecDesc = 'repsp_ROCAP_Annexure_A6 ' ,@MTFlags = Null ,@SystemFlags = 69 ,@Extension = 'xlsx' Go DECLARE @MTID INT = 325009 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTLocation = 1 ,@MTCat = 50000 ,@MTDesc = 'Form 507 - ROCAP Q A7 Assets Owned by Company Header' ,@SavePath = 'Directors' ,@DeleteExisting = 1 ,@MTPrecDesc = 'pdfsp_Form_507 ' ,@MTFlags = Null ,@SystemFlags = 53 ,@Extension = 'pdf' Go --Import Form 507 - ROCAP Q A7 Schedule DECLARE @MTID INT = 325010 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTLocation = 1 ,@MTCat = 50000 ,@MTDesc = 'Form 507 - ROCAP Q A7 Assets Owned by Company Schedule' ,@SavePath = 'Directors' ,@DeleteExisting = 1 ,@MTPrecDesc = 'repsp_ROCAP_Annexure_A7 ' ,@MTFlags = Null ,@SystemFlags = 69 ,@Extension = 'xlsx' Go --Import Form 507 - ROCAP Q B02 DECLARE @MTID INT = 325011 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTLocation = 1 ,@MTCat = 50000 ,@MTDesc = 'Form 507 - ROCAP Q B02 Payments to you from the Company Schedule' ,@SavePath = 'Directors' ,@DeleteExisting = 1 --,@MTPrecDesc = 'repsp_ROCAP_Annexure_B2' ,@MTFlags = Null ,@SystemFlags = 53 ,@Extension = 'pdf' Go --Import Form 507 DECLARE @MTID INT = 325012 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTLocation = 1 ,@MTCat = 50000 ,@MTDesc = 'Form 507 - ROCAP Q B03 Payments to someone else from the Company Schedule' ,@SavePath = 'Directors' ,@DeleteExisting = 1 --,@MTPrecDesc = 'repsp_ROCAP_Annexure_B3' ,@MTFlags = Null ,@SystemFlags = 53 ,@Extension = 'pdf' Go --Import Form 507 - DECLARE @MTID INT = 325013 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTLocation = 1 ,@MTCat = 50000 ,@MTDesc = 'Form 507 - ROCAP Q B21 Repayments to creditors Schedule' ,@SavePath = 'Directors' ,@DeleteExisting = 1 --,@MTPrecDesc = 'repsp_ROCAP_Annexure_B21' ,@MTFlags = Null ,@SystemFlags = 53 ,@Extension = 'pdf' Go --Import Form 507 - DECLARE @MTID INT = 325014 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTLocation = 1 ,@MTCat = 50000 ,@MTDesc = 'Form 507 - ROCAP Q B25 External Advisors Schedule' ,@SavePath = 'Directors' ,@DeleteExisting = 1 --,@MTPrecDesc = 'repsp_ROCAP_Annexure_B25' ,@MTFlags = Null ,@SystemFlags = 53 ,@Extension = 'pdf' Go --Import Form 507 - Instructions DECLARE @MTID INT = 325015 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTDesc = 'Form 507 - ROCAP Instructions' ,@Extension = 'pdf' ,@SavePath = 'Directors' ,@MTCat = 50000 ,@SystemFlags = 53 ,@MTLocation = 1 ,@DeleteExisting = 1 ,@MTPrecDesc = Null Go --Import the ROCAP LETTERPACK DECLARE @MTID INT = 325020 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTLocation = 1 ,@MTCat = 500 ,@MTDesc = 'Form 507 - Report on Company Activities and Property (ROCAP)' ,@DeleteExisting = 1 ,@IsParent = 1 ,@Extension = 'rvlp' ,@SavePath = 'Directors' ,@SystemFlags = 53 ,@MTReportCategoryID = 52000 ,@ReportID = 325020 Go DELETE FROM MergeTemplateGroup WHERE Parent_MergeTemplateID = 325020 INSERT into MergeTemplateGroup (Parent_MergeTemplateID, MergeTemplateID, IsMain, DisplayOrder) values ( 325020, 325000,0,null) ,(325020, 325001,0, null) ,(325020, 325002,0, null) ,(325020, 325003,0, null) ,(325020, 325004,0, null) ,(325020, 325005,0, null) ,(325020, 325006,0, null) ,(325020, 325007,0, null) ,(325020, 325008,0, null) ,(325020, 325009,0, null) ,(325020, 325010,0, null) ,(325020, 325011,0, null) ,(325020, 325012,0, null) ,(325020, 325013,0, null) ,(325020, 325014,0, null) ,(325020, 325015,0, null) --Import Form 507 - ROCAP Part A including fillable fields DECLARE @MTID INT = 325021 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTDesc = 'Form 507 - ROCAP Part A with Fillable Fields' ,@Extension = 'pdf' ,@SavePath = 'Directors' ,@MTCat = 50000 ,@SystemFlags = 53 ,@MTLocation = 1 ,@DeleteExisting = 1 ,@MTPrecDesc = 'pdfsp_Form_507_Blank ' INSERT INTO MergeTemplate_ReplaceField (MergeTemplateId, ReplaceFieldId) VALUES (@MTID ,(select ReplaceFieldId from ReplaceField where CustomPropertyTypeID = 17)) Go --Import Form 507 - ROCAP Part A with Fillable Schedules DECLARE @MTID INT = 325022 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTDesc = 'Form 507 - ROCAP Part A with Fillable Schedules' ,@Extension = 'pdf' ,@SavePath = 'Directors' ,@MTCat = 50000 ,@SystemFlags = 53 ,@MTLocation = 1 ,@DeleteExisting = 1 Go --Import Form 507 - ROCAP Part B with Fillable Fields DECLARE @MTID INT = 325023 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTDesc = 'Form 507 - ROCAP Part B with Fillable Fields' ,@Extension = 'pdf' ,@SavePath = 'Directors' ,@MTCat = 50000 ,@SystemFlags = 53 ,@MTLocation = 1 ,@DeleteExisting = 1 Go --Import Form 507 - ROCAP Part B with Fillable Schedules DECLARE @MTID INT = 325024 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTDesc = 'Form 507 - ROCAP Part B with Fillable Schedules' ,@Extension = 'pdf' ,@SavePath = 'Directors' ,@MTCat = 50000 ,@SystemFlags = 53 ,@MTLocation = 1 ,@DeleteExisting = 1 Go --Import the letterpack Form 507 - ROCAP - With fillable form fields DECLARE @MTID INT = 325025 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTDesc = 'Form 507 - ROCAP - With fillable form fields' ,@Extension = 'rvlp' ,@SavePath = 'Directors' ,@MTCat = 500 ,@SystemFlags = 53 ,@MTLocation = 1 ,@DeleteExisting = 1 ,@IsParent = 1 ,@MTReportCategoryID = 52000 ,@ReportID = @MTID DELETE FROM MergeTemplateGroup WHERE Parent_MergeTemplateID = @MTID INSERT into MergeTemplateGroup (Parent_MergeTemplateID, MergeTemplateID, IsMain, DisplayOrder) VALUES (@MTID, 325021,0,null) ,(@MTID, 325022,0, null) ,(@MTID, 325023,0, null) ,(@MTID, 325024,0, null) Go --Import Form 507 - ROCAP Part A - Form to be completed by hand DECLARE @MTID INT = 325026 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTDesc = 'Form 507 - ROCAP Part A - Form to be completed by hand' ,@Extension = 'pdf' ,@SavePath = 'Directors' ,@MTCat = 50000 ,@SystemFlags = 53 ,@MTLocation = 1 ,@DeleteExisting = 1 Go --Import Form 507 - ROCAP Part A - Schedules to be completed by hand DECLARE @MTID INT = 325027 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTDesc = 'Form 507 - ROCAP Part A - Schedules to be completed by hand' ,@Extension = 'pdf' ,@SavePath = 'Directors' ,@MTCat = 50000 ,@SystemFlags = 53 ,@MTLocation = 1 ,@DeleteExisting = 1 ,@MTPrecDesc = Null Go --Import Form 507 - ROCAP Part B - Form to be completed by hand DECLARE @MTID INT = 325028 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTDesc = 'Form 507 - ROCAP Part B - Form to be completed by hand' ,@Extension = 'pdf' ,@SavePath = 'Directors' ,@MTCat = 50000 ,@SystemFlags = 53 ,@MTLocation = 1 ,@DeleteExisting = 1 Go --Import Form 507 - ROCAP Part B - Form to be completed by hand DECLARE @MTID INT = 325029 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTDesc = 'Form 507 - ROCAP Part B - Schedules to be completed by hand' ,@Extension = 'pdf' ,@SavePath = 'Directors' ,@MTCat = 50000 ,@SystemFlags = 53 ,@MTLocation = 1 ,@DeleteExisting = 1 Go --Import the letterpack Form 507 - ROCAP - With fillable form fields DECLARE @MTID INT = 325030 EXEC gensp_MergeTemplateImport @MTID = @MTID ,@MTDesc = 'Form 507 - ROCAP - To be completed by hand' ,@Extension = 'rvlp' ,@SavePath = 'Directors' ,@MTCat = 500 ,@SystemFlags = 53 ,@MTLocation = 1 ,@DeleteExisting = 1 ,@IsParent = 1 ,@MTReportCategoryID = 52000 ,@ReportID = @MTID DELETE FROM MergeTemplateGroup WHERE Parent_MergeTemplateID = @MTID INSERT into MergeTemplateGroup (Parent_MergeTemplateID, MergeTemplateID, IsMain, DisplayOrder) VALUES (@MTID, 325026,0,null) ,(@MTID, 325027,0, null) ,(@MTID, 325028,0, null) ,(@MTID, 325029,0, null) Go if exists (select * from sysobjects where id = object_id(N'[dbo].[repsp_ROCAP_Annexure_A7]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].repsp_ROCAP_Annexure_A7 GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE PROCEDURE dbo.repsp_ROCAP_Annexure_A7 @AdminID Int ,@AnnexuresBlank NVARCHAR(3) = 'NO' AS /* Author: Simon Allen Date: Purpose: Asset schedule to attach to the ROCAP Notes: Revision History: DEBUG EXEC repsp_ROCAP_Annexure_A7 9682 Notes: */ SET NOCOUNT ON DECLARE @Loc INT DECLARE @PracticeName VARCHAR(100) DECLARE @practiceID INT DECLARE @A7Schedule TABLE ( AssetID int ,AssetDescription varchar(200) ,AssetDetails varchar(200) ,SecurityHeld varchar(500) ,EstAssetValue money ) SELECT @Loc = dbo.UFnSystemLocation() INSERT INTO @A7Schedule (AssetID, AssetDescription, AssetDetails, SecurityHeld, EstAssetValue) SELECT AssetID ,AssetDescription = LEFT(Description, 200) ,AssetDetailsnull = NULL ,SecurityHeldnull = NULL ,RATAERV FROM Asset ass where ass.AdminID = @AdminID and IsRATAAsset = 1 and AssetClassID != 4 /* EXEC repsp_ROCAP_Annexure_A7 9682 */ IF @AnnexuresBlank = 'No' BEGIN IF NOT EXISTS (SELECT * FROM @A7Schedule) SELECT 'ASSET DESCRIPTION' = '' ,'LOCATION/ADDRESS WHERE ASSET IS LOCATED. WITH BANK ACCOUNTS GIVE BANK A/C DETAILS INCLUDING A/C NUMBERS' = '' ,'SECURITY HELD BY (IF APPLICABLE)' = '' ,'ESTIMATED ASSET VALUE' = '' ELSE SELECT 'ASSET DESCRIPTION' = LEFT(AssetDescription ,200) ,'LOCATION/ADDRESS WHERE ASSET IS LOCATED. WITH BANK ACCOUNTS GIVE BANK A/C DETAILS INCLUDING A/C NUMBERS' = LEFT(AssetDetails ,200) ,'SECURITY HELD BY (IF APPLICABLE)' = LEFT(SecurityHeld ,500) ,'ESTIMATED ASSET VALUE' = EstAssetValue FROM @A7Schedule END ELSE SELECT 'ASSET DESCRIPTION' = '' ,'LOCATION/ADDRESS WHERE ASSET IS LOCATED. WITH BANK ACCOUNTS GIVE BANK A/C DETAILS INCLUDING A/C NUMBERS' = '' ,'SECURITY HELD BY (IF APPLICABLE)' = '' ,'ESTIMATED ASSET VALUE' = '' SET NOCOUNT OFF GO GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO GRANT EXECUTE ON [dbo].repsp_ROCAP_Annexure_A7 TO [insol2_users] GO if exists (select * from sysobjects where id = object_id(N'[dbo].[repsp_ROCAP_Annexure_A4]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].repsp_ROCAP_Annexure_A4 GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE PROCEDURE dbo.repsp_ROCAP_Annexure_A4 @AdminID Int ,@AnnexuresBlank NVARCHAR(3) = 'NO' AS /* Author: Simon Allen Date: Purpose: Employee schedule to attach to the ROCAP Notes: Revision History: 30/11/2018 - removed the employees unsecured claim from the OTHER column and added into the A5 schedule. DEBUG EXEC repsp_ROCAP_Annexure_A4 2479 Notes: */ SET NOCOUNT ON DECLARE @Loc INT DECLARE @PracticeName VARCHAR(100) DECLARE @practiceID INT DECLARE @A4Schedule TABLE ( EmployeeID int ,EmployeeName varchar(200) ,StartDate datetime ,WagesOwed money ,HolidayPayOwed money ,LongServiceLeaveOwed money ,SuperannuationOwed money ,RedundancyOwed money ,Other money ,TotalOwed money ,TickIfRelatedParty varchar(1) ) DECLARE @OtherPayments TABLE (EmployeeID int, Other money) SELECT @Loc = dbo.UFnSystemLocation() BEGIN INSERT INTO @A4Schedule (EmployeeID) SELECT DISTINCT employeeid from creditor where IsRATACreditor = 1 and employeeid is not null and adminid = @AdminID UPDATE @A4Schedule SET EmployeeName = ISNULL(e.Title,'') + ' ' + ISNULL(e.FirstName,'') + ' ' + ISNULL(e.LastName,'') ,StartDate = e.StartDate FROM Employee e INNER JOIN @A4Schedule a4 on e.EmployeeID = a4.EmployeeID UPDATE @A4Schedule SET TickIfRelatedParty = 'X' FROM creditor c INNER JOIN @A4Schedule a4 ON c.EmployeeID = a4.EmployeeID WHERE IsNull(Flags,0) & 1 = 1 UPDATE @A4Schedule SET WagesOwed = round(cc.RATAAmount,2) FROM CreditorClaim cc INNER JOIN Creditor c on c.CreditorID = cc.CreditorID and CreditorClaimTypeID = 250 INNER JOIN @A4Schedule a4 ON c.EmployeeID = a4.EmployeeID UPDATE @A4Schedule SET HolidayPayOwed = round(cc.RATAAmount - cc.Spare1,2) FROM CreditorClaim cc INNER JOIN Creditor c ON c.CreditorID = cc.CreditorID and CreditorClaimTypeID = 270 INNER JOIN @A4Schedule a4 ON c.EmployeeID = a4.EmployeeID UPDATE @A4Schedule SET LongServiceLeaveOwed = round(cc.RATAAmount - a4.HolidayPayOwed,2) FROM CreditorClaim cc INNER JOIN Creditor c ON c.CreditorID = cc.CreditorID and CreditorClaimTypeID = 270 INNER JOIN @A4Schedule a4 ON c.EmployeeID = a4.EmployeeID UPDATE @A4Schedule SET SuperannuationOwed = round(CC.RATAAmount,2) FROM CreditorClaim cc INNER JOIN Creditor c ON c.CreditorID = cc.CreditorID and CreditorClaimTypeID = 255 INNER JOIN @A4Schedule a4 ON c.EmployeeID = a4.EmployeeID UPDATE @A4Schedule SET RedundancyOwed = round(CC.RATAAmount,2) FROM CreditorClaim cc INNER JOIN Creditor c ON c.CreditorID = cc.CreditorID and CreditorClaimTypeID = 280 INNER JOIN @A4Schedule a4 ON c.EmployeeID = a4.EmployeeID INSERT INTO @OtherPayments (EmployeeID, Other) select EmployeeID = A4.EmployeeID, other = sum(round(cc.RATAAmount,2)) FROM CreditorClaim cc INNER JOIN Creditor c ON c.CreditorID = cc.CreditorID and CreditorClaimTypeID NOT IN (250,255,270,280,400) INNER JOIN @A4Schedule a4 ON c.EmployeeID = a4.EmployeeID GROUP BY A4.EmployeeID UPDATE @A4Schedule SET Other = op.Other FROM @OtherPayments op INNER JOIN @A4Schedule a4 ON op.EmployeeID = a4.EmployeeID UPDATE @A4Schedule SET TotalOwed = ISNULL(WagesOwed,0) + ISNULL(HolidayPayOwed,0) + ISNULL(LongServiceLeaveOwed,0) + ISNULL(SuperannuationOwed,0) + ISNULL(RedundancyOwed,0) + ISNULL(Other,0) END /* EXEC repsp_ROCAP_Annexure_A4 9682 */ IF @AnnexuresBlank = 'No' BEGIN IF NOT EXISTS (SELECT * FROM @A4Schedule) SELECT 'EMPLOYEE''S NAME' = '' ,'START DATE' = '' ,'WAGES OWED' = '' ,'HOLIDAY PAY OWED' = '' ,'LONG SERVICE LEAVE OWED' = '' ,'SUPERANNUATION OWED' = '' ,'REDUNDANCY OWED' = '' ,'OTHER' = '' ,'TOTAL OWED' = '' ,'TICK IF RELATED PARTY' = '' ELSE SELECT EmployeeName AS 'EMPLOYEE''S NAME' ,StartDate AS 'START DATE' ,WagesOwed AS 'WAGES OWED' ,HolidayPayOwed AS 'HOLIDAY PAY OWED' ,LongServiceLeaveOwed AS 'LONG SERVICE LEAVE OWED' ,SuperannuationOwed AS 'SUPERANNUATION OWED' ,RedundancyOwed AS 'REDUNDANCY OWED' ,Other AS 'OTHER' ,TotalOwed AS 'TOTAL OWED' ,TickIfRelatedParty AS 'TICK IF RELATED PARTY' FROM @A4Schedule END ELSE SELECT 'EMPLOYEE''S NAME' = '' ,'START DATE' = '' ,'WAGES OWED' = '' ,'HOLIDAY PAY OWED' = '' ,'LONG SERVICE LEAVE OWED' = '' ,'SUPERANNUATION OWED' = '' ,'REDUNDANCY OWED' = '' ,'OTHER' = '' ,'TOTAL OWED' = '' ,'TICK IF RELATED PARTY' = '' SET NOCOUNT OFF GO GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO GRANT EXECUTE ON [dbo].repsp_ROCAP_Annexure_A4 TO [insol2_users] GO if exists (select * from sysobjects where id = object_id(N'[dbo].[repsp_ROCAP_Annexure_A5]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].repsp_ROCAP_Annexure_A5 GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE PROCEDURE dbo.repsp_ROCAP_Annexure_A5 @AdminID Int ,@AnnexuresBlank NVARCHAR(3) = 'NO' AS /* Author: Simon Allen Date: Purpose: Creditor schedule to attach to the ROCAP Notes: Revision History: 30/11/2018 - Updated to include the Unsecured Employee claims these were previously populating the OTHER column in the employee's A4 schedule 30/1/2019 - The assetdetails was previously populating with the Asset Description but this was unsuitable for book debts whgere there could be hundreds/thousands of records Update so that were book debts are secured the Asset Detail column will populate as 'Pre-Appointment Accounts Receivables' instead of listing the individual assets. DEBUG EXEC repsp_ROCAP_Annexure_A5 2479 Notes: */ SET NOCOUNT ON DECLARE @Loc INT DECLARE @PracticeName VARCHAR(100) DECLARE @practiceID INT DECLARE @A5Schedule TABLE ( CreditorID int ,CreditorName varchar(200) ,CreditorAddress varchar(800) ,EmailAddress varchar(200) ,TickIfSecured varchar(1) ,PPRS varchar(100) ,AssetDetails varchar(max) ,TickIfRelatedParty varchar(1) ,AmountOwing money ) SELECT @Loc = dbo.UFnSystemLocation() INSERT INTO @A5Schedule (CreditorID, AmountOwing) SELECT c.creditorid, round(sum(cc.RATAAmount),2) FROM creditor c INNER JOIN creditorclaim cc ON c.creditorid = cc.creditorid WHERE IsRATACreditor = 1 AND CounterpartyID is not null AND adminid = @AdminID GROUP BY c.CreditorID UPDATE @A5Schedule SET CreditorName = ISNULL(cp.Name,'') FROM creditor c INNER JOIN Counterparty cp ON c.CounterpartyID = cp.CounterpartyID INNER JOIN @A5Schedule a5 ON C.CreditorID = A5.CreditorID UPDATE @A5Schedule SET EmailAddress = isnull(n.Number,'') FROM Creditor c INNER JOIN Counterparty_Number cn ON c.CounterpartyID = cn.CounterpartyID INNER JOIN @A5Schedule a5 ON C.CreditorID = A5.CreditorID INNER JOIN Number n ON cn.NumberID = n.NumberID and n.NumberTypeID = 4 INSERT INTO @A5Schedule (CreditorID, AmountOwing) SELECT c.creditorid, round(sum(cc.RATAAmount),2) FROM creditor c INNER JOIN creditorclaim cc ON c.creditorid = cc.creditorid and cc.CreditorClaimTypeID = 400 WHERE IsRATACreditor = 1 AND EmployeeID is not null AND adminid = @AdminID GROUP BY c.CreditorID UPDATE @A5Schedule SET CreditorName = ISNULL(e.Title,'') + ' ' + ISNULL(e.FirstName,'') + ' ' + ISNULL(e.LastName,'') FROM creditor c INNER JOIN Employee e ON c.EmployeeID = e.EmployeeID INNER JOIN @A5Schedule a5 ON c.CreditorID = A5.CreditorID UPDATE @A5Schedule SET EmailAddress = isnull(n.Number,'') FROM Creditor c INNER JOIN Employee_Number en ON c.EmployeeID = en.EmployeeID INNER JOIN @A5Schedule a5 ON C.CreditorID = A5.CreditorID INNER JOIN Number n ON en.NumberID = n.NumberID and n.NumberTypeID = 4 UPDATE @A5Schedule SET CreditorAddress = ISNULL(ad.Address,'') + ' ' + ISNULL(ad.City,'') + ' ' + ISNULL(ad.State,'') + ' ' + ISNULL(ad.PostCode,'') FROM Creditor c INNER JOIN Address ad ON c.AddressID = ad.AddressID INNER JOIN @A5Schedule a5 ON c.CreditorID = a5.CreditorID UPDATE @A5Schedule SET TickIfSecured = 'X' FROM creditor c INNER JOIN @A5Schedule a5 ON c.CreditorID = a5.CreditorID AND c.CreditorClassID = 1 /* UPDATE @A5Schedule SET PPRS = 'What goes here?' FROM creditor c INNER JOIN @A5Schedule A5 ON a5.CreditorID = c.CreditorID and c.CreditorClassID = 1 */ UPDATE @A5Schedule SET AssetDetails = (SELECT STUFF( (SELECT ', ' + A.Description FROM @A5Schedule A5a INNER JOIN Security S ON A5a.CreditorID = S.CreditorID INNER JOIN SecurityAsset SA ON SA.SecurityID = S.SecurityID INNER JOIN ASSET A ON SA.AssetID = A.AssetID WHERE A5a.CreditorID = A5b.CreditorID and a.AssetClassID != 4 FOR XML PATH('') ), 1, 1, '')) FROM @A5Schedule A5b if exists (select * from @A5Schedule a5 inner join security s on a5.CreditorID = s.CreditorID inner join SecurityAsset sa on s.SecurityID = sa.SecurityID inner join asset a on sa.AssetID = a.AssetID and a.AssetClassID = 4) UPDATE @A5Schedule Set AssetDetails = 'Pre-Appointment Accounts Receivables,' + AssetDetails from @A5Schedule a5 inner join security s on a5.CreditorID = s.CreditorID inner join SecurityAsset sa on s.SecurityID = sa.SecurityID inner join asset a on sa.AssetID = a.AssetID and a.AssetClassID = 4 UPDATE @A5Schedule SET TickIfRelatedParty = 'X' FROM creditor c INNER JOIN @A5Schedule a5 ON c.CreditorID = a5.CreditorID WHERE IsNull(Flags,0) & 1 = 1 /* EXEC repsp_ROCAP_Annexure_A5 9682 */ IF @AnnexuresBlank = 'No' BEGIN IF NOT EXISTS (SELECT * FROM @A5Schedule) SELECT 'CREDITOR''S NAME' = '' ,'POSTAL ADDRESS IN FULL' = '' ,'EMAIL ADDRESS' = '' ,'TICK IF SECURED' = '' ,'PPRS (IF APPLICABLE)' = '' ,'IF SECURED GIVE ASSET DETAILS' = '' ,'TICK IF RELATED PARTY' = '' ,'AMOUNT OWING' = '' ELSE SELECT CreditorName AS 'CREDITOR''S NAME' ,CreditorAddress AS 'POSTAL ADDRESS IN FULL' ,EmailAddress AS 'EMAIL ADDRESS' ,TickIfSecured AS 'TICK IF SECURED' ,PPRS AS 'PPRS (IF APPLICABLE)' ,AssetDetails AS 'IF SECURED GIVE ASSET DETAILS' ,TickIfRelatedParty AS 'TICK IF RELATED PARTY' ,AmountOwing AS 'AMOUNT OWING' FROM @A5Schedule ORDER BY CreditorName END ELSE SELECT 'CREDITOR''S NAME' = '' ,'POSTAL ADDRESS IN FULL' = '' ,'EMAIL ADDRESS' = '' ,'TICK IF SECURED' = '' ,'PPRS (IF APPLICABLE)' = '' ,'IF SECURED GIVE ASSET DETAILS' = '' ,'TICK IF RELATED PARTY' = '' ,'AMOUNT OWING' = '' SET NOCOUNT OFF GO GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO GRANT EXECUTE ON [dbo].repsp_ROCAP_Annexure_A5 TO [insol2_users] GO if exists (select * from sysobjects where id = object_id(N'[dbo].[repsp_ROCAP_Annexure_A6]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].repsp_ROCAP_Annexure_A6 GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE PROCEDURE dbo.repsp_ROCAP_Annexure_A6 @AdminID Int ,@AnnexuresBlank NVARCHAR(3) = 'NO' AS /* Author: Simon Allen Date: Purpose: Debtor schedule to attach to the ROCAP Notes: Revision History: 30/11/2018 - Change request see Zendesk 6148 30/11/2018 - Updated to populate the AmountOutstanding column with the AssetTotal 30/11/2018 - Updated to populate the Estimated Realisae column with the RATAERV DEBUG EXEC repsp_ROCAP_Annexure_A6 2479 Notes: */ SET NOCOUNT ON DECLARE @Loc INT DECLARE @PracticeName VARCHAR(100) DECLARE @practiceID INT DECLARE @A6Schedule TABLE ( DebtorID int ,DebtorName varchar(200) ,Description varchar(200) ,DebtorAddress varchar(800) ,AmountOutstanding money ,EstimatedRealise money ,PPRS varchar(100) ,SecurityType varchar(75) ,DateSecured datetime ) SELECT @Loc = dbo.UFnSystemLocation() INSERT INTO @A6Schedule (DebtorID, DebtorName, Description, AmountOutstanding, EstimatedRealise) SELECT ass.CounterpartyID, ISNULL(cp.name,''), ac.Description ,sum(AssetValue), sum(RATAERV) FROM asset ass INNER JOIN counterparty cp ON ass.CounterpartyID = cp.CounterpartyID INNER JOIN account ac ON ass.AccountID = ac.AccountID WHERE ass.AdminID = @AdminID AND ass.IsRATAAsset = 1 AND ass.AssetClassID = 4 GROUP BY ass.CounterpartyID, cp.Name, ac.Description UPDATE @A6Schedule SET DebtorAddress = ISNULL(ad.Address,'') + ' ' + ISNULL(ad.City,'') + ' ' + ISNULL(ad.State,'') + ' ' + ISNULL(ad.PostCode,'') FROM @A6Schedule a6 INNER JOIN Counterparty_Address ca ON a6.DebtorID = ca.CounterpartyID INNER JOIN Address ad ON ca.AddressID = ad.AddressID and IsMailing = 1 /* EXEC repsp_ROCAP_Annexure_A6 9682 */ IF @AnnexuresBlank = 'No' BEGIN IF NOT EXISTS (SELECT * FROM @A6Schedule) SELECT 'DEBTOR NAME' = '' ,'DESCRIPTION' = '' ,'DEBTOR ADDRESS' = '' ,'AMOUNT OUTSTANDING' = '' ,'ESTIMATED AMOUNT REALISABLE' = '' ,'PPSR IF APPLICABLE' = '' ,'SECURITY TYPE' = '' ,'DATE SECURED' = '' ELSE SELECT DebtorName AS 'DEBTOR NAME' ,Description AS 'DESCRIPTION' ,DebtorAddress AS 'DEBTOR ADDRESS' ,AmountOutstanding AS 'AMOUNT OUTSTANDING' ,EstimatedRealise AS 'ESTIMATED AMOUNT REALISABLE' ,PPRS AS 'PPSR IF APPLICABLE' ,SecurityType AS 'SECURITY TYPE' ,DateSecured AS 'DATE SECURED' FROM @A6Schedule END ELSE SELECT 'DEBTOR NAME' = '' ,'DESCRIPTION' = '' ,'DEBTOR ADDRESS' = '' ,'AMOUNT OUTSTANDING' = '' ,'ESTIMATED AMOUNT REALISABLE' = '' ,'PPSR IF APPLICABLE' = '' ,'SECURITY TYPE' = '' ,'DATE SECURED' = '' SET NOCOUNT OFF GO GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO GRANT EXECUTE ON [dbo].repsp_ROCAP_Annexure_A6 TO [insol2_users] GO If exists (select * from sysobjects where id = object_id(N'[dbo].[repsp_HomePage]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP procedure [dbo].[repsp_HomePage] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO CREATE PROCEDURE [dbo].[repsp_HomePage] @UserID Int ,@NoDetail Bit = 0 ,@MenuItem int = 1 ,@ServerVer varchar(10) = Null ,@ClientVer varchar(10) = Null ,@OSVer varchar(10) = Null AS /* EXEC repsp_HomePage @UserID = 1 EXEC repsp_HomePage @UserID = 1352,@MenuItem = 1 exec repsp_HomePage @UserID=1352,@MenuItem = 3 SELECT dbo.uFnHomePageTableHeadingToDo(1352,1) */ SET NOCOUNT ON DECLARE @Output Varchar(max) DECLARE @JavaHeader Varchar(max) DECLARE @JavaBody Varchar(max) DECLARE @HTML Varchar(max) DECLARE @XMLDATA Varchar(max) DECLARE @Greeting Varchar(max) DECLARE @GreetingExt Varchar(max) DECLARE @Loc Int DECLARE @CountActiveUsers Int DECLARE @IsAdminUser Int DECLARE @CurrentVersion varchar(50) DECLARE @CurrentVersionImageSrc varchar(100) DECLARE @ReleasenotesJava varchar(500) DECLARE @ReleasenotesSrc varchar(100) DECLARE @CurrentVersionURL varchar(250) = 'https://exalt.zendesk.com/hc/en-us/articles/360000824315-Current-Software-Release' --Admin User If Exists(select * from User_Role Where UserID = @UserID AND RoleID = 1) BEGIN Select @IsAdminUser = 1 Select @CountActiveUsers = Count(u.UserID) FROM [User] u WHERE u.IsInactive = 0 AND u.UserID > 1 END -- Versions SELECT @CurrentVersion = Setting FROM dbo.genvw_ApplicationSetting WHERE SettingID = 1 --SELECT @CurrentVersion = '5.1.32' -- Images SELECT @CurrentVersionImageSrc = 'http://www.members.iinet.net.au/~exalttech/version/' + @CurrentVersion + '.gif' SELECT @ReleasenotesSrc = 'http://www.members.iinet.net.au/~exalttech/version/rn' + @CurrentVersion + '.js' --Greeting SELECT @Loc = dbo.uFnSystemLocation() SELECT @JavaHeader = ' ' SELECT @ServerVer = setting from ApplicationSetting where settingid = 1 SELECT @Greeting = CASE WHEN Right(Convert(varchar(20), getdate(), 100),2) = 'AM' THEN 'Good Morning ' ELSE 'Good Afternoon ' END SELECT @GreetingExt = IsNull(FirstName, CorrespondenceName) From dbo.[User] Where UserID = @UserID SELECT @GreetingExt = '' + @GreetingExt + '' IF dbo.uFnSystemLocation() = 1 SELECT @GreetingExt = @GreetingExt + ', welcome to MYOB Insolvency.' ELSE SELECT @GreetingExt = @GreetingExt + ', welcome to insol6.' --IF @IsAdminUser = 1 -- SELECT @GreetingExt = @GreetingExt + ' ' + 'Active users: ' + convert(varchar(max),@CountActiveUsers) IF @NoDetail = 1 BEGIN SELECT @Output = @Greeting + @GreetingExt SELECT @Output = @Output + '

Loading data, please wait.

' END ELSE BEGIN SELECT @Output = dbo.uFnHomePageNavBar (@UserID) SELECT @Output = @Output + '

' --+'
' -- + '' -- + '' --+'
' + '' + '' + '' + '' --+ '' -- + '' --+ '' + '
' + @Greeting + @GreetingExt + '
' -- + '' -- + '' -- + '
' SELECT @Output = @Output + '

' SELECT @Output = @Output + dbo.uFnHomePageTableHeadingToDo(@UserID,@MenuItem,null) IF @MenuItem = 1 BEGIN SELECT @Output = @Output + '' + '' SELECT @Output = @Output + dbo.uFnHomePageUserMeetings(@UserID) SELECT @Output = @Output + dbo.uFnHomePageTaskDesc(@UserID) SELECT @Output = @Output + dbo.uFnHomePageUserTaskDesc(@UserID) SELECT @Output = @Output + dbo.uFnHomePageLodgements(@UserID) IF @Loc = 1 BEGIN SELECT @Output = @Output + dbo.uFnHomePageComplianceReview(@UserID) SELECT @Output = @Output + dbo.uFnHomePageMetrics(@UserID) END IF @Loc = 3 BEGIN SELECT @Output = @Output + dbo.uFnHomePageBond(@UserID) SELECT @Output = @Output + dbo.uFnHomePageCaseLoad(@UserID) SELECT @Output = @Output + dbo.uFnHomePageJobExpiry(@UserID) SELECT @Output = @Output + dbo.uFnHomePageJobOutcome(@UserID) END END SELECT @Output = @Output + '

' SELECT @Output = @Output + dbo.uFnHomePageTableHeadingTrans(@UserID,@MenuItem,null) IF @MenuItem = 2 BEGIN SELECT @Output = @Output + dbo.uFnHomePagePrintChequesDesc(@UserID) SELECT @Output = @Output + dbo.uFnHomePageAccPayDesc(@UserID) SELECT @Output = @Output + dbo.uFnHomePageAccPayFeesCostsDesc(@UserID) SELECT @Output = @Output + dbo.uFnHomePageTrans(@UserID) -- SELECT @Output = @Output + dbo.uFnHomePageTax(@UserID) --+ '

' + '

' SELECT @Output = @Output + dbo.uFnHomePageBankOverdraft(@UserID) -- SELECT @Output = @Output + dbo.uFnHomePageBankLink(@UserID) END SELECT @Output = @Output + '

' SELECT @Output = @Output + dbo.uFnHomePageTableHeadingDocs(@UserID,@MenuItem,null) IF @MenuItem = 3 BEGIN SELECT @Output = @Output + dbo.uFnHomePageUserDocsToAuthorise(@UserID) SELECT @Output = @Output + dbo.uFnHomePageUserDocsAwaitingAuthorise(@UserID) SELECT @Output = @Output + dbo.uFnHomePageUserDocsToSend(@UserID) END SELECT @Output = @Output + '

' SELECT @Output = @Output + dbo.uFnHomePageTableHeadingBatch(@UserID,@MenuItem,null) IF @MenuItem = 4 BEGIN SELECT @Output = @Output + dbo.uFnHomePageBatchAuthDesc(@UserID) SELECT @Output = @Output + dbo.uFnHomePageBatchDesc(@UserID) SELECT @Output = @Output + dbo.uFnHomePageBatchSubmitDesc(@UserID) SELECT @Output = @Output + dbo.uFnHomePageBatchRejectDesc(@UserID) SELECT @Output = @Output + dbo.uFnHomePageBatchAuthDescAllUsers(@UserID) --SELECT @Output = @Output + dbo.uFnHomePageBatchToPostDesc(NULL) END SELECT @Output = @Output + '

' SELECT @Output = @Output + dbo.uFnHomePageTableHeadingContrib(@UserID,@MenuItem,null) IF @MenuItem = 5 BEGIN SELECT @Output = @Output + dbo.uFnHomePageContribOverDue(@UserID) SELECT @Output = @Output + dbo.uFnHomePageContribUnpaid(@UserID) SELECT @Output = @Output + dbo.uFnHomePageContribExpiry(@UserID) END SELECT @Output = @Output + '

' SELECT @Output = @Output + dbo.uFnHomePageTableHeadingCreditorPortal(@UserID,@MenuItem,null) IF @MenuItem = 6 BEGIN SELECT @Output = @Output + dbo.uFnHomePageCreditorPortalClaims(@UserID) SELECT @Output = @Output + dbo.uFnHomePageCreditorPortalJobsPosted(@UserID) END SELECT @Output = @Output + '

' SELECT @Output = @Output + dbo.uFnHomePageTableHeadingSTP(@UserID,@MenuItem,null) IF @MenuItem = 7 BEGIN SELECT @Output = @Output + dbo.uFnHomePageSTPUnsubmitted(@UserID) SELECT @Output = @Output + dbo.uFnHomePageSTPSubmissionInProgress(@UserID) SELECT @Output = @Output + dbo.uFnHomePageSTPSubmissionError(@UserID) END SELECT @Output = @Output + '

' --Menu Item for Metrics --SELECT @Output = @Output + dbo.uFnHomePageTableHeadingMetrics(@UserID,@MenuItem,null) --IF @MenuItem = 8 -- BEGIN -- SELECT @Output = @Output + dbo.uFnHomePageMetricsSummary(@UserID) -- END SELECT @Output = @Output + '

' --SELECT @Output = @Output + dbo.uFnHomePageTableHeadingPM(@UserID,@MenuItem,null) --IF @MenuItem = 6 -- BEGIN -- --SELECT @Output = @Output + dbo.uFnHomePagePMDesc(@UserID) -- SELECT @Output = @Output + dbo.uFnHomePageWIPSummary(@UserID,NULL) -- END END /* SELECT @Output = @Output + '

' + '' + 'chart fees' + '' +'

' Notifications event IF EXISTS(Select * FROM dbo.User_Role WHERE UserID = @UserID AND RoleID = 1) SELECT @Output = @Output + '

Click here to configure notifications

' SELECT @Output = @Output + ' ' http://www.google.com.au/webhp?sourceid=navclient&ie=UTF-8#hl=en&output=search&sclient=psy-ab&q=30+whitfeld+street+floreat&oq=30+whitfeld+street+floreat&gs_l=hp.3..0i30.3804.8782.0.9407.26.18.0.4.4.1.677.4686.3-6j3j2.11.0.les%3B..0.0...1c.1.mDas-eQVdvc&pbx=1&bav=on.2,or.r_gc.r_pw.r_qf.&fp=7680231318a44bb0&bpcl=35466521&biw=1006&bih=605 --twitter SELECT @Output = @Output + '

Keep abreast of the latest updates by following us on twitter Twitter

' --google SELECT @Output = @Output + '

Keep abreast of the latest updates by following us on google

' */ SET @HTML = + ' ' --+ @JavaHeader + dbo.uFnHomePageStyleSheet() + '' +'' + '' + @Output +' ' SELECT @HTML SET NOCOUNT OFF RETURN @@ERROR GO GRANT EXECUTE ON [dbo].repsp_HomePage TO [insol2_users] GO -- * ************ --END OF 5.2.37 Cumulative -- * ************ Update DataCache Set CacheUID = NewID() Go Update ApplicationSetting Set Setting = '5.2.38' Where SettingID = 1 if exists (select * from sysobjects where id = object_id(N'[dbo].[repsp_eLodge5602Payments]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[repsp_eLodge5602Payments] GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE PROCEDURE dbo.repsp_eLodge5602Payments @AdminID Int ,@AdminTypeID Int ,@AsAtDate DateTime ,@ShowDetail Bit = 0 ,@FromDate DateTime AS /* Author: Jonathan Osborne Date: 24/07/2017 Purpose: Notes: Revision History: exec dbo.repsp_eLodge5602Payments @AdminID=6927, @AdminTypeID=10, @AsAtDate='20190529' ,@FromDate='20180101' */ SET NOCOUNT ON DECLARE @PmtsA Table (CreditorClaimTypeID Int, IsFeg Bit, ControlValue Money, AmountPaid Money, Rate DECIMAL(16,2), EstRate DECIMAL(16,2) ) DECLARE @Pmts Table (CreditorClaimTypeID Int, IsFeg Bit, ControlValue Money, AmountPaid Money, Rate DECIMAL(16,2), EstRate DECIMAL(16,2) ) DECLARE @UnclEnts Table (EntryID Int) INSERT INTO @PmtsA (CreditorClaimTypeID, IsFeg, ControlValue, AmountPaid) SELECT CreditorClaimTypeID = CASE WHEN cct.CreditorClaimTypeID In(100) THEN 100 WHEN cct.CreditorClaimTypeID In(150) THEN 150 WHEN cct.CreditorClaimTypeID In(200) THEN 200 WHEN cct.CreditorClaimTypeID In(250,255) THEN 240 --WHEN cct.CreditorClaimTypeID In(255) THEN 255 WHEN cct.CreditorClaimTypeID In(270) THEN 270 WHEN cct.CreditorClaimTypeID In(280) THEN 280 WHEN cct.CreditorClaimTypeID In(400,600) THEN 400 WHEN cct.CreditorClaimTypeID In(500) THEN 500 ELSE 0 END ,IsFeg = CASE WHEN d.CreditorClaimTypeID = 10000 THEN 1 ELSE 0 END ,ControlValue = IsNull(Sum(CASE WHEN se.EntryType In (100,101) AND ISNULL(IsERAClaimAdmin,0) = 0 THEN (Net * (Direction * -1)) ELSE 0 END),0) ,AmountPaid = IsNull(Sum(CASE WHEN se.EntryType In (100,101) THEN 0 WHEN se.AdminTypeID <> @AdminTypeID THEN 0 ELSE (Net * Direction) END),0) --,se.EntryID FROM dbo.SubEntry se INNER JOIN dbo.CreditorClaim cc ON cc.AccountID = se.Account_AccountID AND cc.CreditorID = se.CreditorID INNER JOIN dbo.Creditor c ON c.CreditorID = cc.CreditorID INNER JOIN dbo.CreditorClaimType cct ON cct.CreditorClaimTypeID = cc.CreditorClaimTypeID LEFT OUTER JOIN dbo.Dividend d ON d.DividendID = se.CreditorDividendID WHERE se.ContextID = @AdminID AND IsNull(se.IsBatchPosting, 0) = 0 AND (se.EntryDate <= @AsAtDate) AND ((se.EntryDate >= @FromDate AND d.CreditorClaimTypeID = 10000) OR (NOT d.CreditorClaimTypeID = 10000)) AND ISNULL(c.NoClaimAdvised,0) = 0 AND (cc.RATAAmount <> 0 OR cc.AdvisedAmount <> 0 OR cc.ClaimedTotalAmount <> 0 OR cc.AdmittedAmount <> 0 OR cc.RejectedPriorityAmount <> 0 ) GROUP BY CASE WHEN cct.CreditorClaimTypeID In(100) THEN 100 WHEN cct.CreditorClaimTypeID In(150) THEN 150 WHEN cct.CreditorClaimTypeID In(200) THEN 200 WHEN cct.CreditorClaimTypeID In(250,255) THEN 240 --WHEN cct.CreditorClaimTypeID In(255) THEN 255 WHEN cct.CreditorClaimTypeID In(270) THEN 270 WHEN cct.CreditorClaimTypeID In(280) THEN 280 WHEN cct.CreditorClaimTypeID In(400,600) THEN 400 WHEN cct.CreditorClaimTypeID In(500) THEN 500 ELSE 0 END ,CASE WHEN d.CreditorClaimTypeID = 10000 THEN 1 ELSE 0 END INSERT INTO @Pmts (CreditorClaimTypeID, IsFeg, ControlValue, AmountPaid) SELECT CreditorClaimTypeID = CASE WHEN CreditorClaimTypeID In(100,150) THEN 100 WHEN CreditorClaimTypeID In(200) THEN 200 WHEN CreditorClaimTypeID In(240) THEN 240 --WHEN cct.CreditorClaimTypeID In(255) THEN 255 WHEN CreditorClaimTypeID In(270) THEN 270 WHEN CreditorClaimTypeID In(280) THEN 280 WHEN CreditorClaimTypeID In(400,600) THEN 400 WHEN CreditorClaimTypeID In(500) THEN 500 ELSE 0 END ,IsFeg ,Sum(ControlValue) ,Sum(AmountPaid) FROM @PmtsA GROUP BY CASE WHEN CreditorClaimTypeID In(100,150) THEN 100 WHEN CreditorClaimTypeID In(200) THEN 200 WHEN CreditorClaimTypeID In(240) THEN 240 --WHEN cct.CreditorClaimTypeID In(255) THEN 255 WHEN CreditorClaimTypeID In(270) THEN 270 WHEN CreditorClaimTypeID In(280) THEN 280 WHEN CreditorClaimTypeID In(400,600) THEN 400 WHEN CreditorClaimTypeID In(500) THEN 500 ELSE 0 END ,IsFeg INSERT INTO @UnclEnts SELECT DISTINCT EntryID FROM dbo.SubEntry se Inner Join dbo.Account acc On acc.AccountID = se.Account_AccountID WHERE se.ContextID = @AdminID AND acc.AccountUID = '{36C41188-1230-41E0-894F-5B9B9D8D6DB7}' INSERT INTO @Pmts (CreditorClaimTypeID, IsFeg, ControlValue, AmountPaid) SELECT CreditorClaimTypeID = -1 ,IsFeg = 0 ,ControlValue = 0 ,AmountPaid = IsNull(Sum(Net * Direction * -1),0) FROM dbo.SubEntry se Inner Join dbo.Account acc On acc.AccountID = se.Account_AccountID Inner Join dbo.AccountGroup ag On ag.AccountGroupID = acc.AccountGroupID Inner Join dbo.Shareholder s ON s.ShareholderID = se.ShareHolderID Left Outer Join @UnclEnts ue On ue.EntryID = se.EntryID Where se.Direction = -1 AND se.ContextID = @AdminID --AND se.AdminTypeID = @AdminTypeID AND IsNull(se.IsBatchPosting, 0) = 0 AND (se.EntryDate <= @AsAtDate) AND ue.EntryID Is Null IF Exists(SELECT * FROM @Pmts WHERE CreditorClaimTypeID = -1) UPDATE @Pmts SET ControlValue = IsNull((SELECT Sum(sg.DividendShares) from dbo.Shareholder s Inner Join dbo.Shareholding sg On sg.ShareholderID = s.ShareholderID Where s.AdminID = @AdminID), 0) FROM @Pmts p WHERE p.CreditorClaimTypeID = -1 UPDATE @Pmts SET Rate = CASE WHEN CreditorClaimTypeID = 100 THEN 0 ELSE (AmountPaid / ControlValue) * 100 END WHERE IsFeg = 0 AND ControlValue > 0 UPDATE @Pmts SET EstRate = 0 WHERE IsFeg = 0 UPDATE @Pmts SET AmountPaid = 0 WHERE AmountPaid Is Null UPDATE @Pmts SET Rate = 0 WHERE Rate Is Null --Work out the estimated return to each class DECLARE @FundsToPref Money DECLARE @FundsToPrefA Money DECLARE @ToApply Money EXECUTE dbo.repsp_CurrentPosition @AdminID=@AdminID,@AdminTypeID=@AdminTypeID,@AsAtDate=@AsAtDate,@AccountInClause=Null,@AsAccrual=0,@GetERVToPref=1,@FundsToPref=@FundsToPref OUTPUT IF @FundsToPref < 0 SET @FundsToPref = 0 SET @FundsToPrefA = @FundsToPref DECLARE @Claims Table(CreditorClaimTypeID Int, ControlValue Money, Applied Money) INSERT INTO @Claims (CreditorClaimTypeID, ControlValue) SELECT CreditorClaimTypeID ,ControlValue FROM @PmtsA WHERE IsFeg = 0 AND CreditorClaimTypeID >= 200 AND CreditorClaimTypeID < 400 INSERT INTO @Claims (CreditorClaimTypeID, ControlValue) SELECT CreditorClaimTypeID ,ControlValue FROM @PmtsA WHERE IsFeg = 0 AND CreditorClaimTypeID = 150 INSERT INTO @Claims (CreditorClaimTypeID, ControlValue) SELECT CreditorClaimTypeID ,ControlValue FROM @PmtsA WHERE IsFeg = 0 AND CreditorClaimTypeID >= 400 INSERT INTO @Claims (CreditorClaimTypeID, ControlValue) SELECT CreditorClaimTypeID ,ControlValue FROM @PmtsA WHERE IsFeg = 0 AND CreditorClaimTypeID = -1 UPDATE @Claims SET @ToApply = CASE WHEN ControlValue < @FundsToPref THEN ControlValue ELSE @FundsToPref END ,Applied = @ToApply ,@FundsToPref = @FundsToPref - @ToApply FROM @Claims c UPDATE @Pmts SET EstRate = CASE WHEN c.Applied > p.ControlValue THEN 100 ELSE (c.Applied / p.ControlValue) * 100 END FROM @Pmts p INNER JOIN @Claims c ON c.CreditorClaimTypeID = p.CreditorClaimTypeID WHERE NOT p.ControlValue = 0 UPDATE @PmtsA SET EstRate = CASE WHEN c.Applied > p.ControlValue THEN 100 ELSE (c.Applied / p.ControlValue) * 100 END FROM @PmtsA p INNER JOIN @Claims c ON c.CreditorClaimTypeID = p.CreditorClaimTypeID WHERE NOT p.ControlValue = 0 IF @ShowDetail = 0 BEGIN IF Not Exists(SELECT * from @Pmts Where CreditorClaimTypeID = 0) INSERT INTO @Pmts Values (0,0,0,0,0,0) UPDATE @Pmts SET ControlValue = @FundsToPrefA WHERE CreditorClaimTypeID = 0 AND IsFeg = 0 SELECT * from @Pmts END ELSE BEGIN SELECT CreditorClaimTypeID = 0 ,Description = 'Estimated funds available to priority claims' ,ControlValue = @FundsToPrefA ,Applied = Null ,EstRate = Null UNION SELECT c.CreditorClaimTypeID ,cc.Description ,c.ControlValue ,c.Applied ,p.EstRate FROM @Claims c LEFT OUTER JOIN @PmtsA p ON p.CreditorClaimTypeID = c.CreditorClaimTypeID AND p.IsFeg = 0 INNER JOIN dbo.CreditorClaimType cc ON cc.CreditorClaimTypeID = c.CreditorClaimTypeID END SET NOCOUNT OFF RETURN @@ERROR GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO GRANT EXECUTE ON [dbo].[repsp_eLodge5602Payments] TO [insol2_users] GO If exists (select * from sysobjects where id = object_id(N'[dbo].[repsp_HomePageSTPUnsubmitted]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) DROP procedure [dbo].repsp_HomePageSTPUnsubmitted GO CREATE PROCEDURE dbo.repsp_HomePageSTPUnsubmitted @UserID Int AS SET NOCOUNT ON DECLARE @Output Varchar(max) DECLARE @HTML Varchar(max) -- TMP DATA -- Create the body set @Output = cast( ( select td = ShortName + '' + cast( Name as varchar(100) ) + '' + cast( BatchType as varchar(100) ) + '' + cast(Batches as varchar(100) ) from ( SELECT ShortName = '' + a.ShortName + '' ,Name = a.Name ,BatchType = '' + 'STP unsubmitted' ,Batches = '' + Ltrim(Rtrim(Str(IsNull(Count(b.BatchID),0)))) + '' from dbo.Batch b Inner Join dbo.Account acc On acc.AccountId = b.AccountID Inner Join dbo.Admin a ON a.AdminID = acc.AdminID Left Outer Join dbo.Transfer t On t.BatchID = b.BatchID Where acc.AccountGroupID = 1030 --Payments batch AND b.PayrollFlags = 1 AND t.TransferType_LookupID = 108800 AND b.PostedBy_UserID Is Not Null AND isNull(t.Flags,0) & 8 = 0 AND isNull(t.Flags,0) & 16 = 0 AND isNull(t.Flags,0) & 32 = 0 AND isNull(t.Flags,0) & 64 = 0 AND isNull(t.Flags,0) & 128 = 0 AND isNull(t.Flags,0) & 256 = 0 AND isNull(t.Flags,0) & 512 = 0 AND isNull(t.Flags,0) & 1024 = 0 AND isNull(t.Flags,0) & 2048 = 0 AND isNull(t.Flags,0) & 4096 = 0 /* AND b.NextFlags IS NULL AND a.AdminStatusID = 1 AND b.PostedBy_UserID IS NULL AND NOT ISNULL(b.BatchType_LookupID,0) = 900006 */ group by a.AdminID, a.ShortName, a.Name--, acc.Description,b.AccountID ) as d ORDER BY Name for xml path( 'tr' ), type ) as varchar(max) ) SET @HTML = ' ' + dbo.uFnHomePageStyleSheet() + + dbo.uFnHomePageTableSorter(4,1) + ' ' + dbo.uFnHomePageNavBar (@UserID) + dbo.uFnHomePageTableHeadingSTP(@UserID,0,'STP Batches Awaiting Submission') --'

Home \ --Unposted Batches Listing

+ '


' --+ '
Click a header cell to sort on that column.

' + '' + '' + '' + '' + '' + '' + replace( replace( IsNull(@Output,''), '<', '<' ), '>', '>' ) + '
Short NameNameBatch TypeNo. of Batches
' SELECT @HTML SET NOCOUNT OFF RETURN @@ERROR GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO GRANT EXECUTE ON [dbo].repsp_HomePageSTPUnsubmitted TO [insol2_users] GO --exec repsp_HomePageBatches 1025 GO /****** Object: UserDefinedFunction [dbo].[uFnHomePageCreditorPortalClaims] Script Date: 26/03/2018 5:43:18 PM ******/ If exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[uFnHomePageSTPUnsubmitted]') and xtype in (N'FN', N'IF', N'TF')) drop function [dbo].[uFnHomePageSTPUnsubmitted] GO /****** Object: UserDefinedFunction [dbo].[uFnHomePageCreditorPortalClaims] Script Date: 26/03/2018 5:43:18 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER OFF GO CREATE FUNCTION [dbo].[uFnHomePageSTPUnsubmitted] (@UserID Int) RETURNS varchar(max) AS /* REVISION HISTORY 01/11/2011 SM Add reporting manager, reporting user, appointee DEBUG SELECT dbo.uFnHomePageCreditorPortalClaims(1) */ BEGIN DECLARE @Ret Varchar(Max) DECLARE @TotCount Int -- TMP DATA SELECT @TotCount = IsNull(Count(b.BatchID),0) from dbo.Batch b Inner Join dbo.Account acc On acc.AccountId = b.AccountID Inner Join dbo.Admin a ON a.AdminID = acc.AdminID Left Outer Join dbo.Transfer t On t.BatchID = b.BatchID Where acc.AccountGroupID = 1030 --Payments batch AND b.PayrollFlags = 1 AND t.TransferType_LookupID = 108800 AND b.PostedBy_UserID Is Not Null AND isNull(t.Flags,0) & 8 = 0 AND isNull(t.Flags,0) & 16 = 0 AND isNull(t.Flags,0) & 32 = 0 AND isNull(t.Flags,0) & 64 = 0 AND isNull(t.Flags,0) & 128 = 0 AND isNull(t.Flags,0) & 256 = 0 AND isNull(t.Flags,0) & 512 = 0 AND isNull(t.Flags,0) & 1024 = 0 AND isNull(t.Flags,0) & 2048 = 0 AND isNull(t.Flags,0) & 4096 = 0 -- RETURN IF @TotCount > 0 SELECT @Ret = '
' -- +'' + '
To SubmitBatches with payroll events requiring submission to STP ' + ' ' + Ltrim(Rtrim(Str(@TotCount))) + CASE WHEN @TotCount = 1 THEN ' item' ELSE ' items' END + ' ' +'
' ELSE SELECT @Ret = '' + '' + '' + '' + '' +'
To SubmitThere isn''t any STP batches awaiting submission.
' SELECT @Ret = '

' + IsNull(@Ret,'') + '

' RETURN @Ret END GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO GRANT EXECUTE ON [dbo].[uFnHomePageSTPUnsubmitted] TO [insol2_users] GO if exists (select * from sysobjects where id = object_id(N'[dbo].repsp_eLodgeEARRemuneration') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure dbo.repsp_eLodgeEARRemuneration GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE PROCEDURE dbo.repsp_eLodgeEARRemuneration @AdminID Int ,@AdminTypeID Int = Null ,@FromDate DateTime ,@ToDate DateTime AS /* Author: JO Date: 24/07/17 Purpose: To validate the 524 eLodgement -- Exec dbo.repsp_eLodgeEARRemuneration @AdminID = 1725,@AdminTypeID = 10, @FromDate = '20111101', @ToDate = '20191031' */ SET NOCOUNT ON SET @FromDate = '18991230' Declare @FeesExpAccountID Int Declare @CostsExpAccountID Int CREATE TABLE [dbo].[#SubEntry] ( [EntryID] Int ,[SubEntryID] Int ,[SourceSubEntryID] Int ,[EntryDate] DateTime ,[Payee] nVarchar(200) ,[AccountID] Int ,[Account] Varchar(200) ,[Memo] Varchar(2000) ,[ReportableTotal] Money ,[Tax] Money ,[Direction] int ,[EntryType] Smallint ,[CreditorDividendID] Int ,[AssetID] Int ,[LiabilityID] Int ) --Temp table for joining sub accounts CREATE TABLE dbo.#Account ( AdminID Int ,AccountID Int ) EXECUTE dbo.repsp_RecPayData @AdminID=@AdminID, @AdminTypeID=@AdminTypeID, @AdminStartDate=@FromDate, @ReportStartDate=@FromDate, @ReportEndDate=@ToDate, @AccountInClause=Null, @InclFlaggedTrans=1 SELECT @FeesExpAccountID = ( SELECT acc.AccountID FROM dbo.Account acc WHERE acc.AdminID = @AdminID AND acc.AccountUID = '{3509E1B4-879E-4F55-8666-F450FF6F3C1A}' ) SELECT @CostsExpAccountID = ( SELECT acc.AccountID FROM dbo.Account acc WHERE acc.AdminID = @AdminID AND acc.AccountUID = '{C46BE169-3F99-4EA2-A291-9F72BBCD031C}' ) DECLARE @Fees Table (AdminTypeID Int, Fees Money) DECLARE @Costs Table (AdminTypeID Int, Costs Money) DECLARE @Output Table ( AdminTypeID Int ,FeesApproved Money ,FeesApprovedGST Money ,Fees Money ,Costs Money ) DECLARE @TAXRATE Float SELECT @TAXRATE = TAXRATE FROM TaxCode WHERE TaxCodeID = 7 EXEC usp_AccountsFromID @FeesExpAccountID INSERT INTO @Fees SELECT se1.AdminTypeID, IsNull(Sum(dbo.uFnRecPaySign (se.EntryType, se.ReportableTotal, se.Direction) * -1),0) FROM dbo.#SubEntry se INNER JOIN dbo.#Account acc ON acc.AccountID = se.AccountID INNER JOIN dbo.SubEntry se1 ON se1.SubentryID = se.SubEntryID WHERE se.EntryDate >= @FromDate AND se.EntryDate <= @ToDate GROUP BY se1.AdminTypeID EXEC usp_AccountsFromID @CostsExpAccountID INSERT INTO @Costs SELECT se1.AdminTypeID, IsNull(Sum(dbo.uFnRecPaySign (se.EntryType, se.ReportableTotal, se.Direction) * -1),0) FROM dbo.#SubEntry se INNER JOIN dbo.#Account acc ON acc.AccountID = se.AccountID INNER JOIN dbo.SubEntry se1 ON se1.SubentryID = se.SubEntryID WHERE se.EntryDate >= @FromDate AND se.EntryDate <= @ToDate GROUP BY se1.AdminTypeID INSERT INTO @Output (AdminTypeID) SELECT @AdminTypeID WHERE @AdminTypeID Not In(SELECT AdminTypeID From @Output) INSERT INTO @Output (AdminTypeID) SELECT AdminTypeID FROM dbo.AdminTypeHistory WHERE AdminID = @AdminID AND AdminTypeID Not In(SELECT AdminTypeID From @Output) UPDATE @Output SET Fees = IsNull((SELECT Sum(Fees) FROM @Fees WHERE AdminTypeID = o.AdminTypeID),0) FROM @Output o UPDATE @Output SET Costs = IsNull((SELECT Sum(Costs) FROM @Costs WHERE AdminTypeID = o.AdminTypeID),0) FROM @Output o UPDATE @Output SET FeesApproved = IsNull((SELECT Sum(Value) from dbo.CustomProperty cp Inner Join dbo.Admin_CustomProperty acp On acp.CustomPropertyID = cp.CustomPropertyID WHERE acp.AdminID = @AdminID AND cp.AdminTypeID = o.AdminTypeID AND cp.CustomPropertyTypeID = 999),0) FROM @Output o UPDATE @Output SET FeesApprovedGST = FeesApproved * @TAXRATE --Return output SELECT at.ShortDescription ,Data = l.Data ,Approved = o.FeesApproved + o.FeesApprovedGST ,Fees = o.Fees ,Costs = o.Costs FROM @Output o INNER JOIN dbo.AdminType at ON at.AdminTypeID = o.AdminTypeID INNER JOIN dbo.Lookup l ON l.Flag = o.AdminTypeID AND l.LookupListID = 111000 SET NOCOUNT OFF GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO GRANT EXECUTE ON dbo.repsp_eLodgeEARRemuneration TO [insol2_users] GO -- * ************ --END OF 5.2.38 Cumulative -- * ************ Update ApplicationSetting Set Setting = '5.2.39' Where SettingID = 1 if exists (select * from sysobjects where id = object_id(N'[dbo].[gensp_CreditorReadByMergeStatusPagePopulate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[gensp_CreditorReadByMergeStatusPagePopulate] GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE PROCEDURE dbo.gensp_CreditorReadByMergeStatusPagePopulate @FromClause Varchar(2000) ,@WhereClause Varchar(2000) ,@AbsolutePage Int ,@PageSize Int ,@PageCount Int OUTPUT ,@RecordCount Int OUTPUT AS /* Author: AUTOGENERATED Date: 18 Mar 2003 Purpose: Notes: Revision History: */ SET NOCOUNT ON DECLARE @CredsPaging Table ( [CreditorID] [Int] NULL ,[SortField] [nVarchar] (200) NULL ) DECLARE @CredsPagingSorted Table ( [PagedRecordNo] [int] IDENTITY (1, 1) NOT NULL ,[CreditorID] [Int] NULL ,[SortField] [nVarchar] (200) NULL ) --DO the join on the CP Table INSERT INTO @CredsPaging ( [CreditorID] ,[SortField] ) EXECUTE ('SELECT DISTINCT [dbo].[genvw_CreditorByMergeStatus].CreditorID, [dbo].[genvw_CreditorByMergeStatus].SortField ' + @FromClause + ' ' + @WhereClause) SET @RecordCount = IsNull(@RecordCount, 0) + @@ROWCOUNT SET @PageCount = @RecordCount / @PageSize IF @RecordCount > (@PageCount * @PageSize) SET @PageCount = @PageCount + 1 --Insert everything into the new table to sort it INSERT INTO @CredsPagingSorted ( [CreditorID] , SortField ) SELECT [CreditorID] , SortField FROM @CredsPaging ORDER BY [SortField] DECLARE @Creds Table (PagedRecordNo Int, CreditorID Int, CounterpartyID Int, EmployeeID Int) INSERT INTO @Creds SELECT cps.PagedRecordNo ,c.CreditorID ,c.CounterpartyID ,c.EmployeeID FROM [dbo].[Creditor] c INNER JOIN @CredsPagingSorted cps ON c.CreditorID = cps.CreditorID WHERE PagedRecordNo > ((@AbsolutePage * @PageSize) - @PageSize) AND PagedRecordNo <= (@AbsolutePage * @PageSize) ORDER BY PagedRecordNo --return the creditors SELECT c.* ,cd.Data ,cd.DocData ,cd.EventUID ,cd.EventFlags ,cd.EventResponseFlags FROM dbo.genvw_CreditorByMergeStatus c INNER JOIN @Creds cps ON c.CreditorID = cps.CreditorID INNER JOIN dbo.CreditorData cd ON cd.CreditorID = c.CreditorID ORDER BY PagedRecordNo --return the creditor claims SELECT DISTINCT cc.* FROM dbo.genvw_CreditorClaim cc INNER JOIN @Creds c ON c.CreditorID = cc.CreditorID --return the counterparies SELECT DISTINCT cp.* FROM dbo.genvw_Counterparty cp INNER JOIN @Creds c ON c.CounterpartyID = cp.CounterpartyID --return the employees SELECT DISTINCT emp.* FROM dbo.genvw_Employee emp INNER JOIN @Creds c ON c.EmployeeID = emp.EmployeeID SET NOCOUNT OFF RETURN @@ERROR GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO GRANT EXECUTE ON [dbo].[gensp_CreditorReadByMergeStatusPagePopulate] TO [insol2_users] GO update MergeTemplateGroup set IsMain = 1 where Parent_MergeTemplateID = 325020 and DisplayOrder = 10 update MergeTemplateGroup set IsMain = 1 where Parent_MergeTemplateID = 325025 and DisplayOrder = 10 update MergeTemplateGroup set IsMain = 1 where Parent_MergeTemplateID = 325030 and DisplayOrder = 10 Go --WWWWWWWWWWWWW if exists (select * from sysobjects where id = object_id(N'[dbo].[gensp_DocumentExtDMStatusUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[gensp_DocumentExtDMStatusUpdate] GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE PROCEDURE dbo.gensp_DocumentExtDMStatusUpdate @DocumentXML XML ,@DocFlags Int ,@AdminID Int ,@UserID Int = 1 AS /* See: $\Insolvency\Revive\SQL\Testing\Insol2TestProject\Doc for more complete tests Select * from Document Where DocumentID In(18107, 18108, 18109) --Ready exec gensp_DocumentExtDMStatusUpdate N'CDE1C108-61CC-41C6-B057-0CF0AA6B2B4C',128,5623,1 Select * from TransferWebRequest --Success exec gensp_DocumentExtDMStatusUpdate N'CDE1C108-61CC-41C6-B057-0CF0AA6B2B4Chttps://www.google.com.au/?gws_rd=ssl',@DocFlags=256,@AdminID=5623,@UserID=1 --Failure exec gensp_DocumentExtDMStatusUpdate N'CDE1C108-61CC-41C6-B057-0CF0AA6B2B4CInsert error message heretrue',@DocFlags=256,@AdminID=5623,@UserID=1 */ DECLARE @Ret Int DECLARE @Error Int = 0 DECLARE @BodyXML XML DECLARE @RequestGroupUID UniqueIdentifier DECLARE @User Varchar(75) = (SELECT LogonName From dbo.[User] Where UserID = @UserID) DECLARE @UserEmail nVarchar(200) = (SELECT Number From dbo.Number n Inner Join dbo.User_Number un ON un.NumberID = n.NumberID Where UserID = @UserID AND NumberTypeID = 4) DECLARE @ExtDMSys Int = Convert(Int, (SELECT Setting FROM dbo.ApplicationSetting Where SettingID = 50121)) DECLARE @ExtDMEnabled Char(1) = IsNull((SELECT Setting From dbo.ApplicationSetting Where SettingID = 50120), '0') DECLARE @FallbackCategory nVarchar(2000) = IsNull((SELECT Top 1 Setting From dbo.DocumentSetting Where AdminID = @AdminID and SettingID in (8,108,208) order by SettingID asc), ''); DECLARE @PageData PageData DECLARE @PageCount Int DECLARE @ThisPage Int = 1 DECLARE @PageSize Int DECLARE @RecordCount Int DECLARE @Pages Int DECLARE @Docs Table ( UID UniqueIdentifier ,URL nVarchar(255) ,DocumentID Int ,ExDocFlags Int ,Failed Int ,Ignore Int ) BEGIN TRANSACTION INSERT INTO @Docs SELECT UID = tab.col.value('DocGUID[1]', 'UniqueIdentifier') ,URL = CASE WHEN Left(d.FileName, 6) Not In('http:/', 'https:') --No URL's THEN tab.col.value('URL[1]', 'nVarchar(255)') ELSE d.FileName END ,DocumentID = d.DocumentID ,ExDocFlags = IsNull(d.Flags, 0) ,Failed = CASE WHEN tab.col.value('Failed[1]', 'nVarchar(5)') = 'true' THEN 1 ELSE 0 END ,Ignore = CASE WHEN Left(d.FileName, 6) Not In('http:/', 'https:') --No URL's THEN 0 ELSE 1 END FROM @DocumentXML.nodes('/Docs/Doc') as tab(col) INNER JOIN dbo.Document d ON d.UID = tab.col.value('DocGUID[1]', 'UniqueIdentifier') AND d.AdminID = @AdminID WHERE Right(d.FileName, 4) Not In('rvfn', 'rvab', 'rvem') --No internal documents --DEBUG select @@ROWCOUNT as FoundDocs; --DEBUG select * from @Docs; IF (Exists (select top 1 (1) from @Docs)) BEGIN SET @Error = @Error + @@Error --Do the paging stuff INSERT INTO @PageData (ObjectID) SELECT DocumentID FROM @Docs SELECT @PageSize = IsNull((SELECT Convert (Int, Setting) FROM dbo.ApplicationSetting Where SettingID = 2005040), 2) SELECT @RecordCount = Count(*) FROM @PageData SET @PageCount = @RecordCount / @PageSize IF @RecordCount > 0 AND @PageCount = 0 SET @PageCount = 1 IF @RecordCount > (@PageCount * @PageSize) SET @PageCount = @PageCount + 1 IF @DocFlags & 128 = 128 --eDocExtDMIsReady BEGIN --Set ready UPDATE @Docs SET ExDocFlags = IsNull(ExDocFlags,0) + 128 WHERE IsNull(ExDocFlags,0) & 128 = 0 --Unset up UPDATE @Docs SET ExDocFlags = IsNull(ExDocFlags,0) - 256 WHERE IsNull(ExDocFlags,0) & 256 <> 0 --Set up for documents to Ignore UPDATE @Docs SET ExDocFlags = IsNull(ExDocFlags,0) + 256 WHERE IsNull(ExDocFlags,0) & 256 = 0 AND Ignore = 1; --Unset failed UPDATE @Docs SET ExDocFlags = IsNull(ExDocFlags,0) - 1048576 WHERE IsNull(ExDocFlags,0) & 1048576 <> 0 UPDATE Document SET Flags = ExDocFlags ,UpdatedBy = @User ,UpdatedDate = GetDate() ,TS = NewID() FROM dbo.Document d INNER JOIN @Docs dt ON dt.DocumentID = d.DocumentID WHERE dt.Ignore = 0; --Mark Docs Ignored HTTP as Success and not Uploading UPDATE Document SET Flags = NULL ,Path = dt.URL ,IsUnderAdminRoot = Null ,UpdatedBy = @User ,UpdatedDate = GetDate() ,TS = NewID() FROM dbo.Document d INNER JOIN @Docs dt ON dt.DocumentID = d.DocumentID AND dt.Failed = 0 AND dt.Ignore = 1; SET @Error = @Error + @@Error END IF @DocFlags & 256=256 --eDocExtDMIsUp BEGIN --SUCCESS --Set up UPDATE @Docs SET ExDocFlags = IsNull(ExDocFlags,0) + 256 WHERE IsNull(ExDocFlags,0) & 256 = 0 AND Failed = 0 --Unset ready UPDATE @Docs SET ExDocFlags = IsNull(ExDocFlags,0) - 128 WHERE IsNull(ExDocFlags,0) & 128 <> 0 AND Failed = 0 --Unset failed UPDATE @Docs SET ExDocFlags = IsNull(ExDocFlags,0) - 1048576 WHERE IsNull(ExDocFlags,0) & 1048576 <> 0 AND Failed = 0 -- FAILED --Set failed UPDATE @Docs SET ExDocFlags = IsNull(ExDocFlags,0) + 1048576 WHERE IsNull(ExDocFlags,0) & 1048576 = 0 AND Failed = 1 --Unset ready UPDATE @Docs SET ExDocFlags = IsNull(ExDocFlags,0) - 128 WHERE IsNull(ExDocFlags,0) & 128 <> 0 AND Failed = 1 --Unset up UPDATE @Docs SET ExDocFlags = IsNull(ExDocFlags,0) - 256 WHERE IsNull(ExDocFlags,0) & 256 <> 0 AND Failed = 1 -- DOCUMENT UPDATE UPDATE Document SET Flags = ExDocFlags ,Path = dt.URL ,IsUnderAdminRoot = Null ,UpdatedBy = @User ,UpdatedDate = GetDate() ,TS = NewID() FROM dbo.Document d INNER JOIN @Docs dt ON dt.DocumentID = d.DocumentID AND Failed = 0 UPDATE Document SET Flags = ExDocFlags ,UpdatedBy = @User ,UpdatedDate = GetDate() ,TS = NewID() FROM dbo.Document d INNER JOIN @Docs dt ON dt.DocumentID = d.DocumentID AND Failed = 1 -- DOCUMENT DATA UPDATE UPDATE DocumentData SET URL = dt.URL FROM dbo.DocumentData d INNER JOIN @Docs dt ON dt.DocumentID = d.DocumentID SET @Error = @Error + @@Error END /* IF @DocFlags = 1048576 --eDocExtDMIsFailure BEGIN --Set failed UPDATE @Docs SET ExDocFlags = IsNull(ExDocFlags,0) + 1048576 WHERE IsNull(ExDocFlags,0) & 1048576 = 0 AND Failed = 1 --Unset ready UPDATE @Docs SET ExDocFlags = IsNull(ExDocFlags,0) - 128 WHERE IsNull(ExDocFlags,0) & 128 <> 0 AND Failed = 1 --Unset up UPDATE @Docs SET ExDocFlags = IsNull(ExDocFlags,0) - 256 WHERE IsNull(ExDocFlags,0) & 256 <> 0 AND Failed = 1 UPDATE Document SET Flags = ExDocFlags ,UpdatedBy = @User ,UpdatedDate = GetDate() ,TS = NewID() FROM dbo.Document d INNER JOIN @Docs dt ON dt.DocumentID = d.DocumentID SET @Error = @Error + @@Error UPDATE DocumentData SET URL = dt.URL FROM dbo.DocumentData d INNER JOIN @Docs dt ON dt.DocumentID = d.DocumentID SET @Error = @Error + @@Error END */ --DEBUG SELECT @Error as Error1; IF @Error = 0 BEGIN If @ExtDMSys = 1 AND @DocFlags & 128 = 128 --Union square BEGIN DECLARE @USQUDoc table ( DocumentID Int ,UID UniqueIdentifier ,Path nVarchar(255) ,Code nVarchar(100) ) INSERT INTO dbo.DocumentRequest (UID, Path, Code) SELECT dt.UID ,Path = CASE WHEN d.IsUnderAdminRoot = 1 THEN a.DocumentFolder + CASE WHEN d.Category Is Not Null THEN d.Category + '\' ELSE '' END + d.FileName ELSE d.Path END ,Code = acp.PropertyValue FROM @Docs dt INNER JOIN dbo.Document d ON d.DocumentID = dt.DocumentID INNER JOIN dbo.Admin a ON a.AdminID = d.AdminID LEFT OUTER JOIN dbo.repvw_AdminCustomProperty acp ON acp.AdminId = a.AdminID AND acp.CustomPropertyTypeID = 1209 END If @ExtDMSys >= 2 AND @DocFlags & 128 = 128 --eDocExtDMIsReady --Net Docs BEGIN SET @RequestGroupUID = NewID(); --Page the data WHILE @ThisPage <= @PageCount BEGIN SELECT @BodyXML = --Job ( SELECT ( SELECT 'JobGUID' = CONVERT(varchar(50),at.UID) FROM dbo.Admin at WHERE at.AdmiNID = @AdminID FOR XML PATH ('JobDetails'), TYPE --XSINIL ) , ( SELECT ------------------------------------------Docs ( SELECT ------------------------------------------Doc 'DocGUID' = CONVERT(varchar(50),d.UID) ,'JobGUID' = CONVERT(varchar(50),a.UID) ,'Path' = CASE WHEN IsUnderAdminRoot = 1 THEN ISNULL(d.Path, a.DocumentFolder + ISNULL(d.Category + '\','') + d.FileName) ELSE d.Path END ,'Category' = CASE WHEN CharIndex('\', d.Category) = 0 THEN d.Category ELSE Left(d.Category, CharIndex('\', d.Category) - 1) END ,'FullCategory' = d.Category ,'JobShortName' = a.ShortName ,'JobType' = at.ShortDescription ,'UserName' = @User ,'UserEmail' = @UserEmail ,'WebFlags' = d.WebFlags ,'Flags' = d.Flags ,'FallbackCategory' = @FallbackCategory FROM dbo.Document d INNER JOIN @Docs dt ON dt.DocumentID = d.DocumentID INNER JOIN @PageData pd ON pd.ObjectID = d.DocumentID INNER JOIN dbo.Admin a ON a.AdminID = d.AdminID INNER JOIN dbo.AdminType at ON at.AdminTypeId = a.AdminTypeID WHERE Ignore = 0 AND ID > ((@ThisPage * @PageSize) - @PageSize) AND ID <= (@ThisPage * @PageSize) FOR XML PATH ('Doc'), TYPE --XSINIL ) FOR XML PATH ('Docs'), TYPE --XSINIL ) FOR XML PATH ('Job'), TYPE --XSINIL ) IF (EXISTS (SELECT Top 1 (1) FROM dbo.Document d INNER JOIN @Docs dt ON dt.DocumentID = d.DocumentID INNER JOIN @PageData pd ON pd.ObjectID = d.DocumentID INNER JOIN dbo.Admin a ON a.AdminID = d.AdminID INNER JOIN dbo.AdminType at ON at.AdminTypeId = a.AdminTypeID WHERE Ignore = 0 AND ID > ((@ThisPage * @PageSize) - @PageSize) AND ID <= (@ThisPage * @PageSize) ) ) BEGIN EXEC @Ret = dbo.websp_TransferWebRequestCreate @Type_LookupID=108050, @AdminID=@AdminID, @BodyXML=@BodyXML, @UserID=@UserID, @IsDelayedRequest=1, @RequestGroupUID=@RequestGroupUID SET @Error = @Error + @Ret END; SET @ThisPage = @ThisPage + 1 END --END WHILE IF ( (@Error = 0) AND ( EXISTS (SELECT Top 1 (1) FROM dbo.Document d INNER JOIN @Docs dt ON dt.DocumentID = d.DocumentID INNER JOIN @PageData pd ON pd.ObjectID = d.DocumentID INNER JOIN dbo.Admin a ON a.AdminID = d.AdminID INNER JOIN dbo.AdminType at ON at.AdminTypeId = a.AdminTypeID WHERE Ignore = 0 ) ) ) BEGIN EXEC @Ret = dbo.websp_TransferWebRequestWatchCreate @RequestGroupUID=@RequestGroupUID SET @Error = @Error + @Ret END END END --DEBUG SELECT @Error as Error; IF @Error = 0 COMMIT TRANSACTION ELSE ROLLBACK TRANSACTION END --IF has @Docs rows to process RETURN @@ERROR GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO GRANT EXECUTE ON [dbo].[gensp_DocumentExtDMStatusUpdate] TO [insol2_users] GO --WWWWWWWWWWWWW if exists (select * from sysobjects where id = object_id(N'[dbo].[repsp_eLodge5602Payments]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[repsp_eLodge5602Payments] GO SET QUOTED_IDENTIFIER ON SET ANSI_NULLS ON GO CREATE PROCEDURE dbo.repsp_eLodge5602Payments @AdminID Int ,@AdminTypeID Int ,@AsAtDate DateTime ,@ShowDetail Bit = 0 ,@FromDate DateTime AS /* Author: Jonathan Osborne Date: 24/07/2017 Purpose: Notes: Revision History: exec dbo.repsp_eLodge5602Payments @AdminID=6927, @AdminTypeID=10, @AsAtDate='20190529' ,@FromDate='20180101' */ SET NOCOUNT ON DECLARE @PmtsA Table (CreditorClaimTypeID Int, IsFeg Bit, ControlValue Money, AmountPaid Money, Rate DECIMAL(16,2), EstRate DECIMAL(16,2) ) DECLARE @Pmts Table (CreditorClaimTypeID Int, IsFeg Bit, ControlValue Money, AmountPaid Money, Rate DECIMAL(16,2), EstRate DECIMAL(16,2) ) DECLARE @UnclEnts Table (EntryID Int) INSERT INTO @PmtsA (CreditorClaimTypeID, IsFeg, ControlValue, AmountPaid) SELECT CreditorClaimTypeID = CASE WHEN cct.CreditorClaimTypeID In(100) THEN 100 WHEN cct.CreditorClaimTypeID In(150) THEN 150 WHEN cct.CreditorClaimTypeID In(200) THEN 200 WHEN cct.CreditorClaimTypeID In(250,255) THEN 240 --WHEN cct.CreditorClaimTypeID In(255) THEN 255 WHEN cct.CreditorClaimTypeID In(270) THEN 270 WHEN cct.CreditorClaimTypeID In(280) THEN 280 WHEN cct.CreditorClaimTypeID In(400,600) THEN 400 WHEN cct.CreditorClaimTypeID In(500) THEN 500 ELSE 0 END ,IsFeg = CASE WHEN d.CreditorClaimTypeID = 10000 THEN 1 ELSE 0 END ,ControlValue = IsNull(Sum(CASE WHEN se.EntryType In (100,101) AND ISNULL(IsERAClaimAdmin,0) = 0 THEN (Net * (Direction * -1)) ELSE 0 END),0) ,AmountPaid = IsNull(Sum(CASE WHEN se.EntryType In (100,101) THEN 0 WHEN se.AdminTypeID <> @AdminTypeID THEN 0 ELSE (Net * Direction) END),0) --,se.EntryID FROM dbo.SubEntry se INNER JOIN dbo.CreditorClaim cc ON cc.AccountID = se.Account_AccountID AND cc.CreditorID = se.CreditorID INNER JOIN dbo.Creditor c ON c.CreditorID = cc.CreditorID INNER JOIN dbo.CreditorClaimType cct ON cct.CreditorClaimTypeID = cc.CreditorClaimTypeID LEFT OUTER JOIN dbo.Dividend d ON d.DividendID = se.CreditorDividendID WHERE se.ContextID = @AdminID AND IsNull(se.IsBatchPosting, 0) = 0 AND (se.EntryDate <= @AsAtDate) AND ((se.EntryDate >= @FromDate AND d.CreditorClaimTypeID = 10000) OR (NOT IsNull(d.CreditorClaimTypeID,0) = 10000)) AND ISNULL(c.NoClaimAdvised,0) = 0 AND (cc.RATAAmount <> 0 OR cc.AdvisedAmount <> 0 OR cc.ClaimedTotalAmount <> 0 OR cc.AdmittedAmount <> 0 OR cc.RejectedPriorityAmount <> 0 ) GROUP BY CASE WHEN cct.CreditorClaimTypeID In(100) THEN 100 WHEN cct.CreditorClaimTypeID In(150) THEN 150 WHEN cct.CreditorClaimTypeID In(200) THEN 200 WHEN cct.CreditorClaimTypeID In(250,255) THEN 240 --WHEN cct.CreditorClaimTypeID In(255) THEN 255 WHEN cct.CreditorClaimTypeID In(270) THEN 270 WHEN cct.CreditorClaimTypeID In(280) THEN 280 WHEN cct.CreditorClaimTypeID In(400,600) THEN 400 WHEN cct.CreditorClaimTypeID In(500) THEN 500 ELSE 0 END ,CASE WHEN d.CreditorClaimTypeID = 10000 THEN 1 ELSE 0 END INSERT INTO @Pmts (CreditorClaimTypeID, IsFeg, ControlValue, AmountPaid) SELECT CreditorClaimTypeID = CASE WHEN CreditorClaimTypeID In(100,150) THEN 100 WHEN CreditorClaimTypeID In(200) THEN 200 WHEN CreditorClaimTypeID In(240) THEN 240 --WHEN cct.CreditorClaimTypeID In(255) THEN 255 WHEN CreditorClaimTypeID In(270) THEN 270 WHEN CreditorClaimTypeID In(280) THEN 280 WHEN CreditorClaimTypeID In(400,600) THEN 400 WHEN CreditorClaimTypeID In(500) THEN 500 ELSE 0 END ,IsFeg ,Sum(ControlValue) ,Sum(AmountPaid) FROM @PmtsA GROUP BY CASE WHEN CreditorClaimTypeID In(100,150) THEN 100 WHEN CreditorClaimTypeID In(200) THEN 200 WHEN CreditorClaimTypeID In(240) THEN 240 --WHEN cct.CreditorClaimTypeID In(255) THEN 255 WHEN CreditorClaimTypeID In(270) THEN 270 WHEN CreditorClaimTypeID In(280) THEN 280 WHEN CreditorClaimTypeID In(400,600) THEN 400 WHEN CreditorClaimTypeID In(500) THEN 500 ELSE 0 END ,IsFeg INSERT INTO @UnclEnts SELECT DISTINCT EntryID FROM dbo.SubEntry se Inner Join dbo.Account acc On acc.AccountID = se.Account_AccountID WHERE se.ContextID = @AdminID AND acc.AccountUID = '{36C41188-1230-41E0-894F-5B9B9D8D6DB7}' INSERT INTO @Pmts (CreditorClaimTypeID, IsFeg, ControlValue, AmountPaid) SELECT CreditorClaimTypeID = -1 ,IsFeg = 0 ,ControlValue = 0 ,AmountPaid = IsNull(Sum(Net * Direction * -1),0) FROM dbo.SubEntry se Inner Join dbo.Account acc On acc.AccountID = se.Account_AccountID Inner Join dbo.AccountGroup ag On ag.AccountGroupID = acc.AccountGroupID Inner Join dbo.Shareholder s ON s.ShareholderID = se.ShareHolderID Left Outer Join @UnclEnts ue On ue.EntryID = se.EntryID Where se.Direction = -1 AND se.ContextID = @AdminID --AND se.AdminTypeID = @AdminTypeID AND IsNull(se.IsBatchPosting, 0) = 0 AND (se.EntryDate <= @AsAtDate) AND ue.EntryID Is Null IF Exists(SELECT * FROM @Pmts WHERE CreditorClaimTypeID = -1) UPDATE @Pmts SET ControlValue = IsNull((SELECT Sum(sg.DividendShares) from dbo.Shareholder s Inner Join dbo.Shareholding sg On sg.ShareholderID = s.ShareholderID Where s.AdminID = @AdminID), 0) FROM @Pmts p WHERE p.CreditorClaimTypeID = -1 UPDATE @Pmts SET Rate = CASE WHEN CreditorClaimTypeID = 100 THEN 0 ELSE (AmountPaid / ControlValue) * 100 END WHERE IsFeg = 0 AND ControlValue > 0 UPDATE @Pmts SET EstRate = 0 WHERE IsFeg = 0 UPDATE @Pmts SET AmountPaid = 0 WHERE AmountPaid Is Null UPDATE @Pmts SET Rate = 0 WHERE Rate Is Null --Work out the estimated return to each class DECLARE @FundsToPref Money DECLARE @FundsToPrefA Money DECLARE @ToApply Money EXECUTE dbo.repsp_CurrentPosition @AdminID=@AdminID,@AdminTypeID=@AdminTypeID,@AsAtDate=@AsAtDate,@AccountInClause=Null,@AsAccrual=0,@GetERVToPref=1,@FundsToPref=@FundsToPref OUTPUT IF @FundsToPref < 0 SET @FundsToPref = 0 SET @FundsToPrefA = @FundsToPref DECLARE @Claims Table(CreditorClaimTypeID Int, ControlValue Money, Applied Money) INSERT INTO @Claims (CreditorClaimTypeID, ControlValue) SELECT CreditorClaimTypeID ,ControlValue FROM @PmtsA WHERE IsFeg = 0 AND CreditorClaimTypeID >= 200 AND CreditorClaimTypeID < 400 INSERT INTO @Claims (CreditorClaimTypeID, ControlValue) SELECT CreditorClaimTypeID ,ControlValue FROM @PmtsA WHERE IsFeg = 0 AND CreditorClaimTypeID = 150 INSERT INTO @Claims (CreditorClaimTypeID, ControlValue) SELECT CreditorClaimTypeID ,ControlValue FROM @PmtsA WHERE IsFeg = 0 AND CreditorClaimTypeID >= 400 INSERT INTO @Claims (CreditorClaimTypeID, ControlValue) SELECT CreditorClaimTypeID ,ControlValue FROM @PmtsA WHERE IsFeg = 0 AND CreditorClaimTypeID = -1 UPDATE @Claims SET @ToApply = CASE WHEN ControlValue < @FundsToPref THEN ControlValue ELSE @FundsToPref END ,Applied = @ToApply ,@FundsToPref = @FundsToPref - @ToApply FROM @Claims c UPDATE @Pmts SET EstRate = CASE WHEN c.Applied > p.ControlValue THEN 100 ELSE (c.Applied / p.ControlValue) * 100 END FROM @Pmts p INNER JOIN @Claims c ON c.CreditorClaimTypeID = p.CreditorClaimTypeID WHERE NOT p.ControlValue = 0 UPDATE @PmtsA SET EstRate = CASE WHEN c.Applied > p.ControlValue THEN 100 ELSE (c.Applied / p.ControlValue) * 100 END FROM @PmtsA p INNER JOIN @Claims c ON c.CreditorClaimTypeID = p.CreditorClaimTypeID WHERE NOT p.ControlValue = 0 IF @ShowDetail = 0 BEGIN IF Not Exists(SELECT * from @Pmts Where CreditorClaimTypeID = 0) INSERT INTO @Pmts Values (0,0,0,0,0,0) UPDATE @Pmts SET ControlValue = @FundsToPrefA WHERE CreditorClaimTypeID = 0 AND IsFeg = 0 SELECT * from @Pmts END ELSE BEGIN SELECT CreditorClaimTypeID = 0 ,Description = 'Estimated funds available to priority claims' ,ControlValue = @FundsToPrefA ,Applied = Null ,EstRate = Null UNION SELECT c.CreditorClaimTypeID ,cc.Description ,c.ControlValue ,c.Applied ,p.EstRate FROM @Claims c LEFT OUTER JOIN @PmtsA p ON p.CreditorClaimTypeID = c.CreditorClaimTypeID AND p.IsFeg = 0 INNER JOIN dbo.CreditorClaimType cc ON cc.CreditorClaimTypeID = c.CreditorClaimTypeID END SET NOCOUNT OFF RETURN @@ERROR GO SET QUOTED_IDENTIFIER OFF SET ANSI_NULLS ON GO GRANT EXECUTE ON [dbo].[repsp_eLodge5602Payments] TO [insol2_users] GO -- * ************ --END OF 5.2.39 Cumulative -- * ************ Update DataCache Set CacheUID = NewID() Go