/*

Unallocated Records Warning.sql

Identifies records in Dynamics SL Application database which need to be allocated but are not
Written by Gail Jones-Nemeth, Creative Associates Int'l

For questions contact her at:   gailjn@creativedc.com

Requires:
- Allocator module should be enabled, allocation methods should be configured and attached to projects;
- Used in all versions of V2015 and V2018


***************************************************************************

This source code is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.

This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
GNU General Public License for more details.

See the GNU General Public License at <http://www.gnu.org/licenses/>.

***************************************************************************

*/


IF OBJECT_ID('Tmp_Alloc_Records','U') IS NOT NULL DROP TABLE Tmp_APTran

--Retrieve unallocated records
select project, allocMthd,  Acct, fiscalno, TranDate, ErrMessage, count(*) as RecordCount, sum(amount) as TranAmt 
from (
		select left(pjtran.fiscalno,4) as FiscYr, pjtran.fiscalno,
			convert(char(12),trans_date,110) as TranDate,  
			convert(char(12),pjtran.crtd_datetime,110) as CrtdDate, 
			pjtran.system_cd, pjtran.project, pjt_entity,pjtran.acct,  
			amount, tr_comment, pjtran.batch_id, Batch_type, tr_id04 as Batnbr, 
			voucher_num as Refnbr, vendor_num, tr_status,alloc_flag,
			pjproj.project_desc, 
			case when pjproj.alloc_method_cd = '' and left(pjtran.project,2) = 'CN' 
					then 'Missing' else pjproj.alloc_method_cd End as AllocMthd,
			alloc_method2_cd, pjtran.Employee,
			pjproj.status_pa, pjproj.end_date, pjtran.gl_acct, pjtran.gl_subacct,
			case when pjtran.alloc_Flag = '' and tr_status = '' then 'Never Allocated, Not Transferred'
					when pjtran.alloc_flag <> '' and tr_status = '' then 'Allocated, Not Transferred'
				when pjtran.alloc_flag = '' and tr_status = 'B' then 'Never Allocated, Transferred'	
				when pjtran.alloc_flag = '' and tr_status = 'N' then 'Not Billable, not allocated'
				when pjtran.alloc_flag <> '' and tr_status = 'N' then 'Not Billable, allocated - correct, reflag'
			else 'investigate' End as ErrMessage, 
			pjtranex.tr_id11, pjtranex.lupd_datetime
		from [DATABASENAME].dbo.pjtran pjtran (nolock)
		inner join [DATABASENAME].dbo.pjacct (nolock) on pjtran.acct = pjacct.acct
		inner join [DATABASENAME].dbo.pjproj   (nolock) on pjtran.project = pjproj.project
		left outer join [DATABASENAME].dbo.pjtranex (nolock) on pjtranex.fiscalno = pjtran.fiscalno and pjtran.system_cd = pjtranex.system_cd and														pjtran.batch_id = pjtranex.batch_id and pjtran.detail_num = pjtranex.detail_num
		where pjtran. fiscalno >= (select pernbr from [DATABASENAME].dbo.glsetup) 
			and alloc_flag = ''
			and tr_status not in ( 'N')  
			and amount <> ''  
			and left(pjtran.project,2) in ('CN' )  --THIS IS HOW WE DETERMINE PROJECTS ARE TO BE ALLOCATED
			and acct_type not in ('AS','RV','NA' ) 
			and pjtran.system_cd not in ('BI','PA') 
			and batch_type not in ('CHRG')
			and pjtran.gl_acct > '4' and pjtran.gl_acct < '5' 
			and pjtran.amount <> 0
			and  pjtran.alloc_flag = '' 
			and tr_status <> 'N'
			and tr_status = 'N' 
		) s1 
group by project,Acct, fiscalno, trandate, ErrMessage, allocmthd having abs(sum(amount)) > 0.5
order by project, fiscalno, ErrMessage




Declare @Exist Integer
set @exist = (select count(*) from Tmp_Alloc_Records )

If  @exist >0 
   EXEC msdb.dbo.sp_send_dbmail
   @profile_name = 'SQLMAILprofilename',
  @recipients = 'USER@domain.com',
  -- @copy_recipients = 'SLHelp@CreativeDC.com', 
   @query = ' select TOP 300 FROM Tmp_Alloc_Records
			  order by project, Acct, fiscalno, ErrMessage
					',
   @subject = 'On-Demand Report - Unallocated Records from Prior Month',
   @body = 'On-Demand Report - Please review the Unallocated Costs spreadsheet as there are unallocated costs from prior periods. 
			Only the top 300 records are displayed.
			 
			',
   @attach_query_result_as_file = false,
   @query_result_width = 220

IF OBJECT_ID('Tmp_Alloc_Records','U') IS NOT NULL DROP TABLE Tmp_APTran