SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'CIZACCRR' AND type = 'P')
   DROP PROCEDURE RPTUSER.CIZACCRR
GO


CREATE PROCEDURE RPTUSER.CIZACCRR
@P_ACCT_ID CHAR(10)

AS

/******************************************************************************
   NAME:       CIZACCRR
   PURPOSE:    

******************************************************************************/

DECLARE @V_CR_RATING_PTS DECIMAL(8)
DECLARE @V_CASH_ONLY_PTS DECIMAL(8)

BEGIN

    BEGIN
        SELECT /*+ RULE */ 
            @V_CR_RATING_PTS=SUM(A.CR_RATING_PTS), 
            @V_CASH_ONLY_PTS= SUM(A.CASH_ONLY_PTS)
        FROM CI_CR_RAT_HIST A
        WHERE 
            A.ACCT_ID = @P_ACCT_ID 
            AND A.START_DT <= CONVERT(CHAR(10),GETDATE(),101) 
            AND (A.END_DT >=  CONVERT(CHAR(10),GETDATE(),101)  OR A.END_DT IS NULL)
        IF (SELECT @@ROWCOUNT) = 0
            BEGIN
                SELECT @V_CR_RATING_PTS = 0
                SELECT @V_CASH_ONLY_PTS = 0
            END
    END

    SELECT
        (A.START_CR_RAT_PTS + ISNULL(@V_CR_RATING_PTS,0)) CR_RATING_PTS,
        (A.START_CASH_ONL_PTS + ISNULL(@V_CASH_ONLY_PTS,0)) CASH_ONLY_PTS
    FROM CI_INSTALLATION A
    WHERE 
        A.INSTALL_OPT_ID = (
            SELECT MAX(B.INSTALL_OPT_ID)
            FROM CI_INSTALLATION B)
			 
RETURN

END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

