1.	Create two Services as:
		1. 	SDPL_Process_Service 
		2. 	SPARC_Process_Service 
		
2.	Add columns in both lead_master tables as :

ALTER TABLE <<DB>>.CTI_LEAD_MASTER_?
ADD COLUMN udf_Transaction_WorkType VARCHAR (100),
ADD COLUMN udf_Existing_PolicyNumber VARCHAR (100),
ADD COLUMN udf_Existing_PlanName VARCHAR (100),
ADD COLUMN udf_Existing_Modal_Premium VARCHAR (100),
ADD COLUMN udf_Servicing_Agent_Name VARCHAR (100),
ADD COLUMN udf_Servicing_Agent_Channel VARCHAR (100),
ADD COLUMN udf_Writing_Agent_Name VARCHAR (100),
ADD COLUMN udf_Writing_Agent_Channel VARCHAR (100),
ADD COLUMN udf_Banner VARCHAR (100),
ADD COLUMN udf_Banner_Premium VARCHAR (100),
ADD COLUMN udf_Current_Age VARCHAR (100),
ADD COLUMN udf_Current_Income VARCHAR (100),
ADD COLUMN udf_Current_State VARCHAR (100),
ADD COLUMN udf_Agency_Level VARCHAR (100),
ADD COLUMN udf_Agency_Activity VARCHAR (100),
ADD COLUMN udf_Campaign_Name VARCHAR (100),
ADD COLUMN udf_ClientId VARCHAR (100),
ADD COLUMN udf_Requested_DateTime VARCHAR (100),
ADD COLUMN udf_LeadType VARCHAR (100),
ADD COLUMN udf_SDPL_SSOID VARCHAR (100),
ADD COLUMN udf_SDPL_Name VARCHAR (100),
ADD COLUMN udf_SDPL_Location VARCHAR (100),
ADD COLUMN udf_SDPL_DID VARCHAR (100),
ADD COLUMN udf_BackUp_SDPL_Name VARCHAR (100),
ADD COLUMN udf_BackUp_SDPL_SSOID VARCHAR (100),
ADD COLUMN udf_BackUp_SDPL_Location VARCHAR (100),
ADD COLUMN udf_BackUp_SDPL_DID VARCHAR(100)

