-- ******************************************************************
-- *
-- * 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 = 'CIBILLPR' AND type = 'P')
   DROP PROCEDURE CIBILLPR 
GO

CREATE PROCEDURE CIBILLPR
@P_USER_ID CHAR(8),
@P_BATCH_SW CHAR(1),			 
@P_FROM_BILL_ID CHAR(12),
@P_TO_BILL_ID  CHAR(12),
@P_BATCH_CD  CHAR(8),			 
@P_BATCH_NBR  INT,
@P_EXTRACT_ALG_CD CHAR(12)			 


AS
   DECLARE   @V_LANGUAGE_CD  CHAR(3)
   DECLARE   @V_RETURN_CD  INT
   DECLARE   @V_FROM_BILL_ID CHAR(12)
   DECLARE   @V_TO_BILL_ID CHAR(12)
   DECLARE   @V_BATCH_SW CHAR(1)
   DECLARE   @V_BATCH_CD  CHAR(8)
   DECLARE   @V_BATCH_NBR  INT
   DECLARE   @V_EXTRACT_ALG_CD CHAR(12)			 


   SELECT @V_RETURN_CD = 0
   SELECT @V_BATCH_NBR = 0
   SELECT @V_EXTRACT_ALG_CD= 0


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  -- ERROR: INVALID USER ID
            END

            IF @V_RETURN_CD = 0
            BEGIN
   	          IF   @P_BATCH_SW IS NULL OR @P_BATCH_SW = ' ' 
	  	  	SELECT @V_BATCH_SW = 'N'
	          ELSE 
	 	        IF  @P_BATCH_SW <> 'N' AND @P_BATCH_SW <> 'Y'
	  	             SELECT @V_RETURN_CD = 1032; -- ERROR: INVALID BATCH SW 
	 	  	ELSE 
	 		     SELECT @V_BATCH_SW = @P_BATCH_SW;
	          END 


	   IF @V_RETURN_CD = 0
	   BEGIN
	        IF @P_FROM_BILL_ID IS NULL OR @P_FROM_BILL_ID = ' '
	             SELECT @V_RETURN_CD = 1025 -- ERROR: FROM BILL ID IS REQUIRED 
	  	ELSE
	             IF @V_BATCH_SW = 'N' 
	 	     BEGIN
		            SELECT @V_FROM_BILL_ID=BILL_ID
   	  		    FROM	CI_BILL
	  	 	    WHERE  BILL_ID = @P_FROM_BILL_ID
   		            IF (@@ROWCOUNT = 0)
		  	    BEGIN
	  		         SELECT @V_RETURN_CD = 1027 -- ERROR: INVALID BILL ID
     	                    END
	 	     END
		     ELSE 
		            SELECT @V_FROM_BILL_ID = @P_FROM_BILL_ID;
          END

	  IF @V_RETURN_CD = 0
	  BEGIN
		IF @V_BATCH_SW = 'Y' 
		BEGIN
	              IF @P_TO_BILL_ID IS NULL OR @P_TO_BILL_ID = ' '
	  		  SELECT @V_RETURN_CD = 1026 -- ERROR: TO BILL ID IS REQUIRED IF BATCH SW = Y
		      ELSE
			  SELECT @V_TO_BILL_ID = @P_TO_BILL_ID;
		END
	  END

	  IF @V_RETURN_CD = 0 
	  BEGIN
	        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     
	  END

	  IF @V_RETURN_CD = 0  
	  BEGIN
	        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
	  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 = 1023 -- ERROR: BILL 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 = 1024 -- ERROR: INVALID BILL EXTRACT ALGORITHM
	  	        END
	        
		END
	  END
	
	  IF @V_RETURN_CD = 0
	  BEGIN
--- Bill Routings are joined to Bill Segments, Bill Calc Headers and Bill Calc Lines, and to BMSGBLPRCVW, 
--- which returns a COUNT of the bill's messages.

--- The first 2 UNION returns a row for a single bill-id online (only 1 copy, only routing sequence 1). 

---The rest of the UNION (4 SQL) returns rows for each copy OF each bill routing for a given language code, 
---range OF Bill IDs, batch code and sequence code.

---A UNION is required TO avoid using OR criteria on whether BATCH_SW = Y OR N
---1ST SQL
	           SELECT @V_RETURN_CD RETURN_CODE
                  ,CAST(NULL AS VARCHAR) MESSAGE_TEXT
                  ,A.SORT_KEY
                  ,A.BILL_ID
                  ,A.SEQNO
                  ,A.PER_ID
                  ,A.NBR_BILL_COPIES
                  ,A.ENTITY_NAME1
                  ,A.ENTITY_NAME2
                  ,A.ENTITY_NAME3
                  ,A.COUNTRY
                  ,A.ADDRESS1
                  ,A.ADDRESS2
                  ,A.ADDRESS3
                  ,A.CITY
                  ,A.COUNTY
                  ,A.STATE
                  ,A.POSTAL
                  ,A.IN_CITY_LIMIT
                  ,A.BATCH_CD
                  ,A.BATCH_NBR
                  ,A.NO_BATCH_PRT_SW
                  ,A.ACCT_ID
                  ,A.BILL_STAT_FLG
                  ,A.BILL_DT
                  ,A.DUE_DT
                  ,A.ACCT_ENTITY_NAME
                  ,A.LANGUAGE_CD
                  ,A.CURRENCY_CD
                  ,A.CUR_SYMBOL
                  ,A.DECIMAL_POSITIONS
                  ,A.CUR_POS_FLG
                  ,A.OPEN_ITEM_SW
                  ,A.BILL_COPY
                  ,B.BSEG_ID
                  ,B.SA_ID
                  ,B.START_DT
                  ,B.END_DT
                  ,B.BILL_CYC_CD
                  ,B.WIN_START_DT
                  ,B.EST_SW, B.CLOSING_BSEG_SW, B.PREM_ID, B.CIS_DIVISION, B.SA_TYPE_CD, B.SAT_DESCR, B.BILL_PRT_PRIO_FLG 
 		  ,B.GRAPH_UOM_CD, B.HEADER_SEQ, B.BCALC_START_DT, B.BCALC_END_DT, B.RS_CD, B.EFFDT, B.BILLABLE_CHG_ID 
		  ,B.CALC_AMT, B.BCALC_DESC_BILL, B.SEQNO, B.PRT_SW, B.APP_IN_SUMM_SW, B.CALC_LN_AMT 
		  ,B.DST_ID, B.MSR_PEAK_QTY_SW, B.DESCR_LN, B.STATE, B.CITY, B.POSTAL, B.SIBLING_ID 	    
		 FROM 
	     --- 1ST MAIN: CI_BILL_PRT_VW ( Bill Print View)  
	     --- This view joins Bill to Bill Routing and Account-Person-Name. 
	     --- The UNION is used to produce 1 row for each copy required for the routing. The UNION should be 
	     --- repeated as many times as required to handle the maximum possible number of copies.

		    (SELECT R.BILL_ID AS SORT_KEY, R.BILL_ID, R.SEQNO, R.PER_ID, R.NBR_BILL_COPIES, R.ENTITY_NAME1, 
		     R.ENTITY_NAME2, R.ENTITY_NAME3, R.COUNTRY, R.ADDRESS1, R.ADDRESS2,R.ADDRESS3, R.CITY, R.COUNTY, 
		     R.STATE, R.POSTAL,  R.IN_CITY_LIMIT, R.BATCH_CD, R.BATCH_NBR, R.NO_BATCH_PRT_SW, R.BILL_RTE_TYPE_CD,	
		     B.ACCT_ID, B.BILL_STAT_FLG, B.BILL_DT, B.DUE_DT, A.ACCT_ENTITY_NAME, A.LANGUAGE_CD, A.CURRENCY_CD,
		     A.CUR_SYMBOL, A.DECIMAL_POSITIONS, A.CUR_POS_FLG, A.OPEN_ITEM_SW, 1 AS BILL_COPY 
	  	     FROM CI_BILL_ROUTING R , CI_BILL B 
	               , (SELECT A.ACCT_ID, B.ENTITY_NAME AS ACCT_ENTITY_NAME, C.LANGUAGE_CD, E.CUST_CL_CD, 
	                         E.OPEN_ITEM_SW, F.CURRENCY_CD, F.CUR_SYMBOL, F.DECIMAL_POSITIONS, F.CUR_POS_FLG 
	                  FROM CI_ACCT_PER A, CI_PER_NAME B, CI_PER C, CI_ACCT D, CI_CUST_CL E, CI_CURRENCY_CD F 
	                  WHERE A.PER_ID = B.PER_ID  AND A.MAIN_CUST_SW = 'Y' 
	                         AND B.NAME_TYPE_FLG = 'PRIM' AND B.PRIM_NAME_SW = 'Y' 
	                         AND C.PER_ID = A.PER_ID AND D.ACCT_ID = A.ACCT_ID 
	                         AND E.CUST_CL_CD = D.CUST_CL_CD AND F.CURRENCY_CD = D.CURRENCY_CD) A -- Account Person Name View   
		     WHERE B.BILL_ID = R.BILL_ID
	    	      AND B.BILL_STAT_FLG = 'C' 
	              AND A.ACCT_ID = B.ACCT_ID 
	              AND R.NBR_BILL_COPIES >= 1
	     	   UNION 	  
	          SELECT R.BILL_ID AS SORT_KEY, R.BILL_ID, R.SEQNO, R.PER_ID, R.NBR_BILL_COPIES, R.ENTITY_NAME1, 
			    R.ENTITY_NAME2, R.ENTITY_NAME3, R.COUNTRY, R.ADDRESS1, R.ADDRESS2,R.ADDRESS3, R.CITY, R.COUNTY, 
			    R.STATE, R.POSTAL,  R.IN_CITY_LIMIT, R.BATCH_CD, R.BATCH_NBR, R.NO_BATCH_PRT_SW, R.BILL_RTE_TYPE_CD,	
			    B.ACCT_ID, B.BILL_STAT_FLG, B.BILL_DT, B.DUE_DT, A.ACCT_ENTITY_NAME, A.LANGUAGE_CD, A.CURRENCY_CD,
			    A.CUR_SYMBOL, A.DECIMAL_POSITIONS, A.CUR_POS_FLG, A.OPEN_ITEM_SW, 2 AS BILL_COPY
	  	  FROM CI_BILL_ROUTING R 
	               , CI_BILL B 
	               , (SELECT A.ACCT_ID, B.ENTITY_NAME AS ACCT_ENTITY_NAME, C.LANGUAGE_CD, E.CUST_CL_CD, 
	                         E.OPEN_ITEM_SW, F.CURRENCY_CD, F.CUR_SYMBOL, F.DECIMAL_POSITIONS, F.CUR_POS_FLG 
	                  FROM CI_ACCT_PER A, CI_PER_NAME B, CI_PER C, CI_ACCT D, CI_CUST_CL E, CI_CURRENCY_CD F 
	                  WHERE A.PER_ID = B.PER_ID  AND A.MAIN_CUST_SW = 'Y' 
	                         AND B.NAME_TYPE_FLG = 'PRIM' AND B.PRIM_NAME_SW = 'Y' 
	                         AND C.PER_ID = A.PER_ID AND D.ACCT_ID = A.ACCT_ID 
	                         AND E.CUST_CL_CD = D.CUST_CL_CD AND F.CURRENCY_CD = D.CURRENCY_CD) A -- Account Person Name View   
	          WHERE B.BILL_ID = R.BILL_ID
	  	      AND B.BILL_STAT_FLG = 'C' 
	              AND A.ACCT_ID = B.ACCT_ID 
	              AND R.NBR_BILL_COPIES >= 2
		     UNION 			  
		     SELECT R.BILL_ID AS SORT_KEY, R.BILL_ID, R.SEQNO, R.PER_ID, R.NBR_BILL_COPIES, R.ENTITY_NAME1, 
			    R.ENTITY_NAME2, R.ENTITY_NAME3, R.COUNTRY, R.ADDRESS1, R.ADDRESS2,R.ADDRESS3, R.CITY, R.COUNTY, 
			    R.STATE, R.POSTAL,  R.IN_CITY_LIMIT, R.BATCH_CD, R.BATCH_NBR, R.NO_BATCH_PRT_SW, R.BILL_RTE_TYPE_CD,	
			    B.ACCT_ID, B.BILL_STAT_FLG, B.BILL_DT, B.DUE_DT, A.ACCT_ENTITY_NAME, A.LANGUAGE_CD, A.CURRENCY_CD,
			    A.CUR_SYMBOL, A.DECIMAL_POSITIONS, A.CUR_POS_FLG, A.OPEN_ITEM_SW, 3 AS BILL_COPY 
		     FROM CI_BILL_ROUTING R 
	               , CI_BILL B 
	               , (SELECT A.ACCT_ID, B.ENTITY_NAME AS ACCT_ENTITY_NAME, C.LANGUAGE_CD, E.CUST_CL_CD, 
	                         E.OPEN_ITEM_SW, F.CURRENCY_CD, F.CUR_SYMBOL, F.DECIMAL_POSITIONS, F.CUR_POS_FLG 
	                    FROM CI_ACCT_PER A, CI_PER_NAME B, CI_PER C, CI_ACCT D, CI_CUST_CL E, CI_CURRENCY_CD F 
	                   WHERE A.PER_ID = B.PER_ID  AND A.MAIN_CUST_SW = 'Y' 
	                         AND B.NAME_TYPE_FLG = 'PRIM' AND B.PRIM_NAME_SW = 'Y' 
	                         AND C.PER_ID = A.PER_ID AND D.ACCT_ID = A.ACCT_ID 
	                         AND E.CUST_CL_CD = D.CUST_CL_CD AND F.CURRENCY_CD = D.CURRENCY_CD) A -- Account Person Name View   
	            WHERE B.BILL_ID = R.BILL_ID
	  	      AND B.BILL_STAT_FLG = 'C' 
	              AND A.ACCT_ID = B.ACCT_ID 
	              AND R.NBR_BILL_COPIES >= 3) A,  --- 1ST MAIN END: CI_BILL_PRT_VW

    --- 2ND MAIN: CI_BILL_DTL_VW ( Bill Details View)  
    --- This view joins Bill Seg, Prem, SA, SA TYPE, Bill Calc, Bill Calc Lines, FT (TYPE BS). 
    --- There IS a blank ADJ_ID field tO allow unioning in the query WITH CI_BILL_DTL2VW

			(SELECT B.BILL_ID, B.BSEG_ID, B.SA_ID, B.START_DT, B.END_DT, B.BILL_CYC_CD, B.WIN_START_DT, B.EST_SW,
			        B.CLOSING_BSEG_SW, B.PREM_ID, P.STATE, P.CITY, P.POSTAL, S.CIS_DIVISION, S.SA_TYPE_CD, 
				TL.DESCR AS SAT_DESCR, TL.LANGUAGE_CD, T.BILL_PRT_PRIO_FLG, T.GRAPH_UOM_CD,	C.HEADER_SEQ, 
				C.START_DT AS BCALC_START_DT, C.END_DT AS BCALC_END_DT, C.RS_CD, C.EFFDT, C.BILLABLE_CHG_ID,
				C.CALC_AMT, C.DESCR_ON_BILL AS BCALC_DESC_BILL, L.SEQNO, L.PRT_SW,  
				L.APP_IN_SUMM_SW, L.CALC_AMT AS CALC_LN_AMT, L.DST_ID, L.MSR_PEAK_QTY_SW, L.DESCR_ON_BILL AS DESCR_LN, 
				' ' AS SIBLING_ID  
		         FROM 
		                 CI_FT F 
		               , CI_SA S 
		               , CI_PREM P RIGHT OUTER JOIN CI_BSEG B ON P.PREM_ID   = B.PREM_ID 
		               , CI_BSEG_CALC C 
		               , CI_BSEG_CALC_LN L 
		               , CI_SA_TYPE T
		               , CI_SA_TYPE_L TL
		         WHERE F.SIBLING_ID = B.BSEG_ID 
		             AND F.FT_TYPE_FLG = 'BS' 
		             AND F.CORRECTION_SW = 'N' 
		             AND F.SHOW_ON_BILL_SW = 'Y' 
		             AND S.SA_ID = B.SA_ID 
		             AND P.PREM_ID   = B.PREM_ID 
		             AND T.CIS_DIVISION = S.CIS_DIVISION 
		             AND T.SA_TYPE_CD = S.SA_TYPE_CD
		             AND TL.CIS_DIVISION = T.CIS_DIVISION 
		             AND TL.SA_TYPE_CD = T.SA_TYPE_CD 
		             AND C.BSEG_ID = B.BSEG_ID 
		             AND L.BSEG_ID = C.BSEG_ID 
		             AND L.HEADER_SEQ = C.HEADER_SEQ) B   --- 2ND MAIN: CI_BILL_DTL_VW
		   WHERE A.LANGUAGE_CD = B.LANGUAGE_CD 
		     AND 'N' = @V_BATCH_SW 
		     AND A.BILL_ID = @V_FROM_BILL_ID 
		     AND A.BILL_COPY = 1 
		     AND A.SEQNO = 1 
		     AND A.BILL_ID = B.BILL_ID 
	   UNION 
