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_DTL

Source


1 PACKAGE BODY hri_oltp_pmv_abs_dtl AS
2 /* $Header: hriopabsdt.pkb 120.9 2008/01/06 10:11:40 vjaganat noship $ */
3 
4 g_rtn                VARCHAR2(30) := '
5 ';
6 
7 g_unassigned         VARCHAR2(50) := HRI_OLTP_VIEW_MESSAGE.get_unassigned_msg;
8 
9 g_abs_cat_cl_view    VARCHAR2(30) :=
10      hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab('HRI_ABSNC+HRI_ABSNC_CAT').viewby_table;
11 g_abs_rsn_cl_view    VARCHAR2(30) :=
12      hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab('HRI_ABSNC+HRI_ABSNC_RSN').viewby_table;
13 g_abs_per_per_cl_view    VARCHAR2(30) := 'hri_dbi_cl_per_n_v';
14 g_abs_per_sup_cl_view    VARCHAR2(30) := 'hri_dbi_cl_per_n_v';
15 
16 /******************************************************************************/
17 /* Absence detail for Staff = Directs                                         */
18 /******************************************************************************/
19 PROCEDURE get_abs_detail_directs
20   (p_parameter_rec  IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
21    p_bind_tab       IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
22    p_lnk_emp_name   IN VARCHAR2,
23    p_lnk_mgr_name   IN VARCHAR2,
24    x_custom_sql     OUT NOCOPY VARCHAR2,
25    x_custom_output  OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
26 
27 /* Variable to return SQL query for report */
28   l_sqltext             VARCHAR2(32000);
29   l_params_header       VARCHAR2(10000);
30   l_lnk_profile_chk     VARCHAR2(4000);
31 
32 /* Table of custom parameters */
33   l_custom_rec          BIS_QUERY_ATTRIBUTES;
34 
35 /* Security clause */
36   l_security_clause     VARCHAR2(1000);
37 
38 /* Variables for dynamic part of SQL query */
39   l_parameter_name      VARCHAR2(100);
40   l_column              VARCHAR2(100);
41   l_where_clause        VARCHAR2(1000);
42 
43   -- functional decision (JC) default to days
44   l_dynmc_drtn_prd      VARCHAR2(100) := 'factM.abs_drtn_days_prd';
45   l_dynmc_drtn          VARCHAR2(100) := 'abs_cs.abs_drtn_days';
46 
47 BEGIN
48 
49 /* Initialize table/record variables */
50   l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
51   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
52 
53 /* Set security clause */
54   l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
55 
56 /* Loop through parameters that have been set */
57   l_parameter_name := p_bind_tab.FIRST;
58 
59   WHILE (l_parameter_name IS NOT NULL) LOOP
60 
61     IF (l_parameter_name = 'HRI_ABSNC+HRI_ABSNC_CAT' ) THEN
62 
63     /* Dynamically set conditions for parameter */
64       l_where_clause := l_where_clause ||
65 'AND abs_cs.' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
66                  (l_parameter_name).fact_viewby_col  ||
67 ' IN (' || p_bind_tab(l_parameter_name).pmv_bind_string || ')' || g_rtn;
68 
69     END IF;
70 
71   /* Move to next parameter */
72     l_parameter_name := p_bind_tab.NEXT(l_parameter_name);
73   END LOOP;
74 
75   /* formulate the dynmaic column selection based on Absence Duration
76      unit of measure profile option                                    */
77     IF (hri_bpl_utilization.get_abs_durtn_profile_vl = 'DAYS') THEN
78          l_dynmc_drtn_prd := 'factM.abs_drtn_days_prd';
79          l_dynmc_drtn     := 'abs_cs.abs_drtn_days';
80     ELSIF (hri_bpl_utilization.get_abs_durtn_profile_vl = 'HOURS') THEN
81          l_dynmc_drtn_prd := 'factM.abs_drtn_hrs_prd';
82          l_dynmc_drtn     := 'abs_cs.abs_drtn_hrs';
83     ELSE -- functional decision (JC) default to days
84          l_dynmc_drtn_prd := 'factM.abs_drtn_days_prd';
85          l_dynmc_drtn     := 'abs_cs.abs_drtn_days';
86     END IF;
87 
88 
89 
90 /* Formulate query */
91   l_sqltext :=
92 'SELECT -- Employee Absence Detail (Direct Reports)' || g_rtn ||
93 ' peo.value                VIEWBY ' || g_rtn ||
94 ',peo.order_by             HRI_P_ORDER_BY_1 ' || g_rtn ||
95 ',peo.value                HRI_P_PER_LNAME_CN ' || g_rtn ||
96 ',peo.id                   HRI_P_PER_ID ' || g_rtn ||
97 ',''' || p_lnk_emp_name || ''' HRI_P_DRILL_URL1' || g_rtn ||
98 ',sup.value                HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
99 ',sup.id                   HRI_P_SUP_ID ' || g_rtn ||
100 ',''' || p_lnk_mgr_name || ''' HRI_P_DRILL_URL2' || g_rtn ||
101 ',abs_cs.abs_start_date    HRI_P_DATE1_GA' || g_rtn ||
102 ',DECODE(abs_cs.abs_end_date,
103            to_date(''' || to_char(hr_general.end_of_time, 'DD-MM-YYYY') ||
104                    ''', ''DD-MM-YYYY''), to_date(NULL),
105          abs_cs.abs_end_date
106         )      HRI_P_DATE2_GA' || g_rtn ||
107 ',abs_cat.value            HRI_P_CHAR1_GA' || g_rtn ||
108 ',pabstyp.name             HRI_P_CHAR2_GA' || g_rtn ||
109 ',abs_rsn.value            HRI_P_CHAR3_GA' || g_rtn ||
110 ','||l_dynmc_drtn_prd||'   HRI_P_MEASURE1' || g_rtn ||
111 ','||l_dynmc_drtn||'       HRI_P_MEASURE2' || g_rtn ||
112 'FROM
113 -- inner query
114  (SELECT /*+ NO_MERGE */
115    fact.abs_person_id
116   ,suph.sup_person_id             supervisor_person_id
117   ,fact.absence_sk_fk
118   ,abs_cs.absence_category_code   absence_category_code
119   ,SUM(fact.abs_drtn_days)        abs_drtn_days_prd
120   ,SUM(fact.abs_drtn_hrs)         abs_drtn_hrs_prd
121   FROM hri_mb_utl_absnc_ct  fact
122       ,hri_cs_suph          suph
123       ,HRI_CS_ABSENCE_CT    abs_cs
124   WHERE suph.sup_person_id = &HRI_PERSON+HRI_PER_USRDR_H
125   AND fact.effective_date BETWEEN suph.effective_start_date
126                           AND suph.effective_end_date
127   AND suph.sub_invalid_flag_code = ''N''
128   AND suph.sub_relative_level = 1
129   AND suph.sub_person_id = fact.abs_person_id
130   AND fact.absence_sk_fk = abs_cs.absence_sk_pk
131   AND fact.abs_person_id = abs_cs.abs_person_id
132   AND fact.effective_date BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
133                           AND &BIS_CURRENT_EFFECTIVE_END_DATE
134 -- dynamic where conditions' || g_rtn ||
135 l_where_clause|| g_rtn ||
136 '-- end of dynamic where conditions
137   GROUP BY
138    fact.abs_person_id
139   ,suph.sup_person_id
140   ,fact.absence_sk_fk
141   ,abs_cs.absence_category_code
142  ) factM
143 , hri_cs_absence_ct abs_cs
144 , per_absence_attendance_types pabstyp
145 , hri_cl_absnc_cat_v abs_cat
146 , hri_cl_absnc_rsn_v abs_rsn
147 , hri_dbi_cl_per_n_v peo
148 , hri_dbi_cl_per_n_v sup
149 , per_all_assignments_f asg
150 WHERE
151     factM.absence_sk_fk = abs_cs.absence_sk_pk
152 AND abs_cs.absence_attendance_type_id  = pabstyp.absence_attendance_type_id
153 AND abs_cs.absence_category_code = abs_cat.id
154 AND abs_cs.absence_reason_code = abs_rsn.id
155 AND factM.abs_person_id = peo.id
156 AND &BIS_CURRENT_ASOF_DATE BETWEEN peo.start_date and peo.end_date
157 AND factM.abs_person_id = asg.person_id
158 AND abs_cs.abs_start_date BETWEEN asg.effective_start_date and asg.effective_end_date
159 AND asg.primary_flag = ''Y''
160 AND asg.supervisor_id = sup.id
161 AND &BIS_CURRENT_ASOF_DATE BETWEEN sup.start_date and sup.end_date' || g_rtn ||
162  l_security_clause || g_rtn ||
163 '&ORDER_BY_CLAUSE ';
164 
165   x_custom_sql := l_sqltext;
166 
167 END get_abs_detail_directs;
168 
169 /******************************************************************************/
170 /* Absence (Employee) Detail for Staff = All                                  */
171 /******************************************************************************/
172 PROCEDURE get_abs_detail_all
173   (p_parameter_rec  IN hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE,
174    p_bind_tab       IN hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE,
175    p_lnk_emp_name   IN VARCHAR2,
176    p_lnk_mgr_name   IN VARCHAR2,
177    x_custom_sql     OUT NOCOPY VARCHAR2,
178    x_custom_output  OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
179 
180 /* Variable to return SQL query for report */
181   l_sqltext             VARCHAR2(32000);
182   l_params_header       VARCHAR2(10000);
183   l_lnk_profile_chk     VARCHAR2(4000);
184 
185 /* Table of custom parameters */
186   l_custom_rec          BIS_QUERY_ATTRIBUTES;
187 
188 /* Security clause */
189   l_security_clause     VARCHAR2(1000);
190 
191 /* Variables for dynamic part of SQL query */
192   l_parameter_name      VARCHAR2(100);
193   l_where_clause        VARCHAR2(1000);
194 
195    -- functional decision (JC) default to days
196   l_dynmc_drtn_prd      VARCHAR2(100) := 'factM.abs_drtn_days_prd';
197   l_dynmc_drtn          VARCHAR2(100) := 'abs_cs.abs_drtn_days';
198 
199 BEGIN
200 
201 /* Initialize table/record variables */
202   l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
203   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
204 
205 /* Set security clause */
206   l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
207 
208 /* Loop through parameters that have been set */
209   l_parameter_name := p_bind_tab.FIRST;
210 
211   WHILE (l_parameter_name IS NOT NULL) LOOP
212 
213     IF (l_parameter_name = 'HRI_ABSNC+HRI_ABSNC_CAT' ) THEN
214 
215     /* Dynamically set conditions for parameter */
216       l_where_clause := l_where_clause ||
217 'AND fact.' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
218                     (l_parameter_name).fact_viewby_col  ||
219 ' IN (' || p_bind_tab(l_parameter_name).pmv_bind_string || ')' || g_rtn;
220 
221     END IF;
222 
223   /* Move to next parameter */
224     l_parameter_name := p_bind_tab.NEXT(l_parameter_name);
225   END LOOP;
226 
227   /* formulate the dynmaic column selection based on Absence Duration
228      unit of measure profile option                                    */
229     IF (hri_bpl_utilization.get_abs_durtn_profile_vl = 'DAYS') THEN
230          l_dynmc_drtn_prd := 'factM.abs_drtn_days_prd';
231          l_dynmc_drtn     := 'abs_cs.abs_drtn_days';
232     ELSIF (hri_bpl_utilization.get_abs_durtn_profile_vl = 'HOURS') THEN
233          l_dynmc_drtn_prd := 'factM.abs_drtn_hrs_prd';
234          l_dynmc_drtn     := 'abs_cs.abs_drtn_hrs';
235     ELSE -- functional decision (JC) default to days
236          l_dynmc_drtn_prd := 'factM.abs_drtn_days_prd';
237          l_dynmc_drtn     := 'abs_cs.abs_drtn_days';
238     END IF;
239 
240 
241 /* Formulate query */
242   l_sqltext :=
243 'SELECT -- Employee Absence Detail (All Staff)' || g_rtn ||
244 ' peo.value                VIEWBY ' || g_rtn ||
245 ',peo.order_by             HRI_P_ORDER_BY_1 ' || g_rtn ||
246 ',peo.value                HRI_P_PER_LNAME_CN ' || g_rtn ||
247 ',peo.id                   HRI_P_PER_ID ' || g_rtn ||
248 ',''' || p_lnk_emp_name || ''' HRI_P_DRILL_URL1' || g_rtn ||
249 ',sup.value                HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
250 ',sup.id                   HRI_P_SUP_ID ' || g_rtn ||
251 ',''' || p_lnk_mgr_name || ''' HRI_P_DRILL_URL2' || g_rtn ||
252 ',abs_cs.abs_start_date    HRI_P_DATE1_GA' || g_rtn ||
253 ',DECODE(abs_cs.abs_end_date,
254            to_date(''' || to_char(hr_general.end_of_time, 'DD-MM-YYYY') ||
255                    ''', ''DD-MM-YYYY''), to_date(NULL),
256          abs_cs.abs_end_date
257         )      HRI_P_DATE2_GA' || g_rtn ||
258 ',abs_cat.value            HRI_P_CHAR1_GA' || g_rtn ||
259 ',pabstyp.name             HRI_P_CHAR2_GA' || g_rtn ||
260 ',abs_rsn.value            HRI_P_CHAR3_GA' || g_rtn ||
261 ','||l_dynmc_drtn_prd||'   HRI_P_MEASURE1' || g_rtn ||
262 ','||l_dynmc_drtn||'       HRI_P_MEASURE2' || g_rtn ||
263 'FROM
264 -- inner query
265  (SELECT /*+ NO_MERGE */
266    fact.abs_person_id
267   ,fact.supervisor_person_id
268   ,fact.absence_sk_fk
269   ,SUM(fact.abs_drtn_days)        abs_drtn_days_prd
270   ,SUM(fact.abs_drtn_hrs)         abs_drtn_hrs_prd
271   FROM
272    hri_mdp_sup_absnc_occ_ct fact
273   WHERE fact.supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
274   AND fact.effective_date BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
275                           AND &BIS_CURRENT_EFFECTIVE_END_DATE
276 -- dynamic where conditions' || g_rtn ||
277 l_where_clause|| g_rtn ||
278 '-- end of dynamic where conditions
279   GROUP BY
280    fact.abs_person_id
281   ,fact.supervisor_person_id
282   ,fact.absence_sk_fk
283  ) factM
284  -- end of inner query
285 , hri_cs_absence_ct abs_cs
286 , per_absence_attendance_types pabstyp
287 , hri_cl_absnc_cat_v abs_cat
288 , hri_cl_absnc_rsn_v abs_rsn
289 , hri_dbi_cl_per_n_v peo
290 , per_all_assignments_f asg
291 , hri_dbi_cl_per_n_v sup
292 WHERE factM.absence_sk_fk = abs_cs.absence_sk_pk
293 AND abs_cs.absence_attendance_type_id  = pabstyp.absence_attendance_type_id
294 AND abs_cs.absence_category_code = abs_cat.id
295 AND abs_cs.absence_reason_code = abs_rsn.id
296 AND factM.abs_person_id = peo.id
297 AND &BIS_CURRENT_ASOF_DATE BETWEEN peo.start_date and peo.end_date
298 AND factM.abs_person_id = asg.person_id
299 AND abs_cs.abs_start_date BETWEEN asg.effective_start_date and asg.effective_end_date
300 AND asg.primary_flag = ''Y''
301 AND asg.supervisor_id = sup.id
302 AND abs_cs.abs_start_date BETWEEN sup.start_date and sup.end_date' || g_rtn ||
303  l_security_clause || g_rtn ||
304 '&ORDER_BY_CLAUSE ';
305 
306   x_custom_sql := l_sqltext;
307 
308 END get_abs_detail_all;
309 
310 /******************************************************************************/
311 /* Absence Detail report                                                      */
312 /******************************************************************************/
313 PROCEDURE GET_ABS_DETAIL(p_page_parameter_tbl IN  BIS_PMV_PAGE_PARAMETER_TBL,
314                           x_custom_sql         OUT NOCOPY VARCHAR2,
315                           x_custom_output      OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
316 
317 /* Parameter values */
318   l_parameter_rec        hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
319   l_bind_tab             hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
320 
321 /* Variables for dynamic part of SQL query */
322   l_lnk_profile_chk     VARCHAR2(4000);
323   l_lnk_emp_name        VARCHAR2(4000);
324   l_lnk_mgr_name        VARCHAR2(4000);
325 
326 BEGIN
327 
328 /* Get common parameter values */
329   hri_oltp_pmv_util_param.get_parameters_from_table
330         (p_page_parameter_tbl  => p_page_parameter_tbl,
331          p_parameter_rec       => l_parameter_rec,
332          p_bind_tab            => l_bind_tab);
333 
334 /* Activite Drill URL for Link to HR Employee Directory */
335   l_lnk_profile_chk := hri_oltp_pmv_util_pkg.chk_emp_dir_lnk
336                         (p_parameter_rec  => l_parameter_rec
337                         ,p_bind_tab       => l_bind_tab);
338 
339   IF (l_lnk_profile_chk = 1 AND
340       l_parameter_rec.time_curr_end_date = TRUNC(SYSDATE)) THEN
341     l_lnk_emp_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_PER_ID&OAPB=FII_HR_BRAND_TEXT';
342     l_lnk_mgr_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_SUP_ID&OAPB=FII_HR_BRAND_TEXT';
343   END IF ;
344 
345 /* Check supervisor hierarchy rollup */
346   IF (l_parameter_rec.peo_sup_rollup_flag = 'N') THEN
347 
348   /* Call directs only function to get the SQL */
349     get_abs_detail_directs
350      (p_parameter_rec => l_parameter_rec,
351       p_bind_tab      => l_bind_tab,
352       p_lnk_emp_name  => l_lnk_emp_name,
353       p_lnk_mgr_name  => l_lnk_mgr_name,
354       x_custom_sql    => x_custom_sql,
355       x_custom_output => x_custom_output);
356 
357   ELSE
358 
359   /* Call all staff function to get the SQL */
360     get_abs_detail_all
361      (p_parameter_rec => l_parameter_rec,
362       p_bind_tab      => l_bind_tab,
363       p_lnk_emp_name  => l_lnk_emp_name,
364       p_lnk_mgr_name  => l_lnk_mgr_name,
365       x_custom_sql    => x_custom_sql,
366       x_custom_output => x_custom_output);
367 
368   END IF;
369 
370 END get_abs_detail;
371 
372 END HRI_OLTP_PMV_ABS_DTL ;