3. CREATE TABLE max_udt_roc_priority_master AS:
	CREATE TABLE [dbo].[max_udt_roc_priority_master](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[udf_roc] [varchar](150) NULL,
	[udf_roc_type] [varchar](10) NULL,
	[udf_roc_priority] [int] NULL,
 CONSTRAINT [PK_max_udt_roc_priority_master] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

4. ADD INDEX ON max_udt_roc_priority_master 
CREATE INDEX idx_udf_roc
ON max_udt_roc_priority_master (udf_roc);


5. Seed the table max_udt_roc_priority_master as:

INSERT INTO max_udt_roc_priority_master (udf_roc,udf_roc_type,udf_roc_priority) VALUES ('Helpline Call -Unanswered','Dialer','3')
INSERT INTO max_udt_roc_priority_master (udf_roc,udf_roc_type,udf_roc_priority) VALUES ('DID Abandon Call','Dialer','4')
INSERT INTO max_udt_roc_priority_master (udf_roc,udf_roc_type,udf_roc_priority) VALUES ('Freelook retention -Fresh','API Flow','7')
INSERT INTO max_udt_roc_priority_master (udf_roc,udf_roc_type,udf_roc_priority) VALUES ('Surrender Retention -Fresh','API Flow','5')
INSERT INTO max_udt_roc_priority_master (udf_roc,udf_roc_type,udf_roc_priority) VALUES ('Website Drop Journey -Fresh','API Flow','8')
INSERT INTO max_udt_roc_priority_master (udf_roc,udf_roc_type,udf_roc_priority) VALUES ('Auto Debit deactivation -Fresh','API Flow','6')


5.	CREATE FUNCTION max_fnc_get_roc_priority AS:

CREATE function [dbo].[max_fnc_get_roc_priority] (@roc_name varchar(150))
returns int 
AS
BEGIN
DECLARE @szreturn as INT
	SET @szreturn = 0
	select top 1 @szreturn=isnull(udf_roc_priority,0) from max_udt_roc_priority_master where UPPER(udf_roc)=UPPER(@roc_name)
	return @szreturn
END

6. CREATE PROCEDURE max_sdpl_sparc_insert_lead AS :
-- EXEC max_sdpl_sparc_insert_lead '@nTransactionWorkType','@nCustomerName','@nContactNumber','@nExistingPolicyNumber','@nExistingPlanName','@nExistingModalPremium','@nServicingAgentName','@nServicingAgentChannel','@nWritingAgentName','@nWritingAgentChannel','@nBanner','@nBannerPremium','@nCurrentAge','@nCurrentIncome','@nCurrentState','@nAgencyLevel','@nAgencyActivity','@nCampaignName','@nClientId','@nRegisterdNumber','@nRequestedDateTime','@nLeadType','@nSDPLSSOID','@nSDPLName','@nSDPLLocation','@nSDPLDID','@nBackUpSDPLName','@nBackUpSDPLSSOID','@nBackUpSDPLLocation','@nBackUpSDPLDID'
CREATE PROCEDURE [dbo].[max_sdpl_sparc_insert_lead]	
@nTransactionWorkType varchar(100),
@nCustomerName varchar(100),
@nContactNumber varchar(100),
@nExistingPolicyNumber varchar(100),
@nExistingPlanName varchar(100),
@nExistingModalPremium varchar(100),
@nServicingAgentName varchar(100),
@nServicingAgentChannel varchar(100),
@nWritingAgentName varchar(100),
@nWritingAgentChannel varchar(10),
@nBanner varchar(100),
@nBannerPremium varchar(100),
@nCurrentAge varchar(100),
@nCurrentIncome varchar(100),
@nCurrentState varchar(100),
@nAgencyLevel varchar(100),
@nAgencyActivity varchar(100),
@nCampaignName varchar(100),
@nClientId varchar(100),
@nRegisterdNumber varchar(100),
@nRequestedDateTime varchar(100),
@nLeadType varchar(50),
@nSDPLSSOID varchar(100),
@nSDPLName varchar(100),
@nSDPLLocation varchar(100),
@nSDPLDID varchar(100),
@nBackUpSDPLName varchar(100),
@nBackUpSDPLSSOID varchar(100),
@nBackUpSDPLLocation varchar(100),
@nBackUpSDPLDID varchar(100)

AS
BEGIN
	DECLARE @nServiceId int

	If UPPER(@nLeadType)='SDPL'
	BEGIN
		SET @nServiceId=5
		EXEC SDPL_INSERT_LEAD_PROC @nServiceId,@nTransactionWorkType,@nCustomerName,@nContactNumber,@nExistingPolicyNumber,@nExistingPlanName,@nExistingModalPremium,@nServicingAgentName,@nServicingAgentChannel,@nWritingAgentName,@nWritingAgentChannel,@nBanner,@nBannerPremium,@nCurrentAge,@nCurrentIncome,@nCurrentState,@nAgencyLevel,@nAgencyActivity,@nCampaignName,@nClientId,@nRegisterdNumber,@nRequestedDateTime,@nLeadType,@nSDPLSSOID,@nSDPLName,@nSDPLLocation,@nSDPLDID,@nBackUpSDPLName,@nBackUpSDPLSSOID,@nBackUpSDPLLocation,@nBackUpSDPLDID
	END
	ELSE IF UPPER(@nLeadType)='SPARC'
	BEGIN
		SET @nServiceId=6
		EXEC SPARC_INSERT_LEAD_PROC @nServiceId,@nTransactionWorkType,@nCustomerName,@nContactNumber,@nExistingPolicyNumber,@nExistingPlanName,@nExistingModalPremium,@nServicingAgentName,@nServicingAgentChannel,@nWritingAgentName,@nWritingAgentChannel,@nBanner,@nBannerPremium,@nCurrentAge,@nCurrentIncome,@nCurrentState,@nAgencyLevel,@nAgencyActivity,@nCampaignName,@nClientId,@nRegisterdNumber,@nRequestedDateTime,@nLeadType,@nSDPLSSOID,@nSDPLName,@nSDPLLocation,@nSDPLDID,@nBackUpSDPLName,@nBackUpSDPLSSOID,@nBackUpSDPLLocation,@nBackUpSDPLDID
	END
	ELSE
	BEGIN
	Select '-1' as LeadId
	END

END

7. CREATE PROCEDURE SDPL_INSERT_LEAD_PROC AS:
-- EXEC SDPL_INSERT_LEAD_PROC 5,'@nTransactionWorkType','@nCustomerName','@nContactNumber','@nExistingPolicyNumber','@nExistingPlanName','@nExistingModalPremium','@nServicingAgentName','@nServicingAgentChannel','@nWritingAgentName','@nWritingAgentChannel','@nBanner','@nBannerPremium','@nCurrentAge','@nCurrentIncome','@nCurrentState','@nAgencyLevel','@nAgencyActivity','@nCampaignName','@nClientId','@nRegisterdNumber','@nRequestedDateTime','@nLeadType','@nSDPLSSOID','@nSDPLName','@nSDPLLocation','@nSDPLDID','@nBackUpSDPLName','@nBackUpSDPLSSOID','@nBackUpSDPLLocation','@nBackUpSDPLDID'
CREATE PROCEDURE [dbo].[SDPL_INSERT_LEAD_PROC]	
@nServiceId INT,
@nTransactionWorkType varchar(100),
@nCustomerName varchar(100),
@nContactNumber varchar(100),
@nExistingPolicyNumber varchar(100),
@nExistingPlanName varchar(100),
@nExistingModalPremium varchar(100),
@nServicingAgentName varchar(100),
@nServicingAgentChannel varchar(100),
@nWritingAgentName varchar(100),
@nWritingAgentChannel varchar(10),
@nBanner varchar(100),
@nBannerPremium varchar(100),
@nCurrentAge varchar(100),
@nCurrentIncome varchar(100),
@nCurrentState varchar(100),
@nAgencyLevel varchar(100),
@nAgencyActivity varchar(100),
@nCampaignName varchar(100),
@nClientId varchar(100),
@szCLI varchar(100),/*@nRegisterdNumber */
@nRequestedDateTime varchar(100),
@nLeadType varchar(100),
@nSDPLSSOID varchar(100),
@nSDPLName varchar(100),
@nSDPLLocation varchar(100),
@nSDPLDID varchar(100),
@nBackUpSDPLName varchar(100),
@nBackUpSDPLSSOID varchar(100),
@nBackUpSDPLLocation varchar(100),
@nBackUpSDPLDID varchar(100)
AS
BEGIN
	
	DECLARE @strTemp AS varchar(max)
	DECLARE @nSourceCount as Int
	DECLARE @strTableDbName AS varchar(999)
	DECLARE @leadmaster_table AS varchar(999)
	DECLARE @ServiceName AS varchar(100)
	DECLARE @Batch AS varchar(15)
	DECLARE @LeadFixAgent AS INT

	
	SELECT  @strTableDbName = service_outbound_lead_db_name, 
	@leadmaster_table = service_leadstructure_master_tablename, 
	@ServiceName = service_name
	FROM cti_services   
	WHERE service_id = @nServiceId  

	SET @LeadFixAgent=0
	Set @Batch ='SDL'+Replace(convert(varchar(10),getdate(),21),'-','')

	IF(LEN(@nSDPLSSOID))>0
	BEGIN
		SET @LeadFixAgent=dbo.idg_fnc_GetAgentId(@nSDPLSSOID)
	END
	ELSE IF LEN(@nBackUpSDPLSSOID)>0
	BEGIN
		SET @LeadFixAgent=dbo.idg_fnc_GetAgentId(@nBackUpSDPLSSOID)
	END
	if LEN(@LeadFixAgent)=0
	BEGIN
	SET @LeadFixAgent=0
	END
	
	SET @strTemp = 'INSERT INTO '+@strTableDbName+'..'+ @leadmaster_table +' (
						lead_service_id, lead_import_batch_id, 
						lead_import_batch_no, lead_zone, 
						lead_phone1,
						lead_import_batch_start_date, lead_import_batch_end_date, 
						lead_call_type, lead_status, lead_next_dial_time, lead_attempts, lead_used,lead_fix_agent,lead_dialable,lead_priority,
						udf_Transaction_WorkType,lead_fname,lead_phone2,udf_Existing_PolicyNumber,udf_Existing_PlanName,
						udf_Existing_Modal_Premium,udf_Servicing_Agent_Name,udf_Servicing_Agent_Channel,udf_Writing_Agent_Name,
						udf_Writing_Agent_Channel,udf_Banner,udf_Banner_Premium,udf_Current_Age,udf_Current_Income,udf_Current_State,
						udf_Agency_Level,udf_Agency_Activity,udf_Campaign_Name,udf_ClientId,udf_Requested_DateTime,
						udf_LeadType,udf_SDPL_SSOID,udf_SDPL_Name,udf_SDPL_Location,udf_SDPL_DID,udf_BackUp_SDPL_Name,udf_BackUp_SDPL_SSOID,
						udf_BackUp_SDPL_Location,udf_BackUp_SDPL_DID
							) 
						VALUES (
						'+convert(varchar(25),@nServiceId)+', 0, 
						''' + @Batch + ''', ''AAA'',
						'''+ @szCLI +''',
						GETDATE(), CAST(CONVERT(date, getdate()) AS VARCHAR(50))+'' 23:59:59'', ''O'', ''P'', DATEADD(MINUTE, 1, GETDATE()), 0, ''N'','+CONVERT(varchar(25),@LeadFixAgent)+',''Y'',dbo.max_fnc_get_roc_priority('''+@nCampaignName+'''),
						'''+@nTransactionWorkType+''','''+@nCustomerName+''','''+@nContactNumber+''','''+@nExistingPolicyNumber+''',
						'''+@nExistingPlanName+''','''+@nExistingModalPremium+''','''+@nServicingAgentName+''','''+@nServicingAgentChannel+''',
						'''+@nWritingAgentName+''','''+@nWritingAgentChannel+''','''+@nBanner+''','''+@nBannerPremium+''','''+@nCurrentAge+''',
						'''+@nCurrentIncome+''','''+@nCurrentState+''','''+@nAgencyLevel+''','''+@nAgencyActivity+''','''+@nCampaignName+''',
						'''+@nClientId+''','''+@nRequestedDateTime+''','''+@nLeadType+''','''+@nSDPLSSOID+''',
						'''+@nSDPLName+''','''+@nSDPLLocation+''','''+@nSDPLDID+''','''+@nBackUpSDPLName+''','''+@nBackUpSDPLSSOID+''',
						'''+@nBackUpSDPLLocation+''','''+@nBackUpSDPLDID+'''
						)'

		EXEC(@strTemp)

		Select @@Identity As LeadId

		set @nSourceCount=0   
		select @nSourceCount = count(*)  from cti_lead_batch_master where Leadimport_Batch_name = @Batch
		and  leadimport_service_id = @nServiceId
		group by Leadimport_Batch_name  

	if @nSourceCount <= 0  
	 BEGIN      
		INSERT INTO [CTI_Lead_Batch_Master]  
		([leadimport_service_id]  
		,[leadimport_service_name]  
		,[leadimport_template_id]  
		,[leadimport_template_name]  
		,[leadimport_vendor_id]  
		,[leadimport_vendor_name]  
		,[leadimport_batch_name]  
		,[leadimport_country_code]  
		,[leadimport_source_type]  
		,[leadimport_source_name]  
		,[leadimport_source_count]  
		,[leadimport_import_count]  
		,[leadimport_rejected_count]  
		,[leadimport_rejected_dnc]  
		,[leadimport_rejected_invalid]  
		,[leadimport_rejected_fieldrules]  
		,[leadimport_rejected_zones]  
		,[leadimport_rejected_duplicate]  
		,[leadimport_rejected_error]  
		,[leadimport_imported_by_id]  
		,[leadimport_imported_by_name]  
		,[leadimport_imported_on_computer]  
		,[leadimport_imported_on_date]  
		,[leadimport_summary]  
		,[leadimport_batch_status]  
		,[leadimport_batch_refill_percentage]  
		,[leadimport_batch_usage_percentage]  
		,[Leadimport_zone]  
		,[Leadimport_batch_fix_agents]  
		,[leadimport_batch_start_date]  
		,[leadimport_batch_end_date])  
		VALUES  
		(@nServiceId,'' + @ServiceName + '',-1,'',-1,'','' + @Batch + '','','E','SQLPROCEDURE',  
		1,1,0,0,0,0,0,0,0,0,'TSPL','TSPL',getdate(),'','A','',0,'AAA','N',GETDATE(),CAST(CONVERT(date, getdate()) AS VARCHAR(50))+' 23:59:59')  
 
	 END  
	ELSE  
	 BEGIN  
		UPDATE CTI_Lead_Batch_Master Set   
		leadimport_source_count = leadimport_source_count + 1 ,  
		leadimport_import_count = leadimport_import_count + 1  WHERE Leadimport_Batch_name =@Batch  
		AND leadimport_service_id = @nServiceId
		AND leadimport_zone = 'AAA'
	 END    
	