--- 2nd SQL
		SELECT @V_RETURN_CD RETURN_CODE, CAST(NULL AS VARCHAR) MESSAGE_TEXT,
		       C.SORT_KEY, C.BILL_ID, C.SEQNO, C.PER_ID, C.NBR_BILL_COPIES, C.ENTITY_NAME1, C.ENTITY_NAME2, C.ENTITY_NAME3, 
		       C.COUNTRY, C.ADDRESS1, C.ADDRESS2, C.ADDRESS3, C.CITY, C.COUNTY, C.STATE, C.POSTAL, C.IN_CITY_LIMIT, 
		       C.BATCH_CD, C.BATCH_NBR, C.NO_BATCH_PRT_SW, C.ACCT_ID, C.BILL_STAT_FLG, C.BILL_DT, C.DUE_DT, 
	  	       C.ACCT_ENTITY_NAME, C.LANGUAGE_CD, C.CURRENCY_CD, C.CUR_SYMBOL, C.DECIMAL_POSITIONS, C.CUR_POS_FLG, 
		       C.OPEN_ITEM_SW, C.BILL_COPY, D.BSEG_ID, D.SA_ID, D.START_DT, D.END_DT, D.BILL_CYC_CD, D.WIN_START_DT, 
			   D.EST_SW, D.CLOSING_BSEG_SW, D.PREM_ID, D.CIS_DIVISION, D.SA_TYPE_CD, D.SAT_DESCR, D.BILL_PRT_PRIO_FLG, 
		   D.GRAPH_UOM_CD, D.HEADER_SEQ, D.BCALC_START_DT, D.BCALC_END_DT, D.RS_CD, D.EFFDT, D.BILLABLE_CHG_ID, 
		   D.CALC_AMT, D.BCALC_DESC_BILL, D.SEQNO, D.PRT_SW, D.APP_IN_SUMM_SW, D.CALC_LN_AMT, 
		   D.DST_ID, D.MSR_PEAK_QTY_SW, 
		   D.DESCR_LN, D.STATE, D.CITY, D.POSTAL, D.SIBLING_ID 	    
		FROM 
    --- 1ST MAIN FOR 2ND SQL: CI_BILL_PRT_VW ( Bill Print View)  
    --- This view joins Bill to Bill Routing and Account-Person-Name. 
    --- The UNION is used to produce 1 row for each copy required for the routing. The UNION should be 
	--- repeated as many times as required to handle the maximum possible number of copies.

		    (SELECT R.BILL_ID AS SORT_KEY, R.BILL_ID, R.SEQNO, R.PER_ID, R.NBR_BILL_COPIES, R.ENTITY_NAME1, 
		        R.ENTITY_NAME2, R.ENTITY_NAME3, R.COUNTRY, R.ADDRESS1, R.ADDRESS2,R.ADDRESS3, R.CITY, R.COUNTY, 
			R.STATE, R.POSTAL,  R.IN_CITY_LIMIT, R.BATCH_CD, R.BATCH_NBR, R.NO_BATCH_PRT_SW, R.BILL_RTE_TYPE_CD,	
			B.ACCT_ID, B.BILL_STAT_FLG, B.BILL_DT, B.DUE_DT, A.ACCT_ENTITY_NAME, A.LANGUAGE_CD, A.CURRENCY_CD,
			A.CUR_SYMBOL, A.DECIMAL_POSITIONS, A.CUR_POS_FLG, A.OPEN_ITEM_SW, 1 AS BILL_COPY 
	  	        FROM CI_BILL_ROUTING R 
        	       , CI_BILL B 
	               , (SELECT A.ACCT_ID, B.ENTITY_NAME AS ACCT_ENTITY_NAME, C.LANGUAGE_CD, E.CUST_CL_CD, 
        	                 E.OPEN_ITEM_SW, F.CURRENCY_CD, F.CUR_SYMBOL, F.DECIMAL_POSITIONS, F.CUR_POS_FLG 
                	    FROM CI_ACCT_PER A, CI_PER_NAME B, CI_PER C, CI_ACCT D, CI_CUST_CL E, CI_CURRENCY_CD F 
	                   WHERE A.PER_ID = B.PER_ID  AND A.MAIN_CUST_SW = 'Y' 
	                         AND B.NAME_TYPE_FLG = 'PRIM' AND B.PRIM_NAME_SW = 'Y' 
	                         AND C.PER_ID = A.PER_ID AND D.ACCT_ID = A.ACCT_ID 
	                         AND E.CUST_CL_CD = D.CUST_CL_CD AND F.CURRENCY_CD = D.CURRENCY_CD) A -- Account Person Name View   
	            WHERE B.BILL_ID = R.BILL_ID
  			  AND B.BILL_STAT_FLG = 'C' 
        		  AND A.ACCT_ID = B.ACCT_ID 
		          AND R.NBR_BILL_COPIES >= 1
	     UNION 	  
		     SELECT R.BILL_ID AS SORT_KEY, R.BILL_ID, R.SEQNO, R.PER_ID, R.NBR_BILL_COPIES, R.ENTITY_NAME1, 
		        R.ENTITY_NAME2, R.ENTITY_NAME3, R.COUNTRY, R.ADDRESS1, R.ADDRESS2,R.ADDRESS3, R.CITY, R.COUNTY, 
			R.STATE, R.POSTAL,  R.IN_CITY_LIMIT, R.BATCH_CD, R.BATCH_NBR, R.NO_BATCH_PRT_SW, R.BILL_RTE_TYPE_CD,	
			B.ACCT_ID, B.BILL_STAT_FLG, B.BILL_DT, B.DUE_DT, A.ACCT_ENTITY_NAME, A.LANGUAGE_CD, A.CURRENCY_CD,
			A.CUR_SYMBOL, A.DECIMAL_POSITIONS, A.CUR_POS_FLG, A.OPEN_ITEM_SW, 2 AS BILL_COPY
	  	        FROM CI_BILL_ROUTING R 
        	       , CI_BILL B 
	               , (SELECT A.ACCT_ID, B.ENTITY_NAME AS ACCT_ENTITY_NAME, C.LANGUAGE_CD, E.CUST_CL_CD, 
	                         E.OPEN_ITEM_SW, F.CURRENCY_CD, F.CUR_SYMBOL, F.DECIMAL_POSITIONS, F.CUR_POS_FLG
	                    FROM CI_ACCT_PER A, CI_PER_NAME B, CI_PER C, CI_ACCT D, CI_CUST_CL E, CI_CURRENCY_CD F 
	                   WHERE A.PER_ID = B.PER_ID  AND A.MAIN_CUST_SW = 'Y' 
        	                 AND B.NAME_TYPE_FLG = 'PRIM' AND B.PRIM_NAME_SW = 'Y' 
	                         AND C.PER_ID = A.PER_ID AND D.ACCT_ID = A.ACCT_ID 
        	                 AND E.CUST_CL_CD = D.CUST_CL_CD AND F.CURRENCY_CD = D.CURRENCY_CD) A -- Account Person Name View   
		     WHERE B.BILL_ID = R.BILL_ID
			  AND B.BILL_STAT_FLG = 'C' 
		          AND A.ACCT_ID = B.ACCT_ID 
	                  AND R.NBR_BILL_COPIES >= 2
	     UNION 			  
		     SELECT R.BILL_ID AS SORT_KEY, R.BILL_ID, R.SEQNO, R.PER_ID, R.NBR_BILL_COPIES, R.ENTITY_NAME1, 
		        R.ENTITY_NAME2, R.ENTITY_NAME3, R.COUNTRY, R.ADDRESS1, R.ADDRESS2,R.ADDRESS3, R.CITY, R.COUNTY, 
			R.STATE, R.POSTAL,  R.IN_CITY_LIMIT, R.BATCH_CD, R.BATCH_NBR, R.NO_BATCH_PRT_SW, R.BILL_RTE_TYPE_CD,	
			B.ACCT_ID, B.BILL_STAT_FLG, B.BILL_DT, B.DUE_DT, A.ACCT_ENTITY_NAME, A.LANGUAGE_CD, A.CURRENCY_CD,
			A.CUR_SYMBOL, A.DECIMAL_POSITIONS, A.CUR_POS_FLG, A.OPEN_ITEM_SW, 3 AS BILL_COPY 
	 	     FROM CI_BILL_ROUTING R 
	               , CI_BILL B 
	               , (SELECT A.ACCT_ID, B.ENTITY_NAME AS ACCT_ENTITY_NAME, C.LANGUAGE_CD, E.CUST_CL_CD, 
	                         E.OPEN_ITEM_SW, F.CURRENCY_CD, F.CUR_SYMBOL, F.DECIMAL_POSITIONS, F.CUR_POS_FLG 
	                    FROM CI_ACCT_PER A, CI_PER_NAME B, CI_PER C, CI_ACCT D, CI_CUST_CL E, CI_CURRENCY_CD F 
	                   WHERE A.PER_ID = B.PER_ID  AND A.MAIN_CUST_SW = 'Y' 
	                         AND B.NAME_TYPE_FLG = 'PRIM' AND B.PRIM_NAME_SW = 'Y' 
	                         AND C.PER_ID = A.PER_ID AND D.ACCT_ID = A.ACCT_ID 
	                         AND E.CUST_CL_CD = D.CUST_CL_CD AND F.CURRENCY_CD = D.CURRENCY_CD) A -- Account Person Name View   
        	    WHERE B.BILL_ID = R.BILL_ID
			  AND B.BILL_STAT_FLG = 'C' 
		          AND A.ACCT_ID = B.ACCT_ID 
	                  AND R.NBR_BILL_COPIES >= 3) C,  --- 1ST MAIN FOR 2ND SQL END: CI_BILL_PRT_VW

    --- 2ND MAIN FOR 2ND SQL: CI_BILL_DTL2VW ( Bill Details 2 View)
	--- This view joins FT, SA, Prem, SA Type using a record structure same as CI_BILL_DTL_VW   

			(SELECT A.BILL_ID, ' ' AS BSEG_ID, A.SA_ID, A.ACCOUNTING_DT AS START_DT, A.ACCOUNTING_DT AS END_DT, 
		        ' ' AS BILL_CYC_CD, A.ACCOUNTING_DT AS WIN_START_DT, ' ' AS EST_SW, ' ' AS CLOSING_BSEG_SW, 
				S.CHAR_PREM_ID AS PREM_ID, P.STATE, P.CITY, P.POSTAL, 
				S.CIS_DIVISION, S.SA_TYPE_CD, TL.DESCR AS SAT_DESCR, TL.LANGUAGE_CD, T.BILL_PRT_PRIO_FLG, T.GRAPH_UOM_CD, 
				0 AS HEADER_SEQ, A.ACCOUNTING_DT AS BCALC_START_DT, A.ACCOUNTING_DT AS BCALC_END_DT, ' ' AS RS_CD, 
				A.ACCOUNTING_DT AS EFFDT, ' ' AS BILLABLE_CHG_ID, 0 AS CALC_AMT,  
				' ' AS BCALC_DESC_BILL, 0 AS SEQNO, ' ' AS PRT_SW, ' ' AS APP_IN_SUMM_SW, 0 AS CALC_LN_AMT,  
		            ' ' AS DST_ID, ' ' AS MSR_PEAK_QTY_SW, ' ' AS DESCR_LN, A.SIBLING_ID
		            FROM CI_FT A 
		               , CI_PREM P RIGHT OUTER JOIN  CI_SA S ON P.PREM_ID   = S.CHAR_PREM_ID 
		               , CI_SA_TYPE T
		               , CI_SA_TYPE_L TL 
		           WHERE S.SA_ID = A.SA_ID 
		             AND P.PREM_ID   = S.CHAR_PREM_ID 
		             AND T.CIS_DIVISION = S.CIS_DIVISION 
        		     AND T.SA_TYPE_CD = S.SA_TYPE_CD 
		             AND TL.CIS_DIVISION = T.CIS_DIVISION 
		             AND TL.SA_TYPE_CD = T.SA_TYPE_CD 
			     AND A.SHOW_ON_BILL_SW = 'Y'
	  		     AND A.FT_TYPE_FLG IN ('AD','AX','PS','PX')) D   --- 2ND MAIN FOR SQL: CI_BILL_DTL2VW
		      WHERE C.LANGUAGE_CD = D.LANGUAGE_CD 
			     AND 'N' = @V_BATCH_SW 
			     AND C.BILL_ID = @V_FROM_BILL_ID 
			     AND C.BILL_COPY = 1 
			     AND C.SEQNO = 1 
			     AND C.BILL_ID = D.BILL_ID 
	  UNION
