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

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

/******************************************************************************
   NAME:       CIBLPRTF
   PURPOSE:    Supply report extract for Crystal Report - Get Bill Segment 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 BSEG ID
      END

   IF @V_RETURN_CD = 0
      BEGIN
         SELECT @V_RETURN_CD RETURN_CODE
                ,CAST(NULL AS VARCHAR) MESSAGE_TEXT
                ,A.BSEG_ID
                ,A.SP_ID
                ,A.SEQNO
                ,A.REG_CONST
                ,A.USAGE_FLG
                ,A.USE_PCT
                ,A.HOW_TO_USE_FLG
                ,A.MSR_PEAK_QTY_SW
                ,A.UOM_CD
                ,A.TOU_CD
                ,A.SQI_CD
                ,A.START_REG_READ_ID 
                ,A.START_READ_DTTM
                ,A.START_REG_READING
                ,A.END_REG_READ_ID
                ,A.END_READ_DTTM
                ,A.END_REG_READING
                ,A.MSR_QTY
                ,A.FINAL_UOM_CD
                ,A.FINAL_TOU_CD
                ,A.FINAL_SQI_CD
                ,A.FINAL_REG_QTY
                ,B.BADGE_NBR
                ,B.NBR_OF_DGTS_RGT 
         FROM   CI_BSEG_READ A 
                ,(SELECT A.REG_READ_ID
                         ,A.MR_ID
                         ,C.MTR_CONFIG_ID
                         ,D.MTR_ID
                         ,D.BADGE_NBR
                         ,D.MTR_TYPE_CD
                         ,E.NBR_OF_DGTS_RGT 
                  FROM   CI_REG_READ A 
                         ,CI_MR B 
                         ,CI_MTR_CONFIG C 
                         ,CI_MTR D 
                         ,CI_REG E 
                  WHERE  B.MR_ID = A.MR_ID 
                         AND C.MTR_CONFIG_ID = B.MTR_CONFIG_ID 
                         AND D.MTR_ID = C.MTR_ID 
                         AND E.REG_ID = A.REG_ID) B -- CI_RGRD_MTR_VW  
         WHERE  A.BSEG_ID = @V_BSEG_ID
                AND B.REG_READ_ID = A.END_REG_READ_ID
      END
  
   ELSE   
      BEGIN
        SELECT MESSAGE_NBR RETURN_CODE
               ,MESSAGE_TEXT
               ,CAST(NULL AS CHAR) BSEG_ID
               ,CAST(NULL AS CHAR) SP_ID
               ,CAST(NULL AS INT) SEQNO
               ,CAST(NULL AS INT) REG_CONST
               ,CAST(NULL AS CHAR) USAGE_FLG
               ,CAST(NULL AS INT) USE_PCT
               ,CAST(NULL AS CHAR) HOW_TO_USE_FLG
               ,CAST(NULL AS CHAR) MSR_PEAK_QTY_SW
               ,CAST(NULL AS CHAR) UOM_CD
               ,CAST(NULL AS CHAR) TOU_CD
               ,CAST(NULL AS CHAR) SQI_CD
               ,CAST(NULL AS CHAR) START_REG_READ_ID
               ,CAST(NULL AS DATETIME) START_READ_DTTM
               ,CAST(NULL AS INT) START_REG_READING
               ,CAST(NULL AS CHAR) END_REG_READ_ID
               ,CAST(NULL AS DATETIME) END_READ_DTTM
               ,CAST(NULL AS INT) END_REG_READING
               ,CAST(NULL AS INT) MSR_QTY
               ,CAST(NULL AS CHAR) FINAL_UOM_CD
               ,CAST(NULL AS CHAR) FINAL_TOU_CD
               ,CAST(NULL AS CHAR) FINAL_SQI_CD
               ,CAST(NULL AS INT) FINAL_REG_QTY
               ,CAST(NULL AS CHAR) BADGE_NBR
               ,CAST(NULL AS INT) NBR_OF_DGTS_RGT 
         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

