-- ******************************************************************
-- *
-- * Copyright (c) 2000, 2007, Oracle. All rights reserved.
-- *
-- ******************************************************************
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
IF EXISTS (SELECT name FROM sysobjects 
         WHERE name = 'CIFOPRNT' AND type = 'P')
   DROP PROCEDURE RPTUSER.CIFOPRNT
GO

CREATE PROCEDURE CIFOPRNT
@P_USER_ID CHAR(8),
@P_BATCH_SW CHAR(1),
@P_FROM_FO_ID CHAR(10),
@P_TO_FO_ID CHAR(10),
@P_BATCH_CD CHAR(8),
@P_BATCH_NBR DECIMAL(10),
@P_EXTRACT_ALG_CD CHAR(12)

AS
        DECLARE @V_LANGUAGE_CD    CHAR(3)
        DECLARE @V_RETURN_CD      INT
        DECLARE @V_BATCH_SW       CHAR(1)
        DECLARE @V_FROM_FO_ID     CHAR(12)
        DECLARE @V_TO_FO_ID       CHAR(12)
        DECLARE @V_BATCH_CD       CHAR(8)
        DECLARE @V_BATCH_NBR      DECIMAL(10)
        DECLARE @V_EXTRACT_ALG_CD CHAR(12)
        DECLARE @V_ALG_ENTITY     CHAR(4)
        DECLARE @V_FIELD_NAME     CHAR(20)
	
	SELECT @V_LANGUAGE_CD = 'ENG'
	SELECT @V_RETURN_CD = 0
	SELECT @V_BATCH_NBR = 0
	SELECT @V_EXTRACT_ALG_CD = 0
	SELECT @V_ALG_ENTITY = 'FOEX'
	SELECT @V_FIELD_NAME = 'FA_STEP_TY_ACT_FLG'

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

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

	IF @V_RETURN_CD = 0
	BEGIN
		IF @P_BATCH_SW IS NULL OR LEN(LTRIM(RTRIM(@P_BATCH_SW))) = 0
	            SELECT @V_BATCH_SW = 'N'
	        ELSE
        	    SELECT @V_BATCH_SW = @P_BATCH_SW

	        IF @V_BATCH_SW NOT IN ('Y','N')
	            SELECT @V_RETURN_CD = 1032 		-- ERROR: INVALID BATCH SW
	END

	IF @V_RETURN_CD = 0
	BEGIN
        	IF @P_FROM_FO_ID IS NULL OR LEN(LTRIM(RTRIM(@P_FROM_FO_ID))) = 0
            		SELECT @V_RETURN_CD = 1034 	-- ERROR: FROM FO ID IS REQUIRED
	        ELSE
        		SELECT @V_FROM_FO_ID = @P_FROM_FO_ID
        END
	
    	IF @V_RETURN_CD = 0
        	IF @V_BATCH_SW = 'N'
		BEGIN
	       		SELECT @V_FROM_FO_ID = FO_ID
	                FROM CI_FO
		        WHERE FO_ID = @P_FROM_FO_ID
			
			IF @@ROWCOUNT = 0
	                	SELECT @V_RETURN_CD = 1033	-- ERROR: INVALID FO ID
		END

	IF @V_RETURN_CD = 0
	BEGIN
   		IF @V_BATCH_SW = 'Y'
		BEGIN
            		IF @P_TO_FO_ID IS NULL OR LEN(LTRIM(RTRIM(@P_TO_FO_ID))) = 0
                		SELECT @V_RETURN_CD = 1035 	-- ERROR: TO FO ID IS REQUIRED IF BATCH SW = Y
			ELSE
                		SELECT @V_TO_FO_ID = @P_TO_FO_ID
		END
	END

	IF @V_RETURN_CD = 0
        	IF @V_BATCH_SW = 'Y'
        		IF @P_FROM_FO_ID > @P_TO_FO_ID
                		SELECT @V_RETURN_CD = 1036 	-- ERROR: FROM FO ID IS GREATER THAN THE TO FO ID.
	
	IF @V_RETURN_CD = 0
	        IF @V_BATCH_SW = 'Y'
		BEGIN
            		IF @P_BATCH_CD IS NULL OR LEN(LTRIM(RTRIM(@P_BATCH_CD))) = 0
                		SELECT @V_RETURN_CD = 1037 	-- ERROR: BATCH CODE IS REQUIRED IF BATCH SW = Y
            		ELSE
	                BEGIN
	                    SELECT @V_BATCH_CD=BATCH_CD
                	    FROM   CI_BATCH_CTRL
	                    WHERE  BATCH_CD = @P_BATCH_CD;

        	            IF @@ROWCOUNT = 0
	                    	SELECT @V_RETURN_CD = 1031 	-- ERROR: INVALID BATCH CODE ID
        	        END
		END

	IF @V_RETURN_CD = 0
	        IF @V_BATCH_SW = 'Y'
		BEGIN
	            IF @P_BATCH_NBR IS NULL OR @P_BATCH_NBR = 0
  			SELECT @V_RETURN_CD = 1038	 -- ERROR: BATCH NUMBER IS REQUIRED IF BATCH SW = Y
		    ELSE
			SELECT @V_BATCH_NBR = @P_BATCH_NBR
		END

	IF @V_RETURN_CD = 0
	BEGIN
	        IF @V_BATCH_SW = 'Y'
		BEGIN
            		IF @P_EXTRACT_ALG_CD IS NULL OR LEN(LTRIM(RTRIM(@P_EXTRACT_ALG_CD))) = 0
                		SELECT @V_RETURN_CD = 1039 	-- ERROR: FO EXTRACT ALGORITHM IS REQUIRED IF BATCH SW = Y
            		ELSE
	                BEGIN
        	            SELECT @V_EXTRACT_ALG_CD = ALG_CD
                	    FROM   CI_ALG
	                    WHERE  ALG_CD = @P_EXTRACT_ALG_CD;
                
        	            IF @@ROWCOUNT = 0
                	    	SELECT @V_RETURN_CD = 1040 	-- ERROR: INVALID FO EXTRACT ALGORITHM
	                END
		END	END

    	IF @V_RETURN_CD = 0
	BEGIN
	        IF @V_BATCH_SW = 'Y'
		BEGIN
	            IF @V_BATCH_NBR = 1
		    BEGIN
        	          SELECT @V_RETURN_CD RETURN_CODE
                	        ,CAST(NULL AS VARCHAR) MESSAGE_TEXT
	                        ,@V_LANGUAGE_CD AS LANGUAGE_CD
        	                ,A.FO_ID AS SORT_KEY, A.FO_ID, A.PREM_ID, A.SCHED_DTTM FO_SCHED_DTTM
                	        ,A.FO_STATUS_FLG, A.WORK_DTTM, A.DISP_GRP_CD, A.REP_CD, A.WORKED_BY
	                        ,A.EXTRACT_NEXT_SW, A.EXTRACT_DTTM, A.DESCR254 FO_DESCR
				, B.BATCH_CD, B.BATCH_NBR
        	                ,C.FA_ID, C.SP_ID, C.FA_TYPE_CD, C.FA_PRIORITY_FLG
                	        ,C.FA_CREATED_BY_FLG, C.SCHED_DTTM FA_SCHED_DTTM, C.FA_STATUS_FLG, C.ELIG_DISPATCH_SW
                        	,C.CRE_DTTM, C.INSTRUCTIONS, C.TEST_SEL_ID, C.DESCR254 FA_DESCR
	                        ,C.FA_CAN_RSN_CD
				, D.STEP_SEQ_NBR, D.FA_STEP_TY_ACT_FLG				
				, F.DESCR FA_STEP_TY_DESCR
        	                ,D.STP_ENTITY_FLG,D.SP_MTR_HIST_ID,D.SP_ITEM_HIST_ID, D.MR_ID, D.MTR_CFG_MTR_ID
                	        ,D.MTR_ID, D.ITEM_ID, D.CC_ID, D.SPAWNED_FA_ID, D.ACCT_ID
                        	,D.DV_TEST_ID
				, E.PREM_TYPE_CD, E.LL_ID, E.MAIL_ADDR_SW, E.KEY_SW
	                        ,E.KEY_ID, E.OK_TO_ENTER_SW, E.MR_INSTR_CD, E.MR_INSTR_DETAILS
        	                ,E.MR_WARN_CD, E.TREND_AREA_CD, E.COUNTRY, E.ADDRESS1, E.ADDRESS2
                	        ,E.ADDRESS3, E.ADDRESS4, E.CITY, E.NUM1, E.NUM2, E.HOUSE_TYPE
                        	,E.COUNTY, E.STATE, E.POSTAL, E.GEO_CODE, E.IN_CITY_LIMIT
	                        ,H.DESCR FA_STEP_TYPE_DESCR
				, G.OPTIONAL_SW
				, J.DESCR REP_DESCR
        	                ,K.DESCR FA_TYPE_DESCR, L.DESCR DISP_GRP_DESCR
				, O.ACCT_ID LL_ACCT_ID, O.DESCR LL_DESCR, P.DESCR MR_WARN_DESCR
                	        ,Q.DESCR MR_INSTR_DESCR
	                    FROM CI_FO A 
					left outer join CI_REP_L J on J.REP_CD = A.REP_CD AND J.LANGUAGE_CD = @V_LANGUAGE_CD
				, CI_FO_STG_DWN B
              	        	,CI_FA C
 					left outer join CI_FA_TYPE_L K on K.FA_TYPE_CD = C.FA_TYPE_CD AND K.LANGUAGE_CD = @V_LANGUAGE_CD
					left outer join CI_DISP_GRP_L L on L.DISP_GRP_CD = C.DISP_GRP_CD AND L.LANGUAGE_CD = @V_LANGUAGE_CD
                        	,CI_FA_STEP D
	                        ,CI_PREM E
					left outer join CI_LANDLORD O on O.LL_ID = E.LL_ID
					left outer join CI_MR_WARN_L P on P.MR_WARN_CD = E.MR_WARN_CD AND P.LANGUAGE_CD = @V_LANGUAGE_CD
					left outer join CI_MR_INSTR_L Q on Q.MR_INSTR_CD = E.MR_INSTR_CD AND Q.LANGUAGE_CD = @V_LANGUAGE_CD
        	                ,CI_LOOKUP F
              	        	,CI_FA_STEP_TYPE G
                         	,CI_FA_STEP_TYPE_L H
                        	,CI_BATCH_CTRL I
	                    WHERE 
			    B.FO_ID BETWEEN @V_FROM_FO_ID AND @V_TO_FO_ID AND
        	            B.BATCH_CD = @V_BATCH_CD
	                    AND B.FO_ID = A.FO_ID
                	    AND B.BATCH_NBR = I.NEXT_BATCH_NBR - 1
        	            AND C.FO_ID = A.FO_ID
                	    AND D.FA_ID = C.FA_ID
	                    AND F.FIELD_NAME = @V_FIELD_NAME
        	            AND F.FIELD_VALUE = D.FA_STEP_TY_ACT_FLG
                	    AND F.LANGUAGE_CD = @V_LANGUAGE_CD
	                    AND E.PREM_ID = A.PREM_ID
        	            AND G.FA_TYPE_CD = C.FA_TYPE_CD
                	    AND G.STEP_SEQ_NBR = D.STEP_SEQ_NBR
	                    AND H.FA_TYPE_CD = G.FA_TYPE_CD
        	            AND H.STEP_SEQ_NBR = G.STEP_SEQ_NBR
                	    AND H.LANGUAGE_CD = @V_LANGUAGE_CD
		            AND I.BATCH_CD = @V_BATCH_CD
                   	    AND EXISTS (SELECT 'X' FROM CI_DISP_GRP_ALG O
                                WHERE O.DISP_GRP_CD = C.DISP_GRP_CD
                                AND O.DG_ALG_ENTITY_FLG = @V_ALG_ENTITY                                AND O.ALG_CD = @V_EXTRACT_ALG_CD)
	                    ORDER BY 
				SORT_KEY
        		        , E.STATE,
        	                E.CITY,
				E.POSTAL,
	                        E.ADDRESS1,
        	                A.PREM_ID,
                	        FO_SCHED_DTTM,
                                C.SP_ID,
                                C.FA_PRIORITY_FLG,
                                C.FA_ID,
                                D.STEP_SEQ_NBR;
		    END
            	    ELSE
		    BEGIN
                  	SELECT @V_RETURN_CD RETURN_CODE
                        	,CAST(NULL AS VARCHAR) MESSAGE_TEXT
	                        ,@V_LANGUAGE_CD AS LANGUAGE_CD
        	                ,A.FO_ID AS SORT_KEY, A.FO_ID, A.PREM_ID, A.SCHED_DTTM FO_SCHED_DTTM
                	        ,A.FO_STATUS_FLG, A.WORK_DTTM, A.DISP_GRP_CD, A.REP_CD, A.WORKED_BY
                        	,A.EXTRACT_NEXT_SW, A.EXTRACT_DTTM, A.DESCR254 FO_DESCR, B.BATCH_CD, B.BATCH_NBR
	                        ,C.FA_ID, C.SP_ID, C.FA_TYPE_CD, C.FA_PRIORITY_FLG, C.FA_CREATED_BY_FLG
        	                ,C.SCHED_DTTM FA_SCHED_DTTM, C.FA_STATUS_FLG, C.ELIG_DISPATCH_SW
                	        ,C.CRE_DTTM, C.INSTRUCTIONS, C.TEST_SEL_ID, C.DESCR254 FA_DESCR
                        	,C.FA_CAN_RSN_CD, D.STEP_SEQ_NBR, D.FA_STEP_TY_ACT_FLG, F.DESCR FA_STEP_TY_DESCR
	                        ,D.STP_ENTITY_FLG,D.SP_MTR_HIST_ID, D.SP_ITEM_HIST_ID, D.MR_ID, D.MTR_CFG_MTR_ID
        	                ,D.MTR_ID, D.ITEM_ID, D.CC_ID, D.SPAWNED_FA_ID, D.ACCT_ID
                	        ,D.DV_TEST_ID, E.PREM_TYPE_CD, E.LL_ID, E.MAIL_ADDR_SW, E.KEY_SW
                        	,E.KEY_ID, E.OK_TO_ENTER_SW, E.MR_INSTR_CD, E.MR_INSTR_DETAILS
	                        ,E.MR_WARN_CD, E.TREND_AREA_CD, E.COUNTRY, E.ADDRESS1, E.ADDRESS2
        	                ,E.ADDRESS3, E.ADDRESS4, E.CITY, E.NUM1, E.NUM2, E.HOUSE_TYPE
                	        ,E.COUNTY, E.STATE, E.POSTAL, E.GEO_CODE, E.IN_CITY_LIMIT
                        	,H.DESCR FA_STEP_TYPE_DESCR, G.OPTIONAL_SW, I.DESCR REP_DESCR
	                        ,J.DESCR FA_TYPE_DESCR, K.DESCR DISP_GRP_DESCR
        	                ,N.ACCT_ID LL_ACCT_ID, N.DESCR LL_DESCR, O.DESCR MR_WARN_DESCR
                	        ,P.DESCR MR_INSTR_DESCR
	                FROM 	CI_FO_STG_DWN B
	                        ,CI_FA C
					left outer join CI_FA_TYPE_L J on J.FA_TYPE_CD = C.FA_TYPE_CD AND J.LANGUAGE_CD = @V_LANGUAGE_CD
					left outer join CI_DISP_GRP_L K on K.DISP_GRP_CD = C.DISP_GRP_CD AND K.LANGUAGE_CD = @V_LANGUAGE_CD
        	                ,CI_FA_STEP D
                	        ,CI_PREM E
					left outer join CI_MR_WARN_L O on O.MR_WARN_CD = E.MR_WARN_CD AND O.LANGUAGE_CD = @V_LANGUAGE_CD
					left outer join CI_MR_INSTR_L P on P.MR_INSTR_CD = E.MR_INSTR_CD    AND P.LANGUAGE_CD = @V_LANGUAGE_CD
					left outer join CI_LANDLORD N on N.LL_ID = E.LL_ID
                        	,CI_LOOKUP F
	                        ,CI_FA_STEP_TYPE G
        	                ,CI_FA_STEP_TYPE_L H
                	        ,CI_REP_L I
					right outer join CI_FO A on I.REP_CD = A.REP_CD AND I.LANGUAGE_CD = @V_LANGUAGE_CD
	                WHERE B.FO_ID BETWEEN @V_FROM_FO_ID AND @V_TO_FO_ID
		                    AND B.BATCH_CD = @V_BATCH_CD
                		    AND B.BATCH_NBR = @V_BATCH_NBR
                		    AND B.FO_ID = A.FO_ID
		                    AND C.FO_ID = A.FO_ID
		                    AND D.FA_ID = C.FA_ID
		                    AND E.PREM_ID = A.PREM_ID
                		    AND F.FIELD_NAME = @V_FIELD_NAME
		                    AND F.FIELD_VALUE = D.FA_STEP_TY_ACT_FLG
		                    AND F.LANGUAGE_CD = @V_LANGUAGE_CD
                		    AND G.FA_TYPE_CD = C.FA_TYPE_CD
		                    AND G.STEP_SEQ_NBR = D.STEP_SEQ_NBR
                		    AND H.FA_TYPE_CD = G.FA_TYPE_CD
		                    AND H.STEP_SEQ_NBR = G.STEP_SEQ_NBR
		                    AND H.LANGUAGE_CD = @V_LANGUAGE_CD
		                    AND EXISTS (SELECT 'X' FROM CI_DISP_GRP_ALG N
                		                WHERE N.DISP_GRP_CD = C.DISP_GRP_CD
                                			AND N.DG_ALG_ENTITY_FLG = @V_ALG_ENTITY
			                                AND N.ALG_CD = @V_EXTRACT_ALG_CD)
			                        ORDER BY SORT_KEY,
					                    E.STATE,
				                            E.CITY,
				                            E.POSTAL,
				                            E.ADDRESS1,
				                            A.PREM_ID,
				                            FO_SCHED_DTTM,
				                            C.SP_ID,
				                            C.FA_PRIORITY_FLG,
				                            C.FA_ID,
				                            D.STEP_SEQ_NBR;
		    END
		END     
                ELSE
		BEGIN
		        SELECT O.LANGUAGE_CD,
				@V_RETURN_CD RETURN_CODE
        		          ,CAST(NULL AS VARCHAR) MESSAGE_TEXT
		                  ,@V_LANGUAGE_CD AS LANGUAGE_CD
		                  ,A.FO_ID AS SORT_KEY, A.FO_ID, A.PREM_ID, A.SCHED_DTTM FO_SCHED_DTTM
		                  ,A.FO_STATUS_FLG, A.WORK_DTTM, A.DISP_GRP_CD, A.REP_CD, A.WORKED_BY
		                  ,A.EXTRACT_NEXT_SW, A.EXTRACT_DTTM, A.DESCR254 FO_DESCR, CAST(NULL AS CHAR) BATCH_CD
		                  ,CAST(NULL AS CHAR) BATCH_NBR, C.FA_ID, C.SP_ID, C.FA_TYPE_CD, C.FA_PRIORITY_FLG, C.FA_CREATED_BY_FLG
		                  ,C.SCHED_DTTM FA_SCHED_DTTM, C.FA_STATUS_FLG, C.ELIG_DISPATCH_SW
		                  ,C.CRE_DTTM, C.INSTRUCTIONS, C.TEST_SEL_ID, C.DESCR254 FA_DESCR
		                  ,C.FA_CAN_RSN_CD
				  , D.STEP_SEQ_NBR, D.FA_STEP_TY_ACT_FLG, F.DESCR FA_STEP_TY_DESCR
		                  ,D.STP_ENTITY_FLG,D.SP_MTR_HIST_ID, D.SP_ITEM_HIST_ID, D.MR_ID, D.MTR_CFG_MTR_ID
		                  ,D.MTR_ID, D.ITEM_ID, D.CC_ID, D.SPAWNED_FA_ID, D.ACCT_ID
		                  ,D.DV_TEST_ID, E.PREM_TYPE_CD, E.LL_ID, E.MAIL_ADDR_SW, E.KEY_SW
		                  ,E.KEY_ID, E.OK_TO_ENTER_SW, E.MR_INSTR_CD, E.MR_INSTR_DETAILS
		                  ,E.MR_WARN_CD, E.TREND_AREA_CD, E.COUNTRY, E.ADDRESS1, E.ADDRESS2
		                  ,E.ADDRESS3, E.ADDRESS4, E.CITY, E.NUM1, E.NUM2, E.HOUSE_TYPE
		                  ,E.COUNTY, E.STATE, E.POSTAL, E.GEO_CODE, E.IN_CITY_LIMIT
		                  ,H.DESCR FA_STEP_TYPE_DESCR, G.OPTIONAL_SW
				  , I.DESCR REP_DESCR
		                  ,J.DESCR FA_TYPE_DESCR, K.DESCR DISP_GRP_DESCR
		                  ,N.ACCT_ID LL_ACCT_ID, N.DESCR LL_DESCR, O.DESCR MR_WARN_DESCR
		                  ,P.DESCR MR_INSTR_DESCR
		        FROM CI_FO A
					left outer join CI_REP_L I on I.REP_CD = A.REP_CD AND I.LANGUAGE_CD = @V_LANGUAGE_CD
              		     	  ,CI_FA C
					left outer join CI_FA_TYPE_L J on J.FA_TYPE_CD = C.FA_TYPE_CD AND J.LANGUAGE_CD = @V_LANGUAGE_CD
					left outer join CI_DISP_GRP_L K on K.DISP_GRP_CD = C.DISP_GRP_CD AND K.LANGUAGE_CD = @V_LANGUAGE_CD
		                  ,CI_FA_STEP D
		                  ,CI_PREM E
					left outer join CI_LANDLORD N on N.LL_ID = E.LL_ID
					left outer join CI_MR_WARN_L O on O.MR_WARN_CD = E.MR_WARN_CD AND O.LANGUAGE_CD = @V_LANGUAGE_CD
					left outer join CI_MR_INSTR_L P on P.MR_INSTR_CD = E.MR_INSTR_CD AND P.LANGUAGE_CD = @V_LANGUAGE_CD
		                  ,CI_LOOKUP F
		                  ,CI_FA_STEP_TYPE G
		                  ,CI_FA_STEP_TYPE_L H
		        WHERE A.FO_ID = @V_FROM_FO_ID
			              AND C.FO_ID = A.FO_ID
			              AND D.FA_ID = C.FA_ID
			              AND E.PREM_ID = A.PREM_ID
			              AND F.FIELD_NAME = @V_FIELD_NAME
			              AND F.FIELD_VALUE = D.FA_STEP_TY_ACT_FLG
			              AND F.LANGUAGE_CD = @V_LANGUAGE_CD			              AND G.FA_TYPE_CD = C.FA_TYPE_CD
			              AND G.STEP_SEQ_NBR = D.STEP_SEQ_NBR
			              AND H.FA_TYPE_CD = G.FA_TYPE_CD
			              AND H.STEP_SEQ_NBR = G.STEP_SEQ_NBR
			              AND H.LANGUAGE_CD = @V_LANGUAGE_CD
		        ORDER BY SORT_KEY, 
				E.STATE,
                		       E.CITY,
		                       E.POSTAL,
                		       E.ADDRESS1,
		                       A.PREM_ID,
		                       FO_SCHED_DTTM,
		                       C.SP_ID,
		                       C.FA_PRIORITY_FLG,
		                       C.FA_ID,
		                       D.STEP_SEQ_NBR;
		END
    	END
    	ELSE
    	BEGIN
      		SELECT MESSAGE_NBR RETURN_CODE
        	    ,MESSAGE_TEXT
	            ,@V_LANGUAGE_CD AS LANGUAGE_CD
        	    ,CAST(NULL AS CHAR) SORT_KEY, CAST(NULL AS CHAR) FO_ID, CAST(NULL AS CHAR) PREM_ID, CAST(NULL AS DATETIME) FO_SCHED_DTTM
	            ,CAST(NULL AS CHAR) FO_STATUS_FLG, CAST(NULL AS DATETIME) WORK_DTTM, CAST(NULL AS CHAR) DISP_GRP_CD, CAST(NULL AS CHAR) REP_CD, CAST(NULL AS CHAR) WORKED_BY
        	    ,CAST(NULL AS CHAR) EXTRACT_NEXT_SW, CAST(NULL AS DATETIME) EXTRACT_DTTM, CAST(NULL AS VARCHAR) FO_DESCR, CAST(NULL AS CHAR) BATCH_CD, CAST(NULL AS INT) BATCH_NBR
	            ,CAST(NULL AS CHAR) FA_ID, CAST(NULL AS CHAR) SP_ID, CAST(NULL AS CHAR) FA_TYPE_CD, CAST(NULL AS CHAR) FA_PRIORITY_FLG, CAST(NULL AS CHAR) FA_CREATED_BY_FLG
	            ,CAST(NULL AS DATETIME) FA_SCHED_DTTM, CAST(NULL AS CHAR) FA_STATUS_FLG, CAST(NULL AS CHAR) ELIG_DISPATCH_SW
        	    ,CAST(NULL AS DATETIME) CRE_DTTM, CAST(NULL AS VARCHAR) INSTRUCTIONS, CAST(NULL AS CHAR) TEST_SEL_ID, CAST(NULL AS VARCHAR) FA_DESCR
	            ,CAST(NULL AS CHAR) FA_CAN_RSN_CD, CAST(NULL AS INT) STEP_SEQ_NBR, CAST(NULL AS CHAR) FA_STEP_TY_ACT_FLG, CAST(NULL AS VARCHAR) FA_STEP_TY_DESCR, CAST(NULL AS CHAR) STP_ENTITY_FLG
	            ,CAST(NULL AS CHAR) SP_MTR_HIST_ID, CAST(NULL AS CHAR) SP_ITEM_HIST_ID, CAST(NULL AS CHAR) MR_ID, CAST(NULL AS CHAR) MTR_CFG_MTR_ID
        	    ,CAST(NULL AS CHAR) MTR_ID, CAST(NULL AS CHAR) ITEM_ID, CAST(NULL AS CHAR) CC_ID, CAST(NULL AS CHAR) SPAWNED_FA_ID, CAST(NULL AS CHAR) ACCT_ID
	            ,CAST(NULL AS CHAR) DV_TEST_ID, CAST(NULL AS CHAR) PREM_TYPE_CD, CAST(NULL AS CHAR) LL_ID, CAST(NULL AS CHAR) MAIL_ADDR_SW, CAST(NULL AS CHAR) KEY_SW
        	    ,CAST(NULL AS CHAR) KEY_ID, CAST(NULL AS CHAR) OK_TO_ENTER_SW, CAST(NULL AS CHAR) MR_INSTR_CD, CAST(NULL AS VARCHAR) MR_INSTR_DETAILS
	            ,CAST(NULL AS CHAR) MR_WARN_CD, CAST(NULL AS CHAR) TREND_AREA_CD, CAST(NULL AS CHAR) COUNTRY, CAST(NULL AS VARCHAR) ADDRESS1, CAST(NULL AS VARCHAR) ADDRESS2
        	    ,CAST(NULL AS VARCHAR) ADDRESS3, CAST(NULL AS VARCHAR) ADDRESS4, CAST(NULL AS VARCHAR) CITY, CAST(NULL AS CHAR) NUM1, CAST(NULL AS CHAR) NUM2, CAST(NULL AS CHAR) HOUSE_TYPE
	            ,CAST(NULL AS VARCHAR) COUNTY, CAST(NULL AS CHAR) STATE, CAST(NULL AS CHAR) POSTAL, CAST(NULL AS CHAR) GEO_CODE, CAST(NULL AS CHAR) IN_CITY_LIMIT
        	    ,CAST(NULL AS VARCHAR) FA_STEP_TYPE_DESCR, CAST(NULL AS CHAR) OPTIONAL_SW, CAST(NULL AS VARCHAR) REP_DESCR, CAST(NULL AS VARCHAR) FA_TYPE_DESCR
	            ,CAST(NULL AS VARCHAR) DISP_GRP_DESCR, CAST(NULL AS CHAR) LL_ACCT_ID, CAST(NULL AS VARCHAR) LL_DESCR
        	    ,CAST(NULL AS VARCHAR) MR_WARN_DESCR, CAST(NULL AS VARCHAR) MR_INSTR_DESCR
		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
