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



CREATE PROCEDURE RPTUSER.CIUASUMH
@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:       CIUASUMH
   PURPOSE:    

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

DECLARE @V_LANGUAGE_CD      CHAR(3)
DECLARE @V_RETURN_CD        DECIMAL(5)
DECLARE @V_AMG              CHAR(10)
DECLARE @V_AMG_DESCR        VARCHAR(60)
DECLARE @V_CHAR_TYPE_CD     CHAR(8)
DECLARE @V_CHAR_VAL         CHAR(16)
DECLARE @V_CHAR_TYPE_DESCR  VARCHAR(60)
DECLARE @V_DFV              CHAR(4)
DECLARE @V_CHAR_TYPE_FLG    CHAR(4)
DECLARE @V_CHAR_VAL_INFO    VARCHAR(254)

SELECT @V_LANGUAGE_CD = 'ENG'
SELECT @V_RETURN_CD = 0
SELECT @V_CHAR_TYPE_CD = NULL
SELECT @V_DFV = 'DFV '

BEGIN

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

    IF (SELECT @@ROWCOUNT) = 0
        SELECT @V_LANGUAGE_CD = 'ENG'

    IF NOT(@P_AMG IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_AMG))) = 0)
        BEGIN
            SELECT @V_AMG_DESCR = DESCR
            FROM CI_ACCT_MGMT_GR_L
            WHERE 
                ACCT_MGMT_GRP_CD = @P_AMG
                AND LANGUAGE_CD = @V_LANGUAGE_CD;
            IF (SELECT @@ROWCOUNT) = 0
                SELECT @V_AMG_DESCR = CAST(NULL AS VARCHAR)
        END

    IF NOT(@P_CHAR_TYPE IS NULL OR DATALENGTH(LTRIM(RTRIM(@P_CHAR_TYPE))) = 0)

        BEGIN

            SELECT 
                @V_CHAR_TYPE_DESCR = B.DESCR, 
                @V_CHAR_TYPE_FLG=A.CHAR_TYPE_FLG
            FROM
                CI_CHAR_TYPE A,
                CI_CHAR_TYPE_L B
            WHERE
                A.CHAR_TYPE_CD = @P_CHAR_TYPE
                AND B.CHAR_TYPE_CD = A.CHAR_TYPE_CD
                AND B.LANGUAGE_CD = @V_LANGUAGE_CD
            IF (SELECT @@ERROR) <> 0
                BEGIN
                    SELECT @V_CHAR_TYPE_DESCR = CAST(NULL AS VARCHAR)
                    SELECT @V_CHAR_TYPE_FLG = CAST(NULL AS CHAR)
                END

            IF @V_CHAR_TYPE_FLG = @V_DFV
                BEGIN
                    SELECT @V_CHAR_VAL_INFO = LTRIM(RTRIM(CHAR_VAL)) + '-' + DESCR
                    FROM CI_CHAR_VAL_L
                    WHERE 
                        CHAR_TYPE_CD = @P_CHAR_TYPE
                        AND CHAR_VAL = @P_CHAR_VALUE
                        AND LANGUAGE_CD = @V_LANGUAGE_CD;
                    IF (SELECT @@ERROR) <> 0
                        SELECT @V_CHAR_VAL_INFO = CAST(NULL AS VARCHAR)
                END
            ELSE
                SELECT @V_CHAR_VAL_INFO = @P_CHAR_VALUE

        END


    SELECT 
        @V_CHAR_TYPE_DESCR CHAR_TYPE_DESCR, 
        @V_CHAR_VAL_INFO CHAR_VAL_INFO,
        @V_AMG_DESCR AMG_DESCR

RETURN

END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