--- 3rd SQL
		SELECT @V_RETURN_CD RETURN_CODE, CAST(NULL AS VARCHAR) MESSAGE_TEXT,
		   E.SORT_KEY, E.BILL_ID, E.SEQNO, E.PER_ID, E.NBR_BILL_COPIES, E.ENTITY_NAME1, E.ENTITY_NAME2, E.ENTITY_NAME3, 
		   E.COUNTRY, E.ADDRESS1, E.ADDRESS2, E.ADDRESS3, E.CITY, E.COUNTY, E.STATE, E.POSTAL, E.IN_CITY_LIMIT, 
		   E.BATCH_CD, E.BATCH_NBR, E.NO_BATCH_PRT_SW, E.ACCT_ID, E.BILL_STAT_FLG, E.BILL_DT, E.DUE_DT, 
		   E.ACCT_ENTITY_NAME, E.LANGUAGE_CD, E.CURRENCY_CD, E.CUR_SYMBOL, E.DECIMAL_POSITIONS, E.CUR_POS_FLG, 
		   E.OPEN_ITEM_SW, E.BILL_COPY, F.BSEG_ID, F.SA_ID, F.START_DT, F.END_DT, F.BILL_CYC_CD, F.WIN_START_DT, 
		   F.EST_SW, F.CLOSING_BSEG_SW, F.PREM_ID, F.CIS_DIVISION, F.SA_TYPE_CD, F.SAT_DESCR, F.BILL_PRT_PRIO_FLG, 
		   F.GRAPH_UOM_CD, F.HEADER_SEQ, F.BCALC_START_DT, F.BCALC_END_DT, F.RS_CD, F.EFFDT, F.BILLABLE_CHG_ID, 
		   F.CALC_AMT, F.BCALC_DESC_BILL, F.SEQNO, F.PRT_SW, F.APP_IN_SUMM_SW, F.CALC_LN_AMT, 
		   F.DST_ID, F.MSR_PEAK_QTY_SW, F.DESCR_LN, F.STATE, F.CITY, F.POSTAL, F.SIBLING_ID 	    
		FROM 
    --- 1ST MAIN FOR 3RD SQL: CI_BILL_PRT_VW ( Bill Print View)  
    --- This view joins Bill to Bill Routing and Account-Person-Name. 
    --- The UNION is used to produce 1 row for each copy required for the routing. The UNION should be 
	--- repeated as many times as required to handle the maximum possible number of copies.

		    (SELECT R.BILL_ID AS SORT_KEY, R.BILL_ID, R.SEQNO, R.PER_ID, R.NBR_BILL_COPIES, R.ENTITY_NAME1, 
	        	R.ENTITY_NAME2, R.ENTITY_NAME3, R.COUNTRY, R.ADDRESS1, R.ADDRESS2,R.ADDRESS3, R.CITY, R.COUNTY, 
			R.STATE, R.POSTAL,  R.IN_CITY_LIMIT, R.BATCH_CD, R.BATCH_NBR, R.NO_BATCH_PRT_SW, R.BILL_RTE_TYPE_CD,	
			B.ACCT_ID, B.BILL_STAT_FLG, B.BILL_DT, B.DUE_DT, A.ACCT_ENTITY_NAME, A.LANGUAGE_CD, A.CURRENCY_CD,
			A.CUR_SYMBOL, A.DECIMAL_POSITIONS, A.CUR_POS_FLG, A.OPEN_ITEM_SW, 1 AS BILL_COPY 
	  	        FROM CI_BILL_ROUTING R 
        	       , CI_BILL B 
	               , (SELECT A.ACCT_ID, B.ENTITY_NAME AS ACCT_ENTITY_NAME, C.LANGUAGE_CD, E.CUST_CL_CD, 
        	                 E.OPEN_ITEM_SW, F.CURRENCY_CD, F.CUR_SYMBOL, F.DECIMAL_POSITIONS, F.CUR_POS_FLG 
                	    FROM CI_ACCT_PER A, CI_PER_NAME B, CI_PER C, CI_ACCT D, CI_CUST_CL E, CI_CURRENCY_CD F 
	                   WHERE A.PER_ID = B.PER_ID  AND A.MAIN_CUST_SW = 'Y' 
	                         AND B.NAME_TYPE_FLG = 'PRIM' AND B.PRIM_NAME_SW = 'Y' 
	                         AND C.PER_ID = A.PER_ID AND D.ACCT_ID = A.ACCT_ID 
	                         AND E.CUST_CL_CD = D.CUST_CL_CD AND F.CURRENCY_CD = D.CURRENCY_CD) A -- Account Person Name View   
	            WHERE B.BILL_ID = R.BILL_ID
			  AND B.BILL_STAT_FLG = 'C' 
	                  AND A.ACCT_ID = B.ACCT_ID 
		          AND R.NBR_BILL_COPIES >= 1
	 UNION 	  
		SELECT R.BILL_ID AS SORT_KEY, R.BILL_ID, R.SEQNO, R.PER_ID, R.NBR_BILL_COPIES, R.ENTITY_NAME1, 
		        R.ENTITY_NAME2, R.ENTITY_NAME3, R.COUNTRY, R.ADDRESS1, R.ADDRESS2,R.ADDRESS3, R.CITY, R.COUNTY, 
			R.STATE, R.POSTAL,  R.IN_CITY_LIMIT, R.BATCH_CD, R.BATCH_NBR, R.NO_BATCH_PRT_SW, R.BILL_RTE_TYPE_CD,	
			B.ACCT_ID, B.BILL_STAT_FLG, B.BILL_DT, B.DUE_DT, A.ACCT_ENTITY_NAME, A.LANGUAGE_CD, A.CURRENCY_CD,
			A.CUR_SYMBOL, A.DECIMAL_POSITIONS, A.CUR_POS_FLG, A.OPEN_ITEM_SW, 2 AS BILL_COPY 
  	        FROM CI_BILL_ROUTING R 
               		, CI_BILL B 
	               , (SELECT A.ACCT_ID, B.ENTITY_NAME AS ACCT_ENTITY_NAME, C.LANGUAGE_CD, E.CUST_CL_CD, 
	                         E.OPEN_ITEM_SW, F.CURRENCY_CD, F.CUR_SYMBOL, F.DECIMAL_POSITIONS, F.CUR_POS_FLG 
	                   FROM CI_ACCT_PER A, CI_PER_NAME B, CI_PER C, CI_ACCT D, CI_CUST_CL E, CI_CURRENCY_CD F 
	                   WHERE A.PER_ID = B.PER_ID  AND A.MAIN_CUST_SW = 'Y' 
	                         AND B.NAME_TYPE_FLG = 'PRIM' AND B.PRIM_NAME_SW = 'Y' 
	                         AND C.PER_ID = A.PER_ID AND D.ACCT_ID = A.ACCT_ID 
	                         AND E.CUST_CL_CD = D.CUST_CL_CD AND F.CURRENCY_CD = D.CURRENCY_CD) A -- Account Person Name View   
	        WHERE B.BILL_ID = R.BILL_ID
		      AND B.BILL_STAT_FLG = 'C' 
		      AND A.ACCT_ID = B.ACCT_ID 
	              AND R.NBR_BILL_COPIES >= 2
	UNION 			  
		 SELECT R.BILL_ID AS SORT_KEY, R.BILL_ID, R.SEQNO, R.PER_ID, R.NBR_BILL_COPIES, R.ENTITY_NAME1, 
		        R.ENTITY_NAME2, R.ENTITY_NAME3, R.COUNTRY, R.ADDRESS1, R.ADDRESS2,R.ADDRESS3, R.CITY, R.COUNTY, 
			R.STATE, R.POSTAL,  R.IN_CITY_LIMIT, R.BATCH_CD, R.BATCH_NBR, R.NO_BATCH_PRT_SW, R.BILL_RTE_TYPE_CD,	
			B.ACCT_ID, B.BILL_STAT_FLG, B.BILL_DT, B.DUE_DT, A.ACCT_ENTITY_NAME, A.LANGUAGE_CD, A.CURRENCY_CD,
			A.CUR_SYMBOL, A.DECIMAL_POSITIONS, A.CUR_POS_FLG, A.OPEN_ITEM_SW, 3 AS BILL_COPY 
  	        FROM CI_BILL_ROUTING R 
	               , CI_BILL B 
	               , (SELECT A.ACCT_ID, B.ENTITY_NAME AS ACCT_ENTITY_NAME, C.LANGUAGE_CD, E.CUST_CL_CD, 
	                         E.OPEN_ITEM_SW, F.CURRENCY_CD, F.CUR_SYMBOL, F.DECIMAL_POSITIONS, F.CUR_POS_FLG 
	                    FROM CI_ACCT_PER A, CI_PER_NAME B, CI_PER C, CI_ACCT D, CI_CUST_CL E, CI_CURRENCY_CD F 
	                   WHERE A.PER_ID = B.PER_ID  AND A.MAIN_CUST_SW = 'Y' 
	                         AND B.NAME_TYPE_FLG = 'PRIM' AND B.PRIM_NAME_SW = 'Y' 
	                         AND C.PER_ID = A.PER_ID AND D.ACCT_ID = A.ACCT_ID 
	                         AND E.CUST_CL_CD = D.CUST_CL_CD AND F.CURRENCY_CD = D.CURRENCY_CD) A -- Account Person Name View   
	        WHERE B.BILL_ID = R.BILL_ID
  	  	      AND B.BILL_STAT_FLG = 'C' 
	              AND A.ACCT_ID = B.ACCT_ID 
                      AND R.NBR_BILL_COPIES >= 3) E,  --- 1ST MAIN FOR 3RD SQL END: CI_BILL_PRT_VW

    --- 2ND MAIN: CI_BILL_DTL_VW ( Bill Details View)  
    --- This view joins Bill Seg, Prem, SA, SA TYPE, Bill Calc, Bill Calc Lines, FT (TYPE BS). 
    --- There IS a blank ADJ_ID field tO allow unioning in the query WITH CI_BILL_DTL2VW

		(SELECT B.BILL_ID, B.BSEG_ID, B.SA_ID, B.START_DT, B.END_DT, B.BILL_CYC_CD, B.WIN_START_DT, B.EST_SW,
		        B.CLOSING_BSEG_SW, B.PREM_ID, P.STATE, P.CITY, P.POSTAL, 
		        S.CIS_DIVISION, S.SA_TYPE_CD, TL.DESCR AS SAT_DESCR, TL.LANGUAGE_CD, T.BILL_PRT_PRIO_FLG, T.GRAPH_UOM_CD, 
			C.HEADER_SEQ, C.START_DT AS BCALC_START_DT, C.END_DT AS BCALC_END_DT, C.RS_CD, C.EFFDT, C.BILLABLE_CHG_ID, 
			C.CALC_AMT, C.DESCR_ON_BILL AS BCALC_DESC_BILL,  
			L.SEQNO, L.PRT_SW,  L.APP_IN_SUMM_SW, L.CALC_AMT AS CALC_LN_AMT, L.DST_ID, 
			L.MSR_PEAK_QTY_SW, L.DESCR_ON_BILL AS DESCR_LN, ' ' AS SIBLING_ID  
	            FROM CI_FT F,CI_SA S,
			 CI_PREM P RIGHT OUTER JOIN CI_BSEG B ON P.PREM_ID   = B.PREM_ID
			,CI_BSEG_CALC C,CI_BSEG_CALC_LN L,CI_SA_TYPE T, CI_SA_TYPE_L TL
	           WHERE F.SIBLING_ID = B.BSEG_ID 
	             AND F.FT_TYPE_FLG = 'BS' 
	             AND F.CORRECTION_SW = 'N' 
	             AND F.SHOW_ON_BILL_SW = 'Y' 
	             AND S.SA_ID = B.SA_ID 
	             AND P.PREM_ID   = B.PREM_ID 
	             AND T.CIS_DIVISION = S.CIS_DIVISION 
	             AND T.SA_TYPE_CD = S.SA_TYPE_CD
	             AND TL.CIS_DIVISION = T.CIS_DIVISION 
	             AND TL.SA_TYPE_CD = T.SA_TYPE_CD 
	             AND C.BSEG_ID = B.BSEG_ID 
	             AND L.BSEG_ID = C.BSEG_ID 
	             AND L.HEADER_SEQ = C.HEADER_SEQ) F,   --- 2ND MAIN: CI_BILL_DTL_VW
		 CI_BATCH_CTRL G, CI_BILL_RT_TYPE BRT3 			 
		WHERE E.LANGUAGE_CD = F.LANGUAGE_CD 
		     AND 'Y' = @V_BATCH_SW 
		     AND (E.BILL_ID BETWEEN @V_FROM_BILL_ID AND @V_TO_BILL_ID) 
		     AND E.BATCH_CD = @V_BATCH_CD
		     AND 0 = @V_BATCH_NBR 
		     AND G.BATCH_CD = E.BATCH_CD 
		     AND E.BATCH_NBR = G.NEXT_BATCH_NBR - 1
		     AND E.NO_BATCH_PRT_SW = 'N' 
		     AND E.BILL_ID = F.BILL_ID
		     AND E.BILL_RTE_TYPE_CD = BRT3.BILL_RTE_TYPE_CD
 	  	     AND BRT3.EXTRACT_ALG_CD = @V_EXTRACT_ALG_CD  
	UNION 
