[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_WMV_TRN_SUP_GRAPH
Source
1 PACKAGE BODY HRI_OLTP_PMV_WMV_TRN_SUP_GRAPH AS
2 /* $Header: hriopwtg.pkb 120.0 2005/05/29 07:39:42 appldev noship $ */
3
4 PROCEDURE GET_SQL2(p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
5 x_custom_sql OUT NOCOPY VARCHAR2,
6 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
7 IS
8
9 l_custom_rec BIS_QUERY_ATTRIBUTES;
10 l_security_clause VARCHAR2(4000);
11 l_SQLText VARCHAR2(10000) ;
12
13 /* Parameter values */
14 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
15 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
16
17 /* Dynamic SQL */
18 l_trend_sql_params hri_oltp_pmv_query_trend.TREND_SQL_PARAMS_TYPE;
19 l_trend_sql VARCHAR2(10000);
20
21 /* Pre-calculations */
22 l_anl_factor NUMBER;
23 l_projection_periods NUMBER;
24 l_previous_periods NUMBER;
25
26 BEGIN
27 --
28 /* Initialize table/record variables */
29 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
30 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
31
32 /* Get security clause for Manager based security */
33 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
34
35 /* Get common parameter values */
36 hri_oltp_pmv_util_param.get_parameters_from_table
37 (p_page_parameter_tbl => p_page_parameter_tbl,
38 p_parameter_rec => l_parameter_rec,
39 p_bind_tab => l_bind_tab);
40
41 /* Get annualization factor */
42 l_anl_factor := hri_oltp_pmv_util_pkg.calc_anl_factor
43 (p_period_type => l_parameter_rec.page_period_type);
44
45 /* Get number of periods to show */
46 hri_oltp_pmv_query_time.get_period_binds
47 (p_page_period_type => l_parameter_rec.page_period_type
48 ,p_page_comp_type => l_parameter_rec.time_comparison_type
49 ,o_previous_periods => l_previous_periods
50 ,o_projection_periods => l_projection_periods);
51
52 /* Set the parameters for getting the inner SQL */
53 l_trend_sql_params.bind_format := 'PMV';
54 l_trend_sql_params.include_hdc := 'Y';
55 l_trend_sql_params.include_sep_inv := 'Y';
56 l_trend_sql_params.include_sep_vol := 'Y';
57
58 /* Get the inner SQL */
59 l_trend_sql := hri_oltp_pmv_query_trend.get_sql
60 (p_parameter_rec => l_parameter_rec,
61 p_bind_tab => l_bind_tab,
62 p_trend_sql_params => l_trend_sql_params,
63 p_calling_module => 'HRI_OLTP_PMV_WMV_TRN_SUP_GRAPH.GET_SQL2');
64
65 l_SQLText := ' -- Annualized Turnover Trend
66 SELECT
67 qry.period_as_of_date VIEWBYID
68 ,qry.period_as_of_date VIEWBY
69 ,qry.period_order HRI_P_ORDER_BY_1
70 ,qry.period_as_of_date HRI_P_GRAPH_X_LABEL_TIME
71 ,DECODE(qry.period_sep_vol_hdc,
72 0, 0,
73 ((qry.period_sep_vol_hdc * :ANL_FACTOR /
74 DECODE(qry.period_hdc_trn,
75 0, (qry.period_sep_vol_hdc + qry.period_sep_invol_hdc),
76 qry.period_hdc_trn)) * 100))
77 HRI_P_WMV_TRN_SEP_VOL_ANL_MV
78 ,DECODE(qry.period_sep_invol_hdc,
79 0, 0,
80 ((qry.period_sep_invol_hdc * :ANL_FACTOR /
81 DECODE(qry.period_hdc_trn,
82 0, (qry.period_sep_vol_hdc + qry.period_sep_invol_hdc),
83 qry.period_hdc_trn)) * 100))
84 HRI_P_WMV_TRN_SEP_INV_ANL_MV
85 ,DECODE(qry.period_sep_vol_hdc + qry.period_sep_invol_hdc,
86 0, 0,
87 (((qry.period_sep_vol_hdc + qry.period_sep_invol_hdc) * :ANL_FACTOR /
88 DECODE(qry.period_hdc_trn,
89 0, (qry.period_sep_vol_hdc + qry.period_sep_invol_hdc),
90 qry.period_hdc_trn)) * 100))
91 HRI_P_WMV_TRN_ANL_SUM_MV
92 ,to_char(qry.period_as_of_date,''DD/MM/YYYY'')
93 HRI_P_CHAR1_GA
94 FROM
95 ('|| l_trend_sql || ') qry
96 WHERE 1 = 1
97 ' || l_security_clause || '
98 ORDER BY qry.period_order ASC';
99
100 x_custom_sql := l_sqltext;
101
102 l_custom_rec.attribute_name := ':TIME_PERIOD_TYPE';
103 l_custom_rec.attribute_value := l_parameter_rec.page_period_type;
104 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
105 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
106 x_custom_output.extend;
107 x_custom_output(1) := l_custom_rec;
108
109 l_custom_rec.attribute_name := ':TIME_COMPARISON_TYPE';
110 l_custom_rec.attribute_value := l_parameter_rec.time_comparison_type;
111 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
112 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
113 x_custom_output.extend;
114 x_custom_output(2) := l_custom_rec;
115
116 l_custom_rec.attribute_name := ':TIME_PERIOD_NUMBER';
117 l_custom_rec.attribute_value := l_previous_periods;
118 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
119 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
120 x_custom_output.extend;
121 x_custom_output(3) := l_custom_rec;
122
123 l_custom_rec.attribute_name := ':ANL_FACTOR';
124 l_custom_rec.attribute_value := l_anl_factor;
125 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
126 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
127 x_custom_output.extend;
128 x_custom_output(4) := l_custom_rec;
129
130 END GET_SQL2 ;
131
132 END HRI_OLTP_PMV_WMV_TRN_SUP_GRAPH;