USE [P21]
GO

/****** Object:  UserDefinedFunction [dbo].[nutech_fn_terms_eval_report]    Script Date: 9/23/2024 6:33:59 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] (
    @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_over_90_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


	-- Lost Sales    
	-- Delete customers that haven't had a lost sale for the lost sale parameter.    
	IF (COALESCE(@lost_sales_uid, 0) <> 0)    
	BEGIN    
	DELETE crm
	FROM @terms_eval_report crm
	WHERE NOT EXISTS( SELECT 1    
	   FROM dbo.p21_view_lost_sales_transaction    
	   INNER JOIN dbo.p21_view_lost_sales ON (dbo.p21_view_lost_sales.lost_sales_uid = dbo.p21_view_lost_sales_transaction.lost_sales_uid)    
	   -- Orders, Quotes, RMAs    
	   LEFT JOIN dbo.p21_view_oe_hdr ON (dbo.p21_view_oe_hdr.order_no = CAST(dbo.p21_view_lost_sales_transaction.transaction_no AS VARCHAR(20)))    
		   AND (dbo.p21_view_lost_sales_transaction.transaction_code_no IN (2143, 2148, 2145, 2707, 2846, 2847, 2144, 2142))    
	   -- Shipments    
	   LEFT JOIN dbo.p21_view_oe_pick_ticket ON (dbo.p21_view_oe_pick_ticket.pick_ticket_no = dbo.p21_view_lost_sales_transaction.transaction_no)    
			 AND (dbo.p21_view_lost_sales_transaction.transaction_code_no IN (2146, 2147, 2149))    
	   LEFT JOIN dbo.p21_view_oe_hdr AS pick_ticket_oe_hdr ON (pick_ticket_oe_hdr.order_no = dbo.p21_view_oe_pick_ticket.order_no)    
	   -- Opportunity    
	   LEFT JOIN dbo.p21_view_opportunity ON (dbo.p21_view_opportunity.opportunity_id = dbo.p21_view_lost_sales_transaction.transaction_no)    
			 AND (dbo.p21_view_lost_sales_transaction.transaction_code_no = 2714)    
	   WHERE dbo.p21_view_lost_sales_transaction.lost_sales_uid = @lost_sales_uid    
		 AND dbo.p21_view_lost_sales.company_id = crm.company_id    
		 AND COALESCE(dbo.p21_view_oe_hdr.customer_id, pick_ticket_oe_hdr.customer_id, dbo.p21_view_opportunity.customer_id) = crm.customer_id    
		)    
	END    

	-- 12 Month Rolling Sales    
	UPDATE	crm    
	SET		last_30_days_sales = COALESCE(v_customer_credit_history_daily.last_30_days_sales, 0) -- last_30_days_sales    
			,last_60_days_sales = COALESCE(v_customer_credit_history_daily.last_60_days_sales, 0) -- last_60_days_sales    
			,last_90_days_sales = COALESCE(v_customer_credit_history_daily.last_90_days_sales, 0) -- last_90_days_sales    
			,last_365_days_sales = COALESCE(v_customer_credit_history_daily.last_365_days_sales, 0) -- last_365_days_sales    
			,previous_year_sales = COALESCE(v_customer_credit_history_daily.previous_year_sales, 0) -- previous_year_sales     
			,last_365_days_cogs = COALESCE(v_customer_credit_history_daily.last_365_days_cogs, 0) -- last_365_days_cogs    
			,last_365_days_comm_cost = COALESCE(v_customer_credit_history_daily.last_365_days_comm_cost, 0) -- last_365_days_comm_cost    
			,last_365_days_other_cost = COALESCE(v_customer_credit_history_daily.last_365_days_other_cost, 0) -- last_365_days_other_cost    
			,last_365_days_freight_billed = COALESCE(v_customer_credit_history_daily.last_365_days_freight_billed, 0) -- last_365_days_freight_billed    
			,last_365_days_freight_unbilled = COALESCE(v_customer_credit_history_daily.last_365_days_freight_unbilled, 0) -- last_365_days_freight_unbilled    
			,new_customer_flag = CASE     
				 WHEN date_acct_opened IS NOT NULL AND DATEDIFF(dd, crm.date_acct_opened, @CURRENT_TIMESTAMP) <= 365 THEN 'Y'     
				 WHEN date_acct_opened IS NULL AND (v_customer_credit_history_daily.last_365_days_sales <> 0 AND v_customer_credit_history_daily.previous_year_sales = 0) THEN 'Y'    
				 ELSE 'N'    
				 END        
			,date_last_invoiced = v_customer_credit_history_daily.date_last_invoiced
			,last_invoice_date = v_customer_credit_history_daily.date_last_invoiced
			,high_credit_limit_used = v_customer_credit_history_daily.high_credit_limit_used
			,high_credit_limit_used_mtd = v_customer_credit_history_daily.high_credit_limit_used_mtd
			,high_credit_limit_used_ytd = v_customer_credit_history_daily.high_credit_limit_used_ytd
            ,invoiced_sales_ytd = v_customer_credit_history_daily.invoiced_sales_ytd
            ,invoiced_sales_prior_ytd = v_customer_credit_history_daily.invoiced_sales_prior_ytd
            ,invoiced_sales_mtd = v_customer_credit_history_daily.invoiced_sales_mtd
            ,invoiced_sales_prior_month = v_customer_credit_history_daily.invoiced_sales_prior_month
			,avg_fast_slow_days_last_completed_month = v_customer_credit_history_daily.sum_days_x_payment_prior_month/NULLIF(v_customer_credit_history_daily.amount_paid_prior_month,0)
			,avg_fast_slow_days_last_completed_3months = v_customer_credit_history_daily.sum_days_x_payment_prior_3months/NULLIF(v_customer_credit_history_daily.amount_paid_prior_3months,0)
	FROM	@terms_eval_report crm
	LEFT JOIN( SELECT dbo.p21_view_customer_credit_history_daily.company_id    
	  ,dbo.p21_view_customer_credit_history_daily.customer_id    
	  ,SUM(CASE WHEN DATEDIFF(dd, dbo.p21_view_customer_credit_history_daily.date_invoiced, @CURRENT_TIMESTAMP) <= 30 THEN dbo.p21_view_customer_credit_history_daily.invoiced_sales ELSE 0.0 END) AS last_30_days_sales    
	  ,SUM(CASE WHEN DATEDIFF(dd, dbo.p21_view_customer_credit_history_daily.date_invoiced, @CURRENT_TIMESTAMP) BETWEEN 31 AND 60 THEN dbo.p21_view_customer_credit_history_daily.invoiced_sales ELSE 0.0 END) AS last_60_days_sales    
	  ,SUM(CASE WHEN DATEDIFF(dd, dbo.p21_view_customer_credit_history_daily.date_invoiced, @CURRENT_TIMESTAMP) BETWEEN 61 AND 90 THEN dbo.p21_view_customer_credit_history_daily.invoiced_sales ELSE 0.0 END) AS last_90_days_sales    
	  ,SUM(CASE WHEN DATEDIFF(dd, dbo.p21_view_customer_credit_history_daily.date_invoiced, @CURRENT_TIMESTAMP) <= 365 THEN dbo.p21_view_customer_credit_history_daily.invoiced_sales ELSE 0.0 END) AS last_365_days_sales    
	  ,SUM(CASE WHEN DATEDIFF(dd, dbo.p21_view_customer_credit_history_daily.date_invoiced, @CURRENT_TIMESTAMP) BETWEEN 366 AND 730 THEN dbo.p21_view_customer_credit_history_daily.invoiced_sales ELSE 0.0 END) AS previous_year_sales    
	  ,SUM(CASE WHEN DATEDIFF(dd, dbo.p21_view_customer_credit_history_daily.date_invoiced, @CURRENT_TIMESTAMP) <= 365 THEN dbo.p21_view_customer_credit_history_daily.invoiced_cost ELSE 0.0 END) AS last_365_days_cogs    
	  ,SUM(CASE WHEN DATEDIFF(dd, dbo.p21_view_customer_credit_history_daily.date_invoiced, @CURRENT_TIMESTAMP) <= 365 THEN dbo.p21_view_customer_credit_history_daily.invoiced_commission_cost ELSE 0 END) last_365_days_comm_cost    
	  ,SUM(CASE WHEN DATEDIFF(dd, dbo.p21_view_customer_credit_history_daily.date_invoiced, @CURRENT_TIMESTAMP) <= 365 THEN dbo.p21_view_customer_credit_history_daily.invoiced_other_cost ELSE 0 END) last_365_days_other_cost    
	  ,SUM(CASE WHEN DATEDIFF(dd, dbo.p21_view_customer_credit_history_daily.date_invoiced, @CURRENT_TIMESTAMP) <= 365 THEN dbo.p21_view_customer_credit_history_daily.freight_billed ELSE 0 END) last_365_days_freight_billed    
	  ,SUM(CASE WHEN DATEDIFF(dd, dbo.p21_view_customer_credit_history_daily.date_invoiced, @CURRENT_TIMESTAMP) <= 365 THEN dbo.p21_view_customer_credit_history_daily.freight_unbilled ELSE 0 END) last_365_days_freight_unbilled   
	  ,MAX(dbo.p21_view_customer_credit_history_daily.date_invoiced) date_last_invoiced
	  ,MAX(dbo.p21_view_customer_credit_history_daily.high_credit_used) high_credit_limit_used
	  ,MAX(CASE WHEN YEAR(@CURRENT_TIMESTAMP) = YEAR(dbo.p21_view_customer_credit_history_daily.date_invoiced) AND MONTH (@CURRENT_TIMESTAMP) = MONTH(dbo.p21_view_customer_credit_history_daily.date_invoiced) THEN dbo.p21_view_customer_credit_history_daily.high_credit_used ELSE 0 END) high_credit_limit_used_mtd
	  ,MAX(CASE WHEN YEAR(@CURRENT_TIMESTAMP) = YEAR(dbo.p21_view_customer_credit_history_daily.date_invoiced) THEN dbo.p21_view_customer_credit_history_daily.high_credit_used ELSE 0 END) high_credit_limit_used_ytd
	  ,SUM(CASE WHEN YEAR(@CURRENT_TIMESTAMP) = YEAR(dbo.p21_view_customer_credit_history_daily.date_invoiced) THEN dbo.p21_view_customer_credit_history_daily.invoiced_sales ELSE 0 END) invoiced_sales_ytd
	  ,SUM(CASE WHEN dbo.p21_view_customer_credit_history_daily.date_invoiced >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE())-1 ,0) AND dbo.p21_view_customer_credit_history_daily.date_invoiced <= DATEADD(yy, -1, GETDATE()) THEN dbo.p21_view_customer_credit_history_daily.invoiced_sales ELSE 0 END) invoiced_sales_prior_ytd
	  ,SUM(CASE WHEN YEAR(@CURRENT_TIMESTAMP) = YEAR(dbo.p21_view_customer_credit_history_daily.date_invoiced) AND MONTH (@CURRENT_TIMESTAMP) = MONTH(dbo.p21_view_customer_credit_history_daily.date_invoiced) THEN dbo.p21_view_customer_credit_history_daily.invoiced_sales ELSE 0 END) invoiced_sales_mtd
	  ,SUM(CASE WHEN @LAST_MONTH_YEAR = YEAR(dbo.p21_view_customer_credit_history_daily.date_invoiced) AND @LAST_MONTH = MONTH(dbo.p21_view_customer_credit_history_daily.date_invoiced) THEN dbo.p21_view_customer_credit_history_daily.invoiced_sales ELSE 0 END) invoiced_sales_prior_month
	  ,SUM(CASE WHEN @LAST_MONTH_YEAR = YEAR(dbo.p21_view_customer_credit_history_daily.date_invoiced) AND @LAST_MONTH = MONTH(dbo.p21_view_customer_credit_history_daily.date_invoiced) THEN dbo.p21_view_customer_credit_history_daily.amount_paid ELSE 0 END) amount_paid_prior_month
	  ,SUM(CASE WHEN @LAST_MONTH_YEAR = YEAR(dbo.p21_view_customer_credit_history_daily.date_invoiced) AND @LAST_MONTH = MONTH(dbo.p21_view_customer_credit_history_daily.date_invoiced) THEN dbo.p21_view_customer_credit_history_daily.sum_days_x_payment ELSE 0 END) sum_days_x_payment_prior_month
	  ,SUM(CASE WHEN dbo.p21_view_customer_credit_history_daily.date_invoiced >= @DATE_FIRSTDAY3MONTHS AND dbo.p21_view_customer_credit_history_daily.date_invoiced <= @DATE_LASTDAYLASTMONTH THEN dbo.p21_view_customer_credit_history_daily.amount_paid ELSE 0 END) amount_paid_prior_3months
	  ,SUM(CASE WHEN dbo.p21_view_customer_credit_history_daily.date_invoiced >= @DATE_FIRSTDAY3MONTHS AND dbo.p21_view_customer_credit_history_daily.date_invoiced <= @DATE_LASTDAYLASTMONTH THEN dbo.p21_view_customer_credit_history_daily.sum_days_x_payment ELSE 0 END) sum_days_x_payment_prior_3months
	FROM dbo.p21_view_customer_credit_history_daily    
	WHERE DATEDIFF(dd, dbo.p21_view_customer_credit_history_daily.date_invoiced, @CURRENT_TIMESTAMP) <= ( @yearsToReview * 365 )       
	  --AND customer_credit_history_daily.invoiced_sales <> 0  
	GROUP BY dbo.p21_view_customer_credit_history_daily.company_id    
	,dbo.p21_view_customer_credit_history_daily.customer_id    
	) AS v_customer_credit_history_daily ON (v_customer_credit_history_daily.company_id = crm.company_id)    
			 AND (v_customer_credit_history_daily.customer_id = crm.customer_id)      

	DELETE crm        
	FROM @terms_eval_report crm
	INNER JOIN dbo.p21_view_customer ON dbo.p21_view_customer.customer_id = crm.customer_id  
	 AND dbo.p21_view_customer.company_id = crm.company_id  
	WHERE COALESCE(DATEDIFF(dd, crm.date_last_invoiced, @CURRENT_TIMESTAMP),99999) > ( @yearsToReview * 365 )    
	AND dbo.p21_view_customer.customer_type_cd = 1203 --if prospects made it this far, don't delete them now  

	-- Delete customers who haven't bought anything from the marketing list.    
	IF (COALESCE(@list_id, 0) <> 0)    
	BEGIN
		IF EXISTS (SELECT 1 FROM marketing_campaign_detail WHERE bought_flag = 'Y' and list_id = @list_id)      
		BEGIN      
			DELETE crm      
			FROM @terms_eval_report crm      
			LEFT JOIN( SELECT dbo.p21_view_oe_hdr.customer_id      
				,dbo.p21_view_oe_hdr.company_id      
				,SUM(CASE      
				  WHEN bought_pg.list_id IS NOT NULL OR bought_item.list_id IS NOT NULL THEN 1      
				  ELSE 0      
				  END) AS number_of_buys      
			  FROM dbo.p21_view_oe_line      
			  INNER JOIN dbo.p21_view_oe_hdr ON (dbo.p21_view_oe_hdr.order_no = dbo.p21_view_oe_line.order_no)      
			  INNER JOIN dbo.p21_view_inv_mast ON (dbo.p21_view_inv_mast.inv_mast_uid = dbo.p21_view_oe_line.inv_mast_uid)      
			  INNER JOIN dbo.p21_view_product_group ON (dbo.p21_view_product_group.company_id = dbo.p21_view_oe_line.company_no)      
					AND (dbo.p21_view_product_group.product_group_id = dbo.p21_view_oe_line.product_group_id)      
			  LEFT JOIN dbo.p21_view_marketing_campaign_detail AS bought_pg ON (bought_pg.entity_uid = dbo.p21_view_product_group.product_group_uid)      
						  AND (bought_pg.bought_flag = 'Y')      
						  AND (bought_pg.entity_type = 218)      
						  AND (     
							COALESCE(bought_pg.days_from_purchase, 0) = 0      
							OR DATEDIFF(dd, dbo.p21_view_oe_line.date_created, @CURRENT_TIMESTAMP) <= bought_pg.days_from_purchase       
						   )      
						  AND (bought_pg.list_id = @list_id)      
			  LEFT JOIN dbo.p21_view_marketing_campaign_detail AS bought_item ON (bought_item.entity_uid = dbo.p21_view_inv_mast.inv_mast_uid)      
							AND (bought_item.bought_flag = 'Y')      
							AND (bought_item.entity_type = 212)      
							AND (      
							COALESCE(bought_item.days_from_purchase, 0) = 0      
							OR DATEDIFF(dd, dbo.p21_view_oe_line.date_created, @CURRENT_TIMESTAMP) <= bought_item.days_from_purchase       
							 )      
							AND (bought_item.list_id = @list_id)      
			  WHERE dbo.p21_view_oe_hdr.delete_flag = 'N'      
				AND dbo.p21_view_oe_hdr.cancel_flag = 'N'      
				AND dbo.p21_view_oe_hdr.rma_flag = 'N'      
				AND dbo.p21_view_oe_hdr.projected_order = 'N'      
				AND (dbo.p21_view_oe_line.qty_ordered - dbo.p21_view_oe_line.qty_canceled) > 0    
			  GROUP BY dbo.p21_view_oe_hdr.customer_id      
				,dbo.p21_view_oe_hdr.company_id      
			  ) AS v_bought ON (v_bought.company_id = crm.company_id)      
				  AND (v_bought.customer_id = crm.customer_id)      
			WHERE COALESCE(v_bought.number_of_buys, 0) <= 0      
		END    

		IF EXISTS (SELECT 1 FROM dbo.p21_view_marketing_campaign_detail WHERE bought_flag = 'N' and list_id = @list_id)      
		BEGIN      
			DELETE crm      
			FROM @terms_eval_report AS crm
			LEFT JOIN( SELECT dbo.p21_view_oe_hdr.customer_id      
				,dbo.p21_view_oe_hdr.company_id      
				,SUM(CASE      
				  WHEN not_bought_pg.list_id IS NOT NULL OR not_bought_item.list_id IS NOT NULL THEN 1      
				  ELSE 0      
				  END) AS number_of_buys      
			  FROM dbo.p21_view_oe_line      
			  INNER JOIN dbo.p21_view_oe_hdr ON (dbo.p21_view_oe_hdr.order_no = dbo.p21_view_oe_line.order_no)      
			  INNER JOIN dbo.p21_view_inv_mast ON (dbo.p21_view_inv_mast.inv_mast_uid = dbo.p21_view_oe_line.inv_mast_uid)      
			  INNER JOIN dbo.p21_view_product_group ON (dbo.p21_view_product_group.product_group_id = dbo.p21_view_oe_line.product_group_id)      
					AND (dbo.p21_view_product_group.company_id = dbo.p21_view_oe_line.company_no)      
			  LEFT JOIN dbo.p21_view_marketing_campaign_detail AS not_bought_pg ON (not_bought_pg.entity_uid = dbo.p21_view_product_group.product_group_uid)      
						   AND (not_bought_pg.bought_flag = 'N')      
						   AND (not_bought_pg.entity_type = 218)      
						   AND (      
							 COALESCE(not_bought_pg.days_from_purchase, 0) = 0      
							 OR DATEDIFF(dd, dbo.p21_view_oe_line.date_created, @CURRENT_TIMESTAMP) <= not_bought_pg.days_from_purchase       
					)      
						   AND (not_bought_pg.list_id = @list_id)      
			  LEFT JOIN dbo.p21_view_marketing_campaign_detail AS not_bought_item ON (not_bought_item.entity_uid = dbo.p21_view_inv_mast.inv_mast_uid)      
							 AND (not_bought_item.bought_flag = 'N')      
							 AND (not_bought_item.entity_type = 212)      
							 AND (      
							 COALESCE(not_bought_item.days_from_purchase, 0) = 0      
							 OR Datediff(dd, dbo.p21_view_oe_line.date_created, @CURRENT_TIMESTAMP) <= not_bought_item.days_from_purchase       
							  )      
							 AND (not_bought_item.list_id = @list_id)      
			  WHERE dbo.p21_view_oe_hdr.delete_flag = 'N'      
				AND dbo.p21_view_oe_hdr.cancel_flag = 'N'      
				AND dbo.p21_view_oe_hdr.rma_flag = 'N'      
				AND dbo.p21_view_oe_hdr.projected_order = 'N'      
				AND (dbo.p21_view_oe_line.qty_ordered - dbo.p21_view_oe_line.qty_canceled) > 0    
			  GROUP BY dbo.p21_view_oe_hdr.customer_id      
				,dbo.p21_view_oe_hdr.company_id      
			  ) AS v_not_bought ON (v_not_bought.company_id = crm.company_id)      
				   AND (v_not_bought.customer_id = crm.customer_id)      
			WHERE COALESCE(v_not_bought.number_of_buys, 0) <> 0      

		END    

		IF (@exclude_customers_in_campaign = 'Y')    
		BEGIN    
			DELETE crm    
			FROM @terms_eval_report AS crm
			INNER JOIN dbo.p21_view_oe_contacts_customer ON (dbo.p21_view_oe_contacts_customer.company_id = crm.company_id)    
					 AND (dbo.p21_view_oe_contacts_customer.customer_id = crm.customer_id)    
			INNER JOIN dbo.p21_view_contactlist ON (dbo.p21_view_contactlist.contact_id = dbo.p21_view_oe_contacts_customer.contact_id)    
			WHERE dbo.p21_view_oe_contacts_customer.delete_flag = 'N'    
			 AND dbo.p21_view_contactlist.list_flag = 'Y'    
			 AND dbo.p21_view_contactlist.list_id = @list_id    
		END   
	END  

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

	UPDATE	crm    
	SET		average_dso = dbo.p21_fn_days_sales_outstanding(crm.company_id, crm.customer_id, NULLIF(@days, 0), @CURRENT_TIMESTAMP)
	FROM	@terms_eval_report AS crm  

	UPDATE crm	
	SET crm.cost_to_carry_late_invoices = dbo.p21_fn_get_delinquent_ar_cost_to_carry(crm.company_id, crm.customer_id)
	FROM @terms_eval_report AS crm   

-- 1 minute 16 seconds
	UPDATE	crm
	SET		open_order_value = COALESCE(v_open_orders.open_order_value,0)
	FROM	@terms_eval_report crm     
	LEFT JOIN (SELECT	crm2.company_id      
						,crm2.customer_id      
						,SUM(CASE       
								WHEN dbo.p21_view_oe_line.lot_bill = 'Y' THEN dbo.p21_view_oe_line_lot_billing.open_price      
								ELSE (dbo.p21_view_oe_line.qty_ordered - dbo.p21_view_oe_line.qty_canceled - dbo.p21_view_oe_line.qty_invoiced) * (dbo.p21_view_oe_line.unit_price / NULLIF(dbo.p21_view_oe_line.pricing_unit_size,0))
							END) AS open_order_value      
			   FROM		@terms_eval_report crm2      
			   INNER JOIN dbo.p21_view_oe_hdr ON (dbo.p21_view_oe_hdr.company_id = crm2.company_id)      
								AND (dbo.p21_view_oe_hdr.customer_id = crm2.customer_id)      
			   INNER JOIN dbo.p21_view_oe_line ON (dbo.p21_view_oe_line.order_no = dbo.p21_view_oe_hdr.order_no)      
			   LEFT JOIN dbo.p21_view_oe_line_lot_billing ON (dbo.p21_view_oe_line_lot_billing.oe_line_uid = dbo.p21_view_oe_line.oe_line_uid)      
			   WHERE dbo.p21_view_oe_line.delete_flag = 'N'       
				 AND dbo.p21_view_oe_hdr.cancel_flag = 'N'      
				 AND dbo.p21_view_oe_hdr.delete_flag = 'N'      
				 AND dbo.p21_view_oe_hdr.completed <> 'T'     
				 AND dbo.p21_view_oe_line.complete = 'N' 
				 AND dbo.p21_view_oe_hdr.projected_order = 'N' 
				 AND dbo.p21_view_oe_hdr.rma_flag = 'N' 	 
				 AND dbo.p21_view_oe_line.parent_oe_line_uid = 0
				 AND COALESCE(dbo.p21_view_oe_hdr.order_type, 706) NOT IN (1877, 1920)    
			   GROUP BY crm2.company_id      
						,crm2.customer_id      
			) AS v_open_orders ON (v_open_orders.company_id = crm.company_id)      
							  AND (v_open_orders.customer_id = crm.customer_id)    


-- 1 minute
	UPDATE	crm      
	SET		open_quote_value = coalesce(v_open_orders.open_quote_value,0)
	FROM	@terms_eval_report crm     
	LEFT JOIN (SELECT	crm2.company_id      
						,crm2.customer_id      
						,SUM(CASE      
								WHEN dbo.p21_view_oe_line.lot_bill = 'Y' THEN dbo.p21_view_oe_line_lot_billing.open_price      
								ELSE(dbo.p21_view_oe_line.qty_ordered - dbo.p21_view_oe_line.qty_canceled - dbo.p21_view_oe_line.qty_invoiced) * (dbo.p21_view_oe_line.unit_price / NULLIF(dbo.p21_view_oe_line.pricing_unit_size,0)) 
							 END) AS open_quote_value      
			   FROM		@terms_eval_report crm2      
			   INNER JOIN dbo.p21_view_oe_hdr ON (dbo.p21_view_oe_hdr.company_id = crm2.company_id)      
								AND (dbo.p21_view_oe_hdr.customer_id = crm2.customer_id)      
			   INNER JOIN dbo.p21_view_oe_line ON (dbo.p21_view_oe_line.order_no = dbo.p21_view_oe_hdr.order_no)      
			   LEFT JOIN dbo.p21_view_oe_line_lot_billing ON (dbo.p21_view_oe_line_lot_billing.oe_line_uid = dbo.p21_view_oe_line.oe_line_uid)      
			   WHERE dbo.p21_view_oe_line.delete_flag = 'N'       
				 AND dbo.p21_view_oe_hdr.cancel_flag = 'N'      
				 AND dbo.p21_view_oe_hdr.delete_flag = 'N'      
				 AND dbo.p21_view_oe_hdr.completed <> 'T'   
				 AND dbo.p21_view_oe_line.complete = 'N' 
				 AND dbo.p21_view_oe_hdr.projected_order = 'Y'   
				 AND dbo.p21_view_oe_line.parent_oe_line_uid = 0 
				 AND COALESCE(dbo.p21_view_oe_hdr.order_type, 706) NOT IN (1877, 1920)       
			   GROUP BY crm2.company_id      
						,crm2.customer_id      
			) AS v_open_orders ON (v_open_orders.company_id = crm.company_id)      
							  AND (v_open_orders.customer_id = crm.customer_id)      

	UPDATE	crm      
	SET		rma_value_last_365 = coalesce(v_open_orders.rma_value_last_365,0)
	FROM	@terms_eval_report crm     
	LEFT JOIN (SELECT	crm2.company_id      
						,crm2.customer_id      
						,SUM(CASE         
								WHEN dbo.p21_view_oe_line.lot_bill = 'Y' THEN dbo.p21_view_oe_line_lot_billing.open_price      
								ELSE (dbo.p21_view_oe_line.qty_ordered - dbo.p21_view_oe_line.qty_canceled) * (dbo.p21_view_oe_line.unit_price / NULLIF(dbo.p21_view_oe_line.pricing_unit_size,0))
							 END) AS rma_value_last_365      
			   FROM		@terms_eval_report crm2      
			   INNER JOIN dbo.p21_view_oe_hdr ON (dbo.p21_view_oe_hdr.company_id = crm2.company_id)      
								AND (dbo.p21_view_oe_hdr.customer_id = crm2.customer_id)      
			   INNER JOIN dbo.p21_view_oe_line ON (dbo.p21_view_oe_line.order_no = dbo.p21_view_oe_hdr.order_no)      
			   LEFT JOIN dbo.p21_view_oe_line_lot_billing ON (dbo.p21_view_oe_line_lot_billing.oe_line_uid = dbo.p21_view_oe_line.oe_line_uid)      
			   WHERE dbo.p21_view_oe_line.delete_flag = 'N'       
				 AND dbo.p21_view_oe_hdr.cancel_flag = 'N'      
				 AND dbo.p21_view_oe_hdr.delete_flag = 'N'      
				 AND dbo.p21_view_oe_hdr.completed <> 'T'      
				 AND dbo.p21_view_oe_hdr.rma_flag = 'Y'
				 AND DATEDIFF(dd, dbo.p21_view_oe_line.date_created, @CURRENT_TIMESTAMP) <= 365
				 AND dbo.p21_view_oe_line.parent_oe_line_uid = 0
				 AND COALESCE(dbo.p21_view_oe_hdr.order_type, 706) NOT IN (1877, 1920)    
			   GROUP BY crm2.company_id      
						,crm2.customer_id      
			) AS v_open_orders ON (v_open_orders.company_id = crm.company_id)      
							  AND (v_open_orders.customer_id = crm.customer_id)      


-- > 5 minutes
/*
	UPDATE	crm      
	SET		order_value_last_365 = coalesce(v_open_orders.order_value_last_365,0)
	FROM	@terms_eval_report crm     
	LEFT JOIN (SELECT	crm2.company_id      
						,crm2.customer_id      
						,SUM(CASE         
								WHEN dbo.p21_view_oe_line.lot_bill = 'Y' THEN dbo.p21_view_oe_line_lot_billing.open_price      
								ELSE (dbo.p21_view_oe_line.qty_ordered - dbo.p21_view_oe_line.qty_canceled) * (dbo.p21_view_oe_line.unit_price / NULLIF(dbo.p21_view_oe_line.pricing_unit_size,0))
							 END) AS order_value_last_365      
			   FROM		@terms_eval_report crm2      
			   INNER JOIN dbo.p21_view_oe_hdr ON (dbo.p21_view_oe_hdr.company_id = crm2.company_id)      
								AND (dbo.p21_view_oe_hdr.customer_id = crm2.customer_id)      
			   INNER JOIN dbo.p21_view_oe_line ON (dbo.p21_view_oe_line.order_no = dbo.p21_view_oe_hdr.order_no)      
			   LEFT JOIN dbo.p21_view_oe_line_lot_billing ON (dbo.p21_view_oe_line_lot_billing.oe_line_uid = dbo.p21_view_oe_line.oe_line_uid)      
			   WHERE dbo.p21_view_oe_line.delete_flag = 'N'       
				 AND dbo.p21_view_oe_hdr.cancel_flag = 'N'      
				 AND dbo.p21_view_oe_hdr.delete_flag = 'N'      
				 AND dbo.p21_view_oe_hdr.completed <> 'T'      
				 AND dbo.p21_view_oe_hdr.rma_flag = 'N' 
				 AND dbo.p21_view_oe_hdr.projected_order = 'N' 
				 AND DATEDIFF(dd, dbo.p21_view_oe_line.date_created, @CURRENT_TIMESTAMP) <= 365 
				 AND dbo.p21_view_oe_line.parent_oe_line_uid = 0
				 AND COALESCE(dbo.p21_view_oe_hdr.order_type, 706) NOT IN (1877, 1920)      
			   GROUP BY crm2.company_id      
						,crm2.customer_id      
			) AS v_open_orders ON (v_open_orders.company_id = crm.company_id)      
							  AND (v_open_orders.customer_id = crm.customer_id)      
*/

