[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_UFR_PKG
Source
1 PACKAGE BODY poa_dbi_ufr_pkg
2 /* $Header: poadbiufrb.pls 120.3 2006/08/08 10:51:43 nchava noship $ */
3 AS
4 --
5 FUNCTION get_amt_sel_clause(
6 p_view_by_dim in VARCHAR2
7 ,p_view_by_col in VARCHAR2
8 , p_bucket_rec in BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE) return VARCHAR2;
9 FUNCTION get_sum_sel_clause(
10 p_view_by_dim in VARCHAR2
11 ,p_view_by_col in VARCHAR2) return VARCHAR2;
12 FUNCTION get_status_sel_clause(
13 p_view_by_dim in VARCHAR2
14 ,p_view_by_col in VARCHAR2) return VARCHAR2;
15 FUNCTION get_age_sel_clause(
16 p_view_by_dim in VARCHAR2
17 ,p_view_by_col in VARCHAR2
18 , p_bucket_rec in BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE) return VARCHAR2;
19 FUNCTION get_status_filter_where(p_view_by IN VARCHAR2) return VARCHAR2;
20 FUNCTION get_amt_filter_where(p_view_by IN VARCHAR2) return VARCHAR2;
21 FUNCTION get_age_filter_where(p_view_by IN VARCHAR2) return VARCHAR2;
22 FUNCTION get_sum_rpt_filter_where(p_view_by IN VARCHAR2) return VARCHAR2;
23
24
25 PROCEDURE status_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
26 x_custom_sql OUT NOCOPY VARCHAR2,
27 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
28 IS
29 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
30 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
31 l_query varchar2(10000);
32 l_view_by varchar2(120);
33 l_view_by_col varchar2(120);
34 l_as_of_date date;
35 l_prev_as_of_date date;
36 l_xtd varchar2(10);
37 l_comparison_type varchar2(1);
38 l_nested_pattern number;
39 l_cur_suffix varchar2(2);
40 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
41 l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
42 l_where_clause VARCHAR2(2000);
43 l_mv VARCHAR2(30);
44 l_view_by_value varchar2(30);
45 ERR_MSG VARCHAR2(100);
46 ERR_CDE NUMBER;
47 BEGIN
48
49 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
50 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
51 l_comparison_type := 'Y';
52 poa_dbi_sutil_pkg.process_parameters(p_param
53 ,l_view_by
54 ,l_view_by_col
55 ,l_view_by_value
56 ,l_comparison_type
57 ,l_xtd
58 ,l_as_of_date
59 ,l_prev_as_of_date
60 ,l_cur_suffix
61 ,l_nested_pattern
62 ,l_where_clause
63 ,l_mv
64 ,l_join_tbl
65 ,l_in_join_tbl
66 ,x_custom_output
67 ,p_trend => 'N'
68 ,p_func_area => 'PO'
69 ,p_version => '7.1'
70 ,p_role => 'VPP'
71 ,p_mv_set => 'REQS');
72
73
74 poa_dbi_util_pkg.add_column(l_col_tbl
75 ,'unfulfilled_cnt'
76 , 'unfulf_cnt'
77 , p_grand_total => 'Y'
78 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
79 , p_to_date_type => 'NA');
80 poa_dbi_util_pkg.add_column(l_col_tbl
81 , 'unfulfilled_ped_cnt'
82 , 'unfulf_ped_cnt'
83 , p_grand_total => 'Y'
84 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
85 , p_to_date_type => 'NA');
86 poa_dbi_util_pkg.add_column(l_col_tbl
87 , 'unfulfilled_amt_' || l_cur_suffix
88 , 'unfulf_amt'
89 , p_grand_total => 'Y'
90 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
91 , p_to_date_type => 'NA');
92 poa_dbi_util_pkg.add_column(l_col_tbl
93 , 'num_days_unfulfilled'
94 , 'num_days_unfulf'
95 , p_grand_total => 'Y'
96 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
97 , p_to_date_type => 'NA');
98
99 if(l_view_by = 'ITEM+POA_ITEMS') then
100 poa_dbi_util_pkg.add_column(l_col_tbl
101 ,'unfulfilled_qty'
102 ,'unfulf_qty'
103 , p_grand_total => 'N'
104 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
105 , p_to_date_type => 'NA');
106
107 end if;
108
109 l_query := get_status_sel_clause(l_view_by, l_view_by_col) || ' from '||
110 poa_dbi_template_pkg.status_sql(
111 l_mv,
112 l_where_clause,
113 l_join_tbl,
114 p_use_windowing => 'P',
115 p_col_name => l_col_tbl,
116 p_use_grpid => 'N',
117 p_filter_where => get_status_filter_where(l_view_by),
118 p_in_join_tables => l_in_join_tbl);
119
120 x_custom_sql := l_query;
121
122
123 EXCEPTION
124 WHEN OTHERS THEN
125 ERR_MSG := SUBSTR(SQLERRM,1,400);
126 end status_sql;
127
128 FUNCTION get_status_sel_clause(p_view_by_dim in VARCHAR2
129 ,p_view_by_col in VARCHAR2) return VARCHAR2 IS
130 l_sel_clause varchar2(4000);
131
132 BEGIN
133
134 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
135 ,'PO'
136 ,'7.1');
137
138
139 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
140 l_sel_clause := l_sel_clause
141 || fnd_global.newline
142 ||
143 ' v.description POA_ATTRIBUTE1, --Description
144 v2.description POA_ATTRIBUTE2, --UOM
145 oset.POA_MEASURE10 POA_MEASURE10, --Unfulfilled Quantity
146
147 '|| fnd_global.newline;
148
149 else
150 l_sel_clause := l_sel_clause
151 || fnd_global.newline
152 ||
153 ' null POA_ATTRIBUTE1, --Description
154 null POA_ATTRIBUTE2, --UOM
155 null POA_MEASURE10, --Quantity'
156 || fnd_global.newline;
157
158 end if;
159
160 l_sel_clause := l_sel_clause ||
161 ' oset.POA_MEASURE1 POA_MEASURE1, --Unfulfilled Lines
162 oset.POA_PERCENT1 POA_PERCENT1, --Percent of Total
163 oset.POA_MEASURE1 POA_MEASURE5, --Unfulfilled Lines for graph 2
164 oset.POA_MEASURE2 POA_MEASURE2, --Lines Past Expected Date
165 oset.POA_MEASURE3 POA_MEASURE3, --Unfulfilled Amount
166 oset.POA_MEASURE4 POA_MEASURE4, --Average Age Days
167 oset.POA_MEASURE6 POA_MEASURE6, --Grand Total Unfulfilled Lines
168 oset.POA_PERCENT2 POA_PERCENT2, --Grand Total Percent of Total
169 oset.POA_MEASURE7 POA_MEASURE7, --Grand Total Lines Past Exp Date
170 oset.POA_MEASURE8 POA_MEASURE8, --Grand Total Unfulfilled Amount
171 oset.POA_MEASURE9 POA_MEASURE9, --Grand Total Days Past
172 oset.POA_MEASURE2 POA_MEASURE11, --Lines past exp for graph';
173
174
175 l_sel_clause := l_sel_clause || '
176 ''pFunctionName=POA_DBI_UFR_SUM_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_PO_CAT&pParamIds=Y'' POA_ATTRIBUTE6,
177 ''pFunctionName=POA_DBI_UFR_SUM_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_PO_CAT&pParamIds=Y'' POA_ATTRIBUTE7,
178 ''pFunctionName=POA_DBI_UFR_PED_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=1'' POA_ATTRIBUTE8,
179 ''pFunctionName=POA_DBI_UFR_PED_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=1'' POA_ATTRIBUTE9,
180 ''pFunctionName=POA_DBI_UFR_AMT_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM_PO_CAT'' POA_ATTRIBUTE10,
181 ''pFunctionName=POA_DBI_UFR_AGE_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM_PO_CAT'' POA_ATTRIBUTE11 ';
182
183
184 l_sel_clause := l_sel_clause ||
185 ' from (select * from (select * from
186 (select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
187 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
188 l_sel_clause := l_sel_clause || ', base_uom';
189 end if;
190
191
192 l_sel_clause := l_sel_clause || ')) - 1 rnk,' || p_view_by_col;
193 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
194 l_sel_clause := l_sel_clause || ',
195 base_uom,
196 POA_MEASURE10';
197 end if;
198
199 l_sel_clause := l_sel_clause || ',POA_MEASURE1,POA_PERCENT1,
200 POA_MEASURE2,POA_MEASURE3,
201 POA_PERCENT2,
202 POA_MEASURE6,POA_MEASURE7,
203 POA_MEASURE8,POA_MEASURE4,POA_MEASURE9
204 from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
205 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
206 l_sel_clause := l_sel_clause || ' base_uom,
207 decode(base_uom,null,to_number(null),nvl(c_unfulf_qty,0)) POA_MEASURE10, ';
208
209 end if;
210 l_sel_clause := l_sel_clause || ' nvl(c_unfulf_cnt,0) POA_MEASURE1,
211 '
212 || poa_dbi_util_pkg.rate_clause('c_unfulf_cnt', 'c_unfulf_cnt_total', 'P') || ' POA_PERCENT1,
213 nvl(c_unfulf_ped_cnt,0) POA_MEASURE2,
214 nvl(c_unfulf_amt,0) POA_MEASURE3,
215 nvl(c_unfulf_cnt_total,0) POA_MEASURE6,
216 decode(c_unfulf_cnt_total, null, null, 100) POA_PERCENT2,
217 nvl(c_unfulf_ped_cnt_total,0) POA_MEASURE7,
218 nvl(c_unfulf_amt_total,0) POA_MEASURE8,
219 ' ||
220 poa_dbi_util_pkg.rate_clause('c_num_days_unfulf','c_unfulf_cnt', 'NP')|| ' POA_MEASURE4,
221 ' ||
222 poa_dbi_util_pkg.rate_clause('c_num_days_unfulf_total','c_unfulf_cnt_total','NP')|| ' POA_MEASURE9
223 ';
224
225 return l_sel_clause;
226 END get_status_sel_clause;
227
228 FUNCTION get_status_filter_where(p_view_by in VARCHAR2) return VARCHAR2
229 IS
230 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
231 BEGIN
232 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
233 l_col_tbl.extend;
234 l_col_tbl(1) := 'POA_MEASURE1';
235 l_col_tbl.extend;
236 l_col_tbl(2) := 'POA_PERCENT1';
237 l_col_tbl.extend;
238 l_col_tbl(3) := 'POA_MEASURE2';
239 l_col_tbl.extend;
240 l_col_tbl(4) := 'POA_MEASURE3';
241 l_col_tbl.extend;
242 l_col_tbl(5) := 'POA_MEASURE4';
243
244 if(p_view_by = 'ITEM+POA_ITEMS') then
245 l_col_tbl.extend;
246 l_col_tbl(6) := 'POA_MEASURE10';
247 end if;
248
249 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
250
251 END;
252
253
254 PROCEDURE amt_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
255 x_custom_sql OUT NOCOPY VARCHAR2,
256 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
257 IS
258 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
259 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
260 l_query varchar2(10000);
261 l_view_by varchar2(120);
262 l_view_by_col varchar2(120);
263 l_as_of_date date;
264 l_prev_as_of_date date;
265 l_xtd varchar2(10);
266 l_comparison_type varchar2(1);
267 l_nested_pattern number;
268 l_cur_suffix varchar2(2);
269 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
270 l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
271 l_where_clause VARCHAR2(2000);
272 l_mv VARCHAR2(30);
273 l_view_by_value varchar2(30);
274 l_bucket_rec BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE;
275 ERR_MSG VARCHAR2(100);
276 ERR_CDE NUMBER;
277 BEGIN
278 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
279 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
280 l_comparison_type := 'Y';
281 poa_dbi_sutil_pkg.process_parameters(p_param
282 , l_view_by
283 , l_view_by_col
284 , l_view_by_value
285 , l_comparison_type
286 , l_xtd
287 , l_as_of_date
288 , l_prev_as_of_date
289 , l_cur_suffix
290 , l_nested_pattern
291 , l_where_clause
292 , l_mv
293 , l_join_tbl
294 , l_in_join_tbl
295 ,x_custom_output
296 , p_trend => 'N'
297 , p_func_area => 'PO'
298 , p_version => '7.1'
299 , p_role => 'VPP'
300 , p_mv_set => 'REQS');
301 poa_dbi_util_pkg.add_bucket_columns(
302 p_short_name => 'POA_DBI_UFR_BUCKET'
303 ,p_col_tbl => l_col_tbl
304 ,p_col_name => 'unfulfilled_amt_' || l_cur_suffix || '_age'
305 ,p_alias_name => 'unfulf_amt_age'
306 ,x_bucket_rec => l_bucket_rec
307 ,p_grand_total => 'Y'
308 ,p_prior_code => poa_dbi_util_pkg.NO_PRIORS
309 ,p_to_date_type => 'NA');
310
311 poa_dbi_util_pkg.add_column(l_col_tbl
312 ,'unfulfilled_amt_'|| l_cur_suffix
313 , 'unfulf_amt'
314 , p_grand_total => 'Y'
315 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
316 , p_to_date_type => 'NA');
317 poa_dbi_util_pkg.add_column(l_col_tbl
318 ,'pen_fulfill_amt_'|| l_cur_suffix
319 , 'pen_fulf_amt'
320 , p_grand_total => 'Y'
321 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
322 , p_to_date_type => 'NA');
323 if(l_view_by = 'ITEM+POA_ITEMS') then
324 poa_dbi_util_pkg.add_column(l_col_tbl
325 ,'unfulfilled_qty'
326 ,'unfulf_qty'
327 , p_grand_total => 'N'
328 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
329 , p_to_date_type => 'NA');
330 end if;
331
332
333 l_query := get_amt_sel_clause(
334 l_view_by
335 , l_view_by_col,l_bucket_rec) || ' from' ||
336 poa_dbi_template_pkg.status_sql(
337 l_mv,
338 l_where_clause,
339 l_join_tbl,
340 p_use_windowing => 'P',
341 p_col_name => l_col_tbl,
342 p_use_grpid => 'N',
343 p_filter_where => get_amt_filter_where(l_view_by),
344 p_in_join_tables => l_in_join_tbl);
345
346 x_custom_sql := l_query;
347
348 EXCEPTION
349 WHEN OTHERS THEN
350 ERR_MSG := SUBSTR(SQLERRM,1,400);
351 END amt_sql;
352
353
354 FUNCTION get_amt_sel_clause(
355 p_view_by_dim in VARCHAR2
356 , p_view_by_col in VARCHAR2
357 , p_bucket_rec in BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE)
358
359 return VARCHAR2
360 IS
361 l_sel_clause varchar2(4000);
362 BEGIN
363 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
364 ,'PO'
365 ,'7.1');
366
367
368 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
369 l_sel_clause := l_sel_clause
370 || fnd_global.newline
371 || '
372 v.description POA_ATTRIBUTE1,
373 v2.description POA_ATTRIBUTE2,
374 oset.POA_MEASURE1 POA_MEASURE1,
375
376 '|| fnd_global.newline;
377
378 else
379 l_sel_clause := l_sel_clause
380 || fnd_global.newline
381 || '
382 null POA_ATTRIBUTE1,
383 null POA_ATTRIBUTE2,
384 null POA_MEASURE1, '
385 || fnd_global.newline;
386
387 end if;
388
389 l_sel_clause := l_sel_clause ||
390 ' oset.POA_MEASURE2 POA_MEASURE2, --unfulf amt
391 oset.POA_MEASURE3 POA_MEASURE3, -- unfulf amt pend process
392 oset.POA_MEASURE4 POA_MEASURE4 -- pending fulf'
393 || fnd_global.newline
394 || poa_dbi_util_pkg.get_bucket_outer_query(
395 p_bucket_rec
396 , p_col_name => 'oset.POA_MEASURE5'
397 , p_alias_name => 'POA_MEASURE5'
398 , p_prefix => ''
399 , p_suffix => ''
400 , p_total_flag => 'N')
401 || fnd_global.newline
402 || ',
403 oset.POA_MEASURE6 POA_MEASURE6, --unfulf amt total
404 oset.POA_MEASURE7 POA_MEASURE7, -- pend process total
405 oset.POA_MEASURE8 POA_MEASURE8 -- unfulf amt pend fulf total'
406 || fnd_global.newline
407 || poa_dbi_util_pkg.get_bucket_outer_query(
408 p_bucket_rec
409 , p_col_name => 'oset.POA_MEASURE9'
410 , p_alias_name => 'POA_MEASURE9'
411 , p_prefix => ''
412 , p_suffix => ''
413 , p_total_flag => 'N')
414 || fnd_global.newline
415 ||
416 ',
417 oset.POA_MEASURE3 POA_ATTRIBUTE4, --unful amt-pending proc for graph
418 oset.POA_MEASURE4 POA_ATTRIBUTE5 --unful amt-pending fulf for graph '
419 || fnd_global.newline ;
420
421
422 l_sel_clause := l_sel_clause ||
423 poa_dbi_util_pkg.get_bucket_drill_url(
424 p_bucket_rec
425 , 'POA_ATTRIBUTE7'
426 ,
427 '''pFunctionName=POA_DBI_UFR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=-2&POA_BUCKET+REQUISITION_AGING='
428 , ''''
429 , p_add_bucket_num => 'Y') || ',';
430
431
432 l_sel_clause := l_sel_clause || '
433 ''pFunctionName=POA_DBI_UFR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=-2'' POA_ATTRIBUTE9,
434 ''pFunctionName=POA_DBI_UFR_PR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=2'' POA_ATTRIBUTE11,
435 ''pFunctionName=POA_DBI_UFR_FR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=3'' POA_ATTRIBUTE12,
436 ''pFunctionName=POA_DBI_UFR_PR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=2'' POA_ATTRIBUTE13,
437 ''pFunctionName=POA_DBI_UFR_FR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=3'' POA_ATTRIBUTE14 ';
438
439
440 l_sel_clause := l_sel_clause || ' from
441 (select * from (select * from (select
442 (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
443
444 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
445 l_sel_clause := l_sel_clause || ', base_uom';
446 end if;
447
448 l_sel_clause := l_sel_clause || ')) - 1 rnk,' || p_view_by_col;
449 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
450 l_sel_clause := l_sel_clause || ',
451 base_uom,
452 POA_MEASURE1';
453 end if;
454
455 l_sel_clause := l_sel_clause || ',POA_MEASURE2,POA_MEASURE3,
456 POA_MEASURE4 '
457 || fnd_global.newline
458 || poa_dbi_util_pkg.get_bucket_outer_query(
459 p_bucket_rec
460 , p_col_name => 'POA_MEASURE5'
461 , p_alias_name => 'POA_MEASURE5'
462 , p_prefix => ''
463 , p_suffix => ''
464 , p_total_flag => 'N')
465 || fnd_global.newline ||
466 ' ,POA_MEASURE6,
467 POA_MEASURE7,POA_MEASURE8'
468 || poa_dbi_util_pkg.get_bucket_outer_query(
469 p_bucket_rec
470 , p_col_name => 'POA_MEASURE9'
471 , p_alias_name => 'POA_MEASURE9'
472 , p_prefix => ''
473 , p_suffix => ''
474 , p_total_flag => 'N')
475 || fnd_global.newline || '
476 from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
477
478 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
479 l_sel_clause := l_sel_clause || ' base_uom,
480 decode(base_uom,null,to_number(null),nvl(c_unfulf_qty,0)) POA_MEASURE1, ';
481
482 end if;
483 l_sel_clause := l_sel_clause
484 || ' nvl(c_unfulf_amt,0) POA_MEASURE2,
485 (nvl(c_unfulf_amt,0)-nvl(c_pen_fulf_amt,0)) POA_MEASURE3,
486 nvl(c_pen_fulf_amt,0) POA_MEASURE4';
487 l_sel_clause := l_sel_clause || fnd_global.newline
488 || poa_dbi_util_pkg.get_bucket_outer_query(
489 p_bucket_rec
490 , p_col_name => 'c_unfulf_amt_age'
491 , p_alias_name => 'POA_MEASURE5'
492 , p_prefix => 'nvl('
493 , p_suffix => ',0)'
494 , p_total_flag => 'N')
495 || fnd_global.newline || ',
496
497 nvl(c_unfulf_amt_total,0) POA_MEASURE6,
498 (nvl(c_unfulf_amt_total,0)-nvl(c_pen_fulf_amt_total,0)) POA_MEASURE7,
499 nvl(c_pen_fulf_amt_total,0) POA_MEASURE8'
500 || poa_dbi_util_pkg.get_bucket_outer_query(
501 p_bucket_rec
502 , p_col_name => 'c_unfulf_amt_age'
503 , p_alias_name => 'POA_MEASURE9'
504 , p_prefix => 'nvl('
505 , p_suffix => ',0)'
506 , p_total_flag => 'Y')
507 || fnd_global.newline;
508
509 return l_sel_clause;
510 END get_amt_sel_clause;
511
512 FUNCTION get_amt_filter_where(p_view_by in VARCHAR2) return VARCHAR2
513 IS
514 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
515 BEGIN
516 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
517 l_col_tbl.extend;
518 l_col_tbl(1) := 'POA_MEASURE2';
519 l_col_tbl.extend;
520 l_col_tbl(2) := 'POA_MEASURE3';
521 l_col_tbl.extend;
522 l_col_tbl(3) := 'POA_MEASURE4';
523
524 if(p_view_by = 'ITEM+POA_ITEMS') then
525 l_col_tbl.extend;
526 l_col_tbl(4) := 'POA_MEASURE1';
527 end if;
528
529 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
530
531 END;
532
533
534 PROCEDURE sum_rpt_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
535 x_custom_sql OUT NOCOPY VARCHAR2,
536 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
537 IS
538 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
539 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
540 l_query varchar2(10000);
541 l_view_by varchar2(120);
542 l_view_by_col varchar2(120);
543 l_as_of_date date;
544 l_prev_as_of_date date;
545 l_xtd varchar2(10);
546 l_comparison_type varchar2(1);
547 l_nested_pattern number;
548 l_cur_suffix varchar2(2);
549 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
550 l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
551 l_where_clause VARCHAR2(2000);
552 l_mv VARCHAR2(30);
553 l_view_by_value varchar2(30);
554 ERR_MSG VARCHAR2(100);
555 ERR_CDE NUMBER;
556 BEGIN
557 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
558 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
559 l_comparison_type := 'Y';
560 poa_dbi_sutil_pkg.process_parameters(p_param
561 , l_view_by
562 , l_view_by_col
563 , l_view_by_value
564 , l_comparison_type
565 , l_xtd
566 , l_as_of_date
567 , l_prev_as_of_date
568 , l_cur_suffix
569 , l_nested_pattern
570 , l_where_clause
571 , l_mv
572 , l_join_tbl
573 , l_in_join_tbl
574 , x_custom_output
575 , p_trend => 'N'
576 , p_func_area => 'PO'
577 , p_version => '7.1'
578 , p_role => 'VPP'
579 , p_mv_set => 'REQS');
580
581 poa_dbi_util_pkg.add_column(l_col_tbl
582 ,'unfulfilled_cnt'
583 , 'unfulf_cnt'
584 , p_grand_total => 'Y'
585 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
586 , p_to_date_type => 'NA');
587 poa_dbi_util_pkg.add_column(l_col_tbl
588 ,'pen_fulfill_cnt'
589 , 'pen_fulf_cnt'
590 , p_grand_total => 'Y'
591 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
592 , p_to_date_type => 'NA');
593 poa_dbi_util_pkg.add_column(l_col_tbl
594 ,'unfulfilled_ped_cnt'
595 , 'unfulf_ped_cnt'
596 , p_grand_total => 'Y'
597 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
598 , p_to_date_type => 'NA');
599
600 poa_dbi_util_pkg.add_column(l_col_tbl
601 ,'unful_po_revisions'
602 , 'unful_po_revisions'
603 , p_grand_total => 'Y'
604 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
605 , p_to_date_type => 'NA');
606 if(l_view_by = 'ITEM+POA_ITEMS') then
607 poa_dbi_util_pkg.add_column(l_col_tbl
608 ,'unfulfilled_qty'
609 ,'unfulf_qty'
610 , p_grand_total => 'N'
611 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
612 , p_to_date_type => 'NA');
613 end if;
614
615 l_query := get_sum_sel_clause(
616 l_view_by
617 , l_view_by_col) || ' from' ||
618 poa_dbi_template_pkg.status_sql(
619 l_mv,
620 l_where_clause,
621 l_join_tbl,
622 p_use_windowing => 'P',
623 p_col_name => l_col_tbl,
624 p_use_grpid => 'N',
625 p_filter_where => get_sum_rpt_filter_where(l_view_by),
626 p_in_join_tables => l_in_join_tbl);
627
628 x_custom_sql := l_query;
629
630 EXCEPTION
631 WHEN OTHERS THEN
632 ERR_MSG := SUBSTR(SQLERRM,1,400);
633 END sum_rpt_sql;
634
635
636
637
638 FUNCTION get_sum_sel_clause(
639 p_view_by_dim in VARCHAR2
640 , p_view_by_col in VARCHAR2
641 ) return VARCHAR2
642 IS
643 l_sel_clause varchar2(4000);
644 BEGIN
645 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
646 ,'PO'
647 ,'7.1');
648
649
650 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
651 l_sel_clause := l_sel_clause
652 || fnd_global.newline
653 || '
654 v.description POA_ATTRIBUTE1, --Description
655 v2.description POA_ATTRIBUTE2, --UOM
656 oset.POA_MEASURE1 POA_MEASURE1, --Unfulfilled Quantity
657
658 '|| fnd_global.newline;
659
660 else
661 l_sel_clause := l_sel_clause
662 || fnd_global.newline
663 || '
664 null POA_ATTRIBUTE1, --Description
665 null POA_ATTRIBUTE2, --UOM
666 null POA_MEASURE1, --Unfulfilled Quantity'
667
668 || fnd_global.newline;
669
670 end if;
671
672 l_sel_clause := l_sel_clause ||
673 ' oset.POA_MEASURE2 POA_MEASURE2, --Total Requisition Lines
674 oset.POA_MEASURE3 POA_MEASURE3, --Lines Pending Processing
675 oset.POA_MEASURE4 POA_MEASURE4, --Lines Processed Pending Fulfillment
676 oset.POA_MEASURE5 POA_MEASURE5, --Lines Past Expected Date
677 oset.POA_MEASURE6 POA_MEASURE6, --PO Revisions
678 oset.POA_MEASURE7 POA_MEASURE7, --Grand Total for Total Column
679 oset.POA_MEASURE8 POA_MEASURE8, --Grand Total for Pending Processing
680 oset.POA_MEASURE9 POA_MEASURE9, --Grand Total for Processed Pending Fulfillment
681 oset.POA_MEASURE10 POA_MEASURE10, --Grand Total for Lines Past Expected Date
682 oset.POA_MEASURE11 POA_MEASURE11, --Grand total for PO Revisions
683 oset.POA_MEASURE3 POA_ATTRIBUTE4, --Pending Processing for graph
684 oset.POA_MEASURE4 POA_ATTRIBUTE5, --Processed Pending Fulfillment for graph
685 oset.POA_MEASURE2 POA_ATTRIBUTE6, --Unfulfilled Lines for graph
686 oset.POA_MEASURE5 POA_ATTRIBUTE7, --Lines Past Expected Date for graph';
687
688 l_sel_clause := l_sel_clause || '
689 ''pFunctionName=POA_DBI_UFR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=-2'' POA_ATTRIBUTE11,
690 ''pFunctionName=POA_DBI_UFR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=-2'' POA_ATTRIBUTE13,
691 ''pFunctionName=POA_DBI_UFR_PR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=2'' POA_ATTRIBUTE14,
692 ''pFunctionName=POA_DBI_UFR_PR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=2'' POA_ATTRIBUTE15,
693 ''pFunctionName=POA_DBI_UFR_FR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=3'' POA_ATTRIBUTE16,
694 ''pFunctionName=POA_DBI_UFR_FR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=3'' POA_ATTRIBUTE17,
695 ''pFunctionName=POA_DBI_UFR_PED_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=1'' POA_ATTRIBUTE18,
696 ''pFunctionName=POA_DBI_UFR_PED_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=1'' POA_ATTRIBUTE19,
697 ''pFunctionName=POA_DBI_UFR_POR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=-2'' POA_ATTRIBUTE20 ';
698
699
700 l_sel_clause := l_sel_clause ||
701 ' from
702 (select * from (select * from (select
703 (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
704 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
705 l_sel_clause := l_sel_clause || ', base_uom';
706 end if;
707 l_sel_clause := l_sel_clause || ')) - 1 rnk,' || p_view_by_col;
708 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
709 l_sel_clause := l_sel_clause || ',
710 base_uom,
711 POA_MEASURE1';
712 end if;
713
714 l_sel_clause := l_sel_clause || ',POA_MEASURE2,POA_MEASURE3,
715 POA_MEASURE4,POA_MEASURE5,
716 POA_MEASURE7,POA_MEASURE8,POA_MEASURE9,POA_MEASURE10,POA_MEASURE6,POA_MEASURE11
717 from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
718
719 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
720 l_sel_clause := l_sel_clause || ' base_uom,
721 decode(base_uom,null,to_number(null),nvl(c_unfulf_qty,0)) POA_MEASURE1, ';
722
723 end if;
724 l_sel_clause := l_sel_clause
725 || ' nvl(c_unfulf_cnt,0) POA_MEASURE2,
726 (nvl(c_unfulf_cnt,0)-nvl(c_pen_fulf_cnt,0)) POA_MEASURE3,
727 nvl(c_pen_fulf_cnt,0) POA_MEASURE4,
728 nvl(c_unfulf_ped_cnt,0) POA_MEASURE5,
729 nvl(c_unfulf_cnt_total,0) POA_MEASURE7,
730 (nvl(c_unfulf_cnt_total,0)-nvl(c_pen_fulf_cnt_total,0)) POA_MEASURE8,
731 nvl(c_pen_fulf_cnt_total,0) POA_MEASURE9,
732 nvl(c_unfulf_ped_cnt_total,0) POA_MEASURE10,
733 nvl(c_unful_po_revisions,0) POA_MEASURE6,
734 nvl(c_unful_po_revisions_total,0) POA_MEASURE11
735 ';
736
737 return l_sel_clause;
738 END get_sum_sel_clause;
739
740 FUNCTION get_sum_rpt_filter_where(p_view_by in VARCHAR2) return VARCHAR2
741 IS
742 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
743 BEGIN
744 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
745 l_col_tbl.extend;
746 l_col_tbl(1) := 'POA_MEASURE2';
747 l_col_tbl.extend;
748 l_col_tbl(2) := 'POA_MEASURE3';
749 l_col_tbl.extend;
750 l_col_tbl(3) := 'POA_MEASURE4';
751 l_col_tbl.extend;
752 l_col_tbl(4) := 'POA_MEASURE5';
753 l_col_tbl.extend;
754 l_col_tbl(5) := 'POA_MEASURE6';
755
756 if(p_view_by = 'ITEM+POA_ITEMS') then
757 l_col_tbl.extend;
758 l_col_tbl(6) := 'POA_MEASURE1';
759 end if;
760
761 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
762
763 END;
764
765 PROCEDURE age_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
766 x_custom_sql OUT NOCOPY VARCHAR2,
767 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
768 IS
769 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
770 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
771 l_query varchar2(10000);
772 l_view_by varchar2(120);
773 l_view_by_col varchar2(120);
774 l_as_of_date date;
775 l_prev_as_of_date date;
776 l_xtd varchar2(10);
777 l_comparison_type varchar2(1);
778 l_nested_pattern number;
779 l_cur_suffix varchar2(2);
780 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
781 l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
782 l_where_clause VARCHAR2(2000);
783 l_mv VARCHAR2(30);
784 l_view_by_value varchar2(30);
785 l_bucket_rec BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE;
786 ERR_MSG VARCHAR2(100);
787 ERR_CDE NUMBER;
788 BEGIN
789 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
790 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
791 l_comparison_type := 'Y';
792 poa_dbi_sutil_pkg.process_parameters(p_param
793 , l_view_by
794 , l_view_by_col
795 , l_view_by_value
796 , l_comparison_type
797 , l_xtd
798 , l_as_of_date
799 , l_prev_as_of_date
800 , l_cur_suffix
801 , l_nested_pattern
802 , l_where_clause
803 , l_mv
804 , l_join_tbl
805 , l_in_join_tbl
806 , x_custom_output
807 , p_trend => 'N'
808 , p_func_area => 'PO'
809 , p_version => '7.1'
810 , p_role => 'VPP'
811 , p_mv_set => 'REQS');
812 poa_dbi_util_pkg.add_bucket_columns(
813 p_short_name => 'POA_DBI_UFR_BUCKET'
814 ,p_col_tbl => l_col_tbl
815 ,p_col_name => 'unfulfilled_cnt_age'
816 ,p_alias_name => 'unfulf_cnt_age'
817 ,x_bucket_rec => l_bucket_rec
818 ,p_grand_total => 'Y'
819 ,p_prior_code => poa_dbi_util_pkg.NO_PRIORS
820 ,p_to_date_type => 'NA');
821
822 poa_dbi_util_pkg.add_column(l_col_tbl
823 ,'unfulfilled_cnt'
824 , 'unfulf_cnt'
825 , p_grand_total => 'Y'
826 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
827 , p_to_date_type => 'NA');
828 poa_dbi_util_pkg.add_column(l_col_tbl
829 ,'num_days_unfulfilled'
830 , 'num_days_unfulf'
831 , p_grand_total => 'Y'
832 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
833 , p_to_date_type => 'NA');
834
835 if(l_view_by = 'ITEM+POA_ITEMS') then
836 poa_dbi_util_pkg.add_column(l_col_tbl
837 ,'unfulfilled_qty'
838 ,'unfulf_qty'
839 , p_grand_total => 'N'
840 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
841 , p_to_date_type => 'NA');
842 end if;
843
844
845 l_query := get_age_sel_clause(
846 l_view_by
847 , l_view_by_col,l_bucket_rec) || ' from' ||
848 poa_dbi_template_pkg.status_sql(
849 l_mv,
850 l_where_clause,
851 l_join_tbl,
852 p_use_windowing => 'P',
853 p_col_name => l_col_tbl,
854 p_use_grpid => 'N',
855 p_filter_where => get_age_filter_where(l_view_by),
856 p_in_join_tables => l_in_join_tbl);
857
858 x_custom_sql := l_query;
859
860 EXCEPTION
861 WHEN OTHERS THEN
862 ERR_MSG := SUBSTR(SQLERRM,1,400);
863 END age_sql;
864
865
866 FUNCTION get_age_sel_clause(
867 p_view_by_dim in VARCHAR2
868 , p_view_by_col in VARCHAR2
869 , p_bucket_rec in BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE)
870
871 return VARCHAR2
872 IS
873 l_sel_clause varchar2(4000);
874 BEGIN
875 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
876 ,'PO'
877 ,'7.1');
878
879 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
880 l_sel_clause := l_sel_clause
881 || fnd_global.newline
882 || '
883 v.description POA_ATTRIBUTE1, --Description
884 v2.description POA_ATTRIBUTE2, --UOM
885 oset.POA_MEASURE1 POA_MEASURE1, --Unfulfilled Quantity
886
887 '|| fnd_global.newline;
888
889 else
890 l_sel_clause := l_sel_clause
891 || fnd_global.newline
892 || '
893 null POA_ATTRIBUTE1, --Description
894 null POA_ATTRIBUTE2, --UOM
895 null POA_MEASURE1, --Unfulfilled Quantity'
896
897 || fnd_global.newline;
898
899 end if;
900
901 l_sel_clause := l_sel_clause ||
902 ' oset.POA_MEASURE2 POA_MEASURE2, --Average Age (Days)
903 oset.POA_MEASURE3 POA_MEASURE3 --Unfulfilled Lines'
904
905 || fnd_global.newline
906 || poa_dbi_util_pkg.get_bucket_outer_query(
907 p_bucket_rec
908 , p_col_name => 'oset.POA_MEASURE4'
909 , p_alias_name => 'POA_MEASURE4'
910 , p_prefix => ''
911 , p_suffix => ''
912 , p_total_flag => 'N')
913 || fnd_global.newline
914 || ',
915
916 oset.POA_MEASURE5 POA_MEASURE5, --Grand Total for Average Age (Days)
917 oset.POA_MEASURE6 POA_MEASURE6 --Grand Total for Unfulfilled Lines'
918 || fnd_global.newline
919 || poa_dbi_util_pkg.get_bucket_outer_query(
920 p_bucket_rec
921 , p_col_name => 'oset.POA_MEASURE7'
922 , p_alias_name => 'POA_MEASURE7'
923 , p_prefix => ''
924 , p_suffix => ''
925 , p_total_flag => 'N')
926 || fnd_global.newline ;
927
928
929 l_sel_clause := l_sel_clause ||
930 poa_dbi_util_pkg.get_bucket_drill_url(
931 p_bucket_rec
932 , 'POA_ATTRIBUTE5'
933 ,
934 '''pFunctionName=POA_DBI_UFR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=-2&POA_BUCKET+REQUISITION_AGING='
935 , ''''
936 , p_add_bucket_num => 'Y') || ',';
937 l_sel_clause := l_sel_clause || '
938 ''pFunctionName=POA_DBI_UFR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=-2'' POA_ATTRIBUTE6 ';
939
940
941 l_sel_clause := l_sel_clause || ' from
942 (select * from (select * from (select
943 (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
944
945 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
946 l_sel_clause := l_sel_clause || ', base_uom';
947 end if;
948
949 l_sel_clause := l_sel_clause || ')) - 1 rnk,' || p_view_by_col;
950 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
951 l_sel_clause := l_sel_clause || ',
952 base_uom,
953 POA_MEASURE1';
954 end if;
955
956 l_sel_clause := l_sel_clause || ',POA_MEASURE2,POA_MEASURE3'
957
958 || fnd_global.newline
959 || poa_dbi_util_pkg.get_bucket_outer_query(
960 p_bucket_rec
961 , p_col_name => 'POA_MEASURE4'
962 , p_alias_name => 'POA_MEASURE4'
963 , p_prefix => ''
964 , p_suffix => ''
965 , p_total_flag => 'N')
966 || fnd_global.newline ||
967 ' ,POA_MEASURE5,
968 POA_MEASURE6'
969 || poa_dbi_util_pkg.get_bucket_outer_query(
970 p_bucket_rec
971 , p_col_name => 'POA_MEASURE7'
972 , p_alias_name => 'POA_MEASURE7'
973 , p_prefix => ''
974 , p_suffix => ''
975 , p_total_flag => 'N')
976 || fnd_global.newline || '
977 from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
978
979 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
980 l_sel_clause := l_sel_clause || ' base_uom,
981 decode(base_uom,null,to_number(null),nvl(c_unfulf_qty,0)) POA_MEASURE1, ';
982
983 end if;
984 l_sel_clause := l_sel_clause
985 || ' nvl(c_num_days_unfulf,0)/decode(c_unfulf_cnt,0,null,c_unfulf_cnt) POA_MEASURE2,
986 nvl(c_unfulf_cnt,0) POA_MEASURE3';
987
988 l_sel_clause := l_sel_clause || fnd_global.newline
989 || poa_dbi_util_pkg.get_bucket_outer_query(
990 p_bucket_rec
991 , p_col_name => 'c_unfulf_cnt_age'
992 , p_alias_name => 'POA_MEASURE4'
993 , p_prefix => 'nvl('
994 , p_suffix => ',0)'
995 , p_total_flag => 'N')
996 || fnd_global.newline || ',
997 nvl(c_num_days_unfulf_total,0)/decode(c_unfulf_cnt_total,0,null,c_unfulf_cnt_total) POA_MEASURE5,
998
999 nvl(c_unfulf_cnt_total,0) POA_MEASURE6'
1000
1001 || poa_dbi_util_pkg.get_bucket_outer_query(
1002 p_bucket_rec
1003 , p_col_name => 'c_unfulf_cnt_age'
1004 , p_alias_name => 'POA_MEASURE7'
1005 , p_prefix => 'nvl('
1006 , p_suffix => ',0)'
1007 , p_total_flag => 'Y')
1008 || fnd_global.newline;
1009
1010 return l_sel_clause;
1011 END get_age_sel_clause;
1012
1013 FUNCTION get_age_filter_where(p_view_by in VARCHAR2) return VARCHAR2
1014 IS
1015 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
1016 BEGIN
1017 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
1018 l_col_tbl.extend;
1019 l_col_tbl(1) := 'POA_MEASURE2';
1020 l_col_tbl.extend;
1021 l_col_tbl(2) := 'POA_MEASURE3';
1022
1023 if(p_view_by = 'ITEM+POA_ITEMS') then
1024 l_col_tbl.extend;
1025 l_col_tbl(3) := 'POA_MEASURE1';
1026 end if;
1027
1028 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
1029
1030 END;
1031
1032
1033 PROCEDURE dtl_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
1034 x_custom_sql OUT NOCOPY VARCHAR2,
1035 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1036 IS
1037 l_query varchar2(10000);
1038 l_option number;
1039 l_cur_suffix varchar2(2);
1040 l_where_clause varchar2(2000);
1041 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1042 l_in_join_tables VARCHAR2(240) ;
1043 l_context number;
1044 l_bucket varchar2(400);
1045 l_context_where VARCHAR2(240);
1046 l_aging_where VARCHAR2(440) ;
1047 l_rownum_where varchar2(300);
1048 l_vo_max_fetch_size varchar2(100);
1049 BEGIN
1050 l_aging_where := ' ';
1051 poa_dbi_sutil_pkg.drill_process_parameters(p_param, l_cur_suffix,
1052 l_where_clause, l_in_join_tbl, 'PO', '7.1', 'VPP','REQS');
1053
1054 IF(l_in_join_tbl is not null) then
1055 FOR i in 1 .. l_in_join_tbl.COUNT
1056 LOOP
1057 l_in_join_tables := l_in_join_tables || ', ' || l_in_join_tbl(i).table_name || ' ' || l_in_join_tbl(i).table_alias;
1058 END LOOP;
1059 END IF;
1060
1061 FOR i IN 1..p_param.COUNT
1062 LOOP
1063 IF (p_param(i).parameter_name = 'POA_ATTRIBUTE10')
1064 THEN l_context := p_param(i).parameter_id;
1065 END IF;
1066 IF (p_param(i).parameter_name = 'POA_BUCKET+REQUISITION_AGING')
1067 THEN l_bucket := p_param(i).parameter_id;
1068 END IF;
1069 END LOOP;
1070
1071 IF(l_context = 1) THEN -- Past Expected Date
1072 l_context_where := ' and fact.expected_date <= to_date(&REQ_FACT_UPDATE_DATE,''DD/MM/YYYY HH24:MI:SS'') ';
1073 poa_dbi_sutil_pkg.bind_reqfact_date(x_custom_output);
1074 ELSIF(l_context=2) THEN -- Pending Processing
1075 l_context_where := ' and fact.po_approved_date is null';
1076 ELSIF(l_context = 3) THEN -- Processed Pending Fulfill
1077 l_context_where := ' and fact.po_approved_date is not null';
1078 END IF;
1079
1080 if(l_bucket is not null) then
1081 l_aging_where := 'and (&RANGE_LOW is null or '
1082 || '(to_date(&REQ_FACT_UPDATE_DATE,''DD/MM/YYYY HH24:MI:SS'')-fact.req_approved_date)'
1083 || ' >= &RANGE_LOW)'
1084 || fnd_global.newline
1085 ||' and (&RANGE_HIGH is null or '
1086 ||'(to_date(&REQ_FACT_UPDATE_DATE,''DD/MM/YYYY HH24:MI:SS'')-fact.req_approved_date)'
1087 || ' < &RANGE_HIGH)';
1088
1089 poa_dbi_util_pkg.bind_low_high(p_param
1090 , 'POA_DBI_UFR_BUCKET'
1091 , 'POA_BUCKET+REQUISITION_AGING'
1092 , '&RANGE_LOW'
1093 , '&RANGE_HIGH'
1094 , x_custom_output);
1095 else
1096 l_aging_where := '';
1097 end if;
1098
1099 poa_dbi_sutil_pkg.bind_reqfact_date(x_custom_output);
1100
1101 /* Determine the l_rownum_where. If VO_MAX_FETCH_SIZE is null then dont filter any rows */
1102 select fnd_profile.value('VO_MAX_FETCH_SIZE')
1103 into l_vo_max_fetch_size
1104 from dual;
1105
1106 if (l_vo_max_fetch_size is not null) then
1107 l_rownum_where := ' where rownum < '||l_vo_max_fetch_size||' + 1 ';
1108 else
1109 l_rownum_where := ' ';
1110 end if;
1111
1112 x_custom_sql :=
1113 'select prh.segment1 POA_MEASURE1, -- Requisition Number
1114 prl.line_num POA_MEASURE2, -- Requisition Line Num
1115 rorg.name POA_ATTRIBUTE1, -- Req OU
1116 substrb(perf.first_name,1,1) || ''. ''|| perf.last_name POA_ATTRIBUTE2, -- Requester
1117 i.POA_ATTRIBUTE3 POA_ATTRIBUTE3, -- Req Approved Date
1118 i.POA_ATTRIBUTE8 POA_ATTRIBUTE8, -- Processed Date
1119 i.POA_ATTRIBUTE4 POA_ATTRIBUTE4, -- Expected Date
1120 item.value POA_ATTRIBUTE5, -- Item
1121 supplier.value POA_ATTRIBUTE6, -- Supplier
1122 i.POA_MEASURE3 POA_MEASURE3, -- amount
1123 decode(por.po_release_id,null,
1124 poh.segment1,
1125 poh.segment1||''-''||por.release_num) POA_MEASURE4,-- PO Number
1126 porg.name POA_ATTRIBUTE7, -- PO Org
1127 i.POA_MEASURE5 POA_MEASURE5, -- PO Revisions
1128 i.POA_MEASURE6 POA_MEASURE6, -- Amount total
1129 i.POA_MEASURE7 POA_MEASURE7, -- Revisions Total
1130 i.req_header_id POA_ATTRIBUTE13, -- Req Header Id
1131 decode(poh.segment1,null,null,
1132 decode(i.POA_ATTRIBUTE8,null,
1133 decode(pll.po_release_id,null,
1134 ''pFunctionName=POA_DBI_PDF_DRILL&DocumentId='' || poh.po_header_id || ''&RevisionNum=''
1135 || poh.revision_num || ''&LanguageCode='' || userenv(''LANG'')||''&DocumentType=PO&DocumentSubtype=STANDARD&OrgId='' || poh.org_id
1136 || ''&UserSecurity=Y&StoreFlag=N&ViewOrCommunicate=View&CallFromForm=N'',
1137 ''pFunctionName=POA_DBI_PDF_DRILL&DocumentId='' || por.po_release_id || ''&RevisionNum=''
1138 || por.revision_num || ''&LanguageCode='' || userenv(''LANG'')||''&DocumentType=RELEASE&DocumentSubtype=BLANKET&OrgId='' || por.org_id
1139 || ''&UserSecurity=Y&StoreFlag=N&ViewOrCommunicate=View&CallFromForm=N'')
1140 ,''pFunctionName=POA_DBI_ISP_DRILL&PoHeaderId=''||poh.po_header_id ||''&PoReleaseId=''||pll.po_release_id
1141 ||''&addBreadCrumb=Y&retainAM=Y'')) POA_ATTRIBUTE14,
1142 decode(i.POA_ATTRIBUTE8,null,null,''pFunctionName=POA_DBI_PCH_DRILL&CorePO=Y&CompareTo=ALL&addBreadCrumb=Y&retainAM=Y&PoHeaderId=''
1143 || poh.po_header_id
1144 ||''&PoReleaseId=''||pll.po_release_id
1145 || ''&PoNum='' || decode(por.po_release_id, null, poh.segment1, poh.segment1 || ''-'' || por.release_num)
1146 || ''&RevisionNum=''||i.POA_MEASURE5) POA_ATTRIBUTE15
1147 from
1148 (select * from (select * from
1149 (select (rank() over
1150 (&ORDER_BY_CLAUSE nulls last, req_header_id ,
1151 req_line_id))-1 rnk,
1152 req_header_id,
1153 req_line_id,
1154 req_creation_ou_id,
1155 requester_id,
1156 POA_ATTRIBUTE3 POA_ATTRIBUTE3,
1157 POA_ATTRIBUTE4 POA_ATTRIBUTE4,
1158 POA_ATTRIBUTE8 POA_ATTRIBUTE8,
1159 po_item_id,
1160 supplier_id,
1161 nvl(POA_MEASURE3,0) POA_MEASURE3,
1162 nvl(POA_MEASURE5,0) POA_MEASURE5,
1163 nvl(POA_MEASURE6,0) POA_MEASURE6,
1164 nvl(POA_MEASURE7,0) POA_MEASURE7,
1165 po_line_location_id,
1166 po_creation_ou_id
1167 from(
1168 select
1169 fact.req_header_id ,
1170 fact.req_line_id,
1171 fact.req_creation_ou_id,
1172 fact.requester_id,
1173 fact.req_approved_date POA_ATTRIBUTE3,
1174 fact.expected_date POA_ATTRIBUTE4,
1175 fact.po_approved_date POA_ATTRIBUTE8,
1176 fact.po_item_id,
1177 fact.supplier_id,
1178 fact.line_amount_'
1179 || l_cur_suffix ||
1180 ' POA_MEASURE3,
1181 po_revisions POA_MEASURE5,
1182 sum(fact.line_amount_'
1183 || l_cur_suffix || ') over() POA_MEASURE6,
1184 sum(po_revisions)over() POA_MEASURE7,
1185 fact.po_line_location_id,
1186 fact.po_creation_ou_id
1187 from
1188 poa_dbi_req_f fact ' || l_in_join_tables || '
1189 where
1190 fact.req_approved_date is not null
1191 and fact.req_fulfilled_date is null' || '
1192 and fact.include_in_ufr=''Y'' ' || l_where_clause || fnd_global.newline ||
1193 l_context_where || fnd_global.newline ||
1194 l_aging_where ||
1195 ')) i2 where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1) ) i3 '||l_rownum_where||' ) i,
1196 po_requisition_headers_all prh,
1197 po_requisition_lines_all prl,
1198 po_headers_all poh,
1199 po_line_locations_all pll,
1200 poa_items_v item,
1201 poa_suppliers_v supplier,
1202 per_all_people_f perf,
1203 hr_all_organization_units_vl rorg,
1204 hr_all_organization_units_vl porg,
1205 po_releases_all por
1206 where
1207 i.req_header_id=prh.requisition_header_id
1208 and i.req_line_id=prl.requisition_line_id
1209 and prh.requisition_header_id=prl.requisition_header_id
1210 and i.po_item_id=item.id
1211 and i.req_creation_ou_id=rorg.organization_id
1212 and i.requester_id=perf.person_id
1213 and sysdate between perf.effective_start_date and perf.effective_end_date
1214 and i.supplier_id=supplier.id(+)
1215 and i.po_line_location_id=pll.line_location_id(+)
1216 and pll.po_header_id=poh.po_header_id(+)
1217 and poh.org_id=porg.organization_id(+)
1218 and pll.po_header_id=por.po_header_id(+)
1219 and pll.po_release_id=por.po_release_id(+)
1220 ORDER BY rnk';
1221
1222 poa_dbi_util_pkg.get_custom_status_binds(x_custom_output);
1223
1224 end;
1225
1226
1227 end poa_dbi_ufr_pkg;
1228