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

CREATE PROCEDURE RPTUSER.CIBLPRTJ
@P_USER_ID CHAR(8),
@P_BILL_ID CHAR(12)
AS
/******************************************************************************
   NAME:       CIBLPRTJ
   PURPOSE:    Supply report extract for Crystal Report - Bill Print - Count Adj and Bill Msgs

******************************************************************************/

   DECLARE   @V_LANGUAGE_CD   CHAR(3)
   DECLARE   @V_BILL_ID       CHAR(12)
   DECLARE   @V_RETURN_CD     INT

   SET @V_RETURN_CD = 0

BEGIN


/******************************************************************************
   Checking for nulls in parameters and setting default values for select 
   statements
******************************************************************************/

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

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

   IF @V_RETURN_CD = 0
      BEGIN
         SELECT @V_BILL_ID = BILL_ID
         FROM   CI_BILL
         WHERE  BILL_ID = @P_BILL_ID

         IF @@ROWCOUNT = 0
            SET @V_RETURN_CD = 1027  -- ERROR: INVALID BILL ID
      END
   
   IF @V_RETURN_CD = 0
      BEGIN
         SELECT @V_RETURN_CD RETURN_CODE
                ,CAST(NULL AS VARCHAR) MESSAGE_TEXT
                ,FT.BILL_ID
                ,'BADJ' AS BILL_PRT_FLG
                ,COUNT(FT.SIBLING_ID) AS CNT
         FROM   CI_FT FT
                ,CI_ADJ ADJ
                ,CI_SA SA 
         WHERE  FT.FT_TYPE_FLG IN ('AD','AX') 
                AND ADJ.ADJ_ID = FT.SIBLING_ID 
                AND SA.SA_ID = ADJ.SA_ID  
                AND BILL_ID = @V_BILL_ID 
         GROUP BY FT.BILL_ID 
  
         UNION 

         SELECT @V_RETURN_CD RETURN_CODE
                ,CAST(NULL AS VARCHAR) MESSAGE_TEXT
                ,B.BILL_ID
                ,'BADJ' AS BILL_PRT_FLG
                ,0 AS CNT
         FROM   CI_BILL B 
         WHERE  NOT EXISTS (SELECT 'X' 
                            FROM   CI_FT FT
                                   ,CI_ADJ ADJ
                                   ,CI_SA SA 
                                   WHERE  FT.FT_TYPE_FLG IN ('AD','AX') 
                                          AND ADJ.ADJ_ID = FT.SIBLING_ID 
                                          AND SA.SA_ID = ADJ.SA_ID
                                          AND FT.BILL_ID = B.BILL_ID) 
                AND BILL_ID = @V_BILL_ID 

         UNION 

         SELECT @V_RETURN_CD RETURN_CODE
                ,CAST(NULL AS VARCHAR) MESSAGE_TEXT
                ,A.BILL_ID
                ,'BMSG' AS BILL_PRT_FLG
                ,COUNT(A.BILL_MSG_CD) AS CNT 
         FROM   CI_BILL_MSGS A 
         WHERE  A.BILL_ID = @V_BILL_ID
         GROUP BY A.BILL_ID

         UNION
 
         SELECT @V_RETURN_CD RETURN_CODE
                ,CAST(NULL AS VARCHAR) MESSAGE_TEXT
                ,B.BILL_ID
                ,'BMSG' AS BILL_PRT_FLG
                ,COUNT(B.BILL_MSG_CD) AS CNT 
         FROM   (SELECT DISTINCT C.BILL_ID
                        ,E.BILL_MSG_CD 
                 FROM   CI_BSEG C
                        ,CI_BSEG_CALC D
                        ,CI_RS_BMSG E 
                 WHERE  C.BSEG_ID = D.BSEG_ID 
                        AND D.RS_CD > ' ' 
                        AND E.RS_CD = D.RS_CD 
                        AND E.START_DT <= C.START_DT 
                        AND (E.END_DT IS NULL OR E.END_DT >= C.START_DT) 
                        AND NOT EXISTS (SELECT G.BILL_ID 
                                        FROM   CI_BILL_MSGS G 
                                        WHERE  G.BILL_ID = C.BILL_ID
                                               AND G.BILL_MSG_CD = E.BILL_MSG_CD)) B 
         WHERE  B.BILL_ID = @V_BILL_ID
         GROUP BY B.BILL_ID

         UNION 

         SELECT @V_RETURN_CD RETURN_CODE
                ,CAST(NULL AS VARCHAR) MESSAGE_TEXT
                ,D.BILL_ID
                ,'BMSG' AS BILL_PRT_FLG
                ,0 AS CNT 
         FROM   CI_BILL D 
         WHERE  NOT EXISTS (SELECT 'X' 
                            FROM (SELECT A.BILL_ID
                                         ,A.BILL_MSG_CD
                                  FROM   CI_BILL_MSGS A 
                                       
                                  UNION 
                                  
                                  SELECT DISTINCT C.BILL_ID
                                         ,E.BILL_MSG_CD 
                                  FROM   CI_BSEG C
                                         ,CI_BSEG_CALC D
                                         ,CI_RS_BMSG E 
                                  WHERE  C.BSEG_ID = D.BSEG_ID 
                                         AND D.RS_CD > ' ' 
                                         AND E.RS_CD = D.RS_CD 
                                         AND E.START_DT <= C.START_DT 
                                         AND (E.END_DT IS NULL OR E.END_DT >= C.START_DT) 
                                         AND NOT EXISTS (SELECT G.BILL_ID FROM CI_BILL_MSGS G 
                                                         WHERE  G.BILL_ID = C.BILL_ID 
                                                                AND G.BILL_MSG_CD = E.BILL_MSG_CD)) C 
                            WHERE C.BILL_ID = D.BILL_ID)
                AND BILL_ID = @V_BILL_ID 		  
      END
  
   ELSE  
      BEGIN
         SELECT MESSAGE_NBR RETURN_CODE
                ,MESSAGE_TEXT
                ,CAST(NULL AS CHAR) BILL_ID
                ,CAST(NULL AS CHAR) BILL_PRT_FLG
                ,CAST(NULL AS INT) CNT
         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
