SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'CISAINFO' AND type = 'FN')
   DROP FUNCTION RPTUSER.CISAINFO
GO



CREATE FUNCTION RPTUSER.CISAINFO
(
@P_SA_ID CHAR(10), 
@P_LANGUAGE_CD CHAR(3)
)
RETURNS VARCHAR(185)
AS
BEGIN

DECLARE @V_SA_INFO VARCHAR(200)
DECLARE @V_SA_ID CHAR(10)
DECLARE @V_CHAR_PREM_ID CHAR(10)
DECLARE @V_CIS_DIVISION VARCHAR(60)
DECLARE @V_SA_TYPE_CD CHAR(8)
DECLARE @V_START_DT DATETIME
DECLARE @V_END_DT DATETIME
DECLARE @V_START_OPT_CD  CHAR(12)
DECLARE @V_SPECIAL_USAGE_FLG  CHAR(4)
DECLARE @V_PROP_SA_STAT_FLG  CHAR(2)
DECLARE @V_CURRENCY_CD  CHAR(3)
DECLARE @V_SA_TYPE VARCHAR(60)
DECLARE @V_SA_STATUS VARCHAR(60)
DECLARE @V_LANGUAGE_CD CHAR(3)
DECLARE @V_TEMP1 VARCHAR(20)
DECLARE @V_TEMP2 VARCHAR(20)

SELECT @V_LANGUAGE_CD= 'ENG'


/******************************************************************************
   NAME:       CISAINFO
   PURPOSE:     

******************************************************************************/
BEGIN

    IF @P_SA_ID IS NOT NULL

        BEGIN 
        IF @P_LANGUAGE_CD IS NULL
            SELECT @V_LANGUAGE_CD = @P_LANGUAGE_CD;

     SELECT
          @V_SA_ID = A.SA_ID ,
          @V_CHAR_PREM_ID = A.CHAR_PREM_ID ,
          @V_CIS_DIVISION = D.DESCR ,
          @V_SA_TYPE_CD = A.SA_TYPE_CD ,
          @V_START_DT = A.START_DT ,
          @V_END_DT = A.END_DT,
          @V_START_OPT_CD = A.START_OPT_CD ,
          @V_SPECIAL_USAGE_FLG = A.SPECIAL_USAGE_FLG ,
          @V_PROP_SA_STAT_FLG = A.PROP_SA_STAT_FLG ,
          @V_CURRENCY_CD = A.CURRENCY_CD ,
          @V_SA_TYPE = B.DESCR ,
          @V_SA_STATUS = C.DESCR 
        FROM
          CI_SA A,
          CI_SA_TYPE_L B,
          CI_LOOKUP_VAL_L C,
          CI_CIS_DIVISION_L D
        WHERE 
            A.SA_ID = @P_SA_ID  
            AND B.CIS_DIVISION = A.CIS_DIVISION
            AND B.SA_TYPE_CD = A.SA_TYPE_CD
            AND D.CIS_DIVISION = A.CIS_DIVISION
            AND B.LANGUAGE_CD = @V_LANGUAGE_CD
            AND C.LANGUAGE_CD = @V_LANGUAGE_CD
            AND D.LANGUAGE_CD = @V_LANGUAGE_CD
            AND (
                    (A.SPECIAL_USAGE_FLG <> 'PROP' 
                    AND C.FIELD_VALUE = A.SA_STATUS_FLG 
                    AND C.FIELD_NAME = 'SA_STATUS_FLG')
                    OR (A.SPECIAL_USAGE_FLG = 'PROP' 
                    AND C.FIELD_VALUE = A.PROP_SA_STAT_FLG 
                    AND C.FIELD_NAME = 'PROP_SA_STAT_FLG')
                )

        SELECT @V_SA_INFO = LTRIM(RTRIM(@V_CIS_DIVISION)) + ' / ' + LTRIM(RTRIM(@V_SA_TYPE))
       
        IF @V_SA_STATUS IS NOT NULL
            SELECT @V_SA_INFO = @V_SA_INFO + ', ' +  LTRIM(RTRIM(@V_SA_STATUS))

        RETURN @V_SA_INFO
        
        END

    ELSE
        RETURN ' '

    IF(SELECT @@ROWCOUNT) = 0
        RETURN ' '


    RETURN ''

END

END


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

