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



CREATE PROCEDURE RPTUSER.CIUASUMM
@P_USER_ID CHAR(8),
@P_UA_ID CHAR(10),
@P_AMG CHAR (10),
@P_CHAR_TYPE CHAR(10),
@P_CHAR_VALUE CHAR(16),
@P_EXPIRE_IN_X_DAYS CHAR

AS

/******************************************************************************
   NAME:       CIUASUMM
   PURPOSE:    

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

DECLARE @V_LANGUAGE_CD      CHAR(3)
DECLARE @V_UA_FIELD_NAME    CHAR(20)
DECLARE @V_TOS_FIELD_NAME   CHAR(20)
DECLARE @V_ACCT_MGMT_GRP_CD CHAR(10)
DECLARE @V_CHAR_VAL         CHAR(16)
DECLARE @V_UA_ID            CHAR(10)
DECLARE @V_CHAR_UA_ID       CHAR(10)
DECLARE @V_CHAR_TYPE_CD     CHAR(8)
DECLARE @V_SRCH_CHAR_VAL    VARCHAR(50)
DECLARE @V_SEQ_NUM          DECIMAL(3)
DECLARE @V_EXPIRE_IN_X_DAYS DECIMAL(3)
DECLARE @V_RETURN_CD        DECIMAL(5)
DECLARE @V_INDEX1           DECIMAL(2)
DECLARE @V_INDEX2           DECIMAL(2)

SELECT @V_LANGUAGE_CD = 'ENG'
SELECT @V_UA_FIELD_NAME = 'UA_STATUS_FLG'
SELECT @V_TOS_FIELD_NAME = 'TOS_STATUS_FLG'
SELECT @V_ACCT_MGMT_GRP_CD = NULL
SELECT @V_CHAR_VAL = NULL
SELECT @V_CHAR_TYPE_CD = NULL
SELECT @V_SRCH_CHAR_VAL = NULL
SELECT @V_SEQ_NUM = NULL
SELECT @V_EXPIRE_IN_X_DAYS = 0
SELECT @V_RETURN_CD = 0
SELECT @V_INDEX1 = 0
SELECT @V_INDEX2 = 0

BEGIN

    SELECT @V_LANGUAGE_CD=LANGUAGE_CD
    FROM SC_USER
    WHERE SC_USER.USER_ID = @P_USER_ID

    IF (SELECT @@ROWCOUNT) = 0
        BEGIN
            SELECT @V_LANGUAGE_CD = 'ENG'
            SELECT @V_RETURN_CD = 1003
        END


    IF @V_RETURN_CD = 0
        IF NOT (@P_UA_ID IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_UA_ID))) = 0)
            IF  (@P_AMG IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_AMG))) = 0) 
                AND (@P_CHAR_TYPE IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_CHAR_TYPE))) = 0)
                AND (@P_CHAR_VALUE IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_CHAR_VALUE))) = 0)
                AND (@P_EXPIRE_IN_X_DAYS IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_EXPIRE_IN_X_DAYS))) = 0)
                    BEGIN
                        SELECT @V_UA_ID = UA_ID
                            FROM CI_UA
                            WHERE UA_ID = @P_UA_ID;
                            IF (SELECT @@ROWCOUNT) = 0
                                SELECT @V_RETURN_CD = 1203
                    END
            ELSE
                SELECT @V_RETURN_CD = 1202
        ELSE
            IF (@P_AMG IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_AMG))) = 0) 
                AND (@P_CHAR_TYPE IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_CHAR_TYPE))) = 0)
                AND (@P_CHAR_VALUE IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_CHAR_VALUE))) = 0)
                AND (@P_EXPIRE_IN_X_DAYS IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_EXPIRE_IN_X_DAYS))) = 0)
                    SELECT @V_RETURN_CD = 1201


    IF @V_RETURN_CD = 0
        IF NOT (@P_AMG IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_AMG))) = 0)
            BEGIN
                SELECT @V_ACCT_MGMT_GRP_CD=ACCT_MGMT_GRP_CD
                FROM CI_ACCT_MGMT_GR
                WHERE ACCT_MGMT_GRP_CD = @P_AMG
                IF (SELECT @@ROWCOUNT) = 0
                    SELECT @V_RETURN_CD = 1204
            END


    IF @V_RETURN_CD = 0
        IF (NOT (@P_CHAR_TYPE IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_CHAR_TYPE))) = 0) 
            AND (@P_CHAR_VALUE IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_CHAR_VALUE))) = 0))
            OR ((@P_CHAR_TYPE IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_CHAR_TYPE))) = 0) 
            AND NOT (@P_CHAR_VALUE IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_CHAR_VALUE))) = 0))
                SELECT @V_RETURN_CD = 1207


    IF @V_RETURN_CD = 0
        IF NOT (@P_CHAR_TYPE IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_CHAR_TYPE))) = 0)
            BEGIN
                SELECT @V_CHAR_TYPE_CD = CHAR_TYPE_CD
                FROM CI_CHAR_TYPE
                WHERE CHAR_TYPE_CD = @P_CHAR_TYPE
                IF (SELECT @@ERROR) <> 0
                    SELECT @V_RETURN_CD = 1007
            END


    IF @V_RETURN_CD = 0
        SELECT @V_EXPIRE_IN_X_DAYS = 0
        IF NOT (@P_EXPIRE_IN_X_DAYS IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_EXPIRE_IN_X_DAYS))) = 0)
            BEGIN
                SELECT @V_EXPIRE_IN_X_DAYS = CAST(@P_EXPIRE_IN_X_DAYS AS DECIMAL)
                IF @V_EXPIRE_IN_X_DAYS <= 0
                    SELECT @V_RETURN_CD = 1208
                IF (SELECT @@ERROR) <> 0
                    SELECT @V_RETURN_CD = 1206
            END

    IF @V_RETURN_CD = 0
        IF @P_UA_ID IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_UA_ID))) = 0

            BEGIN
 
                 SELECT DISTINCT 
                    @V_RETURN_CD RETURN_CODE,
                    CAST(NULL AS VARCHAR) MESSAGE_TEXT,
                    @V_LANGUAGE_CD LANGUAGE_CD,
                    A.UA_ID, 
                    A.DESCR UA_DESCR, 
                    A.START_DT, 
                    A.END_DT,
                    A.RENEWAL_DT,
                    B.UA_RENEWAL_FLG,
                    A.COMMENT_LONG UA_COMMENT, 
                    C.DESCR UA_TYPE_DESCR, 
                    I.DESCR ACCT_MGMT_GR_DESCR, 
                    D.DESCR UA_STAT_DESCR,
                    E.TOS_ID,
                    E.SA_ID TEMPL_SA_ID, 
                    CASE E.SA_ID
                        WHEN NULL THEN ' '
                        ELSE RPTUSER.CISAINFO(E.SA_ID,@V_LANGUAGE_CD)
                    END AS TEMPL_SA_INFO,
                    E.DESCR TOS_DESCR, 
                    G.DESCR TOS_TYPE_DESCR,
                    F.TMPL_SA_USAGE_FLG, 
                    H.DESCR TOS_STAT_DESCR,
                    E.COMMENT_LONG TOS_COMMENT 
                FROM 
                    CI_UA A
                        INNER JOIN CI_UA_TYPE B
                            ON A.UA_TYPE_CD = B.UA_TYPE_CD
                            AND A.UA_STATUS_FLG = '20  '
                        LEFT OUTER JOIN CI_ACCT_MGMT_GR_L I
                            ON A.ACCT_MGMT_GRP_CD = I.ACCT_MGMT_GRP_CD
                            AND I.LANGUAGE_CD = @V_LANGUAGE_CD
                        INNER JOIN CI_LOOKUP_VAL_L D
                            ON A.UA_STATUS_FLG = D.FIELD_VALUE
                            AND D.LANGUAGE_CD = @V_LANGUAGE_CD
                            AND D.FIELD_NAME = @V_UA_FIELD_NAME
                        INNER JOIN CI_UA_TYPE_L C
                            ON B.UA_TYPE_CD = C.UA_TYPE_CD
                            AND C.LANGUAGE_CD = @V_LANGUAGE_CD
                        RIGHT OUTER JOIN CI_TOS E
                            ON E.UA_ID = A.UA_ID
                            AND E.TOS_STATUS_FLG <> '30  '
                        LEFT OUTER JOIN CI_TOS_TYPE F
                            ON E.TOS_TYPE_CD = F.TOS_TYPE_CD
                        LEFT OUTER JOIN CI_LOOKUP_VAL_L H
                            ON E.TOS_STATUS_FLG = H.FIELD_VALUE
                            AND H.LANGUAGE_CD = @V_LANGUAGE_CD
                            AND H.FIELD_NAME = @V_TOS_FIELD_NAME
                        LEFT OUTER JOIN CI_TOS_TYPE_L G
                            ON F.TOS_TYPE_CD = G.TOS_TYPE_CD
                            AND G.LANGUAGE_CD= @V_LANGUAGE_CD
                WHERE                    
                    (A.ACCT_MGMT_GRP_CD = @P_AMG OR @P_AMG IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_AMG))) = 0)
                    AND (
                        (@V_EXPIRE_IN_X_DAYS > 0 AND 
                            (A.END_DT BETWEEN GETDATE() AND GETDATE() + @V_EXPIRE_IN_X_DAYS)) 
                        OR 
                        (@V_EXPIRE_IN_X_DAYS = 0 AND 
                            ((GETDATE() BETWEEN A.START_DT AND A.END_DT AND A.END_DT IS NOT NULL)
                                OR (A.START_DT <= GETDATE() AND A.END_DT IS NULL)))
                    )
                    AND (
                        (@P_CHAR_TYPE IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_CHAR_TYPE))) = 0) 
                         OR 
                         EXISTS (
                             SELECT 'X'
                             FROM CI_UA_CHAR
                             WHERE 
                                 CI_UA_CHAR.UA_ID = A.UA_ID
                                 AND CI_UA_CHAR.CHAR_TYPE_CD = @P_CHAR_TYPE
                                 AND UPPER(CI_UA_CHAR.SRCH_CHAR_VAL) = UPPER(@P_CHAR_VALUE))
                     )
                ORDER BY A.UA_ID, E.TOS_ID
            END

        ELSE

            BEGIN

                SELECT 
                    DISTINCT @V_RETURN_CD RETURN_CODE,
                    CAST(NULL AS CHAR) MESSAGE_TEXT,
                    @V_LANGUAGE_CD LANGUAGE_CD,
                    A.UA_ID, 
                    A.DESCR UA_DESCR, 
                    A.START_DT, 
                    A.END_DT,
                    A.RENEWAL_DT,
                    B.UA_RENEWAL_FLG,
                    A.COMMENT_LONG UA_COMMENT, 
                    C.DESCR UA_TYPE_DESCR, 
                    I.DESCR ACCT_MGMT_GR_DESCR, 
                    D.DESCR UA_STAT_DESCR,
                    E.TOS_ID,
                    E.SA_ID TEMPL_SA_ID, 
                    CASE E.SA_ID
                        WHEN NULL THEN ' '
                        ELSE RPTUSER.CISAINFO(E.SA_ID,@V_LANGUAGE_CD)
                    END AS TEMPL_SA_INFO,
                    E.DESCR TOS_DESCR, 
                    G.DESCR TOS_TYPE_DESCR,
                    F.TMPL_SA_USAGE_FLG, 
                    H.DESCR TOS_STAT_DESCR,
                    E.COMMENT_LONG TOS_COMMENT 
                FROM 
                    CI_UA A
                        INNER JOIN CI_UA_TYPE B
                            ON A.UA_TYPE_CD = B.UA_TYPE_CD
                            AND A.UA_ID = @P_UA_ID
                        LEFT OUTER JOIN CI_ACCT_MGMT_GR_L I
                            ON A.ACCT_MGMT_GRP_CD = I.ACCT_MGMT_GRP_CD
                            AND I.LANGUAGE_CD = @V_LANGUAGE_CD
                        INNER JOIN CI_LOOKUP_VAL_L D
                            ON A.UA_STATUS_FLG = D.FIELD_VALUE
                            AND D.LANGUAGE_CD = @V_LANGUAGE_CD
                            AND D.FIELD_NAME = @V_UA_FIELD_NAME
                        INNER JOIN CI_UA_TYPE_L C
                            ON B.UA_TYPE_CD = C.UA_TYPE_CD
                            AND C.LANGUAGE_CD = @V_LANGUAGE_CD
                        RIGHT OUTER JOIN CI_TOS E
                            ON E.UA_ID = A.UA_ID
                            AND E.TOS_STATUS_FLG <> '30  '
                        LEFT OUTER JOIN CI_TOS_TYPE F
                            ON E.TOS_TYPE_CD = F.TOS_TYPE_CD
                        LEFT OUTER JOIN CI_LOOKUP_VAL_L H
                            ON E.TOS_STATUS_FLG = H.FIELD_VALUE
                            AND H.LANGUAGE_CD = @V_LANGUAGE_CD
                            AND H.FIELD_NAME = @V_TOS_FIELD_NAME
                        LEFT OUTER JOIN CI_TOS_TYPE_L G
                            ON F.TOS_TYPE_CD = G.TOS_TYPE_CD
                            AND G.LANGUAGE_CD= @V_LANGUAGE_CD
                ORDER BY A.UA_ID, E.TOS_ID
            END

    ELSE

        BEGIN

            SELECT  
                MESSAGE_NBR RETURN_CODE,
                MESSAGE_TEXT,
                @V_LANGUAGE_CD LANGUAGE_CD,
                CAST (NULL AS CHAR) UA_ID, 
                CAST (NULL AS VARCHAR) UA_DESCR, 
                CAST (NULL AS DATETIME) START_DT, 
                CAST (NULL AS DATETIME) END_DT,
                CAST (NULL AS DATETIME) RENEWAL_DT,
                CAST (NULL AS DATETIME) UA_ACCT_BEGIN_DT,
                CAST (NULL AS DATETIME) UA_ACCT_END_DT,
                CAST (NULL AS CHAR) UA_RENEWAL_FLG, 
                CAST (NULL AS VARCHAR) UA_COMMENT, 
                CAST (NULL AS VARCHAR) UA_TYPE_DESCR, 
                CAST (NULL AS VARCHAR) ACCT_MGMT_GR_DESCR, 
                CAST (NULL AS VARCHAR) UA_STAT_DESCR,
                CAST (NULL AS CHAR) TOS_ID, 
                CAST (NULL AS CHAR) TEMPL_SA_ID, 
                CAST (NULL AS VARCHAR) TEMPL_SA_INFO,
                CAST (NULL AS VARCHAR) TOS_DESCR, 
                CAST (NULL AS VARCHAR) TOS_TYPE_DESCR, 
                CAST (NULL AS CHAR) TMPL_SA_USAGE_FLG, 
                CAST (NULL AS VARCHAR) TOS_STAT_DESCR,
                CAST (NULL AS VARCHAR) TOS_COMMENT 
            FROM 
                CI_MSG_L
            WHERE
                MESSAGE_CAT_NBR = 30
                AND MESSAGE_NBR = @V_RETURN_CD
                AND LANGUAGE_CD = @V_LANGUAGE_CD

        END

RETURN

END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

