[Home] [Help]
PACKAGE BODY: APPS.ISC_DEPOT_MARGIN_RPT_PKG
Source
1 PACKAGE BODY ISC_DEPOT_MARGIN_RPT_PKG AS
2 --$Header: iscdepotmrgrqb.pls 120.0 2005/05/25 17:34:31 appldev noship $
3
4 /*++++++++++++++++++++++++++++++++++++++++*/
5 /* Function and procedure declarations in this file but not in spec*/
6 /*++++++++++++++++++++++++++++++++++++++++*/
7
8 FUNCTION get_ro_mrg_tbl_sel_clause(p_view_by_dim IN VARCHAR2, p_view_by_col IN VARCHAR2)
9 return VARCHAR2;
10
11 FUNCTION get_ro_mrg_trd_sel_clause(p_view_by_dim IN VARCHAR2)
12 return VARCHAR2;
13
14 FUNCTION get_chg_summ_tbl_sel_clause(p_view_by_dim IN VARCHAR2, p_view_by_col IN VARCHAR2)
15 return VARCHAR2;
16
17 FUNCTION get_chg_summ_trd_sel_clause(p_view_by_dim IN VARCHAR2)
18 return VARCHAR2;
19
20 FUNCTION get_cst_summ_tbl_sel_clause(p_view_by_dim IN VARCHAR2, p_view_by_col IN VARCHAR2)
21 return VARCHAR2;
22
23 FUNCTION get_cst_summ_trd_sel_clause(p_view_by_dim IN VARCHAR2)
24 return VARCHAR2;
25
26 FUNCTION get_mrg_summ_tbl_sel_clause(p_view_by_dim IN VARCHAR2, p_view_by_col IN VARCHAR2)
27 return VARCHAR2;
28
29 FUNCTION get_mrg_summ_trd_sel_clause(p_view_by_dim IN VARCHAR2)
30 return VARCHAR2;
31
32 FUNCTION get_mrg_dtl_sel_clause(p_curr_suffix IN VARCHAR2)
33 return VARCHAR2;
34
35
36 /*----------------------------------
37 Repair Order Margin Table
38 ----------------------------------*/
39
40 PROCEDURE get_ro_mrg_tbl_sql(
41 p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
42 x_custom_sql OUT NOCOPY VARCHAR2,
43 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
44 ) IS
45
46 l_query ISC_DEPOT_RPT_UTIL_PKG.g_query_typ%type;
47 l_view_by ISC_DEPOT_RPT_UTIL_PKG.g_view_by_typ%type;
48 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG.g_view_by_col_typ%type;
49 l_xtd ISC_DEPOT_RPT_UTIL_PKG.g_xtd_typ%type;
50 l_comparison_type VARCHAR2(1);
51 l_cur_suffix VARCHAR2(2);
52 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
53 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
54 l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type;
55 l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type;
56 l_mv_set VARCHAR2(50);
57 l_aggregation_flag NUMBER;
58 l_custom_rec BIS_QUERY_ATTRIBUTES;
59 l_mv_type VARCHAR2(10);
60 l_err_stage VARCHAR2(500);
61 l_debug_mode VARCHAR2(1);
62 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type;
63
64 BEGIN
65 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
66 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
67 l_comparison_type := 'Y';
68 -- clear out the tables.
69 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
70 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
71
72 -- get all the query parameters
73 ISC_DEPOT_RPT_UTIL_PKG.process_parameters (p_param => p_param,
74 x_view_by => l_view_by,
75 x_view_by_col_name => l_view_by_col,
76 x_comparison_type => l_comparison_type,
77 x_xtd => l_xtd,
78 x_cur_suffix => l_cur_suffix,
79 x_where_clause => l_where_clause,
80 x_mv => l_mv,
81 x_join_tbl => l_join_tbl,
82 x_mv_type => l_mv_type,
83 x_aggregation_flag => l_aggregation_flag,
84 p_trend => 'N',
85 p_mv_set => 'MARGIN',
86 x_custom_output => x_custom_output);
87
88 -- The measure columns that need to be aggregated
89 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
90 p_col_name => 'nvl(material_charges_' || l_cur_suffix ||
91 ',0) + nvl(labor_charges_' || l_cur_suffix ||
92 ',0) + nvl(expense_charges_' || l_cur_suffix || ',0)',
93 p_alias_name => 'charges',
94 p_grand_total => 'Y',
95 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
96
97 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
98 p_col_name => 'nvl(material_cost_' || l_cur_suffix ||
99 ',0) + nvl(labor_cost_' || l_cur_suffix ||
100 ',0) + nvl(expense_cost_' || l_cur_suffix || ',0)',
101 p_alias_name => 'costs',
102 p_grand_total => 'Y',
103 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
104
105 l_join_tbl(l_join_tbl.count).additional_where_clause := l_join_tbl(l_join_tbl.count).additional_where_clause;
106
107
108 -- construct the query
109 l_query := get_ro_mrg_tbl_sel_clause (l_view_by, l_view_by_col)
110 || ' from
111 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
112 p_where_clause => l_where_clause,
113 p_join_tables => l_join_tbl,
114 p_use_windowing => 'Y',
115 p_col_name => l_col_tbl,
116 p_use_grpid => 'N',
117 p_paren_count => 3,
118 p_filter_where => '(BIV_MEASURE1 > 0 OR BIV_MEASURE2 > 0 OR BIV_MEASURE4 > 0 OR BIV_MEASURE5 > 0)',
119 p_generate_viewby => 'Y',
120 p_in_join_tables => NULL);
121
122 -- prepare output for bind variables
123 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
124 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
125
126 -- set the basic bind variables for the status SQL
127 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
128
129 l_custom_rec.attribute_name := ':AGGREGATION_FLAG';
130 l_custom_rec.attribute_value := l_aggregation_flag;
131 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
132 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
133 x_custom_output.extend;
134 x_custom_output(x_custom_output.count) := l_custom_rec;
135
136 x_custom_sql := l_query;
137 EXCEPTION
138 WHEN OTHERS THEN
139 l_err_stage := SQLERRM;
140
141 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
142 l_err_stage:='The exception is : ';
143 ISC_DEPOT_RPT_UTIL_PKG.write('get_ro_mrg_tbl_sql : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
144 END IF;
145
146 END get_ro_mrg_tbl_sql;
147
148 FUNCTION get_ro_mrg_tbl_sel_clause (p_view_by_dim IN VARCHAR2, p_view_by_col IN VARCHAR2)
149 return VARCHAR2
150 IS
151
152 l_sel_clause varchar2(8000);
153 l_p_margin varchar2 (500);
154 l_c_margin varchar2 (500);
155 l_p_margin_total varchar2 (500);
156 l_c_margin_total varchar2 (500);
157 l_p_margin_percent varchar2 (500);
158 l_c_margin_percent varchar2 (500);
159 l_p_margin_percent_total varchar2 (500);
160 l_c_margin_percent_total varchar2 (500);
161 l_description varchar2(30);
162
163 l_cat_drill_down varchar2(500);
164
165 BEGIN
166 l_description := 'null';
167 l_cat_drill_down := 'null';
168
169 l_p_margin := '(NVL (p_charges, 0) - NVL (p_costs, 0))';
170 l_c_margin := '(NVL (c_charges, 0) - NVL (c_costs, 0))';
171 l_p_margin_total := '(NVL (p_charges_total, 0) - NVL (p_costs_total, 0))';
172 l_c_margin_total := '(NVL (c_charges_total, 0) - NVL (c_costs_total, 0))';
173 l_p_margin_percent := poa_dbi_util_pkg.rate_clause (l_p_margin, 'NVL (p_charges, 0)', 'P');
174 l_c_margin_percent := poa_dbi_util_pkg.rate_clause (l_c_margin, 'NVL (c_charges, 0)', 'P');
175 l_p_margin_percent_total := poa_dbi_util_pkg.rate_clause (l_p_margin_total, 'NVL (p_charges_total, 0)', 'P');
176 l_c_margin_percent_total := poa_dbi_util_pkg.rate_clause (l_c_margin_total, 'NVL (c_charges_total, 0)', 'P');
177
178
179 IF p_view_by_dim = 'ITEM+ENI_ITEM' THEN
180 l_description := 'v.description';
181 END IF;
182
183 IF (p_view_by_dim = 'ITEM+ENI_ITEM_VBH_CAT') THEN
184 l_cat_drill_down := 'decode(v.leaf_node_flag, ''Y'', ' ||
185 '''pFunctionName=ISC_DEPOT_RO_MARGIN_TBL_REP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM&pParamIds=Y'', ' ||
186 '''pFunctionName=ISC_DEPOT_RO_MARGIN_TBL_REP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'') ';
187 END IF;
188
189 l_sel_clause :=
190 ' SELECT ' ||
191 ISC_DEPOT_RPT_UTIL_PKG.get_viewby_select_clause (p_view_by_dim) || fnd_global.newline ||
192 l_description || ' BIV_ATTRIBUTE1 ' || fnd_global.newline || ', ' ||
193 'BIV_MEASURE1 ' || fnd_global.newline || ', ' ||
194 'BIV_MEASURE2 ' || fnd_global.newline || ', ' ||
195 'BIV_MEASURE3 ' || fnd_global.newline || ', ' ||
196 'BIV_MEASURE4 ' || fnd_global.newline || ', ' ||
197 'BIV_MEASURE5 ' || fnd_global.newline || ', ' ||
198 'BIV_MEASURE6 ' || fnd_global.newline || ', ' ||
199 'BIV_MEASURE7 ' || fnd_global.newline || ', ' ||
200 'BIV_MEASURE8 ' || fnd_global.newline || ', ' ||
201 'BIV_MEASURE9 ' || fnd_global.newline || ', ' ||
202 'BIV_MEASURE10 ' || fnd_global.newline || ', ' ||
203 'BIV_MEASURE11 ' || fnd_global.newline || ', ' ||
204 'BIV_MEASURE12 ' || fnd_global.newline || ', ' ||
205 'BIV_MEASURE13 ' || fnd_global.newline || ', ' ||
206 'BIV_MEASURE14 ' || fnd_global.newline || ', ' ||
207 'BIV_MEASURE15 ' || fnd_global.newline || ', ' ||
208 'BIV_MEASURE16 ' || fnd_global.newline || ', ' ||
209 'BIV_MEASURE17 ' || fnd_global.newline || ', ' ||
210 'BIV_MEASURE18 ' || fnd_global.newline || ', ' ||
211 'BIV_MEASURE19 ' || fnd_global.newline || ', ' ||
212 'BIV_MEASURE20 ' || fnd_global.newline || ', ' ||
213 'BIV_MEASURE21 ' || fnd_global.newline || ', ' ||
214 'BIV_MEASURE22 ' || fnd_global.newline || ', ' ||
215 'BIV_MEASURE23 ' || fnd_global.newline || ', ' ||
216 'BIV_MEASURE24 ' || fnd_global.newline || ', ' ||
217 l_cat_drill_down || ' BIV_DYNAMIC_URL1 ' || fnd_global.newline ||
218 'FROM ( SELECT ' ||
219 ' rank() over (&ORDER_BY_CLAUSE'||' nulls last, '|| p_view_by_col ||' ) - 1 rnk' || fnd_global.newline || ', ' ||
220 p_view_by_col || fnd_global.newline || ', ' ||
221 'BIV_MEASURE1 ' || fnd_global.newline || ', ' ||
222 'BIV_MEASURE2 ' || fnd_global.newline || ', ' ||
223 'BIV_MEASURE3 ' || fnd_global.newline || ', ' ||
224 'BIV_MEASURE4 ' || fnd_global.newline || ', ' ||
225 'BIV_MEASURE5 ' || fnd_global.newline || ', ' ||
226 'BIV_MEASURE6 ' || fnd_global.newline || ', ' ||
227 'BIV_MEASURE7 ' || fnd_global.newline || ', ' ||
228 'BIV_MEASURE8 ' || fnd_global.newline || ', ' ||
229 'BIV_MEASURE9 ' || fnd_global.newline || ', ' ||
230 'BIV_MEASURE10 ' || fnd_global.newline || ', ' ||
231 'BIV_MEASURE11 ' || fnd_global.newline || ', ' ||
232 'BIV_MEASURE12 ' || fnd_global.newline || ', ' ||
233 'BIV_MEASURE13 ' || fnd_global.newline || ', ' ||
234 'BIV_MEASURE14 ' || fnd_global.newline || ', ' ||
235 'BIV_MEASURE15 ' || fnd_global.newline || ', ' ||
236 'BIV_MEASURE16 ' || fnd_global.newline || ', ' ||
237 'BIV_MEASURE17 ' || fnd_global.newline || ', ' ||
238 'BIV_MEASURE18 ' || fnd_global.newline || ', ' ||
239 'BIV_MEASURE19 ' || fnd_global.newline || ', ' ||
240 'BIV_MEASURE20 ' || fnd_global.newline || ', ' ||
241 'BIV_MEASURE21 ' || fnd_global.newline || ', ' ||
242 'BIV_MEASURE22 ' || fnd_global.newline || ', ' ||
243 'BIV_MEASURE23 ' || fnd_global.newline || ', ' ||
244 'BIV_MEASURE24 ' || fnd_global.newline ||
245 ' FROM (SELECT ' || p_view_by_col || fnd_global.newline || ', ' ||
246 'NVL (p_charges, 0) BIV_MEASURE1 ' || fnd_global.newline || ', ' ||
247 'NVL (c_charges, 0) BIV_MEASURE2 ' || fnd_global.newline || ', ' ||
248 poa_dbi_util_pkg.change_clause ('c_charges', 'p_charges') || ' BIV_MEASURE3 ' || fnd_global.newline || ', ' ||
249 'NVL (p_costs, 0) BIV_MEASURE4 ' || fnd_global.newline || ', ' ||
250 'NVL (c_costs, 0) BIV_MEASURE5 ' || fnd_global.newline || ', ' ||
251 poa_dbi_util_pkg.change_clause ('c_costs', 'p_costs') || ' BIV_MEASURE6 ' || fnd_global.newline || ', ' ||
252 l_p_margin || ' BIV_MEASURE7 ' || fnd_global.newline || ', ' ||
253 l_c_margin || ' BIV_MEASURE8 ' || fnd_global.newline || ', ' ||
254 poa_dbi_util_pkg.change_clause (l_c_margin, l_p_margin) || ' BIV_MEASURE9 ' || fnd_global.newline || ', ' ||
255 l_p_margin_percent || ' BIV_MEASURE10 ' || fnd_global.newline || ', ' ||
256 l_c_margin_percent || ' BIV_MEASURE11 ' || fnd_global.newline || ', ' ||
257 poa_dbi_util_pkg.change_clause (l_c_margin_percent, l_p_margin_percent, 'P') || ' BIV_MEASURE12 ' || fnd_global.newline || ', ' ||
258 'NVL (c_charges_total, 0) BIV_MEASURE13 ' || fnd_global.newline || ', ' ||
259 poa_dbi_util_pkg.change_clause ('c_charges_total', 'p_charges_total') || ' BIV_MEASURE14 ' || fnd_global.newline || ', ' ||
260 'NVL (c_costs_total, 0) BIV_MEASURE15 ' || fnd_global.newline || ', ' ||
261 poa_dbi_util_pkg.change_clause ('c_costs_total', 'p_costs_total') || ' BIV_MEASURE16 ' || fnd_global.newline || ', ' ||
262 l_c_margin_total || ' BIV_MEASURE17 ' || fnd_global.newline || ', ' ||
263 poa_dbi_util_pkg.change_clause (l_c_margin_total, l_p_margin_total) || ' BIV_MEASURE18 ' || fnd_global.newline || ', ' ||
264 l_c_margin_percent_total || ' BIV_MEASURE19 ' || fnd_global.newline || ', ' ||
265 poa_dbi_util_pkg.change_clause (l_c_margin_percent_total, l_p_margin_percent_total, 'P') || ' BIV_MEASURE20 ' || fnd_global.newline || ', ' ||
266 l_c_margin || ' BIV_MEASURE21 ' || fnd_global.newline || ', ' ||
267 l_p_margin || ' BIV_MEASURE22 ' || fnd_global.newline || ', ' ||
268 l_c_margin_total || ' BIV_MEASURE23 ' || fnd_global.newline || ', ' ||
269 l_p_margin_total || ' BIV_MEASURE24 ' || fnd_global.newline;
270 RETURN l_sel_clause;
271 END get_ro_mrg_tbl_sel_clause;
272
273 /*----------------------------------
274 Repair Order Margin Trend
275 ----------------------------------*/
276 PROCEDURE get_ro_mrg_trd_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
277 x_custom_sql OUT NOCOPY VARCHAR2,
278 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
279 IS
280 l_query ISC_DEPOT_RPT_UTIL_PKG.g_query_typ%type;
281 l_view_by ISC_DEPOT_RPT_UTIL_PKG.g_view_by_typ%type;
282 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG.g_view_by_col_typ%type;
283 l_xtd ISC_DEPOT_RPT_UTIL_PKG.g_xtd_typ%type;
284 l_comparison_type VARCHAR2(1);
285 l_cur_suffix VARCHAR2(2);
286 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
287 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
288 l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type;
289 l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type;
290 l_mv_set VARCHAR2(50);
291 l_aggregation_flag NUMBER;
292 l_custom_rec BIS_QUERY_ATTRIBUTES;
293 l_mv_type VARCHAR2(10);
294 l_err_stage VARCHAR2(500);
295 l_debug_mode VARCHAR2(1);
296 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type;
297
298 BEGIN
299 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
300 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
301 l_comparison_type := 'Y';
302 -- clear out the tables.
303 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
304 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
305
306 -- get all the query parameters
307 ISC_DEPOT_RPT_UTIL_PKG.process_parameters (p_param => p_param,
308 x_view_by => l_view_by,
309 x_view_by_col_name => l_view_by_col,
310 x_comparison_type => l_comparison_type,
311 x_xtd => l_xtd,
312 x_cur_suffix => l_cur_suffix,
313 x_where_clause => l_where_clause,
314 x_mv => l_mv,
315 x_join_tbl => l_join_tbl,
316 x_mv_type => l_mv_type,
317 x_aggregation_flag => l_aggregation_flag,
318 p_trend => 'Y',
319 p_mv_set => 'MARGIN',
320 x_custom_output => x_custom_output);
321
322 -- The measure columns that need to be aggregated
323 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
324 p_col_name => 'nvl(material_charges_' || l_cur_suffix ||
325 ',0) + nvl(labor_charges_' || l_cur_suffix ||
326 ',0) + nvl(expense_charges_' || l_cur_suffix || ',0)',
327 p_alias_name => 'charges',
328 p_grand_total => 'N',
329 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
330
331 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
332 p_col_name => 'nvl(material_cost_' || l_cur_suffix ||
333 ',0) + nvl(labor_cost_' || l_cur_suffix ||
334 ',0) + nvl(expense_cost_' || l_cur_suffix || ',0)',
335 p_alias_name => 'costs',
336 p_grand_total => 'N',
337 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
338
339 -- Joining Outer and Inner Query
340 l_query := get_ro_mrg_trd_sel_clause (l_view_by) ||
341 ' from ' ||
342 poa_dbi_template_pkg.trend_sql (p_xtd => l_xtd,
343 p_comparison_type => l_comparison_type,
344 p_fact_name => l_mv,
345 p_where_clause => l_where_clause,
346 p_col_name => l_col_tbl,
347 p_use_grpid => 'N',
348 p_in_join_tables => NULL);
349
350 -- Prepare PMV bind variables
351 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
352 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
353
354 -- get all the basic binds used by POA queries
355 -- Do this before adding any of our binds, since the procedure
356 -- reinitializes the output table
357 poa_dbi_util_pkg.get_custom_trend_binds (p_xtd => l_xtd,
358 p_comparison_type => l_comparison_type,
359 x_custom_output => x_custom_output);
360
361 l_custom_rec.attribute_name := ':AGGREGATION_FLAG';
362 l_custom_rec.attribute_value := l_aggregation_flag;
363 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
364 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
365 x_custom_output.extend;
366 x_custom_output(x_custom_output.count) := l_custom_rec;
367
368 x_custom_sql := l_query;
369 EXCEPTION
370 WHEN OTHERS THEN
371 l_err_stage := SQLERRM;
372
373 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
374 l_err_stage:='The exception is : ';
375 ISC_DEPOT_RPT_UTIL_PKG.write('get_ro_mrg_trd_sql : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
376 END IF;
377 END get_ro_mrg_trd_sql;
378
379
380 /*
381 The outer main query for the trend SQL.
382 */
383 FUNCTION get_ro_mrg_trd_sel_clause(p_view_by_dim IN VARCHAR2)
384 return VARCHAR2
385 IS
386
387 l_sel_clause varchar2(4500);
388 l_p_margin varchar2 (500);
389 l_c_margin varchar2 (500);
390 l_p_margin_percent varchar2 (500);
391 l_c_margin_percent varchar2 (500);
392
393 BEGIN
394 l_p_margin := '(NVL (iset.p_charges, 0) - NVL (iset.p_costs, 0))';
395 l_c_margin := '(NVL (iset.c_charges, 0) - NVL (iset.c_costs, 0))';
396 l_p_margin_percent := poa_dbi_util_pkg.rate_clause (l_p_margin, 'NVL (iset.p_charges, 0)', 'P');
397 l_c_margin_percent := poa_dbi_util_pkg.rate_clause (l_c_margin, 'NVL (iset.c_charges, 0)', 'P');
398
399 l_sel_clause :=
400 ' SELECT ' ||
401 ' cal.name VIEWBY ' || fnd_global.newline || ', ' ||
402 'NVL (iset.p_charges, 0) BIV_MEASURE1 ' || fnd_global.newline || ', ' ||
403 'NVL (iset.c_charges, 0) BIV_MEASURE2 ' || fnd_global.newline || ', ' ||
404 poa_dbi_util_pkg.change_clause ('iset.c_charges', 'iset.p_charges') || ' BIV_MEASURE3 ' || fnd_global.newline || ', ' ||
405 'NVL (iset.p_costs, 0) BIV_MEASURE4 ' || fnd_global.newline || ', ' ||
406 'NVL (iset.c_costs, 0) BIV_MEASURE5 ' || fnd_global.newline || ', ' ||
407 poa_dbi_util_pkg.change_clause ('iset.c_costs', 'iset.p_costs') || ' BIV_MEASURE6 ' || fnd_global.newline || ', ' ||
408 l_p_margin || ' BIV_MEASURE7 ' || fnd_global.newline || ', ' ||
409 l_c_margin || ' BIV_MEASURE8 ' || fnd_global.newline || ', ' ||
410 poa_dbi_util_pkg.change_clause (l_c_margin, l_p_margin) || ' BIV_MEASURE9 ' || fnd_global.newline || ', ' ||
411 l_p_margin_percent || ' BIV_MEASURE10 ' || fnd_global.newline || ', ' ||
412 l_c_margin_percent || ' BIV_MEASURE11 ' || fnd_global.newline || ', ' ||
413 poa_dbi_util_pkg.change_clause (l_c_margin_percent, l_p_margin_percent, 'P') || ' BIV_MEASURE12 ' || fnd_global.newline;
414 RETURN l_sel_clause;
415
416 END get_ro_mrg_trd_sel_clause;
417
418 /*----------------------------------
419 Charges Summary Table
420 ----------------------------------*/
421
422 PROCEDURE get_chg_summ_tbl_sql(
423 p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
424 x_custom_sql OUT NOCOPY VARCHAR2,
425 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
426 ) IS
427
428 l_query ISC_DEPOT_RPT_UTIL_PKG.g_query_typ%type;
429 l_view_by ISC_DEPOT_RPT_UTIL_PKG.g_view_by_typ%type;
430 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG.g_view_by_col_typ%type;
431 l_xtd ISC_DEPOT_RPT_UTIL_PKG.g_xtd_typ%type;
432 l_comparison_type VARCHAR2(1);
433 l_cur_suffix VARCHAR2(2);
434 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
435 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
436 l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type;
437 l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type;
438 l_mv_set VARCHAR2(50);
439 l_aggregation_flag NUMBER;
440 l_custom_rec BIS_QUERY_ATTRIBUTES;
441 l_mv_type VARCHAR2(10);
442 l_err_stage VARCHAR2(500);
443 l_debug_mode VARCHAR2(1);
444 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type;
445
446 BEGIN
447 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
448 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
449 l_comparison_type := 'Y';
450 -- clear out the tables.
451 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
452 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
453
454 -- get all the query parameters
455 ISC_DEPOT_RPT_UTIL_PKG.process_parameters (p_param => p_param,
456 x_view_by => l_view_by,
457 x_view_by_col_name => l_view_by_col,
458 x_comparison_type => l_comparison_type,
459 x_xtd => l_xtd,
460 x_cur_suffix => l_cur_suffix,
461 x_where_clause => l_where_clause,
462 x_mv => l_mv,
463 x_join_tbl => l_join_tbl,
464 x_mv_type => l_mv_type,
465 x_aggregation_flag => l_aggregation_flag,
466 p_trend => 'N',
467 p_mv_set => 'CHARGES',
468 x_custom_output => x_custom_output);
469
470 -- The measure columns that need to be aggregated
471 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
472 p_col_name => 'material_charges_' || l_cur_suffix,
473 p_alias_name => 'm_charges',
474 p_grand_total => 'Y',
475 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
476
477 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
478 p_col_name => 'labor_charges_' || l_cur_suffix,
479 p_alias_name => 'l_charges',
480 p_grand_total => 'Y',
481 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
482
483 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
484 p_col_name => 'expense_charges_' || l_cur_suffix,
485 p_alias_name => 'e_charges',
486 p_grand_total => 'Y',
487 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
488
489 l_join_tbl(l_join_tbl.count).additional_where_clause := l_join_tbl(l_join_tbl.count).additional_where_clause;
490
491
492 -- construct the query
493 l_query := get_chg_summ_tbl_sel_clause (l_view_by, l_view_by_col)
494 || ' from
495 ' || poa_dbi_template_pkg.status_sql (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_paren_count => 3,
502 p_filter_where => '(BIV_MEASURE7 > 0 OR BIV_MEASURE8 > 0)',
503 p_generate_viewby => 'Y',
504 p_in_join_tables => NULL);
505
506 -- prepare output for bind variables
507 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
508 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
509
510 -- set the basic bind variables for the status SQL
511 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
512
513 l_custom_rec.attribute_name := ':AGGREGATION_FLAG';
514 l_custom_rec.attribute_value := l_aggregation_flag;
515 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
516 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
517 x_custom_output.extend;
518 x_custom_output(x_custom_output.count) := l_custom_rec;
519
520 x_custom_sql := l_query;
521 EXCEPTION
522 WHEN OTHERS THEN
523 l_err_stage := SQLERRM;
524
525 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
526 l_err_stage:='The exception is : ';
527 ISC_DEPOT_RPT_UTIL_PKG.write('get_chg_summ_tbl_sql : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
528 END IF;
529
530 END get_chg_summ_tbl_sql;
531
532 FUNCTION get_chg_summ_tbl_sel_clause (p_view_by_dim IN VARCHAR2, p_view_by_col IN VARCHAR2)
533 return VARCHAR2
534 IS
535
536 l_sel_clause varchar2(8000);
537 l_p_total_charges varchar2 (500);
538 l_c_total_charges varchar2 (500);
539 l_p_total_charges_total varchar2 (500);
540 l_c_total_charges_total varchar2 (500);
541 l_description varchar2(30);
542 l_cat_drill_down varchar2 (500);
543
544 BEGIN
545 l_description := 'null';
546 l_cat_drill_down := 'null';
547
548 l_p_total_charges := '(NVL (p_m_charges, 0) + NVL (p_l_charges, 0) + NVL (p_e_charges, 0))';
549 l_c_total_charges := '(NVL (c_m_charges, 0) + NVL (c_l_charges, 0) + NVL (c_e_charges, 0))';
550 l_p_total_charges_total := '(NVL (p_m_charges_total, 0) + NVL (p_l_charges_total, 0) + ' ||
551 'NVL (p_e_charges_total, 0))';
552 l_c_total_charges_total := '(NVL (c_m_charges_total, 0) + NVL (c_l_charges_total, 0) +' ||
553 'NVL (c_e_charges_total, 0))';
554
555 IF p_view_by_dim = 'ITEM+ENI_ITEM_VBH_CAT' THEN
556 l_cat_drill_down := 'decode(v.leaf_node_flag, ''Y'', ' ||
557 '''pFunctionName=ISC_DEPOT_CHARGES_TBL_REP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM&pParamIds=Y'', ' ||
558 '''pFunctionName=ISC_DEPOT_CHARGES_TBL_REP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'') ';
559 ELSIF p_view_by_dim = 'ITEM+ENI_ITEM' THEN
560 l_description := 'v.description';
561 END IF;
562
563 l_sel_clause :=
564 ' SELECT ' ||
565 ISC_DEPOT_RPT_UTIL_PKG.get_viewby_select_clause (p_view_by_dim) || fnd_global.newline ||
566 l_description || ' BIV_ATTRIBUTE1 ' || fnd_global.newline || ',' ||
567 'BIV_MEASURE1 ' || fnd_global.newline || ',' ||
568 'BIV_MEASURE2 ' || fnd_global.newline || ',' ||
569 'BIV_MEASURE3 ' || fnd_global.newline || ',' ||
570 'BIV_MEASURE4 ' || fnd_global.newline || ',' ||
571 'BIV_MEASURE5 ' || fnd_global.newline || ',' ||
572 'BIV_MEASURE6 ' || fnd_global.newline || ',' ||
573 'BIV_MEASURE7 ' || fnd_global.newline || ',' ||
574 'BIV_MEASURE8 ' || fnd_global.newline || ',' ||
575 'BIV_MEASURE9 ' || fnd_global.newline || ',' ||
576 'BIV_MEASURE10 ' || fnd_global.newline || ',' ||
577 'BIV_MEASURE11 ' || fnd_global.newline || ',' ||
578 'BIV_MEASURE12 ' || fnd_global.newline || ',' ||
579 'BIV_MEASURE13 ' || fnd_global.newline || ',' ||
580 'BIV_MEASURE14 ' || fnd_global.newline || ',' ||
581 'BIV_MEASURE15 ' || fnd_global.newline || ',' ||
582 'BIV_MEASURE16 ' || fnd_global.newline || ',' ||
583 'BIV_MEASURE17 ' || fnd_global.newline || ',' ||
584 l_cat_drill_down || ' BIV_DYNAMIC_URL1 ' || fnd_global.newline ||
585 'FROM ( SELECT ' || fnd_global.newline ||
586 ' rank() over (&ORDER_BY_CLAUSE'||' nulls last, '|| p_view_by_col ||' ) - 1 rnk ' || fnd_global.newline || ', ' ||
587 p_view_by_col || fnd_global.newline || ', ' ||
588 'BIV_MEASURE1 ' || fnd_global.newline || ',' ||
589 'BIV_MEASURE2 ' || fnd_global.newline || ',' ||
590 'BIV_MEASURE3 ' || fnd_global.newline || ',' ||
591 'BIV_MEASURE4 ' || fnd_global.newline || ',' ||
592 'BIV_MEASURE5 ' || fnd_global.newline || ',' ||
593 'BIV_MEASURE6 ' || fnd_global.newline || ',' ||
594 'BIV_MEASURE7 ' || fnd_global.newline || ',' ||
595 'BIV_MEASURE8 ' || fnd_global.newline || ',' ||
596 'BIV_MEASURE9 ' || fnd_global.newline || ',' ||
597 'BIV_MEASURE10 ' || fnd_global.newline || ',' ||
598 'BIV_MEASURE11 ' || fnd_global.newline || ',' ||
599 'BIV_MEASURE12 ' || fnd_global.newline || ',' ||
600 'BIV_MEASURE13 ' || fnd_global.newline || ',' ||
601 'BIV_MEASURE14 ' || fnd_global.newline || ',' ||
602 'BIV_MEASURE15 ' || fnd_global.newline || ',' ||
603 'BIV_MEASURE16 ' || fnd_global.newline || ',' ||
604 'BIV_MEASURE17 ' || fnd_global.newline ||
605 'FROM (' ||
606 ' SELECT ' || p_view_by_col || fnd_global.newline || ', ' ||
607 'NVL (c_m_charges, 0) BIV_MEASURE1 ' || fnd_global.newline || ', ' ||
608 poa_dbi_util_pkg.change_clause ('c_m_charges', 'p_m_charges') || ' BIV_MEASURE2 ' || fnd_global.newline || ', ' ||
609 'NVL (c_l_charges, 0) BIV_MEASURE3 ' || fnd_global.newline || ', ' ||
610 poa_dbi_util_pkg.change_clause ('c_l_charges', 'p_l_charges') || ' BIV_MEASURE4 ' || fnd_global.newline || ', ' ||
611 'NVL (c_e_charges, 0) BIV_MEASURE5 ' || fnd_global.newline || ', ' ||
612 poa_dbi_util_pkg.change_clause ('c_e_charges', 'p_e_charges') || ' BIV_MEASURE6 ' || fnd_global.newline || ', ' ||
613 l_p_total_charges || ' BIV_MEASURE7 ' || fnd_global.newline || ', ' ||
614 l_c_total_charges || ' BIV_MEASURE8 ' || fnd_global.newline || ', ' ||
615 poa_dbi_util_pkg.change_clause (l_c_total_charges, l_p_total_charges) || ' BIV_MEASURE9 ' || fnd_global.newline || ', ' ||
616 'NVL (c_m_charges_total, 0) BIV_MEASURE10 ' || fnd_global.newline || ', ' ||
617 poa_dbi_util_pkg.change_clause ('c_m_charges_total', 'p_m_charges_total') || ' BIV_MEASURE11 ' || fnd_global.newline || ', ' ||
618 'NVL (c_l_charges_total, 0) BIV_MEASURE12 ' || fnd_global.newline || ', ' ||
619 poa_dbi_util_pkg.change_clause ('c_l_charges_total', 'p_l_charges_total') || ' BIV_MEASURE13 ' || fnd_global.newline || ', ' ||
620 'NVL (c_e_charges_total, 0) BIV_MEASURE14 ' || fnd_global.newline || ', ' ||
621 poa_dbi_util_pkg.change_clause ('c_e_charges_total', 'p_e_charges_total') || ' BIV_MEASURE15 ' || fnd_global.newline || ', ' ||
622 l_c_total_charges_total || ' BIV_MEASURE16 ' || fnd_global.newline || ', ' ||
623 poa_dbi_util_pkg.change_clause (l_c_total_charges_total, l_p_total_charges_total) || ' BIV_MEASURE17 ' || fnd_global.newline;
624
625
626 RETURN l_sel_clause;
627 END get_chg_summ_tbl_sel_clause;
628
629 /*----------------------------------
630 Charges Summary Trend
631 ----------------------------------*/
632 PROCEDURE get_chg_summ_trd_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
633 x_custom_sql OUT NOCOPY VARCHAR2,
634 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
635 IS
636 l_query ISC_DEPOT_RPT_UTIL_PKG.g_query_typ%type;
637 l_view_by ISC_DEPOT_RPT_UTIL_PKG.g_view_by_typ%type;
638 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG.g_view_by_col_typ%type;
639 l_xtd ISC_DEPOT_RPT_UTIL_PKG.g_xtd_typ%type;
640 l_comparison_type VARCHAR2(1);
641 l_cur_suffix VARCHAR2(2);
642 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
643 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
644 l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type;
645 l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type;
646 l_mv_set VARCHAR2(50);
647 l_aggregation_flag NUMBER;
648 l_custom_rec BIS_QUERY_ATTRIBUTES;
649 l_mv_type VARCHAR2(10);
650 l_err_stage VARCHAR2(500);
651 l_debug_mode VARCHAR2(1);
652 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type;
653
654 BEGIN
655 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
656 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
657 l_comparison_type := 'Y';
658 -- clear out the tables.
659 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
660 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
661
662 -- get all the query parameters
663 ISC_DEPOT_RPT_UTIL_PKG.process_parameters (p_param => p_param,
664 x_view_by => l_view_by,
665 x_view_by_col_name => l_view_by_col,
666 x_comparison_type => l_comparison_type,
667 x_xtd => l_xtd,
668 x_cur_suffix => l_cur_suffix,
669 x_where_clause => l_where_clause,
670 x_mv => l_mv,
671 x_join_tbl => l_join_tbl,
672 x_mv_type => l_mv_type,
673 x_aggregation_flag => l_aggregation_flag,
674 p_trend => 'Y',
675 p_mv_set => 'CHARGES',
676 x_custom_output => x_custom_output);
677
678 -- The measure columns that need to be aggregated
679 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
680 p_col_name => 'material_charges_' || l_cur_suffix,
681 p_alias_name => 'm_charges',
682 p_grand_total => 'N',
683 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
684
685 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
686 p_col_name => 'labor_charges_' || l_cur_suffix,
687 p_alias_name => 'l_charges',
688 p_grand_total => 'N',
689 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
690
691 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
692 p_col_name => 'expense_charges_' || l_cur_suffix,
693 p_alias_name => 'e_charges',
694 p_grand_total => 'N',
695 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
696
697 -- Joining Outer and Inner Query
698 l_query := get_chg_summ_trd_sel_clause (l_view_by) ||
699 ' from ' ||
700 poa_dbi_template_pkg.trend_sql (p_xtd => l_xtd,
701 p_comparison_type => l_comparison_type,
702 p_fact_name => l_mv,
703 p_where_clause => l_where_clause,
704 p_col_name => l_col_tbl,
705 p_use_grpid => 'N',
706 p_in_join_tables => NULL);
707
708 -- Prepare PMV bind variables
709 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
710 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
711
712 -- get all the basic binds used by POA queries
713 -- Do this before adding any of our binds, since the procedure
714 -- reinitializes the output table
715 poa_dbi_util_pkg.get_custom_trend_binds (p_xtd => l_xtd,
716 p_comparison_type => l_comparison_type,
717 x_custom_output => x_custom_output);
718
719
720 l_custom_rec.attribute_name := ':AGGREGATION_FLAG';
721 l_custom_rec.attribute_value := l_aggregation_flag;
722 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
723 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
724 x_custom_output.extend;
725 x_custom_output(x_custom_output.count) := l_custom_rec;
726
727 x_custom_sql := l_query;
728 EXCEPTION
729 WHEN OTHERS THEN
730 l_err_stage := SQLERRM;
731
732 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
733 l_err_stage:='The exception is : ';
734 ISC_DEPOT_RPT_UTIL_PKG.write('get_chg_summ_trd_sql : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
735 END IF;
736
737 END get_chg_summ_trd_sql;
738
739
740 /*
741 The outer main query for the trend SQL.
742 */
743 FUNCTION get_chg_summ_trd_sel_clause(p_view_by_dim IN VARCHAR2)
744 return VARCHAR2
745 IS
746
747 l_sel_clause varchar2(4500);
748 l_p_total_charges varchar2 (500);
749 l_c_total_charges varchar2 (500);
750
751
752 BEGIN
753 l_p_total_charges := '(NVL (iset.p_m_charges, 0) + NVL (iset.p_l_charges, 0) + NVL (iset.p_e_charges, 0))';
754 l_c_total_charges := '(NVL (iset.c_m_charges, 0) + NVL (iset.c_l_charges, 0) + NVL (iset.c_e_charges, 0))';
755
756 l_sel_clause :=
757 ' SELECT ' ||
758 ' cal.name VIEWBY ' || fnd_global.newline || ', ' ||
759 'NVL (iset.c_m_charges, 0) BIV_MEASURE1 ' || fnd_global.newline || ', ' ||
760 poa_dbi_util_pkg.change_clause ('iset.c_m_charges', 'iset.p_m_charges') || ' BIV_MEASURE2 ' || fnd_global.newline || ', ' ||
761 'NVL (iset.c_l_charges, 0) BIV_MEASURE3 ' || fnd_global.newline || ', ' ||
762 poa_dbi_util_pkg.change_clause ('iset.c_l_charges', 'iset.p_l_charges') || ' BIV_MEASURE4 ' || fnd_global.newline || ', ' ||
763 'NVL (iset.c_e_charges, 0) BIV_MEASURE5 ' || fnd_global.newline || ', ' ||
764 poa_dbi_util_pkg.change_clause ('iset.c_e_charges', 'iset.p_e_charges') || ' BIV_MEASURE6 ' || fnd_global.newline || ', ' ||
765 l_p_total_charges || ' BIV_MEASURE7 ' || fnd_global.newline || ', ' ||
766 l_c_total_charges || ' BIV_MEASURE8 ' || fnd_global.newline || ', ' ||
767 poa_dbi_util_pkg.change_clause (l_c_total_charges, l_p_total_charges) || ' BIV_MEASURE9 ' || fnd_global.newline;
768 RETURN l_sel_clause;
769 END get_chg_summ_trd_sel_clause;
770
771 /*----------------------------------
772 Cost Summary Table
773 ----------------------------------*/
774
775 PROCEDURE get_cst_summ_tbl_sql(
776 p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
777 x_custom_sql OUT NOCOPY VARCHAR2,
778 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
779 ) IS
780
781 l_query ISC_DEPOT_RPT_UTIL_PKG.g_query_typ%type;
782 l_view_by ISC_DEPOT_RPT_UTIL_PKG.g_view_by_typ%type;
783 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG.g_view_by_col_typ%type;
784 l_xtd ISC_DEPOT_RPT_UTIL_PKG.g_xtd_typ%type;
785 l_comparison_type VARCHAR2(1);
786 l_cur_suffix VARCHAR2(2);
787 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
788 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
789 l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type;
790 l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type;
791 l_mv_set VARCHAR2(50);
792 l_aggregation_flag NUMBER;
793 l_custom_rec BIS_QUERY_ATTRIBUTES;
794 l_mv_type VARCHAR2(10);
795 l_err_stage VARCHAR2(500);
796 l_debug_mode VARCHAR2(1);
797 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type;
798
799 BEGIN
800 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
801 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
802 l_comparison_type := 'Y';
803
804 -- clear out the tables.
805 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
806 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
807
808 -- get all the query parameters
809 ISC_DEPOT_RPT_UTIL_PKG.process_parameters (p_param => p_param,
810 x_view_by => l_view_by,
811 x_view_by_col_name => l_view_by_col,
812 x_comparison_type => l_comparison_type,
813 x_xtd => l_xtd,
814 x_cur_suffix => l_cur_suffix,
815 x_where_clause => l_where_clause,
816 x_mv => l_mv,
817 x_join_tbl => l_join_tbl,
818 x_mv_type => l_mv_type,
819 x_aggregation_flag => l_aggregation_flag,
820 p_trend => 'N',
821 p_mv_set => 'COSTS',
822 x_custom_output => x_custom_output);
823
824 -- The measure columns that need to be aggregated
825 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
826 p_col_name => 'material_cost_' || l_cur_suffix,
827 p_alias_name => 'm_cost',
828 p_grand_total => 'Y',
829 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
830
831 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
832 p_col_name => 'labor_cost_' || l_cur_suffix,
833 p_alias_name => 'l_cost',
834 p_grand_total => 'Y',
835 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
836
837 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
838 p_col_name => 'expense_cost_' || l_cur_suffix,
839 p_alias_name => 'e_cost',
840 p_grand_total => 'Y',
841 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
842
843 l_join_tbl(l_join_tbl.count).additional_where_clause := l_join_tbl(l_join_tbl.count).additional_where_clause;
844
845 -- construct the query
846 l_query := get_cst_summ_tbl_sel_clause (l_view_by, l_view_by_col)
847 || ' from '
848 || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
849 p_where_clause => l_where_clause,
850 p_join_tables => l_join_tbl,
851 p_use_windowing => 'Y',
852 p_col_name => l_col_tbl,
853 p_use_grpid => 'N',
854 p_paren_count => 3,
855 p_filter_where => '(BIV_MEASURE7 > 0 OR BIV_MEASURE8 > 0)',
856 p_generate_viewby => 'Y',
857 p_in_join_tables => NULL);
858
859 -- prepare output for bind variables
860 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
861 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
862
863 -- set the basic bind variables for the status SQL
864 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
865
866 l_custom_rec.attribute_name := ':AGGREGATION_FLAG';
867 l_custom_rec.attribute_value := l_aggregation_flag;
868 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
869 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
870 x_custom_output.extend;
871 x_custom_output(x_custom_output.count) := l_custom_rec;
872
873 x_custom_sql := l_query;
874 EXCEPTION
875 WHEN OTHERS THEN
876 l_err_stage := SQLERRM;
877
878 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
879 l_err_stage:='The exception is : ';
880 ISC_DEPOT_RPT_UTIL_PKG.write('get_cst_summ_tbl_sql : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
881 END IF;
882
883 END get_cst_summ_tbl_sql;
884
885 FUNCTION get_cst_summ_tbl_sel_clause (p_view_by_dim IN VARCHAR2, p_view_by_col IN VARCHAR2)
886 return VARCHAR2
887 IS
888
889 l_sel_clause varchar2(8000);
890 l_p_total_cost varchar2 (500);
891 l_c_total_cost varchar2 (500);
892 l_p_total_cost_total varchar2 (500);
893 l_c_total_cost_total varchar2 (500);
894 l_description varchar2(30);
895 l_cat_drill_down varchar2(500);
896
897 BEGIN
898 l_description := 'null';
899 l_cat_drill_down := 'null';
900
901 l_p_total_cost := '(NVL (p_m_cost, 0) + NVL (p_l_cost, 0) + NVL (p_e_cost, 0))';
902 l_c_total_cost := '(NVL (c_m_cost, 0) + NVL (c_l_cost, 0) + NVL (c_e_cost, 0))';
903 l_p_total_cost_total := '(NVL (p_m_cost_total, 0) + NVL (p_l_cost_total, 0) + ' ||
904 'NVL (p_e_cost_total, 0))';
905 l_c_total_cost_total := '(NVL (c_m_cost_total, 0) + NVL (c_l_cost_total, 0) + ' ||
906 'NVL (c_e_cost_total, 0))';
907
908 IF p_view_by_dim = 'ITEM+ENI_ITEM_VBH_CAT' THEN
909 l_cat_drill_down := 'decode(v.leaf_node_flag, ''Y'', ' ||
910 '''pFunctionName=ISC_DEPOT_COSTS_TBL_REP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM&pParamIds=Y'', ' ||
911 '''pFunctionName=ISC_DEPOT_COSTS_TBL_REP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'') ';
912 ELSIF p_view_by_dim = 'ITEM+ENI_ITEM' THEN
913 l_description := 'v.description';
914 END IF;
915
916 l_sel_clause :=
917 ' SELECT ' ||
918 ISC_DEPOT_RPT_UTIL_PKG.get_viewby_select_clause (p_view_by_dim) || fnd_global.newline ||
919 l_description || ' BIV_ATTRIBUTE1 ' || fnd_global.newline || ',' ||
920 'BIV_MEASURE1 ' || fnd_global.newline || ',' ||
921 'BIV_MEASURE2 ' || fnd_global.newline || ',' ||
922 'BIV_MEASURE3 ' || fnd_global.newline || ',' ||
923 'BIV_MEASURE4 ' || fnd_global.newline || ',' ||
924 'BIV_MEASURE5 ' || fnd_global.newline || ',' ||
925 'BIV_MEASURE6 ' || fnd_global.newline || ',' ||
926 'BIV_MEASURE7 ' || fnd_global.newline || ',' ||
927 'BIV_MEASURE8 ' || fnd_global.newline || ',' ||
928 'BIV_MEASURE9 ' || fnd_global.newline || ',' ||
929 'BIV_MEASURE10 ' || fnd_global.newline || ',' ||
930 'BIV_MEASURE11 ' || fnd_global.newline || ',' ||
931 'BIV_MEASURE12 ' || fnd_global.newline || ',' ||
932 'BIV_MEASURE13 ' || fnd_global.newline || ',' ||
933 'BIV_MEASURE14 ' || fnd_global.newline || ',' ||
934 'BIV_MEASURE15 ' || fnd_global.newline || ',' ||
935 'BIV_MEASURE16 ' || fnd_global.newline || ',' ||
936 'BIV_MEASURE17 ' || fnd_global.newline || ',' ||
937 l_cat_drill_down || ' BIV_DYNAMIC_URL1 ' || fnd_global.newline ||
938 'FROM ( SELECT ' || fnd_global.newline ||
939 ' rank() over (&ORDER_BY_CLAUSE'||' nulls last, '|| p_view_by_col ||' ) - 1 rnk' || fnd_global.newline || ', ' ||
940 p_view_by_col || fnd_global.newline || ', ' ||
941 'BIV_MEASURE1 ' || fnd_global.newline || ', ' ||
942 'BIV_MEASURE2 ' || fnd_global.newline || ', ' ||
943 'BIV_MEASURE3 ' || fnd_global.newline || ', ' ||
944 'BIV_MEASURE4 ' || fnd_global.newline || ', ' ||
945 'BIV_MEASURE5 ' || fnd_global.newline || ', ' ||
946 'BIV_MEASURE6 ' || fnd_global.newline || ', ' ||
947 'BIV_MEASURE7 ' || fnd_global.newline || ', ' ||
948 'BIV_MEASURE8 ' || fnd_global.newline || ', ' ||
949 'BIV_MEASURE9 ' || fnd_global.newline || ', ' ||
950 'BIV_MEASURE10 ' || fnd_global.newline || ', ' ||
951 'BIV_MEASURE11 ' || fnd_global.newline || ', ' ||
952 'BIV_MEASURE12 ' || fnd_global.newline || ', ' ||
953 'BIV_MEASURE13 ' || fnd_global.newline || ', ' ||
954 'BIV_MEASURE14 ' || fnd_global.newline || ', ' ||
955 'BIV_MEASURE15 ' || fnd_global.newline || ', ' ||
956 'BIV_MEASURE16 ' || fnd_global.newline || ', ' ||
957 'BIV_MEASURE17 ' || fnd_global.newline ||
958 'FROM (' || fnd_global.newline ||
959 ' SELECT ' || p_view_by_col || fnd_global.newline || ', ' ||
960 'NVL (c_m_cost, 0) BIV_MEASURE1 ' || fnd_global.newline || ', ' ||
961 poa_dbi_util_pkg.change_clause ('c_m_cost', 'p_m_cost') || ' BIV_MEASURE2 ' || fnd_global.newline || ', ' ||
962 'NVL (c_l_cost, 0) BIV_MEASURE3 ' || fnd_global.newline || ', ' ||
963 poa_dbi_util_pkg.change_clause ('c_l_cost', 'p_l_cost') || ' BIV_MEASURE4 ' || fnd_global.newline || ', ' ||
964 'NVL (c_e_cost,0) BIV_MEASURE5 ' || fnd_global.newline || ', ' ||
965 poa_dbi_util_pkg.change_clause ('c_e_cost', 'p_e_cost') || ' BIV_MEASURE6 ' || fnd_global.newline || ', ' ||
966 l_p_total_cost || ' BIV_MEASURE7 ' || fnd_global.newline || ', ' ||
967 l_c_total_cost || ' BIV_MEASURE8 ' || fnd_global.newline || ', ' ||
968 poa_dbi_util_pkg.change_clause (l_c_total_cost, l_p_total_cost) || ' BIV_MEASURE9 ' || fnd_global.newline || ', ' ||
969 'NVL (c_m_cost_total, 0) BIV_MEASURE10 ' || fnd_global.newline || ', ' ||
970 poa_dbi_util_pkg.change_clause ('c_m_cost_total', 'p_m_cost_total') || ' BIV_MEASURE11 ' || fnd_global.newline || ', ' ||
971 'NVL (c_l_cost_total, 0) BIV_MEASURE12 ' || fnd_global.newline || ', ' ||
972 poa_dbi_util_pkg.change_clause ('c_l_cost_total', 'p_l_cost_total') || ' BIV_MEASURE13 ' || fnd_global.newline || ', ' ||
973 'NVL (c_e_cost_total, 0) BIV_MEASURE14 ' || fnd_global.newline || ', ' ||
974 poa_dbi_util_pkg.change_clause ('c_e_cost_total', 'p_e_cost_total') || ' BIV_MEASURE15 ' || fnd_global.newline || ', ' ||
975 l_c_total_cost_total || ' BIV_MEASURE16 ' || fnd_global.newline || ', ' ||
976 poa_dbi_util_pkg.change_clause (l_c_total_cost_total, l_p_total_cost_total) || ' BIV_MEASURE17 ' || fnd_global.newline;
977
978 RETURN l_sel_clause;
979 END get_cst_summ_tbl_sel_clause;
980
981 /*----------------------------------
982 Cost Summary Trend
983 ----------------------------------*/
984 PROCEDURE get_cst_summ_trd_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
985 x_custom_sql OUT NOCOPY VARCHAR2,
986 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
987 IS
988 l_query ISC_DEPOT_RPT_UTIL_PKG.g_query_typ%type;
989 l_view_by ISC_DEPOT_RPT_UTIL_PKG.g_view_by_typ%type;
990 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG.g_view_by_col_typ%type;
991 l_xtd ISC_DEPOT_RPT_UTIL_PKG.g_xtd_typ%type;
992 l_comparison_type VARCHAR2(1);
993 l_cur_suffix VARCHAR2(2);
994 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
995 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
996 l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type;
997 l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type;
998 l_mv_set VARCHAR2(50);
999 l_aggregation_flag NUMBER;
1000 l_custom_rec BIS_QUERY_ATTRIBUTES;
1001 l_mv_type VARCHAR2(10);
1002 l_err_stage VARCHAR2(500);
1003 l_debug_mode VARCHAR2(1);
1004 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type;
1005
1006 BEGIN
1007 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1008 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
1009 l_comparison_type := 'Y';
1010 -- clear out the tables.
1011 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
1012 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
1013
1014 -- get all the query parameters
1015 ISC_DEPOT_RPT_UTIL_PKG.process_parameters (p_param => p_param,
1016 x_view_by => l_view_by,
1017 x_view_by_col_name => l_view_by_col,
1018 x_comparison_type => l_comparison_type,
1019 x_xtd => l_xtd,
1020 x_cur_suffix => l_cur_suffix,
1021 x_where_clause => l_where_clause,
1022 x_mv => l_mv,
1023 x_join_tbl => l_join_tbl,
1024 x_mv_type => l_mv_type,
1025 x_aggregation_flag => l_aggregation_flag,
1026 p_trend => 'Y',
1027 p_mv_set => 'COSTS',
1028 x_custom_output => x_custom_output);
1029
1030 -- The measure columns that need to be aggregated
1031 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1032 p_col_name => 'material_cost_' || l_cur_suffix,
1033 p_alias_name => 'm_cost',
1034 p_grand_total => 'N',
1035 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
1036
1037 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1038 p_col_name => 'labor_cost_' || l_cur_suffix,
1039 p_alias_name => 'l_cost',
1040 p_grand_total => 'N',
1041 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
1042
1043 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1044 p_col_name => 'expense_cost_' || l_cur_suffix,
1045 p_alias_name => 'e_cost',
1046 p_grand_total => 'N',
1047 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
1048
1049 -- Joining Outer and Inner Query
1050 l_query := get_cst_summ_trd_sel_clause (l_view_by) ||
1051 ' from ' ||
1052 poa_dbi_template_pkg.trend_sql (p_xtd => l_xtd,
1053 p_comparison_type => l_comparison_type,
1054 p_fact_name => l_mv,
1055 p_where_clause => l_where_clause,
1056 p_col_name => l_col_tbl,
1057 p_use_grpid => 'N',
1058 p_in_join_tables => NULL);
1059
1060 -- Prepare PMV bind variables
1061 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
1062 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1063
1064 -- get all the basic binds used by POA queries
1065 -- Do this before adding any of our binds, since the procedure
1066 -- reinitializes the output table
1067 poa_dbi_util_pkg.get_custom_trend_binds (p_xtd => l_xtd,
1068 p_comparison_type => l_comparison_type,
1069 x_custom_output => x_custom_output);
1070
1071 l_custom_rec.attribute_name := ':AGGREGATION_FLAG';
1072 l_custom_rec.attribute_value := l_aggregation_flag;
1073 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1074 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1075 x_custom_output.extend;
1076 x_custom_output(x_custom_output.count) := l_custom_rec;
1077
1078 x_custom_sql := l_query;
1079 EXCEPTION
1080 WHEN OTHERS THEN
1081 l_err_stage := SQLERRM;
1082
1083 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
1084 l_err_stage:='The exception is : ';
1085 ISC_DEPOT_RPT_UTIL_PKG.write('get_cst_summ_trd_sql : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
1086 END IF;
1087
1088 END get_cst_summ_trd_sql;
1089
1090
1091 /*
1092 The outer main query for the trend SQL.
1093 */
1094 FUNCTION get_cst_summ_trd_sel_clause(p_view_by_dim IN VARCHAR2)
1095 return VARCHAR2
1096 IS
1097
1098 l_sel_clause varchar2(4500);
1099 l_p_total_cost varchar2 (500);
1100 l_c_total_cost varchar2 (500);
1101
1102
1103 BEGIN
1104 l_p_total_cost := '(NVL (iset.p_m_cost, 0) + NVL (iset.p_l_cost, 0) + NVL (iset.p_e_cost, 0))';
1105 l_c_total_cost := '(NVL (iset.c_m_cost, 0) + NVL (iset.c_l_cost, 0) + NVL (iset.c_e_cost, 0))';
1106
1107 l_sel_clause :=
1108 ' SELECT ' ||
1109 ' cal.name VIEWBY ' || fnd_global.newline || ', ' ||
1110 'NVL (iset.c_m_cost, 0) BIV_MEASURE1 ' || fnd_global.newline || ', ' ||
1111 poa_dbi_util_pkg.change_clause ('iset.c_m_cost', 'iset.p_m_cost') || ' BIV_MEASURE2 ' || fnd_global.newline || ', ' ||
1112 'NVL (iset.c_l_cost, 0) BIV_MEASURE3 ' || fnd_global.newline || ', ' ||
1113 poa_dbi_util_pkg.change_clause ('iset.c_l_cost', 'iset.p_l_cost') || ' BIV_MEASURE4 ' || fnd_global.newline || ', ' ||
1114 'NVL (iset.c_e_cost, 0) BIV_MEASURE5 ' || fnd_global.newline || ', ' ||
1115 poa_dbi_util_pkg.change_clause ('iset.c_e_cost', 'iset.p_e_cost') || ' BIV_MEASURE6 ' || fnd_global.newline || ', ' ||
1116 l_p_total_cost || ' BIV_MEASURE7 ' || fnd_global.newline || ', ' ||
1117 l_c_total_cost || ' BIV_MEASURE8 ' || fnd_global.newline || ', ' ||
1118 poa_dbi_util_pkg.change_clause (l_c_total_cost, l_p_total_cost) || ' BIV_MEASURE9 ' || fnd_global.newline;
1119 RETURN l_sel_clause;
1120 END get_cst_summ_trd_sel_clause;
1121
1122 /*----------------------------------
1123 Margin Summary Table
1124 ----------------------------------*/
1125
1126 PROCEDURE get_mrg_summ_tbl_sql(
1127 p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
1128 x_custom_sql OUT NOCOPY VARCHAR2,
1129 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
1130 ) IS
1131
1132 l_query ISC_DEPOT_RPT_UTIL_PKG.g_query_typ%type;
1133 l_view_by ISC_DEPOT_RPT_UTIL_PKG.g_view_by_typ%type;
1134 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG.g_view_by_col_typ%type;
1135 l_xtd ISC_DEPOT_RPT_UTIL_PKG.g_xtd_typ%type;
1136 l_comparison_type VARCHAR2(1);
1137 l_cur_suffix VARCHAR2(2);
1138 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1139 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1140 l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type;
1141 l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type;
1142 l_mv_set VARCHAR2(50);
1143 l_aggregation_flag NUMBER;
1144 l_custom_rec BIS_QUERY_ATTRIBUTES;
1145 l_mv_type VARCHAR2(10);
1146 l_err_stage VARCHAR2(500);
1147 l_debug_mode VARCHAR2(1);
1148 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type;
1149 l_supress VARCHAR2 (200);
1150
1151 BEGIN
1152 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1153 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
1154 l_comparison_type := 'Y';
1155
1156 -- clear out the tables.
1157 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
1158 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
1159
1160 -- get all the query parameters
1161 ISC_DEPOT_RPT_UTIL_PKG.process_parameters (p_param => p_param,
1162 x_view_by => l_view_by,
1163 x_view_by_col_name => l_view_by_col,
1164 x_comparison_type => l_comparison_type,
1165 x_xtd => l_xtd,
1166 x_cur_suffix => l_cur_suffix,
1167 x_where_clause => l_where_clause,
1168 x_mv => l_mv,
1169 x_join_tbl => l_join_tbl,
1170 x_mv_type => l_mv_type,
1171 x_aggregation_flag => l_aggregation_flag,
1172 p_trend => 'N',
1173 p_mv_set => 'MARGIN',
1174 x_custom_output => x_custom_output);
1175
1176 -- The measure columns that need to be aggregated
1177 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1178 p_col_name => 'nvl(material_charges_' || l_cur_suffix || ', 0) - '
1179 || 'nvl(material_cost_' || l_cur_suffix || ', 0)',
1180 p_alias_name => 'm_margin',
1181 p_grand_total => 'Y',
1182 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
1183
1184 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1185 p_col_name => 'nvl(labor_charges_' || l_cur_suffix || ', 0) - '
1186 || 'nvl(labor_cost_' || l_cur_suffix || ', 0)',
1187 p_alias_name => 'l_margin',
1188 p_grand_total => 'Y',
1189 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
1190
1191 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1192 p_col_name => 'nvl(expense_charges_' || l_cur_suffix || ', 0) - '
1193 || 'nvl(expense_cost_' || l_cur_suffix || ', 0)',
1194 p_alias_name => 'e_margin',
1195 p_grand_total => 'Y',
1196 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
1197
1198 l_join_tbl(l_join_tbl.count).additional_where_clause := l_join_tbl(l_join_tbl.count).additional_where_clause;
1199
1200
1201 -- construct the query
1202 l_supress := ' and (nvl(material_charges_' || l_cur_suffix || ', 0) > 0 OR nvl(labor_charges_' || l_cur_suffix || ', 0) > 0 '
1203 || 'OR nvl(expense_charges_' || l_cur_suffix || ', 0) > 0 OR nvl(material_cost_' || l_cur_suffix || ', 0) > 0 '
1204 || 'OR nvl(labor_cost_' || l_cur_suffix || ', 0) > 0 OR nvl(expense_cost_' || l_cur_suffix || ', 0) > 0) ';
1205
1206 l_query := get_mrg_summ_tbl_sel_clause (l_view_by, l_view_by_col)
1207 || ' from
1208 ' || poa_dbi_template_pkg.status_sql (p_fact_name => l_mv,
1209 p_where_clause => l_where_clause || l_supress,
1210 p_join_tables => l_join_tbl,
1211 p_use_windowing => 'Y',
1212 p_col_name => l_col_tbl,
1213 p_use_grpid => 'N',
1214 p_paren_count => 3,
1215 p_filter_where => NULL,
1216 p_generate_viewby => 'Y',
1217 p_in_join_tables => NULL);
1218
1219 -- prepare output for bind variables
1220 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
1221 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1222
1223 -- set the basic bind variables for the status SQL
1224 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
1225
1226 l_custom_rec.attribute_name := ':AGGREGATION_FLAG';
1227 l_custom_rec.attribute_value := l_aggregation_flag;
1228 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1229 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1230 x_custom_output.extend;
1231 x_custom_output(x_custom_output.count) := l_custom_rec;
1232
1233 x_custom_sql := l_query;
1234 EXCEPTION
1235 WHEN OTHERS THEN
1236 l_err_stage := SQLERRM;
1237
1238 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
1239 l_err_stage:='The exception is : ';
1240 ISC_DEPOT_RPT_UTIL_PKG.write('get_mrg_summ_tbl_sql : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
1241 END IF;
1242
1243 END get_mrg_summ_tbl_sql;
1244
1245 FUNCTION get_mrg_summ_tbl_sel_clause (p_view_by_dim IN VARCHAR2, p_view_by_col IN VARCHAR2)
1246 return VARCHAR2
1247 IS
1248
1249 l_sel_clause varchar2(8000);
1250 l_p_total_margin varchar2 (500);
1251 l_c_total_margin varchar2 (500);
1252 l_p_total_margin_total varchar2 (500);
1253 l_c_total_margin_total varchar2 (500);
1254 l_description varchar2(30);
1255 l_cat_drill_down varchar2 (500);
1256
1257 BEGIN
1258 l_description := 'null';
1259 l_cat_drill_down := 'null';
1260
1261 l_p_total_margin := '(NVL (p_m_margin, 0) + NVL (p_l_margin, 0) + NVL (p_e_margin, 0))';
1262 l_c_total_margin := '(NVL (c_m_margin, 0) + NVL (c_l_margin, 0) + NVL (c_e_margin, 0))';
1263 l_p_total_margin_total := '(NVL (p_m_margin_total, 0) + NVL (p_l_margin_total, 0) + ' ||
1264 'NVL (p_e_margin_total, 0))';
1265 l_c_total_margin_total := '(NVL (c_m_margin_total, 0) + NVL (c_l_margin_total, 0) + ' ||
1266 'NVL (c_e_margin_total, 0))';
1267
1268 IF p_view_by_dim = 'ITEM+ENI_ITEM_VBH_CAT' THEN
1269 l_cat_drill_down := 'decode(v.leaf_node_flag, ''Y'', ' ||
1270 '''pFunctionName=ISC_DEPOT_MARGIN_TBL_REP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM&pParamIds=Y'', ' ||
1271 '''pFunctionName=ISC_DEPOT_MARGIN_TBL_REP&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'') ';
1272 ELSIF p_view_by_dim = 'ITEM+ENI_ITEM' THEN
1273 l_description := 'v.description';
1274 END IF;
1275
1276 l_sel_clause :=
1277 ' SELECT ' ||
1278 ISC_DEPOT_RPT_UTIL_PKG.get_viewby_select_clause (p_view_by_dim) || fnd_global.newline ||
1279 l_description || ' BIV_ATTRIBUTE1 ' || fnd_global.newline || ',' ||
1280 'BIV_MEASURE1 ' || fnd_global.newline || ',' ||
1281 'BIV_MEASURE2 ' || fnd_global.newline || ',' ||
1282 'BIV_MEASURE3 ' || fnd_global.newline || ',' ||
1283 'BIV_MEASURE4 ' || fnd_global.newline || ',' ||
1284 'BIV_MEASURE5 ' || fnd_global.newline || ',' ||
1285 'BIV_MEASURE6 ' || fnd_global.newline || ',' ||
1286 'BIV_MEASURE7 ' || fnd_global.newline || ',' ||
1287 'BIV_MEASURE8 ' || fnd_global.newline || ',' ||
1288 'BIV_MEASURE9 ' || fnd_global.newline || ',' ||
1289 'BIV_MEASURE10 ' || fnd_global.newline || ',' ||
1290 'BIV_MEASURE11 ' || fnd_global.newline || ',' ||
1291 'BIV_MEASURE12 ' || fnd_global.newline || ',' ||
1292 'BIV_MEASURE13 ' || fnd_global.newline || ',' ||
1293 'BIV_MEASURE14 ' || fnd_global.newline || ',' ||
1294 'BIV_MEASURE15 ' || fnd_global.newline || ',' ||
1295 'BIV_MEASURE16 ' || fnd_global.newline || ',' ||
1296 'BIV_MEASURE17 ' || fnd_global.newline || ',' ||
1297 l_cat_drill_down || ' BIV_DYNAMIC_URL1 ' || fnd_global.newline ||
1298 'FROM ( SELECT ' || fnd_global.newline ||
1299 ' rank() over (&ORDER_BY_CLAUSE'||' nulls last, '|| p_view_by_col ||' ) - 1 rnk' || fnd_global.newline || ', ' ||
1300 p_view_by_col || fnd_global.newline || ', ' ||
1301 'BIV_MEASURE1 ' || fnd_global.newline || ', ' ||
1302 'BIV_MEASURE2 ' || fnd_global.newline || ', ' ||
1303 'BIV_MEASURE3 ' || fnd_global.newline || ', ' ||
1304 'BIV_MEASURE4 ' || fnd_global.newline || ', ' ||
1305 'BIV_MEASURE5 ' || fnd_global.newline || ', ' ||
1306 'BIV_MEASURE6 ' || fnd_global.newline || ', ' ||
1307 'BIV_MEASURE7 ' || fnd_global.newline || ', ' ||
1308 'BIV_MEASURE8 ' || fnd_global.newline || ', ' ||
1309 'BIV_MEASURE9 ' || fnd_global.newline || ', ' ||
1310 'BIV_MEASURE10 ' || fnd_global.newline || ', ' ||
1311 'BIV_MEASURE11 ' || fnd_global.newline || ', ' ||
1312 'BIV_MEASURE12 ' || fnd_global.newline || ', ' ||
1313 'BIV_MEASURE13 ' || fnd_global.newline || ', ' ||
1314 'BIV_MEASURE14 ' || fnd_global.newline || ', ' ||
1315 'BIV_MEASURE15 ' || fnd_global.newline || ', ' ||
1316 'BIV_MEASURE16 ' || fnd_global.newline || ', ' ||
1317 'BIV_MEASURE17 ' || fnd_global.newline ||
1318 'FROM (' || fnd_global.newline ||
1319 ' SELECT ' || p_view_by_col || fnd_global.newline || ', ' ||
1320 'NVL (c_m_margin, 0) BIV_MEASURE1 ' || fnd_global.newline || ', ' ||
1321 poa_dbi_util_pkg.change_clause ('c_m_margin', 'p_m_margin') || ' BIV_MEASURE2 ' || fnd_global.newline || ', ' ||
1322 'NVL (c_l_margin, 0) BIV_MEASURE3 ' || fnd_global.newline || ', ' ||
1323 poa_dbi_util_pkg.change_clause ('c_l_margin', 'p_l_margin') || ' BIV_MEASURE4 ' || fnd_global.newline || ', ' ||
1324 'NVL (c_e_margin,0) BIV_MEASURE5 ' || fnd_global.newline || ', ' ||
1325 poa_dbi_util_pkg.change_clause ('c_e_margin', 'p_e_margin') || ' BIV_MEASURE6 ' || fnd_global.newline || ', ' ||
1326 l_p_total_margin || ' BIV_MEASURE7 ' || fnd_global.newline || ', ' ||
1327 l_c_total_margin || ' BIV_MEASURE8 ' || fnd_global.newline || ', ' ||
1328 poa_dbi_util_pkg.change_clause (l_c_total_margin, l_p_total_margin) || ' BIV_MEASURE9 ' || fnd_global.newline || ', ' ||
1329 'NVL (c_m_margin_total, 0) BIV_MEASURE10 ' || fnd_global.newline || ', ' ||
1330 poa_dbi_util_pkg.change_clause ('c_m_margin_total', 'p_m_margin_total') || ' BIV_MEASURE11 ' || fnd_global.newline || ', ' ||
1331 'NVL (c_l_margin_total, 0) BIV_MEASURE12 ' || fnd_global.newline || ', ' ||
1332 poa_dbi_util_pkg.change_clause ('c_l_margin_total', 'p_l_margin_total') || ' BIV_MEASURE13 ' || fnd_global.newline || ', ' ||
1333 'NVL (c_e_margin_total, 0) BIV_MEASURE14 ' || fnd_global.newline || ', ' ||
1334 poa_dbi_util_pkg.change_clause ('c_e_margin_total', 'p_e_margin_total') || ' BIV_MEASURE15 ' || fnd_global.newline || ', ' ||
1335 l_c_total_margin_total || ' BIV_MEASURE16 ' || fnd_global.newline || ', ' ||
1336 poa_dbi_util_pkg.change_clause (l_c_total_margin_total, l_p_total_margin_total) || ' BIV_MEASURE17 ' || fnd_global.newline;
1337
1338
1339 RETURN l_sel_clause;
1340 END get_mrg_summ_tbl_sel_clause;
1341
1342 /*----------------------------------
1343 Margin Summary Trend
1344 ----------------------------------*/
1345 PROCEDURE get_mrg_summ_trd_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
1346 x_custom_sql OUT NOCOPY VARCHAR2,
1347 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1348 IS
1349 l_query ISC_DEPOT_RPT_UTIL_PKG.g_query_typ%type;
1350 l_view_by ISC_DEPOT_RPT_UTIL_PKG.g_view_by_typ%type;
1351 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG.g_view_by_col_typ%type;
1352 l_xtd ISC_DEPOT_RPT_UTIL_PKG.g_xtd_typ%type;
1353 l_comparison_type VARCHAR2(1);
1354 l_cur_suffix VARCHAR2(2);
1355 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1356 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1357 l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type;
1358 l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type;
1359 l_mv_set VARCHAR2(50);
1360 l_aggregation_flag NUMBER;
1361 l_custom_rec BIS_QUERY_ATTRIBUTES;
1362 l_mv_type VARCHAR2(10);
1363 l_err_stage VARCHAR2(500);
1364 l_debug_mode VARCHAR2(1);
1365 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type;
1366
1367 BEGIN
1368 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1369 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
1370 l_comparison_type := 'Y';
1371 -- clear out the tables.
1372 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
1373 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
1374
1375 -- get all the query parameters
1376 ISC_DEPOT_RPT_UTIL_PKG.process_parameters (p_param => p_param,
1377 x_view_by => l_view_by,
1378 x_view_by_col_name => l_view_by_col,
1379 x_comparison_type => l_comparison_type,
1380 x_xtd => l_xtd,
1381 x_cur_suffix => l_cur_suffix,
1382 x_where_clause => l_where_clause,
1383 x_mv => l_mv,
1384 x_join_tbl => l_join_tbl,
1385 x_mv_type => l_mv_type,
1386 x_aggregation_flag => l_aggregation_flag,
1387 p_trend => 'Y',
1388 p_mv_set => 'MARGIN',
1389 x_custom_output => x_custom_output);
1390
1391 -- The measure columns that need to be aggregated
1392
1393 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1394 p_col_name => 'nvl(material_charges_' || l_cur_suffix || ', 0) - '
1395 || 'nvl(material_cost_' || l_cur_suffix || ', 0)',
1396 p_alias_name => 'm_margin',
1397 p_grand_total => 'N',
1398 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
1399
1400 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1401 p_col_name => 'nvl(labor_charges_' || l_cur_suffix || ', 0) - '
1402 || 'nvl(labor_cost_' || l_cur_suffix || ', 0)',
1403 p_alias_name => 'l_margin',
1404 p_grand_total => 'N',
1405 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
1406
1407 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl,
1408 p_col_name => 'nvl(expense_charges_' || l_cur_suffix || ', 0) - '
1409 || 'nvl(expense_cost_' || l_cur_suffix || ', 0)',
1410 p_alias_name => 'e_margin',
1411 p_grand_total => 'N',
1412 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS);
1413
1414 -- Joining Outer and Inner Query
1415 l_query := get_mrg_summ_trd_sel_clause (l_view_by) ||
1416 ' from ' ||
1417 poa_dbi_template_pkg.trend_sql (p_xtd => l_xtd,
1418 p_comparison_type => l_comparison_type,
1419 p_fact_name => l_mv,
1420 p_where_clause => l_where_clause,
1421 p_col_name => l_col_tbl,
1422 p_use_grpid => 'N',
1423 p_in_join_tables => NULL);
1424
1425 -- Prepare PMV bind variables
1426 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
1427 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
1428
1429 -- get all the basic binds used by POA queries
1430 -- Do this before adding any of our binds, since the procedure
1431 -- reinitializes the output table
1432 poa_dbi_util_pkg.get_custom_trend_binds (p_xtd => l_xtd,
1433 p_comparison_type => l_comparison_type,
1434 x_custom_output => x_custom_output);
1435
1436 l_custom_rec.attribute_name := ':AGGREGATION_FLAG';
1437 l_custom_rec.attribute_value := l_aggregation_flag;
1438 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1439 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1440 x_custom_output.extend;
1441 x_custom_output(x_custom_output.count) := l_custom_rec;
1442
1443 x_custom_sql := l_query;
1444 EXCEPTION
1445 WHEN OTHERS THEN
1446 l_err_stage := SQLERRM;
1447
1448 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
1449 l_err_stage:='The exception is : ';
1450 ISC_DEPOT_RPT_UTIL_PKG.write('get_mrg_summ_trd_sql : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
1451 END IF;
1452
1453 END get_mrg_summ_trd_sql;
1454
1455
1456 /*
1457 The outer main query for the trend SQL.
1458 */
1459 FUNCTION get_mrg_summ_trd_sel_clause(p_view_by_dim IN VARCHAR2)
1460 return VARCHAR2
1461 IS
1462
1463 l_sel_clause varchar2(8000);
1464 l_p_total_margin varchar2 (500);
1465 l_c_total_margin varchar2 (500);
1466
1467
1468 BEGIN
1469 l_p_total_margin := '(NVL (iset.p_m_margin, 0) + NVL (iset.p_l_margin, 0) + NVL (iset.p_e_margin, 0))';
1470 l_c_total_margin := '(NVL (iset.c_m_margin, 0) + NVL (iset.c_l_margin, 0) + NVL (iset.c_e_margin, 0))';
1471
1472 l_sel_clause :=
1473 ' SELECT ' ||
1474 ' cal.name VIEWBY ' || fnd_global.newline || ', ' ||
1475 'NVL (iset.c_m_margin, 0) BIV_MEASURE1 ' || fnd_global.newline || ', ' ||
1476 poa_dbi_util_pkg.change_clause ('iset.c_m_margin', 'iset.p_m_margin') || ' BIV_MEASURE2 ' || fnd_global.newline || ', ' ||
1477 'NVL (iset.c_l_margin, 0) BIV_MEASURE3 ' || fnd_global.newline || ', ' ||
1478 poa_dbi_util_pkg.change_clause ('iset.c_l_margin', 'iset.p_l_margin') || ' BIV_MEASURE4 ' || fnd_global.newline || ', ' ||
1479 'NVL (iset.c_e_margin, 0) BIV_MEASURE5 ' || fnd_global.newline || ', ' ||
1480 poa_dbi_util_pkg.change_clause ('iset.c_e_margin', 'iset.p_e_margin') || ' BIV_MEASURE6 ' || fnd_global.newline || ', ' ||
1481 l_p_total_margin || ' BIV_MEASURE7 ' || fnd_global.newline || ', ' ||
1482 l_c_total_margin || ' BIV_MEASURE8 ' || fnd_global.newline || ', ' ||
1483 poa_dbi_util_pkg.change_clause (l_c_total_margin, l_p_total_margin) || ' BIV_MEASURE9 ' || fnd_global.newline;
1484 RETURN l_sel_clause;
1485 END get_mrg_summ_trd_sel_clause;
1486
1487 /*----------------------------------
1488 Repair Order Margin Detail
1489 ----------------------------------*/
1490
1491 PROCEDURE get_mrg_dtl_tbl_sql(
1492 p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
1493 x_custom_sql OUT NOCOPY VARCHAR2,
1494 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL
1495 ) IS
1496
1497 l_query ISC_DEPOT_RPT_UTIL_PKG.g_query_typ%type;
1498 l_view_by ISC_DEPOT_RPT_UTIL_PKG.g_view_by_typ%type;
1499 l_view_by_col ISC_DEPOT_RPT_UTIL_PKG.g_view_by_col_typ%type;
1500 l_xtd ISC_DEPOT_RPT_UTIL_PKG.g_xtd_typ%type;
1501 l_comparison_type VARCHAR2(1);
1502 l_cur_suffix VARCHAR2(2);
1503 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
1504 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
1505 l_where_clause ISC_DEPOT_RPT_UTIL_PKG.g_where_clause_typ%type;
1506 l_mv ISC_DEPOT_RPT_UTIL_PKG.g_mv_typ%type;
1507 l_mv_set VARCHAR2(50);
1508 l_aggregation_flag NUMBER;
1509 l_custom_rec BIS_QUERY_ATTRIBUTES;
1510 l_mv_type VARCHAR2(10);
1511 l_err_stage VARCHAR2(500);
1512 l_debug_mode VARCHAR2(1);
1513 l_module_name ISC_DEPOT_RPT_UTIL_PKG.g_module_name_typ%type;
1514
1515 BEGIN
1516 l_debug_mode := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
1517 l_module_name := FND_PROFILE.value('AFLOG_MODULE');
1518 l_comparison_type := 'Y';
1519 -- clear out the tables.
1520 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
1521 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
1522
1523 -- get all the query parameters
1524 ISC_DEPOT_RPT_UTIL_PKG.process_parameters (p_param => p_param,
1525 x_view_by => l_view_by,
1526 x_view_by_col_name => l_view_by_col,
1527 x_comparison_type => l_comparison_type,
1528 x_xtd => l_xtd,
1529 x_cur_suffix => l_cur_suffix,
1530 x_where_clause => l_where_clause,
1531 x_mv => l_mv,
1532 x_join_tbl => l_join_tbl,
1533 x_mv_type => l_mv_type,
1534 x_aggregation_flag => l_aggregation_flag,
1535 p_trend => 'N',
1536 p_mv_set => 'MRGN_DTL',
1537 x_custom_output => x_custom_output);
1538
1539 l_query := get_mrg_dtl_sel_clause (l_cur_suffix) ||
1540 ' FROM ' || fnd_global.newline
1541 || l_mv ||
1542 ' ISC_DR_REPAIR_ORDERS_F fact' || fnd_global.newline ||
1543 ' ,ISC_DR_CHARGES_F charges' || fnd_global.newline ||
1544 ' ,ISC_DR_COSTS_F costs' || fnd_global.newline ||
1545 ' ,ENI_ITEM_V eiov ' || fnd_global.newline ||
1546 ' WHERE fact.item_org_id = eiov.id ' || fnd_global.newline ||
1547 ' AND fact.repair_line_id = charges.repair_line_id (+) ' || fnd_global.newline ||
1548 ' AND fact.repair_line_id = costs.repair_line_id (+) ' || fnd_global.newline ||
1549 ' AND (charges.repair_line_id IS NOT NULL OR costs.repair_line_id IS NOT NULL) ' || fnd_global.newline ||
1550 ' AND (costs.labor_cost_'||l_cur_suffix ||'<> 0
1551 or costs.expense_cost_'||l_cur_suffix ||' <> 0
1552 or costs.material_cost_'||l_cur_suffix ||' <> 0
1553 or charges.labor_charges_'||l_cur_suffix ||' <> 0
1554 or charges.expense_charges_'||l_cur_suffix ||' <> 0
1555 or charges.material_charges_'||l_cur_suffix ||' <> 0 )'|| fnd_global.newline ||
1556 ' AND fact.dbi_date_closed BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_ASOF_DATE ' || fnd_global.newline
1557 || l_where_clause || fnd_global.newline
1558 || ' ) ) where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1) '||
1559 'ORDER BY rnk' ;
1560
1561 x_custom_sql := l_query;
1562
1563 EXCEPTION
1564 WHEN OTHERS THEN
1565 l_err_stage := SQLERRM;
1566
1567 IF l_debug_mode = 'Y' and upper(l_module_name) like 'BIS%' THEN
1568 l_err_stage:='The exception is : ';
1569 ISC_DEPOT_RPT_UTIL_PKG.write('get_mrg_dtl_sql : ',l_err_stage,ISC_DEPOT_RPT_UTIL_PKG.C_DEBUG_LEVEL);
1570 END IF;
1571 END get_mrg_dtl_tbl_sql;
1572
1573 FUNCTION get_mrg_dtl_sel_clause (p_curr_suffix IN VARCHAR2)
1574 return VARCHAR2
1575 IS
1576
1577 l_sel_clause varchar2(8000);
1578 l_total_charges varchar2 (500);
1579 l_total_cost varchar2 (500);
1580 l_total_margin varchar2 (500);
1581 l_total_charges_total varchar2 (500);
1582 l_total_cost_total varchar2 (500);
1583 l_total_margin_total varchar2 (500);
1584
1585 BEGIN
1586 l_total_charges := 'NVL(charges.material_charges_' || p_curr_suffix || ', 0) + ' ||
1587 'NVL(charges.labor_charges_' || p_curr_suffix || ', 0) + ' ||
1588 'NVL(charges.expense_charges_' || p_curr_suffix || ', 0)';
1589
1590 l_total_cost := 'NVL(costs.material_cost_' || p_curr_suffix || ', 0) + ' ||
1591 'NVL(costs.labor_cost_' || p_curr_suffix || ', 0) + ' ||
1592 'NVL(costs.expense_cost_' || p_curr_suffix || ', 0)';
1593
1594 l_total_margin := '(NVL(charges.material_charges_' || p_curr_suffix || ', 0) - ' ||
1595 'NVL(costs.material_cost_' || p_curr_suffix || ', 0)) ' ||
1596 '+ (NVL(charges.labor_charges_' || p_curr_suffix || ', 0) - ' ||
1597 'NVL(costs.labor_cost_' || p_curr_suffix || ', 0)) ' ||
1598 '+ (NVL(charges.expense_charges_' || p_curr_suffix || ', 0) - ' ||
1599 'NVL(costs.expense_cost_' || p_curr_suffix || ', 0)) ';
1600
1601 l_total_charges_total := 'NVL(sum(charges.material_charges_' || p_curr_suffix || ') over(), 0) + ' ||
1602 'NVL(sum(charges.labor_charges_' || p_curr_suffix || ') over(), 0) + ' ||
1603 'NVL(sum(charges.expense_charges_' || p_curr_suffix || ') over(), 0)';
1604
1605 l_total_cost_total := 'NVL(sum(costs.material_cost_' || p_curr_suffix || ') over(), 0) + ' ||
1606 'NVL(sum(costs.labor_cost_' || p_curr_suffix || ') over(), 0) + ' ||
1607 'NVL(sum(costs.expense_cost_' || p_curr_suffix || ') over(), 0)';
1608
1609 l_total_margin_total := '(NVL(sum(charges.material_charges_' || p_curr_suffix || ') over(), 0) - ' ||
1610 'NVL(sum(costs.material_cost_' || p_curr_suffix || ') over(), 0)) ' ||
1611 '+ (NVL(sum(charges.labor_charges_' || p_curr_suffix || ') over(), 0) - ' ||
1612 'NVL(sum(costs.labor_cost_' || p_curr_suffix || ') over(), 0)) ' ||
1613 '+ (NVL(sum(charges.expense_charges_' || p_curr_suffix || ') over(), 0) - ' ||
1614 'NVL(sum(costs.expense_cost_' || p_curr_suffix || ') over(), 0)) ';
1615 l_sel_clause :=
1616 'SELECT '||
1617 'BIV_ATTRIBUTE1 ' || fnd_global.newline || ', ' ||
1618 'BIV_ATTRIBUTE2 ' || fnd_global.newline || ', ' ||
1619 'BIV_ATTRIBUTE3 ' || fnd_global.newline || ', ' ||
1620 'BIV_ATTRIBUTE4 ' || fnd_global.newline || ', ' ||
1621 'BIV_ATTRIBUTE5 ' || fnd_global.newline || ', ' ||
1622 'BIV_MEASURE2 ' || fnd_global.newline || ', ' ||
1623 'BIV_MEASURE3 ' || fnd_global.newline || ', ' ||
1624 'BIV_MEASURE4 ' || fnd_global.newline || ', ' ||
1625 'BIV_MEASURE6 ' || fnd_global.newline || ', ' ||
1626 'BIV_MEASURE7 ' || fnd_global.newline || ', ' ||
1627 'BIV_MEASURE8 ' || fnd_global.newline || ', ' ||
1628 'BIV_MEASURE10 ' || fnd_global.newline || ', ' ||
1629 'BIV_MEASURE11 ' || fnd_global.newline || ', ' ||
1630 'BIV_MEASURE12 ' || fnd_global.newline || ', ' ||
1631 'BIV_MEASURE14 ' || fnd_global.newline || ', ' ||
1632 'BIV_MEASURE15 ' || fnd_global.newline || ', ' ||
1633 'BIV_MEASURE16 ' || fnd_global.newline || ', ' ||
1634 'BIV_MEASURE17 ' || fnd_global.newline || ', ' ||
1635 'BIV_MEASURE18 ' || fnd_global.newline || ', ' ||
1636 'BIV_MEASURE19 ' || fnd_global.newline || ', ' ||
1637 'BIV_MEASURE20 ' || fnd_global.newline || ', ' ||
1638 'BIV_MEASURE21 ' || fnd_global.newline || ', ' ||
1639 'BIV_MEASURE22 ' || fnd_global.newline || ', ' ||
1640 'BIV_MEASURE23 ' || fnd_global.newline || ', ' ||
1641 'BIV_MEASURE24 ' || fnd_global.newline || ', ' ||
1642 'BIV_MEASURE25 ' || fnd_global.newline || ', ' ||
1643 'BIV_MEASURE26 ' || fnd_global.newline || ', ' ||
1644 'BIV_MEASURE27 ' || fnd_global.newline || ', ' ||
1645 'BIV_MEASURE28 ' || fnd_global.newline || ', ' ||
1646 'BIV_MEASURE29 ' || fnd_global.newline || ', ' ||
1647 'BIV_MEASURE30 ' || fnd_global.newline || ', ' ||
1648 '''pFunctionName=CSD_RO_DETAILS&csdInvOrgId=''||BIV_MEASURE30||''&csdRepairLineId=''||BIV_MEASURE29 BIV_DYNAMIC_URL1'|| ', '||
1649 ISC_DEPOT_RPT_UTIL_PKG.get_service_request_url || '|| BIV_ATTRIBUTE5 BIV_DYNAMIC_URL2 '||
1650 ' FROM ( ' || fnd_global.newline ||
1651 'SELECT ' || fnd_global.newline ||
1652 'rank() over (&ORDER_BY_CLAUSE nulls last ,BIV_ATTRIBUTE1) - 1 rnk ' || fnd_global.newline || ', ' ||
1653 'BIV_ATTRIBUTE1 ' || fnd_global.newline || ', ' ||
1654 'BIV_ATTRIBUTE2 ' || fnd_global.newline || ', ' ||
1655 'BIV_ATTRIBUTE3 ' || fnd_global.newline || ', ' ||
1656 'BIV_ATTRIBUTE4 ' || fnd_global.newline || ', ' ||
1657 'BIV_ATTRIBUTE5 ' || fnd_global.newline || ', ' ||
1658 'BIV_MEASURE2 ' || fnd_global.newline || ', ' ||
1659 'BIV_MEASURE3 ' || fnd_global.newline || ', ' ||
1660 'BIV_MEASURE4 ' || fnd_global.newline || ', ' ||
1661 'BIV_MEASURE6 ' || fnd_global.newline || ', ' ||
1662 'BIV_MEASURE7 ' || fnd_global.newline || ', ' ||
1663 'BIV_MEASURE8 ' || fnd_global.newline || ', ' ||
1664 'BIV_MEASURE10 ' || fnd_global.newline || ', ' ||
1665 'BIV_MEASURE11 ' || fnd_global.newline || ', ' ||
1666 'BIV_MEASURE12 ' || fnd_global.newline || ', ' ||
1667 'BIV_MEASURE14 ' || fnd_global.newline || ', ' ||
1668 'BIV_MEASURE15 ' || fnd_global.newline || ', ' ||
1669 'BIV_MEASURE16 ' || fnd_global.newline || ', ' ||
1670 'BIV_MEASURE17 ' || fnd_global.newline || ', ' ||
1671 'BIV_MEASURE18 ' || fnd_global.newline || ', ' ||
1672 'BIV_MEASURE19 ' || fnd_global.newline || ', ' ||
1673 'BIV_MEASURE20 ' || fnd_global.newline || ', ' ||
1674 'BIV_MEASURE21 ' || fnd_global.newline || ', ' ||
1675 'BIV_MEASURE22 ' || fnd_global.newline || ', ' ||
1676 'BIV_MEASURE23 ' || fnd_global.newline || ', ' ||
1677 'BIV_MEASURE24 ' || fnd_global.newline || ', ' ||
1678 'BIV_MEASURE25 ' || fnd_global.newline || ', ' ||
1679 'BIV_MEASURE26 ' || fnd_global.newline || ', ' ||
1680 'BIV_MEASURE27 ' || fnd_global.newline || ', ' ||
1681 'BIV_MEASURE28 ' || fnd_global.newline || ', ' ||
1682 'BIV_MEASURE29 ' || fnd_global.newline || ', ' ||
1683 'BIV_MEASURE30 ' || fnd_global.newline ||
1684 'FROM ( ' || fnd_global.newline ||
1685 'SELECT ' || fnd_global.newline ||
1686 'repair_number BIV_ATTRIBUTE1 ' || fnd_global.newline || ', ' ||
1687 'incident_number BIV_ATTRIBUTE2 ' || fnd_global.newline || ', ' ||
1688 'fact.repair_line_id BIV_MEASURE29 ' || fnd_global.newline || ', ' ||
1689 'fact.master_organization_id BIV_MEASURE30 ' || fnd_global.newline || ', ' ||
1690 'crt.name BIV_ATTRIBUTE3 ' || fnd_global.newline || ', ' ||
1691 'eiov.value BIV_ATTRIBUTE4 ' || fnd_global.newline || ', ' ||
1692 'incident_id BIV_ATTRIBUTE5 ' || fnd_global.newline || ', ' ||
1693 'NVL(charges.material_charges_' || p_curr_suffix || ', 0) BIV_MEASURE2' || fnd_global.newline || ', ' ||
1694 'NVL(costs.material_cost_' || p_curr_suffix || ', 0) BIV_MEASURE3' || fnd_global.newline || ', ' ||
1695 'NVL(charges.material_charges_' || p_curr_suffix || ', 0) - ' ||
1696 'NVL(costs.material_cost_' || p_curr_suffix || ', 0) BIV_MEASURE4' || fnd_global.newline || ', ' ||
1697 'NVL(charges.labor_charges_' || p_curr_suffix || ', 0) BIV_MEASURE6' || fnd_global.newline || ', ' ||
1698 'NVL(costs.labor_cost_' || p_curr_suffix || ', 0) BIV_MEASURE7' || fnd_global.newline || ', ' ||
1699 'NVL(charges.labor_charges_' || p_curr_suffix || ', 0) - ' ||
1700 'NVL(costs.labor_cost_' || p_curr_suffix || ', 0) BIV_MEASURE8' || fnd_global.newline || ', ' ||
1701 'NVL(charges.expense_charges_' || p_curr_suffix || ', 0) BIV_MEASURE10' || fnd_global.newline || ', ' ||
1702 'NVL(costs.expense_cost_' || p_curr_suffix || ', 0) BIV_MEASURE11' || fnd_global.newline || ', ' ||
1703 'NVL(charges.expense_charges_' || p_curr_suffix || ', 0) - ' ||
1704 'NVL(costs.expense_cost_' || p_curr_suffix || ', 0) BIV_MEASURE12' || fnd_global.newline || ', ' ||
1705 l_total_charges || ' BIV_MEASURE14' || fnd_global.newline || ', ' ||
1706 l_total_cost || ' BIV_MEASURE15' || fnd_global.newline || ', ' ||
1707 l_total_margin || ' BIV_MEASURE16' || fnd_global.newline || ', ' ||
1708 'NVL(sum(charges.material_charges_' || p_curr_suffix || ') over(), 0) BIV_MEASURE17' || fnd_global.newline || ', ' ||
1709 'NVL(sum(costs.material_cost_' || p_curr_suffix || ') over(), 0) BIV_MEASURE18' || fnd_global.newline || ', ' ||
1710 'NVL(sum(charges.material_charges_' || p_curr_suffix || ') over(), 0) - ' ||
1711 'NVL(sum(costs.material_cost_' || p_curr_suffix || ') over(), 0) BIV_MEASURE19' || fnd_global.newline || ', ' ||
1712 'NVL(sum(charges.labor_charges_' || p_curr_suffix || ') over(), 0) BIV_MEASURE20' || fnd_global.newline || ', ' ||
1713 'NVL(sum(costs.labor_cost_' || p_curr_suffix || ') over(), 0) BIV_MEASURE21' || fnd_global.newline || ', ' ||
1714 'NVL(sum(charges.labor_charges_' || p_curr_suffix || ') over(), 0) - ' ||
1715 'NVL(sum(costs.labor_cost_' || p_curr_suffix || ') over(), 0) BIV_MEASURE22' || fnd_global.newline || ', ' ||
1716 'NVL(sum(charges.expense_charges_' || p_curr_suffix || ') over(), 0) BIV_MEASURE23' || fnd_global.newline || ', ' ||
1717 'NVL(sum(costs.expense_cost_' || p_curr_suffix || ') over(), 0) BIV_MEASURE24' || fnd_global.newline || ', ' ||
1718 'NVL(sum(charges.expense_charges_' || p_curr_suffix || ') over(), 0) - ' ||
1719 'NVL(sum(costs.expense_cost_' || p_curr_suffix || ') over(), 0) BIV_MEASURE25' || fnd_global.newline || ', ' ||
1720 l_total_charges_total || ' BIV_MEASURE26' || fnd_global.newline || ', ' ||
1721 l_total_cost_total || ' BIV_MEASURE27' || fnd_global.newline || ', ' ||
1722 l_total_margin_total || ' BIV_MEASURE28 ' || fnd_global.newline;
1723 RETURN l_sel_clause;
1724 END get_mrg_dtl_sel_clause;
1725
1726 END ISC_DEPOT_MARGIN_RPT_PKG;