[Home] [Help]
PACKAGE BODY: APPS.FII_AP_INV_DETAIL
Source
1 PACKAGE BODY FII_AP_INV_DETAIL AS
2 /* $Header: FIIAPD1B.pls 120.3 2005/10/12 20:09:36 vkazhipu noship $ */
3
4 -- To show the as-of-date in the report title --
5 FUNCTION get_report_title(
6 p_page_parameter_tbl BIS_PMV_PAGE_PARAMETER_TBL)
7 RETURN VARCHAR2
8 IS
9 l_as_of_date VARCHAR2(240);
10 BEGIN
11 IF (p_page_parameter_tbl.count > 0) THEN
12 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
13 IF p_page_parameter_tbl(i).parameter_name = 'AS_OF_DATE' THEN
14 l_as_of_date := p_page_parameter_tbl(i).parameter_value;
15 END IF;
16 END LOOP;
17 END IF;
18 RETURN l_as_of_date;
19 END get_report_title;
20
21
22
23 -- To get last_update_date for Past Due Invoices --
24
25 FUNCTION get_past_due_inv_up_date
26 RETURN VARCHAR2
27 IS
28 l_last_update_date VARCHAR2(300);
29 l_date_mask VARCHAR2(240);
30
31 BEGIN
32
33 FII_PMV_Util.get_format_mask(l_date_mask);
34
35 SELECT ' ('||fnd_message.get_string('FII','FII_AP_ASOF')||' '||to_char(TRUNC(last_refresh_date),l_date_mask)||')'
36 INTO l_last_update_date FROM bis_obj_properties
37 WHERE object_name = 'FII_AP_CURR_TOP_PDUE' AND object_type = 'REPORT';
38
39 RETURN l_last_update_date;
40
41 END get_past_due_inv_up_date;
42
43
44 --Procedure added as part of report Current Past Due Invoices
45
46 PROCEDURE get_current_top_pdue (
47 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
48 inv_dtl_sql OUT NOCOPY VARCHAR2,
49 inv_dtl_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
50 IS
51 l_viewby_dim VARCHAR2(240); -- the viewby selected
52 l_as_of_date DATE;
53 l_organization VARCHAR2(240);
54 l_supplier VARCHAR2(240);
55 l_currency VARCHAR2(240);
56 l_viewby_id VARCHAR2(240); -- org_id or supplier_id
57 l_viewby_string VARCHAR2(240);
58 l_record_type_id NUMBER; -- only possible value is 1143
59 l_curr_suffix VARCHAR2(240);
60 l_org_where VARCHAR2(240);
61 l_sup_where VARCHAR2(240);
62 l_gid NUMBER;
63 sqlstmt VARCHAR2(14000);
64 l_period_type VARCHAR2(240);
65 l_invoice_number VARCHAR2(240);
66 l_invoice_amt_col VARCHAR2(240);
67 l_unpaid_amt_col VARCHAR2(240);
68 l_report_source VARCHAR2(240);
69 l_discount_lost_col VARCHAR2(240);
70 l_past_due_amt_col VARCHAR2(240);
71 l_check_id NUMBER := 0;
72 l_url_inv_activity VARCHAR2(1000);
73 l_url_pay_discount VARCHAR2(1000);
74 l_url_hold_history VARCHAR2(1000);
75 l_url_inv_detail VARCHAR2(1000);
76 l_yes VARCHAR2(240);
77 l_no VARCHAR2(240);
78 l_date_mask VARCHAR2(240);
79 l_sysdate VARCHAR2(30);
80
81 BEGIN
82
83 -- Read the parameters passed
84
85 FII_PMV_UTIL.get_parameters(
86 p_page_parameter_tbl=>p_page_parameter_tbl,
87 p_as_of_date=>l_as_of_date,
88 p_operating_unit=>l_organization,
89 p_supplier=>l_supplier,
90 p_invoice_number=>l_invoice_number,
91 p_period_type=>l_period_type,
92 p_record_type_id=>l_record_type_id,
93 p_view_by=>l_viewby_dim,
94 p_currency=>l_curr_suffix,
95 p_column_name=>l_viewby_id,
96 p_table_name=>l_viewby_string,
97 p_gid=>l_gid,
98 p_org_where=>l_org_where,
99 p_supplier_where=>l_sup_where);
100
101
102 IF l_curr_suffix IS NOT NULL THEN
103 IF l_curr_suffix = '_prim_g' THEN
104 l_invoice_amt_col := 'invoice_amt_prim_g';
105 l_unpaid_amt_col := 'unpaid_amt_prim_g';
106 l_past_due_amt_col := 'past_due_amt_prim_g';
107 l_discount_lost_col := 'discount_lost_prim_g';
108 ELSIF l_curr_suffix = '_sec_g' THEN
109 l_invoice_amt_col := 'invoice_amt_sec_g';
110 l_unpaid_amt_col := 'unpaid_amt_sec_g';
111 l_past_due_amt_col := 'past_due_amt_sec_g';
112 l_discount_lost_col := 'discount_lost_sec_g';
113 ELSE
114 l_invoice_amt_col := 'invoice_amt_b';
115 l_unpaid_amt_col := 'unpaid_amt_b';
116 l_past_due_amt_col := 'past_due_amt_b';
117 l_discount_lost_col := 'discount_lost_b';
118 END IF;
119 END IF;
120
121 --Added for Bug 4309974
122 SELECT TO_CHAR(TRUNC(sysdate),'DD/MM/YYYY') INTO l_sysdate FROM dual;
123
124
125 /* url defined for the drills */
126 l_url_inv_activity := 'AS_OF_DATE='||l_sysdate||'&pFunctionName=FII_AP_INV_ACTIVITY_HISTORY&pParamIds=Y&FII_INVOICE_ID=FII_INVOICE_ID&FII_INVOICE=FII_INVOICE_NUM';
127 l_url_pay_discount := 'AS_OF_DATE='||l_sysdate||'&pFunctionName=FII_AP_SCHED_PAY_DISCOUNT&pParamIds=Y&FII_AP_INVOICE_ID=FII_INVOICE_ID&FII_INVOICE=FII_INVOICE_NUM';
128 l_url_hold_history := 'AS_OF_DATE='||l_sysdate||'&pFunctionName=FII_AP_HOLD_HISTORY&pParamIds=Y&FII_AP_INVOICE_ID=FII_INVOICE_ID&FII_INVOICE=FII_INVOICE_NUM';
129 -- l_url_inv_detail:= 'AS_OF_DATE='||l_sysdate||'&pFunctionName=FII_AP_INV_DIST_DETAIL&pParamIds=Y&FII_INVOICE_ID=FII_INVOICE_ID&FII_INVOICE=FII_INVOICE_NUM';
130 l_url_inv_detail:= 'AS_OF_DATE='||l_sysdate||'&pFunctionName=FII_AP_INV_LINES_DETAIL&pParamIds=Y&FII_AP_INVOICE_ID=FII_INVOICE_ID&FII_INVOICE_NUM=FII_INVOICE_NUM';
131
132 FII_PMV_Util.get_yes_no_msg(l_yes, l_no);
133
134 /*
135 Added the following code to replace f.org_id with org.organization_id, due to
136 performance consciderations to make the table table hr_all_organization_units as the
137 driving table in place of fii_ap_inv_b_mv in the case when only l_supplier = ALL.
138
139 The replace is not done in the case when l_supplier = 1 supplier, because it's found
140 during 1 supplier the performance degrades if we make table hr_all_organization_units
141 as the driving table.
142 */
143
144 IF l_supplier = 'All' THEN
145 l_org_where := REPLACE(l_org_where,'f.org_id','org.organization_id');
146 END IF;
147
148 /*------------------------------------------------------+
149 | |
150 | FII_INVOICE_NUM - Invoice Number |
151 | FII_INVOICE_ID - Invoice ID |
152 | FII_INVOICE_TYPE- Invoice Type |
153 | FII_SUPPLIER - Supplier |
154 | FII_OPER_UNIT - Operating Unit |
155 | FII_INVOICE_DATE- Invoice Date |
156 | FII_ENT_DATE - Entered Date |
157 | FII_DUE_DATE - Due Date |
158 | FII_DAYS_PDUE - Days Past Due |
159 | FII_TRX_CURRENCY- Tran. Currency |
160 | FII_TRX_INVOICE_AMT- Tran.Invoice Amt |
161 | FII_INVOICE_AMT - Invoice Amount |
162 | FII_UNPAID_AMT - Unpaid Amount |
163 | FII_AMT_PDUE - Amount Past Due |
164 | FII_ON_HOLD - On Hold |
165 | FII_DAYS_ON_HOLD- Days on Hold |
166 | FII_DISC_LOST - Discount Lost |
167 | FII_TERMS - Terms |
168 | FII_GT_INVOICE_AMT- Grand Total Inv. Amount |
169 | FII_GT_UNPAID_AMT - Grand Total Unpaid Amount|
170 | FII_GT_AMT_PDUE - Grand Total Amt. Past Due|
171 | FII_GT_DISC_LOST- Grand Total Discount Lost|
172 | FII_INV_ACT_HIST_URL- URL on Invoice Number |
173 | FII_SCHD_PAY_URL- URL on Due Date |
174 | FII_HOLD_HIST_URL- URL on On Hold |
175 | FII_INV_DIST_URL- URL on Tran. Inv. Amount|
176 +-------------------------------------------------------*/
177
178 sqlstmt := '
179 SELECT
180 h.FII_INVOICE_NUM FII_INVOICE_NUM,
181 h.FII_INVOICE_ID FII_INVOICE_ID,
182 h.FII_INVOICE_TYPE FII_INVOICE_TYPE,
183 SUPP.VALUE FII_SUPPLIER,
184 h.FII_OPER_UNIT FII_OPER_UNIT,
185 h.FII_INVOICE_DATE FII_INVOICE_DATE,
186 h.FII_ENT_DATE FII_ENT_DATE,
187 h.FII_DUE_DATE FII_DUE_DATE,
188 h.FII_DAYS_PDUE FII_DAYS_PDUE,
189 h.FII_TRX_CURRENCY FII_TRX_CURRENCY,
190 h.FII_TRX_INVOICE_AMT FII_TRX_INVOICE_AMT,
191 h.FII_INVOICE_AMT FII_INVOICE_AMT,
192 h.FII_UNPAID_AMT FII_UNPAID_AMT,
193 h.FII_AMT_PDUE FII_AMT_PDUE,
194 h.FII_ON_HOLD FII_ON_HOLD,
195 h.FII_DAYS_ON_HOLD FII_DAYS_ON_HOLD,
196 h.FII_DISC_LOST FII_DISC_LOST,
197 TERMS.NAME FII_TERMS,
198 h.FII_GT_INVOICE_AMT FII_GT_INVOICE_AMT,
199 h.FII_GT_UNPAID_AMT FII_GT_UNPAID_AMT,
200 h.FII_GT_AMT_PDUE FII_GT_AMT_PDUE,
201 h.FII_GT_DISC_LOST FII_GT_DISC_LOST,
202 '''||l_url_inv_activity||''' FII_INV_ACT_HIST_URL,
203 '''||l_url_pay_discount||''' FII_SCHD_PAY_URL,
204 '''||l_url_hold_history||''' FII_HOLD_HIST_URL,
205 '''||l_url_inv_detail||''' FII_INV_DIST_URL
206 FROM
207 (
208 SELECT
209 g.TERMS_ID TERMS_ID,
210 g.FII_INVOICE_NUM FII_INVOICE_NUM,
211 g.FII_INVOICE_ID FII_INVOICE_ID,
212 g.FII_INVOICE_TYPE FII_INVOICE_TYPE,
213 g.SUPPLIER_ID SUPPLIER_ID,
214 g.FII_OPER_UNIT FII_OPER_UNIT,
215 g.FII_INVOICE_DATE FII_INVOICE_DATE,
216 g.FII_ENT_DATE FII_ENT_DATE,
217 g.FII_DUE_DATE FII_DUE_DATE,
218 g.FII_DAYS_PDUE FII_DAYS_PDUE,
219 g.FII_TRX_CURRENCY FII_TRX_CURRENCY,
220 g.FII_TRX_INVOICE_AMT FII_TRX_INVOICE_AMT,
221 g.FII_INVOICE_AMT FII_INVOICE_AMT,
222 g.FII_UNPAID_AMT FII_UNPAID_AMT,
223 g.FII_AMT_PDUE FII_AMT_PDUE,
224 g.FII_ON_HOLD FII_ON_HOLD,
225 g.FII_DAYS_ON_HOLD FII_DAYS_ON_HOLD,
226 g.FII_DISC_LOST FII_DISC_LOST,
227 g.FII_GT_INVOICE_AMT FII_GT_INVOICE_AMT,
228 g.FII_GT_UNPAID_AMT FII_GT_UNPAID_AMT,
229 g.FII_GT_AMT_PDUE FII_GT_AMT_PDUE,
230 g.FII_GT_DISC_LOST FII_GT_DISC_LOST,
231 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_INVOICE_ID)) -1 rnk
232 FROM
233 (SELECT f.terms_id TERMS_ID,
234 f.invoice_number FII_INVOICE_NUM,
235 f.invoice_id FII_INVOICE_ID,
236 f.invoice_type FII_INVOICE_TYPE,
237 f.supplier_id SUPPLIER_ID,
238 org.name FII_OPER_UNIT,
239 f.invoice_date FII_INVOICE_DATE,
240 f.entered_date FII_ENT_DATE,
241 f.due_date FII_DUE_DATE,
242 f.days_past_due FII_DAYS_PDUE,
243 f.trx_currency_code FII_TRX_CURRENCY,
244 f.invoice_amt_t FII_TRX_INVOICE_AMT,
245 f.'||l_invoice_amt_col||' FII_INVOICE_AMT,
246 f.'||l_unpaid_amt_col||' FII_UNPAID_AMT,
247 f.'||l_past_due_amt_col||' FII_AMT_PDUE,
248 decode(nvl(f.on_hold, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_ON_HOLD,
249 nvl(f.days_on_hold,0) FII_DAYS_ON_HOLD,
250 f.'||l_discount_lost_col||' FII_DISC_LOST,
251 SUM(f.'||l_invoice_amt_col||') OVER() FII_GT_INVOICE_AMT,
252 SUM(f.'||l_unpaid_amt_col||') OVER() FII_GT_UNPAID_AMT,
253 SUM(f.'||l_past_due_amt_col||') OVER() FII_GT_AMT_PDUE,
254 SUM(f.'||l_discount_lost_col||') OVER() FII_GT_DISC_LOST
255 FROM
256 FII_AP_INV_B_MV f, hr_all_organization_units org
257 WHERE f.org_id=org.organization_id
258 '||l_org_where||' '||l_sup_where||'
259 ) g
260 ) h, ap_terms_tl terms, poa_suppliers_v supp
261 WHERE h.terms_id = terms.term_id
262 AND terms.language = userenv(''LANG'')
263 AND h.supplier_id = supp.id
264 AND (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
265 &ORDER_BY_CLAUSE
266 ';
267
268 -- Attach bind parameters
269 FII_PMV_UTIL.bind_variable(
270 p_sqlstmt=>sqlstmt,
271 p_page_parameter_tbl=>p_page_parameter_tbl,
272 p_sql_output=>inv_dtl_sql,
273 p_bind_output_table=>inv_dtl_output);
274
275 END get_current_top_pdue;
276
277
278 -- For the Invoice Detail report --
279 PROCEDURE get_inv_detail (
280 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
281 inv_dtl_sql OUT NOCOPY VARCHAR2,
282 inv_dtl_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
283 IS
284 l_viewby_dim VARCHAR2(240); -- the viewby selected
285 l_as_of_date DATE;
286 l_organization VARCHAR2(240);
287 l_supplier VARCHAR2(240);
288 l_currency VARCHAR2(240);
289 l_viewby_id VARCHAR2(240); -- org_id or supplier_id
290 l_viewby_string VARCHAR2(240);
291 l_record_type_id NUMBER; -- only possible value is 1143
292 l_curr_suffix VARCHAR2(240);
293 l_org_where VARCHAR2(240);
294 l_sup_where VARCHAR2(240);
295 l_gid NUMBER;
296 sqlstmt VARCHAR2(14000);
297 l_period_type VARCHAR2(240);
298 l_invoice_number VARCHAR2(240);
299 l_invoice_amt_col VARCHAR2(240);
300 l_unpaid_amt_col VARCHAR2(240);
301 l_report_source VARCHAR2(240);
302 l_discount_offered VARCHAR2(240);
303 l_discount_lost VARCHAR2(240);
304 l_discount_taken VARCHAR2(240);
305 l_discount_available VARCHAR2(240);
306 l_check_id NUMBER := 0;
307 l_url_1 VARCHAR2(1000);
308 l_url_2 VARCHAR2(1000);
309 l_url_3 VARCHAR2(1000);
310 l_url_4 VARCHAR2(1000);
311 l_yes VARCHAR2(240);
312 l_no VARCHAR2(240);
313 l_date_mask VARCHAR2(240);
314 l_sysdate VARCHAR2(30);
315 BEGIN
316
317 -- Read the parameters passed
318 FII_PMV_UTIL.get_parameters(
319 p_page_parameter_tbl=>p_page_parameter_tbl,
320 p_as_of_date=>l_as_of_date,
321 p_operating_unit=>l_organization,
322 p_supplier=>l_supplier,
323 p_invoice_number=>l_invoice_number,
324 p_period_type=>l_period_type,
325 p_record_type_id=>l_record_type_id,
326 p_view_by=>l_viewby_dim,
327 p_currency=>l_curr_suffix,
328 p_column_name=>l_viewby_id,
329 p_table_name=>l_viewby_string,
330 p_gid=>l_gid,
331 p_org_where=>l_org_where,
332 p_supplier_where=>l_sup_where);
333
334
335 IF (p_page_parameter_tbl.count > 0) THEN
336 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
337 IF p_page_parameter_tbl(i).parameter_name = 'CURRENCY+FII_CURRENCIES'
338 THEN
339 l_currency := p_page_parameter_tbl(i).parameter_id;
340 END IF;
341 END LOOP;
342 END IF;
343
344
345 FII_PMV_UTIL.get_report_source (
346 p_page_parameter_tbl=>p_page_parameter_tbl,
347 p_report_source=>l_report_source);
348
349 l_record_type_id := 1143; -- no other value possible in this report
350 l_discount_offered := FII_PMV_UTIL.get_base_curr_colname(l_curr_suffix, 'discount_offered');
351 l_discount_lost := FII_PMV_UTIL.get_base_curr_colname(l_curr_suffix, 'discount_lost');
352 l_discount_taken := FII_PMV_UTIL.get_base_curr_colname(l_curr_suffix, 'discount_taken');
353 l_discount_available := FII_PMV_UTIL.get_base_curr_colname(l_curr_suffix, 'discount_available');
354
355 IF l_curr_suffix IS NOT NULL THEN
356 IF l_curr_suffix = '_prim_g' THEN
357 l_invoice_amt_col := 'prim_amount';
358 l_unpaid_amt_col := 'prim_amount_remaining';
359 ELSIF l_curr_suffix = '_sec_g' THEN
360 l_invoice_amt_col := 'sec_amount';
361 l_unpaid_amt_col := 'sec_amount_remaining';
362 ELSE
363 l_invoice_amt_col := 'base_amount';
364 l_unpaid_amt_col := 'amount_remaining_b';
365 END IF;
366 END IF;
367
368 FII_PMV_UTIL.get_check_id(p_page_parameter_tbl, l_check_id);
369
370 --Added for Bug 4309974
371 SELECT TO_CHAR(TRUNC(sysdate),'DD/MM/YYYY') INTO l_sysdate FROM dual;
372
373 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';
374 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';
375 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';
376 -- 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';
377 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';
378 FII_PMV_Util.get_yes_no_msg(l_yes, l_no);
379 FII_PMV_Util.get_format_mask(l_date_mask);
380
381 /*--------------------------------------------------------------+
382 | FII_MEASURE1 - Invoice Number |
383 | FII_MEASURE2 - Invoice ID |
384 | FII_MEASURE3 - Invoice Type |
385 | FII_MEASURE4 - Invoice Date |
386 | FII_MEASURE5 - Entered Date |
390 | FII_MEASURE9 - Invoice Amount |
387 | FII_MEASURE6 - Due Date |
388 | FII_MEASURE7 - Transaction Currency Code |
389 | FII_MEASURE8 - Transaction invoice Amount |
391 | FII_MEASURE10 - Unpaid Amount |
392 | FII_MEASURE11 - On Hold |
393 | FII_MEASURE12 - Days on Hold |
394 | FII_MEASURE13 - Discount Offered |
395 | FII_MEASURE14 - Discount Taken |
396 | FII_MEASURE15 - Discount Lost |
397 | FII_MEASURE16 - Discount Remaining |
398 | FII_MEASURE17 - Terms |
399 | FII_MEASURE18 - Source |
400 | FII_MEASURE[21-27] - Grand Total columns |
401 | FII_ATTRIBUTE[10-13] - Drill columns |
402 +--------------------------------------------------------------*/
403
404
405
406 /*Changes made for version 115.35 include
407 1. Removed the WITHHOLDING logic from all reports. Earlier Due date was not populated
408 for the invoice.Now the withholding action carries a due date as part of
409 enhancement 3065476 .
410 2. Changed the logic for due detail reports .Removed the +1 day logic implemented
411 earlier.
412 3. Made changes as part of impact of enhancement 3065413.
413 */
414
415 /* Changes made during Performance tuning + enhancement for Days on Hold
416
417 1. Removed period_type_id = 1 from all the reports
418 2. Implemented the Start and End index logic to support Windowed mode for all reports
419 3. Using fii_ap_inv_holds_b to return on hold on the As of date
420 4. Using fii_ap_hhist_ib_mv to return days on hold .
421 5. Changed the driving tables to maximise the use of index (driving table now is fii_ap_invoice_b)
422 6. Cleaned up the commented part of code not being used (as fix for some bug) eg.
423 Removed the commented line
424 sum(base.base_amount) over() FII_MEASURE21 ,
425 7. Made use of the same Measure name as being passed to AK in the inner query
426 8. Moved the urls to top level .
427 9. In AK for FII_AP_INV_DETAIL,changed the no.of display rows to -30 and
428 Number of rows per portlet to -10
429
430 */
431
432
433 -- Construct the query to be sent
434
435 CASE l_report_source
436
437 WHEN 'FII_AP_UNPAID_INV_DETAIL' THEN -- 1st, Unpaid Invoices Detail
438 sqlstmt := '
439 SELECT
440 h.FII_MEASURE1 FII_MEASURE1,
441 h.FII_MEASURE2 FII_MEASURE2,
442 h.FII_MEASURE3 FII_MEASURE3,
443 h.FII_MEASURE4 FII_MEASURE4,
444 h.FII_MEASURE5 FII_MEASURE5,
445 h.FII_MEASURE6 FII_MEASURE6,
446 h.FII_MEASURE7 FII_MEASURE7,
447 h.FII_MEASURE8 FII_MEASURE8,
448 h.FII_MEASURE9 FII_MEASURE9,
449 h.FII_MEASURE10 FII_MEASURE10,
450 h.FII_MEASURE11 FII_MEASURE11,
451 h.FII_MEASURE12 FII_MEASURE12,
452 h.FII_MEASURE13 FII_MEASURE13,
453 h.FII_MEASURE14 FII_MEASURE14,
454 h.FII_MEASURE15 FII_MEASURE15,
455 h.FII_MEASURE16 FII_MEASURE16,
456 h.FII_MEASURE17 FII_MEASURE17,
457 h.FII_MEASURE18 FII_MEASURE18,
458 h.FII_MEASURE21 FII_MEASURE21,
459 h.FII_MEASURE22 FII_MEASURE22,
460 h.FII_MEASURE23 FII_MEASURE23,
461 h.FII_MEASURE24 FII_MEASURE24,
462 h.FII_MEASURE25 FII_MEASURE25,
463 h.FII_MEASURE26 FII_MEASURE26,
464 h.FII_MEASURE27 FII_MEASURE27,
465 '''||l_url_1||''' FII_ATTRIBUTE10,
466 '''||l_url_2||''' FII_ATTRIBUTE11,
467 '''||l_url_3||''' FII_ATTRIBUTE12,
468 '''||l_url_4||''' FII_ATTRIBUTE13
469
470 FROM
471 (
472 SELECT
473 g.FII_MEASURE1 FII_MEASURE1,
474 g.FII_MEASURE2 FII_MEASURE2,
475 g.FII_MEASURE3 FII_MEASURE3,
476 g.FII_MEASURE4 FII_MEASURE4,
477 g.FII_MEASURE5 FII_MEASURE5,
478 g.FII_MEASURE6 FII_MEASURE6,
479 g.FII_MEASURE7 FII_MEASURE7,
480 g.FII_MEASURE8 FII_MEASURE8,
481 g.FII_MEASURE9 FII_MEASURE9,
482 g.FII_MEASURE10 FII_MEASURE10,
483 g.FII_MEASURE11 FII_MEASURE11,
484 g.FII_MEASURE12 FII_MEASURE12,
485 g.FII_MEASURE13 FII_MEASURE13,
486 g.FII_MEASURE14 FII_MEASURE14,
487 g.FII_MEASURE15 FII_MEASURE15,
488 g.FII_MEASURE16 FII_MEASURE16,
489 g.FII_MEASURE17 FII_MEASURE17,
490 g.FII_MEASURE18 FII_MEASURE18,
491 g.FII_MEASURE21 FII_MEASURE21,
492 g.FII_MEASURE22 FII_MEASURE22,
493 g.FII_MEASURE23 FII_MEASURE23,
494 g.FII_MEASURE24 FII_MEASURE24,
495 g.FII_MEASURE25 FII_MEASURE25,
496 g.FII_MEASURE26 FII_MEASURE26,
497 g.FII_MEASURE27 FII_MEASURE27,
498 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
499 FROM
500
501 (SELECT f.invoice_number FII_MEASURE1,
502 f.invoice_id FII_MEASURE2,
503 f.invoice_type FII_MEASURE3,
504 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
505 f.entered_date FII_MEASURE5, -- Bug #4266826
506 MIN(fpay.due_date) FII_MEASURE6, -- Bug #4266826
507 f.invoice_currency_code FII_MEASURE7,
508 f.invoice_amount FII_MEASURE8,
509 f.'||l_invoice_amt_col||' FII_MEASURE9,
510 sum(fpay.'||l_unpaid_amt_col||') FII_MEASURE10,
511 decode(nvl(hold.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
512 nvl(hold1.FII_MEASURE12,0) FII_MEASURE12,
513 f.'||l_discount_offered||' FII_MEASURE13,
514 SUM(fpay.'||l_discount_taken||') FII_MEASURE14,
515 SUM(fpay.'||l_discount_lost||') FII_MEASURE15,
516 SUM(fpay.'||l_discount_available||') FII_MEASURE16,
517 t.name FII_MEASURE17,
521 SUM(SUM(fpay.'||l_unpaid_amt_col||')) OVER() FII_MEASURE23,
518 f.source FII_MEASURE18,
519 to_number(null) FII_MEASURE21,
520 SUM(f.'||l_invoice_amt_col||') OVER() FII_MEASURE22,
522 SUM(f.'||l_discount_offered||') OVER() FII_MEASURE24,
523 SUM(SUM(fpay.'||l_discount_taken||')) OVER() FII_MEASURE25,
524 SUM(SUM(fpay.'||l_discount_lost||')) OVER() FII_MEASURE26,
525 SUM(SUM(fpay.'||l_discount_available||')) OVER() FII_MEASURE27
526 FROM fii_ap_invoice_b f,
527 fii_ap_pay_sched_b fpay,
528 ap_terms_tl t,
529 (
530 SELECT f.invoice_id,
531 ''Y'' FII_MEASURE11
532 FROM fii_ap_inv_holds_b f
533 WHERE ( f.release_date > &BIS_CURRENT_ASOF_DATE OR f.release_date is null)
534 AND f.hold_date <= &BIS_CURRENT_ASOF_DATE
535 '||l_org_where||' '||l_sup_where|| '
536 GROUP BY invoice_id
537 ) hold,
538 (SELECT f.invoice_id,
539 SUM(days_on_hold) FII_MEASURE12
540 FROM fii_ap_hhist_ib_mv f
541 WHERE 1 = 1
542 '||l_org_where||' '||l_sup_where|| '
543 GROUP BY invoice_id
544 ) hold1
545 WHERE f.entered_Date<= &BIS_CURRENT_ASOF_DATE /*added for bug no.3054524*/
546 '||l_org_where||' '||l_sup_where||'
547 AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added for bug no.3114633*/
548 AND fpay.action <> ''PREPAYMENT''
549 AND t.term_id = f.terms_id
550 AND t.language = userenv(''LANG'')
551 AND f.cancel_flag = ''N''
552 AND ( f.fully_paid_date > &BIS_CURRENT_ASOF_DATE or f.fully_paid_date is null)
553 AND f.invoice_id = fpay.invoice_id
554 AND f.invoice_id = hold.invoice_id(+)
555 AND f.invoice_id = hold1.invoice_id(+)
556 HAVING SUM(fpay.amount_remaining) <> 0 /* bug # 3191403*/
557 GROUP BY f.invoice_number,
558 f.invoice_id,
559 f.invoice_type,
560 f.invoice_date,
561 f.entered_date,
562 f.invoice_currency_code,
563 f.invoice_amount,
564 f.'||l_invoice_amt_col||',
565 hold.FII_MEASURE11,
566 hold1.FII_MEASURE12,
567 f.'||l_discount_offered||',
568 t.name,
569 f.source) g ) h
570 WHERE (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
571 &ORDER_BY_CLAUSE
572 ';
573
574
575
576 /*added logic base.entered_Date<&BIS_CURRENT_ASOF_DATE
577 for Bug no.3054524 in Unpaid Invoices,
578 Invoices Due Detail repts */
579 /* Added logic in Invoice due detail report to include
580 witholding amt in unpaid invoices for
581 bug no 3055143 */
582
583 WHEN 'FII_AP_UNPAID_INV_DUE' THEN -- 2nd, Invoices Due Detail
584 sqlstmt := '
585 SELECT
586 h.FII_MEASURE1 FII_MEASURE1,
587 h.FII_MEASURE2 FII_MEASURE2,
588 h.FII_MEASURE3 FII_MEASURE3,
589 h.FII_MEASURE4 FII_MEASURE4,
590 h.FII_MEASURE5 FII_MEASURE5,
591 h.FII_MEASURE6 FII_MEASURE6,
592 h.FII_MEASURE7 FII_MEASURE7,
593 h.FII_MEASURE8 FII_MEASURE8,
594 h.FII_MEASURE9 FII_MEASURE9,
595 h.FII_MEASURE10 FII_MEASURE10,
596 h.FII_MEASURE11 FII_MEASURE11,
597 h.FII_MEASURE12 FII_MEASURE12,
598 h.FII_MEASURE13 FII_MEASURE13,
599 h.FII_MEASURE14 FII_MEASURE14,
600 h.FII_MEASURE15 FII_MEASURE15,
601 h.FII_MEASURE16 FII_MEASURE16,
602 h.FII_MEASURE17 FII_MEASURE17,
603 h.FII_MEASURE18 FII_MEASURE18,
604 h.FII_MEASURE21 FII_MEASURE21,
605 h.FII_MEASURE22 FII_MEASURE22,
606 h.FII_MEASURE23 FII_MEASURE23,
607 h.FII_MEASURE24 FII_MEASURE24,
608 h.FII_MEASURE25 FII_MEASURE25,
609 h.FII_MEASURE26 FII_MEASURE26,
610 h.FII_MEASURE27 FII_MEASURE27,
611 '''||l_url_1||''' FII_ATTRIBUTE10,
612 '''||l_url_2||''' FII_ATTRIBUTE11,
613 '''||l_url_3||''' FII_ATTRIBUTE12,
614 '''||l_url_4||''' FII_ATTRIBUTE13
615
616 FROM
617 (
618 SELECT
619 g.FII_MEASURE1 FII_MEASURE1,
620 g.FII_MEASURE2 FII_MEASURE2,
621 g.FII_MEASURE3 FII_MEASURE3,
622 g.FII_MEASURE4 FII_MEASURE4,
623 g.FII_MEASURE5 FII_MEASURE5,
624 g.FII_MEASURE6 FII_MEASURE6,
625 g.FII_MEASURE7 FII_MEASURE7,
626 g.FII_MEASURE8 FII_MEASURE8,
627 g.FII_MEASURE9 FII_MEASURE9,
628 g.FII_MEASURE10 FII_MEASURE10,
629 g.FII_MEASURE11 FII_MEASURE11,
630 g.FII_MEASURE12 FII_MEASURE12,
631 g.FII_MEASURE13 FII_MEASURE13,
632 g.FII_MEASURE14 FII_MEASURE14,
633 g.FII_MEASURE15 FII_MEASURE15,
634 g.FII_MEASURE16 FII_MEASURE16,
635 g.FII_MEASURE17 FII_MEASURE17,
636 g.FII_MEASURE18 FII_MEASURE18,
637 g.FII_MEASURE21 FII_MEASURE21,
638 g.FII_MEASURE22 FII_MEASURE22,
639 g.FII_MEASURE23 FII_MEASURE23,
640 g.FII_MEASURE24 FII_MEASURE24,
641 g.FII_MEASURE25 FII_MEASURE25,
642 g.FII_MEASURE26 FII_MEASURE26,
643 g.FII_MEASURE27 FII_MEASURE27,
644 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
645 FROM
646
647 (SELECT f.invoice_number FII_MEASURE1,
648 f.invoice_id FII_MEASURE2,
649 f.invoice_type FII_MEASURE3,
650 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
651 f.entered_date FII_MEASURE5, -- Bug #4266826
655 f.'||l_invoice_amt_col||' FII_MEASURE9,
652 MIN(fpay.due_date) FII_MEASURE6, -- Bug #4266826
653 f.invoice_currency_code FII_MEASURE7,
654 f.invoice_amount FII_MEASURE8,
656 sum(fpay.'||l_unpaid_amt_col||') FII_MEASURE10,
657 decode(nvl(hold.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
658 nvl(hold1.FII_MEASURE12,0) FII_MEASURE12,
659 f.'||l_discount_offered||' FII_MEASURE13,
660 SUM(fpay.'||l_discount_taken||') FII_MEASURE14,
661 SUM(fpay.'||l_discount_lost||') FII_MEASURE15,
662 SUM(fpay.'||l_discount_available||') FII_MEASURE16,
663 t.name FII_MEASURE17,
664 f.source FII_MEASURE18,
665 to_number(null) FII_MEASURE21,
666 SUM(f.'||l_invoice_amt_col||') OVER() FII_MEASURE22,
667 SUM(sum(fpay.'||l_unpaid_amt_col||')) OVER() FII_MEASURE23,
668 SUM(f.'||l_discount_offered||') OVER() FII_MEASURE24,
669 SUM(SUM(fpay.'||l_discount_taken||')) OVER() FII_MEASURE25,
670 SUM(SUM(fpay.'||l_discount_lost||')) OVER() FII_MEASURE26,
671 SUM(SUM(fpay.'||l_discount_available||')) OVER() FII_MEASURE27
672 FROM fii_ap_invoice_b f,
673 fii_ap_pay_sched_b fpay,
674 ap_terms_tl t,
675 (
676 SELECT f.invoice_id,
677 ''Y'' FII_MEASURE11
678 FROM fii_ap_inv_holds_b f
679 WHERE ( f.release_date > &BIS_CURRENT_ASOF_DATE OR f.release_date is null)
680 AND f.hold_date <= &BIS_CURRENT_ASOF_DATE
681 '||l_org_where||' '||l_sup_where|| '
682 GROUP BY invoice_id
683 ) hold,
684 (SELECT f.invoice_id,
685 SUM(days_on_hold) FII_MEASURE12
686 FROM fii_ap_hhist_ib_mv f
687 WHERE 1 = 1
688 '||l_org_where||' '||l_sup_where|| '
689 GROUP BY invoice_id
690 ) hold1
691 WHERE fpay.due_date >= &BIS_CURRENT_ASOF_DATE
692 AND f.entered_Date<=&BIS_CURRENT_ASOF_DATE /*added for bug no.3054524*/
693 AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added for bug no.3114633*/
694 AND fpay.action <> ''PREPAYMENT''
695 '||l_org_where||' '||l_sup_where||'
696 AND t.term_id = f.terms_id
697 AND t.language = userenv(''LANG'')
698 AND (f.fully_paid_date > &BIS_CURRENT_ASOF_DATE OR f.fully_paid_date IS NULL)
699 AND f.invoice_id = fpay.invoice_id
700 AND f.cancel_flag = ''N''
701 AND f.invoice_id =hold.invoice_id (+)
702 AND f.invoice_id =hold1.invoice_id (+)
703 HAVING SUM(fpay.amount_remaining) <> 0 /* bug # 3191403*/
704 GROUP BY f.invoice_number,
705 f.invoice_id,
706 f.invoice_type,
707 f.invoice_date,
708 f.entered_date,
709 f.invoice_currency_code,
710 f.invoice_amount,
711 f.'||l_invoice_amt_col||',
712 hold.FII_MEASURE11,
713 hold1.FII_MEASURE12,
714 f.'||l_discount_offered||',
715 t.name,
716 f.source) g ) h
717 WHERE (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
718 &ORDER_BY_CLAUSE
719 ';
720
721 /*added logic base.entered_Date<&BIS_CURRENT_ASOF_DATE
722 for Bug no.3054524 in Invoices Past Due Detail repts */
723 /* Added logic in Invoice past due detail report to include
724 witholding amt in unpaid invoices for
725 bug no 3055143 */
726 WHEN 'FII_AP_UNPAID_INV_PAST_DUE' THEN -- 3rd, Invoices Past Due Detail
727 sqlstmt := '
728 SELECT
729 h.FII_MEASURE1 FII_MEASURE1,
730 h.FII_MEASURE2 FII_MEASURE2,
731 h.FII_MEASURE3 FII_MEASURE3,
732 h.FII_MEASURE4 FII_MEASURE4,
733 h.FII_MEASURE5 FII_MEASURE5,
734 h.FII_MEASURE6 FII_MEASURE6,
735 h.FII_MEASURE7 FII_MEASURE7,
736 h.FII_MEASURE8 FII_MEASURE8,
737 h.FII_MEASURE9 FII_MEASURE9,
738 h.FII_MEASURE10 FII_MEASURE10,
739 h.FII_MEASURE11 FII_MEASURE11,
740 h.FII_MEASURE12 FII_MEASURE12,
741 h.FII_MEASURE13 FII_MEASURE13,
742 h.FII_MEASURE14 FII_MEASURE14,
743 h.FII_MEASURE15 FII_MEASURE15,
744 h.FII_MEASURE16 FII_MEASURE16,
745 h.FII_MEASURE17 FII_MEASURE17,
746 h.FII_MEASURE18 FII_MEASURE18,
747 h.FII_MEASURE21 FII_MEASURE21,
748 h.FII_MEASURE22 FII_MEASURE22,
749 h.FII_MEASURE23 FII_MEASURE23,
750 h.FII_MEASURE24 FII_MEASURE24,
751 h.FII_MEASURE25 FII_MEASURE25,
752 h.FII_MEASURE26 FII_MEASURE26,
753 h.FII_MEASURE27 FII_MEASURE27,
754 '''||l_url_1||''' FII_ATTRIBUTE10,
755 '''||l_url_2||''' FII_ATTRIBUTE11,
756 '''||l_url_3||''' FII_ATTRIBUTE12,
757 '''||l_url_4||''' FII_ATTRIBUTE13
758
759 FROM
760 (
761 SELECT
762 g.FII_MEASURE1 FII_MEASURE1,
763 g.FII_MEASURE2 FII_MEASURE2,
764 g.FII_MEASURE3 FII_MEASURE3,
765 g.FII_MEASURE4 FII_MEASURE4,
766 g.FII_MEASURE5 FII_MEASURE5,
767 g.FII_MEASURE6 FII_MEASURE6,
768 g.FII_MEASURE7 FII_MEASURE7,
769 g.FII_MEASURE8 FII_MEASURE8,
770 g.FII_MEASURE9 FII_MEASURE9,
771 g.FII_MEASURE10 FII_MEASURE10,
772 g.FII_MEASURE11 FII_MEASURE11,
773 g.FII_MEASURE12 FII_MEASURE12,
774 g.FII_MEASURE13 FII_MEASURE13,
775 g.FII_MEASURE14 FII_MEASURE14,
776 g.FII_MEASURE15 FII_MEASURE15,
777 g.FII_MEASURE16 FII_MEASURE16,
778 g.FII_MEASURE17 FII_MEASURE17,
782 g.FII_MEASURE23 FII_MEASURE23,
779 g.FII_MEASURE18 FII_MEASURE18,
780 g.FII_MEASURE21 FII_MEASURE21,
781 g.FII_MEASURE22 FII_MEASURE22,
783 g.FII_MEASURE24 FII_MEASURE24,
784 g.FII_MEASURE25 FII_MEASURE25,
785 g.FII_MEASURE26 FII_MEASURE26,
786 g.FII_MEASURE27 FII_MEASURE27,
787 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
788 FROM
789
790 (SELECT f.invoice_number FII_MEASURE1,
791 f.invoice_id FII_MEASURE2,
792 f.invoice_type FII_MEASURE3,
793 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
794 f.entered_date FII_MEASURE5, -- Bug #4266826
795 MIN(fpay.due_date) FII_MEASURE6, -- Bug #4266826
796 f.invoice_currency_code FII_MEASURE7,
797 f.invoice_amount FII_MEASURE8,
798 f.'||l_invoice_amt_col||' FII_MEASURE9,
799 sum(fpay.'||l_unpaid_amt_col||') FII_MEASURE10,
800 decode(nvl(hold.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
801 nvl(hold1.FII_MEASURE12,0) FII_MEASURE12,
802 f.'||l_discount_offered||' FII_MEASURE13,
803 SUM(fpay.'||l_discount_taken||') FII_MEASURE14,
804 SUM(fpay.'||l_discount_lost||') FII_MEASURE15,
805 SUM(fpay.'||l_discount_available||') FII_MEASURE16,
806 t.name FII_MEASURE17,
807 f.source FII_MEASURE18,
808 to_number(null) FII_MEASURE21,
809 SUM(f.'||l_invoice_amt_col||') OVER() FII_MEASURE22,
810 SUM(sum(fpay.'||l_unpaid_amt_col||')) OVER() FII_MEASURE23,
811 SUM(f.'||l_discount_offered||') OVER() FII_MEASURE24,
812 SUM(SUM(fpay.'||l_discount_taken||')) OVER() FII_MEASURE25,
813 SUM(SUM(fpay.'||l_discount_lost||')) OVER() FII_MEASURE26,
814 SUM(SUM(fpay.'||l_discount_available||')) OVER() FII_MEASURE27
815 FROM fii_ap_invoice_b f,
816 fii_ap_pay_sched_b fpay,
817 ap_terms_tl t,
818 (
819 SELECT f.invoice_id,
820 ''Y'' FII_MEASURE11
821 FROM fii_ap_inv_holds_b f
822 WHERE ( f.release_date > &BIS_CURRENT_ASOF_DATE OR f.release_date is null)
823 AND f.hold_date <= &BIS_CURRENT_ASOF_DATE
824 '||l_org_where||' '||l_sup_where|| '
825 GROUP BY invoice_id
826 ) hold,
827 (SELECT f.invoice_id,
828 SUM(days_on_hold) FII_MEASURE12
829 FROM fii_ap_hhist_ib_mv f
830 WHERE 1 = 1
831 '||l_org_where||' '||l_sup_where|| '
832 GROUP BY invoice_id
833 ) hold1
834 WHERE fpay.due_date < &BIS_CURRENT_ASOF_DATE
835 AND f.entered_Date<=&BIS_CURRENT_ASOF_DATE /*added for bug no.3054524*/
836 AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added for bug no.3114633*/
837 AND fpay.action <> ''PREPAYMENT''
838 '||l_org_where||' '||l_sup_where||'
839 AND t.term_id = f.terms_id
840 AND t.language = userenv(''LANG'')
841 AND ( f.fully_paid_date > &BIS_CURRENT_ASOF_DATE OR f.fully_paid_date is null )
842 AND f.invoice_id = fpay.invoice_id
843 AND f.cancel_flag = ''N''
844 AND f.invoice_id =hold.invoice_id (+)
845 AND f.invoice_id =hold1.invoice_id (+)
846 -- HAVING sum(fpay.'||l_unpaid_amt_col||') <> 0 /* bug # 3129815*/
847 HAVING sum(fpay.amount_remaining) <> 0 /* bug # 3191403*/
848 GROUP BY f.invoice_number,
849 f.invoice_id,
850 f.invoice_type,
851 f.invoice_date,
852 f.entered_date,
853 f.invoice_currency_code,
854 f.invoice_amount,
855 f.'||l_invoice_amt_col||',
856 hold.FII_MEASURE11,
857 hold1.FII_MEASURE12,
858 f.'||l_discount_offered||',
859 t.name,
860 f.source) g ) h
861 WHERE (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
862 &ORDER_BY_CLAUSE
863 ';
864
865
866 WHEN 'FII_AP_DUE_BUCKET1_INV_DETAIL' THEN -- 4th, Invoices Due in 1-15 days
867 sqlstmt := '
868 SELECT
869 h.FII_MEASURE1 FII_MEASURE1,
870 h.FII_MEASURE2 FII_MEASURE2,
871 h.FII_MEASURE3 FII_MEASURE3,
872 h.FII_MEASURE4 FII_MEASURE4,
873 h.FII_MEASURE5 FII_MEASURE5,
874 h.FII_MEASURE6 FII_MEASURE6,
875 h.FII_MEASURE7 FII_MEASURE7,
876 h.FII_MEASURE8 FII_MEASURE8,
877 h.FII_MEASURE9 FII_MEASURE9,
878 h.FII_MEASURE10 FII_MEASURE10,
879 h.FII_MEASURE11 FII_MEASURE11,
880 h.FII_MEASURE12 FII_MEASURE12,
881 h.FII_MEASURE13 FII_MEASURE13,
882 h.FII_MEASURE14 FII_MEASURE14,
883 h.FII_MEASURE15 FII_MEASURE15,
884 h.FII_MEASURE16 FII_MEASURE16,
885 h.FII_MEASURE17 FII_MEASURE17,
886 h.FII_MEASURE18 FII_MEASURE18,
887 h.FII_MEASURE21 FII_MEASURE21,
888 h.FII_MEASURE22 FII_MEASURE22,
889 h.FII_MEASURE23 FII_MEASURE23,
890 h.FII_MEASURE24 FII_MEASURE24,
891 h.FII_MEASURE25 FII_MEASURE25,
892 h.FII_MEASURE26 FII_MEASURE26,
893 h.FII_MEASURE27 FII_MEASURE27,
894 '''||l_url_1||''' FII_ATTRIBUTE10,
895 '''||l_url_2||''' FII_ATTRIBUTE11,
896 '''||l_url_3||''' FII_ATTRIBUTE12,
897 '''||l_url_4||''' FII_ATTRIBUTE13
898
899 FROM
900 (
901 SELECT
902 g.FII_MEASURE1 FII_MEASURE1,
903 g.FII_MEASURE2 FII_MEASURE2,
904 g.FII_MEASURE3 FII_MEASURE3,
908 g.FII_MEASURE7 FII_MEASURE7,
905 g.FII_MEASURE4 FII_MEASURE4,
906 g.FII_MEASURE5 FII_MEASURE5,
907 g.FII_MEASURE6 FII_MEASURE6,
909 g.FII_MEASURE8 FII_MEASURE8,
910 g.FII_MEASURE9 FII_MEASURE9,
911 g.FII_MEASURE10 FII_MEASURE10,
912 g.FII_MEASURE11 FII_MEASURE11,
913 g.FII_MEASURE12 FII_MEASURE12,
914 g.FII_MEASURE13 FII_MEASURE13,
915 g.FII_MEASURE14 FII_MEASURE14,
916 g.FII_MEASURE15 FII_MEASURE15,
917 g.FII_MEASURE16 FII_MEASURE16,
918 g.FII_MEASURE17 FII_MEASURE17,
919 g.FII_MEASURE18 FII_MEASURE18,
920 g.FII_MEASURE21 FII_MEASURE21,
921 g.FII_MEASURE22 FII_MEASURE22,
922 g.FII_MEASURE23 FII_MEASURE23,
923 g.FII_MEASURE24 FII_MEASURE24,
924 g.FII_MEASURE25 FII_MEASURE25,
925 g.FII_MEASURE26 FII_MEASURE26,
926 g.FII_MEASURE27 FII_MEASURE27,
927 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
928 FROM
929
930 (SELECT f.invoice_number FII_MEASURE1,
931 f.invoice_id FII_MEASURE2,
932 f.invoice_type FII_MEASURE3,
933 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
934 f.entered_date FII_MEASURE5, -- Bug #4266826
935 MIN(fpay.due_date) FII_MEASURE6, -- Bug #4266826
936 f.invoice_currency_code FII_MEASURE7,
937 f.invoice_amount FII_MEASURE8,
938 f.'||l_invoice_amt_col||' FII_MEASURE9,
939 sum(fpay.'||l_unpaid_amt_col||') FII_MEASURE10,
940 decode(nvl(hold.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
941 nvl(hold1.FII_MEASURE12,0) FII_MEASURE12,
942 f.'||l_discount_offered||' FII_MEASURE13,
943 SUM(fpay.'||l_discount_taken||') FII_MEASURE14,
944 SUM(fpay.'||l_discount_lost||') FII_MEASURE15,
945 SUM(fpay.'||l_discount_available||') FII_MEASURE16,
946 t.name FII_MEASURE17,
947 f.source FII_MEASURE18,
948 to_number(null) FII_MEASURE21,
949 SUM(f.'||l_invoice_amt_col||') OVER() FII_MEASURE22,
950 SUM(sum(fpay.'||l_unpaid_amt_col||')) OVER() FII_MEASURE23,
951 SUM(f.'||l_discount_offered||') OVER() FII_MEASURE24,
952 SUM(SUM(fpay.'||l_discount_taken||')) OVER() FII_MEASURE25,
953 SUM(SUM(fpay.'||l_discount_lost||')) OVER() FII_MEASURE26,
954 SUM(SUM(fpay.'||l_discount_available||')) OVER() FII_MEASURE27
955 FROM fii_ap_invoice_b f,
956 fii_ap_pay_sched_b fpay,
957 ap_terms_tl t,
958 (
959 SELECT f.invoice_id,
960 ''Y'' FII_MEASURE11
961 FROM fii_ap_inv_holds_b f
962 WHERE ( f.release_date > &BIS_CURRENT_ASOF_DATE OR f.release_date is null)
963 AND f.hold_date <= &BIS_CURRENT_ASOF_DATE
964 '||l_org_where||' '||l_sup_where|| '
965 GROUP BY invoice_id
966 ) hold,
967 ( SELECT f.invoice_id,
968 SUM(days_on_hold) FII_MEASURE12
969 FROM fii_ap_hhist_ib_mv f
970 WHERE 1 = 1
971 '||l_org_where||' '||l_sup_where|| '
972 GROUP BY invoice_id
973 ) hold1
974 WHERE (fpay.due_date - &BIS_CURRENT_ASOF_DATE ) BETWEEN 0 AND 15
975 AND f.entered_Date<=&BIS_CURRENT_ASOF_DATE /*added for bug no.3054524*/
976 AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added for bug no.3114633*/
977 AND fpay.action <> ''PREPAYMENT''
978 '||l_org_where||' '||l_sup_where||'
979 AND t.term_id = f.terms_id
980 AND t.language = userenv(''LANG'')
981 AND (f.fully_paid_date > &BIS_CURRENT_ASOF_DATE OR f.fully_paid_date is null )
982 AND f.invoice_id = fpay.invoice_id
983 AND f.cancel_flag = ''N''
984 AND f.invoice_id =hold.invoice_id (+)
985 AND f.invoice_id =hold1.invoice_id (+)
986 -- HAVING sum(fpay.'||l_unpaid_amt_col||') <> 0 /* bug # 3148973 */
987 HAVING sum(fpay.amount_remaining) <> 0 /* bug # 3191403*/
988 GROUP BY f.invoice_number,
989 f.invoice_id,
990 f.invoice_type,
991 f.invoice_date,
992 f.entered_date,
993 f.invoice_currency_code,
994 f.invoice_amount,
995 f.'||l_invoice_amt_col||',
996 hold.FII_MEASURE11,
997 hold1.FII_MEASURE12,
998 f.'||l_discount_offered||',
999 t.name,
1000 f.source) g ) h
1001 WHERE (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
1002 &ORDER_BY_CLAUSE
1003 ';
1004
1005
1006 WHEN 'FII_AP_DUE_BUCKET2_INV_DETAIL' THEN -- 5th, Invoices Due in 16-30 days
1007 sqlstmt := '
1008 SELECT
1009 h.FII_MEASURE1 FII_MEASURE1,
1010 h.FII_MEASURE2 FII_MEASURE2,
1011 h.FII_MEASURE3 FII_MEASURE3,
1012 h.FII_MEASURE4 FII_MEASURE4,
1013 h.FII_MEASURE5 FII_MEASURE5,
1014 h.FII_MEASURE6 FII_MEASURE6,
1015 h.FII_MEASURE7 FII_MEASURE7,
1016 h.FII_MEASURE8 FII_MEASURE8,
1017 h.FII_MEASURE9 FII_MEASURE9,
1018 h.FII_MEASURE10 FII_MEASURE10,
1019 h.FII_MEASURE11 FII_MEASURE11,
1020 h.FII_MEASURE12 FII_MEASURE12,
1021 h.FII_MEASURE13 FII_MEASURE13,
1022 h.FII_MEASURE14 FII_MEASURE14,
1023 h.FII_MEASURE15 FII_MEASURE15,
1024 h.FII_MEASURE16 FII_MEASURE16,
1025 h.FII_MEASURE17 FII_MEASURE17,
1026 h.FII_MEASURE18 FII_MEASURE18,
1027 h.FII_MEASURE21 FII_MEASURE21,
1028 h.FII_MEASURE22 FII_MEASURE22,
1032 h.FII_MEASURE26 FII_MEASURE26,
1029 h.FII_MEASURE23 FII_MEASURE23,
1030 h.FII_MEASURE24 FII_MEASURE24,
1031 h.FII_MEASURE25 FII_MEASURE25,
1033 h.FII_MEASURE27 FII_MEASURE27,
1034 '''||l_url_1||''' FII_ATTRIBUTE10,
1035 '''||l_url_2||''' FII_ATTRIBUTE11,
1036 '''||l_url_3||''' FII_ATTRIBUTE12,
1037 '''||l_url_4||''' FII_ATTRIBUTE13
1038
1039 FROM
1040 (
1041 SELECT
1042 g.FII_MEASURE1 FII_MEASURE1,
1043 g.FII_MEASURE2 FII_MEASURE2,
1044 g.FII_MEASURE3 FII_MEASURE3,
1045 g.FII_MEASURE4 FII_MEASURE4,
1046 g.FII_MEASURE5 FII_MEASURE5,
1047 g.FII_MEASURE6 FII_MEASURE6,
1048 g.FII_MEASURE7 FII_MEASURE7,
1049 g.FII_MEASURE8 FII_MEASURE8,
1050 g.FII_MEASURE9 FII_MEASURE9,
1051 g.FII_MEASURE10 FII_MEASURE10,
1052 g.FII_MEASURE11 FII_MEASURE11,
1053 g.FII_MEASURE12 FII_MEASURE12,
1054 g.FII_MEASURE13 FII_MEASURE13,
1055 g.FII_MEASURE14 FII_MEASURE14,
1056 g.FII_MEASURE15 FII_MEASURE15,
1057 g.FII_MEASURE16 FII_MEASURE16,
1058 g.FII_MEASURE17 FII_MEASURE17,
1059 g.FII_MEASURE18 FII_MEASURE18,
1060 g.FII_MEASURE21 FII_MEASURE21,
1061 g.FII_MEASURE22 FII_MEASURE22,
1062 g.FII_MEASURE23 FII_MEASURE23,
1063 g.FII_MEASURE24 FII_MEASURE24,
1064 g.FII_MEASURE25 FII_MEASURE25,
1065 g.FII_MEASURE26 FII_MEASURE26,
1066 g.FII_MEASURE27 FII_MEASURE27,
1067 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
1068 FROM
1069
1070 (SELECT f.invoice_number FII_MEASURE1,
1071 f.invoice_id FII_MEASURE2,
1072 f.invoice_type FII_MEASURE3,
1073 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
1074 f.entered_date FII_MEASURE5, -- Bug #4266826
1075 MIN(fpay.due_date) FII_MEASURE6, -- Bug #4266826
1076 f.invoice_currency_code FII_MEASURE7,
1077 f.invoice_amount FII_MEASURE8,
1078 f.'||l_invoice_amt_col||' FII_MEASURE9,
1079 sum(fpay.'||l_unpaid_amt_col||') FII_MEASURE10,
1080 decode(nvl(hold.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
1081 nvl(hold1.FII_MEASURE12,0) FII_MEASURE12,
1082 f.'||l_discount_offered||' FII_MEASURE13,
1083 SUM(fpay.'||l_discount_taken||') FII_MEASURE14,
1084 SUM(fpay.'||l_discount_lost||') FII_MEASURE15,
1085 SUM(fpay.'||l_discount_available||') FII_MEASURE16,
1086 t.name FII_MEASURE17,
1087 f.source FII_MEASURE18,
1088 to_number(null) FII_MEASURE21,
1089 SUM(f.'||l_invoice_amt_col||') OVER() FII_MEASURE22,
1090 SUM(sum(fpay.'||l_unpaid_amt_col||')) OVER() FII_MEASURE23,
1091 SUM(f.'||l_discount_offered||') OVER() FII_MEASURE24,
1092 SUM(SUM(fpay.'||l_discount_taken||')) OVER() FII_MEASURE25,
1093 SUM(SUM(fpay.'||l_discount_lost||')) OVER() FII_MEASURE26,
1094 SUM(SUM(fpay.'||l_discount_available||')) OVER() FII_MEASURE27
1095 FROM fii_ap_invoice_b f,
1096 fii_ap_pay_sched_b fpay,
1097 ap_terms_tl t,
1098 (
1099 SELECT f.invoice_id,
1100 ''Y'' FII_MEASURE11
1101 FROM fii_ap_inv_holds_b f
1102 WHERE ( f.release_date > &BIS_CURRENT_ASOF_DATE OR f.release_date is null)
1103 AND f.hold_date <= &BIS_CURRENT_ASOF_DATE
1104 '||l_org_where||' '||l_sup_where|| '
1105 GROUP BY invoice_id
1106 ) hold,
1107 ( SELECT f.invoice_id,
1108 SUM(days_on_hold) FII_MEASURE12
1109 FROM fii_ap_hhist_ib_mv f
1110 WHERE 1 = 1
1111 '||l_org_where||' '||l_sup_where|| '
1112 GROUP BY invoice_id
1113 ) hold1
1114 WHERE (fpay.due_date - &BIS_CURRENT_ASOF_DATE) BETWEEN 16 AND 30
1115 AND f.entered_Date<=&BIS_CURRENT_ASOF_DATE /*added for bug no.3054524*/
1116 AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added for bug no.3114633*/
1117 AND fpay.action <> ''PREPAYMENT''
1118 '||l_org_where||' '||l_sup_where||'
1119 AND t.term_id = f.terms_id
1120 AND t.language = userenv(''LANG'')
1121 AND (f.fully_paid_date > &BIS_CURRENT_ASOF_DATE OR f.fully_paid_date IS NULL)
1122 AND f.invoice_id = fpay.invoice_id
1123 AND f.cancel_flag = ''N''
1124 AND f.invoice_id =hold.invoice_id (+)
1125 AND f.invoice_id =hold1.invoice_id (+)
1126 -- HAVING sum(fpay.'||l_unpaid_amt_col||') <> 0 /* bug # 3148973 */
1127 HAVING sum(fpay.amount_remaining) <> 0 /* bug # 3191403*/
1128 GROUP BY f.invoice_number,
1129 f.invoice_id,
1130 f.invoice_type,
1131 f.invoice_date,
1132 f.entered_date,
1133 f.invoice_currency_code,
1134 f.invoice_amount,
1135 f.'||l_invoice_amt_col||',
1136 hold.FII_MEASURE11,
1137 hold1.FII_MEASURE12,
1138 f.'||l_discount_offered||',
1139 t.name,
1140 f.source) g ) h
1141 WHERE (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
1142 &ORDER_BY_CLAUSE
1143 ';
1144
1145
1146 WHEN 'FII_AP_DUE_BUCKET3_INV_DETAIL' THEN -- 6th, Invoices Due After 30 days
1147 sqlstmt := '
1148 SELECT
1149 h.FII_MEASURE1 FII_MEASURE1,
1150 h.FII_MEASURE2 FII_MEASURE2,
1151 h.FII_MEASURE3 FII_MEASURE3,
1152 h.FII_MEASURE4 FII_MEASURE4,
1153 h.FII_MEASURE5 FII_MEASURE5,
1154 h.FII_MEASURE6 FII_MEASURE6,
1158 h.FII_MEASURE10 FII_MEASURE10,
1155 h.FII_MEASURE7 FII_MEASURE7,
1156 h.FII_MEASURE8 FII_MEASURE8,
1157 h.FII_MEASURE9 FII_MEASURE9,
1159 h.FII_MEASURE11 FII_MEASURE11,
1160 h.FII_MEASURE12 FII_MEASURE12,
1161 h.FII_MEASURE13 FII_MEASURE13,
1162 h.FII_MEASURE14 FII_MEASURE14,
1163 h.FII_MEASURE15 FII_MEASURE15,
1164 h.FII_MEASURE16 FII_MEASURE16,
1165 h.FII_MEASURE17 FII_MEASURE17,
1166 h.FII_MEASURE18 FII_MEASURE18,
1167 h.FII_MEASURE21 FII_MEASURE21,
1168 h.FII_MEASURE22 FII_MEASURE22,
1169 h.FII_MEASURE23 FII_MEASURE23,
1170 h.FII_MEASURE24 FII_MEASURE24,
1171 h.FII_MEASURE25 FII_MEASURE25,
1172 h.FII_MEASURE26 FII_MEASURE26,
1173 h.FII_MEASURE27 FII_MEASURE27,
1174 '''||l_url_1||''' FII_ATTRIBUTE10,
1175 '''||l_url_2||''' FII_ATTRIBUTE11,
1176 '''||l_url_3||''' FII_ATTRIBUTE12,
1177 '''||l_url_4||''' FII_ATTRIBUTE13
1178
1179 FROM
1180 (
1181 SELECT
1182 g.FII_MEASURE1 FII_MEASURE1,
1183 g.FII_MEASURE2 FII_MEASURE2,
1184 g.FII_MEASURE3 FII_MEASURE3,
1185 g.FII_MEASURE4 FII_MEASURE4,
1186 g.FII_MEASURE5 FII_MEASURE5,
1187 g.FII_MEASURE6 FII_MEASURE6,
1188 g.FII_MEASURE7 FII_MEASURE7,
1189 g.FII_MEASURE8 FII_MEASURE8,
1190 g.FII_MEASURE9 FII_MEASURE9,
1191 g.FII_MEASURE10 FII_MEASURE10,
1192 g.FII_MEASURE11 FII_MEASURE11,
1193 g.FII_MEASURE12 FII_MEASURE12,
1194 g.FII_MEASURE13 FII_MEASURE13,
1195 g.FII_MEASURE14 FII_MEASURE14,
1196 g.FII_MEASURE15 FII_MEASURE15,
1197 g.FII_MEASURE16 FII_MEASURE16,
1198 g.FII_MEASURE17 FII_MEASURE17,
1199 g.FII_MEASURE18 FII_MEASURE18,
1200 g.FII_MEASURE21 FII_MEASURE21,
1201 g.FII_MEASURE22 FII_MEASURE22,
1202 g.FII_MEASURE23 FII_MEASURE23,
1203 g.FII_MEASURE24 FII_MEASURE24,
1204 g.FII_MEASURE25 FII_MEASURE25,
1205 g.FII_MEASURE26 FII_MEASURE26,
1206 g.FII_MEASURE27 FII_MEASURE27,
1207 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
1208 FROM
1209
1210 (SELECT f.invoice_number FII_MEASURE1,
1211 f.invoice_id FII_MEASURE2,
1212 f.invoice_type FII_MEASURE3,
1213 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
1214 f.entered_date FII_MEASURE5, -- Bug #4266826
1215 MIN(fpay.due_date) FII_MEASURE6, -- Bug #4266826
1216 f.invoice_currency_code FII_MEASURE7,
1217 f.invoice_amount FII_MEASURE8,
1218 f.'||l_invoice_amt_col||' FII_MEASURE9,
1219 sum(fpay.'||l_unpaid_amt_col||') FII_MEASURE10,
1220 decode(nvl(hold.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
1221 nvl(hold1.FII_MEASURE12,0) FII_MEASURE12,
1222 f.'||l_discount_offered||' FII_MEASURE13,
1223 SUM(fpay.'||l_discount_taken||') FII_MEASURE14,
1224 SUM(fpay.'||l_discount_lost||') FII_MEASURE15,
1225 SUM(fpay.'||l_discount_available||') FII_MEASURE16,
1226 t.name FII_MEASURE17,
1227 f.source FII_MEASURE18,
1228 to_number(null) FII_MEASURE21,
1229 SUM(f.'||l_invoice_amt_col||') OVER() FII_MEASURE22,
1230 SUM(sum(fpay.'||l_unpaid_amt_col||')) OVER() FII_MEASURE23,
1231 SUM(f.'||l_discount_offered||') OVER() FII_MEASURE24,
1232 SUM(SUM(fpay.'||l_discount_taken||')) OVER() FII_MEASURE25,
1233 SUM(SUM(fpay.'||l_discount_lost||')) OVER() FII_MEASURE26,
1234 SUM(SUM(fpay.'||l_discount_available||')) OVER() FII_MEASURE27
1235 FROM fii_ap_invoice_b f,
1236 fii_ap_pay_sched_b fpay,
1237 ap_terms_tl t,
1238 (
1239 SELECT f.invoice_id,
1240 ''Y'' FII_MEASURE11
1241 FROM fii_ap_inv_holds_b f
1242 WHERE ( f.release_date > &BIS_CURRENT_ASOF_DATE OR f.release_date is null)
1243 AND f.hold_date <= &BIS_CURRENT_ASOF_DATE
1244 '||l_org_where||' '||l_sup_where|| '
1245 GROUP BY invoice_id
1246 ) hold,
1247 ( SELECT f.invoice_id,
1248 SUM(days_on_hold) FII_MEASURE12
1249 FROM fii_ap_hhist_ib_mv f
1250 WHERE 1 = 1
1251 '||l_org_where||' '||l_sup_where|| '
1252 GROUP BY invoice_id
1253 ) hold1
1254 WHERE (fpay.due_date - &BIS_CURRENT_ASOF_DATE) > 30
1255 AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added for bug no.3114633*/
1256 AND fpay.action <> ''PREPAYMENT''
1257 '||l_org_where||' '||l_sup_where||'
1258 AND f.entered_Date<=&BIS_CURRENT_ASOF_DATE /*added for bug no.3054524*/
1259 AND t.term_id = f.terms_id
1260 AND t.language = userenv(''LANG'')
1261 AND (f.fully_paid_date > &BIS_CURRENT_ASOF_DATE OR f.fully_paid_date IS NULL)
1262 AND f.invoice_id = fpay.invoice_id
1263 AND f.cancel_flag = ''N''
1264 AND f.invoice_id =hold.invoice_id (+)
1265 AND f.invoice_id =hold1.invoice_id (+)
1266 -- HAVING sum(fpay.'||l_unpaid_amt_col||') <> 0 /* bug # 3148973 */
1267 HAVING sum(fpay.amount_remaining) <> 0 /* bug # 3191403*/
1268 GROUP BY f.invoice_number,
1269 f.invoice_id,
1270 f.invoice_type,
1271 f.invoice_date,
1272 f.entered_date,
1273 f.invoice_currency_code,
1274 f.invoice_amount,
1275 f.'||l_invoice_amt_col||',
1276 hold.FII_MEASURE11,
1277 hold1.FII_MEASURE12,
1278 f.'||l_discount_offered||',
1279 t.name,
1283 ';
1280 f.source) g ) h
1281 WHERE (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
1282 &ORDER_BY_CLAUSE
1284
1285
1286 WHEN 'FII_AP_PDUE_BUCKET1_INV_DETAIL' THEN -- 7th, Invoices 1-15 Days Past Due
1287 sqlstmt := '
1288 SELECT
1289 h.FII_MEASURE1 FII_MEASURE1,
1290 h.FII_MEASURE2 FII_MEASURE2,
1291 h.FII_MEASURE3 FII_MEASURE3,
1292 h.FII_MEASURE4 FII_MEASURE4,
1293 h.FII_MEASURE5 FII_MEASURE5,
1294 h.FII_MEASURE6 FII_MEASURE6,
1295 h.FII_MEASURE7 FII_MEASURE7,
1296 h.FII_MEASURE8 FII_MEASURE8,
1297 h.FII_MEASURE9 FII_MEASURE9,
1298 h.FII_MEASURE10 FII_MEASURE10,
1299 h.FII_MEASURE11 FII_MEASURE11,
1300 h.FII_MEASURE12 FII_MEASURE12,
1301 h.FII_MEASURE13 FII_MEASURE13,
1302 h.FII_MEASURE14 FII_MEASURE14,
1303 h.FII_MEASURE15 FII_MEASURE15,
1304 h.FII_MEASURE16 FII_MEASURE16,
1305 h.FII_MEASURE17 FII_MEASURE17,
1306 h.FII_MEASURE18 FII_MEASURE18,
1307 h.FII_MEASURE21 FII_MEASURE21,
1308 h.FII_MEASURE22 FII_MEASURE22,
1309 h.FII_MEASURE23 FII_MEASURE23,
1310 h.FII_MEASURE24 FII_MEASURE24,
1311 h.FII_MEASURE25 FII_MEASURE25,
1312 h.FII_MEASURE26 FII_MEASURE26,
1313 h.FII_MEASURE27 FII_MEASURE27,
1314 '''||l_url_1||''' FII_ATTRIBUTE10,
1315 '''||l_url_2||''' FII_ATTRIBUTE11,
1316 '''||l_url_3||''' FII_ATTRIBUTE12,
1317 '''||l_url_4||''' FII_ATTRIBUTE13
1318
1319 FROM
1320 (
1321 SELECT
1322 g.FII_MEASURE1 FII_MEASURE1,
1323 g.FII_MEASURE2 FII_MEASURE2,
1324 g.FII_MEASURE3 FII_MEASURE3,
1325 g.FII_MEASURE4 FII_MEASURE4,
1326 g.FII_MEASURE5 FII_MEASURE5,
1327 g.FII_MEASURE6 FII_MEASURE6,
1328 g.FII_MEASURE7 FII_MEASURE7,
1329 g.FII_MEASURE8 FII_MEASURE8,
1330 g.FII_MEASURE9 FII_MEASURE9,
1331 g.FII_MEASURE10 FII_MEASURE10,
1332 g.FII_MEASURE11 FII_MEASURE11,
1333 g.FII_MEASURE12 FII_MEASURE12,
1334 g.FII_MEASURE13 FII_MEASURE13,
1335 g.FII_MEASURE14 FII_MEASURE14,
1336 g.FII_MEASURE15 FII_MEASURE15,
1337 g.FII_MEASURE16 FII_MEASURE16,
1338 g.FII_MEASURE17 FII_MEASURE17,
1339 g.FII_MEASURE18 FII_MEASURE18,
1340 g.FII_MEASURE21 FII_MEASURE21,
1341 g.FII_MEASURE22 FII_MEASURE22,
1342 g.FII_MEASURE23 FII_MEASURE23,
1343 g.FII_MEASURE24 FII_MEASURE24,
1344 g.FII_MEASURE25 FII_MEASURE25,
1345 g.FII_MEASURE26 FII_MEASURE26,
1346 g.FII_MEASURE27 FII_MEASURE27,
1347 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
1348 FROM
1349
1350 (SELECT f.invoice_number FII_MEASURE1,
1351 f.invoice_id FII_MEASURE2,
1352 f.invoice_type FII_MEASURE3,
1353 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
1354 f.entered_date FII_MEASURE5, -- Bug #4266826
1355 MIN(fpay.due_date) FII_MEASURE6, -- Bug #4266826
1356 f.invoice_currency_code FII_MEASURE7,
1357 f.invoice_amount FII_MEASURE8,
1358 f.'||l_invoice_amt_col||' FII_MEASURE9,
1359 sum(fpay.'||l_unpaid_amt_col||') FII_MEASURE10,
1360 decode(nvl(hold.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
1361 nvl(hold1.FII_MEASURE12,0) FII_MEASURE12,
1362 f.'||l_discount_offered||' FII_MEASURE13,
1363 SUM(fpay.'||l_discount_taken||') FII_MEASURE14,
1364 SUM(fpay.'||l_discount_lost||') FII_MEASURE15,
1365 SUM(fpay.'||l_discount_available||') FII_MEASURE16,
1366 t.name FII_MEASURE17,
1367 f.source FII_MEASURE18,
1368 to_number(null) FII_MEASURE21,
1369 SUM(f.'||l_invoice_amt_col||') OVER() FII_MEASURE22,
1370 SUM(sum(fpay.'||l_unpaid_amt_col||')) OVER() FII_MEASURE23,
1371 SUM(f.'||l_discount_offered||') OVER() FII_MEASURE24,
1372 SUM(SUM(fpay.'||l_discount_taken||')) OVER() FII_MEASURE25,
1373 SUM(SUM(fpay.'||l_discount_lost||')) OVER() FII_MEASURE26,
1374 SUM(SUM(fpay.'||l_discount_available||')) OVER() FII_MEASURE27
1375 FROM fii_ap_invoice_b f,
1376 fii_ap_pay_sched_b fpay,
1377 ap_terms_tl t,
1378 (
1379 SELECT f.invoice_id,
1380 ''Y'' FII_MEASURE11
1381 FROM fii_ap_inv_holds_b f
1382 WHERE ( f.release_date > &BIS_CURRENT_ASOF_DATE OR f.release_date is null)
1383 AND f.hold_date <= &BIS_CURRENT_ASOF_DATE
1384 '||l_org_where||' '||l_sup_where|| '
1385 GROUP BY invoice_id
1386 ) hold,
1387 ( SELECT f.invoice_id,
1388 SUM(days_on_hold) FII_MEASURE12
1389 FROM fii_ap_hhist_ib_mv f
1390 WHERE 1 = 1
1391 '||l_org_where||' '||l_sup_where|| '
1392 GROUP BY invoice_id
1393 ) hold1
1394 WHERE (&BIS_CURRENT_ASOF_DATE - fpay.due_date) BETWEEN 1 AND 15
1395 AND f.entered_Date<=&BIS_CURRENT_ASOF_DATE /*added for bug no.3054524*/
1396 AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added for bug no.3114633*/
1397 AND fpay.action <> ''PREPAYMENT''
1398 '||l_org_where||' '||l_sup_where||'
1399 AND t.term_id = f.terms_id
1400 AND t.language = userenv(''LANG'')
1401 AND (f.fully_paid_date > &BIS_CURRENT_ASOF_DATE OR f.fully_paid_date IS NULL)
1402 AND f.invoice_id = fpay.invoice_id
1403 AND f.cancel_flag = ''N''
1404 AND f.invoice_id =hold.invoice_id (+)
1405 AND f.invoice_id =hold1.invoice_id (+)
1406 HAVING sum(fpay.amount_remaining) <> 0 /* bug # 3191403*/
1407 GROUP BY f.invoice_number,
1408 f.invoice_id,
1409 f.invoice_type,
1410 f.invoice_date,
1414 f.'||l_invoice_amt_col||',
1411 f.entered_date,
1412 f.invoice_currency_code,
1413 f.invoice_amount,
1415 hold.FII_MEASURE11,
1416 hold1.FII_MEASURE12,
1417 f.'||l_discount_offered||',
1418 t.name,
1419 f.source) g ) h
1420 WHERE (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
1421 &ORDER_BY_CLAUSE
1422 ';
1423
1424
1425 WHEN 'FII_AP_PDUE_BUCKET2_INV_DETAIL' THEN -- 8th, Invoices 16-30 Days Past Due
1426 sqlstmt := '
1427 SELECT
1428 h.FII_MEASURE1 FII_MEASURE1,
1429 h.FII_MEASURE2 FII_MEASURE2,
1430 h.FII_MEASURE3 FII_MEASURE3,
1431 h.FII_MEASURE4 FII_MEASURE4,
1432 h.FII_MEASURE5 FII_MEASURE5,
1433 h.FII_MEASURE6 FII_MEASURE6,
1434 h.FII_MEASURE7 FII_MEASURE7,
1435 h.FII_MEASURE8 FII_MEASURE8,
1436 h.FII_MEASURE9 FII_MEASURE9,
1437 h.FII_MEASURE10 FII_MEASURE10,
1438 h.FII_MEASURE11 FII_MEASURE11,
1439 h.FII_MEASURE12 FII_MEASURE12,
1440 h.FII_MEASURE13 FII_MEASURE13,
1441 h.FII_MEASURE14 FII_MEASURE14,
1442 h.FII_MEASURE15 FII_MEASURE15,
1443 h.FII_MEASURE16 FII_MEASURE16,
1444 h.FII_MEASURE17 FII_MEASURE17,
1445 h.FII_MEASURE18 FII_MEASURE18,
1446 h.FII_MEASURE21 FII_MEASURE21,
1447 h.FII_MEASURE22 FII_MEASURE22,
1448 h.FII_MEASURE23 FII_MEASURE23,
1449 h.FII_MEASURE24 FII_MEASURE24,
1450 h.FII_MEASURE25 FII_MEASURE25,
1451 h.FII_MEASURE26 FII_MEASURE26,
1452 h.FII_MEASURE27 FII_MEASURE27,
1453 '''||l_url_1||''' FII_ATTRIBUTE10,
1454 '''||l_url_2||''' FII_ATTRIBUTE11,
1455 '''||l_url_3||''' FII_ATTRIBUTE12,
1456 '''||l_url_4||''' FII_ATTRIBUTE13
1457
1458 FROM
1459 (
1460 SELECT
1461 g.FII_MEASURE1 FII_MEASURE1,
1462 g.FII_MEASURE2 FII_MEASURE2,
1463 g.FII_MEASURE3 FII_MEASURE3,
1464 g.FII_MEASURE4 FII_MEASURE4,
1465 g.FII_MEASURE5 FII_MEASURE5,
1466 g.FII_MEASURE6 FII_MEASURE6,
1467 g.FII_MEASURE7 FII_MEASURE7,
1468 g.FII_MEASURE8 FII_MEASURE8,
1469 g.FII_MEASURE9 FII_MEASURE9,
1470 g.FII_MEASURE10 FII_MEASURE10,
1471 g.FII_MEASURE11 FII_MEASURE11,
1472 g.FII_MEASURE12 FII_MEASURE12,
1473 g.FII_MEASURE13 FII_MEASURE13,
1474 g.FII_MEASURE14 FII_MEASURE14,
1475 g.FII_MEASURE15 FII_MEASURE15,
1476 g.FII_MEASURE16 FII_MEASURE16,
1477 g.FII_MEASURE17 FII_MEASURE17,
1478 g.FII_MEASURE18 FII_MEASURE18,
1479 g.FII_MEASURE21 FII_MEASURE21,
1480 g.FII_MEASURE22 FII_MEASURE22,
1481 g.FII_MEASURE23 FII_MEASURE23,
1482 g.FII_MEASURE24 FII_MEASURE24,
1483 g.FII_MEASURE25 FII_MEASURE25,
1484 g.FII_MEASURE26 FII_MEASURE26,
1485 g.FII_MEASURE27 FII_MEASURE27,
1486 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
1487 FROM
1488
1489 (SELECT f.invoice_number FII_MEASURE1,
1490 f.invoice_id FII_MEASURE2,
1491 f.invoice_type FII_MEASURE3,
1492 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
1493 f.entered_date FII_MEASURE5, -- Bug #4266826
1494 MIN(fpay.due_date) FII_MEASURE6, -- Bug #4266826
1495 f.invoice_currency_code FII_MEASURE7,
1496 f.invoice_amount FII_MEASURE8,
1497 f.'||l_invoice_amt_col||' FII_MEASURE9,
1498 sum(fpay.'||l_unpaid_amt_col||') FII_MEASURE10,
1499 decode(nvl(hold.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
1503 SUM(fpay.'||l_discount_lost||') FII_MEASURE15,
1500 nvl(hold1.FII_MEASURE12,0) FII_MEASURE12,
1501 f.'||l_discount_offered||' FII_MEASURE13,
1502 SUM(fpay.'||l_discount_taken||') FII_MEASURE14,
1504 SUM(fpay.'||l_discount_available||') FII_MEASURE16,
1505 t.name FII_MEASURE17,
1506 f.source FII_MEASURE18,
1507 to_number(null) FII_MEASURE21,
1508 SUM(f.'||l_invoice_amt_col||') OVER() FII_MEASURE22,
1509 SUM(sum(fpay.'||l_unpaid_amt_col||')) OVER() FII_MEASURE23,
1510 SUM(f.'||l_discount_offered||') OVER() FII_MEASURE24,
1511 SUM(SUM(fpay.'||l_discount_taken||')) OVER() FII_MEASURE25,
1512 SUM(SUM(fpay.'||l_discount_lost||')) OVER() FII_MEASURE26,
1513 SUM(SUM(fpay.'||l_discount_available||')) OVER() FII_MEASURE27
1514 FROM fii_ap_invoice_b f,
1515 fii_ap_pay_sched_b fpay,
1516 ap_terms_tl t,
1517 (
1518 SELECT f.invoice_id,
1519 ''Y'' FII_MEASURE11
1520 FROM fii_ap_inv_holds_b f
1521 WHERE ( f.release_date > &BIS_CURRENT_ASOF_DATE OR f.release_date is null)
1522 AND f.hold_date <= &BIS_CURRENT_ASOF_DATE
1523 '||l_org_where||' '||l_sup_where|| '
1524 GROUP BY invoice_id
1525 ) hold,
1526 ( SELECT f.invoice_id,
1527 SUM(days_on_hold) FII_MEASURE12
1528 FROM fii_ap_hhist_ib_mv f
1529 WHERE 1 = 1
1530 '||l_org_where||' '||l_sup_where|| '
1531 GROUP BY invoice_id
1532 ) hold1
1533 WHERE (&BIS_CURRENT_ASOF_DATE - fpay.due_date) BETWEEN 16 AND 30
1534 AND f.entered_Date<=&BIS_CURRENT_ASOF_DATE /*added for bug no.3054524*/
1535 AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added for bug no.3114633*/
1536 AND fpay.action <> ''PREPAYMENT''
1537 '||l_org_where||' '||l_sup_where||'
1538 AND t.term_id = f.terms_id
1539 AND t.language = userenv(''LANG'')
1540 AND (f.fully_paid_date > &BIS_CURRENT_ASOF_DATE OR f.fully_paid_date IS NULL)
1541 AND f.cancel_flag = ''N''
1542 AND f.invoice_id = fpay.invoice_id
1543 AND f.invoice_id =hold.invoice_id (+)
1544 AND f.invoice_id =hold1.invoice_id (+)
1545 HAVING sum(fpay.amount_remaining) <> 0 /* bug # 3191403*/
1546 GROUP BY f.invoice_number,
1547 f.invoice_id,
1548 f.invoice_type,
1549 f.invoice_date,
1550 f.entered_date,
1551 f.invoice_currency_code,
1552 f.invoice_amount,
1553 f.'||l_invoice_amt_col||',
1554 hold.FII_MEASURE11,
1555 hold1.FII_MEASURE12,
1556 f.'||l_discount_offered||',
1557 t.name,
1558 f.source) g ) h
1559 WHERE (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
1560 &ORDER_BY_CLAUSE
1561 ';
1562
1563
1564 WHEN 'FII_AP_PDUE_BUCKET3_INV_DETAIL' THEN -- 9th, Invoices Over 30 Days Past Due
1565 sqlstmt := '
1566 SELECT
1567 h.FII_MEASURE1 FII_MEASURE1,
1568 h.FII_MEASURE2 FII_MEASURE2,
1569 h.FII_MEASURE3 FII_MEASURE3,
1570 h.FII_MEASURE4 FII_MEASURE4,
1571 h.FII_MEASURE5 FII_MEASURE5,
1572 h.FII_MEASURE6 FII_MEASURE6,
1573 h.FII_MEASURE7 FII_MEASURE7,
1574 h.FII_MEASURE8 FII_MEASURE8,
1575 h.FII_MEASURE9 FII_MEASURE9,
1576 h.FII_MEASURE10 FII_MEASURE10,
1577 h.FII_MEASURE11 FII_MEASURE11,
1578 h.FII_MEASURE12 FII_MEASURE12,
1579 h.FII_MEASURE13 FII_MEASURE13,
1580 h.FII_MEASURE14 FII_MEASURE14,
1581 h.FII_MEASURE15 FII_MEASURE15,
1582 h.FII_MEASURE16 FII_MEASURE16,
1583 h.FII_MEASURE17 FII_MEASURE17,
1584 h.FII_MEASURE18 FII_MEASURE18,
1585 h.FII_MEASURE21 FII_MEASURE21,
1586 h.FII_MEASURE22 FII_MEASURE22,
1587 h.FII_MEASURE23 FII_MEASURE23,
1588 h.FII_MEASURE24 FII_MEASURE24,
1589 h.FII_MEASURE25 FII_MEASURE25,
1590 h.FII_MEASURE26 FII_MEASURE26,
1591 h.FII_MEASURE27 FII_MEASURE27,
1592 '''||l_url_1||''' FII_ATTRIBUTE10,
1593 '''||l_url_2||''' FII_ATTRIBUTE11,
1594 '''||l_url_3||''' FII_ATTRIBUTE12,
1595 '''||l_url_4||''' FII_ATTRIBUTE13
1596
1597 FROM
1598 (
1599 SELECT
1600 g.FII_MEASURE1 FII_MEASURE1,
1601 g.FII_MEASURE2 FII_MEASURE2,
1602 g.FII_MEASURE3 FII_MEASURE3,
1603 g.FII_MEASURE4 FII_MEASURE4,
1604 g.FII_MEASURE5 FII_MEASURE5,
1605 g.FII_MEASURE6 FII_MEASURE6,
1606 g.FII_MEASURE7 FII_MEASURE7,
1607 g.FII_MEASURE8 FII_MEASURE8,
1608 g.FII_MEASURE9 FII_MEASURE9,
1609 g.FII_MEASURE10 FII_MEASURE10,
1610 g.FII_MEASURE11 FII_MEASURE11,
1611 g.FII_MEASURE12 FII_MEASURE12,
1612 g.FII_MEASURE13 FII_MEASURE13,
1613 g.FII_MEASURE14 FII_MEASURE14,
1614 g.FII_MEASURE15 FII_MEASURE15,
1615 g.FII_MEASURE16 FII_MEASURE16,
1616 g.FII_MEASURE17 FII_MEASURE17,
1617 g.FII_MEASURE18 FII_MEASURE18,
1618 g.FII_MEASURE21 FII_MEASURE21,
1619 g.FII_MEASURE22 FII_MEASURE22,
1620 g.FII_MEASURE23 FII_MEASURE23,
1621 g.FII_MEASURE24 FII_MEASURE24,
1622 g.FII_MEASURE25 FII_MEASURE25,
1623 g.FII_MEASURE26 FII_MEASURE26,
1624 g.FII_MEASURE27 FII_MEASURE27,
1625 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
1626 FROM
1627
1628 (SELECT f.invoice_number FII_MEASURE1,
1629 f.invoice_id FII_MEASURE2,
1630 f.invoice_type FII_MEASURE3,
1631 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
1632 f.entered_date FII_MEASURE5, -- Bug #4266826
1633 MIN(fpay.due_date) FII_MEASURE6, -- Bug #4266826
1634 f.invoice_currency_code FII_MEASURE7,
1635 f.invoice_amount FII_MEASURE8,
1636 f.'||l_invoice_amt_col||' FII_MEASURE9,
1637 sum(fpay.'||l_unpaid_amt_col||') FII_MEASURE10,
1638 decode(nvl(hold.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
1642 SUM(fpay.'||l_discount_lost||') FII_MEASURE15,
1639 nvl(hold1.FII_MEASURE12,0) FII_MEASURE12,
1640 f.'||l_discount_offered||' FII_MEASURE13,
1641 SUM(fpay.'||l_discount_taken||') FII_MEASURE14,
1643 SUM(fpay.'||l_discount_available||') FII_MEASURE16,
1644 t.name FII_MEASURE17,
1645 f.source FII_MEASURE18,
1646 to_number(null) FII_MEASURE21,
1647 SUM(f.'||l_invoice_amt_col||') OVER() FII_MEASURE22,
1648 SUM(sum(fpay.'||l_unpaid_amt_col||')) OVER() FII_MEASURE23,
1649 SUM(f.'||l_discount_offered||') OVER() FII_MEASURE24,
1650 SUM(SUM(fpay.'||l_discount_taken||')) OVER() FII_MEASURE25,
1651 SUM(SUM(fpay.'||l_discount_lost||')) OVER() FII_MEASURE26,
1652 SUM(SUM(fpay.'||l_discount_available||')) OVER() FII_MEASURE27
1653 FROM fii_ap_invoice_b f,
1654 fii_ap_pay_sched_b fpay,
1655 ap_terms_tl t,
1656 (
1657 SELECT f.invoice_id,
1658 ''Y'' FII_MEASURE11
1659 FROM fii_ap_inv_holds_b f
1660 WHERE ( f.release_date > &BIS_CURRENT_ASOF_DATE OR f.release_date IS NULL)
1661 AND f.hold_date <= &BIS_CURRENT_ASOF_DATE
1662 '||l_org_where||' '||l_sup_where|| '
1663 GROUP BY invoice_id
1664 ) hold,
1665 ( SELECT f.invoice_id,
1666 SUM(days_on_hold) FII_MEASURE12
1667 FROM fii_ap_hhist_ib_mv f
1668 WHERE 1 = 1
1669 '||l_org_where||' '||l_sup_where|| '
1670 GROUP BY invoice_id
1671 ) hold1
1672 WHERE (&BIS_CURRENT_ASOF_DATE - fpay.due_date) > 30
1673 AND f.entered_Date<=&BIS_CURRENT_ASOF_DATE /*added for bug no.3054524*/
1674 AND f.cancel_flag = ''N''
1675 AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added for bug no.3114633*/
1676 AND fpay.action <> ''PREPAYMENT''
1677 '||l_org_where||' '||l_sup_where||'
1678 AND t.term_id = f.terms_id
1679 AND t.language = userenv(''LANG'')
1680 AND (f.fully_paid_date > &BIS_CURRENT_ASOF_DATE OR f.fully_paid_date IS NULL)
1681 AND f.invoice_id = fpay.invoice_id
1682 AND f.invoice_id =hold.invoice_id (+)
1683 AND f.invoice_id =hold1.invoice_id (+)
1684 HAVING SUM(fpay.amount_remaining) <> 0 /* bug # 3191403*/
1685 GROUP BY f.invoice_number,
1686 f.invoice_id,
1687 f.invoice_type,
1688 f.invoice_date,
1689 f.entered_date,
1690 f.invoice_currency_code,
1691 f.invoice_amount,
1692 f.'||l_invoice_amt_col||',
1693 hold.FII_MEASURE11,
1694 hold1.FII_MEASURE12,
1695 f.'||l_discount_offered||',
1696 t.name,
1697 f.source) g ) h
1698 WHERE (rnk BETWEEN &START_INDEX AND &END_INDEX or &END_INDEX = -1)
1699 &ORDER_BY_CLAUSE
1700 ';
1701
1702 ELSE -- dummy code
1703 sqlstmt := 'Invalid report source';
1704
1705 END CASE;
1706
1707 -- Attach bind parameters
1708 FII_PMV_UTIL.bind_variable(
1709 p_sqlstmt=>sqlstmt,
1710 p_page_parameter_tbl=>p_page_parameter_tbl,
1711 p_sql_output=>inv_dtl_sql,
1712 p_bind_output_table=>inv_dtl_output);
1713 -- ,p_record_type_id=>l_record_type_id);
1714
1715 END get_inv_detail;
1716
1717
1718
1719 END FII_AP_INV_DETAIL;
1720