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

CREATE PROCEDURE CIRCVAGA
@P_USER_ID CHAR(8),
@P_CUTOFF_DATE VARCHAR(26),
@P_B1_LIMIT NUMERIC,
@P_B2_LIMIT NUMERIC,
@P_B3_LIMIT NUMERIC
AS
	DECLARE @V_B1_LIMIT NUMERIC
	DECLARE @V_B2_LIMIT NUMERIC
	DECLARE @V_B3_LIMIT NUMERIC
	DECLARE @V_LANGUAGE_CD CHAR(3)
	DECLARE @V_CUTOFF_DATE DATETIME
	DECLARE @V_RETURN_CD   INT

	SELECT @V_LANGUAGE_CD = 'ENG'
	SELECT @V_RETURN_CD = 0

BEGIN
	
/******************************************************************************
   Checking for nulls in parameters and setting default values for select 
   statements
******************************************************************************/

   IF @P_CUTOFF_DATE IS NULL OR @P_CUTOFF_DATE = ' '
	SELECT @V_CUTOFF_DATE = getdate() - 7
   ELSE 
	SELECT @V_CUTOFF_DATE = CAST(@P_CUTOFF_DATE AS DATETIME)
	 
   IF @@ERROR <> 0
   BEGIN    
	SELECT @V_RETURN_CD = 1000	-- SET RETURN CODE TO BE INVALID DATETIME PARAMETERS
	SELECT @V_CUTOFF_DATE = NULL
   END

   IF @P_B1_LIMIT IS NULL
	SELECT @V_B1_LIMIT = 30
   ELSE 
	SELECT @V_B1_LIMIT = @P_B1_LIMIT

   IF @P_B2_LIMIT IS NULL
	SELECT @V_B2_LIMIT = 60
   ELSE 
	SELECT @V_B2_LIMIT = @P_B2_LIMIT

   IF @P_B3_LIMIT IS NULL
	SELECT @V_B3_LIMIT = 90
   ELSE 
	SELECT @V_B3_LIMIT = @P_B3_LIMIT

   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

   IF @V_RETURN_CD = 0
   BEGIN
	PRINT @V_B3_LIMIT
	PRINT @V_CUTOFF_DATE
		
   	SELECT  @V_RETURN_CD RETURN_CODE
	  	,CAST (NULL AS VARCHAR) MESSAGE_TEXT
		,CAST (@V_CUTOFF_DATE AS DATETIME) V_CUTOFF_DATE
          	,FT_QUERY.CURRENCY_CD
          	,FT_QUERY.CUST_CL_CD
          	,CI_CUST_CL_L.DESCR CUST_CL
          	,TOT_AR,UB_AR,PAY,BKT1,BKT2,BKT3,BKT4
		,CI_CURRENCY_CD.CUR_SYMBOL
		,CI_CURRENCY_CD.DECIMAL_POSITIONS
		,CI_CURRENCY_CD.SCALE_POSITIONS
		,CI_CURRENCY_CD.CUR_POS_FLG
		,CI_CURRENCY_CD_L.DESCR CURR_DESC
		,FT_QUERY.ACCT_ID,ENTITY_NAME
   	FROM CI_CUST_CL_L,
   	     CI_CURRENCY_CD,
	     CI_CURRENCY_CD_L,
	     CI_ACCT_PER,CI_PER_NAME,
   	     (

/* This is the Sub-Select which represents most of the logic */

		SELECT CI_FT.CURRENCY_CD,
          	       CI_ACCT.CUST_CL_CD,
		       CI_ACCT.ACCT_ID,

/******************************************************************************************
The following logic for summarizing a debt bucket is repeated (more or less)
for buckets 1-4.
First we include only FTs which have positive current amount (charges).
Then we calculate the difference (in days) between the arrears date (ars_dt) and the cutoff
date for the report and check that the difference is within the limits for the checked bucket
(in that case, between 0 and bucket 1 limit).
Since IFs are not allowed here, we had to use the "Sign" function, which tests an expression
and return (-1,0,1) according to its numeric sign.
So, for example instead of "(x<=P<=y)" we did: "(sign(x-p)*sign(p-y))".
If the overall sign of the exp is negative (sign returned -1) then (x<=P<=y) is False.
Otherwise it is True.
So if the condition is True, that means that the difference in days falls in the boundaries
of the bucket and the current amount in the FT (cur_amt) is returned, otherwise 0 is returned.
This logic is repeated for buckets 1-4 with the applicable parameters for the bucket limits.
The "NVL" function is used to control null values. If a date is null then the year 9999 is
used to ensure that the relevant row will not be a part of the report by being beyond any
possible bucket.
We summarize all credits into a separate bucket PAY. Report applies credits and shows calculated
values for each bucket.
THIS SUMMARY IS DONE ON A CUSTOMER CLASS LEVEL !!!
THIS WILL ONLY WORK WITH BALANCE_FORWARD-ACCOUNTING METHOD  
for open item accounting please see the second sql in the UNION clause.
****************************************************************************************/

/* Bucket 1 */
		       SUM(CASE SIGN(CI_FT.CUR_AMT)
				WHEN -1 THEN 0
				WHEN  0 THEN 0
			        ELSE CASE SIGN(0-(DATEDIFF(dd, ISNULL(ARS_DT, CAST('1/1/9999' AS DATETIME)), @V_CUTOFF_DATE)))*
      					  SIGN(DATEDIFF(dd, ISNULL(ARS_DT, CAST('1/1/9999' AS DATETIME)), @V_CUTOFF_DATE)- @V_B1_LIMIT)
				     	  WHEN -1 THEN 0
					  ELSE CUR_AMT
				     END
 			    END
                           ) BKT1,

/* Bucket 2 */
		       SUM(CASE SIGN(CI_FT.CUR_AMT)
				WHEN -1 THEN 0
				WHEN  0 THEN 0
				ELSE CASE SIGN(@V_B1_LIMIT-(DATEDIFF(dd, ISNULL(ARS_DT, CAST('1/1/9999' AS DATETIME)), @V_CUTOFF_DATE)))*
                   			  SIGN(DATEDIFF(dd, ISNULL(ARS_DT, CAST('1/1/9999' AS DATETIME)), @V_CUTOFF_DATE)- @V_B2_LIMIT)
				     	WHEN -1 THEN 0
				     	WHEN  0 THEN CASE @V_B1_LIMIT-(DATEDIFF(dd, @V_CUTOFF_DATE, ISNULL(ARS_DT, CAST('1/1/9999' AS DATETIME))))
						     	WHEN 0 THEN 0
						  	ELSE CUR_AMT
						     END
					ELSE CUR_AMT
 				     END
			   END
                          ) BKT2,

/* Bucket 3 */
			SUM(CASE SIGN(CI_FT.CUR_AMT)
			    	WHEN -1 THEN 0
				WHEN  0 THEN 0
				ELSE CASE SIGN(0-(DATEDIFF(dd, ISNULL(ARS_DT, CAST('1/1/9999' AS DATETIME)), @V_CUTOFF_DATE)))*
      					  SIGN((DATEDIFF(dd, ISNULL(ARS_DT, CAST('1/1/9999' AS DATETIME)), @V_CUTOFF_DATE))- @V_B1_LIMIT)
					WHEN -1 THEN 0
					WHEN  0 THEN CASE @V_B2_LIMIT-DATEDIFF(dd, ISNULL(ARS_DT, CAST('1/1/9999' AS DATETIME)), @V_CUTOFF_DATE)
							WHEN 0 THEN 0
							ELSE CUR_AMT
						     END
					ELSE CUR_AMT
				     END
			    END
			   ) BKT3,

/* Bucket 4 */
			SUM(CASE SIGN(CI_FT.CUR_AMT)
				WHEN -1 THEN 0
				WHEN  0 THEN 0
				ELSE CASE SIGN(@V_B3_LIMIT-DATEDIFF(dd, ISNULL(ARS_DT, CAST('1/1/9999' AS DATETIME)), @V_CUTOFF_DATE))*(-1)
					WHEN -1 THEN 0
					WHEN  0 THEN 0
					ELSE CUR_AMT
				     END
				END
			    ) BKT4,

/* In this selective summary we include all credits (current amount >0 )
  It may be canceled bill segments, credit adjustments, payments, etc. */
			SUM(CASE SIGN(CI_FT.CUR_AMT)
				 WHEN  1 THEN 0
				 WHEN  0 THEN 0
				 WHEN -1 THEN CUR_AMT
			         ELSE  0
			     END
			    ) PAY,		

/* In this selective summary we include all unbilled receivables
 where ars_dt is empty     */
			SUM(CASE SIGN(CI_FT.CUR_AMT)
				WHEN -1 THEN 0
				WHEN  0 THEN 0
				ELSE
					CASE ISNULL(ARS_DT, CAST('1/1/9999' AS DATETIME))
						WHEN CAST('1/1/9999' AS DATETIME) THEN CUR_AMT
						ELSE 0
					END
			    END
			) UB_AR,

			SUM(CUR_AMT) TOT_AR

		FROM CI_ACCT, CI_SA,CI_FT,CI_CUST_CL
		WHERE
		     CI_FT.FREEZE_SW = 'Y' AND
		     CI_FT.REDUNDANT_SW = 'N' AND
		     CI_FT.NOT_IN_ARS_SW = 'N' AND
		     CI_FT.SA_ID = CI_SA.SA_ID AND
		     CI_SA.ACCT_ID = CI_ACCT.ACCT_ID AND
		     CI_ACCT.CUST_CL_CD = CI_CUST_CL.CUST_CL_CD AND
		     CI_CUST_CL.OPEN_ITEM_SW = 'N' AND
		     (CI_FT.ARS_DT <= @V_CUTOFF_DATE OR CI_FT.ARS_DT IS NULL)

		GROUP BY
		     CI_FT.CURRENCY_CD
		     ,CI_ACCT.CUST_CL_CD
		     ,CI_ACCT.ACCT_ID

	) FT_QUERY
