[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_REJ_PKG
Source
1 PACKAGE BODY poa_dbi_rej_pkg
2 /* $Header: poadbirejb.pls 120.1 2005/08/04 06:14:15 sriswami noship $ */
3 AS
4 -- Initial declarations
5 -- -----------------------------------------------------------------------
6 -- |---------------------< get_status_sel_clause >-----------------------|
7 -- -----------------------------------------------------------------------
8 FUNCTION get_status_sel_clause(p_view_by_dim IN VARCHAR2
9 ,p_view_by_col IN VARCHAR2
10 ,p_url IN VARCHAR2) RETURN VARCHAR2;
11 -- -----------------------------------------------------------------------
12 -- |---------------------< get_reason_sel_clause >-----------------------|
13 -- -----------------------------------------------------------------------
14 FUNCTION get_reason_sel_clause(p_view_by_dim IN VARCHAR2
15 ,p_view_by_col IN VARCHAR2
16 ,p_url IN VARCHAR2) RETURN VARCHAR2;
17
18 FUNCTION get_status_filter_where(p_view_by in VARCHAR2) return VARCHAR2;
19 FUNCTION get_reason_filter_where return VARCHAR2;
20
21
22
23 -- -----------------------------------------------------------------------
24 -- |-------------------------< status_sql >------------------------------|
25 -- -----------------------------------------------------------------------
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_query VARCHAR2(20000);
31 l_view_by VARCHAR2(120);
32 l_view_by_col VARCHAR2(120);
33 l_as_of_date DATE;
34 l_prev_as_of_date DATE;
35 l_xtd VARCHAR2(10);
36 l_comparison_type VARCHAR2(1) :='Y';
37 l_nested_pattern NUMBER;
38 l_cur_suffix VARCHAR2(2);
39 l_url VARCHAR2(300);
40 l_view_by_value VARCHAR2(30);
41 l_col_tbl POA_DBI_UTIL_PKG.POA_DBI_COL_TBL;
42 l_join_tbl POA_DBI_UTIL_PKG.POA_DBI_JOIN_TBL;
43 l_in_join_tbl POA_DBI_UTIL_PKG.POA_DBI_IN_JOIN_TBL;
44 l_where_clause VARCHAR2(2000);
45 l_mv VARCHAR2(30);
46 ERR_MSG VARCHAR2(100);
47 l_context_code VARCHAR2(10);
48 l_to_date_type VARCHAR2(10);
49 BEGIN
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 => p_param
54 ,P_VIEW_BY => l_view_by
55 ,P_VIEW_BY_COL_NAME => l_view_by_col
56 ,P_VIEW_BY_VALUE => l_view_by_value
57 ,P_COMPARISON_TYPE => l_comparison_type
58 ,P_XTD => l_xtd
59 ,P_AS_OF_DATE => l_as_of_date
60 ,P_PREV_AS_OF_DATE => l_prev_as_of_date
61 ,P_CUR_SUFFIX => l_cur_suffix
62 ,P_NESTED_PATTERN => l_nested_pattern
63 ,P_WHERE_CLAUSE => l_where_clause
64 ,P_MV => l_mv
65 ,P_JOIN_TBL => l_join_tbl
66 ,P_IN_JOIN_TBL => l_in_join_tbl
67 ,X_CUSTOM_OUTPUT => x_custom_output
68 ,P_TREND => 'N'
69 ,P_FUNC_AREA => 'PO'
70 ,P_VERSION => '6.0'
71 ,P_ROLE => 'COM'
72 ,P_MV_SET => 'RTX');
73 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
74 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
75 l_to_date_type := 'RLX';
76 ELSE
77 l_to_date_type := 'XTD';
78 END IF;
79
80 poa_dbi_util_pkg.add_column(l_col_tbl
81 ,'amt_reject_' || l_cur_suffix
82 ,'amt_reject'
83 ,p_to_date_type => l_to_date_type);
84
85 poa_dbi_util_pkg.add_column(l_col_tbl
86 ,'amt_receipt_' || l_cur_suffix
87 ,'amt_receipt'
88 ,p_to_date_type => l_to_date_type);
89
90 poa_dbi_util_pkg.add_column(l_col_tbl
91 ,'amt_receipt_reject_' || l_cur_suffix
92 ,'amt_receipt_reject'
93 ,p_to_date_type => l_to_date_type);
94
95 poa_dbi_util_pkg.add_column(l_col_tbl
96 ,'amt_inspected_' || l_cur_suffix
97 ,'amt_inspected'
98 ,p_to_date_type => l_to_date_type);
99
100 if(l_view_by = 'ITEM+POA_ITEMS') then
101 poa_dbi_util_pkg.add_column(l_col_tbl
102 ,'qty_reject'
103 ,'qty_reject'
104 ,p_to_date_type => l_to_date_type);
105
106 poa_dbi_util_pkg.add_column(l_col_tbl
107 ,'qty_receipt'
108 ,'qty_receipt'
109 ,p_to_date_type => l_to_date_type);
110
111 end if;
112
113 if(l_view_by='ITEM+ENI_ITEM_PO_CAT' or l_view_by='ITEM+POA_ITEMS') then
114 l_url := 'pFunctionName=POA_DBI_REJ_REASON_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=LOOKUP+RETURN_REASON&pParamIds=Y';
115 else
116 l_url := 'pFunctionName=POA_DBI_REJ_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=ITEM+ENI_ITEM_PO_CAT&pParamIds=Y';
117 end if;
118 --
119 l_query := get_status_sel_clause(l_view_by
120 ,l_view_by_col
121 ,l_url)
122 || ' FROM ' ||
123 poa_dbi_template_pkg.status_sql(p_fact_name => l_mv
124 ,p_where_clause => l_where_clause
125 ,p_join_tables => l_join_tbl
126 ,p_use_windowing => 'Y'
127 ,p_col_name => l_col_tbl
128 ,p_filter_where => get_status_filter_where(l_view_by)
129 , p_use_grpid => 'N'
130 ,p_in_join_tables => l_in_join_tbl);
131 --
132 x_custom_sql := l_query;
133 --
134 EXCEPTION
135 WHEN OTHERS THEN
136 ERR_MSG := SUBSTR(SQLERRM,1,400);
137 END status_sql;
138
139 FUNCTION get_status_filter_where(p_view_by in VARCHAR2) return VARCHAR2
140 IS
141 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
142 BEGIN
143 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
144 l_col_tbl.extend;
145 l_col_tbl(1) := 'POA_MEASURE3';
146 l_col_tbl.extend;
147 l_col_tbl(2) := 'POA_PERCENT1';
148 l_col_tbl.extend;
149 l_col_tbl(3) := 'POA_MEASURE4';
150 l_col_tbl.extend;
151 l_col_tbl(4) := 'POA_PERCENT2';
152 l_col_tbl.extend;
153 l_col_tbl(5) := 'POA_MEASURE5';
154 l_col_tbl.extend;
155 l_col_tbl(6) := 'POA_MEASURE7';
156 l_col_tbl.extend;
157 l_col_tbl(7) := 'POA_MEASURE8';
158 if(p_view_by= 'ITEM+POA_ITEMS') then
159 l_col_tbl.extend;
160 l_col_tbl(8) := 'POA_MEASURE1';
161 l_col_tbl.extend;
162 l_col_tbl(9) := 'POA_MEASURE2';
163 end if;
164 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
165
166 END;
167
168
169
170 -- -----------------------------------------------------------------------
171 -- |---------------------< get_status_sel_clause >-----------------------|
172 -- -----------------------------------------------------------------------
173 FUNCTION get_status_sel_clause(p_view_by_dim IN VARCHAR2
174 ,p_view_by_col IN VARCHAR2
175 ,p_url IN VARCHAR2) RETURN VARCHAR2 IS
176 l_sel_clause VARCHAR2(8000);
177 BEGIN
178 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(P_VIEWBY => p_view_by_dim
179 ,P_FUNC_AREA => 'PO'
180 ,P_VERSION => '6.0');
181 IF(p_view_by_dim = 'ITEM+POA_ITEMS') THEN
182 l_sel_clause := l_sel_clause || '
183 v.description POA_ATTRIBUTE1, --Description
184 v2.description POA_ATTRIBUTE2, --UOM
185 oset.POA_MEASURE1 POA_MEASURE1, --Reject Quantity
186 oset.POA_MEASURE2 POA_MEASURE2, --Receipt Quantity
187 ';
188 ELSE
189 l_sel_clause := l_sel_clause || ' null POA_ATTRIBUTE1, --Description
190 null POA_ATTRIBUTE2, --UOM
191 null POA_MEASURE1, --Reject Quantity
192 null POA_MEASURE2, --Receipt Quantity
193 ';
194 END IF;
195 --
196 l_sel_clause := l_sel_clause || '
197 oset.POA_MEASURE3 POA_MEASURE3, --Rejection Amount
198 oset.POA_PERCENT1 POA_PERCENT1, --Change
199 oset.POA_MEASURE4 POA_MEASURE4, --Receipt Amount
200 oset.POA_MEASURE5 POA_MEASURE5, --Receipt Rejection Amount
201 oset.POA_PERCENT2 POA_PERCENT2, --Rejection Rate
202 oset.POA_MEASURE7 POA_MEASURE7, --Receipt Inspected Amount
203 oset.POA_MEASURE8 POA_MEASURE8, --Receipt Inspected Rejection Rate
204 oset.POA_MEASURE9 POA_MEASURE9, --Grand Total for Rejection Amount
205 oset.POA_MEASURE10 POA_MEASURE10, --Grand Total for Change
206 oset.POA_MEASURE11 POA_MEASURE11, --Grand Total for Receipt Rejection Amount
207 oset.POA_MEASURE12 POA_MEASURE12, --Grand Total for Receipt Rejection Amount
208 oset.POA_MEASURE13 POA_MEASURE13, --Grand Total for Rejection Rate
209 oset.POA_MEASURE14 POA_MEASURE14, --Grand Total for Receipt Inspected Amount
210 oset.POA_MEASURE15 POA_MEASURE15, --Grand Total for Receipt Inspected Rejection Rate
211 ''' || p_url || ''' POA_ATTRIBUTE3,
212 oset.POA_PERCENT3 POA_PERCENT3, -- KPI - Compare TO measure
213 oset.POA_PERCENT4 POA_PERCENT4 -- KPI - Compare to Grand Total
214 FROM
215 (SELECT (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col;
216
217 if(p_view_by_dim = 'ITEM+POA_ITEMS') then
218 l_sel_clause := l_sel_clause || ', base_uom';
219 end if;
220
221 l_sel_clause := l_sel_clause || ')) - 1 rnk,' || p_view_by_col;
222 --
223 IF(p_view_by_dim = 'ITEM+POA_ITEMS') THEN
224 l_sel_clause := l_sel_clause || ',
225 base_uom,
226 POA_MEASURE1,
227 POA_MEASURE2';
228 END IF;
229 --
230 l_sel_clause := l_sel_clause || ',POA_MEASURE3,POA_PERCENT1,
231 POA_MEASURE4,POA_MEASURE5,
232 POA_PERCENT2,POA_MEASURE7,
233 POA_MEASURE8,POA_MEASURE9,
234 POA_MEASURE10,POA_MEASURE11,
235 POA_MEASURE12,POA_MEASURE13,
236 POA_MEASURE14,POA_MEASURE15,
237 POA_PERCENT3, POA_PERCENT4
238 FROM (SELECT ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ';
239 --
240 IF(p_view_by_dim = 'ITEM+POA_ITEMS') THEN
241 l_sel_clause := l_sel_clause || ' base_uom,
242 decode(base_uom,null,to_number(null),nvl(c_qty_reject,0)) POA_MEASURE1,
243 decode(base_uom,null,to_number(null),nvl(c_qty_receipt,0)) POA_MEASURE2, ';
244 END IF;
245 --
246 l_sel_clause := l_sel_clause || ' nvl(c_amt_reject,0) POA_MEASURE3,
247 '|| poa_dbi_util_pkg.change_clause('c_amt_reject','p_amt_reject') || ' POA_PERCENT1,
248 '|| 'nvl(c_amt_receipt,0) POA_MEASURE4,
249 nvl(c_amt_receipt_reject,0) POA_MEASURE5,
250 '|| poa_dbi_util_pkg.rate_clause('c_amt_receipt_reject','c_amt_receipt') || ' POA_PERCENT2,
251 '||'nvl(c_amt_inspected,0) POA_MEASURE7,
252 '|| poa_dbi_util_pkg.rate_clause('c_amt_reject','c_amt_inspected') || ' POA_MEASURE8,
253 '|| 'nvl(c_amt_reject_total,0) POA_MEASURE9,
254 '|| poa_dbi_util_pkg.change_clause('c_amt_reject_total','p_amt_reject_total') || ' POA_MEASURE10,
255 '||' nvl(c_amt_receipt_total,0) POA_MEASURE11,
256 nvl(c_amt_receipt_reject_total,0) POA_MEASURE12,
257 '|| poa_dbi_util_pkg.rate_clause('c_amt_receipt_reject_total','c_amt_receipt_total') || ' POA_MEASURE13,
258 '|| ' nvl(c_amt_inspected_total,0) POA_MEASURE14,
262 ';
259 '|| poa_dbi_util_pkg.rate_clause('c_amt_reject_total','c_amt_inspected_total') || ' POA_MEASURE15,
260 '|| poa_dbi_util_pkg.rate_clause('p_amt_receipt_reject','p_amt_receipt') || ' POA_PERCENT3,
261 '|| poa_dbi_util_pkg.rate_clause('p_amt_receipt_reject_total','p_amt_receipt_total') || ' POA_PERCENT4
263 --
264 RETURN l_sel_clause;
265 END get_status_sel_clause;
266
267
268
269 -- -----------------------------------------------------------------------
270 -- |---------------------------< rej_rsn_sql >---------------------------|
271 -- -----------------------------------------------------------------------
272 PROCEDURE rej_rsn_sql(p_param IN BIS_PMV_PAGE_PARAMETER_TBL
273 ,x_custom_sql OUT NOCOPY VARCHAR2
274 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
275 IS
276 l_query VARCHAR2(10000);
277 l_view_by VARCHAR2(120);
278 l_view_by_col VARCHAR2(120);
279 l_as_of_date DATE;
280 l_prev_as_of_date DATE;
281 l_xtd VARCHAR2(10);
282 l_comparison_type VARCHAR2(1) :='Y';
283 l_nested_pattern NUMBER;
284 l_cur_suffix VARCHAR2(2);
285 l_url VARCHAR2(300);
286 l_view_by_value VARCHAR2(30);
287 l_where_clause VARCHAR2(2000);
288 l_mv VARCHAR2(30);
289 l_col_tbl POA_DBI_UTIL_PKG.POA_DBI_COL_TBL;
290 l_join_tbl POA_DBI_UTIL_PKG.POA_DBI_JOIN_TBL;
291 l_in_join_tbl POA_DBI_UTIL_PKG.POA_DBI_IN_JOIN_TBL;
292 ERR_MSG VARCHAR2(100);
293 l_context_code VARCHAR2(10);
294 l_to_date_type VARCHAR2(10);
295 BEGIN
296 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
297 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
298 --
299 poa_dbi_sutil_pkg.process_parameters(p_param => p_param
300 ,p_view_by => l_view_by
301 ,p_view_by_col_name => l_view_by_col
302 ,p_view_by_value => l_view_by_value
303 ,p_comparison_type => l_comparison_type
304 ,p_xtd => l_xtd
305 ,p_as_of_date => l_as_of_date
306 ,p_prev_as_of_date => l_prev_as_of_date
307 ,p_cur_suffix => l_cur_suffix
308 ,p_nested_pattern => l_nested_pattern
309 ,p_where_clause => l_where_clause
310 ,p_mv => l_mv
311 ,p_join_tbl => l_join_tbl
312 ,p_in_join_tbl => l_in_join_tbl
313 ,x_custom_output => x_custom_output
314 ,p_trend => 'N'
315 ,p_func_area => 'PO'
316 ,p_version => '6.0'
317 ,p_role => 'COM'
318 ,p_mv_set => 'RTX');
319 --
320 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
321 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
322 l_to_date_type := 'RLX';
323 ELSE
324 l_to_date_type := 'XTD';
325 END IF;
326 poa_dbi_util_pkg.add_column(l_col_tbl,'amt_reject_' || l_cur_suffix,'amt_reject',p_to_date_type => l_to_date_type);
327 poa_dbi_util_pkg.add_column(l_col_tbl,'amt_receipt_reject_' || l_cur_suffix,'amt_receipt_reject',p_to_date_type => l_to_date_type);
328 l_query := get_reason_sel_clause(P_VIEW_BY_DIM => l_view_by
329 ,P_VIEW_BY_COL => l_view_by_col
330 ,P_URL => null)
331 || ' FROM ' ||
332 poa_dbi_template_pkg.status_sql(p_fact_name => l_mv
333 ,p_where_clause => l_where_clause
334 ,p_join_tables => l_join_tbl
335 ,p_use_windowing => 'Y'
336 ,p_col_name => l_col_tbl
337 ,p_filter_where => get_reason_filter_where
338 , p_use_grpid => 'N'
339 ,p_in_join_tables => l_in_join_tbl);
340 x_custom_sql := l_query;
341 --
342 EXCEPTION
343 WHEN OTHERS THEN
344 ERR_MSG := SUBSTR(SQLERRM,1,400);
345 END rej_rsn_sql;
346
347
348 FUNCTION get_reason_filter_where return VARCHAR2
349 IS
350 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
351 BEGIN
352 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
353 l_col_tbl.extend;
354 l_col_tbl(1) := 'POA_MEASURE1';
355 l_col_tbl.extend;
356 l_col_tbl(2) := 'POA_PERCENT1';
357 l_col_tbl.extend;
358 l_col_tbl(3) := 'POA_PERCENT2';
359
360 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
361
362 END;
363
364
365 -- -----------------------------------------------------------------------
366 -- |---------------------< get_reason_sel_clause >-----------------------|
367 -- -----------------------------------------------------------------------
368 FUNCTION get_reason_sel_clause(p_view_by_dim IN VARCHAR2
369 ,p_view_by_col IN VARCHAR2
370 ,p_url IN VARCHAR2) RETURN VARCHAR2
371 IS
372 l_sel_clause VARCHAR2(4000);
373 BEGIN
374 l_sel_clause := poa_dbi_sutil_pkg.get_viewby_select_clause(P_VIEWBY => p_view_by_dim
375 ,P_FUNC_AREA => 'PO'
376 ,P_VERSION => '6.0');
377 --
378 l_sel_clause := l_sel_clause || 'oset.reason_id POA_ATTRIBUTE1,' ;
379 --
380 l_sel_clause := l_sel_clause || '
381 oset.POA_MEASURE1 POA_MEASURE1, --Rejection Amount
382 oset.POA_PERCENT1 POA_PERCENT1, --Change
383 oset.POA_PERCENT2 POA_PERCENT2, --Percent of Total
384 oset.POA_MEASURE2 POA_MEASURE2, --Grand Total for Rejection Amount
385 oset.POA_MEASURE3 POA_MEASURE3, --Grand Total for Change
386 oset.POA_MEASURE4 POA_MEASURE4, --Grand Total for Percent of Total
387 oset.POA_MEASURE1 POA_MEASURE5, --Rejection Amount
388 oset.POA_MEASURE2 POA_MEASURE6 --Grand Total for Rejection Amount for Pie
389 FROM (SELECT (rank() over(&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col || ')) - 1 rnk,' || p_view_by_col || ',';
390 --
391 l_sel_clause := l_sel_clause || '
392 reason_id POA_ATTRIBUTE1,
393 POA_MEASURE1,POA_PERCENT1,
394 POA_PERCENT2,POA_MEASURE2,
395 POA_MEASURE3,POA_MEASURE4
396 FROM (SELECT ' || p_view_by_col || ',' || p_view_by_col || ' VIEWBY,' || p_view_by_col || ' VIEWBYID, ' || p_view_by_col || ' POA_ATTRIBUTE1,';
397 l_sel_clause := l_sel_clause || '
398 nvl(c_amt_reject,0) POA_MEASURE1,
399 ' || poa_dbi_util_pkg.change_clause('c_amt_reject','p_amt_reject') || ' POA_PERCENT1,
400 ' || poa_dbi_util_pkg.rate_clause('c_amt_reject','c_amt_reject_total') || 'POA_PERCENT2,
401 ' || ' c_amt_reject_total POA_MEASURE2,
402 ' || poa_dbi_util_pkg.change_clause('c_amt_reject_total','p_amt_reject_total') || ' POA_MEASURE3,
403 ' || poa_dbi_util_pkg.rate_clause('c_amt_reject_total','c_amt_reject_total') || ' POA_MEASURE4
404 ';
405 RETURN l_sel_clause;
406 END get_reason_sel_clause;
407 --
408 END poa_dbi_rej_pkg;