[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_POP_PKG
Source
1 PACKAGE BODY poa_dbi_pop_pkg
2 /* $Header: poadbipopb.pls 120.4 2006/09/19 17:04:23 sriswami noship $ */
3
4 AS
5
6 -- private methods
7 FUNCTION get_status_sel_clause(p_view_by_col_name in VARCHAR2, p_url in VARCHAR2) return VARCHAR2;
8 FUNCTION get_trend_sel_clause return VARCHAR2;
9
10 FUNCTION get_view_by_col(view_by varchar2) return VARCHAR2;
11 FUNCTION get_status_filter_where return VARCHAR2;
12
13 FUNCTION get_kpi_filter_where return VARCHAR2;
14 ----
15 -- public methods
16
17 PROCEDURE status_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
18 x_custom_sql OUT NOCOPY VARCHAR2,
19 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
20 IS
21 l_query varchar2(4000);
22 l_view_by varchar2(120);
23 l_view_by_col varchar2(120);
24 l_as_of_date date;
25 l_prev_as_of_date date;
26 l_xtd varchar2(10);
27 l_comparison_type varchar2(1) := 'Y';
28 l_nested_pattern number;
29 l_cur_suffix varchar2(2);
30 l_url varchar2(300);
31 l_custom_sql varchar2(4000);
32 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
33 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
34 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
35 l_where_clause VARCHAR2(2000);
36 l_view_by_value VARCHAR2(100);
37 l_mv VARCHAR2(30);
38 l_context_code VARCHAR2(10);
39 l_to_date_type VARCHAR2(10);
40 BEGIN
41 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
42 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
43
44 poa_dbi_sutil_pkg.process_parameters(p_param,l_view_by,l_view_by_col,l_view_by_value, l_comparison_type, l_xtd, l_as_of_date, l_prev_as_of_date, l_cur_suffix, l_nested_pattern, l_where_clause, l_mv, l_join_tbl, l_in_join_tbl,
45 x_custom_output,
46 'N','PO', '5.0', 'VPP','POD');
47
48 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
49 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
50 l_to_date_type := 'RLX';
51 ELSE
52 l_to_date_type := 'XTD';
53 END IF;
54 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt',p_to_date_type => l_to_date_type);
55
56
57 if((l_view_by = 'SUPPLIER+POA_SUPPLIERS') and (l_view_by_value is not null) and (instr(l_view_by_value,',') = 0)) then
58 l_url := null;
59 else
60 l_url := 'pFunctionName=POA_DBI_POP_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=SUPPLIER+POA_SUPPLIERS&pParamIds=Y';
61 end if;
62
63 l_query := get_status_sel_clause(l_view_by_col, l_url) || ' from
64 '|| poa_dbi_template_pkg.status_sql(
65 l_mv,
66 l_where_clause,
67 l_join_tbl,
68 p_use_windowing => 'Y',
69 p_col_name => l_col_tbl,
70 p_use_grpid => 'N',
71 p_filter_where => get_status_filter_where,
72 p_in_join_tables => l_in_join_tbl);
73 x_custom_sql := l_query;
74
75 END;
76
77
78 PROCEDURE trend_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
79 x_custom_sql OUT NOCOPY VARCHAR2,
80 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
81 IS
82 l_query varchar2(32000);
83 l_view_by varchar2(120);
84 l_view_by_col varchar2(120);
85 l_as_of_date date;
86 l_prev_as_of_date date;
87 l_xtd varchar2(10);
88 l_comparison_type varchar2(1) := 'Y';
89 l_nested_pattern number;
90 l_cur_suffix varchar2(2);
91 l_custom_sql varchar2(4000);
92 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
93 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
94 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
95 l_mv VARCHAR2(30);
96 l_where_clause VARCHAR2(2000);
97 l_view_by_value VARCHAR2(100);
98 l_context_code VARCHAR2(10);
99 l_to_date_type VARCHAR2(10);
100 BEGIN
101 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
102 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
103
104 poa_dbi_sutil_pkg.process_parameters(p_param,l_view_by,l_view_by_col,l_view_by_value,l_comparison_type, l_xtd, l_as_of_date, l_prev_as_of_date, l_cur_suffix, l_nested_pattern, l_where_clause, l_mv, l_join_tbl, l_in_join_tbl,
105 x_custom_output,
106 'Y','PO', '5.0', 'VPP','POD');
107 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
108 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
109 l_to_date_type := 'RLX';
110 ELSE
111 l_to_date_type := 'XTD';
112 END IF;
113
114 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt', 'N',p_to_date_type => l_to_date_type);
115
116 l_query := get_trend_sel_clause || ' from
117 '|| poa_dbi_template_pkg.trend_sql(
118 l_xtd,
119 l_comparison_type,
120 l_mv,
121 l_where_clause,
122 l_col_tbl,
123 p_use_grpid => 'N',
124 p_in_join_tables => l_in_join_tbl);
125
126 x_custom_sql := l_query;
127
128 END;
129
130 PROCEDURE kpi_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
131 x_custom_sql OUT NOCOPY VARCHAR2,
132 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
133 IS
134
135 l_query varchar2(4000);
136 l_view_by varchar2(120);
137 l_view_by_col varchar2(120);
138 l_as_of_date date;
139 l_prev_as_of_date date;
140 l_prev_as_of_date2 date;
141 l_xtd varchar2(10);
142 l_comparison_type varchar2(1) := 'Y';
143 l_nested_pattern number;
144 l_cur_suffix varchar2(2);
145 l_custom_sql varchar2(4000);
146 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
147 l_mv varchar2(30);
148 l_org_where varchar2(500);
149 l_where_clause varchar2(1000);
150 l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
151 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
152 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
153 l_custom_rec BIS_QUERY_ATTRIBUTES;
154 l_view_by_value VARCHAR2(100);
155 l_sel_clause VARCHAR2(4000);
156 l_context_code VARCHAR2(10);
157 l_to_date_type VARCHAR2(10);
158 l_cols VARCHAR2(600);
159 BEGIN
160 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
161 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
162 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
163
164 poa_dbi_sutil_pkg.process_parameters(p_param,l_view_by,l_view_by_col,l_view_by_value,l_comparison_type, l_xtd, l_as_of_date, l_prev_as_of_date, l_cur_suffix, l_nested_pattern, l_where_clause, l_mv, l_join_tbl, l_in_join_tbl,
165 x_custom_output,
166 'N','PO', '5.0', 'VPP','POD');
167
168 l_prev_as_of_date2 := poa_dbi_calendar_pkg.previous_period_asof_date(l_prev_as_of_date, l_xtd, l_comparison_type);
169 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
170
171 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
172 l_to_date_type := 'RLX';
173 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt', 'Y',
174 p_to_date_type => l_to_date_type);
175 l_cols := poa_dbi_util_pkg.change_clause('c_purchase_amt','p_purchase_amt') || ' POA_PERCENT1,
176 null POA_PERCENT2,
177 ' || poa_dbi_util_pkg.change_clause('c_purchase_amt_total','p_purchase_amt_total') || ' POA_MEASURE1,
178 null POA_MEASURE2, ';
179 ELSE
180 l_to_date_type := 'XTD';
181 poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt',
182 'Y', poa_dbi_util_pkg.PREV_PREV, p_to_date_type => l_to_date_type);
183 l_cols := poa_dbi_util_pkg.change_clause('c_purchase_amt','p_purchase_amt') || ' POA_PERCENT1,
184 ' || poa_dbi_util_pkg.change_clause('p_purchase_amt','p2_purchase_amt') || ' POA_PERCENT2,
185 ' || poa_dbi_util_pkg.change_clause('c_purchase_amt_total','p_purchase_amt_total') || ' POA_MEASURE1,
186 ' || poa_dbi_util_pkg.change_clause('p_purchase_amt_total','p2_purchase_amt_total') || ' POA_MEASURE2, ';
187 END IF;
188
189 /* l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
190
191 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
192 l_join_rec.table_name :=
193 poa_dbi_sutil_pkg.get_table('SUPPLIER+POA_SUPPLIERS', 'PO', '5.0');
194 l_join_rec.table_alias := 'v';
195 l_join_rec.fact_column :=
196 poa_dbi_sutil_pkg.get_col_name('SUPPLIER+POA_SUPPLIERS', 'PO', '5.0', 'POD');
197 l_join_rec.column_name := 'id';
198 ELSE
199 l_join_rec.table_name :=
200 poa_dbi_sutil_pkg.get_table('ORGANIZATION+FII_OPERATING_UNITS', 'PO', '5.0');
201 l_join_rec.table_alias := 'v';
202 l_join_rec.fact_column :=
203 poa_dbi_sutil_pkg.get_col_name('ORGANIZATION+FII_OPERATING_UNITS', 'PO', '5.0', 'POD');
204 l_join_rec.column_name := 'id';
205 END IF;
206
207 l_join_tbl.extend;
208 l_join_tbl(l_join_tbl.count) :=l_join_rec;
209 */
210 if(l_view_by_col = 'commodity_id') then
211 l_sel_clause := 'select decode(v.name,null, fnd_message.get_string(''POA'', ''POA_DBI_APL_UNASSIGNED''), v.name) VIEWBY,
212 decode(v.commodity_id,null, -1, v.commodity_id) VIEWBYID,';
213 else
214 l_sel_clause := 'select v.value VIEWBY, v.id VIEWBYID, ';
215 end if;
216
217 l_query :=
218 l_sel_clause || '
219 oset.POA_PERCENT1 POA_PERCENT1,
220 oset.POA_PERCENT2 POA_PERCENT2,
221 oset.POA_MEASURE1 POA_MEASURE1,
222 oset.POA_MEASURE2 POA_MEASURE2,
223 oset.POA_MEASURE4 POA_MEASURE4,
224 oset.POA_MEASURE5 POA_MEASURE5,
225 oset.POA_MEASURE6 POA_MEASURE6,
226 oset.POA_MEASURE7 POA_MEASURE7
227 from
228 (select * from (select ' || l_view_by_col || ',' || l_cols ||
229 ' nvl(c_purchase_amt,0) POA_MEASURE4,
230 p_purchase_amt POA_MEASURE5,
231 nvl(c_purchase_amt_total,0) POA_MEASURE6,
232 p_purchase_amt_total POA_MEASURE7
233 from
234 ' || poa_dbi_template_pkg.status_sql(
235 l_mv,
236 l_where_clause,
237 l_join_tbl,
238 p_use_windowing => 'N',
239 p_col_name => l_col_tbl,
240 p_use_grpid => 'N',
241 p_filter_where => get_kpi_filter_where,
242 p_in_join_tables => l_in_join_tbl);
243
244 x_custom_sql := l_query;
245
246 l_custom_rec.attribute_name := '&PREV_PREV_DATE';
247 l_custom_rec.attribute_value := TO_CHAR(l_prev_as_of_date2, 'DD/MM/YYYY');
248 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
249 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
250 x_custom_output.EXTEND;
251 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
252
253
254 END;
255
256 FUNCTION get_status_filter_where return VARCHAR2
257 IS
258 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
259 BEGIN
260 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
261 l_col_tbl.extend;
262 l_col_tbl(1) := 'POA_MEASURE1';
263 l_col_tbl.extend;
264 l_col_tbl(2) := 'POA_PERCENT2';
265 l_col_tbl.extend;
266 l_col_tbl(3) := 'POA_PERCENT3';
267 l_col_tbl.extend;
268 l_col_tbl(4) := 'POA_MEASURE3';
269 l_col_tbl.extend;
270 l_col_tbl(5) := 'POA_MEASURE4';
271 l_col_tbl.extend;
272 l_col_tbl(6) := 'POA_MEASURE5';
273 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
274
275 END;
276
277 FUNCTION get_kpi_filter_where return VARCHAR2
278 IS
279 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
280 BEGIN
281 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
282 l_col_tbl.extend;
283 l_col_tbl(1) := 'POA_PERCENT1';
284 l_col_tbl.extend;
285 l_col_tbl(2) := 'POA_PERCENT2';
286 l_col_tbl.extend;
287 l_col_tbl(3) := 'POA_MEASURE4';
288 l_col_tbl.extend;
289 l_col_tbl(4) := 'POA_MEASURE5';
290 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
291
292 END;
293
294
295
296
297 FUNCTION get_status_sel_clause(p_view_by_col_name in VARCHAR2, p_url in VARCHAR2) return VARCHAR2
298 IS
299 l_sel_clause varchar2(4000);
300 BEGIN
301
302 if(p_view_by_col_name = 'commodity_id') then
303 l_sel_clause := 'select decode(v.name,null, fnd_message.get_string(''POA'', ''POA_DBI_APL_UNASSIGNED''), v.name) VIEWBY,
304 decode(v.commodity_id,null, -1, v.commodity_id) VIEWBYID,';
305 else
306 l_sel_clause := 'select v.value VIEWBY, v.id VIEWBYID, ';
307 end if;
308 l_sel_clause := l_sel_clause ||
309 ' oset.POA_MEASURE1 POA_MEASURE1, --PO Purchases Amount
310 oset.POA_MEASURE1 POA_MEASURE2, --PO Purchases Amount
311 oset.POA_PERCENT3 POA_PERCENT3, --Growth Rate
312 oset.POA_MEASURE3 POA_MEASURE3, --Percent of Total
313 oset.POA_PERCENT2 POA_PERCENT2, --Change
314 oset.POA_MEASURE4 POA_MEASURE4, --Total PO Purchases Amount
315 oset.POA_MEASURE5 POA_MEASURE5, --Total Growth Rate
316 oset.POA_MEASURE6 POA_MEASURE6, --Total Percent of Total
317 ''' || p_url || ''' POA_MEASURE7,
318 ''' || p_url || ''' POA_MEASURE8
319 from
320 (select (rank() over
321 (&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col_name || ')) - 1 rnk,'
322 || p_view_by_col_name || ',
323 POA_MEASURE1, POA_PERCENT3, POA_MEASURE3, POA_PERCENT2, POA_MEASURE4,
324 POA_MEASURE5, POA_MEASURE6 from
325 (select ' || p_view_by_col_name || ',
326 ' || p_view_by_col_name || ' VIEWBY,
327 nvl(c_purchase_amt,0) POA_MEASURE1,
328 ' || poa_dbi_util_pkg.change_clause('c_purchase_amt','p_purchase_amt') || ' POA_PERCENT3,
329 ' || poa_dbi_util_pkg.rate_clause('c_purchase_amt','c_purchase_amt_total') || ' POA_MEASURE3,
330 ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_purchase_amt','c_purchase_amt_total'),poa_dbi_util_pkg.rate_clause('p_purchase_amt','p_purchase_amt_total'),'P') || ' POA_PERCENT2,
331 nvl(c_purchase_amt_total,0) POA_MEASURE4,
332 ' || poa_dbi_util_pkg.change_clause('c_purchase_amt_total','p_purchase_amt_total') || ' POA_MEASURE5,
333 decode(c_purchase_amt_total, null, null, 100) POA_MEASURE6';
334
335 return l_sel_clause;
336
337 END;
338
339
340 FUNCTION get_trend_sel_clause return VARCHAR2
341 IS
342 l_sel_clause varchar2(4000);
343 BEGIN
344
345 l_sel_clause :=
346 'select cal.name VIEWBY,
347 nvl(p_purchase_amt,0) POA_MEASURE1,
348 nvl(c_purchase_amt,0) POA_MEASURE2,
349 ' || poa_dbi_util_pkg.change_clause('c_purchase_amt','p_purchase_amt') || ' POA_MEASURE3,
350 nvl(p_purchase_amt,0) POA_MEASURE4,
351 nvl(c_purchase_amt,0) POA_MEASURE5';
352
353 return l_sel_clause;
354
355 END;
356
357
358 FUNCTION get_view_by_col(view_by varchar2) return varchar2
359 is
360 BEGIN
361 return (case view_by
362 when 'ORGANIZATION+FII_OPERATING_UNITS' then 'org_id'
363 when 'ITEM+ENI_ITEM_PO_CAT' then 'category_id'
364 when 'ITEM+POA_ITEMS' then 'po_item_id'
365 when 'SUPPLIER+POA_SUPPLIERS' then 'supplier_id'
366 when 'SUPPLIER+POA_SUPPLIER_SITES' then 'supplier_site_id'
367 when 'HRI_PERSON+HRI_PER' then 'buyer_id'
368 else ''
369 end);
370 END;
371
372 END POA_DBI_POP_PKG;