/****************************************************************************
MAIN select conditions, used mainly to decipher code descriptions and to get
language related translations of, for example, the customer class.
*****************************************************************************/
	WHERE
		CI_CUST_CL_L.CUST_CL_CD = FT_QUERY.CUST_CL_CD AND
		CI_CUST_CL_L.LANGUAGE_CD = @V_LANGUAGE_CD
		AND FT_QUERY.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
		AND CI_PER_NAME.PRIM_NAME_SW = 'Y' AND
		CI_PER_NAME.PER_ID = CI_ACCT_PER.PER_ID AND
		FT_QUERY.ACCT_ID = CI_ACCT_PER.ACCT_ID AND
		CI_ACCT_PER.MAIN_CUST_SW = 'Y'

	UNION

   	SELECT  @V_RETURN_CD RETURN_CODE
	  	,CAST (NULL AS VARCHAR) MESSAGE_TEXT
		,CAST (@V_CUTOFF_DATE AS DATETIME) V_CUTOFF_DATE
          	,FT_QUERY.CURRENCY_CD
          	,FT_QUERY.CUST_CL_CD
          	,CI_CUST_CL_L.DESCR CUST_CL
          	,TOT_AR,UB_AR,PAY,BKT1,BKT2,BKT3,BKT4
		,CI_CURRENCY_CD.CUR_SYMBOL
		,CI_CURRENCY_CD.DECIMAL_POSITIONS
		,CI_CURRENCY_CD.SCALE_POSITIONS
		,CI_CURRENCY_CD.CUR_POS_FLG
		,CI_CURRENCY_CD_L.DESCR CURR_DESC
		,FT_QUERY.ACCT_ID,ENTITY_NAME

   	FROM CI_CUST_CL_L,
   	     CI_CURRENCY_CD,
	     CI_CURRENCY_CD_L,
	     CI_ACCT_PER,CI_PER_NAME,
	     (

/* This is the Sub-Select which represents most of the logic */

		SELECT CI_FT.CURRENCY_CD,
          	       CI_ACCT.CUST_CL_CD,
		       CI_ACCT.ACCT_ID,

/* Bucket 1 */

		       SUM(CASE SIGN(CI_FT.CUR_AMT)
				WHEN -1 THEN 0
				WHEN  0 THEN 0
			        ELSE CASE SIGN(0-(DATEDIFF(dd, ISNULL(ARS_DT, CAST('1/1/9999' AS DATETIME)), @V_CUTOFF_DATE)))*
      					  SIGN(DATEDIFF(dd, ISNULL(ARS_DT, CAST('1/1/9999' AS DATETIME)), @V_CUTOFF_DATE)- @V_B1_LIMIT)
				     	  WHEN -1 THEN 0
					  ELSE CUR_AMT
				     END
 			    END
                           ) BKT1,

/* Bucket 2 */
		       SUM(CASE SIGN(CI_FT.CUR_AMT)
				WHEN -1 THEN 0
				WHEN  0 THEN 0
				ELSE CASE SIGN(@V_B1_LIMIT-(DATEDIFF(dd, ISNULL(ARS_DT, CAST('1/1/9999' AS DATETIME)), @V_CUTOFF_DATE)))*
                   			  SIGN(DATEDIFF(dd, ISNULL(ARS_DT, CAST('1/1/9999' AS DATETIME)), @V_CUTOFF_DATE)- @V_B2_LIMIT)
				     	WHEN -1 THEN 0
				     	WHEN  0 THEN CASE @V_B1_LIMIT-(DATEDIFF(dd, @V_CUTOFF_DATE, ISNULL(ARS_DT, CAST('1/1/9999' AS DATETIME))))
						     	WHEN 0 THEN 0
						  	ELSE CUR_AMT
						     END
					ELSE CUR_AMT
 				     END
			   END
                          ) BKT2,

/* Bucket 3 */
			SUM(CASE SIGN(CI_FT.CUR_AMT)
			    	WHEN -1 THEN 0
				WHEN  0 THEN 0
				ELSE CASE SIGN(0-(DATEDIFF(dd, ISNULL(ARS_DT, CAST('1/1/9999' AS DATETIME)), @V_CUTOFF_DATE)))*
      					  SIGN((DATEDIFF(dd, ISNULL(ARS_DT, CAST('1/1/9999' AS DATETIME)), @V_CUTOFF_DATE))- @V_B1_LIMIT)
					WHEN -1 THEN 0
					WHEN  0 THEN CASE @V_B2_LIMIT-DATEDIFF(dd, ISNULL(ARS_DT, CAST('1/1/9999' AS DATETIME)), @V_CUTOFF_DATE)
							WHEN 0 THEN 0
							ELSE CUR_AMT
						     END
					ELSE CUR_AMT
				     END
			    END
			   ) BKT3,

/* Bucket 4 */
			SUM(CASE SIGN(CI_FT.CUR_AMT)
				WHEN -1 THEN 0
				WHEN  0 THEN 0
				ELSE CASE SIGN(@V_B3_LIMIT-DATEDIFF(dd, ISNULL(ARS_DT, CAST('1/1/9999' AS DATETIME)), @V_CUTOFF_DATE))*(-1)
					WHEN -1 THEN 0
					WHEN  0 THEN 0
					ELSE CUR_AMT
				     END
				END
			    ) BKT4,

/* In this selective summary we include all credits  */
			SUM(CASE SIGN(CI_FT.CUR_AMT)
				 WHEN  1 THEN 0
				 WHEN  0 THEN 0
				 WHEN -1 THEN CUR_AMT
			         ELSE  0
			     END
			    ) PAY,

/* In this selective summary we include all unbilled receivables */
			SUM(CASE SIGN(CI_FT.CUR_AMT)
				WHEN -1 THEN 0
				WHEN  0 THEN 0
				ELSE
					CASE ISNULL(ARS_DT, CAST('1/1/9999' AS DATETIME))
						WHEN CAST('1/1/9999' AS DATETIME) THEN CUR_AMT
						ELSE 0
					END
			    END
			) UB_AR,

			SUM(CUR_AMT) TOT_AR

		FROM CI_ACCT, CI_SA,CI_FT,CI_CUST_CL
		WHERE
		     CI_FT.FREEZE_SW = 'Y' AND
		     CI_FT.REDUNDANT_SW = 'N' AND
		     CI_FT.SA_ID = CI_SA.SA_ID AND
		     CI_SA.ACCT_ID = CI_ACCT.ACCT_ID AND
		     CI_ACCT.CUST_CL_CD = CI_CUST_CL.CUST_CL_CD AND
		     CI_CUST_CL.OPEN_ITEM_SW = 'Y' AND
		     (CI_FT.ARS_DT <= @V_CUTOFF_DATE OR CI_FT.ARS_DT IS NULL)
		     AND   NOT EXISTS (
				SELECT 1
				FROM   CI_MATCH_EVT ME
				WHERE  ME.MATCH_EVT_ID = CI_FT.MATCH_EVT_ID
				       AND MEVT_STATUS_FLG = 'B')
		GROUP BY
		     CI_FT.CURRENCY_CD
		     ,CI_ACCT.CUST_CL_CD
		     ,CI_ACCT.ACCT_ID
	) FT_QUERY

