eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.POA_DBI_PCL_PKG

Source


1 PACKAGE BODY poa_dbi_pcl_pkg
2 /* $Header: poadbipclb.pls 120.0 2005/06/01 14:39:15 appldev noship $ */
3 
4 AS
5 
6   FUNCTION get_status_sel_clause(p_view_by_col 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 
10   FUNCTION get_kpi_filter_where return VARCHAR2;
11 
12   PROCEDURE status_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
13                       x_custom_sql  OUT NOCOPY VARCHAR2,
14                       x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
15   IS
16 	l_query varchar2(4000);
17         l_view_by varchar2(120);
18 	l_view_by_col varchar2(120);
19         l_as_of_date date;
20         l_prev_as_of_date date;
21         l_xtd varchar2(10);
22         l_comparison_type varchar2(1) := 'Y';
23         l_nested_pattern number;
24         l_cur_suffix varchar2(2);
25         l_url varchar2(300);
26         l_custom_sql varchar2(4000);
27         l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
28 	l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
29 	l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
30 	l_where_clause VARCHAR2(2000);
31 	l_view_by_value VARCHAR2(100);
32 	l_mv VARCHAR2(30);
33 
34   BEGIN
35    l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
36    l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
37 
38   poa_dbi_sutil_pkg.process_parameters(p_param,l_view_by,l_view_by_col,l_view_by_value, l_comparison_type, l_xtd, l_as_of_date, l_prev_as_of_date, l_cur_suffix, l_nested_pattern, l_where_clause, l_mv, l_join_tbl, l_in_join_tbl,
39 x_custom_output,
40                                        'N','PO', '5.0', 'VPP','POD');
41 
42    poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
43    poa_dbi_util_pkg.add_column(l_col_tbl, 'p_savings_amt_' || l_cur_suffix, 'p_savings_amt');
44    poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
45 
46   if((l_view_by = 'HRI_PERSON+HRI_PER') and (l_view_by_value is not null) and (instr(l_view_by_value,',') = 0)) then
47     l_url := null;
48   else
49     l_url := 'pFunctionName=POA_DBI_PCL_STATUS_RPT&VIEW_BY_NAME=VIEW_BY_VALUE&VIEW_BY=HRI_PERSON+HRI_PER';
50   end if;
51 
52   l_query := get_status_sel_clause(l_view_by_col, l_url) || ' from
53               '|| poa_dbi_template_pkg.status_sql(
54                                               l_mv,
55                                               l_where_clause,
56                                               l_join_tbl,
57                                               p_use_windowing => 'Y',
58                                               p_col_name => l_col_tbl,
59 					      p_use_grpid => 'N',
60 					      p_filter_where => get_status_filter_where,
61                                               p_in_join_tables => l_in_join_tbl);
62 
63   x_custom_sql := l_query;
64 
65   end;
66 
67   PROCEDURE trend_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
68                       x_custom_sql  OUT NOCOPY VARCHAR2,
69                       x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
70   IS
71 	l_query varchar2(4000);
72         l_view_by varchar2(120);
73 	l_view_by_col varchar2(120);
74         l_as_of_date date;
75         l_prev_as_of_date date;
76         l_xtd varchar2(10);
77         l_comparison_type varchar2(1) := 'Y';
78         l_nested_pattern number;
79         l_cur_suffix varchar2(2);
80         l_custom_sql varchar2(4000);
81         l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
82 	l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
83 	l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
84 	l_mv VARCHAR2(30);
85 	l_where_clause VARCHAR2(2000);
86 	l_view_by_value VARCHAR2(100);
87   BEGIN
88   l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
89   l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
90 
91  poa_dbi_sutil_pkg.process_parameters(p_param,l_view_by,l_view_by_col,l_view_by_value,l_comparison_type, l_xtd, l_as_of_date, l_prev_as_of_date, l_cur_suffix, l_nested_pattern, l_where_clause, l_mv, l_join_tbl, l_in_join_tbl,
92 x_custom_output,
93                                      'Y','PO', '5.0', 'VPP','POD');
94 
95  poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt', 'N');
96  poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt', 'N');
97 
98   l_query := get_trend_sel_clause || ' from
99              '|| poa_dbi_template_pkg.trend_sql(
100                                               l_xtd,
101                                               l_comparison_type,
102                                               l_mv,
103                                               l_where_clause,
104                                               l_col_tbl,
105 					      p_use_grpid => 'N',
106                                               p_in_join_tables => l_in_join_tbl);
107 
108 
109   x_custom_sql := l_query;
110 
111   END;
112 
113   PROCEDURE kpi_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL,
114                       x_custom_sql  OUT NOCOPY VARCHAR2,
115                       x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
116   IS
117        l_query varchar2(4000);
118        l_view_by varchar2(120);
119 	l_view_by_col varchar2(120);
120        l_as_of_date date;
121        l_prev_as_of_date date;
122        l_xtd varchar2(10);
123        l_comparison_type varchar2(1) := 'Y';
124        l_nested_pattern number;
125        l_org_where varchar2(500);
126        l_cur_suffix varchar2(2);
127   	l_custom_sql varchar2(4000);
128        	l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
129 	l_where_clause varchar2(1000);
130 	l_mv varchar2(30);
131   	l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
132 	l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
133 	l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
134   	l_custom_rec BIS_QUERY_ATTRIBUTES;
135 	l_view_by_value VARCHAR2(100);
136   begin                                                                           l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
137   l_col_tbl := poa_dbi_util_pkg.POA_DBI_COL_TBL();
138 
139  poa_dbi_sutil_pkg.process_parameters(p_param,l_view_by,l_view_by_col,l_view_by_value,l_comparison_type, l_xtd, l_as_of_date, l_prev_as_of_date, l_cur_suffix, l_nested_pattern, l_where_clause, l_mv, l_join_tbl, l_in_join_tbl,
140 x_custom_output,
141                                       'N','PO', '5.0', 'VPP','POD');
142 
143   poa_dbi_util_pkg.add_column(l_col_tbl, 'purchase_amt_' || l_cur_suffix, 'purchase_amt');
144   poa_dbi_util_pkg.add_column(l_col_tbl, 'p_contract_amt_' || l_cur_suffix, 'p_contract_amt');
145   poa_dbi_util_pkg.add_column(l_col_tbl, 'n_contract_amt_' || l_cur_suffix, 'n_contract_amt');
146 
147   l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL();
148 
149   l_join_rec.table_name :=
150 	poa_dbi_sutil_pkg.get_table('ORGANIZATION+FII_OPERATING_UNITS', 'PO', '5.0');
151   l_join_rec.table_alias := 'v';
152   l_join_rec.fact_column :=
153 	poa_dbi_sutil_pkg.get_col_name('ORGANIZATION+FII_OPERATING_UNITS', 'PO', '5.0', 'POD');
154   l_join_rec.column_name := 'id';
155 
156   l_join_tbl.extend;
157   l_join_tbl(l_join_tbl.count) :=l_join_rec;
158 
159   l_query :=
160   'select v.value VIEWBY,
161           oset.POA_PERCENT1 POA_PERCENT1,
162           oset.POA_PERCENT2 POA_PERCENT2,
163           oset.POA_PERCENT3 POA_PERCENT3,
164           oset.POA_PERCENT4 POA_PERCENT4,
165           oset.POA_MEASURE1 POA_MEASURE1,
166           oset.POA_MEASURE2 POA_MEASURE2,
167           oset.POA_MEASURE3 POA_MEASURE3,
168           oset.POA_MEASURE4 POA_MEASURE4 from
169    (select * from
170      (select org_id,
171              ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') || ' POA_PERCENT1,
172              ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt','p_purchase_amt') || ' POA_PERCENT2,
173              ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt','c_purchase_amt') || ' POA_PERCENT3,
174              ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt','p_purchase_amt') || ' POA_PERCENT4,
175              ' || poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE1,
176              ' || poa_dbi_util_pkg.rate_clause('p_p_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE2,
177              ' || poa_dbi_util_pkg.rate_clause('c_n_contract_amt_total','c_purchase_amt_total') || ' POA_MEASURE3,
178              ' || poa_dbi_util_pkg.rate_clause('p_n_contract_amt_total','p_purchase_amt_total') || ' POA_MEASURE4
179    from
180   ' || poa_dbi_template_pkg.status_sql(      l_mv,
181                                               l_where_clause,
182                                              l_join_tbl,
183                                               p_use_windowing => 'N',
184                                               p_col_name => l_col_tbl,
185 					      p_use_grpid => 'N',
186 						p_filter_where => get_kpi_filter_where,
187                                               p_in_join_tables => l_in_join_tbl);
188 
189   x_custom_sql := l_query;
190 
191   end;
192 
193 
194   FUNCTION get_status_filter_where return VARCHAR2
195   IS
196     l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
197   BEGIN
198     l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
199     l_col_tbl.extend;
200     l_col_tbl(1) := 'POA_MEASURE1';
201     l_col_tbl.extend;
202     l_col_tbl(2) := 'POA_PERCENT2';
203     l_col_tbl.extend;
204     l_col_tbl(3) := 'POA_PERCENT1';
205     l_col_tbl.extend;
206     l_col_tbl(4) := 'POA_MEASURE3';
207 
208     return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
209 
210   END;
211 
212  FUNCTION get_kpi_filter_where return VARCHAR2
213   IS
214     l_col_tbl poa_dbi_sutil_pkg.poa_dbi_filter_tbl;
215   BEGIN
216     l_col_tbl := poa_dbi_sutil_pkg.POA_DBI_FILTER_TBL();
217     l_col_tbl.extend;
218     l_col_tbl(1) := 'POA_PERCENT3';
219     l_col_tbl.extend;
220     l_col_tbl(2) := 'POA_PERCENT2';
221     l_col_tbl.extend;
222     l_col_tbl(3) := 'POA_PERCENT1';
223     l_col_tbl.extend;
224     l_col_tbl(4) := 'POA_PERCENT4';
225 
226     return poa_dbi_sutil_pkg.get_filter_where(l_col_tbl);
227 
228   END;
229 
230 
231 
232 
233   FUNCTION get_status_sel_clause(p_view_by_col in VARCHAR2, p_url in VARCHAR2) return VARCHAR2
234   IS
235 
236   l_sel_clause varchar2(4000);
237 
238 
239   BEGIN
240   l_sel_clause :=
241   'select v.value VIEWBY,
242 	oset.POA_MEASURE1 POA_MEASURE1,		--Leakage Amount
243 	oset.POA_PERCENT1 POA_PERCENT1, 	--Change
244 	oset.POA_MEASURE3 POA_MEASURE3,		--Leakage Impact Amount
245 	oset.POA_PERCENT2 POA_PERCENT2,		--Leakage Rate
246 	oset.POA_MEASURE4 POA_MEASURE4, 	--Total Leakage Amount
247 	oset.POA_MEASURE5 POA_MEASURE5,		--Total Leakage Impact Amount
248 	oset.POA_MEASURE6 POA_MEASURE6, 	--Total Change
249 	oset.POA_MEASURE7 POA_MEASURE7,		-- Total Leakage Rate
250            ''' || p_url || ''' POA_MEASURE8
251      from
252      (select (rank() over
253                    (&ORDER_BY_CLAUSE nulls last, ' || p_view_by_col || ')) - 1 rnk,'
254         || p_view_by_col || ',
255            POA_MEASURE1, POA_PERCENT1, POA_MEASURE3, POA_PERCENT2, POA_MEASURE4,
256            POA_MEASURE5, POA_MEASURE6, POA_MEASURE7 from
257      (select ' || p_view_by_col || ',
258              ' || p_view_by_col || ' VIEWBY,
259            nvl(c_p_contract_amt,0) POA_MEASURE1,
260            ' || poa_dbi_util_pkg.change_clause('c_p_contract_amt','p_p_contract_amt') || ' POA_PERCENT1,
261            nvl(c_p_savings_amt,0) POA_MEASURE3,
262            '|| poa_dbi_util_pkg.rate_clause('c_p_contract_amt','c_purchase_amt') ||' POA_PERCENT2,
263            nvl(c_p_contract_amt_total,0) POA_MEASURE4,
264            nvl(c_p_savings_amt_total,0) POA_MEASURE5,
265            '||poa_dbi_util_pkg.change_clause('c_p_contract_amt_total','p_p_contract_amt_total') || ' POA_MEASURE6,
266            '||poa_dbi_util_pkg.rate_clause('c_p_contract_amt_total','c_purchase_amt_total')||' POA_MEASURE7';
267 
268   return l_sel_clause;
269 
270   END;
271 
272   FUNCTION get_trend_sel_clause return VARCHAR2
273   IS
274 
275   l_sel_clause varchar2(4000);
276 
277   BEGIN
278 
279 
280   l_sel_clause :=
281   'select cal.name VIEWBY,
282              nvl(p_p_contract_amt,0) POA_MEASURE1,
283              nvl(c_p_contract_amt,0) POA_MEASURE2,
284 	     nvl(p_p_contract_amt,0) POA_PERCENT1,
285           ' || poa_dbi_util_pkg.change_clause('c_p_contract_amt','p_p_contract_amt') || ' POA_PERCENT3,
286 	     nvl(c_p_contract_amt,0) POA_PERCENT2';
287 
288   return l_sel_clause;
289 
290   END;
291 
292 end poa_dbi_pcl_pkg;