-- total time about 2 minutes for 3672 records
-- Order and Quote data.    
-- 2 seconds
	UPDATE crm    
	SET  days_since_last_order = DATEDIFF(dd, v_oe_hdr.last_order_date, @CURRENT_TIMESTAMP) -- days_since_last_order    
	,days_since_first_order = DATEDIFF(dd, v_oe_hdr.first_order_date, @CURRENT_TIMESTAMP) -- days_since_first_order    
	,number_of_orders = v_oe_hdr.number_of_orders    
	,days_since_last_quote = DATEDIFF(dd, v_oe_hdr.last_quote_date, @CURRENT_TIMESTAMP) -- days_since_last_quote    
	,days_since_first_quote = DATEDIFF(dd, v_oe_hdr.first_quote_date, @CURRENT_TIMESTAMP) -- days_since_first_quote    
	,number_of_quotes = v_oe_hdr.number_of_quotes

	,total_orders = v_oe_hdr.total_orders  --DBG: 5/7/13
	,total_quotes = v_oe_hdr.total_quotes  --DBG: 5/7/13  
   
	FROM @terms_eval_report crm   
	LEFT JOIN( SELECT dbo.p21_view_oe_hdr.company_id    
	  ,dbo.p21_view_oe_hdr.customer_id    
	  ,MAX(CASE WHEN dbo.p21_view_oe_hdr.projected_order = 'N' THEN dbo.p21_view_oe_hdr.order_date ELSE CAST('01/01/90' AS datetime) END) last_order_date    
	  ,MIN(CASE WHEN dbo.p21_view_oe_hdr.projected_order = 'N' THEN dbo.p21_view_oe_hdr.order_date ELSE CAST('12/31/49' AS datetime) END) first_order_date    
	  ,SUM(CASE WHEN dbo.p21_view_oe_hdr.projected_order = 'N' AND DATEDIFF(dd, dbo.p21_view_oe_hdr.order_date, @CURRENT_TIMESTAMP) <= 365 THEN 1 ELSE 0 END) number_of_orders    
	  ,MAX(CASE WHEN dbo.p21_view_oe_hdr.projected_order = 'Y' THEN dbo.p21_view_oe_hdr.order_date ELSE CAST('01/01/90' AS datetime) END) last_quote_date    
	  ,MIN(CASE WHEN dbo.p21_view_oe_hdr.projected_order = 'Y' THEN dbo.p21_view_oe_hdr.order_date ELSE CAST('12/31/49' AS datetime) END) first_quote_date    
	  ,SUM(CASE WHEN dbo.p21_view_oe_hdr.projected_order = 'Y' AND DATEDIFF(dd, dbo.p21_view_oe_hdr.order_date, @CURRENT_TIMESTAMP) <= 365 THEN 1 ELSE 0 END) number_of_quotes 
      ,SUM(CASE WHEN dbo.p21_view_oe_hdr.projected_order = 'N' THEN 1 ELSE 0 END) total_orders     --DBG: 5/7/13 
      ,SUM(CASE WHEN dbo.p21_view_oe_hdr.projected_order = 'Y' THEN 1 ELSE 0 END) total_quotes      --DBG: 5/7/13 
	FROM dbo.p21_view_oe_hdr    
	WHERE dbo.p21_view_oe_hdr.delete_flag = 'N'    
	GROUP BY dbo.p21_view_oe_hdr.company_id    
	  ,dbo.p21_view_oe_hdr.customer_id    
	) AS v_oe_hdr ON (v_oe_hdr.company_id = crm.company_id)    
	   AND (v_oe_hdr.customer_id = crm.customer_id)  

