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

CREATE PROCEDURE RPTUSER.CIBLPRTG
@P_USER_ID CHAR(8),
@P_GRAPH_UOM_CD CHAR(4),
@P_SA_ID CHAR(10),
@P_START_DT VARCHAR(10),
@P_END_DT VARCHAR(10)
AS

/******************************************************************************
   NAME:       CIBLPRTG
   PURPOSE:    Supply report extract for Crystal Report - Bill Print - SA Comsumption Hist 

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

   DECLARE   @V_LANGUAGE_CD   CHAR(3)
   DECLARE   @V_GRAPH_UOM_CD  CHAR(4)
   DECLARE   @V_SA_ID         CHAR(10)
   DECLARE   @V_START_DT      DATETIME
   DECLARE   @V_END_DT        DATETIME
   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_SA_ID = SA_ID
         FROM CI_SA
         WHERE SA_ID = @P_SA_ID
      
         IF @@ROWCOUNT = 0
            SET @V_RETURN_CD = 1028  -- ERROR: INVALID SA ID
      END

   IF @V_RETURN_CD = 0   
      BEGIN
         IF @P_GRAPH_UOM_CD IS NULL OR @P_GRAPH_UOM_CD = ' '    
            SET @V_GRAPH_UOM_CD = ' '
         ELSE
            BEGIN
               SELECT @V_GRAPH_UOM_CD = UOM_CD
               FROM   CI_UOM
               WHERE  UOM_CD = @P_GRAPH_UOM_CD

               IF @@ROWCOUNT = 0
                  SET @V_RETURN_CD = 1030  -- ERROR: INVALID GRAPH UOM CD
            END    
      END

   IF @V_RETURN_CD = 0
      BEGIN
         IF @P_START_DT IS NULL OR @P_START_DT = ' '
            SET @V_START_DT = GETDATE() - 7    
         ELSE
            SET @V_START_DT = CAST(@P_START_DT AS DATETIME)     

         IF @P_END_DT IS NULL OR @P_END_DT = ' '
            SET @V_END_DT = GETDATE()
         ELSE
            SET @V_END_DT = CAST(@P_END_DT AS DATETIME) 
      
         IF @V_START_DT = NULL OR @V_END_DT = NULL
            SET @V_RETURN_CD = 1000  -- SET RETURN CODE TO BE INVALID DATETIME PARAMETERS
      END   
   
   IF @V_RETURN_CD = 0
      BEGIN
         SELECT @V_RETURN_CD RETURN_CODE
                ,CAST(NULL AS VARCHAR) MESSAGE_TEXT
                ,BS.SA_ID
                ,BS.START_DT
                ,BS.BSEG_ID
                ,BS.END_DT
                ,SQ.UOM_CD
                ,SQ.TOU_CD
                ,SQ.SQI_CD
                ,SQ.BILL_SQ AS "QTY"
         FROM   CI_BSEG BS, CI_BSEG_SQ SQ 
         WHERE  BS.BSEG_STAT_FLG = '50' 
                AND BS.BSEG_ID = SQ.BSEG_ID
                AND BS.SA_ID = @V_SA_ID 
                AND SQ.UOM_CD = @V_GRAPH_UOM_CD  
                AND BS.START_DT >= @V_START_DT
                AND BS.START_DT <= @V_END_DT 
         ORDER BY 3, 4, 5 
      END
  
   ELSE  
      BEGIN
         SELECT MESSAGE_NBR RETURN_CODE
                ,MESSAGE_TEXT
                ,CAST(NULL AS CHAR) SA_ID
                ,CAST(NULL AS DATETIME) START_DT
                ,CAST(NULL AS CHAR) BSEG_ID
                ,CAST(NULL AS DATETIME) END_DT
                ,CAST(NULL AS CHAR) UOM_CD
                ,CAST(NULL AS CHAR) TOU_CD
                ,CAST(NULL AS CHAR) SQI_CD
                ,CAST(NULL AS INT) QTY
         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

