PROCEDURE clCreateUnpostedTrxEarnType 
(
@SERIES int,
@PERIODID int,
@YEAR int,
@PTRATIO float,
@CPTYPE int,
@CPTRXTYPE int,
@Startdate datetime,
@Enddate datetime,
@O_iErrorState int = NULL  output 
)

CREATE PROCEDURE clCreateUnpostedTrxEarnType 
(
@SERIES int,
@PERIODID int,
@YEAR int,
@PTRATIO float,
@CPTYPE int,
@CPTRXTYPE int,
@Startdate datetime,
@Enddate datetime,
@O_iErrorState int = NULL  output 
)
AS
Declare @count int
Declare @tTransaction tinyint,
 	@iError int
BEGIN
	SELECT @O_iErrorState = 0
	
	IF @@trancount = 0
	BEGIN
	 SELECT @tTransaction = 1
	 BEGIN TRANSACTION
	END 
	
	IF @SERIES IS NULL OR
	 @PERIODID IS NULL OR
	 @YEAR IS NULL OR
	 @PTRATIO IS NULL OR
	 @CPTYPE IS NULL OR
	 @CPTRXTYPE IS NULL OR
	 @Startdate IS NULL OR
	 @Enddate IS NULL 
	 BEGIN
	 	SELECT @iError = 20018
		GOTO Error
	 END 
	
	DELETE CL40104 WHERE SYEAR=@YEAR AND SMONTH=@PERIODID AND CPTYPE=@CPTYPE AND CPTRXTYPE=@CPTRXTYPE
	
	SELECT @iError = @@error
	IF @iError <> 0
		GOTO Error
	
	INSERT INTO CL40104 (SYEAR,SMONTH,CUSTNMBR,CPTYPE,CPTRXTYPE,CPTRXSOURCE,ORTRXSRC,
	COMMENT_4,CLFUNCSALAMNT,PTRATIO,CPPOINTS,CPPOSTED)
	SELECT @YEAR,@PERIODID,CASE WHEN f.CUSTNMBR IS NULL THEN g.CUSTNMBR ELSE f.CUSTNMBR END,@CPTYPE,@CPTRXTYPE,dbo.clGetNextTrxSource(@CPTYPE,@CPTRXTYPE,@PERIODID,@YEAR),'SOP',
	convert(varchar(24),getdate()),	round((isnull(f.XTNDPRCE,0)- isnull(g.XTNDPRCE,0)),0),@PTRATIO,
	@PTRATIO*round((isnull(f.XTNDPRCE,0)- isnull(g.XTNDPRCE,0)),0),0 FROM 
	(SELECT b.CUSTNMBR,sum(isnull(a.XTNDPRCE,0)) AS XTNDPRCE FROM SOP30300 a 
	 INNER JOIN SOP30200 b ON a.SOPTYPE=b.SOPTYPE AND a.SOPNUMBE=b.SOPNUMBE
	 INNER JOIN CL40102 c ON b.CUSTNMBR=c.CUSTNMBR 
	 INNER JOIN IV00101 d ON a.ITEMNMBR=d.ITEMNMBR 
	 INNER JOIN EXT00103 e ON d.ITMCLSCD=e.PT_UD_Key 
	WHERE b.GLPOSTDT >= c.REGDATE AND c.REGFLAG=1 AND e.PT_Window_ID='ITEMCLASSPRIV'
	AND a.PRCLEVEL NOT LIKE 'PRIV%' AND a.SOPTYPE=3 AND b.CUSTNMBR IS NOT NULL
	AND b.VOIDSTTS=0 AND b.GLPOSTDT BETWEEN @Startdate AND @Enddate
	group by b.CUSTNMBR) f FULL OUTER JOIN
	(select b.CUSTNMBR,sum(isnull(a.XTNDPRCE,0)) AS XTNDPRCE FROM SOP30300 a 
	 INNER JOIN SOP30200 b ON a.SOPTYPE=b.SOPTYPE AND a.SOPNUMBE=b.SOPNUMBE
	 INNER JOIN CL40102 c ON b.CUSTNMBR=c.CUSTNMBR 
	 INNER JOIN IV00101 d ON a.ITEMNMBR=d.ITEMNMBR 
	 INNER JOIN EXT00103 e ON d.ITMCLSCD=e.PT_UD_Key 
	WHERE b.GLPOSTDT >= c.REGDATE AND c.REGFLAG=1 AND e.PT_Window_ID='ITEMCLASSPRIV'
	AND a.PRCLEVEL NOT LIKE 'PRIV%' AND a.SOPTYPE=4 AND b.CUSTNMBR IS NOT NULL
	AND b.VOIDSTTS=0 AND b.GLPOSTDT BETWEEN @Startdate AND @Enddate
	group by b.CUSTNMBR) g on f.CUSTNMBR =g.CUSTNMBR
	
	SELECT @iError = @@error
	IF @iError <> 0
		GOTO Error
	
	SELECT @count=count(1)  FROM CL40104 WHERE SYEAR=@YEAR AND SMONTH=@PERIODID 
	AND CPTYPE=@CPTYPE AND CPTRXTYPE=@CPTRXTYPE
	
	SELECT @iError = @@error
	IF @iError <> 0
		GOTO Error
	
	UPDATE CL40106 SET CL_Trx_Source_ID=CL_Trx_Source_ID + @count WHERE CPTYPE=@CPTYPE AND CPTRXTYPE=@CPTRXTYPE
	
	SELECT @iError = @@error
	IF @iError <> 0
		GOTO Error