-- Calculate the average days between orders in the last 365 days     
-- 1 second
	UPDATE crm    
	SET  avg_days_between_orders = ROUND(DATEDIFF(dd, v_order_span.first_order_date, v_order_span.last_order_date) * 1.00 / NULLIF(v_order_span.number_of_orders - 1, 0), 0)     
	FROM @terms_eval_report AS crm   
	INNER JOIN( SELECT dbo.p21_view_oe_hdr.company_id    
						,dbo.p21_view_oe_hdr.customer_id     
						,MIN(dbo.p21_view_oe_hdr.order_date) first_order_date    
						,MAX(dbo.p21_view_oe_hdr.order_date) last_order_date    
						,COUNT(DISTINCT DATEPART(DY, dbo.p21_view_oe_hdr.order_date)) AS number_of_orders    
	FROM dbo.p21_view_oe_hdr
	-- P21CD-13210 - modify to use @current_timestamp   
	WHERE DATEDIFF(dd, dbo.p21_view_oe_hdr.order_date, @CURRENT_TIMESTAMP) <= 365    
	  AND dbo.p21_view_oe_hdr.rma_flag = 'N' -- Exclude RMAs    
	  AND dbo.p21_view_oe_hdr.projected_order = 'N' -- Exclude quotes    
	  AND dbo.p21_view_oe_hdr.order_type NOT IN (1706, 1920) -- Exclude service orders and service quotes    
	  AND dbo.p21_view_oe_hdr.delete_flag = 'N'    
	GROUP BY dbo.p21_view_oe_hdr.company_id    
	  ,dbo.p21_view_oe_hdr.customer_id     
	 ) AS v_order_span ON (v_order_span.customer_id = crm.customer_id)    
		  AND (v_order_span.company_id = crm.company_id)