--- 4th SQL
		SELECT @V_RETURN_CD RETURN_CODE, CAST(NULL AS VARCHAR) MESSAGE_TEXT,
		    H.SORT_KEY, H.BILL_ID, H.SEQNO, H.PER_ID, H.NBR_BILL_COPIES, H.ENTITY_NAME1, H.ENTITY_NAME2, H.ENTITY_NAME3, 
		    H.COUNTRY, H.ADDRESS1, H.ADDRESS2, H.ADDRESS3, H.CITY, H.COUNTY, H.STATE, H.POSTAL, H.IN_CITY_LIMIT, 
	  	    H.BATCH_CD, H.BATCH_NBR, H.NO_BATCH_PRT_SW, H.ACCT_ID, H.BILL_STAT_FLG, H.BILL_DT, H.DUE_DT, 
 	 	    H.ACCT_ENTITY_NAME, H.LANGUAGE_CD, H.CURRENCY_CD, H.CUR_SYMBOL, H.DECIMAL_POSITIONS, H.CUR_POS_FLG, 
	   	    H.OPEN_ITEM_SW, H.BILL_COPY, I.BSEG_ID, I.SA_ID, I.START_DT, I.END_DT, I.BILL_CYC_CD, I.WIN_START_DT, 
	 	    I.EST_SW, I.CLOSING_BSEG_SW, I.PREM_ID, I.CIS_DIVISION, I.SA_TYPE_CD, I.SAT_DESCR, I.BILL_PRT_PRIO_FLG, 
		   I.GRAPH_UOM_CD, I.HEADER_SEQ, I.BCALC_START_DT, I.BCALC_END_DT, I.RS_CD, I.EFFDT, I.BILLABLE_CHG_ID, 
		   I.CALC_AMT, I.BCALC_DESC_BILL, I.SEQNO, I.PRT_SW, I.APP_IN_SUMM_SW, I.CALC_LN_AMT, 
		   I.DST_ID, I.MSR_PEAK_QTY_SW, I.DESCR_LN, I.STATE, I.CITY, I.POSTAL, I.SIBLING_ID 	    
		FROM 
    --- 1ST MAIN FOR 4TH SQL: CI_BILL_PRT_VW ( Bill Print View)  
    --- This view joins Bill to Bill Routing and Account-Person-Name. 
    --- The UNION is used to produce 1 row for each copy required for the routing. The UNION should be 
	--- repeated as many times as required to handle the maximum possible number of copies.

   		 (SELECT R.BILL_ID AS SORT_KEY, R.BILL_ID, R.SEQNO, R.PER_ID, R.NBR_BILL_COPIES, R.ENTITY_NAME1, 
		        R.ENTITY_NAME2, R.ENTITY_NAME3, R.COUNTRY, R.ADDRESS1, R.ADDRESS2,R.ADDRESS3, R.CITY, R.COUNTY, 
			R.STATE, R.POSTAL,  R.IN_CITY_LIMIT, R.BATCH_CD, R.BATCH_NBR, R.NO_BATCH_PRT_SW, R.BILL_RTE_TYPE_CD,	
			B.ACCT_ID, B.BILL_STAT_FLG, B.BILL_DT, B.DUE_DT, A.ACCT_ENTITY_NAME, A.LANGUAGE_CD, A.CURRENCY_CD,
			A.CUR_SYMBOL, A.DECIMAL_POSITIONS, A.CUR_POS_FLG, A.OPEN_ITEM_SW, 1 AS BILL_COPY 
  	          FROM CI_BILL_ROUTING R 
	               , CI_BILL B 
        	       , (SELECT A.ACCT_ID, B.ENTITY_NAME AS ACCT_ENTITY_NAME, C.LANGUAGE_CD, E.CUST_CL_CD, 
                	         E.OPEN_ITEM_SW, F.CURRENCY_CD, F.CUR_SYMBOL, F.DECIMAL_POSITIONS, F.CUR_POS_FLG 
	                    FROM CI_ACCT_PER A, CI_PER_NAME B, CI_PER C, CI_ACCT D, CI_CUST_CL E, CI_CURRENCY_CD F 
	                   WHERE A.PER_ID = B.PER_ID  AND A.MAIN_CUST_SW = 'Y' 
	                         AND B.NAME_TYPE_FLG = 'PRIM' AND B.PRIM_NAME_SW = 'Y' 
	                         AND C.PER_ID = A.PER_ID AND D.ACCT_ID = A.ACCT_ID 
	                         AND E.CUST_CL_CD = D.CUST_CL_CD AND F.CURRENCY_CD = D.CURRENCY_CD) A -- Account Person Name View   
		 WHERE B.BILL_ID = R.BILL_ID
  			AND B.BILL_STAT_FLG = 'C' 
              		AND A.ACCT_ID = B.ACCT_ID 
              		AND R.NBR_BILL_COPIES >= 1
	 UNION 	  
		SELECT R.BILL_ID AS SORT_KEY, R.BILL_ID, R.SEQNO, R.PER_ID, R.NBR_BILL_COPIES, R.ENTITY_NAME1, 
		        R.ENTITY_NAME2, R.ENTITY_NAME3, R.COUNTRY, R.ADDRESS1, R.ADDRESS2,R.ADDRESS3, R.CITY, R.COUNTY, 
			R.STATE, R.POSTAL,  R.IN_CITY_LIMIT, R.BATCH_CD, R.BATCH_NBR, R.NO_BATCH_PRT_SW, R.BILL_RTE_TYPE_CD,	
			B.ACCT_ID, B.BILL_STAT_FLG, B.BILL_DT, B.DUE_DT, A.ACCT_ENTITY_NAME, A.LANGUAGE_CD, A.CURRENCY_CD,
			A.CUR_SYMBOL, A.DECIMAL_POSITIONS, A.CUR_POS_FLG, A.OPEN_ITEM_SW, 2 AS BILL_COPY 
  	        FROM CI_BILL_ROUTING R 
               , CI_BILL B 
               , (SELECT A.ACCT_ID, B.ENTITY_NAME AS ACCT_ENTITY_NAME, C.LANGUAGE_CD, E.CUST_CL_CD, 
                         E.OPEN_ITEM_SW, F.CURRENCY_CD, F.CUR_SYMBOL, F.DECIMAL_POSITIONS, F.CUR_POS_FLG 
                    FROM CI_ACCT_PER A, CI_PER_NAME B, CI_PER C, CI_ACCT D, CI_CUST_CL E, CI_CURRENCY_CD F 
                   WHERE A.PER_ID = B.PER_ID  AND A.MAIN_CUST_SW = 'Y' 
                         AND B.NAME_TYPE_FLG = 'PRIM' AND B.PRIM_NAME_SW = 'Y' 
                         AND C.PER_ID = A.PER_ID AND D.ACCT_ID = A.ACCT_ID 
                         AND E.CUST_CL_CD = D.CUST_CL_CD AND F.CURRENCY_CD = D.CURRENCY_CD) A -- Account Person Name View   
            WHERE B.BILL_ID = R.BILL_ID
  			  AND B.BILL_STAT_FLG = 'C' 
              AND A.ACCT_ID = B.ACCT_ID 
              AND R.NBR_BILL_COPIES >= 2
     UNION 			  
	     SELECT R.BILL_ID AS SORT_KEY, R.BILL_ID, R.SEQNO, R.PER_ID, R.NBR_BILL_COPIES, R.ENTITY_NAME1, 
	        R.ENTITY_NAME2, R.ENTITY_NAME3, R.COUNTRY, R.ADDRESS1, R.ADDRESS2,R.ADDRESS3, R.CITY, R.COUNTY, 
			R.STATE, R.POSTAL,  R.IN_CITY_LIMIT, R.BATCH_CD, R.BATCH_NBR, R.NO_BATCH_PRT_SW, R.BILL_RTE_TYPE_CD,	
			B.ACCT_ID, B.BILL_STAT_FLG, B.BILL_DT, B.DUE_DT, A.ACCT_ENTITY_NAME, A.LANGUAGE_CD, A.CURRENCY_CD,
			A.CUR_SYMBOL, A.DECIMAL_POSITIONS, A.CUR_POS_FLG, A.OPEN_ITEM_SW, 3 AS BILL_COPY 
  	        FROM CI_BILL_ROUTING R 
               , CI_BILL B 
               , (SELECT A.ACCT_ID, B.ENTITY_NAME AS ACCT_ENTITY_NAME, C.LANGUAGE_CD, E.CUST_CL_CD, 
                         E.OPEN_ITEM_SW, F.CURRENCY_CD, F.CUR_SYMBOL, F.DECIMAL_POSITIONS, F.CUR_POS_FLG 
                    FROM CI_ACCT_PER A, CI_PER_NAME B, CI_PER C, CI_ACCT D, CI_CUST_CL E, CI_CURRENCY_CD F 
                   WHERE A.PER_ID = B.PER_ID  AND A.MAIN_CUST_SW = 'Y' 
                         AND B.NAME_TYPE_FLG = 'PRIM' AND B.PRIM_NAME_SW = 'Y' 
                         AND C.PER_ID = A.PER_ID AND D.ACCT_ID = A.ACCT_ID 
                         AND E.CUST_CL_CD = D.CUST_CL_CD AND F.CURRENCY_CD = D.CURRENCY_CD) A -- Account Person Name View   
            WHERE B.BILL_ID = R.BILL_ID
  			  AND B.BILL_STAT_FLG = 'C' 
              AND A.ACCT_ID = B.ACCT_ID 
              AND R.NBR_BILL_COPIES >= 3) H,  --- 1ST MAIN FOR 4TH SQL END: CI_BILL_PRT_VW

    --- 2ND MAIN FOR 2ND SQL: CI_BILL_DTL2VW ( Bill Details 2 View)
	--- This view joins FT, SA, Prem, SA Type using a record structure same as CI_BILL_DTL_VW   

		(SELECT A.BILL_ID, ' ' AS BSEG_ID, A.SA_ID, A.ACCOUNTING_DT AS START_DT, A.ACCOUNTING_DT AS END_DT, 
	        ' ' AS BILL_CYC_CD, A.ACCOUNTING_DT AS WIN_START_DT, ' ' AS EST_SW, ' ' AS CLOSING_BSEG_SW, 
			S.CHAR_PREM_ID AS PREM_ID, P.STATE, P.CITY, P.POSTAL, 
			S.CIS_DIVISION, S.SA_TYPE_CD, TL.DESCR AS SAT_DESCR, TL.LANGUAGE_CD, T.BILL_PRT_PRIO_FLG, T.GRAPH_UOM_CD, 
			0 AS HEADER_SEQ, A.ACCOUNTING_DT AS BCALC_START_DT, A.ACCOUNTING_DT AS BCALC_END_DT, ' ' AS RS_CD, 
			A.ACCOUNTING_DT AS EFFDT, ' ' AS BILLABLE_CHG_ID, 0 AS CALC_AMT,  
			' ' AS BCALC_DESC_BILL, 0 AS SEQNO, ' ' AS PRT_SW, ' ' AS APP_IN_SUMM_SW, 0 AS CALC_LN_AMT,  
        	    ' ' AS DST_ID, ' ' AS MSR_PEAK_QTY_SW, ' ' AS DESCR_LN, A.SIBLING_ID
		    FROM CI_FT A     
			, CI_PREM P RIGHT OUTER JOIN CI_SA S ON P.PREM_ID   = S.CHAR_PREM_ID
			, CI_SA_TYPE T, CI_SA_TYPE_L TL 
		 WHERE S.SA_ID = A.SA_ID 
	             AND P.PREM_ID   = S.CHAR_PREM_ID 
	             AND T.CIS_DIVISION = S.CIS_DIVISION 
	             AND T.SA_TYPE_CD = S.SA_TYPE_CD 
	             AND TL.CIS_DIVISION = T.CIS_DIVISION 
	             AND TL.SA_TYPE_CD = T.SA_TYPE_CD 
	             AND A.SHOW_ON_BILL_SW = 'Y'
	  	     AND A.FT_TYPE_FLG IN ('AD','AX','PS','PX')) I,   --- 2ND MAIN FOR SQL: CI_BILL_DTL2VW
		 CI_BATCH_CTRL J,
 		 CI_BILL_RT_TYPE BRT4
	WHERE H.LANGUAGE_CD = I.LANGUAGE_CD 
	     AND 'Y' = @V_BATCH_SW 
	     AND (H.BILL_ID BETWEEN @V_FROM_BILL_ID AND @V_TO_BILL_ID) 
	     AND H.BATCH_CD = @V_BATCH_CD 
	     AND 0 = @V_BATCH_NBR 
	     AND J.BATCH_CD = H.BATCH_CD 
	     AND H.BATCH_NBR = J.NEXT_BATCH_NBR - 1 
	     AND H.NO_BATCH_PRT_SW = 'N' 
	     AND H.BILL_ID = I.BILL_ID 
	     AND H.BILL_RTE_TYPE_CD = BRT4.BILL_RTE_TYPE_CD
	     AND BRT4.EXTRACT_ALG_CD = @V_EXTRACT_ALG_CD  
    UNION
