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



CREATE PROCEDURE RPTUSER.CITOSSAR
@P_SA_ID CHAR(10), 
@P_START_DT DATETIME, 
@P_END_DT DATETIME,
@P_LANGUAGE_CD CHAR(3)
AS

/******************************************************************************
   NAME:       CITOSSAR
   PURPOSE:    Supply report extract for Crystal Report - 
               TOS SA Rate Info for a Given SA ID  
******************************************************************************/

DECLARE @V_FIELD_NAME CHAR(20)
SELECT @V_FIELD_NAME = 'CONTERM_TYPE_FLG'

BEGIN

    BEGIN

        SELECT 
            C.FIELD_VALUE RATE_INFO_FLG,
            C.DESCR RATE_INFO_DESCR,
            CAST(NULL AS CHAR) RS_CD,
            CAST(NULL AS DATETIME) EFFDT,
            CAST(NULL AS VARCHAR) RS_DESCR,
            A.BF_CD,
            B.DESCR BF_DESCR,
            A.START_DT,
            A.END_DT,
            A.VAL,
            A.PCT_EXEMPT,
            A.TAX_EX_TYPE_CD,
            A.TAX_CERT,
            D.DESCR TAX_EX_TYPE_DESR,
            CASE CONTERM_TYPE_FLG
                WHEN 'R ' THEN 2
                WHEN 'V ' THEN 3
                WHEN 'T ' THEN 4
            END
            AS SORT_ORDER
        FROM  
            CI_SA_CONTERM A 
                INNER JOIN 
                    CI_LOOKUP_VAL_L C
		    ON A.CONTERM_TYPE_FLG = C.FIELD_VALUE
                INNER JOIN 
                    CI_BF_L B
		    ON B.BF_CD = A.BF_CD
		LEFT OUTER JOIN 
		    CI_TAX_EX_TYPE_L D
		    ON A.TAX_EX_TYPE_CD = D.TAX_EX_TYPE_CD
                    AND D.LANGUAGE_CD = @P_LANGUAGE_CD
        WHERE
            A.SA_ID = @P_SA_ID
            AND B.LANGUAGE_CD = @P_LANGUAGE_CD
            AND C.FIELD_NAME = @V_FIELD_NAME
            AND C.LANGUAGE_CD = @P_LANGUAGE_CD
            AND ((A.END_DT IS NOT NULL AND 
                    ((A.START_DT BETWEEN @P_START_DT AND @P_END_DT)
                    OR (A.END_DT BETWEEN @P_START_DT AND @P_END_DT))
                )
                OR (A.END_DT IS NULL AND A.START_DT < @P_END_DT))
        UNION
        SELECT 
            'RS' RATE_INFO_FLG,
            'Rate' RATE_INFO_DESCR,
            A.RS_CD,
            A.EFFDT,
            B.DESCR RS_DESCR,
            CAST(NULL AS CHAR) BF_CD,
            CAST(NULL AS VARCHAR) BF_DESCR,
            CAST(NULL AS DATETIME) START_DT,
            CAST(NULL AS DATETIME) END_DT,
            CAST(NULL AS DECIMAL)  VAL,
            CAST(NULL AS DECIMAL) PCT_EXEMPT,
            CAST(NULL AS CHAR) TAX_EX_TYPE_CD,
            CAST(NULL AS VARCHAR) TAX_CERT,
            CAST(NULL AS VARCHAR) TAX_EX_TYPE_DESR,
            1 SORT_ORDER
        FROM   
            CI_SA_RS_HIST A
                INNER JOIN 
                    CI_RS_L B
                    ON B.RS_CD = A.RS_CD
        WHERE  
            A.SA_ID = @P_SA_ID
            AND B.LANGUAGE_CD = @P_LANGUAGE_CD
            AND A.EFFDT BETWEEN @P_START_DT AND @P_END_DT 
        ORDER BY 
            SORT_ORDER,
            EFFDT,
            START_DT

    END

RETURN

END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

