Unable To Complie

hi
i WAS ABLE TO COMPLIE this proc in the morning but droped unknowningly now iam unable to complie
all the column 'empr_id' exists when i run this speratly the select statement it works


CREATE PROCEDURE search_trans
(
@tpa_idvarchar(6),
@empr_idvarchar(18) = NULL,
@empe_keyint = NULL,
@service_start_dtedatetime= NULL,
@user_id_keyint,
@acct_type_cdevarchar(4) = NULL,
@batch_countint = 20
)
AS
BEGIN

SET NOCOUNT ON

DECLARE@total_errorstinyint,
@TXN_PROCESS_CDE_VALIDsmallint,
@DuplicateFlagint,
@user_type_cdesmallint,
@USER_TYPE_MBIsmallint,
@USER_TYPE_SYSTEMsmallint

SELECT@total_errors = 0,
@TXN_PROCESS_CDE_VALID= 1,
@DuplicateFlag=16,
@user_type_cde= dbo.udf_GetUserTypeCode(@user_id_key),
@USER_TYPE_MBI= 1,
@USER_TYPE_SYSTEM= 16,
@tpa_id = dbo.udf_IsDefaultString(@tpa_id, NULL),
@empr_id = dbo.udf_IsDefaultString(@empr_id, NULL),
@empe_key = dbo.udf_IsDefaultInt32(@empe_key, NULL),
@acct_type_cde = dbo.udf_IsDefaultString(@acct_type_cde, NULL),
@service_start_dte = dbo.udf_IsDefaultDateTime(@service_start_dte, NULL)


IF @tpa_id IS NULL
BEGIN
EXECUTE @total_errors = dbo.usp_ERR_InsertUserError @error_cde = 18015, -- TODO error code
@error_cnt = @total_errors
END

IF @total_errors > 0
BEGIN
GOTO EXITSP
END

DECLARE@TEMP_DUPLICATES TABLE
(
tpa_id VARCHAR(6) NOT NULL,
empr_id VARCHAR(18) NOT NULL,
empe_key INT NOT NULL,
service_start_dte datetime, -- TODO finish this declaration,
service_end_dte datetime,
cardholder_name VARCHAR(2000),
txn_amt_orig decimal(19,4),
approved_amt int,
denied_amt int,
merchant_name VARCHAR(50),
MTC char(4),
notes varchar(255),
txn_options smallint
)
INSERT INTO @TEMP_DUPLICATES
(
tpa_id,
empr_id,
empe_key,
service_start_dte,
service_end_dte,
cardholder_name,
txn_amt_orig,
--approved_amt
denied_amt,
merchant_name,
MTC,
notes,
is_dup
)
SELECT TOP 20 -- TODO latest entered @batch_count value duplicate records
FT.tpa_id,
FT.empr_id,
FT.empe_key,
FT.service_start_dte,
FT.service_end_dte,
(e.first_name+ ', '+ e.last_name ) AS cardholder_name, -- TODO: construct name with