/****************************************************************************
MAIN select conditions, used mainly to decipher code descriptions and to get
language related translations of, for example, the customer class.
*****************************************************************************/
	WHERE
		CI_CUST_CL_L.CUST_CL_CD = FT_QUERY.CUST_CL_CD AND
		CI_CUST_CL_L.LANGUAGE_CD = @V_LANGUAGE_CD
		AND FT_QUERY.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
		AND CI_PER_NAME.PRIM_NAME_SW = 'Y' AND
		CI_PER_NAME.PER_ID = CI_ACCT_PER.PER_ID AND
		FT_QUERY.ACCT_ID = CI_ACCT_PER.ACCT_ID AND
		CI_ACCT_PER.MAIN_CUST_SW = 'Y'
	ORDER BY 3,4,19;
   END
   ELSE
   BEGIN
	SELECT MESSAGE_NBR RETURN_CODE
	       ,MESSAGE_TEXT
	       ,CAST (NULL AS DATETIME) V_CUTOFF_DATE
               ,CAST (NULL AS CHAR) CURRENCY_CD
               ,CAST (NULL AS CHAR) CUST_CL_CD
               ,CAST (NULL AS VARCHAR) CUST_CL
               ,CAST (NULL AS NUMERIC) TOT_AR
               ,CAST (NULL AS NUMERIC) UB_AR
               ,CAST (NULL AS NUMERIC) PAY
               ,CAST (NULL AS INTEGER) BKT1
               ,CAST (NULL AS INTEGER) BKT2
	       ,CAST (NULL AS INTEGER) BKT3
	       ,CAST (NULL AS INTEGER) BKT4
	       ,CAST (NULL AS CHAR) CUR_SYMBOL
	       ,CAST (NULL AS NUMERIC) DECIMAL_POSITIONS
	       ,CAST (NULL AS NUMERIC) SCALE_POSITIONS
	       ,CAST (NULL AS CHAR) CUR_POS_FLG
	       ,CAST (NULL AS VARCHAR) CURR_DESC
	       ,CAST (NULL AS CHAR) ACCT_ID
               ,CAST (NULL AS VARCHAR) ENTITY_NAME
      	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