--- 5th SQL
	SELECT @V_RETURN_CD RETURN_CODE, CAST(NULL AS VARCHAR) MESSAGE_TEXT,
       		K.SORT_KEY, K.BILL_ID, K.SEQNO, K.PER_ID, K.NBR_BILL_COPIES, K.ENTITY_NAME1, K.ENTITY_NAME2, K.ENTITY_NAME3, 
		K.COUNTRY, K.ADDRESS1, K.ADDRESS2, K.ADDRESS3, K.CITY, K.COUNTY, K.STATE, K.POSTAL, K.IN_CITY_LIMIT, 
		   K.BATCH_CD, K.BATCH_NBR, K.NO_BATCH_PRT_SW, K.ACCT_ID, K.BILL_STAT_FLG, K.BILL_DT, K.DUE_DT, 
		   K.ACCT_ENTITY_NAME, K.LANGUAGE_CD, K.CURRENCY_CD, K.CUR_SYMBOL, K.DECIMAL_POSITIONS, K.CUR_POS_FLG, 
		   K.OPEN_ITEM_SW, K.BILL_COPY, L.BSEG_ID, L.SA_ID, L.START_DT, L.END_DT, L.BILL_CYC_CD, L.WIN_START_DT, 
		   L.EST_SW, L.CLOSING_BSEG_SW, L.PREM_ID, L.CIS_DIVISION, L.SA_TYPE_CD, L.SAT_DESCR, L.BILL_PRT_PRIO_FLG, 
		   L.GRAPH_UOM_CD, L.HEADER_SEQ, L.BCALC_START_DT, L.BCALC_END_DT, L.RS_CD, L.EFFDT, L.BILLABLE_CHG_ID, 
		   L.CALC_AMT, L.BCALC_DESC_BILL, L.SEQNO, L.PRT_SW, L.APP_IN_SUMM_SW, L.CALC_LN_AMT, 
		   L.DST_ID, L.MSR_PEAK_QTY_SW, L.DESCR_LN, L.STATE, L.CITY, L.POSTAL, L.SIBLING_ID 	    
	FROM 
    --- 1ST MAIN FOR 5TH SQL: CI_BILL_PRT_VW ( Bill Print View)  
    --- This view joins Bill to Bill Routing and Account-Person-Name. 
    --- The UNION is used to produce 1 row for each copy required for the routing. The UNION should be 
	--- repeated as many times as required to handle the maximum possible number of copies.

	    (SELECT R.BILL_ID AS SORT_KEY, R.BILL_ID, R.SEQNO, R.PER_ID, R.NBR_BILL_COPIES, R.ENTITY_NAME1, 
	        R.ENTITY_NAME2, R.ENTITY_NAME3, R.COUNTRY, R.ADDRESS1, R.ADDRESS2,R.ADDRESS3, R.CITY, R.COUNTY, 
			R.STATE, R.POSTAL,  R.IN_CITY_LIMIT, R.BATCH_CD, R.BATCH_NBR, R.NO_BATCH_PRT_SW, R.BILL_RTE_TYPE_CD,	
			B.ACCT_ID, B.BILL_STAT_FLG, B.BILL_DT, B.DUE_DT, A.ACCT_ENTITY_NAME, A.LANGUAGE_CD, A.CURRENCY_CD,
			A.CUR_SYMBOL, A.DECIMAL_POSITIONS, A.CUR_POS_FLG, A.OPEN_ITEM_SW, 1 AS BILL_COPY 
  	        FROM CI_BILL_ROUTING R 
               , CI_BILL B 
               , (SELECT A.ACCT_ID, B.ENTITY_NAME AS ACCT_ENTITY_NAME, C.LANGUAGE_CD, E.CUST_CL_CD, 
                         E.OPEN_ITEM_SW, F.CURRENCY_CD, F.CUR_SYMBOL, F.DECIMAL_POSITIONS, F.CUR_POS_FLG 
                    FROM CI_ACCT_PER A, CI_PER_NAME B, CI_PER C, CI_ACCT D, CI_CUST_CL E, CI_CURRENCY_CD F 
                   WHERE A.PER_ID = B.PER_ID  AND A.MAIN_CUST_SW = 'Y' 
                         AND B.NAME_TYPE_FLG = 'PRIM' AND B.PRIM_NAME_SW = 'Y' 
                         AND C.PER_ID = A.PER_ID AND D.ACCT_ID = A.ACCT_ID 
                         AND E.CUST_CL_CD = D.CUST_CL_CD AND F.CURRENCY_CD = D.CURRENCY_CD) A -- Account Person Name View   
            WHERE B.BILL_ID = R.BILL_ID
  			  AND B.BILL_STAT_FLG = 'C' 
              AND A.ACCT_ID = B.ACCT_ID 
              AND R.NBR_BILL_COPIES >= 1
     UNION 	  
     SELECT R.BILL_ID AS SORT_KEY, R.BILL_ID, R.SEQNO, R.PER_ID, R.NBR_BILL_COPIES, R.ENTITY_NAME1, 
	        R.ENTITY_NAME2, R.ENTITY_NAME3, R.COUNTRY, R.ADDRESS1, R.ADDRESS2,R.ADDRESS3, R.CITY, R.COUNTY, 
			R.STATE, R.POSTAL,  R.IN_CITY_LIMIT, R.BATCH_CD, R.BATCH_NBR, R.NO_BATCH_PRT_SW, R.BILL_RTE_TYPE_CD,	
			B.ACCT_ID, B.BILL_STAT_FLG, B.BILL_DT, B.DUE_DT, A.ACCT_ENTITY_NAME, A.LANGUAGE_CD, A.CURRENCY_CD,
			A.CUR_SYMBOL, A.DECIMAL_POSITIONS, A.CUR_POS_FLG, A.OPEN_ITEM_SW, 2 AS BILL_COPY 
  	        FROM CI_BILL_ROUTING R 
               , CI_BILL B 
               , (SELECT A.ACCT_ID, B.ENTITY_NAME AS ACCT_ENTITY_NAME, C.LANGUAGE_CD, E.CUST_CL_CD, 
                         E.OPEN_ITEM_SW, F.CURRENCY_CD, F.CUR_SYMBOL, F.DECIMAL_POSITIONS, F.CUR_POS_FLG 
                    FROM CI_ACCT_PER A, CI_PER_NAME B, CI_PER C, CI_ACCT D, CI_CUST_CL E, CI_CURRENCY_CD F 
                   WHERE A.PER_ID = B.PER_ID  AND A.MAIN_CUST_SW = 'Y' 
                         AND B.NAME_TYPE_FLG = 'PRIM' AND B.PRIM_NAME_SW = 'Y' 
                         AND C.PER_ID = A.PER_ID AND D.ACCT_ID = A.ACCT_ID 
                         AND E.CUST_CL_CD = D.CUST_CL_CD AND F.CURRENCY_CD = D.CURRENCY_CD) A -- Account Person Name View   
            WHERE B.BILL_ID = R.BILL_ID
  			  AND B.BILL_STAT_FLG = 'C' 
              AND A.ACCT_ID = B.ACCT_ID 
              AND R.NBR_BILL_COPIES >= 2
     UNION 			  
     SELECT R.BILL_ID AS SORT_KEY, R.BILL_ID, R.SEQNO, R.PER_ID, R.NBR_BILL_COPIES, R.ENTITY_NAME1, 
	        R.ENTITY_NAME2, R.ENTITY_NAME3, R.COUNTRY, R.ADDRESS1, R.ADDRESS2,R.ADDRESS3, R.CITY, R.COUNTY, 
			R.STATE, R.POSTAL,  R.IN_CITY_LIMIT, R.BATCH_CD, R.BATCH_NBR, R.NO_BATCH_PRT_SW, R.BILL_RTE_TYPE_CD,	
			B.ACCT_ID, B.BILL_STAT_FLG, B.BILL_DT, B.DUE_DT, A.ACCT_ENTITY_NAME, A.LANGUAGE_CD, A.CURRENCY_CD,
			A.CUR_SYMBOL, A.DECIMAL_POSITIONS, A.CUR_POS_FLG, A.OPEN_ITEM_SW, 3 AS BILL_COPY 
  	        FROM CI_BILL_ROUTING R 
               , CI_BILL B 
               , (SELECT A.ACCT_ID, B.ENTITY_NAME AS ACCT_ENTITY_NAME, C.LANGUAGE_CD, E.CUST_CL_CD, 
                         E.OPEN_ITEM_SW, F.CURRENCY_CD, F.CUR_SYMBOL, F.DECIMAL_POSITIONS, F.CUR_POS_FLG 
                    FROM CI_ACCT_PER A, CI_PER_NAME B, CI_PER C, CI_ACCT D, CI_CUST_CL E, CI_CURRENCY_CD F 
                   WHERE A.PER_ID = B.PER_ID  AND A.MAIN_CUST_SW = 'Y' 
                         AND B.NAME_TYPE_FLG = 'PRIM' AND B.PRIM_NAME_SW = 'Y' 
                         AND C.PER_ID = A.PER_ID AND D.ACCT_ID = A.ACCT_ID 
                         AND E.CUST_CL_CD = D.CUST_CL_CD AND F.CURRENCY_CD = D.CURRENCY_CD) A -- Account Person Name View   
            WHERE B.BILL_ID = R.BILL_ID
  			  AND B.BILL_STAT_FLG = 'C' 
              AND A.ACCT_ID = B.ACCT_ID 
              AND R.NBR_BILL_COPIES >= 3) K,  --- 1ST MAIN FOR 5TH SQL END: CI_BILL_PRT_VW

    --- 2ND MAIN: CI_BILL_DTL_VW ( Bill Details View)  
    --- This view joins Bill Seg, Prem, SA, SA TYPE, Bill Calc, Bill Calc Lines, FT (TYPE BS). 
    --- There IS a blank ADJ_ID field tO allow unioning in the query WITH CI_BILL_DTL2VW

	(SELECT B.BILL_ID, B.BSEG_ID, B.SA_ID, B.START_DT, B.END_DT, B.BILL_CYC_CD, B.WIN_START_DT, B.EST_SW,
	        B.CLOSING_BSEG_SW, B.PREM_ID, P.STATE, P.CITY, P.POSTAL, 
            S.CIS_DIVISION, S.SA_TYPE_CD, TL.DESCR AS SAT_DESCR, TL.LANGUAGE_CD, T.BILL_PRT_PRIO_FLG, T.GRAPH_UOM_CD, 
			C.HEADER_SEQ, C.START_DT AS BCALC_START_DT, C.END_DT AS BCALC_END_DT,  C.RS_CD, C.EFFDT, C.BILLABLE_CHG_ID, C.CALC_AMT,  
			C.DESCR_ON_BILL AS BCALC_DESC_BILL,  
			L.SEQNO, L.PRT_SW,  L.APP_IN_SUMM_SW, L.CALC_AMT AS CALC_LN_AMT, L.DST_ID, 
			L.MSR_PEAK_QTY_SW, L.DESCR_ON_BILL AS DESCR_LN, ' ' AS SIBLING_ID  
            FROM  CI_FT F 
               , CI_SA S 
               , CI_PREM P RIGHT OUTER JOIN CI_BSEG B ON P.PREM_ID   = B.PREM_ID
               , CI_BSEG_CALC C 
               , CI_BSEG_CALC_LN L 
               , CI_SA_TYPE T
               , CI_SA_TYPE_L TL
           WHERE F.SIBLING_ID = B.BSEG_ID 
             AND F.FT_TYPE_FLG = 'BS' 
             AND F.CORRECTION_SW = 'N' 
             AND F.SHOW_ON_BILL_SW = 'Y' 
             AND S.SA_ID = B.SA_ID 
             AND P.PREM_ID   = B.PREM_ID 
             AND T.CIS_DIVISION = S.CIS_DIVISION 
             AND T.SA_TYPE_CD = S.SA_TYPE_CD
             AND TL.CIS_DIVISION = T.CIS_DIVISION 
             AND TL.SA_TYPE_CD = T.SA_TYPE_CD 
             AND C.BSEG_ID = B.BSEG_ID 
             AND L.BSEG_ID = C.BSEG_ID 
             AND L.HEADER_SEQ = C.HEADER_SEQ) L,   --- 2ND MAIN: CI_BILL_DTL_VW
	    	 CI_BILL_RT_TYPE BRT5
