[Home] [Help]
PACKAGE BODY: APPS.ENI_DBI_OEX_T_PKG
Source
1 PACKAGE BODY eni_dbi_oex_t_pkg AS
2 /*$Header: ENIOETPB.pls 120.2 2006/03/23 04:38:10 pgopalar noship $*/
3
4 PROCEDURE get_sql
5 (
6 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
7 x_custom_sql OUT NOCOPY VARCHAR2,
8 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
9 )
10 IS
11
12 l_period_type VARCHAR2(100);
13 l_period_bitand NUMBER;
14 l_view_by VARCHAR2(100);
15 l_as_of_date DATE;
16 l_prev_as_of_date DATE;
17 l_report_start DATE;
18 l_cur_period NUMBER;
19 l_days_into_period NUMBER;
20 l_comp_type VARCHAR2(100);
21 l_category VARCHAR2(100);
22 l_item VARCHAR2(100);
23 l_org VARCHAR2(100);
24 l_id_column VARCHAR2(100);
25 l_order_by VARCHAR2(100);
26 l_drill VARCHAR2(100);
27 l_status VARCHAR2(100);
28 l_priority VARCHAR2(100);
29 l_reason VARCHAR2(100);
30 l_lifecycle_phase VARCHAR2(100);
31 l_currency VARCHAR2(100);
32 l_bom_type VARCHAR2(100);
33 l_type VARCHAR2(100);
34 l_manager VARCHAR2(100);
35 l_lob VARCHAR2(100);
36
37 l_from_clause VARCHAR2(1000);
38 l_where_clause VARCHAR2(1000);
39 l_group_by_clause VARCHAR2(1000);
40
41 l_comp_where VARCHAR2(100);
42
43 l_err_msg VARCHAR2(100);
44
45 -- The record structure for bind variable values
46 l_custom_rec BIS_QUERY_ATTRIBUTES;
47
48 l_currency_column varchar2(30);
49 l_currency_string varchar2(100);
50 BEGIN
51
52 eni_dbi_util_pkg.get_parameters
53 (
54 p_page_parameter_tbl,
55 l_period_type,
56 l_period_bitand,
57 l_view_by,
58 l_as_of_date,
59 l_prev_as_of_date,
60 l_report_start,
61 l_cur_period,
62 l_days_into_period,
63 l_comp_type,
64 l_category,
65 l_item,
66 l_org,
67 l_id_column,
68 l_order_by,
69 l_drill,
70 l_status,
71 l_priority,
72 l_reason,
73 l_lifecycle_phase,
74 l_currency,
75 l_bom_type,
76 l_type,
77 l_manager,
78 l_lob
79 );
80
81 /* To provide secondary currency support */
82 l_currency := TRIM(both '''' from l_currency);
83 l_currency_string := eni_dbi_util_pkg.get_curr_prim;
84 l_currency_string := TRIM(both '''' from l_currency_string);
85
86 IF l_currency = l_currency_string THEN
87 l_currency_column := 'PRIM_ACTUAL_G';
88 ELSE
89 l_currency_column := 'SEC_ACTUAL_G';
90 END IF;
91 /* To provide secondary currency support */
92
93 eni_dbi_util_pkg.get_time_clauses
94 (
95 'A',
96 'fgbm',
97 l_period_type,
98 l_period_bitand,
99 l_as_of_date,
100 l_prev_as_of_date,
101 l_report_start,
102 l_cur_period,
103 l_days_into_period,
104 l_comp_type,
105 l_id_column,
106 l_from_clause,
107 l_where_clause,
108 l_group_by_clause
109 );
110
111 l_where_clause := NULL;
112 --Bug 5083913 Replaced l_category with :CATEGORY
113 IF l_category IS NOT NULL THEN
114 l_where_clause := l_where_clause ||
115 'AND product_category_id (+) = :CATEGORY
116 AND gid (+) = 2
117 AND marker (+) = 1';
118 ELSE
119 l_where_clause := l_where_clause ||
120 ' AND gid (+) = 0
121 AND marker(+) = 2';
122 END IF;
123
124 IF l_comp_type = 'SEQUENTIAL' THEN
125 l_comp_where := 'AND c.start_date = p.end_date + 1';
126 ELSIF l_period_type = 'FII_TIME_WEEK' THEN
127 l_comp_where := 'AND c.week_id = p.week_id + 10000';
128 ELSIF l_period_type = 'FII_TIME_ENT_PERIOD' THEN
129 l_comp_where := 'AND c.ent_period_id = p.ent_period_id + 1000';
130 ELSIF l_period_type = 'FII_TIME_ENT_QTR' THEN
131 l_comp_where := 'AND c.ent_qtr_id = p.ent_qtr_id + 10';
132 ELSIF l_period_type = 'FII_TIME_ENT_YEAR' THEN
133 l_comp_where := 'AND c.ent_year_id = p.ent_year_id + 1';
134 END IF;
135
136 /*
137 Bug: 3450100
138 1. BIS PMV returns Order by clause with NLSSORT(t.start_date,'NLS_BINARY')
139 Removed NLSSORT as this must be applied only to character columns not date column
140
141 2. The FII TIME where clause contained AND p.start_date < ' ||'&'||'BIS_PREVIOUS_ASOF_DATE
142 This will return n-1 data points if the as_of_date is first date in the qtr,year,period, week
143 hence removed this condition
144
145
146 */
147 IF UPPER(l_order_by) LIKE '%DESC%' THEN
148 l_order_by := 't.start_date desc' ;
149 ELSE
150 l_order_by := 't.start_date asc' ;
151 END IF;
152
153 x_custom_sql := '
154 SELECT
155 t.name AS VIEWBY,
156 SUM(case when ftrs.report_date = t.c_end_date
157 THEN NVL(fgbm.' || l_currency_column || ', 0)
158 ELSE 0 END) AS ENI_MEASURE1,
159 SUM(case when ftrs.report_date = t.p_end_date
160 THEN NVL(fgbm.' || l_currency_column || ', 0)
161 ELSE 0 END) AS ENI_MEASURE2
162 FROM
163 eni_dbi_gl_base_sum_mv fgbm,
164 fii_time_rpt_struct ftrs,
165 (
166 SELECT
167 c.name,
168 c.'||l_id_column||',
169 c.start_date AS start_date,
170 (case when '|| '&' || 'BIS_CURRENT_ASOF_DATE < c.end_date
171 then '|| '&' || 'BIS_CURRENT_ASOF_DATE else c.end_date end ) AS c_end_date,
172 (case when '|| '&' || 'BIS_PREVIOUS_ASOF_DATE < p.end_date
173 then '|| '&' || 'BIS_PREVIOUS_ASOF_DATE else p.end_date end) AS p_end_date
174 FROM
175 ' || l_period_type ||' c, ' || l_period_type || ' p
176 WHERE
177 c.start_date >= ' || '&' || 'BIS_CURRENT_REPORT_START_DATE
178 AND c.'||l_id_column||' <= :CUR_PERIOD_ID
179 AND p.start_date >= ' || '&' || 'BIS_PREVIOUS_REPORT_START_DATE
180 ' || l_comp_where || '
181 ) t
182 WHERE
183 (
184 t.c_end_date = ftrs.report_date
185 OR t.p_end_date = ftrs.report_date
186 )
187 AND fgbm.time_id (+) = ftrs.time_id
188 AND BITAND(ftrs.record_type_id, ' || '&' || 'BIS_NESTED_PATTERN ) = ftrs.record_type_id
189 ' || l_where_clause || '
190 GROUP BY
191 ' || l_group_by_clause || '
192 ORDER BY
193 ' || l_order_by;
194
195 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
196 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
197 x_custom_output.extend;
198
199 l_custom_rec.attribute_name := ':CUR_PERIOD_ID';
200 l_custom_rec.attribute_value := l_cur_period;
201 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
202 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
203 x_custom_output.extend;
204 x_custom_output(1) := l_custom_rec;
205
206 l_custom_rec.attribute_name := ':CATEGORY';
207 l_custom_rec.attribute_value := l_category;
208 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
209 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
210 x_custom_output.extend;
211 x_custom_output(2) := l_custom_rec;
212
213 --Bug 5083652 -- Start Code
214
215 x_custom_output.extend;
216 l_custom_rec.attribute_name := ':PERIODTYPE';
217 l_custom_rec.attribute_value := REPLACE(l_period_type,'''');
218 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
219 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
220 x_custom_output(3) := l_custom_rec;
221
222 x_custom_output.extend;
223 l_custom_rec.attribute_name := ':COMPARETYPE';
224 l_custom_rec.attribute_value := REPLACE(l_comp_type,'''');
225 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
226 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
227 x_custom_output(4) := l_custom_rec;
228
229
230 x_custom_output.extend;
231 l_custom_rec.attribute_name := ':PERIODAND';
232 l_custom_rec.attribute_value := REPLACE(l_period_bitand,'''');
233 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
234 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
235 x_custom_output(5) := l_custom_rec;
236
237 --Bug 5083652 -- End Code
238
239 END get_sql;
240
241 END eni_dbi_oex_t_pkg;