eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.POA_DBI_PR_PKG

Source


1 package body poa_dbi_pr_pkg
2 /* $Header: poadbiprb.pls 120.2 2006/08/11 07:48:58 sdiwakar noship $ */
3 as
4   /*forward declarations of local functions*/
5   function get_status_sel_clause(
6              p_view_by_dim in varchar2,
7              p_view_by_col in varchar2
8            ) return varchar2;
9 
10   function get_status_sum_sel_clause(
11              p_view_by_dim in varchar2,
12              p_view_by_col in varchar2
13            ) return varchar2;
14 
15   function get_amt_sel_clause(
16              p_view_by_dim in varchar2,
17              p_view_by_col in varchar2,
18              p_bucket_rec in BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE
19            )  return VARCHAR2;
20 
21   function get_age_sel_clause(
22              p_view_by_dim in varchar2,
23              p_view_by_col in varchar2,
24              p_bucket_rec in BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE
25            )  return VARCHAR2;
26 
27   function get_amt_trend_sel_clause return varchar2;
28   function get_age_trend_sel_clause return varchar2;
29   function get_au_trend_sel_clause return varchar2;
30   function get_status_filter_where(p_view_by in VARCHAR2) return varchar2;
31   function get_summary_filter_where(p_view_by in VARCHAR2) return varchar2;
32   function get_amt_filter_where(p_view_by in VARCHAR2) return varchar2;
33   function get_req_age_filter_where(p_view_by in VARCHAR2) return varchar2;
34   /*public procedure definitions*/
35 
36   procedure status_sql(p_param in bis_pmv_page_parameter_tbl
37                        ,x_custom_sql out nocopy varchar2
38                        ,x_custom_output out nocopy bis_query_attributes_tbl)
39   is
40     l_col_tbl             poa_dbi_util_pkg.poa_dbi_col_tbl;
41     l_join_tbl            poa_dbi_util_pkg.poa_dbi_join_tbl;
42     l_query               varchar2(10000);
43     l_view_by             varchar2(120);
44     l_view_by_col         varchar2(120);
45     l_as_of_date          date;
46     l_prev_as_of_date     date;
47     l_xtd                 varchar2(10);
48     l_comparison_type     varchar2(1);
49     l_nested_pattern      number;
50     l_cur_suffix          varchar2(2);
51     l_in_join_tbl         poa_dbi_util_pkg.poa_dbi_in_join_tbl;
52     l_join_rec            poa_dbi_util_pkg.poa_dbi_join_rec;
53     l_where_clause        varchar2(2000);
54     l_mv                  varchar2(30);
55     l_view_by_value       varchar2(30);
56     err_msg               varchar2(100);
57     err_cde               number;
58   begin
59     l_comparison_type := 'Y';
60     l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
61     l_col_tbl :=  poa_dbi_util_pkg.POA_DBI_COL_TBL();
62 
63     poa_dbi_sutil_pkg.process_parameters(p_param
64                                         ,l_view_by
65                                         ,l_view_by_col
66                                         ,l_view_by_value
67                                         ,l_comparison_type
68                                         ,l_xtd
69                                         ,l_as_of_date
70                                         ,l_prev_as_of_date
71                                         ,l_cur_suffix
72                                         ,l_nested_pattern
73                                         ,l_where_clause
74                                         ,l_mv
75                                         ,l_join_tbl
76                                         ,l_in_join_tbl
77 					,x_custom_output
78                                         ,p_trend => 'N'
79                                         ,p_func_area => 'PO'
80                                         ,p_version => '7.1'
81                                         ,p_role => 'VPP'
82                                         ,p_mv_set => 'REQMP');
83 
84     poa_dbi_util_pkg.add_column(l_col_tbl
85                     , 'processed_cnt_' || l_cur_suffix
86                     , 'proc_cnt'
87                     , p_grand_total => 'Y'
88                     , p_prior_code => poa_dbi_util_pkg.both_priors
89                     , p_to_date_type => 'RLX');
90 
91     poa_dbi_util_pkg.add_column(l_col_tbl
92                     , 'processed_man_cnt_' || l_cur_suffix
93                     , 'man_lines_cnt'
94                     , p_grand_total => 'Y'
95                     , p_prior_code => poa_dbi_util_pkg.both_priors
96                     , p_to_date_type => 'RLX');
97 
98     poa_dbi_util_pkg.add_column(l_col_tbl
99                     , 'processed_amt_'  || l_cur_suffix
100                     , 'proc_amt'
101                     , p_grand_total => 'Y'
102                     , p_prior_code => poa_dbi_util_pkg.both_priors
103                     , p_to_date_type => 'RLX');
104 
105     poa_dbi_util_pkg.add_column(l_col_tbl
106                     , 'days_to_process'
107                     , 'proc_days'
108                     , p_grand_total => 'Y'
109                     , p_prior_code => poa_dbi_util_pkg.both_priors
110                     , p_to_date_type => 'RLX');
111 
112  if(l_view_by = 'ITEM+POA_ITEMS') then
113     poa_dbi_util_pkg.add_column(l_col_tbl
114                                ,'processed_qty'
115                                ,'proc_qty'
116                    , p_grand_total => 'N'
117                   , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
118                   , p_to_date_type => 'RLX');
119 end if;
120 
121     l_query := get_status_sel_clause(l_view_by, l_view_by_col) || ' from ';
122     l_query := l_query ||
123                  poa_dbi_template_pkg.status_sql(
124                                         l_mv,
125                                         l_where_clause,
126                                         l_join_tbl,
127                                         p_use_windowing => 'Y',
128                                         p_col_name => l_col_tbl,
129                                         p_use_grpid => 'N',
130                                         p_filter_where => get_status_filter_where(l_view_by),
131                                         p_in_join_tables => l_in_join_tbl
132                                       );
133 
134     x_custom_sql := l_query;
135 
136   exception
137    when others then
138       err_msg := substr(sqlerrm,1,400);
139    end;
140 
141   function get_status_sel_clause(p_view_by_dim in varchar2
142                                 ,p_view_by_col in varchar2)
143   return varchar2 is
144     l_sel_clause varchar2(6000);
145   begin
146     l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
147                                                               ,'PO'
148                                                               ,'7.1');
149 
150     if(p_view_by_dim = 'ITEM+POA_ITEMS')
151     then
152       l_sel_clause := l_sel_clause || fnd_global.newline ||  '
153       v.description POA_ATTRIBUTE1,                --Description
154       v2.description POA_ATTRIBUTE2,              --UOM
155       oset.POA_MEASURE10 POA_MEASURE10,        --Unfulfilled Quantity'|| fnd_global.newline;
156 
157     else
158       l_sel_clause :=  l_sel_clause || fnd_global.newline ||  '
159     null POA_ATTRIBUTE1,        --Description
160     null POA_ATTRIBUTE2,        --UOM
161     null POA_MEASURE10,        --Quantity' || fnd_global.newline;
162     end if;
163 
164     l_sel_clause := l_sel_clause ||
165 '    oset.POA_MEASURE1  POA_MEASURE1,  -- Processed Lines
166     oset.POA_MEASURE11 POA_MEASURE11,  -- Processed Lines Prior
167     oset.POA_PERCENT1  POA_PERCENT1,  -- Change
168     oset.POA_MEASURE2  POA_MEASURE2,  -- Manual Lines
169     oset.POA_PERCENT2  POA_PERCENT2,  -- Manual Lines Rate
170     oset.POA_PERCENT3  POA_PERCENT3,  -- Change
171     oset.POA_MEASURE3  POA_MEASURE3,  -- Processed Amount
172     oset.POA_MEASURE12 POA_MEASURE12, -- Processed Amount Prior
173     oset.POA_PERCENT4  POA_PERCENT4,  -- Change
174     oset.POA_MEASURE4  POA_MEASURE4,  -- Average Age (Days)
175     oset.POA_MEASURE13 POA_MEASURE13, -- Average Age (Days) Prior
176     oset.POA_PERCENT5  POA_PERCENT5,  -- Change
177     oset.POA_MEASURE6  POA_MEASURE6,  -- Total Processed Lines
178     oset.POA_MEASURE14  POA_MEASURE14,  -- Total Processed Lines Prior
179     oset.POA_PERCENT6  POA_PERCENT6,  -- Total Change
180     oset.POA_MEASURE7  POA_MEASURE7,  -- Total Manual Lines
181     oset.POA_PERCENT7  POA_PERCENT7,  -- Total Manual Lines Rate
182     oset.POA_PERCENT8  POA_PERCENT8,  -- Total Change
183     oset.POA_MEASURE8  POA_MEASURE8,  -- Total Processed Amount
184     oset.POA_MEASURE15  POA_MEASURE15,  -- Total Processed Amount Prior
185     oset.POA_PERCENT9  POA_PERCENT9,  -- Total Change
186     oset.POA_MEASURE9  POA_MEASURE9,  -- Total Average Age (Days)
187     oset.POA_MEASURE16  POA_MEASURE16,  -- Total Average Age (Days) Prior
188     oset.POA_PERCENT10 POA_PERCENT10, -- Total Change
189     oset.POA_MEASURE2 POA_ATTRIBUTE5,  -- Manual (Graph 1)
190     oset.POA_ATTRIBUTE6 POA_ATTRIBUTE6
191     from
192     (select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
193 
194     if(p_view_by_dim = 'ITEM+POA_ITEMS')
195     then
196       l_sel_clause := l_sel_clause || ', base_uom';
197     end if;
198 
199     l_sel_clause := l_sel_clause || ')) - 1 rnk,' || p_view_by_col;
200     if(p_view_by_dim = 'ITEM+POA_ITEMS')
201     then
202       l_sel_clause := l_sel_clause || ',
203                          base_uom,
204                          POA_MEASURE10';
205     end if;
206 
207     l_sel_clause := l_sel_clause || ',
208     POA_MEASURE1,
209     POA_MEASURE11,
210     POA_PERCENT1,
211     POA_MEASURE2,
212     POA_PERCENT2,
213     POA_PERCENT3,
214     POA_MEASURE3,
215     POA_MEASURE12,
216     POA_PERCENT4,
217     POA_MEASURE4,
218     POA_MEASURE13,
219     POA_PERCENT5,
220     POA_MEASURE6,
221     POA_PERCENT6,
222     POA_MEASURE14,
223     POA_MEASURE7,
224     POA_PERCENT7,
225     POA_PERCENT8,
226     POA_MEASURE8,
227     POA_MEASURE15,
228     POA_PERCENT9,
229     POA_MEASURE9,
230     POA_MEASURE16,
231     POA_PERCENT10,
232     POA_ATTRIBUTE6
233     from   (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
234 
235     if(p_view_by_dim = 'ITEM+POA_ITEMS')
236     then
237       l_sel_clause := l_sel_clause || ' base_uom,
238                          decode(base_uom,null,to_number(null),nvl(c_proc_qty,0)) POA_MEASURE10, ';
239     end if;
240 
241     l_sel_clause := l_sel_clause || '
242          nvl(c_proc_cnt, 0) POA_MEASURE1,
243          nvl(p_proc_cnt, 0) POA_MEASURE11,
244     ' || poa_dbi_util_pkg.change_clause('c_proc_cnt','p_proc_cnt','NP') || ' POA_PERCENT1,
245         nvl(c_man_lines_cnt,0) POA_MEASURE2,
246      ' || poa_dbi_util_pkg.rate_clause('c_man_lines_cnt','c_proc_cnt', 'P') || ' POA_PERCENT2,
247   ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_man_lines_cnt','c_proc_cnt','P'),poa_dbi_util_pkg.rate_clause('p_man_lines_cnt','p_proc_cnt', 'P'),'P') || ' POA_PERCENT3,
248          nvl(c_proc_amt,0) POA_MEASURE3,
249          nvl(p_proc_amt,0) POA_MEASURE12,
250     ' || poa_dbi_util_pkg.change_clause('c_proc_amt','p_proc_amt', 'NP') || ' POA_PERCENT4,
251      ' || poa_dbi_util_pkg.rate_clause('c_proc_days','c_proc_cnt', 'NP') || ' POA_MEASURE4,
252      ' || poa_dbi_util_pkg.rate_clause('p_proc_days','p_proc_cnt', 'NP') || ' POA_MEASURE13,
253 ' || poa_dbi_util_pkg.change_clause(poa_dbi_util_pkg.rate_clause('c_proc_days','c_proc_cnt', 'NP'),poa_dbi_util_pkg.rate_clause('p_proc_days','p_proc_cnt', 'NP'),'P') || ' POA_PERCENT5,
254         nvl(c_proc_cnt_total,0) POA_MEASURE6,
255         nvl(p_proc_cnt_total,0) POA_MEASURE14,
256     ' || poa_dbi_util_pkg.change_clause('c_proc_cnt_total','p_proc_cnt_total', 'NP') || ' POA_PERCENT6,
257         nvl(c_man_lines_cnt_total,0) POA_MEASURE7,
258      ' || poa_dbi_util_pkg.rate_clause('c_man_lines_cnt_total','c_proc_cnt_total', 'P') || ' POA_PERCENT7,
259   ' || poa_dbi_util_pkg.change_clause(
260          poa_dbi_util_pkg.rate_clause('c_man_lines_cnt_total','c_proc_cnt_total', 'P'),
261          poa_dbi_util_pkg.rate_clause('p_man_lines_cnt_total','p_proc_cnt_total', 'P'),
262          'P'
263        ) || ' POA_PERCENT8,
264         nvl(c_proc_amt_total,0) POA_MEASURE8,
265         nvl(p_proc_amt_total,0) POA_MEASURE15,
266     ' || poa_dbi_util_pkg.change_clause('c_proc_amt_total','p_proc_amt_total', 'NP') || ' POA_PERCENT9,
267              ' || poa_dbi_util_pkg.rate_clause('c_proc_days_total','c_proc_cnt_total', 'NP') || ' POA_MEASURE9,
268              ' || poa_dbi_util_pkg.rate_clause('p_proc_days_total','p_proc_cnt_total', 'NP') ||' POA_MEASURE16,
269     ' || poa_dbi_util_pkg.change_clause(
270            poa_dbi_util_pkg.rate_clause('c_proc_days_total','c_proc_cnt_total', 'NP'),
271            poa_dbi_util_pkg.rate_clause('p_proc_days_total','p_proc_cnt_total', 'NP'),
272            'P'
273          ) || ' POA_PERCENT10,
274     nvl(c_proc_cnt,0) - nvl(c_man_lines_cnt,0) POA_ATTRIBUTE6
275 ';
276 
277     return l_sel_clause;
278   end;
279 
280  function get_status_filter_where(p_view_by in VARCHAR2) return varchar2
281   is
282     l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
283   begin
284     l_col_tbl := poa_dbi_sutil_pkg.poa_dbi_filter_tbl();
285     l_col_tbl.extend;
286     l_col_tbl(1) := 'POA_MEASURE1';
287     l_col_tbl.extend;
288     l_col_tbl(2) := 'POA_PERCENT1';
289     l_col_tbl.extend;
290     l_col_tbl(3) := 'POA_MEASURE2';
291     l_col_tbl.extend;
292     l_col_tbl(4) := 'POA_PERCENT2';
293     l_col_tbl.extend;
294     l_col_tbl(5) := 'POA_PERCENT3';
295     l_col_tbl.extend;
296     l_col_tbl(6) := 'POA_MEASURE3';
297     l_col_tbl.extend;
298     l_col_tbl(7) := 'POA_PERCENT4';
299     l_col_tbl.extend;
300     l_col_tbl(8) := 'POA_MEASURE4';
301     l_col_tbl.extend;
302     l_col_tbl(9) := 'POA_PERCENT5';
303 
304     if(p_view_by = 'ITEM+POA_ITEMS') then
305      l_col_tbl.extend;
306      l_col_tbl(10) := 'POA_MEASURE10';
307     end if;
308     return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
309   end;
310 
311 
312   procedure status_sum_sql(
313               p_param in bis_pmv_page_parameter_tbl,
314               x_custom_sql out nocopy varchar2,
315               x_custom_output out nocopy bis_query_attributes_tbl
316             )
317   is
318     l_col_tbl             poa_dbi_util_pkg.poa_dbi_col_tbl;
319     l_join_tbl            poa_dbi_util_pkg.poa_dbi_join_tbl;
320     l_query               varchar2(10000);
321     l_view_by             varchar2(120);
322     l_view_by_col         varchar2(120);
323     l_as_of_date          date;
324     l_prev_as_of_date     date;
325     l_xtd                 varchar2(10);
326     l_comparison_type     varchar2(1);
327     l_nested_pattern      number;
328     l_cur_suffix          varchar2(2);
329     l_in_join_tbl         poa_dbi_util_pkg.poa_dbi_in_join_tbl;
330     l_join_rec            poa_dbi_util_pkg.poa_dbi_join_rec;
331     l_where_clause        varchar2(2000);
332     l_mv                  varchar2(30);
333     l_view_by_value       varchar2(30);
334     err_msg               varchar2(100);
335     err_cde               number;
336   begin
337     l_comparison_type := 'Y';
338     l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
339     l_col_tbl :=  poa_dbi_util_pkg.POA_DBI_COL_TBL();
340 
341     poa_dbi_sutil_pkg.process_parameters(
342                         p_param,
343                         l_view_by,
344                         l_view_by_col,
345                         l_view_by_value,
346                         l_comparison_type,
347                         l_xtd,
348                         l_as_of_date,
349                         l_prev_as_of_date,
350                         l_cur_suffix,
351                         l_nested_pattern,
352                         l_where_clause,
353                         l_mv,
354                         l_join_tbl,
355                         l_in_join_tbl,
356 			x_custom_output,
357                         p_trend => 'N',
358                         p_func_area => 'PO',
359                         p_version => '7.1',
360                         p_role => 'VPP',
361                         p_mv_set => 'REQMP'
362                       );
363 
364     poa_dbi_util_pkg.add_column(
365                        l_col_tbl,
366                        'processed_cnt_' || l_cur_suffix,
367                        'proc_cnt',
368                        p_grand_total => 'Y',
369                        p_prior_code => poa_dbi_util_pkg.both_priors,
370                        p_to_date_type => 'RLX'
371                      );
372 
373     poa_dbi_util_pkg.add_column(
374                        l_col_tbl,
375                        'processed_man_cnt_' || l_cur_suffix,
376                        'man_lines_cnt',
377                        p_grand_total => 'Y',
378                        p_prior_code => poa_dbi_util_pkg.both_priors,
379                        p_to_date_type => 'RLX'
380                      );
381 
382     if(l_view_by = 'ITEM+POA_ITEMS')
383     then
384       poa_dbi_util_pkg.add_column(
385                          l_col_tbl,
386                          'processed_qty',
387                          'proc_qty',
388                          p_grand_total => 'N',
389                          p_prior_code => poa_dbi_util_pkg.no_priors,
390                          p_to_date_type => 'RLX'
391                        );
392     end if;
393 
394     l_query := get_status_sum_sel_clause(l_view_by, l_view_by_col) || ' from ';
395     l_query := l_query ||
396                  poa_dbi_template_pkg.status_sql(
397                                         l_mv,
398                                         l_where_clause,
399                                         l_join_tbl,
400                                         p_use_windowing => 'Y',
401                                         p_col_name => l_col_tbl,
402                                         p_use_grpid => 'N',
403                                         p_filter_where => get_summary_filter_where(l_view_by),
404                                         p_in_join_tables => l_in_join_tbl
405                                       );
406 
407     x_custom_sql := l_query;
408 
409   exception
410     when others then
411       err_msg := substr(sqlerrm,1,400);
412   end;
413 
414   function get_status_sum_sel_clause(
415              p_view_by_dim in varchar2,
416              p_view_by_col in varchar2
417            )
418   return varchar2 is
419     l_sel_clause varchar2(4000);
420   begin
421     l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(
422                                         p_view_by_dim,
423                                         'PO',
424                                         '7.1'
425                                       );
426 
427   if(p_view_by_dim = 'ITEM+POA_ITEMS')
428   then
429     l_sel_clause := l_sel_clause || fnd_global.newline ||
430       ' v.description POA_ATTRIBUTE1,            --Description' || fnd_global.newline ||
431       ' v2.description POA_ATTRIBUTE2,           --UOM' || fnd_global.newline ||
432       ' oset.POA_MEASURE10 POA_MEASURE10,        --Unfulfilled Quantity' || fnd_global.newline;
433   else
434     l_sel_clause :=  l_sel_clause || fnd_global.newline ||
435       '  null POA_ATTRIBUTE1,        --Description' || fnd_global.newline ||
436       '  null POA_ATTRIBUTE2,        --UOM' || fnd_global.newline ||
437       '  null POA_MEASURE10,        --Quantity' || fnd_global.newline;
438 
439   end if;
440 
441     l_sel_clause := l_sel_clause ||
442 '    oset.POA_MEASURE1  POA_MEASURE1,  -- Processed Lines
443     oset.POA_PERCENT1  POA_PERCENT1,  -- Change
444     oset.POA_MEASURE2  POA_MEASURE2,  -- Manual Lines
445     oset.POA_PERCENT2  POA_PERCENT2,  -- Manual Lines Rate
446     oset.POA_PERCENT3  POA_PERCENT3,  -- Change
447     oset.POA_MEASURE6  POA_MEASURE6,  -- Total Processed Lines
448     oset.POA_PERCENT6  POA_PERCENT6,  -- Total Change
449     oset.POA_MEASURE7  POA_MEASURE7,  -- Total Manual Lines
450     oset.POA_PERCENT7  POA_PERCENT7,  -- Total Manual Lines Rate
451     oset.POA_PERCENT8  POA_PERCENT8,  -- Total Change
452     oset.POA_MEASURE2 POA_ATTRIBUTE5,  -- Manual (Graph 1)
453     oset.POA_MEASURE4 POA_MEASURE4    -- Automated
454     from
455     (select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
456 
457   if(p_view_by_dim = 'ITEM+POA_ITEMS')
458   then
459     l_sel_clause := l_sel_clause || ', base_uom';
460   end if;
461 
462   l_sel_clause := l_sel_clause || ')) - 1 rnk,' || p_view_by_col;
463 
464   if(p_view_by_dim = 'ITEM+POA_ITEMS')
465   then
466     l_sel_clause := l_sel_clause || ',
467                        base_uom,
468                        POA_MEASURE10';
469   end if;
470 
471   l_sel_clause := l_sel_clause || ',
472     POA_MEASURE1,
473     POA_PERCENT1,
474     POA_MEASURE2,
475     POA_PERCENT2,
476     POA_PERCENT3,
477     POA_MEASURE6,
478     POA_PERCENT6,
479     POA_MEASURE7,
480     POA_PERCENT7,
481     POA_PERCENT8,
482     POA_MEASURE4
483     from   (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
484 
485   if(p_view_by_dim = 'ITEM+POA_ITEMS')
486   then
487     l_sel_clause := l_sel_clause || ' base_uom,
488                          decode(base_uom,null,to_number(null),nvl(c_proc_qty,0)) POA_MEASURE10, ';
489   end if;
490 
491   l_sel_clause := l_sel_clause || '
492       nvl(c_proc_cnt, 0) POA_MEASURE1,
493       ' || poa_dbi_util_pkg.change_clause('c_proc_cnt','p_proc_cnt','NP') || ' POA_PERCENT1,
494       nvl(c_man_lines_cnt,0) POA_MEASURE2,
495       ' || poa_dbi_util_pkg.rate_clause('c_man_lines_cnt','c_proc_cnt','P') || ' POA_PERCENT2,
496       ' || poa_dbi_util_pkg.change_clause(
497                           poa_dbi_util_pkg.rate_clause('c_man_lines_cnt','c_proc_cnt','P'),
498                           poa_dbi_util_pkg.rate_clause('p_man_lines_cnt','p_proc_cnt','P'),
499                           'P'
500                         ) || ' POA_PERCENT3,
501       nvl(c_proc_cnt_total,0) POA_MEASURE6,
502       ' || poa_dbi_util_pkg.change_clause('c_proc_cnt_total','p_proc_cnt_total','NP') || ' POA_PERCENT6,
503       nvl(c_man_lines_cnt_total,0) POA_MEASURE7,
504       ' || poa_dbi_util_pkg.rate_clause('c_man_lines_cnt_total','c_proc_cnt_total','P') || ' POA_PERCENT7,
505       ' || poa_dbi_util_pkg.change_clause(
506                           poa_dbi_util_pkg.rate_clause('c_man_lines_cnt_total','c_proc_cnt_total','P'),
507                           poa_dbi_util_pkg.rate_clause('p_man_lines_cnt_total','p_proc_cnt_total','P'),
508                           'P'
509                         ) || ' POA_PERCENT8,
510       nvl(c_proc_cnt,0) - nvl(c_man_lines_cnt,0) POA_MEASURE4
511 ';
512     return l_sel_clause;
513   end;
514 
515   function get_summary_filter_where(p_view_by in VARCHAR2) return varchar2
516   is
517     l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
518   begin
519     l_col_tbl := poa_dbi_sutil_pkg.poa_dbi_filter_tbl();
520     l_col_tbl.extend;
521     l_col_tbl(1) := 'POA_MEASURE1';
522     l_col_tbl.extend;
523     l_col_tbl(2) := 'POA_PERCENT1';
524     l_col_tbl.extend;
525     l_col_tbl(3) := 'POA_MEASURE2';
526     l_col_tbl.extend;
527     l_col_tbl(4) := 'POA_PERCENT2';
528     l_col_tbl.extend;
529     l_col_tbl(5) := 'POA_PERCENT3';
530 
531     if(p_view_by = 'ITEM+POA_ITEMS') then
532      l_col_tbl.extend;
533      l_col_tbl(6) := 'POA_MEASURE10';
534     end if;
535 
536     return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
537   end;
538 
539 
540   /* PROCESSED REQUISITIONS AMOUNT REPORT */
541 
542   procedure amt_sql(p_param in bis_pmv_page_parameter_tbl
543                        ,x_custom_sql out nocopy varchar2
544                        ,x_custom_output out nocopy bis_query_attributes_tbl)
545   is
546     l_col_tbl             poa_dbi_util_pkg.poa_dbi_col_tbl;
547     l_join_tbl            poa_dbi_util_pkg.poa_dbi_join_tbl;
548     l_query               varchar2(10000);
549     l_view_by             varchar2(120);
550     l_view_by_col         varchar2(120);
551     l_as_of_date          date;
552     l_prev_as_of_date     date;
553     l_xtd                 varchar2(10);
554     l_comparison_type     varchar2(1);
555     l_nested_pattern      number;
556     l_cur_suffix          varchar2(2);
557     l_in_join_tbl         poa_dbi_util_pkg.poa_dbi_in_join_tbl;
558     l_join_rec            poa_dbi_util_pkg.poa_dbi_join_rec;
559     l_where_clause        varchar2(2000);
560     l_mv                  varchar2(30);
561     l_view_by_value       varchar2(30);
562     l_bucket_rec          BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE;
563     err_msg               varchar2(100);
564     err_cde               number;
565   begin
566     l_comparison_type := 'Y';
567     l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
568     l_col_tbl :=  poa_dbi_util_pkg.POA_DBI_COL_TBL();
569 
570     poa_dbi_sutil_pkg.process_parameters(p_param
571                                         ,l_view_by
572                                         ,l_view_by_col
573                                         ,l_view_by_value
574                                         ,l_comparison_type
575                                         ,l_xtd
576                                         ,l_as_of_date
577                                         ,l_prev_as_of_date
578                                         ,l_cur_suffix
579                                         ,l_nested_pattern
580                                         ,l_where_clause
581                                         ,l_mv
582                                         ,l_join_tbl
583                                         ,l_in_join_tbl
584 					,x_custom_output
585                                         ,p_trend => 'N'
586                                         ,p_func_area => 'PO'
587                                         ,p_version => '7.1'
588                                         ,p_role => 'VPP'
589                                         ,p_mv_set => 'REQMP');
590 
591 
592     poa_dbi_util_pkg.add_column(l_col_tbl
593                     , 'processed_amt_' || l_cur_suffix
594                     , 'proc_amt'
595                     , p_grand_total => 'Y'
596                     , p_prior_code => poa_dbi_util_pkg.both_priors
597                     , p_to_date_type => 'RLX');
598 
599     poa_dbi_util_pkg.add_column(l_col_tbl
600                     , 'processed_man_amt_' || l_cur_suffix
601                     , 'man_amt'
602                     , p_grand_total => 'Y'
603                     , p_prior_code => poa_dbi_util_pkg.both_priors
604                     , p_to_date_type => 'RLX');
605 
606     poa_dbi_util_pkg.add_bucket_columns(
607                     p_short_name => 'POA_DBI_PR_BUCKET'
608                    ,p_col_tbl => l_col_tbl
609                    ,p_col_name => 'processed_amt_'|| l_cur_suffix || '_age'
610                    ,p_alias_name => 'proc_amt_age'
611                    ,x_bucket_rec => l_bucket_rec
612                    ,p_grand_total => 'Y'
613                    ,p_prior_code => poa_dbi_util_pkg.NO_PRIORS
614                    ,p_to_date_type => 'RLX');
615 
616     if(l_view_by = 'ITEM+POA_ITEMS')
617     then
618       poa_dbi_util_pkg.add_column(l_col_tbl
619                       ,'processed_qty'
620                       ,'proc_qty'
621                       , p_grand_total => 'N'
622                       , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
623                       , p_to_date_type => 'RLX');
624     end if;
625 
626     l_query := get_amt_sel_clause(l_view_by, l_view_by_col, l_bucket_rec) || ' from ';
627     l_query := l_query ||
628                  poa_dbi_template_pkg.status_sql(
629                    l_mv,
630                    l_where_clause,
631                    l_join_tbl,
632                    p_use_windowing => 'Y',
633                    p_col_name => l_col_tbl,
634                    p_use_grpid => 'N',
635                    p_filter_where => get_amt_filter_where(l_view_by),
636                    p_in_join_tables => l_in_join_tbl);
637 
638     x_custom_sql := l_query;
639 
640   exception
641    when others then
642       err_msg := substr(sqlerrm,1,400);
643    end;
644 
645 
646   function get_amt_sel_clause(
647              p_view_by_dim in varchar2,
648              p_view_by_col in varchar2,
649              p_bucket_rec in BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE
650            )
651   return varchar2 is
652     l_sel_clause varchar2(4000);
653   begin
654     l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
655                                                               ,'PO'
656                                                               ,'7.1');
657 
658   if(p_view_by_dim = 'ITEM+POA_ITEMS')
659   then
660     l_sel_clause := l_sel_clause
661         || fnd_global.newline
662         ||  '
663     v.description POA_ATTRIBUTE1,                --Description
664     v2.description POA_ATTRIBUTE2,              --UOM
665     oset.POA_MEASURE10 POA_MEASURE10,        --Quantity
666 '|| fnd_global.newline;
667 
668   else
669     l_sel_clause :=  l_sel_clause
670         || fnd_global.newline
671         ||  '
672     null POA_ATTRIBUTE1,        --Description
673     null POA_ATTRIBUTE2,        --UOM
674     null POA_MEASURE10,        --Quantity'
675 || fnd_global.newline;
676 
677   end if;
678   l_sel_clause := l_sel_clause || fnd_global.newline ||
679 '    oset.POA_MEASURE1  POA_MEASURE1,  -- Processed Amount
680     oset.POA_PERCENT1  POA_PERCENT1,  -- Change
681     oset.POA_MEASURE2  POA_MEASURE2,  -- Manual Amount
682     oset.POA_PERCENT2  POA_PERCENT2,  -- Manual Amount Rate
683     oset.POA_MEASURE3  POA_MEASURE3   -- Change
684         '|| poa_dbi_util_pkg.get_bucket_outer_query(
685                p_bucket_rec
686              , p_col_name => 'oset.POA_MEASURE4'
687              , p_alias_name => 'POA_MEASURE4'
688              , p_prefix => ''
689              , p_suffix => ''
690              , p_total_flag => 'N') ||',
691     oset.POA_MEASURE5  POA_MEASURE5,  -- Total Processed Amount
692     oset.POA_PERCENT3  POA_PERCENT3,  -- Total Change
693     oset.POA_MEASURE6  POA_MEASURE6,  -- Total Manual Amount
694     oset.POA_PERCENT4  POA_PERCENT4,  -- Total Manual Amount Rate
695     oset.POA_MEASURE7  POA_MEASURE7   -- Total Change
696         '|| poa_dbi_util_pkg.get_bucket_outer_query(
697                p_bucket_rec
698              , p_col_name => 'oset.POA_MEASURE8'
699              , p_alias_name => 'POA_MEASURE8'
700              , p_prefix => ''
701              , p_suffix => ''
702              , p_total_flag => 'N') ||'
703         '|| poa_dbi_util_pkg.get_bucket_drill_url(
704                                p_bucket_rec,
705                                'POA_ATTRIBUTE6',
706                                '''pFunctionName=POA_DBI_PR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=1&POA_BUCKET+REQUISITION_AGING=',
707                                '''',
708                                p_add_bucket_num => 'Y'
709                              ) || '
710     from
711     (select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
712  if(p_view_by_dim = 'ITEM+POA_ITEMS') then
713     l_sel_clause := l_sel_clause || ', base_uom';
714  end if;
715 
716 l_sel_clause := l_sel_clause || ')) - 1 rnk,' || p_view_by_col;
717   if(p_view_by_dim = 'ITEM+POA_ITEMS') then
718     l_sel_clause := l_sel_clause || ',
719                        base_uom,
720                        POA_MEASURE10';
721   end if;
722 
723     l_sel_clause := l_sel_clause || ',
724     POA_MEASURE1,
725     POA_PERCENT1,
726     POA_MEASURE2,
727     POA_PERCENT2,
728     POA_MEASURE3
729     '|| poa_dbi_util_pkg.get_bucket_outer_query(
730         p_bucket_rec
731       , p_col_name => 'POA_MEASURE4'
732       , p_alias_name => 'POA_MEASURE4'
733       , p_prefix => ''
734       , p_suffix => ''
735       , p_total_flag => 'N') ||',
736     POA_MEASURE5,
737     POA_PERCENT3,
738     POA_MEASURE6,
739     POA_PERCENT4,
740     POA_MEASURE7
741     '|| poa_dbi_util_pkg.get_bucket_outer_query(
742           p_bucket_rec
743        , p_col_name => 'POA_MEASURE8'
744         , p_alias_name => 'POA_MEASURE8'
745         , p_prefix => ''
746         , p_suffix => ''
747         , p_total_flag => 'N') ||'
748      from   (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
749    if(p_view_by_dim = 'ITEM+POA_ITEMS') then
750     l_sel_clause := l_sel_clause || ' base_uom,
751                          decode(base_uom,null,to_number(null),nvl(c_proc_qty,0)) POA_MEASURE10, ';
752 
753    end if;
754     l_sel_clause := l_sel_clause || '
755         nvl(c_proc_amt,0) POA_MEASURE1,
756         ' || poa_dbi_util_pkg.change_clause('c_proc_amt','p_proc_amt', 'NP') || ' POA_PERCENT1,
757         nvl(c_man_amt,0) POA_MEASURE2,
758      ' || poa_dbi_util_pkg.rate_clause('c_man_amt','c_proc_amt', 'P') || ' POA_PERCENT2,
759      ' || poa_dbi_util_pkg.change_clause(
760             poa_dbi_util_pkg.rate_clause('c_man_amt','c_proc_amt', 'P'),
761             poa_dbi_util_pkg.rate_clause('p_man_amt','p_proc_amt', 'P'),
762             'P') || ' POA_MEASURE3
763      '|| poa_dbi_util_pkg.get_bucket_outer_query(
764            p_bucket_rec
765          , p_col_name => 'c_proc_amt_age'
766          , p_alias_name => 'POA_MEASURE4'
767          , p_prefix => 'nvl('
768          , p_suffix => ',0)'
769          , p_total_flag => 'N') ||',
770      nvl(c_proc_amt_total,0) POA_MEASURE5,
771      ' || poa_dbi_util_pkg.change_clause('c_proc_amt_total','p_proc_amt_total', 'NP') || ' POA_PERCENT3,
772      nvl(c_man_amt_total,0) POA_MEASURE6,
773      ' || poa_dbi_util_pkg.rate_clause('c_man_amt_total','c_proc_amt_total','P') || ' POA_PERCENT4,
774      ' || poa_dbi_util_pkg.change_clause(
775             poa_dbi_util_pkg.rate_clause('c_man_amt_total','c_proc_amt_total', 'P'),
776             poa_dbi_util_pkg.rate_clause('p_man_amt_total','p_proc_amt_total', 'P'),
777             'P') || ' POA_MEASURE7
778      '|| poa_dbi_util_pkg.get_bucket_outer_query(
779            p_bucket_rec
780          , p_col_name => 'c_proc_amt_age'
781          , p_alias_name => 'POA_MEASURE8'
782          , p_prefix => 'nvl('
783          , p_suffix => ',0)'
784          , p_total_flag => 'Y') ||'
785 ';
786     return l_sel_clause;
787   end;
788 
789 
790   function get_amt_filter_where(p_view_by in VARCHAR2) return varchar2
791   is
792     l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
793   begin
794     l_col_tbl := poa_dbi_sutil_pkg.poa_dbi_filter_tbl();
795     l_col_tbl.extend;
796     l_col_tbl(1) := 'POA_MEASURE1';
797     l_col_tbl.extend;
798     l_col_tbl(2) := 'POA_PERCENT1';
799     l_col_tbl.extend;
800     l_col_tbl(3) := 'POA_MEASURE2';
801     l_col_tbl.extend;
802     l_col_tbl(4) := 'POA_PERCENT2';
803     l_col_tbl.extend;
804     l_col_tbl(5) := 'POA_MEASURE3';
805     if(p_view_by = 'ITEM+POA_ITEMS') then
806        l_col_tbl.extend;
807        l_col_tbl(6) := 'POA_MEASURE10';
808     end if;
809     return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
810   end;
811 
812 /* PROCESSED REQUISITIONS AGING REPORT */
813 
814   procedure age_sql(p_param in bis_pmv_page_parameter_tbl
815                        ,x_custom_sql out nocopy varchar2
816                        ,x_custom_output out nocopy bis_query_attributes_tbl)
817   is
818     l_col_tbl             poa_dbi_util_pkg.poa_dbi_col_tbl;
819     l_join_tbl            poa_dbi_util_pkg.poa_dbi_join_tbl;
820     l_query               varchar2(10000);
821     l_view_by             varchar2(120);
822     l_view_by_col         varchar2(120);
823     l_as_of_date          date;
824     l_prev_as_of_date     date;
825     l_xtd                 varchar2(10);
826     l_comparison_type     varchar2(1);
827     l_nested_pattern      number;
828     l_cur_suffix          varchar2(2);
829     l_in_join_tbl         poa_dbi_util_pkg.poa_dbi_in_join_tbl;
830     l_join_rec            poa_dbi_util_pkg.poa_dbi_join_rec;
831     l_where_clause        varchar2(2000);
832     l_mv                  varchar2(30);
833     l_view_by_value       varchar2(30);
834     l_bucket_rec          BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE;
835     err_msg               varchar2(100);
836     err_cde               number;
837   begin
838     l_comparison_type := 'Y';
839     l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
840     l_col_tbl :=  poa_dbi_util_pkg.POA_DBI_COL_TBL();
841 
842     poa_dbi_sutil_pkg.process_parameters(p_param
843                                         ,l_view_by
844                                         ,l_view_by_col
845                                         ,l_view_by_value
846                                         ,l_comparison_type
847                                         ,l_xtd
848                                         ,l_as_of_date
852                                         ,l_where_clause
849                                         ,l_prev_as_of_date
850                                         ,l_cur_suffix
851                                         ,l_nested_pattern
853                                         ,l_mv
854                                         ,l_join_tbl
855                                         ,l_in_join_tbl
856 					,x_custom_output
857                                         ,p_trend => 'N'
858                                         ,p_func_area => 'PO'
859                                         ,p_version => '7.1'
860                                         ,p_role => 'VPP'
861                                         ,p_mv_set => 'REQMP');
862 
863 
864     poa_dbi_util_pkg.add_column(l_col_tbl
865                     , 'days_to_process'
866                     , 'proc_days'
867                     , p_grand_total => 'Y'
868                     , p_prior_code => poa_dbi_util_pkg.both_priors
869                     , p_to_date_type => 'RLX');
870 
871 
872     poa_dbi_util_pkg.add_column(l_col_tbl
873                     , 'processed_cnt_' || l_cur_suffix
874                     , 'proc_cnt'
875                     , p_grand_total => 'Y'
876                     , p_prior_code => poa_dbi_util_pkg.both_priors
877                     , p_to_date_type => 'RLX');
878 
879     poa_dbi_util_pkg.add_column(l_col_tbl
880                     , 'processed_man_cnt_' || l_cur_suffix
881                     , 'man_lines_cnt'
882                     , p_grand_total => 'Y'
883                     , p_prior_code => poa_dbi_util_pkg.both_priors
884                     , p_to_date_type => 'RLX');
885 
886  poa_dbi_util_pkg.add_bucket_columns(
887                     p_short_name => 'POA_DBI_PR_BUCKET'
888                    ,p_col_tbl => l_col_tbl
889                    ,p_col_name => 'processed_cnt_'||l_cur_suffix||'_age'
890                    ,p_alias_name => 'proc_cnt_age'
891                    ,x_bucket_rec => l_bucket_rec
892                    ,p_grand_total => 'Y'
893                    ,p_prior_code => poa_dbi_util_pkg.NO_PRIORS
894                    ,p_to_date_type => 'RLX');
895 
896  if(l_view_by = 'ITEM+POA_ITEMS') then
897     poa_dbi_util_pkg.add_column(l_col_tbl
898                                ,'processed_qty'
899                                ,'proc_qty'
900                    , p_grand_total => 'N'
901                   , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
902                   , p_to_date_type => 'RLX');
903 end if;
904 
905     l_query := get_age_sel_clause(l_view_by, l_view_by_col, l_bucket_rec) || ' from ';
906     l_query := l_query ||
907                  poa_dbi_template_pkg.status_sql(
908                                                      l_mv,
909                                                      l_where_clause,
910                                                      l_join_tbl,
911                                                      p_use_windowing => 'Y',
912                                                      p_col_name => l_col_tbl,
913                                                      p_use_grpid => 'N',
914                                                      p_filter_where => get_req_age_filter_where(l_view_by),
915                                                      p_in_join_tables => l_in_join_tbl);
916 
917     x_custom_sql := l_query;
918 
919   exception
920    when others then
921       err_msg := substr(sqlerrm,1,400);
922    end;
923 
924 
925   function get_age_sel_clause(p_view_by_dim in varchar2
926                          ,p_view_by_col in varchar2
927              , p_bucket_rec in BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE)
928   return varchar2 is
929     l_sel_clause varchar2(6000);
930   begin
931     l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
932                                                               ,'PO'
933                                                               ,'7.1');
934 
935   if(p_view_by_dim = 'ITEM+POA_ITEMS') then
936      l_sel_clause := l_sel_clause
937         || fnd_global.newline
938         ||  '
939     v.description POA_ATTRIBUTE1,                --Description
940         v2.description POA_ATTRIBUTE2,              --UOM
941         oset.POA_MEASURE10 POA_MEASURE10,        --Quantity
942 
943 '|| fnd_global.newline;
944 
945   else
946     l_sel_clause :=  l_sel_clause
947         || fnd_global.newline
948         ||  '
949     null POA_ATTRIBUTE1,        --Description
950      null POA_ATTRIBUTE2,        --UOM
951     null POA_MEASURE10,        --Quantity'
952 
953 
954 || fnd_global.newline;
955 
956   end if;
957     l_sel_clause := l_sel_clause || fnd_global.newline ||
958 '    oset.POA_MEASURE1  POA_MEASURE1,  -- Average Age (Days)
959     oset.POA_MEASURE2  POA_MEASURE2,  -- Change
960     oset.POA_MEASURE3  POA_MEASURE3,  -- Processed Lines
961     oset.POA_PERCENT1  POA_PERCENT1,  -- Change
962     oset.POA_MEASURE4  POA_MEASURE4,  -- Manual Lines
963     oset.POA_PERCENT2  POA_PERCENT2,  -- Manual Lines Rate
964     oset.POA_MEASURE5  POA_MEASURE5   -- Change
965         '|| poa_dbi_util_pkg.get_bucket_outer_query(
966                p_bucket_rec
967              , p_col_name => 'oset.POA_MEASURE6'
968              , p_alias_name => 'POA_MEASURE6'
969              , p_prefix => ''
970              , p_suffix => ''
971              , p_total_flag => 'N') ||',
972     oset.POA_MEASURE7  POA_MEASURE7,  -- Total Average Age (Days)
973     oset.POA_MEASURE8  POA_MEASURE8,  -- Total Change
974     oset.POA_MEASURE9  POA_MEASURE9,  -- Total Processed Lines
975     oset.POA_PERCENT3  POA_PERCENT3,  -- Total Change
976     oset.POA_MEASURE11  POA_MEASURE11, -- Total Manual Lines
977     oset.POA_PERCENT4  POA_PERCENT4,  -- Total Manual Lines Rate
981              , p_col_name => 'oset.POA_MEASURE13'
978     oset.POA_MEASURE12  POA_MEASURE12   -- Total Change
979         '|| poa_dbi_util_pkg.get_bucket_outer_query(
980                p_bucket_rec
982              , p_alias_name => 'POA_MEASURE13'
983              , p_prefix => ''
984              , p_suffix => ''
985              , p_total_flag => 'N') ||'
986         '|| poa_dbi_util_pkg.get_bucket_drill_url(
987                                p_bucket_rec,
988                                'POA_ATTRIBUTE6',
989                                '''pFunctionName=POA_DBI_PR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=1&POA_BUCKET+REQUISITION_AGING=',
990                                '''',
991                                p_add_bucket_num => 'Y'
992                              ) || '
993     from
994     (select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
995  if(p_view_by_dim = 'ITEM+POA_ITEMS') then
996     l_sel_clause := l_sel_clause || ', base_uom';
997  end if;
998 
999 l_sel_clause := l_sel_clause || ')) - 1 rnk,' || p_view_by_col;
1000   if(p_view_by_dim = 'ITEM+POA_ITEMS') then
1001     l_sel_clause := l_sel_clause || ',
1002                        base_uom,
1003                        POA_MEASURE10';
1004   end if;
1005 
1006     l_sel_clause := l_sel_clause || ',
1007     POA_MEASURE1,
1008     POA_MEASURE2,
1009     POA_MEASURE3,
1010     POA_PERCENT1,
1011     POA_MEASURE4,
1012     POA_PERCENT2,
1013     POA_MEASURE5
1014     '|| poa_dbi_util_pkg.get_bucket_outer_query(
1015         p_bucket_rec
1016       , p_col_name => 'POA_MEASURE6'
1017       , p_alias_name => 'POA_MEASURE6'
1018       , p_prefix => ''
1019       , p_suffix => ''
1020       , p_total_flag => 'N') ||',
1021     POA_MEASURE7,
1022     POA_MEASURE8,
1023     POA_MEASURE9,
1024     POA_PERCENT3,
1025     POA_MEASURE11,
1026     POA_PERCENT4,
1027     POA_MEASURE12
1028     '|| poa_dbi_util_pkg.get_bucket_outer_query(
1029           p_bucket_rec
1030        , p_col_name => 'POA_MEASURE13'
1031         , p_alias_name => 'POA_MEASURE13'
1032         , p_prefix => ''
1033         , p_suffix => ''
1034         , p_total_flag => 'N') ||'
1035      from   (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
1036    if(p_view_by_dim = 'ITEM+POA_ITEMS') then
1037     l_sel_clause := l_sel_clause || ' base_uom,
1038                          decode(base_uom,null,to_number(null),nvl(c_proc_qty,0)) POA_MEASURE10, ';
1039 
1040    end if;
1041     l_sel_clause := l_sel_clause || '
1042      ' || poa_dbi_util_pkg.rate_clause('c_proc_days','c_proc_cnt', 'NP') || ' POA_MEASURE1,
1043      ' || poa_dbi_util_pkg.change_clause(
1044             poa_dbi_util_pkg.rate_clause('c_proc_days','c_proc_cnt', 'NP'),
1045             poa_dbi_util_pkg.rate_clause('p_proc_days','p_proc_cnt', 'NP'),
1046             'P') || ' POA_MEASURE2,
1047          nvl(c_proc_cnt, 0) POA_MEASURE3,
1048     ' || poa_dbi_util_pkg.change_clause('c_proc_cnt','p_proc_cnt', 'NP') || ' POA_PERCENT1,
1049         nvl(c_man_lines_cnt,0) POA_MEASURE4,
1050      ' || poa_dbi_util_pkg.rate_clause('c_man_lines_cnt','c_proc_cnt', 'P') || ' POA_PERCENT2,
1051      ' || poa_dbi_util_pkg.change_clause(
1052             poa_dbi_util_pkg.rate_clause('c_man_lines_cnt','c_proc_cnt', 'P'),
1053             poa_dbi_util_pkg.rate_clause('p_man_lines_cnt','p_proc_cnt', 'P'),
1054             'P') || ' POA_MEASURE5
1055      '|| poa_dbi_util_pkg.get_bucket_outer_query(
1056            p_bucket_rec
1057          , p_col_name => 'c_proc_cnt_age'
1058          , p_alias_name => 'POA_MEASURE6'
1059          , p_prefix => 'nvl('
1060          , p_suffix => ',0)'
1061          , p_total_flag => 'N') ||',
1062      ' || poa_dbi_util_pkg.rate_clause('c_proc_days_total','c_proc_cnt_total', 'NP') || ' POA_MEASURE7,
1063      ' || poa_dbi_util_pkg.change_clause(
1064             poa_dbi_util_pkg.rate_clause('c_proc_days_total','c_proc_cnt_total', 'NP'),
1065             poa_dbi_util_pkg.rate_clause('p_proc_days_total','p_proc_cnt_total', 'NP'),
1066             'P') || ' POA_MEASURE8,
1067          nvl(c_proc_cnt_total, 0) POA_MEASURE9,
1068     ' || poa_dbi_util_pkg.change_clause('c_proc_cnt_total','p_proc_cnt_total', 'NP') || ' POA_PERCENT3,
1069         nvl(c_man_lines_cnt_total,0) POA_MEASURE11,
1070      ' || poa_dbi_util_pkg.rate_clause('c_man_lines_cnt_total','c_proc_cnt_total', 'P') || ' POA_PERCENT4,
1071      ' || poa_dbi_util_pkg.change_clause(
1072             poa_dbi_util_pkg.rate_clause('c_man_lines_cnt_total','c_proc_cnt_total', 'P'),
1073             poa_dbi_util_pkg.rate_clause('p_man_lines_cnt_total','p_proc_cnt_total', 'P'),
1074             'P') || ' POA_MEASURE12
1075      '|| poa_dbi_util_pkg.get_bucket_outer_query(
1076              p_bucket_rec
1077            , p_col_name => 'c_proc_cnt_age'
1078            , p_alias_name => 'POA_MEASURE13'
1079            , p_prefix => 'nvl('
1080            , p_suffix => ',0)'
1081            , p_total_flag => 'Y') ||'
1082 ';
1083     return l_sel_clause;
1084   end;
1085 
1086 
1087   function get_req_age_filter_where(p_view_by in VARCHAR2) return varchar2
1088   is
1089     l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
1090   begin
1091     l_col_tbl := poa_dbi_sutil_pkg.poa_dbi_filter_tbl();
1092     l_col_tbl.extend;
1093     l_col_tbl(1) := 'POA_MEASURE1';
1094     l_col_tbl.extend;
1095     l_col_tbl(2) := 'POA_MEASURE2';
1096     l_col_tbl.extend;
1097     l_col_tbl(3) := 'POA_MEASURE3';
1098     l_col_tbl.extend;
1099     l_col_tbl(4) := 'POA_PERCENT1';
1100     l_col_tbl.extend;
1101     l_col_tbl(5) := 'POA_MEASURE4';
1102     l_col_tbl.extend;
1103     l_col_tbl(6) := 'POA_PERCENT2';
1104     l_col_tbl.extend;
1105     l_col_tbl(7) := 'POA_MEASURE5';
1106 
1107     if(p_view_by = 'ITEM+POA_ITEMS') then
1108      l_col_tbl.extend;
1109      l_col_tbl(8) := 'POA_MEASURE10';
1113   end;
1110     end if;
1111 
1112     return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
1114 
1115 
1116 
1117 
1118 
1119 /* PROCESSED LINES AMOUNT TREND REPORT */
1120 
1121   procedure amt_trend_sql(p_param in bis_pmv_page_parameter_tbl
1122                        ,x_custom_sql out nocopy varchar2
1123                        ,x_custom_output out nocopy bis_query_attributes_tbl)
1124   is
1125     l_query               varchar2(10000);
1126     l_view_by             varchar2(120);
1127     l_view_by_col_name    varchar2(120);
1128     l_as_of_date          date;
1129     l_prev_as_of_date     date;
1130     l_org                 varchar2(100);
1131     l_category            varchar2(2000);
1132     l_commodity           varchar2(2000);
1133     l_commodity_where     varchar2(2000);
1134     l_item                varchar2(2000);
1135     l_buyer               varchar2(2000);
1136     l_mv                  VARCHAR2(90);
1137     l_supplier            varchar2(2000);
1138     l_supplier_site       VARCHAR2(2000);
1139     l_xtd                 varchar2(10);
1140     l_comparison_type     varchar2(1);
1141     l_nested_pattern      number;
1142     l_dim_bmap            number;
1143     l_org_where           varchar2(240);
1144     l_category_where      varchar2(120);
1145     l_item_where          varchar2(120);
1146     l_buyer_where         varchar2(1000);
1147     l_supplier_where      varchar2(120);
1148     l_supplier_site_where varchar2(120);
1149     l_cur_suffix          varchar2(2);
1150     l_url                 varchar2(300);
1151     l_impact_amount       varchar2(15);
1152     l_leakage_amount      varchar2(25);
1153     l_purchase_amount     varchar2(25);
1154     l_custom_sql          varchar2(9000);
1155     l_view_by_value       varchar2(30);
1156     l_dim_in_tbl          poa_dbi_util_pkg.POA_DBI_DIM_TBL;
1157     l_dim_out_tbl         poa_dbi_util_pkg.POA_DBI_DIM_TBL;
1158     l_col_rec             poa_dbi_util_pkg.POA_DBI_COL_REC;
1159     l_col_tbl             poa_dbi_util_pkg.POA_DBI_COL_TBL;
1160     l_total_col_tbl       poa_dbi_util_pkg.POA_DBI_COL_TBL;
1161     l_join_tbl            poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1162     l_in_join_tbl         poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1163     l_join_rec            poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1164     l_where_clause        VARCHAR2(2000);
1165     ERR_MSG               VARCHAR2(100);
1166     ERR_CDE               NUMBER;
1167   begin
1168     l_comparison_type := 'Y';
1169     l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1170     l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1171 
1172     poa_dbi_sutil_pkg.process_parameters(p_param,
1173                                          l_view_by,
1174                                          l_view_by_col_name,
1175                                          l_view_by_value,
1176                                          l_comparison_type,
1177                                          l_xtd,
1178                                          l_as_of_date,
1179                                          l_prev_as_of_date,
1180                                          l_cur_suffix,
1181                                          l_nested_pattern,
1182                                          l_where_clause,
1183                                          l_mv,
1184                                          l_join_tbl,
1185                                          l_in_join_tbl,
1186 					 x_custom_output,
1187                                          p_trend => 'Y',
1188                                          p_func_area => 'PO',
1189                                          p_version => '7.1',
1190                                          p_role => 'VPP',
1191                                          p_mv_set => 'REQMP');
1192 
1193 
1194     poa_dbi_util_pkg.add_column(l_col_tbl
1195                     , 'processed_amt_'  || l_cur_suffix
1196                     , 'proc_amt'
1197                     , p_grand_total => 'N'
1198                     , p_prior_code => poa_dbi_util_pkg.both_priors
1199                     , p_to_date_type => 'RLX');
1200 
1201     poa_dbi_util_pkg.add_column(l_col_tbl
1202                     , 'processed_man_amt_'  || l_cur_suffix
1203                     , 'man_amt'
1204                     , p_grand_total => 'N'
1205                     , p_prior_code => poa_dbi_util_pkg.both_priors
1206                     , p_to_date_type => 'RLX');
1207 
1208     l_query := get_amt_trend_sel_clause || '
1209                       from '
1210                       || poa_dbi_template_pkg.trend_sql(
1211                           l_xtd,
1212                           l_comparison_type,
1213                           l_mv,
1214                           l_where_clause,
1215                           l_col_tbl,
1216                           p_use_grpid => 'N',
1217                           p_in_join_tables => l_in_join_tbl);
1218 
1219     x_custom_sql := l_query;
1220   exception
1221    when others then
1222      err_msg := substr(sqlerrm,1,400);
1223   end;
1224 
1225   function get_amt_trend_sel_clause return varchar2
1226   is
1227     l_sel_clause varchar2(4000);
1228   begin
1229     l_sel_clause := 'select cal.name VIEWBY,';
1230     l_sel_clause := l_sel_clause || '
1231               nvl(c_proc_amt,0) POA_MEASURE1,
1232               ' || poa_dbi_util_pkg.change_clause('c_proc_amt','p_proc_amt','NP') || ' POA_PERCENT1,
1233               nvl(c_man_amt,0) POA_MEASURE2,
1234     ' || poa_dbi_util_pkg.rate_clause('c_man_amt','c_proc_amt', 'P') || ' POA_PERCENT2,
1235     ' || poa_dbi_util_pkg.change_clause(
1236            poa_dbi_util_pkg.rate_clause('c_man_amt','c_proc_amt', 'P'),
1237            poa_dbi_util_pkg.rate_clause('p_man_amt','p_proc_amt', 'P'),
1238            'P') || ' POA_MEASURE3,
1239     nvl(p_proc_amt,0) POA_ATTRIBUTE3,
1240     nvl(p_man_amt,0) POA_ATTRIBUTE4
1241 ';
1245 
1242     return l_sel_clause;
1243   end;
1244 
1246 /* PROCESSED AVERAGE AGE TREND REPORT */
1247 
1248   procedure age_trend_sql(p_param in bis_pmv_page_parameter_tbl
1249                        ,x_custom_sql out nocopy varchar2
1250                        ,x_custom_output out nocopy bis_query_attributes_tbl)
1251   is
1252     l_query               varchar2(10000);
1253     l_view_by             varchar2(120);
1254     l_view_by_col_name    varchar2(120);
1255     l_as_of_date          date;
1256     l_prev_as_of_date     date;
1257     l_org                 varchar2(100);
1258     l_category            varchar2(2000);
1259     l_commodity           varchar2(2000);
1260     l_commodity_where     varchar2(2000);
1261     l_item                varchar2(2000);
1262     l_buyer               varchar2(2000);
1263     l_mv                  VARCHAR2(90);
1264     l_supplier            varchar2(2000);
1265     l_supplier_site       VARCHAR2(2000);
1266     l_xtd                 varchar2(10);
1267     l_comparison_type     varchar2(1);
1268     l_nested_pattern      number;
1269     l_dim_bmap            number;
1270     l_org_where           varchar2(240);
1271     l_category_where      varchar2(120);
1272     l_item_where          varchar2(120);
1273     l_buyer_where         varchar2(1000);
1274     l_supplier_where      varchar2(120);
1275     l_supplier_site_where varchar2(120);
1276     l_cur_suffix          varchar2(2);
1277     l_url                 varchar2(300);
1278     l_impact_amount       varchar2(15);
1279     l_leakage_amount      varchar2(25);
1280     l_purchase_amount     varchar2(25);
1281     l_custom_sql          varchar2(9000);
1282     l_view_by_value       varchar2(30);
1283     l_dim_in_tbl          poa_dbi_util_pkg.POA_DBI_DIM_TBL;
1284     l_dim_out_tbl         poa_dbi_util_pkg.POA_DBI_DIM_TBL;
1285     l_col_rec             poa_dbi_util_pkg.POA_DBI_COL_REC;
1286     l_col_tbl             poa_dbi_util_pkg.POA_DBI_COL_TBL;
1287     l_total_col_tbl       poa_dbi_util_pkg.POA_DBI_COL_TBL;
1288     l_join_tbl            poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1289     l_in_join_tbl         poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1290     l_join_rec            poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1291     l_where_clause        VARCHAR2(2000);
1292     ERR_MSG               VARCHAR2(100);
1293     ERR_CDE               NUMBER;
1294   begin
1295     l_comparison_type := 'Y';
1296     l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1297     l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1298 
1299     poa_dbi_sutil_pkg.process_parameters(p_param,
1300                                          l_view_by,
1301                                          l_view_by_col_name,
1302                                          l_view_by_value,
1303                                          l_comparison_type,
1304                                          l_xtd,
1305                                          l_as_of_date,
1306                                          l_prev_as_of_date,
1307                                          l_cur_suffix,
1308                                          l_nested_pattern,
1309                                          l_where_clause,
1310                                          l_mv,
1311                                          l_join_tbl,
1312                                          l_in_join_tbl,
1313 					 x_custom_output,
1314                                          p_trend => 'Y',
1315                                          p_func_area => 'PO',
1316                                          p_version => '7.1',
1317                                          p_role => 'VPP',
1318                                          p_mv_set => 'REQMP');
1319 
1320 
1321     poa_dbi_util_pkg.add_column(
1322                        l_col_tbl,
1323                        'days_to_process',
1324                        'days_to_proc',
1325                        p_grand_total => 'N',
1326                        p_prior_code => poa_dbi_util_pkg.both_priors,
1327                        p_to_date_type => 'RLX'
1328                      );
1329 
1330     poa_dbi_util_pkg.add_column(
1331                        l_col_tbl,
1332                        'days_to_process_cnt',
1333                        'days_to_proc_cnt',
1334                        p_grand_total => 'N',
1335                        p_prior_code => poa_dbi_util_pkg.both_priors,
1336                        p_to_date_type => 'RLX'
1337                      );
1338 
1339     l_query := get_age_trend_sel_clause || 'from '|| fnd_global.newline
1340                  || poa_dbi_template_pkg.trend_sql(
1341                       l_xtd,
1342                       l_comparison_type,
1343                       l_mv,
1344                       l_where_clause,
1345                       l_col_tbl,
1346 		      p_use_grpid => 'N',
1347                       p_in_join_tables => l_in_join_tbl
1348                     );
1349 
1350     x_custom_sql := l_query;
1351   exception
1352    when others then
1353      err_msg := substr(sqlerrm,1,400);
1354   end;
1355 
1356   function get_age_trend_sel_clause return varchar2
1357   is
1358     l_sel_clause varchar2(4000);
1359   begin
1360     l_sel_clause := 'select cal.name VIEWBY,';
1361     l_sel_clause := l_sel_clause || '
1362 ' || poa_dbi_util_pkg.rate_clause(
1363                         'c_days_to_proc',
1364                         'c_days_to_proc_cnt',
1365                         'NP'
1366                       ) ||' POA_MEASURE1,
1367 ' || poa_dbi_util_pkg.change_clause(
1368                         poa_dbi_util_pkg.rate_clause('c_days_to_proc','c_days_to_proc_cnt','NP'),
1369                         poa_dbi_util_pkg.rate_clause('p_days_to_proc','p_days_to_proc_cnt','NP'),
1370                         'P'
1371                       ) || ' POA_MEASURE2,
1372 ' || poa_dbi_util_pkg.rate_clause(
1373                         'p_days_to_proc',
1377 ';
1374                         'p_days_to_proc_cnt',
1375                         'NP'
1376                       ) ||' POA_ATTRIBUTE2
1378     return l_sel_clause;
1379   end;
1380 
1381 
1382 /* PROCESSED LINES AUTOMATION TREND REPORT */
1383 
1384   procedure au_trend_sql(p_param in bis_pmv_page_parameter_tbl
1385                        ,x_custom_sql out nocopy varchar2
1386                        ,x_custom_output out nocopy bis_query_attributes_tbl)
1387   is
1388     l_query               varchar2(10000);
1389     l_view_by             varchar2(120);
1390     l_view_by_col_name    varchar2(120);
1391     l_as_of_date          date;
1392     l_prev_as_of_date     date;
1393     l_org                 varchar2(100);
1394     l_category            varchar2(2000);
1395     l_commodity           varchar2(2000);
1396     l_commodity_where     varchar2(2000);
1397     l_item                varchar2(2000);
1398     l_buyer               varchar2(2000);
1399     l_mv                  VARCHAR2(90);
1400     l_supplier            varchar2(2000);
1401     l_supplier_site       VARCHAR2(2000);
1402     l_xtd                 varchar2(10);
1403     l_comparison_type     varchar2(1);
1404     l_nested_pattern      number;
1405     l_dim_bmap            number;
1406     l_org_where           varchar2(240);
1407     l_category_where      varchar2(120);
1408     l_item_where          varchar2(120);
1409     l_buyer_where         varchar2(1000);
1410     l_supplier_where      varchar2(120);
1411     l_supplier_site_where varchar2(120);
1412     l_cur_suffix          varchar2(2);
1413     l_url                 varchar2(300);
1414     l_impact_amount       varchar2(15);
1415     l_leakage_amount      varchar2(25);
1416     l_purchase_amount     varchar2(25);
1417     l_custom_sql          varchar2(9000);
1418     l_view_by_value       varchar2(30);
1419     l_dim_in_tbl          poa_dbi_util_pkg.POA_DBI_DIM_TBL;
1420     l_dim_out_tbl         poa_dbi_util_pkg.POA_DBI_DIM_TBL;
1421     l_col_rec             poa_dbi_util_pkg.POA_DBI_COL_REC;
1422     l_col_tbl             poa_dbi_util_pkg.POA_DBI_COL_TBL;
1423     l_total_col_tbl       poa_dbi_util_pkg.POA_DBI_COL_TBL;
1424     l_join_tbl            poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1425     l_in_join_tbl         poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1426     l_join_rec            poa_dbi_util_pkg.POA_DBI_JOIN_REC;
1427     l_where_clause        VARCHAR2(2000);
1428     ERR_MSG               VARCHAR2(100);
1429     ERR_CDE               NUMBER;
1430   begin
1431     l_comparison_type := 'Y';
1432     l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1433     l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1434 
1435     poa_dbi_sutil_pkg.process_parameters(p_param,
1436                                          l_view_by,
1437                                          l_view_by_col_name,
1438                                          l_view_by_value,
1439                                          l_comparison_type,
1440                                          l_xtd,
1441                                          l_as_of_date,
1442                                          l_prev_as_of_date,
1443                                          l_cur_suffix,
1444                                          l_nested_pattern,
1445                                          l_where_clause,
1446                                          l_mv,
1447                                          l_join_tbl,
1448                                          l_in_join_tbl,
1449 					 x_custom_output,
1450                                          p_trend => 'Y',
1451                                          p_func_area => 'PO',
1452                                          p_version => '7.1',
1453                                          p_role => 'VPP',
1454                                          p_mv_set => 'REQMP');
1455 
1456 
1457     poa_dbi_util_pkg.add_column(l_col_tbl
1458                     , 'processed_cnt_'||l_cur_suffix
1459                     , 'proc_cnt'
1460                     , p_grand_total => 'N'
1461                     , p_prior_code => poa_dbi_util_pkg.both_priors
1462                     , p_to_date_type => 'RLX');
1463 
1464     poa_dbi_util_pkg.add_column(l_col_tbl
1465                     , 'processed_man_cnt_'||l_cur_suffix
1466                     , 'man_cnt'
1467                     , p_grand_total => 'N'
1468                     , p_prior_code => poa_dbi_util_pkg.both_priors
1469                     , p_to_date_type => 'RLX');
1470 
1471     l_query := get_au_trend_sel_clause || '
1472                       from '
1473                       || poa_dbi_template_pkg.trend_sql(
1474                           l_xtd,
1475                           l_comparison_type,
1476                           l_mv,
1477                           l_where_clause,
1478                           l_col_tbl,
1479 			  p_use_grpid => 'N',
1480                           p_in_join_tables => l_in_join_tbl);
1481 
1482     x_custom_sql := l_query;
1483   exception
1484    when others then
1485      err_msg := substr(sqlerrm,1,400);
1486   end;
1487 
1488   function get_au_trend_sel_clause return varchar2
1489   is
1490     l_sel_clause varchar2(4000);
1491   begin
1492     l_sel_clause := 'select cal.name VIEWBY,';
1493     l_sel_clause := l_sel_clause || '
1494 nvl(c_proc_cnt,0) POA_MEASURE1,
1495 ' || poa_dbi_util_pkg.change_clause('c_proc_cnt','p_proc_cnt','NP') || ' POA_PERCENT1,
1496 nvl(c_man_cnt,0) POA_MEASURE2,
1497 ' || poa_dbi_util_pkg.change_clause('c_man_cnt','p_man_cnt','NP') || ' POA_PERCENT2,
1498 (nvl(c_proc_cnt,0) - nvl(c_man_cnt,0)) POA_MEASURE3,
1499 ' || poa_dbi_util_pkg.change_clause('(nvl(c_proc_cnt,0) - nvl(c_man_cnt,0))','(nvl(p_proc_cnt,0) - nvl(p_man_cnt,0))','NP') || ' POA_PERCENT3
1500 ';
1501     return l_sel_clause;
1502   end;
1503 
1504   procedure dtl_sql(p_param in bis_pmv_page_parameter_tbl
1508     l_query               varchar2(10000);
1505                        ,x_custom_sql out nocopy varchar2
1506                        ,x_custom_output out nocopy bis_query_attributes_tbl)
1507   is
1509     l_option              number;
1510     l_cur_suffix          varchar2(2);
1511     l_where_clause        varchar2(2000);
1512     l_in_join_tbl         poa_dbi_util_pkg.poa_dbi_in_join_tbl;
1513     l_in_join_tables      varchar2(240) ;
1514     l_xtd                 varchar2(10);
1515     err_msg               varchar2(100);
1516     l_bucket              varchar2(50);
1517     l_bucket_where        varchar2(440);
1518   begin
1519     poa_dbi_sutil_pkg.drill_process_parameters(
1520                         p_param,
1521                         l_cur_suffix,
1522                         l_where_clause,
1523                         l_in_join_tbl,
1524                         'PO',
1525                         '7.1',
1526                         'VPP',
1527                         'REQMP'
1528                       );
1529 
1530     for i in 1..p_param.count
1531     loop
1532       if (p_param(i).parameter_name = 'POA_ATTRIBUTE10')
1533       then
1534         l_option := p_param(i).parameter_id;
1535       end if;
1536       if (p_param(i).parameter_name = 'POA_BUCKET+REQUISITION_AGING')
1537       then
1538         l_bucket := p_param(i).parameter_id;
1539       end if;
1540     end loop;
1541 
1542     if (l_option = 2) then --manual reqs
1543       l_where_clause := l_where_clause || fnd_global.newline || ' and fact.po_creation_method = ''M'' ';
1544     end if;
1545 
1546     if(l_bucket is not null)
1547     then
1548       l_bucket_where := 'and (&RANGE_LOW is null or '
1549           || 'greatest(0,(fact.po_approved_date-fact.req_approved_date))'
1550           || ' >= &RANGE_LOW)'
1551           || fnd_global.newline
1552           || 'and (&RANGE_HIGH is null or '
1553           || 'greatest(0,(fact.po_approved_date-fact.req_approved_date))'
1554           || ' < &RANGE_HIGH)';
1555 
1556       poa_dbi_util_pkg.bind_low_high(p_param
1557           , 'POA_DBI_PR_BUCKET'
1558           , 'POA_BUCKET+REQUISITION_AGING'
1559           , '&RANGE_LOW'
1560           , '&RANGE_HIGH'
1561           , x_custom_output);
1562     else
1563       l_bucket_where := '';
1564     end if;
1565 
1566 
1567 
1568     ---Begin MOAC changes
1569     ---Following block is removed from custom_sql as l_where_clause is already
1570     --- having a security clause
1571        --        per_organization_list orgl
1572        --       where
1573        --        fact.org_id=orgl.organization_id
1574        --        and orgl.security_profile_id=' || poa_dbi_util_pkg.get_sec_profile ||
1575     ---End  MOAC changes
1576 
1577     x_custom_sql := '
1578     select
1579     prh.segment1 POA_MEASURE1,                                -- Requisition Number
1580     prl.line_num POA_PERCENT1,                                -- Line Num
1581     rorg.name POA_MEASURE5,                                   -- Req Creation OU
1582     substrb(perf.first_name,1,1) || ''. '' || perf.last_name POA_MEASURE2,  -- Requestor Name
1583     POA_PERCENT2 POA_PERCENT2,                                -- Req Approved Date
1584     POA_MEASURE7 POA_MEASURE7,                                -- Processed Date
1585     POA_MEASURE8 POA_MEASURE8,                                -- Fulfilled Date
1586     item.value POA_PERCENT3,                                  -- Item
1587     supplier.value POA_PERCENT4,                              -- Supplier
1588     i.POA_MEASURE3 POA_MEASURE3,                              -- Amount
1589     decode(pll.po_release_id,null,
1590            poh.segment1,
1591            poh.segment1||''-''||por.release_num) POA_PERCENT5,   -- PO Number
1592     porg.name POA_MEASURE4,                                   -- PO OU
1593     POA_MEASURE6 POA_MEASURE6,                                -- Grand Total for Amount
1594     prh.requisition_header_id POA_ATTRIBUTE3,
1595     prl.requisition_line_id POA_ATTRIBUTE4,
1596     poh.po_header_id POA_ATTRIBUTE5,
1597     pll.po_release_id POA_ATTRIBUTE6
1598     from (select (rank() over (&ORDER_BY_CLAUSE nulls last, req_header_id, req_line_id))-1 rnk,
1599           req_header_id,
1600           req_line_id,
1601           req_creation_ou_id,
1602           requester_id,
1603           POA_PERCENT2 POA_PERCENT2,
1604           POA_MEASURE7 POA_MEASURE7,
1605           POA_MEASURE8 POA_MEASURE8,
1606           POA_MEASURE9 POA_MEASURE9,
1607           po_item_id,
1608           supplier_id,
1609           nvl(POA_MEASURE3,0) POA_MEASURE3,
1610           nvl(POA_MEASURE6,0) POA_MEASURE6,
1611           po_line_location_id,
1612           po_creation_ou_id
1613           from ( select
1614                  fact.req_header_id,
1615                  fact.req_line_id,
1616                  fact.req_creation_ou_id,
1617                  fact.requester_id,
1618                  fact.req_approved_date POA_PERCENT2,
1619                  fact.po_approved_date POA_MEASURE7,
1620                  fact.req_fulfilled_date POA_MEASURE8,
1621                  fact.expected_date POA_MEASURE9,
1622                  fact.po_item_id,
1623                  fact.supplier_id,
1624                  fact.line_amount_'||l_cur_suffix||' POA_MEASURE3,
1625                  sum(fact.line_amount_'||l_cur_suffix||') over() POA_MEASURE6,
1626                  fact.po_line_location_id,
1627                  fact.po_creation_ou_id
1628                  from
1629                  poa_dbi_req_f fact
1630                  where fact.po_approved_date between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_EFFECTIVE_END_DATE + (86399/86400) '
1631                  || fnd_global.newline || l_where_clause
1632                  || fnd_global.newline || l_bucket_where ||
1633               ')
1634          ) i,
1635          po_requisition_headers_all prh,
1636          po_requisition_lines_all prl,
1637          po_headers_all poh,
1638          po_line_locations_all pll,
1639          poa_items_v item,
1640          poa_suppliers_v supplier,
1641          per_all_people_f perf,
1642          hr_all_organization_units_vl rorg,
1643          hr_all_organization_units_vl porg,
1644          po_releases_all por
1645     where i.req_header_id=prh.requisition_header_id
1646     and   i.req_line_id=prl.requisition_line_id
1647     and   prh.requisition_header_id=prl.requisition_header_id
1648     and   i.po_item_id=item.id
1649     and   i.req_creation_ou_id=rorg.organization_id
1650     and   i.requester_id=perf.person_id
1651     and   sysdate between perf.effective_start_date and perf.effective_end_date
1652     and   i.supplier_id=supplier.id(+)
1653     and   i.po_line_location_id=pll.line_location_id(+)
1654     and   pll.po_header_id=poh.po_header_id(+)
1655     and   poh.org_id=porg.organization_id(+)
1656     and   pll.po_header_id = por.po_header_id(+)
1657     and   pll.po_release_id = por.po_release_id(+)
1658     and   (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
1659     ORDER BY rnk ';
1660 
1661     poa_dbi_util_pkg.get_custom_status_binds(x_custom_output);
1662     poa_dbi_util_pkg.get_custom_rolling_binds(x_custom_output,l_xtd);
1663   exception
1664     when others then
1665       err_msg := substr(sqlerrm,1,400);
1666   end;
1667 
1668 end poa_dbi_pr_pkg;