Job 4

-- ============================================================================================================================
-- Author: Santosh Gupta
-- Create date: 2012-03-09 08:45PM
-- Description: EOD Process.
--
-- Params:
-- @run_type INT, --Steps
-- @master_process_id VARCHAR(120),
-- @process_id VARCHAR(120),
-- @status VARCHAR(50) , -- 1 for success, 0 for failure
-- @date VARCHAR(10) Date
-- @exec_only_this_step INT = 0 -- 1 for single step execution, default 0 for serial execution
-- ============================================================================================================================
CREATE PROCEDURE [dbo].[usp_core_process]
    @run_type INT,
    @master_process_id VARCHAR(120) = NULL,
    @process_id VARCHAR(120) = NULL,
    @status VARCHAR(50) = NULL,   -- 1 for success, 0 for failure
    @date VARCHAR(10) = NULL,
    @exec_only_this_step INT = 1, -- 1 for single step execution, default 0 for serial execution
    @spa VARCHAR(MAX),
    @sendemail BIT = 1,
    @Module NVARCHAR(100),
    @returnvalue BIT = 0,
    @isschedule BIT = 0,
    @next_run_type INT = NULL,
    @starttime INT = NULL
AS

--EXEC usp_core_process '123',NULL,NULL,NULL,GETDATE,1,'select 1 '
SET NOCOUNT ON;

DECLARE @sql VARCHAR(MAX),
        @user_login_id VARCHAR(50),
        @job_name VARCHAR(150),
        @ssis_path VARCHAR(1000),
        @root VARCHAR(1000),
        @proc_desc VARCHAR(1000),
        @as_of_date VARCHAR(10),
        @source VARCHAR(100),
        @label VARCHAR(500),
        @log_status VARCHAR(50),
        @message VARCHAR(MAX),
        @Email VARCHAR(255);

DECLARE @mail_profile VARCHAR(100) = 'DbSupport';

DECLARE @count VARCHAR(10);
DECLARE @notes_id INT;
DECLARE @send_from VARCHAR(100);
DECLARE @send_to VARCHAR(5000);
DECLARE @send_cc VARCHAR(5000);
DECLARE @send_bcc VARCHAR(5000);
DECLARE @subject VARCHAR(250);
DECLARE @notes_text VARCHAR(MAX);
DECLARE @notes_attachment_filename VARCHAR(5000);
DECLARE @iMsg INT; --Object reference,


DECLARE @resultcode INT;
DECLARE @template_params VARCHAR(MAX);
IF @date IS NULL
    SET @date = CONVERT(VARCHAR(10), GETDATE(), 120);
--declare @sql nvarchar(max)
IF @next_run_type IS NULL
    SET @next_run_type = CONVERT(VARCHAR(10), GETDATE(), 112);
SET @log_status = @status;
--/* To DO CHANGE AS PER PROJECT
SET @Email = '[email protected]';
SET @source = 'SFPilotProject';
--*/
IF @log_status = 'TechError'
BEGIN
    SET @label = 'Job Process Stoped for run date ' + @date + ' (Technical Errors Found).';
    SET @label = @label + 'Manually Start The Jobs at Error Step: ' + @source;
    SET @message = '<font color=#0000ff><u>' + @label + '</u></font>';
    SET @template_params = 'Automated Job Failed at this step';
    SET @template_params = dbo.FNABuildNameValueXML(@template_params, '<JOB_RUN_DATE>', @date); --replace template fields
    SET @template_params = dbo.FNABuildNameValueXML(@template_params, '<JOB_STATUS>', 'Failure Job Step:' + @message);

    --PRINT @source
    --PRINT @label
    --PRINT @template_params
    --PRINT @email
    --PRINT @process_id

    --INSERT  INTO EmailNotes(
    -- NotesobjectName,
    -- notessubject,
    -- notestext,
    -- sendfrom,
    -- sendto,
    -- sendstatus,isactive,
    -- addedby,AddedOn,processid)
    -- VALUES (@source,@label,@template_params,@email, @Email,0,1,'Admin',GETDATE(),@process_id)

    SET @subject = 'Tech Error found while batch processing of ' + @Module;
    --EXEC msdb.dbo.sp_send_dbmail
    -- @profile_name=@mail_profile,
    -- @recipients=@email,
    -- @copy_recipients  = @send_cc,
    -- @blind_copy_recipients = @send_bcc,
    -- @subject = @Subject,
    -- @body = @message,
    -- @body_format = 'HTML';



    RETURN;
END;
ELSE

--IF @returnvalue = 0
--BEGIN
-- SET @spa='Select 1'
--END

IF @run_type = 123
BEGIN

    --SET @process_id = dbo.FNAGetNewID()
    SET @master_process_id = dbo.FNAGetNewID();
    SET @proc_desc = 'Automated Job Process';
    SET @job_name = @proc_desc + '_' + @process_id;

    IF @isschedule = 0
    BEGIN
        EXEC usp_process_as_job @job_name,
                                @spa,
                                @proc_desc,
                                @user_login_id,
                                'TSQL',
                                @run_type,
                                @master_process_id,
                                @process_id,
                                @date,
                                @exec_only_this_step,
                                @Module,
                                @returnvalue;
    END;
    ELSE IF @isschedule = 1
    BEGIN
        EXEC usp_process_as_job_schedule @job_name,
                                         @spa,
                                         @proc_desc,
                                         @user_login_id,
                                         'TSQL',
                                         @run_type,
                                         @master_process_id,
                                         @process_id,
                                         @date,
                                         @next_run_type,
                                         @starttime,
                                         NULL,
                                         NULL,
                                         NULL,
                                         NULL,
                                         NULL,
                                         NULL,
                                         NULL,
                                         NULL,
                                         @exec_only_this_step,
                                         @Module,
                                         @returnvalue;
    END;

    IF @sendemail = 1
    BEGIN
        SET @subject = 'Batch Processing completed successfully for  ' + @Module;
    --EXEC msdb.dbo.sp_send_dbmail
    -- @profile_name=@mail_profile,
    -- @recipients=@email,
    -- @copy_recipients  = @send_cc,
    -- @blind_copy_recipients = @send_bcc,
    -- @subject = @Subject,
    -- @body = @message,
    -- @body_format = 'HTML';

    END;
    RETURN;
END;
RETURN;