1 package body FII_SIZING_UTIL AS
2 /* $Header: FIISZ01B.pls 120.1 2005/06/07 11:57:04 sgautam noship $ */
3 PROCEDURE fii_pa_revenue_f_cnt (p_from_date DATE,
4 p_to_date DATE,
5 p_num_rows OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
6 cursor c_cnt_rows is
7 select SUM(cnt) from
8 (select count(*) cnt
9 FROM pa_cust_rev_dist_lines_all
10 WHERE program_update_date BETWEEN p_from_date and p_to_date
11 and function_code NOT IN ('LRL','LRB','URL','URB')
12 UNION ALL
13 select count(*) cnt
14 FROM pa_cust_event_rdl_all
15 WHERE program_update_date BETWEEN p_from_date and p_to_date);
16 BEGIN
17 open c_cnt_rows;
18 fetch c_cnt_rows into p_num_rows;
19 close c_cnt_rows;
20 EXCEPTION
21 When OTHERS
22 then p_num_rows := Null;
23 END;
24
25
26 PROCEDURE fii_pa_revenue_f_len (p_from_date DATE,p_to_date DATE,p_avg_row_len OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
27 BEGIN
28 p_avg_row_len := 173;
29 END;
30
31
32 PROCEDURE fii_pa_cost_f_cnt (p_from_date DATE,
33 p_to_date DATE,
34 p_num_rows OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
35 cursor c_cnt_rows is
36 select count(*)
37 from pa_cost_distribution_lines_all
38 WHERE line_type = 'R'
39 AND program_update_date BETWEEN p_from_date AND p_to_date;
40 BEGIN
41 open c_cnt_rows;
42 fetch c_cnt_rows into p_num_rows;
43 close c_cnt_rows;
44 EXCEPTION
45 When OTHERS
46 then p_num_rows := Null;
47 END;
48
49 PROCEDURE fii_pa_cost_f_len (p_from_date DATE,p_to_date DATE,p_avg_row_len OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
50 BEGIN
51 p_avg_row_len := 212;
52 END;
53
54
55 PROCEDURE fii_pa_budget_f_cnt (p_from_date DATE,
56 p_to_date DATE,
57 p_num_rows OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
58 cursor c_cnt_rows is
59 SELECT count(*)
60 FROM pa_budget_lines
61 WHERE last_update_date BETWEEN p_from_date AND p_to_date;
62 BEGIN
63 open c_cnt_rows;
64 fetch c_cnt_rows into p_num_rows;
65 close c_cnt_rows;
66 EXCEPTION
67 When OTHERS
68 then p_num_rows := Null;
69 END;
70
71
72 PROCEDURE fii_pa_budget_f_len (p_from_date DATE,p_to_date DATE,p_avg_row_len OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
73 BEGIN
74 p_avg_row_len := 190;
75 END;
76
77 PROCEDURE fii_pa_budget_m_cnt (p_from_date DATE,
78 p_to_date DATE,
79 p_num_rows OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
80 cursor c_cnt_rows is
81 select count(*)
82 FROM
83 PA_BUDGET_TYPES BT,
84 PA_BUDGET_VERSIONS BV
85 WHERE
86 BV.BUDGET_TYPE_CODE = BT.BUDGET_TYPE_CODE
87 AND BV.BUDGET_STATUS_CODE = 'B'
88 AND BV.LAST_UPDATE_DATE BETWEEN p_from_date AND p_to_date;
89 BEGIN
90 open c_cnt_rows;
91 fetch c_cnt_rows into p_num_rows;
92 close c_cnt_rows;
93 EXCEPTION
94 When OTHERS
95 then p_num_rows := Null;
96 END;
97
98 PROCEDURE fii_pa_budget_m_len (p_from_date DATE,p_to_date DATE,p_avg_row_len OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
99 BEGIN
100 p_avg_row_len := 209;
101 END;
102
103 PROCEDURE fii_pa_exp_type_m_cnt (p_from_date DATE,
104 p_to_date DATE,
105 p_num_rows OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
106 cursor c_cnt_rows is
107 select sum(cnt)
108 from (
109 select count(*) cnt
110 FROM PA_EXPENDITURE_TYPES
111 WHERE last_update_date BETWEEN p_from_date AND p_to_date
112 UNION ALL
113 select count(*) cnt
114 FROM PA_LOOKUPS PL,
115 PA_LOOKUPS PLU
116 WHERE PL.LOOKUP_TYPE = 'ADW DIM LEVEL NAME'
117 AND PL.LOOKUP_CODE = 'ALL_EXP_TYPES'
118 AND PLU.LOOKUP_TYPE = 'ADW RESOURCE NAME'
119 AND PLU.LOOKUP_CODE = 'UNKNOWN');
120 BEGIN
121 open c_cnt_rows;
122 fetch c_cnt_rows into p_num_rows;
123 close c_cnt_rows;
124 EXCEPTION
125 When OTHERS
126 then p_num_rows := Null;
127 END fii_pa_exp_type_m_cnt;
128
129
130 PROCEDURE fii_pa_exp_type_m_len (p_from_date DATE,p_to_date DATE,p_avg_row_len OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
131 BEGIN
132 p_avg_row_len := 119;
133 END;
134
135 PROCEDURE edw_project_m_cnt (p_from_date DATE,
136 p_to_date DATE,
137 p_num_rows OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
138 cursor c_cnt_rows IS
139 select sum(cnt) from (
140 SELECT count(*) cnt
141 from pa_tasks pt
142 WHERE (NOT EXISTS
143 (select '*' from pa_tasks
144 where parent_task_id = pt.task_id)
145 OR pt.parent_task_id IS NULL )
146 AND pt.last_update_date BETWEEN p_from_date AND p_to_date
147 UNION
148 SELECT count(*) cnt
149 FROM pa_projects_all pa
150 WHERE pa.last_update_date BETWEEN p_from_date AND p_to_date
151 UNION
152 SELECT count(*) cnt
153 FROM pjm_seiban_numbers sb
154 WHERE sb.last_update_date BETWEEN p_from_date AND p_to_date);
155 BEGIN
156 open c_cnt_rows;
157 fetch c_cnt_rows into p_num_rows;
158 close c_cnt_rows;
159 EXCEPTION
160 When OTHERS
161 then p_num_rows := Null;
162 END edw_project_m_cnt;
163
164 PROCEDURE edw_project_m_len (p_from_date DATE,p_to_date DATE,p_avg_row_len OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
165 BEGIN
166 p_avg_row_len := 1613;
167 END;
168
169 PROCEDURE FII_AP_INV_ON_HOLD_F_CNT(p_from_date DATE,
170 p_to_date DATE,
171 p_num_rows OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
172
173 CURSOR c_cnt_rows IS
174 SELECT count(*)
175 FROM ap_invoices_all ai
176 WHERE ai.last_update_date between p_from_date and p_to_date
177 AND ai.invoice_type_lookup_code <> 'EXPENSE REPORT'
178 AND ai.cancelled_date IS NULL
179 AND ((EXISTS (SELECT 'this invoice is on hold' FROM ap_holds_all ah
180 WHERE ai.invoice_id = ah.invoice_id
181 AND ai.org_id = ah.org_id
182 AND ah.hold_lookup_code IS NOT NULL
183 AND ah.release_lookup_code IS NULL))
184 OR (EXISTS (SELECT 'this invoice has payment schedule hold' FROM ap_payment_schedules_all aps
185 WHERE ai.invoice_id = aps.invoice_id
186 AND ai.org_id = aps.org_id
187 AND NVL(aps.hold_flag, 'N') = 'Y')));
188 BEGIN
189 OPEN c_cnt_rows;
190 FETCH c_cnt_rows INTO p_num_rows;
191 CLOSE c_cnt_rows;
192 EXCEPTION
193 When OTHERS
194 then p_num_rows := Null;
195 END;
196
197 PROCEDURE FII_AP_INV_ON_HOLD_F_LEN (p_from_date DATE,p_to_date DATE,p_avg_row_len OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
198 BEGIN
199 p_avg_row_len := 203;
200 END;
201
202 PROCEDURE FII_AR_TRX_DIST_F_CNT(p_from_date DATE,
203 p_to_date DATE,
204 p_num_rows OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
205
206 CURSOR c_cnt_rows IS
207 select sum(cnt) from
208 (SELECT count(*) cnt
209 FROM
210 ra_cust_trx_line_gl_dist_all ctlgd,
211 ra_customer_trx_lines_all ctl,
212 ra_customer_trx_all ct
213 WHERE ctlgd.account_set_flag = 'N'
214 AND ctlgd.last_update_date between p_from_date and p_to_date
215 AND ctlgd.customer_trx_line_id = ctl.customer_trx_line_id
216 AND (nvl(ctl.interface_line_context, 'xxx') NOT IN ('ORDER ENTRY', 'GEMMS OP')
217 OR (ctl.interface_line_context = 'ORDER ENTRY'
218 AND translate(ctl.interface_line_attribute6, 'z0123456789', 'z') IS NOT NULL)
219 OR (ctl.interface_line_context = 'GEMMS OP'
220 AND (ctl.interface_line_attribute1 <> '0')))
221 AND ctl.customer_trx_id = ct.customer_trx_id
222 AND ct.complete_flag = 'Y'
223 UNION ALL
224 SELECT count(*)
225 FROM
226 ra_customer_trx_lines_all ctl,
227 ra_customer_trx_all ct
228 WHERE ctl.last_update_date between p_from_date and p_to_date
229 AND (nvl(ctl.interface_line_context, 'xxx') <> 'ORDER ENTRY'
230 OR (ctl.interface_line_context = 'ORDER ENTRY'
231 AND translate(ctl.interface_line_attribute6, 'z0123456789', 'z') IS NOT NULL))
232 AND ctl.customer_trx_id = ct.customer_trx_id
233 AND ct.complete_flag = 'Y'
234 UNION ALL
235 SELECT count(*)
236 FROM
237 ar_adjustments_all adj,
238 ra_customer_trx_all ct
239 WHERE adj.last_update_date between p_from_date and p_to_date
240 AND nvl(adj.status, 'A') = 'A'
241 AND nvl(adj.postable,'Y') = 'Y'
242 AND ct.customer_trx_id = adj.customer_trx_id
243 AND nvl(ct.org_id, -999) = nvl(adj.org_id, -999)
244 AND ct.complete_flag = 'Y'
245 UNION ALL
246 SELECT count(*)
247 from
248 ra_cust_trx_line_gl_dist_all ctlgd,
249 ra_customer_trx_lines_all ctl,
250 ra_customer_trx_all ct
251 WHERE ctlgd.last_update_date between p_from_date and p_to_date
252 AND ctlgd.account_set_flag = 'N'
253 AND ctlgd.customer_trx_line_id = ctl.customer_trx_line_id
254 AND ctl.customer_trx_id = ct.customer_trx_id
255 AND ctl.interface_line_context = 'ORDER ENTRY'
256 AND DECODE(ctl.interface_line_attribute6, NULL, NULL,
257 translate(ctl.interface_line_attribute6, 'z0123456789', 'z')) IS NULL
258 AND ct.complete_flag = 'Y'
259 UNION ALL
260 SELECT count(*)
261 FROM
262 ra_customer_trx_lines_all ctl,
263 ra_customer_trx_all ct
264 WHERE ctl.last_update_date between p_from_date and p_to_date
265 AND ctl.interface_line_context = 'ORDER ENTRY'
266 AND DECODE(ctl.interface_line_attribute6, NULL, NULL,
267 translate(ctl.interface_line_attribute6, 'z0123456789', 'z')) IS NULL
268 AND ctl.customer_trx_id = ct.customer_trx_id
269 AND ct.complete_flag = 'Y'
270 UNION ALL
271 SELECT count(*)
272 FROM
273 ra_cust_trx_line_gl_dist_all ctlgd,
274 ra_customer_trx_lines_all ctl,
275 ra_customer_trx_all ct
276 WHERE ctlgd.last_update_date between p_from_date and p_to_date
277 AND ctlgd.account_set_flag = 'N'
278 AND ctlgd.customer_trx_line_id = ctl.customer_trx_line_id
279 AND nvl(ctl.interface_line_context, 'xxx') IN ('GEMMS OP')
280 AND ctl.customer_trx_id = ct.customer_trx_id
281 AND ct.complete_flag = 'Y'
282 );
283 BEGIN
284 OPEN c_cnt_rows;
285 FETCH c_cnt_rows INTO p_num_rows;
286 CLOSE c_cnt_rows;
287 EXCEPTION
288 When OTHERS
289 then p_num_rows := Null;
290 END;
291
292 PROCEDURE FII_AR_TRX_DIST_F_LEN (p_from_date DATE,p_to_date DATE,p_avg_row_len OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
293 BEGIN
294 p_avg_row_len := 415;
295 END;
296
297 PROCEDURE FII_E_REVENUE_F_CNT(p_from_date DATE,
298 p_to_date DATE,
299 p_num_rows OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
300
301 CURSOR c_cnt_rows IS
302 select sum(cnt) from
303 (SELECT count(*) cnt
304 FROM
305 ra_cust_trx_line_gl_dist_all ctlgd,
306 ra_customer_trx_lines_all ctl,
307 ra_customer_trx_all ct
308 WHERE ctlgd.last_update_date between p_from_date and p_to_date
309 AND ctlgd.account_class not in ('REC','UNBILL')
310 AND ctlgd.account_set_flag = 'N'
311 AND nvl(ctlgd.amount,0) <> 0
312 AND ctlgd.customer_trx_line_id = ctl.customer_trx_line_id
313 AND (nvl(ctl.interface_line_context, 'xxx') NOT IN ('PA INVOICES','ORDER ENTRY')
314 OR (ctl.interface_line_context = 'ORDER ENTRY'
315 AND translate(ctl.interface_line_attribute6, 'z0123456789', 'z') IS NOT NULL))
316 AND ctl.customer_trx_id = ct.customer_trx_id
317 AND ct.complete_flag = 'Y'
318 UNION ALL
319 SELECT count(*)
320 FROM
321 ra_customer_trx_lines_all ctl,
322 ra_customer_trx_all ct
323 WHERE ctl.last_update_date between p_from_date and p_to_date
324 AND (nvl(ctl.interface_line_context, 'xxx') NOT IN ('PA INVOICES', 'ORDER ENTRY')
325 OR (ctl.interface_line_context = 'ORDER ENTRY'
326 AND translate(ctl.interface_line_attribute6, 'z0123456789', 'z') IS NOT NULL))
327 AND (nvl(ctl.quantity_ordered,0) <> 0 OR
328 nvl(ctl.quantity_invoiced,0) <> 0 OR
329 nvl(ctl.quantity_credited,0) <> 0)
330 AND ctl.customer_trx_id = ct.customer_trx_id
331 AND ct.complete_flag = 'Y'
332 UNION ALL
333 SELECT count(*)
334 FROM
335 ar_adjustments_all adj,
336 ra_customer_trx_all ct
337 WHERE adj.last_update_date between p_from_date and p_to_date
338 AND nvl(adj.status, 'A') = 'A'
339 AND nvl(adj.postable,'Y') = 'Y'
340 AND ct.customer_trx_id = adj.customer_trx_id
341 AND nvl(ct.org_id, -999) = nvl(adj.org_id, -999)
342 AND ct.complete_flag = 'Y'
343 UNION ALL
344 SELECT count(*)
345 FROM
346 gl_je_lines jel,
347 gl_je_headers jeh
348 WHERE jel.last_update_date between p_from_date and p_to_date
349 AND jel.status = 'P'
350 AND jel.je_header_id = jeh.je_header_id
351 AND jeh.je_source = 'Manual'
352 AND jeh.actual_flag = 'A'
353 UNION ALL
354 SELECT count(*)
355 FROM
356 pa_cust_rev_dist_lines_all rdl
357 WHERE rdl.creation_date between p_from_date and p_to_date
358 AND rdl.function_code NOT IN ('LRL', 'LRB', 'URL', 'URB')
359 UNION ALL
360 SELECT count(*)
361 FROM
362 PA_CUST_EVENT_RDL_ALL RDL
363 WHERE rdl.creation_date between p_from_date and p_to_date
364 UNION ALL
365 select count(*)
366 FROM
367 pa_draft_revenues_all pdr
368 WHERE pdr.last_update_date between p_from_date and p_to_date
369 AND pdr.unearned_revenue_cr <> 0
370 AND pdr.released_date IS NOT NULL
371 UNION ALL
372 SELECT count(*)
373 from
374 ra_cust_trx_line_gl_dist_all ctlgd,
375 ra_customer_trx_lines_all ctl,
376 ra_customer_trx_all ct
377 WHERE ctlgd.last_update_date between p_from_date and p_to_date
378 AND ctlgd.account_class not in ('REC','UNBILL')
379 AND ctlgd.account_set_flag = 'N'
380 AND nvl(ctlgd.amount,0) <> 0
381 AND ctlgd.customer_trx_line_id = ctl.customer_trx_line_id
382 AND ctl.customer_trx_id = ct.customer_trx_id
383 AND ctl.interface_line_context = 'ORDER ENTRY'
384 AND DECODE(ctl.interface_line_attribute6, NULL, NULL,
385 translate(ctl.interface_line_attribute6, 'z0123456789', 'z')) IS NULL
386 AND ct.complete_flag = 'Y'
387 UNION ALL
388 SELECT count(*)
389 FROM
390 ra_customer_trx_lines_all ctl,
391 ra_customer_trx_all ct
392 WHERE ctl.last_update_date between p_from_date and p_to_date
393 AND ctl.interface_line_context = 'ORDER ENTRY'
394 AND DECODE(ctl.interface_line_attribute6, NULL, NULL,
395 translate(ctl.interface_line_attribute6, 'z0123456789', 'z')) IS NULL
396 AND (nvl(ctl.quantity_ordered,0) <> 0 OR
397 nvl(ctl.quantity_invoiced,0) <> 0 OR
398 nvl(ctl.quantity_credited,0) <> 0)
399 AND ctl.customer_trx_id = ct.customer_trx_id
400 AND ct.complete_flag = 'Y'
401 );
402 BEGIN
403 OPEN c_cnt_rows;
404 FETCH c_cnt_rows INTO p_num_rows;
405 CLOSE c_cnt_rows;
406 EXCEPTION
407 When OTHERS
408 then p_num_rows := Null;
409 END;
410
411 PROCEDURE FII_E_REVENUE_F_LEN (p_from_date DATE,p_to_date DATE,p_avg_row_len OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
412 BEGIN
413 p_avg_row_len := 410;
414 END;
415
416 PROCEDURE EDW_AR_DOC_NUM_M_CNT(p_from_date DATE,
417 p_to_date DATE,
418 p_num_rows OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
419
420 CURSOR c_cnt_rows IS
421 SELECT count(*)
422 FROM ra_customer_trx_all
423 where last_update_date between p_from_date and p_to_date;
424 BEGIN
425 OPEN c_cnt_rows;
426 FETCH c_cnt_rows INTO p_num_rows;
427 CLOSE c_cnt_rows;
428 EXCEPTION
429 When OTHERS
430 then p_num_rows := Null;
431 END;
432
433 PROCEDURE EDW_AR_DOC_NUM_M_LEN (p_from_date DATE,p_to_date DATE,p_avg_row_len OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
434 BEGIN
435 p_avg_row_len := 93;
436 END;
437
438 PROCEDURE EDW_TIME_M_CNT(p_from_date DATE,
439 p_to_date DATE,
440 p_num_rows OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
441
442 CURSOR c_cnt_rows IS
443 select sum(cnt) from
444 (SELECT max(end_date)-min(per.start_date)+1 cnt
445 From gl_periods per,
446 gl_sets_of_books book
447 where per.start_date <= p_to_date
448 and per.end_date >= p_from_date
449 and per.ADJUSTMENT_PERIOD_FLAG = 'N'
450 and per.period_set_name = book.period_set_name
451 and per.period_type = book.accounted_period_type
452 Group By per.period_set_name, per.period_type
453 union all
454 select count(*)
455 from gl_date_period_map map,
456 pa_implementations_all pa,
457 gl_sets_of_books gl,
458 pa_periods_all period
459 where pa.set_of_books_id=gl.set_of_books_id
460 and period.org_id=pa.org_id
461 and map.period_type=pa.pa_period_type
462 and map.period_name=period.period_name
463 and map.period_set_name=gl.period_set_name
464 and map.accounting_date >= p_from_date
465 and map.accounting_date <= p_to_date
466 and map.period_name <> 'NOT ASSIGNED'
467 union all
468 select count(*)
469 from pa_implementations_all pa,
470 gl_sets_of_books gl,
471 pa_periods_all period
472 where pa.set_of_books_id=gl.set_of_books_id
473 and period.org_id=pa.org_id
474 and period.end_date >= p_from_date
475 and period.start_date <= p_to_date
476 and period.period_name <> 'NOT ASSIGNED'
477 union all
478 select count(*) from
479 (select distinct per.period_set_name,
480 per.period_type,
481 per.period_name,
482 per.period_year,
483 per.quarter_num,
484 per.start_date,
485 per.end_date
486 from gl_periods per, gl_sets_of_books book
487 where per.adjustment_period_flag = 'N'
488 and per.period_set_name = book.period_set_name
489 and per.period_type = book.accounted_period_type)
490 union all
491 select to_date('31-12-2000','dd-mm-yyyy')-to_date('01-01-1995','dd-mm-yyyy')+1 cnt
492 from dual
493 union all
494 select months_between(last_day(to_date('31-12-2000','dd-mm-yyyy')),last_day(to_date('01-01-1995','dd-mm-yyyy')))+1 cnt
495 from dual);
496 BEGIN
497 OPEN c_cnt_rows;
498 FETCH c_cnt_rows INTO p_num_rows;
499 CLOSE c_cnt_rows;
500 EXCEPTION
501 When OTHERS
502 then p_num_rows := Null;
503 END;
504
505 PROCEDURE EDW_TIME_M_LEN (p_from_date DATE,p_to_date DATE,p_avg_row_len OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
506 BEGIN
507 p_avg_row_len := 1523;
508 END;
509
510 /* Estimate average row length and number of rows for FII_AP_INV_LINES_F */
511 PROCEDURE FII_AP_INV_LINES_F_CNT (p_from_date DATE,
512 p_to_date DATE,
513 p_num_rows OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
514
515 CURSOR c_cnt_rows IS
516 Select count(*)
517 from
518 ap_invoice_distributions_all aid,
519 ap_invoices_all ai
520 WHERE aid.last_update_date between p_from_date and p_to_date
521 AND NVL(aid.reversal_flag,'N') <> 'Y'
522 AND aid.invoice_id = ai.invoice_id
523 AND aid.org_id = ai.org_id
524 AND ai.invoice_type_lookup_code <> 'EXPENSE REPORT'
525 AND ai.cancelled_date IS NULL;
526
527 BEGIN
528 OPEN c_cnt_rows;
529 FETCH c_cnt_rows INTO p_num_rows;
530 CLOSE c_cnt_rows;
531 END;
532
533 PROCEDURE FII_AP_INV_LINES_F_LEN (p_from_date DATE,p_to_date DATE,p_avg_row_len OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
534 BEGIN
535 p_avg_row_len := 385;
536 END;
537
538 /* Estimate average row length and number of rows for FII_AP_SCH_PAYMTS_F */
539 PROCEDURE FII_AP_SCH_PAYMTS_F_CNT (p_from_date DATE,
540 p_to_date DATE,
541 p_num_rows OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
542 CURSOR c_cnt_rows IS
543 SELECT count(*) cnt
544 FROM ap_payment_schedules_all aps
545 WHERE aps.last_update_date BETWEEN p_from_date and p_to_date;
546
547 BEGIN
548 OPEN c_cnt_rows;
549 FETCH c_cnt_rows INTO p_num_rows;
550 CLOSE c_cnt_rows;
551 END;
552
553 PROCEDURE FII_AP_SCH_PAYMTS_F_LEN (p_from_date DATE,p_to_date DATE,p_avg_row_len OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
554 BEGIN
555 p_avg_row_len := 352;
556 END;
557
558 /* Estimate average row length and number of rows for FII_AP_INV_PAYMTS_F */
559 PROCEDURE FII_AP_INV_PAYMTS_F_CNT (p_from_date DATE,
560 p_to_date DATE,
561 p_num_rows OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
562 CURSOR c_cnt_rows IS
563 SELECT count(*) cnt
564 FROM
565 ap_invoice_payments_all aip,
566 ap_invoices_all ai
567 WHERE aip.last_update_date between p_from_date and p_to_date
568 AND aip.invoice_id = ai.invoice_id
569 AND ai.invoice_type_lookup_code <> 'EXPENSE REPORT';
570
571 BEGIN
572 OPEN c_cnt_rows;
573 FETCH c_cnt_rows INTO p_num_rows;
574 CLOSE c_cnt_rows;
575 END;
576
577 PROCEDURE FII_AP_INV_PAYMTS_F_LEN (p_from_date DATE,p_to_date DATE,p_avg_row_len OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
578 BEGIN
579 p_avg_row_len := 415;
580 END;
581
582 /* Estimate average row length and number of rows for FII_AP_HOLD_DATA_F */
583 PROCEDURE FII_AP_HOLD_DATA_F_CNT (p_from_date DATE,
584 p_to_date DATE,
585 p_num_rows OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
586 CURSOR c_cnt_rows IS
587 SELECT count(*)
588 FROM ap_holds_all ah
589 WHERE ah.last_update_date BETWEEN p_from_date and p_to_date;
590
591 BEGIN
592 OPEN c_cnt_rows;
593 FETCH c_cnt_rows INTO p_num_rows;
594 CLOSE c_cnt_rows;
595 END;
596
597 PROCEDURE FII_AP_HOLD_DATA_F_LEN (p_from_date DATE,p_to_date DATE,p_avg_row_len OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
598 BEGIN
599 p_avg_row_len := 415;
600 END;
601
602 /* Estimate average row length and number of rows for EDW_AP_PAYMENT_M */
603 PROCEDURE EDW_AP_PAYMENT_M_CNT (p_from_date DATE,
604 p_to_date DATE,
605 p_num_rows OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
606 CURSOR c_cnt_rows IS
607 select count(*) cnt
608 from ap_invoice_payments_all aip
609 WHERE aip.last_update_date between p_from_date and p_to_date;
610
611
612 BEGIN
613 OPEN c_cnt_rows;
614 FETCH c_cnt_rows INTO p_num_rows;
615 CLOSE c_cnt_rows;
616 END;
617
618
619 PROCEDURE EDW_AP_PAYMENT_M_LEN (p_from_date DATE,p_to_date DATE,p_avg_row_len OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
620 BEGIN
621 p_avg_row_len := 188;
622 END;
623
624 /* Estimate average row length and number of rows for EDW_INV_TYPE_M */
625 PROCEDURE EDW_INV_TYPE_M_CNT (p_from_date DATE,
626 p_to_date DATE,
627 p_num_rows OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
628 CURSOR c_cnt_rows IS
629 Select count(*) cnt
630 from ap_invoices_all ai
631 where invoice_type_lookup_code <> 'EXPENSE REPORT'
632 AND ai.last_update_date between p_from_date and p_to_date;
633
634 BEGIN
635 OPEN c_cnt_rows;
636 FETCH c_cnt_rows INTO p_num_rows;
637 CLOSE c_cnt_rows;
638 END;
639
640 PROCEDURE EDW_INV_TYPE_M_LEN (p_from_date DATE,p_to_date DATE,p_avg_row_len OUT NOCOPY /* file.sql.39 change */ NUMBER) IS
641 BEGIN
642 p_avg_row_len := 149;
643 END;
644
645 END;
[Home] [Help]