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

CREATE PROCEDURE  CIBLPRTB
@P_USER_ID CHAR(8),
@P_BILL_ID CHAR(12),
@P_LANGUAGE_CD CHAR(3)


AS
   DECLARE   @V_LANGUAGE_CD  CHAR(3)
   DECLARE   @V_USER_LANG_CD  CHAR(3)
   DECLARE   @V_RETURN_CD  INT
   DECLARE   @V_BILL_ID CHAR(12)
  

   SELECT @V_RETURN_CD = 0

 BEGIN

     SELECT @V_LANGUAGE_CD=LANGUAGE_CD
     FROM   CI_LANGUAGE
     WHERE  LANGUAGE_CD = @P_LANGUAGE_CD;
     IF (@@ROWCOUNT = 0)	
     BEGIN
          SELECT @V_LANGUAGE_CD = 'ENG'
     END

     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
	           SELECT @V_USER_LANG_CD = 'ENG'
	           SELECT @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)	
           BEGIN
	           SELECT @V_RETURN_CD = 1027  -- ERROR: INVALID BILL ID
           END
     END
     IF @V_RETURN_CD = 0
	BEGIN
       --- Bill Charges BY debt class


            SELECT @V_RETURN_CD RETURN_CODE
                   ,CAST(NULL AS VARCHAR) MESSAGE_TEXT 
                   ,SUM(A.CUR_AMT) AS SUM_CUR_AMT
                   ,SUM(A.TOT_AMT) AS SUM_TOT_AMT
                   ,A.CURRENCY_CD
                   ,D.DEBT_CL_CD
                   ,DL.DESCR 
            FROM  CI_FT A, CI_SA B, CI_SA_TYPE C, CI_DEBT_CL D, CI_DEBT_CL_L DL			    
            WHERE B.SA_ID = A.SA_ID 
              AND C.CIS_DIVISION = B.CIS_DIVISION
              AND C.SA_TYPE_CD = B.SA_TYPE_CD 
              AND D.DEBT_CL_CD = C.DEBT_CL_CD
              AND D.DEBT_CL_CD = DL.DEBT_CL_CD
              AND DL.LANGUAGE_CD = @V_LANGUAGE_CD 			   			    
              AND A.BILL_ID = @V_BILL_ID 
              AND A.SHOW_ON_BILL_SW = 'Y' 
              AND A.FT_TYPE_FLG IN ('BS','BX') 
              AND A.CORRECTION_SW = 'N' 
            GROUP BY A.CURRENCY_CD, DL.DESCR, D.DEBT_CL_CD 
            ORDER BY DL.DESCR;
       END
     ELSE
       BEGIN


   	      SELECT MESSAGE_NBR RETURN_CODE
   	             ,MESSAGE_TEXT
   	             ,CAST(NULL AS INT) SUM_CUR_AMT
   	             ,CAST(NULL AS INT) SUM_TOT_AMT
                   ,CAST(NULL AS CHAR) CURRENCY_CD
                   ,CAST(NULL AS CHAR) DEBT_CL_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

