APPS.FII_PSI_ENCUM_SUM_PKG SQL Statements

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

Line: 76

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

Line: 118

		SELECT  inline_view.viewby VIEWBY,
			inline_view.viewby_id VIEWBYID,
			SUM(FII_PSI_XTD) FII_PSI_XTD,
			SUM(FII_PSI_COMMITMENTS) FII_PSI_COMMITMENTS,
			SUM(FII_PSI_OBLIGATIONS) FII_PSI_OBLIGATIONS,
			SUM(FII_PSI_OTHER) FII_PSI_OTHER,

			SUM(FII_PSI_HIST_COL1) FII_PSI_HIST_COL1,
			SUM(FII_PSI_HIST_COL2) FII_PSI_HIST_COL2,
			SUM(FII_PSI_HIST_COL3) FII_PSI_HIST_COL3,
			SUM(FII_PSI_HIST_COL4) FII_PSI_HIST_COL4,
                        DECODE(SUM(FII_PSI_XTD), 0, NULL, NULL, NULL, '''|| l_xtd_drill_url||''') FII_PSI_XTD_DRILL,
			DECODE((SELECT  is_leaf_flag
				FROM	fii_company_hierarchies
				WHERE	parent_company_id = inline_view.viewby_id
					and child_company_id = inline_view.viewby_id),
					''Y'',
					'''',
				''pFunctionName=FII_PSI_ENCUM_SUM&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 = inline_view.viewby_id
					and child_cc_id = inline_view.viewby_id),
					''Y'',
					'''',
					''pFunctionName=FII_PSI_ENCUM_SUM&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 = inline_view.viewby_id
					and child_fin_cat_id = inline_view.viewby_id),
					''Y'',
					'''',
				-- Additional DECODE added for bug 4190997
					DECODE(:G_ID, inline_view.viewby_id,'''',
					''pFunctionName=FII_PSI_ENCUM_SUM&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 = inline_view.viewby_id
					and child_value_id = inline_view.viewby_id),
					''Y'',
					'''',
				-- Additional DECODE added for bug 4190997
					DECODE(:G_ID, inline_view.viewby_id,'''',
					''pFunctionName=FII_PSI_ENCUM_SUM&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 = inline_view.viewby_id
					and child_value_id = inline_view.viewby_id),
					''Y'',
					'''',
					''pFunctionName=FII_PSI_ENCUM_SUM&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=FII_USER_DEFINED+FII_USER_DEFINED_2&pParamIds=Y'') FII_PSI_UD2_DRILL,

			SUM(SUM(FII_PSI_COMMITMENTS)) OVER () FII_PSI_GT_COMMITMENTS,
			SUM(SUM(FII_PSI_OBLIGATIONS)) OVER () FII_PSI_GT_OBLIGATIONS,
			SUM(SUM(FII_PSI_OTHER)) OVER () FII_PSI_GT_OTHER,
			SUM(SUM(FII_PSI_XTD)) OVER () FII_PSI_GT_XTD,
			SUM(SUM(FII_PSI_HIST_COL1)) OVER ()  FII_PSI_GT_HIST_COL1,
			SUM(SUM(FII_PSI_HIST_COL2)) OVER ()  FII_PSI_GT_HIST_COL2,
			SUM(SUM(FII_PSI_HIST_COL3)) OVER ()  FII_PSI_GT_HIST_COL3,
			SUM(SUM(FII_PSI_HIST_COL4)) OVER () FII_PSI_GT_HIST_COL4

		FROM
		( ';

Line: 184

			-- This part of the query gets executed if the nodes selected are aggregated nodes
			SELECT /*+ index(f fii_gl_agrt_sum_mv_n1) */
				'||l_aggrt_viewby_id||' viewby_id, inner_inline_view.viewby viewby, inner_inline_view.sort_order sort_order,

				SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.commitments_g  ELSE NULL END) ) ) FII_PSI_COMMITMENTS,
				SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.obligations_g  ELSE NULL END) ) ) FII_PSI_OBLIGATIONS,
				SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.others_g  ELSE NULL END) ) ) FII_PSI_OTHER,
				SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.commitments_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.obligations_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.others_g  ELSE NULL END) ) ) FII_PSI_XTD,

				SUM(DECODE(inner_inline_view.report_date,   :PREVIOUS_THREE_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date,   :PREVIOUS_THREE_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date,   :PREVIOUS_THREE_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g  ELSE NULL END) ) ) FII_PSI_HIST_COL1,
				SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_TWO_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_TWO_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_TWO_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g  ELSE NULL END) ) ) FII_PSI_HIST_COL2,
				SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g  ELSE NULL END) ) ) FII_PSI_HIST_COL3,
				SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g  ELSE NULL END) ) ) FII_PSI_HIST_COL4

			FROM	fii_gl_agrt_sum_mv_p_v f,
  				'||l_fud2_from||'
				(
				-- This part of the query joins the fii_time_structures with fii_pmv_aggrt_gt
				SELECT /*+ NO_MERGE cardinality(gt 1) */ *
				  FROM fii_time_structures cal, fii_pmv_aggrt_gt gt
					WHERE report_date IN (
						:PREVIOUS_ONE_END_DATE,
						:PREVIOUS_TWO_END_DATE,
						:PREVIOUS_THREE_END_DATE,
						:BUD_ASOF_DATE
						)
						AND (
						BITAND(cal.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
						OR
						BITAND(cal.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_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: 260

			--This part of the query gets executed if the nodes selected are non aggregated nodes
			SELECT /*+ index(f fii_gl_base_map_mv_n1) */ '||l_nonaggrt_viewby_id||' viewby_id, inner_inline_view.viewby viewby, inner_inline_view.sort_order sort_order,

				SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.commitments_g  ELSE NULL END) ) ) FII_PSI_COMMITMENTS,
				SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.obligations_g  ELSE NULL END) ) ) FII_PSI_OBLIGATIONS,
				SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.others_g  ELSE NULL END) ) ) FII_PSI_OTHER,
				SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.commitments_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.obligations_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.others_g  ELSE NULL END) ) ) FII_PSI_XTD,

				SUM(DECODE(inner_inline_view.report_date,   :PREVIOUS_THREE_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date,   :PREVIOUS_THREE_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date,   :PREVIOUS_THREE_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g  ELSE NULL END) ) ) FII_PSI_HIST_COL1,
				SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_TWO_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_TWO_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_TWO_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g  ELSE NULL END) ) ) FII_PSI_HIST_COL2,
				SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g  ELSE NULL END) ) ) FII_PSI_HIST_COL3,
				SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g  ELSE NULL END) ) ) FII_PSI_HIST_COL4

			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||'
				(
				-- This part of the query joins the fii_time_structures with fii_pmv_non_aggrt_gt
				SELECT /*+ NO_MERGE cardinality(gt 1) */ *
				FROM fii_time_structures cal, fii_pmv_non_aggrt_gt gt
				WHERE report_date IN (
					:PREVIOUS_ONE_END_DATE,
					:PREVIOUS_TWO_END_DATE,
					:PREVIOUS_THREE_END_DATE,
					:BUD_ASOF_DATE
					)
					AND
					(
					BITAND(cal.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
					OR
					BITAND(cal.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_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 f.cost_center_id = cc_hier.child_cc_id
             	AND f.fin_category_id = fin_hier.child_fin_cat_id
				AND f.fud1_id = fud1_hier.child_value_id
				AND co_hier.parent_company_id = inner_inline_view.company_id
                AND cc_hier.parent_cc_id = inner_inline_view.cost_center_id
                AND fin_hier.parent_fin_cat_id = inner_inline_view.fin_category_id
                AND fud1_hier.parent_value_id = inner_inline_view.fud1_id
 		'||l_fud2_where||'
			GROUP BY '||l_nonaggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order	';

Line: 341

			SELECT '||l_aggrt_viewby_id||' viewby_id,
				inner_inline_view.viewby viewby,
				inner_inline_view.sort_order sort_order,
				SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.commitments_g  ELSE NULL END) ) ) FII_PSI_COMMITMENTS,
				SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.obligations_g  ELSE NULL END) ) ) FII_PSI_OBLIGATIONS,
				SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.others_g  ELSE NULL END) ) ) FII_PSI_OTHER,
				SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.commitments_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.obligations_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND THEN f.others_g  ELSE NULL END) ) ) FII_PSI_XTD,

				SUM(DECODE(inner_inline_view.report_date,   :PREVIOUS_THREE_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date,   :PREVIOUS_THREE_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date,   :PREVIOUS_THREE_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g  ELSE NULL END) ) ) FII_PSI_HIST_COL1,
				SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_TWO_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_TWO_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_TWO_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g  ELSE NULL END) ) ) FII_PSI_HIST_COL2,
				SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :PREVIOUS_ONE_END_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g  ELSE NULL END) ) ) FII_PSI_HIST_COL3,
				SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.commitments_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.obligations_g  ELSE NULL END) ) )
				+ SUM(DECODE(inner_inline_view.report_date, :BUD_ASOF_DATE,
					(CASE WHEN BITAND(inner_inline_view.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_ACTUAL_BITAND THEN f.others_g  ELSE NULL END) ) ) FII_PSI_HIST_COL4

			FROM	fii_gl_trend_sum_mv_p_v f,
				(
				-- This part of the query joins the fii_time_structures with fii_pmv_aggrt_gt
				SELECT /*+ NO_MERGE cardinality(gt 1) */ *
				  FROM fii_time_structures cal, fii_pmv_aggrt_gt gt
				 WHERE report_date IN (
						:PREVIOUS_ONE_END_DATE,
						:PREVIOUS_TWO_END_DATE,
						:PREVIOUS_THREE_END_DATE,
						:BUD_ASOF_DATE
						)
						AND (
						BITAND(cal.record_type_id,:ACTUAL_BITAND) = :ACTUAL_BITAND
						OR
						BITAND(cal.record_type_id,:HIST_ACTUAL_BITAND) = :HIST_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

			GROUP BY '||l_aggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order ';

Line: 522

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

Line: 564

		SELECT  inline_view.viewby VIEWBY,
			inline_view.viewby_id VIEWBYID,
			SUM(FII_PSI_XTD) FII_PSI_XTD,
			SUM(FII_PSI_COMMITMENTS) FII_PSI_COMMITMENTS,
			SUM(FII_PSI_OBLIGATIONS) FII_PSI_OBLIGATIONS,
			SUM(FII_PSI_OTHER) FII_PSI_OTHER,
                        DECODE(SUM(FII_PSI_XTD), 0, NULL, NULL, NULL, '''|| l_xtd_drill_url||''') FII_PSI_XTD_DRILL,
			DECODE((SELECT  is_leaf_flag
				FROM	fii_company_hierarchies
				WHERE	parent_company_id = inline_view.viewby_id
					AND child_company_id = inline_view.viewby_id),
					''Y'',
					'''',
				''pFunctionName=FII_PSI_ENCUM_SUM&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=FII_COMPANIES+FII_COMPANIES&pParamIds=Y'') FII_PSI_COMP_DRILL,
			SUM(SUM(FII_PSI_COMMITMENTS)) OVER () FII_PSI_GT_COMMITMENTS,
			SUM(SUM(FII_PSI_OBLIGATIONS)) OVER () FII_PSI_GT_OBLIGATIONS,
			SUM(SUM(FII_PSI_OTHER)) OVER () FII_PSI_GT_OTHER,
			SUM(SUM(FII_PSI_XTD)) OVER () FII_PSI_GT_XTD

		FROM
		( ';

Line: 587

			-- This part of the query gets executed if the nodes selected are aggregated nodes

			SELECT /*+ index(f fii_gl_agrt_sum_mv_n1) */
				'||l_aggrt_viewby_id||' viewby_id,
				inner_inline_view.viewby viewby,
				inner_inline_view.sort_order sort_order,
				SUM(f.commitments_g) FII_PSI_COMMITMENTS,
				SUM(f.obligations_g) FII_PSI_OBLIGATIONS,
				SUM(f.others_g) FII_PSI_OTHER,
				SUM(f.commitments_g) + SUM(f.obligations_g) + SUM(f.others_g) FII_PSI_XTD

			FROM	fii_gl_agrt_sum_mv_p_v f,
				'||l_fud2_from||'
				(
				-- This part of the query joins the fii_time_structures with fii_pmv_aggrt_gt
				SELECT /*+ NO_MERGE cardinality(gt 1) */ *
				  FROM fii_time_structures cal, fii_pmv_aggrt_gt gt
				 WHERE report_date = :BUD_ASOF_DATE
 					      AND (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: 623

			--This part of the query gets executed if the nodes selected are non aggregated nodes

			SELECT /*+ index(f fii_gl_base_map_mv_n1) */
				'||l_nonaggrt_viewby_id||' viewby_id,
				inner_inline_view.viewby viewby,
				inner_inline_view.sort_order sort_order,
				SUM(f.commitments_g) FII_PSI_COMMITMENTS,
				SUM(f.obligations_g) FII_PSI_OBLIGATIONS,
				SUM(f.others_g) FII_PSI_OTHER,
				SUM(f.commitments_g) + SUM(f.obligations_g) + SUM(f.others_g) FII_PSI_XTD

			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||'
				(
				-- This part of the query joins the fii_time_structures with fii_pmv_non_aggrt_gt
				SELECT /*+ NO_MERGE cardinality(gt 1) */ *
				FROM fii_time_structures cal, fii_pmv_non_aggrt_gt gt
				WHERE report_date = :BUD_ASOF_DATE
					AND ( 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 f.cost_center_id = cc_hier.child_cc_id
             			AND f.fin_category_id = fin_hier.child_fin_cat_id
				AND f.fud1_id = fud1_hier.child_value_id
				AND co_hier.parent_company_id = inner_inline_view.company_id
				AND cc_hier.parent_cc_id = inner_inline_view.cost_center_id
				AND fin_hier.parent_fin_cat_id = inner_inline_view.fin_category_id
				AND fud1_hier.parent_value_id = inner_inline_view.fud1_id
 				'||l_fud2_where||'

			GROUP BY '||l_nonaggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order ';

Line: 665

			SELECT '||l_aggrt_viewby_id||' viewby_id,
				inner_inline_view.viewby viewby,
				inner_inline_view.sort_order sort_order,
				SUM(f.commitments_g) FII_PSI_COMMITMENTS,
				SUM(f.obligations_g) FII_PSI_OBLIGATIONS,
				SUM(f.others_g) FII_PSI_OTHER,
				SUM(f.commitments_g) + SUM(f.obligations_g) + SUM(f.others_g) FII_PSI_XTD

			FROM	fii_gl_trend_sum_mv_p_v f,
				(
				-- This part of the query joins the fii_time_structures with fii_pmv_aggrt_gt
				SELECT	/*+ NO_MERGE cardinality(gt 1) */ *
				FROM	fii_time_structures cal,
					fii_pmv_aggrt_gt gt
				WHERE	report_date = :BUD_ASOF_DATE
					AND ( 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

			GROUP BY '||l_aggrt_viewby_id||', inner_inline_view.viewby, inner_inline_view.sort_order ';