[Home] [Help]
PACKAGE BODY: APPS.HRI_OLTP_PMV_WMV_SUP_GRAPH
Source
1 PACKAGE BODY HRI_OLTP_PMV_WMV_SUP_GRAPH AS
2 /* $Header: hriopbdg.pkb 120.2 2006/01/05 00:43:08 anmajumd 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 /* Parameter values */
17 l_parameter_rec hri_oltp_pmv_util_param.HRI_PMV_PARAM_REC_TYPE;
18 l_bind_tab hri_oltp_pmv_util_param.HRI_PMV_BIND_TAB_TYPE;
19 l_debug_header VARCHAR(550);
20 /* Pre-calculations */
21 l_previous_periods NUMBER;
22 l_projection_periods NUMBER;
23 l_trend_table VARCHAR2(4000);
24 g_rtn VARCHAR2(30) := '
25 ';
26
27 BEGIN
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 fii offset table for alias in main query and Variables */
42 HRI_OLTP_PMV_QUERY_TIME.GET_TIME_CLAUSE
43 ('Y'
44 ,l_parameter_rec.page_period_type
45 ,l_parameter_rec.time_comparison_type
46 ,l_trend_table
47 ,l_previous_periods
48 ,l_projection_periods );
49
50 l_SQLText :=
51 ' SELECT -- Headcount Budget Trend
52 a.period_as_of_date VIEWBY,
53 a.period_as_of_date VIEWBYID,
54 a.period_order hri_p_order_by_1,
55 a.period_as_of_date hri_p_graph_x_label_time,
56 SUM(decode(hri_dbi_wmv_budget.comp_date(a.period_as_of_date,trunc(&BIS_CURRENT_ASOF_DATE))
57 , ''N'', b.total_headcount
58 ,''Y'', null) ) hri_p_wmv_sum_mv,
59 c.budget_value hri_p_wmv_budget_sum_mv
60 FROM '||l_trend_table || ' a,
61 HRI_MDP_SUP_WRKFC_SUP_MV b,
62 hri_dbi_wmv_budget_mv c
63 WHERE b.supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
64 AND a.period_as_of_date BETWEEN b.effective_start_date AND b.effective_end_date
65 AND c.supervisor_id(+) = b.supervisor_person_id
66 AND b.wkth_wktyp_sk_fk = ''EMP''
67 AND NVL(c.effective_date,SYSDATE) = ( SELECT NVL(MAX(e.effective_date),SYSDATE)
68 FROM hri_dbi_wmv_budget_mv e
69 WHERE e.supervisor_id = c.supervisor_id
70 AND e.effective_date <= a.period_as_of_date
71 AND e.count_type = ''TOTAL_WMV_BUDGET'')
72 AND c.count_type(+) = ''TOTAL_WMV_BUDGET''
73 '|| l_security_clause ||'
74 GROUP BY a.period_order
75 , a.period_as_of_date
76 , c.budget_value
77 ORDER BY a.period_order ASC' ;
78
79 x_custom_sql := l_sqltext;
80
81 l_custom_rec.attribute_name := ':TIME_PERIOD_TYPE';
82 l_custom_rec.attribute_value := l_parameter_rec.page_period_type;
83 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
84 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
85 x_custom_output.extend;
86 x_custom_output(1) := l_custom_rec;
87
88 l_custom_rec.attribute_name := ':TIME_PROJECT_PERIOD_TYPE';
89 l_custom_rec.attribute_value := l_parameter_rec.page_period_type;
90 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
91 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
92 x_custom_output.extend;
93 x_custom_output(2) := l_custom_rec;
94
95 l_custom_rec.attribute_name := ':TIME_COMPARISON_TYPE';
96 l_custom_rec.attribute_value := l_parameter_rec.time_comparison_type;
97 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
98 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
99 x_custom_output.extend;
100 x_custom_output(3) := l_custom_rec;
101
102 l_custom_rec.attribute_name := ':TIME_PROJECT_COMPARISON_TYPE';
103 l_custom_rec.attribute_value := l_parameter_rec.time_comparison_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(4) := l_custom_rec;
108
109 l_custom_rec.attribute_name := ':TIME_PERIOD_NUMBER';
110 l_custom_rec.attribute_value := l_previous_periods;
111 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
112 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.numeric_bind;
113 x_custom_output.extend;
114 x_custom_output(5) := l_custom_rec;
115
116 l_custom_rec.attribute_name := ':TIME_PROJECT_PERIOD_NUMBER';
117 l_custom_rec.attribute_value := l_projection_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(6) := l_custom_rec;
122
123 END;
124
125 END HRI_OLTP_PMV_WMV_SUP_GRAPH;