|
oracle_corrgi
Yak Posting Veteran
98 Posts |
 Posted - 2007-01-26 : 07:10:44
|
| hii WAS ABLE TO COMPLIE this proc in the morning but droped unknowningly now iam unable to complieall the column 'empr_id' exists when i run this speratly the select statement it works CREATE PROCEDURE search_trans(@tpa_id varchar(6),@empr_id varchar(18) = NULL,@empe_key int = NULL,@service_start_dte datetime = NULL,@user_id_key int,@acct_type_cde varchar(4) = NULL,@batch_count int = 20)ASBEGIN SET NOCOUNT ON DECLARE @total_errors tinyint, @TXN_PROCESS_CDE_VALID smallint, @DuplicateFlag int, @user_type_cde smallint, @USER_TYPE_MBI smallint, @USER_TYPE_SYSTEM smallint 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 AND FT.[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] FROM dbo.[USER_EMPLOYER] WHERE [tpa_id] = @tpa_id AND user_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') AND T.[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) ENDGOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGOerror:Server: Msg 207, Level 16, State 1, Procedure usp_TXN_SearchDupFlexTxn, Line 70Invalid column name 'is_dup'.Server: Msg 207, Level 16, State 1, Procedure usp_TXN_SearchDupFlexTxn, Line 70Invalid column name 'empr_id'.Server: Msg 207, Level 16, State 1, Procedure usp_TXN_SearchDupFlexTxn, Line 70Invalid column name 'empr_id'.Server: Msg 207, Level 16, State 1, Procedure usp_TXN_SearchDupFlexTxn, Line 139Invalid column name 'is_dup'.krmm |
|
proc help