eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.FII_AR_COLL_EFF_IND_PKG SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 86


    select sequence into g_current_sequence from fii_time_week where fii_ar_util_pkg.g_as_of_date between start_date and end_date;

Line: 89


    select sequence into g_current_sequence from FII_TIME_ENT_QTR where fii_ar_util_pkg.g_as_of_date between start_date and end_date;

Line: 92


   select sequence into g_current_sequence from FII_TIME_ENT_YEAR where fii_ar_util_pkg.g_as_of_date between start_date and end_date;

Line: 95


   select sequence into g_current_sequence from FII_TIME_ENT_PERIOD where fii_ar_util_pkg.g_as_of_date between start_date and end_date;

Line: 131


  l_select_sql1			varchar2(500);

Line: 132


  l_select_sql2			varchar2(500);

Line: 133


  l_select_sql3			varchar2(500);

Line: 136


  l_inner_cst_select		varchar2(70);

Line: 138


  l_self_drill_select		varchar2(200);

Line: 144


  l_customer_select		varchar2(500);

Line: 210


  l_self_drill_select := '''''';

Line: 225


	l_select_sql1 := ''''||l_beg_open_rec_drill_2||'''';

Line: 227


	l_select_sql2 := ''''||l_end_open_rec_drill_2||'''';

Line: 229


	l_select_sql3 := ''''||l_end_curr_rec_drill_2||'''';

Line: 232


	l_select_sql1 := ''''||l_beg_open_rec_drill_l||'''';

Line: 234


	l_select_sql2 := ''''||l_end_open_rec_drill_l||'''';

Line: 236


	l_select_sql3 := ''''||l_end_curr_rec_drill_l||'''';

Line: 240


	l_customer_select := ' t.party_id   party_id, ';

