-- ==============================================================================
-- DBMS Name       :     SQL Server
-- Script Name     :     update_current_dt_parameter
-- Description     :     WhereScape generated
-- Generated by    :     WhereScape RED
-- Generated for   :     Raphael Klebanov
-- Author          :     Raphael Klebanov
-- ==============================================================================
-- Notes / History
-- This procedure executes as a very first task in the DW Loading Process
-- << THIS IS CUSTOM PROCEDURE. PLEASE DO NOT REBUILD >>

CREATE PROCEDURE update_current_dt_parameter
  @p_sequence         integer
, @p_job_name         varchar(256)
, @p_task_name        varchar(256)
, @p_job_id           integer
, @p_task_id          integer
, @p_return_msg       varchar(256) OUTPUT
, @p_status           integer      OUTPUT
AS
  SET XACT_ABORT OFF  -- Turn off auto abort on errors

  SET NOCOUNT ON      -- Turn off row count messages

  --===============================================================
  -- Control variables used in most programs
  --===============================================================
  DECLARE
    @v_msgtext         varchar(256) -- Text for audit_trail
  , @v_step            integer      -- return code
  , @v_update_count    integer      -- no of records updated
  , @v_insert_count    integer      -- no of records inserted
  , @v_count           integer      -- General counter
  , @v_return_status   integer      -- Query result status
  , @v_row_count       integer      -- Query returned row count
  , @v_db_code         varchar(10)  -- Database error code
  , @v_db_msg          varchar(100) -- Database error message
  , @v_today           varchar(30)  -- Today's timestamp

  --===============================================================
  -- Main
  --===============================================================
  SET @v_step = 100
  SET @v_update_count = 0
  SET @v_insert_count = 0

  -- ====================================================================
  -- Convert the date to a format of yyyy-mm-dd hh:mi:ss.mmm(24h)
  -- ====================================================================

  set @v_today = CONVERT(VARCHAR,GETDATE(),121)

  -- ====================================================================
  -- Update the parameter table with current day info
  -- ====================================================================

  Exec WsParameterWrite 'Current_dt' ,@v_today,'Updated via update_current_dt_parameter proc'

  --===============================================================
  -- Handle Error
  --===============================================================
  IF @v_return_status <> 0
  BEGIN
    SET @v_db_code = CONVERT(varchar, @v_return_status)
    SELECT @v_db_msg = description FROM master.dbo.sysmessages
    WHERE error = @v_return_status
    SET @p_return_msg = 'Unhandled Exception in update_current_dt_parameter at step '
      + CONVERT(varchar,@v_step) +  SUBSTRING(@v_db_msg,1,150)
    EXEC @v_return_status = WsWrkAudit 'F',@p_job_name,@p_task_name,
    @p_sequence,@p_return_msg,@v_db_code,@v_db_msg,@p_task_id,@p_job_id
    SET @p_status = -3
    RETURN 0
  END

  --===============================================================
  --All Done report the results and return.
  --p_status is the return code. Valid values are:
  --  1  successful completion
  -- -2  failed with error
  -- -3  failed with unhandled error
  --p_return_msg is a 256 character message which should
  --  provide a summary of the result of this procedure.
  --===============================================================

    SET @v_step = 200
    SET @p_return_msg = 'Update Load Date Parameter completed.Current Date is ' + @v_today
    SET @p_status = 1


  RETURN 0