[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_RET_PKG
Source
1 PACKAGE BODY poa_dbi_ret_pkg
2 /* $Header: poadbiretb.pls 120.2 2006/06/27 23:43:03 sdiwakar noship $ */
3 AS
4 --
5 FUNCTION get_status_sel_clause(p_view_by_dim in VARCHAR2, p_view_by_col in VARCHAR2, p_url in VARCHAR2) return VARCHAR2;
6 FUNCTION get_reason_sel_clause(p_view_by_dim in VARCHAR2, p_view_by_col in VARCHAR2, p_url in VARCHAR2) return VARCHAR2;
7 FUNCTION get_trend_sel_clause return VARCHAR2;
8 FUNCTION get_status_filter_where(p_view_by IN VARCHAR2) return VARCHAR2;
9 FUNCTION get_retdist_filter_where(p_view_by IN VARCHAR2) return VARCHAR2;
10
11 PROCEDURE status_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL
12 ,x_custom_sql OUT NOCOPY VARCHAR2
13 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
14 IS
15 l_query varchar2(32000);
16 l_view_by varchar2(120);
17 l_view_by_col varchar2(120);
18 l_as_of_date date;
19 l_prev_as_of_date date;
20 l_xtd varchar2(10);
21 l_comparison_type varchar2(1) :='Y';
22 l_nested_pattern number;
23 l_cur_suffix varchar2(2);
24 l_url varchar2(300);
25 l_custom_sql varchar2(10000);
26 l_view_by_value varchar2(30);
27 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
28 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
29 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
30 l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
31 l_where_clause VARCHAR2(2000);
32 l_mv VARCHAR2(30);
33 ERR_MSG VARCHAR2(100);
34 ERR_CDE NUMBER;
35 l_context_code VARCHAR2(10);
36 l_to_date_type VARCHAR2(10);
37 BEGIN
38
39 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
40 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
41
42 poa_dbi_sutil_pkg.process_parameters(p_param
43 ,l_view_by
44 ,l_view_by_col
45 ,l_view_by_value
46 ,l_comparison_type
47 ,l_xtd
48 ,l_as_of_date
49 ,l_prev_as_of_date
50 ,l_cur_suffix
51 ,l_nested_pattern
52 ,l_where_clause
53 ,l_mv
54 ,l_join_tbl
55 ,l_in_join_tbl
56 , x_custom_output
57 ,'N'
58 ,'PO'
59 , '6.0'
60 , 'COM'
61 ,'RTX');
62 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
63 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
64 l_to_date_type := 'RLX';
65 ELSE
66 l_to_date_type := 'XTD';
67 END IF;
68
69 poa_dbi_util_pkg.add_column(l_col_tbl
70 ,'amt_return_' || l_cur_suffix
71 ,'amt_return'
72 ,p_to_date_type => l_to_date_type);
73 poa_dbi_util_pkg.add_column(l_col_tbl
74 ,'amt_receipt_and_dropship_' || l_cur_suffix
75 ,'amt_receipt'
76 ,p_to_date_type => l_to_date_type);
77 poa_dbi_util_pkg.add_column(l_col_tbl
78 ,'amt_receipt_return_' || l_cur_suffix
79 ,'amt_receipt_return'
80 ,p_to_date_type => l_to_date_type);
81
82 poa_dbi_util_pkg.add_column(l_col_tbl
83 ,'num_txns_return_cnt'
84 ,'cnt_return'
85 ,p_to_date_type => l_to_date_type);
86
87 poa_dbi_util_pkg.add_column(l_col_tbl
88 ,'num_txns_receipt_return_cnt'
89 ,'cnt_receipt_return'
90 ,p_to_date_type => l_to_date_type);
91
92 poa_dbi_util_pkg.add_column(l_col_tbl
93 ,'num_txns_receipt_cnt'
94 ,'cnt_receipt'
95 ,p_to_date_type => l_to_date_type);
96
97
98 if(l_view_by = 'ITEM+POA_ITEMS') then
99 poa_dbi_util_pkg.add_column(l_col_tbl
100 ,'qty_return'
101 ,'qty_return'
102 ,p_to_date_type => l_to_date_type);
103 poa_dbi_util_pkg.add_column(l_col_tbl
104 ,'qty_receipt_and_dropship'
105 ,'qty_receipt'
106 ,p_to_date_type => l_to_date_type);
107 end if;
108
109 l_url := 'pFunctionName=POA_DBI_RET_REASON_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_PO_CAT&pParamIds=Y';
110
111 l_query := get_status_sel_clause(l_view_by, l_view_by_col, l_url) || ' from ' ||
112 poa_dbi_template_pkg.status_sql(l_mv,
113 l_where_clause,
114 l_join_tbl,
115 p_use_windowing => 'Y',
116 p_col_name => l_col_tbl,
117 p_use_grpid => 'N',
118 p_filter_where => get_status_filter_where(l_view_by),
119 p_in_join_tables => l_in_join_tbl);
120 x_custom_sql := l_query;
121
122 EXCEPTION
123 WHEN OTHERS THEN
124 ERR_MSG := SUBSTR(SQLERRM,1,400);
125 end;
126
127
128 FUNCTION get_status_filter_where(p_view_by in VARCHAR2) return VARCHAR2
129 IS
130 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
131 BEGIN
132 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
133 l_col_tbl.extend;
134 l_col_tbl(1) := 'POA_MEASURE1';
135 l_col_tbl.extend;
136 l_col_tbl(2) := 'POA_MEASURE2';
137 l_col_tbl.extend;
138 l_col_tbl(3) := 'POA_MEASURE3';
139 l_col_tbl.extend;
140 l_col_tbl(4) := 'POA_PERCENT1';
141 l_col_tbl.extend;
142 l_col_tbl(5) := 'POA_PERCENT2';
143 l_col_tbl.extend;
144 l_col_tbl(6) := 'POA_MEASURE13';
145 l_col_tbl.extend;
146 l_col_tbl(7) := 'POA_PERCENT3';
147
148
149 if(p_view_by = 'ITEM+POA_ITEMS') then
150
151 l_col_tbl.extend;
152 l_col_tbl(8) := 'POA_MEASURE4';
153 l_col_tbl.extend;
154 l_col_tbl(9) := 'POA_MEASURE5';
155 end if;
156
157
158 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
159
160 END;
161
162 FUNCTION get_status_sel_clause(p_view_by_dim in VARCHAR2
163 ,p_view_by_col in VARCHAR2
164 ,p_url in VARCHAR2) return VARCHAR2 IS
165 l_sel_clause varchar2(8000);
166 --
167 BEGIN
168 --
169 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
170 ,'PO'
171 ,'6.0');
172 --
173 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
174 l_sel_clause := l_sel_clause || '
175 v.description POA_ATTRIBUTE1, --Description
176 v2.description POA_ATTRIBUTE2, --UOM
177 oset.POA_MEASURE4 POA_MEASURE4, --Return Quantity
178 oset.POA_MEASURE5 POA_MEASURE5, --Receipt Quantity
179 ';
180 else
181 l_sel_clause := l_sel_clause || '
182 null POA_ATTRIBUTE1, --Description
183 null POA_ATTRIBUTE2, --UOM
184 null POA_MEASURE4, --Return Quantity
185 null POA_MEASURE5, --Receipt Quantity
186 ';
187 end if;
188
189 l_sel_clause := l_sel_clause ||
190 ' oset.POA_MEASURE1 POA_MEASURE1, --Return Amount
191 oset.POA_PERCENT1 POA_PERCENT1, --Change
192 oset.POA_MEASURE2 POA_MEASURE2, --Receipt Amount
193 oset.POA_MEASURE3 POA_MEASURE3, --Receipt Returned Amount
194 oset.POA_PERCENT2 POA_PERCENT2, --Return Rate
195 oset.POA_MEASURE6 POA_MEASURE6, --Grand Total Return Amount
196 oset.POA_MEASURE7 POA_MEASURE7, --Grand Total Change
197 oset.POA_MEASURE8 POA_MEASURE8, --Grand Total Receipt Amount
198 oset.POA_MEASURE9 POA_MEASURE9, --Grand Total Receipt Returned Amount
199 oset.POA_MEASURE10 POA_MEASURE10, --Grand Total Return Rate
200 oset.POA_MEASURE13 POA_MEASURE13, --Return Txns
201 oset.POA_PERCENT3 POA_PERCENT3, --Change
202 oset.POA_MEASURE14 POA_MEASURE14, --Grand Total Return Txns
203 oset.POA_PERCENT4 POA_PERCENT4, --Grand Total Change
204 ''' || p_url || ''' POA_MEASURE11,
205 ''' || p_url || ''' POA_ATTRIBUTE5,
206 oset.poa_percent5 poa_percent5, -- KPI - Prior Receipt Return Rate
207 oset.poa_percent6 poa_percent6, -- KPI - Grand Total Prior receipt return rate
208 oset.poa_measure16 poa_measure16, -- KPI - Receipt Return Transactions
209 oset.poa_measure17 poa_measure17, -- KPI - Grand Total for Receipt Return transactions
210 oset.poa_measure18 poa_measure18, -- KPI - Prior Receipt Return Transactions
211 oset.poa_measure19 poa_measure19 -- KPI - Grand Total for Receipt Return Transactions
212 from
213 (select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
214
215 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
216 l_sel_clause := l_sel_clause || ', base_uom';
217 end if;
218
219 l_sel_clause := l_sel_clause || ')) - 1 rnk,' || p_view_by_col;
220 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
221 l_sel_clause := l_sel_clause || ',
222 base_uom,
223 POA_MEASURE4,
224 POA_MEASURE5';
225 end if;
226
227 l_sel_clause := l_sel_clause || ',POA_MEASURE1,POA_PERCENT1,
228 POA_MEASURE2,POA_MEASURE3,
229 POA_PERCENT2,POA_MEASURE6,
230 POA_MEASURE7,POA_MEASURE8,
231 POA_MEASURE9,POA_MEASURE10,
232 poa_measure13, poa_percent3,
233 poa_measure14, poa_percent4,
234 poa_percent5, poa_percent6,
235 POA_MEASURE16, POA_MEASURE17,
236 POA_MEASURE18, POA_MEASURE19
237 from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
238 --
239 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
240 l_sel_clause := l_sel_clause || ' base_uom,
241 decode(base_uom,null,to_number(null),nvl(c_qty_return,0)) POA_MEASURE4,
242 decode(base_uom,null,to_number(null),nvl(c_qty_receipt,0)) POA_MEASURE5, ';
243 end if;
244 --
245 l_sel_clause := l_sel_clause || ' nvl(c_amt_return,0) POA_MEASURE1,
246 ' || poa_dbi_util_pkg.change_clause('c_amt_return','p_amt_return') || ' POA_PERCENT1,
247 nvl(c_amt_receipt,0) POA_MEASURE2,
248 nvl(c_amt_receipt_return,0) POA_MEASURE3,
249 ' || poa_dbi_util_pkg.rate_clause('c_amt_receipt_return','c_amt_receipt') || ' POA_PERCENT2,
250 nvl(c_amt_return_total,0) POA_MEASURE6,
251 ' || poa_dbi_util_pkg.change_clause('c_amt_return_total','p_amt_return_total') || ' POA_MEASURE7,
252 nvl(c_amt_receipt_total,0) POA_MEASURE8,
253 nvl(c_amt_receipt_return_total,0) POA_MEASURE9,
254 ' || poa_dbi_util_pkg.rate_clause('c_amt_receipt_return_total','c_amt_receipt_total') || ' POA_MEASURE10,
255 Nvl(c_cnt_return,0) POA_MEASURE13,
256 ' || poa_dbi_util_pkg.change_clause('c_cnt_return','p_cnt_return') || 'POA_PERCENT3,
257 Nvl(c_cnt_return_total,0) POA_MEASURE14,
258 ' || poa_dbi_util_pkg.change_clause('c_cnt_return_total','p_cnt_return_total') || ' POA_PERCENT4,
259 ' || poa_dbi_util_pkg.rate_clause('p_amt_receipt_return','p_amt_receipt') || ' POA_PERCENT5,
260 ' || poa_dbi_util_pkg.rate_clause('p_amt_receipt_return_total','p_amt_receipt_total') || ' POA_PERCENT6,
261 ' || poa_dbi_util_pkg.rate_clause('c_cnt_receipt_return','c_cnt_receipt') || ' POA_MEASURE16,
262 ' || poa_dbi_util_pkg.rate_clause('c_cnt_receipt_return_total','c_cnt_receipt_total') || ' POA_MEASURE17,
263 ' || poa_dbi_util_pkg.rate_clause('p_cnt_receipt_return','p_cnt_receipt') || ' POA_MEASURE18,
264 ' || poa_dbi_util_pkg.rate_clause('p_cnt_receipt_return_total','p_cnt_receipt_total') || ' POA_MEASURE19 ';
265 return l_sel_clause;
266 END;
267 --
268 PROCEDURE rtn_rsn_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
269 x_custom_sql OUT NOCOPY VARCHAR2,
270 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
271 IS
272 l_query varchar2(10000);
273 l_view_by varchar2(120);
274 l_view_by_col varchar2(120);
275 l_as_of_date date;
276 l_prev_as_of_date date;
277 l_xtd varchar2(10);
278 l_comparison_type varchar2(1) :='Y';
279 l_nested_pattern number;
280 l_cur_suffix varchar2(2);
281 l_url varchar2(300);
282 l_custom_sql varchar2(10000);
283 l_view_by_value varchar2(30);
284 l_where_clause VARCHAR2(2000);
285 l_mv VARCHAR2(30);
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_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
289 l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
290 ERR_MSG VARCHAR2(100);
291 ERR_CDE NUMBER;
292 l_context_code VARCHAR2(10);
293 l_to_date_type VARCHAR2(10);
294 BEGIN
295
296 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
297 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
298
299 poa_dbi_sutil_pkg.process_parameters(p_param,l_view_by,l_view_by_col,l_view_by_value,l_comparison_type,l_xtd,l_as_of_date,l_prev_as_of_date,l_cur_suffix,l_nested_pattern,l_where_clause,l_mv,l_join_tbl,l_in_join_tbl,
300 x_custom_output,
301 'N','PO', '6.0', 'COM','RTX');
302
303 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
304 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
305 l_to_date_type := 'RLX';
306 ELSE
307 l_to_date_type := 'XTD';
308 END IF;
309
310 poa_dbi_util_pkg.add_column(l_col_tbl,'amt_return_' || l_cur_suffix,'amt_return',p_to_date_type => l_to_date_type);
311 poa_dbi_util_pkg.add_column(l_col_tbl,'amt_receipt_return_' || l_cur_suffix,'amt_receipt_return',p_to_date_type => l_to_date_type);
312 poa_dbi_util_pkg.add_column(l_col_tbl,'num_txns_return_cnt','cnt_return',p_to_date_type => l_to_date_type);
313 if(l_view_by = 'ITEM+POA_ITEMS') then
314 poa_dbi_util_pkg.add_column(l_col_tbl
315 ,'qty_return'
316 ,'qty_return'
317 ,p_to_date_type => l_to_date_type);
318 end if;
319 l_query := get_reason_sel_clause(l_view_by, l_view_by_col,null) || ' from '
320 || poa_dbi_template_pkg.status_sql(l_mv,
321 l_where_clause,
322 l_join_tbl,
323 p_use_windowing => 'Y',
324 p_col_name => l_col_tbl,
325 p_use_grpid => 'N',
326 p_filter_where => get_retdist_filter_where(l_view_by),
327 p_in_join_tables => l_in_join_tbl);
328
329 x_custom_sql := l_query;
330
331 EXCEPTION
332 WHEN OTHERS THEN
333 ERR_MSG := SUBSTR(SQLERRM,1,400);
334 end;
335
336
337 FUNCTION get_reason_sel_clause(p_view_by_dim in VARCHAR2, p_view_by_col in VARCHAR2, p_url in VARCHAR2) return VARCHAR2
338 IS
339 l_sel_clause varchar2(4000);
340
341 BEGIN
342
343 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim,'PO','6.0');
344
345 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
346 l_sel_clause := l_sel_clause || '
347 v.description POA_ATTRIBUTE1, --Description
348 v2.description POA_ATTRIBUTE4, --UOM
349 oset.POA_MEASURE14 POA_MEASURE14, --Return Quantity
350 ';
351 else
352 l_sel_clause := l_sel_clause || '
353 null POA_ATTRIBUTE3, --Description
354 null POA_ATTRIBUTE4, --UOM
355 null POA_MEASURE14, --Return Quantity
356 ';
357 end if;
358
359
360
361 l_sel_clause := l_sel_clause ||
362 ' oset.POA_MEASURE1 POA_MEASURE1, --Return Amount
363 oset.POA_PERCENT1 POA_PERCENT1, --Change
364 oset.POA_MEASURE2 POA_MEASURE2, --Receipt Returned Amount
365 oset.POA_PERCENT2 POA_PERCENT2, --Percent of Total
366 oset.POA_MEASURE3 POA_MEASURE3, --Grand Total of Return Amount
367 oset.POA_MEASURE4 POA_MEASURE4, --Grand Total of Change
371 oset.POA_MEASURE10 poa_measure10, --Grand Total for Return Transactions
368 oset.POA_MEASURE5 POA_MEASURE5, --Grand Total of Receipt Returned Amount
369 oset.POA_MEASURE6 POA_MEASURE6, --Grand Total of Percent of Total
370 oset.POA_MEASURE9 POA_MEASURE9, --Return Transactions
372 oset.poa_percent3 poa_percent3, -- Change
373 oset.poa_percent4 poa_percent4, -- Percent of Total
374 oset.poa_percent5 poa_percent5, -- Grand Total Change
375 oset.poa_percent6 poa_percent6, -- Grand Total Percent of Total
376 oset.poa_percent2 poa_measure15, -- label for % of Total Return Amount
377 oset.poa_percent4 poa_measure16 -- label for % of Total Return Transactions
378 from (select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col ;
379
380 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
381 l_sel_clause := l_sel_clause || ', base_uom';
382 end if;
383
384 l_sel_clause := l_sel_clause || ')) - 1 rnk,' || p_view_by_col || ',';
385 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
386 l_sel_clause := l_sel_clause || '
387 base_uom,
388 poa_measure14,';
389 end if;
390
391 l_sel_clause := l_sel_clause || '
392 POA_MEASURE1,POA_PERCENT1,
393 POA_MEASURE2,POA_PERCENT2,
394 POA_MEASURE3,POA_MEASURE4,
395 POA_MEASURE5,poa_measure6,
396 poa_measure9,poa_percent3,
397 poa_measure10,poa_percent5,
398 poa_percent4,poa_percent6
399 from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID,';
400 --
401 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
402 l_sel_clause := l_sel_clause || ' base_uom,
403 decode(base_uom,null,to_number(null),nvl(c_qty_return,0)) poa_measure14, ';
404 end if;
405 --
406 l_sel_clause := l_sel_clause || '
407
408 nvl(c_amt_return,0) POA_MEASURE1,
409 ' || poa_dbi_util_pkg.change_clause('c_amt_return','p_amt_return') || ' POA_PERCENT1,
410 c_amt_receipt_return POA_MEASURE2,
411 ' || poa_dbi_util_pkg.rate_clause('c_amt_return','c_amt_return_total') || ' POA_PERCENT2,
412 nvl(c_amt_return_total,0) POA_MEASURE3,
413 ' || poa_dbi_util_pkg.change_clause('c_amt_return_total','p_amt_return_total') || ' POA_MEASURE4,
414 c_amt_receipt_return_total POA_MEASURE5,
415 ' || poa_dbi_util_pkg.rate_clause('c_amt_return_total','c_amt_return_total') || ' POA_MEASURE6 ,
416 Nvl(c_cnt_return,0) POA_MEASURE9,
417 ' || poa_dbi_util_pkg.change_clause('c_cnt_return','p_cnt_return') || 'POA_PERCENT3,
418 Nvl(c_cnt_return_total,0) POA_MEASURE10,
419 ' || poa_dbi_util_pkg.change_clause('c_cnt_return_total','p_cnt_return_total') || ' POA_PERCENT5,
420 ' || poa_dbi_util_pkg.rate_clause('c_cnt_return','c_cnt_return_total') || ' POA_PERCENT4,
421 ' || poa_dbi_util_pkg.rate_clause('c_cnt_return_total','c_cnt_return_total') || ' POA_PERCENT6
422 ';
423
424 return l_sel_clause;
425 END;
426
427
428 FUNCTION get_retdist_filter_where(p_view_by IN VARCHAR2) return VARCHAR2
429 IS
430 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
431 BEGIN
432 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
433 l_col_tbl.extend;
434 l_col_tbl(1) := 'POA_MEASURE1';
435 l_col_tbl.extend;
436 l_col_tbl(2) := 'POA_MEASURE9';
437 l_col_tbl.extend;
438 l_col_tbl(3) := 'POA_PERCENT3';
439 l_col_tbl.extend;
440 l_col_tbl(4) := 'POA_PERCENT1';
441 l_col_tbl.extend;
442 l_col_tbl(5) := 'POA_PERCENT2';
443 l_col_tbl.extend;
444 l_col_tbl(6) := 'POA_PERCENT4';
445
446 if(p_view_by = 'ITEM+POA_ITEMS') then
447 l_col_tbl.extend;
448 l_col_tbl(7) := 'POA_MEASURE14';
449 end if;
450 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
451
452 END;
453
454 PROCEDURE trend_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
455 x_custom_sql OUT NOCOPY VARCHAR2,
456 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
457 IS
458 l_query varchar2(10000);
459 l_view_by varchar2(120);
460 l_view_by_col_name varchar2(120);
461 l_as_of_date date;
462
463 l_prev_as_of_date date;
464 l_org varchar2(100);
465 l_category varchar2(2000);
466 l_commodity varchar2(2000);
467 l_commodity_where varchar2(2000);
468 l_item varchar2(2000);
469 l_buyer varchar2(2000);
470 l_mv VARCHAR2(90);
471 l_supplier varchar2(2000);
472 l_supplier_site VARCHAR2(2000);
473 l_xtd varchar2(10);
474 l_comparison_type varchar2(1) := 'Y';
475 l_nested_pattern number;
476
477 l_dim_bmap number;
478 l_org_where varchar2(240);
479 l_category_where varchar2(120);
480 l_item_where varchar2(120);
481 l_buyer_where varchar2(1000);
482 l_supplier_where varchar2(120);
483 l_supplier_site_where varchar2(120);
484 l_cur_suffix varchar2(2);
488 l_purchase_amount varchar2(25);
485 l_url varchar2(300);
486 l_impact_amount varchar2(15);
487 l_leakage_amount varchar2(25);
489 l_custom_sql varchar2(9000);
490
491 l_view_by_value varchar2(30);
492 l_dim_in_tbl poa_dbi_util_pkg.POA_DBI_DIM_TBL;
493 l_dim_out_tbl poa_dbi_util_pkg.POA_DBI_DIM_TBL;
494 l_col_rec poa_dbi_util_pkg.POA_DBI_COL_REC;
495 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
496 l_total_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
497 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
498 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
499 l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
500 l_where_clause VARCHAR2(2000);
501 ERR_MSG VARCHAR2(100);
502 ERR_CDE NUMBER;
503 l_context_code VARCHAR2(10);
504 l_to_date_type VARCHAR2(10);
505 BEGIN
506 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
507 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
508
509 poa_dbi_sutil_pkg.process_parameters(p_param,
510 l_view_by,
511 l_view_by_col_name,
512 l_view_by_value,
513 l_comparison_type,
514 l_xtd,
515 l_as_of_date,
516 l_prev_as_of_date,
517 l_cur_suffix,
518 l_nested_pattern,
519 l_where_clause,
520 l_mv,
521 l_join_tbl,
522 l_in_join_tbl,
523 x_custom_output,
524 'Y',
525 'PO',
526 '6.0',
527 'COM',
528 'RTX');
529 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
530 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
531 l_to_date_type := 'RLX';
532 ELSE
533 l_to_date_type := 'XTD';
534 END IF;
535
536 poa_dbi_util_pkg.add_column(l_col_tbl, 'amt_return_' || l_cur_suffix, 'amt_return','N',3,p_to_date_type => l_to_date_type);
537 poa_dbi_util_pkg.add_column(l_col_tbl,'num_txns_return_cnt','cnt_return','N',3,p_to_date_type => l_to_date_type);
538
539 l_query := get_trend_sel_clause || '
540 from '
541 || poa_dbi_template_pkg.trend_sql(
542 l_xtd,
543 l_comparison_type,
544 l_mv,
545 l_where_clause,
546 l_col_tbl,
547 p_use_grpid => 'N',
548 p_in_join_tables => l_in_join_tbl);
549
550 x_custom_sql := l_query;
551
552 END;
553
554 PROCEDURE kpi_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
555 x_custom_sql OUT NOCOPY VARCHAR2,
556 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
557 IS
558 l_query varchar2(10000);
559 l_view_by varchar2(120);
560 l_view_by_col_name varchar2(120);
561 l_as_of_date date;
562 l_prev_as_of_date date;
563 l_org varchar2(100);
564 l_category varchar2(2000);
565 l_commodity varchar2(2000);
566 l_item varchar2(2000);
567 l_buyer varchar2(2000);
568 l_mv VARCHAR2(90);
569 l_supplier varchar2(2000);
570 l_supplier_site VARCHAR2(2000);
571 l_xtd varchar2(10);
572 l_comparison_type varchar2(1) := 'Y';
573 l_nested_pattern number;
574
575 l_dim_bmap number;
576 l_cur_suffix varchar2(2);
577 l_url varchar2(300);
578 l_custom_sql varchar2(9000);
579
580 l_view_by_value varchar2(30);
581 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
582 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
583 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
584 l_where_clause VARCHAR2(2000);
585 ERR_MSG VARCHAR2(100);
586 ERR_CDE NUMBER;
587 l_context_code VARCHAR2(10);
588 l_to_date_type VARCHAR2(10);
589 BEGIN
590
591 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
592 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
593
594 poa_dbi_sutil_pkg.process_parameters(p_param,
595 l_view_by,
596 l_view_by_col_name,
597 l_view_by_value,
598 l_comparison_type,
599 l_xtd,
600 l_as_of_date,
601 l_prev_as_of_date,
602 l_cur_suffix,
603 l_nested_pattern,
604 l_where_clause,
605 l_mv,
606 l_join_tbl,
607 l_in_join_tbl,
608 x_custom_output,
609 'Y',
610 'PO',
611 '6.0',
612 'COM',
613 'RTX');
614 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
615 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
616 l_to_date_type := 'RLX';
617 ELSE
618 l_to_date_type := 'XTD';
619 END IF;
620
621 poa_dbi_util_pkg.add_column(l_col_tbl, 'amt_return_' || l_cur_suffix, 'amt_return','Y',3,p_to_date_type => l_to_date_type);
622 poa_dbi_util_pkg.add_column(l_col_tbl,'num_txns_return_cnt','cnt_return','Y',3, p_to_date_type => l_to_date_type);
623
624 l_query := poa_dbi_sutil_pkg.get_viewby_select_clause(l_view_by, 'PO','6.0');
625
626 l_query := l_query || '
627 oset.POA_MEASURE1 POA_MEASURE1, -- Return Amount
628 oset.POA_MEASURE2 poa_measure2, -- Prior Return Amount
629 oset.POA_MEASURE5 poa_measure5, -- Total Return Amount
630 oset.POA_MEASURE6 poa_measure6, -- Total Prior Return Amount
631 oset.POA_MEASURE3 poa_measure3, -- Return Transactions
632 oset.POA_MEASURE4 poa_measure4, -- Prior Return Transactions
633 oset.POA_MEASURE8 poa_measure8, -- Total Return Trasactions
634 oset.POA_MEASURE9 poa_measure9 -- Total Prior Return Transactions
635 from
636 (select * from
637 (select ' || l_view_by_col_name || ',';
638 if ( l_view_by = 'ITEM+POA_ITEMS' ) then
639 l_query := l_query || ' base_uom, ';
640 end if;
641 l_query := l_query || '
642 nvl(c_amt_return,0) POA_MEASURE1,
643 nvl(p_amt_return,0) poa_measure2,
644 nvl(c_amt_return_total,0) poa_measure5,
645 nvl(p_amt_return_total,0) poa_measure6,
646 nvl(c_cnt_return,0) POA_MEASURE3,
647 nvl(p_cnt_return,0) poa_measure4,
648 nvl(c_cnt_return_total,0) POA_MEASURE8,
649 nvl(p_cnt_return_total,0) poa_measure9
650 from '
651 || poa_dbi_template_pkg.status_sql(
652 l_mv,
653 l_where_clause,
654 l_join_tbl,
655 p_use_windowing => 'N',
656 p_col_name => l_col_tbl,
657 p_use_grpid => 'N',
658 p_in_join_tables => l_in_join_tbl);
659
660 x_custom_sql := l_query;
661
662 END;
663
664 FUNCTION get_trend_sel_clause return VARCHAR2
665 IS
666 l_sel_clause varchar2(4000);
667 BEGIN
668 l_sel_clause := 'select cal.name VIEWBY,';
669 l_sel_clause := l_sel_clause || '
670 nvl(c_amt_return,0) POA_MEASURE1,
671 p_amt_return POA_MEASURE2,
672 ' || poa_dbi_util_pkg.change_clause('c_amt_return','p_amt_return') || ' poa_percent1,
673 Nvl(c_cnt_return,0) poa_measure4,
674 p_cnt_return poa_measure5,
675 ' || poa_dbi_util_pkg.change_clause('c_cnt_return','p_cnt_return') || ' poa_percent2'
676 ;
677 return l_sel_clause;
678 END;
679
680 end poa_dbi_ret_pkg;