[Home] [Help]
PACKAGE BODY: APPS.ITG_SYNCEXCHINBOUND_PVT
Source
1 PACKAGE BODY ITG_SyncExchInbound_PVT AS
2 /* ARCS: $Header: itgvseib.pls 120.6 2006/08/16 09:05:59 bsaratna noship $
3 * CVS: itgvseib.pls,v 1.15 2002/12/23 21:20:30 ecoe Exp
4 */
5 l_debug_level NUMBER := to_number(nvl(fnd_profile.value('CLN_DEBUG_LEVEL'), '5'));
6 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ITG_SyncExchInbound_PVT';
7 g_action VARCHAR2(400);
8
9 FUNCTION valid_rate_type(
10 p_ratetype IN VARCHAR2
11 ) RETURN boolean
12 IS
13 l_number NUMBER;
14 BEGIN
15 SELECT 1
16 INTO l_number
17 FROM DUAL
18 WHERE EXISTS (SELECT t.conversion_type
19 from gl_daily_conversion_types_v t
20 where t.conversion_type <> 'User'
21 and t.conversion_type <> 'EMU FIXED'
22 and t.modify_privilege = 'Y'
23 and t.conversion_type = p_ratetype);
24
25 RETURN true;
26 EXCEPTION
27 WHEN OTHERS THEN
28 RETURN false;
29 END valid_rate_type;
30
31 FUNCTION get_currency_code(
32 p_sob VARCHAR2,
33 p_sob_id VARCHAR2
34 ) RETURN VARCHAR2
35 IS
36 l_currency_code VARCHAR2(15);
37 BEGIN
38 IF (l_Debug_Level <= 2) THEN
39 itg_debug_pub.Add('Entering - GCC :Getting GL currency code' ,2);
40 END IF;
41
42 BEGIN
43 SELECT currency_code
44 INTO l_currency_code
45 FROM gl_sets_of_books
46 WHERE set_of_books_id = p_sob_id;
47
48 IF (l_Debug_Level <= 1) THEN
49 itg_debug_pub.Add('GCC - l_currency_code'||l_currency_code ,1);
50 END IF;
51
52 EXCEPTION
53 WHEN OTHERS THEN
54 itg_msg.no_gl_currency(p_sob);
55 RAISE FND_API.G_EXC_ERROR;
56 END;
57
58 IF (l_Debug_Level <= 2) THEN
59 itg_debug_pub.Add('Exiting - GCC :Getting GL currency code' ,2);
60 END IF;
61
62 RETURN l_currency_code;
63 END;
64
65
66
67 PROCEDURE Process_ExchangeRate(
68 x_return_status OUT NOCOPY VARCHAR2,
69 x_msg_count OUT NOCOPY NUMBER,
70 x_msg_data OUT NOCOPY VARCHAR2,
71 p_syncind IN VARCHAR2,
72 p_quantity IN NUMBER,
73 p_currency_from IN VARCHAR2,
74 p_currency_to IN VARCHAR2,
75 p_factor IN VARCHAR2,
76 p_sob IN VARCHAR2,
77 p_ratetype IN VARCHAR2,
78 p_creation_date IN DATE,
79 p_effective_date IN DATE
80 )
81 IS
82 /* Business object constants. */
83 l_api_name CONSTANT VARCHAR2(30) := 'Process_ExchangeRate';
84 l_api_version CONSTANT NUMBER := 1.0;
85 l_quantity NUMBER;
86 l_count NUMBER;
87 l_var NUMBER;
88
89 CURSOR currency_csr(
90 p_ccode IN VARCHAR2
91 ) IS
92 SELECT currency_code,
93 precision,
94 extended_precision,
95 derive_type
96 FROM fnd_currencies
97 WHERE currency_code = p_ccode
98 AND enabled_flag = 'Y'
99 AND currency_flag = 'Y'
100 AND (start_date_active <= p_effective_date
101 OR start_date_active IS NULL)
102 AND (end_date_active >= p_effective_date
103 OR end_date_active IS NULL);
104
105 currency_to_rec currency_csr%ROWTYPE;
106 currency_from_rec currency_csr%ROWTYPE;
107 BEGIN
108 /* Initialize return status */
109 x_return_status := FND_API.G_RET_STS_SUCCESS;
110 g_action := 'Exchange-rate sync';
111
112 IF (l_Debug_Level <= 2) THEN
113 itg_debug_pub.Add('Entering - PER :Process_ExchangeRate' ,2);
114 END IF;
115
116 BEGIN
117 SAVEPOINT Process_ExchangeRate_PVT;
118 ITG_Debug.setup(
119 p_reset => TRUE,
120 p_pkg_name => G_PKG_NAME,
121 p_proc_name => l_api_name
122 );
123
124 IF (l_Debug_Level <= 1) THEN
125 itg_debug_pub.Add('PER - Top of procedure.' ,1);
126 itg_debug_pub.Add('PER - p_syncind' ||p_syncind ,1);
127 itg_debug_pub.Add('PER - p_quantity' ||p_quantity ,1);
128 itg_debug_pub.Add('PER - p_currency_from' ||p_currency_from ,1);
129 itg_debug_pub.Add('PER - p_currency_to' ||p_currency_to ,1);
130 itg_debug_pub.Add('PER - p_factor' ||p_factor ,1);
131 itg_debug_pub.Add('PER - p_sob' ||p_sob ,1);
132 itg_debug_pub.Add('PER - p_ratetype' ||p_ratetype ,1);
133 itg_debug_pub.Add('PER - p_creation_date' ||p_creation_date ,1);
134 itg_debug_pub.Add('PER - p_effective_date' ||p_effective_date ,1);
135 END IF;
136
137 g_action := 'Exchange-rate parameter validation';
138 /* now validate data */
139 DECLARE
140 l_param_name VARCHAR2(30) := NULL;
141 l_param_value VARCHAR2(2000) := 'NULL';
142 BEGIN
143 IF p_currency_to IS NULL THEN
144 l_param_name := 'CURRTO';
145 ELSIF p_quantity IS NULL THEN
146 l_param_name := 'QUANTITY';
147 ELSIF p_creation_date IS NULL THEN
148 l_param_name := 'DATETIME qualifier="CREATION"';
149 ELSIF p_effective_date IS NULL THEN
150 l_param_name := 'DATETIME qualifier="EFFECTIVE"';
151 ELSIF NOT valid_rate_type(p_ratetype) THEN
152 l_param_name := 'RATETYPE';
153 l_param_value := p_ratetype;
154 ELSIF NVL(UPPER(p_syncind), 'z') NOT IN ('A', 'C') THEN
155 l_param_name := 'SYNCIND';
156 l_param_value := p_syncind;
157 ELSIF p_factor IS NOT NULL THEN
158 BEGIN
159 select to_number(p_factor) into l_var from dual;
160 EXCEPTION
161 WHEN OTHERS THEN
162 l_param_name := 'FACTOR';
163 l_param_value := p_factor;
164 END;
165 END IF;
166
167 IF l_param_name IS NOT NULL THEN
168 ITG_MSG.missing_element_value(l_param_name, l_param_value);
169 RAISE FND_API.G_EXC_ERROR;
170 END IF;
171
172
173 /* validate currency: exist, enabled, date effective. not EMU to EMU. */
174 /* validate currto */
175 IF (l_Debug_Level <= 1) THEN
176 itg_debug_pub.Add('PER - Checking/getting p_currency_to' ,1);
177 END IF;
178
179 OPEN currency_csr(p_currency_to);
180 FETCH currency_csr INTO currency_to_rec;
181 CLOSE currency_csr;
182
183 IF(currency_to_rec.currency_code IS NULL) THEN
184 ITG_MSG.missing_element_value('CURRTO', p_currency_to);
185 RAISE FND_API.G_EXC_ERROR;
186 END IF;
187
188 /* validate currfrom */
189 IF p_currency_from IS NULL THEN
190 ITG_MSG.missing_element_value('CURRFROM', p_currency_from);
191 RAISE FND_API.G_EXC_ERROR;
192 END IF;
193
194
195 IF (l_Debug_Level <= 1) THEN
196 itg_debug_pub.Add('PER - Checking/getting p_currency_from' ,1);
197 END IF;
198
199 OPEN currency_csr(p_currency_from);
200 FETCH currency_csr INTO currency_from_rec;
201 CLOSE currency_csr;
202
203 IF (currency_from_rec.currency_code IS NULL) THEN
204 ITG_MSG.missing_element_value('CURRFROM', p_currency_from);
205 RAISE FND_API.G_EXC_ERROR;
206 END IF;
207
208 IF (l_Debug_Level <= 1) THEN
209 itg_debug_pub.Add('PER - Some final checks.' ,1);
210 END IF;
211
212 IF currency_from_rec.currency_code = currency_to_rec.currency_code THEN
213 ITG_MSG.same_currency_code;
214 RAISE FND_API.G_EXC_ERROR;
215 ELSIF( currency_from_rec.derive_type IS NOT NULL
216 AND currency_from_rec.derive_type <> currency_to_rec.derive_type
217 ) THEN
218 /* Check this condition , I am not sure, it was checking with =
219 originally .I changed that to <> ** */
220 ITG_MSG.no_currtype_match(currency_from_rec.derive_type,currency_to_rec.derive_type);
221 RAISE FND_API.G_EXC_ERROR;
222 END IF;
223 END;
224 -- Validation ends
225 /* NOTE: Create a quantity based on the factor ??
226 * (not sure, copied from the V1 handler)
227 */
228 l_quantity := p_quantity / power(10, p_factor);
229
230 IF (l_Debug_Level <= 1) THEN
231 itg_debug_pub.Add('PER - l_quantity'||l_quantity,1);
232 END IF;
233
234
235 /* round up to max precision allowed. */
236 l_quantity := ROUND(l_quantity, currency_to_rec.extended_precision);
237
238 /* insert data */
239 g_action := 'Exchange-rate creation';
240
241 IF (l_Debug_Level <= 1) THEN
242 itg_debug_pub.Add('PER - Processing data.' ,1);
243 END IF;
244
245 IF (l_Debug_Level <= 1) THEN
246 itg_debug_pub.Add('PER - Insert into gl_daily_rates_interface' ,1);
247 END IF;
248
249 /* Set the Enable Trigger flag to TRUE "gl_crm_utilities_pkg.enable_trigger" */
250 gl_crm_utilities_pkg.change_flag(TRUE);
251
252 INSERT INTO gl_daily_rates_interface(
253 from_currency, to_currency,
254 from_conversion_date, to_conversion_date,
255 user_conversion_type, conversion_rate, mode_flag
256 ) VALUES (
257 p_currency_from, p_currency_to,
258 p_effective_date, p_effective_date,
259 p_ratetype, l_quantity, 'I');
260
261 /* Set the Enable Trigger flag to FALSE "gl_crm_utilities_pkg.enable_trigger" */
262 gl_crm_utilities_pkg.change_flag(FALSE);
263
264 DECLARE
265 CURSOR get_error_code IS
266 SELECT error_code
267 FROM gl_daily_rates_interface
268 WHERE from_currency = p_currency_from
269 AND to_currency = p_currency_to
270 AND from_conversion_date = p_effective_date
271 AND to_conversion_date = p_effective_date
272 AND user_conversion_type = 'Corporate'
273 AND mode_flag = 'X';
274
275 l_error_code gl_daily_rates_interface.error_code%TYPE;
276 l_found BOOLEAN;
277
278 BEGIN
279 IF (l_Debug_Level <= 1) THEN
280 itg_debug_pub.Add('PER - Check errors in gl_daily_rates_interface' ,1);
281 END IF;
282
283 OPEN get_error_code;
284 FETCH get_error_code INTO l_error_code;
285 l_found := get_error_code%FOUND;
286 CLOSE get_error_code;
287
288 IF l_found THEN
289 ITG_MSG.daily_exchange_rate_error(
290 p_currency_from,
291 p_currency_to,
292 l_error_code);
293 RAISE FND_API.G_EXC_ERROR;
294 END IF;
295 END;
296
297 COMMIT WORK;
298
299 IF (l_Debug_Level <= 2) THEN
300 itg_debug_pub.Add('EXITING - PER: Process_ExchangeRate.' ,2);
301 END IF;
302 EXCEPTION
303 WHEN FND_API.G_EXC_ERROR THEN
304 ROLLBACK TO Process_ExchangeRate_PVT;
305 x_return_status := FND_API.G_RET_STS_ERROR;
306 ITG_msg.checked_error(g_action);
307
308 WHEN OTHERS THEN
309 ROLLBACK TO Process_ExchangeRate_PVT;
310 ITG_msg.unexpected_error(g_action);
311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
312 itg_debug.msg('Unexpected error (Exchange-rate sync) - ' || substr(SQLERRM,1,255),true);
313 END;
314
315
316 -- Removed FND_MSG_PUB.Count_And_Get
317 END Process_ExchangeRate;
318 END ITG_SyncExchInbound_PVT;