APPS.FII_PSI_FUNDS_AVAIL_SUM_PKG SQL Statements

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

Line: 21

       l_insert_sql_b            VARCHAR2(2000);

Line: 126

SELECT dbi_enabled_flag INTO l_ud1_enabled_flag
FROM fii_financial_dimensions
WHERE dimension_short_name = 'FII_USER_DEFINED_1';

Line: 133

SELECT dbi_enabled_flag INTO l_enabled_flag
FROM fii_financial_dimensions
WHERE dimension_short_name = 'FII_USER_DEFINED_2';

Line: 157

l_insert_sql_b :=
	'INSERT INTO FII_PSI_PMV_GT(
			VIEWBY,
			VIEWBYID,
			SORT_ORDER,
			FII_PSI_AVAIL_C,
			FII_PSI_PRIOR_AVAIL_C,
			FII_PSI_GT_AVAIL_C,
			FII_PSI_GT_PRIOR_AVAIL_C,
			FII_PSI_PCNT_AVAIL_C,
			FII_PSI_PRIOR_PCNT_AVAIL_C,
			FII_PSI_GT_PCNT_AVAIL_C,
			FII_PSI_GT_PRIOR_PCNT_AVAIL_C,
			FII_PSI_BUDGET_C,
			FII_PSI_PRIOR_BUDGET_C,
			FII_PSI_GT_BUDGET_C,
			FII_PSI_GT_PRIOR_BUDGET_C,
			FII_PSI_ENCUMBRANCES_C,
			FII_PSI_PRIOR_ENCUMBRANCES_C,
			FII_PSI_GT_ENCUMBRANCES_C,
			FII_PSI_GT_PRIOR_ENCUM_C,
			FII_PSI_COMMITTED_C_KPI,
			FII_PSI_PRIOR_COMMITTED_C,
			FII_PSI_GT_COMMITTED_C_KPI,
			FII_PSI_GT_PRIOR_COMMITTED_C,
			FII_PSI_OBLIGATED_C_KPI,
			FII_PSI_PRIOR_OBLIGATED_C,
			FII_PSI_GT_OBLIGATED_C_KPI,
			FII_PSI_GT_PRIOR_OBLIGATED_C,
			FII_PSI_OTHERS_C_KPI,
			FII_PSI_PRIOR_OTHERS_C,
			FII_PSI_GT_OTHERS_C_KPI,
			FII_PSI_GT_PRIOR_OTHERS_C,
			FII_PSI_ACTUALS_C,
			FII_PSI_PRIOR_ACTUALS_C,
			FII_PSI_GT_ACTUALS_C,
			FII_PSI_GT_PRIOR_ACTUALS_C,
			FII_PSI_BUDGET_A,
			FII_PSI_ENCUMBRANCES_A,
			FII_PSI_ACTUALS_A,
			FII_PSI_GT_BUDGET_A,
			FII_PSI_GT_ENCUMBRANCES_A,
			FII_PSI_GT_ACTUALS_A,
			FII_PSI_COMP_DRILL,
			FII_PSI_CC_DRILL,
			FII_PSI_CAT_DRILL,
			FII_PSI_PROJECT_DRILL,
			FII_PSI_UD2_DRILL)';

Line: 208