END


8.	CREATE PROCEDURE SPARC_INSERT_LEAD_PROC AS:

-- EXEC SPARC_INSERT_LEAD_PROC 6,'@nTransactionWorkType','@nCustomerName','@nContactNumber','@nExistingPolicyNumber','@nExistingPlanName','@nExistingModalPremium','@nServicingAgentName','@nServicingAgentChannel','@nWritingAgentName','@nWritingAgentChannel','@nBanner','@nBannerPremium','@nCurrentAge','@nCurrentIncome','@nCurrentState','@nAgencyLevel','@nAgencyActivity','@nCampaignName','@nClientId','@nRegisterdNumber','@nRequestedDateTime','@nLeadType','@nSDPLSSOID','@nSDPLName','@nSDPLLocation','@nSDPLDID','@nBackUpSDPLName','@nBackUpSDPLSSOID','@nBackUpSDPLLocation','@nBackUpSDPLDID'
CREATE PROCEDURE [dbo].[SPARC_INSERT_LEAD_PROC]	
@nServiceId INT,
@nTransactionWorkType varchar(100),
@nCustomerName varchar(100),
@nContactNumber varchar(100),
@nExistingPolicyNumber varchar(100),
@nExistingPlanName varchar(100),
@nExistingModalPremium varchar(100),
@nServicingAgentName varchar(100),
@nServicingAgentChannel varchar(100),
@nWritingAgentName varchar(100),
@nWritingAgentChannel varchar(10),
@nBanner varchar(100),
@nBannerPremium varchar(100),
@nCurrentAge varchar(100),
@nCurrentIncome varchar(100),
@nCurrentState varchar(100),
@nAgencyLevel varchar(100),
@nAgencyActivity varchar(100),
@nCampaignName varchar(100),
@nClientId varchar(100),
@szCLI varchar(100),/*@nRegisterdNumber */
@nRequestedDateTime varchar(100),
@nLeadType varchar(50),
@nSDPLSSOID varchar(100),
@nSDPLName varchar(100),
@nSDPLLocation varchar(100),
@nSDPLDID varchar(100),
@nBackUpSDPLName varchar(100),
@nBackUpSDPLSSOID varchar(100),
@nBackUpSDPLLocation varchar(100),
@nBackUpSDPLDID varchar(100)
AS
BEGIN
	
	DECLARE @strTemp AS varchar(max)
	DECLARE @nSourceCount as Int
	DECLARE @strTableDbName AS varchar(999)
	DECLARE @leadmaster_table AS varchar(999)
	DECLARE @ServiceName AS varchar(100)
	DECLARE @Batch AS varchar(15)

	
	SELECT  @strTableDbName = service_outbound_lead_db_name, 
	@leadmaster_table = service_leadstructure_master_tablename, 
	@ServiceName = service_name
	FROM cti_services   
	WHERE service_id = @nServiceId  

	Set @Batch ='SPR'+Replace(convert(varchar(10),getdate(),21),'-','')

	SET @strTemp = 'INSERT INTO '+@strTableDbName+'..'+ @leadmaster_table +' (
						lead_service_id, lead_import_batch_id, 
						lead_import_batch_no, lead_zone, 
						lead_phone1,
						lead_import_batch_start_date, lead_import_batch_end_date, 
						lead_call_type, lead_status, lead_next_dial_time, lead_attempts, lead_used,lead_fix_agent,lead_dialable,
						udf_Transaction_WorkType,lead_fname,lead_phone2,udf_Existing_PolicyNumber,udf_Existing_PlanName,
						udf_Existing_Modal_Premium,udf_Servicing_Agent_Name,udf_Servicing_Agent_Channel,udf_Writing_Agent_Name,
						udf_Writing_Agent_Channel,udf_Banner,udf_Banner_Premium,udf_Current_Age,udf_Current_Income,udf_Current_State,
						udf_Agency_Level,udf_Agency_Activity,udf_Campaign_Name,udf_ClientId,udf_Requested_DateTime,
						udf_LeadType,udf_SDPL_SSOID,udf_SDPL_Name,udf_SDPL_Location,udf_SDPL_DID,udf_BackUp_SDPL_Name,udf_BackUp_SDPL_SSOID,
						udf_BackUp_SDPL_Location,udf_BackUp_SDPL_DID
							) 
						VALUES (
						'+convert(varchar(25),@nServiceId)+', 0, 
						''' + @Batch + ''', ''AAA'',
						'''+ @szCLI +''',
						GETDATE(), CAST(CONVERT(date, getdate()) AS VARCHAR(50))+'' 23:59:59'', ''O'', ''P'', DATEADD(MINUTE, 1, GETDATE()), 0, ''N'',0,''Y'',
						'''+@nTransactionWorkType+''','''+@nCustomerName+''','''+@nContactNumber+''','''+@nExistingPolicyNumber+''',
						'''+@nExistingPlanName+''','''+@nExistingModalPremium+''','''+@nServicingAgentName+''','''+@nServicingAgentChannel+''',
						'''+@nWritingAgentName+''','''+@nWritingAgentChannel+''','''+@nBanner+''','''+@nBannerPremium+''','''+@nCurrentAge+''',
						'''+@nCurrentIncome+''','''+@nCurrentState+''','''+@nAgencyLevel+''','''+@nAgencyActivity+''','''+@nCampaignName+''',
						'''+@nClientId+''','''+@nRequestedDateTime+''','''+@nLeadType+''','''+@nSDPLSSOID+''',
						'''+@nSDPLName+''','''+@nSDPLLocation+''','''+@nSDPLDID+''','''+@nBackUpSDPLName+''','''+@nBackUpSDPLSSOID+''',
						'''+@nBackUpSDPLLocation+''','''+@nBackUpSDPLDID+'''
						)'


		EXEC(@strTemp)

		Select @@Identity As LeadId

		set @nSourceCount=0   
		select @nSourceCount = count(*)  from cti_lead_batch_master where Leadimport_Batch_name = @Batch
		and  leadimport_service_id = @nServiceId
		group by Leadimport_Batch_name  

	if @nSourceCount <= 0  
	 BEGIN      
		INSERT INTO [CTI_Lead_Batch_Master]  
		([leadimport_service_id]  
		,[leadimport_service_name]  
		,[leadimport_template_id]  
		,[leadimport_template_name]  
		,[leadimport_vendor_id]  
		,[leadimport_vendor_name]  
		,[leadimport_batch_name]  
		,[leadimport_country_code]  
		,[leadimport_source_type]  
		,[leadimport_source_name]  
		,[leadimport_source_count]  
		,[leadimport_import_count]  
		,[leadimport_rejected_count]  
		,[leadimport_rejected_dnc]  
		,[leadimport_rejected_invalid]  
		,[leadimport_rejected_fieldrules]  
		,[leadimport_rejected_zones]  
		,[leadimport_rejected_duplicate]  
		,[leadimport_rejected_error]  
		,[leadimport_imported_by_id]  
		,[leadimport_imported_by_name]  
		,[leadimport_imported_on_computer]  
		,[leadimport_imported_on_date]  
		,[leadimport_summary]  
		,[leadimport_batch_status]  
		,[leadimport_batch_refill_percentage]  
		,[leadimport_batch_usage_percentage]  
		,[Leadimport_zone]  
		,[Leadimport_batch_fix_agents]  
		,[leadimport_batch_start_date]  
		,[leadimport_batch_end_date])  
		VALUES  
		(@nServiceId,'' + @ServiceName + '',-1,'',-1,'','' + @Batch + '','','E','SQLPROCEDURE',  
		1,1,0,0,0,0,0,0,0,0,'TSPL','TSPL',getdate(),'','A','',0,'AAA','N',GETDATE(),CAST(CONVERT(date, getdate()) AS VARCHAR(50))+' 23:59:59')  
 
	 END  
	ELSE  
	 BEGIN  
		UPDATE CTI_Lead_Batch_Master Set   
		leadimport_source_count = leadimport_source_count + 1 ,  
		leadimport_import_count = leadimport_import_count + 1  WHERE Leadimport_Batch_name =@Batch  
		AND leadimport_service_id = @nServiceId
		AND leadimport_zone = 'AAA'
	 END    