Line: 245


		l_select_sql1 := 'DECODE(inline_view.is_self_flag, ''Y'' ,'''||l_beg_open_rec_drill_l||''', DECODE(is_leaf_flag,''Y'', '''||l_beg_open_rec_drill_l||''', '''||l_beg_open_rec_drill||'''))';

Line: 247


		l_select_sql2 := 'DECODE(inline_view.is_self_flag, ''Y'' ,'''||l_end_open_rec_drill_l||''', DECODE(is_leaf_flag,''Y'', '''||l_end_open_rec_drill_l||''', '''||l_end_open_rec_drill||'''))';

Line: 249


		l_select_sql3 := 'DECODE(inline_view.is_self_flag, ''Y'' ,'''||l_end_curr_rec_drill_l||''', DECODE(is_leaf_flag,''Y'', '''||l_end_curr_rec_drill_l||''', '''||l_end_curr_rec_drill||'''))';

Line: 256


		l_self_drill_select := 'DECODE(inline_view.is_self_flag, ''Y'', '''', DECODE(inline_view.is_leaf_flag, ''Y'','''','''||l_self_drill||'''))';

Line: 258


		--Select and Groupp by Clauses
		l_inner_cst_select := 'is_self_flag is_self_flag, is_leaf_flag is_leaf_flag,';

Line: 264


		l_select_sql1 := ''''||l_beg_open_rec_drill_l||'''';

Line: 266


		l_select_sql2 := ''''||l_end_open_rec_drill_l||'''';

Line: 268


		l_select_sql3 := ''''||l_end_curr_rec_drill_l||'''';

Line: 281


  sqlstmt := 'SELECT
	inline_view.viewby          VIEWBY,
	inline_view.viewby_id	    VIEWBYID,
	((DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC) +
	(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||')
	- DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC))/
        NULLIF((DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC)
	+ (DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||')
	- DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC)),0)) * 100   FII_AR_COLL_EFF_INDEX,
(((DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC) +
	(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||')
	-DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC))/
        NULLIF((NULLIF(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0,FII_AR_BEG_OPEN_REC), 0)
	+(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||')
	-DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC)),0))* 100) -
        (((DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)
	+(DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||')
	- DECODE(FII_AR_PRIOR_END_OPEN_REC, NULL, 0, FII_AR_PRIOR_END_OPEN_REC))/
        NULLIF((DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)
	+(DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||')
	- DECODE(FII_AR_PRIOR_END_CURR_REC, NULL, 0, FII_AR_PRIOR_END_CURR_REC)),0))*100) FII_AR_COLL_EFF_CHANGE,
  FII_AR_BEG_OPEN_REC,
	FII_AR_BILLED_AMOUNT,
	FII_AR_END_OPEN_REC,
	FII_AR_BEG_CURR_REC,
	FII_AR_PAST_DUE_REC,
	FII_AR_END_CURR_REC,
((SUM(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC)) OVER() +
	SUM(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||') OVER()
	-SUM(DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC)) OVER())/
        NULLIF((SUM(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC))OVER()
	+SUM(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||') OVER()
	-SUM(DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC)) OVER()),0)) *100  FII_AR_GT_COLL_EFF_IND,
(((SUM(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC)) OVER()
	+SUM(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||') OVER()
	-SUM(DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC)) OVER())/
        NULLIF((SUM(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC))OVER()
        +SUM(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||') OVER()
	-SUM(DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC)) OVER()),0))* 100)  -
        (((SUM(DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)) OVER()
	+SUM(DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||') OVER()
	-SUM(DECODE(FII_AR_PRIOR_END_OPEN_REC, NULL, 0, FII_AR_PRIOR_END_OPEN_REC)) OVER())/
        NULLIF((SUM(DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)) OVER()
	+SUM(DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||') OVER()
	-SUM(DECODE(FII_AR_PRIOR_END_CURR_REC, NULL, 0, FII_AR_PRIOR_END_CURR_REC)) OVER()),0))* 100) FII_AR_GT_COLL_EFF_CHG,
 SUM(FII_AR_BEG_OPEN_REC) OVER() FII_AR_GT_BEG_OPEN_REC,
	SUM(FII_AR_BILLED_AMOUNT) OVER() FII_AR_GT_BILLED_AMT,
	SUM(FII_AR_END_OPEN_REC) OVER() FII_AR_GT_END_OPEN_REC,
	SUM(FII_AR_END_CURR_REC) OVER() FII_AR_GT_END_CURR_REC,
((DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)
	+ (DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||')
	- DECODE(FII_AR_PRIOR_END_OPEN_REC, NULL, 0, FII_AR_PRIOR_END_OPEN_REC))/
        NULLIF((DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)
        + (DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||')
	- DECODE(FII_AR_PRIOR_END_CURR_REC, NULL, 0, FII_AR_PRIOR_END_CURR_REC)),0))  * 100 FII_AR_PRIO_COLL_EFF_INDEX,
      FII_AR_END_PAST_DUE_REC,
	DECODE(FII_AR_BEG_OPEN_REC,0,'''',DECODE(NVL(FII_AR_BEG_OPEN_REC,-999999),-999999,'''',
	'||l_select_sql1||')) FII_AR_BEG_OPEN_REC_DRILL,
DECODE(FII_AR_END_OPEN_REC,0,'''',DECODE(NVL(FII_AR_END_OPEN_REC,-999999),-999999,'''',
	'||l_select_sql2||')) FII_AR_END_OPEN_REC_DRILL,
DECODE(FII_AR_END_CURR_REC,0,'''',DECODE(NVL(FII_AR_END_CURR_REC,-999999),-999999,'''',
	'||l_select_sql3||')) FII_AR_END_CURR_REC_DRILL,
'||l_self_drill_select ||' FII_AR_CUST_SELF_DRILL  ,
	((SUM(DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)) OVER() +
	SUM(DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||') OVER()
	-SUM(DECODE(FII_AR_PRIOR_END_OPEN_REC, NULL, 0, FII_AR_PRIOR_END_OPEN_REC)) OVER())/
        NULLIF((SUM(DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC))OVER()
	+SUM(DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||') OVER()
	-SUM(DECODE(FII_AR_PRIOR_END_CURR_REC, NULL, 0, FII_AR_PRIOR_END_CURR_REC)) OVER()),0)) * 100 FII_AR_GT_PRIO_COLL_EFF_INDEX
  FROM (
  SELECT	 /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/  VIEWBY,
		  viewby_code	viewby_id,
'||l_inner_cst_select||l_customer_select||'
		SUM(DECODE(t.report_date, :CURR_PERIOD_START ,
					(CASE	WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
						THEN total_open_amount  ELSE NULL END) ) )   FII_AR_BEG_OPEN_REC,
					SUM(DECODE(t.report_date, :ASOF_DATE,
					(CASE	WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
						THEN total_open_amount  ELSE NULL END) ) )   FII_AR_END_OPEN_REC,
				SUM(DECODE(t.report_date, :ASOF_DATE,
					(CASE	WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
						THEN current_open_amount  ELSE NULL END) ) )   FII_AR_END_CURR_REC,
			SUM(DECODE(t.report_date, :CURR_PERIOD_START,
					(CASE	WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
						THEN current_open_amount  ELSE NULL END) ) )   FII_AR_BEG_CURR_REC,
		   SUM(DECODE(t.report_date, :CURR_PERIOD_START, /*This date will be starting date of the period*/
					(CASE	WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
						THEN past_due_open_amount  ELSE NULL END) ) )   FII_AR_PAST_DUE_REC,
       SUM(DECODE(t.report_date, :ASOF_DATE, /*This date will be the as-of-date*/
					(CASE	WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
						THEN past_due_open_amount  ELSE NULL END) ) )   FII_AR_END_PAST_DUE_REC,
			 SUM(DECODE(t.report_date, :ASOF_DATE, /*This date will be the as-of-date*/
					(CASE	WHEN bitand(t.record_type_id,:BITAND) = :BITAND
						THEN billed_amount ELSE NULL END) ) )   FII_AR_BILLED_AMOUNT,
   SUM(DECODE(t.report_date, :PRIOR_PERIOD_START, /*This date will be starting date of the prior period*/
					(CASE	WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
						THEN total_open_amount ELSE NULL END) ) )   FII_AR_PRIOR_BEG_OPEN_REC,
				SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE, /*This date will be the prior as-of-date*/
					(CASE	WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
						THEN total_open_amount  ELSE NULL END) ) )   FII_AR_PRIOR_END_OPEN_REC,
		 SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE, /*This date will be the prior as-of-date*/
					(CASE	WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
						THEN current_open_amount  ELSE NULL END) ) )   FII_AR_PRIOR_END_CURR_REC,
      SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE, /*This date will be the prior as-of-date*/
					(CASE	WHEN bitand(t.record_type_id,:BITAND) = :BITAND
						THEN billed_amount  ELSE NULL END) ) )   FII_AR_PRIOR_BILLED_AMOUNT
  FROM FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix
             ||'  f,(	SELECT	/*+ no_merge '||l_gt_hint|| ' */ *
				FROM 	fii_time_structures cal,
					'||fii_ar_util_pkg.get_from_statement||' gt
		     	        WHERE	report_date IN ( :CURR_PERIOD_START ,
							 :ASOF_DATE,
							 :PREVIOUS_ASOF_DATE,
							 :PRIOR_PERIOD_START
						       )
					AND (	bitand(cal.record_type_id, :BITAND) = :BITAND OR
						bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
					    )
				AND '||fii_ar_util_pkg.get_where_statement||'
		     ) t
  WHERE  f.time_id = t.time_id
  AND f.period_type_id = t.period_type_id	'||l_child_party_where||'
  AND f.org_id = t.org_id
  AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_party_where ||' ' || l_collector_where ||' '|| l_cust_acct_where ||'
   GROUP BY  viewby_code, VIEWBY '||l_inner_cst_group||') inline_view
   '||l_order_by;

Line: 437


  l_select_sql1			varchar2(500);

Line: 438


  l_select_sql2			varchar2(500);

Line: 441


  l_inner_cst_select		varchar2(70);

Line: 443


  l_self_drill_select		varchar2(200);

Line: 449


  l_customer_select		varchar2(500);

Line: 488


  l_self_drill_select := '''''';

Line: 513


	l_select_sql1 := ''''||l_rec_amt_drill_2||'''';

Line: 514


	l_select_sql2 := ''''||l_wadp_drill_1||'''';

Line: 518


	l_select_sql1 := ''''||l_rec_amt_drill_1||'''';

Line: 519


	l_select_sql2 := '''''';

Line: 522


	l_customer_select := ' t.party_id   party_id, ';

Line: 527


		l_select_sql1 := 'DECODE(inline_view.is_self_flag, ''Y'' ,'''||l_rec_amt_drill_1||''', DECODE(is_leaf_flag,''Y'', '''||l_rec_amt_drill_1||''', '''||l_rec_amt_drill||'''))';

Line: 528


		l_select_sql2 := 'DECODE(inline_view.is_self_flag, ''Y'' ,'''', DECODE(is_leaf_flag,''Y'', '''', '''||l_wadp_drill||'''))';

Line: 533


		l_self_drill_select := 'DECODE(inline_view.is_self_flag, ''Y'' , '''', DECODE(inline_view.is_leaf_flag, ''Y'','''','''||l_self_drill||'''))';

Line: 535


		--Select and group by clauses for hierarchical Customer dimension
		l_inner_cst_select := 'is_self_flag is_self_flag, is_leaf_flag is_leaf_flag,';

Line: 540


		l_select_sql1 := ''''||l_rec_amt_drill_1||'''';

Line: 541


		l_select_sql2 := '''''';

Line: 559


 sqlstmt := 'SELECT
        inline_view.viewby          VIEWBY,
	inline_view .viewby_id	    VIEWBYID,
	((DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC)
	+ (DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||')
	- DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC))/
        NULLIF((DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC)
	+ (DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||')
	- DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC)),0))  * 100 FII_AR_COLL_EFF_INDEX,
(((DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC)
	+(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||')
	-DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC))/
        NULLIF((DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC)
	+(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||')
	-DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC)),0)) * 100) -
        (((DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)
	+(DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||')
	-DECODE(FII_AR_PRIOR_END_OPEN_REC, NULL, 0, FII_AR_PRIOR_END_OPEN_REC))/
        NULLIF((DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)
	+(DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||')
	-DECODE(FII_AR_PRIOR_END_CURR_REC, NULL, 0, FII_AR_PRIOR_END_CURR_REC)),0)) * 100)  FII_AR_COLL_EFF_CHANGE,
(FII_AR_WTD_DAYS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0))	FII_AR_DAYS_PAID,
((FII_AR_WTD_DAYS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) - (FII_AR_PRIOR_WTD_DP_NUM/NULLIF(FII_AR_PRIOR_APPLIED_AMOUNT,0)))FII_AR_CHANGE_DP,
((FII_AR_WTD_DAYS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) - (FII_AR_WTD_TERMS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)))	FII_AR_DAYS_DELQ,
(((FII_AR_WTD_DAYS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) - (FII_AR_WTD_TERMS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)))
	-
	((FII_AR_PRIOR_WTD_DP_NUM/NULLIF(FII_AR_PRIOR_APPLIED_AMOUNT,0)) - (FII_AR_PRIOR_WTD_TP_NUM/NULLIF(FII_AR_PRIOR_APPLIED_AMOUNT,0))) )	FII_AR_CHANGE_DD,
(FII_AR_WTD_TERMS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0))	FII_AR_TERMS_PAID,
((FII_AR_WTD_TERMS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) - (FII_AR_PRIOR_WTD_TP_NUM/NULLIF(FII_AR_PRIOR_APPLIED_AMOUNT,0)))	FII_AR_CHANGE_TP,
FII_AR_BILLED_AMOUNT	FII_AR_BILLED_AMOUNT,
FII_AR_REC_AMT	FII_AR_REC_AMT,
((sum(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC)) over()
	+sum(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||') over()
	-sum(DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC)) over())/
        NULLIF((sum(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC))over()
	+sum(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||') over()
	-sum(DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC)) over()),0)) * 100 FII_AR_GT_COLL_EFF_IND,
(((sum(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC)) over()
	+sum(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||') over()
	-sum(DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC)) over())/
        NULLIF((sum(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC))over()
	+sum(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT) '||g_scale_sign||' '||g_scaling_factor||') over()
	-sum(DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC)) over()),0)) * 100 ) -
        (((sum(DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)) over()
	+sum(DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||') over()
	-sum(DECODE(FII_AR_PRIOR_END_OPEN_REC, NULL, 0, FII_AR_PRIOR_END_OPEN_REC)) over())/
        NULLIF((sum(DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)) over()
	+sum(DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||') over()
	-sum(DECODE(FII_AR_PRIOR_END_CURR_REC, NULL, 0, FII_AR_PRIOR_END_CURR_REC)) over()),0)) * 100 ) FII_AR_GT_COLL_EFF_CHG,
(SUM(FII_AR_WTD_DAYS_PAID_NUM) OVER()/NULLIF(SUM(FII_AR_APPLIED_AMOUNT) OVER(),0))	FII_AR_GT_DP,
((SUM(FII_AR_WTD_DAYS_PAID_NUM) OVER()/NULLIF(SUM(FII_AR_APPLIED_AMOUNT) OVER(),0)) - (SUM(FII_AR_PRIOR_WTD_DP_NUM) OVER()/NULLIF(SUM(FII_AR_PRIOR_APPLIED_AMOUNT) OVER(),0)))	FII_AR_GT_DP_CHG,
((SUM(FII_AR_WTD_DAYS_PAID_NUM) OVER()/NULLIF(SUM(FII_AR_APPLIED_AMOUNT) OVER(),0)) - (SUM(FII_AR_WTD_TERMS_PAID_NUM) OVER()/NULLIF(SUM(FII_AR_APPLIED_AMOUNT) OVER(),0)))	FII_AR_GT_DD,
(((SUM(FII_AR_WTD_DAYS_PAID_NUM) OVER()/NULLIF(SUM(FII_AR_APPLIED_AMOUNT) OVER(),0)) - (SUM(FII_AR_WTD_TERMS_PAID_NUM) OVER()/NULLIF(SUM(FII_AR_APPLIED_AMOUNT) OVER(),0)))
	-
	((SUM(FII_AR_PRIOR_WTD_DP_NUM) OVER() /NULLIF(SUM(FII_AR_PRIOR_APPLIED_AMOUNT) OVER(),0)) - (SUM(FII_AR_PRIOR_WTD_TP_NUM) OVER()/NULLIF(SUM(FII_AR_PRIOR_APPLIED_AMOUNT) OVER(),0))) )        FII_AR_GT_DD_CHG,
	(SUM(FII_AR_WTD_TERMS_PAID_NUM) OVER()/NULLIF(SUM(FII_AR_APPLIED_AMOUNT) OVER(),0))	FII_AR_GT_TP,
((SUM(FII_AR_WTD_TERMS_PAID_NUM) OVER()/NULLIF(SUM(FII_AR_APPLIED_AMOUNT) OVER(),0)) - (SUM(FII_AR_PRIOR_WTD_TP_NUM) OVER()/NULLIF(SUM(FII_AR_PRIOR_APPLIED_AMOUNT) OVER(),0)))	FII_AR_GT_TP_CHG,
SUM(FII_AR_BILLED_AMOUNT) OVER()	FII_AR_GT_BILLED_AMT,
SUM(FII_AR_REC_AMT) OVER()	FII_AR_GT_REC_AMT,
((DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0, FII_AR_PRIOR_BEG_OPEN_REC)
	+ (DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||')
	- DECODE(FII_AR_PRIOR_END_OPEN_REC, NULL, 0, FII_AR_PRIOR_END_OPEN_REC))/
        NULLIF((DECODE(FII_AR_PRIOR_BEG_OPEN_REC, NULL, 0,FII_AR_PRIOR_BEG_OPEN_REC)
	+ (DECODE(FII_AR_PRIOR_BILLED_AMOUNT, NULL, 0, FII_AR_PRIOR_BILLED_AMOUNT)'||g_scale_sign||' '||g_scaling_factor||')
	- DECODE(FII_AR_PRIOR_END_CURR_REC, NULL, 0, FII_AR_PRIOR_END_CURR_REC)),0)) * 100 FII_AR_PRIO_COLL_EFF_INDEX_G,
((FII_AR_PRIOR_WTD_DP_NUM/NULLIF(FII_AR_PRIOR_APPLIED_AMOUNT,0)) - (FII_AR_PRIOR_WTD_TP_NUM/NULLIF(FII_AR_PRIOR_APPLIED_AMOUNT,0)))	FII_AR_AVG_DD_PRIOR_G,
	'||l_self_drill_select ||'  FII_AR_VIEW_BY_DRILL,
DECODE(FII_AR_REC_AMT,0,'''',DECODE(NVL(FII_AR_REC_AMT,-999999),-999999,'''',
	'||l_select_sql1||')) FII_AR_REC_AMT_DRILL,
DECODE((FII_AR_WTD_DAYS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)),0,'''',DECODE(NVL((FII_AR_WTD_DAYS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)),-999999),-999999,'''',
	'||l_select_sql2||')) FII_AR_WADP_DRILL,
	FII_AR_PRIOR_REC_AMT		 FII_AR_PRIOR_REC_AMT,
	SUM(FII_AR_PRIOR_REC_AMT) OVER() FII_AR_GT_PRIOR_REC_AMT,
	FII_AR_PRIOR_WTD_DP_NUM/NULLIF(FII_AR_PRIOR_APPLIED_AMOUNT,0)	FII_AR_PRIOR_WTD_AVG_DP,
        ((FII_AR_PRIOR_WTD_DP_NUM/NULLIF(FII_AR_PRIOR_APPLIED_AMOUNT,0)) - (FII_AR_PRIOR_WTD_TP_NUM/NULLIF(FII_AR_PRIOR_APPLIED_AMOUNT,0)))	FII_AR_PRIOR_WTD_AVG_DD,
          NVL((SUM(FII_AR_PRIOR_WTD_DP_NUM) OVER()/NULLIF(SUM(FII_AR_PRIOR_APPLIED_AMOUNT) OVER(),0)),0)	FII_AR_GT_PRIOR_WTD_AVG_DP,
NVL( ((SUM(FII_AR_PRIOR_WTD_DP_NUM) OVER()/NULLIF(SUM(FII_AR_PRIOR_APPLIED_AMOUNT) OVER(),0))
  - (SUM(FII_AR_PRIOR_WTD_TP_NUM) OVER()/NULLIF(SUM(FII_AR_PRIOR_APPLIED_AMOUNT) OVER(),0))),0)	FII_AR_GT_PRIOR_WTD_AVG_DD
	FROM (
  SELECT	  /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/ VIEWBY,
		  viewby_code	viewby_id,
'|| l_inner_cst_select || l_customer_select ||'
		SUM(DECODE(t.report_date, :CURR_PERIOD_START ,
					(CASE	WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
						THEN total_open_amount  ELSE NULL END) ) )   FII_AR_BEG_OPEN_REC,
				 SUM(DECODE(t.report_date, :ASOF_DATE,
					(CASE	WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
						THEN total_open_amount  ELSE NULL END) ) )   FII_AR_END_OPEN_REC,
			SUM(DECODE(t.report_date, :ASOF_DATE,
					(CASE	WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
						THEN current_open_amount  ELSE NULL END) ) )   FII_AR_END_CURR_REC,
			SUM(DECODE(t.report_date, :ASOF_DATE,
					(CASE	WHEN bitand(t.record_type_id,:BITAND) = :BITAND
						THEN billed_amount ELSE NULL END) ) )   FII_AR_BILLED_AMOUNT,
		   SUM(DECODE(t.report_date, :ASOF_DATE,
					(CASE	WHEN bitand(t.record_type_id,:BITAND) = :BITAND
						THEN app_amount ELSE NULL END) ) )   FII_AR_APPLIED_AMOUNT,
SUM(DECODE(t.report_date, :ASOF_DATE,
					(CASE	WHEN bitand(t.record_type_id,:BITAND) = :BITAND
						THEN wtd_days_paid_num ELSE NULL END) ) )   FII_AR_WTD_DAYS_PAID_NUM,
SUM(DECODE(t.report_date, :ASOF_DATE,
					(CASE	WHEN bitand(t.record_type_id,:BITAND) = :BITAND
						THEN wtd_terms_paid_num ELSE NULL END) ) )   FII_AR_WTD_TERMS_PAID_NUM,
    SUM(DECODE(t.report_date, :ASOF_DATE,
					(CASE WHEN (f.header_filter_date <= :ASOF_DATE) and
					(f.header_filter_date >= :CURR_PERIOD_START) and (bitand(t.record_type_id,:BITAND) = :BITAND)
					 THEN total_receipt_amount ELSE NULL END) ) )   FII_AR_REC_AMT,
	SUM(DECODE(t.report_date, :PRIOR_PERIOD_START,
					(CASE	WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
						THEN total_open_amount ELSE NULL END) ) )   FII_AR_PRIOR_BEG_OPEN_REC,
				SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE,
					(CASE	WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
						THEN total_open_amount  ELSE NULL END) ) )   FII_AR_PRIOR_END_OPEN_REC,
		SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE,
					(CASE	WHEN bitand(t.record_type_id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE
						THEN current_open_amount  ELSE NULL END) ) )   FII_AR_PRIOR_END_CURR_REC,
     SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE,
					(CASE	WHEN bitand(t.record_type_id,:BITAND) = :BITAND
						THEN billed_amount  ELSE NULL END) ) )   FII_AR_PRIOR_BILLED_AMOUNT,
SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE,
					(CASE	WHEN bitand(t.record_type_id,:BITAND) = :BITAND
						THEN app_amount ELSE NULL END) ) )   FII_AR_PRIOR_APPLIED_AMOUNT,
		SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE,
					(CASE	WHEN bitand(t.record_type_id,:BITAND) = :BITAND
						THEN wtd_days_paid_num ELSE NULL END) ) )   FII_AR_PRIOR_WTD_DP_NUM,
SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE,
					(CASE	WHEN bitand(t.record_type_id,:BITAND) = :BITAND
						THEN wtd_terms_paid_num ELSE NULL END) ) )   FII_AR_PRIOR_WTD_TP_NUM,
SUM(DECODE(t.report_date, :PREVIOUS_ASOF_DATE,
					(CASE	WHEN (f.header_filter_date <= :PREVIOUS_ASOF_DATE) and
					(f.header_filter_date >= :PRIOR_PERIOD_START) and (bitand(t.record_type_id,:BITAND) = :BITAND)
						THEN total_receipt_amount ELSE NULL END) ) )   FII_AR_PRIOR_REC_AMT
	FROM FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix
             ||'  f,(	SELECT	/*+ no_merge '||l_gt_hint|| ' */  *
				FROM 	fii_time_structures cal,
					'||fii_ar_util_pkg.get_from_statement||' gt
		     	        WHERE	report_date in ( :CURR_PERIOD_START ,
							 :ASOF_DATE,
							 :PREVIOUS_ASOF_DATE,
							 :PRIOR_PERIOD_START
						       )
					AND (	bitand(cal.record_type_id, :BITAND) = :BITAND OR
						bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
					    )
				AND '||fii_ar_util_pkg.get_where_statement||'
		     ) t
  WHERE  f.time_id = t.time_id
  AND f.period_type_id = t.period_type_id	'||l_child_party_where||'
  AND f.org_id = t.org_id
  AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_party_where ||' ' || l_collector_where ||' '|| l_cust_acct_where ||' '|| l_industry_where ||'
  GROUP BY  viewby_code, VIEWBY '||l_inner_cst_group||' ) inline_view
   '||l_order_by;

Line: 722


  l_select_clause		VARCHAR2(30000); --Intermediate sql statement for the upper select part of the final sql

Line: 736


  l_prior_column		VARCHAR2(5000); --Prior columns select clause

Line: 737


  l_current_prior_column	varchar2(5000); --Prior columns select clause for current period

Line: 821


        l_prior_column:= ' SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'' ,
	    CASE
		WHEN t.report_date < :SD_SDATE
		AND bitand(t.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
		THEN f.total_open_amount
		ELSE null
	    END )) FII_PRIOR_AR_BEG_OPEN_REC,
	    SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'', null,
	    CASE
		WHEN t.report_date < :SD_SDATE
		AND bitand(t.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
		THEN f.total_open_amount
		ELSE null
	    END     )) FII_PRIOR_AR_END_OPEN_REC,
	    SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'' , null,
	    CASE
		WHEN t.report_date < :SD_SDATE
		AND bitand(t.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
		THEN f.current_open_amount
		ELSE null
	    END    )) FII_PRIOR_AR_END_CURR_REC,
	    SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'', null,
            CASE
		WHEN t.report_date < :SD_SDATE
		AND bitand(t.record_type_id, :BITAND) = :BITAND
		THEN f.billed_amount
            ELSE NULL
            END )) FII_PRIOR_AR_BILLED_AMOUNT, ';

Line: 896


    /*Upper select clause. This is common for all period types*/
    -- In this case Union All is not required which means all periods are fully exhausted and
    -- hence the scaling factor are constant for all

   l_select_clause := 'SELECT
     cy_per.name FII_AR_COLL_EFF_VIEW_BY,
     FII_AR_COLL_EFF_INDEX * 100 FII_AR_COLL_EFF_INDEX,
     FII_AR_PRIO_COLL_EFF_INDEX_G * 100 FII_AR_PRIO_COLL_EFF_INDEX_G,
     FII_AR_WTD_AVG_DP,
     (FII_AR_WTD_AVG_DP - FII_AR_WTD_AVG_TP) FII_AR_DAYS_DELQ,
     FII_AR_WTD_AVG_TP,
     FII_AR_REC_AMT,
DECODE(FII_AR_WTD_AVG_DP,0,'''',DECODE(NVL(FII_AR_WTD_AVG_DP, -99999),-99999, '''',DECODE(SIGN(cy_per.end_date - :ASOF_DATE),1,
	''pFunctionName=FII_AR_COLL_EFFECTIVENESS&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'',
	''AS_OF_DATE=''|| to_char(cy_per.end_date,''DD/MM/YYYY'')||''&pFunctionName=FII_AR_COLL_EFFECTIVENESS&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'' )))
	FII_AR_DAYS_PAID_DRILL,
 DECODE(FII_AR_COLL_EFF_INDEX,0,'''',DECODE(NVL(FII_AR_COLL_EFF_INDEX, -99999),-99999, '''',DECODE(SIGN(cy_per.end_date - :ASOF_DATE),1,
	''pFunctionName=FII_AR_COLL_EFF_INDEX&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'',
	''AS_OF_DATE=''|| to_char(cy_per.end_date,''DD/MM/YYYY'')||''&pFunctionName=FII_AR_COLL_EFF_INDEX&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'' )))
	FII_AR_CEI_DRILL,
 DECODE(FII_AR_REC_AMT,0,'''',DECODE(NVL(FII_AR_REC_AMT, -99999),-99999, '''', DECODE(SIGN(cy_per.end_date - :ASOF_DATE),1,
	''pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'',
	''AS_OF_DATE=''|| to_char(cy_per.end_date,''DD/MM/YYYY'')||''&pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'' )))
	FII_AR_RCT_AMT_DRILL
 FROM '||l_time_table||' cy_per, (
    SELECT
        sequence sequence,
(SUM(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC))
    +(SUM(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT))'||g_scale_sign_cons||' '||g_scaling_factor_cons||')
    - SUM(DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC)))
    /NULLIF((SUM(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC))
    +(SUM(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT))'||g_scale_sign_cons||' '||g_scaling_factor_cons||')
    -SUM(DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC))),0) FII_AR_COLL_EFF_INDEX,
 SUM(FII_AR_WTD_DAYS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) FII_AR_WTD_AVG_DP,
    SUM(FII_AR_AVG_DD_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) FII_AR_DAYS_DELQ,
    SUM(FII_AR_WTD_TERMS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) FII_AR_WTD_AVG_TP,
    SUM(FII_AR_REC_AMT) FII_AR_REC_AMT,
 (SUM(DECODE(FII_PRIOR_AR_BEG_OPEN_REC, NULL, 0, FII_PRIOR_AR_BEG_OPEN_REC))
    +(SUM(DECODE(FII_PRIOR_AR_BILLED_AMOUNT, NULL, 0, FII_PRIOR_AR_BILLED_AMOUNT))'||g_scale_sign_cons||' '||g_scaling_factor_cons||')
    -SUM(DECODE(FII_PRIOR_AR_END_OPEN_REC, NULL, 0, FII_PRIOR_AR_END_OPEN_REC)))
    /NULLIF((SUM(DECODE(FII_PRIOR_AR_BEG_OPEN_REC, NULL, 0, FII_PRIOR_AR_BEG_OPEN_REC))
    +(SUM(DECODE(FII_PRIOR_AR_BILLED_AMOUNT, NULL, 0, FII_PRIOR_AR_BILLED_AMOUNT))'||g_scale_sign_cons||' '||g_scaling_factor_cons||')
    - SUM(DECODE(FII_PRIOR_AR_END_CURR_REC, NULL, 0, FII_PRIOR_AR_END_CURR_REC))),0) FII_AR_PRIO_COLL_EFF_INDEX_G
  from (
     SELECT per.name,per.sequence,
   sum(FII_AR_REC_AMT) FII_AR_REC_AMT,
   sum(FII_AR_BEG_OPEN_REC) FII_AR_BEG_OPEN_REC,
   sum(FII_AR_END_OPEN_REC) FII_AR_END_OPEN_REC,
   sum(FII_AR_END_CURR_REC) FII_AR_END_CURR_REC,
   sum(FII_PRIOR_AR_BEG_OPEN_REC) FII_PRIOR_AR_BEG_OPEN_REC,
   sum(FII_PRIOR_AR_END_OPEN_REC)FII_PRIOR_AR_END_OPEN_REC,
   sum(FII_PRIOR_AR_END_CURR_REC) FII_PRIOR_AR_END_CURR_REC,
   sum(FII_PRIOR_AR_BILLED_AMOUNT) FII_PRIOR_AR_BILLED_AMOUNT,
   sum(FII_AR_BILLED_AMOUNT) FII_AR_BILLED_AMOUNT,
   sum(FII_AR_APPLIED_AMOUNT) FII_AR_APPLIED_AMOUNT,
   sum(FII_AR_WTD_DAYS_PAID_NUM) FII_AR_WTD_DAYS_PAID_NUM,
   sum(FII_AR_WTD_TERMS_PAID_NUM) FII_AR_WTD_TERMS_PAID_NUM,
   sum(FII_AR_AVG_DD_NUM) FII_AR_AVG_DD_NUM FROM '||l_time_table||' per,(';

Line: 958


    SELECT /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
        per.name,
        per.sequence sequence,
        SUM(DECODE(bitand(t.record_type_id,:BITAND), :BITAND,
        CASE
            WHEN (t.report_date = :ASOF_DATE)
	    AND (f.header_filter_date <= :ASOF_DATE )
 	    AND (f.header_filter_date >= :CURR_PERIOD_START)
            THEN f.total_receipt_amount
            ELSE null
        END
        )) FII_AR_REC_AMT,
        SUM(DECODE(bitand(t.record_type_id,:BITAND_INC_TODATE), :BITAND_INC_TODATE,
        CASE
            WHEN t.report_date = :CURR_PERIOD_START
            THEN f.total_open_amount
            ELSE null
        END
        )) FII_AR_BEG_OPEN_REC,
        SUM(DECODE(bitand(t.record_type_id,:BITAND_INC_TODATE), :BITAND_INC_TODATE,
        CASE
            WHEN t.report_date = :ASOF_DATE
            THEN f.total_open_amount
            ELSE null
        END
        )) FII_AR_END_OPEN_REC,
        SUM(DECODE(bitand(t.record_type_id,:BITAND_INC_TODATE), :BITAND_INC_TODATE,
        CASE
            WHEN t.report_date = :ASOF_DATE
            THEN f.current_open_amount
            ELSE null
        END
        )) FII_AR_END_CURR_REC,
        '||l_current_prior_column||'
        SUM(DECODE(bitand(t.record_type_id,:BITAND), :BITAND,
        CASE
            WHEN t.report_date = :ASOF_DATE
            THEN f.billed_amount
            ELSE null
        END
        )) FII_AR_BILLED_AMOUNT,
       SUM(DECODE(bitand(t.record_type_id,:BITAND), :BITAND,
        CASE
            WHEN t.report_date = :ASOF_DATE
            THEN f.app_amount
            ELSE null
        END
        )) FII_AR_APPLIED_AMOUNT,
        SUM(DECODE(bitand(t.record_type_id,:BITAND), :BITAND,
        CASE
            WHEN t.report_date = :ASOF_DATE
            THEN f.wtd_days_paid_num
            ELSE null
        END
        )) FII_AR_WTD_DAYS_PAID_NUM,
        SUM(DECODE(bitand(t.record_type_id,:BITAND), :BITAND,
        CASE
            WHEN t.report_date = :ASOF_DATE
            THEN f.wtd_terms_paid_num
            ELSE null
        END
        )) FII_AR_WTD_TERMS_PAID_NUM,
        SUM(DECODE(bitand(t.record_type_id,:BITAND), :BITAND,
        CASE
            WHEN t.report_date = :ASOF_DATE
            THEN f.avg_dd_num
            ELSE null
        END
        )) FII_AR_AVG_DD_NUM
    FROM '||l_time_table||' per,
        FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix ||' f,
        (
        SELECT /*+ no_merge leading(gt) cardinality(gt 1)*/  *
        FROM fii_time_structures cal,
            fii_ar_summary_gt gt
        WHERE report_date in(:ASOF_DATE, :PREVIOUS_ASOF_DATE, :PRIOR_PERIOD_START, :CURR_PERIOD_START)
            AND
            (
                bitand(cal.record_type_id, :BITAND) = :BITAND
                OR bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
            )
        ) t
    WHERE f.time_id = t.time_id
    AND f.period_type_id = t.period_type_id
    AND f.org_id = t.org_id '|| l_party_where ||' ' || l_collector_where ||' ' || l_industry_where || '
    AND per.end_date = :CURR_PERIOD_END
    AND '||fii_ar_util_pkg.get_mv_where_statement||'
    GROUP BY t.report_date, per.sequence, name';

Line: 1047


     /*Upper select clause. This is common for all period types*/
     -- In case when Union All is used which means that the current period should use a different scaling factor compared to
     -- the other periods

    l_select_clause := 'SELECT
     cy_per.name FII_AR_COLL_EFF_VIEW_BY,
     FII_AR_COLL_EFF_INDEX * 100 FII_AR_COLL_EFF_INDEX,
     FII_AR_PRIO_COLL_EFF_INDEX_G * 100 FII_AR_PRIO_COLL_EFF_INDEX_G,
     FII_AR_WTD_AVG_DP,
     (FII_AR_WTD_AVG_DP - FII_AR_WTD_AVG_TP) FII_AR_DAYS_DELQ,
     FII_AR_WTD_AVG_TP,
     FII_AR_REC_AMT,
 DECODE(FII_AR_WTD_AVG_DP,0,'''',DECODE(NVL(FII_AR_WTD_AVG_DP, -99999),-99999, '''',DECODE(SIGN(cy_per.end_date - :ASOF_DATE),1,
	''pFunctionName=FII_AR_COLL_EFFECTIVENESS&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'',
	''AS_OF_DATE=''|| to_char(cy_per.end_date,''DD/MM/YYYY'')||''&pFunctionName=FII_AR_COLL_EFFECTIVENESS&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'' )))
	FII_AR_DAYS_PAID_DRILL,
 DECODE(FII_AR_COLL_EFF_INDEX,0,'''',DECODE(NVL(FII_AR_COLL_EFF_INDEX, -99999),-99999, '''',DECODE(SIGN(cy_per.end_date - :ASOF_DATE),1,
	''pFunctionName=FII_AR_COLL_EFF_INDEX&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'',
	''AS_OF_DATE=''|| to_char(cy_per.end_date,''DD/MM/YYYY'')||''&pFunctionName=FII_AR_COLL_EFF_INDEX&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'' )))
	FII_AR_CEI_DRILL,
   DECODE(FII_AR_REC_AMT,0,'''',DECODE(NVL(FII_AR_REC_AMT, -99999),-99999, '''', DECODE(SIGN(cy_per.end_date - :ASOF_DATE),1,
	''pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'',
	''AS_OF_DATE=''|| to_char(cy_per.end_date,''DD/MM/YYYY'')||''&pFunctionName=FII_AR_REC_ACTIVITY&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+FII_OPERATING_UNITS&pParamIds=Y'' )))
	FII_AR_RCT_AMT_DRILL
FROM '||l_time_table||' cy_per, (
    SELECT
        sequence sequence,
     DECODE (sequence, ' || g_current_sequence ||',
    (SUM(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC))
    +(SUM(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT))'||g_scale_sign||' '||g_scaling_factor||')
    - SUM(DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC)))/NULLIF((SUM(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC))
    +(SUM(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT))'||g_scale_sign||' '||g_scaling_factor||')
    -SUM(DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC))),0),
    (SUM(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC))
    +(SUM(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT))'||g_scale_sign_cons||' '||g_scaling_factor_cons||')
    - SUM(DECODE(FII_AR_END_OPEN_REC, NULL, 0, FII_AR_END_OPEN_REC)))/NULLIF((SUM(DECODE(FII_AR_BEG_OPEN_REC, NULL, 0, FII_AR_BEG_OPEN_REC))
    +(SUM(DECODE(FII_AR_BILLED_AMOUNT, NULL, 0, FII_AR_BILLED_AMOUNT))'||g_scale_sign_cons||' '||g_scaling_factor_cons||')
    -SUM(DECODE(FII_AR_END_CURR_REC, NULL, 0, FII_AR_END_CURR_REC))),0) )FII_AR_COLL_EFF_INDEX,
 SUM(FII_AR_WTD_DAYS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) FII_AR_WTD_AVG_DP,
    SUM(FII_AR_AVG_DD_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) FII_AR_DAYS_DELQ,
    SUM(FII_AR_WTD_TERMS_PAID_NUM/NULLIF(FII_AR_APPLIED_AMOUNT,0)) FII_AR_WTD_AVG_TP,
    SUM(FII_AR_REC_AMT) FII_AR_REC_AMT,
 DECODE (sequence, ' || g_current_sequence ||',
    (SUM(DECODE(FII_PRIOR_AR_BEG_OPEN_REC, NULL, 0, FII_PRIOR_AR_BEG_OPEN_REC))
    +(SUM(DECODE(FII_PRIOR_AR_BILLED_AMOUNT, NULL, 0, FII_PRIOR_AR_BILLED_AMOUNT))'||g_scale_sign||' '||g_scaling_factor||')
    -SUM(DECODE(FII_PRIOR_AR_END_OPEN_REC, NULL, 0, FII_PRIOR_AR_END_OPEN_REC)))/NULLIF((SUM(DECODE(FII_PRIOR_AR_BEG_OPEN_REC, NULL, 0, FII_PRIOR_AR_BEG_OPEN_REC))
    +(SUM(DECODE(FII_PRIOR_AR_BILLED_AMOUNT, NULL, 0, FII_PRIOR_AR_BILLED_AMOUNT))'||g_scale_sign||' '||g_scaling_factor||')
    - SUM(DECODE(FII_PRIOR_AR_END_CURR_REC, NULL, 0, FII_PRIOR_AR_END_CURR_REC))),0),
    (SUM(DECODE(FII_PRIOR_AR_BEG_OPEN_REC, NULL, 0, FII_PRIOR_AR_BEG_OPEN_REC))
    +(SUM(DECODE(FII_PRIOR_AR_BILLED_AMOUNT, NULL, 0, FII_PRIOR_AR_BILLED_AMOUNT))'||g_scale_sign_cons||' '||g_scaling_factor_cons||')
    -SUM(DECODE(FII_PRIOR_AR_END_OPEN_REC, NULL, 0, FII_PRIOR_AR_END_OPEN_REC)))/NULLIF((SUM(DECODE(FII_PRIOR_AR_BEG_OPEN_REC, NULL, 0, FII_PRIOR_AR_BEG_OPEN_REC))
    +(SUM(DECODE(FII_PRIOR_AR_BILLED_AMOUNT, NULL, 0, FII_PRIOR_AR_BILLED_AMOUNT))'||g_scale_sign_cons||' '||g_scaling_factor_cons||')
    - SUM(DECODE(FII_PRIOR_AR_END_CURR_REC, NULL, 0, FII_PRIOR_AR_END_CURR_REC))),0) ) FII_AR_PRIO_COLL_EFF_INDEX_G
	from (
   SELECT per.name,per.sequence,
   sum(FII_AR_REC_AMT) FII_AR_REC_AMT,
   sum(FII_AR_BEG_OPEN_REC) FII_AR_BEG_OPEN_REC,
   sum(FII_AR_END_OPEN_REC) FII_AR_END_OPEN_REC,
   sum(FII_AR_END_CURR_REC) FII_AR_END_CURR_REC,
   sum(FII_PRIOR_AR_BEG_OPEN_REC) FII_PRIOR_AR_BEG_OPEN_REC,
   sum(FII_PRIOR_AR_END_OPEN_REC)FII_PRIOR_AR_END_OPEN_REC,
   sum(FII_PRIOR_AR_END_CURR_REC) FII_PRIOR_AR_END_CURR_REC,
   sum(FII_PRIOR_AR_BILLED_AMOUNT) FII_PRIOR_AR_BILLED_AMOUNT,
   sum(FII_AR_BILLED_AMOUNT) FII_AR_BILLED_AMOUNT,
   sum(FII_AR_APPLIED_AMOUNT) FII_AR_APPLIED_AMOUNT,
   sum(FII_AR_WTD_DAYS_PAID_NUM) FII_AR_WTD_DAYS_PAID_NUM,
   sum(FII_AR_WTD_TERMS_PAID_NUM) FII_AR_WTD_TERMS_PAID_NUM,
   sum(FII_AR_AVG_DD_NUM) FII_AR_AVG_DD_NUM FROM '||l_time_table||' per,(';

Line: 1121


 sqlstmt := l_select_clause || '
     SELECT /*+ no_merge INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
        t.report_date,
        SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'' , null,
        CASE
            WHEN (t.report_date >= :SD_SDATE )
	    AND (f.header_filter_date <= t.report_date)
	    AND (f.header_filter_date >= (select start_date
		                             from '||l_time_table||'
					     where end_date = t.report_date))
            AND (bitand(t.record_type_id, :BITAND) = :BITAND )
            THEN f.total_receipt_amount
            ELSE NULL
        END )) FII_AR_REC_AMT,
        SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'' ,
        CASE
            WHEN t.report_date >= :SD_SDATE
            AND bitand(t.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE /*This needs to be the first date of the month */
            THEN f.total_open_amount
            ELSE null
        END )) FII_AR_BEG_OPEN_REC,
        SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'', null,
        CASE
            WHEN t.report_date >= :SD_SDATE
            AND bitand(t.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
            THEN f.total_open_amount
            ELSE null
        END
        )) FII_AR_END_OPEN_REC,
        SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'' , null,
        CASE
            WHEN t.report_date >= :SD_SDATE
            AND bitand(t.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
            THEN f.current_open_amount
            ELSE null
        END
        )) FII_AR_END_CURR_REC,
     '||l_prior_column||'
        SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'' , null,
        CASE
            WHEN t.report_date >= :SD_SDATE
            AND bitand(t.record_type_id, :BITAND) = :BITAND
            THEN f.billed_amount
            ELSE NULL
        END))
        FII_AR_BILLED_AMOUNT,
        SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'' , null,
        CASE
            WHEN t.report_date >= :SD_SDATE
            AND bitand(t.record_type_id, :BITAND) = :BITAND
            THEN f.app_amount
            ELSE NULL
        END ))        FII_AR_APPLIED_AMOUNT,
        SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'', null,
        CASE
            WHEN t.report_date >= :SD_SDATE
            AND bitand(t.record_type_id, :BITAND) = :BITAND
            THEN f.wtd_days_paid_num
            ELSE NULL
        END ))        FII_AR_WTD_DAYS_PAID_NUM,
        SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'' , null,
        CASE
            WHEN t.report_date >= :SD_SDATE
            AND bitand(t.record_type_id, :BITAND) = :BITAND
            THEN f.wtd_terms_paid_num
            ELSE NULL
        END ))        FII_AR_WTD_TERMS_PAID_NUM,
        SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'' , null,
        CASE
            WHEN t.report_date >= :SD_SDATE
            AND bitand(t.record_type_id, :BITAND) = :BITAND
            THEN f.avg_dd_num
            ELSE NULL
        END ))        FII_AR_AVG_DD_NUM
 FROM  FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix
             ||' f,
        (
         SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) leading(gt) cardinality(gt 1)*/   *
	 FROM fii_time_structures cal,
         fii_ar_summary_gt gt
	 WHERE report_date in
         (
          SELECT end_date
          FROM '||l_time_table||' cy_per
          WHERE cy_per.start_date < '||l_date_bind||'
          AND cy_per.start_date >= :SD_PRIOR_PRIOR
          UNION
          SELECT start_date
          FROM '||l_time_table||' cy_per
          WHERE cy_per.start_date <'||l_date_bind||'
          AND cy_per.start_date >=:SD_PRIOR_PRIOR
         )
         AND (bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE  OR bitand(cal.record_type_id, :BITAND) = :BITAND )
        )
        t
    WHERE f.time_id = t.time_id
    AND f.period_type_id = t.period_type_id
    AND f.org_id = t.org_id
    AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_party_where ||' ' || l_collector_where ||' ' || l_industry_where || '
     GROUP BY t.report_date) mv
    WHERE     per.start_date >= :SD_PRIOR_PRIOR
    and ( per.end_date = mv.report_date
        OR per.start_date = mv.report_date)
    GROUP BY per.sequence, per.name
    '||l_curr_sql_stmt||'
    )
    group by sequence)outer_view
 WHERE cy_per.start_date <= :ASOF_DATE
 AND cy_per.start_date > :SD_PRIOR
 AND cy_per.sequence = outer_view.sequence (+)
 ORDER BY cy_per.start_date   ';

Line: 1247


  sqlstmt := l_select_clause || '
     SELECT
        /*+ no_merge INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
        t.report_date,
      SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'' , null,
        CASE
            WHEN  (f.header_filter_date <= t.report_date)
	    AND (f.header_filter_date >= (select start_date
		                             from '||l_time_table||'
					     where end_date = t.report_date))
   AND (bitand(t.record_type_id, :BITAND) = :BITAND)
            THEN f.total_receipt_amount
            ELSE NULL
        END )) FII_AR_REC_AMT,
        SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'' ,
        CASE
            WHEN  bitand(t.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE /*This needs to be the first date of the month */
            THEN f.total_open_amount
            ELSE null
        END )) FII_AR_BEG_OPEN_REC,
        SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'' , null,
        CASE
            WHEN  bitand(t.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
            THEN f.total_open_amount
            ELSE null
        END
        )) FII_AR_END_OPEN_REC,
        SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'' , null,
        CASE
            WHEN bitand(t.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE
            THEN f.current_open_amount
            ELSE null
        END
        )) FII_AR_END_CURR_REC,
        '||l_prior_column||'
        SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'' , null,
        CASE
            WHEN  bitand(t.record_type_id, :BITAND) = :BITAND
            THEN f.billed_amount
            ELSE NULL
        END))
        FII_AR_BILLED_AMOUNT,
        SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'' , null,
        CASE
            WHEN  bitand(t.record_type_id, :BITAND) = :BITAND
            THEN f.app_amount
            ELSE NULL
        END ))        FII_AR_APPLIED_AMOUNT,
        SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'' , null,
        CASE
            WHEN  bitand(t.record_type_id, :BITAND) = :BITAND
            THEN f.wtd_days_paid_num
            ELSE NULL
        END ))        FII_AR_WTD_DAYS_PAID_NUM,
        SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'' , null,
        CASE
            WHEN  bitand(t.record_type_id, :BITAND) = :BITAND
            THEN f.wtd_terms_paid_num
            ELSE NULL
        END ))        FII_AR_WTD_TERMS_PAID_NUM,
        SUM(DECODE((SELECT ''Y'' from '||l_time_table||'
		    WHERE start_date = t.report_date), ''Y'' , null,
        CASE
            WHEN  bitand(t.record_type_id, :BITAND) = :BITAND
            THEN f.avg_dd_num
            ELSE NULL
        END ))        FII_AR_AVG_DD_NUM
FROM  FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv'|| fii_ar_util_pkg.g_curr_suffix
             ||' f,
        (
         SELECT /*+ no_merge INDEX(cal FII_TIME_STRUCTURES_N1) leading(gt) cardinality(gt 1) */  *
	 FROM fii_time_structures cal,
         fii_ar_summary_gt gt
	 WHERE report_date in
         (
          SELECT end_date
          FROM '||l_time_table||' cy_per
          WHERE cy_per.start_date < '||l_date_bind||'
          AND cy_per.start_date >= :SD_PRIOR_PRIOR
          UNION
          SELECT start_date
          FROM '||l_time_table||' cy_per
          WHERE cy_per.start_date < '||l_date_bind||'
          AND cy_per.start_date >=:SD_PRIOR_PRIOR
         )
         AND (bitand(cal.record_type_id, :BITAND_INC_TODATE) = :BITAND_INC_TODATE  OR bitand(cal.record_type_id, :BITAND) = :BITAND )
        ) t
    WHERE f.time_id = t.time_id
    AND f.period_type_id = t.period_type_id
    AND f.org_id = t.org_id
    AND '||fii_ar_util_pkg.get_mv_where_statement||' '|| l_party_where ||'
    ' || l_collector_where ||'
    ' || l_industry_where || '
    GROUP BY t.report_date) mv
    WHERE     per.start_date >= :SD_PRIOR_PRIOR
    and ( per.end_date = mv.report_date
        OR per.start_date = mv.report_date
	)
    GROUP BY per.sequence, per.name
   '||l_curr_sql_stmt||'
    )
    group by sequence)outer_view
  WHERE cy_per.start_date <= :ASOF_DATE
  AND cy_per.start_date > :SD_PRIOR
  AND cy_per.sequence = outer_view.sequence (+)
  ORDER BY cy_per.start_date   ';