WHERE K.LANGUAGE_CD = L.LANGUAGE_CD 
     AND 'Y' = @V_BATCH_SW 
     AND (K.BILL_ID BETWEEN @V_FROM_BILL_ID AND @V_TO_BILL_ID) 
     AND K.BATCH_CD = @V_BATCH_CD
     AND K.BATCH_NBR = @V_BATCH_NBR
     AND 0 <> @V_BATCH_NBR 
     AND K.BILL_ID = L.BILL_ID 
	 AND K.BILL_RTE_TYPE_CD = BRT5.BILL_RTE_TYPE_CD
     AND BRT5.EXTRACT_ALG_CD = @V_EXTRACT_ALG_CD  

UNION 
--- 6th SQL
SELECT @V_RETURN_CD RETURN_CODE, CAST(NULL AS VARCHAR) MESSAGE_TEXT,
       M.SORT_KEY, M.BILL_ID, M.SEQNO, M.PER_ID, M.NBR_BILL_COPIES, M.ENTITY_NAME1, M.ENTITY_NAME2, M.ENTITY_NAME3, 
	   M.COUNTRY, M.ADDRESS1, M.ADDRESS2, M.ADDRESS3, M.CITY, M.COUNTY, M.STATE, M.POSTAL, M.IN_CITY_LIMIT, 
	   M.BATCH_CD, M.BATCH_NBR, M.NO_BATCH_PRT_SW, M.ACCT_ID, M.BILL_STAT_FLG, M.BILL_DT, M.DUE_DT, 
	   M.ACCT_ENTITY_NAME, M.LANGUAGE_CD, M.CURRENCY_CD, M.CUR_SYMBOL, M.DECIMAL_POSITIONS, M.CUR_POS_FLG, 
	   M.OPEN_ITEM_SW, M.BILL_COPY, N.BSEG_ID, N.SA_ID, N.START_DT, N.END_DT, N.BILL_CYC_CD, N.WIN_START_DT, 
	   N.EST_SW, N.CLOSING_BSEG_SW, N.PREM_ID, N.CIS_DIVISION, N.SA_TYPE_CD, N.SAT_DESCR, N.BILL_PRT_PRIO_FLG, 
	   N.GRAPH_UOM_CD, N.HEADER_SEQ, N.BCALC_START_DT, N.BCALC_END_DT, N.RS_CD, N.EFFDT, N.BILLABLE_CHG_ID, 
	   N.CALC_AMT, N.BCALC_DESC_BILL, N.SEQNO, N.PRT_SW, N.APP_IN_SUMM_SW, N.CALC_LN_AMT, 
	   N.DST_ID, N.MSR_PEAK_QTY_SW, N.DESCR_LN, N.STATE, N.CITY, N.POSTAL, N.SIBLING_ID 	    
