[Home] [Help]
PACKAGE BODY: APPS.FII_AP_PAY_STATUS
Source
1 PACKAGE BODY FII_AP_PAY_STATUS AS
2 /* $Header: FIIAPPSB.pls 120.5 2007/05/07 14:11:18 hsoorea ship $ */
3
4 PROCEDURE GET_OPEN_PAY_TABLE_PORTLET (
5 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
6 open_pay_sum_sql OUT NOCOPY VARCHAR2,
7 open_pay_sum_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
8 IS
9 i NUMBER;
10 l_viewby_dim VARCHAR2(240); -- what is the viewby
11 l_as_of_date DATE;
12 l_organization VARCHAR2(240);
13 l_currency VARCHAR2(240); -- random size, possibly high
14 l_viewby_id VARCHAR2(240); -- org_id or supplier_id
15 l_record_type_id NUMBER; --
16 l_gid NUMBER; -- 0,4 or 8
17 l_viewby_string VARCHAR2(240);
18 l_org_where VARCHAR2(240);
19 l_sup_where VARCHAR2(240);
20 l_curr_suffix VARCHAR2(240);
21 sqlstmt VARCHAR2(14000);
22 l_supplier VARCHAR2(240);
23 l_period_type VARCHAR2(240);
24 l_invoice_number VARCHAR2(240);
25 l_url_1 VARCHAR2(1000);
26 l_url_2 VARCHAR2(1000);
27 l_url_3 VARCHAR2(1000);
28 -- l_pper_end_date DATE;
29 -- l_asof_date_julien NUMBER;
30 BEGIN
31
32 -- Read the parameters passed
33 FII_PMV_UTIL.get_parameters(
34 p_page_parameter_tbl=>p_page_parameter_tbl,
35 p_as_of_date=>l_as_of_date,
36 p_operating_unit=>l_organization,
37 p_supplier=>l_supplier,
38 p_invoice_number=>l_invoice_number,
39 p_period_type=>l_period_type,
40 p_record_type_id=>l_record_type_id,
41 p_view_by=>l_viewby_dim,
42 p_currency=>l_curr_suffix,
43 p_column_name=>l_viewby_id,
44 p_table_name=>l_viewby_string,
45 p_gid=>l_gid,
46 p_org_where=>l_org_where,
47 p_supplier_where=>l_sup_where);
48
49 -- l_record_type_id := 1143; -- no other value possible in this report
50 l_record_type_id := 512;
51
52 -- l_asof_date_julien := to_number(to_char(l_as_of_date,'J'));
53
54 -- so that no conditional query construction is required
55 IF(l_org_where is null) THEN
56 l_org_where := ' ';
57 END IF;
58
59 IF(l_sup_where is null) THEN
60 l_sup_where := ' ';
61 END IF;
62
63 -- l_pper_end_date := fii_time_api.ent_pper_end(l_as_of_date);
64
65 -- To implement the selective drill functionality
66 /*CHANGED THE DRILLS FOR BUG NO.3096365*/
67 /* Changed drill l_url_1 to drill to Unpaid Invoice Detail Report: Bug 3096072 */
68
69 -- l_url_1 := 'pFunctionName=FII_AP_OPEN_PAY_SUM&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
70
71 l_url_1 := 'pFunctionName=FII_AP_UNPAID_INV_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_UNPAID_INV_DETAIL';
72 l_url_2 := 'pFunctionName=FII_AP_PAYMENT_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&FII_TIME_ENT_PERIOD=TIME+FII_TIME_ENT_PERIOD&pParamIds=Y';
73 l_url_3 := 'pFunctionName=FII_AP_PAYMENT_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&FII_TIME_ENT_PERIOD=TIME+FII_TIME_ENT_PERIOD&pParamIds=Y';
74
75 /* Added FII_MEASURE15 for bug # 3343566 . We encounter an error when we hit the next button to
76 render the next 15 records.According to PMV, it is because we did not have an ak region item with sort
77 sequence equal to 1. We cannot change the existing ak region item(Amount) with sort sequence 2 to
78 1 because we don't want the triangle to be displayed. For the same we have added
79 a hidden dummy ak region item same as the Amount column and specified the sort sequence to 1 . */
80
81
82 /*--------------------------------------------------------------+
83 | VIEWBY - Either Operating Unit / Supplier |
84 | VIEWBYID - Either org_id / supplier_id |
85 | FII_MEASURE1 - Open Payables Amount |
86 | FII_MEASURE15 - Open Payables Amount |
87 | FII_MEASURE2 - Past Due Amount |
88 | FII_MEASURE4 - Days Past Due |
89 | FII_MEASURE5 - On Time Payment |
90 | FII_MEASURE6 - Late Payment |
91 | FII_MEASURE7 - Paid Amount |
92 | FII_MEASURE9-14 - Grand Total columns |
93 +--------------------------------------------------------------*/
94
95 /* Made changes for bug # 3611195. Added or bitand(cal.record_type_id,64)=64 to where clause to make sure
96 month data is also selected and made corresponding changes in Select clause.*/
97
98 -- Construct the sql query to be sent
99
100 sqlstmt := '
101 SELECT viewby_dim.value VIEWBY,
102 viewby_dim.id VIEWBYID,
103 sum(f.FII_MEASURE1) FII_MEASURE1,
104 sum(f.FII_MEASURE15) FII_MEASURE15,
105 sum(f.FII_MEASURE2) FII_MEASURE2,
106 sum(f.FII_MEASURE4) FII_MEASURE4,
107 sum(f.FII_MEASURE5) FII_MEASURE5,
108 sum(f.FII_MEASURE6) FII_MEASURE6,
109 sum(f.FII_MEASURE7) FII_MEASURE7,
110 sum(f.FII_MEASURE9) FII_MEASURE9,
111 sum(f.FII_MEASURE10) FII_MEASURE10,
112 sum(f.FII_MEASURE11) FII_MEASURE11,
113 sum(f.FII_MEASURE12) FII_MEASURE12,
114 sum(f.FII_MEASURE13) FII_MEASURE13,
115 sum(f.FII_MEASURE14) FII_MEASURE14,
116 '''||l_url_1||''' FII_ATTRIBUTE5,
117 '''||l_url_2||''' FII_ATTRIBUTE6,
118 '''||l_url_3||''' FII_ATTRIBUTE7
119 from
120 (select ID,
121 FII_MEASURE1,
122 FII_MEASURE15,
123 FII_MEASURE2,
124 FII_MEASURE4,
125 FII_MEASURE5,
126 FII_MEASURE6,
127 FII_MEASURE7,
128 ( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk,
129 SUM(FII_MEASURE1) OVER() FII_MEASURE9,
130 DECODE(NVL(FII_MEASURE1,0), 0, 0,(SUM(FII_MEASURE2) OVER()/
131 SUM(FII_MEASURE1) OVER() * 100)) FII_MEASURE10,
132 -- DECODE(NVL(FII_MEASURE2,0), 0, 0,sum(FII_MEASURE2 * days_past_due) over()/
133 -- sum(FII_MEASURE2) over() ) FII_MEASURE11,
134 DECODE(sum(FII_MEASURE2) over (), 0, 0,
135 :ASOF_DATE_JULIEN - sum(wt_open_past_due_amt) over()/
136 sum(FII_MEASURE2) over() ) FII_MEASURE11,
137 SUM(FII_MEASURE5) OVER() FII_MEASURE12,
138 SUM(FII_MEASURE6) OVER() FII_MEASURE13,
139 DECODE(NVL((FII_MEASURE5+FII_MEASURE6), 0), 0, 0,(SUM(FII_MEASURE6) OVER() /
140 SUM(FII_MEASURE5+FII_MEASURE6) OVER() * 100)) FII_MEASURE14
141 FROM
142 (SELECT f.'||l_viewby_id||' ID,
143 SUM(CASE WHEN bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
144 THEN f.open_amt'||l_curr_suffix||' ELSE 0 END) FII_MEASURE1,
145 SUM(CASE WHEN bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
146 THEN f.open_amt'||l_curr_suffix||' ELSE 0 END) FII_MEASURE15,
147 SUM(CASE WHEN bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
148 THEN f.open_past_due_amt'||l_curr_suffix||' ELSE 0 END) FII_MEASURE2,
149 decode(sum(CASE WHEN bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID then f.open_past_due_amt'||l_curr_suffix||' else 0 end) ,0,0,
150 :ASOF_DATE_JULIEN - SUM(CASE WHEN bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID then f.wt_open_past_due_amt'||l_curr_suffix||' else 0 end)/
151 sum(CASE WHEN bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID then f.open_past_due_amt'||l_curr_suffix||' else 0 end)) FII_MEASURE4,
152 -- 0 days_past_due,
153 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
154 THEN f.on_time_payment_amt'||l_curr_suffix||' ELSE 0 END)
155 FII_MEASURE5,
156 SUM(CASE WHEN bitand(cal.record_type_id, 64) = 64
157 THEN f.late_payment_amt'||l_curr_suffix||' ELSE 0 END)
158 FII_MEASURE6,
159 SUM(CASE WHEN cal.report_date = &BIS_CURRENT_ASOF_DATE THEN (case when bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID then f.open_payment_amt'||l_curr_suffix||' else 0 end) ELSE 0 END)
160 FII_MEASURE7,
161 SUM(CASE WHEN bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
162 THEN f.wt_open_past_due_amt'||l_curr_suffix||' ELSE 0 END) wt_open_past_due_amt
163 FROM FII_AP_LIA_IB_MV f,
164 FII_TIME_STRUCTURES cal
165 WHERE f.time_id = cal.time_id
166 AND f.period_type_id = cal.period_type_id '||l_sup_where||' '||l_org_where||'
167 AND (bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID or bitand(cal.record_type_id,64)=64)
168 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE)
169 AND f.gid = :GID
170 GROUP BY f.'||l_viewby_id||'
171 )) f,
172 ('||l_viewby_string||') viewby_dim
173 WHERE f.id = viewby_dim.id
174 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
175 GROUP BY viewby_dim.value, viewby_dim.id
176 &ORDER_BY_CLAUSE';
177
178 -- Attach bind parameters
179 FII_PMV_UTIL.bind_variable(
180 p_sqlstmt=>sqlstmt,
181 p_page_parameter_tbl=>p_page_parameter_tbl,
182 p_sql_output=>open_pay_sum_sql,
183 p_bind_output_table=>open_pay_sum_output,
184 p_invoice_number=>l_invoice_number,
185 p_record_type_id=>l_record_type_id,
186 p_view_by=>l_viewby_id,
187 p_gid=>l_gid);
188
189 END GET_OPEN_PAY_TABLE_PORTLET;
190
191 /***************************************************************************/
192 -- For the Invoices Aging Portlet Report, Payables Status Page --
193
194 PROCEDURE get_inv_aging (
195 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
196 inv_age_sql OUT NOCOPY VARCHAR2,
197 inv_age_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
198 IS
199 l_viewby_dim VARCHAR2(240); -- what is the viewby
200 l_as_of_date DATE;
201 l_organization VARCHAR2(240);
202 l_supplier VARCHAR2(240);
203 l_currency VARCHAR2(240); -- random size, possibly high
204 l_viewby_id VARCHAR2(240); -- org_id or supplier_id
205 l_record_type_id NUMBER; --
206 l_gid NUMBER; -- 0,4 or 8
207 l_viewby_string VARCHAR2(240);
208 l_org_where VARCHAR2(240);
209 l_sup_where VARCHAR2(240);
210 l_curr_suffix VARCHAR2(240);
211 sqlstmt VARCHAR2(14000);
212 l_period_type VARCHAR2(240);
213 l_invoice_number VARCHAR2(240);
214 stmt1 VARCHAR2(500);
215 stmt2 VARCHAR2(500);
216 stmt3 VARCHAR2(500);
217 stmt4 VARCHAR2(500);
218 stmt5 VARCHAR2(500);
219 stmt6 VARCHAR2(500); /* Added for bug 3120355 */
220 BEGIN
221
222 FII_PMV_UTIL.get_parameters(
223 p_page_parameter_tbl=>p_page_parameter_tbl,
224 p_as_of_date=>l_as_of_date,
225 p_operating_unit=>l_organization,
226 p_supplier=>l_supplier,
227 p_invoice_number=>l_invoice_number,
228 p_period_type=>l_period_type,
229 p_record_type_id=>l_record_type_id,
230 p_view_by=>l_viewby_dim,
231 p_currency=>l_curr_suffix,
232 p_column_name=>l_viewby_id,
233 p_table_name=>l_viewby_string,
234 p_gid=>l_gid,
235 p_org_where=>l_org_where,
236 p_supplier_where=>l_sup_where);
237
238 -- l_record_type_id := 1143; -- no other value possible here
239 l_record_type_id := 512;
240
241 /*--------------------------------------------------------------+
242 | VIEWBY - Either Operating Unit / Supplier |
243 | VIEWBYID - Either org_id / supplier_id |
244 | FII_MEASURE1 - Invoice Age |
245 | FII_MEASURE2 - Number of Invoices |
246 +--------------------------------------------------------------*/
247
248 /****************Messages to be displayed in the report**************/ /* Added for bug 3120355 */
249 stmt1 := FND_MESSAGE.get_string('FII', 'FII_AP_PAST_DUE1');
250 stmt2 := FND_MESSAGE.get_string('FII', 'FII_AP_PAST_DUE2');
251 stmt3 := FND_MESSAGE.get_string('FII', 'FII_AP_PAST_DUE3');
252 stmt4 := FND_MESSAGE.get_string('FII', 'FII_AP_DUE3');
253 stmt5 := FND_MESSAGE.get_string('FII', 'FII_AP_DUE2');
254 stmt6 := FND_MESSAGE.get_string('FII', 'FII_AP_DUE1');
255
256 -- Construct the sql query to be sent
257 -- Following sql changed for customer bug-6028881. Added supplier to where clause
258 sqlstmt := '
259
260 SELECT decode(t1.multiplier,''1'', :FIIBIND1,
261 ''2'', :FIIBIND2,
262 ''3'', :FIIBIND3,
263 ''4'', :FIIBIND4,
264 ''5'', :FIIBIND5,
265 ''6'', :FIIBIND6) FII_MEASURE1,
266 DECODE(t1.multiplier, ''1'', SUM(open_past_due_bucket1_count),
267 ''2'', SUM(open_past_due_bucket2_count),
268 ''3'', SUM(open_past_due_bucket3_count),
269 ''4'', SUM(open_due_bucket3_count),
270 ''5'', SUM(open_due_bucket2_count),
271 ''6'', SUM(open_due_bucket1_count)) FII_MEASURE2
272 FROM FII_AP_LIA_IB_MV f,
273 fii_time_structures cal,
274 gl_row_multipliers t1
275 WHERE t1.multiplier <= 6
276 AND f.time_id = cal.time_id
277 AND f.period_type_id = cal.period_type_id
278 '||l_org_where||'
279 '||l_sup_where||'
280 AND bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
281 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE)
282 AND f.gid = :GID
283 Group by t1.multiplier order by t1.multiplier asc';
284
285 -- Attach bind parameters
286 FII_PMV_UTIL.bind_variable(
287 p_sqlstmt=>sqlstmt,
288 p_page_parameter_tbl=>p_page_parameter_tbl,
289 p_sql_output=>inv_age_sql,
290 p_bind_output_table=>inv_age_output,
291 p_invoice_number=>l_invoice_number,
292 p_record_type_id=>l_record_type_id,
293 p_view_by=>l_viewby_id,
294 p_gid=>l_gid,
295
296 p_fiibind1=>stmt1,
297 p_fiibind2=>stmt2,
298 p_fiibind3=>stmt3,
299 p_fiibind4=>stmt4,
300 p_fiibind5=>stmt5,
301 p_fiibind6=>stmt6
302 );
303
304 END get_inv_aging;
305
306 PROCEDURE get_pay_liability_pie (
307 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
308 open_pay_sum_pie_sql OUT NOCOPY VARCHAR2,
309 open_pay_sum_pie_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
310 IS
311 l_viewby_dim VARCHAR2(240); -- what is the viewby
312 l_as_of_date DATE;
313 l_organization VARCHAR2(240);
314 l_currency VARCHAR2(240); -- random size, possibly high
315 l_viewby_id VARCHAR2(240); -- org_id or supplier_id
316 l_record_type_id NUMBER; --
317 l_gid NUMBER; -- 0,4 or 8
318 l_viewby_string VARCHAR2(240);
319 l_org_where VARCHAR2(240);
320 l_sup_where VARCHAR2(240);
321 l_curr_suffix VARCHAR2(240);
322 l_sqlstmt VARCHAR2(14000);
323 l_supplier VARCHAR2(240);
324 l_period_type VARCHAR2(240);
325 l_invoice_number VARCHAR2(240);
326
327 BEGIN
328
329 -- Read the parameters passed
330 FII_PMV_UTIL.get_parameters(
331 p_page_parameter_tbl=>p_page_parameter_tbl,
332 p_as_of_date=>l_as_of_date,
333 p_operating_unit=>l_organization,
334 p_supplier=>l_supplier,
335 p_invoice_number=>l_invoice_number,
336 p_period_type=>l_period_type,
337 p_record_type_id=>l_record_type_id,
338 p_view_by=>l_viewby_dim,
339 p_currency=>l_curr_suffix,
340 p_column_name=>l_viewby_id,
341 p_table_name=>l_viewby_string,
342 p_gid=>l_gid,
343 p_org_where=>l_org_where,
344 p_supplier_where=>l_sup_where);
345
346 -- l_record_type_id := 1143; -- no other value possible in this report
347 l_record_type_id := 512; -- no other value possible in this report
348
349 -- so that no conditional query construction is required
350 IF(l_org_where is null) THEN
351 l_org_where := ' ';
352 END IF;
353
354 IF(l_sup_where is null) THEN
355 l_sup_where := ' ';
356 END IF;
357
358 /*--------------------------------------------------------------+
359 | VIEWBY - Either Operating Unit / Supplier |
360 | VIEWBYID - Either org_id / supplier_id |
361 | FII_MEASURE1 - Open Payables Amount |
362 +--------------------------------------------------------------*/
363
364 -- Construct the sql query to be sent
365 l_sqlstmt := '
366 SELECT viewby_dim.value VIEWBY,
367 viewby_dim.id VIEWBYID,
368 sum(f.FII_MEASURE1) FII_MEASURE1,
369 sum(f.FII_MEASURE2) FII_MEASURE2
370 FROM
371 (select id,
372 FII_MEASURE1,
373 ( rank() over (&ORDER_BY_CLAUSE nulls last, id)) - 1 rnk,
374 FII_MEASURE2
375 from
376 (SELECT f.'||l_viewby_id||' id,
377 SUM(f.open_amt'||l_curr_suffix||' ) FII_MEASURE1,
378 SUM(SUM(f.open_amt'||l_curr_suffix||')) over() FII_MEASURE2,
379 SUM(f.open_count) open_count,
380 SUM(f.open_due_count) due_count,
381 0 weighted_avg_days_due,
382 SUM(f.open_past_due_amt'||l_curr_suffix||' )
383 past_due_amt,
384 SUM(f.open_past_due_count) past_due_count,
385 0 weighted_avg_days_past_due
386 FROM FII_AP_LIA_IB_MV f ,fii_time_structures cal
387 WHERE f.time_id = cal.time_id
388 AND f.period_type_id = cal.period_type_id '||l_org_where||'
389 AND bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
390 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE)
391 AND f.gid = :GID
392 GROUP BY f.'||l_viewby_id||'
393 ) ) f,
394 ('||l_viewby_string||') viewby_dim
395 WHERE f.id = viewby_dim.id
396 and (f.rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
397 GROUP BY viewby_dim.value, viewby_dim.id
398 &ORDER_BY_CLAUSE';
399
400 -- Attach bind parameters
401 FII_PMV_UTIL.bind_variable(
402 p_sqlstmt=>l_sqlstmt,
403 p_page_parameter_tbl=>p_page_parameter_tbl,
404 p_sql_output=>open_pay_sum_pie_sql,
405 p_bind_output_table=>open_pay_sum_pie_output,
406 p_invoice_number=>l_invoice_number,
407 p_record_type_id=>l_record_type_id,
408 p_view_by=>l_viewby_id,
409 p_gid=>l_gid);
410
411 END get_pay_liability_pie;
412
413 /***************************************************************************/
414 -- For the KPI Portlet, Payables Status Page --
415
416 PROCEDURE get_kpi (
417 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
418 kpi_sql OUT NOCOPY VARCHAR2,
419 kpi_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
420 IS
421 l_viewby_dim VARCHAR2(240); -- what is the viewby
422 l_as_of_date DATE;
423 l_organization VARCHAR2(240);
424 l_supplier VARCHAR2(240);
425 l_currency VARCHAR2(240); -- random size, possibly high
426 l_viewby_id VARCHAR2(240); -- org_id or supplier_id
427 l_record_type_id NUMBER; --
428 l_gid NUMBER; -- 0,4 or 8
429 l_viewby_string VARCHAR2(240);
430 l_org_where VARCHAR2(240);
431 l_sup_where VARCHAR2(240);
432 l_curr_suffix VARCHAR2(240);
433 sqlstmt VARCHAR2(14000);
434 l_period_type VARCHAR2(240);
435 l_invoice_number VARCHAR2(240);
436
437
438 BEGIN
439
440 FII_PMV_UTIL.get_parameters(
441 p_page_parameter_tbl=>p_page_parameter_tbl,
442 p_as_of_date=>l_as_of_date,
443 p_operating_unit=>l_organization,
444 p_supplier=>l_supplier,
445 p_invoice_number=>l_invoice_number,
446 p_period_type=>l_period_type,
447 p_record_type_id=>l_record_type_id,
448 p_view_by=>l_viewby_dim,
449 p_currency=>l_curr_suffix,
450 p_column_name=>l_viewby_id,
451 p_table_name=>l_viewby_string,
452 p_gid=>l_gid,
453 p_org_where=>l_org_where,
454 p_supplier_where=>l_sup_where);
455
456 -- l_record_type_id := 1143; -- no other value possible here
457 l_record_type_id := 512; -- no other value possible here
458 --l_asof_date_julien := to_number(to_char(l_as_of_date,'J'));
459 /*--------------------------------------------------------------+
460 | VIEWBY - Either Operating Unit / Supplier |
461 | VIEWBYID - Either org_id / supplier_id |
462 | FII_MEASURE1 - Invoice Age |
463 | FII_MEASURE2 - Number of Invoices |
464 +--------------------------------------------------------------*/
465
466 -- Construct the sql query to be sent
467
468 sqlstmt := '
469
470 select sum(f.open_amt'||l_curr_suffix||') FII_MEASURE1,
471 sum(f.open_amt'||l_curr_suffix||') - sum(f.open_past_due_amt'||l_curr_suffix||') FII_MEASURE2,
472 sum(f.open_due_count) FII_MEASURE3,
473 decode(SUM(open_amt'||l_curr_suffix||' - open_past_due_amt'||l_curr_suffix||'),
474 0, 0,
475 (SUM(dd_open_due_amt'||l_curr_suffix||') /
476 SUM(open_amt'||l_curr_suffix||' - open_past_due_amt'||l_curr_suffix||')) -
477 :ASOF_DATE_JULIEN) FII_MEASURE4,
478 sum(f.open_past_due_amt'||l_curr_suffix||') FII_MEASURE5,
479 sum(f.open_past_due_count) FII_MEASURE6,
480 decode(SUM(f.open_past_due_amt'||l_curr_suffix||'), 0, 0,
481 :ASOF_DATE_JULIEN - SUM(f.dd_open_past_due_amt'||l_curr_suffix||')
482 /SUM(f.open_past_due_amt'||l_curr_suffix||')) FII_MEASURE7,
483 sum(f.open_discount_remaining'||l_curr_suffix||') FII_MEASURE8,
484 sum(f.open_discount_offered'||l_curr_suffix||') FII_MEASURE9,
485 sum(f.inv_on_hold_amt'||l_curr_suffix||') FII_MEASURE10,
486 sum(f.inv_on_hold_amt'||l_curr_suffix||')/sum(f.open_amt'||l_curr_suffix||')*100 FII_MEASURE11
487 from FII_AP_LIA_KPI_MV f,
488 fii_time_structures cal
489 WHERE f.time_id = cal.time_id
490 AND f.period_type_id = cal.period_type_id
491 '||l_org_where||'
492 AND bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
493 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE)';
494
495 -- Attach bind parameters
496 FII_PMV_UTIL.bind_variable(
497 p_sqlstmt=>sqlstmt,
498 p_page_parameter_tbl=>p_page_parameter_tbl,
499 p_sql_output=>kpi_sql,
500 p_bind_output_table=>kpi_output,
501 p_invoice_number=>l_invoice_number,
502 p_record_type_id=>l_record_type_id,
503 p_view_by=>l_viewby_id,
504 p_gid=>l_gid);
505
506 END get_kpi;
507
508 PROCEDURE get_hold_sum
509 ( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
510 get_hold_sum_sql out NOCOPY VARCHAR2,
511 get_hold_sum_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)IS
512
513 -- declaration section
514 sqlstmt VARCHAR2(14000);
515
516 l_as_of_date DATE;
517 l_operating_unit VARCHAR2(240);
518 l_supplier VARCHAR2(240);
519 l_invoice_number VARCHAR2(240);
520 l_period_type VARCHAR2(240);
521 l_record_type_id NUMBER;
522 l_view_by VARCHAR2(240);
523 l_currency VARCHAR2(240);
524 l_column_name VARCHAR2(240);
525 l_table_name VARCHAR2(240);
526 l_gid NUMBER;
527 l_org_where VARCHAR2(240);
528 l_supplier_where VARCHAR2(240);
529 l_url_1 VARCHAR2(240);
530 l_url_4 VARCHAR2(240);
531 l_order VARCHAR2(500);
532 l_order2 VARCHAR2(100);
533
534 BEGIN
535
536 -- Retrieve parameter info
537
538 FII_PMV_Util.Get_Parameters(
539 p_page_parameter_tbl,
540 l_as_of_date,
541 l_operating_unit,
542 l_supplier,
543 l_invoice_number,
544 l_period_type,
545 l_record_type_id,
546 l_view_by,
547 l_currency,
548 l_column_name,
549 l_table_name,
550 l_gid,
551 l_org_where,
552 l_supplier_where
553 );
554
555 -- l_record_type_id := 1143;
556 l_record_type_id := 512;
557
558
559 -- Decide on the viewby stuff and pk to be used
560 -- Map the l_column_name based on the selected viewby
561
562 l_url_1 := 'pFunctionName=FII_AP_INV_ON_HOLD_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_INV_ON_HOLD_DETAIL' ;
563
564 l_url_4 := 'pFunctionName=FII_AP_HOLD_SUM&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y' ;
565
566 -- to know sort direction DESC or ASC
567 IF (p_page_parameter_tbl.count > 0) THEN
568 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
569 IF p_page_parameter_tbl(i).parameter_name = 'ORDERBY' THEN
570 l_order := p_page_parameter_tbl(i).parameter_value;
571 END IF;
572 END LOOP;
573 END IF;
574
575 IF (INSTR(l_order,'ASC')>0) THEN
576 l_order2 := 'ASC';
577 ELSE
578 l_order2 := 'DESC';
579 END IF;
580
581
582 /*--------------------------------------------------------------+
583 | VIEWBY - Either Operating Unit / Supplier |
584 | VIEWBYID - Either org_id / supplier_id |
585 | FII_MEASURE1 - Invoices on Hold Amount |
586 | FII_MEASURE2 - Number of Invoices |
587 | FII_MEASURE3 - Number of Holds |
588 | FII_MEASURE4 - Grand Total of Invoices on Hold Amount |
589 | FII_MEASURE5 - Grand Total of Number of Invoices |
590 | FII_MEASURE6 - Grand Total of Number of Holds |
591 +--------------------------------------------------------------*/
592
593 -- Construct the sql query to be sent
594
595
596 sqlstmt := '
597 SELECT viewby_dim.value VIEWBY,
598 viewby_dim.id VIEWBYID,
599 sum(inv_on_hold_amt) FII_MEASURE1,
600 sum(inv_on_hold_count) FII_MEASURE2,
601 sum(no_of_holds) FII_MEASURE3,
602 sum(on_hold_past_due_amt) FII_MEASURE4,
603 sum(inv_on_hold_amt) - sum(on_hold_past_due_amt) FII_MEASURE5,
604 sum(gt_inv_on_hold_amt) FII_MEASURE6,
605 sum(gt_inv_on_hold_count) FII_MEASURE7,
606 sum(gt_no_of_holds) FII_MEASURE8,
607 sum(gt_on_hold_past_due_amt) FII_MEASURE9,
608 sum(gt_hold_due_amt) FII_MEASURE10,
609 decode('''||l_view_by||''',''ORGANIZATION+FII_OPERATING_UNITS'',
610 '''||l_url_4||''', ''SUPPLIER+POA_SUPPLIERS'','''||l_url_1||''',
611 null) FII_ATTRIBUTE1
612 FROM
613 (SELECT f.'||l_column_name||' id,
614 sum(f.inv_on_hold_amt'||l_currency||') inv_on_hold_amt,
615 ( rank() over (ORDER BY sum(f.inv_on_hold_amt'||l_currency||') '||l_order2||' nulls last, f.'||l_column_name||')) - 1 rnk,
616 sum(f.inv_on_hold_count) inv_on_hold_count,
617 sum(f.no_of_holds) no_of_holds,
618 sum(f.on_hold_past_due_amt'||l_currency||') on_hold_past_due_amt,
619 sum(sum(f.inv_on_hold_amt'||l_currency||')) over() gt_inv_on_hold_amt,
620 sum(sum(inv_on_hold_count)) over() gt_inv_on_hold_count,
621 sum(sum(no_of_holds)) over() gt_no_of_holds,
622 sum(sum(f.on_hold_past_due_amt'||l_currency||')) over() gt_on_hold_past_due_amt,
623 sum(sum(f.inv_on_hold_amt'||l_currency||') - sum(f.on_hold_past_due_amt'||l_currency||')) over() gt_hold_due_amt
624 FROM FII_AP_HLIA_I_MV f,
625 fii_time_structures cal
626 WHERE f.time_id = cal.time_id '||l_org_where||l_supplier_where||'
627 AND f.period_type_id = cal.period_type_id
628 AND bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
629 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE)
630 AND f.gid = :GID
631 GROUP BY f.'||l_column_name||'
632 ) f,
633 ('||l_table_name||') viewby_dim
634 WHERE f.id = viewby_dim.id
635 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
636 GROUP BY viewby_dim.value, viewby_dim.id
637 &ORDER_BY_CLAUSE ' ;
638
639 -- Binding Section
640
641 FII_PMV_Util.bind_variable(
642 p_sqlstmt=> sqlstmt,
643 p_page_parameter_tbl=>p_page_parameter_tbl,
644 p_sql_output=>get_hold_sum_sql,
645 p_bind_output_table=>get_hold_sum_output,
646 p_record_type_id=>l_record_type_id,
647 p_view_by=>l_view_by,
648 p_gid=>l_gid
649 );
650
651 END get_hold_sum;
652
653
654 END FII_AP_PAY_STATUS;
655