APPS.FII_AR_OPEN_REC_SUMMARY SQL Statements

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

Line: 9

l_party_select		VARCHAR2(100);

Line: 21

l_select		VARCHAR2(10000);

Line: 119

l_party_select   := ' t.party_id,';

Line: 140

   /*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_ORS_OPEN_REC_AMT, -999999999) DESC';

Line: 164

l_select :=
' SELECT
viewby       VIEWBY,
viewby_code  VIEWBYID,
FII_AR_ORS_OPEN_REC_AMT,
FII_AR_ORS_PDUE_REC_AMT,
FII_AR_ORS_CURR_REC_AMT,
FII_AR_ORS_OPEN_REC_CT,
FII_AR_ORS_PDUE_REC_CT,
FII_AR_ORS_CURR_REC_CT,
FII_AR_ORS_OPEN_REC_WTD_TRM,
FII_AR_ORS_PDUE_REC_PERCENT,
FII_AR_ORS_PDUE_REC_WTD_DDSO,
SUM(FII_AR_ORS_OPEN_REC_AMT) over() FII_AR_ORS_OPEN_R_AMT_GT,
SUM(FII_AR_ORS_PDUE_REC_AMT) over() FII_AR_ORS_PDUE_R_AMT_GT,
SUM(FII_AR_ORS_CURR_REC_AMT) over() FII_AR_ORS_CURR_R_AMT_GT,
SUM(FII_AR_ORS_OPEN_REC_CT)  over() FII_AR_ORS_OPEN_R_CT_GT,
SUM(FII_AR_ORS_PDUE_REC_CT)  over() FII_AR_ORS_PDUE_R_CT_GT,
SUM(FII_AR_ORS_CURR_REC_CT)  over() FII_AR_ORS_CURR_R_CT_GT,
CASE WHEN sum(FII_AR_ORS_OPEN_REC_AMT) over() = 0
     THEN NULL
     ELSE sum(FII_AR_ORS_OPEN_REC_WTD_TRM_N) over() / sum(FII_AR_ORS_OPEN_REC_AMT) over()
     END  FII_AR_ORS_OPEN_R_WTD_TRM_GT,
CASE WHEN sum(FII_AR_ORS_OPEN_REC_AMT) over() = 0
     THEN NULL
     ELSE (sum(FII_AR_ORS_PDUE_REC_AMT) over() / sum(FII_AR_ORS_OPEN_REC_AMT) over()) * 100
	 END FII_AR_ORS_PDUE_R_PERCENT_GT,
CASE WHEN sum(FII_AR_ORS_PDUE_REC_AMT) over() = 0
     THEN NULL
     ELSE (sum(FII_AR_ORS_PDUE_REC_AMT) over()
	       * to_number(to_char(&BIS_CURRENT_ASOF_DATE,''J''))
	       - sum(FII_AR_ORS_PDUE_REC_WTD_DDSO_N) over()) / sum(FII_AR_ORS_PDUE_REC_AMT) over()
     END FII_AR_ORS_PDUE_R_WTD_DDSO_GT,
'|| l_cust_drill ||' FII_AR_ORS_CUST_DRILL,
'|| l_open_drill ||' FII_AR_ORS_OPEN_DRILL,
'|| l_pdue_drill ||' FII_AR_ORS_PDUE_DRILL,
'|| l_curr_drill ||' FII_AR_ORS_CURR_DRILL
FROM (
SELECT  /*+ INDEX(f FII_AR_NET_REC'|| fii_ar_util_pkg.g_cust_suffix ||'_mv_N1)*/
t.is_leaf_flag,
'||l_self||'
'||l_party_select||'
t.viewby,
t.viewby_code,
sum(total_open_amount)                       FII_AR_ORS_OPEN_REC_AMT,
sum(past_due_open_amount)                         FII_AR_ORS_PDUE_REC_AMT,
sum(current_open_amount)                     FII_AR_ORS_CURR_REC_AMT,
sum(total_open_count)                        FII_AR_ORS_OPEN_REC_CT,
sum(past_due_count)                          FII_AR_ORS_PDUE_REC_CT,
sum(current_open_count)                      FII_AR_ORS_CURR_REC_CT,
sum(wtd_terms_out_open_num)                  FII_AR_ORS_OPEN_REC_WTD_TRM_N,
sum(wtd_DDSO_due_num)                        FII_AR_ORS_PDUE_REC_WTD_DDSO_N,
CASE WHEN abs(sum(total_open_amount)) = 0
     THEN NULL
     ELSE sum(wtd_terms_out_open_num) / abs(sum(total_open_amount))
     END  FII_AR_ORS_OPEN_REC_WTD_TRM,
CASE WHEN abs(sum(total_open_amount)) = 0
     THEN NULL
     ELSE sum(past_due_open_amount) / abs(sum(total_open_amount)) * 100
     END  FII_AR_ORS_PDUE_REC_PERCENT,
CASE WHEN abs(sum(past_due_open_amount)) = 0
     THEN NULL
     ELSE (sum(past_due_open_amount)
	       * to_number(to_char(&BIS_CURRENT_ASOF_DATE ,''J''))
	       - sum(wtd_DDSO_due_num)) / abs(sum(past_due_open_amount))
     END FII_AR_ORS_PDUE_REC_WTD_DDSO
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 = :ASOF_DATE
        AND 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
  AND  f.org_id = t.org_id
  AND '||fii_ar_util_pkg.get_mv_where_statement||' '||l_parent_party_where||l_party_where||l_collector_where|| l_cust_acct_where||'
GROUP BY  t.is_leaf_flag, '||l_self||l_party_group_by||' t.viewby, t.viewby_code)  inline_view
GROUP BY '||l_group_by||',
FII_AR_ORS_OPEN_REC_AMT,
FII_AR_ORS_PDUE_REC_AMT,
FII_AR_ORS_CURR_REC_AMT,
FII_AR_ORS_OPEN_REC_CT,
FII_AR_ORS_PDUE_REC_CT,
FII_AR_ORS_CURR_REC_CT,
FII_AR_ORS_OPEN_REC_WTD_TRM_N,
FII_AR_ORS_OPEN_REC_WTD_TRM,
FII_AR_ORS_PDUE_REC_PERCENT,
FII_AR_ORS_PDUE_REC_WTD_DDSO_N,
FII_AR_ORS_PDUE_REC_WTD_DDSO
'||l_order_by||' ';

Line: 258

fii_ar_util_pkg.bind_variable(l_select, p_page_parameter_tbl, open_rec_sum_sql, open_rec_sum_output);