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.OPI_DBI_PRD_CST_MARGIN_PKG

Source


1 PACKAGE BODY opi_dbi_prd_cst_margin_pkg AS
2 /*$Header: OPIDRPPGMB.pls 120.0 2005/05/24 18:21:01 appldev noship $ */
3 
4 /*++++++++++++++++++++++++++++++++++++++++*/
5 /* Function and procedure declarations in this file but not in spec*/
6 /*++++++++++++++++++++++++++++++++++++++++*/
7 
8 
9 FUNCTION get_status_sel_clause(p_view_by_dim IN VARCHAR2,
10                                p_join_tbl IN
11                                poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
12     return VARCHAR2;
13 
14 FUNCTION get_trend_sel_clause(p_view_by_dim IN VARCHAR2, p_url IN VARCHAR2)
15     return VARCHAR2;
16 
17 PROCEDURE get_qty_columns (p_dim_name VARCHAR2,
18                            x_description OUT NOCOPY VARCHAR2,
19                            x_uom OUT NOCOPY VARCHAR2,
20                            x_qty1 OUT NOCOPY VARCHAR2);
21 
22 FUNCTION get_drill_across (p_view_by_dim IN VARCHAR2)
23     return VARCHAR2;
24 /*
25     Report query Function for viewby = Item, Org, Cat, Customer
26 */
27 PROCEDURE margin_status_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
28                              x_custom_sql OUT NOCOPY VARCHAR2,
29                              x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
30 IS
31     l_query VARCHAR2(32767);
32     l_view_by VARCHAR2(120);
33     l_view_by_col VARCHAR2 (120);
34     l_xtd VARCHAR2(10);
35     l_comparison_type VARCHAR2(1);
36     l_cur_suffix VARCHAR2(2);
37     l_custom_sql VARCHAR2 (10000);
38 
39     l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
40     l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
41 
42     l_where_clause VARCHAR2 (2000);
43     l_mv VARCHAR2 (2000);
44 
45     l_mv_flag_type  VARCHAR2(50);
46     l_mv_set  VARCHAR2(50);
47 
48     l_prd_cust_flag VARCHAR2(100);
49     l_custom_rec BIS_QUERY_ATTRIBUTES;
50 
51 BEGIN
52 
53     -- initialization block
54     l_comparison_type := 'Y';
55     l_prd_cust_flag := '';
56 
57     -- clear out the tables.
58     l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
59     l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
60 
61     -- get all the query parameters
62     OPI_DBI_RPT_UTIL_PKG.process_parameters (p_param => p_param,
63                                              p_view_by => l_view_by,
64                                              p_view_by_col_name => l_view_by_col,
65                                              p_comparison_type => l_comparison_type,
66                                              p_xtd => l_xtd,
67                                              p_cur_suffix => l_cur_suffix,
68                                              p_where_clause => l_where_clause,
69                                              p_mv => l_mv,
70                                              p_join_tbl => l_join_tbl,
71                                              p_mv_level_flag => l_prd_cust_flag,
72                                              p_trend => 'N',
73                                              p_func_area => 'OPI',
74                                              p_version => '7.0',
75                                              p_role => '',
76                                              p_mv_set => 'PGM',
77                                              p_mv_flag_type => 'PRD_CUST');
78 
79     -- The measure columns that need to be aggregated
80     poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
81                                  p_col_name => 'fulfilled_val_' || l_cur_suffix,
82                                  p_alias_name => 'fulfilled_val');
83 
84     poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
85                                  p_col_name => 'cogs_val_' || l_cur_suffix,
86                                  p_alias_name => 'cogs_val');
87 
88     IF (l_view_by = 'ITEM+ENI_ITEM_ORG') THEN
89         poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
90                                      p_col_name => 'decode(organization_id,top_model_org_id,NVL(fulfilled_qty,0),NULL)',
91                                      p_alias_name => 'fulfilled_qty',
92                                      p_grand_total => 'N');
93     END IF;
94 
95     -- construct the query
96     l_query := get_status_sel_clause (l_view_by, l_join_tbl)
97           || ' from
98         ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
99                                               p_where_clause => l_where_clause,
100                                               p_join_tables => l_join_tbl,
101                                               p_use_windowing => 'Y',
102                                               p_col_name => l_col_tbl,
103                                               p_use_grpid => 'N');
104 
105     -- prepare output for bind variables
106     x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
107     l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
108 
109     -- set the basic bind variables for the status SQL
110     poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
111 
112 
113     /*
114      No Bind Variables if the MV being used is at the Root Product Category level.
115     */
116 
117     IF (l_mv <> 'OPI_PGM_CAT_MV') THEN
118     l_custom_rec.attribute_name := ':OPI_PRDCAT_CUST_FLAG';
119     l_custom_rec.attribute_value := l_prd_cust_flag;
120     l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
121     l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
122     x_custom_output.extend;
123     x_custom_output(x_custom_output.count) := l_custom_rec;
124     END IF;
125     x_custom_sql := l_query;
126 
127 END margin_status_sql;
128 
129 /*
130     Outer main query for viewby = item, org, cat, customer
131 */
132 
133 
134 FUNCTION get_status_sel_clause(p_view_by_dim IN VARCHAR2,
135                                p_join_tbl IN
136                                poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
137     return VARCHAR2
138 IS
139     l_sel_clause varchar2(10000);
140     l_view_by_col_name varchar2(60);
141     l_description varchar2(30);
142     l_uom varchar2(30);
143     l_qty1 varchar2(35);
144     l_view_by_fact_col VARCHAR2(400);
145 
146 
147     l_c_fulfilled_val varchar2(35);
148     l_p_fulfilled_val varchar2(35);
149     l_c_margin varchar2(100);
150     l_p_margin varchar2(100);
151 
152     l_c_fulfilled_val_total varchar2(35);
153     l_p_fulfilled_val_total varchar2(35);
154     l_c_margin_total varchar2(100);
155     l_p_margin_total varchar2(100);
156     l_drill_across_url varchar2(500);
157 
158 
159 
160 BEGIN
161     -- Main Outer query
162     -- Column to get view by column name
163     l_view_by_col_name := OPI_DBI_RPT_UTIL_PKG.get_view_by_col_name (p_view_by_dim);
164     get_qty_columns (p_view_by_dim, l_description, l_uom, l_qty1);
165     -- Fulfilled Value/COGS/Margin
166     l_p_margin := OPI_DBI_RPT_UTIL_PKG.nvl_str ('p_fulfilled_val') || '-' ||
167                   OPI_DBI_RPT_UTIL_PKG.nvl_str ('p_cogs_val');
168     l_c_margin := OPI_DBI_RPT_UTIL_PKG.nvl_str ('c_fulfilled_val') || '-' ||
169                   OPI_DBI_RPT_UTIL_PKG.nvl_str ('c_cogs_val');
170     l_p_margin_total := OPI_DBI_RPT_UTIL_PKG.nvl_str ('p_fulfilled_val_total')
171                         || '-' ||
172                         OPI_DBI_RPT_UTIL_PKG.nvl_str ('p_cogs_val_total');
173     l_c_margin_total := OPI_DBI_RPT_UTIL_PKG.nvl_str ('c_fulfilled_val_total')
174                         || '-' ||
175                         OPI_DBI_RPT_UTIL_PKG.nvl_str ('c_cogs_val_total');
176 
177     l_p_fulfilled_val := OPI_DBI_RPT_UTIL_PKG.nvl_str ('p_fulfilled_val');
178     l_c_fulfilled_val := OPI_DBI_RPT_UTIL_PKG.nvl_str ('c_fulfilled_val');
179     l_p_fulfilled_val_total := OPI_DBI_RPT_UTIL_PKG.nvl_str ('p_fulfilled_val_total');
180     l_c_fulfilled_val_total := OPI_DBI_RPT_UTIL_PKG.nvl_str ('c_fulfilled_val_total');
181     l_drill_across_url := get_drill_across (p_view_by_dim => p_view_by_dim);
182     -- fact column view by's
183     l_view_by_fact_col := opi_dbi_rpt_util_pkg.get_fact_select_columns
184                                                 (p_join_tbl);
185 
186 
187     -- Outer select clause
188     l_sel_clause :=
189     'SELECT
190     ' || opi_dbi_rpt_util_pkg.get_viewby_select_clause (p_view_by_dim)
191       || l_view_by_col_name || ' OPI_ATTRIBUTE1,
192     ' || l_description || ' OPI_ATTRIBUTE2,
193     ' || l_uom || ' OPI_ATTRIBUTE3,
194     ' || 'oset.OPI_MEASURE1,
195     ' || 'oset.OPI_MEASURE2,
196     ' || 'oset.OPI_MEASURE3,
197     ' || 'oset.OPI_MEASURE4,
198     ' || 'oset.OPI_MEASURE5,
199     ' || 'oset.OPI_MEASURE6,
200     ' || 'oset.OPI_MEASURE7,
201     ' || 'oset.OPI_MEASURE8,
202     ' || 'oset.OPI_MEASURE9,
203     ' || 'oset.OPI_MEASURE10,
204     ' || 'oset.OPI_MEASURE11,
205     ' || 'oset.OPI_MEASURE12,
206     ' || 'oset.OPI_MEASURE13,
207     ' || 'oset.OPI_MEASURE14,
208     ' || 'oset.OPI_MEASURE15,
209     ' || 'oset.OPI_MEASURE16,
210     ' || 'oset.OPI_MEASURE17,
211     ' || 'oset.OPI_MEASURE18,
212     ' || 'oset.OPI_MEASURE19,
213     ' || l_drill_across_url || ' OPI_DYNAMIC_URL_1
214     ' || 'FROM
215     ' || '(SELECT (rank () over
216     ' || ' (&ORDER_BY_CLAUSE nulls last,
217     ' || l_view_by_fact_col || ')) - 1 rnk,
218     ' || l_view_by_fact_col || ',
219     ' || 'OPI_MEASURE1,
220     ' || 'OPI_MEASURE2,
221     ' || 'OPI_MEASURE3,
222     ' || 'OPI_MEASURE4,
223     ' || 'OPI_MEASURE5,
224     ' || 'OPI_MEASURE6,
225     ' || 'OPI_MEASURE7,
226     ' || 'OPI_MEASURE8,
227     ' || 'OPI_MEASURE9,
228     ' || 'OPI_MEASURE10,
229     ' || 'OPI_MEASURE11,
230     ' || 'OPI_MEASURE12,
231     ' || 'OPI_MEASURE13,
232     ' || 'OPI_MEASURE14,
233     ' || 'OPI_MEASURE15,
234     ' || 'OPI_MEASURE16,
235     ' || 'OPI_MEASURE17,
236     ' || 'OPI_MEASURE18,
237     ' || 'OPI_MEASURE19
238     ' || 'FROM
239     ' || '(SELECT
240           ' || l_view_by_fact_col || ',
241           ' || OPI_DBI_RPT_UTIL_PKG.nvl_str ('c_fulfilled_val')
242                                             || ' OPI_MEASURE1,
243           ' || OPI_DBI_RPT_UTIL_PKG.nvl_str ('c_cogs_val')
244                                             || ' OPI_MEASURE2,
245           ' || l_p_margin || ' OPI_MEASURE3,
246           ' || l_c_margin || ' OPI_MEASURE4,
247           ' || OPI_DBI_RPT_UTIL_PKG.change_str (l_c_margin,
248                                                 l_p_margin,
249                                                 l_p_margin, '')
250                                             || ' OPI_MEASURE5,
251           ' || OPI_DBI_RPT_UTIL_PKG.percent_str (l_p_margin,
252                                                  l_p_fulfilled_val, '')
253                                             || ' OPI_MEASURE6,
254           ' || OPI_DBI_RPT_UTIL_PKG.percent_str (l_c_margin,
255                                                  l_c_fulfilled_val, '')
256                                             || ' OPI_MEASURE7,
257           ' || OPI_DBI_RPT_UTIL_PKG.change_pct_str (l_c_margin,
258                                                     l_c_fulfilled_val,
259                                                     l_p_margin,
260                                                     l_p_fulfilled_val, '')
261                                             || ' OPI_MEASURE8,
262           ' || OPI_DBI_RPT_UTIL_PKG.nvl_str ('c_fulfilled_val_total')
263                                             || ' OPI_MEASURE9,
264           ' || OPI_DBI_RPT_UTIL_PKG.nvl_str ('c_cogs_val_total')
265                                             || ' OPI_MEASURE10,
266           ' || l_c_margin_total || ' OPI_MEASURE11,
267           ' || OPI_DBI_RPT_UTIL_PKG.change_str (l_c_margin_total,
268                                                 l_p_margin_total,
269                                                 l_p_margin_total, '')
270                                             || ' OPI_MEASURE12,
271           ' || OPI_DBI_RPT_UTIL_PKG.percent_str (l_c_margin_total,
272                                                  l_c_fulfilled_val_total, '')
273                                             || ' OPI_MEASURE13,
274           ' || OPI_DBI_RPT_UTIL_PKG.change_pct_str (l_c_margin_total,
275                                                     l_c_fulfilled_val_total,
276                                                     l_p_margin_total,
277                                                     l_p_fulfilled_val_total,
278                                                     '')
279                                             || ' OPI_MEASURE14,
280           ' || OPI_DBI_RPT_UTIL_PKG.percent_str (l_c_margin,
281                                                  l_c_fulfilled_val, '')
282                                             || ' OPI_MEASURE15,
283           ' || OPI_DBI_RPT_UTIL_PKG.percent_str (l_p_margin,
284                                                  l_p_fulfilled_val, '')
285                                             || ' OPI_MEASURE16,
286           ' || OPI_DBI_RPT_UTIL_PKG.nvl_str (l_qty1)
287                                             || ' OPI_MEASURE17,
288           ' || OPI_DBI_RPT_UTIL_PKG.percent_str (l_c_margin_total,
289                                                  l_c_fulfilled_val_total, '')
290                                             || ' OPI_MEASURE18,
291           ' || OPI_DBI_RPT_UTIL_PKG.percent_str (l_p_margin_total,
292                                                  l_p_fulfilled_val_total, '')
293                                             || ' OPI_MEASURE19 ';
294 
295   RETURN l_sel_clause;
296 
297 END get_status_sel_clause;
298 
299 /*
300     For viewby = item, get the quantity columns that have to be displayed.
301     For all other viewby values, there is no quantity to display.
302 */
303 PROCEDURE get_qty_columns (p_dim_name VARCHAR2,
304                            x_description OUT NOCOPY VARCHAR2,
305                            x_uom OUT NOCOPY VARCHAR2,
306                            x_qty1 OUT NOCOPY VARCHAR2)
307 IS
308 BEGIN
309       CASE
310       WHEN p_dim_name = 'ITEM+ENI_ITEM_ORG' THEN
311               BEGIN
312                   x_description := 'v.description';
313                   x_uom := 'v2.unit_of_measure';
314                   --x_qty1 := opi_dbi_rpt_util_pkg.nvl_str ('c_fulfilled_qty');
315                   x_qty1 := 'c_fulfilled_qty';
316               END;
317           ELSE
318               BEGIN
319                   x_description := 'null';
320                   x_uom := 'null';
321                   x_qty1 := 'null';
322               END;
323       END CASE;
324 END get_qty_columns;
325 
326 /*
327      This Drill Across is for ViewBy Product Category.
328      If it is a LeafNode the Drill Across is ViewBy is Item else ViewBy Product Category.
329 */
330 FUNCTION get_drill_across (p_view_by_dim IN VARCHAR2)
331    return VARCHAR2
332 IS
333     l_drill_across VARCHAR2(500);
334 BEGIN
335 
336     -- initialization block
337     l_drill_across := 'NULL';
338 
339     IF (p_view_by_dim = 'ITEM+ENI_ITEM_VBH_CAT') THEN
340         l_drill_across := 'decode(v.leaf_node_flag, ''Y'',
344     RETURN l_drill_across;
341         ''pFunctionName=OPI_DBI_PRD_CST_MARGIN_TBL_REP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_ORG&pParamIds=Y'',
342         ''pFunctionName=OPI_DBI_PRD_CST_MARGIN_TBL_REP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'') ';
343     END IF;
345 END get_drill_across ;
346 
347 /*
348     Report query for viewby = time (Trend Report)
349 */
350 
351 PROCEDURE margin_trend_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
352                            x_custom_sql OUT NOCOPY VARCHAR2,
353                            x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
354 IS
355     l_query VARCHAR2(32767);
356     l_view_by VARCHAR2(120);
357     l_view_by_col VARCHAR2 (120);
358     l_xtd varchar2(10);
359     l_comparison_type VARCHAR2(1);
360     l_cur_suffix VARCHAR2(2);
361     l_custom_sql VARCHAR2(4000);
362     l_mv VARCHAR2 (2000);
363     l_where_clause VARCHAR2 (4000);
364     l_custom_rec BIS_QUERY_ATTRIBUTES;
365 
366     l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
367     l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
368 
369     l_mv_flag_type  VARCHAR2(50);
370     l_mv_set  VARCHAR2(50);
371 
372     l_prd_cust_flag VARCHAR2(100);
373 
374 BEGIN
375 
376     -- initialization block
377     l_comparison_type := 'Y';
378     l_where_clause := '';
379     l_prd_cust_flag := '';
380 
381     -- clear out the tables.
382     l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
383     l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
384 
385     -- get all the query parameters
386     OPI_DBI_RPT_UTIL_PKG.process_parameters (p_param => p_param,
387                                              p_view_by => l_view_by,
388                                              p_view_by_col_name => l_view_by_col,
389                                              p_comparison_type => l_comparison_type,
390                                              p_xtd => l_xtd,
391                                              p_cur_suffix => l_cur_suffix,
392                                              p_where_clause => l_where_clause,
393                                              p_mv => l_mv,
394                                              p_join_tbl => l_join_tbl,
395                                              p_mv_level_flag => l_prd_cust_flag,
396                                              p_trend => 'Y',
397                                              p_func_area => 'OPI',
398                                              p_version => '7.0',
399                                              p_role => '',
400                                              p_mv_set => 'PGM',
401                                              p_mv_flag_type => 'PRD_CUST');
402 
403 
404     -- The measure columns that need to be aggregated are
405     -- production_val_<b/g>, scrap_val_<b/g>
406     -- If viewing by item as, then sum up
407     -- production_qty, scrap_qty.
408     -- No Grand totals required.
409     poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
410                                  p_col_name => 'fulfilled_val_' || l_cur_suffix,
411                                  p_alias_name => 'fulfilled_val',
412                                  p_grand_total => 'N');
413 
414     poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
415                                  p_col_name => 'cogs_val_' || l_cur_suffix,
416                                  p_alias_name => 'cogs_val',
417                                  p_grand_total => 'N');
418 
419     -- Joining Outer and Inner Query
420     l_query := get_trend_sel_clause(l_view_by, null) ||
421                ' from ' ||
422                poa_dbi_template_pkg.trend_sql (p_xtd => l_xtd,
423                                        p_comparison_type => l_comparison_type,
424                                        p_fact_name => l_mv,
425                                        p_where_clause => l_where_clause,
426                                        p_col_name => l_col_tbl,
427                                        p_use_grpid => 'N');
428 
429     -- Prepare PMV bind variables
430     x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
431     l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
432 
433     -- get all the basic binds used by POA queries
434     -- Do this before adding any of our binds, since the procedure
435     -- reinitializes the output table
436     poa_dbi_util_pkg.get_custom_trend_binds (p_xtd => l_xtd,
437                              p_comparison_type => l_comparison_type,
438                                              x_custom_output => x_custom_output);
439 
440     /*
441      No Bind Variables if the MV being used is at the Root Product Category level.
442     */
443     l_custom_rec.attribute_name := ':OPI_PRDCAT_CUST_FLAG';
444     l_custom_rec.attribute_value := l_prd_cust_flag;
445     l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
446     l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
447     x_custom_output.extend;
448     x_custom_output(x_custom_output.count) := l_custom_rec;
449 
450     x_custom_sql := l_query;
451 END margin_trend_sql;
452 
453 
454 /*
455     The outer main query for the trend SQL.
456 */
457 FUNCTION get_trend_sel_clause(p_view_by_dim IN VARCHAR2, p_url IN VARCHAR2)
458     return VARCHAR2
459 IS
460 
461     l_sel_clause varchar2(4500);
465     l_p_margin varchar2(100);
462     l_c_fulfilled_val varchar2(35);
463     l_p_fulfilled_val varchar2(35);
464     l_c_margin varchar2(100);
466 
467 BEGIN
468 
469     -- Fulfilled Value/COGS/Margin
470 
471     l_p_margin := OPI_DBI_RPT_UTIL_PKG.nvl_str ('iset.p_fulfilled_val') || '-' || OPI_DBI_RPT_UTIL_PKG.nvl_str ('iset.p_cogs_val');
472     l_c_margin := OPI_DBI_RPT_UTIL_PKG.nvl_str ('iset.c_fulfilled_val') || '-' || OPI_DBI_RPT_UTIL_PKG.nvl_str ('iset.c_cogs_val');
473 
474     l_p_fulfilled_val := OPI_DBI_RPT_UTIL_PKG.nvl_str ('iset.p_fulfilled_val');
475     l_c_fulfilled_val := OPI_DBI_RPT_UTIL_PKG.nvl_str ('iset.c_fulfilled_val');
476 
477     -- Main Outer query
478     l_sel_clause :=
479         'SELECT
480     ' || ' cal.name VIEWBY,
481     ' || OPI_DBI_RPT_UTIL_PKG.nvl_str ('iset.c_fulfilled_val') || ' OPI_MEASURE1,
482     ' || OPI_DBI_RPT_UTIL_PKG.nvl_str ('iset.c_cogs_val') || ' OPI_MEASURE2,
483     ' || l_p_margin       || ' OPI_MEASURE3,
484     ' || l_c_margin       || ' OPI_MEASURE4,
485     ' || OPI_DBI_RPT_UTIL_PKG.change_str (l_c_margin, l_p_margin, l_p_margin, '') || ' OPI_MEASURE5,
486     ' || OPI_DBI_RPT_UTIL_PKG.percent_str (l_p_margin, l_p_fulfilled_val, '') || ' OPI_MEASURE6,
487     ' || OPI_DBI_RPT_UTIL_PKG.percent_str (l_c_margin, l_c_fulfilled_val, '') || ' OPI_MEASURE7,
488     ' || OPI_DBI_RPT_UTIL_PKG.change_pct_str (l_c_margin, l_c_fulfilled_val,
489                             l_p_margin, l_p_fulfilled_val, '') || ' OPI_MEASURE8 ';
490   RETURN l_sel_clause;
491 
492 END get_trend_sel_clause;
493 
494 END opi_dbi_prd_cst_margin_pkg;