-- total time 2 minute 5 seconds
-- > 5 minutes
/*
	UPDATE crm    
	SET  ytd_prct_amt_won = COALESCE(v_quote.ytd_converted / NULLIF(v_quote.ytd_quoted, 0), 0) -- ytd_prct_amt_won    
	,ytd_prct_lines_won = COALESCE(v_quote.ytd_converted_lines / NULLIF(v_quote.ytd_quoted_lines, 0), 0) -- ytd_prct_lines_won    
	FROM @terms_eval_report AS crm    
	LEFT JOIN( SELECT dbo.p21_view_oe_hdr.customer_id    
	  ,dbo.p21_view_oe_hdr.company_id    
	  ,SUM(dbo.p21_view_oe_line.unit_price / NULLIF(dbo.p21_view_oe_line.pricing_unit_size,0)
	   * CASE     
		WHEN DATEDIFF(dd, dbo.p21_view_quote_line.date_created, @CURRENT_TIMESTAMP) <= 365 AND dbo.p21_view_quote_line.qty_converted > dbo.p21_view_oe_line.qty_ordered THEN dbo.p21_view_oe_line.qty_ordered    
		WHEN DATEDIFF(dd, dbo.p21_view_quote_line.date_created, @CURRENT_TIMESTAMP) <= 365 THEN dbo.p21_view_quote_line.qty_converted    
		ELSE 0    
		 END    
		 ) AS ytd_converted    
	  ,SUM(CASE    
		WHEN DATEDIFF(dd, dbo.p21_view_quote_line.date_created, @CURRENT_TIMESTAMP) <= 365 THEN dbo.p21_view_oe_line.extended_price    
		ELSE 0    
		END    
	   ) AS ytd_quoted    
	  ,CAST(SUM(CASE     
		 WHEN DATEDIFF(dd, dbo.p21_view_quote_line.date_created, @CURRENT_TIMESTAMP) <= 365 THEN 1     
		 ELSE 0     
		  END) AS DECIMAL(10,2)) AS ytd_quoted_lines    
	  ,CAST(SUM(CASE     
		 WHEN DATEDIFF(dd, dbo.p21_view_quote_line.date_created, @CURRENT_TIMESTAMP) <= 365 AND dbo.p21_view_quote_line.qty_converted > 0 THEN 1     
		 ELSE 0     
		  END) AS DECIMAL(10,2)) AS ytd_converted_lines    
	FROM dbo.p21_view_quote_line    
	INNER JOIN dbo.p21_view_oe_line ON (dbo.p21_view_oe_line.oe_line_uid = dbo.p21_view_quote_line.oe_line_uid)    
	INNER JOIN dbo.p21_view_oe_hdr ON (dbo.p21_view_oe_hdr.order_no = dbo.p21_view_oe_line.order_no)    
	WHERE dbo.p21_view_quote_line.date_created >= DATEADD(DAY, -365, CAST(@CURRENT_TIMESTAMP AS VARCHAR(11)))    
	  AND dbo.p21_view_oe_hdr.delete_flag = 'N'    
	GROUP BY dbo.p21_view_oe_hdr.customer_id    
	  ,dbo.p21_view_oe_hdr.company_id    
	) AS v_quote ON (v_quote.customer_id = crm.customer_id)    
	   AND (v_quote.company_id = crm.company_id)    
*/


	IF @salesrep_id IS NOT NULL
	DELETE crm      
	FROM @terms_eval_report crm      
	LEFT JOIN (
		SELECT dbo.p21_view_customer.company_id
			,dbo.p21_view_customer.customer_id
		FROM dbo.p21_view_customer
		LEFT JOIN dbo.p21_view_customer_salesrep ON dbo.p21_view_customer_salesrep.customer_id = dbo.p21_view_customer.customer_id
								AND dbo.p21_view_customer_salesrep.company_id = dbo.p21_view_customer.company_id
								AND dbo.p21_view_customer_salesrep.row_status_flag = 704
		LEFT JOIN (
			SELECT dbo.p21_view_ship_to_salesrep.company_id
				,dbo.p21_view_ship_to.customer_id
				,dbo.p21_view_ship_to_salesrep.salesrep_id
			FROM dbo.p21_view_ship_to_salesrep
			INNER JOIN dbo.p21_view_ship_to ON dbo.p21_view_ship_to.company_id = dbo.p21_view_ship_to_salesrep.company_id
							AND dbo.p21_view_ship_to.ship_to_id = dbo.p21_view_ship_to_salesrep.ship_to_id
							AND dbo.p21_view_ship_to_salesrep.delete_flag = 'N'
			WHERE @include_ship2_salesreps = 'Y'
				AND dbo.p21_view_ship_to_salesrep.salesrep_id = @salesrep_id
		) v_ship2_salesrep ON v_ship2_salesrep.company_id = dbo.p21_view_customer.company_id
					AND v_ship2_salesrep.customer_id = dbo.p21_view_customer.customer_id
		WHERE dbo.p21_view_customer.delete_flag = 'N'
			AND (	dbo.p21_view_customer.salesrep_id = @salesrep_id		
					OR COALESCE(dbo.p21_view_customer_salesrep.salesrep_id, '') = @salesrep_id
					OR COALESCE(v_ship2_salesrep.salesrep_id, '') = @salesrep_id
				)
	) v_salesrep ON v_salesrep.customer_id = crm.customer_id
				AND v_salesrep.company_id = crm.company_id
	WHERE v_salesrep.customer_id IS NULL


