ALTER PROCEDURE dbo.CM_REQUEST_TEMPLATE_Get_Report_No
@P_DEP_CODE VARCHAR(30),
@p_REPORT_NO VARCHAR(50) OUT
AS
DECLARE @REPORT_NO INT
DECLARE @REPORT_YEAR INT=CONVERT(INT,(SELECT ParaValue FROM SYS_PARAMETERS WHERE ParaKey='Report_No_year'))

IF(@REPORT_YEAR=YEAR(GETDATE()))
BEGIN
	 SET @REPORT_NO =CONVERT(INT, (SELECT ParaValue FROM SYS_PARAMETERS WHERE ParaKey='Report_no'))
	 UPDATE SYS_PARAMETERS SET ParaValue=CONVERT(NVARCHAR,@REPORT_NO+1) WHERE ParaKey='Report_no'
END
ELSE
BEGIN 
	SET @REPORT_NO=1
	UPDATE SYS_PARAMETERS SET ParaValue=CONVERT(NVARCHAR,YEAR(GETDATE())) WHERE ParaKey='Report_No_year'
END
SET @p_REPORT_NO=RIGHT('000000'+CAST( @REPORT_NO AS varchar),6) +'/'+ CAST( YEAR(GETDATE()) AS varchar)+'/TTr-'+@P_DEP_CODE

GO