[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_WMV_WF_SUP_GRAPH
Source
1 PACKAGE BODY HRI_OLTP_PMV_WMV_WF_SUP_GRAPH AS
2 /* $Header: hriopwfsg.pkb 120.1 2005/10/26 07:54 jrstewar noship $ */
3
4 g_rtn VARCHAR2(30) := '
5 ';
6
7
8 PROCEDURE GET_SQL2(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
9 x_custom_sql OUT NOCOPY VARCHAR2,
10 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
11 IS
12
13 l_custom_rec BIS_QUERY_ATTRIBUTES;
14 l_security_clause VARCHAR2(4000);
15 l_SQLText VARCHAR2(4000);
16 l_trend_sql_params hri_oltp_pmv_query_trend.TREND_SQL_PARAMS_TYPE;
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_debug_header VARCHAR(550);
22 l_trend_sql VARCHAR2(32000);
23
24 /* Pre-calculations */
25 l_trend_table VARCHAR2(4000);
26 l_projection_periods NUMBER;
27 l_previous_periods NUMBER;
28
29 l_drill_url1 VARCHAR2(300);
30 l_drill_url2 VARCHAR2(300);
31 l_drill_url3 VARCHAR2(300);
32
33 BEGIN
34
35 /* Initialize table/record variables */
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 /* Get number of periods to use */
49 hri_oltp_pmv_query_time.get_period_binds
50 (p_page_period_type => l_parameter_rec.page_period_type
51 ,p_page_comp_type => l_parameter_rec.time_comparison_type
52 ,o_previous_periods => l_previous_periods
53 ,o_projection_periods => l_projection_periods);
54
55 /* Get the trend sql */
56 l_trend_sql_params.bind_format := 'PMV';
57 l_trend_sql_params.include_hdc := 'Y';
58 l_trend_sql_params.bucket_dim := 'HRI_PRSNTYP+HRI_WKTH_WKTYP';
59
60 l_trend_sql := hri_oltp_pmv_query_trend.get_sql
61 (p_parameter_rec => l_parameter_rec,
62 p_bind_tab => l_bind_tab,
63 p_trend_sql_params => l_trend_sql_params,
64 p_calling_module => 'HRI_OLTP_PMV_WMV_WF_SUP_GRAPH.get_sql2');
65
66 /* use selective drill across feature */
67 -- drill to employee detail report
68 l_drill_url2 := 'pFunctionName=HRI_P_WMV_SAL_SUP_DTL&' ||
69 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
70 'VIEW_BY_NAME=VIEW_BY_ID&' ||
71 'pParamIds=Y';
72 -- drill to worker detail report
73 l_drill_url3 := 'pFunctionName=HRI_P_WMV_WF_SUP_DTL&' ||
74 'VIEW_BY=HRI_PERSON+HRI_PER_USRDR_H&' ||
75 'VIEW_BY_NAME=VIEW_BY_ID&' ||
76 'HRI_P_PRSNTYP_WKTYP_CN=CWK'||
77 'pParamIds=Y';
78
79 /* */
80 l_sqltext :=
81 -- l_parameter_rec.debug_header || g_rtn ||
82 'SELECT -- Workforce Ratio Trend
83 qry.period_as_of_date VIEWBYID
84 ,qry.period_as_of_date VIEWBY
85 ,qry.period_order HRI_P_ORDER_BY_1
86 ,qry.period_as_of_date HRI_P_GRAPH_X_LABEL_TIME
87 ,qry.period_hdc HRI_P_MEASURE1
88 ,qry.period_hdc_emp HRI_P_MEASURE2
89 ,'''|| l_drill_url2 ||''' HRI_P_DRILL_URL2
90 ,qry.period_hdc_cwk HRI_P_MEASURE3
91 ,'''|| l_drill_url3 ||''' HRI_P_DRILL_URL3
92 ,DECODE(qry.period_hdc, 0, 0,
93 (qry.period_hdc_emp/qry.period_hdc)*100)
94 HRI_P_MEASURE2_MP
95 ,DECODE(qry.period_hdc, 0, 0,
96 (qry.period_hdc_cwk/qry.period_hdc)*100)
97 HRI_P_MEASURE3_MP
98 ,to_char(qry.period_as_of_date ,''DD/MM/YYYY'')
99 HRI_P_CHAR1_GA
100 FROM
101 (' || l_trend_sql || ') qry
102 WHERE 1=1
103 '||l_security_clause||'
104 ORDER BY qry.period_order ASC ';
105
106 x_custom_sql := l_sqltext;
107
108 l_custom_rec.attribute_name := ':TIME_PERIOD_TYPE';
109 l_custom_rec.attribute_value := l_parameter_rec.page_period_type;
110 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
111 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
112 x_custom_output.extend;
113 x_custom_output(1) := l_custom_rec;
114
115 l_custom_rec.attribute_name := ':TIME_COMPARISON_TYPE';
116 l_custom_rec.attribute_value := l_parameter_rec.time_comparison_type;
117 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
118 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
119 x_custom_output.extend;
120 x_custom_output(2) := l_custom_rec;
121
122 l_custom_rec.attribute_name := ':TIME_PERIOD_NUMBER';
123 l_custom_rec.attribute_value := l_previous_periods;
124 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
125 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
126 x_custom_output.extend;
127 x_custom_output(3) := l_custom_rec;
128
129 END GET_SQL2;
130
131 END HRI_OLTP_PMV_WMV_WF_SUP_GRAPH;