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

CREATE PROCEDURE RPTUSER.CIBLPRTD
@P_USER_ID CHAR(8),
@P_PREM_ID CHAR(10)
AS

/******************************************************************************
   NAME:       CIBLPRTD
   PURPOSE:    Supply report extract for Crystal Report - Get Premise Info

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

   DECLARE   @V_LANGUAGE_CD   CHAR(3)  
   DECLARE   @V_PREM_ID       CHAR(10)
   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_PREM_ID = PREM_ID
         FROM CI_PREM
         WHERE PREM_ID = @P_PREM_ID
      
         IF @@ROWCOUNT = 0
         SET @V_RETURN_CD = 1028  -- ERROR: INVALID PREM ID
      END

   IF @V_RETURN_CD = 0
      BEGIN
         --Get Premise Info
         SELECT @V_RETURN_CD RETURN_CODE
                ,CAST(NULL AS VARCHAR) MESSAGE_TEXT 
                ,A.PREM_ID
                ,A.PREM_TYPE_CD
                ,A.LL_ID
                ,A.MAIL_ADDR_SW
                ,A.TREND_AREA_CD
                ,A.COUNTRY
                ,A.ADDRESS1
                ,A.ADDRESS2
                ,A.ADDRESS3
                ,A.ADDRESS4
                ,A.CITY
                ,A.NUM1
                ,A.NUM2
                ,A.HOUSE_TYPE
                ,A.COUNTY
                ,A.STATE
                ,A.POSTAL
                ,A.GEO_CODE
                ,A.IN_CITY_LIMIT 
         FROM   CI_PREM A 
         WHERE  A.PREM_ID = @V_PREM_ID
      END

   ELSE
     BEGIN
         SELECT MESSAGE_NBR RETURN_CODE
                ,MESSAGE_TEXT
                ,CAST(NULL AS CHAR) PREM_ID
                ,CAST(NULL AS CHAR) PREM_TYPE_CD
                ,CAST(NULL AS CHAR) LL_ID
                ,CAST(NULL AS CHAR) MAIL_ADDR_SW
                ,CAST(NULL AS CHAR) TREND_AREA_CD
                ,CAST(NULL AS CHAR) COUNTRY
                ,CAST(NULL AS VARCHAR) ADDRESS1
                ,CAST(NULL AS VARCHAR) ADDRESS2
                ,CAST(NULL AS VARCHAR) ADDRESS3
                ,CAST(NULL AS VARCHAR) ADDRESS4
                ,CAST(NULL AS VARCHAR) CITY
                ,CAST(NULL AS CHAR) NUM1
                ,CAST(NULL AS CHAR) NUM2
                ,CAST(NULL AS CHAR) HOUSE_TYPE
                ,CAST(NULL AS VARCHAR) COUNTY
                ,CAST(NULL AS CHAR) STATE
                ,CAST(NULL AS CHAR) POSTAL
                ,CAST(NULL AS CHAR) GEO_CODE
                ,CAST(NULL AS CHAR) IN_CITY_LIMIT 			
         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