-- 36 seconds
	UPDATE crm    
	SET  next_task_to_complete = drv_next_task_to_completed.activity_trans_no    
	,last_completed_task = drv_previous_completed_task.activity_trans_no    
	FROM @terms_eval_report AS crm   
	LEFT JOIN( SELECT drv_max_completed_date.company_id    
	  ,drv_max_completed_date.customer_id    
	  ,MAX(activity_trans.activity_trans_no) AS activity_trans_no    
	FROM( SELECT dbo.p21_view_activity_trans.company_id    
		,dbo.p21_view_activity_trans.link_id AS customer_id    
		,MAX(completed_date) AS completed_date    
	  FROM dbo.p21_view_activity_trans    
	  WHERE dbo.p21_view_activity_trans.link_type_cd = 1203    
		AND dbo.p21_view_activity_trans.completed_flag = 'Y'    
	  GROUP BY dbo.p21_view_activity_trans.company_id    
		,dbo.p21_view_activity_trans.link_id    
		) AS drv_max_completed_date     
	INNER JOIN activity_trans ON (activity_trans.link_type_cd = 1203)    
		   AND (activity_trans.completed_flag = 'Y')    
		   AND (activity_trans.company_id = drv_max_completed_date.company_id)    
		   AND (activity_trans.link_id = drv_max_completed_date.customer_id)    
		   AND (activity_trans.completed_date = drv_max_completed_date.completed_date)    
	GROUP BY drv_max_completed_date.company_id    
	  ,drv_max_completed_date.customer_id    
	 ) AS drv_previous_completed_task ON (drv_previous_completed_task.company_id = crm.company_id)    
			 AND (drv_previous_completed_task.customer_id = crm.customer_id)    
	LEFT JOIN( SELECT dbo.p21_view_activity_trans.company_id    
	  ,dbo.p21_view_activity_trans.link_id AS customer_id    
	  ,MIN(dbo.p21_view_activity_trans.activity_trans_no) AS activity_trans_no    
	FROM dbo.p21_view_activity_trans    
	WHERE dbo.p21_view_activity_trans.link_type_cd = 1203    
	  AND dbo.p21_view_activity_trans.completed_flag = 'N'    
	GROUP BY dbo.p21_view_activity_trans.company_id    
	  ,dbo.p21_view_activity_trans.link_id    
	 ) AS drv_next_task_to_completed ON (drv_next_task_to_completed.company_id = crm.company_id)    
			AND (drv_next_task_to_completed.customer_id = crm.customer_id)    


	-- Lost Sales    
	IF (COALESCE(@lost_sales_uid, 0) <> 0)    
	BEGIN    
		DELETE crm
		FROM @terms_eval_report AS crm  
		WHERE NOT EXISTS( SELECT 1    
		   FROM dbo.p21_view_lost_sales_transaction    
		   INNER JOIN lost_sales ON (lost_sales.lost_sales_uid = dbo.p21_view_lost_sales_transaction.lost_sales_uid)    
		   -- Orders, Quotes, RMAs    
		   LEFT JOIN dbo.p21_view_oe_hdr ON (dbo.p21_view_oe_hdr.order_no = CAST(dbo.p21_view_lost_sales_transaction.transaction_no AS VARCHAR(8)))    
			   AND (dbo.p21_view_lost_sales_transaction.transaction_code_no IN (2143, 2148, 2145, 2707, 2846, 2847, 2144, 2142))    
		   -- Shipments    
		   LEFT JOIN dbo.p21_view_oe_pick_ticket ON (dbo.p21_view_oe_pick_ticket.pick_ticket_no = dbo.p21_view_lost_sales_transaction.transaction_no)    
				 AND (dbo.p21_view_lost_sales_transaction.transaction_code_no IN (2146, 2147, 2149))    
		   LEFT JOIN dbo.p21_view_oe_hdr AS pick_ticket_oe_hdr ON (pick_ticket_oe_hdr.order_no = dbo.p21_view_oe_pick_ticket.order_no)    
		   -- Opportunity    
		   LEFT JOIN dbo.p21_view_opportunity ON (dbo.p21_view_opportunity.opportunity_id = dbo.p21_view_lost_sales_transaction.transaction_no)    
				 AND (dbo.p21_view_lost_sales_transaction.transaction_code_no = 2714)    
		   WHERE dbo.p21_view_lost_sales_transaction.lost_sales_uid = @lost_sales_uid    
			 AND lost_sales.company_id = crm.company_id    
			 AND COALESCE(dbo.p21_view_oe_hdr.customer_id, pick_ticket_oe_hdr.customer_id, dbo.p21_view_opportunity.customer_id) = crm.customer_id     
			 )    
	END    

	IF (@strategic_pricing_enabled = 'Y')    
	BEGIN    
		UPDATE crm    
		SET  roa_0_to_30 = COALESCE(drv_spa_invoice.sum_extended_price_0_to_30 / NULLIF(drv_spa_invoice.sum_extended_price_strategic_0_to_30, 0), 0)    
		,roa_31_to_60 = COALESCE(drv_spa_invoice.sum_extended_price_31_to_60 / NULLIF(drv_spa_invoice.sum_extended_price_strategic_31_to_60, 0), 0)    
		,roa_61_to_90 = COALESCE(drv_spa_invoice.sum_extended_price_61_to_90 / NULLIF(drv_spa_invoice.sum_extended_price_strategic_61_to_90, 0), 0)    
		,gap_0_to_30 = COALESCE(drv_spa_invoice.sum_extended_price_strategic_0_to_30 - drv_spa_invoice.sum_extended_price_0_to_30, 0)    
		,gap_31_to_60 = COALESCE(drv_spa_invoice.sum_extended_price_strategic_31_to_60 - drv_spa_invoice.sum_extended_price_31_to_60 , 0)    
		,gap_61_to_90 = COALESCE(drv_spa_invoice.sum_extended_price_strategic_61_to_90 - drv_spa_invoice.sum_extended_price_61_to_90, 0)    
		FROM @terms_eval_report  AS crm  
		LEFT JOIN( SELECT dbo.p21_view_invoice_hdr.customer_id    
		   ,dbo.p21_view_invoice_hdr.company_no    
		   ,SUM( CASE    
			  WHEN DATEDIFF(DD, dbo.p21_view_invoice_hdr.invoice_date, @CURRENT_TIMESTAMP) <= 30 THEN dbo.p21_view_invoice_line.extended_price    
			  ELSE 0    
			 END    
			) AS sum_extended_price_0_to_30    
		   ,SUM( CASE    
			  WHEN DATEDIFF(DD, dbo.p21_view_invoice_hdr.invoice_date, @CURRENT_TIMESTAMP) BETWEEN 31 AND 60 THEN dbo.p21_view_invoice_line.extended_price    
			  ELSE 0    
			 END    
			) AS sum_extended_price_31_to_60    
		   ,SUM( CASE    
			  WHEN DATEDIFF(DD, dbo.p21_view_invoice_hdr.invoice_date, @CURRENT_TIMESTAMP) BETWEEN 61 AND 90 THEN dbo.p21_view_invoice_line.extended_price    
			  ELSE 0    
			  END    
			) AS sum_extended_price_61_to_90    
		   ,SUM( CASE    
			  WHEN DATEDIFF(DD, dbo.p21_view_invoice_hdr.invoice_date, @CURRENT_TIMESTAMP) <= 30 THEN COALESCE(dbo.p21_view_invoice_line.qty_shipped * (dbo.p21_view_oe_line.strategic_unit_price / NULLIF(dbo.p21_view_invoice_line.pricing_unit_size, 0)), 0)    
			  ELSE 0    
			  END    
			) AS sum_extended_price_strategic_0_to_30    
		   ,SUM( CASE    
			  WHEN DATEDIFF(DD, dbo.p21_view_invoice_hdr.invoice_date, @CURRENT_TIMESTAMP) BETWEEN 31 AND 60 THEN COALESCE(dbo.p21_view_invoice_line.qty_shipped * (dbo.p21_view_oe_line.strategic_unit_price / NULLIF(dbo.p21_view_invoice_line.pricing_unit_size, 0)), 0)    
			  ELSE 0    
			  END    
			) AS sum_extended_price_strategic_31_to_60    
		   ,SUM( CASE    
			  WHEN DATEDIFF(DD, dbo.p21_view_invoice_hdr.invoice_date, @CURRENT_TIMESTAMP) BETWEEN 61 AND 90 THEN COALESCE(dbo.p21_view_invoice_line.qty_shipped * (dbo.p21_view_oe_line.strategic_unit_price / NULLIF(dbo.p21_view_invoice_line.pricing_unit_size, 0)), 0)    
			  ELSE 0    
			  END    
			) AS sum_extended_price_strategic_61_to_90    
		 FROM dbo.p21_view_strategic_pricing_invoice    
		 INNER JOIN dbo.p21_view_invoice_line ON (dbo.p21_view_invoice_line.invoice_no = dbo.p21_view_strategic_pricing_invoice.invoice_no)    
				 AND (dbo.p21_view_invoice_line.line_no = dbo.p21_view_strategic_pricing_invoice.line_no)    
		 INNER JOIN dbo.p21_view_oe_line ON (dbo.p21_view_oe_line.order_no = dbo.p21_view_invoice_line.order_no)    
			   AND (dbo.p21_view_oe_line.line_no = dbo.p21_view_invoice_line.oe_line_number)    
		 INNER JOIN dbo.p21_view_invoice_hdr ON dbo.p21_view_invoice_hdr.invoice_no = dbo.p21_view_invoice_line.invoice_no    
		 WHERE dbo.p21_view_oe_line.used_strategic_pricing_flag IS NOT NULL    
		   AND dbo.p21_view_oe_line.strategic_unit_price IS NOT NULL    
		   AND dbo.p21_view_invoice_hdr.invoice_date >= DATEADD(DAY, -90, @CURRENT_TIMESTAMP)    
		 GROUP BY dbo.p21_view_invoice_hdr.customer_id    
		   ,dbo.p21_view_invoice_hdr.company_no    
		 ) AS drv_spa_invoice ON (drv_spa_invoice.company_no = crm.company_id)    
			  AND (drv_spa_invoice.customer_id = crm.customer_id) 

	END   


	UPDATE crm
	SET  open_opportunity_value = drv_opportunity.open_opportunity_value    
	,number_of_opportunities = drv_opportunity.number_of_opportunities    
	FROM @terms_eval_report AS crm   
	LEFT JOIN( SELECT dbo.p21_view_opportunity.company_id    
	  ,dbo.p21_view_opportunity.customer_id    
	  ,SUM( CASE    
		 WHEN dbo.p21_view_opportunity.complete_flag = 'N' THEN dbo.p21_view_opportunity.opportunity_size    
		 ELSE 0    
		END    
	   ) AS open_opportunity_value    
	  ,SUM( CASE    
		 WHEN DATEDIFF(dd, dbo.p21_view_opportunity.date_created, @CURRENT_TIMESTAMP) <= 365 THEN 1    
		 ELSE 0    
		END         ) AS number_of_opportunities    
	FROM dbo.p21_view_opportunity    
	GROUP BY dbo.p21_view_opportunity.company_id    
	  ,dbo.p21_view_opportunity.customer_id    
	) AS drv_opportunity ON (drv_opportunity.company_id = crm.company_id)    
		 AND (drv_opportunity.customer_id = crm.customer_id)    


	UPDATE crm    
	SET  missed_buy_flag = CASE     
		WHEN drv_buying_trend_final_form.number_of_missed_buys > 0 THEN 'Y'    
		ELSE 'N'    
		 END    
	FROM @terms_eval_report  crm  
	INNER JOIN( SELECT dbo.p21_view_buying_trend_final_form.customer_id    
	  ,dbo.p21_view_buying_trend_final_form.company_id    
	  ,COUNT(*) AS number_of_missed_buys     
	FROM dbo.p21_view_buying_trend_final_form    
	WHERE dbo.p21_view_buying_trend_final_form.normal = 'Y'    
	  AND COALESCE(dbo.p21_view_buying_trend_final_form.last_bucket_qty, 0) < dbo.p21_view_buying_trend_final_form.lower_spec_limit    
	GROUP BY dbo.p21_view_buying_trend_final_form.customer_id    
	  ,dbo.p21_view_buying_trend_final_form.company_id    
	 ) AS drv_buying_trend_final_form ON (drv_buying_trend_final_form.company_id = crm.company_id)    
			 AND (drv_buying_trend_final_form.customer_id = crm.customer_id)    

