APPS.FII_AR_TOP_PDUE_PKG SQL Statements

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

Line: 19

  l_parent_select    varchar2(500);

Line: 99

	    SELECT view_all_organizations_flag, business_group_id
    	INTO l_all_org_flag, l_business_group_id
    	FROM per_security_profiles
    	WHERE security_profile_id = l_security_profile_id;

Line: 113

				l_org_where := ' and f.org_id in (SELECT per.organization_id
												FROM hr_operating_units per, ar_system_parameters_all ar
												WHERE per.business_group_id = 	'||l_business_group_id ||'
			                  AND per.organization_id = ar.org_id '||l_org_specific_where||') ';

Line: 120

        l_org_where := ' and f.org_id in  (SELECT per.organization_id
												FROM hr_operating_units per
												WHERE 1=1 '||l_org_specific_where||') ';

Line: 126

			  l_org_where := ' and f.org_id in  (SELECT organization_id
		   									FROM per_organization_list per, ar_system_parameters_all ar
			                  WHERE per.security_profile_id = '||l_security_profile_id ||'
			                   AND per.organization_id = ar.org_id '||l_org_specific_where||') ';

Line: 155

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

Line: 190

sqlstmt := 'SELECT inline_view.view_by VIEWBY,
		     		inline_view.viewby_code VIEWBYID,
            inline_view.view_by FII_AR_TOP_PDUE_VIEW_BY,
            SUM(FII_AR_PASTDUE_REC_AMT) FII_AR_PASTDUE_REC_AMT,
            (SUM(FII_AR_DISPUTE_AMT)/NULLIF(SUM(FII_AR_PASTDUE_REC_AMT),0))*100 FII_AR_DISPUTE_PER,
            (SUM(FII_AR_PASTDUE_REC_AMT) * to_number(to_char(:PAGE_REFRESH_DATE , ''J'')) -  SUM(FII_AR_WEIGHTED_DDSO_NUM))/NULLIF(SUM(FII_AR_PASTDUE_REC_AMT),0) FII_AR_WEIGHTED_DDSO,
            (SUM(FII_AR_PASTDUE_REC_AMT)/NULLIF((SUM(FII_AR_PASTDUE_REC_AMT) + to_number(SUM(FII_AR_CURRENT_OPEN_AMT))),0)) *100 FII_AR_OPEN_REC_PER,
            (SUM(FII_AR_PASTDUE_REC_AMT) + SUM(FII_AR_CURRENT_OPEN_AMT)) FII_AR_OPEN_REC_AMT,
            (SUM(FII_AR_WEIGHTED_TO_NUM)/NULLIF((SUM(FII_AR_PASTDUE_REC_AMT) + SUM(FII_AR_CURRENT_OPEN_AMT)),0)) FII_AR_WEIGHTED_TO,
            SUM(SUM(FII_AR_PASTDUE_REC_AMT)) over() FII_AR_GT_PASTDUE_REC_AMT,
            (SUM(SUM(FII_AR_DISPUTE_AMT))over()/NULLIF(SUM(SUM(FII_AR_PASTDUE_REC_AMT))over(),0))*100 FII_AR_GT_DISPUTE_PCT_TOTAL,
            (SUM(SUM(FII_AR_PASTDUE_REC_AMT)) over() * to_number(to_char(:PAGE_REFRESH_DATE , ''J'')) -  SUM(SUM(FII_AR_WEIGHTED_DDSO_NUM)) over())/NULLIF(SUM(SUM(FII_AR_PASTDUE_REC_AMT)) OVER(),0) FII_AR_GT_WEIGHTED_DDSO,
            (SUM(SUM(FII_AR_PASTDUE_REC_AMT)) OVER()/NULLIF((SUM(SUM(FII_AR_PASTDUE_REC_AMT)) OVER() + SUM(SUM(FII_AR_CURRENT_OPEN_AMT)) OVER()),0)) *100 FII_AR_GT_PER_OPEN_REC,
            (SUM(SUM(FII_AR_PASTDUE_REC_AMT)) over() + SUM(SUM(FII_AR_CURRENT_OPEN_AMT)) over()) FII_AR_GT_OPEN_REC_AMT,
            (SUM(SUM(FII_AR_WEIGHTED_TO_NUM)) OVER()/NULLIF((SUM(SUM(FII_AR_PASTDUE_REC_AMT)) OVER() + SUM(SUM(FII_AR_CURRENT_OPEN_AMT)) OVER()),0)) FII_AR_GT_WEIGHTED_TO,
            DECODE(SUM(FII_AR_PASTDUE_REC_AMT),0,'''',DECODE(NVL(SUM(FII_AR_PASTDUE_REC_AMT), -99999),-99999, '''',''pFunctionName=FII_AR_PASTDUE_REC_AGING'||
            '&FII_CUSTOMER=VIEWBYID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS&BIS_PMV_DRILL_CODE_AS_OF_DATE='||l_page_refresh_date||'&pParamIds=Y'')) FII_AR_PDUE_REC_DRILL,
            DECODE((SUM(FII_AR_PASTDUE_REC_AMT) + SUM(FII_AR_CURRENT_OPEN_AMT)),0,'''',
            DECODE(NVL((SUM(FII_AR_PASTDUE_REC_AMT) +
            SUM(FII_AR_CURRENT_OPEN_AMT)), -99999),-99999, '''',''pFunctionName=FII_AR_OPEN_REC_SUMMARY&FII_CUSTOMER=VIEWBYID&VIEW_BY=CUSTOMER+FII_CUSTOMER_ACCOUNTS'||
            '&BIS_PMV_DRILL_CODE_AS_OF_DATE='||l_page_refresh_date||'&pParamIds=Y'')) FII_AR_OPEN_REC_DRILL,
            DECODE(inline_view.is_self_flag, ''Y'' , '''', DECODE(inline_view.is_leaf_flag, ''Y'','''', ''pFunctionName=FII_AR_TOP_PDUE_CUSTOMER&FII_CUSTOMER=VIEWBYID&VIEW_BY=CUSTOMER+FII_CUSTOMERS&pParamIds=Y'')) FII_AR_CUST_SELF_DRILL
      from (
           SELECT VIEW_BY view_by, viewby_code,
                  is_self_flag is_self_flag,
                  is_leaf_flag is_leaf_flag,
                  sum(past_due_open_amount'||fii_ar_util_pkg.g_col_curr_suffix||')   FII_AR_PASTDUE_REC_AMT,
                  sum(wtd_terms_out_open_num'||fii_ar_util_pkg.g_col_curr_suffix||') FII_AR_WEIGHTED_TO_NUM,
                  sum(wtd_ddso_due_num'||fii_ar_util_pkg.g_col_curr_suffix||')       FII_AR_WEIGHTED_DDSO_NUM,
                  sum(current_open_amount'||fii_ar_util_pkg.g_col_curr_suffix||')    FII_AR_CURRENT_OPEN_AMT,
                  sum(past_due_dispute_amount'||fii_ar_util_pkg.g_col_curr_suffix||') FII_AR_DISPUTE_AMT
            FROM FII_AR_TPDUE'|| fii_ar_util_pkg.g_cust_suffix ||'_F f
            WHERE 1=1
            '||l_party_where||l_collector_where||l_org_where||'
       			GROUP BY  viewby_code, VIEW_BY, is_self_flag, is_leaf_flag
  					)inline_view
       GROUP BY  viewby_code, VIEW_BY, is_self_flag, is_leaf_flag '||l_order_by;