[Home] [Help]
PACKAGE BODY: APPS.POA_DBI_SPND_PKG
Source
1 PACKAGE BODY poa_dbi_spnd_pkg
2 /* $Header: poadbispndb.pls 120.2 2005/11/30 15:31:53 nnewadka noship $ */
3 AS
4 --
5
6
7
8
9 FUNCTION get_trend_sel_clause (p_view_by IN VARCHAR2)
10 RETURN VARCHAR2 ;
11
12
13 PROCEDURE trend_sql(p_param in BIS_PMV_PAGE_PARAMETER_TBL
14 ,x_custom_sql OUT NOCOPY VARCHAR2
15 ,x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
16 IS
17 l_query VARCHAR2(32000);
18 l_view_by VARCHAR2(120);
19 l_view_by_col_name VARCHAR2(120);
20 l_as_of_date DATE;
21 l_prev_as_of_date DATE;
22 l_xtd VARCHAR2(10);
23 l_nested_pattern NUMBER;
24 l_comparison_type VARCHAR2(1) ;
25 l_cur_suffix VARCHAR2(10);
26 l_url VARCHAR2(300);
27 l_view_by_value VARCHAR2(30);
28 l_col_tbl poa_dbi_util_pkg.POA_DBI_COL_TBL;
29 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
30 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
31 l_join_rec poa_dbi_util_pkg.POA_DBI_JOIN_REC;
32 l_where_clause VARCHAR2(2000);
33 l_mv VARCHAR2(30);
34 ERR_MSG VARCHAR2(100);
35 ERR_CDE NUMBER;
36 l_context_code VARCHAR2(10);
37 l_to_date_type VARCHAR2(10);
38
39 l_col_tbl1 poa_dbi_util_pkg.POA_DBI_COL_TBL;
40 l_col_tbl2 poa_dbi_util_pkg.POA_DBI_COL_TBL;
41 l_col_tbl3 poa_dbi_util_pkg.POA_DBI_COL_TBL;
42 l_col_tbl4 poa_dbi_util_pkg.POA_DBI_COL_TBL;
43 l_where_clause1 VARCHAR2 (2000);
44 l_where_clause2 VARCHAR2 (2000);
45 l_where_clause3 VARCHAR2 (2000);
46 l_where_clause4 VARCHAR2 (2000);
47 l_mv1 VARCHAR2 (30);
48 l_mv2 VARCHAR2 (30);
49 l_mv3 VARCHAR2 (30);
50 l_mv4 VARCHAR2 (30);
51 l_in_join_tbl1 poa_dbi_util_pkg.poa_dbi_in_join_tbl;
52 l_in_join_tbl2 poa_dbi_util_pkg.poa_dbi_in_join_tbl;
53 l_in_join_tbl3 poa_dbi_util_pkg.poa_dbi_in_join_tbl;
54 l_in_join_tbl4 poa_dbi_util_pkg.poa_dbi_in_join_tbl;
55 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
56
57 l_file varchar2(500);
58 BEGIN
59 l_comparison_type := 'Y';
60
61 l_col_tbl1 := poa_dbi_util_pkg.poa_dbi_col_tbl();
62 l_col_tbl2 := poa_dbi_util_pkg.poa_dbi_col_tbl();
63 l_col_tbl3 := poa_dbi_util_pkg.poa_dbi_col_tbl();
64 l_col_tbl4 := poa_dbi_util_pkg.poa_dbi_col_tbl();
65
66 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl();
67
68 ---Get the Invoice Amount Entered Measure
69 poa_dbi_sutil_pkg.process_parameters(p_param,
70 l_view_by,
71 l_view_by_col_name,
72 l_view_by_value,
73 l_comparison_type,
74 l_xtd,
75 l_as_of_date,
76 l_prev_as_of_date,
77 l_cur_suffix,
78 l_nested_pattern,
79 l_where_clause1,
80 l_mv1,
81 l_join_tbl,
82 l_in_join_tbl1,
83 x_custom_output,
84 p_trend => 'Y',
85 p_func_area => 'PO',
86 p_version => '8.0',
87 p_role => 'VPP',
88 p_mv_set => 'FIIIV');
89
90 poa_dbi_util_pkg.add_column(l_col_tbl1
91 , 'invoice_amt_entered_' || l_cur_suffix
92 , 'invoice_entered_amt'
93 , p_grand_total => 'N'
94 , p_prior_code => poa_dbi_util_pkg.both_priors
95 , p_to_date_type => 'RLX');
96
97 ---Get the Paid Amount Measure
98 poa_dbi_sutil_pkg.process_parameters(p_param,
99 l_view_by,
100 l_view_by_col_name,
101 l_view_by_value,
102 l_comparison_type,
103 l_xtd,
104 l_as_of_date,
105 l_prev_as_of_date,
106 l_cur_suffix,
107 l_nested_pattern,
108 l_where_clause2,
109 l_mv2,
110 l_join_tbl,
111 l_in_join_tbl2,
112 x_custom_output,
113 p_trend => 'Y',
114 p_func_area => 'PO',
115 p_version => '8.0',
116 p_role => 'VPP',
117 p_mv_set => 'FIIPA');
118
119 poa_dbi_util_pkg.add_column(l_col_tbl2
120 , 'paid_amt_' || l_cur_suffix
121 , 'paid_amt'
122 , p_grand_total => 'N'
123 , p_prior_code => poa_dbi_util_pkg.both_priors
124 , p_to_date_type => 'RLX');
125
126 ---Get the Purchased Amt. Measure
127 poa_dbi_sutil_pkg.process_parameters(p_param,
128 l_view_by,
129 l_view_by_col_name,
130 l_view_by_value,
131 l_comparison_type,
132 l_xtd,
133 l_as_of_date,
134 l_prev_as_of_date,
135 l_cur_suffix,
136 l_nested_pattern,
137 l_where_clause3,
138 l_mv3,
139 l_join_tbl,
140 l_in_join_tbl3,
141 x_custom_output,
142 p_trend => 'Y',
143 p_func_area => 'PO',
144 p_version => '8.0',
145 p_role => 'VPP',
146 p_mv_set => 'POD');
147
148 poa_dbi_util_pkg.add_column(l_col_tbl3
149 , 'purchase_amt_' || l_cur_suffix
150 , 'purchase_amt'
151 , p_grand_total => 'N'
152 , p_prior_code => poa_dbi_util_pkg.both_priors
153 , p_to_date_type => 'RLX');
154
155 --Get the Invoice Amt. which is Matched to PO mesaure
156 poa_dbi_sutil_pkg.process_parameters(p_param,
157 l_view_by,
158 l_view_by_col_name,
159 l_view_by_value,
160 l_comparison_type,
161 l_xtd,
162 l_as_of_date,
163 l_prev_as_of_date,
164 l_cur_suffix,
165 l_nested_pattern,
166 l_where_clause4,
167 l_mv4,
168 l_join_tbl,
169 l_in_join_tbl4,
170 x_custom_output,
171 p_trend => 'Y',
172 p_func_area => 'PO',
173 p_version => '8.0',
174 p_role => 'VPP',
175 p_mv_set => 'API');
176
177 poa_dbi_util_pkg.add_column(l_col_tbl4
178 , 'amount_' || l_cur_suffix
179 , 'invoice_matched_amt'
180 , p_grand_total => 'N'
181 , p_prior_code => poa_dbi_util_pkg.both_priors
182 , p_to_date_type => 'RLX');
183
184 ---Now populate the MV table list
185 l_mv_tbl.extend;
186 l_mv_tbl(1).mv_name := l_mv1;
187 l_mv_tbl(1).mv_col := l_col_tbl1;
188 l_mv_tbl(1).mv_where := l_where_clause1;
189 l_mv_tbl(1).in_join_tbls := l_in_join_tbl1;
190 l_mv_tbl(1).use_grp_id := 'N';
191 l_mv_tbl(1).mv_hint := poa_dbi_sutil_pkg.get_fact_hint(l_mv1);
192 l_mv_tbl(1).mv_xtd := l_xtd;
193
194 l_mv_tbl.extend;
195 l_mv_tbl(2).mv_name := l_mv2;
196 l_mv_tbl(2).mv_col := l_col_tbl2;
197 l_mv_tbl(2).mv_where := l_where_clause2;
198 l_mv_tbl(2).in_join_tbls := l_in_join_tbl2;
199 l_mv_tbl(2).use_grp_id := 'N';
200 l_mv_tbl(2).mv_hint := poa_dbi_sutil_pkg.get_fact_hint(l_mv2);
201 l_mv_tbl(2).mv_xtd := l_xtd;
202
203 l_mv_tbl.extend;
204 l_mv_tbl(3).mv_name := l_mv3;
205 l_mv_tbl(3).mv_col := l_col_tbl3;
206 l_mv_tbl(3).mv_where := l_where_clause3;
207 l_mv_tbl(3).in_join_tbls := l_in_join_tbl3;
208 l_mv_tbl(3).use_grp_id := 'N';
209 l_mv_tbl(3).mv_hint := poa_dbi_sutil_pkg.get_fact_hint(l_mv3);
210 l_mv_tbl(3).mv_xtd := l_xtd;
211
212 l_mv_tbl.extend;
213 l_mv_tbl(4).mv_name := l_mv4;
214 l_mv_tbl(4).mv_col := l_col_tbl4;
215 l_mv_tbl(4).mv_where := l_where_clause4;
216 l_mv_tbl(4).in_join_tbls := l_in_join_tbl4;
217 l_mv_tbl(4).use_grp_id := 'N';
218 l_mv_tbl(4).mv_hint := poa_dbi_sutil_pkg.get_fact_hint(l_mv4);
219 l_mv_tbl(4).mv_xtd := l_xtd;
220
221
222 l_query := get_trend_sel_clause(l_view_by) ||
223 ' from ' ||
224 poa_dbi_template_pkg.union_all_trend_sql (
225 p_mv => l_mv_tbl,
226 p_comparison_type => l_comparison_type,
227 p_filter_where => NULL);
228
229 x_custom_sql := l_query ;
230
231
232 END trend_sql ;
233
234 FUNCTION get_trend_sel_clause (p_view_by IN VARCHAR2)
235 return VARCHAR2
236 IS
237 l_sel_clause VARCHAR2(4000);
238 BEGIN
239 l_sel_clause := 'select cal_name VIEWBY,';
240 l_sel_clause := l_sel_clause ||
241 'c_purchase_amt POA_MEASURE1,
242 c_invoice_entered_amt POA_MEASURE2,
243 c_invoice_matched_amt POA_MEASURE3,
244 c_paid_amt POA_MEASURE4,'
245 ||
246 poa_dbi_util_pkg.change_clause('c_purchase_amt','p_purchase_amt') || ' POA_PERCENT1, ' ||
247 poa_dbi_util_pkg.change_clause('c_invoice_entered_amt','p_invoice_entered_amt') || ' POA_PERCENT2,'
248 || poa_dbi_util_pkg.change_clause('c_invoice_matched_amt','p_invoice_matched_amt') || ' POA_PERCENT3,'
249 || poa_dbi_util_pkg.change_clause('c_paid_amt','p_paid_amt') || ' POA_PERCENT4 ' ;
250
251 RETURN l_sel_clause;
252 END get_trend_sel_clause ;
253
254 END poa_dbi_spnd_pkg;