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		=	'<root><sql>pdfsp_Form_507<parameters>
							<parameter prompt="Enter the date this completed form must be returned by" datatype="7" sqlparameter="@RATAReturnDate"/>
							<parameter prompt="Are you providing the ROCAP Instructions to the director?" datatype="36" sqlparameter="@Instructions" options="Yes|No"/>
							<parameter prompt="Does the company have other places of business? if Yes amend the form after merging" datatype="36" sqlparameter="@OtherAddress" options="Yes|No"/>
							<parameter prompt="Does the company hold property on trust?" datatype="36" sqlparameter="@PropOnTrust" options="Yes|No"/>
							<parameter prompt="Is the company a trustee of a superannuation fund?" datatype="36" sqlparameter="@superftrustee" options="Yes|No"/>
							<parameter prompt="Have you provided full details what the company owes and owns?" datatype="36" sqlparameter="@A8" options="Yes|No"/>
							<parameter default="No" prompt="Are you preparing a Controllers'' report?" datatype="36" sqlparameter="@ControllersReport" options="Yes|No"/>
							<parameter default="No" prompt="Do you wish to prepare blank asset/creditor schedules?" datatype="36" sqlparameter="@AnnexuresBlank" options="Yes|No"/>
							</parameters></sql></root>'
							

 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		=	'<root><sql>pdfsp_Form_507<parameters>
							<parameter prompt="Enter the date this completed form must be returned by" datatype="7" sqlparameter="@RATAReturnDate"/>
							</parameters></sql></root>'


 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		=	'<root><sql>pdfsp_Form_507<parameters>
							<parameter prompt="Enter the date this completed form must be returned by" datatype="7" sqlparameter="@RATAReturnDate"/>
							<parameter prompt="Are you providing the ROCAP Instructions to the director?" datatype="36" sqlparameter="@Instructions" options="Yes|No"/>
							<parameter prompt="Does the company have other places of business? if Yes amend the form after merging" datatype="36" sqlparameter="@OtherAddress" options="Yes|No"/>
							<parameter prompt="Does the company hold property on trust?" datatype="36" sqlparameter="@PropOnTrust" options="Yes|No"/>
							<parameter prompt="Is the company a trustee of a superannuation fund?" datatype="36" sqlparameter="@superftrustee" options="Yes|No"/>
							<parameter prompt="Have you provided full details what the company owes and owns?" datatype="36" sqlparameter="@A8" options="Yes|No"/>
							<parameter default="No" prompt="Do you wish to prepare blank asset/creditor schedules?" datatype="36" sqlparameter="@AnnexuresBlank" options="Yes|No"/>
							</parameters></sql></root>'

 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                = '<root><sql>repsp_ROCAP_Annexure_A4<parameters>
							<parameter default="No" prompt="Do you wish to prepare blank asset/creditor schedules?" datatype="36" sqlparameter="@AnnexuresBlank" options="Yes|No"/>
							</parameters></sql></root>'
,@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		=	'<root><sql>pdfsp_Form_507<parameters>
							<parameter prompt="Enter the date this completed form must be returned by" datatype="7" sqlparameter="@RATAReturnDate"/>
							<parameter prompt="Are you providing the ROCAP Instructions to the director?" datatype="36" sqlparameter="@Instructions" options="Yes|No"/>
							<parameter prompt="Does the company have other places of business? if Yes amend the form after merging" datatype="36" sqlparameter="@OtherAddress" options="Yes|No"/>
							<parameter prompt="Does the company hold property on trust?" datatype="36" sqlparameter="@PropOnTrust" options="Yes|No"/>
							<parameter prompt="Is the company a trustee of a superannuation fund?" datatype="36" sqlparameter="@superftrustee" options="Yes|No"/>
							<parameter prompt="Have you provided full details what the company owes and owns?" datatype="36" sqlparameter="@A8" options="Yes|No"/>
							<parameter default="No" prompt="Do you wish to prepare blank asset/creditor schedules?" datatype="36" sqlparameter="@AnnexuresBlank" options="Yes|No"/>
							</parameters></sql></root>'

 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                = '<root><sql>repsp_ROCAP_Annexure_A5<parameters>
							<parameter default="No" prompt="Do you wish to prepare blank asset/creditor schedules?" datatype="36" sqlparameter="@AnnexuresBlank" options="Yes|No"/>
							</parameters></sql></root>'
