Notes

IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'pr_grfp_subt_ref_ltr'
AND uid=user_id('flp'))
DROP PROC flp.pr_grfp_subt_ref_ltr

GO

/****************************************************************************
* Name: pr_grfp_subt_ref_ltr
*
* Author: AGOUD
*
* Purpose: Inserts reference letter letter path
*
* Modification Log:
* Date changed By Comments
* ------------ -------------- -------------------------------------
* 07/16/2007 Anand Goud Initial Creation
* 06/11/2012 Radhika Banda removed ref_ltr_cond_flag in update query as of DIS00089937 Reference Writer: redesign the upload process
*****************************************************************************/


CREATE PROCEDURE pr_grfp_subt_ref_ltr (
@personID numeric(10),
@applFiscalYear int,
@seqNum int,
@lastUpdatedUser varchar(8),
@isLogged char(1) = 'N'
)
AS
BEGIN
DECLARE @logID NUMERIC(10)
DECLARE @retCode INT

IF @@trancount = 0
SET CHAINED OFF

BEGIN TRAN

IF @isLogged = 'Y'
BEGIN
EXECUTE @logID = flp.pr_grfp_ins_chng_log 'grfp_ref', @lastUpdatedUser

IF @logID < 0
BEGIN
PRINT 'Error obtaining log ID'
ROLLBACK TRANSACTION
RETURN @logID
END

EXECUTE @retCode = flp.pr_grfp_log_ref @logID, 'UB', @personID, @applFiscalYear, @seqNum

IF @retCode < 0
BEGIN
PRINT 'Error logging grfp_ref (before update)'
ROLLBACK TRANSACTION
RETURN @retCode
END
END

UPDATE flp.grfp_ref
SET ref_ltr_subm_date = getDate(),
/* DIS00089937 Reference Writer: redesign the upload process : saving confidential fal will be done before submitting the letter*/
-- ref_ltr_cond_flag = @refIdentityConfidential,
last_updt_user = @lastUpdatedUser,
last_updt_tmsp = getDate(),
last_updt_pgm = 'grfp'
WHERE prsn_id = @personID
AND appl_fisc_yr = @applFiscalYear
AND seq_num = @seqNum


IF @@error != 0
BEGIN
PRINT 'Error inserting into flp.pr_grfp_subt_ref_ltr'
ROLLBACK TRANSACTION
RETURN -1
END


IF @isLogged = 'Y'
BEGIN
EXECUTE @retCode = flp.pr_grfp_log_ref @logID, 'UA', @personID, @applFiscalYear, @seqNum

IF @retCode < 0
BEGIN
PRINT 'Error loggin grfp_ref (after update)'
ROLLBACK TRANSACTION
RETURN @retCode
END
END


COMMIT TRAN
END

GO