bKapXPGr

1USE [C211920]
2GO
3/****** Object:  StoredProcedure [dbo].[Rpt_NineFingerModule_GenerateData]    Script Date: 19-09-2019 08:15:36 ******/
4SET ANSI_NULLS ON
5GO
6SET QUOTED_IDENTIFIER ON
7GO
8--exec Rpt_NineFingerModule 1, 'AMD', '4', '2019', '6', '2019', 'M', 'Lakhs', 'N', 'S'
9ALTER PROCEDURE [dbo].[Rpt_NineFingerModule_GenerateData]
10	@ReportId int, 
11	@FromMonth varchar(10), 
12	@FromYear varchar(5), 
13	@ToMonth varchar(10), 
14	@ToYear varchar(5),
15	@UserId int=0
16AS
17BEGIN
18	-- SET NOCOUNT ON added to prevent extra result sets from
19	-- interfering with SELECT statements.
20	SET NOCOUNT ON;
21
22	declare @DivisionCode varchar(20), @AllTogether char(1), @DetailsType char(1), @ReportType char(1)
23	set @AllTogether='N'
24	set @DetailsType='S'
25	set @ReportType='M'
26
27	declare @DivisionNumber float
28	set @DivisionNumber=100000
29
30	set @FromMonth=DATENAME("M",@FromMonth+'/01/2019')
31	set @ToMonth=DATENAME("M",@ToMonth+'/01/2019')
32
33	declare @MPDBName varchar(50), @MPBranchId int
34	set @MPDBName=(select value from CompanyParameter where CompanyParameterId=125)
35	
36	declare @BranchQry nvarchar(max), @qry nvarchar(max)
37	if @DivisionCode<>''
38	BEGIN
39		set @BranchQry='select @MPBranchId=b.BranchMasterId from '+@MPDBName+'.dbo.BranchMaster as b 
40					where b.Deleted=0 and b.ERPBranchId=(select div.DivisionMasterId from vwDivision as div where div.DivisionCode='''+@DivisionCode+''')'
41		exec sp_executesql @BranchQry,N'@MPBranchId bigint Output', @MPBranchId output
42	END
43
44	declare @CorporateBranchCode varchar(20)
45	set @CorporateBranchCode=(select DivisioNCode from vwDivision where DivisionName like '%CORPORATE%')
46	set @CorporateBranchCode=ISNULL(@CorporateBranchCode,'')
47
48	--UTILITY VARIABLES
49	Declare @FinancialStart_year varchar(50), @FinancialEnd_year varchar(50),@GLCode varchar(50), @FinancialStart_Month varchar(50), @FinancialEnd_Month varchar(50), 
50			@FinancialYearStart datetime, @FinancialYearEnd datetime, @col varchar(200),@col_month varchar(25), @col_Year varchar(25), @FinancialYearStart_from Varchar(50), 
51			@FinancialYearEnd_to Varchar(50), @amount varchar(50)='0', @targetCol varchar(20), @cntr int, @Targetamount varchar(50)='0', @amount_total money= 0,
52			@Targetamount_total money= 0, @SerFromDate Varchar(50) , @SerToDate Varchar(50), @Count varchar(20) =0, @SCount varchar(20) =0, @Sitecode varchar(20) =0, 
53			@MaxMonthName varchar(50), @MaxYear varchar(50), @collectionAmount money, @collectionAmount_total money=0
54	Declare @tempAmt money, @tempAmtTwo money
55
56	if @ReportId IN(6,7,8,15)  or @AllTogether='Y'
57	BEGIN
58		declare @Key varchar(max),@TaxName varchar(max),@TaxColName varchar(max),@TaxPlusColName varchar(max),@TaxSumColName varchar(max),
59			@BalTaxAmount varchar(max), @GrossTotal varchar(max),@SerPartyGLCode varchar(50)=''
60		set @SerFromDate=''
61		--Select @SerFromDate=Convert (Date,FinancialYearStart,23),@SerToDate=convert(Date,FinancialYearEnd,23)
62		--from Accounts_DEM.dbo.CompanyFinancialYearMaster where Deleted=0 and FinancialYearCode=DB_Name()
63
64		Create table #temp1_Outstand([Branch Code] varchar(10),[Branch Name] varchar(100),[Bill No.] varchar(30),[Bill Date] datetime,[Bill Month] varchar(15),
65			[Bill Year] varchar(5),[Party Code] varchar(20),[Party Name] varchar(500),[Location Code] varchar(100),[Location Name] varchar(500))
66
67		set @qry=''
68		set @Key=''
69		set @TaxName=''
70		set @TaxColName=''
71		set @TaxPlusColName=''
72		set @TaxSumColName=''
73		set @BalTaxAmount=''
74		set @GrossTotal='0'
75		
76		Select @Key = @Key + isnull([Key],'') from InvMain i with (NOLOCK) where i.Deleted=0 group by [Key]
77		declare cur cursor for 
78			Select GLCode from SalesRegister with (NOLOCK) where GLCode in (Select * from fnSplit((@Key),'--')) order by Sequence
79		open cur
80		fetch next from cur into @GLCode
81		while @@FETCH_STATUS=0
82		BEGIN
83			exec('ALTER TABLE #temp1_Outstand ADD [' + @GLCode + '] numeric(22,2)')
84			set @TaxName = @TaxName + ',Convert(numeric(22,2),(isnull([' + @GLCode + '],0)*convert(numeric(22,6),(case ca.Total when 0 then 0 else (c.Amount/ca.Total)*100 end)))/100) as ['+@GLCode+']'
85			set @TaxColName = @TaxColName + ',['+ @GLCode + ']'
86			set @TaxPlusColName = @TaxPlusColName + ' + ['+ @GLCode + ']'
87			set @TaxSumColName = @TaxSumColName + ',sum(['+ @GLCode + ']) as ['+ @GLCode +']'
88			set @GrossTotal = @GrossTotal + ' + sum(['+ @GLCode + '])'
89			
90			set @BalTaxAmount = @BalTaxAmount + ',sum(Convert(numeric(22,2),(isnull([' + @GLCode + '],0)* ((case isnull([Bill Amount],0) when 0 then 0 else (convert(numeric(22,2),convert(numeric(22,2),[Bill Amount]) - (ISNULL([Cheque Amount],0) + ISNULL(TDS,0)))/convert(numeric(22,2),[Bill Amount])) end) *100))/100)) as ['+@GLCode+']'
91
92			fetch next from cur into @GLCode
93		END
94		close cur
95		deallocate cur
96
97		ALTER TABLE #temp1_Outstand ADD [Bill Amount] numeric(22,2), [Cheque Amount] numeric(22,2), TDS numeric(22,2), [Received Date] datetime, OfficeTypeName Varchar(50)
98
99		--isnull(dt.GLCode,''''),isnull(dt.GLDescription,'''')
100		set @qry= 'insert into #temp1_Outstand
101		Select cm.DivisionCode,d.DivisionName,c.DocumentNo,c.Pop,
102			case isnull(im.SelMonth,'''') when '''' then DATENAME(MONTH,c.Pop) else im.SelMonth end as SelMonth,
103			case isnull(im.SelYear,'''') when '''' then DATEPART(YEAR,c.Pop) else im.SelYear end as SelYear,
104			case when '''+@SerPartyGLCode+'''='''' then convert(varchar(20),isnull(cg.ClientGroupID,0)) else isnull(dt.GLCode,'''') end,
105			case when '''+@SerPartyGLCode+'''='''' then isnull(cg.ClientGroupName,'''') else isnull(dt.GLDescription,'''') end,
106			c.CostCentreCode as CCCode,cm.Description as CCName
107			'+@TaxName+',Convert(numeric(22,2),(t.Amount*convert(numeric(22,6),(case ca.Total when 0 then 0 else (c.Amount/ca.Total)*100 end)))/100) as InvBreakupAmt,'
108		
109		set @qry= @qry + 'Convert(numeric(22,2),(isnull((Select sum(r.Amount) from RefNoTxn r with (NOLOCK) where r.RefDocumentNo=t.DocumentNo and r.Deleted=0
110					and ('''+@SerFromDate+'''='''' OR exists(select tt.Date from Transactions as tt with(NOLOCK) where tt.Deleted=0 and tt.DocumentNo=r.DocumentNo and tt.Date<='''+@SerFromDate+'''))),0)
111					*Convert(numeric(22,6),(case ca.Total when 0 then 0 else (c.Amount/ca.Total)*100 end)))/100) as ChqAmount,
112				Convert(numeric(22,2),(isnull((Select sum(r.TDSAmount) from RefNoTxn r with (NOLOCK) where r.RefDocumentNo=t.DocumentNo and r.Deleted=0
113					and ('''+@SerFromDate+'''='''' OR exists(select tt.Date from Transactions as tt with(NOLOCK) where tt.Deleted=0 and tt.DocumentNo=r.DocumentNo and tt.Date<='''+@SerFromDate+'''))),0)
114					*Convert(numeric(22,6),(case ca.Total when 0 then 0 else (c.Amount/ca.Total)*100 end)))/100) as TDSAmt,'
115
116		set @qry= @qry + '(Select top 1 r.Pop from RefNoTxn r with (NOLOCK) where r.DocumentNo not like ''SA___RR%'' and r.RefDocumentNo=t.DocumentNo and r.Deleted=0 order by r.RefNoTxnID desc) as ReceivedDate,
117		OTM.OfficeTypeName
118			from Transactions t with (NOLOCK) 
119				left outer join InvMain im with (NOLOCK) on im.Deleted=0 and im.DocNo=t.DocumentNo	
120				left outer join SalDet s with (NOLOCK) on s.Deleted=0 and s.DocNo=t.DocumentNo
121				Inner join DebtInd dt with (NOLOCK) on dt.Deleted=0 and dt.GLCode=t.GLCode
122				left outer join ClientGroup cg with (NOLOCK) on cg.Deleted=0 and dt.ClientGroupID=cg.ClientGroupID
123				left outer join ['+@MPDBName+'].dbo.ClientGroupMaster cgm with (NOLOCK) on cgm.Deleted=0 and cg.PayMaxClientGroupID=cgm.ClientGroupMasterID
124				Inner join CCTxn c with (NOLOCK) on c.Deleted=0 and t.DocumentNo=c.DocumentNo 
125				Inner join CostCentreMaster cm with (NOLOCK) on cm.Deleted=0 and cm.CostCentreCode=c.CostCentreCode
126				--Left Outer Join CCSitesMarking as ccs with (NOLOCK) on ccs.Deleted=0 and ccs.CostCentreCode=cm.CostCentreCode
127				Left Outer Join ['+@MPDBName+'].dbo.SiteMaster as sm with (NOLOCK) on sm.Deleted=0 and sm.SiteCode=cm.CostCentreCode
128				Left Outer Join ['+@MPDBName+'].dbo.ServiceTypeMaster as stm with (NOLOCK) on stm.Deleted=0 and stm.ServiceTypeID=sm.ServiceTypeID
129				Inner join vwDivision d on cm.DivisionCode=d.DivisionCode
130				left outer join ['+@MPDBName+'].dbo.OfficeTypeMaster OTM with (NOLOCK) on OTM.Deleted=0 and OTM.OfficeTypeMasterID=cgm.OfficeType
131				cross apply (Select convert(numeric(22,6),sum(cc.Amount)) as Total from CCTxn cc with (NOLOCK) where cc.Deleted=0 and cc.DocumentNo=c.DocumentNo and cc.GLCode=c.GLCode group by cc.DocumentNo,cc.GLCode) ca
132			where t.Deleted=0 and t.CCType=''D'' and t.GLType=''D'' and t.Amount>0
133				and (cm.DivisionCOde='''+@DivisionCode+''' or '''+@DivisionCode+'''='''')
134				and (t.Amount<>Convert(numeric(22,2),(isnull((Select sum(r.Amount+isnull(r.TDSAmount,0)) from RefNoTxn r with (NOLOCK) where r.RefDocumentNo=t.DocumentNo and r.Deleted=0
135					and ('''+@SerFromDate+'''='''' OR exists(select tt.Date from Transactions as tt with(NOLOCK) where tt.Deleted=0 and tt.DocumentNo=r.DocumentNo and tt.Date<='''+@SerFromDate+'''))),0)))
136					)'
137		
138		--if @ReportId=15
139		--	select @qry
140
141		exec(@qry)
142
143		--select  * from #temp1_Outstand
144
145		Select [Branch Code],[Branch Name], [Bill No.],[OfficeTypeName], convert(varchar(20),[Bill Date],106) as [Bill Date],[Bill Month],[Bill Year],[Party Name],[Location Code],
146			[Location Name],[Bill Amount] as [Gross Amount],convert(numeric(22,2),[Bill Amount] - ([Cheque Amount] + TDS)) as [Pending Amount]
147		into  #temp1_Final from #temp1_Outstand 
148		where ([Bill Amount] - ([Cheque Amount] + TDS)) <> 0 and ([Bill Date] <=@SerFromDate or @SerFromDate='') 
149
150		--select * from #temp1_Final
151	END
152
153	select distinct(GLCode) into #temp from CCTxn with(NOLOCK) where LEFT(DocumentNo,2)='SA' and Deleted=0 
154	
155	if @ReportId=1 or @AllTogether='Y' --BRANCH SUMMARY
156	BEGIN
157		if @DetailsType='S'
158		BEGIN
159			if ISNULL(@MPDBName,'')<>''
160			BEGIN
161				print 'A'
162				Delete from Accounts_DEM.dbo.NineFinger_BranchSummary where CompanyCode=left(DB_NAME(),3) and FYCode=DB_NAME() and BranchCode=@DivisionCode
163				set @qry='
164				Insert into Accounts_DEM.dbo.NineFinger_BranchSummary(CompanyCode, FYCode, BranchCode, FromMonth, FromYear, ToMonth, ToYear, CreatedBy, BranchName, 
165					EstablishmentDate, BranchBillingForTheMonth, TotalBillingForTheMonth, BusinessShare, BranchHeadName, TotalBranchBackOfficeEmployees, 
166					TotalBackOfficeEmployees, BackOfficeEmployeePercentage, TotalBranchFrontOfficeEmployees, TotalFrontOfficeEmployees, FrontOfficeEmployeePercentage)
167				select left(db_name(),3), db_name(), d.DivisionCode, '''+@FromMonth+''','''+@FromYear+''','''+@ToMonth+''','''+@ToYear+''', 
168					'+convert(varchar(20),@UserId)+', BM.BranchName, convert(varchar(20),BranchStartDate,106) as EstablishmentDate, 
169							convert(numeric(22,2),ROUND((select sum(Convert(numeric(22,2),(tr.Amount*convert(numeric(22,6),(case ca.Total when 0 then 0 else (cctb.Amount/ca.Total)*100 end)))/100))
170								from CCTxn as cctb with(NOLOCK) 
171									INNER JOIN Transactions as tr with(NOLOCK) ON tr.Deleted=0 and tr.GLtype=''D'' and cctb.DocumentNo=tr.DocumentNo
172									INNER JOIN InvMain as im with(NOLOCK) ON im.Deleted=0 and im.DocNo=tr.DocumentNo
173									inner join costcentremaster as ccm with(NOLOCK) ON cctb.CostCentreCode = ccm.CostCentreCode
174									cross apply (Select convert(numeric(22,6),sum(cc.Amount)) as Total from CCTxn cc with (NOLOCK) 
175										where cc.Deleted=0 and cc.DocumentNo=cctb.DocumentNo and cc.GLCode=cctb.GLCode group by cc.DocumentNo,cc.GLCode) ca
176								where cctb.Deleted=0 and ccm.deleted=0 and tr.TransactionType=''SA'' and im.SelMonth='''+@ToMonth+''' and im.SelYear='''+@ToYear+''' 
177										and ccm.DivisionCode=d.DivisionCode) / '+convert(varchar(10),@DivisionNumber)+',2)) as BranchBillingForTheMonth,
178						
179							convert(numeric(22,2), ROUND((select sum(Convert(numeric(22,2),(tr.Amount*convert(numeric(22,6),(case ca.Total when 0 then 0 else (cctb.Amount/ca.Total)*100 end)))/100))
180								from CCTxn as cctb with(NOLOCK) 
181									INNER JOIN Transactions as tr with(NOLOCK) ON tr.Deleted=0 and tr.GLtype=''D'' and cctb.DocumentNo=tr.DocumentNo
182									INNER JOIN InvMain as im with(NOLOCK) ON im.Deleted=0 and im.DocNo=tr.DocumentNo
183									inner join costcentremaster as ccm with(NOLOCK) ON cctb.CostCentreCode = ccm.CostCentreCode
184									cross apply (Select convert(numeric(22,6),sum(cc.Amount)) as Total from CCTxn cc with (NOLOCK) 
185										where cc.Deleted=0 and cc.DocumentNo=cctb.DocumentNo and cc.GLCode=cctb.GLCode group by cc.DocumentNo,cc.GLCode) ca
186								where cctb.Deleted=0 and ccm.deleted =0 and tr.TransactionType=''SA'' and im.SelMonth='''+@ToMonth+''' and im.SelYear='''+@ToYear+''')
187									 / '+convert(varchar(10),@DivisionNumber)+',2)) as TotalBillingForTheMonth,
188
189							case when (select sum(Convert(numeric(22,2),(tr.Amount*convert(numeric(22,6),(case ca.Total when 0 then 0 else (cctb.Amount/ca.Total)*100 end)))/100))
190									from CCTxn as cctb with(NOLOCK)
191										INNER JOIN Transactions as tr with(NOLOCK) ON tr.Deleted=0 and tr.GLtype=''D'' and cctb.DocumentNo=tr.DocumentNo
192										INNER JOIN InvMain as im with(NOLOCK) ON im.Deleted=0 and im.DocNo=tr.DocumentNo
193										inner join costcentremaster as ccm with(NOLOCK) ON cctb.costcentrecode = ccm.costcentrecode
194										cross apply (Select convert(numeric(22,6),sum(cc.Amount)) as Total from CCTxn cc with (NOLOCK) 
195											where cc.Deleted=0 and cc.DocumentNo=cctb.DocumentNo and cc.GLCode=cctb.GLCode group by cc.DocumentNo,cc.GLCode) ca
196									where cctb.Deleted=0 and ccm.deleted = 0 and tr.TransactionType=''SA'' 
197										and im.SelMonth='''+@ToMonth+''' and im.SelYear='''+@ToYear+''')=0 then 0 else 
198							convert(numeric(22,2), Round(((select sum(Convert(numeric(22,2),(tr.Amount*convert(numeric(22,6),(case ca.Total when 0 then 0 else (cctb.Amount/ca.Total)*100 end)))/100))
199								from CCTxn as cctb with(NOLOCK) 
200									INNER JOIN Transactions as tr with(NOLOCK) ON tr.Deleted=0 and tr.GLtype=''D'' and cctb.DocumentNo=tr.DocumentNo
201									INNER JOIN InvMain as im with(NOLOCK) ON im.Deleted=0 and im.DocNo=tr.DocumentNo
202									inner join costcentremaster as ccm with(NOLOCK) ON cctb.costcentrecode = ccm.CostCentreCode
203									cross apply (Select convert(numeric(22,6),sum(cc.Amount)) as Total from CCTxn cc with (NOLOCK) 
204										where cc.Deleted=0 and cc.DocumentNo=cctb.DocumentNo and cc.GLCode=cctb.GLCode group by cc.DocumentNo,cc.GLCode) ca
205								where cctb.Deleted=0 and ccm.deleted = 0 and tr.TransactionType=''SA'' and im.SelMonth='''+@ToMonth+''' and im.SelYear='''+@ToYear+''' 
206										and ccm.DivisionCode=d.DivisionCode)  
207								/ 
208								(select sum(Convert(numeric(22,2),(tr.Amount*convert(numeric(22,6),(case ca.Total when 0 then 0 else (cctb.Amount/ca.Total)*100 end)))/100))
209									from CCTxn as cctb with(NOLOCK)
210										INNER JOIN Transactions as tr with(NOLOCK) ON tr.Deleted=0 and tr.GLtype=''D'' and cctb.DocumentNo=tr.DocumentNo
211										INNER JOIN InvMain as im with(NOLOCK) ON im.Deleted=0 and im.DocNo=tr.DocumentNo
212										inner join costcentremaster as ccm with(NOLOCK) ON cctb.costcentrecode = ccm.costcentrecode
213										cross apply (Select convert(numeric(22,6),sum(cc.Amount)) as Total from CCTxn cc with (NOLOCK) 
214											where cc.Deleted=0 and cc.DocumentNo=cctb.DocumentNo and cc.GLCode=cctb.GLCode group by cc.DocumentNo,cc.GLCode) ca
215									where cctb.Deleted=0 and ccm.deleted = 0 and tr.TransactionType=''SA'' 
216										and im.SelMonth='''+@ToMonth+''' and im.SelYear='''+@ToYear+'''))*100,2)) end as BusinessShare,'
217			
218				set @qry=@qry + ' ISNULL(UM.FirstName,'''') + '' '' + ISNULL(UM.MiddleName,'''') + '' '' + ISNULL(UM.LastName,'''') as BranchHeadName, 
219							(select Count(e.EmpMasterID)  from '+@MPDBName+'.dbo.EmpMaster e with(NOLOCK) 
220									inner join '+@MPDBName+'.dbo.MonthAttendance_Report MAR on e.EmpMasterID=MAR.EmpMasterId And MAR.Deleted=0 and MonthName='''+@FromMonth+''' and YearName='''+@FromYear+'''
221									inner join '+@MPDBName+'.dbo.EmpStatusDetails esd with(NOLOCK) on esd.EmpMasterID=e.EmpMasterID and PresentStatus=1
222								Where EmpType=''back'' and Branch=BM.BranchMasterId and e.Deleted=0) as TotalBranchBackOfficeEmployees,
223							(select Count(emp.EmpMasterID) from '+@MPDBName+'.dbo.EmpMaster emp with(NOLOCK)
224									inner join '+@MPDBName+'.dbo.MonthAttendance_Report MAR on emp.EmpMasterID=MAR.EmpMasterId And MAR.Deleted=0 and MonthName='''+@FromMonth+''' and YearName='''+@FromYear+'''
225									Inner Join '+@MPDBName+'.dbo.EmpStatusDetails esd with(NOLOCK) on esd.EmpMasterID=emp.EmpMasterID and PresentStatus=1
226								where EmpType=''back'' and emp.Deleted=0 and IsActive=1) as TotalBackOfficeEmployees,
227						
228							case when convert(numeric(22,2),(select Count(emp.EmpMasterID) from '+@MPDBName+'.dbo.EmpMaster emp with(NOLOCK)
229									inner join '+@MPDBName+'.dbo.MonthAttendance_Report MAR on emp.EmpMasterID=MAR.EmpMasterId And MAR.Deleted=0 and MonthName='''+@FromMonth+''' and YearName='''+@FromYear+'''
230									Inner Join '+@MPDBName+'.dbo.EmpStatusDetails esd with(NOLOCK) on esd.EmpMasterID=emp.EmpMasterID and PresentStatus=1
231								where EmpType=''back'' and emp.Deleted=0 and IsActive=1))=0 then 0 else
232							convert(numeric(22,2),(convert(numeric(22,2),(select Count(e.EmpMasterID)  from '+@MPDBName+'.dbo.EmpMaster e with(NOLOCK) 
233									inner join '+@MPDBName+'.dbo.MonthAttendance_Report MAR on e.EmpMasterID=MAR.EmpMasterId And MAR.Deleted=0 and MonthName='''+@FromMonth+''' and YearName='''+@FromYear+'''
234									inner join '+@MPDBName+'.dbo.EmpStatusDetails esd with(NOLOCK) on esd.EmpMasterID=e.EmpMasterID and PresentStatus=1
235								Where EmpType=''back'' and Branch=BM.BranchMasterId and e.Deleted=0))
236								/ 
237								convert(numeric(22,2),(select Count(emp.EmpMasterID) from '+@MPDBName+'.dbo.EmpMaster emp with(NOLOCK)
238									inner join '+@MPDBName+'.dbo.MonthAttendance_Report MAR on emp.EmpMasterID=MAR.EmpMasterId And MAR.Deleted=0 and MonthName='''+@FromMonth+''' and YearName='''+@FromYear+'''
239									Inner Join '+@MPDBName+'.dbo.EmpStatusDetails esd with(NOLOCK) on esd.EmpMasterID=emp.EmpMasterID and PresentStatus=1
240								where EmpType=''back'' and emp.Deleted=0 and IsActive=1)))*100) end as BackOfficeEmployeePercentage, '
241			
242				set @qry=@qry + ' (select Count(em.EmpMasterID) from '+@MPDBName+'.dbo.EmpMaster em with(NOLOCK)
243									inner join '+@MPDBName+'.dbo.MonthAttendance_Report MAR on em.EmpMasterID=MAR.EmpMasterId And MAR.Deleted=0 and MonthName='''+@FromMonth+''' and YearName='''+@FromYear+'''
244									inner join '+@MPDBName+'.dbo.EmpStatusDetails esd with(NOLOCK) on esd.EmpMasterID=EM.EmpMasterID and PresentStatus=1
245								where EmpType=''Front'' and Branch=BM.BranchMasterId and em.Deleted=0 and IsActive=1) as TotalBranchFrontOfficeEmployees,
246							(select Count(emps.EmpMasterID)  from '+@MPDBName+'.dbo.EmpMaster emps with(NOLOCK)
247									inner join '+@MPDBName+'.dbo.MonthAttendance_Report MAR on emps.EmpMasterID=MAR.EmpMasterId And MAR.Deleted=0 and MonthName='''+@FromMonth+''' and YearName='''+@FromYear+'''
248									inner join '+@MPDBName+'.dbo.EmpStatusDetails esd with(NOLOCK) on esd.EmpMasterID=emps.EmpMasterID and PresentStatus=1
249								where EmpType=''Front'' and emps.Deleted=0 and IsActive=1) as TotalFrontOfficeEmployees,
250							
251							case when convert(numeric(22,2),(select Count(emps.EmpMasterID)  from '+@MPDBName+'.dbo.EmpMaster emps with(NOLOCK) 
252									inner join '+@MPDBName+'.dbo.MonthAttendance_Report MAR on emps.EmpMasterID=MAR.EmpMasterId And MAR.Deleted=0 and MonthName='''+@FromMonth+''' and YearName='''+@FromYear+'''
253									inner join '+@MPDBName+'.dbo.EmpStatusDetails esd with(NOLOCK) on esd.EmpMasterID=emps.EmpMasterID and PresentStatus=1
254								where EmpType=''Front'' and emps.Deleted=0 and IsActive=1))=0 then 0 else
255							convert(numeric(22,2), (convert(numeric(22,2),(select Count(em.EmpMasterID) from '+@MPDBName+'.dbo.EmpMaster em with(NOLOCK)
256									inner join '+@MPDBName+'.dbo.MonthAttendance_Report MAR on em.EmpMasterID=MAR.EmpMasterId And MAR.Deleted=0 and MonthName='''+@FromMonth+''' and YearName='''+@FromYear+'''
257									inner join '+@MPDBName+'.dbo.EmpStatusDetails esd with(NOLOCK) on esd.EmpMasterID=EM.EmpMasterID and PresentStatus=1
258								where EmpType=''Front'' and Branch=BM.BranchMasterId and em.Deleted=0 and IsActive=1))
259								/
260								convert(numeric(22,2),(select Count(emps.EmpMasterID)  from '+@MPDBName+'.dbo.EmpMaster emps with(NOLOCK) 
261									inner join '+@MPDBName+'.dbo.MonthAttendance_Report MAR on emps.EmpMasterID=MAR.EmpMasterId And MAR.Deleted=0 and MonthName='''+@FromMonth+''' and YearName='''+@FromYear+'''
262									inner join '+@MPDBName+'.dbo.EmpStatusDetails esd with(NOLOCK) on esd.EmpMasterID=emps.EmpMasterID and PresentStatus=1
263								where EmpType=''Front'' and emps.Deleted=0 and IsActive=1)))*100) end as FrontOfficeEmployeePercentage
264						from '+@MPDBName+'.dbo.Branchmaster BM 
265							left join '+@MPDBName+'.dbo.UserMaster UM on UserMasterId=ApproverUserMasterId
266							left join Accounts_DEM.dbo.DivisionMaster as d ON d.Deleted=0 and d.DivisionMasterId=BM.ERPBranchId
267						where BM.Deleted=0'
268						--BM.BranchMasterId='+convert(varchar(10),@MPBranchId)+' and 
269				select @qry
270				exec(@qry)
271			END
272		END
273	END
274
275	if @ReportId=2 or @AllTogether='Y' --SALES REVENUE SUMMARY
276	BEGIN
277		if @DetailsType='S'
278		BEGIN
279			Create Table #SRMonth (Months Varchar(50))
280			insert into #SRMonth (Months) values ('Target')
281			insert into #SRMonth (Months) values ('Actual')
282
283			--Delete from Accounts_DEM.dbo.NineFinger_SalesRevenue where CompanyCode=left(DB_NAME(),3) and FYCode=DB_NAME() and BranchCode=@DivisionCode
284			--	set @qry='
285			--	Insert into Accounts_DEM.dbo.NineFinger_SalesRevenue(CompanyCode, FYCode, BranchCode, FromMonth, FromYear, ToMonth, ToYear, CreatedBy, DivisionCode,
286			--	Months,Target,Actual)
287			    Select @FinancialStart_year=DATEPART (Year,FinancialYearStart),@FinancialEnd_year=DATEPART (Year,FinancialYearEnd),
288				@FinancialStart_Month=DATENAME (MONTH,FinancialYearStart),@FinancialEnd_Month=DATENAME (MONTH,FinancialYearEnd),
289				@FinancialYearStart=Convert (Date,FinancialYearStart,23),@FinancialYearEnd=convert(Date,FinancialYearEnd,23),
290				@FinancialYearStart_from=Convert (Date,FinancialYearStart,23),@FinancialYearEnd_to=convert(Date,FinancialYearEnd,23)
291			from Accounts_Dem.dbo.CompanyFinancialYearMaster
292			where FinancialYearCode=db_name() and Deleted=0
293
294			if @FinancialYearEnd>CONVERT(datetime,'01 ' + @ToMonth + ' ' + @ToYear)
295				set @FinancialYearEnd=CONVERT(datetime,'01 ' + @ToMonth + ' ' + @ToYear)
296
297			if exists (select top 1 DivisionCode from Transactions with(NOLOCK) where Deleted=0 and TransactionType='SA' and DivisionCode=@DivisionCode)
298			BEGIN
299				--Insert all Sales GL Code into #temp Table
300				
301
302				set @cntr=1
303				--Loop Through for all Months
304				while @FinancialYearStart<=@FinancialYearEnd
305				Begin
306					--GET Month & Year, ColumnName and Add cOLUMN to Table
307					SET @col_month =Convert(varchar(20),DateName(month,@FinancialYearStart))
308					SET @col_Year =Convert(varchar(20),DateName(YEAR,@FinancialYearStart))
309					SET @col =Convert(varchar(20),DateName(month,@FinancialYearStart))+'-'+convert(varchar(10),year(@FinancialYearStart))
310					EXEC('Alter table #SRMonth add ['+@col+'] money')
311				
312					--GET SALes for the Month & Update in Actual Column for Current Month
313					set @amount = (select Sum(Convert(numeric(22,2),(TS.Amount*convert(numeric(22,6),(case ca.Total when 0 then 0 else (CCBU.Amount/ca.Total)*100 end)))/100)) 
314											as Total_Amount 
315									from CCTxn CCBU with(NOLOCK)
316										inner join Transactions TS with(NOLOCK) on TS.DocumentNo = CCBU.DocumentNo and TS.Deleted=0 and TS.GLType='D'
317										INNER JOIN InvMain as im with(NOLOCK) ON im.Deleted=0 and im.DocNo=TS.DocumentNo
318										inner join CostCentreMaster ccm with(NOLOCK) ON CCBU.CostCentreCode = ccm.CostCentreCode
319										cross apply (Select convert(numeric(22,6),sum(cc.Amount)) as Total from CCTxn cc with (NOLOCK) 
320											where cc.Deleted=0 and cc.DocumentNo=CCBU.DocumentNo and cc.GLCode=CCBU.GLCode
321											group by cc.DocumentNo,cc.GLCode) ca
322									Where CCBU.Deleted=0 and ccm.Deleted = 0 and Ts.TransactionType='SA' and CCBU.GLCode in (select GLCode from #temp)
323										and ccm.DivisionCode=@DivisionCode and im.SelMonth= @col_month and im.SelYear= @col_Year)
324					set @amount=ROUND((@amount/@DivisionNumber),2)
325
326					exec('Update #SRMonth set ['+@col+']='''+@amount+''' where months=''Actual''')
327					set @amount_total=(ISNULL(@amount_total,0)+isNull((convert(money,(@amount))),0))
328				
329					--GET Budgeted Figure Value for GL's & Update in Budget Column
330					set @targetCol='BudgetedFigure' + CONVERT(varchar(10),@cntr)
331					set @qry='select @Targetamount=isnull(' + @targetCol+ ',0) from '+@DivisionCode+'_GLMaster where Deleted=0 and GLCode in (select GLCode from #temp)'
332					exec sp_executesql @qry ,N'@Targetamount varchar(200) output',  @Targetamount output
333
334					--print @Targetamount
335
336					set @Targetamount=ROUND((@Targetamount/@DivisionNumber),2)
337
338					exec('Update #SRMonth set ['+@col+']='''+@Targetamount+''' where months=''Target''')
339					set @Targetamount_total=(ISNULL(@Targetamount_total,0)+isNull((convert(money,(@Targetamount))),0))
340					print '--'
341					print @Targetamount_total
342
343					--Reset/Increament Counter variables
344					set @cntr=@cntr+1
345					set @FinancialYearStart=dateadd("M",1, @FinancialYearStart)
346				End
347
348				Alter Table #SRMonth add Total varchar(50)
349			
350				Update #SRMonth set Total = @amount_total where months='Actual' 
351				Update #SRMonth set Total = @Targetamount_total where months='Target' 
352
353				Alter Table #SRMonth add Achievement varchar(50)
354			
355				Update #SRMonth set Achievement=case when @Targetamount_total=0 then 'NA' else convert(varchar(20), ROUND(((@amount_total/@Targetamount_total) * 100),2)) + '%' end
356				where months='Actual'
357				Update #SRMonth set Achievement=''
358				where months='Target'
359
360				select * from #SRMonth
361			
362				--Drop table #temp
363				drop table #SRMonth
364			END
365		END
366		else
367		BEGIN
368			select TS.DocumentNo, convert(varchar(20), TS.Date,106) as DocDate, d.GLCode as CustomerCode, d.GLDescription as CustomerName,
369					ccm.CostCentreCode as SiteCode, ccm.Description as SiteName, im.SelMonth, im.SelYear,
370					Convert(numeric(22,2),(TS.Amount*convert(numeric(22,6),(case ca.Total when 0 then 0 else (CCBU.Amount/ca.Total)*100 end)))/100) 
371						as Total_Amount 
372			from CCTxn CCBU with(NOLOCK)
373				inner join Transactions TS with(NOLOCK) on TS.DocumentNo = CCBU.DocumentNo and TS.Deleted=0 and TS.GLType='D'
374				INNER JOIN InvMain as im with(NOLOCK) ON im.Deleted=0 and im.DocNo=TS.DocumentNo
375				inner join CostCentreMaster ccm with(NOLOCK) ON CCBU.CostCentreCode = ccm.CostCentreCode
376				inner join DebtInd d with(NOLOCK) ON d.GLCode = TS.GLCode and d.Deleted=0
377				cross apply (Select convert(numeric(22,6),sum(cc.Amount)) as Total from CCTxn cc with (NOLOCK) 
378					where cc.Deleted=0 and cc.DocumentNo=CCBU.DocumentNo and cc.GLCode=CCBU.GLCode
379					group by cc.DocumentNo,cc.GLCode) ca
380			Where CCBU.Deleted=0 and ccm.Deleted = 0 and Ts.TransactionType='SA' 
381				and CCBU.GLCode in (select distinct(cct.GLCode) from CCTxn as cct with(NOLOCK) where LEFT(cct.DocumentNo,2)='SA' and cct.Deleted=0 )
382				and ccm.DivisionCode=@DivisionCode and convert(datetime,'01 ' + im.SelMonth + ' ' + convert(varchar(10),im.SelYear)) 
383					between convert(datetime,'01 ' + @FromMonth + ' ' + @FromYear) and convert(datetime,'01 ' + @ToMonth + ' ' + @ToYear)
384		END
385	END
386
387	if @ReportId=3 or @AllTogether='Y' --NEW SALES SUMMARY
388	BEGIN
389		declare @Newcount_total int=0, @amount_Out_total int=0
390
391		Select @SerFromDate=Convert (Date,FinancialYearStart,23), @SerToDate=convert(Date,FinancialYearEnd,23),
392			@FinancialStart_year=DATEPART (Year,FinancialYearStart),@FinancialEnd_year=DATEPART (Year,FinancialYearEnd),
393			@FinancialStart_Month=DATENAME (MONTH,FinancialYearStart),@FinancialEnd_Month=DATENAME (MONTH,FinancialYearEnd),
394			@FinancialYearStart=Convert (Date,FinancialYearStart,23),@FinancialYearEnd=convert(Date,FinancialYearEnd,23),
395			@FinancialYearStart_from=Convert (Date,FinancialYearStart,23),@FinancialYearEnd_to=convert(Date,FinancialYearEnd,23)
396		from Accounts_Dem.dbo.CompanyFinancialYearMaster where FinancialYearCode=db_name() and Deleted=0
397
398		if @FinancialYearEnd>CONVERT(datetime,'01 ' + @ToMonth + ' ' + @ToYear)
399			set @FinancialYearEnd=CONVERT(datetime,'01 ' + @ToMonth + ' ' + @ToYear)
400
401		--print @FinancialYearEnd
402
403
404		create table #ClientSite(tempId int identity(1,1), ClientId int, SiteId int, SiteCode varchar(50), MinMonthYear varchar(20))
405		exec('Insert into #ClientSite(ClientId, SiteId, SiteCode, MinMonthYear)
406		select a.ClientGroupMasterId, ss.SiteMasterId, ss.SiteCode, a.MinMonthYear
407		from Module_Paymax_MSF.dbo.MonthAttendance_Report as m with(NOLOCK)
408			INNER JOIN Module_Paymax_MSF.dbo.SiteMaster as ss ON ss.Deleted=0 and ss.SiteMasterId=m.SiteId
409			CROSS APPLY(select cg.ClientGroupMasterId, min(CONVERT(date,''01-'' + ma.MonthName + ''-'' + ma.YearName)) as MinMonthYear
410				from Module_Paymax_MSF.dbo.MonthAttendance_Report as ma
411					INNER JOIN Module_Paymax_MSF.dbo.SiteMaster as sm ON sm.Deleted=0 and sm.SiteMasterId=ma.SiteId
412					INNER JOIN Module_Paymax_MSF.dbo.ClientGroupMaster as cg ON cg.Deleted=0 and cg.ClientGroupMasterid=sm.ClientGroupId
413				where ma.Deleted=0 --and sm.SiteMasterId=ss.SiteMasterId and cg.ClientGroupMasterId=ss.ClientGroupId
414				group by cg.ClientGroupMasterId
415				having MIN(CONVERT(date,''01-'' + ma.MonthName + ''-'' + ma.YearName)) between '''+@FinancialYearStart+''' and '''+@FinancialYearEnd+''') as a
416		where m.Deleted=0 and ss.ClientGroupId=a.ClientGroupMasterId and ss.BranchId='+@MPBranchId+'
417		group by ss.SiteMasterId, ss.SiteCode, a.ClientGroupMasterId, a.MinMonthYear
418		having MIN(CONVERT(date,''01-'' + m.MonthName + ''-'' + m.YearName))=MinMonthYear
419		Order By ss.SiteMasterId, a.ClientGroupMasterId')
420		--select distinct(GLCode) into #temp1 from CCTxnBreakUp with(NOLOCK) where LEFT(DocumentNo,2)='SA' and Deleted=0 
421
422		print 'S'
423
424		if @DetailsType='S'
425		BEGIN
426			if exists (select top 1 DivisionCode from Transactions  with(NOLOCK) where Deleted=0 and TransactionType='SA' and DivisionCode=@DivisionCode)
427			BEGIN
428				
429				Create Table #Month (Months Varchar(50))
430				insert into #Month (Months) values ('New Clients No.')
431				insert into #Month (Months) values ('New Sites No.')
432				insert into #Month (Months) values ('New Client Sales')
433
434				--select * from #ClientSite
435
436				while @FinancialYearStart<=@FinancialYearEnd
437				Begin
438					--GET Month Year & add Columns in TempTable
439					SET @col_month =Convert(varchar(20),DateName(month,@FinancialYearStart))
440					SET @col_Year =Convert(varchar(20),DateName(YEAR,@FinancialYearStart))
441
442					SET @col =Convert(varchar(20),DateName(month,@FinancialYearStart))+'-'+convert(varchar(10),year(@FinancialYearStart))
443					EXEC('Alter table #Month add ['+@col+'] Varchar(50)')
444				
445					--GET DISBANDED CLIENT & SITE COUNT & Update in TempTable
446					select @SCount=count(distinct SiteId), @Count=count(distinct CLientId)
447					from #ClientSite
448					where MinMonthYear=convert(date, '01-'+@col)
449					
450					--print 'A'
451
452					/*set @qry= 'select @SCount=isnull(Count(Distinct SiteMasterId),0) from '+@MPDBName+'.dbo.SiteMaster with(NOLOCK)
453								where Deleted=0 and BranchId='+convert(varchar(10),@MPBranchId)+' 
454									and convert(smalldatetime,''01-'+@col+''')=(select MIN(CONVERT(smalldatetime,''01-'' + mar.MonthName + ''-'' + mar.YearName))
455										from '+@MPDBName+'.dbo.MonthAttendance_Report as mar with(NOLOCK)
456										where mar.Deleted=0 and mar.SiteId='+@MPDBName+'.dbo.SiteMaster.SiteMasterId)'
457					exec sp_executesql @qry, N'@SCount varchar(200) output',  @SCount output
458					*/
459					set @SCount=ISNULL(@SCount,0)
460					set @Count=ISNULL(@Count,0)
461
462					Exec('update #Month set ['+@col+'] ='+@Count+' where Months=''New Clients No.''')
463					Exec('update #Month set ['+@col+'] ='+@SCount+' where Months=''New Sites No.''')
464
465					set @Newcount_total=(@Newcount_total+isNull((convert(money,(@Count))),0))
466					set @amount_Out_total=(@amount_Out_total+isNull((convert(money,(@SCount))),0))
467				
468					--print 'B'
469					--GET LAST MONTH SALES FOR THESE SITES
470					select @amount=Sum(Convert(numeric(22,2),(TS.Amount*convert(numeric(22,6),(case ca.Total when 0 then 0 else (CCBU.Amount/ca.Total)*100 end)))/100))
471								from CCTxn CCBU with(NOLOCK)
472									inner join Transactions TS with(NOLOCK) on TS.DocumentNo = CCBU.DocumentNo and TS.Deleted=0 and TS.GLType='D'
473									INNER JOIN InvMain as im with(NOLOCK) ON im.Deleted=0 and im.DocNo=TS.DocumentNo
474									inner join CostCentreMaster ccm with(NOLOCK) ON CCBU.CostCentreCode = ccm.CostCentreCode
475									cross apply (Select convert(numeric(22,6),sum(cc.Amount)) as Total from CCTxn cc with (NOLOCK) 
476										where cc.Deleted=0 and cc.DocumentNo=CCBU.DocumentNo and cc.GLCode=CCBU.GLCode group by cc.DocumentNo,cc.GLCode) ca
477								Where CCBU.Deleted=0 and ccm.deleted = 0 and Ts.TransactionType='SA' and CCBU.GLCode in (select GLCode from #temp)
478										and ccm.DivisionCode=@DivisionCode
479										and im.selMonth + '-' + convert(varchar(10),im.SelYear)=@col
480										and CCBU.CostCentreCOde IN (select t.SIteCode from #ClientSite as t where t.MinMonthYear=convert(datetime,'01-' + @col))
481
482					--exec sp_executesql @qry,N'@amount varchar(200) output', @amount output
483					set @amount=ISNULL(@amount,0)
484
485					--print 'C'
486
487					set @amount=ROUND((@amount / @DivisionNumber),2)
488					
489					--select @qry
490
491					exec('Update #Month set ['+@col+']='''+@amount+''' where months=''Sales Amount''')
492					set @amount_total=(@amount_total+isNull((convert(money,(@amount))),0))
493
494					--print 'D'
495
496					--RESET COUNTER VARIABLES
497					set @cntr=@cntr+1
498					set @FinancialYearStart=dateadd("M",1, @FinancialYearStart)
499					
500				END
501
502				Alter Table #month add Total int
503			
504				Update #Month set Total = @Newcount_total where months='New Clients No.' 
505				Update #Month set Total = @amount_Out_total where months='New Sites No.' 
506				Update #Month set Total = @amount_total where months='Sales Amount' 
507
508				select * from #Month
509				--drop table #temp1
510				drop table #Month
511			END
512		END
513		else
514		BEGIN
515			select dd.GLCOde, dd.GLDEscription, cg.ClientGroupName, datename("MM",cs.MinMonthYear) as FirstSalMonth, 
516				YEAR(cs.MinMonthYear) as FirstSalYear, 
517				(select Sum(Convert(numeric(22,2),(TS.Amount*convert(numeric(22,6),(case ca.Total when 0 then 0 else (CCBU.Amount/ca.Total)*100 end)))/100))
518					from CCTxn CCBU with(NOLOCK)
519						inner join Transactions TS with(NOLOCK) on TS.DocumentNo = CCBU.DocumentNo and TS.Deleted=0 and TS.GLType='D'
520						INNER JOIN InvMain as im with(NOLOCK) ON im.Deleted=0 and im.DocNo=TS.DocumentNo
521						cross apply (Select convert(numeric(22,6),sum(cc.Amount)) as Total from CCTxn cc with (NOLOCK) 
522							where cc.Deleted=0 and cc.DocumentNo=CCBU.DocumentNo and cc.GLCode=CCBU.GLCode group by cc.DocumentNo,cc.GLCode) ca
523					Where CCBU.Deleted=0 and Ts.TransactionType='SA' and CCBU.GLCode in (select GLCode from #temp)
524							and cs.MinMonthYear=convert(datetime,'01-' + im.selMonth + '-' + convert(varchar(10),im.SelYear)) and cs.SiteCode=CCBU.CostCentreCOde) as BillAmt
525			from #ClientSite as cs
526				INNER JOIN ClientGroup as cg with(NOLOCK) ON cg.Deleted=0 and cg.PaymaxClientGroupID=cs.ClientId
527				INNER JOIN DebtInd as dd with(NOLOCK) ON dd.Deleted=0 and dd.ClientGroupId=cg.ClientGroupId
528			group by dd.GLCode, dd.GLDEscription, cg.ClientGroupName, cs.MinMonthYear, cs.SiteCode
529		END
530
531		drop table #ClientSite
532	END
533
534	if @ReportId=4 or @AllTogether='Y' --ACCOUNTS & FINANCE SUMMARY
535	BEGIN
536		DeClare	@LastYearFrom Datetime, @LastYearTo datetime, @CurrentFrom Datetime, @CurrentTO Datetime
537		set @CurrentFrom = CONVERT(Date, @FromMonth +' '+ @FromYear)
538		set @CurrentTO = CONVERT(Date, @ToMonth +' '+ @ToYear)
539		set @CurrentTO=DATEADD("D",-1, DATEADD("M",1,@CurrentTO))
540		set @LastYearFrom=DATEAdd(YEAR,-1,@CurrentFrom)
541		set @LastYearTo=DATEAdd(YEAR,-1,@CurrentTO)
542
543		declare @LastYearMaxFinDBName varchar(20)
544		set @LastYearMaxFinDBName=(select top 1 FinancialYearCode from Accounts_DEM.dbo.CompanyFinancialYearMaster where Deleted=0 and CompanyCode=left(DB_NAME(),3)
545			and FinancialYearCode<DB_NAME() Order By FinancialYearCode DESC)
546		
547		if @DetailsType='S'
548		BEGIN
549			--CREATE TEMP TABLE
550			create table #AFtemp(Particulars varchar(50), CurrentYear money, LastYear money, GrowthPercent float)
551
552			--GET CURRENT REVENUE
553			insert into #AFtemp(Particulars) values('Revenue')
554			declare @Revenue money, @CorpRevenue money, @RevenuePerc float, @LYRevenuePerc float
555			set @Revenue = (select Sum(Convert(numeric(22,2),(TS.Amount*convert(numeric(22,6),(case ca.Total when 0 then 0 else (CCBU.Amount/ca.Total)*100 end)))/100))
556									from CCTxn CCBU with(NOLOCK)
557										inner join Transactions TS with(NOLOCK) on TS.DocumentNo = CCBU.DocumentNo and TS.Deleted=0 and TS.GLCode IN(select GLCode from #temp)
558										INNER JOIN InvMain as im with(NOLOCK) ON im.Deleted=0 and im.DocNo=ts.DocumentNo
559										inner join CostCentreMaster ccm with(NOLOCK) on CCBU.CostCentreCode = ccm.CostCentreCode
560										cross apply (Select convert(numeric(22,6),sum(cc.Amount)) as Total from CCTxn cc with (NOLOCK) 
561											where cc.Deleted=0 and cc.DocumentNo=CCBU.DocumentNo and cc.GLCode=CCBU.GLCode group by cc.DocumentNo,cc.GLCode) ca
562									Where CCBU.Deleted=0 and ccm.Deleted = 0 and Ts.TransactionType='SA' and ccm.DivisionCode=@DivisionCode 
563										and convert(date, im.SelMonth + ' ' + CONVERT(varchar(20), im.SelYear)) BETWEEN @CurrentFrom and @CurrentTO)
564			set @Revenue=ROUND((@Revenue/@DivisionNumber),2)
565			Update #AFtemp set CurrentYear=ISNULL(@Revenue,0) where Particulars='Revenue'
566
567			set @CorpRevenue = (select Sum(Convert(numeric(22,2),(TS.Amount*convert(numeric(22,6),(case ca.Total when 0 then 0 else (CCBU.Amount/ca.Total)*100 end)))/100))
568								from CCTxn CCBU with(NOLOCK)
569									inner join Transactions TS with(NOLOCK) on TS.DocumentNo = CCBU.DocumentNo and TS.Deleted=0 and TS.GLCode IN(select GLCode from #temp)
570									INNER JOIN InvMain as im with(NOLOCK) ON im.Deleted=0 and im.DocNo=ts.DocumentNo
571									inner join CostCentreMaster ccm with(NOLOCK) on CCBU.CostCentreCode = ccm.CostCentreCode
572									cross apply (Select convert(numeric(22,6),sum(cc.Amount)) as Total from CCTxn cc with (NOLOCK) 
573										where cc.Deleted=0 and cc.DocumentNo=CCBU.DocumentNo and cc.GLCode=CCBU.GLCode group by cc.DocumentNo,cc.GLCode) ca
574								Where CCBU.Deleted=0 and ccm.Deleted = 0 and Ts.TransactionType='SA'
575									and convert(date, im.SelMonth + ' ' + CONVERT(varchar(20), im.SelYear)) BETWEEN @CurrentFrom and @CurrentTO)
576			set @CorpRevenue=ROUND((@CorpRevenue/@DivisionNumber),2)
577
578			if @CorpRevenue<>0
579				set @RevenuePerc=ROUND((@Revenue / @CorpRevenue)*100,2)
580			else
581				set @RevenuePerc=0
582			
583			--GET LAST YEAR REVENUE
584			if ISNULL(@LastYearMaxFinDBName,'')=''
585			BEGIN
586				Update #AFtemp set LastYear=0 where Particulars='Revenue'
587				set @LYRevenuePerc=0
588			END
589			else
590			BEGIN
591				set @Revenue=0
592				set @qry='select @Revenue=Sum(Convert(numeric(22,2),(TS.Amount*convert(numeric(22,6),(case ca.Total when 0 then 0 else (CCBU.Amount/ca.Total)*100 end)))/100))
593									from '+@LastYearMaxFinDBName+'.dbo.CCTxn CCBU with(NOLOCK)
594										inner join '+@LastYearMaxFinDBName+'.dbo.Transactions TS with(NOLOCK) on TS.DocumentNo = CCBU.DocumentNo and TS.Deleted=0 and TS.GLCode IN(select GLCode from #temp)
595										INNER JOIN '+@LastYearMaxFinDBName+'.dbo.InvMain as im with(NOLOCK) ON im.Deleted=0 and im.DocNo=ts.DocumentNo
596										inner join costCentreMaster ccm with(NOLOCK) ON ccbu.costcentrecode = ccm.costcentrecode
597										cross apply (Select convert(numeric(22,6),sum(cc.Amount)) as Total from '+@LastYearMaxFinDBName+'.dbo.CCTxn cc with (NOLOCK) 
598											where cc.Deleted=0 and cc.DocumentNo=cCBU.DocumentNo and cc.GLCode=cCBU.GLCode group by cc.DocumentNo,cc.GLCode) ca
599									Where CCBU.Deleted=0 and ccm.deleted = 0 and Ts.TransactionType=''SA'' and ccm.DivisionCode='''+@DivisionCode +'''
600										and convert(date, im.SelMonth + '' '' + convert(varchar(20), im.SelYear)) 
601											BETWEEN '''+convert(varchar(20),@LastYearFrom)+''' and '''+convert(varchar(20),@LastYearTo)+''''
602				exec sp_Executesql @qry,N'@Revenue money output', @Revenue output
603				set @Revenue=ROUND((@Revenue/@DivisionNumber),2)
604				Update #AFtemp set LastYear=ISNULL(@Revenue,0) where Particulars='Revenue'
605			
606				set @CorpRevenue=0
607				set @qry='select @CorpRevenue=Sum(Convert(numeric(22,2),(ts.Amount*convert(numeric(22,6),(case ca.Total when 0 then 0 else (ccbu.Amount/ca.Total)*100 end)))/100)) 
608							from '+@LastYearMaxFinDBName+'.dbo.CCTxn CCBU with(NOLOCK)
609									inner join '+@LastYearMaxFinDBName+'.dbo.Transactions TS with(NOLOCK) on TS.DocumentNo = CCBU.DocumentNo and TS.Deleted=0 and TS.GLCode IN(select GLCode from #temp)
610									INNER JOIN '+@LastYearMaxFinDBName+'.dbo.InvMain as im with(NOLOCK) ON im.Deleted=0 and im.DocNo=ts.DocumentNo
611									inner join costcentremaster ccm with(NOLOCK) on ccbu.costcentrecode = ccm.costcentrecode
612									cross apply (Select convert(numeric(22,6),sum(cc.Amount)) as Total from '+@LastYearMaxFinDBName+'.dbo.CCTxn cc with (NOLOCK) 
613										where cc.Deleted=0 and cc.DocumentNo=cCBU.DocumentNo and cc.GLCode=cCBU.GLCode group by cc.DocumentNo,cc.GLCode) ca
614								Where CCBU.Deleted=0 and ccm.deleted = 0 and Ts.TransactionType=''SA''
615									and convert(date, im.SelMonth + '' '' + convert(varchar(20),im.SelYear)) 
616										BETWEEN '''+convert(varchar(20),@LastYearFrom)+''' and '''+convert(varchar(20),@LastYearTo)+''''
617				exec sp_Executesql @qry,N'@CorpRevenue money output', @CorpRevenue output
618				set @CorpRevenue=ROUND((@CorpRevenue/@DivisionNumber),2)
619
620				if @CorpRevenue<>0
621					set @LYRevenuePerc=ROUND((@Revenue / @CorpRevenue)*100,2)
622				else
623					set @LYRevenuePerc=0
624			END
625
626			--GET WAGES
627			insert into #AFtemp(Particulars) values('Wages')
628			set @Revenue=0
629			set @Revenue = (select Sum(case when ISNULL(TS.Amount,0)<>0 then TS.Amount else TS.Amount end * case when TS.CCType='C' then -1 else 1 end)
630							from Transactions TS with(NOLOCK) 
631								INNER JOIN GLMaster as g with(NOLOCK) ON g.Deleted=0 and g.GLCode=TS.GLCode and g.CompanyMISMarkingId=1
632							Where TS.Deleted=0 and TS.DivisionCode=@DivisionCode and TS.Date BETWEEN @CurrentFrom and @CurrentTO)
633			set @Revenue=ROUND((@Revenue/@DivisionNumber),2)
634			Update #AFtemp set CurrentYear=ISNULL(@Revenue,0) where Particulars='Wages'
635
636			--GET LAST YEAR Wages
637			if ISNULL(@LastYearMaxFinDBName,'')=''
638			BEGIN
639				Update #AFtemp set LastYear=0 where Particulars='Wages'
640			END
641			else
642			BEGIN
643				set @Revenue=0
644				set @qry='select @Revenue=Sum(case when ISNULL(TS.Amount,0)<>0 then TS.Amount else TS.Amount end * case when TS.CCType=''C'' then -1 else 1 end) 
645							from '+@LastYearMaxFinDBName+'.dbo.Transactions TS with(NOLOCK) 
646								INNER JOIN '+@LastYearMaxFinDBName+'.dbo.GLMaster as g with(NOLOCK) ON g.Deleted=0 and g.GLCode=TS.GLCode and g.CompanyMISMarkingId=1
647							Where TS.Deleted=0 and TS.DivisionCode='''+@DivisionCode+''' 
648								and TS.Date BETWEEN '''+convert(varchar(20),@LastYearFrom) + ''' and '''+convert(varchar(20),@LastYearTo)+''''
649				exec sp_Executesql @qry,N'@Revenue money output', @Revenue output
650				set @Revenue=ROUND((@Revenue/@DivisionNumber),2)
651				Update #AFtemp set LastYear=ISNULL(@Revenue,0) where Particulars='Wages'
652			END
653
654			--GET Statutory
655			insert into #AFtemp(Particulars) values('Statutory')
656			set @Revenue=0
657			set @Revenue = (select Sum(case when ISNULL(TS.Amount,0)<>0 then TS.Amount else TS.Amount end * case when TS.CCType='C' then -1 else 1 end) 
658							from Transactions TS with(NOLOCK) 
659								INNER JOIN GLMaster as g with(NOLOCK) ON g.Deleted=0 and g.GLCode=TS.GLCode and g.CompanyMISMarkingId=2
660							Where TS.Deleted=0 and TS.DivisionCode=@DivisionCode and TS.Date BETWEEN @CurrentFrom and @CurrentTO)
661			set @Revenue=ROUND((@Revenue/@DivisionNumber),2)
662			Update #AFtemp set CurrentYear=ISNULL(@Revenue,0) where Particulars='Statutory'
663
664			--GET LAST YEAR Statutory
665			if ISNULL(@LastYearMaxFinDBName,'')=''
666			BEGIN
667				Update #AFtemp set LastYear=0 where Particulars='Statutory'
668			END
669			else
670			BEGIN
671				set @Revenue=0
672				set @qry='select @Revenue=Sum(case when ISNULL(TS.Amount,0)<>0 then TS.Amount else TS.Amount end * case when TS.CCType=''C'' then -1 else 1 end) 
673							from '+@LastYearMaxFinDBName+'.dbo.Transactions TS with(NOLOCK) 
674								INNER JOIN '+@LastYearMaxFinDBName+'.dbo.GLMaster as g with(NOLOCK) ON g.Deleted=0 and g.GLCode=TS.GLCode and g.CompanyMISMarkingId=2
675							Where TS.Deleted=0 and TS.DivisionCode='''+@DivisionCode+''' 
676								and TS.Date BETWEEN '''+convert(varchar(20),@LastYearFrom) + ''' and '''+convert(varchar(20),@LastYearTo)+''''
677				exec sp_Executesql @qry,N'@Revenue money output', @Revenue output
678				set @Revenue=ROUND((@Revenue/@DivisionNumber),2)
679				Update #AFtemp set LastYear=ISNULL(@Revenue,0) where Particulars='Statutory'
680			END
681
682			--TOTAL DIRECT COST
683			insert into #AFtemp(Particulars) values('Total Direct Cost')
684			Update #AFtemp set CurrentYear=(select sum(a.CurrentYear) from #AFtemp as a where a.Particulars IN('Wages','Statutory')) where Particulars='Total Direct Cost'
685			Update #AFtemp set LastYear=(select sum(a.LastYear) from #AFtemp as a where a.Particulars IN('Wages','Statutory')) where Particulars='Total Direct Cost'
686
687			--DIRECT COST %
688			insert into #AFtemp(Particulars) values('Direct Cost %')
689			Update #AFtemp set CurrentYear=ROUND(((select a.CurrentYear from #AFtemp as a where a.Particulars IN('Total Direct Cost')) 
690									/ (select a.CurrentYear from #AFtemp as a where a.Particulars IN('Revenue')))*100,2)
691					where Particulars='Direct Cost %'
692			Update #AFtemp set LastYear=ROUND(((select a.LastYear from #AFtemp as a where a.Particulars IN('Total Direct Cost')) 
693									/ (select a.LastYear from #AFtemp as a where a.Particulars IN('Revenue')))*100,2)
694					where Particulars='Direct Cost %'
695		
696			--STAFF COST
697			insert into #AFtemp(Particulars) values('Staff Cost')
698			set @Revenue=0
699			set @Revenue = (select Sum(case when ISNULL(TS.Amount,0)<>0 then TS.Amount else TS.Amount end * case when TS.CCType='C' then -1 else 1 end) 
700							from Transactions TS with(NOLOCK) 
701								INNER JOIN GLMaster as g with(NOLOCK) ON g.Deleted=0 and g.GLCode=TS.GLCode and g.CompanyMISMarkingId=3
702							Where TS.Deleted=0 and TS.DivisionCode=@DivisionCode and TS.Date BETWEEN @CurrentFrom and @CurrentTO)
703			set @Revenue=ROUND((@Revenue/@DivisionNumber),2)
704			Update #AFtemp set CurrentYear=ISNULL(@Revenue,0) where Particulars='Staff Cost'
705
706			--GET LAST YEAR Staff Cost
707			if ISNULL(@LastYearMaxFinDBName,'')=''
708			BEGIN
709				Update #AFtemp set LastYear=0 where Particulars='Staff Cost'
710			END
711			else
712			BEGIN
713				set @Revenue=0
714				set @qry='select @Revenue=Sum(case when ISNULL(TS.Amount,0)<>0 then TS.Amount else TS.Amount end * case when TS.CCType=''C'' then -1 else 1 end) 
715							from '+@LastYearMaxFinDBName+'.dbo.Transactions TS with(NOLOCK) 
716								INNER JOIN '+@LastYearMaxFinDBName+'.dbo.GLMaster as g with(NOLOCK) ON g.Deleted=0 and g.GLCode=TS.GLCode and g.CompanyMISMarkingId=3
717							Where TS.Deleted=0 and TS.DivisionCode='''+@DivisionCode+''' 
718								and TS.Date BETWEEN '''+convert(varchar(20),@LastYearFrom) + ''' and '''+convert(varchar(20),@LastYearTo)+''''
719				exec sp_Executesql @qry,N'@Revenue money output', @Revenue output
720				set @Revenue=ROUND((@Revenue/@DivisionNumber),2)
721				Update #AFtemp set LastYear=ISNULL(@Revenue,0) where Particulars='Staff Cost'
722			END
723		
724			--BRANCH OVERHEAD
725			insert into #AFtemp(Particulars) values('Branch Overhead')
726			set @Revenue=0
727			set @Revenue = (select Sum(case when ISNULL(TS.Amount,0)<>0 then TS.Amount else TS.Amount end * case when TS.CCType='C' then -1 else 1 end) 
728							from Transactions TS with(NOLOCK) 
729								INNER JOIN GLMaster as g with(NOLOCK) ON g.Deleted=0 and g.GLCode=TS.GLCode and g.CompanyMISMarkingId=4
730							Where TS.Deleted=0 and TS.DivisionCode=@DivisionCode and TS.Date BETWEEN @CurrentFrom and @CurrentTO)
731			set @Revenue=ROUND((@Revenue/@DivisionNumber),2)
732			Update #AFtemp set CurrentYear=ISNULL(@Revenue,0) where Particulars='Branch Overhead'
733
734			--GET LAST YEAR Branch Overhead
735			if ISNULL(@LastYearMaxFinDBName,'')=''
736			BEGIN
737				Update #AFtemp set LastYear=0 where Particulars='Branch Overhead'
738			END
739			else
740			BEGIN
741				set @Revenue=0
742				set @qry='select @Revenue=Sum(case when ISNULL(TS.Amount,0)<>0 then TS.Amount else TS.Amount end * case when TS.CCType=''C'' then -1 else 1 end) 
743							from '+@LastYearMaxFinDBName+'.dbo.Transactions TS with(NOLOCK) 
744								INNER JOIN '+@LastYearMaxFinDBName+'.dbo.GLMaster as g with(NOLOCK) ON g.Deleted=0 and g.GLCode=TS.GLCode and g.CompanyMISMarkingId=4
745							Where TS.Deleted=0 and TS.DivisionCode='''+@DivisionCode+''' 
746								and TS.Date BETWEEN '''+convert(varchar(20),@LastYearFrom) + ''' and '''+convert(varchar(20),@LastYearTo)+''''
747				exec sp_Executesql @qry,N'@Revenue money output', @Revenue output
748				set @Revenue=ROUND((@Revenue/@DivisionNumber),2)
749				Update #AFtemp set LastYear=ISNULL(@Revenue,0) where Particulars='Branch Overhead'
750			END
751
752			--CORPORATE OVERHEAD
753			print @LYRevenuePerc
754			insert into #AFtemp(Particulars) values('Corporate Overhead')
755			if @CorporateBranchCode<>''
756			BEGIN
757				set @Revenue=0
758				set @Revenue = (select Sum(case when ISNULL(TS.Amount,0)<>0 then TS.Amount else TS.Amount end * case when TS.CCType='C' then -1 else 1 end) 
759								from Transactions TS with(NOLOCK) 
760									INNER JOIN GLMaster as g with(NOLOCK) ON g.Deleted=0 and g.GLCode=TS.GLCode and g.CompanyMISMarkingId=4
761								Where TS.Deleted=0 and TS.DivisionCode=@CorporateBranchCode and TS.Date BETWEEN @CurrentFrom and @CurrentTO)
762				set @Revenue=ROUND((@Revenue/@DivisionNumber),2)
763				set @Revenue=ROUND((@Revenue*@RevenuePerc)/100,2)
764				Update #AFtemp set CurrentYear= ISNULL(@Revenue,0) where Particulars='Corporate Overhead'
765
766				--GET LAST YEAR Branch Overhead
767				if ISNULL(@LastYearMaxFinDBName,'')=''
768				BEGIN
769					Update #AFtemp set LastYear=0 where Particulars='Corporate Overhead'
770				END
771				else
772				BEGIN
773					set @Revenue=0
774					set @qry='select @Revenue=Sum(case when ISNULL(TS.Amount,0)<>0 then TS.Amount else TS.Amount end * case when TS.CCType=''C'' then -1 else 1 end) 
775								from '+@LastYearMaxFinDBName+'.dbo.Transactions TS with(NOLOCK) 
776									INNER JOIN '+@LastYearMaxFinDBName+'.dbo.GLMaster as g with(NOLOCK) ON g.Deleted=0 and g.GLCode=TS.GLCode and g.CompanyMISMarkingId=4
777								Where TS.Deleted=0 and TS.DivisionCode='''+@CorporateBranchCode+''' 
778									and TS.Date BETWEEN '''+convert(varchar(20),@LastYearFrom) + ''' and '''+convert(varchar(20),@LastYearTo)+''''
779					exec sp_Executesql @qry,N'@Revenue money output', @Revenue output
780					set @Revenue=ROUND((@Revenue/@DivisionNumber),2)
781					set @Revenue=ROUND((@Revenue*@LYRevenuePerc)/100,2)
782					Update #AFtemp set LastYear=ISNULL(@Revenue,0) where Particulars='Corporate Overhead'
783				END
784			END
785
786			--TOTAL INDIRECT COST
787			insert into #AFtemp(Particulars) values('Total Indirect Cost')
788			Update #AFtemp set CurrentYear=(select sum(a.CurrentYear) from #AFtemp as a where a.Particulars IN('Staff Cost','Branch Overhead','Corporate Overhead')) where Particulars='Total Indirect Cost'
789			Update #AFtemp set LastYear=(select sum(a.LastYear) from #AFtemp as a where a.Particulars IN('Staff Cost','Branch Overhead','Corporate Overhead')) where Particulars='Total Indirect Cost'
790
791			--TOTAL INDIRECT COST %
792			insert into #AFtemp(Particulars) values('Indirect Cost %')
793			Update #AFtemp set CurrentYear=ROUND(((select a.CurrentYear from #AFtemp as a where a.Particulars IN('Total Indirect Cost')) 
794									/ (select a.CurrentYear from #AFtemp as a where a.Particulars IN('Revenue')))*100,2)
795					where Particulars='Indirect Cost %'
796			Update #AFtemp set LastYear=ROUND(((select a.LastYear from #AFtemp as a where a.Particulars IN('Total Indirect Cost')) 
797									/ (select a.LastYear from #AFtemp as a where a.Particulars IN('Revenue')))*100,2)
798					where Particulars='Indirect Cost %'
799		
800			--TOTAL INDIRECT COST
801			insert into #AFtemp(Particulars) values('Total Cost')
802			Update #AFtemp set CurrentYear=(select sum(a.CurrentYear) from #AFtemp as a where a.Particulars IN('Total Direct Cost','Total Indirect Cost')) where Particulars='Total Cost'
803			Update #AFtemp set LastYear=(select sum(a.LastYear) from #AFtemp as a where a.Particulars IN('Total Direct Cost','Total Indirect Cost')) where Particulars='Total Cost'
804
805			--TOTAL PROFIT
806			insert into #AFtemp(Particulars) values('Total Profit')
807			Update #AFtemp set CurrentYear=(select sum(a.CurrentYear) from #AFtemp as a where a.Particulars IN('Revenue')) 
808				- (select sum(a.CurrentYear) from #AFtemp as a where a.Particulars IN('Total Cost')) where Particulars='Total Profit'
809			Update #AFtemp set LastYear=(select sum(a.LastYear) from #AFtemp as a where a.Particulars IN('Revenue')) 
810				- (select sum(a.LastYear) from #AFtemp as a where a.Particulars IN('Total Cost')) where Particulars='Total Profit'
811
812			--TOTAL INDIRECT COST %
813			insert into #AFtemp(Particulars) values('Profit %')
814			Update #AFtemp set CurrentYear=ROUND(((select a.CurrentYear from #AFtemp as a where a.Particulars IN('Total Profit')) 
815									/ (select a.CurrentYear from #AFtemp as a where a.Particulars IN('Revenue')))*100,2)
816					where Particulars='Profit %'
817			Update #AFtemp set LastYear=ROUND(((select a.LastYear from #AFtemp as a where a.Particulars IN('Total Profit')) 
818									/ (select a.LastYear from #AFtemp as a where a.Particulars IN('Revenue')))*100,2)
819					where Particulars='Profit %'
820		
821			--GET GROWTH %
822			Update #AFtemp set GrowthPercent=case when LastYear<>0 then  ROUND(((CurrentYear - LastYear) / LastYear)*100,2) else 0 end
823
824			select * from #AFtemp
825			drop table #AFtemp
826		END
827		else
828		BEGIN
829			select case ccm.DivisionCode when 'COO' then 'Corporate ' else '' end + 'Revenue' as EntryType, 
830				TS.DocumentNo, CONVERT(varchar(20),TS.Date,106) as DocDate, vf.GLType, vf.GLCode, vf.GLName, 
831				ccm.CostCentreCode as SiteCode, ccm.Description as SiteName, convert(varchar(20),im.SelMonth), convert(varchar(20),im.SelYear),
832				Convert(numeric(22,2),(TS.Amount*convert(numeric(22,6),(case ca.Total when 0 then 0 else (CCBU.Amount/ca.Total)*100 end)))/100) as Amount
833			from CCTxn CCBU with(NOLOCK)
834				inner join Transactions TS with(NOLOCK) on TS.DocumentNo = CCBU.DocumentNo and TS.Deleted=0 and TS.GLCode IN(select GLCode from #temp)
835				INNER JOIN InvMain as im with(NOLOCK) ON im.Deleted=0 and im.DocNo=TS.DocumentNo
836				inner join vwFindTransaction vf with(NOLOCK) on vf.GLCode= TS.GLCode and vf.GLType=TS.GLType
837				inner join CostCentreMaster ccm with(NOLOCK) on CCBU.CostCentreCode = ccm.CostCentreCode
838				cross apply (Select convert(numeric(22,6),sum(cc.Amount)) as Total from CCTxn cc with (NOLOCK) 
839					where cc.Deleted=0 and cc.DocumentNo=CCBU.DocumentNo and cc.GLCode=CCBU.GLCode group by cc.DocumentNo,cc.GLCode) ca
840			Where CCBU.Deleted=0 and ccm.Deleted = 0 and Ts.TransactionType='SA' and (ccm.DivisionCode=@DivisionCode or ccm.DivisionCode='COO')
841				and convert(date, im.SelMonth + ' ' + CONVERT(varchar(20), im.SelYear)) BETWEEN @CurrentFrom and @CurrentTO
842
843			UNION ALL
844
845			select case g.CompanyMISMarkingId when 1 then 'Wages' when 2 then 'Statutory' when '3' then 'StaffCost' when '4' then 'Branch Overhead' end,
846				TS.DocumentNo, CONVERT(varchar(20),TS.Date,106) as DocDate, vf.GLType, vf.GLCode, vf.GLName, 
847				'' as SiteCode, '' as SiteName, datename("M", convert(varchar(20),month(TS.Date))+'/01/2019'), datename("M",'01/01/' + convert(varchar(20),YEAR(TS.Date))),
848				case when ISNULL(TS.Amount,0)<>0 then TS.Amount else TS.Amount end * case when TS.CCType='C' then -1 else 1 end
849			from Transactions TS with(NOLOCK) 
850				inner join vwFindTransaction vf with(NOLOCK) on vf.GLCode= TS.GLCode and vf.GLType=TS.GLType
851				INNER JOIN GLMaster as g with(NOLOCK) ON g.Deleted=0 and g.GLCode=TS.GLCode and g.CompanyMISMarkingId IN(1,2,3,4)
852			Where TS.Deleted=0 and TS.DivisionCode=@DivisionCode and TS.Date BETWEEN @CurrentFrom and @CurrentTO
853
854			UNION ALL
855
856			select 'Corporate Branch Overhead',
857				TS.DocumentNo, CONVERT(varchar(20),TS.Date,106) as DocDate, vf.GLType, vf.GLCode, vf.GLName, 
858				'' as SiteCode, '' as SiteName, datename("M", convert(varchar(20),month(TS.Date))+'/01/2019'), datename("M",'01/01/' + convert(varchar(20),YEAR(TS.Date))),
859				case when ISNULL(TS.Amount,0)<>0 then TS.Amount else TS.Amount end * case when TS.CCType='C' then -1 else 1 end
860			from Transactions TS with(NOLOCK) 
861				inner join vwFindTransaction vf with(NOLOCK) on vf.GLCode= TS.GLCode and vf.GLType=TS.GLType
862				INNER JOIN GLMaster as g with(NOLOCK) ON g.Deleted=0 and g.GLCode=TS.GLCode and g.CompanyMISMarkingId IN(4)
863			Where TS.Deleted=0 and TS.DivisionCode='COO' and TS.Date BETWEEN @CurrentFrom and @CurrentTO
864
865		END
866	END
867
868	if @ReportId=5 or @AllTogether='Y' --CREDIT CONTROL - BILLING VS COLLECTION
869	BEGIN
870		Select  @FinancialStart_year=DATEPART (Year,FinancialYearStart),@FinancialEnd_year=DATEPART (Year,FinancialYearEnd),
871			@FinancialStart_Month=DATENAME (MONTH,FinancialYearStart),@FinancialEnd_Month=DATENAME (MONTH,FinancialYearEnd),
872			@FinancialYearStart=Convert (Date,FinancialYearStart,23),@FinancialYearEnd=convert(Date,FinancialYearEnd,23),
873			@FinancialYearStart_from=Convert (Date,FinancialYearStart,23),@FinancialYearEnd_to=convert(Date,FinancialYearEnd,23)
874		from Accounts_Dem.dbo.CompanyFinancialYearMaster where FinancialYearCode=db_name() and Deleted=0
875		set @cntr=1
876		
877		if @FinancialYearEnd>DATEADD("D",-1, DATEADD("M", 1, CONVERT(datetime,'01 ' + @ToMonth + ' ' + @ToYear)))
878			set @FinancialYearEnd=DATEADD("D",-1, DATEADD("M", 1, CONVERT(datetime,'01 ' + @ToMonth + ' ' + @ToYear)))
879
880		if @DetailsType='S'
881		BEGIN
882			--CREATE TEMP TABLE
883			Create Table #BCMonth (Months Varchar(50))
884			insert into #BCMonth (Months) values ('Billing')
885			insert into #BCMonth (Months) values ('Collection')
886
887			EXEC('Alter table #BCMonth add [Outstanding as On 31st March-'+@FinancialStart_year+'] money')
888			
889			--GET Prev OS 
890			declare @OSAmount money
891			set @OSAmount = (select IsNull(Sum(case when ISNULL(ccb.Amount,0)<>0 then (case when TS.CCType='D' then 1 else -1 end * 
892									(Convert(numeric(22,2),(TS.Amount*convert(numeric(22,6),(case ca.Total when 0 then 0 else (ccb.Amount/ca.Total)*100 end)))/100)))
893										else (case when TS.CCType='D' then Ts.Amount else 0-TS.Amount end) end),0)
894								--else IsNull((Sum(case when TS.CCType='D' then Ts.Amount else 0-TS.Amount end)),0) end as Total_Amount 
895						from Transactions TS with(NOLOCK)
896							LEFT OUTER JOIN CCTxn as ccb with(NOLOCK) ON ccb.Deleted=0 and ccb.DocumentNo=TS.DocumentNo
897							LEFT OUTER JOIN CostCentreMaster as cm with(NOLOCK) ON cm.Deleted=0 and cm.CostCentreCode=ccb.CostCentreCode
898							cross apply (Select convert(numeric(22,6),sum(cc.Amount)) as Total from CCTxn cc with (NOLOCK) where cc.Deleted=0 
899								and cc.DocumentNo=TS.DocumentNo and cc.GLCode=ccb.GLCode group by cc.DocumentNo,cc.GLCode) ca
900						where @DivisionCode=case when ISNULL(cm.DivisionCode,'')<>'' then cm.DivisionCode else TS.DivisionCode end
901							and TS.Date< @FinancialYearStart and TS.Gltype='D' 
902							and TS.Deleted=0)
903		
904			set @OSAmount=ROUND((@OSAmount/@DivisionNumber),2)
905
906			exec('Update #BCMonth set [Outstanding as On 31st March-'+@FinancialStart_year+']='''+@OSAmount+''' where months=''Billing''')
907			exec('Update #BCMonth set [Outstanding as On 31st March-'+@FinancialStart_year+']=''0'' where months=''Collection''')
908
909			--LOOP THRU THE MONTHS
910			while @FinancialYearStart<=@FinancialYearEnd
911			Begin
912				--GET MONTH, YEAR & ADD COLUMN in TEMP TABLE
913				if @ReportType='M'--MONTHLY
914				BEGIN
915					SET @col_month =Convert(varchar(20),DateName(month,@FinancialYearStart))
916					SET @col_Year =Convert(varchar(20),DateName(YEAR,@FinancialYearStart))
917
918					SET @col =Convert(varchar(20),DateName(month,@FinancialYearStart))+'-'+convert(varchar(10),year(@FinancialYearStart))
919					EXEC('Alter table #BCMonth add ['+@col+'] money')
920
921					set @FinancialYearStart_from=CONVERT(datetime,@col_month + ' 01 ' + @col_Year)
922					set @FinancialYearEnd_to=DATEADD("D",-1, DATEADD("M",1,@FinancialYearStart_from))
923				END
924				else --QUARTERLY
925				BEGIN
926					SET @col_month =Convert(varchar(20),DateName(month,@FinancialYearStart))
927					SET @col_Year =Convert(varchar(20),DateName(YEAR,@FinancialYearStart))
928
929					SET @col='Quarter'+convert(varchar(10),@cntr)+'-'+convert(varchar(10),year(@FinancialYearStart))
930					EXEC('Alter table #BCMonth add ['+@col+'] money')
931
932					set @FinancialYearStart_from=CONVERT(datetime,@col_month + ' 01 ' + @col_Year)
933					set @FinancialYearEnd_to=DATEADD("D",-1, DATEADD("M",3,@FinancialYearStart_from))
934				END
935
936				print @col
937				print @FinancialYearStart_from
938				print @FinancialYearEnd_to
939
940				--GET BILL AMOUNT FOR THE MONTH
941				set @amount = (select Sum(Convert(numeric(22,2),(TS.Amount*convert(numeric(22,6),(case ca.Total when 0 then 0 else (CCBU.Amount/ca.Total)*100 end)))/100)) as Total_Amount 
942								from CCTxn CCBU with(NOLOCK)
943									inner join Transactions TS with(NOLOCK) on TS.DocumentNo = CCBU.DocumentNo and TS.Deleted=0 and TS.GLType='D'
944									INNER JOIN InvMain as im with(NOLOCK) ON im.Deleted=0 and im.DocNo=TS.DocumentNo
945									inner join CostCentreMaster ccm with(NOLOCK) ON CCBU.CostCentreCode = ccm.CostCentreCode
946									cross apply (Select convert(numeric(22,6),sum(cc.Amount)) as Total from CCTxn cc with (NOLOCK) 
947										where cc.Deleted=0 and cc.DocumentNo=CCBU.DocumentNo and cc.GLCode=ccbu.GLCode group by cc.DocumentNo,cc.GLCode) ca
948								Where  CCBU.Deleted=0 AND ccm.Deleted = 0 and Ts.TransactionType='SA' and ccm.DivisionCode=@DivisionCode
949									and convert(datetime, im.SelMonth + ' 01 ' + CONVERT(varchar(20),im.SelYear)) between @FinancialYearStart_from and @FinancialYearEnd_to)
950				set @amount=ROUND((@amount/@DivisionNumber),2)
951
952				exec('Update #BCMonth set ['+@col+']='''+@amount+''' where months=''Billing''')
953				set @amount_total=(@amount_total+isNull((convert(money,(@amount))),0))
954
955				--GET COLLECTION FOR THE MONTH
956				set @collectionAmount = (select Sum(abs(ISNULL(ccb.Amount,TS.Amount))	) as Total_Amount 
957									from Transactions TS with(NOLOCK)
958										LEFT OUTER JOIN RefNoTxn as ref ON ref.Deleted=0 and ref.DocumentNo=TS.DocumentNo and ref.GLCode=TS.GLCode and ref.UniqueCode=TS.RefDocumentNo
959										LEFT OUTER JOIN CCTxn as ccb with(NOLOCK) ON ccb.Deleted=0 and ccb.DocumentNo=ref.RefDocumentNo
960										LEFT OUTER JOIN CostCentreMaster as cm with(NOLOCK) ON cm.Deleted=0 and cm.CostCentreCode=ccb.CostCentreCode
961									Where ISNULL(cm.DivisionCode,TS.DivisionCode)=@DivisionCode and TS.Date between @FinancialYearStart_from and @FinancialYearEnd_to
962										and TS.GLType='D' and ((TS.CCType='C' and TS.Amount>0) OR (TS.CCType='D' and TS.Amount<0)))
963				set @collectionAmount=ROUND((@collectionAmount/@DivisionNumber),2)
964
965				exec('Update #BCMonth set ['+@col+']='''+@collectionAmount+''' where months=''Collection''')
966				set @collectionAmount_total=(@collectionAmount_total+isNull((convert(money,(@collectionAmount))),0))
967
968				set @cntr=@cntr+1
969				if @ReportType='M'
970					set @FinancialYearStart=dateadd("M",1, @FinancialYearStart)
971				else
972					set @FinancialYearStart=dateadd("M",3, @FinancialYearStart)
973			End
974
975			--ADD SUMMARY COLUMN
976			Alter Table #BCMonth add  Total varchar(50)
977			Update #BCMonth set Total = (@amount_total+@OSAmount) where months='Billing' 
978			Update #BCMonth set Total = @collectionAmount_total where months='Collection' 
979
980			insert into #BCMonth (Months) values  ('Overall Collection %')
981			Update #BCMonth set Total = ((@Collectionamount_total/(@amount_total+@OSAmount))*100) where months='Overall Collection %' 
982
983			select * from #BCMonth
984			drop table #BCMonth
985		END
986		else
987		BEGIN
988			select 'Opening' as Type, TS.DocumentNo, CONVERT(varchar(20),TS.Date,106) as DocDate, TS.DivisionCode as [Entry Branch], vt.GLType, vt.GLCode, vt.GLName, 
989				cm.CostCentreCode as SiteCode, cm.Description as SiteName, cm.DivisionCode as [Site Branch], 
990					case when ISNULL(ccb.Amount,0)<>0 then (case when Ts.CCType='D' then ccb.Amount else 0-ccb.Amount end)
991							else (case when TS.CCType='D' then Ts.Amount else 0-TS.Amount end) end as Amount
992			from Transactions TS with(NOLOCK)
993				LEFT OUTER JOIN vwFindTransaction as vt ON vt.GLType=TS.GLType and vt.GLCode=TS.GLCode
994				LEFT OUTER JOIN CCTxn as ccb with(NOLOCK) ON ccb.Deleted=0 and ccb.DocumentNo=TS.DocumentNo
995				LEFT OUTER JOIN CostCentreMaster as cm with(NOLOCK) ON cm.Deleted=0 and cm.CostCentreCode=ccb.CostCentreCode
996			where @DivisionCode=case when ISNULL(cm.DivisionCode,'')<>'' then cm.DivisionCode else TS.DivisionCode end
997				and TS.Date< @FinancialYearStart and TS.Gltype='D' 
998				and TS.Deleted=0
999
1000			UNION ALL
1001
1002			select 'Billing' as Type, TS.DocumentNo, CONVERT(varchar(20),TS.Date,106) as DocDate, TS.DivisionCode as [Entry Branch], vt.GLType, vt.GLCode, vt.GLName, 
1003				ccm.CostCentreCode as SiteCode, ccm.Description as SiteName, ccm.DivisionCode as [Site Branch], 
1004				CCBU.Amount
1005			from CCTxn CCBU with(NOLOCK)
1006				inner join Transactions TS with(NOLOCK) on TS.DocumentNo = CCBU.DocumentNo and TS.Deleted=0 and TS.GLType='D'
1007				LEFT OUTER JOIN vwFindTransaction as vt ON vt.GLType=TS.GLType and vt.GLCode=TS.GLCode
1008				INNER JOIN InvMain as im with(NOLOCK) ON im.Deleted=0 and im.DocNo=TS.DocumentNo
1009				inner join CostCentreMaster ccm with(NOLOCK) ON CCBU.CostCentreCode = ccm.CostCentreCode
1010			Where  CCBU.Deleted=0 AND ccm.Deleted = 0 and Ts.TransactionType='SA' and ccm.DivisionCode=@DivisionCode
1011				and convert(datetime, im.SelMonth + ' 01 ' + im.SelYear) between @FinancialYearStart and @FinancialYearEnd
1012
1013			UNION ALL
1014
1015			select 'Collection' as Type, TS.DocumentNo, CONVERT(varchar(20),TS.Date,106) as DocDate, TS.DivisionCode as [Entry Branch], vt.GLType, vt.GLCode, vt.GLName, 
1016				cm.CostCentreCode as SiteCode, cm.Description as SiteName, cm.DivisionCode as [Site Branch], 
1017					abs(ISNULL(Ccb.AMount,Ts.Amount))
1018			from Transactions TS with(NOLOCK)
1019				LEFT OUTER JOIN vwFindTransaction as vt ON vt.GLType=TS.GLType and vt.GLCode=TS.GLCode
1020				LEFT OUTER JOIN RefNoTxn as ref with(NOLOCK) ON ref.Deleted=0 and ref.DocumentNo=TS.DocumentNo and ref.GLCode=TS.GLCode and ref.UniqueCode=TS.RefDocumentNo
1021				LEFT OUTER JOIN CCTxn as ccb with(NOLOCK) ON ccb.Deleted=0 and ccb.DocumentNo=ref.RefDocumentNo
1022				LEFT OUTER JOIN CostCentreMaster as cm with(NOLOCK) ON cm.Deleted=0 and cm.CostCentreCode=ccb.CostCentreCode
1023			Where ISNULL(cm.DivisionCode,TS.DivisionCode)=@DivisionCode and TS.Date between @FinancialYearStart and @FinancialYearEnd
1024				and TS.GLType='D' and ((TS.CCType='C' and TS.Amount>0) OR (TS.CCType='D' and TS.Amount<0))
1025		END		
1026	END
1027
1028	if @ReportId=6 or @AllTogether='Y' --CREDIT CONTROL - OUTSTANDING SUMMARY
1029	BEGIN
1030		Select @SerFromDate=Convert (Date,FinancialYearStart,23),@SerToDate=convert(Date,FinancialYearEnd,23),
1031			@FinancialStart_year=DATEPART (Year,FinancialYearStart),@FinancialEnd_year=DATEPART (Year,FinancialYearEnd),
1032			@FinancialStart_Month=DATENAME (MONTH,FinancialYearStart),@FinancialEnd_Month=DATENAME (MONTH,FinancialYearEnd),
1033			@FinancialYearStart=Convert (Date,FinancialYearStart,23),@FinancialYearEnd=convert(Date,FinancialYearEnd,23),
1034			@FinancialYearStart_from=Convert (Date,FinancialYearStart,23),@FinancialYearEnd_to=convert(Date,FinancialYearEnd,23)
1035		from Accounts_Dem.dbo.CompanyFinancialYearMaster where FinancialYearCode=db_name() and Deleted=0
1036
1037		if @FinancialYearEnd>DATEADD("D",-1, DATEADD("M", 1, CONVERT(datetime,'01 ' + @ToMonth + ' ' + @ToYear)))
1038			set @FinancialYearEnd=DATEADD("D",-1, DATEADD("M", 1, CONVERT(datetime,'01 ' + @ToMonth + ' ' + @ToYear)))
1039
1040		if @DetailsType='S'
1041		BEGIN
1042			set @cntr=1
1043
1044			Create Table #OSMonth (Months Varchar(50))
1045			insert into #OSMonth (Months) values ('MSF-Local')
1046			insert into #OSMonth (Months) values ('MSF-Central')
1047			insert into #OSMonth (Months) values ('Total')
1048
1049			--ADD BEFORE FY START Local & Central O/S from Temptable into TempTable
1050			EXEC('Alter table #OSMonth add [OLD-'+@FinancialStart_year+'] money')
1051			set @amount = (select isNull(Sum([Pending Amount]),0) as Total_Amount from #temp1_Final 
1052							where convert(datetime,'01' + [Bill Month] + ' ' + [Bill Year])< @SerFromDate and [OfficeTypeName]='Local'
1053								and [Branch Code]=@DivisionCode)
1054			set @amount=ROUND((@amount/@DivisionNumber),2)
1055			set @amount_total=ISNULL(@amount_total,0)+isNull((convert(money,(@amount))),0)
1056
1057			exec('Update #OSMonth set [OLD-'+@FinancialStart_year+']='''+@amount+''' where months=''MSF-Local''')
1058
1059			set @amount = (select isNull(Sum([Pending Amount]),0) as Total_Amount from #temp1_Final 
1060							where convert(datetime,'01' + [Bill Month] + ' ' + [Bill Year])< @SerFromDate and [OfficeTypeName]='Central'
1061								and [Branch Code]=@DivisionCode)
1062			set @amount=ROUND((@amount/@DivisionNumber),2)
1063			set @Targetamount_total=ISNULL(@Targetamount_total,0)+isNull((convert(money,(@amount))),0)
1064			exec('Update #OSMonth set [OLD-'+@FinancialStart_year+']='''+@amount+''' where months=''MSF-Central''')
1065
1066			--For Total Row
1067			exec('Update #OSMonth set [OLD-'+@FinancialStart_year+']=(select sum(o.[OLD-'+@FinancialStart_year+']) from #OSMonth as o) where Months=''Total''')
1068
1069			while @FinancialYearStart<=@FinancialYearEnd
1070			Begin
1071				if @ReportType='M'--MONTHLY
1072				BEGIN
1073					SET @col_month =Convert(varchar(20),DateName(month,@FinancialYearStart))
1074					SET @col_Year =Convert(varchar(20),DateName(YEAR,@FinancialYearStart))
1075
1076					SET @col =Convert(varchar(20),DateName(month,@FinancialYearStart))+'-'+convert(varchar(10),year(@FinancialYearStart))
1077					EXEC('Alter table #OSMonth add ['+@col+'] money')
1078
1079					set @FinancialYearStart_from=@FinancialYearStart
1080					set @FinancialYearEnd_to=DATEADD("D",-1,DATEADD("M",1,@FinancialYearStart))
1081				END
1082				else--QUARTERLY
1083				BEGIN
1084					SET @col_month =Convert(varchar(20),DateName(month,@FinancialYearStart))
1085					SET @col_Year =Convert(varchar(20),DateName(YEAR,@FinancialYearStart))
1086
1087					SET @col='Quarter'+convert(varchar(10),@cntr)+'-'+convert(varchar(10),year(@FinancialYearStart))
1088					EXEC('Alter table #OSMonth add ['+@col+'] money')
1089
1090					set @FinancialYearStart_from=@FinancialYearStart
1091					set @FinancialYearEnd_to=DATEADD("D",-1,DATEADD("M",3,@FinancialYearStart))
1092				END
1093
1094				--FOR LOCAL
1095				set @amount = (select isNull(Sum([Pending Amount]),0) as Total_Amount from #temp1_Final
1096							Where convert(datetime,'01' + [Bill Month] + ' ' + [Bill Year]) Between @FinancialYearStart_from and @FinancialYearEnd_to
1097								and  [OfficeTypeName]='Local' and [Branch Code]=@DivisionCode)
1098				set @amount=ROUND((@amount/@DivisionNumber),2)
1099				exec('Update #OSMonth set ['+@col+']='''+@amount+''' where months=''MSF-Local''')
1100				set @amount_total=ISNULL(@amount_total,0)+isNull((convert(money,(@amount))),0)
1101
1102				--FOR CENTRAL
1103				set @Targetamount = (select isNull(Sum([Pending Amount]),0) as Total_Amount from #temp1_Final
1104							Where convert(datetime,'01' + [Bill Month] + ' ' + [Bill Year]) Between @FinancialYearStart_from and @FinancialYearEnd_to
1105								and  [OfficeTypeName]='Central' and [Branch Code]=@DivisionCode)
1106				set @Targetamount=ROUND((@Targetamount/@DivisionNumber),2)
1107				exec('Update #OSMonth set ['+@col+']='''+@Targetamount+''' where months=''MSF-Central''')
1108				set @Targetamount_total=ISNULL(@Targetamount_total,0)+isNull((convert(money,(@Targetamount))),0)
1109
1110				set @cntr=@cntr+1
1111				if @ReportType='M'
1112					set @FinancialYearStart=dateadd("M",1, @FinancialYearStart)
1113				else 
1114					set @FinancialYearStart=dateadd("M",3, @FinancialYearStart)
1115
1116				--Total Row
1117				exec('Update #OSMonth set ['+@col+']=(select sum(o.['+@col+']) from #OSMonth as o) where Months=''Total''')
1118			END
1119
1120			Alter Table #OSMonth add  Total varchar(50)
1121			Update #OSMonth set Total = @amount_total where Months='MSF-Local' 
1122			Update #OSMonth set Total = @Targetamount_total where Months='MSF-Central'
1123			--Total Row
1124			Update #OSMonth set Total=(select sum(convert(numeric(22,2),o.Total)) from #OSMonth as o) where Months='Total'
1125		
1126			--insert into #OSMonth(Months) values  ('Overall Total % -')
1127		
1128			--Update #OSMonth set Total = @amount_total - @Targetamount_total where Months='Overall Total % -' 
1129
1130			select * from #OSMonth
1131			drop table #OSMonth
1132		END
1133	END
1134
1135	if @ReportId=7 or @AllTogether='Y' --CREDIT CONTROL - DSO
1136	BEGIN
1137		if @DetailsType='S'
1138		BEGIN
1139			Create Table #DSOMonth (Branch Varchar(50))
1140			insert into #DSOMonth (Branch) values (@DivisionCode)
1141
1142			Select @SerFromDate=Convert (Date,FinancialYearStart,23),@SerToDate=convert(Date,FinancialYearEnd,23),
1143				@FinancialStart_year=DATEPART (Year,FinancialYearStart),@FinancialEnd_year=DATEPART (Year,FinancialYearEnd),
1144				@FinancialStart_Month=DATENAME (MONTH,FinancialYearStart),@FinancialEnd_Month=DATENAME (MONTH,FinancialYearEnd),
1145				@FinancialYearStart=Convert (Date,FinancialYearStart,23),@FinancialYearEnd=convert(Date,FinancialYearEnd,23),
1146				@FinancialYearStart_from=Convert (Date,FinancialYearStart,23),@FinancialYearEnd_to=convert(Date,FinancialYearEnd,23)
1147			from Accounts_Dem.dbo.CompanyFinancialYearMaster where FinancialYearCode=db_name() and Deleted=0
1148
1149			SET @col_month = @ToMonth
1150			SET @col_Year = @ToYear
1151
1152			Declare @MonthDate Varchar(50)=0 , @MonthDays int=0
1153			set @MonthDate= (select (convert(datetime,'01' + @ToMonth + ' ' + @ToYear)))
1154			set @MonthDays=(select DAY(DATEADD(d,-1,DATEADD(mm,DATEDIFF(m,0,@MonthDate)+1,0))))
1155		
1156			SET @col =@ToMonth+'-'+@ToYear+'- Billing'
1157			EXEC('Alter table #DSOMonth add ['+@col+'] money')
1158		
1159			set @amount = (select Sum(Convert(numeric(22,2),(TS.Amount*convert(numeric(22,6),(case ca.Total when 0 then 0 else (CCBU.Amount/ca.Total)*100 end)))/100)) as Total_Amount 
1160							from CCTxn CCBU with(NOLOCK)
1161								inner join Transactions TS with(NOLOCK) on TS.DocumentNo = CCBU.DocumentNo and TS.Deleted=0 and TS.GLType='D'
1162								INNER JOIN CostCentreMaster ccm WITH(NOLOCK) ON CCBU.CostCentreCode = ccm.CostCentreCode
1163								cross apply (Select convert(numeric(22,6),sum(cc.Amount)) as Total from CCTxn cc with (NOLOCK) 
1164									where cc.Deleted=0 and cc.DocumentNo=CCBU.DocumentNo and cc.GLCode=CCBU.GLCode group by cc.DocumentNo,cc.GLCode) ca
1165								INNER JOIN InvMain as im with(NOLOCK) ON im.Deleted=0 and im.DocNo=TS.DocumentNo
1166							Where CCBU.Deleted=0 and ccm.Deleted = 0 and Ts.TransactionType='SA' and ccm.DivisionCode=@DivisionCode 
1167								and im.SelMonth= @ToMonth and im.SelYear= @ToYear)
1168
1169			set @amount=ROUND((@amount/@DivisionNumber),2)
1170
1171			set @Targetamount = (select isNull(Sum([Pending Amount]),0) as Total_Amount from #temp1_Final
1172							Where [Branch Code]=@DivisionCode and convert(datetime, [Bill Month] + ' 01 ' + [Bill Year])<=convert(datetime,@ToMonth + ' 01 ' + @ToYear))
1173			set @Targetamount=ROUND((@Targetamount/@DivisionNumber),2)
1174
1175			EXEC('Alter table #DSOMonth add [Outstanding upto '+@ToMonth+'-'+@ToYear+'] money')
1176			exec('Update #DSOMonth set [Outstanding upto '+@ToMonth+'-'+@ToYear+']='''+@Targetamount+''' where Branch='''+@DivisionCode+'''')
1177
1178			exec('Update #DSOMonth set ['+@col+']='''+@amount+''' where Branch='''+@DivisionCode+'''')
1179
1180			Alter Table #DSOmonth add  [Per Day Sale] varchar(50), [Number of Days Sale Outstanding] varchar(50)
1181			print 'a'
1182			Update #DSOMonth set [Per Day Sale] = CONVERT(money, ROUND((@amount/convert(money,@MonthDays)),2)) where Branch=@DivisionCode
1183			print 'b'
1184			Update #DSOMonth set [Number of Days Sale Outstanding] = CAST((@Targetamount/(@amount/convert(float,@MonthDays)))As int) where Branch=@DivisionCode
1185
1186			select * from #DSOMonth
1187		
1188			drop table #DSOMonth
1189		END
1190	END
1191
1192	if @ReportId=8 or @AllTogether='Y' --CREDIT CONTROL - OLD OUTSTANDING
1193	BEGIN
1194		if @DetailsType='S'
1195		BEGIN
1196			Select @FinancialYearStart=Convert (Date,FinancialYearStart)
1197			from Accounts_Dem.dbo.CompanyFinancialYearMaster where FinancialYearCode=db_name() and Deleted=0
1198
1199			select top 5 [Party Name], ROUND((isNull(Sum([Pending Amount]),0)/@DivisionNumber),2) as Total_Amount from #temp1_Final 
1200			where convert(datetime,'01' + [Bill Month] + ' ' + [Bill Year])<= @FinancialYearStart
1201				and  [Branch Code]=@DivisionCode and [OfficeTypeName]='Local'
1202			group by [Party Name]	
1203			order by isNull(Sum([Pending Amount]),0) desc
1204		END
1205	END
1206
1207	if @ReportId=9 or @AllTogether='Y' --OPERATIONS MANAGEMENT - Customer Retention
1208	BEGIN
1209		declare @Disbandcount_total int=0, @Disbandcount_S_Total int=0
1210
1211		Select @SerFromDate=Convert (Date,FinancialYearStart,23), @SerToDate=convert(Date,FinancialYearEnd,23),
1212			@FinancialStart_year=DATEPART (Year,FinancialYearStart),@FinancialEnd_year=DATEPART (Year,FinancialYearEnd),
1213			@FinancialStart_Month=DATENAME (MONTH,FinancialYearStart),@FinancialEnd_Month=DATENAME (MONTH,FinancialYearEnd),
1214			@FinancialYearStart=Convert (Date,FinancialYearStart,23),@FinancialYearEnd=convert(Date,FinancialYearEnd,23),
1215			@FinancialYearStart_from=Convert (Date,FinancialYearStart,23),@FinancialYearEnd_to=convert(Date,FinancialYearEnd,23)
1216		from Accounts_Dem.dbo.CompanyFinancialYearMaster where FinancialYearCode=db_name() and Deleted=0
1217
1218		if @FinancialYearEnd>DATEADD("D",-1, DATEADD("M", 1, CONVERT(datetime,'01 ' + @ToMonth + ' ' + @ToYear)))
1219			set @FinancialYearEnd=DATEADD("D",-1, DATEADD("M", 1, CONVERT(datetime,'01 ' + @ToMonth + ' ' + @ToYear)))
1220
1221		if exists (select top 1 DivisionCode from Transactions with(NOLOCK) where Deleted=0 and TransactionType='SA' and DivisionCode=@DivisionCode)
1222		BEGIN
1223			if @DetailsType='S'
1224			BEGIN
1225				Create Table #DisMonth (Months Varchar(50))
1226				insert into #DisMonth (Months) values ('Disbandment Clients No.')
1227				insert into #DisMonth (Months) values ('Disbandment Sites No.')
1228				insert into #DisMonth (Months) values ('Disbandment Amount')
1229
1230				while @FinancialYearStart<=@FinancialYearEnd
1231				Begin
1232					--GET Month Year & add Columns in TempTable
1233					SET @col_month =Convert(varchar(20),DateName(month,@FinancialYearStart))
1234					SET @col_Year =Convert(varchar(20),DateName(YEAR,@FinancialYearStart))
1235
1236					SET @col =Convert(varchar(20),DateName(month,@FinancialYearStart))+'-'+convert(varchar(10),year(@FinancialYearStart))
1237					EXEC('Alter table #DisMonth add ['+@col+'] Varchar(50)')
1238				
1239					--GET DISBANDED CLIENT & SITE COUNT & Update in TempTable
1240					set @qry= 'select @Count=isnull(Count(Distinct ClientgroupID),0) from '+@MPDBName+'.dbo.SiteMaster  with(NOLOCK)
1241								where Deleted=0 and Convert(varchar(20),DateName(month,SiteEndDate))+''-''+Convert(varchar(20),DateName(YEAR,SiteEndDate))='''+@col+''''
1242					exec sp_executesql @qry,N'@Count varchar(200) output', @Count output
1243				
1244					set @qry= 'select @SCount=isnull(Count(*),0) from '+@MPDBName+'.dbo.SiteMaster  with(NOLOCK)
1245								where Deleted=0 and Convert(varchar(20),DateName(month,SiteEndDate))+''-''+Convert(varchar(20),DateName(YEAR,SiteEndDate))='''+@col+''''
1246					exec sp_executesql @qry, N'@SCount varchar(200) output',  @SCount output
1247				
1248					Exec('update #DisMonth set ['+@col+'] ='+@Count+' where Months=''Disbandment Clients No.''')
1249					Exec('update #DisMonth set ['+@col+'] ='+@SCount+' where Months=''Disbandment Sites No.''')
1250
1251					set @Disbandcount_total=ISNULL(@Disbandcount_total,0)+isNull((convert(money,(@Count))),0)
1252					set @Disbandcount_S_Total=ISNULL(@Disbandcount_S_Total,0)+isNull((convert(money,(@SCount))),0)
1253				
1254					--GET LAST MONTH SALES FOR THESE SITES
1255					set @qry=('select @amount=Sum(Convert(numeric(22,2),(ts.Amount*convert(numeric(22,6),(case ca.Total when 0 then 0 else (ccbu.Amount/ca.Total)*100 end)))/100)) 
1256								from CCTxn CCBU with(NOLOCK)
1257									inner join Transactions TS with(NOLOCK) on TS.DocumentNo = CCBU.DocumentNo and TS.Deleted=0 and TS.GLType=''D''
1258									inner join CostCentreMaster ccm with(NOLOCK) on ccbu.costcentrecode = ccm.costcentrecode
1259									INNER JOIN InvMain as im with(NOLOCK) ON im.Deleted=0 and im.DocNo=TS.DocumentNo
1260									cross apply (Select convert(numeric(22,6),sum(cc.Amount)) as Total from CCTxn cc with (NOLOCK) 
1261										where cc.Deleted=0 and cc.DocumentNo=ccbu.DocumentNo and cc.GLCode=ccbu.GLCode group by cc.DocumentNo,cc.GLCode) ca
1262								Where CCBU.Deleted=0 and ccm.deleted = 0  and Ts.TransactionType=''SA'' 
1263										and ccm.DivisionCode='''+@DivisionCode+'''
1264										and convert(datetime,''01 '' + im.selmonth + '' '' + im.selYear)
1265											=(select Max(convert(datetime,''01 '' + imm.selmonth + '' '' + imm.selYear)) 
1266												from InvMain as imm with(NOLOCK) INNER JOIN CCTxn as ct with(NOLOCK) ON imm.DocNo=ct.DocumentNo and ct.Deleted=0
1267												where imm.Deleted=0 and ct.CostCentreCOde=CCBU.CostCentreCode)
1268										and CCBU.CostCentreCode in (select s.Sitecode from  '+@MPDBName+'.dbo.SiteMaster as s
1269										where s.Deleted=0 and Convert(varchar(20),DateName(month,s.SiteEndDate))+''-''+Convert(varchar(20),DateName(YEAR,s.SiteEndDate))='''+@col+''')
1270										')
1271
1272					set @amount=ROUND((@amount/@DivisionNumber),2)
1273					exec sp_executesql @qry,N'@amount varchar(200) output', @amount output
1274					set @amount=ROUND((@amount / @DivisionNumber),2)
1275				
1276					exec('Update #DisMonth set ['+@col+']='''+@amount+''' where months=''Sales Amount''')
1277					set @amount_total=(@amount_total+isNull((convert(money,(@amount))),0))
1278
1279					--RESET COUNTER VARIABLES
1280					set @cntr=@cntr+1
1281					set @FinancialYearStart=dateadd("M",1, @FinancialYearStart)
1282				END
1283
1284				Alter Table #Dismonth add Total int
1285			
1286				Update #DisMonth set Total = @Disbandcount_total where months='Disbandment Clients No.' 
1287				Update #DisMonth set Total = @Disbandcount_S_Total where months='Disbandment Sites No.' 
1288				Update #DisMonth set Total = @amount_total where months='Sales Amount' 
1289
1290				select * from #DisMonth
1291				drop table #DisMonth
1292			END
1293			else
1294			BEGIN
1295				exec('select s.SiteCode, s.SiteName, c.ClientGroupName, convert(varchar(20),s.SiteEndDate,106) as SiteEndDate
1296					from '+@MPDBName+'.dbo.SiteMaster as s with(NOLOCK)
1297						INNER JOIN '+@MPDBName+'.dbo.ClientGroupMaster as c with(NOLOCK) ON c.Deleted=0 and c.ClientGroupMasterId=s.ClientGroupId
1298					where s.Deleted=0 and s.BranchID='+@MPBranchId+'
1299						and s.SiteEndDate<='''+@FinancialYearEnd+'''')
1300			END
1301		END
1302	END
1303
1304	if @ReportId=10 or @AllTogether='Y' --OPERATIONS MANAGEMENT - Major Disbandment
1305	BEGIN
1306		if @DetailsType='S'
1307		BEGIN
1308			Select @FinancialYearStart=Convert (Date,FinancialYearStart,23),@FinancialYearEnd=convert(Date,FinancialYearEnd,23)
1309			from Accounts_Dem.dbo.CompanyFinancialYearMaster where FinancialYearCode=db_name() and Deleted=0
1310
1311			exec ('select top 10 CG.ClientGroupName, SM.CostCentreCode as [Site Code], SM.Description as [Site Name], 
1312					ROUND((Sum(Convert(numeric(22,2),(ts.Amount*convert(numeric(22,6),(case ca.Total when 0 then 0 else (ccbu.Amount/ca.Total)*100 end)))/100))/'+@DivisionNumber+'),2) 
1313						as ''TotalAmount''
1314				from CCTxn CCBU with(NOLOCK)
1315					inner join Transactions TS with(NOLOCK) on TS.DocumentNo = CCBU.DocumentNo and TS.Deleted=0 and TS.GLType=''D''
1316					inner join CostCentreMaster ccm with (NOLOCK) on ccbu.costcentrecode = ccm.costcentrecode
1317					INNER JOIN InvMain as im with(NOLOCK) ON im.Deleted=0 and im.DocNo=TS.DocumentNo
1318					left join DebtInd DI on DI.GLcode=TS.GLcode and DI.Deleted=0
1319					left join ClientGroup CG on CG.CLientGroupId=DI.CLientGroupId and CG.Deleted=0
1320					LEFT OUTER JOIN CostCentreMaster as SM ON SM.Deleted=0 and SM.CostCentreCode=CCBU.CostCentreCode
1321					cross apply (Select convert(numeric(22,6),sum(cc.Amount)) as Total from CCTxn cc with (NOLOCK) 
1322						where cc.Deleted=0 and cc.DocumentNo=ccbu.DocumentNo and cc.GLCode=ccbu.GLCode group by cc.DocumentNo,cc.GLCode) ca
1323				Where CCBU.Deleted=0 and ccm.deleted = 0 and Ts.TransactionType=''SA'' 
1324					and convert(datetime,''01 '' + im.selmonth + '' '' + im.selYear)
1325						=(select Max(convert(datetime,''01 '' + imm.selmonth + '' '' + imm.selYear)) from InvMain as imm with(NOLOCK)
1326								INNER Join CCTXN as c with(NOLOCK) ON imm.DocNo=c.DocumentNo and c.Deleted=0
1327							where imm.Deleted=0 and c.CostCentreCOde=CCBU.CostCentreCode)
1328					and ccm.DivisionCode='''+@DivisionCode+''' 
1329					and CCBU.CostCentreCode in (select s.Sitecode from  '+@MPDBName+'.dbo.SiteMaster as s
1330								where s.Deleted=0 and s.SiteEndDate between '''+@FinancialYearStart+''' and '''+@FinancialYearEnd+''')
1331				group by CG.ClientGroupName, SM.CostCentreCode, SM.Description
1332				order by Sum(CCBU.Amount) desc')
1333		END
1334	END
1335
1336	if @ReportId=11 or @AllTogether='Y' --HR & Statutory
1337	BEGIN
1338		if @DetailsType='S'
1339		BEGIN
1340			exec('select BranchName,  ''GUARD'' as EmployeeType, count( Distinct   EM.EmpMasterID) ''Total GUARD STRENGTH'' ,
1341						Count(Distinct ESD.UANNo) ''UANNo Strength'', Cast((Count(Distinct ESD.UANNo)*100)/count(Distinct EM.EmpMasterID)  as float) ''UANNo %'',
1342						Count(Distinct ES.ESICNo) ''Esic No. Strength'',
1343						Convert(decimal(6,2),(Count(Distinct ES.ESICNo)*100/count(Distinct EM.EmpMasterID))) ''ESIC NO. %'',
1344						Count(Distinct Aadhar.DocumentNumber) ''Aadhar Card No. Strength'',
1345						Convert(float,(Count(Distinct Aadhar.DocumentNumber)*100/count(Distinct EM.EmpMasterID))) ''Aadhar Card No. %'',
1346						Count(Distinct Police.DocumentNumber) ''Police Verification'',Convert(float,(Count(Distinct Police.DocumentNumber)*100/count(Distinct EM.EmpMasterID))) ''Police Verification %'',
1347						Count(Distinct Traning.DocumentNumber) ''TRAINING CERTIFICATE'',Convert(float,(Count(Distinct Traning.DocumentNumber)*100/count(Distinct EM.EmpMasterID))) ''TRAINING CERTIFICATE %'',
1348						Count(Distinct Medical.DocumentNumber) ''Medical Certificate'',Convert(float,(Count(Distinct Medical.DocumentNumber)*100/count(Distinct EM.EmpMasterID))) ''Medical Certificate %''
1349					from '+@MPDBName+'.dbo.EmpMaster EM
1350						inner join '+@MPDBName+'.dbo.MonthAttendance_Report MAR on EM.EmpMasterID=MAR.EmpMasterId And MAR.Deleted=0 and MonthName='''+@FromMonth+''' and YearName='''+@FromYear+'''
1351						left outer join '+@MPDBName+'.dbo.EmpESICDetails ES  on EM.EmpMasterID=ES.EmpMasterID  AND Len(ESICNo)>=1  and ES.Deleted=0
1352						left outer join '+@MPDBName+'.dbo.EmpESICDetails ESD  on EM.EmpMasterID=ESD.EmpMasterID  AND  Len(ESD.UANNo)>=1 And ESD.Deleted=0
1353						left outer join '+@MPDBName+'.dbo.EmpDocumentDetails Aadhar  on EM.EmpmasterID=Aadhar.EmpMasterID and Len(DocumentNumber)>=1 and DocumentTypeID=5 and Aadhar.Deleted=0
1354						left outer join '+@MPDBName+'.dbo.EmpDocumentDetails Police   on EM.EmpmasterID=Police.EmpMasterID and Len(Police.DocumentNumber)>=1 and Police.DocumentTypeID=14 and Police.Deleted=0
1355						left outer join '+@MPDBName+'.dbo.EmpDocumentDetails Traning   on EM.EmpmasterID=Traning.EmpMasterID and Len(Traning.DocumentNumber)>=1 and Traning.DocumentTypeID=16 and Traning.Deleted=0
1356						left outer join '+@MPDBName+'.dbo.EmpDocumentDetails Medical   on EM.EmpmasterID=Medical.EmpMasterID and Len(Medical.DocumentNumber)>=1 and Medical.DocumentTypeID=17 and Medical.Deleted=0
1357						Inner join '+@MPDBName+'.dbo.BranchMaster  on MAR.BranchID=BranchMasterID and BranchMaster.Deleted=0
1358						--inner join '+@MPDBName+'.dbo.EmpStatusDetails esde on esde.EmpMasterID=EM.EmpMasterID and PresentStatus=1
1359					where MAR.BranchID='+@MPBranchId+' and EM.EmpType=''Front'' and EM.Deleted=0 and IsActive=1
1360					group by BranchName
1361						
1362				Union all
1363
1364				select BranchName, ''STAFF'' as EmployeeType, count( Distinct EM.EmpMasterID) ''Total GUARD STRENGTH'', 
1365					Count(Distinct ESD.UANNo) ''UANNo Strength'', Cast((Count(Distinct ESD.UANNo)*100)/count(Distinct EM.EmpMasterID)  as float) ''UANNo %'',
1366					Count(Distinct ES.ESICNo) ''Esic No. Strength'',
1367					Convert(decimal(6,2),(Count(Distinct ES.ESICNo)*100/count(Distinct EM.EmpMasterID))) ''ESIC NO. %'',
1368					Count(Distinct Aadhar.DocumentNumber) ''Aadhar Card No. Strength'',
1369					Convert(float,(Count(Distinct Aadhar.DocumentNumber)*100/count(Distinct EM.EmpMasterID))) ''Aadhar Card No. %'',
1370					Count(Distinct Police.DocumentNumber) ''Police Verification'',Convert(float,(Count(Distinct Police.DocumentNumber)*100/count(Distinct EM.EmpMasterID))) ''Police Verification %'',
1371					Count(Distinct Traning.DocumentNumber) ''TRAINING CERTIFICATE'',Convert(float,(Count(Distinct Traning.DocumentNumber)*100/count(Distinct EM.EmpMasterID))) ''TRAINING CERTIFICATE %'',
1372					Count(Distinct Medical.DocumentNumber) ''Medical Certificate'',Convert(float,(Count(Distinct Medical.DocumentNumber)*100/count(Distinct EM.EmpMasterID))) ''Medical Certificate %''
1373				from '+@MPDBName+'.dbo.EmpMaster EM
1374					inner join '+@MPDBName+'.dbo.StaffMonthAttendance_Report MAR on EM.EmpMasterID=MAR.EmpMasterId And MAR.Deleted=0 and MonthName='''+@FromMonth+''' and YearName='''+@FromYear+'''
1375					left outer join '+@MPDBName+'.dbo.EmpESICDetails ES  on EM.EmpMasterID=ES.EmpMasterID  AND Len(ESICNo)>=1  and ES.Deleted=0
1376					left outer join '+@MPDBName+'.dbo.EmpESICDetails ESD  on EM.EmpMasterID=ESD.EmpMasterID  AND  Len(ESD.UANNo)>=1 And ESD.Deleted=0
1377					left outer join '+@MPDBName+'.dbo.EmpDocumentDetails Aadhar  on EM.EmpmasterID=Aadhar.EmpMasterID and Len(DocumentNumber)>=1 and DocumentTypeID=5 and Aadhar.Deleted=0
1378					left outer join '+@MPDBName+'.dbo.EmpDocumentDetails Police   on EM.EmpmasterID=Police.EmpMasterID and Len(Police.DocumentNumber)>=1 and Police.DocumentTypeID=14 and Police.Deleted=0
1379					left outer join '+@MPDBName+'.dbo.EmpDocumentDetails Traning   on EM.EmpmasterID=Traning.EmpMasterID and Len(Traning.DocumentNumber)>=1 and Traning.DocumentTypeID=16 and Traning.Deleted=0
1380					left outer join '+@MPDBName+'.dbo.EmpDocumentDetails Medical   on EM.EmpmasterID=Medical.EmpMasterID and Len(Medical.DocumentNumber)>=1 and Medical.DocumentTypeID=17 and Medical.Deleted=0
1381					Inner join '+@MPDBName+'.dbo.BranchMaster  on MAR.BranchID=BranchMasterID and BranchMaster.Deleted=0
1382					--inner join '+@MPDBName+'.dbo.EmpStatusDetails esde on esde.EmpMasterID=EM.EmpMasterID and PresentStatus=1
1383				where MAR.BranchID='+@MPBranchId+' and EM.EmpType=''Back'' and EM.Deleted=0 and IsActive=1
1384				group by BranchName')
1385		END
1386	END
1387
1388	if @ReportId=12 or @AllTogether='Y' --PENDING UNIFORM
1389	BEGIN
1390		declare @UniformSalaryHeadId bigint, @curFYStartDate datetime, @EndFYStartDate datetime, @minFYStartDate datetime, @FromDate datetime, @ToDate datetime, 
1391			@colName varchar(50)
1392		set @qry='select top 1 @UniformSalaryHeadId=SalaryHeadMasterID from '+@MPDBName+'.dbo.SalaryHeadMaster WITH(nolock) 
1393			where SalaryHeadCODE in(''TMPADV'',''UNI'') and Deleted=0'
1394		exec sp_executesql @qry, N'@UniformSalaryHeadId INT output', @UniformSalaryHeadId OUTPUT
1395
1396		if @DetailsType='S'
1397		BEGIN
1398			declare @colRowTotal nvarchar(max)
1399			set @colRowTotal=''
1400			
1401			CREATE TABLE #UniformRecovery(UniformRecoveryId int identity(1,1), BranchId bigint, BranchName varchar(50))
1402
1403			EXEC('Insert into #UniformRecovery(BranchId, BranchName)
1404			select BranchMasterID, BranchName from '+@MPDBName+'.dbo.BranchMaster where Deleted=0 Order By BranchName')
1405
1406			Insert into #UniformRecovery(BranchId, BranchName)
1407			values(999, 'TOTAL')
1408
1409			if month(GETDATE())<=3
1410				set @curFYStartDate='04/01/' + convert(varchar(20),(YEAR(GETDATE()) - 1))
1411			else
1412				set @curFYStartDate='04/01/' + convert(varchar(20),YEAR(GETDATE()))
1413
1414			set @EndFYStartDate=DATEADD("YEAR",1,@curFYStartDate)
1415
1416			if ISNULL(@UniformSalaryHeadId,0)<>0
1417			BEGIN
1418				declare @minFY datetime
1419				SET @qry='select @minFY=min(EntryDate) from '+@MPDBName+'.dbo.EmployeeEarningDeduction as eed with(NOLOCK) 
1420					where eed.Deleted=0 and eed.SalaryHeadMasterID='+convert(varchar(20),@UniformSalaryHeadId)+'
1421						and eed.Amount > (select sum(emd.AMount) from '+@MPDBName+'.dbo.EmployeeMonthlyDeduction as emd with(NOLOCK) 
1422							where emd.Deleted=0 and emd.EmployeeEarningDeductionID=eed.EmployeeEarningDeductionID)'
1423				exec sp_executesql @qry, N'@minFY datetime OUTPUT', @minFY OUTPUT
1424	
1425				if MONTH(@minFY)<=3
1426					set @minFYStartDate='04/01/' + convert(varchar(20),(YEAR(@minFY) - 1))
1427				else
1428					set @minFYStartDate='04/01/' + convert(varchar(20),(YEAR(@minFY)))
1429
1430				while @minFYStartDate<@EndFYStartDate and @minFYStartDate<GETDATE()
1431				BEGIN
1432					set @FromDate=@minFYStartDate
1433					if @minFYStartDate>=@curFYStartDate
1434						set @ToDate=DATEADD("M",3,@minFYStartDate)
1435					else
1436						set @ToDate=DATEADD("YEAR",1,@minFYStartDate)
1437					set @ToDate=DATEADD("D",-1,@ToDate)
1438
1439					set @colName=DATENAME("MONTH",@FromDate) + '-' + convert(varchar(5),YEAR(@FromDate)) + ' to ' + DATENAME("MONTH",@ToDate) + '-' + convert(varchar(5),YEAR(@ToDate))
1440					exec('ALTER TABLE #UniformRecovery ADD ['+@colName+'] numeric (22,2) DEFAULT(0)')
1441
1442					exec('Update #UniformRecovery set ['+@colName+']=ISNULL((select sum(s.UniformPendingAmt) 
1443								from (select eed.Amount - ISNULL((select sum(emd.AMount) from '+@MPDBName+'.dbo.EmployeeMonthlyDeduction as emd with(NOLOCK) 
1444										where emd.Deleted=0 and emd.EmployeeEarningDeductionID=eed.EmployeeEarningDeductionID),0) as UniformPendingAmt
1445									from '+@MPDBName+'.dbo.EmployeeEarningDeduction as eed with(NOLOCK) where eed.BranchId=#UniformRecovery.BranchId and eed.Deleted=0 
1446										and eed.SalaryHeadMasterID='+@UniformSalaryHeadId+'
1447										and eed.EntryDate between '''+@FromDate+''' and '''+@ToDate+'''
1448										and eed.Amount > ISNULL((select sum(emd.AMount) from '+@MPDBName+'.dbo.EmployeeMonthlyDeduction as emd with(NOLOCK) 
1449											where emd.Deleted=0 and emd.EmployeeEarningDeductionID=eed.EmployeeEarningDeductionID),0)) as s),0)')
1450
1451					set @colRowTotal=@colRowTotal + ' + ISNULL(['+@colName+'],0)'
1452					exec('Update #UniformRecovery set ['+@colName+']=(select sum(ISNULL(['+@colName+'],0)) from #UniformRecovery)
1453						where BranchId=999
1454					')
1455
1456					if @minFYStartDate>=@curFYStartDate
1457						set @minFYStartDate=DATEADD("M",3,@minFYStartDate)
1458					else
1459						set @minFYStartDate=DATEADD("YEAR",1,@minFYStartDate)
1460				END
1461			END
1462
1463			ALTER TABLE #UniformRecovery ADD Total numeric(22,2)
1464			print @colRowTotal
1465			exeC('Update #UniformRecovery set Total=0 ' + @colRowTotal)
1466
1467			ALTER TABLE #UniformRecovery DROP COLUMN BranchId
1468
1469			select * from #UniformRecovery
1470
1471			drop table #UniformRecovery
1472		END
1473		else
1474		BEGIN
1475			exec('select e.EmpCode, ISNULL(e.FirstName,'''') + '' '' + ISNULL(e.MiddleName,'''') + '' '' + ISNULL(e.Surname,'''') as EmpName,
1476					eed.Reference, eed.Amount, convert(varchar(20),eed.EntryDate,106) as DeductionDate, 
1477					ISNULL(eed.MaxFinVoucherNo,'''') as AccountsVoucherNo, eed.Amount, 
1478					ISNULL((select sum(emd.AMount) from '+@MPDBName+'.dbo.EmployeeMonthlyDeduction as emd with(NOLOCK) 
1479									where emd.Deleted=0 and emd.EmployeeEarningDeductionID=eed.EmployeeEarningDeductionID),0) as Recd, 
1480					eed.Amount - ISNULL((select sum(emd.AMount) from '+@MPDBName+'.dbo.EmployeeMonthlyDeduction as emd with(NOLOCK) 
1481									where emd.Deleted=0 and emd.EmployeeEarningDeductionID=eed.EmployeeEarningDeductionID),0) as PendingAmt 
1482				from '+@MPDBName+'.dbo.EmployeeEarningDeduction as eed with(NOLOCK) 
1483					INNER JOIN '+@MPDBName+'.dbo.EmpMaster as e ON e.EmpMasterID=eed.EmpMasterID
1484				where eed.BranchId='+@MPBranchId+' and eed.Deleted=0 
1485					and eed.SalaryHeadMasterID='+@UniformSalaryHeadId+'
1486					and eed.Amount > ISNULL((select sum(emd.AMount) from '+@MPDBName+'.dbo.EmployeeMonthlyDeduction as emd with(NOLOCK) 
1487						where emd.Deleted=0 and emd.EmployeeEarningDeductionID=eed.EmployeeEarningDeductionID),0)')
1488		END
1489	END
1490END