,@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                = '<root><sql>pdfsp_Form_507<parameters>
							<parameter prompt="Enter the date this completed form must be returned by" datatype="7" sqlparameter="@RATAReturnDate"/>
							<parameter prompt="Are you providing the ROCAP Instructions to the director?" datatype="36" sqlparameter="@Instructions" options="Yes|No"/>
							<parameter prompt="Does the company have other places of business? if Yes amend the form after merging" datatype="36" sqlparameter="@OtherAddress" options="Yes|No"/>
							<parameter prompt="Does the company hold property on trust?" datatype="36" sqlparameter="@PropOnTrust" options="Yes|No"/>
							<parameter prompt="Is the company a trustee of a superannuation fund?" datatype="36" sqlparameter="@superftrustee" options="Yes|No"/>
							<parameter prompt="Have you provided full details what the company owes and owns?" datatype="36" sqlparameter="@A8" options="Yes|No"/>
							<parameter default="No" prompt="Do you wish to prepare blank asset/creditor schedules?" datatype="36" sqlparameter="@AnnexuresBlank" options="Yes|No"/>
							</parameters></sql></root>'
,@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                = '<root><sql>repsp_ROCAP_Annexure_A6<parameters>
							<parameter default="No" prompt="Do you wish to prepare blank asset/creditor schedules?" datatype="36" sqlparameter="@AnnexuresBlank" options="Yes|No"/>
							</parameters></sql></root>'
,@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                = '<root><sql>pdfsp_Form_507<parameters>
							<parameter prompt="Enter the date this completed form must be returned by" datatype="7" sqlparameter="@RATAReturnDate"/>
							<parameter prompt="Are you providing the ROCAP Instructions to the director?" datatype="36" sqlparameter="@Instructions" options="Yes|No"/>
							<parameter prompt="Does the company have other places of business? if Yes amend the form after merging" datatype="36" sqlparameter="@OtherAddress" options="Yes|No"/>
							<parameter prompt="Does the company hold property on trust?" datatype="36" sqlparameter="@PropOnTrust" options="Yes|No"/>
							<parameter prompt="Is the company a trustee of a superannuation fund?" datatype="36" sqlparameter="@superftrustee" options="Yes|No"/>
							<parameter prompt="Have you provided full details what the company owes and owns?" datatype="36" sqlparameter="@A8" options="Yes|No"/>
							<parameter default="No" prompt="Do you wish to prepare blank asset/creditor schedules?" datatype="36" sqlparameter="@AnnexuresBlank" options="Yes|No"/>
							</parameters></sql></root>'
,@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                = '<root><sql>repsp_ROCAP_Annexure_A7<parameters>
							<parameter default="No" prompt="Do you wish to prepare blank asset/creditor schedules?" datatype="36" sqlparameter="@AnnexuresBlank" options="Yes|No"/>
							</parameters></sql></root>'
,@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                = '<root><sql>repsp_ROCAP_Annexure_B2<parameters></parameters></sql></root>'
,@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                = '<root><sql>repsp_ROCAP_Annexure_B3<parameters></parameters></sql></root>'
,@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                = '<root><sql>repsp_ROCAP_Annexure_B21<parameters></parameters></sql></root>'
,@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                = '<root><sql>repsp_ROCAP_Annexure_B25<parameters></parameters></sql></root>'
,@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		=	'<root><sql>pdfsp_Form_507_Blank<parameters>
							<parameter prompt="Enter the date this completed form must be returned by" datatype="7" sqlparameter="@RATAReturnDate"/>
							<parameter default="Yes" prompt="Who to contact with queries related to this form?" datatype="13" sqlparameter="@ExtAdminFields" sql="SELECT 1,''Do not fill the external administrators fields'' UNION Select 2, correspondencename FROM dbo.genvw_User u INNER JOIN genvw_Admin a ON a.ReportingUserId = u.UserId WHERE a.AdminID = @AdminID UNION Select 3, correspondencename FROM dbo.genvw_User u INNER JOIN genvw_Admin a ON a.ReportingUserId1 = u.UserId WHERE a.AdminID = @AdminID"/>
							</parameters></sql></root>'				

 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 =
			'
			<script type="text/javascript">
			function open_win() 
			{
			window.open("http://twitter.com/#!/MYOBInsolvency");
			}
			</script>
			'

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 = '<b>' +  @GreetingExt +  '</b>'
	
	IF dbo.uFnSystemLocation() = 1
		SELECT @GreetingExt = @GreetingExt + ', welcome to MYOB Insolvency.'
		
	ELSE
		SELECT @GreetingExt = @GreetingExt + ', welcome to insol6.'


	--IF @IsAdminUser = 1
	--	SELECT @GreetingExt = @GreetingExt + '&emsp;' + 'Active users: ' + convert(varchar(max),@CountActiveUsers)


