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

CREATE PROCEDURE  CIBLPRTA
@P_USER_ID CHAR(8),
@P_BILL_ID CHAR(12)


AS
   DECLARE   @V_LANGUAGE_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   SC_USER
     WHERE  SC_USER.USER_ID = @P_USER_ID;
     IF (@@ROWCOUNT = 0)	
     BEGIN
          SELECT @V_LANGUAGE_CD = 'ENG'
          SELECT @V_RETURN_CD = 1003  -- ERROR: INVALID USER ID
     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 )
	             SELECT @V_RETURN_CD = 1027  -- ERROR: INVALID BILL ID
        END


     IF @V_RETURN_CD = 0
      BEGIN

  --- Get Previous Balance
         SELECT @V_RETURN_CD RETURN_CODE
                ,CAST (NULL AS VARCHAR) MESSAGE_TEXT
                ,A.CURRENCY_CD 
                ,SUM(A.CUR_AMT) SUM_CUR_AMT 
		,SUM(A.TOT_AMT) SUM_TOT_AMT
                ,'ASBL' BILL_PRT_FLG
          FROM     CI_BILL_SA A
          WHERE    A.BILL_ID = @V_BILL_ID
          GROUP BY A.CURRENCY_CD
      UNION
  --- Get Current Charges
         SELECT @V_RETURN_CD RETURN_CODE
                ,CAST (NULL AS VARCHAR) MESSAGE_TEXT
                ,B.CURRENCY_CD
                ,SUM(B.CUR_AMT) SUM_CUR_AMT
                ,SUM(B.TOT_AMT) SUM_TOT_AMT
                ,'ASBS' BILL_PRT_FLG
          FROM  CI_FT B
          WHERE B.BILL_ID = @V_BILL_ID
                AND B.FT_TYPE_FLG IN ('BS','BX')
                AND B.CORRECTION_SW = 'N'
                AND B.SHOW_ON_BILL_SW = 'Y'
          GROUP BY B.CURRENCY_CD 	  
      UNION
  --- Get Corrections
         SELECT @V_RETURN_CD RETURN_CODE
                ,CAST (NULL AS VARCHAR) MESSAGE_TEXT
                ,C.CURRENCY_CD
                ,SUM(C.CUR_AMT) SUM_CUR_AMT
                ,SUM(C.TOT_AMT) SUM_TOT_AMT
                ,'ASBC' BILL_PRT_FLG
         FROM     CI_FT C
         WHERE    C.BILL_ID = @V_BILL_ID
                  AND C.FT_TYPE_FLG IN ('BS','BX')
                  AND C.CORRECTION_SW = 'Y'
                  AND C.SHOW_ON_BILL_SW = 'Y'
         GROUP BY C.CURRENCY_CD 
     UNION
  --- Get Adjustments
         SELECT @V_RETURN_CD RETURN_CODE
               ,CAST (NULL AS VARCHAR) MESSAGE_TEXT
               ,D.CURRENCY_CD
               ,SUM(D.CUR_AMT) SUM_CUR_AMT
               ,SUM(D.TOT_AMT) SUM_TOT_AMT
               ,'ASAD' BILL_PRT_FLG
         FROM     CI_FT D
         WHERE    D.BILL_ID = @V_BILL_ID
               AND D.FT_TYPE_FLG IN ('AD','AX')
               AND D.SHOW_ON_BILL_SW = 'Y'
          GROUP BY D.CURRENCY_CD 
     UNION
  --- Get Payment Received
         SELECT @V_RETURN_CD RETURN_CODE
                ,CAST (NULL AS VARCHAR) MESSAGE_TEXT
                ,E.CURRENCY_CD
                ,SUM(E.CUR_AMT) SUM_CUR_AMT
                ,SUM(E.TOT_AMT) SUM_TOT_AMT
                ,'ASPS' BILL_PRT_FLG
          FROM     CI_FT E
          WHERE    E.BILL_ID = @V_BILL_ID
             AND E.FT_TYPE_FLG IN  ('PS','PX')
             AND E.SHOW_ON_BILL_SW = 'Y'
          GROUP BY E.CURRENCY_CD ;
     END
   ELSE
     BEGIN

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


