[Home] [Help]
PACKAGE BODY: APPS.FII_AP_PAID_INV_DETAIL
Source
1 PACKAGE BODY FII_AP_PAID_INV_DETAIL AS
2 /* $Header: FIIAPD2B.pls 120.8 2006/02/28 17:49:01 vkazhipu ship $ */
3
4 PROCEDURE Get_Pay_Activity_History
5 ( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
6 pay_act_sql out NOCOPY VARCHAR2,
7 pay_act_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)IS
8 /* declaration section */
9 sqlstmt VARCHAR2(14000);
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
25 l_check_id NUMBER := 0;
26
27 stmt1 VARCHAR2(240);
28 stmt2 VARCHAR2(240);
29 stmt3 VARCHAR2(240);
30 stmt4 VARCHAR2(240);
31 stmt5 VARCHAR2(240);
32 stmt6 VARCHAR2(240);
33 stmt7 VARCHAR2(240);
34 stmt8 VARCHAR2(240);
35
36 l_date_mask VARCHAR2(240);
37 BEGIN
38 FII_PMV_Util.Get_Parameters(
39 p_page_parameter_tbl,
40 l_as_of_date,
41 l_operating_unit,
42 l_supplier,
43 l_invoice_number,
44 l_period_type,
45 l_record_type_id,
46 l_view_by,
47 l_currency,
48 l_column_name,
49 l_table_name,
50 l_gid,
51 l_org_where,
52 l_supplier_where
53 );
54
55 FII_PMV_Util.get_check_id(p_page_parameter_tbl, l_check_id);
56
57 /**************Description of Measures, Attributes returned to PMV *****
58 FII_MEASURE1 - Action,
59 FII_MEASURE2 - Date,
60 FII_MEASURE3 - User
61 **********************************************************************/
62
63
64 /****************Messages to be displayed in the report**************/
65 stmt1 := FND_MESSAGE.get_string('FII', 'FII_AP_CREATED');
66 stmt2 := FND_MESSAGE.get_string('FII', 'FII_AP_STOPPED');
67 stmt3 := FND_MESSAGE.get_string('FII', 'FII_AP_STOP_RELEASED');
68 stmt4 := FND_MESSAGE.get_string('FII', 'FII_AP_CLEARED');
69 stmt5 := FND_MESSAGE.get_string('FII', 'FII_AP_RECONCILED');
70 stmt6 := FND_MESSAGE.get_string('FII', 'FII_AP_UNRECONCILED');
71 stmt7 := FND_MESSAGE.get_string('FII', 'FII_AP_UNCLEARED');
72 stmt8 := FND_MESSAGE.get_string('FII', 'FII_AP_VOIDED');
73
74 /*get date mask */
75 FII_PMV_Util.get_format_mask(l_date_mask);
76
77
78 /* Main SQL section */
79
80 sqlstmt := 'select action FII_MEASURE1,
81 to_char(action_date, '''||l_date_mask||''') FII_MEASURE2,
82 usr.user_name FII_MEASURE3
83 from
84 (select :CREATED action,
85 creation_date action_date,
86 created_by by_whom
87 from ap_checks_all
88 where check_id=:CHECK_ID
89 and creation_date is not null
90 union all
91 select :STOPPED action,
92 stopped_date action_date,
93 stopped_by by_whom
94 from ap_checks_all
95 where check_id=:CHECK_ID
96 and stopped_date is not null
97 union all
98 select :STOP_RELEASED action,
99 released_date action_date,
100 released_by by_whom
101 from ap_checks_all
102 where check_id=:CHECK_ID
103 and released_date is not null
104 union all
105 select :CLEARED action,
106 creation_date action_date,
107 created_by by_whom
108 from ap_payment_history_all
109 where check_id=:CHECK_ID
110 and transaction_type=''PAYMENT CLEARING''
111 and matched_flag=''N''
112 and creation_date is not null
113 union all
114 select :RECONCILED action,
115 creation_date action_date,
116 created_by by_whom
117 from ap_payment_history_all
118 where check_id=:CHECK_ID
119 and transaction_type=''PAYMENT CLEARING''
120 and matched_flag=''Y''
121 and creation_date is not null
122 union all
123 select :UNRECONCILED action,
124 creation_date action_date,
125 created_by by_whom
126 from ap_payment_history_all
127 where check_id=:CHECK_ID
128 and transaction_type=''PAYMENT UNCLEARING''
129 and matched_flag=''Y''
130 and creation_date is not null
131 union all
132 select :UNCLEARED action,
133 creation_date action_date,
134 created_by by_whom
135 from ap_payment_history_all
136 where check_id=:CHECK_ID
137 and transaction_type=''PAYMENT UNCLEARING''
138 and matched_flag=''N''
139 and creation_date is not null
140 union all
141 select :VOIDED action,
142 apc.void_date action_date,
143 pay.last_updated_by by_whom
144 from ap_checks_all apc, ap_invoice_payments_all pay
145 where apc.check_id=:CHECK_ID
146 and apc.check_id=pay.check_id
147 and void_date is not null
148 ) a,
149 fnd_user_view usr
150 Where a.by_whom = usr.user_id
151 &ORDER_BY_CLAUSE';
152
153 /* Binding Section */
154 FII_PMV_Util.bind_variable(
155 p_sqlstmt=>sqlstmt,
156 p_page_parameter_tbl=>p_page_parameter_tbl,
157 p_sql_output=>pay_act_sql,
158 p_bind_output_table=>pay_act_output,
159 p_check_id=>l_check_id,
160
161 p_created=>stmt1,
162 p_stopped=>stmt2,
163 p_stop_released=>stmt3,
164 p_cleared=>stmt4,
165 p_reconciled=>stmt5,
166 p_unreconciled=>stmt6,
167 p_uncleared=>stmt7,
168 p_voided=>stmt8
169 );
170 END;
171
172 PROCEDURE Get_Paid_Inv_Detail
173 ( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
174 paid_inv_sql out NOCOPY VARCHAR2,
175 paid_inv_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)IS
176 /* declaration section */
177 sqlstmt VARCHAR2(14000);
178 sqlstmt1 VARCHAR2(14000);
179
180 l_as_of_date DATE;
181 l_operating_unit VARCHAR2(240);
182 l_supplier VARCHAR2(240);
183 l_invoice_number VARCHAR2(240);
184 l_period_type VARCHAR2(240);
185 l_record_type_id NUMBER;
186 l_view_by VARCHAR2(240);
187 l_currency VARCHAR2(240);
188 l_column_name VARCHAR2(240);
189 l_table_name VARCHAR2(240);
190 l_gid NUMBER;
191 l_org_where VARCHAR2(240);
192 l_supplier_where VARCHAR2(240);
193
194 l_discount_offered VARCHAR2(240);
195 l_late_payment_amt VARCHAR2(240);
196 l_on_time_payment_amt VARCHAR2(240);
197 l_discount_taken VARCHAR2(240);
198 l_discount_lost VARCHAR2(240);
199 l_payment_amount VARCHAR2(240);
200 l_invoice_amount VARCHAR2(240);
201 l_report_source VARCHAR2(240);
202 l_check_id NUMBER := 0;
203
204 l_url_1 VARCHAR2(1000);
205 l_url_2 VARCHAR2(1000);
206 l_url_3 VARCHAR2(1000);
207 l_url_4 VARCHAR2(1000);
208
209 l_yes VARCHAR2(240);
210 l_no VARCHAR2(240);
211
212 l_date_mask VARCHAR2(240);
213
214 l_period_start DATE;
215 l_days_into_period NUMBER;
216 l_cur_period NUMBER;
217 l_id_column VARCHAR2(240);
218 l_sysdate VARCHAR2(30);
219
220 BEGIN
221 FII_PMV_Util.Get_Parameters(
222 p_page_parameter_tbl,
223 l_as_of_date,
224 l_operating_unit,
225 l_supplier,
226 l_invoice_number,
227 l_period_type,
228 l_record_type_id,
229 l_view_by,
230 l_currency,
231 l_column_name,
232 l_table_name,
233 l_gid,
234 l_org_where,
235 l_supplier_where
236 );
237
238 FII_PMV_Util.Get_Report_Source(p_page_parameter_tbl, l_report_source);
239
240 l_discount_offered := FII_PMV_Util.get_base_curr_colname(l_currency, 'discount_offered');
241
242 l_late_payment_amt := FII_PMV_Util.get_base_curr_colname(l_currency, 'late_payment_amt');
243
244 l_payment_amount := FII_PMV_Util.get_base_curr_colname(l_currency, 'payment_amount');
245
246 l_on_time_payment_amt := FII_PMV_Util.get_base_curr_colname(l_currency, 'on_time_payment_amt');
247
248 l_discount_taken := FII_PMV_Util.get_base_curr_colname(l_currency, 'discount_taken');
249
250 l_discount_lost := FII_PMV_Util.get_base_curr_colname(l_currency, 'discount_lost');
251
252 If l_currency = '_prim_g' then
253 l_invoice_amount := 'prim_amount';
254 Elsif l_currency = '_sec_g' then
255 l_invoice_amount := 'sec_amount';
256 Elsif l_currency = '_b' then
257 l_invoice_amount := 'base_amount';
258 End if;
259
260 FII_PMV_Util.get_check_id(p_page_parameter_tbl, l_check_id);
261
262 FII_PMV_Util.Get_Period_Strt(
263 p_page_parameter_tbl,
264 l_period_start,
265 l_days_into_period,
266 l_cur_period,
267 l_id_column);
268
269 /**************Description of Measures, Attributes returned to PMV *****
270 FII_MEASURE1 - Invoice Number,
271 FII_MEASURE2 - Invoice Id,
272 FII_MEASURE3 - Invoice Type,
273 FII_MEASURE4 - Invoice Date,
274 FII_MEASURE5 - Entered Date,
275 FII_MEASURE6 - Due Date,
276 FII_MEASURE7 - Transaction Currency Code,
277 FII_MEASURE8 - Transaction Invoice Amount,
278 FII_MEASURE9 - Invoice Amoun,
279 FII_MEASURE10 - Paid Amount,
280 FII_MEASURE11 - Paid on Time Amount,
281 FII_MEASURE12 - Paid Late Amount,
282 FII_MEASURE13 - Ever on Hold,
283 FII_MEASURE14 - Discount Offered,
284 FII_MEASURE15 - Discount Taken,
285 FII_MEASURE16 - Discount Lost,
286 FII_MEASURE17 - Terms,
287 FII_MEASURE18 - Source
288 FII_MEASURE20 - Grand Total Invoice Amount
289 FII_MEASURE21 - Grand Total Discount Lost
290 FII_ATTRIBUTE2 - Grand Total Paid Amount
291 FII_ATTRIBUTE3 - Grand Total Paid on Time Amount
292 FII_ATTRIBUTE4 - Grand Total Paid Late Amount
293 FII_ATTRIBUTE5 - Grand Total Discount Offered
294 FII_ATTRIBUTE6 - Grand Total Discount Taken
295 ***********************************************************************/
296
297 --Added for Bug 4309974
298 SELECT TO_CHAR(TRUNC(sysdate),'DD/MM/YYYY') INTO l_sysdate FROM dual;
299
300 /***************Custom drill URLs************************************/
301 l_url_1 := 'AS_OF_DATE='||l_sysdate||'&pFunctionName=FII_AP_INV_ACTIVITY_HISTORY&pParamIds=Y&FII_INVOICE_ID=FII_MEASURE2&FII_INVOICE=FII_MEASURE1'
302 ;
303 l_url_2 := 'AS_OF_DATE='||l_sysdate||'&pFunctionName=FII_AP_SCHED_PAY_DISCOUNT&pParamIds=Y&FII_AP_INVOICE_ID=FII_MEASURE2&FII_INVOICE=FII_MEASURE1'
304 ;
305 l_url_3 := 'AS_OF_DATE='||l_sysdate||'&pFunctionName=FII_AP_HOLD_HISTORY&pParamIds=Y&FII_AP_INVOICE_ID=FII_MEASURE2&FII_INVOICE=FII_MEASURE1'
306 ;
307 -- l_url_4 := 'AS_OF_DATE='||l_sysdate||'&pFunctionName=FII_AP_INV_DIST_DETAIL&pParamIds=Y&FII_INVOICE_ID=FII_MEASURE2&FII_INVOICE=FII_MEASURE1'
308 l_url_4 := 'AS_OF_DATE='||l_sysdate||'&pFunctionName=FII_AP_INV_LINES_DETAIL&pParamIds=Y&FII_AP_INVOICE_ID=FII_MEASURE2&FII_INVOICE_NUM=FII_MEASURE1'
309 ;
310
311 /* get mls message for yes no */
312 FII_PMV_Util.get_yes_no_msg(l_yes, l_no);
313
314 /*get date mask */
315 FII_PMV_Util.get_format_mask(l_date_mask);
316
317 /* Performance Tuning
318 1.removed period type id = 1
319 2.Implemented start/end index.
320 3.driving table is fii_ap_pay_sched_b.
321 4.column aliases for the columns selected by sub-query match the AK MEASURE name
322 5.moved urls to top level. moved grand totals to second level.
323 3.AK FII_AP_PAID_INV_DETAIL # of rows displayed = -30, # of rows displayed in portlet = -10.
324 */
325
326
327 /* Main SQL section */
328 IF l_report_source = 'FII_AP_PAID_INV_DETAIL' then
329 sqlstmt := '
330 Select h.FII_MEASURE1 FII_MEASURE1,
331 h.FII_MEASURE2 FII_MEASURE2,
332 h.FII_MEASURE3 FII_MEASURE3,
333 h.FII_MEASURE4 FII_MEASURE4,
334 h.FII_MEASURE5 FII_MEASURE5,
335 h.FII_MEASURE6 FII_MEASURE6,
336 h.FII_MEASURE7 FII_MEASURE7,
337 h.FII_MEASURE8 FII_MEASURE8,
338 h.FII_MEASURE9 FII_MEASURE9,
339 h.FII_MEASURE10 FII_MEASURE10,
340 h.FII_MEASURE11 FII_MEASURE11,
341 h.FII_MEASURE12 FII_MEASURE12,
342 h.FII_MEASURE13 FII_MEASURE13,
343 h.FII_MEASURE14 FII_MEASURE14,
344 h.FII_MEASURE15 FII_MEASURE15,
345 h.FII_MEASURE16 FII_MEASURE16,
346 h.FII_MEASURE17 FII_MEASURE17,
347 h.FII_MEASURE18 FII_MEASURE18,
348 h.FII_MEASURE21 FII_MEASURE21,
349 h.FII_MEASURE22 FII_MEASURE22,
350 h.FII_ATTRIBUTE2 FII_ATTRIBUTE2,
351 h.FII_ATTRIBUTE3 FII_ATTRIBUTE3,
352 h.FII_ATTRIBUTE4 FII_ATTRIBUTE4,
353 h.FII_ATTRIBUTE5 FII_ATTRIBUTE5,
354 h.FII_ATTRIBUTE6 FII_ATTRIBUTE6,
355 '''||l_url_1||''' FII_ATTRIBUTE10,
356 '''||l_url_2||''' FII_ATTRIBUTE11,
357 '''||l_url_3||''' FII_ATTRIBUTE12,
358 '''||l_url_4||''' FII_ATTRIBUTE13
359 from
360 (
361 Select g.FII_MEASURE1 FII_MEASURE1,
362 g.FII_MEASURE2 FII_MEASURE2,
363 g.FII_MEASURE3 FII_MEASURE3,
364 g.FII_MEASURE4 FII_MEASURE4,
365 g.FII_MEASURE5 FII_MEASURE5,
366 g.FII_MEASURE6 FII_MEASURE6,
367 g.FII_MEASURE7 FII_MEASURE7,
368 g.FII_MEASURE8 FII_MEASURE8,
369 g.FII_MEASURE9 FII_MEASURE9,
373 g.FII_MEASURE13 FII_MEASURE13,
370 g.FII_MEASURE10 FII_MEASURE10,
371 g.FII_MEASURE11 FII_MEASURE11,
372 g.FII_MEASURE12 FII_MEASURE12,
374 g.FII_MEASURE14 FII_MEASURE14,
375 g.FII_MEASURE15 FII_MEASURE15,
376 g.FII_MEASURE16 FII_MEASURE16,
377 g.FII_MEASURE17 FII_MEASURE17,
378 g.FII_MEASURE18 FII_MEASURE18,
379 sum(g.FII_MEASURE9) over() FII_MEASURE21,
380 sum(g.FII_MEASURE16) over() FII_MEASURE22,
381 sum(g.FII_MEASURE10) over() FII_ATTRIBUTE2,
382 sum(g.FII_MEASURE11) over() FII_ATTRIBUTE3,
383 sum(g.FII_MEASURE12) over() FII_ATTRIBUTE4,
384 sum(g.FII_MEASURE14) over() FII_ATTRIBUTE5,
385 sum(g.FII_MEASURE15) over() FII_ATTRIBUTE6,
386 ( rank() over (&ORDER_BY_CLAUSE nulls last, g.FII_MEASURE2)) - 1 rnk
387 from
388 (
389 select
390 f.invoice_number FII_MEASURE1,
391 f.invoice_id FII_MEASURE2,
392 f.invoice_type FII_MEASURE3,
393 to_char(f.invoice_date,'''||l_date_mask||''') FII_MEASURE4,
394 to_char(f.entered_date,'''||l_date_mask||''') FII_MEASURE5,
395 to_char(min(f.due_date), '''||l_date_mask||''') FII_MEASURE6,
396 f.invoice_currency_code FII_MEASURE7,
397 sum(f.base_amount) FII_MEASURE8,
398 sum(f.invoice_amount) FII_MEASURE9,
399 sum(f.payment_amount) FII_MEASURE10,
400 sum(f.on_time_payment_amount) FII_MEASURE11,
401 sum(f.late_payment_amount) FII_MEASURE12,
402 decode(nvl(hold.FII_MEASURE13, ''N''), ''Y'', '''||l_yes||''', ''N'', '''||l_no||''') FII_MEASURE13,
403 sum(f.discount_offered) FII_MEASURE14,
404 sum(f.discount_taken) FII_MEASURE15,
405 sum(f.discount_lost) FII_MEASURE16,
406 term.name FII_MEASURE17,
407 f.source FII_MEASURE18
408 from
409 (
410 select base.invoice_number invoice_number,
411 base.invoice_id invoice_id,
412 base.invoice_type invoice_type,
413 base.invoice_date invoice_date,
414 base.entered_date entered_date,
415 min(f.due_date) due_date,
416 base.invoice_currency_code invoice_currency_code,
417 base.invoice_amount base_amount,
418 base.'||l_invoice_amount||' invoice_amount,
419 sum(f.'||l_payment_amount||') payment_amount,
420 sum(f.'||l_on_time_payment_amt||') on_time_payment_amount,
421 sum(f.'||l_late_payment_amt||') late_payment_amount,
422 base.'||l_discount_offered||' discount_offered,
423 sum(f.'||l_discount_taken||') discount_taken,
424 sum(f.'||l_discount_lost||') discount_lost,
425 base.source source,
426 base.terms_id,
427 base.org_id,
428 base.supplier_id
429 from fii_ap_invoice_b base,
430 fii_ap_pay_sched_b f
431 where f.action_date >= :PERIOD_START
432 and f.action_date <= &BIS_CURRENT_ASOF_DATE
433 and f.action = ''PAYMENT''
434 and base.invoice_id = f.invoice_id
435 and base.cancel_flag = ''N'' '
436 ||l_org_where||l_supplier_where|| '
437 group by base.invoice_number,
438 base.invoice_id,
439 base.invoice_type,
440 base.invoice_date,
441 base.entered_date,
442 base.invoice_currency_code,
443 base.invoice_amount,
444 base.'||l_invoice_amount||',
445 base.'||l_discount_offered||',
446 base.source,
447 base.terms_id,
448 base.org_id,
449 base.supplier_id
450 union
451 select base.invoice_number invoice_number,
452 base.invoice_id invoice_id,
453 base.invoice_type invoice_type,
454 base.invoice_date invoice_date,
455 base.entered_date entered_date,
456 min(base.due_date) due_date,
457 base.invoice_currency_code invoice_currency_code,
458 0 base_amount,
459 0 invoice_amount,
460 0 payment_amount,
464 0 discount_taken,
461 0 on_time_payment_amount,
462 0 late_payment_amount,
463 0 discount_offered,
465 sum(f.'||l_discount_lost||') discount_lost,
466 base.source source,
467 base.terms_id terms_id,
468 base.org_id org_id,
469 base.supplier_id supplier_id
470 from fii_ap_invoice_b base,
471 fii_ap_pay_sched_b f
472 where f.action_date >= :PERIOD_START
473 and f.action_date <= &BIS_CURRENT_ASOF_DATE
474 and f.action = ''DISCOUNT''
475 and base.invoice_id = f.invoice_id
476 and base.cancel_flag = ''N'' '
477 ||l_org_where||l_supplier_where|| '
478 and f.invoice_id in (select distinct f.invoice_id
479 from fii_ap_pay_sched_b f
480 where f.action_date >= :PERIOD_START
481 and f.action_date <= &BIS_CURRENT_ASOF_DATE
482 and f.action = ''PAYMENT''
483 '||l_org_where||l_supplier_where|| '
484 )
485 group by base.invoice_number,
486 base.invoice_id,
487 base.invoice_type,
488 base.invoice_date,
489 base.entered_date,
490 base.invoice_currency_code,
491 base.source,
492 base.terms_id,
493 base.org_id,
494 base.supplier_id
495 )
496 f, (select distinct invoice_id,
497 ''Y'' FII_MEASURE13
498 from fii_ap_inv_holds_b f
499 where 1 = 1
500 '||l_org_where||l_supplier_where|| '
501 group by invoice_id) hold,
502 ap_terms_tl term, POA_SUPPLIERS_V viewby_dim
503 where hold.invoice_id (+)= f.invoice_id
504 and f.SUPPLIER_ID = viewby_dim.id
505 and f.terms_id = term.term_id
506 and term.language = userenv(''LANG'')
507 group by f.invoice_number,
508 f.invoice_id,
509 f.invoice_type,
510 f.invoice_date,
511 f.entered_date,
512 f.invoice_currency_code,
513 hold.FII_MEASURE13,
514 term.name,
515 f.source
516 ) g
517 ) h
518 where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
519 &ORDER_BY_CLAUSE';
520 ELSIF ( l_report_source = 'FII_AP_PAID_INV_DETAIL_PYMT') then
521 sqlstmt := '
522 Select h.FII_MEASURE1 FII_MEASURE1,
523 h.FII_MEASURE2 FII_MEASURE2,
524 h.FII_MEASURE3 FII_MEASURE3,
525 h.FII_MEASURE4 FII_MEASURE4,
526 h.FII_MEASURE5 FII_MEASURE5,
527 h.FII_MEASURE6 FII_MEASURE6,
528 h.FII_MEASURE7 FII_MEASURE7,
529 h.FII_MEASURE8 FII_MEASURE8,
530 h.FII_MEASURE9 FII_MEASURE9,
531 h.FII_MEASURE10 FII_MEASURE10,
532 h.FII_MEASURE11 FII_MEASURE11,
533 h.FII_MEASURE12 FII_MEASURE12,
534 h.FII_MEASURE13 FII_MEASURE13,
535 h.FII_MEASURE14 FII_MEASURE14,
536 h.FII_MEASURE15 FII_MEASURE15,
537 h.FII_MEASURE16 FII_MEASURE16,
538 h.FII_MEASURE17 FII_MEASURE17,
539 h.FII_MEASURE18 FII_MEASURE18,
540 h.FII_MEASURE21 FII_MEASURE21,
541 h.FII_MEASURE22 FII_MEASURE22,
542 h.FII_ATTRIBUTE2 FII_ATTRIBUTE2,
543 h.FII_ATTRIBUTE3 FII_ATTRIBUTE3,
544 h.FII_ATTRIBUTE4 FII_ATTRIBUTE4,
545 h.FII_ATTRIBUTE5 FII_ATTRIBUTE5,
546 h.FII_ATTRIBUTE6 FII_ATTRIBUTE6,
547 '''||l_url_1||''' FII_ATTRIBUTE10,
548 '''||l_url_2||''' FII_ATTRIBUTE11,
549 '''||l_url_3||''' FII_ATTRIBUTE12,
550 '''||l_url_4||''' FII_ATTRIBUTE13
551 from
552 (
553 Select g.FII_MEASURE1 FII_MEASURE1,
554 g.FII_MEASURE2 FII_MEASURE2,
555 g.FII_MEASURE3 FII_MEASURE3,
556 g.FII_MEASURE4 FII_MEASURE4,
557 g.FII_MEASURE5 FII_MEASURE5,
558 g.FII_MEASURE6 FII_MEASURE6,
559 g.FII_MEASURE7 FII_MEASURE7,
560 g.FII_MEASURE8 FII_MEASURE8,
564 g.FII_MEASURE12 FII_MEASURE12,
561 g.FII_MEASURE9 FII_MEASURE9,
562 g.FII_MEASURE10 FII_MEASURE10,
563 g.FII_MEASURE11 FII_MEASURE11,
565 g.FII_MEASURE13 FII_MEASURE13,
566 g.FII_MEASURE14 FII_MEASURE14,
567 g.FII_MEASURE15 FII_MEASURE15,
568 g.FII_MEASURE16 FII_MEASURE16,
569 g.FII_MEASURE17 FII_MEASURE17,
570 g.FII_MEASURE18 FII_MEASURE18,
571 sum(g.FII_MEASURE9) over() FII_MEASURE21,
572 sum(g.FII_MEASURE16) over() FII_MEASURE22,
573 sum(g.FII_MEASURE10) over() FII_ATTRIBUTE2,
574 sum(g.FII_MEASURE11) over() FII_ATTRIBUTE3,
575 sum(g.FII_MEASURE12) over() FII_ATTRIBUTE4,
576 sum(g.FII_MEASURE14) over() FII_ATTRIBUTE5,
577 sum(g.FII_MEASURE15) over() FII_ATTRIBUTE6,
578 ( rank() over (&ORDER_BY_CLAUSE nulls last, g.FII_MEASURE2)) - 1 rnk
579
580 from
581 (
582 select
583 f.invoice_number FII_MEASURE1,
584 f.invoice_id FII_MEASURE2,
585 f.invoice_type FII_MEASURE3,
586 to_char(f.invoice_date,'''||l_date_mask||''') FII_MEASURE4,
587 to_char(f.entered_date,'''||l_date_mask||''') FII_MEASURE5,
588 to_char(min(f.due_date), '''||l_date_mask||''') FII_MEASURE6,
589 f.invoice_currency_code FII_MEASURE7,
590 sum(f.base_amount) FII_MEASURE8,
591 sum(f.invoice_amount) FII_MEASURE9,
592 sum(f.payment_amount) FII_MEASURE10,
593 sum(f.on_time_payment_amount) FII_MEASURE11,
594 sum(f.late_payment_amount) FII_MEASURE12,
595 decode(nvl(hold.FII_MEASURE13, ''N''), ''Y'', '''||l_yes||''', ''N'', '''||l_no||''') FII_MEASURE13,
596 sum(f.discount_offered) FII_MEASURE14,
597 sum(f.discount_taken) FII_MEASURE15,
598 sum(f.discount_lost) FII_MEASURE16,
599 term.name FII_MEASURE17,
600 f.source FII_MEASURE18
601 from
602 (
603 select base.invoice_number invoice_number,
604 base.invoice_id invoice_id,
605 base.invoice_type invoice_type,
606 base.invoice_date invoice_date,
607 base.entered_date entered_date,
608 min(f.due_date) due_date,
609 base.invoice_currency_code invoice_currency_code,
610 base.invoice_amount base_amount,
611 base.'||l_invoice_amount||' invoice_amount,
612 sum(f.'||l_payment_amount||') payment_amount,
613 sum(f.'||l_on_time_payment_amt||') on_time_payment_amount,
614 sum(f.'||l_late_payment_amt||') late_payment_amount,
615 base.'||l_discount_offered||' discount_offered,
616 sum(f.'||l_discount_taken||') discount_taken,
617 sum(f.'||l_discount_lost||') discount_lost,
618 base.source source,
619 base.terms_id,
620 base.org_id,
621 base.supplier_id
622 from fii_ap_invoice_b base,
623 fii_ap_pay_sched_b f
624 where f.action_date >= :PERIOD_START
625 and f.action_date <= &BIS_CURRENT_ASOF_DATE
626 and f.action = ''PAYMENT''
627 and f.check_id = :CHECK_ID
628 and base.invoice_id = f.invoice_id
629 and base.cancel_flag = ''N'' '
630 ||l_org_where||l_supplier_where|| '
631 group by base.invoice_number,
632 base.invoice_id,
633 base.invoice_type,
634 base.invoice_date,
635 base.entered_date,
636 base.invoice_currency_code,
637 base.invoice_amount,
638 base.'||l_invoice_amount||',
639 base.'||l_discount_offered||',
640 base.source,
641 base.terms_id,
642 base.org_id,
643 base.supplier_id
644 union
645 select base.invoice_number invoice_number,
646 base.invoice_id invoice_id,
647 base.invoice_type invoice_type,
648 base.invoice_date invoice_date,
649 base.entered_date entered_date,
650 min(base.due_date) due_date,
651 base.invoice_currency_code invoice_currency_code,
655 0 on_time_payment_amount,
652 0 base_amount,
653 0 invoice_amount,
654 0 payment_amount,
656 0 late_payment_amount,
657 0 discount_offered,
658 0 discount_taken,
659 sum(f.'||l_discount_lost||') discount_lost,
660 base.source source,
661 base.terms_id terms_id,
662 base.org_id org_id,
663 base.supplier_id supplier_id
664 from fii_ap_invoice_b base,
665 fii_ap_pay_sched_b f
666 where f.action_date >= :PERIOD_START
667 and f.action_date <= &BIS_CURRENT_ASOF_DATE
668 and f.action = ''DISCOUNT'' '
669 ||l_org_where||l_supplier_where|| '
670 and base.invoice_id = f.invoice_id
671 and base.cancel_flag = ''N''
672 and f.invoice_id in (select distinct f.invoice_id
673 from fii_ap_pay_sched_b f
674 where f.action_date >= :PERIOD_START
675 and f.action_date <= &BIS_CURRENT_ASOF_DATE
676 and f.action = ''PAYMENT''
677 and f.check_id = :CHECK_ID '
678 ||l_org_where||l_supplier_where|| '
679 )
680 group by base.invoice_number,
681 base.invoice_id,
682 base.invoice_type,
683 base.invoice_date,
684 base.entered_date,
685 base.invoice_currency_code,
686 base.source,
687 base.terms_id,
688 base.org_id,
689 base.supplier_id
690 )
691 f, (select distinct invoice_id,
692 ''Y'' FII_MEASURE13
693 from fii_ap_inv_holds_b f
694 where 1 = 1
695 '||l_org_where||l_supplier_where|| '
696 group by invoice_id) hold,
697 ap_terms_tl term, POA_SUPPLIERS_V viewby_dim
698 where hold.invoice_id (+)= f.invoice_id
699 and f.SUPPLIER_ID = viewby_dim.id '
700 ||l_org_where||l_supplier_where|| '
701 and f.terms_id = term.term_id
702 and term.language = userenv(''LANG'')
703 group by f.invoice_number,
704 f.invoice_id,
705 f.invoice_type,
706 f.invoice_date,
707 f.entered_date,
708 f.invoice_currency_code,
709 hold.FII_MEASURE13,
710 term.name,
711 f.source
712 ) g
713 ) h
714 where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
715 &ORDER_BY_CLAUSE';
716 ELSIF l_report_source = 'FII_AP_PAID_INV_DETAIL_PYLATE' then
717
718 sqlstmt := '
719 Select h.FII_MEASURE1 FII_MEASURE1,
720 h.FII_MEASURE2 FII_MEASURE2,
721 h.FII_MEASURE3 FII_MEASURE3,
722 h.FII_MEASURE4 FII_MEASURE4,
723 h.FII_MEASURE5 FII_MEASURE5,
724 h.FII_MEASURE6 FII_MEASURE6,
725 h.FII_MEASURE7 FII_MEASURE7,
726 h.FII_MEASURE8 FII_MEASURE8,
727 h.FII_MEASURE9 FII_MEASURE9,
728 h.FII_MEASURE10 FII_MEASURE10,
729 h.FII_MEASURE11 FII_MEASURE11,
730 h.FII_MEASURE12 FII_MEASURE12,
731 h.FII_MEASURE13 FII_MEASURE13,
732 h.FII_MEASURE14 FII_MEASURE14,
733 h.FII_MEASURE15 FII_MEASURE15,
734 h.FII_MEASURE16 FII_MEASURE16,
735 h.FII_MEASURE17 FII_MEASURE17,
736 h.FII_MEASURE18 FII_MEASURE18,
737 h.FII_MEASURE21 FII_MEASURE21,
738 h.FII_MEASURE22 FII_MEASURE22,
739 h.FII_ATTRIBUTE2 FII_ATTRIBUTE2,
740 h.FII_ATTRIBUTE3 FII_ATTRIBUTE3,
741 h.FII_ATTRIBUTE4 FII_ATTRIBUTE4,
742 h.FII_ATTRIBUTE5 FII_ATTRIBUTE5,
743 h.FII_ATTRIBUTE6 FII_ATTRIBUTE6,
744 '''||l_url_1||''' FII_ATTRIBUTE10,
745 '''||l_url_2||''' FII_ATTRIBUTE11,
746 '''||l_url_3||''' FII_ATTRIBUTE12,
747 '''||l_url_4||''' FII_ATTRIBUTE13
748 from
749 (
750 Select g.FII_MEASURE1 FII_MEASURE1,
751 g.FII_MEASURE2 FII_MEASURE2,
752 g.FII_MEASURE3 FII_MEASURE3,
756 g.FII_MEASURE7 FII_MEASURE7,
753 g.FII_MEASURE4 FII_MEASURE4,
754 g.FII_MEASURE5 FII_MEASURE5,
755 g.FII_MEASURE6 FII_MEASURE6,
757 g.FII_MEASURE8 FII_MEASURE8,
758 g.FII_MEASURE9 FII_MEASURE9,
759 g.FII_MEASURE10 FII_MEASURE10,
760 g.FII_MEASURE11 FII_MEASURE11,
761 g.FII_MEASURE12 FII_MEASURE12,
762 g.FII_MEASURE13 FII_MEASURE13,
763 g.FII_MEASURE14 FII_MEASURE14,
764 g.FII_MEASURE15 FII_MEASURE15,
765 g.FII_MEASURE16 FII_MEASURE16,
766 g.FII_MEASURE17 FII_MEASURE17,
767 g.FII_MEASURE18 FII_MEASURE18,
768 sum(g.FII_MEASURE9) over() FII_MEASURE21,
769 sum(g.FII_MEASURE16) over() FII_MEASURE22,
770 sum(g.FII_MEASURE10) over() FII_ATTRIBUTE2,
771 sum(g.FII_MEASURE11) over() FII_ATTRIBUTE3,
772 sum(g.FII_MEASURE12) over() FII_ATTRIBUTE4,
773 sum(g.FII_MEASURE14) over() FII_ATTRIBUTE5,
774 sum(g.FII_MEASURE15) over() FII_ATTRIBUTE6,
775 ( rank() over (&ORDER_BY_CLAUSE nulls last, g.FII_MEASURE2)) - 1 rnk
776 from
777 (
778 select
779 f.invoice_number FII_MEASURE1,
780 f.invoice_id FII_MEASURE2,
781 f.invoice_type FII_MEASURE3,
782 to_char(f.invoice_date,'''||l_date_mask||''') FII_MEASURE4,
783 to_char(f.entered_date,'''||l_date_mask||''') FII_MEASURE5,
784 to_char(min(f.due_date), '''||l_date_mask||''') FII_MEASURE6,
785 f.invoice_currency_code FII_MEASURE7,
786 sum(f.base_amount) FII_MEASURE8,
787 sum(f.invoice_amount) FII_MEASURE9,
788 sum(f.payment_amount) FII_MEASURE10,
789 sum(f.on_time_payment_amount) FII_MEASURE11,
790 sum(f.late_payment_amount) FII_MEASURE12,
791 decode(nvl(hold.FII_MEASURE13, ''N''), ''Y'', '''||l_yes||''', ''N'', '''||l_no||''') FII_MEASURE13,
792 sum(f.discount_offered) FII_MEASURE14,
793 sum(f.discount_taken) FII_MEASURE15,
794 sum(f.discount_lost) FII_MEASURE16,
795 term.name FII_MEASURE17,
796 f.source FII_MEASURE18
797 from
798 (
799 select base.invoice_number invoice_number,
800 base.invoice_id invoice_id,
801 base.invoice_type invoice_type,
802 base.invoice_date invoice_date,
803 base.entered_date entered_date,
804 min(f.due_date) due_date,
805 base.invoice_currency_code invoice_currency_code,
806 base.invoice_amount base_amount,
807 base.'||l_invoice_amount||' invoice_amount,
808 sum(f.'||l_payment_amount||') payment_amount,
809 sum(f.'||l_on_time_payment_amt||') on_time_payment_amount,
810 sum(f.'||l_late_payment_amt||') late_payment_amount,
811 base.'||l_discount_offered||' discount_offered,
812 sum(f.'||l_discount_taken||') discount_taken,
813 sum(f.'||l_discount_lost||') discount_lost,
814 base.source source,
815 base.terms_id,
816 base.org_id,
817 base.supplier_id
818 from fii_ap_invoice_b base,
819 fii_ap_pay_sched_b f
820 where f.action_date >= :PERIOD_START
821 and f.action_date <= &BIS_CURRENT_ASOF_DATE
822 and f.action = ''PAYMENT''
823 and f.check_id = :CHECK_ID
824 and f.'||l_late_payment_amt||'<> 0
825 and base.invoice_id = f.invoice_id
826 and base.cancel_flag = ''N'' '
827 ||l_org_where||l_supplier_where|| '
828 group by base.invoice_number,
829 base.invoice_id,
830 base.invoice_type,
831 base.invoice_date,
832 base.entered_date,
833 base.invoice_currency_code,
834 base.invoice_amount,
835 base.'||l_invoice_amount||',
836 base.'||l_discount_offered||',
837 base.source,
838 base.terms_id,
839 base.org_id,
840 base.supplier_id
841 union
842 select base.invoice_number invoice_number,
843 base.invoice_id invoice_id,
847 min(base.due_date) due_date,
844 base.invoice_type invoice_type,
845 base.invoice_date invoice_date,
846 base.entered_date entered_date,
848 base.invoice_currency_code invoice_currency_code,
849 0 base_amount,
850 0 invoice_amount,
851 0 payment_amount,
852 0 on_time_payment_amount,
853 0 late_payment_amount,
854 0 discount_offered,
855 0 discount_taken,
856 sum(f.'||l_discount_lost||') discount_lost,
857 base.source source,
858 base.terms_id terms_id,
859 base.org_id org_id,
860 base.supplier_id supplier_id
861 from fii_ap_invoice_b base,
862 fii_ap_pay_sched_b f
863 where f.action_date >= :PERIOD_START
864 and f.action_date <= &BIS_CURRENT_ASOF_DATE
865 and f.action = ''DISCOUNT'' '
866 ||l_org_where||l_supplier_where|| '
867 and base.invoice_id = f.invoice_id
868 and base.cancel_flag = ''N''
869 and f.invoice_id in (select distinct f.invoice_id
870 from fii_ap_pay_sched_b f
871 where f.action_date >= :PERIOD_START
872 and f.action_date <= &BIS_CURRENT_ASOF_DATE
873 and f.action = ''PAYMENT''
874 and f.check_id = :CHECK_ID
875 and f.'||l_late_payment_amt||'<> 0 '
876 ||l_org_where||l_supplier_where|| '
877 )
878 group by base.invoice_number,
879 base.invoice_id,
880 base.invoice_type,
881 base.invoice_date,
882 base.entered_date,
883 base.invoice_currency_code,
884 base.source,
885 base.terms_id,
886 base.org_id,
887 base.supplier_id
888 )
889 f, (select distinct invoice_id,
890 ''Y'' FII_MEASURE13
891 from fii_ap_inv_holds_b f
892 where 1 = 1
893 '||l_org_where||l_supplier_where|| '
894 group by invoice_id) hold,
895 ap_terms_tl term, POA_SUPPLIERS_V viewby_dim
896 where hold.invoice_id (+)= f.invoice_id
897 and f.SUPPLIER_ID = viewby_dim.id '
898 ||l_org_where||l_supplier_where||'
899 and f.terms_id = term.term_id
900 and term.language = userenv(''LANG'')
901 group by f.invoice_number,
902 f.invoice_id,
903 f.invoice_type,
904 f.invoice_date,
905 f.entered_date,
906 f.invoice_currency_code,
907 hold.FII_MEASURE13,
908 term.name,
909 f.source
910 ) g
911 ) h
912 where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
913 &ORDER_BY_CLAUSE';
914
915 ELSIF l_report_source = 'FII_AP_PAID_INV_DETAIL_PYTIME' then
916
917 sqlstmt := '
918 Select h.FII_MEASURE1 FII_MEASURE1,
919 h.FII_MEASURE2 FII_MEASURE2,
920 h.FII_MEASURE3 FII_MEASURE3,
921 h.FII_MEASURE4 FII_MEASURE4,
922 h.FII_MEASURE5 FII_MEASURE5,
923 h.FII_MEASURE6 FII_MEASURE6,
924 h.FII_MEASURE7 FII_MEASURE7,
925 h.FII_MEASURE8 FII_MEASURE8,
926 h.FII_MEASURE9 FII_MEASURE9,
927 h.FII_MEASURE10 FII_MEASURE10,
928 h.FII_MEASURE11 FII_MEASURE11,
929 h.FII_MEASURE12 FII_MEASURE12,
930 h.FII_MEASURE13 FII_MEASURE13,
931 h.FII_MEASURE14 FII_MEASURE14,
932 h.FII_MEASURE15 FII_MEASURE15,
933 h.FII_MEASURE16 FII_MEASURE16,
934 h.FII_MEASURE17 FII_MEASURE17,
935 h.FII_MEASURE18 FII_MEASURE18,
936 h.FII_MEASURE21 FII_MEASURE21,
937 h.FII_MEASURE22 FII_MEASURE22,
938 h.FII_ATTRIBUTE2 FII_ATTRIBUTE2,
939 h.FII_ATTRIBUTE3 FII_ATTRIBUTE3,
940 h.FII_ATTRIBUTE4 FII_ATTRIBUTE4,
941 h.FII_ATTRIBUTE5 FII_ATTRIBUTE5,
942 h.FII_ATTRIBUTE6 FII_ATTRIBUTE6,
943 '''||l_url_1||''' FII_ATTRIBUTE10,
947 from
944 '''||l_url_2||''' FII_ATTRIBUTE11,
945 '''||l_url_3||''' FII_ATTRIBUTE12,
946 '''||l_url_4||''' FII_ATTRIBUTE13
948 (
949 Select g.FII_MEASURE1 FII_MEASURE1,
950 g.FII_MEASURE2 FII_MEASURE2,
951 g.FII_MEASURE3 FII_MEASURE3,
952 g.FII_MEASURE4 FII_MEASURE4,
953 g.FII_MEASURE5 FII_MEASURE5,
954 g.FII_MEASURE6 FII_MEASURE6,
955 g.FII_MEASURE7 FII_MEASURE7,
956 g.FII_MEASURE8 FII_MEASURE8,
957 g.FII_MEASURE9 FII_MEASURE9,
958 g.FII_MEASURE10 FII_MEASURE10,
959 g.FII_MEASURE11 FII_MEASURE11,
960 g.FII_MEASURE12 FII_MEASURE12,
961 g.FII_MEASURE13 FII_MEASURE13,
962 g.FII_MEASURE14 FII_MEASURE14,
963 g.FII_MEASURE15 FII_MEASURE15,
964 g.FII_MEASURE16 FII_MEASURE16,
965 g.FII_MEASURE17 FII_MEASURE17,
966 g.FII_MEASURE18 FII_MEASURE18,
967 sum(g.FII_MEASURE9) over() FII_MEASURE21,
968 sum(g.FII_MEASURE16) over() FII_MEASURE22,
969 sum(g.FII_MEASURE10) over() FII_ATTRIBUTE2,
970 sum(g.FII_MEASURE11) over() FII_ATTRIBUTE3,
971 sum(g.FII_MEASURE12) over() FII_ATTRIBUTE4,
972 sum(g.FII_MEASURE14) over() FII_ATTRIBUTE5,
973 sum(g.FII_MEASURE15) over() FII_ATTRIBUTE6,
974 ( rank() over (&ORDER_BY_CLAUSE nulls last, g.FII_MEASURE2)) - 1 rnk
975 from
976 (
977 select
978 f.invoice_number FII_MEASURE1,
979 f.invoice_id FII_MEASURE2,
980 f.invoice_type FII_MEASURE3,
981 to_char(f.invoice_date,'''||l_date_mask||''') FII_MEASURE4,
982 to_char(f.entered_date,'''||l_date_mask||''') FII_MEASURE5,
983 to_char(min(f.due_date), '''||l_date_mask||''') FII_MEASURE6,
984 f.invoice_currency_code FII_MEASURE7,
985 sum(f.base_amount) FII_MEASURE8,
986 sum(f.invoice_amount) FII_MEASURE9,
987 sum(f.payment_amount) FII_MEASURE10,
988 sum(f.on_time_payment_amount) FII_MEASURE11,
989 sum(f.late_payment_amount) FII_MEASURE12,
990 decode(nvl(hold.FII_MEASURE13, ''N''), ''Y'', '''||l_yes||''', ''N'', '''||l_no||''') FII_MEASURE13,
991 sum(f.discount_offered) FII_MEASURE14,
992 sum(f.discount_taken) FII_MEASURE15,
993 sum(f.discount_lost) FII_MEASURE16,
994 term.name FII_MEASURE17,
995 f.source FII_MEASURE18
996 from
997 (
998 select base.invoice_number invoice_number,
999 base.invoice_id invoice_id,
1000 base.invoice_type invoice_type,
1001 base.invoice_date invoice_date,
1002 base.entered_date entered_date,
1003 min(f.due_date) due_date,
1004 base.invoice_currency_code invoice_currency_code,
1005 base.invoice_amount base_amount,
1006 base.'||l_invoice_amount||' invoice_amount,
1007 sum(f.'||l_payment_amount||') payment_amount,
1008 sum(f.'||l_on_time_payment_amt||') on_time_payment_amount,
1009 sum(f.'||l_late_payment_amt||') late_payment_amount,
1010 base.'||l_discount_offered||' discount_offered,
1011 sum(f.'||l_discount_taken||') discount_taken,
1012 sum(f.'||l_discount_lost||') discount_lost,
1013 base.source source,
1014 base.terms_id,
1015 base.org_id,
1016 base.supplier_id
1017 from fii_ap_invoice_b base,
1018 fii_ap_pay_sched_b f
1019 where f.action_date >= :PERIOD_START
1020 and f.action_date <= &BIS_CURRENT_ASOF_DATE
1021 and f.action = ''PAYMENT''
1022 and f.check_id = :CHECK_ID
1023 and f.no_days_late = 0
1024 and base.invoice_id = f.invoice_id
1025 and base.cancel_flag = ''N'' '
1026 ||l_org_where||l_supplier_where||'
1027 group by base.invoice_number,
1028 base.invoice_id,
1029 base.invoice_type,
1030 base.invoice_date,
1031 base.entered_date,
1032 base.invoice_currency_code,
1033 base.invoice_amount,
1034 base.'||l_invoice_amount||',
1038 base.org_id,
1035 base.'||l_discount_offered||',
1036 base.source,
1037 base.terms_id,
1039 base.supplier_id
1040 union
1041 select base.invoice_number invoice_number,
1042 base.invoice_id invoice_id,
1043 base.invoice_type invoice_type,
1044 base.invoice_date invoice_date,
1045 base.entered_date entered_date,
1046 min(base.due_date) due_date,
1047 base.invoice_currency_code invoice_currency_code,
1048 0 base_amount,
1049 0 invoice_amount,
1050 0 payment_amount,
1051 0 on_time_payment_amount,
1052 0 late_payment_amount,
1053 0 discount_offered,
1054 0 discount_taken,
1055 sum(f.'||l_discount_lost||') discount_lost,
1056 base.source source,
1057 base.terms_id terms_id,
1058 base.org_id org_id,
1059 base.supplier_id supplier_id
1060 from fii_ap_invoice_b base,
1061 fii_ap_pay_sched_b f
1062 where f.action_date >= :PERIOD_START
1063 and f.action_date <= &BIS_CURRENT_ASOF_DATE
1064 and f.action = ''DISCOUNT'' '
1065 ||l_org_where||l_supplier_where|| '
1066 and base.invoice_id = f.invoice_id
1067 and base.cancel_flag = ''N''
1068 and f.invoice_id in (select distinct f.invoice_id
1069 from fii_ap_pay_sched_b f
1070 where f.action_date >= :PERIOD_START
1071 and f.action_date <= &BIS_CURRENT_ASOF_DATE
1072 and f.action = ''PAYMENT''
1073 and f.check_id = :CHECK_ID
1074 and f.no_days_late = 0 '
1075 ||l_org_where||l_supplier_where||'
1076 )
1077 group by base.invoice_number,
1078 base.invoice_id,
1079 base.invoice_type,
1080 base.invoice_date,
1081 base.entered_date,
1082 base.invoice_currency_code,
1083 base.source,
1084 base.terms_id,
1085 base.org_id,
1086 base.supplier_id
1087 )
1088 f, (select distinct invoice_id,
1089 ''Y'' FII_MEASURE13
1090 from fii_ap_inv_holds_b f
1091 where 1 = 1
1092 '||l_org_where||l_supplier_where|| '
1093 group by invoice_id) hold,
1094 ap_terms_tl term, POA_SUPPLIERS_V viewby_dim
1095 where hold.invoice_id (+)= f.invoice_id
1096 and f.SUPPLIER_ID = viewby_dim.id '
1097 ||l_org_where||l_supplier_where||'
1098 and f.terms_id = term.term_id
1099 and term.language = userenv(''LANG'')
1100 group by f.invoice_number,
1101 f.invoice_id,
1102 f.invoice_type,
1103 f.invoice_date,
1104 f.entered_date,
1105 f.invoice_currency_code,
1106 hold.FII_MEASURE13,
1107 term.name,
1108 f.source
1109 ) g
1110 ) h
1111 where (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
1112 &ORDER_BY_CLAUSE';
1113
1114 END IF;
1115
1116
1117 /* Binding Section */
1118 FII_PMV_Util.bind_variable(
1119 p_sqlstmt=>sqlstmt,
1120 p_page_parameter_tbl=>p_page_parameter_tbl,
1121 p_sql_output=>paid_inv_sql,
1122 p_bind_output_table=>paid_inv_output,
1123 p_record_type_id=>l_record_type_id,
1124 p_check_id=>l_check_id,
1125 p_period_start=>l_period_start
1126 );
1127 END;
1128
1129 PROCEDURE Get_Payment_Detail
1130 ( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
1131 pay_detail_sql out NOCOPY VARCHAR2,
1132 pay_detail_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)IS
1133 /* declaration section */
1134 sqlstmt VARCHAR2(14000);
1135
1136 l_as_of_date DATE;
1137 l_operating_unit VARCHAR2(240);
1138 l_supplier VARCHAR2(240);
1139 l_invoice_number VARCHAR2(240);
1140 l_period_type VARCHAR2(240);
1141 l_record_type_id NUMBER;
1142 l_view_by VARCHAR2(240);
1143 l_currency VARCHAR2(240);
1144 l_column_name VARCHAR2(240);
1145 l_table_name VARCHAR2(240);
1146 l_gid NUMBER;
1150 l_period_start DATE;
1147 l_org_where VARCHAR2(240);
1148 l_supplier_where VARCHAR2(240);
1149
1151 l_days_into_period NUMBER;
1152 l_cur_period NUMBER;
1153 l_id_column VARCHAR2(240);
1154
1155 l_url_1 VARCHAR2(1000);
1156 l_url_2 VARCHAR2(1000);
1157 l_url_3 VARCHAR2(1000);
1158 l_url_4 VARCHAR2(1000);
1159
1160 l_date_mask VARCHAR2(240);
1161 l_curr VARCHAR2(240);
1162 BEGIN
1163 FII_PMV_Util.Get_Parameters(
1164 p_page_parameter_tbl,
1165 l_as_of_date,
1166 l_operating_unit,
1167 l_supplier,
1168 l_invoice_number,
1169 l_period_type,
1170 l_record_type_id,
1171 l_view_by,
1172 l_currency,
1173 l_column_name,
1174 l_table_name,
1175 l_gid,
1176 l_org_where,
1177 l_supplier_where
1178 );
1179
1180
1181 FII_PMV_Util.Get_Period_Strt(
1182 p_page_parameter_tbl,
1183 l_period_start,
1184 l_days_into_period,
1185 l_cur_period,
1186 l_id_column);
1187
1188 /**************Description of Measures, Attributes returned to PMV *****
1189 FII_MEASURE1 - Payment Number,
1190 FII_MEASURE2 - Check ID,
1191 FII_MEASURE3 - Payment Method,
1192 FII_MEASURE4 - Payment Amount,
1193 FII_MEASURE5 - Payment Date,
1194 FII_MEASURE6 - Status,
1195 FII_MEASURE7 - Bank Account Name,
1196 FII_MEASURE8 - Bank Account Number,
1197 FII_MEASURE9 - Remit to Bank,
1198 FII_MEASURE10 - Remit to Number,
1199 FII_MEASURE11 - Transaction Payment Amount,
1200 FII_MEASURE12 - Transaction Currency Code,
1201 FII_MEASURE13 - Total Paid Invoices,
1202 FII_MEASURE14 - Invoices Paid Late,
1203 FII_MEASURE15 - Invoices Paid on Time
1204 FII_MEASURE20 - Grand Total for Payment Amount
1205 FII_MEASURE21 - Grand Total for Total Paid Invoices
1206 FII_ATTRIBUTE2 - Grand Total for Invoices Paid Late
1207 FII_ATTRIBUTE3 - Grand Total for Invoices Paid on Time
1208 ***********************************************************************/
1209
1210
1211 l_url_1 := 'pFunctionName=FII_AP_PAID_INV_DETAIL_PYMT&pParamIds=Y&FII_CHECK_ID=FII_MEASURE2&FII_REPORT_SOURCE=FII_AP_PAID_INV_DETAIL_PYMT&FII_CHECK=FII_MEASURE1'
1212 ;
1213 l_url_2 := 'pFunctionName=FII_AP_PAID_INV_DETAIL_PYLATE&pParamIds=Y&FII_CHECK_ID=FII_MEASURE2&FII_REPORT_SOURCE=FII_AP_PAID_INV_DETAIL_PYLATE&FII_CHECK=FII_MEASURE1'
1214 ;
1215 l_url_3 := 'pFunctionName=FII_AP_PAID_INV_DETAIL_PYTIME&pParamIds=Y&FII_CHECK_ID=FII_MEASURE2&FII_REPORT_SOURCE=FII_AP_PAID_INV_DETAIL_PYTIME&FII_CHECK=FII_MEASURE1'
1216 ;
1217 l_url_4 := 'pFunctionName=FII_AP_PAY_ACTIVITY_HISTORY&pParamIds=Y&FII_CHECK_ID=FII_MEASURE2&FII_CHECK=FII_MEASURE1'
1218 ;
1219
1220 /*get date mask */
1221 FII_PMV_Util.get_format_mask(l_date_mask);
1222
1223 l_supplier_where := replace(l_supplier_where, 'supplier', 'vendor');
1224
1225 --Added for fix of bug 4327606
1226 l_column_name := replace(l_column_name, 'SUPPLIER', 'VENDOR');
1227
1228 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
1229 If p_page_parameter_tbl(i).parameter_name = 'CURRENCY+FII_CURRENCIES' then
1230 l_curr := p_page_parameter_tbl(i).parameter_value;
1231 --l_curr := substr(l_curr, 1, 3);
1232 End if;
1233 End Loop;
1234
1235
1236 /* Main SQL section */
1237 sqlstmt := 'select f.check_number FII_MEASURE1,
1238 f.check_id FII_MEASURE2,
1239 code.payment_method_name FII_MEASURE3,
1240 decode('''||l_currency||''', ''_prim_g'', nvl(f.base_amount, f.amount)*fii_currency.get_global_rate_primary(asp.base_currency_code,f.check_date),
1241 ''_sec_g'', nvl(f.base_amount, f.amount)*fii_currency.get_global_rate_secondary(asp.base_currency_code,f.check_date),
1242 ''_b'', nvl(f.base_amount, f.amount)*fii_currency.get_rate(asp.base_currency_code, '''||l_curr||''', f.check_date, bis_common_parameters.get_rate_type)
1243 )
1244 FII_MEASURE4,
1245 f.check_date FII_MEASURE5,
1246 code1.displayed_field FII_MEASURE6,
1247 f.bank_account_name FII_MEASURE7,
1248 f.bank_account_num FII_MEASURE8,
1249 bankacct.bank_name FII_MEASURE9,
1250 bankacct.bank_number FII_MEASURE10,
1251 f.currency_code FII_MEASURE12,
1252 f.amount FII_MEASURE11,
1253 count(distinct pay.invoice_id) FII_MEASURE13,
1254 count(distinct case when pay.no_days_late <> 0 then i.invoice_id else null end) FII_MEASURE14,
1255 count(distinct case when pay.no_days_late = 0 then i.invoice_id else null end) FII_MEASURE15,
1256 sum(decode('''||l_currency||''', ''_prim_g'', nvl(f.base_amount, f.amount)*fii_currency.get_global_rate_primary(asp.base_currency_code,f.check_date),
1257 ''_sec_g'', nvl(f.base_amount, f.amount)*fii_currency.get_global_rate_secondary(asp.base_currency_code,f.check_date),
1258 ''_b'', nvl(f.base_amount, f.amount)*fii_currency.get_rate(asp.base_currency_code,'''||l_curr||''' , f.check_date, bis_common_parameters.get_rate_type)
1259 )) over() FII_MEASURE20,
1260 sum(count(distinct pay.invoice_id)) over() FII_MEASURE21,
1261 sum(count(distinct case when pay.no_days_late <> 0 then i.invoice_id else null end)) over() FII_ATTRIBUTE2,
1262 sum(count(distinct case when pay.no_days_late = 0 then i.invoice_id else null end)) over() FII_ATTRIBUTE3,
1263 '''||l_url_1||''' FII_ATTRIBUTE10,
1264 '''||l_url_1||''' FII_ATTRIBUTE11,
1265 '''||l_url_2||''' FII_ATTRIBUTE12,
1266 '''||l_url_3||''' FII_ATTRIBUTE13,
1267 '''||l_url_4||''' FII_ATTRIBUTE14
1268 from ap_checks_all f, IBY_PAYMENT_METHODS_VL code, ap_lookup_codes code1,
1269 iby_payee_assigned_bankacct_v bankacct, ap_invoices_all i,
1270 ap_system_parameters_all asp,
1271 fii_ap_pay_sched_b pay
1272 where trunc(pay.action_date) <= &BIS_CURRENT_ASOF_DATE
1273 and trunc(pay.action_date) >= :PERIOD_START
1274 and code1.lookup_type = ''CHECK STATE''
1275 and f.payment_method_code = code.payment_method_code
1276 and f.status_lookup_code = code1.lookup_code
1277 and f.external_bank_account_id = bankacct.ext_bank_account_id(+)
1278 and f.check_id = pay.check_id
1279 and f.void_date is null
1280 and pay.invoice_id = i.invoice_id
1281 and pay.action = ''PAYMENT''
1282 and i.org_id = asp.org_id
1283 and i.invoice_type_lookup_code <> ''EXPENSE REPORT''
1284 '
1285 ||l_org_where||l_supplier_where||'
1286 group by f.check_number, f.check_id, code.payment_method_name, f.amount,
1287 f.check_date, code1.displayed_field, f.bank_account_name,
1288 f.bank_account_num, bankacct.bank_name, bankacct.bank_number,
1289 f.currency_code, asp.base_currency_code, f.base_amount
1290 &ORDER_BY_CLAUSE ';
1291
1292 /* Binding Section */
1293 FII_PMV_Util.bind_variable(
1294 p_sqlstmt=>sqlstmt,
1295 p_page_parameter_tbl=>p_page_parameter_tbl,
1296 p_sql_output=>pay_detail_sql,
1297 p_bind_output_table=>pay_detail_output,
1298 p_period_start=>l_period_start
1299 );
1300 END;
1301 END FII_AP_PAID_INV_DETAIL;