[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_IAP_PKG
Source
1 PACKAGE BODY poa_dbi_iap_pkg
2 /* $Header: poadbiavgprb.pls 120.14 2006/05/05 10:50:40 sriswami noship $ */
3 AS
4
5
6 FUNCTION get_status_filter_where RETURN VARCHAR2 ;
7
8 FUNCTION get_status_sel_clause(p_view_by_col_name in VARCHAR2)
9 RETURN VARCHAR2 ;
10
11 FUNCTION get_iapd_filter_where RETURN VARCHAR2 ;
12
13 FUNCTION get_trend_sel_clause
14 return VARCHAR2 ;
15
16 FUNCTION get_dtl_sts_filter_where RETURN VARCHAR2 ;
17
18 FUNCTION get_group_and_sel_clause(
19 p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
20 , p_use_alias IN VARCHAR2
21 ) RETURN VARCHAR2 ;
22
23 FUNCTION dtl_status_sql (
24 p_fact_name IN VARCHAR2
25 , p_where_clause IN VARCHAR2
26 , p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
27 , p_use_windowing IN VARCHAR2
28 , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
29 , p_use_grpid VARCHAR2 := 'Y'
30 , p_paren_count IN NUMBER := 4
31 , p_filter_where IN VARCHAR2 := NULL
32 , p_generate_viewby IN VARCHAR2 := 'Y'
33 , p_in_join_tables IN poa_dbi_util_pkg.poa_dbi_in_join_tbl := NULL
34 , p_uom_code IN VARCHAR2
35 , p_view_by IN VARCHAR2
36 ) RETURN VARCHAR2 ;
37
38
39 FUNCTION get_dtl_sel_clause(p_view_by_col in VARCHAR2)
40 RETURN VARCHAR2 ;
41
42 FUNCTION get_paren_str(p_paren_count IN NUMBER,
43 p_filter_where IN VARCHAR2) RETURN VARCHAR2 ;
44
45 PROCEDURE status_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL
46 ,x_custom_sql OUT NOCOPY VARCHAR2
47 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
48 IS
49 l_query varchar2(8000);
50 l_view_by varchar2(120);
51 l_view_by_col varchar2(120);
52 l_as_of_date date;
53 l_prev_as_of_date date;
54 l_xtd varchar2(10);
55 l_comparison_type varchar2(1) := 'Y';
56 l_nested_pattern number;
57 l_cur_suffix varchar2(3);
58 l_url varchar2(300);
59 l_custom_sql varchar2(4000);
60 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
61 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
62 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
63 l_where_clause VARCHAR2(2000);
64 l_view_by_value VARCHAR2(100);
65 l_mv VARCHAR2(30);
66 l_context_code VARCHAR2(10);
67 l_to_date_type VARCHAR2(10);
68 l_file varchar2(500);
69 BEGIN
70 l_comparison_type := 'Y';
71
72 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
73 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
74
75 poa_dbi_sutil_pkg.process_parameters (
76 p_param => p_param,
77 p_view_by => l_view_by,
78 p_view_by_col_name => l_view_by_col ,
79 p_view_by_value => l_view_by_value,
80 p_comparison_type => l_comparison_type ,
81 p_xtd => l_xtd,
82 p_as_of_date => l_as_of_date ,
83 p_prev_as_of_date => l_prev_as_of_date ,
84 p_cur_suffix => l_cur_suffix ,
85 p_nested_pattern => l_nested_pattern ,
86 p_where_clause => l_where_clause ,
87 p_mv => l_mv ,
88 p_join_tbl => l_join_tbl ,
89 p_in_join_tbl => l_in_join_tbl,
90 x_custom_output => x_custom_output,
91 p_trend => 'N',
92 p_func_area => 'PO',
93 p_version => '8.0',
94 p_role => 'VPP',
95 p_mv_set => 'POD');
96
97 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
98
99 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
100 l_to_date_type := 'RLX';
101 ELSE
102 l_to_date_type := 'XTD';
103 END IF;
104
105 poa_dbi_util_pkg.add_column(l_col_tbl,
106 'purchase_amt_' || l_cur_suffix,
107 'purchase_amt',
108 p_to_date_type => l_to_date_type);
109
110
111 poa_dbi_util_pkg.add_column(l_col_tbl,
112 'nz_quantity',
113 'nz_quantity',
114 p_to_date_type => l_to_date_type);
115
116
117
118 l_query := get_status_sel_clause(l_view_by_col) ;
119
120 l_query := l_query || ' from
121 '|| poa_dbi_template_pkg.status_sql(
122 l_mv,
123 l_where_clause,
124 l_join_tbl,
125 p_use_windowing => 'Y',
126 p_col_name => l_col_tbl,
127 p_use_grpid => 'N',
128 p_filter_where => get_status_filter_where,
129 p_in_join_tables => l_in_join_tbl);
130
131 x_custom_sql := l_query;
132
133 poa_dbi_util_pkg.get_custom_status_binds(x_custom_output);
134 poa_dbi_util_pkg.get_custom_rolling_binds(x_custom_output,l_xtd);
135
136 END status_sql ;
137
138 ----------------------------------------------------------------------------------
139
140 FUNCTION get_status_filter_where RETURN VARCHAR2
141 IS
142 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
143 BEGIN
144 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
145 l_col_tbl.extend;
146 l_col_tbl(1) := 'POA_MEASURE1';
147 l_col_tbl.extend;
148 l_col_tbl(2) := 'POA_PERCENT1';
149 l_col_tbl.extend;
150 l_col_tbl(3) := 'POA_MEASURE2';
151 l_col_tbl.extend;
152 l_col_tbl(4) := 'POA_PERCENT2';
153 l_col_tbl.extend;
154 l_col_tbl(5) := 'POA_MEASURE12';
155
156 RETURN poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
157
158 END get_status_filter_where ;
159
160
161 ----------------------------------------------------------------------------------
162
163 FUNCTION get_status_sel_clause(p_view_by_col_name in VARCHAR2)
164 RETURN VARCHAR2
165 IS
166 l_sel_clause varchar2(4000);
167 BEGIN
168
169 if(p_view_by_col_name = 'commodity_id') then
170 l_sel_clause := 'select decode(v.name,null,
171 fnd_message.get_string(''POA'', ''POA_DBI_APL_UNASSIGNED''), v.name) VIEWBY,
172 decode(v.commodity_id,null, -1, v.commodity_id) VIEWBYID,';
173 else
174 l_sel_clause := 'select v.value VIEWBY, v.id VIEWBYID, ';
175 end if;
176
177 l_sel_clause := l_sel_clause || '
178 v.description POA_ATTRIBUTE1,
179 v2.description POA_ATTRIBUTE2,
180 oset.POA_MEASURE12 POA_MEASURE12,
181 oset.POA_MEASURE1 POA_MEASURE1,
182 oset.POA_PERCENT1 POA_PERCENT1 ,
183 oset.POA_MEASURE2 POA_MEASURE2 ,
184 oset.POA_PERCENT2 POA_PERCENT2 ,
185 oset.POA_MEASURE3 POA_MEASURE3 ,
186 oset.POA_PERCENT3 POA_PERCENT3 ,
187 v2.unit_of_measure POA_ATTRIBUTE9
188 from
189 (select (rank() over
190 (&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col_name || ', base_uom )) - 1 rnk,'
191 || p_view_by_col_name || ', base_uom ,
192 POA_MEASURE12, POA_PERCENT1, POA_MEASURE1, POA_PERCENT2, POA_MEASURE2,
193 POA_MEASURE3, POA_PERCENT3
194 from
195 (select ' || p_view_by_col_name || ',
196 ' || p_view_by_col_name || ' VIEWBY, base_uom,
197 decode(base_uom,null,to_number(null),nvl(c_nz_quantity,0)) POA_MEASURE12,
198 nvl(c_purchase_amt,0)/decode(c_nz_quantity,0,null,c_nz_quantity) POA_MEASURE1,
199 (((nvl(c_purchase_amt,0)/decode(c_nz_quantity,0,null,c_nz_quantity)) -
200 (nvl(p_purchase_amt,0)/decode(p_nz_quantity,0,null,p_nz_quantity)))/
201 (nvl(p_purchase_amt,0)/decode(p_nz_quantity,0,null,p_nz_quantity))) * 100 POA_PERCENT1,
202 nvl(c_purchase_amt,0) POA_MEASURE2,
203 ((nvl(c_purchase_amt,0)- nvl(p_purchase_amt,0)) /
204 decode(p_purchase_amt,0,null,p_purchase_amt))*100 POA_PERCENT2,
205 nvl(c_purchase_amt_total,0) POA_MEASURE3,
206 ((nvl(c_purchase_amt_total,0)-nvl(p_purchase_amt_total,0))/
207 (decode(p_purchase_amt_total,0,null,p_purchase_amt_total))) * 100 POA_PERCENT3 ' ;
208
209
210 return l_sel_clause;
211
212 END get_status_sel_clause ;
213
214
215 ----------------------------------------------------------------------------------
216
217 FUNCTION get_iapd_filter_where RETURN VARCHAR2
218 IS
219 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
220 BEGIN
221 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
222 l_col_tbl.extend;
223 l_col_tbl(1) := 'POA_MEASURE1';
224 l_col_tbl.extend;
225 l_col_tbl(2) := 'POA_PERCENT1';
226 l_col_tbl.extend;
227 l_col_tbl(3) := 'POA_MEASURE2';
228 l_col_tbl.extend;
229 l_col_tbl(4) := 'POA_PERCENT2';
230
231 RETURN poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
232
233 END get_iapd_filter_where ;
234
235 ----------------------------------------------------------------------------------
236 PROCEDURE iapd_dtl_sql (p_param in BIS_PMV_PAGE_PARAMETER_TBL
237 ,x_custom_sql OUT NOCOPY VARCHAR2
238 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
239 IS
240 l_query varchar2(8000);
241 l_view_by varchar2(120);
242 l_view_by_col varchar2(120);
243 l_as_of_date date;
244 l_prev_as_of_date date;
245 l_xtd varchar2(10);
246 l_comparison_type varchar2(1) := 'Y';
247 l_nested_pattern number;
248 l_cur_suffix varchar2(3);
249 l_url varchar2(300);
250 l_custom_sql varchar2(4000);
251 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
252 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
253 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
254 l_where_clause VARCHAR2(2000);
255 l_view_by_value VARCHAR2(100);
256 l_mv VARCHAR2(30);
257 l_context_code VARCHAR2(10);
258 l_to_date_type VARCHAR2(10);
259 l_file varchar2(500);
260 l_uom VARCHAR2(200);
261 l_custom_rec BIS_QUERY_ATTRIBUTES;
262 BEGIN
263
264 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
265 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
266 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
267
268 FOR i in 1..p_param.last
269 LOOP
270 IF p_param(i).parameter_name = 'LOOKUP+UOMCODE' THEN
271 l_uom := p_param(i).parameter_value ;
272 END IF ;
273 END LOOP ;
274
275
276 poa_dbi_sutil_pkg.drill_process_parameters(
277 p_param => p_param,
278 p_cur_suffix => l_cur_suffix,
282 p_version => '8.0',
279 p_where_clause => l_where_clause,
280 p_in_join_tbl => l_in_join_tbl,
281 p_func_area => 'PO',
283 p_role => 'VPP',
284 p_mv_set => 'POD');
285
286
287 x_custom_sql := 'Select
288 poh.segment1 POA_ATTRIBUTE1,
289 poorg.name POA_ATTRIBUTE8,
290 uom.description POA_ATTRIBUTE10,
291 substrb(perf.first_name,1,1) || ''. ''|| perf.last_name POA_ATTRIBUTE3,
292 POA_MEASURE12,
293 POA_MEASURE1,
294 POA_MEASURE5,
295 POA_MEASURE4,
296 POA_MEASURE2,
297 POA_MEASURE13,
298 POA_MEASURE3,
299 i.po_header_id POA_ATTRIBUTE6,
300 ''pFunctionName=POA_DBI_ISP_DRILL&PoHeaderId=''||poh.po_header_id||''&addBreadCrumb=Y&retainAM=Y'' POA_ATTRIBUTE4
301 from
302 (select (rank() over
303 (&ORDER_BY_CLAUSE nulls last, po_header_id,org_id,buyer_id,base_uom)) - 1 rnk,
304 po_header_id,
305 org_id,
306 buyer_id,
307 base_uom,
308 decode(base_uom,null,to_number(null),nvl(POA_MEASURE12,0)) POA_MEASURE12,
309 POA_MEASURE3 / decode(POA_MEASURE13, 0, null, POA_MEASURE13) POA_MEASURE1,
310 POA_MEASURE5, POA_MEASURE4, nvl(POA_MEASURE2,0) POA_MEASURE2,
311 POA_MEASURE13, nvl(POA_MEASURE3,0) POA_MEASURE3
312 from
313 (select po_header_id, base_uom, buyer_id, org_id,
314 decode(base_uom,null,to_number(null),nvl(quantity,0)) POA_MEASURE12,
315 total_purch_amt /decode(total_quantity,0,null,total_quantity) POA_MEASURE1,
316 purch_amt / decode(quantity,0,null, quantity) POA_MEASURE5,
317 (purch_amt / decode(quantity,0,null, quantity) -
318 total_purch_amt /decode(total_quantity,0,null,total_quantity)) POA_MEASURE4,
319 purch_amt POA_MEASURE2,
320 decode(base_uom,null,to_number(null),nvl(total_quantity,0)) POA_MEASURE13,
321 total_purch_amt POA_MEASURE3
322 from
323 (select fact.po_header_id,
324 fact.base_uom,
325 fact.buyer_id,
326 fact.org_id,
327 sum(quantity) quantity,
328 sum(purchase_amt_' || l_cur_suffix || ') purch_amt,
329 sum(sum(quantity)) over () total_quantity,
330 sum(sum(purchase_amt_' || l_cur_suffix || ')) over () total_purch_amt
331 from poa_dbi_pod_f_v fact
332 where fact.approved_date between &BIS_CURRENT_EFFECTIVE_START_DATE
333 and &BIS_CURRENT_ASOF_DATE
334 and fact.consigned_code <> 1
335 and fact.purchase_amt_'|| l_cur_suffix || ' > 0
336 and fact.base_uom = &BASEUOM '|| l_where_clause ||'
337 group by fact.po_header_id, fact.base_uom, fact.org_id , fact.buyer_id))
338 where coalesce( decode(POA_MEASURE5,0,null,POA_MEASURE5),
339 decode(POA_MEASURE2,0,null,POA_MEASURE2)) is not null )
340 i,
341 po_headers_all poh,
342 per_all_people_f perf,
343 mtl_units_of_measure_vl uom,
344 hr_all_organization_units_vl poorg
345 where i.po_header_id = poh.po_header_id
346 and i.buyer_id = perf.person_id
347 and sysdate between perf.effective_start_date and perf.effective_end_date
348 and i.base_uom = uom.unit_of_measure(+)
349 and i.org_id = poorg.organization_id
350 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
351 ORDER BY rnk ' ;
352
353 poa_dbi_util_pkg.get_custom_status_binds(x_custom_output);
354 poa_dbi_util_pkg.get_custom_rolling_binds(x_custom_output,l_xtd);
355
356 l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
357 l_custom_rec.attribute_name := '&BASEUOM';
358 l_custom_rec.attribute_value := l_uom;
359 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
360 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.CHARACTER_BIND;
361 x_custom_output.EXTEND;
362 x_custom_output(x_custom_output.COUNT) := l_custom_rec;
363
364 END iapd_dtl_sql ;
365
366
367 ----------------------------------------------------------------------------------
368
369 PROCEDURE iap_trend_rpt_sql (p_param in BIS_PMV_PAGE_PARAMETER_TBL
370 ,x_custom_sql OUT NOCOPY VARCHAR2
371 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
372 IS
373 l_query varchar2(8000);
374 l_view_by varchar2(120);
375 l_view_by_col varchar2(120);
376 l_as_of_date date;
377 l_prev_as_of_date date;
378 l_xtd varchar2(10);
379 l_comparison_type varchar2(1) := 'Y';
380 l_nested_pattern number;
381 l_cur_suffix varchar2(3);
382 l_url varchar2(300);
383 l_custom_sql varchar2(4000);
384 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
385 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
386 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
387 l_where_clause VARCHAR2(2000);
388 l_view_by_value VARCHAR2(100);
389 l_mv VARCHAR2(30);
390 l_context_code VARCHAR2(10);
391 l_to_date_type VARCHAR2(10);
392 l_file varchar2(500);
393 l_uom VARCHAR2(200);
394 l_view_by_col_name VARCHAR2(250);
395 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
396 BEGIN
397
398 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
399 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
400
401 FOR i in 1..p_param.last
402 LOOP
403 IF p_param(i).parameter_name = 'LOOKUP+UOMCODE' THEN
404 l_uom := p_param(i).parameter_value ;
405 l_uom := nvl(l_uom,'-1');
406 END IF ;
410 poa_dbi_sutil_pkg.process_parameters(p_param,
407 END LOOP ;
408
409 ---Get the Purchased Amt. Measure
411 l_view_by,
412 l_view_by_col_name,
413 l_view_by_value,
414 l_comparison_type,
415 l_xtd,
416 l_as_of_date,
417 l_prev_as_of_date,
418 l_cur_suffix,
419 l_nested_pattern,
420 l_where_clause,
421 l_mv,
422 l_join_tbl,
423 l_in_join_tbl,
424 x_custom_output,
425 p_trend => 'Y',
426 p_func_area => 'PO',
427 p_version => '8.0',
428 p_role => 'VPP',
429 p_mv_set => 'POD');
430
431 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
432 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
433 l_to_date_type := 'RLX';
434 ELSE
435 l_to_date_type := 'XTD';
436 END IF;
437 poa_dbi_util_pkg.add_column(l_col_tbl
438 , 'purchase_amt_' || l_cur_suffix
439 , 'purchase_amt'
440 , p_grand_total => 'N'
441 , p_prior_code => poa_dbi_util_pkg.both_priors
442 , p_to_date_type => l_to_date_type );
443
444 poa_dbi_util_pkg.add_column(l_col_tbl
445 , 'nz_quantity'
446 , 'nz_quantity'
447 , p_grand_total => 'N'
448 , p_prior_code => poa_dbi_util_pkg.both_priors
449 , p_to_date_type => l_to_date_type );
450
451
452 ---Now populate the MV table list
453 l_mv_tbl.extend;
454 l_mv_tbl(1).mv_name := l_mv;
455 l_mv_tbl(1).mv_col := l_col_tbl;
456 l_mv_tbl(1).mv_where := l_where_clause;
457 l_mv_tbl(1).in_join_tbls := l_in_join_tbl;
458 l_mv_tbl(1).use_grp_id := 'N';
459 l_mv_tbl(1).mv_hint := poa_dbi_sutil_pkg.get_fact_hint(l_mv);
460 l_mv_tbl(1).mv_xtd := l_xtd;
461
462 l_where_clause := l_where_clause ||
463 ' and nvl(fact.base_uom ,''-1'') = ''' || l_uom || '''' ;
464
465
466 l_query := get_trend_sel_clause || ' from '|| fnd_global.newline ||
467 poa_dbi_template_pkg.trend_sql(
468 p_xtd => l_xtd,
469 p_comparison_type => l_comparison_type,
470 p_fact_name => l_mv,
471 p_where_clause => l_where_clause,
472 p_col_name => l_col_tbl,
473 p_use_grpid => 'N',
474 p_in_join_tables => l_in_join_tbl);
475
476 x_custom_sql := l_query ;
477
478 END iap_trend_rpt_sql ;
479 ----------------------------------------------------------------------------------
480
481 FUNCTION get_trend_sel_clause
482 return VARCHAR2
483 IS
484 l_sel_clause VARCHAR2(4000);
485 BEGIN
486 l_sel_clause := 'select cal.name VIEWBY,';
487 l_sel_clause := l_sel_clause ||
488 ' nvl(c_purchase_amt,0)/decode(c_nz_quantity,0,null,c_nz_quantity) POA_MEASURE1,
489 (((nvl(c_purchase_amt,0)/decode(c_nz_quantity,0,null,c_nz_quantity)) -
490 (nvl(p_purchase_amt,0)/decode(p_nz_quantity,0,null,p_nz_quantity)))/
491 (nvl(p_purchase_amt,0)/decode(p_nz_quantity,0,null,p_nz_quantity))) * 100 POA_PERCENT1 ,
492 nvl(c_nz_quantity,0) POA_MEASURE12, ' ||
493 poa_dbi_util_pkg.change_clause('c_nz_quantity','p_nz_quantity') || ' POA_PERCENT2,
494 nvl(c_purchase_amt,0) POA_MEASURE2, ' ||
495 poa_dbi_util_pkg.change_clause('c_purchase_amt','p_purchase_amt') || ' POA_PERCENT3 ,
496 nvl(p_purchase_amt,0)/decode(p_nz_quantity,0,null,p_nz_quantity) POA_MEASURE3 ,
497 (nvl(p_purchase_amt,0)) POA_MEASURE4 ,
498 nvl(p_nz_quantity,0) POA_MEASURE5 ' ;
499
500
501 RETURN l_sel_clause;
502 END get_trend_sel_clause ;
503
504 ----------------------------------------------------------------------------------
505
506 PROCEDURE iapd_rpt_sql (p_param in BIS_PMV_PAGE_PARAMETER_TBL
507 ,x_custom_sql OUT NOCOPY VARCHAR2
508 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
509 IS
510 l_query varchar2(8000);
511 l_view_by varchar2(120);
512 l_view_by_col varchar2(120);
513 l_as_of_date date;
514 l_prev_as_of_date date;
515 l_xtd varchar2(10);
516 l_comparison_type varchar2(1) := 'Y';
517 l_nested_pattern number;
518 l_cur_suffix varchar2(3);
519 l_url varchar2(300);
520 l_custom_sql varchar2(4000);
521 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
522 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
523 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
524 l_where_clause VARCHAR2(2000);
525 l_view_by_value VARCHAR2(100);
526 l_mv VARCHAR2(30);
527 l_context_code VARCHAR2(10);
528 l_to_date_type VARCHAR2(10);
529 l_file varchar2(500);
530 l_uom VARCHAR2(200);
531 l_from_clause VARCHAR2(500);
532 l_where_clause2 VARCHAR2(500);
533 l_sel_clause varchar2(4000);
534 BEGIN
535 l_comparison_type := 'Y';
536
537
538 FOR i in 1..p_param.last
539 LOOP
540 IF p_param(i).parameter_name = 'LOOKUP+UOMCODE' THEN
541 l_uom := p_param(i).parameter_value ;
545
542 l_uom := nvl(l_uom,'-1');
543 END IF ;
544 END LOOP ;
546
547 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
548 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
549 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
550
551
552 poa_dbi_sutil_pkg.process_parameters(p_param,
553 l_view_by,
554 l_view_by_col,
555 l_view_by_value,
556 l_comparison_type,
557 l_xtd,
558 l_as_of_date,
559 l_prev_as_of_date,
560 l_cur_suffix,
561 l_nested_pattern,
562 l_where_clause,
563 l_mv,
564 l_join_tbl,
565 l_in_join_tbl,
566 x_custom_output,
567 'N','PO', '8.0', 'VPP','POD');
568
569
570 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
571 l_to_date_type := 'RLX';
572 ELSE
573 l_to_date_type := 'XTD';
574 END IF;
575
576 poa_dbi_util_pkg.add_column(l_col_tbl,
577 'purchase_amt_' || l_cur_suffix,
578 'purchase_amt',
579 p_to_date_type => l_to_date_type);
580
581
582 poa_dbi_util_pkg.add_column(l_col_tbl,
583 'nz_quantity',
584 'nz_quantity',
585 p_to_date_type => l_to_date_type);
586
587
588 l_query := get_dtl_sel_clause(l_view_by_col) ;
589
590 l_query := l_query || ' from
591 '|| dtl_status_sql(
592 l_mv,
593 l_where_clause,
594 l_join_tbl,
595 p_use_windowing => 'Y',
596 p_col_name => l_col_tbl,
597 p_use_grpid => 'N',
598 p_filter_where => get_dtl_sts_filter_where,
599 p_in_join_tables => l_in_join_tbl,
600 p_uom_code => l_uom ,
601 p_view_by => l_view_by );
602
603 x_custom_sql := l_query;
604
605 poa_dbi_util_pkg.get_custom_status_binds(x_custom_output);
606 poa_dbi_util_pkg.get_custom_rolling_binds(x_custom_output,l_xtd);
607
608 END iapd_rpt_sql ;
609
610 ----------------------------------------------------------------------------------
611
612 FUNCTION get_dtl_sts_filter_where RETURN VARCHAR2
613 IS
614 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
615 BEGIN
616 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
617 l_col_tbl.extend;
618 l_col_tbl(1) := 'POA_MEASURE1';
619 l_col_tbl.extend;
620 l_col_tbl(2) := 'POA_PERCENT1';
621 l_col_tbl.extend;
622 l_col_tbl(3) := 'POA_MEASURE2';
623 l_col_tbl.extend;
624 l_col_tbl(4) := 'POA_PERCENT2';
625 l_col_tbl.extend;
626 l_col_tbl(5) := 'POA_MEASURE12';
627
628 RETURN poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
629
630 END get_dtl_sts_filter_where ;
631
632 ----------------------------------------------------------------------------------
633
634 FUNCTION dtl_status_sql (
635 p_fact_name IN VARCHAR2
636 , p_where_clause IN VARCHAR2
637 , p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
638 , p_use_windowing IN VARCHAR2
639 , p_col_name IN poa_dbi_util_pkg.poa_dbi_col_tbl
640 , p_use_grpid VARCHAR2 := 'Y'
641 , p_paren_count IN NUMBER := 4
642 , p_filter_where IN VARCHAR2 := NULL
643 , p_generate_viewby IN VARCHAR2 := 'Y'
644 , p_in_join_tables IN poa_dbi_util_pkg.poa_dbi_in_join_tbl := NULL
645 , p_uom_code IN VARCHAR2
646 , p_view_by IN VARCHAR2
647 )
648 RETURN VARCHAR2
649 IS
650 l_query VARCHAR2 (10000);
651 l_col_names VARCHAR2 (10000);
652 l_group_and_sel_clause VARCHAR2 (10000);
653 l_from_clause VARCHAR2 (10000);
654 l_full_where_clause VARCHAR2 (10000);
655 l_grpid_clause VARCHAR2 (200);
656 l_compute_prior VARCHAR2 (1) := 'N';
657 l_compute_prev_prev VARCHAR2 (1) := 'N';
658 l_paren_str VARCHAR2 (2000);
659 l_compute_opening_bal VARCHAR2(1) := 'N';
660 l_inlist VARCHAR2 (300);
661 l_inlist_bmap NUMBER := 0;
662 l_total_col_names VARCHAR2 (10000);
663 l_viewby_rank_where VARCHAR2 (10000);
664 l_in_join_tables VARCHAR2 (1000) := '';
665 l_filter_where VARCHAR2 (1000);
666 l_join_tables VARCHAR2 (10000);
667 l_col_calc_tbl poa_dbi_util_pkg.poa_dbi_col_calc_tbl;
668 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl ;
669 l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC ;
670 l_group_by varchar2(100);
671 l_add_sel_col varchar2(100);
672
673 BEGIN
674
675 IF (p_use_grpid = 'Y')
676 THEN
677 l_grpid_clause := 'and fact.grp_id = decode(cal.period_type_id,1,14,16,13,32,11,64,7)';
681 END IF;
678 ELSIF (p_use_grpid = 'R')
679 THEN
680 l_grpid_clause := 'and fact.grp_id = decode(cal.period_type_id,1,0,16,1,32,3,64,7)';
682 l_group_and_sel_clause := get_group_and_sel_clause(p_join_tables, p_use_alias => 'Y');
683
684 IF(p_in_join_tables is not null) then
685
686 FOR i in 1 .. p_in_join_tables.COUNT
687 LOOP
688 l_in_join_tables := l_in_join_tables || ', ' || p_in_join_tables(i).table_name || ' ' || p_in_join_tables(i).table_alias;
689 END LOOP;
690 END IF;
691
692 poa_dbi_template_pkg.get_status_col_calc( p_col_name
693 , l_col_calc_tbl
694 , l_inlist_bmap
695 , l_compute_prior
696 , l_compute_prev_prev
697 , l_compute_opening_bal);
698
699 l_col_names := '';
700
701 FOR i IN 1 .. l_col_calc_tbl.COUNT
702 LOOP
703 l_col_names := l_col_names
704 || ', '
705 || l_col_calc_tbl(i).calc_begin
706 || l_col_calc_tbl(i).date_decode_begin
707 || l_col_calc_tbl(i).column_name
708 || l_col_calc_tbl(i).date_decode_end
709 || l_col_calc_tbl(i).calc_end
710 || ' '
711 || l_col_calc_tbl(i).alias_begin
712 || l_col_calc_tbl(i).alias_end
713 || fnd_global.newline;
714 END LOOP;
715
716
717 -- 0 (0 0 0) = neither XTD or XED
718 -- 1 (0 0 1) = XED
719 -- 2 (0 1 0) = XTD
720 -- 3 (0 1 1) = both XTD and XED
721 -- 4 (1 0 0) = YTD
722 -- 5 (1 0 1) = YTD and XED
723 -- 6 (1 1 0) = YTD and XTD)
724 -- 7 (1 1 1) = YTD and XTD and XED
725
726 l_inlist :=
727 '('
728 || CASE
729 WHEN -- if one or more columns had XED
730 BITAND (l_inlist_bmap
731 , poa_dbi_template_pkg.g_inlist_xed) = poa_dbi_template_pkg.g_inlist_xed
732 THEN -- alway append current
733 poa_dbi_template_pkg.g_c_period_end_date
734 || CASE -- append prev date if needed
735 WHEN l_compute_prior = 'Y'
736 THEN ',' || poa_dbi_template_pkg.g_p_period_end_date
737 END
738 END
739 || CASE -- when XED and (XTD or YTD) exist
740 WHEN l_inlist_bmap IN (3, 5, 7)
741 THEN ','
742 END
743 || CASE -- if one or more columns had XTD
744 WHEN ( BITAND (l_inlist_bmap
745 , poa_dbi_template_pkg.g_inlist_xtd) = poa_dbi_template_pkg.g_inlist_xtd
746 OR BITAND (l_inlist_bmap
747 , poa_dbi_template_pkg.g_inlist_ytd) = poa_dbi_template_pkg.g_inlist_ytd)
748 THEN -- alway append current
749 poa_dbi_template_pkg.g_c_as_of_date
750 || CASE -- append prev date if needed
751 WHEN l_compute_prior = 'Y'
752 THEN ',' || poa_dbi_template_pkg.g_p_as_of_date
753 END
754 END
755 || case
756 when bitand(l_inlist_bmap, poa_dbi_template_pkg.g_inlist_rlx) = poa_dbi_template_pkg.g_inlist_rlx then
757 poa_dbi_template_pkg.g_c_period_end_date
758 || case -- append prev date if needed
759 when l_compute_prior = 'Y' then
760 ',' || poa_dbi_template_pkg.g_p_period_end_date
761 end
762 end
763 || case
764 when bitand(l_inlist_bmap, poa_dbi_template_pkg.g_inlist_bal) = poa_dbi_template_pkg.g_inlist_bal then
765 poa_dbi_template_pkg.g_c_as_of_date_balance
766 || case -- append prev date if needed
767 when l_compute_prior = 'Y' then
768 ',' || poa_dbi_template_pkg.g_p_as_of_date_balance
769 end
770 || case
771 when l_compute_opening_bal = 'Y' then
772 ',' || poa_dbi_template_pkg.g_c_as_of_date_o_balance
773 end
774 end
775 || CASE
776 WHEN l_compute_prev_prev = 'Y'
777 THEN ', &PREV_PREV_DATE'
778 END
779 || ')';
780
781
782 IF p_filter_where is not null
783 THEN
784 l_filter_where := ' where ' || p_filter_where;
785 END IF;
786
787 -- Determine how many closing parens we need
788 l_paren_str := get_paren_str(p_paren_count,
789 l_filter_where);
790
791 if( bitand(l_inlist_bmap, poa_dbi_template_pkg.g_inlist_rlx) = poa_dbi_template_pkg.g_inlist_rlx) then
792 l_join_tables := ', fii_time_structures cal '|| l_in_join_tables;
793 l_full_where_clause := ' where fact.time_id = cal.time_id '
794 || 'and fact.period_type_id = cal.period_type_id '
795 || fnd_global.newline
796 || p_where_clause
797 || fnd_global.newline
798 || 'and cal.report_date in '
799 || l_inlist
800 -- &RLX_NESTED_PATTERN should be replaced with some
801 -- &BIS bind substitution when available from fii/bis team.
802 || fnd_global.newline
803 || 'and bitand(cal.record_type_id, &RLX_NESTED_PATTERN) = '
804 || '&RLX_NESTED_PATTERN ';
805 elsif( bitand(l_inlist_bmap, poa_dbi_template_pkg.g_inlist_bal) = poa_dbi_template_pkg.g_inlist_bal) then
806 l_join_tables := '';
807 l_full_where_clause := ' where fact.report_date in '
808 || l_inlist
809 || p_where_clause;
810 elsif( l_inlist_bmap = 0) then --for status sqls with no as-of date or compare to
811 l_join_tables := l_in_join_tables;
812 if(p_where_clause is not null) then
813 l_full_where_clause := ' where ' || p_where_clause;
814 end if;
815 else
819 l_full_where_clause :=' where fact.time_id = cal.time_id '
816 l_join_tables := ', fii_time_rpt_struct_v cal'
817 || fnd_global.newline
818 || l_in_join_tables;
820 || p_where_clause
821 || fnd_global.newline
822 || ' and cal.report_date in '
823 || l_inlist
824 || fnd_global.newline
825 || ' and bitand(cal.record_type_id, '
826 || CASE -- if one or more columns = YTD then use nested pattern
827 WHEN BITAND (l_inlist_bmap, poa_dbi_template_pkg.g_inlist_ytd) = poa_dbi_template_pkg.g_inlist_ytd
828 THEN '&YTD_NESTED_PATTERN'
829 ELSE '&BIS_NESTED_PATTERN'
830 END
831 || ') = cal.record_type_id ';
832 end if;
833
834 IF (p_view_by <> 'ITEM+POA_ITEMS')
835 THEN
836 l_group_by := ' group by fact.base_uom, ' ;
837 l_add_sel_col := ' fact.base_uom, ' ;
838 ELSE
839 l_group_by := 'group by ' ;
840 l_add_sel_col := null ;
841 END IF ;
842 l_query := '(select '
843 || l_add_sel_col
844 || l_group_and_sel_clause
845 || l_col_names
846 || fnd_global.newline||' from '
847 || p_fact_name
848 || ' fact'
849 || l_join_tables
850 || l_full_where_clause
851 || ' and nvl(fact.base_uom ,''-1'') = ''' || p_uom_code || ''''
852 || l_grpid_clause
853 || fnd_global.newline
854 || l_group_by
855 || l_group_and_sel_clause
856 || l_paren_str;
857
858
859
860 IF(p_generate_viewby = 'Y')
861 THEN
862 l_viewby_rank_where :='';
863
864 if(p_use_windowing <> 'P') then
865 l_viewby_rank_where := l_viewby_rank_where ||
866 ',' || fnd_global.newline;
867 end if;
868
869 l_join_tbl := p_join_tables ;
870
871 IF (p_view_by <> 'ITEM+POA_ITEMS' )
872 THEN
873 l_join_rec.table_name := 'mtl_units_of_measure_vl';
874 l_join_rec.table_alias := 'v2';
875 l_join_rec.fact_column :='base_uom';
876 l_join_rec.column_name := 'unit_of_measure';
877 l_join_rec.dim_outer_join := 'Y';
878 l_join_tbl.extend;
879 l_join_tbl(l_join_tbl.count) := l_join_rec;
880 END IF ;
881
882 l_viewby_rank_where := l_viewby_rank_where ||
883 poa_dbi_template_pkg.get_viewby_rank_clause (
884 p_join_tables => l_join_tbl
885 , p_use_windowing => p_use_windowing);
886 END IF;
887
888 l_query := l_query || l_viewby_rank_where;
889
890 RETURN l_query;
891 END dtl_status_sql;
892
893
894 ----------------------------------------------------------------------------------
895
896 FUNCTION get_dtl_sel_clause(p_view_by_col in VARCHAR2)
897 RETURN VARCHAR2
898 IS
899 l_sel_clause varchar2(4000);
900 BEGIN
901
902 if(p_view_by_col = 'commodity_id') then
903 l_sel_clause := 'select decode(v.name,null,
904 fnd_message.get_string(''POA'', ''POA_DBI_APL_UNASSIGNED''), v.name) VIEWBY,
905 decode(v.commodity_id,null, -1, v.commodity_id) VIEWBYID,';
906 else
907 l_sel_clause := 'select v.value VIEWBY, v.id VIEWBYID, ';
908 end if;
909
910 l_sel_clause := l_sel_clause || ' v2.description POA_ATTRIBUTE10,
911 oset.POA_MEASURE12 POA_MEASURE12,
912 oset.POA_MEASURE13 POA_MEASURE13,
913 oset.POA_MEASURE1 POA_MEASURE1,
914 oset.POA_PERCENT1 POA_PERCENT1 ,
915 oset.POA_MEASURE2 POA_MEASURE2 ,
916 oset.POA_PERCENT2 POA_PERCENT2 ,
917 oset.POA_MEASURE3 POA_MEASURE3 ,
918 oset.POA_PERCENT3 POA_PERCENT3 ,
919 oset.POA_MEASURE4 POA_MEASURE4 ,
920 oset.POA_MEASURE5 POA_MEASURE5 ,
921 oset.POA_MEASURE6 POA_MEASURE6,
922 oset.POA_PERCENT4 POA_PERCENT4
923 from
924 (select (rank() over
925 (&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col || ')) - 1 rnk,'
926 || p_view_by_col || ', base_uom ,
927 POA_MEASURE12, POA_MEASURE13, POA_PERCENT1, POA_MEASURE1, POA_PERCENT2, POA_MEASURE2,
928 POA_MEASURE3, POA_PERCENT3 , POA_MEASURE4, POA_MEASURE5 , POA_MEASURE6,
929 POA_PERCENT4
930 from
931 ( select ' || p_view_by_col || ', base_uom ,
932 POA_MEASURE12, POA_MEASURE13, POA_PERCENT1, POA_MEASURE1, POA_PERCENT2, POA_MEASURE2,
933 POA_MEASURE3, POA_PERCENT3 , (POA_MEASURE2 - POA_MEASURE12*min_avg) POA_MEASURE4,
934 (POA_MEASURE3 - POA_MEASURE13*min_avg) POA_MEASURE5 ,
935 (POA_MEASURE3/decode(POA_MEASURE13,0,null,POA_MEASURE13)) POA_MEASURE6,
936 POA_PERCENT4
937 from
938 (select ' || p_view_by_col || ', base_uom ,
939 ' || p_view_by_col || ' VIEWBY,
940 decode(base_uom,null,to_number(null), nvl(c_nz_quantity,0)) POA_MEASURE12,
941 decode(base_uom,null,to_number(null),nvl(c_nz_quantity_total,0)) POA_MEASURE13,
942 min(nvl(c_purchase_amt,0)/decode(c_nz_quantity,0,null,c_nz_quantity)) over() min_avg ,
943 nvl(c_purchase_amt,0)/decode(c_nz_quantity,0,null,c_nz_quantity) POA_MEASURE1,
944 (((nvl(c_purchase_amt,0)/decode(c_nz_quantity,0,null,c_nz_quantity)) -
945 (nvl(p_purchase_amt,0)/decode(p_nz_quantity,0,null,p_nz_quantity)))/
946 (nvl(p_purchase_amt,0)/decode(p_nz_quantity,0,null,p_nz_quantity)))*100 POA_PERCENT1,
947 nvl(c_purchase_amt,0) POA_MEASURE2,
948 ((nvl(c_purchase_amt,0)- nvl(p_purchase_amt,0)) /
949 (decode(p_purchase_amt,0,null,p_purchase_amt)))*100 POA_PERCENT2,
950 nvl(c_purchase_amt_total,0) POA_MEASURE3,
951 ((nvl(c_purchase_amt_total,0)-nvl(p_purchase_amt_total,0))/
952 (decode(p_purchase_amt_total,0,null,p_purchase_amt_total))) * 100 POA_PERCENT3 ,
953 (((nvl(c_purchase_amt_total,0)/decode(c_nz_quantity_total,0,null,c_nz_quantity_total)) -
954 (nvl(p_purchase_amt_total,0)/decode(p_nz_quantity_total,0,null,p_nz_quantity_total)))/
955 (nvl(p_purchase_amt_total,0)/decode(p_nz_quantity_total,0,null,p_nz_quantity_total)))*100 POA_PERCENT4
956 ' ;
957
958 RETURN l_sel_clause ;
959 END get_dtl_sel_clause ;
960
961
962 FUNCTION get_group_and_sel_clause(
963 p_join_tables IN poa_dbi_util_pkg.poa_dbi_join_tbl
964 , p_use_alias IN VARCHAR2
965 ) RETURN VARCHAR2
966 IS
967 l_group_and_sel_clause VARCHAR2 (500);
968 l_alias VARCHAR2 (200);
969 BEGIN
970 l_alias := '';
971 if(p_use_alias = 'Y') then
972 if(p_join_tables(1).inner_alias is not null) then
973 l_alias := p_join_tables(1).inner_alias || '.';
974 else
975 l_alias := 'fact.';
976 end if;
977 end if;
978
979 l_group_and_sel_clause := ' ' || l_alias || p_join_tables (1).fact_column;
980
981
982 FOR i IN 2 .. p_join_tables.COUNT
983 LOOP
984
985 l_alias := '';
986 if(p_use_alias = 'Y') then
987 if(p_join_tables(i).inner_alias is not null) then
988 l_alias := p_join_tables(i).inner_alias || '.';
989 else
990 l_alias := 'fact.';
991 end if;
992 end if;
993
994 l_group_and_sel_clause := l_group_and_sel_clause
995 ||', ' || l_alias
996 || p_join_tables(i).fact_column;
997 END LOOP;
998
999 return l_group_and_sel_clause;
1000 END get_group_and_sel_clause;
1001
1002
1003
1004 FUNCTION get_paren_str(p_paren_count IN NUMBER,
1005 p_filter_where IN VARCHAR2) RETURN VARCHAR2
1006 IS
1007 l_paren_str VARCHAR2 (10000);
1008 BEGIN
1009 IF p_paren_count = 2
1010 THEN
1011 l_paren_str := ' ) oset05 ' || p_filter_where || ') oset ';
1012 ELSIF p_paren_count = 3
1013 THEN
1014 l_paren_str := ' ) ) ' || p_filter_where || ' ) oset ';
1015 ELSIF p_paren_count = 4
1016 THEN
1017 l_paren_str := ' ) ) ) ' || p_filter_where || ' ) oset ';
1018 ELSIF p_paren_count = 5
1019 THEN
1020 l_paren_str := ' ) oset05) oset10) oset15) oset20 '
1021 || p_filter_where || ')oset ';
1022 ELSIF p_paren_count = 6
1023 THEN
1024 l_paren_str := ' ) oset05) oset10) oset15) oset20) oset25 '
1025 || p_filter_where || ' )oset ';
1026 ELSIF p_paren_count = 7
1027 THEN
1028 l_paren_str := ' ) oset05) oset10) oset13) oset15) oset20) '
1029 || p_filter_where || ' )oset ';
1030 END IF;
1031
1032 return l_paren_str;
1033 END get_paren_str;
1034
1035 END poa_dbi_iap_pkg;