-- total time 2 minutes 5 seconds 3672 records

	UPDATE crm
	SET  last_payment_date = ar_last_payment.last_payment_date,  
	     last_payment_amount = ar_last_payment.last_payment_amount 
	FROM @terms_eval_report crm   
	LEFT JOIN( SELECT dbo.p21_view_ar_receipts.company_id, dbo.p21_view_ar_receipts_detail.customer_id, dbo.p21_view_ar_receipts.receipt_number,
	                  dbo.p21_view_ar_receipts.date_created last_payment_date, sum(dbo.p21_view_ar_receipts_detail.payment_amount) last_payment_amount
               FROM dbo.p21_view_ar_receipts 
                 INNER JOIN dbo.p21_view_ar_receipts_detail on dbo.p21_view_ar_receipts_detail.receipt_number = dbo.p21_view_ar_receipts.receipt_number
                        and dbo.p21_view_ar_receipts_detail.company_id = dbo.p21_view_ar_receipts.company_id
               WHERE (dbo.p21_view_ar_receipts.company_id = '10') AND
                     coalesce(dbo.p21_view_ar_receipts.reverse_payment_flag, 'N') = 'N' AND
		             dbo.p21_view_ar_receipts.receipt_number = (select max(ar.receipt_number) FROM ar_receipts_detail ar where ar.customer_id = dbo.p21_view_ar_receipts_detail.customer_id)
               GROUP BY dbo.p21_view_ar_receipts.company_id, dbo.p21_view_ar_receipts_detail.customer_id, dbo.p21_view_ar_receipts.receipt_number, dbo.p21_view_ar_receipts.date_created    
	          ) AS ar_last_payment ON (ar_last_payment.company_id = crm.company_id)
                                  AND (ar_last_payment.customer_id = crm.customer_id)

