[Home] [Help]
PACKAGE BODY: APPS.FII_AP_PAID_INV_ACTIVITY
Source
1 PACKAGE BODY FII_AP_PAID_INV_ACTIVITY AS
2 /* $Header: FIIAPS2B.pls 120.1 2005/08/22 15:34:16 vkazhipu ship $ */
3
4 PROCEDURE GET_PAID_INV
5 ( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
6 paid_invoice_sql out NOCOPY VARCHAR2,
7 paid_invoice_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)IS
8 /* declaration section */
9 sqlstmt VARCHAR2(15000);
10
11 l_as_of_date DATE;
12 l_operating_unit VARCHAR2(240);
13 l_supplier VARCHAR2(240);
14 l_invoice_number VARCHAR2(240);
15 l_period_type VARCHAR2(240);
16 l_record_type_id NUMBER;
17 l_view_by VARCHAR2(240);
18 l_currency VARCHAR2(240);
19 l_column_name VARCHAR2(240);
20 l_table_name VARCHAR2(240);
21 l_gid NUMBER;
22 l_org_where VARCHAR2(240);
23 l_supplier_where VARCHAR2(240);
24 l_paid_inv_count VARCHAR2(240);
25 l_paid_on_time VARCHAR2(240);
26 l_paid_late VARCHAR2(240);
27 l_payment_count VARCHAR2(240);
28 l_period_type_str VARCHAR2(10);
29
30 l_url_1 VARCHAR2(10000);
31 l_url_4 VARCHAR2(10000);
32 l_url_2 VARCHAR2(10000);
33 l_url_3 VARCHAR2(10000);
34 BEGIN
35 FII_PMV_Util.Get_Parameters(
36 p_page_parameter_tbl,
37 l_as_of_date,
38 l_operating_unit,
39 l_supplier,
40 l_invoice_number,
41 l_period_type,
42 l_record_type_id,
43 l_view_by,
44 l_currency,
45 l_column_name,
46 l_table_name,
47 l_gid,
48 l_org_where,
49 l_supplier_where
50 );
51 l_period_type_str := FII_PMV_Util.get_period_type_suffix(l_period_type);
52 l_paid_inv_count := 'paid_inv_count'||l_period_type_str;
53 l_paid_on_time := 'paid_on_time_count'||l_period_type_str;
54 l_paid_late := 'paid_late_count'||l_period_type_str;
55 l_payment_count := 'payment_count'||l_period_type_str;
56
57 /**************Description of Measures, Attributes returned to
58 PMV *****
59 FII_MEASURE1 - Paid Amount, (Payments)
60 FII_MEASURE12 - Paid Amount in base currency, (Payments)
61 FII_MEASURE2 - Prior Paid amount,(Payments)
62 FII_MEASURE16 - Prior Paid amount in base currency,(Payments)
63 FII_MEASURE3 - Change (Payments)
64 FII_MEASURE4 - Number of Invoices, (Payments)
65 FII_MEASURE5 - Prior Number of Invoices,
66 FII_MEASURE6 - Change (Payments)
67 FII_MEASURE7 - Number of Payments,(Payments)
68 FII_MEASURE8 - Prior Number of Payments,
69 FII_MEASURE9 - Change (Payments)
70 FII_MEASURE10 - Paid on Time,
71 FII_MEASURE60 - # of invoices (Paid on Time)
72 FII_MEASURE61 = prior # of invoices (Paid on Time)
73 FII_MEASURE13 - Change (Paid on Time)
74 FII_MEASURE14 - Paid late Amount,
75 FII_MEASURE62 - # of invoices (Paid Late)
76 FII_MEASURE63 - prior # of invoices (Paid Late)
77 FII_MEASURE17 - Change (Paid Late)
78 FII_MEASURE18 - Electronic Disbursement Amount,
79 FII_MEASURE27 - Electronic Payment (calculation goes as MEASURE18/MEASURE1*100)
80 FII_MEASURE28 - Invoice to Payment Days
81 FII_MEASURE29 - Prior Invoice to Payment Days
82 FII_MEASURE30 - Change (between MEASURE51 and MEASURE52)
83 FII_MEASURE20 - Grand Total Amount (Payment)
84 FII_MEASURE21 - Grand Total Change (Payment)
85 FII_MEASURE22 - Grand Total # of invocies (Paid on Time)
86 FII_MEASURE23 - Grand Total Change (Paid on Time)
87 FII_MEASURE24 - Grand Total Amount (Paid Late)
88 FII_MEASURE25 - Grand Total # of invoices (Paid Late)
89 FII_MEASURE64 Paid Late % for Payment Porltet (this is refered by FII_CV3)
90 FII_MEASURE65 Change (Paid Late - Payment portlet). This is refered by FII_CV4.
91 FII_MEASURE66 - Grand Total(Paid LAte % Payment Portlet)
92 FII_MEASURE67 - Grand Total (Change - Payment Portlet)
93
94 FII_MEASURE26 - Grand Total Change (Paid Late)
95 FII_ATTRIBUTE2 - Grand Total Number of Invoices
96 FII_ATTRIBUTE3 - Grand Total Change
97 FII_ATTRIBUTE4 - Grand Total Number of Payments
98 FII_ATTRIBUTE5 - Grand Total Change (Payments)
99 FII_ATTRIBUTE6 - Grand Total Amount (Paid on Time)
100 FII_GRAND_TOTAL1 - Grand Total for Electronic Payment
101 FII_GRAND_TOTAL2 - Grand Total Invoice to Payment Days
102 FII_GRAND_TOTAL3 - Grand Total Change
103 FII_MEASURE31 - Graph Legends for Paid on Time
104 FII_MEASURE32 - Graph Legends for Paid Late
105 FII_EMPTY_COLSPAN - Empty Column span on top of Electronic Payment, Invoice to Payment Days, Change
106 FII_PAID_LATE - Colspan Paid Late
107 FII_PAID_TIME - Colspan Paid Time
108 FII_PAYMENTS_COLSPAN - Colspan Payments
109 FII_ATTRIBUTE10 - Drills for Number of Invoices
110 FII_ATTRIBUTE11 - Drills for Number of Payments
111 FII_MEASURE51 - Invoice to Payment Days (Measure28/Measure1)
112 FII_MEASURE52 - Prior invoice to payment days (Measure29/MEasure2)
113 ***********************************************************************/
114
115 /**************CustomDrills*******************************************/
116 l_url_1 :=
117
118 'pFunctionName=FII_AP_PAID_INV_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_PAID_INV_DETAIL&FII_CHECK_ID=All&FII_CHECK=All'
119
120 ;
121 l_url_4 :=
122 'pFunctionName=FII_AP_PAID_INV&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'
123
124 ;
125 l_url_2 :=
126 'pFunctionName=FII_AP_PAYMENT_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'
127
128 ;
129 l_url_3 :=
130 'pFunctionName=FII_AP_PAID_INV&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'
131
132 ;
133
134
135 /* Main SQL section */
136 sqlstmt := 'select VIEWBY,
137 VIEWBYID,
138 sum(FII_MEASURE1) FII_MEASURE1,
139 sum(FII_MEASURE12) FII_MEASURE12,
140 sum(FII_MEASURE2) FII_MEASURE2,
141 sum(FII_MEASURE16) FII_MEASURE16,
142 sum(FII_MEASURE4) FII_MEASURE4,
143 sum(FII_MEASURE5) FII_MEASURE5,
144 sum(FII_MEASURE7) FII_MEASURE7,
145 sum(FII_MEASURE8) FII_MEASURE8,
146 sum(FII_MEASURE10) FII_MEASURE10,
147 sum(FII_MEASURE11) FII_MEASURE11,
148 sum(FII_MEASURE60) FII_MEASURE60,
149 sum(FII_MEASURE61) FII_MEASURE61,
150 sum(FII_MEASURE14) FII_MEASURE14,
151 sum(FII_MEASURE15) FII_MEASURE15,
152 sum(FII_MEASURE62) FII_MEASURE62,
153 sum(FII_MEASURE63) FII_MEASURE63,
154 sum(FII_MEASURE18) FII_MEASURE18,
155 sum(FII_MEASURE28) FII_MEASURE28,
156 sum(FII_MEASURE29) FII_MEASURE29,
157 sum(FII_MEASURE20) FII_MEASURE20,
158 sum(FII_MEASURE24) FII_MEASURE24,
159 sum(FII_ATTRIBUTE2) FII_ATTRIBUTE2,
160 sum(FII_ATTRIBUTE4) FII_ATTRIBUTE4,
161 sum(FII_MEASURE23) FII_MEASURE23,
162 sum(FII_MEASURE26) FII_MEASURE26,
163 sum(FII_MEASURE21) FII_MEASURE21,
164 sum(FII_MEASURE22) FII_MEASURE22,
165 sum(FII_MEASURE25) FII_MEASURE25,
166 sum(FII_ATTRIBUTE6) FII_ATTRIBUTE6,
167 sum(FII_ATTRIBUTE3) FII_ATTRIBUTE3,
168 sum(FII_ATTRIBUTE5) FII_ATTRIBUTE5,
169 sum(FII_GRAND_TOTAL1) FII_GRAND_TOTAL1,
170 sum(FII_GRAND_TOTAL2) FII_GRAND_TOTAL2,
171 sum(FII_GRAND_TOTAL3) FII_GRAND_TOTAL3,
172 sum(FII_MEASURE66) FII_MEASURE66,
173 sum(FII_MEASURE67) FII_MEASURE67,
174 decode('''||l_view_by||''',''ORGANIZATION+FII_OPERATING_UNITS'','''||l_url_4||''', ''SUPPLIER+POA_SUPPLIERS'','''||l_url_1||''', null) FII_ATTRIBUTE10,
175 decode('''||l_view_by||''',''ORGANIZATION+FII_OPERATING_UNITS'','''||l_url_3||''', ''SUPPLIER+POA_SUPPLIERS'','''||l_url_2||''', null) FII_ATTRIBUTE11
176 FROM (select VIEWBY,
177 VIEWBYID,
178 FII_MEASURE1 FII_MEASURE1,
179 FII_MEASURE12 FII_MEASURE12,
180 FII_MEASURE2 FII_MEASURE2,
181 FII_MEASURE16 FII_MEASURE16,
182 FII_MEASURE4 FII_MEASURE4,
183 FII_MEASURE5 FII_MEASURE5,
184 FII_MEASURE7 FII_MEASURE7,
185 FII_MEASURE8 FII_MEASURE8,
186 FII_MEASURE10 FII_MEASURE10,
187 FII_MEASURE11 FII_MEASURE11,
188 FII_MEASURE60 FII_MEASURE60,
189 FII_MEASURE61 FII_MEASURE61,
190 FII_MEASURE14 FII_MEASURE14,
191 FII_MEASURE15 FII_MEASURE15,
192 FII_MEASURE62 FII_MEASURE62,
193 FII_MEASURE63 FII_MEASURE63,
194 FII_MEASURE18 FII_MEASURE18,
195 FII_MEASURE28 FII_MEASURE28,
196 FII_MEASURE29 FII_MEASURE29,
197 sum(FII_MEASURE1) over() FII_MEASURE20,
198 sum(FII_MEASURE14) over() FII_MEASURE24,
199 sum(FII_MEASURE4) over() FII_ATTRIBUTE2,
200 sum(FII_MEASURE7) over() FII_ATTRIBUTE4,
201 decode(sum(FII_MEASURE61) over(), 0, 0,
202 (sum(FII_MEASURE60) over() - sum(FII_MEASURE61) over()) * 100
203 /sum(FII_MEASURE61) over()
204 ) FII_MEASURE23, -- Bug #3969884
205 decode(sum(FII_MEASURE63) over(), 0, 0,
206 (sum(FII_MEASURE62) over() - sum(FII_MEASURE63) over()) * 100
207 /sum(FII_MEASURE63) over()
208 ) FII_MEASURE26, -- Bug #3969884
209 decode(sum(FII_MEASURE2) over(), 0, 0,
210 (sum(FII_MEASURE1) over() - sum(FII_MEASURE2) over()) *100/sum(FII_MEASURE2) over()) FII_MEASURE21, -- Bug #3969884
211 sum(FII_MEASURE60) over() FII_MEASURE22,
212 sum(FII_MEASURE62) over() FII_MEASURE25,
213 sum(FII_MEASURE10) over() FII_ATTRIBUTE6,
214 decode(sum(FII_MEASURE5) over(), 0, 0,
215 (sum(FII_MEASURE4) over() - sum(FII_MEASURE5) over()) *100
216 /sum(FII_MEASURE5) over()) FII_ATTRIBUTE3, -- Bug #3969884
217 decode(sum(FII_MEASURE8) over(), 0, 0,
218 (sum(FII_MEASURE7) over() - sum(FII_MEASURE8) over()) *100
219 /sum(FII_MEASURE8) over()) FII_ATTRIBUTE5, -- Bug #3969884
220 decode(sum(FII_MEASURE7) over(), 0, 0,
221 sum(FII_MEASURE18) over() * 100/sum(FII_MEASURE7) over()
222 ) FII_GRAND_TOTAL1, -- Bug #3969884
223 decode(sum(FII_MEASURE12) over(), 0, 0,
224 sum(FII_MEASURE28) over()
225 /sum(FII_MEASURE12) over() ) FII_GRAND_TOTAL2, -- Bug #3969884
226 case when sum(FII_MEASURE12) over() = 0 then 0 else
227 case when sum(FII_MEASURE16) over() = 0 then 0 else
228 case when sum(FII_MEASURE29) over() = 0 then 0 else
229 (
230 (sum(FII_MEASURE28) over()
231 /sum(FII_MEASURE12) over() -
232 sum(FII_MEASURE29) over()
233 /sum(FII_MEASURE16) over()
234 )*100)
235 /(sum(FII_MEASURE29) over()
236 /sum(FII_MEASURE16) over() )
237 end
238 end
239 end FII_GRAND_TOTAL3, -- Bug #3969884
240 decode(sum(FII_MEASURE4) over(), 0, 0,
241 sum(FII_MEASURE62) over()*100/sum(FII_MEASURE4) over()) FII_MEASURE66, -- Bug #3969884
242 case when sum(FII_MEASURE4) over() = 0 then 0 else
243 case when sum(FII_MEASURE5) over() = 0 then 0 else
244 case when sum(FII_MEASURE63) over() = 0 then 0 else
245 ((sum(FII_MEASURE62) over()*100/sum(FII_MEASURE4) over()) - (sum(FII_MEASURE63) over()*100/sum(FII_MEASURE5) over()))
246 end
247 end
248 end FII_MEASURE67, -- Bug #3969884
249 ( rank() over (&ORDER_BY_CLAUSE nulls last, VIEWBYID)) - 1 rnk
250 FROM (
251 select VIEWBY,
252 VIEWBYID,
253 sum(FII_MEASURE1) FII_MEASURE1,
254 sum(FII_MEASURE12) FII_MEASURE12,
255 sum(FII_MEASURE2) FII_MEASURE2,
256 sum(FII_MEASURE16) FII_MEASURE16,
257 sum(FII_MEASURE4) FII_MEASURE4,
258 sum(FII_MEASURE5) FII_MEASURE5,
259 sum(FII_MEASURE7) FII_MEASURE7,
260 sum(FII_MEASURE8) FII_MEASURE8,
261 sum(FII_MEASURE10) FII_MEASURE10,
262 sum(FII_MEASURE11) FII_MEASURE11,
263 sum(FII_MEASURE60) FII_MEASURE60,
264 sum(FII_MEASURE61) FII_MEASURE61,
265 sum(FII_MEASURE14) FII_MEASURE14,
266 sum(FII_MEASURE15) FII_MEASURE15,
267 sum(FII_MEASURE62) FII_MEASURE62,
268 sum(FII_MEASURE63) FII_MEASURE63,
269 sum(FII_MEASURE18) FII_MEASURE18,
270 sum(FII_MEASURE28) FII_MEASURE28,
271 sum(FII_MEASURE29) FII_MEASURE29
272 from(
273 select viewby_dim.value VIEWBY,
274 viewby_dim.id VIEWBYID,
275 sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end) FII_MEASURE1,
276 sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE then f.paid_amt_b else 0 end) FII_MEASURE12,
277 sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end) FII_MEASURE2,
278 sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE then f.paid_amt_b else 0 end) FII_MEASURE16,
279 sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE then f.'||l_paid_inv_count||' else 0 end) FII_MEASURE4,
283 sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE then f.paid_on_time_amt'||l_currency||' else 0 end) FII_MEASURE10,
280 sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE then f.'||l_paid_inv_count||' else 0 end) FII_MEASURE5,
281 sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE then f.'||l_payment_count||' else 0 end) FII_MEASURE7,
282 sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE then f.'||l_payment_count||' else 0 end) FII_MEASURE8,
284 sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE then f.paid_on_time_amt'||l_currency||' else 0 end) FII_MEASURE11,
285 0 FII_MEASURE60,
286 0 FII_MEASURE61,
287 sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE then f.paid_late_amt'||l_currency||' else 0 end) FII_MEASURE14,
288 sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE then f.paid_late_amt'||l_currency||' else 0 end) FII_MEASURE15,
289 0 FII_MEASURE62,
290 0 FII_MEASURE63,
291 sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE then f.e_payment_count else 0 end) FII_MEASURE18,
292 sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE then f.invoice_to_payment_days else 0 end) FII_MEASURE28,
293 sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE then f.invoice_to_payment_days else 0 end) FII_MEASURE29
294 from FII_AP_PAID_XB_MV f,
295 fii_time_structures cal, '
296 ||l_table_name||' viewby_dim
297 where f.time_id = cal.time_id
298 and f.period_type_id = cal.period_type_id
299 and bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
300 and cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
301 and f.gid = :GID
302 and f.'||l_column_name||' = viewby_dim.id '
303 ||l_org_where||l_supplier_where||
304 ' group by viewby_dim.value, viewby_dim.id, to_number(null)
305 union
306 select
307 viewby_dim.value VIEWBY,
308 viewby_dim.id VIEWBYID,
309 0 FII_MEASURE1,
310 0 FII_MEASURE12,
311 0 FII_MEASURE2,
312 0 FII_MEASURE16,
313 0 FII_MEASURE4,
314 0 FII_MEASURE5,
315 0 FII_MEASURE7,
316 0 FII_MEASURE8,
317 0 FII_MEASURE10,
318 0 FII_MEASURE11,
319 sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE then f.'||l_paid_on_time||' else 0 end) FII_MEASURE60,
320 sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE then f.'||l_paid_on_time||' else 0 end) FII_MEASURE61,
321 0 FII_MEASURE14,
322 0 FII_MEASURE15,
323 sum(case when cal.report_date =&BIS_CURRENT_ASOF_DATE then f.'||l_paid_late||' else 0 end) FII_MEASURE62,
324 sum(case when cal.report_date =&BIS_PREVIOUS_ASOF_DATE then f.'||l_paid_late||' else 0 end) FII_MEASURE63,
325 0 FII_MEASURE18,
326 0 FII_MEASURE28,
327 0 FII_MEASURE29
328 FROM FII_AP_PAYOL_XB_MV f,
329 fii_time_structures cal, '
330 ||l_table_name||' viewby_dim
331 where f.time_id = cal.time_id
332 and f.period_type_id = cal.period_type_id
333 and bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
334 and cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)
335 and f.gid = :GID
336 and f.'||l_column_name||' = viewby_dim.id '
337 ||l_org_where||l_supplier_where||
338 ' group by viewby_dim.value, viewby_dim.id, to_number(null))
339 group by VIEWBY, VIEWBYID)
340 )
341 where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
342 group by VIEWBY, VIEWBYID
343 &ORDER_BY_CLAUSE';
344
345 /* Binding Section */
346 FII_PMV_Util.bind_variable(
347 p_sqlstmt=>sqlstmt,
348 p_page_parameter_tbl=>p_page_parameter_tbl,
349 p_sql_output=>paid_invoice_sql,
350 p_bind_output_table=>paid_invoice_output,
351 p_record_type_id=>l_record_type_id,
352 p_gid=>l_gid
353 );
354
355 END;
356
357 PROCEDURE GET_PAID_INV_DISCOUNT
358 ( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
359 paid_invoice_sql out NOCOPY VARCHAR2,
360 paid_invoice_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)IS
361 /* declaration section */
362 sqlstmt VARCHAR2(20000);
363
364 l_as_of_date DATE;
365 l_operating_unit VARCHAR2(240);
366 l_supplier VARCHAR2(240);
367 l_invoice_number VARCHAR2(240);
368 l_period_type VARCHAR2(240);
369 l_record_type_id NUMBER;
370 l_view_by VARCHAR2(240);
371 l_currency VARCHAR2(240);
372 l_column_name VARCHAR2(240);
373 l_table_name VARCHAR2(240);
377 l_paid_inv_count VARCHAR2(240);
374 l_gid NUMBER;
375 l_org_where VARCHAR2(240);
376 l_supplier_where VARCHAR2(240);
378 l_paid_amt VARCHAR2(240);
379 l_per_type VARCHAR2(240);
380
381 l_url_1 VARCHAR2(1000);
382 l_url_4 VARCHAR2(1000);
383 BEGIN
384 FII_PMV_Util.Get_Parameters(
385 p_page_parameter_tbl,
386 l_as_of_date,
387 l_operating_unit,
388 l_supplier,
389 l_invoice_number,
390 l_period_type,
391 l_record_type_id,
392 l_view_by,
393 l_currency,
394 l_column_name,
395 l_table_name,
396 l_gid,
397 l_org_where,
398 l_supplier_where
399 );
400
401 l_per_type := FII_PMV_Util.get_period_type_suffix(l_period_type);
402
403 /**************Description of Measures, Attributes returned to PMV *****
404 FII_MEASURE1 - Paid Amount,(Payments)
405 FII_MEASURE2 - Prior Paid amount,(Payments)
406 FII_MEASURE3 - Change(Payments)
407 FII_MEASURE4 - Number of Invoices,(Payments)
408 FII_MEASURE28 - Gross Invoice Amount
409 FII_MEASURE5 - Total Invoice Amount,
410 FII_MEASURE6 - Percent (Discount Offered)
411 FII_MEASURE8 - Discount Offered Amount,
412 FII_MEASURE9 - Prior Discount Offered,
413 FII_MEASURE10 - change (Discount Offered)
414 FII_MEASURE11 - Paid Discount Taken,
415 FII_MEASURE12 - Prior Paid Discount Taken,
416 FII_MEASURE7 - Percent (Discount Taken)
417 FII_MEASURE13 - Change (Discount Taken)
418 FII_MEASURE14 - Discount Lost Amount,
419 FII_MEASURE15 - Prior Discount Lost
420 FII_MEASURE16 - Change (Discount Lost)
421 FII_MEASURE20 - Grand Total Amount (Payment)
422 FII_MEASURE21 - Grand Total Change (Payment)
423 FII_MEASURE22 - Grand Total Change (Discount Offered)
424 FII_MEASURE23 - Grand Total Change (Discount Taken)
425 FII_MEASURE24 - Grand Total Amount (Discount Lost)
426 FII_MEASURE25 - Grand Total Change (Discount Lost)
427 FII_MEASURE26 - Grand Total Amount (Discount Taken)
428 FII_ATTRIBUTE2 - Grand Total Number of Invoices (Payments)
429 FII_ATTRIBUTE3 - Grand Total Gross Invoice Amount (Payments)
430 FII_ATTRIBUTE4 - Grand Total Percent (Discount taken)
431 FII_ATTRIBUTE5 - Grand Total Percent (Discount Offered)
432 FII_ATTRIBUTE6 - Grand Total Amount (Discount Offered)
433 FII_MEASURE31 - Graph Legends for Discounts Taken
434 FII_MEASURE30 - Graph Legends for Discounts Offered
435 FII_PAYMENTS_COLSPAN - Colspan Payments
436 FII_EMPTY_COLSPAN - Colspan on top of Gross Invoice Amount
437 FII_DISCOUNT_OFFERED - Colspan
438 FII_DISCOUNT_TAKEN - Colspan
439 FII_DISCOUNT_LOST - Colspan
440 FII_ATTRIBUTE10 - Drill on Number of Invoices
441 FII_ATTRIBUTE13 - Grand Total Prior Percent (Discount taken)
442 FII_ATTRIBUTE14 - Grand Total Prior Percent (Discount Offered)
443 ***********************************************************************/
444
445 /***************************Custom Drills******************************/
446 l_url_1 := 'pFunctionName=FII_AP_PAID_INV_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_PAID_INV_DETAIL&FII_CHECK_ID=All&FII_CHECK=All'
447 ;
448 l_url_4 := 'pFunctionName=FII_AP_PAID_INV_DISCOUNT&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y'
449 ;
450
451 /* Main SQL section */
452 sqlstmt := '
453 select viewby_dim.value VIEWBY,
454 viewby_dim.id VIEWBYID,
455 sum(f.FII_MEASURE1) FII_MEASURE1,
456 sum(f.FII_MEASURE2) FII_MEASURE2,
457 sum(f.FII_MEASURE4) FII_MEASURE4,
458 sum(f.FII_MEASURE28) FII_MEASURE28,
459 sum(f.FII_MEASURE5) FII_MEASURE5,
460 sum(f.FII_MEASURE8) FII_MEASURE8,
461 sum(f.FII_MEASURE9) FII_MEASURE9,
462 sum(f.FII_MEASURE11) FII_MEASURE11,
463 sum(f.FII_MEASURE12) FII_MEASURE12,
464 sum(f.FII_MEASURE14) FII_MEASURE14,
465 sum(f.FII_MEASURE15) FII_MEASURE15,
466 sum(f.FII_MEASURE20) FII_MEASURE20,
467 sum(f.FII_MEASURE21) FII_MEASURE21,
468 sum(f.FII_MEASURE22) FII_MEASURE22,
469 sum(f.FII_MEASURE23) FII_MEASURE23,
470 sum(f.FII_MEASURE24) FII_MEASURE24,
471 sum(f.FII_MEASURE25) FII_MEASURE25,
472 sum(f.FII_MEASURE26) FII_MEASURE26,
473 sum(f.FII_ATTRIBUTE2) FII_ATTRIBUTE2,
474 sum(f.FII_ATTRIBUTE3) FII_ATTRIBUTE3,
475 sum(f.FII_ATTRIBUTE4) FII_ATTRIBUTE4,
476 sum(f.FII_ATTRIBUTE5) FII_ATTRIBUTE5,
477 sum(f.FII_ATTRIBUTE6) FII_ATTRIBUTE6,
478 decode('''||l_view_by||''',''ORGANIZATION+FII_OPERATING_UNITS'','''||l_url_4||''',
479 ''SUPPLIER+POA_SUPPLIERS'','''||l_url_1||''', null) FII_ATTRIBUTE10,
480 sum(f.FII_CV1) FII_CV1,
481 sum(f.FII_CV2) FII_CV2,
482 sum(f.FII_ATTRIBUTE7) FII_ATTRIBUTE7,
483 sum(f.FII_ATTRIBUTE8) FII_ATTRIBUTE8,
484 sum(f.FII_ATTRIBUTE13) FII_ATTRIBUTE13,
485 sum(f.FII_ATTRIBUTE14) FII_ATTRIBUTE14
486 from
487 (select ID,
491 FII_MEASURE28,
488 FII_MEASURE1,
489 FII_MEASURE2,
490 FII_MEASURE4,
492 FII_MEASURE5,
493 FII_MEASURE8,
494 FII_MEASURE9,
495 FII_MEASURE11,
496 FII_MEASURE12,
497 FII_MEASURE14,
498 FII_MEASURE15,
499 FII_MEASURE20,
500 FII_MEASURE21,
501 FII_MEASURE22,
502 FII_MEASURE23,
503 FII_MEASURE24,
504 FII_MEASURE25,
505 FII_MEASURE26,
506 FII_ATTRIBUTE2,
507 FII_ATTRIBUTE3,
508 FII_ATTRIBUTE4,
509 FII_ATTRIBUTE5,
510 FII_ATTRIBUTE6,
511 FII_CV1,
512 FII_CV2,
513 FII_ATTRIBUTE7,
514 FII_ATTRIBUTE8,
515 FII_ATTRIBUTE13,
516 FII_ATTRIBUTE14,
517 ( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk
518 from
519 (select f.'||l_column_name||' ID,
520 sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end) FII_MEASURE1,
521 sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end) FII_MEASURE2,
522 sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_inv_count'||l_per_type||' else 0 end) FII_MEASURE4,
523 sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then
524 (f.paid_amt'||l_currency||' + f.paid_dis_taken'||l_currency||') else 0 end) FII_MEASURE28,
525 sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_invoice_amt'||l_per_type||l_currency||' else 0 end) FII_MEASURE5,
526 sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_offered'||l_per_type||l_currency|| ' else 0 end) FII_MEASURE8,
527 sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_offered'||l_per_type||l_currency|| ' else 0 end) FII_MEASURE9,
528 sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end) FII_MEASURE11,
529 sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end) FII_MEASURE12,
530 sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_lost'||l_per_type||l_currency||' else 0 end) FII_MEASURE14,
531 sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_lost'||l_per_type||l_currency||' else 0 end) FII_MEASURE15,
532 sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end)) over()
533 FII_MEASURE20,
534 decode(nvl(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end),0), 0, 0,
535 (sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end)) over()
536 - sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end)) over())
537 *100/sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end)) over())
538 FII_MEASURE21,
539 decode(nvl(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_offered'||l_per_type||l_currency|| ' else 0 end),0), 0, 0,
540 (sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_offered'||l_per_type||l_currency|| ' else 0 end)) over()
541 - sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_offered'||l_per_type||l_currency|| ' else 0 end)) over())
542 *100/sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_offered'||l_per_type||l_currency|| ' else 0 end)) over())
543 FII_MEASURE22,
544 decode(nvl(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end),0), 0, 0,
545 (sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end)) over()
546 - sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end)) over())
547 *100/sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end)) over())
548 FII_MEASURE23,
549 sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_lost'||l_per_type||l_currency||' else 0 end)) over()
550 FII_MEASURE24,
551 decode(nvl(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_lost'||l_per_type||l_currency||' else 0 end),0), 0, 0,
552 (sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_lost'||l_per_type||l_currency||' else 0 end)) over()
553 - sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_lost'||l_per_type||l_currency||' else 0 end)) over())
554 *100/sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_lost'||l_per_type||l_currency||' else 0 end)) over())
555 FII_MEASURE25,
556 sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end)) over()
557 FII_MEASURE26,
561 + sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end)) over()
558 sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_inv_count'||l_per_type||' else 0 end)) over()
559 FII_ATTRIBUTE2,
560 sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end)) over()
562 FII_ATTRIBUTE3,
563 case when(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end)) = 0 then 0
564 when(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end)) = 0 then 0
565 else
566 sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end)) over()
567 *100/(sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end)) over()
568 + sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end)) over())
569 end
570 FII_ATTRIBUTE4,
571 decode(nvl(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_invoice_amt'||l_per_type||l_currency||' else 0 end),0), 0, 0,
572 sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_offered'||l_per_type||l_currency|| ' else 0 end)) over()
573 * 100/sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_invoice_amt'||l_per_type||l_currency||' else 0 end)) over())
574 FII_ATTRIBUTE5,
575 sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_offered'||l_per_type||l_currency|| ' else 0 end)) over()
576 FII_ATTRIBUTE6,
577 sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end) FII_CV1,
578 sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then
579 (f.paid_amt'||l_currency||' + f.paid_dis_taken'||l_currency||') else 0 end) FII_CV2,
580 sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_amt'||l_currency||' else 0 end)) over()
581 + sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end)) over()
582 FII_ATTRIBUTE7,
583 sum(sum(case when cal.report_date = &BIS_CURRENT_ASOF_DATE then f.paid_dis_taken'||l_currency||' else 0 end)) over()
584 FII_ATTRIBUTE8,
585 case when(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
586 then f.paid_amt'||l_currency||' else 0 end)) = 0
587 then 0
588 when(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
589 then f.paid_dis_taken'||l_currency||' else 0 end)) = 0
590 then 0
591 else
592 sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
593 then f.paid_dis_taken'||l_currency||' else 0 end)) over()
594 *100/(sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
595 then f.paid_amt'||l_currency||' else 0 end)) over()
596 + sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE
597 then f.paid_dis_taken'||l_currency||' else 0 end)) over()) end FII_ATTRIBUTE13,
598 decode(nvl(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_invoice_amt'||l_per_type||l_currency||' else 0 end),0), 0, 0,
599 sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_dis_offered'||l_per_type||l_currency|| ' else 0 end)) over()
600 * 100/sum(sum(case when cal.report_date = &BIS_PREVIOUS_ASOF_DATE then f.paid_invoice_amt'||l_per_type||l_currency||' else 0 end)) over()) FII_ATTRIBUTE14
601 from FII_AP_PAID_XB_MV f,
602 fii_time_structures cal
603 where f.time_id = cal.time_id
604 and f.period_type_id = cal.period_type_id
605 and bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
606 and cal.report_date in (&BIS_CURRENT_ASOF_DATE, &BIS_PREVIOUS_ASOF_DATE)'
607 ||l_org_where||l_supplier_where||'
608 and f.gid = :GID
609 group by f.'||l_column_name||')) f, '||l_table_name||' viewby_dim
610 where f.id = viewby_dim.id
611 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
612 group by viewby_dim.value, viewby_dim.id
613 &ORDER_BY_CLAUSE';
614
615 /* Binding Section */
616 FII_PMV_Util.bind_variable(
617 p_sqlstmt=>sqlstmt,
618 p_page_parameter_tbl=>p_page_parameter_tbl,
619 p_sql_output=>paid_invoice_sql,
620 p_bind_output_table=>paid_invoice_output,
621 p_record_type_id=>l_record_type_id,
622 p_gid=>l_gid
623 );
624
625 END;
626 END FII_AP_PAID_INV_ACTIVITY;