[Home] [Help]
PACKAGE BODY: APPS.FII_AP_HOLD_SUM
Source
1 PACKAGE BODY fii_ap_hold_sum AS
2 /* $Header: FIIAPS3B.pls 120.3 2006/01/25 23:36:04 vkazhipu noship $ */
3
4 PROCEDURE get_hold_sum
5 ( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
6 get_hold_sum_sql out NOCOPY VARCHAR2,
7 get_hold_sum_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)IS
8
9 -- declaration section
10 sqlstmt VARCHAR2(14000);
11
12 l_as_of_date DATE;
13 l_operating_unit VARCHAR2(240);
14 l_supplier VARCHAR2(240);
15 l_invoice_number VARCHAR2(240);
16 l_period_type VARCHAR2(240);
17 l_record_type_id NUMBER;
18 l_view_by VARCHAR2(240);
19 l_currency VARCHAR2(240);
20 l_column_name VARCHAR2(240);
21 l_table_name VARCHAR2(240);
22 l_gid NUMBER;
23 l_org_where VARCHAR2(240);
24 l_supplier_where VARCHAR2(240);
25 l_url_1 VARCHAR2(240);
26 l_url_2 VARCHAR2(240);
27 l_url_3 VARCHAR2(240);
28 l_url_4 VARCHAR2(240);
29
30 BEGIN
31
32 -- Retrieve parameter info
33
34 FII_PMV_Util.Get_Parameters(
35 p_page_parameter_tbl,
36 l_as_of_date,
37 l_operating_unit,
38 l_supplier,
39 l_invoice_number,
40 l_period_type,
41 l_record_type_id,
42 l_view_by,
43 l_currency,
44 l_column_name,
45 l_table_name,
46 l_gid,
47 l_org_where,
48 l_supplier_where
49 );
50
51 l_record_type_id := 512;
52
53
54 -- Decide on the viewby stuff and pk to be used
55 -- Map the l_column_name based on the selected viewby
56
57
58 /*--------------------------------------------------------------+
59 | VIEWBY - Either Operating Unit / Supplier |
60 | VIEWBYID - Either org_id / supplier_id |
61 | FII_MEASURE1 - Open Payables Amount |
62 | FII_MEASURE2 - Total Number of Invoices |
63 | FII_MEASURE3 - Invoices on Hold Amount |
64 | FII_MEASURE4 - Number of Invoices |
65 | FII_MEASURE5 - Holds Due Amount |
66 | FII_MEASURE6 - Number of Invoices |
67 | FII_MEASURE7 - Weighted Average Days Due |
68 | FII_MEASURE8 - Holds Past Due Amount |
69 | FII_MEASURE9 - Number of Invoices |
70 | FII_MEASURE10 - Weighte Average Days Past Due |
71 | FII_MEASURE11 - Number of Holds |
72 | FII_MEASURE12 - Average Days on Hold |
73 | FII_MEASURE13 - Grand Total of Open Payables Amount |
74 | FII_MEASURE14 - Grand Total of Total Number of Invoices |
75 | FII_MEASURE15 - Grand Total of Invoices on Hold Amount |
76 | FII_MEASURE16 - Grand Total of Number of Invoices |
77 | FII_MEASURE17 - Grand Total of Holds Due Amount |
78 | FII_MEASURE18 - Grand Total of Number of Invoices |
79 | FII_MEASURE19 - Grand Total of Holds Past Due Amount |
80 | FII_MEASURE20 - Grand Total of Number of Invoices |
81 | FII_MEASURE21 - Grand Total of Number of Holds |
82 | FII_MEASURE22 - Percent on Hold |
83 | FII_MEASURE23 - Grand Total of Percent on Hold |
84 | FII_ATTRIBUTE2 - URL for FII_MEASURE6 |
85 | FII_ATTRIBUTE3 - URL for FII_MEASURE4 |
86 | FII_ATTRIBUTE4 - URL for FII_MEASURE9 |
87 +--------------------------------------------------------------*/
88
89 -- Construct the sql query to be sent
90
91 -- for fii_measure4
92
93 -- If view by Operating Unit, drills to a breakdown of suppliers using the same parameters
94 -- as selected in this report.
95 -- if view by Supplier, drills to Invoice Detail report for the total invoices
96 -- for the given Supplier selected using the same parameters.
97 -- Form function to drill to : FII_AP_INV_ON_HOLD_DETAIL.
98
99 l_url_1 := 'pFunctionName=FII_AP_INV_ON_HOLD_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_INV_ON_HOLD_DETAIL' ;
100
101 l_url_4 := 'pFunctionName=FII_AP_HOLD_SUM&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y' ;
102
103
104 -- for fii_measure6
105 -- Drills only available when view by Supplier. It drills to Invoice Detail report
106 -- for the invoices due for the given Supplier selected using the same parameters.
107 -- Form function to drill to : FII_AP_INV_ON_HOLD_DUE_DETAIL.
108
109 l_url_2 := 'pFunctionName=FII_AP_INV_ON_HOLD_DUE_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_INV_ON_HOLD_DUE_DETAIL' ;
110
111
112 -- for fii_measure9
113 -- Drills only available when view by Supplier. It drills to Invoice Detail report
114 -- for the invoices past due for the given Supplier selected using the same parameters.
115 -- Form function to drill to : FII_AP_INV_ON_HOLD_PDUE_DETAIL.
116
117 l_url_3 := 'pFunctionName=FII_AP_INV_ON_HOLD_PDUE_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_INV_ON_HOLD_PDUE_DETAIL' ;
118
119
120 sqlstmt := '
121 SELECT h.VIEWBY VIEWBY,
122 h.VIEWBYID VIEWBYID,
123 h.FII_MEASURE1 FII_MEASURE1,
124 h.FII_MEASURE2 FII_MEASURE2,
125 h.FII_MEASURE3 FII_MEASURE3,
126 h.FII_MEASURE4 FII_MEASURE4,
127 h.FII_MEASURE5 FII_MEASURE5,
128 h.FII_MEASURE6 FII_MEASURE6,
129 h.FII_MEASURE8 FII_MEASURE8,
130 h.FII_MEASURE9 FII_MEASURE9,
131 h.FII_MEASURE11 FII_MEASURE11,
132 h.FII_MEASURE12 FII_MEASURE12,
133 h.FII_MEASURE13 FII_MEASURE13,
134 h.FII_MEASURE14 FII_MEASURE14,
135 h.FII_MEASURE15 FII_MEASURE15,
136 h.FII_MEASURE16 FII_MEASURE16,
137 h.FII_MEASURE17 FII_MEASURE17,
138 h.FII_MEASURE18 FII_MEASURE18,
139 h.FII_MEASURE19 FII_MEASURE19,
140 h.FII_MEASURE20 FII_MEASURE20,
141 h.FII_MEASURE21 FII_MEASURE21,
142 decode('''||l_view_by||''',''SUPPLIER+POA_SUPPLIERS'','''||l_url_2||''',null)
143 FII_ATTRIBUTE2, -- for fii_measure6
144 decode('''||l_view_by||''',''ORGANIZATION+FII_OPERATING_UNITS'','''||l_url_4||''',
145 ''SUPPLIER+POA_SUPPLIERS'','''||l_url_1||''',
146 null) FII_ATTRIBUTE3, -- for fii_measure4
147
148 decode('''||l_view_by||''',''SUPPLIER+POA_SUPPLIERS'','''||l_url_3||''',null)
149 FII_ATTRIBUTE4, -- for fii_measure9
150
151 decode('''||l_view_by||''',''ORGANIZATION+FII_OPERATING_UNITS'','''||l_url_4||''',
152 ''SUPPLIER+POA_SUPPLIERS'','''||l_url_1||''',
153 null) FII_ATTRIBUTE5 /* Added for Bug 3096072 */
154 FROM
155 (SELECT g.VIEWBY VIEWBY,
156 g.VIEWBYID VIEWBYID,
157 g.FII_MEASURE1 FII_MEASURE1,
158 g.FII_MEASURE2 FII_MEASURE2,
159 g.FII_MEASURE3 FII_MEASURE3,
160 g.FII_MEASURE4 FII_MEASURE4,
161 g.FII_MEASURE5 FII_MEASURE5,
162 g.FII_MEASURE6 FII_MEASURE6,
163 g.FII_MEASURE8 FII_MEASURE8,
164 g.FII_MEASURE9 FII_MEASURE9,
165 g.FII_MEASURE11 FII_MEASURE11,
166 g.FII_MEASURE12 FII_MEASURE12,
167 sum(g.FII_MEASURE1) over() FII_MEASURE13,
168 sum(g.FII_MEASURE2) over() FII_MEASURE14,
169 sum(g.FII_MEASURE3) over() FII_MEASURE15,
170 sum(g.FII_MEASURE4) over() FII_MEASURE16,
171 sum(g.FII_MEASURE3 - g.FII_MEASURE8) over() FII_MEASURE17,
172 sum(g.FII_MEASURE6) over() FII_MEASURE18,
173 sum(g.FII_MEASURE8) over() FII_MEASURE19,
174 sum(g.FII_MEASURE9) over() FII_MEASURE20,
175 sum(g.FII_MEASURE11) over() FII_MEASURE21,
176 ( rank() over (&ORDER_BY_CLAUSE nulls last, VIEWBYID)) - 1 rnk
177 FROM
178 (SELECT viewby_dim.value VIEWBY,
179 viewby_dim.id VIEWBYID,
180 sum(open_amt) FII_MEASURE1,
181 sum(open_count) FII_MEASURE2,
182 sum(inv_on_hold_amt) FII_MEASURE3,
183 sum(inv_on_hold_count) FII_MEASURE4,
184 sum(inv_on_hold_amt) - sum(on_hold_past_due_amt) FII_MEASURE5,
185 sum(on_hold_due_count) FII_MEASURE6,
186 sum(on_hold_past_due_amt) FII_MEASURE8,
187 sum(on_hold_past_due_count) FII_MEASURE9,
188 sum(no_of_holds) FII_MEASURE11,
189 decode(sum(inv_on_hold_count),0,0,
190 sum(days_on_hold)/sum(inv_on_hold_count)) FII_MEASURE12
191 -- sum(sum(open_amt)) over() FII_MEASURE13,
192 -- sum(sum(open_count)) over() FII_MEASURE14,
193 -- sum(sum(inv_on_hold_amt)) over() FII_MEASURE15,
194 -- sum(sum(inv_on_hold_count)) over() FII_MEASURE16,
195 -- sum(sum(inv_on_hold_amt) - sum(on_hold_past_due_amt)) over() FII_MEASURE17,
196 -- sum(sum(on_hold_due_count)) over() FII_MEASURE18,
197 -- sum(sum(on_hold_past_due_amt)) over() FII_MEASURE19,
198 -- sum(sum(on_hold_past_due_count)) over() FII_MEASURE20,
199 -- sum(sum(no_of_holds)) over() FII_MEASURE21,
200 FROM
201 (SELECT f.'||l_column_name||' id,
202 sum(f.open_amt'||l_currency||' ) open_amt,
203 sum(f.open_count) open_count,
204 0 inv_on_hold_amt,
205 0 inv_on_hold_count,
206 0 on_hold_due_count,
207 0 on_hold_past_due_amt,
208 0 on_hold_past_due_count,
209 0 no_of_holds,
210 0 days_on_hold
211 FROM FII_AP_LIA_IB_MV f,
212 fii_time_structures cal
213 WHERE f.time_id = cal.time_id '||l_org_where||l_supplier_where||'
214 AND f.period_type_id = cal.period_type_id
215 AND bitand(cal.record_type_id,:RECORD_TYPE_ID) = :RECORD_TYPE_ID
216 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE)
217 AND f.gid = :GID
218 GROUP BY f.'||l_column_name||'
219 UNION ALL
220 SELECT f.'||l_column_name||' id,
221 0 open_amt,
225 sum(f.on_hold_due_count) on_hold_due_count,
222 0 open_count,
223 sum(f.inv_on_hold_amt'||l_currency||') inv_on_hold_amt,
224 sum(f.inv_on_hold_count) inv_on_hold_count,
226 sum(f.on_hold_past_due_amt'||l_currency||') on_hold_past_due_amt,
227 sum(f.on_hold_past_due_count) on_hold_past_due_count,
228 sum(f.no_of_holds) no_of_holds,
229 sum(f.days_on_hold) days_on_hold
230 FROM FII_AP_HLIA_I_MV f,
231 fii_time_structures cal
232 WHERE f.time_id = cal.time_id '||l_org_where||l_supplier_where||'
233 AND f.period_type_id = cal.period_type_id
234 AND bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
235 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE)
236 AND f.gid = :GID
237 GROUP BY f.'||l_column_name||'
238 ) f,
239 ('||l_table_name||') viewby_dim
240 WHERE f.id = viewby_dim.id
241 GROUP BY viewby_dim.value, viewby_dim.id) g ) h
242 WHERE ((rnk between &START_INDEX and &END_INDEX) or (&END_INDEX = -1))
243 &ORDER_BY_CLAUSE' ;
244
245 -- Binding Section
246
247 FII_PMV_Util.bind_variable(
248 p_sqlstmt=> sqlstmt,
249 p_page_parameter_tbl=>p_page_parameter_tbl,
250 p_sql_output=>get_hold_sum_sql,
251 p_bind_output_table=>get_hold_sum_output,
252 -- p_invoice_number=>l_invoice_number,
253 p_record_type_id=>l_record_type_id,
254 p_view_by=>l_view_by,
255 p_gid=>l_gid
256 );
257
258
259
260 END get_hold_sum;
261
262
263 PROCEDURE get_hold_discount_sum (
264 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
265 get_hold_discount_sum_sql OUT NOCOPY VARCHAR2,
266 get_hold_discount_sum_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
267 IS
268
269 -- declaration section
270 sqlstmt VARCHAR2(14000);
271
272 l_as_of_date DATE;
273 l_operating_unit VARCHAR2(240);
274 l_supplier VARCHAR2(240);
275 l_invoice_number VARCHAR2(240);
276 l_period_type VARCHAR2(240);
277 l_record_type_id NUMBER;
278 l_view_by VARCHAR2(240);
279 l_currency VARCHAR2(240);
280 l_column_name VARCHAR2(240);
281 l_table_name VARCHAR2(240);
282 l_gid NUMBER;
283 l_org_where VARCHAR2(240);
284 l_supplier_where VARCHAR2(240);
285 l_url_1 VARCHAR2(240);
286 l_url_4 VARCHAR2(240);
287
288
289 BEGIN
290
291 -- Retrieve parameter info
292
293 FII_PMV_Util.Get_Parameters(
294 p_page_parameter_tbl,
295 l_as_of_date,
296 l_operating_unit,
297 l_supplier,
298 l_invoice_number,
299 l_period_type,
300 l_record_type_id,
301 l_view_by,
302 l_currency,
303 l_column_name,
304 l_table_name,
305 l_gid,
306 l_org_where,
307 l_supplier_where
308 );
309
310 l_record_type_id := 512;
311
312 -- Decide on the viewby stuff and pk to be used
313 -- Map the l_column_name based on the selected viewby
314
315 /*--------------------------------------------------------------+
316 | VIEWBY - Either Operating Unit / Supplier |
317 | VIEWBYID - Either org_id / supplier_id |
318 | FII_MEASURE1 - Invoices on Hold Amount |
319 | FII_MEASURE2 - Total Invoice Amount |
320 | FII_MEASURE3 - Number of Invoices |
321 | FII_MEASURE4 - Discount Offered |
322 | FII_MEASURE5 - % Offered |
323 | FII_MEASURE6 - Discount Taken |
324 | FII_MEASURE7 - % Taken of Offered |
325 | FII_MEASURE8 - Discount Lost |
326 | FII_MEASURE9 - % Lost of Offered |
327 | FII_MEASURE10 - Discount Remaining |
328 | FII_MEASURE11 - % Remaining of Offered |
329 | FII_MEASURE12 - Average Days on Hold |
330 | FII_MEASURE13 - Grand Total of Invoices on Hold Amount|
331 | FII_MEASURE14 - Grand Total of Total Invoice Amount |
332 | FII_MEASURE15 - Grand Total of Number of Invoices |
333 | FII_MEASURE16 - Grand Total of Discount Offered |
334 | FII_MEASURE17 - Grand Total of Discount Lost |
335 | FII_MEASURE18 - Grand Total of Discount Remaining |
336 | FII_MEASURE19 - Grand Total of % Offered |
337 | FII_MEASURE20 - Grand Total of % Taken of Offered |
338 | FII_MEASURE21 - Grand Total of % Lost of Offered |
339 | FII_MEASURE22 - Grand Total of % Remaining of Offered |
340 +--------------------------------------------------------------*/
341
342
343 -- Construct the sql query to be sent
344
345 -- for fii_measure3
346
347 -- If view by Operating Unit, drills to a breakdown of suppliers using the same parameters
351 -- Form function to drill to : FII_AP_INV_ON_HOLD_DETAIL.
348 -- as selected in this report.
349 -- if view by Supplier, drills to Invoice Detail report for the total invoices
350 -- for the given Supplier selected using the same parameters.
352
353 l_url_1 := 'pFunctionName=FII_AP_INV_ON_HOLD_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_INV_ON_HOLD_DETAIL' ;
354
355 l_url_4 := 'pFunctionName=FII_AP_HOLD_DISCOUNT_SUM&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y' ;
356
357
358
359 sqlstmt := '
360 SELECT viewby_dim.value VIEWBY,
361 viewby_dim.id VIEWBYID,
362 f.FII_MEASURE1 FII_MEASURE1,
363 f.FII_MEASURE2 FII_MEASURE2,
364 f.FII_MEASURE3 FII_MEASURE3,
365 f.FII_MEASURE4 FII_MEASURE4,
366 f.FII_MEASURE6 FII_MEASURE6,
367 f.FII_MEASURE8 FII_MEASURE8,
368 f.FII_MEASURE10 FII_MEASURE10,
369 f.FII_MEASURE12 FII_MEASURE12,
370 f.FII_MEASURE13 FII_MEASURE13,
371 f.FII_MEASURE14 FII_MEASURE14,
372 f.FII_MEASURE15 FII_MEASURE15,
373 f.FII_MEASURE16 FII_MEASURE16,
374 f.FII_MEASURE17 FII_MEASURE17,
375 f.FII_MEASURE18 FII_MEASURE18,
376 f.FII_MEASURE19 FII_MEASURE19,
377 f.FII_MEASURE20 FII_MEASURE20,
378 f.FII_MEASURE21 FII_MEASURE21,
379 f.FII_MEASURE22 FII_MEASURE22,
380 decode('''||l_view_by||''',''ORGANIZATION+FII_OPERATING_UNITS'','''||l_url_4||''',
381 ''SUPPLIER+POA_SUPPLIERS'','''||l_url_1||''',
382 null) FII_ATTRIBUTE2 -- for fii_measure3
383 FROM
384 (SELECT
385 id ,
386 FII_MEASURE1,
387 FII_MEASURE2,
388 FII_MEASURE3,
389 FII_MEASURE4,
390 FII_MEASURE6,
391 FII_MEASURE8,
392 FII_MEASURE10,
393 sum(FII_MEASURE1) over() FII_MEASURE12,
394 sum(FII_MEASURE2) over() FII_MEASURE13,
395 sum(FII_MEASURE3) over() FII_MEASURE14,
396 sum(FII_MEASURE4) over() FII_MEASURE15,
397 sum(FII_MEASURE6) over() FII_MEASURE16,
398 sum(FII_MEASURE8) over() FII_MEASURE17,
399 sum(FII_MEASURE10) over() FII_MEASURE18,
400 decode (sum(FII_MEASURE2) over(),0,0,
401 ((sum(FII_MEASURE4) over() / sum(FII_MEASURE2) over()) *100))
402 FII_MEASURE19,
403 decode (sum(FII_MEASURE4) over(),0,0,
404 ((sum(FII_MEASURE6) over() / sum(FII_MEASURE4) over()) *100))
405 FII_MEASURE20,
406 decode (sum(FII_MEASURE4) over(),0,0,
407 ((sum(FII_MEASURE8) over() / sum(FII_MEASURE4) over()) *100))
408 FII_MEASURE21,
409 decode (sum(FII_MEASURE4) over(),0,0,
410 ((sum(FII_MEASURE10) over() / sum(FII_MEASURE4) over()) *100))
411 FII_MEASURE22,
412 ( rank() over (&ORDER_BY_CLAUSE nulls last, ID)) - 1 rnk
413 FROM
414 (SELECT f.'||l_column_name||' id,
415 sum(INV_ON_HOLD_AMT'||l_currency||' ) FII_MEASURE1,
416 sum(INV_ON_HOLD_AMT'||l_currency||'+
417 ON_HOLD_PAYMENT_AMOUNT'||l_currency||'+
418 ON_HOLD_DIS_TAKEN'||l_currency||') FII_MEASURE2,
419 sum(INV_ON_HOLD_COUNT) FII_MEASURE3,
420 sum(ON_HOLD_DIS_TAKEN'||l_currency||' +
421 ON_HOLD_DIS_LOST'||l_currency||' +
422 ON_HOLD_DIS_REMAINING'||l_currency||') FII_MEASURE4,
423 sum(ON_HOLD_DIS_TAKEN'||l_currency||') FII_MEASURE6,
424 sum(ON_HOLD_DIS_LOST'||l_currency||') FII_MEASURE8,
425 sum(ON_HOLD_DIS_REMAINING'||l_currency||') FII_MEASURE10
426 FROM FII_AP_HLIA_I_MV f,
427 fii_time_structures cal
428 WHERE f.time_id = cal.time_id '||l_org_where||l_supplier_where||'
429 AND f.period_type_id = cal.period_type_id
430 AND bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
431 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE)
432 AND f.gid = :GID
433 GROUP BY f.'||l_column_name||'
434 )) f,
435 ('||l_table_name||') viewby_dim
436 WHERE f.id = viewby_dim.id
437 and (rnk between &START_INDEX and &END_INDEX or &END_INDEX = -1)
438 &ORDER_BY_CLAUSE' ;
439
440
441
445 p_sqlstmt=> sqlstmt,
442 -- Binding Section
443
444 FII_PMV_Util.bind_variable(
446 p_page_parameter_tbl=>p_page_parameter_tbl,
447 p_sql_output=>get_hold_discount_sum_sql,
448 p_bind_output_table=>get_hold_discount_sum_output,
449 -- p_invoice_number=>l_invoice_number,
450 p_record_type_id=>l_record_type_id,
451 p_view_by=>l_view_by,
452 p_gid=>l_gid
453 );
454
455
456
457
458 END get_hold_discount_sum;
459
460
461 PROCEDURE get_hold_cat_sum
462 ( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
463 get_hold_cat_sum_sql out NOCOPY VARCHAR2,
464 get_hold_cat_sum_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)IS
465
466 -- declaration section
467 sqlstmt VARCHAR2(14000);
468
469 l_as_of_date DATE;
470 l_operating_unit VARCHAR2(240);
471 l_supplier VARCHAR2(240);
472 l_invoice_number VARCHAR2(240);
473 l_period_type VARCHAR2(240);
474 l_record_type_id NUMBER;
475 l_view_by VARCHAR2(240);
476 l_currency VARCHAR2(240);
477 l_column_name VARCHAR2(240);
478 l_table_name VARCHAR2(240);
479 l_gid NUMBER;
480 l_org_where VARCHAR2(240);
481 l_supplier_where VARCHAR2(240);
482 l_url_1 VARCHAR2(240);
483 l_url_2 VARCHAR2(240);
484
485 BEGIN
486
487 -- Retrieve parameter info
488
489 FII_PMV_Util.Get_Parameters(
490 p_page_parameter_tbl,
491 l_as_of_date,
492 l_operating_unit,
493 l_supplier,
494 l_invoice_number,
495 l_period_type,
496 l_record_type_id,
497 l_view_by,
498 l_currency,
499 l_column_name,
500 l_table_name,
501 l_gid,
502 l_org_where,
503 l_supplier_where
504 );
505
506
507 l_record_type_id := 512;
508
509 -- Decide on the viewby stuff and pk to be used
510 -- Map the l_column_name based on the selected viewby
511
512
513 /*--------------------------------------------------------------+
514 | VIEWBY - Either Operating Unit / Supplier |
515 | VIEWBYID - Either org_id / supplier_id |
516 | FII_MEASURE1 - Open Payables Amount |
517 | FII_MEASURE2 - Total Number of Invoices |
518 | FII_MEASURE3 - Invoices on Hold Amount |
519 | FII_MEASURE4 - Number of Invoices |
520 | FII_MEASURE5 - Holds Due Amount |
521 | FII_MEASURE6 - Number of Invoices |
522 | FII_MEASURE7 - Weighted Average Days Due |
523 | FII_MEASURE8 - Holds Past Due Amount |
524 | FII_MEASURE9 - Number of Invoices |
525 | FII_MEASURE10 - Weighte Average Days Past Due |
526 | FII_MEASURE11 - Number of Holds |
527 | FII_MEASURE12 - Average Days on Hold |
528 | FII_MEASURE13 - Grand Total of Open Payables Amount |
529 | FII_MEASURE14 - Grand Total of Total Number of Invoices |
530 | FII_MEASURE15 - Grand Total of Invoices on Hold Amount |
531 | FII_MEASURE16 - Grand Total of Number of Invoices |
532 | FII_MEASURE17 - Grand Total of Holds Due Amount |
533 | FII_MEASURE18 - Grand Total of Number of Invoices |
534 | FII_MEASURE19 - Grand Total of Holds Past Due Amount |
535 | FII_MEASURE20 - Grand Total of Number of Invoices |
536 | FII_MEASURE21 - Grand Total of Number of Holds |
537 +--------------------------------------------------------------*/
538
539 -- Construct the sql query to be sent
540
541 l_url_1 := 'pFunctionName=FII_AP_INV_HOLD_CAT_DETAIL&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_INV_HOLD_CAT_DETAIL&FII_DIM1=All' ;
542
543 l_url_2 := 'pFunctionName=FII_AP_HOLD_CAT_SUM&VIEW_BY=SUPPLIER+POA_SUPPLIERS&VIEW_BY_NAME=VIEW_BY_ID&pParamIds=Y' ;
544
545
546 sqlstmt := '
547 SELECT
548 h.VIEWBY VIEWBY,
549 h.VIEWBYID VIEWBYID,
550 h.FII_MEASURE1 FII_MEASURE1,
551 h.FII_MEASURE2 FII_MEASURE2,
552 h.FII_MEASURE3 FII_MEASURE3,
553 h.FII_MEASURE4 FII_MEASURE4,
554 h.FII_MEASURE5 FII_MEASURE5,
555 h.FII_MEASURE6 FII_MEASURE6,
556 h.FII_MEASURE7 FII_MEASURE7,
557 h.FII_MEASURE8 FII_MEASURE8,
558 h.FII_MEASURE13 FII_MEASURE13,
559 h.FII_MEASURE14 FII_MEASURE14,
560 h.FII_MEASURE15 FII_MEASURE15,
561 h.FII_MEASURE16 FII_MEASURE16,
562 h.FII_MEASURE17 FII_MEASURE17,
563 h.FII_MEASURE18 FII_MEASURE18,
564 h.FII_MEASURE19 FII_MEASURE19,
568 null) FII_ATTRIBUTE2 -- for fii_measure2
565 h.FII_MEASURE20 FII_MEASURE20,
566 decode('''||l_view_by||''',''ORGANIZATION+FII_OPERATING_UNITS'','''||l_url_2||''',
567 ''SUPPLIER+POA_SUPPLIERS'','''||l_url_1||''',
569 FROM
570 (SELECT g.VIEWBY VIEWBY,
571 g.VIEWBYID VIEWBYID,
572 g.FII_MEASURE1 FII_MEASURE1,
573 g.FII_MEASURE2 FII_MEASURE2,
574 g.FII_MEASURE3 FII_MEASURE3,
575 g.FII_MEASURE4 FII_MEASURE4,
576 g.FII_MEASURE5 FII_MEASURE5,
577 g.FII_MEASURE6 FII_MEASURE6,
578 g.FII_MEASURE7 FII_MEASURE7,
579 g.FII_MEASURE8 FII_MEASURE8,
580 sum(g.FII_MEASURE1) over() FII_MEASURE13,
581 sum(g.FII_MEASURE2) over() FII_MEASURE14,
582 sum(g.FII_MEASURE3) over() FII_MEASURE15,
583 sum(g.FII_MEASURE4) over() FII_MEASURE16,
584 sum(g.FII_MEASURE5) over() FII_MEASURE17,
585 sum(g.FII_MEASURE6) over() FII_MEASURE18,
586 sum(g.FII_MEASURE7) over() FII_MEASURE19,
587 sum(g.FII_MEASURE8) over() FII_MEASURE20,
588 ( rank() over (&ORDER_BY_CLAUSE nulls last, VIEWBYID)) - 1 rnk
589 FROM
590 (SELECT viewby_dim.value VIEWBY,
591 viewby_dim.id VIEWBYID,
592 sum(inv_on_hold_amt) FII_MEASURE1,
593 sum(inv_on_hold_count) FII_MEASURE2,
594 sum(no_of_holds) FII_MEASURE3,
595 sum(VARIANCE_HOLD_COUNT) FII_MEASURE4,
596 sum(PO_MATCHING_HOLD_COUNT) FII_MEASURE5,
597 sum(INVOICE_HOLD_COUNT) FII_MEASURE6,
598 sum(USER_DEFINED_HOLD_COUNT) FII_MEASURE7,
599 sum(OTHER_HOLD_COUNT) FII_MEASURE8
600 -- sum(sum(inv_on_hold_amt)) over() FII_MEASURE13,
601 -- sum(sum(inv_on_hold_count)) over() FII_MEASURE14,
602 -- sum(sum(no_of_holds)) over() FII_MEASURE15,
603 -- sum(sum(VARIANCE_HOLD_COUNT)) over() FII_MEASURE16,
604 -- sum(sum(PO_MATCHING_HOLD_COUNT)) over() FII_MEASURE17,
605 -- sum(sum(INVOICE_HOLD_COUNT)) over() FII_MEASURE18,
606 -- sum(sum(USER_DEFINED_HOLD_COUNT)) over() FII_MEASURE19,
607 -- sum(sum(OTHER_HOLD_COUNT)) over() FII_MEASURE20,
608 FROM
609 (SELECT f.'||l_column_name||' id,
610 sum(f.inv_on_hold_amt'||l_currency||' ) inv_on_hold_amt,
611 sum(f.inv_on_hold_count) inv_on_hold_count,
612 sum(f.no_of_holds) no_of_holds,
613 0 VARIANCE_HOLD_COUNT,
614 0 PO_MATCHING_HOLD_COUNT,
615 0 INVOICE_HOLD_COUNT,
616 0 USER_DEFINED_HOLD_COUNT,
617 0 OTHER_HOLD_COUNT
618 FROM FII_AP_HLIA_I_MV f,
619 fii_time_structures cal
620 WHERE f.time_id = cal.time_id '||l_org_where||l_supplier_where||'
621 AND f.period_type_id = cal.period_type_id
622 AND bitand(cal.record_type_id,:RECORD_TYPE_ID) = :RECORD_TYPE_ID
623 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE)
624 AND f.gid = :GID
625 GROUP BY f.'||l_column_name||'
626 UNION ALL
627 SELECT f.'||l_column_name||' id,
628 0 inv_on_hold_amt,
629 0 inv_on_hold_count,
630 0 no_of_holds,
631 sum(f.VARIANCE_HOLD_COUNT) VARIANCE_HOLD_COUNT,
632 sum(f.PO_MATCHING_HOLD_COUNT) PO_MATCHING_HOLD_COUNT,
633 sum(f.INVOICE_HOLD_COUNT) INVOICE_HOLD_COUNT,
634 sum(f.USER_DEFINED_HOLD_COUNT) USER_DEFINED_HOLD_COUNT,
635 sum(f.OTHER_HOLD_COUNT) OTHER_HOLD_COUNT
636 FROM FII_AP_HCAT_IB_MV f,
637 fii_time_structures cal
638 WHERE f.time_id = cal.time_id '||l_org_where||l_supplier_where||'
639 AND f.period_type_id = cal.period_type_id
640 AND bitand(cal.record_type_id,:RECORD_TYPE_ID) = :RECORD_TYPE_ID
644 ) f,
641 AND cal.report_date in (&BIS_CURRENT_ASOF_DATE)
642 AND f.gid = :GID
643 GROUP BY f.'||l_column_name||'
645 ('||l_table_name||') viewby_dim
646 WHERE f.id = viewby_dim.id
647 GROUP BY viewby_dim.value, viewby_dim.id) g ) h
648 WHERE ((rnk between &START_INDEX and &END_INDEX) or (&END_INDEX = -1))
649 &ORDER_BY_CLAUSE' ;
650
651 -- Binding Section
652
653 FII_PMV_Util.bind_variable(
654 p_sqlstmt=> sqlstmt,
655 p_page_parameter_tbl=>p_page_parameter_tbl,
656 p_sql_output=>get_hold_cat_sum_sql,
657 p_bind_output_table=>get_hold_cat_sum_output,
658 p_invoice_number=>l_invoice_number,
659 p_record_type_id=>l_record_type_id,
660 p_view_by=>l_view_by,
661 p_gid=>l_gid
662 );
663
664
665
666 END get_hold_cat_sum;
667
668 PROCEDURE get_hold_type_sum
669 ( p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
670 get_hold_type_sum_sql out NOCOPY VARCHAR2,
671 get_hold_type_sum_output out NOCOPY BIS_QUERY_ATTRIBUTES_TBL)IS
672
673 -- declaration section
674 sqlstmt VARCHAR2(14000);
675
676 l_as_of_date DATE;
677 l_operating_unit VARCHAR2(240);
678 l_supplier VARCHAR2(240);
679 l_invoice_number VARCHAR2(240);
680 l_period_type VARCHAR2(240);
681 l_record_type_id NUMBER;
682 l_view_by VARCHAR2(240);
683 l_currency VARCHAR2(240);
684 l_column_name VARCHAR2(240);
685 l_table_name VARCHAR2(240);
686 l_gid NUMBER;
687 l_org_where VARCHAR2(240);
688 l_supplier_where VARCHAR2(240);
689 l_fii_dim1 VARCHAR2(240);
690 l_cat_table VARCHAR2(240);
691 l_cat_join VARCHAR2(240);
692
693 BEGIN
694
695 -- Retrieve parameter info
696
697 FII_PMV_Util.Get_Parameters(
698 p_page_parameter_tbl,
699 l_as_of_date,
700 l_operating_unit,
701 l_supplier,
702 l_invoice_number,
703 l_period_type,
704 l_record_type_id,
705 l_view_by,
706 l_currency,
707 l_column_name,
708 l_table_name,
709 l_gid,
710 l_org_where,
711 l_supplier_where
712 );
713
714 l_record_type_id := 512;
715
716 -- Decide on the viewby stuff and pk to be used
717 -- Map the l_column_name based on the selected viewby
718
719 IF (p_page_parameter_tbl.count > 0) THEN
720 FOR i IN p_page_parameter_tbl.first..p_page_parameter_tbl.last LOOP
721 IF p_page_parameter_tbl(i).parameter_name = 'FII_DIM1' THEN
722 l_fii_dim1 := p_page_parameter_tbl(i).parameter_id;
723 END IF;
724 END LOOP;
725 END IF;
726
727
728 /*--------------------------------------------------------------+
729 | FII_MEASURE1 - Hold Name |
730 | FII_MEASURE2 - Number of Holds |
731 | FII_MEASURE3 - Number of Invoices |
732 | FII_MEASURE14 - Grand Total of Number of Holds |
733 | FII_MEASURE15 - Grand Total of Number of Invoices |
734 +--------------------------------------------------------------*/
735
736 IF ((l_fii_dim1 is not null) AND (l_fii_dim1 <> 'All' )) THEN
737 IF l_fii_dim1 = 'OTHER' THEN
738 l_cat_join := 'and f.HOLD_CATEGORY NOT IN (''VARIANCE'',''PO MATCHING'',
739 ''INVOICE'', ''USER DEFINED'')';
740 ELSE
741 l_cat_join := 'and f.HOLD_CATEGORY in (&FII_DIM1)';
742 END IF;
743 elsif ((l_fii_dim1 is null) OR (l_fii_dim1 = 'All' )) THEN
744 l_cat_join := ' ';
745 END IF;
746
747
748
749 -- Construct the sql query to be sent
750 sqlstmt := '
751 SELECT
752 f.HOLD_CODE FII_MEASURE1,
753 count(f.HOLD_CODE) FII_MEASURE2,
754 count(distinct(f.INVOICE_ID)) FII_MEASURE3,
755 sum(count(f.HOLD_CODE)) over() FII_MEASURE14,
756 sum(count(distinct(f.INVOICE_ID))) over() FII_MEASURE15
757 FROM FII_AP_INV_HOLDS_B f
758 WHERE f.hold_date <= &BIS_CURRENT_ASOF_DATE
759 '||l_org_where||l_supplier_where||'
760 '||l_cat_join||'
761 AND (f.release_date > &BIS_CURRENT_ASOF_DATE OR f.release_date IS NULL)
762 GROUP BY f.HOLD_CODE
763 &ORDER_BY_CLAUSE' ;
764
765 -- Binding Section
766
767 FII_PMV_Util.bind_variable(
768 p_sqlstmt=> sqlstmt,
769 p_page_parameter_tbl=>p_page_parameter_tbl,
770 p_sql_output=>get_hold_type_sum_sql,
771 p_bind_output_table=>get_hold_type_sum_output,
772 p_invoice_number=>l_invoice_number,
773 p_record_type_id=>l_record_type_id,
774 p_view_by=>l_view_by,
775 p_gid=>l_gid
776 );
777
778
779
780 END get_hold_type_sum;
781
782
783 PROCEDURE get_hold_trend (
784 p_page_parameter_tbl IN BIS_PMV_PAGE_PARAMETER_TBL,
785 get_hold_trend_sql OUT NOCOPY VARCHAR2,
786 get_hold_trend_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL)
787 IS
788 sqlstmt VARCHAR2(14000);
792 l_supplier VARCHAR2(240);
789
790 l_as_of_date DATE;
791 l_operating_unit VARCHAR2(240);
793 l_invoice_number VARCHAR2(240);
794 l_period_type VARCHAR2(240);
795 l_record_type_id NUMBER;
796 l_view_by VARCHAR2(240);
797 l_currency VARCHAR2(240);
798 l_column_name VARCHAR2(240);
799 l_table_name VARCHAR2(240);
800 l_gid NUMBER;
801 l_org_where VARCHAR2(240);
802 l_supplier_where VARCHAR2(240);
803 -- l_period_start Date;
804 -- l_days_into_period Number;
805 -- l_cur_period Number;
806 -- l_id_column VARCHAR2(100);
807 l_date_mask VARCHAR2(240);
808 l_url_1 VARCHAR2(240);
809 l_url_2 VARCHAR2(240);
810 l_previous_date DATE;
811 -- l_count NUMBER;
812 l_date VARCHAR2(1000);
813 l_fii_schema VARCHAR2(30);
814 l_status VARCHAR2(30);
815 l_industry VARCHAR2(30);
816
817
818 BEGIN
819
820 FII_PMV_Util.Get_Parameters(
821 p_page_parameter_tbl,
822 l_as_of_date,
823 l_operating_unit,
824 l_supplier,
825 l_invoice_number,
826 l_period_type,
827 l_record_type_id,
828 l_view_by,
829 l_currency,
830 l_column_name,
831 l_table_name,
832 l_gid,
833 l_org_where,
834 l_supplier_where
835 );
836
837
838
839 l_record_type_id := 512;
840
841 FII_PMV_Util.get_format_mask(l_date_mask);
842
843
844 -- IF l_supplier is not null and l_supplier <> 'All' then
845 -- l_gid := 0;
846 -- ELSE
847 -- l_gid := 4;
848 -- END IF;
849
850 -- l_previous_date := add_months (l_as_of_date, -11);
851
852
853 /*--------------------------------------------------------------+
854 | FII_MEASURE1 - Date |
855 | FII_MEASURE2 - Open Payables Amount |
856 | FII_MEASURE3 - Total Number of Invoices |
857 | FII_MEASURE4 - Invoices on Hold Amount |
858 | FII_MEASURE5 - Number of Invoices |
859 | FII_MEASURE6 - Weighted Average Days Past Due |
860 ---------------------------------------------------------------*/
861
862 -- for fii_measure5
863
864 -- if Operating Unit and Supplier are selected then drills to Invoice Detail
865 -- report for the total invoices using the same parameters.
866 -- Form function to drill to : FII_AP_INV_ON_HOLD_DETAIL.
867
868
869 /* As part of bug 3497818 we check if the table FII_AR_SALES_CREDITS is present. If it is present then we need
870 to use the new logic of populating the urls which is available in 11.5.10 env so as to avoid the security concern
871 arising due to using of Drill across package.
872 For 11.5.9 environments we will be using the same old logic of using the drill across package for passing the dates
873 In 11.5.9 environments the FII_AR_SALES_CREDITS table will not exist and hence this test will suffice.*/
874
875 IF(FND_INSTALLATION.GET_APP_INFO('FII', l_status, l_industry, l_fii_schema))
876 THEN NULL;
877 END IF;
878
879 /* Commented out by VKAZHIPU, since FII_AP_DRILL_ACROSS Package is not used for drill down */
880 /* bug 4568962 */
881
882
883 /*select count(*) into l_count from all_tables where table_name = 'FII_AR_SALES_CREDITS' and
884 rownum = 1 and owner = l_fii_schema;
885
886 IF l_count = 0 THEN
887
888 IF (l_org_where LIKE '%ORGANIZATION+FII_OPERATING_UNITS%' AND l_supplier_where LIKE '%SUPPLIER+POA_SUPPLIERS%') THEN
889 l_url_1 := 'pFunctionName=FII_AP_DRILL_ACROSS&pSource=FII_AP_HOLD_TREND&pOperatingUnit=FII_OPERATING_UNITS&pSupplier=POA_SUPPLIERS&pCurrency=FII_CURRENCIES&pAsOfDateValue=FII_MEASURE1&pPeriod=Dummy&pParamIds=Y';
890 ELSE
891 l_url_1 := '';
892 END IF;
893
894 ELSE */
895
896
897 IF (l_org_where LIKE '%ORGANIZATION+FII_OPERATING_UNITS%' AND l_supplier_where LIKE '%SUPPLIER+POA_SUPPLIERS%') THEN
898 l_url_1 := 'AS_OF_DATE=''||drill_date||''&pFunctionName=FII_AP_INV_ON_HOLD_DETAIL&pParamIds=Y&FII_REPORT_SOURCE=FII_AP_INV_ON_HOLD_DETAIL';
899 ELSE
900 l_url_1 := '';
901 END IF;
902
903 --END IF;
904
905 sqlstmt :=
906 ' SELECT name VIEWBY,
907 id VIEWBYID,
908 name FII_MEASURE1,
909 sum(open_amt) FII_MEASURE2,
910 sum(open_count) FII_MEASURE3,
911 sum(inv_on_hold_amt) FII_MEASURE4,
912 sum(inv_on_hold_count) FII_MEASURE5,
913 id FII_MEASURE6,
914 '''||l_url_1||''' FII_ATTRIBUTE1
915 FROM
916 (SELECT
917 t.ent_period_id id,
918 to_char(t.end_date,'''||l_date_mask||''') name,
919 to_char(t.end_date,''DD/MM/YYYY'') drill_date,
920 sum( f.open_amt'||l_currency||') open_amt,
921 sum(f.open_count) open_count,
922 0 inv_on_hold_amt,
923 0 inv_on_hold_count
924 FROM FII_AP_LIA_IB_MV f,
925 fii_time_structures cal,
926 fii_time_ent_period t
927 WHERE f.time_id = cal.time_id
928 AND f.period_type_id = cal.period_type_id
929 AND bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
930 AND cal.report_date = t.end_date
931 AND t.end_date >= :PREVIOUS_DATE
932 AND t.end_date < &BIS_CURRENT_ASOF_DATE
933 AND f.gid = :GID2'||l_org_where||l_supplier_where||'
934 GROUP BY t.ent_period_id, t.end_date
935 UNION ALL
936 SELECT
937 10000000 id,
938 to_char(cal.report_date,'''||l_date_mask||''') name,
939 to_char(cal.report_date,''DD/MM/YYYY'') drill_date,
940 sum(f.open_amt'||l_currency||') open_amt,
941 sum(f.open_count) open_count,
942 0 inv_on_hold_amt,
943 0 inv_on_hold_count
944 FROM FII_AP_LIA_IB_MV f,
945 fii_time_structures cal
946 WHERE f.time_id = cal.time_id
947 AND f.period_type_id = cal.period_type_id
948 AND bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
949 AND cal.report_date = &BIS_CURRENT_ASOF_DATE
950 AND f.gid = :GID2'||l_org_where||l_supplier_where||'
951 GROUP BY cal.report_date
952 UNION ALL
953 SELECT
954 t.ent_period_id id,
955 to_char(t.end_date,'''||l_date_mask||''') name,
956 to_char(t.end_date,''DD/MM/YYYY'') drill_date,
957 0 open_amt,
958 0 open_count,
959 sum(f.inv_on_hold_amt'||l_currency||') inv_on_hold_amt,
960 sum(f.inv_on_hold_count) inv_on_hold_count
961 FROM FII_AP_HLIA_I_MV f,
962 fii_time_structures cal,
963 fii_time_ent_period t
964 WHERE f.time_id = cal.time_id
965 AND f.period_type_id = cal.period_type_id
966 AND bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
967 AND cal.report_date = t.end_date
968 AND t.end_date >= :PREVIOUS_DATE
969 AND t.end_date < &BIS_CURRENT_ASOF_DATE
970 AND f.gid = :GID2'||l_org_where||l_supplier_where||'
971 GROUP BY t.ent_period_id, t.end_date
972 UNION ALL
973 SELECT
974 10000000 id,
975 to_char(cal.report_date,'''||l_date_mask||''') name,
976 to_char(cal.report_date,''DD/MM/YYYY'') drill_date,
977 0 open_amt,
978 0 open_count,
979 sum(f.inv_on_hold_amt'||l_currency||') inv_on_hold_amt,
980 sum(f.inv_on_hold_count) inv_on_hold_count
981 FROM FII_AP_HLIA_I_MV f,
982 fii_time_structures cal
983 WHERE f.time_id = cal.time_id
984 AND f.period_type_id = cal.period_type_id
985 AND bitand(cal.record_type_id, :RECORD_TYPE_ID) = :RECORD_TYPE_ID
986 AND cal.report_date = &BIS_CURRENT_ASOF_DATE
987 AND f.gid = :GID2'||l_org_where||l_supplier_where||'
988 GROUP BY cal.report_date)
989 GROUP by id, name, drill_date
990 ORDER BY id asc ' ;
991
992
993
994
995 FII_PMV_UTIL.bind_variable(
996 p_sqlstmt=>sqlstmt,
997 p_page_parameter_tbl=>p_page_parameter_tbl,
998 p_sql_output=>get_hold_trend_sql,
999 p_bind_output_table=>get_hold_trend_output,
1000 p_record_type_id=>l_record_type_id,
1001 p_view_by=>l_view_by,
1002 p_gid=>l_gid
1003 -- p_period_start=>l_period_start
1004 -- p_cur_period=>l_cur_period
1005 );
1006 END get_hold_trend;
1007
1008
1009 END fii_ap_hold_sum;