FROM 
    --- 1ST MAIN FOR 6TH SQL: CI_BILL_PRT_VW ( Bill Print View)  
    --- This view joins Bill to Bill Routing and Account-Person-Name. 
    --- The UNION is used to produce 1 row for each copy required for the routing. The UNION should be 
	--- repeated as many times as required to handle the maximum possible number of copies.

    (SELECT R.BILL_ID AS SORT_KEY, R.BILL_ID, R.SEQNO, R.PER_ID, R.NBR_BILL_COPIES, R.ENTITY_NAME1, 
	        R.ENTITY_NAME2, R.ENTITY_NAME3, R.COUNTRY, R.ADDRESS1, R.ADDRESS2,R.ADDRESS3, R.CITY, R.COUNTY, 
			R.STATE, R.POSTAL,  R.IN_CITY_LIMIT, R.BATCH_CD, R.BATCH_NBR, R.NO_BATCH_PRT_SW, R.BILL_RTE_TYPE_CD,	
			B.ACCT_ID, B.BILL_STAT_FLG, B.BILL_DT, B.DUE_DT, A.ACCT_ENTITY_NAME, A.LANGUAGE_CD, A.CURRENCY_CD,
			A.CUR_SYMBOL, A.DECIMAL_POSITIONS, A.CUR_POS_FLG, A.OPEN_ITEM_SW, 1 AS BILL_COPY 
  	        FROM CI_BILL_ROUTING R 
               , CI_BILL B 
               , (SELECT A.ACCT_ID, B.ENTITY_NAME AS ACCT_ENTITY_NAME, C.LANGUAGE_CD, E.CUST_CL_CD, 
                         E.OPEN_ITEM_SW, F.CURRENCY_CD, F.CUR_SYMBOL, F.DECIMAL_POSITIONS, F.CUR_POS_FLG 
                    FROM CI_ACCT_PER A, CI_PER_NAME B, CI_PER C, CI_ACCT D, CI_CUST_CL E, CI_CURRENCY_CD F 
                   WHERE A.PER_ID = B.PER_ID  AND A.MAIN_CUST_SW = 'Y' 
                         AND B.NAME_TYPE_FLG = 'PRIM' AND B.PRIM_NAME_SW = 'Y' 
                         AND C.PER_ID = A.PER_ID AND D.ACCT_ID = A.ACCT_ID 
                         AND E.CUST_CL_CD = D.CUST_CL_CD AND F.CURRENCY_CD = D.CURRENCY_CD) A -- Account Person Name View   
            WHERE B.BILL_ID = R.BILL_ID
  			  AND B.BILL_STAT_FLG = 'C' 
              AND A.ACCT_ID = B.ACCT_ID 
              AND R.NBR_BILL_COPIES >= 1
     UNION 	  
     SELECT R.BILL_ID AS SORT_KEY, R.BILL_ID, R.SEQNO, R.PER_ID, R.NBR_BILL_COPIES, R.ENTITY_NAME1, 
	        R.ENTITY_NAME2, R.ENTITY_NAME3, R.COUNTRY, R.ADDRESS1, R.ADDRESS2,R.ADDRESS3, R.CITY, R.COUNTY, 
			R.STATE, R.POSTAL,  R.IN_CITY_LIMIT, R.BATCH_CD, R.BATCH_NBR, R.NO_BATCH_PRT_SW, R.BILL_RTE_TYPE_CD,	
			B.ACCT_ID, B.BILL_STAT_FLG, B.BILL_DT, B.DUE_DT, A.ACCT_ENTITY_NAME, A.LANGUAGE_CD, A.CURRENCY_CD,
			A.CUR_SYMBOL, A.DECIMAL_POSITIONS, A.CUR_POS_FLG, A.OPEN_ITEM_SW, 2 AS BILL_COPY 
  	        FROM CI_BILL_ROUTING R 
               , CI_BILL B 
               , (SELECT A.ACCT_ID, B.ENTITY_NAME AS ACCT_ENTITY_NAME, C.LANGUAGE_CD, E.CUST_CL_CD, 
                         E.OPEN_ITEM_SW, F.CURRENCY_CD, F.CUR_SYMBOL, F.DECIMAL_POSITIONS, F.CUR_POS_FLG 
                    FROM CI_ACCT_PER A, CI_PER_NAME B, CI_PER C, CI_ACCT D, CI_CUST_CL E, CI_CURRENCY_CD F 
                   WHERE A.PER_ID = B.PER_ID  AND A.MAIN_CUST_SW = 'Y' 
                         AND B.NAME_TYPE_FLG = 'PRIM' AND B.PRIM_NAME_SW = 'Y' 
                         AND C.PER_ID = A.PER_ID AND D.ACCT_ID = A.ACCT_ID 
                         AND E.CUST_CL_CD = D.CUST_CL_CD AND F.CURRENCY_CD = D.CURRENCY_CD) A -- Account Person Name View   
            WHERE B.BILL_ID = R.BILL_ID
  			  AND B.BILL_STAT_FLG = 'C' 
              AND A.ACCT_ID = B.ACCT_ID 
              AND R.NBR_BILL_COPIES >= 2
     UNION 			  
     SELECT R.BILL_ID AS SORT_KEY, R.BILL_ID, R.SEQNO, R.PER_ID, R.NBR_BILL_COPIES, R.ENTITY_NAME1, 
	        R.ENTITY_NAME2, R.ENTITY_NAME3, R.COUNTRY, R.ADDRESS1, R.ADDRESS2,R.ADDRESS3, R.CITY, R.COUNTY, 
			R.STATE, R.POSTAL,  R.IN_CITY_LIMIT, R.BATCH_CD, R.BATCH_NBR, R.NO_BATCH_PRT_SW, R.BILL_RTE_TYPE_CD,	
			B.ACCT_ID, B.BILL_STAT_FLG, B.BILL_DT, B.DUE_DT, A.ACCT_ENTITY_NAME, A.LANGUAGE_CD, A.CURRENCY_CD,
			A.CUR_SYMBOL, A.DECIMAL_POSITIONS, A.CUR_POS_FLG, A.OPEN_ITEM_SW, 3 AS BILL_COPY
  	        FROM CI_BILL_ROUTING R 
               , CI_BILL B 
               , (SELECT A.ACCT_ID, B.ENTITY_NAME AS ACCT_ENTITY_NAME, C.LANGUAGE_CD, E.CUST_CL_CD, 
                         E.OPEN_ITEM_SW, F.CURRENCY_CD, F.CUR_SYMBOL, F.DECIMAL_POSITIONS, F.CUR_POS_FLG 
                    FROM CI_ACCT_PER A, CI_PER_NAME B, CI_PER C, CI_ACCT D, CI_CUST_CL E, CI_CURRENCY_CD F 
                   WHERE A.PER_ID = B.PER_ID  AND A.MAIN_CUST_SW = 'Y' 
                         AND B.NAME_TYPE_FLG = 'PRIM' AND B.PRIM_NAME_SW = 'Y' 
                         AND C.PER_ID = A.PER_ID AND D.ACCT_ID = A.ACCT_ID 
                         AND E.CUST_CL_CD = D.CUST_CL_CD AND F.CURRENCY_CD = D.CURRENCY_CD) A -- Account Person Name View   
            WHERE B.BILL_ID = R.BILL_ID
  			  AND B.BILL_STAT_FLG = 'C' 
              AND A.ACCT_ID = B.ACCT_ID 
              AND R.NBR_BILL_COPIES >= 3) M,  --- 1ST MAIN FOR 6TH SQL END: CI_BILL_PRT_VW

    --- 2ND MAIN FOR 2ND SQL: CI_BILL_DTL2VW ( Bill Details 2 View)
	--- This view joins FT, SA, Prem, SA Type using a record structure same as CI_BILL_DTL_VW   

	(SELECT A.BILL_ID, ' ' AS BSEG_ID, A.SA_ID, A.ACCOUNTING_DT AS START_DT, A.ACCOUNTING_DT AS END_DT, 
        ' ' AS BILL_CYC_CD, A.ACCOUNTING_DT AS WIN_START_DT, ' ' AS EST_SW, ' ' AS CLOSING_BSEG_SW, 
	S.CHAR_PREM_ID AS PREM_ID, P.STATE, P.CITY, P.POSTAL, 
			S.CIS_DIVISION, S.SA_TYPE_CD, TL.DESCR AS SAT_DESCR, TL.LANGUAGE_CD, T.BILL_PRT_PRIO_FLG, T.GRAPH_UOM_CD, 
			0 AS HEADER_SEQ, A.ACCOUNTING_DT AS BCALC_START_DT, A.ACCOUNTING_DT AS BCALC_END_DT, ' ' AS RS_CD, 
			A.ACCOUNTING_DT AS EFFDT, ' ' AS BILLABLE_CHG_ID, 0 AS CALC_AMT,  
			' ' AS BCALC_DESC_BILL, 0 AS SEQNO, ' ' AS PRT_SW, ' ' AS APP_IN_SUMM_SW, 0 AS CALC_LN_AMT,  
            ' ' AS DST_ID, ' ' AS MSR_PEAK_QTY_SW, ' ' AS DESCR_LN, A.SIBLING_ID
			FROM CI_FT A 
               , CI_PREM P RIGHT OUTER JOIN CI_SA S ON P.PREM_ID   = S.CHAR_PREM_ID 
               , CI_SA_TYPE T
               , CI_SA_TYPE_L TL 
           WHERE S.SA_ID = A.SA_ID 
             AND P.PREM_ID   = S.CHAR_PREM_ID 
             AND T.CIS_DIVISION = S.CIS_DIVISION 
             AND T.SA_TYPE_CD = S.SA_TYPE_CD 
             AND TL.CIS_DIVISION = T.CIS_DIVISION 
             AND TL.SA_TYPE_CD = T.SA_TYPE_CD 
             AND A.SHOW_ON_BILL_SW = 'Y'
			 AND A.FT_TYPE_FLG IN ('AD','AX','PS','PX')) N,			    --- 2ND MAIN FOR SQL: CI_BILL_DTL2VW
		 	 CI_BILL_RT_TYPE BRT6
