[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Get the parameters that the user has selected */
fii_ar_util_pkg.get_parameters(p_page_parameter_tbl);
/*This means no particular sort column is selected in the report
So sort on the default column in descending order
NVL is added to make sure the NULL values appear last*/
l_order_by := 'ORDER BY NVL(FII_AR_BILL_ACT_AMT, -999999999) DESC';
l_sql_stmt := 'SELECT VIEWBY,
VIEW_BY_ID VIEWBYID,
SUM(FII_AR_BILL_ACT_AMT_PRIOR) FII_AR_BILL_ACT_AMT_PRIOR,
NULLIF(SUM(FII_AR_BILL_ACT_AMT),0) FII_AR_BILL_ACT_AMT,
(( SUM(FII_AR_BILL_ACT_AMT)-SUM(FII_AR_BILL_ACT_AMT_PRIOR) ) /
NULLIF(SUM(FII_AR_BILL_ACT_AMT_PRIOR),0 )) * 100 FII_AR_BILL_ACT_AMT_CHG,
SUM(FII_AR_BILL_ACT_COUNT) FII_AR_BILL_ACT_COUNT,
(( SUM(FII_AR_BILL_ACT_COUNT)-SUM(FII_AR_BILL_ACT_COUNT_PRIOR) ) /
NULLIF(SUM(FII_AR_BILL_ACT_COUNT_PRIOR),0) ) * 100 FII_AR_BILL_ACT_COUNT_CHG,
NULLIF(SUM(FII_AR_OPEN_REC_AMT),0) FII_AR_BILL_OPEN_REC_AMT,
(SUM(net_receivable_amount) / NULLIF(SUM(billed_amount),0)) *'||l_dso_period||' FII_AR_BILL_ACT_DSO,
NULLIF(SUM(SUM(FII_AR_BILL_ACT_AMT)) over(),0) FII_AR_GT_BILL_ACT_AMT ,
((SUM(SUM(FII_AR_BILL_ACT_AMT)) over() - SUM(SUM(FII_AR_BILL_ACT_AMT_PRIOR)) over() )/
NULLIF(SUM(SUM(FII_AR_BILL_ACT_AMT_PRIOR)) over(),0)) *100
FII_AR_GT_BILL_ACT_AMT_CHG,
NULLIF(SUM(SUM(FII_AR_BILL_ACT_COUNT)) over(),0) FII_AR_GT_BILL_ACT_COUNT,
((SUM(SUM(FII_AR_BILL_ACT_COUNT)) over() - SUM(SUM(FII_AR_BILL_ACT_COUNT_PRIOR)) over() )/
NULLIF(SUM(SUM(FII_AR_BILL_ACT_COUNT_PRIOR)) over(),0)) *100
FII_AR_GT_BILL_ACT_COUNT_CHG,
NULLIF(SUM(SUM(FII_AR_OPEN_REC_AMT)) over(),0) FII_AR_GT_OPEN_REC_AMT,
(SUM(SUM(net_receivable_amount)) over()/NULLIF(SUM(SUM(billed_amount)) over(),0) )*'||l_dso_period||' FII_AR_GT_BILL_ACT_DSO,
(SUM(prior_net_receivable_amount) / NULLIF(SUM(prior_billed_amount),0)) *'||l_dso_period||' FII_AR_PRIOR_DSO_KPI,
(SUM(SUM(prior_net_receivable_amount)) OVER()/NULLIF(SUM(SUM(prior_billed_amount)) OVER(),0) )*'||l_dso_period||' FII_AR_GT_PRIOR_DSO_KPI,
SUM (SUM(FII_AR_BILL_ACT_AMT_PRIOR)) OVER () FII_AR_GT_BILL_ACT_PRIOR_AMT, ';
l_sql_stmt := l_sql_stmt || ' FROM ( select /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
CASE WHEN gt.report_date=:PREVIOUS_ASOF_DATE and BITAND(gt.record_type_id,:BITAND)= :BITAND THEN
f.billing_activity_amount
ELSE
NULL
END FII_AR_BILL_ACT_AMT_PRIOR,
CASE WHEN gt.report_date=:ASOF_DATE and BITAND(gt.record_type_id,:BITAND)= :BITAND THEN
f.billing_activity_amount
ELSE
NULL
END FII_AR_BILL_ACT_AMT,
CASE WHEN gt.report_date=:ASOF_DATE and BITAND(gt.record_type_id,:BITAND)= :BITAND THEN
f.billing_activity_count
ELSE
NULL
END FII_AR_BILL_ACT_COUNT,
CASE WHEN gt.report_date=:PREVIOUS_ASOF_DATE and BITAND(gt.record_type_id,:BITAND)= :BITAND THEN
f.billing_activity_count
ELSE
NULL
END FII_AR_BILL_ACT_COUNT_PRIOR,
CASE when gt.report_date=:ASOF_DATE and BITAND(gt.record_type_Id,:BITAND_INC_TODATE) = :BITAND_INC_TODATE THEN
f.total_open_amount
ELSE
NULL
END FII_AR_OPEN_REC_AMT,
CASE when gt.report_date=:ASOF_DATE and BITAND(gt.record_type_id,:BITAND_INC_TODATE)= :BITAND_INC_TODATE THEN '||
l_class_inclusion ||'
ELSE
NULL
END NET_RECEIVABLE_AMOUNT,
CASE when gt.report_date=:ASOF_DATE and BITAND(gt.record_type_id,:DSO_BITAND)= :DSO_BITAND THEN
f.billed_amount
ELSE
NULL
END BILLED_AMOUNT ,
CASE when gt.report_date=:PREVIOUS_ASOF_DATE and BITAND(gt.record_type_id,:BITAND_INC_TODATE)= :BITAND_INC_TODATE THEN '||
l_class_inclusion ||'
ELSE
NULL
END PRIOR_NET_RECEIVABLE_AMOUNT,
CASE when gt.report_date=:PREVIOUS_ASOF_DATE and BITAND(gt.record_type_id,:DSO_BITAND)= :DSO_BITAND THEN
f.billed_amount
ELSE
NULL
END PRIOR_BILLED_AMOUNT,
gt.viewby viewby,
gt.viewby_code VIEW_BY_ID';
(select /*+ no_merge '||l_gt_hint|| ' */ * from fii_time_structures cal, '||l_inner_from_clause||' t
where cal.report_date in(:ASOF_DATE ,:PREVIOUS_ASOF_DATE)
and (BITAND(cal.record_type_id,:BITAND)= :BITAND
OR BITAND(cal.record_type_id,:BITAND_INC_TODATE)= :BITAND_INC_TODATE
OR BITAND(cal.record_type_id,:DSO_BITAND)=:DSO_BITAND ) '||l_inner_where_clause ||' ) gt
where f.time_id = gt.time_id
and gt.period_type_id=f.period_type_id
'|| l_where_clause||' and '||fii_ar_util_pkg.get_mv_where_statement;
SELECT /*+ INDEX(f FII_AR_RCT_AGING'|| fii_ar_util_pkg.g_cust_suffix ||'_MV_N1)*/ NULL FII_AR_BILL_ACT_AMT_PRIOR,
NULL FII_AR_BILL_ACT_AMT,
NULL FII_AR_BILL_ACT_COUNT,
NULL FII_AR_BILL_ACT_COUNT_PRIOR,
NULL FII_AR_OPEN_REC_AMT,
CASE when gt.report_date=:ASOF_DATE and BITAND(gt.record_type_id,:BITAND_INC_TODATE)= :BITAND_INC_TODATE THEN '||
l_unapp_amount || ' ELSE NULL END NET_RECEIVABLE_AMOUNT,
NULL BILLED_AMOUNT,
CASE when gt.report_date=:PREVIOUS_ASOF_DATE and BITAND(gt.record_type_id,:BITAND_INC_TODATE)= :BITAND_INC_TODATE THEN '||
l_unapp_amount || ' ELSE NULL END PRIOR_NET_RECEIVABLE_AMOUNT,
NULL PRIOR_BILLED_AMOUNT,
gt.viewby viewby,
gt.viewby_code VIEW_BY_ID';
-- This piece was redundant as it was already done for the 1st select and was not reqd for 2nd select
/* l_cust_acct_or_leaf_amt_drill := l_cust_acct_or_leaf_amt_drill||'&BIS_PMV_DRILL_CODE_CUSTOMER+FII_CUSTOMERS=';
(select /*+ no_merge '||l_gt_hint|| ' */ * from fii_time_structures cal, '||l_inner_from_clause||' t
where cal.report_date in(:ASOF_DATE ,:PREVIOUS_ASOF_DATE)
and (BITAND(cal.record_type_id,:BITAND)= :BITAND
OR BITAND(cal.record_type_id,:BITAND_INC_TODATE)= :BITAND_INC_TODATE
OR BITAND(cal.record_type_id,:DSO_BITAND)=:DSO_BITAND ) '||l_inner_where_clause ||' ) gt
where f.time_id = gt.time_id
and gt.period_type_id=f.period_type_id
AND '||fii_ar_util_pkg.get_rct_mv_where_statement||' '|| l_where_clause;