-- total time 2 minutes 7 seconds
    UPDATE crm
	SET  last_order_date = order_info.last_order_date,
	     last_order_amount = order_info.last_order_amount
	FROM @terms_eval_report crm   
	LEFT JOIN (  SELECT  dbo.p21_view_oe_hdr.company_id, dbo.p21_view_oe_hdr.customer_id,
	                     dbo.p21_view_oe_hdr.order_date last_order_date,
                     sum(dbo.p21_view_oe_line.extended_price) last_order_amount, avg(v_open_order.total_order_amt) total_open_orders_amount
                 FROM dbo.p21_view_oe_hdr 
       INNER JOIN dbo.p21_view_customer 
         ON dbo.p21_view_customer.customer_id = dbo.p21_view_oe_hdr.customer_id AND
            dbo.p21_view_customer.company_id = dbo.p21_view_oe_hdr.company_id
       INNER JOIN dbo.p21_view_oe_line 
          ON (dbo.p21_view_oe_line.order_no = dbo.p21_view_oe_hdr.order_no AND
              dbo.p21_view_oe_line.parent_oe_line_uid = 0)
       CROSS JOIN
          (SELECT SUM( CASE WHEN dbo.p21_view_oe_line.lot_bill = 'Y' and dbo.p21_view_oe_line.parent_oe_line_uid = 0
               THEN dbo.p21_view_oe_line_lot_billing.open_price
               ELSE CASE
               WHEN dbo.p21_view_oe_line.parent_oe_line_uid = 0
               THEN ( dbo.p21_view_oe_line.qty_ordered - dbo.p21_view_oe_line.qty_invoiced - dbo.p21_view_oe_line.qty_canceled ) * ( dbo.p21_view_oe_line.unit_price / dbo.p21_view_oe_line.pricing_unit_size )
               ELSE 0 END
               END
          ) total_order_amt
          FROM dbo.p21_view_oe_hdr 
               INNER JOIN dbo.p21_view_oe_line 
                 ON (dbo.p21_view_oe_line.order_no = dbo.p21_view_oe_hdr.order_no)
               INNER JOIN dbo.p21_view_customer
                 ON (dbo.p21_view_customer.customer_id = dbo.p21_view_oe_hdr.customer_id AND
                     dbo.p21_view_customer.company_id = dbo.p21_view_oe_hdr.company_id)
               LEFT JOIN dbo.p21_view_oe_line_lot_billing ON dbo.p21_view_oe_line_lot_billing.oe_line_uid = dbo.p21_view_oe_line.oe_line_uid
          WHERE dbo.p21_view_oe_hdr.projected_order = 'N' 
		    AND dbo.p21_view_oe_hdr.rma_flag = 'N'
            AND dbo.p21_view_oe_hdr.delete_flag = 'N'
            AND dbo.p21_view_oe_hdr.cancel_flag = 'N'
            AND dbo.p21_view_oe_hdr.completed = 'N'
            AND dbo.p21_view_oe_line.complete = 'N'
            AND Coalesce(dbo.p21_view_oe_hdr.order_type,0) NOT IN (1877, 1920)) v_open_order
   WHERE dbo.p21_view_oe_hdr.projected_order = 'N' 
     AND dbo.p21_view_oe_hdr.rma_flag = 'N'
     AND dbo.p21_view_oe_hdr.delete_flag = 'N'
     AND dbo.p21_view_oe_hdr.cancel_flag = 'N'
     AND dbo.p21_view_oe_hdr.completed <> 'T'
     AND Coalesce(dbo.p21_view_oe_hdr.order_type,0) NOT IN (1877, 1920)
     AND dbo.p21_view_oe_hdr.order_date in (select max(oe_hdr2.order_date) 
	                                        FROM dbo.p21_view_oe_hdr oe_hdr2
											where oe_hdr2.customer_id = dbo.p21_view_oe_hdr.customer_id
										  	  AND oe_hdr2.projected_order = 'N' 
                                              AND oe_hdr2.rma_flag = 'N'
                                              AND oe_hdr2.delete_flag = 'N'
                                              AND oe_hdr2.cancel_flag = 'N'
                                              AND oe_hdr2.completed <> 'T'
                                              AND Coalesce(oe_hdr2.order_type,0) NOT IN (1877, 1920)) 
   GROUP BY dbo.p21_view_oe_hdr.company_id, dbo.p21_view_oe_hdr.customer_id, dbo.p21_view_oe_hdr.order_no, dbo.p21_view_oe_hdr.order_date
	          ) AS order_info ON (order_info.company_id = crm.company_id)
                    AND (order_info.customer_id = crm.customer_id)

