[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT dbi_hier_top_node_id INTO l_child_cc_id
FROM fii_financial_dimensions
WHERE DIMENSION_SHORT_NAME = 'HRI_CL_ORGCC';
SELECT dbi_hier_top_node_id INTO l_child_company_id
FROM fii_financial_dimensions
WHERE DIMENSION_SHORT_NAME = 'FII_COMPANIES';
SELECT SEQUENCE INTO l_months
FROM fii_time_ent_period WHERE l_as_of_date BETWEEN start_date AND end_date;
SELECT ENT_YEAR_ID, SEQUENCE INTO l_year_id, l_sequence
FROM fii_time_ent_qtr WHERE l_as_of_date BETWEEN start_date AND end_date;
SELECT SEQUENCE INTO l_months
FROM fii_time_ent_period WHERE l_as_of_date BETWEEN start_date AND end_date;
SELECT ent_year_id, sequence INTO l_year_id, l_sequence
FROM fii_time_ent_period WHERE l_as_of_date BETWEEN start_date AND end_date;
SELECT sequence, ent_year_id INTO l_months, l_year_id
FROM fii_time_ent_period WHERE l_as_of_date BETWEEN start_date AND end_date;
SELECT
f.company_id FII_EA_COL_COMPANY_ID,
f.cost_center_id FII_EA_COL_COST_CENTER_ID,
f.fin_category_id FII_EA_COL_FIN_CAT_ID,
TO_NUMBER(NULL) FII_EA_XTD,
SUM(CASE WHEN ('||p_hist_amt1||'+0) = 0 THEN TO_NUMBER(NULL)
ELSE ('||p_hist_amt1||'+0) END) FII_EA_HIST_COL1,
SUM(CASE WHEN ('||p_hist_amt2||'+0) = 0 THEN TO_NUMBER(NULL)
ELSE ('||p_hist_amt2||'+0) END) FII_EA_HIST_COL2,
SUM(CASE WHEN ('||p_hist_amt3||'+0) = 0 THEN TO_NUMBER(NULL)
ELSE ('||p_hist_amt3||'+0) END) FII_EA_HIST_COL3,
TO_NUMBER(NULL) FII_EA_HIST_COL4
FROM
fii_gl_local_snap_f'||l_curr_view||' f
'||l_company_id_from||l_fin_category_id_from||'
WHERE f.amount_type_code = ''A''
AND f.year_id = :PRIOR_YEAR_ID
AND f.fin_cat_type_code = '''||p_fin_cat||'''
'||l_company_id_where||l_fin_category_id_where||l_fud1_where||l_fud2_where||'
'||l_ledger_where||'
GROUP BY
f.company_id,
f.cost_center_id,
f.fin_category_id ';
SELECT
FII_EA_COL_COMPANY,
FII_EA_COL_COMPANY_ID,
FII_EA_COL_COST_CENTER,
FII_EA_COL_COST_CENTER_ID,
FII_EA_COL_FIN_CAT,
FII_EA_COL_FIN_CAT_ID,
FII_EA_COL_FIN_CAT_DESP,
FII_EA_XTD,
FII_EA_HIST_COL1,
FII_EA_HIST_COL2,
FII_EA_HIST_COL3,
FII_EA_HIST_COL4,
FII_EA_GT_XTD,
FII_EA_GT_HIST_COL1,
FII_EA_GT_HIST_COL2,
FII_EA_GT_HIST_COL3,
FII_EA_GT_HIST_COL4,
DECODE(FII_EA_XTD, NULL, NULL, '''||l_url1||''') FII_EA_XTD_DRILL,
DECODE(FII_EA_HIST_COL1, NULL, NULL, '''||l_url2||''') FII_EA_HIST_COL1_DRILL,
DECODE(FII_EA_HIST_COL2, NULL, NULL, '''||l_url3||''') FII_EA_HIST_COL2_DRILL,
DECODE(FII_EA_HIST_COL3, NULL, NULL, '''||l_url4||''') FII_EA_HIST_COL3_DRILL,
DECODE(FII_EA_HIST_COL4, NULL, NULL, '''||l_url5||''') FII_EA_HIST_COL4_DRILL
FROM (
SELECT
com_flex.FLEX_VALUE_MEANING FII_EA_COL_COMPANY,
FII_EA_COL_COMPANY_ID,
cc_flex.FLEX_VALUE_MEANING FII_EA_COL_COST_CENTER,
FII_EA_COL_COST_CENTER_ID,
fin_flex.FLEX_VALUE_MEANING FII_EA_COL_FIN_CAT,
FII_EA_COL_FIN_CAT_ID,
com_flex.DESCRIPTION||''.''||cc_flex.DESCRIPTION||''.''||fin_flex.DESCRIPTION
FII_EA_COL_FIN_CAT_DESP,
sum(FII_EA_XTD) FII_EA_XTD,
sum(FII_EA_HIST_COL1) FII_EA_HIST_COL1,
sum(FII_EA_HIST_COL2) FII_EA_HIST_COL2,
sum(FII_EA_HIST_COL3) FII_EA_HIST_COL3,
sum(FII_EA_HIST_COL4) FII_EA_HIST_COL4,
sum(sum(FII_EA_XTD)) over() FII_EA_GT_XTD,
sum(sum(FII_EA_HIST_COL1)) over() FII_EA_GT_HIST_COL1,
sum(sum(FII_EA_HIST_COL2)) over() FII_EA_GT_HIST_COL2,
sum(sum(FII_EA_HIST_COL3)) over() FII_EA_GT_HIST_COL3,
sum(sum(FII_EA_HIST_COL4)) over() FII_EA_GT_HIST_COL4,
(rank () OVER (ORDER BY '||l_order2||' nulls last )) - 1 rnk
FROM
(SELECT
f.company_id FII_EA_COL_COMPANY_ID,
f.cost_center_id FII_EA_COL_COST_CENTER_ID,
f.fin_category_id FII_EA_COL_FIN_CAT_ID,
SUM(CASE WHEN ('||xtd||')=0 THEN TO_NUMBER(NULL)
ELSE ('||xtd||') END) FII_EA_XTD,
SUM(CASE WHEN ('||hist_amt1||'+0) = 0 THEN TO_NUMBER(NULL)
ELSE ('||hist_amt1||'+0) END) FII_EA_HIST_COL1,
SUM(CASE WHEN ('||hist_amt2||'+0) = 0 THEN TO_NUMBER(NULL)
ELSE ('||hist_amt2||'+0) END) FII_EA_HIST_COL2,
SUM(CASE WHEN ('||hist_amt3||'+0) = 0 THEN TO_NUMBER(NULL)
ELSE ('||hist_amt3||'+0) END) FII_EA_HIST_COL3,
SUM(CASE WHEN ('||hist_amt4||'+0) = 0 THEN TO_NUMBER(NULL)
ELSE ('||hist_amt4||'+0) END) FII_EA_HIST_COL4
FROM
fii_gl_local_snap_f'||l_curr_view||' f
'||l_company_id_from||l_fin_category_id_from||'
WHERE f.amount_type_code = ''A''
AND f.year_id = :YEAR_ID
AND f.fin_cat_type_code = '''||p_fin_cat||'''
'||l_company_id_where||l_fin_category_id_where||l_fud1_where||l_fud2_where||'
'||l_ledger_where||'
GROUP BY f.company_id,
f.cost_center_id,
f.fin_category_id
'|| l_prior_year_sqlstmt||' )
,fnd_flex_values_tl com_flex
,fnd_flex_values_tl cc_flex
,fnd_flex_values_tl fin_flex
WHERE FII_EA_COL_COMPANY_ID = com_flex.flex_value_id
AND com_flex.language = userenv(''LANG'')
AND FII_EA_COL_COST_CENTER_ID = cc_flex.flex_value_id
AND cc_flex.language = userenv(''LANG'')
AND FII_EA_COL_FIN_CAT_ID = fin_flex.flex_value_id
AND fin_flex.language = userenv(''LANG'')
group by
com_flex.flex_value_meaning,
FII_EA_COL_COMPANY_ID,
cc_flex.flex_value_meaning,
FII_EA_COL_COST_CENTER_ID,
fin_flex.flex_value_meaning,
FII_EA_COL_FIN_CAT_ID,
com_flex.DESCRIPTION||''.''||cc_flex.DESCRIPTION||''.''||fin_flex.DESCRIPTION
'||l_having_clause||'
&ORDER_BY_CLAUSE nulls last)
WHERE ((rnk between &START_INDEX and &END_INDEX) or (&END_INDEX = -1))';