[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_WMV_ABS_SUP_GRAPH
Source
1 PACKAGE BODY HRI_OLTP_PMV_WMV_ABS_SUP_GRAPH AS
2 /* $Header: hriopwabst.pkb 120.1 2005/11/03 06:57 cbridge noship $ */
3
4 g_rtn VARCHAR2(30) := '
5 ';
6
7 g_unassigned VARCHAR2(50) := HRI_OLTP_VIEW_MESSAGE.get_unassigned_msg;
8
9 PROCEDURE GET_SQL
10 (p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
11 x_custom_sql OUT NOCOPY VARCHAR2,
12 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
13
14 l_sqltext VARCHAR2(32767);
15 l_custom_rec BIS_QUERY_ATTRIBUTES;
16 l_security_clause VARCHAR2(4000);
17
18 -- Parameter values
19 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
20 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
21 l_abs_category_tab hri_oltp_pmv_rank_abs.abs_category_tab;
22
23 -- Trend Period Parameters
24 l_projection_periods NUMBER;
25 l_previous_periods NUMBER;
26 l_trend_sql VARCHAR2(10000);
27 l_trend_sql_params hri_oltp_pmv_query_trend.trend_sql_params_type;
28
29 -- dyanamic columns
30 l_period_abs_drtn_metric1 VARCHAR2(1000);
31 l_period_abs_drtn_metric2 VARCHAR2(1000);
32
33 l_drill_url1 VARCHAR2(1000);
34 l_drill_url2 VARCHAR2(1000);
35 l_drill_url3 VARCHAR2(1000);
36 l_drill_url4 VARCHAR2(1000);
37
38
39 BEGIN
40
41 /* Initialize out parameters */
42 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
43 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
44
45 /* Get common parameter values */
46 hri_oltp_pmv_util_param.get_parameters_from_table
47 (p_page_parameter_tbl => p_page_parameter_tbl,
48 p_parameter_rec => l_parameter_rec,
49 p_bind_tab => l_bind_tab);
50
51 /* Get security clause for Manager based security */
52 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
53
54 /* Get bind values for number of time periods */
55 hri_oltp_pmv_query_time.get_period_binds
56 (p_page_period_type => l_parameter_rec.page_period_type
57 ,p_page_comp_type => l_parameter_rec.time_comparison_type
58 ,o_previous_periods => l_previous_periods
59 ,o_projection_periods => l_projection_periods);
60
61 /* Set the trend sql context for the code centralization call */
62 l_trend_sql_params.bind_format := 'PMV';
63 l_trend_sql_params.include_hdc := 'Y';
64 l_trend_sql_params.include_abs_in_period := 'Y';
65
66 /* Set metric base on absence UOM profile option, default to DAYS */
67 IF (hri_bpl_utilization.get_abs_durtn_profile_vl = 'DAYS') THEN
68 l_trend_sql_params.include_abs_drtn_days := 'Y';
69
70 l_period_abs_drtn_metric1 :=
71 'DECODE(qry.period_hdc_abs, 0, to_number(NULL), (qry.period_abs_drtn_days/qry.period_hdc_abs))';
72 l_period_abs_drtn_metric2 := 'qry.period_abs_drtn_days';
73
74 ELSIF (hri_bpl_utilization.get_abs_durtn_profile_vl = 'HOURS') THEN
75 l_trend_sql_params.include_abs_drtn_hrs := 'Y';
76 l_period_abs_drtn_metric1 :=
77 'DECODE(qry.period_hdc_abs, 0, to_number(NULL), (qry.period_abs_drtn_hrs/qry.period_hdc_abs))';
78 l_period_abs_drtn_metric2 := 'qry.period_abs_drtn_hrs';
79 ELSE
80 l_trend_sql_params.include_abs_drtn_days := 'Y';
81 l_period_abs_drtn_metric1 :=
82 'DECODE(qry.period_hdc_abs, 0, to_number(NULL), (qry.period_abs_drtn_days/qry.period_hdc_abs))';
83 l_period_abs_drtn_metric2 := 'qry.period_abs_drtn_days';
84
85 END IF;
86
87 /* Call the UI fact code centralization for the Trend */
88 l_trend_sql := hri_oltp_pmv_query_trend.get_sql
89 (p_parameter_rec => l_parameter_rec,
90 p_bind_tab => l_bind_tab,
91 p_trend_sql_params => l_trend_sql_params,
92 p_calling_module => 'HRI_OLTP_PMV_WMV_ABS_SUP_GRAPH.GET_SQL');
93
94 /* set the dynamic drill urls
95 l_drill_url1 :=
96 'pFunctionName=HRI_P_ABS_SUP_DTL&' ||
97 'VIEW_BY=VIEW_BY_NAME&' ||
98 'VIEW_BY_NAME=VIEW_BY_ID&' ||
99 ''' || ''' || 'AS_OF_DATE=HRI_P_CHAR1_GA&' ||
100 'pParamIds=Y';
101
102 l_drill_url2 :=
103 'pFunctionName=HRI_P_ABS_SUP_DTL&' ||
104 'VIEW_BY=VIEW_BY_NAME&' ||
105 'VIEW_BY_NAME=VIEW_BY_ID&' ||
106 ''' || ''' || 'AS_OF_DATE=HRI_P_CHAR1_GA&' ||
107 'pParamIds=Y';
108
109 l_drill_url3 :=
110 'pFunctionName=HRI_P_ABS_SUP_DTL&' ||
111 'VIEW_BY=VIEW_BY_NAME&' ||
112 'VIEW_BY_NAME=VIEW_BY_ID&' ||
113 ''' || ''' || 'AS_OF_DATE=HRI_P_CHAR1_GA&' ||
114 'pParamIds=Y';
115 l_drill_url4 :=
116 'pFunctionName=HRI_P_ABS_SUP_DTL&' ||
117 'VIEW_BY=VIEW_BY_NAME&' ||
118 'VIEW_BY_NAME=VIEW_BY_ID&' ||
119 ''' || ''' || 'AS_OF_DATE=HRI_P_CHAR1_GA&' ||
120 'pParamIds=Y';
121 */
122
123 /* Formulate the PMV final query output for the UI*/
124 l_sqltext :=
125 'SELECT -- Employee Absence Trend
126 qry.period_as_of_date VIEWBYID
127 ,qry.period_as_of_date VIEWBY
128 ,qry.period_order HRI_P_ORDER_BY_1
129 ,qry.period_as_of_date HRI_P_GRAPH_X_LABEL_TIME
130 ,'||l_period_abs_drtn_metric1 ||' HRI_P_MEASURE1
131 ,''' || l_drill_url1 || ''' HRI_P_DRILL_URL1
132 ,'||l_period_abs_drtn_metric2 ||' HRI_P_MEASURE2
133 ,''' || l_drill_url2 || ''' HRI_P_DRILL_URL2
134 ,qry.period_abs_in_period HRI_P_MEASURE3
135 ,''' || l_drill_url3 || ''' HRI_P_DRILL_URL3
136 ,qry.period_hdc_abs HRI_P_MEASURE4
137 ,''' || l_drill_url4 || ''' HRI_P_DRILL_URL4
138 ,to_char(qry.period_as_of_date,''DD/MM/YYYY'') HRI_P_CHAR1_GA
139 FROM
140 (' || l_trend_sql || ') qry
141 WHERE 1=1
142 '
143 || l_security_clause ||
144 ' ORDER BY
145 period_order';
146
147 x_custom_sql := l_sqltext;
148
149 l_custom_rec.attribute_name := ':TIME_PERIOD_TYPE';
150 l_custom_rec.attribute_value := l_parameter_rec.page_period_type;
151 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
152 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
153 x_custom_output.extend;
154 x_custom_output(1) := l_custom_rec;
155
156 l_custom_rec.attribute_name := ':TIME_COMPARISON_TYPE';
157 l_custom_rec.attribute_value := l_parameter_rec.time_comparison_type;
158 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
159 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
160 x_custom_output.extend;
161 x_custom_output(2) := l_custom_rec;
162
163 l_custom_rec.attribute_name := ':TIME_PERIOD_NUMBER';
164 l_custom_rec.attribute_value := l_previous_periods;
165 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
166 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
167 x_custom_output.extend;
168 x_custom_output(3) := l_custom_rec;
169
170 END GET_SQL ;
171
172 END HRI_OLTP_PMV_WMV_ABS_SUP_GRAPH ;