Sep 5, 2000
Hi
I have a problem in validating the user ..if the user is not a valid user
it shouldn't execute the stored procedure ..but it should quit the SP and
return a message to the frontend application.The SP look something like this..
when I execute the below SP I get the following ERROR
Server: Msg 3902, Level 16, State 1, Line 0
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Not a Valid USER
Server: Msg 3902, Level 16, State 1, Procedure test, Line 118
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Not a Valid USER
HERE IS THE SP For REFERENCE
CREATE PROCEDURE test
@userid varchar(25),
@revenue_date datetime,
@veropt numeric(25)
AS
set nocount on
DECLARE @version NUMERIC(25)
DECLARE @stg_version NUMERIC(25)
DECLARE @record_count NUMERIC(25)
DECLARE @temp_Count NUMERIC(25)
DECLARE @Stg_Count NUMERIC(25)
DECLARE @Count_Temp_Exep NUMERIC(25)
DECLARE @Temp VARCHAR(20)
DECLARE C1 CURSOR FOR
SELECT userid FROM CEB_REVENUE_RECOG_USERID
OPEN C1
FETCH NEXT FROM C1 INTO @Temp
WHILE @@fetch_status = 0
BEGIN
IF @Temp <> @Userid
PRINT 'Not a Valid USER'
---IF THE USER IS NOT A VALID USER IT SHOULD RETURN A MESSAGE TO A FRONTEND APPLICATION SAYING THAT HE IS NOT A VALID USER
--AND IT SHOULD NOT EXECUTE FURTHER CODE
ELSE
BEGIN TRANSACTION one
if exists (select * from sysobjects where id = object_id(N'[dbo].[CEB_REVENUE_RECOG_TEMP]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[CEB_REVENUE_RECOG_TEMP]
CREATE TABLE [dbo].[CEB_REVENUE_RECOG_TEMP] (
[VERSION] [numeric](15, 0) NOT NULL ,
[STAGING_VERSION] [numeric](15, 0) NOT NULL,
[OPPORTUNITY_ID] [varchar] (15) NULL ,
[CURRENT_GRADE] [varchar] (15) NULL ,
[AMOUNT] [real] NULL ,
[NEGOTIATED_AMOUNT] [real] NULL ,
[ANTICIPATED_AMOUNT] [real] NULL ,
[START_DATE] [datetime] NULL ,
[END_DATE] [datetime] NULL ,
[REC_STATUS] [varchar] (1) NULL,
[PRIOR_YTD] [real] NULL ,
[MTD_REVENUE] [real] NULL ,
[YTD_REVENUE] [real] NULL ,
[DEFERRED] [real] NULL ,
[DAY1] [numeric](5,0) NULL,
[DAY2] [numeric](5,0) NULL,
[DAY3] [numeric](5,0) NULL,
[percentage] [numeric](5,0) NULL,
[YTDDAYS] [numeric](5,0) NULL
)
SELECT @version = max(version) from CEB_REVENUE_RECOG_AUDIT
SELECT @Stg_Count = Count(*) FROM CEB_STG_REVENUE_RECOG
IF @version IS NULL
BEGIN
SELECT @version = 1
END
ELSE
BEGIN
SELECT @version = @version +1
END
IF @veropt = 0
BEGIN
SELECT @stg_version = max(staging_version) from CEB_STG_REVENUE_RECOG_AUDIT
IF @stg_version IS NULL
BEGIN
SELECT @stg_version = 1
END
ELSE
BEGIN
SELECT @stg_version = @stg_version +1
END
INSERT INTO CEB_REVENUE_RECOG_AUDIT
(date, USERID, version, staging_version, starttime,REVENUE_DATE,STATUS)
values
(getdate(), @USERID, @version,@stg_version,getdate(),@revenue_date,'P')
INSERT INTO CEB_STG_REVENUE_RECOG_AUDIT (date, USERID, staging_version, starttime) values(getdate(), @USERID, @stg_version, getdate())
---DELETE FROM CEB_STG_REVENUE_RECOG_CEB
---EXEC 'Stored Procedure of Carlos'
SELECT @record_Count = count(*) From CEB_STG_REVENUE_RECOG
INSERT INTO CEB_STG_REVENUE_RECOG_HISTORICAL
SELECT @stg_version,OPPORTUNITY_ID,OPPORTUNITY_NAME, INSTITUTION_ID, INSTITUTION_NAME, ELVIS_ID,PROGRAM_ID,PRODUCT, OPPORTUNITY_TYPE,
OPPORTUNITY_STATUS, CURRENT_GRADE,MEMBERSHIP_YEAR,NEGOTIATED_AMOUNT,NE GOTIATED_DATE,ANTICIPATED_AMOUNT,START_DATE,
END_DATE,RENEWAL_MONTH,JOIN_DATE,MEMBERSHIP_PERIOD ,ORIG_NEG_AMOUNT,PREV_NEG_AMOUNT,EST_AMOUNT, PR_SAL_LAST_NAME,
PR_SAL_FST_NAME,PR_SAL_MID_NAME,CREATION_TIMESTAMP ,LAST_UPD_BY,REC_STATUS
FROM CEB_STG_REVENUE_RECOG
INSERT INTO CEB_REVENUE_RECOG_TEMP(VERSION,STAGING_VERSION, OPPORTUNITY_ID,CURRENT_GRADE,NEGOTIATED_AMOUNT, ANTICIPATED_AMOUNT,START_DATE,END_DATE)
SELECT @version,@stg_version, OPPORTUNITY_ID,CURRENT_GRADE,NEGOTIATED_AMOUNT, ANTICIPATED_AMOUNT,START_DATE, END_DATE
FROM CEB_STG_REVENUE_RECOG
--DELETE FROM CEB_STG_REVENUE_RECOG_CEB
UPDATE CEB_STG_REVENUE_RECOG_AUDIT SET ENDTIME = getdate(), RECORD_COUNT = @RECORD_COUNT
WHERE STAGING_VERSION = @stg_version
END
ELSE
BEGIN
INSERT INTO CEB_REVENUE_RECOG_AUDIT
(date, USERID, version, staging_version, starttime,REVENUE_DATE,STATUS)
values (getdate(), @USERID, @version,@stg_version,getdate(),@revenue_date,'P')
INSERT INTO CEB_REVENUE_RECOG_TEMP(VERSION, STAGING_VERSION,OPPORTUNITY_ID,CURRENT_GRADE, NEGOTIATED_AMOUNT,
ANTICIPATED_AMOUNT,START_DATE,END_DATE)
SELECT @version,@stg_version, OPPORTUNITY_ID,CURRENT_GRADE,NEGOTIATED_AMOUNT, ANTICIPATED_AMOUNT,START_DATE, END_DATE
FROM CEB_STG_REVENUE_RECOG_HISTORICAL WHERE STAGING_VERSION = @veropt
SELECT @record_count = COUNT(*) FROM CEB_REVENUE_RECOG_TEMP
UPDATE CEB_REVENUE_RECOG_AUDIT set ENDTIME = getdate(), STATUS = 'C' where VERSION = @version
END
COMMIT TRANSACTION one
BEGIN TRANSACTION two
UPDATE CEB_REVENUE_RECOG_TEMP SET PRIOR_YTD = 0, YTD_REVENUE=0, MTD_REVENUE=0, AMOUNT = 0, deferred=0
UPDATE CEB_REVENUE_RECOG_TEMP SET REC_STATUS = 'B' WHERE LEN(END_DATE)>=10
UPDATE CEB_REVENUE_RECOG_TEMP SET REC_STATUS = 'P', END_DATE = start_date + 364 WHERE (LEN(END_DATE) = 0 OR END_DATE IS NULL)
UPDATE CEB_REVENUE_RECOG_TEMP SET CEB_REVENUE_RECOG_TEMP.PERCENTAGE = CEB_REVENUE_RECOG_GRADES.percentage
from CEB_REVENUE_RECOG_TEMP INNER JOIN CEB_REVENUE_RECOG_GRADES
ON CEB_REVENUE_RECOG_TEMP.CURRENT_GRADE = CEB_REVENUE_RECOG_GRADES.GRADE
UPDATE CEB_REVENUE_RECOG_TEMP SET CEB_REVENUE_RECOG_TEMP.PERCENTAGE = 100
WHERE CEB_REVENUE_RECOG_TEMP.percentage IS NULL
UPDATE CEB_REVENUE_RECOG_TEMP SET amount = CEB_REVENUE_RECOG_TEMP. ANTICIPATED_AMOUNT * percentage / 100
from CEB_REVENUE_RECOG_TEMP WHERE REC_STATUS = 'P'
UPDATE CEB_REVENUE_RECOG_TEMP SET amount = negotiated_AMOUNT
from CEB_REVENUE_RECOG_TEMP WHERE REC_STATUS = 'B'
/*--TODAY'S ADDITION--*/
INSERT INTO CEB_REVENUE_RECOG_EXCEPTION
SELECT VERSION,STAGING_VERSION, OPPORTUNITY_ID,CURRENT_GRADE, NEGOTIATED_AMOUNT,ANTICIPATED_AMOUNT,START_DATE,EN D_DATE,'06',REC_STATUS,AMOUNT
FROM CEB_REVENUE_RECOG_TEMP WHERE OPPORTUNITY_ID IS NULL OR OPPORTUNITY_ID = ' '
DELETE FROM CEB_REVENUE_RECOG_TEMP WHERE OPPORTUNITY_ID IS NULL OR OPPORTUNITY_ID = ' '
INSERT INTO CEB_REVENUE_RECOG_EXCEPTION
SELECT VERSION,STAGING_VERSION, OPPORTUNITY_ID, CURRENT_GRADE, NEGOTIATED_AMOUNT, ANTICIPATED_AMOUNT, START_DATE, END_DATE, '02',REC_STATUS,AMOUNT
FROM CEB_REVENUE_RECOG_TEMP WHERE Start_date > @REVENUE_DATE
DELETE FROM CEB_REVENUE_RECOG_TEMP WHERE Start_date >@REVENUE_DATE
INSERT INTO CEB_REVENUE_RECOG_EXCEPTION
SELECT VERSION,STAGING_VERSION, OPPORTUNITY_ID,CURRENT_GRADE, NEGOTIATED_AMOUNT,ANTICIPATED_AMOUNT,START_DATE,EN D_DATE,'03',REC_STATUS,AMOUNT
FROM CEB_REVENUE_RECOG_TEMP WHERE Start_date IS NULL
DELETE FROM CEB_REVENUE_RECOG_TEMP WHERE Start_date IS NULL
INSERT INTO CEB_REVENUE_RECOG_EXCEPTION
SELECT VERSION,STAGING_VERSION,OPPORTUNITY_ID,CURRENT_GRA DE,NEGOTIATED_AMOUNT,ANTICIPATED_AMOUNT,START_DATE ,END_DATE, '01',REC_STATUS,AMOUNT
FROM CEB_REVENUE_RECOG_TEMP WHERE Start_date > End_Date
DELETE FROM CEB_REVENUE_RECOG_TEMP WHERE Start_date > End_Date
INSERT INTO CEB_REVENUE_RECOG_EXCEPTION
SELECT VERSION,STAGING_VERSION,OPPORTUNITY_ID,CURRENT_GRA DE,NEGOTIATED_AMOUNT,ANTICIPATED_AMOUNT,START_DATE ,END_DATE, '04',REC_STATUS,AMOUNT
FROM CEB_REVENUE_RECOG_TEMP WHERE AMOUNT < 0
DELETE FROM CEB_REVENUE_RECOG_TEMP WHERE AMOUNT < 0
INSERT INTO CEB_REVENUE_RECOG_YTD(OPPORTUNITY_ID, MYTD_REVENUE, YTDDAYS)
SELECT OPPORTUNITY_ID, 0 ,0
FROM CEB_REVENUE_RECOG_TEMP
WHERE OPPORTUNITY_ID
NOT IN (SELECT OPPORTUNITY_ID FROM CEB_REVENUE_RECOG_YTD)
UPDATE CEB_REVENUE_RECOG_TEMP SET PRIOR_YTD = CEB_REVENUE_RECOG_YTD.MYTD_REVENUE, YTDDAYS = CEB_REVENUE_RECOG_YTD.YTDDAYS
FROM CEB_REVENUE_RECOG_TEMP inner join CEB_REVENUE_RECOG_YTD
ON CEB_REVENUE_RECOG_TEMP.OPPORTUNITY_ID = CEB_REVENUE_RECOG_YTD.OPPORTUNITY_ID
UPDATE CEB_REVENUE_RECOG_TEMP SET day1 = datediff(day,start_date,@revenue_date)+1 - YTDDAYS, day2 = datediff(day,start_date,END_DATE) + 1 - YTDDAYS
UPDATE CEB_REVENUE_RECOG_TEMP SET DAY3 = DAY1
UPDATE CEB_REVENUE_RECOG_TEMP SET DAY3 = DAY2 WHERE DAY1 > DAY2
INSERT INTO CEB_REVENUE_RECOG_EXCEPTION
SELECT VERSION,STAGING_VERSION,OPPORTUNITY_ID,CURRENT_GRA DE,NEGOTIATED_AMOUNT,ANTICIPATED_AMOUNT,START_DATE ,END_DATE, '05',REC_STATUS,AMOUNT
FROM CEB_REVENUE_RECOG_TEMP WHERE day2 = 0
DELETE FROM CEB_REVENUE_RECOG_TEMP WHERE day2 = 0
UPDATE CEB_REVENUE_RECOG_TEMP SET mtd_revenue = Day3 * (amount - PRIOR_YTD) / day2
UPDATE CEB_REVENUE_RECOG_TEMP SET mtd_revenue = amount - prior_ytd WHERE amount < prior_ytd
INSERT INTO CEB_REVENUE_RECOG_TEMP(VERSION,STAGING_VERSION,OPP ORTUNITY_ID,CURRENT_GRADE,MTD_REVENUE, NEGOTIATED_AMOUNT, ANTICIPATED_AMOUNT,
START_DATE,END_DATE,REC_STATUS,AMOUNT,YTDDAYS,DAY2 ,PRIOR_YTD)
SELECT @version, @stg_version,CEB_REVENUE_RECOG_EXCEPTION.OPPORTUNI TY_ID,CURRENT_GRADE, (CEB_REVENUE_RECOG_EXCEPTION.AMOUNT-CEB_REVENUE_RECOG_YTD.MYTD_REVENUE),
NEGOTIATED_AMOUNT,ANTICIPATED_AMOUNT,START_DATE, END_DATE,REC_STATUS,AMOUNT,CEB_REVENUE_RECOG_YTD.Y TDDAYS,0,MYTD_REVENUE
FROM CEB_REVENUE_RECOG_EXCEPTION INNER JOIN CEB_REVENUE_RECOG_YTD
ON CEB_REVENUE_RECOG_EXCEPTION.OPPORTUNITY_ID = CEB_REVENUE_RECOG_YTD.OPPORTUNITY_ID
WHERE CEB_REVENUE_RECOG_EXCEPTION.VERSION = @version AND CODE = '05' AND
(CEB_REVENUE_RECOG_YTD.MYTD_REVENUE - CEB_REVENUE_RECOG_EXCEPTION.AMOUNT) <> 0
SELECT @Temp_Count = COUNT(*) FROM CEB_REVENUE_RECOG_TEMP
/*--TODAYS ADDITION--*/
UPDATE CEB_REVENUE_RECOG_TEMP SET NEGOTIATED_AMOUNT = 0, ANTICIPATED_AMOUNT = 0 WHERE NEGOTIATED_AMOUNT IS NULL AND ANTICIPATED_AMOUNT IS NULL
UPDATE CEB_REVENUE_RECOG_TEMP SET ytd_revenue = mtd_revenue + PRIOR_YTD, DEFERRED = AMOUNT - mtd_revenue - PRIOR_YTD
UPDATE CEB_REVENUE_RECOG_TEMP SET DEFERRED = 0 WHERE YTD_REVENUE> =amount
INSERT INTO CEB_REVENUE_RECOG_HISTORICAL(VERSION,STAGING_VERSI ON,OPPORTUNITY_ID,CURRENT_GRADE,AMOUNT,NEGOTIATED_ AMOUNT,ANTICIPATED_AMOUNT,
START_DATE,END_DATE,REC_STATUS,REVENUE_DATE,PRIOR_ YTD,MTD_REVENUE,YTD_REVENUE,DEFERRED,REMAINING_DAY S,DAYS_IN_PERIOD,PERCENTAGE,ADJUSTMENT_FLAG)
SELECT VERSION,STAGING_VERSION,OPPORTUNITY_ID,CURRENT_GRA DE, AMOUNT,NEGOTIATED_AMOUNT, ANTICIPATED_AMOUNT,START_DATE,END_DATE,
REC_STATUS,@revenue_date,PRIOR_YTD, MTD_REVENUE, YTD_REVENUE,DEFERRED,DAY2,0,100,'A'
FROM CEB_REVENUE_RECOG_TEMP
--DELETE FROM CEB_REVENUE_RECOG_TEMP
UPDATE CEB_REVENUE_RECOG_AUDIT SET ENDTIME = getdate(), RECORD_COUNT = @RECORD_COUNT, STATUS = 'C' WHERE VERSION = @version
UPDATE CEB_REVENUE_RECOG_YTD SET MYTD_REVENUE = MYTD_REVENUE + CEB_REVENUE_RECOG_TEMP.mtd_revenue,
CEB_REVENUE_RECOG_YTD.YTDDAYS = CEB_REVENUE_RECOG_YTD.YTDDAYS + CEB_REVENUE_RECOG_TEMP.DAY3
FROM CEB_REVENUE_RECOG_YTD INNER JOIN CEB_REVENUE_RECOG_TEMP
ON CEB_REVENUE_RECOG_YTD.OPPORTUNITY_ID = CEB_REVENUE_RECOG_TEMP.OPPORTUNITY_ID
WHERE CEB_REVENUE_RECOG_TEMP.VERSION = @version
/*--INSERTING RECORD INTO EXCEPTION SUMMARY----*/
/*--TODAYS ADDITION--*/
INSERT INTO CEB_REVENUE_RECOG_EXCEPTION_SUMMARY VALUES(@version,'00','Total Records in Temp',@Temp_Count)
INSERT INTO CEB_REVENUE_RECOG_EXCEPTION_SUMMARY
SELECT @version,CEB_REVENUE_RECOG_EXCEPTION.CODE,DESCRIPT ION ,COUNT(CEB_REVENUE_RECOG_EXCEPTION.CODE) as RECORD_COUNT
FROM CEB_REVENUE_RECOG_EXCEPTION INNER JOIN CEB_REVENUE_RECOG_EXCEPTION_CODES ON
CEB_REVENUE_RECOG_EXCEPTION.CODE = CEB_REVENUE_RECOG_EXCEPTION_CODES.CODE
WHERE VERSION = @version GROUP BY CEB_REVENUE_RECOG_EXCEPTION.CODE,DESCRIPTION
INSERT INTO CEB_REVENUE_RECOG_EXCEPTION_SUMMARY VALUES(@version,'91','Total Records in Staging',@Stg_Count)
SELECT @Count_Temp_Exep = SUM(record_count) from CEB_REVENUE_RECOG_EXCEPTION_SUMMARY
where code < '90' and VERSION = @version
INSERT INTO CEB_REVENUE_RECOG_EXCEPTION_SUMMARY VALUES(@version,'92','Record Count',@Count_Temp_Exep)
INSERT INTO CEB_REVENUE_RECOG_CLOSED_PERIOD VALUES(@revenue_date)
UPDATE CEB_REVENUE_RECOG_AUDIT SET Status = 'X' WHERE VERSION = @version
COMMIT TRANSACTION two
FETCH NEXT from C1 INTO @Temp
end
CLOSE C1
DEALLOCATE C1
THANKS
VENU
View 2 Replies
View Related
