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

CREATE PROCEDURE RPTUSER.CIBLPRTL
@P_USER_ID CHAR(8),
@P_BSEG_ID CHAR(12)
AS

/******************************************************************************
   NAME:       CIBLPRTL
   PURPOSE:    Supply report extract for Crystal Report - Bill Print - Count Bseg Reads 

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

   DECLARE   @V_LANGUAGE_CD   CHAR(3)
   DECLARE   @V_BSEG_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  -- ERROR: INVALID USER ID
      END

   IF @V_RETURN_CD = 0 
      BEGIN
         SELECT @V_BSEG_ID = BSEG_ID
         FROM   CI_BSEG
         WHERE  BSEG_ID = @P_BSEG_ID
      
         IF @@ROWCOUNT = 0
               SET @V_RETURN_CD = 1029  -- ERROR: INVALID BILL SEGMENT ID
      END
   
   IF @V_RETURN_CD = 0
      BEGIN
         SELECT @V_RETURN_CD RETURN_CODE 
                ,CAST(NULL AS VARCHAR) MESSAGE_TEXT
                ,A.BSEG_ID
                ,'BRAL' AS BILL_PRT_FLG
                ,COUNT(A.SEQNO) AS CNT_READS 
         FROM   CI_BSEG_READ A 
         WHERE  A.BSEG_ID = @V_BSEG_ID 
         GROUP BY A.BSEG_ID 
         UNION
         SELECT @V_RETURN_CD RETURN_CODE
                ,CAST(NULL AS VARCHAR) MESSAGE_TEXT
                ,B.BSEG_ID
                ,'BRAL' AS BILL_PRT_FLG
                ,0 AS CNT_READS
         FROM   CI_BSEG B 
         WHERE NOT EXISTS (SELECT 'X'
                           FROM CI_BSEG_READ R2 
                           WHERE R2.BSEG_ID = B.BSEG_ID)
               AND B.BSEG_ID = @V_BSEG_ID 
      END
  
   ELSE  
      BEGIN
         SELECT MESSAGE_NBR RETURN_CODE
                ,MESSAGE_TEXT
                ,CAST(NULL AS CHAR) BSEG_ID
                ,CAST(NULL AS CHAR) BILL_PRT_FLG
                ,CAST(NULL AS INT) CNT_READS
         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
