[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_CARR_BILL_PAY_TRD_PKG
Source
1 PACKAGE BODY ISC_DBI_CARR_BILL_PAY_TRD_PKG AS
2 /*$Header: ISCRGC4B.pls 120.1 2006/06/26 06:53:20 abhdixi noship $
3 /*----------------------------------------------------
4 Declare PRIVATE procedures and functions for package
5 -----------------------------------------------------*/
6 /* Trend Report */
7 FUNCTION get_trd_sel_clause(p_view_by_dim IN VARCHAR2)
8 RETURN VARCHAR2;
9
10
11
12 /*----------------------------------------
13 Trend Report Function
14 ----------------------------------------*/
15 PROCEDURE get_trd_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
16 x_custom_sql OUT NOCOPY VARCHAR2,
17 x_custom_output OUT NOCOPY
18 BIS_QUERY_ATTRIBUTES_TBL)
19 IS
20 l_query VARCHAR2(32767);
21 l_view_by VARCHAR2(120);
22 l_view_by_col VARCHAR2 (120);
23 l_xtd1 VARCHAR2(10);
24 l_xtd2 VARCHAR2(10);
25 l_comparison_type VARCHAR2(1);
26 l_cur_suffix VARCHAR2(10);
27 l_currency VARCHAR2(10);
28 l_custom_sql VARCHAR2 (10000);
29
30 l_col_tbl1 poa_dbi_util_pkg.POA_DBI_COL_TBL;
31 l_col_tbl2 poa_dbi_util_pkg.POA_DBI_COL_TBL;
32 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
33 l_in_join_tbl poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
34 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
35
36 l_where_clause VARCHAR2 (2000);
37 l_mv1 VARCHAR2 (30);
38 l_mv2 VARCHAR2 (30);
39
40 l_aggregation_level_flag1 VARCHAR2(10);
41 l_aggregation_level_flag2 VARCHAR2(10);
42
43 l_custom_rec BIS_QUERY_ATTRIBUTES;
44
45 BEGIN
46
47 -- initialization block
48 l_comparison_type := 'Y';
49 l_aggregation_level_flag1 := '0';
50 l_aggregation_level_flag2 := '0';
51
52 -- clear out the tables.
53 l_col_tbl1 := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
54 l_col_tbl2 := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
55 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
56 x_custom_sql := l_query;
57
58 -- get all the query parameters
59 isc_dbi_sutil_pkg.process_parameters (
60 p_param => p_param,
61 p_view_by => l_view_by,
62 p_view_by_col_name => l_view_by_col,
63 p_comparison_type => l_comparison_type,
64 p_xtd => l_xtd1,
65 p_cur_suffix => l_cur_suffix,
66 p_where_clause => l_where_clause,
67 p_mv => l_mv1,
68 p_join_tbl => l_join_tbl,
69 p_mv_level_flag => l_aggregation_level_flag1,
70 p_trend => 'Y',
71 p_func_area => 'ISC',
72 p_version => '7.1',
73 p_role => '',
74 p_mv_set => 'C41',
75 p_mv_flag_type => 'FLAG2',
76 p_in_join_tbl => l_in_join_tbl);
77
78 -- Add measure columns that need to be aggregated
79 -- No Grand totals required.
80
81 --Convert the currency suffix to conform to ISC standards
82 IF (l_cur_suffix = 'g')
83 THEN l_currency := 'g';
84 ELSIF (l_cur_suffix = 'sg')
85 THEN l_currency := 'g1';
86 ELSE l_currency := 'g';
87 END IF;
88
89
90 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1,
91 p_col_name => 'fully_paid_amt_'|| l_currency,
92 p_alias_name => 'fully_paid_amt',
93 p_grand_total => 'N',
94 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
95 p_to_date_type => 'XTD');
96
97 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1,
98 p_col_name => 'bill_amt_' || l_currency,
99 p_alias_name => 'bill_amt',
100 p_grand_total => 'N',
101 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
102 p_to_date_type => 'XTD');
103
104 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1,
105 p_col_name => 'approved_amt_'||l_currency,
106 p_alias_name => 'approved_amt',
107 p_grand_total => 'N',
108 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
109 p_to_date_type => 'XTD');
110
111
112
113
114 isc_dbi_sutil_pkg.process_parameters (
115 p_param => p_param,
116 p_view_by => l_view_by,
117 p_view_by_col_name => l_view_by_col,
118 p_comparison_type => l_comparison_type,
119 p_xtd => l_xtd2,
120 p_cur_suffix => l_cur_suffix,
121 p_where_clause => l_where_clause,
122 p_mv => l_mv2,
123 p_join_tbl => l_join_tbl,
124 p_mv_level_flag => l_aggregation_level_flag2,
125 p_trend => 'Y',
126 p_func_area => 'ISC',
127 p_version => '7.1',
128 p_role => '',
129 p_mv_set => 'C42',
130 p_mv_flag_type => 'FLAG2',
131 p_in_join_tbl => l_in_join_tbl);
132
133
134
135 -- Add measure columns that need to be aggregated
136 -- No Grand totals required.
137
138
139 --Convert the currency suffix to conform to ISC standards
140 IF (l_cur_suffix = 'g')
141 THEN l_currency := 'g';
142 ELSIF (l_cur_suffix = 'sg')
143 THEN l_currency := 'g1';
144 ELSE l_currency := 'g';
145 END IF;
146
147
148 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2,
149 p_col_name => 'payment_amt_'||l_currency,
150 p_alias_name => 'payment_amt',
151 p_grand_total => 'N',
152 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
153 p_to_date_type => 'XTD');
154
155
156 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
157
158 l_mv_tbl.extend;
159 l_mv_tbl(1).mv_name := l_mv1;
160 l_mv_tbl(1).mv_col := l_col_tbl1;
161 l_mv_tbl(1).mv_where := l_where_clause;
162 l_mv_tbl(1).in_join_tbls := NULL;
163 l_mv_tbl(1).use_grp_id := 'N';
164 l_mv_tbl(1).mv_xtd := l_xtd1;
165
166 l_mv_tbl.extend;
167 l_mv_tbl(2).mv_name := l_mv2;
168 l_mv_tbl(2).mv_col := l_col_tbl2;
169 l_mv_tbl(2).mv_where := l_where_clause;
170 l_mv_tbl(2).in_join_tbls := NULL;
171 l_mv_tbl(2).use_grp_id := 'N';
172 l_mv_tbl(2).mv_xtd := l_xtd2;
173
174
175
176 -- Merge Outer and Inner Query
177 l_query := get_trd_sel_clause(l_view_by)
178 || ' from ' || poa_dbi_template_pkg.union_all_trend_sql
179 (p_mv => l_mv_tbl,
180 p_comparison_type => l_comparison_type,
181 p_filter_where => NULL);
182
183
184
185
186
187 -- Prepare PMV bind variables
188 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
189 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
190
191 -- get all the basic binds used by POA queries
192 -- Do this before adding any of our binds, since the procedure
193 -- reinitializes the output table
194 poa_dbi_util_pkg.get_custom_trend_binds (
195 p_xtd => l_xtd2,
196 p_comparison_type => l_comparison_type,
197 x_custom_output => x_custom_output);
198
199 -- Passing ISC_AGGREGATION_LEVEL_FLAG to PMV
200 l_custom_rec.attribute_name := ':ISC_AGG_FLAG';
201 l_custom_rec.attribute_value := l_aggregation_level_flag1;
202 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
203 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
204 x_custom_output.extend;
205 x_custom_output(x_custom_output.count) := l_custom_rec;
206
207 x_custom_sql := l_query;
208
209 END get_trd_sql;
210
211 /*--------------------------------------------------
212 Function: get_trd_sel_clause
213 Description: builds the outer select clause
214 ---------------------------------------------------*/
215
216 FUNCTION get_trd_sel_clause (p_view_by_dim IN VARCHAR2)
217 RETURN VARCHAR2
218 IS
219 l_sel_clause varchar2(7500);
220 BEGIN
221
222 -- Main Outer query
223
224 l_sel_clause :=
225 'SELECT
226 ' || ' cal_name VIEWBY,
227 p_payment_amt ISC_MEASURE_9,
228 c_payment_amt ISC_MEASURE_10,
229 ' || isc_dbi_sutil_pkg.change_str (
230 p_new_numerator => 'c_payment_amt',
231 p_old_numerator => 'p_payment_amt',
232 p_denominator => 'p_payment_amt',
233 p_measure_name => 'ISC_MEASURE_11') || ', -- Payment Change
234 c_bill_amt - c_fully_paid_amt ISC_MEASURE_15, --- Bill to Paid Variance Amount
235 ' || isc_dbi_sutil_pkg.change_str (
236 p_new_numerator => '(c_bill_amt - c_fully_paid_amt)',
237 p_old_numerator => '(p_bill_amt - p_fully_paid_amt)',
238 p_denominator => '(p_bill_amt - p_fully_paid_amt)',
239 p_measure_name => 'ISC_MEASURE_16') || ', -- Billed to Paid Variance Amount Change
240 ' || isc_dbi_sutil_pkg.change_str (
241 p_new_numerator => 'p_bill_amt',
242 p_old_numerator => 'p_fully_paid_amt',
243 p_denominator => 'p_fully_paid_amt',
244 p_measure_name => 'ISC_MEASURE_17') || ', --(Bill-to-Paid Variance Percent) Prior
245 ' || isc_dbi_sutil_pkg.change_str (
246 p_new_numerator => 'c_bill_amt',
247 p_old_numerator => 'c_fully_paid_amt',
248 p_denominator => 'c_fully_paid_amt',
249 p_measure_name => 'ISC_MEASURE_18') || ', --(Bill-to-Paid Variance Percent) Current
250 ' || isc_dbi_sutil_pkg.change_rate_str (
251 p_new_numerator => '(c_bill_amt - c_fully_paid_amt)',
252 p_new_denominator => 'c_fully_paid_amt',
253 p_old_numerator => '(p_bill_amt - p_fully_paid_amt)',
254 p_old_denominator => 'p_fully_paid_amt',
255 p_rate_type => 'RATIO',
256 p_measure_name => 'ISC_MEASURE_19') || ', -- Bill-to_Paid Variance Percent Change
257 c_bill_amt - c_approved_amt ISC_MEASURE_22, --- Bill to Approved Variance Amount
258 ' || isc_dbi_sutil_pkg.change_str (
259 p_new_numerator => '(c_bill_amt - c_approved_amt)',
260 p_old_numerator => '(p_bill_amt - p_approved_amt)',
261 p_denominator => '(p_bill_amt - p_approved_amt)',
262 p_measure_name => 'ISC_MEASURE_21') || ',--Billed to Approved Variance Amount Change
263 ' || isc_dbi_sutil_pkg.change_str (
264 p_new_numerator => 'p_bill_amt',
265 p_old_numerator => 'p_approved_amt',
266 p_denominator => 'p_approved_amt',
267 p_measure_name => 'ISC_MEASURE_12') ||',--(Bill-to-Approved Variance Percent) Prior
268 ' || isc_dbi_sutil_pkg.change_str (
269 p_new_numerator => 'c_bill_amt',
270 p_old_numerator => 'c_approved_amt',
271 p_denominator => 'c_approved_amt',
272 p_measure_name => 'ISC_MEASURE_23') ||',--(Bill-to-Approved Var Percent) Current
273 ' || isc_dbi_sutil_pkg.change_rate_str (
274 p_new_numerator => '(c_bill_amt - c_approved_amt)',
275 p_new_denominator => 'c_approved_amt',
276 p_old_numerator => '(p_bill_amt - p_approved_amt)',
277 p_old_denominator => 'p_approved_amt',
278 p_rate_type => 'RATIO',
279 p_measure_name => 'ISC_MEASURE_24'); -- Bill-to_Approved Variance Percent Change
280
281
282 RETURN l_sel_clause;
283
284 END get_trd_sel_clause;
285
286 END ISC_DBI_CARR_BILL_PAY_TRD_PKG;