ALTER PROCEDURE [dbo].[sp_DataLoad_di_Customer_import] (
@ProcessId varchar(100), @OrganizationID varchar(100), @TenantId varchar(50)
)
AS
BEGIN
/*
--Version 1 by Wei Joe - Master Data Auto Creation
--2024/02/26-RL: version 2: Changed di_customer_import to #tempCus for performance improvement
--2024/03/20-RL: version 3: Changed master data to insert/update based on tenant_id
*/

SET NOCOUNT ON;

	Select * into #tempCus from di_Customer_import where process_id = @ProcessId and org_id = @OrganizationID

	Declare @defaultBuyingGroup1Id varchar(50) = '';
	 Select top 1 @defaultBuyingGroup1Id = buying_group_id_one from mst_buying_group_one where tenant_id = @TenantId order by is_default desc

    IF((select count(1) from #tempCus where IsNull(error_message,'') = '') > 0)
        BEGIN
					
			---------------------------------------------Update Insert Country------------------------------------
			UPDATE country set country.country_code = x.country_code
			FROM 
			(
			select country_code, tenant_id from 
			#tempCus --where process_id = @ProcessId
			group by country_code, tenant_id
			) x
			where country.country_code = x.country_code and country.tenant_id = x.tenant_id
			--and di_Customer_import.process_id = @ProcessId 
			and x.tenant_id = @TenantId

			INSERT into country (country_id, country_code, country_desc, status, created_date, created_by, updated_date, updated_by, tenant_id, org_id)
			SELECT NEWID(), x.country_code, x.country_code, 'Active', GETDATE(), x.created_by, GETDATE(), x.updated_by, x.tenant_id, x.tenant_id 
			FROM 
			(
			select country_code, created_by, updated_by, tenant_id from 
			#tempCus where IsNull(country_code,'') <> ''
			group by country_code, created_by, updated_by, tenant_id
			) x
			WHERE NOT EXISTS(SELECT 1 FROM country WHERE country.country_code=x.country_code and country.tenant_id=x.tenant_id)
			--and x.process_id = @ProcessId 
			and x.tenant_id = @TenantId
			
			
			UPDATE country set country.country_code = x.billing_country_code
			FROM 
			(
			select billing_country_code, tenant_id from 
			#tempCus --where process_id = @ProcessId
			group by billing_country_code, tenant_id
			) x
			where country.country_code = x.billing_country_code and country.tenant_id = x.tenant_id
			--and di_Customer_import.process_id = @ProcessId 
			and x.tenant_id = @TenantId

			INSERT into country (country_id, country_code, country_desc, status, created_date, created_by, updated_date, updated_by, tenant_id, org_id)
			SELECT NEWID(), x.billing_country_code, x.billing_country_code, 'Active', GETDATE(), x.created_by, GETDATE(), x.updated_by, x.tenant_id, x.tenant_id 
			FROM 
			(
			select billing_country_code, created_by, updated_by, tenant_id from 
			#tempCus  where IsNull(billing_country_code,'') <> ''
			group by billing_country_code, created_by, updated_by, tenant_id
			) x
			WHERE NOT EXISTS(SELECT 1 FROM country WHERE country.country_code=x.billing_country_code and country.tenant_id=x.tenant_id)
			--and x.process_id = @ProcessId 
			and x.tenant_id = @TenantId

			UPDATE country set country.country_code = x.shipping_country_code
			FROM 
			(
			select shipping_country_code, tenant_id from 
			#tempCus --where process_id = @ProcessId
			group by shipping_country_code, tenant_id
			) x
			where country.country_code = x.shipping_country_code and country.tenant_id = x.tenant_id
			--and di_Customer_import.process_id = @ProcessId 
			and x.tenant_id = @TenantId

			INSERT into country (country_id, country_code, country_desc, status, created_date, created_by, updated_date, updated_by, tenant_id, org_id)
			SELECT NEWID(), x.shipping_country_code, x.shipping_country_code, 'Active', GETDATE(), x.created_by, GETDATE(), x.updated_by, x.tenant_id, x.tenant_id 
			FROM 
			(
			select shipping_country_code, created_by, updated_by, tenant_id from 
			#tempCus  where IsNull(shipping_country_code,'') <> ''
			group by shipping_country_code, created_by, updated_by, tenant_id
			) x
			WHERE NOT EXISTS(SELECT 1 FROM country WHERE country.country_code=x.shipping_country_code and country.tenant_id=x.tenant_id)
			--and x.process_id = @ProcessId 
			and x.tenant_id = @TenantId
			

			--------------------------------------------Update Insert Region---------------------------------------
			UPDATE mst_region SET mst_region.region_code = x.region_code, mst_region.country_id = c.country_id
			FROM 
			(
			select di.region_code, di.country_code, di.tenant_id, di.org_id from #tempCus di
			--where process_id = @ProcessId
			group by di.region_code, di.country_code, di.tenant_id, di.org_id
			) x
			, Country c
			where mst_region.region_code = x.region_code and mst_region.org_id = x.org_id and c.country_code = x.country_code and c.tenant_id = x.tenant_id
			--and di.process_id = @ProcessId 
			and x.org_id = @OrganizationID
			
			INSERT into mst_region (region_id, region_code, region_desc, status, country_id, created_date, created_by, updated_date, updated_by, tenant_id, org_id)
			SELECT NEWID(), x.region_code, x.region_code, 'Active', c.country_id, GETDATE(), x.created_by, GETDATE(), x.updated_by, x.tenant_id, x.org_id 
			from 
			(
			select di.region_code, di.country_code, di.created_by, di.updated_by, di.tenant_id, di.org_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.region_code, di.country_code, di.created_by, di.updated_by, di.tenant_id, di.org_id
			) x
			inner join country c on c.country_code = x.country_code and c.tenant_id = x.tenant_id
			where NOT EXISTS(SELECT 1 FROM mst_region WHERE mst_region.region_code=x.region_code and mst_region.org_id=x.org_id AND mst_region.tenant_id = x.tenant_id) 
			--and di.process_id = @ProcessId 
			and x.org_id = @OrganizationID


			
			UPDATE mst_region SET mst_region.region_code = x.billing_region_code, mst_region.country_id = c.country_id
			FROM 
			(
			select di.billing_region_code, di.billing_country_code, di.tenant_id, di.org_id from #tempCus di
			--where process_id = @ProcessId
			group by di.billing_region_code, di.billing_country_code, di.tenant_id, di.org_id
			) x
			, Country c
			where mst_region.region_code = x.billing_region_code and mst_region.org_id = x.org_id and c.country_code = x.billing_country_code and c.tenant_id = x.tenant_id
			--and di.process_id = @ProcessId 
			and x.org_id = @OrganizationID
			
			INSERT into mst_region (region_id, region_code, region_desc, status, country_id, created_date, created_by, updated_date, updated_by, tenant_id, org_id)
			SELECT NEWID(), x.billing_region_code, x.billing_region_code, 'Active', c.country_id, GETDATE(), x.created_by, GETDATE(), x.updated_by, x.tenant_id, x.org_id 
			from 
			(
			select di.billing_region_code, di.billing_country_code, di.created_by, di.updated_by, di.tenant_id, di.org_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.billing_region_code, di.billing_country_code, di.created_by, di.updated_by, di.tenant_id, di.org_id
			) x
			inner join country c on c.country_code = x.billing_country_code and c.tenant_id = x.tenant_id
			where NOT EXISTS(SELECT 1 FROM mst_region WHERE mst_region.region_code=x.billing_region_code and mst_region.org_id=x.org_id AND mst_region.tenant_id = x.tenant_id) 
			--and di.process_id = @ProcessId 
			and x.org_id = @OrganizationID

			UPDATE mst_region SET mst_region.region_code = x.shipping_region_code, mst_region.country_id = c.country_id
			FROM 
			(
			select di.shipping_region_code, di.shipping_country_code, di.tenant_id, di.org_id from #tempCus di
			--where process_id = @ProcessId
			group by di.shipping_region_code, di.shipping_country_code, di.tenant_id, di.org_id
			) x
			, Country c
			where mst_region.region_code = x.shipping_region_code and mst_region.org_id = x.org_id and c.country_code = x.shipping_country_code and c.tenant_id = x.tenant_id
			--and di.process_id = @ProcessId 
			and x.org_id = @OrganizationID
			
			INSERT into mst_region (region_id, region_code, region_desc, status, country_id, created_date, created_by, updated_date, updated_by, tenant_id, org_id)
			SELECT NEWID(), x.shipping_region_code, x.shipping_region_code, 'Active', c.country_id, GETDATE(), x.created_by, GETDATE(), x.updated_by, x.tenant_id, x.org_id 
			from 
			(
			select di.shipping_region_code, di.shipping_country_code, di.created_by, di.updated_by, di.tenant_id, di.org_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.shipping_region_code, di.shipping_country_code, di.created_by, di.updated_by, di.tenant_id, di.org_id
			) x
			inner join country c on c.country_code = x.shipping_country_code and c.tenant_id = x.tenant_id
			where NOT EXISTS(SELECT 1 FROM mst_region WHERE mst_region.region_code=x.shipping_region_code and mst_region.org_id=x.org_id AND mst_region.tenant_id = x.tenant_id) 
			--and di.process_id = @ProcessId 
			and x.org_id = @OrganizationID

			
			--------------------------------------------Update Insert State---------------------------------------
			UPDATE state SET state.state_code = x.state_code, state.country_id = c.country_id, state.region_id = r.region_id
			FROM 
			(
			select di.state_code, di.region_code, di.country_code, di.tenant_id, di.org_id from #tempCus di
			--where process_id = @ProcessId
			group by di.state_code, di.region_code, di.country_code, di.tenant_id, di.org_id
			) x
			, Country c, mst_region r
			where state.state_code = x.state_code and state.org_id = x.org_id and c.country_code = x.country_code and c.tenant_id = x.tenant_id and r.region_code = x.region_code and r.org_id = x.org_id
			--and di.process_id = @ProcessId 
			and x.org_id = @OrganizationID
			
			INSERT into state (state_id, state_code, state_desc, status, country_id, created_date, created_by, updated_date, updated_by, tenant_id, org_id, region_id)
			SELECT NEWID(), x.state_code, x.state_code, 'Active', c.country_id, GETDATE(), x.created_by, GETDATE(), x.updated_by, x.tenant_id, x.org_id, r.region_id 
			from 
			(
			select di.state_code, di.region_code, di.country_code, di.created_by, di.updated_by, di.tenant_id, di.org_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.state_code, di.region_code, di.country_code, di.created_by, di.updated_by, di.tenant_id, di.org_id
			) x
			inner join country c on c.country_code = x.country_code and c.tenant_id = x.tenant_id
			inner join mst_region r on r.region_code = x.region_code and r.org_id = x.org_id
			where NOT EXISTS(SELECT 1 FROM state WHERE state.state_code=x.state_code and state.org_id=x.org_id) 
			--and di.process_id = @ProcessId 
			and x.org_id = @OrganizationID

			
			UPDATE state SET state.state_code = x.billing_state_code, state.country_id = c.country_id, state.region_id = r.region_id
			FROM 
			(
			select di.billing_state_code, di.billing_region_code, di.billing_country_code, di.tenant_id, di.org_id from #tempCus di
			--where process_id = @ProcessId
			group by di.billing_state_code, di.billing_region_code, di.billing_country_code, di.tenant_id, di.org_id
			) x
			, Country c, mst_region r
			where state.state_code = x.billing_state_code and state.org_id = x.org_id and c.country_code = x.billing_country_code and c.tenant_id = x.tenant_id and r.region_code = x.billing_region_code and r.org_id = x.org_id
			--and di.process_id = @ProcessId 
			and x.org_id = @OrganizationID
			
			INSERT into state (state_id, state_code, state_desc, status, country_id, created_date, created_by, updated_date, updated_by, tenant_id, org_id, region_id)
			SELECT NEWID(), x.billing_state_code, x.billing_state_code, 'Active', c.country_id, GETDATE(), x.created_by, GETDATE(), x.updated_by, x.tenant_id, x.org_id, r.region_id 
			from 
			(
			select di.billing_state_code, di.billing_region_code, di.billing_country_code, di.created_by, di.updated_by, di.tenant_id, di.org_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.billing_state_code, di.billing_region_code, di.billing_country_code, di.created_by, di.updated_by, di.tenant_id, di.org_id
			) x
			inner join country c on c.country_code = x.billing_country_code and c.tenant_id = x.tenant_id
			inner join mst_region r on r.region_code = x.billing_region_code and r.org_id = x.org_id
			where NOT EXISTS(SELECT 1 FROM state WHERE state.state_code=x.billing_state_code and state.org_id=x.org_id) 
			--and di.process_id = @ProcessId 
			and x.org_id = @OrganizationID

			UPDATE state SET state.state_code = x.shipping_state_code, state.country_id = c.country_id, state.region_id = r.region_id
			FROM 
			(
			select di.shipping_state_code, di.shipping_region_code, di.shipping_country_code, di.tenant_id, di.org_id from #tempCus di
			--where process_id = @ProcessId
			group by di.shipping_state_code, di.shipping_region_code, di.shipping_country_code, di.tenant_id, di.org_id
			) x
			, Country c, mst_region r
			where state.state_code = x.shipping_state_code and state.org_id = x.org_id and c.country_code = x.shipping_country_code and c.tenant_id = x.tenant_id and r.region_code = x.shipping_region_code and r.org_id = x.org_id
			--and di.process_id = @ProcessId 
			and x.org_id = @OrganizationID
			
			INSERT into state (state_id, state_code, state_desc, status, country_id, created_date, created_by, updated_date, updated_by, tenant_id, org_id, region_id)
			SELECT NEWID(), x.shipping_state_code, x.shipping_state_code, 'Active', c.country_id, GETDATE(), x.created_by, GETDATE(), x.updated_by, x.tenant_id, x.org_id, r.region_id 
			from 
			(
			select di.shipping_state_code, di.shipping_region_code, di.shipping_country_code, di.created_by, di.updated_by, di.tenant_id, di.org_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.shipping_state_code, di.shipping_region_code, di.shipping_country_code, di.created_by, di.updated_by, di.tenant_id, di.org_id
			) x
			inner join country c on c.country_code = x.shipping_country_code and c.tenant_id = x.tenant_id
			inner join mst_region r on r.region_code = x.shipping_region_code and r.org_id = x.org_id
			where NOT EXISTS(SELECT 1 FROM state WHERE state.state_code=x.shipping_state_code and state.org_id=x.org_id) 
			--and di.process_id = @ProcessId 
			and x.org_id = @OrganizationID
					
					
			---------------------------------------------Update Insert Chain--------------------------------------
			UPDATE chain SET chain.chain_code = x.chain_code
			FROM 
			(
			select di.chain_code, di.tenant_id, di.org_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.chain_code, di.tenant_id, di.org_id
			) x
			where chain.chain_code = x.chain_code and chain.tenant_id = x.tenant_id
			--and x.process_id = @ProcessId 
			and x.tenant_id = @TenantId and  x.org_id=@OrganizationID

			INSERT into chain (chain_id, org_id, chain_code, chain_desc, chain_short_desc, status, created_date, created_by, updated_date, updated_by, tenant_id)
			SELECT NEWID(), x.org_id, x.chain_code, x.chain_code, x.chain_code, 'Active', GETDATE(), x.created_by, GETDATE(), x.updated_by, x.tenant_id
			from 
			(
			select di.chain_code, di.created_by, di.updated_by, di.tenant_id, di.org_id from #tempCus di
			where IsNull(di.chain_code,'') <> ''
			group by di.chain_code, di.created_by, di.updated_by, di.tenant_id, di.org_id
			) x
			WHERE NOT EXISTS(SELECT 1 FROM chain WHERE chain.chain_code = x.chain_code and chain.tenant_id = x.tenant_id) --and chain.org_id=@OrganizationID)
			--and x.process_id = @ProcessId 
			and x.tenant_id =  @TenantId 
			--arif 20240607 start
			and x.org_id=@OrganizationID
			--arif 20240607 end

			
			-------------------------------------------------Update Insert Channel (Channel maintain by org & tenant for Suntory) ------------------------------------
			UPDATE channel SET channel_code = x.channel_code
			FROM
			(
			select di.channel_code, di.org_id, di.tenant_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.channel_code, di.org_id, di.tenant_id
			) x
			where channel.channel_code = x.channel_code and channel.tenant_id = x.tenant_id
			--and x.process_id = @ProcessId 
			and x.org_id = @OrganizationID

			INSERT into channel (channel_id, org_id, channel_code, channel_desc, channel_short_desc, sector_id, sub_channel, status, created_date, created_by, updated_date, updated_by, tenant_id)
			SELECT NEWID(), x.org_id, x.channel_code, x.channel_code, x.channel_code, 'NA', 'NA', 'Active', GETDATE(), x.created_by, GETDATE(), x.updated_by, x.tenant_id
			FROM 
			(
			select di.channel_code, di.created_by, di.updated_by, di.org_id, di.tenant_id from #tempCus di
			 where IsNull(di.channel_code,'') <> ''
			group by di.channel_code, di.org_id, di.created_by, di.updated_by, di.tenant_id
			) x 
			WHERE NOT EXISTS(SELECT 1 FROM channel WHERE channel.channel_code = x.channel_code and channel.tenant_id = x.tenant_id)
			--and di.process_id = @ProcessId 
			and x.org_id = @OrganizationID

			
			-----------------------------------------------------Update Insert Cluster------------------------------------
			UPDATE cluster SET cluster.cluster_code = x.cluster_code
			FROM 
			(
			select di.cluster_code, di.channel_code, di.org_id, di.tenant_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.cluster_code, di.channel_code, di.org_id, di.tenant_id
			) x
			inner join channel c on c.channel_code = x.channel_code and c.tenant_id = x.tenant_id
			where cluster.cluster_code = x.cluster_code and cluster.channel_id = c.channel_id and cluster.tenant_id = x.tenant_id
			--and x.process_id = @ProcessId 
			and x.org_id = @OrganizationID

			INSERT into cluster (cluster_id, channel_id, org_id, cluster_code, cluster_desc, cluster_short_desc, status, created_date, created_by, updated_date, updated_by, tenant_id)
			SELECT NEWID(), c.channel_id, x.tenant_id, x.cluster_code, x.cluster_code, x.cluster_code, 'Active', GETDATE(), x.created_by, GETDATE(), x.updated_by, x.tenant_id
			from 
			(
			select di.cluster_code, di.org_id, di.created_by, di.updated_by, di.tenant_id, di.channel_code from #tempCus di
			 where IsNull(di.cluster_code,'') <> ''
			group by di.cluster_code, di.org_id, di.created_by, di.updated_by, di.tenant_id, di.channel_code
			) x
			inner join channel c on c.channel_code = x.channel_code and c.tenant_id = x.tenant_id
			where NOT EXISTS(SELECT 1 FROM cluster WHERE cluster.cluster_code=x.cluster_code and cluster.channel_id = c.channel_id and cluster.tenant_id = x.tenant_id) 
			--and di.process_id = @ProcessId 
			and x.org_id = @OrganizationID
			
			
			-------------------------------------------------Update Insert Mst Branch (Branch maintain at org level for Suntory) -----------------------------------------------
			UPDATE mst_branch set branch_code = x.branch_code
			FROM 
			(
			select di.branch_code, di.org_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.branch_code, di.org_id
			) x
			where mst_branch.branch_code = x.branch_code and mst_branch.org_id = x.org_id
			--and x.process_id = @ProcessId 
			and x.org_id = @OrganizationID

			INSERT into mst_branch (branch_id, branch_code, branch_desc, status, created_date, created_by, updated_date, updated_by, tenant_id, org_id)
			SELECT NEWID(), x.branch_code, x.branch_code, 'Active', GETDATE(), x.created_by, GETDATE(), x.updated_by, x.tenant_id, x.org_id 
			from 
			(
			select di.branch_code, di.created_by, di.updated_by, di.org_id, di.tenant_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.branch_code, di.created_by, di.updated_by, di.org_id, di.tenant_id
			) x
			where NOT EXISTS(SELECT 1 FROM mst_branch WHERE mst_branch.branch_code = x.branch_code and mst_branch.org_id=x.org_id)
			--and x.process_id = @ProcessId 
			and x.org_id = @OrganizationID

			
			---------------------------------------------------Update Insert Mst Area-------------------------------------------------
			UPDATE mst_area SET mst_area.area_code = x.area_code
			FROM 
			(
			select di.area_code, di.org_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.area_code, di.org_id
			) x
			where mst_area.area_code = x.area_code and mst_area.org_id = x.org_id
			--and x.process_id = @ProcessId 
			and x.org_id = @OrganizationID

			INSERT into mst_area (area_id, area_code, area_description, status, branch_id, created_date, created_by, updated_date, updated_by, tenant_id, org_id)
			SELECT NEWID(), x.area_code, x.area_code, 'Active', b.branch_id, GETDATE(), x.created_by, GETDATE(), x.updated_by, x.tenant_id, x.org_id 
			FROM 
			(
			select  di.area_code, di.branch_code, di.created_by, di.updated_by, di.org_id, di.tenant_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.area_code, di.branch_code, di.created_by, di.updated_by, di.org_id, di.tenant_id
			) x
			inner join mst_branch b on b.branch_code = x.branch_code and b.org_id = x.org_id
			where NOT EXISTS(SELECT 1 FROM mst_area WHERE mst_area.area_code=x.area_code and mst_area.org_id=x.org_id and mst_area.branch_id = b.branch_id)
			--and x.process_id = @ProcessId 
			and x.org_id = @OrganizationID

			
			---------------------------------------------Update Insert Mst Terms Code-------------------------------------------
			UPDATE mst_terms_code set mst_terms_code.terms_code = x.terms_code
			FROM 
			(
			select di.terms_code, di.tenant_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.terms_code, di.tenant_id
			) x
			where mst_terms_code.terms_code = x.terms_code and mst_terms_code.tenant_id = x.tenant_id
			--and x.process_id = @ProcessId 
			and x.tenant_id = @TenantId

			INSERT into mst_terms_code (terms_id, terms_code, terms_description, terms_days, status, created_date, created_by, updated_date, updated_by, org_id, tenant_id)
			SELECT NEWID(), x.terms_code, x.terms_code, x.terms_code, 'Active', GETDATE(), x.created_by, GETDATE(), x.updated_by, x.tenant_id, x.tenant_id 
			FROM 
			(
			select di.terms_code, di.created_by, di.updated_by, di.tenant_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.terms_code, di.created_by, di.updated_by, di.tenant_id
			) x
			where NOT EXISTS(SELECT 1 FROM mst_terms_code WHERE mst_terms_code.terms_code=x.terms_code and mst_terms_code.tenant_id=x.tenant_id)
			--and x.process_id = @ProcessId 
			and x.tenant_id = @TenantId

			
			-----------------------------------------------Update Insert Mst Price Code (Maintain at org and tenant level) -------------------------------------------
			UPDATE mst_price_code set mst_price_code.price_code = x.price_code
			FROM 
			(
			select di.price_code, di.org_id, di.tenant_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.price_code, di.org_id, di.tenant_id
			) x
			where mst_price_code.price_code = x.price_code and mst_price_code.org_id = x.org_id and mst_price_code.tenant_id = x.tenant_id
			--and x.process_id = @ProcessId 
			and x.org_id = @OrganizationID

			INSERT into mst_price_code (price_id, price_code, price_description, status, created_date, created_by, updated_date, updated_by, org_id, tenant_id)
			SELECT NEWID(), x.price_code, x.price_code, 'Active', GETDATE(), x.created_by, GETDATE(), x.updated_by, x.org_id, x.tenant_id 
			from 
			(
			select di.price_code, di.created_by, di.updated_by, di.org_id, di.tenant_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.price_code, di.created_by, di.updated_by, di.org_id, di.tenant_id
			) x
			WHERE NOT EXISTS (SELECT 1 FROM mst_price_code WHERE mst_price_code.price_code=x.price_code and mst_price_code.org_id=x.org_id and mst_price_code.tenant_id=x.tenant_id)
			--and x.process_id = @ProcessId 
			and x.org_id = @OrganizationID

			
			-----------------------------------------------Update Insert Mst Invoice Discount---------------------------------
			UPDATE mst_invoice_discount set mst_invoice_discount.invoice_discount_code = x.invoice_discount_code
			FROM 
			(
			select di.invoice_discount_code, di.tenant_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.invoice_discount_code, di.tenant_id
			) x
			where mst_invoice_discount.invoice_discount_code = x.invoice_discount_code and mst_invoice_discount.tenant_id = x.tenant_id
			--and x.process_id = @ProcessId 
			and x.tenant_id =  @TenantId

			INSERT into mst_invoice_discount(invoice_discount_id, invoice_discount_code, invoice_description, status, created_date, created_by, updated_date, updated_by, org_id, tenant_id)
			SELECT NEWID(), x.invoice_discount_code, x.invoice_discount_code, 'Active', GETDATE(), x.created_by, GETDATE(), x.updated_by, x.tenant_id, x.tenant_id  
			from 
			(
			select di.invoice_discount_code, di.created_by, di.updated_by, di.tenant_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.invoice_discount_code, di.created_by, di.updated_by, di.tenant_id
			) x
			WHERE NOT EXISTS(SELECT 1 FROM mst_invoice_discount WHERE mst_invoice_discount.invoice_discount_code=x.invoice_discount_code and mst_invoice_discount.tenant_id=x.tenant_id)
			--and x.process_id = @ProcessId 
			and x.tenant_id = @TenantId

			
			-----------------------------------------------Update Insert Buying Group One---------------------------------
			UPDATE mst_buying_group_one set mst_buying_group_one.buying_group_one_code = x.buying_group_one_code
			FROM 
			(
			select di.buying_group_one_code, di.tenant_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.buying_group_one_code, di.tenant_id
			) x
			where mst_buying_group_one.buying_group_one_code = x.buying_group_one_code and mst_buying_group_one.tenant_id = x.tenant_id
			--and x.process_id = @ProcessId 
			and x.tenant_id =  @TenantId

			INSERT into mst_buying_group_one(buying_group_id_one, buying_group_one_code, buying_group_one_desc, status, created_date, created_by, updated_date, updated_by, org_id, tenant_id)
			SELECT NEWID(), x.buying_group_one_code, x.buying_group_one_code, 'Active', GETDATE(), x.created_by, GETDATE(), x.updated_by, x.tenant_id, x.tenant_id  
			from 
			(
			select di.buying_group_one_code, di.created_by, di.updated_by, di.tenant_id from #tempCus di
			where IsNull(di.buying_group_one_code,'') <>''
			group by di.buying_group_one_code, di.created_by, di.updated_by, di.tenant_id
			) x
			WHERE NOT EXISTS(SELECT 1 FROM mst_buying_group_one WHERE mst_buying_group_one.buying_group_one_code=x.buying_group_one_code and mst_buying_group_one.tenant_id=x.tenant_id)
			--and x.process_id = @ProcessId 
			and x.tenant_id =  @TenantId


			-----------------------------------------------Update Insert Buying Group Two---------------------------------
			UPDATE mst_buying_group_two set mst_buying_group_two.buying_group_two_code = x.buying_group_two_code
			FROM 
			(
			select di.buying_group_two_code, di.tenant_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.buying_group_two_code, di.tenant_id
			) x
			where mst_buying_group_two.buying_group_two_code = x.buying_group_two_code and mst_buying_group_two.tenant_id = x.tenant_id
			--and x.process_id = @ProcessId 
			and x.tenant_id =  @TenantId

			INSERT into mst_buying_group_two(buying_group_id_two, buying_group_two_code, buying_group_two_desc, status, created_date, created_by, updated_date, updated_by, org_id, tenant_id)
			SELECT NEWID(), x.buying_group_two_code, x.buying_group_two_code, 'Active', GETDATE(), x.created_by, GETDATE(), x.updated_by, x.tenant_id, x.tenant_id  
			from 
			(
			select di.buying_group_two_code, di.created_by, di.updated_by, di.tenant_id from #tempCus di
			 where IsNull(di.buying_group_two_code,'') <> ''
			group by di.buying_group_two_code, di.created_by, di.updated_by, di.tenant_id
			) x
			WHERE NOT EXISTS(SELECT 1 FROM mst_buying_group_two WHERE mst_buying_group_two.buying_group_two_code=x.buying_group_two_code and mst_buying_group_two.tenant_id=x.tenant_id)
			--and x.process_id = @ProcessId 
			and x.tenant_id =  @TenantId


			-----------------------------------------------Update Insert Buying Group Three---------------------------------
			UPDATE mst_buying_group_three set mst_buying_group_three.buying_group_three_code = x.buying_group_three_code
			FROM 
			(
			select di.buying_group_three_code, di.tenant_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.buying_group_three_code, di.tenant_id
			) x
			where mst_buying_group_three.buying_group_three_code = x.buying_group_three_code and mst_buying_group_three.tenant_id = x.tenant_id
			--and x.process_id = @ProcessId 
			and x.tenant_id =  @TenantId

			INSERT into mst_buying_group_three(buying_group_id_three, buying_group_three_code, buying_group_three_desc, status, created_date, created_by, updated_date, updated_by, org_id, tenant_id)
			SELECT NEWID(), x.buying_group_three_code, x.buying_group_three_code, 'Active', GETDATE(), x.created_by, GETDATE(), x.updated_by, x.tenant_id, x.tenant_id  
			from 
			(
			select di.buying_group_three_code, di.created_by, di.updated_by, di.tenant_id from #tempCus di
			 where IsNull(di.buying_group_three_code,'') <> ''
			group by di.buying_group_three_code, di.created_by, di.updated_by, di.tenant_id
			) x
			WHERE NOT EXISTS(SELECT 1 FROM mst_buying_group_three WHERE mst_buying_group_three.buying_group_three_code=x.buying_group_three_code and mst_buying_group_three.tenant_id=x.tenant_id)
			--and x.process_id = @ProcessId 
			and x.tenant_id =  @TenantId

			


			------------------------------------------------Update Insert member-----------------------------------------------

			update member set member.member_cd = x.person_in_charge
			FROM 
			(
			select di.person_in_charge, di.org_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.person_in_charge, di.org_id
			) x
			where member.member_cd = x.person_in_charge and member.org_id = x.org_id
			--and x.process_id = @ProcessId 
			and x.org_id = @OrganizationID

			INSERT into member (member_id, member_cd, org_id, first_name, created_date, created_by, updated_date, updated_by, status)
			SELECT NEWID(), x.person_in_charge, x.org_id, x.person_in_charge, GETDATE(), x.created_by, GETDATE(), x.updated_by, 'Active'
			FROM 
			(
			select di.person_in_charge, di.created_by, di.updated_by, di.org_id, di.tenant_id from #tempCus di
			--where di.process_id = @ProcessId
			group by di.person_in_charge, di.created_by, di.updated_by, di.org_id, di.tenant_id
			) x
			WHERE NOT EXISTS(SELECT 1 FROM member WHERE member.member_cd=x.person_in_charge and member.org_id=x.org_id)
			--and x.process_id = @ProcessId 
			and x.org_id = @OrganizationID



			------------------------------------------------Update Insert member branch-----------------------------------------------

            INSERT into member_branch (id, org_id, tenant_id, member_id, branch_id, status, created_date, created_by, updated_date, updated_by)
            SELECT NEWID() [id], di.org_id [org_id], di.tenant_id [tenant_id], m.member_id [member_id], b.branch_id [branch_id], 
                'Active' [status], GetDate() [created_date], di.created_by [created_by], GetDate() [updated_date], di.updated_by [updated_by]
            from #tempCus di
                left join member m on m.member_cd = di.person_in_charge
                left join mst_branch b on b.branch_code = di.branch_code
            where --di.process_id = @ProcessId and 
				m.org_id = di.org_id and b.org_id = di.org_id
                and Not Exists (Select 1 from member_branch mb where mb.member_id = m.member_id and mb.branch_id = b.branch_id)
            Group By di.org_id, di.tenant_id, m.member_id, b.branch_id, di.created_by, di.updated_by
			
			

			----------------------------------------------Update Insert org_hierarchy ------------------------------------------------------
			Declare @salesmanLevelParent varchar(50) = '', @groupCheck int = 0, @hierarchyId varchar(50) = '',  @salesmanLevel varchar(50) =''


			--Select Top 1 @TenantId = tenant_id from #tempCus where process_id = @ProcessId
			
			-- Identify Level above salesman level
			Select @salesmanLevelParent = l2.hierarchy_level_id, @salesmanLevel = l.hierarchy_level_id
			from org_hierarchy_level l
			left join org_hierarchy_level l2 on l2.hierarchy_level = (l.hierarchy_level - 1)
			where l.hierarchy_type = 'Salesman' and l.tenant_id = @TenantId
			and l2.tenant_id = @TenantId

			-- Check if have existing Salesman group
			Select @groupCheck = Count(hierarchy_group_id) from org_hierarchy_group g
			left join org_hierarchy_level l on l.hierarchy_level_id = g.hierarchy_level_id
			where l.hierarchy_type = 'Salesman' and g.org_id = @OrganizationID

			-- If 0 / >1 existing salesman group, create new. Else reuse existing.
			IF (@groupCheck <> 1)
			BEGIN
				SET @hierarchyId = 'Imported-Salesman-Group-' + @OrganizationID;

				Insert into org_hierarchy_group (hierarchy_group_id, hierarchy_level_id, hierarchy_group_name, hierarchy_group_parent, status, created_by, created_date, updated_by, updated_date, org_id, tenant_id) 
				Select 'Imported-Salesman-Group-' + @OrganizationID [hierarchy_group_id], @salesmanLevel [hierarchy_level_id], 'Imported-Salesman-Group-'+@OrganizationID [hierarchy_group_name], 
					Max(g.hierarchy_group_id) [hierarchy_group_parent], 'Active' [status], 'System' [created_by], GetDate() [created_date], 'System' [updated_by], GetDate() [updated_date], @OrganizationID [org_id], @TenantId [tenant_id]
				from org_hierarchy_group g
				where g.org_id = @OrganizationID and g.tenant_id = @TenantId
				and g.hierarchy_level_id = @salesmanLevelParent
				and Not Exists (Select 1 from org_hierarchy_group db where db.org_id = @OrganizationID and db.tenant_id = @TenantId and db.hierarchy_level_id = @salesmanLevel and db.hierarchy_group_name = 'Imported-Salesman-Group-' + @OrganizationID)
				Group By g.hierarchy_level_id
			END
			ELSE
			BEGIN
				Select @hierarchyId = IsNull(Max(hierarchy_group_id), '') from org_hierarchy_group g
				left join org_hierarchy_level l on l.hierarchy_level_id = g.hierarchy_level_id
				where l.hierarchy_type = 'Salesman' and g.org_id = @OrganizationID
			END
				
			-- == Insert into group members ==
			Insert into org_hierarchy_group_member (hierarchy_member_id, hierarchy_group_id, member_id, status, created_by, created_date, updated_by, updated_date, org_id, tenant_id)
			Select NewId() [hierarchy_member_id], 
			@hierarchyId [hierarchy_group_id], 
			m.member_id [member_id], 'Active' [status], 'System' [created_by], GetDate() [created_date], 'System' [updated_by], GetDate() [updated_date], @OrganizationID [org_id], @TenantId [tenant_id]
			from #tempCus di
			Left Join member m on m.member_cd = di.person_in_charge and m.org_id = di.org_id
			where  --di.process_id = @ProcessId 
			 Not Exists (Select 1 from org_hierarchy_group_member gm where gm.member_id = m.member_id and gm.org_id = @OrganizationID and gm.tenant_id = @TenantId)
			 and m.org_id = @OrganizationID
			Group By m.member_id

			-- == Update Hierarchy Mapping ==
			exec web_spUpdateOrgHierarchyMapping 
				@OrgId = @OrganizationID,
				@TenantId = @TenantId,
				@LoginId = 'System',
				@HierarchyGroupId = @hierarchyId,
				@Task = 'Insert',
				@DeleteKey = '',
				@NewParentId = '',
				@OldParentId = ''

				
			------------------------------------------------Update Insert customer class-----------------------------------------------

			UPDATE mst_customer_class set mst_customer_class.customer_class_code = x.customer_class_code
			FROM 
			(
			select customer_class_code, org_id, tenant_id from 
			#tempCus -- where process_id = @ProcessId
			group by customer_class_code, org_id, tenant_id
			) x
			where mst_customer_class.customer_class_code = x.customer_class_code and mst_customer_class.tenant_id = x.tenant_id
			--and di_Customer_import.process_id = @ProcessId 
			and x.org_id = @OrganizationID

			INSERT into mst_customer_class (customer_class_id, customer_class_code, customer_class_description, status, created_date, created_by, updated_date, updated_by, tenant_id, org_id)
			SELECT NEWID(), x.customer_class_code, x.customer_class_code, 'Active', GETDATE(), x.created_by, GETDATE(), x.updated_by, x.tenant_id, x.tenant_id 
			FROM 
			(
			select customer_class_code, org_id, created_by, updated_by, tenant_id from 
			#tempCus --where process_id = @ProcessId
			where IsNull(customer_class_code, '') <> ''
			group by customer_class_code, org_id, created_by, updated_by, tenant_id
			) x
			WHERE NOT EXISTS(SELECT 1 FROM mst_customer_class WHERE mst_customer_class.customer_class_code=x.customer_class_code and mst_customer_class.tenant_id=x.tenant_id)
			--and x.process_id = @ProcessId 
			and x.org_id = @OrganizationID


			---------------------------------------------Update Insert Mst Outlet Type------------------------------------
			UPDATE mst_outlet_type set mst_outlet_type.outlet_type_code = x.outlet_type
			FROM 
			(
			select outlet_type, tenant_id from 
			#tempCus --where process_id = @ProcessId
			group by outlet_type, tenant_id
			) x
			where mst_outlet_type.outlet_type_code = x.outlet_type and mst_outlet_type.tenant_id = x.tenant_id
			--and di_Customer_import.process_id = @ProcessId 
			and x.tenant_id =  @TenantId

			INSERT into mst_outlet_type (outlet_type_id, outlet_type_code, outlet_type_desc, status, created_date, created_by, updated_date, updated_by, tenant_id, org_id)
			SELECT NEWID(), x.outlet_type, x.outlet_type, 'Active', GETDATE(), x.created_by, GETDATE(), x.updated_by, x.tenant_id, x.tenant_id 
			FROM 
			(
			select outlet_type, created_by, updated_by, tenant_id from 
			#tempCus where IsNull(outlet_type,'') <> ''
			group by outlet_type, created_by, updated_by, tenant_id
			) x
			WHERE NOT EXISTS(SELECT 1 FROM mst_outlet_type WHERE mst_outlet_type.outlet_type_code=x.outlet_type and mst_outlet_type.tenant_id=x.tenant_id)
			--and x.process_id = @ProcessId 
			and x.tenant_id =  @TenantId
			
			-------------------------------------------------------------------------------------------
			SELECT c.customer_id AS 'customer_id',c.customer_code AS 'customer_code'Into #tempImportDelete from #tempCus di
			inner join customer c on  c.customer_code=di.customer_code
				--where di.org_id = @OrganizationID and di.process_id = @ProcessId

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

			IF(SELECT auto_populate_customer_code from org where org_id = @OrganizationID) = 'No'
			BEGIN
				--Delete customer where exists (
				--	SELECT 1 FROM #tempImportDelete temp where customer.customer_code = temp.customer_code and org_id = @OrganizationID)


				update cst  set
				cst.customer_name=IsNull(a.customer_name, ''),-- AS CustomerName,
				cst.customer_Short_name=IsNull(a.customer_short_name, ''),-- AS CustomerShortName,
				cst.state_id=IsNull(s.state_id, ''),--As StateId,
				cst.chain_id=IsNull(c.chain_id, ''),-- AS ChainId,
				cst.channel_id=IsNull(ch.channel_id, ''),-- AS ChannelId,
				cst.cluster_id=IsNull(cl.cluster_id, ''),--AS ClusterId,0,
				cst.status=IsNull(a.status, ''),--As Status,
				cst.updated_date=IsNull(a.updated_date, ''),--AS UpdatedDate,
				cst.updated_by=IsNull(a.updated_by, ''),--AS UpdatedBy,
				cst.country_id=IsNull(cy.country_id, ''),--AS COuntryId,
				cst.area=IsNull(ma.area_description, ''),--AS AreaDescription,
				cst.address=IsNull(a.address1, ''),--AS Address1,
				cst.contact_no=IsNull(a.tel_no, ''),--AS ContactNo,
				cst.pic=IsNull(a.pic, ''),--AS Pic,
				cst.latitude=IsNull(a.latitude, ''),--AS Latitude,
				cst.longitude=IsNull(a.longitude, ''),--AS Longitude,
				cst.branch_id=IsNull(b.branch_id, ''),--AS BranchId,
				cst.store_number=IsNull(a.store_number, ''),--AS StoreNumber,
				cst.special_instruction=IsNull(a.special_instruction, ''),--AS SpecialInstruction,
				cst.person_in_charge=IsNull(m.member_cd, ''),-- AS PersonInCharge,
				cst.customer_on_hold=IsNull(a.customer_on_hold, '') ,--AS CustomerOnHold,
				cst.terms_id=IsNull(ct.terms_id, '') ,--AS TermId,
				cst.price_id=IsNull(pc.price_id, ''),-- AS PriceId,
				cst.invoice_discount_id=IsNull(mid.invoice_discount_id, ''),-- AS InvoiceDiscountId,
				cst.area_id=IsNull(ma.area_id, '') ,--AS AreaId,
				cst.salesperson_id=IsNull(m.member_id, ''),--AS SalespersonId
				cst.distributor_code=IsNull(a.distributor_code, ''),--AS DistributorCode
				cst.distributor_name=IsNull(a.distributor_name, ''),--AS DistributorName
				cst.outlet_type_id=IsNull(ot.outlet_type_id, ''),--AS OutletTypeId
				cst.buying_group_id_one=IsNull(buying_group_one.buying_group_id_one, @defaultBuyingGroup1Id),--AS Buying Group One
				cst.buying_group_id_two=IsNull(buying_group_two.buying_group_id_two, ''),--AS Buying Group Two
				cst.buying_group_id_three=IsNull(buying_group_three.buying_group_id_three, ''),--AS Buying Group Three
				cst.mt_gt = IsNull(a.mtgt, ''), -- Mt/Gt
				cst.customer_type = IsNull(a.customer_type, '') -- Mt/Gt
				FROM customer cst
				inner join #tempCus a  on cst.customer_Code=a.customer_code
				inner join state s on a.state_code=s.state_code and s.org_id = a.tenant_id
				inner join chain c on a.chain_code=c.chain_code and c.org_id= a.org_id
				inner join channel ch on a.channel_code=ch.channel_code and ch.tenant_id = a.tenant_id
				inner join cluster cl on a.cluster_code=cl.cluster_code and cl.channel_id = ch.channel_id and cl.tenant_id = a.tenant_id
				inner join country cy on a.country_code=cy.country_code and cy.org_id= a.tenant_id
				inner join mst_branch b on a.branch_code=b.branch_code and b.org_id= a.org_id 
				inner join mst_area ma on a.area_code=ma.area_code and ma.org_id= a.org_id and b.branch_id = ma.branch_id
				inner join mst_terms_code ct on a.terms_code=ct.terms_code and ct.tenant_id= a.tenant_id
				inner join mst_price_code pc on a.price_code=pc.price_code and pc.tenant_id= a.tenant_id and pc.org_id = a.org_id
				inner join mst_invoice_discount mid on a.invoice_discount_code= mid.invoice_discount_code and mid.tenant_id= a.tenant_id
				inner join member m on a.person_in_charge =m.member_cd and m.org_id=a.org_id
				left join mst_outlet_type ot on ot.outlet_type_code = a.outlet_type and ot.tenant_id = a.tenant_id
				left join mst_buying_group_one buying_group_one on buying_group_one.buying_group_one_code = a.buying_group_one_code and buying_group_one.tenant_id = a.tenant_id
				left join mst_buying_group_two buying_group_two on buying_group_two.buying_group_two_code = a.buying_group_two_code and buying_group_two.tenant_id = a.tenant_id
				left join mst_buying_group_three buying_group_three on buying_group_three.buying_group_three_code = a.buying_group_three_code and buying_group_three.tenant_id = a.tenant_id
				--inner join customer_to_user_relation cu on m.member_id= cu.user_id and cu.org_id=a.org_id
				--inner join mst_customer_class cc on a.id 
				WHERE  EXISTS(SELECT 1 FROM customer st WHERE a.customer_code = st.customer_code)
				AND isNull(a.error_message, '') = ''
				AND a.process_id = @ProcessId 
				AND a.org_id = @OrganizationID

				
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

				--DELETE mst_customer_contact_address where exists (
				--	SELECT 1 FROM #tempImportDelete temp where mst_customer_contact_address.customer_id = temp.customer_id and org_id = @OrganizationID)


				update ww set 
				ww.address1=IsNull(a.address1, ''),-- AS Address1,
				ww.address2=IsNull(a.address2, ''),-- AS Address2,
				ww.address3=IsNull(a.address3, ''),-- AS Address3,
				ww.address4=IsNull(a.address4, ''),-- AS Address4,
				ww.address5=IsNull(a.address5, ''),-- AS Address5,
				ww.state_id=IsNull(s.state_id, ''),-- AS StateId,
				ww.postcode=IsNull(a.postcode, ''),-- AS PostCode,
				ww.country_id=IsNull(cy.country_id, ''),-- AS CountryId,
				ww.status=IsNull(a.status, ''),-- AS Status,
				ww.tel_no=IsNull(a.tel_no, ''),-- AS TelNo,
				ww.additional_tel_no=IsNull(a.additional_tel_no, ''),-- AS AdditionalTelNo,
				ww.updated_Date=IsNull(a.updated_date, ''),-- AS UpdatedDate,
				ww.updated_by=IsNull(a.updated_by, ''),-- AS UpdatedBt,
				ww.latitude=IsNull(a.latitude, ''),-- AS Latitude,
				ww.longitude=IsNull(a.longitude, ''),-- AS Longitude,
				ww.radius=IsNull(a.radius, ''),-- AS Radius,
				ww.region_id= IsNull(r.region_id, '')-- AS region_id
				FROM mst_customer_contact_address ww
				inner join customer wer on ww.customer_id=wer.customer_id
				inner join #tempCus a on a.customer_code = wer.customer_code
				inner join state s on a.state_code=s.state_code and s.org_id = a.org_id
				inner join country cy on a.country_code=cy.country_code and cy.tenant_id= a.tenant_id
				inner join mst_region r on r.region_code = a.region_code and r.org_id = a.org_id
				WHERE  EXISTS(SELECT 1 FROM mst_customer_contact_address st WHERE wer.customer_id = st.customer_id and st.address_type='Contact')
				AND isNull(a.error_message, '') = '' and ww.address_type = 'Contact' 
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID
				
				update ww set 
				ww.address1=Case When IsNull(a.billing_address1, '') = '' Then a.address1 ELSE a.billing_address1 END,-- AS Address1,
				ww.address2=Case When IsNull(a.billing_address1, '') = '' Then a.address2 ELSE a.billing_address2 END,-- AS Address2,
				ww.address3=Case When IsNull(a.billing_address1, '') = '' Then a.address3 ELSE a.billing_address3 END,-- AS Address3,
				ww.address4=Case When IsNull(a.billing_address1, '') = '' Then a.address4 ELSE a.billing_address4 END,-- AS Address4,
				ww.address5=Case When IsNull(a.billing_address1, '') = '' Then a.address5 ELSE a.billing_address5 END,-- AS Address5,
				ww.state_id=IsNull(s.state_id, ''),-- AS StateId,
				ww.postcode=IsNull(a.billing_postcode, ''),-- AS PostCode,
				ww.country_id=IsNull(cy.country_id, ''),-- AS CountryId,
				ww.status=IsNull(a.status, ''),-- AS Status,
				ww.tel_no=IsNull(a.tel_no, ''),-- AS TelNo,
				ww.additional_tel_no=IsNull(a.additional_tel_no, ''),-- AS AdditionalTelNo,
				ww.updated_Date=IsNull(a.updated_date, ''),-- AS UpdatedDate,
				ww.updated_by=IsNull(a.updated_by, ''),-- AS UpdatedBt,
				ww.latitude=IsNull(a.billing_latitude, ''),-- AS Latitude,
				ww.longitude=IsNull(a.billing_longitude, ''),-- AS Longitude,
				ww.radius=IsNull(a.billing_radius, ''),-- AS Radius,
				ww.region_id= IsNull(r.region_id, '')-- AS region_id
				FROM mst_customer_contact_address ww
				inner join customer wer on ww.customer_id=wer.customer_id
				inner join #tempCus a on a.customer_code = wer.customer_code
				inner join state s on a.billing_state_code=s.state_code and s.org_id = a.org_id
				inner join country cy on a.billing_country_code=cy.country_code and cy.tenant_id= a.tenant_id
				inner join mst_region r on r.region_code = a.billing_region_code and r.org_id = a.org_id
				WHERE  EXISTS(SELECT 1 FROM mst_customer_contact_address st WHERE wer.customer_id = st.customer_id and st.address_type='Billing')
				AND isNull(a.error_message, '') = '' and ww.address_type = 'Billing' 
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID
				

				update ww set 
				ww.address1=Case When IsNull(a.shipping_address1, '') = '' Then a.address1 ELSE a.shipping_address1 END,-- AS Address1,
				ww.address2=Case When IsNull(a.shipping_address1, '') = '' Then a.address2 ELSE a.shipping_address2 END,-- AS Address2,
				ww.address3=Case When IsNull(a.shipping_address1, '') = '' Then a.address3 ELSE a.shipping_address3 END,-- AS Address3,
				ww.address4=Case When IsNull(a.shipping_address1, '') = '' Then a.address4 ELSE a.shipping_address4 END,-- AS Address4,
				ww.address5=Case When IsNull(a.shipping_address1, '') = '' Then a.address5 ELSE a.shipping_address5 END,-- AS Address5,
				ww.state_id=IsNull(s.state_id, ''),-- AS StateId,
				ww.postcode=IsNull(a.shipping_postcode, ''),-- AS PostCode,
				ww.country_id=IsNull(cy.country_id, ''),-- AS CountryId,
				ww.status=IsNull(a.status, ''),-- AS Status,
				ww.tel_no=IsNull(a.tel_no, ''),-- AS TelNo,
				ww.additional_tel_no=IsNull(a.additional_tel_no, ''),-- AS AdditionalTelNo,
				ww.updated_Date=IsNull(a.updated_date, ''),-- AS UpdatedDate,
				ww.updated_by=IsNull(a.updated_by, ''),-- AS UpdatedBt,
				ww.latitude=IsNull(a.shipping_latitude, ''),-- AS Latitude,
				ww.longitude=IsNull(a.shipping_longitude, ''),-- AS Longitude,
				ww.radius=IsNull(a.shipping_radius, ''),-- AS Radius,
				ww.region_id= IsNull(r.region_id, '')-- AS region_id
				FROM mst_customer_contact_address ww
				inner join customer wer on ww.customer_id=wer.customer_id
				inner join #tempCus a on a.customer_code = wer.customer_code
				inner join state s on a.shipping_state_code=s.state_code and s.org_id = a.org_id
				inner join country cy on a.shipping_country_code=cy.country_code and cy.tenant_id= a.tenant_id
				inner join mst_region r on r.region_code = a.shipping_region_code and r.org_id = a.org_id
				WHERE  EXISTS(SELECT 1 FROM mst_customer_contact_address st WHERE wer.customer_id = st.customer_id and st.address_type='Shipping')
				AND isNull(a.error_message, '') = '' and ww.address_type = 'Shipping' 
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID
				
				
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
				--DELETE mst_customer_branch where exists (
				--	SELECT 1 FROM #tempImportDelete temp where mst_customer_branch.customer_id = temp.customer_id and org_id = @OrganizationID)

				update vv set 
				vv.branch_id=IsNull(b.branch_id, ''), --AS BranchId,
				vv.status=IsNull(a.status, ''),-- AS Status,
				vv.updated_Date=IsNull(a.updated_date, ''),-- AS UpdatedDate,
				vv.updated_by=IsNull(a.updated_by, '')-- AS UpdatedBy
				from mst_customer_branch vv
				inner join customer xc  on vv.customer_id=xc.customer_id
				inner join  #tempCus a   on a.customer_code=xc.customer_code
				inner join mst_branch b on a.branch_code=b.branch_code and b.org_id = a.org_id
				WHERE  EXISTS(SELECT 1 FROM mst_customer_branch st WHERE xc.customer_id = st.customer_id)
				AND isNull(a.error_message, '') = '' 
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID

				
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

				--DELETE customer_to_user_relation where exists (
				--	SELECT 1 FROM #tempImportDelete temp where customer_to_user_relation.customer_id = temp.customer_id and org_id = @OrganizationID)
				
				--DROP table #tempImportDelete

				update tur set
				tur.user_id=IsNull(m.member_id, ''),-- AS user_id,
				tur.updated_date=IsNull(a.updated_date, ''),-- AS UpdatedDate,
				tur.updated_by=IsNull(a.updated_by, '')-- AS UpdatedBy,
				from customer_to_user_relation tur 
				inner join customer dd on tur.customer_id=dd.customer_id
				inner join #tempCus a  on a.customer_code=dd.customer_code
				inner join member m on m.member_cd=a.person_in_charge and m.org_id = a.org_id
				WHERE  EXISTS(SELECT 1 FROM customer_to_user_relation st WHERE dd.customer_id = st.customer_id)
				AND isNull(a.error_message, '') = '' 
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID
				
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

				INSERT into customer
				( customer_id, org_id,customer_code,customer_name,customer_short_name,customer_business_type,state_id,chain_id,channel_id,cluster_id,visit_frequency,status,created_date,created_by,updated_date,updated_by,country_id,area,distributor,geolocation,photo,address,contact_no,pic,latitude,longitude,file_name,file_directory,branch_id,current_balance,date_last_sales,date_last_pay,master_account,store_number,line_discount_code,special_instruction,outstanding_order_value,number_outstanding_order,person_in_charge,user_field,customer_on_hold,high_inv_days,credit_limit,terms_id,price_id,customer_class_id,invoice_discount_id,area_id,buying_group_id_one,buying_group_id_two,buying_group_id_three,salesperson_id,tenant_id,ams,ams_id, distributor_code, distributor_name, outlet_type_id, mt_gt, customer_type)
				SELECT IsNull(a.customer_id, '') AS customer_id,
				IsNull(a.org_id, '') AS OrgId,
				IsNull(a.customer_code, '') AS CustomerCode,
				IsNull(a.customer_name, '') AS CustomerName,
				IsNull(a.customer_short_name, '') AS CustomerShortName,
				IsNull('', '')AS CustomerBusinessType,
				IsNull(s.state_id, '')As StateId,
				IsNull(c.chain_id, '') AS ChainId,
				IsNull(ch.channel_id, '') AS ChannelId,
				IsNull(cl.cluster_id, '')AS ClusterId,0,
				IsNull(a.status, '')As Status,
				IsNull(a.created_date, '')AS CreatedDate,
				IsNull(a.created_by, '')AS CreatedBy,
				IsNull(a.updated_date, '')AS UpdatedDate,
				IsNull(a.updated_by, '')AS UpdatedBy,
				IsNull(cy.country_id, '')AS COuntryId,
				IsNull(ma.area_description, '')AS AreaDescription,
				IsNull('', '')AS Distributor,
				IsNull('', '')AS Geolocation,
				IsNull('', '')AS Photo,
				IsNull(a.address1, '')AS Address1,
				IsNull(a.tel_no, '')AS ContactNo,
				IsNull(a.pic, '')AS Pic
				,IsNull(a.latitude, '')AS Latitude,
				IsNull(a.longitude, '')AS Longitude,
				IsNull('', '')AS FileName,
				IsNull('', '')AS FileDirectory,
				IsNull(b.branch_id, '')AS BranchId,
				0 AS CurrentBalance,
				IsNull('', '') AS DateLastSales,
				IsNull('', '') AS DateLastPay,
				IsNull('', '')AS MasterAccount,
				IsNull(a.store_number, '')AS StoreNumber,
				IsNull('', '')AS LineDiscountCode,
				IsNull(a.special_instruction, '')AS SpecialInstruction,
				0 AS OutstandingOrderValue,
				0 AS NumberOutStandingOrder,
				IsNull(m.member_cd, '') AS PersonInCharge,
				IsNull('', '') AS UserField,
				IsNull(a.customer_on_hold, '') AS CustomerOnHold,
				IsNull('', '') AS HighInvDays,
				0 AS CreditLimit,
				IsNull(ct.terms_id, '') AS TermId,
				IsNull(pc.price_id, '') AS PriceId,
				IsNull('', '') AS CustomerClassId,
				IsNull(mid.invoice_discount_id, '') AS InvoiceDiscountId,
				IsNull(ma.area_id, '') AS AreaId,
				IsNull(buying_group_one.buying_group_id_one, @defaultBuyingGroup1Id) AS BuyingGroupIdOne,
				IsNull(buying_group_two.buying_group_id_two, '') AS BuyingGroupIdTwo,
				IsNull(buying_group_three.buying_group_id_three, '') AS BuyingGroupIdThree,
				IsNull(m.member_id, '')AS SalespersonId,
				IsNull(a.tenant_id, '') AS TenantId,
				0 AS Ams,
				IsNull('', '') As AmsId,
				IsNull(a.distributor_code, '') AS DistributorCode,
				IsNull(a.distributor_name, '') AS DistributorName,
				IsNull(ot.outlet_type_id, '') AS OutletTypeId,
				IsNull(a.mtgt, '') AS MtGt,
				IsNull(a.customer_type, '') AS CustomerType
				FROM #tempCus a 
				inner join org o on o.org_id = a.org_id
				inner join state s on a.state_code=s.state_code and s.org_id = a.tenant_id
				inner join chain c on a.chain_code=c.chain_code and c.org_id= a.org_id
				inner join channel ch on a.channel_code=ch.channel_code and ch.org_id= a.org_id and ch.tenant_id = a.tenant_id
				inner join cluster cl on a.cluster_code=cl.cluster_code and cl.channel_id = ch.channel_id and cl.tenant_id = a.tenant_id
				inner join country cy on a.country_code=cy.country_code and cy.tenant_id= a.tenant_id
				inner join mst_branch b on a.branch_code=b.branch_code and b.org_id= a.org_id 
				inner join mst_area ma on a.area_code=ma.area_code and ma.org_id= a.org_id and b.branch_id = ma.branch_id
				inner join mst_terms_code ct on a.terms_code=ct.terms_code and ct.tenant_id= a.tenant_id
				inner join mst_price_code pc on a.price_code=pc.price_code and pc.tenant_id= a.tenant_id and pc.org_id = a.org_id
				inner join mst_invoice_discount mid on a.invoice_discount_code= mid.invoice_discount_code and mid.tenant_id= a.tenant_id
				inner join member m on a.person_in_charge =m.member_cd and m.org_id=a.org_id
				left join mst_outlet_type ot on ot.outlet_type_code = a.outlet_type and ot.tenant_id = a.tenant_id
				left join mst_buying_group_one buying_group_one on buying_group_one.buying_group_one_code = a.buying_group_one_code and buying_group_one.tenant_id = a.tenant_id
				left join mst_buying_group_two buying_group_two on buying_group_two.buying_group_two_code = a.buying_group_two_code and buying_group_two.tenant_id = a.tenant_id
				left join mst_buying_group_three buying_group_three on buying_group_three.buying_group_three_code = a.buying_group_three_code and buying_group_three.tenant_id = a.tenant_id
				--inner join customer_to_user_relation cu on m.member_id= cu.user_id and cu.org_id=a.org_id
				--inner join mst_customer_class cc on a.id 
				WHERE NOT EXISTS(SELECT 1 FROM customer st WHERE a.customer_code = st.customer_code)
				AND isNull(a.error_message, '') = ''
				AND a.process_id = @ProcessId 
				AND a.org_id = @OrganizationID
				
				INSERT into  mst_customer_contact_address  ( customer_address_id,customer_id,address1,address2,address3,address4,address5,state_id,postcode,country_id,address_type,status,tel_no,additional_tel_no,created_date,created_by,updated_date,updated_by,org_id,tenant_id,latitude,longitude,radius,contact,sold_to_addr3_loc, region_id)
				SELECT IsNull(a.customer_address_id, '') AS CustomerAddressId,
				IsNull(a.customer_id, '') AS CustomerId,
				IsNull(a.address1, '') AS Address1,
				IsNull(a.address2, '') AS Address2,
				IsNull(a.address3, '') AS Address3,
				IsNull(a.address4, '') AS Address4,
				IsNull(a.address5, '') AS Address5,
				IsNull(s.state_id, '') AS StateId,
				IsNull(a.postcode, '') AS PostCode,
				IsNull(cy.country_id, '') AS CountryId,
				'Contact' AS AddressType,
				IsNull(a.status, '') AS Status,
				IsNull(a.tel_no, '') AS TelNo,
				IsNull(a.additional_tel_no, '') AS AdditionalTelNo,
				IsNull(a.created_date, '') AS CreatedDate,
				IsNull(a.created_by, '') AS CreatedBy,
				IsNull(a.updated_date, '') AS UpdatedDate,
				IsNull(a.updated_by, '') AS UpdatedBt,
				IsNull(a.org_id, '') AS OrgId,
				IsNull(a.tenant_id, '') AS TenantId,
				IsNull(a.latitude, '') AS Latitude,
				IsNull(a.longitude, '') AS Longitude,
				IsNull(a.radius, '') AS Radius,
				IsNull('', '') AS Contact,
				IsNull('', '') AS SoldToAddr3Loc,
				IsNull(r.region_id, '') AS region_id
				FROM #tempCus a 
				inner join state s on a.state_code=s.state_code and s.org_id = a.org_id
				inner join country cy on a.country_code=cy.country_code and cy.tenant_id= a.tenant_id
				inner join mst_region r on r.region_code = a.region_code and r.org_id = a.org_id
				inner join customer werr on a.customer_code = werr.customer_code
				WHERE NOT EXISTS(SELECT 1 FROM mst_customer_contact_address st WHERE werr.customer_id = st.customer_id and st.address_type='Contact')
				AND isNull(a.error_message, '') = '' 
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID
				
				INSERT into  mst_customer_contact_address  ( customer_address_id,customer_id,address1,address2,address3,address4,address5,state_id,postcode,country_id,address_type,status,tel_no,additional_tel_no,created_date,created_by,updated_date,updated_by,org_id,tenant_id,latitude,longitude,radius,contact,sold_to_addr3_loc, region_id)
				SELECT newid() AS CustomerAddressId,
				IsNull(wert.customer_id, '') AS CustomerId,
				Case When IsNull(a.billing_address1, '') = '' Then a.address1 ELSE a.billing_address1 END AS Address1,
				Case When IsNull(a.billing_address1, '') = '' Then a.address2 ELSE a.billing_address2 END AS Address2,
				Case When IsNull(a.billing_address1, '') = '' Then a.address3 ELSE a.billing_address3 END AS Address3,
				Case When IsNull(a.billing_address1, '') = '' Then a.address4 ELSE a.billing_address4 END AS Address4,
				Case When IsNull(a.billing_address1, '') = '' Then a.address5 ELSE a.billing_address5 END AS Address5,
				IsNull(s.state_id, '') AS StateId,
				IsNull(a.billing_postcode, '') AS PostCode,
				IsNull(cy.country_id, '') AS CountryId,
				'Billing' AS AddressType,
				IsNull(a.status, '') AS Status,
				IsNull(a.tel_no, '') AS TelNo,
				IsNull(a.additional_tel_no, '') AS AdditionalTelNo,
				IsNull(a.created_date, '') AS CreatedDate,
				IsNull(a.created_by, '') AS CreatedBy,
				IsNull(a.updated_date, '') AS UpdatedDate,
				IsNull(a.updated_by, '') AS UpdatedBt,
				IsNull(a.org_id, '') AS OrgId,
				IsNull(a.tenant_id, '') AS TenantId,
				IsNull(a.billing_latitude, '') AS Latitude,
				IsNull(a.billing_longitude, '') AS Longitude,
				IsNull(a.billing_radius, '') AS Radius,
				IsNull('', '') AS Contact,
				IsNull('', '') AS SoldToAddr3Loc,
				IsNull(r.region_id, '') AS region_id
				FROM #tempCus a 
				inner join state s on a.billing_state_code=s.state_code and s.org_id = a.org_id
				inner join country cy on a.billing_country_code=cy.country_code and cy.tenant_id= a.tenant_id
				inner join mst_region r on r.region_code = a.billing_region_code and r.org_id = a.org_id
				inner join customer wert on a.customer_code=wert.customer_code
				WHERE NOT EXISTS(SELECT 1 FROM mst_customer_contact_address st WHERE wert.customer_id = st.customer_id and st.address_type='Billing')
				AND isNull(a.error_message, '') = '' 
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID
			
				INSERT into  mst_customer_contact_address  ( customer_address_id,customer_id,address1,address2,address3,address4,address5,state_id,postcode,country_id,address_type,status,tel_no,additional_tel_no,created_date,created_by,updated_date,updated_by,org_id,tenant_id,latitude,longitude,radius,contact,sold_to_addr3_loc, region_id)
				SELECT newid() AS CustomerAddressId,
				IsNull(werw.customer_id, '') AS CustomerId,
				Case When IsNull(a.shipping_address1, '') = '' Then a.address1 ELSE a.shipping_address1 END AS Address1,
				Case When IsNull(a.shipping_address1, '') = '' Then a.address2 ELSE a.shipping_address2 END AS Address2,
				Case When IsNull(a.shipping_address1, '') = '' Then a.address3 ELSE a.shipping_address3 END AS Address3,
				Case When IsNull(a.shipping_address1, '') = '' Then a.address4 ELSE a.shipping_address4 END AS Address4,
				Case When IsNull(a.shipping_address1, '') = '' Then a.address5 ELSE a.shipping_address5 END AS Address5,
				IsNull(s.state_id, '') AS StateId,
				IsNull(a.postcode, '') AS PostCode,
				IsNull(cy.country_id, '') AS CountryId,
				'Shipping' AS AddressType,
				IsNull(a.status, '') AS Status,
				IsNull(a.tel_no, '') AS TelNo,
				IsNull(a.additional_tel_no, '') AS AdditionalTelNo,
				IsNull(a.created_date, '') AS CreatedDate,
				IsNull(a.created_by, '') AS CreatedBy,
				IsNull(a.updated_date, '') AS UpdatedDate,
				IsNull(a.updated_by, '') AS UpdatedBt,
				IsNull(a.org_id, '') AS OrgId,
				IsNull(a.tenant_id, '') AS TenantId,
				IsNull(a.billing_latitude, '') AS Latitude,
				IsNull(a.billing_longitude, '') AS Longitude,
				IsNull(a.billing_radius, '') AS Radius,
				IsNull('', '') AS Contact,
				IsNull('', '') AS SoldToAddr3Loc,
				IsNull(r.region_id, '') AS region_id
				FROM #tempCus a 
				inner join state s on a.billing_state_code=s.state_code and s.org_id = a.org_id
				inner join country cy on a.billing_country_code=cy.country_code and cy.tenant_id= a.tenant_id
				inner join mst_region r on r.region_code = a.shipping_region_code and r.org_id = a.org_id
				inner join customer werw on a.customer_code=werw.customer_code
				WHERE NOT EXISTS(SELECT 1 FROM mst_customer_contact_address st WHERE werw.customer_id = st.customer_id and st.address_type='Shipping')
				AND isNull(a.error_message, '') = '' 
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID
				
				
				INSERT into  mst_customer_branch(customer_branch_id,customer_id,branch_id,status,created_date,created_by,updated_date,updated_by,org_id,tenant_id)
				SELECT IsNull(a.customer_branch_id, '') AS CustomerBranchId,
				IsNull(a.customer_id, '') AS CustomerId,
				IsNull(b.branch_id, '') AS BranchId,
				IsNull(a.status, '') AS Status,
				IsNull(a.created_date, '') AS CreatedDate,
				IsNull(a.created_by, '') AS CreatedBy,
				IsNull(a.updated_date, '') AS UpdatedDate,
				IsNull(a.updated_by, '') AS UpdatedBy,
				IsNull(a.org_id, '') AS OrgId,
				IsNull(a.tenant_id, '') AS TenantId
				FROM #tempCus a 
				inner join mst_branch b on a.branch_code=b.branch_code and b.org_id = a.org_id
				inner join customer xc on a.customer_code=xc.customer_code
				WHERE NOT EXISTS(SELECT 1 FROM mst_customer_branch st WHERE xc.customer_id = st.customer_id)
				AND isNull(a.error_message, '') = '' 
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID
				
				INSERT into  customer_to_user_relation(customer_to_user_relation_id,customer_id,user_id,created_date,created_by,updated_date,updated_by,org_id,tenant_id,start_date,end_date)
				SELECT IsNull(a.customer_to_user_relation_id, '') AS CustomerToUserRelationId,
				IsNull(a.customer_id, '') AS CustomerId,
				IsNull(m.member_id, '') AS user_id,
				IsNull(a.created_date, '') AS CreatedDate,
				IsNull(a.created_by, '') AS CreatedBy,
				IsNull(a.updated_date, '') AS UpdatedDate,
				IsNull(a.updated_by, '') AS UpdatedBy,
				IsNull(a.org_id, '') AS OrgId,
				IsNull(a.tenant_id, '') AS TenantId,
				'2023-01-01 00:00:00.000' as start_date,
				'2099-12-31 00:00:00.000' as end_date
				FROM #tempCus a
				inner join member m on m.member_cd=a.person_in_charge and m.org_id = a.org_id
				inner join customer aw on a.customer_code=aw.customer_code
				WHERE NOT EXISTS(SELECT 1 FROM customer_to_user_relation st WHERE aw.customer_id = st.customer_id)
				AND isNull(a.error_message, '') = '' 
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID

				
			END

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
			IF(SELECT auto_populate_customer_code from org where org_id = @OrganizationID) = 'Yes'
			BEGIN
				--Delete customer where exists (
				--	SELECT 1 FROM #tempImportDelete temp where customer.customer_code = temp.customer_code and org_id = @OrganizationID)


				update cst  set
				cst.customer_name=IsNull(a.customer_name, ''),-- AS CustomerName,
				cst.customer_Short_name=IsNull(a.customer_short_name, ''),-- AS CustomerShortName,
				cst.state_id=IsNull(s.state_id, ''),--As StateId,
				cst.chain_id=IsNull(c.chain_id, ''),-- AS ChainId,
				cst.channel_id=IsNull(ch.channel_id, ''),-- AS ChannelId,
				cst.cluster_id=IsNull(cl.cluster_id, ''),--AS ClusterId,0,
				cst.status=IsNull(a.status, ''),--As Status,
				cst.updated_date=IsNull(a.updated_date, ''),--AS UpdatedDate,
				cst.updated_by=IsNull(a.updated_by, ''),--AS UpdatedBy,
				cst.country_id=IsNull(cy.country_id, ''),--AS COuntryId,
				cst.area=IsNull(ma.area_description, ''),--AS AreaDescription,
				cst.address=IsNull(a.address1, ''),--AS Address1,
				cst.contact_no=IsNull(a.tel_no, ''),--AS ContactNo,
				cst.pic=IsNull(a.pic, ''),--AS Pic,
				cst.latitude=IsNull(a.latitude, ''),--AS Latitude,
				cst.longitude=IsNull(a.longitude, ''),--AS Longitude,
				cst.branch_id=IsNull(b.branch_id, ''),--AS BranchId,
				cst.store_number=IsNull(a.store_number, ''),--AS StoreNumber,
				cst.special_instruction=IsNull(a.special_instruction, ''),--AS SpecialInstruction,
				cst.person_in_charge=IsNull(m.member_cd, ''),-- AS PersonInCharge,
				cst.customer_on_hold=IsNull(a.customer_on_hold, '') ,--AS CustomerOnHold,
				cst.terms_id=IsNull(ct.terms_id, '') ,--AS TermId,
				cst.price_id=IsNull(pc.price_id, ''),-- AS PriceId,
				cst.invoice_discount_id=IsNull(mid.invoice_discount_id, ''),-- AS InvoiceDiscountId,
				cst.area_id=IsNull(ma.area_id, '') ,--AS AreaId,
				cst.salesperson_id=IsNull(m.member_id, ''),--AS SalespersonId
				cst.distributor_code=IsNull(a.distributor_code, ''),--AS DistributorCode
				cst.distributor_name=IsNull(a.distributor_name, ''),--AS DistributorName
				cst.outlet_type_id=IsNull(ot.outlet_type_id, ''),--AS OutletTypeId
				cst.buying_group_id_one=IsNull(buying_group_one.buying_group_id_one, @defaultBuyingGroup1Id),--AS Buying Group One
				cst.buying_group_id_two=IsNull(buying_group_two.buying_group_id_two, ''),--AS Buying Group Two
				cst.buying_group_id_three=IsNull(buying_group_three.buying_group_id_three, ''),--AS Buying Group Three
				cst.mt_gt = IsNull(a.mtgt, ''), -- Mt/Gt
				cst.customer_type = IsNull(a.customer_type, ''), -- Customer Type
				cst.customer_class_id = IsNull(mcc.customer_class_id, '') -- Customer Class
				FROM customer cst
				inner join #tempCus a  on cst.distributor_customer_code=a.customer_code
				inner join state s on a.state_code=s.state_code and s.org_id = a.org_id
				inner join chain c on a.chain_code=c.chain_code and c.tenant_id = a.tenant_id
				inner join channel ch on a.channel_code=ch.channel_code and ch.tenant_id = a.tenant_id
				inner join cluster cl on a.cluster_code=cl.cluster_code and cl.channel_id = ch.channel_id and cl.tenant_id = a.tenant_id
				inner join country cy on a.country_code=cy.country_code and cy.tenant_id= a.tenant_id
				inner join mst_branch b on a.branch_code=b.branch_code and b.org_id= a.org_id 
				inner join mst_area ma on a.area_code=ma.area_code and ma.org_id= a.org_id and b.branch_id = ma.branch_id
				inner join mst_terms_code ct on a.terms_code=ct.terms_code and ct.tenant_id= a.tenant_id
				inner join mst_price_code pc on a.price_code=pc.price_code and pc.tenant_id= a.tenant_id and pc.org_id = a.org_id
				inner join mst_invoice_discount mid on a.invoice_discount_code= mid.invoice_discount_code and mid.tenant_id= a.tenant_id
				inner join member m on a.person_in_charge =m.member_cd and m.org_id=a.org_id
				left join mst_outlet_type ot on ot.outlet_type_code = a.outlet_type and ot.tenant_id = a.tenant_id
				left join mst_buying_group_one buying_group_one on buying_group_one.buying_group_one_code = a.buying_group_one_code and buying_group_one.tenant_id = a.tenant_id
				left join mst_buying_group_two buying_group_two on buying_group_two.buying_group_two_code = a.buying_group_two_code and buying_group_two.tenant_id = a.tenant_id
				left join mst_buying_group_three buying_group_three on buying_group_three.buying_group_three_code = a.buying_group_three_code and buying_group_three.tenant_id = a.tenant_id
				left join mst_customer_class mcc ON mcc.customer_class_code = a.customer_class_code AND mcc.tenant_id = a.tenant_id
				--inner join customer_to_user_relation cu on m.member_id= cu.user_id and cu.org_id=a.org_id
				--inner join mst_customer_class cc on a.id 
				WHERE  EXISTS(SELECT 1 FROM customer st WHERE a.customer_code = st.distributor_customer_code)
				AND isNull(a.error_message, '') = ''
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID
				
				
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

				--DELETE mst_customer_contact_address where exists (
				--	SELECT 1 FROM #tempImportDelete temp where mst_customer_contact_address.customer_id = temp.customer_id and org_id = @OrganizationID)


				update ww set 
				ww.address1=IsNull(a.address1, ''),-- AS Address1,
				ww.address2=IsNull(a.address2, ''),-- AS Address2,
				ww.address3=IsNull(a.address3, ''),-- AS Address3,
				ww.address4=IsNull(a.address4, ''),-- AS Address4,
				ww.address5=IsNull(a.address5, ''),-- AS Address5,
				ww.state_id=IsNull(s.state_id, ''),-- AS StateId,
				ww.postcode=IsNull(a.postcode, ''),-- AS PostCode,
				ww.country_id=IsNull(cy.country_id, ''),-- AS CountryId,
				ww.status=IsNull(a.status, ''),-- AS Status,
				ww.tel_no=IsNull(a.tel_no, ''),-- AS TelNo,
				ww.additional_tel_no=IsNull(a.additional_tel_no, ''),-- AS AdditionalTelNo,
				ww.updated_Date=IsNull(a.updated_date, ''),-- AS UpdatedDate,
				ww.updated_by=IsNull(a.updated_by, ''),-- AS UpdatedBt,
				ww.latitude=IsNull(a.latitude, ''),-- AS Latitude,
				ww.longitude=IsNull(a.longitude, ''),-- AS Longitude,
				ww.radius=IsNull(a.radius, ''),-- AS Radius,
				ww.region_id= IsNull(r.region_id, '')-- AS region_id
				FROM mst_customer_contact_address ww
				inner join customer wer on ww.customer_id=wer.customer_id
				inner join #tempCus a on a.customer_code = wer.distributor_customer_code
				inner join state s on a.state_code=s.state_code and s.org_id = a.org_id
				inner join country cy on a.country_code=cy.country_code and cy.tenant_id= a.tenant_id
				inner join mst_region r on r.region_code = a.region_code and r.org_id = a.org_id
				WHERE  EXISTS(SELECT 1 FROM mst_customer_contact_address st WHERE wer.customer_id = st.customer_id and st.address_type='Contact')
				AND isNull(a.error_message, '') = '' and ww.address_type = 'Contact' 
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID
				
				update ww set 
				ww.address1=Case When IsNull(a.billing_address1, '') = '' Then a.address1 ELSE a.billing_address1 END,-- AS Address1,
				ww.address2=Case When IsNull(a.billing_address1, '') = '' Then a.address2 ELSE a.billing_address2 END,-- AS Address2,
				ww.address3=Case When IsNull(a.billing_address1, '') = '' Then a.address3 ELSE a.billing_address3 END,-- AS Address3,
				ww.address4=Case When IsNull(a.billing_address1, '') = '' Then a.address4 ELSE a.billing_address4 END,-- AS Address4,
				ww.address5=Case When IsNull(a.billing_address1, '') = '' Then a.address5 ELSE a.billing_address5 END,-- AS Address5,
				ww.state_id=IsNull(s.state_id, ''),-- AS StateId,
				ww.postcode=IsNull(a.billing_postcode, ''),-- AS PostCode,
				ww.country_id=IsNull(cy.country_id, ''),-- AS CountryId,
				ww.status=IsNull(a.status, ''),-- AS Status,
				ww.tel_no=IsNull(a.tel_no, ''),-- AS TelNo,
				ww.additional_tel_no=IsNull(a.additional_tel_no, ''),-- AS AdditionalTelNo,
				ww.updated_Date=IsNull(a.updated_date, ''),-- AS UpdatedDate,
				ww.updated_by=IsNull(a.updated_by, ''),-- AS UpdatedBt,
				ww.latitude=IsNull(a.billing_latitude, ''),-- AS Latitude,
				ww.longitude=IsNull(a.billing_longitude, ''),-- AS Longitude,
				ww.radius=IsNull(a.billing_radius, ''),-- AS Radius,
				ww.region_id= IsNull(r.region_id, '')-- AS region_id
				FROM mst_customer_contact_address ww
				inner join customer wer on ww.customer_id=wer.customer_id
				inner join #tempCus a on a.customer_code = wer.distributor_customer_code
				inner join state s on a.billing_state_code=s.state_code and s.org_id = a.org_id
				inner join country cy on a.billing_country_code=cy.country_code and cy.tenant_id= a.tenant_id
				inner join mst_region r on r.region_code = a.billing_region_code and r.org_id = a.org_id
				WHERE  EXISTS(SELECT 1 FROM mst_customer_contact_address st WHERE wer.customer_id = st.customer_id and st.address_type='Billing')
				AND isNull(a.error_message, '') = '' and ww.address_type = 'Billing' 
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID
				
				update ww set 
				ww.address1=Case When IsNull(a.shipping_address1, '') = '' Then a.address1 ELSE a.shipping_address1 END,-- AS Address1,
				ww.address2=Case When IsNull(a.shipping_address1, '') = '' Then a.address2 ELSE a.shipping_address2 END,-- AS Address2,
				ww.address3=Case When IsNull(a.shipping_address1, '') = '' Then a.address3 ELSE a.shipping_address3 END,-- AS Address3,
				ww.address4=Case When IsNull(a.shipping_address1, '') = '' Then a.address4 ELSE a.shipping_address4 END,-- AS Address4,
				ww.address5=Case When IsNull(a.shipping_address1, '') = '' Then a.address5 ELSE a.shipping_address5 END,-- AS Address5,
				ww.state_id=IsNull(s.state_id, ''),-- AS StateId,
				ww.postcode=IsNull(a.shipping_postcode, ''),-- AS PostCode,
				ww.country_id=IsNull(cy.country_id, ''),-- AS CountryId,
				ww.status=IsNull(a.status, ''),-- AS Status,
				ww.tel_no=IsNull(a.tel_no, ''),-- AS TelNo,
				ww.additional_tel_no=IsNull(a.additional_tel_no, ''),-- AS AdditionalTelNo,
				ww.updated_Date=IsNull(a.updated_date, ''),-- AS UpdatedDate,
				ww.updated_by=IsNull(a.updated_by, ''),-- AS UpdatedBt,
				ww.latitude=IsNull(a.shipping_latitude, ''),-- AS Latitude,
				ww.longitude=IsNull(a.shipping_longitude, ''),-- AS Longitude,
				ww.radius=IsNull(a.shipping_radius, ''),-- AS Radius,
				ww.region_id= IsNull(r.region_id, '')-- AS region_id
				FROM mst_customer_contact_address ww
				inner join customer wer on ww.customer_id=wer.customer_id
				inner join #tempCus a on a.customer_code = wer.distributor_customer_code
				inner join state s on a.shipping_state_code=s.state_code and s.org_id = a.org_id
				inner join country cy on a.shipping_country_code=cy.country_code and cy.tenant_id= a.tenant_id
				inner join mst_region r on r.region_code = a.shipping_region_code and r.org_id = a.org_id
				WHERE  EXISTS(SELECT 1 FROM mst_customer_contact_address st WHERE wer.customer_id = st.customer_id and st.address_type='Shipping')
				AND isNull(a.error_message, '') = '' and ww.address_type = 'Shipping' 
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID

---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
				--DELETE mst_customer_branch where exists (
				--	SELECT 1 FROM #tempImportDelete temp where mst_customer_branch.customer_id = temp.customer_id and org_id = @OrganizationID)

				update vv set 
				vv.branch_id=IsNull(b.branch_id, ''), --AS BranchId,
				vv.status=IsNull(a.status, ''),-- AS Status,
				vv.updated_Date=IsNull(a.updated_date, ''),-- AS UpdatedDate,
				vv.updated_by=IsNull(a.updated_by, '')-- AS UpdatedBy
				from mst_customer_branch vv
				inner join customer xc  on vv.customer_id=xc.customer_id
				inner join  #tempCus a   on a.customer_code=xc.distributor_customer_code
				inner join mst_branch b on a.branch_code=b.branch_code and b.org_id = a.org_id
				WHERE  EXISTS(SELECT 1 FROM mst_customer_branch st WHERE xc.customer_id = st.customer_id)
				AND isNull(a.error_message, '') = '' 
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID
				

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

				--DELETE customer_to_user_relation where exists (
				--	SELECT 1 FROM #tempImportDelete temp where customer_to_user_relation.customer_id = temp.customer_id and org_id = @OrganizationID)
				
				--DROP table #tempImportDelete

				update tur set
				tur.user_id=IsNull(m.member_id, ''),-- AS user_id,
				tur.updated_date=IsNull(a.updated_date, ''),-- AS UpdatedDate,
				tur.updated_by=IsNull(a.updated_by, '')-- AS UpdatedBy,
				from customer_to_user_relation tur 
				inner join customer dd on tur.customer_id=dd.customer_id
				inner join #tempCus a  on a.customer_code=dd.distributor_customer_code
				inner join member m on m.member_cd=a.person_in_charge and m.org_id = a.org_id
				WHERE  EXISTS(SELECT 1 FROM customer_to_user_relation st WHERE dd.customer_id = st.customer_id)
				AND isNull(a.error_message, '') = '' 
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID
				
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

				INSERT into customer
				( customer_id, org_id,customer_code, distributor_customer_code, customer_name,customer_short_name,customer_business_type,state_id,chain_id,channel_id,cluster_id,visit_frequency,status,created_date,created_by,updated_date,updated_by,country_id,area,distributor,geolocation,photo,address,contact_no,pic,latitude,longitude,file_name,file_directory,branch_id,current_balance,date_last_sales,date_last_pay,master_account,store_number,line_discount_code,special_instruction,outstanding_order_value,number_outstanding_order,person_in_charge,user_field,customer_on_hold,high_inv_days,credit_limit,terms_id,price_id,invoice_discount_id,area_id,buying_group_id_one,buying_group_id_two,buying_group_id_three,salesperson_id,tenant_id,ams,ams_id, distributor_code, distributor_name, outlet_type_id, mt_gt, customer_type, customer_class_id)
				SELECT NEWID() AS customer_id,
				IsNull(a.org_id, '') AS OrgId,
				--IsNull(CONCAT(o.prefix, FORMAT(o.running_no + ROW_NUMBER() OVER (ORDER BY a.customer_code), '000000')), '') AS CustomerCode,
				IsNull(Replace(dbo.fn_get_configured_code(@OrganizationID, @TenantId, 'Customer', 'Code', IsNull(a.mtgt,'MT/GT'), DEFAULT), '[RUN]',Format(dbo.fn_get_configured_code(@OrganizationID, @TenantId, 'Customer', 'Running Number', IsNull(a.mtgt,'MT/GT'), DEFAULT)+ ROW_NUMBER() OVER (PARTITION BY a.mtgt ORDER BY a.customer_code), '000000')), '') [CustomerCode],
				IsNull(a.customer_code, '') AS CustomerCode,
				IsNull(a.customer_name, '') AS CustomerName,
				IsNull(a.customer_short_name, '') AS CustomerShortName,
				IsNull('', '')AS CustomerBusinessType,
				IsNull(s.state_id, '')As StateId,
				IsNull(c.chain_id, '') AS ChainId,
				IsNull(ch.channel_id, '') AS ChannelId,
				IsNull(cl.cluster_id, '')AS ClusterId,0,
				IsNull(a.status, '')As Status,
				IsNull(a.created_date, '')AS CreatedDate,
				IsNull(a.created_by, '')AS CreatedBy,
				IsNull(a.updated_date, '')AS UpdatedDate,
				IsNull(a.updated_by, '')AS UpdatedBy,
				IsNull(cy.country_id, '')AS CountryId,
				IsNull(ma.area_description, '')AS AreaDescription,
				IsNull('', '')AS Distributor,
				IsNull('', '')AS Geolocation,
				IsNull('', '')AS Photo,
				IsNull(a.address1, '')AS Address1,
				IsNull(a.tel_no, '')AS ContactNo,
				IsNull(a.pic, '')AS Pic
				,IsNull(a.latitude, '')AS Latitude,
				IsNull(a.longitude, '')AS Longitude,
				IsNull('', '')AS FileName,
				IsNull('', '')AS FileDirectory,
				IsNull(b.branch_id, '')AS BranchId,
				0 AS CurrentBalance,
				IsNull('', '') AS DateLastSales,
				IsNull('', '') AS DateLastPay,
				IsNull('', '')AS MasterAccount,
				IsNull(a.store_number, '')AS StoreNumber,
				IsNull('', '')AS LineDiscountCode,
				IsNull(a.special_instruction, '')AS SpecialInstruction,
				0 AS OutstandingOrderValue,
				0 AS NumberOutStandingOrder,
				IsNull(m.member_cd, '') AS PersonInCharge,
				IsNull('', '') AS UserField,
				IsNull(a.customer_on_hold, '') AS CustomerOnHold,
				IsNull('', '') AS HighInvDays,
				0 AS CreditLimit,
				IsNull(ct.terms_id, '') AS TermId,
				IsNull(pc.price_id, '') AS PriceId,
				IsNull(mid.invoice_discount_id, '') AS InvoiceDiscountId,
				IsNull(ma.area_id, '') AS AreaId,
				IsNull(buying_group_one.buying_group_id_one, @defaultBuyingGroup1Id) AS BuyingGroupIdOne,
				IsNull(buying_group_two.buying_group_id_two, '') AS BuyingGroupIdTwo,
				IsNull(buying_group_three.buying_group_id_three, '') AS BuyingGroupIdThree,
				IsNull(m.member_id, '')AS SalespersonId,
				IsNull(a.tenant_id, '') AS TenantId,
				0 AS Ams,
				IsNull('', '') As AmsId,
				IsNull(a.distributor_code, '') AS DistributorCode,
				IsNull(a.distributor_name, '') AS DistributorName,
				IsNull(ot.outlet_type_id, '') AS OutletTypeId,
				IsNull(a.mtgt, '') AS MtGt,
				IsNull(a.customer_type, '') AS CustomerType,
				IsNull(mcc.customer_class_id, '') AS CustomerClass
				FROM #tempCus a 
				inner join org o on o.org_id = a.org_id
				inner join state s on a.state_code=s.state_code and s.org_id = a.org_id
				inner join chain c on a.chain_code=c.chain_code and c.tenant_id= a.tenant_id
				inner join channel ch on a.channel_code=ch.channel_code and ch.tenant_id = a.tenant_id
				inner join cluster cl on a.cluster_code=cl.cluster_code and cl.channel_id = ch.channel_id and cl.tenant_id = a.tenant_id
				inner join country cy on a.country_code=cy.country_code and cy.tenant_id= a.tenant_id
				inner join mst_branch b on a.branch_code=b.branch_code and b.org_id= a.org_id 
				inner join mst_area ma on a.area_code=ma.area_code and ma.org_id= a.org_id and b.branch_id = ma.branch_id
				inner join mst_terms_code ct on a.terms_code=ct.terms_code and ct.tenant_id= a.tenant_id
				inner join mst_price_code pc on a.price_code=pc.price_code and pc.tenant_id= a.tenant_id and pc.org_id = a.org_id
				inner join mst_invoice_discount mid on a.invoice_discount_code= mid.invoice_discount_code and mid.tenant_id= a.tenant_id
				inner join member m on a.person_in_charge =m.member_cd and m.org_id=a.org_id
				left join mst_outlet_type ot on ot.outlet_type_code = a.outlet_type and ot.tenant_id = a.tenant_id
				left join mst_buying_group_one buying_group_one on buying_group_one.buying_group_one_code = a.buying_group_one_code and buying_group_one.tenant_id = a.tenant_id
				left join mst_buying_group_two buying_group_two on buying_group_two.buying_group_two_code = a.buying_group_two_code and buying_group_two.tenant_id = a.tenant_id
				left join mst_buying_group_three buying_group_three on buying_group_three.buying_group_three_code = a.buying_group_three_code and buying_group_three.tenant_id = a.tenant_id
				left join mst_customer_class mcc ON mcc.customer_class_code = a.customer_class_code AND mcc.tenant_id = a.tenant_id
				--inner join customer_to_user_relation cu on m.member_id= cu.user_id and cu.org_id=a.org_id
				--inner join mst_customer_class cc on a.id 
				WHERE NOT EXISTS(SELECT 1 FROM customer st WHERE a.customer_code = st.distributor_customer_code and st.org_id = a.org_id)
				AND isNull(a.error_message, '') = ''
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID
				
				INSERT into  mst_customer_contact_address  ( customer_address_id,customer_id,address1,address2,address3,address4,address5,state_id,postcode,country_id,address_type,status,tel_no,additional_tel_no,created_date,created_by,updated_date,updated_by,org_id,tenant_id,latitude,longitude,radius,contact,sold_to_addr3_loc, region_id)
				SELECT NewId() AS CustomerAddressId,
				IsNull(werr.customer_id, '') AS CustomerId,
				IsNull(a.address1, '') AS Address1,
				IsNull(a.address2, '') AS Address2,
				IsNull(a.address3, '') AS Address3,
				IsNull(a.address4, '') AS Address4,
				IsNull(a.address5, '') AS Address5,
				IsNull(s.state_id, '') AS StateId,
				IsNull(a.postcode, '') AS PostCode,
				IsNull(cy.country_id, '') AS CountryId,
				'Contact' AS AddressType,
				IsNull(a.status, '') AS Status,
				IsNull(a.tel_no, '') AS TelNo,
				IsNull(a.additional_tel_no, '') AS AdditionalTelNo,
				IsNull(a.created_date, '') AS CreatedDate,
				IsNull(a.created_by, '') AS CreatedBy,
				IsNull(a.updated_date, '') AS UpdatedDate,
				IsNull(a.updated_by, '') AS UpdatedBt,
				IsNull(a.org_id, '') AS OrgId,
				IsNull(a.tenant_id, '') AS TenantId,
				IsNull(a.latitude, '') AS Latitude,
				IsNull(a.longitude, '') AS Longitude,
				IsNull(a.radius, '') AS Radius,
				IsNull('', '') AS Contact,
				IsNull('', '') AS SoldToAddr3Loc,
				IsNull(r.region_id, '') AS region_id
				FROM #tempCus a 
				inner join state s on a.state_code=s.state_code and s.org_id = a.org_id
				inner join country cy on a.country_code=cy.country_code and cy.tenant_id= a.tenant_id
				inner join mst_region r on r.region_code = a.region_code and r.org_id = a.org_id
				inner join customer werr on a.customer_code = werr.distributor_customer_code
				WHERE NOT EXISTS(SELECT 1 FROM mst_customer_contact_address st WHERE werr.customer_id = st.customer_id and st.address_type='Contact')
				AND isNull(a.error_message, '') = '' 
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID
				
				INSERT into  mst_customer_contact_address  ( customer_address_id,customer_id,address1,address2,address3,address4,address5,state_id,postcode,country_id,address_type,status,tel_no,additional_tel_no,created_date,created_by,updated_date,updated_by,org_id,tenant_id,latitude,longitude,radius,contact,sold_to_addr3_loc, region_id)
				SELECT Newid() AS CustomerAddressId,
				IsNull(werr.customer_id, '') AS CustomerId,
				Case When IsNull(a.billing_address1, '') = '' Then a.address1 ELSE a.billing_address1 END AS Address1,
				Case When IsNull(a.billing_address1, '') = '' Then a.address2 ELSE a.billing_address2 END AS Address2,
				Case When IsNull(a.billing_address1, '') = '' Then a.address3 ELSE a.billing_address3 END AS Address3,
				Case When IsNull(a.billing_address1, '') = '' Then a.address4 ELSE a.billing_address4 END AS Address4,
				Case When IsNull(a.billing_address1, '') = '' Then a.address5 ELSE a.billing_address5 END AS Address5,
				IsNull(s.state_id, '') AS StateId,
				IsNull(a.billing_postcode, '') AS PostCode,
				IsNull(cy.country_id, '') AS CountryId,
				'Billing' AS AddressType,
				IsNull(a.status, '') AS Status,
				IsNull(a.tel_no, '') AS TelNo,
				IsNull(a.additional_tel_no, '') AS AdditionalTelNo,
				IsNull(a.created_date, '') AS CreatedDate,
				IsNull(a.created_by, '') AS CreatedBy,
				IsNull(a.updated_date, '') AS UpdatedDate,
				IsNull(a.updated_by, '') AS UpdatedBt,
				IsNull(a.org_id, '') AS OrgId,
				IsNull(a.tenant_id, '') AS TenantId,
				IsNull(a.billing_latitude, '') AS Latitude,
				IsNull(a.billing_longitude, '') AS Longitude,
				IsNull(a.billing_radius, '') AS Radius,
				IsNull('', '') AS Contact,
				IsNull('', '') AS SoldToAddr3Loc,
				IsNull(r.region_id, '') AS region_id
				FROM #tempCus a 
				inner join state s on a.billing_state_code=s.state_code and s.org_id = a.org_id
				inner join country cy on a.billing_country_code=cy.country_code and cy.tenant_id= a.tenant_id
				inner join mst_region r on r.region_code = a.billing_region_code and r.org_id = a.org_id
				inner join customer werr on a.customer_code = werr.distributor_customer_code
				WHERE NOT EXISTS(SELECT 1 FROM mst_customer_contact_address st WHERE werr.customer_id = st.customer_id and st.address_type='Billing')
				AND isNull(a.error_message, '') = '' 
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID
				
				INSERT into  mst_customer_contact_address  ( customer_address_id,customer_id,address1,address2,address3,address4,address5,state_id,postcode,country_id,address_type,status,tel_no,additional_tel_no,created_date,created_by,updated_date,updated_by,org_id,tenant_id,latitude,longitude,radius,contact,sold_to_addr3_loc, region_id)
				SELECT newid() AS CustomerAddressId,
				IsNull(wert.customer_id, '') AS CustomerId,
				Case When IsNull(a.shipping_address1, '') = '' Then a.address1 ELSE a.shipping_address1 END AS Address1,
				Case When IsNull(a.shipping_address1, '') = '' Then a.address2 ELSE a.shipping_address2 END AS Address2,
				Case When IsNull(a.shipping_address1, '') = '' Then a.address3 ELSE a.shipping_address3 END AS Address3,
				Case When IsNull(a.shipping_address1, '') = '' Then a.address4 ELSE a.shipping_address4 END AS Address4,
				Case When IsNull(a.shipping_address1, '') = '' Then a.address5 ELSE a.shipping_address5 END AS Address5,
				IsNull(s.state_id, '') AS StateId,
				IsNull(a.shipping_postcode, '') AS PostCode,
				IsNull(cy.country_id, '') AS CountryId,
				'Shipping' AS AddressType,
				IsNull(a.status, '') AS Status,
				IsNull(a.tel_no, '') AS TelNo,
				IsNull(a.additional_tel_no, '') AS AdditionalTelNo,
				IsNull(a.created_date, '') AS CreatedDate,
				IsNull(a.created_by, '') AS CreatedBy,
				IsNull(a.updated_date, '') AS UpdatedDate,
				IsNull(a.updated_by, '') AS UpdatedBt,
				IsNull(a.org_id, '') AS OrgId,
				IsNull(a.tenant_id, '') AS TenantId,
				IsNull(a.shipping_latitude, '') AS Latitude,
				IsNull(a.shipping_longitude, '') AS Longitude,
				IsNull(a.shipping_radius, '') AS Radius,
				IsNull('', '') AS Contact,
				IsNull('', '') AS SoldToAddr3Loc,
				IsNull(r.region_id, '') AS region_id
				FROM #tempCus a 
				inner join state s on a.shipping_state_code=s.state_code and s.org_id = a.org_id
				inner join country cy on a.shipping_country_code=cy.country_code and cy.tenant_id= a.tenant_id
				inner join mst_region r on r.region_code = a.shipping_region_code and r.org_id = a.org_id
				inner join customer wert on a.customer_code=wert.distributor_customer_code
				WHERE NOT EXISTS(SELECT 1 FROM mst_customer_contact_address st WHERE wert.customer_id = st.customer_id and st.address_type='Shipping')
				AND isNull(a.error_message, '') = '' 
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID

				INSERT into  mst_customer_branch(customer_branch_id,customer_id,branch_id,status,created_date,created_by,updated_date,updated_by,org_id,tenant_id)
				SELECT IsNull(a.customer_branch_id, '') AS CustomerBranchId,
				IsNull(xc.customer_id, '') AS CustomerId,
				IsNull(b.branch_id, '') AS BranchId,
				IsNull(a.status, '') AS Status,
				IsNull(a.created_date, '') AS CreatedDate,
				IsNull(a.created_by, '') AS CreatedBy,
				IsNull(a.updated_date, '') AS UpdatedDate,
				IsNull(a.updated_by, '') AS UpdatedBy,
				IsNull(a.org_id, '') AS OrgId,
				IsNull(a.tenant_id, '') AS TenantId
				FROM #tempCus a 
				inner join mst_branch b on a.branch_code=b.branch_code and b.org_id = a.org_id
				inner join customer xc on a.customer_code=xc.distributor_customer_code
				WHERE NOT EXISTS(SELECT 1 FROM mst_customer_branch st WHERE xc.customer_id = st.customer_id)
				AND isNull(a.error_message, '') = '' 
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID
				
				INSERT into  customer_to_user_relation(customer_to_user_relation_id,customer_id,user_id,created_date,created_by,updated_date,updated_by,org_id,tenant_id,start_date,end_date)
				SELECT IsNull(a.customer_to_user_relation_id, '') AS CustomerToUserRelationId,
				IsNull(aw.customer_id, '') AS CustomerId,
				IsNull(m.member_id, '') AS user_id,
				IsNull(a.created_date, '') AS CreatedDate,
				IsNull(a.created_by, '') AS CreatedBy,
				IsNull(a.updated_date, '') AS UpdatedDate,
				IsNull(a.updated_by, '') AS UpdatedBy,
				IsNull(a.org_id, '') AS OrgId,
				IsNull(a.tenant_id, '') AS TenantId,
				'2023-01-01 00:00:00.000' as start_date,
				'2099-12-31 00:00:00.000' as end_date
				FROM #tempCus a 
				inner join member m on m.member_cd=a.person_in_charge and m.org_id = a.org_id
				inner join customer aw on a.customer_code=aw.distributor_customer_code
				WHERE NOT EXISTS(SELECT 1 FROM customer_to_user_relation st WHERE aw.customer_id = st.customer_id)
				AND isNull(a.error_message, '') = '' 
				--AND a.process_id = @ProcessId 
				--AND a.org_id = @OrganizationID
				
				--UPDATE org set running_no = (SELECT COUNT(1) FROM customer WHERE org_id = @OrganizationID) WHERE org_id = @OrganizationID

				Declare @MtCount int, @GtCount int;

				SET @MtCount = (Select Count(1) from #tempCus where org_id = @OrganizationID and process_id = @ProcessId and mtgt = 'MT' Group By mtgt) ;
				SET @GtCount = (Select Count(1) from #tempCus where org_id = @OrganizationID and process_id = @ProcessId and mtgt = 'GT' Group By mtgt) ;
							   								
				-- Set increment to 0 if NULL
				SET @MtCount = (Select IsNull(@MtCount,0));
				SET @GtCount = (Select IsNull(@GtCount,0));

				exec sp_update_configured_running_number @OrganizationID, @TenantId, 'MT', @MtCount;
				exec sp_update_configured_running_number @OrganizationID, @TenantId, 'GT', @GtCount;

				--Update org set running_no = Case When x.mtgt = 'GT' Then running_no + x.count Else running_no End
				--from (Select Count(1) [count], mtgt from #tempCus where org_id = @OrganizationID and process_id = @ProcessId and mtgt = 'GT' Group By mtgt) x
				--Where org_id = @TenantId

				--Update org set mt_running_no = Case When x.mtgt = 'MT' Then mt_running_no + x.count Else mt_running_no End
				--from (Select Count(1) [count], mtgt from #tempCus where org_id = @OrganizationID and process_id = @ProcessId and mtgt = 'MT' Group By mtgt) x
				--Where org_id = @TenantId




				
			END
	END
END