WHERE M.LANGUAGE_CD = N.LANGUAGE_CD 
     AND 'Y' = @V_BATCH_SW 
     AND (M.BILL_ID BETWEEN @V_FROM_BILL_ID AND @V_TO_BILL_ID) 
     AND M.BATCH_CD = @V_BATCH_CD
     AND M.BATCH_NBR = @V_BATCH_NBR 
     AND 0 <> @V_BATCH_NBR 
     AND M.BILL_ID = N.BILL_ID 
	 AND M.BILL_RTE_TYPE_CD = BRT6.BILL_RTE_TYPE_CD
	 AND BRT6.EXTRACT_ALG_CD = @V_EXTRACT_ALG_CD  
ORDER BY 3, 4, 5, 34, 64, 65, 66, 43, 47, 36, 37, 35, 49, 57 
--A.SORT_KEY, A.BILL_ID, A.SEQNO, A.BILL_COPY, B.STATE, B.CITY, B.POSTAL, B.PREM_ID, B.BILL_PRINT_PRIO_FLG,
--         B.SA_ID, B.START_DT, B.BSEG_ID, B.HEADER_SEQ, B.SEQ_NO 
;

	END
     ELSE
        BEGIN

	 SELECT MESSAGE_NBR RETURN_CODE,
  	 	MESSAGE_TEXT,
       		CAST (NULL AS CHAR) SORT_KEY,
		CAST (NULL AS CHAR) BILL_ID, 
		CAST (NULL AS INT) SEQNO, 
		CAST (NULL AS CHAR) PER_ID, 
		CAST (NULL AS CHAR) NBR_BILL_COPIES,  
		CAST(NULL AS VARCHAR) ENTITY_NAME1, 
		CAST(NULL AS VARCHAR) ENTITY_NAME2, 
		CAST(NULL AS VARCHAR) ENTITY_NAME3, 
		CAST (NULL AS CHAR) COUNTRY,
		CAST(NULL AS VARCHAR) ADDRESS1, 
		CAST(NULL AS VARCHAR) ADDRESS2, 
		CAST(NULL AS VARCHAR) ADDRESS3, 
		CAST(NULL AS VARCHAR) CITY, 
		CAST(NULL AS VARCHAR) COUNTY, 
		CAST (NULL AS CHAR) STATE, 
		CAST (NULL AS CHAR) POSTAL, 
		CAST (NULL AS CHAR) IN_CITY_LIMIT, 
		CAST (NULL AS CHAR) BATCH_CD,
		CAST(NULL AS INT) BATCH_NBR, 
		CAST (NULL AS CHAR) NO_BATCH_PRT_SW, 
		CAST (NULL AS CHAR) ACCT_ID, 
	   	CAST (NULL AS CHAR) BILL_STAT_FLG, 
		CAST(NULL AS DATETIME) BILL_DT, 
		CAST(NULL AS DATETIME) DUE_DT, 
		CAST(NULL AS VARCHAR) ACCT_ENTITY_NAME, 
	   	CAST (NULL AS CHAR) LANGUAGE_CD, 
		CAST (NULL AS CHAR) CURRENCY_CD, 
		CAST (NULL AS CHAR) CUR_SYMBOL, 
		CAST(NULL AS INT) DECIMAL_POSITIONS, 
		CAST (NULL AS CHAR) CUR_POS_FLG,
		CAST (NULL AS CHAR) OPEN_ITEM_SW, 
		CAST (NULL AS INT) BILL_COPY, 
		CAST (NULL AS CHAR) BSEG_ID, 
	   	CAST (NULL AS CHAR) SA_ID, 
		CAST (NULL AS DATETIME) START_DT, 
		CAST(NULL AS DATETIME) END_DT, 
		CAST(NULL AS CHAR) BILL_CYC_CD,
	   	CAST(NULL AS DATETIME) WIN_START_DT, 
		CAST (NULL AS CHAR) EST_SW, 
		CAST (NULL AS CHAR) CLOSING_BSEG_SW,  
	   	CAST (NULL AS CHAR) PREM_ID, 
		CAST (NULL AS CHAR) CIS_DIVISION, 
		CAST (NULL AS CHAR) SA_TYPE_CD, 
	   	CAST(NULL AS VARCHAR) SAT_DESCR,
		CAST (NULL AS CHAR) BILL_PRT_PRIO_FLG,
		CAST (NULL AS CHAR) GRAPH_UOM_CD, 
		CAST (NULL AS INT) HEADER_SEQ, 
	   	CAST(NULL AS DATETIME) BCALC_START_DT, 
		CAST(NULL AS DATETIME) BCALC_END_DT, 
		CAST (NULL AS CHAR) RS_CD, 
		CAST(NULL AS DATETIME) EFFDT, 
	   	CAST (NULL AS CHAR) BILLABLE_CHG_ID, 
		CAST(NULL AS INT) CALC_AMT, 
		CAST(NULL AS VARCHAR) BCALC_DESC_BILL, 
	   	CAST(NULL AS INT) SEQNO, 
		CAST (NULL AS CHAR) PRT_SW, 
		CAST (NULL AS CHAR) APP_IN_SUMM_SW, 
		CAST(NULL AS INT)CALC_LN_AMT, 
	   	CAST (NULL AS CHAR) DST_ID, 
		CAST (NULL AS CHAR) MSR_PEAK_QTY_SW, 
		CAST(NULL AS VARCHAR) DESCR_LN, 
		CAST (NULL AS CHAR) STATE, 
	   	CAST(NULL AS VARCHAR) CITY, 
		CAST (NULL AS CHAR) POSTAL, 
		CAST (NULL AS CHAR) SIBLING_ID 	    
	      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
