[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_APM_PKG
Source
1 PACKAGE BODY poa_dbi_apm_pkg
2 /* $Header: poadbiapmib.pls 120.1 2005/08/04 06:11:51 sriswami noship $ */
3
4 AS
5
6 FUNCTION get_status_sel_clause(p_view_by_col_name in VARCHAR2, p_url in VARCHAR2) return VARCHAR2;
7 FUNCTION get_trend_sel_clause return VARCHAR2;
8 FUNCTION get_status_filter_where return VARCHAR2;
9 FUNCTION get_kpi_filter_where return VARCHAR2;
10
11 ----
12 ---- public methods
13 ----
14
15 PROCEDURE status_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
16 x_custom_sql OUT NOCOPY VARCHAR2,
17 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
18 IS
19 l_query varchar2(4000);
20 l_view_by varchar2(120);
21 l_view_by_col varchar2(120);
22 l_as_of_date date;
23 l_prev_as_of_date date;
24 l_xtd varchar2(10);
25 l_comparison_type varchar2(1) := 'Y';
26 l_nested_pattern number;
27 l_cur_suffix varchar2(2);
28 l_url varchar2(300);
29 l_custom_sql varchar2(4000);
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_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
33 l_where_clause VARCHAR2(2000);
34 l_view_by_value VARCHAR2(100);
35 l_mv VARCHAR2(30);
36 l_context_code VARCHAR2(30);
37 l_to_date_type VARCHAR2(30);
38 BEGIN
39 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
40 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
41
42 poa_dbi_sutil_pkg.process_parameters(p_param,l_view_by,l_view_by_col,l_view_by_value, l_comparison_type,
43 l_xtd, l_as_of_date, l_prev_as_of_date, l_cur_suffix, l_nested_pattern,
44 l_where_clause, l_mv, l_join_tbl, l_in_join_tbl,
45 x_custom_output,'N','AP','5.0','VPP','MID');
46
47 /* Get the Context Code of the Dashboard and set the Period Type to be XTD or Rolling */
48 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
49 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
50 l_to_date_type := 'RLX';
51 ELSE
52 l_to_date_type := 'XTD';
53 END IF;
54
55 poa_dbi_util_pkg.add_column(l_col_tbl, 'manual_dist_count', 'manual_dist_count',p_to_date_type => l_to_date_type);
56 poa_dbi_util_pkg.add_column(l_col_tbl, 'dist_count', 'dist_count',p_to_date_type => l_to_date_type);
57
58 if((l_view_by = 'HRI_PERSON+HRI_PER') and (l_view_by_value is not null) and (instr(l_view_by_value,',') = 0)) then
59 l_url := null;
60 else
61 l_url := 'pFunctionName=POA_DBI_APM_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_ID&VIEW_BY=HRI_PERSON+HRI_PER&pParamIds=Y';
62 end if;
63
64 l_query := get_status_sel_clause(l_view_by_col, l_url) || ' from
65 '|| poa_dbi_template_pkg.status_sql(l_mv,
66 l_where_clause,
67 l_join_tbl,
68 p_use_windowing => 'Y',
69 p_col_name => l_col_tbl,
70 p_use_grpid => 'N',
71 p_filter_where => get_status_filter_where,
72 p_in_join_tables => l_in_join_tbl);
73 x_custom_sql := l_query;
74 END;
75
76
77 PROCEDURE trend_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
78 x_custom_sql OUT NOCOPY VARCHAR2,
79 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
80 IS
81 l_query varchar2(32767);
82 l_view_by varchar2(120);
83 l_view_by_col varchar2(120);
84 l_as_of_date date;
85 l_prev_as_of_date date;
86 l_xtd varchar2(10);
87 l_comparison_type varchar2(1) := 'Y';
88 l_nested_pattern number;
89 l_cur_suffix varchar2(2);
90 l_custom_sql varchar2(4000);
91 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
92 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
93 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
94 l_mv VARCHAR2(30);
95 l_where_clause VARCHAR2(4000);
96 l_view_by_value VARCHAR2(100);
97 l_context_code VARCHAR2(30);
98 l_to_date_type VARCHAR2(30);
99 BEGIN
100 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
101 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
102
103 poa_dbi_sutil_pkg.process_parameters(p_param,l_view_by,l_view_by_col,l_view_by_value,l_comparison_type,
104 l_xtd, l_as_of_date, l_prev_as_of_date, l_cur_suffix, l_nested_pattern,
105 l_where_clause, l_mv, l_join_tbl, l_in_join_tbl,x_custom_output,
106 'Y','AP','5.0','VPP','MID');
107 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
108 /* Get the Context Code of the Dashboard and set the Period Type to be XTD or Rolling */
109 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
110 l_to_date_type := 'RLX';
111 ELSE
112 l_to_date_type := 'XTD';
113 END IF;
114 poa_dbi_util_pkg.add_column(l_col_tbl, 'manual_dist_count', 'manual_dist_count', 'N',p_to_date_type => l_to_date_type);
115 poa_dbi_util_pkg.add_column(l_col_tbl, 'dist_count', 'dist_count', 'N',p_to_date_type => l_to_date_type);
116
117 l_query := get_trend_sel_clause || ' from
118 '|| poa_dbi_template_pkg.trend_sql(
119 l_xtd,
120 l_comparison_type,
121 l_mv,
122 l_where_clause,
123 l_col_tbl,
124 p_use_grpid => 'N',
125 p_in_join_tables => l_in_join_tbl);
126 x_custom_sql := l_query;
127 END;
128
129
130 PROCEDURE kpi_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
131 x_custom_sql OUT NOCOPY VARCHAR2,
132 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
133 IS
134 l_query varchar2(4000);
135 l_view_by varchar2(120);
136 l_view_by_col varchar2(120);
137 l_as_of_date date;
138 l_prev_as_of_date date;
139 l_xtd varchar2(10);
140 l_comparison_type varchar2(1) := 'Y';
141 l_nested_pattern number;
142 l_org_where varchar2(500);
143 l_cur_suffix varchar2(2);
144 l_custom_sql varchar2(4000);
145 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
146 l_where_clause varchar2(1000);
147 l_mv varchar2(30);
148 l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
149 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
150 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
151 l_custom_rec BIS_QUERY_ATTRIBUTES;
152 l_view_by_value VARCHAR2(100);
153 l_context_code VARCHAR2(30);
154 l_to_date_type VARCHAR2(30);
155 BEGIN
156 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
157 l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
158
159 poa_dbi_sutil_pkg.process_parameters(p_param,l_view_by,l_view_by_col,l_view_by_value,l_comparison_type,
160 l_xtd, l_as_of_date, l_prev_as_of_date, l_cur_suffix, l_nested_pattern,
161 l_where_clause, l_mv, l_join_tbl, l_in_join_tbl,x_custom_output,
162 'N','AP', '5.0', 'VPP','MID');
163 /* Get the Context Code of the Dashboard and set the Period Type to be XTD or Rolling */
164 l_context_code := poa_dbi_sutil_pkg.get_sec_context(p_param);
165 IF(l_context_code = 'OU' or l_context_code = 'SUPPLIER') THEN
166 l_to_date_type := 'RLX';
167 ELSE
168 l_to_date_type := 'XTD';
169 END IF;
170 poa_dbi_util_pkg.add_column(l_col_tbl, 'manual_dist_count', 'manual_dist_count',p_to_date_type => l_to_date_type);
171 poa_dbi_util_pkg.add_column(l_col_tbl, 'dist_count', 'dist_count',p_to_date_type => l_to_date_type);
172
173 l_query :=
174 'select v.value VIEWBY,
175 v.id VIEWBYID,
176 oset.POA_PERCENT1 POA_PERCENT1,
177 oset.POA_PERCENT2 POA_PERCENT2,
178 oset.POA_MEASURE1 POA_MEASURE1,
179 oset.POA_MEASURE2 POA_MEASURE2
180 from
181 (select * from
182 (select ' || l_view_by_col || ',
183 ' || poa_dbi_util_pkg.rate_clause('c_manual_dist_count','c_dist_count') || ' POA_PERCENT1,
184 ' || poa_dbi_util_pkg.rate_clause('p_manual_dist_count','p_dist_count') || ' POA_PERCENT2,
185 ' || poa_dbi_util_pkg.rate_clause('c_manual_dist_count_total','c_dist_count_total') || ' POA_MEASURE1,
186 ' || poa_dbi_util_pkg.rate_clause('p_manual_dist_count_total','p_dist_count_total') || ' POA_MEASURE2
187 from
188 ' || poa_dbi_template_pkg.status_sql(l_mv,
189 l_where_clause,
190 l_join_tbl,
191 p_use_windowing => 'N',
192 p_col_name => l_col_tbl,
193 p_use_grpid => 'N',
194 p_filter_where => get_kpi_filter_where,
195 p_in_join_tables => l_in_join_tbl);
196
197 x_custom_sql := l_query;
198 END;
199
200
201 FUNCTION get_kpi_filter_where return VARCHAR2
202 IS
203 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
204 BEGIN
205 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
206 l_col_tbl.extend;
207 l_col_tbl(1) := 'POA_PERCENT2';
208 l_col_tbl.extend;
209 l_col_tbl(2) := 'POA_PERCENT1';
210
211 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
212
213 END;
214
215
216
217 FUNCTION get_status_filter_where return VARCHAR2
218 IS
219 l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
220 BEGIN
221 l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
222 l_col_tbl.extend;
223 l_col_tbl(1) := 'POA_MEASURE1';
224 l_col_tbl.extend;
225 l_col_tbl(2) := 'POA_PERCENT2';
226 l_col_tbl.extend;
227 l_col_tbl(3) := 'POA_PERCENT1';
228 l_col_tbl.extend;
229 l_col_tbl(4) := 'POA_MEASURE3';
230
231 return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
232
233 END;
234
235
236
237 FUNCTION get_status_sel_clause(p_view_by_col_name in VARCHAR2, p_url in VARCHAR2) return VARCHAR2
238 IS
239 l_sel_clause varchar2(4000);
240
241 BEGIN
242
243 l_sel_clause := 'select ' || case p_view_by_col_name
244 when 'inv_d_created_by' then 'decode(v.value, null, fnd_message.get_string(''POA'', ''POA_DBI_APL_UNASSIGNED''), v.value) '
245 else 'v.value ' end ||
246 'VIEWBY,
247 decode(v.id, null, -1, v.id) VIEWBYID,
248 oset.POA_MEASURE1 POA_MEASURE1, --Manual Distributions
249 oset.POA_PERCENT1 POA_PERCENT1, --Change
250 oset.POA_MEASURE3 POA_MEASURE3, --Distributions
251 oset.POA_PERCENT2 POA_PERCENT2, --Manual Distribution Rate
252 oset.POA_MEASURE4 POA_MEASURE4, --Total Manual Distributions
253 oset.POA_MEASURE5 POA_MEASURE5, --Total Distributions
254 oset.POA_MEASURE6 POA_MEASURE6, --Total Change
255 oset.POA_MEASURE7 POA_MEASURE7, --Total Manual Distribution Rate
256 ''' || p_url || ''' POA_MEASURE8
257 from
258 (select (rank() over
259 (&ORDER_BY_CLAUSE nulls last, '||p_view_by_col_name||')) - 1 rnk, '||
260 p_view_by_col_name ||',
261 POA_MEASURE1, POA_PERCENT1, POA_MEASURE3, POA_PERCENT2, POA_MEASURE4,
262 POA_MEASURE5, POA_MEASURE6, POA_MEASURE7 from
263 (select '||p_view_by_col_name||', '||
264 p_view_by_col_name ||' VIEWBY,
265 nvl(c_manual_dist_count,0) POA_MEASURE1,
266 ' || poa_dbi_util_pkg.change_clause('c_manual_dist_count','p_manual_dist_count') || ' POA_PERCENT1,
267 nvl(c_dist_count,0) POA_MEASURE3,
268 ' || poa_dbi_util_pkg.rate_clause('c_manual_dist_count','c_dist_count') || ' POA_PERCENT2,
269 nvl(c_manual_dist_count_total,0) POA_MEASURE4,
270 nvl(c_dist_count_total,0) POA_MEASURE5,
271 ' || poa_dbi_util_pkg.change_clause('c_manual_dist_count_total','p_manual_dist_count_total') || ' POA_MEASURE6,
272 ' || poa_dbi_util_pkg.rate_clause('c_manual_dist_count_total','c_dist_count_total') || ' POA_MEASURE7';
273 return l_sel_clause;
274
275 end;
276
277 FUNCTION get_trend_sel_clause return VARCHAR2
278 IS
279 l_sel_clause varchar2(4000);
280
281 BEGIN
282
283 l_sel_clause :=
284 'select cal.name VIEWBY,
285 nvl(p_manual_dist_count,0) POA_MEASURE1,
286 nvl(c_manual_dist_count,0) POA_MEASURE2,
287 nvl(p_manual_dist_count,0) POA_PERCENT1,
288 ' || poa_dbi_util_pkg.change_clause('c_manual_dist_count','p_manual_dist_count') || ' POA_PERCENT3,
289 nvl(c_manual_dist_count,0) POA_PERCENT2';
290
291 return l_sel_clause;
292
293 END;
294
295
296 FUNCTION get_view_by_col(view_by varchar2) return varchar2
297 is
298 BEGIN
299 return (case view_by
300 when 'ORGANIZATION+FII_OPERATING_UNITS' then 'org_id'
301 when 'HRI_PERSON+HRI_PER' then 'inv_d_created_by'
302 when 'SUPPLIER+POA_SUPPLIERS' then 'supplier_id'
303 when 'SUPPLIER+POA_SUPPLIER_SITES' then 'supplier_site_id'
304 else ''
305 end);
306 END;
307
308 end poa_dbi_apm_pkg;