'SELECT  DECODE(:G_ID, f.VIEWBYID, DECODE(''' || l_if_leaf_flag || ''', ''Y'',
                                                   f.VIEWBY, f.VIEWBY||'' ''||:DIR_MSG),
                                            f.VIEWBY) VIEWBY,
        f.VIEWBYID VIEWBYID, ' || l_sort_order || '
        DECODE(:G_ID, f.VIEWBYID, DECODE(''' || l_if_leaf_flag || ''', ''Y'',
               FII_PSI_BUDGET_C - FII_PSI_ENCUMBRANCES_C - FII_PSI_ACTUALS_C, NULL),
               FII_PSI_BUDGET_C - FII_PSI_ENCUMBRANCES_C - FII_PSI_ACTUALS_C) FII_PSI_AVAIL_C,
        FII_PSI_PRIOR_BUDGET_C - FII_PSI_PRIOR_ENCUMBRANCES_C - FII_PSI_PRIOR_ACTUALS_C FII_PSI_PRIOR_AVAIL_C,
        SUM(FII_PSI_BUDGET_C - FII_PSI_ENCUMBRANCES_C - FII_PSI_ACTUALS_C) OVER () FII_PSI_GT_AVAIL_C,
        SUM(FII_PSI_PRIOR_BUDGET_C - FII_PSI_PRIOR_ENCUMBRANCES_C - FII_PSI_PRIOR_ACTUALS_C) OVER () FII_PSI_GT_PRIOR_AVAIL_C,
        DECODE(:G_ID, f.VIEWBYID, DECODE(''' || l_if_leaf_flag || ''', ''Y'',
             ((FII_PSI_BUDGET_C - FII_PSI_ENCUMBRANCES_C - FII_PSI_ACTUALS_C) / NULLIF(FII_PSI_BUDGET_C,0)) * 100, NULL),
             ((FII_PSI_BUDGET_C - FII_PSI_ENCUMBRANCES_C - FII_PSI_ACTUALS_C) / NULLIF(FII_PSI_BUDGET_C,0)) * 100) FII_PSI_PCNT_AVAIL_C,
        ((FII_PSI_PRIOR_BUDGET_C - FII_PSI_PRIOR_ENCUMBRANCES_C - FII_PSI_PRIOR_ACTUALS_C)
          / NULLIF(FII_PSI_PRIOR_BUDGET_C,0)) * 100 FII_PSI_PRIOR_PCNT_AVAIL_C,
        ((SUM(FII_PSI_BUDGET_C - FII_PSI_ENCUMBRANCES_C - FII_PSI_ACTUALS_C) OVER ()) /
           (NULLIF(SUM(FII_PSI_BUDGET_C) OVER (),0))) * 100 FII_PSI_GT_PCNT_AVAIL_C,
        ((SUM(FII_PSI_PRIOR_BUDGET_C - FII_PSI_PRIOR_ENCUMBRANCES_C - FII_PSI_PRIOR_ACTUALS_C) OVER ()) /
           (NULLIF(SUM(FII_PSI_PRIOR_BUDGET_C) OVER (),0))) * 100 FII_PSI_GT_PRIOR_PCNT_AVAIL_C,
        FII_PSI_BUDGET_C,
        FII_PSI_PRIOR_BUDGET_C,
        SUM(FII_PSI_BUDGET_C) OVER () FII_PSI_GT_BUDGET_C,
        SUM(FII_PSI_PRIOR_BUDGET_C) OVER () FII_PSI_GT_PRIOR_BUDGET_C,
        DECODE(:G_ID, f.VIEWBYID, DECODE(''' || l_if_leaf_flag || ''', ''Y'',
               FII_PSI_ENCUMBRANCES_C, NULL),
               FII_PSI_ENCUMBRANCES_C) FII_PSI_ENCUMBRANCES_C,
        FII_PSI_PRIOR_ENCUMBRANCES_C,
        SUM(FII_PSI_ENCUMBRANCES_C) OVER () FII_PSI_GT_ENCUMBRANCES_C,
        SUM(FII_PSI_PRIOR_ENCUMBRANCES_C) OVER () FII_PSI_GT_PRIOR_ENCUM_C,
        FII_PSI_COMMITTED_C_KPI,
        FII_PSI_PRIOR_COMMITTED_C,
        SUM(FII_PSI_COMMITTED_C_KPI) OVER () FII_PSI_GT_COMMITTED_C_KPI,
        SUM(FII_PSI_PRIOR_COMMITTED_C) OVER () FII_PSI_GT_PRIOR_COMMITTED_C,
        FII_PSI_OBLIGATED_C_KPI,
        FII_PSI_PRIOR_OBLIGATED_C,
        SUM(FII_PSI_OBLIGATED_C_KPI) OVER () FII_PSI_GT_OBLIGATED_C_KPI,
        SUM(FII_PSI_PRIOR_OBLIGATED_C) OVER () FII_PSI_GT_PRIOR_OBLIGATED_C,
        FII_PSI_OTHERS_C_KPI,
        FII_PSI_PRIOR_OTHERS_C,
        SUM(FII_PSI_OTHERS_C_KPI) OVER ()       FII_PSI_GT_OTHERS_C_KPI,
        SUM(FII_PSI_PRIOR_OTHERS_C) OVER ()       FII_PSI_GT_PRIOR_OTHERS_C,
        DECODE(:G_ID, f.VIEWBYID, DECODE(''' || l_if_leaf_flag || ''', ''Y'',
               FII_PSI_ACTUALS_C, NULL),
               FII_PSI_ACTUALS_C) FII_PSI_ACTUALS_C,
        FII_PSI_PRIOR_ACTUALS_C,
        SUM(FII_PSI_ACTUALS_C) OVER ()      FII_PSI_GT_ACTUALS_C,
        SUM(FII_PSI_PRIOR_ACTUALS_C) OVER ()      FII_PSI_GT_PRIOR_ACTUALS_C,
        FII_PSI_BUDGET_A,
        DECODE(:G_ID, f.VIEWBYID, DECODE(''' || l_if_leaf_flag || ''', ''Y'',
               FII_PSI_ENCUMBRANCES_A, NULL),
               FII_PSI_ENCUMBRANCES_A) FII_PSI_ENCUMBRANCES_A,
        DECODE(:G_ID, f.VIEWBYID, DECODE(''' || l_if_leaf_flag || ''', ''Y'',
               FII_PSI_ACTUALS_A, NULL),
               FII_PSI_ACTUALS_A) FII_PSI_ACTUALS_A,
        SUM(FII_PSI_BUDGET_A) OVER ()                      FII_PSI_GT_BUDGET_A,
        SUM(FII_PSI_ENCUMBRANCES_A) OVER ()                FII_PSI_GT_ENCUMBRANCES_A,
        SUM(FII_PSI_ACTUALS_A) OVER ()                     FII_PSI_GT_ACTUALS_A,
        DECODE((SELECT is_leaf_flag FROM fii_company_hierarchies
                WHERE parent_company_id = f.VIEWBYID and child_company_id = f.VIEWBYID), ''Y'', '''',
                ''pFunctionName=FII_PSI_FUNDS_AVAIL_SUM_C&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=FII_COMPANIES+FII_COMPANIES&pParamIds=Y'') FII_PSI_COMP_DRILL,
        DECODE((SELECT is_leaf_flag FROM fii_cost_ctr_hierarchies
                WHERE parent_cc_id = f.VIEWBYID and child_cc_id = f.VIEWBYID), ''Y'', '''',
                ''pFunctionName=FII_PSI_FUNDS_AVAIL_SUM_C&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ORGANIZATION+HRI_CL_ORGCC&pParamIds=Y'') FII_PSI_CC_DRILL,
        DECODE((SELECT is_leaf_flag FROM fii_fin_item_leaf_hiers
                WHERE parent_fin_cat_id = f.VIEWBYID and child_fin_cat_id = f.VIEWBYID), ''Y'',  '''',
                DECODE(:G_ID, f.VIEWBYID, '''',
                ''pFunctionName=FII_PSI_FUNDS_AVAIL_SUM_C&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=FINANCIAL ITEM+GL_FII_FIN_ITEM&pParamIds=Y'')) FII_PSI_CAT_DRILL,
        DECODE((SELECT  is_leaf_flag FROM fii_udd1_hierarchies
                WHERE parent_value_id = f.VIEWBYID and child_value_id = f.VIEWBYID), ''Y'', '''',
                DECODE(:G_ID, f.VIEWBYID, '''',
                ''pFunctionName=FII_PSI_FUNDS_AVAIL_SUM_C&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=FII_USER_DEFINED+FII_USER_DEFINED_1&pParamIds=Y'')) FII_PSI_PROJECT_DRILL,
        DECODE((SELECT  is_leaf_flag FROM fii_udd2_hierarchies
                WHERE parent_value_id = f.VIEWBYID and child_value_id = f.VIEWBYID), ''Y'', '''',
                ''pFunctionName=FII_PSI_FUNDS_AVAIL_SUM_C&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=FII_USER_DEFINED+FII_USER_DEFINED_2&pParamIds=Y'') FII_PSI_UD2_DRILL
FROM (';

Line: 341

'      SELECT /*+ index(f fii_gl_snap_sum_f_n1) */
		' || l_snap_aggrt_viewby_id || ' VIEWBYID,
		' || l_inner_sql_sys || '
       FROM	fii_gl_snap_sum_f_p_v f,
		' || l_fud2_from || '
		fii_pmv_aggrt_gt gt
      WHERE	f.parent_company_id = gt.parent_company_id
		AND   f.company_id = gt.company_id
		AND   f.parent_cost_center_id = gt.parent_cc_id
		AND   f.cost_center_id = gt.cc_id
		AND   f.parent_fin_category_id = gt.parent_fin_category_id
		AND   f.fin_category_id = gt.fin_category_id
		' || l_fud1_where || '
		' || l_fud2_where || '
      GROUP BY ' || l_snap_aggrt_viewby_id || ', gt.viewby, gt.sort_order';

Line: 375

'      SELECT /*+ index(f fii_gl_snap_f_n1) */
		' || l_non_aggrt_viewby_id || ' VIEWBYID,
		' || l_inner_sql_sys || '
       FROM	fii_gl_snap_f_p_v f,
		fii_company_hierarchies co_hier,
		fii_cost_ctr_hierarchies cc_hier,
		fii_fin_item_leaf_hiers fin_hier,
		' || l_fud1_from || '
		' || l_fud2_from || '
		fii_pmv_non_aggrt_gt gt
      WHERE	f.company_id = co_hier.child_company_id
		AND   co_hier.parent_company_id = gt.company_id
		AND   f.cost_center_id = cc_hier.child_cc_id
		AND   cc_hier.parent_cc_id = gt.cost_center_id
		AND   f.fin_category_id = fin_hier.child_fin_cat_id ' || l_cat_decode || '
		AND   fin_hier.parent_fin_cat_id = gt.fin_category_id
		' || l_fud1_where || '
		' || l_fud2_where || '
      GROUP BY ' || l_non_aggrt_viewby_id || ', gt.viewby, gt.sort_order';

Line: 491

'      SELECT   ' || l_aggrt_viewby_id || ' VIEWBYID,
		' || l_inner_sql_nonsys || '
       FROM	fii_gl_trend_sum_mv f,
		(SELECT /*+ NO_MERGE cardinality(gt 1) */ *
		 FROM	fii_time_structures cal,
			fii_pmv_aggrt_gt gt
		 WHERE report_date in (:BOUNDARY_END,:PRIOR_BOUNDARY_END, :ASOF_DATE,:PREVIOUS_ASOF_DATE)
			AND  (BITAND(cal.record_type_id, :AMOUNT_TYPE_BITAND) = :AMOUNT_TYPE_BITAND
			OR BITAND(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND)) inner_inline_view
      WHERE	f.time_id = inner_inline_view.time_id
		AND   f.period_type_id = inner_inline_view.period_type_id
		AND   f.parent_company_dim_id = inner_inline_view.parent_company_id
		AND   f.company_dim_id = inner_inline_view.company_id
		AND   f.parent_cost_center_dim_id = inner_inline_view.parent_cc_id
		AND   f.cost_center_dim_id = inner_inline_view.cc_id
		AND   f.parent_fin_category_dim_id = inner_inline_view.parent_fin_category_id
		AND   f.fin_category_dim_id = inner_inline_view.fin_category_id
      GROUP BY ' || l_aggrt_viewby_id || ', inner_inline_view.viewby, inner_inline_view.sort_order';

Line: 514

'      SELECT /*+ index(f fii_gl_agrt_sum_mv_n1) */
		' || l_aggrt_viewby_id || ' VIEWBYID,
		' || l_inner_sql_nonsys || '
       FROM	fii_gl_agrt_sum_mv_p_v f,
		' || l_fud2_from || '
		(SELECT /*+ NO_MERGE cardinality(gt 1) */ *
		 FROM	fii_time_structures cal,
			fii_pmv_aggrt_gt gt
		 WHERE report_date in (:BOUNDARY_END,:PRIOR_BOUNDARY_END, :ASOF_DATE,:PREVIOUS_ASOF_DATE)
			AND  (BITAND(cal.record_type_id, :AMOUNT_TYPE_BITAND) = :AMOUNT_TYPE_BITAND
			OR BITAND(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND)) inner_inline_view
      WHERE	f.time_id = inner_inline_view.time_id
		AND   f.period_type_id = inner_inline_view.period_type_id
		AND   f.parent_company_id = inner_inline_view.parent_company_id
		AND   f.company_id = inner_inline_view.company_id
		AND   f.parent_cost_center_id = inner_inline_view.parent_cc_id
		AND   f.cost_center_id = inner_inline_view.cc_id
		AND   f.parent_fin_category_id = inner_inline_view.parent_fin_category_id
		AND   f.fin_category_id = inner_inline_view.fin_category_id
		AND   f.parent_fud1_id = inner_inline_view.parent_fud1_id
		AND   f.fud1_id = inner_inline_view.fud1_id
		' || l_fud2_where || '
      GROUP BY ' || l_aggrt_viewby_id || ', inner_inline_view.viewby, inner_inline_view.sort_order';

Line: 545

      SELECT	/*+ index(f fii_gl_base_map_mv_n1) */ ' || l_non_aggrt_viewby_id || ' VIEWBYID,
		' || l_inner_sql_nonsys || '
      FROM	fii_gl_base_map_mv_p_v f,
		fii_company_hierarchies co_hier,
		fii_cost_ctr_hierarchies cc_hier,
		fii_fin_item_leaf_hiers fin_hier,
		fii_udd1_hierarchies fud1_hier,
		' || l_fud2_from || '
		(SELECT /*+ NO_MERGE cardinality(gt 1) */ *
		 FROM	fii_time_structures cal,
			fii_pmv_non_aggrt_gt gt
		 WHERE report_date in (:BOUNDARY_END, :PRIOR_BOUNDARY_END, :ASOF_DATE, :PREVIOUS_ASOF_DATE)
			AND  (BITAND(cal.record_type_id, :AMOUNT_TYPE_BITAND) = :AMOUNT_TYPE_BITAND
			OR BITAND(cal.record_type_id, :ACTUAL_BITAND) = :ACTUAL_BITAND)) inner_inline_view
      WHERE	f.time_id = inner_inline_view.time_id
		AND   f.period_type_id = inner_inline_view.period_type_id
		AND   f.company_id = co_hier.child_company_id
		AND   co_hier.parent_company_id = inner_inline_view.company_id
		AND   f.cost_center_id = cc_hier.child_cc_id
		AND   cc_hier.parent_cc_id = inner_inline_view.cost_center_id
		AND   f.fin_category_id = fin_hier.child_fin_cat_id ' || l_cat_decode || '
		AND   fin_hier.parent_fin_cat_id = inner_inline_view.fin_category_id
		AND   f.fud1_id = fud1_hier.child_value_id ' || l_fud1_decode || '
		AND   fud1_hier.parent_value_id = inner_inline_view.fud1_id
		' || l_fud2_where || '
      GROUP BY ' || l_non_aggrt_viewby_id || ', inner_inline_view.viewby, inner_inline_view.sort_order';

Line: 595

sqlstmt_temp := l_insert_sql_b ||
           l_outer_sql_b || l_inner_sql_nonsys_agg
                         || l_union_sql
                         || l_inner_sql_nonsys_nonagg
           || l_outer_sql_e;

Line: 612

'SELECT VIEWBY,
	VIEWBYID,
	FII_PSI_AVAIL_C,
	FII_PSI_PRIOR_AVAIL_C,
	FII_PSI_GT_AVAIL_C,
	FII_PSI_GT_PRIOR_AVAIL_C,
	FII_PSI_PCNT_AVAIL_C,
	FII_PSI_PRIOR_PCNT_AVAIL_C,
	FII_PSI_GT_PCNT_AVAIL_C,
	FII_PSI_GT_PRIOR_PCNT_AVAIL_C,
	FII_PSI_BUDGET_C,
	FII_PSI_PRIOR_BUDGET_C,
	FII_PSI_GT_BUDGET_C,
	FII_PSI_GT_PRIOR_BUDGET_C,
	FII_PSI_ENCUMBRANCES_C,
	FII_PSI_PRIOR_ENCUMBRANCES_C,
	FII_PSI_GT_ENCUMBRANCES_C,
	FII_PSI_GT_PRIOR_ENCUM_C,
	FII_PSI_COMMITTED_C_KPI,
	FII_PSI_PRIOR_COMMITTED_C,
	FII_PSI_GT_COMMITTED_C_KPI,
	FII_PSI_GT_PRIOR_COMMITTED_C,
	FII_PSI_OBLIGATED_C_KPI,
	FII_PSI_PRIOR_OBLIGATED_C,
	FII_PSI_GT_OBLIGATED_C_KPI,
	FII_PSI_GT_PRIOR_OBLIGATED_C,
	FII_PSI_OTHERS_C_KPI,
	FII_PSI_PRIOR_OTHERS_C,
	FII_PSI_GT_OTHERS_C_KPI,
	FII_PSI_GT_PRIOR_OTHERS_C,
	FII_PSI_ACTUALS_C,
	FII_PSI_PRIOR_ACTUALS_C,
	FII_PSI_GT_ACTUALS_C,
	FII_PSI_GT_PRIOR_ACTUALS_C,
	FII_PSI_BUDGET_A,
	FII_PSI_ENCUMBRANCES_A,
	FII_PSI_ACTUALS_A,
	FII_PSI_GT_BUDGET_A,
	FII_PSI_GT_ENCUMBRANCES_A,
	FII_PSI_GT_ACTUALS_A,
	FII_PSI_COMP_DRILL,
	FII_PSI_CC_DRILL,
	FII_PSI_CAT_DRILL,
	FII_PSI_PROJECT_DRILL,
	FII_PSI_UD2_DRILL
FROM	FII_PSI_PMV_GT
ORDER BY NVL(SORT_ORDER, 999999) asc,
         NVL(FII_PSI_BUDGET_C, -999999) desc,
         NVL(VIEWBY, 999999) asc';