[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_FR_PKG
Source
1 package body poa_dbi_fr_pkg
2 /* $Header: poadbifrb.pls 120.3 2006/08/11 08:04:46 sriswami noship $ */
3 as
4 /*Forward declaration of local functions*/
5 function get_status_sel_clause(p_view_by_dim in varchar2
6 ,p_view_by_col in varchar2) return varchar2;
7 function get_summary_sel_clause(p_view_by_dim in varchar2
8 ,p_view_by_col in varchar2) return varchar2;
9 function get_amt_sql_sel_clause(p_view_by_dim in varchar2
10 ,p_view_by_col in varchar2
11 ,p_bucket_rec in BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE) return varchar2;
12 function get_req_age_sel_clause(p_view_by_dim in varchar2
13 ,p_view_by_col in varchar2
14 ,p_bucket_rec in BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE) return varchar2;
15 function get_trend_sel_clause return varchar2;
16 function get_amt_trend_sel_clause return varchar2;
17 function get_ped_trend_sel_clause return varchar2;
18 function get_status_filter_where(p_view_by in varchar2) return varchar2;
19 function get_summary_filter_where(p_view_by in varchar2) return varchar2;
20 function get_amt_sql_filter_where(p_view_by in varchar2) return varchar2;
21 function get_req_age_filter_where(p_view_by in varchar2) return varchar2;
22 /*End of forward declaration section*/
23
24 /*Procedure and Function definitions*/
25 /*Fulfilled Requisitions report*/
26 procedure status_sql(p_param in bis_pmv_page_parameter_tbl
27 ,x_custom_sql out nocopy varchar2
28 ,x_custom_output out nocopy bis_query_attributes_tbl)
29 is
30 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
31 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
32 l_query varchar2(10000);
33 l_view_by varchar2(120);
34 l_view_by_col varchar2(120);
35 l_as_of_date date;
36 l_prev_as_of_date date;
37 l_xtd varchar2(10);
38 l_comparison_type varchar2(1);
39 l_nested_pattern number;
40 l_cur_suffix varchar2(2);
41 l_in_join_tbl poa_dbi_util_pkg.poa_dbi_in_join_tbl;
42 l_join_rec poa_dbi_util_pkg.poa_dbi_join_rec;
43 l_where_clause varchar2(2000);
44 l_mv varchar2(30);
45 l_view_by_value varchar2(30);
46 err_msg varchar2(100);
47 err_cde number;
48 begin
49 l_comparison_type := 'Y';
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
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 => 'REQMF');
73
74 poa_dbi_util_pkg.add_column(l_col_tbl
75 , 'fulfilled_cnt_'||l_cur_suffix
76 , 'fulf_cnt'
77 , p_grand_total => 'Y'
78 , p_prior_code => poa_dbi_util_pkg.both_priors
79 , p_to_date_type => 'RLX');
80
81 poa_dbi_util_pkg.add_column(l_col_tbl
82 , 'fulfilled_man_cnt_'||l_cur_suffix
83 , 'fulf_man_cnt'
84 , p_grand_total => 'Y'
85 , p_prior_code => poa_dbi_util_pkg.both_priors
86 , p_to_date_type => 'RLX');
87
88 poa_dbi_util_pkg.add_column(l_col_tbl
89 , 'fulfilled_ped_cnt'
90 , 'fulf_ped_cnt'
91 , p_grand_total => 'Y'
92 , p_prior_code => poa_dbi_util_pkg.both_priors
93 , p_to_date_type => 'RLX');
94
95 poa_dbi_util_pkg.add_column(l_col_tbl
96 , 'fulfilled_amt_'||l_cur_suffix
97 , 'fulf_amt'
98 , p_grand_total => 'Y'
99 , p_prior_code => poa_dbi_util_pkg.both_priors
100 , p_to_date_type => 'RLX');
101
102 poa_dbi_util_pkg.add_column(l_col_tbl
103 , 'days_to_fulfill'
104 , 'days_fulf'
105 , p_grand_total => 'Y'
106 , p_prior_code => poa_dbi_util_pkg.both_priors
107 , p_to_date_type => 'RLX');
108
109 if(l_view_by = 'ITEM+POA_ITEMS') then
110 poa_dbi_util_pkg.add_column(l_col_tbl
111 ,'fulfilled_qty'
112 ,'fulf_qty'
113 , p_grand_total => 'Y'
114 , p_prior_code => poa_dbi_util_pkg.both_priors
115 , p_to_date_type => 'RLX');
116 end if;
117
118 l_query := get_status_sel_clause(l_view_by, l_view_by_col) || ' from ';
119
120 l_query := l_query ||
121 poa_dbi_template_pkg.status_sql(l_mv,
122 l_where_clause,
123 l_join_tbl,
124 p_use_windowing => 'Y',
125 p_col_name => l_col_tbl,
126 p_use_grpid => 'N',
127 p_filter_where => get_status_filter_where(l_view_by),
128 p_in_join_tables => l_in_join_tbl);
129
130 x_custom_sql := l_query;
131 exception
132 when others then
133 err_msg := substr(sqlerrm,1,400);
134 end;
135
136 function get_status_sel_clause(p_view_by_dim in varchar2
137 ,p_view_by_col in varchar2)
138 return varchar2 is
139 l_sel_clause varchar2(6000);
140 begin
141 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
142 ,'PO'
143 ,'7.1');
144 if(p_view_by_dim = 'ITEM+POA_ITEMS')
145 then
146 l_sel_clause := l_sel_clause || fnd_global.newline ||
147 'v.description POA_ATTRIBUTE3, --Description
148 v2.description POA_ATTRIBUTE4, --UOM
149 oset.POA_ATTRIBUTE5 POA_ATTRIBUTE5, --Quantity';
150 else
151 l_sel_clause := l_sel_clause || fnd_global.newline ||
152 'null POA_ATTRIBUTE3, --Description
153 null POA_ATTRIBUTE4, --UOM
154 null POA_ATTRIBUTE5, --Quantity';
155 end if;
156
157 l_sel_clause := l_sel_clause || fnd_global.newline ||
158 'oset.POA_MEASURE1 POA_MEASURE1, --Fulfilled Lines
159 oset.POA_MEASURE10 POA_MEASURE10, --Fulfilled Lines Prior
160 oset.POA_PERCENT1 POA_PERCENT1, --Fulf Lines Change
161 oset.POA_MEASURE2 POA_MEASURE2, --Manual Lines
162 oset.POA_PERCENT2 POA_PERCENT2, --Manual Lines Rate
163 oset.POA_PERCENT3 POA_PERCENT3, --Manual Lines Rate Change
164 oset.POA_PERCENT4 POA_PERCENT4, --Percent Past Expected Date
165 oset.POA_PERCENT11 POA_PERCENT11, --Percent Past Expected Date Prior
166 oset.POA_MEASURE3 POA_MEASURE3, --Fulfilled Amount
167 oset.POA_MEASURE11 POA_MEASURE11, --Fulfilled Amount Prior
168 oset.POA_PERCENT5 POA_PERCENT5, --Fulf Amount Change
169 oset.POA_MEASURE4 POA_MEASURE4, --Average Age (Days)
170 oset.POA_MEASURE12 POA_MEASURE12, --Average Age (Days) Prior
171 oset.POA_MEASURE2 POA_MEASURE20, --Manual (Graph 1)
172 oset.POA_MEASURE5 POA_MEASURE5, --Automated Lines
173 oset.POA_MEASURE6 POA_MEASURE6, --Grand Total for Fulfilled Lines
174 oset.POA_MEASURE13 POA_MEASURE13, --Grand Total for Fulfilled Lines Prior
175 oset.POA_PERCENT6 POA_PERCENT6, --Grand Tot fulf lines change
176 oset.POA_MEASURE7 POA_MEASURE7, --Grand Total for Manual Lines
177 oset.POA_PERCENT7 POA_PERCENT7, --Grand Total for Manual Lines Rate
178 oset.POA_PERCENT8 POA_PERCENT8, --Grand Tot Man Lines Rate Change
179 oset.POA_PERCENT9 POA_PERCENT9, --Grand Total for Percent Past Expected Date
180 oset.POA_PERCENT12 POA_PERCENT12, --Grand Total for Percent PED Prior
181 oset.POA_MEASURE8 POA_MEASURE8, --Grand Total for Fulfilled Amount
182 oset.POA_MEASURE14 POA_MEASURE14, --Grand Total for Fulfilled Amount Prior
183 oset.POA_PERCENT10 POA_PERCENT10, --Grand Total for fulf amt Change
184 oset.POA_MEASURE9 POA_MEASURE9, --Grand Total for Average Age (Days)
185 oset.POA_MEASURE15 POA_MEASURE15, --Grand Total for Average Age (Days) Prior '||fnd_global.newline;
186
187 l_sel_clause := l_sel_clause ||
188 '''pFunctionName=POA_DBI_FR_SUM_RPT&VIEW_BY=ITEM+ENI_ITEM_PO_CAT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' POA_ATTRIBUTE6,
189 ''pFunctionName=POA_DBI_FR_MAN_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=2'' POA_ATTRIBUTE7,
190 ''pFunctionName=POA_DBI_FR_SUM_RPT&VIEW_BY=ITEM+ENI_ITEM_PO_CAT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&pCustomView=POA_DBI_FR_SUM_RPT_CV2'' POA_ATTRIBUTE8,
191 ''pFunctionName=POA_DBI_FR_AMT_RPT&VIEW_BY=ITEM+ENI_ITEM_PO_CAT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' POA_ATTRIBUTE9,
192 ''pFunctionName=POA_DBI_FR_AGE_RPT&VIEW_BY=ITEM+ENI_ITEM_PO_CAT&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' POA_ATTRIBUTE10';
193
194 l_sel_clause := l_sel_clause|| fnd_global.newline||'from
195 ( select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col|| ')) - 1 rnk,'
196 ||fnd_global.newline|| ' ' || p_view_by_col;
197
198 if(p_view_by_dim = 'ITEM+POA_ITEMS')
199 then
200 l_sel_clause := l_sel_clause ||',
201 base_uom,
202 POA_ATTRIBUTE5';
203 end if;
204
205 l_sel_clause := l_sel_clause || ',
206 POA_MEASURE1,
207 POA_MEASURE10,
208 POA_PERCENT1,
209 POA_MEASURE2,
210 POA_PERCENT2,
211 POA_PERCENT3,
212 POA_PERCENT4,
213 POA_PERCENT11,
214 POA_MEASURE3,
215 POA_MEASURE11,
216 POA_PERCENT5,
217 POA_MEASURE4,
218 POA_MEASURE12,
219 POA_MEASURE5,
220 POA_MEASURE6,
221 POA_MEASURE13,
222 POA_PERCENT6,
223 POA_MEASURE7,
224 POA_PERCENT7,
225 POA_PERCENT8,
226 POA_PERCENT9,
227 POA_PERCENT12,
228 POA_MEASURE8,
229 POA_MEASURE14,
230 POA_PERCENT10,
231 POA_MEASURE9,
232 POA_MEASURE15
233 from
234 ( select ' || p_view_by_col || ',';
235
236 if(p_view_by_dim = 'ITEM+POA_ITEMS')
237 then
238 l_sel_clause := l_sel_clause || '
239 base_uom,
240 nvl(c_fulf_qty,0) POA_ATTRIBUTE5,';
241 end if;
242
243 l_sel_clause := l_sel_clause || '
244 nvl(c_fulf_cnt,0) POA_MEASURE1,
245 nvl(p_fulf_cnt,0) POA_MEASURE10,
246 '||poa_dbi_util_pkg.change_clause('c_fulf_cnt','p_fulf_cnt','NP')||' POA_PERCENT1,
247 nvl(c_fulf_man_cnt,0) POA_MEASURE2,
248 '||poa_dbi_util_pkg.rate_clause('c_fulf_man_cnt','c_fulf_cnt','P')||' POA_PERCENT2,
249 '||poa_dbi_util_pkg.change_clause(
250 poa_dbi_util_pkg.rate_clause('c_fulf_man_cnt','c_fulf_cnt','P'),
251 poa_dbi_util_pkg.rate_clause('p_fulf_man_cnt','p_fulf_cnt','P'),'P')||' POA_PERCENT3,
252 '||poa_dbi_util_pkg.rate_clause('c_fulf_ped_cnt','c_fulf_cnt','P')||' POA_PERCENT4,
253 '||poa_dbi_util_pkg.rate_clause('p_fulf_ped_cnt','p_fulf_cnt','P')||' POA_PERCENT11,
254 nvl(c_fulf_amt,0) POA_MEASURE3,
255 nvl(p_fulf_amt,0) POA_MEASURE11,
256 '||poa_dbi_util_pkg.change_clause('c_fulf_amt','p_fulf_amt','NP')||' POA_PERCENT5, ' || fnd_global.newline ||
257 poa_dbi_util_pkg.rate_clause('c_days_fulf','c_fulf_cnt','NP') || ' POA_MEASURE4, ' || fnd_global.newline ||
258 poa_dbi_util_pkg.rate_clause('p_days_fulf','p_fulf_cnt','NP') || ' POA_MEASURE12,
259 nvl(c_fulf_cnt,0) - nvl(c_fulf_man_cnt, 0) POA_MEASURE5,
260 nvl(c_fulf_cnt_total,0) POA_MEASURE6,
261 nvl(p_fulf_cnt_total,0) POA_MEASURE13,
262 '||poa_dbi_util_pkg.change_clause('c_fulf_cnt_total','p_fulf_cnt_total','NP')||' POA_PERCENT6,
263 nvl(c_fulf_man_cnt_total,0) POA_MEASURE7,
264 '||poa_dbi_util_pkg.rate_clause('c_fulf_man_cnt_total','c_fulf_cnt_total','P')||' POA_PERCENT7,
265 '||poa_dbi_util_pkg.change_clause(
266 poa_dbi_util_pkg.rate_clause('c_fulf_man_cnt_total','c_fulf_cnt_total','P'),
267 poa_dbi_util_pkg.rate_clause('p_fulf_man_cnt_total','p_fulf_cnt_total','P'),'P')||' POA_PERCENT8,
268 '||poa_dbi_util_pkg.rate_clause('c_fulf_ped_cnt_total','c_fulf_cnt_total','P')||' POA_PERCENT9,
269 '||poa_dbi_util_pkg.rate_clause('p_fulf_ped_cnt_total','p_fulf_cnt_total','P')||' POA_PERCENT12,
270 nvl(c_fulf_amt_total,0) POA_MEASURE8,
271 nvl(p_fulf_amt_total,0) POA_MEASURE14,' || fnd_global.newline ||
272 poa_dbi_util_pkg.change_clause('c_fulf_amt_total','p_fulf_amt_total','NP') || ' POA_PERCENT10,' || fnd_global.newline ||
273 poa_dbi_util_pkg.rate_clause('c_days_fulf_total','c_fulf_cnt_total','NP') || ' POA_MEASURE9,' || fnd_global.newline ||
274 poa_dbi_util_pkg.rate_clause('p_days_fulf_total','p_fulf_cnt_total','NP') || ' POA_MEASURE15
275 ';
276
277 return l_sel_clause;
278 end;
279
280 function get_status_filter_where(p_view_by in varchar2) return varchar2
281 is
282 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
283 begin
284 l_col_tbl := poa_dbi_sutil_pkg.poa_dbi_filter_tbl();
285 l_col_tbl.extend;
286 l_col_tbl(1) := 'POA_MEASURE1';
287 l_col_tbl.extend;
288 l_col_tbl(2) := 'POA_PERCENT1';
289 l_col_tbl.extend;
290 l_col_tbl(3) := 'POA_MEASURE2';
291 l_col_tbl.extend;
292 l_col_tbl(4) := 'POA_PERCENT2';
293 l_col_tbl.extend;
294 l_col_tbl(5) := 'POA_PERCENT3';
295 l_col_tbl.extend;
296 l_col_tbl(6) := 'POA_PERCENT4';
297 l_col_tbl.extend;
298 l_col_tbl(7) := 'POA_MEASURE3';
299 l_col_tbl.extend;
300 l_col_tbl(8) := 'POA_PERCENT5';
301 l_col_tbl.extend;
302 l_col_tbl(9) := 'POA_MEASURE4';
303 l_col_tbl.extend;
304 l_col_tbl(10) := 'POA_MEASURE5';
305 if( p_view_by = 'ITEM+POA_ITEMS' ) then
306 l_col_tbl.extend;
307 l_col_tbl(11) := 'POA_ATTRIBUTE5';
308 end if;
309 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
310 end;
311
312 /*Fulfilled Requisition Lines Summary*/
313 procedure status_summary_sql(p_param in bis_pmv_page_parameter_tbl
314 ,x_custom_sql out nocopy varchar2
315 ,x_custom_output out nocopy bis_query_attributes_tbl)
316 is
317 err_msg varchar2(100);
318 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
319 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
320 l_query varchar2(10000);
321 l_view_by varchar2(120);
322 l_view_by_col varchar2(120);
323 l_as_of_date date;
324 l_prev_as_of_date date;
325 l_xtd varchar2(10);
326 l_comparison_type varchar2(1);
330 l_join_rec poa_dbi_util_pkg.poa_dbi_join_rec;
327 l_nested_pattern number;
328 l_cur_suffix varchar2(2);
329 l_in_join_tbl poa_dbi_util_pkg.poa_dbi_in_join_tbl;
331 l_where_clause varchar2(2000);
332 l_mv varchar2(30);
333 l_view_by_value varchar2(30);
334 err_cde number;
335 begin
336 l_comparison_type := 'Y';
337 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
338 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
339
340 poa_dbi_sutil_pkg.process_parameters(p_param
341 ,l_view_by
342 ,l_view_by_col
343 ,l_view_by_value
344 ,l_comparison_type
345 ,l_xtd
346 ,l_as_of_date
347 ,l_prev_as_of_date
348 ,l_cur_suffix
349 ,l_nested_pattern
350 ,l_where_clause
351 ,l_mv
355 ,p_trend => 'N'
352 ,l_join_tbl
353 ,l_in_join_tbl
354 ,x_custom_output
356 ,p_func_area => 'PO'
357 ,p_version => '7.1'
358 ,p_role => 'VPP'
359 ,p_mv_set => 'REQMF');
360
361 poa_dbi_util_pkg.add_column(l_col_tbl
362 , 'fulfilled_cnt_'||l_cur_suffix
363 , 'fulf_cnt'
364 , p_grand_total => 'Y'
365 , p_prior_code => poa_dbi_util_pkg.both_priors
366 , p_to_date_type => 'RLX');
367
368 poa_dbi_util_pkg.add_column(l_col_tbl
369 , 'fulfilled_man_cnt_'||l_cur_suffix
370 , 'fulf_man_cnt'
371 , p_grand_total => 'Y'
372 , p_prior_code => poa_dbi_util_pkg.both_priors
373 , p_to_date_type => 'RLX');
374
375 poa_dbi_util_pkg.add_column(l_col_tbl
376 , 'fulfilled_ped_cnt'
377 , 'fulf_ped_cnt'
378 , p_grand_total => 'Y'
379 , p_prior_code => poa_dbi_util_pkg.both_priors
380 , p_to_date_type => 'RLX');
381
382 if(l_view_by = 'ITEM+POA_ITEMS') then
383 poa_dbi_util_pkg.add_column(l_col_tbl
384 ,'fulfilled_qty'
385 ,'fulf_qty'
386 , p_grand_total => 'Y'
387 , p_prior_code => poa_dbi_util_pkg.both_priors
388 , p_to_date_type => 'RLX');
389 end if;
390
391 l_query := get_summary_sel_clause(l_view_by, l_view_by_col) || ' from ';
392 l_query := l_query ||
393 poa_dbi_template_pkg.status_sql(
394 l_mv,
395 l_where_clause,
396 l_join_tbl,
397 p_use_windowing => 'Y',
398 p_col_name => l_col_tbl,
399 p_use_grpid => 'N',
400 p_filter_where => get_summary_filter_where(l_view_by),
401 p_in_join_tables => l_in_join_tbl);
402
403 x_custom_sql := l_query;
404
405 exception
406 when others then
407 err_msg := substr(sqlerrm,1,400);
408 end;
409
410 function get_summary_sel_clause(p_view_by_dim in varchar2
411 ,p_view_by_col in varchar2)
412 return varchar2 is
413 l_sel_clause varchar2(5000);
414 begin
415 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
416 ,'PO'
417 ,'7.1');
418 if(p_view_by_dim = 'ITEM+POA_ITEMS')
419 then
420 l_sel_clause := l_sel_clause || fnd_global.newline ||
421 'v.description POA_ATTRIBUTE3, --Description
422 v2.description POA_ATTRIBUTE4, --UOM
423 oset.POA_ATTRIBUTE5 POA_ATTRIBUTE5, --Quantity';
424 else
425 l_sel_clause := l_sel_clause || fnd_global.newline ||
426 'null POA_ATTRIBUTE3, --Description
427 null POA_ATTRIBUTE4, --UOM
428 null POA_ATTRIBUTE5, -- Quantity';
429 end if;
430
431 l_sel_clause := l_sel_clause || fnd_global.newline ||
432 'oset.POA_MEASURE1 POA_MEASURE1, --Fulfilled Lines
433 oset.POA_PERCENT1 POA_PERCENT1, --fulf lines Change
434 oset.POA_MEASURE2 POA_MEASURE2, --Manual Lines
435 oset.POA_PERCENT2 POA_PERCENT2, --Manual Lines Rate
436 oset.POA_PERCENT3 POA_PERCENT3, --man lines rate Change
437 oset.POA_PERCENT4 POA_PERCENT4, --Past Expected Date
438 oset.POA_PERCENT4 POA_MEASURE9, --Fulfilled Lines Past Expected Date (Graph 2)
439 oset.POA_MEASURE3 POA_MEASURE3, --Percent Past Expected Date
440 oset.POA_MEASURE2 POA_MEASURE8, --Manual (Graph 1)
441 oset.POA_MEASURE4 POA_MEASURE4, --Automated Lines
442 oset.POA_MEASURE5 POA_MEASURE5, --Grand Total for Fulfilled Lines
443 oset.POA_PERCENT5 POA_PERCENT5, --Grand Total for fulf lines Change
444 oset.POA_MEASURE6 POA_MEASURE6, --Grand Total for Manual Lines
445 oset.POA_PERCENT6 POA_PERCENT6, --Grand Total for Manual Lines Rate
446 oset.POA_PERCENT7 POA_PERCENT7, --Grand Tot man lines rate Change
447 oset.POA_PERCENT8 POA_PERCENT8, --Grand Total for Past Expected Date
448 oset.POA_MEASURE7 POA_MEASURE7, --Grand Total for Percent Past Expected Date'||fnd_global.newline;
449
450 l_sel_clause := l_sel_clause ||
451 '''pFunctionName=POA_DBI_FR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' POA_ATTRIBUTE6,
452 ''pFunctionName=POA_DBI_FR_MAN_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=2'' POA_ATTRIBUTE7,
453 ''pFunctionName=POA_DBI_FR_PED_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=3'' POA_ATTRIBUTE8';
454
455 l_sel_clause := l_sel_clause||fnd_global.newline||'from
456 ( select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col|| ')) - 1 rnk,'
457 ||fnd_global.newline|| ' ' || p_view_by_col;
458
459 if(p_view_by_dim = 'ITEM+POA_ITEMS')
460 then
461 l_sel_clause := l_sel_clause ||',
462 base_uom,
463 POA_ATTRIBUTE5';
464 end if;
465
466 l_sel_clause := l_sel_clause || ',
467 POA_MEASURE1,
468 POA_PERCENT1,
469 POA_MEASURE2,
470 POA_PERCENT2,
471 POA_PERCENT3,
472 POA_PERCENT4,
473 POA_MEASURE3,
474 POA_MEASURE4,
478 POA_PERCENT6,
475 POA_MEASURE5,
476 POA_PERCENT5,
477 POA_MEASURE6,
479 POA_PERCENT7,
480 POA_PERCENT8,
481 POA_MEASURE7
482 from
483 ( select ' || p_view_by_col || ',';
484
485 if(p_view_by_dim = 'ITEM+POA_ITEMS')
486 then
487 l_sel_clause := l_sel_clause || '
488 base_uom,
489 nvl(c_fulf_qty,0) POA_ATTRIBUTE5,';
490 end if;
491
492 l_sel_clause := l_sel_clause || '
493 nvl(c_fulf_cnt,0) POA_MEASURE1,
494 '||poa_dbi_util_pkg.change_clause('c_fulf_cnt','p_fulf_cnt','NP')||' POA_PERCENT1,
495 nvl(c_fulf_man_cnt,0) POA_MEASURE2,
496 '||poa_dbi_util_pkg.rate_clause('c_fulf_man_cnt','c_fulf_cnt','P')||' POA_PERCENT2,
497 '||poa_dbi_util_pkg.change_clause(
498 poa_dbi_util_pkg.rate_clause('c_fulf_man_cnt','c_fulf_cnt','P'),
499 poa_dbi_util_pkg.rate_clause('p_fulf_man_cnt','p_fulf_cnt','P'),'P')||' POA_PERCENT3,
500 nvl(c_fulf_ped_cnt,0) POA_PERCENT4,
501 '||poa_dbi_util_pkg.rate_clause('c_fulf_ped_cnt','c_fulf_cnt','P')||' POA_MEASURE3,
502 nvl(c_fulf_cnt,0) - nvl(c_fulf_man_cnt,0) POA_MEASURE4,
503 nvl(c_fulf_cnt_total,0) POA_MEASURE5,
504 '||poa_dbi_util_pkg.change_clause('c_fulf_cnt_total','p_fulf_cnt_total','NP')||' POA_PERCENT5,
505 nvl(c_fulf_man_cnt_total,0) POA_MEASURE6,
506 '||poa_dbi_util_pkg.rate_clause('c_fulf_man_cnt_total','c_fulf_cnt_total','P')||' POA_PERCENT6,
507 '||poa_dbi_util_pkg.change_clause(
508 poa_dbi_util_pkg.rate_clause('c_fulf_man_cnt_total','c_fulf_cnt_total','P'),
509 poa_dbi_util_pkg.rate_clause('p_fulf_man_cnt_total','p_fulf_cnt_total','P'),'P')||' POA_PERCENT7,
510 nvl(c_fulf_ped_cnt_total,0) POA_PERCENT8,
511 '||poa_dbi_util_pkg.rate_clause('c_fulf_ped_cnt_total','c_fulf_cnt_total','P')||' POA_MEASURE7
512 ';
513
514 return l_sel_clause;
515 end;
516
517 function get_summary_filter_where(p_view_by in varchar2) return varchar2
518 is
519 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
520 begin
521 l_col_tbl := poa_dbi_sutil_pkg.poa_dbi_filter_tbl();
522 l_col_tbl.extend;
523 l_col_tbl(1) := 'POA_MEASURE1';
524 l_col_tbl.extend;
525 l_col_tbl(2) := 'POA_PERCENT1';
526 l_col_tbl.extend;
527 l_col_tbl(3) := 'POA_MEASURE2';
528 l_col_tbl.extend;
529 l_col_tbl(4) := 'POA_PERCENT2';
530 l_col_tbl.extend;
531 l_col_tbl(5) := 'POA_PERCENT3';
532 l_col_tbl.extend;
533 l_col_tbl(6) := 'POA_PERCENT4';
534 l_col_tbl.extend;
535 l_col_tbl(7) := 'POA_MEASURE3';
536 if( p_view_by = 'ITEM+POA_ITEMS' ) then
537 l_col_tbl.extend;
538 l_col_tbl(8) := 'POA_ATTRIBUTE5';
539 end if;
540 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
541 end;
542
543 /*Fulfilled Requisitions Amount report*/
544 procedure amt_sql(p_param in bis_pmv_page_parameter_tbl
545 ,x_custom_sql out nocopy varchar2
546 ,x_custom_output out nocopy bis_query_attributes_tbl)
547 is
548 err_msg varchar2(100);
549 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
550 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
551 l_query varchar2(10000);
552 l_view_by varchar2(120);
553 l_view_by_col varchar2(120);
554 l_as_of_date date;
555 l_prev_as_of_date date;
556 l_xtd varchar2(10);
557 l_comparison_type varchar2(1);
558 l_nested_pattern number;
559 l_cur_suffix varchar2(2);
560 l_in_join_tbl poa_dbi_util_pkg.poa_dbi_in_join_tbl;
561 l_join_rec poa_dbi_util_pkg.poa_dbi_join_rec;
562 l_where_clause varchar2(2000);
563 l_mv varchar2(30);
564 l_view_by_value varchar2(30);
565 err_cde number;
566 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
567 begin
568 l_comparison_type := 'Y';
569 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
570 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
571
572 poa_dbi_sutil_pkg.process_parameters(p_param
573 ,l_view_by
574 ,l_view_by_col
575 ,l_view_by_value
576 ,l_comparison_type
577 ,l_xtd
578 ,l_as_of_date
579 ,l_prev_as_of_date
580 ,l_cur_suffix
581 ,l_nested_pattern
582 ,l_where_clause
583 ,l_mv
584 ,l_join_tbl
585 ,l_in_join_tbl
586 ,x_custom_output
587 ,p_trend => 'N'
588 ,p_func_area => 'PO'
589 ,p_version => '7.1'
590 ,p_role => 'VPP'
591 ,p_mv_set => 'REQMF');
592
593 poa_dbi_util_pkg.add_column(l_col_tbl
594 , 'fulfilled_amt_'||l_cur_suffix
595 , 'fulf_amt'
596 , p_grand_total => 'Y'
597 , p_prior_code => poa_dbi_util_pkg.both_priors
598 , p_to_date_type => 'RLX');
599
600 poa_dbi_util_pkg.add_column(l_col_tbl
601 , 'fulfilled_man_amt_'||l_cur_suffix
605 , p_to_date_type => 'RLX');
602 , 'fulf_man_amt'
603 , p_grand_total => 'Y'
604 , p_prior_code => poa_dbi_util_pkg.both_priors
606
607 poa_dbi_util_pkg.add_bucket_columns(
608 p_short_name => 'POA_DBI_FR_BUCKET'
609 ,p_col_tbl => l_col_tbl
610 ,p_col_name => 'fulfilled_amt_'||l_cur_suffix||'_age'
611 ,p_alias_name => 'fulf_amt_age'
612 ,x_bucket_rec => l_bucket_rec
613 ,p_grand_total => 'Y'
614 ,p_prior_code => poa_dbi_util_pkg.NO_PRIORS
615 ,p_to_date_type => 'RLX');
616
617 if(l_view_by = 'ITEM+POA_ITEMS') then
618 poa_dbi_util_pkg.add_column(l_col_tbl
619 ,'fulfilled_qty'
620 ,'fulf_qty'
621 , p_grand_total => 'Y'
622 , p_prior_code => poa_dbi_util_pkg.both_priors
623 , p_to_date_type => 'RLX');
624 end if;
625
626 l_query := get_amt_sql_sel_clause(l_view_by, l_view_by_col,l_bucket_rec) || ' from ';
627
628 l_query := l_query ||
629 poa_dbi_template_pkg.status_sql(l_mv,
630 l_where_clause,
631 l_join_tbl,
632 p_use_windowing => 'Y',
633 p_col_name => l_col_tbl,
634 p_use_grpid => 'N',
635 p_filter_where => get_amt_sql_filter_where(l_view_by),
636 p_in_join_tables => l_in_join_tbl);
637
638 x_custom_sql := l_query;
639 exception
640 when others then
641 err_msg := substr(sqlerrm,1,400);
642 end;
643
644 function get_amt_sql_sel_clause(p_view_by_dim in varchar2
645 , p_view_by_col in varchar2
646 , p_bucket_rec in BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE)
647 return varchar2 is
648 l_sel_clause varchar2(5000);
649 begin
650 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
651 ,'PO'
652 ,'7.1');
653 if(p_view_by_dim = 'ITEM+POA_ITEMS')
654 then
655 l_sel_clause := l_sel_clause || fnd_global.newline ||
656 'v.description POA_ATTRIBUTE3, --Description
657 v2.description POA_ATTRIBUTE4, --UOM
658 oset.POA_ATTRIBUTE5 POA_ATTRIBUTE5, --Quantity';
659 else
660 l_sel_clause := l_sel_clause || fnd_global.newline ||
661 'null POA_ATTRIBUTE3, --Description
662 null POA_ATTRIBUTE4, --UOM
663 null POA_ATTRIBUTE5, --Quantity';
664 end if;
665 l_sel_clause := l_sel_clause || fnd_global.newline ||
666 'oset.POA_MEASURE1 POA_MEASURE1, --Fulfilled Amount
667 oset.POA_PERCENT1 POA_PERCENT1, --fulf amt Change
668 oset.POA_MEASURE2 POA_MEASURE2, --Manual Amount
669 oset.POA_PERCENT2 POA_PERCENT2, --Manual Amount Rate
670 oset.POA_PERCENT3 POA_PERCENT3 --Man Amt Rate Change ' || fnd_global.newline
671 || poa_dbi_util_pkg.get_bucket_outer_query(
672 p_bucket_rec
673 , p_col_name => 'oset.POA_PERCENT4'
674 , p_alias_name => 'POA_PERCENT4'
675 , p_prefix => ''
676 , p_suffix => ''
677 , p_total_flag => 'N') ||',
678 oset.POA_MEASURE3 POA_MEASURE3, --Grand Total for Fulfilled Amount
679 oset.POA_PERCENT5 POA_PERCENT5, --Grand Total for fulf amt Change
680 oset.POA_MEASURE4 POA_MEASURE4, --Grand Total for Manual Amount
681 oset.POA_PERCENT6 POA_PERCENT6, --Grand Total for Manual Amount Rate
682 oset.POA_PERCENT7 POA_PERCENT7 --Grand tot for man amt rate change '
683 || fnd_global.newline ||
684 poa_dbi_util_pkg.get_bucket_outer_query(
685 p_bucket_rec
686 , p_col_name => 'oset.POA_PERCENT8'
687 , p_alias_name => 'POA_PERCENT8'
688 , p_prefix => ''
689 , p_suffix => ''
690 , p_total_flag => 'N') || fnd_global.newline;
691
692 l_sel_clause := l_sel_clause ||
693 poa_dbi_util_pkg.get_bucket_drill_url(
694 p_bucket_rec
695 , 'POA_ATTRIBUTE6'
696 , '''pFunctionName=POA_DBI_FR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=1&POA_BUCKET+REQUISITION_AGING='
697 , ''''
698 , p_add_bucket_num => 'Y') || ' ,'|| fnd_global.newline ;
699
700 l_sel_clause := l_sel_clause ||
701 '''pFunctionName=POA_DBI_FR_DTL&POA_ATTRIBUTE10=1&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' POA_ATTRIBUTE7,
702 ''pFunctionName=POA_DBI_FR_MAN_DTL&POA_ATTRIBUTE10=2&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' POA_ATTRIBUTE8';
703
704 l_sel_clause := l_sel_clause||fnd_global.newline||'from
705 ( select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col|| ')) - 1 rnk,' ||fnd_global.newline|| ' ' || p_view_by_col;
706
707 if(p_view_by_dim = 'ITEM+POA_ITEMS')
708 then
709 l_sel_clause := l_sel_clause ||',
710 base_uom,
711 POA_ATTRIBUTE5';
712 end if;
713
714 l_sel_clause := l_sel_clause || ',
715 POA_MEASURE1,
716 POA_PERCENT1,
717 POA_MEASURE2,
718 POA_PERCENT2,
719 POA_PERCENT3
720 '|| poa_dbi_util_pkg.get_bucket_outer_query(
721 p_bucket_rec
722 , p_col_name => 'POA_PERCENT4'
723 , p_alias_name => 'POA_PERCENT4'
724 , p_prefix => ''
725 , p_suffix => ''
726 , p_total_flag => 'N') ||',
727 POA_MEASURE3,
728 POA_PERCENT5,
729 POA_MEASURE4,
733 p_bucket_rec
730 POA_PERCENT6,
731 POA_PERCENT7
732 '|| poa_dbi_util_pkg.get_bucket_outer_query(
734 , p_col_name => 'POA_PERCENT8'
735 , p_alias_name => 'POA_PERCENT8'
736 , p_prefix => ''
737 , p_suffix => ''
738 , p_total_flag => 'N') ||'
739 from
740 ( select ' || p_view_by_col || ',';
741
742 if(p_view_by_dim = 'ITEM+POA_ITEMS')
743 then
744 l_sel_clause := l_sel_clause || '
745 base_uom,
746 nvl(c_fulf_qty,0) POA_ATTRIBUTE5,';
747 end if;
748
749 l_sel_clause := l_sel_clause || '
750 nvl(c_fulf_amt,0) POA_MEASURE1,
751 '||poa_dbi_util_pkg.change_clause('c_fulf_amt','p_fulf_amt','NP')||' POA_PERCENT1,
752 nvl(c_fulf_man_amt,0) POA_MEASURE2,
753 '||poa_dbi_util_pkg.rate_clause('c_fulf_man_amt','c_fulf_amt','P')||' POA_PERCENT2,
754 '||poa_dbi_util_pkg.change_clause(
755 poa_dbi_util_pkg.rate_clause('c_fulf_man_amt','c_fulf_amt','P'),
756 poa_dbi_util_pkg.rate_clause('p_fulf_man_amt','p_fulf_amt','P'),'P')||' POA_PERCENT3
757 '|| poa_dbi_util_pkg.get_bucket_outer_query(
758 p_bucket_rec
759 , p_col_name => 'c_fulf_amt_age'
760 , p_alias_name => 'POA_PERCENT4'
761 , p_prefix => 'nvl('
762 , p_suffix => ',0)'
763 , p_total_flag => 'N') ||',
764 nvl(c_fulf_amt_total,0) POA_MEASURE3,
765 '||poa_dbi_util_pkg.change_clause('c_fulf_amt_total','p_fulf_amt_total','NP')||' POA_PERCENT5,
766 nvl(c_fulf_man_amt_total,0) POA_MEASURE4,
767 '||poa_dbi_util_pkg.rate_clause('c_fulf_man_amt_total','c_fulf_amt_total','P')||' POA_PERCENT6,
768 '||poa_dbi_util_pkg.change_clause(
769 poa_dbi_util_pkg.rate_clause('c_fulf_man_amt_total','c_fulf_amt_total','P'),
770 poa_dbi_util_pkg.rate_clause('p_fulf_man_amt_total','p_fulf_amt_total','P'),'P')||' POA_PERCENT7
771 '|| poa_dbi_util_pkg.get_bucket_outer_query(
772 p_bucket_rec
773 , p_col_name => 'c_fulf_amt_age'
774 , p_alias_name => 'POA_PERCENT8'
775 , p_prefix => 'nvl('
776 , p_suffix => ',0)'
777 , p_total_flag => 'Y') ||'
778 ';
779
780 return l_sel_clause;
781 end;
782
783 function get_amt_sql_filter_where(p_view_by in varchar2) return varchar2
784 is
785 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
786 begin
787 l_col_tbl := poa_dbi_sutil_pkg.poa_dbi_filter_tbl();
788 l_col_tbl.extend;
789 l_col_tbl(1) := 'POA_MEASURE1';
790 l_col_tbl.extend;
791 l_col_tbl(2) := 'POA_PERCENT1';
792 l_col_tbl.extend;
793 l_col_tbl(3) := 'POA_MEASURE2';
794 l_col_tbl.extend;
795 l_col_tbl(4) := 'POA_PERCENT2';
796 l_col_tbl.extend;
797 l_col_tbl(5) := 'POA_PERCENT3';
798 if( p_view_by = 'ITEM+POA_ITEMS' ) then
799 l_col_tbl.extend;
800 l_col_tbl(6) := 'POA_ATTRIBUTE5';
801 end if;
802 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
803 end;
804
805 /*fulfilled Requisition Lines report*/
806 procedure req_lines_sql(p_param in bis_pmv_page_parameter_tbl
807 ,x_custom_sql out nocopy varchar2
808 ,x_custom_output out nocopy bis_query_attributes_tbl)
809 is
810 l_query varchar2(10000);
811 l_option number;
812 l_cur_suffix varchar2(2);
813 l_where_clause varchar2(2000);
814 l_in_join_tbl poa_dbi_util_pkg.poa_dbi_in_join_tbl;
815 l_in_join_tables varchar2(240) ;
816 l_xtd varchar2(10);
817 err_msg varchar2(100);
818 l_bucket varchar2(50);
819 l_bucket_where varchar2(440);
820 begin
821 poa_dbi_sutil_pkg.drill_process_parameters(p_param, l_cur_suffix, l_where_clause, l_in_join_tbl, 'PO', '7.1', 'VPP','REQMF');
822
823 for i in 1..p_param.count
824 loop
825 if (p_param(i).parameter_name = 'POA_ATTRIBUTE10') then
826 l_option := p_param(i).parameter_id;
827 end if;
828 if (p_param(i).parameter_name = 'POA_BUCKET+REQUISITION_AGING')
829 then
830 l_bucket := p_param(i).parameter_id;
831 end if;
832 end loop;
833
834 if(l_bucket is not null)
835 then
836 l_bucket_where := 'and (&RANGE_LOW is null or '
837 || 'greatest(0,(fact.req_fulfilled_date-fact.req_approved_date))'
838 || ' >= &RANGE_LOW)'
839 || fnd_global.newline
840 || 'and (&RANGE_HIGH is null or '
841 || 'greatest(0,(fact.req_fulfilled_date-fact.req_approved_date))'
842 || ' < &RANGE_HIGH)';
843
844 poa_dbi_util_pkg.bind_low_high(p_param
845 , 'POA_DBI_FR_BUCKET'
846 , 'POA_BUCKET+REQUISITION_AGING'
847 , '&RANGE_LOW'
848 , '&RANGE_HIGH'
849 , x_custom_output);
850 else
851 l_bucket_where := '';
852 end if;
853
854 l_where_clause := l_where_clause || fnd_global.newline || ' and fact.req_fulfilled_date is not null ';
855 if (l_option = 2) then --manual reqs
856 l_where_clause := l_where_clause || fnd_global.newline || ' and fact.po_creation_method = ''M'' ';
857 elsif (l_option = 3) then --past-expected-date reqs
858 l_where_clause := l_where_clause || fnd_global.newline || ' and fact.expected_date < fact.req_fulfilled_date ';
859 end if;
860
861 ---Begin MOAC changes
862 ---Following block is removed from custom_sql as l_where_clause is already
863 --- having a security clause
864 -- per_organization_list orgl
868 ---End MOAC changes
865 -- where
866 -- fact.org_id=orgl.organization_id
867 -- and orgl.security_profile_id=' || poa_dbi_util_pkg.get_sec_profile ||
869
870 x_custom_sql := '
871 select
872 prh.segment1 POA_MEASURE1, -- Requisition Number
873 prl.line_num POA_PERCENT1, -- Line Num
874 rorg.name POA_MEASURE5, -- Req Creation OU
875 substrb(perf.first_name,1,1) || ''. '' || perf.last_name POA_MEASURE2, -- Requestor Name
876 POA_PERCENT2 POA_PERCENT2, -- Req Approved Date
877 POA_MEASURE7 POA_MEASURE7, -- Processed Date
878 POA_MEASURE8 POA_MEASURE8, -- Fulfilled Date
879 POA_MEASURE9 POA_MEASURE9, -- Expected Date
880 item.value POA_PERCENT3, -- Item
881 supplier.value POA_PERCENT4, -- Supplier
882 i.POA_MEASURE3 POA_MEASURE3, -- Amount
883 decode(por.po_release_id,null,
884 poh.segment1,
885 poh.segment1||''-''||por.release_num) POA_PERCENT5, -- PO Number
886 porg.name POA_MEASURE4, -- PO OU
887 POA_MEASURE6 POA_MEASURE6, -- Grand Total for Amount
888 prh.requisition_header_id POA_ATTRIBUTE3,
889 prl.requisition_line_id POA_ATTRIBUTE4,
890 poh.po_header_id POA_ATTRIBUTE5,
891 por.po_release_id POA_ATTRIBUTE6
892 from (select (rank() over (&ORDER_BY_CLAUSE nulls last, req_header_id, req_line_id))-1 rnk,
893 req_header_id,
894 req_line_id,
895 req_creation_ou_id,
896 requester_id,
897 POA_PERCENT2 POA_PERCENT2,
898 POA_MEASURE7 POA_MEASURE7,
899 POA_MEASURE8 POA_MEASURE8,
900 POA_MEASURE9 POA_MEASURE9,
901 po_item_id,
902 supplier_id,
903 nvl(POA_MEASURE3,0) POA_MEASURE3,
904 nvl(POA_MEASURE6,0) POA_MEASURE6,
905 po_line_location_id,
906 po_creation_ou_id
907 from ( select
908 fact.req_header_id,
909 fact.req_line_id,
910 fact.req_creation_ou_id,
911 fact.requester_id,
912 fact.req_approved_date POA_PERCENT2,
913 fact.po_approved_date POA_MEASURE7,
914 fact.req_fulfilled_date POA_MEASURE8,
915 fact.expected_date POA_MEASURE9,
916 fact.po_item_id,
917 fact.supplier_id,
918 fact.line_amount_'||l_cur_suffix||' POA_MEASURE3,
919 sum(fact.line_amount_'||l_cur_suffix||') over() POA_MEASURE6,
920 fact.po_line_location_id,
921 fact.po_creation_ou_id
922 from
923 poa_dbi_req_f fact
924 where fact.req_fulfilled_date between &BIS_CURRENT_EFFECTIVE_START_DATE and &BIS_CURRENT_EFFECTIVE_END_DATE + (86399/86400)
925 and fact.include_in_ufr = ''Y'' '
926 || fnd_global.newline || l_where_clause
927 || fnd_global.newline || l_bucket_where
928 || fnd_global.newline ||
929 ')
930 ) i,
931 po_requisition_headers_all prh,
932 po_requisition_lines_all prl,
933 po_headers_all poh,
934 po_line_locations_all pll,
935 poa_items_v item,
936 poa_suppliers_v supplier,
937 per_all_people_f perf,
938 hr_all_organization_units_vl rorg,
939 hr_all_organization_units_vl porg,
940 po_releases_all por
941 where i.req_header_id=prh.requisition_header_id
942 and i.req_line_id=prl.requisition_line_id
943 and prh.requisition_header_id=prl.requisition_header_id
944 and i.po_item_id=item.id
945 and i.req_creation_ou_id=rorg.organization_id
946 and i.requester_id=perf.person_id
947 and sysdate between perf.effective_start_date and perf.effective_end_date
948 and i.supplier_id=supplier.id(+)
949 and i.po_line_location_id=pll.line_location_id(+)
950 and pll.po_header_id=poh.po_header_id(+)
951 and pll.po_header_id=por.po_header_id(+)
952 and pll.po_release_id=por.po_release_id(+)
953 and poh.org_id=porg.organization_id(+)
954 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
955 ORDER BY rnk ';
956
957 poa_dbi_util_pkg.get_custom_status_binds(x_custom_output);
958 poa_dbi_util_pkg.get_custom_rolling_binds(x_custom_output,l_xtd);
959 exception
960 when others then
961 err_msg := substr(sqlerrm,1,400);
962 end;
963
964 /*Fulfilled Requisitions Aging report*/
965 procedure req_age_sql(p_param in bis_pmv_page_parameter_tbl
966 ,x_custom_sql out nocopy varchar2
967 ,x_custom_output out nocopy bis_query_attributes_tbl)
968 is
969 err_msg varchar2(100);
970 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
971 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
972 l_query varchar2(10000);
973 l_view_by varchar2(120);
974 l_view_by_col varchar2(120);
975 l_as_of_date date;
976 l_prev_as_of_date date;
977 l_xtd varchar2(10);
978 l_comparison_type varchar2(1);
979 l_nested_pattern number;
980 l_cur_suffix varchar2(2);
981 l_in_join_tbl poa_dbi_util_pkg.poa_dbi_in_join_tbl;
982 l_join_rec poa_dbi_util_pkg.poa_dbi_join_rec;
983 l_where_clause varchar2(2000);
984 l_mv varchar2(30);
988 begin
985 l_view_by_value varchar2(30);
986 err_cde number;
987 l_bucket_rec bis_bucket_pub.bis_bucket_rec_type;
989 l_comparison_type := 'Y';
990 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
991 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
992
993 poa_dbi_sutil_pkg.process_parameters(p_param
994 ,l_view_by
995 ,l_view_by_col
996 ,l_view_by_value
997 ,l_comparison_type
998 ,l_xtd
999 ,l_as_of_date
1000 ,l_prev_as_of_date
1001 ,l_cur_suffix
1002 ,l_nested_pattern
1003 ,l_where_clause
1004 ,l_mv
1005 ,l_join_tbl
1006 ,l_in_join_tbl
1007 ,x_custom_output
1008 ,p_trend => 'N'
1009 ,p_func_area => 'PO'
1010 ,p_version => '7.1'
1011 ,p_role => 'VPP'
1012 ,p_mv_set => 'REQMF');
1013
1014 poa_dbi_util_pkg.add_column(l_col_tbl
1015 , 'days_to_fulfill'
1016 , 'days_fulf'
1017 , p_grand_total => 'Y'
1018 , p_prior_code => poa_dbi_util_pkg.both_priors
1019 , p_to_date_type => 'RLX');
1020
1021 poa_dbi_util_pkg.add_column(l_col_tbl
1022 , 'fulfilled_cnt_'||l_cur_suffix
1023 , 'fulf_cnt'
1024 , p_grand_total => 'Y'
1025 , p_prior_code => poa_dbi_util_pkg.both_priors
1026 , p_to_date_type => 'RLX');
1027
1028 poa_dbi_util_pkg.add_column(l_col_tbl
1029 , 'fulfilled_man_cnt_'||l_cur_suffix
1030 , 'fulf_man_cnt'
1031 , p_grand_total => 'Y'
1032 , p_prior_code => poa_dbi_util_pkg.both_priors
1033 , p_to_date_type => 'RLX');
1034
1035 if(l_view_by = 'ITEM+POA_ITEMS') then
1036 poa_dbi_util_pkg.add_column(l_col_tbl
1037 ,'fulfilled_qty'
1038 ,'fulf_qty'
1039 , p_grand_total => 'Y'
1040 , p_prior_code => poa_dbi_util_pkg.both_priors
1041 , p_to_date_type => 'RLX');
1042 end if;
1043
1044 poa_dbi_util_pkg.add_bucket_columns(
1045 p_short_name => 'POA_DBI_FR_BUCKET'
1046 ,p_col_tbl => l_col_tbl
1047 ,p_col_name => 'fulfilled_cnt_'||l_cur_suffix||'_age'
1048 ,p_alias_name => 'fulf_cnt_age'
1049 ,x_bucket_rec => l_bucket_rec
1050 ,p_grand_total => 'Y'
1051 ,p_prior_code => poa_dbi_util_pkg.NO_PRIORS
1052 ,p_to_date_type => 'RLX');
1053
1054 l_query := get_req_age_sel_clause(l_view_by, l_view_by_col,l_bucket_rec) || ' from ';
1055
1056 l_query := l_query ||
1057 poa_dbi_template_pkg.status_sql(
1058 l_mv,
1059 l_where_clause,
1060 l_join_tbl,
1061 p_use_windowing => 'Y',
1062 p_col_name => l_col_tbl,
1063 p_use_grpid => 'N',
1064 p_filter_where => get_req_age_filter_where(l_view_by),
1065 p_in_join_tables => l_in_join_tbl);
1066
1067 x_custom_sql := l_query;
1068
1069 exception
1070 when others then
1071 err_msg := substr(sqlerrm,1,400);
1072 end;
1073
1074 function get_req_age_sel_clause(p_view_by_dim in varchar2
1075 ,p_view_by_col in varchar2
1076 , p_bucket_rec in BIS_BUCKET_PUB.BIS_BUCKET_REC_TYPE)
1077 return varchar2 is
1078 l_sel_clause varchar2(5000);
1079 begin
1080 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(p_view_by_dim
1081 ,'PO'
1082 ,'7.1');
1083 if(p_view_by_dim = 'ITEM+POA_ITEMS')
1084 then
1085 l_sel_clause := l_sel_clause || fnd_global.newline ||
1086 'v.description POA_ATTRIBUTE3, --Description
1087 v2.description POA_ATTRIBUTE4, --UOM
1088 oset.POA_ATTRIBUTE5 POA_ATTRIBUTE5, --Quantity';
1089 else
1090 l_sel_clause := l_sel_clause || fnd_global.newline ||
1091 'null POA_ATTRIBUTE3, --Description
1092 null POA_ATTRIBUTE4, --UOM
1093 null POA_ATTRIBUTE5, --Quantity';
1094 end if;
1095 l_sel_clause := l_sel_clause || fnd_global.newline ||
1096 'oset.POA_MEASURE1 POA_MEASURE1, --Average Age (Days)
1097 oset.POA_PERCENT1 POA_PERCENT1, --Avg Age Change
1098 oset.POA_MEASURE2 POA_MEASURE2, --Fulfilled Lines
1099 oset.POA_PERCENT2 POA_PERCENT2, --Fulf Lines Change
1100 oset.POA_PERCENT3 POA_PERCENT3, --Manual Lines
1101 oset.POA_PERCENT4 POA_PERCENT4, --Manual Lines Rate
1102 oset.POA_MEASURE3 POA_MEASURE3 --Manual Lines Rate Change '
1103 || fnd_global.newline ||
1104 poa_dbi_util_pkg.get_bucket_outer_query(
1105 p_bucket_rec
1109 , p_suffix => ''
1106 , p_col_name => 'oset.POA_MEASURE4'
1107 , p_alias_name => 'POA_MEASURE4'
1108 , p_prefix => ''
1110 , p_total_flag => 'N') ||',
1111 oset.POA_MEASURE5 POA_MEASURE5, --Grand Total for Average Age (Days)
1112 oset.POA_PERCENT5 POA_PERCENT5, --Grand Total for avg age Change
1113 oset.POA_MEASURE6 POA_MEASURE6, --Grand Total for Fufilled Lines
1114 oset.POA_PERCENT6 POA_PERCENT6, --Grand Total for fulf lines Change
1115 oset.POA_PERCENT7 POA_PERCENT7, --Grand Total for Manual Lines
1116 oset.POA_PERCENT8 POA_PERCENT8, --Grand Total for Manual Lines Rate
1117 oset.POA_MEASURE7 POA_MEASURE7 -- grand tot for man lines rate change'
1118 || fnd_global.newline ||
1119 poa_dbi_util_pkg.get_bucket_outer_query(
1120 p_bucket_rec
1121 , p_col_name => 'oset.POA_MEASURE8'
1122 , p_alias_name => 'POA_MEASURE8'
1123 , p_prefix => ''
1124 , p_suffix => ''
1125 , p_total_flag => 'N') || fnd_global.newline;
1126
1127 l_sel_clause := l_sel_clause ||
1128 poa_dbi_util_pkg.get_bucket_drill_url(
1129 p_bucket_rec
1130 , 'POA_ATTRIBUTE6'
1131 , '''pFunctionName=POA_DBI_FR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=1&POA_BUCKET+REQUISITION_AGING='
1132 , ''''
1133 , p_add_bucket_num => 'Y') || ' ,'||fnd_global.newline;
1134
1135 l_sel_clause := l_sel_clause ||
1136 '''pFunctionName=POA_DBI_FR_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'' POA_ATTRIBUTE7,
1137 ''pFunctionName=POA_DBI_FR_MAN_DTL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&POA_ATTRIBUTE10=2'' POA_ATTRIBUTE8';
1138
1139 l_sel_clause := l_sel_clause || fnd_global.newline||'from
1140 ( select (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col|| ')) - 1 rnk,' ||fnd_global.newline|| ' ' || p_view_by_col;
1141
1142 if(p_view_by_dim = 'ITEM+POA_ITEMS')
1143 then
1144 l_sel_clause := l_sel_clause ||',
1145 base_uom,
1146 POA_ATTRIBUTE5';
1147 end if;
1148
1149 l_sel_clause := l_sel_clause || ',
1150 POA_MEASURE1,
1151 POA_PERCENT1,
1152 POA_MEASURE2,
1153 POA_PERCENT2,
1154 POA_PERCENT3,
1155 POA_PERCENT4,
1156 POA_MEASURE3
1157 '|| poa_dbi_util_pkg.get_bucket_outer_query(
1158 p_bucket_rec
1159 , p_col_name => 'POA_MEASURE4'
1160 , p_alias_name => 'POA_MEASURE4'
1161 , p_prefix => ''
1162 , p_suffix => ''
1163 , p_total_flag => 'N') ||',
1164 POA_MEASURE5,
1165 POA_PERCENT5,
1166 POA_MEASURE6,
1167 POA_PERCENT6,
1168 POA_PERCENT7,
1169 POA_PERCENT8,
1170 POA_MEASURE7
1171 '|| poa_dbi_util_pkg.get_bucket_outer_query(
1172 p_bucket_rec
1173 , p_col_name => 'POA_MEASURE8'
1174 , p_alias_name => 'POA_MEASURE8'
1175 , p_prefix => ''
1176 , p_suffix => ''
1177 , p_total_flag => 'N') ||'
1178 from
1179 ( select ' || p_view_by_col || ',';
1180
1181 if(p_view_by_dim = 'ITEM+POA_ITEMS')
1182 then
1183 l_sel_clause := l_sel_clause || '
1184 base_uom,
1185 nvl(c_fulf_qty,0) POA_ATTRIBUTE5,';
1186 end if;
1187
1188 l_sel_clause := l_sel_clause || '
1189 '||poa_dbi_util_pkg.rate_clause('c_days_fulf','c_fulf_cnt','NP')||' POA_MEASURE1,
1190 '||poa_dbi_util_pkg.change_clause(
1191 poa_dbi_util_pkg.rate_clause('c_days_fulf','c_fulf_cnt','NP'),
1192 poa_dbi_util_pkg.rate_clause('p_days_fulf','p_fulf_cnt','NP'),
1193 'P')||' POA_PERCENT1,
1194 nvl(c_fulf_cnt,0) POA_MEASURE2,
1195 '||poa_dbi_util_pkg.change_clause('c_fulf_cnt','p_fulf_cnt','NP')||' POA_PERCENT2,
1196 nvl(c_fulf_man_cnt,0) POA_PERCENT3,
1197 '||poa_dbi_util_pkg.rate_clause('c_fulf_man_cnt','c_fulf_cnt','P')||' POA_PERCENT4,
1198 '||poa_dbi_util_pkg.change_clause(
1199 poa_dbi_util_pkg.rate_clause('c_fulf_man_cnt','c_fulf_cnt','P'),
1200 poa_dbi_util_pkg.rate_clause('p_fulf_man_cnt','p_fulf_cnt','P'),
1201 'P')||' POA_MEASURE3
1202 '|| poa_dbi_util_pkg.get_bucket_outer_query(
1203 p_bucket_rec
1204 , p_col_name => 'c_fulf_cnt_age'
1205 , p_alias_name => 'POA_MEASURE4'
1206 , p_prefix => 'nvl('
1207 , p_suffix => ',0)'
1208 , p_total_flag => 'N') ||',
1209 '||poa_dbi_util_pkg.rate_clause('c_days_fulf_total','c_fulf_cnt_total','NP')||' POA_MEASURE5, '|| fnd_global.newline ||
1210 poa_dbi_util_pkg.change_clause(
1211 poa_dbi_util_pkg.rate_clause('c_days_fulf_total','c_fulf_cnt_total','NP'),
1212 poa_dbi_util_pkg.rate_clause('p_days_fulf_total','p_fulf_cnt_total','NP'),
1213 'P')|| ' POA_PERCENT5,
1214 nvl(c_fulf_cnt_total,0) POA_MEASURE6,
1215 '||poa_dbi_util_pkg.change_clause('c_fulf_cnt_total','p_fulf_cnt_total','NP')||' POA_PERCENT6,
1216 nvl(c_fulf_man_cnt_total,0) POA_PERCENT7,
1217 '||poa_dbi_util_pkg.rate_clause('c_fulf_man_cnt_total','c_fulf_cnt_total','P')||' POA_PERCENT8,
1218 '||
1219 poa_dbi_util_pkg.change_clause(
1220 poa_dbi_util_pkg.rate_clause('c_fulf_man_cnt_total','c_fulf_cnt_total','P'),
1221 poa_dbi_util_pkg.rate_clause('p_fulf_man_cnt_total','p_fulf_cnt_total','P'),
1222 'P')||' POA_MEASURE7
1223 '|| poa_dbi_util_pkg.get_bucket_outer_query(
1224 p_bucket_rec
1225 , p_col_name => 'c_fulf_cnt_age'
1226 , p_alias_name => 'POA_MEASURE8'
1227 , p_prefix => 'nvl('
1228 , p_suffix => ',0)'
1229 , p_total_flag => 'Y') ||'
1230 ';
1231
1232 return l_sel_clause;
1233 end;
1234
1235 function get_req_age_filter_where(p_view_by in varchar2) return varchar2
1236 is
1237 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
1238 begin
1242 l_col_tbl.extend;
1239 l_col_tbl := poa_dbi_sutil_pkg.poa_dbi_filter_tbl();
1240 l_col_tbl.extend;
1241 l_col_tbl(1) := 'POA_MEASURE1';
1243 l_col_tbl(2) := 'POA_PERCENT1';
1244 l_col_tbl.extend;
1245 l_col_tbl(3) := 'POA_MEASURE2';
1246 l_col_tbl.extend;
1247 l_col_tbl(4) := 'POA_PERCENT2';
1248 l_col_tbl.extend;
1249 l_col_tbl(5) := 'POA_PERCENT3';
1250 l_col_tbl.extend;
1251 l_col_tbl(6) := 'POA_PERCENT4';
1252 l_col_tbl.extend;
1253 l_col_tbl(7) := 'POA_MEASURE3';
1254 if( p_view_by = 'ITEM+POA_ITEMS' ) then
1255 l_col_tbl.extend;
1256 l_col_tbl(8) := 'POA_ATTRIBUTE5';
1257 end if;
1258 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
1259 end;
1260
1261 /*Fulfilled Lines Automation Trend report*/
1262 procedure trend_sql(p_param in bis_pmv_page_parameter_tbl
1263 ,x_custom_sql out nocopy varchar2
1264 ,x_custom_output out nocopy bis_query_attributes_tbl)
1265 is
1266 l_query varchar2(10000);
1267 l_view_by varchar2(120);
1268 l_view_by_col_name varchar2(120);
1269 l_view_by_value varchar2(30);
1270 l_as_of_date date;
1271 l_prev_as_of_date date;
1272 l_mv VARCHAR2(90);
1273 l_xtd varchar2(10);
1274 l_comparison_type varchar2(1);
1275 l_nested_pattern number;
1276 l_cur_suffix varchar2(2);
1277 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
1278 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
1279 l_in_join_tbl poa_dbi_util_pkg.poa_dbi_in_join_tbl;
1280 l_where_clause varchar2(2000);
1281 err_msg varchar2(100);
1282 err_cde number;
1283 begin
1284 l_comparison_type := 'Y';
1285 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
1286 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
1287
1288 poa_dbi_sutil_pkg.process_parameters(p_param,
1289 l_view_by,
1290 l_view_by_col_name,
1291 l_view_by_value,
1292 l_comparison_type,
1293 l_xtd,
1294 l_as_of_date,
1295 l_prev_as_of_date,
1296 l_cur_suffix,
1297 l_nested_pattern,
1298 l_where_clause,
1299 l_mv,
1300 l_join_tbl,
1301 l_in_join_tbl,
1302 x_custom_output,
1303 'Y',
1304 'PO',
1305 '7.1',
1306 'VPP',
1307 'REQMF');
1308
1309 poa_dbi_util_pkg.add_column(l_col_tbl
1310 , 'fulfilled_man_cnt_'||l_cur_suffix
1311 , 'fulf_man_cnt'
1312 , p_grand_total => 'N'
1313 , p_prior_code => poa_dbi_util_pkg.both_priors
1314 , p_to_date_type => 'RLX');
1315
1316 poa_dbi_util_pkg.add_column(l_col_tbl
1317 , 'fulfilled_cnt_'||l_cur_suffix
1318 , 'fulf_cnt'
1319 , p_grand_total => 'N'
1320 , p_prior_code => poa_dbi_util_pkg.both_priors
1321 , p_to_date_type => 'RLX');
1322
1323 l_query := get_trend_sel_clause || '
1324 from '
1325 || poa_dbi_template_pkg.trend_sql(
1326 l_xtd,
1327 l_comparison_type,
1328 l_mv,
1329 l_where_clause,
1330 l_col_tbl,
1331 p_use_grpid => 'N',
1332 p_in_join_tables => l_in_join_tbl);
1333
1334 x_custom_sql := l_query;
1335 exception
1336 when others then
1337 err_msg := substr(sqlerrm,1,400);
1338 end;
1339
1340 function get_trend_sel_clause return varchar2
1341 is
1342 l_sel_clause varchar2(4000);
1343 begin
1344 l_sel_clause := 'select cal.name VIEWBY,';
1345 l_sel_clause := l_sel_clause || '
1346 nvl(c_fulf_cnt,0) POA_MEASURE1, -- fulf lines
1347 ' || poa_dbi_util_pkg.change_clause('c_fulf_cnt','p_fulf_cnt','NP') || ' POA_PERCENT1, -- fulf lines change
1348 nvl(c_fulf_man_cnt,0) POA_MEASURE2, -- man lines
1349 ' || poa_dbi_util_pkg.change_clause('c_fulf_man_cnt','p_fulf_man_cnt') || ' POA_PERCENT2, -- man lines change
1350 nvl(c_fulf_cnt,0)-nvl(c_fulf_man_cnt,0) POA_MEASURE3, -- auto lines
1351 ' || poa_dbi_util_pkg.change_clause('c_fulf_cnt-c_fulf_man_cnt','p_fulf_cnt-p_fulf_man_cnt') || ' POA_PERCENT3 -- auto lines change';
1352 return l_sel_clause;
1353 end;
1354
1355 /*Fulfilled Requisitions Amount Trend*/
1356 procedure amt_trend_sql(p_param in bis_pmv_page_parameter_tbl
1357 ,x_custom_sql out nocopy varchar2
1358 ,x_custom_output out nocopy bis_query_attributes_tbl)
1359 is
1360 l_query varchar2(10000);
1361 l_view_by varchar2(120);
1362 l_view_by_col_name varchar2(120);
1363 l_view_by_value varchar2(30);
1364 l_as_of_date date;
1365 l_prev_as_of_date date;
1366 l_mv varchar2(90);
1367 l_xtd varchar2(10);
1371 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
1368 l_comparison_type varchar2(1);
1369 l_nested_pattern number;
1370 l_cur_suffix varchar2(2);
1372 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
1373 l_in_join_tbl poa_dbi_util_pkg.poa_dbi_in_join_tbl;
1374 l_where_clause varchar2(2000);
1375 err_msg varchar2(100);
1376 err_cde number;
1377 begin
1378 l_comparison_type := 'Y';
1379 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
1380 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
1381
1382 poa_dbi_sutil_pkg.process_parameters(p_param,
1383 l_view_by,
1384 l_view_by_col_name,
1385 l_view_by_value,
1386 l_comparison_type,
1387 l_xtd,
1388 l_as_of_date,
1389 l_prev_as_of_date,
1390 l_cur_suffix,
1391 l_nested_pattern,
1392 l_where_clause,
1393 l_mv,
1394 l_join_tbl,
1395 l_in_join_tbl,
1396 x_custom_output,
1397 'Y',
1398 'PO',
1399 '7.1',
1400 'VPP',
1401 'REQMF');
1402
1403 poa_dbi_util_pkg.add_column(l_col_tbl
1404 , 'fulfilled_man_amt_'||l_cur_suffix
1405 , 'fulf_man_amt'
1406 , p_grand_total => 'N'
1407 , p_prior_code => poa_dbi_util_pkg.both_priors
1408 , p_to_date_type => 'RLX');
1409
1410 poa_dbi_util_pkg.add_column(l_col_tbl
1411 , 'fulfilled_amt_'||l_cur_suffix
1412 , 'fulf_amt'
1413 , p_grand_total => 'N'
1414 , p_prior_code => poa_dbi_util_pkg.both_priors
1415 , p_to_date_type => 'RLX');
1416
1417 l_query := get_amt_trend_sel_clause || '
1418 from '
1419 || poa_dbi_template_pkg.trend_sql(
1420 l_xtd,
1421 l_comparison_type,
1422 l_mv,
1423 l_where_clause,
1424 l_col_tbl,
1425 p_use_grpid => 'N',
1426 p_in_join_tables => l_in_join_tbl);
1427
1428 x_custom_sql := l_query;
1429 exception
1430 when others then
1431 err_msg := substr(sqlerrm,1,400);
1432 end;
1433
1434 function get_amt_trend_sel_clause return varchar2
1435 is
1436 l_sel_clause varchar2(4000);
1437 begin
1438 l_sel_clause := 'select cal.name VIEWBY,';
1439 l_sel_clause := l_sel_clause || '
1440 nvl(p_fulf_amt,0) POA_MEASURE3, -- prior fulf amt
1441 nvl(c_fulf_amt,0) POA_MEASURE1, -- fulf amt
1442 ' || poa_dbi_util_pkg.change_clause('c_fulf_amt','p_fulf_amt','NP') || ' POA_PERCENT1, -- fulf amt change
1443 nvl(p_fulf_man_amt,0) POA_MEASURE4, -- prior man amt
1444 nvl(c_fulf_man_amt,0) POA_MEASURE2, -- man amt
1445 ' || poa_dbi_util_pkg.rate_clause('c_fulf_man_amt','c_fulf_amt', 'P') || ' POA_PERCENT2, -- man amt rate
1446 ' ||
1447 poa_dbi_util_pkg.change_clause(
1448 poa_dbi_util_pkg.rate_clause('c_fulf_man_amt','c_fulf_amt', 'P'),
1449 poa_dbi_util_pkg.rate_clause('p_fulf_man_amt','p_fulf_amt', 'P'),
1450 'P') || ' POA_PERCENT3 --man amt rate change';
1451 return l_sel_clause;
1452 end;
1453
1454 /*Percent Fulfilled Past Expected Date Trend report*/
1455 procedure ped_trend_sql(p_param in bis_pmv_page_parameter_tbl
1456 ,x_custom_sql out nocopy varchar2
1457 ,x_custom_output out nocopy bis_query_attributes_tbl)
1458 is
1459 l_query varchar2(10000);
1460 l_view_by varchar2(120);
1461 l_view_by_col_name varchar2(120);
1462 l_view_by_value varchar2(30);
1463 l_as_of_date date;
1464 l_prev_as_of_date date;
1465 l_mv varchar2(90);
1466 l_xtd varchar2(10);
1467 l_comparison_type varchar2(1);
1468 l_nested_pattern number;
1469 l_cur_suffix varchar2(2);
1470 l_col_tbl poa_dbi_util_pkg.poa_dbi_col_tbl;
1471 l_join_tbl poa_dbi_util_pkg.poa_dbi_join_tbl;
1472 l_in_join_tbl poa_dbi_util_pkg.poa_dbi_in_join_tbl;
1473 l_where_clause varchar2(2000);
1474 err_msg varchar2(100);
1475 err_cde number;
1476 begin
1477 l_comparison_type := 'Y';
1478 l_join_tbl := poa_dbi_util_pkg.poa_dbi_join_tbl();
1479 l_col_tbl := poa_dbi_util_pkg.poa_dbi_col_tbl();
1480
1481 poa_dbi_sutil_pkg.process_parameters(p_param,
1482 l_view_by,
1483 l_view_by_col_name,
1484 l_view_by_value,
1485 l_comparison_type,
1486 l_xtd,
1487 l_as_of_date,
1488 l_prev_as_of_date,
1489 l_cur_suffix,
1490 l_nested_pattern,
1494 l_in_join_tbl,
1491 l_where_clause,
1492 l_mv,
1493 l_join_tbl,
1495 x_custom_output,
1496 'Y',
1497 'PO',
1498 '7.1',
1499 'VPP',
1500 'REQMF');
1501
1502 poa_dbi_util_pkg.add_column(l_col_tbl
1503 , 'fulfilled_ped_cnt'
1504 , 'fulf_ped_cnt'
1505 , p_grand_total => 'N'
1506 , p_prior_code => poa_dbi_util_pkg.both_priors
1507 , p_to_date_type => 'RLX');
1508
1509 poa_dbi_util_pkg.add_column(l_col_tbl
1510 , 'fulfilled_cnt_'||l_cur_suffix
1511 , 'fulf_cnt'
1512 , p_grand_total => 'N'
1513 , p_prior_code => poa_dbi_util_pkg.both_priors
1514 , p_to_date_type => 'RLX');
1515
1516 l_query := get_ped_trend_sel_clause || '
1517 from '
1518 || poa_dbi_template_pkg.trend_sql(
1519 l_xtd,
1520 l_comparison_type,
1521 l_mv,
1522 l_where_clause,
1523 l_col_tbl,
1524 p_use_grpid => 'N',
1525 p_in_join_tables => l_in_join_tbl);
1526
1527 x_custom_sql := l_query;
1528 exception
1529 when others then
1530 err_msg := substr(sqlerrm,1,400);
1531 end;
1532
1533 function get_ped_trend_sel_clause return varchar2
1534 is
1535 l_sel_clause varchar2(4000);
1536 begin
1537 l_sel_clause := 'select cal.name VIEWBY,';
1538 l_sel_clause := l_sel_clause || '
1539 '
1540 || poa_dbi_util_pkg.rate_clause('p_fulf_ped_cnt','p_fulf_cnt','P') || ' POA_PERCENT3, -- prior percent ped rate
1541 '
1542 || poa_dbi_util_pkg.rate_clause('c_fulf_ped_cnt','c_fulf_cnt','P') || ' POA_PERCENT1, -- percent ped rate
1543 '
1544 || poa_dbi_util_pkg.change_clause(
1545 poa_dbi_util_pkg.rate_clause('c_fulf_ped_cnt','c_fulf_cnt','P'),
1546 poa_dbi_util_pkg.rate_clause('p_fulf_ped_cnt','p_fulf_cnt','P'),
1547 'P') || ' POA_PERCENT2 -- percent ped rate change';
1548 return l_sel_clause;
1549 end;
1550
1551 end poa_dbi_fr_pkg;