1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
|
PROCEDURE GET_finance_FACTS AS
cursor c_finance is
select util_pkg.GET_CUSTOMER_ID(CUSTOMER_ID) as cus_ID,
util_pkg.GET_SECTOR_BY_NATURAL_ID(SECTOR_ID) as sec_ID,
util_pkg.GET_SERVICEAREA_ID(SERVICEAREA_ID) as ser_ID,
util_pkg.GET_GEOGRAPHY_ID(FACILITY_ID,null) as faci_ID,
util_pkg.GET_TIME_ID(YEAR_ID,MONTH_ID) as tran_date_ID,
util_pkg.GET_PRODUCT_ID(PRODUCT_ID) as prod_ID,
util_pkg.GET_SCENARIO_ID(SCENARIO_ID) as scen_ID,
util_pkg.GET_CURRENCY_ID(CURRENCY_ID) as curr_ID,
util_pkg.GET_SYSTEM_ID(SYSTEM_ID) as system_ID,
VALUE,
ACCOUNT_ID
from UPLOAD_finance_NEW;
TYPE t_var IS TABLE OF c_finance%ROWTYPE;
l_finance t_var;
BEGIN
OPEN c_finance;
LOOP
FETCH c_finance
BULK COLLECT INTO l_finance LIMIT 10000;
EXIT WHEN l_finance.COUNT = 0;
--DBMS_OUTPUT.put_line('Loop starting...');
FOR i IN l_finance.FIRST..l_finance.LAST
LOOP
BEGIN
execute immediate 'INSERT INTO f_finance (c_id,
s_id,
sa_id,
geo_id,
t_id_transaction,
prd_id,
sen_id,
cur_id,
sys_id,
pa_value,
ACCOUNT_ID)
VALUES ('
|| '''' || l_finance(i).cus_ID
|| ''',''' || l_finance(i).sec_ID
|| ''',''' || l_finance(i).ser_ID
|| ''',''' || l_finance(i).faci_ID
|| ''',''' || l_finance(i).tran_date_ID
|| ''',''' || l_finance(i).prod_ID
|| ''',''' || l_finance(i).scen_ID
|| ''',''' || l_finance(i).curr_ID
|| ''',''' || l_finance(i).system_ID
|| ''',''' || l_finance(i).value
|| ''',''' || l_finance(i).ACCOUNT_ID
|| ''' )';
END;
END LOOP;
--DBMS_OUTPUT.put_line('Inner Loop completed... Rowcount ' || l_finance.LAST);
END LOOP;
--DBMS_OUTPUT.put_line('Master Loop completed... ');
CLOSE c_finance;
COMMIT;
NULL;
END GET_finance_FACTS; |
