[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_DTL_WRK_EVENT
Source
1 PACKAGE BODY HRI_OLTP_PMV_DTL_WRK_EVENT AS
2 /* $Header: hriopwev.pkb 120.17 2005/12/19 07:43:58 jrstewar noship $ */
3
4 g_rtn VARCHAR2(30) := '
5 ';
6
7 /******************************************************************************/
8 /* The following drills: */
9 /* - HIRE */
10 /* - TRANSFER IN */
11 /* - TRANSFER OUT */
12 /* - TERMINATION / TURNOVER */
13 /* in order to match the figure on the headcount/turnover portlets should be */
14 /* derived from the INSERTs into the table HRI_DBI_**_WMV_CHGS. This SQL can */
15 /* be found in the calc_events_** procedures of the headcount changes package */
16 /* HRI_DBI_WMV_CHANGES (hriwvch.pkb). */
17 /******************************************************************************/
18
19 /******************************************************************************/
20 /* Returns the number of hires which should match the figure where the drill */
21 /* came from because it runs off the same DBI summary table. */
22 /* */
23 /* All the lookup views are DBI specific for easy DBI maintenance */
24 /******************************************************************************/
25
26 PROCEDURE get_hire_detail2(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
27 x_custom_sql OUT NOCOPY VARCHAR2,
28 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
29
30 l_lnk_emp_name VARCHAR2(4000);
31 l_lnk_mgr_name VARCHAR2(4000);
32
33 l_select_clause VARCHAR2(4000);
34 l_from_clause VARCHAR2(4000);
35 l_where_clause VARCHAR2(4000);
36 l_orderby_clause VARCHAR2(4000);
37 l_security_clause VARCHAR2(4000);
38
39 l_lnk_profile_chk VARCHAR2(4000);
40
41 l_custom_rec BIS_QUERY_ATTRIBUTES;
42
43 /* Parameter values */
44 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
45 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
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 /* Get parameters into a pl/sql record l_param_rec */
54 hri_oltp_pmv_util_param.get_parameters_from_table
55 (p_page_parameter_tbl => p_param,
56 p_parameter_rec => l_parameter_rec,
57 p_bind_tab => l_bind_tab);
58
59 /* Activite Drill URL for Link to HR Employee Directory */
60 l_lnk_profile_chk := hri_oltp_pmv_util_pkg.chk_emp_dir_lnk(p_parameter_rec => l_parameter_rec
61 ,p_bind_tab => l_bind_tab);
62
63 IF (l_lnk_profile_chk = 1 AND l_parameter_rec.time_curr_end_date = TRUNC(SYSDATE)) THEN
64 l_lnk_emp_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_PER_ID&OAPB=FII_HR_BRAND_TEXT';
65 l_lnk_mgr_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_SUP_ID&OAPB=FII_HR_BRAND_TEXT';
66 ELSE
67 l_lnk_emp_name := '';
68 l_lnk_mgr_name := '';
69 END IF ;
70
71 /* Set WHERE clause for direct reports or not */
72 IF (l_parameter_rec.peo_sup_rollup_flag = 'Y' ) THEN
73
74 l_select_clause :=
75 'SELECT -- headcount hire detail' || g_rtn ||
76 /* View by name of person hired */
77 ' peo.value VIEWBY ' || g_rtn ||
78 /* Name of person hired */
79 ',peo.value HRI_P_PER_LNAME_CN ' || g_rtn ||
80 ',peo.id HRI_P_PER_ID ' || g_rtn ||
81 ',''' || l_lnk_emp_name || '''
82 HRI_P_DRILL_URL1' || g_rtn ||
83 /* Manager of person hired as of hire date */
84 ',sup.value HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
85 ',sup.id HRI_P_SUP_ID ' || g_rtn ||
86 ',''' || l_lnk_mgr_name || '''
87 HRI_P_DRILL_URL2' || g_rtn ||
88 /* Organization person hired into */
89 ',org.value HRI_P_ORG_CN ' || g_rtn ||
90 /* Country where person was hired */
91 ',ctr.value HRI_P_GEO_CTY_CN ' || g_rtn ||
92 /* Job person was hired into (using default display configuration) */
93 ',hri_bpl_job.get_job_display_name
94 (job.id
95 ,job.business_group_id
96 ,job.value) HRI_P_JOB_CN ' || g_rtn ||
97 /* Person type on hire date */
98 ', null HRI_P_CHAR1_GA ' || g_rtn ||
99 /* Hire Date converted to chars */
100 ',tch.effective_date HRI_P_DATE1_GA ' || g_rtn ||
101 /* Event Type - for future use */
102 ',null HRI_P_CHAR2_GA ' || g_rtn ||
103 /* Order by default name sort order */
104 ',peo.order_by HRI_P_ORDER_BY_1';
105
106 l_from_clause :=
107 'FROM hri_mdp_sup_wcnt_chg_asg_mv tch
108 , hri_mb_asgn_events_ct hri_asg
109 , hri_cs_geo_lochr_ct geo
110 , hri_dbi_cl_job_n_v job
111 , hri_dbi_cl_org_n_v org
112 , hri_dbi_cl_per_n_v sup
113 , hri_dbi_cl_per_n_v peo
114 , hri_dbi_cl_geo_country_v ctr';
115
116 l_where_clause :=
117 'WHERE
118 tch.supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
119 AND hri_asg.supervisor_id = sup.id
120 AND tch.person_id = peo.id
121 AND tch.assignment_id = hri_asg.assignment_id
122 AND tch.effective_date = hri_asg.effective_change_date
123 AND hri_asg.organization_id = org.id
124 AND hri_asg.job_id = job.id (+)
125 AND hri_asg.location_id = geo.location_id
126 AND geo.country_code = ctr.id
127 AND tch.change_type_id = 1
128 AND tch.hire_hdc > 0
129 AND tch.direct_record_ind = 0
130 AND &BIS_CURRENT_ASOF_DATE BETWEEN peo.effective_start_date AND peo.effective_end_date
131 AND &BIS_CURRENT_ASOF_DATE BETWEEN sup.effective_start_date AND sup.effective_end_date
132 AND tch.effective_date
133 BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE AND &BIS_CURRENT_EFFECTIVE_END_DATE';
134
135 ELSE
136
137 l_select_clause :=
138 'SELECT -- headcount hire detail' || g_rtn ||
139 /* View by name of person hired */
140 ' peo.value VIEWBY ' || g_rtn ||
141 /* Name of person hired */
142 ',peo.value HRI_P_PER_LNAME_CN ' || g_rtn ||
143 ',peo.id HRI_P_PER_ID ' || g_rtn ||
144 ',''' || l_lnk_emp_name || '''
145 HRI_P_DRILL_URL1' || g_rtn ||
146 /* Manager of person hired as of hire date */
147 ',sup.value HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
148 ',sup.id HRI_P_SUP_ID ' || g_rtn ||
149 ',''' || l_lnk_mgr_name || '''
150 HRI_P_DRILL_URL2' || g_rtn ||
151 /* Organization person hired into */
152 ',org.value HRI_P_ORG_CN ' || g_rtn ||
153 /* Country where person was hired */
154 ',ctr.value HRI_P_GEO_CTY_CN ' || g_rtn ||
155 /* Job person was hired into (using default display configuration) */
156 ',hri_bpl_job.get_job_display_name
157 (job.id
158 ,job.business_group_id
159 ,job.value) HRI_P_JOB_CN ' || g_rtn ||
160 /* Person type on hire date */
161 ', null HRI_P_CHAR1_GA ' || g_rtn ||
162 /* Hire Date converted to chars */
163 ',hri_asg.effective_change_date HRI_P_DATE1_GA ' || g_rtn ||
164 /* Event Type - for future use */
165 ',null HRI_P_CHAR2_GA ' || g_rtn ||
166 /* Order by default name sort order */
167 ',peo.order_by HRI_P_ORDER_BY_1';
168
169 l_from_clause :=
170 'FROM
171 hri_mb_asgn_events_ct hri_asg
172 , hri_cs_geo_lochr_ct geo
173 , hri_dbi_cl_job_n_v job
174 , hri_dbi_cl_org_n_v org
175 , hri_dbi_cl_per_n_v sup
176 , hri_dbi_cl_per_n_v peo
177 , hri_dbi_cl_geo_country_v ctr';
178
179 l_where_clause :=
180 'WHERE
181 hri_asg.supervisor_id = &HRI_PERSON+HRI_PER_USRDR_H
182 AND hri_asg.supervisor_id = sup.id
183 AND hri_asg.person_id = peo.id
184 AND hri_asg.organization_id = org.id
185 AND hri_asg.job_id = job.id (+)
186 AND hri_asg.location_id = geo.location_id
187 AND geo.country_code = ctr.id
188 AND (hri_asg.worker_hire_ind = 1
189 OR hri_asg.post_hire_asgn_start_ind = 1)
190 AND hri_asg.headcount > 0
191 AND hri_asg.summarization_rqd_ind = 1
192 AND &BIS_CURRENT_ASOF_DATE BETWEEN peo.effective_start_date AND peo.effective_end_date
193 AND &BIS_CURRENT_ASOF_DATE BETWEEN sup.effective_start_date AND sup.effective_end_date
194 AND hri_asg.effective_change_date
195 BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE AND &BIS_CURRENT_EFFECTIVE_END_DATE';
196
197 END IF; -- where clause
198
199 /* Check Whether the Report is being run in Emp or CWK Mode */
200 IF (l_parameter_rec.wkth_wktyp_sk_fk = 'EMP') THEN
201 l_where_clause := l_where_clause || g_rtn
202 ||'AND hri_asg.contingent_ind = 0';
203 ELSIF (l_parameter_rec.wkth_wktyp_sk_fk = 'CWK') THEN
204 l_where_clause := l_where_clause || g_rtn
205 || 'AND hri_asg.contingent_ind = 1';
206 ELSE
207 l_where_clause := l_where_clause || g_rtn
208 || 'AND 1 = 1';
209 END IF;
210
211 /* get security clause for Manager based security */
212 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
213 l_orderby_clause := '&ORDER_BY_CLAUSE';
214
215 x_custom_sql := l_select_clause || g_rtn
216 ||l_from_clause || g_rtn
217 ||l_where_clause || g_rtn
218 ||l_security_clause || g_rtn
219 ||l_orderby_clause;
220
221 END get_hire_detail2;
222
223 /**
224 * Returns the number of terms which should match the figure where the drill
225 * came from
226 *
227 * All the lookup views are DBI specific for easy DBI maintenance
228 **/
229 PROCEDURE get_term_detail2(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
230 x_custom_sql OUT NOCOPY VARCHAR2,
231 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
232
233 l_lnk_mgr_name VARCHAR2(4000);
234 l_select_clause VARCHAR2(4000);
235 l_from_clause VARCHAR2(4000);
236 l_where_clause VARCHAR2(4000);
237 l_orderby_clause VARCHAR2(4000);
238 l_security_clause VARCHAR2(4000);
239
240 l_lnk_profile_chk VARCHAR2(4000);
241
242 l_custom_rec BIS_QUERY_ATTRIBUTES;
243
244 /* Parameter values */
245 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
246 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
247
248 BEGIN
249
250 /* Initialize table/record variables */
251 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
252 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
253
254 /* Get parameters into a pl/sql record l_param_rec */
255 hri_oltp_pmv_util_param.get_parameters_from_table
256 (p_page_parameter_tbl => p_param,
257 p_parameter_rec => l_parameter_rec,
258 p_bind_tab => l_bind_tab);
259
260 /* Activite Drill URL for Link to HR Employee Directory */
261 l_lnk_profile_chk := hri_oltp_pmv_util_pkg.chk_emp_dir_lnk(p_parameter_rec => l_parameter_rec
262 ,p_bind_tab => l_bind_tab);
263
264 IF (l_lnk_profile_chk = 1 AND l_parameter_rec.time_curr_end_date = TRUNC(SYSDATE)) THEN
265 l_lnk_mgr_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_SUP_ID&OAPB=FII_HR_BRAND_TEXT';
266 ELSE
267 l_lnk_mgr_name := '';
268 END IF ;
269
270 /* Set WHERE clause for direct reports or not */
271 IF (l_parameter_rec.peo_sup_rollup_flag = 'Y' ) THEN
272
273 l_select_clause :=
274 'SELECT -- headcount terminations detail' || g_rtn ||
275 /* View by name of person terminated */
276 ' peo.value VIEWBY ' || g_rtn ||
277 /* Name of person terminated */
278 ',peo.value HRI_P_PER_LNAME_CN ' || g_rtn ||
279 /* Manager of person terminated as of termination date */
280 ',sup.value HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
281 ',sup.id HRI_P_SUP_ID ' || g_rtn ||
282 ',''' || l_lnk_mgr_name || '''
283 HRI_P_DRILL_URL1' || g_rtn ||
284 /* Organization person terminated from */
285 ',org.value HRI_P_ORG_CN ' || g_rtn ||
286 /* Country where person was terminated */
287 ',ctr.value HRI_P_GEO_CTY_CN ' || g_rtn ||
288 /* Job person was terminated from (using default display configuration) */
289 ',hri_bpl_job.get_job_display_name
290 (job.id
291 ,job.business_group_id
292 ,job.value) HRI_P_JOB_CN ' || g_rtn ||
293 /* Person type on termination date */
294 ','''' HRI_P_CHAR1_GA ' || g_rtn ||
295 /* bug 3147015 Most recent hire date of terminated person */
296 ',hri_asg.pow_start_date_adj HRI_P_DATE1_GA ' || g_rtn ||
297 /* Termination Date */
298 ',tch.effective_date-1 HRI_P_DATE2_GA ' || g_rtn ||
299 /* Termination Reason */
300 ',decode(hri_asg.worker_term_ind
301 , 1, hrl.meaning
302 , to_char(null)) HRI_P_CHAR2_GA ' || g_rtn ||
303 /* Period of work in years
304 Length of Service is defined as the number of years (in decimal format) between an employee's
305 most recent hire date and the event date (termination date)
306 */
307 ',DECODE(tch.wkth_wktyp_sk_fk,''EMP'',pow_days_on_event_date/365
308 ,DECODE(tch.wkth_wktyp_sk_fk,''CWK'',pow_months_on_event_date
309 ,0)
310 ) HRI_P_MEASURE1 '|| g_rtn ||
311 /* Performance Band */
312 ',prf.value HRI_P_CHAR3_GA ' || g_rtn ||
313 /* Event Type - for future use */
314 ',null HRI_P_CHAR4_GA' || g_rtn ||
315 /* Order by default person name sort order */
316 ',peo.order_by HRI_P_ORDER_BY_1 ';
317
318 l_from_clause :=
319 'FROM hri_mdp_sup_wcnt_chg_asg_mv tch
320 ,hri_mb_asgn_events_ct hri_asg
321 ,hri_cs_geo_lochr_ct geo
322 ,hri_cl_prfmnc_rtng_x_v prf
323 ,hri_dbi_cl_geo_country_v ctr
324 ,hri_dbi_cl_job_n_v job
325 ,hri_dbi_cl_org_n_v org
326 ,hri_dbi_cl_per_n_v sup
327 ,hri_dbi_cl_per_n_v peo
328 ,hr_standard_lookups hrl';
329
330 l_where_clause :=
331 'WHERE tch.supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
332 AND tch.change_type_id = 4
333 AND tch.termination_hdc > 0
334 AND hri_asg.summarization_rqd_ind = 1
335 AND tch.direct_record_ind = 0
336 AND tch.person_id = peo.id
337 AND tch.assignment_id = hri_asg.assignment_id
338 AND tch.effective_date = hri_asg.effective_change_date
339 AND hri_asg.supervisor_prv_id = sup.id
340 AND hri_asg.organization_prv_id = org.id
341 AND hri_asg.job_prv_id = job.id (+)
342 AND hri_asg.location_prv_id = geo.location_id
343 AND hri_asg.perf_band_prv = prf.id (+)
344 AND geo.country_code = ctr.id
345 AND tch.effective_date-1
346 BETWEEN peo.effective_start_date AND peo.effective_end_date
347 AND tch.effective_date-1
348 BETWEEN sup.effective_start_date AND sup.effective_end_date
349 AND tch.effective_date
350 BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE AND &BIS_CURRENT_EFFECTIVE_END_DATE
351 AND hri_asg.leaving_reason_code = hrl.lookup_code (+)';
352
353 ELSE
354
355 l_select_clause :=
356 'SELECT -- headcount terminations detail' || g_rtn ||
357 /* View by name of person terminated */
358 ' peo.value VIEWBY ' || g_rtn ||
359 /* Name of person terminated */
360 ',peo.value HRI_P_PER_LNAME_CN ' || g_rtn ||
361 /* Manager of person terminated as of termination date */
362 ',sup.value HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
363 ',sup.id HRI_P_SUP_ID ' || g_rtn ||
364 ',''' || l_lnk_mgr_name || '''
365 HRI_P_DRILL_URL1' || g_rtn ||
366 /* Organization person terminated from */
367 ',org.value HRI_P_ORG_CN ' || g_rtn ||
368 /* Country where person was terminated */
369 ',ctr.value HRI_P_GEO_CTY_CN ' || g_rtn ||
370 /* Job person was terminated from (using default display configuration) */
371 ',hri_bpl_job.get_job_display_name
372 (job.id
373 ,job.business_group_id
374 ,job.value) HRI_P_JOB_CN ' || g_rtn ||
375 /* Person type on termination date */
376 ',null HRI_P_CHAR1_GA ' || g_rtn ||
377 /* bug 3147015 Most recent hire date of terminated person */
378 ',hri_asg.pow_start_date_adj HRI_P_DATE1_GA ' || g_rtn ||
379 /* Termination Date */
380 ',hri_asg.effective_change_date-1 HRI_P_DATE2_GA ' || g_rtn ||
381 /* Termination Reason */
382 ',decode(hri_asg.worker_term_ind
383 , 1, hrl.meaning
384 , to_char(null)) HRI_P_CHAR2_GA ' || g_rtn ||
385 /* Period of work in years
386 Length of Service is defined as the number of years (in decimal format) between an employee's
387 most recent hire date and the event date (termination date).
388 */
389 ',DECODE(tch.wkth_wktyp_sk_fk,''EMP'',pow_days_on_event_date/365
390 ,DECODE(tch.wkth_wktyp_sk_fk,''CWK'',pow_months_on_event_date
391 ,0)
392 )
393 HRI_P_MEASURE1 '|| g_rtn ||
394 /* Performance Band */
395 ',prf.value HRI_P_CHAR3_GA ' ||
396 /* Event Type - for future use */
397 ',null HRI_P_CHAR4_GA' || g_rtn ||
398 /* Order by default person name sort order */
399 ',peo.order_by HRI_P_ORDER_BY_1 ' ;
400 l_from_clause :=
401 'FROM hri_mdp_sup_wcnt_chg_asg_mv tch
402 ,hri_mb_asgn_events_ct hri_asg
403 ,hri_cs_geo_lochr_ct geo
404 ,hri_dbi_cl_geo_country_v ctr
405 ,hri_dbi_cl_job_n_v job
406 ,hri_dbi_cl_org_n_v org
407 ,hri_dbi_cl_per_n_v sup
408 ,hri_dbi_cl_per_n_v peo
409 ,hri_cl_prfmnc_rtng_x_v prf
410 ,hr_standard_lookups hrl';
411
412 l_where_clause :=
413 'WHERE tch.supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
414 AND tch.change_type_id = 4
415 AND tch.termination_hdc > 0
416 AND hri_asg.summarization_rqd_ind = 1
417 AND tch.direct_record_ind = 1
418 AND tch.person_id = peo.id
419 AND tch.assignment_id = hri_asg.assignment_id
420 AND tch.effective_date = hri_asg.effective_change_date
421 AND hri_asg.supervisor_prv_id = sup.id
422 AND hri_asg.organization_prv_id = org.id
423 AND hri_asg.job_prv_id = job.id (+)
424 AND hri_asg.location_prv_id = geo.location_id
425 AND hri_asg.perf_band_prv = prf.id (+)
426 AND geo.country_code = ctr.id
427 AND tch.effective_date-1
428 BETWEEN peo.effective_start_date AND peo.effective_end_date
429 AND tch.effective_date-1
430 BETWEEN sup.effective_start_date AND sup.effective_end_date
431 AND tch.effective_date
432 BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE AND &BIS_CURRENT_EFFECTIVE_END_DATE
433 AND hri_asg.leaving_reason_code = hrl.lookup_code (+)';
434
435 END IF; -- where clause
436
437 /* Check Whether the Report is being run in Emp or CWK Mode */
438 IF (l_parameter_rec.wkth_wktyp_sk_fk = 'EMP') THEN
439 l_where_clause := l_where_clause || g_rtn
440 || 'AND tch.wkth_wktyp_sk_fk = ''EMP''' || g_rtn
441 || 'AND hrl.lookup_type (+) = ''LEAV_REAS''';
442 ELSIF (l_parameter_rec.wkth_wktyp_sk_fk = 'CWK') THEN
443 l_where_clause := l_where_clause || g_rtn
444 || 'AND tch.wkth_wktyp_sk_fk = ''CWK''' || g_rtn
445 || 'AND hrl.lookup_type (+) = ''HR_CWK_TERMINATION_REASONS''';
446 ELSE
447 l_where_clause := l_where_clause || g_rtn
448 || 'AND 1 = 1';
449 END IF;
450
451 /* get security clause for Manager based security */
452 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
453
454 l_orderby_clause := '&ORDER_BY_CLAUSE';
455
456 x_custom_sql := l_select_clause || g_rtn
457 ||l_from_clause || g_rtn
458 ||l_where_clause || g_rtn
459 ||l_security_clause || g_rtn
460 ||l_orderby_clause;
461
462
463 END get_term_detail2;
464
465 /**
466 * Returns the number of transfers in which should match the figure where the
467 * drill came from .
468 *
469 * All the lookup views are DBI specific for easy DBI maintenance
470 **/
471 PROCEDURE get_trans_in_detail2(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
472 x_custom_sql OUT NOCOPY VARCHAR2,
473 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
474
475 l_lnk_emp_name VARCHAR2(4000);
476 l_lnk_mgr_name VARCHAR2(4000);
477 l_select_clause VARCHAR2(4000);
478 l_from_clause VARCHAR2(4000);
479 l_where_clause VARCHAR2(4000);
480 l_orderby_clause VARCHAR2(4000);
481 l_security_clause VARCHAR2(4000);
482
483 l_lnk_profile_chk VARCHAR2(4000);
484
485 l_custom_rec BIS_QUERY_ATTRIBUTES;
486
487 /* Parameter values */
488 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
489 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
490
491 BEGIN
492
493 /* Initialize table/record variables */
494 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
495 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
496
497 /* Get parameters into a pl/sql record l_param_rec */
498 hri_oltp_pmv_util_param.get_parameters_from_table
499 (p_page_parameter_tbl => p_param,
500 p_parameter_rec => l_parameter_rec,
501 p_bind_tab => l_bind_tab);
502
503 /* Activite Drill URL for Link to HR Employee Directory */
504 l_lnk_profile_chk := hri_oltp_pmv_util_pkg.chk_emp_dir_lnk(p_parameter_rec => l_parameter_rec
505 ,p_bind_tab => l_bind_tab);
506
507 IF (l_lnk_profile_chk = 1 AND l_parameter_rec.time_curr_end_date = TRUNC(SYSDATE)) THEN
508 l_lnk_emp_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_PER_ID&OAPB=FII_HR_BRAND_TEXT';
509 l_lnk_mgr_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_SUP_ID&OAPB=FII_HR_BRAND_TEXT';
510 ELSE
511 l_lnk_emp_name := '';
512 l_lnk_mgr_name := '';
513 END IF ;
514
515 l_select_clause :=
516 'SELECT -- headcount transfers in detail' || g_rtn ||
517 /* View by name of person transferred */
518 ' peo.value VIEWBY ' || g_rtn ||
519 /* Name of person transferred */
520 ',peo.value HRI_P_PER_LNAME_CN ' || g_rtn ||
521 ',peo.id HRI_P_PER_ID ' || g_rtn ||
522 ',''' || l_lnk_emp_name || '''
523 HRI_P_DRILL_URL1' || g_rtn ||
524 /* Transfer to Manager */
525 ',sup.value HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
526 ',sup.id HRI_P_SUP_ID ' || g_rtn ||
527 ',''' || l_lnk_mgr_name || '''
528 HRI_P_DRILL_URL2' || g_rtn ||
529 /* Transfer from Organization */
530 ',org_prev.value HRI_P_ORG_CN ' || g_rtn ||
531 /* Transfer to Organization */
532 ',org.value HRI_P_CHAR1_GA ' || g_rtn ||
533 /* Transfer to country */
534 ',ctr.value HRI_P_GEO_CTY_CN ' || g_rtn ||
535 /* Transfer to job (using default display configuration) */
536 ',hri_bpl_job.get_job_display_name
537 (job.id
538 ,job.business_group_id
539 ,job.value) HRI_P_JOB_CN ' || g_rtn ||
540 /* Person type on hire date */
541 ',null HRI_P_CHAR2_GA ' || g_rtn ||
542 /* Most Recent Hire Date */
543 ', to_char(null) HRI_P_DATE1_GA ' || g_rtn ||
544 /* Transfer (In) Date */
545 ',tch.effective_date HRI_P_DATE2_GA ' || g_rtn ||
546 /* Event Type - for future use */
547 ',null HRI_P_CHAR3_GA ' || g_rtn ||
548 /* Order by default name sort order */
549 ',peo.order_by HRI_P_ORDER_BY_1 ';
550
551 l_from_clause :=
552 'FROM
553 hri_mb_asgn_events_ct asg_to
554 ,hri_mb_asgn_events_ct asg_from
555 ,hri_mdp_sup_wcnt_chg_asg_mv tch
556 ,hri_cs_geo_lochr_ct geo
557 ,hri_dbi_cl_geo_country_v ctr
558 ,hri_dbi_cl_job_n_v job
559 ,hri_dbi_cl_org_n_v org_prev
560 ,hri_dbi_cl_org_n_v org
561 ,hri_dbi_cl_per_n_v sup
562 ,hri_dbi_cl_per_n_v peo';
563
564 l_where_clause :=
565 'WHERE tch.supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
566 AND tch.effective_date
567 BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE AND &BIS_CURRENT_EFFECTIVE_END_DATE
568 AND tch.change_type_id = 2
569 AND tch.transfer_in_hdc > 0
570 AND tch.transfer_within_count = 1 -- to exclude transfers within
571 AND tch.assignment_id = asg_to.assignment_id
572 AND tch.effective_date BETWEEN asg_to.effective_change_date AND asg_to.effective_change_end_date
573 AND tch.assignment_id = asg_from.assignment_id
574 AND tch.effective_date-1 BETWEEN asg_from.effective_change_date AND asg_from.effective_change_end_date
575 AND asg_to.person_id = peo.id
576 AND tch.effective_date BETWEEN peo.effective_start_date AND peo.effective_end_date
577 AND asg_to.supervisor_id = sup.id -- manager transferred into
578 AND tch.effective_date BETWEEN sup.effective_start_date AND sup.effective_end_date
579 AND asg_from.organization_id = org_prev.id
580 AND asg_to.organization_id = org.id
581 AND asg_to.job_id = job.id (+)
582 AND asg_to.location_id = geo.location_id
583 AND geo.country_code = ctr.id ';
584
585 /* append to where clause a condition if for direct reports*/
586 IF (l_parameter_rec.peo_sup_rollup_flag = 'N' ) THEN
587 -- add condition to restrict to directs only
588 l_where_clause := l_where_clause || g_rtn
589 || 'AND tch.direct_record_ind = 1';
590 ELSE
591 l_where_clause := l_where_clause || g_rtn
592 || 'AND tch.direct_record_ind = 0';
593 END IF;
594
595 /* Check Whether the Report is being run in Emp or CWK Mode */
596 IF (l_parameter_rec.wkth_wktyp_sk_fk = 'EMP') THEN
597 l_where_clause := l_where_clause || g_rtn
598 ||'AND asg_to.contingent_ind = 0';
599 ELSIF (l_parameter_rec.wkth_wktyp_sk_fk = 'CWK') THEN
600 l_where_clause := l_where_clause || g_rtn
601 || 'AND asg_to.contingent_ind = 1';
602 ELSE
603 l_where_clause := l_where_clause || g_rtn
604 || 'AND 1 = 1';
605 END IF;
606
607 /* get security clause for Manager based security */
608 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
609
610 l_orderby_clause := '&ORDER_BY_CLAUSE';
611
612 x_custom_sql := l_select_clause || g_rtn
613 ||l_from_clause || g_rtn
614 ||l_where_clause || g_rtn
615 ||l_security_clause || g_rtn
616 ||l_orderby_clause;
617
618 END get_trans_in_detail2;
619
620 /**
621 * Returns the number of transfers out which should match the figure where the
622 * drill came from .
623 *
624 * All the lookup views are DBI specific for easy DBI maintenance
625 **/
626 PROCEDURE get_trans_out_detail2(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
627 x_custom_sql OUT NOCOPY VARCHAR2,
628 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
629
630 l_lnk_emp_name VARCHAR2(4000);
631 l_lnk_mgr_name VARCHAR2(4000);
632 l_select_clause VARCHAR2(4000);
633 l_from_clause VARCHAR2(4000);
634 l_where_clause VARCHAR2(4000);
635 l_orderby_clause VARCHAR2(4000);
636 l_security_clause VARCHAR2(4000);
637
638 l_lnk_profile_chk VARCHAR2(4000);
639
640 l_custom_rec BIS_QUERY_ATTRIBUTES;
641
642 /* Parameter values */
643 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
644 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
645
646 BEGIN
647
648 /* Initialize table/record variables */
649 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
650 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
651
652 /* Get parameters into a pl/sql record l_param_rec */
653 hri_oltp_pmv_util_param.get_parameters_from_table
654 (p_page_parameter_tbl => p_param,
655 p_parameter_rec => l_parameter_rec,
656 p_bind_tab => l_bind_tab);
657
658 /* Activite Drill URL for Link to HR Employee Directory */
659 l_lnk_profile_chk := hri_oltp_pmv_util_pkg.chk_emp_dir_lnk(p_parameter_rec => l_parameter_rec
660 ,p_bind_tab => l_bind_tab);
661
662 IF (l_lnk_profile_chk = 1 AND l_parameter_rec.time_curr_end_date = TRUNC(SYSDATE)) THEN
663 l_lnk_emp_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_PER_ID&OAPB=FII_HR_BRAND_TEXT';
664 l_lnk_mgr_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_SUP_ID&OAPB=FII_HR_BRAND_TEXT';
665 ELSE
666 l_lnk_emp_name := '';
667 l_lnk_mgr_name := '';
668 END IF ;
669
670 l_select_clause :=
671 'SELECT -- headcount transfers out detail' || g_rtn ||
672 /* View by name of person transferring out */
673 ' peo.value VIEWBY ' || g_rtn ||
674 /* Name of person transferred */
675 ',peo.value HRI_P_PER_LNAME_CN ' || g_rtn ||
676 ',peo.id HRI_P_PER_ID ' || g_rtn ||
677 ',''' || l_lnk_emp_name || '''
678 HRI_P_DRILL_URL1' || g_rtn ||
679 /* Transfer from manager */
680 ',sup.value HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
681 ',sup.id HRI_P_SUP_ID ' || g_rtn ||
682 ',''' || l_lnk_mgr_name || '''
683 HRI_P_DRILL_URL2' || g_rtn ||
684 /* Transfer from Organization */
685 ',org_prev.value HRI_P_ORG_CN ' || g_rtn ||
686 /* Transfer to Organization */
687 ',org.value HRI_P_CHAR1_GA ' || g_rtn ||
688 /* Transfer from Country */
689 ',ctr.value HRI_P_GEO_CTY_CN ' || g_rtn ||
690 /* Transfer from Job (using default display configuration) */
691 ',hri_bpl_job.get_job_display_name
692 (job.id
693 ,job.business_group_id
694 ,job.value) HRI_P_JOB_CN ' || g_rtn ||
695 /* Transfer from Person Type */
696 ', null HRI_P_CHAR2_GA ' || g_rtn ||
697 /* bug Most recent hire date of transferee */
698 ', to_char(null) HRI_P_DATE1_GA ' || g_rtn ||
699 /* Transfer Out Date */
700 ', tch.effective_date HRI_P_DATE2_GA ' || g_rtn ||
701 /* Period of work in years
702 Length of Service is defined as the number of years (in decimal format) between an employee's
703 most recent hire date up the event date (transfer out).
704 */
705 ',ROUND ( ( (asg_from.POW_DAYS_ON_EVENT_DATE +
706 (tch.effective_date - asg_from.EFFECTIVE_CHANGE_DATE)
707 )
708 /365),2)
709 HRI_P_MEASURE1 '|| g_rtn ||
710 /* Performance Band */
711 ',prf.value HRI_P_CHAR3_GA ' || g_rtn ||
712 /* Event Type - for future use */
713 ',null HRI_P_CHAR4_GA ' || g_rtn ||
714 /* Order by default person name sort order */
715 ',peo.order_by HRI_P_ORDER_BY_1 ';
716
717 l_from_clause :=
718 'FROM
719 hri_mb_asgn_events_ct asg_to
720 ,hri_mb_asgn_events_ct asg_from
721 ,hri_mdp_sup_wcnt_chg_asg_mv tch
722 ,hri_cs_geo_lochr_ct geo
723 ,hri_cl_prfmnc_rtng_x_v prf
724 ,hri_dbi_cl_geo_country_v ctr
725 ,hri_dbi_cl_job_n_v job
726 ,hri_dbi_cl_org_n_v org_prev
727 ,hri_dbi_cl_org_n_v org
728 ,hri_dbi_cl_per_n_v sup
729 ,hri_dbi_cl_per_n_v peo';
730
731 l_where_clause :=
732 'WHERE tch.supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
733 AND tch.effective_date
734 BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE AND &BIS_CURRENT_EFFECTIVE_END_DATE
735 AND tch.change_type_id = 3
736 AND tch.transfer_out_hdc > 0
737 AND tch.transfer_within_count = 1
738 AND tch.assignment_id = asg_to.assignment_id
739 AND tch.effective_date BETWEEN asg_to.effective_change_date AND asg_to.effective_change_end_date
740 AND tch.assignment_id = asg_from.assignment_id
741 AND tch.effective_date-1 BETWEEN asg_from.effective_change_date AND asg_from.effective_change_end_date
742 AND asg_to.person_id = peo.id
743 AND tch.effective_date BETWEEN peo.effective_start_date AND peo.effective_end_date
744 AND asg_from.supervisor_id = sup.id -- manager who had the transfer out
745 AND tch.effective_date BETWEEN sup.effective_start_date AND sup.effective_end_date
746 AND asg_from.organization_id = org_prev.id
747 AND asg_to.organization_id = org.id
748 AND asg_to.job_id = job.id (+)
749 AND asg_to.location_id = geo.location_id
750 AND asg_from.perf_band = prf.id (+)
751 AND geo.country_code = ctr.id ';
752
753 /* append to where clause a condition if for direct reports*/
754 IF (l_parameter_rec.peo_sup_rollup_flag = 'N' ) THEN
755 -- add condition to restrict to directs only
756 l_where_clause := l_where_clause || g_rtn
757 || 'AND tch.direct_record_ind = 1';
758 ELSE
759 l_where_clause := l_where_clause || g_rtn
760 || 'AND tch.direct_record_ind = 0';
761 END IF;
762
763 /* Check Whether the Report is being run in Emp or CWK Mode */
764 IF (l_parameter_rec.wkth_wktyp_sk_fk = 'EMP') THEN
765 l_where_clause := l_where_clause || g_rtn
766 ||'AND asg_from.contingent_ind = 0';
767 ELSIF (l_parameter_rec.wkth_wktyp_sk_fk = 'CWK') THEN
768 l_where_clause := l_where_clause || g_rtn
769 || 'AND asg_from.contingent_ind = 1';
770 ELSE
771 l_where_clause := l_where_clause || g_rtn
772 || 'AND 1 = 1';
773 END IF;
774
775 /* get security clause for Manager based security */
776 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
777
778 l_orderby_clause := '&ORDER_BY_CLAUSE';
779
780 x_custom_sql := l_select_clause || g_rtn
781 ||l_from_clause || g_rtn
782 ||l_where_clause || g_rtn
783 ||l_security_clause || g_rtn
784 ||l_orderby_clause;
785
786 END get_trans_out_detail2;
787
788
789 PROCEDURE get_turnover_detail2(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
790 x_custom_sql OUT NOCOPY VARCHAR2,
791 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
792 IS
793
794 l_custom_rec BIS_QUERY_ATTRIBUTES;
795
796 /* Parameter values */
797 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
798 l_parameter_name VARCHAR2(100);
799 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
800
801 /* Dynamic drill to emp dir */
802 l_lnk_mgr_name VARCHAR2(4000);
803 l_lnk_profile_chk VARCHAR2(4000);
804
805 /* Dynamic sql variables */
806 l_sql_stmt VARCHAR2(32000);
807 l_security_clause VARCHAR2(4000);
808 l_fact_conditions VARCHAR2(8000);
809
810 BEGIN
811
812 /* Initialize table/record variables */
813 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
814 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
815
816 /* Get parameters into a pl/sql record l_param_rec */
817 hri_oltp_pmv_util_param.get_parameters_from_table
818 (p_page_parameter_tbl => p_param,
819 p_parameter_rec => l_parameter_rec,
820 p_bind_tab => l_bind_tab);
821
822 /* Check Whether the Report is being run in Emp or CWK Mode */
823 IF (l_parameter_rec.wkth_wktyp_sk_fk = 'EMP') THEN
824 l_fact_conditions := l_fact_conditions ||
825 'AND fact.wkth_wktyp_sk_fk = ''EMP''' || g_rtn;
826 ELSIF (l_parameter_rec.wkth_wktyp_sk_fk = 'CWK') THEN
827 l_fact_conditions := l_fact_conditions ||
828 'AND fact.wkth_wktyp_sk_fk = ''CWK''' || g_rtn;
829 END IF;
830
831 /* Get security clause for Manager based security */
832 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
833
834 /* Activate Drill URL for Link to HR Employee Directory */
835 l_lnk_profile_chk := hri_oltp_pmv_util_pkg.chk_emp_dir_lnk
836 (p_parameter_rec => l_parameter_rec
837 ,p_bind_tab => l_bind_tab);
838
839 /* Drill only possible on current date */
840 IF (l_lnk_profile_chk = 1 AND l_parameter_rec.time_curr_end_date = TRUNC(SYSDATE)) THEN
841 l_lnk_mgr_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&' ||
842 'pId=HRI_P_SUP_ID&OAPB=FII_HR_BRAND_TEXT';
843 END IF;
844
845 /* Loop through parameters that have been set */
846 l_parameter_name := l_bind_tab.FIRST;
847
848 WHILE (l_parameter_name IS NOT NULL) LOOP
849 IF (l_parameter_name = 'GEOGRAPHY+COUNTRY' OR
850 l_parameter_name = 'GEOGRAPHY+AREA' OR
851 l_parameter_name = 'JOB+JOB_FAMILY' OR
852 l_parameter_name = 'JOB+JOB_FUNCTION' OR
853 l_parameter_name = 'HRI_PRFRMNC+HRI_PRFMNC_RTNG_X' OR
854 l_parameter_name = 'HRI_PRSNTYP+HRI_WKTH_WKTYP' OR
855 l_parameter_name = 'HRI_WRKACTVT+HRI_WAC_SEPCAT_X' OR
856 l_parameter_name = 'HRI_REASON+HRI_RSN_SEP_X' OR
857 l_parameter_name = 'HRI_LOW+HRI_LOW_BAND_X') THEN
858
859 /* Dynamically set conditions for parameter */
860 l_fact_conditions := l_fact_conditions ||
861 'AND fact.' || hri_mtdt_dim_lvl.g_dim_lvl_mtdt_tab
862 (l_parameter_name).fact_viewby_col ||
863 ' IN (' || l_bind_tab(l_parameter_name).pmv_bind_string || ')' || g_rtn;
864
865 END IF;
866
867 /* Move to next parameter */
868 l_parameter_name := l_bind_tab.NEXT(l_parameter_name);
869 END LOOP;
870
871 /* Add directs condition if rollup = 'N' */
872 IF (l_parameter_rec.peo_sup_rollup_flag = 'N') THEN
873 l_fact_conditions := l_fact_conditions ||
874 'AND fact.direct_ind = 1';
875 END IF;
876
877 l_sql_stmt :=
878 'SELECT -- turnover detail' || g_rtn ||
879 /* Order by default person name sort order */
880 ' peo.order_by HRI_P_ORDER_BY_1' || g_rtn ||
881 /* View by name of person terminated */
882 ',peo.id VIEWBYID' || g_rtn ||
883 ',peo.value VIEWBY' || g_rtn ||
884 /* Name of person terminated */
885 ',peo.value HRI_P_CHAR1_GA' || g_rtn ||
886 /* Manager of person terminated as of termination date */
887 ',sup.value HRI_P_CHAR2_GA' || g_rtn ||
888 ',sup.id HRI_P_SUP_ID' || g_rtn ||
889 ',''' || l_lnk_mgr_name || ''' HRI_P_DRILL_URL1' || g_rtn ||
890 /* Organization person terminated from */
891 ',org.value HRI_P_CHAR3_GA' || g_rtn ||
892 /* Country where person was terminated */
893 ',ctr.value HRI_P_CHAR4_GA' || g_rtn ||
894 /* Job person was terminated from (using default display configuration) */
895 ',hri_oltp_view_job.get_job_display_name
896 (job.id
897 ,job.business_group_id
898 ,job.value) HRI_P_CHAR5_GA' || g_rtn ||
899 /* Most recent hire date of terminated person */
900 ',fact.pow_start_date HRI_P_DATE1_GA' || g_rtn ||
901 /* Termination Date */
902 ',fact.effective_date - 1 HRI_P_DATE2_GA' || g_rtn ||
903 /* Termination Reason */
904 ',rsn.value HRI_P_CHAR6_GA' || g_rtn ||
905 /* Period of work in years
906 Length of Service is defined as the number of years (in decimal format) between an employee's
907 most recent hire date and the event_date (termination date). */
908 ',(fact.effective_date - fact.pow_start_date) / 365
909 HRI_P_MEASURE1' || g_rtn ||
910 /* Performance Band */
911 ',prf.value HRI_P_MEASURE2
912 FROM
913 hri_mdp_sup_wcnt_term_asg_mv fact
914 ,hri_cs_geo_lochr_ct geo
915 ,hri_dbi_cl_geo_country_v ctr
916 ,hri_dbi_cl_job_n_v job
917 ,hri_dbi_cl_org_n_v org
918 ,hri_dbi_cl_per_n_v sup
919 ,hri_dbi_cl_per_n_v peo
920 ,hri_cl_prfmnc_rtng_x_v prf
921 ,hri_cl_rsn_sep_x_v rsn
922 WHERE fact.supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
923 AND fact.separation_hdc > 0
924 AND fact.person_id = peo.id
925 AND fact.direct_supervisor_person_id = sup.id
926 AND fact.organization_id = org.id
927 AND fact.job_id = job.id (+)
928 AND fact.perf_band = prf.id (+)
929 AND fact.location_id = geo.location_id
930 AND fact.geo_country_code = ctr.id
931 AND fact.effective_date - 1 BETWEEN peo.effective_start_date
932 AND peo.effective_end_date
933 AND fact.effective_date - 1 BETWEEN sup.effective_start_date
934 AND sup.effective_end_date
935 AND fact.effective_date BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
936 AND &BIS_CURRENT_EFFECTIVE_END_DATE
937 AND fact.leaving_reason_code = rsn.id' || g_rtn ||
938 l_fact_conditions ||
939 '&ORDER_BY_CLAUSE';
940
941 x_custom_sql := l_sql_stmt;
942
943 END get_turnover_detail2;
944
945 PROCEDURE get_wf_trans_out_detail2(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
946 x_custom_sql OUT NOCOPY VARCHAR2,
947 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
948
949 l_lnk_emp_name VARCHAR2(4000);
950 l_lnk_mgr_name VARCHAR2(4000);
951 l_lnk_mgr_to_name VARCHAR2(4000);
952 l_select_clause VARCHAR2(4000);
953 l_from_clause VARCHAR2(4000);
954 l_where_clause VARCHAR2(4000);
955 l_orderby_clause VARCHAR2(4000);
956 l_security_clause VARCHAR2(4000);
957
958 l_lnk_profile_chk VARCHAR2(4000);
959
960 l_custom_rec BIS_QUERY_ATTRIBUTES;
961
962 /* Parameter values */
963 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
964 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
965
966 BEGIN
967
968 /* Initialize table/record variables */
969 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
970 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
971
972 /* Get parameters into a pl/sql record l_param_rec */
973 hri_oltp_pmv_util_param.get_parameters_from_table
974 (p_page_parameter_tbl => p_param,
975 p_parameter_rec => l_parameter_rec,
976 p_bind_tab => l_bind_tab);
977
978 /* Activite Drill URL for Link to HR Employee Directory */
979 l_lnk_profile_chk := hri_oltp_pmv_util_pkg.chk_emp_dir_lnk(p_parameter_rec => l_parameter_rec
980 ,p_bind_tab => l_bind_tab);
981
982 IF (l_lnk_profile_chk = 1 AND l_parameter_rec.time_curr_end_date = TRUNC(SYSDATE)) THEN
983 l_lnk_emp_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_PER_ID&OAPB=FII_HR_BRAND_TEXT';
984 l_lnk_mgr_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_SUP_ID&OAPB=FII_HR_BRAND_TEXT';
985 l_lnk_mgr_to_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_SUP_TO_ID&OAPB=FII_HR_BRAND_TEXT';
986 ELSE
987 l_lnk_emp_name := '';
988 l_lnk_mgr_name := '';
989 l_lnk_mgr_to_name := '';
990 END IF ;
991
992 l_select_clause :=
993 'SELECT -- Staff transfers out detail' || g_rtn ||
994 /* View by name of person transferring out */
995 ' peo.value VIEWBY ' || g_rtn ||
996 /* Name of person transferred */
997 ',peo.value HRI_P_PER_LNAME_CN ' || g_rtn ||
998 ',peo.id HRI_P_PER_ID ' || g_rtn ||
999 ',''' || l_lnk_emp_name || ''' HRI_P_DRILL_URL1' || g_rtn ||
1000 /* Transfer from manager */
1001 ',sup.value HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
1002 ',sup.id HRI_P_SUP_ID ' || g_rtn ||
1003 ',''' || l_lnk_mgr_name || ''' HRI_P_DRILL_URL2' || g_rtn ||
1004 /* Transfer To manager */
1005 ',supTo.value HRI_P_PER_SUP_TO_LNAME_CN ' || g_rtn ||
1006 ',supTo.id HRI_P_SUP_TO_ID ' || g_rtn ||
1007 ',''' || l_lnk_mgr_to_name || ''' HRI_P_DRILL_URL3' || g_rtn ||
1008 /* Transfer from Organization */
1009 ',org_prev.value HRI_P_ORG_CN' || g_rtn ||
1010 /* Transfer to Organization */
1011 ',org.value HRI_P_CHAR1_GA' || g_rtn ||
1012 /* Transfer from Country */
1013 ',ctr.value HRI_P_GEO_CTY_CN ' || g_rtn ||
1014 /* Transfer from Job (using default display configuration) */
1015 ',hri_bpl_job.get_job_display_name
1016 (job.id
1017 ,job.business_group_id
1018 ,job.value) HRI_P_JOB_CN' || g_rtn ||
1019 ',tch.transfer_out_hdc HRI_P_MEASURE1' || g_rtn ||
1020 ',prsnwtyp.value HRI_P_CHAR3_GA' || g_rtn ||
1021 ',tch.effective_date HRI_P_DATE2_GA' || g_rtn ||
1022 ',peo.order_by HRI_P_ORDER_BY_1 ';
1023
1024 l_from_clause :=
1025 'FROM
1026 hri_mb_asgn_events_ct asg_to
1027 ,hri_mb_asgn_events_ct asg_from
1028 ,hri_mdp_sup_wcnt_chg_asg_mv tch
1029 ,hri_cs_geo_lochr_ct geo
1030 ,hri_dbi_cl_geo_country_v ctr
1031 ,hri_dbi_cl_job_n_v job
1032 ,hri_dbi_cl_org_n_v org_prev
1033 ,hri_dbi_cl_org_n_v org
1034 ,hri_dbi_cl_per_n_v sup
1035 ,hri_dbi_cl_per_n_v supTo
1036 ,hri_cl_wkth_wktyp_v prsnwtyp
1037 ,hri_dbi_cl_per_n_v peo';
1038
1039 l_where_clause :=
1040 'WHERE tch.supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
1041 AND tch.effective_date
1042 BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE AND &BIS_CURRENT_EFFECTIVE_END_DATE
1043 AND tch.change_type_id = 3
1044 AND tch.transfer_within_count = 1
1045 AND tch.transfer_out_hdc > 0 -- headcount greater than zero restriction
1046 AND tch.assignment_id = asg_to.assignment_id
1047 AND tch.effective_date BETWEEN asg_to.effective_change_date AND asg_to.effective_change_end_date
1048 AND tch.assignment_id = asg_from.assignment_id
1049 AND tch.effective_date-1 BETWEEN asg_from.effective_change_date AND asg_from.effective_change_end_date
1050 AND asg_to.person_id = peo.id
1051 AND tch.effective_date BETWEEN peo.effective_start_date AND peo.effective_end_date
1052 AND asg_from.supervisor_id = sup.id -- manager who had the transfer out
1053 AND tch.effective_date BETWEEN sup.effective_start_date AND sup.effective_end_date
1054 AND asg_to.supervisor_id = supTo.id -- manager who had the transfer in
1055 AND tch.effective_date BETWEEN supTo.effective_start_date AND supTo.effective_end_date
1056 AND asg_from.organization_id = org_prev.id
1057 AND asg_to.organization_id = org.id
1058 AND asg_to.job_id = job.id (+)
1059 AND asg_to.location_id = geo.location_id
1060 AND geo.country_code = ctr.id
1061 AND tch.wkth_wktyp_sk_fk = prsnwtyp.id
1062 ';
1063
1064 /* append to where clause a condition if for direct reports*/
1065 IF (l_parameter_rec.peo_sup_rollup_flag = 'N' ) THEN
1066 -- add condition to restrict to directs only
1067 l_where_clause := l_where_clause || g_rtn
1068 || 'AND tch.direct_record_ind = 1';
1069 ELSE
1070 l_where_clause := l_where_clause || g_rtn
1071 || 'AND tch.direct_record_ind = 0';
1072 END IF;
1073
1074 /* Check Whether the Report is being run in Emp or CWK Mode */
1075 IF (l_parameter_rec.wkth_wktyp_sk_fk = 'EMP') THEN
1076 l_where_clause := l_where_clause || g_rtn
1077 ||'AND tch.wkth_wktyp_sk_fk = ''EMP''';
1078 ELSIF (l_parameter_rec.wkth_wktyp_sk_fk = 'CWK') THEN
1079 l_where_clause := l_where_clause || g_rtn
1080 || 'AND tch.wkth_wktyp_sk_fk = ''CWK''';
1081 ELSE
1082 l_where_clause := l_where_clause || g_rtn
1083 || 'AND 1 = 1';
1084 END IF;
1085
1086 /* get security clause for Manager based security */
1087 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
1088
1089 l_orderby_clause := '&ORDER_BY_CLAUSE';
1090
1091 x_custom_sql := l_select_clause || g_rtn
1092 ||l_from_clause || g_rtn
1093 ||l_where_clause || g_rtn
1094 ||l_security_clause || g_rtn
1095 ||l_orderby_clause;
1096
1097 END get_wf_trans_out_detail2;
1098
1099 PROCEDURE get_c_trans_out_detail2(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
1100 x_custom_sql OUT NOCOPY VARCHAR2,
1101 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
1102
1103 l_lnk_emp_name VARCHAR2(4000);
1104 l_lnk_mgr_name VARCHAR2(4000);
1105 l_lnk_mgr_to_name VARCHAR2(4000);
1106 l_select_clause VARCHAR2(4000);
1107 l_from_clause VARCHAR2(4000);
1108 l_where_clause VARCHAR2(4000);
1109 l_orderby_clause VARCHAR2(4000);
1110 l_security_clause VARCHAR2(4000);
1111
1112 l_lnk_profile_chk VARCHAR2(4000);
1113
1114 l_custom_rec BIS_QUERY_ATTRIBUTES;
1115
1116 /* Parameter values */
1117 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
1118 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
1119
1120 BEGIN
1121
1122 /* Initialize table/record variables */
1123 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
1124 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
1125
1126 /* Get parameters into a pl/sql record l_param_rec */
1127 hri_oltp_pmv_util_param.get_parameters_from_table
1128 (p_page_parameter_tbl => p_param,
1129 p_parameter_rec => l_parameter_rec,
1130 p_bind_tab => l_bind_tab);
1131
1132 /* Activite Drill URL for Link to HR Employee Directory */
1133 l_lnk_profile_chk := hri_oltp_pmv_util_pkg.chk_emp_dir_lnk(p_parameter_rec => l_parameter_rec
1134 ,p_bind_tab => l_bind_tab);
1135
1136 IF (l_lnk_profile_chk = 1 AND l_parameter_rec.time_curr_end_date = TRUNC(SYSDATE)) THEN
1137 l_lnk_emp_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_PER_ID&OAPB=FII_HR_BRAND_TEXT';
1138 l_lnk_mgr_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_SUP_ID&OAPB=FII_HR_BRAND_TEXT';
1139 l_lnk_mgr_to_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_SUP_TO_ID&OAPB=FII_HR_BRAND_TEXT';
1140 ELSE
1141 l_lnk_emp_name := '';
1142 l_lnk_mgr_name := '';
1143 l_lnk_mgr_to_name := '';
1144 END IF ;
1145
1146 l_select_clause :=
1147 'SELECT -- Staff transfers out detail' || g_rtn ||
1148 /* View by name of person transferring out */
1149 ' peo.value VIEWBY ' || g_rtn ||
1150 /* Name of person transferred */
1151 ',peo.value HRI_P_PER_LNAME_CN ' || g_rtn ||
1152 ',peo.id HRI_P_PER_ID ' || g_rtn ||
1153 ',''' || l_lnk_emp_name || ''' HRI_P_DRILL_URL1' || g_rtn ||
1154 /* Transfer from manager */
1155 ',sup.value HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
1156 ',sup.id HRI_P_SUP_ID ' || g_rtn ||
1157 ',''' || l_lnk_mgr_name || ''' HRI_P_DRILL_URL2' || g_rtn ||
1158 /* Transfer To manager */
1159 ',supTo.value HRI_P_PER_SUP_TO_LNAME_CN ' || g_rtn ||
1160 ',supTo.id HRI_P_SUP_TO_ID ' || g_rtn ||
1161 ',''' || l_lnk_mgr_to_name || ''' HRI_P_DRILL_URL3' || g_rtn ||
1162 /* Transfer from Organization */
1163 ',org_prev.value HRI_P_ORG_CN' || g_rtn ||
1164 /* Transfer to Organization */
1165 ',org.value HRI_P_CHAR1_GA' || g_rtn ||
1166 /* Transfer from Country */
1167 ',ctr.value HRI_P_GEO_CTY_CN ' || g_rtn ||
1168 /* Transfer from Job (using default display configuration) */
1169 ',hri_bpl_job.get_job_display_name
1170 (job.id
1171 ,job.business_group_id
1172 ,job.value) HRI_P_JOB_CN' || g_rtn ||
1173 ',tch.transfer_out_hdc HRI_P_MEASURE1' || g_rtn ||
1174 ',prsnwtyp.value HRI_P_CHAR3_GA' || g_rtn ||
1175 ',asg_to.pow_start_date_adj HRI_P_DATE1_GA' || g_rtn ||
1176 ',tch.effective_date HRI_P_DATE2_GA' || g_rtn ||
1177 ',ROUND ( ( (asg_from.POW_DAYS_ON_EVENT_DATE +
1178 (tch.effective_date - asg_from.EFFECTIVE_CHANGE_DATE)
1179 )
1180 /30.42),2) HRI_P_MEASURE2' || g_rtn ||
1181 ',peo.order_by HRI_P_ORDER_BY_1 ';
1182
1183 l_from_clause :=
1184 'FROM
1185 hri_mb_asgn_events_ct asg_to
1186 ,hri_mb_asgn_events_ct asg_from
1187 ,hri_mdp_sup_wcnt_chg_asg_mv tch
1188 ,hri_cs_geo_lochr_ct geo
1189 ,hri_dbi_cl_geo_country_v ctr
1190 ,hri_dbi_cl_job_n_v job
1191 ,hri_dbi_cl_org_n_v org_prev
1192 ,hri_dbi_cl_org_n_v org
1193 ,hri_dbi_cl_per_n_v sup
1194 ,hri_dbi_cl_per_n_v supTo
1195 ,hri_cl_wkth_wktyp_v prsnwtyp
1196 ,hri_dbi_cl_per_n_v peo';
1197
1198 l_where_clause :=
1199 'WHERE tch.supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
1200 AND tch.effective_date
1201 BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE AND &BIS_CURRENT_EFFECTIVE_END_DATE
1202 AND tch.change_type_id = 3
1203 AND tch.transfer_within_count = 1
1204 AND tch.transfer_out_hdc > 0 -- headcount greater than zero restriction
1205 AND tch.assignment_id = asg_to.assignment_id
1206 AND tch.effective_date BETWEEN asg_to.effective_change_date AND asg_to.effective_change_end_date
1207 AND tch.assignment_id = asg_from.assignment_id
1208 AND tch.effective_date-1 BETWEEN asg_from.effective_change_date AND asg_from.effective_change_end_date
1209 AND asg_to.person_id = peo.id
1210 AND tch.effective_date BETWEEN peo.effective_start_date AND peo.effective_end_date
1211 AND asg_from.supervisor_id = sup.id -- manager who had the transfer out
1212 AND tch.effective_date BETWEEN sup.effective_start_date AND sup.effective_end_date
1213 AND asg_to.supervisor_id = supTo.id -- manager who had the transfer in
1214 AND tch.effective_date BETWEEN supTo.effective_start_date AND supTo.effective_end_date
1215 AND asg_from.organization_id = org_prev.id
1216 AND asg_to.organization_id = org.id
1217 AND asg_to.job_id = job.id (+)
1218 AND asg_to.location_id = geo.location_id
1219 AND geo.country_code = ctr.id
1220 AND tch.wkth_wktyp_sk_fk = prsnwtyp.id
1221 AND tch.wkth_wktyp_sk_fk = ''CWK'' -- contingent workers only
1222 ';
1223
1224 /* append to where clause a condition if for direct reports*/
1225 IF (l_parameter_rec.peo_sup_rollup_flag = 'N' ) THEN
1226 -- add condition to restrict to directs only
1227 l_where_clause := l_where_clause || g_rtn
1228 || 'AND tch.direct_record_ind = 1';
1229 ELSE
1230 l_where_clause := l_where_clause || g_rtn
1231 || 'AND tch.direct_record_ind = 0';
1232 END IF;
1233
1234 /* get security clause for Manager based security */
1235 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
1236
1237 l_orderby_clause := '&ORDER_BY_CLAUSE';
1238
1239 x_custom_sql := l_select_clause || g_rtn
1240 ||l_from_clause || g_rtn
1241 ||l_where_clause || g_rtn
1242 ||l_security_clause || g_rtn
1243 ||l_orderby_clause;
1244
1245 END get_c_trans_out_detail2;
1246
1247 PROCEDURE get_c_trans_in_detail2(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
1248 x_custom_sql OUT NOCOPY VARCHAR2,
1249 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
1250
1251 l_lnk_emp_name VARCHAR2(4000);
1252 l_lnk_mgr_name VARCHAR2(4000);
1253 l_select_clause VARCHAR2(4000);
1254 l_from_clause VARCHAR2(4000);
1255 l_where_clause VARCHAR2(4000);
1256 l_orderby_clause VARCHAR2(4000);
1257 l_security_clause VARCHAR2(4000);
1258
1259 l_lnk_profile_chk VARCHAR2(4000);
1260
1261 l_custom_rec BIS_QUERY_ATTRIBUTES;
1262
1263 /* Parameter values */
1264 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
1265 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
1266
1267 BEGIN
1268
1269 /* Initialize table/record variables */
1270 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
1271 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
1272
1273 /* Get parameters into a pl/sql record l_param_rec */
1274 hri_oltp_pmv_util_param.get_parameters_from_table
1275 (p_page_parameter_tbl => p_param,
1276 p_parameter_rec => l_parameter_rec,
1277 p_bind_tab => l_bind_tab);
1278
1279 /* Activite Drill URL for Link to HR Employee Directory */
1280 l_lnk_profile_chk := hri_oltp_pmv_util_pkg.chk_emp_dir_lnk(p_parameter_rec => l_parameter_rec
1281 ,p_bind_tab => l_bind_tab);
1282
1283 IF (l_lnk_profile_chk = 1 AND l_parameter_rec.time_curr_end_date = TRUNC(SYSDATE)) THEN
1284 l_lnk_emp_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_PER_ID&OAPB=FII_HR_BRAND_TEXT';
1285 l_lnk_mgr_name := 'pFunctionName=HR_EMPDIR_EMPDTL_PROXY_SS&pId=HRI_P_SUP_ID&OAPB=FII_HR_BRAND_TEXT';
1286 ELSE
1287 l_lnk_emp_name := '';
1288 l_lnk_mgr_name := '';
1289 END IF ;
1290
1291 l_select_clause :=
1292 'SELECT -- headcount transfers in detail' || g_rtn ||
1293 /* View by name of person transferred */
1294 ' peo.value VIEWBY ' || g_rtn ||
1295 /* Name of person transferred */
1296 ',peo.value HRI_P_PER_LNAME_CN ' || g_rtn ||
1297 ',peo.id HRI_P_PER_ID ' || g_rtn ||
1298 ',''' || l_lnk_emp_name || '''
1299 HRI_P_DRILL_URL1' || g_rtn ||
1300 /* Transfer to Manager */
1301 ',sup.value HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
1302 ',sup.id HRI_P_SUP_ID ' || g_rtn ||
1303 ',''' || l_lnk_mgr_name || '''
1304 HRI_P_DRILL_URL2' || g_rtn ||
1305 /* Transfer from Organization */
1306 ',org_prev.value HRI_P_ORG_CN ' || g_rtn ||
1307 /* Transfer to Organization */
1308 ',org.value HRI_P_CHAR1_GA ' || g_rtn ||
1309 /* Transfer to country */
1310 ',ctr.value HRI_P_GEO_CTY_CN ' || g_rtn ||
1311 /* Transfer to job (using default display configuration) */
1312 ',hri_bpl_job.get_job_display_name
1313 (job.id
1314 ,job.business_group_id
1315 ,job.value) HRI_P_JOB_CN ' || g_rtn ||
1316 /* Person type on hire date */
1317 ',null HRI_P_CHAR2_GA ' || g_rtn ||
1318 /* Most Recent Hire Date */
1319 ', to_char(null) HRI_P_DATE1_GA ' || g_rtn ||
1320 /* Transfer (In) Date */
1321 ',tch.effective_date HRI_P_DATE2_GA ' || g_rtn ||
1322 /* Event Type - for future use */
1323 ',null HRI_P_CHAR3_GA ' || g_rtn ||
1324 /* Order by default name sort order */
1325 ',peo.order_by HRI_P_ORDER_BY_1 ';
1326
1327 l_from_clause :=
1328 'FROM
1329 hri_mb_asgn_events_ct asg_to
1330 ,hri_mb_asgn_events_ct asg_from
1331 ,hri_mdp_sup_wcnt_chg_asg_mv tch
1332 ,hri_cs_geo_lochr_ct geo
1333 ,hri_dbi_cl_geo_country_v ctr
1334 ,hri_dbi_cl_job_n_v job
1335 ,hri_dbi_cl_org_n_v org_prev
1336 ,hri_dbi_cl_org_n_v org
1337 ,hri_dbi_cl_per_n_v sup
1338 ,hri_dbi_cl_per_n_v peo';
1339
1340 l_where_clause :=
1341 'WHERE tch.supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
1342 AND tch.effective_date
1343 BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE AND &BIS_CURRENT_EFFECTIVE_END_DATE
1344 AND tch.change_type_id = 2
1345 AND tch.transfer_in_hdc > 0
1346 AND tch.transfer_within_count = 1 -- to exclude transfers within
1347 AND tch.assignment_id = asg_to.assignment_id
1348 AND tch.effective_date BETWEEN asg_to.effective_change_date AND asg_to.effective_change_end_date
1349 AND tch.assignment_id = asg_from.assignment_id
1350 AND tch.effective_date-1 BETWEEN asg_from.effective_change_date AND asg_from.effective_change_end_date
1351 AND asg_to.person_id = peo.id
1352 AND tch.effective_date BETWEEN peo.effective_start_date AND peo.effective_end_date
1353 AND asg_to.supervisor_id = sup.id -- manager transferred into
1354 AND tch.effective_date BETWEEN sup.effective_start_date AND sup.effective_end_date
1355 AND asg_from.organization_id = org_prev.id
1356 AND asg_to.organization_id = org.id
1357 AND asg_to.job_id = job.id (+)
1358 AND asg_to.location_id = geo.location_id
1359 AND geo.country_code = ctr.id
1360 AND tch.wkth_wktyp_sk_fk = ''CWK'' '; --contingent workers only
1361
1362 /* append to where clause a condition if for direct reports*/
1363 IF (l_parameter_rec.peo_sup_rollup_flag = 'N' ) THEN
1364 -- add condition to restrict to directs only
1365 l_where_clause := l_where_clause || g_rtn
1366 || 'AND tch.direct_record_ind = 1';
1367 ELSE
1368 l_where_clause := l_where_clause || g_rtn
1369 || 'AND tch.direct_record_ind = 0';
1370 END IF;
1371
1372 /* get security clause for Manager based security */
1373 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
1374
1375 l_orderby_clause := '&ORDER_BY_CLAUSE';
1376
1377 x_custom_sql := l_select_clause || g_rtn
1378 ||l_from_clause || g_rtn
1379 ||l_where_clause || g_rtn
1380 ||l_security_clause || g_rtn
1381 ||l_orderby_clause;
1382
1383 END get_c_trans_in_detail2;
1384
1385
1386 END HRI_OLTP_PMV_DTL_WRK_EVENT;