[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_UTIL_PARAM
Source
1 PACKAGE BODY hri_oltp_pmv_util_param AS
2 /* $Header: hrioputp.pkb 120.8 2005/12/20 06:08:48 jtitmas noship $ */
3
4 /* TYPE HRI_PMV_PARAM_REC_TYPE defined in package header */
5
6 /* Return character */
7 g_rtn VARCHAR2(30) := '
8 ';
9
10 /******************************************************************************/
11 /* Checks whether a restricting parameter value is passed in */
12 /******************************************************************************/
13 FUNCTION is_parameter_set(p_parameter_value_id IN VARCHAR2)
14 RETURN BOOLEAN IS
15
16 BEGIN
17
18 /* Checks for NULL (no value) or All (all values) */
19 IF (p_parameter_value_id IS NOT NULL AND
20 p_parameter_value_id <> '''-1''' AND
21 p_parameter_value_id <> '-1') THEN
22 RETURN TRUE;
23 END IF;
24
25 RETURN FALSE;
26
27 END is_parameter_set;
28
29
30 /******************************************************************************/
31 /* Adds required parameters to the table of bind values */
32 /******************************************************************************/
33 PROCEDURE fill_in_bind_table
34 (p_parameter_rec IN OUT NOCOPY HRI_PMV_PARAM_REC_TYPE,
35 p_bind_tab IN OUT NOCOPY HRI_PMV_BIND_TAB_TYPE) IS
36
37 l_low_band_buckets_tab hri_mtdt_dim_lvl.dim_lvl_buckets_tabtype;
38 l_wkth_wktyp_bind VARCHAR2(240);
39
40 BEGIN
41
42 /* Add the time dates to the binds table */
43 p_bind_tab('TIME_CURR_START_DATE').pmv_bind_string :=
44 hri_mtdt_param.g_param_mtdt_tab
45 ('TIME_CURR_START_DATE').pmv_bind_string;
46 p_bind_tab('TIME_CURR_START_DATE').sql_bind_string :=
47 'to_date(''' || to_char(p_parameter_rec.time_curr_start_date,'DD-MM-YYYY') || ''',''DD-MM-YYYY'')';
48
49 p_bind_tab('TIME_CURR_END_DATE').pmv_bind_string :=
50 hri_mtdt_param.g_param_mtdt_tab
51 ('TIME_CURR_END_DATE').pmv_bind_string;
52 p_bind_tab('TIME_CURR_END_DATE').sql_bind_string :=
53 'to_date(''' || to_char(p_parameter_rec.time_curr_end_date,'DD-MM-YYYY') || ''',''DD-MM-YYYY'')';
54
55 p_bind_tab('TIME_CURR_ASOF_DATE').pmv_bind_string :=
56 hri_mtdt_param.g_param_mtdt_tab
57 ('TIME_CURR_ASOF_DATE').pmv_bind_string;
58 p_bind_tab('TIME_CURR_ASOF_DATE').sql_bind_string :=
59 'to_date(''' || to_char(p_parameter_rec.time_curr_end_date,'DD-MM-YYYY') || ''',''DD-MM-YYYY'')';
60
61 p_bind_tab('TIME_COMP_START_DATE').pmv_bind_string :=
62 hri_mtdt_param.g_param_mtdt_tab
63 ('TIME_COMP_START_DATE').pmv_bind_string;
64 p_bind_tab('TIME_COMP_START_DATE').sql_bind_string :=
65 'to_date(''' || to_char(p_parameter_rec.time_comp_start_date,'DD-MM-YYYY') || ''',''DD-MM-YYYY'')';
66
67 p_bind_tab('TIME_COMP_END_DATE').pmv_bind_string :=
68 hri_mtdt_param.g_param_mtdt_tab
69 ('TIME_COMP_END_DATE').pmv_bind_string;
70 p_bind_tab('TIME_COMP_END_DATE').sql_bind_string :=
71 'to_date(''' || to_char(p_parameter_rec.time_comp_end_date,'DD-MM-YYYY') || ''',''DD-MM-YYYY'')';
72
73 p_bind_tab('TIME_COMP_ASOF_DATE').pmv_bind_string :=
74 hri_mtdt_param.g_param_mtdt_tab
75 ('TIME_COMP_ASOF_DATE').pmv_bind_string;
76 p_bind_tab('TIME_COMP_ASOF_DATE').sql_bind_string :=
77 'to_date(''' || to_char(p_parameter_rec.time_comp_end_date,'DD-MM-YYYY') || ''',''DD-MM-YYYY'')';
78
79 /* Add currency binds to bind table, if set */
80 IF (p_parameter_rec.currency_code IS NOT NULL) THEN
81 p_bind_tab('CURRENCY').pmv_bind_string :=
82 hri_mtdt_param.g_param_mtdt_tab('CURRENCY').pmv_bind_string;
83 p_bind_tab('CURRENCY').sql_bind_string := '''' || p_parameter_rec.currency_code || '''';
84 p_bind_tab('RATE_TYPE').pmv_bind_string :=
85 hri_mtdt_param.g_param_mtdt_tab('RATE_TYPE').pmv_bind_string;
86 p_bind_tab('RATE_TYPE').sql_bind_string := '''' || p_parameter_rec.rate_type || '''';
87 END IF;
88
89 /* Defaulting required parameters for bug 4134385
90 usually due to the FND_USER not being assigned a Employee */
91 IF NOT p_bind_tab.EXISTS('HRI_PERSON+HRI_PER_USRDR_H') THEN
92 p_parameter_rec.peo_supervisor_id := '-1';
93 p_bind_tab('HRI_PERSON+HRI_PER_USRDR_H').pmv_bind_string := '-1';
94 p_bind_tab('HRI_PERSON+HRI_PER_USRDR_H').sql_bind_string := '-1';
95 END IF;
96
97 /* Get worker type bind */
98 l_wkth_wktyp_bind := hri_mtdt_ak_region.get_ak_region_wkth_wktyp
99 (p_ak_region_code => p_parameter_rec.bis_region_code);
100
101 /* Add worker type bind if set */
102 IF (is_parameter_set(l_wkth_wktyp_bind)) THEN
103 p_parameter_rec.wkth_wktyp_sk_fk := l_wkth_wktyp_bind;
104 p_bind_tab('HRI_PRSNTYP+HRI_WKTH_WKTYP').pmv_bind_string :=
105 '''' || l_wkth_wktyp_bind || '''';
106 p_bind_tab('HRI_PRSNTYP+HRI_WKTH_WKTYP').sql_bind_string :=
107 '''' || l_wkth_wktyp_bind || '''';
108 END IF;
109
110 END fill_in_bind_table;
111
112
113 /******************************************************************************/
114 /* Reads parameter table and populates the parameter record and bind table */
115 /******************************************************************************/
116 PROCEDURE get_parameters_from_table
117 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
118 p_parameter_rec OUT NOCOPY HRI_PMV_PARAM_REC_TYPE,
119 p_bind_tab OUT NOCOPY HRI_PMV_BIND_TAB_TYPE) IS
120
121 l_add_to_bind_table BOOLEAN;
122 l_currency_id VARCHAR2(240);
123
124 BEGIN
125
126 /* Default the page period type */
127 p_parameter_rec.page_period_type := 'DEFAULT';
128
129 /* Loop through the parameters passed in */
130 FOR i IN p_page_parameter_tbl.FIRST..p_page_parameter_tbl.LAST LOOP
131
132 /* Reset flag */
133 l_add_to_bind_table := FALSE;
134
135 /******************************************************************************/
136 /* PMV Attributes */
137 /******************/
138
139 /* Get the BIS region code */
140 IF p_page_parameter_tbl(i).parameter_name = 'BIS_REGION_CODE' THEN
141 p_parameter_rec.bis_region_code := p_page_parameter_tbl(i).parameter_value;
142
143 /******************************************************************************/
144 /* Report Display Parameters */
145 /*****************************/
146
147 /* Get the ORDER BY string */
148 ELSIF p_page_parameter_tbl(i).parameter_name = 'ORDERBY' THEN
149 p_parameter_rec.order_by := p_page_parameter_tbl(i).parameter_value;
150
151 /* Get the view by dimension */
152 ELSIF p_page_parameter_tbl(i).parameter_name = 'VIEW_BY' THEN
153 p_parameter_rec.view_by := p_page_parameter_tbl(i).parameter_value;
154
155 /******************************************************************************/
156 /* Report Data Parameters */
157 /**************************/
158
159 /* Central Parameters */
160 /**********************/
161
162 /* Checks for time dimension */
163 ELSIF substr(p_page_parameter_tbl(i).parameter_name, 1, 5) = 'TIME+' THEN
164
165 /* Get the comparison period start date */
166 IF p_page_parameter_tbl(i).parameter_name='TIME+FII_ROLLING_YEAR_PFROM' THEN
167 p_parameter_rec.time_comp_start_date := p_page_parameter_tbl(i).period_date;
168 ELSIF p_page_parameter_tbl(i).parameter_name='TIME+FII_ROLLING_MONTH_PFROM' THEN
169 p_parameter_rec.time_comp_start_date := p_page_parameter_tbl(i).period_date;
170 ELSIF p_page_parameter_tbl(i).parameter_name='TIME+FII_ROLLING_WEEK_PFROM' THEN
171 p_parameter_rec.time_comp_start_date := p_page_parameter_tbl(i).period_date;
172 ELSIF p_page_parameter_tbl(i).parameter_name='TIME+FII_ROLLING_QTR_PFROM' THEN
173 p_parameter_rec.time_comp_start_date := p_page_parameter_tbl(i).period_date;
174
175 /* Get the comparison period end date */
176 ELSIF p_page_parameter_tbl(i).parameter_name='TIME+FII_ROLLING_YEAR_PTO' THEN
177 p_parameter_rec.time_comp_end_date := p_page_parameter_tbl(i).period_date;
178 ELSIF p_page_parameter_tbl(i).parameter_name='TIME+FII_ROLLING_MONTH_PTO' THEN
179 p_parameter_rec.time_comp_end_date := p_page_parameter_tbl(i).period_date;
180 ELSIF p_page_parameter_tbl(i).parameter_name='TIME+FII_ROLLING_WEEK_PTO' THEN
181 p_parameter_rec.time_comp_end_date := p_page_parameter_tbl(i).period_date;
182 ELSIF p_page_parameter_tbl(i).parameter_name='TIME+FII_ROLLING_QTR_PTO' THEN
183 p_parameter_rec.time_comp_end_date := p_page_parameter_tbl(i).period_date;
184
185 /* Get the current period start date */
186 ELSIF p_page_parameter_tbl(i).parameter_name='TIME+FII_ROLLING_YEAR_FROM' THEN
187 p_parameter_rec.time_curr_start_date := p_page_parameter_tbl(i).period_date;
188 ELSIF p_page_parameter_tbl(i).parameter_name='TIME+FII_ROLLING_MONTH_FROM' THEN
189 p_parameter_rec.time_curr_start_date := p_page_parameter_tbl(i).period_date;
190 ELSIF p_page_parameter_tbl(i).parameter_name='TIME+FII_ROLLING_WEEK_FROM' THEN
191 p_parameter_rec.time_curr_start_date := p_page_parameter_tbl(i).period_date;
192 ELSIF p_page_parameter_tbl(i).parameter_name='TIME+FII_ROLLING_QTR_FROM' THEN
193 p_parameter_rec.time_curr_start_date := p_page_parameter_tbl(i).period_date;
194 END IF;
195
196 /* Get the current period end date */
197 ELSIF p_page_parameter_tbl(i).parameter_name='AS_OF_DATE' THEN
198 p_parameter_rec.time_curr_end_date :=
199 to_date(p_page_parameter_tbl(i).parameter_value,'DD-MM-YYYY');
200
201 /* Get the page_period_type */
202 ELSIF p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' THEN
203 p_parameter_rec.page_period_type := p_page_parameter_tbl(i).parameter_value;
204 l_add_to_bind_table := TRUE;
205
206 /* Get the time_comparison_type */
207 ELSIF p_page_parameter_tbl(i).parameter_name = 'TIME_COMPARISON_TYPE' THEN
208 p_parameter_rec.time_comparison_type := p_page_parameter_tbl(i).parameter_value;
209 l_add_to_bind_table := TRUE;
210
211 /* Get the currency and rate type */
212 ELSIF p_page_parameter_tbl(i).parameter_name = 'CURRENCY+FII_CURRENCIES' THEN
213
214 l_currency_id := ltrim(rtrim(p_page_parameter_tbl(i).parameter_id,''''),'''');
215
216 IF l_currency_id = 'FII_GLOBAL1' THEN
217 p_parameter_rec.currency_code := bis_common_parameters.get_currency_code;
218 p_parameter_rec.rate_type := bis_common_parameters.get_rate_type;
219 ELSIF l_currency_id = 'FII_GLOBAL2' THEN
220 p_parameter_rec.currency_code := bis_common_parameters.get_secondary_currency_code;
221 p_parameter_rec.rate_type := bis_common_parameters.get_secondary_rate_type;
222 END IF;
223
224 /* Get the top supervisor id */
225 ELSIF p_page_parameter_tbl(i).parameter_name='HRI_PERSON+HRI_PER_USRDR_H' THEN
226 p_parameter_rec.peo_supervisor_id :=
227 ltrim(rtrim(p_page_parameter_tbl(i).parameter_id,''''),'''');
228 IF is_parameter_set(p_page_parameter_tbl(i).parameter_id) THEN
229 l_add_to_bind_table := TRUE;
230 END IF;
231
232 /* HRI Parameters */
233 /**********************/
234
235 /* Get the supervisor rollup type */
236 ELSIF p_page_parameter_tbl(i).parameter_name = 'HRI_PRSNDMGR+HRI_PDG_GENDER_X' THEN
237 p_parameter_rec.peo_sup_rollup_flag :=
238 ltrim(rtrim(p_page_parameter_tbl(i).parameter_id,''''),'''');
239 /* If All (no parameter value selected) is passed then default to Yes (rollup) */
240 IF (p_parameter_rec.peo_sup_rollup_flag IS NULL OR
241 p_parameter_rec.peo_sup_rollup_flag = '-1') THEN
242 p_parameter_rec.peo_sup_rollup_flag := 'Y';
243 END IF;
244
245 /* Checks for separation type */
246 ELSIF p_page_parameter_tbl(i).parameter_name = 'HRI_WRKACTVT+HRI_WAC_SEPCAT_X' THEN
247 p_parameter_rec.event_sep_type :=
248 ltrim(rtrim(p_page_parameter_tbl(i).parameter_id,''''),'''');
249 IF is_parameter_set(p_page_parameter_tbl(i).parameter_id) THEN
250 l_add_to_bind_table := TRUE;
251 END IF;
252
253 /* Checks for worker type */
254 ELSIF (substr(p_page_parameter_tbl(i).parameter_name, 1, 12) = 'HRI_PRSNTYP+') THEN
255 p_parameter_rec.wkth_wktyp_sk_fk :=
256 ltrim(rtrim(p_page_parameter_tbl(i).parameter_id,''''),'''');
257 IF is_parameter_set(p_page_parameter_tbl(i).parameter_id) THEN
258 p_bind_tab(p_page_parameter_tbl(i).parameter_name).pmv_bind_string :=
259 '''' || p_parameter_rec.wkth_wktyp_sk_fk || '''';
260 p_bind_tab(p_page_parameter_tbl(i).parameter_name).sql_bind_string :=
261 '''' || p_parameter_rec.wkth_wktyp_sk_fk || '''';
262 END IF;
263
264 -- START OF NEW ABSENCE PARAMETERS
265 /* Checks for Absence Duration UOM */
266 ELSIF p_page_parameter_tbl(i).parameter_name = 'HRI_ABSNC_M+HRI_ABSNC_M_DRTN_UOM' THEN
267 p_parameter_rec.absence_duration_uom :=
268 ltrim(rtrim(p_page_parameter_tbl(i).parameter_id,''''),'''');
269 IF is_parameter_set(p_page_parameter_tbl(i).parameter_id) THEN
270 l_add_to_bind_table := TRUE;
271 END IF;
272
273 /* Checks for Absence Category */
274 ELSIF p_page_parameter_tbl(i).parameter_name = 'HRI_ABSNC+HRI_ABSNC_CAT' THEN
275 p_parameter_rec.absence_category :=
276 ltrim(rtrim(p_page_parameter_tbl(i).parameter_id,''''),'''');
277 IF is_parameter_set(p_page_parameter_tbl(i).parameter_id) THEN
278 l_add_to_bind_table := TRUE;
279 END IF;
280
281
282 /* Checks for Absence Type */
283 ELSIF p_page_parameter_tbl(i).parameter_name = 'HRI_ABSNC+HRI_ABSNC_TYP' THEN
284 p_parameter_rec.absence_type :=
285 ltrim(rtrim(p_page_parameter_tbl(i).parameter_id,''''),'''');
286 IF is_parameter_set(p_page_parameter_tbl(i).parameter_id) THEN
287 l_add_to_bind_table := TRUE;
288 END IF;
289
290
291 /* Checks for Absence Reason */
292 ELSIF p_page_parameter_tbl(i).parameter_name = 'HRI_ABSNC+HRI_ABSNC_RSN' THEN
293 p_parameter_rec.absence_reason :=
294 ltrim(rtrim(p_page_parameter_tbl(i).parameter_id,''''),'''');
295 IF is_parameter_set(p_page_parameter_tbl(i).parameter_id) THEN
296 l_add_to_bind_table := TRUE;
297 END IF;
298
299 -- END OF NEW ABSENCE PARAMETERS
300
301 /* Multi-select dimension levels */
302 /*********************************/
303
304 ELSIF (substr(p_page_parameter_tbl(i).parameter_name, 1, 10) = 'GEOGRAPHY+'
305 OR substr(p_page_parameter_tbl(i).parameter_name, 1, 4) = 'JOB+'
306 OR p_page_parameter_tbl(i).parameter_name = 'HRI_LOW+HRI_LOW_BAND_X'
307 OR p_page_parameter_tbl(i).parameter_name = 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X'
308 OR p_page_parameter_tbl(i).parameter_name = 'HRI_REASON+HRI_RSN_SEP_X') THEN
309 IF is_parameter_set(p_page_parameter_tbl(i).parameter_id) THEN
310 l_add_to_bind_table := TRUE;
311 END IF;
312
313 END IF;
314
315 /* Add the parameter information to the debug output */
316 p_parameter_rec.debug_header := p_parameter_rec.debug_header || '-- ' ||
317 p_page_parameter_tbl(i).parameter_name || ': ' ||
318 p_page_parameter_tbl(i).parameter_id || ' -> ' ||
319 p_page_parameter_tbl(i).parameter_value || g_rtn;
320
321 /* Add the parameter details to the parameter table if the parameter is set */
322 IF l_add_to_bind_table THEN
323 p_bind_tab(p_page_parameter_tbl(i).parameter_name).pmv_bind_string :=
324 hri_mtdt_param.g_param_mtdt_tab
325 (p_page_parameter_tbl(i).parameter_name).pmv_bind_string;
326 p_bind_tab(p_page_parameter_tbl(i).parameter_name).sql_bind_string :=
327 p_page_parameter_tbl(i).parameter_id;
328 END IF;
329
330 END LOOP;
331
332 /* Add required parameters to bind table */
333 fill_in_bind_table
334 (p_parameter_rec => p_parameter_rec,
335 p_bind_tab => p_bind_tab);
336
337 /* Add FND_GLOBAL variables we depend on in DBI to the debug header */
338 p_parameter_rec.debug_header := p_parameter_rec.debug_header || '-- ' ||
339 'FND_GLOBAL.EMPLOYEE_ID: ' || FND_GLOBAL.EMPLOYEE_ID || g_rtn;
340
341 END get_parameters_from_table;
342
343 END hri_oltp_pmv_util_param;