[Home] [Help]
PACKAGE BODY: APPS.FII_AP_OPEN_PAY_SUM
Source
1 PACKAGE BODY fii_ap_open_pay_sum AS
2 /* $Header: FIIAPS1B.pls 120.2 2006/01/26 00:02:05 vkazhipu noship $ */
3
4 -- For the Open Payables Summary report --
5 PROCEDURE get_pay_liability (
6 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
7 open_pay_sum_sql OUT NOCOPY VARCHAR2,
8 open_pay_sum_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
9 IS
10 i NUMBER;
11 l_viewby_dim VARCHAR2(240); -- what is the viewby
12 l_as_of_date DATE;
13 l_organization VARCHAR2(240);
14 l_currency VARCHAR2(240); -- random size, possibly high
15 l_viewby_id VARCHAR2(240); -- org_id or supplier_id
16 l_record_type_id NUMBER; --
17 l_gid NUMBER; -- 0,4 or 8
18 l_viewby_string VARCHAR2(240);
19 l_org_where VARCHAR2(240);
20 l_sup_where VARCHAR2(240);
21 l_curr_suffix VARCHAR2(240);
22 sqlstmt VARCHAR2(14000);
23 l_supplier VARCHAR2(240);
24 l_period_type VARCHAR2(240);
25 l_invoice_number VARCHAR2(240);
26 l_url_1 VARCHAR2(1000);
27 l_url_2 VARCHAR2(1000);
28 l_url_3 VARCHAR2(1000);
29 l_asof_date_julien NUMBER;
30
31 BEGIN
32
33 -- Read the parameters passed
34 FII_PMV_UTIL.get_parameters(
35 p_page_parameter_tbl=>p_page_parameter_tbl,
36 p_as_of_date=>l_as_of_date,
37 p_operating_unit=>l_organization,
38 p_supplier=>l_supplier,
39 p_invoice_number=>l_invoice_number,
40 p_period_type=>l_period_type,
41 p_record_type_id=>l_record_type_id,
42 p_view_by=>l_viewby_dim,
43 p_currency=>l_curr_suffix,
44 p_column_name=>l_viewby_id,
45 p_table_name=>l_viewby_string,
46 p_gid=>l_gid,
47 p_org_where=>l_org_where,
48 p_supplier_where=>l_sup_where);
49
50 l_record_type_id := 512; -- no other value possible in this report
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 -- To implement the selective drill functionality
64 IF l_viewby_dim = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
65 l_url_1 := 'pFunctionName=FII_AP_OPEN_PAY_SUM&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
66 l_url_2 := 'pFunctionName=FII_AP_OPEN_PAY_SUM&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
67 l_url_3 := 'pFunctionName=FII_AP_OPEN_PAY_SUM&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
68 ELSIF l_viewby_dim = 'SUPPLIER+POA_SUPPLIERS' THEN
69 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';
70 l_url_2 := 'pFunctionName=FII_AP_UNPAID_INV_DUE&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_UNPAID_INV_DUE';
71 l_url_3 := 'pFunctionName=FII_AP_UNPAID_INV_PAST_DUE&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_UNPAID_INV_PAST_DUE';
72 END IF;
73
74 /*--------------------------------------------------------------+
75 | VIEWBY Either Operating Unit / Supplier
76 | VIEWBYID Either org_id / supplier_id
77 | FII_MEASURE1 Open Payables Amount
78 | FII_MEASURE2 Total Number of Invoices
79 | FII_ATTRIBUTE12 Invoices Due
80 | FII_MEASURE3 Invoices Due Amount
81 | FII_KPI2 Hidden column for Invoices due amt
82 | FII_MEASURE4 Number of Invoices
83 | FII_KPI4 Hidden column for number of invoices measure
84 | FII_ATTRIBUTE2 Legend
85 | FII_MEASURE5 Weighted Average Days Due
86 | FII_KPI5 Hidden column for weighted avg days due
87 | FII_ATTRIBUTE13 Invoices Past Due
88 | FII_MEASURE6 Invoices Past Due Amount
89 | FII_KPI6
90 | FII_MEASURE7 Number of Invoices
91 | FII_KPI7 Hidden column for Invoice past due amt
92 | FII_ATTRIBUTE3 Legend
93 | FII_MEASURE8 Weighted Average Days Past Due
94 | FII_KPI8 Hidden column for number of invoices past due
95 | FII_MEASURE9 Grand Total (Open Payables Amount)
96 | FII_MEASURE10 Grand Total (Total Number of Invoices)
97 | FII_MEASURE11 Grand Total (Invoices Due Amount)
98 | FII_MEASURE12 Grand Total (Number of Invoices (Due))
99 | FII_MEASURE13 Grand Total (Invoices Past Due Amount)
100 | FII_MEASURE14 Grand Total (Number of Invoices (Past Due))
101 | FII_ATTRIBUTE5 Drill (Total Number of Invoices)
102 | FII_ATTRIBUTE6 Drill (Number of Invoices)
103 | FII_ATTRIBUTE7 Drill (Number of Invoices Past Due)
104 | FII_KPI1 Open Payables Amount
105 | FII_DIM1 - Grand total for use in KPI
106 | FII_DIM2 - Grand total for use in KPI
107 | FII_DIM3 - Grand total for use in KPI
108 | FII_DIM4 - Grand total for use in KPI
109 +----------------------------------------------------------------*/
110
111 -- Construct the sql query to be sent
112 sqlstmt := '
113 SELECT viewby_dim.value VIEWBY,
114 viewby_dim.id VIEWBYID,
115 f.FII_MEASURE1 FII_MEASURE1,
116 f.FII_MEASURE2 FII_MEASURE2,
117 f.FII_MEASURE3 FII_MEASURE3,
118 f.FII_MEASURE4 FII_MEASURE4,
119 f.FII_MEASURE5 FII_MEASURE5,
120 f.FII_MEASURE6 FII_MEASURE6,
121 f.FII_MEASURE7 FII_MEASURE7,
122 f.FII_MEASURE8 FII_MEASURE8,
123 f.FII_MEASURE9 FII_MEASURE9,
124 f.FII_MEASURE10 FII_MEASURE10,
125 f.FII_MEASURE11 FII_MEASURE11,
126 f.FII_MEASURE12 FII_MEASURE12,
127 f.FII_MEASURE13 FII_MEASURE13,
128 f.FII_MEASURE14 FII_MEASURE14,
129 '''||l_url_1||''' FII_ATTRIBUTE5,
130 '''||l_url_2||''' FII_ATTRIBUTE6,
131 '''||l_url_3||''' FII_ATTRIBUTE7,
132 to_number(null) FII_DIM1,
133 to_number(null) FII_DIM2,
134 to_number(null) FII_DIM3,
135 to_number(null) FII_DIM4
136 FROM
137 (SELECT
138 ID,
139 FII_MEASURE1,
140 FII_MEASURE2,
141 FII_MEASURE3,
142 FII_MEASURE4,
143 FII_MEASURE5,
144 FII_MEASURE6,
145 FII_MEASURE7,
146 FII_MEASURE8,
147 SUM(FII_MEASURE1) OVER() FII_MEASURE9,
148 SUM(FII_MEASURE2) OVER() FII_MEASURE10,
149 SUM(FII_MEASURE3) OVER() FII_MEASURE11,
150 SUM(FII_MEASURE4) OVER() FII_MEASURE12,
151 SUM(FII_MEASURE6) OVER() FII_MEASURE13,
152 SUM(FII_MEASURE7) OVER() FII_MEASURE14,
153 ( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk
154 FROM
155 (SELECT f.'||l_viewby_id||' id,
156 SUM(f.open_amt'||l_curr_suffix||' ) FII_MEASURE1,
157 SUM(f.open_count) FII_MEASURE2,
158 SUM(f.open_amt'||l_curr_suffix||' ) -
159 SUM(f.open_past_due_amt'||l_curr_suffix||' )
160 FII_MEASURE3,
161 SUM(f.open_due_count) FII_MEASURE4,
162 decode(SUM(open_amt'||l_curr_suffix||' - open_past_due_amt'||l_curr_suffix||'),
163 0, 0,
164 (SUM(wt_open_due_amt'||l_curr_suffix||') /
165 SUM(open_amt'||l_curr_suffix||' - open_past_due_amt'||l_curr_suffix||')) -
166 :ASOF_DATE_JULIEN )
167 FII_MEASURE5,
168 SUM(f.open_past_due_amt'||l_curr_suffix||' )
169 FII_MEASURE6,
170 SUM(f.open_past_due_count) FII_MEASURE7,
171 decode(SUM(open_past_due_amt'||l_curr_suffix||'), 0, 0,
172 :ASOF_DATE_JULIEN -
173 SUM(wt_open_past_due_amt'||l_curr_suffix||') /
174 SUM(open_past_due_amt'||l_curr_suffix||'))
175 FII_MEASURE8
176 FROM FII_AP_LIA_IB_MV f ,fii_time_structures cal
177 WHERE f.time_id = cal.time_id
178 AND f.period_type_id = cal.period_type_id '||l_sup_where||' '||l_org_where||'
179 AND bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
180 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE)
181 AND f.gid = :GID
182 GROUP BY f.'||l_viewby_id||'
183 HAVING (SUM(f.open_amt'||l_curr_suffix||' ) > 0
184 OR SUM(f.open_count) > 0)
185 )) f,
186 ('||l_viewby_string||') viewby_dim
187 WHERE f.id = viewby_dim.id
188 AND (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
189 &ORDER_BY_CLAUSE';
190
191
192 -- Attach bind parameters
193 FII_PMV_UTIL.bind_variable(
194 p_sqlstmt=>sqlstmt,
195 p_page_parameter_tbl=>p_page_parameter_tbl,
196 p_sql_output=>open_pay_sum_sql,
197 p_bind_output_table=>open_pay_sum_output,
198 p_invoice_number=>l_invoice_number,
199 p_record_type_id=>l_record_type_id,
200 p_view_by=>l_viewby_id,
201 p_gid=>l_gid);
202
203 END get_pay_liability;
204
205
206
207 -- For the Discount Opportunities Summary report --
208 PROCEDURE get_discount_opp_sum (
209 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
210 disc_opp_sum_sql OUT NOCOPY VARCHAR2,
211 disc_opp_sum_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
212 IS
213 i NUMBER;
214 l_viewby_dim VARCHAR2(240); -- what is the viewby
215 l_as_of_date DATE;
216 l_organization VARCHAR2(240);
217 l_supplier VARCHAR2(240);
218 l_currency VARCHAR2(240); -- random size, possibly high
219 l_viewby_id VARCHAR2(240); -- org_id or supplier_id
220 l_record_type_id NUMBER; --
221 l_gid NUMBER; -- 0,4 or 8
222 l_viewby_string VARCHAR2(240);
223 l_org_where VARCHAR2(240);
224 l_sup_where VARCHAR2(240);
225 l_curr_suffix VARCHAR2(240);
226 sqlstmt VARCHAR2(14000);
227 l_period_type VARCHAR2(240);
228 l_invoice_number VARCHAR2(240);
229 l_url_1 VARCHAR2(1000);
230 BEGIN
231
232 FII_PMV_UTIL.get_parameters(
233 p_page_parameter_tbl=>p_page_parameter_tbl,
234 p_as_of_date=>l_as_of_date,
235 p_operating_unit=>l_organization,
236 p_supplier=>l_supplier,
237 p_invoice_number=>l_invoice_number,
238 p_period_type=>l_period_type,
239 p_record_type_id=>l_record_type_id,
240 p_view_by=>l_viewby_dim,
241 p_currency=>l_curr_suffix,
242 p_column_name=>l_viewby_id,
243 p_table_name=>l_viewby_string,
244 p_gid=>l_gid,
245 p_org_where=>l_org_where,
246 p_supplier_where=>l_sup_where);
247
248 l_record_type_id := 512; -- no other value possible in this report
249
250 -- so that no conditional query construction is required
251 IF(l_org_where is null) THEN
252 l_org_where := ' ';
253 END IF;
254
255 IF(l_sup_where is null) THEN
256 l_sup_where := ' ';
257 END IF;
258
259 -- To implement the selective drill functionality
260 IF l_viewby_dim = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
261 l_url_1 := 'pFunctionName=FII_AP_DISCOUNT_OPP_SUM&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
262 ELSIF l_viewby_dim = 'SUPPLIER+POA_SUPPLIERS' THEN
263 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';
264 END IF;
265
266
267
268 /*----------------------------------------------------------------+
269 | VIEWBY Either Operating Unit / Supplier
270 | VIEWBYID Either org_id / supplier_id
271 | FII_ATTRIBUTE11 Open Payables
272 | FII_MEASURE1 Open Payables Amount
273 | FII_MEASURE2 Total Invoice Amount
274 | FII_MEASURE3 Total Number of Invoices
275 | FII_ATTRIBUTE12 Discount Offered
276 | FII_MEASURE4 Discount Offered
277 | FII_KPI2 Hidden column for Discount offered amt
278 | FII_MEASURE8 % Offered
279 | FII_ATTRIBUTE13 Discount Taken
280 | FII_MEASURE5 Discount Taken
281 | FII_ATTRIBUTE6 Discount Remaining
282 | FII_MEASURE9 % Taken of Offered
283 | FII_ATTRIBUTE14 Discount Lost
284 | FII_MEASURE6 Discount Lost
285 | FII_ATTRIBUTE3 Discount Lost
286 | FII_MEASURE10 % Lost of Offered
287 | FII_ATTRIBUTE10 Discount Remaining
288 | FII_MEASURE7 Discount Remaining
289 | FII_KPI1 Hidden column for Discount remaining Amount
290 | FII_ATTRIBUTE2 Discount Taken
291 | FII_MEASURE11 % Remaining of Offered
292 | FII_MEASURE13 Grand Total (Open Payables Amount)
293 | FII_MEASURE14 Grand Total (Total Invoice Amount)
294 | FII_MEASURE15 Grand Total (Total Number of Invoices)
295 | FII_MEASURE16 Grand Total (Discount Offered)
296 | FII_MEASURE17 Grand Total (Discount Taken)
297 | FII_MEASURE18 Grand Total (Discount Lost)
298 | FII_MEASURE19 Grand Total (Discount Remaining)
299 | FII_MEASURE20 Grand Total (% Offered)
300 | FII_MEASURE21 Grand Total (% Taken of Offered)
301 | FII_MEASURE22 Grand Total (% Lost of Offered)
302 | FII_MEASURE23 Grand Total (% Remaining of Offered)
303 | FII_ATTRIBUTE5 Drill (Total Number of Invoices)
304 | FII_CV1 Discount Offered
305 | FII_CV2 % Remaining
306 | FII_CV3 % Lost
307 +------------------------------------------------------------------*/
308
309 -- Construct the sql query to be sent
310
311 sqlstmt := '
312 SELECT viewby_dim.value VIEWBY,
313 viewby_dim.id VIEWBYID,
314 f.FII_MEASURE1 FII_MEASURE1,
315 f.FII_MEASURE2 FII_MEASURE2,
316 f.FII_MEASURE3 FII_MEASURE3,
317 f.FII_MEASURE4 FII_MEASURE4,
318 f.FII_MEASURE5 FII_MEASURE5,
319 f.FII_MEASURE6 FII_MEASURE6,
320 f.FII_MEASURE7 FII_MEASURE7,
321 f.FII_MEASURE13 FII_MEASURE13,
322 f.FII_MEASURE14 FII_MEASURE14,
323 f.FII_MEASURE15 FII_MEASURE15,
324 f.FII_MEASURE16 FII_MEASURE16,
325 f.FII_MEASURE17 FII_MEASURE17,
326 f.FII_MEASURE18 FII_MEASURE18,
327 f.FII_MEASURE19 FII_MEASURE19,
328 '''||l_url_1||''' FII_ATTRIBUTE5,
329 '''||l_url_1||''' FII_DIM1 /* Added for Bug 3096072 */
330 FROM
331 (select
332 ID,
333 FII_MEASURE1,
334 FII_MEASURE2,
335 FII_MEASURE3,
336 FII_MEASURE4,
337 ( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk,
338 FII_MEASURE5,
339 FII_MEASURE6,
340 FII_MEASURE7,
341 SUM(FII_MEASURE1) OVER() FII_MEASURE13,
342 SUM(FII_MEASURE2) OVER() FII_MEASURE14,
343 SUM(FII_MEASURE3) OVER() FII_MEASURE15,
344 SUM(FII_MEASURE4) OVER() FII_MEASURE16,
345 SUM(FII_MEASURE5) OVER() FII_MEASURE17,
346 SUM(FII_MEASURE6) OVER() FII_MEASURE18,
347 SUM(FII_MEASURE7) OVER() FII_MEASURE19
348 from
349 (SELECT
350 f.'||l_viewby_id||' ID,
351 SUM(f.open_amt'||l_curr_suffix||') FII_MEASURE1,
352 SUM(f.open_amt'||l_curr_suffix||') + SUM(f.open_payment_amt'||l_curr_suffix||')
353 + SUM(f.open_discount_taken'||l_curr_suffix||') FII_MEASURE2,
354 SUM(f.open_count) FII_MEASURE3,
355 SUM(f.open_discount_offered'||l_curr_suffix||') FII_MEASURE4,
356 SUM(f.open_discount_taken'||l_curr_suffix||') FII_MEASURE5,
357 SUM(f.open_discount_lost'||l_curr_suffix||') FII_MEASURE6,
358 SUM(f.open_discount_remaining'||l_curr_suffix||') FII_MEASURE7
359 FROM FII_AP_LIA_IB_MV f, fii_time_structures cal
360 WHERE f.time_id = cal.time_id
361 AND f.period_type_id = cal.period_type_id '||l_sup_where||' '||l_org_where||'
362 AND bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
363 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE)
364 AND f.gid = :GID
365 GROUP BY f.'||l_viewby_id||')) f,
366 ('||l_viewby_string||') viewby_dim
367 WHERE f.id = viewby_dim.id
368 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
369 &ORDER_BY_CLAUSE';
370
371 -- Attach bind parameters
372 FII_PMV_UTIL.bind_variable(
373 p_sqlstmt=>sqlstmt,
374 p_page_parameter_tbl=>p_page_parameter_tbl,
375 p_sql_output=>disc_opp_sum_sql,
376 p_bind_output_table=>disc_opp_sum_output,
377 p_invoice_number=>l_invoice_number,
378 p_record_type_id=>l_record_type_id,
379 p_view_by=>l_viewby_id,
380 p_gid=>l_gid);
381
382
383 END get_discount_opp_sum;
384
385
386 -- For the Invoices Due Aging Summary report --
387
388 PROCEDURE get_inv_due_age (
389 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
390 inv_due_sum_sql OUT NOCOPY VARCHAR2,
391 inv_due_sum_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
392 IS
393 i NUMBER;
394 l_viewby_dim VARCHAR2(240); -- what is the viewby
395 l_as_of_date DATE;
396 l_organization VARCHAR2(240);
397 l_supplier VARCHAR2(240);
398 l_currency VARCHAR2(240); -- random size, possibly high
399 l_viewby_id VARCHAR2(240); -- org_id or supplier_id
400 l_record_type_id NUMBER; --
401 l_gid NUMBER; -- 0,4 or 8
402 l_viewby_string VARCHAR2(240);
403 l_org_where VARCHAR2(240);
404 l_sup_where VARCHAR2(240);
405 l_curr_suffix VARCHAR2(240);
406 sqlstmt VARCHAR2(14000);
407 l_period_type VARCHAR2(240);
408 l_invoice_number VARCHAR2(240);
409 l_url_1 VARCHAR2(1000);
410 l_url_2 VARCHAR2(1000);
411 l_url_3 VARCHAR2(1000);
412 l_url_4 VARCHAR2(1000);
413 BEGIN
414
415 FII_PMV_UTIL.get_parameters(
416 p_page_parameter_tbl=>p_page_parameter_tbl,
417 p_as_of_date=>l_as_of_date,
418 p_operating_unit=>l_organization,
419 p_supplier=>l_supplier,
420 p_invoice_number=>l_invoice_number,
421 p_period_type=>l_period_type,
422 p_record_type_id=>l_record_type_id,
423 p_view_by=>l_viewby_dim,
424 p_currency=>l_curr_suffix,
425 p_column_name=>l_viewby_id,
426 p_table_name=>l_viewby_string,
427 p_gid=>l_gid,
428 p_org_where=>l_org_where,
429 p_supplier_where=>l_sup_where);
430
431 l_record_type_id := 512; -- no other value possible in this report
432
433 -- so that no conditional query construction is required
434 IF(l_org_where is null) THEN
435 l_org_where := ' ';
436 END IF;
437
438 IF(l_sup_where is null) THEN
439 l_sup_where := ' ';
440 END IF;
441
442 -- To implement the selective drill functionality
443 IF l_viewby_dim = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
444 l_url_1 := 'pFunctionName=FII_AP_INV_DUE_AGE_SUM&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
445 l_url_2 := 'pFunctionName=FII_AP_INV_DUE_AGE_SUM&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
446 l_url_3 := 'pFunctionName=FII_AP_INV_DUE_AGE_SUM&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
447 l_url_4 := 'pFunctionName=FII_AP_INV_DUE_AGE_SUM&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
448 ELSIF l_viewby_dim = 'SUPPLIER+POA_SUPPLIERS' THEN
449 l_url_1 := 'pFunctionName=FII_AP_UNPAID_INV_DUE&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_UNPAID_INV_DUE';
450 l_url_2 := 'pFunctionName=FII_AP_DUE_BUCKET1_INV_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_DUE_BUCKET1_INV_DETAIL';
451 l_url_3 := 'pFunctionName=FII_AP_DUE_BUCKET2_INV_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_DUE_BUCKET2_INV_DETAIL';
452 l_url_4 := 'pFunctionName=FII_AP_DUE_BUCKET3_INV_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_DUE_BUCKET3_INV_DETAIL';
453 END IF;
454
455
456
457 /*--------------------------------------------------------------+
458 | VIEWBY Either Operating Unit / Supplier
459 | VIEWBYID Either org_id / supplier_id
460 | FII_MEASURE1 Open Payables Amount
461 | FII_ATTRIBUTE10 Invoices Due
462 | FII_MEASURE2 Invoices Due Amount
463 | FII_MEASURE3 Number of Invoices
464 | FII_ATTRIBUTE11 Due in 1 - 15 Days
465 | FII_MEASURE4 Amount Due in 1 to 15 Days
466 | FII_ATTRIBUTE2 In 1-15 Days
467 | FII_MEASURE5 Number of Invoices
468 | FII_ATTRIBUTE12 Due in 16 - 30 Days
469 | FII_MEASURE6 Amount Due in 16 to 30 Days
470 | FII_ATTRIBUTE3 in 16 - 30 Days
471 | FII_MEASURE7 Number of Invoices
472 | FII_ATTRIBUTE13 Due After 30 Days
473 | FII_MEASURE8 Amount Due After 30 Days
474 | FII_ATTRIBUTE4 After 30 Days
475 | FII_MEASURE9 Number of Invoices
476 | FII_MEASURE13 Grand Total (Open Payables Amount)
477 | FII_MEASURE14 Grand Total (Invoices Due Amount)
478 | FII_MEASURE15 Grand Total (Number of Invoices)
479 | FII_MEASURE16 Grand Total (Amount Due in 1-15 Days)
480 | FII_MEASURE17 Grand Total (Number of Invoices)
481 | FII_MEASURE18 Grand Total (Amount Due in 16-30 Days)
482 | FII_MEASURE19 Grand Total (Number of Invoices)
483 | FII_MEASURE20 Grand Total (Amount Due after 30 Days)
484 | FII_MEASURE21 Grand Total (Number of Invoices)
485 | FII_ATTRIBUTE5 Drill (Number of Invoices)
486 | FII_ATTRIBUTE6 Drill (Number of Invoices :Bucket 1)
487 | FII_ATTRIBUTE7 Drill (Number of Invoices :Bucket 2)
488 | FII_ATTRIBUTE8 Drill (Number of Invoices :Bucket 3)
489 +----------------------------------------------------------------*/
490
491 -- Construct the sql query to be sent
492
493 sqlstmt := '
494 SELECT viewby_dim.value VIEWBY,
495 viewby_dim.id VIEWBYID,
496 f.FII_MEASURE1 FII_MEASURE1,
497 f.FII_MEASURE2 FII_MEASURE2,
498 f.FII_MEASURE3 FII_MEASURE3,
499 f.FII_MEASURE4 FII_MEASURE4,
500 f.FII_MEASURE5 FII_MEASURE5,
501 f.FII_MEASURE6 FII_MEASURE6,
502 f.FII_MEASURE7 FII_MEASURE7,
503 f.FII_MEASURE8 FII_MEASURE8,
504 f.FII_MEASURE9 FII_MEASURE9,
505 f.FII_MEASURE13 FII_MEASURE13,
506 f.FII_MEASURE14 FII_MEASURE14,
507 f.FII_MEASURE15 FII_MEASURE15,
508 f.FII_MEASURE16 FII_MEASURE16,
509 f.FII_MEASURE17 FII_MEASURE17,
510 f.FII_MEASURE18 FII_MEASURE18,
511 f.FII_MEASURE19 FII_MEASURE19,
512 f.FII_MEASURE20 FII_MEASURE20,
513 f.FII_MEASURE21 FII_MEASURE21,
514 '''||l_url_1||''' FII_ATTRIBUTE5,
515 '''||l_url_2||''' FII_ATTRIBUTE6,
516 '''||l_url_3||''' FII_ATTRIBUTE7,
517 '''||l_url_4||''' FII_ATTRIBUTE8
518 FROM
519 (SELECT
520 id,
521 FII_MEASURE1,
522 FII_MEASURE2,
523 FII_MEASURE3,
524 FII_MEASURE4,
525 FII_MEASURE5,
526 FII_MEASURE6,
527 FII_MEASURE7,
528 FII_MEASURE8,
529 FII_MEASURE9,
530 SUM(FII_MEASURE1) OVER() FII_MEASURE13,
531 SUM(FII_MEASURE2) OVER() FII_MEASURE14,
532 SUM(FII_MEASURE3) OVER() FII_MEASURE15,
533 SUM(FII_MEASURE4) OVER() FII_MEASURE16,
534 SUM(FII_MEASURE5) OVER() FII_MEASURE17,
535 SUM(FII_MEASURE6) OVER() FII_MEASURE18,
536 SUM(FII_MEASURE7) OVER() FII_MEASURE19,
537 SUM(FII_MEASURE8) OVER() FII_MEASURE20,
538 SUM(FII_MEASURE9) OVER() FII_MEASURE21,
539 ( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk
540 FROM
541 ( SELECT f.'||l_viewby_id||' id,
542 sum(f.open_amt'||l_curr_suffix||') FII_MEASURE1,
543 sum(f.open_amt'||l_curr_suffix||') -
544 sum(f.open_past_due_amt'||l_curr_suffix||') FII_MEASURE2,
545 sum(f.open_due_count) FII_MEASURE3,
546 sum(f.open_due_bucket3'||l_curr_suffix||') FII_MEASURE4,
547 sum(f.open_due_bucket3_count) FII_MEASURE5,
548 sum(f.open_due_bucket2'||l_curr_suffix||') FII_MEASURE6,
549 sum(f.open_due_bucket2_count) FII_MEASURE7,
550 sum(f.open_due_bucket1'||l_curr_suffix||') FII_MEASURE8,
551 sum(f.open_due_bucket1_count) FII_MEASURE9
552 FROM FII_AP_LIA_IB_MV f, fii_time_structures cal
553 WHERE f.time_id = cal.time_id
554 AND f.period_type_id = cal.period_type_id '||l_sup_where ||' '||l_org_where||'
555 AND bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
556 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE)
557 AND f.gid = :GID
558 HAVING sum(f.open_amt'||l_curr_suffix||') <> 0 /* bug # 3148973 */
559 group by f.'||l_viewby_id||')) f,
560 ('||l_viewby_string||') viewby_dim
561 WHERE f.id = viewby_dim.id
562 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
563 &ORDER_BY_CLAUSE';
564
565
566 -- Attach bind parameters
567 FII_PMV_UTIL.bind_variable(
568 p_sqlstmt=>sqlstmt,
569 p_page_parameter_tbl=>p_page_parameter_tbl,
570 p_sql_output=>inv_due_sum_sql,
571 p_bind_output_table=>inv_due_sum_output,
572 p_invoice_number=>l_invoice_number,
573 p_record_type_id=>l_record_type_id,
574 p_view_by=>l_viewby_id,
575 p_gid=>l_gid);
576
577
578 END get_inv_due_age;
579
580
581 -- For the Invoice Past Due Aging Summary report --
582
583 PROCEDURE get_inv_past_due_age (
584 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
585 inv_past_due_sum_sql OUT NOCOPY VARCHAR2,
586 inv_past_due_sum_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
587 IS
588 i NUMBER;
589 l_viewby_dim VARCHAR2(240); -- what is the viewby
590 l_as_of_date DATE;
591 l_organization VARCHAR2(240);
592 l_supplier VARCHAR2(240);
593 l_currency VARCHAR2(240); -- random size, possibly high
594 l_viewby_id VARCHAR2(240); -- org_id or supplier_id
595 l_record_type_id NUMBER; --
596 l_gid NUMBER; -- 0,4 or 8
597 l_viewby_string VARCHAR2(240);
598 inv_past_due_rec BIS_QUERY_ATTRIBUTES;
599 l_org_where VARCHAR2(240);
600 l_sup_where VARCHAR2(240);
601 l_curr_suffix VARCHAR2(240);
602 sqlstmt VARCHAR2(14000);
603 l_period_type VARCHAR2(240);
604 l_invoice_number VARCHAR2(240);
605 l_url_1 VARCHAR2(1000);
606 l_url_2 VARCHAR2(1000);
607 l_url_3 VARCHAR2(1000);
608 l_url_4 VARCHAR2(1000);
609 BEGIN
610
611 -- Read the parameters passed
612 FII_PMV_UTIL.get_parameters(
613 p_page_parameter_tbl=>p_page_parameter_tbl,
614 p_as_of_date=>l_as_of_date,
615 p_operating_unit=>l_organization,
616 p_supplier=>l_supplier,
617 p_invoice_number=>l_invoice_number,
618 p_period_type=>l_period_type,
619 p_record_type_id=>l_record_type_id,
620 p_view_by=>l_viewby_dim,
621 p_currency=>l_curr_suffix,
622 p_column_name=>l_viewby_id,
623 p_table_name=>l_viewby_string,
624 p_gid=>l_gid,
625 p_org_where=>l_org_where,
626 p_supplier_where=>l_sup_where);
627
628 l_record_type_id := 512; -- no other value possible in this report
629
630 -- so that no conditional query construction is required
631 IF(l_org_where is null) THEN
632 l_org_where := ' ';
633 END IF;
634
635 IF(l_sup_where is null) THEN
636 l_sup_where := ' ';
637 END IF;
638
639 -- To implement the selective drill functionality
640 IF l_viewby_dim = 'ORGANIZATION+FII_OPERATING_UNITS' THEN
641 l_url_1 := 'pFunctionName=FII_AP_INV_PAST_DUE_AGE_SUM&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
642 l_url_2 := 'pFunctionName=FII_AP_INV_PAST_DUE_AGE_SUM&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
643 l_url_3 := 'pFunctionName=FII_AP_INV_PAST_DUE_AGE_SUM&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
644 l_url_4 := 'pFunctionName=FII_AP_INV_PAST_DUE_AGE_SUM&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y';
645 ELSIF l_viewby_dim = 'SUPPLIER+POA_SUPPLIERS' THEN
646 l_url_1 := 'pFunctionName=FII_AP_UNPAID_INV_PAST_DUE&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_UNPAID_INV_PAST_DUE';
647 l_url_2 := 'pFunctionName=FII_AP_PDUE_BUCKET1_INV_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_PDUE_BUCKET1_INV_DETAIL';
648 l_url_3 := 'pFunctionName=FII_AP_PDUE_BUCKET2_INV_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_PDUE_BUCKET2_INV_DETAIL';
649 l_url_4 := 'pFunctionName=FII_AP_PDUE_BUCKET3_INV_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_PDUE_BUCKET3_INV_DETAIL';
650 END IF;
651
652
653 /*--------------------------------------------------------------+
654 | VIEWBY Either Operating Unit / Supplier
655 | VIEWBYID Either org_id / supplier_id
656 | FII_MEASURE1 Open Payables Amount
657 | FII_ATTRIBUTE10 Invoices Past Due
658 | FII_MEASURE2 Invoices Past Due Amount
659 | FII_MEASURE3 Number of Invoices
660 | FII_ATTRIBUTE11 Past Due in 1 - 15 Days
661 | FII_MEASURE4 Amount 1to 15 Days Past Due
662 | FII_ATTRIBUTE2 1-15 Days Past Due
663 | FII_MEASURE5 Number of Invoices
664 | FII_ATTRIBUTE12 Past Due in 16 - 30 Days
665 | FII_MEASURE6 Amount 16 to 30 Days Past Due
666 | FII_ATTRIBUTE3 16-30 Days Past Due
667 | FII_MEASURE7 Number of Invoices
668 | FII_ATTRIBUTE13 Past Due 30 Days
669 | FII_MEASURE8 Amount Over 30 Days Past Due
670 | FII_ATTRIBUTE4 Over 30 Days Past Due
671 | FII_MEASURE9 Number of Invoices
672 | FII_MEASURE13 Grand Total (Open Payables Amount)
673 | FII_MEASURE14 Grand Total (Invoices Past Due Amount)
674 | FII_MEASURE15 Grand Total (Number of Invoices)
675 | FII_MEASURE16 Grand Total (Amount 1-15 Days Past Due)
676 | FII_MEASURE17 Grand Total (Number of Invoices)
677 | FII_MEASURE18 Grand Total (Amount 16-30 Days Past Due)
678 | FII_MEASURE19 Grand Total (Number of Invoices)
679 | FII_MEASURE20 Grand Total (Amount over 30 Days Past Due)
680 | FII_MEASURE21 Grand Total (Number of Invoices)
681 | FII_ATTRIBUTE5 Drill (Number of Invoices)
682 | FII_ATTRIBUTE6 Drill (Invoices Past Due Bucket 1)
683 | FII_ATTRIBUTE7 Drill (Invoices Past Due Bucket 2)
684 | FII_ATTRIBUTE8 Drill (Invoices Past Due Bucket 3)
685 |
686 +----------------------------------------------------------------*/
687
688 -- Construct the sql query to be sent
689
690 sqlstmt := '
691 SELECT viewby_dim.value VIEWBY,
692 viewby_dim.id VIEWBYID,
693 f.FII_MEASURE1,
694 f.FII_MEASURE2,
695 f.FII_MEASURE3,
696 f.FII_MEASURE4,
697 f.FII_MEASURE5,
698 f.FII_MEASURE6,
699 f.FII_MEASURE7,
700 f.FII_MEASURE8,
701 f.FII_MEASURE9,
702 f.FII_MEASURE13,
703 f.FII_MEASURE14,
704 f.FII_MEASURE15,
705 f.FII_MEASURE16,
706 f.FII_MEASURE17,
707 f.FII_MEASURE18,
708 f.FII_MEASURE19,
709 f.FII_MEASURE20,
710 f.FII_MEASURE21,
711 '''||l_url_1||''' FII_ATTRIBUTE5,
712 '''||l_url_2||''' FII_ATTRIBUTE6,
713 '''||l_url_3||''' FII_ATTRIBUTE7,
714 '''||l_url_4||''' FII_ATTRIBUTE8
715 FROM
716 (SELECT
717 id,
718 FII_MEASURE1,
719 FII_MEASURE2,
720 FII_MEASURE3,
721 FII_MEASURE4,
722 FII_MEASURE5,
723 FII_MEASURE6,
724 FII_MEASURE7,
725 FII_MEASURE8,
726 FII_MEASURE9,
727 SUM(FII_MEASURE1) OVER() FII_MEASURE13,
728 SUM(FII_MEASURE2) OVER() FII_MEASURE14,
729 SUM(FII_MEASURE3) OVER() FII_MEASURE15,
730 SUM(FII_MEASURE4) OVER() FII_MEASURE16,
731 SUM(FII_MEASURE5) OVER() FII_MEASURE17,
732 SUM(FII_MEASURE6) OVER() FII_MEASURE18,
733 SUM(FII_MEASURE7) OVER() FII_MEASURE19,
734 SUM(FII_MEASURE8) OVER() FII_MEASURE20,
735 SUM(FII_MEASURE9) OVER() FII_MEASURE21,
736 ( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk
737 FROM
738 ( SELECT f.'||l_viewby_id||' id,
739 sum(f.open_amt'||l_curr_suffix||') FII_MEASURE1,
740 sum(f.open_past_due_amt'||l_curr_suffix||')
741 FII_MEASURE2,
742 sum(f.open_past_due_count) FII_MEASURE3,
743 sum(f.open_past_due_bucket3'||l_curr_suffix||')
744 FII_MEASURE4,
745 sum(f.open_past_due_bucket3_count) FII_MEASURE5,
746 sum(f.open_past_due_bucket2'||l_curr_suffix||')
747 FII_MEASURE6,
748 sum(f.open_past_due_bucket2_count) FII_MEASURE7,
749 sum(f.open_past_due_bucket1'||l_curr_suffix||')
750 FII_MEASURE8,
751 sum(f.open_past_due_bucket1_count) FII_MEASURE9
752 FROM FII_AP_LIA_IB_MV f, fii_time_structures cal
753 WHERE f.time_id = cal.time_id
754 AND f.period_type_id = cal.period_type_id '||l_sup_where ||' '||l_org_where||'
755 AND bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
756 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE)
757 AND f.gid = :GID group by f.'||l_viewby_id||')) f,
758 ('||l_viewby_string||') viewby_dim
759 WHERE f.id = viewby_dim.id
760 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
761 &ORDER_BY_CLAUSE';
762
763
764 -- Attach bind parameters
765 FII_PMV_UTIL.bind_variable(
766 p_sqlstmt=>sqlstmt,
767 p_page_parameter_tbl=>p_page_parameter_tbl,
768 p_sql_output=>inv_past_due_sum_sql,
769 p_bind_output_table=>inv_past_due_sum_output,
770 p_invoice_number=>l_invoice_number,
771 p_record_type_id=>l_record_type_id,
772 p_view_by=>l_viewby_id,
773 p_gid=>l_gid);
774
775
776 END get_inv_past_due_age;
777
778
779 END fii_ap_open_pay_sum;
780 -- End of Summary Reports package
781