END


9. API Testing details:

SDPL Campaign Testing:
=========================

URL: http://<<HOST IP>>/api/leadpush/Post
Method:POST
Body:
{
  "TransactionWorkType": "TW008",
  "CustomerName": "Naresh Chand Sharma",
  "ContactNumber": "9953595715",
  "ExistingPolicyNumber": "POL02124",
  "ExistingPlanName": "MAXAnandLife",
  "ExistingModalPremium": "108440",
  "ServicingAgentName": "Neha",
  "ServicingAgentChannel": "Ch1",
  "WritingAgentName": "Gitika",
  "WritingAgentChannel": "WCH01",
  "Banner": "Max6",
  "BannerPremium": "8550",
  "CurrentAge": "38",
  "CurrentIncome": "258700",
  "CurrentState": "New Delhi",
  "AgencyLevel": "L1",
  "AgencyActivity": "Working",
  "CampaignName": "Website Drop Journey -Fresh",
  "ClientId": "pclient5",
  "RegisterdNumber": "9952247899",
  "RequestedDateTime": "2021-06-25 12:10:00",
  "LeadType": "SDPL",
  "SDPLSSOID": "agent1",
  "SDPLName": "SDPL",
  "SDPLLocation": "SDPLLOC1",
  "SDPLDID": "5558",
  "BackUpSDPLName": "SDPLBCK1",
  "BackUpSDPLSSOID": "SSO1",
  "BackUpSDPLLocation": "BCKDelhi",
  "BackUpSDPLDID": "BCK1001"
}
=========== API RESULTS =========================
In Case of Success:
{
	"status": "Success",
	"description": "Lead Id:9"
}
In Case of Error:
Error Sample1:
{
"status": "Error",
"description": "Invaild Lead Type, Failed to insert lead!"
}
Error Sample2:
{
"status": "Error",
"description": "Invalid mobile number!"
}
Error Sample3:
{
"status": "Error",
"description": <<ERROR MESSAGE>>
}

