[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_WMV_C_EXTN_GRAPH
Source
1 PACKAGE BODY HRI_OLTP_PMV_WMV_C_EXTN_GRAPH AS
2 /* $Header: hriopcetg.pkb 120.1 2005/07/19 03:47 cbridge noship $ */
3
4 g_rtn VARCHAR2(30) := '
5 ';
6
7
8 PROCEDURE GET_SQL(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(32000);
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 BEGIN
30
31 /* Initialize table/record variables */
32 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
33 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
34
35 /* Get security clause for Manager based security */
36 l_security_clause := hri_oltp_pmv_util_pkg.get_security_clause('MGR');
37
38 /* Get common parameter values */
39 hri_oltp_pmv_util_param.get_parameters_from_table
40 (p_page_parameter_tbl => p_page_parameter_tbl,
41 p_parameter_rec => l_parameter_rec,
42 p_bind_tab => l_bind_tab);
43
44 /* Get number of periods to use */
45 hri_oltp_pmv_query_time.get_period_binds
46 (p_page_period_type => l_parameter_rec.page_period_type
47 ,p_page_comp_type => l_parameter_rec.time_comparison_type
48 ,o_previous_periods => l_previous_periods
49 ,o_projection_periods => l_projection_periods);
50
51 /* Get the trend sql */
52 l_trend_sql_params.bind_format := 'PMV';
53 l_trend_sql_params.include_hdc := 'Y';
54 l_trend_sql_params.include_pasg_cnt := 'Y';
55 l_trend_sql_params.include_pasg_pow := 'Y';
56 l_trend_sql_params.include_extn_cnt := 'Y';
57 l_trend_sql_params.include_extn_pow := 'Y';
58
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_C_EXTN_GRAPH.get_sql');
64
65 l_sqltext :=
66 -- l_parameter_rec.debug_header || g_rtn ||
67 'SELECT -- Contingent Worker Placement (with Extension) Trend
68 qry.period_as_of_date VIEWBYID
69 ,qry.period_as_of_date VIEWBY
70 ,qry.period_order HRI_P_ORDER_BY_1
71 ,qry.period_as_of_date HRI_P_GRAPH_X_LABEL_TIME
72 ,DECODE(qry.period_pasg_cnt
73 ,0,0
74 ,((qry.period_pasg_pow - qry.period_extn_pow)
75 /(qry.period_pasg_cnt*:MONTHS_MULTIPLIER))
76 ) HRI_P_MEASURE1
77 ,DECODE(qry.period_pasg_cnt
78 ,0,0
79 ,((qry.period_extn_pow)/(period_pasg_cnt*:MONTHS_MULTIPLIER))
80 ) HRI_P_MEASURE2
81 ,DECODE(qry.period_pasg_cnt
82 ,0,0
83 ,((qry.period_pasg_pow)/(period_pasg_cnt*:MONTHS_MULTIPLIER))
84 ) HRI_P_MEASURE3
85 ,qry.period_pasg_cnt HRI_P_MEASURE4
86 ,((qry.period_pasg_pow)/:MONTHS_MULTIPLIER)
87 HRI_P_MEASURE5
88 ,qry.period_extn_cnt HRI_P_MEASURE6
89 ,((qry.period_extn_pow)/:MONTHS_MULTIPLIER)
90 HRI_P_MEASURE7
91 ,to_char(qry.period_as_of_date ,''DD/MM/YYYY'')
92 HRI_P_CHAR1_GA
93 FROM
94 (' || l_trend_sql || ') qry
95 WHERE 1=1
96 '||l_security_clause||'
97 ORDER BY qry.period_order ASC ';
98
99 x_custom_sql := l_sqltext;
100
101 l_custom_rec.attribute_name := ':TIME_PERIOD_TYPE';
102 l_custom_rec.attribute_value := l_parameter_rec.page_period_type;
103 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
104 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
105 x_custom_output.extend;
106 x_custom_output(1) := l_custom_rec;
107
108 l_custom_rec.attribute_name := ':TIME_COMPARISON_TYPE';
109 l_custom_rec.attribute_value := l_parameter_rec.time_comparison_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(2) := l_custom_rec;
114
115 l_custom_rec.attribute_name := ':TIME_PERIOD_NUMBER';
116 l_custom_rec.attribute_value := l_previous_periods;
117 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
118 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
119 x_custom_output.extend;
120 x_custom_output(3) := l_custom_rec;
121
122 l_custom_rec.attribute_name := ':MONTHS_MULTIPLIER';
123 l_custom_rec.attribute_value := 30.42;
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(4) := l_custom_rec;
128
129
130 END GET_SQL;
131
132 END HRI_OLTP_PMV_WMV_C_EXTN_GRAPH;