-- 2 minutes 6 seconds
/*
	UPDATE crm
	SET  last_invoice_date = invoice_info.date_created,
	     last_invoice_amount = invoice_info.total_amount
	FROM @terms_eval_report crm   
	LEFT JOIN (select dbo.p21_view_invoice_hdr.company_no, dbo.p21_view_invoice_hdr.customer_id,
	                  dbo.p21_view_invoice_hdr.total_amount, dbo.p21_view_invoice_hdr.date_created
               from dbo.p21_view_invoice_hdr
               where dbo.p21_view_invoice_hdr.invoice_type = 'IN' 
                 AND dbo.p21_view_invoice_hdr.invoice_adjustment_type = 'I'
                 AND dbo.p21_view_invoice_hdr.approved = 'Y'
                 AND dbo.p21_view_invoice_hdr.consolidated = 'N'
                 AND dbo.p21_view_invoice_hdr.date_created = (select max(invoice_hdr2.date_created) 
                                                 from dbo.p21_view_invoice_hdr invoice_hdr2
												 where invoice_hdr2.customer_id = dbo.p21_view_invoice_hdr.customer_id
												 AND invoice_hdr2.invoice_type = 'IN' 
                                                 AND invoice_hdr2.invoice_adjustment_type = 'I'
                                                 AND invoice_hdr2.approved = 'Y'
                                                 AND invoice_hdr2.consolidated = 'N')
			  ) AS invoice_info ON (invoice_info.company_no = crm.company_id)
                               AND (invoice_info.customer_id = crm.customer_id)
*/

-- 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_over_90_days = artb_info.ar_over_90_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 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_90_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


