[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'SELECT --Labor Cost with Headcount Trend
hc.period_as_of_date VIEWBYID
,hc.period_as_of_date VIEWBY
,hc.period_order HRI_P_ORDER_BY_1
,hc.period_as_of_date HRI_P_GRAPH_X_LABEL_TIME
,x.budgeted_amount HRI_P_MEASURE1
,x.actual_amount + x.committed_amount HRI_P_MEASURE2
,hc.budgeted HRI_P_MEASURE3
,hc.actual HRI_P_MEASURE4
,to_char(hc.period_as_of_date,''DD/MM/YYYY'') HRI_P_CHAR1_GA
FROM
(
SELECT b.period_as_of_date,
b.period_order,
b.budgeted_amount,
a.actual_amount,
a.committed_amount
FROM
(
SELECT prds.period_as_of_date
,prds.period_order
,hri_oltp_view_currency.convert_currency_amount
(bgt.CURRENCY_CODE,
'''||l_currency||''',
&BIS_CURRENT_ASOF_DATE,
SUM(BUDGET_VALUE),
'''||l_rateType||''') budgeted_amount
FROM ' || l_trend_table || ' prds,
HRI_MDP_BDGTS_LBRCST_MV bgt
WHERE bgt.ORGMGR_ID(+) = &HRI_PERSON+HRI_PER_USRDR_H
AND prds.period_start_date <= bgt.EFFECTIVE_END_DATE(+)
AND prds.period_end_date >= bgt.EFFECTIVE_START_DATE(+)
GROUP BY prds.period_as_of_date,
prds.period_order,
bgt.CURRENCY_CODE ) b,
(
SELECT prds.period_as_of_date
,prds.period_order
,hri_oltp_view_currency.convert_currency_amount
(act.CURRENCY_CODE,
'''||l_currency||''',
&BIS_CURRENT_ASOF_DATE,
sum(act.ACTUAL_VALUE),
'''||l_rateType||''') actual_amount
,hri_oltp_view_currency.convert_currency_amount
(act.CURRENCY_CODE,
'''||l_currency||''',
&BIS_CURRENT_ASOF_DATE,
sum(act.COMMITMENT_VALUE),
'''||l_rateType||''') committed_amount
FROM ' || l_trend_table || ' prds,
HRI_MDP_CMNTS_ACTLS_MV act
WHERE act.ORGMGR_ID(+) = &HRI_PERSON+HRI_PER_USRDR_H
AND prds.period_start_date <= act.EFFECTIVE_END_DATE(+)
AND prds.period_end_date >= act.EFFECTIVE_START_DATE(+)
GROUP BY prds.period_as_of_date,
prds.period_order,
act.CURRENCY_CODE ) a
WHERE a.period_as_of_date = b.period_as_of_date) x,
(
SELECT a.period_as_of_date,
a.period_order,
b.total budgeted,
a.total actual
FROM
(
SELECT prds.period_as_of_date
,prds.period_order
,NVL(sum(act.total_headcount),0) total
FROM ' || l_trend_table || ' prds,
HRI_MDP_WRKFC_MV act
WHERE prds.PERIOD_AS_OF_DATE BETWEEN act.EFFECTIVE_START_DATE(+) AND act.EFFECTIVE_END_DATE(+)
AND act.ORGMGR_ID(+) = &HRI_PERSON+HRI_PER_USRDR_H
GROUP BY prds.period_as_of_date,
prds.period_order )a,
(
SELECT period_as_of_date
,period_order
,SUM(TOTAL) TOTAL
FROM
(
SELECT prds.period_as_of_date
,prds.period_order
,bgt.ORGANIZATION_ID
,bgt.POSITION_ID
,CASE WHEN bgt.BUDGET_AGGREGATE = ''ACCUMULATE'' THEN SUM(bgt.HEADCOUNT_VALUE)
WHEN bgt.BUDGET_AGGREGATE = ''AVERAGE'' THEN AVG(bgt.HEADCOUNT_VALUE)
WHEN bgt.BUDGET_AGGREGATE = ''MAXIMUM'' THEN MAX(bgt.HEADCOUNT_VALUE)
ELSE SUM(bgt.HEADCOUNT_VALUE)
END TOTAL
FROM ' || l_trend_table || ' prds,
HRI_MDP_BDGTS_HDCNT_ORGMGR_CT bgt
WHERE bgt.ORGMGR_ID(+) = &HRI_PERSON+HRI_PER_USRDR_H
AND prds.period_start_date <= bgt.EFFECTIVE_END_DATE(+)
AND prds.period_end_date >= bgt.EFFECTIVE_START_DATE(+)
GROUP BY prds.period_as_of_date,
prds.period_order,
bgt.BUDGET_AGGREGATE,
bgt.ORGANIZATION_ID,
bgt.POSITION_ID
)
GROUP BY period_as_of_date,
period_order
)b
WHERE a.period_as_of_date=b.period_as_of_date ) hc
WHERE hc.period_as_of_date = x.period_as_of_date
ORDER BY hc.period_as_of_date
';
'SELECT --Budgeted and Projected Labor Cost Trend
a.period_as_of_date VIEWBYID
,a.period_as_of_date VIEWBY
,a.period_order HRI_P_ORDER_BY_1
,a.period_as_of_date HRI_P_GRAPH_X_LABEL_TIME
,b.budgeted_amount HRI_P_MEASURE2
,a.actual_amount + a.committed_amount HRI_P_MEASURE4
,to_char(a.period_as_of_date,''DD/MM/YYYY'') HRI_P_CHAR1_GA
FROM
(
SELECT prds.period_as_of_date
,prds.period_order
,hri_oltp_view_currency.convert_currency_amount
(bgt.CURRENCY_CODE,
'''||l_currency||''',
&BIS_CURRENT_ASOF_DATE,
SUM(BUDGET_VALUE),
'''||l_rateType||''') budgeted_amount
FROM ' || l_trend_table || ' prds,
HRI_MDP_BDGTS_LBRCST_ORG_MV bgt
WHERE bgt.ORGMGR_ID(+) = &HRI_PERSON+HRI_PER_USRDR_H
AND prds.period_start_date <= bgt.EFFECTIVE_END_DATE(+)
AND prds.period_end_date >= bgt.EFFECTIVE_START_DATE(+)
GROUP BY prds.period_as_of_date,
prds.period_order,
bgt.CURRENCY_CODE ) b,
(
SELECT prds.period_as_of_date
,prds.period_order
,hri_oltp_view_currency.convert_currency_amount
(act.CURRENCY_CODE,
'''||l_currency||''',
&BIS_CURRENT_ASOF_DATE,
sum(act.ACTUAL_VALUE),
'''||l_rateType||''') actual_amount
,hri_oltp_view_currency.convert_currency_amount
(act.CURRENCY_CODE,
'''||l_currency||''',
&BIS_CURRENT_ASOF_DATE,
sum(act.COMMITMENT_VALUE),
'''||l_rateType||''') committed_amount
FROM ' || l_trend_table || ' prds,
HRI_MDP_CMNTS_ACTLS_ORG_MV act
WHERE act.ORGMGR_ID(+) = &HRI_PERSON+HRI_PER_USRDR_H
AND prds.period_start_date <= act.EFFECTIVE_END_DATE(+)
AND prds.period_end_date >= act.EFFECTIVE_START_DATE(+)
GROUP BY prds.period_as_of_date,
prds.period_order,
act.CURRENCY_CODE)a
WHERE b.PERIOD_AS_OF_DATE=a.PERIOD_AS_OF_DATE
ORDER BY a.period_as_of_date
';
'SELECT -- Headcount Budget Trend
a.period_as_of_date VIEWBYID
,a.period_as_of_date VIEWBY
,a.period_order HRI_P_ORDER_BY_1
,a.period_as_of_date HRI_P_GRAPH_X_LABEL_TIME
,b.total HRI_P_MEASURE1
,a.total HRI_P_MEASURE2
,to_char(a.period_as_of_date,''DD/MM/YYYY'') HRI_P_CHAR1_GA
FROM
(
SELECT prds.period_as_of_date
,prds.period_order
,NVL(sum(act.total_headcount),0) total
FROM ' || l_trend_table || ' prds,
HRI_MDP_WRKFC_MV act
WHERE prds.PERIOD_AS_OF_DATE BETWEEN act.EFFECTIVE_START_DATE(+) AND act.EFFECTIVE_END_DATE(+)
AND act.ORGMGR_ID(+) = &HRI_PERSON+HRI_PER_USRDR_H
Group by prds.PERIOD_AS_OF_DATE
,prds.PERIOD_ORDER )a,
(
SELECT period_as_of_date
,period_order
,SUM(TOTAL) TOTAL
FROM
(
SELECT prds.period_as_of_date
,prds.period_order
,bgt.ORGANIZATION_ID
,bgt.POSITION_ID
,CASE WHEN bgt.BUDGET_AGGREGATE = ''ACCUMULATE'' THEN SUM(bgt.HEADCOUNT_VALUE)
WHEN bgt.BUDGET_AGGREGATE = ''AVERAGE'' THEN AVG(bgt.HEADCOUNT_VALUE)
WHEN bgt.BUDGET_AGGREGATE = ''MAXIMUM'' THEN MAX(bgt.HEADCOUNT_VALUE)
ELSE SUM(bgt.HEADCOUNT_VALUE)
END TOTAL
FROM ' || l_trend_table || ' prds,
HRI_MDP_BDGTS_HDCNT_ORGMGR_CT bgt
WHERE bgt.ORGMGR_ID(+) = &HRI_PERSON+HRI_PER_USRDR_H
AND prds.period_start_date <= bgt.EFFECTIVE_END_DATE(+)
AND prds.period_end_date >= bgt.EFFECTIVE_START_DATE(+)
GROUP BY prds.period_as_of_date,
prds.period_order,
bgt.BUDGET_AGGREGATE,
bgt.ORGANIZATION_ID,
bgt.POSITION_ID
)
GROUP BY period_as_of_date,
period_order
)b
WHERE a.period_as_of_date=b.period_as_of_date
ORDER BY a.period_as_of_date';