firstname,lastname with joining with employee/dep tables if needed should decrypt the data
--E.first_name if cardholder_name is accessed by employee then these two column can be ignored,
--E.last_name,
FT.txn_amt_orig,
--approved_amt, -- --Need to to put condition to how to get approved amt,
FT.denied_fee, -- This column considered for denied_amt as this column doesnot exists
FT.merch_name,
FT.merch_id, -- MTC column on UI
FT.notes,
is_dup = '1'
FROM dbo.[vw_PDB_FLEX_TXN] FT (NOLOCK) INNER JOIN
dbo.[EMPLOYEE] E ON (E.[tpa_id] = FT.[tpa_id] AND E.[empr_id] = FT.[empr_id] AND E.[empe_key] =

FT.[empe_key])
inner join dbo.[TPA] T ON (E.[tpa_id] = T.[tpa_id]) INNER JOIN
dbo.[EMPLOYER] ER on (E.[tpa_id] = ER.[tpa_id] and E.[empr_id] = ER.[empr_id])
WHERE -- Duplicate records
FT.origin_cde >= 50 -- manual transactions
AND FT.txn_adjud_cde = 1 -- approved transactions
AND FT.txn_cde in ('10')
AND FT.txn_process_cde = @TXN_PROCESS_CDE_VALID -- valid
AND FT.[tpa_id] = @tpa_id
AND FT.[service_start_dte] = @service_start_dte
ANDFT.[etxn_seq_num] = 1
AND ((@acct_type_cde IS NULL) OR (@acct_type_cde IS NOT NULL AND FT.[acct_type_cde] =

@acct_type_cde))
AND (FT.txn_options & @DuplicateFlag = @DuplicateFlag)
AND FT.reimb_key is null -- not reimbused yet
-- Employer Id optional
AND ((@empr_id IS NULL) OR (@empr_id IS NOT NULL AND T.[empr_id] = @empr_id))
-- User has access to employer
AND(@user_type_cde IN (@USER_TYPE_MBI, @USER_TYPE_SYSTEM)
OR (@user_type_cde NOT IN (@USER_TYPE_MBI, @USER_TYPE_SYSTEM)
AND T.[empr_id] IN (SELECT[empr_id]
FROMdbo.[USER_EMPLOYER]
WHERE[tpa_id] = @tpa_id
ANDuser_id_key = @user_id_key)))
AND ((@empe_key IS NULL) OR (@empe_key IS NOT NULL AND FT.empe_key = @empe_key))
-- TPA is not active (All Status Except Perm Inactive)
AND T.[tpa_status_cde] < 4
-- Employer is active (All Status Except Perm Inactive)
AND ER.[empr_status_cde] < 4
ORDER BY FT.txn_dte DESC
--UNION ALL

-- join @TEMP_DUPLICATES table with dbo.[vw_PDB_FLEX_TXN]
-- to find out for each of above 20 records, possible duplicate claims
-- investigate more to make join/union/insert into -- the final goal is this SP should return all 'y' and 'n' data

EFFICIENTLY (NO CURSORS IN SP)


INSERT INTO @TEMP_DUPLICATES
(
tpa_id,
empr_id,
empe_key,
service_start_dte,
service_end_dte,
cardholder_name,
txn_amt_orig,
--approved_amt,
denied_amt,
merchant_name,
MTC,
notes,
is_dup
)
SELECT t.tpa_id,
t.empr_id,
t.empe_key,
t.service_start_dte,
t.service_end_dte,
(e.first_name+ ', '+ e.last_name ) AS cardholder_name, -- TODO: construct name with firstname,

lastname with joining with employee/dep tables
t.txn_amt_orig,
--t.approved_amt,
t.denied_fee,-- This column considered for denied_amt as this column doesnot exists
t.merch_name,
t.MTC,
t.notes ,
is_dup = '0'
FROM dbo.[vw_PDB_FLEX_TXN] T (NOLOCK) INNER JOIN @TEMP_DUPLICATES TP ON
(TP.tpa_id = T.tpa_id
AND TP.empr_id = T.empr_id
AND TP.empe_key = T.empe_key
AND TP.service_start_dte = T.service_start_dte
AND TP.[txn_amt_orig] = T.[txn_amt_orig]
)
INNER JOIN dbo.[EMPLOYEE] E ON E.empr_id = T.empr_id

WHERE (T.txn_options & @DuplicateFlag != @DuplicateFlag)
AND T.[tpa_id] = @tpa_id
AND T.[service_start_dte] = @service_start_dte
AND T.txn_cde in ('10','11','12')
ANDT.[etxn_seq_num] = 1
AND T.txn_process_cde = @TXN_PROCESS_CDE_VALID -- valid



-- NEED TO RETURN @TEMP_DUPLICATES ROWS
RETURN(0)

EXITSP:

IF @total_errors > 0
BEGIN
EXECUTE dbo.usp_ERR_GetUserErrors
END

RETURN (@total_errors)

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

error:
Server: Msg 207, Level 16, State 1, Procedure usp_TXN_SearchDupFlexTxn, Line 70
Invalid column name 'is_dup'.
Server: Msg 207, Level 16, State 1, Procedure usp_TXN_SearchDupFlexTxn, Line 70
Invalid column name 'empr_id'.
Server: Msg 207, Level 16, State 1, Procedure usp_TXN_SearchDupFlexTxn, Line 70
Invalid column name 'empr_id'.
Server: Msg 207, Level 16, State 1, Procedure usp_TXN_SearchDupFlexTxn, Line 139
Invalid column name 'is_dup'.

krmm