1

USE glrGO/****** Object: StoredProcedure dbo.GLR_SP_UpdLeaseTransLI Script Date: 06/26/2009 17:37:55 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER OFFGO----------------------------------------------------------------- Drop the procedure if it exists---------------------------------------------------------------if exists ( select name from sysobjects where type = 'P' and name = 'GLR_SP_UpdLeaseTransLI' ) begin drop procedure GLR_SP_UpdLeaseTransLI print 'Original stored procedure, GLR_SP_UpdLeaseTransLI, dropped from the database' endelse begin print 'Stored procedure, GLR_SP_UpdLeaseTransLI, did not exist in the database' endgo ----------------------------------------------------------------------- Create the procedure---------------------------------------------------------------------create procedure dbo.GLR_SP_UpdLeaseTransLI/*********************************************************************************Stored Procedure:GLR_SP_UpdLeaseTransLIDate Created:07/24/2009 By L.HDescription:This stored procedure used to update LeaseTransLI .Parameters:Dependent on:MaintainEDSPOs.aspx* -------------------------------------------------------------------------------------* MODIFICATION LOG** Ver# DateDeveloperCSR Description* ---- ----------------------------------- -----------------------------------------* 1.007/24/2009 Levi Huang CR27524 Initial creation **************************************************************************************** Copyright (C)2009 Electronic Data Systems Corporation. All rights reserved. * ***************************************************************************************/( @P_LoadPONbr char(30) = null ,@P_ClientRequest varchar(50) = null ,@P_ClientRequestLineNumber int = null ,@P_ClientRequestPOSNbr smallint = null ,@p_ItemStatusDesc varchar(40) ,@p_StatusEffectiveDate datetime ,@p_LeaseScheduleSAK int ,@p_LeaseItemID bigint ,@p_LeaseTransactionCode char(3) ,@P_TransactionFDOC datetime ,@p_LIItemMonthlyPayment money = null ,@p_LIPaymentFrequencyNbr smallint = null ,@p_LILeaseRateFactor float = null ,@p_LIFDOC datetime ,@p_LILDOC datetime ,@p_LITerm smallint ,@p_LIFDOL datetime = null ,@p_LILDOL datetime = null ,@p_LITermExtendedZeroCost tinyint = null ,@p_LIReportDate datetime = null ,@p_LIComment varchar(200) = null ,@p_LastUpdtByTxt varchar(200) = null ,@p_TransactionFDOC_old datetime ,@p_LoadPONbr_old char(30) = null ,@p_ClientRequestPOSNbr_old smallint = null ,@p_ClientRequest_old varchar(50) = null ,@p_ClientRequestLineNumber_old int = null ,@p_errorMessage VARCHAR(500) = null OUTPUT )ASBEGIN BEGIN TRY IF @p_LeaseScheduleSAK IS NULL BEGIN SET @p_errorMessage = 'ERROR: LeaseScheduleSAK is NULL. GLR_SP_AddLeaseTransLI aborted.'RAISERROR(@p_errorMessage,11,1)END IF @p_ItemStatusDesc IS NULL BEGIN SET @p_errorMessage = 'ERROR: ItemStatusDesc is NULL. GLR_SP_AddLeaseTransLI aborted.'RAISERROR(@p_errorMessage,11,1)END IF @p_LIFDOC IS NULL BEGIN SET @p_errorMessage = 'ERROR: LIFDOC is NULL. GLR_SP_AddLeaseTransLI aborted.'RAISERROR(@p_errorMessage,11,1)END IF @p_LILDOC IS NULL BEGIN SET @p_errorMessage = 'ERROR: LILDOC is NULL. GLR_SP_AddLeaseTransLI aborted.'RAISERROR(@p_errorMessage,11,1)END IF @p_LITerm IS NULL BEGIN SET @p_errorMessage = 'ERROR: LITerm is NULL. GLR_SP_AddLeaseTransLI aborted.'RAISERROR(@p_errorMessage,11,1)END DECLARE @_flag_LoadPONbr int SELECT @_flag_LoadPONbr=COUNT(*) FROM glr.dbo.Lease_Transaction_LoadPO WHERE LeaseScheduleSAK = @p_LeaseScheduleSAK and LeaseItemID = @p_LeaseItemIDand LeaseTransactionCode =@p_LeaseTransactionCodeand TransactionFDOC = @p_TransactionFDOCand LoadPONbr = @p_LoadPONbr_old DECLARE @_flag_ClientPONbr int SELECT @_flag_ClientPONbr=COUNT(*) FROM glr.dbo.Lease_Transaction_ClientRequest WHERE LeaseScheduleSAK = @p_LeaseScheduleSAK and LeaseItemID = @p_LeaseItemIDand LeaseTransactionCode =@p_LeaseTransactionCodeand TransactionFDOC = @p_TransactionFDOCand ClientRequestPOSNbr = @p_ClientRequestPOSNbr_oldand ClientRequest = @p_ClientRequest_oldand ClientRequestLineNumber = @p_ClientRequestLineNumber_old DECLARE @bTranModeOn bitSET @bTranModeOn = 0SET IMPLICIT_TRANSACTIONS ONSET @bTranModeOn = 1 UPDATE glr.dbo.Lease_Item_Transaction SET TransactionFDOC = @p_TransactionFDOC ,LastUpdtDt = getdate() ,LastUpdtByTxt = @p_LastUpdtByTxt WHERE LeaseScheduleSAK = @p_LeaseScheduleSAK and LeaseItemID = @p_LeaseItemIDand LeaseTransactionCode =@p_LeaseTransactionCode UPDATE glr.dbo.Lease_Item_Transaction_LI SET LIItemMonthlyPayment = @p_LIItemMonthlyPayment ,LIPaymentFrequencyNbr = @p_LIPaymentFrequencyNbr ,LILeaseRateFactor = @p_LILeaseRateFactor ,LIFDOC = @p_LIFDOC ,LILDOC = @p_LILDOC ,LITerm = @p_LITerm ,LIFDOL = @p_LIFDOL ,LILDOL = @p_LILDOL ,LITermExtendedZeroCost = @p_LITermExtendedZeroCost ,LIReportDate = @p_LIReportDate ,LIComment = @p_LIComment ,LastUpdtDt = getdate() ,LastUpdtByTxt = @p_LastUpdtByTxt WHERE LeaseScheduleSAK = @p_LeaseScheduleSAK and LeaseItemID = @p_LeaseItemIDand LeaseTransactionCode =@p_LeaseTransactionCodeand TransactionFDOC = @p_TransactionFDOCIF @P_LoadPONbr IS NOT NULL BEGIN IF @_flag_LoadPONbr !=0 BEGIN UPDATE glr.dbo.Lease_Transaction_LoadPO SET LoadPONbr = @p_LoadPONbr ,LastUpdtDt = getdate() ,LastUpdtByTxt = @p_LastUpdtByTxt WHERE LeaseScheduleSAK = @p_LeaseScheduleSAK and LeaseItemID = @p_LeaseItemIDand LeaseTransactionCode =@p_LeaseTransactionCodeand TransactionFDOC = @p_TransactionFDOCand LoadPONbr = @p_LoadPONbr_old INSERT INTO glr.dbo.Lease_Transaction_LoadPO (LeaseScheduleSAK,LeaseItemID,LeaseTransactionCode,TransactionFDOC,LoadPONbr,CreateDt,LastUpdtDt, LastUpdtByTxt)VALUES (@P_LeaseScheduleSAK, @P_LeaseItemID,@P_LeaseTransactionCode,@P_TransactionFDOC, @p_LoadPONbr_old, getdate(),getdate(),@P_LastUpdtByTxt) END ELSEBEGIN INSERT INTO glr.dbo.Lease_Transaction_LoadPO (LeaseScheduleSAK,LeaseItemID,LeaseTransactionCode,TransactionFDOC,LoadPONbr,CreateDt,LastUpdtDt, LastUpdtByTxt)VALUES (@P_LeaseScheduleSAK, @P_LeaseItemID,@P_LeaseTransactionCode,@P_TransactionFDOC, @P_LoadPONbr, getdate(),getdate(),@P_LastUpdtByTxt) END END IF @P_ClientRequest IS NOT NULL BEGIN IF @_flag_ClientPONbr != 0 BEGIN UPDATE glr.dbo.Lease_Transaction_ClientRequest SET ClientRequestPOSNbr = @p_ClientRequestPOSNbr ,ClientRequest = @p_ClientRequest ,ClientRequestLineNumber = @p_ClientRequestLineNumber ,LastUpdtDt = getdate() ,LastUpdtByTxt = @p_LastUpdtByTxt WHERE LeaseScheduleSAK = @p_LeaseScheduleSAK and LeaseItemID = @p_LeaseItemIDand LeaseTransactionCode =@p_LeaseTransactionCodeand TransactionFDOC = @p_TransactionFDOCand ClientRequestPOSNbr = @p_ClientRequestPOSNbr_oldand ClientRequest = @p_ClientRequest_oldand ClientRequestLineNumber = @p_ClientRequestLineNumber_old INSERT INTO glr.dbo.Lease_Transaction_ClientRequest (LeaseScheduleSAK, LeaseItemID, LeaseTransactionCode, TransactionFDOC, ClientRequestPOSNbr, ClientRequest , ClientRequestLineNumber, CreateDt, LastUpdtDt, LastUpdtByTxt) VALUES (@P_LeaseScheduleSAK, @P_LeaseItemID, @P_LeaseTransactionCode, @P_TransactionFDOC, @p_ClientRequestPOSNbr_old, @p_ClientRequest_old , @p_ClientRequestLineNumber_old, getdate(), getdate(), @P_LastUpdtByTxt) END ELSE BEGIN INSERT INTO glr.dbo.Lease_Transaction_ClientRequest (LeaseScheduleSAK, LeaseItemID, LeaseTransactionCode, TransactionFDOC, ClientRequestPOSNbr, ClientRequest , ClientRequestLineNumber, CreateDt, LastUpdtDt, LastUpdtByTxt) VALUES (@P_LeaseScheduleSAK, @P_LeaseItemID, @P_LeaseTransactionCode, @P_TransactionFDOC, @P_ClientRequestPOSNbr, @P_ClientRequest , @P_ClientRequestLineNumber, getdate(), getdate(), @P_LastUpdtByTxt) ENDEND UPDATE glr.dbo.Lease_Asset_Status_History SETItemStatusNbr =( select ItemStatusNbr from glr.dbo.Item_Status where ItemStatusDesc = @p_ItemStatusDesc) ,LastUpdtDt = getdate() ,LastUpdtByTxt = @p_LastUpdtByTxt WHERE LeaseItemID = @p_LeaseItemID and StatusEffectiveDate = @p_StatusEffectiveDate COMMIT TRANSACTIONSET IMPLICIT_TRANSACTIONS OFFSET @bTranModeOn = 0 END TRYBEGIN CATCHIF @bTranModeOn = 1BEGINROLLBACK TRANSACTIONSET IMPLICIT_TRANSACTIONS OFFSET @bTranModeOn = 0END--Handle any T-SQL errors and raise them back to the client by calling the GLR_sp_RethrowError stored procedure.EXEC GLR_sp_RethrowErrorSET @p_errorMessage = SUBSTRING(ERROR_MESSAGE(), 1, 500)RETURN 1END CATCHENDgogrant execute on dbo.GLR_SP_UpdLeaseTransLI to glrgo