[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_CARR_BILL_PAY_PKG
Source
1 PACKAGE BODY ISC_DBI_CARR_BILL_PAY_PKG AS
2 /*$Header: ISCRGC3B.pls 120.0 2005/05/25 17:17:50 appldev noship $
3 /*----------------------------------------------------
4 Declare PRIVATE procedures and functions for package
5 -----------------------------------------------------*/
6
7
8 /* Non-Trend Report */
9 FUNCTION get_rpt_sel_clause (p_view_by_dim IN VARCHAR2,
10 p_join_tbl IN
11 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
12 RETURN VARCHAR2;
13
14
15 /*----------------------------------------
16 Carrier Billing and Payment Variance Report Function
17 ----------------------------------------*/
18 PROCEDURE get_tbl_sql (p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
19 x_custom_sql OUT NOCOPY VARCHAR2,
20 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
21 IS
22 l_query VARCHAR2(32767);
23 l_view_by VARCHAR2(120);
24 l_view_by_col VARCHAR2 (120);
25 l_xtd1 VARCHAR2(10);
26 l_xtd2 VARCHAR2(10);
27 l_comparison_type VARCHAR2(1);
28 l_cur_suffix VARCHAR2(5);
29 l_currency VARCHAR2(10);
30
31 l_custom_sql VARCHAR2 (10000);
32
33 l_col_tbl1 poa_dbi_util_pkg.POA_DBI_COL_TBL;
34 l_col_tbl2 poa_dbi_util_pkg.POA_DBI_COL_TBL;
35 l_join_tbl poa_dbi_util_pkg.POA_DBI_JOIN_TBL;
36 l_in_join_tbl1 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
37 l_in_join_tbl2 poa_dbi_util_pkg.POA_DBI_IN_JOIN_TBL;
38 l_mv_tbl poa_dbi_util_pkg.poa_dbi_mv_tbl;
39
40 l_where_clause VARCHAR2 (2000);
41 l_mv1 VARCHAR2 (30);
42 l_mv2 VARCHAR2 (30);
43
44 l_aggregation_level_flag1 VARCHAR2(10);
45 l_aggregation_level_flag2 VARCHAR2(10);
46
47 l_custom_rec BIS_QUERY_ATTRIBUTES;
48
49 BEGIN
50
51 -- initialization block
52 l_comparison_type := 'Y';
53 l_aggregation_level_flag1 := '0';
54
55 -- clear out the column and Join info tables.
56 l_col_tbl1 := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
57 l_col_tbl2 := poa_dbi_util_pkg.POA_DBI_COL_TBL ();
58 l_join_tbl := poa_dbi_util_pkg.POA_DBI_JOIN_TBL ();
59
60 -- get all the query parameters for the RTX MV
61 isc_dbi_sutil_pkg.process_parameters (
62 p_param => p_param,
63 p_view_by => l_view_by,
64 p_view_by_col_name => l_view_by_col,
65 p_comparison_type => l_comparison_type,
66 p_xtd => l_xtd1,
67 p_cur_suffix => l_cur_suffix,
68 p_where_clause => l_where_clause,
69 p_mv => l_mv1,
70 p_join_tbl => l_join_tbl,
71 p_mv_level_flag => l_aggregation_level_flag1,
72 p_trend => 'N',
73 p_func_area => 'ISC',
74 p_version => '7.1',
75 p_role => '',
76 p_mv_set => 'C31',
77 p_mv_flag_type => 'FLAG2',
78 p_in_join_tbl => l_in_join_tbl1);
79
80
81
82 --Convert the currency suffix to conform to ISC standards
83 IF (l_cur_suffix = 'g')
84 THEN l_currency := 'g';
85 ELSIF (l_cur_suffix = 'sg')
86 THEN l_currency := 'g1';
87 ELSE l_currency := 'g';
88 END IF;
89
90
91 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1,
92 p_col_name => 'fully_paid_amt_'|| l_currency,
93 p_alias_name => 'fully_paid_amt',
94 p_grand_total => 'Y',
95 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
96 p_to_date_type => 'XTD');
97
98 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1,
99 p_col_name => 'bill_amt_' || l_currency,
100 p_alias_name => 'bill_amt',
101 p_grand_total => 'Y',
102 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
103 p_to_date_type => 'XTD');
104
105 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl1,
106 p_col_name => 'approved_amt_'||l_currency,
107 p_alias_name => 'approved_amt',
108 p_grand_total => 'Y',
109 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
110 p_to_date_type => 'XTD');
111
112
113
114
115 isc_dbi_sutil_pkg.process_parameters (
116 p_param => p_param,
117 p_view_by => l_view_by,
118 p_view_by_col_name => l_view_by_col,
119 p_comparison_type => l_comparison_type,
120 p_xtd => l_xtd2,
121 p_cur_suffix => l_cur_suffix,
122 p_where_clause => l_where_clause,
123 p_mv => l_mv2,
124 p_join_tbl => l_join_tbl,
125 p_mv_level_flag => l_aggregation_level_flag2,
126 p_trend => 'N',
127 p_func_area => 'ISC',
128 p_version => '7.1',
129 p_role => '',
130 p_mv_set => 'C32',
131 p_mv_flag_type => 'FLAG2',
132 p_in_join_tbl => l_in_join_tbl2);
133
134
135
136 -- Add measure columns that need to be aggregated
137 -- No Grand totals required.
138
139
140 --Convert the currency suffix to conform to ISC standards
141 IF (l_cur_suffix = 'g')
142 THEN l_currency := 'g';
143 ELSIF (l_cur_suffix = 'sg')
144 THEN l_currency := 'g1';
145 ELSE l_currency := 'g';
146 END IF;
147
148
149 poa_dbi_util_pkg.add_column (p_col_tbl => l_col_tbl2,
150 p_col_name => 'payment_amt_'||l_currency,
151 p_alias_name => 'payment_amt',
152 p_grand_total => 'Y',
153 p_prior_code => poa_dbi_util_pkg.BOTH_PRIORS,
154 p_to_date_type => 'XTD');
155
156
157
158
159 l_mv_tbl := poa_dbi_util_pkg.poa_dbi_mv_tbl ();
160
161 l_mv_tbl.extend;
162 l_mv_tbl(1).mv_name := l_mv1;
163 l_mv_tbl(1).mv_col := l_col_tbl1;
164 l_mv_tbl(1).mv_where := l_where_clause;
165 l_mv_tbl(1).in_join_tbls := NULL;
166 l_mv_tbl(1).use_grp_id := 'N';
167
168 l_mv_tbl.extend;
169 l_mv_tbl(2).mv_name := l_mv2;
170 l_mv_tbl(2).mv_col := l_col_tbl2;
171 l_mv_tbl(2).mv_where := l_where_clause;
172 l_mv_tbl(2).in_join_tbls := NULL;
173 l_mv_tbl(2).use_grp_id := 'N';
174
175
176
177
178 -- construct the query
179 l_query := get_rpt_sel_clause (l_view_by, l_join_tbl)
180 || ' from (
181 ' || poa_dbi_template_pkg.union_all_status_sql
182 (p_mv => l_mv_tbl,
183 p_join_tables => l_join_tbl,
184 p_use_windowing => 'Y',
185 p_paren_count => 3,
186 p_filter_where => NULL);
187
188 -- prepare output for bind variables
189 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
190 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
191
192 -- set the basic bind variables for the status SQL
193 poa_dbi_util_pkg.get_custom_status_binds (x_custom_output);
194 poa_dbi_util_pkg.get_custom_rolling_binds(x_custom_output,l_xtd1);
195 poa_dbi_util_pkg.get_custom_rolling_binds(x_custom_output,l_xtd2);
196 -- Passing OPI_AGGREGATION_LEVEL_FLAGS to PMV
197 l_custom_rec.attribute_name := ':ISC_AGG_FLAG';
198 l_custom_rec.attribute_value := l_aggregation_level_flag1;
199 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
200 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
201 x_custom_output.extend;
202
203
204 l_custom_rec.attribute_name := ':ISC_AGG_FLAG';
205 l_custom_rec.attribute_value := l_aggregation_level_flag2;
206 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
207 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
208 x_custom_output.extend;
209 x_custom_output(x_custom_output.count) := l_custom_rec;
210
211 x_custom_sql := l_query;
212
213 END get_tbl_sql;
214
215
216
217
218 FUNCTION get_rpt_sel_clause(p_view_by_dim IN VARCHAR2,
219 p_join_tbl IN
220 poa_dbi_util_pkg.POA_DBI_JOIN_TBL)
221 RETURN VARCHAR2
222 IS
223
224 l_sel_clause VARCHAR2(15000);
225 l_view_by_col_name VARCHAR2(60);
226 l_description VARCHAR2(30);
227 l_drill_across_rep_1 VARCHAR2(50);
228 l_drill_across_rep_2 VARCHAR2(50);
229 l_view_by_fact_col VARCHAR2(400);
230 l_drill_across VARCHAR2(1000);
231
232 BEGIN
233
234 -- initialization block
235 -- Column to get view by column name
236 l_view_by_col_name := isc_dbi_sutil_pkg.get_view_by_col_name (p_view_by_dim);
237
238 -- fact column view by's
239 l_view_by_fact_col := isc_dbi_sutil_pkg.get_fact_select_columns (p_join_tbl);
240
241
242 -- Outer select clause
243 l_sel_clause :=
244 'SELECT ' || isc_dbi_sutil_pkg.get_view_by_select_clause (p_view_by_dim)
245 || 'oset.ISC_MEASURE_9 ISC_MEASURE_9,
246 ' || 'oset.ISC_MEASURE_10 ISC_MEASURE_10,
247 ' || 'oset.ISC_MEASURE_11 ISC_MEASURE_11,
248 ' || 'oset.ISC_MEASURE_12 ISC_MEASURE_12,
249 ' || 'oset.ISC_MEASURE_13 ISC_MEASURE_13,
250 ' || 'oset.ISC_MEASURE_15 ISC_MEASURE_15,
251 ' || 'oset.ISC_MEASURE_16 ISC_MEASURE_16,
252 ' || 'oset.ISC_MEASURE_17 ISC_MEASURE_17,
253 ' || 'oset.ISC_MEASURE_18 ISC_MEASURE_18,
254 ' || 'oset.ISC_MEASURE_19 ISC_MEASURE_19,
255 ' || 'oset.ISC_MEASURE_21 ISC_MEASURE_21,
256 ' || 'oset.ISC_MEASURE_22 ISC_MEASURE_22,
257 ' || 'oset.ISC_MEASURE_23 ISC_MEASURE_23,
258 ' || 'oset.ISC_MEASURE_24 ISC_MEASURE_24,
259 ' || 'oset.ISC_MEASURE_1 ISC_MEASURE_1,
260 ' || 'oset.ISC_MEASURE_2 ISC_MEASURE_2,
261 ' || 'oset.ISC_MEASURE_3 ISC_MEASURE_3,
262 ' || 'oset.ISC_MEASURE_4 ISC_MEASURE_4,
263 ' || 'oset.ISC_MEASURE_5 ISC_MEASURE_5,
264 ' || 'oset.ISC_MEASURE_6 ISC_MEASURE_6,
265 ' || 'oset.ISC_MEASURE_7 ISC_MEASURE_7,
266 ' || 'oset.ISC_MEASURE_8 ISC_MEASURE_8,
267 ' || 'oset.ISC_MEASURE_25 ISC_MEASURE_25,
268 ' || 'oset.ISC_MEASURE_26 ISC_MEASURE_26,
269 ' || 'oset.ISC_MEASURE_27 ISC_MEASURE_27,
270 ' || 'oset.ISC_MEASURE_28 ISC_MEASURE_28,
271 ' || 'oset.ISC_MEASURE_30 ISC_MEASURE_30,
272 ' || 'oset.ISC_MEASURE_32 ISC_MEASURE_32,
273 ' || 'oset.ISC_MEASURE_33 ISC_MEASURE_33,
274 ' || 'oset.ISC_MEASURE_34 ISC_MEASURE_34,
275 ' || 'oset.ISC_MEASURE_35 ISC_MEASURE_35
276 ' || 'FROM
277 ' || '(SELECT (rank () over
278 ' || ' (&ORDER_BY_CLAUSE nulls last,
279 ' || l_view_by_fact_col || ')) - 1 rnk,
280 ' || l_view_by_fact_col || ',
281 ' || 'ISC_MEASURE_1,ISC_MEASURE_2,ISC_MEASURE_3,ISC_MEASURE_4,ISC_MEASURE_5,
282 ISC_MEASURE_6,ISC_MEASURE_7,ISC_MEASURE_8,ISC_MEASURE_9,ISC_MEASURE_10,
283 ISC_MEASURE_11,ISC_MEASURE_12,ISC_MEASURE_13,ISC_MEASURE_15,
284 ISC_MEASURE_16,ISC_MEASURE_17,ISC_MEASURE_18,ISC_MEASURE_19,
285 ISC_MEASURE_21,ISC_MEASURE_22,ISC_MEASURE_23,ISC_MEASURE_24,
286 ISC_MEASURE_25,ISC_MEASURE_26,ISC_MEASURE_27,ISC_MEASURE_28,
287 ISC_MEASURE_30,ISC_MEASURE_32,ISC_MEASURE_33,ISC_MEASURE_34,ISC_MEASURE_35
288 ' || 'FROM
289 ' || '(SELECT
290 ' || l_view_by_fact_col || ',
291 p_payment_amt ISC_MEASURE_9,
292 c_payment_amt ISC_MEASURE_10,
293 p_payment_amt_total ISC_MEASURE_32,
294 c_payment_amt_total ISC_MEASURE_1,
295 c_fully_paid_amt ISC_MEASURE_13,
296 c_fully_paid_amt_total ISC_MEASURE_4,
297 c_bill_amt ISC_MEASURE_15,
298 c_bill_amt_total ISC_MEASURE_5,
299 c_bill_amt - c_fully_paid_amt ISC_MEASURE_16,
300 c_bill_amt_total - c_fully_paid_amt_total ISC_MEASURE_6,
301 c_approved_amt ISC_MEASURE_21,
302 c_bill_amt - c_approved_amt ISC_MEASURE_22,
303 c_approved_amt_total ISC_MEASURE_25,
304 c_bill_amt_total - c_approved_amt_total ISC_MEASURE_26,
305 ' || isc_dbi_sutil_pkg.change_str (
306 p_new_numerator => 'c_payment_amt',
307 p_old_numerator => 'p_payment_amt',
308 p_denominator => 'p_payment_amt',
309 p_measure_name => 'ISC_MEASURE_11') || ', -- Payment Change
310
311 ' || isc_dbi_sutil_pkg.change_str (
312 p_new_numerator => 'c_payment_amt_total',
313 p_old_numerator => 'p_payment_amt_total',
314 p_denominator => 'p_payment_amt_total',
315 p_measure_name => 'ISC_MEASURE_2') || ', -- GT - Total Payment Change
316
317 ' || isc_dbi_sutil_pkg.rate_str (
321 p_measure_name => 'ISC_MEASURE_12') || ', -- Percent of Total
318 p_numerator => 'c_payment_amt',
319 p_denominator => 'c_payment_amt_total',
320 p_rate_type => 'PERCENT',
322
323 ' || isc_dbi_sutil_pkg.rate_str (
324 p_numerator => 'c_payment_amt_total',
325 p_denominator => 'c_payment_amt_total',
326 p_rate_type => 'PERCENT',
327 p_measure_name => 'ISC_MEASURE_3') || ', -- GT- Percent of Total
328
329 ' || isc_dbi_sutil_pkg.change_str (
330 p_new_numerator => 'p_bill_amt',
331 p_old_numerator => 'p_fully_paid_amt',
332 p_denominator => 'p_fully_paid_amt',
333 p_measure_name => 'ISC_MEASURE_17') || ', -- (Bill-to-Paid Variance Percent) Prior
334
335 ' || isc_dbi_sutil_pkg.change_str (
336 p_new_numerator => 'p_bill_amt',
337 p_old_numerator => 'p_fully_paid_amt',
338 p_denominator => 'p_fully_paid_amt',
339 p_measure_name => 'ISC_MEASURE_35') || ', -- KPI (Bill-to-Paid Variance Percent) Prior
340
341 ' || isc_dbi_sutil_pkg.change_str (
342 p_new_numerator => 'p_bill_amt_total',
343 p_old_numerator => 'p_fully_paid_amt_total',
344 p_denominator => 'p_fully_paid_amt_total',
345 p_measure_name => 'ISC_MEASURE_30') || ', -- GT (Bill-to-Paid Variance Percent) Prior
346
347 ' || isc_dbi_sutil_pkg.change_str (
348 p_new_numerator => 'c_bill_amt',
349 p_old_numerator => 'c_fully_paid_amt',
350 p_denominator => 'c_fully_paid_amt',
351 p_measure_name => 'ISC_MEASURE_18') || ', --(Bill-to-Paid Variance Percent) Current
352
353 ' || isc_dbi_sutil_pkg.change_str (
354 p_new_numerator => 'c_bill_amt',
355 p_old_numerator => 'c_fully_paid_amt',
356 p_denominator => 'c_fully_paid_amt',
357 p_measure_name => 'ISC_MEASURE_33') || ', --(Bill-to-Paid Variance Percent) Current (for KPI)
358
359 ' || isc_dbi_sutil_pkg.change_str (
360 p_new_numerator => 'c_bill_amt_total',
361 p_old_numerator => 'c_fully_paid_amt_total',
362 p_denominator => 'c_fully_paid_amt_total',
363 p_measure_name => 'ISC_MEASURE_7') || ', -- GT-(Bill-to-Paid Variance Percent) Current
364
365 ' || isc_dbi_sutil_pkg.change_str (
366 p_new_numerator => 'c_bill_amt_total',
367 p_old_numerator => 'c_fully_paid_amt_total',
368 p_denominator => 'c_fully_paid_amt_total',
369 p_measure_name => 'ISC_MEASURE_34') || ', -- KPIGT-(Bill-to-Paid Variance Percent) Current
370
371 ' || isc_dbi_sutil_pkg.change_rate_str (
372 p_new_numerator => '(c_bill_amt - c_fully_paid_amt)',
373 p_new_denominator => 'c_fully_paid_amt',
374 p_old_numerator => '(p_bill_amt - p_fully_paid_amt)',
375 p_old_denominator => 'p_fully_paid_amt',
376 p_rate_type => 'RATIO',
377 p_measure_name => 'ISC_MEASURE_19') || ', -- Bill-to_Paid Variance Percent Change
378
379 ' || isc_dbi_sutil_pkg.change_rate_str (
380 p_new_numerator => '(c_bill_amt_total - c_fully_paid_amt_total)',
381 p_new_denominator => 'c_fully_paid_amt_total',
382 p_old_numerator => '(p_bill_amt_total - p_fully_paid_amt_total)',
383 p_old_denominator => 'p_fully_paid_amt_total',
384 p_rate_type => 'RATIO',
385 p_measure_name => 'ISC_MEASURE_8') || ', --GT - Bill-to_Paid Variance Percent Change
386
387 ' || isc_dbi_sutil_pkg.change_str (
388 p_new_numerator => 'c_bill_amt',
389 p_old_numerator => 'c_approved_amt',
390 p_denominator => 'c_approved_amt',
391 p_measure_name => 'ISC_MEASURE_23') ||',--(Bill-to-Approved Var Percent) Current
392
393 ' || isc_dbi_sutil_pkg.change_rate_str (
394 p_new_numerator => '(c_bill_amt - c_approved_amt)',
395 p_new_denominator => 'c_approved_amt',
396 p_old_numerator => '(p_bill_amt - p_approved_amt)',
397 p_old_denominator => 'p_approved_amt',
398 p_rate_type => 'RATIO',
399 p_measure_name => 'ISC_MEASURE_24') || ', -- Bill-to-Approved Variance Percent Change
400
401 ' || isc_dbi_sutil_pkg.change_str (
402 p_new_numerator => 'c_bill_amt_total',
403 p_old_numerator => 'c_approved_amt_total',
404 p_denominator => 'c_approved_amt_total',
405 p_measure_name => 'ISC_MEASURE_27') ||',--GT (Bill-to-Approved Var Percent) Current
406
407 ' || isc_dbi_sutil_pkg.change_rate_str (
408 p_new_numerator => '(c_bill_amt_total - c_approved_amt_total)',
409 p_new_denominator => 'c_approved_amt_total',
410 p_old_numerator => '(p_bill_amt_total - p_approved_amt_total)',
411 p_old_denominator => 'p_approved_amt_total',
412 p_rate_type => 'RATIO',
413 p_measure_name => 'ISC_MEASURE_28'); -- GT Bill-to-Approved Variance Percent Change
414
415
416 RETURN l_sel_clause;
417
418 END get_rpt_sel_clause;
419
421 END ISC_DBI_CARR_BILL_PAY_PKG;
420