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

CREATE PROCEDURE CIPMTBAL
@P_USER_ID CHAR(8),
@P_FROM_DT VARCHAR(26),
@P_TO_DT  VARCHAR(26)



AS
   DECLARE   @V_LANGUAGE_CD  CHAR(3)
   DECLARE   @V_FROM_DT DATETIME
   DECLARE   @V_TO_DT   DATETIME
   DECLARE   @V_RETURN_CD    INT
   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
      END


/******************************************************************************
   Checking for nulls in parameters and setting default values for select 
   statements
******************************************************************************/
	  
      IF @V_RETURN_CD = 0
      BEGIN 
           BEGIN
		SELECT @V_FROM_DT = NULL;
		SELECT @V_TO_DT = NULL;
  	   END

	   IF   @P_FROM_DT IS NULL OR @P_FROM_DT = ' '
	   	SELECT @V_FROM_DT = getDate() - 7
	   ELSE 
		SELECT @V_FROM_DT = CAST(@P_FROM_DT AS DATETIME);

	   IF   @P_TO_DT IS NULL OR @P_TO_DT = ' '
		SELECT @V_TO_DT = getDate();
	   ELSE 
		SELECT @V_TO_DT = CAST(@P_TO_DT AS DATETIME);

            IF @V_TO_DT = NULL OR @V_FROM_DT = NULL
               SELECT @V_RETURN_CD = 1000  	-- Set return code to be invalid datetime parameters
      	
     END

 
     IF @V_RETURN_CD = 0
     BEGIN


/********************************************************************
This is the main select for this SP. Most of the logic is in the
Sub-Select statement below. The main select handles the code-descr
fields display.
*********************************************************************/
	SELECT @V_RETURN_CD RETURN_CODE
	  	,CAST(NULL AS VARCHAR) MESSAGE_TEXT
		,CAST (@V_FROM_DT AS DATETIME) V_FROM_DT
		,CAST (@V_TO_DT AS DATETIME) V_TO_DT
		,TNDRS.CURRENCY_CD
	        ,TNDRS.TNDR_SOURCE_CD
	        ,CI_TNDR_SRCE_L.DESCR TNDR_SRC
	        ,TNDRS.TENDER_TYPE_CD
	        ,CI_TENDER_TYPE_L.DESCR TNDR_TYPE
		,TNDRS.TNDR_STATUS_FLG
		,CI_LOOKUP_VAL_L.DESCR TNDR_STATUS
	        ,TNDRS.NUM_PAY
	        ,TNDRS.TOT_PAY
		,CI_CURRENCY_CD.CUR_SYMBOL
		,CI_CURRENCY_CD.CUR_POS_FLG
		,CI_CURRENCY_CD.DECIMAL_POSITIONS
		,CI_CURRENCY_CD.SCALE_POSITIONS
		,CI_CURRENCY_CD_L.DESCR CURR_DESCR

	FROM CI_TNDR_SRCE_L, CI_TENDER_TYPE_L,CI_LOOKUP_VAL_L, CI_CURRENCY_CD, CI_CURRENCY_CD_L,


/****************************************************************************
This is the Sub-Select statement. It gets all the payments that
have been received during a given date range (from and to date parameters).
****************************************************************************/

		(
		SELECT CI_PAY_TNDR.CURRENCY_CD,
		   CI_TNDR_CTL.TNDR_SOURCE_CD,
		   CI_PAY_TNDR.TENDER_TYPE_CD,
		   CI_PAY_TNDR.TNDR_STATUS_FLG,
	           COUNT(CI_PAY_TNDR.TENDER_AMT) NUM_PAY,
	           SUM(CI_PAY_TNDR.TENDER_AMT) TOT_PAY

		FROM CI_PAY_EVENT, CI_PAY_TNDR, CI_TNDR_CTL

		WHERE

		   CI_PAY_EVENT.CRE_DTTM BETWEEN @V_FROM_DT AND @V_TO_DT AND
	           CI_PAY_TNDR.PAY_EVENT_ID =  CI_PAY_EVENT.PAY_EVENT_ID AND
		   CI_TNDR_CTL.TNDR_CTL_ID = CI_PAY_TNDR.TNDR_CTL_ID

		GROUP BY CI_PAY_TNDR.CURRENCY_CD,
	               TNDR_SOURCE_CD,TENDER_TYPE_CD, TNDR_STATUS_FLG) TNDRS

	WHERE

		CI_TNDR_SRCE_L.TNDR_SOURCE_CD = TNDRS.TNDR_SOURCE_CD AND
		CI_TNDR_SRCE_L.LANGUAGE_CD = @V_LANGUAGE_CD AND
		CI_TENDER_TYPE_L.TENDER_TYPE_CD =TNDRS.TENDER_TYPE_CD AND
		CI_TENDER_TYPE_L.LANGUAGE_CD = @V_LANGUAGE_CD AND
		CI_LOOKUP_VAL_L.FIELD_NAME = 'TNDR_STATUS_FLG' AND
		CI_LOOKUP_VAL_L.FIELD_VALUE = TNDRS.TNDR_STATUS_FLG
		AND CI_LOOKUP_VAL_L.LANGUAGE_CD = @V_LANGUAGE_CD
		AND TNDRS.CURRENCY_CD = CI_CURRENCY_CD.CURRENCY_CD
		AND CI_CURRENCY_CD.CURRENCY_CD = CI_CURRENCY_CD_L.CURRENCY_CD
		AND CI_CURRENCY_CD_L.LANGUAGE_CD = @V_LANGUAGE_CD


	ORDER BY TNDRS.CURRENCY_CD,
	         TNDRS.TNDR_SOURCE_CD, TNDRS.TENDER_TYPE_CD;
        END
      ELSE
         BEGIN

	      SELECT   MESSAGE_NBR RETURN_CODE
	 	  ,MESSAGE_TEXT
		,CAST (@V_FROM_DT AS DATETIME) V_FROM_DT
		,CAST (@V_TO_DT AS DATETIME) V_TO_DT
		,CAST(NULL AS CHAR) CURRENCY_CD
	        ,CAST(NULL AS CHAR) TNDR_SOURCE_CD
	        ,CAST(NULL AS VARCHAR) TNDR_SRC
	        ,CAST(NULL AS CHAR) TENDER_TYPE_CD
	        ,CAST(NULL AS VARCHAR) TNDR_TYPE
	        ,CAST(NULL AS CHAR) TNDR_STATUS_FLG
		,CAST(NULL AS VARCHAR) TNDR_STATUS
	        ,CAST(NULL AS INT) NUM_PAY
        	,CAST(NULL AS NUMERIC) TOT_PAY
		,CAST(NULL AS CHAR) CUR_SYMBOL
		,CAST(NULL AS CHAR) CUR_POS_FLG
		,CAST(NULL AS NUMERIC) DECIMAL_POSITIONS
		,CAST(NULL AS NUMERIC) SCALE_POSITIONS
  	  	,CAST(NULL AS VARCHAR)  CURR_DESCR
 
      		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