Error:
	SELECT @O_iErrorState = @iError
END

IF @O_iErrorState <> 0
BEGIN	 
IF @tTransaction = 1
 ROLLBACK TRANSACTION	 
END
ELSE IF @tTransaction = 1
 COMMIT TRANSACTION	

RETURN 

GO

CREATE PROCEDURE clCreateUnpostedTrxEarnType
(
	@SERIES INT,
	@PERIODID INT,
	@YEAR INT,
	@PTRATIO FLOAT,
	@CPTYPE INT,
	@CPTRXTYPE INT,
	@StartDate DATETIME,
	@EndDate DATETIME,
	@O_iErrorState INT = NULL OUTPUT 
)
AS

SET NOCOUNT ON

DECLARE @Count INT,
	@tTransaction TINYINT,
 	@iError INT

SET @O_iErrorState = 0
	
IF @@trancount = 0
	BEGIN
		SET @tTransaction = 1
		BEGIN TRANSACTION
	END
	
IF @SERIES IS NULL OR @PERIODID IS NULL OR @YEAR IS NULL OR @PTRATIO IS NULL OR @CPTYPE IS NULL OR @CPTRXTYPE IS NULL OR @Startdate IS NULL OR @Enddate IS NULL
	BEGIN
		SET @iError = 20018
		GOTO Error
	END

DELETE
FROM	CL40104
WHERE	SYEAR = @YEAR
	AND SMONTH = @PERIODID
	AND CPTYPE = @CPTYPE
	AND CPTRXTYPE = @CPTRXTYPE

SET @iError = @@ERROR

IF @iError <> 0
	GOTO Error
	
INSERT		CL40104
		(
			SYEAR,
			SMONTH,
			CUSTNMBR,
			CPTYPE,
			CPTRXTYPE,
			CPTRXSOURCE,
			ORTRXSRC,
			COMMENT_4,
			CLFUNCSALAMNT,
			PTRATIO,
			CPPOINTS,
			CPPOSTED
		)
SELECT		@YEAR,
		@PERIODID,
		f.CUSTNMBR,
		@CPTYPE,
		@CPTRXTYPE,
		dbo.clGetNextTrxSource(@CPTYPE, @CPTRXTYPE, @PERIODID, @YEAR),
		'SOP',
		CONVERT(VARCHAR, CURRENT_TIMESTAMP, 101),
		ROUND(f.XTNDPRCE_3 - f.XTNDPRCE_4, 0),
		@PTRATIO,
		@PTRATIO * ROUND(f.XTNDPRCE_3 - f.XTNDPRCE_4, 0),
		0
FROM		(
			SELECT		b.CUSTNMBR
					SUM(CASE WHEN a.SOPTYPE = 3 THEN ISNULL(a.XTNDPRCE, 0) ELSE 0) END AS XTNDPRCE_3,
					SUM(CASE WHEN a.SOPTYPE = 4 THEN ISNULL(a.XTNDPRCE, 0) ELSE 0) END AS XTNDPRCE_4,
			FROM		SOP30300 AS a 
			INNER JOIN	SOP30200 AS b ON b.SOPTYPE = a.SOPTYPE AND b.SOPNUMBE = a.SOPNUMBE
			INNER JOIN	CL40102 AS c ON c.CUSTNMBR = b.CUSTNMBR AND c.REGFLAG = 1
			INNER JOIN	IV00101 AS d ON d.ITEMNMBR = a.ITEMNMBR
			INNER JOIN	EXT00103 AS e ON e.PT_UD_Key = d.ITMCLSCD AND e.PT_Window_ID = 'ITEMCLASSPRIV'
			WHERE		b.GLPOSTDT >= c.REGDATE
					AND a.PRCLEVEL NOT LIKE 'PRIV%'
					AND a.SOPTYPE = 3
					AND b.CUSTNMBR IS NOT NULL
					AND b.VOIDSTTS = 0
					AND b.GLPOSTDT BETWEEN @Startdate AND @Enddate
			GROUP BY	b.CUSTNMBR
		) AS f

SET @iError = @@ERROR

