[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select total_headcount+1 into fii_gl_util_pkg.g_total_hc
from hri_mdp_sup_wmv_sup_mv
where supervisor_person_id = fii_gl_util_pkg.g_mgr_id
and effective_start_date = (SELECT max(aa.effective_start_date)
FROM hri_mdp_sup_wmv_sup_mv aa
WHERE aa.supervisor_person_id = fii_gl_util_pkg.g_mgr_id
AND aa.effective_start_date <= decode(l_shared_hr_flag, 'N', fii_gl_util_pkg.g_as_of_date, sysdate));
select total_headcount+1 into l_total_prior_hc
from hri_mdp_sup_wmv_sup_mv
where supervisor_person_id = fii_gl_util_pkg.g_mgr_id
and effective_start_date = (SELECT max(aa.effective_start_date)
FROM hri_mdp_sup_wmv_sup_mv aa
WHERE aa.supervisor_person_id = fii_gl_util_pkg.g_mgr_id
AND aa.effective_start_date <= decode(l_shared_hr_flag, 'N', fii_gl_util_pkg.g_previous_asof_date, sysdate));
sqlstmt := 'SELECT NULL VIEWBY,
NULL VIEWBYID,
NULL FII_MEASURE1,
NULL FII_MEASURE9,
NULL FII_MEASURE8,
NULL FII_MEASURE10,
NULL FII_MEASURE2,
NULL FII_MEASURE3,
NULL FII_MEASURE12,
NULL FII_ATTRIBUTE10,
NULL FII_ATTRIBUTE11,
NULL FII_ATTRIBUTE12,
NULL FII_MEASURE14,
NULL FII_MEASURE5
FROM DUAL
WHERE 1=2';
SELECT
decode(:MGR_ID, f.viewby_id, decode(:DIM_FLAG,''Y'','||fii_gl_util_pkg.g_viewby_value||', '||fii_gl_util_pkg.g_viewby_value||'||'''||' '||'''||:DIR_MSG), '||fii_gl_util_pkg.g_viewby_value||') VIEWBY,
f.viewby_id VIEWBYID,
CY_XTD FII_MEASURE1,
'||l_prior_exp||'
case when f.VIEWBY_ID = &HRI_PERSON+HRI_PER_USRDR_H
then :TOTAL_HC+1-sum(f.headcnt) over()
else f.headcnt
end FII_MEASURE8,
'||l_prior_hc||'
case when f.VIEWBY_ID = &HRI_PERSON+HRI_PER_USRDR_H
then :TOTAL_HC+1-sum(f.headcnt) over()
else f.headcnt
end FII_MEASURE2,
CY_XTD /nullif((case when f.VIEWBY_ID = &HRI_PERSON+HRI_PER_USRDR_H
then :TOTAL_HC+1-sum(f.headcnt) over()
else f.headcnt end), 0) FII_MEASURE3,
sum(CY_XTD) over() FII_MEASURE12, -- Added for bug#2955837,
'||l_prior_gt_exp||'
:TOTAL_HC FII_ATTRIBUTE11,
'||l_prior_gt_hc||' -- Added for bug#2955837
decode(NVL(:MGR_ID, -9999), f.viewby_id, '''', ''pFunctionName=FII_GL_EXP_PER_EMP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=VIEW_BY&pParamIds=Y'') FII_MEASURE14,
sum(CY_XTD) over() /
NULLIF(:TOTAL_HC,0) FII_MEASURE5
FROM (
SELECT /*+ NO_MERGE */ viewby_id VIEWBY_ID,
sum(cy_xtd) CY_XTD,
sum(py_xtd) PY_XTD,
sum(headcnt) HEADCNT,
sum(py_headcnt) PY_HEADCNT
FROM (
SELECT mgr.emp_id viewby_id,
NVL(cnt.total_headcount,0) HEADCNT,
to_number(NULL) PY_HEADCNT,
to_number(NULL) CY_XTD,
to_number(NULL) PY_XTD
FROM fii_cc_mgr_hierarchies mgr,
hri_mdp_sup_wmv_sup_mv cnt
WHERE mgr.mgr_id = &HRI_PERSON+HRI_PER_USRDR_H
AND mgr.emp_level = mgr.mgr_level + 1
AND cnt.supervisor_person_id = mgr.emp_id
AND cnt.effective_start_date = (SELECT /*+ no_unnest*/ MAX(cnt2.effective_start_date)
FROM hri_mdp_sup_wmv_sup_mv cnt2
WHERE cnt.supervisor_person_id = cnt2.supervisor_person_id
AND cnt2.effective_start_date <= decode('''||l_shared_hr_flag||''', ''N'', &BIS_CURRENT_ASOF_DATE, sysdate))
AND cnt.total_headcount > 0
UNION ALL
SELECT mgr.emp_id viewby_id,
to_number(NULL) HEADCNT,
NVL(cnt.total_headcount,0) PY_HEADCNT,
to_number(NULL) CY_XTD,
to_number(NULL) PY_XTD
FROM fii_cc_mgr_hierarchies mgr,
hri_mdp_sup_wmv_sup_mv cnt
WHERE mgr.mgr_id = &HRI_PERSON+HRI_PER_USRDR_H
AND mgr.emp_level = mgr.mgr_level + 1
AND cnt.supervisor_person_id = mgr.emp_id
AND cnt.effective_start_date = (SELECT /*+ no_unnest*/ MAX(cnt2.effective_start_date)
FROM hri_mdp_sup_wmv_sup_mv cnt2
WHERE cnt.supervisor_person_id = cnt2.supervisor_person_id
AND cnt2.effective_start_date <= decode('''||l_shared_hr_flag||''', ''N'', &BIS_PREVIOUS_ASOF_DATE, sysdate))
AND cnt.total_headcount > 0
UNION ALL
SELECT mgr.EMP_ID viewby_id,
1 HEADCNT,
1 PY_HEADCNT,
to_number(NULL) CY_XTD,
to_number(NULL) PY_XTD
FROM fii_cc_mgr_hierarchies mgr
WHERE mgr.mgr_id = &HRI_PERSON+HRI_PER_USRDR_H
AND mgr.emp_level <= mgr.mgr_level + 1
UNION ALL
SELECT f.person_id viewby_id,
to_number(NULL) HEADCNT,
to_number(NULL) PY_HEADCNT,
sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE
then f.actual_g else to_number(null) end) CY_XTD,
sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
then f.actual_g else to_number(null) end) PY_XTD
FROM fii_time_rpt_struct cal'||fii_gl_util_pkg.g_view||'
WHERE 1=1'||fii_gl_util_pkg.g_mgr_join||fii_gl_util_pkg.g_cat_join||fii_gl_util_pkg.g_gid||'
AND f.time_id = cal.time_id
AND f.period_type_id = cal.period_type_id
AND bitand(cal.record_type_id, :ACTUAL_PERIOD_TYPE)= cal.record_type_id
AND cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
GROUP BY f.person_id
) h
GROUP BY VIEWBY_ID
) f, '||fii_gl_util_pkg.g_viewby_from_clause||'
WHERE '||fii_gl_util_pkg.g_viewby_join||'
ORDER BY NVL(FII_MEASURE3, -9999999999) desc';