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_PQC_PKG

Source


1 PACKAGE BODY poa_dbi_pqc_pkg
2 /* $Header: poadbipqcb.pls 120.12 2006/09/15 10:44:29 nchava noship $*/
3 AS
4 FUNCTION get_status_sel_clause(p_view_by_dim    IN VARCHAR2
5                               ,p_view_by_col    IN VARCHAR2
6                               ,p_url            IN VARCHAR2
7                               ,p_sameyear       IN NUMBER
8                               ,p_sec_context    IN VARCHAR2) RETURN VARCHAR2;
9 
10 FUNCTION get_kpi_sel_clause(p_view_by_dim   IN VARCHAR2
11                            ,p_view_by_col   IN VARCHAR2
12                            ,p_url           IN VARCHAR2
13                            ,p_sameyear      IN NUMBER
14                            ,p_prev_sameyear IN NUMBER) RETURN VARCHAR2;
15 
16 FUNCTION get_status_filter_where(p_view_by IN VARCHAR2) return VARCHAR2;
17 FUNCTION get_dtl_filter_where return VARCHAR2;
18 
19   PROCEDURE status_sql(p_param            IN          BIS_PMV_PAGE_PARAMETER_TBL
20                     ,x_custom_sql       OUT NOCOPY  VARCHAR2
21                     ,x_custom_output    OUT NOCOPY  BIS_QUERY_ATTRIBUTES_TBL)
22   IS
23     l_query             varchar2(10000);
24     l_view_by           varchar2(120);
25     l_view_by_col       varchar2(120);
26     l_as_of_date        date;
27     l_prev_as_of_date   date;
28     l_xtd               varchar2(10);
29     l_comparison_type   varchar2(1) := 'Y';
30     l_nested_pattern    number;
31     l_cur_suffix        varchar2(2);
32     l_col_tbl           poa_dbi_util_pkg.poa_dbi_col_tbl;
33     l_join_tbl          poa_dbi_util_pkg.poa_dbi_join_tbl;
34     l_in_join_tbl       poa_dbi_util_pkg.poa_dbi_in_join_tbl;
35     l_in_join_tbl2      poa_dbi_util_pkg.poa_dbi_in_join_tbl;
36     l_where_clause      varchar2(2000);
37     l_where_clause2     varchar2(2000);
38     l_view_by_value     varchar2(100);
39     l_mv                varchar2(30);
40     l_mv2               varchar2(30);
41     l_asof_year         date;
42     l_prev_asof_year    date;
43     l_url               varchar2(300);
44     l_sec_context       varchar2(10);
45     l_use_only_agg_mv   varchar2(1);
46     l_mv_tbl            poa_dbi_util_pkg.poa_dbi_mv_tbl;
47   BEGIN
48     l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
49     l_col_tbl  := poa_dbi_util_pkg.poa_dbi_col_tbl();
50 
51     l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
52     if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then
53       poa_dbi_sutil_pkg.process_parameters(
54         p_param              => p_param,
55         p_view_by            => l_view_by,
56         p_view_by_col_name   => l_view_by_col,
57         p_view_by_value      => l_view_by_value,
58         p_comparison_type    => l_comparison_type,
59         p_xtd                => l_xtd,
60         p_as_of_date         => l_as_of_date,
61         p_prev_as_of_date    => l_prev_as_of_date,
62         p_cur_suffix         => l_cur_suffix,
63         p_nested_pattern     => l_nested_pattern,
64         p_where_clause       => l_where_clause,
65         p_mv                 => l_mv,
66         p_join_tbl           => l_join_tbl,
67         p_in_join_tbl        => l_in_join_tbl,
68         x_custom_output      => x_custom_output,
69         p_trend              => 'N',
70         p_func_area          => 'PO',
71         p_version            => '6.0',
72         p_role               => 'COM',
73         p_mv_set             => 'PQC');
74 
75       poa_dbi_util_pkg.add_column(l_col_tbl, 'pbpcqco_amt_' || l_cur_suffix, 'benchmark_amt');
76       poa_dbi_util_pkg.add_column(l_col_tbl, 'pbcqco_amt_' || l_cur_suffix, 'fallback_amt');
77       poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'amt');
78 
79       if(l_view_by = 'ITEM+POA_ITEMS') then
80         poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
81       end if;
82 
83       if((l_view_by = 'SUPPLIER+POA_SUPPLIERS') and (l_view_by_value is not null) and (instr(l_view_by_value,',') = 0)) then
84         l_url := null;
85       else
86         if(l_view_by = 'ITEM+POA_ITEMS') then
87           l_url := 'pFunctionName=POA_DBI_PQC_DTL_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
88         else
89           l_url := 'pFunctionName=POA_DBI_PQC_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=SUPPLIER+POA_SUPPLIERS&pParamIds=Y';
90         end if;
91       end if;
92 
93       l_asof_year := fii_time_api.ent_cyr_start(l_as_of_date);
94       l_prev_asof_year := fii_time_api.ent_cyr_start(l_prev_as_of_date);
95 
96       l_query := get_status_sel_clause(
97                    l_view_by,
98                    l_view_by_col,
99                    l_url,
100                    l_asof_year - l_prev_asof_year,
101                    l_sec_context) || ' from
102                    ' ||
103                    poa_dbi_template_pkg.status_sql(
104                      p_fact_name      => l_mv,
105                      p_where_clause   => l_where_clause,
106                      p_join_tables    => l_join_tbl,
107                      p_use_windowing  => 'Y',
108                      p_col_name       => l_col_tbl,
109                      p_use_grpid      => 'N',
110                      p_filter_where   => get_status_filter_where(l_view_by),
111                      p_in_join_tables => l_in_join_tbl);
112     elsif (l_sec_context = 'COMP') then
113       poa_dbi_sutil_pkg.process_parameters(
114         p_param              => p_param,
115         p_view_by            => l_view_by,
116         p_view_by_col_name   => l_view_by_col,
117         p_view_by_value      => l_view_by_value,
118         p_comparison_type    => l_comparison_type,
119         p_xtd                => l_xtd,
120         p_as_of_date         => l_as_of_date,
121         p_prev_as_of_date    => l_prev_as_of_date,
122         p_cur_suffix         => l_cur_suffix,
123         p_nested_pattern     => l_nested_pattern,
124         p_where_clause       => l_where_clause,
125         p_mv                 => l_mv,
126         p_join_tbl           => l_join_tbl,
127         p_in_join_tbl        => l_in_join_tbl,
128         x_custom_output      => x_custom_output,
129         p_trend              => 'N',
130         p_func_area          => 'PO',
131         p_version            => '8.0',
132         p_role               => 'COM',
133         p_mv_set             => 'PQCA');
134 
135       /*check if we can get everything from aggregated mv*/
136       l_use_only_agg_mv := 'Y';
137       for i in 1..l_in_join_tbl.count loop
138         if(l_in_join_tbl(i).table_alias = 'com' or l_in_join_tbl(i).table_alias = 'cc') then
139           if(l_in_join_tbl(i).aggregated_flag = 'N')then
140             l_use_only_agg_mv := 'N';
141           end if;
142         end if;
143       end loop;
144 
145       if(l_use_only_agg_mv = 'N') then
146         poa_dbi_sutil_pkg.process_parameters(
147           p_param              => p_param,
148           p_view_by            => l_view_by,
149           p_view_by_col_name   => l_view_by_col,
150           p_view_by_value      => l_view_by_value,
151           p_comparison_type    => l_comparison_type,
152           p_xtd                => l_xtd,
153           p_as_of_date         => l_as_of_date,
154           p_prev_as_of_date    => l_prev_as_of_date,
155           p_cur_suffix         => l_cur_suffix,
156           p_nested_pattern     => l_nested_pattern,
157           p_where_clause       => l_where_clause2,
158           p_mv                 => l_mv2,
159           p_join_tbl           => l_join_tbl,
160           p_in_join_tbl        => l_in_join_tbl2,
161           x_custom_output      => x_custom_output,
162           p_trend              => 'N',
163           p_func_area          => 'PO',
164           p_version            => '8.0',
165           p_role               => 'COM',
166           p_mv_set             => 'PQCB');
167       end if;
168       poa_dbi_util_pkg.add_column(l_col_tbl, 'pbpcqco_amt_' || l_cur_suffix, 'benchmark_amt');
169       poa_dbi_util_pkg.add_column(l_col_tbl, 'pbcqco_amt_' || l_cur_suffix, 'fallback_amt');
170       poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'amt');
171 
172       if(l_view_by = 'ITEM+POA_ITEMS') then
173         poa_dbi_util_pkg.add_column(l_col_tbl, 'quantity', 'quantity');
174       end if;
175 
176       if((l_view_by = 'SUPPLIER+POA_SUPPLIERS') and (l_view_by_value is not null) and (instr(l_view_by_value,',') = 0)) then
177         l_url := null;
178       else
179         if(l_view_by = 'ITEM+POA_ITEMS') then
180           l_url := 'pFunctionName=POA_DBI_CC_PQC_DTL_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
181         else
182           l_url := 'pFunctionName=POA_DBI_CC_PQC_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=SUPPLIER+POA_SUPPLIERS&pParamIds=Y';
183         end if;
184       end if;
185 
186       l_asof_year := fii_time_api.ent_cyr_start(l_as_of_date);
187       l_prev_asof_year := fii_time_api.ent_cyr_start(l_prev_as_of_date);
188 
189       if(l_use_only_agg_mv = 'N') then
190         l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
191         l_mv_tbl.extend;
192         l_mv_tbl(1).mv_name := l_mv;
193         l_mv_tbl(1).mv_col := l_col_tbl;
194         l_mv_tbl(1).mv_where := l_where_clause;
195         l_mv_tbl(1).in_join_tbls := l_in_join_tbl;
196         l_mv_tbl(1).use_grp_id := 'N';
197 
198         l_mv_tbl.extend;
199         l_mv_tbl(2).mv_name := l_mv2;
200         l_mv_tbl(2).mv_col := l_col_tbl;
201         l_mv_tbl(2).mv_where := l_where_clause2;
202         l_mv_tbl(2).in_join_tbls := l_in_join_tbl2;
203         l_mv_tbl(2).use_grp_id := 'N';
204 
205         l_query := get_status_sel_clause(
206                      l_view_by,
207                      l_view_by_col,
208                      l_url,
209                      l_asof_year - l_prev_asof_year,
210                      l_sec_context) || ' from ('||fnd_global.newline||
211                      poa_dbi_template_pkg.union_all_status_sql(
212                        p_mv              => l_mv_tbl,
213                        p_join_tables     => l_join_tbl,
214                        p_use_windowing   => 'Y',
215                        p_paren_count     => 3,
216                        p_filter_where    => get_status_filter_where(l_view_by),
217                        p_generate_viewby => 'Y',
218                        p_diff_measures   => 'N');
219       else
220         l_query := get_status_sel_clause(
221                      l_view_by,
222                      l_view_by_col,
223                      l_url,
224                      l_asof_year - l_prev_asof_year,
225                      l_sec_context) || ' from
226                      ' ||
227                      poa_dbi_template_pkg.status_sql(
228                        p_fact_name      => l_mv,
229                        p_where_clause   => l_where_clause,
230                        p_join_tables    => l_join_tbl,
231                        p_use_windowing  => 'Y',
232                        p_col_name       => l_col_tbl,
233                        p_use_grpid      => 'N',
234                        p_filter_where   => get_status_filter_where(l_view_by),
235                        p_in_join_tables => l_in_join_tbl);
236       end if; /* l_use_only_agg_mv = 'N' */
237     end if; /* l_sec_context = 'OU' or l_sec_context = 'OU/COM' */
238     x_custom_sql := l_query;
239   end;
240 
241 
242   FUNCTION get_status_filter_where(p_view_by IN VARCHAR2) return VARCHAR2
243   IS
244     l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
245   BEGIN
246     l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
247     l_col_tbl.extend;
248     l_col_tbl(1) := 'POA_MEASURE1';
249     l_col_tbl.extend;
250     l_col_tbl(2) := 'POA_MEASURE2';
251     l_col_tbl.extend;
252     l_col_tbl(3) := 'POA_MEASURE3';
253     l_col_tbl.extend;
254     l_col_tbl(4) := 'POA_MEASURE4';
255     l_col_tbl.extend;
256     l_col_tbl(5) := 'POA_MEASURE5';
257     l_col_tbl.extend;
258     l_col_tbl(6) := 'POA_MEASURE6';
259     l_col_tbl.extend;
260     l_col_tbl(7) := 'POA_MEASURE17';
261 
262     if(p_view_by = 'ITEM+POA_ITEMS') then
263        l_col_tbl.extend;
264        l_col_tbl(8) := 'POA_MEASURE13';
265        l_col_tbl.extend;
266        l_col_tbl(9) := 'POA_MEASURE15';
267        l_col_tbl.extend;
268        l_col_tbl(10) := 'POA_MEASURE16';
269     end if;
270     return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
271   END;
272 
273 
274   FUNCTION get_status_sel_clause(p_view_by_dim    IN VARCHAR2
275                               ,p_view_by_col    IN VARCHAR2
276                               ,p_url            IN VARCHAR2
277                               ,p_sameyear       IN NUMBER
278                               ,p_sec_context    IN VARCHAR2) RETURN VARCHAR2
279   IS
280     l_sel_clause VARCHAR2(4000);
281   BEGIN
282     l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim, 'PO', '6.0');
283 
284     if(p_view_by_dim = 'ITEM+POA_ITEMS') then
285       l_sel_clause := l_sel_clause || '
286       v.description POA_ATTRIBUTE1,     --Description
287       v2.description POA_ATTRIBUTE2,    --UOM
288       oset.POA_MEASURE13 POA_MEASURE13, --Current Quantity
289       oset.POA_MEASURE15 POA_MEASURE15, --Prior Quantity
290       oset.POA_MEASURE16 POA_MEASURE16, --Quantity Change
291 ';
292     else
293       l_sel_clause := l_sel_clause || '
294       null POA_MEASURE13,  --Current Quantity
295       null POA_MEASURE15,  --Prior Quantity
296       null POA_MEASURE16,  --Quantity Change
297       null POA_ATTRIBUTE1, --Description
298       null POA_ATTRIBUTE2, --UOM
299     ';
300     end if;
301 
302     l_sel_clause := l_sel_clause || '
303       oset.POA_MEASURE1 POA_MEASURE1,   --Price Savings Amount
304       oset.POA_MEASURE2 POA_MEASURE2,   --Savings Rate
305       oset.POA_MEASURE3 POA_MEASURE3,   --Current Amount at PO Price
306       oset.POA_MEASURE4 POA_MEASURE4,   --Current Amount
307       oset.POA_MEASURE5 POA_MEASURE5,   --Prior Amount
308       oset.POA_MEASURE6 POA_MEASURE6,   --Quantity Change Amount
309       oset.POA_MEASURE17 POA_MEASURE17, --Quantity Change Amount at Benchmark
310       oset.POA_MEASURE7 POA_MEASURE7,   --Total Price Savings Amount[
311       oset.POA_MEASURE8 POA_MEASURE8,   --Total Savings Rate
312       oset.POA_MEASURE9 POA_MEASURE9,   --Total Current Amount at PO Price
313       oset.POA_MEASURE10 POA_MEASURE10, --Total Current Amount
314       oset.POA_MEASURE11 POA_MEASURE11, --Total Prior Amount
315       oset.POA_MEASURE12 poa_measure12, --Total Quantity Change Amount
316       oset.POA_MEASURE12 poa_measure18, --Total Quantity Change Amount at Benchmark
317       ''' || p_url || ''' POA_ATTRIBUTE3,';
318 
319    if (p_view_by_dim = 'FII_COMPANIES+FII_COMPANIES' or
320        p_view_by_dim = 'ORGANIZATION+HRI_CL_ORGCC') then
321      l_sel_clause := l_sel_clause || '
322        decode(v.summary_flag,''Y'',''pFunctionName=POA_DBI_CC_PQC_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'',null) POA_ATTRIBUTE6,
323        decode(v.summary_flag,''Y'',''pFunctionName=POA_DBI_CC_PQC_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'',null) POA_ATTRIBUTE7,';
324    else
325      l_sel_clause := l_sel_clause || '
326        null POA_ATTRIBUTE6,
327        null POA_ATTRIBUTE7,';
328    end if;
329 
330    if (p_sec_context = 'COMP') then
331      l_sel_clause := l_sel_clause ||'
332        ''pFunctionName=POA_DBI_CC_PQC_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+POA_ITEMS&pParamIds=Y'' POA_ATTRIBUTE8 ';
333    else
334      l_sel_clause := l_sel_clause ||'
335        ''pFunctionName=POA_DBI_PQC_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+POA_ITEMS&pParamIds=Y'' POA_ATTRIBUTE8 ';
336    end if;
337 
338    l_sel_clause := l_sel_clause || '
339       from
340       (select (rank() over ( &ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
341 
342     if(p_view_by_dim = 'ITEM+POA_ITEMS') then
343       l_sel_clause := l_sel_clause || ', base_uom';
344     end if;
345 
346     l_sel_clause := l_sel_clause || ')) - 1 rnk,'
347         || p_view_by_col;
348 
349     if(p_view_by_dim = 'ITEM+POA_ITEMS') then
350       l_sel_clause := l_sel_clause || ', base_uom, POA_MEASURE13,POA_MEASURE15,POA_MEASURE16';
351     end if;
352 
353     l_sel_clause := l_sel_clause || ',
354         POA_MEASURE1, POA_MEASURE2,
355         POA_MEASURE3, POA_MEASURE4,
356         POA_MEASURE5, POA_MEASURE6,
357         POA_MEASURE7, POA_MEASURE8,
358         POA_MEASURE9, POA_MEASURE10,
359         POA_MEASURE11, POA_MEASURE12,
360         POA_MEASURE17
361         from
362         (select ' || p_view_by_col || ',
363              ' || p_view_by_col || ' VIEWBY,';
364 
365 
366     if(p_view_by_dim = 'ITEM+POA_ITEMS') then
367      l_sel_clause := l_sel_clause || ' base_uom, nvl(c_quantity,0) POA_MEASURE13,
368       nvl(p_quantity,0) POA_MEASURE15, nvl(c_quantity,0) - Nvl(p_quantity,0) POA_MEASURE16,';
369     end if;
370 
371     IF (p_sameyear = 0) then
372       l_sel_clause := l_sel_clause || '
373       nvl(c_benchmark_amt,0) - nvl(c_amt,0) POA_MEASURE1,
374       ' || poa_dbi_util_pkg.rate_clause('c_benchmark_amt','c_amt') || '-100 POA_MEASURE2,
375       nvl(c_amt,0) POA_MEASURE3,
376       nvl(c_benchmark_amt,0) POA_MEASURE4,
377       nvl(p_benchmark_amt,0) POA_MEASURE5,
378       Nvl(c_benchmark_amt,0) - Nvl(p_benchmark_amt, 0) POA_MEASURE6,
379       Nvl(c_benchmark_amt,0) - Nvl(p_benchmark_amt, 0) POA_MEASURE17,
380       nvl(c_benchmark_amt_total,0) - nvl(c_amt_total,0) POA_MEASURE7,
381       ' || poa_dbi_util_pkg.rate_clause('c_benchmark_amt_total','c_amt_total') || '-100 POA_MEASURE8,
382       nvl(c_amt_total,0) poa_measure9,
383       nvl(c_benchmark_amt_total,0) POA_MEASURE10,
384       nvl(p_benchmark_amt_total,0) POA_MEASURE11,
385       nvl(c_benchmark_amt_total,0) - Nvl(p_benchmark_amt_total, 0) POA_MEASURE12
386       ';
387     ELSE
388       l_sel_clause := l_sel_clause || '
389       nvl(c_benchmark_amt,0) - nvl(c_amt,0)  POA_MEASURE1,
390       ' || poa_dbi_util_pkg.rate_clause('c_benchmark_amt','c_amt') || '-100 POA_MEASURE2,
391       nvl(c_amt,0) POA_MEASURE3,
392       nvl(c_benchmark_amt,0) POA_MEASURE4,
393       nvl(p_fallback_amt,0) POA_MEASURE5,
394       nvl(c_benchmark_amt,0) - Nvl(p_fallback_amt, 0) POA_MEASURE6,
395       nvl(c_benchmark_amt,0) - Nvl(p_fallback_amt, 0) POA_MEASURE17,
396       nvl(c_benchmark_amt_total,0) - nvl(c_amt_total,0) POA_MEASURE7,
397       ' || poa_dbi_util_pkg.rate_clause('c_benchmark_amt_total','c_amt_total') || '-100 POA_MEASURE8,
398       nvl(c_amt_total,0) poa_measure9,
399       nvl(c_benchmark_amt_total,0) POA_MEASURE10,
400       nvl(p_fallback_amt_total,0) POA_MEASURE11,
401       nvl(c_benchmark_amt_total,0) - Nvl(p_fallback_amt_total, 0) POA_MEASURE12
402     ';
403     END IF;
404 
405     RETURN l_sel_clause;
406   END;
407 
408   PROCEDURE kpi_sql(p_param         IN         BIS_PMV_PAGE_PARAMETER_TBL
409                    ,x_custom_sql    OUT NOCOPY VARCHAR2
410                    ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
411   is
412     l_query                    varchar2(10000);
413     l_view_by                  varchar2(120);
414     l_as_of_date               date;
415     l_prev_as_of_date          date;
416     l_prev_prev_as_of_date     date;
417     l_view_by_col              varchar2(120);
418     l_asof_year                date;
419     l_prev_asof_year           date;
420     l_prev_prev_asof_year      date;
421     l_url                      varchar2(300);
422     l_mv                       varchar2(30);
423     l_mv2                      varchar2(30);
424     l_view_by_value            varchar2(100);
425     l_xtd                      varchar2(10);
426     l_where_clause             varchar2(2000);
427     l_where_clause2            varchar2(2000);
428     l_comparison_type          varchar2(1) := 'Y';
429     l_nested_pattern           number;
430     l_cur_suffix               varchar2(2);
431     l_col_tbl                  poa_dbi_util_pkg.poa_dbi_col_tbl;
432     l_join_tbl                 poa_dbi_util_pkg.poa_dbi_join_tbl;
433     l_in_join_tbl              poa_dbi_util_pkg.poa_dbi_in_join_tbl;
434     l_in_join_tbl2             poa_dbi_util_pkg.poa_dbi_in_join_tbl;
435     l_custom_rec               bis_query_attributes;
436     l_sec_context              varchar2(10);
437     l_use_only_agg_mv          varchar2(1);
438     l_mv_tbl                   poa_dbi_util_pkg.poa_dbi_mv_tbl;
439   begin
440     l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
441     l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
442     l_custom_rec := bis_pmv_parameters_pub.initialize_query_type;
443 
444     l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
445     if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then
446       poa_dbi_sutil_pkg.process_parameters(
447         p_param              => p_param,
448         p_view_by            => l_view_by,
449         p_view_by_col_name   => l_view_by_col,
450         p_view_by_value      => l_view_by_value,
451         p_comparison_type    => l_comparison_type,
452         p_xtd                => l_xtd,
453         p_as_of_date         => l_as_of_date,
454         p_prev_as_of_date    => l_prev_as_of_date,
455         p_cur_suffix         => l_cur_suffix,
456         p_nested_pattern     => l_nested_pattern,
457         p_where_clause       => l_where_clause,
458         p_mv                 => l_mv,
459         p_join_tbl           => l_join_tbl,
460         p_in_join_tbl        => l_in_join_tbl,
461         x_custom_output      => x_custom_output,
462         p_trend              => 'N',
463         p_func_area          => 'PO',
464         p_version            => '6.0',
465         p_role               => 'COM',
466         p_mv_set             => 'PQC');
467 
468       poa_dbi_util_pkg.add_column(l_col_tbl, 'pbpcqco_amt_' || l_cur_suffix, 'benchmark_amt','Y',poa_dbi_util_pkg.PREV_PREV);
469       poa_dbi_util_pkg.add_column(l_col_tbl, 'pbcqco_amt_' || l_cur_suffix, 'fallback_amt','Y',poa_dbi_util_pkg.PREV_PREV);
470       poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'amt','Y');
471 
472       l_prev_prev_as_of_date := poa_dbi_calendar_pkg.previous_period_asof_date(l_prev_as_of_date, l_xtd, l_comparison_type);
473 
474       l_asof_year := fii_time_api.ent_cyr_start(l_as_of_date);
475       l_prev_asof_year := fii_time_api.ent_cyr_start(l_prev_as_of_date);
476 
477        begin
478            l_prev_prev_asof_year := fii_time_api.ent_cyr_start(l_prev_prev_as_of_date);
479            exception
480             when no_data_found then
481             l_prev_prev_asof_year := null;
482        end;
483 
484 
485 
486       l_query := get_kpi_sel_clause(l_view_by
487                                    ,l_view_by_col
488                                    ,l_url
489                                    ,l_asof_year - l_prev_asof_year
490                                    ,l_prev_asof_year-l_prev_prev_asof_year);
491       l_query := l_query || ' from ';
492 
493       l_query := l_query ||
494                    poa_dbi_template_pkg.status_sql(
495                      p_fact_name      => l_mv,
496                      p_where_clause   => l_where_clause,
497                      p_join_tables    => l_join_tbl,
498                      p_use_windowing  => 'Y',
499                      p_col_name       => l_col_tbl,
500                      p_use_grpid      => 'N',
501                      p_in_join_tables => l_in_join_tbl);
502 
503     elsif(l_sec_context = 'COMP') then
504       poa_dbi_sutil_pkg.process_parameters(
505         p_param              => p_param,
506         p_view_by            => l_view_by,
507         p_view_by_col_name   => l_view_by_col,
508         p_view_by_value      => l_view_by_value,
509         p_comparison_type    => l_comparison_type,
510         p_xtd                => l_xtd,
511         p_as_of_date         => l_as_of_date,
512         p_prev_as_of_date    => l_prev_as_of_date,
513         p_cur_suffix         => l_cur_suffix,
514         p_nested_pattern     => l_nested_pattern,
515         p_where_clause       => l_where_clause,
516         p_mv                 => l_mv,
517         p_join_tbl           => l_join_tbl,
518         p_in_join_tbl        => l_in_join_tbl,
519         x_custom_output      => x_custom_output,
520         p_trend              => 'N',
521         p_func_area          => 'PO',
522         p_version            => '8.0',
523         p_role               => 'COM',
524         p_mv_set             => 'PQCA');
525 
526       poa_dbi_util_pkg.add_column(l_col_tbl, 'pbpcqco_amt_' || l_cur_suffix, 'benchmark_amt','Y',poa_dbi_util_pkg.PREV_PREV);
527       poa_dbi_util_pkg.add_column(l_col_tbl, 'pbcqco_amt_' || l_cur_suffix, 'fallback_amt','Y',poa_dbi_util_pkg.PREV_PREV);
528       poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'amt','Y');
529 
530       l_prev_prev_as_of_date := poa_dbi_calendar_pkg.previous_period_asof_date(l_prev_as_of_date, l_xtd, l_comparison_type);
531 
532       l_asof_year := fii_time_api.ent_cyr_start(l_as_of_date);
533       l_prev_asof_year := fii_time_api.ent_cyr_start(l_prev_as_of_date);
534       l_prev_prev_asof_year := fii_time_api.ent_cyr_start(l_prev_prev_as_of_date);
535 
536       /*check if we can get everything from aggregated mv*/
537       l_use_only_agg_mv := 'Y';
538       for i in 1..l_in_join_tbl.count loop
539         if(l_in_join_tbl(i).table_alias = 'com' or l_in_join_tbl(i).table_alias = 'cc') then
540           if(l_in_join_tbl(i).aggregated_flag = 'N')then
541             l_use_only_agg_mv := 'N';
542           end if;
543         end if;
544       end loop;
545 
546       if(l_use_only_agg_mv = 'N') then
547         poa_dbi_sutil_pkg.process_parameters(
548           p_param              => p_param,
549           p_view_by            => l_view_by,
550           p_view_by_col_name   => l_view_by_col,
551           p_view_by_value      => l_view_by_value,
552           p_comparison_type    => l_comparison_type,
553           p_xtd                => l_xtd,
554           p_as_of_date         => l_as_of_date,
555           p_prev_as_of_date    => l_prev_as_of_date,
556           p_cur_suffix         => l_cur_suffix,
557           p_nested_pattern     => l_nested_pattern,
558           p_where_clause       => l_where_clause2,
559           p_mv                 => l_mv2,
560           p_join_tbl           => l_join_tbl,
561           p_in_join_tbl        => l_in_join_tbl2,
562           x_custom_output      => x_custom_output,
563           p_trend              => 'N',
564           p_func_area          => 'PO',
565           p_version            => '8.0',
566           p_role               => 'COM',
567           p_mv_set             => 'PQCB');
568 
569         l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
570         l_mv_tbl.extend;
571         l_mv_tbl(1).mv_name := l_mv;
572         l_mv_tbl(1).mv_col := l_col_tbl;
573         l_mv_tbl(1).mv_where := l_where_clause;
574         l_mv_tbl(1).in_join_tbls := l_in_join_tbl;
575         l_mv_tbl(1).use_grp_id := 'N';
576 
577         l_mv_tbl.extend;
578         l_mv_tbl(2).mv_name := l_mv2;
579         l_mv_tbl(2).mv_col := l_col_tbl;
580         l_mv_tbl(2).mv_where := l_where_clause2;
581         l_mv_tbl(2).in_join_tbls := l_in_join_tbl2;
582         l_mv_tbl(2).use_grp_id := 'N';
583         l_query := get_kpi_sel_clause(l_view_by
584                                      ,l_view_by_col
585                                      ,l_url
586                                      ,l_asof_year - l_prev_asof_year
587                                      ,l_prev_asof_year-l_prev_prev_asof_year);
588 
589         l_query := l_query || ' from ( ' ||
590                      poa_dbi_template_pkg.union_all_status_sql(
591                        p_mv              => l_mv_tbl,
592                        p_join_tables     => l_join_tbl,
593                        p_use_windowing   => 'Y',
594                        p_paren_count     => 3,
595                        p_generate_viewby => 'Y',
596                        p_diff_measures   => 'N');
597       else
598         l_query := get_kpi_sel_clause(l_view_by
599                                      ,l_view_by_col
600                                      ,l_url
601                                      ,l_asof_year - l_prev_asof_year
602                                      ,l_prev_asof_year-l_prev_prev_asof_year);
603 
604         l_query := l_query || ' from ' ||
605                      poa_dbi_template_pkg.status_sql(
606                        p_fact_name      => l_mv,
607                        p_where_clause   => l_where_clause,
608                        p_join_tables    => l_join_tbl,
609                        p_use_windowing  => 'Y',
610                        p_col_name       => l_col_tbl,
611                        p_use_grpid      => 'N',
612                        p_in_join_tables => l_in_join_tbl);
613       end if; /* l_use_only_agg_mv = 'N' */
614     end if; /* l_sec_context = 'OU' or l_sec_context = 'OU/COM' */
615     x_custom_sql := l_query;
616 
617     l_custom_rec.attribute_name := '&PREV_PREV_DATE';
618     l_custom_rec.attribute_value := TO_CHAR(l_prev_prev_as_of_date, 'DD/MM/YYYY');
619     l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
620     l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
621     x_custom_output.EXTEND;
622     x_custom_output(x_custom_output.COUNT) := l_custom_rec;
623   end kpi_sql;
624 
625 FUNCTION get_kpi_sel_clause(p_view_by_dim   IN VARCHAR2
626                            ,p_view_by_col   IN VARCHAR2
627                            ,p_url           IN VARCHAR2
628                            ,p_sameyear      IN NUMBER
629                            ,p_prev_sameyear IN NUMBER) RETURN VARCHAR2
630 IS
631   l_sel_clause VARCHAR2(4000);
632 BEGIN
633   l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim, 'PO', '6.0') ;
634   l_sel_clause := l_sel_clause || '
635   oset.POA_MEASURE1 POA_MEASURE1,
636   oset.POA_MEASURE3 POA_MEASURE3,
637   oset.POA_MEASURE5 POA_MEASURE5,
638   oset.POA_MEASURE6 POA_MEASURE6,
639   oset.POA_MEASURE2 POA_MEASURE2,
640   oset.POA_MEASURE4 POA_MEASURE4,
641   oset.POA_MEASURE8 POA_MEASURE8,
642   oset.POA_MEASURE9 POA_MEASURE9
643 
644   from
645    (select (rank() over
646        ( &ORDER_BY_CLAUSE nulls last, ' || p_view_by_col || ')) - 1 rnk,'
647        || p_view_by_col;
648 
649   l_sel_clause := l_sel_clause || ',
650        POA_MEASURE1,  -- Price Savings Amount
651        POA_MEASURE3,  -- Prior Price Savings Amount
652        POA_MEASURE5,  -- Total Price Savings Amount
653        POA_MEASURE6,  -- Total Prior Price Savings Amount
654        POA_MEASURE2,  -- Qty Savings @ Benchmark
655        POA_MEASURE4,  -- Prior Qty Savings @ Benchmark
656        POA_MEASURE8,  -- Total Qty Savings @ Benchmark
657        POA_MEASURE9   -- Total Prior Qty Savings @ Benchmark
658     from
659     (select ' || p_view_by_col || ',
660             ' || p_view_by_col || ' VIEWBY,';
661 
662 
663   if (p_sameyear = 0)
664   then
665    l_sel_clause := l_sel_clause || '
666    nvl(c_benchmark_amt,0) - nvl(c_amt,0) POA_MEASURE1,
667    nvl(p_benchmark_amt,0) - nvl(p_amt,0) POA_MEASURE3,
668    nvl(c_benchmark_amt_total,0) - nvl(c_amt_total,0) POA_MEASURE5,
669    nvl(p_benchmark_amt_total,0) - nvl(p_amt_total,0) POA_MEASURE6,
670    nvl(c_benchmark_amt,0) - nvl(p_benchmark_amt, 0) POA_MEASURE2,
671    nvl(c_benchmark_amt_total,0) - Nvl(p_benchmark_amt_total, 0) POA_MEASURE8,';
672   else
673    l_sel_clause := l_sel_clause || '
674    nvl(c_benchmark_amt,0) - nvl(c_amt,0) POA_MEASURE1,
675    nvl(p_benchmark_amt,0) - nvl(p_amt,0) POA_MEASURE3,
676    nvl(c_benchmark_amt_total,0) - nvl(c_amt_total,0) POA_MEASURE5,
677    nvl(p_benchmark_amt_total,0) - nvl(p_amt_total,0) POA_MEASURE6,
678    nvl(c_benchmark_amt,0) - Nvl(p_fallback_amt, 0) POA_MEASURE2,
679    nvl(c_benchmark_amt_total,0) - Nvl(p_fallback_amt_total, 0) POA_MEASURE8,';
680   end if;
681 
682   if(p_prev_sameyear = 0)
683   then
684    l_sel_clause := l_sel_clause || '
685    nvl(p_benchmark_amt,0) - Nvl(p2_benchmark_amt,0) POA_MEASURE4,
686    nvl(p_benchmark_amt_total,0) - Nvl(p2_benchmark_amt_total,0) POA_MEASURE9
687    ';
688   else
689    l_sel_clause := l_sel_clause || '
690    nvl(p_benchmark_amt,0) - Nvl(p2_fallback_amt,0) POA_MEASURE4,
691    nvl(p_benchmark_amt_total,0) - Nvl(p2_fallback_amt_total,0) POA_MEASURE9
692    ';
693   end if;
694 
695   return l_sel_clause;
696 
697 END GET_KPI_SEL_CLAUSE;
698 
699 
700 
701 
702 /*
703 FUNCTION get_kpi_sel_clause(p_view_by_dim   IN VARCHAR2
704                            ,p_view_by_col   IN VARCHAR2
705                            ,p_url           IN VARCHAR2
706                            ,p_sameyear      IN NUMBER
707                            ,p_prev_sameyear IN NUMBER) RETURN VARCHAR2
708 IS
709   l_sel_clause VARCHAR2(4000);
710 BEGIN
711   l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim, 'PO', '6.0') ;
712   l_sel_clause := l_sel_clause || '
713   oset.POA_MEASURE1 POA_MEASURE1,
714   oset.POA_MEASURE3 POA_MEASURE3,
715   oset.POA_MEASURE5 POA_MEASURE5,
716   oset.POA_MEASURE6 POA_MEASURE6,
717   oset.POA_MEASURE2 POA_MEASURE2,
718   oset.POA_MEASURE4 POA_MEASURE4,
719   oset.POA_MEASURE8 POA_MEASURE8,
720   oset.POA_MEASURE9 POA_MEASURE9
721   from
722    (select (rank() over
723        ( &ORDER_BY_CLAUSE nulls last, ' || p_view_by_col || ')) - 1 rnk,'
724        || p_view_by_col;
725 
726   l_sel_clause := l_sel_clause || ',
727        POA_MEASURE1,    --Price Savings Amount
728        POA_MEASURE3,    --Prior Price Savings Amount
729        POA_MEASURE5,    --Total Price Savings Amount
730        POA_MEASURE6,    --Total Prior Price Savings Amount
731        POA_MEASURE2,    --Qty Savings at Benchmark
732        POA_MEASURE4,    --Prior Qty Savings at Benchmark
733        POA_MEASURE8,    --Total Qty Savings at Benchmark
734        POA_MEASURE9     --Total Prior Qty Savings at Benchmark
735     from
736     (select ' || p_view_by_col || ',
737             ' || p_view_by_col || ' VIEWBY,';
738 
739 
740   if (p_sameyear = 0)
741   then
742    l_sel_clause := l_sel_clause || '
743    nvl(c_benchmark_amt,0) - nvl(c_amt,0) POA_MEASURE1,
744    nvl(p_benchmark_amt,0) - nvl(p_amt,0) POA_MEASURE3,
745    nvl(c_benchmark_amt_total,0) - nvl(c_amt_total,0) POA_MEASURE5,
746    nvl(p_benchmark_amt_total,0) - nvl(p_amt_total,0) POA_MEASURE6,
747    nvl(c_benchmark_amt,0) - nvl(p_benchmark_amt, 0) POA_MEASURE2,
748    nvl(c_benchmark_amt_total,0) - nvl(p_benchmark_amt_total,0) POA_MEASURE8,';
749   else
750    l_sel_clause := l_sel_clause || '
751    nvl(c_benchmark_amt,0) - nvl(c_amt,0) POA_MEASURE1,
752    nvl(p_benchmark_amt,0) - nvl(p_amt,0) POA_MEASURE3,
753    nvl(c_benchmark_amt_total,0) - nvl(c_amt_total,0) POA_MEASURE5,
754    nvl(p_benchmark_amt_total,0) - nvl(p_amt_total,0) POA_MEASURE6,
755    nvl(c_benchmark_amt,0) - Nvl(p_fallback_amt, 0) POA_MEASURE2,
756    nvl(c_benchmark_amt_total,0) - nvl(p_fallback_amt_total,0) POA_MEASURE8,';
757   end if;
758 
759   if(p_prev_sameyear = 0)
760   then
761    l_sel_clause := l_sel_clause || '
762    Nvl(p_benchmark_amt,0) - Nvl(p2_benchmark_amt,0) POA_MEASURE4,
763    nvl(p_benchmark_amt_total,0) - nvl(p2_benchmark_amt_total,0) POA_MEASURE9
764    ';
765   else
766    l_sel_clause := l_sel_clause || '
767    Nvl(p_benchmark_amt,0) - Nvl(p2_fallback_amt,0) POA_MEASURE4,
768    nvl(p_benchmark_amt_total,0) - nvl(p2_fallback_amt_total,0) POA_MEASURE9,
769    ';
770   end if;
771 
772   return l_sel_clause;
773 
774 END GET_KPI_SEL_CLAUSE;
775 */
776 
777 FUNCTION get_dtl_filter_where return VARCHAR2
778   IS
779     l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
780   BEGIN
781     l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
782     l_col_tbl.extend;
783     l_col_tbl(1) := 'POA_MEASURE1';
784     l_col_tbl.extend;
785     l_col_tbl(2) := 'POA_MEASURE3';
786     l_col_tbl.extend;
787     l_col_tbl(3) := 'POA_MEASURE5';
788     l_col_tbl.extend;
789     l_col_tbl(4) := 'POA_MEASURE6';
790 
791     return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
792 
793   END;
794 
795 
796 
797   PROCEDURE dtl_rpt_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
798                       x_custom_sql  OUT NOCOPY VARCHAR2,
799                       x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
800   IS
801     l_query varchar2(8000);
802     l_cur_suffix varchar2(2);
803     l_where_clause varchar2(2000);
804     l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
805     l_in_join_tables    VARCHAR2(1000) := '';
806     l_filter_where VARCHAR2(1000);
807     l_sec_context varchar2(10);
808   BEGIN
809     x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
810     l_filter_where := get_dtl_filter_where;
811 
812     l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
813     if(l_sec_context = 'OU' or l_sec_context = 'OU/COM') then
814       poa_dbi_sutil_pkg.drill_process_parameters(
815         p_param,
816         l_cur_suffix,
817         l_where_clause,
818         l_in_join_tbl,
819         'PO',
820         '6.0',
821         'COM',
822         'PQC');
823     else
824       poa_dbi_sutil_pkg.drill_process_parameters(
825         p_param,
826         l_cur_suffix,
827         l_where_clause,
828         l_in_join_tbl,
829         'PO',
830         '8.0',
831         'COM',
832         'PQCB');
833     end if;
834 
835     IF(l_in_join_tbl is not null) then
836       FOR i in 1 .. l_in_join_tbl.COUNT LOOP
837         l_in_join_tables := l_in_join_tables || ', ' ||  l_in_join_tbl(i).table_name || ' ' || l_in_join_tbl(i).table_alias;
838       END LOOP;
839     END IF;
840 
841     l_query :=
842     'select poh.segment1 || decode(rel.release_num, null, null, ''-'' || rel.release_num) POA_ATTRIBUTE1, --PO Number
843      pol.line_num POA_ATTRIBUTE2,   --Line Number
844      poorg.name POA_ATTRIBUTE3,    --Operating Unit
845      item.value POA_ATTRIBUTE4,    --Item
846      uom.description POA_ATTRIBUTE5,  --UOM
847      POA_MEASURE1,  --Quantity
848      POA_MEASURE2,  --Benchmark Price
849      POA_MEASURE3,  --PO Price
850      POA_MEASURE4,  --Price Difference
851      POA_MEASURE5,  --Price Savings Amount
852      POA_MEASURE6,  --Current Amount At PO Price
853      POA_MEASURE7,  --Price Saving Total
854      POA_MEASURE8,  --Cur amt po price total
855      i.po_header_id POA_ATTRIBUTE6,  -- Header_id (hidden)
856      i.po_release_id POA_ATTRIBUTE7  -- release_id (hidden)
857      from
858      ( select (rank() over (&ORDER_BY_CLAUSE nulls last,
859          po_header_id, po_line_id, po_item_id, base_uom,
860          po_release_id, org_id, POA_MEASURE2, POA_MEASURE3)) - 1 rnk,
861        po_header_id,
862        po_line_id,
863        po_item_id,
864        org_id,
865        base_uom,
866        po_release_id,
867        decode(base_uom,null,to_number(null),nvl(POA_MEASURE1,0)) POA_MEASURE1,
868        POA_MEASURE2,
869        POA_MEASURE3,
870        POA_MEASURE4,
871        nvl(POA_MEASURE5,0) POA_MEASURE5,
872        nvl(POA_MEASURE6,0) POA_MEASURE6,
873        nvl(POA_MEASURE7,0) POA_MEASURE7,
874        nvl(POA_MEASURE8,0) POA_MEASURE8
875        from
876        ( select f.po_header_id,
877          f.po_line_id,
878          f.po_item_id,
879          f.base_uom,
880          f.po_release_id,
881          f.org_id,
882          sum(f.quantity) POA_MEASURE1,
883          nvl(f.pip_amt_' || l_cur_suffix || '/f.pip_quantity, cip.purchase_amt_' || l_cur_suffix || '/cip.quantity) POA_MEASURE2,
884          f.purchase_amt_' || l_cur_suffix || '/f.quantity POA_MEASURE3,
885          ((nvl(f.pip_amt_' || l_cur_suffix || '/f.pip_quantity, cip.purchase_amt_' || l_cur_suffix || '/cip.quantity))-(
886          f.purchase_amt_' || l_cur_suffix || '/f.quantity)) POA_MEASURE4,
887          sum(f.quantity * (nvl(f.pip_amt_' || l_cur_suffix || '/f.pip_quantity, cip.purchase_amt_' || l_cur_suffix || '/cip.quantity) - f.purchase_amt_' || l_cur_suffix || '/f.quantity)) POA_MEASURE5,
888          sum(f.purchase_amt_' || l_cur_suffix || ') POA_MEASURE6,
889          sum(sum(f.quantity * (nvl(f.pip_amt_' || l_cur_suffix || '/f.pip_quantity, cip.purchase_amt_' || l_cur_suffix || '/cip.quantity) - f.purchase_amt_' || l_cur_suffix || '/f.quantity))) over () POA_MEASURE7,
890          sum(sum(f.purchase_amt_' || l_cur_suffix || ')) over () POA_MEASURE8
891          from  poa_bm_item_o_mv cip,
892          ( select /*+ NO_MERGE */ fact.po_header_id,
893            fact.po_line_id,
894            fact.po_item_id,
895            fact.base_uom,
896            fact.po_release_id,
897            fact.org_id,
898            fact.ent_year_id,
899            fact.pip_amt_b,
900            fact.pip_amt_g,
901            fact.pip_amt_sg,
902            fact.purchase_amt_b,
903            fact.purchase_amt_g,
904            fact.purchase_amt_sg,
905            fact.quantity,
906            fact.pip_quantity
907            from poa_pqc_bs_j2_mv fact
908       ' || l_in_join_tables || '
909            where fact.approved_date between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE
910            and fact.complex_work_flag = ''N''
911            and fact.consigned_code <> 1
912            and fact.order_type = ''QUANTITY''
913       ' || l_where_clause;
914     if(l_sec_context = 'COMP')then
915       l_query := l_query || '
916            and fact.company_id = com.child_company_id
917            and fact.cost_center_id = cc.child_cc_id'||fnd_global.newline;
918     end if;
919     l_query :=l_query || '
920          ) f
921          where f.ent_year_id = cip.ent_year_id
922          and   f.org_id = cip.org_id
923          and   f.po_item_id = cip.po_item_id
924          and   f.base_uom = cip.base_uom
925          group by f.po_header_id, f.po_line_id, f.po_item_id, f.base_uom, f.po_release_id, f.org_id,
926          nvl(f.pip_amt_' || (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end) || '/f.pip_quantity, cip.purchase_amt_' || (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end) || '/cip.quantity),
927          nvl(f.pip_amt_b/f.pip_quantity, cip.purchase_amt_b/cip.quantity), f.purchase_amt_' || (case l_cur_suffix when 'b' then 'g' else l_cur_suffix end) || '/f.quantity,  f.purchase_amt_b/f.quantity
928        )
929        where ' || l_filter_where ||'
930      ) i,
931      po_headers_all poh,
932      po_lines_all pol,
933      po_releases_all rel,
934      poa_items_v item,
935      mtl_units_of_measure_vl uom,
936      hr_all_organization_units_vl poorg
937      where i.po_header_id = poh.po_header_id
938      and i.po_line_id = pol.po_line_id
939      and i.po_item_id = item.id
940      and i.base_uom = uom.unit_of_measure
941      and i.org_id = poorg.organization_id
942      and i.po_release_id = rel.po_release_id (+)
943      and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
944      ORDER BY rnk';
945 
946      x_custom_sql := l_query;
947      poa_dbi_util_pkg.get_custom_status_binds(x_custom_output);
948      if(l_sec_context = 'COMP')then
949        poa_dbi_sutil_pkg.bind_com_cc_values(x_custom_output, p_param);
950      end if;
951   end;
952 
953 
954   PROCEDURE trend_sql(p_param             IN          BIS_PMV_PAGE_PARAMETER_TBL
955                    ,x_custom_sql        OUT NOCOPY  VARCHAR2
956                    ,x_custom_output     OUT NOCOPY  BIS_QUERY_ATTRIBUTES_TBL)
957   IS
958     l_query             varchar2(20000);
959     l_view_by           varchar2(120);
960     l_view_by_col       varchar2(120);
961     l_as_of_date        date;
962     l_prev_as_of_date   date;
963     l_xtd               varchar2(10);
964     l_comparison_type   varchar2(1) := 'Y';
965     l_nested_pattern    number;
966     l_cur_suffix        varchar2(2);
967     l_col_tbl           poa_dbi_util_pkg.poa_dbi_col_tbl;
968     l_join_tbl          poa_dbi_util_pkg.poa_dbi_join_tbl;
969     l_in_join_tbl       poa_dbi_util_pkg.poa_dbi_in_join_tbl;
970     l_in_join_tbl2      poa_dbi_util_pkg.poa_dbi_in_join_tbl;
971     l_mv                varchar2(30);
972     l_mv2               varchar2(30);
973     l_where_clause      varchar2(2000);
974     l_where_clause2     varchar2(2000);
975     l_view_by_value     varchar2(100);
976     l_cal_tbl           varchar2(30);
977     l_rec_type          number;
978     l_custom_rec        bis_query_attributes;
979     l_in_join_tables    varchar2(1000) := '';
980     l_in_join_tables2   varchar2(1000) := '';
981     l_adjust1           varchar2(100);
982     l_adjust2           varchar2(100);
983     l_curr_start        date;
984     l_curr_end          date;
985     l_prior_start       date;
986     l_prior_end         date;
987     l_cur_month_start   date;
988     l_cur_month_end     date;
989     l_cur_where_clause  varchar2(2000);
990     l_prev_where_clause varchar2(2000);
991     l_record_type1      number;
992     l_record_type2      number;
993     l_sec_context       varchar2(10);
994     l_use_only_agg_mv   varchar2(1);
995   BEGIN
996 
997     /* sets up calls to  ("fii_msg.get_curr_label") for col labels */
998     /*fii_gl_util_pkg.reset_globals;*/
999     poa_dbi_sutil_pkg.get_parameters(p_param);
1000 
1001     l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
1002     l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
1003 
1004     l_sec_context := poa_dbi_sutil_pkg.get_sec_context(p_param);
1005     if (l_sec_context = 'OU' or l_sec_context = 'OU/COM')then
1006       poa_dbi_sutil_pkg.process_parameters(
1007         p_param              => p_param,
1008         p_view_by            => l_view_by,
1009         p_view_by_col_name   => l_view_by_col,
1010         p_view_by_value      => l_view_by_value,
1011         p_comparison_type    => l_comparison_type,
1012         p_xtd                => l_xtd,
1013         p_as_of_date         => l_as_of_date,
1014         p_prev_as_of_date    => l_prev_as_of_date,
1015         p_cur_suffix         => l_cur_suffix,
1016         p_nested_pattern     => l_nested_pattern,
1017         p_where_clause       => l_where_clause,
1018         p_mv                 => l_mv,
1019         p_join_tbl           => l_join_tbl,
1020         p_in_join_tbl        => l_in_join_tbl,
1021         x_custom_output      => x_custom_output,
1022         p_trend              => 'Y',
1023         p_func_area          => 'PO',
1024         p_version            => '6.0',
1025         p_role               => 'COM',
1026         p_mv_set             => 'PQC');
1027      elsif (l_sec_context = 'COMP') then
1028       poa_dbi_sutil_pkg.process_parameters(
1029         p_param              => p_param,
1030         p_view_by            => l_view_by,
1031         p_view_by_col_name   => l_view_by_col,
1032         p_view_by_value      => l_view_by_value,
1033         p_comparison_type    => l_comparison_type,
1034         p_xtd                => l_xtd,
1035         p_as_of_date         => l_as_of_date,
1036         p_prev_as_of_date    => l_prev_as_of_date,
1037         p_cur_suffix         => l_cur_suffix,
1038         p_nested_pattern     => l_nested_pattern,
1039         p_where_clause       => l_where_clause,
1040         p_mv                 => l_mv,
1041         p_join_tbl           => l_join_tbl,
1042         p_in_join_tbl        => l_in_join_tbl,
1043         x_custom_output      => x_custom_output,
1044         p_trend              => 'Y',
1045         p_func_area          => 'PO',
1046         p_version            => '8.0',
1047         p_role               => 'COM',
1048         p_mv_set             => 'PQCA');
1049 
1050       /*check if we can get everything from aggregated mv*/
1051       l_use_only_agg_mv := 'Y';
1052       for i in 1..l_in_join_tbl.count loop
1053         if(l_in_join_tbl(i).table_alias = 'com' or l_in_join_tbl(i).table_alias = 'cc') then
1054           if(l_in_join_tbl(i).aggregated_flag = 'N')then
1055             l_use_only_agg_mv := 'N';
1056           end if;
1057         end if;
1058       end loop;
1059       if(l_use_only_agg_mv = 'N') then
1060         poa_dbi_sutil_pkg.process_parameters(
1061           p_param              => p_param,
1062           p_view_by            => l_view_by,
1063           p_view_by_col_name   => l_view_by_col,
1064           p_view_by_value      => l_view_by_value,
1065           p_comparison_type    => l_comparison_type,
1066           p_xtd                => l_xtd,
1067           p_as_of_date         => l_as_of_date,
1068           p_prev_as_of_date    => l_prev_as_of_date,
1069           p_cur_suffix         => l_cur_suffix,
1070           p_nested_pattern     => l_nested_pattern,
1071           p_where_clause       => l_where_clause2,
1072           p_mv                 => l_mv2,
1073           p_join_tbl           => l_join_tbl,
1074           p_in_join_tbl        => l_in_join_tbl2,
1075           x_custom_output      => x_custom_output,
1076           p_trend              => 'Y',
1077           p_func_area          => 'PO',
1078           p_version            => '8.0',
1079           p_role               => 'COM',
1080           p_mv_set             => 'PQCB');
1081        end if;
1082      end if;
1083 
1084     IF l_xtd = 'YTD' THEN
1085       l_adjust1     := NULL;
1086       l_adjust2     := NULL;
1087       l_curr_start  := fii_time_api.ent_cyr_start(l_as_of_date);
1088       l_curr_end    := fii_time_api.ent_cyr_end(l_as_of_date);
1089       l_prior_start := fii_time_api.ent_cyr_start(l_prev_as_of_date);
1090       l_prior_end   := fii_time_api.ent_cyr_end(l_prev_as_of_date);
1091       l_record_type1 := 119;
1092       l_record_type2 := 23;
1093     ELSIF l_xtd = 'QTD' THEN
1094       l_adjust1     := ':POA_CURR_START-:POA_CURR_END';
1095       l_adjust2     := ':POA_PRIOR_START-:POA_PRIOR_END';
1096       l_curr_start  := fii_time_api.ent_cqtr_start(l_as_of_date);
1097       l_curr_end    := fii_time_api.ent_cqtr_end(l_as_of_date);
1098       l_prior_start := fii_time_api.ent_cqtr_start(l_prev_as_of_date);
1099       l_prior_end   := fii_time_api.ent_cqtr_end(l_prev_as_of_date);
1100       l_record_type1 := 11;
1101       l_record_type2 := 1;
1102     ELSIF l_xtd = 'MTD' THEN
1103       l_adjust1     := '1';
1104       l_adjust2     := '1';
1105       l_curr_start  := fii_time_api.ent_cper_start(l_as_of_date);
1106       l_curr_end    := fii_time_api.ent_cper_end(l_as_of_date);
1107       l_prior_start := fii_time_api.ent_cper_start(l_prev_as_of_date);
1108       l_prior_end   := fii_time_api.ent_cper_end(l_prev_as_of_date);
1109       l_record_type1 := 11;
1110       l_record_type2 := 1;
1111     ELSE -- l_period_type = 'FII_TIME_WEEK'
1112       l_adjust1     := '1';
1113       l_adjust2     := '1';
1114       l_curr_start  := fii_time_api.cwk_start(l_as_of_date);
1115       l_curr_end    := fii_time_api.cwk_end(l_as_of_date);
1116       l_prior_start := fii_time_api.cwk_start(l_prev_as_of_date);
1117       l_prior_end   := fii_time_api.cwk_end(l_prev_as_of_date);
1118       l_record_type1 := 11;
1119       l_record_type2 := 1;
1120     END IF;
1121 
1122     IF(l_in_join_tbl is not null) then
1123       FOR i in 1 .. l_in_join_tbl.COUNT LOOP
1124         l_in_join_tables := l_in_join_tables || ', ' ||  l_in_join_tbl(i).table_name || ' ' || l_in_join_tbl(i).table_alias;
1125       END LOOP;
1126     END IF;
1127 
1128     if(l_in_join_tbl2 is not null) then
1129       for i in 1 .. l_in_join_tbl2.count loop
1130         l_in_join_tables2 := l_in_join_tables2 || ', ' ||  l_in_join_tbl2(i).table_name || ' ' || l_in_join_tbl2(i).table_alias;
1131       end loop;
1132     end if;
1133 
1134     l_cur_where_clause :=
1135       '(
1136           &BIS_CURRENT_ASOF_DATE
1137           BETWEEN cal.start_date AND cal.end_date
1138           OR cal.end_date BETWEEN :POA_CURR_START
1139           AND &BIS_CURRENT_ASOF_DATE
1140       )
1141       AND n.report_date BETWEEN  :POA_CURR_START
1142       AND &BIS_CURRENT_ASOF_DATE
1143       AND n.report_date = least(cal.end_date, &BIS_CURRENT_ASOF_DATE
1144       )
1145       AND
1146       (
1147           CASE
1148               WHEN cal.start_date <  :POA_CURR_START
1149               THEN bitand(n.record_type_id, ' || l_record_type1 || ')
1150               ELSE bitand(n.record_type_id, ' || l_record_type2 || ')
1151           END
1152       )
1153       = n.record_type_id
1154       AND fact.time_id = n.time_id
1155     ';
1156 
1157     l_prev_where_clause :=
1158 '    (
1159           :POA_PRIOR_END
1160           BETWEEN cal.start_date AND cal.end_date
1161           OR cal.end_date BETWEEN :POA_PRIOR_START
1162           AND :POA_PRIOR_END
1163       )
1164       AND n.report_date BETWEEN :POA_PRIOR_START
1165       AND :POA_PRIOR_END
1166       AND n.report_date = least(cal.end_date, :POA_PRIOR_END
1167       )
1168       AND
1169       (
1170           CASE
1171               WHEN cal.start_date < :POA_PRIOR_START
1172               THEN bitand(n.record_type_id, ' || l_record_type1 ||')
1173               ELSE bitand(n.record_type_id, ' || l_record_type2 ||')
1174           END
1175       )
1176       = n.record_type_id
1177       AND fact.time_id = n.time_id
1178 ';
1179 
1180     if(l_xtd='YTD') then
1181       l_query := '
1182     select VIEWBY,
1183     CASE WHEN start_date > &BIS_CURRENT_ASOF_DATE
1184     THEN to_number(NULL)
1185     ELSE c_cumulative_ps_amt END    POA_MEASURE1,
1186     p_cumulative_ps_amt POA_MEASURE2,
1187     CASE WHEN start_date >= &BIS_CURRENT_ASOF_DATE
1188     THEN to_number(NULL)
1189     ELSE c_ps_amt END    POA_MEASURE3,
1190     p_ps_amt POA_MEASURE4
1191     from
1192     ( select month_name VIEWBY,
1193       sum(ent_period_id) period_id,
1194       max(start_date) start_date,
1195       sum(p_cumulative_ps_amt)    p_cumulative_ps_amt,
1196       sum(c_cumulative_ps_amt)   c_cumulative_ps_amt,
1197       sum(p_ps_amt)    p_ps_amt,
1198       sum(c_ps_amt)   c_ps_amt
1199       from
1200       (
1201         ( select
1202           substr(cal.name,1,3) month_name,
1203           ent_period_id,
1204           cal.start_date,
1205           c_ps_amt,
1206           sum(nvl(c_ps_amt,0)) over ( ORDER BY ent_period_id ROWS UNBOUNDED PRECEDING) c_cumulative_ps_amt,
1207           null p_ps_amt,
1208           null p_cumulative_ps_amt
1209           from
1210           ( SELECT
1211             cal.start_date,
1212             sum(pbpcqco_amt_' || l_cur_suffix || ' - purchase_amt_' || l_cur_suffix || ') c_ps_amt
1213             FROM ' || l_mv || ' fact,
1214             FII_TIME_ENT_PERIOD   cal,
1215             fii_time_rpt_struct_v n
1216             ' || l_in_join_tables || '
1217             WHERE
1218             ' || l_cur_where_clause || l_where_clause || '
1219             GROUP BY  cal.start_date,cal.end_date
1220           ) iset,
1221           FII_TIME_ENT_PERIOD cal
1222           where cal.start_date = iset.start_date(+)
1223           AND cal.start_date <= :POA_CURR_END
1224           AND  cal.end_date   >= :POA_CURR_START
1225         )
1226         UNION ALL
1227         ( select
1228           substr(cal.name,1,3) month_name,
1229           null ent_period_id,
1230           null start_date,
1231           null c_ps_amt,
1232           null c_cumulative_ps_amt,
1233           p_ps_amt,
1234           sum(nvl(p_ps_amt,0)) over ( ORDER BY ent_period_id ROWS UNBOUNDED PRECEDING) p_cumulative_ps_amt
1235           from
1236           ( SELECT
1237             cal.start_date,
1238             sum(pbpcqco_amt_' || l_cur_suffix || ' - purchase_amt_' || l_cur_suffix || ') p_ps_amt
1239             FROM ' || l_mv || ' fact,
1240             FII_TIME_ENT_PERIOD cal,
1241             fii_time_rpt_struct_v n
1242             ' || l_in_join_tables || '
1243             WHERE
1244             ' ||    l_prev_where_clause || l_where_clause || '
1245             GROUP BY cal.start_date,cal.end_date
1246           ) iset,
1247           FII_TIME_ENT_PERIOD cal
1248           where cal.start_date = iset.start_date(+)
1249           AND cal.start_date <= :POA_PRIOR_END
1250           AND cal.end_date   >= :POA_PRIOR_START
1251         )';
1252       if(l_use_only_agg_mv = 'N') then
1253         l_query := l_query ||'
1254        UNION ALL
1255        ( select
1256           substr(cal.name,1,3) month_name,
1257           ent_period_id,
1258           cal.start_date,
1259           c_ps_amt,
1260           sum(nvl(c_ps_amt,0)) over ( ORDER BY ent_period_id ROWS UNBOUNDED PRECEDING) c_cumulative_ps_amt,
1261           null p_ps_amt,
1262           null p_cumulative_ps_amt
1263           from
1264           ( SELECT
1265             cal.start_date,
1266             sum(pbpcqco_amt_' || l_cur_suffix || ' - purchase_amt_' || l_cur_suffix || ') c_ps_amt
1267             FROM ' || l_mv2 || ' fact,
1268             FII_TIME_ENT_PERIOD   cal,
1269             fii_time_rpt_struct_v n
1270             ' || l_in_join_tables2 || '
1271             WHERE
1272             ' || l_cur_where_clause || l_where_clause2 || '
1273             GROUP BY  cal.start_date,cal.end_date
1274           ) iset,
1275           FII_TIME_ENT_PERIOD cal
1276           where cal.start_date = iset.start_date(+)
1277           AND cal.start_date <= :POA_CURR_END
1278           AND  cal.end_date   >= :POA_CURR_START
1279         )
1280         UNION ALL
1281         ( select
1282           substr(cal.name,1,3) month_name,
1283           null ent_period_id,
1284           null start_date,
1285           null c_ps_amt,
1286           null c_cumulative_ps_amt,
1287           p_ps_amt,
1288           sum(nvl(p_ps_amt,0)) over ( ORDER BY ent_period_id ROWS UNBOUNDED PRECEDING) p_cumulative_ps_amt
1289           from
1290           ( SELECT
1291             cal.start_date,
1292             sum(pbpcqco_amt_' || l_cur_suffix || ' - purchase_amt_' || l_cur_suffix || ') p_ps_amt
1293             FROM ' || l_mv2 || ' fact,
1294             FII_TIME_ENT_PERIOD cal,
1295             fii_time_rpt_struct_v n
1296             ' || l_in_join_tables2 || '
1297             WHERE
1298             ' ||    l_prev_where_clause || l_where_clause2 || '
1299             GROUP BY cal.start_date,cal.end_date
1300           ) iset,
1301           FII_TIME_ENT_PERIOD cal
1302           where cal.start_date = iset.start_date(+)
1303           AND cal.start_date <= :POA_PRIOR_END
1304           AND cal.end_date   >= :POA_PRIOR_START
1305         )';
1306       end if; /* l_use_only_agg_mv = 'N' */
1307         l_query := l_query ||
1308      ')
1309       group by month_name
1310       order by period_id
1311     )';
1312     ELSE --Quarter Week or Month
1313       l_query := '
1314     select days VIEWBY,
1315     sum(DECODE(SIGN(report_date - &BIS_CURRENT_ASOF_DATE),
1316     1, NULL,
1317     decode(SIGN(:POA_CURR_START-report_date),1,NULL,c_cumulative_ps_amt)))
1318     POA_MEASURE1,
1319     SUM(DECODE(SIGN(report_date - :POA_PRIOR_END),
1320     1, NULL, p_cumulative_ps_amt))     POA_MEASURE2,
1321     sum(DECODE(SIGN(report_date - &BIS_CURRENT_ASOF_DATE),
1322     1, NULL,
1323     decode(SIGN(:POA_CURR_START-report_date),1,NULL,nvl(c_ps_amt,0))))
1324     POA_MEASURE3,
1325     SUM(DECODE(SIGN(report_date - :POA_PRIOR_END),
1326     1, NULL, nvl(p_ps_amt,0)))     POA_MEASURE4
1327     from
1328     (
1329       ( select
1330         cal.report_date -  :POA_CURR_START + to_number('
1331         || l_adjust1 || ') days,
1332         report_date,
1333         c_ps_amt,
1334         sum(nvl(c_ps_amt,0)) over (
1335         ORDER BY
1336         (cal.report_date -  :POA_CURR_START + to_number('
1337         || l_adjust1 || '))
1338         ROWS UNBOUNDED PRECEDING) c_cumulative_ps_amt,
1339         null p_ps_amt,
1340         null p_cumulative_ps_amt
1341         from
1342         ( SELECT
1343           cal.start_date,
1344           cal.end_date,
1345           sum(pbpcqco_amt_' || l_cur_suffix || ' - purchase_amt_' || l_cur_suffix || ') c_ps_amt
1346           FROM ' || l_mv || ' fact,
1347           fii_time_day cal,
1348           fii_time_rpt_struct_v n
1349           ' || l_in_join_tables || '
1350           WHERE
1351           ' || l_cur_where_clause || l_where_clause || '
1352           GROUP BY  cal.start_date,cal.end_date
1353         ) iset, fii_time_day cal
1354         where cal.start_date = iset.start_date(+)
1355         AND cal.start_date <= :POA_CURR_END
1356         AND cal.end_date   >= :POA_CURR_START
1357       )
1358       UNION ALL
1359       ( select
1360         cal.report_date -  :POA_PRIOR_START + to_number('|| l_adjust2 || ') days,
1361         report_date,
1362         null c_ps_amt,
1363         null c_cumulative_ps_amt,
1364         p_ps_amt,
1365         sum(nvl(p_ps_amt,0)) over (
1366         ORDER BY
1367            cal.report_date - :POA_PRIOR_START + to_number('||l_adjust2||')
1368         ROWS UNBOUNDED PRECEDING) p_cumulative_ps_amt
1369         from
1370         ( SELECT
1371           cal.start_date,
1372           cal.end_date,
1373           sum(pbpcqco_amt_' || l_cur_suffix || ' - purchase_amt_' || l_cur_suffix || ') p_ps_amt
1374           FROM ' || l_mv || ' fact,
1375           fii_time_day cal,
1376           fii_time_rpt_struct_v n
1377           ' || l_in_join_tables || '
1378           WHERE
1379           ' || l_prev_where_clause || l_where_clause || '
1380           GROUP BY cal.start_date,cal.end_date
1381         ) iset, fii_time_day cal
1382         where cal.start_date = iset.start_date(+)
1383         AND cal.start_date <= :POA_PRIOR_END
1384         AND cal.end_date   >= :POA_PRIOR_START
1385       )';
1386       if(l_use_only_agg_mv = 'N') then
1387         l_query := l_query ||'
1388      UNION ALL
1389      ( select
1390         cal.report_date -  :POA_CURR_START + to_number('
1391         || l_adjust1 || ') days,
1392         report_date,
1393         c_ps_amt,
1394         sum(nvl(c_ps_amt,0)) over (
1395         ORDER BY
1396         (cal.report_date -  :POA_CURR_START + to_number('
1397         || l_adjust1 || '))
1398         ROWS UNBOUNDED PRECEDING) c_cumulative_ps_amt,
1399         null p_ps_amt,
1400         null p_cumulative_ps_amt
1401         from
1402         ( SELECT
1403           cal.start_date,
1404           cal.end_date,
1405           sum(pbpcqco_amt_' || l_cur_suffix || ' - purchase_amt_' || l_cur_suffix || ') c_ps_amt
1406           FROM ' || l_mv2 || ' fact,
1407           fii_time_day cal,
1408           fii_time_rpt_struct_v n
1409           ' || l_in_join_tables2 || '
1410           WHERE
1411           ' || l_cur_where_clause || l_where_clause2 || '
1412           GROUP BY  cal.start_date,cal.end_date
1413         ) iset, fii_time_day cal
1414         where cal.start_date = iset.start_date(+)
1415         AND cal.start_date <= :POA_CURR_END
1416         AND cal.end_date   >= :POA_CURR_START
1417       )
1418       UNION ALL
1419       ( select
1420         cal.report_date -  :POA_PRIOR_START + to_number('|| l_adjust2 || ') days,
1421         report_date,
1422         null c_ps_amt,
1423         null c_cumulative_ps_amt,
1424         p_ps_amt,
1425         sum(nvl(p_ps_amt,0)) over (
1426         ORDER BY
1427            cal.report_date - :POA_PRIOR_START + to_number('||l_adjust2||')
1428         ROWS UNBOUNDED PRECEDING) p_cumulative_ps_amt
1429         from
1430         ( SELECT
1431           cal.start_date,
1432           cal.end_date,
1433           sum(pbpcqco_amt_' || l_cur_suffix || ' - purchase_amt_' || l_cur_suffix || ') p_ps_amt
1434           FROM ' || l_mv2 || ' fact,
1435           fii_time_day cal,
1436           fii_time_rpt_struct_v n
1437           ' || l_in_join_tables2 || '
1438           WHERE
1439           ' || l_prev_where_clause || l_where_clause2 || '
1440           GROUP BY cal.start_date,cal.end_date
1441         ) iset, fii_time_day cal
1442         where cal.start_date = iset.start_date(+)
1443         AND cal.start_date <= :POA_PRIOR_END
1444         AND cal.end_date   >= :POA_PRIOR_START
1445       )';
1446       end if; /* l_use_only_agg_mv = 'N' */
1447    l_query := l_query || ')
1448     group by days
1449     order by days';
1450     end if; /* l_xtd='YTD' */
1451     x_custom_sql := l_query;
1452 
1453     l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1454 
1455     l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
1456     l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
1457     if(l_xtd = 'YTD') then
1458       l_custom_rec.attribute_value := 'TIME+FII_TIME_ENT_PERIOD';
1459     elsif(l_xtd = 'QTD') then
1460       l_custom_rec.attribute_value := 'TIME+FII_TIME_DAY';
1461     elsif(l_xtd = 'MTD') then
1462       l_custom_rec.attribute_value := 'TIME+FII_TIME_DAY';
1463     else
1464       l_custom_rec.attribute_value := 'TIME+FII_TIME_DAY';
1465     end if;
1466     x_custom_output.EXTEND;
1467     x_custom_output(x_custom_output.COUNT) := l_custom_rec;
1468 
1469     l_custom_rec.attribute_name := '&FND_USER_ID';
1470     l_custom_rec.attribute_value := poa_dbi_util_pkg.get_fnd_user_profile;
1471     l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1472     l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1473     x_custom_output.EXTEND;
1474     x_custom_output(x_custom_output.COUNT) := l_custom_rec;
1475 
1476     l_custom_rec.attribute_name := ':POA_CURR_START';
1477     l_custom_rec.attribute_value := to_char(l_curr_start,'DD-MM-YYYY');
1478     l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1479     l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
1480     x_custom_output.extend;
1481     x_custom_output(x_custom_output.COUNT) := l_custom_rec;
1482 
1483     l_custom_rec.attribute_name := ':POA_CURR_END';
1484     l_custom_rec.attribute_value := to_char(l_curr_end,'DD-MM-YYYY');
1485     l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1486     l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
1487     x_custom_output.extend;
1488     x_custom_output(x_custom_output.COUNT) := l_custom_rec;
1489 
1490     l_custom_rec.attribute_name := ':POA_PRIOR_START';
1491     l_custom_rec.attribute_value := to_char(l_prior_start,'DD-MM-YYYY');
1492     l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1493     l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
1494     x_custom_output.extend;
1495     x_custom_output(x_custom_output.COUNT) := l_custom_rec;
1496 
1497     l_custom_rec.attribute_name := ':POA_PRIOR_END';
1498     l_custom_rec.attribute_value := to_char(l_prior_end,'DD-MM-YYYY');
1499     l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
1500     l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
1501     x_custom_output.extend;
1502     x_custom_output(x_custom_output.COUNT) := l_custom_rec;
1503 
1504   END trend_sql;
1505 
1506 END POA_DBI_PQC_PKG;