eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.CE_FORECAST_REMOTE_SOURCES

Source


1 PACKAGE BODY CE_FORECAST_REMOTE_SOURCES AS
2 /* $Header: cefremtb.pls 120.5 2005/02/09 22:03:44 sspoonen 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 		forecast_id		NUMBER,
22 		source_view		VARCHAR2,
23 		db_link			VARCHAR2,
24 		forecast_row_id		NUMBER,
25 		aging_table		AgingTab,
26 		conversion_table	ConversionTab,
27 		rp_forecast_currency	VARCHAR2,
28 		rp_exchange_date	DATE,
29 		rp_exchange_type	VARCHAR2,
30 		rp_exchange_rate	NUMBER,
31 		rp_src_curr_type	VARCHAR2,
32 		rp_src_currency		VARCHAR2,
33 		rp_amount_threshold	NUMBER,
34 		lead_time		NUMBER,
35 		criteria1		VARCHAR2,
36 		criteria2		VARCHAR2,
37 		criteria3		VARCHAR2,
38 		criteria4		VARCHAR2,
39 		criteria5		VARCHAR2,
40 		criteria6		VARCHAR2,
41 		criteria7		VARCHAR2,
42 		criteria8		VARCHAR2,
43 		criteria9		VARCHAR2,
44 		criteria10		VARCHAR2,
45 		criteria11		VARCHAR2,
46 		criteria12		VARCHAR2,
47 		criteria13		VARCHAR2,
48 		criteria14		VARCHAR2,
49 		criteria15		VARCHAR2,
50 		amount_table		IN OUT NOCOPY AmountTab) RETURN NUMBER IS
51   from_where_clause   	VARCHAR2(2000) := null;
52   main_query1		VARCHAR2(2500) := null;
53   main_query2		VARCHAR2(2500) := null;
54   cursor_id		INTEGER;
55   exec_id		INTEGER;
56   forecast_column_id	NUMBER;
57   forecast_amount	NUMBER;
58   trx_amount		NUMBER;
59   currency_code		VARCHAR2(30);
60   trx_date		DATE;
61   bank_account_id	NUMBER;
62   i			NUMBER;
63   dummy			NUMBER;
64 
65 BEGIN
66   BEGIN
67     main_query1 := 'SELECT count(*) from ' || source_view;
68     cursor_id := DBMS_SQL.open_cursor;
69     DBMS_SQL.Parse(cursor_id, main_query1, DBMS_SQL.v7);
70     DBMS_SQL.Define_Column(cursor_id, 1, dummy);
71     exec_id := dbms_sql.execute(cursor_id);
72     DBMS_SQL.CLOSE_CURSOR(CURSOR_ID);
73   EXCEPTION
74     WHEN OTHERS THEN
75     	IF DBMS_SQL.is_open(cursor_id) THEN
76 	  DBMS_SQL.close_cursor(cursor_id);
77 	END IF;
78         return (-1);
79   END;
80 
81   --
82   -- Insert aging bucket and conversion rate information to temporary table
83   --
84   IF(db_link IS NOT NULL)THEN
85     FOR i IN 1 .. aging_table.count LOOP
86       INSERT INTO CE_FORECAST_EXT_TEMP (context_value, forecast_request_id, start_date, end_date, forecast_column_id, conversion_rate)
87 	  VALUES ('A', forecast_id, aging_table(i).start_date, aging_table(i).end_date, aging_table(i).column_id, forecast_row_id);
88     END LOOP;
89   END IF;
90 
91   IF(rp_exchange_type <> 'User')THEN
92     FOR i IN 1 .. conversion_table.count LOOP
93       INSERT INTO CE_FORECAST_EXT_TEMP (context_value, forecast_request_id, from_currency_code, conversion_rate)
94 	VALUES ('C', forecast_id, conversion_table(i).from_currency_code, conversion_table(i).conversion_rate);
95     END LOOP;
96   END IF;
97 
98   --
99   -- Build dynamic SQL statement using the user-defined view
100   --
101   IF(rp_exchange_type <> 'User')THEN
102     main_query1 := 'SELECT 	cab.forecast_column_id,
103         src.transaction_amount*curr.conversion_rate,
104 	src.transaction_amount,
105 	src.currency_code,
106 	src.cash_activity_date + '||to_char(lead_time)||',
107 	src.bank_account_id ';
108     main_query2 := 'SELECT 	cab.forecast_column_id,
109         src.transaction_amount*curr.conversion_rate,
110 	src.transaction_amount,
111 	src.currency_code,
112 	src.cash_activity_date + '||to_char(lead_time)||' ';
113   ELSIF(rp_exchange_type IS NOT NULL) THEN
114     main_query1 := 'SELECT 	cab.forecast_column_id,
115         src.transaction_amount*'||to_char(rp_exchange_rate)||',
116 	src.transaction_amount,
117 	src.currency_code,
118 	src.cash_activity_date + '||to_char(lead_time)||',
119 	src.bank_account_id ';
120     main_query2 := 'SELECT 	cab.forecast_column_id,
121         src.transaction_amount*'||to_char(rp_exchange_rate)||',
122 	src.transaction_amount,
123 	src.currency_code,
124 	src.cash_activity_date + '||to_char(lead_time)||' ';
125   ELSE
126     -- cases where src_type = 'E' and forecast_currency = source currency
127     main_query1 := 'SELECT 	cab.forecast_column_id,
128         src.transaction_amount,
129 	src.transaction_amount,
130 	src.currency_code,
131 	src.cash_activity_date + '||to_char(lead_time)||',
132 	src.bank_account_id ';
133     main_query2 := 'SELECT 	cab.forecast_column_id,
134         src.transaction_amount,
135 	src.transaction_amount,
136 	src.currency_code,
137 	src.cash_activity_date + '||to_char(lead_time)||' ';
138   END IF;
139 
140   from_where_clause := '
141 	FROM 	'|| source_view ||' src,
142                	CE_FORECAST_EXT_TEMP cab ';
143 
144   IF(rp_exchange_type <> 'User')THEN
145     from_where_clause := from_where_clause || ',
146 		CE_FORECAST_EXT_TEMP curr ';
147   END IF;
148 
149   from_where_clause := from_where_clause || '
150 	WHERE 	cab.context_value 	= ''A''
151 	AND 	cab.forecast_request_id = '||to_char(forecast_id)||'
152 	AND 	cab.conversion_rate	= '||to_char(forecast_row_id)||'
153 	AND 	src.cash_activity_date + '||to_char(lead_time)||' BETWEEN cab.start_date and cab.end_date ';
154 
155   IF( rp_src_curr_type = 'E' )THEN
156     from_where_clause := from_where_clause || '
157 	AND 	src.currency_code	= '''||rp_src_currency||''' ';
158   ELSIF( rp_src_curr_type = 'F' )THEN
159     from_where_clause := from_where_clause || '
160 	AND 	src.functional_currency	= '''||rp_src_currency||''' ';
161   END IF;
162 
163   IF(rp_exchange_type <> 'User')THEN
164     from_where_clause := from_where_clause || '
165 	AND 	curr.forecast_request_id= '||to_char(forecast_id)||'
166 	AND 	curr.context_value 	= ''C''
167 	AND 	curr.from_currency_code = src.currency_code ';
168   END IF;
169 
170   IF(rp_amount_threshold IS NOT NULL)THEN
171     IF( rp_src_curr_type = 'E' )THEN
172       from_where_clause := from_where_clause || '
173         AND src.transaction_amount	>= '||to_char(rp_amount_threshold);
174     ELSIF( rp_src_curr_type = 'F' )THEN
175       from_where_clause := from_where_clause || '
176         AND src.functional_amount	>= '||to_char(rp_amount_threshold);
177     END IF;
178   END IF;
179 
180   IF( criteria1  IS NOT NULL )THEN from_where_clause := from_where_clause || ' AND src.criteria1  = '''||criteria1 ||''' '; END IF;
181   IF( criteria2  IS NOT NULL )THEN from_where_clause := from_where_clause || ' AND src.criteria2  = '''||criteria2 ||''' '; END IF;
182   IF( criteria3  IS NOT NULL )THEN from_where_clause := from_where_clause || ' AND src.criteria3  = '''||criteria3 ||''' '; END IF;
183   IF( criteria4  IS NOT NULL )THEN from_where_clause := from_where_clause || ' AND src.criteria4  = '''||criteria4 ||''' '; END IF;
184   IF( criteria5  IS NOT NULL )THEN from_where_clause := from_where_clause || ' AND src.criteria5  = '''||criteria5 ||''' '; END IF;
185   IF( criteria6  IS NOT NULL )THEN from_where_clause := from_where_clause || ' AND src.criteria6  = '''||criteria6 ||''' '; END IF;
186   IF( criteria7  IS NOT NULL )THEN from_where_clause := from_where_clause || ' AND src.criteria7  = '''||criteria7 ||''' '; END IF;
187   IF( criteria8  IS NOT NULL )THEN from_where_clause := from_where_clause || ' AND src.criteria8  = '''||criteria8 ||''' '; END IF;
188   IF( criteria9  IS NOT NULL )THEN from_where_clause := from_where_clause || ' AND src.criteria9  = '''||criteria9 ||''' '; END IF;
189   IF( criteria10 IS NOT NULL )THEN from_where_clause := from_where_clause || ' AND src.criteria10 = '''||criteria10||''' '; END IF;
190   IF( criteria11 IS NOT NULL )THEN from_where_clause := from_where_clause || ' AND src.criteria11 = '''||criteria11||''' '; END IF;
191   IF( criteria12 IS NOT NULL )THEN from_where_clause := from_where_clause || ' AND src.criteria12 = '''||criteria12||''' '; END IF;
192   IF( criteria13 IS NOT NULL )THEN from_where_clause := from_where_clause || ' AND src.criteria13 = '''||criteria13||''' '; END IF;
193   IF( criteria14 IS NOT NULL )THEN from_where_clause := from_where_clause || ' AND src.criteria14 = '''||criteria14||''' '; END IF;
194   IF( criteria15 IS NOT NULL )THEN from_where_clause := from_where_clause || ' AND src.criteria15 = '''||criteria15||''' '; END IF;
195 
196   main_query1 := main_query1 || from_where_clause;
197   main_query2 := main_query2 || from_where_clause;
198 
199   BEGIN
200     --
201     -- Execute the dynamic SQL statement and prepare the forecast values into
202     -- amount_table, which will be returned to the local database
203     --
204     cursor_id := DBMS_SQL.open_cursor;
205 
206     DBMS_SQL.Parse(cursor_id, main_query1, DBMS_SQL.v7);
207 
208     DBMS_SQL.Define_Column(cursor_id, 1, forecast_column_id);
209     DBMS_SQL.Define_Column(cursor_id, 2, forecast_amount);
210     DBMS_SQL.Define_Column(cursor_id, 3, trx_amount);
211     DBMS_SQL.Define_Column(cursor_id, 4, currency_code,15);
212     DBMS_SQL.Define_Column(cursor_id, 5, trx_date);
213     DBMS_SQL.Define_Column(cursor_id, 6, bank_account_id);
214 
215     exec_id := dbms_sql.execute(cursor_id);
216     i := 0;
217     LOOP
218       IF (DBMS_SQL.FETCH_ROWS(cursor_id) >0 ) THEN
219         DBMS_SQL.COLUMN_VALUE(cursor_id, 1, forecast_column_id);
220         DBMS_SQL.COLUMN_VALUE(cursor_id, 2, forecast_amount);
221         DBMS_SQL.COLUMN_VALUE(cursor_id, 3, trx_amount);
222         DBMS_SQL.COLUMN_VALUE(cursor_id, 4, currency_code);
223         DBMS_SQL.COLUMN_VALUE(cursor_id, 5, trx_date);
224         DBMS_SQL.COLUMN_VALUE(cursor_id, 6, bank_account_id);
225 
226         i := i + 1;
227         amount_table(i).forecast_column_id := forecast_column_id;
228         amount_table(i).forecast_amount := forecast_amount;
229         amount_table(i).trx_amount := trx_amount;
230         amount_table(i).currency_code := currency_code;
231         amount_table(i).trx_date := trx_date;
232         amount_table(i).bank_account_id := bank_account_id;
233       ELSE
234         EXIT;
235       END IF;
236     END LOOP;
237     DBMS_SQL.close_cursor(cursor_id);
238   EXCEPTION
239     WHEN OTHERS THEN
240       IF DBMS_SQL.is_open(cursor_id) THEN
241         DBMS_SQL.close_cursor(cursor_id);
242       END IF;
243       cursor_id := DBMS_SQL.open_cursor;
244 
245       DBMS_SQL.Parse(cursor_id, main_query2, DBMS_SQL.v7);
246 
247       DBMS_SQL.Define_Column(cursor_id, 1, forecast_column_id);
248       DBMS_SQL.Define_Column(cursor_id, 2, forecast_amount);
249       DBMS_SQL.Define_Column(cursor_id, 3, trx_amount);
250       DBMS_SQL.Define_Column(cursor_id, 4, currency_code,15);
251       DBMS_SQL.Define_Column(cursor_id, 5, trx_date);
252 
253       exec_id := dbms_sql.execute(cursor_id);
254       i := 0;
255       LOOP
256         IF (DBMS_SQL.FETCH_ROWS(cursor_id) >0 ) THEN
257           DBMS_SQL.COLUMN_VALUE(cursor_id, 1, forecast_column_id);
258           DBMS_SQL.COLUMN_VALUE(cursor_id, 2, forecast_amount);
259           DBMS_SQL.COLUMN_VALUE(cursor_id, 3, trx_amount);
260           DBMS_SQL.COLUMN_VALUE(cursor_id, 4, currency_code);
261           DBMS_SQL.COLUMN_VALUE(cursor_id, 5, trx_date);
262 
263           i := i + 1;
264           amount_table(i).forecast_column_id := forecast_column_id;
265           amount_table(i).forecast_amount := forecast_amount;
266           amount_table(i).trx_amount := trx_amount;
267           amount_table(i).currency_code := currency_code;
268           amount_table(i).trx_date := trx_date;
269 	  amount_table(i).bank_account_id := null;
270         ELSE
271           EXIT;
272         END IF;
273       END LOOP;
274       DBMS_SQL.close_cursor(cursor_id);
275 
276   END;
277 
278   --
279   -- Delete records from temporary table
280   --
281   DELETE FROM CE_FORECAST_EXT_TEMP
282 	WHERE 	forecast_request_id = forecast_id
283 	AND	context_value = 'C';
284   DELETE FROM CE_FORECAST_EXT_TEMP
285 	WHERE	forecast_request_id = forecast_id
286 	AND	conversion_rate = forecast_row_id
287 	AND	context_value = 'A';
288 
289   return (0);
290 
291 EXCEPTION
292   WHEN OTHERS THEN
293     	IF DBMS_SQL.is_open(cursor_id) THEN
294 	  DBMS_SQL.close_cursor(cursor_id);
295 	END IF;
296     	return (-3);
297 END Populate_Remote_Amounts;
298 
299 END CE_FORECAST_REMOTE_SOURCES;