IF @NoDetail = 1

	BEGIN
	
	SELECT @Output = @Greeting + @GreetingExt
	SELECT @Output = @Output + '<P>Loading data, please wait.</P>'
	
	END
	
ELSE

	BEGIN

	SELECT @Output = dbo.uFnHomePageNavBar (@UserID)		 

	SELECT @Output = @Output 
						+ '<P></P>'
						
						--+'<div align = "right" >'
						--			+ '<a href="' + @CurrentVersionURL + '">'
						--			+ '<img src="'+ @CurrentVersionImageSrc + '" style=border: 0 '
						--			+ 'alt="Software Version Image" >'
						--+'</div>'

						+ '<table class ="Main">'
							+ '<tr>'
								+ '<td>'
								+ @Greeting 
								+ @GreetingExt 
								+ '</td>'
							+ '</tr>'

							--+ '</tr>'
							--	+ '<td>'
							--		+ '<a href="' + @CurrentVersionURL + '">'
							--		+ '<img src="'+ @CurrentVersionImageSrc + '" style=border: 0'
							--		+ 'alt="Software Version Image" >'
							--	+ '</td>'
							--+ '</tr>'
							
						+ '</table>' 

	SELECT @Output = @Output + '<P></P>' 


	
	SELECT @Output = @Output + dbo.uFnHomePageTableHeadingToDo(@UserID,@MenuItem,null)  
	IF @MenuItem =  1
		BEGIN
			SELECT @Output = @Output + '<a href="' + @CurrentVersionURL + '">' + '<img src="'+ @CurrentVersionImageSrc + '" style=border: 0 ' + 'alt="Software Version Image" >'
			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 + '<p></p>'

	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) --+ '<P>' + '</P>'
			SELECT @Output = @Output + dbo.uFnHomePageBankOverdraft(@UserID) 
		--	SELECT @Output = @Output + dbo.uFnHomePageBankLink(@UserID) 
		END
	SELECT @Output = @Output + '<p></p>'

	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 + '<p></p>'

	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 + '<p></p>'	

	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 + '<p></p>'

	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 + '<p></p>'

	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 + '<p></p>'	

	--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 + '<p></p>'	

	--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 + '<P>'
				+ '<a href="PROC:dbo.repsp_HomePageChart' + ' @UserID ='  + Ltrim(Rtrim(Str(@UserID))) + '">' + 'chart fees' + '</a>' 
								+'</P>'				
	



	Notifications event
	IF EXISTS(Select * FROM dbo.User_Role WHERE UserID = @UserID AND RoleID = 1)
		SELECT @Output = @Output + '<P>Click <a href="EVENT:ConfigNotifications">here</a> to configure notifications</P>'

	SELECT @Output = @Output +
	
		'
		<ul id="menu-bottom"> 
		<li id="nav_1"><a href="PROC:dbo.repsp_HomePageSupport @UserID=' + CONVERT(VARCHAR,@UserID) + '" class="Support" title="Support" ></a></li>
		<li id="nav_2"><a href="PROC:dbo.repsp_HomePageCommunity @UserID=' + CONVERT(VARCHAR,@UserID) + '" class="Community" title="Community"></a></li>
		<li id="nav_3"><a href="PROC:dbo.repsp_HomePageResources @UserID=' + CONVERT(VARCHAR,@UserID) + '" class="Resources" title="Resources"></a></li>
		</ul>
		<div id="menu-bottom"></div>
		'

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 + '<P>Keep abreast of the latest updates by following us on twitter  
								<a href=''http://twitter.com/#!/MYOBInsolvency''><img height="16" width="16" src="http://twitter-badges.s3.amazonaws.com/t_mini-b.png" alt="Twitter" title="Twitter" /></a>
								</P>'

	 --google
	 SELECT @Output = @Output + '<P>Keep abreast of the latest updates by following us on   
								<a href=''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''>google</a>
								</P>'
*/


SET @HTML = 
+
'<HTML>
<HEAD>'
				--+ @JavaHeader
				+ dbo.uFnHomePageStyleSheet() 
				
				+	'</HEAD>'

