[Home] [Help]
PACKAGE BODY: APPS.XTR_FORECAST_REMOTE_SOURCES
Source
1 PACKAGE BODY XTR_FORECAST_REMOTE_SOURCES AS
2 /* $Header: xtrfrmtb.pls 115.0 99/07/17 00:31:38 porting ship $ */
3 /* ---------------------------------------------------------------------
4 | PUBLIC PROCEDURE |
5 | Populate_Remote_Amounts |
6 | |
7 | DESCRIPTION |
8 | This procedure builds the query to calculate the forecast |
9 | amounts from remote source transactions. |
10 | CALLED BY |
11 | CE_CSH_FCST_POP.Build_Remote_Query |
12 | REQUIRES |
13 | |
14 | RETURN VALUE |
15 | 0 No Error |
16 | -1 Other exceptions |
17 | HISTORY |
18 | 27-JUN-1997 Created Wynne Chan |
19 --------------------------------------------------------------------- */
20 FUNCTION Populate_Remote_Amounts (
21 source_view VARCHAR2,
22 db_link VARCHAR2,
23 criteria1 VARCHAR2,
24 criteria2 VARCHAR2,
25 criteria3 VARCHAR2,
26 criteria4 VARCHAR2,
27 criteria5 VARCHAR2,
28 criteria6 VARCHAR2,
29 criteria7 VARCHAR2,
30 criteria8 VARCHAR2,
31 criteria9 VARCHAR2,
32 criteria10 VARCHAR2,
33 criteria11 VARCHAR2,
34 criteria12 VARCHAR2,
35 criteria13 VARCHAR2,
36 criteria14 VARCHAR2,
37 criteria15 VARCHAR2) RETURN NUMBER IS
38 main_query varchar2(2000) := null;
39 cursor_id INTEGER;
40 exec_id INTEGER;
41 forecast_period_temp_id NUMBER;
42 forecast_amount NUMBER;
43 i NUMBER;
44 dummy NUMBER;
45 currency VARCHAR2(15);
46 amount_date DATE;
47 level_of_summary VARCHAR2(1);
48 l_emu VARCHAR2(15);
49
50 CURSOR C_cur(p_cur VARCHAR2) IS
51 SELECT nvl(derive_type, 'NONE')
52 FROM gl_currencies
53 WHERE currency_code = p_cur;
54
55 CURSOR C_period(p_pid NUMBER) IS
56 SELECT end_date,
57 level_of_summary
58 FROM xtr_forecast_period_temp
59 WHERE forecast_period_temp_id = p_pid;
60
61 BEGIN
62 BEGIN
63 main_query := 'SELECT count(*) from ' || source_view;
64 cursor_id := DBMS_SQL.open_cursor;
65 DBMS_SQL.Parse(cursor_id, main_query, DBMS_SQL.v7);
66 DBMS_SQL.Define_Column(cursor_id, 1, dummy);
67 exec_id := dbms_sql.execute(cursor_id);
68 DBMS_SQL.CLOSE_CURSOR(CURSOR_ID);
69 EXCEPTION
70 WHEN OTHERS THEN
71 IF DBMS_SQL.is_open(cursor_id) THEN
72 DBMS_SQL.close_cursor(cursor_id);
73 END IF;
74 return (-1);
75 END;
76
77 --
78 -- Build dynamic SQL statement using the user-defined view
79 --
80 main_query := 'SELECT cab.forecast_period_temp_id,
81 src.currency_code,
82 SUM(src.transaction_amount) ';
83
84 main_query := main_query || '
85 FROM '|| source_view ||' src,
86 XTR_FORECAST_PERIOD_TEMP cab ';
87
88 main_query := main_query || '
89 WHERE src.cash_activity_date BETWEEN cab.start_date and cab.end_date ';
90
91 IF( criteria1 IS NOT NULL )THEN main_query := main_query || ' AND src.criteria1 = '''||criteria1 ||''' '; END IF;
92 IF( criteria2 IS NOT NULL )THEN main_query := main_query || ' AND src.criteria2 = '''||criteria2 ||''' '; END IF;
93 IF( criteria3 IS NOT NULL )THEN main_query := main_query || ' AND src.criteria3 = '''||criteria3 ||''' '; END IF;
94 IF( criteria4 IS NOT NULL )THEN main_query := main_query || ' AND src.criteria4 = '''||criteria4 ||''' '; END IF;
95 IF( criteria5 IS NOT NULL )THEN main_query := main_query || ' AND src.criteria5 = '''||criteria5 ||''' '; END IF;
96 IF( criteria6 IS NOT NULL )THEN main_query := main_query || ' AND src.criteria6 = '''||criteria6 ||''' '; END IF;
97 IF( criteria7 IS NOT NULL )THEN main_query := main_query || ' AND src.criteria7 = '''||criteria7 ||''' '; END IF;
98 IF( criteria8 IS NOT NULL )THEN main_query := main_query || ' AND src.criteria8 = '''||criteria8 ||''' '; END IF;
99 IF( criteria9 IS NOT NULL )THEN main_query := main_query || ' AND src.criteria9 = '''||criteria9 ||''' '; END IF;
100 IF( criteria10 IS NOT NULL )THEN main_query := main_query || ' AND src.criteria10 = '''||criteria10||''' '; END IF;
101 IF( criteria11 IS NOT NULL )THEN main_query := main_query || ' AND src.criteria11 = '''||criteria11||''' '; END IF;
102 IF( criteria12 IS NOT NULL )THEN main_query := main_query || ' AND src.criteria12 = '''||criteria12||''' '; END IF;
103 IF( criteria13 IS NOT NULL )THEN main_query := main_query || ' AND src.criteria13 = '''||criteria13||''' '; END IF;
104 IF( criteria14 IS NOT NULL )THEN main_query := main_query || ' AND src.criteria14 = '''||criteria14||''' '; END IF;
105 IF( criteria15 IS NOT NULL )THEN main_query := main_query || ' AND src.criteria15 = '''||criteria15||''' '; END IF;
106 main_query := main_query || ' GROUP BY cab.forecast_period_temp_id, src.currency_code';
107 BEGIN
108 --
109 -- Execute the dynamic SQL statement and prepare the forecast values into
110 -- amount_table, which will be returned to the local database
111 --
112 cursor_id := DBMS_SQL.open_cursor;
113
114 DBMS_SQL.Parse(cursor_id, main_query, DBMS_SQL.v7);
115
116 DBMS_SQL.Define_Column(cursor_id, 1, forecast_period_temp_id);
117 DBMS_SQL.Define_Column(cursor_id, 2, currency, 15);
118 DBMS_SQL.Define_Column(cursor_id, 3, forecast_amount);
119
120 exec_id := dbms_sql.execute(cursor_id);
121 i := 0;
122 LOOP
123 IF (DBMS_SQL.FETCH_ROWS(cursor_id) >0 ) THEN
124 DBMS_SQL.COLUMN_VALUE(cursor_id, 1, forecast_period_temp_id);
125 DBMS_SQL.COLUMN_VALUE(cursor_id, 2, currency);
126 DBMS_SQL.COLUMN_VALUE(cursor_id, 3, forecast_amount);
127
128 OPEN C_cur(currency);
129 FETCH C_cur INTO l_emu;
130 CLOSE C_cur;
131
132 IF l_emu = 'EMU' THEN
133 forecast_amount := GL_CURRENCY_API.convert_amount(currency,
134 'EUR',
135 XTR_CASH_FCST.G_rp_forecast_start_date,
136 'EMU-FIXED',
137 forecast_amount);
138
139 currency := 'EUR';
140 END IF;
141
142 OPEN C_period(forecast_period_temp_id);
143 FETCH C_period INTO amount_date,
144 level_of_summary;
145 CLOSE C_period;
146
147 INSERT INTO xtr_external_cashflows(amount_date,
148 amount,
149 currency,
150 company_code,
151 trx_type,
152 level_of_summary)
153 VALUES (amount_date,
154 forecast_amount,
155 currency,
156 XTR_CASH_FCST.G_party_code,
157 XTR_CASH_FCST.G_trx_type,
158 level_of_summary);
159 ELSE
160 EXIT;
161 END IF;
162 END LOOP;
163 DBMS_SQL.CLOSE_CURSOR(CURSOR_ID);
164 EXCEPTION
165 WHEN OTHERS THEN
166 IF DBMS_SQL.is_open(cursor_id) THEN
167 DBMS_SQL.close_cursor(cursor_id);
168 END IF;
169 return (-2);
170 END;
171
172 return (0);
173
174 EXCEPTION
175 WHEN OTHERS THEN
176 IF DBMS_SQL.is_open(cursor_id) THEN
177 DBMS_SQL.close_cursor(cursor_id);
178 END IF;
179 return (-3);
180 END Populate_Remote_Amounts;
181
182 END XTR_FORECAST_REMOTE_SOURCES;