[Home] [Help]
PACKAGE BODY: APPS.FII_AP_INV_ACTIVITY_DETAIL
Source
1 PACKAGE BODY FII_AP_INV_ACTIVITY_DETAIL AS
2 /* $Header: FIIAPD4B.pls 120.3 2005/10/12 20:10:20 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 -- For the Invoice Activity Detail report --
24 PROCEDURE get_inv_activity_detail (
25 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
26 inv_dtl_sql OUT NOCOPY VARCHAR2,
27 inv_dtl_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
28 IS
29 i NUMBER;
30 l_viewby_dim VARCHAR2(240); -- what is the viewby
31 l_as_of_date DATE;
32 l_organization VARCHAR2(240);
33 l_supplier VARCHAR2(240);
34 l_currency VARCHAR2(240); -- random size, is possibly high
35 l_viewby_id VARCHAR2(240); -- org_id or supplier_id
36 l_viewby_string VARCHAR2(240);
37 l_record_type_id NUMBER; -- only possible value is 1143
38 l_curr_suffix VARCHAR2(240);
39 l_org_where VARCHAR2(240);
40 l_sup_where VARCHAR2(240);
41 base_org_where VARCHAR2(240);
42 base_sup_where VARCHAR2(240);
43 l_gid NUMBER;
44 sqlstmt VARCHAR2(14000);
45 l_period_type VARCHAR2(240);
46 l_invoice_number VARCHAR2(240);
47 l_report_source VARCHAR2(240);
48 l_discount_offered VARCHAR2(240);
49 l_period_start DATE;
50 l_check_id NUMBER := 0;
51 l_url_1 VARCHAR2(1000);
52 l_url_2 VARCHAR2(1000);
53 l_url_3 VARCHAR2(1000);
54 l_url_4 VARCHAR2(1000);
55 l_yes VARCHAR2(240);
56 l_no VARCHAR2(240);
57 l_date_mask VARCHAR2(240);
58 l_days_into_period NUMBER;
59 l_cur_period NUMBER;
60 l_id_column VARCHAR2(240);
61 l_invoice_amount VARCHAR2(240);
62 l_payment_amount VARCHAR2(240);
63 l_sysdate VARCHAR2(30);
64 BEGIN
65
66 -- Read the parameters passed
67 FII_PMV_UTIL.get_parameters(
68 p_page_parameter_tbl=>p_page_parameter_tbl,
69 p_as_of_date=>l_as_of_date,
70 p_operating_unit=>l_organization,
71 p_supplier=>l_supplier,
72 p_invoice_number=>l_invoice_number,
73 p_period_type=>l_period_type,
74 p_record_type_id=>l_record_type_id,
75 p_view_by=>l_viewby_dim,
76 p_currency=>l_curr_suffix,
77 p_column_name=>l_viewby_id,
78 p_table_name=>l_viewby_string,
79 p_gid=>l_gid,
80 p_org_where=>l_org_where,
81 p_supplier_where=>l_sup_where);
82
83
84 FII_PMV_UTIL.get_report_source (
85 p_page_parameter_tbl=>p_page_parameter_tbl,
86 p_report_source=>l_report_source);
87
88 l_discount_offered := FII_PMV_UTIL.get_base_curr_colname(l_curr_suffix, 'discount_offered');
89
90 --Added for Bug 4309974
91 SELECT TO_CHAR(TRUNC(sysdate),'DD/MM/YYYY') INTO l_sysdate FROM dual;
92
93 FII_PMV_UTIL.get_check_id(p_page_parameter_tbl, l_check_id);
94
95 --Modified for Bug 4309974
96 -- l_url_1 := 'pFunctionName=FII_AP_INV_ACTIVITY_HISTORY&pParamIds=Y&FII_INVOICE_ID=FII_MEASURE2&FII_INVOICE=FII_MEASURE1';
97 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';
98
99 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';
100 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';
101 -- 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';
102
103 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';
104
105 FII_PMV_Util.get_yes_no_msg(l_yes, l_no);
106 FII_PMV_Util.get_format_mask(l_date_mask);
107 FII_PMV_Util.get_period_strt(p_page_parameter_tbl,
108 l_period_start,
109 l_days_into_period,
110 l_cur_period,
111 l_id_column );
112
113
114 /*--------------------------------------------------------------+
115 | FII_MEASURE1 - Invoice Number |
116 | FII_MEASURE2 - Invoice ID |
117 | FII_MEASURE3 - Invoice Type |
118 | FII_MEASURE4 - Invoice Date |
119 | FII_MEASURE5 - Entered Date |
120 | FII_MEASURE6 - Due Date |
121 | FII_MEASURE7 - Transaction Currency Code |
122 | FII_MEASURE8 - Transaction invoice Amount |
123 | FII_MEASURE9 - Invoice Amount |
124 | FII_MEASURE10 - Unpaid Amount |
125 | FII_MEASURE11 - Ever On Hold |
126 | FII_MEASURE12 - Number of Holds Placed |
127 | FII_MEASURE13 - Days on Hold |
128 | FII_MEASURE14 - Distribution Lines |
129 | FII_MEASURE15 - Terms |
130 | FII_MEASURE16 - Source |
131 | FII_MEASURE18 - Check ID |
132 | FII_MEASURE[21-27] - GrAND Total columns |
133 +--------------------------------------------------------------*/
134
135 l_payment_amount := FII_PMV_Util.get_base_curr_colname(l_curr_suffix, 'payment_amount');
136
137
138 If l_curr_suffix = '_prim_g' then
139 l_invoice_amount := 'prim_amount';
140 Elsif l_curr_suffix = '_sec_g' then
141 l_invoice_amount := 'sec_amount';
142 Elsif l_curr_suffix = '_b' then
143 l_invoice_amount := 'base_amount';
144 End if;
145
146 /* Added code for bug number 3423861. It is for the case when a prepayment
147 invoice is entered and an action takes place on the invoice in future date
148 eg payment. As the action is recorded in fii_ap_pay_sched_b at a future date,
149 we need to display the invoice taking care about not displaying the affected
150 columns like paid amount in the future date . For this we use a decode and
151 nvl in the select statement to filter it out . To enable the rows to be selected
152 we add a condition in WHERE clause "or (base.action = 'PREPAYMENT') ".
153 Made changes in foll. reports
154 1. Manual Invoice Entered Detail
155 2. Electronic Invoice Entered Detail
156 3. XML Invoices Entered Detail
157 4. EDI Invoices Entered Detail
158 5. ASBN Invoices Entered Detail
159 6. ISP Invoices Entered Detail
160 7. Other Integrated Invoices Entered Detail
161 8. ERS Invoices Entered Detail
162 9. Prepayment invoices entered detail
163 10.Invoices Entered Detail
164 */
165
166
167 /* Changes made during Performance tuning + enhancement for Days on Hold
168
169 1. Removed period_type_id = 1 from all the reports
170 2. Implemented the Start and End index logic to support Windowed mode for all reports
171 3. Using fii_ap_inv_holds_b to return count of holds in the period in all reports
172 4. Using fii_ap_hhist_ib_mv to return days on hold and Ever on Hold in all reports
173 5. Removed the release condition from all the reports as release date as no role in
174 either days on hold,or Ever on Hold or Count ( in all reports)
175 6. Changed the driving tables to maximise the use of index (driving table now is fii_ap_invoice_b)
176 7. Cleaned up the commented part of code not being used (as fix for some bug) eg.
177 Removed the commented line
178 sum(base.base_amount) over() FII_MEASURE21 ,
179 8. Made use of the same Measure name as being passed to AK in the inner query
180 9. Moved the urls to top level .
181 10. Removed the check_id from the group by clause as bug 3063385 was not generic.
182 11. In AK for FII_AP_INV_ACTIVITY_DETAIL,changed the no.of display rows to -30 and
183 Number of rows per portlet to -10
184
185 */
186
187 -- Construct the query to be sent
188
189
190
191 CASE l_report_source
192
193 WHEN 'FII_AP_INV_ENT_DTL' THEN -- 1st, Invoices Entered Detail
194 sqlstmt := '
195 Select
196 h.FII_MEASURE1 FII_MEASURE1,
197 h.FII_MEASURE2 FII_MEASURE2,
198 h.FII_MEASURE3 FII_MEASURE3,
199 h.FII_MEASURE4 FII_MEASURE4,
200 h.FII_MEASURE5 FII_MEASURE5,
201 h.FII_MEASURE6 FII_MEASURE6,
202 h.FII_MEASURE7 FII_MEASURE7,
203 h.FII_MEASURE8 FII_MEASURE8,
204 h.FII_MEASURE9 FII_MEASURE9,
205 h.FII_MEASURE10 FII_MEASURE10,
206 h.FII_MEASURE11 FII_MEASURE11,
207 h.FII_MEASURE12 FII_MEASURE12,
208 h.FII_MEASURE13 FII_MEASURE13,
209 h.FII_MEASURE14 FII_MEASURE14,
210 h.FII_MEASURE15 FII_MEASURE15,
211 h.FII_MEASURE16 FII_MEASURE16,
212 h.FII_MEASURE18 FII_MEASURE18,
213 h.FII_MEASURE21 FII_MEASURE21,
214 h.FII_MEASURE22 FII_MEASURE22,
215 h.FII_MEASURE23 FII_MEASURE23,
216 h.FII_MEASURE24 FII_MEASURE24,
217 h.FII_MEASURE25 FII_MEASURE25,
218 h.FII_MEASURE26 FII_MEASURE26,
219 '''||l_url_1||''' FII_ATTRIBUTE10,
220 '''||l_url_2||''' FII_ATTRIBUTE11,
221 '''||l_url_4||''' FII_ATTRIBUTE12, /* Bug 3044407: Swapped the URLs of */
222 '''||l_url_3||''' FII_ATTRIBUTE13 /* FII_ATTRIBUTE12 and FII_ATTRIBUTE13 */
223
224 FROM
225 (
226 SELECT
227 g.FII_MEASURE1 FII_MEASURE1,
228 g.FII_MEASURE2 FII_MEASURE2,
229 g.FII_MEASURE3 FII_MEASURE3,
230 g.FII_MEASURE4 FII_MEASURE4,
231 g.FII_MEASURE5 FII_MEASURE5,
232 g.FII_MEASURE6 FII_MEASURE6,
233 g.FII_MEASURE7 FII_MEASURE7,
234 g.FII_MEASURE8 FII_MEASURE8,
235 g.FII_MEASURE9 FII_MEASURE9,
236 g.FII_MEASURE10 FII_MEASURE10,
237 g.FII_MEASURE11 FII_MEASURE11,
238 g.FII_MEASURE12 FII_MEASURE12,
239 g.FII_MEASURE13 FII_MEASURE13,
240 g.FII_MEASURE14 FII_MEASURE14,
241 g.FII_MEASURE15 FII_MEASURE15,
242 g.FII_MEASURE16 FII_MEASURE16,
243 g.FII_MEASURE18 FII_MEASURE18,
244 g.FII_MEASURE21 FII_MEASURE21,
245 g.FII_MEASURE22 FII_MEASURE22,
246 g.FII_MEASURE23 FII_MEASURE23,
247 g.FII_MEASURE24 FII_MEASURE24,
248 g.FII_MEASURE25 FII_MEASURE25,
249 g.FII_MEASURE26 FII_MEASURE26,
250 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
251 FROM
252
253 (SELECT f.invoice_number FII_MEASURE1,
254 f.invoice_id FII_MEASURE2,
255 f.invoice_type FII_MEASURE3,
256 /* Bug 3583140. Removed to_char statement */
257 f.invoice_date FII_MEASURE4,
258 f.entered_date FII_MEASURE5,
259 MIN(f.due_Date) FII_MEASURE6,
260 f.invoice_currency_code FII_MEASURE7,
261 f.invoice_amount FII_MEASURE8,
262 f.'||l_invoice_amount||' FII_MEASURE9,
263 sum(decode(base.action,''PREPAYMENT'',decode(sign(base.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
264 nvl(base.'||l_payment_amount||',0)),nvl(base.'||l_payment_amount||' ,0))) FII_MEASURE10, /*changes for bug 3423861*/
265 decode(nvl(hold1.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
266 nvl(hold.FII_MEASURE12,0) FII_MEASURE12,
267 nvl(hold1.FII_MEASURE13,0) FII_MEASURE13,
268 f.dist_count FII_MEASURE14,
269 t.name FII_MEASURE15,
270 f.source FII_MEASURE16,
271 max(base.check_id) FII_MEASURE18,
272 to_number(null) FII_MEASURE21,
273 sum(f.'||l_invoice_amount||') over() FII_MEASURE22,
274 sum(sum(decode(base.action,''PREPAYMENT'',decode(sign(base.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
275 nvl(base.'||l_payment_amount||',0)),nvl(base.'||l_payment_amount||' ,0)))) over() FII_MEASURE23, /*changes for bug 3423861*/
276 sum(nvl(hold.FII_MEASURE12,0)) over() FII_MEASURE24,
277 sum(nvl(hold1.FII_MEASURE13,0)) over() FII_MEASURE25,
278 sum(f.dist_count) over() FII_MEASURE26
279 FROM fii_ap_invoice_b f,
280 fii_ap_pay_sched_b base,
281 ap_terms_tl t,
282 (SELECT invoice_id,
283 COUNT(hold_date) FII_MEASURE12
284 FROM fii_ap_inv_holds_b f
285 WHERE hold_date BETWEEN :PERIOD_START AND &BIS_CURRENT_ASOF_DATE
286 '||l_org_where||' '||l_sup_where||'
287 GROUP BY invoice_id
288 ) hold,
289 (SELECT invoice_id,
290 ''Y'' FII_MEASURE11,
291 sum(days_on_hold) FII_MEASURE13
292 FROM fii_ap_hhist_ib_mv f
293 WHERE 1 =1
294 '||l_org_where||' '||l_sup_where||'
295 GROUP BY invoice_id
296 ) hold1
297 WHERE
298 f.entered_date <= &BIS_CURRENT_ASOF_DATE
299 '||l_org_where||' '||l_sup_where||'
300 AND (nvl(base.action_Date,&BIS_CURRENT_ASOF_DATE) <= &BIS_CURRENT_ASOF_DATE
301 or (base.action = ''PREPAYMENT'') ) /* made changes for bug 3423861*/
302 /*added code for bug no.3113879*/
303 AND f.entered_date >= :PERIOD_START
304 AND f.cancel_flag = ''N'' /*Added code for bug. no.3084280 */
305 AND base.invoice_id (+)= f.invoice_id /*added outer join and the decode condition above for bug no.3063372*/
306 AND decode(base.supplier_id,null,1,decode(base.supplier_id,f.supplier_id,1,0))=1
307 AND decode(base.org_id,null,1,decode(base.org_id,f.org_id,1,0))=1
308 AND t.term_id = f.terms_id
309 AND t.language = userenv(''LANG'')
310 AND f.invoice_id =hold.invoice_id (+)
311 AND f.invoice_id =hold1.invoice_id (+)
312 GROUP BY f.invoice_number,
313 f.invoice_id,
314 f.invoice_type,
315 f.invoice_date,
316 f.entered_date,
317 f.invoice_currency_code,
318 f.invoice_amount,
319 f.'||l_invoice_amount||',
320 hold1.FII_MEASURE11,
321 hold1.FII_MEASURE13,
322 f.'||l_discount_offered||',
323 hold.FII_MEASURE12,
324 f.dist_count,
325 t.name,
326 f.source) g ) h
327 WHERE (rnk between &START_INDEX AND &END_INDEX or &END_INDEX = -1)
328 &ORDER_BY_CLAUSE
329 ';
330
331
332 WHEN 'FII_AP_MANUAL_INV_ENT_DTL' THEN -- 2nd, Manual Invoices Entered Detail
333 sqlstmt := '
334 Select
335 h.FII_MEASURE1 FII_MEASURE1,
336 h.FII_MEASURE2 FII_MEASURE2,
337 h.FII_MEASURE3 FII_MEASURE3,
338 h.FII_MEASURE4 FII_MEASURE4,
339 h.FII_MEASURE5 FII_MEASURE5,
340 h.FII_MEASURE6 FII_MEASURE6,
341 h.FII_MEASURE7 FII_MEASURE7,
342 h.FII_MEASURE8 FII_MEASURE8,
343 h.FII_MEASURE9 FII_MEASURE9,
344 h.FII_MEASURE10 FII_MEASURE10,
345 h.FII_MEASURE11 FII_MEASURE11,
346 h.FII_MEASURE12 FII_MEASURE12,
347 h.FII_MEASURE13 FII_MEASURE13,
348 h.FII_MEASURE14 FII_MEASURE14,
349 h.FII_MEASURE15 FII_MEASURE15,
350 h.FII_MEASURE16 FII_MEASURE16,
351 h.FII_MEASURE18 FII_MEASURE18,
352 h.FII_MEASURE21 FII_MEASURE21,
353 h.FII_MEASURE22 FII_MEASURE22,
354 h.FII_MEASURE23 FII_MEASURE23,
355 h.FII_MEASURE24 FII_MEASURE24,
356 h.FII_MEASURE25 FII_MEASURE25,
357 h.FII_MEASURE26 FII_MEASURE26,
358 '''||l_url_1||''' FII_ATTRIBUTE10,
359 '''||l_url_2||''' FII_ATTRIBUTE11,
360 '''||l_url_4||''' FII_ATTRIBUTE12,
361 '''||l_url_3||''' FII_ATTRIBUTE13
362
363 FROM
364 (
365 SELECT
366 g.FII_MEASURE1 FII_MEASURE1,
367 g.FII_MEASURE2 FII_MEASURE2,
368 g.FII_MEASURE3 FII_MEASURE3,
369 g.FII_MEASURE4 FII_MEASURE4,
370 g.FII_MEASURE5 FII_MEASURE5,
371 g.FII_MEASURE6 FII_MEASURE6,
372 g.FII_MEASURE7 FII_MEASURE7,
373 g.FII_MEASURE8 FII_MEASURE8,
374 g.FII_MEASURE9 FII_MEASURE9,
375 g.FII_MEASURE10 FII_MEASURE10,
376 g.FII_MEASURE11 FII_MEASURE11,
377 g.FII_MEASURE12 FII_MEASURE12,
378 g.FII_MEASURE13 FII_MEASURE13,
379 g.FII_MEASURE14 FII_MEASURE14,
380 g.FII_MEASURE15 FII_MEASURE15,
381 g.FII_MEASURE16 FII_MEASURE16,
382 g.FII_MEASURE18 FII_MEASURE18,
383 g.FII_MEASURE21 FII_MEASURE21,
384 g.FII_MEASURE22 FII_MEASURE22,
385 g.FII_MEASURE23 FII_MEASURE23,
386 g.FII_MEASURE24 FII_MEASURE24,
387 g.FII_MEASURE25 FII_MEASURE25,
388 g.FII_MEASURE26 FII_MEASURE26,
389 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
390 FROM
391
392 (
393 SELECT f.invoice_number FII_MEASURE1,
394 f.invoice_id FII_MEASURE2,
395 f.invoice_type FII_MEASURE3,
396 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
397 to_char(f.entered_date, '''||l_date_mask||''') FII_MEASURE5,
398 to_char(MIN(f.due_Date), '''||l_date_mask||''') FII_MEASURE6,
399 f.invoice_currency_code FII_MEASURE7,
400 f.invoice_amount FII_MEASURE8,
401 f.'||l_invoice_amount||' FII_MEASURE9,
402 sum(decode(base.action,''PREPAYMENT'',decode(sign(base.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
403 nvl(base.'||l_payment_amount||',0)),nvl(base.'||l_payment_amount||' ,0))) FII_MEASURE10, /*changes for bug 3423861*/
404 decode(nvl(hold1.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
405 nvl(hold.FII_MEASURE12,0) FII_MEASURE12,
406 nvl(hold1.FII_MEASURE13,0) FII_MEASURE13,
407 f.dist_count FII_MEASURE14,
408 t.name FII_MEASURE15,
409 f.source FII_MEASURE16,
410 max(base.check_id) FII_MEASURE18,
411 to_number(null) FII_MEASURE21,
412 sum(f.'||l_invoice_amount||') over() FII_MEASURE22,
413 sum(sum(decode(base.action,''PREPAYMENT'',decode(sign(base.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
414 nvl(base.'||l_payment_amount||',0)),nvl(base.'||l_payment_amount||' ,0)))) over() FII_MEASURE23, /*changes for bug 3423861*/
415 sum(nvl(hold.FII_MEASURE12,0)) over() FII_MEASURE24,
416 sum(nvl(hold1.FII_MEASURE13,0)) over() FII_MEASURE25,
417 sum(f.dist_count) over() FII_MEASURE26
418 FROM fii_ap_invoice_b f,
419 fii_ap_pay_sched_b base,
420 ap_terms_tl t,
421 (SELECT invoice_id,
422 COUNT(hold_date) FII_MEASURE12
423 FROM fii_ap_inv_holds_b f
424 WHERE hold_date BETWEEN :PERIOD_START AND &BIS_CURRENT_ASOF_DATE
425 '||l_org_where||' '||l_sup_where||'
426 GROUP BY invoice_id
427 ) hold,
428 (SELECT invoice_id,
429 ''Y'' FII_MEASURE11,
430 sum(days_on_hold) FII_MEASURE13
431 FROM fii_ap_hhist_ib_mv f
432 WHERE 1 =1
433 '||l_org_where||' '||l_sup_where||'
434 GROUP BY invoice_id
435 ) hold1
436 WHERE
437 f.e_invoices_flag=''N''
438 '||l_org_where||' '||l_sup_where||'
439 AND f.entered_date <= &BIS_CURRENT_ASOF_DATE
440 AND f.entered_date >= :PERIOD_START
441 AND (nvl(base.action_Date,&BIS_CURRENT_ASOF_DATE) <= &BIS_CURRENT_ASOF_DATE
442 OR (base.action = ''PREPAYMENT'') ) /* made changes for bug 3423861*/
443 /* added code for bug no.3113879 */
444 AND f.cancel_flag = ''N'' /* Added code for bug. no.3084280 */
445 AND base.invoice_id (+)= f.invoice_id /* added outer join and the decode condition above for bug no.3063372 */
446 AND decode(base.supplier_id,null,1,decode(base.supplier_id,f.supplier_id,1,0))=1
447 AND decode(base.org_id,null,1,decode(base.org_id,f.org_id,1,0))=1
448 AND t.term_id = f.terms_id
449 AND t.language = userenv(''LANG'')
450 AND f.invoice_id =hold.invoice_id (+)
451 AND f.invoice_id =hold1.invoice_id (+)
452 GROUP BY f.invoice_number,
453 f.invoice_id,
454 f.invoice_type,
455 f.invoice_date,
456 f.entered_date,
457 f.invoice_currency_code,
458 f.invoice_amount,
459 f.'||l_invoice_amount||',
460 hold1.FII_MEASURE11,
461 hold1.FII_MEASURE13,
462 f.'||l_discount_offered||',
463 hold.FII_MEASURE12,
464 f.dist_count,
465 t.name,
466 f.source) g ) h
467 WHERE (rnk between &START_INDEX AND &END_INDEX or &END_INDEX = -1)
468 &ORDER_BY_CLAUSE
469 ';
470
471 WHEN 'FII_AP_E_INV_ENT_DTL' THEN -- 3rd, Electronic Invoices Entered Detail
472 sqlstmt := '
473 Select
474 h.FII_MEASURE1 FII_MEASURE1,
475 h.FII_MEASURE2 FII_MEASURE2,
476 h.FII_MEASURE3 FII_MEASURE3,
477 h.FII_MEASURE4 FII_MEASURE4,
478 h.FII_MEASURE5 FII_MEASURE5,
479 h.FII_MEASURE6 FII_MEASURE6,
480 h.FII_MEASURE7 FII_MEASURE7,
481 h.FII_MEASURE8 FII_MEASURE8,
482 h.FII_MEASURE9 FII_MEASURE9,
483 h.FII_MEASURE10 FII_MEASURE10,
484 h.FII_MEASURE11 FII_MEASURE11,
485 h.FII_MEASURE12 FII_MEASURE12,
486 h.FII_MEASURE13 FII_MEASURE13,
487 h.FII_MEASURE14 FII_MEASURE14,
488 h.FII_MEASURE15 FII_MEASURE15,
489 h.FII_MEASURE16 FII_MEASURE16,
490 h.FII_MEASURE18 FII_MEASURE18,
491 h.FII_MEASURE21 FII_MEASURE21,
492 h.FII_MEASURE22 FII_MEASURE22,
493 h.FII_MEASURE23 FII_MEASURE23,
494 h.FII_MEASURE24 FII_MEASURE24,
495 h.FII_MEASURE25 FII_MEASURE25,
496 h.FII_MEASURE26 FII_MEASURE26,
497 '''||l_url_1||''' FII_ATTRIBUTE10,
498 '''||l_url_2||''' FII_ATTRIBUTE11,
499 '''||l_url_4||''' FII_ATTRIBUTE12,
500 '''||l_url_3||''' FII_ATTRIBUTE13
501
502 FROM
503 (
504 SELECT
505 g.FII_MEASURE1 FII_MEASURE1,
506 g.FII_MEASURE2 FII_MEASURE2,
507 g.FII_MEASURE3 FII_MEASURE3,
508 g.FII_MEASURE4 FII_MEASURE4,
509 g.FII_MEASURE5 FII_MEASURE5,
510 g.FII_MEASURE6 FII_MEASURE6,
511 g.FII_MEASURE7 FII_MEASURE7,
512 g.FII_MEASURE8 FII_MEASURE8,
513 g.FII_MEASURE9 FII_MEASURE9,
514 g.FII_MEASURE10 FII_MEASURE10,
515 g.FII_MEASURE11 FII_MEASURE11,
516 g.FII_MEASURE12 FII_MEASURE12,
517 g.FII_MEASURE13 FII_MEASURE13,
518 g.FII_MEASURE14 FII_MEASURE14,
519 g.FII_MEASURE15 FII_MEASURE15,
520 g.FII_MEASURE16 FII_MEASURE16,
521 g.FII_MEASURE18 FII_MEASURE18,
522 g.FII_MEASURE21 FII_MEASURE21,
523 g.FII_MEASURE22 FII_MEASURE22,
524 g.FII_MEASURE23 FII_MEASURE23,
525 g.FII_MEASURE24 FII_MEASURE24,
526 g.FII_MEASURE25 FII_MEASURE25,
527 g.FII_MEASURE26 FII_MEASURE26,
528 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
529 FROM
530
531 (
532 SELECT f.invoice_number FII_MEASURE1,
533 f.invoice_id FII_MEASURE2,
534 f.invoice_type FII_MEASURE3,
535 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
536 to_char(f.entered_date, '''||l_date_mask||''') FII_MEASURE5,
537 to_char(MIN(f.due_Date), '''||l_date_mask||''') FII_MEASURE6,
538 f.invoice_currency_code FII_MEASURE7,
539 f.invoice_amount FII_MEASURE8,
540 f.'||l_invoice_amount||' FII_MEASURE9,
541 sum(decode(base.action,''PREPAYMENT'',decode(sign(base.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
542 nvl(base.'||l_payment_amount||',0)),nvl(base.'||l_payment_amount||' ,0))) FII_MEASURE10, /*changes for bug 3423861*/
543 decode(nvl(hold1.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
544 nvl(hold.FII_MEASURE12,0) FII_MEASURE12,
545 nvl(hold1.FII_MEASURE13,0) FII_MEASURE13,
546 f.dist_count FII_MEASURE14,
547 t.name FII_MEASURE15,
548 f.source FII_MEASURE16,
549 max(base.check_id) FII_MEASURE18,
550 to_number(null) FII_MEASURE21,
551 sum(f.'||l_invoice_amount||') over() FII_MEASURE22,
552 sum(sum(decode(base.action,''PREPAYMENT'',decode(sign(base.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
553 nvl(base.'||l_payment_amount||',0)),nvl(base.'||l_payment_amount||' ,0)))) over() FII_MEASURE23, /*changes for bug 3423861*/
554 sum(nvl(hold.FII_MEASURE12,0)) over() FII_MEASURE24,
555 sum(nvl(hold1.FII_MEASURE13,0)) over() FII_MEASURE25,
556 sum(f.dist_count) over() FII_MEASURE26
557 FROM fii_ap_invoice_b f,
558 fii_ap_pay_sched_b base,
559 ap_terms_tl t,
560 (SELECT invoice_id,
561 COUNT(hold_date) FII_MEASURE12
562 FROM fii_ap_inv_holds_b f
563 WHERE hold_date BETWEEN :PERIOD_START AND &BIS_CURRENT_ASOF_DATE
564 '||l_org_where||' '||l_sup_where||'
565 GROUP BY invoice_id
566 ) hold,
567 (SELECT invoice_id,
568 ''Y'' FII_MEASURE11,
569 sum(days_on_hold) FII_MEASURE13
570 FROM fii_ap_hhist_ib_mv f
571 WHERE 1 =1
572 '||l_org_where||' '||l_sup_where||'
573 GROUP BY invoice_id
574 ) hold1
575 WHERE f.e_invoices_flag=''Y''
576 '||l_org_where||' '||l_sup_where||'
577 AND f.entered_date <= &BIS_CURRENT_ASOF_DATE
578 AND f.entered_date >= :PERIOD_START
579 AND (nvl(base.action_Date,&BIS_CURRENT_ASOF_DATE) <= &BIS_CURRENT_ASOF_DATE /*added code for bug no.3113879*/
580 OR (base.action = ''PREPAYMENT'') ) /* made changes for bug 3423861*/
581 AND f.cancel_flag = ''N'' /*Added code for bug. no.3084280 */
582 AND base.invoice_id (+)= f.invoice_id /*added outer join and the decode condition above for bug no.3063372*/
583 AND decode(base.supplier_id,null,1,decode(base.supplier_id,f.supplier_id,1,0))=1
584 AND decode(base.org_id,null,1,decode(base.org_id,f.org_id,1,0))=1
585 AND t.term_id = f.terms_id
586 AND t.language = userenv(''LANG'')
587 AND f.invoice_id =hold.invoice_id (+)
588 AND f.invoice_id =hold1.invoice_id (+)
589 GROUP BY f.invoice_number,
590 f.invoice_id,
591 f.invoice_type,
592 f.invoice_date,
593 f.entered_date,
594 f.invoice_currency_code,
595 f.invoice_amount,
596 f.'||l_invoice_amount||',
597 hold1.FII_MEASURE11,
598 hold1.FII_MEASURE13,
599 f.'||l_discount_offered||',
600 hold.FII_MEASURE12,
601 f.dist_count,
602 t.name,
603 f.source) g ) h
604 WHERE (rnk between &START_INDEX AND &END_INDEX or &END_INDEX = -1)
605 &ORDER_BY_CLAUSE
606 ';
607
608 WHEN 'FII_AP_XML_INV_ENT_DTL' THEN -- 4th, XML Invoices Entered Detail
609 sqlstmt := '
610 Select
611 h.FII_MEASURE1 FII_MEASURE1,
612 h.FII_MEASURE2 FII_MEASURE2,
613 h.FII_MEASURE3 FII_MEASURE3,
614 h.FII_MEASURE4 FII_MEASURE4,
615 h.FII_MEASURE5 FII_MEASURE5,
616 h.FII_MEASURE6 FII_MEASURE6,
617 h.FII_MEASURE7 FII_MEASURE7,
618 h.FII_MEASURE8 FII_MEASURE8,
619 h.FII_MEASURE9 FII_MEASURE9,
620 h.FII_MEASURE10 FII_MEASURE10,
621 h.FII_MEASURE11 FII_MEASURE11,
622 h.FII_MEASURE12 FII_MEASURE12,
623 h.FII_MEASURE13 FII_MEASURE13,
624 h.FII_MEASURE14 FII_MEASURE14,
625 h.FII_MEASURE15 FII_MEASURE15,
626 h.FII_MEASURE16 FII_MEASURE16,
627 h.FII_MEASURE18 FII_MEASURE18,
628 h.FII_MEASURE21 FII_MEASURE21,
629 h.FII_MEASURE22 FII_MEASURE22,
630 h.FII_MEASURE23 FII_MEASURE23,
631 h.FII_MEASURE24 FII_MEASURE24,
632 h.FII_MEASURE25 FII_MEASURE25,
633 h.FII_MEASURE26 FII_MEASURE26,
634 '''||l_url_1||''' FII_ATTRIBUTE10,
635 '''||l_url_2||''' FII_ATTRIBUTE11,
636 '''||l_url_4||''' FII_ATTRIBUTE12,
637 '''||l_url_3||''' FII_ATTRIBUTE13
638
639 FROM
640 (
641 SELECT
642 g.FII_MEASURE1 FII_MEASURE1,
643 g.FII_MEASURE2 FII_MEASURE2,
644 g.FII_MEASURE3 FII_MEASURE3,
645 g.FII_MEASURE4 FII_MEASURE4,
646 g.FII_MEASURE5 FII_MEASURE5,
647 g.FII_MEASURE6 FII_MEASURE6,
648 g.FII_MEASURE7 FII_MEASURE7,
649 g.FII_MEASURE8 FII_MEASURE8,
650 g.FII_MEASURE9 FII_MEASURE9,
651 g.FII_MEASURE10 FII_MEASURE10,
652 g.FII_MEASURE11 FII_MEASURE11,
653 g.FII_MEASURE12 FII_MEASURE12,
654 g.FII_MEASURE13 FII_MEASURE13,
655 g.FII_MEASURE14 FII_MEASURE14,
656 g.FII_MEASURE15 FII_MEASURE15,
657 g.FII_MEASURE16 FII_MEASURE16,
658 g.FII_MEASURE18 FII_MEASURE18,
659 g.FII_MEASURE21 FII_MEASURE21,
660 g.FII_MEASURE22 FII_MEASURE22,
661 g.FII_MEASURE23 FII_MEASURE23,
662 g.FII_MEASURE24 FII_MEASURE24,
663 g.FII_MEASURE25 FII_MEASURE25,
664 g.FII_MEASURE26 FII_MEASURE26,
665 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
666 FROM
667 (
668 SELECT f.invoice_number FII_MEASURE1,
669 f.invoice_id FII_MEASURE2,
670 f.invoice_type FII_MEASURE3,
671 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
672 to_char(f.entered_date, '''||l_date_mask||''') FII_MEASURE5,
673 to_char(MIN(nvl(fpay.due_date, f.due_date)), '''||l_date_mask||''') FII_MEASURE6,
674 f.invoice_currency_code FII_MEASURE7,
675 f.invoice_amount FII_MEASURE8,
676 f.'||l_invoice_amount||' FII_MEASURE9,
677 sum(decode(fpay.action,''PREPAYMENT'',decode(sign(fpay.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
678 nvl(fpay.'||l_payment_amount||',0)),nvl(fpay.'||l_payment_amount||' ,0))) FII_MEASURE10, /*changes for bug 3423861*/
679 decode(nvl(hold1.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
680 nvl(hold.FII_MEASURE12,0) FII_MEASURE12,
681 nvl(hold1.FII_MEASURE13,0) FII_MEASURE13,
682 f.dist_count FII_MEASURE14,
683 t.name FII_MEASURE15,
684 f.source FII_MEASURE16,
685 max(fpay.check_id) FII_MEASURE18,
686 to_number(null) FII_MEASURE21,
687 sum(f.'||l_invoice_amount||') over() FII_MEASURE22,
688 sum(sum(decode(fpay.action,''PREPAYMENT'',decode(sign(fpay.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
689 nvl(fpay.'||l_payment_amount||',0)),nvl(fpay.'||l_payment_amount||' ,0)))) over() FII_MEASURE23, /*changes for bug 3423861*/
690 sum(nvl(hold.FII_MEASURE12,0)) over() FII_MEASURE24,
691 sum(nvl(hold1.FII_MEASURE13,0)) over() FII_MEASURE25,
692 sum(f.dist_count) over() FII_MEASURE26
693 FROM fii_ap_invoice_b f,
694 fii_ap_pay_sched_b fpay,
695 ap_terms_tl t,
696 (SELECT invoice_id,
697 COUNT(hold_date) FII_MEASURE12
698 FROM fii_ap_inv_holds_b f
699 WHERE hold_date BETWEEN :PERIOD_START AND &BIS_CURRENT_ASOF_DATE
700 '||l_org_where||' '||l_sup_where||'
701 GROUP BY invoice_id
702 ) hold,
703 (SELECT invoice_id,
704 ''Y'' FII_MEASURE11,
705 sum(days_on_hold) FII_MEASURE13
706 FROM fii_ap_hhist_ib_mv f
707 WHERE 1 =1
708 '||l_org_where||' '||l_sup_where||'
709 GROUP BY invoice_id
710 ) hold1
711 WHERE f.e_invoices_flag = ''Y''
712 AND f.source = ''XML GATEWAY''
713 '||l_org_where||' '||l_sup_where||'
714 AND f.cancel_flag = ''N'' /*Added code for bug. no.3084280 */
715 AND f.entered_date <= &BIS_CURRENT_ASOF_DATE
716 AND f.entered_date >= :PERIOD_START
717 AND (nvl(fpay.action_date,&BIS_CURRENT_ASOF_DATE) <= &BIS_CURRENT_ASOF_DATE /*added code for bug no.3113879*/
718 OR (fpay.action = ''PREPAYMENT'') ) /* made changes for bug 3423861*/
719 AND f.invoice_id = fpay.invoice_id(+)
720 AND t.term_id = f.terms_id
721 AND t.language = userenv(''LANG'')
722 AND f.invoice_id =hold.invoice_id (+)
723 AND f.invoice_id =hold1.invoice_id (+)
724 GROUP BY f.invoice_number,
725 f.invoice_id,
726 f.invoice_type,
727 f.invoice_date,
728 f.entered_date,
729 f.invoice_currency_code,
730 f.invoice_amount,
731 f.'||l_invoice_amount||',
732 hold1.FII_MEASURE11,
733 hold1.FII_MEASURE13,
734 f.'||l_discount_offered||',
735 hold.FII_MEASURE12,
736 f.dist_count,
737 t.name,
738 f.source) g ) h
739 WHERE (rnk between &START_INDEX AND &END_INDEX or &END_INDEX = -1)
740 &ORDER_BY_CLAUSE
741 ';
742
743
744 WHEN 'FII_AP_EDI_INV_ENT_DTL' THEN -- 5th, EDI Invoices Entered Detail
745 sqlstmt := '
746 Select
747 h.FII_MEASURE1 FII_MEASURE1,
748 h.FII_MEASURE2 FII_MEASURE2,
749 h.FII_MEASURE3 FII_MEASURE3,
750 h.FII_MEASURE4 FII_MEASURE4,
751 h.FII_MEASURE5 FII_MEASURE5,
752 h.FII_MEASURE6 FII_MEASURE6,
753 h.FII_MEASURE7 FII_MEASURE7,
754 h.FII_MEASURE8 FII_MEASURE8,
755 h.FII_MEASURE9 FII_MEASURE9,
756 h.FII_MEASURE10 FII_MEASURE10,
757 h.FII_MEASURE11 FII_MEASURE11,
758 h.FII_MEASURE12 FII_MEASURE12,
759 h.FII_MEASURE13 FII_MEASURE13,
760 h.FII_MEASURE14 FII_MEASURE14,
761 h.FII_MEASURE15 FII_MEASURE15,
762 h.FII_MEASURE16 FII_MEASURE16,
763 h.FII_MEASURE18 FII_MEASURE18,
764 h.FII_MEASURE21 FII_MEASURE21,
765 h.FII_MEASURE22 FII_MEASURE22,
766 h.FII_MEASURE23 FII_MEASURE23,
767 h.FII_MEASURE24 FII_MEASURE24,
768 h.FII_MEASURE25 FII_MEASURE25,
769 h.FII_MEASURE26 FII_MEASURE26,
770 '''||l_url_1||''' FII_ATTRIBUTE10,
771 '''||l_url_2||''' FII_ATTRIBUTE11,
772 '''||l_url_4||''' FII_ATTRIBUTE12,
773 '''||l_url_3||''' FII_ATTRIBUTE13
774
775 FROM
776 (
777 SELECT
778 g.FII_MEASURE1 FII_MEASURE1,
779 g.FII_MEASURE2 FII_MEASURE2,
780 g.FII_MEASURE3 FII_MEASURE3,
781 g.FII_MEASURE4 FII_MEASURE4,
782 g.FII_MEASURE5 FII_MEASURE5,
783 g.FII_MEASURE6 FII_MEASURE6,
784 g.FII_MEASURE7 FII_MEASURE7,
785 g.FII_MEASURE8 FII_MEASURE8,
786 g.FII_MEASURE9 FII_MEASURE9,
787 g.FII_MEASURE10 FII_MEASURE10,
788 g.FII_MEASURE11 FII_MEASURE11,
789 g.FII_MEASURE12 FII_MEASURE12,
790 g.FII_MEASURE13 FII_MEASURE13,
791 g.FII_MEASURE14 FII_MEASURE14,
792 g.FII_MEASURE15 FII_MEASURE15,
793 g.FII_MEASURE16 FII_MEASURE16,
794 g.FII_MEASURE18 FII_MEASURE18,
795 g.FII_MEASURE21 FII_MEASURE21,
796 g.FII_MEASURE22 FII_MEASURE22,
797 g.FII_MEASURE23 FII_MEASURE23,
798 g.FII_MEASURE24 FII_MEASURE24,
799 g.FII_MEASURE25 FII_MEASURE25,
800 g.FII_MEASURE26 FII_MEASURE26,
801 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
802 FROM
803 (
804 SELECT f.invoice_number FII_MEASURE1,
805 f.invoice_id FII_MEASURE2,
806 f.invoice_type FII_MEASURE3,
807 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
808 to_char(f.entered_date, '''||l_date_mask||''') FII_MEASURE5,
809 to_char(MIN(nvl(fpay.due_date, f.due_date)), '''||l_date_mask||''') FII_MEASURE6,
810 f.invoice_currency_code FII_MEASURE7,
811 f.invoice_amount FII_MEASURE8,
812 f.'||l_invoice_amount||' FII_MEASURE9,
813 sum(decode(fpay.action,''PREPAYMENT'',decode(sign(fpay.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
814 nvl(fpay.'||l_payment_amount||',0)),nvl(fpay.'||l_payment_amount||' ,0))) FII_MEASURE10, /*changes for bug 3423861*/
815 decode(nvl(hold1.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
816 nvl(hold.FII_MEASURE12,0) FII_MEASURE12,
817 nvl(hold1.FII_MEASURE13,0) FII_MEASURE13,
818 f.dist_count FII_MEASURE14,
819 t.name FII_MEASURE15,
820 f.source FII_MEASURE16,
821 max(fpay.check_id) FII_MEASURE18,
822 to_number(null) FII_MEASURE21,
823 sum(f.'||l_invoice_amount||') over() FII_MEASURE22,
824 sum(sum(decode(fpay.action,''PREPAYMENT'',decode(sign(fpay.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
825 nvl(fpay.'||l_payment_amount||',0)),nvl(fpay.'||l_payment_amount||' ,0)))) over() FII_MEASURE23, /*changes for bug 3423861*/
826 sum(nvl(hold.FII_MEASURE12,0)) over() FII_MEASURE24,
827 sum(nvl(hold1.FII_MEASURE13,0)) over() FII_MEASURE25,
828 sum(f.dist_count) over() FII_MEASURE26
829 FROM fii_ap_invoice_b f,
830 fii_ap_pay_sched_b fpay,
831 ap_terms_tl t,
832 (SELECT invoice_id,
833 COUNT(hold_date) FII_MEASURE12
834 FROM fii_ap_inv_holds_b f
835 WHERE hold_date BETWEEN :PERIOD_START AND &BIS_CURRENT_ASOF_DATE
836 '||l_org_where||' '||l_sup_where||'
837 GROUP BY invoice_id
838 ) hold,
839 (SELECT invoice_id,
840 ''Y'' FII_MEASURE11,
841 sum(days_on_hold) FII_MEASURE13
842 FROM fii_ap_hhist_ib_mv f
843 WHERE 1 =1
844 '||l_org_where||' '||l_sup_where||'
845 GROUP BY invoice_id
846 ) hold1
847 WHERE f.e_invoices_flag = ''Y''
848 AND f.source = ''EDI GATEWAY''
849 '||l_org_where||' '||l_sup_where||'
850 AND f.cancel_flag = ''N'' /*Added code for bug. no.3084280 */
851 AND f.entered_date <= &BIS_CURRENT_ASOF_DATE
852 AND f.entered_date >= :PERIOD_START
853 AND (nvl(fpay.action_date,&BIS_CURRENT_ASOF_DATE) <= &BIS_CURRENT_ASOF_DATE /*added code for bug no.3113879*/
854 OR (fpay.action = ''PREPAYMENT'') ) /* made changes for bug 3423861*/
855 AND f.invoice_id = fpay.invoice_id (+)
856 AND t.term_id = f.terms_id
857 AND t.language = userenv(''LANG'')
858 AND f.invoice_id =hold.invoice_id (+)
859 AND f.invoice_id =hold1.invoice_id (+)
860 GROUP BY f.invoice_number,
861 f.invoice_id,
862 f.invoice_type,
863 f.invoice_date,
864 f.entered_date,
865 f.invoice_currency_code,
866 f.invoice_amount,
867 f.'||l_invoice_amount||',
868 hold1.FII_MEASURE11,
869 hold1.FII_MEASURE13,
870 f.'||l_discount_offered||',
871 hold.FII_MEASURE12,
872 f.dist_count,
873 t.name,
874 f.source) g ) h
875 WHERE (rnk between &START_INDEX AND &END_INDEX or &END_INDEX = -1)
876 &ORDER_BY_CLAUSE
877 ';
878
879
880 WHEN 'FII_AP_ERS_INV_ENT_DTL' THEN -- 6th, ERS Invoices Entered Detail
881 sqlstmt := '
882 SELECT
883 h.FII_MEASURE1 FII_MEASURE1,
884 h.FII_MEASURE2 FII_MEASURE2,
885 h.FII_MEASURE3 FII_MEASURE3,
886 h.FII_MEASURE4 FII_MEASURE4,
887 h.FII_MEASURE5 FII_MEASURE5,
888 h.FII_MEASURE6 FII_MEASURE6,
889 h.FII_MEASURE7 FII_MEASURE7,
890 h.FII_MEASURE8 FII_MEASURE8,
891 h.FII_MEASURE9 FII_MEASURE9,
892 h.FII_MEASURE10 FII_MEASURE10,
893 h.FII_MEASURE11 FII_MEASURE11,
894 h.FII_MEASURE12 FII_MEASURE12,
895 h.FII_MEASURE13 FII_MEASURE13,
896 h.FII_MEASURE14 FII_MEASURE14,
897 h.FII_MEASURE15 FII_MEASURE15,
898 h.FII_MEASURE16 FII_MEASURE16,
899 h.FII_MEASURE18 FII_MEASURE18,
900 h.FII_MEASURE21 FII_MEASURE21,
901 h.FII_MEASURE22 FII_MEASURE22,
902 h.FII_MEASURE23 FII_MEASURE23,
903 h.FII_MEASURE24 FII_MEASURE24,
904 h.FII_MEASURE25 FII_MEASURE25,
905 h.FII_MEASURE26 FII_MEASURE26,
906 '''||l_url_1||''' FII_ATTRIBUTE10,
907 '''||l_url_2||''' FII_ATTRIBUTE11,
908 '''||l_url_4||''' FII_ATTRIBUTE12,
909 '''||l_url_3||''' FII_ATTRIBUTE13
910
911 FROM
912 (
913 SELECT
914 g.FII_MEASURE1 FII_MEASURE1,
915 g.FII_MEASURE2 FII_MEASURE2,
916 g.FII_MEASURE3 FII_MEASURE3,
917 g.FII_MEASURE4 FII_MEASURE4,
918 g.FII_MEASURE5 FII_MEASURE5,
919 g.FII_MEASURE6 FII_MEASURE6,
920 g.FII_MEASURE7 FII_MEASURE7,
921 g.FII_MEASURE8 FII_MEASURE8,
922 g.FII_MEASURE9 FII_MEASURE9,
923 g.FII_MEASURE10 FII_MEASURE10,
924 g.FII_MEASURE11 FII_MEASURE11,
925 g.FII_MEASURE12 FII_MEASURE12,
926 g.FII_MEASURE13 FII_MEASURE13,
927 g.FII_MEASURE14 FII_MEASURE14,
928 g.FII_MEASURE15 FII_MEASURE15,
929 g.FII_MEASURE16 FII_MEASURE16,
930 g.FII_MEASURE18 FII_MEASURE18,
931 g.FII_MEASURE21 FII_MEASURE21,
932 g.FII_MEASURE22 FII_MEASURE22,
933 g.FII_MEASURE23 FII_MEASURE23,
934 g.FII_MEASURE24 FII_MEASURE24,
935 g.FII_MEASURE25 FII_MEASURE25,
936 g.FII_MEASURE26 FII_MEASURE26,
937 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
938 FROM
939 (
940 SELECT f.invoice_number FII_MEASURE1,
941 f.invoice_id FII_MEASURE2,
942 f.invoice_type FII_MEASURE3,
943 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
944 to_char(f.entered_date, '''||l_date_mask||''') FII_MEASURE5,
945 to_char(MIN(nvl(fpay.due_date, f.due_date)), '''||l_date_mask||''') FII_MEASURE6,
946 f.invoice_currency_code FII_MEASURE7,
947 f.invoice_amount FII_MEASURE8,
948 f.'||l_invoice_amount||' FII_MEASURE9,
949 sum(decode(fpay.action,''PREPAYMENT'',decode(sign(fpay.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
950 nvl(fpay.'||l_payment_amount||',0)),nvl(fpay.'||l_payment_amount||' ,0))) FII_MEASURE10, /*changes for bug 3423861*/
951 decode(nvl(hold1.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
952 nvl(hold.FII_MEASURE12,0) FII_MEASURE12,
953 nvl(hold1.FII_MEASURE13,0) FII_MEASURE13,
954 f.dist_count FII_MEASURE14,
955 t.name FII_MEASURE15,
956 f.source FII_MEASURE16,
957 max(fpay.check_id) FII_MEASURE18,
958 to_number(null) FII_MEASURE21,
959 sum(f.'||l_invoice_amount||') over() FII_MEASURE22,
960 sum(sum(decode(fpay.action,''PREPAYMENT'',decode(sign(fpay.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
961 nvl(fpay.'||l_payment_amount||',0)),nvl(fpay.'||l_payment_amount||' ,0)))) over() FII_MEASURE23, /*changes for bug 3423861*/
962 sum(nvl(hold.FII_MEASURE12,0)) over() FII_MEASURE24,
963 sum(nvl(hold1.FII_MEASURE13,0)) over() FII_MEASURE25,
964 sum(f.dist_count) over() FII_MEASURE26
965 FROM fii_ap_invoice_b f,
966 fii_ap_pay_sched_b fpay,
967 ap_terms_tl t,
968 (SELECT invoice_id,
969 COUNT(hold_date) FII_MEASURE12
970 FROM fii_ap_inv_holds_b f
971 WHERE hold_date BETWEEN :PERIOD_START AND &BIS_CURRENT_ASOF_DATE
972 '||l_org_where||' '||l_sup_where||'
973 GROUP BY invoice_id
974 ) hold,
975 (SELECT invoice_id,
976 ''Y'' FII_MEASURE11,
977 sum(days_on_hold) FII_MEASURE13
978 FROM fii_ap_hhist_ib_mv f
979 WHERE 1 =1
980 '||l_org_where||' '||l_sup_where||'
981 GROUP BY invoice_id
982 ) hold1
983 WHERE f.e_invoices_flag = ''Y''
984 AND f.source = ''ERS''
985 '||l_org_where||' '||l_sup_where||'
986 AND f.cancel_flag = ''N'' /*Added code for bug. no.3084280 */
987 AND f.entered_date <= &BIS_CURRENT_ASOF_DATE
988 AND f.entered_date >= :PERIOD_START
989 AND (nvl(fpay.action_date,&BIS_CURRENT_ASOF_DATE) <= &BIS_CURRENT_ASOF_DATE /*added code for bug no.3113879*/
990 OR (fpay.action = ''PREPAYMENT'') ) /* made changes for bug 3423861*/
991 AND f.invoice_id = fpay.invoice_id (+)
992 AND t.term_id = f.terms_id
993 AND t.language = userenv(''LANG'')
994 AND f.invoice_id =hold.invoice_id (+)
995 AND f.invoice_id =hold1.invoice_id (+)
996 GROUP BY f.invoice_number,
997 f.invoice_id,
998 f.invoice_type,
999 f.invoice_date,
1000 f.entered_date,
1001 f.invoice_currency_code,
1002 f.invoice_amount,
1003 f.'||l_invoice_amount||',
1004 hold1.FII_MEASURE11,
1005 hold1.FII_MEASURE13,
1006 f.'||l_discount_offered||',
1007 hold.FII_MEASURE12,
1008 f.dist_count,
1009 t.name,
1010 f.source) g ) h
1011 WHERE (rnk between &START_INDEX AND &END_INDEX or &END_INDEX = -1)
1012 &ORDER_BY_CLAUSE
1013 ';
1014
1015
1016 WHEN 'FII_AP_ISP_INV_ENT_DTL' THEN -- 7th, ISP Invoices Entered Detail
1017 sqlstmt := '
1018 SELECT
1019 h.FII_MEASURE1 FII_MEASURE1,
1020 h.FII_MEASURE2 FII_MEASURE2,
1021 h.FII_MEASURE3 FII_MEASURE3,
1022 h.FII_MEASURE4 FII_MEASURE4,
1023 h.FII_MEASURE5 FII_MEASURE5,
1024 h.FII_MEASURE6 FII_MEASURE6,
1025 h.FII_MEASURE7 FII_MEASURE7,
1026 h.FII_MEASURE8 FII_MEASURE8,
1027 h.FII_MEASURE9 FII_MEASURE9,
1028 h.FII_MEASURE10 FII_MEASURE10,
1029 h.FII_MEASURE11 FII_MEASURE11,
1030 h.FII_MEASURE12 FII_MEASURE12,
1031 h.FII_MEASURE13 FII_MEASURE13,
1032 h.FII_MEASURE14 FII_MEASURE14,
1033 h.FII_MEASURE15 FII_MEASURE15,
1034 h.FII_MEASURE16 FII_MEASURE16,
1035 h.FII_MEASURE18 FII_MEASURE18,
1036 h.FII_MEASURE21 FII_MEASURE21,
1037 h.FII_MEASURE22 FII_MEASURE22,
1038 h.FII_MEASURE23 FII_MEASURE23,
1039 h.FII_MEASURE24 FII_MEASURE24,
1040 h.FII_MEASURE25 FII_MEASURE25,
1041 h.FII_MEASURE26 FII_MEASURE26,
1042 '''||l_url_1||''' FII_ATTRIBUTE10,
1043 '''||l_url_2||''' FII_ATTRIBUTE11,
1044 '''||l_url_4||''' FII_ATTRIBUTE12,
1045 '''||l_url_3||''' FII_ATTRIBUTE13
1046
1047 FROM
1048 (
1049 SELECT
1050 g.FII_MEASURE1 FII_MEASURE1,
1051 g.FII_MEASURE2 FII_MEASURE2,
1052 g.FII_MEASURE3 FII_MEASURE3,
1053 g.FII_MEASURE4 FII_MEASURE4,
1054 g.FII_MEASURE5 FII_MEASURE5,
1055 g.FII_MEASURE6 FII_MEASURE6,
1056 g.FII_MEASURE7 FII_MEASURE7,
1057 g.FII_MEASURE8 FII_MEASURE8,
1058 g.FII_MEASURE9 FII_MEASURE9,
1059 g.FII_MEASURE10 FII_MEASURE10,
1060 g.FII_MEASURE11 FII_MEASURE11,
1061 g.FII_MEASURE12 FII_MEASURE12,
1062 g.FII_MEASURE13 FII_MEASURE13,
1063 g.FII_MEASURE14 FII_MEASURE14,
1064 g.FII_MEASURE15 FII_MEASURE15,
1065 g.FII_MEASURE16 FII_MEASURE16,
1066 g.FII_MEASURE18 FII_MEASURE18,
1067 g.FII_MEASURE21 FII_MEASURE21,
1068 g.FII_MEASURE22 FII_MEASURE22,
1069 g.FII_MEASURE23 FII_MEASURE23,
1070 g.FII_MEASURE24 FII_MEASURE24,
1071 g.FII_MEASURE25 FII_MEASURE25,
1072 g.FII_MEASURE26 FII_MEASURE26,
1073 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
1074 FROM
1075 (
1076 SELECT f.invoice_number FII_MEASURE1,
1077 f.invoice_id FII_MEASURE2,
1078 f.invoice_type FII_MEASURE3,
1079 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
1080 to_char(f.entered_date, '''||l_date_mask||''') FII_MEASURE5,
1081 to_char(MIN(nvl(fpay.due_date, f.due_date)), '''||l_date_mask||''') FII_MEASURE6,
1082 f.invoice_currency_code FII_MEASURE7,
1083 f.invoice_amount FII_MEASURE8,
1084 f.'||l_invoice_amount||' FII_MEASURE9,
1085 sum(decode(fpay.action,''PREPAYMENT'',decode(sign(fpay.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
1086 nvl(fpay.'||l_payment_amount||',0)),nvl(fpay.'||l_payment_amount||' ,0))) FII_MEASURE10, /*changes for bug 3423861*/
1087 decode(nvl(hold1.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
1088 nvl(hold.FII_MEASURE12,0) FII_MEASURE12,
1089 nvl(hold1.FII_MEASURE13,0) FII_MEASURE13,
1090 f.dist_count FII_MEASURE14,
1091 t.name FII_MEASURE15,
1092 f.source FII_MEASURE16,
1093 max(fpay.check_id) FII_MEASURE18,
1094 to_number(null) FII_MEASURE21,
1095 sum(f.'||l_invoice_amount||') over() FII_MEASURE22,
1096 sum(sum(decode(fpay.action,''PREPAYMENT'',decode(sign(fpay.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
1097 nvl(fpay.'||l_payment_amount||',0)),nvl(fpay.'||l_payment_amount||' ,0)))) over() FII_MEASURE23, /*changes for bug 3423861*/
1098 sum(nvl(hold.FII_MEASURE12,0)) over() FII_MEASURE24,
1099 sum(nvl(hold1.FII_MEASURE13,0)) over() FII_MEASURE25,
1100 sum(f.dist_count) over() FII_MEASURE26
1101 FROM fii_ap_invoice_b f,
1102 fii_ap_pay_sched_b fpay,
1103 ap_terms_tl t,
1104 (SELECT invoice_id,
1105 COUNT(hold_date) FII_MEASURE12
1106 FROM fii_ap_inv_holds_b f
1107 WHERE hold_date BETWEEN :PERIOD_START AND &BIS_CURRENT_ASOF_DATE
1108 '||l_org_where||' '||l_sup_where||'
1109 GROUP BY invoice_id
1110 ) hold,
1111 (SELECT invoice_id,
1112 ''Y'' FII_MEASURE11,
1113 sum(days_on_hold) FII_MEASURE13
1114 FROM fii_ap_hhist_ib_mv f
1115 WHERE 1 =1
1116 '||l_org_where||' '||l_sup_where||'
1117 GROUP BY invoice_id
1118 ) hold1
1119 WHERE f.e_invoices_flag = ''Y''
1120 AND f.source = ''ISP''
1121 '||l_org_where||' '||l_sup_where||'
1122 AND f.cancel_flag = ''N'' /*Added code for bug. no.3084280 */
1123 AND f.entered_date <= &BIS_CURRENT_ASOF_DATE
1124 AND f.entered_date >= :PERIOD_START
1125 AND f.invoice_id = fpay.invoice_id (+)
1126 AND (nvl(fpay.action_date,&BIS_CURRENT_ASOF_DATE) <= &BIS_CURRENT_ASOF_DATE /*added code for bug no.3113879*/
1127 OR (fpay.action = ''PREPAYMENT'') ) /* made changes for bug 3423861*/
1128 AND t.term_id = f.terms_id
1129 AND t.language = userenv(''LANG'')
1130 AND f.invoice_id =hold.invoice_id (+)
1131 AND f.invoice_id =hold1.invoice_id (+)
1132 GROUP BY f.invoice_number,
1133 f.invoice_id,
1134 f.invoice_type,
1135 f.invoice_date,
1136 f.entered_date,
1137 f.invoice_currency_code,
1138 f.invoice_amount,
1139 f.'||l_invoice_amount||',
1140 hold1.FII_MEASURE11,
1141 hold1.FII_MEASURE13,
1142 f.'||l_discount_offered||',
1143 hold.FII_MEASURE12,
1144 f.dist_count,
1145 t.name,
1146 f.source) g ) h
1147 WHERE (rnk between &START_INDEX AND &END_INDEX or &END_INDEX = -1)
1148 &ORDER_BY_CLAUSE
1149 ';
1150
1151
1152 WHEN 'FII_AP_ASBN_INV_ENT_DTL' THEN -- 8th, ASBN Invoices Entered Detail
1153 sqlstmt := '
1154 SELECT
1155 h.FII_MEASURE1 FII_MEASURE1,
1156 h.FII_MEASURE2 FII_MEASURE2,
1157 h.FII_MEASURE3 FII_MEASURE3,
1158 h.FII_MEASURE4 FII_MEASURE4,
1159 h.FII_MEASURE5 FII_MEASURE5,
1160 h.FII_MEASURE6 FII_MEASURE6,
1161 h.FII_MEASURE7 FII_MEASURE7,
1162 h.FII_MEASURE8 FII_MEASURE8,
1163 h.FII_MEASURE9 FII_MEASURE9,
1164 h.FII_MEASURE10 FII_MEASURE10,
1165 h.FII_MEASURE11 FII_MEASURE11,
1166 h.FII_MEASURE12 FII_MEASURE12,
1167 h.FII_MEASURE13 FII_MEASURE13,
1168 h.FII_MEASURE14 FII_MEASURE14,
1169 h.FII_MEASURE15 FII_MEASURE15,
1170 h.FII_MEASURE16 FII_MEASURE16,
1171 h.FII_MEASURE18 FII_MEASURE18,
1172 h.FII_MEASURE21 FII_MEASURE21,
1173 h.FII_MEASURE22 FII_MEASURE22,
1174 h.FII_MEASURE23 FII_MEASURE23,
1175 h.FII_MEASURE24 FII_MEASURE24,
1176 h.FII_MEASURE25 FII_MEASURE25,
1177 h.FII_MEASURE26 FII_MEASURE26,
1178 '''||l_url_1||''' FII_ATTRIBUTE10,
1179 '''||l_url_2||''' FII_ATTRIBUTE11,
1180 '''||l_url_4||''' FII_ATTRIBUTE12,
1181 '''||l_url_3||''' FII_ATTRIBUTE13
1182
1183 FROM
1184 (
1185 SELECT
1186 g.FII_MEASURE1 FII_MEASURE1,
1187 g.FII_MEASURE2 FII_MEASURE2,
1188 g.FII_MEASURE3 FII_MEASURE3,
1189 g.FII_MEASURE4 FII_MEASURE4,
1190 g.FII_MEASURE5 FII_MEASURE5,
1191 g.FII_MEASURE6 FII_MEASURE6,
1192 g.FII_MEASURE7 FII_MEASURE7,
1193 g.FII_MEASURE8 FII_MEASURE8,
1194 g.FII_MEASURE9 FII_MEASURE9,
1195 g.FII_MEASURE10 FII_MEASURE10,
1196 g.FII_MEASURE11 FII_MEASURE11,
1197 g.FII_MEASURE12 FII_MEASURE12,
1198 g.FII_MEASURE13 FII_MEASURE13,
1199 g.FII_MEASURE14 FII_MEASURE14,
1200 g.FII_MEASURE15 FII_MEASURE15,
1201 g.FII_MEASURE16 FII_MEASURE16,
1202 g.FII_MEASURE18 FII_MEASURE18,
1203 g.FII_MEASURE21 FII_MEASURE21,
1204 g.FII_MEASURE22 FII_MEASURE22,
1205 g.FII_MEASURE23 FII_MEASURE23,
1206 g.FII_MEASURE24 FII_MEASURE24,
1207 g.FII_MEASURE25 FII_MEASURE25,
1208 g.FII_MEASURE26 FII_MEASURE26,
1209 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
1210 FROM
1211 (
1212 SELECT f.invoice_number FII_MEASURE1,
1213 f.invoice_id FII_MEASURE2,
1214 f.invoice_type FII_MEASURE3,
1215 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
1216 to_char(f.entered_date, '''||l_date_mask||''') FII_MEASURE5,
1217 to_char(MIN(nvl(fpay.due_date, f.due_date)), '''||l_date_mask||''') FII_MEASURE6,
1218 f.invoice_currency_code FII_MEASURE7,
1219 f.invoice_amount FII_MEASURE8,
1220 f.'||l_invoice_amount||' FII_MEASURE9,
1221 sum(decode(fpay.action,''PREPAYMENT'',decode(sign(fpay.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
1222 nvl(fpay.'||l_payment_amount||',0)),nvl(fpay.'||l_payment_amount||' ,0))) FII_MEASURE10, /*changes for bug 3423861*/
1223 decode(nvl(hold1.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
1224 nvl(hold.FII_MEASURE12,0) FII_MEASURE12,
1225 nvl(hold1.FII_MEASURE13,0) FII_MEASURE13,
1226 f.dist_count FII_MEASURE14,
1227 t.name FII_MEASURE15,
1228 f.source FII_MEASURE16,
1229 max(fpay.check_id) FII_MEASURE18,
1230 to_number(null) FII_MEASURE21,
1231 sum(f.'||l_invoice_amount||') over() FII_MEASURE22,
1232 sum(sum(decode(fpay.action,''PREPAYMENT'',decode(sign(fpay.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
1233 nvl(fpay.'||l_payment_amount||',0)),nvl(fpay.'||l_payment_amount||' ,0)))) over() FII_MEASURE23, /*changes for bug 3423861*/
1234 sum(nvl(hold.FII_MEASURE12,0)) over() FII_MEASURE24,
1235 sum(nvl(hold1.FII_MEASURE13,0)) over() FII_MEASURE25,
1236 sum(f.dist_count) over() FII_MEASURE26
1237 FROM fii_ap_invoice_b f,
1238 fii_ap_pay_sched_b fpay,
1239 ap_terms_tl t,
1240 (SELECT invoice_id,
1241 COUNT(hold_date) FII_MEASURE12
1242 FROM fii_ap_inv_holds_b f
1243 WHERE hold_date BETWEEN :PERIOD_START AND &BIS_CURRENT_ASOF_DATE
1244 '||l_org_where||' '||l_sup_where||'
1245 GROUP BY invoice_id
1246 ) hold,
1247 (SELECT invoice_id,
1248 ''Y'' FII_MEASURE11,
1249 sum(days_on_hold) FII_MEASURE13
1250 FROM fii_ap_hhist_ib_mv f
1251 WHERE 1 =1
1252 '||l_org_where||' '||l_sup_where||'
1253 GROUP BY invoice_id
1254 ) hold1
1255 WHERE f.e_invoices_flag = ''Y''
1256 AND f.source = ''ASBN''
1257 '||l_org_where||' '||l_sup_where||'
1258 AND f.cancel_flag = ''N'' /*Added code for bug. no.3084280 */
1259 AND f.entered_date <= &BIS_CURRENT_ASOF_DATE
1260 AND f.entered_date >= :PERIOD_START
1261 AND f.invoice_id = fpay.invoice_id (+)
1262 AND (nvl(fpay.action_date,&BIS_CURRENT_ASOF_DATE) <= &BIS_CURRENT_ASOF_DATE /*added code for bug no.3113879*/
1263 OR (fpay.action = ''PREPAYMENT'') ) /* made changes for bug 3423861*/
1264 AND t.term_id = f.terms_id
1265 AND t.language = userenv(''LANG'')
1266 AND f.invoice_id =hold.invoice_id (+)
1267 AND f.invoice_id =hold1.invoice_id (+)
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_amount||',
1276 hold1.FII_MEASURE11,
1277 hold1.FII_MEASURE13,
1278 f.'||l_discount_offered||',
1279 hold.FII_MEASURE12,
1280 f.dist_count,
1281 t.name,
1282 f.source) g ) h
1283 WHERE (rnk between &START_INDEX AND &END_INDEX or &END_INDEX = -1)
1284 &ORDER_BY_CLAUSE
1285 ';
1286
1287
1288 WHEN 'FII_AP_OTHER_SRC_INV_ENT_DTL' THEN -- 9th, Other Integrated Invoices Entered Detail
1289 sqlstmt := '
1290 SELECT
1291 h.FII_MEASURE1 FII_MEASURE1,
1292 h.FII_MEASURE2 FII_MEASURE2,
1293 h.FII_MEASURE3 FII_MEASURE3,
1294 h.FII_MEASURE4 FII_MEASURE4,
1295 h.FII_MEASURE5 FII_MEASURE5,
1296 h.FII_MEASURE6 FII_MEASURE6,
1297 h.FII_MEASURE7 FII_MEASURE7,
1298 h.FII_MEASURE8 FII_MEASURE8,
1299 h.FII_MEASURE9 FII_MEASURE9,
1300 h.FII_MEASURE10 FII_MEASURE10,
1301 h.FII_MEASURE11 FII_MEASURE11,
1302 h.FII_MEASURE12 FII_MEASURE12,
1303 h.FII_MEASURE13 FII_MEASURE13,
1304 h.FII_MEASURE14 FII_MEASURE14,
1305 h.FII_MEASURE15 FII_MEASURE15,
1306 h.FII_MEASURE16 FII_MEASURE16,
1307 h.FII_MEASURE18 FII_MEASURE18,
1308 h.FII_MEASURE21 FII_MEASURE21,
1309 h.FII_MEASURE22 FII_MEASURE22,
1310 h.FII_MEASURE23 FII_MEASURE23,
1311 h.FII_MEASURE24 FII_MEASURE24,
1312 h.FII_MEASURE25 FII_MEASURE25,
1313 h.FII_MEASURE26 FII_MEASURE26,
1314 '''||l_url_1||''' FII_ATTRIBUTE10,
1315 '''||l_url_2||''' FII_ATTRIBUTE11,
1316 '''||l_url_4||''' FII_ATTRIBUTE12,
1317 '''||l_url_3||''' 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_MEASURE18 FII_MEASURE18,
1339 g.FII_MEASURE21 FII_MEASURE21,
1340 g.FII_MEASURE22 FII_MEASURE22,
1341 g.FII_MEASURE23 FII_MEASURE23,
1342 g.FII_MEASURE24 FII_MEASURE24,
1343 g.FII_MEASURE25 FII_MEASURE25,
1344 g.FII_MEASURE26 FII_MEASURE26,
1345 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
1346 FROM
1347 (
1348
1349 SELECT f.invoice_number FII_MEASURE1,
1350 f.invoice_id FII_MEASURE2,
1351 f.invoice_type FII_MEASURE3,
1352 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
1353 to_char(f.entered_date, '''||l_date_mask||''') FII_MEASURE5,
1354 to_char(MIN(nvl(fpay.due_date, f.due_date)), '''||l_date_mask||''') FII_MEASURE6,
1355 f.invoice_currency_code FII_MEASURE7,
1356 f.invoice_amount FII_MEASURE8,
1357 f.'||l_invoice_amount||' FII_MEASURE9,
1358 sum(decode(fpay.action,''PREPAYMENT'',decode(sign(fpay.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
1359 nvl(fpay.'||l_payment_amount||',0)),nvl(fpay.'||l_payment_amount||' ,0))) FII_MEASURE10, /*changes for bug 3423861*/
1360 decode(nvl(hold1.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
1361 nvl(hold.FII_MEASURE12,0) FII_MEASURE12,
1362 nvl(hold1.FII_MEASURE13,0) FII_MEASURE13,
1363 f.dist_count FII_MEASURE14,
1364 t.name FII_MEASURE15,
1365 f.source FII_MEASURE16,
1366 max(fpay.check_id) FII_MEASURE18,
1367 to_number(null) FII_MEASURE21,
1368 sum(f.'||l_invoice_amount||') over() FII_MEASURE22,
1369 sum(sum(decode(fpay.action,''PREPAYMENT'',decode(sign(fpay.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
1370 nvl(fpay.'||l_payment_amount||',0)),nvl(fpay.'||l_payment_amount||' ,0)))) over() FII_MEASURE23, /*changes for bug 3423861*/
1371 sum(nvl(hold.FII_MEASURE12,0)) over() FII_MEASURE24,
1372 sum(nvl(hold1.FII_MEASURE13,0)) over() FII_MEASURE25,
1373 sum(f.dist_count) over() FII_MEASURE26
1374 FROM fii_ap_invoice_b f,
1375 fii_ap_pay_sched_b fpay,
1376 ap_terms_tl t,
1377 (SELECT invoice_id,
1378 COUNT(hold_date) FII_MEASURE12
1379 FROM fii_ap_inv_holds_b f
1380 WHERE hold_date BETWEEN :PERIOD_START AND &BIS_CURRENT_ASOF_DATE
1381 '||l_org_where||' '||l_sup_where||'
1382 GROUP BY invoice_id
1383 ) hold,
1384 (SELECT invoice_id,
1385 ''Y'' FII_MEASURE11,
1386 sum(days_on_hold) FII_MEASURE13
1387 FROM fii_ap_hhist_ib_mv f
1388 WHERE 1 =1
1389 '||l_org_where||' '||l_sup_where||'
1390 GROUP BY invoice_id
1391 ) hold1
1392 WHERE f.e_invoices_flag = ''Y''
1393 AND f.source NOT IN (''Manual Invoice Entry'', ''INVOICE GATEWAY'',
1394 ''RECURRING INVOICE'', ''XML GATEWAY'', ''EDI GATEWAY'', ''ERS'', ''ISP'', ''ASBN'')
1395 '||l_org_where||' '||l_sup_where||'
1396 AND f.cancel_flag = ''N'' /*Added code for bug. no.3084280 */
1397 AND f.entered_date <= &BIS_CURRENT_ASOF_DATE
1398 AND f.entered_date >= :PERIOD_START
1399 AND (nvl(fpay.action_date,&BIS_CURRENT_ASOF_DATE) <= &BIS_CURRENT_ASOF_DATE /*added during Prepayment enh. as was not done earlier*/
1400 OR (fpay.action = ''PREPAYMENT'') ) /* made changes for bug 3423861*/
1401 AND f.invoice_id = fpay.invoice_id (+)
1402 AND t.term_id = f.terms_id
1403 AND t.language = userenv(''LANG'')
1404 AND f.invoice_id =hold.invoice_id (+)
1405 AND f.invoice_id =hold1.invoice_id (+)
1406 GROUP BY f.invoice_number,
1407 f.invoice_id,
1408 f.invoice_type,
1409 f.invoice_date,
1410 f.entered_date,
1411 f.invoice_currency_code,
1412 f.invoice_amount,
1413 f.'||l_invoice_amount||',
1414 hold1.FII_MEASURE11,
1415 hold1.FII_MEASURE13,
1416 f.'||l_discount_offered||',
1417 hold.FII_MEASURE12,
1418 f.dist_count,
1419 t.name,
1420 f.source) g ) h
1421 WHERE (rnk between &START_INDEX AND &END_INDEX or &END_INDEX = -1)
1422 &ORDER_BY_CLAUSE
1423 ';
1424
1425
1426 WHEN 'FII_AP_STANDARD_INV_ENT_DTL' THEN -- 10th, Standard Invoices Entered Detail
1427 sqlstmt := '
1428 SELECT
1429 h.FII_MEASURE1 FII_MEASURE1,
1430 h.FII_MEASURE2 FII_MEASURE2,
1431 h.FII_MEASURE3 FII_MEASURE3,
1432 h.FII_MEASURE4 FII_MEASURE4,
1433 h.FII_MEASURE5 FII_MEASURE5,
1434 h.FII_MEASURE6 FII_MEASURE6,
1435 h.FII_MEASURE7 FII_MEASURE7,
1436 h.FII_MEASURE8 FII_MEASURE8,
1437 h.FII_MEASURE9 FII_MEASURE9,
1438 h.FII_MEASURE10 FII_MEASURE10,
1439 h.FII_MEASURE11 FII_MEASURE11,
1440 h.FII_MEASURE12 FII_MEASURE12,
1441 h.FII_MEASURE13 FII_MEASURE13,
1442 h.FII_MEASURE14 FII_MEASURE14,
1443 h.FII_MEASURE15 FII_MEASURE15,
1444 h.FII_MEASURE16 FII_MEASURE16,
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 '''||l_url_1||''' FII_ATTRIBUTE10,
1453 '''||l_url_2||''' FII_ATTRIBUTE11,
1454 '''||l_url_4||''' FII_ATTRIBUTE12,
1455 '''||l_url_3||''' FII_ATTRIBUTE13
1456
1457 FROM
1458 (
1459 SELECT
1460 g.FII_MEASURE1 FII_MEASURE1,
1461 g.FII_MEASURE2 FII_MEASURE2,
1462 g.FII_MEASURE3 FII_MEASURE3,
1463 g.FII_MEASURE4 FII_MEASURE4,
1464 g.FII_MEASURE5 FII_MEASURE5,
1465 g.FII_MEASURE6 FII_MEASURE6,
1466 g.FII_MEASURE7 FII_MEASURE7,
1467 g.FII_MEASURE8 FII_MEASURE8,
1468 g.FII_MEASURE9 FII_MEASURE9,
1469 g.FII_MEASURE10 FII_MEASURE10,
1470 g.FII_MEASURE11 FII_MEASURE11,
1471 g.FII_MEASURE12 FII_MEASURE12,
1472 g.FII_MEASURE13 FII_MEASURE13,
1473 g.FII_MEASURE14 FII_MEASURE14,
1474 g.FII_MEASURE15 FII_MEASURE15,
1475 g.FII_MEASURE16 FII_MEASURE16,
1476 g.FII_MEASURE18 FII_MEASURE18,
1477 g.FII_MEASURE21 FII_MEASURE21,
1478 g.FII_MEASURE22 FII_MEASURE22,
1479 g.FII_MEASURE23 FII_MEASURE23,
1480 g.FII_MEASURE24 FII_MEASURE24,
1481 g.FII_MEASURE25 FII_MEASURE25,
1482 g.FII_MEASURE26 FII_MEASURE26,
1483 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
1484 FROM
1485 (
1486 SELECT f.invoice_number FII_MEASURE1,
1487 f.invoice_id FII_MEASURE2,
1488 f.invoice_type FII_MEASURE3,
1489 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
1490 to_char(f.entered_date, '''||l_date_mask||''') FII_MEASURE5,
1491 to_char(MIN(fpay.due_date), '''||l_date_mask||''') FII_MEASURE6,
1492 f.invoice_currency_code FII_MEASURE7,
1493 f.invoice_amount FII_MEASURE8,
1494 f.'||l_invoice_amount||' FII_MEASURE9,
1495 sum(fpay.'||l_payment_amount||') FII_MEASURE10,
1496 decode(nvl(hold1.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
1497 nvl(hold.FII_MEASURE12,0) FII_MEASURE12,
1498 nvl(hold1.FII_MEASURE13,0) FII_MEASURE13,
1499 f.dist_count FII_MEASURE14,
1500 t.name FII_MEASURE15,
1501 f.source FII_MEASURE16,
1502 max(fpay.check_id) FII_MEASURE18,
1503 to_number(null) FII_MEASURE21,
1504 sum(f.'||l_invoice_amount||') over() FII_MEASURE22,
1505 sum(sum(fpay.'||l_payment_amount||')) over() FII_MEASURE23,
1506 sum(nvl(hold.FII_MEASURE12,0)) over() FII_MEASURE24,
1507 sum(nvl(hold1.FII_MEASURE13,0)) over() FII_MEASURE25,
1508 sum(f.dist_count) over() FII_MEASURE26
1509 FROM fii_ap_invoice_b f,
1510 fii_ap_pay_sched_b fpay,
1511 ap_terms_tl t,
1512 (SELECT invoice_id,
1513 COUNT(hold_date) FII_MEASURE12
1514 FROM fii_ap_inv_holds_b f
1515 WHERE hold_date BETWEEN :PERIOD_START AND &BIS_CURRENT_ASOF_DATE
1516 '||l_org_where||' '||l_sup_where||'
1517 GROUP BY invoice_id
1518 ) hold,
1519 (SELECT invoice_id,
1520 ''Y'' FII_MEASURE11,
1521 sum(days_on_hold) FII_MEASURE13
1522 FROM fii_ap_hhist_ib_mv f
1523 WHERE 1 =1
1524 '||l_org_where||' '||l_sup_where||'
1525 GROUP BY invoice_id
1526 ) hold1
1527 WHERE f.invoice_type = ''STANDARD''
1528 '||l_org_where||' '||l_sup_where||'
1529 AND f.cancel_flag = ''N'' /*Added code for bug. no.3084280 */
1530 AND f.entered_date <= &BIS_CURRENT_ASOF_DATE
1531 AND f.entered_date >= :PERIOD_START
1532 AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added code for bug no.3113879*/
1533 AND f.invoice_id = fpay.invoice_id
1534 AND t.term_id = f.terms_id
1535 AND t.language = userenv(''LANG'')
1536 AND f.invoice_id =hold.invoice_id (+)
1537 AND f.invoice_id =hold1.invoice_id (+)
1538 GROUP BY f.invoice_number,
1539 f.invoice_id,
1540 f.invoice_type,
1541 f.invoice_date,
1542 f.entered_date,
1543 f.invoice_currency_code,
1544 f.invoice_amount,
1545 f.'||l_invoice_amount||',
1546 hold1.FII_MEASURE11,
1547 hold1.FII_MEASURE13,
1548 f.'||l_discount_offered||',
1549 hold.FII_MEASURE12,
1550 f.dist_count,
1551 t.name,
1552 f.source) g ) h
1553 WHERE (rnk between &START_INDEX AND &END_INDEX or &END_INDEX = -1)
1554 &ORDER_BY_CLAUSE
1555 ';
1556
1557
1558 WHEN 'FII_AP_WITHHOLDING_INV_ENT_DTL' THEN -- 11th, Withholding Invoices Entered Detail
1559 sqlstmt := '
1560 SELECT
1561 h.FII_MEASURE1 FII_MEASURE1,
1562 h.FII_MEASURE2 FII_MEASURE2,
1563 h.FII_MEASURE3 FII_MEASURE3,
1564 h.FII_MEASURE4 FII_MEASURE4,
1565 h.FII_MEASURE5 FII_MEASURE5,
1566 h.FII_MEASURE6 FII_MEASURE6,
1567 h.FII_MEASURE7 FII_MEASURE7,
1568 h.FII_MEASURE8 FII_MEASURE8,
1569 h.FII_MEASURE9 FII_MEASURE9,
1570 h.FII_MEASURE10 FII_MEASURE10,
1571 h.FII_MEASURE11 FII_MEASURE11,
1572 h.FII_MEASURE12 FII_MEASURE12,
1573 h.FII_MEASURE13 FII_MEASURE13,
1574 h.FII_MEASURE14 FII_MEASURE14,
1575 h.FII_MEASURE15 FII_MEASURE15,
1576 h.FII_MEASURE16 FII_MEASURE16,
1577 h.FII_MEASURE18 FII_MEASURE18,
1578 h.FII_MEASURE21 FII_MEASURE21,
1579 h.FII_MEASURE22 FII_MEASURE22,
1580 h.FII_MEASURE23 FII_MEASURE23,
1581 h.FII_MEASURE24 FII_MEASURE24,
1582 h.FII_MEASURE25 FII_MEASURE25,
1583 h.FII_MEASURE26 FII_MEASURE26,
1584 '''||l_url_1||''' FII_ATTRIBUTE10,
1585 '''||l_url_2||''' FII_ATTRIBUTE11,
1586 '''||l_url_4||''' FII_ATTRIBUTE12,
1587 '''||l_url_3||''' FII_ATTRIBUTE13
1588
1589 FROM
1590 (
1591 SELECT
1592 g.FII_MEASURE1 FII_MEASURE1,
1593 g.FII_MEASURE2 FII_MEASURE2,
1594 g.FII_MEASURE3 FII_MEASURE3,
1595 g.FII_MEASURE4 FII_MEASURE4,
1596 g.FII_MEASURE5 FII_MEASURE5,
1597 g.FII_MEASURE6 FII_MEASURE6,
1598 g.FII_MEASURE7 FII_MEASURE7,
1599 g.FII_MEASURE8 FII_MEASURE8,
1600 g.FII_MEASURE9 FII_MEASURE9,
1601 g.FII_MEASURE10 FII_MEASURE10,
1602 g.FII_MEASURE11 FII_MEASURE11,
1603 g.FII_MEASURE12 FII_MEASURE12,
1604 g.FII_MEASURE13 FII_MEASURE13,
1605 g.FII_MEASURE14 FII_MEASURE14,
1606 g.FII_MEASURE15 FII_MEASURE15,
1607 g.FII_MEASURE16 FII_MEASURE16,
1608 g.FII_MEASURE18 FII_MEASURE18,
1609 g.FII_MEASURE21 FII_MEASURE21,
1610 g.FII_MEASURE22 FII_MEASURE22,
1611 g.FII_MEASURE23 FII_MEASURE23,
1612 g.FII_MEASURE24 FII_MEASURE24,
1613 g.FII_MEASURE25 FII_MEASURE25,
1614 g.FII_MEASURE26 FII_MEASURE26,
1615 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
1616 FROM
1617 (
1618 SELECT f.invoice_number FII_MEASURE1,
1619 f.invoice_id FII_MEASURE2,
1620 f.invoice_type FII_MEASURE3,
1621 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
1622 to_char(f.entered_date, '''||l_date_mask||''') FII_MEASURE5,
1623 to_char(MIN(fpay.due_date), '''||l_date_mask||''') FII_MEASURE6,
1624 f.invoice_currency_code FII_MEASURE7,
1625 f.invoice_amount FII_MEASURE8,
1626 f.'||l_invoice_amount||' FII_MEASURE9,
1627 sum(fpay.'||l_payment_amount||') FII_MEASURE10,
1628 decode(nvl(hold1.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
1629 nvl(hold.FII_MEASURE12,0) FII_MEASURE12,
1630 nvl(hold1.FII_MEASURE13,0) FII_MEASURE13,
1631 f.dist_count FII_MEASURE14,
1632 t.name FII_MEASURE15,
1633 f.source FII_MEASURE16,
1634 max(fpay.check_id) FII_MEASURE18,
1635 to_number(null) FII_MEASURE21,
1636
1637 sum(f.'||l_invoice_amount||') over() FII_MEASURE22,
1638 sum(sum(fpay.'||l_payment_amount||')) over() FII_MEASURE23,
1639 sum(nvl(hold.FII_MEASURE12,0)) over() FII_MEASURE24,
1640 sum(nvl(hold1.FII_MEASURE13,0)) over() FII_MEASURE25,
1641 sum(f.dist_count) over() FII_MEASURE26
1642 FROM fii_ap_invoice_b f,
1643 fii_ap_pay_sched_b fpay,
1644 ap_terms_tl t,
1645 (SELECT invoice_id,
1646 COUNT(hold_date) FII_MEASURE12
1647 FROM fii_ap_inv_holds_b f
1648 WHERE hold_date BETWEEN :PERIOD_START AND &BIS_CURRENT_ASOF_DATE
1649 '||l_org_where||' '||l_sup_where||'
1650 GROUP BY invoice_id
1651 ) hold,
1652 (SELECT invoice_id,
1653 ''Y'' FII_MEASURE11,
1654 sum(days_on_hold) FII_MEASURE13
1655 FROM fii_ap_hhist_ib_mv f
1656 WHERE 1 =1
1657 '||l_org_where||' '||l_sup_where||'
1658 GROUP BY invoice_id
1659 ) hold1
1660 WHERE f.invoice_type = ''AWT''
1661 '||l_org_where||' '||l_sup_where||'
1662 AND f.cancel_flag = ''N'' /*Added code for bug. no.3084280 */
1663 AND f.entered_date <= &BIS_CURRENT_ASOF_DATE
1664 AND f.entered_date >= :PERIOD_START
1665 AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added code for bug no.3113879*/
1666 AND f.invoice_id = fpay.invoice_id
1667 AND t.term_id = f.terms_id
1668 AND t.language = userenv(''LANG'')
1669 AND f.invoice_id =hold.invoice_id (+)
1670 AND f.invoice_id =hold1.invoice_id (+)
1671 GROUP BY f.invoice_number,
1672 f.invoice_id,
1673 f.invoice_type,
1674 f.invoice_date,
1675 f.entered_date,
1676 f.invoice_currency_code,
1677 f.invoice_amount,
1678 f.'||l_invoice_amount||',
1679 hold1.FII_MEASURE11,
1680 hold1.FII_MEASURE13,
1681 f.'||l_discount_offered||',
1682 hold.FII_MEASURE12,
1683 f.dist_count,
1684 t.name,
1685 f.source) g ) h
1686 WHERE (rnk between &START_INDEX AND &END_INDEX or &END_INDEX = -1)
1687 &ORDER_BY_CLAUSE
1688 ';
1689
1690 /*Changed the query for bug no.3074836 . Changes similar to bug no.3063372*/
1691
1692
1693 WHEN 'FII_AP_PREPAYMENT_INV_ENT_DTL' THEN -- 12th, Prepayment Invoices Entered Detail
1694 sqlstmt := '
1695 SELECT
1696 h.FII_MEASURE1 FII_MEASURE1,
1697 h.FII_MEASURE2 FII_MEASURE2,
1698 h.FII_MEASURE3 FII_MEASURE3,
1699 h.FII_MEASURE4 FII_MEASURE4,
1700 h.FII_MEASURE5 FII_MEASURE5,
1701 h.FII_MEASURE6 FII_MEASURE6,
1702 h.FII_MEASURE7 FII_MEASURE7,
1703 h.FII_MEASURE8 FII_MEASURE8,
1704 h.FII_MEASURE9 FII_MEASURE9,
1705 h.FII_MEASURE10 FII_MEASURE10,
1706 h.FII_MEASURE11 FII_MEASURE11,
1707 h.FII_MEASURE12 FII_MEASURE12,
1708 h.FII_MEASURE13 FII_MEASURE13,
1709 h.FII_MEASURE14 FII_MEASURE14,
1710 h.FII_MEASURE15 FII_MEASURE15,
1711 h.FII_MEASURE16 FII_MEASURE16,
1712 h.FII_MEASURE18 FII_MEASURE18,
1713 h.FII_MEASURE21 FII_MEASURE21,
1714 h.FII_MEASURE22 FII_MEASURE22,
1715 h.FII_MEASURE23 FII_MEASURE23,
1716 h.FII_MEASURE24 FII_MEASURE24,
1717 h.FII_MEASURE25 FII_MEASURE25,
1718 h.FII_MEASURE26 FII_MEASURE26,
1719 '''||l_url_1||''' FII_ATTRIBUTE10,
1720 '''||l_url_2||''' FII_ATTRIBUTE11,
1721 '''||l_url_4||''' FII_ATTRIBUTE12,
1722 '''||l_url_3||''' FII_ATTRIBUTE13
1723
1724 FROM
1725 (
1726 SELECT
1727 g.FII_MEASURE1 FII_MEASURE1,
1728 g.FII_MEASURE2 FII_MEASURE2,
1729 g.FII_MEASURE3 FII_MEASURE3,
1730 g.FII_MEASURE4 FII_MEASURE4,
1731 g.FII_MEASURE5 FII_MEASURE5,
1732 g.FII_MEASURE6 FII_MEASURE6,
1733 g.FII_MEASURE7 FII_MEASURE7,
1734 g.FII_MEASURE8 FII_MEASURE8,
1735 g.FII_MEASURE9 FII_MEASURE9,
1736 g.FII_MEASURE10 FII_MEASURE10,
1737 g.FII_MEASURE11 FII_MEASURE11,
1738 g.FII_MEASURE12 FII_MEASURE12,
1739 g.FII_MEASURE13 FII_MEASURE13,
1740 g.FII_MEASURE14 FII_MEASURE14,
1741 g.FII_MEASURE15 FII_MEASURE15,
1742 g.FII_MEASURE16 FII_MEASURE16,
1743 g.FII_MEASURE18 FII_MEASURE18,
1744 g.FII_MEASURE21 FII_MEASURE21,
1745 g.FII_MEASURE22 FII_MEASURE22,
1746 g.FII_MEASURE23 FII_MEASURE23,
1747 g.FII_MEASURE24 FII_MEASURE24,
1748 g.FII_MEASURE25 FII_MEASURE25,
1749 g.FII_MEASURE26 FII_MEASURE26,
1750 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
1751 FROM
1752 (
1753 SELECT f.invoice_number FII_MEASURE1,
1754 f.invoice_id FII_MEASURE2,
1755 f.invoice_type FII_MEASURE3,
1756 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
1757 to_char(f.entered_date, '''||l_date_mask||''') FII_MEASURE5,
1758 to_char(MIN(nvl(base.due_date,f.due_Date)), '''||l_date_mask||''') FII_MEASURE6,
1759 f.invoice_currency_code FII_MEASURE7,
1760 f.invoice_amount FII_MEASURE8,
1761 f.'||l_invoice_amount||' FII_MEASURE9,
1762 sum(decode(base.action,''PREPAYMENT'',decode(sign(base.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
1763 nvl(base.'||l_payment_amount||',0)),nvl(base.'||l_payment_amount||' ,0))) FII_MEASURE10, /*changes for bug 3423861*/
1764 decode(nvl(hold1.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
1765 nvl(hold.FII_MEASURE12,0) FII_MEASURE12,
1766 nvl(hold1.FII_MEASURE13,0) FII_MEASURE13,
1767 f.dist_count FII_MEASURE14,
1768 t.name FII_MEASURE15,
1769 f.source FII_MEASURE16,
1770 max(base.check_id) FII_MEASURE18,
1771 to_number(null) FII_MEASURE21,
1772 sum(f.'||l_invoice_amount||') over() FII_MEASURE22,
1773 sum(sum(decode(base.action,''PREPAYMENT'',decode(sign(base.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
1774 nvl(base.'||l_payment_amount||',0)),nvl(base.'||l_payment_amount||' ,0)))) over() FII_MEASURE23, /*changes for bug 3423861*/
1775 sum(nvl(hold.FII_MEASURE12,0)) over() FII_MEASURE24,
1776 sum(nvl(hold1.FII_MEASURE13,0)) over() FII_MEASURE25,
1777 sum(f.dist_count) over() FII_MEASURE26
1778 FROM fii_ap_invoice_b f,
1779 fii_ap_pay_sched_b base,
1780 ap_terms_tl t,
1781 (SELECT invoice_id,
1782 COUNT(hold_date) FII_MEASURE12
1783 FROM fii_ap_inv_holds_b f
1784 WHERE hold_date BETWEEN :PERIOD_START AND &BIS_CURRENT_ASOF_DATE
1785 '||l_org_where||' '||l_sup_where||'
1786 GROUP BY invoice_id
1787 ) hold,
1788 (SELECT invoice_id,
1789 ''Y'' FII_MEASURE11,
1790 sum(days_on_hold) FII_MEASURE13
1791 FROM fii_ap_hhist_ib_mv f
1792 WHERE 1 =1
1793 '||l_org_where||' '||l_sup_where||'
1794 GROUP BY invoice_id
1795 ) hold1
1796 WHERE
1797 f.invoice_type= ''PREPAYMENT''
1798 '||l_org_where||' '||l_sup_where||'
1799 AND f.cancel_flag = ''N'' /*Added code for bug. no.3084280 */
1800 AND f.entered_date <= &BIS_CURRENT_ASOF_DATE
1801 AND f.entered_date >= :PERIOD_START
1802 AND (nvl(base.action_Date,&BIS_CURRENT_ASOF_DATE) <= &BIS_CURRENT_ASOF_DATE /*added code for bug no.3113879*/
1803 OR (base.action = ''PREPAYMENT'') ) /* made changes for bug 3423861*/
1804 AND base.invoice_id (+)= f.invoice_id /*added outer join and the decode condition above for bug no.3063372*/
1805 AND decode(base.supplier_id,null,1,decode(base.supplier_id,f.supplier_id,1,0))=1
1806 AND decode(base.org_id,null,1,decode(base.org_id,f.org_id,1,0))=1
1807 AND t.term_id = f.terms_id
1808 AND t.language = userenv(''LANG'')
1809 AND f.invoice_id =hold.invoice_id (+)
1810 AND f.invoice_id =hold1.invoice_id (+)
1811 GROUP BY f.invoice_number,
1812 f.invoice_id,
1813 f.invoice_type,
1814 f.invoice_date,
1815 f.entered_date,
1816 f.invoice_currency_code,
1817 f.invoice_amount,
1818 f.'||l_invoice_amount||',
1819 hold1.FII_MEASURE11,
1820 hold1.FII_MEASURE13,
1821 f.'||l_discount_offered||',
1822 hold.FII_MEASURE12,
1823 f.dist_count,
1824 t.name,
1825 f.source) g ) h
1826 WHERE (rnk between &START_INDEX AND &END_INDEX or &END_INDEX = -1)
1827 &ORDER_BY_CLAUSE
1828 ';
1829
1830
1831 WHEN 'FII_AP_CREDIT_INV_ENT_DTL' THEN -- 13th, Credit Invoices Entered Detail
1832 sqlstmt := '
1833 SELECT
1834 h.FII_MEASURE1 FII_MEASURE1,
1835 h.FII_MEASURE2 FII_MEASURE2,
1836 h.FII_MEASURE3 FII_MEASURE3,
1837 h.FII_MEASURE4 FII_MEASURE4,
1838 h.FII_MEASURE5 FII_MEASURE5,
1839 h.FII_MEASURE6 FII_MEASURE6,
1840 h.FII_MEASURE7 FII_MEASURE7,
1841 h.FII_MEASURE8 FII_MEASURE8,
1842 h.FII_MEASURE9 FII_MEASURE9,
1843 h.FII_MEASURE10 FII_MEASURE10,
1844 h.FII_MEASURE11 FII_MEASURE11,
1845 h.FII_MEASURE12 FII_MEASURE12,
1846 h.FII_MEASURE13 FII_MEASURE13,
1847 h.FII_MEASURE14 FII_MEASURE14,
1848 h.FII_MEASURE15 FII_MEASURE15,
1849 h.FII_MEASURE16 FII_MEASURE16,
1850 h.FII_MEASURE18 FII_MEASURE18,
1851 h.FII_MEASURE21 FII_MEASURE21,
1852 h.FII_MEASURE22 FII_MEASURE22,
1853 h.FII_MEASURE23 FII_MEASURE23,
1854 h.FII_MEASURE24 FII_MEASURE24,
1855 h.FII_MEASURE25 FII_MEASURE25,
1856 h.FII_MEASURE26 FII_MEASURE26,
1857 '''||l_url_1||''' FII_ATTRIBUTE10,
1858 '''||l_url_2||''' FII_ATTRIBUTE11,
1859 '''||l_url_4||''' FII_ATTRIBUTE12,
1860 '''||l_url_3||''' FII_ATTRIBUTE13
1861
1862 FROM
1863 (
1864 SELECT
1865 g.FII_MEASURE1 FII_MEASURE1,
1866 g.FII_MEASURE2 FII_MEASURE2,
1867 g.FII_MEASURE3 FII_MEASURE3,
1868 g.FII_MEASURE4 FII_MEASURE4,
1869 g.FII_MEASURE5 FII_MEASURE5,
1870 g.FII_MEASURE6 FII_MEASURE6,
1871 g.FII_MEASURE7 FII_MEASURE7,
1872 g.FII_MEASURE8 FII_MEASURE8,
1873 g.FII_MEASURE9 FII_MEASURE9,
1874 g.FII_MEASURE10 FII_MEASURE10,
1875 g.FII_MEASURE11 FII_MEASURE11,
1876 g.FII_MEASURE12 FII_MEASURE12,
1877 g.FII_MEASURE13 FII_MEASURE13,
1878 g.FII_MEASURE14 FII_MEASURE14,
1879 g.FII_MEASURE15 FII_MEASURE15,
1880 g.FII_MEASURE16 FII_MEASURE16,
1881 g.FII_MEASURE18 FII_MEASURE18,
1882 g.FII_MEASURE21 FII_MEASURE21,
1883 g.FII_MEASURE22 FII_MEASURE22,
1884 g.FII_MEASURE23 FII_MEASURE23,
1885 g.FII_MEASURE24 FII_MEASURE24,
1886 g.FII_MEASURE25 FII_MEASURE25,
1887 g.FII_MEASURE26 FII_MEASURE26,
1888 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
1889 FROM
1890 (
1891 SELECT f.invoice_number FII_MEASURE1,
1892 f.invoice_id FII_MEASURE2,
1893 f.invoice_type FII_MEASURE3,
1894 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
1895 to_char(f.entered_date, '''||l_date_mask||''') FII_MEASURE5,
1896 to_char(MIN(fpay.due_date), '''||l_date_mask||''') FII_MEASURE6,
1897 f.invoice_currency_code FII_MEASURE7,
1898 f.invoice_amount FII_MEASURE8,
1899 f.'||l_invoice_amount||' FII_MEASURE9,
1900 sum(fpay.'||l_payment_amount||') FII_MEASURE10,
1901 decode(nvl(hold1.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
1902 nvl(hold.FII_MEASURE12,0) FII_MEASURE12,
1903 nvl(hold1.FII_MEASURE13,0) FII_MEASURE13,
1904 f.dist_count FII_MEASURE14,
1905 t.name FII_MEASURE15,
1906 f.source FII_MEASURE16,
1907 max(fpay.check_id) FII_MEASURE18,
1908 to_number(null) FII_MEASURE21,
1909
1910 sum(f.'||l_invoice_amount||') over() FII_MEASURE22,
1911 sum(sum(fpay.'||l_payment_amount||')) over() FII_MEASURE23,
1912 sum(nvl(hold.FII_MEASURE12,0)) over() FII_MEASURE24,
1913 sum(nvl(hold1.FII_MEASURE13,0)) over() FII_MEASURE25,
1914 sum(f.dist_count) over() FII_MEASURE26
1915 FROM fii_ap_invoice_b f,
1916 fii_ap_pay_sched_b fpay,
1917 ap_terms_tl t,
1918 (SELECT invoice_id,
1919 COUNT(hold_date) FII_MEASURE12
1920 FROM fii_ap_inv_holds_b f
1921 WHERE hold_date BETWEEN :PERIOD_START AND &BIS_CURRENT_ASOF_DATE
1922 '||l_org_where||' '||l_sup_where||'
1923 GROUP BY invoice_id
1924 ) hold,
1925 (SELECT invoice_id,
1926 ''Y'' FII_MEASURE11,
1927 sum(days_on_hold) FII_MEASURE13
1928 FROM fii_ap_hhist_ib_mv f
1929 WHERE 1 =1
1930 '||l_org_where||' '||l_sup_where||'
1931 GROUP BY invoice_id
1932 ) hold1
1933 WHERE f.invoice_type = ''CREDIT''
1934 '||l_org_where||' '||l_sup_where||'
1935 AND f.cancel_flag = ''N'' /*Added code for bug. no.3084280 */
1936 AND f.entered_date <= &BIS_CURRENT_ASOF_DATE
1937 AND f.entered_date >= :PERIOD_START
1938 AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added code for bug no.3113879*/
1939 AND f.invoice_id = fpay.invoice_id
1940 AND t.term_id = f.terms_id
1941 AND t.language = userenv(''LANG'')
1942 AND f.invoice_id =hold.invoice_id (+)
1943 AND f.invoice_id =hold1.invoice_id (+)
1944 GROUP BY f.invoice_number,
1945 f.invoice_id,
1946 f.invoice_type,
1947 f.invoice_date,
1948 f.entered_date,
1949 f.invoice_currency_code,
1950 f.invoice_amount,
1951 f.'||l_invoice_amount||',
1952 hold1.FII_MEASURE11,
1953 hold1.FII_MEASURE13,
1954 f.'||l_discount_offered||',
1955 hold.FII_MEASURE12,
1956 f.dist_count,
1957 t.name,
1958 f.source) g ) h
1959 WHERE (rnk between &START_INDEX AND &END_INDEX or &END_INDEX = -1)
1960 &ORDER_BY_CLAUSE
1961 ';
1962
1963
1964 WHEN 'FII_AP_DEBIT_INV_ENT_DTL' THEN -- 14th, Debit Invoices Entered Detail
1965 sqlstmt := '
1966 SELECT
1967 h.FII_MEASURE1 FII_MEASURE1,
1968 h.FII_MEASURE2 FII_MEASURE2,
1969 h.FII_MEASURE3 FII_MEASURE3,
1970 h.FII_MEASURE4 FII_MEASURE4,
1971 h.FII_MEASURE5 FII_MEASURE5,
1972 h.FII_MEASURE6 FII_MEASURE6,
1973 h.FII_MEASURE7 FII_MEASURE7,
1974 h.FII_MEASURE8 FII_MEASURE8,
1975 h.FII_MEASURE9 FII_MEASURE9,
1976 h.FII_MEASURE10 FII_MEASURE10,
1977 h.FII_MEASURE11 FII_MEASURE11,
1978 h.FII_MEASURE12 FII_MEASURE12,
1979 h.FII_MEASURE13 FII_MEASURE13,
1980 h.FII_MEASURE14 FII_MEASURE14,
1981 h.FII_MEASURE15 FII_MEASURE15,
1982 h.FII_MEASURE16 FII_MEASURE16,
1983 h.FII_MEASURE18 FII_MEASURE18,
1984 h.FII_MEASURE21 FII_MEASURE21,
1985 h.FII_MEASURE22 FII_MEASURE22,
1986 h.FII_MEASURE23 FII_MEASURE23,
1987 h.FII_MEASURE24 FII_MEASURE24,
1988 h.FII_MEASURE25 FII_MEASURE25,
1989 h.FII_MEASURE26 FII_MEASURE26,
1990 '''||l_url_1||''' FII_ATTRIBUTE10,
1991 '''||l_url_2||''' FII_ATTRIBUTE11,
1992 '''||l_url_4||''' FII_ATTRIBUTE12,
1993 '''||l_url_3||''' FII_ATTRIBUTE13
1994
1995 FROM
1996 (
1997 SELECT
1998 g.FII_MEASURE1 FII_MEASURE1,
1999 g.FII_MEASURE2 FII_MEASURE2,
2000 g.FII_MEASURE3 FII_MEASURE3,
2001 g.FII_MEASURE4 FII_MEASURE4,
2002 g.FII_MEASURE5 FII_MEASURE5,
2003 g.FII_MEASURE6 FII_MEASURE6,
2004 g.FII_MEASURE7 FII_MEASURE7,
2005 g.FII_MEASURE8 FII_MEASURE8,
2006 g.FII_MEASURE9 FII_MEASURE9,
2007 g.FII_MEASURE10 FII_MEASURE10,
2008 g.FII_MEASURE11 FII_MEASURE11,
2009 g.FII_MEASURE12 FII_MEASURE12,
2010 g.FII_MEASURE13 FII_MEASURE13,
2011 g.FII_MEASURE14 FII_MEASURE14,
2012 g.FII_MEASURE15 FII_MEASURE15,
2013 g.FII_MEASURE16 FII_MEASURE16,
2014 g.FII_MEASURE18 FII_MEASURE18,
2015 g.FII_MEASURE21 FII_MEASURE21,
2016 g.FII_MEASURE22 FII_MEASURE22,
2017 g.FII_MEASURE23 FII_MEASURE23,
2018 g.FII_MEASURE24 FII_MEASURE24,
2019 g.FII_MEASURE25 FII_MEASURE25,
2020 g.FII_MEASURE26 FII_MEASURE26,
2021 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
2022 FROM
2023 (
2024 SELECT f.invoice_number FII_MEASURE1,
2025 f.invoice_id FII_MEASURE2,
2026 f.invoice_type FII_MEASURE3,
2027 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
2028 to_char(f.entered_date, '''||l_date_mask||''') FII_MEASURE5,
2029 to_char(MIN(fpay.due_date), '''||l_date_mask||''') FII_MEASURE6,
2030 f.invoice_currency_code FII_MEASURE7,
2031 f.invoice_amount FII_MEASURE8,
2032 f.'||l_invoice_amount||' FII_MEASURE9,
2033 sum(fpay.'||l_payment_amount||') FII_MEASURE10,
2034 decode(nvl(hold1.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
2035 nvl(hold.FII_MEASURE12,0) FII_MEASURE12,
2036 nvl(hold1.FII_MEASURE13,0) FII_MEASURE13,
2037 f.dist_count FII_MEASURE14,
2038 t.name FII_MEASURE15,
2039 f.source FII_MEASURE16,
2040 max(fpay.check_id) FII_MEASURE18,
2041 to_number(null) FII_MEASURE21,
2042
2043 sum(f.'||l_invoice_amount||') over() FII_MEASURE22,
2044 sum(sum(fpay.'||l_payment_amount||')) over() FII_MEASURE23,
2045 sum(nvl(hold.FII_MEASURE12,0)) over() FII_MEASURE24,
2046 sum(nvl(hold1.FII_MEASURE13,0)) over() FII_MEASURE25,
2047 sum(f.dist_count) over() FII_MEASURE26
2048 FROM fii_ap_invoice_b f,
2049 fii_ap_pay_sched_b fpay,
2050 ap_terms_tl t,
2051 (SELECT invoice_id,
2052 COUNT(hold_date) FII_MEASURE12
2053 FROM fii_ap_inv_holds_b f
2054 WHERE hold_date BETWEEN :PERIOD_START AND &BIS_CURRENT_ASOF_DATE
2055 '||l_org_where||' '||l_sup_where||'
2056 GROUP BY invoice_id
2057 ) hold,
2058 (SELECT invoice_id,
2059 ''Y'' FII_MEASURE11,
2060 sum(days_on_hold) FII_MEASURE13
2061 FROM fii_ap_hhist_ib_mv f
2062 WHERE 1 =1
2063 '||l_org_where||' '||l_sup_where||'
2064 GROUP BY invoice_id
2065 ) hold1
2066 WHERE f.invoice_type = ''DEBIT''
2067 '||l_org_where||' '||l_sup_where||'
2068 AND f.cancel_flag = ''N'' /*Added code for bug. no.3084280 */
2069 AND f.entered_date <= &BIS_CURRENT_ASOF_DATE
2070 AND f.entered_date >= :PERIOD_START
2071 AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added code for bug no.3113879*/
2072 AND f.invoice_id = fpay.invoice_id
2073 AND t.term_id = f.terms_id
2074 AND t.language = userenv(''LANG'')
2075 AND f.invoice_id =hold.invoice_id (+)
2076 AND f.invoice_id =hold1.invoice_id (+)
2077 GROUP BY f.invoice_number,
2078 f.invoice_id,
2079 f.invoice_type,
2080 f.invoice_date,
2081 f.entered_date,
2082 f.invoice_currency_code,
2083 f.invoice_amount,
2084 f.'||l_invoice_amount||',
2085 hold1.FII_MEASURE11,
2086 hold1.FII_MEASURE13,
2087 f.'||l_discount_offered||',
2088 hold.FII_MEASURE12,
2089 f.dist_count,
2090 t.name,
2091 f.source) g ) h
2092 WHERE (rnk between &START_INDEX AND &END_INDEX or &END_INDEX = -1)
2093 &ORDER_BY_CLAUSE
2094 ';
2095
2096
2097 WHEN 'FII_AP_MIXED_INV_ENT_DTL' THEN -- 15th, Mixed Invoices Entered Detail
2098 sqlstmt := '
2099 SELECT
2100 h.FII_MEASURE1 FII_MEASURE1,
2101 h.FII_MEASURE2 FII_MEASURE2,
2102 h.FII_MEASURE3 FII_MEASURE3,
2103 h.FII_MEASURE4 FII_MEASURE4,
2104 h.FII_MEASURE5 FII_MEASURE5,
2105 h.FII_MEASURE6 FII_MEASURE6,
2106 h.FII_MEASURE7 FII_MEASURE7,
2107 h.FII_MEASURE8 FII_MEASURE8,
2108 h.FII_MEASURE9 FII_MEASURE9,
2109 h.FII_MEASURE10 FII_MEASURE10,
2110 h.FII_MEASURE11 FII_MEASURE11,
2111 h.FII_MEASURE12 FII_MEASURE12,
2112 h.FII_MEASURE13 FII_MEASURE13,
2113 h.FII_MEASURE14 FII_MEASURE14,
2114 h.FII_MEASURE15 FII_MEASURE15,
2115 h.FII_MEASURE16 FII_MEASURE16,
2116 h.FII_MEASURE18 FII_MEASURE18,
2117 h.FII_MEASURE21 FII_MEASURE21,
2118 h.FII_MEASURE22 FII_MEASURE22,
2119 h.FII_MEASURE23 FII_MEASURE23,
2120 h.FII_MEASURE24 FII_MEASURE24,
2121 h.FII_MEASURE25 FII_MEASURE25,
2122 h.FII_MEASURE26 FII_MEASURE26,
2123 '''||l_url_1||''' FII_ATTRIBUTE10,
2124 '''||l_url_2||''' FII_ATTRIBUTE11,
2125 '''||l_url_4||''' FII_ATTRIBUTE12,
2126 '''||l_url_3||''' FII_ATTRIBUTE13
2127
2128 FROM
2129 (
2130 SELECT
2131 g.FII_MEASURE1 FII_MEASURE1,
2132 g.FII_MEASURE2 FII_MEASURE2,
2133 g.FII_MEASURE3 FII_MEASURE3,
2134 g.FII_MEASURE4 FII_MEASURE4,
2135 g.FII_MEASURE5 FII_MEASURE5,
2136 g.FII_MEASURE6 FII_MEASURE6,
2137 g.FII_MEASURE7 FII_MEASURE7,
2138 g.FII_MEASURE8 FII_MEASURE8,
2139 g.FII_MEASURE9 FII_MEASURE9,
2140 g.FII_MEASURE10 FII_MEASURE10,
2141 g.FII_MEASURE11 FII_MEASURE11,
2142 g.FII_MEASURE12 FII_MEASURE12,
2143 g.FII_MEASURE13 FII_MEASURE13,
2144 g.FII_MEASURE14 FII_MEASURE14,
2145 g.FII_MEASURE15 FII_MEASURE15,
2146 g.FII_MEASURE16 FII_MEASURE16,
2147 g.FII_MEASURE18 FII_MEASURE18,
2148 g.FII_MEASURE21 FII_MEASURE21,
2149 g.FII_MEASURE22 FII_MEASURE22,
2150 g.FII_MEASURE23 FII_MEASURE23,
2151 g.FII_MEASURE24 FII_MEASURE24,
2152 g.FII_MEASURE25 FII_MEASURE25,
2153 g.FII_MEASURE26 FII_MEASURE26,
2154 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
2155 FROM
2156 (
2157 SELECT f.invoice_number FII_MEASURE1,
2158 f.invoice_id FII_MEASURE2,
2159 f.invoice_type FII_MEASURE3,
2160 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
2161 to_char(f.entered_date, '''||l_date_mask||''') FII_MEASURE5,
2162 to_char(MIN(fpay.due_date), '''||l_date_mask||''') FII_MEASURE6,
2163 f.invoice_currency_code FII_MEASURE7,
2164 f.invoice_amount FII_MEASURE8,
2165 f.'||l_invoice_amount||' FII_MEASURE9,
2166 sum(fpay.'||l_payment_amount||') FII_MEASURE10,
2167 decode(nvl(hold1.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
2168 nvl(hold.FII_MEASURE12,0) FII_MEASURE12,
2169 nvl(hold1.FII_MEASURE13,0) FII_MEASURE13,
2170 f.dist_count FII_MEASURE14,
2171 t.name FII_MEASURE15,
2172 f.source FII_MEASURE16,
2173 max(fpay.check_id) FII_MEASURE18,
2174 to_number(null) FII_MEASURE21,
2175
2176 sum(f.'||l_invoice_amount||') over() FII_MEASURE22,
2177 sum(sum(fpay.'||l_payment_amount||')) over() FII_MEASURE23,
2178 sum(nvl(hold.FII_MEASURE12,0)) over() FII_MEASURE24,
2179 sum(nvl(hold1.FII_MEASURE13,0)) over() FII_MEASURE25,
2180 sum(f.dist_count) over() FII_MEASURE26
2181 FROM fii_ap_invoice_b f,
2182 fii_ap_pay_sched_b fpay,
2183 ap_terms_tl t,
2184 (SELECT invoice_id,
2185 COUNT(hold_date) FII_MEASURE12
2186 FROM fii_ap_inv_holds_b f
2187 WHERE hold_date BETWEEN :PERIOD_START AND &BIS_CURRENT_ASOF_DATE
2188 '||l_org_where||' '||l_sup_where||'
2189 GROUP BY invoice_id
2190 ) hold,
2191 (SELECT invoice_id,
2192 ''Y'' FII_MEASURE11,
2193 sum(days_on_hold) FII_MEASURE13
2194 FROM fii_ap_hhist_ib_mv f
2195 WHERE 1 =1
2196 '||l_org_where||' '||l_sup_where||'
2197 GROUP BY invoice_id
2198 ) hold1
2199 WHERE f.invoice_type = ''MIXED''
2200 '||l_org_where||' '||l_sup_where||'
2201 AND f.cancel_flag = ''N'' /*Added code for bug. no.3084280 */
2202 AND f.entered_date <= &BIS_CURRENT_ASOF_DATE
2203 AND f.entered_date >= :PERIOD_START
2204 AND f.invoice_id = fpay.invoice_id
2205 AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added code for bug no.3113879*/
2206 AND t.term_id = f.terms_id
2207 AND t.language = userenv(''LANG'')
2208 AND f.invoice_id =hold.invoice_id (+)
2209 AND f.invoice_id =hold1.invoice_id (+)
2210 GROUP BY f.invoice_number,
2211 f.invoice_id,
2212 f.invoice_type,
2213 f.invoice_date,
2214 f.entered_date,
2215 f.invoice_currency_code,
2216 f.invoice_amount,
2217 f.'||l_invoice_amount||',
2218 hold1.FII_MEASURE11,
2219 hold1.FII_MEASURE13,
2220 f.'||l_discount_offered||',
2221 hold.FII_MEASURE12,
2222 f.dist_count,
2223 t.name,
2224 f.source) g ) h
2225 WHERE (rnk between &START_INDEX AND &END_INDEX or &END_INDEX = -1)
2226 &ORDER_BY_CLAUSE
2227 ';
2228
2229
2230 WHEN 'FII_AP_INTEREST_INV_ENT_DTL' THEN -- 16th, Interest Invoices Entered Detail
2231 sqlstmt := '
2232 SELECT
2233 h.FII_MEASURE1 FII_MEASURE1,
2234 h.FII_MEASURE2 FII_MEASURE2,
2235 h.FII_MEASURE3 FII_MEASURE3,
2236 h.FII_MEASURE4 FII_MEASURE4,
2237 h.FII_MEASURE5 FII_MEASURE5,
2238 h.FII_MEASURE6 FII_MEASURE6,
2239 h.FII_MEASURE7 FII_MEASURE7,
2240 h.FII_MEASURE8 FII_MEASURE8,
2241 h.FII_MEASURE9 FII_MEASURE9,
2242 h.FII_MEASURE10 FII_MEASURE10,
2243 h.FII_MEASURE11 FII_MEASURE11,
2244 h.FII_MEASURE12 FII_MEASURE12,
2245 h.FII_MEASURE13 FII_MEASURE13,
2246 h.FII_MEASURE14 FII_MEASURE14,
2247 h.FII_MEASURE15 FII_MEASURE15,
2248 h.FII_MEASURE16 FII_MEASURE16,
2249 h.FII_MEASURE18 FII_MEASURE18,
2250 h.FII_MEASURE21 FII_MEASURE21,
2251 h.FII_MEASURE22 FII_MEASURE22,
2252 h.FII_MEASURE23 FII_MEASURE23,
2253 h.FII_MEASURE24 FII_MEASURE24,
2254 h.FII_MEASURE25 FII_MEASURE25,
2255 h.FII_MEASURE26 FII_MEASURE26,
2256 '''||l_url_1||''' FII_ATTRIBUTE10,
2257 '''||l_url_2||''' FII_ATTRIBUTE11,
2258 '''||l_url_4||''' FII_ATTRIBUTE12,
2259 '''||l_url_3||''' FII_ATTRIBUTE13
2260
2261 FROM
2262 (
2263 SELECT
2264 g.FII_MEASURE1 FII_MEASURE1,
2265 g.FII_MEASURE2 FII_MEASURE2,
2266 g.FII_MEASURE3 FII_MEASURE3,
2267 g.FII_MEASURE4 FII_MEASURE4,
2268 g.FII_MEASURE5 FII_MEASURE5,
2269 g.FII_MEASURE6 FII_MEASURE6,
2270 g.FII_MEASURE7 FII_MEASURE7,
2271 g.FII_MEASURE8 FII_MEASURE8,
2272 g.FII_MEASURE9 FII_MEASURE9,
2273 g.FII_MEASURE10 FII_MEASURE10,
2274 g.FII_MEASURE11 FII_MEASURE11,
2275 g.FII_MEASURE12 FII_MEASURE12,
2276 g.FII_MEASURE13 FII_MEASURE13,
2277 g.FII_MEASURE14 FII_MEASURE14,
2278 g.FII_MEASURE15 FII_MEASURE15,
2279 g.FII_MEASURE16 FII_MEASURE16,
2280 g.FII_MEASURE18 FII_MEASURE18,
2281 g.FII_MEASURE21 FII_MEASURE21,
2282 g.FII_MEASURE22 FII_MEASURE22,
2283 g.FII_MEASURE23 FII_MEASURE23,
2284 g.FII_MEASURE24 FII_MEASURE24,
2285 g.FII_MEASURE25 FII_MEASURE25,
2286 g.FII_MEASURE26 FII_MEASURE26,
2287 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
2288 FROM
2289 (
2290 SELECT f.invoice_number FII_MEASURE1,
2291 f.invoice_id FII_MEASURE2,
2292 f.invoice_type FII_MEASURE3,
2293 to_char(f.invoice_date, '''||l_date_mask||''') FII_MEASURE4,
2294 to_char(f.entered_date, '''||l_date_mask||''') FII_MEASURE5,
2295 to_char(MIN(fpay.due_date), '''||l_date_mask||''') FII_MEASURE6,
2296 f.invoice_currency_code FII_MEASURE7,
2297 f.invoice_amount FII_MEASURE8,
2298 f.'||l_invoice_amount||' FII_MEASURE9,
2299 sum(fpay.'||l_payment_amount||') FII_MEASURE10,
2300 decode(nvl(hold1.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
2301 nvl(hold.FII_MEASURE12,0) FII_MEASURE12,
2302 nvl(hold1.FII_MEASURE13,0) FII_MEASURE13,
2303 f.dist_count FII_MEASURE14,
2304 t.name FII_MEASURE15,
2305 f.source FII_MEASURE16,
2306 max(fpay.check_id) FII_MEASURE18,
2307 to_number(null) FII_MEASURE21,
2308 sum(f.'||l_invoice_amount||') over() FII_MEASURE22,
2309 sum(sum(fpay.'||l_payment_amount||')) over() FII_MEASURE23,
2310 sum(nvl(hold.FII_MEASURE12,0)) over() FII_MEASURE24,
2311 sum(nvl(hold1.FII_MEASURE13,0)) over() FII_MEASURE25,
2312 sum(f.dist_count) over() FII_MEASURE26
2313 FROM fii_ap_invoice_b f,
2314 fii_ap_pay_sched_b fpay,
2315 ap_terms_tl t,
2316 (SELECT invoice_id,
2317 COUNT(hold_date) FII_MEASURE12
2318 FROM fii_ap_inv_holds_b f
2319 WHERE hold_date BETWEEN :PERIOD_START AND &BIS_CURRENT_ASOF_DATE
2320 '||l_org_where||' '||l_sup_where||'
2321 GROUP BY invoice_id
2322 ) hold,
2323 (SELECT invoice_id,
2324 ''Y'' FII_MEASURE11,
2325 sum(days_on_hold) FII_MEASURE13
2326 FROM fii_ap_hhist_ib_mv f
2327 WHERE 1 =1
2328 '||l_org_where||' '||l_sup_where||'
2329 GROUP BY invoice_id
2330 ) hold1
2331 WHERE f.invoice_type = ''INTEREST''
2332 '||l_org_where||' '||l_sup_where||'
2333 AND f.cancel_flag = ''N'' /*Added code for bug. no.3084280 */
2334 AND f.entered_date <= &BIS_CURRENT_ASOF_DATE
2335 AND f.entered_date >= :PERIOD_START
2336 AND f.invoice_id = fpay.invoice_id
2337 AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE /*added code for bug no.3113879*/
2338 AND t.term_id = f.terms_id
2339 AND t.language = userenv(''LANG'')
2340 AND f.invoice_id =hold.invoice_id (+)
2341 AND f.invoice_id =hold1.invoice_id (+)
2342 GROUP BY f.invoice_number,
2343 f.invoice_id,
2344 f.invoice_type,
2345 f.invoice_date,
2346 f.entered_date,
2347 f.invoice_currency_code,
2348 f.invoice_amount,
2349 f.'||l_invoice_amount||',
2350 hold1.FII_MEASURE11,
2351 hold1.FII_MEASURE13,
2352 f.'||l_discount_offered||',
2353 hold.FII_MEASURE12,
2354 f.dist_count,
2355 t.name,
2356 f.source) g ) h
2357 WHERE (rnk between &START_INDEX AND &END_INDEX or &END_INDEX = -1)
2358 &ORDER_BY_CLAUSE
2359 ';
2360
2361
2362 WHEN 'FII_AP_INV_HOLD_ACTIVITY_DTL' THEN -- 17th, Invoices Placed on Hold Detail
2363
2364 sqlstmt := '
2365 SELECT
2366 h.FII_MEASURE1 FII_MEASURE1,
2367 h.FII_MEASURE2 FII_MEASURE2,
2368 h.FII_MEASURE3 FII_MEASURE3,
2369 h.FII_MEASURE4 FII_MEASURE4,
2370 h.FII_MEASURE5 FII_MEASURE5,
2371 h.FII_MEASURE6 FII_MEASURE6,
2372 h.FII_MEASURE7 FII_MEASURE7,
2373 h.FII_MEASURE8 FII_MEASURE8,
2374 h.FII_MEASURE9 FII_MEASURE9,
2375 h.FII_MEASURE10 FII_MEASURE10,
2376 h.FII_MEASURE11 FII_MEASURE11,
2377 h.FII_MEASURE12 FII_MEASURE12,
2378 h.FII_MEASURE13 FII_MEASURE13,
2379 h.FII_MEASURE14 FII_MEASURE14,
2380 h.FII_MEASURE15 FII_MEASURE15,
2381 h.FII_MEASURE16 FII_MEASURE16,
2382 h.FII_MEASURE18 FII_MEASURE18,
2383 h.FII_MEASURE21 FII_MEASURE21,
2384 h.FII_MEASURE22 FII_MEASURE22,
2385 h.FII_MEASURE23 FII_MEASURE23,
2386 h.FII_MEASURE24 FII_MEASURE24,
2387 h.FII_MEASURE25 FII_MEASURE25,
2388 h.FII_MEASURE26 FII_MEASURE26,
2389 '''||l_url_1||''' FII_ATTRIBUTE10,
2390 '''||l_url_2||''' FII_ATTRIBUTE11,
2391 '''||l_url_4||''' FII_ATTRIBUTE12,
2392 '''||l_url_3||''' FII_ATTRIBUTE13
2393
2394 FROM
2395 (
2396 SELECT
2397 g.FII_MEASURE1 FII_MEASURE1,
2398 g.FII_MEASURE2 FII_MEASURE2,
2399 g.FII_MEASURE3 FII_MEASURE3,
2400 g.FII_MEASURE4 FII_MEASURE4,
2401 g.FII_MEASURE5 FII_MEASURE5,
2402 g.FII_MEASURE6 FII_MEASURE6,
2403 g.FII_MEASURE7 FII_MEASURE7,
2404 g.FII_MEASURE8 FII_MEASURE8,
2405 g.FII_MEASURE9 FII_MEASURE9,
2406 g.FII_MEASURE10 FII_MEASURE10,
2407 g.FII_MEASURE11 FII_MEASURE11,
2408 g.FII_MEASURE12 FII_MEASURE12,
2409 g.FII_MEASURE13 FII_MEASURE13,
2410 g.FII_MEASURE14 FII_MEASURE14,
2411 g.FII_MEASURE15 FII_MEASURE15,
2412 g.FII_MEASURE16 FII_MEASURE16,
2413 g.FII_MEASURE18 FII_MEASURE18,
2414 g.FII_MEASURE21 FII_MEASURE21,
2415 g.FII_MEASURE22 FII_MEASURE22,
2416 g.FII_MEASURE23 FII_MEASURE23,
2417 g.FII_MEASURE24 FII_MEASURE24,
2418 g.FII_MEASURE25 FII_MEASURE25,
2419 g.FII_MEASURE26 FII_MEASURE26,
2420 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
2421 FROM
2422 (
2423 SELECT
2424 f.invoice_number FII_MEASURE1,
2425 f.invoice_id FII_MEASURE2,
2426 f.invoice_type FII_MEASURE3,
2427 to_char(f.invoice_date, '''||l_date_mask||''')
2428 FII_MEASURE4,
2429 to_char(f.entered_date, '''||l_date_mask||''')
2430 FII_MEASURE5,
2431 to_char(pay.due_date, '''||l_date_mask||''') FII_MEASURE6,
2432 f.invoice_currency_code FII_MEASURE7,
2433 f.invoice_amount FII_MEASURE8,
2434 f.'||l_invoice_amount||' FII_MEASURE9,
2435 nvl(pay.payment_amount, 0) FII_MEASURE10,
2436 decode(nvl(hold1.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''',
2437 ''N'','''||l_no||''') FII_MEASURE11,
2438 nvl(hold.FII_MEASURE12,0) FII_MEASURE12,
2439 nvl(hold1.FII_MEASURE13,0) FII_MEASURE13,
2440 f.dist_count FII_MEASURE14,
2441 t.name FII_MEASURE15,
2442 f.source FII_MEASURE16,
2443 null FII_MEASURE18,
2444 to_number(null) FII_MEASURE21,
2445 sum(f.'||l_invoice_amount||') over() FII_MEASURE22,
2446 sum(nvl(pay.payment_amount, 0)) over() FII_MEASURE23,
2447 sum(nvl(hold.FII_MEASURE12,0)) over() FII_MEASURE24,
2448 sum(nvl(hold1.FII_MEASURE13,0)) over() FII_MEASURE25,
2449 sum(f.dist_count) over() FII_MEASURE26
2450 FROM fii_ap_invoice_b f,
2451 (SELECT invoice_id,
2452 COUNT(hold_date) FII_MEASURE12
2453 FROM fii_ap_inv_holds_b f
2454 WHERE hold_date BETWEEN :PERIOD_START AND &BIS_CURRENT_ASOF_DATE
2455 '||l_org_where||' '||l_sup_where||'
2456 GROUP BY invoice_id
2457 ) hold,
2458 (SELECT invoice_id,
2459 ''Y'' FII_MEASURE11,
2460 sum(days_on_hold) FII_MEASURE13
2461 FROM fii_ap_hhist_ib_mv f
2462 WHERE 1 =1
2463 '||l_org_where||' '||l_sup_where||'
2464 GROUP BY invoice_id
2465 ) hold1,
2466 (SELECT fpay.invoice_id,
2467 sum(fpay.'||l_payment_amount||') payment_amount,
2468 min(fpay.due_date) due_date
2469 FROM fii_ap_pay_sched_b fpay
2470 WHERE fpay.action_date >= :PERIOD_START
2471 AND fpay.action_date <= &BIS_CURRENT_ASOF_DATE
2472 AND fpay.action <> ''PREPAYMENT''
2473 AND fpay.invoice_id in
2474 (SELECT f.invoice_id
2475 FROM fii_ap_inv_holds_b f
2476 WHERE f.hold_date BETWEEN :PERIOD_START AND &BIS_CURRENT_ASOF_DATE
2477 '||l_org_where||' '||l_sup_where||'
2478 )
2479 GROUP BY fpay.invoice_id
2480 ) pay,
2481 ap_terms_tl t
2482 WHERE f.cancel_flag = ''N'' /*Added code for bug. no.3084280 */
2483 AND f.invoice_id = hold.invoice_id
2484 AND f.invoice_id = pay.invoice_id(+)
2485 AND f.invoice_id = hold1.invoice_id
2486 AND hold.invoice_id = hold1.invoice_id
2487 AND t.term_id = f.terms_id
2488 AND t.language = userenv(''LANG'')
2489 GROUP BY f.invoice_number,
2490 f.invoice_id,
2491 f.invoice_type,
2492 f.invoice_date,
2493 f.entered_date,
2494 f.invoice_currency_code,
2495 f.invoice_amount,
2496 f.'||l_invoice_amount||',
2497 hold1.FII_MEASURE11,
2498 hold.FII_MEASURE12,
2499 hold1.FII_MEASURE13,
2500 f.'||l_discount_offered||',
2501 f.dist_count,
2502 t.name,
2503 f.source,
2504 pay.payment_amount,
2505 pay.due_date) g ) h
2506 WHERE (rnk between &START_INDEX AND &END_INDEX or &END_INDEX = -1)
2507 &ORDER_BY_CLAUSE
2508 ';
2509
2510
2511 WHEN 'FII_EA_AP_TRAN' THEN -- Drill from Expense Analysis Payables Invoices report
2512 sqlstmt := '
2513 Select
2514 h.FII_MEASURE1 FII_MEASURE1,
2515 h.FII_MEASURE2 FII_MEASURE2,
2516 h.FII_MEASURE3 FII_MEASURE3,
2517 h.FII_MEASURE4 FII_MEASURE4,
2518 h.FII_MEASURE5 FII_MEASURE5,
2519 h.FII_MEASURE6 FII_MEASURE6,
2520 h.FII_MEASURE7 FII_MEASURE7,
2521 h.FII_MEASURE8 FII_MEASURE8,
2522 h.FII_MEASURE9 FII_MEASURE9,
2523 h.FII_MEASURE10 FII_MEASURE10,
2524 h.FII_MEASURE11 FII_MEASURE11,
2525 h.FII_MEASURE12 FII_MEASURE12,
2526 h.FII_MEASURE13 FII_MEASURE13,
2527 h.FII_MEASURE14 FII_MEASURE14,
2528 h.FII_MEASURE15 FII_MEASURE15,
2529 h.FII_MEASURE16 FII_MEASURE16,
2530 h.FII_MEASURE18 FII_MEASURE18,
2531 h.FII_MEASURE21 FII_MEASURE21,
2532 h.FII_MEASURE22 FII_MEASURE22,
2533 h.FII_MEASURE23 FII_MEASURE23,
2534 h.FII_MEASURE24 FII_MEASURE24,
2535 h.FII_MEASURE25 FII_MEASURE25,
2536 h.FII_MEASURE26 FII_MEASURE26,
2537 '''||l_url_1||''' FII_ATTRIBUTE10,
2538 '''||l_url_2||''' FII_ATTRIBUTE11,
2539 '''||l_url_4||''' FII_ATTRIBUTE12, /* Bug 3044407: Swapped the URLs of */
2540 '''||l_url_3||''' FII_ATTRIBUTE13 /* FII_ATTRIBUTE12 and FII_ATTRIBUTE13 */
2541
2542 FROM
2543 (
2544 SELECT
2545 g.FII_MEASURE1 FII_MEASURE1,
2546 g.FII_MEASURE2 FII_MEASURE2,
2547 g.FII_MEASURE3 FII_MEASURE3,
2548 g.FII_MEASURE4 FII_MEASURE4,
2549 g.FII_MEASURE5 FII_MEASURE5,
2550 g.FII_MEASURE6 FII_MEASURE6,
2551 g.FII_MEASURE7 FII_MEASURE7,
2552 g.FII_MEASURE8 FII_MEASURE8,
2553 g.FII_MEASURE9 FII_MEASURE9,
2554 g.FII_MEASURE10 FII_MEASURE10,
2555 g.FII_MEASURE11 FII_MEASURE11,
2556 g.FII_MEASURE12 FII_MEASURE12,
2557 g.FII_MEASURE13 FII_MEASURE13,
2558 g.FII_MEASURE14 FII_MEASURE14,
2559 g.FII_MEASURE15 FII_MEASURE15,
2560 g.FII_MEASURE16 FII_MEASURE16,
2561 g.FII_MEASURE18 FII_MEASURE18,
2562 g.FII_MEASURE21 FII_MEASURE21,
2563 g.FII_MEASURE22 FII_MEASURE22,
2564 g.FII_MEASURE23 FII_MEASURE23,
2565 g.FII_MEASURE24 FII_MEASURE24,
2566 g.FII_MEASURE25 FII_MEASURE25,
2567 g.FII_MEASURE26 FII_MEASURE26,
2568 (rank () over(&ORDER_BY_CLAUSE nulls last,g.FII_MEASURE2)) -1 rnk
2569 FROM
2570
2571 (SELECT f.invoice_number FII_MEASURE1,
2572 f.invoice_id FII_MEASURE2,
2573 f.invoice_type FII_MEASURE3,
2574 f.invoice_date FII_MEASURE4,
2575 f.entered_date FII_MEASURE5,
2576 MIN(f.due_Date) FII_MEASURE6,
2577 f.invoice_currency_code FII_MEASURE7,
2578 f.invoice_amount FII_MEASURE8,
2579 f.'||l_invoice_amount||' FII_MEASURE9,
2580 SUM(CASE WHEN base.action = ''PREPAYMENT'' THEN
2581 DECODE(SIGN(base.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
2582 NVL(base.'||l_payment_amount||',0))
2583 WHEN NVL(base.action_date, &BIS_CURRENT_ASOF_DATE) <= &BIS_CURRENT_ASOF_DATE THEN
2584 NVL(base.'||l_payment_amount||' ,0)
2585 ELSE 0
2586 END) FII_MEASURE10,
2587 decode(nvl(hold1.FII_MEASURE11, ''N''), ''Y'', '''||l_yes||''', ''N'','''||l_no||''') FII_MEASURE11,
2588 nvl(hold.FII_MEASURE12,0) FII_MEASURE12,
2589 nvl(hold1.FII_MEASURE13,0) FII_MEASURE13,
2590 f.dist_count FII_MEASURE14,
2591 t.name FII_MEASURE15,
2592 f.source FII_MEASURE16,
2593 max(base.check_id) FII_MEASURE18,
2594 to_number(null) FII_MEASURE21,
2595 sum(f.'||l_invoice_amount||') over() FII_MEASURE22,
2596 SUM(SUM(CASE WHEN base.action = ''PREPAYMENT'' THEN
2597 DECODE(SIGN(base.action_date-&BIS_CURRENT_ASOF_DATE),1,0,
2598 NVL(base.'||l_payment_amount||',0))
2599 WHEN NVL(base.action_date, &BIS_CURRENT_ASOF_DATE) <= &BIS_CURRENT_ASOF_DATE THEN
2600 NVL(base.'||l_payment_amount||' ,0)
2601 ELSE 0
2602 END)) OVER () FII_MEASURE23,
2603 sum(nvl(hold.FII_MEASURE12,0)) over() FII_MEASURE24,
2604 sum(nvl(hold1.FII_MEASURE13,0)) over() FII_MEASURE25,
2605 sum(f.dist_count) over() FII_MEASURE26
2606 FROM fii_ap_invoice_b f,
2607 fii_ap_pay_sched_b base,
2608 ap_terms_tl t,
2609 (SELECT invoice_id,
2610 COUNT(hold_date) FII_MEASURE12
2611 FROM fii_ap_inv_holds_b f
2612 WHERE hold_date BETWEEN :PERIOD_START AND &BIS_CURRENT_ASOF_DATE
2613 '||l_org_where||' '||l_sup_where||'
2614 GROUP BY invoice_id
2615 ) hold,
2616 (SELECT invoice_id,
2617 ''Y'' FII_MEASURE11,
2618 sum(days_on_hold) FII_MEASURE13
2619 FROM fii_ap_hhist_ib_mv f
2620 WHERE 1 =1
2621 '||l_org_where||' '||l_sup_where||'
2622 GROUP BY invoice_id
2623 ) hold1
2624 WHERE
2625 f.invoice_id = &FII_AP_INV_ID
2626 '||l_org_where||' '||l_sup_where||'
2627 AND f.cancel_flag = ''N'' /*Added code for bug. no.3084280 */
2628 AND base.invoice_id (+) = f.invoice_id /*added outer join and the decode condition above for bug no.3063372*/
2629 AND decode(base.supplier_id,null,1,decode(base.supplier_id,f.supplier_id,1,0))=1
2630 AND decode(base.org_id,null,1,decode(base.org_id,f.org_id,1,0))=1
2631 AND t.term_id = f.terms_id
2632 AND t.language = userenv(''LANG'')
2633 AND f.invoice_id =hold.invoice_id (+)
2634 AND f.invoice_id =hold1.invoice_id (+)
2635 GROUP BY f.invoice_number,
2636 f.invoice_id,
2637 f.invoice_type,
2638 f.invoice_date,
2639 f.entered_date,
2640 f.invoice_currency_code,
2641 f.invoice_amount,
2642 f.'||l_invoice_amount||',
2643 hold1.FII_MEASURE11,
2644 hold1.FII_MEASURE13,
2645 f.'||l_discount_offered||',
2646 hold.FII_MEASURE12,
2647 f.dist_count,
2648 t.name,
2649 f.source) g ) h
2650 WHERE (rnk between &START_INDEX AND &END_INDEX or &END_INDEX = -1)
2651 &ORDER_BY_CLAUSE
2652 ';
2653 ELSE -- dummy code
2654 sqlstmt := 'Unknown report source';
2655
2656
2657 END CASE;
2658
2659 -- Attach bind parameters
2660 FII_PMV_UTIL.bind_variable(
2661 p_sqlstmt=>sqlstmt,
2662 p_page_parameter_tbl=>p_page_parameter_tbl,
2663 p_sql_output=>inv_dtl_sql,
2664 p_bind_output_table=>inv_dtl_output,
2665 p_period_start=>l_period_start);
2666 -- p_record_type_id=>l_record_type_id,
2667
2668
2669 END get_inv_activity_detail;
2670
2671
2672
2673 END FII_AP_INV_ACTIVITY_DETAIL;
2674