eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.HRI_OLTP_PMV_ABS_CAT

Source


1 PACKAGE BODY HRI_OLTP_PMV_ABS_CAT AS
2 /* $Header: hriopabsct.pkb 120.3 2005/10/26 07:55 jrstewar noship $ */
3 
4 g_rtn                VARCHAR2(30) := '
5 ';
6 
7 g_unassigned         VARCHAR2(50) := HRI_OLTP_VIEW_MESSAGE.get_unassigned_msg;
8 
9 PROCEDURE get_sql_abscat_t4
10   (p_page_parameter_tbl  IN BIS_PMV_PAGE_PARAMETER_TBL,
11    x_custom_sql          OUT NOCOPY VARCHAR2,
12    x_custom_output       OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
13 
14   l_sqltext                 VARCHAR2(32767);
15   l_custom_rec              BIS_QUERY_ATTRIBUTES;
16   l_security_clause         VARCHAR2(4000);
17 
18 -- Parameter values
19   l_parameter_rec         hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
20   l_bind_tab              hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
21   l_abs_category_tab      hri_oltp_pmv_rank_abs.abs_category_tab;
22 
23 -- Trend Period Parameters
24   l_projection_periods  NUMBER;
25   l_previous_periods    NUMBER;
26   l_trend_sql           VARCHAR2(10000);
27   l_trend_sql_params    hri_oltp_pmv_query_trend.trend_sql_params_type;
28 
29 -- dyanamic columns
30   l_period_abs_drtn_metric1 VARCHAR2(1000);
31   l_period_abs_drtn_metric2 VARCHAR2(1000);
32   l_period_abs_drtn_metric3 VARCHAR2(1000);
33   l_period_abs_drtn_metric4 VARCHAR2(1000);
34 
35   l_drill_url1   VARCHAR2(1000);
36   l_drill_url2   VARCHAR2(1000);
37   l_drill_url3   VARCHAR2(1000);
38   l_drill_url4   VARCHAR2(1000);
39 
40 
41 BEGIN
42 
43 /* Initialize out parameters */
44   l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
45   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
46 
47 /* Get common parameter values */
48   hri_oltp_pmv_util_param.get_parameters_from_table
49         (p_page_parameter_tbl  => p_page_parameter_tbl,
50          p_parameter_rec       => l_parameter_rec,
51          p_bind_tab            => l_bind_tab);
52 
53 /* Get security clause for Manager based security */
54   l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
55 
56 /* Get bind values for number of time periods */
57   hri_oltp_pmv_query_time.get_period_binds
58           (p_page_period_type    => l_parameter_rec.page_period_type
59           ,p_page_comp_type      => l_parameter_rec.time_comparison_type
60           ,o_previous_periods    => l_previous_periods
61           ,o_projection_periods  => l_projection_periods);
62 
63 /* Get the top 4 Absence Categories for selection Mgr on effective date */
64   hri_oltp_pmv_rank_abs.set_top_categories
65    (p_supervisor_id  => l_parameter_rec.peo_supervisor_id,
66     p_effective_from_date => l_parameter_rec.time_curr_start_date,
67     p_effective_to_date => l_parameter_rec.time_curr_end_date,
68     p_no_categories  => 4,
69     p_category_tab   => l_abs_category_tab);
70 
71 /* Set the trend sql context for the code centralization call */
72   l_trend_sql_params.bind_format := 'PMV';
73 
74 /* Set metric base on absence UOM parameter, default to DAYS */
75   IF (hri_bpl_utilization.get_abs_durtn_profile_vl = 'DAYS') THEN
76       l_trend_sql_params.include_abs_drtn_days   := 'Y';
77 
78       l_period_abs_drtn_metric1 := 'qry.period_abs_drtn_days_absCat1';
79       l_period_abs_drtn_metric2 := 'qry.period_abs_drtn_days_absCat2';
80       l_period_abs_drtn_metric3 := 'qry.period_abs_drtn_days_absCat3';
81       l_period_abs_drtn_metric4 := 'qry.period_abs_drtn_days_absCat4';
82 
83   ELSIF (hri_bpl_utilization.get_abs_durtn_profile_vl = 'HOURS') THEN
84       l_trend_sql_params.include_abs_drtn_hrs   := 'Y';
85       l_period_abs_drtn_metric1 := 'qry.period_abs_drtn_hrs_absCat1';
86       l_period_abs_drtn_metric2 := 'qry.period_abs_drtn_hrs_absCat2';
87       l_period_abs_drtn_metric3 := 'qry.period_abs_drtn_hrs_absCat3';
88       l_period_abs_drtn_metric4 := 'qry.period_abs_drtn_hrs_absCat4';
89   ELSE
90       l_trend_sql_params.include_abs_drtn_days   := 'Y';
91       l_period_abs_drtn_metric1 := 'qry.period_abs_drtn_days_absCat1';
92       l_period_abs_drtn_metric2 := 'qry.period_abs_drtn_days_absCat2';
93       l_period_abs_drtn_metric3 := 'qry.period_abs_drtn_days_absCat3';
94       l_period_abs_drtn_metric4 := 'qry.period_abs_drtn_days_absCat4';
95 
96   END IF;
97 
98 /* Set to bucket for Absence Category */
99   l_trend_sql_params.bucket_dim := 'HRI_ABSNC+HRI_ABSNC_CAT';
100 
101 /* Call the UI fact code centralization for the Trend */
102   l_trend_sql := hri_oltp_pmv_query_trend.get_sql
103                   (p_parameter_rec    => l_parameter_rec,
104                    p_bind_tab         => l_bind_tab,
105                    p_trend_sql_params => l_trend_sql_params,
106                    p_calling_module   => 'HRI_OLTP_PMV_ABS_CAT.GET_SQL_ABSCAT_T4');
107 
108 /* set the dynamic drill urls
109    Commented out, as this is not requested in the functional spec.
110 
111 l_drill_url1 :=
112     'pFunctionName=HRI_P_ABS_SUP_DTL&' ||
113                    'VIEW_BY=VIEW_BY_NAME&' ||
114                    'VIEW_BY_NAME=VIEW_BY_ID&' ||
115                    'HRI_ABSNC+HRI_ABSNC_CAT='|| hri_oltp_pmv_rank_abs.get_category_code(1)||'&'||
116                    ''' || ''' || 'AS_OF_DATE=HRI_P_CHAR2_GA&' ||
117                    'pParamIds=Y';
118 l_drill_url2 :=
119     'pFunctionName=HRI_P_ABS_SUP_DTL&' ||
120                    'VIEW_BY=VIEW_BY_NAME&' ||
121                    'VIEW_BY_NAME=VIEW_BY_ID&' ||
122                    'HRI_ABSNC+HRI_ABSNC_CAT='|| hri_oltp_pmv_rank_abs.get_category_code(2)||'&'||
123                    ''' || ''' || 'AS_OF_DATE=HRI_P_CHAR2_GA&' ||
124                    'pParamIds=Y';
125 l_drill_url3 :=
126     'pFunctionName=HRI_P_ABS_SUP_DTL&' ||
127                    'VIEW_BY=VIEW_BY_NAME&' ||
128                    'VIEW_BY_NAME=VIEW_BY_ID&' ||
129                    'HRI_ABSNC+HRI_ABSNC_CAT='|| hri_oltp_pmv_rank_abs.get_category_code(3)||'&'||
130                    ''' || ''' || 'AS_OF_DATE=HRI_P_CHAR2_GA&' ||
131                    'pParamIds=Y';
132 l_drill_url4 :=
133     'pFunctionName=HRI_P_ABS_SUP_DTL&' ||
134                    'VIEW_BY=VIEW_BY_NAME&' ||
135                    'VIEW_BY_NAME=VIEW_BY_ID&' ||
136                    'HRI_ABSNC+HRI_ABSNC_CAT='|| hri_oltp_pmv_rank_abs.get_category_code(4)||'&'||
137                    ''' || ''' || 'AS_OF_DATE=HRI_P_CHAR2_GA&' ||
138                    'pParamIds=Y';
139 */
140 
141 /* Formulate the PMV final query output for the UI*/
142 l_sqltext :=
143 'SELECT -- Employee Absence Top 4 Categories Trend
144  qry.period_as_of_date     VIEWBYID
145 ,qry.period_as_of_date     VIEWBY
146 ,qry.period_order          HRI_P_ORDER_BY_1
147 ,'||l_period_abs_drtn_metric1 ||'   HRI_P_MEASURE1
148 ,''' || l_drill_url1 || '''         HRI_P_DRILL_URL1
149 ,'||l_period_abs_drtn_metric2 ||'   HRI_P_MEASURE2
150 ,''' || l_drill_url2 || '''         HRI_P_DRILL_URL2
151 ,'||l_period_abs_drtn_metric3 ||'   HRI_P_MEASURE3
152 ,''' || l_drill_url3 || '''         HRI_P_DRILL_URL3
153 ,'||l_period_abs_drtn_metric4 ||'   HRI_P_MEASURE4
154 ,''' || l_drill_url4 || '''         HRI_P_DRILL_URL4
155 ,to_char(qry.period_as_of_date,''DD/MM/YYYY'')   HRI_P_CHAR2_GA
156 FROM
157  (' || l_trend_sql || ')  qry
158 WHERE 1=1
159 '
160 || l_security_clause ||
161 ' ORDER BY
162   period_order';
163 
164   x_custom_sql := l_sqltext;
165 
166   l_custom_rec.attribute_name := ':TIME_PERIOD_TYPE';
167   l_custom_rec.attribute_value := l_parameter_rec.page_period_type;
168   l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
169   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
170   x_custom_output.extend;
171   x_custom_output(1) := l_custom_rec;
172 
173   l_custom_rec.attribute_name := ':TIME_COMPARISON_TYPE';
174   l_custom_rec.attribute_value := l_parameter_rec.time_comparison_type;
175   l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
176   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
177   x_custom_output.extend;
178   x_custom_output(2) := l_custom_rec;
179 
180   l_custom_rec.attribute_name := ':TIME_PERIOD_NUMBER';
181   l_custom_rec.attribute_value := l_previous_periods;
182   l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
183   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
184   x_custom_output.extend;
185   x_custom_output(3) := l_custom_rec;
186 
187   l_custom_rec.attribute_name := ':ABS_CATEGORY_CODE1';
188   l_custom_rec.attribute_value := l_abs_category_tab(1);
189   l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
190   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
191   x_custom_output.extend;
192   x_custom_output(4) := l_custom_rec;
193 
194   l_custom_rec.attribute_name := ':ABS_CATEGORY_CODE2';
195   l_custom_rec.attribute_value := l_abs_category_tab(2);
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(5) := l_custom_rec;
200 
201   l_custom_rec.attribute_name := ':ABS_CATEGORY_CODE3';
202   l_custom_rec.attribute_value := l_abs_category_tab(3);
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(6) := l_custom_rec;
207 
208   l_custom_rec.attribute_name := ':ABS_CATEGORY_CODE4';
209   l_custom_rec.attribute_value := l_abs_category_tab(4);
210   l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
211   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
212   x_custom_output.extend;
213   x_custom_output(7) := l_custom_rec;
214 
215 
216 END get_sql_abscat_t4 ;
217 
218 PROCEDURE 	GET_SQL_TN(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
219                  ,x_custom_sql  OUT NOCOPY VARCHAR2
220                  ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
221        IS
222 
223   l_SQLText               VARCHAR2(32000);
224   l_security_clause       VARCHAR2(4000);
225   l_custom_rec BIS_QUERY_ATTRIBUTES ;
226 
227 /* Dynamic SQL Controls */
228   l_abs_fact_params       hri_bpl_fact_abs_sql.abs_fact_param_type;
229   l_abs_fact_sql          VARCHAR2(10000);
230   l_parameter_name        VARCHAR2(100);
231   l_dynmc_drtn_curr       VARCHAR2(100) DEFAULT 'curr_abs_drtn_days';
232   l_dynmc_tot_drtn_curr   VARCHAR2(100) DEFAULT 'tot_abs_drtn_days_curr';
233   l_drill_abs_detail      VARCHAR2(1000);
234 
235 /* Parameter values */
236   l_parameter_rec         hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
237   l_bind_tab              hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
238   l_debug_header          VARCHAR(550);
239 
240 BEGIN
241 /* Initialize out parameters */
242   l_custom_rec    := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
243   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
244 
245 /* Get security clause for Manager based security */
246   l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
247 
248 /* Get common parameter values */
249   hri_oltp_pmv_util_param.get_parameters_from_table
250         (p_page_parameter_tbl  => p_page_parameter_tbl,
251          p_parameter_rec       => l_parameter_rec,
252          p_bind_tab            => l_bind_tab);
253 
254 /* Drill URL's for Manager and Direct Reports */
255   l_drill_abs_detail :='pFunctionName=HRI_P_ABS_SUP_DTL&' ||
256                      'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
257                      'VIEW_BY_NAME=VIEW_BY_ID&' ||
258                      'pParamIds=Y';
259 
260 /* Set order by */
261   l_parameter_rec.order_by :=  hri_oltp_pmv_util_pkg.set_default_order_by
262                                 (p_order_by_clause => l_parameter_rec.order_by);
263 
264   /* formulate the dynmaic column selection based on Absence Duration
265      unit of measure paramter selection  Default Days                */
266 
267      IF (hri_bpl_utilization.get_abs_durtn_profile_vl = 'DAYS') THEN
268         l_dynmc_drtn_curr := 'curr_abs_drtn_days';
269         l_abs_fact_params.include_abs_drtn_days := 'Y';
270       ELSIF (hri_bpl_utilization.get_abs_durtn_profile_vl = 'HOURS') THEN
271         l_dynmc_drtn_curr := 'curr_abs_drtn_hrs';
272         l_abs_fact_params.include_abs_drtn_hrs  := 'Y';
273       ELSE -- functional decision (JC) default to days
274         l_dynmc_drtn_curr := 'curr_abs_drtn_days';
275         l_abs_fact_params.include_abs_drtn_days := 'Y';
276       END IF;
277 
278 /* Get SQL for workforce fact */
279   l_abs_fact_params.bind_format := 'PMV';
280   l_abs_fact_params.include_abs_in_period := 'N';
281   l_abs_fact_params.include_abs_ntfctn_period := 'N';
282   l_abs_fact_params.include_comp := 'N';
283   l_abs_fact_params.kpi_mode := 'N';
284   l_abs_fact_sql := hri_bpl_fact_abs_sql.get_sql
285    (p_parameter_rec  => l_parameter_rec,
286     p_bind_tab       => l_bind_tab,
287     p_abs_params     => l_abs_fact_params,
288     p_calling_module => 'HRI_P_ABS_CAT_TN_GRAPH');
289 
290 l_SQLText :=
291     ' -- Employee Absence by Category Graph Pie
292 SELECT
293  babs.vby_id											VIEWBYID
294 ,babs.value												VIEWBY'|| g_rtn
295 /* Absence  */ || g_rtn ||'
296 ,NVL(babs.curr_abs_drtn,to_number(NULL))                HRI_P_MEASURE1 '|| g_rtn
297 /* Total Absence  */ || g_rtn ||'
298 ,NVL(babs.curr_tot_abs_drtn,to_number(NULL))   		    HRI_P_GRAND_TOTAL1'|| g_rtn
299 /* Drill URLs */ || g_rtn ||'
300 ,'''|| l_drill_abs_detail ||'''	                        HRI_P_DRILL_URL1
301 FROM
302 (
303 SELECT
304 /* Base Measures */
305  vby.id                   				          vby_id
306 ,vby.value                                        value
307 ,vby.order_by                                     order_by
308 ,NVL(afact.'|| l_dynmc_drtn_curr ||',0)           curr_abs_drtn
309 ,SUM(afact.'|| l_dynmc_drtn_curr ||') OVER()      curr_tot_abs_drtn
310 
311 FROM
312  hri_cl_absnc_cat_v  vby
313 ,('|| l_abs_fact_sql ||') afact
314 WHERE
315    vby.id = afact.vby_id
316  ' || l_security_clause || ') babs' || g_rtn ||
317 '&ORDER_BY_CLAUSE';
318 
319   x_custom_sql := l_SQLText ;
320 
321 END GET_SQL_TN ;
322 
323 
324 END HRI_OLTP_PMV_ABS_CAT ;