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

CREATE PROCEDURE RPTUSER.CIBLPRTE
@P_USER_ID CHAR(8),
@P_BILL_ID CHAR(12),
@P_LANGUAGE_CD CHAR(3)
AS

/******************************************************************************
   NAME:       CIBLPRTE
   PURPOSE:    Supply report extract for Crystal Report - Get Bill Auto Pay

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

   DECLARE   @V_LANGUAGE_CD   CHAR(3)
   DECLARE   @V_USER_LANG_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   CI_LANGUAGE
   WHERE  LANGUAGE_CD = @P_LANGUAGE_CD

   IF @@ROWCOUNT = 0 
         SET @V_LANGUAGE_CD = 'ENG'

   IF @V_RETURN_CD = 0
      BEGIN
         SELECT @V_USER_LANG_CD = LANGUAGE_CD
         FROM SC_USER
         WHERE SC_USER.USER_ID = @P_USER_ID

         IF @@ROWCOUNT = 0  
            BEGIN
               SET @V_USER_LANG_CD = 'ENG'
               SET @V_RETURN_CD = 1003  -- ERROR: INVALID USER ID  
            END      
      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
         --Get Autopay for a bill 
         SELECT @V_RETURN_CD RETURN_CODE
                ,CAST(NULL AS VARCHAR) MESSAGE_TEXT
                ,A.BILL_ID
                ,A.APAY_SRC_CD
                ,A.EXT_ACCT_ID
                ,A.SCHED_EXTRACT_DT
                ,BL.APAY_SRC_NAME
                ,BL.DESCR AS APAY_SRC_DESCR
                ,CL.DESCR AS TNDR_TYPE_DESCR
                ,C.TENDER_TYPE_CD  
         FROM   CI_APAY_CLR_STG A
                ,CI_APAY_SRC B
                ,CI_APAY_SRC_L BL
                ,CI_TENDER_TYPE C
                ,CI_TENDER_TYPE_L CL		 	  
         WHERE  A.BILL_ID = @V_BILL_ID
                AND B.APAY_SRC_CD = A.APAY_SRC_CD 
                AND BL.APAY_SRC_CD = B.APAY_SRC_CD
                AND BL.LANGUAGE_CD = @V_LANGUAGE_CD
                AND C.TENDER_TYPE_CD = B.TENDER_TYPE_CD
                AND CL.TENDER_TYPE_CD = C.TENDER_TYPE_CD
                AND CL.LANGUAGE_CD = @V_LANGUAGE_CD	
      END

   ELSE    
      BEGIN
         SELECT MESSAGE_NBR RETURN_CODE
                ,MESSAGE_TEXT
                ,CAST(NULL AS CHAR) BILL_ID
                ,CAST(NULL AS CHAR) APAY_SRC_CD
                ,CAST(NULL AS VARCHAR) EXT_ACCT_ID
                ,CAST( NULL AS DATETIME) SCHED_EXTRACT_DT
                ,CAST(NULL AS VARCHAR) APAY_SRC_NAME
                ,CAST(NULL AS VARCHAR) APAY_SRC_DESCR
                ,CAST(NULL AS VARCHAR) TNDR_TYPE_DESCR
                ,CAST(NULL AS CHAR) TENDER_TYPE_CD 
         FROM   CI_MSG_L
         WHERE  MESSAGE_CAT_NBR = 30
                AND MESSAGE_NBR = @V_RETURN_CD
	        AND LANGUAGE_CD = @V_USER_LANG_CD	 
      END  

RETURN  

END

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

