[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_ABS_PVT
Source
1 PACKAGE BODY HRI_OLTP_PMV_ABS_PVT AS
2 /* $Header: hriopabspvt.pkb 120.5 2005/11/17 07:20 jrstewar noship $ */
3 g_rtn VARCHAR2(30) := '
4 ';
5 --
6 --****************************************************************************
7 --* AK SQL For Absence Summary Status *
8 --* AK Region : HRI_P_ABS_PVT *
9 --****************************************************************************
10 --
11 PROCEDURE get_sql(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL
12 ,x_custom_sql OUT NOCOPY VARCHAR2
13 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
14 IS
15
16 l_SQLText VARCHAR2(32000);
17 l_security_clause VARCHAR2(4000);
18 l_custom_rec BIS_QUERY_ATTRIBUTES ;
19
20 /* Dynamic SQL Controls */
21 l_abs_fact_params hri_bpl_fact_abs_sql.abs_fact_param_type;
22 l_abs_fact_sql VARCHAR2(10000);
23 l_parameter_name VARCHAR2(100);
24 l_dynmc_drtn_curr VARCHAR2(100) DEFAULT 'curr_abs_drtn_days';
25 l_dynmc_drtn_comp VARCHAR2(100) DEFAULT 'comp_abs_drtn_days';
26 l_drill_abs_detail VARCHAR2(1000);
27 l_dynsql_order_by VARCHAR2(100);
28
29 /* Parameter values */
30 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
31 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
32 l_debug_header VARCHAR(550);
33
34 BEGIN
35 /* Initialize out parameters */
36 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
37 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
38
39 /* Get security clause for Manager based security */
40 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
41
42 /* Get common parameter values */
43 hri_oltp_pmv_util_param.get_parameters_from_table
44 (p_page_parameter_tbl => p_page_parameter_tbl,
45 p_parameter_rec => l_parameter_rec,
46 p_bind_tab => l_bind_tab);
47
48 /* Drill URL's for Manager and Direct Reports */
49 l_drill_abs_detail :='pFunctionName=HRI_P_ABS_SUP_DTL&' ||
50 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
51 'VIEW_BY_NAME=VIEW_BY_ID&' ||
52 'HRI_P_SUPH_RO_CA=HRI_P_SUPH_RO_CA&' ||
53 'pParamIds=Y';
54
55 /* Set order by */
56 l_dynsql_order_by := hri_oltp_pmv_util_pkg.set_default_order_by
57 (p_order_by_clause => l_parameter_rec.order_by);
58
59
60 /* formulate the dynmaic column selection based on Absence Duration
61 unit of measure paramter selection Default Days */
62
63 IF (hri_bpl_utilization.get_abs_durtn_profile_vl = 'DAYS') THEN
64 l_dynmc_drtn_curr := 'curr_abs_drtn_days';
65 l_dynmc_drtn_comp := 'comp_abs_drtn_days';
66 l_abs_fact_params.include_abs_drtn_days := 'Y';
67 ELSIF (hri_bpl_utilization.get_abs_durtn_profile_vl = 'HOURS') THEN
68 l_dynmc_drtn_curr := 'curr_abs_drtn_hrs';
69 l_dynmc_drtn_comp := 'comp_abs_drtn_hrs';
70 l_abs_fact_params.include_abs_drtn_hrs := 'Y';
71 ELSE -- functional decision (JC) default to days
72 l_dynmc_drtn_curr := 'curr_abs_drtn_days';
73 l_dynmc_drtn_comp := 'comp_abs_drtn_days';
74 l_abs_fact_params.include_abs_drtn_days := 'Y';
75 END IF;
76
77
78 /* Get SQL for workforce fact */
79 l_abs_fact_params.bind_format := 'PMV';
80 l_abs_fact_params.include_abs_in_period := 'Y';
81 l_abs_fact_params.include_abs_ntfctn_period := 'Y';
82 l_abs_fact_params.include_comp := 'Y';
83 l_abs_fact_params.kpi_mode := 'N';
84 l_abs_fact_sql := hri_bpl_fact_abs_sql.get_sql
85 (p_parameter_rec => l_parameter_rec,
86 p_bind_tab => l_bind_tab,
87 p_abs_params => l_abs_fact_params,
88 p_calling_module => 'HRI_P_ABS_PVT');
89
90 l_SQLText :=
91 ' -- Absence Summary Status by Category only 70C
92 SELECT
93 babs.vby_id VIEWBYID
94 ,babs.value VIEWBY '|| g_rtn
95 /* Absence */ || g_rtn ||'
96 ,NVL(babs.curr_abs_in_period,to_number(NULL)) HRI_P_MEASURE1
97 ,NVL(babs.comp_abs_in_period,to_number(NULL)) HRI_P_MEASURE2'|| g_rtn
98 /* Total Notification */ || g_rtn ||'
99 ,NVL(babs.curr_abs_ntfctn_period,to_number(NULL)) HRI_P_MEASURE3
100 ,NVL(babs.comp_abs_ntfctn_period,to_number(NULL)) HRI_P_MEASURE4'|| g_rtn
101 /* Average Notification */ || g_rtn ||'
102 ,NVL(curr_abs_avg_ntfctn_period,to_number(NULL)) HRI_P_MEASURE5
103 ,NVL(comp_abs_avg_ntfctn_period,to_number(NULL)) HRI_P_MEASURE6'|| g_rtn
104 /* Change - Average Notification */ || g_rtn ||'
105 ,'|| hri_oltp_pmv_util_pkg.get_change_percent_sql
106 (p_previous_col => 'comp_abs_avg_ntfctn_period',
107 p_current_col => 'curr_abs_avg_ntfctn_period') || '
108 HRI_P_MEASURE5_MP'|| g_rtn
109 /* Total Absence Duration */ || g_rtn ||'
110 ,NVL(babs.curr_abs_drtn,to_number(NULL)) HRI_P_MEASURE7
111 ,NVL(babs.comp_abs_drtn,to_number(NULL)) HRI_P_MEASURE8'|| g_rtn
112 /* Change - Total Absence Duration */ || g_rtn ||'
113 ,'|| hri_oltp_pmv_util_pkg.get_change_percent_sql
114 (p_previous_col => 'babs.comp_abs_drtn',
115 p_current_col => 'babs.curr_abs_drtn') || ' HRI_P_MEASURE7_MP'|| g_rtn
116 /* Average Absence Duration */ || g_rtn ||'
117 ,DECODE(babs.curr_abs_in_period,0,to_number(NULL)
118 ,(babs.curr_abs_drtn / babs.curr_abs_in_period)
119 ) HRI_P_MEASURE9
120 ,DECODE(babs.comp_abs_in_period,0,to_number(NULL)
121 ,(babs.curr_abs_drtn / babs.comp_abs_in_period)
122 ) HRI_P_MEASURE10'|| g_rtn
123 /* Total Absence */ || g_rtn ||'
124 ,NVL(babs.curr_tot_abs_in_period,to_number(NULL)) HRI_P_GRAND_TOTAL1
125 ,NVL(babs.comp_tot_abs_in_period,to_number(NULL)) HRI_P_GRAND_TOTAL2'|| g_rtn
126 /* Total Notification */ || g_rtn ||'
127 ,NVL(babs.curr_tot_abs_ntfctn_period,to_number(NULL)) HRI_P_GRAND_TOTAL3
128 ,NVL(babs.comp_tot_abs_ntfctn_period,to_number(NULL)) HRI_P_GRAND_TOTAL4'|| g_rtn
129 /* Total Average Notification */ || g_rtn ||'
130 ,NVL(babs.curr_tot_avg_abs_ntfctn_period,to_number(NULL))
131 HRI_P_GRAND_TOTAL5
132 ,NVL(babs.comp_tot_avg_abs_ntfctn_period,to_number(NULL))
133 HRI_P_GRAND_TOTAL6'|| g_rtn
134 /* Change Total - Total Average Notification */ || g_rtn ||'
135 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
136 (p_previous_col => 'babs.comp_tot_avg_abs_ntfctn_period',
137 p_current_col => 'babs.curr_tot_avg_abs_ntfctn_period') || '
138 HRI_P_GRAND_TOTAL5_MP'|| g_rtn
139 /* Total Absence Duration */ || g_rtn ||'
140 ,NVL(babs.curr_tot_abs_drtn,to_number(NULL)) HRI_P_GRAND_TOTAL7
141 ,NVL(babs.comp_tot_abs_drtn,to_number(NULL)) HRI_P_GRAND_TOTAL8'|| g_rtn
142 /* Change Total - Total Absence Duration */ || g_rtn ||'
143 ,' || hri_oltp_pmv_util_pkg.get_change_percent_sql
144 (p_previous_col => 'babs.comp_tot_abs_drtn',
145 p_current_col => 'babs.curr_tot_abs_drtn') || '
146 HRI_P_GRAND_TOTAL7_MP'|| g_rtn
147 /* Total Average Absence Duration */ || g_rtn ||'
148 ,DECODE(babs.curr_tot_abs_in_period,0,to_number(NULL)
149 ,(babs.curr_tot_abs_drtn / babs.curr_tot_abs_in_period)
150 ) HRI_P_GRAND_TOTAL9
151 ,DECODE(babs.comp_tot_abs_in_period,0,to_number(NULL)
152 ,(babs.comp_tot_abs_drtn / babs.comp_tot_abs_in_period)
153 ) HRI_P_GRAND_TOTAL10' || g_rtn ||
154 /* Order by person name default sort order */
155 ',babs.order_by HRI_P_ORDER_BY_1 ' || g_rtn ||
156 /* Whether the row is a supervisor rollup row */
157 ','''' HRI_P_SUPH_RO_CA '|| g_rtn
158 /* Drill URLs */ || g_rtn ||'
159 ,'''|| l_drill_abs_detail ||''' HRI_P_DRILL_URL1
160 FROM
161 (
162 SELECT
163 /* Base Measures */
164 vby.id vby_id
165 ,vby.value value
166 ,vby.order_by order_by
167 ,NVL(fact.'||l_dynmc_drtn_curr ||',0) curr_abs_drtn
168 ,NVL(fact.curr_abs_in_period,0) curr_abs_in_period
169 ,NVL(fact.'||l_dynmc_drtn_comp ||',0) comp_abs_drtn
170 ,NVL(fact.comp_abs_in_period,0) comp_abs_in_period
171 ,NVL(fact.curr_abs_ntfctn_period,0) curr_abs_ntfctn_period
172 ,NVL(fact.comp_abs_ntfctn_period,0) comp_abs_ntfctn_period
173 ,DECODE(fact.curr_abs_ntfctn_period,0,to_number(NULL)
174 ,DECODE(fact.curr_abs_in_period,0,to_number(NULL)
175 ,(fact.curr_abs_ntfctn_period / fact.curr_abs_in_period)
176 )
177 ) curr_abs_avg_ntfctn_period
178 ,DECODE(fact.comp_abs_ntfctn_period,0,to_number(NULL)
179 ,DECODE(fact.curr_abs_in_period,0,to_number(NULL)
180 ,(fact.comp_abs_ntfctn_period / fact.comp_abs_in_period)
181 )
182 ) comp_abs_avg_ntfctn_period
183 ,SUM(fact.'||l_dynmc_drtn_curr||') OVER()
184 curr_tot_abs_drtn
185 ,SUM(fact.curr_abs_in_period) OVER() curr_tot_abs_in_period
186 ,SUM(fact.'||l_dynmc_drtn_comp||') OVER() comp_tot_abs_drtn
187 ,SUM(fact.comp_abs_in_period) OVER() comp_tot_abs_in_period
188 ,SUM(fact.curr_abs_ntfctn_period) OVER() curr_tot_abs_ntfctn_period
189 ,SUM(fact.comp_abs_ntfctn_period) OVER() comp_tot_abs_ntfctn_period
190 ,DECODE(SUM(fact.curr_abs_ntfctn_period) OVER(),0,to_number(NULL)
191 ,DECODE(SUM(fact.curr_abs_in_period) OVER(),0,to_number(NULL)
192 ,(SUM(fact.curr_abs_ntfctn_period) OVER() / SUM(fact.curr_abs_in_period) OVER())
193 )
194 ) curr_tot_avg_abs_ntfctn_period
195 ,DECODE(SUM(fact.comp_abs_ntfctn_period) OVER(),0,to_number(NULL)
196 ,DECODE(SUM(fact.comp_abs_in_period) OVER(),0,to_number(NULL)
197 ,(SUM(fact.comp_abs_ntfctn_period) OVER() / SUM(fact.comp_abs_in_period) OVER())
198 )
199 ) comp_tot_avg_abs_ntfctn_period
200 FROM
201 hri_cl_absnc_cat_v vby
202 ,('|| l_abs_fact_sql ||') fact
203 WHERE
204 vby.id = fact.vby_id
205 ' || l_security_clause || ') babs
206 ORDER BY '|| l_dynsql_order_by;
207
208 x_custom_sql := l_SQLText ;
209
210 END get_sql;
211
212 --
213 --****************************************************************************
214 --* AK SQL For
215 --* AK Region :
216 --****************************************************************************
217 --
218 END HRI_OLTP_PMV_ABS_PVT;