[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_WMV_BCKT_GRAPH
Source
1 PACKAGE BODY hri_oltp_pmv_wmv_bckt_graph AS
2 /* $Header: hriophrg.pkb 120.0 2005/05/29 07:32:57 appldev noship $ */
3
4 PROCEDURE get_sql_bckt_low
5 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
6 x_custom_sql OUT NOCOPY VARCHAR2,
7 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
8
9 l_custom_rec BIS_QUERY_ATTRIBUTES;
10 l_security_clause VARCHAR2(4000);
11 l_SQLText VARCHAR2(4000);
12
13 /* Parameter values */
14 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
15 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
16
17 /* Pre-calculations */
18 l_trend_table VARCHAR2(4000);
19 l_projection_periods NUMBER;
20 l_previous_periods NUMBER;
21
22 /* Dynamic SQL Controls */
23 l_trend_sql_params hri_oltp_pmv_query_trend.trend_sql_params_type;
24 l_trend_sql VARCHAR2(10000);
25
26 BEGIN
27
28 /* Initialize table/record variables */
29 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
30 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
31
32 /* Get security clause for Manager based security */
33 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
34
35 /* Get common parameter values */
36 hri_oltp_pmv_util_param.get_parameters_from_table
37 (p_page_parameter_tbl => p_page_parameter_tbl
38 ,p_parameter_rec => l_parameter_rec
39 ,p_bind_tab => l_bind_tab);
40
41 /* Get the number of periods to display */
42 hri_oltp_pmv_query_time.get_period_binds
43 (p_page_period_type => l_parameter_rec.page_period_type
44 ,p_page_comp_type => l_parameter_rec.time_comparison_type
45 ,o_previous_periods => l_previous_periods
46 ,o_projection_periods => l_projection_periods);
47
48 /* Get the trend sql */
49 l_trend_sql_params.bind_format := 'PMV';
50 l_trend_sql_params.include_hdc := 'Y';
51 l_trend_sql_params.bucket_dim := 'HRI_LOW+HRI_LOW_BAND_X';
52 l_trend_sql := hri_oltp_pmv_query_trend.get_sql
53 (p_parameter_rec => l_parameter_rec,
54 p_bind_tab => l_bind_tab,
55 p_trend_sql_params => l_trend_sql_params,
56 p_calling_module => 'HRI_OLTP_PMV_WMV_BCKT_GRAPH.GET_SQL_BCKT_LOW');
57
58 /* AK SQL Statement */
59 l_SQLText :=
60 'SELECT -- Headcount with LOW Band Trend
61 qry.period_as_of_date VIEWBYID
62 ,qry.period_as_of_date VIEWBY
63 ,qry.period_order HRI_P_ORDER_BY_1
64 ,DECODE(qry.period_hdc, 0, 0,
65 (qry.period_hdc_b1/qry.period_hdc)*100)
66 HRI_P_MEASURE1
67 ,DECODE(qry.period_hdc, 0, 0,
68 (qry.period_hdc_b2/qry.period_hdc)*100)
69 HRI_P_MEASURE2
70 ,DECODE(qry.period_hdc, 0, 0,
71 (qry.period_hdc_b3/qry.period_hdc)*100)
72 HRI_P_MEASURE3
73 ,DECODE(qry.period_hdc, 0, 0,
74 (qry.period_hdc_b4/qry.period_hdc)*100)
75 HRI_P_MEASURE4
76 ,DECODE(qry.period_hdc, 0, 0,
77 (qry.period_hdc_b5/qry.period_hdc)*100)
78 HRI_P_MEASURE5
79 ,to_char(qry.period_as_of_date, ''DD/MM/YYYY'')
80 HRI_P_CHAR1_GA
81 FROM
82 (' || l_trend_sql || ') qry
83 WHERE 1=1
84 ' || l_security_clause || '
85 ORDER BY qry.period_order';
86
87 x_custom_sql := l_sqltext;
88
89 l_custom_rec.attribute_name := ':TIME_PERIOD_TYPE';
90 l_custom_rec.attribute_value := l_parameter_rec.page_period_type;
91 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
92 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
93 x_custom_output.extend;
94 x_custom_output(1) := l_custom_rec;
95
96 l_custom_rec.attribute_name := ':TIME_COMPARISON_TYPE';
97 l_custom_rec.attribute_value := l_parameter_rec.time_comparison_type;
98 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
99 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
100 x_custom_output.extend;
101 x_custom_output(2) := l_custom_rec;
102
103 l_custom_rec.attribute_name := ':TIME_PERIOD_NUMBER';
104 l_custom_rec.attribute_value := l_previous_periods;
105 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
106 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
107 x_custom_output.extend;
108 x_custom_output(3) := l_custom_rec;
109
110 END get_sql_bckt_low;
111
112 PROCEDURE get_sql_bckt_perf
113 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
114 x_custom_sql OUT NOCOPY VARCHAR2,
115 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
116
117 l_custom_rec BIS_QUERY_ATTRIBUTES;
118 l_security_clause VARCHAR2(4000);
119 l_SQLText VARCHAR2(4000);
120
121 /* Parameter values */
122 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
123 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
124
125 /* Pre-calculations */
126 l_trend_table VARCHAR2(4000);
127 l_projection_periods NUMBER;
128 l_previous_periods NUMBER;
129
130 /* Dynamic SQL Controls */
131 l_trend_sql_params hri_oltp_pmv_query_trend.trend_sql_params_type;
132 l_trend_sql VARCHAR2(10000);
133
134 BEGIN
135
136 /* Initialize table/record variables */
137 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
138 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
139
140 /* Get security clause for Manager based security */
141 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
142
143 /* Get common parameter values */
144 hri_oltp_pmv_util_param.get_parameters_from_table
145 (p_page_parameter_tbl => p_page_parameter_tbl
146 ,p_parameter_rec => l_parameter_rec
147 ,p_bind_tab => l_bind_tab);
148
149 /* Get the number of periods to display */
150 hri_oltp_pmv_query_time.get_period_binds
151 (p_page_period_type => l_parameter_rec.page_period_type
152 ,p_page_comp_type => l_parameter_rec.time_comparison_type
153 ,o_previous_periods => l_previous_periods
154 ,o_projection_periods => l_projection_periods);
155
156 /* Get the trend sql */
157 l_trend_sql_params.bind_format := 'PMV';
158 l_trend_sql_params.include_hdc := 'Y';
159 l_trend_sql_params.bucket_dim := 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X';
160 l_trend_sql := hri_oltp_pmv_query_trend.get_sql
161 (p_parameter_rec => l_parameter_rec,
162 p_bind_tab => l_bind_tab,
163 p_trend_sql_params => l_trend_sql_params,
164 p_calling_module => 'HRI_OLTP_PMV_WMV_BCKT_GRAPH.GET_SQL_BCKT_PERF');
165
166 /* AK SQL Statement */
167 l_SQLText :=
168 'SELECT -- Headcount with Performance Band Trend
169 qry.period_as_of_date VIEWBYID
170 ,qry.period_as_of_date VIEWBY
171 ,qry.period_order HRI_P_ORDER_BY_1
172 ,DECODE(qry.period_hdc, 0, 0,
173 (qry.period_hdc_b3/qry.period_hdc)*100)
174 HRI_P_MEASURE1
175 ,DECODE(qry.period_hdc, 0, 0,
176 (qry.period_hdc_b2/qry.period_hdc)*100)
177 HRI_P_MEASURE2
178 ,DECODE(qry.period_hdc, 0, 0,
179 (qry.period_hdc_b1/qry.period_hdc)*100)
180 HRI_P_MEASURE3
181 ,DECODE(qry.period_hdc, 0, 0,
182 (qry.period_hdc_na/qry.period_hdc)*100)
183 HRI_P_MEASURE4
184 ,to_char(qry.period_as_of_date, ''DD/MM/YYYY'')
185 HRI_P_CHAR1_GA
186 FROM
187 (' || l_trend_sql || ') qry
188 WHERE 1=1
189 ' || l_security_clause || '
190 ORDER BY qry.period_order';
191
192 x_custom_sql := l_sqltext;
193
194 l_custom_rec.attribute_name := ':TIME_PERIOD_TYPE';
195 l_custom_rec.attribute_value := l_parameter_rec.page_period_type;
196 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
197 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
198 x_custom_output.extend;
199 x_custom_output(1) := l_custom_rec;
200
201 l_custom_rec.attribute_name := ':TIME_COMPARISON_TYPE';
202 l_custom_rec.attribute_value := l_parameter_rec.time_comparison_type;
203 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
204 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
205 x_custom_output.extend;
206 x_custom_output(2) := l_custom_rec;
207
208 l_custom_rec.attribute_name := ':TIME_PERIOD_NUMBER';
209 l_custom_rec.attribute_value := l_previous_periods;
210 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
211 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
212 x_custom_output.extend;
213 x_custom_output(3) := l_custom_rec;
214
215 END get_sql_bckt_perf;
216
217 END hri_oltp_pmv_wmv_bckt_graph;