Sorry, what do you mean by "plans"?
The entired stored procedure is the following:
For first file:
USE hpmqa
go
IF OBJECT_ID('dbo.pr_get_RemitByStatus') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.pr_get_RemitByStatus
IF OBJECT_ID('dbo.pr_get_RemitByStatus') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.pr_get_RemitByStatus >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.pr_get_RemitByStatus >>>'
END
go
create procedure dbo.pr_get_RemitByStatus
@RemittanceStatusCd smallint,
@app_object varchar(25),
@app_object_version smallint,
@LANGUAGE_CD smallint
as
declare @proc_name varchar(30), -- this procedure name
@section_ident varchar(30), -- identifier for a section of code - used for debugging
@errmsg varchar(255), -- message text
@lang char(8), -- language name NULL if English otherwise "French"
@procerr tinyint, -- used to trap return codes from called procs
@err int -- used to capture the current @@error value
-- ---------------// initialize variables //----------------------
select @proc_name = "pr_template"
------------------// validate parameters //-------------------------
select @section_ident = "validate parameters"
--
if @LANGUAGE_CD = 1
select @lang = NULL
else
if @LANGUAGE_CD = 2
select @lang = "French"
else
begin
select @lang = NULL
select @err = 50000
goto error
end
--
if @app_object = NULL
begin
select @err = 50001
goto error
end
--
if @app_object_version = NULL
begin
select @err = 50002
goto error
end
select @section_ident = "mainline"
begin
select
r.REMITTANCE_TYPE_TABLE_ID,
r.REMITTANCE_TYPE_CD,
gd.cd as REMITTANCE_TYPE_CD_TEXT,
r.REMITTANCE_ID,
r.REMITTANCE_SEQUENCE_NO,
r.REMITTANCE_STATUS_TABLE_ID,
r.REMITTANCE_STATUS_CD,
gc.SHORT_DESC AS REMITTANCE_STATUS_TEXT,
r.REMITTED_AMT,
r.COLLECTED_AMT,
r.REMITTANCE_USED_IND,
r.BANK_ERROR_IND,
r.REMITTANCE_ERROR_IND,
r.BANK_BATCH_ID,
r.EXPIRED_IND,
r.PAID_DT
from
REMITTANCE r
LEFT JOIN generic_code gc
ON r.REMITTANCE_STATUS_CD = gc.NUMERIC_CD
AND gc.TABLE_ID = 5
AND gc.language_cd = @LANGUAGE_CD
LEFT JOIN generic_code gd
ON r.REMITTANCE_TYPE_CD = gd.NUMERIC_CD
AND gd.TABLE_ID = 6
AND gd.language_cd = @LANGUAGE_CD
where
REMITTANCE_STATUS_CD = @RemittanceStatusCD
ORDER BY r.REMITTANCE_ID
end
if (@@error != 0)
begin
select @err = @@error
goto error
end
return
-- ---------------// error handling //------------------------
--
error:
--
-- get the text of the message in the correct language
--
exec @procerr = sp_getmessage @err, @errmsg output, @lang
--
if @procerr = 0
raiserror @err, @errmsg
else
raiserror
@err "unknown error: %1! occurred in proc: %2!, section: %3!",
@err, @proc_name, @section_ident
--
return
go
EXEC sp_procxmode 'dbo.pr_get_RemitByStatus', 'anymode'
go
IF OBJECT_ID('dbo.pr_get_RemitByStatus') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.pr_get_RemitByStatus >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.pr_get_RemitByStatus >>>'
go
GRANT EXECUTE ON dbo.pr_get_RemitByStatus TO gp_developer
go
GRANT EXECUTE ON dbo.pr_get_RemitByStatus TO gp_user
go
---------------------------------------------------------------
second file (longer processing time):
USE hpmqa
go
IF OBJECT_ID('dbo.pr_get_BankRemittanceDetailByStatus') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.pr_get_BankRemittanceDetailByStatus
IF OBJECT_ID('dbo.pr_get_BankRemittanceDetailByStatus') IS NOT NULL
PRINT '<<< FAILED DROPPING PROCEDURE dbo.pr_get_BankRemittanceDetailByStatus >>>'
ELSE
PRINT '<<< DROPPED PROCEDURE dbo.pr_get_BankRemittanceDetailByStatus >>>'
END
go
create procedure dbo.pr_get_BankRemittanceDetailByStatus
@RemittanceStatusCd smallint,
@app_object varchar(12),
@app_object_version smallint,
@LANGUAGE_CD smallint
as
declare @proc_name varchar(30), -- this procedure name
@section_ident varchar(30), -- identifier for a section of code - used for debugging
@errmsg varchar(255), -- message text
@lang char(8), -- language name NULL if English otherwise "French"
@procerr tinyint, -- used to trap return codes from called procs
@err int -- used to capture the current @@error value
-- ---------------// initialize variables //----------------------
select @proc_name = "pr_template"
------------------// validate parameters //-------------------------
select @section_ident = "validate parameters"
--
if @LANGUAGE_CD = 1
select @lang = NULL
else
if @LANGUAGE_CD = 2
select @lang = "French"
else
begin
select @lang = NULL
select @err = 50000
goto error
end
--
if @app_object = NULL
begin
select @err = 50001
goto error
end
--
if @app_object_version = NULL
begin
select @err = 50002
goto error
end
select @section_ident = "mainline"
begin
SELECT
BRD.REMITTANCE_TYPE_TABLE_ID,
BRD.REMITTANCE_TYPE_CD,
BRD.REMITTANCE_ID,
BRD.REMITTANCE_SEQUENCE_NO,
BRD.LOB_TABLE_ID,
BRD.LINE_OF_BUSINESS_CD,
GC.SHORT_DESC as LINE_OF_BUSINESS_TEXT,
GC.CD as LOB_CODE,
LOB.LINE_OBJECT_CD as LINE_OBJECT_CODE,
BRD.REMITTANCE_UNIT_NO,
BRD.REMITTANCE_LINE_AMT,
BRD.CAPTURED_LINE_AMT,
BRD.DEFERRED_STATUS_TABLE_ID,
BRD.DEFERRED_STATUS_CD,
BRD.COUNTED_IND
FROM
BANK_REMITTANCE_DETAIL BRD
LEFT JOIN Line_of_Business LOB
ON BRD.LINE_OF_BUSINESS_CD = LOB.LINE_OF_BUSINESS_CD
AND LOB.LOB_TABLE_ID = 10
LEFT JOIN generic_code GC
ON BRD.LINE_OF_BUSINESS_CD = GC.NUMERIC_CD
AND GC.TABLE_ID = 10
AND GC.language_cd = 1
LEFT JOIN remittance rm
ON BRD.REMITTANCE_TYPE_TABLE_ID = RM.REMITTANCE_TYPE_TABLE_ID
AND BRD.REMITTANCE_TYPE_CD = RM.REMITTANCE_TYPE_CD
AND BRD.REMITTANCE_ID = RM.REMITTANCE_ID
AND BRD.REMITTANCE_SEQUENCE_NO = RM.REMITTANCE_SEQUENCE_NO
WHERE
RM.REMITTANCE_STATUS_CD = @RemittanceStatusCd
ORDER BY
BRD.REMITTANCE_TYPE_CD, BRD.REMITTANCE_ID
end
if (@@error != 0)
begin
select @err = @@error
goto error
end
return
-- ---------------// error handling //------------------------
--
-- some specific messages should be passed parameters
--
error:
--
-- get the text of the message in the correct language
--
exec @procerr = sp_getmessage @err, @errmsg output, @lang
--
if @procerr = 0
raiserror @err, @errmsg
else
raiserror
@err "unknown error: %1! occurred in proc: %2!, section: %3!",
@err, @proc_name, @section_ident
--
return
go
EXEC sp_procxmode 'dbo.pr_get_BankRemittanceDetailByStatus', 'anymode'
go
IF OBJECT_ID('dbo.pr_get_BankRemittanceDetailByStatus') IS NOT NULL
PRINT '<<< CREATED PROCEDURE dbo.pr_get_BankRemittanceDetailByStatus >>>'
ELSE
PRINT '<<< FAILED CREATING PROCEDURE dbo.pr_get_BankRemittanceDetailByStatus >>>'
go
GRANT EXECUTE ON dbo.pr_get_BankRemittanceDetailByStatus TO gp_developer
go
GRANT EXECUTE ON dbo.pr_get_BankRemittanceDetailByStatus TO gp_user
go
