[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* formulate the dynmaic column selection based on Absence Duration
unit of measure profile option */
IF (hri_bpl_utilization.get_abs_durtn_profile_vl = 'DAYS') THEN
l_dynmc_drtn_prd := 'factM.abs_drtn_days_prd';
'SELECT -- Employee Absence Detail (Direct Reports)' || g_rtn ||
' peo.value VIEWBY ' || g_rtn ||
',peo.order_by HRI_P_ORDER_BY_1 ' || g_rtn ||
',peo.value HRI_P_PER_LNAME_CN ' || g_rtn ||
',peo.id HRI_P_PER_ID ' || g_rtn ||
',''' || p_lnk_emp_name || ''' HRI_P_DRILL_URL1' || g_rtn ||
',sup.value HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
',sup.id HRI_P_SUP_ID ' || g_rtn ||
',''' || p_lnk_mgr_name || ''' HRI_P_DRILL_URL2' || g_rtn ||
',abs_cs.abs_start_date HRI_P_DATE1_GA' || g_rtn ||
',DECODE(abs_cs.abs_end_date,
to_date(''' || to_char(hr_general.end_of_time, 'DD-MM-YYYY') ||
''', ''DD-MM-YYYY''), to_date(NULL),
abs_cs.abs_end_date
) HRI_P_DATE2_GA' || g_rtn ||
',abs_cat.value HRI_P_CHAR1_GA' || g_rtn ||
',pabstyp.name HRI_P_CHAR2_GA' || g_rtn ||
',abs_rsn.value HRI_P_CHAR3_GA' || g_rtn ||
','||l_dynmc_drtn_prd||' HRI_P_MEASURE1' || g_rtn ||
','||l_dynmc_drtn||' HRI_P_MEASURE2' || g_rtn ||
'FROM
-- inner query
(SELECT /*+ NO_MERGE */
fact.abs_person_id
,suph.sup_person_id supervisor_person_id
,fact.absence_sk_fk
,abs_cs.absence_category_code absence_category_code
,SUM(fact.abs_drtn_days) abs_drtn_days_prd
,SUM(fact.abs_drtn_hrs) abs_drtn_hrs_prd
FROM hri_mb_utl_absnc_ct fact
,hri_cs_suph suph
,HRI_CS_ABSENCE_CT abs_cs
WHERE suph.sup_person_id = &HRI_PERSON+HRI_PER_USRDR_H
AND fact.effective_date BETWEEN suph.effective_start_date
AND suph.effective_end_date
AND suph.sub_invalid_flag_code = ''N''
AND suph.sub_relative_level = 1
AND suph.sub_person_id = fact.abs_person_id
AND fact.absence_sk_fk = abs_cs.absence_sk_pk
AND fact.abs_person_id = abs_cs.abs_person_id
AND fact.effective_date BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
AND &BIS_CURRENT_EFFECTIVE_END_DATE
-- dynamic where conditions' || g_rtn ||
l_where_clause|| g_rtn ||
'-- end of dynamic where conditions
GROUP BY
fact.abs_person_id
,suph.sup_person_id
,fact.absence_sk_fk
,abs_cs.absence_category_code
) factM
, hri_cs_absence_ct abs_cs
, per_absence_attendance_types pabstyp
, hri_cl_absnc_cat_v abs_cat
, hri_cl_absnc_rsn_v abs_rsn
, hri_dbi_cl_per_n_v peo
, hri_dbi_cl_per_n_v sup
, per_all_assignments_f asg
WHERE
factM.absence_sk_fk = abs_cs.absence_sk_pk
AND abs_cs.absence_attendance_type_id = pabstyp.absence_attendance_type_id
AND abs_cs.absence_category_code = abs_cat.id
AND abs_cs.absence_reason_code = abs_rsn.id
AND factM.abs_person_id = peo.id
AND &BIS_CURRENT_ASOF_DATE BETWEEN peo.start_date and peo.end_date
AND factM.abs_person_id = asg.person_id
AND abs_cs.abs_start_date BETWEEN asg.effective_start_date and asg.effective_end_date
AND asg.primary_flag = ''Y''
AND asg.supervisor_id = sup.id
AND &BIS_CURRENT_ASOF_DATE BETWEEN sup.start_date and sup.end_date' || g_rtn ||
l_security_clause || g_rtn ||
'&ORDER_BY_CLAUSE ';
/* formulate the dynmaic column selection based on Absence Duration
unit of measure profile option */
IF (hri_bpl_utilization.get_abs_durtn_profile_vl = 'DAYS') THEN
l_dynmc_drtn_prd := 'factM.abs_drtn_days_prd';
'SELECT -- Employee Absence Detail (All Staff)' || g_rtn ||
' peo.value VIEWBY ' || g_rtn ||
',peo.order_by HRI_P_ORDER_BY_1 ' || g_rtn ||
',peo.value HRI_P_PER_LNAME_CN ' || g_rtn ||
',peo.id HRI_P_PER_ID ' || g_rtn ||
',''' || p_lnk_emp_name || ''' HRI_P_DRILL_URL1' || g_rtn ||
',sup.value HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
',sup.id HRI_P_SUP_ID ' || g_rtn ||
',''' || p_lnk_mgr_name || ''' HRI_P_DRILL_URL2' || g_rtn ||
',abs_cs.abs_start_date HRI_P_DATE1_GA' || g_rtn ||
',DECODE(abs_cs.abs_end_date,
to_date(''' || to_char(hr_general.end_of_time, 'DD-MM-YYYY') ||
''', ''DD-MM-YYYY''), to_date(NULL),
abs_cs.abs_end_date
) HRI_P_DATE2_GA' || g_rtn ||
',abs_cat.value HRI_P_CHAR1_GA' || g_rtn ||
',pabstyp.name HRI_P_CHAR2_GA' || g_rtn ||
',abs_rsn.value HRI_P_CHAR3_GA' || g_rtn ||
','||l_dynmc_drtn_prd||' HRI_P_MEASURE1' || g_rtn ||
','||l_dynmc_drtn||' HRI_P_MEASURE2' || g_rtn ||
'FROM
-- inner query
(SELECT /*+ NO_MERGE */
fact.abs_person_id
,fact.supervisor_person_id
,fact.absence_sk_fk
,SUM(fact.abs_drtn_days) abs_drtn_days_prd
,SUM(fact.abs_drtn_hrs) abs_drtn_hrs_prd
FROM
hri_mdp_sup_absnc_occ_ct fact
WHERE fact.supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
AND fact.effective_date BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
AND &BIS_CURRENT_EFFECTIVE_END_DATE
-- dynamic where conditions' || g_rtn ||
l_where_clause|| g_rtn ||
'-- end of dynamic where conditions
GROUP BY
fact.abs_person_id
,fact.supervisor_person_id
,fact.absence_sk_fk
) factM
-- end of inner query
, hri_cs_absence_ct abs_cs
, per_absence_attendance_types pabstyp
, hri_cl_absnc_cat_v abs_cat
, hri_cl_absnc_rsn_v abs_rsn
, hri_dbi_cl_per_n_v peo
, per_all_assignments_f asg
, hri_dbi_cl_per_n_v sup
WHERE factM.absence_sk_fk = abs_cs.absence_sk_pk
AND abs_cs.absence_attendance_type_id = pabstyp.absence_attendance_type_id
AND abs_cs.absence_category_code = abs_cat.id
AND abs_cs.absence_reason_code = abs_rsn.id
AND factM.abs_person_id = peo.id
AND &BIS_CURRENT_ASOF_DATE BETWEEN peo.start_date and peo.end_date
AND factM.abs_person_id = asg.person_id
AND abs_cs.abs_start_date BETWEEN asg.effective_start_date and asg.effective_end_date
AND asg.primary_flag = ''Y''
AND asg.supervisor_id = sup.id
AND abs_cs.abs_start_date BETWEEN sup.start_date and sup.end_date' || g_rtn ||
l_security_clause || g_rtn ||
'&ORDER_BY_CLAUSE ';