================================================================================================
SPARC Campaign Testing:
=========================

URL: http://<<HOST IP>>/api/leadpush/Post
Method:POST
Body:
{
  "TransactionWorkType": "TW008",
  "CustomerName": "Naresh Chand Sharma",
  "ContactNumber": "9953595715",
  "ExistingPolicyNumber": "POL02124",
  "ExistingPlanName": "MAXAnandLife",
  "ExistingModalPremium": "108440",
  "ServicingAgentName": "Neha",
  "ServicingAgentChannel": "Ch1",
  "WritingAgentName": "Gitika",
  "WritingAgentChannel": "WCH01",
  "Banner": "Max6",
  "BannerPremium": "8550",
  "CurrentAge": "38",
  "CurrentIncome": "258700",
  "CurrentState": "New Delhi",
  "AgencyLevel": "L1",
  "AgencyActivity": "Working",
  "CampaignName": "Website Drop Journey -Fresh",
  "ClientId": "pclient5",
  "RegisterdNumber": "9952247899",
  "RequestedDateTime": "2021-06-25 12:10:00",
  "LeadType": "SPARC",
  "SDPLSSOID": "agent1",
  "SDPLName": "SPARC",
  "SDPLLocation": "SDPLLOC1",
  "SDPLDID": "5558",
  "BackUpSDPLName": "SDPLBCK1",
  "BackUpSDPLSSOID": "SSO1",
  "BackUpSDPLLocation": "BCKDelhi",
  "BackUpSDPLDID": "BCK1001"
}
=========== API RESULTS =========================
In Case of Success:
{
	"status": "Success",
	"description": "Lead Id:9"
}
In Case of Error:
Error Sample1:
{
"status": "Error",
"description": "Invaild Lead Type, Failed to insert lead!"
}
Error Sample2:
{
"status": "Error",
"description": "Invalid mobile number!"
}
Error Sample3:
{
"status": "Error",
"description": <<ERROR MESSAGE>>
}