[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_UPR_PKG
Source
1 PACKAGE BODY poa_dbi_upr_pkg
2 /* $Header: poadbiuprb.pls 120.6 2006/07/29 08:29:18 sriswami noship $ */
3 AS
4 --
5 FUNCTION get_status_sel_clause(p_view_by_dim in VARCHAR2
6 ,p_view_by_col in VARCHAR2) return VARCHAR2;
7 FUNCTION get_amt_sel_clause(p_view_by_dim in VARCHAR2
8 ,p_view_by_col in VARCHAR2
9 , p_bucket_rec in BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE)
10 return VARCHAR2;
11
12 FUNCTION get_sum_sel_clause(p_view_by_dim in VARCHAR2
13 ,p_view_by_col in VARCHAR2) return VARCHAR2;
14 FUNCTION get_age_sel_clause( p_view_by_dim in VARCHAR2
15 ,p_view_by_col in VARCHAR2
16 ,p_bucket_rec in BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE)
17 return VARCHAR2;
18 FUNCTION get_status_filter_where(p_view_by IN VARCHAR2) return VARCHAR2;
19 FUNCTION get_amt_filter_where(p_view_by IN VARCHAR2) return VARCHAR2;
20 FUNCTION get_age_filter_where(p_view_by IN VARCHAR2) return VARCHAR2;
21 FUNCTION get_sum_rpt_filter_where(p_view_by IN VARCHAR2) return VARCHAR2;
22 -- FUNCTION get_dtl_filter_where(p_view_by IN VARCHAR2) return VARCHAR2;
23
24 PROCEDURE status_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
25 x_custom_sql OUT NOCOPY VARCHAR2,
26 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
27 IS
28
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
48 BEGIN
49
50 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
51 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
52 l_comparison_type := 'Y';
53 poa_dbi_sutil_pkg.process_parameters(p_param
54 ,l_view_by
55 ,l_view_by_col
56 ,l_view_by_value
57 ,l_comparison_type
58 ,l_xtd
59 ,l_as_of_date
60 ,l_prev_as_of_date
61 ,l_cur_suffix
62 ,l_nested_pattern
63 ,l_where_clause
64 ,l_mv
65 ,l_join_tbl
66 ,l_in_join_tbl
67 ,x_custom_output
68 ,p_trend => 'N'
69 ,p_func_area => 'PO'
70 ,p_version => '7.1'
71 ,p_role => 'VPP'
72 ,p_mv_set => 'REQS');
73
74 poa_dbi_util_pkg.add_column(l_col_tbl
75 ,'num_days_unprocessed'
76 , 'num_days_unproc'
77 , p_grand_total => 'Y'
78 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
79 , p_to_date_type => 'NA');
80
81 poa_dbi_util_pkg.add_column(l_col_tbl
82 ,'unprocessed_cnt'
83 , 'unproc_cnt'
84 , p_grand_total => 'Y'
85 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
86 , p_to_date_type => 'NA');
87
88
89 poa_dbi_util_pkg.add_column(l_col_tbl
90 ,'unprocessed_ped_cnt'
91 , 'unproc_ped_cnt'
92 , p_grand_total => 'Y'
93 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
94 , p_to_date_type => 'NA');
95
96 poa_dbi_util_pkg.add_column(l_col_tbl
97 ,'unprocessed_amt_' || l_cur_suffix
98 , 'unproc_amt'
99 , p_grand_total => 'Y'
100 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
101 , p_to_date_type => 'NA');
102
103 if(l_view_by = 'ITEM+POA_ITEMS') then
104 poa_dbi_util_pkg.add_column(l_col_tbl
105 , 'unprocessed_qty'
106 , 'unproc_qty'
107 , p_grand_total => 'N'
108 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
109 , p_to_date_type => 'NA');
110
111 end if;
112
113
114 l_query := get_status_sel_clause(l_view_by, l_view_by_col) || ' from '||
115 poa_dbi_template_pkg.status_sql(
116 l_mv,
117 l_where_clause,
118 l_join_tbl,
119 p_use_windowing => 'P',
120 p_col_name => l_col_tbl,
121 p_use_grpid => 'N',
122 p_filter_where => get_status_filter_where(l_view_by),
123 p_in_join_tables => l_in_join_tbl);
124
125 x_custom_sql := l_query;
126
127 EXCEPTION
128 WHEN OTHERS THEN
129 ERR_MSG := SUBSTR(SQLERRM,1,400);
130 end;
131
132 FUNCTION get_status_sel_clause(p_view_by_dim in VARCHAR2
133 ,p_view_by_col in VARCHAR2) return VARCHAR2 IS
134 l_sel_clause varchar2(4000);
135
136 BEGIN
137
138 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
139 ,'PO'
140 ,'7.1');
141
142 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
143 l_sel_clause := l_sel_clause
144 || fnd_global.newline
145 ||
146 ' v.description POA_ATTRIBUTE1, --Description
147 v2.description POA_ATTRIBUTE2, --UOM
148 oset.POA_MEASURE8 POA_MEASURE8, --Quantity'
149 || fnd_global.newline;
150 else
151 l_sel_clause := l_sel_clause
152 || fnd_global.newline
153 ||
154 ' null POA_ATTRIBUTE1, --Description
155 null POA_ATTRIBUTE2, --UOM
156 null POA_MEASURE8, --Quantity'
157 || fnd_global.newline;
158 end if;
159
160
161 l_sel_clause := l_sel_clause ||
162 ' oset.POA_MEASURE1 POA_MEASURE1, --Unprocessed Lines
163 oset.POA_PERCENT1 POA_PERCENT1, --Percent of Total
164 oset.POA_MEASURE1 POA_MEASURE7, --Unprocessed Lines for graph 2
165 oset.POA_MEASURE2 POA_MEASURE2, --Lines Past Expected Date
166 oset.POA_MEASURE3 POA_MEASURE3, --Unprocessed Amount
167 oset.POA_MEASURE10 POA_MEASURE10, --Average Age (Days)
168 oset.POA_MEASURE4 POA_MEASURE4, --Grand Total Unfulfilled Lines
169 oset.POA_PERCENT3 POA_PERCENT3, --Grand Total Percent of Total
170 oset.POA_MEASURE5 POA_MEASURE5, --Grand Total Lines Past Exp Date
171 oset.POA_MEASURE6 POA_MEASURE6, --Grand Total Unprocessed Amount
172 oset.POA_MEASURE11 POA_MEASURE11, --Grand Total Average Age Days
173 oset.POA_MEASURE2 POA_MEASURE9, --Lines past exp for graph';
174
175
176 l_sel_clause := l_sel_clause || '
177 ''pFunctionName=POA_DBI_UPR_SUM_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_PO_CAT&pParamIds=Y'' POA_ATTRIBUTE7,
178 ''pFunctionName=POA_DBI_UPR_SUM_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_PO_CAT&pParamIds=Y'' POA_ATTRIBUTE8,
179 ''pFunctionName=POA_DBI_UPR_PED_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=6'' POA_ATTRIBUTE9,
180 ''pFunctionName=POA_DBI_UPR_PED_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=6'' POA_ATTRIBUTE10,
181 ''pFunctionName=POA_DBI_UPR_AMT_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM_PO_CAT'' POA_ATTRIBUTE11,
182 ''pFunctionName=POA_DBI_UPR_AGE_RPT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&VIEW_BY=ITEM+ENI_ITEM_PO_CAT'' POA_ATTRIBUTE12 ';
183
184
185 l_sel_clause := l_sel_clause ||
186 ' from (select * from (select * from
187 (select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
188
189 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
190 l_sel_clause := l_sel_clause || ',base_uom';
191 end if;
192
193 l_sel_clause := l_sel_clause || ')) - 1 rnk,' || p_view_by_col;
194
195 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
196 l_sel_clause := l_sel_clause ||
197 ' , base_uom,
198 POA_MEASURE8';
199 end if;
200
201 l_sel_clause := l_sel_clause || ',POA_MEASURE1,POA_PERCENT1,
202 POA_MEASURE2,POA_MEASURE3,
203 POA_MEASURE4, POA_PERCENT3,
204 POA_MEASURE5,POA_MEASURE6,
205 POA_MEASURE10, POA_MEASURE11
206 from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
207
208 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
209 l_sel_clause := l_sel_clause || ' base_uom,
210 decode(base_uom,null,to_number(null),nvl(c_unproc_qty,0)) POA_MEASURE8,';
211 end if;
212
213 l_sel_clause := l_sel_clause || ' nvl(c_unproc_cnt,0) POA_MEASURE1,
214 ' || poa_dbi_util_pkg.rate_clause('c_unproc_cnt','c_unproc_cnt_total', 'P') || ' POA_PERCENT1,
215 nvl(c_unproc_ped_cnt,0) POA_MEASURE2,
216 nvl(c_unproc_amt,0) POA_MEASURE3,
217 nvl(c_unproc_cnt_total,0) POA_MEASURE4,
218 decode(c_unproc_cnt_total,0,null,100) POA_PERCENT3,
219 nvl(c_unproc_ped_cnt_total,0) POA_MEASURE5,
220 nvl(c_unproc_amt_total,0) POA_MEASURE6,
221 ' || poa_dbi_util_pkg.rate_clause('c_num_days_unproc','c_unproc_cnt', 'NP') || ' POA_MEASURE10,
222 ' || poa_dbi_util_pkg.rate_clause('c_num_days_unproc_total','c_unproc_cnt_total', 'NP') || ' POA_MEASURE11
223 ';
224
225 return l_sel_clause;
226 END;
227
228
229 FUNCTION get_status_filter_where(p_view_by in VARCHAR2) return VARCHAR2
230 IS
231 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
232 BEGIN
233 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
234 l_col_tbl.extend;
235 l_col_tbl(1) := 'POA_MEASURE1';
236 l_col_tbl.extend;
237 l_col_tbl(2) := 'POA_PERCENT1';
238 l_col_tbl.extend;
239 l_col_tbl(3) := 'POA_MEASURE2';
240 l_col_tbl.extend;
241 l_col_tbl(4) := 'POA_MEASURE3';
242 l_col_tbl.extend;
243 l_col_tbl(5) := 'POA_MEASURE10';
244
245 if(p_view_by = 'ITEM+POA_ITEMS') then
246 l_col_tbl.extend;
247 l_col_tbl(6) := 'POA_MEASURE8';
248 end if;
249
250 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
251
252 END;
253
254
255 PROCEDURE amt_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
256 x_custom_sql OUT NOCOPY VARCHAR2,
257 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
258 IS
259
260 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
261 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
262 l_query varchar2(10000);
263 l_view_by varchar2(120);
264 l_view_by_col varchar2(120);
265 l_as_of_date date;
266 l_prev_as_of_date date;
267 l_xtd varchar2(10);
268 l_comparison_type varchar2(1);
269 l_nested_pattern number;
270 l_cur_suffix varchar2(2);
271 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
272 l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
273 l_where_clause VARCHAR2(2000);
274 l_mv VARCHAR2(30);
275 l_view_by_value varchar2(30);
276 l_bucket_rec BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE;
277 ERR_MSG VARCHAR2(100);
278 ERR_CDE NUMBER;
279
280 BEGIN
281
282 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
283 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
284 l_comparison_type :='Y';
285 poa_dbi_sutil_pkg.process_parameters(p_param
286 ,l_view_by
287 ,l_view_by_col
288 ,l_view_by_value
289 ,l_comparison_type
290 ,l_xtd
291 ,l_as_of_date
292 ,l_prev_as_of_date
293 ,l_cur_suffix
294 ,l_nested_pattern
295 ,l_where_clause
296 ,l_mv
297 ,l_join_tbl
298 ,l_in_join_tbl
299 ,x_custom_output
300 ,p_trend => 'N'
301 ,p_func_area => 'PO'
302 ,p_version => '7.1'
303 ,p_role => 'VPP'
304 ,p_mv_set => 'REQS');
305
306 poa_dbi_util_pkg.add_bucket_columns(
307 p_short_name => 'POA_DBI_UPR_BUCKET'
308 ,p_col_tbl => l_col_tbl
309 ,p_col_name => 'unprocessed_amt_' || l_cur_suffix || '_age'
310 ,p_alias_name => 'unproc_amt_age'
311 ,x_bucket_rec => l_bucket_rec
312 ,p_grand_total => 'Y'
313 ,p_prior_code => poa_dbi_util_pkg.NO_PRIORS
314 ,p_to_date_type => 'NA');
315
316 poa_dbi_util_pkg.add_column(l_col_tbl
317 ,'unprocessed_amt_' || l_cur_suffix
318 , 'unproc_amt'
319 , p_grand_total => 'Y'
320 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
321 , p_to_date_type => 'NA');
322
323
324 poa_dbi_util_pkg.add_column(l_col_tbl
325 ,'pen_src_amt_' || l_cur_suffix
326 , 'pen_src_amt'
327 , p_grand_total => 'Y'
328 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
329 , p_to_date_type => 'NA');
330
331
332 poa_dbi_util_pkg.add_column(l_col_tbl
333 ,'pen_buyer_wk_amt_' || l_cur_suffix
334 , 'pen_buyer_wk_amt'
335 , p_grand_total => 'Y'
336 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
337 , p_to_date_type => 'NA');
338
339
340 poa_dbi_util_pkg.add_column(l_col_tbl
341 ,'pen_po_submit_amt_' || l_cur_suffix
342 , 'pen_po_submit_amt'
343 , p_grand_total => 'Y'
344 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
345 , p_to_date_type => 'NA');
346
347 poa_dbi_util_pkg.add_column(l_col_tbl
348 ,'pen_po_appr_amt_' || l_cur_suffix
349 , 'pen_po_appr_amt'
350 , p_grand_total => 'Y'
351 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
352 , p_to_date_type => 'NA');
353
354 if(l_view_by = 'ITEM+POA_ITEMS') then
355 poa_dbi_util_pkg.add_column(l_col_tbl
356 , 'unprocessed_qty'
357 , 'unproc_qty'
358 , p_grand_total => 'N'
359 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
360 , p_to_date_type => 'NA');
361
362 end if;
363
364 l_query := get_amt_sel_clause(l_view_by
365 ,l_view_by_col
366 ,l_bucket_rec) || ' from ' ||
367 poa_dbi_template_pkg.status_sql(
368 l_mv,
369 l_where_clause,
370 l_join_tbl,
371 p_use_windowing => 'P',
372 p_col_name => l_col_tbl,
373 p_use_grpid => 'N',
374 p_filter_where => get_amt_filter_where(l_view_by),
375 p_in_join_tables => l_in_join_tbl);
376
377
378 x_custom_sql := l_query;
379
380 EXCEPTION
381 WHEN OTHERS THEN
382 ERR_MSG := SUBSTR(SQLERRM,1,400);
383 end;
384
385 FUNCTION get_amt_sel_clause(p_view_by_dim in VARCHAR2
386 ,p_view_by_col in VARCHAR2
387 , p_bucket_rec in BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE)
388 return VARCHAR2
389 IS
390 l_sel_clause varchar2(8000);
391
392 BEGIN
393 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
394 ,'PO'
395 ,'7.1');
396
397
398 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
399 l_sel_clause := l_sel_clause
400 || fnd_global.newline
401 ||
402 ' v.description POA_ATTRIBUTE1, --Description
403 v2.description POA_ATTRIBUTE2, --UOM
404 oset.POA_MEASURE1 POA_MEASURE1, --Quantity'
405 || fnd_global.newline;
406 else
407 l_sel_clause := l_sel_clause
408 || fnd_global.newline
409 ||
410 ' null POA_ATTRIBUTE1, --Description
411 null POA_ATTRIBUTE2, --UOM
412 null POA_MEASURE1, --Quantity'
413 || fnd_global.newline;
414 end if;
415
416
417 l_sel_clause := l_sel_clause ||
418 ' oset.POA_MEASURE2 POA_MEASURE2, --Unprocessed Lines Total
419 oset.POA_MEASURE3 POA_MEASURE3, -- Lines Pending Sourcing
420 oset.POA_MEASURE4 POA_MEASURE4, -- Lines Pending Buyers Workbench
421 oset.POA_MEASURE5 POA_MEASURE5, --Lines Pending PO submit
422 oset.POA_MEASURE6 POA_MEASURE6 --Lines Pending PO Approval '
423 || fnd_global.newline
424 || poa_dbi_util_pkg.get_bucket_outer_query(
425 p_bucket_rec
426 , p_col_name => 'oset.POA_MEASURE7'
427 , p_alias_name => 'POA_MEASURE7'
428 , p_prefix => ''
429 , p_suffix => ''
430 , p_total_flag => 'N')
431 || fnd_global.newline
432 || ' ,oset.POA_MEASURE8 POA_MEASURE8, -- Grand Total for Total
433 oset.POA_MEASURE9 POA_MEASURE9, -- Grand Total for Pending Src
434 oset.POA_MEASURE10 POA_MEASURE10, -- Grand Total for Buyers Wkbnch
435 oset.POA_MEASURE11 POA_MEASURE11, -- Grand Total for PO Submit
436 oset.POA_MEASURE12 POA_MEASURE12 -- Grand Total for PO Approval '
437 || fnd_global.newline
438 || poa_dbi_util_pkg.get_bucket_outer_query(
439 p_bucket_rec
440 , p_col_name => 'oset.POA_MEASURE13'
441 , p_alias_name => 'POA_MEASURE13'
442 , p_prefix => ''
443 , p_suffix => ''
444 , p_total_flag => 'N')
445 || fnd_global.newline
446 || ',oset.POA_MEASURE3 POA_MEASURE14,
447 oset.POA_MEASURE4 POA_MEASURE15,
448 oset.POA_MEASURE5 POA_MEASURE16,
449 oset.POA_MEASURE6 POA_MEASURE17' ;
450
451
452 l_sel_clause := l_sel_clause ||
453 poa_dbi_util_pkg.get_bucket_drill_url(
454 p_bucket_rec
455 , 'POA_ATTRIBUTE5'
456 ,
457 '''pFunctionName=POA_DBI_UPR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=1&POA_BUCKET+REQUISITION_AGING='
458 , ''''
459 , p_add_bucket_num => 'Y') || ',';
460
461
462
463 l_sel_clause := l_sel_clause || '
464 ''pFunctionName=POA_DBI_UPR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=1'' POA_ATTRIBUTE7,
465 ''pFunctionName=POA_DBI_UPR_SRC_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=2'' POA_ATTRIBUTE8,
466 ''pFunctionName=POA_DBI_UPR_SRC_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=2'' POA_ATTRIBUTE9,
467 ''pFunctionName=POA_DBI_UPR_BW_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=3'' POA_ATTRIBUTE10,
468 ''pFunctionName=POA_DBI_UPR_BW_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=3'' POA_ATTRIBUTE11,
469 ''pFunctionName=POA_DBI_UPR_BSA_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=4'' POA_ATTRIBUTE12,
470 ''pFunctionName=POA_DBI_UPR_BSA_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=4'' POA_ATTRIBUTE13,
471 ''pFunctionName=POA_DBI_UPR_PA_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=5'' POA_ATTRIBUTE14,
472 ''pFunctionName=POA_DBI_UPR_PA_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=5'' POA_ATTRIBUTE15 ';
473
474
475
476 l_sel_clause := l_sel_clause || 'from
477 (select * from (select * from
478 (select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
479
480
481 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
482 l_sel_clause := l_sel_clause || ',base_uom';
483 end if;
484
485 l_sel_clause := l_sel_clause || ')) - 1 rnk,' || p_view_by_col;
486
487 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
488 l_sel_clause := l_sel_clause ||
489 ' , base_uom,
490 POA_MEASURE1';
491 end if;
492
493 l_sel_clause := l_sel_clause || ',
494 POA_MEASURE2,POA_MEASURE3,
495 POA_MEASURE4, POA_MEASURE5,
496 POA_MEASURE6'
497 || fnd_global.newline
498 || poa_dbi_util_pkg.get_bucket_outer_query(
499 p_bucket_rec
500 , p_col_name => 'POA_MEASURE7'
501 , p_alias_name => 'POA_MEASURE7'
502 , p_prefix => ''
503 , p_suffix => ''
504 , p_total_flag => 'N')
505 || fnd_global.newline ||
506 ' , POA_MEASURE8, POA_MEASURE9,
507 POA_MEASURE10, POA_MEASURE11,
508 POA_MEASURE12 '
509 || poa_dbi_util_pkg.get_bucket_outer_query(
510 p_bucket_rec
511 , p_col_name => 'POA_MEASURE13'
512 , p_alias_name => 'POA_MEASURE13'
513 , p_prefix => ''
514 , p_suffix => ''
515 , p_total_flag => 'N')
516 || fnd_global.newline ||
517 ' from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
518
519 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
520 l_sel_clause := l_sel_clause || ' base_uom,
521 decode(base_uom,null,null,nvl(c_unproc_qty,0)) POA_MEASURE1,';
522 end if;
523 l_sel_clause := l_sel_clause || ' nvl(c_unproc_amt,0) POA_MEASURE2,
524 nvl(c_pen_src_amt,0) POA_MEASURE3,
525 nvl(c_pen_buyer_wk_amt,0) POA_MEASURE4,
526 nvl(c_pen_po_submit_amt,0) POA_MEASURE5,
527 nvl(c_pen_po_appr_amt,0) POA_MEASURE6
528 ';
529
530 l_sel_clause := l_sel_clause || fnd_global.newline
531 || poa_dbi_util_pkg.get_bucket_outer_query(
532 p_bucket_rec
533 , p_col_name => 'c_unproc_amt_age'
534 , p_alias_name => 'POA_MEASURE7'
535 , p_prefix => 'nvl('
536 , p_suffix => ',0)'
537 , p_total_flag => 'N')
538 || fnd_global.newline || ',
539 nvl(c_unproc_amt_total,0) POA_MEASURE8,
540 nvl(c_pen_src_amt_total,0) POA_MEASURE9,
541 nvl(c_pen_buyer_wk_amt_total,0) POA_MEASURE10,
542 nvl(c_pen_po_submit_amt_total,0) POA_MEASURE11,
543 nvl(c_pen_po_appr_amt_total,0) POA_MEASURE12'
544 || poa_dbi_util_pkg.get_bucket_outer_query(
545 p_bucket_rec
546 , p_col_name => 'c_unproc_amt_age'
547 , p_alias_name => 'POA_MEASURE13'
548 , p_prefix => 'nvl('
549 , p_suffix => ',0)'
550 , p_total_flag => 'Y')
551 || fnd_global.newline ;
552
553 return l_sel_clause;
554 END;
555
556
557 FUNCTION get_amt_filter_where(p_view_by in VARCHAR2) return VARCHAR2
558 IS
559 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
560 BEGIN
561 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
562 l_col_tbl.extend;
563 l_col_tbl(1) := 'POA_MEASURE2';
564 l_col_tbl.extend;
565 l_col_tbl(2) := 'POA_MEASURE3';
566 l_col_tbl.extend;
567 l_col_tbl(3) := 'POA_MEASURE4';
568 l_col_tbl.extend;
569 l_col_tbl(4) := 'POA_MEASURE5';
570 l_col_tbl.extend;
571 l_col_tbl(5) := 'POA_MEASURE6';
572
573 if(p_view_by = 'ITEM+POA_ITEMS') then
574 l_col_tbl.extend;
575 l_col_tbl(6) := 'POA_MEASURE1';
576 end if;
577
578 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
579
580 END;
581
582 PROCEDURE age_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
583 x_custom_sql OUT NOCOPY VARCHAR2,
584 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
585 IS
586
587 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
588 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
589 l_query varchar2(10000);
590 l_view_by varchar2(120);
591 l_view_by_col varchar2(120);
592 l_as_of_date date;
593 l_prev_as_of_date date;
594 l_xtd varchar2(10);
595 l_comparison_type varchar2(1);
596 l_nested_pattern number;
597 l_cur_suffix varchar2(2);
598 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
599 l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
600 l_where_clause VARCHAR2(2000);
601 l_mv VARCHAR2(30);
602 l_view_by_value varchar2(30);
603 l_bucket_rec BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE;
604 ERR_MSG VARCHAR2(100);
605 ERR_CDE NUMBER;
606
607 BEGIN
608
609 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
610 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
611 l_comparison_type := 'Y';
612 poa_dbi_sutil_pkg.process_parameters(p_param
613 ,l_view_by
614 ,l_view_by_col
615 ,l_view_by_value
616 ,l_comparison_type
617 ,l_xtd
618 ,l_as_of_date
619 ,l_prev_as_of_date
620 ,l_cur_suffix
621 ,l_nested_pattern
622 ,l_where_clause
623 ,l_mv
624 ,l_join_tbl
625 ,l_in_join_tbl
626 ,x_custom_output
627 ,p_trend => 'N'
628 ,p_func_area => 'PO'
629 ,p_version => '7.1'
630 ,p_role => 'VPP'
631 ,p_mv_set => 'REQS');
632
633 poa_dbi_util_pkg.add_bucket_columns(
634 p_short_name => 'POA_DBI_UPR_BUCKET'
635 ,p_col_tbl => l_col_tbl
636 ,p_col_name => 'unprocessed_cnt_age'
637 ,p_alias_name => 'unproc_cnt_age'
638 ,x_bucket_rec => l_bucket_rec
639 ,p_grand_total => 'Y'
640 ,p_prior_code => poa_dbi_util_pkg.NO_PRIORS
641 ,p_to_date_type => 'NA');
642
643 poa_dbi_util_pkg.add_column(l_col_tbl
644 ,'num_days_unprocessed'
645 , 'num_days_unproc'
646 , p_grand_total => 'Y'
647 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
648 , p_to_date_type => 'NA');
649
650
651 poa_dbi_util_pkg.add_column(l_col_tbl
652 ,'unprocessed_cnt'
653 , 'unproc_cnt'
654 , p_grand_total => 'Y'
655 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
656 , p_to_date_type => 'NA');
657
658
659 if(l_view_by = 'ITEM+POA_ITEMS') then
660 poa_dbi_util_pkg.add_column(l_col_tbl
661 , 'unprocessed_qty'
662 , 'unproc_qty'
663 , p_grand_total => 'N'
664 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
665 , p_to_date_type => 'NA');
666
667 end if;
668 l_query := get_age_sel_clause(l_view_by
669 ,l_view_by_col
670 ,l_bucket_rec) || ' from ' ||
671 poa_dbi_template_pkg.status_sql(
672 l_mv,
673 l_where_clause,
674 l_join_tbl,
675 p_use_windowing => 'P',
676 p_col_name => l_col_tbl,
677 p_use_grpid => 'N',
678 p_filter_where => get_age_filter_where(l_view_by),
679 p_in_join_tables => l_in_join_tbl);
680
681
682 x_custom_sql := l_query;
683
684 EXCEPTION
685 WHEN OTHERS THEN
686 ERR_MSG := SUBSTR(SQLERRM,1,400);
687
688 end;
689
690
691 FUNCTION get_age_sel_clause(p_view_by_dim in VARCHAR2
692 ,p_view_by_col in VARCHAR2
693 , p_bucket_rec in BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE)
694 return VARCHAR2
695 IS
696 l_sel_clause varchar2(4000);
697
698 BEGIN
699
700 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
701 ,'PO'
702 ,'7.1');
703
704 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
705 l_sel_clause := l_sel_clause
706 || fnd_global.newline
707 ||
708 ' v.description POA_ATTRIBUTE1, --Description
709 v2.description POA_ATTRIBUTE2, --UOM
710 oset.POA_MEASURE1 POA_MEASURE1, --Quantity'
711 || fnd_global.newline;
712 else
713 l_sel_clause := l_sel_clause
714 || fnd_global.newline
715 ||
716 ' null POA_ATTRIBUTE1, --Description
717 null POA_ATTRIBUTE2, --UOM
718 null POA_MEASURE1, --Quantity'
719 || fnd_global.newline;
720 end if;
721
722
723 l_sel_clause := l_sel_clause ||
724 ' oset.POA_MEASURE7 POA_MEASURE7, -- Avg Age (days)
725 oset.POA_MEASURE2 POA_MEASURE2 -- Unprocessed Lines'
726 || fnd_global.newline
727 || poa_dbi_util_pkg.get_bucket_outer_query(
728 p_bucket_rec
729 , p_col_name => 'oset.POA_MEASURE3'
730 , p_alias_name => 'POA_MEASURE3'
731 , p_prefix => ''
732 , p_suffix => ''
733 , p_total_flag => 'N')
734 || fnd_global.newline || ',
735 oset.POA_MEASURE8 POA_MEASURE8, -- Grand Total by Avg Days
736 oset.POA_MEASURE4 POA_MEASURE4 -- Grand Total Unprocessed Lns'
737 || fnd_global.newline
738 || poa_dbi_util_pkg.get_bucket_outer_query(
739 p_bucket_rec
740 , p_col_name => 'oset.POA_MEASURE5'
741 , p_alias_name => 'POA_MEASURE5'
742 , p_prefix => ''
743 , p_suffix => ''
744 , p_total_flag => 'N')
745 || fnd_global.newline ;
746
747 l_sel_clause := l_sel_clause ||
748 poa_dbi_util_pkg.get_bucket_drill_url(
749 p_bucket_rec
750 , 'POA_ATTRIBUTE5'
751 ,
752 '''pFunctionName=POA_DBI_UPR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=1&POA_BUCKET+REQUISITION_AGING='
753 , ''''
754 , p_add_bucket_num => 'Y') || ',';
755
756
757 l_sel_clause := l_sel_clause || '
758 ''pFunctionName=POA_DBI_UPR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_BUCKET+REQUISITION_AGING=&POA_ATTRIBUTE10=1'' POA_ATTRIBUTE7 ';
759
760 l_sel_clause := l_sel_clause || ' from
761 (select * from (select * from
762 (select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
763
764 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
765 l_sel_clause := l_sel_clause || ',base_uom';
766 end if;
767 l_sel_clause := l_sel_clause || ')) - 1 rnk,' || p_view_by_col;
768
769 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
770 l_sel_clause := l_sel_clause ||
771 ' , base_uom,
772 POA_MEASURE1';
773 end if;
774 l_sel_clause := l_sel_clause || ',
775 POA_MEASURE7,POA_MEASURE2'
776 || fnd_global.newline
777 || poa_dbi_util_pkg.get_bucket_outer_query(
778 p_bucket_rec
779 , p_col_name => 'POA_MEASURE3'
780 , p_alias_name => 'POA_MEASURE3'
781 , p_prefix => ''
782 , p_suffix => ''
783 , p_total_flag => 'N')
784 || fnd_global.newline ||
785 ' , POA_MEASURE8, POA_MEASURE4'
786 || poa_dbi_util_pkg.get_bucket_outer_query(
787 p_bucket_rec
788 , p_col_name => 'POA_MEASURE5'
789 , p_alias_name => 'POA_MEASURE5'
790 , p_prefix => ''
791 , p_suffix => ''
792 , p_total_flag => 'N')
793 || fnd_global.newline ||
794 ' from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
795
796 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
797 l_sel_clause := l_sel_clause || ' base_uom,
798 decode(base_uom,null,to_number(null),nvl(c_unproc_qty,0)) POA_MEASURE1,';
799 end if;
800 l_sel_clause := l_sel_clause || ' nvl(c_num_days_unproc,0)/decode(c_unproc_cnt,0,null,c_unproc_cnt) POA_MEASURE7,
801 nvl(c_unproc_cnt,0) POA_MEASURE2
802 ';
803
804 l_sel_clause := l_sel_clause || fnd_global.newline
805 || poa_dbi_util_pkg.get_bucket_outer_query(
806 p_bucket_rec
807 , p_col_name => 'c_unproc_cnt_age'
808 , p_alias_name => 'POA_MEASURE3'
809 , p_prefix => 'nvl('
810 , p_suffix => ',0)'
811 , p_total_flag => 'N')
812 || fnd_global.newline || ',
813 nvl(c_num_days_unproc_total,0)/decode(c_unproc_cnt_total,0,null,c_unproc_cnt_total) POA_MEASURE8,
814 nvl(c_unproc_cnt_total,0) POA_MEASURE4'
815 || poa_dbi_util_pkg.get_bucket_outer_query(
816 p_bucket_rec
817 , p_col_name => 'c_unproc_cnt_age'
818 , p_alias_name => 'POA_MEASURE5'
819 , p_prefix => 'nvl('
820 , p_suffix => ',0)'
821 , p_total_flag => 'Y')
822 || fnd_global.newline ;
823
824 return l_sel_clause;
825 END;
826
827
828 FUNCTION get_age_filter_where(p_view_by in VARCHAR2) return VARCHAR2
829 IS
830 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
831 BEGIN
832 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
833 l_col_tbl.extend;
834 l_col_tbl(1) := 'POA_MEASURE7';
835 l_col_tbl.extend;
836 l_col_tbl(2) := 'POA_MEASURE2';
837
838 if(p_view_by = 'ITEM+POA_ITEMS') then
839 l_col_tbl.extend;
840 l_col_tbl(3) := 'POA_MEASURE1';
841 end if;
842
843 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
844
845 END;
846
847 PROCEDURE sum_rpt_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
848 x_custom_sql OUT NOCOPY VARCHAR2,
849 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
850 IS
851
852 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
853 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
854 l_query varchar2(10000);
855 l_view_by varchar2(120);
856 l_view_by_col varchar2(120);
857 l_as_of_date date;
858 l_prev_as_of_date date;
859 l_xtd varchar2(10);
860 l_comparison_type varchar2(1);
861 l_nested_pattern number;
862 l_cur_suffix varchar2(2);
863 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
864 l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
865 l_where_clause VARCHAR2(2000);
866 l_mv VARCHAR2(30);
867 l_view_by_value varchar2(30);
868 ERR_MSG VARCHAR2(100);
869 ERR_CDE NUMBER;
870
871
872 BEGIN
873
874 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
875 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
876 l_comparison_type := 'Y';
877 poa_dbi_sutil_pkg.process_parameters(p_param
878 ,l_view_by
879 ,l_view_by_col
880 ,l_view_by_value
881 ,l_comparison_type
882 ,l_xtd
883 ,l_as_of_date
884 ,l_prev_as_of_date
885 ,l_cur_suffix
886 ,l_nested_pattern
887 ,l_where_clause
888 ,l_mv
889 ,l_join_tbl
890 ,l_in_join_tbl
891 ,x_custom_output
892 ,p_trend => 'N'
893 ,p_func_area => 'PO'
894 ,p_version => '7.1'
895 ,p_role => 'VPP'
896 ,p_mv_set => 'REQS');
897 poa_dbi_util_pkg.add_column(l_col_tbl
898 ,'pen_src_cnt'
899 ,'pen_src_cnt'
900 , p_grand_total => 'Y'
901 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
902 , p_to_date_type => 'NA');
903
904
905 poa_dbi_util_pkg.add_column(l_col_tbl
906 ,'pen_buyer_wk_cnt'
907 ,'pen_buyer_wk_cnt'
908 , p_grand_total => 'Y'
909 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
910 , p_to_date_type => 'NA');
911
912
913 poa_dbi_util_pkg.add_column(l_col_tbl
914 ,'pen_po_submit_cnt'
915 ,'pen_po_submit_cnt'
916 , p_grand_total => 'Y'
917 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
918 , p_to_date_type => 'NA');
919
920
921 poa_dbi_util_pkg.add_column(l_col_tbl
922 ,'pen_po_appr_cnt'
923 ,'pen_po_appr_cnt'
924 , p_grand_total => 'Y'
925 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
926 , p_to_date_type => 'NA');
927
928
929
930 poa_dbi_util_pkg.add_column(l_col_tbl
931 ,'unprocessed_cnt'
932 , 'unproc_cnt'
933 , p_grand_total => 'Y'
934 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
935 , p_to_date_type => 'NA');
936
937
938 poa_dbi_util_pkg.add_column(l_col_tbl
939 ,'unprocessed_ped_cnt'
940 , 'unproc_ped_cnt'
941 , p_grand_total => 'Y'
942 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
943 , p_to_date_type => 'NA');
944
945 poa_dbi_util_pkg.add_column(l_col_tbl
946 ,'unprocessed_emer_cnt'
947 , 'unproc_emer_cnt'
948 , p_grand_total => 'Y'
949 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
950 , p_to_date_type => 'NA');
951
952 poa_dbi_util_pkg.add_column(l_col_tbl
953 ,'unprocessed_urg_cnt'
954 , 'unproc_urg_cnt'
955 , p_grand_total => 'Y'
956 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
957 , p_to_date_type => 'NA');
958
959 if(l_view_by = 'ITEM+POA_ITEMS') then
960 poa_dbi_util_pkg.add_column(l_col_tbl
961 , 'unprocessed_qty'
962 , 'unproc_qty'
963 , p_grand_total => 'N'
964 , p_prior_code => poa_dbi_util_pkg.NO_PRIORS
965 , p_to_date_type => 'NA');
966
967 end if;
968 l_query := get_sum_sel_clause(l_view_by, l_view_by_col) || ' from '||
969 poa_dbi_template_pkg.status_sql(
970 l_mv,
971 l_where_clause,
972 l_join_tbl,
973 p_use_windowing => 'P',
974 p_col_name => l_col_tbl,
975 p_use_grpid => 'N',
976 p_filter_where => get_sum_rpt_filter_where(l_view_by),
977 p_in_join_tables => l_in_join_tbl);
978
979 x_custom_sql := l_query;
980
981 EXCEPTION
982 WHEN OTHERS THEN
983 ERR_MSG := SUBSTR(SQLERRM,1,400);
984
985 end;
986
987 FUNCTION get_sum_sel_clause(p_view_by_dim in VARCHAR2
988 ,p_view_by_col in VARCHAR2) return VARCHAR2 IS
989 l_sel_clause varchar2(10000);
990
991 BEGIN
992
993 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
994 ,'PO'
995 ,'7.1');
996
997
998 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
999 l_sel_clause := l_sel_clause
1000 || fnd_global.newline
1001 ||
1002 ' v.description POA_ATTRIBUTE1, --Description
1003 v2.description POA_ATTRIBUTE2, --UOM
1004 oset.POA_MEASURE1 POA_MEASURE1, --Quantity'
1005 || fnd_global.newline;
1006 else
1007 l_sel_clause := l_sel_clause
1008 || fnd_global.newline
1009 ||
1010 ' null POA_ATTRIBUTE1, --Description
1011 null POA_ATTRIBUTE2, --UOM
1012 null POA_MEASURE1, --Quantity'
1013 || fnd_global.newline;
1014 end if;
1015
1016 l_sel_clause := l_sel_clause ||
1017 ' oset.POA_MEASURE2 POA_MEASURE2, --Unprocessed Lines Total
1018 oset.POA_MEASURE3 POA_MEASURE3, --Unprocessed Lines Pending Sourcing
1019 oset.POA_MEASURE4 POA_MEASURE4, --Unprocessed Lines Pending Buyers Workbench
1020 oset.POA_MEASURE5 POA_MEASURE5, --Unprocessed Lines Pending Buyer Submission for Approval
1021 oset.POA_MEASURE6 POA_MEASURE6, --Unprocessed Lines Pending PO Approval
1022 oset.POA_MEASURE7 POA_MEASURE7, --Past Expected Date
1023 oset.POA_MEASURE8 POA_MEASURE8, --Emergency
1024 oset.POA_MEASURE9 POA_MEASURE9, --Urgent
1025 oset.POA_MEASURE10 POA_MEASURE10, --Grand Total Unprocessed Lines Total
1026 oset.POA_MEASURE11 POA_MEASURE11, --Grand Total Unprocessed Lines Pending Sourcing
1027 oset.POA_MEASURE12 POA_MEASURE12, --Grand Total Unprocessed Lines Pending Buyers Workbench
1028 oset.POA_MEASURE13 POA_MEASURE13, --Grand Total Unprocessed Lines Pending Buyer Submission for Approval
1029 oset.POA_MEASURE14 POA_MEASURE14, --Grand Total Unprocessed Lines Pending PO Approval
1030 oset.POA_MEASURE15 POA_MEASURE15, --Grand Total Past Expected Date
1031 oset.POA_MEASURE16 POA_MEASURE16, --Grand Total Emergency
1032 oset.POA_MEASURE17 POA_MEASURE17, --Grand Total Urgent
1033 oset.POA_MEASURE3 POA_ATTRIBUTE4, --Graph Unprocessed Lines Pending Sourcing
1034 oset.POA_MEASURE4 POA_ATTRIBUTE5, --Graph Unprocessed Lines Pending Buyers Workbench
1035 oset.POA_MEASURE5 POA_ATTRIBUTE6, --Graph Unprocessed Lines Pending Buyer Submission for Approval
1036 oset.POA_MEASURE6 POA_ATTRIBUTE7, --Graph Unprocessed Lines Pending PO Approval
1037 oset.POA_MEASURE2 POA_ATTRIBUTE8, --Graph Unprocessed Lines Total
1038 oset.POA_MEASURE7 POA_ATTRIBUTE9, ';
1039
1040
1041 l_sel_clause := l_sel_clause || '
1042 ''pFunctionName=POA_DBI_UPR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=1'' POA_ATTRIBUTE14,
1043 ''pFunctionName=POA_DBI_UPR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=1'' POA_ATTRIBUTE15,
1044 ''pFunctionName=POA_DBI_UPR_SRC_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=2'' POA_ATTRIBUTE16,
1045 ''pFunctionName=POA_DBI_UPR_SRC_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=2'' POA_ATTRIBUTE17,
1046 ''pFunctionName=POA_DBI_UPR_BW_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=3'' POA_ATTRIBUTE18,
1047 ''pFunctionName=POA_DBI_UPR_BW_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=3'' POA_ATTRIBUTE19,
1048 ''pFunctionName=POA_DBI_UPR_BSA_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=4'' POA_ATTRIBUTE20,
1049 ''pFunctionName=POA_DBI_UPR_BSA_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=4'' POA_ATTRIBUTE21,
1050 ''pFunctionName=POA_DBI_UPR_PA_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=5'' POA_ATTRIBUTE22,
1051 ''pFunctionName=POA_DBI_UPR_PA_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=5'' POA_ATTRIBUTE23,
1052 ''pFunctionName=POA_DBI_UPR_PED_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=6'' POA_ATTRIBUTE24,
1053 ''pFunctionName=POA_DBI_UPR_PED_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=6'' POA_ATTRIBUTE25,
1054 ''pFunctionName=POA_DBI_UPR_EMG_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=7'' POA_ATTRIBUTE26,
1055 ''pFunctionName=POA_DBI_UPR_URG_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=8'' POA_ATTRIBUTE27 ';
1056
1057
1058
1059 l_sel_clause := l_sel_clause || '
1060 from
1061 (select * from (select * from
1062 (select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
1063
1064 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
1065 l_sel_clause := l_sel_clause || ',base_uom';
1066 end if;
1067 l_sel_clause := l_sel_clause || ')) - 1 rnk,' || p_view_by_col;
1068
1069 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
1070 l_sel_clause := l_sel_clause ||
1071 ' , base_uom,
1072 POA_MEASURE1';
1073 end if;
1074
1075 l_sel_clause := l_sel_clause || ',POA_MEASURE2,
1076 POA_MEASURE3, POA_MEASURE4,
1077 POA_MEASURE5, POA_MEASURE6,
1078 POA_MEASURE7, POA_MEASURE8,
1079 POA_MEASURE9, POA_MEASURE10,
1080 POA_MEASURE11, POA_MEASURE12,
1081 POA_MEASURE13, POA_MEASURE14,
1082 POA_MEASURE15, POA_MEASURE16,
1083 POA_MEASURE17
1084 from (select ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
1085
1086 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
1087 l_sel_clause := l_sel_clause || ' base_uom,
1088 decode(base_uom,null,to_number(null),nvl(c_unproc_qty,0)) POA_MEASURE1,';
1089 end if;
1090 l_sel_clause := l_sel_clause || '
1091 nvl(c_unproc_cnt,0) POA_MEASURE2,
1092 nvl(c_pen_src_cnt,0) POA_MEASURE3,
1093 nvl(c_pen_buyer_wk_cnt,0) POA_MEASURE4,
1094 nvl(c_pen_po_submit_cnt,0) POA_MEASURE5,
1095 nvl(c_pen_po_appr_cnt,0) POA_MEASURE6,
1096 nvl(c_unproc_ped_cnt,0) POA_MEASURE7,
1097 nvl(c_unproc_emer_cnt,0) POA_MEASURE8,
1098 nvl(c_unproc_urg_cnt,0) POA_MEASURE9,
1099 nvl(c_unproc_cnt_total,0) POA_MEASURE10,
1100 nvl(c_pen_src_cnt_total,0) POA_MEASURE11,
1101 nvl(c_pen_buyer_wk_cnt_total,0) POA_MEASURE12,
1102 nvl(c_pen_po_submit_cnt_total,0) POA_MEASURE13,
1103 nvl(c_pen_po_appr_cnt_total,0) POA_MEASURE14,
1104 nvl(c_unproc_ped_cnt_total,0) POA_MEASURE15,
1105 nvl(c_unproc_emer_cnt_total,0) POA_MEASURE16,
1106 nvl(c_unproc_urg_cnt_total,0) POA_MEASURE17
1107 ';
1108
1109 return l_sel_clause;
1110 END;
1111
1112
1113 FUNCTION get_sum_rpt_filter_where(p_view_by in VARCHAR2) return VARCHAR2
1114 IS
1115 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
1116 BEGIN
1117 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
1118 l_col_tbl.extend;
1119 l_col_tbl(1) := 'POA_MEASURE2';
1120 l_col_tbl.extend;
1121 l_col_tbl(2) := 'POA_MEASURE3';
1122 l_col_tbl.extend;
1123 l_col_tbl(3) := 'POA_MEASURE4';
1124 l_col_tbl.extend;
1125 l_col_tbl(4) := 'POA_MEASURE5';
1126 l_col_tbl.extend;
1127 l_col_tbl(5) := 'POA_MEASURE6';
1128 l_col_tbl.extend;
1129 l_col_tbl(6) := 'POA_MEASURE7';
1130 l_col_tbl.extend;
1131 l_col_tbl(7) := 'POA_MEASURE8';
1132 l_col_tbl.extend;
1133 l_col_tbl(8) := 'POA_MEASURE9';
1134
1135 if(p_view_by = 'ITEM+POA_ITEMS') then
1136 l_col_tbl.extend;
1137 l_col_tbl(9) := 'POA_MEASURE1';
1138 end if;
1139
1140 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
1141
1142 END;
1143
1144
1145 PROCEDURE dtl_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
1146 x_custom_sql OUT NOCOPY VARCHAR2,
1147 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
1148 IS
1149 l_query varchar2(10000);
1150 l_cur_suffix varchar2(2);
1151 l_where_clause varchar2(2000);
1152 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
1153 l_in_join_tables VARCHAR2(240) ;
1154 l_context NUMBER;
1155 l_context_where VARCHAR2(240);
1156 l_bucket VARCHAR2(50);
1157 l_bucket_where VARCHAR2(440);
1158 l_rownum_where varchar2(300);
1159 l_vo_max_fetch_size varchar2(100);
1160 BEGIN
1161 poa_dbi_sutil_pkg.drill_process_parameters(p_param, l_cur_suffix, l_where_clause, l_in_join_tbl, 'PO', '7.1',
1162 'VPP','REQS');
1163
1164 IF(l_in_join_tbl is not null) then
1165 FOR i in 1 .. l_in_join_tbl.COUNT
1166 LOOP
1167 l_in_join_tables := l_in_join_tables || ', ' || l_in_join_tbl(i).table_name || ' ' || l_in_join_tbl(i).table_alias;
1168 END LOOP;
1169 END IF;
1170
1171 FOR i IN 1..p_param.COUNT
1172 LOOP
1173
1174 IF (p_param(i).parameter_name = 'POA_ATTRIBUTE10')
1175 THEN l_context:= p_param(i).parameter_id;
1176 END IF;
1177 IF (p_param(i).parameter_name = 'POA_BUCKET+REQUISITION_AGING')
1178 THEN l_bucket := p_param(i).parameter_id;
1179 END IF;
1180 END LOOP;
1181
1182 l_context_where := CASE
1183 WHEN l_context=1 THEN
1184 ' and fact.po_approved_date is null '
1185 WHEN l_context=2 THEN
1186 ' and fact.po_creation_date is null and fact.sourcing_flag=''Y'' '
1187 -- Add on Req flag and Auction Header clause
1188 WHEN l_context=3 THEN
1189 ' and fact.po_creation_date is null and fact.sourcing_flag=''N'' '
1190 WHEN l_context=4 THEN
1191 ' and fact.po_creation_date <= to_date(&REQ_FACT_UPDATE_DATE,''DD/MM/YYYY HH24:MI:SS'') and fact.po_submit_date is null '
1192 WHEN l_context=5 THEN
1193 ' and fact.po_submit_date <= to_date(&REQ_FACT_UPDATE_DATE,''DD/MM/YYYY HH24:MI:SS'') '
1194 WHEN l_context=6 THEN
1195 ' and (fact.expected_date <= to_date(&REQ_FACT_UPDATE_DATE,''DD/MM/YYYY HH24:MI:SS'') or fact.unproc_ped_flag = ''Y'' ) '
1196 WHEN l_context=7 THEN
1197 ' and nvl(fact.emergency_flag,''N'')=''Y'' '
1198 WHEN l_context=8 THEN
1199 ' and nvl(fact.urgent_flag,''N'')=''Y'' '
1200 ELSE
1201 ''
1202 END;
1203
1204 if(l_bucket is not null) then
1205 l_bucket_where := 'and (&RANGE_LOW is null or '
1206 || '(to_date(&REQ_FACT_UPDATE_DATE,''DD/MM/YYYY HH24:MI:SS'')-fact.req_approved_date)'
1207 || ' >= &RANGE_LOW)'
1208 || fnd_global.newline
1209 ||' and (&RANGE_HIGH is null or '
1210 ||'(to_date(&REQ_FACT_UPDATE_DATE,''DD/MM/YYYY HH24:MI:SS'')-fact.req_approved_date)'
1211 || ' < &RANGE_HIGH)';
1212
1213 poa_dbi_util_pkg.bind_low_high(p_param
1214 , 'POA_DBI_UPR_BUCKET'
1215 , 'POA_BUCKET+REQUISITION_AGING'
1216 , '&RANGE_LOW'
1217 , '&RANGE_HIGH'
1218 , x_custom_output);
1219 else
1220 l_bucket_where := '';
1221 end if;
1222
1223 poa_dbi_sutil_pkg.bind_reqfact_date(x_custom_output);
1224
1225 /* Determine the l_rownum_where. If VO_MAX_FETCH_SIZE is null then dont filter any rows */
1226 select fnd_profile.value('VO_MAX_FETCH_SIZE')
1227 into l_vo_max_fetch_size
1228 from dual;
1229
1230 if (l_vo_max_fetch_size is not null) then
1231 l_rownum_where := ' where rownum < '||l_vo_max_fetch_size||' + 1 ';
1232 else
1233 l_rownum_where := ' ';
1234 end if;
1235 x_custom_sql := ' select
1236 prh.segment1 POA_MEASURE1, -- Requisition Number
1237 prl.line_num POA_MEASURE2, -- Line Num
1238 rorg.name POA_ATTRIBUTE1, -- Req Creation OU
1239 substrb(perf.first_name,1,1) || ''. '' || perf.last_name POA_ATTRIBUTE2, -- Requestor Name
1240 POA_ATTRIBUTE3 POA_ATTRIBUTE3, -- Req Approved Date
1241 POA_ATTRIBUTE4 POA_ATTRIBUTE4, -- Expected Date
1242 item.value POA_ATTRIBUTE5, -- Item Name
1243 supplier.value POA_ATTRIBUTE6, -- Supplier Name
1244 nvl(i.POA_MEASURE3,0) POA_MEASURE3, -- Amount
1245 ';
1246
1247 if(l_context=2) then
1248 x_custom_sql := x_custom_sql || '
1249 decode(prl.auction_display_number,null,''RFQ'',prl.auction_display_number) POA_MEASURE4, -- Sourcing Document Number
1250 decode(pll.po_release_id,null,
1251 poh.segment1,
1252 poh.segment1||''-''||por.release_num) POA_MEASURE5, -- PO Number
1253 ponorg.name POA_ATTRIBUTE7, -- Sourcing Org Value
1254 ';
1255 else
1256 x_custom_sql := x_custom_sql || '
1257 decode(i.sourcing_flag,''Y'',
1258 decode(prl.auction_display_number,null,''RFQ'',prl.auction_display_number),null) POA_MEASURE4, -- Sourcing Document Number
1259 decode(pll.po_release_id,null,
1260 poh.segment1,
1261 poh.segment1||''-''||por.release_num) POA_MEASURE5, -- PO Number
1262 porg.name POA_ATTRIBUTE7, -- PO Value Value
1263 ';
1264 end if;
1265 x_custom_sql := x_custom_sql || ' nvl(i.POA_MEASURE6,0) POA_MEASURE6, -- Grand Total Amount
1266 i.req_header_id POA_ATTRIBUTE11, -- Req Header ID for drill down to ip Report
1267 ';
1268 if(l_context=2) then
1269 x_custom_sql := x_custom_sql || ' decode(prl.auction_display_number,null,null,decode(pon.auction_status, ''DRAFT'',null,
1270 ''pFunctionName=POA_DBI_NEG_DRILL&AuctionId=''||prl.auction_display_number ||''&addBreadCrumb=Y&retainAM=Y'')) POA_ATTRIBUTE12, ';
1271 else
1272 x_custom_sql := x_custom_sql || ' null POA_ATTRIBUTE12,';
1273 end if;
1274 x_custom_sql := x_custom_sql || '
1275 ( case when poh.segment1 is null
1276 then null
1277 when pll.po_release_id is null
1278 then ( case when poh.type_lookup_code = ''PLANNED''
1279 then null
1280 else ''pFunctionName=POA_DBI_PDF_DRILL&DocumentId='' || poh.po_header_id || ''&RevisionNum=''
1281 || poh.revision_num || ''&LanguageCode='' || userenv(''LANG'') || ''&DocumentType=PO&DocumentSubtype=STANDARD&OrgId='' || poh.org_id
1282 || ''&UserSecurity=Y&StoreFlag=N&ViewOrCommunicate=View&CallFromForm=N''
1283 end
1284 )
1285 else ''pFunctionName=POA_DBI_PDF_DRILL&DocumentId='' || por.po_release_id || ''&RevisionNum=''
1286 || por.revision_num || ''&LanguageCode='' || userenv(''LANG'') || ''&DocumentType=RELEASE&DocumentSubtype=BLANKET&OrgId='' || por.org_id
1287 || ''&UserSecurity=Y&StoreFlag=N&ViewOrCommunicate=View&CallFromForm=N''
1288 end
1289 ) POA_ATTRIBUTE13
1290 from (select * from (select * from
1291 (select (rank() over
1292 (&ORDER_BY_CLAUSE nulls last, req_header_id,
1293 req_line_id))-1 rnk,
1294 req_header_id,
1295 req_line_id,
1296 req_creation_ou_id,
1297 requester_id,
1298 POA_ATTRIBUTE3 POA_ATTRIBUTE3,
1299 POA_ATTRIBUTE4 POA_ATTRIBUTE4,
1300 po_item_id,
1301 supplier_id,
1302 nvl(POA_MEASURE3,0) POA_MEASURE3,
1303 nvl(POA_MEASURE6,0) POA_MEASURE6,
1304 po_line_location_id,
1305 po_creation_ou_id,
1306 sourcing_flag
1307 from
1308 (
1309 select
1310 fact.req_header_id,
1311 fact.req_line_id,
1312 fact.req_creation_ou_id,
1313 fact.requester_id,
1314 fact.req_approved_date POA_ATTRIBUTE3,
1315 fact.expected_date POA_ATTRIBUTE4,
1316 fact.po_item_id,
1317 fact.supplier_id,
1318 fact.line_amount_' || l_cur_suffix || ' POA_MEASURE3,
1319 sum(fact.line_amount_' || l_cur_suffix || ') over() POA_MEASURE6,
1320 fact.po_line_location_id,
1321 fact.po_creation_ou_id,
1322 fact.sourcing_flag
1323 from
1324 poa_dbi_req_f fact ' || l_in_join_tables || '
1325 where
1326 fact.req_approved_date is not null
1327 and fact.po_approved_date is null '
1328 || l_where_clause || fnd_global.newline ||
1329 l_context_where || fnd_global.newline ||
1330 l_bucket_where ||
1331 ')) i2 where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)) i3 '||l_rownum_where||' ) i,
1332 po_requisition_headers_all prh,
1333 po_requisition_lines_all prl,
1334 po_headers_all poh,
1335 po_line_locations_all pll,
1336 poa_items_v item,
1337 poa_suppliers_v supplier,
1338 per_all_people_f perf,
1339 hr_all_organization_units_vl rorg,
1340 hr_all_organization_units_vl porg,
1341 po_releases_all por ';
1342 if(l_context=2) then
1343 x_custom_sql := x_custom_sql || '
1344 , pon_auction_headers_all pon
1345 , hr_all_organization_units_vl ponorg ';
1346 end if;
1347 x_custom_sql := x_custom_sql || 'where
1348 i.req_header_id=prh.requisition_header_id
1349 and i.req_line_id=prl.requisition_line_id
1350 and prh.requisition_header_id=prl.requisition_header_id
1351 and i.po_item_id=item.id
1352 and i.req_creation_ou_id=rorg.organization_id
1353 and i.requester_id=perf.person_id
1354 and sysdate between perf.effective_start_date and perf.effective_end_date
1355 and i.supplier_id=supplier.id(+)
1356 and i.po_line_location_id=pll.line_location_id(+)
1357 and pll.po_header_id=poh.po_header_id(+)
1358 and poh.org_id=porg.organization_id(+)
1359 and pll.po_header_id = por.po_header_id(+)
1360 and pll.po_release_id = por.po_release_id(+) ';
1361 if(l_context=2) then
1362 x_custom_sql := x_custom_sql || '
1363 and prl.auction_header_id=pon.auction_header_id(+)
1364 and pon.org_id= ponorg.organization_id(+) ';
1365 end if;
1366
1367 x_custom_sql := x_custom_sql || '
1368 ORDER BY rnk';
1369 poa_dbi_util_pkg.get_custom_status_binds(x_custom_output);
1370
1371 end;
1372 end poa_dbi_upr_pkg;