USE [P21]
GO

/****** Object:  UserDefinedFunction [dbo].[nutech_fn_terms_eval_report_test]    Script Date: 9/23/2024 6:34:24 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- ===================================================
-- Author:		Fred Rappuhn, NuTech Services, LLC
-- Create date: 05/17/2021
-- Description:	
-- ===================================================
CREATE FUNCTION [dbo].[nutech_fn_terms_eval_report_test] (
    @company_id  VARCHAR(8),
    @start_customer_id INT = 0,
	@end_customer_id INT = 999999,
    @territory_uid   INT,
    @days     INT,
    @user_id    VARCHAR(30),
    @list_id    DECIMAL(19, 0) = NULL,
    @rewards_program_uid INT = NULL,
    @lost_sales_uid  INT = NULL,
    @credit_status   VARCHAR(8) = NULL,
    @exclude_customers_in_campaign CHAR(1) = 'N',
    @include_prospects CHAR(1) = 'N',
    @salesrep_id VARCHAR(16) = NULL,
    @include_ship2_salesreps CHAR(1) = 'N'
)
RETURNS @terms_eval_report TABLE 
(	
    [company_id] [varchar](8) NOT NULL,
    [customer_id] [decimal](19, 0) NOT NULL primary key,
    [customer_name] [varchar](255) NULL,
    [last_30_days_sales] [decimal](19, 2) NULL,
    [last_60_days_sales] [decimal](19, 2) NULL,
    [last_90_days_sales] [decimal](19, 2) NULL,
    [last_365_days_sales] [decimal](19, 2) NULL,
    [last_365_days_comm_cost] [decimal](19, 9) NULL,
    [last_365_days_other_cost] [decimal](19, 9) NULL,
    [last_365_days_freight_billed] [decimal](19, 4) NULL,
    [last_365_days_freight_unbilled] [decimal](19, 4) NULL,
    [previous_year_sales] [decimal](19, 2) NULL,
    [date_acct_opened] [datetime] NULL,
    [last_hard_touch_date] [datetime] NULL,
    [open_order_value] [decimal](19, 9) NULL,
    [open_quote_value] [decimal](19, 9) NULL,
    [open_opportunity_value] [decimal](19, 9) NULL,
    [number_of_opportunities] [int] NULL,
    [average_dso] [decimal](19, 9) NULL,
    [ytd_prct_amt_won] [decimal](19, 9) NULL,
    [ytd_prct_lines_won] [decimal](19, 9) NULL,
    [rma_value_last_365] [decimal](19, 9) NULL,
    [order_value_last_365] [decimal](19, 9) NULL,
    [last_365_days_cogs] [decimal](19, 9) NULL,
    [days_since_last_order] [int] NULL,
    [days_since_first_order] [int] NULL,
    [number_of_orders] [int] NULL,
    [days_since_last_quote] [int] NULL,
    [days_since_first_quote] [int] NULL,
    [number_of_quotes] [int] NULL,
    [sic_code] [decimal](6, 0) NULL,
    [sic_description] [varchar](40) NULL,
    [lead_source_id] [varchar](8) NULL,
    [source_description] [varchar](30) NULL,
    [class_1id] [varchar](8) NULL,
    [class_2id] [varchar](8) NULL,
    [class_3id] [varchar](8) NULL,
    [class_4id] [varchar](8) NULL,
    [class_5id] [varchar](8) NULL,
    [salesrep_id] [varchar](16) NULL,
    [phys_address1] [varchar](50) NULL,
    [phys_address2] [varchar](50) NULL,
    [phys_address3] [varchar](50) NULL,
    [phys_city] [varchar](50) NULL,
    [phys_state] [varchar](50) NULL,
    [phys_postal_code] [varchar](10) NULL,
    [phys_country] [varchar](50) NULL,
    [central_phone_number] [varchar](20) NULL,
    [new_customer_flag] [char](1) NULL,
    [salesrep_name] [varchar](255) NULL,
    [in_rewards_program_flag] [char](1) NULL,
    [next_task_to_complete] [varchar](16) NULL,
    [last_completed_task] [varchar](16) NULL,
    [customer_category_uid] [int] NULL,
    [warehouse_size_cd] [int] NULL,
    [retail_size_cd] [int] NULL,
    [roa_0_to_30] [decimal](19, 4) NULL,
    [roa_31_to_60] [decimal](19, 4) NULL,
    [roa_61_to_90] [decimal](19, 4) NULL,
    [gap_0_to_30] [decimal](19, 4) NULL,
    [gap_31_to_60] [decimal](19, 4) NULL,
    [gap_61_to_90] [decimal](19, 4) NULL,
    [missed_buy_flag] [char](1) NULL,
    [credit_status] [char](8) NULL,
    [average_order_size] [decimal](19, 4) NULL,
    [avg_days_between_orders] [int] NULL,
    [date_last_invoiced] [datetime] NULL,
    [total_orders] [int] NULL,
    [total_quotes] [int] NULL,
    [cost_to_carry_late_invoices] [decimal](19, 2) NULL,
	[last_payment_date] [datetime] NULL,
	[last_payment_amount] [decimal](19, 4) NULL,
	[terms_desc] [varchar](20) NULL,
	[credit_limit] [decimal](19, 4) NULL,
	[high_credit_limit_used] [decimal](19, 4) NULL,
	[high_credit_limit_used_mtd] [decimal](19, 4) NULL,
	[high_credit_limit_used_ytd] [decimal](19, 4) NULL,
	[avg_fast_slow_days_last_completed_month] [decimal](19, 4) NULL,
	[avg_fast_slow_days_last_completed_3months] [decimal](19, 4) NULL,
	[last_order_date] [datetime] NULL,
	[last_order_amount] [decimal](19, 4) NULL,
	[last_invoice_date] [datetime] NULL,
	[last_invoice_amount] [decimal](19, 4) NULL,
	[open_ar_amount] [decimal](19, 4) NULL,
	[ar_30_days] [decimal](19, 4) NULL,
	[ar_31_60_days] [decimal](19, 4) NULL,
	[ar_61_90_days] [decimal](19, 4) NULL,
	[ar_91_120_days] [decimal](19, 4) NULL,
	[ar_121_150_days] [decimal](19, 4) NULL,
	[ar_151_180_days] [decimal](19, 4) NULL,
	[ar_181_210_days] [decimal](19, 4) NULL,
	[ar_211_240_days] [decimal](19, 4) NULL,
	[ar_241_270_days] [decimal](19, 4) NULL,
	[ar_271_300_days] [decimal](19, 4) NULL,
	[ar_301_330_days] [decimal](19, 4) NULL,
	[ar_331_360_days] [decimal](19, 4) NULL,
	[ar_361_390_days] [decimal](19, 4) NULL,
	[ar_391_420_days] [decimal](19, 4) NULL,
	[ar_421_450_days] [decimal](19, 4) NULL,
	[ar_451_480_days] [decimal](19, 4) NULL,
	[ar_481_510_days] [decimal](19, 4) NULL,
	[ar_511_540_days] [decimal](19, 4) NULL,
    [ar_over_540_days] [decimal](19, 4) NULL,
    [invoiced_sales_ytd] [decimal](19, 4) NULL,
    [invoiced_sales_prior_ytd] [decimal](19, 4) NULL,
    [invoiced_sales_mtd] [decimal](19, 4) NULL,
    [invoiced_sales_prior_month] [decimal](19, 4) NULL,
	[email_address] [varchar](255) NULL,
	[invoice_batch_desc] [varchar](40) NULL
)
AS
BEGIN
--SET NOCOUNT ON

	DECLARE @CURRENT_TIMESTAMP			DATETIME    
	DECLARE @strategic_pricing_enabled	CHAR(1)    
	DECLARE @yearsToReview				INT = 2

	DECLARE @year_period				INT;
	DECLARE @CURRENT_YEAR				INT;
	DECLARE @CURRENT_MONTH				INT;
	DECLARE @LAST_MONTH_YEAR			INT;
	DECLARE @LAST_MONTH					INT;
	DECLARE @DATE_FIRSTDAY3MONTHS	DATETIME;
	DECLARE @DATE_FIRSTDAYLASTMONTH	DATETIME;
	DECLARE @DATE_LASTDAYLASTMONTH	DATETIME;


	-- Store current time as a variable instead of calling the function multiple times.    
	SET @CURRENT_TIMESTAMP = dbo.p21_fn_GetSystemDatetime(CURRENT_TIMESTAMP,NULL,NULL)    

	select @CURRENT_YEAR = Year(@CURRENT_TIMESTAMP), @CURRENT_MONTH = Month(@CURRENT_TIMESTAMP),
	       @LAST_MONTH_YEAR = Year(DateAdd(m,-1,@CURRENT_TIMESTAMP)), @LAST_MONTH = Month(DateAdd(m,-1,@CURRENT_TIMESTAMP))

    SELECT @year_period = (@CURRENT_YEAR*1000) + @CURRENT_MONTH

    SELECT @DATE_FIRSTDAY3MONTHS = DATEADD(mm, DATEDIFF(mm, 0, @CURRENT_TIMESTAMP) - 3, 0),
           @DATE_FIRSTDAYLASTMONTH = DATEADD(mm, DATEDIFF(mm, 0, @CURRENT_TIMESTAMP) - 0, 0),
		   @DATE_LASTDAYLASTMONTH = DATEADD(ms,-2, DATEADD(month, DATEDIFF(month, 0, @CURRENT_TIMESTAMP), 0))

	-- See if strategic pricing is enabled.    
	SELECT @strategic_pricing_enabled = CASE    
		   WHEN COALESCE(value, '') = '' THEN 'N'    
		   ELSE 'Y'    
		   END    
	FROM system_setting    
	WHERE name = 'strategic_pricing_enabled_key'    

--	SELECT @yearsToReview = COALESCE(value, 2) --default to 2 years      
--	FROM system_setting        
--	WHERE name = 'years_to_review_in_smi'     

	-- Populate table with Customer Data.    
	INSERT INTO @terms_eval_report    
			( company_id    
			,customer_id    
			,customer_name    
			,date_acct_opened    
			,sic_code    
			,sic_description    
			,lead_source_id     
			,source_description    
			,class_1id    
			,class_2id    
			,class_3id    
			,class_4id    
			,class_5id    
			,salesrep_id    
			,phys_address1    
			,phys_address2   
			,phys_address3   
			,phys_city    
			,phys_state    
			,phys_postal_code    
			,phys_country    
			,central_phone_number    
			,last_hard_touch_date    
			,salesrep_name    
			,in_rewards_program_flag    
			,customer_category_uid    
			,warehouse_size_cd    
			,retail_size_cd    
			,credit_status
			,credit_limit
			,terms_desc)    
		SELECT	customer.company_id    
				,customer.customer_id    
				,customer.customer_name    
				,customer.date_acct_opened    
				,customer.sic_code    
				,COALESCE(dbo.p21_view_sic.sic_description, '')    
				,customer.lead_source_id    
				,COALESCE(dbo.p21_view_lead_source.source_description, '')    
				,customer.class_1id    
				,customer.class_2id    
				,customer.class_3id    
				,customer.class_4id    
				,customer.class_5id    
				,customer.salesrep_id                  
				,dbo.p21_view_address.phys_address1    
				,dbo.p21_view_address.phys_address2   
				,dbo.p21_view_address.phys_address3   
				,dbo.p21_view_address.phys_city    
				,dbo.p21_view_address.phys_state    
				,dbo.p21_view_address.phys_postal_code    
				,dbo.p21_view_address.phys_country    
				,dbo.p21_view_address.central_phone_number    
				,dbo.p21_view_crm_contact_information.last_hard_touch_date    
				,COALESCE(dbo.p21_view_contacts.last_name + ', ', '') + COALESCE(dbo.p21_view_contacts.first_name, '') AS salesrep_name    
				,'N'    
				,dbo.p21_view_customer_strategic_pricing.customer_category_uid    
				,dbo.p21_view_customer_strategic_pricing.warehouse_size_cd    
				,dbo.p21_view_customer_strategic_pricing.retail_size_cd    
				,customer.credit_status
				,customer.credit_limit
				,dbo.p21_view_terms.terms_desc
		FROM P21_fnt_get_customers(@user_id) AS customer    
		INNER JOIN dbo.p21_view_address ON (dbo.p21_view_address.id = customer.customer_id)    
		INNER JOIN dbo.p21_view_contacts ON (dbo.p21_view_contacts.id = customer.salesrep_id)    
		LEFT JOIN dbo.p21_view_crm_contact_information ON (dbo.p21_view_crm_contact_information.entity_type_cd = 1203)    
			   AND (dbo.p21_view_crm_contact_information.entity_link_id_dec = customer.customer_id)    
			   AND (dbo.p21_view_crm_contact_information.company_id = customer.company_id)    
		LEFT JOIN dbo.p21_view_customer_x_rewards_program ON (dbo.p21_view_customer_x_rewards_program.company_id = customer.company_id)    
			   AND (dbo.p21_view_customer_x_rewards_program.customer_id = customer.customer_id)    
			   AND (dbo.p21_view_customer_x_rewards_program.rewards_program_uid = @rewards_program_uid)    
			   AND (dbo.p21_view_customer_x_rewards_program.row_status_flag = 704)    
		LEFT JOIN dbo.p21_view_customer_strategic_pricing ON (dbo.p21_view_customer_strategic_pricing.customer_id = customer.customer_id)    
			   AND (dbo.p21_view_customer_strategic_pricing.company_id = customer.company_id)    
		LEFT JOIN(SELECT	dbo.p21_view_territory_x_customer.company_id    
						   ,dbo.p21_view_territory_x_customer.customer_id    
				  FROM		dbo.p21_view_territory_x_customer     
				  WHERE		dbo.p21_view_territory_x_customer.territory_uid = @territory_uid    
					AND		dbo.p21_view_territory_x_customer.row_status_flag <> 700
				  GROUP BY dbo.p21_view_territory_x_customer.company_id    
							,dbo.p21_view_territory_x_customer.customer_id) AS drv_territory ON (drv_territory.company_id = customer.company_id)    
																			   AND (drv_territory.customer_id = customer.customer_id)    
		LEFT JOIN dbo.p21_view_sic ON (dbo.p21_view_sic.sic_code = customer.sic_code)    
		LEFT JOIN dbo.p21_view_lead_source ON (dbo.p21_view_lead_source.source_id = customer.lead_source_id)
        INNER JOIN dbo.p21_view_terms ON dbo.p21_view_terms.terms_id = customer.terms_id
		WHERE (    
		 @company_id IS NULL    
		 OR customer.company_id = @company_id    
		)    
		AND (    
		 COALESCE(@rewards_program_uid, 0) = 0    
		 OR dbo.p21_view_customer_x_rewards_program.rewards_program_uid IS NOT NULL    
		)    
		AND (    
		 COALESCE(@credit_status, '') = ''    
		 OR customer.credit_status = @credit_status    
		)    
		AND (    
		 COALESCE(@territory_uid, 0) = 0    
		 OR drv_territory.customer_id IS NOT NULL    
		)       
		AND (( customer.customer_type_cd = 1203 AND @include_prospects <> 'O' ) OR @include_prospects = 'Y' OR ( @include_prospects = 'O' AND customer.customer_type_cd = 1204 ))  
		AND customer.delete_flag = 'N'
		AND customer.customer_id >= @start_customer_id
		AND customer.customer_id <= @end_customer_id


 



	DECLARE @comparison_date DATETIME 
	SET  @comparison_date = DATEADD(DAY, -1 * (@days) , CAST(@CURRENT_TIMESTAMP AS VARCHAR(11)))    



-- 11 seconds
    UPDATE crm
	SET  open_ar_amount = artb_info.open_ar_amount,
	     ar_30_days = artb_info.ar_30_days,
	     ar_31_60_days = artb_info.ar_31_60_days,
	     ar_61_90_days = artb_info.ar_61_90_days,
	     ar_91_120_days = artb_info.ar_91_120_days,
	     ar_121_150_days = artb_info.ar_121_150_days,
	     ar_151_180_days = artb_info.ar_151_180_days,
	     ar_181_210_days = artb_info.ar_181_210_days,
	     ar_211_240_days = artb_info.ar_211_240_days,
	     ar_241_270_days = artb_info.ar_241_270_days,
	     ar_271_300_days = artb_info.ar_271_300_days,
	     ar_301_330_days = artb_info.ar_301_330_days,
	     ar_331_360_days = artb_info.ar_331_360_days,
	     ar_361_390_days = artb_info.ar_361_390_days,
	     ar_391_420_days = artb_info.ar_391_420_days,
	     ar_421_450_days = artb_info.ar_421_450_days,
	     ar_451_480_days = artb_info.ar_451_480_days,
	     ar_481_510_days = artb_info.ar_481_510_days,
	     ar_511_540_days = artb_info.ar_511_540_days,
	     ar_over_540_days = artb_info.ar_over_540_days
	FROM @terms_eval_report crm   
	LEFT JOIN (SELECT artb.company_id, artb.customer_id,
	             sum(artb.total_amount + artb.memo_amount + artb.bad_debt_amount - artb.allowed_amount - artb.terms_amount - artb.tax_terms_taken_amt - artb.total_receipts) open_ar_amount
                ,SUM(CASE WHEN DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) <= 30 THEN (artb.total_amount + artb.memo_amount + artb.bad_debt_amount - artb.allowed_amount - artb.terms_amount - artb.tax_terms_taken_amt - artb.total_receipts) ELSE 0.0 END) AS ar_30_days
                ,SUM(CASE WHEN DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) BETWEEN 31 AND 60 THEN (artb.total_amount + artb.memo_amount + artb.bad_debt_amount - artb.allowed_amount - artb.terms_amount - artb.tax_terms_taken_amt - artb.total_receipts) ELSE 0.0 END) AS ar_31_60_days
  	            ,SUM(CASE WHEN DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) > 60 AND DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) <= 90 THEN (artb.total_amount + artb.memo_amount + artb.bad_debt_amount - artb.allowed_amount - artb.terms_amount - artb.tax_terms_taken_amt - artb.total_receipts) ELSE 0.0 END) AS ar_61_90_days
  	            ,SUM(CASE WHEN DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) > 90 AND DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) <= 120 THEN (artb.total_amount + artb.memo_amount + artb.bad_debt_amount - artb.allowed_amount - artb.terms_amount - artb.tax_terms_taken_amt - artb.total_receipts) ELSE 0.0 END) AS ar_91_120_days
  	            ,SUM(CASE WHEN DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) > 120 AND DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) <= 150 THEN (artb.total_amount + artb.memo_amount + artb.bad_debt_amount - artb.allowed_amount - artb.terms_amount - artb.tax_terms_taken_amt - artb.total_receipts) ELSE 0.0 END) AS ar_121_150_days
  	            ,SUM(CASE WHEN DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) > 150 AND DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) <= 180 THEN (artb.total_amount + artb.memo_amount + artb.bad_debt_amount - artb.allowed_amount - artb.terms_amount - artb.tax_terms_taken_amt - artb.total_receipts) ELSE 0.0 END) AS ar_151_180_days
  	            ,SUM(CASE WHEN DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) > 180 AND DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) <= 210 THEN (artb.total_amount + artb.memo_amount + artb.bad_debt_amount - artb.allowed_amount - artb.terms_amount - artb.tax_terms_taken_amt - artb.total_receipts) ELSE 0.0 END) AS ar_181_210_days
  	            ,SUM(CASE WHEN DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) > 210 AND DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) <= 240 THEN (artb.total_amount + artb.memo_amount + artb.bad_debt_amount - artb.allowed_amount - artb.terms_amount - artb.tax_terms_taken_amt - artb.total_receipts) ELSE 0.0 END) AS ar_211_240_days
  	            ,SUM(CASE WHEN DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) > 240 AND DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) <= 270 THEN (artb.total_amount + artb.memo_amount + artb.bad_debt_amount - artb.allowed_amount - artb.terms_amount - artb.tax_terms_taken_amt - artb.total_receipts) ELSE 0.0 END) AS ar_241_270_days
  	            ,SUM(CASE WHEN DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) > 270 AND DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) <= 300 THEN (artb.total_amount + artb.memo_amount + artb.bad_debt_amount - artb.allowed_amount - artb.terms_amount - artb.tax_terms_taken_amt - artb.total_receipts) ELSE 0.0 END) AS ar_271_300_days
  	            ,SUM(CASE WHEN DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) > 300 AND DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) <= 330 THEN (artb.total_amount + artb.memo_amount + artb.bad_debt_amount - artb.allowed_amount - artb.terms_amount - artb.tax_terms_taken_amt - artb.total_receipts) ELSE 0.0 END) AS ar_301_330_days
  	            ,SUM(CASE WHEN DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) > 330 AND DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) <= 360 THEN (artb.total_amount + artb.memo_amount + artb.bad_debt_amount - artb.allowed_amount - artb.terms_amount - artb.tax_terms_taken_amt - artb.total_receipts) ELSE 0.0 END) AS ar_331_360_days
  	            ,SUM(CASE WHEN DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) > 360 AND DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) <= 390 THEN (artb.total_amount + artb.memo_amount + artb.bad_debt_amount - artb.allowed_amount - artb.terms_amount - artb.tax_terms_taken_amt - artb.total_receipts) ELSE 0.0 END) AS ar_361_390_days
  	            ,SUM(CASE WHEN DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) > 390 AND DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) <= 420 THEN (artb.total_amount + artb.memo_amount + artb.bad_debt_amount - artb.allowed_amount - artb.terms_amount - artb.tax_terms_taken_amt - artb.total_receipts) ELSE 0.0 END) AS ar_391_420_days
  	            ,SUM(CASE WHEN DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) > 420 AND DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) <= 450 THEN (artb.total_amount + artb.memo_amount + artb.bad_debt_amount - artb.allowed_amount - artb.terms_amount - artb.tax_terms_taken_amt - artb.total_receipts) ELSE 0.0 END) AS ar_421_450_days
  	            ,SUM(CASE WHEN DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) > 450 AND DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) <= 480 THEN (artb.total_amount + artb.memo_amount + artb.bad_debt_amount - artb.allowed_amount - artb.terms_amount - artb.tax_terms_taken_amt - artb.total_receipts) ELSE 0.0 END) AS ar_451_480_days
  	            ,SUM(CASE WHEN DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) > 480 AND DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) <= 510 THEN (artb.total_amount + artb.memo_amount + artb.bad_debt_amount - artb.allowed_amount - artb.terms_amount - artb.tax_terms_taken_amt - artb.total_receipts) ELSE 0.0 END) AS ar_481_510_days
  	            ,SUM(CASE WHEN DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) > 510 AND DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) <= 540 THEN (artb.total_amount + artb.memo_amount + artb.bad_debt_amount - artb.allowed_amount - artb.terms_amount - artb.tax_terms_taken_amt - artb.total_receipts) ELSE 0.0 END) AS ar_511_540_days
  	            ,SUM(CASE WHEN DATEDIFF(dd, artb.invoice_date, @CURRENT_TIMESTAMP) > 540 THEN (artb.total_amount + artb.memo_amount + artb.bad_debt_amount - artb.allowed_amount - artb.terms_amount - artb.tax_terms_taken_amt - artb.total_receipts) ELSE 0.0 END) AS ar_over_540_days
               FROM dbo.p21_fnt_artb(@year_period, '10', 'Y', '0', 'zzzzzzzz', 0, @CURRENT_TIMESTAMP, 'N', @START_CUSTOMER_ID, @END_CUSTOMER_ID) artb
               WHERE ( artb.total_receipts <> ((artb.total_amount + artb.memo_amount + artb.bad_debt_amount) - artb.allowed_amount - artb.terms_amount - artb.tax_terms_taken_amt) ) 
                   AND artb.approved = 'Y'
               GROUP BY artb.company_id, artb.customer_id
			  ) AS artb_info ON (artb_info.company_id = crm.company_id)
                    AND (artb_info.customer_id = crm.customer_id)


    UPDATE crm
	SET  email_address = contact_info.email_address
	    ,invoice_batch_desc = contact_info.invoice_batch_desc
	FROM @terms_eval_report crm   
	LEFT JOIN (SELECT '10' AS company_id, dbo.p21_view_customer.customer_id, 
                 CASE WHEN dbo.p21_view_customer.invoice_batch_uid = 3 
	                  THEN '1' + replace(dbo.p21_view_contacts.direct_fax, ' ', '') + '@nextivafax.com' 
			          ELSE dbo.p21_view_contacts.email_address
			          END AS email_address,
	             dbo.p21_view_invoice_batch.invoice_batch_desc, dbo.p21_view_contacts.direct_fax,
	             REPLACE(dbo.p21_view_contacts.direct_phone, ' ', '.') AS direct_phone
               FROM dbo.p21_view_contacts 
               INNER JOIN dbo.p21_view_customer ON 
	             dbo.p21_view_contacts.id = dbo.p21_view_customer.bill_to_contact_id
	           INNER JOIN dbo.p21_view_invoice_batch ON
	             dbo.p21_view_customer.invoice_batch_uid = dbo.p21_view_invoice_batch.invoice_batch_uid
	           INNER JOIN dbo.p21_view_address ON
	             dbo.p21_view_contacts.address_id = dbo.p21_view_address.id
             WHERE (dbo.p21_view_contacts.delete_flag = 'N') 
               AND (dbo.p21_view_customer.delete_flag = 'N') 
               AND (dbo.p21_view_customer.bill_to_contact_id IS NOT NULL) 
			 ) AS contact_info ON (contact_info.company_id = crm.company_id)
                              AND (contact_info.customer_id = crm.customer_id)

return

END

GO