+'<script type="text/javascript" src="http://www.members.iinet.net.au/~exalttech/Charts/FusionCharts.js">
</script>' 
				
				+	'<BODY id="home_page">'
					+ @Output
					+'</BODY>
			
			</HTML>'

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 
				+ '</td><td>' 
				+ cast( Name as varchar(100) ) 
				+ '</td><td>' 
				+ cast( BatchType as varchar(100) ) 
				+ '</td><td>' 
				+ cast(Batches as varchar(100) )
	
	from (

			SELECT ShortName  = '<a href="EVENT:Admin=' + Ltrim(Rtrim(Str(a.AdminID))) + '">' + a.ShortName + '</a>'
					,Name = a.Name 
					,BatchType = '<a href="EVENT:stpnotsubmitted=' + Ltrim(Rtrim(Str(a.AdminID))) + '">' + 'STP unsubmitted' 
					,Batches = '<a href="EVENT:stpnotsubmitted=' + Ltrim(Rtrim(Str(a.AdminID))) + '">' +  Ltrim(Rtrim(Str(IsNull(Count(b.BatchID),0)))) + '</a>'
			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 =		'<HTML>
				<HEAD>'
				+ dbo.uFnHomePageStyleSheet() +
				+ dbo.uFnHomePageTableSorter(4,1) +
				'
				</HEAD>
				<BODY id="batch_page">'	
				
					+ dbo.uFnHomePageNavBar (@UserID) 
					+ dbo.uFnHomePageTableHeadingSTP(@UserID,0,'STP Batches Awaiting Submission') 
				
					--'<H2><a href="PROC:dbo.repsp_HomePage @UserID =' + Ltrim(Rtrim(Str(@UserID))) + '">Home</a> \ 
					--<a href="PROC:dbo.repsp_HomePageBatches @UserID =' + Ltrim(Rtrim(Str(@UserID))) + '">Unposted Batches Listing</a></H2>
					+ '<br><br><br>'
					--+ '<br>Click a header cell to sort on that column. <br><br>'

						+ '<table class="sortable" id="table-sortable" cellspacing="0">'
						+ '<thead>'
						+ '<tr><th abbr="link_column">Short Name</th>'
						+ '<th>Name</th>'
						+ '<th abbr="link_column">Batch Type</th>'
						+ '<th abbr="link_column">No. of Batches</th></tr></thead>'
						  + replace( replace( IsNull(@Output,''), '&lt;', '<' ), '&gt;', '>' ) 
						  + '<table>
				  </BODY>
				  </HTML>'



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 = 
		
				'<table class ="main">
				<tr>

					<td class ="COL1" width = "120"><strong>To Submit</strong></td>'
--					+'<td class ="colspacer"></td>'
					+ '<td>Batches with payroll events requiring submission to STP '
					+ ' <a href="PROC:repsp_HomePageSTPUnsubmitted @UserID=' + Ltrim(Rtrim(Str(@UserID))) 
					--+ ',@FromDate=''' + Convert(Varchar(8), @Today, 112) 
					+ '">' 
					+ Ltrim(Rtrim(Str(@TotCount))) 
					+ CASE WHEN @TotCount = 1 THEN ' item' ELSE ' items' END
					+ '</a> '
				 
			   +'</tr>
			</table>
			'

	ELSE
	
		SELECT	@Ret = 
				'<table class ="main">'
					+ '<tr>'
						+ '<td class ="COL1" width = "120"><strong>To Submit</strong></td>'
						+ '<td>There isn''t any STP batches awaiting submission.</td>'
					+ '</tr>'
				+'</table>'


	SELECT @Ret = '<P>' + IsNull(@Ret,'') + '</P>'

	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'<Docs><Doc><DocGUID>CDE1C108-61CC-41C6-B057-0CF0AA6B2B4C</DocGUID></Doc></Docs>',128,5623,1

Select * from TransferWebRequest



--Success
exec gensp_DocumentExtDMStatusUpdate N'<Docs><Doc><DocGUID>CDE1C108-61CC-41C6-B057-0CF0AA6B2B4C</DocGUID><URL>https://www.google.com.au/?gws_rd=ssl</URL></Doc></Docs>',@DocFlags=256,@AdminID=5623,@UserID=1

--Failure


exec gensp_DocumentExtDMStatusUpdate N'<Docs><Doc><DocGUID>CDE1C108-61CC-41C6-B057-0CF0AA6B2B4C</DocGUID><URL>Insert error message here</URL><Failed>true</Failed></Doc></Docs>',@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