[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_NET_BOOK_FULF_PKG
Source
1 PACKAGE BODY ISC_DBI_NET_BOOK_FULF_PKG AS
2 /* $Header: ISCRGBEB.pls 120.0 2005/05/25 17:13:00 appldev noship $ */
3
4 PROCEDURE GET_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,x_custom_sql OUT NOCOPY VARCHAR2,
5 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
6
7 l_inner_sql VARCHAR2(32000);
8 l_stmt VARCHAR2(32000);
9 l_view_by VARCHAR2(32000);
10 l_sgid VARCHAR2(32000);
11 l_sg_where VARCHAR2(32000);
12 l_prod_cat VARCHAR2(32000);
13 l_prod_cat_from VARCHAR2(32000);
14 l_prod_cat_where VARCHAR2(32000);
15 l_cust VARCHAR2(32000);
16 l_cust_where VARCHAR2(32000);
17 l_class VARCHAR2(32000);
18 l_class_where VARCHAR2(32000);
19 l_viewby_col VARCHAR2(200);
20 l_sg_sg NUMBER;
21 l_sg_res NUMBER;
22 l_item_cat_flag NUMBER;
23 l_cust_flag NUMBER; -- 0 for customer, 1 for cust classification, 3 for all
24 l_drill_ord VARCHAR2(10000);
25 l_drill_ret VARCHAR2(10000);
26 l_cat_join VARCHAR2(50);
27 l_flags VARCHAR2(32000);
28 l_mv VARCHAR2(100);
29 l_curr VARCHAR2(10000);
30 l_curr_suffix VARCHAR2(120);
31 l_invalid_curr BOOLEAN;
32 l_custom_rec BIS_QUERY_ATTRIBUTES ;
33
34
35 BEGIN
36
37 l_invalid_curr := FALSE;
38
39 FOR i IN 1..p_param.COUNT
40 LOOP
41
42 IF( p_param(i).parameter_name= 'VIEW_BY') THEN
43 l_view_by := p_param(i).parameter_value;
44 END IF;
45
46 IF(p_param(i).parameter_name = 'ORGANIZATION+JTF_ORG_SALES_GROUP') THEN
47 l_sgid := p_param(i).parameter_id;
48 END IF;
49
50 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT') THEN
51 l_prod_cat := p_param(i).parameter_id;
52 END IF;
53
54 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS') THEN
55 l_cust := p_param(i).parameter_id;
56 END IF;
57
58 IF(p_param(i).parameter_name = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') THEN
59 l_class := p_param(i).parameter_id;
60 END IF;
61
62 IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
63 THEN l_curr := p_param(i).parameter_id;
64 END IF;
65
66 END LOOP;
67
68 IF (l_curr = '''FII_GLOBAL1''')
69 THEN l_curr_suffix := 'g';
70 ELSIF (l_curr = '''FII_GLOBAL2''')
71 THEN l_curr_suffix := 'g1';
72 ELSE
73 l_invalid_curr := TRUE;
74 END IF;
75
76 l_sg_sg := to_number(replace(substr(l_sgid,instr(l_sgid,'.') + 1),''''));
77 l_sg_res := to_number(replace(substr(l_sgid,1,instr(l_sgid,'.') - 1),''''));
78
79 IF (l_sg_res IS NULL) -- when a sales group is chosen
80 THEN
81 l_drill_ord := 'NULL';
82 l_drill_ret := 'NULL';
83 IF l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP'
84 THEN
85 l_sg_where := '
86 AND f.parent_grp_id = (&ORGANIZATION+JTF_ORG_SALES_GROUP)
87 AND f.grp_marker <> ''TOP GROUP'''; -- exclude the top groups when VB=SG
88 ELSE -- other view bys
89 l_sg_where := '
90 AND f.sales_grp_id = (&ORGANIZATION+JTF_ORG_SALES_GROUP)
91 AND f.resource_id IS NULL';
92 END IF;
93 ELSE -- when the LOV parameter is a SRep (no need to go through the SG hierarchy MV
94 l_drill_ord := '''pFunctionName=ISC_DBI_BK_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''';
95 l_drill_ret := '''pFunctionName=ISC_DBI_RET_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''';
96 l_sg_where := '
97 AND f.sales_grp_id = :ISC_SG
98 AND f.resource_id = :ISC_RES';
99 END IF;
100
101
102 IF (l_cust IS NULL)
103 THEN
104 l_cust_where:='';
105 IF (l_view_by = 'CUSTOMER+FII_CUSTOMERS')
106 THEN l_cust_flag := 0; -- customer
107 ELSIF (l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS')
108 THEN l_cust_flag := 1; -- customer classification
109 ELSE
110 IF (l_class IS NULL)
111 THEN l_cust_flag := 3; -- all
112 ELSE l_cust_flag := 1; -- customer classification
113 END IF;
114 END IF;
115 ELSE
116 l_cust_where :='
117 AND f.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
118 l_cust_flag := 0; -- customer
119 END IF;
120
121 IF (l_class IS NULL) THEN
122 l_class_where:='';
123 ELSE
124 l_class_where :='
125 AND f.class_code in (&FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS)';
126 END IF;
127
128
129 IF (l_view_by <> 'CUSTOMER+FII_CUSTOMERS' AND l_cust IS NULL
130 AND l_view_by <> 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS'
131 AND l_class IS NULL) THEN -- use double rollup without cust
132 l_flags := '';
133 l_mv := 'ISC_DBI_SCR_002_MV';
134 IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') THEN
135 l_prod_cat_from := ',
136 ENI_DENORM_HIERARCHIES eni_cat,
137 MTL_DEFAULT_CATEGORY_SETS mdcs';
138 IF (l_prod_cat IS NULL) THEN
139 l_prod_cat_where := '
140 AND f.cat_top_node_flag = ''Y''
141 AND f.item_category_id = eni_cat.imm_child_id
142 AND eni_cat.top_node_flag = ''Y''
143 AND eni_cat.dbi_flag = ''Y''
144 AND eni_cat.object_type = ''CATEGORY_SET''
145 AND eni_cat.object_id = mdcs.category_set_id
146 AND mdcs.functional_area_id = 11';
147 ELSE l_prod_cat_where := '
148 AND f.item_category_id = eni_cat.imm_child_id
149 AND ((eni_cat.leaf_node_flag = ''N'' and
150 eni_cat.child_id <> eni_cat.parent_id and imm_child_id = child_id)
151 OR (eni_cat.leaf_node_flag = ''Y''))
152 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
153 AND eni_cat.dbi_flag = ''Y''
154 AND eni_cat.object_type = ''CATEGORY_SET''
155 AND eni_cat.object_id = mdcs.category_set_id
156 AND mdcs.functional_area_id = 11';
157 END IF;
158 ELSE -- view by <> cat.
159 l_prod_cat_from := ''; -- do not need to join to denorm table
160 IF (l_prod_cat IS NULL) THEN
161 l_prod_cat_where :='
162 AND f.cat_top_node_flag = ''Y''';
163 ELSE -- view by sales group, prod.cat selected
164 l_prod_cat_where :='
165 AND f.item_category_id IN (&ITEM+ENI_ITEM_VBH_CAT)';
166 END IF;
167 END IF;
168
169 ELSE -- use single rollup with customer dimension
170 l_flags := '
171 AND f.item_cat_flag = :ISC_ITEM_CAT_FLAG
172 AND f.customer_flag = :ISC_CUST';
173 l_mv := 'ISC_DBI_SCR_001_MV';
174 IF (l_prod_cat IS NULL) THEN
175 IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') THEN
176 l_prod_cat_from := ',
177 ENI_DENORM_HIERARCHIES eni_cat,
178 MTL_DEFAULT_CATEGORY_SETS mdcs';
179 l_prod_cat_where := '
180 AND f.item_category_id = eni_cat.child_id
181 AND eni_cat.top_node_flag = ''Y''
182 AND eni_cat.dbi_flag = ''Y''
183 AND eni_cat.object_type = ''CATEGORY_SET''
184 AND eni_cat.object_id = mdcs.category_set_id
185 AND mdcs.functional_area_id = 11';
186 ELSE
187 l_prod_cat_from := '';
188 l_prod_cat_where := '';
189 END IF;
190 ELSE -- a prod cat has been selected
191 l_prod_cat_from := ',
192 ENI_DENORM_HIERARCHIES eni_cat,
193 MTL_DEFAULT_CATEGORY_SETS mdcs';
194 l_prod_cat_where := '
195 AND f.item_category_id = eni_cat.child_id
196 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
197 AND eni_cat.dbi_flag = ''Y''
198 AND eni_cat.object_type = ''CATEGORY_SET''
199 AND eni_cat.object_id = mdcs.category_set_id
200 AND mdcs.functional_area_id = 11';
201 END IF;
202 END IF;
203
204
205 IF (l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP') THEN
206 l_viewby_col :='resource_id, sales_grp_id';
207
208 ELSIF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT') THEN
209 IF (l_prod_cat is null) THEN
210 l_viewby_col := 'parent_id';
211 ElSE
212 l_viewby_col :='imm_child_id';
213 END IF;
214
215 ELSIF (l_view_by = 'CUSTOMER+FII_CUSTOMERS') THEN
216 l_viewby_col :='customer_id';
217
218 ELSIF (l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS') THEN
219 l_viewby_col :='class_code';
220
221 END IF;
222
223 IF (l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' OR l_prod_cat IS NOT NULL)
224 THEN l_item_cat_flag := 0; -- Product Category
225 ELSE l_item_cat_flag := 1; -- All
226 END IF;
227
228 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
229 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
230
231 IF (l_invalid_curr)
232 THEN l_stmt := '
233 /* Unsupported currency */
234 SELECT 0 VIEWBY,
235 0 VIEWBYID,
236 0 ISC_ATTRIBUTE_2,
237 0 ISC_ATTRIBUTE_3,
238 0 ISC_ATTRIBUTE_4,
239 0 ISC_ATTRIBUTE_5,
240 0 ISC_MEASURE_2,
241 0 ISC_MEASURE_3,
242 0 ISC_MEASURE_4,
243 0 ISC_MEASURE_5,
244 0 ISC_MEASURE_6,
245 0 ISC_MEASURE_7,
246 0 ISC_MEASURE_8,
247 0 ISC_MEASURE_9,
248 0 ISC_MEASURE_10,
249 0 ISC_MEASURE_11,
250 0 ISC_MEASURE_12,
251 0 ISC_MEASURE_13
252 FROM dual
253 WHERE 1 = 2';
254
255 ELSE
256
257 l_inner_sql:='
258 ISC_MEASURE_2,ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,ISC_MEASURE_6,ISC_MEASURE_7,
259 ISC_MEASURE_8,ISC_MEASURE_9,ISC_MEASURE_10,ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13
260 FROM(SELECT
261 (rank() over (&ORDER_BY_CLAUSE nulls last,'||l_viewby_col||'))-1 rnk,
262 '||l_viewby_col||',
263 ISC_MEASURE_2,ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,ISC_MEASURE_6,ISC_MEASURE_7,
264 ISC_MEASURE_8,ISC_MEASURE_9,ISC_MEASURE_10,ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13
265 FROM
266 (SELECT '||l_viewby_col||',
267 nvl(a.c_book, 0) ISC_MEASURE_2,
268 (a.c_book-a.p_book)
269 / abs(decode(a.p_book,0,null,a.p_book))*100 ISC_MEASURE_3,
270 nvl(a.c_book_ret, 0) ISC_MEASURE_4,
271 (a.c_book_ret - a.p_book_ret)
272 / abs(decode(a.p_book_ret,0,null,a.p_book_ret))*100 ISC_MEASURE_5,
273 nvl((a.c_book - a.c_book_ret), 0) ISC_MEASURE_6,
274 ((a.c_book-a.c_book_ret) - (a.p_book-a.p_book_ret))
275 / abs(decode((a.p_book-a.p_book_ret), 0,
276 null,(a.p_book-a.p_book_ret)))*100 ISC_MEASURE_7,
277 nvl(sum(a.c_book) over (), 0) ISC_MEASURE_8,
278 (sum(a.c_book) over () - sum(a.p_book) over ())
279 / abs(decode(sum(a.p_book) over (), 0,
280 null,sum(a.p_book) over ()))*100 ISC_MEASURE_9,
281 nvl(sum(a.c_book_ret) over (), 0) ISC_MEASURE_10,
282 (sum(a.c_book_ret) over () - sum(a.p_book_ret) over ())
283 / abs(decode(sum(a.p_book_ret) over (), 0,
284 null,sum(a.p_book_ret) over ()))*100 ISC_MEASURE_11,
285 nvl(sum(a.c_book - a.c_book_ret) over (), 0) ISC_MEASURE_12,
286 (sum(a.c_book-a.c_book_ret) over () - sum(a.p_book-a.p_book_ret) over ())
287 / abs(decode(sum(a.p_book-a.p_book_ret) over (), 0,
288 null,sum(a.p_book-a.p_book_ret) over ()))*100 ISC_MEASURE_13
289 FROM
290 (SELECT '||l_viewby_col||',
291 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
292 nvl(booked_amt_'||l_curr_suffix||',0),0)) C_BOOK,
293 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
294 nvl(booked_amt_'||l_curr_suffix||',0),0)) P_BOOK,
295 sum(decode(cal.report_date,&BIS_CURRENT_ASOF_DATE,
296 nvl(returned_amt_'||l_curr_suffix||',0),0)) C_BOOK_RET,
297 sum(decode(cal.report_date,&BIS_PREVIOUS_ASOF_DATE,
298 nvl(returned_amt_'||l_curr_suffix||',0),0)) P_BOOK_RET
299 FROM '||l_mv||' f,
300 FII_TIME_RPT_STRUCT_V cal'
301 ||l_prod_cat_from||'
302 WHERE f.time_id = cal.time_id
303 AND cal.report_date IN (&BIS_CURRENT_ASOF_DATE,&BIS_PREVIOUS_ASOF_DATE)
304 AND cal.period_type_id = f.period_type_id
308 GROUP BY '||l_viewby_col||') a)) c,';
305 AND bitand(cal.record_type_id,&BIS_NESTED_PATTERN) = cal.record_type_id'
306 ||l_flags
307 ||l_sg_where||l_prod_cat_where||l_cust_where||l_class_where||'
309
310
311
312 IF l_view_by = 'ITEM+ENI_ITEM_VBH_CAT' THEN
313 IF (l_prod_cat is null) THEN
314 l_cat_join := 'AND c.parent_id = ecat.id';
315 ElSE
316 l_cat_join := 'AND c.imm_child_id = ecat.id';
317 END IF;
318
319 l_stmt := ' SELECT ecat.value VIEWBY,
320 ecat.id VIEWBYID,
321 null ISC_ATTRIBUTE_2, -- Drill - Sales Group
322 decode(ecat.leaf_node_flag, ''Y'',
323 NULL,
324 ''pFunctionName=ISC_DBI_NET_BOOK_FULF&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_VBH_CAT&pParamIds=Y'')
325 ISC_ATTRIBUTE_3, -- Drill - Product Category
326 '||l_drill_ord||' ISC_ATTRIBUTE_4, -- Drill - Orders Value
327 '||l_drill_ret||' ISC_ATTRIBUTE_5, -- Drill - Returns Value'
328 ||l_inner_sql||'
329 ENI_ITEM_VBH_NODES_V ecat
330 WHERE ecat.parent_id = ecat.child_id
331 '||l_cat_join||'
332 AND ((c.rnk between &START_INDEX and &END_INDEX) OR(&END_INDEX = -1))
333 ORDER BY rnk';
334
335 ELSIF l_view_by = 'ORGANIZATION+JTF_ORG_SALES_GROUP' THEN
336 l_stmt := 'SELECT
337 decode(c.resource_id,null,g.group_name,
338 r.resource_name) VIEWBY,
339 decode(c.resource_id,null,to_char(c.sales_grp_id),
340 c.resource_id||''.''||c.sales_grp_id)
341 VIEWBYID,
342 decode(c.resource_id, NULL,
343 ''pFunctionName=ISC_DBI_NET_BOOK_FULF&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=VIEW_BY'',
344 NULL) ISC_ATTRIBUTE_2, -- Drill - Sales Group
345 null ISC_ATTRIBUTE_3, -- Drill - Product Category
346 decode(c.resource_id, NULL,
347 NULL,
348 decode(c.sales_grp_id, -1, NULL,
349 ''pFunctionName=ISC_DBI_BK_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' ))
350 ISC_ATTRIBUTE_4, -- Drill - Orders Value
351 decode(c.resource_id, NULL,
352 NULL,
353 decode(c.sales_grp_id, -1, NULL,
354 ''pFunctionName=ISC_DBI_RET_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y''))
355 ISC_ATTRIBUTE_5, -- Drill - Returns Value'
356 ||l_inner_sql||'
357 JTF_RS_GROUPS_VL g,
358 JTF_RS_RESOURCE_EXTNS_VL r
359 WHERE c.sales_grp_id = g.group_id
360 AND c.resource_id = r.resource_id(+)
361 AND ((c.rnk between &START_INDEX and &END_INDEX) OR(&END_INDEX = -1))
362 ORDER BY rnk' ;
363
364 ELSIF l_view_by = 'FII_TRADING_PARTNER_MKT_CLASS+FII_TRADING_PARTNER_MKT_CLASS' THEN
365 l_stmt := '
366 SELECT cc.value VIEWBY,
367 cc.id VIEWBYID,
368 null ISC_ATTRIBUTE_2, -- Drill - Sales Group
369 null ISC_ATTRIBUTE_3, -- Drill - Product Category
370 '||l_drill_ord||' ISC_ATTRIBUTE_4, -- Drill - Orders Value
371 '||l_drill_ret||' ISC_ATTRIBUTE_5, -- Drill - Returns Value'
372 ||l_inner_sql||'
373 FII_PARTNER_MKT_CLASS_V cc
374 WHERE c.class_code = cc.id
375 AND ((c.rnk between &START_INDEX and &END_INDEX) OR (&END_INDEX = -1))
376 ORDER BY rnk';
377
378 ELSE -- l_view_by = 'CUSTOMER+FII_CUSTOMERS'
379 l_stmt := 'SELECT cust.value VIEWBY,
380 cust.id VIEWBYID,
381 null ISC_ATTRIBUTE_2, -- Drill - Sales Group
382 null ISC_ATTRIBUTE_3, -- Drill - Product Category
383 '||l_drill_ord||' ISC_ATTRIBUTE_4, -- Drill - Orders Value
384 '||l_drill_ret||' ISC_ATTRIBUTE_5, -- Drill - Returns Value'
385 ||l_inner_sql||'
386 FII_CUSTOMERS_V cust
387 WHERE c.customer_id = cust.id
388 AND ((c.rnk between &START_INDEX and &END_INDEX) OR(&END_INDEX = -1))
389 ORDER BY rnk';
390
391 END IF;
392
393 END IF;
394
395 x_custom_sql := l_stmt;
396
397 l_custom_rec.attribute_name := ':ISC_CUST';
398 l_custom_rec.attribute_value := l_cust_flag;
399 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
400 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
401 x_custom_output.EXTEND;
402 x_custom_output(1) := l_custom_rec;
403
404 l_custom_rec.attribute_name := ':ISC_SG';
405 l_custom_rec.attribute_value := to_char(l_sg_sg);
406 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
407 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
408 x_custom_output.extend;
409 x_custom_output(2) := l_custom_rec;
410
411 l_custom_rec.attribute_name := ':ISC_RES';
412 l_custom_rec.attribute_value := to_char(l_sg_res);
413 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
414 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
415 x_custom_output.extend;
416 x_custom_output(3) := l_custom_rec;
417
418 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
419 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
420 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
421 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
422 x_custom_output.extend;
423 x_custom_output(4) := l_custom_rec;
424
425 END get_sql;
426
427 END ISC_DBI_NET_BOOK_FULF_PKG ;
428