IF @iError <> 0
	GOTO Error

SELECT	@Count = COUNT(*)
FROM	CL40104
WHERE	SYEAR = @YEAR
	AND SMONTH = @PERIODID
	AND CPTYPE = @CPTYPE
	AND CPTRXTYPE = @CPTRXTYPE
	
SET @iError = @@ERROR

IF @iError <> 0
	GOTO Error
	
UPDATE	CL40106
SET	CL_Trx_Source_ID = CL_Trx_Source_ID + @Count
WHERE	CPTYPE = @CPTYPE
	AND CPTRXTYPE = @CPTRXTYPE

SET @iError = @@ERROR

IF @iError <> 0
	GOTO Error

Error:

SET @O_iErrorState = @iError

IF @O_iErrorState <> 0
	IF @tTransaction = 1
		ROLLBACK TRANSACTION	 
ELSE
	IF @tTransaction = 1
		COMMIT TRANSACTION	
GO

quote:

---

Originally posted by Peso

No difference. I prefer CURRENT_TIMESTAMP because there are less paranthesis to keep track of.

---

CREATE FUNCTION  clGetNextTrxSource 
(
	@CPTYPE INT,
	@CPTRXTYPE INT,
	@PERIODID INT,
	@YEAR INT
)
RETURNS NVARCHAR(13)
AS
/*
pvasu 5/4/07	Initial Implementation
	To Get Next CL Transaction Source
*/
BEGIN

DECLARE @NextTrxSource nvarchar(13),
	@CLTrxSourceID int,
	@CLTRXSRCIDABR nvarchar(3),
	@i int,
	@string char(10)

	SELECT @CLTrxSourceID=CL_Trx_Source_ID,@CLTRXSRCIDABR=CLTRXSRCIDABR FROM CL40106 WHERE
	CPTYPE=@CPTYPE AND CPTRXTYPE=@CPTRXTYPE  
	if @CPTRXTYPE=1
		SELECT @CLTrxSourceID=@CLTrxSourceID + 1 FROM CL40104 WHERE SYEAR=@YEAR AND SMONTH=@PERIODID 
		AND CPTYPE=@CPTYPE AND CPTRXTYPE=@CPTRXTYPE
	else
		SELECT @CLTrxSourceID=count(1) + @CLTrxSourceID FROM CL40104 WHERE SYEAR=@YEAR AND SMONTH=@PERIODID 
		AND CPTYPE=@CPTYPE AND CPTRXTYPE=@CPTRXTYPE 

	SELECT @i=len(LTRIM(RTRIM(STR(@CLTrxSourceID))))
	SELECT @string=
	CASE @i
		WHEN 1 THEN '000000000'
		WHEN 2 THEN '00000000'
		WHEN 3 THEN '0000000'
		WHEN 4 THEN '000000'
		WHEN 5 THEN '00000'
		WHEN 6 THEN '0000'
		WHEN 7 THEN '000'
		WHEN 8 THEN '00'
		WHEN 9 THEN '0'
		WHEN 10 THEN ''
	END
	
	SELECT @NextTrxSource=@CLTRXSRCIDABR + LTRIM(RTRIM(@string)) + LTRIM(RTRIM(STR(@CLTrxSourceID)))
	RETURN(@NextTrxSource)
END

GO

	IF @CPTRXTYPE = 1
		SELECT	@CLTrxSourceID = @CLTrxSourceID + 1
		FROM	CL40104
		WHERE	SYEAR = @YEAR
			AND SMONTH = @PERIODID 
			AND CPTYPE = @CPTYPE
			AND CPTRXTYPE = @CPTRXTYPE
	ELSE
		SELECT	@CLTrxSourceID = @CLTrxSourceID + COUNT(1)
		FROM	CL40104
		WHERE	SYEAR = @YEAR
			AND SMONTH = @PERIODID
			AND CPTYPE = @CPTYPE
			AND CPTRXTYPE = @CPTRXTYPE

IF @CPTRXTYPE = 1
		SELECT	@CLTrxSourceID = @CLTrxSourceID + 1
	ELSE
		SELECT	@CLTrxSourceID = @CLTrxSourceID + COUNT(1)
		FROM	CL40104
		WHERE	SYEAR = @YEAR
			AND SMONTH = @PERIODID
			AND CPTYPE = @CPTYPE
			AND CPTRXTYPE = @CPTRXTYPE

UPDATE	CL40106
SET	CL_Trx_Source_ID = CL_Trx_Source_ID + 1
WHERE	CPTYPE = @CPTYPE
	AND CPTRXTYPE = @CPTRXTYPE

UPDATE	CL40106
SET	CL_Trx_Source_ID = CL_Trx_Source_ID + @Count
WHERE	CPTYPE = @CPTYPE
	AND CPTRXTYPE = @CPTRXTYPE