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



CREATE PROCEDURE RPTUSER.CITXPYBL
@P_USER_ID CHAR(8),
@P_FROM_DT VARCHAR(23),
@P_TO_DT VARCHAR(23),
@P_CHAR_TYPE CHAR(8),
@P_TAX_ACCTY_CHAR CHAR(16)
AS

/******************************************************************************
   NAME:       CITXPYBL
   PURPOSE:    Supply report extract for Crystal Report - Tax Payable Analysis

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

DECLARE @V_FROM_DT DATETIME
DECLARE @V_TO_DT DATETIME
DECLARE @V_LANGUAGE_CD CHAR(3)
DECLARE @V_RETURN_CD DECIMAL(5)
DECLARE @V_CHAR_TYPE CHAR(8)
DECLARE @V_CHAR_VAL CHAR(16)

SELECT @V_RETURN_CD = 0

			 
BEGIN	 -- MAIN BLOCK
   
    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

/******************************************************************************
   Checking for nulls in parameters and setting default values for select 
   statements
******************************************************************************/
	 
    BEGIN
        IF @P_FROM_DT IS NULL OR @P_FROM_DT = ' '
            SELECT @V_FROM_DT = GETDATE() - 7
        ELSE 
            SELECT @V_FROM_DT = CAST(@P_FROM_DT AS DATETIME)
	 
        IF @P_FROM_DT IS NULL OR @P_FROM_DT = ' '
            SELECT @V_TO_DT = GETDATE()
        ELSE 
            SELECT @V_TO_DT = CAST(@P_TO_DT AS DATETIME)
        
        IF (SELECT @@ERROR) <> 0
            BEGIN
                SELECT @V_RETURN_CD = 1000 	-- Set return code to be invalid datetime parameters
                SELECT @V_TO_DT = NULL
                SELECT @V_FROM_DT = NULL
            END
    END

    BEGIN
        SELECT @V_CHAR_TYPE = CHAR_TYPE_CD
        FROM CI_CHAR_TYPE
        WHERE CHAR_TYPE_CD = @P_CHAR_TYPE
        IF (SELECT @@ROWCOUNT) = 0
            SELECT @V_RETURN_CD = 1007
    END

    IF (SELECT @V_RETURN_CD) = 0 
        BEGIN
            SELECT @V_CHAR_VAL = CHAR_VAL
            FROM CI_CHAR_VAL
            WHERE
                CHAR_TYPE_CD = @V_CHAR_TYPE
                AND CHAR_VAL = @P_TAX_ACCTY_CHAR
            IF (SELECT @@ROWCOUNT) = 0
                SELECT @V_RETURN_CD = 1008
        END

    IF (SELECT @V_RETURN_CD) = 0
        BEGIN

 	  /****************************************************************************
	  This is the main select for the tax payable report extract. It handles the
	  overall display of the records retreived by the sub-select below and it also
	  handles the code-descr translations for division,dist-code,and characteristics
	  related to the calculated tax lines.
	  *****************************************************************************/

        SELECT
            @V_RETURN_CD RETURN_CODE,
            CAST(NULL AS VARCHAR) MESSAGE_TEXT,
            CAST(@V_FROM_DT AS DATETIME) V_FROM_DT,
            CAST(@V_TO_DT AS DATETIME) V_TO_DT,
            BSQ.CURRENCY_CD,
            BSQ.CIS_DIVISION CIS_DIV_CD,
            CI_CIS_DIVISION_L.DESCR CIS_DIV,
            BSQ.GL_ACCT,
            BSQ.CHAR_TYPE_CD,
            ISNULL(CI_CHAR_TYPE_L.DESCR, '---') CHAR_TYPE,
            BSQ.CHAR_VAL CHAR_VAL_CD,
            ISNULL(CI_CHAR_VAL_L.DESCR, '---') CHAR_VAL,
            BSQ.TAX_AMT,
            BSQ.TAX_EXEMPT,
            CI_CURRENCY_CD.CUR_SYMBOL,
            CI_CURRENCY_CD.SCALE_POSITIONS,
            CI_CURRENCY_CD.DECIMAL_POSITIONS,
            CI_CURRENCY_CD.CUR_POS_FLG,
            CI_CURRENCY_CD_L.DESCR CURR_DESC
        FROM 
            (	   
                SELECT FTS.CURRENCY_CD, 
        	FTS.CIS_DIVISION, 
        	FTS.CHAR_TYPE_CD, 
        	FTS.CHAR_VAL, 
        	FTS.GL_ACCT, 
        	SUM(FTS.TAX_AMT) TAX_AMT, 
        	SUM(FTS.TAX_EXEMPT) TAX_EXEMPT 
        	FROM (		 
                    SELECT 
                        CI_FT.CURRENCY_CD,
                        CI_FT.CIS_DIVISION,
                        CI_BSEG_CALC_LN.CHAR_TYPE_CD,
                        CI_BSEG_CALC_LN.CHAR_VAL,
                        CI_DST_CODE_EFF.GL_ACCT,
                        SIGN(CI_FT.TOT_AMT)*CI_BSEG_CALC_LN.CALC_AMT  TAX_AMT,
                        SIGN(CI_FT.TOT_AMT)*CI_BSEG_CALC_LN.EXEMPT_AMT TAX_EXEMPT
                    FROM
                        CI_BSEG_CALC_LN, 
                        CI_DST_CODE_EFF,
                        CI_DST_CD_CHAR, 
                        CI_FT
                    WHERE
                        CI_BSEG_CALC_LN.BSEG_ID = CI_FT.SIBLING_ID
                        AND CI_FT.FREEZE_SW = 'Y'
                        AND CI_FT.REDUNDANT_SW = 'N'  
                        AND CI_FT.FT_TYPE_FLG IN ('BS', 'BX') 
                        AND CI_FT.ACCOUNTING_DT BETWEEN @V_FROM_DT AND @V_TO_DT
                        AND CI_DST_CODE_EFF.DST_ID = CI_BSEG_CALC_LN.DST_ID
                        AND CI_DST_CODE_EFF.EFFDT =
                            (SELECT MAX(EFFDT)
                            FROM CI_DST_CODE_EFF T
                            WHERE    
                                T.DST_ID = CI_DST_CODE_EFF.DST_ID
                                AND T.EFFDT <= CI_FT.ACCOUNTING_DT
                            )
                        AND CI_DST_CD_CHAR.DST_ID = CI_BSEG_CALC_LN.DST_ID
                        AND CI_DST_CD_CHAR.CHAR_TYPE_CD = @V_CHAR_TYPE
                        AND CI_DST_CD_CHAR.CHAR_VAL = @V_CHAR_VAL
                        AND CI_DST_CD_CHAR.EFFDT = 
                            (SELECT MAX(EFFDT)
                            FROM CI_DST_CD_CHAR T
                            WHERE     
                                T.DST_ID = CI_DST_CD_CHAR.DST_ID
                                AND T.EFFDT <= CI_FT.ACCOUNTING_DT
                                AND T.CHAR_TYPE_CD = CI_DST_CD_CHAR.CHAR_TYPE_CD)
                    UNION ALL		
                    SELECT 
                        CI_FT.CURRENCY_CD,
                        CI_FT.CIS_DIVISION,
                        CI_FT_GL.CHAR_TYPE_CD,
                        CI_FT_GL.CHAR_VAL,
                        CI_DST_CODE_EFF.GL_ACCT,
                        SIGN(CI_FT.TOT_AMT)*CI_FT_GL.AMOUNT  TAX_AMT,
                        0 TAX_EXEMPT
                    FROM 
                        CI_FT_GL, 
                        CI_DST_CODE_EFF,
                        CI_DST_CD_CHAR, 
                        CI_FT
                    WHERE     
                        CI_FT_GL.FT_ID = CI_FT.FT_ID
                        AND CI_FT.FREEZE_SW = 'Y' 
                        AND CI_FT.REDUNDANT_SW = 'N' 
                        AND CI_FT.FT_TYPE_FLG NOT IN ('BS', 'BX') 
                        AND CI_FT.ACCOUNTING_DT BETWEEN @V_FROM_DT AND @V_TO_DT
                        AND CI_DST_CODE_EFF.DST_ID = CI_FT_GL.DST_ID
                        AND CI_DST_CODE_EFF.EFFDT = 
	                    (SELECT MAX(EFFDT)
        		    FROM CI_DST_CODE_EFF T
                            WHERE    
                                T.DST_ID = CI_DST_CODE_EFF.DST_ID
                                AND T.EFFDT <= CI_FT.ACCOUNTING_DT)
                                AND CI_DST_CD_CHAR.DST_ID = CI_FT_GL.DST_ID
                                AND CI_DST_CD_CHAR.CHAR_TYPE_CD = @V_CHAR_TYPE
                                AND CI_DST_CD_CHAR.CHAR_VAL = @V_CHAR_VAL
                                AND CI_DST_CD_CHAR.EFFDT = 
	                            (SELECT MAX(EFFDT)
	                            FROM CI_DST_CD_CHAR T
	                            WHERE    
		                        T.DST_ID = CI_DST_CD_CHAR.DST_ID
                                        AND T.EFFDT <= CI_FT.ACCOUNTING_DT
                                        AND T.CHAR_TYPE_CD = CI_DST_CD_CHAR.CHAR_TYPE_CD)
                ) FTS	
                GROUP BY 
                    FTS.CURRENCY_CD,
                    FTS.CIS_DIVISION,
                    FTS.CHAR_TYPE_CD,
                    FTS.CHAR_VAL,
                    FTS.GL_ACCT
            ) BSQ
                INNER JOIN CI_CIS_DIVISION_L
                    ON CI_CIS_DIVISION_L.CIS_DIVISION = BSQ.CIS_DIVISION
                    AND CI_CIS_DIVISION_L.LANGUAGE_CD = @V_LANGUAGE_CD 
                LEFT OUTER JOIN CI_CHAR_TYPE_L
                    ON CI_CHAR_TYPE_L.CHAR_TYPE_CD = BSQ.CHAR_TYPE_CD
                    AND CI_CHAR_TYPE_L.LANGUAGE_CD = @V_LANGUAGE_CD 
                LEFT OUTER JOIN CI_CHAR_VAL_L
                    ON CI_CHAR_VAL_L.CHAR_TYPE_CD = BSQ.CHAR_TYPE_CD
                    AND CI_CHAR_VAL_L.CHAR_VAL = BSQ.CHAR_VAL
                    AND CI_CHAR_VAL_L.LANGUAGE_CD = @V_LANGUAGE_CD
                INNER JOIN CI_CURRENCY_CD
                    ON BSQ.CURRENCY_CD = CI_CURRENCY_CD.CURRENCY_CD
                INNER JOIN CI_CURRENCY_CD_L
                    ON CI_CURRENCY_CD.CURRENCY_CD = CI_CURRENCY_CD_L.CURRENCY_CD
                    AND CI_CURRENCY_CD_L.LANGUAGE_CD = @V_LANGUAGE_CD
        ORDER BY 
            BSQ.CURRENCY_CD,
            BSQ.CIS_DIVISION,
            BSQ.GL_ACCT,
            BSQ.CHAR_TYPE_CD,
            BSQ.CHAR_VAL
        END

    ELSE
        BEGIN
  
            SELECT 
                MESSAGE_NBR RETURN_CODE,
                MESSAGE_TEXT,
                CAST(NULL AS DATETIME) V_FROM_DT,
                CAST(NULL AS DATETIME) V_TO_DT,
                CAST(NULL AS CHAR) CURRENCY_CD,
                CAST(NULL AS CHAR) CIS_DIV_CD,
                CAST(NULL AS VARCHAR) CIS_DIV,
                CAST(NULL AS VARCHAR) GL_ACCT,
                CAST(NULL AS CHAR) CHAR_TYPE_CD,
                CAST(NULL AS VARCHAR) CHAR_TYPE,
                CAST(NULL AS CHAR) CHAR_VAL_CD,
                CAST(NULL AS VARCHAR) CHAR_VAL,
                CAST(NULL AS DECIMAL) TAX_AMT,
                CAST(NULL AS DECIMAL) TAX_EXEMPT,
                CAST(NULL AS CHAR) CUR_SYMBOL,
                CAST(NULL AS DECIMAL) SCALE_POSITIONS,
                CAST(NULL AS DECIMAL) DECIMAL_POSITIONS,
                CAST(NULL AS CHAR) CUR_POS_FLG,
                CAST(NULL AS VARCHAR) CURR_DESC
            FROM 
                CI_MSG_L
            WHERE
                MESSAGE_CAT_NBR = 30
                AND MESSAGE_NBR = @V_RETURN_CD
                AND LANGUAGE_CD = @V_LANGUAGE_CD

        END

END

RETURN

END



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

