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

CREATE PROCEDURE RPTUSER.CIBLPRTN
@P_USER_ID CHAR(8),
@P_LANGUAGE_CD CHAR(3)
AS

/******************************************************************************
   NAME:       CIBLPRTN
   PURPOSE:    Supply report extract for Crystal Report - Get Adj Cancel Reasons

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

   DECLARE   @V_LANGUAGE_CD   CHAR(3)
   DECLARE   @V_USER_LANG_CD  CHAR(3)
   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_RETURN_CD RETURN_CODE
                ,CAST(NULL AS VARCHAR) MESSAGE_TEXT 
                ,A.CAN_RSN_CD
                ,AL.DESCR 
         FROM   CI_ADJ_CAN_RSN A 
                ,CI_ADJ_CAN_RSN_L AL
         WHERE  A.CAN_RSN_CD = AL.CAN_RSN_CD
                AND AL.LANGUAGE_CD = @V_LANGUAGE_CD		  
   END
  
   ELSE  
       BEGIN
         SELECT MESSAGE_NBR RETURN_CODE
                ,MESSAGE_TEXT
                ,CAST(NULL AS CHAR) CAN_RSN_CD
                ,CAST(NULL AS VARCHAR) DESCR
         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

