[Home] [Help]
PACKAGE BODY: APPS.FII_AP_PAY_MGT
Source
1 PACKAGE BODY FII_AP_PAY_MGT AS
2 /* $Header: FIIAPPMB.pls 120.2 2006/08/22 12:20:45 sajgeo noship $ */
3
4
5 /* Package for Holds Graph portlet */
6 PROCEDURE get_hold_cat_graph (
7 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
8 inv_graph_sql OUT NOCOPY VARCHAR2,
9 inv_graph_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
10 IS
11 l_viewby_dim VARCHAR2(240); -- what is the viewby
12 l_as_of_date DATE;
13 l_organization VARCHAR2(240);
14 l_supplier VARCHAR2(240);
15 l_currency VARCHAR2(240);
16 l_viewby_id VARCHAR2(240); -- org_id or supplier_id
17 l_record_type_id NUMBER; --
18 l_gid NUMBER; -- 0,4 or 8
19 l_viewby_string VARCHAR2(240);
20 inv_trend_rec BIS_QUERY_ATTRIBUTES;
21 l_param_join VARCHAR2(240);
22 l_cur_period NUMBER;
23 l_id_column VARCHAR2(100);
24 sqlstmt VARCHAR2(14000);
25 l_invoice_number VARCHAR2(240);
26 l_org_WHERE VARCHAR2(240);
27 l_supplier_WHERE VARCHAR2(240);
28 l_period_type VARCHAR2(240);
29 BEGIN
30
31 FII_PMV_UTIL.Get_Parameters(
32 p_page_parameter_tbl,
33 l_as_of_date,
34 l_organization,
35 l_supplier,
36 l_invoice_number,
37 l_period_type,
38 l_record_type_id,
39 l_viewby_dim,
40 l_currency,
41 l_viewby_id,
42 l_viewby_string,
43 l_gid,
44 l_org_WHERE,
45 l_supplier_WHERE
46 );
47
48 -- l_record_type_id := 1143; /*removing this change made for bug no.3118619*/
49
50 /*-----------------------------------------------------+
51 | FII_MEASURE1 - Hold Category |
52 | FII_MEASURE2 - Number of Holds |
53 +-----------------------------------------------------*/
54
55 -- construct the sql statement
56
57 sqlstmt := '
58 SELECT DECODE(t.multiplier,1, fnd_message.get_string(''FII'',''FII_AP_HOLD_PO''),
59 2, fnd_message.get_string(''FII'',''FII_AP_HOLD_VAR''),
60 3, fnd_message.get_string(''FII'',''FII_AP_HOLD_INV''),
61 4, fnd_message.get_string(''FII'',''FII_AP_HOLD_USR''),
62 5, fnd_message.get_string(''FII'',''FII_AP_HOLD_OTR''), null) FII_MEASURE1,
63 DECODE(t.multiplier, 1, SUM(po_matching_hold_count),
64 2, SUM(variance_hold_count) ,
65 3, SUM(invoice_hold_count),
66 4, SUM(user_defined_hold_count),
67 5, SUM(other_hold_count)) FII_MEASURE2
68 FROM fii_ap_hcat_ib_mv f,
69 gl_row_multipliers t,
70 fii_time_structures cal
71 WHERE f.time_id = cal.time_id
72 AND f.period_type_id = cal.period_type_id
73 '||l_supplier_where||' '||l_org_where||'
74 AND bitand(cal.record_type_id,:RECORD_TYPE_ID) = :RECORD_TYPE_ID
75 AND f.hold_release_flag = ''H'' /*added this code for bugno. 3108542*/
76 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE)
77 AND f.gid = :GID
78 AND t.multiplier in (1,2,3,4,5)
79 GROUP by t.multiplier';
80
81
82 FII_PMV_UTIL.bind_variable(
83 p_sqlstmt=>sqlstmt,
84 p_page_parameter_tbl=>p_page_parameter_tbl,
85 p_sql_output=>inv_graph_sql,
86 p_bind_output_table=>inv_graph_output,
87 p_invoice_number=>l_invoice_number,
88 p_record_type_id=>l_record_type_id,
89 p_view_by=>l_viewby_id,
90 p_gid=>l_gid);
91
92 END get_hold_cat_graph;
93
94
95 PROCEDURE get_late_ontime_payment (
96 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
97 inv_graph_sql OUT NOCOPY VARCHAR2,
98 inv_graph_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
99 IS
100 l_viewby_dim VARCHAR2(240); -- what is the viewby
101 l_as_of_date DATE;
102 l_organization VARCHAR2(240);
103 l_supplier VARCHAR2(240);
104 l_currency VARCHAR2(240);
105 l_viewby_id VARCHAR2(240); -- org_id or supplier_id
106 l_record_type_id NUMBER; --
107 l_gid NUMBER; -- 0,4 or 8
108 l_viewby_string VARCHAR2(240);
109 l_param_join VARCHAR2(240);
110 l_cur_period NUMBER;
111 l_id_column VARCHAR2(100);
112 sqlstmt VARCHAR2(14000);
113 l_invoice_number VARCHAR2(240);
114 l_org_WHERE VARCHAR2(240);
115 l_supplier_WHERE VARCHAR2(240);
116 l_period_type VARCHAR2(240);
117 l_period_start DATE;
118 l_report_start DATE;
119 l_cur_effective_num NUMBER;
120 l_paid_on_time_count VARCHAR2(240);
121 l_paid_late_count VARCHAR2(240);
122 BEGIN
123
124 FII_PMV_UTIL.Get_Parameters(
125 p_page_parameter_tbl,
126 l_as_of_date,
127 l_organization,
128 l_supplier,
129 l_invoice_number,
130 l_period_type,
131 l_record_type_id,
132 l_viewby_dim,
133 l_currency,
134 l_viewby_id,
135 l_viewby_string,
136 l_gid,
137 l_org_WHERE,
138 l_supplier_WHERE
139 );
140
141
142 l_paid_late_count := 'paid_late_count'||FII_PMV_UTIL.get_period_type_suffix (l_period_type);
143 l_paid_on_time_count := 'paid_on_time_count'||FII_PMV_UTIL.get_period_type_suffix (l_period_type);
144
145
146 /*------------------------------------------------------+
147 | FII_MEASURE1 - Prior Paid Late |
148 | FII_MEASURE2 - Prior Paid on Time |
149 | FII_MEASURE3 - Invoice Paid Late |
150 | FII_MEASURE4 - Invoice Paid on Time |
151 +------------------------------------------------------*/
152
153 -- construct the sql statement
154
155
156 sqlstmt := '
157 SELECT
158 viewby_dim.value VIEWBY,
159 viewby_dim.id VIEWBYID,
160 sum(f.FII_MEASURE1) FII_MEASURE1,
161 sum(f.FII_MEASURE2) FII_MEASURE2,
162 sum(f.FII_MEASURE3) FII_MEASURE3,
163 sum(f.FII_MEASURE4) FII_MEASURE4
164 from
165 (select id,
166 FII_MEASURE1,
167 FII_MEASURE2,
168 FII_MEASURE3,
169 FII_MEASURE4,
170 ( rank() over (order by ID asc)) - 1 rnk
171 from
172 (select f.'||l_viewby_id||' id,
173 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
174 THEN f.'||l_paid_late_count||' ELSE 0 END) FII_MEASURE1,
175 SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
176 THEN f.'||l_paid_late_count||' ELSE 0 END) FII_MEASURE2,
177 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE
178 THEN f.'||l_paid_on_time_count||' ELSE 0 END) FII_MEASURE3,
179 SUM(CASE WHEN cal.report_date = &BIS_PREVIOUS_ASOF_DATE
180 THEN f.'||l_paid_on_time_count||' ELSE 0 END) FII_MEASURE4
181 FROM FII_AP_PAYOL_XB_MV f,
182 fii_time_structures cal
183 WHERE f.time_id = cal.time_id
184 AND f.period_type_id = cal.period_type_id
185 AND bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
186 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
187 AND f.gid = :GID
188 '||l_org_WHERE||' '||l_supplier_WHERE||'
189 group by f.'||l_viewby_id||')) f,
190 '||l_viewby_string||' viewby_dim
191 where f.id = viewby_dim.id
192 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
193 GROUP BY viewby_dim.value, viewby_dim.id
194 &ORDER_BY_CLAUSE';
195
196
197 FII_PMV_UTIL.bind_variable(
198 p_sqlstmt=>sqlstmt,
199 p_page_parameter_tbl=>p_page_parameter_tbl,
200 p_sql_output=>inv_graph_sql,
201 p_bind_output_table=>inv_graph_output,
202 p_invoice_number=>l_invoice_number,
203 p_record_type_id=>l_record_type_id,
204 p_view_by=>l_viewby_id,
205 p_gid=>l_gid);
206
207 END get_late_ontime_payment;
208
209
210 /* For Invoices Graph Report */
211
212 PROCEDURE get_inv_graph (
213 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
214 inv_graph_sql OUT NOCOPY VARCHAR2,
215 inv_graph_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
216 IS
217 l_stmt VARCHAR2(10000);
218 l_page_period_type VARCHAR2(32000);
219 l_pk VARCHAR2(30);
220 l_name VARCHAR2(100);
221 l_actual_period_type NUMBER;
222 l_period_type VARCHAR2(1000);
223 l_week_num NUMBER;
224 inv_graph_rec BIS_QUERY_ATTRIBUTES;
225 l_time_comp VARCHAR2(20);
226 l_as_of_date DATE;
227 l_p_as_of_date DATE;
228 l_ent_pyr_start DATE;
229 l_ent_pyr_END DATE;
230 l_ent_cyr_start DATE;
231 l_ent_cyr_END DATE;
232 l_cy_period_END DATE;
233 l_start DATE;
234 l_curr_effective_num NUMBER;
235 i NUMBER;
236 l_begin_date DATE;
237 l_currency VARCHAR2(240);
238 l_organization VARCHAR2(240);
239 l_supplier VARCHAR2(240);
240 l_org_WHERE VARCHAR2(240);
241 l_supplier_WHERE VARCHAR2(240);
242 l_invoice_number NUMBER;
243 l_record_type_id NUMBER;
244 l_viewby_dim VARCHAR2(240);
245 l_viewby_id VARCHAR2(240);
246 l_viewby_string VARCHAR2(240);
247 l_gid NUMBER;
248 l_start_date DATE;
249 BEGIN
250 FII_PMV_Util.Get_Parameters(
251 p_page_parameter_tbl,
252 l_as_of_date,
253 l_organization,
254 l_supplier,
255 l_invoice_number,
256 l_period_type,
257 l_record_type_id,
258 l_viewby_dim,
259 l_currency,
260 l_viewby_id,
261 l_viewby_string,
262 l_gid,
263 l_org_WHERE,
264 l_supplier_WHERE
265 );
266 /* Removing the hard coded value for bug # 3262629*/
267 -- l_gid := 4;
268
269 l_week_num := 13;
270 inv_graph_output := BIS_QUERY_ATTRIBUTES_TBL();
271 inv_graph_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
272
273 IF (p_page_parameter_tbl.count > 0) THEN
274 i:= p_page_parameter_tbl.first;
275 FOR cnt in 1..p_page_parameter_tbl.count LOOP
276 IF p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' THEN
277 l_page_period_type := p_page_parameter_tbl(i).parameter_value;
278 ELSIF p_page_parameter_tbl(i).parameter_name = 'AS_OF_DATE' THEN
279 l_as_of_date := to_date(p_page_parameter_tbl(i).parameter_value,'DD/MM/YYYY');
280 ELSIF p_page_parameter_tbl(i).parameter_name= 'TIME_COMPARISON_TYPE' THEN
281 l_time_comp := p_page_parameter_tbl(i).parameter_value;
282 END IF;
283 i := p_page_parameter_tbl.next(i);
284 END LOOP;
285 END IF;
286
287
288 CASE l_page_period_type
289 WHEN 'FII_TIME_WEEK' THEN l_actual_period_type := 32;
290 WHEN 'FII_TIME_ENT_PERIOD' THEN l_actual_period_type := 64;
291 WHEN 'FII_TIME_ENT_QTR' THEN l_actual_period_type := 128;
292 WHEN 'FII_TIME_ENT_YEAR' THEN l_actual_period_type := 256;
293 END CASE;
294
295 select nvl(min(start_date), trunc(sysdate)) into l_start_date from fii_time_ent_year;
296
297 --l_ent_pyr_start := fii_time_api.ent_pyr_start(l_as_of_date);
298 select nvl(fii_time_api.ent_pyr_END(l_as_of_date), l_start_date-1) into l_ent_pyr_END from dual;
299 select nvl(fii_time_api.ent_cyr_start(l_as_of_date), l_start_date) into l_ent_cyr_start from dual;
300 select nvl(fii_time_api.ent_cyr_END(l_as_of_date), l_start_date) into l_ent_cyr_END from dual;
301
302 select nvl(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start
303 (fii_time_api.ent_pyr_start(l_as_of_date))),l_start_date-1)
304 into l_ent_pyr_start from dual; /* Bug 3325387 */
305
306 CASE l_page_period_type
307 WHEN 'FII_TIME_WEEK' THEN
308 l_period_type := 16;
309 l_pk := 'week_id';
310 inv_graph_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
311 inv_graph_rec.attribute_value := 'TIME+FII_TIME_WEEK';
312 inv_graph_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
313 l_name := 'replace(fnd_message.get_string(''FII'',''FII_WEEK_LABEL''),''&WEEK_NUMBER'',t.sequence)';
314 select nvl(fii_time_api.pwk_END(l_as_of_date), l_start_date) into l_cy_period_END from dual;
315 select nvl(fii_time_api.sd_lyswk(l_as_of_date), l_start_date) into l_p_as_of_date from dual;
316 select nvl(fii_time_api.sd_lyswk(l_p_as_of_date), l_start_date) into l_start from dual;
317 l_begin_date := l_as_of_date - 91;
318 SELECT sequence into l_curr_effective_num
319 FROM fii_time_week
320 WHERE l_as_of_date between start_date AND END_date;
321
322 WHEN 'FII_TIME_ENT_PERIOD' THEN
323 l_period_type := 32;
324 l_pk := 'ent_period_id';
325 -- l_bis_map_rec.key:= BIS_PMV_QUERY_PVT.VIEW_BY_KEY;
326 -- l_bis_map_rec.value:= 'TIME+FII_TIME_ENT_PERIOD';
327 inv_graph_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
328 inv_graph_rec.attribute_value :='TIME+FII_TIME_ENT_PERIOD';
329 inv_graph_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
330 l_name := 'to_char(t.start_date,''Mon'')';
331 select nvl(fii_time_api.ent_pper_END(l_as_of_date), l_start_date) into l_cy_period_END from dual;
332 select nvl(fii_time_api.ent_sd_lysper_END(l_as_of_date), l_start_date) into l_p_as_of_date from dual;
333 select nvl(fii_time_api.ent_sd_lysper_END(l_p_as_of_date), l_start_date) into l_start from dual;
334 l_begin_date := l_p_as_of_date;
335 SELECT sequence into l_curr_effective_num
336 FROM fii_time_ent_period
337 WHERE l_as_of_date between start_date AND END_date;
338
339 WHEN 'FII_TIME_ENT_QTR' THEN
340 l_period_type := 64;
341 l_pk := 'ent_qtr_id';
342 -- l_bis_map_rec.key:= BIS_PMV_QUERY_PVT.VIEW_BY_KEY;
343 -- l_bis_map_rec.value:= 'TIME+FII_TIME_ENT_QTR';
344 inv_graph_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
345 inv_graph_rec.attribute_value :='TIME+FII_TIME_ENT_QTR';
346 inv_graph_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
347 l_name :=
348 'replace(fnd_message.get_string(''FII'',''FII_QUARTER_LABEL''),''&QUARTER_NUMBER'',t.sequence)';
349 select nvl(fii_time_api.ent_pqtr_END(l_as_of_date), l_start_date) into l_cy_period_END from dual;
350 IF (l_time_comp = 'SEQUENTIAL') THEN
351 select nvl(fii_time_api.ent_sd_lysqtr_END(l_as_of_date), l_start_date) into l_p_as_of_date from dual;
352 SELECT ent_qtr_id into l_curr_effective_num
353 FROM fii_time_ent_qtr
354 WHERE l_as_of_date between start_date AND END_date;
355 select nvl(fii_time_api.ent_sd_lysqtr_END(fii_time_api.ent_sd_lysqtr_END(l_p_as_of_date)), l_start_date) into l_begin_date from dual;
356 select nvl(fii_time_api.ent_sd_lysqtr_END(l_p_as_of_date), l_start_date-1) into l_start from dual;
357 ELSE
358 select nvl(fii_time_api.ent_sd_lysqtr_END(l_as_of_date), l_start_date) into l_p_as_of_date from dual;
359 SELECT sequence into l_curr_effective_num
360 FROM fii_time_ent_qtr
361 WHERE l_as_of_date between start_date AND END_date;
362 l_begin_date := l_p_as_of_date;
363 select nvl(fii_time_api.ent_sd_lysqtr_END(l_p_as_of_date), l_start_date) into l_start from dual;
364 END IF;
365
369 -- l_bis_map_rec.key:= BIS_PMV_QUERY_PVT.VIEW_BY_KEY;
366 WHEN 'FII_TIME_ENT_YEAR' THEN
367 l_period_type := 128;
368 l_pk := 'ent_year_id';
370 -- l_bis_map_rec.value:= 'TIME+FII_TIME_ENT_YEAR';
371 inv_graph_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
372 inv_graph_rec.attribute_value :='TIME+FII_TIME_ENT_YEAR';
373 inv_graph_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
374
375 select nvl(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(l_ent_cyr_start))), l_start_date) into l_begin_date from dual;
376 END CASE;
377
378 -- p_bis_map_tbl.extEND;
379 -- p_bis_map_tbl(p_bis_map_tbl.count) := l_bis_map_rec;
380 -- l_bis_map_rec.key := BIS_PMV_QUERY_PVT.QUERY_STR_KEY;
381 inv_graph_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
382 inv_graph_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
383 inv_graph_output.EXTEND;
384 inv_graph_output(inv_graph_output.COUNT) := inv_graph_rec;
385
386 /*------------------------------------------------------+
387 | FII_MEASURE1 - Electronic Invoices Entered |
388 | FII_MEASURE2 - Manual Invoices Entered |
389 | FII_MEASURE3 - Name of the period |
390 +------------------------------------------------------*/
391
392 IF l_page_period_type = 'FII_TIME_ENT_YEAR' THEN
393 -- l_bis_map_rec.value := '
394
395 inv_graph_sql := 'SELECT
396 t.name FII_MEASURE3,
397 max(inline_view.e_invoice_count) FII_MEASURE1,
398 max(inline_view.m_invoice_count) FII_MEASURE2
399 FROM
400 (SELECT inner_inline_view.FII_SEQUENCE FII_SEQUENCE,
401 SUM(e_invoice_count) e_invoice_count,
402 SUM(m_invoice_count) m_invoice_count
403 FROM
404 ( SELECT
405 t.sequence FII_SEQUENCE,
406 f.e_invoice_count e_invoice_count,
407 (f.invoice_count_entered - f.e_invoice_count) m_invoice_count
408
409 FROM FII_AP_IVATY_XB_MV f,
410 '||l_page_period_type||' t
411 WHERE f.gid = :GID
412 AND f.time_id = t.'||l_pk||'
413 AND f.period_type_id = :FII_BIND6
414 AND t.start_date between
415 to_date(:FII_BIND9, ''DD/MM/YYYY'') /* Bug 3325387 */
416
417 AND to_date(:FII_BIND10, ''DD/MM/YYYY'')
418 '||l_org_WHERE||' '||l_supplier_WHERE||'
419
420 UNION ALL
421 SELECT
422 t.sequence FII_SEQUENCE,
423 f.e_invoice_count e_invoice_count,
424 (f.invoice_count_entered - f.e_invoice_count) m_invoice_count
425
426 FROM FII_AP_IVATY_XB_MV f,
427 fii_time_structures cal,
428 '||l_page_period_type||' t ,
429 fii_time_day day
430
431 WHERE f.gid = :GID
432 AND f.period_type_id = cal.period_type_id
433 AND f.time_id = cal.time_id
434 AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1 /*made changes for bug no.3108435*/
435 AND cal.report_date = &BIS_CURRENT_ASOF_DATE
436 AND cal.report_date = day.report_date
437 AND day.'||l_pk||' = t.'||l_pk||'
438 '||l_org_WHERE||' '||l_supplier_WHERE||'
439
440 ) inner_inline_view
441 GROUP BY inner_inline_view.FII_SEQUENCE
442
443 ) inline_view, '||l_page_period_type||' t
444
445 WHERE FII_SEQUENCE (+)= t.sequence
446 AND t.start_date >= to_date(:FII_BIND14, ''DD/MM/YYYY'')
447 AND t.END_date <= to_date(:FII_BIND12, ''DD/MM/YYYY'')
448 GROUP BY t.sequence, t.name, t.'||l_pk||'
449 ORDER BY t.sequence';
450
451
452 ELSIF ((l_page_period_type = 'FII_TIME_ENT_QTR') AND (l_time_comp = 'SEQUENTIAL'))THEN
453
454 inv_graph_sql := 'SELECT
455 t.name FII_MEASURE3,
456 inline_view.e_invoice_count FII_MEASURE1,
457 inline_view.m_invoice_count FII_MEASURE2
458
459 FROM
460 ( SELECT
461 inner_inline_view.FII_SEQUENCE FII_EFFECTIVE_NUM,
462 SUM(e_invoice_count) e_invoice_count,
463 SUM(m_invoice_count) m_invoice_count
464
465 FROM
466 ( SELECT
467 t.'||l_pk||' FII_SEQUENCE,
468 (CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN
469 (CASE WHEN t.start_date > to_date(:FII_BIND7,''DD/MM/YYYY'')
470 AND t.start_date <= to_date(:FII_BIND8, ''DD/MM/YYYY'')
471 THEN f.e_invoice_count ELSE TO_NUMBER(NULL) END)
472 ELSE TO_NUMBER(NULL) END ) e_invoice_count,
473
474 (CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN
475 (CASE WHEN t.start_date > to_date(:FII_BIND7,''DD/MM/YYYY'')
476 AND t.start_date <= to_date(:FII_BIND8, ''DD/MM/YYYY'')
480 FROM FII_AP_IVATY_XB_MV f,
477 THEN (f.invoice_count_entered - f.e_invoice_count) ELSE TO_NUMBER(NULL) END)
478 ELSE TO_NUMBER(NULL) END ) m_invoice_count
479
481 '||l_page_period_type||' t
482
483 WHERE f.gid = :GID
484 AND f.time_id = t.'||l_pk||'
485 AND f.period_type_id = :FII_BIND6
486 AND t.start_date between to_date(:FII_BIND13, ''DD/MM/YYYY'') AND &BIS_CURRENT_ASOF_DATE
487 '||l_org_WHERE||' '||l_supplier_WHERE||'
488 UNION ALL
489
490 SELECT
491 t.'||l_pk||' FII_SEQUENCE,
492 (CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN
493 (CASE WHEN t.start_date > to_date(:FII_BIND13,''DD/MM/YYYY'')
494 AND t.start_date <= to_date(:FII_BIND7, ''DD/MM/YYYY'')
495 THEN f.e_invoice_count ELSE TO_NUMBER(NULL) END)
496 ELSE TO_NUMBER(NULL) END ) e_invoice_count,
497
498 (CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN
499 (CASE WHEN t.start_date > to_date(:FII_BIND13,''DD/MM/YYYY'')
500 AND t.start_date <= to_date(:FII_BIND7, ''DD/MM/YYYY'')
501 THEN (f.invoice_count_entered - f.e_invoice_count)
502 ELSE TO_NUMBER(NULL) END) ELSE TO_NUMBER(NULL) END ) m_invoice_count
503
504 FROM FII_AP_IVATY_XB_MV f,
505 '||l_page_period_type||' t
506
507 WHERE f.gid = :GID
508 AND f.time_id = t.'||l_pk||'
509 AND f.period_type_id = :FII_BIND6
510 AND t.start_date between to_date(:FII_BIND14, ''DD/MM/YYYY'')
511 AND to_date(:FII_BIND7, ''DD/MM/YYYY'')
512 '||l_org_WHERE||' '||l_supplier_WHERE||'
513
514 UNION ALL
515
516 SELECT :FII_BIND5 FII_SEQUENCE,
517 (CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE AND
518 bitand(cal.record_type_id, :FII_BIND1) = :FII_BIND1
519 THEN f.e_invoice_count ELSE TO_NUMBER(NULL) END) e_invoice_count,
520
521 (CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE AND
522 bitand(cal.record_type_id, :FII_BIND1) = :FII_BIND1
523 THEN (f.invoice_count_entered - f.e_invoice_count) ELSE TO_NUMBER(NULL) END) m_invoice_count
524
525 FROM FII_AP_IVATY_XB_MV f,
526 fii_time_structures cal
527
528 WHERE f.gid = :GID
529 AND f.time_id = cal.time_id
530 AND f.period_type_id = cal.period_type_id
531 AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
532 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE, (to_date(:FII_BIND7, ''DD/MM/YYYY'')) )
533 '||l_org_WHERE||' '||l_supplier_WHERE||'
534
535 ) inner_inline_view
536
537 GROUP BY inner_inline_view.FII_SEQUENCE
538
539 ) inline_view, '||l_page_period_type||' t
540
541 WHERE inline_view.fii_effective_num (+)= t.'||l_pk||'
542 AND t.start_date <= &BIS_CURRENT_ASOF_DATE
543 AND t.start_date > to_date(:FII_BIND13, ''DD/MM/YYYY'')
544 ORDER BY t.start_date';
545
546 ELSE
547 inv_graph_sql := '
548 SELECT
549 t.name FII_MEASURE3,
550 inline_view.e_invoice_count FII_MEASURE1,
551 inline_view.m_invoice_count FII_MEASURE2
552 FROM
553 (
554 SELECT inner_inline_view.FII_SEQUENCE FII_EFFECTIVE_NUM,
555 SUM(e_invoice_count) e_invoice_count,
556 SUM(m_invoice_count) m_invoice_count
557 FROM
558 (
559 SELECT
560 t.sequence FII_SEQUENCE,
561 (CASE WHEN t.sequence <> :FII_BIND5 THEN (CASE WHEN t.start_date between
562 to_date(:FII_BIND7,''DD/MM/YYYY'') AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
563 THEN f.e_invoice_count ELSE TO_NUMBER(NULL) END) ELSE TO_NUMBER(NULL) END ) e_invoice_count,
564
565 (CASE WHEN t.sequence <> :FII_BIND5 THEN (CASE WHEN t.start_date between
566 to_date(:FII_BIND7,''DD/MM/YYYY'') AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
567 THEN (f.invoice_count_entered - f.e_invoice_count) ELSE TO_NUMBER(NULL) END) ELSE TO_NUMBER(NULL) END ) m_invoice_count
568
569 FROM FII_AP_IVATY_XB_MV f,
570 '||l_page_period_type||' t
571
572 WHERE f.gid = :GID
573 AND f.time_id = t.'||l_pk||'
574 AND f.period_type_id = :FII_BIND6
575 AND t.start_date between to_date(:FII_BIND13, ''DD/MM/YYYY'') AND &BIS_CURRENT_ASOF_DATE
576 '||l_org_WHERE||' '||l_supplier_WHERE||'
577
578 UNION ALL
579
580 SELECT
581 :FII_BIND5 FII_SEQUENCE,
585
582 (CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE AND
583 bitand(cal.record_type_id, :FII_BIND1) = :FII_BIND1
584 THEN f.e_invoice_count ELSE TO_NUMBER(NULL) END ) e_invoice_count,
586 (CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE AND
587 bitand(cal.record_type_id, :FII_BIND1) = :FII_BIND1
588 THEN (f.invoice_count_entered - f.e_invoice_count) ELSE TO_NUMBER(NULL) END ) m_invoice_count
589
590 FROM FII_AP_IVATY_XB_MV f,
591 fii_time_structures cal
592 WHERE f.gid = :GID
593 AND f.period_type_id = cal.period_type_id
594 AND f.time_id = cal.time_id
595 AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
596 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE, (to_date(:FII_BIND7, ''DD/MM/YYYY'')))
597 '||l_org_WHERE||' '||l_supplier_WHERE||'
598
599 ) inner_inline_view
600
601 GROUP BY inner_inline_view.FII_SEQUENCE
602
603 ) inline_view, '||l_page_period_type||' t
604 WHERE inline_view.fii_effective_num (+)= t.sequence
605 AND t.start_date <= &BIS_CURRENT_ASOF_DATE
606 AND t.start_date > to_date(:FII_BIND14, ''DD/MM/YYYY'')
607 ORDER BY t.start_date';
608
609 END IF;
610
611 inv_graph_output.EXTEND;
612 inv_graph_rec.attribute_name := ':FII_BIND1';
613 inv_graph_rec.attribute_value := TO_CHAR(l_actual_period_type);
614 inv_graph_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
615 inv_graph_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
616 inv_graph_output(inv_graph_output.COUNT) := inv_graph_rec;
617 inv_graph_output.EXTEND;
618 inv_graph_rec.attribute_name := ':FII_BIND5';
619 inv_graph_rec.attribute_value := TO_CHAR(l_curr_effective_num);
620 inv_graph_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
621 inv_graph_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
622 inv_graph_output(inv_graph_output.COUNT) := inv_graph_rec;
623 inv_graph_output.EXTEND;
624 inv_graph_rec.attribute_name := ':FII_BIND6';
625 inv_graph_rec.attribute_value := TO_CHAR(l_period_type);
626 inv_graph_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
627 inv_graph_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
628 inv_graph_output(inv_graph_output.COUNT) := inv_graph_rec;
629 inv_graph_output.EXTEND;
630 inv_graph_rec.attribute_name := ':FII_BIND7';
631 inv_graph_rec.attribute_value := TO_CHAR(l_p_as_of_date,'DD/MM/YYYY');
632 inv_graph_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
633 inv_graph_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
634 inv_graph_output(inv_graph_output.COUNT) := inv_graph_rec;
635 inv_graph_output.EXTEND;
636 inv_graph_rec.attribute_name := ':FII_BIND8';
637 inv_graph_rec.attribute_value := TO_CHAR(l_cy_period_END,'DD/MM/YYYY');
638 inv_graph_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
639 inv_graph_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
640 inv_graph_output(inv_graph_output.COUNT) := inv_graph_rec;
641 inv_graph_output.EXTEND;
642 inv_graph_rec.attribute_name := ':FII_BIND9';
643 inv_graph_rec.attribute_value := TO_CHAR(l_ent_pyr_start,'DD/MM/YYYY');
644 inv_graph_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
645 inv_graph_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
646 inv_graph_output(inv_graph_output.COUNT) := inv_graph_rec;
647 inv_graph_output.EXTEND;
648 inv_graph_rec.attribute_name := ':FII_BIND10';
649 inv_graph_rec.attribute_value := TO_CHAR(l_ent_pyr_END,'DD/MM/YYYY');
650 inv_graph_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
651 inv_graph_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
652 inv_graph_output(inv_graph_output.COUNT) := inv_graph_rec;
653 inv_graph_output.EXTEND;
654 inv_graph_rec.attribute_name := ':FII_BIND11';
655 inv_graph_rec.attribute_value := TO_CHAR(l_ent_cyr_start,'DD/MM/YYYY');
656 inv_graph_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
657 inv_graph_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
658 inv_graph_output(inv_graph_output.COUNT) := inv_graph_rec;
659 inv_graph_output.EXTEND;
660 inv_graph_rec.attribute_name := ':FII_BIND12';
661 inv_graph_rec.attribute_value := TO_CHAR(l_ent_cyr_END,'DD/MM/YYYY');
662 inv_graph_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
663 inv_graph_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
664 inv_graph_output(inv_graph_output.COUNT) := inv_graph_rec;
665 inv_graph_output.EXTEND;
666 inv_graph_rec.attribute_name := ':FII_BIND13';
667 inv_graph_rec.attribute_value := TO_CHAR(l_start,'DD/MM/YYYY');
668 inv_graph_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
669 inv_graph_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
670 inv_graph_output(inv_graph_output.COUNT) := inv_graph_rec;
671 inv_graph_output.EXTEND;
672 inv_graph_rec.attribute_name := ':FII_BIND14';
673 inv_graph_rec.attribute_value := TO_CHAR(l_begin_date,'DD/MM/YYYY');
674 inv_graph_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
675 inv_graph_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
676 inv_graph_output(inv_graph_output.COUNT) := inv_graph_rec;
680 inv_graph_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
677 inv_graph_output.EXTEND;
678 inv_graph_rec.attribute_name := ':GID';
679 inv_graph_rec.attribute_value := to_char(l_gid);
681 inv_graph_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
682 inv_graph_output(inv_graph_output.COUNT) := inv_graph_rec;
683 inv_graph_output.EXTEND;
684 inv_graph_rec.attribute_name := ':SEC_ID';
685 inv_graph_rec.attribute_value := fii_pmv_util.get_sec_profile;
686 inv_graph_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
687 inv_graph_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
688 inv_graph_output(inv_graph_output.COUNT) := inv_graph_rec;
689 inv_graph_output.EXTEND;
690
691 END get_inv_graph;
692
693
694 /* For Electronic Invoices and Late Payments */
695
696
697 PROCEDURE get_elec_late_payment (p_page_parameter_tbl in BIS_PMV_PAGE_PARAMETER_TBL,
698 elec_late_payment_sql out NOCOPY VARCHAR2, elec_late_payment_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
699 IS
700 l_stmt VARCHAR2(10000);
701 l_page_period_type VARCHAR2(32000);
702 l_pk VARCHAR2(30);
703 l_name VARCHAR2(100);
704 l_actual_period_type NUMBER;
705 l_period_type VARCHAR2(1000);
706 l_week_num NUMBER;
707 elec_late_payment_rec BIS_QUERY_ATTRIBUTES;
708 l_time_comp VARCHAR2(20);
709 l_as_of_date DATE;
710 l_p_as_of_date DATE;
711 l_ent_pyr_start DATE;
712 l_ent_pyr_END DATE;
713 l_ent_cyr_start DATE;
714 l_ent_cyr_END DATE;
715 l_cy_period_END DATE;
716 l_start DATE;
717 l_curr_effective_num NUMBER;
718 i NUMBER;
719 l_begin_date DATE;
720 l_period_suffix VARCHAR2(240);
721 l_currency VARCHAR2(240);
722 l_organization VARCHAR2(240);
723 l_supplier VARCHAR2(240);
724 l_org_WHERE VARCHAR2(240);
725 l_supplier_WHERE VARCHAR2(240);
726 l_invoice_number NUMBER;
727 l_record_type_id NUMBER;
728 l_viewby_dim VARCHAR2(240);
729 l_viewby_id VARCHAR2(240);
730 l_viewby_string VARCHAR2(240);
731 l_gid NUMBER;
732 ltd VARCHAR2(4);
733 l_ent_year_st1 DATE;
734 l_ent_year_st2 DATE;
735
736 l_start_date DATE;
737
738 BEGIN
739 FII_PMV_Util.Get_Parameters(
740 p_page_parameter_tbl,
741 l_as_of_date,
742 l_organization,
743 l_supplier,
744 l_invoice_number,
745 l_period_type,
746 l_record_type_id,
747 l_viewby_dim,
748 l_currency,
749 l_viewby_id,
750 l_viewby_string,
751 l_gid,
752 l_org_WHERE,
753 l_supplier_WHERE
754 );
755
756
757 l_week_num := 13;
758 elec_late_payment_output := BIS_QUERY_ATTRIBUTES_TBL();
759 elec_late_payment_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
760
761 IF (p_page_parameter_tbl.count > 0) THEN
762 i:= p_page_parameter_tbl.first;
763 FOR cnt in 1..p_page_parameter_tbl.count LOOP
764 IF p_page_parameter_tbl(i).parameter_name = 'PERIOD_TYPE' THEN
765 l_page_period_type := p_page_parameter_tbl(i).parameter_value;
766 ELSIF p_page_parameter_tbl(i).parameter_name = 'AS_OF_DATE' THEN
767 l_as_of_date := to_date(p_page_parameter_tbl(i).parameter_value,'DD/MM/YYYY');
768 ELSIF p_page_parameter_tbl(i).parameter_name= 'TIME_COMPARISON_TYPE' THEN
769 l_time_comp := p_page_parameter_tbl(i).parameter_value;
770
771 END IF;
772 i := p_page_parameter_tbl.next(i);
773 END LOOP;
774 END IF;
775
776 CASE l_page_period_type
777 WHEN 'FII_TIME_WEEK' THEN l_actual_period_type := 32;
778 WHEN 'FII_TIME_ENT_PERIOD' THEN l_actual_period_type := 64;
779 WHEN 'FII_TIME_ENT_QTR' THEN l_actual_period_type := 128;
780 WHEN 'FII_TIME_ENT_YEAR' THEN l_actual_period_type := 256;
781 END CASE;
782
783 select nvl(min(start_date), trunc(sysdate)) into l_start_date from fii_time_ent_year;
784
785 select fii_time_api.ent_pyr_start(l_as_of_date) into l_ent_pyr_start from dual;
786 select nvl(fii_time_api.ent_pyr_END(l_as_of_date), l_start_date-1) into l_ent_pyr_END from dual;
787 select fii_time_api.ent_cyr_start(l_as_of_date) into l_ent_cyr_start from dual;
788 select nvl(fii_time_api.ent_cyr_END(l_as_of_date), l_start_date) into l_ent_cyr_END from dual;
789
790
791 select nvl(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(l_ent_pyr_start)),l_start_date-1)
792 into l_ent_year_st1 from dual;
793 select nvl(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(fii_time_api.ent_pyr_start(l_ent_cyr_start))),l_start_date-1)
794 into l_ent_year_st2 from dual;
795
796 CASE l_page_period_type
797 WHEN 'FII_TIME_WEEK' THEN
798 l_period_type := 16;
799 l_pk := 'week_id';
800 ltd := '_WTD';
801 elec_late_payment_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
805 ''&WEEK_NUMBER'',t.sequence)';
802 elec_late_payment_rec.attribute_value := 'TIME+FII_TIME_WEEK';
803 elec_late_payment_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
804 l_name := 'replace(fnd_message.get_string(''FII'',''FII_WEEK_LABEL''),
806 select nvl(fii_time_api.pwk_END(l_as_of_date), l_start_date) into l_cy_period_END from dual;
807 select nvl(fii_time_api.sd_lyswk(l_as_of_date), l_start_date) into l_p_as_of_date from dual;
808 select nvl(fii_time_api.sd_lyswk(l_p_as_of_date), l_start_date) into l_start from dual;
809 l_begin_date := l_as_of_date - 91;
810
811 SELECT sequence into l_curr_effective_num
812 FROM fii_time_week
813 WHERE l_as_of_date between start_date AND END_date;
814
815 WHEN 'FII_TIME_ENT_PERIOD' THEN
816 l_period_type := 32;
817 l_pk := 'ent_period_id';
818 ltd := '_MTD';
819 -- l_bis_map_rec.key:= BIS_PMV_QUERY_PVT.VIEW_BY_KEY;
820 -- l_bis_map_rec.value:= 'TIME+FII_TIME_ENT_PERIOD';
821 elec_late_payment_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
822 elec_late_payment_rec.attribute_value :='TIME+FII_TIME_ENT_PERIOD';
823 elec_late_payment_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
824 l_name := 'to_char(t.start_date,''Mon'')';
825 select nvl(fii_time_api.ent_pper_END(l_as_of_date), l_start_date) into l_cy_period_END from dual;
826 select nvl(fii_time_api.ent_sd_lysper_END(l_as_of_date), l_start_date) into l_p_as_of_date from dual;
827 select nvl(fii_time_api.ent_sd_lysper_END(l_p_as_of_date), l_start_date) into l_start from dual;
828 l_begin_date := l_p_as_of_date;
829
830 SELECT sequence into l_curr_effective_num
831 FROM fii_time_ent_period
832 WHERE l_as_of_date between start_date AND END_date;
833
834 WHEN 'FII_TIME_ENT_QTR' THEN
835 l_period_type := 64;
836 l_pk := 'ent_qtr_id';
837 ltd := '_QTD';
838 -- l_bis_map_rec.key:= BIS_PMV_QUERY_PVT.VIEW_BY_KEY;
839 -- l_bis_map_rec.value:= 'TIME+FII_TIME_ENT_QTR';
840 elec_late_payment_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
841 elec_late_payment_rec.attribute_value :='TIME+FII_TIME_ENT_QTR';
842 elec_late_payment_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
843 l_name :=
844 'replace(fnd_message.get_string(''FII'',''FII_QUARTER_LABEL''),''&QUARTER_NUMBER'',t.sequence)';
845 select nvl(fii_time_api.ent_pqtr_END(l_as_of_date), l_start_date) into l_cy_period_END from dual;
846 IF (l_time_comp = 'SEQUENTIAL') THEN
847 -- select nvl(fii_time_api.ent_sd_lysqtr_END(l_as_of_date), l_start_date) into l_p_as_of_date from dual;
848 select nvl(fii_time_api.ent_sd_pqtr_END(l_as_of_date), l_start_date) into
849 l_p_as_of_date from dual;
850
851 SELECT ent_qtr_id into l_curr_effective_num
852 FROM fii_time_ent_qtr
853 WHERE l_as_of_date between start_date AND END_date;
854 --select nvl(fii_time_api.ent_sd_lysqtr_END(fii_time_api.ent_sd_lysqtr_END(l_p_as_of_date)), l_start_date) into l_begin_date from dual;
855 select
856 nvl(fii_time_api.ent_sd_lysqtr_END(nvl(fii_time_api.ent_sd_lysqtr_END(fii_time_api.ent_sd_lysqtr_END(l_p_as_of_date)),
857 l_start_date)), l_start_date) into l_begin_date from dual;
858
859 --select nvl(fii_time_api.ent_sd_lysqtr_END(l_p_as_of_date), l_start_date-1) into l_start from dual;
860 select
861 fii_time_api.ent_sd_lysqtr_END(nvl(fii_time_api.ent_sd_lysqtr_END(l_as_of_date),
862 l_start_date-1)) into l_start from dual;
863
864 ELSE
865 select nvl(fii_time_api.ent_sd_lysqtr_END(l_as_of_date), l_start_date) into l_p_as_of_date from dual;
866 SELECT sequence into l_curr_effective_num
867 FROM fii_time_ent_qtr
868 WHERE l_as_of_date between start_date AND END_date;
869 l_begin_date := l_p_as_of_date;
870 select nvl(fii_time_api.ent_sd_lysqtr_END(l_p_as_of_date), l_start_date) into l_start from dual;
871 END IF;
872 WHEN 'FII_TIME_ENT_YEAR' THEN
873 l_period_type := 128;
874 l_pk := 'ent_year_id';
875 ltd := '_YTD';
876 -- l_bis_map_rec.key:= BIS_PMV_QUERY_PVT.VIEW_BY_KEY;
877 -- l_bis_map_rec.value:= 'TIME+FII_TIME_ENT_YEAR';
878 elec_late_payment_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
879 elec_late_payment_rec.attribute_value :='TIME+FII_TIME_ENT_YEAR';
880 elec_late_payment_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
881
882 END CASE;
883
884 -- p_bis_map_tbl.extEND;
885 -- p_bis_map_tbl(p_bis_map_tbl.count) := l_bis_map_rec;
886 -- l_bis_map_rec.key := BIS_PMV_QUERY_PVT.QUERY_STR_KEY;
887 elec_late_payment_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
888 elec_late_payment_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
889 elec_late_payment_output.EXTEND;
890 elec_late_payment_output(elec_late_payment_output.COUNT) := elec_late_payment_rec;
891
892 l_period_suffix:=FII_PMV_UTIL.get_period_type_suffix(l_page_period_type);
893
894 /*-----------------------------------------------------+
895 | FII_MEASURE1 - Electronic |
896 | FII_MEASURE2 - Prior Electronic |
897 | FII_MEASURE3 - Paid Late |
898 | FII_MEASURE4 - Prior Paid Late |
899 | FII_MEASURE5 - Name of the Period |
900 +------------------------------------------------------*/
901
902 IF (l_supplier_where is null) then
903 /* This part will handle the case wherein Supplier= All */
904
905 IF l_page_period_type = 'FII_TIME_ENT_YEAR' THEN
906 -- l_bis_map_rec.value := '
907
908 elec_late_payment_sql := 'SELECT t.name FII_MEASURE5,
909 max(inline_view.electronic) FII_MEASURE1,
910 max(inline_view.prior_electronic) FII_MEASURE2,
911 max(inline_view.paid_late) FII_MEASURE3,
912 max(inline_view.prior_paid_late) FII_MEASURE4
913 FROM
914 ( SELECT
915 inner_inline_view.fii_sequence FII_SEQUENCE,
916 (CASE WHEN SUM(invoice_count_entered) = 0 THEN 0
917 ELSE (SUM(e_invoice_count)/SUM(invoice_count_entered)*100 ) END) electronic,
918 TO_NUMBER(NULL) prior_electronic,
919 (CASE WHEN SUM(paid_invoice_count) = 0 THEN 0
920 ELSE (SUM(paid_late_count)/SUM(paid_invoice_count)*100 ) END) paid_late,
921 TO_NUMBER(NULL) prior_paid_late
922 FROM
923 ( SELECT
924 t.sequence FII_SEQUENCE,
925 f.e_invoice_count e_invoice_count,
926 f.invoice_count_entered invoice_count_entered,
927 f.paid_late_count'||ltd||' paid_late_count,
928 f.paid_inv_count'||ltd||' paid_invoice_count
929 FROM FII_AP_MGT_KPI_MV f,
930 '||l_page_period_type||' t
931 WHERE
932 f.time_id = t.'||l_pk||'
933 AND f.period_type_id = :FII_BIND6
934 AND t.start_date between to_date(:FII_BIND15, ''DD/MM/YYYY'') AND to_date(:FII_BIND10, ''DD/MM/YYYY'')
935 '||l_org_WHERE||' '||l_supplier_WHERE||'
936
937 UNION ALL
938 SELECT
939 t.sequence FII_SEQUENCE,
940 f.e_invoice_count e_invoice_count,
941 f.invoice_count_entered invoice_count_entered,
942 f.paid_late_count'||ltd||' paid_late_count,
943 f.paid_inv_count'||ltd||' paid_invoice_count
944 FROM FII_AP_MGT_KPI_MV f,
945 fii_time_structures cal,
946 '||l_page_period_type||' t,
947 fii_time_day day
948 WHERE
949 f.period_type_id = cal.period_type_id
950 AND f.time_id = cal.time_id
951 AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
952 AND cal.report_date = &BIS_CURRENT_ASOF_DATE
953 AND cal.report_date = day.report_date
954 AND day.'||l_pk||' = t.'||l_pk||'
955 '||l_org_WHERE||' '||l_supplier_WHERE||'
956 ) inner_inline_view
957 GROUP BY inner_inline_view.FII_SEQUENCE
958 ) inline_view, '||l_page_period_type||' t
959 WHERE FII_SEQUENCE (+)= t.sequence
960 AND t.start_date >= to_date(:FII_BIND16, ''DD/MM/YYYY'')
961 AND t.END_date <= to_date(:FII_BIND12, ''DD/MM/YYYY'')
962 GROUP BY t.sequence, t.name, t.'||l_pk||'
963 ORDER BY t.sequence';
964
968 SELECT t.name FII_MEASURE5,
965 ELSIF ((l_page_period_type = 'FII_TIME_ENT_QTR') AND (l_time_comp = 'SEQUENTIAL'))THEN
966
967 elec_late_payment_sql := '
969 inline_view.electronic FII_MEASURE1,
970 inline_view.prior_electronic FII_MEASURE2,
971 inline_view.paid_late FII_MEASURE3,
972 inline_view.prior_paid_late FII_MEASURE4
973 FROM
974 ( SELECT
975 inner_inline_view.FII_SEQUENCE FII_EFFECTIVE_NUM,
976 (CASE WHEN SUM(invoice_count_entered) = 0 THEN 0 ELSE
977 (SUM(e_invoice_count)/SUM(invoice_count_entered)*100 ) END) electronic,
978 to_number(null) prior_electronic,
979 (CASE WHEN SUM(paid_invoice_count) = 0 THEN 0 ELSE
980 (SUM(paid_late_count)/SUM(paid_invoice_count)*100 ) END) paid_late,
981 to_number(null) prior_paid_late
982 FROM
983 (
984 SELECT
985 t.'||l_pk||' FII_SEQUENCE,
986 (CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN
987 f.e_invoice_count ELSE TO_NUMBER(NULL) END ) e_invoice_count,
988
989 (CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN f.invoice_count_entered
990 ELSE TO_NUMBER(NULL) END ) invoice_count_entered,
991
992 (CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN
993 f.paid_late_count'||ltd||'
994 ELSE TO_NUMBER(NULL) END ) paid_late_count,
995
996 (CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN
997 f.paid_inv_count'||ltd||'
998 ELSE TO_NUMBER(NULL) END ) paid_invoice_count
999
1000 FROM FII_AP_MGT_KPI_MV f,
1001 '||l_page_period_type||' t
1002
1003 WHERE
1004 f.time_id = t.'||l_pk||'
1005 AND f.period_type_id = :FII_BIND6
1006 AND t.start_date between to_date(:FII_BIND14, ''DD/MM/YYYY'') AND to_date(:FII_BIND7, ''DD/MM/YYYY'')
1007 '||l_org_WHERE||' '||l_supplier_WHERE||'
1008
1009 UNION ALL
1010 SELECT :FII_BIND5 FII_SEQUENCE,
1011 f.e_invoice_count e_invoice_count,
1012 f.invoice_count_entered invoice_count_entered,
1013 f.paid_late_count'||ltd||' paid_late_count,
1014 f.paid_inv_count'||ltd||' paid_invoice_count
1015
1016 FROM FII_AP_MGT_KPI_MV f,
1017 fii_time_structures cal
1018
1019 WHERE
1020 f.time_id = cal.time_id
1021 AND f.period_type_id = cal.period_type_id
1022 AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
1023 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE)
1024 '||l_org_WHERE||' '||l_supplier_WHERE||'
1025 ) inner_inline_view
1026 GROUP BY inner_inline_view.FII_SEQUENCE
1027 ) inline_view, '||l_page_period_type||' t
1028 WHERE inline_view.fii_effective_num (+)= t.'||l_pk||'
1029 AND t.start_date <= &BIS_CURRENT_ASOF_DATE
1030 AND t.start_date > to_date(:FII_BIND13, ''DD/MM/YYYY'')
1031 ORDER BY t.start_date';
1032
1033 ELSE
1034
1035 elec_late_payment_sql := '
1036
1037 SELECT t.name FII_MEASURE5,
1038
1039 inline_view.electronic FII_MEASURE1,
1040 inline_view.prior_electronic FII_MEASURE2,
1041 inline_view.paid_late FII_MEASURE3,
1042 inline_view.prior_paid_late FII_MEASURE4
1043 FROM
1044 ( SELECT
1045 inner_inline_view.FII_SEQUENCE FII_EFFECTIVE_NUM,
1046 (CASE WHEN SUM(invoice_count_entered) = 0 THEN 0 ELSE
1047 (SUM(e_invoice_count)/SUM(invoice_count_entered)*100 ) END) electronic,
1048 to_number(null) prior_electronic,
1049
1050 (CASE WHEN SUM(paid_invoice_count) = 0 THEN 0 ELSE
1051 (SUM(paid_late_count)/SUM(paid_invoice_count)*100 ) END) paid_late,
1052
1053 to_number(null) prior_paid_late
1054 FROM
1055 ( SELECT
1056 t.sequence FII_SEQUENCE,
1057 (CASE WHEN t.sequence <> :FII_BIND5 THEN (CASE WHEN t.start_date between
1058 to_date(:FII_BIND7,''DD/MM/YYYY'') AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
1059 THEN f.e_invoice_count ELSE TO_NUMBER(NULL) END)
1060 ELSE TO_NUMBER(NULL) END ) e_invoice_count,
1061 (CASE WHEN t.sequence <> :FII_BIND5 THEN (CASE WHEN t.start_date between
1062 to_date(:FII_BIND7,''DD/MM/YYYY'') AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
1066 THEN f.paid_late_count'||ltd||' ELSE TO_NUMBER(NULL) END)
1063 THEN f.invoice_count_entered ELSE TO_NUMBER(NULL) END) ELSE TO_NUMBER(NULL) END ) invoice_count_entered,
1064 (CASE WHEN t.sequence <> :FII_BIND5 THEN (CASE WHEN t.start_date between
1065 to_date(:FII_BIND7, ''DD/MM/YYYY'') AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
1067 ELSE TO_NUMBER(NULL) END ) paid_late_count,
1068 (CASE WHEN t.sequence <> :FII_BIND5 THEN (CASE WHEN t.start_date between
1069 to_date(:FII_BIND7,''DD/MM/YYYY'') AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
1070 THEN f.paid_inv_count'||ltd||'
1071 ELSE TO_NUMBER(NULL) END) ELSE TO_NUMBER(NULL) END ) paid_invoice_count
1072
1073 FROM FII_AP_MGT_KPI_MV f,
1074 '||l_page_period_type||' t
1075 WHERE
1076 f.time_id = t.'||l_pk||'
1077 AND f.period_type_id = :FII_BIND6
1078 AND t.start_date between to_date(:FII_BIND13, ''DD/MM/YYYY'') AND
1079 to_date(:FII_BIND8, ''DD/MM/YYYY'')
1080 '||l_org_WHERE||' '||l_supplier_WHERE||'
1081 UNION ALL
1082 SELECT
1083 :FII_BIND5 FII_SEQUENCE,
1084 f.e_invoice_count e_invoice_count,
1085 f.invoice_count_entered invoice_count_entered,
1086 f.paid_late_count'||ltd||' paid_late_count,
1087 f.paid_inv_count'||ltd||' paid_invoice_count
1088
1089 FROM FII_AP_MGT_KPI_MV f,
1090 fii_time_structures cal
1091 WHERE
1092 f.period_type_id = cal.period_type_id
1093 AND f.time_id = cal.time_id
1094 AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
1095 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE)
1096 '||l_org_WHERE||' '||l_supplier_WHERE||'
1097 ) inner_inline_view
1098 GROUP BY inner_inline_view.FII_SEQUENCE
1099 ) inline_view, '||l_page_period_type||' t
1100 WHERE inline_view.fii_effective_num (+)= t.sequence
1101 AND t.start_date <= &BIS_CURRENT_ASOF_DATE
1102 AND t.start_date > to_date(:FII_BIND14, ''DD/MM/YYYY'')
1103 ORDER BY t.start_date';
1104 END IF;
1105
1106 ELSE
1107
1108 /* This part will handle the case when Supplier has been chosen */
1109
1110 IF l_page_period_type = 'FII_TIME_ENT_YEAR' THEN
1111 -- l_bis_map_rec.value := '
1112
1113 elec_late_payment_sql := 'SELECT t.name FII_MEASURE5,
1114 max(inline_view.electronic) FII_MEASURE1,
1115 max(inline_view.prior_electronic) FII_MEASURE2,
1116 max(inline_view.paid_late) FII_MEASURE3,
1117 max(inline_view.prior_paid_late) FII_MEASURE4
1118 FROM
1119 ( SELECT
1120 inner_inline_view.fii_sequence FII_SEQUENCE,
1121 (CASE WHEN SUM(invoice_count_entered) = 0 THEN 0
1122 ELSE (SUM(e_invoice_count)/SUM(invoice_count_entered)*100 ) END) electronic,
1123 TO_NUMBER(NULL) prior_electronic,
1124 (CASE WHEN SUM(paid_invoice_count) = 0 THEN 0
1125 ELSE (SUM(paid_late_count)/SUM(paid_invoice_count)*100 ) END) paid_late,
1126 TO_NUMBER(NULL) prior_paid_late
1127 FROM
1128 (
1129 (
1130 SELECT
1131 t.sequence FII_SEQUENCE,
1132 f.e_invoice_count e_invoice_count,
1133 f.invoice_count_entered invoice_count_entered,
1134 0 paid_late_count,
1135 0 paid_invoice_count
1136 FROM FII_AP_IVATY_XB_MV f,
1137 '||l_page_period_type||' t
1138 WHERE f.time_id = t.'||l_pk||'
1139 AND f.period_type_id = :FII_BIND6
1140 AND t.start_date between to_date(:FII_BIND15, ''DD/MM/YYYY'') AND to_date(:FII_BIND10, ''DD/MM/YYYY'')
1141 '||l_org_where||' '||l_supplier_WHERE||'
1142
1143
1144 UNION ALL
1145
1146 SELECT
1147 t.sequence FII_SEQUENCE,
1148 0 e_invoice_count,
1149 0 invoice_count_entered,
1150 f.paid_late_count'||ltd||' paid_late_count,
1151 (f.paid_late_count'||ltd||' + f.PAID_ON_TIME_COUNT'||ltd||' ) paid_invoice_count
1152 FROM FII_AP_PAYOL_XB_MV f,
1153 '||l_page_period_type||' t
1154 WHERE f.time_id = t.'||l_pk||'
1155 AND f.period_type_id = :FII_BIND6
1156 AND t.start_date between to_date(:FII_BIND15, ''DD/MM/YYYY'')
1157 AND to_date(:FII_BIND10, ''DD/MM/YYYY'')
1158 '||l_org_where||' '||l_supplier_WHERE||'
1159
1160 )
1161
1162
1163 UNION ALL
1164
1165 (
1166 SELECT
1170 0 paid_late_count,
1167 t.sequence FII_SEQUENCE,
1168 f.e_invoice_count e_invoice_count,
1169 f.invoice_count_entered invoice_count_entered,
1171 0 paid_invoice_count
1172 FROM FII_AP_IVATY_XB_MV f,
1173 fii_time_structures cal,
1174 '||l_page_period_type||' t,
1175 fii_time_day day
1176 WHERE
1177 f.period_type_id = cal.period_type_id
1178 AND f.time_id = cal.time_id
1179 AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
1180 AND cal.report_date = &BIS_CURRENT_ASOF_DATE
1181 AND cal.report_date = day.report_date
1182 AND day.'||l_pk||' = t.'||l_pk||'
1183 '||l_org_where||' '||l_supplier_WHERE||'
1184
1185 UNION ALL
1186
1187
1188 SELECT
1189 t.sequence FII_SEQUENCE,
1190 0 e_invoice_count,
1191 0 invoice_count_entered,
1192 f.paid_late_count'||ltd||' paid_late_count,
1193 (f.paid_late_count'||ltd||' + f.PAID_ON_TIME_COUNT'||ltd||' ) paid_invoice_count
1194 FROM FII_AP_PAYOL_XB_MV f,
1195 fii_time_structures cal,
1196 '||l_page_period_type||' t,
1197 fii_time_day day
1198 WHERE f.period_type_id = cal.period_type_id
1199 AND f.time_id = cal.time_id
1200 AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
1201 AND cal.report_date = &BIS_CURRENT_ASOF_DATE
1202 AND cal.report_date = day.report_date
1203 AND day.'||l_pk||' = t.'||l_pk||'
1204 '||l_org_where||' '||l_supplier_WHERE||'
1205 )
1206 ) inner_inline_view
1207
1208
1209 GROUP BY inner_inline_view.FII_SEQUENCE
1210 ) inline_view, '||l_page_period_type||' t
1211 WHERE FII_SEQUENCE (+)= t.sequence
1212 AND t.start_date >= to_date(:FII_BIND16, ''DD/MM/YYYY'')
1213 AND t.END_date <= to_date(:FII_BIND12, ''DD/MM/YYYY'')
1214 GROUP BY t.sequence, t.name, t.'||l_pk||'
1215 ORDER BY t.sequence';
1216
1217
1218 ELSIF ((l_page_period_type = 'FII_TIME_ENT_QTR') AND (l_time_comp = 'SEQUENTIAL')) THEN
1219
1220 elec_late_payment_sql := '
1221 SELECT t.name FII_MEASURE5,
1222 inline_view.electronic FII_MEASURE1,
1223 inline_view.prior_electronic FII_MEASURE2,
1224 inline_view.paid_late FII_MEASURE3,
1225 inline_view.prior_paid_late FII_MEASURE4
1226 FROM
1227 ( SELECT
1228 inner_inline_view.FII_SEQUENCE FII_EFFECTIVE_NUM,
1229 (CASE WHEN SUM(invoice_count_entered) = 0 THEN 0 ELSE
1230 (SUM(e_invoice_count)/SUM(invoice_count_entered)*100 ) END) electronic,
1231 to_number(null) prior_electronic,
1232 (CASE WHEN SUM(paid_invoice_count) = 0 THEN 0 ELSE
1233 (SUM(paid_late_count)/SUM(paid_invoice_count)*100 ) END) paid_late,
1234 to_number(null) prior_paid_late
1235 FROM
1236 (
1237 (
1238
1239 SELECT
1240 t.'||l_pk||' FII_SEQUENCE,
1241 (CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN f.e_invoice_count
1242 ELSE TO_NUMBER(NULL) END ) e_invoice_count,
1243 (CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN f.invoice_count_entered
1244 ELSE TO_NUMBER(NULL) END ) invoice_count_entered,
1245 0 paid_late_count,
1246 0 paid_invoice_count
1247 FROM FII_AP_IVATY_XB_MV f,
1248 '||l_page_period_type||' t
1249 WHERE
1250 f.time_id = t.'||l_pk||'
1251 AND f.period_type_id = :FII_BIND6
1252 AND t.start_date between to_date(:FII_BIND14, ''DD/MM/YYYY'') AND to_date(:FII_BIND7, ''DD/MM/YYYY'')
1253 '||l_org_WHERE||' '||l_supplier_WHERE||'
1254 union all
1255 SELECT
1256 t.'||l_pk||' FII_SEQUENCE,
1257 0 e_invoice_count,
1258 0 invoice_count_entered,
1259 (CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN f.paid_late_count'||ltd||'
1260 ELSE TO_NUMBER(NULL) END ) paid_late_count,
1261
1262 (CASE WHEN t.'||l_pk||' <> :FII_BIND5 THEN (f.paid_late_count'||ltd||' + f.PAID_ON_TIME_COUNT'||ltd||')
1263 ELSE TO_NUMBER(NULL) END ) paid_invoice_count
1264
1265 FROM FII_AP_PAYOL_XB_MV f,
1269 AND f.period_type_id = :FII_BIND6
1266 '||l_page_period_type||' t
1267 WHERE
1268 f.time_id = t.'||l_pk||'
1270 AND t.start_date between to_date(:FII_BIND14, ''DD/MM/YYYY'') AND to_date(:FII_BIND7, ''DD/MM/YYYY'')
1271 '||l_org_WHERE||' '||l_supplier_WHERE||'
1272 UNION ALL
1273
1274 SELECT :FII_BIND5 FII_SEQUENCE,
1275 f.e_invoice_count e_invoice_count,
1276 f.invoice_count_entered invoice_count_entered,
1277 0 paid_late_count,
1278 0 paid_invoice_count
1279 FROM FII_AP_IVATY_XB_MV f,
1280 fii_time_structures cal
1281 WHERE f.time_id = cal.time_id
1282 AND f.period_type_id = cal.period_type_id
1283 AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
1284 AND cal.report_date = (&BIS_CURRENT_ASOF_DATE)
1285 '||l_org_WHERE||' '||l_supplier_WHERE||'
1286 union all
1287 SELECT :FII_BIND5 FII_SEQUENCE,
1288 0 e_invoice_count,
1289 0 invoice_count_entered,
1290 f.paid_late_count'||ltd||' paid_late_count,
1291 (f.paid_late_count'||ltd||' + f.PAID_ON_TIME_COUNT'||ltd||' ) paid_invoice_count
1292 FROM FII_AP_PAYOL_XB_MV f,
1293 fii_time_structures cal
1294 WHERE f.time_id = cal.time_id
1295 AND f.period_type_id = cal.period_type_id
1296 AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
1297 AND cal.report_date = (&BIS_CURRENT_ASOF_DATE)
1298 '||l_org_WHERE||' '||l_supplier_WHERE||'
1299 )
1300 ) inner_inline_view
1301 GROUP BY inner_inline_view.FII_SEQUENCE
1302 ) inline_view, '||l_page_period_type||' t
1303 WHERE inline_view.fii_effective_num (+)= t.'||l_pk||'
1304 AND t.start_date <= &BIS_CURRENT_ASOF_DATE
1305 AND t.start_date > to_date(:FII_BIND13, ''DD/MM/YYYY'')
1306 ORDER BY t.start_date';
1307
1308 ELSE
1309
1310 elec_late_payment_sql := '
1311
1312 SELECT t.name FII_MEASURE5,
1313 inline_view.electronic FII_MEASURE1,
1314 inline_view.prior_electronic FII_MEASURE2,
1315 inline_view.paid_late FII_MEASURE3,
1316 inline_view.prior_paid_late FII_MEASURE4
1317 FROM
1318 (
1319 SELECT
1320 inner_inline_view.FII_SEQUENCE FII_EFFECTIVE_NUM,
1321 (CASE WHEN SUM(invoice_count_entered) = 0 THEN 0 ELSE
1322 (SUM(e_invoice_count)/SUM(invoice_count_entered)*100 ) END) electronic,
1323 to_number(null) prior_electronic,
1324
1325 (CASE WHEN SUM(paid_invoice_count) = 0 THEN 0 ELSE
1326 (SUM(paid_late_count)/SUM(paid_invoice_count)*100 ) END) paid_late,
1327
1328 to_number(null) prior_paid_late
1329 FROM
1330 (
1331 (
1332
1333 SELECT
1334 t.sequence FII_SEQUENCE,
1335 (CASE WHEN t.sequence <> :FII_BIND5 THEN (CASE WHEN t.start_date between
1336 to_date(:FII_BIND7,''DD/MM/YYYY'') AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
1337 THEN f.e_invoice_count ELSE TO_NUMBER(NULL) END)
1338 ELSE TO_NUMBER(NULL) END ) e_invoice_count,
1339 (CASE WHEN t.sequence <> :FII_BIND5 THEN (CASE WHEN t.start_date between
1340 to_date(:FII_BIND7,''DD/MM/YYYY'') AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
1341 THEN f.invoice_count_entered ELSE TO_NUMBER(NULL) END)
1342 ELSE TO_NUMBER(NULL) END ) invoice_count_entered,
1343 0 paid_late_count,
1344 0 paid_invoice_count
1345 FROM FII_AP_IVATY_XB_MV f,
1346 '||l_page_period_type||' t
1347 WHERE f.time_id = t.'||l_pk||'
1348 AND f.period_type_id = :FII_BIND6
1349 AND t.start_date between to_date(:FII_BIND13, ''DD/MM/YYYY'')
1350 AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
1351 '||l_org_WHERE||' '||l_supplier_WHERE||'
1352
1353
1354 UNION ALL
1355
1356
1357 SELECT
1358 t.sequence FII_SEQUENCE,
1359 0 e_invoice_count,
1360 0 invoice_count_entered,
1361 (CASE WHEN t.sequence <> :FII_BIND5 THEN (CASE WHEN t.start_date between
1362 to_date(:FII_BIND7, ''DD/MM/YYYY'') AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
1363 THEN f.paid_late_count'||ltd||' ELSE TO_NUMBER(NULL) END)
1364 ELSE TO_NUMBER(NULL) END ) paid_late_count,
1365
1366 (CASE WHEN t.sequence <> :FII_BIND5 THEN (CASE WHEN t.start_date between
1367 to_date(:FII_BIND7, ''DD/MM/YYYY'') AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
1368 THEN (f.paid_late_count'||ltd||' + f.PAID_ON_TIME_COUNT'||ltd||' ) ELSE TO_NUMBER(NULL) END)
1372 '||l_page_period_type||' t
1369 ELSE TO_NUMBER(NULL) END ) paid_invoice_count
1370
1371 FROM FII_AP_PAYOL_XB_MV f,
1373 WHERE f.time_id = t.'||l_pk||'
1374 AND f.period_type_id = :FII_BIND6
1375 AND t.start_date between to_date(:FII_BIND13, ''DD/MM/YYYY'')
1376 AND to_date(:FII_BIND8, ''DD/MM/YYYY'')
1377 '||l_org_WHERE||' '||l_supplier_WHERE||'
1378 )
1379
1380 UNION ALL
1381 (
1382 SELECT
1383 :FII_BIND5 FII_SEQUENCE,
1384 f.e_invoice_count e_invoice_count,
1385 f.invoice_count_entered invoice_count_entered,
1386 0 paid_late_count,
1387 0 paid_invoice_count
1388 FROM FII_AP_IVATY_XB_MV f,
1389 fii_time_structures cal
1390 WHERE f.period_type_id = cal.period_type_id
1391 AND f.time_id = cal.time_id
1392 AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
1393 AND cal.report_date = (&BIS_CURRENT_ASOF_DATE)
1394 '||l_org_WHERE||' '||l_supplier_WHERE||'
1395
1396 UNION ALL
1397
1398 SELECT
1399 :FII_BIND5 FII_SEQUENCE,
1400 0 e_invoice_count,
1401 0 invoice_count_entered,
1402 f.paid_late_count'||ltd||' paid_late_count,
1403 (f.paid_late_count'||ltd||' + f.PAID_ON_TIME_COUNT'||ltd||' ) paid_invoice_count
1404 FROM FII_AP_PAYOL_XB_MV f,
1405 fii_time_structures cal
1406 WHERE f.period_type_id = cal.period_type_id
1407 AND f.time_id = cal.time_id
1408 AND bitand(cal.record_type_id,:FII_BIND1)= :FII_BIND1
1409 AND cal.report_date = (&BIS_CURRENT_ASOF_DATE)
1410 '||l_org_WHERE||' '||l_supplier_WHERE||'
1411 )
1412 ) inner_inline_view
1413 GROUP BY inner_inline_view.FII_SEQUENCE
1414 ) inline_view, '||l_page_period_type||' t
1415 WHERE inline_view.fii_effective_num (+)= t.sequence
1416 AND t.start_date <= &BIS_CURRENT_ASOF_DATE
1417 AND t.start_date > to_date(:FII_BIND14, ''DD/MM/YYYY'')
1418 ORDER BY t.start_date';
1419
1420 END IF;
1421 end if;
1422
1423
1424 elec_late_payment_output.EXTEND;
1425 elec_late_payment_rec.attribute_name := ':FII_BIND1';
1426 elec_late_payment_rec.attribute_value := TO_CHAR(l_actual_period_type);
1427 elec_late_payment_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1428 elec_late_payment_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1429 elec_late_payment_output(elec_late_payment_output.COUNT) := elec_late_payment_rec;
1430 elec_late_payment_output.EXTEND;
1431 elec_late_payment_rec.attribute_name := ':FII_BIND5';
1432 elec_late_payment_rec.attribute_value := TO_CHAR(l_curr_effective_num);
1433 elec_late_payment_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1434 elec_late_payment_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1435 elec_late_payment_output(elec_late_payment_output.COUNT) := elec_late_payment_rec;
1436 elec_late_payment_output.EXTEND;
1437 elec_late_payment_rec.attribute_name := ':FII_BIND6';
1438 elec_late_payment_rec.attribute_value := TO_CHAR(l_period_type);
1439 elec_late_payment_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1440 elec_late_payment_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1441 elec_late_payment_output(elec_late_payment_output.COUNT) := elec_late_payment_rec;
1442 elec_late_payment_output.EXTEND;
1443 elec_late_payment_rec.attribute_name := ':FII_BIND7';
1444 elec_late_payment_rec.attribute_value := TO_CHAR(l_p_as_of_date,'DD/MM/YYYY');
1445 elec_late_payment_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1446 elec_late_payment_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1447 elec_late_payment_output(elec_late_payment_output.COUNT) := elec_late_payment_rec;
1448 elec_late_payment_output.EXTEND;
1449 elec_late_payment_rec.attribute_name := ':FII_BIND8';
1450 elec_late_payment_rec.attribute_value := TO_CHAR(l_cy_period_END,'DD/MM/YYYY');
1451 elec_late_payment_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1452 elec_late_payment_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1453 elec_late_payment_output(elec_late_payment_output.COUNT) := elec_late_payment_rec;
1454 elec_late_payment_output.EXTEND;
1455 elec_late_payment_rec.attribute_name := ':FII_BIND10';
1456 elec_late_payment_rec.attribute_value := TO_CHAR(l_ent_pyr_END,'DD/MM/YYYY');
1457 elec_late_payment_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1458 elec_late_payment_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1459 elec_late_payment_output(elec_late_payment_output.COUNT) := elec_late_payment_rec;
1460 elec_late_payment_output.EXTEND;
1461 elec_late_payment_rec.attribute_name := ':FII_BIND12';
1462 elec_late_payment_rec.attribute_value := TO_CHAR(l_ent_cyr_END,'DD/MM/YYYY');
1463 elec_late_payment_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1464 elec_late_payment_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1465 elec_late_payment_output(elec_late_payment_output.COUNT) := elec_late_payment_rec;
1469 elec_late_payment_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1466 elec_late_payment_output.EXTEND;
1467 elec_late_payment_rec.attribute_name := ':FII_BIND13';
1468 elec_late_payment_rec.attribute_value := TO_CHAR(l_start,'DD/MM/YYYY');
1470 elec_late_payment_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1471 elec_late_payment_output(elec_late_payment_output.COUNT) := elec_late_payment_rec;
1472 elec_late_payment_output.EXTEND;
1473 elec_late_payment_rec.attribute_name := ':FII_BIND14';
1474 elec_late_payment_rec.attribute_value := TO_CHAR(l_begin_date,'DD/MM/YYYY');
1475 elec_late_payment_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1476 elec_late_payment_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1477 elec_late_payment_output(elec_late_payment_output.COUNT) := elec_late_payment_rec;
1478 elec_late_payment_output.EXTEND;
1479 elec_late_payment_rec.attribute_name := ':FII_BIND15';
1480 elec_late_payment_rec.attribute_value := TO_CHAR(l_ent_year_st1,'DD/MM/YYYY');
1481 elec_late_payment_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1482 elec_late_payment_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1483 elec_late_payment_output(elec_late_payment_output.COUNT) := elec_late_payment_rec;
1484 elec_late_payment_output.EXTEND;
1485 elec_late_payment_rec.attribute_name := ':FII_BIND16';
1486 elec_late_payment_rec.attribute_value := TO_CHAR(l_ent_year_st2,'DD/MM/YYYY');
1487 elec_late_payment_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1488 elec_late_payment_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
1489 elec_late_payment_output(elec_late_payment_output.COUNT) := elec_late_payment_rec;
1490 elec_late_payment_output.EXTEND;
1491 elec_late_payment_rec.attribute_name := ':GID';
1492 elec_late_payment_rec.attribute_value := to_char(l_gid);
1493 elec_late_payment_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1494 elec_late_payment_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.NUMERIC_BIND;
1495 elec_late_payment_output(elec_late_payment_output.COUNT) := elec_late_payment_rec;
1496 elec_late_payment_output.EXTEND;
1497 elec_late_payment_rec.attribute_name := ':SEC_ID';
1498 elec_late_payment_rec.attribute_value := fii_pmv_util.get_sec_profile;
1499 elec_late_payment_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
1500 elec_late_payment_rec.attribute_data_type :=BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
1501 elec_late_payment_output(elec_late_payment_output.COUNT) := elec_late_payment_rec;
1502 elec_late_payment_output.EXTEND;
1503
1504 END get_elec_late_payment;
1505
1506
1507 /* For KPIs */
1508
1509
1510 PROCEDURE GET_KPI
1511 ( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
1512 kpi_sql out NOCOPY VARCHAR2,
1513 kpi_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)IS
1514 /* declaration section */
1515 sqlstmt VARCHAR2(14000);
1516
1517 l_viewby_dim VARCHAR2(240); -- what is the viewby
1518 l_as_of_date DATE;
1519 l_organization VARCHAR2(240);
1520 l_supplier VARCHAR2(240);
1521 l_currency VARCHAR2(240); -- random size, possibly high
1522 l_viewby_id VARCHAR2(240); -- org_id or supplier_id
1523 l_record_type_id NUMBER; --
1524 l_gid NUMBER; -- 0,4 or 8
1525 l_viewby_string VARCHAR2(240);
1526 l_org_where VARCHAR2(240);
1527 l_sup_where VARCHAR2(240);
1528 l_curr_suffix VARCHAR2(240);
1529 l_period_type VARCHAR2(240);
1530 l_invoice_number VARCHAR2(240);
1531
1532 l_paid_inv_count VARCHAR2(240);
1533 l_paid_on_time_count VARCHAR2(240);
1534 l_paid_late_count VARCHAR2(240);
1535 l_per_type VARCHAR2(240);
1536 l_payment_count VARCHAR2(50);
1537
1538 BEGIN
1539 -- Read the parameters passed
1540 FII_PMV_UTIL.get_parameters(
1541 p_page_parameter_tbl=>p_page_parameter_tbl,
1542 p_as_of_date=>l_as_of_date,
1543 p_operating_unit=>l_organization,
1544 p_supplier=>l_supplier,
1545 p_invoice_number=>l_invoice_number,
1546 p_period_type=>l_period_type,
1547 p_record_type_id=>l_record_type_id,
1548 p_view_by=>l_viewby_dim,
1549 p_currency=>l_curr_suffix,
1550 p_column_name=>l_viewby_id,
1551 p_table_name=>l_viewby_string,
1552 p_gid=>l_gid,
1553 p_org_where=>l_org_where,
1554 p_supplier_where=>l_sup_where);
1555
1556 l_per_type := FII_PMV_Util.get_period_type_suffix(l_period_type);
1557 l_paid_inv_count :=
1558 'paid_inv_count'||l_per_type;
1559
1560 l_paid_on_time_count :=
1561 'paid_on_time_count'||l_per_type;
1562
1563 l_paid_late_count :=
1564 'paid_late_count'||l_per_type;
1565
1566 /* added by vkazhipu for bug 4424398 */
1567
1568 l_payment_count := 'payment_count'||l_per_type;
1569
1570
1571 /* Main SQL section */
1572
1573 sqlstmt := '
1574 SELECT viewby_dim.value VIEWBY,
1575 viewby_dim.id VIEWBYID,
1576 f.invoice_count_entered_cur FII_MEASURE1,
1580 FII_MEASURE3,
1577 f.invoice_count_entered_pre FII_MEASURE2,
1578 decode(f.invoice_count_entered_cur,0,0,
1579 (f.e_invoice_count_cur * 100 /f.invoice_count_entered_cur))
1581 decode(f.invoice_count_entered_pre,0,0,
1582 (f.e_invoice_count_pre * 100 /f.invoice_count_entered_pre))
1583 FII_MEASURE4,
1584 f.paid_inv_count_cur FII_MEASURE5,
1585 f.paid_inv_count_pre FII_MEASURE6,
1586 decode(f.paid_inv_count_cur,0,0,
1587 (f.paid_late_count_cur *100/f.paid_inv_count_cur))
1588 FII_MEASURE7,
1589 decode(f.paid_inv_count_pre,0,0,
1590 (f.paid_late_count_pre *100/f.paid_inv_count_pre))
1591 FII_MEASURE8,
1592 decode(f.paid_amt_cur,0,0,
1593 (f.invoice_to_payment_days_cur / f.paid_amt_cur))
1594 FII_MEASURE9,
1595 decode(f.paid_amt_pre,0,0,
1596 (f.invoice_to_payment_days_pre / f.paid_amt_pre))
1597 FII_MEASURE10,
1598 f.payment_count_cur FII_MEASURE11,
1599 f.payment_count_pre FII_MEASURE12,
1600 decode(f.paid_invoice_amt_cur,0,0,
1601 (f.paid_dis_offered_cur * 100 /f.paid_invoice_amt_cur))
1602 FII_MEASURE13,
1603 decode(f.paid_invoice_amt_pre,0,0,
1604 (f.paid_dis_offered_pre * 100 /f.paid_invoice_amt_pre))
1605 FII_MEASURE14,
1606 decode(f.total_paid_amt_cur,0,0,
1607 (f.paid_dis_taken_cur * 100 /f.total_paid_amt_cur))
1608 FII_MEASURE15,
1609 decode(f.total_paid_amt_pre,0,0,
1610 (f.paid_dis_taken_pre * 100 /f.total_paid_amt_pre))
1611 FII_MEASURE16,
1612 decode(sum(f.e_invoice_count_cur) over(),0,0,
1613 sum(f.e_invoice_count_cur) over() *100 /
1614 sum(f.invoice_count_entered_cur) over())
1615 FII_ATTRIBUTE1,
1616 decode(sum(f.e_invoice_count_pre) over(),0,0,
1617 sum(f.e_invoice_count_pre) over() *100 /
1618 sum(f.invoice_count_entered_pre) over())
1619 FII_ATTRIBUTE2,
1620 decode(sum(f.paid_inv_count_cur) over(),0,0,
1621 sum(f.paid_late_count_cur) over() *100 /
1622 sum(f.paid_inv_count_cur) over())
1623 FII_ATTRIBUTE3,
1624 decode(sum(f.paid_inv_count_pre) over(),0,0,
1625 sum(f.paid_late_count_pre) over() *100 /
1626 sum(f.paid_inv_count_pre) over())
1627 FII_ATTRIBUTE4,
1628 decode(sum(f.paid_invoice_amt_cur) over(),0,0,
1629 sum(f.paid_dis_offered_cur) over() *100 /
1630 sum(f.paid_invoice_amt_cur) over())
1631 FII_ATTRIBUTE5,
1632 decode(sum(f.paid_invoice_amt_pre) over(),0,0,
1633 sum(f.paid_dis_offered_pre) over() *100 /
1634 sum(f.paid_invoice_amt_pre) over())
1635 FII_ATTRIBUTE6,
1636 decode(sum(f.total_paid_amt_cur) over(),0,0,
1637 sum(f.paid_dis_taken_cur) over() *100 /
1638 sum(f.total_paid_amt_cur) over())
1639 FII_ATTRIBUTE7,
1640 decode(sum(f.total_paid_amt_pre) over(),0,0,
1641 sum(f.paid_dis_taken_pre) over() *100 /
1642 sum(f.total_paid_amt_pre) over())
1643 FII_ATTRIBUTE8,
1644 decode(sum(f.paid_amt_cur) over(),0,0,
1645 sum(f.invoice_to_payment_days_cur) over() /
1646 sum(f.paid_amt_cur) over())
1647 FII_ATTRIBUTE10,
1648 decode(sum(f.paid_amt_pre) over(),0,0,
1649 sum(f.invoice_to_payment_days_pre) over() /
1650 sum(f.paid_amt_pre) over())
1651 FII_ATTRIBUTE11,
1652 sum(f.invoice_count_entered_cur) over() FII_ATTRIBUTE12,
1653 sum(f.invoice_count_entered_pre) over() FII_ATTRIBUTE13,
1654 sum(f.paid_inv_count_cur) over() FII_ATTRIBUTE14,
1655 sum(f.paid_inv_count_pre) over() FII_ATTRIBUTE15,
1656 sum(f.payment_count_cur) over() FII_ATTRIBUTE16,
1657 sum(f.payment_count_pre) over() FII_ATTRIBUTE17
1658
1659
1660 FROM
1661 (SELECT
1662 f.'||l_viewby_id||' ID,
1663 sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE
1664 then f.invoice_count_entered else to_number(null) end) invoice_count_entered_cur,
1665 sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE
1666 then f.invoice_count_entered else to_number(null) end) invoice_count_entered_pre,
1667 sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE
1668 then f.e_invoice_count else to_number(null) end) e_invoice_count_cur,
1669 sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE
1670 then f.e_invoice_count else to_number(null) end) e_invoice_count_pre,
1671 sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE
1672 then f.'||l_paid_inv_count||' else to_number(null) end) paid_inv_count_cur,
1673 sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE
1674 then f.'||l_paid_inv_count||' else to_number(null) end) paid_inv_count_pre,
1675 sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE
1676 then f.'||l_paid_on_time_count||' else to_number(null) end) paid_on_time_count_cur,
1677 sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE
1678 then f.'||l_paid_on_time_count||' else to_number(null) end) paid_on_time_count_pre,
1679 sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE
1680 then f.'||l_paid_late_count||' else to_number(null) end) paid_late_count_cur,
1681 sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE
1682 then f.'||l_paid_late_count||' else to_number(null) end) paid_late_count_pre,
1683 sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE
1684 then f.invoice_to_payment_days else to_number(null) end)
1685 invoice_to_payment_days_cur,
1686 sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE
1687 then f.invoice_to_payment_days else to_number(null) end)
1688 invoice_to_payment_days_pre,
1689 sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE
1690 then f.paid_amt_b else to_number(null) end)
1691 paid_amt_cur,
1692 sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE
1693 then f.paid_amt_b else to_number(null) end)
1694 paid_amt_pre,
1695 sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE
1696 then f.paid_invoice_amt'||l_per_type||l_curr_suffix||' else to_number(null) end) paid_invoice_amt_cur,
1697 sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE
1698 then f.paid_invoice_amt'||l_per_type||l_curr_suffix||' else to_number(null) end) paid_invoice_amt_pre,
1699 sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE
1700 then f.paid_dis_offered'||l_per_type||l_curr_suffix||' else to_number(null) end) paid_dis_offered_cur,
1701 sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE
1702 then f.paid_dis_offered'||l_per_type||l_curr_suffix||' else to_number(null) end) paid_dis_offered_pre,
1703 sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE
1704 then f.'||l_payment_count||' else to_number(null) end) payment_count_cur,
1705 sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
1706 then f.'||l_payment_count||' else to_number(null) end) payment_count_pre,
1707 sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE
1708 then (f.paid_amt'||l_curr_suffix||'+ f.paid_dis_taken'||l_curr_suffix||') else to_number(null) end) total_paid_amt_cur,
1709 sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
1710 then (f.paid_amt'||l_curr_suffix||'+ f.paid_dis_taken'||l_curr_suffix||') else to_number(null) end) total_paid_amt_pre,
1711 sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE
1712 then f.paid_dis_taken'||l_curr_suffix||' else to_number(null) end) paid_dis_taken_cur,
1713 sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
1714 then f.paid_dis_taken'||l_curr_suffix||' else to_number(null) end) paid_dis_taken_pre
1715 FROM FII_AP_MGT_KPI_MV f, fii_time_structures cal
1716 WHERE f.time_id = cal.time_id
1717 AND f.period_type_id = cal.period_type_id
1718 '||l_sup_where||' '||l_org_where||'
1719 AND bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
1720 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
1721 GROUP BY f.'||l_viewby_id||') f,
1722 ('||l_viewby_string||') viewby_dim
1723 WHERE f.id = viewby_dim.id';
1724
1725
1726 /* Binding Section */
1727 FII_PMV_Util.bind_variable(
1728 p_sqlstmt=>sqlstmt,
1729 p_page_parameter_tbl=>p_page_parameter_tbl,
1730 p_sql_output=>kpi_sql,
1731 p_bind_output_table=>kpi_output,
1732 p_record_type_id=>l_record_type_id,
1733 p_gid=>l_gid
1734 );
1735
1736 END GET_KPI;
1737
1738
1739 END FII_AP_PAY_MGT;
1740