[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_WMV_SAL_SUP_GRAPH
Source
1 PACKAGE BODY HRI_OLTP_PMV_WMV_SAL_SUP_GRAPH AS
2 /* $Header: hriopsal.pkb 120.1 2005/11/14 02:49:27 cbridge noship $ */
3
4 g_rtn VARCHAR2(30) := '
5 ';
6
7 PROCEDURE GET_SQL2(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
8 x_custom_sql OUT NOCOPY VARCHAR2,
9 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
10 IS
11
12 l_custom_rec BIS_QUERY_ATTRIBUTES;
13 l_security_clause VARCHAR2(4000);
14 l_SQLText VARCHAR2(32000);
15 l_trend_sql_params hri_oltp_pmv_query_trend.TREND_SQL_PARAMS_TYPE;
16
17 /* Parameter values */
18 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
19 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
20 l_debug_header VARCHAR(550);
21 l_trend_sql VARCHAR2(32000);
22
23 /* Pre-calculations */
24 l_trend_table VARCHAR2(4000);
25 l_projection_periods NUMBER;
26 l_previous_periods NUMBER;
27
28 BEGIN
29
30 /* Initialize table/record variables */
31 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
32 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
33
34 /* Get security clause for Manager based security */
35 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
36
37 /* Get common parameter values */
38 hri_oltp_pmv_util_param.get_parameters_from_table
39 (p_page_parameter_tbl => p_page_parameter_tbl,
40 p_parameter_rec => l_parameter_rec,
41 p_bind_tab => l_bind_tab);
42
43 /* Get number of periods to use */
44 hri_oltp_pmv_query_time.get_period_binds
45 (p_page_period_type => l_parameter_rec.page_period_type
46 ,p_page_comp_type => l_parameter_rec.time_comparison_type
47 ,o_previous_periods => l_previous_periods
48 ,o_projection_periods => l_projection_periods);
49
50 /* Get the trend sql */
51 l_trend_sql_params.bind_format := 'PMV';
52 l_trend_sql_params.include_hdc := 'Y';
53 l_trend_sql_params.include_sal := 'Y';
54
55 l_trend_sql := hri_oltp_pmv_query_trend.get_sql
56 (p_parameter_rec => l_parameter_rec,
57 p_bind_tab => l_bind_tab,
58 p_trend_sql_params => l_trend_sql_params,
59 p_calling_module => 'HRI_OLTP_PMV_WMV_SAL_SUP_GRAPH.GET_SQL2');
60
61 l_sqltext :=
62 'SELECT -- Headcount and Salary Trend
63 qry.period_as_of_date VIEWBYID
64 ,qry.period_as_of_date VIEWBY
65 ,qry.period_order HRI_P_ORDER_BY_1
66 ,qry.period_as_of_date HRI_P_GRAPH_X_LABEL_TIME
67 ,qry.period_hdc HRI_P_WMV_SUM_MV
68 ,qry.period_sal_end HRI_P_SAL_ANL_CUR_PARAM_SUM_MV
69 ,to_char(qry.period_as_of_date ,''DD/MM/YYYY'')
70 HRI_P_CHAR1_GA
71 FROM
72 (' || l_trend_sql || ') qry
73 WHERE 1=1
74 '||l_security_clause||'
75 ORDER BY qry.period_order ASC ';
76
77 x_custom_sql := l_sqltext;
78
79 l_custom_rec.attribute_name := ':TIME_PERIOD_TYPE';
80 l_custom_rec.attribute_value := l_parameter_rec.page_period_type;
81 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
82 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
83 x_custom_output.extend;
84 x_custom_output(1) := l_custom_rec;
85
86 l_custom_rec.attribute_name := ':TIME_COMPARISON_TYPE';
87 l_custom_rec.attribute_value := l_parameter_rec.time_comparison_type;
88 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
89 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
90 x_custom_output.extend;
91 x_custom_output(2) := l_custom_rec;
92
93 l_custom_rec.attribute_name := ':TIME_PERIOD_NUMBER';
94 l_custom_rec.attribute_value := l_previous_periods;
95 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
96 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
97 x_custom_output.extend;
98 x_custom_output(3) := l_custom_rec;
99
100 l_custom_rec.attribute_name := ':GLOBAL_CURRENCY';
101 l_custom_rec.attribute_value := l_parameter_rec.currency_code;
102 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
103 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
104 x_custom_output.extend;
105 x_custom_output(4) := l_custom_rec;
106
107 l_custom_rec.attribute_name := ':GLOBAL_RATE';
108 l_custom_rec.attribute_value := l_parameter_rec.rate_type;
109 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
110 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
111 x_custom_output.extend;
112 x_custom_output(5) := l_custom_rec;
113
114 END;
115
116 END HRI_OLTP_PMV_WMV_SAL_SUP_GRAPH ;